To find the Transaction details based on Create accounting (Warning) program output event number

To find the payment and bank details based on Create accounting (Warning) program output event number:

=======================================================================                                                             Create accounting program goes in "warning" while extracting output file of create accounting request ,it does not provide any details of payment related even in detail mode as well as summary mode where as only the event number and event type reflects in the output report .

The accounting for payment will be generally in the partial status.

We can find the payment details by using below query ; 

select distinct aca.check_number

,cba.bank_account_name

,cba.bank_account_num

,apha.accounting_date

,apha.posted_flag

,apha.org_id

,gcc1.concatenated_segments cash

,gcc2.concatenated_segments cash_clearing

,gcc3.concatenated_segments bank_charge

,gcc4.concatenated_segments bank_error

from

ce_bank_accounts cba,

ap_checks_all aca,

ap_payment_history_all apha,

ap_payment_hist_dists aphd,

xla_distribution_links xdl,

xla_transaction_entities xte,

xla_events xe,

gl_code_combinations_kfv gcc1,

gl_code_combinations_kfv gcc2,

gl_code_combinations_kfv gcc3,

gl_code_combinations_kfv gcc4

where 1=1

and cba.asset_code_combination_id = gcc1.code_combination_id

and cba.cash_clearing_ccid = gcc2.code_combination_id

and cba.bank_charges_ccid = gcc3.code_combination_id

and cba.bank_errors_ccid = gcc4.code_combination_id

and cba.bank_account_id =aca.bank_account_id

and aca.check_id = apha.check_id

and apha.payment_history_id = aphd.payment_history_id

and aphd.payment_hist_dist_id = xdl.source_distribution_id_num_1

and xdl.event_id = xe.event_id

and xdl.source_distribution_type = 'AP_PMT_DIST'

and xe.event_type_code = 'PAYMENT CLEARING ADJUSTED' ---provide the event type based on the output file of create accounting 

and xe.event_status_code = 'U'

and xe.process_status_code = 'I'

and xe.event_number =  XXX ; --provide the event number based on the output file of create accounting

To find the PO and receipt details based on Create accounting-Cost Management (Warning) program output event number:

====================================================================

                                                                     Create accounting cost management program goes in "warning" while extracting output file of create accounting request ,it does not provide any details of PO related even in detail mode as well as summary mode where as only the event number and event type reflects in the output report .

We can find the PO details by using below query ; 

select  distinct pha.segment1 po_num,rsh.receipt_num,pv.vendor_name,haou.name org_name,hou.name ou from

xla_events xe,

xla_distribution_links xdl,

rcv_receiving_sub_ledger rrsl,

po_distributions_all pda,

po_headers_all pha,

rcv_transactions rt,

rcv_shipment_headers  rsh,

hr_operating_units hou,

hr_all_organization_units haou,

po_vendors pv

where 1=1

and xe.event_id = xdl.event_id

and xdl.source_distribution_id_num_1 = rrsl.rcv_sub_ledger_id

and rrsl.reference3 = pda.po_distribution_id

and pda.po_header_id = pha.po_header_id

and rrsl.rcv_transaction_id = rt.transaction_id

and rt.shipment_header_id = rsh.shipment_header_id

and hou.organization_id = pha.org_id

and rsh.ship_to_org_id = haou.organization_id

and pv.vendor_id = rsh.vendor_id

--and pv.vendor_site_id = rsh.vendor_site_id

and xe.event_status_code = 'U' 

and xe.process_status_code = 'I' ---for error transaction else remove 

and xe.event_type_code = 'DELIVER_EXPENSE' --provide the event type based on the output file of create accounting 

and xe.event_number = 1 ;--provide the event number based on the output file of create accounting  

If your know the PO details then we could find the accounting details using below query :

Provide PO Number to the following query. 

 SELECT PO_HEADER_ID
   FROM PO_HEADERS_ALL
  WHERE SEGMENT1 ='&PO_NUMBER';

SELECT *
FROM   XLA_DISTRIBUTION_LINKS XDL
WHERE  XDL.SOURCE_DISTRIBUTION_ID_NUM_1 IN
       (SELECT TO_CHAR(RRSL.RCV_SUB_LEDGER_ID)
       FROM    RCV_RECEIVING_SUB_LEDGER RRSL
       WHERE   RRSL.REFERENCE3 IN
               (SELECT TO_CHAR(PO_DISTRIBUTION_ID)
               FROM    PO_DISTRIBUTIONS_ALL
               WHERE   PO_HEADER_ID = 4064913) --&PO_HEADER_ID
       )
AND    SOURCE_DISTRIBUTION_TYPE = 'RCV_RECEIVING_SUB_LEDGER';
AND    APPLICATION_ID           = 707;



SELECT *
FROM   XLA_DISTRIBUTION_LINKS XDL
WHERE  XDL.SOURCE_DISTRIBUTION_ID_NUM_1 IN
       ( SELECT aida.invoice_distribution_id
       FROM    AP_INVOICE_DISTRIBUTIONS_ALL aida
       WHERE   aida.PO_DISTRIBUTION_ID IN
               (SELECT pod.PO_DISTRIBUTION_ID
               FROM    PO_DISTRIBUTIONS_ALL pod
               WHERE   pod.PO_HEADER_ID = &PO_HEADER_ID)
       )
AND    APPLICATION_ID = 200;