Global Intercompany Balances Report

Intercompany

Intercompany receivable and payable balances across every ledger, paired by trading partner, so the consolidation team can see which intercompany positions don't agree before elimination — the report that drives intercompany reconciliation at close.

Related Pairs with the Intercompany Invoiced Reconciliation Report — this is the balance view, that is the invoiced-transaction view.

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

Global Intercompany Balances Report

Sample build · illustrative

Filters

Amount Type

$96,400.00

Conversion Date

2026-02-28

Conversion Rate Type

Standard

Currency

USD

Intercompany Account Pair

1000-1410-000

Period

FEB-26

Entity pairs

$94.0M

IC gross

$1.30M

Out of balance

From Entity	To Entity	Currency	Ic Receivable	Ic Payable	Imbalance
Acme Industrial	Acme Industrial	USD	Sample	Sample	$1,240,500.00
Northwind Trading	Northwind Trading	USD	—	—	$842,150.75
Globex Holdings	Globex Holdings	USD	Sample	Sample	$96,400.00
Initech LLC	Initech LLC	USD	—	—	$1,005,233.10
Umbrella Corp	Umbrella Corp	USD	Sample	Sample	$58,720.40
Acme Industrial	Acme Industrial	USD	Sample	Sample	$1,240,500.00

AI Analyst · active

reading

The report reads GL_BALANCES on the intercompany accounts across ledgers and pairs each entity's receivable with its counterparty's payable.

flag

$1.3M of intercompany positions don't net to zero across seven entity pairs — the larger ones are timing differences where one side booked in a later period than the other.

root cause & next step

Chase the out-of-balance pairs to a booking-period or FX-rate difference; unresolved intercompany imbalances are what stall a consolidation.

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

SELECT 
intercompany_receivable_Account
,intercompany_payable_Account
,SUM(Variance) Variance_subtotal
,SUM(Variance_abs) Variance_abs_subtotal

