Auto Reconciliation Execution Report

Cash Management

The outcome of the automatic bank-reconciliation run — which imported statement lines matched system transactions, which were left unreconciled, and why — by bank account and statement.

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

Sample build · illustrative

Filters

Bank Account Num

1003

Branch Name

Globex Holdings

Bu Name

Globex Holdings

Date From

2026-02-28

Date To

2026-02-28

Recon

Sample

4,820

Statement lines

96.2%

Auto-matched

183

Unreconciled

Bank Account	Statement Date	Statement Line	Amount	Match Status	Exception Reason
1000-2100-000	2026-04-30	Sample	$1,240,500.00	Open	Standard
1000-5400-000	2026-03-31	—	$842,150.75	Posted	Corporate
1000-1410-000	2026-02-28	Sample	$96,400.00	Validated	Standard
2000-2100-000	2026-01-31	—	$1,005,233.10	Open	Default
1000-6300-000	2025-12-31	Sample	$58,720.40	Paid	Standard
1000-2100-000	2026-04-30	Sample	$1,240,500.00	Open	Standard

AI Analyst · active

reading

The run matches imported lines in CE_STATEMENT_LINES_INT against payments in AP_CHECKS_ALL and receipts in AR_CASH_RECEIPTS_ALL using the configured matching rules. 96.2% matched automatically; the 183 unreconciled lines land in CE_RECON_EXCEPTIONS.

flag

Of the 183 exceptions, 140 share one reason — an amount-tolerance mismatch on a single bank account — which points to a matching rule that is too tight rather than 140 genuine breaks.

root cause & next step

Widen the amount tolerance on that account's rule, or add a date-window rule, then re-run; the matched rate should absorb the bulk of those 140 lines.

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

CE_STATEMENT_HEADERS_INT
CE_BANK_ACCOUNTS
CE_BANK_ACCT_USES_ALL
HR_OPERATING_UNITS
CE_STMT_IMPORT_ERRORS
CE_BANK_BRANCHES_V
CE_STATEMENT_LINES_INT
FND_LOOKUP_VALUES_VL
CE_LOOKUPS
CE_RECON_EXCEPTIONS
AP_CHECKS_ALL
AR_CASH_RECEIPTS_ALL

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

