Ledger Comparison Report

General Ledger

A side-by-side of two ledgers' balances — typically primary versus secondary or reporting ledger — lined up through the chart-of-accounts mapping, so finance can confirm the secondary ledger mirrors the primary and explain any gap.

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 Ledger Comparison Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Balance Type

$96,400.00

Entity

Globex Holdings

Period

FEB-26

Pl Ledger

US Primary

Sl Ledger

US Primary

1,420

Accounts compared

$48.2M

Primary total

Mapping gaps

Account	Primary Balance	Secondary Balance	Mapped Account	Difference
1000-2100-000	$1,240,500.00	$1,240,500.00	1000-2100-000	Sample
1000-5400-000	$842,150.75	$842,150.75	1000-5400-000	—
1000-1410-000	$96,400.00	$96,400.00	1000-1410-000	Sample
2000-2100-000	$1,005,233.10	$1,005,233.10	2000-2100-000	—
1000-6300-000	$58,720.40	$58,720.40	1000-6300-000	Sample
1000-2100-000	$1,240,500.00	$1,240,500.00	1000-2100-000	Sample

AI Analyst · active

reading

The report reads GL_BALANCES for both ledgers and applies the chart-of-accounts mapping in GL_COA_MAPPINGS to line them up account for account.

flag

Nine primary accounts map to no secondary account — their balances never reach the secondary ledger, so it understates by that amount.

root cause & next step

