Apps Technical

SQL queries to get Requisition | PO and PO Receipt details

By Jag - August 24, 2012
I had to provide data to auditors on the
  1. Internal & Purchase Requisitions created by users
  2. Purchase Orders created for the requisitions (inventory and non inventory items)
  3. Receiving transactions with PO and Requisition information
I have given the queries here as could be useful. All data has been taken for years 2010 and 2011. You can change it as per your requirement.

 Purchase Requisition details
01SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by, poh.segment1 "PO #", ppx.full_name "Requestor Name",
02       prh.description "Req Description", prh.authorization_status, prh.note_to_authorizer, prh.type_lookup_code, prl.line_num,
03       prl.line_type_id, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered,
04       prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date,
05       prl.cancel_reason
06  FROM po_requisition_headers_all prh,
07       po_requisition_lines_all prl,
08       po_req_distributions_all prd,
09       per_people_x ppx,
10       po_headers_all poh,
11       po_distributions_all pda
12 WHERE prh.requisition_header_id = prl.requisition_header_id
13   AND ppx.person_id = prh.preparer_id
14   AND prh.type_lookup_code = 'PURCHASE'
15   AND prd.requisition_line_id = prl.requisition_line_id
16   AND pda.req_distribution_id = prd.distribution_id
17   AND pda.po_header_id = poh.po_header_id
18   AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')

 Internal Requisition details
01SELECT prh.segment1 "Req #", prh.creation_date, prh.created_by, poh.segment1 "PO #", ppx.full_name "Requestor Name",
02       prh.description "Req Description", prh.authorization_status, prh.note_to_authorizer, prl.line_num,
03       prl.line_type_id, prl.source_type_code, prl.item_description, prl.unit_meas_lookup_code, prl.unit_price, prl.quantity, prl.quantity_delivered,
04       prl.need_by_date, prl.note_to_agent, prl.currency_code, prl.rate_type, prl.rate_date, prl.quantity_cancelled, prl.cancel_date,
05       prl.cancel_reason
06  FROM po_requisition_headers_all prh,
07       po_requisition_lines_all prl,
08       po_req_distributions_all prd,
09       per_people_x ppx,
10       po_headers_all poh,
11       po_distributions_all pda
12 WHERE prh.requisition_header_id = prl.requisition_header_id
13   AND ppx.person_id = prh.preparer_id
14   AND prh.type_lookup_code = 'INTERNAL'
15   AND prd.requisition_line_id = prl.requisition_line_id
16   AND pda.req_distribution_id (+) = prd.distribution_id
17   AND pda.po_header_id = poh.po_header_id (+)
18   AND TO_CHAR (prh.creation_date, 'YYYY') IN ('2010', '2011')

 Purchase Order details
