Search Results ams_columns_metadata

Overview

AMS_COLUMNS_METADATA is a configuration and metadata repository table owned by the AMS schema within the Oracle E-Business Suite Marketing module. Its documented purpose is to store metadata used for locking and search behavior in Oracle Marketing's extensible attribute framework. In practice, the table records the relationship between an AMS object type, a specific object attribute, and the corresponding activity type, along with the database column binding, HTML form parameters, and the search controls that the application should expose for that attribute. It is one of the foundational tables that drives dynamically rendered search pages and column-locking semantics in Oracle Marketing applications such as campaign management, list management, and one-to-one marketing.

From a heuristic Data Vault modeling perspective, the documented structure is best classified as a standalone object. It does not present the multi-parent foreign-key pattern typical of a link table, nor does it carry the temporal, fully dependent attribute profile of a pure satellite. It behaves most like a reference or metadata hub-like entity that anchors descriptive attributes to a natural business key composed of object type, attribute, activity type, and form parameter.

Key Information Stored

The table contains 44 documented columns in 12.2.2. The most significant are:

COLUMNS_METADATA_ID — the surrogate primary key column; also part of the unique index AMS_COLUMNS_METADATA_U1.
OBJECT_ATTRIBUTE and OBJECT_TYPE — identify the AMS entity and the attribute whose search or locking metadata is described. These form part of the business unique key ACMD_UK.
ACTIVITY_TYPE_CODE — specifies the activity context in which the attribute metadata applies; also part of ACMD_UK.
HTML_FORM_PARAM — the parameter name used when the attribute is rendered on an HTML form; part of ACMD_UK.
APPLICATION_ID — the owning application, completing the ACMD_UK business key.
AK_REGION_CODE and AK_ATTRIBUTE_CODE — the Oracle Application Framework region and attribute identifiers used to map metadata to UI components.
DB_TABLE_NAME and DB_COLUMN_NAME — the physical table and column that back the attribute, enabling dynamic query generation.
SEEDED_FLAG — indicates whether the row is Oracle-seeded or customer-defined.
SEARCH_TYPE, SEARCH_QUERY, and SEARCH_PARAM1..SEARCH_PARAM15 — define how an attribute can be searched, including the query fragment and its bind parameters.
SEARCH_DISPLAY_ALLOWED_FLAG, SEARCH_CONDITION_ALLOWED_FLAG, SEARCH_GROUP, SEARCH_EXCLUSION_GROUP, and SEARCH_COLSPAN — govern presentation and grouping of search fields.
SECURITY_GROUP_ID — links the metadata row to an FND security group for access control.
ZD_EDITION_NAME — the editioning column used by the 12.2 online patching architecture.

Common Use Cases and Queries

Typical uses include diagnosing why an attribute is not searchable or incorrectly displayed, listing the seeded versus custom search metadata for a given object, and auditing which database columns back a searchable attribute.

Query all metadata for a specific object type and attribute to inspect its search definition.
Join to FND_SECURITY_GROUPS on SECURITY_GROUP_ID to determine which responsibility-level security group governs a metadata row.
Filter on SEEDED_FLAG to separate Oracle-delivered configuration from customer extensions.
Report on SEARCH_TYPE and SEARCH_QUERY to troubleshoot dynamic search SQL generation.
Use AK_REGION_CODE and AK_ATTRIBUTE_CODE to correlate metadata with OA Framework page definitions.

Related Objects

The documented foreign-key relationship ties AMS_COLUMNS_METADATA.SECURITY_GROUP_ID to FND_SECURITY_GROUPS, which is the primary external dependency for access control. Because the table is classified as standalone, it has no other documented inbound or outbound foreign keys. In practice, related AMS tables include the object and activity definition tables that supply OBJECT_TYPE and ACTIVITY_TYPE_CODE values, along with AMS attribute metadata tables that share the AK region and attribute mapping columns. The PK constraint AMS_COLUMNS_METADATA_PK and unique index AMS_COLUMNS_METADATA_U1 are the principal integrity mechanisms supporting joins and lookups.

