OM Useful Queries

-- zom
select hou.name ou, mp.organization_code,mp.organization_id,ooha.header_id,ooha.order_number,oola.*
from oe_order_headers_all ooha
    ,oe_order_lines_all oola
    ,hr_operating_units hou
    ,mtl_parameters mp
where 1=1
  and ooha.order_number = 11238463494
  and ooha.org_id = 6941
  and oola.header_id = ooha.header_id
  and ooha.org_id = hou.organization_id
  and oola.ship_from_org_id = mp.organization_id
  and oola.cancelled_flag <> 'Y'
  and oola.flow_status_code not in ('CLOSED','AWAITING_FULFILLMENT','CANCELLED')
  order by 1,2,3
  ;

-- zomwsh
select hou.name ou, mp.organization_code,ooha.header_id,ooha.order_number
,wnd.name delivery,fcr.request_id pick_release_request_id,wdd.request_id ship_confirm_request_id
from oe_order_headers_all ooha
    ,oe_order_lines_all oola
    ,hr_operating_units hou
    ,wsh_delivery_assignments wda
    ,wsh_delivery_details wdd
    ,wsh_new_deliveries wnd
    ,wsh_picking_batches wpb
    ,mtl_parameters mp
    ,fnd_concurrent_requests fcr
where 1=1 and ooha.order_number = 11238463494
  and oola.header_id = ooha.header_id
  and ooha.org_id = hou.organization_id
  and wdd.source_header_id = oola.header_id
  and wdd.source_line_id = oola.line_id
  and wda.delivery_detail_id = wdd.delivery_detail_id
  and wda.delivery_id = wnd.delivery_id
  and wpb.batch_id = wdd.batch_id
  --and wdd.delivery_detail_id = 196027682
  and wdd.organization_id = mp.organization_id
  and fcr.request_id = wpb.request_id
  --and fcr.argument1 = wpb.batch_id
  --and fcr.concurrent_program_id = 40178
  order by 1,2,3
  ;

--zwsh
select wnd.* from wsh_new_deliveries wnd, wsh_delivery_assignments wda, wsh_delivery_details wdd
where 1=1
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
--and wdd.delivery_detail_id = 1245469
and wda.delivery_id = 90431545
;

PO, PR and RCV useful queries

-- zpr
select pha.*
from po_requisition_headers_all prha
, po_requisition_lines_all prla
where 1=1
--and prha.org_id = 6941
and prha.segment1 = '15668'
and prha.requisition_header_id = prla.requisition_header_id
--and prla.quantity <> prla.quantity_delivered
;

-- zprpo
select prha.segment1,prla.*
from po_headers_all pha
, po_lines_all pla
, po_distributions_all pda
, po_req_distributions_all prda
, po_requisition_lines_all prla
, po_requisition_headers_all prha
where 1=1
and pha.org_id = 6941
and pha.segment1 = '6715'
and pha.po_header_id = pla.po_header_id
and pla.po_line_id = pda.po_line_id
and pda.req_distribution_id = prda.distribution_id
and prda.requisition_line_id = prla.requisition_line_id
and prla.requisition_header_id = prha.requisition_header_id
;

-- zpo
select pha.segment1,plla.*
from po_headers_all pha
    ,po_lines_all pla
    --,po_line_locations_all plla
where 1=1 --and pha.po_header_id = 73815567
  and pha.segment1 = '2740'
  and pha.org_id = 6941
  and pha.po_header_id = pla.po_header_id
  and pla.po_header_id = pla.po_header_id
  --and pla.po_line_id = plla.po_line_id
  order by 1,2,3
  ;

--zrcv
select rsh.*,rsl.*
from rcv_shipment_headers rsh
    ,rcv_shipment_lines rsl
where 1=1 --and pha.po_header_id = 73815567
  and rsh.receipt_num in ('11228','12175','11362')
  --and rsh.shipment_num in ('11228','12175','11362')
  and rsh.shipment_header_id = rsl.shipment_header_id
  --order by 1,2,3
  ;

--zporcv 
select pha.segment1,pla.line_num,rt.*
from po_headers_all pha
    ,po_lines_all pla
    --,po_line_locations_all plla
,rcv_shipment_headers rsh
    ,rcv_shipment_lines rsl
    ,rcv_transactions rt
