SQL queries to get Requisition, PO and PO Receipt details

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
-- Purchase Orders for non inventory items like service
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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

 

Hope these queries help.

posted @ 2013-04-03 13:46  PPTNotes  阅读(912)  评论(0)    收藏  举报