-- Interface Header Details
select * from 
(
SELECT   distinct 
 --sh.statement_header_int_id
ba.bank_account_num Bank_Account_Number
,ba.bank_account_name Bank_Account_Name
,ba.currency_code Bank_Account_Currency
,sh.statement_number Statement_ID  
,to_char(sh.statement_date,'DD-Mon-YYYY','nls_date_language=American') statement_date
,to_char(sh.electronic_seq_num)      Document_Number
,null  line_no
,null  Transaction_Number
,to_char(sh.statement_date,'DD-Mon-YYYY','nls_date_language=American')  trx_date
,null                 trx_type
,null                 Agent_name
,null                 Agent_bank_account
,null                 description
,sh.currency_code     currency_code
,null                 amount 
,e.error_msg          Error
,null recon_status
,null Recon_Trx_source
,null RECON_REF
from
CE_STATEMENT_HEADERS_INT sh
,ce_bank_accounts ba
,ce_bank_acct_uses_all bau
,hr_operating_units hou
,ce_stmt_import_errors e
,ce_bank_branches_v cbbv
where 1=1
--and ba.bank_account_name ='1829-101 MITU 130BA1022USD GENERAL OPERATING ACC'
and sh.statement_header_int_id = e.statement_header_int_id
and e.line_number is null
and sh.bank_account_num = ba.bank_account_num
and NOT EXISTS (select 1 from ce_statement_lines_int sl where sl.statement_header_int_id = sh.statement_header_int_id)
and ba.bank_account_id = bau.bank_account_id
and bau.org_id = hou.organization_id
and nvl(bau.end_date,sysdate+1)>=sysdate
and ba.bank_branch_id = cbbv.branch_party_id 
and hou.name = nvl(:P_BU_NAME,hou.name)
and cbbv.bank_branch_name = NVL(:P_BRANCH_NAME,cbbv.bank_branch_name)
and ba.bank_account_num = nvl(:P_BANK_ACCOUNT_NUM,ba.bank_account_num)
and TRUNC(sh.statement_date) BETWEEN nvl(:P_DATE_FROM,TRUNC(sh.statement_date)) AND nvl(:P_DATE_TO,TRUNC(sh.statement_date))
and EXISTS (SELECT 1
						FROM 
						 fusion.fun_user_role_data_asgnmnts role
						,fusion.per_users pu
						,fusion.per_roles_dn_vl pr
						WHERE role.org_id = hou.organization_id
						AND pu.user_guid = role.user_guid
						AND pr.role_common_name = role.ROLE_NAME
						and role.active_flag ='Y' 
						and pu.username = fnd_global.user_name
						)
AND HOU.NAME IN (SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES_VL FLV WHERE  FLV.LOOKUP_TYPE = 'BU_REGION_REPORTING' AND FLV.ENABLED_FLAG='Y'
                 AND (FLV.DESCRIPTION IN (:P_REGION) OR 'All' IN ('All'||:P_REGION)))						

UNION ALL

--Interface Line Details
SELECT distinct 
 --sh.statement_header_int_id   Statement_ID
ba.bank_account_num      Bank_Account_Number  
,ba.bank_account_name     bank_account_name
,ba.currency_code         Bank_Account_Currency 
,sh.statement_number      Statement_ID  
,to_char(sh.statement_date,'DD-Mon-YYYY','nls_date_language=American') statement_date
,to_char(sh.electronic_seq_num)      Document_Number
,sl.line_number             line_no
,null  Transaction_Number
,to_char(sh.statement_date,'DD-Mon-YYYY','nls_date_language=American')    trx_date
,(select  meaning from CE_LOOKUPS where LOOKUP_TYPE  ='CE_TRX_TYPE'  and lookup_code =  sl.TRX_TYPE and ENABLED_FLAG='Y' and rownum=1)  trx_type
,sl.customer_reference Agent_name
,ba.bank_account_num   Agent_bank_account
,REPLACE(sl.addenda_txt,CHR(10),' ')       description
,sh.currency_code     currency_code
,sl.amount amount
,e.error_msg       Error
,null recon_status
,null Recon_Trx_source
,null RECON_REF
from
CE_STATEMENT_HEADERS_INT sh
,ce_statement_lines_int sl
,ce_bank_accounts ba
,ce_bank_acct_uses_all bau
,hr_operating_units hou
,ce_stmt_import_errors e
,ce_bank_branches_v cbbv
where 1=1
--and ba.bank_account_name ='1829-101 MITU 130BA1022USD GENERAL OPERATING ACC'
and sh.statement_header_int_id = sl.statement_header_int_id
and sh.statement_header_int_id = e.statement_header_int_id
and e.line_number = sl.line_number
and sh.bank_account_num = ba.bank_account_num
and ba.bank_account_id = bau.bank_account_id
and bau.org_id = hou.organization_id
and nvl(bau.end_date,sysdate+1)>=sysdate
and ba.bank_branch_id = cbbv.branch_party_id 
and hou.name = nvl(:P_BU_NAME,hou.name)
and cbbv.bank_branch_name = NVL(:P_BRANCH_NAME,cbbv.bank_branch_name)
and ba.bank_account_num = nvl(:P_BANK_ACCOUNT_NUM,ba.bank_account_num)
and TRUNC(sh.statement_date) BETWEEN nvl(:P_DATE_FROM,TRUNC(sh.statement_date)) AND nvl(:P_DATE_TO,TRUNC(sh.statement_date))
and EXISTS (SELECT 1
						FROM 
						 fusion.fun_user_role_data_asgnmnts role
						,fusion.per_users pu
						,fusion.per_roles_dn_vl pr
						WHERE role.org_id = hou.organization_id
						AND pu.user_guid = role.user_guid
						AND pr.role_common_name = role.ROLE_NAME
						and role.active_flag ='Y' 
						and pu.username = fnd_global.user_name
						)
AND HOU.NAME IN (SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES_VL FLV WHERE  FLV.LOOKUP_TYPE = 'BU_REGION_REPORTING' AND FLV.ENABLED_FLAG='Y'
                 AND (FLV.DESCRIPTION IN (:P_REGION) OR 'All' IN ('All'||:P_REGION)))
				 
UNION ALL

--/* All Reconciliation errors */ 

SELECT  distinct
	 ba.bank_account_num     bank_acc   
	,ba.bank_account_name     bank_account_name 
	,ba.currency_code         acc_currency
	,sh.statement_number      statement_no 
	,to_char(sh.statement_date,'DD-Mon-YYYY','nls_date_language=American')        statement_date 
	,sh.electronic_seq_num    doc_sequence_value
	,sl.line_number           line_no 
	,sl.recon_reference       Transaction_Number
	,to_char(sl.booking_date,'DD-Mon-YYYY','nls_date_language=American') trx_date
	,(select  meaning from CE_LOOKUPS where LOOKUP_TYPE  ='CE_TRX_TYPE'  and lookup_code =  sl.TRX_TYPE and ENABLED_FLAG='Y' and rownum=1)trx_type
	,sl.customer_reference Agent_name  
	,ba.bank_account_num Agent_bank_account 
	,REPLACE(sl.addenda_txt,CHR(10),' ') description 
	,nvl(sl.trx_curr_code, ba.currency_code) CURRENCY_CODE 
	,sl.amount 
	,(select listagg (l.meaning,', ') within group ( order by statement_line_id )
	from CE_RECON_EXCEPTIONS e , CE_LOOKUPS l
	where e.exception_type= l.lookup_code
	and l.lookup_type ='CE_RECON_EXCEPTION_TYPE'
	and  statement_line_id = sl.statement_line_id
	) Error
    ,(select l1.meaning from ce_lookups l1 where l1.lookup_type = 'LINE_RECON_STATUS' and l1.lookup_code = sl.recon_status) recon_status
	,(DECODE (rhi.RECON_SOURCE, 'AP', 'Payables'
                          , 'AR', 'Receivables'
                          , 'XT'  ,'External Transaction'
						  , 'ORA_GL', 'Journal'
						  , '' ) ) RECON_TRX_SOURCE
    ,(DECODE (rhi.RECON_SOURCE , 'AP', (select to_char(CHECK_NUMBER) from AP_CHECKS_ALL where CHECK_ID = rhi.source_id )
                          , 'AR', (select RECEIPT_NUMBER from AR_CASH_RECEIPTS_ALL where CASH_RECEIPT_ID = rhi.source_id)
                          , 'XT', (select REFERENCE_TEXT from CE_EXTERNAL_TRANSACTIONS where EXTERNAL_TRANSACTION_ID = rhi.source_id)
						  , 'ORA_GL',(select gjh.NAME ||' - line '||rhi.source_line_id from  gl_je_headers gjh where gjh.je_header_id=rhi.source_id ) 
						  , '' ) )	RECON_REF					  

from 
CE_BANK_ACCOUNTS     ba
,ce_bank_acct_uses_all bau
,ce_bank_branches_v   bb
,CE_STATEMENT_HEADERS sh 
,ce_statement_lines sl
,hr_operating_units hou
,ce_recon_history_items rhi
where 1=1
and ba.bank_account_id = bau.bank_account_id
and bau.org_id = hou.organization_id
and nvl(bau.end_date,sysdate+1)>=sysdate
and bb.branch_party_id = ba.bank_branch_id
and ba.bank_account_id = sh.bank_account_id
and ba.account_classification = 'INTERNAL'
--and sh.statement_header_id = 300000011335529
and sh.statement_header_id=sl.statement_header_id
and sl.recon_history_id = rhi.recon_history_id(+)
and rhi.RECON_SOURCE(+) !='BS'	
and ( (:P_RECON='Yes') or ( sl.recon_status!='REC'and :P_RECON='No') ) 
and hou.name = nvl(:P_BU_NAME,hou.name)
and bb.bank_branch_name = NVL(:P_BRANCH_NAME,bb.bank_branch_name)
and ba.bank_account_num = nvl(:P_BANK_ACCOUNT_NUM,ba.bank_account_num)
and TRUNC(sh.statement_date) BETWEEN nvl(:P_DATE_FROM,TRUNC(sh.statement_date)) AND nvl(:P_DATE_TO,TRUNC(sh.statement_date))
and EXISTS (SELECT 1
						FROM 
						 fusion.fun_user_role_data_asgnmnts role
						,fusion.per_users pu
						,fusion.per_roles_dn_vl pr
						WHERE role.org_id = hou.organization_id
						AND pu.user_guid = role.user_guid
						AND pr.role_common_name = role.ROLE_NAME
						and role.active_flag ='Y' 
						and pu.username = fnd_global.user_name
						)
AND HOU.NAME IN (SELECT LOOKUP_CODE FROM FND_LOOKUP_VALUES_VL FLV WHERE  FLV.LOOKUP_TYPE = 'BU_REGION_REPORTING' AND FLV.ENABLED_FLAG='Y'
                 AND (FLV.DESCRIPTION IN (:P_REGION) OR 'All' IN ('All'||:P_REGION)))						
)
order by 1,5,10 asc

