Search Results ams_column_rules




Overview
Explore in AI Chat

Overview

The AMS_COLUMN_RULES table is a Marketing (AMS) module data object that defines configurable column-level rules applied to marketing objects, attributes, and activities. Residing in the AMS schema, it lets administrators control how specific object attributes are rendered, validated, or constrained when presented through HTML-based forms and region/attribute frameworks. Because the rules are keyed by a combination of object type, object attribute, activity type, HTML form parameter, application, rule type, and system status, the table functions as a metadata-driven configuration layer rather than a transactional store.

In Oracle EBS 12.1.1 and 12.2.2, the table is reported with 22 physical columns, and its structure has been carried forward into the 12.2.2 documented schema under the AMS owner. The heuristic Data Vault classification derived from its foreign-key topology is standalone, suggesting it is best modeled as an independent reference or configuration table rather than a strict hub, link, or satellite. Its two documented relationships—to ECE_COLUMN_RULES via COLUMN_RULE_ID and to FND_SECURITY_GROUPS via SECURITY_GROUP_ID—indicate it participates in both rule-definition and security-group scoping.

Key Information Stored

The surrogate primary key is COLUMN_RULE_ID, exposed through the AMS_COLUMN_RULES_PK constraint. A composite unique index, ACRS_UK, enforces the business key across OBJECT_TYPE, OBJECT_ATTRIBUTE, ACTIVITY_TYPE_CODE, HTML_FORM_PARAM, APPLICATION_ID, RULE_TYPE, and SYSTEM_STATUS_CODE. A second unique index, AMS_COLUMN_RULES_U1, is documented on COLUMN_RULE_ID together with ZD_EDITION_NAME.

  • COLUMN_RULE_ID — surrogate primary key identifying each rule row.
  • OBJECT_TYPE / OBJECT_ATTRIBUTE — the marketing entity and specific attribute the rule governs.
  • ACTIVITY_TYPE_CODE — the activity context in which the rule applies.
  • HTML_FORM_PARAM — the form parameter binding the rule to a UI element.
  • RULE_TYPE — classifies the kind of rule being applied.
  • SYSTEM_STATUS_CODE — lifecycle or activation status of the rule.
  • APPLICATION_ID — owning application context.
  • SECURITY_GROUP_ID — scopes visibility to a security group (FK to FND_SECURITY_GROUPS).
  • DB_TABLE_NAME / DB_COLUMN_NAME — physical table and column targeted by the rule.
  • AK_REGION_CODE / AK_ATTRIBUTE_CODE — region and attribute codes for the attribute framework.
  • COLUMNS_METADATA_ID — linkage to column metadata definitions.
  • SEEDED_FLAG — indicates whether the rule is Oracle-seeded or customer-defined.
  • OBJECT_VERSION_NUMBER — optimistic locking version column.
  • Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical use cases include auditing which rules apply to a given marketing object attribute, inspecting seeded versus custom rule configurations, and joining to security groups to review access scoping. A representative query lists active rules for an object type:

  • SELECT column_rule_id, object_type, object_attribute, rule_type, system_status_code FROM ams.ams_column_rules WHERE object_type = :p_object_type AND rule_type = :p_rule_type;
  • Join to fnd_security_groups on security_group_id to report group-scoped rules.
  • Filter on seeded_flag = 'N' to isolate customer-defined overrides.
  • Use db_table_name/db_column_name to trace rules back to physical columns.

Related Objects

  • ECE_COLUMN_RULES — referenced via COLUMN_RULE_ID, providing the base rule definition.
  • FND_SECURITY_GROUPS — source of SECURITY_GROUP_ID scoping.
  • AMS_COLUMNS_METADATA — linked through COLUMNS_METADATA_ID for column definitions.
  • FND_APPLICATION — resolves APPLICATION_ID.
  • FND_OBJECTS / AK_REGIONS — contextual metadata for OBJECT_TYPE and AK_REGION_CODE.
  • AMS_ACTIVITY_TYPES — supporting ACTIVITY_TYPE_CODE lookups.