Ledger Comparison Report
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.
| 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 |
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.
Nine primary accounts map to no secondary account — their balances never reach the secondary ledger, so it understates by that amount.
Add the missing segment rules to the COA mapping; unmapped accounts are the usual reason a secondary or reporting ledger drifts from the primary.
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
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=:P_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 = :P_PRIMARY_LEDGER
AND GLL2.NAME = :P_SECONDARY_LEDGER
--AND GP1.PERIOD_NAME = '01-24'
--AND GP2.PERIOD_NAME = '01-24'
----
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 GLL1.NAME IN (:P_PRIMARY_LEDGER)
---
AND
(case when ('ADJ-'|| substr(:P_PERIOD,INSTR(:P_PERIOD, '-')+1, 2))= (:P_PERIOD) then (:P_CLOSING_PERIOD_PREFIX|| 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=:P_ACCOUNT_VALUE_SET
and gb1.currency_code<>'STAT'
--
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 GLL2.NAME IN (:P_SECONDARY_LEDGER)
---
AND
(case when (:P_CLOSING_PERIOD_PREFIX|| 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'
---
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
,CHANNEL
,COST_CENTER
,LOCATION
,PRODUCT_LINE
,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)) CHANNEL
,CAST(SEGMENT3 AS VARCHAR(4)) COST_CENTER
,CAST(SEGMENT5 AS VARCHAR(4)) LOCATION
,CAST(SEGMENT6 AS VARCHAR(4)) PRODUCT_LINE
,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)) CHANNEL
,CAST(SEGMENT3 AS VARCHAR(4)) COST_CENTER
,CAST(SEGMENT5 AS VARCHAR(4)) LOCATION
,CAST(SEGMENT6 AS VARCHAR(4)) PRODUCT_LINE
,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)) CHANNEL
,CAST(SEGMENT3 AS VARCHAR(4)) COST_CENTER
,CAST(SEGMENT5 AS VARCHAR(4)) LOCATION
,CAST(SEGMENT6 AS VARCHAR(4)) PRODUCT_LINE
,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
,CHANNEL
,COST_CENTER
,LOCATION
,PRODUCT_LINE
,INTERCOMPANY
,FUTURE1
,FUTURE2
,PL_ACCOUNT
,PL_ACCOUNT_DESCRIPTION
,SL_ACCOUNT
,SL_ACCOUNT_DESCRIPTION
ORDER BY ENTITY
,SL_ACCOUNTThe 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.
| 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 |
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 | No page in this catalog’s data dictionary yet | |
| GL_BALANCES | No page in this catalog’s data dictionary yet | |
| GL_LEDGERS | 10 | 104 |
| GL_CODE_COMBINATIONS | 7 | 61 |
| GL_COA_MAPPINGS | No page in this catalog’s data dictionary yet | |
| GL_COA_MAP_SEGMENT_RULES | No page in this catalog’s data dictionary yet | |
| GL_COA_MAP_ROLLUP_RULES | No page in this catalog’s data dictionary yet | |
| FND_FLEX_VALUES_VL | 72 | 0 |
| MIDDLE_OUT | No page in this catalog’s data dictionary yet | |
| MIDDLE_IN | No page in this catalog’s data dictionary yet | |
| SECONDARY_LEDGER | No page in this catalog’s data dictionary yet | |