Cash to General Ledger Reconciliation Report

Cash Management

Compares the general ledger cash account balance to the bank account balance for each account, listing the unreconciled GL journal lines and unreconciled bank statement lines that explain the gap — the book-to-bank tie-out every close depends on.

Run note · BIP run High-volume GL extracts can exceed BI Publisher's online output limit and time out. Run it as a scheduled process (ESS) with output bursted to file or email rather than online preview, and bound it by ledger and period.

Run note · Performance The subledger (XLA) join — especially Projects-to-XLA — is a known BI Publisher performance trap on large ledgers. Pre-join it in the warehouse, or apply tight ledger, period, and source filters before running online.

Sample build of the Cash to General Ledger Reconciliation Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Cash to General Ledger Reconciliation Report

Sample build · illustrative

Filters

Gc From Date

2026-02-28

Gc Set Of Books Id

1003

Gc To Date

2026-02-28

Bank Account Id

1003

Gc Bank Type

Standard

Bank accounts

$48K

Book-to-bank gap

Unreconciled lines

Bank Account	Gl Cash Balance	Bank Balance	Unreconciled Gl	Unreconciled Bank	Difference
1000-2100-000	$1,240,500.00	$1,240,500.00	Sample	Sample	Sample
1000-5400-000	$842,150.75	$842,150.75	—	—	—
1000-1410-000	$96,400.00	$96,400.00	Sample	Sample	Sample
2000-2100-000	$1,005,233.10	$1,005,233.10	—	—	—
1000-6300-000	$58,720.40	$58,720.40	Sample	Sample	Sample
1000-2100-000	$1,240,500.00	$1,240,500.00	Sample	Sample	Sample

AI Analyst · active

reading

The report compares the GL cash balance to the bank balance and lists what's unreconciled on each side.

flag

A $48K book-to-bank gap comes from GL journals posted directly to the cash account that never matched a bank line.

root cause & next step

Route cash entries through Cash Management rather than manual GL journals; a direct GL post to the cash account is the classic reason book and bank diverge.

Illustrative data. The live interactive version — drill-through, filters, export, and the AI Analyst — runs on your warehouse. See it live →

This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.

Data sources

How it interconnects: this data set reads the physical tables above. Those same tables surface in OTBI as subject areas and in BICC as PVOs — three lenses on one source. Open any table to trace its subject areas and View Objects.

SQL data set · genericized · parameterized · no hardcoded segments

Show / hide SQL

select * from (
SELECT  /* Unreconciled bank statement lines */
    CEL_SOURCE.meaning                     as ORIGIN
  , CEL_TYPE.meaning                       as TRX_TYPE
  , SL.RECON_REFERENCE                     as DOC_NUMBER
  , TO_CHAR(SL.BOOKING_DATE,'DD-Mon-YYYY','nls_date_language=American')  as TRX_DATE
  , 0                                      as BOOKS
  ,DECODE(SL.FLOW_INDICATOR, 'DBIT', -1 * SL.AMOUNT, SL.AMOUNT) entered_amount  -- added w.r.t v1
  ,CURRENCY_CODE curr            -- added w.r.t v1
  , DECODE(SL.FLOW_INDICATOR, 'DBIT', -1 * SL.AMOUNT, SL.AMOUNT)     as BANK
  , 'Line ' || to_char(SL.LINE_NUMBER) || ',' || SH.statement_number as STATEMENT_NUMBER
  , NULL JOURNAL_NAME
  , NULL BATCH_NAME
FROM CE_STATEMENT_LINES SL
  , CE_STATEMENT_HEADERS SH
  , CE_LOOKUPS CEL_TYPE
  , CE_LOOKUPS CEL_SOURCE
WHERE
    SL.STATEMENT_HEADER_ID = SH.STATEMENT_HEADER_ID
AND RECON_STATUS = 'UNR'
AND CEL_TYPE.lookup_type = 'CE_TRX_TYPE'
AND CEL_TYPE.lookup_code = SL.TRX_TYPE
AND CEL_SOURCE.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL_SOURCE.lookup_code = 'BS'
AND nvl(sh.intraday_flag, 'N') <> 'Y'
AND TRUNC(SL.BOOKING_DATE) BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND SH.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
UNION ALL
SELECT DISTINCT /* Journals for unreconciled AP transactions */
    CEL_SOURCE.meaning                      as ORIGIN
  , CEL_TYPE.meaning                        as TRX_TYPE
  , to_char(AC.CHECK_NUMBER)                as DOC_NUMBER
  , TO_CHAR(NVL(AC.CLEARED_DATE, AC.CHECK_DATE),'DD-Mon-YYYY','nls_date_language=American') as TRX_DATE
  , decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) as BOOKS /* 22550369 include FBANK */
  ,(NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)) entered_amount  -- added w.r.t v1
  ,XAL.CURRENCY_CODE curr            -- added w.r.t v1
  , 0                                                                 as BANK
  , 'Line ' || GJL.je_Line_num || ',' || GJH.Name || ',' || GJB.NAME  as STATEMENT_NUMBER
  , GJH.NAME JOURNAL_NAME
  , GJB.NAME BATCH_NAM
