Tuesday, October 9, 2012

To debug for a PO



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 receiving 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: Many Time 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 GLIWHERE user_je_source_name =’Purchasing’ AND gl_sl_link_table =’RSL’AND reference21=’PO’AND EXISTS (SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLI.reference22 =RRSL.reference2AND 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 GLIRWHERE reference_1=’PO’AND gl_sl_link_table =’RSL’AND EXISTS (SELECT 1FROM rcv_receiving_sub_ledger RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3 =RRSL.reference3AND GLIR.reference_4 =RRSL.reference4AND RRSL.rcv_transaction_id in(select transaction_id from rcv_transactions where po_header_id =))

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect