PO Purchase Order Distributions Lines Report (SQL Script)

Supply Chain & Manufacturing (SCM) Purchase Order (PO)
1

This PO Purchase Order Distributions Lines Report query displays detailed purchase order distribution amounts, quantities, and charge accounts for each PO line and shipment, helping users analyze procurement activity and monitor fund encumbrance status.

#sqlquery

SELECT
   --SQL4Fusion (An Orbit Analytics Project)
   --PO distribution details with amounts, accounts, and encumbrance info for procurement tracking.
  PHA.SEGMENT1                                 	PO_NUMBER,
  -- PO Line and Shipment Info
  PLA.LINE_NUM || '-' || PLLA.SHIPMENT_NUM     	LINE_SHIPMENT_NUMBER,
  -- Item Info
  ESI.ITEM_NUMBER,
  PLA.ITEM_REVISION,
  PLA.ITEM_DESCRIPTION,
  -- Quantity and Pricing
  ROUND((PLLA.QUANTITY - NVL(PLLA.QUANTITY_CANCELLED, 0)),2) QUANTITY_ORDERED,
  -- Charge Account
  FND_FLEX_EXT.GET_SEGS(
    'GL',
    'GL#',
    GCC.CHART_OF_ACCOUNTS_ID,
    GCC.CODE_COMBINATION_ID
  )                                            	CHARGE_ACCOUNT,
  -- Funds & Encumbrance Info
  PDA.ENCUMBERED_FLAG                          	ENCUMBERED_FLAG,
  PDA.FUNDS_STATUS                             	FUNDS_STATUS,
  PDA.GL_ENCUMBERED_DATE                       	RESERVED_DATE,
  -- Amounts
  PHA.CURRENCY_CODE                            	CURRENCY,
  PLLA.PRICE_OVERRIDE                          	UNIT_PRICE,
  ( PDA.QUANTITY_ORDERED - NVL(PDA.QUANTITY_CANCELLED, 0) 
  ) * PLLA.PRICE_OVERRIDE 						PO_CURRENCY_AMOUNT,
  ROUND((( PDA.QUANTITY_ORDERED - NVL(PDA.QUANTITY_CANCELLED, 0)
		 ) * PLLA.PRICE_OVERRIDE * NVL(PDA.RATE, 1)),FC.PRECISION) BASE_CURRENCY_AMOUNT
FROM
  PO_HEADERS_ALL             PHA,
  PO_LINES_ALL               PLA,
  PO_LINE_LOCATIONS_ALL      PLLA,
  PO_DISTRIBUTIONS_ALL       PDA,
  GL_CODE_COMBINATIONS       GCC,
  EGP_SYSTEM_ITEMS           ESI,
  FND_CURRENCIES             FC
WHERE
  PHA.PO_HEADER_ID                      = PLA.PO_HEADER_ID
  AND PLA.PO_LINE_ID                    = PLLA.PO_LINE_ID
  AND PLLA.LINE_LOCATION_ID             = PDA.LINE_LOCATION_ID
  AND PDA.CODE_COMBINATION_ID           = GCC.CODE_COMBINATION_ID
  AND PLA.ITEM_ID                       = ESI.INVENTORY_ITEM_ID
  AND PDA.DESTINATION_ORGANIZATION_ID   = ESI.ORGANIZATION_ID
  AND PHA.CURRENCY_CODE                 = FC.CURRENCY_CODE
  AND NVL(PLLA.CANCEL_FLAG, 'N')        <> 'Y'
  AND PLLA.SHIPMENT_TYPE                IN ('STANDARD', 'BLANKET', 'PLANNED', 'SCHEDULED')
  AND DECODE(
        PLA.ORDER_TYPE_LOOKUP_CODE,
        'RATE', PDA.AMOUNT_ORDERED - NVL(PDA.AMOUNT_CANCELLED, 0),
        'FIXED PRICE', PDA.AMOUNT_ORDERED - NVL(PDA.AMOUNT_CANCELLED, 0),
        PDA.QUANTITY_ORDERED - NVL(PDA.QUANTITY_CANCELLED, 0)) > 0
ORDER BY
	PHA.SEGMENT1,
	PLA.LINE_NUM,
	PLLA.SHIPMENT_NUM,
	ESI.ITEM_NUMBER;