PO Receiving Accounting Tables in Oracle Fusion

 Hi Friends, we are going to discuss about the PO receiving accounting  tables in oracle fusion. If we will compare the PO receipt accounting table with oracle apps , then there is huge difference. Accounting tables have been completely changed in oracle fusion. If you want to find out the PO receiving accounting information in oracle fusion , then you can only find with these tables only. These PO receiving tables helps to make the Join between the PO receiving and xla tables. In Oracle apps , we have RCV_RECEIVING_SUB_LEDGER table which helps to store and keep the PO receipt accounting details but in oracle fusion , there is no as such table. Now in Oracle fusion , we have multiple CMR tables which helps to find out the PO receipt accounting details information in oracle fusion. We are sharing these tables and sql queries from our real time working examples. These PO receiving and accounting tables helps to develop the custom BIP reports as well the data extraction too.

PO Receiving Accounting Tables in Oracle Fusion

6 Important PO Receiving Accounting Tables in Oracle Fusion

1.CMR_TRANSACTION_TAXES

2.CMR_RCV_EVENTS

3.CMR_RCV_EVENT_COSTS

4.CMR_RCV_TRANSACTIONS

5.CMR_RCV_DISTRIBUTIONS

6.CMR_TRANSACTIONS

Important SQL Query using PO receiving accounting tables information in oracle fusion

Here below is the detail sql query which helps to extract the complete PO receipt accounting details in oracle fusion. Please refer this below sql for custom BIP report.

SELECT

             gh.period_name,

             cmrd.po_number,

            poz.vendor_name Supplier_Name,

            cmrt.Receipt_number,

            cmrt.Transaction_date Receipt_Date,

            cmrd.currency_code POCurrency,

            gll.currency_code FuncCurrency,

cmrt.Transaction_Quantity Received_Qty,

           (select Sum(invoice_Qty) from cmr_ap_invoice_dtls where cmr_rcv_transaction_id = cmrt.cmr_rcv_transaction_id) Invoice_Qty,

           NVL((cmrt.Transaction_Quantity),0)-NVL((select Sum(invoice_Qty) from cmr_ap_invoice_dtls where cmr_rcv_transaction_id = cmrt.cmr_rcv_transaction_id),0) Uninvoiced_Qty,

    NVL(xl.Entered_Cr,0) - NVL(xl.Entered_dr,0) Uninvoiced_Amount_POCurrency,

    NVL(xl.Accounted_Cr,0) - NVL(xl.Accounted_dr,0) Uninvoiced_Amount_FuncCurrency

 FROM

gl_ledgers gll,

gl_je_batches gb,

gl_je_headers gh,

gl_je_lines gl,

gl_code_combinations gcc,

gl_import_references gr,

xla_ae_lines xl,

xla_ae_headers xh,

cmr_rcv_events cmre,

cmr_purchase_order_dtls cmrd,

poz_suppliers_v poz,

cmr_rcv_transactions cmrt,

hr_Organization_units hru

WHERE 

           gh.je_header_id = gl.je_header_id

         AND gh.ledger_id = gll.ledger_id

         AND gh.je_batch_id = gb.je_batch_id

         AND gl.code_combination_id = gcc.code_combination_id

         AND gr.je_header_id = gl.je_header_id

         AND gr.je_line_num = gl.je_line_num

         AND xh.ae_header_id = xl.ae_header_id

         AND xl.gl_sl_link_id(+) = gr.gl_sl_link_id

       AND  xl.gl_sl_link_table(+) = gr.gl_sl_link_table

       

       AND  xh.event_id =  cmre.event_id

       

       AND cmrd.cmr_po_distribution_id = cmre.cmr_po_distribution_id

       

       AND poz.vendor_id = cmrd.vendor_id

       AND cmrt.cmr_rcv_transaction_id = cmre.cmr_rcv_transaction_id

       AND xh.gl_transfer_status_code = 'Y'

        AND gb.status = 'P'

Some other Important PO Receiving Queries in Oracle Fusion

select * from fusion.cmr_rcv_event_costs where accounting_event_id in (select accounting_event_id from cmr_rcv_events where cmr_po_distribution_id in (select cmr_po_distribution_id from cmr_purchase_order_dtls where po_number in ()));
select * from cmr_rcv_distributions where accounting_event_id in (select accounting_event_id from cmr_rcv_events where cmr_po_distribution_id in (select cmr_po_distribution_id from cmr_purchase_order_dtls where po_number in ()));
select * from cmr_exp_po_dist_costs where cmr_po_distribution_id in (select cmr_po_distribution_id from cmr_purchase_order_dtls where po_number in ());
select * from cmr_ap_invoice_dtls where cmr_po_distribution_id in (select cmr_po_distribution_id from cmr_purchase_order_dtls where po_number in ());

select * from cmr_purchase_order_dtls where po_number in ();

select * from cmr_rcv_transactions where po_line_location_id in (select po_line_location_id from cmr_purchase_order_dtls where po_number in ());

select * from cmr_transactions where cmr_po_distribution_id in (select cmr_po_distribution_id from cmr_purchase_order_dtls where po_number in ());
select * from fusion.cmr_transaction_taxes where transaction_id in (select transaction_id from cmr_transactions where cmr_po_distribution_id in (select cmr_po_distribution_id from cmr_purchase_order_dtls where po_number in ()));
select * from cmr_rcv_events where cmr_po_distribution_id in (select cmr_po_distribution_id from cmr_purchase_order_dtls where po_number in ());

PO Receiving Accounting Tables in Oracle Fusion