Search Results ams_status_order_rules_pk

Overview

AMS_STATUS_ORDER_RULES is a Marketing (AMS) schema table that stores the permitted status transitions for marketing objects. Its documented purpose is to identify the next available status(es) a user can select from a given current status, for a given system status type. Rather than allowing users to move a record to any arbitrary status, Oracle Marketing consults this table to present a controlled list of valid successor statuses, typically surfaced through a List of Values (LOV) in the application forms.

In terms of Data Vault modeling, the object is heuristically classified as hub-leaning. This classification is a modeling suggestion rather than a documented Oracle property: the table is dominated by a single-column surrogate primary key (STATUS_ORDER_RULE_ID) and carries descriptive attributes and flags that behave like satellite or reference data, while its business identity is defined by the combination of status codes held in a unique key. Analysts building a Data Vault layer over EBS Marketing data may therefore choose to treat AMS_STATUS_ORDER_RULES as a hub with associated reference and descriptive satellites.

The table exists in both Oracle EBS 12.1.1 and 12.2.2. In 12.2.2 the physical schema is documented with 16 columns, and the presence of the ZD_EDITION_NAME column reflects the editioning and Online Patching (ADOP) architecture introduced in Release 12.2, which is not present in the 12.1.1 data model.

Key Information Stored

The surrogate primary key is STATUS_ORDER_RULE_ID, enforced through the AMS_STATUS_ORDER_RULES_PK constraint. The business identity of a rule is captured by the unique index AMS_STATUS_ORDER_RULES_U2, whose columns are CURRENT_STATUS_CODE, SYSTEM_STATUS_TYPE, NEXT_STATUS_CODE, and ZD_EDITION_NAME. A second unique index, AMS_STATUS_ORDER_RULES_U1, covers STATUS_ORDER_RULE_ID and ZD_EDITION_NAME.

The functional columns are:

• CURRENT_STATUS_CODE – the status a record is currently in; the "from" side of the transition rule.
• NEXT_STATUS_CODE – the status that may be selected from the current status; the "to" side of the rule.
• SYSTEM_STATUS_TYPE – the marketing object or status framework to which the rule applies, allowing the same rule table to serve multiple status systems.
• SHOW_IN_LOV_FLAG – controls whether the next status is presented in the status List of Values, enabling rules to exist for validation or backend purposes without appearing in the user interface.
• THEME_APPROVAL_FLAG – indicates whether the transition is relevant to theme approval processing.
• BUDGET_APPROVAL_FLAG – indicates whether the transition is relevant to budget approval processing.
• SECURITY_GROUP_ID – the security group (operating unit style partitioning) to which the rule belongs, referencing FND_SECURITY_GROUPS.
• APPLICATION_ID – the owning application identifier, used for application-level partitioning and registration.
• ZD_EDITION_NAME – the edition identifier used by the 12.2 Online Patching / editioning model.

Standard EBS who-columns are also present: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, and OBJECT_VERSION_NUMBER (used for optimistic locking).

Common Use Cases and Queries

The most common requirement is to retrieve the valid next statuses for a given current status and system status type, typically filtered to those visible in the LOV. A representative query is:

SELECT next_status_code
FROM ams.ams_status_order_rules
WHERE current_status_code = :p_current_status
AND system_status_type = :p_system_status_type
AND show_in_lov_flag = 'Y'
AND NVL(security_group_id, 0) = NVL(:p_security_group_id, 0)
ORDER BY next_status_code;

Reporting and validation scenarios include auditing which transitions are exposed to users versus those retained only for backend validation, reviewing approval-related transitions by querying THEME_APPROVAL_FLAG or BUDGET_APPROVAL_FLAG, and diagnosing why a status does not appear in an LOV when a rule row is missing or has SHOW_IN_LOV_FLAG set to N. Data migration and configuration comparison exercises frequently join this table against AMS_ACT_QA_CHECKS to verify that QA check configuration references valid status rules.

Because the table is small and highly cached, queries are inexpensive; however, when querying in a 12.2 environment, developers must be aware that ZD_EDITION_NAME participates in the unique keys, and edition-aware queries should be used when running under Online Patching.

Related Objects

The following objects are the most significant dependencies for AMS_STATUS_ORDER_RULES:

• AMS_ACT_QA_CHECKS – references this table through AMS_ACT_QA_CHECKS.ACTIVITY_QA_CHECK_FOR_ID; QA check definitions are therefore tied to specific status order rules.
• FND_SECURITY_GROUPS – referenced by AMS_STATUS_ORDER_RULES.SECURITY_GROUP_ID, providing the security group partitioning for each rule.
• FND_APPLICATION – the parent of the APPLICATION_ID column, identifying the owning application.
• AMS_STATUS_ORDER_RULES_PK – the primary key constraint on STATUS_ORDER_RULE_ID.
• AMS_STATUS_ORDER_RULES_U1 and AMS_STATUS_ORDER_RULES_U2 – the unique indexes defining the edition-aware business keys.

Application logic that consumes these rules is embedded in the Oracle Marketing status framework rather than exposed as a standalone public API, so integrations typically read AMS_STATUS_ORDER_RULES directly or through the Marketing status validation routines that enforce the allowed transitions.