FROM 
    AP_CHECKS_ALL AC
  , XLA_TRANSACTION_ENTITIES XTE
  , XLA_AE_HEADERS XAH
  , XLA_AE_LINES XAL
  , GL_IMPORT_REFERENCES GLIR
  , GL_JE_LINES GJL
  , GL_JE_HEADERS GJH
  , GL_JE_BATCHES GJB
  , CE_INTERNAL_BANK_ACCTS_V ACCT
  , CE_BANK_ACCT_USES_ALL CE_BAU
  , CE_TRX_TYPE_MAPPING CTTM
  , CE_LOOKUPS CEL_TYPE
  , CE_LOOKUPS CEL_SOURCE
WHERE
    GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE = 'Payables'
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJB.STATUS = 'P' /* bug 15858278 ADDED */
AND GJL.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND XAL.APPLICATION_ID = 200
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = 'XLAJEL'
AND XAL.GL_SL_LINK_TABLE = GLIR.GL_SL_LINK_TABLE
AND XAL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.APPLICATION_ID = 200 /* bug 15858278 ADDED */
AND NVL(XTE.SOURCE_ID_INT_1, -99) = AC.CHECK_ID
AND XAH.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = 200
AND XTE.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND XTE.ENTITY_CODE = 'AP_PAYMENTS'
AND AC.RECON_FLAG = 'N'
AND AC.STATUS_LOOKUP_CODE in ('NEGOTIABLE', 'CLEARED') /* bug 22499351 exclude Voided trx */
AND ACCT.BANK_ACCOUNT_ID = CE_BAU.BANK_ACCOUNT_ID
AND ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND GJL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
AND CE_BAU.BANK_ACCT_USE_ID = AC.CE_BANK_ACCT_USE_ID
AND CE_BAU.AP_USE_ENABLE_FLAG = 'Y'
AND NVL(CTTM.active_flag (+), 'Y') = 'Y'
AND CTTM.PMT_RCT_METHOD(+) = ac.payment_method_code
AND CEL_TYPE.lookup_code(+) = CTTM.trx_type
AND CEL_TYPE.lookup_type(+) = 'CE_TRX_TYPE'
AND CEL_SOURCE.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL_SOURCE.lookup_code = 'AP'
UNION ALL
SELECT DISTINCT /* Journals for unreconciled AR transactions */
    CEL_SOURCE.meaning ORIGIN
  , CEL_TYPE.meaning TRX_TYPE
  , to_char(CR.RECEIPT_NUMBER) DOC_NUMBER
  , TO_CHAR(NVL(CR.DEPOSIT_DATE, CR.RECEIPT_DATE),'DD-Mon-YYYY','nls_date_language=American') TRX_DATE
  , decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS /* 22550369 include FBANK */
  ,(NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)) entered_amount  -- added w.r.t v1
  ,XAL.CURRENCY_CODE curr            -- added w.r.t v1  
  , 0  BANK
  , 'Line ' || GJL.je_Line_num || ',' || GJH.Name ||','|| GJB.NAME STATEMENT_NUMBER
  , GJH.NAME JOURNAL_NAME
  , GJB.NAME BATCH_NAME
