Search Results get_rate_denominator_sql

Overview

APPS.GL_CURRENCY_API is the central PL/SQL currency services package in Oracle E-Business Suite, shipped in Release 12.1.1 and 12.2.2 with the same public interface. Its header dates to 1997 (created by W Wong, last revised at version 120.5 in 2005), confirming that the API has remained interface-stable across the R12 family. The package exposes reusable, server-side currency logic for Oracle General Ledger and for any other product or custom component that must reason about exchange rates without duplicating rate-resolution rules.

Functionally, the package addresses six documented concerns: determining whether a fixed conversion rate exists between two currencies; returning the relationship (derivation direction and triangulation path) between two currencies; returning the currency code that represents the EURO; retrieving a daily rate for a given currency pair, conversion type, and date; retrieving the closest available daily rate within a caller-specified rollback window; and converting an amount between currencies. It is classified in the ETRM repository as an API and is referenced by 251 other packages, which makes it one of the most widely depended-upon currency utilities in the applications schema.

Key Procedures and Functions

The ETRM metadata documents thirty-three public subprograms. They fall into recognizable families.

• Rate availability and structure: IS_FIXED_RATE returns 'Y' or 'N' to indicate a fixed relationship between two currencies for an effective date. GET_RELATION returns the relationship between two currencies. RATE_EXISTS tests for the presence of a rate for a given currency pair, conversion type, and date. GET_DERIVE_TYPE returns the derivation type in effect for a currency during a period.
• Currency identification: GET_EURO_CODE returns the currency code used for the EURO.
• Direct rate retrieval: GET_RATE returns the daily rate for two currencies, a conversion type, and a date. GET_RATE_SQL (the object referenced in the user's search) returns the same rate information as a SQL fragment, allowing callers to embed rate resolution inside a query rather than a PL/SQL call. GET_CLOSEST_RATE and GET_CLOSEST_RATE_SQL perform the equivalent operations but roll back to the nearest available rate within a caller-supplied maximum number of days.
• Numerator and denominator decomposition: GET_RATE_NUMERATOR_SQL, GET_RATE_DENOMINATOR_SQL, GET_CLOSEST_RATE_NUMERATOR_SQL, and GET_CLOSEST_RATE_DENOM_SQL expose the individual components of a rate expression, which is required when the rate is derived by triangulation through a third currency.
• Triangulation: GET_TRIANGULATION_RATE and GET_CLOSEST_TRIANGULATION_RATE return rates computed through an intermediate currency, the standard mechanism when no direct rate exists.
• Amount conversion: CONVERT_AMOUNT and CONVERT_AMOUNT_SQL convert a monetary amount using a rate for a specified date and conversion type, while CONVERT_CLOSEST_AMOUNT and CONVERT_CLOSEST_AMOUNT_SQL perform the conversion using the nearest available rate.

Three user-defined exceptions are declared: INVALID_CURRENCY (one of the two currencies is not valid), NO_RATE (no rate exists for the date and conversion type), and NO_DERIVE_TYPE (no derivation type found for the currency in the period). Callers are expected to handle these explicitly rather than rely on generic error trapping.

Tables Accessed

• FND_CURRENCIES — validates currency codes and supplies currency attributes such as precision, enabling INVALID_CURRENCY detection and EURO identification.
• GL_DAILY_RATES — the primary rate repository; source for GET_RATE, GET_CLOSEST_RATE, and the SQL-variant and triangulation functions.
• GL_FIXED_CONV_RATES — supplies fixed-rate definitions used by IS_FIXED_RATE and fixed-rate resolution paths.
• GL_LEDGERS — provides ledger context, including functional currency, needed when deriving or triangulating rates.
• GL_PERIOD_STATUSES — used by GET_DERIVE_TYPE and rate lookup logic to determine which period's derivation rules apply for a given date.

Usage Notes

GL_CURRENCY_API is normally invoked from PL/SQL rather than directly by end users. Typical callers include General Ledger forms and concurrent programs, subledger currency conversion routines, and the many product packages counted among its 251 references. Custom code that must reproduce EBS currency behavior should call this API instead of querying GL_DAILY_RATES directly, because the package encapsulates derivation types, fixed rates, triangulation, and rollback-window logic that are difficult to replicate correctly.

The SQL-returning variants (GET_RATE_SQL being the most searched) are the appropriate choice when rate resolution must be embedded in a larger SELECT statement, for example to convert a set of foreign-currency balances in one query. Callers should always provide an effective date and conversion type, and should explicitly handle INVALID_CURRENCY, NO_RATE, and NO_DERIVE_TYPE to avoid uncontrolled failures in batch processing.