This GA PA Invoices Without POETA query identifies accounts payable invoices missing project-related (POETA) information for grant-related purchases, helping ensure compliance with funding rules.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- Identifies AP invoices missing POETA data for grants compliance
--Organization
HAOUS.NAME ORGANIZATION,
--Invoice Header Info
PSV.VENDOR_NAME SUPPLIER,
AIA.INVOICE_NUM INVOICE_NUMBER,
--Invoice Line Info
AILA.LINE_NUMBER INVOICE_LINE_NUMBER,
--Invoice Distribution Info
AIDA.DISTRIBUTION_LINE_NUMBER INVOICE_DISTRIBUTION_LINE,
AIDA.LINE_TYPE_LOOKUP_CODE,
AIDA.ACCOUNTING_DATE INVOICE_DISTRIBUTION_ACCOUNTING_DATE,
AIDA.PERIOD_NAME INVOICE_DISTRIBUTION_PERIOD_NAME,
AIA.INVOICE_CURRENCY_CODE,
AIDA.AMOUNT INVOICE_DISTRIBUTION_AMOUNT,
--Accounting Info (GL Code Combinations)
GCC.SEGMENT1 FUND,
GCC.SEGMENT2 ACCOUNT,
GCC.SEGMENT3 COST_CENTER,
GCC.SEGMENT4 SITE,
GCC.SEGMENT5 PROGRAM,
--Purchase Order Info
PHA.SEGMENT1 PO_NUMBER,
PLA.LINE_NUM PO_LINE_NUMBER,
PDA.DISTRIBUTION_NUM PO_DISTRIBUTION_NUMBER
FROM
AP_INVOICES_ALL AIA,
AP_INVOICE_LINES_ALL AILA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
PO_DISTRIBUTIONS_ALL PDA,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
POZ_SUPPLIERS_V PSV,
GL_CODE_COMBINATIONS GCC,
HR_ALL_ORGANIZATION_UNITS HAOUS
WHERE
1 = 1
AND AIDA.ORG_ID = HAOUS.ORGANIZATION_ID
AND PDA.PRC_BU_ID = HAOUS.ORGANIZATION_ID
AND AIA.INVOICE_ID = AILA.INVOICE_ID
AND AILA.INVOICE_ID = AIDA.INVOICE_ID(+)
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER(+)
AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID(+)
AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID(+)
AND PDA.PO_LINE_ID = PLA.PO_LINE_ID(+)
AND AIA.VENDOR_ID = PSV.VENDOR_ID
AND AIDA.DIST_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GCC.SEGMENT1 BETWEEN '0001' AND '9999'
AND AIDA.PROJECT_ID IS NULL