This PO Contracts query generates information about the Contract Purchase Orders with the contract creation date, vendor info, buyer details and the authorization status.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Displays contract POs with supplier, buyer, and approval details.
HAOU.NAME ORGANIZATION_NAME,
-- Supplier Details
PSV.VENDOR_NAME SUPPLIER_NAME,
PSSAM.VENDOR_SITE_CODE SUPPLIER_SITE_CODE,
-- Buyer Details
PPNF.FULL_NAME,
PAPF.PERSON_NUMBER BUYER_NUMBER,
-- Contract Details
PHA.SEGMENT1 CONTRACT_NUMBER,
PHA.CREATION_DATE CONTRACT_CREATION_DATE,
PHA.COMMENTS COMMENTS,
PHA.REVISION_NUM REVISION_NUMBER,
PHA.CLOSED_DATE CLOSED_DATE,
-- Approval & Status
NVL(PHA.APPROVED_FLAG, 'N') APPROVED_CODE,
PHA.APPROVED_DATE APPROVED_DATE,
-- Acceptance Info
PHA.ACCEPTANCE_DUE_DATE ACCEPTANCE_DUE_DATE,
NVL(PHA.ACCEPTANCE_REQUIRED_FLAG, 'N') ACCEPTANCE_REQUIRED_FLAG,
-- Financial Details
NVL(PHA.BLANKET_TOTAL_AMOUNT, 0) AMOUNT_AGREED,
(
SELECT
-- Simplified RELEASED_AMOUNT calculation
SUM(
NVL(PLL.PRICE_OVERRIDE, 0) * (NVL(PLL.QUANTITY, 0) - NVL(PLL.QUANTITY_CANCELLED, 0)) * NVL(PHA.RATE, 1)
)
FROM
PO_LINE_LOCATIONS PLL,
PO_LINES PL
WHERE
PLL.PO_LINE_ID = PL.PO_LINE_ID
AND PLL.SHIPMENT_TYPE IN ('STANDARD')
AND PL.CONTRACT_ID = PHA.PO_HEADER_ID
) RELEASED_AMOUNT,
PHA.AMOUNT_LIMIT AMOUNT_LIMIT,
PHA.MIN_RELEASE_AMOUNT MINIMUM_RELEASE_AMOUNT,
-- Currency & Rate Info
GL.CURRENCY_CODE BASE_CURRENCY_CODE,
PHA.RATE RATE,
PHA.RATE_DATE ,
PHA.RATE_TYPE RATE_TYPE,
-- Shipping & Billing Locations
HLA.LOCATION_CODE BILL_TO_LOCATION,
HLA1.LOCATION_CODE SHIP_TO_LOCATION,
-- Other
PHA.FOB_LOOKUP_CODE FOB
FROM
PO_HEADERS_ALL PHA,
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_ALL HLA,
HR_LOCATIONS_ALL HLA1,
GL_LEDGERS GL,
FINANCIALS_SYSTEM_PARAMS_ALL FSPA
WHERE
PHA.TYPE_LOOKUP_CODE = 'CONTRACT'
AND NVL(PHA.PRC_BU_ID, -9999) = NVL(FSPA.ORG_ID, NVL(PHA.PRC_BU_ID, -9999))
AND FSPA.SET_OF_BOOKS_ID = GL.LEDGER_ID
AND PHA.VENDOR_ID = PSV.VENDOR_ID(+)
AND PHA.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID(+)
AND PHA.SHIP_TO_LOCATION_ID = HLA1.LOCATION_ID(+)
AND PHA.BILL_TO_LOCATION_ID = HLA.LOCATION_ID(+)
AND PAPF.PERSON_ID = PHA.AGENT_ID
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND HAOU.ORGANIZATION_ID = PHA.PRC_BU_ID
AND PPNF.NAME_TYPE = 'GLOBAL';