Analytics Catalog/Oracle Fusion ERP/General Ledger/Journal Entry Reversal Detail Report
Explore the catalogReportsModulesEnterprise modelOTBI subject areasBICC PVOs
Oracle Fusion · General Ledger

Journal Entry Reversal Detail Report

General Ledger

Every reversed journal and its reversal — the original entry, the reversing entry, the reason, and the period each hit — so finance can confirm reversals landed in the right period and aren't being used to move results between months.

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.

Sample build of the Journal Entry Reversal Detail Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Journal Entry Reversal Detail Report
Sample build · illustrative
Filters
Category
Vehicles
From Period
FEB-26
Ledger
US Primary
Reversal Status
Validated
Source
Sample
To Period
FEB-26
148
Reversals
$5.10M
Reversed value
22
Cross-period
Original JournalReversal JournalReasonOriginal PeriodReversal PeriodAmount
SampleSampleStandardAPR-26APR-26$1,240,500.00
——CorporateMAR-26MAR-26$842,150.75
SampleSampleStandardFEB-26FEB-26$96,400.00
——DefaultJAN-26JAN-26$1,005,233.10
SampleSampleStandardDEC-25DEC-25$58,720.40
SampleSampleStandardAPR-26APR-26$1,240,500.00
AI Analyst · active
reading

The report pairs each reversed entry in GL_JE_HEADERS with its reversal, showing source, reason, and the two periods involved.

flag

22 reversals cross into a later period rather than reversing within the same one — fine for accruals, but a cluster near period end can shift results between months.

root cause & next step

Confirm the cross-period reversals are accruals, not earnings management; if a manual source drives them, route those through a controlled accrual process.

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
WITH REV_STATUS AS
(
SELECT 
je_header_id,
(CASE WHEN ACCRUAL_REV_STATUS = 'R' THEN 'Reversed'
ELSE 'Not Reversed' END) JOURNAL_REVERSAL_STATUS
FROM GL_JE_HEADERS
),
SECURITY_TBL AS
(
SELECT DISTINCT GL.LEDGER_ID
FROM GL_LEDGERS GL
,(
SELECT DISTINCT GL.LEDGER_ID
FROM 
GL_LEDGERS GL,
GL_ACCESS_SETS GAS,
	FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
	PER_ROLES_DN PRD,
	PER_USER_ROLES PUR,
	PER_USERS PU

WHERE 
UPPER(GL.LEDGER_CATEGORY_CODE) <> 'NONE'
AND GAS.ACCESS_SET_ID=GL.IMPLICIT_ACCESS_SET_ID
	AND (GAS.ACCESS_SET_ID =FURDA.ACCESS_SET_ID OR GL.LEDGER_ID=FURDA.LEDGER_ID)
	AND UPPER(FURDA.role_name) in ('XXC_GL_INQUIRY_JOB','XXC_GENERAL_ACCOUNTANT_JOB','XXC_GENERAL_ACCOUNTING_MANAGER_JOB')
	AND UPPER(FURDA.ACTIVE_FLAG) = 'Y'
	AND UPPER(FURDA.ROLE_NAME) = UPPER(PRD.ROLE_COMMON_NAME)
	AND PRD.ROLE_ID = PUR.ROLE_ID
	AND PUR.USER_ID = PU.USER_ID
	AND  UPPER(PU.USERNAME) = UPPER(:xdo_user_name)
	AND PU.USER_GUID = FURDA.USER_GUID

) ST
WHERE 
1=1

AND ST.LEDGER_ID=GL.LEDGER_ID
)

SELECT 
	LEDGER_NAME,
	PERIOD,
	JOURNAL_BATCH_NAME,
	JOURNAL_NAME,
	JOURNAL_CREATED_BY,
	JOURNAL_SOURCE,
	JOURNAL_CATEGORY,
	BATCH_STATUS,
	JOURNAL_CURRENCY,
	JOURNAL_ENTERED_AMOUNT,
	JOURNAL_ACCOUNTED_AMOUNT,
	LEDGER_CURRENCY,
	JOURNAL_REVERSAL_STATUS,
	REVERSED_JOURNAL_NAME,
	REVERSAL_PERIOD,
	REVERSED_JOURNAL_BATCH_STATUS,
	REVERSAL_JOURNAL_CREATED_BY,
	DOC_SEQ_NUM,
	DOC_SEQ_NAME,
	REV_DOC_SEQ_NUM,
	REV_DOC_SEQ_NAME,
	P_LEDGER,
	REVERSED_JE_HEADER_ID