Table: AMS_COLUMNS_METADATA 12.2.2
owner:AMS, object_type:TABLE, fnd_design_data:AMS.AMS_COLUMNS_METADATA, object_name:AMS_COLUMNS_METADATA, status:VALID, product: AMS - Marketing , description: This table is used to store metadata for Locking , implementation_dba_data: AMS.AMS_COLUMNS_METADATA ,
Table: AMS_COLUMNS_METADATA 12.1.1
owner:AMS, object_type:TABLE, fnd_design_data:AMS.AMS_COLUMNS_METADATA, object_name:AMS_COLUMNS_METADATA, status:VALID, product: AMS - Marketing , description: This table is used to store metadata for Locking , implementation_dba_data: AMS.AMS_COLUMNS_METADATA ,
SYNONYM: APPS.AMS_COLUMNS_METADATA 12.2.2
owner:APPS, object_type:SYNONYM, object_name:AMS_COLUMNS_METADATA, status:VALID,
VIEW: AMS.AMS_COLUMNS_METADATA# 12.2.2
owner:AMS, object_type:VIEW, object_name:AMS_COLUMNS_METADATA#, status:VALID,
TRIGGER: APPS.AMS_COLUMNS_METADATA+ 12.2.2
owner:APPS, object_type:TRIGGER, object_name:AMS_COLUMNS_METADATA+, status:VALID,
SYNONYM: APPS.AMS_COLUMNS_METADATA 12.1.1
owner:APPS, object_type:SYNONYM, object_name:AMS_COLUMNS_METADATA, status:VALID,
TRIGGER: APPS.AMS_COLUMNS_METADATA+ 12.2.2
APPS.AMS_COLUMNS_METADATA_PKG SQL Statements 12.2.2
APPS.AMS_COLUMNS_METADATA_PKG SQL Statements 12.1.1
VIEW: AMS.AMS_COLUMNS_METADATA# 12.2.2
FUNCTION: APPS.AMS_COLUMNS_METADATA= 12.2.2
FUNCTION: APPS.AMS_COLUMNS_METADATA= 12.2.2
owner:APPS, object_type:FUNCTION, object_name:AMS_COLUMNS_METADATA=, status:VALID,
TABLE: AMS.AMS_COLUMNS_METADATA 12.2.2
owner:AMS, object_type:TABLE, fnd_design_data:AMS.AMS_COLUMNS_METADATA, object_name:AMS_COLUMNS_METADATA, status:VALID,
PACKAGE BODY: APPS.AMS_COLUMNS_METADATA_PKG 12.2.2
owner:APPS, object_type:PACKAGE BODY, object_name:AMS_COLUMNS_METADATA_PKG, status:VALID,
TABLE: AMS.AMS_COLUMNS_METADATA 12.1.1
owner:AMS, object_type:TABLE, fnd_design_data:AMS.AMS_COLUMNS_METADATA, object_name:AMS_COLUMNS_METADATA, status:VALID,
PACKAGE BODY: APPS.AMS_COLUMNS_METADATA_PKG 12.1.1
owner:APPS, object_type:PACKAGE BODY, object_name:AMS_COLUMNS_METADATA_PKG, status:VALID,
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
PACKAGE BODY: APPS.AMS_COLUMNS_METADATA_PKG 12.1.1
PACKAGE BODY: APPS.AMS_COLUMNS_METADATA_PKG 12.2.2
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
12.1.1 DBA Data 12.1.1
12.1.1 DBA Data 12.1.1
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
12.2.2 FND Design Data 12.2.2
12.1.1 FND Design Data 12.1.1
12.2.2 DBA Data 12.2.2
12.1.1 DBA Data 12.1.1
APPS.AMS_COLUMNS_METADATA_PKG dependencies on AMS_COLUMNS_METADATA 12.2.2
APPS.AMS_COLUMNS_METADATA_PKG dependencies on AMS_COLUMNS_METADATA 12.1.1
APPS.AMS_COLUMNS_METADATA_PKG dependencies on AMS_COLUMNS_METADATA_S 12.2.2
APPS.AMS_COLUMNS_METADATA_PKG dependencies on AMS_COLUMNS_METADATA_S 12.1.1
SYNONYM: PUBLIC.DATABASE_PROPERTIES 12.2.2
owner:PUBLIC, object_type:SYNONYM, object_name:DATABASE_PROPERTIES, status:VALID,
12.2.2 DBA Data 12.2.2
12.2.2 DBA Data 12.2.2
PACKAGE: SYS.DBMS_STANDARD 12.2.2
owner:SYS, object_type:PACKAGE, object_name:DBMS_STANDARD, status:VALID,
eTRM - AMS Tables and Views 12.2.2
description: This table is used to store tracking data for web advertisement and offer type schedules ,
eTRM - AMS Tables and Views 12.1.1
description: This table is used to store tracking data for web advertisement and offer type schedules ,
12.2.2 DBA Data 12.2.2
12.1.1 DBA Data 12.1.1
eTRM - AMS Tables and Views 12.2.2
description: This table is used to store tracking data for web advertisement and offer type schedules ,
eTRM - AMS Tables and Views 12.1.1
description: This table is used to store tracking data for web advertisement and offer type schedules ,