Unrealized Foreign Currency Revaluation Report - All revaluations
The all-revaluations view of unrealized foreign-currency gain or loss — every revaluation rule's accounts in one run, for a complete period-end FX picture rather than a single scoped range.
Related A scoped view of the Unrealized Foreign Currency Revaluation Report — this one runs every rule at once; see also the Banks-only 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 Unrealized Foreign Currency Revaluation Report - All revaluations — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
| Rule | Account | Currency | Entered Balance | Revalued Balance | Unrealized G/l |
|---|---|---|---|---|---|
| Standard | 1000-2100-000 | USD | $1,240,500.00 | $1,240,500.00 | Sample |
| Corporate | 1000-5400-000 | USD | $842,150.75 | $842,150.75 | — |
| Standard | 1000-1410-000 | USD | $96,400.00 | $96,400.00 | Sample |
| Default | 2000-2100-000 | USD | $1,005,233.10 | $1,005,233.10 | — |
| Standard | 1000-6300-000 | USD | $58,720.40 | $58,720.40 | Sample |
| Standard | 1000-2100-000 | USD | $1,240,500.00 | $1,240,500.00 | Sample |
The report runs every revaluation rule together, summing the unrealized gain or loss across all account ranges.
The combined total is larger than any single rule's because it includes intercompany balances some scoped runs exclude — confirm that's intended for the statement.
Decide once whether intercompany FX belongs in the headline unrealized figure, and use the matching scope consistently each period.
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_DAILY_RATES
- GL_BALANCES
- GL_CODE_COMBINATIONS
- GL_LEDGERS
- GL_REVALUATIONS
Show / hide SQL
SELECT /*+parallel(12)*/
LEDGER_NAME
, ACCOUNTING_STRING
, ACCOUNT_DECSCRIPTION
, FUNCTIONAL_CURRENCY
, JOURNAL_CURRENCY
, FOREIGN_CURRENCY_AMOUNT
, FUNCTIONAL_CURRENCY_AMOUNT
, ROUND(DECODE(FOREIGN_CURRENCY_AMOUNT,0,0,(FUNCTIONAL_CURRENCY_AMOUNT/FOREIGN_CURRENCY_AMOUNT)),7) AVERAGE_RATE
, ROUND(REVALUATION_RATE,30) REVALUATION_RATE
, (FOREIGN_CURRENCY_AMOUNT*REVALUATION_RATE) REVALUATED_BALANCE
, DECODE(FUNCTIONAL_CURRENCY_AMOUNT,0,0,(((FOREIGN_CURRENCY_AMOUNT*REVALUATION_RATE)-FUNCTIONAL_CURRENCY_AMOUNT)/FUNCTIONAL_CURRENCY_AMOUNT)*100) VARIANCE_PERCENT
, (FUNCTIONAL_CURRENCY_AMOUNT-(FOREIGN_CURRENCY_AMOUNT*REVALUATION_RATE)) UNREALIZED_GAIN_LOSS_AFT_REV
, ENTITY
, chart_of_accounts_id,code_combination_id
FROM
(
SELECT /*+parallel(12)*/
GL.name LEDGER_NAME
, GCC.segment1||'.'||GCC.segment2||'.'||GCC.segment3||'.'||GCC.segment4||'.'||GCC.segment5||'.'||GCC.segment6||'.'||GCC.segment7||'.'||GCC.segment8||'.'||GCC.segment9 ACCOUNTING_STRING
, FFVV.description ACCOUNT_DECSCRIPTION
--, GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID,4,GCC.SEGMENT4) ACCOUNT_DECSCRIPTION
, GL.currency_code FUNCTIONAL_CURRENCY
, GB.currency_code JOURNAL_CURRENCY
, (NVL(GB.begin_balance_dr,0))-(NVL(GB.begin_balance_cr,0))+(NVL(GB.period_net_dr,0))-(NVL(GB.period_net_cr,0)) FOREIGN_CURRENCY_AMOUNT
, (NVL(GB.begin_balance_dr_beq,0))-(NVL(GB.begin_balance_cr_beq,0))+(NVL(GB.period_net_dr_beq,0))-(NVL(GB.period_net_cr_beq,0)) FUNCTIONAL_CURRENCY_AMOUNT
, (SELECT
DISTINCT GDR.Conversion_Rate
FROM
gl_daily_conversion_types GDC
, gl_daily_rates GDR
WHERE
1 =1
AND GDR.conversion_type =GDC.conversion_type
AND GDR.from_currency =GB.currency_code
AND GDR.to_currency =GL.currency_code
AND (GDC.User_Conversion_Type IN (:P_RATE_TYPE) OR LEAST (:P_RATE_TYPE) IS NULL)
AND GDR.Conversion_Date =:P_CONVERSION_DATE
--AND Rownum = 1
) REVALUATION_RATE
, GCC.segment1 ENTITY
, GCC.chart_of_accounts_id,GCC.code_combination_id
FROM
gl_balances GB
, gl_code_combinations GCC
, gl_ledgers GL
, fnd_flex_values_vl FFVV
WHERE
1=1
AND GB.code_combination_id =GCC.code_combination_id
AND GB.ledger_id =GL.ledger_id
AND GCC.chart_of_accounts_id =GL.chart_of_accounts_id
AND GB.translated_flag IS NOT NULL
AND FFVV.flex_value =GCC.segment4
AND FFVV.flex_value_set_id IN (SELECT FLEX_VALUE_SET_ID
FROM fnd_id_flex_segments
WHERE
1=1
and GL.chart_of_accounts_id = id_flex_num
and application_id = '101'
AND id_flex_code = 'GL#'
AND enabled_flag = 'Y'
AND application_column_name = 'SEGMENT4')
AND EXISTS (SELECT /*+parallel(12)*/
CODE_COMBINATION_ID
FROM
gl_code_combinations GCC1,
gl_revaluations GR,
gl_reval_account_ranges GRR
WHERE
1 = 1
AND CAST(GCC1.SEGMENT4 AS NUMERIC) BETWEEN NVL(CAST(GRR.SEGMENT4_LOW AS NUMERIC), CAST(GCC1.SEGMENT4 AS NUMERIC)) AND NVL(CAST(GRR.SEGMENT4_HIGH AS NUMERIC), CAST(GCC1.SEGMENT4 AS NUMERIC))
AND GRR.REVALUATION_ID = GR.REVALUATION_ID
AND GCC.CODE_COMBINATION_ID = GCC1.CODE_COMBINATION_ID
AND GL.CHART_OF_ACCOUNTS_ID=GR.CHART_OF_ACCOUNTS_ID
AND (GR.NAME IN (:P_REVAL) OR 'All' IN (:P_REVAL||'All'))
AND (SELECT DISTINCT GDC.User_Conversion_Type
FROM
gl_daily_conversion_types GDC
WHERE
1 =1
AND GR.conversion_rate_type =GDC.conversion_type)=:P_RATE_TYPE
AND GCC1.account_type IN ('L','A')
)
AND (GL.NAME IN (SELECT DISTINCT GL.NAME
FROM
gl_ledger_set_assignments GLSA,
gl_ledgers GLS
WHERE
GLSA.ledger_set_id =GLS.ledger_id
AND GL.ledger_id =GLSA.ledger_id
AND UPPER(GL.LEDGER_CATEGORY_CODE) <> 'NONE'
AND GLS.name = :P_LEDGER) OR GL.NAME IN (:P_LEDGER) OR 'All' IN (:P_LEDGER||'All'))
AND CAST(GCC.segment1 AS NUMERIC) BETWEEN NVL(:P_FROM_ENTITY,CAST(GCC.segment1 AS NUMERIC)) AND NVL(:P_TO_ENTITY,CAST(GCC.segment1 AS NUMERIC))
AND (GB.period_name IN (:P_PERIOD) OR 'All' IN (:P_PERIOD||'All'))
)
ORDER BY
LEDGER_NAME,
JOURNAL_CURRENCYThe 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_CODE_COMBINATIONS | dimension | dimension |
| GL_LEDGERS | dimension | dimension |
| GL_REVALUATIONS | dimension | dimension |
| Entered Balance | measure | measure |
| Revalued 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_DAILY_RATES | 1 | 10 |
| GL_BALANCES | Setup / configuration table — joined for reference, not exposed for analytics | |
| GL_CODE_COMBINATIONS | 7 | 61 |
| GL_LEDGERS | 10 | 104 |
| GL_REVALUATIONS | Setup / configuration table — joined for reference, not exposed for analytics | |