FROM ar_cash_receipts_all cr
  , ar_cash_receipt_history_all crh
  , ar_distributions_all ard
  , xla_distribution_links xdl
  , xla_ae_lines xal
  , gl_import_references glir
  , gl_je_lines gjl
  , gl_je_headers gjh
  , gl_je_batches gjb
  , ce_bank_acct_uses_all ce_bau
  , ce_internal_bank_accts_v acct
  , ce_trx_type_mapping cttm
  , ce_lookups cel_type
  , ce_lookups cel_source
WHERE
    GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE = 'Receivables'
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJB.STATUS = 'P'
AND GJL.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
AND XAL.APPLICATION_ID = 222
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GLIR.GL_SL_LINK_TABLE
AND XAL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XDL.APPLICATION_ID = 222
AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
AND ARD.LINE_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND ARD.SOURCE_ID = CRH.CASH_RECEIPT_HISTORY_ID
AND ARD.SOURCE_TABLE = 'CRH'
AND CRH.STATUS IN ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')  /* bug 22499351 exclude Voided trx */
AND NOT EXISTS (
    SELECT 1 FROM ar_cash_receipt_history_all crh1
    WHERE crh1.status = 'REVERSED'
    AND crh1.current_record_flag = 'Y'
    AND crh.cash_receipt_id = crh1.cash_receipt_id)
AND CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CR.RECON_FLAG = 'N'
AND CE_BAU.BANK_ACCT_USE_ID = CR.REMIT_BANK_ACCT_USE_ID
AND ACCT.BANK_ACCOUNT_ID = CE_BAU.BANK_ACCOUNT_ID
AND ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND CTTM.PMT_RCT_METHOD(+) = TO_CHAR(CR.RECEIPT_METHOD_ID)
AND CTTM.MAPPING_TYPE(+) = 'RECEIPT'
AND nvl(CTTM.active_flag (+), 'Y') = 'Y'
AND CEL_TYPE.lookup_code(+) = CTTM.trx_type
AND CEL_TYPE.lookup_type(+) = 'CE_TRX_TYPE'
AND CEL_SOURCE.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL_SOURCE.lookup_code = 'AR'
UNION ALL
SELECT DISTINCT /* Journals for unreconciled Payroll transactions */
    CEL_SOURCE.meaning ORIGIN
  , CEL_TYPE.meaning TRX_TYPE
  , to_char(CE_PR.CHECK_NUMBER) DOC_NUMBER
  , TO_CHAR(NVL(CE_PR.CLEARED_DATE, CE_PR.PAYMENT_DATE),'DD-Mon-YYYY','nls_date_language=American') TRX_DATE
  , decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS /* 22550369 include FBANK */
  ,(NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)) entered_amount  -- added w.r.t v1
  ,XAL.CURRENCY_CODE curr            -- added w.r.t v1  
  , 0  BANK
  , 'Line ' || GJL.je_Line_num || ',' || GJH.Name ||','|| GJB.NAME STATEMENT_NUMBER
  , GJH.NAME JOURNAL_NAME
  , GJB.NAME BATCH_NAME
FROM PAY_CE_TRANSACTIONS CE_PR
  , pay_xla_events pe
  , xla_ae_headers xah
  , XLA_AE_LINES XAL
  , GL_IMPORT_REFERENCES GLIR /* bug 15858278 - gl/xla tables added */
  , GL_JE_LINES GJL
  , GL_JE_HEADERS GJH
  , GL_JE_BATCHES GJB
  , CE_INTERNAL_BANK_ACCTS_V CE_BA
  , CE_TRX_TYPE_MAPPING CTTM
  , CE_LOOKUPS CEL_SOURCE
  , CE_LOOKUPS CEL_TYPE
WHERE CE_PR.RECON_FLAG = 'N'
AND CE_PR.PAYMENT_STATUS = 'PAID'
AND CE_BA.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND CE_BA.PAY_USE_ALLOWED_FLAG = 'Y'
AND CE_BA.BANK_ACCOUNT_ID = CE_PR.PAYER_BANK_ACCOUNT_ID
AND CTTM.PAY_PAYMENT_TYPE_ID(+) = CE_PR.PAYMENT_TYPE_ID
AND NVL(CTTM.ACTIVE_FLAG (+), 'Y') = 'Y'
AND CEL_TYPE.LOOKUP_CODE (+)= CTTM.TRX_TYPE
AND CEL_TYPE.LOOKUP_TYPE (+)= 'CE_TRX_TYPE'
AND CE_BA.ASSET_CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID
AND CEL_SOURCE.LOOKUP_TYPE = 'CE_UNRECON_SOURCE'
AND CEL_SOURCE.LOOKUP_CODE = 'PR'
AND XAL.CODE_COMBINATION_ID = CE_BA.ASSET_CODE_COMBINATION_ID
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND XAH.APPLICATION_ID = 801
AND XAL.APPLICATION_ID = 801
AND XAL.GL_SL_LINK_TABLE = GLIR.GL_SL_LINK_TABLE
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJB.STATUS = 'P'
AND XAH.GL_TRANSFER_STATUS_CODE = 'Y'
AND PE.EVENT_ID = XAH.EVENT_ID
AND EXISTS
    (SELECT 1
     FROM pay_payroll_rel_actions ra,
          pay_action_interlocks int1,
          pay_action_interlocks int2,
          pay_action_interlocks int3
     WHERE ce_pr.pre_payment_id = ra.pre_payment_id
      AND ce_pr.source_type = 'REL_ACTION'
      AND int1.locked_action_id = ra.payroll_rel_action_id
      AND int2.locked_action_id = int1.locking_action_id
      AND int2.locking_action_id = pe.payroll_rel_action_id
      AND int3.locked_action_id = ra.payroll_rel_action_id
      AND ce_pr.payroll_rel_action_id = int3.locking_action_id
      UNION ALL 
    SELECT 1
     FROM pay_object_actions ra,
          pay_action_interlocks int1,
          pay_action_interlocks int2,
          pay_action_interlocks int3
     WHERE ce_pr.pre_payment_id = ra.object_id
      AND ce_pr.source_type = 'OBJ_ACTION'
      AND int1.locked_action_id = ra.object_action_id
      AND int2.locked_action_id = int1.locking_action_id
      AND int2.locking_action_id = pe.payroll_rel_action_id
      AND int3.locked_action_id = ra.object_action_id
      AND ce_pr.payroll_rel_action_id = int3.locking_action_id
)
UNION ALL
SELECT /* Journals for unreconciled XT transactions */
    CEL_SOURCE.Meaning ORIGIN
  , CEL_TYPE.Meaning TRX_TYPE
  , TO_CHAR(EXT.REFERENCE_TEXT) DOC_NUMBER
  , TO_CHAR(EXT.TRANSACTION_DATE,'DD-Mon-YYYY','nls_date_language=American') TRX_DATE
  , /* 22550369 include FBANK */ decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS
  ,(NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)) entered_amount  -- added w.r.t v1
  ,XAL.CURRENCY_CODE curr            -- added w.r.t v1  
  , 0 BANK
  , 'Line ' || GJL.je_Line_num ||',' ||GJH.Name||','||GJB.NAME STATEMENT_NUMBER
  , GJH.NAME JOURNAL_NAME
  , GJB.NAME BATCH_NAME
