GL Journal Entry for AP Invoices (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle General Ledger (GL-Fusion-SQL's)
1

This GL Journal Entry for AP Invoices query displays general ledger journal entries linked to accounts payable invoices across periods, helping users reconcile invoice-level accounting details for audit and reporting.

#sqlquery

SELECT
  -- SQL4Fusion (An Orbit Analytics Project) 
  -- Displays GL journal entries linked to AP invoices
  GL.NAME LEDGER_NAME,
  DECODE (
    GCC.ACCOUNT_TYPE,
    'A',
    'Asset',
    'C',
    'Budgetary (CR)',
    'D',
    'Budgetary (DR)',
    'E',
    'Expense',
    'L',
    'Liability',
    'O',
    'Owners Equity',
    'R',
    'Revenue',
    NULL
  ) ACCOUNT_NAME,
  GCC.ACCOUNT_TYPE,
  XAH.ACCOUNTING_DATE ACCOUNTING_DATE,
  XAH.PERIOD_NAME ACCOUNTING_PERIOD_NAME,
  GJH.PERIOD_NAME JOURNAL_PERIOD,
  GJH.NAME JE_HEADER_NAME,
  GJH.DESCRIPTION JE_HEADER_DESCRIPTION,
  GJB.NAME JE_BATCH_NAME,
  GJL.JE_LINE_NUM JE_LINE_NUMBER,
  GJH.JE_CATEGORY JE_HEADER_CATEGORY,
  GJL.EFFECTIVE_DATE JE_LINE_EFFECTIVE_DATE,
  GJH.CURRENCY_CODE JE_HEADER_CURRENCY_CODE,
  DECODE (
    GJH.CURRENCY_CODE,
    'STAT',
    0,
    NVL(GJL.ENTERED_DR, 0)
  ) JE_AMOUNT_DR,
  DECODE (
    GJH.CURRENCY_CODE,
    'STAT',
    0,
    NVL(GJL.ENTERED_CR, 0)
  ) JE_AMOUNT_CR,
  GJL.DESCRIPTION JE_LINE_DESCRIPTION,
  AIA.INVOICE_NUM INVOICE_NUMBER,
  AIA.INVOICE_DATE INVOICE_DATE,
  AIA.DESCRIPTION INVOICE_DESCRIPTION,
  AIA.INVOICE_AMOUNT,
  AIA.SOURCE INVOICE_SOURCE,
  AIA.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
  XAL.AE_LINE_NUM ACCOUNTING_EVENT_LINE_NUMBER
FROM
  GL_JE_BATCHES GJB,
  GL_JE_HEADERS GJH,
  GL_JE_LINES GJL,
  GL_IMPORT_REFERENCES GIR,
  XLA_AE_HEADERS XAH,
  XLA_AE_LINES XAL,
  XLA_EVENTS XE,
  XLA_TRANSACTION_ENTITIES XTE,
  AP_INVOICES_ALL AIA,
  GL_LEDGERS GL,
  GL_CODE_COMBINATIONS GCC
WHERE
  1 = 1
  AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
  AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
  AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
  AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
  AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
  AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
  AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
  AND XAL.APPLICATION_ID = XAH.APPLICATION_ID
  AND XAH.EVENT_ID = XE.EVENT_ID
  AND XE.APPLICATION_ID = XTE.APPLICATION_ID
  AND XE.ENTITY_ID = XTE.ENTITY_ID
  AND GJL.LEDGER_ID = GL.LEDGER_ID
  AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
  AND XTE.ENTITY_CODE = 'AP_INVOICES'
  AND AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1