Search Results xtr_spot_rates




Overview
Explore in AI Chat

Overview

XTR_SPOT_RATES is a Treasury (XTR) product table within Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the historical record of foreign exchange spot rates. It functions as the reference repository for bid, offer, and USD-based conversion rates keyed by currency and rate date, and is consumed by Treasury dealing, exposure management, hedge accounting, and revaluation processes that require a dated FX rate for a given currency pair.

Under the heuristic Data Vault classification mined from its foreign key structure, XTR_SPOT_RATES is best modeled as a link. Its composite primary key combines a currency reference and a rate date, and its foreign keys point outward to XTR_MASTER_CURRENCIES and XTR_MARKET_PRICES, indicating that the table records associations between a currency dimension and a market price period rather than describing a single business entity on its own.

Key Information Stored

The documented physical schema exposes twelve columns. The most operationally significant are:

  • CURRENCY — the currency being quoted; part of the primary key and a foreign key to XTR_MASTER_CURRENCIES.
  • RATE_DATE — the effective date of the quoted rate; the second component of the primary key.
  • UNIQUE_PERIOD_ID — the market price period identifier and foreign key to XTR_MARKET_PRICES.
  • BID_RATE_AGAINST_USD and OFFER_RATE_AGAINST_USD — the bid and offer quotes expressed against the US dollar.
  • USD_BASE_CURR_BID_RATE and USD_BASE_CURR_OFFER_RATE — the bid and offer rates expressed with the USD as the base currency.
  • HCE_RATE — the rate used for high-level or consolidated currency conversion.
  • SPREAD_AGAINST_USD — the quoted spread against the USD rate.
  • SEQ_NO — a sequencing column supporting ordered storage of rate records.
  • ARCHIVE_DATE and ARCHIVE_BY — audit columns recording when and by whom a rate row was archived.

The surrogate primary key is XTR_SPOT_RATES_PK, defined on (CURRENCY, RATE_DATE). A second unique index, XTR_SPOT_RATES_U1, is defined on (RATE_DATE, CURRENCY), which is a business-key candidate enforcing the same uniqueness with reversed column order. Both constraints guarantee that only one spot rate record exists per currency per date.

Common Use Cases and Queries

Typical usage retrieves the applicable rate for a currency on or before a transaction date, supports period-end revaluation, and feeds Treasury reporting on rate movements.

  • Rate lookup for a single currency and date: SELECT bid_rate_against_usd, offer_rate_against_usd, hce_rate FROM xtr.xtr_spot_rates WHERE currency = :p_currency AND rate_date = :p_date;
  • Most recent rate on or before a date: SELECT * FROM (SELECT * FROM xtr.xtr_spot_rates WHERE currency = :p_currency AND rate_date <= :p_date ORDER BY rate_date DESC) WHERE ROWNUM = 1;
  • Rate trend reporting: select currency, rate_date, and the bid/offer columns ordered by rate_date to chart historical movement for a currency.
  • Market price reconciliation: join on UNIQUE_PERIOD_ID to XTR_MARKET_PRICES to validate that spot rates align with the associated market price records.
  • Archive auditing: filter on ARCHIVE_DATE and ARCHIVE_BY to identify rates retired from active use and the user who archived them.

Related Objects

The FK and PK relationship data identifies the following significant related objects:

  • XTR_MASTER_CURRENCIES — joined via XTR_SPOT_RATES.CURRENCY = XTR_MASTER_CURRENCIES.CURRENCY; supplies the currency definition.
  • XTR_MARKET_PRICES — joined via XTR_SPOT_RATES.UNIQUE_PERIOD_ID = XTR_MARKET_PRICES.UNIQUE_PERIOD_ID; groups spot quotes into market price periods.