This AP Invoice Activity Report query provides a comprehensive view of AP invoice details including amounts, lines, distributions, GL account segments, supplier info, and item-level data, along with invoice and distribution types across operating units.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query provides a comprehensive view of invoice details, line items, and accounting distributions
AIA.INVOICE_NUM INVOICE_NUMBER,
AIA.DESCRIPTION INVOICE_DESCRIPTION,
ALC.DISPLAYED_FIELD INVOICE_TYPE,
NVL (AIA.INVOICE_AMOUNT, 0) INVOICE_AMOUNT,
NVL (AIA.AMOUNT_PAID, 0) AMOUNT_PAID,
AIA.INVOICE_DATE,
FND_FLEX_EXT.GET_SEGS(
'GL',
'GL#',
GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID
) ACCOUNT,
AILA.AMOUNT LINE_AMOUNT,
(
SELECT
DISTINCT MSIB.ITEM_NUMBER
FROM
EGP_SYSTEM_ITEMS_B MSIB
WHERE
MSIB.INVENTORY_ITEM_ID = AILA.INVENTORY_ITEM_ID
) ITEM_NAME,
AILA.ITEM_DESCRIPTION,
AIDA.ACCOUNTING_DATE,
AIDA.DESCRIPTION DISTRIBUTION_DESCRIPTION,
AIDA.INVOICE_LINE_NUMBER LINE_NUMBER,
AIDA.DISTRIBUTION_LINE_NUMBER,
NVL (AIDA.AMOUNT, 0) DISTRIBUTION_AMOUNT,
NVL (AILA.BASE_AMOUNT, 0) LINE_BASE_AMOUNT,
AILA.DESCRIPTION LINE_DESCRIPTION,
AIA.GL_DATE,
AIA.INVOICE_CURRENCY_CODE,
AIA.INVOICE_TYPE_LOOKUP_CODE,
AIA.SET_OF_BOOKS_ID,
AIA.INVOICE_ID,
AIA.VENDOR_ID,
NVL (PSV.VENDOR_NAME, HP.PARTY_NAME) AS SUPPLIER_NAME,
AILA.INVENTORY_ITEM_ID,
AIA.ORG_ID,
AIDA.PERIOD_NAME,
AIDA.QUANTITY_INVOICED,
AIDA.QUANTITY_VARIANCE,
NVL (
AIDA.TOTAL_DIST_AMOUNT,
0
) TOTAL_DIST_AMOUNT,
NVL (
AIDA.TOTAL_DIST_BASE_AMOUNT,
0
) TOTAL_DIST_BASE_AMOUNT,
HAOU.NAME ORGANIZATION_NAME,
PDA.UNENCUMBERED_QUANTITY QUANTITY_UNENCUMBERED,
AIDA.LINE_TYPE_LOOKUP_CODE LINE_TYPE,
AIDA.DIST_MATCH_TYPE,
AIDA.FINAL_MATCH_FLAG,
AIDA.PA_ADDITION_FLAG,
ALC1.DISPLAYED_FIELD DISTRIBUTION_LINE_TYPE
FROM
HZ_PARTIES HP,
AP_INVOICES_ALL AIA,
AP_INVOICE_LINES_ALL AILA,
PO_DISTRIBUTIONS_ALL PDA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
AP_LOOKUP_CODES ALC,
AP_LOOKUP_CODES ALC1,
GL_CODE_COMBINATIONS GCC,
HR_ALL_ORGANIZATION_UNITS HAOU,
POZ_SUPPLIERS_V PSV
WHERE
1 = 1
AND AIA.INVOICE_ID = AILA.INVOICE_ID
AND AILA.INVOICE_ID = AIDA.INVOICE_ID
AND AIA.INVOICE_ID = AIDA.INVOICE_ID
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND PSV.VENDOR_ID(+) = AIA.VENDOR_ID
AND GCC.CODE_COMBINATION_ID = AIDA.DIST_CODE_COMBINATION_ID
AND HAOU.ORGANIZATION_ID = AIA.ORG_ID
AND ALC.LOOKUP_TYPE = 'INVOICE TYPE'
AND ALC.LOOKUP_CODE = AIA.INVOICE_TYPE_LOOKUP_CODE
AND ALC1.LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
AND ALC1.LOOKUP_CODE = AIDA.LINE_TYPE_LOOKUP_CODE
AND AIA.PARTY_ID(+) = HP.PARTY_ID
AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID