This PO Vendor Purchase Summary Report query retrieves a purchase order summary, focusing on the amounts associated with each order. The data is grouped by vendor and site, with details aggregated at the PO header level. This helps in tracking total spending by vendor and site, providing a high-level overview of procurement activity and facilitating vendor management and financial reporting.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Purchase order summary with amounts by vendor and site, grouped by PO header data.
-- Supplier Information
PSV.VENDOR_NAME AS SUPPLIER_NAME,
PSV.SEGMENT1 AS SUPPLIER_NUMBER,
PSV.VENDOR_TYPE_LOOKUP_CODE AS VENDOR_TYPE,
PSSA.VENDOR_SITE_CODE AS VENDOR_SITE,
-- PO Information
PHA.SEGMENT1 AS PO_NUMBER,
PLC.DISPLAYED_FIELD AS PO_TYPE,
NVL(FCB.PRECISION, 2) AS PO_CURRENCY_PRECISION,
PHA.COMMENTS,
TRUNC(PHA.CREATION_DATE) AS PO_CREATION_DATE,
PHA.REVISION_NUM AS REVISION_NUMBER,
PHA.REVISED_DATE AS REVISED_DATE,
-- Amount Details
PHA.CURRENCY_CODE CURRENCY,
SUM(PLL.PRICE_OVERRIDE * (NVL(PLL.QUANTITY, 0) - NVL(PLL.QUANTITY_CANCELLED, 0))) AS PO_AMOUNT,
SUM(PLL.PRICE_OVERRIDE * (NVL(PLL.QUANTITY, 0) - NVL(PLL.QUANTITY_CANCELLED, 0)) * NVL(PHA.RATE, 1)) AS FUNCTIONAL_AMOUNT
FROM
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSSA,
PO_HEADERS_ALL PHA,
PO_LOOKUP_CODES PLC,
FND_CURRENCIES_B FCB,
PO_LINE_LOCATIONS_ALL PLL,
PO_SYSTEM_PARAMETERS_ALL PSP
WHERE
PHA.PO_HEADER_ID = PLL.PO_HEADER_ID
AND PSV.VENDOR_ID = PSSA.VENDOR_ID
AND PSV.VENDOR_ID = PHA.VENDOR_ID
AND PSSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
AND FCB.CURRENCY_CODE = PHA.CURRENCY_CODE
AND PHA.TYPE_LOOKUP_CODE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'DOCUMENT_SUBTYPE'
AND PLC.LOOKUP_CODE NOT IN ('QUOTATION', 'RFQ')
AND PHA.PRC_BU_ID = PSP.PRC_BU_ID
GROUP BY
PSV.VENDOR_NAME,
PSV.SEGMENT1,
PSV.VENDOR_TYPE_LOOKUP_CODE,
PSSA.VENDOR_SITE_CODE,
PHA.SEGMENT1,
PLC.DISPLAYED_FIELD,
NVL(FCB.PRECISION, 2),
PHA.COMMENTS,
TRUNC(PHA.CREATION_DATE),
PHA.CURRENCY_CODE,
PHA.REVISION_NUM,
PHA.REVISED_DATE;