FROM CE_EXTERNAL_TRANSACTIONS EXT
  , XLA_TRANSACTION_ENTITIES TRX
  , XLA_AE_HEADERS XAH
  , XLA_AE_LINES XAL
  , GL_IMPORT_REFERENCES GLIR
  , GL_JE_LINES GJL
  , GL_JE_HEADERS GJH
  , GL_JE_BATCHES GJB
  , CE_INTERNAL_BANK_ACCTS_V ACCT
  , CE_LOOKUPS CEL_SOURCE
  , CE_LOOKUPS CEL_TYPE
WHERE  GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJH.JE_SOURCE = 'Cash Management'
AND GJL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
AND GJL.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND GJB.JE_BATCH_ID  = GJH.JE_BATCH_ID
AND GJB.STATUS = 'P'
AND GLIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GLIR.JE_LINE_NUM  = GJL.JE_LINE_NUM
AND XAL.APPLICATION_ID = 260
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE IN ('XLAJEL')
AND XAL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XAH.APPLICATION_ID = 260
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.ENTITY_ID = TRX.ENTITY_ID
AND TRX.APPLICATION_ID  = 260
AND TRX.ENTITY_CODE = 'CE_EXTERNAL'
AND TRX.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND NVL(TRX.SOURCE_ID_INT_1, -99) = EXT.TRANSACTION_ID
AND EXT.STATUS = 'UNR'
AND CEL_SOURCE.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL_SOURCE.Lookup_code = 'XT'
AND CEL_TYPE.Lookup_type(+) = 'CE_TRX_TYPE'
AND CEL_TYPE.lookup_code(+) = EXT.TRANSACTION_TYPE
AND ACCT.BANK_ACCOUNT_ID = EXT.BANK_ACCOUNT_ID
AND ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
UNION ALL
SELECT /* unreconciled manual journal entries for single cash CCID bank accounts */
    CEL_SOURCE.MEANING ORIGIN
  , NULL TRX_TYPE
  , GLH.NAME  DOC_NUMBER
  , TO_CHAR(GLL.EFFECTIVE_DATE,'DD-Mon-YYYY','nls_date_language=American') TRX_DATE
  , decode(:gc_bank_type, 'FBANK', (NVL(GLL.ENTERED_DR,0)- NVL(GLL.ENTERED_CR ,0)), (NVL(GLL.ACCOUNTED_DR,0)- NVL(GLL.ACCOUNTED_CR ,0))) BOOKS
  ,(NVL(GLL.ENTERED_DR,0)- NVL(GLL.ENTERED_CR ,0)) entered_amount  -- added w.r.t v1
  ,GLL.CURRENCY_CODE curr            -- added w.r.t v1  
  , 0 BANK
  , /* 22550369 include FBANK */ 'Line ' || GLL.je_Line_num || ',' || GLH.Name || ',' || GJB.NAME STATEMENT_NUMBER
  , GLH.NAME JOURNAL_NAME
  , GJB.NAME BATCH_NAME
FROM GL_JE_LINES GLL
  , GL_JE_HEADERS  GLH
  , GL_JE_BATCHES GJB
  , GL_LEDGERS GL
  , CE_INTERNAL_BANK_ACCTS_V ACCT
  , CE_LOOKUPS CEL_SOURCE
