Trace/ Log File Path against of a Concurrent Request ID

 Trace/ Log File Path against of a Concurrent Request ID :

SELECT
    req.request_id
    ,req.logfile_node_name node
    ,req.oracle_Process_id
    ,req.enable_trace
    ,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
    ,prog.user_concurrent_program_name
    ,execname.execution_file_name
    ,execname.subroutine_name
    ,phase_code
    ,status_code
    ,ses.SID
    ,ses.serial#
    ,ses.module
    ,ses.machine
    FROM
    fnd_concurrent_requests req
    ,v$session ses
    ,v$process proc
    ,v$parameter dest
    ,v$parameter dbnm
    ,fnd_concurrent_programs_vl prog
    ,fnd_executables execname
    WHERE 1=1
    AND req.request_id = &request --Request ID
    AND req.oracle_process_id=proc.spid(+)
    AND proc.addr = ses.paddr(+)
    AND dest.NAME='user_dump_dest'
    AND dbnm.NAME='db_name'
    AND req.concurrent_program_id = prog.concurrent_program_id
    AND req.program_application_id = prog.application_id
    AND prog.application_id = execname.application_id
    AND prog.executable_id=execname.executable_id

UOM & UOM Conversion

UOM & UOM Conversion

PO: Stuck PO/PR resolution

SQL For IN PROCESS PO:

SELECT         wf_item_key, wf_item_type, authorization_status,
org_id, last_update_date, segment1 po_no
FROM             po_headers_all
WHERE          authorization_status = ‘IN PROCESS’
ORDER BY    last_update_date;


SQL For IN PROCESS PO Requisition :

SELECT         wf_item_type , wf_item_key, authorization_status,
org_id , last_update_date, segment1 poreq_no
FROM             po_requisition_headers_all
WHERE         authorization_status = ‘IN PROCESS’
ORDER BY   last_update_date;


To remove stuck in process status need to run bellow SQL using APPS SESSION LOGIN
For PO
begin
—  wf_engine.startprocess( ‘WF_ITEM_TYPE’ , ‘WF_ITEM_KEY’ );
wf_engine.startprocess( ‘POAPPRV’  ,  ‘1276980-832203’ );
end;


For PO REQ
begin
—  wf_engine.startprocess( ‘WF_ITEM_TYPE’ , ‘WF_ITEM_KEY’ );
wf_engine.startprocess(‘REQAPPRV’  ,  ‘1276980-832203’ );
end;

SLA Condition check with ADR

SLA Condition check with ADR
---------------------------------------------


SELECT USER_SEQUENCE,A.VALUE_CONSTANT, A.ORGANIZATION_CODE, A.ORGANIZATION_NAME,O1.ORGANIZATION_CODE, O1.ORGANIZATION_NAME,CONDITION_ID, ou.ORGANIZATION_ID ou_id, ou.NAME ou_name,
ou.SHORT_CODE ou_short_name, gcc.segment1, gcc.CONCATENATED_SEGMENTS, a.name, EVENT_CLASS_NAME, EVENT_TYPE_NAME,  JLD_CODE,ACCOUNTING_LINE_CODE,  ADR_NAME, ADR_CODE , A.ADR_DESC
 FROM
