Purchase 2 Pay Query

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 * from po_line_locations_all where po_header_id =<po_header_id>;
po_distributions_all
select * from po_distributions_all where po_header_id =<po_header_id>;
po_releases_all
SELECT * FROM po_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 * from rcv_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 * from rcv_shipment_lines where po_header_id =<po_header_id>;
RCV_TRANSACTIONS
select * from rcv_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 * from rcv_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 * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);
MTL_MATERIAL_TRANSACTIONS
select * from mtl_material_transactions where transaction_source_id =<po_header_id>;
MTL_TRANSACTION_ACCOUNTS
select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =<po_header_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 =<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 =<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 = <po_header_id> );
Stage 5 : General Ledger
Prompt 17. 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
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.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 *
FROM gl_import_references GLIR
WHERE reference_1='PO'
AND gl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROM rcv_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>))

Use of different Purchase order types

• Standard Purchase order: This type of PO is used when you know the Item, Price, Delivery Schedule and payment terms. Most of the time Standard PO is used to fulfill sporadic demands or say demand coming once or twice a year. In this type of PO you are committing a purchase of item/s with particular quantity and particular price at particular shipment schedule.

For example

• Purchasing for any specific event happening in Company
• Where purchasing item/s is one time job.
• Planned Purchase Order (PPO): This type of PO is used when you are not sure about the exact delivery schedules but other details are quite clear (like Item, Quantity, Price, approximate Delivery Schedule and Payment Term). For PPO Need-By-Date has to be entered, but this date will be treated as tentative date only. Once you are sure about the delivery schedule you create releases against this PPO with detailed delivery schedule. In this type of PO you are committing a purchase of item/s with particular quantity and particular price but with tentative shipment schedule. When you make a release, you are committing the delivery also.

For example

• You need 1200 notebooks yearly, so you can raise PPO with quantity 1200 and in shipment details you can have shipment schedule as per your need (Say 12 shipments with 100 quantity each). This will be tentative schedule, you need to generate a release as and when you need the good and supplier will provide you material.
• Blanket Purchase Order: This kind of PO is used when are not sure about quantity, price, delivery schedule. As soon as you select PO type as Blanket Purchase Agreement the fields for quantity  gets disabled. Blanket PO can be based on max agreed amount. Exact quantity Delivery Schedule and price will be informed to supplier by creating Blanket releases against blanket PO. You can have different ‘Price Breaks’ and specify the quantity / discount / effectively details. In this type of PO you are not committing your supplier at the time of creating PO, all the commitments are done when release is sent.

For Example:

• A car manufacturer needs dashboard for each vehicle and it is purchased from selective suppliers only. But demand for dashboard is not clear. In this case Blanket PO is used and whenever demand comes, releases are sent to supplier.
• Contract Purchase Order: This type of PO is used when you are not sure even about the item which need to be purchased J. The only information that you provide in a Contract PO is supplier, supplier site, payment terms and agreement control details (header part only). Standard PO are created by referring the Contract PO when some thing is to be purchased against the Contract PO from that supplier.

For example:

• You need to import many items to run your business, but you don’t have Import/Export license. In this case you create Contract PO with supplier who has Import/Export license and whenever you need something to be imported, you generate standard PO referring the Contract PO for that Item/s.

Out of above 4 types you can add only Blanket and Contract POs in Approved Supplier List (ASL).