Revaluation Rules Report

Control Reports

The general-ledger revaluation rules defined for foreign-currency balances — the account ranges, conversion type, and gain and loss accounts each rule uses — so finance can confirm period-end FX revaluation covers the right accounts.

Sample build of the Revaluation Rules Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Sample

Coa

Sample

Revaluation rules

240

Accounts covered

Coverage gaps

Revaluation Rule	Account Range	Conversion Type	Gain Account	Loss Account
Standard	1000-2100-000	Standard	1000-2100-000	1000-2100-000
Corporate	1000-5400-000	Corporate	1000-5400-000	1000-5400-000
Standard	1000-1410-000	Standard	1000-1410-000	1000-1410-000
Default	2000-2100-000	Default	2000-2100-000	2000-2100-000
Standard	1000-6300-000	Standard	1000-6300-000	1000-6300-000
Standard	1000-2100-000	Standard	1000-2100-000	1000-2100-000

AI Analyst · active

reading

The report reads GL_REVALUATIONS with its account ranges and conversion types, showing which balances each rule revalues.

flag

Two foreign-currency liability accounts fall outside every rule's range — their balances will not be revalued at period end, understating unrealized FX gain or loss.

root cause & next step

Extend a rule's account range to include the two accounts, or add a rule; uncovered FX accounts are a recurring period-end revaluation miss.

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_REVALUATIONS
FND_ID_FLEX_STRUCTURES_VL
GL_DAILY_CONVERSION_TYPES
GL_CODE_COMBINATIONS
GL_REVAL_ACCOUNT_RANGES

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
 NAME,
 DESCRIPTION,
 ID_FLEX_STRUCTURE_NAME,
 FROM_CURRENCY_OPTION_CODE,
 USER_CONVERSION_TYPE,
 DAYS_TO_ROLL_FORWARD,
 ABCREAL_GAIN,
 ABCREAL_LOSS,
 INCOME_STMT_ACCOUNTS_RULE,
 AUTOMATIC_POST_FLAG,
 Entity_range,
 selling_range,
 cc_range,
 acc_range,
 loc_range,
 brand_range,
 ic_range,
 CREATED_BY,
 TO_CHAR(CREATION_DATE,'dd-Mon-YYYY HH:MM AM','NLS_DATE_LANGUAGE=English') CREATION_DATE,
 LAST_UPDATED_BY,
 TO_CHAR(LAST_UPDATE_DATE,'dd-Mon-YYYY HH:MM AM','NLS_DATE_LANGUAGE=English') LAST_UPDATE_DATE
