Search Results ams_approval_rules_pk

Overview

AMS_APPROVAL_RULES is a configuration table in the Oracle E-Business Suite Marketing (AMS) module. It stores the approval rules that govern what kind of approvals are required when a "major activity" (a marketing campaign, event, or similar AMS object) transitions between workflow statuses. In effect, the table is the metadata backbone of the AMS approval engine: rather than hard-coding approval logic, the application reads the appropriate row for a given activity type and approval context, then determines which approvers must act, at what priority, and within what deadline.

The table resides in the AMS schema and is documented as VALID in both ETRM 12.1.1 and 12.2.2. Its ETRM metadata classifies it heuristically (mined from FK structure) as standalone — meaning it has no child tables referencing it and behaves largely as a reference/configuration entity. In a Data Vault modeling suggestion, this would be treated as a satellite-like reference object (dimension), keyed on its own surrogate and dependent on the owner (SECURITY_GROUP_ID) for access scoping, rather than as a link joining two business hubs.

Key Information Stored

Of the 17 documented columns, the following are the most operationally significant:

• APPROVAL_RULE_ID — the surrogate primary key (AMS_APPROVAL_RULES_PK) and the only documented unique business-key candidate (AMS_APPROVAL_RULES_U1).
• ARC_APPROVAL_FOR_OBJECT — identifies the object type to which the approval rule applies (e.g., campaign, event).
• ACTIVITY_TYPE_CODE — the marketing activity type the rule governs.
• APPROVAL_TYPE — the nature/route of approval required.
• MGR_APPROVAL_NEEDED_FLAG — indicates whether manager-level approval is required.
• PARENT_OWNER_APPROVAL_FLAG — indicates whether the parent owner's approval is required.
• TIMEOUT_DAYS_LOW_PRIO, TIMEOUT_DAYS_MEDIUM_PRIO, TIMEOUT_DAYS_STD_PRIO, TIMEOUT_DAYS_HIGH_PRIO — escalation/response deadlines, one per priority tier.
• SECURITY_GROUP_ID — scopes the rule to a security group (FK to FND_SECURITY_GROUPS).
• OBJECT_VERSION_NUMBER and the standard CREATED_BY / CREATION_DATE / LAST_UPDATED_BY / LAST_UPDATE_DATE / LAST_UPDATE_LOGIN audit columns — supporting concurrency control and audit trails.

Common Use Cases and Queries

Typical reporting and configuration queries resolve the approval behavior for a given activity type:

• List all rules for a specific activity type: SELECT APPROVAL_RULE_ID, APPROVAL_TYPE, MGR_APPROVAL_NEEDED_FLAG, PARENT_OWNER_APPROVAL_FLAG FROM AMS_APPROVAL_RULES WHERE ACTIVITY_TYPE_CODE = :type;
• Identify rules requiring manager approval and their high-priority timeouts.
• Audit recent changes: join CREATED_BY/LAST_UPDATED_BY to FND_USER and filter on LAST_UPDATE_DATE.
• Confirm the escalation SLA per priority tier using the four TIMEOUT_DAYS_* columns for workflow deadline tuning.

Because the SLAs are stored per priority, DBAs and workflow administrators can adjust response windows without code changes — a common operational use of this table.

Related Objects

• FND_SECURITY_GROUPS — referenced by AMS_APPROVAL_RULES.SECURITY_GROUP_ID. Join on SECURITY_GROUP_ID = :group_id to determine which security group owns each rule.
• AMS_APPROVAL_RULES_PK / AMS_APPROVAL_RULES_U1 — the primary and unique indexes enforcing APPROVAL_RULE_ID uniqueness.
• FND_USER / FND_RESPONSIBILITY — used to resolve the CREATED_BY and LAST_UPDATED_BY actor identities for audit reporting.
• AMS activity/event tables (e.g., campaign and event headers) — consumed indirectly by the approval engine when a major activity changes status; these tables supply the activity type that maps to APPROVAL_RULE_ID.
• Workflow/Marketing application logic — the AMS approval engine reads this table at runtime to determine approver routing, manager/parent-owner flags, and escalation timeouts; there is no separate child table, consistent with the standalone classification.