SELECT
POH.SEGMENT1 PO_NUMBER,
POL.LINE_NUM "Line",
LINETYPE.LINE_TYPE "Line Type",
ITEM.ITEM_NUMBER "Item",
POL.ITEM_DESCRIPTION "Item Description",
POL.ITEM_REVISION "Item Revision",
ECT.CATEGORY_NAME "Category Name",
POL.AMOUNT "Amount",
POL.QUANTITY "Quantity",
INV.UNIT_OF_MEASURE "UOM",
POL.UNIT_PRICE "Price",
POL.SECONDARY_QUANTITY "Secondary Quantity",
POL.SECONDARY_UOM_CODE "Secondary UOM",
POL.VENDOR_PRODUCT_NUM "Supplier Item",
POL.NEGOTIATED_BY_PREPARER_FLAG "Negotiated",
POL.HAZARD_CLASS_ID "Hazard Class",
POL.UN_NUMBER_ID "UN Number",
POL.NOTE_TO_VENDOR "Note to Supplier",
POH.NOTE_TO_RECEIVER "Note to Receiver",
POL.ATTRIBUTE_CATEGORY "ATTRIBUTE_CATEGORY",
BPA_POH.SEGMENT1 "SOURCE AGREEMENT",
BPA_POL.LINE_NUM "SOURCE AGREEMENT LINE",
PRC_BU.BU_NAME "Source Agreement Procurement BU",
POL.DISCOUNT_TYPE "Discount Type Code",
POL.DISCOUNT "Discount",
POL.DISCOUNT_REASON "Discount Reason",
POL.MAX_RETAINAGE_AMOUNT "Maximum Retainage Amount",
POL.LINE_STATUS ,
POL.CREATED_BY "Created BY_",
TO_CHAR(POL.CREATION_DATE,'YYYY/MM/DD') "Creation Date",
POL.LAST_UPDATED_BY "LAST_UPDATED_BY",
TO_CHAR(POL.LAST_UPDATE_DATE,'YYYY/MM/DD') "LAST_UPDATE_DATE",
POL.OBJECT_VERSION_NUMBER "OBJECT_VERSION_NUMBER"
FROM
PO_HEADERS_ALL POH,
PO_LINES_ALL POL,
PO_LINE_TYPES_TL LINETYPE,
EGP_SYSTEM_ITEMS_B ITEM,
INV_ORG_PARAMETERS IOP,
PO_LINE_LOCATIONS_ALL PLL,
INV_UNITS_OF_MEASURE INV,
EGP_CATEGORIES_TL ECT,
PO_HEADERS_ALL BPA_POH,
PO_LINES_ALL BPA_POL,
FUN_ALL_BUSINESS_UNITS_V PRC_BU,
FUN_ALL_BUSINESS_UNITS_V PRC_BU_PO
WHERE
POH.PO_HEADER_ID = POL.PO_HEADER_ID
AND POL.PO_LINE_ID=PLL.PO_LINE_ID
AND INV.UOM_CODE(+)=POL.UOM_CODE --ADDED TO GET UNIT OF MEASURE
AND POL.ITEM_ID = ITEM.INVENTORY_ITEM_ID(+)
AND LINETYPE.LINE_TYPE_ID(+) = POL.LINE_TYPE_ID
AND LINETYPE.LANGUAGE(+) = 'US'
AND PLL.SHIP_TO_ORGANIZATION_ID=IOP.ORGANIZATION_ID
AND POH.TYPE_LOOKUP_CODE='STANDARD'
AND POL.CATEGORY_ID = ECT.CATEGORY_ID
AND ECT.LANGUAGE = 'US'
AND POL.FROM_HEADER_ID = BPA_POH.PO_HEADER_ID (+)
AND POL.FROM_LINE_ID = BPA_POL.PO_LINE_ID (+)
AND BPA_POH.PO_HEADER_ID = BPA_POL.PO_HEADER_ID (+)
AND BPA_POH.PRC_BU_ID = PRC_BU.BU_ID(+)
AND POH.PRC_BU_ID = PRC_BU_PO.BU_ID
ORDER BY POH.PO_HEADER_ID