Search Results ams_ds_tca_entity_cols




Overview
Explore in AI Chat

Overview

AMS_DS_TCA_ENTITY_COLS is a metadata repository table in the AMS (Marketing) product schema of Oracle E-Business Suite. Its documented purpose is to store the column definition for each entity, meaning it acts as a dictionary that describes which data columns belong to a given TCA entity within the AMS data source layer. In practice, this table underpins the source-definition framework used by AMS data synchronization and marketing list-generation processes, allowing the application to resolve entity attributes dynamically at run time rather than through hard-coded references.

Under the heuristic Data Vault classification supplied with the ETRM metadata, this object is categorized as standalone. From a modeling perspective, this suggests the table is best treated as an independent reference or descriptor structure rather than as a hub, link, or satellite. It carries no mined parent/child dependencies other than a security reference, so it can be modeled as a self-contained lookup whose rows describe entity columns rather than transactional events.

Key Information Stored

The table contains fifteen documented columns. The most significant are:

  • ENTITY_COLUMN_ID — the surrogate primary key that uniquely identifies each entity-column definition row.
  • ENTITY_ID — identifies the parent entity to which the column belongs, linking the column definition back to its owning entity.
  • COLUMN_NAME — the physical or logical name of the column being described.
  • DATATYPE — the data type declared for the column, used when validating or generating SQL and target structures.
  • FIELD_SIZE — the declared length or precision of the column.
  • RECORD_STUCTURE — the documented (note the spelling as stored) structure descriptor for the record layout.
  • ENABLED_FLAG — indicates whether the column definition is active and should be considered during processing.
  • SOURCE_CATEGORY — classifies the origin or category of the column within the AMS data source layer.
  • SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing multi-tenant/security-group isolation.
  • ZD_EDITION_NAME — the editioning column used by EBS online patching (introduced in 12.2.x) to isolate editions during upgrade and patching cycles.

The documented unique index AMS_DS_TCA_ENTITY_COLS_U1 is defined on (ENTITY_COLUMN_ID, ZD_EDITION_NAME), confirming the edition-aware nature of the business key. Standard audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — are also present and follow conventional EBS Who-column conventions.

Common Use Cases and Queries

This table is primarily consumed by AMS and TCA integration routines that need to introspect entity structure at run time. Typical reporting and diagnostic scenarios include:

  • Listing all enabled columns for a specific entity:
SELECT column_name, datatype, field_size, source_category
FROM   ams.ams_ds_tca_entity_cols
WHERE  entity_id = :entity_id
AND    enabled_flag = 'Y';
  • Auditing which entity columns are disabled or deprecated.
  • Validating that the physical column set matches the metadata definition during upgrade or patch verification.
  • Querying by security group to isolate configuration per operating unit or tenant.

Because the table includes ZD_EDITION_NAME, queries in a 12.2.2 environment typically filter on the current edition (zd_edition_name = 'ORA$BASE') unless performing cross-edition comparison. In 12.1.1 the editioning column does not exist, so the same query must omit that predicate.

Related Objects

The documented referential relationship is to the FND security framework:

  • FND_SECURITY_GROUPS — joined via AMS_DS_TCA_ENTITY_COLS.SECURITY_GROUP_ID = FND_SECURITY_GROUPS.SECURITY_GROUP_ID.

Beyond the documented FK, AMS_DS_TCA_ENTITY_COLS is closely associated with the parent entity definitions it describes (referenced by ENTITY_ID), and with the broader AMS data source and TCA integration objects that consume column definitions to build queries and staging structures. Reporting often joins it to the entity-level metadata table to produce a complete entity-to-column map across the AMS marketing data model.