Results for “ams_ds_tca_entity_cols_vl”

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

Overview

The AMS_DS_TCA_ENTITY_COLS_VL view is a marketing module (AMS) database object owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It presents translated, language-specific metadata describing the columns that belong to TCA (Trading Community Architecture) entities used by Oracle Marketing's data synchronization framework. In practical terms, the view exposes the definition of each entity column—its identifier, its parent entity, its datatype, size, structural record type, source category, and enabled status—alongside a user-facing description resolved for the session's current language.

The "_VL" suffix denotes a "view with language," meaning the underlying translation table is joined against the base table filtered by USERENV('LANG') so that only rows matching the runtime language are returned. This pattern is standard for Oracle EBS descriptive and reference data, and it allows reporting tools, concurrent programs, and integration logic to consume human-readable descriptions without being aware of the underlying multilingual storage model. Within AMS, this view supports the metadata-driven mapping between TCA entities and the marketing data synchronization structures that depend on them.

Because it is a reporting-oriented view rather than a transactional table, it is typically queried for configuration validation, data migration analysis, and integration diagnostics rather than for high-volume transaction processing.

Underlying Base Objects

Per the documented ETRM metadata, the view is defined over two referenced base objects, both accessed through APPS synonyms:

• AMS_DS_TCA_ENTITY_COLS — the base (non-translated) table holding the physical column metadata, including datatype, field size, record structure, source category, and enabled flag.
• AMS_DS_TCA_ENTITY_COLS_TL — the translation table supplying the language-specific DESCRIPTION value.

The view text confirms the join condition B.ENTITY_COLUMN_ID = T.ENTITY_COLUMN_ID combined with T.LANGUAGE = USERENV('LANG'). All columns except DESCRIPTION originate from the base table B; only DESCRIPTION is drawn from the translation table T. The view also exposes B.ROWID as ROW_ID, and carries the standard EBS WHO columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) as well as SECURITY_GROUP_ID.

Key Columns

• ENTITY_COLUMN_ID — the primary key and the column the user searched for; uniquely identifies each entity column definition and is the join key between the base and translation tables.
• ENTITY_ID — identifies the parent TCA entity to which the column belongs.
• ROW_ID — the ROWID of the underlying base-table row, useful for direct row addressing.
• DATATYPE — the data type of the entity column.
• FIELD_SIZE — the defined size or length of the column.
• RECORD_STUCTURE — the record structure classification (note the documented spelling).
• SOURCE_CATEGORY — the category from which the column originates.
• ENABLED_FLAG — indicates whether the column definition is active.
• DESCRIPTION — the language-specific description from the translation table.

Common Use Cases and Queries

Typical scenarios include verifying which columns are defined and enabled for a given TCA entity, auditing datatype and size metadata before an integration or migration, and retrieving localized descriptions for display or documentation.

Retrieve all columns for a specific entity:

SELECT ENTITY_COLUMN_ID, ENTITY_ID, DATATYPE, FIELD_SIZE, DESCRIPTION
FROM   APPS.AMS_DS_TCA_ENTITY_COLS_VL
WHERE  ENTITY_ID = :p_entity_id
AND    ENABLED_FLAG = 'Y';

Look up a single column by the searched identifier:

SELECT ENTITY_COLUMN_ID, ENTITY_ID, SOURCE_CATEGORY, RECORD_STUCTURE, DESCRIPTION
FROM   APPS.AMS_DS_TCA_ENTITY_COLS_VL
WHERE  ENTITY_COLUMN_ID = :p_entity_column_id;

Because the view already filters by USERENV('LANG'), queries automatically return descriptions in the session language, making it suitable for concurrent programs, BI Publisher reports, and diagnostic scripts that must present configuration metadata in a user's locale.