:P_BANK_ACCOUNT_NUM :P_BRANCH_NAME :P_BU_NAME :P_DATE_FROM :P_DATE_TO :P_RECON :P_REGION

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_BANK_ACCOUNTS	dimension	dimension
CE_BANK_ACCT_USES_ALL	dimension	dimension
HR_OPERATING_UNITS	dimension	dimension
CE_STMT_IMPORT_ERRORS	dimension	dimension
Amount	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_HEADERS_INT	Setup / configuration table — joined for reference, not exposed for analytics
CE_BANK_ACCOUNTS	9	12
CE_BANK_ACCT_USES_ALL	Setup / configuration table — joined for reference, not exposed for analytics
HR_OPERATING_UNITS	Setup / configuration table — joined for reference, not exposed for analytics
CE_STMT_IMPORT_ERRORS	Setup / configuration table — joined for reference, not exposed for analytics
CE_BANK_BRANCHES_V	2	12
CE_STATEMENT_LINES_INT	Setup / configuration table — joined for reference, not exposed for analytics
FND_LOOKUP_VALUES_VL	Setup / configuration table — joined for reference, not exposed for analytics
CE_LOOKUPS	Setup / configuration table — joined for reference, not exposed for analytics
CE_RECON_EXCEPTIONS	Setup / configuration table — joined for reference, not exposed for analytics
AP_CHECKS_ALL	44	6
AR_CASH_RECEIPTS_ALL	25	9

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.