This PO Release Shipments query retrieves detailed information about suppliers, including Purchase Order (PO) headers, lines, and shipment details. It focuses specifically on planned and blanket purchase orders, providing insights into supplier relationships and the associated order and shipment statuses. This data is key for tracking long-term purchase agreements and planned procurements.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves supplier, PO header, line, and shipment details for planned and blanket purchase orders.
-- Supplier Details
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.SEGMENT1 SUPPLIER_NUMBER,
PSAM.VENDOR_SITE_CODE SUPPLIER_SITE,
PU.USERNAME USERNAME,
--PO Information
PHA.SEGMENT1 PO_NUMBER,
PLA.LINE_NUM PO_LINE_NUMBER,
PHA.TYPE_LOOKUP_CODE PO_TYPE,
PLA.ITEM_DESCRIPTION ITEM_DESCRIPTION,
--Shipment Information
PLL.SHIPMENT_NUM RELEASE_SHIPMENT_NUMBER,
PHA.ACCEPTANCE_DUE_DATE,
NVL(PLL.CANCEL_FLAG, 'N') SHIPMENT_CANCELLED_FLAG,
PLL.PROMISED_DATE,
PLL.SHIPMENT_CLOSED_DATE,
PLL.SHIPPING_UOM_CODE,
-- Amount Information
PHA.CURRENCY_CODE CURRENCY,
NVL(PLL.QUANTITY, 0) RELEASED_QUANTITY,
PLL.PRICE_OVERRIDE ACTUAL_UNIT_PRICE,
PLA.UNIT_PRICE AGREED_UNIT_PRICE,
NVL(PLA.AMOUNT_RELEASED,0) AMOUNT_RELEASED,
NVL(PLL.AMOUNT,0) PO_LINE_AMOUNT,
NVL(PLL.AMOUNT_BILLED,0) LINE_BILLED_AMOUNT
FROM
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSAM,
PER_USERS PU,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLL,
PER_ALL_PEOPLE_F PAPF,
HR_LOCATIONS_ALL_TL HLATL,
HR_LOCATIONS HL
WHERE
PAPF.PERSON_ID = PU.PERSON_ID
AND PHA.TYPE_LOOKUP_CODE IN ('PLANNED', 'BLANKET')
AND PHA.PO_HEADER_ID = PLL.PO_HEADER_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLL.PO_LINE_ID
-- AND PLL.SHIPMENT_TYPE IN ('BLANKET', 'PLANNED', 'SCHEDULED')
AND HL.LOCATION_ID(+) = PLL.SHIP_TO_LOCATION_ID
AND HLATL.LANGUAGE = USERENV('LANG')
AND PHA.VENDOR_ID = PSV.VENDOR_ID(+)
AND PHA.VENDOR_SITE_ID = PSAM.VENDOR_SITE_ID(+);