Add the missing segment rules to the COA mapping; unmapped accounts are the usual reason a secondary or reporting ledger drifts from the primary.

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_PERIODS
GL_BALANCES
GL_LEDGERS
GL_CODE_COMBINATIONS
GL_COA_MAPPINGS
GL_COA_MAP_SEGMENT_RULES
GL_COA_MAP_ROLLUP_RULES
FND_FLEX_VALUES_VL
MIDDLE_OUT
MIDDLE_IN
SECONDARY_LEDGER

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 middle_out as 
(SELECT DISTINCT
GLL1.NAME PRIMARY_LEDGER
,GLL2.NAME SECONDARY_LEDGER
,NULL FROM_COA_ID
,NULL TO_COA_ID 
,GB1.PERIOD_NAME PL_PERIOD
,GB2.PERIOD_NAME SL_PERIOD
,CC2.SEGMENT1 
,CC2.SEGMENT2 
,CC2.SEGMENT3 
,CC2.SEGMENT5 
,CC2.SEGMENT6 
,CC2.SEGMENT7 
,CC2.SEGMENT8 
,CC2.SEGMENT9 
--,CC1.SEGMENT4 PL_ACCOUNT
, CC1.SEGMENT4 PL_ACCOUNT
,CC2.SEGMENT4 SL_ACCOUNT
--,A1.DESCRIPTION PL_ACCOUNT_DESCRIPTION
, A1.DESCRIPTION  PL_ACCOUNT_DESCRIPTION
,A2.DESCRIPTION SL_ACCOUNT_DESCRIPTION
,SUM(CASE WHEN :P_BALANCE_TYPE='YTD' THEN 
(CASE WHEN GB2.PERIOD_NUM=1 AND GP2.ADJUSTMENT_PERIOD_FLAG='Y' 
      THEN 0
      WHEN (GB2.PERIOD_NUM= 13 AND GP2.ADJUSTMENT_PERIOD_FLAG='Y')--(SELECT MAX(PERIOD_NUM) FROM GL_PERIODS GP WHERE GP1.PERIOD_SET_NAME=GP.PERIOD_SET_NAME AND GP1.PERIOD_YEAR=GP.PERIOD_YEAR
      THEN (SELECT (GB.BEGIN_BALANCE_DR_BEQ-GB.BEGIN_BALANCE_CR_BEQ)+(GB.PERIOD_NET_DR_BEQ-GB.PERIOD_NET_CR_BEQ) 
	        FROM GL_BALANCES GB
			   ,GL_PERIODS GP 
		    WHERE 1=1
		    AND GB.PERIOD_NAME=GP.PERIOD_NAME 
		    AND GP.PERIOD_NUM =(SELECT MAX(PERIOD_NUM) 
			                    FROM GL_PERIODS 
						        WHERE 1=1
						        AND PERIOD_YEAR=GP1.PERIOD_YEAR 
						        AND PERIOD_SET_NAME=GP1.PERIOD_SET_NAME)
		    AND GB.LEDGER_ID=GB1.LEDGER_ID
            AND GB.CODE_COMBINATION_ID=GB1.CODE_COMBINATION_ID	
            AND GP.PERIOD_SET_NAME=GP1.PERIOD_SET_NAME
		    AND GP.PERIOD_YEAR=GP1.PERIOD_YEAR
			)	
	 ELSE
(GB1.BEGIN_BALANCE_DR_BEQ-GB1.BEGIN_BALANCE_CR_BEQ)+(GB1.PERIOD_NET_DR_BEQ-GB1.PERIOD_NET_CR_BEQ) 
END )
ELSE
(CASE WHEN GB2.PERIOD_NUM=1 AND GP2.ADJUSTMENT_PERIOD_FLAG='Y' 
      THEN 0
      WHEN (GB2.PERIOD_NUM= 13 AND GP2.ADJUSTMENT_PERIOD_FLAG='Y')--(SELECT MAX(PERIOD_NUM) FROM GL_PERIODS GP WHERE GP1.PERIOD_SET_NAME=GP.PERIOD_SET_NAME AND GP1.PERIOD_YEAR=GP.PERIOD_YEAR
      THEN (SELECT (GB.PERIOD_NET_DR_BEQ-GB.PERIOD_NET_CR_BEQ) 
	        FROM GL_BALANCES GB
			   ,GL_PERIODS GP 
		    WHERE 1=1
		    AND GB.PERIOD_NAME=GP.PERIOD_NAME 
		    AND GP.PERIOD_NUM =(SELECT MAX(PERIOD_NUM) 
			                    FROM GL_PERIODS 
						        WHERE 1=1
						        AND PERIOD_YEAR=GP1.PERIOD_YEAR 
						        AND PERIOD_SET_NAME=GP1.PERIOD_SET_NAME)
		    AND GB.LEDGER_ID=GB1.LEDGER_ID
            AND GB.CODE_COMBINATION_ID=GB1.CODE_COMBINATION_ID	
            AND GP.PERIOD_SET_NAME=GP1.PERIOD_SET_NAME
		    AND GP.PERIOD_YEAR=GP1.PERIOD_YEAR
			)	
	 ELSE
(GB1.PERIOD_NET_DR_BEQ-GB1.PERIOD_NET_CR_BEQ) 
END ) 
END) PL_BALANCE
,SUM (CASE WHEN :P_BALANCE_TYPE='YTD'  THEN 
( CASE WHEN  GP1.PERIOD_NUM=1 AND GP1.ADJUSTMENT_PERIOD_FLAG='Y' 
      THEN 0
      WHEN (GB1.PERIOD_NUM= 14)--(SELECT MAX(PERIOD_NUM) FROM GL_PERIODS GP WHERE GP1.PERIOD_SET_NAME=GP.PERIOD_SET_NAME AND GP1.PERIOD_YEAR=GP.PERIOD_YEAR
      THEN (SELECT (GB.BEGIN_BALANCE_DR_BEQ-GB.BEGIN_BALANCE_CR_BEQ)+(GB.PERIOD_NET_DR_BEQ-GB.PERIOD_NET_CR_BEQ) 
	        FROM GL_BALANCES GB
			    ,GL_PERIODS GP 
			WHERE 1=1
			AND GB.PERIOD_NAME=GP.PERIOD_NAME 
			AND GP.PERIOD_NUM=(SELECT MAX(PERIOD_NUM) 
			                   FROM GL_PERIODS 
							   WHERE 1=1
							   AND PERIOD_YEAR=GP2.PERIOD_YEAR 
							   AND PERIOD_SET_NAME=GP2.PERIOD_SET_NAME)
			AND GB.LEDGER_ID=GB2.LEDGER_ID
            AND GB.CODE_COMBINATION_ID=GB2.CODE_COMBINATION_ID	
            AND GP.PERIOD_SET_NAME=GP2.PERIOD_SET_NAME
			AND GP.PERIOD_YEAR=GP2.PERIOD_YEAR
			)
      ELSE  
(GB2.BEGIN_BALANCE_DR_BEQ-GB2.BEGIN_BALANCE_CR_BEQ)+(GB2.PERIOD_NET_DR_BEQ-GB2.PERIOD_NET_CR_BEQ) 
END )
ELSE
( CASE WHEN  GP1.PERIOD_NUM=1 AND GP1.ADJUSTMENT_PERIOD_FLAG='Y' 
      THEN 0
      WHEN (GB1.PERIOD_NUM= 14)--(SELECT MAX(PERIOD_NUM) FROM GL_PERIODS GP WHERE GP1.PERIOD_SET_NAME=GP.PERIOD_SET_NAME AND GP1.PERIOD_YEAR=GP.PERIOD_YEAR
      THEN (SELECT (GB.PERIOD_NET_DR_BEQ-GB.PERIOD_NET_CR_BEQ) 
	        FROM GL_BALANCES GB
			    ,GL_PERIODS GP 
			WHERE 1=1
			AND GB.PERIOD_NAME=GP.PERIOD_NAME 
			AND GP.PERIOD_NUM=(SELECT MAX(PERIOD_NUM) 
			                   FROM GL_PERIODS 
							   WHERE 1=1
							   AND PERIOD_YEAR=GP2.PERIOD_YEAR 
							   AND PERIOD_SET_NAME=GP2.PERIOD_SET_NAME)
			AND GB.LEDGER_ID=GB2.LEDGER_ID
            AND GB.CODE_COMBINATION_ID=GB2.CODE_COMBINATION_ID	
            AND GP.PERIOD_SET_NAME=GP2.PERIOD_SET_NAME
			AND GP.PERIOD_YEAR=GP2.PERIOD_YEAR
			)
      ELSE  
(GB2.PERIOD_NET_DR_BEQ-GB2.PERIOD_NET_CR_BEQ) 
END )
END) SL_BALANCE
 , GB1.CURRENCY_CODE PL_CURRENCY
,GB2.CURRENCY_CODE SL_CURRENCY

FROM
 
 GL_LEDGERS GLL1
,GL_CODE_COMBINATIONS CC1
,GL_BALANCES GB1
,GL_PERIODS GP1
,GL_COA_MAPPINGS GCM
,GL_COA_MAP_SEGMENT_RULES GCMC 
,GL_COA_MAP_ROLLUP_RULES GCMR 
,GL_PERIODS GP2
,GL_LEDGERS GLL2
,GL_CODE_COMBINATIONS CC2
,GL_BALANCES GB2
,FND_FLEX_VALUES_VL A1
,FND_FLEX_VALUES_VL A2

 
WHERE 1=1
AND GB1.LEDGER_ID=GLL1.LEDGER_ID
AND GB2.LEDGER_ID=GLL2.LEDGER_ID
AND GLL1.PERIOD_SET_NAME=GP1.PERIOD_SET_NAME
AND GLL2.PERIOD_SET_NAME=GP2.PERIOD_SET_NAME
AND GB1.PERIOD_NAME=GP1.PERIOD_NAME
AND GP2.PERIOD_NAME=GB2.PERIOD_NAME
AND GP2.PERIOD_YEAR=GP1.PERIOD_YEAR
AND GB1.CODE_COMBINATION_ID=CC1.CODE_COMBINATION_ID
AND GB2.CODE_COMBINATION_ID=CC2.CODE_COMBINATION_ID
--AND (CASE WHEN GP2.START_DATE=GP1.START_DATE AND GP2.END_DATE=GP1.END_DATE  THEN GP1.PERIOD_NAME END  )=GP2.PERIOD_NAME
AND GLL1.LEDGER_CATEGORY_CODE='PRIMARY'
AND GLL2.LEDGER_CATEGORY_CODE='SECONDARY' 
AND CC2.SEGMENT1=CC1.SEGMENT1
AND CC2.SEGMENT2=CC1.SEGMENT2
AND CC2.SEGMENT3=CC1.SEGMENT3
AND CC2.SEGMENT5=CC1.SEGMENT5
AND CC2.SEGMENT6=CC1.SEGMENT6
AND CC2.SEGMENT7=CC1.SEGMENT7
AND CC2.SEGMENT8=CC1.SEGMENT8
AND CC2.SEGMENT9=CC1.SEGMENT9
AND GB1.CURRENCY_CODE=GB2.CURRENCY_CODE
and gb1.currency_code<>'STAT'
and gb2.currency_code<>'STAT'
AND GLL2.CHART_OF_ACCOUNTS_ID<>GLL1.CHART_OF_ACCOUNTS_ID
AND GCM.FROM_COA_ID=GLL1.CHART_OF_ACCOUNTS_ID
AND GCM.TO_COA_ID=GLL2.CHART_OF_ACCOUNTS_ID
AND GCM.COA_MAPPING_ID=GCMC.COA_MAPPING_ID
AND GCMC.FROM_SEG_COLUMN_NAME='SEGMENT4'
AND GCMR.SEGMENT_RULE_ID=GCMC.SEGMENT_RULE_ID
AND CC1.SEGMENT4 BETWEEN FROM_FLEX_VALUE_HIGH AND FROM_FLEX_VALUE_LOW
AND CC2.SEGMENT4 = TO_FLEX_VALUE
AND A1.FLEX_VALUE =CC1.SEGMENT4
AND NVL(GB1.TRANSLATED_FLAG , '-1')<>'R'
and nvl(gb2.TRANSLATED_FLAG, '-1')<>'R'
AND A1.VALUE_CATEGORY='GREECE ACCOUNT VALUE SET' 
AND A2.FLEX_VALUE = CC2.SEGMENT4
AND A2.VALUE_CATEGORY='ACCOUNT VALUE SET'

AND ((CASE WHEN GP1.START_DATE=GP2.START_DATE AND GP1.END_dATE=GP2.END_DATE AND GP1.ADJUSTMENT_PERIOD_FLAG='N' AND GP1.PERIOD_NAME=:P_PERIOD AND GP1.PERIOD_NAME=GP2.PERIOD_NAME THEN GP1.PERIOD_NAME ELSE  (SELECT DISTINCT GP.PERIOD_NAME
																																											                      FROM GL_PERIODS GP
																																										                           WHERE GP.ADJUSTMENT_PERIOD_FLAG='N'
																																																   AND GP.PERIOD_NAME=:P_PERIOD
																																																   AND GP.PERIOD_NAME=GP1.PERIOD_NAME
																																																   AND GP.PERIOD_SET_NAME=GP1.PERIOD_SET_NAME
																																																   AND GP.PERIOD_YEAR=GP1.PERIOD_YEAR )  END )	=GP2.PERIOD_NAME	
      OR (CASE WHEN GP1.START_DATE=GP2.START_DATE AND GP1.END_dATE=GP2.END_DATE AND GP1.START_DATE=GP1.END_DATE AND GP1.PERIOD_NUM=14 AND GP1.PERIOD_NAME=:P_PERIOD THEN GP1.PERIOD_NUM ELSE (SELECT DISTINCT GP.PERIOD_NUM
																																											                      FROM GL_PERIODS GP
																																										                           WHERE GP.ADJUSTMENT_PERIOD_FLAG='Y'
																																																   AND GP.PERIOD_NUM=14
																																																   AND GP.PERIOD_NAME=:P_PERIOD
																																																   AND GP.PERIOD_SET_NAME=GP1.PERIOD_SET_NAME
																																																   AND GP.PERIOD_YEAR=GP1.PERIOD_YEAR ) END)=	( SELECT DISTINCT GP.PERIOD_NUM
																																											                      FROM GL_PERIODS GP
																																										                           WHERE GP.PERIOD_NUM=13
																																																   AND GP.PERIOD_NAME=GP2.PERIOD_NAME
																																																   AND GP.PERIOD_SET_NAME=GP2.PERIOD_SET_NAME
																																																   AND GP.PERIOD_YEAR=GP1.PERIOD_YEAR
																																									                                               )+1
	  OR (CASE WHEN GP1.START_DATE=GP2.START_DATE AND GP1.END_dATE=GP2.END_DATE AND GP2.START_DATE=GP2.END_DATE AND GP2.PERIOD_NUM=13 AND GP2.PERIOD_NAME=:P_PERIOD THEN GP2.PERIOD_NUM	ELSE (SELECT DISTINCT GP.PERIOD_NUM
																																											                      FROM GL_PERIODS GP
																																										                           WHERE GP.ADJUSTMENT_PERIOD_FLAG='Y'
																																																   AND GP.PERIOD_NUM=13
																																																   AND GP.PERIOD_NAME=:P_PERIOD
																																																   AND GP.PERIOD_SET_NAME=GP2.PERIOD_SET_NAME
																																																   AND GP.PERIOD_YEAR=GP2.PERIOD_YEAR) END)= 	(SELECT DISTINCT GP.PERIOD_NUM
																																											                      FROM GL_PERIODS GP
																																										                           WHERE GP.ADJUSTMENT_PERIOD_FLAG='Y'
																																																   AND GP.PERIOD_NUM=14
																																																   AND GP.PERIOD_NAME=GP1.PERIOD_NAME
																																																   AND GP.PERIOD_SET_NAME=GP1.PERIOD_SET_NAME
																																																   AND GP.PERIOD_YEAR=GP2.PERIOD_YEAR )	-1																																															   																																							                                               )						

AND GLL1.NAME  = 'PL GREECE EUR LOCAL'
AND GLL2.NAME  = 'SL GREECE EUR GAAP'
--AND GP1.PERIOD_NAME = '01-24'
--AND GP2.PERIOD_NAME = '01-24'
--AND CC2.SEGMENT1 = '3241'
--AND CC1.SEGMENT4 = '3803010001'
----
AND ((GP1.PERIOD_NAME IN (:P_PERIOD) OR 'ALL' IN ('ALL'||:P_PERIOD))
OR (GP2.PERIOD_NAME IN (:P_PERIOD) OR 'ALL' IN ('ALL'||:P_PERIOD)))
AND (CC1.SEGMENT1 IN (:P_ENTITY) OR 'ALL' IN ('ALL'||:P_ENTITY))
AND (GLL1.NAME IN (:P_PL_LEDGER) OR 'ALL' IN ('ALL'||:P_PL_LEDGER))
AND (GLL2.NAME IN (:P_SL_LEDGER) OR 'ALL' IN ('ALL'||:P_SL_LEDGER))

GROUP BY 

 GLL1.NAME 
,GLL2.NAME 
,GB1.PERIOD_NAME 
,GB2.PERIOD_NAME 
,CC2.SEGMENT1 
,CC2.SEGMENT2 
,CC2.SEGMENT3 
,CC2.SEGMENT5 
,CC2.SEGMENT6 
,CC2.SEGMENT7 
,CC2.SEGMENT8 
,CC2.SEGMENT9 
,CC2.SEGMENT4 
,A2.DESCRIPTION 
,CC1.SEGMENT4 
,A1.DESCRIPTION 
, GB1.CURRENCY_CODE 
,GB2.CURRENCY_CODE

)

,middle_in as
(

SELECT DISTINCT
  PRIMARY_LEDGER
, SECONDARY_LEDGER
, FROM_COA_ID
, TO_COA_ID 
, PL_PERIOD
, SL_PERIOD
,SEGMENT1 
,SEGMENT2 
,SEGMENT3 
,SEGMENT5 
,SEGMENT6 
,SEGMENT7 
,SEGMENT8 
,SEGMENT9 
--,CC1.SEGMENT4 PL_ACCOUNT
, PL_ACCOUNT
,SL_ACCOUNT
--,A1.DESCRIPTION PL_ACCOUNT_DESCRIPTION
,  PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT_DESCRIPTION
,SUM(PL_BALANCE) PL_BALANCE
,SUM(SL_BALANCE) SL_BALANCE

FROM 

middle_out m

GROUP BY 

  PRIMARY_LEDGER
, SECONDARY_LEDGER
, FROM_COA_ID
, TO_COA_ID 
, PL_PERIOD
, SL_PERIOD
,SEGMENT1 
,SEGMENT2 
,SEGMENT3 
,SEGMENT5 
,SEGMENT6 
,SEGMENT7 
,SEGMENT8 
,SEGMENT9 
, PL_ACCOUNT
,SL_ACCOUNT
--,A1.DESCRIPTION PL_ACCOUNT_DESCRIPTION
,  PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT_DESCRIPTION
)

,

middle as 
(
SELECT DISTINCT
  PRIMARY_LEDGER
, SECONDARY_LEDGER
, FROM_COA_ID
, TO_COA_ID 
, PL_PERIOD
, SL_PERIOD
,SEGMENT1 
,SEGMENT2 
,SEGMENT3 
,SEGMENT5 
,SEGMENT6 
,SEGMENT7 
,SEGMENT8 
,SEGMENT9 
--,CC1.SEGMENT4 PL_ACCOUNT
,LISTAGG(DISTINCT PL_ACCOUNT , '|') PL_ACCOUNT
,SL_ACCOUNT
--,A1.DESCRIPTION PL_ACCOUNT_DESCRIPTION
,LISTAGG(DISTINCT PL_ACCOUNT_DESCRIPTION, '|')  PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT_DESCRIPTION
,SUM(PL_BALANCE) PL_BALANCE
,CASE WHEN MAX(SL_BALANCE) <0 THEN MIN(SL_BALANCE) ELSE MAX(SL_BALANCE) END SL_BALANCE

FROM 

middle_in m

GROUP BY 

  PRIMARY_LEDGER
, SECONDARY_LEDGER
, FROM_COA_ID
, TO_COA_ID 
, PL_PERIOD
, SL_PERIOD
,SEGMENT1 
,SEGMENT2 
,SEGMENT3 
,SEGMENT5 
,SEGMENT6 
,SEGMENT7 
,SEGMENT8 
,SEGMENT9 
,SL_ACCOUNT
,SL_ACCOUNT_DESCRIPTION
),
only_primary as (
SELECT DISTINCT
GLL1.NAME PRIMARY_LEDGER
, NULL SECONDARY_LEDGER
, NULL FROM_COA_ID
, NULL TO_COA_ID 
, GB1.PERIOD_NAME PL_PERIOD
, NULL SL_PERIOD
,cc1.segment1
,cc1.segment2
,cc1.segment3
,cc1.segment5
,cc1.segment6
,cc1.segment7
,cc1.segment8
,cc1.segment9
, cc1.segment4 PL_ACCOUNT
,NULL SL_ACCOUNT
,A1.DESCRIPTION PL_ACCOUNT_DESCRIPTION
,NULL SL_ACCOUNT_DESCRIPTION
,SUM(CASE WHEN :P_BALANCE_TYPE='YTD' 
      THEN (gb1.BEGIN_BALANCE_DR_BEQ-gb1.BEGIN_BALANCE_CR_BEQ)+(gb1.period_net_DR_BEQ-gb1.period_net_CR_BEQ)
      ELSE (gb1.period_net_DR_BEQ-gb1.period_net_CR_BEQ) END)  PL_BALANCE

, 0  SL_BALANCE
 ,GB1.CURRENCY_CODE PL_CURRENCY
 
FROM
GL_LEDGERS GLL1
, GL_CODE_COMBINATIONS CC1
, GL_BALANCES GB1
,GL_PERIODS GP1

,fnd_flex_values_vl A1

WHERE
gll1.LEDGER_CATEGORY_CODE='PRIMARY'
and gll1.ledger_id=gb1.ledger_id
and gll1.period_set_name=gp1.period_set_name
and gp1.period_name=gb1.period_name
and gb1.code_combination_id=cc1.code_combination_id
--AND cc1.chart_of_accounts_id = fksiv1.structure_instance_number 
--AND fksiv1.application_id  = 101
--AND fksiv1.key_flexfield_code = 'GL#'
--AND fksiv1.NAME = 'Global COA'
--AND fksiv1.NAME NOT IN ('LOCAL COA CHINA MFR STD','LOCAL COA CHINA MFR SYS','LOCAL COA CHINA MKTG STD','LOCAL COA CHINA SER SYS','Local COA Brazil','Local COA Colombia','Local COA Colombia IFRS','Local COA France','Local COA Greece','Local COA Peru','Local COA Spain','Local COA Turkey')
---
AND GLL1.NAME IN ('PL GREECE EUR LOCAL')
---
 AND 
(case when ('ADJ-'|| substr(:P_PERIOD,INSTR(:P_PERIOD, '-')+1, 2))= (:P_PERIOD) then ('ΚΛΕΙΣ-'|| substr(:P_PERIOD,INSTR(:P_PERIOD, '-')+1, 2)) else (:P_PERIOD) end) =gp1.period_name
AND (gll1.name IN (:P_PL_LEDGER) OR 'All' IN ('All'||:P_PL_LEDGER))
--AND (gll2.name IN (:P_SL_LEDGER) OR 'All' IN ('All'||:P_SL_LEDGER))
AND (cc1.segment1 IN (:P_ENTITY) OR 'All' IN ('All'||:P_ENTITY))

--and nvl(gb1.TRANSLATED_FLAG , '-1')<>'R'
and A1.flex_value =cc1.segment4
AND A1.VALUE_CATEGORY='GREECE ACCOUNT VALUE SET'
and gb1.currency_code<>'STAT'
--
--AND cc1.segment4 = 111108 --111101 
GROUP BY
GLL1.NAME
, GB1.PERIOD_NAME
,cc1.segment1
,cc1.segment2
,cc1.segment3
,cc1.segment5
,cc1.segment6
,cc1.segment7
,cc1.segment8
,cc1.segment9
, cc1.segment4
,A1.DESCRIPTION
, cc1.code_combination_id
 ,GB1.CURRENCY_CODE 
),

only_secondary as (
SELECT DISTINCT
NULL PRIMARY_LEDGER
, GLL2.NAME SECONDARY_LEDGER
, NULL FROM_COA_ID
, NULL TO_COA_ID 
, NULL PL_PERIOD
, GB2.PERIOD_NAME SL_PERIOD
,cc2.segment1
,cc2.segment2
,cc2.segment3
,cc2.segment5
,cc2.segment6
,cc2.segment7
,cc2.segment8
,cc2.segment9
, NULL PL_ACCOUNT
,cc2.segment4 SL_ACCOUNT
,NULL PL_ACCOUNT_DESCRIPTION
,A2.DESCRIPTION SL_ACCOUNT_DESCRIPTION
,0 PL_BALANCE

, SUM(CASE WHEN :P_BALANCE_TYPE='YTD' 
       THEN (gb2.BEGIN_BALANCE_DR_BEQ-gb2.BEGIN_BALANCE_CR_BEQ)+(gb2.period_net_DR_BEQ-gb2.period_net_CR_BEQ)   
      ELSE (gb2.period_net_DR_BEQ-gb2.period_net_CR_BEQ)  END) SL_BALANCE
 
,GB2.CURRENCY_CODE SL_CURRENCY
 FROM 
GL_PERIODS GP2
, GL_LEDGERS GLL2
, GL_CODE_COMBINATIONS CC2
, GL_BALANCES GB2

,fnd_flex_values_vl A2  

WHERE
 gll2.LEDGER_CATEGORY_CODE='SECONDARY'
and gll2.ledger_id=gb2.ledger_id
and gll2.period_set_name=gp2.period_set_name
and gp2.period_name=gb2.period_name
and gb2.code_combination_id=cc2.code_combination_id
--AND cc2.chart_of_accounts_id = fksiv2.structure_instance_number 
--AND fksiv2.application_id  = 101
--AND fksiv2.key_flexfield_code = 'GL#'
--AND fksiv2.NAME = 'Global COA'
--AND fksiv2.NAME NOT IN ('LOCAL COA CHINA MFR STD','LOCAL COA CHINA MFR SYS','LOCAL COA CHINA MKTG STD','LOCAL COA CHINA SER SYS','Local COA Brazil','Local COA Colombia','Local COA Colombia IFRS','Local COA France','Local COA Greece','Local COA Peru','Local COA Spain','Local COA Turkey')
---
AND GLL2.NAME  IN ('SL GREECE EUR GAAP')
---
 AND 
(case when ('ΚΛΕΙΣ-'|| substr(:P_PERIOD,INSTR(:P_PERIOD, '-')+1, 2))= (:P_PERIOD) then ('ADJ-'|| substr(:P_PERIOD,INSTR(:P_PERIOD, '-')+1, 2)) else (:P_PERIOD) end) =gp2.period_name 
--AND (gll1.name IN (:P_PL_LEDGER) OR 'All' IN ('All'||:P_PL_LEDGER))
AND (gll2.name IN (:P_SL_LEDGER) OR 'All' IN ('All'||:P_SL_LEDGER))
AND (cc2.segment1 IN (:P_ENTITY) OR 'All' IN ('All'||:P_ENTITY))
   
--and nvl(gb2.TRANSLATED_FLAG, '-1')<>'R'
and  A2.flex_value = cc2.segment4
AND A2.VALUE_CATEGORY='ACCOUNT VALUE SET'
and gb2.currency_code<>'STAT'
---
--AND cc2.segment4 = 111108 --111101  
GROUP BY
 GLL2.NAME 
, GB2.PERIOD_NAME 
,cc2.segment1
,cc2.segment2
,cc2.segment3
,cc2.segment5
,cc2.segment6
,cc2.segment7
,cc2.segment8
,cc2.segment9 
,cc2.segment4
,A2.DESCRIPTION
, cc2.code_combination_id
,GB2.CURRENCY_CODE 
),

left as (

select a.*
from 
--only_primary a left join middle b on a.code_combination_id=b.code_combination_id
only_primary a 
, middle_out b where  a.SEGMENT1=b.SEGMENT1(+)
                                      and a.SEGMENT2=b.SEGMENT2(+)
									  and a.SEGMENT3=b.SEGMENT3(+)
									  and a.SEGMENT5=b.SEGMENT5(+)
									  and a.SEGMENT6=b.SEGMENT6(+)
									  and a.SEGMENT7=b.SEGMENT7(+)
									  and a.SEGMENT8=b.SEGMENT8(+)
									  and a.SEGMENT9=b.SEGMENT9(+)
and	 a.PL_ACCOUNT=b.PL_ACCOUNT(+)
and B.SL_ACCOUNT IS NULL 
and a.PL_CURRENCY=b.PL_CURRENCY(+)
),

right as (

select a.*
from 
--only_secondary a left join middle b on a.code_combination_id=b.code_combination_id
only_secondary a 
, middle_out b where a.SEGMENT1=b.SEGMENT1(+)
                                      and a.SEGMENT2=b.SEGMENT2(+)
									  and a.SEGMENT3=b.SEGMENT3(+)
									  and a.SEGMENT5=b.SEGMENT5(+)
									  and a.SEGMENT6=b.SEGMENT6(+)
									  and a.SEGMENT7=b.SEGMENT7(+)
									  and a.SEGMENT8=b.SEGMENT8(+)
									  and a.SEGMENT9=b.SEGMENT9(+)
and	 a.SL_ACCOUNT=b.SL_ACCOUNT(+)
and B.PL_ACCOUNT IS NULL
and a.SL_CURRENCY=b.SL_CURRENCY(+)
)

SELECT 
PRIMARY_LEDGER
,SECONDARY_LEDGER
,ENTITY
,SELLING_METHOD
,COST_CENTER
,LOCATION
,BRAND
,INTERCOMPANY
,FUTURE1
,FUTURE2
,PL_ACCOUNT
,PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT
,SL_ACCOUNT_DESCRIPTION
,NVL(SUM(PL_BALANCE),0) PL_BALANCE
,NVL(SUM(SL_BALANCE),0) SL_BALANCE
,NVL(SUM(VARIANCE),0) VARIANCE

FROM
(
SELECT PRIMARY_LEDGER  PRIMARY_LEDGER
,SECONDARY_LEDGER SECONDARY_LEDGER
,CAST(SEGMENT1 AS VARCHAR(4))  ENTITY
,CAST(SEGMENT2 AS VARCHAR(3)) SELLING_METHOD
,CAST(SEGMENT3 AS VARCHAR(4))  COST_CENTER
,CAST(SEGMENT5 AS VARCHAR(4)) LOCATION
,CAST(SEGMENT6 AS VARCHAR(4)) BRAND
,CAST(SEGMENT7 AS VARCHAR(4)) INTERCOMPANY
,CAST(SEGMENT8 AS VARCHAR(3))  FUTURE1
,CAST(SEGMENT9 AS VARCHAR(5)) FUTURE2
,PL_ACCOUNT
,PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT  SL_ACCOUNT
,SL_ACCOUNT_DESCRIPTION SL_ACCOUNT_DESCRIPTION
,NVL(PL_BALANCE,0) PL_BALANCE
,NVL(SL_BALANCE,0) SL_BALANCE
,NVL(PL_BALANCE,0) - NVL(SL_BALANCE,0) VARIANCE

from 
left a 

/* GROUP BY   
 CAST(SEGMENT1 AS VARCHAR(4))
,CAST(SEGMENT2 AS VARCHAR(3)) 
,CAST(SEGMENT3 AS VARCHAR(4))  
,CAST(SEGMENT5 AS VARCHAR(4)) 
,CAST(SEGMENT6 AS VARCHAR(4)) 
,CAST(SEGMENT7 AS VARCHAR(4)) 
,CAST(SEGMENT8 AS VARCHAR(3)) 
,CAST(SEGMENT9 AS VARCHAR(5))
,PL_ACCOUNT
,PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT   
,SL_ACCOUNT_DESCRIPTION */

union 

SELECT PRIMARY_LEDGER  PRIMARY_LEDGER
,SECONDARY_LEDGER SECONDARY_LEDGER
,CAST(SEGMENT1 AS VARCHAR(4))  ENTITY
,CAST(SEGMENT2 AS VARCHAR(3)) SELLING_METHOD
,CAST(SEGMENT3 AS VARCHAR(4))  COST_CENTER
,CAST(SEGMENT5 AS VARCHAR(4)) LOCATION
,CAST(SEGMENT6 AS VARCHAR(4)) BRAND
,CAST(SEGMENT7 AS VARCHAR(4)) INTERCOMPANY
,CAST(SEGMENT8 AS VARCHAR(3))  FUTURE1
,CAST(SEGMENT9 AS VARCHAR(5)) FUTURE2
,PL_ACCOUNT
,PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT  SL_ACCOUNT
,SL_ACCOUNT_DESCRIPTION SL_ACCOUNT_DESCRIPTION
,NVL(PL_BALANCE,0) PL_BALANCE
,NVL(SL_BALANCE,0) SL_BALANCE
,NVL(PL_BALANCE,0) - NVL(SL_BALANCE,0) VARIANCE

from 
middle b 

/* GROUP BY   
 CAST(SEGMENT1 AS VARCHAR(4))
,CAST(SEGMENT2 AS VARCHAR(3)) 
,CAST(SEGMENT3 AS VARCHAR(4))  
,CAST(SEGMENT5 AS VARCHAR(4)) 
,CAST(SEGMENT6 AS VARCHAR(4)) 
,CAST(SEGMENT7 AS VARCHAR(4)) 
,CAST(SEGMENT8 AS VARCHAR(3)) 
,CAST(SEGMENT9 AS VARCHAR(5))
,PL_ACCOUNT
,PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT   
,SL_ACCOUNT_DESCRIPTION */

union 

SELECT PRIMARY_LEDGER  PRIMARY_LEDGER
,SECONDARY_LEDGER SECONDARY_LEDGER
,CAST(SEGMENT1 AS VARCHAR(4))  ENTITY
,CAST(SEGMENT2 AS VARCHAR(3)) SELLING_METHOD
,CAST(SEGMENT3 AS VARCHAR(4))  COST_CENTER
,CAST(SEGMENT5 AS VARCHAR(4)) LOCATION
,CAST(SEGMENT6 AS VARCHAR(4)) BRAND
,CAST(SEGMENT7 AS VARCHAR(4)) INTERCOMPANY
,CAST(SEGMENT8 AS VARCHAR(3))  FUTURE1
,CAST(SEGMENT9 AS VARCHAR(5)) FUTURE2
,PL_ACCOUNT
,PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT  SL_ACCOUNT
,SL_ACCOUNT_DESCRIPTION SL_ACCOUNT_DESCRIPTION
,NVL(PL_BALANCE,0) PL_BALANCE
,NVL(SL_BALANCE,0) SL_BALANCE
,NVL(PL_BALANCE,0) - NVL(SL_BALANCE,0) VARIANCE

from 
right c  

/* GROUP BY   
 CAST(SEGMENT1 AS VARCHAR(4))
,CAST(SEGMENT2 AS VARCHAR(3)) 
,CAST(SEGMENT3 AS VARCHAR(4))  
,CAST(SEGMENT5 AS VARCHAR(4)) 
,CAST(SEGMENT6 AS VARCHAR(4)) 
,CAST(SEGMENT7 AS VARCHAR(4)) 
,CAST(SEGMENT8 AS VARCHAR(3)) 
,CAST(SEGMENT9 AS VARCHAR(5))
,PL_ACCOUNT
,PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT   
,SL_ACCOUNT_DESCRIPTION */
)

GROUP BY
PRIMARY_LEDGER
,SECONDARY_LEDGER
,ENTITY
,SELLING_METHOD
,COST_CENTER
,LOCATION
,BRAND
,INTERCOMPANY
,FUTURE1
,FUTURE2
,PL_ACCOUNT
,PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT
,SL_ACCOUNT_DESCRIPTION
ORDER BY ENTITY
,SL_ACCOUNT

:P_BALANCE_TYPE :P_ENTITY :P_PERIOD :P_PL_LEDGER :P_SL_LEDGER

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_BALANCES	dimension	dimension
GL_LEDGERS	dimension	dimension
GL_CODE_COMBINATIONS	dimension	dimension
GL_COA_MAPPINGS	dimension	dimension
Primary Balance	measure	measure
Secondary 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.

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_PERIODS	Setup / configuration table — joined for reference, not exposed for analytics
GL_BALANCES	Setup / configuration table — joined for reference, not exposed for analytics
GL_LEDGERS	10	104
GL_CODE_COMBINATIONS	7	61
GL_COA_MAPPINGS	Setup / configuration table — joined for reference, not exposed for analytics
GL_COA_MAP_SEGMENT_RULES	Setup / configuration table — joined for reference, not exposed for analytics
GL_COA_MAP_ROLLUP_RULES	Setup / configuration table — joined for reference, not exposed for analytics
FND_FLEX_VALUES_VL	72	0
MIDDLE_OUT	Setup / configuration table — joined for reference, not exposed for analytics
MIDDLE_IN	Setup / configuration table — joined for reference, not exposed for analytics
SECONDARY_LEDGER	Setup / 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.