FROM
(

SELECT 
provider.provider_legal_entity
,provider.intercompany_receivable_Account
,provider.intercompany_receivable_Account ic_rec_Account_Header
,provider.provider_entity_amount
,provider.provider_currency
,provider.FROM_CURRENCY
,receiver.receiver_legal_entity
,receiver.intercompany_payable_Account
,receiver.receiver_entity_amount
,receiver.receiver_currency
,receiver.TO_CURRENCY
,provider.period_name
, :P_CONVERSION_RATE_TYPE USER_CONVERSION_TYPE
,:P_CONVERSION_DATE CONVERSION_DATE
,1 CONVERSION_RATE
, provider_entity_amount+receiver_entity_amount Variance
, abs(provider_entity_amount+receiver_entity_amount) Variance_abs
FROM

(SELECT 
 LPAD(CAST(cc1.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A11.DESCRIPTION provider_legal_entity
 ,cc1.segment4|| ' - ' || A41.DESCRIPTION intercompany_receivable_Account
 , CASE WHEN :P_AMOUNT_TYPE='MTD' 
       THEN  SUM(gb1.period_net_dr_beq-gb1.period_net_cr_beq) 
	   ELSE SUM((gb1.begin_balance_dr_beq-gb1.begin_balance_cr_beq)+(gb1.period_net_dr_beq-gb1.period_net_cr_beq)) END provider_entity_amount
,gll1.currency_Code provider_currency
,Gll1.CURRENCY_CODE FROM_CURRENCY
,cc1.segment1
,cc1.segment7
,cc1.segment4
,gp1.period_name
FROM 

GL_LEDGERS gll1
,gl_code_combinations cc1
, gl_balances gb1
,gl_periods gp1
,fnd_flex_values_vl A11
,fnd_flex_values_vl A41

WHERE
 gb1.ledger_id=gll1.ledger_id
 and gll1.period_set_name=gp1.period_set_name
 and gb1.period_name=gp1.period_name
 and gb1.code_combination_id=cc1.code_combination_id
 AND A11.flex_value = cc1.segment1
	    AND A11.VALUE_CATEGORY = 'ENTITY VALUE SET'
		AND A41.flex_value = cc1.segment4
	    AND A41.VALUE_CATEGORY = 'ACCOUNT VALUE SET'
		AND GLL1.NAME LIKE UPPER('%GAAP%')
		
		AND Gll1.CURRENCY_CODE=:P_CURRENCY

AND (gp1.period_name IN (:P_PERIOD) OR 'All' IN ('All'||:P_PERIOD))		
AND (cc1.segment1 || ' - ' ||  A11.DESCRIPTION IN (:P_PROVIDER_LEGAL_ENTITY) OR 'All' IN ('All'||:P_PROVIDER_LEGAL_ENTITY))

GROUP BY
LPAD(CAST(cc1.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A11.DESCRIPTION
,cc1.segment4|| ' - ' || A41.DESCRIPTION 
,gll1.currency_Code 
,cc1.segment1
,cc1.segment7
,cc1.segment4
,gp1.period_name

) provider

,(SELECT 
LPAD(CAST(cc2.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A12.DESCRIPTION receiver_legal_entity
,cc2.segment4|| ' - ' || A42.DESCRIPTION  intercompany_payable_Account
, CASE WHEN :P_AMOUNT_TYPE='MTD' 
       THEN SUM(gb2.period_net_dr_beq-gb2.period_net_cr_beq) 
	   ELSE SUM((gb2.begin_balance_dr_beq-gb2.begin_balance_cr_beq)+(gb2.period_net_dr_beq-gb2.period_net_cr_beq))  END receiver_entity_amount

,gll2.currency_Code receiver_currency
,Gll2.CURRENCY_CODE TO_CURRENCY
,cc2.segment1
,cc2.segment7
,cc2.segment4
,gp2.period_name
FROM
gl_ledgers gll2
,gl_code_combinations cc2
, gl_balances gb2
,fnd_flex_values_vl A12
,fnd_flex_values_vl A42
,gl_periods gp2
WHERE
gb2.ledger_id=gll2.ledger_id
and gll2.period_set_name=gp2.period_set_name
and gb2.period_name=gp2.period_name
and gb2.code_combination_id=cc2.code_combination_id 
AND A12.flex_value = cc2.segment1
	    AND A12.VALUE_CATEGORY = 'ENTITY VALUE SET'
		AND A42.flex_value = cc2.segment4
	    AND A42.VALUE_CATEGORY = 'ACCOUNT VALUE SET'
		AND GLL2.NAME LIKE UPPER('%GAAP%')
		
		AND Gll2.CURRENCY_CODE=:P_CURRENCY
		
AND (gp2.period_name IN (:P_PERIOD) OR 'All' IN ('All'||:P_PERIOD))
AND (cc2.segment1 || ' - ' ||  A12.DESCRIPTION IN (:P_RECEIVER_LEGAL_ENTITY) OR 'All' IN ('All'||:P_RECEIVER_LEGAL_ENTITY))

GROUP BY
LPAD(CAST(cc2.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A12.DESCRIPTION
,cc2.segment4|| ' - ' || A42.DESCRIPTION 
,gll2.currency_Code 
,cc2.segment1
,cc2.segment7
,cc2.segment4
,gp2.period_name

) receiver
,fnd_lookup_values_vl flv

WHERE
provider.segment1=receiver.segment7
and receiver.segment1=provider.segment7
and provider.segment1<>receiver.segment1
AND provider.SEGMENT4 =FLV.LOOKUP_CODE
AND receiver.SEGMENT4 =FLV.TAG
AND FLV.LOOKUP_TYPE='XXC_IC_PAIRING'
AND ((FLV.LOOKUP_CODE || ' - ' || FLV.TAG) IN (:P_INTERCOMPANY_ACCOUNT_PAIR) OR 'All' IN ('All'||:P_INTERCOMPANY_ACCOUNT_PAIR))

UNION

SELECT 
provider.provider_legal_entity
,provider.intercompany_receivable_Account
,provider.intercompany_receivable_Account ic_rec_Account_Header
,provider.provider_entity_amount
,provider.provider_currency
,provider.TO_CURRENCY
,receiver.receiver_legal_entity
,receiver.intercompany_payable_Account
,receiver.receiver_entity_amount
,receiver.receiver_currency
,receiver.FROM_CURRENCY
,provider.period_name
, :P_CONVERSION_RATE_TYPE USER_CONVERSION_TYPE
,:P_CONVERSION_DATE CONVERSION_DATE
,receiver.CONVERSION_RATE
, provider_entity_amount+receiver_entity_amount Variance
, abs(provider_entity_amount+receiver_entity_amount) Variance_abs
FROM

(SELECT 
 LPAD(CAST(cc1.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A11.DESCRIPTION provider_legal_entity
 ,cc1.segment4|| ' - ' || A41.DESCRIPTION intercompany_receivable_Account
 , CASE WHEN :P_AMOUNT_TYPE='MTD' 
       THEN  SUM(gb1.period_net_dr_beq-gb1.period_net_cr_beq) 
	   ELSE SUM((gb1.begin_balance_dr_beq-gb1.begin_balance_cr_beq)+(gb1.period_net_dr_beq-gb1.period_net_cr_beq)) END provider_entity_amount
,gll1.currency_Code provider_currency
,Gll1.CURRENCY_CODE TO_CURRENCY
,cc1.segment1
,cc1.segment7
,cc1.segment4
,gp1.period_name
FROM 

GL_LEDGERS gll1
,gl_code_combinations cc1
, gl_balances gb1
,gl_periods gp1
,fnd_flex_values_vl A11
,fnd_flex_values_vl A41

WHERE
 gb1.ledger_id=gll1.ledger_id
 and gll1.period_set_name=gp1.period_set_name
 and gb1.period_name=gp1.period_name
 and gb1.code_combination_id=cc1.code_combination_id
 AND A11.flex_value = cc1.segment1
	    AND A11.VALUE_CATEGORY = 'ENTITY VALUE SET'
		AND A41.flex_value = cc1.segment4
	    AND A41.VALUE_CATEGORY = 'ACCOUNT VALUE SET'
		AND GLL1.NAME LIKE UPPER('%GAAP%')
		
		AND Gll1.CURRENCY_CODE=:P_CURRENCY

AND (gp1.period_name IN (:P_PERIOD) OR 'All' IN ('All'||:P_PERIOD))		
AND (cc1.segment1 || ' - ' ||  A11.DESCRIPTION IN (:P_PROVIDER_LEGAL_ENTITY) OR 'All' IN ('All'||:P_PROVIDER_LEGAL_ENTITY))

GROUP BY
LPAD(CAST(cc1.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A11.DESCRIPTION
,cc1.segment4|| ' - ' || A41.DESCRIPTION 
,gll1.currency_Code 
,cc1.segment1
,cc1.segment7
,cc1.segment4
,gp1.period_name

) provider

,(SELECT 
LPAD(CAST(cc2.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A12.DESCRIPTION receiver_legal_entity
,cc2.segment4|| ' - ' || A42.DESCRIPTION  intercompany_payable_Account
, CASE WHEN :P_AMOUNT_TYPE='MTD' 
       THEN SUM(gb2.period_net_dr_beq*gdr2.conversion_rate-gb2.period_net_cr_beq*gdr2.conversion_rate) 
	   ELSE SUM((gb2.begin_balance_dr_beq*gdr2.conversion_rate-gb2.begin_balance_cr_beq*gdr2.conversion_rate)+(gb2.period_net_dr_beq*gdr2.conversion_rate-gb2.period_net_cr_beq*gdr2.conversion_rate))  END receiver_entity_amount

,gll2.currency_Code receiver_currency
,Gll2.CURRENCY_CODE FROM_CURRENCY
,cc2.segment1
,cc2.segment7
,cc2.segment4
,gp2.period_name
, gdr2.conversion_rate
FROM
gl_ledgers gll2
,gl_code_combinations cc2
, gl_balances gb2
,fnd_flex_values_vl A12
,fnd_flex_values_vl A42
,gl_periods gp2

 ,GL_DAILY_CONVERSION_TYPES gdc
		, gl_daily_rates gdr2 
		
WHERE
gb2.ledger_id=gll2.ledger_id
and gll2.period_set_name=gp2.period_set_name
and gb2.period_name=gp2.period_name
and gb2.code_combination_id=cc2.code_combination_id 
AND A12.flex_value = cc2.segment1
	    AND A12.VALUE_CATEGORY = 'ENTITY VALUE SET'
		AND A42.flex_value = cc2.segment4
	    AND A42.VALUE_CATEGORY = 'ACCOUNT VALUE SET'
		AND GLL2.NAME LIKE UPPER('%GAAP%')
		
		AND Gll2.CURRENCY_CODE<>:P_CURRENCY
		
		               AND GDC.USER_CONVERSION_TYPE=:P_CONVERSION_RATE_TYPE
                         AND GDR2.FROM_CURRENCy= gll2.currency_Code
                        AND gdc.CONVERSION_TYPE=GDR2.CONVERSION_TYPE
						 AND  GDR2.FROM_CURRENCY<>GDR2.TO_CURRENCY
                        AND GDR2.TO_CURRENCY=:P_CURRENCY
                        AND GDR2.CONVERSION_DATE=:P_CONVERSION_DATE 
		
AND (gp2.period_name IN (:P_PERIOD) OR 'All' IN ('All'||:P_PERIOD))
AND (cc2.segment1 || ' - ' ||  A12.DESCRIPTION IN (:P_RECEIVER_LEGAL_ENTITY) OR 'All' IN ('All'||:P_RECEIVER_LEGAL_ENTITY))

GROUP BY
LPAD(CAST(cc2.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A12.DESCRIPTION
,cc2.segment4|| ' - ' || A42.DESCRIPTION 
,gll2.currency_Code 
,cc2.segment1
,cc2.segment7
,cc2.segment4
,gp2.period_name
, gdr2.conversion_rate
) receiver
,fnd_lookup_values_vl flv

WHERE
provider.segment1=receiver.segment7
and receiver.segment1=provider.segment7
and provider.segment1<>receiver.segment1
AND provider.SEGMENT4 =FLV.LOOKUP_CODE
AND receiver.SEGMENT4 =FLV.TAG
AND FLV.LOOKUP_TYPE='XXC_IC_PAIRING'
AND ((FLV.LOOKUP_CODE || ' - ' || FLV.TAG) IN (:P_INTERCOMPANY_ACCOUNT_PAIR) OR 'All' IN ('All'||:P_INTERCOMPANY_ACCOUNT_PAIR))

UNION

SELECT 
provider.provider_legal_entity
,provider.intercompany_receivable_Account
,provider.intercompany_receivable_Account ic_rec_Account_Header
,provider.provider_entity_amount
,provider.provider_currency
,provider.FROM_CURRENCY
,receiver.receiver_legal_entity
,receiver.intercompany_payable_Account
,receiver.receiver_entity_amount
,receiver.receiver_currency
,receiver.TO_CURRENCY
,provider.period_name
, :P_CONVERSION_RATE_TYPE USER_CONVERSION_TYPE
,:P_CONVERSION_DATE CONVERSION_DATE
,provider.CONVERSION_RATE
, provider_entity_amount+receiver_entity_amount Variance
, abs(provider_entity_amount+receiver_entity_amount) Variance_abs
FROM

(SELECT 
 LPAD(CAST(cc1.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A11.DESCRIPTION provider_legal_entity
 ,cc1.segment4|| ' - ' || A41.DESCRIPTION intercompany_receivable_Account
 , CASE WHEN :P_AMOUNT_TYPE='MTD' 
       THEN  SUM(gb1.period_net_dr_beq*gdr1.conversion_rate-gb1.period_net_cr_beq*gdr1.conversion_rate) 
	   ELSE SUM((gb1.begin_balance_dr_beq*gdr1.conversion_rate-gb1.begin_balance_cr_beq*gdr1.conversion_rate)+(gb1.period_net_dr_beq*gdr1.conversion_rate-gb1.period_net_cr_beq*gdr1.conversion_rate)) END provider_entity_amount
,gll1.currency_Code provider_currency
,Gll1.CURRENCY_CODE FROM_CURRENCY
,cc1.segment1
,cc1.segment7
,cc1.segment4
,gp1.period_name
,gdr1.conversion_rate
FROM 

GL_LEDGERS gll1
,gl_code_combinations cc1
, gl_balances gb1
,gl_periods gp1
,fnd_flex_values_vl A11
,fnd_flex_values_vl A41
,GL_DAILY_CONVERSION_TYPES gdc
		, gl_daily_rates gdr1
		
WHERE
 gb1.ledger_id=gll1.ledger_id
 and gll1.period_set_name=gp1.period_set_name
 and gb1.period_name=gp1.period_name
 and gb1.code_combination_id=cc1.code_combination_id
 AND A11.flex_value = cc1.segment1
	    AND A11.VALUE_CATEGORY = 'ENTITY VALUE SET'
		AND A41.flex_value = cc1.segment4
	    AND A41.VALUE_CATEGORY = 'ACCOUNT VALUE SET'
		AND GLL1.NAME LIKE UPPER('%GAAP%')
		
		AND gll1.currency_Code<>:P_CURRENCY
		
		AND GDC.USER_CONVERSION_TYPE=:P_CONVERSION_RATE_TYPE
                         AND GDR1.FROM_CURRENCy= gll1.currency_Code
                        AND gdc.CONVERSION_TYPE=GDR1.CONVERSION_TYPE
						 AND  GDR1.FROM_CURRENCY<>GDR1.TO_CURRENCY
                        AND GDR1.TO_CURRENCY=:P_CURRENCY
                        AND GDR1.CONVERSION_DATE=:P_CONVERSION_DATE 

AND (gp1.period_name IN (:P_PERIOD) OR 'All' IN ('All'||:P_PERIOD))		
AND (cc1.segment1 || ' - ' ||  A11.DESCRIPTION IN (:P_PROVIDER_LEGAL_ENTITY) OR 'All' IN ('All'||:P_PROVIDER_LEGAL_ENTITY))

GROUP BY
LPAD(CAST(cc1.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A11.DESCRIPTION
,cc1.segment4|| ' - ' || A41.DESCRIPTION 
,gll1.currency_Code 
,cc1.segment1
,cc1.segment7
,cc1.segment4
,gp1.period_name
,gdr1.conversion_rate

) provider

,(SELECT 
LPAD(CAST(cc2.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A12.DESCRIPTION receiver_legal_entity
,cc2.segment4|| ' - ' || A42.DESCRIPTION  intercompany_payable_Account
, CASE WHEN :P_AMOUNT_TYPE='MTD' 
       THEN SUM(gb2.period_net_dr_beq-gb2.period_net_cr_beq) 
	   ELSE SUM((gb2.begin_balance_dr_beq-gb2.begin_balance_cr_beq)+(gb2.period_net_dr_beq-gb2.period_net_cr_beq))  END receiver_entity_amount

,gll2.currency_Code receiver_currency
,Gll2.CURRENCY_CODE TO_CURRENCY
,cc2.segment1
,cc2.segment7
,cc2.segment4
,gp2.period_name
FROM
gl_ledgers gll2
,gl_code_combinations cc2
, gl_balances gb2
,fnd_flex_values_vl A12
,fnd_flex_values_vl A42
,gl_periods gp2
WHERE
gb2.ledger_id=gll2.ledger_id
and gll2.period_set_name=gp2.period_set_name
and gb2.period_name=gp2.period_name
and gb2.code_combination_id=cc2.code_combination_id 
AND A12.flex_value = cc2.segment1
	    AND A12.VALUE_CATEGORY = 'ENTITY VALUE SET'
		AND A42.flex_value = cc2.segment4
	    AND A42.VALUE_CATEGORY = 'ACCOUNT VALUE SET'
		AND GLL2.NAME LIKE UPPER('%GAAP%')
		
		AND gll2.currency_Code=:P_CURRENCY
		
AND (gp2.period_name IN (:P_PERIOD) OR 'All' IN ('All'||:P_PERIOD))
AND (cc2.segment1 || ' - ' ||  A12.DESCRIPTION IN (:P_RECEIVER_LEGAL_ENTITY) OR 'All' IN ('All'||:P_RECEIVER_LEGAL_ENTITY))

GROUP BY
LPAD(CAST(cc2.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A12.DESCRIPTION
,cc2.segment4|| ' - ' || A42.DESCRIPTION 
,gll2.currency_Code 
,cc2.segment1
,cc2.segment7
,cc2.segment4
,gp2.period_name

) receiver
,fnd_lookup_values_vl flv

WHERE
provider.segment1=receiver.segment7
and receiver.segment1=provider.segment7
and provider.segment1<>receiver.segment1
AND provider.SEGMENT4 =FLV.LOOKUP_CODE
AND receiver.SEGMENT4 =FLV.TAG
AND FLV.LOOKUP_TYPE='XXC_IC_PAIRING'
AND ((FLV.LOOKUP_CODE || ' - ' || FLV.TAG) IN (:P_INTERCOMPANY_ACCOUNT_PAIR) OR 'All' IN ('All'||:P_INTERCOMPANY_ACCOUNT_PAIR))

UNION

SELECT 
provider.provider_legal_entity
,provider.intercompany_receivable_Account
,provider.intercompany_receivable_Account ic_rec_Account_Header
,provider.provider_entity_amount
,provider.provider_currency
,provider.FROM_CURRENCY
,receiver.receiver_legal_entity
,receiver.intercompany_payable_Account
,receiver.receiver_entity_amount
,receiver.receiver_currency
,receiver.TO_CURRENCY
,provider.period_name
, :P_CONVERSION_RATE_TYPE USER_CONVERSION_TYPE
,:P_CONVERSION_DATE CONVERSION_DATE
,provider.CONVERSION_RATE
, provider_entity_amount+receiver_entity_amount Variance
, abs(provider_entity_amount+receiver_entity_amount) Variance_abs
FROM

(SELECT 
 LPAD(CAST(cc1.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A11.DESCRIPTION provider_legal_entity
 ,cc1.segment4|| ' - ' || A41.DESCRIPTION intercompany_receivable_Account
 , CASE WHEN :P_AMOUNT_TYPE='MTD' 
       THEN  SUM(gb1.period_net_dr_beq*gdr1.CONVERSION_rATE-gb1.period_net_cr_beq*gdr1.CONVERSION_rATE) 
	   ELSE SUM((gb1.begin_balance_dr_beq*gdr1.CONVERSION_rATE-gb1.begin_balance_cr_beq*gdr1.CONVERSION_rATE)+(gb1.period_net_dr_beq*gdr1.CONVERSION_rATE-gb1.period_net_cr_beq*gdr1.CONVERSION_rATE)) END provider_entity_amount
,gll1.currency_Code provider_currency
,Gll1.CURRENCY_CODE FROM_CURRENCY
,cc1.segment1
,cc1.segment7
,cc1.segment4
,gp1.period_name
,gdr1.CONVERSION_rATE
FROM 

GL_LEDGERS gll1
,gl_code_combinations cc1
, gl_balances gb1
,gl_periods gp1
,fnd_flex_values_vl A11
,fnd_flex_values_vl A41
,GL_DAILY_CONVERSION_TYPES gdc
		, gl_daily_rates gdr1 

WHERE
 gb1.ledger_id=gll1.ledger_id
 and gll1.period_set_name=gp1.period_set_name
 and gb1.period_name=gp1.period_name
 and gb1.code_combination_id=cc1.code_combination_id
 AND A11.flex_value = cc1.segment1
	    AND A11.VALUE_CATEGORY = 'ENTITY VALUE SET'
		AND A41.flex_value = cc1.segment4
	    AND A41.VALUE_CATEGORY = 'ACCOUNT VALUE SET'
		AND GLL1.NAME LIKE UPPER('%GAAP%')
		
		AND gll1.currency_Code<>:P_CURRENCY
		
		AND GDC.USER_CONVERSION_TYPE=:P_CONVERSION_RATE_TYPE
                         AND GDR1.FROM_CURRENCy= gll1.currency_Code
                        AND gdc.CONVERSION_TYPE=GDR1.CONVERSION_TYPE
						 AND  GDR1.FROM_CURRENCY<>GDR1.TO_CURRENCY
                        AND GDR1.TO_CURRENCY=:P_CURRENCY
                        AND GDR1.CONVERSION_DATE=:P_CONVERSION_DATE 

AND (gp1.period_name IN (:P_PERIOD) OR 'All' IN ('All'||:P_PERIOD))		
AND (cc1.segment1 || ' - ' ||  A11.DESCRIPTION IN (:P_PROVIDER_LEGAL_ENTITY) OR 'All' IN ('All'||:P_PROVIDER_LEGAL_ENTITY))

GROUP BY
LPAD(CAST(cc1.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A11.DESCRIPTION
,cc1.segment4|| ' - ' || A41.DESCRIPTION 
,gll1.currency_Code 
,cc1.segment1
,cc1.segment7
,cc1.segment4
,gp1.period_name
,gdr1.CONVERSION_rATE
) provider

,(SELECT 
LPAD(CAST(cc2.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A12.DESCRIPTION receiver_legal_entity
,cc2.segment4|| ' - ' || A42.DESCRIPTION  intercompany_payable_Account
, CASE WHEN :P_AMOUNT_TYPE='MTD' 
       THEN SUM(gb2.period_net_dr_beq*gdr2.conversion_rate-gb2.period_net_cr_beq*gdr2.conversion_rate) 
	   ELSE SUM((gb2.begin_balance_dr_beq*gdr2.conversion_rate-gb2.begin_balance_cr_beq*gdr2.conversion_rate)+(gb2.period_net_dr_beq*gdr2.conversion_rate-gb2.period_net_cr_beq*gdr2.conversion_rate))  END receiver_entity_amount

,gll2.currency_Code receiver_currency
,Gll2.CURRENCY_CODE TO_CURRENCY
,cc2.segment1
,cc2.segment7
,cc2.segment4
,gp2.period_name
, gdr2.conversion_rate
FROM
gl_ledgers gll2
,gl_code_combinations cc2
, gl_balances gb2
,fnd_flex_values_vl A12
,fnd_flex_values_vl A42
,gl_periods gp2

 ,GL_DAILY_CONVERSION_TYPES gdc
		, gl_daily_rates gdr2 
		
WHERE
gb2.ledger_id=gll2.ledger_id
and gll2.period_set_name=gp2.period_set_name
and gb2.period_name=gp2.period_name
and gb2.code_combination_id=cc2.code_combination_id 
AND A12.flex_value = cc2.segment1
	    AND A12.VALUE_CATEGORY = 'ENTITY VALUE SET'
		AND A42.flex_value = cc2.segment4
	    AND A42.VALUE_CATEGORY = 'ACCOUNT VALUE SET'
		AND GLL2.NAME LIKE UPPER('%GAAP%')
		
		AND Gll2.CURRENCY_CODE<>:P_CURRENCY
		
		               AND GDC.USER_CONVERSION_TYPE=:P_CONVERSION_RATE_TYPE
                         AND GDR2.FROM_CURRENCy= gll2.currency_Code
                        AND gdc.CONVERSION_TYPE=GDR2.CONVERSION_TYPE
						 AND  GDR2.FROM_CURRENCY<>GDR2.TO_CURRENCY
                        AND GDR2.TO_CURRENCY=:P_CURRENCY
                        AND GDR2.CONVERSION_DATE=:P_CONVERSION_DATE 
		
AND (gp2.period_name IN (:P_PERIOD) OR 'All' IN ('All'||:P_PERIOD))
AND (cc2.segment1 || ' - ' ||  A12.DESCRIPTION IN (:P_RECEIVER_LEGAL_ENTITY) OR 'All' IN ('All'||:P_RECEIVER_LEGAL_ENTITY))

GROUP BY
LPAD(CAST(cc2.segment1 AS VARCHAR(4)), 4, '0') || ' - ' || A12.DESCRIPTION
,cc2.segment4|| ' - ' || A42.DESCRIPTION 
,gll2.currency_Code 
,cc2.segment1
,cc2.segment7
,cc2.segment4
,gp2.period_name
, gdr2.conversion_rate
) receiver
,fnd_lookup_values_vl flv

WHERE
provider.segment1=receiver.segment7
and receiver.segment1=provider.segment7
and provider.segment1<>receiver.segment1
AND provider.SEGMENT4 =FLV.LOOKUP_CODE
AND receiver.SEGMENT4 =FLV.TAG
AND FLV.LOOKUP_TYPE='XXC_IC_PAIRING'
AND ((FLV.LOOKUP_CODE || ' - ' || FLV.TAG) IN (:P_INTERCOMPANY_ACCOUNT_PAIR) OR 'All' IN ('All'||:P_INTERCOMPANY_ACCOUNT_PAIR))

ORDER BY 
intercompany_receivable_Account
,intercompany_payable_Account
,provider_legal_entity
,receiver_legal_entity

)
GROUP BY
intercompany_receivable_Account
,intercompany_payable_Account

:P_AMOUNT_TYPE :P_CONVERSION_DATE :P_CONVERSION_RATE_TYPE :P_CURRENCY :P_INTERCOMPANY_ACCOUNT_PAIR :P_PERIOD :P_PROVIDER_LEGAL_ENTITY :P_RECEIVER_LEGAL_ENTITY

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_CODE_COMBINATIONS	dimension	dimension
GL_BALANCES	dimension	dimension
GL_PERIODS	dimension	dimension
FND_FLEX_VALUES_VL	dimension	dimension
Imbalance	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.

Intercompany 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_CODE_COMBINATIONS	7	61
GL_BALANCES	Setup / configuration table — joined for reference, not exposed for analytics
GL_PERIODS	Setup / configuration table — joined for reference, not exposed for analytics
FND_FLEX_VALUES_VL	72	0
GL_DAILY_CONVERSION_TYPES	2	20
GL_DAILY_RATES	1	10

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.