Results for “cst_pac_gl_translation_view”

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

The view CST_PAC_GL_TRANSLATION_VIEW is a critical integration object in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 that exposes foreign currency translation rates used by the Bills of Material (BOM) module and the Project Accounting / Costing (PAC) sub-ledger. Its owner is the APPS schema and it is documented as VALID. The view's primary purpose is to provide a unified, query-able representation of translation rates (both historical average and end-of-period) between a set of books' functional currency and foreign currencies, along with a derived display rate and an inverse flag.

Overview

In an Oracle EBS environment, general ledger translation rates are stored in the GL_TRANSLATION_RATES view (backed by the underlying GL tables) and are keyed by set of books, period, and currency. The CST_PAC_GL_TRANSLATION_VIEW abstracts and enriches this data so that other modules—particularly BOM and Costing—can retrieve consistent rates for cost rollups, standard cost updates, and project costing calculations. The view also handles cases where no specific period rate exists, by returning a default rate (1) or by dynamically deriving a rate using the GL_CURRENCY_API for currencies configured with a derivation type. The presence of the RATE_TYPE column (aliased from ML.LOOKUP_CODE) directly addresses the user search term rate_type, which is central to distinguishing average versus end-of-period rates.

Underlying Base Objects

According to the ETRM metadata, the view is defined over the following base objects (all accessed via synonyms or views in the APPS schema):

• GL_TRANSLATION_RATES (view) – source of period-specific translation rates.
• GL_PERIODS (synonym) – provides period year and period number for the translation period.
• GL_SETS_OF_BOOKS (view) – supplies set of books ID, period set name, and functional currency.
• MFG_LOOKUPS (view) – lookup codes for rate type (CST_GL_TRANS_RATE_TYPE).
• FND_LOOKUPS (view) – lookup codes for the inverse flag (YES_NO).
• FND_CURRENCIES (synonym) – foreign currency definitions, including derivation type.
• FND_GLOBAL (package) – not directly referenced in the excerpt but documented as a base object; likely used for session context.
• GL_CURRENCY_API (package) – used in the third UNION branch to retrieve a rate for the current date via GET_RATE_SQL.

The view is a UNION of three distinct queries: (1) period-specific rates from GL_TRANSLATION_RATES, (2) a default row per set of books with NULL period and rate 1, and (3) a derived row per foreign currency with a derivation type, using the current date.

Key Columns

• SET_OF_BOOKS_ID – identifier of the ledger or set of books.
• PERIOD / PERIOD_YEAR / PERIOD_NUM – the accounting period; NULL or 9999 for the default and derived rows.
• CURRENCY_CODE – the foreign (to) currency code for translation.
• RATE_TYPE – lookup code from CST_GL_TRANS_RATE_TYPE (e.g., 1 = average, 2 = end-of-period). This is the column most relevant to the user's search.
• INVERSE – YES/NO flag indicating whether the rate should be inverted.
• DISPLAY_RATE – a truncated, rounded rate for display purposes.
• ACTUAL_RATE – a rounded rate for calculation (20 decimal places).

Common Use Cases and Queries

Typical use cases include cost rollups that require period-average or end-of-period rates, and reporting on available translation rates by ledger and currency. A common query filtering by rate type would be:

SELECT set_of_books_id, period, currency_code, rate_type, display_rate, actual_rate
FROM   cst_pac_gl_translation_view
WHERE  rate_type = '1'  -- average rate
AND    period = 'JAN-24'
ORDER BY currency_code;

Another scenario retrieves the default or derived rate for a currency when no period rate exists:

SELECT set_of_books_id, currency_code, rate_type, actual_rate
FROM   cst_pac_gl_translation_view
WHERE  period IS NULL
AND    currency_code = 'EUR';

The view thus serves as a consolidated reference for translation rates needed by BOM and costing processes, directly supporting the rate_type search through its exposed RATE_TYPE column.