Search Results ams_column_rules_u1

Overview

The AMS.AMS_COLUMN_RULES table is a seed data object within the Oracle E-Business Suite Marketing (AMS) schema. It stores configuration metadata that governs how individual columns are rendered, validated, and presented across Oracle Marketing and Oracle CRM application pages. The table is classified as belonging to the APPS_TS_SEED tablespace, confirming its role as a reference/seed data repository rather than a transactional table. In Oracle EBS 12.1.1 and 12.2.2, this object supports the declarative column-level rule engine used by the AMS module and its dependent CRM components.

Under a heuristic Data Vault classification, the table behaves as a satellite — it carries descriptive, context-dependent attributes (rule type, status, metadata identifiers) that qualify a business key rather than acting as an independent hub or a pure linking table. It should be modeled as a satellite keyed off the column rule identifier, with its descriptive payload changing over time.

Key Information Stored

The table contains 22 documented columns. The most significant include:

• COLUMN_RULE_ID — surrogate primary key component and the column referenced by the unique index AMS_COLUMN_RULES_U1 (paired with ZD_EDITION_NAME).
• OBJECT_TYPE and OBJECT_ATTRIBUTE — identify the business object and attribute the rule applies to; both are indexed (N1, N2) and form part of the composite business key ACRS_UK.
• ACTIVITY_TYPE_CODE and RULE_TYPE — qualify the rule's behavioral context and are also components of ACRS_UK.
• HTML_FORM_PARAM and APPLICATION_ID — further complete the composite business key and bind the rule to a specific form and application.
• SYSTEM_STATUS_CODE — indexed (N3) and included in the business key, controlling rule activation.
• AK_ATTRIBUTE_CODE and AK_REGION_CODE — link the rule to Oracle Application Framework (OAF) regions and attributes; AK_ATTRIBUTE_CODE is indexed (N4).
• DB_TABLE_NAME and DB_COLUMN_NAME — map the rule to its underlying physical column.
• COLUMNS_METADATA_ID — references the parent column metadata definition.
• SEEDED_FLAG — distinguishes Oracle-seeded rules from customer-created ones.
• ZD_EDITION_NAME — the editioning column that supports EBS Online Patching in 12.2.2, enabling edition-based redefinition of seed data.

The surrogate key is COLUMN_RULE_ID; the documented business-key candidate is the unique index AMS_COLUMN_RULES_U1 on (COLUMN_RULE_ID, ZD_EDITION_NAME), while ACRS_UK serves as the composite primary key spanning seven columns.

Common Use Cases and Queries

Typical scenarios include verifying seeded column rules after patching, troubleshooting rendering anomalies on AMS CRM pages, and auditing which object attributes carry custom rules. A representative query retrieving active rules for a given object type:

SELECT COLUMN_RULE_ID, OBJECT_ATTRIBUTE, DB_COLUMN_NAME, RULE_TYPE, SYSTEM_STATUS_CODE FROM AMS.AMS_COLUMN_RULES WHERE OBJECT_TYPE = :p_object_type AND SYSTEM_STATUS_CODE = 'ACTIVE';

To isolate customer-defined (non-seeded) rows:

SELECT * FROM AMS.AMS_COLUMN_RULES WHERE SEEDED_FLAG = 'N';

Reporting use cases include joining to FND_SECURITY_GROUPS to validate security group scoping, and joining to ECE_COLUMN_RULES via COLUMN_RULE_ID to reconcile AMS rules with their ECE counterparts.

Related Objects

The following objects reference or depend on AMS_COLUMN_RULES:

• ECE_COLUMN_RULES — referenced through AMS_COLUMN_RULES.COLUMN_RULE_ID; the upstream rule definition structure.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID; scopes rules by security group.
• FND_APPLICATION — implicitly related through APPLICATION_ID.
• AMS_COLUMNS_METADATA — parent metadata referenced through COLUMNS_METADATA_ID.
• AK_REGIONS / AK_ATTRIBUTES — OAF region and attribute definitions mapped via AK_REGION_CODE and AK_ATTRIBUTE_CODE.
• FND_OBJECTS / JTF_OBJECTS — object type definitions consistent with OBJECT_TYPE.

Oracle supports access to this object only through standard Oracle Applications programs; direct DML should be avoided in production environments.