Search Results ams_columns_metadata_u1

Overview

AMS.AMS_COLUMNS_METADATA is a repository table within the Oracle Applications schema (AMS) that stores descriptive metadata used to render and manage setup-driven columns and searchable attributes for application objects. It is a seed-data object physically housed in the APPS_TS_SEED tablespace, indicating that its rows are largely shipped by Oracle and treated as reference data rather than transactional content. The table carries the standard Oracle EBS "Oracle Internal Use Only" restriction, and it is supported for access only through standard Oracle Applications programs, not through custom SQL against the base table.

From a Data Vault modeling perspective, the ETRM metadata classifies this object as standalone, meaning it exhibits no parent-child link or satellite dependency structure within its own FK network. As a modeling suggestion, it functions effectively as a reference or hub-like reference table: its surrogate identifier uniquely distinguishes each metadata row, while the combination of OBJECT_ATTRIBUTE, OBJECT_TYPE, ACTIVITY_TYPE_CODE, HTML_FORM_PARAM, and APPLICATION_ID behaves as the natural business key via the ACMD_UK unique constraint. The ZD_EDITION_NAME column signals edition-based redefinition support, aligning the object with the 12.2.x online patching architecture.

Key Information Stored

The documented schema contains 44 columns. The most operationally significant are:

• COLUMNS_METADATA_ID — the surrogate primary key and the leading column of the unique index AMS_COLUMNS_METADATA_U1 (paired with ZD_EDITION_NAME).
• OBJECT_TYPE, OBJECT_ATTRIBUTE, ACTIVITY_TYPE_CODE, HTML_FORM_PARAM, APPLICATION_ID — the documented business-key columns composing the ACMD_UK unique constraint; together they identify which object, attribute, activity, and form parameter the metadata describes.
• AK_REGION_CODE, AK_ATTRIBUTE_CODE — oracle Application Framework (OAF/AK) region and attribute references used for UI rendering.
• DB_TABLE_NAME, DB_COLUMN_NAME — the underlying database table and column that back the rendered attribute.
• SEARCH_TYPE, SEARCH_QUERY, SEARCH_PARAM1 through SEARCH_PARAM15 — search behavior configuration and up to fifteen parameter slots querying the backing data.
• SEARCH_DISPLAY_ALLOWED_FLAG, SEARCH_CONDITION_ALLOWED_FLAG, SEARCH_GROUP, SEARCH_EXCLUSION_GROUP, SEARCH_COLSPAN, SEARCH_DISPLAY_SCALE, SEARCH_OPERATOR_EXCLUSION_ID — presentation and filter-control settings.
• OBJECT_RULE_ID — associates the metadata row with an object rule definition.
• SEEDED_FLAG, SECURITY_GROUP_ID, OBJECT_VERSION_NUMBER — seed indicator, security grouping, and optimistic locking respectively.

Common Use Cases and Queries

Typical scenarios include: auditing which columns Oracle seeds for a given object, reconciling customization against seeded SEEDED_FLAG values, and extracting search configuration for reporting. A representative query by business key is:

SELECT COLUMNS_METADATA_ID, DB_TABLE_NAME, DB_COLUMN_NAME, SEARCH_TYPE
FROM AMS.AMS_COLUMNS_METADATA
WHERE OBJECT_TYPE = :p_object_type
AND OBJECT_ATTRIBUTE = :p_attribute
AND ACTIVITY_TYPE_CODE = :p_activity
AND APPLICATION_ID = :p_app_id;

To trace only seeded rows: ... WHERE SEEDED_FLAG = 'Y'. Because access is restricted to standard Oracle programs, such queries should be executed read-only, preferably through supported views or concurrent programs rather than direct DML.

Related Objects

The documented foreign key ties SECURITY_GROUP_ID to FND_SECURITY_GROUPS, governing which security group owns each metadata row. The OBJECT_RULE_ID column links to object rule definitions in the AMS rule tables, and APPLICATION_ID resolves to FND_APPLICATION for the owning application. OBJECT_TYPE, OBJECT_ATTRIBUTE, and AK_REGION_CODE / AK_ATTRIBUTE_CODE reference the AOL and AK metadata framework used to build Oracle EBS forms and OAF pages. The AMS_COLUMNS_METADATA_U1 unique index enforces the COLUMNS_METADATA_ID / ZD_EDITION_NAME uniqueness, and ZD_EDITION_NAME participates in the editioning model alongside the FND and AD edition metadata. Joins to FND_SECURITY_GROUPS on SECURITY_GROUP_ID, and to FND_APPLICATION on APPLICATION_ID, are the most common reconciliation paths.