from
(

SELECT 
	GL.name LEDGER_NAME,
	GP.period_name PERIOD,
	GJB2.name JOURNAL_BATCH_NAME,
	GJH2.name JOURNAL_NAME,
	GJB2.created_by JOURNAL_CREATED_BY,
	GJSV2.USER_JE_SOURCE_NAME JOURNAL_SOURCE,
	GJCB.USER_JE_CATEGORY_NAME JOURNAL_CATEGORY,
	--GJB2.status BATCH_STATUS,
	FLV2.description BATCH_STATUS,
	GJH2.currency_code JOURNAL_CURRENCY,
	GJH2.running_total_dr JOURNAL_ENTERED_AMOUNT,
	GJH2.running_total_accounted_dr JOURNAL_ACCOUNTED_AMOUNT,
	GL.currency_code LEDGER_CURRENCY,
	RS.JOURNAL_REVERSAL_STATUS,
	GJH1.name REVERSED_JOURNAL_NAME,
	GJH2.accrual_rev_period_name REVERSAL_PERIOD,
	--GJB1.status REVERSED_JOURNAL_BATCH_STATUS,
	FLV1.description REVERSED_JOURNAL_BATCH_STATUS,
	GJB1.created_by REVERSAL_JOURNAL_CREATED_BY,
	GJH2.posting_acct_seq_value DOC_SEQ_NUM,
	FSV2.header_name DOC_SEQ_NAME,
	GJH1.posting_acct_seq_value REV_DOC_SEQ_NUM,
	FSV1.header_name REV_DOC_SEQ_NAME,
	:P_LEDGER P_LEDGER,
	1 REVERSED_JE_HEADER_ID
	
FROM
	gl_je_headers GJH1,
	GL_JE_SOURCES_VL GJSV1,
	gl_je_headers GJH2,
	GL_JE_SOURCES_VL GJSV2,
	gl_je_batches GJB1,
	gl_je_batches GJB2,
	gl_ledgers GL,
	gl_periods GP,
	REV_STATUS RS,
	gl_je_categories_vl GJCB,
	gl_autoreverse_options GAO,
	fun_seq_versions FSV1,
	fun_seq_versions FSV2,
	fnd_lookup_values FLV1,
	fnd_lookup_values FLV2,
	SECURITY_TBL ST

	
WHERE
	1=1
	AND GJH2.accrual_rev_je_header_id 	= GJH1.je_header_id(+)
	AND GJH1.je_batch_id 				= GJB1.je_batch_id(+)
	AND GJH1.je_source					= GJSV1.JE_SOURCE_NAME(+)--new
	AND GJH2.je_batch_id				= GJB2.je_batch_id
	AND GJH2.ledger_id					= GL.ledger_id
	AND GJH2.period_name				= GP.period_name
	AND GJH2.je_header_id				= RS.je_header_id
	AND GJH2.je_category 				= GJCB.je_category_name
	AND GJCB.je_category_name			= GAO.je_category_name
	AND GJH2.je_source					= GJSV2.JE_SOURCE_NAME(+)--new
	--AND GAO.AUTOREVERSE_FLAG			= 'Y'
	AND GJH2.posting_acct_seq_version_id = FSV2.seq_version_id(+)
	AND GJH1.posting_acct_seq_version_id = FSV1.seq_version_id(+)
	--AND RS.JOURNAL_REVERSAL_STATUS		= 'Reversed'
	AND GJB1.status 					= FLV1.lookup_code
	AND FLV1.lookup_type 				= 'MJE_BATCH_STATUS'
	AND FLV1.language					= USERENV('LANG')
	
	AND GJB2.status 					= FLV2.lookup_code
	AND FLV2.lookup_type 				= 'MJE_BATCH_STATUS'
	AND FLV2.language					= USERENV('LANG')
	--AND GP.period_name LIKE '%-24'
	
	AND (GL.name                    	IN (:P_LEDGER)
	OR  'All'                       	IN (:P_LEDGER||'All'))	
	AND GP.start_date					>= (select distinct start_date from gl_periods where period_name = :P_FROM_PERIOD)
	AND GP.end_date						<= (select distinct end_date from gl_periods where period_name = :P_TO_PERIOD)
	AND (GJSV2.USER_JE_SOURCE_NAME             	IN (:P_SOURCE)
	OR  'All'                       	IN (:P_SOURCE||'All'))	
	AND (GJCB.USER_JE_CATEGORY_NAME           	IN (:P_CATEGORY)
	OR  'All'                       	IN (:P_CATEGORY||'All'))	
	AND (RS.JOURNAL_REVERSAL_STATUS     IN (:P_REVERSAL_STATUS)
	OR  'All'                       	IN (:P_REVERSAL_STATUS||'All'))
	AND GL.ledger_id  					= ST.ledger_id
	
UNION 

SELECT 
	GL.name LEDGER_NAME,
	GP.period_name PERIOD,
	GJB2.name JOURNAL_BATCH_NAME,
	GJH2.name JOURNAL_NAME,
	GJB2.created_by JOURNAL_CREATED_BY,
	GJSV2.USER_JE_SOURCE_NAME JOURNAL_SOURCE,
	GJCB.USER_JE_CATEGORY_NAME JOURNAL_CATEGORY,
	FLV2.description BATCH_STATUS,
	GJH2.currency_code JOURNAL_CURRENCY,
	GJH2.running_total_dr JOURNAL_ENTERED_AMOUNT,
	GJH2.running_total_accounted_dr JOURNAL_ACCOUNTED_AMOUNT,
	GL.currency_code LEDGER_CURRENCY,
	RS.JOURNAL_REVERSAL_STATUS,
	NULL REVERSED_JOURNAL_NAME,
	NULL REVERSAL_PERIOD,
	NULL REVERSED_JOURNAL_BATCH_STATUS,
	NULL REVERSAL_JOURNAL_CREATED_BY,
	GJH2.posting_acct_seq_value DOC_SEQ_NUM,
	FSV2.header_name DOC_SEQ_NAME,
	NULL REV_DOC_SEQ_NUM,
	NULL REV_DOC_SEQ_NAME,
	:P_LEDGER P_LEDGER,
	GJH2.REVERSED_JE_HEADER_ID
	
FROM
	
	gl_je_headers GJH2,
	GL_JE_SOURCES_VL GJSV2,
	gl_je_batches GJB2,
	gl_ledgers GL,
	gl_periods GP,
	REV_STATUS RS,
	gl_je_categories_vl GJCB,
	gl_autoreverse_options GAO,
	fun_seq_versions FSV2,
	fnd_lookup_values FLV2,
	SECURITY_TBL ST
	
WHERE
	1=1
	AND GJH2.je_batch_id				= GJB2.je_batch_id
	AND GJH2.ledger_id					= GL.ledger_id
	AND GJH2.period_name				= GP.period_name
	AND GJH2.je_header_id				= RS.je_header_id
	AND GJH2.je_category 				= GJCB.je_category_name
	AND GJCB.je_category_name			= GAO.je_category_name
	AND GJH2.je_source					= GJSV2.JE_SOURCE_NAME--new
	AND GAO.AUTOREVERSE_FLAG			= 'Y'
	AND RS.JOURNAL_REVERSAL_STATUS		= 'Not Reversed'
	AND GJH2.posting_acct_seq_version_id = FSV2.seq_version_id(+)
	AND GJB2.status 					= FLV2.lookup_code
	AND FLV2.lookup_type 				= 'MJE_BATCH_STATUS'
	AND FLV2.language					= USERENV('LANG')
	--AND GP.period_name LIKE '%-24'
	AND (GL.name                    	IN (:P_LEDGER)
	OR  'All'                       	IN (:P_LEDGER||'All'))	
	AND GP.start_date					>= (select distinct start_date from gl_periods where period_name = :P_FROM_PERIOD)
	AND GP.end_date						<= (select distinct end_date from gl_periods where period_name = :P_TO_PERIOD)
	AND (GJSV2.USER_JE_SOURCE_NAME             	IN (:P_SOURCE)
	OR  'All'                       	IN (:P_SOURCE||'All'))	
	AND (GJCB.USER_JE_CATEGORY_NAME           	IN (:P_CATEGORY)
	OR  'All'                       	IN (:P_CATEGORY||'All'))	
	AND (RS.JOURNAL_REVERSAL_STATUS     IN (:P_REVERSAL_STATUS)
	OR  'All'                       	IN (:P_REVERSAL_STATUS||'All'))
	AND GL.ledger_id  					= ST.ledger_id
)
order by 
JOURNAL_REVERSAL_STATUS desc,
REVERSED_JE_HEADER_ID desc
:P_CATEGORY :P_FROM_PERIOD :P_LEDGER :P_REVERSAL_STATUS :P_SOURCE :P_TO_PERIOD :xdo_user_name

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.

