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)

ORDER BY reqh.requisition_header_id, reql.line_num;

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

AND rl.requisition_header_id = r.requisition_header_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

ORDER BY 2


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

AND NVL(prl.cancel_flag,'N') <> 'Y'

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

AND h.type_lookup_code not in ('QUOTATION')

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>))

------------------