Find number of PO's based on Organization Name.
SELECT
hou.name AS organization_name,
COUNT(pha.po_header_id) AS standard_po_count
FROM
po_headers_all pha,
hr_operating_units hou
WHERE
pha.prc_bu_id = hou.organization_id
AND pha.type_lookup_code = 'STANDARD'
GROUP BY
hou.name
--3198
-------------
Standard PO count query
SELECT
hou.name OrgName,
count(*)
FROM po_headers_all pha,
hr_operating_units hou
WHERE type_lookup_code = 'STANDARD' --'BLANKET' or 'CONTRACT' AND
pha.prc_bu_id = hou.organization_id
GROUP BY hou. NAME
--------------------------
--Write query to get PO details with no supplier.
SELECT
pha.segment1 AS po_number,
pha.document_status,
pha.creation_date,
ps.segment1 AS supplier_number,
hp.party_name AS supplier_name
FROM
po_headers_all pha,
poz_suppliers ps,
hz_parties hp
WHERE
pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.vendor_id IS NULL
ORDER BY
pha.creation_date DESC
--------------------
Write a query to get PO details with Supplier
SELECT
pha.segment1 AS po_number,
pha.document_status,
pha.creation_date,
ps.segment1 AS supplier_number,
hp.party_name AS supplier_name
FROM
po_headers_all pha,
poz_suppliers ps,
hz_parties hp
WHERE
pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.vendor_id IS NOT NULL
ORDER BY
pha.creation_date DESC
----------------------
Write a query to list every PO and the total number of lines associated with it.
SELECT
pha.segment1 AS po_number,
hp.party_name AS supplier_name,
pha.document_status,
COUNT(pla.po_line_id) AS total_lines
FROM
po_headers_all pha,
po_lines_all pla,
poz_suppliers ps,
hz_parties hp
WHERE
pha.po_header_id = pla.po_header_id(+)
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
GROUP BY
pha.segment1,
hp.party_name,
pha.document_status
ORDER BY
total_lines DESC
-------------------------
Write a query to get Line Amount for every PO
SELECT
pha.segment1 AS po_number,
hp.party_name AS supplier_name,
pha.currency_code,
pha.document_status,
SUM(NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0))) AS total_po_amount,
COUNT(DISTINCT pla.po_line_id) AS total_lines
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp
WHERE
pha.po_header_id = pla.po_header_id(+)
AND pla.po_line_id = plla.po_line_id(+)
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
GROUP BY
pha.segment1,
hp.party_name,
pha.currency_code,
pha.document_status
ORDER BY
pha.segment1 DESC
---------------
Write a query to get Line Amount for every PO along with BU name.
SELECT
hou.name AS organization_name,
pha.segment1 AS po_number,
hp.party_name AS supplier_name,
pha.currency_code,
pha.document_status,
SUM(NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0))) AS total_po_amount,
COUNT(DISTINCT pla.po_line_id) AS total_lines
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp,
hr_organization_units hou
WHERE
pha.po_header_id = pla.po_header_id(+)
AND pla.po_line_id = plla.po_line_id(+)
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.prc_bu_id = hou.organization_id(+)
GROUP BY
hou.name,
pha.segment1,
hp.party_name,
pha.currency_code,
pha.document_status
ORDER BY
hou.name ASC,
pha.segment1 DESC
=====================================
Which PO has maximum amount among all the BU.
SELECT * FROM (
SELECT
hou.name AS organization_name,
pha.segment1 AS po_number,
hp.party_name AS supplier_name,
pha.currency_code,
pha.document_status,
SUM(NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0))) AS total_po_amount
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp,
hr_organization_units hou
WHERE
pha.po_header_id = pla.po_header_id(+)
AND pla.po_line_id = plla.po_line_id(+)
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.prc_bu_id = hou.organization_id(+)
GROUP BY
hou.name,
pha.segment1,
hp.party_name,
pha.currency_code,
pha.document_status
ORDER BY
6 DESC -- Sorts by the 6th column (total_po_amount)
)
WHERE ROWNUM = 1
=================
Lowest PO Amount among all the BU's
SELECT * FROM (
SELECT
hou.name AS organization_name,
pha.segment1 AS po_number,
hp.party_name AS supplier_name,
pha.currency_code,
pha.document_status,
SUM(NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0))) AS total_po_amount
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp,
hr_organization_units hou
WHERE
pha.po_header_id = pla.po_header_id(+)
AND pla.po_line_id = plla.po_line_id(+)
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.prc_bu_id = hou.organization_id(+)
GROUP BY
hou.name,
pha.segment1,
hp.party_name,
pha.currency_code,
pha.document_status
HAVING SUM(NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0))) > 0
ORDER BY
total_po_amount ASC
)
WHERE ROWNUM = 1
=========================================
Real-Time Reports related to P2P cycle
1.Purchase Order PDF Report (Standard/Custom)
-----------------------
SELECT
hou.name AS business_unit_name,
pha.segment1 AS po_number,
pha.document_status,
pha.currency_code,
pha.creation_date,
-- Supplier Details
hp.party_name AS supplier_name,
ps.segment1 AS supplier_number,
pasa.vendor_site_code AS supplier_site,
-- Line Details
pla.line_num,
pla.item_description,
pla.unit_price,
pla.quantity,
pla.uom_code,
-- Amount Calculation
NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0)) AS line_amount,
-- Ship-to/Bill-to (Using Base Table for IDs)
hla_ship.location_code AS ship_to_location,
hla_bill.location_code AS bill_to_location
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp,
poz_supplier_sites_all_m pasa,
hr_organization_units hou,
hr_locations_all hla_ship,
hr_locations_all hla_bill
WHERE 1=1
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.vendor_site_id = pasa.vendor_site_id(+)
AND pha.prc_bu_id = hou.organization_id(+)
AND pha.ship_to_location_id = hla_ship.location_id(+)
AND pha.bill_to_location_id = hla_bill.location_id(+)
ORDER BY
pla.line_num ASC
---------------------
2.Purchase Order Change Order Report
------------------------
SELECT
hou.name AS business_unit,
pha.segment1 AS po_number,
pha.revision_num AS current_revision,
pha.document_status,
hp.party_name AS supplier_name,
-- Line Details
pla.line_num,
pla.item_description,
-- Changed Values
plla.quantity AS revised_quantity,
pla.unit_price AS revised_price,
NVL(plla.amount, NVL(plla.quantity, 0) * NVL(pla.unit_price, 0)) AS revised_line_amount,
-- Meta Data
pha.last_update_date AS change_date,
pu.username AS changed_by
FROM
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
poz_suppliers ps,
hz_parties hp,
hr_organization_units hou,
per_users pu
WHERE
pha.po_header_id = pla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pha.vendor_id = ps.vendor_id(+)
AND ps.party_id = hp.party_id(+)
AND pha.prc_bu_id = hou.organization_id(+)
AND pha.last_updated_by = pu.user_guid(+)
-- Filter for POs that have undergone a Change Order
AND pha.revision_num > 0
ORDER BY
pha.last_update_date DESC,
pla.line_num ASC
-------------------
Write a query to find Price Changes for PO lines.
--------------------
SELECT
pha.segment1 AS po_number,
pla.line_num,
pla.unit_price AS current_price,
pla_arch.unit_price AS previous_price,
(pla.unit_price - pla_arch.unit_price) AS price_diff
FROM
po_headers_all pha,
po_lines_all pla,
po_lines_archive_all pla_arch
WHERE 1=1
AND pha.po_header_id = pla.po_header_id
AND pla.po_line_id = pla_arch.po_line_id
AND pla.unit_price <> pla_arch.unit_price
AND pla_arch.object_version_number = (
SELECT MAX(pla_sub.object_version_number)
FROM po_lines_archive_all pla_sub
WHERE pla_sub.po_line_id = pla.po_line_id
AND pla_sub.object_version_number < pla.object_version_number
)
ORDER BY
pha.segment1,
pla.line_num
==============================
3.Goods Receipt Note (GRN) / Receiving Receipt Report
SELECT
rsh.receipt_num,
rsh.shipment_num,
rsh.waybill_airbill_num,
hp.party_name AS supplier_name,
pha.segment1 AS po_number,
pla.line_num,
rsl.item_description,
rt.transaction_type,
rt.transaction_date,
rt.quantity AS received_qty,
rt.uom_code,
flv.meaning AS routing_name
FROM
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
rcv_transactions rt,
po_headers_all pha,
po_lines_all pla,
hz_parties hp,
poz_suppliers_v psv,
fnd_lookup_values flv
WHERE 1=1
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND rsl.po_header_id = pha.po_header_id
AND rsl.po_line_id = pla.po_line_id
AND pha.vendor_id = psv.vendor_id
AND psv.party_id = hp.party_id
AND TO_CHAR(rt.routing_header_id) = flv.lookup_code(+)
AND flv.lookup_type(+) = 'RCV_ROUTING_HEADERS'
AND flv.language(+) = USERENV('LANG')
AND rt.transaction_type = 'RECEIVE'
AND rsh.shipment_num IS NOT NULL
AND rsh.waybill_airbill_num IS NOT NULL
ORDER BY
rsh.receipt_num DESC
