Results for “gl_translation_rates_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

GL_TRANSLATION_RATES_V is a General Ledger (GL) reporting view in Oracle E-Business Suite 12.1.1 and 12.2.2 that exposes currency translation rates maintained for each accounting period and set of books. It consolidates average and end-of-period (EOP) translation rates together with period attributes and the functional currency of the ledger, presenting them in a denormalized form suitable for inquiry screens, concurrent programs, and integration extracts. Because it joins rate rows to period status records, the view only surfaces rates whose corresponding period has a defined status row for the GL application, which typically restricts output to periods that have been opened or otherwise established in the ledger calendar.

The view is read-only. It is defined in the GL schema and is commonly referenced by translation and revaluation reporting logic where both the direct rate and its reciprocal are required.

Underlying Base Objects

The view text is defined over three base objects joined on the set of books and period name:

• GL_TRANSLATION_RATES (aliased R) — the primary rate table supplying average and end-of-period rates, numerators and denominators, currency codes, and audit columns. ROW_ID is derived from its ROWID.
• GL_SETS_OF_BOOKS (aliased S) — supplies the ledger's functional currency as FUNCTIONAL_CURRENCY, joined on SET_OF_BOOKS_ID.
• GL_PERIOD_STATUSES (aliased P) — supplies PERIOD_NUM, PERIOD_YEAR, EFFECTIVE_PERIOD_NUM and PERIOD_END_DATE, joined on SET_OF_BOOKS_ID and PERIOD_NAME.

The join to GL_PERIOD_STATUSES is further constrained with P.APPLICATION_ID = DECODE(R.SET_OF_BOOKS_ID, 0, 101, 101), which resolves to application 101 (General Ledger) for all ledgers, ensuring period attributes are taken from GL rather than another subledger.

Key Columns

• SET_OF_BOOKS_ID — identifies the ledger for which the rate is defined.
• PERIOD_NAME, PERIOD_NUM, PERIOD_YEAR, EFFECTIVE_PERIOD_NUM, PERIOD_END_DATE — the accounting period and its calendar attributes.
• TO_CURRENCY_CODE, FUNCTIONAL_CURRENCY — the target currency of translation and the ledger's functional currency.
• AVG_RATE / SHOW_AVG_RATE — the average rate and the same value rounded to ten decimal places.
• EOP_RATE / SHOW_EOP_RATE — the end-of-period rate and its rounded form.
• REV_RATE / SHOW_REV_RATE — the reciprocal of the EOP rate, computed as DECODE(R.EOP_RATE, 0, 0, (1/R.EOP_RATE)) and correspondingly rounded. Both return zero when the EOP rate is zero, avoiding division-by-zero errors. This is the rev_rate column referenced in user searches.
• AVG_RATE_NUMERATOR / AVG_RATE_DENOMINATOR / EOP_RATE_NUMERATOR / EOP_RATE_DENOMINATOR — the components used to express rates in ratio form.
• UPDATE_FLAG, ACTUAL_FLAG, ATTRIBUTE1–ATTRIBUTE5, CONTEXT — descriptive and descriptive-flexfield columns.
• Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include reviewing translation rates by period, extracting inverse rates for reporting, and validating that rate rows exist for every open period prior to running translation.

• Retrieve the reciprocal rate for a ledger and period:

SELECT period_name, to_currency_code, functional_currency,
       eop_rate, rev_rate, show_rev_rate
FROM   gl_translation_rates_v
WHERE  set_of_books_id = :ledger_id
AND    period_name = :period_name;

• List all rates for a ledger ordered by period:

SELECT period_year, period_num, period_name,
       to_currency_code, avg_rate, eop_rate, rev_rate
FROM   gl_translation_rates_v
WHERE  set_of_books_id = :ledger_id
ORDER  BY period_year, effective_period_num, to_currency_code;

• Detect periods with a zero or missing EOP rate, where REV_RATE is returned as zero by the DECODE logic.