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
====================================================
-- 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)
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
Cancel Requisitions
====================================================
SELECT prh.requisition_header_id, prh.preparer_id, prh.segment1 "REQ NUM",
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
Internal Requisitions that do not have an associated Internal Sales Order
====================================================
SELECT rqh.segment1, rql.line_num, rql.requisition_header_id,
rql.requisition_line_id, rql.item_id, rql.unit_meas_lookup_code,
rql.unit_price, rql.quantity, rql.quantity_cancelled,
rql.quantity_delivered, rql.cancel_flag, rql.source_type_code,
rql.source_organization_id, rql.destination_organization_id,
rqh.transferred_to_oe_flag
FROM po_requisition_lines_all rql, po_requisition_headers_all rqh
WHERE rql.requisition_header_id = rqh.requisition_header_id
AND rql.source_type_code = 'INVENTORY'
AND rql.source_organization_id IS NOT NULL
AND NOT EXISTS (
SELECT 'existing internal order'
FROM oe_order_lines_all lin
WHERE lin.source_document_line_id = rql.requisition_line_id
AND lin.source_document_type_id = 10)
ORDER BY rqh.requisition_header_id, rql.line_num
Relation with Requisition and PO
====================================================
SELECT r.segment1 "Req Num", p.segment1 "PO Num"
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
Purchase Requisition without a Purchase Order
====================================================
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.pl_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'
ORDER BY 1, 2
Requisition moved from different stages till converting into PR
====================================================
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
PO’s which does not have any PR
====================================================
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'
ORDER BY 1, 2
All Open Purchase Orders
====================================================
SELECT h.segment1 "PO NUM", 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')
All POs with Approval, Invoice & Payment details
====================================================
SELECT a.org_id "ORG ID", e.segment1 "VENDOR NUM",
e.vendor_name "SUPPLIER NAME",
UPPER (e.vendor_type_lookup_code) "VENDOR TYPE",
f.vendor_site_code "VENDOR SITE CODE", f.address_line1 "ADDRESS",
f.city "CITY", f.country "COUNTRY",
TO_CHAR (TRUNC (d.creation_date)) "PO Date", d.segment1 "PO NUM",
d.type_lookup_code "PO Type", c.quantity_ordered "QTY ORDERED",
c.quantity_cancelled "QTY CANCELLED", g.item_id "ITEM ID",
g.item_description "ITEM DESCRIPTION", g.unit_price "UNIT PRICE",
(NVL (c.quantity_ordered, 0) - NVL (c.quantity_cancelled, 0)
)
* NVL (g.unit_price, 0) "PO Line Amount",
(SELECT DECODE (ph.approved_flag, 'Y', 'Approved')
FROM po.po_headers_all ph
WHERE ph.po_header_id = d.po_header_id) "PO Approved?",
a.invoice_type_lookup_code "INVOICE TYPE",
a.invoice_amount "INVOICE AMOUNT",
TO_CHAR (TRUNC (a.invoice_date)) "INVOICE DATE",
a.invoice_num "INVOICE NUMBER",
(SELECT DECODE (x.match_status_flag,
'A', 'Approved'
)
FROM ap.ap_invoice_distributions_all x
WHERE x.invoice_distribution_id = b.invoice_distribution_id)
"Invoice Approved?",
a.amount_paid, h.amount, h.check_id, h.invoice_payment_id "Payment Id",
i.check_number "Cheque Number",
TO_CHAR (TRUNC (i.check_date)) "Payment Date"
FROM ap.ap_invoices_all a,
ap.ap_invoice_distributions_all b,
po.po_distributions_all c,
po.po_headers_all d,
po.po_vendors e,
po.po_vendor_sites_all f,
po.po_lines_all g,
ap.ap_invoice_payments_all h,
ap.ap_checks_all i
WHERE a.invoice_id = b.invoice_id
AND b.po_distribution_id = c.po_distribution_id(+)
AND c.po_header_id = d.po_header_id(+)
AND e.vendor_id(+) = d.vendor_id
AND f.vendor_site_id(+) = d.vendor_site_id
AND d.po_header_id = g.po_header_id
AND c.po_line_id = g.po_line_id
AND a.invoice_id = h.invoice_id
AND h.check_id = i.check_id
AND f.vendor_site_id = i.vendor_site_id
AND c.po_header_id IS NOT NULL
AND a.payment_status_flag = 'Y'
AND d.type_lookup_code != 'BLANKET'
Po Which are not received for a perticular Preparer
====================================================
SELECT p.po_header_id po_header_id, r.segment1 req_number,
p.segment1 po_number
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
AND r.preparer_id = 12345
AND p.po_header_id NOT IN (
SELECT po_header_id
FROM apps.rcv_transactions
WHERE po_header_id IN (
SELECT p.po_header_id po_header_id
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
AND r.preparer_id = 12345
GROUP BY p.po_header_id))
