Oracle Purchasing – SQLs to debug / investigate PO issues


Get po_header_id first and run each query and then analyze the data.
For better understanding this is splited into 5 major stages.
Stage 1: PO Creation
PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =;
select * from po_headers_all where po_header_id =;
PO_LINES_ALL
select * from po_lines_all where po_header_id =;
PO_LINE_LOCATIONS_ALL
select * from po_line_locations_all where po_header_id =;
PO_DISTRIBUTIONS_ALL
select * from po_distributions_all where po_header_id =;
PO_RELEASES_ALL
SELECT * FROM po_releases_all WHERE po_header_id =;

Stage 2: Once PO is received, data is moved to respective receving tables and inventory tables
RCV_SHIPMENT_HEADERS
select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lineswhere po_header_id =);
RCV_SHIPMENT_LINES
select * from rcv_shipment_lines where po_header_id =;
RCV_TRANSACTIONS
select * from rcv_transactions where po_header_id =;
RCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactionswhere po_header_id =);
RCV_RECEIVING_SUB_LEDGER
select * from rcv_receiving_sub_ledger where rcv_transaction_id IN
(select transaction_id from rcv_transactions where po_header_id =);
RCV_SUB_LEDGER_DETAILS
select * from rcv_sub_ledger_detailswhere rcv_transaction_id IN
(select transaction_id from rcv_transactions where po_header_id =);
MTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions where transaction_source_id =;
MTL_TRANSACTION_ACCOUNTS
select * from mtl_transaction_accounts where transaction_id IN
(select transaction_id from mtl_material_transactions where transaction_source_id =);

Stage 3: Invoicing details
AP_INVOICE_DISTRIBUTIONS_ALL
select * from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =);
AP_INVOICES_ALL
select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =));

Stage 4 : Mostly there is tie up with Project related PO
PA_EXPENDITURE_ITEMS_ALL
select * from pa_expenditure_items_all peia where peia.orig_transaction_reference IN
(select to_char(transaction_id) from mtl_material_transactions where transaction_source_id = );

Stage 5 : General Ledger
Prompt 17. GL_BC_PACKETS ..This is for encumbrances
SELECT * FROM gl_bc_packets WHERE reference2 IN (’‘);
GL_INTERFACE
select * from gl_interface gli where user_je_source_name =’Purchasing’
and gl_sl_link_table =’RSL’ and reference21=’PO’ and exists
(select 1 from rcv_receiving_sub_ledger rrsl where gli.reference22 =RRSL.reference2 and GLI.reference23 =RRSL.reference3AND GLI.reference24 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactionswhere po_header_id ));
GL_IMPORT_REFERENCES
SELECT *FROM gl_import_references GLIR WHERE reference_1=’PO’ AND gl_sl_link_table =’RSL’AND EXISTS
( SELECT 1 FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3 AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions where po_header_id =))

 

Advertisements

One thought on “Oracle Purchasing – SQLs to debug / investigate PO issues

Want to give some comment to author ( Shivmohan Purohit )

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s