WHERE ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND ACCT.MULTI_CASH_RECON_ENABLED_FLAG = 'N'
AND GLL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
AND GLL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND GLH.JE_SOURCE  NOT IN ('Cash Management','Receivables','Payables','Revaluation','Payroll')
AND ((GLH.JE_FROM_SLA_FLAG is NULL) or (GLH.JE_FROM_SLA_FLAG='N'))
AND GJB.JE_BATCH_ID = GLH.JE_BATCH_ID
AND GLL.JE_HEADER_ID = GLH.JE_HEADER_ID
AND GLL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND CEL_SOURCE.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL_SOURCE.lookup_code = 'GL'
AND GJB.STATUS = 'P'
AND GLL.CURRENCY_CODE = decode(ACCT.CURRENCY_CODE, GL.CURRENCY_CODE, GLL.CURRENCY_CODE, ACCT.CURRENCY_CODE) /* bug 30231283 ADDED */
/* bug 22635379, filtering out the journals which are reconciled. Journal reconciliation is enabled by fin-514 project.  */
AND NOT EXISTS(
      SELECT 1
      FROM CE_RECON_HISTORY_ITEMS CRHI
      WHERE CRHI.SOURCE_ID = GLL.JE_HEADER_ID
      AND CRHI.SOURCE_LINE_ID = GLL.JE_LINE_NUM
      AND CRHI.RECON_SOURCE = 'ORA_GL'
      AND CRHI.CLEARED_DATE <= :GC_TO_DATE)
/* begin FIN-1057 exclude reversed journals */
AND (
      cep_standard.is_feature_enabled('FSM', 'ORA_EXCLUDE_REVERSAL_JOURNAL_ENTRIES_FROM_BANK_STATEMENT_RECONCILIATION') = 'N'
   OR (GLH.accrual_rev_je_header_id is null AND GLH.reversed_je_header_id is null)
) /* end FIN-1057 */
UNION ALL
SELECT /* unreconciled manual journal entries for multi cash CCID bank accounts */
    CEL_SOURCE.MEANING ORIGIN
  , NULL TRX_TYPE
  , GLH.NAME  DOC_NUMBER
  , TO_CHAR(GLL.EFFECTIVE_DATE,'DD-Mon-YYYY','nls_date_language=American') TRX_DATE
  , decode(:gc_bank_type, 'FBANK', (NVL(GLL.ENTERED_DR,0)- NVL(GLL.ENTERED_CR ,0)), (NVL(GLL.ACCOUNTED_DR,0)- NVL(GLL.ACCOUNTED_CR ,0))) BOOKS
  ,(NVL(GLL.ENTERED_DR,0)- NVL(GLL.ENTERED_CR ,0)) entered_amount  -- added w.r.t v1
  ,GLL.CURRENCY_CODE curr            -- added w.r.t v1
  , 0 BANK
  , /* 22550369 include FBANK */ 'Line ' || GLL.je_Line_num || ',' || GLH.Name || ',' || GJB.NAME STATEMENT_NUMBER
  , GLH.NAME JOURNAL_NAME
  , GJB.NAME BATCH_NAME
FROM GL_JE_LINES GLL
  , GL_JE_HEADERS  GLH
  , GL_JE_BATCHES GJB
  , GL_LEDGERS GL
  , CE_INTERNAL_BANK_ACCTS_V ACCT
  , CE_BANK_ACCOUNT_CASH_CCIDS CE_CCID
  , CE_LOOKUPS CEL_SOURCE
