Results for “gl_translation_rates”

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

Overview

GL_TRANSLATION_RATES is an APPS-owned database view in the General Ledger (GL) module of Oracle E-Business Suite, valid in release 12.1.1 and 12.2.2. It presents, in a single denormalized result set, the period-end and period-average conversion rates that General Ledger applies when translating account balances from a ledger's functional currency into a foreign reporting currency. Rather than requiring report developers to join GL_LEDGERS, GL_PERIOD_STATUSES, GL_DAILY_RATES, and GL_ROW_MULTIPLIERS manually, the view encapsulates that logic and exposes one row per ledger, accounting period, target currency, and rate type category (actual or budget).

The view is read-only and is intended for reporting and integration rather than transactional maintenance. Its principal role is to support translation-related reporting, reconciliation of translated balances, and external data extraction where downstream systems need the same average and end-of-period rates that Oracle General Ledger used during the translation process.

Underlying Base Objects

The view is defined over four referenced objects, all exposed through APPS synonyms:

• GL_LEDGERS — supplies the ledger identifier, functional currency, and the rate types configured for period-end, period-average, budget period-end, and budget period-average translation.
• GL_PERIOD_STATUSES — supplies the accounting period name and end date, restricted to APPLICATION_ID = 101 (General Ledger).
• GL_DAILY_RATES — referenced twice (aliases PERT and PART) to retrieve the end-of-period rate and the period-average rate for the matching conversion date, conversion type, and currency pair.
• GL_ROW_MULTIPLIERS — drives the ACTUAL_FLAG derivation, producing 'A' for multiplier 1 (actual) and 'B' for multiplier 2 (budget).

The join is anchored on ledger and period, selecting the daily rate whose CONVERSION_DATE equals the period end date, and matching the conversion type to the ledger's configured rate type. This mirrors the rate selection logic used by the translation program.

Key Columns

• SET_OF_BOOKS_ID — the ledger identifier; retained as SET_OF_BOOKS_ID for backward compatibility, though it maps to LEDGER_ID.
• PERIOD_NAME — the accounting period for which the rates apply.
• TO_CURRENCY_CODE — the target (translation) currency.
• ACTUAL_FLAG — 'A' for actual rates or 'B' for budget rates, derived via DECODE on the row multiplier.
• AVG_RATE / EOP_RATE — the period-average and end-of-period conversion rates.
• AVG_RATE_NUMERATOR, EOP_RATE_NUMERATOR, AVG_RATE_DENOMINATOR, EOP_RATE_DENOMINATOR — numerator and denominator components, both denominators fixed at 1.
• UPDATE_FLAG — computed as 'N', indicating the view is not updateable.
• Standard WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN are all returned as NULL, confirming the view's read-only, non-audited nature.
• ATTRIBUTE1–ATTRIBUTE5 and CONTEXT — descriptive flexfield placeholders, returned as NULL.

Common Use Cases and Queries

Typical uses include verifying which rates GL selected for a period, reconciling translated balances, and feeding rate data into external consolidation or reporting tools.

A representative query retrieves end-of-period and average rates for a ledger and period:

SELECT period_name, to_currency_code, actual_flag,
       avg_rate, eop_rate
FROM   apps.gl_translation_rates
WHERE  set_of_books_id = :ledger_id
AND    period_name     = :period_name;

To compare actual versus budget rates for a single currency, filter on ACTUAL_FLAG IN ('A','B') and TO_CURRENCY_CODE. Because all WHO columns are NULL and UPDATE_FLAG is 'N', the view must never be treated as a maintenance interface; any adjustment to conversion rates must be performed against GL_DAILY_RATES directly and through supported functionality.