To release the locks from a table

How to Unlock the Locked Table in ORACLE

Oracle puts locks while performing any DDL or DML operation on oracle tables.When table locks is present on any tables in Oracle we cannot run DDL on those tables.

Some of the locks automatically set by oracle are RS and RX Locks.

SELECT … FOR UPDATE execution results in RS (row share) table lock. When you execute an INSERT, UPDATE or DELETE Oracle puts RX (row exclusive) table lock.

We have to kill the session which holds the lock in order to execute further operations. Follow the below steps to kill the session and forcibly unlock the table.

Let’s assume that 'EMP' table is locked,

SELECT object_id FROM dba_objects WHERE object_name='EMP';

 OBJECT_ID

----------

   7401242

If there are no locks present for the table 'EMP' this query won’t return any values.

SELECT sid FROM v$lock WHERE id1=7401242

SID

----------

   3434

SELECT sid, serial# from v$session where sid=3434

       SID    SERIAL#

---------- ----------

      3434      92193

ALTER SYSTEM KILL SESSION '3434,92193' ;

Once the session is killed you will be able to carry out any DDL activities on EMP table. Also you can check in TOAD if there are any active sessions associated to the SID that we killed, to make sure that the session has been killed.

How to Show/Hide Colums in an oracle report at runtime

I have build a report. but now i want that the user of the report can make the unnecessary columns invisible from the report at runtime and next time when he opens the report that invisible colums should not be visible to him. but when he needs, he could make that colum visible.

From the column's property; you can use "Format Trigger" to code your conditions that you need either to show that column (Return (true) ) or hide it (Return (False)). 
Hope that helps;

Solution:
-----------

Create a user parameter field in the report, let user to select in parameter form, for example in the field named "show_hide" user can have options 
1: Show Column 
2: Hide Column 
Create a format trigger for the column(s) which you desired to show/hide, 
enter the following code 
if :show_hide='1' then 
return (TRUE); 
else 
return (FALSE); 
end if; 

Thats it. 

Alert on Order is booked but line is Entered

Based on a request from one of our reader, below are the steps on how to notify users when an order is booked or new line is inserted on a booked order.

The Alert below will send notification once a day in the morning at 8:00 am.

1) Define Alert
                                            
Query used is

SELECT   (select lower(name) from v$database) instance_name ,RPAD(HZP.PARTY_NAME,40)
,        RPAD(OOH.CUST_PO_NUMBER,35)
,        RPAD(OOH.ORDER_NUMBER,15)
,        RPAD(OOL.LINE_NUMBER,10)
,        RPAD(OOL.ORDERED_ITEM,40)
,        RPAD(OOL.ORDERED_QUANTITY,15)
,        RPAD(HCSU.LOCATION,15)  
,        RPAD(DECODE(OOL.DELIVER_TO_ORG_ID,NULL,NULL,HCSU2.LOCATION),15) 
,        RPAD('CUSTOMER',41)||RPAD('CUST_PO_NUMBER',36)
                            ||RPAD('ORDER_NUMBER',16)
                            ||RPAD('LINE_NUM',11)
                            ||RPAD('ORDERED_ITEM',41)
                            ||RPAD('ORDERED_QTY',16)
                            ||RPAD('SHIP_TO_STORE',16)
                            ||RPAD('DELIVER_TO',16)
,        RPAD('--------',41)||RPAD('--------------',36)
                            ||RPAD('------------',16)
                            ||RPAD('--------',11)
                            ||RPAD('------------',41)
                            ||RPAD('-----------',16)
                            ||RPAD('-------------',16)
                            ||RPAD('----------',16)
 INTO    &instance,&CUSTOMER
,        &CUST_PO_NUMBER
,        &ORDER_NUMBER 
,        &LINE_NUMBER
,        &ORDERED_ITEM
,        &ORDERED_QUANTITY
,        &SHIP_TO_STORE 
,        &DELIVER_TO 
,        &HEADING
,        &UNDER_LINE 
 FROM    OE_ORDER_LINES_ALL              OOL,
         OE_ORDER_HEADERS_ALL            OOH,
         HZ_CUST_ACCOUNTS_ALL            HCA,
         HZ_PARTIES                      HZP,
         HZ_CUST_SITE_USES_ALL           HCSU,
         HZ_CUST_ACCT_SITES_ALL          HCAS,
         HZ_PARTY_SITES                  HPS,
         HZ_LOCATIONS                    HZL,
         HZ_CUST_ACCOUNTS_ALL            HCA2,
         HZ_PARTIES                      HZP2,
         HZ_CUST_SITE_USES_ALL           HCSU2,
         HZ_CUST_ACCT_SITES_ALL          HCAS2,
         HZ_PARTY_SITES                  HPS2,
         HZ_LOCATIONS                    HZL2
WHERE    OOL.HEADER_ID            =   OOH.HEADER_ID
  AND    HCA.PARTY_ID             =   HZP.PARTY_ID
  AND    HCSU.CUST_ACCT_SITE_ID   =   HCAS.CUST_ACCT_SITE_ID(+)
  AND    HCAS.PARTY_SITE_ID       =   HPS.PARTY_SITE_ID
  AND    HPS.LOCATION_ID          =   HZL.LOCATION_ID
  AND    HCSU.SITE_USE_ID         =   OOL.SHIP_TO_ORG_ID
  AND    OOH.SOLD_TO_ORG_ID       =   HCA.CUST_ACCOUNT_ID
  AND    HCA2.PARTY_ID            =   HZP2.PARTY_ID
  AND    HCSU2.CUST_ACCT_SITE_ID  =   HCAS2.CUST_ACCT_SITE_ID(+)
  AND    HCAS2.PARTY_SITE_ID      =   HPS2.PARTY_SITE_ID
  AND    HPS2.LOCATION_ID         =   HZL2.LOCATION_ID
  AND    HCSU2.SITE_USE_ID        =   NVL(OOL.DELIVER_TO_ORG_ID,OOL.SHIP_TO_ORG_ID)
  AND    OOH.SOLD_TO_ORG_ID       =   HCA2.CUST_ACCOUNT_ID
  AND    OOL.FLOW_STATUS_CODE='ENTERED'
  AND    OOH.FLOW_STATUS_CODE='BOOKED'
  AND    OOH.CREATION_DATE LIKE SYSDATE

2) Define Actions
Click on the actions button and then actions Detail button and define message as shown in screenshot. Note that the message type is summary.

3) Define Action Sets
Click on action sets and then action set details and in the members tab enter the action defined in step 2

4) Schedule the Request
Navigate to Request --> Check and submit the alert. Based on the definition of alert it will be scheduled to run.

order is not eligible for booking check workflow status for this order

Message::order is not eligible for booking check workflow status for this order

Solution::

àrun the scrpit with order number and then run the “work flow process” then book the order

DECLARE
   v_headerid   NUMBER;

   CURSOR cr
   IS
      SELECT oeh.header_id
        FROM oe_order_headers_all oeh
       WHERE oeh.order_number = '791137';                        --order in issue 

   l_org_id     NUMBER := 204;                                        --OU ID get org id form above
BEGIN
   mo_global.set_policy_context ('S', l_org_id);

   FOR rs IN cr
   LOOP
      v_headerid := rs.header_id;
      apps.wf_engine.startprocess ('OEOH', TO_CHAR (v_headerid));
   END LOOP;
END;

-->Run the   ” wokflow background process “

Goto -àsystem administrator -àview requests-àsubmit new request

Prog name( wokflow background process )

Parameters:

Process Deferred:YES
Process Timeout:YES