GL_LEDGERSdimensionGL_ACCESS_SETSdimensionFUN_USER_ROLE_DATA_ASGNM…dimensionPER_ROLES_DNdimensionGL_JE_HEADERSfact · one row per source transactionAmount
●— fact → dimension join
ElementTypeDefinition
GL_LEDGERSdimensiondimension
GL_ACCESS_SETSdimensiondimension
FUN_USER_ROLE_DATA_ASGNMNTSdimensiondimension
PER_ROLES_DNdimensiondimension
Amountmeasuremeasure
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.
General Ledger 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.

TableReporting columnsSubject areas
GL_JE_HEADERS342
GL_LEDGERS10104
GL_ACCESS_SETSSetup / configuration table — joined for reference, not exposed for analytics
FUN_USER_ROLE_DATA_ASGNMNTSSetup / configuration table — joined for reference, not exposed for analytics
PER_ROLES_DNSetup / configuration table — joined for reference, not exposed for analytics
PER_USER_ROLES160
PER_USERS2227
GL_JE_SOURCES_VLSetup / configuration table — joined for reference, not exposed for analytics
GL_JE_BATCHES142
GL_PERIODSSetup / configuration table — joined for reference, not exposed for analytics
REV_STATUSSetup / configuration table — joined for reference, not exposed for analytics
GL_JE_CATEGORIES_VLSetup / configuration table — joined for reference, not exposed for analytics
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.