AP Invoice Distributions (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Account Payables (AP-Fusion-SQL's)
1

This AP Invoice Distributions query provides a detailed analysis of invoice lines and distributions, capturing item details, GL accounts, vendor info, and PO matching for accurate financial tracking and reconciliation.

#sqlquery

SELECT
 --SQL4Fusion (An Orbit Analytics Project) 
 --This query provides a detailed breakdown of invoice distributions
  HAOU.NAME ORGANIZATION_NAME,
  AIA.INVOICE_DATE, 
  AIA.INVOICE_NUM INVOICE_NUMBER,
  AIDA.INVOICE_LINE_NUMBER,
  AILA.DESCRIPTION LINE_DESCRIPTION,
  AIDA.DISTRIBUTION_LINE_NUMBER,
  ALC.DISPLAYED_FIELD DISTRIBUTION_LINE_TYPE,
  AIDA.DESCRIPTION DISTRIBUTION_DESCRIPTION,
  PSV.VENDOR_NAME SUPPLIER_NAME,
  AIA.INVOICE_CURRENCY_CODE,
  NVL (AIA.INVOICE_AMOUNT, 0) INVOICE_AMOUNT,
  NVL (AIA.AMOUNT_PAID, 0) INVOICE_AMOUNT_PAID,
  NVL (AILA.BASE_AMOUNT, 0) LINE_BASE_AMOUNT,
  NVL (AIDA.AMOUNT, 0) DISTRIBUTION_AMOUNT,
  AIA.INVOICE_TYPE_LOOKUP_CODE,
  (
    SELECT
      DISTINCT ESIB.ITEM_NUMBER
    FROM
      EGP_SYSTEM_ITEMS_B ESIB
    WHERE
      ESIB.INVENTORY_ITEM_ID = AILA.INVENTORY_ITEM_ID
	  AND ESIB.ORGANIZATION_ID= AILA.ORG_ID
  ) ITEM_NUMBER,
  AILA.ITEM_DESCRIPTION,
  AIDA.ACCOUNTING_DATE,
  AIA.GL_DATE,
  AIDA.PERIOD_NAME,
  AIDA.QUANTITY_INVOICED,
  AIDA.QUANTITY_VARIANCE,
  NVL(PDA.UNENCUMBERED_QUANTITY,0) QUANTITY_UNENCUMBERED,
  NVL(AIDA.TOTAL_DIST_AMOUNT,0) TOTAL_DISTRIBUTION_AMOUNT,
  NVL(AIDA.TOTAL_DIST_BASE_AMOUNT,0) TOTAL_DISTRIBUTION_BASE_AMOUNT, 
  AIDA.DIST_MATCH_TYPE DISTRIBUTION_MATCH_TYPE,
  AIDA.FINAL_MATCH_FLAG,
  AIDA.PA_ADDITION_FLAG
FROM
  AP_INVOICES_ALL AIA,
  AP_INVOICE_LINES_ALL AILA,
  AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
  POZ_SUPPLIERS_V PSV,
  GL_CODE_COMBINATIONS GCC,
  AP_LOOKUP_CODES ALC,
  PO_DISTRIBUTIONS_ALL PDA,
  HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
  1 = 1
  AND AIA.INVOICE_ID = AILA.INVOICE_ID
  AND AILA.INVOICE_ID = AIDA.INVOICE_ID
  AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
  AND AIA.VENDOR_ID = PSV.VENDOR_ID
  AND AIDA.DIST_CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID  
  AND ALC.LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
  AND ALC.LOOKUP_CODE = AIDA.LINE_TYPE_LOOKUP_CODE
  AND AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
  AND AIA.ORG_ID = HAOU.ORGANIZATION_ID