This PO Item Summary Listing Report query displays purchase order item details including active or inactive status and commodity categories, helping users review and manage inactive items within the purchasing process.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Lists PO items with status and commodity details to review inactive items.
IODV.ORGANIZATION_NAME,
PHA.SEGMENT1 PO_NUMBER,
PLA.LINE_NUM PO_LINE_NUMBER,
PLA.ITEM_DESCRIPTION,
-- Item Info
ESIB.ITEM_NUMBER,
ESIB.ITEM_TYPE,
ESIB.CREATION_DATE ITEM_CREATION_DATE,
-- Item Status
ESIB.APPROVAL_STATUS ITEM_APPROVAL_STATUS,
ESIB.INVENTORY_ITEM_FLAG INVENTORY_FLAG,
ESIB.PURCHASING_ENABLED_FLAG PURCHASING_FLAG,
-- Category/Commodity Info
ECB.CATEGORY_CODE,
-- PO Location & Quantity Info
PLLA.NEED_BY_DATE,
PLLA.QUANTITY,
PLLA.UNIT_OF_MEASURE_CLASS,
HL.LOCATION_CODE LOCATION_NAME,
-- Item Active Status (Derived)
CASE
WHEN ESIB.INVENTORY_ITEM_FLAG = 'Y' AND ESIB.PURCHASING_ENABLED_FLAG = 'Y' THEN 'ACTIVE'
ELSE 'INACTIVE'
END ITEM_STATUS
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
EGP_SYSTEM_ITEMS_B ESIB,
EGP_ITEM_CATEGORIES EIC,
EGP_CATEGORIES_B ECB,
EGP_CATEGORY_SETS_B ECSB,
INV_ORGANIZATION_DEFINITIONS_V IODV,
HR_LOCATIONS HL
WHERE
PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLA.ITEM_ID = ESIB.INVENTORY_ITEM_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID = ESIB.ORGANIZATION_ID
AND ESIB.INVENTORY_ITEM_ID = EIC.INVENTORY_ITEM_ID
AND ESIB.ORGANIZATION_ID = EIC.ORGANIZATION_ID
AND EIC.CATEGORY_ID = ECB.CATEGORY_ID
AND EIC.CATEGORY_SET_ID = ECSB.CATEGORY_SET_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID = IODV.ORGANIZATION_ID
AND PLLA.SHIP_TO_LOCATION_ID = HL.LOCATION_ID
AND ECSB.CATALOG_CODE = 'Purchasing'
ORDER BY
PHA.SEGMENT1 ,
PLA.LINE_NUM ,
ESIB.ITEM_NUMBER;