FROM
(
SELECT
GR.NAME,
GR.DESCRIPTION,
FIFSV.ID_FLEX_STRUCTURE_NAME,
GR.FROM_CURRENCY_CODE FROM_CURRENCY_OPTION_CODE,
--GR.FROM_CURRENCY_OPTION_CODE,
GDCT.USER_CONVERSION_TYPE,
GR.DAYS_TO_ROLL_FORWARD,
GCC1.CONCATENATED_SEGMENTS ABCREAL_GAIN,
GCC2.CONCATENATED_SEGMENTS ABCREAL_LOSS,
GR.INCOME_STMT_ACCOUNTS_RULE,
GR.AUTOMATIC_POST_FLAG,
MIN(GRAR.SEGMENT1_LOW)||'-'||MAX(GRAR.SEGMENT1_HIGH) Entity_range,
MIN(GRAR.SEGMENT2_LOW)||'-'||MAX(GRAR.SEGMENT2_HIGH) selling_range,
MIN(GRAR.SEGMENT3_LOW)||'-'||MAX(GRAR.SEGMENT3_HIGH) cc_range,
MIN(GRAR.SEGMENT4_LOW)||'-'||MAX(GRAR.SEGMENT4_HIGH) acc_range,
MIN(GRAR.SEGMENT5_LOW)||'-'||MAX(GRAR.SEGMENT5_HIGH) loc_range,
MIN(GRAR.SEGMENT6_LOW)||'-'||MAX(GRAR.SEGMENT6_HIGH) brand_range,
MIN(GRAR.SEGMENT7_LOW)||'-'||MAX(GRAR.SEGMENT7_HIGH) ic_range,
GR.CREATED_BY,
GR.CREATION_DATE,
GR.LAST_UPDATED_BY,
GR.LAST_UPDATE_DATE

FROM
GL_REVALUATIONS GR,
FND_ID_FLEX_STRUCTURES_VL FIFSV,
GL_DAILY_CONVERSION_TYPES GDCT,
GL_CODE_COMBINATIONS GCC1,
GL_CODE_COMBINATIONS GCC2,
GL_REVAL_ACCOUNT_RANGES GRAR
WHERE 1=1
AND GR.CHART_OF_ACCOUNTS_ID=FIFSV.ID_FLEX_NUM(+)
AND GR.CONVERSION_RATE_TYPE=GDCT.CONVERSION_TYPE(+)
AND GR.UNREALIZED_GAIN_CCID=GCC1.CODE_COMBINATION_ID(+)
AND GR.UNREALIZED_LOSS_CCID=GCC2.CODE_COMBINATION_ID(+)
AND GR.REVALUATION_ID=GRAR.REVALUATION_ID(+)
AND (FIFSV.ID_FLEX_STRUCTURE_NAME IN (:P_COA)
OR  'ALL' IN(:P_COA||'ALL'))
--AND GR.NAME='350011 Global COA Revaluation'
GROUP BY
GR.NAME,
GR.DESCRIPTION,
FIFSV.ID_FLEX_STRUCTURE_NAME,
GR.FROM_CURRENCY_CODE,
GDCT.USER_CONVERSION_TYPE,
GR.DAYS_TO_ROLL_FORWARD,
GCC1.CONCATENATED_SEGMENTS ,
GCC2.CONCATENATED_SEGMENTS ,
GR.INCOME_STMT_ACCOUNTS_RULE,
GR.AUTOMATIC_POST_FLAG,
GR.CREATED_BY,
GR.CREATION_DATE,
GR.LAST_UPDATED_BY,
GR.LAST_UPDATE_DATE

UNION

SELECT
'' NAME,
'' DESCRIPTION,
'' ID_FLEX_STRUCTURE_NAME,
'' FROM_CURRENCY_OPTION_CODE,
'NO DATA' USER_CONVERSION_TYPE,
TO_NUMBER('') DAYS_TO_ROLL_FORWARD,
'' ABCREAL_GAIN,
'' ABCREAL_LOSS,
'' INCOME_STMT_ACCOUNTS_RULE,
'' AUTOMATIC_POST_FLAG,
'' Entity_range,
'' selling_range,
'' cc_range,
'' acc_range,
'' loc_range,
'' brand_range,
'' ic_range,
'' CREATED_BY,
TO_DATE('') CREATION_DATE,
'' LAST_UPDATED_BY,
TO_DATE('') LAST_UPDATE_DATE

FROM
GL_REVALUATIONS GR
WHERE NOT EXISTS(
SELECT
GR.NAME,
GR.DESCRIPTION,
FIFSV.ID_FLEX_STRUCTURE_NAME,
GR.FROM_CURRENCY_CODE,
GDCT.USER_CONVERSION_TYPE,
GR.DAYS_TO_ROLL_FORWARD,
GCC1.CONCATENATED_SEGMENTS ABCREAL_GAIN,
GCC2.CONCATENATED_SEGMENTS ABCREAL_LOSS,
GR.INCOME_STMT_ACCOUNTS_RULE,
GR.AUTOMATIC_POST_FLAG,
MIN(GRAR.SEGMENT1_LOW)||'-'||MAX(GRAR.SEGMENT1_HIGH) Entity_range,
MIN(GRAR.SEGMENT2_LOW)||'-'||MAX(GRAR.SEGMENT2_HIGH) selling_range,
MIN(GRAR.SEGMENT3_LOW)||'-'||MAX(GRAR.SEGMENT3_HIGH) cc_range,
MIN(GRAR.SEGMENT4_LOW)||'-'||MAX(GRAR.SEGMENT4_HIGH) acc_range,
MIN(GRAR.SEGMENT5_LOW)||'-'||MAX(GRAR.SEGMENT5_HIGH) loc_range,
MIN(GRAR.SEGMENT6_LOW)||'-'||MAX(GRAR.SEGMENT6_HIGH) brand_range,
MIN(GRAR.SEGMENT7_LOW)||'-'||MAX(GRAR.SEGMENT7_HIGH) ic_range,
GR.CREATED_BY,
GR.CREATION_DATE,
GR.LAST_UPDATED_BY,
GR.LAST_UPDATE_DATE

FROM
GL_REVALUATIONS GR,
FND_ID_FLEX_STRUCTURES_VL FIFSV,
GL_DAILY_CONVERSION_TYPES GDCT,
GL_CODE_COMBINATIONS GCC1,
GL_CODE_COMBINATIONS GCC2,
GL_REVAL_ACCOUNT_RANGES GRAR
WHERE 1=1
AND GR.CHART_OF_ACCOUNTS_ID=FIFSV.ID_FLEX_NUM(+)
AND GR.CONVERSION_RATE_TYPE=GDCT.CONVERSION_TYPE(+)
AND GR.UNREALIZED_GAIN_CCID=GCC1.CODE_COMBINATION_ID(+)
AND GR.UNREALIZED_LOSS_CCID=GCC2.CODE_COMBINATION_ID(+)
AND GR.REVALUATION_ID=GRAR.REVALUATION_ID(+)
AND (FIFSV.ID_FLEX_STRUCTURE_NAME IN (:P_COA)
OR  'ALL' IN(:P_COA||'ALL'))
--AND GR.NAME='350011 Global COA Revaluation'
GROUP BY
GR.NAME,
GR.DESCRIPTION,
FIFSV.ID_FLEX_STRUCTURE_NAME,
GR.FROM_CURRENCY_CODE,
GDCT.USER_CONVERSION_TYPE,
GR.DAYS_TO_ROLL_FORWARD,
GCC1.CONCATENATED_SEGMENTS ,
GCC2.CONCATENATED_SEGMENTS ,
GR.INCOME_STMT_ACCOUNTS_RULE,
GR.AUTOMATIC_POST_FLAG,
GR.CREATED_BY,
GR.CREATION_DATE,
GR.LAST_UPDATED_BY,
GR.LAST_UPDATE_DATE
)
)
ORDER BY
ID_FLEX_STRUCTURE_NAME,
NAME,
acc_range,
loc_range,
cc_range

:MM :P_COA

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
FND_ID_FLEX_STRUCTURES_VL	dimension	dimension
GL_DAILY_CONVERSION_TYPES	dimension	dimension
GL_CODE_COMBINATIONS	dimension	dimension
GL_REVAL_ACCOUNT_RANGES	dimension	dimension
Amount	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.

Control Reports 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_REVALUATIONS	Setup / configuration table — joined for reference, not exposed for analytics
FND_ID_FLEX_STRUCTURES_VL	Setup / configuration table — joined for reference, not exposed for analytics
GL_DAILY_CONVERSION_TYPES	2	20
GL_CODE_COMBINATIONS	7	61
GL_REVAL_ACCOUNT_RANGES	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.