Restrict

Sunday, March 12, 2017

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;