PO Purchase Summary By Category (Oracle Fusion SQL)

Supply Chain & Manufacturing (SCM) Purchase Order (PO-Fusion-SQL's)
1

This PO Purchase Summary Report By Category query displays purchase order (PO) details including PO number, type, buyer, supplier, category, currency, and calculated amounts for purchase orders, helping users analyze procurement data and monitor spending across categories and suppliers.

#sqlquery

SELECT 
  --SQL4Fusion (An Orbit Analytics Project)
  --Retrieve PO details, buyer, supplier, category, and amounts.
  PH.SEGMENT1 PO_NUMBER, 
  PDTAT.TYPE_NAME PO_TYPE, 
  PSV.SEGMENT1 SUPPLIER_NUMBER,
  PSV.VENDOR_NAME SUPPLIER_NAME,
  PPNF.FULL_NAME BUYER, 
  ECB.SEGMENT1 || '.' || ECB.SEGMENT2 CATEGORY, 
  PH.CURRENCY_CODE CURRENCY, 
  SUM (
    DECODE (
      PL.ORDER_TYPE_LOOKUP_CODE, 
      'RATE', 
      NVL (PLL.AMOUNT, 0), 
      'FIXED PRICE', 
      NVL (PLL.AMOUNT, 0), 
      NVL (
        PLL.PRICE_OVERRIDE, 
        0
      ) * (
        NVL (PL.QUANTITY, 0) - NVL (
          PLL.QUANTITY_CANCELLED, 
          0
        )
      )
    )
  ) AMOUNT, 
  SUM (
    DECODE (
      PL.ORDER_TYPE_LOOKUP_CODE, 
      'RATE', 
      NVL (PLL.AMOUNT, 0), 
      'FIXED PRICE', 
      NVL (PLL.AMOUNT, 0), 
      NVL (
        PLL.PRICE_OVERRIDE, 
        0
      ) * (
        NVL (PL.QUANTITY, 0) - NVL (
          PLL.QUANTITY_CANCELLED, 
          0
        )
      )
    )
  ) * NVL (PH.RATE, 1) FUNCTIONAL_AMOUNT 
FROM 
  PO_HEADERS PH, 
  PO_LINES PL, 
  PO_LINE_LOCATIONS PLL, 
  PO_DOCUMENT_TYPES_ALL_TL PDTAT, 
  PO_DOCUMENT_TYPES_ALL_B PDTAB, 
  EGP_CATEGORIES_B ECB, 
  POZ_SUPPLIERS_V PSV,
  PER_PERSON_NAMES_F PPNF 
WHERE 
  1 = 1 
  AND PH.AGENT_ID = PPNF.PERSON_ID
  AND PL.CATEGORY_ID = ECB.CATEGORY_ID 
  AND PL.PO_HEADER_ID = PH.PO_HEADER_ID 
  AND PLL.PO_HEADER_ID = PH.PO_HEADER_ID 
  AND PH.TYPE_LOOKUP_CODE = PDTAT.DOCUMENT_SUBTYPE
  AND PH.PRC_BU_ID = PDTAT.PRC_BU_ID
  AND PLL.PO_LINE_ID = PL.PO_LINE_ID 
  AND PSV.VENDOR_ID = PH.VENDOR_ID 
  AND PLL.SHIPMENT_TYPE NOT IN ('PRICE BREAK', 'PLANNED') 
  AND PDTAB.DOCUMENT_TYPE_CODE = PDTAT.DOCUMENT_TYPE_CODE 
  AND PDTAB.DOCUMENT_SUBTYPE = PDTAT.DOCUMENT_SUBTYPE 
  AND PDTAT.LANGUAGE = USERENV ('LANG') 
  AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE 
  AND PDTAB.DOCUMENT_TYPE_CODE IN ('PO', 'PA') 
  AND PDTAB.PRC_BU_ID = PDTAT.PRC_BU_ID 
  AND PDTAB.PRC_BU_ID = PH.PRC_BU_ID 
  AND PPNF.NAME_TYPE = 'GLOBAL'
GROUP BY 
  PSV.SEGMENT1,
  PSV.VENDOR_NAME,
  PH.SEGMENT1, 
  PDTAT.TYPE_NAME, 
  PPNF.FULL_NAME,
  ECB.SEGMENT1, 
  ECB.SEGMENT2,  
  PH.CURRENCY_CODE,
  PH.RATE