001-- Purchase Orders for non inventory items like service
002SELECT
003    ph.SEGMENT1 po_num
004  , ph.CREATION_DATE
005  , hou.name  "Operating Unit"
006  , ppx.full_name "Buyer Name"
007  , ph.type_lookup_code "PO Type"
008  , plc.displayed_field "PO Status"
009  , ph.COMMENTS
010  , pl.line_num
011  , plt.order_type_lookup_code "Line Type"
012  , NULL "Item Code"
013  , pl.item_description
014  , pl.unit_meas_lookup_code "UOM"
015  , pl.base_unit_price
016  , pl.unit_price
017  , pl.quantity
018  , ood.organization_code "Shipment Org Code"
019  , ood.organization_name "Shipment Org Name"
020  , pv.vendor_name supplier
021  , pvs.vendor_site_code
022  , (pl.unit_price * pl.quantity) "Line Amount"
023  , prh.segment1 req_num
024  , prh.type_lookup_code req_method
025  , ppx1.full_name "Requisition requestor"
026FROM  po_headers_all ph
027    , po_lines_all pl
028    , po_distributions_all pda
029    , po_vendors pv
030    , po_vendor_sites_all pvs
031    , po_distributions_all pd
032    , po_req_distributions_all prd
033    , po_requisition_lines_all prl
034    , po_requisition_headers_all prh
035    , hr_operating_units hou
036    , per_people_x ppx
037    , po_line_types_b plt
038    , org_organization_definitions ood
039    , per_people_x ppx1
040    , po_lookup_codes plc
041WHERE
042  1=1
043  AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011)
044  AND ph.vendor_id = pv.vendor_id
045  AND ph.po_header_id = pl.po_header_id
046  AND ph.vendor_site_id = pvs.vendor_site_id
047  AND ph.po_header_id = pd.po_header_id
048  and pl.po_line_id = pd.po_line_id
049  AND pd.req_distribution_id = prd.distribution_id (+)
050  AND prd.requisition_line_id = prl.requisition_line_id (+)
051  AND prl.requisition_header_id = prh.requisition_header_id (+)
052 and hou.organization_id = ph.org_id
053 and ph.agent_id = ppx.person_id
054 and pda.po_header_id = ph.po_header_id
055 and pda.po_line_id = pl.po_line_id
056 and pl.line_type_id = plt.line_type_id
057 and ood.organization_id = pda.destination_organization_id
058 and ppx1.person_id (+) = prh.preparer_id
059 and plc.lookup_type = 'DOCUMENT STATE'
060 and plc.LOOKUP_CODE = ph.closed_code
061 and pl.item_id is null
062UNION
063-- Purchase Orders for inventory items
064SELECT
065    ph.SEGMENT1 po_num
066  , ph.CREATION_DATE
067  , hou.name  "Operating Unit"
068  , ppx.full_name "Buyer Name"
069  , ph.type_lookup_code "PO Type"
070  , plc.displayed_field "PO Status"
071  , ph.COMMENTS
072  , pl.line_num
073  , plt.order_type_lookup_code "Line Type"
074  , msi.segment1 "Item Code"
075  , pl.item_description
076  , pl.unit_meas_lookup_code "UOM"
077  , pl.base_unit_price
078  , pl.unit_price
079  , pl.quantity
080  , ood.organization_code "Shipment Org Code"
081  , ood.organization_name "Shipment Org Name"
082  , pv.vendor_name supplier
083  , pvs.vendor_site_code
084  , (pl.unit_price * pl.quantity) "Line Amount"
085  , prh.segment1 req_num
086  , prh.type_lookup_code req_method
087  , ppx1.full_name "Requisition requestor"
088FROM  po_headers_all ph
089    , po_lines_all pl
090    , po_distributions_all pda
091    , po_vendors pv
092    , po_vendor_sites_all pvs
093    , po_distributions_all pd
094    , po_req_distributions_all prd
095    , po_requisition_lines_all prl
096    , po_requisition_headers_all prh
097    , hr_operating_units hou
098    , per_people_x ppx
099    , mtl_system_items_b msi
100    , po_line_types_b plt
101    , org_organization_definitions ood
102    , per_people_x ppx1
103    , po_lookup_codes plc
104WHERE
105  1=1
106  AND TO_CHAR(ph.creation_date, 'YYYY') IN (2010, 2011)
107  AND ph.vendor_id = pv.vendor_id
108  AND ph.po_header_id = pl.po_header_id
109  AND ph.vendor_site_id = pvs.vendor_site_id
110  AND ph.po_header_id = pd.po_header_id
111  and pl.po_line_id = pd.po_line_id
112  AND pd.req_distribution_id = prd.distribution_id (+)
113  AND prd.requisition_line_id = prl.requisition_line_id (+)
114  AND prl.requisition_header_id = prh.requisition_header_id (+)
115 and hou.organization_id = ph.org_id
116 and ph.agent_id = ppx.person_id
117 and pda.po_header_id = ph.po_header_id
118 and pda.po_line_id = pl.po_line_id
119 and pl.line_type_id = plt.line_type_id
120 and ood.organization_id = pda.destination_organization_id
121 and ppx1.person_id (+) = prh.preparer_id
122 and pda.destination_organization_id = msi.organization_id (+)
123 and msi.inventory_item_id = nvl(pl.item_id, msi.inventory_item_id)-- OR pl.item_id is null)
124 and plc.lookup_type = 'DOCUMENT STATE'
125 and plc.LOOKUP_CODE = ph.closed_code
126 and pl.item_id is not null

 Receiving transactions with PO and requisition information
01SELECT
02ph.segment1 po_num,
03ood.organization_name,
04pol.po_line_id,
05pll.quantity,
06rsh. receipt_source_code,
07rsh. vendor_id,
08rsh. vendor_site_id,
09rsh. organization_id,
10rsh. shipment_num,
11rsh. receipt_num,
12rsh. ship_to_location_id,
13rsh. bill_of_lading,
14rsl.shipment_line_id,
15rsl.QUANTITY_SHIPPED,
16rsl.QUANTITY_RECEIVED ,
17rct.transaction_type,
18rct.transaction_id,
19nvl(rct.source_doc_quantity,0) transaction_qty
20from rcv_transactions rct
21, rcv_shipment_headers rsh
22, rcv_shipment_lines rsl
23, po_lines_all pol
24, po_line_locations_all pll
25, po_headers_all ph
26, org_organization_definitions ood
27where 1=1
28and to_char(rct.creation_date, 'YYYY') in ('2010', '2011')
29and rct.po_header_id = ph.po_header_id
30and rct.po_line_location_id = pll.line_location_id
31and rct.po_line_id = pol.po_line_id
32and rct.shipment_line_id=rsl.shipment_line_id
33and rsl.shipment_header_id=rsh.shipment_header_id
34and rsh.ship_to_org_id = ood.organization_id
35order by rct.transaction_id

 Hope these queries help.
  • Share:

You Might Also Like

0 comments