Query to get PO Requisition, PO and Receipt details in R12
I had to provide data to auditors on the
- Internal & Purchase Requisitions created by users
- Purchase Orders created for the requisitions (inventory and non inventory items)
- Receiving transactions with PO and Requisition information
Purchase Requisition details
SELECT prh.segment1 “Req #”, prh.creation_date, prh.created_by, poh.segment1 “PO #”, ppx.full_name “Requestor Name”,prh.description “Req Description”, prh.authorization_status, prh.note_to_authorizer, prh.type_lookup_code, prl.line_num,prl.line_type_id, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered,prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date,prl.cancel_reason
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
per_people_x ppx,
po_headers_all poh,
po_distributions_all pda
WHERE prh.requisition_header_id = prl.requisition_header_id
AND ppx.person_id = prh.preparer_id
AND prh.type_lookup_code = ‘PURCHASE’
AND prd.requisition_line_id = prl.requisition_line_id
AND pda.req_distribution_id = prd.distribution_id
AND pda.po_header_id = poh.po_header_id
AND TO_CHAR (prh.creation_date, ‘YYYY’) IN (‘2010’, ‘2011’)
Internal Requisition details
SELECT prh.segment1 “Req #”, prh.creation_date, prh.created_by, poh.segment1 “PO #”, ppx.full_name “Requestor Name”,
prh.description “Req Description”, prh.authorization_status, prh.note_to_authorizer, prl.line_num,
prl.line_type_id, prl.source_type_code, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered,
prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date,
prl.cancel_reason
FROM po_requisition_headers_all prh,
po_requisition_lines_all prl,
po_req_distributions_all prd,
per_people_x ppx,
po_headers_all poh,
po_distributions_all pda
WHERE prh.requisition_header_id = prl.requisition_header_id
AND ppx.person_id = prh.preparer_id
AND prh.type_lookup_code = ‘INTERNAL’
AND prd.requisition_line_id = prl.requisition_line_id
AND pda.req_distribution_id (+) = prd.distribution_id
AND pda.po_header_id = poh.po_header_id (+)
AND TO_CHAR (prh.creation_date, ‘YYYY’) IN (‘2010’, ‘2011’)
Purchase Order details
SELECT
ph.SEGMENT1 po_num
, ph.CREATION_DATE
, hou.name “Operating Unit”
, ppx.full_name “Buyer Name”
, ph.type_lookup_code “PO Type”
, plc.displayed_field “PO Status”
, ph.COMMENTS
, pl.line_num
, plt.order_type_lookup_code “Line Type”
, NULL “Item Code”
, pl.item_description
, pl.unit_meas_lookup_code “UOM”
, pl.base_unit_price
, pl.unit_price
, pl.quantity
, ood.organization_code “Shipment Org Code”
, ood.organization_name “Shipment Org Name”
, pv.vendor_name supplier
, pvs.vendor_site_code
, (pl.unit_price * pl.quantity) “Line Amount”
, prh.segment1 req_num
, prh.type_lookup_code req_method
, ppx1.full_name “Requisition requestor”
FROM po_headers_all ph
, po_lines_all pl
, po_distributions_all pda
, po_vendors pv
, po_vendor_sites_all pvs
, po_distributions_all pd
, po_req_distributions_all prd
, po_requisition_lines_all prl
, po_requisition_headers_all prh
, hr_operating_units hou
, per_people_x ppx
, po_line_types_b plt
, org_organization_definitions ood
, per_people_x ppx1
, po_lookup_codes plc
WHERE
1=1
AND TO_CHAR(ph.creation_date, ‘YYYY’) IN (2010, 2011)
AND ph.vendor_id = pv.vendor_id
AND ph.po_header_id = pl.po_header_id
AND ph.vendor_site_id = pvs.vendor_site_id
AND ph.po_header_id = pd.po_header_id
and pl.po_line_id = pd.po_line_id
AND pd.req_distribution_id = prd.distribution_id (+)
AND prd.requisition_line_id = prl.requisition_line_id (+)
AND prl.requisition_header_id = prh.requisition_header_id (+)
and hou.organization_id = ph.org_id
and ph.agent_id = ppx.person_id
and pda.po_header_id = ph.po_header_id
and pda.po_line_id = pl.po_line_id
and pl.line_type_id = plt.line_type_id
and ood.organization_id = pda.destination_organization_id
and ppx1.person_id (+) = prh.preparer_id
and plc.lookup_type = ‘DOCUMENT STATE’
and plc.LOOKUP_CODE = ph.closed_code
and pl.item_id is null
UNION
— Purchase Orders for inventory items
SELECT
ph.SEGMENT1 po_num
, ph.CREATION_DATE
, hou.name “Operating Unit”
, ppx.full_name “Buyer Name”
, ph.type_lookup_code “PO Type”
, plc.displayed_field “PO Status”
, ph.COMMENTS
, pl.line_num
, plt.order_type_lookup_code “Line Type”
, msi.segment1 “Item Code”
, pl.item_description
, pl.unit_meas_lookup_code “UOM”
, pl.base_unit_price
, pl.unit_price
, pl.quantity
, ood.organization_code “Shipment Org Code”
, ood.organization_name “Shipment Org Name”
, pv.vendor_name supplier
, pvs.vendor_site_code
, (pl.unit_price * pl.quantity) “Line Amount”
, prh.segment1 req_num
, prh.type_lookup_code req_method
, ppx1.full_name “Requisition requestor”
FROM po_headers_all ph
, po_lines_all pl
, po_distributions_all pda
, po_vendors pv
, po_vendor_sites_all pvs
, po_distributions_all pd
, po_req_distributions_all prd
, po_requisition_lines_all prl
, po_requisition_headers_all prh
, hr_operating_units hou
, per_people_x ppx
, mtl_system_items_b msi
, po_line_types_b plt
, org_organization_definitions ood
, per_people_x ppx1
, po_lookup_codes plc
WHERE
1=1
AND TO_CHAR(ph.creation_date, ‘YYYY’) IN (2010, 2011)
AND ph.vendor_id = pv.vendor_id
AND ph.po_header_id = pl.po_header_id
AND ph.vendor_site_id = pvs.vendor_site_id
AND ph.po_header_id = pd.po_header_id
and pl.po_line_id = pd.po_line_id
AND pd.req_distribution_id = prd.distribution_id (+)
AND prd.requisition_line_id = prl.requisition_line_id (+)
AND prl.requisition_header_id = prh.requisition_header_id (+)
and hou.organization_id = ph.org_id
and ph.agent_id = ppx.person_id
and pda.po_header_id = ph.po_header_id
and pda.po_line_id = pl.po_line_id
and pl.line_type_id = plt.line_type_id
and ood.organization_id = pda.destination_organization_id
and ppx1.person_id (+) = prh.preparer_id
and pda.destination_organization_id = msi.organization_id (+)
and msi.inventory_item_id = nvl(pl.item_id, msi.inventory_item_id)– OR pl.item_id is null)
and plc.lookup_type = ‘DOCUMENT STATE’
and plc.LOOKUP_CODE = ph.closed_code
and pl.item_id is not null
Receiving transactions with PO and requisition information
SELECT
ph.segment1 po_num,
ood.organization_name,
pol.po_line_id,
pll.quantity,
rsh. receipt_source_code,
rsh. vendor_id,
rsh. vendor_site_id,
rsh. organization_id,
rsh. shipment_num,
rsh. receipt_num,
rsh. ship_to_location_id,
rsh. bill_of_lading,
rsl.shipment_line_id,
rsl.QUANTITY_SHIPPED,
rsl.QUANTITY_RECEIVED ,
rct.transaction_type,
rct.transaction_id,
nvl(rct.source_doc_quantity,0) transaction_qty
from rcv_transactions rct
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
, po_lines_all pol
, po_line_locations_all pll
, po_headers_all ph
, org_organization_definitions ood
where 1=1
and to_char(rct.creation_date, ‘YYYY’) in (‘2010’, ‘2011’)
and rct.po_header_id = ph.po_header_id
and rct.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and rct.shipment_line_id=rsl.shipment_line_id
and rsl.shipment_header_id=rsh.shipment_header_id
and rsh.ship_to_org_id = ood.organization_id
order by rct.transaction_id
When there is one PO against two pr for same item your receiving query is not showing correct result as it is doubling the records….. will u remove this error in ur query…
Hi,
Thanks for your comment.
How many lines your PO have? Tell me you received an item partially or fully?
Happy Learning.