where 1=1 --and pha.po_header_id = 73815567
  and pha.segment1 = '2740'
  and pha.org_id = 6941
  and pha.po_header_id = pla.po_header_id
  and pla.po_header_id = plla.po_header_id
  --and pla.po_line_id = plla.po_line_id
  and rsl.po_line_id = pla.po_line_id
  and rsh.shipment_header_id = rsl.shipment_header_id
  and rsl.shipment_header_id = rt.shipment_header_id
  and rsl.shipment_line_id = rt.shipment_line_id
  order by 1,2,3
  ; 

Find the EBS application url from the database

select * from icx_parameters

Find the application top server path from the database

Select Distinct Variable_Name  "TOP_NAME"
,Value  "PATH"
From Fnd_Env_Context Fec ,
Fnd_Concurrent_Processes Fcp,
fnd_concurrent_requests fcr
Where 1=1
And Fec.Concurrent_Process_Id=Fcp.Concurrent_Process_Id
And Fec.Concurrent_Process_Id=Fcr.Controlling_Manager
And Fec.Variable_Name ='&TOP_NAME'; -- AU_TOP FND_TOP CUSTOM_TOP etc...

Enable the debug profiles for concurrent program or API

For Concurrent Program:

FND: Debug Log Enabled YES 
FND: Debug Log Filename NULL 
FND: Debug Log Level STATEMENT   -- Unexpected, Error, Exception, Event, Procedure, Statement
FND: Debug Log Module %


SELECT log.module , log.message_text message
FROM fnd_log_messages log,
            fnd_log_transaction_context con
WHERE con.transaction_id = < request_id >
AND con.transaction_type = 'REQUEST'
AND con.transaction_context_id = log.transaction_context_id

ORDER BY log.log_sequence;


For API:

1) Execute below before API call:

fnd_global.apps_initialize(fnd_user_id, fnd_resp_id, fnd_appl_id);
fnd_profile.put('AFLOG_ENABLED', 'Y');
fnd_profile.put('AFLOG_MODULE', '%');
fnd_profile.put('AFLOG_LEVEL','1'); -- Level 1 is Statement Level

fnd_log_repository.init;

2) Find the current max log sequence value

SELECT MAX(LOG_SEQUENCE)

FROM FND_LOG_MESSAGES;

3) Call API

4) Execute below after API call:

fnd_global.apps_initialize(fnd_user_id, fnd_resp_id, fnd_appl_id);
fnd_profile.put('AFLOG_ENABLED', 'N');
fnd_profile.put('AFLOG_MODULE', '%');
fnd_profile.put('AFLOG_LEVEL','1'); -- Level 1 is Statement Level

fnd_log_repository.init;

5) Run the below query 

SELECT module, message_text

FROM fnd_log_messages

WHERE log_sequence > &max_log_from_step2

ORDER BY log_sequence;

Query to find the current running sql query of the concurrent program

select hash_value,sql_text
from v$sqltext
where hash_value in (select vs.sql_hash_value
                    from v$session vs
                    where vs.module like '%CONC_PRGO_SHORT_NAME%')
order by hash_value,piece;

Queries to find the DB locked object

select * from v$lock;

select C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE,B.sid,B.SERIAL#,B.STATUS,B.OSUSER,B.MACHINE
from V$LOCKED_OBJECT a
,    V$SESSION B
,    DBA_OBJECTS C
where 1=1
and   b.sid = a.session_id
and   a.OBJECT_ID = C.OBJECT_ID
;
select acc.*
from  DBA_DDL_LOCKS LOCKS
, V$ACCESS ACC
where 1=1
and LOCKS.session_id = ACC.sid
;
select * from  V$ACCESS;


select distinct B.sid,B.SERIAL#--c.object_id--C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE,B.sid,B.SERIAL#,B.STATUS,B.OSUSER,B.MACHINE
from V$LOCKED_OBJECT a
,    V$SESSION B
,    DBA_OBJECTS C
where 1=1
and   b.sid = a.session_id
and   a.OBJECT_ID = C.OBJECT_ID
--and c.object_id in (3384631,3384632)
and c.object_name = 'CONC_PROG_SHORT_NAME';

select acc.*
from  DBA_DDL_LOCKS LOCKS
, V$ACCESS ACC
where 1=1
and LOCKS.session_id = ACC.sid;

select * from  V$ACCESS;

SELECT SID,SERIAL#
FROM V$SESSION
WHERE SID IN (SELECT SESSION_ID
FROM DBA_DML_LOCKS
WHERE NAME = 'CONC_PROG_SHORT_NAME');

ALTER SYSTEM KILL SESSION 'SID,SERIALl#';
ALTER SYSTEM KILL SESSION '6247,5179';