Search Results ams_status_order_rules_u2

Overview

AMS.AMS_STATUS_ORDER_RULES is a seed-data reference table in the Oracle Marketing (AMS) schema that defines the permissible status transitions for marketing objects. Its stated purpose in the ETRM documentation is to store "what are the allowed changes that can be done of status," functioning as a state-machine definition consumed by all marketing entities that carry a system status. Each row encodes a single legal edge from a current status code to a next status code within a named status lookup type.

The table governs both user-driven and system-driven transitions. Certain target statuses are withheld from end users and are set only by internal processing; the SHOW_IN_LOV_FLAG column controls whether a given next status appears in a user-facing list of values, while THEME_APPROVAL_FLAG and BUDGET_APPROVAL_FLAG gate transitions on approval prerequisites. Typical documented transitions include New to Submit for Theme Approval, Submit for Theme Approval to Planned or New, Planned to Active or Cancelled, and Active to Archived.

From a modeling perspective, the heuristic Data Vault classification derived from the foreign-key structure is hub-leaning. AMS_STATUS_ORDER_RULES is best treated as a durable reference or rule-set table rather than a transactional fact; the unique business key formed by the status codes makes it a stable anchor for downstream references.

Key Information Stored

• STATUS_ORDER_RULE_ID — Surrogate primary key (NUMBER) uniquely identifying each rule, backed by unique index AMS_STATUS_ORDER_RULES_U1.
• CURRENT_STATUS_CODE — The system status code from which a transition originates.
• NEXT_STATUS_CODE — The system status code to which the transition is permitted.
• SYSTEM_STATUS_TYPE — The lookup type that defines the set of valid system status codes for a given marketing object; together with the two status codes it forms the business-key candidate AMS_STATUS_ORDER_RULES_U2.
• SHOW_IN_LOV_FLAG — Indicates whether the next status is presented to the user for selection; false values denote system-only transitions.
• THEME_APPROVAL_FLAG — Indicates whether theme approval is required before the transition may proceed.
• BUDGET_APPROVAL_FLAG — Indicates whether budget approval is required for the status change.
• SECURITY_GROUP_ID — Security grouping column with a documented foreign key to FND_SECURITY_GROUPS.
• OBJECT_VERSION_NUMBER — Optimistic-locking column used to detect concurrent updates to rule rows.
• CREATED_BY / CREATION_DATE / LAST_UPDATED_BY / LAST_UPDATE_DATE / LAST_UPDATE_LOGIN — Standard WHO audit columns tracking row provenance.
• ZD_EDITION_NAME — Editioning column present in the 12.2.x physical schema; included in both unique indexes to support online patching editions.

Note the distinction between the surrogate key (STATUS_ORDER_RULE_ID) and the composite business key (CURRENT_STATUS_CODE, SYSTEM_STATUS_TYPE, NEXT_STATUS_CODE), which is what application logic and joins should generally rely on.

Common Use Cases and Queries

The most frequent use is resolving the set of valid target statuses for a marketing object given its present state. A representative query for LOV-driven transitions is:

SELECT next_status_code, show_in_lov_flag
FROM   ams.ams_status_order_rules
WHERE  system_status_type  = :p_status_type
AND    current_status_code = :p_current_status
AND    show_in_lov_flag    = 'Y';

Validation logic invokes the same table to confirm that a requested status change is legal, filtering on the current and next codes simultaneously. Reporting scenarios include auditing the complete state model for a marketing object by selecting all rows for a SYSTEM_STATUS_TYPE and rendering them as a directed graph, and identifying system-only transitions (SHOW_IN_LOV_FLAG = 'N') to distinguish automation from user action. Approval-path analysis uses THEME_APPROVAL_FLAG and BUDGET_APPROVAL_FLAG to enumerate transitions that require workflow approval, which is useful when diagnosing why an object cannot advance. Because the table resides in the APPS_TS_SEED tablespace, it should be queried as reference data and treated as non-transactional in extracts.

Related Objects

• AMS.AMS_ACT_QA_CHECKS — References this table via ACTIVITY_QA_CHECK_FOR_ID, tying quality-check definitions to the status rules they enforce.
• FND_SECURITY_GROUPS — Parent of AMS_STATUS_ORDER_RULES.SECURITY_GROUP_ID, controlling security-group scoping of rules.
• FND_LOOKUP_VALUES — Supplies the status code meanings for SYSTEM_STATUS_TYPE, CURRENT_STATUS_CODE, and NEXT_STATUS_CODE, since those values are lookup-driven rather than foreign-keyed.
• AMS_STATUS_ORDER_RULES_PK / _U1 / _U2 / _N1 — Primary, unique, and supporting indexes that enforce row identity and business-key integrity and accelerate lookups.
• Oracle Marketing status-tracked entities — The activities, campaigns, and related AMS tables that consult this rule set when statuses change.