Results for “pa_projects_erp_ext_tl_u1”

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

Overview

PA.PA_PROJECTS_ERP_EXT_TL is a translation (TL) table in the Oracle E-Business Suite Projects (PA) schema. It stores language-specific, translated values for user-defined extensible attributes attached to projects and project elements (tasks). In Oracle EBS, extensible attribute frameworks allow customers to declare additional descriptive fields without altering the base project schema; the transactional values are held in PA_PROJECTS_ERP_EXT_B, while their translated counterparts are persisted here, one row per extension and language combination.

Each row carries the translation of the descriptive attribute text for a given EXTENSION_ID and LANGUAGE. Because Oracle EBS stores user-entered multi-language text in separate TL tables, source language content is captured on the base table and the translated values are captured here, with SOURCE_LANG indicating the originating language and LANGUAGE the language of the present row.

Under a Data Vault modeling heuristic, this table classifies as a link object. It resolves the many-to-many intersection between the extension business entity (identified by EXTENSION_ID) and the language dimension supplied by FND_LANGUAGES, while also keying back to project and project element. It is not a pure hub or satellite because its identity is composite and derived from the combination of extension and language.

Key Information Stored

The most significant columns are:

• EXTENSION_ID — System-generated number uniquely identifying the extension row; part of the composite primary key.
• LANGUAGE — The language of the translated attribute values; the second component of the composite primary key.
• PROJECT_ID — Identifier of the project whose extensible attribute values are stored.
• PROJ_ELEMENT_ID — Identifier of the project element (task) for which the values apply.
• ATTR_GROUP_ID — Identifier of the attribute group that organizes the extension attributes.
• SOURCE_LANG — The originating language from which the translation was made.
• TL_EXT_ATTR1 through TL_EXT_ATTR40 — Character-based translated extensible attribute columns (VARCHAR2(1000)).
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns.

The surrogate primary key is PA_PROJECTS_ERP_EXT_TL_PK over (EXTENSION_ID, LANGUAGE). The unique index PA_PROJECTS_ERP_EXT_TL_U1 on the same column pair reinforces this as the business-key candidate. A separate non-unique index, PA_PROJECTS_ERP_EXT_TL_N1, covers (PROJECT_ID, LANGUAGE, PROJ_ELEMENT_ID) and supports project-centric access paths. Both indexes reside in the APPS_TS_TX_IDX tablespace.

Common Use Cases and Queries

Typical usage includes multi-language project reporting, translation maintenance, and extraction of user-defined project attributes for downstream interfaces or data warehouse loads. A common pattern retrieves the translated attributes for a project in a specific language:

• SELECT e.extension_id, e.language, e.project_id, e.proj_element_id, e.attr_group_id, e.tl_ext_attr1, e.tl_ext_attr2 FROM pa.pa_projects_erp_ext_tl e WHERE e.project_id = :project_id AND e.language = USERENV('LANG');
• Joining to the base extension table on EXTENSION_ID to reconcile source-language values with translations, filtering on SOURCE_LANG to detect missing or stale translations.
• Auditing translation coverage by grouping on LANGUAGE and counting rows per project or attribute group.
• Extracting all attribute groups defined for a project element when building project performance reports that expose customer-defined attributes.

Because the attribute columns are generic (TL_EXT_ATTR1–40), reporting tools typically resolve the semantic name through the attribute group and attribute definitions rather than referencing column numbers directly. The N1 index makes project- and element-scoped queries efficient.

Related Objects

The following objects are the most significant related to PA_PROJECTS_ERP_EXT_TL, based on documented foreign key relationships:

• PA.PA_PROJECTS_ALL — Referenced via PROJECT_ID; the master project record supplying project identity.
• PA.PA_PROJ_ELEMENTS — Referenced via PROJ_ELEMENT_ID; defines project elements/tasks to which attributes apply.
• FND_LANGUAGES — Referenced twice, via SOURCE_LANG and LANGUAGE; the installed language registry.
• PA.PA_PROJECTS_ERP_EXT_B — The base (non-translated) extension table sharing EXTENSION_ID; the natural join partner for translation comparisons.
• PA.PA_PROJECTS_ERP_EXT_TL_U1 and PA_PROJECTS_ERP_EXT_TL_N1 — Unique and non-unique indexes supporting the primary key and project-language access paths.

Together these objects form the project extension framework, with the TL table providing the language-dependent layer over project-level user-defined data.