Oracle Purchasing Handbook of queries
1. List of internal requisitions and which are not associated with sales orders.
SELECT reqh.segment1 req_number,
reql.line_num,
reql.requisition_header_id ,
reql.requisition_line_id,
reql.item_id ,
reql.unit_meas_lookup_code ,
reql.unit_price ,
reql.quantity ,
reql.quantity_cancelled,
reql.quantity_delivered ,
reql.cancel_flag ,
reql.source_type_code ,
reql.source_organization_id ,
reql.destination_organization_id,
reqh.transferred_to_oe_flag
FROM po_requisition_lines_all reql,
po_requisition_headers_all reqh
WHERE reql.requisition_header_id = reqh.requisition_header_id
AND reql.source_type_code = 'INVENTORY'
AND reql.source_organization_id IS NOT NULL
AND NOT EXISTS (SELECT 'internal order'
FROM oe_order_lines_all ol
WHERE ol.source_document_line_id = reql.requisition_line_id
AND ol.source_document_type_id = 10)
2. List of Purchase Orders with Requisition Numbers
--List of PO's with requisitions
SELECT r.segment1 "Req No",
p.segment1 "PO No",
p.po_header_id,
p.vendor_id,
p.vendor_site_id,
p.vendor_contact_id,
p.ship_to_location_id,
p.bill_to_location_id,
d.quantity_ordered,
d.set_of_books_id,
d.code_combination_id,
d.po_release_id,
d.quantity_delivered,
d.quantity_billed,
d.amount_billed,
d.destination_type_code
FROM po_headers_all p,
po_distributions_all d,
po_req_distributions_all rd,
po_requisition_lines_all rl,
po_requisition_headers_all r
WHERE p.po_header_id = d.po_header_id
AND d.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = rl.requisition_line_id
For more information about Purchase Orders & requisitions:
SELECT DISTINCT u.description "Requestor",
porh.segment1 as "Req Number",
TRUNC(porh.Creation_Date) "Created On",
pord.LAST_UPDATED_BY,
porh.Authorization_Status "Status",
porh.Description "Description",
poh.segment1 "PO Number",
TRUNC(poh.Creation_date) "PO Creation Date",
poh.AUTHORIZATION_STATUS "PO Status",
TRUNC(poh.Approved_Date) "Approved Date"
FROM apps.po_headers_all poh,
apps.po_distributions_all pod,
apps.po_req_distributions_all pord,
apps.po_requisition_lines_all porl,
apps.po_requisition_headers_all porh,
apps.fnd_user u
WHERE porh.requisition_header_id = porl.requisition_header_id
AND porl.requisition_line_id = pord.requisition_line_id
AND pord.distribution_id = pod.req_distribution_id(+)
AND pod.po_header_id = poh.po_header_id(+)
AND porh.created_by = u.user_id
3. List of Cancelled Requisitions
SELECT prh.requisition_header_id,
prh.preparer_id ,
prh.segment1 "REQ No",
TRUNC(prh.creation_date),
prh.description,
prh.note_to_authorizer
FROM apps.po_requisition_headers_all prh,
apps.po_action_history pah
WHERE action_code='CANCEL'
AND pah.object_type_code='REQUISITION'
AND pah.object_id=prh.requisition_header_id
-- req without PO
SELECT prh.segment1 "PR NUM",
trunc(prh.creation_date) "CREATED ON",
trunc(prl.creation_date) "Line Creation Date" ,
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
TRUNC(prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
FROM po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(SELECT DISTINCT agent_id,agent_name FROM apps.po_agents_v ) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
po.po_line_locations_all pll,
po.po_lines_all pl,
po.po_headers_all ph
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.requisition_line_id = prd.requisition_line_id
AND ppf1.person_id = prh.preparer_id
AND prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
AND ppf2.agent_id(+) = msi.buyer_id
AND msi.inventory_item_id = prl.item_id
AND msi.organization_id = prl.destination_organization_id
AND pll.line_location_id(+) = prl.line_location_id
AND pll.po_header_id = ph.po_header_id(+)
AND pll.po_line_id = pl.po_line_id(+)
AND prh.authorization_status = 'APPROVED'
AND pll.line_location_id IS NULL
AND prl.closed_code IS NULL
4. PO Approval Tables:
PO_APPROVAL_LIST_HEADERS,
PO_APPROVAL_LIST_LINES
5. List of Open Purchase Orders:
SELECT h.segment1 "PO NUMBER",
h.authorization_status "STATUS",
l.line_num "SEQ NUM",
ll.line_location_id,
d.po_distribution_id ,
h.type_lookup_code "TYPE"
FROM po.po_headers_all h,
po.po_lines_all l,
po.po_line_locations_all ll,
po.po_distributions_all d
WHERE h.po_header_id = l.po_header_id
AND ll.po_line_id = l.po_Line_id
AND ll.line_location_id = d.line_location_id
AND h.closed_date is null
6. There are different authorization_status can a
requisition have.
· Approved
· Cancelled
· In Process
· Incomplete
· Pre-Approved
· Rejected
When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it's closed_code becomes 'FINALLY CLOSED'.
PO Flow fromdata Base perspective from PO à Receiving à Inventory à AP à GL
Step1: PO Creation :
SELECT po_header_id FROM po_headers_all WHERE segment1 =<po_number>;
SELECT * FROM po_headers_all WHERE po_header_id =<po_header_id>;
SELECT * FROM po_lines_all WHERE po_header_id =<po_header_id>;
SELECT * FROM po_line_locations_all WHERE po_header_id =<po_header_id>;
SELECT * FROM po_distributions_all WHERE po_header_id =<po_header_id>;
SELECT * FROM po_releases_all WHERE po_header_id =<po_header_id>;
Step 2: PO Receiving and inventory updations
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>);
SELECT * FROM rcv_shipment_lines WHERE po_header_id =<po_header_id>;
SELECT * FROM rcv_transactions WHERE po_header_id =<po_header_id>;
SELECT * FROM rcv_Accounting_Events
WHERE rcv_transaction_id IN (SELECT transaction_id
FROM rcv_transactions WHERE po_header_id =<po_header_id>);
SELECT * FROM rcv_receiving_sub_ledger
WHERE rcv_transaction_id IN (SELECT transaction_id
FROM rcv_transactions WHERE po_header_id =<po_header_id>);
SELECT *
FROM rcv_sub_ledger_details
WHERE rcv_transaction_id IN (SELECT transaction_id
FROM rcv_transactions WHERE po_header_id =<po_header_id>);
SELECT * FROM mtl_material_transactions WHERE transaction_source_id =<po_header_id>;
SELECT * FROM mtl_transaction_accounts
WHERE transaction_id IN ( SELECT transaction_id
FROM mtl_material_transactions
WHERE transaction_source_id = =<po_header_id>);
Step 3: Payables Invoicing
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>);
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>));
Step 4 : General Ledger
SELECT * FROM gl_bc_packets WHERE reference2 IN ('
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>));
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>))