WHERE ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND ACCT.MULTI_CASH_RECON_ENABLED_FLAG = 'Y'
AND ACCT.BANK_ACCOUNT_ID = CE_CCID.BANK_ACCOUNT_ID
AND GLL.CODE_COMBINATION_ID = CE_CCID.CASH_CCID
AND GLL.EFFECTIVE_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND GLH.JE_SOURCE  NOT IN ('Cash Management','Receivables','Payables','Revaluation','Payroll')
AND GJB.JE_BATCH_ID = GLH.JE_BATCH_ID
AND GLL.JE_HEADER_ID = GLH.JE_HEADER_ID
AND GLL.LEDGER_ID = GL.LEDGER_ID
AND GL.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND GL.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND CEL_SOURCE.lookup_type = 'CE_UNRECON_SOURCE'
AND CEL_SOURCE.lookup_code = 'GL'
AND GJB.STATUS = 'P'
AND ((GLH.JE_FROM_SLA_FLAG is NULL) or (GLH.JE_FROM_SLA_FLAG='N'))
AND GLL.CURRENCY_CODE = decode(ACCT.CURRENCY_CODE, GL.CURRENCY_CODE, GLL.CURRENCY_CODE, ACCT.CURRENCY_CODE)   --bug 30231283 ADDED
/* bug 22635379, filtering out the journals which are reconciled. Journal reconciliation is enabled by fin-514 project.  */
AND NOT EXISTS
    (
      SELECT 1
      FROM CE_RECON_HISTORY_ITEMS CRHI
      WHERE CRHI.SOURCE_ID    = GLL.JE_HEADER_ID
      AND CRHI.SOURCE_LINE_ID = GLL.JE_LINE_NUM
      AND CRHI.RECON_SOURCE       = 'ORA_GL'
      AND CRHI.CLEARED_DATE <= :GC_TO_DATE
    )
/* begin FIN-1057 exclude reversed journals */
AND (
      cep_standard.is_feature_enabled('FSM', 'ORA_EXCLUDE_REVERSAL_JOURNAL_ENTRIES_FROM_BANK_STATEMENT_RECONCILIATION') = 'N'
   OR (GLH.accrual_rev_je_header_id is null AND GLH.reversed_je_header_id is null)
) /* end FIN-1057 */
UNION ALL
SELECT /* Manual journals from SLA - single cash CCID bank accounts */
    CEL_SOURCE.MEANING ORIGIN
  , NULL TRX_TYPE
  , XAH.DESCRIPTION DOC_NUMBER
  , TO_CHAR(XAL.ACCOUNTING_DATE,'DD-Mon-YYYY','nls_date_language=American') TRX_DATE
  , decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS
  ,(NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)) entered_amount  -- added w.r.t v1
  ,XAL.CURRENCY_CODE curr            -- added w.r.t v1
  , 0 BANK
  , 'Line ' || XAL.AE_LINE_NUM || ',' || XAH.DESCRIPTION || ',' || GJB.NAME STATEMENT_NUMBER
  , XAH.DESCRIPTION JOURNAL_NAME
  , GJB.NAME BATCH_NAME
FROM XLA_AE_HEADERS XAH
  , XLA_AE_LINES XAL
  , GL_IMPORT_REFERENCES GLIR
  , GL_JE_BATCHES GJB
  , CE_INTERNAL_BANK_ACCTS_V ACCT
  , CE_LOOKUPS CEL_SOURCE
WHERE ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND ACCT.MULTI_CASH_RECON_ENABLED_FLAG = 'N'
AND XAL.CODE_COMBINATION_ID = ACCT.ASSET_CODE_COMBINATION_ID
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.EVENT_TYPE_CODE = 'MANUAL'
AND XAH.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GLIR.GL_SL_LINK_TABLE
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND GJB.STATUS = 'P'
AND CEL_SOURCE.LOOKUP_TYPE = 'CE_UNRECON_SOURCE'
AND CEL_SOURCE.LOOKUP_CODE = 'GL'
UNION ALL
SELECT /* Manual journals from SLA - multi cash CCID bank accounts */
    CEL_SOURCE.MEANING ORIGIN
  , NULL TRX_TYPE
  , XAH.DESCRIPTION DOC_NUMBER
  , TO_CHAR(XAL.ACCOUNTING_DATE,'DD-Mon-YYYY','nls_date_language=American') TRX_DATE
  , decode(:gc_bank_type, 'FBANK', (NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)), (NVL(XAL.ACCOUNTED_DR,0)- NVL(XAL.ACCOUNTED_CR,0))) BOOKS
  ,(NVL(XAL.ENTERED_DR,0)- NVL(XAL.ENTERED_CR ,0)) entered_amount  -- added w.r.t v1
  ,XAL.CURRENCY_CODE curr            -- added w.r.t v1  
  , 0 BANK
  , 'Line ' || XAL.AE_LINE_NUM || ',' || XAH.DESCRIPTION || ',' || GJB.NAME STATEMENT_NUMBER
  , XAH.DESCRIPTION JOURNAL_NAME
  , GJB.NAME BATCH_NAME
