Results for “gr_ein_numbers_tl”

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

Overview

GR_EIN_NUMBERS_TL is a translation (TL) table within the GR schema, owned by the Process Manufacturing Regulatory Management module in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores one row for every language-specific description of a European Index Number (EIN). European Index Numbers are regulatory identifiers assigned to chemical substances under the European Inventory of Existing Commercial Chemical Substances (EINECS) framework, and the GR module uses them to support substance compliance, hazard classification, and regulatory reporting for process manufacturing.

The _TL suffix indicates that this table holds translated (multi-language) descriptive text rather than master data. The corresponding base table, GR_EIN_NUMBERS_B, holds the language-independent attributes, while this table holds the LANGUAGE-dependent DESCRIPTION and its source-language origin. The two are linked through the shared composite key EUROPEAN_INDEX_NUMBER.

The heuristic Data Vault classification mined from the foreign-key structure is satellite. In Data Vault 2.0 modeling terms, GR_EIN_NUMBERS_TL would be modeled as a multi-active satellite attached to a hub keyed on EUROPEAN_INDEX_NUMBER, with LANGUAGE acting as the additional qualifying attribute that allows several descriptive rows to exist per hub key. This classification is a modeling suggestion, not a physical constraint of the EBS table itself.

Key Information Stored

The table contains nine documented columns. The most significant are described below; the audit columns follow Oracle's standard WHO convention.

• EUROPEAN_INDEX_NUMBER — The business identifier of the European Index Number. This column forms part of the composite primary key.
• LANGUAGE — The Oracle language code for the description row. Also part of the composite primary key, it enables multiple rows per index number.
• DESCRIPTION — The translated textual description of the European Index Number for the given language. This is the principal payload of the table.
• SOURCE_LANG — The language from which the row was derived or translated, supporting the Oracle translation mechanism.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard EBS audit columns recording row creation and last modification.

The surrogate distinction here is important: the documented primary key GR_EIN_NUMBERS_TL_PK is composite, comprising (EUROPEAN_INDEX_NUMBER, LANGUAGE). There is no separate single-column surrogate key. The composite key also serves as the unique business-key candidate, since it is the only unique index listed. Consequently, no explicit surrogate-versus-business-key split exists in the physical schema; the composite identifier performs both roles.

Common Use Cases and Queries

Typical use cases involve joining the translation table to the base index-number table to retrieve a language-appropriate description, or querying directly for multilingual reporting.

• Retrieving the description for a specific index number in a chosen language:

SELECT ein.european_index_number, ein.description
FROM   gr.gr_ein_numbers_tl ein
WHERE  ein.european_index_number = :ein_number
AND    ein.language = USERENV('LANG');

• Listing all available translations for an index number:

SELECT language, description, source_lang
FROM   gr.gr_ein_numbers_tl
WHERE  european_index_number = :ein_number
ORDER BY language;

• Regulatory reporting extracts that combine the base row with its translated descriptions to produce multilingual substance dossiers.
• Data-quality audits verifying that every EIN in the base table has at least one translation row for each active language.
• Migration or data-load validation confirming the composite key GR_EIN_NUMBERS_TL_PK is unique across source extracts.

Related Objects

As a translation table, GR_EIN_NUMBERS_TL is tightly coupled to the base EIN table and to the GR regulatory data model. The most significant related objects include:

• GR_EIN_NUMBERS_B — The base (non-translated) table holding language-independent EIN attributes. Joined on EUROPEAN_INDEX_NUMBER.
• GR_EIN_NUMBERS_VL — The standard EBS view layered over the base and translation tables, resolving the current-language DESCRIPTION. Joined on EUROPEAN_INDEX_NUMBER and LANGUAGE.
• GR_EIN_NUMBERS_TL (self) — The composite key GR_EIN_NUMBERS_TL_PK enforces uniqueness across EUROPEAN_INDEX_NUMBER and LANGUAGE.
• Other GR regulatory reference tables keyed on European Index Number that consume the description for display and reporting, joined through EUROPEAN_INDEX_NUMBER.

No foreign-key relationships to other tables were documented in the metadata, consistent with the standalone Data Vault classification. Join logic is therefore expressed through the shared business columns rather than enforced referential constraints.