Journal Approval Audit History Report

General Ledger

The approval trail for journals — who submitted, who approved or rejected, and when — so finance can prove journals went through the required approvals and spot entries that posted without them.

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

Sample build · illustrative

Filters

Batch Status

Validated

From Period

FEB-26

Ledger

US Primary

To Period

FEB-26

Xdo User Name

Globex Holdings

640

Journals routed

612

Approved

Posted w/o approval

Journal Batch	Submitted By	Approver	Action	Action Date	Amount
Sample	Sample	Sample	Sample	2026-04-30	$1,240,500.00
—	—	—	—	2026-03-31	$842,150.75
Sample	Sample	Sample	Sample	2026-02-28	$96,400.00
—	—	—	—	2026-01-31	$1,005,233.10
Sample	Sample	Sample	Sample	2025-12-31	$58,720.40
Sample	Sample	Sample	Sample	2026-04-30	$1,240,500.00

AI Analyst · active

reading

The report reads GL_JE_ACTION_LOG joined to the approver's HR record, reconstructing each journal's approval path.

flag

Four journals posted without a recorded approval — auto-approved under a rule, or entered by a role that bypasses approval, which is exactly the control auditors test.

root cause & next step

Confirm whether the bypass is intended; if not, tighten the approval rule so journals above the threshold cannot self-approve.

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

GL_LEDGERS
GL_ACCESS_SETS
FUN_USER_ROLE_DATA_ASGNMNTS
PER_ROLES_DN
PER_USER_ROLES
PER_USERS
GL_JE_ACTION_LOG
PER_ALL_ASSIGNMENTS_M
PER_JOBS_F_TL
PER_JOBS_F
GL_DAILY_RATES
GL_JE_BATCHES

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

