Search Results gl_translation_statuses_u1

Overview

The GL.GL_TRANSLATION_STATUSES table is a General Ledger control table within the Oracle E-Business Suite 12.1.1 and 12.2.2 data model. It stores the translation state of balances for each combination of ledger, period, balancing segment value, target currency, and balance type. Translation is the process by which functional-currency balances are converted into a reporting currency, and this table is the repository that records whether that conversion has already been performed and is still valid for a given scope. Each physical row represents one translated scope, identified by its ledger, period, balancing segment value, target currency, average translation flag, and (where applicable) target budget version.

From a data-modeling perspective, the heuristics mined from the foreign-key structure classify this object as a link table. It associates independent reference entities — a ledger, a currency, and a budget version — within a time-bound context, while also carrying descriptive state (STATUS, LAST_RUN_DATE) and standard audit columns. This classification is a modeling suggestion rather than a stated attribute of the object itself.

Key Information Stored

The table physically holds 18 documented columns. The surrogate primary key is not a single numeric column but the composite business key defined by the unique index GL_TRANSLATION_STATUSES_U1, which the user searched for. That index enforces uniqueness across the following six columns and is the authoritative business-key candidate for the table:

• LEDGER_ID — the ledger (originally set of books) defining column; foreign key to GL_SETS_OF_BOOKS_11I.
• PERIOD_NAME — the translation period in which the balances were translated.
• BAL_SEG_VALUE — the balancing segment value that scopes the translation, permitting translation status to be tracked below the ledger level.
• TARGET_CURRENCY — the currency into which balances were translated; foreign key to FND_CURRENCIES.
• AVERAGE_TRANSLATION_FLAG — indicates whether standard or average balances were translated.
• TARGET_BUDGET_VERSION_ID — the budget version being translated; foreign key to GL_BUDGET_VERSIONS.

Among the descriptive columns, four are operationally significant. STATUS carries the translation state, with documented valid values of 'C' (translated balances are current) and 'U' (translated balances are not current). ACTUAL_FLAG distinguishes Actual from Budget balance types. LAST_RUN_DATE records the date on which translation was last executed for the row's scope. The remaining documented columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE — are the standard and enhanced Who columns that provide audit and concurrent-program attribution.

Common Use Cases and Queries

The primary operational scenario is determining which translation scopes are stale and must be re-run before reporting. A typical diagnostic query restricts to a ledger and period and returns rows whose status is not current:

• SELECT LEDGER_ID, PERIOD_NAME, BAL_SEG_VALUE, TARGET_CURRENCY FROM GL.GL_TRANSLATION_STATUSES WHERE STATUS = 'U';
• SELECT PERIOD_NAME, TARGET_CURRENCY, MAX(LAST_RUN_DATE) FROM GL.GL_TRANSLATION_STATUSES WHERE LEDGER_ID = :ledger_id GROUP BY PERIOD_NAME, TARGET_CURRENCY;
• SELECT COUNT(*) FROM GL.GL_TRANSLATION_STATUSES WHERE LEDGER_ID = :ledger_id AND PERIOD_NAME = :period AND AVERAGE_TRANSLATION_FLAG = 'N' AND ACTUAL_FLAG = 'A';

Because the unique index GL_TRANSLATION_STATUSES_U1 covers the full business key, lookups against all six key columns are optimally served and can be relied upon to return at most one row. Reporting use cases include translation completeness audits, period-close checklists that verify all balancing segment values carry current translated balances, and reconciliation of budget translation against actual translation by joining on TARGET_BUDGET_VERSION_ID.

Related Objects

The documented foreign keys and the General Ledger translation process link this table to the following objects:

• GL.GL_SETS_OF_BOOKS_11I — joined via LEDGER_ID; supplies the ledger definition and functional currency.
• FND.FND_CURRENCIES — joined via TARGET_CURRENCY; supplies the target currency description and precision.
• GL.GL_BUDGET_VERSIONS — joined via TARGET_BUDGET_VERSION_ID; identifies the budget version translated.
• GL.GL_LEDGERS — the 12.2.x ledger definition from which LEDGER_ID is derived.
• GL.GL_BALANCES and GL.GL_DAILY_BALANCES — the balance stores whose translated amounts correspond to rows marked current in this status table.
• GL.GL_PERIOD_STATUSES — provides the open/closed state of the periods referenced by PERIOD_NAME.
• FND_CONCURRENT_REQUESTS — joined via REQUEST_ID; identifies the translation program run that last updated the row.

Together these relationships allow the translation status of any ledger and period combination to be assessed directly from the General Ledger schema.