Results for “iex_cases_tl”

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

Overview

IEX_CASES_TL is the translation table for IEX_CASES, the base transactional table that stores collection case records within the Oracle E-Business Suite Collections (IEX) module. In a multi-language EBS deployment, Oracle separates language-independent transactional data from language-dependent descriptive text. IEX_CASES holds the case itself—its identifiers, statuses, amounts, and operational attributes—while IEX_CASES_TL holds the translatable portions, most notably the COMMENTS field. The table resides in the IEX schema and is marked VALID in both 12.1.1 and 12.2.2. Under a heuristic Data Vault classification mined from the foreign key structure, IEX_CASES_TL is modeled as a standalone satellite: it carries descriptive context for the IEX_CASES hub and is not itself referenced as a parent by other tables.

Key Information Stored

The table contains 11 documented columns. Among the most significant:

• CAS_ID — The foreign key to IEX_CASES. It identifies which collection case the translated row describes. CAS_ID is also the first component of the composite primary key.
• LANGUAGE — The NLS language code for the row (for example, US or DE). It is the second component of the primary key and determines which linguistic version of the case text is retrieved.
• SOURCE_LANG — Indicates the language in which the source text was originally authored, used by the translation framework to track the baseline language.
• COMMENTS — The primary translatable payload, holding free-form collector notes and case commentary in the row's LANGUAGE.
• ACTIVE_FLAG — Denotes whether the translation row is active and available for display.
• SECURITY_GROUP_ID — References FND_SECURITY_GROUPS, enforcing row-level security for the translation record.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — The standard EBS audit columns recording who created and last modified each row, when, and through which login session.

The surrogate primary key is IEX_CASES_TL_PK, defined on (CAS_ID, LANGUAGE). A unique index IEX_CASES_TL_U1 also exists on (CAS_ID, LANGUAGE), making that pair the business-key candidate that guarantees exactly one translation row per case per language.

Common Use Cases and Queries

Reporting on collections activity frequently requires case commentary in a specific language. Because the base table stores no language-dependent text, reports must join or filter on IEX_CASES_TL. A typical query retrieves case comments for the session language:

• SELECT c.cas_id, t.comments FROM iex_cases c, iex_cases_tl t WHERE c.cas_id = t.cas_id AND t.language = USERENV('LANG');
• Filtering active translations only: add AND t.active_flag = 'Y'.
• Auditing translation coverage: select language, count(*) from iex_cases_tl group by language to determine which languages have been populated.
• Joining to FND_SECURITY_GROUPS via security_group_id to enforce row-level security in custom extracts.

Translation rows are typically maintained through the Collections application's own case-entry and translation forms rather than by direct DML; direct inserts or updates must respect the composite key and audit columns, and must supply a valid LANGUAGE value.

Related Objects

• IEX_CASES — The base table; join on CAS_ID. This is the parent hub for the satellite.
• FND_SECURITY_GROUPS — Referenced by IEX_CASES_TL.SECURITY_GROUP_ID; join on SECURITY_GROUP_ID for row-level security resolution.
• IEX_CASES_TL_PK / IEX_CASES_TL_U1 — The primary key constraint and unique index on (CAS_ID, LANGUAGE) that enforce one translation row per case per language.
• FND_LANGUAGES — Provides the valid language codes against which the LANGUAGE and SOURCE_LANG values are interpreted.
• IEX_CASES_API or equivalent Collections case PL/SQL APIs — Application programmatic entry points that create and maintain cases and their translation rows, and which should be preferred over direct table manipulation.