Unrealized Foreign Currency Revaluation Report - Banks
The banks-only view of unrealized foreign-currency gain or loss — revaluation limited to bank and cash accounts — used to isolate FX movement on cash positions at period end.
Related A scoped view of the Unrealized Foreign Currency Revaluation Report — limited to bank and cash accounts; see also the all-revaluations 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 - Banks — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
| Account | Currency | Entered Balance | Revalued Balance | Unrealized G/l |
|---|---|---|---|---|
| 1000-2100-000 | USD | $1,240,500.00 | $1,240,500.00 | Sample |
| 1000-5400-000 | USD | $842,150.75 | $842,150.75 | — |
| 1000-1410-000 | USD | $96,400.00 | $96,400.00 | Sample |
| 2000-2100-000 | USD | $1,005,233.10 | $1,005,233.10 | — |
| 1000-6300-000 | USD | $58,720.40 | $58,720.40 | Sample |
| 1000-2100-000 | USD | $1,240,500.00 | $1,240,500.00 | Sample |
The report revalues only bank and cash accounts to the period-end rate, isolating FX on cash positions.
One bank account's revalued balance won't agree to the bank reconciliation because it was revalued on a different rate than treasury used.
Use the same period-end rate source for GL revaluation and the bank reconciliation; a rate mismatch is why cash FX won't tie.
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.SEGMENT1 AS NUMERIC)= CAST(GRR.SEGMENT1_LOW AS NUMERIC) AND CAST(GCC1.SEGMENT4 AS NUMERIC)= CAST(GRR.SEGMENT4_LOW AS NUMERIC))
OR
(CAST(GCC1.SEGMENT1 AS NUMERIC) IS NULL AND CAST(GCC1.SEGMENT4 AS NUMERIC)= CAST(GRR.SEGMENT4_LOW 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 | |