This PO Invoices query displays detailed invoice distribution data linked to purchase orders and vendors across accounting periods, helping users validate invoice postings and approval flows.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--Displays PO invoice distribution details with approval and accounting data.
PSV.VENDOR_NAME AS SUPPLIER_NAME,
PSV.SEGMENT1 AS SUPPLIER_NUMBER,
-- Buyer Info
PU.USERNAME AS BUYER_NAME,
PU.USER_ID AS BUYER_ID,
-- Invoice Header Info
AIA.INVOICE_NUM AS INVOICE_NUMBER,
AIA.DESCRIPTION AS INVOICE_DESCRIPTION,
AIA.SOURCE AS INVOICE_SOURCE,
ATT.NAME AS INVOICE_TERMS,
AIA.INVOICE_DATE AS INVOICE_DATE,
NVL(AIA.APPROVED_AMOUNT, 0) AS APPROVED_AMOUNT,
AIA.APPROVAL_STATUS AS APPROVAL_STATUS,
-- Invoice Distribution Info
AIDA.INVOICE_LINE_NUMBER,
AIDA.CREATION_DATE AS INVOICE_CREATION_DATE,
NVL(AIDA.AMOUNT, 0) AS DISTRIBUTION_AMOUNT,
AIDA.DESCRIPTION AS INVOICE_DISTRIBUTION_DESCRIPTION,
AIDA.LINE_TYPE_LOOKUP_CODE AS DISTRIBUTION_LINE_TYPE,
AIDA.POSTED_FLAG ,
AIDA.PERIOD_NAME AS INVOICE_POSTING_PERIOD_NAME,
-- PO Header / Line Info
PHA.SEGMENT1 AS PO_DOCUMENT_NUMBER,
PHA.TYPE_LOOKUP_CODE AS PO_DOCUMENT_TYPE,
PLA.LINE_NUM AS PO_LINE_NUMBER,
PLA.LINE_TYPE_ID AS PO_LINE_TYPE_ID,
NVL(PLA.QUANTITY * PLA.UNIT_PRICE, 0) AS PO_LINE_AMOUNT,
NVL(AIDA.UNIT_PRICE, PLA.UNIT_PRICE) AS PO_UNIT_PRICE,
-- Shipment Info
PLLA.SHIPMENT_NUM AS SHIPMENT_NUMBER,
PLLA.QUANTITY AS SHIPMENT_QUANTITY,
PLLA.QUANTITY_BILLED AS SHIPMENT_QUANTITY_BILLED,
PLLA.SHIP_TO_ORGANIZATION_ID AS SHIP_TO_ORGANIZATION
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS PD,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_INVOICES_ALL AIA,
POZ_SUPPLIERS_V PSV,
POZ_SUPPLIER_SITES_ALL_M PSSAM,
PER_USERS PU,
PER_ALL_PEOPLE_F PAPF,
AP_TERMS_TL ATT,
GL_CODE_COMBINATIONS GCC5,
GL_CODE_COMBINATIONS GCC4,
GL_CODE_COMBINATIONS GCC2,
PJF_TASKS_V PTV,
PJF_PROJECTS_ALL_TL PPAT
WHERE
1 = 1
AND PAPF.PERSON_ID = PU.PERSON_ID
AND PAPF.PERSON_ID = PHA.AGENT_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PHA.VENDOR_ID = PSV.VENDOR_ID
AND PLLA.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND PD.PO_DISTRIBUTION_ID = AIDA.PO_DISTRIBUTION_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND AIA.VENDOR_SITE_ID = PSSAM.VENDOR_SITE_ID
AND PD.ACCRUAL_ACCOUNT_ID = GCC5.CODE_COMBINATION_ID(+)
AND PD.CODE_COMBINATION_ID = GCC4.CODE_COMBINATION_ID(+)
AND AIDA.DIST_CODE_COMBINATION_ID = GCC2.CODE_COMBINATION_ID
AND AIA.TERMS_ID = ATT.TERM_ID(+)
AND ATT.LANGUAGE = USERENV('LANG')
AND (
PHA.AGENT_ID IS NULL
OR TO_CHAR(PAPF.EFFECTIVE_START_DATE, 'YYYYMMDDHH24MISS') || TO_CHAR(PAPF.EFFECTIVE_END_DATE, 'YYYYMMDDHH24MISS') = (
SELECT MAX(
TO_CHAR(PAPF1.EFFECTIVE_START_DATE, 'YYYYMMDDHH24MISS') || TO_CHAR(PAPF1.EFFECTIVE_END_DATE, 'YYYYMMDDHH24MISS')
)
FROM PER_ALL_PEOPLE_F PAPF1
WHERE PAPF1.PERSON_ID = PHA.AGENT_ID
)
)
AND PPAT.PROJECT_ID(+) = PD.PJC_PROJECT_ID
AND PTV.TASK_ID(+) = PD.PJC_TASK_ID;