<![CDATA[WITH 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,
	start_date,
	CREATION_DATE,
	JOURNAL_DATE,
	JOURNAL_BATCH_ID,
	JOURNAL_BATCH_NAME,
	BATCH_APPROVAL_STATUS,
	JOURNAL_HEADER_ID,
	JOURNAL_NAME,
	JOURNAL_SOURCE_NAME,
	JOURNAL_CURRENCY,
	JOURNAL_AMOUNT,
	CONVERSION_DATE,
	CONVERSION_RATE_TYPE,
	CONVERSION_RATE,
	--TRANS_VAL_USD,
	(JOURNAL_AMOUNT*EXCHANGE_RATE) TRANS_VAL_USD,
	WORK_FLOW_START_DATE,
	CREATED_BY,
	JOB_TITLE_OF_CREATED_BY,   --  JOB_TITLE  CREATED
	PREPARER,
	JOB_TITLE_OF_PREPARER,      --  JOB_TITLE  PREPARER
	LEVEL1_APPROVER,
	JOB_TITLE_OF_L1APPROVER JOB_TITLE_OF_LEVE1_APPROVER,  --  JOB_TITLE  L1 Approval
	LEVEL1_APPROVAL_DATE LEVEL1_APPROVAL_DATE,
	
	ROUND(NVL((CASE WHEN LEVEL1_APPROVAL_DATE IS NULL and BATCH_APPROVAL_STATUS not in ('Approved','Not required') THEN (SELECT TRUNC(SYSDATE) - TRUNC(CREATION_DATE) FROM dual) END),0),0) DAYS_LEVEL1_APPROVAL_OUTSTANDING, --REF-289628
	
	LEVEL2_APPROVER,
	JOB_TITLE_OF_L2APPROVER JOB_TITLE_OF_LEVEL2_APPROVER, -- JOB_TITLE  L2 approval
	LEVEL2_APPROVAL_DATE LEVEL2_APPROVAL_DATE,
	--ROUND(NVL((CASE WHEN LEVEL2_APPROVAL_DATE IS NULL THEN (SELECT TRUNC(SysDate)-TRUNC(LEVEL1_APPROVAL_DATE) FROM dual) END ),0),0) DAYS_LEVEL2_APPROVAL_OUTSTANDING,
	
	ROUND(NVL((CASE WHEN LEVEL2_APPROVAL_DATE IS NULL and BATCH_APPROVAL_STATUS not in ('Approved') THEN (SELECT TRUNC(SysDate)-TRUNC(LEVEL1_APPROVAL_DATE) FROM dual) END ),0),0) DAYS_LEVEL2_APPROVAL_OUTSTANDING,  --REF-289628
	
	ROUND(NVL((CASE WHEN LEVEL2_APPROVAL_DATE IS NOT NULL THEN ( SELECT LEVEL2_APPROVAL_DATE - LEVEL1_APPROVAL_DATE FROM DUAL) END ),0),0) DAYS_TO_LEVEL2_APPROVAL,
	ROUND(NVL((CASE WHEN LEVEL1_APPROVAL_DATE IS NOT NULL THEN (SELECT TRUNC(LEVEL1_APPROVAL_DATE) - TRUNC(CREATION_DATE) FROM dual) END),0),0) DAYS_TO_LEVEL1_APPROVAL,
	EXCHANGE_RATE,
	--(CASE WHEN CREATED_BY <> LEVEL1_APPROVER AND (L1_JOB_LEVEL > CB_LEVEL) THEN 'Yes' ELSE 'No' END) C_Not_Equal_L1_Approver, --33 REF-289628
	
	(CASE
        WHEN (JOB_TITLE_OF_CREATED_BY not like '%GENPACT%' and JOB_TITLE_OF_L1APPROVER not like '%GENPACT%' ) and (upper(CREATED_BY) <> upper(LEVEL1_APPROVER) ) and (L1_JOB_LEVEL > CB_LEVEL) THEN 'Yes'
		WHEN (JOB_TITLE_OF_CREATED_BY  like '%GENPACT%' and JOB_TITLE_OF_L1APPROVER like '%GENPACT%') and (upper(CREATED_BY) <> upper(LEVEL1_APPROVER ) ) AND ( REGEXP_SUBSTR(JOB_TITLE_OF_L1APPROVER, '\d+$')  > REGEXP_SUBSTR(JOB_TITLE_OF_CREATED_BY, '\d+$') ) THEN 'Yes'
		WHEN (JOB_TITLE_OF_CREATED_BY  like '%GENPACT%' and JOB_TITLE_OF_L1APPROVER not like '%GENPACT%') THEN 'Yes'  -- Default any CUSTOM role is higher to any GENPACT role
	  ELSE 'No'
	  
    END)  C_Not_Equal_L1_Approver,
	
	
	
	--(CASE WHEN PREPARER <> LEVEL1_APPROVER AND (L1_JOB_LEVEL > PREP_JOB_LEVEL) THEN 'Yes' ELSE 'No' END) P_Not_Equal_L1_Approver, --34 REF-289628
	(CASE
        WHEN (JOB_TITLE_OF_PREPARER not like '%GENPACT%' and JOB_TITLE_OF_L1APPROVER not like '%GENPACT%' ) and (upper(PREPARER) <> upper(LEVEL1_APPROVER) ) and (L1_JOB_LEVEL > CB_LEVEL) THEN 'Yes'
		WHEN (JOB_TITLE_OF_PREPARER  like '%GENPACT%' and JOB_TITLE_OF_L1APPROVER like '%GENPACT%') and ( upper(PREPARER) <> upper(LEVEL1_APPROVER ) ) AND ( REGEXP_SUBSTR(JOB_TITLE_OF_L1APPROVER, '\d+$')  > REGEXP_SUBSTR(JOB_TITLE_OF_PREPARER, '\d+$') ) THEN 'Yes'
		WHEN (JOB_TITLE_OF_PREPARER  like '%GENPACT%' and JOB_TITLE_OF_L1APPROVER not like '%GENPACT%') THEN 'Yes'  -- Default any CUSTOM role is higher to any GENPACT role
	  ELSE 'No'
	  
    END)  P_Not_Equal_L1_Approver,
	

	--(CASE WHEN CREATED_BY <> LEVEL2_APPROVER AND LEVEL1_APPROVER <> LEVEL2_APPROVER AND (L2_JOB_LEVEL > CB_LEVEL) AND (L2_JOB_LEVEL > L1_JOB_LEVEL) THEN 'Yes' ELSE 'No' END) C_And_L1_Not_Equal_L2_Approver, -- 35 REF-289628
	(CASE 
	    when  LEVEL2_APPROVER is null THEN 'N/A' 
		when  LEVEL2_APPROVER is not null and ( upper(CREATED_BY) <> upper(LEVEL2_APPROVER) and upper(LEVEL1_APPROVER) <> upper(LEVEL2_APPROVER))  THEN  'Yes'
		ELSE 'No'
	  END
	)C_And_L1_Not_Equal_L2_Approver,
	
	
	--(CASE WHEN PREPARER <> LEVEL2_APPROVER AND LEVEL1_APPROVER <> LEVEL2_APPROVER AND (L2_JOB_LEVEL > CB_LEVEL) AND (L2_JOB_LEVEL > L1_JOB_LEVEL) THEN 'Yes' ELSE 'No' END) P_And_L1_Not_Equal_L2_Approver, -- REF-289628
	(CASE 
	    when  LEVEL2_APPROVER is null THEN 'N/A' 
		when  LEVEL2_APPROVER is not null and (upper(PREPARER) <> upper(LEVEL2_APPROVER) and upper(LEVEL1_APPROVER) <> upper(LEVEL2_APPROVER) ) THEN  'Yes'
		ELSE 'No'
	  END
	) P_And_L1_Not_Equal_L2_Approver,   
	
	
	
	(CASE WHEN L1_DELIGATED_TO IS NOT NULL THEN 'Yes' ELSE 'No' END) DELIGATED_L1,
	L1_DELIGATED_TO,
	(CASE WHEN L2_DELIGATED_TO IS NOT NULL THEN 'Yes' ELSE 'No' END) DELIGATED_L2,
	L2_DELIGATED_TO
FROM
(
SELECT 
	GL.name LEDGER_NAME,
	GP.period_name PERIOD,
	GP.start_date,
	GJB.creation_date CREATION_DATE,
	GJH.DEFAULT_EFFECTIVE_DATE JOURNAL_DATE,
	GJB.je_batch_id JOURNAL_BATCH_ID,
	GJB.name JOURNAL_BATCH_NAME,
	FLV.meaning BATCH_APPROVAL_STATUS,
	GJH.je_header_id JOURNAL_HEADER_ID,
	GJH.name JOURNAL_NAME,
	GJSV.user_je_source_name JOURNAL_SOURCE_NAME,
	GJH.currency_code JOURNAL_CURRENCY,
	GJH.running_total_dr JOURNAL_AMOUNT,
	GJH.currency_conversion_date CONVERSION_DATE,
	(
		SELECT distinct GDC.user_conversion_type
		FROM gl_daily_conversion_types GDC
		WHERE GJH.currency_conversion_type=GDC.conversion_type 
	)	CONVERSION_RATE_TYPE,
	ROUND(GJH.currency_conversion_rate,2) CONVERSION_RATE,
	--(GJH.running_total_dr*ROUND(GJH.currency_conversion_rate,2)) TRANS_VAL_USD,
	(
	select
	MIN(ACTION_DATE)
	from
	gl_je_action_log
	where
	1=1
	and je_batch_id = GJB.je_batch_id
	and period_name = GJH.period_name
	) WORK_FLOW_START_DATE,
	GJB.created_by CREATED_BY,
	(select distinct
		PJFT.name
	from 
		per_users PU,
		per_all_assignments_m PAAM,
		per_jobs_f_tl PJFT
	where
		1=1
		and PU.username = GJB.created_by
		and PU.person_id = PAAM.person_id
		and PAAM.job_id = PJFT.job_id
		and sysdate between PAAM.effective_start_date and PAAM.effective_end_date
	) JOB_TITLE_OF_CREATED_BY,
	(select distinct
		PJFT.approval_authority
	from 
		per_users PU,
		per_all_assignments_m PAAM,
		per_jobs_f PJFT
	where
		1=1
		and PU.username = GJB.created_by
		and PU.person_id = PAAM.person_id
		and PAAM.job_id = PJFT.job_id
		and sysdate between PAAM.effective_start_date and PAAM.effective_end_date
	) CB_LEVEL,
	(
	select distinct
	user_id
	from
	gl_je_action_log GJAL
	where
	1=1
	and GJAL.je_batch_id = GJB.je_batch_id
	and GJAL.period_name = GJH.period_name
	AND GJAL.action_date = (select min(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_code in ('SENT_APPROVAL','SENT_APPROVAL_W_POSTING'))
	and GJAL.action_code in ('SENT_APPROVAL','SENT_APPROVAL_W_POSTING')
	) PREPARER,
	(
	select distinct
		PJFT.name
	from 
		per_users PU,
		per_all_assignments_m PAAM,
		per_jobs_f_tl PJFT,
		gl_je_action_log GJAL
	where
		1=1
		and PU.username = GJAL.user_id
		and GJAL.je_batch_id = GJB.je_batch_id
		and GJAL.period_name = GJH.period_name
		AND GJAL.action_date = (select min(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_code in ('SENT_APPROVAL','SENT_APPROVAL_W_POSTING'))
		and GJAL.action_code in ('SENT_APPROVAL','SENT_APPROVAL_W_POSTING')
		and PU.person_id = PAAM.person_id
		and PAAM.job_id = PJFT.job_id
		and sysdate between PAAM.effective_start_date and PAAM.effective_end_date
	) JOB_TITLE_OF_PREPARER,
	(
	select 
	GJAL.user_id
	from
	gl_je_action_log GJAL
	where
	1=1
	AND GJAL.je_batch_id = GJB.je_batch_id
	and GJAL.period_name = GJH.period_name
	AND GJAL.action_date = (select min(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_code = 'APPROVED')
	AND GJAL.action_code = 'APPROVED'
	) LEVEL1_APPROVER,
	(
	select distinct
		PJFT.name
	from 
		per_users PU,
		per_all_assignments_m PAAM,
		per_jobs_f_tl PJFT,
		gl_je_action_log GJAL
	where
		1=1
		and UPPER(PU.username) = UPPER(GJAL.user_id)
		AND GJAL.je_batch_id = GJB.je_batch_id
		and GJAL.period_name = GJH.period_name
		AND GJAL.action_date = (select min(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_code = 'APPROVED')
		AND GJAL.action_code = 'APPROVED'
		and PU.person_id = PAAM.person_id
		and PAAM.job_id = PJFT.job_id
		and sysdate between PAAM.effective_start_date and PAAM.effective_end_date
	) JOB_TITLE_OF_L1APPROVER,
	(
	select distinct
	GJAL.action_date
	from
	gl_je_action_log GJAL
	where
	1=1
	AND GJAL.je_batch_id = GJB.je_batch_id
	and GJAL.period_name = GJH.period_name
	AND GJAL.action_date = (select min(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_code = 'APPROVED')
	AND GJAL.action_code = 'APPROVED'
	) LEVEL1_APPROVAL_DATE,
	(
	select distinct
	GJAL.user_id
	from
	gl_je_action_log GJAL
	where
	1=1
	AND GJAL.je_batch_id = GJB.je_batch_id
	and GJAL.period_name = GJH.period_name
	AND GJAL.action_date = (select MAX(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_date not in(SELECT MIN(GJLL.action_date) FROM gl_je_action_log GJLL WHERE GJLL.je_batch_id = GJAL.je_batch_id AND GJLL.action_code = 'APPROVED') AND GJAL1.action_code = 'APPROVED')
	AND GJAL.action_code = 'APPROVED'
	) LEVEL2_APPROVER,
	(select distinct
		PJFT.name
	from 
		per_users PU,
		per_all_assignments_m PAAM,
		per_jobs_f_tl PJFT,
		gl_je_action_log GJAL
	where
		1=1
		and UPPER(PU.username) = UPPER(GJAL.user_id)
		AND GJAL.je_batch_id = GJB.je_batch_id
		and GJAL.period_name = GJH.period_name
		AND GJAL.action_date = (select MAX(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_date not in(SELECT MIN(GJLL.action_date) FROM gl_je_action_log GJLL WHERE GJLL.je_batch_id = GJAL.je_batch_id AND GJLL.action_code = 'APPROVED') AND GJAL1.action_code = 'APPROVED')
		AND GJAL.action_code = 'APPROVED'
		and PU.person_id = PAAM.person_id
		and PAAM.job_id = PJFT.job_id
		and sysdate between PAAM.effective_start_date and PAAM.effective_end_date
	) JOB_TITLE_OF_L2APPROVER,
	(
	select distinct
	GJAL.ACTION_DATE
	from
	gl_je_action_log GJAL
	where
	1=1
	AND GJAL.je_batch_id = GJB.je_batch_id
	and GJAL.period_name = GJH.period_name
	AND GJAL.action_date = (select MAX(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_date not in(SELECT MIN(GJLL.action_date) FROM gl_je_action_log GJLL WHERE GJLL.je_batch_id = GJAL.je_batch_id AND GJLL.action_code = 'APPROVED') AND GJAL1.action_code = 'APPROVED')
	AND GJAL.action_code = 'APPROVED'
	) LEVEL2_APPROVAL_DATE,
	(CASE WHEN GJH.CURRENCY_CODE = 'USD' THEN 1
	ELSE
	(
	SELECT 
		GR.conversion_rate
	FROM 
		GL_DAILY_RATES GR,
		gl_je_action_log GJAL		
	WHERE
		1=1
		AND GR.FROM_CURRENCY 			= GJH.currency_code
		AND GR.TO_CURRENCY 				= 'USD' 
		AND UPPER(GR.CONVERSION_TYPE)	= 'CORPORATE'
		AND TRUNC(GR.CONVERSION_DATE)	= TRUNC(GJAL.ACTION_DATE)
		AND GJAL.je_batch_id 			= GJB.je_batch_id
		and GJAL.period_name 			= GJH.period_name
		AND GJAL.action_date 			= (select min(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_code = 'APPROVED')
		AND GJAL.action_code 			= 'APPROVED'
	) END)EXCHANGE_RATE,
	(select distinct
		PJFT.approval_authority
	from 
		per_users PU,
		per_all_assignments_m PAAM,
		per_jobs_f PJFT,
		gl_je_action_log GJAL
	where
		1=1
		and PU.username = GJAL.user_id
		and PU.person_id = PAAM.person_id
		and PAAM.job_id = PJFT.job_id
		and sysdate between PAAM.effective_start_date and PAAM.effective_end_date
		and GJAL.je_batch_id = GJB.je_batch_id
		and GJAL.period_name = GJH.period_name
		AND GJAL.action_date = (select min(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_code in ('SENT_APPROVAL','SENT_APPROVAL_W_POSTING'))
		and GJAL.action_code in ('SENT_APPROVAL','SENT_APPROVAL_W_POSTING')
	) PREP_JOB_LEVEL,
	(select distinct
		PJFT.approval_authority
	from 
		per_users PU,
		per_all_assignments_m PAAM,
		per_jobs_f PJFT,
		gl_je_action_log GJAL
	where
		1=1
		and UPPER(PU.username) = UPPER(GJAL.user_id)
		AND GJAL.je_batch_id = GJB.je_batch_id
		and GJAL.period_name = GJH.period_name
		AND GJAL.action_date = (select min(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_code = 'APPROVED')
		AND GJAL.action_code = 'APPROVED'
		and PU.person_id = PAAM.person_id
		and PAAM.job_id = PJFT.job_id
		and sysdate between PAAM.effective_start_date and PAAM.effective_end_date
	) L1_JOB_LEVEL,
	(select distinct
		PJFT.approval_authority
	from 
		per_users PU,
		per_all_assignments_m PAAM,
		per_jobs_f PJFT,
		gl_je_action_log GJAL
	where
		1=1
		and UPPER(PU.username) = UPPER(GJAL.user_id)
		AND GJAL.je_batch_id = GJB.je_batch_id
		and GJAL.period_name = GJH.period_name
		AND GJAL.action_date = (select min(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_date not in(SELECT MIN(GJLL.action_date) FROM gl_je_action_log GJLL WHERE GJLL.je_batch_id = GJAL.je_batch_id AND GJLL.action_code = 'APPROVED') AND GJAL1.action_code = 'APPROVED')
		AND GJAL.action_code = 'APPROVED'
		and PU.person_id = PAAM.person_id
		and PAAM.job_id = PJFT.job_id
		and sysdate between PAAM.effective_start_date and PAAM.effective_end_date
	) L2_JOB_LEVEL,
	(SELECT distinct
	GJAL.USER_ID
	FROM
	gl_je_action_log GJAL
	WHERE
	GJAL.je_batch_id = GJB.je_batch_id
	 AND GJAL.action_date = (select min(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id  and GJAL1.action_code in ('ORA_DELEGATED TO','ORA_REASSIGNED TO'))
	AND GJAL.ACTION_CODE in ('ORA_DELEGATED TO','ORA_REASSIGNED TO') 
	) L1_DELIGATED_TO,
	(SELECT distinct
	GJAL.USER_ID
	FROM
	gl_je_action_log GJAL
	WHERE
	GJAL.je_batch_id = GJB.je_batch_id
	AND GJAL.action_date = (select MAX(GJAL1.action_date) from gl_je_action_log GJAL1 where GJAL1.je_batch_id = GJAL.je_batch_id and GJAL1.action_date not in(SELECT MIN(GJLL.action_date) FROM gl_je_action_log GJLL WHERE GJLL.je_batch_id = GJAL.je_batch_id AND GJLL.action_code in ('ORA_DELEGATED TO','ORA_REASSIGNED TO')) AND GJAL1.action_code in ('ORA_DELEGATED TO','ORA_REASSIGNED TO'))
	AND GJAL.ACTION_CODE in ('ORA_DELEGATED TO','ORA_REASSIGNED TO')
	) L2_DELIGATED_TO
FROM
	gl_je_batches GJB,
	gl_je_headers GJH,
	gl_ledgers GL,
	gl_periods GP,
	gl_je_sources_vl GJSV,
	fnd_lookup_values FLV,
	SECURITY_TBL ST
	
WHERE
	1=1
	AND ST.ledger_id					= GL.ledger_id
	AND GJB.je_batch_id					= GJH.je_batch_id
	AND GJH.ledger_id					= GL.ledger_id
	AND GL.period_set_name				= GP.period_set_name
	AND GJH.period_name					= GP.period_name
	AND GJH.je_source					= GJSV.je_source_name
	--AND GJB.je_batch_id					in (48363,97765,40183,41162,49436,49140,49141,74836)
	--and FLV.meaning <> 'Not required'
	AND GJSV.user_je_source_name		in ('Manual','Spreadsheet','AutoCopy')
	---AND GJB.name = 'PL FRANCE USD GAAP REVALUATION ZW Spreadsheet A 300000016367129 2768560 N'
	AND GJB.APPROVAL_STATUS_CODE 		= FLV.lookup_code
	AND FLV.lookup_type 				= 'JE_BATCH_APPROVAL_STATUS'
	AND FLV.language					= USERENV('LANG')
	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 (FLV.meaning				IN (:P_BATCH_STATUS)
	OR 'ALL'							IN (:P_BATCH_STATUS||'ALL'))
	
)
ORDER BY
start_date,
LEDGER_NAME,
CREATION_DATE]]>

:P_BATCH_STATUS :P_FROM_PERIOD :P_LEDGER :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.

●— fact → dimension join

Element	Type	Definition
GL_ACCESS_SETS	dimension	dimension
FUN_USER_ROLE_DATA_ASGNMNTS	dimension	dimension
PER_ROLES_DN	dimension	dimension
PER_USER_ROLES	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.

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.

Table	Reporting columns	Subject areas
GL_LEDGERS	10	104
GL_ACCESS_SETS	Setup / configuration table — joined for reference, not exposed for analytics
FUN_USER_ROLE_DATA_ASGNMNTS	Setup / configuration table — joined for reference, not exposed for analytics
PER_ROLES_DN	Setup / configuration table — joined for reference, not exposed for analytics
PER_USER_ROLES	16	0
PER_USERS	22	27
GL_JE_ACTION_LOG	Setup / configuration table — joined for reference, not exposed for analytics
PER_ALL_ASSIGNMENTS_M	129	21
PER_JOBS_F_TL	12	8
PER_JOBS_F	98	4
GL_DAILY_RATES	1	10
GL_JE_BATCHES	14	2

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.