This PO standard purchase order shipment & suypplier details report displays detailed purchase order (PO) line and shipment data for standard POs, including pricing, supplier, buyer, shipment dates, and quantity, helping users monitor procurement activities and analyze PO fulfillment and financial impact.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Retrieve standard PO details , shipment, supplier, and buyer info.
--Purchase Order Header & Line Info
--Supplier Info
PSV.SEGMENT1 SUPPLIER_NUMBER,
PSV.VENDOR_NAME SUPPLIER_NAME,
PHA.SEGMENT1 PO_NUMBER,
PHA.TYPE_LOOKUP_CODE PO_TYPE,
PLA.LINE_NUM PO_LINE_NUMBER,
PLA.LINE_TYPE_ID PO_LINE_TYPE,
PLA.CLOSED_REASON,
--Shipment & Location Info
PLLA.SHIPMENT_NUM SHIPMENT_NUMBER,
HL.LOCATION_CODE SHIP_TO_LOCATION,
PLLA.SHIP_TO_ORGANIZATION_ID SHIP_TO_ORGANIZATION,
TRUNC(PLLA.CREATION_DATE) SHIPMENT_CREATION_DATE,
PLLA.NEED_BY_DATE,
PLLA.PROMISED_DATE,
PLLA.CANCEL_DATE,
PLLA.CLOSED_DATE,
PLLA.DAYS_EARLY_RECEIPT_ALLOWED,
PLLA.DAYS_LATE_RECEIPT_ALLOWED,
-- Buyer Info
PPNF.FULL_NAME BUYER_NAME,
--Currency Info
PHA.CURRENCY_CODE CURRENCY,
--Quantities & Unit Price Info
PLA.UNIT_PRICE AGREED_UNIT_PRICE,
PLLA.PRICE_OVERRIDE UNIT_PRICE_OVERRIDE,
PLLA.QUANTITY * PLA.UNIT_PRICE BUYING_PRICE,
NVL(PLLA.QUANTITY, 0) RELEASED_QUANTITY,
NVL(PLLA.QUANTITY, 0) QUANTITY,
NVL(PLLA.QUANTITY_ACCEPTED, 0) QUANTITY_ACCEPTED,
NVL(PLLA.QUANTITY_RECEIVED, 0) QUANTITY_RECEIVED,
NVL(PLLA.QUANTITY_REJECTED, 0) QUANTITY_REJECTED,
NVL(PLLA.QUANTITY_BILLED, 0) QUANTITY_BILLED,
NVL(PLLA.QUANTITY_CANCELLED, 0) QUANTITY_CANCELLED,
NVL(PLLA.QUANTITY, 0) - NVL(PLLA.QUANTITY_CANCELLED, 0) - NVL(PLLA.QUANTITY_RECEIVED, 0) QUANTITY_OUTSTANDING,
PLLA.QTY_RCV_TOLERANCE QUANTITY_RECEIVED_TOLERANCE,
--Flags and Status
NVL(PHA.ACCEPTANCE_REQUIRED_FLAG, 'N') ACCEPTANCE_REQUIRED_FLAG,
PLLA.ACCRUE_ON_RECEIPT_FLAG ,
NVL(PLLA.CANCEL_FLAG, 'N') CANCEL_FLAG,
PLLA.INVOICE_CLOSE_TOLERANCE
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
HR_ALL_ORGANIZATION_UNITS HAOU,
HR_LOCATIONS HL
WHERE
1 = 1
AND PHA.TYPE_LOOKUP_CODE = 'STANDARD'
AND PLLA.SHIP_TO_LOCATION_ID = HL.LOCATION_ID(+)
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND HAOU.ORGANIZATION_ID = PHA.PRC_BU_ID
AND PHA.VENDOR_ID = PSV.VENDOR_ID
AND PHA.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID
AND PAPF.PERSON_ID = PHA.AGENT_ID
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND PPNF.NAME_TYPE = 'GLOBAL'
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID(+)
ORDER BY PSV.SEGMENT1,
PHA.SEGMENT1