FROM XLA_AE_HEADERS XAH
  , XLA_AE_LINES XAL
  , GL_IMPORT_REFERENCES GLIR
  , GL_JE_BATCHES GJB
  , CE_INTERNAL_BANK_ACCTS_V ACCT
  , CE_LOOKUPS CEL_SOURCE
  , CE_BANK_ACCOUNT_CASH_CCIDS CE_CCID
WHERE ACCT.BANK_ACCOUNT_ID = :P_BANK_ACCOUNT_ID
AND ACCT.MULTI_CASH_RECON_ENABLED_FLAG = 'Y'
AND ACCT.BANK_ACCOUNT_ID = CE_CCID.BANK_ACCOUNT_ID
AND XAL.CODE_COMBINATION_ID = CE_CCID.CASH_CCID
AND XAL.ACCOUNTING_DATE BETWEEN :GC_FROM_DATE AND :GC_TO_DATE
AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
AND XAH.LEDGER_ID = :GC_SET_OF_BOOKS_ID
AND XAH.APPLICATION_ID = XAL.APPLICATION_ID
AND XAL.GL_SL_LINK_ID = GLIR.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_TABLE = GLIR.GL_SL_LINK_TABLE
AND GJB.JE_BATCH_ID = GLIR.JE_BATCH_ID
AND CEL_SOURCE.LOOKUP_TYPE = 'CE_UNRECON_SOURCE'
AND CEL_SOURCE.LOOKUP_CODE = 'GL'
AND GJB.STATUS = 'P'
AND XAH.EVENT_TYPE_CODE = 'MANUAL'
)
order by 1,2,4

:GC_FROM_DATE :GC_SET_OF_BOOKS_ID :GC_TO_DATE :P_BANK_ACCOUNT_ID :gc_bank_type

The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.

●— fact → dimension join

Element	Type	Definition
CE_STATEMENT_HEADERS	dimension	dimension
CE_LOOKUPS	dimension	dimension
AP_CHECKS_ALL	dimension	dimension
XLA_TRANSACTION_ENTITIES	dimension	dimension
Gl Cash Balance	measure	measure
Bank Balance	measure	measure

Runs on your cloud warehouse — Snowflake, BigQuery, Redshift, or Synapse on AWS, Google Cloud, Azure, or any provider. Reconciled to the source control total — 0% variance by design. You own the code, the model, and the data.

How the data gets here: a BICC bulk extract of the source tables above, on the same pattern for every report. See the extraction pattern & data flow →

See the complete model

How this report's fact and dimensions fit the full picture, via conformed keys.

Cash Management data model →Enterprise model →

Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.

Table	Reporting columns	Subject areas
CE_STATEMENT_LINES	23	3
CE_STATEMENT_HEADERS	7	4
CE_LOOKUPS	Setup / configuration table — joined for reference, not exposed for analytics
AP_CHECKS_ALL	44	6
XLA_TRANSACTION_ENTITIES	2	3
XLA_AE_HEADERS	16	19
XLA_AE_LINES	23	17
GL_IMPORT_REFERENCES	Setup / configuration table — joined for reference, not exposed for analytics
GL_JE_LINES	26	2
GL_JE_HEADERS	34	2
GL_JE_BATCHES	14	2
CE_INTERNAL_BANK_ACCTS_V	10	11

Reporting columns = fields the report selects that are exposed as analytics attributes; subject areas = the OTBI subject areas the table appears in. Setup and configuration tables (master data, ledger and book setup, lookups) are referenced by the report's joins but aren't exposed as analytics columns or subject areas — that's expected, not a gap.