Query to Link between Order Management and Account Receivables

On request I continue to write further on Post Relation between AR Invoice and Sales Order tables and based on the standard assumption, provide a query that can be used to link Invoice and Sales Order.

Query below can be handy

SELECT ooha.order_number
     , oola.line_number so_line_number
     , oola.ordered_item
     , oola.ordered_quantity * oola.unit_selling_price so_extended_price
     , rcta.trx_number invoice_number
     , rcta.trx_date
     , rctla.line_number inv_line_number
     , rctla.unit_selling_price inv_unit_selling_price
FROM   oe_order_headers_all ooha
     , oe_order_lines_all oola
     , ra_customer_trx_all rcta
     , ra_customer_trx_lines_all rctla
WHERE  ooha.header_id = oola.header_id
AND    rcta.customer_trx_id = rctla.customer_trx_id
AND    rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND    rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND    order_number = :p_order_number

Keywords: Sales Order, AR, OM

Query to find locked objects in Oracle

Sometimes program takes a little longer time then expected, one of the reason to this could be that the table/object you are trying to manipulate is locked by other program and hence it is waiting for the resource to be released.
Below query can be handy to find the objects that are locked

SELECT   c.owner
       , c.object_name
       , c.object_type
       , b.SID
       , b.serial#
       , b.status
       , b.osuser
       , b.machine
       , b.program
       , b.module
       , b.action
FROM     v$locked_object a
       , v$session b
       , dba_objects c
WHERE    b.SID = a.session_id 
AND a.object_id = c.object_id
ORDER BY module

If you want to forcefully kill any session then it can be done using

alter system kill session 'sid,serial#'

e.g. 
altery system kill session '123,5325'

Restart or Bounce Apache Server

Following command can be used to bounce apache. An apache bounce is needed to reflect any changes in the self service pages.

To Stop

$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop

To Start

$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start