Results for “rate_source_code”

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

Overview

GL_DAILY_RATES is the Oracle General Ledger table that stores daily conversion rates used to translate foreign currency transactions into a functional or reporting currency within Oracle E-Business Suite 12.1.1 and 12.2.2. It resides in the GL schema and is owned by the General Ledger product. Every foreign currency journal entry, revaluation, translation, and subledger posting that requires a rate on a specific date ultimately resolves that rate through this table, making it one of the most heavily accessed reference tables in the financials data model.

The table supports multiple conversion rate types concurrently, allowing a single currency pair to carry independent rate streams — for example a corporate spot rate, a corporate average rate, and a user-defined daily rate — each keyed by date. Because rates are stored at day granularity rather than per transaction, GL_DAILY_RATES functions as a shared rate repository that other modules read rather than duplicate.

Under the heuristic Data Vault classification derived from its foreign key structure, this object is modeled as a link. It connects two independent business entities — the from-currency and the to-currency — through a dated, typed relationship, which is precisely the role of a link table in a Data Vault model. Treating it as a link rather than a satellite reflects that its grain is defined by the combination of the two currencies, the conversion date, and the conversion type.

Key Information Stored

GL_DAILY_RATES contains 28 documented columns. The most significant are the key components and the rate value itself.

• FROM_CURRENCY — the source currency of the conversion pair; part of the primary key and a foreign key to FND_CURRENCIES.
• TO_CURRENCY — the target currency of the conversion pair; also part of the primary key and a foreign key to FND_CURRENCIES.
• CONVERSION_DATE — the effective date on which the rate applies; part of the primary key.
• CONVERSION_TYPE — the named rate stream (for example a corporate or user-defined type); part of the primary key and a foreign key to GL_DAILY_CONVERSION_TYPES.
• CONVERSION_RATE — the actual multiplier used to convert from the from-currency into the to-currency.
• STATUS_CODE — indicates whether the rate row is active or disabled; disabled rows are excluded from rate resolution.
• RATE_SOURCE_CODE — identifies the origin of the rate, distinguishing rates entered manually from those loaded or derived.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns recording insert and update provenance.
• CONTEXT and ATTRIBUTE1 through ATTRIBUTE15 — the standard descriptive flexfield columns available for client-specific extensions.

The primary key is GL_DAILY_RATES_PK, defined over FROM_CURRENCY, TO_CURRENCY, CONVERSION_DATE, and CONVERSION_TYPE. A unique index, GL_DAILY_RATES_U1, covers the identical four columns. Because the primary key is a composite business key rather than a system-generated surrogate, the table does not carry a separate single-column surrogate identifier; the business key and the unique index are effectively the same set of columns, guaranteeing one rate per currency pair, per date, per conversion type.

Common Use Cases and Queries

The most frequent operational requirement is retrieving the rate applicable to a transaction date for a given currency pair and conversion type. A typical query filters on all four key columns and restricts to active rows:

• Rate lookup for a posting date: SELECT conversion_rate FROM gl_daily_rates WHERE from_currency = :from AND to_currency = :to AND conversion_type = :type AND conversion_date <= :txn_date AND status_code = 'C' ORDER BY conversion_date DESC — the descending order retrieves the most recent rate on or before the transaction date.
• Rate maintenance review: listing all rates for a currency pair across a date range to verify coverage and detect gaps before a period close.
• Audit and provenance reporting: grouping by RATE_SOURCE_CODE to quantify manually entered versus system-loaded rates.
• Missing-rate detection: outer-joining expected currency pairs and dates against GL_DAILY_RATES to identify dates with no active rate, a common cause of posting failures.
• Historical trending: charting CONVERSION_RATE over CONVERSION_DATE for a currency pair to support revaluation and translation analysis.

Related Objects

The table participates in three documented foreign key relationships and is referenced widely by General Ledger and subledger components.

• FND_CURRENCIES — referenced twice, via GL_DAILY_RATES.FROM_CURRENCY and GL_DAILY_RATES.TO_CURRENCY; supplies currency names and precision.
• GL_DAILY_CONVERSION_TYPES — referenced via GL_DAILY_RATES.CONVERSION_TYPE; defines the valid rate streams.
• GL_DAILY_RATES_PK / GL_DAILY_RATES_U1 — the primary key constraint and its corresponding unique index.
• GL_JE_HEADERS and GL_JE_LINES — journals that consume rates at posting time to derive converted amounts.
• GL_CURRENCY_API — the public API used to insert and maintain daily rates programmatically rather than by direct DML.