Stage 1: PO Creation :
PO_HEADERS_ALL
select po_header_id
from po_headers_all
where segment1 =<po_number>;
select * from po_headers_all
where po_header_id =<po_header_id>;
po_lines_all
select * from po_lines_all where po_header_id =<po_header_id>;
po_line_locations_all
select * frompo_line_locations_all where po_header_id =<po_header_id>;
po_distributions_all
select * frompo_distributions_all where po_header_id =<po_header_id>;
po_releases_all
SELECT * FROMpo_releases_all WHERE po_header_id =<po_header_id>;
Stage 2: Once PO is received data is moved to respective receving tables and inventory tables
RCV_SHIPMENT_HEADERS
select * fromrcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =<po_header_id>);
RCV_SHIPMENT_LINES
select * fromrcv_shipment_lines where po_header_id =<po_header_id>;
RCV_TRANSACTIONS
select * fromrcv_transactions where po_header_id =<po_header_id>;
RCV_ACCOUNTING_EVENTS
SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>);
RCV_RECEIVING_SUB_LEDGER
select * fromrcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);
RCV_SUB_LEDGER_DETAILS
select * fromrcv_sub_ledger_details
wherercv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);
MTL_MATERIAL_TRANSACTIONS
select * frommtl_material_transactions where transaction_source_id =<po_header_id>;
MTL_TRANSACTION_ACCOUNTS
select * frommtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions wheretransaction_source_id = =<po_header_id>);
Stage 3: Invoicing details
AP_INVOICE_DISTRIBUTIONS_ALL
select * fromap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>);
AP_INVOICES_ALL
select * fromap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all wherepo_distribution_id in
( selectpo_distribution_id from po_distributions_all where po_header_id =<po_header_id>));
Stage 4 : Many Time there is tie up with Project related PO
PA_EXPENDITURE_ITEMS_ALL
select * frompa_expenditure_items_all peia where peia.orig_transaction_reference in
( select to_char(transaction_id) from mtl_material_transactions
wheretransaction_source_id = <po_header_id> );
Stage 5 : General Ledger
GL_BC_PACKETS ..This is for encumbrances
SELECT * FROM gl_bc_packets WHERE reference2 IN ('<po_header_id>');
GL_INTERFACE
SELECT *
FROM GL_INTERFACE GLI
WHEREuser_je_source_name ='Purchasing'
ANDgl_sl_link_table ='RSL'
AND reference21='PO'
AND EXISTS
( SELECT 1
FROMrcv_receiving_sub_ledger RRSL
WHERE GLI.reference22 =RRSL.reference2
AND GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id <po_header_id>));
GL_IMPORT_REFERENCES
SELECT *
FROMgl_import_references GLIR
WHERE reference_1='PO'
ANDgl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROMrcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>))
No comments:
Post a Comment