(
SELECT seq.USER_SEQUENCE, CTN.VALUE_CONSTANT, o.ORGANIZATION_CODE, o.ORGANIZATION_NAME,CTN.CONDITION_ID, srd.VALUE_CODE_COMBINATION_ID, name, E.EVENT_CLASS_NAME, E.EVENT_TYPE_NAME, C.LINE_DEFINITION_CODE JLD_CODE,C.ACCOUNTING_LINE_CODE, SEGMENT_RULE_NAME ADR_NAME,C.SEGMENT_RULE_CODE ADR_CODE , SEGMENT_RULE_DESC ADR_DESC
FROM xla_seg_rules_b srb,xla_seg_rules_tl srl,fnd_application_tl apl,xla_seg_rule_details srd, xla_conditions ctn,XLA_SEG_RULE_DETAILS seq
, ORG_ORGANIZATION_DEFINITIONS O,xla_line_defn_adr_assgns_f_v c,xla_line_defn_jlt_assgns_f_v D, xla_prod_acct_headers_fvl e
WHERE apl.APPLICATION_ID=555 and apl.APPLICATION_ID=srb.APPLICATION_ID
AND SRB.SEGMENT_RULE_CODE= C.SEGMENT_RULE_CODE AND SRB.SEGMENT_RULE_CODE=  :rule_code
AND SRB.APPLICATION_ID=SRL.APPLICATION_ID AND SRB.SEGMENT_RULE_CODE=SRL.SEGMENT_RULE_CODE
AND  SRB.APPLICATION_ID=SRD.APPLICATION_ID AND SRB.SEGMENT_RULE_CODE=SRD.SEGMENT_RULE_CODE
AND SRD.APPLICATION_ID=CTN.APPLICATION_ID AND SRD.SEGMENT_RULE_DETAIL_ID=CTN.SEGMENT_RULE_DETAIL_ID AND CTN.SOURCE_CODE='ORGANIZATION_CODE'
AND CTN.VALUE_CONSTANT=O.ORGANIZATION_CODE and srb.APPLICATION_ID=seq.APPLICATION_ID and srb.SEGMENT_RULE_CODE=seq.SEGMENT_RULE_CODE and srd.APPLICATION_ID=seq.APPLICATION_ID
AND SRD.SEGMENT_RULE_DETAIL_ID=SEQ.SEGMENT_RULE_DETAIL_ID
AND SEQ.APPLICATION_ID=C.APPLICATION_ID AND SEQ.SEGMENT_RULE_CODE=C.SEGMENT_RULE_CODE AND SEQ.SEGMENT_RULE_TYPE_CODE =C.SEGMENT_RULE_TYPE_CODE
  AND  C.APPLICATION_ID=D.APPLICATION_ID AND c.APPLICATION_ID=555
 AND C.ACCOUNTING_LINE_CODE =D.ACCOUNTING_LINE_CODE AND C.ACCOUNTING_LINE_TYPE_CODE=D.ACCOUNTING_LINE_TYPE_CODE AND C.EVENT_CLASS_CODE=D.EVENT_CLASS_CODE
 AND C.EVENT_TYPE_CODE=D.EVENT_TYPE_CODE AND C.SEGMENT_RULE_OWNER='User'
 AND C.LINE_DEFINITION_OWNER_CODE= D.LINE_DEFINITION_OWNER_CODE
 AND d.APPLICATION_ID=555 and d.APPLICATION_ID=e.APPLICATION_ID and d.EVENT_CLASS_CODE =e.EVENT_CLASS_CODE
 AND d.EVENT_TYPE_CODE=e.EVENT_TYPE_CODE
 and PRODUCT_RULE_CODE='PRG_PROCESS_MFG_FINANCIALS' and C.LINE_DEFINITION_OWNER_CODE='C'
) A,  ORG_ORGANIZATION_DEFINITIONS O1, hr_operating_units ou, gl_code_combinations_kfv gcc
 WHERE A.ORGANIZATION_CODE(+) =O1.ORGANIZATION_CODE and O1.operating_unit=ou.ORGANIZATION_ID and VALUE_CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
 and ou.SHORT_CODE <> gcc.segment1
order by USER_SEQUENCE
 

AR Accounting Error checking SQL for Bank

SELECT distinct a.receipt_method_id, a.NAME receiptmethod, b.remit_bank_acct_use_id,
       c.bank_account_id, d.bank_account_name, d.bank_account_num, h.ENTITY_ID, h.EVENT_ID, h.AE_HEADER_ID, h.AE_LINE_NUM,  h.ENCODED_MSG
  FROM ar_receipt_methods a,
       ar_receipt_method_accounts_all b,
       ce_bank_acct_uses_all c,
       ce_bank_accounts d,
       ar_receipt_classes e,
       ar_cash_receipts_all f,
        ar_receivable_applications_all g,
        xla_accounting_errors h
 WHERE a.receipt_method_id = b.receipt_method_id
   AND b.remit_bank_acct_use_id = c.bank_acct_use_id
   AND c.bank_account_id = d.bank_account_id
   and a.RECEIPT_CLASS_ID=e.RECEIPT_CLASS_ID
   and a.RECEIPT_METHOD_ID=f.RECEIPT_METHOD_ID
   and f.CASH_RECEIPT_ID= g.CASH_RECEIPT_ID
   and g.EVENT_ID=h.EVENT_ID and h.request_id=:request_id