Results for “notification_type”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMS_APPROVERS is a transaction table in the AMS (Marketing) product schema of Oracle E-Business Suite, documented as VALID in both release 12.1.1 and 12.2.2. It stores the approver assignments associated with an approval rule definition. Each row identifies a single approver, the approval detail (rule step) to which that approver belongs, the sequencing and notification behavior for that approver, and the effective dating that governs when the assignment is active. The table is a supporting detail object for the Oracle Marketing approval workflow, which routes marketing objects such as campaigns, events, and deliverables through one or more approval steps before they can progress.

Under the heuristic Data Vault classification derived from the foreign key structure, AMS_APPROVERS is modeled as a standalone object. In practice it behaves as a dependent link/satellite construct: the table is keyed on a surrogate identifier and hangs off the approval detail record rather than acting as an independent hub. The single documented foreign key to FND_SECURITY_GROUPS supports security-group scoping rather than a hard parent-child dependency. This classification is a modeling suggestion only; the physical implementation remains a conventional Oracle EBS child table with who-columns and effective dating.

Key Information Stored

The table contains 19 documented columns. The most significant are:

• APPROVER_ID — the surrogate primary key, enforced by AMS_APPROVERS_PK. All external references to an approver row use this column.
• AMS_APPROVAL_DETAIL_ID — identifies the approval rule step (detail) to which the approver is attached; this is the primary business linkage.
• APPROVER_SEQ — determines the evaluation order of approvers within the same approval detail.
• APPROVER_TYPE — classifies the approver entry (for example, a user, a role, or a group), which drives how OBJECT_APPROVER_ID is resolved.
• OBJECT_APPROVER_ID — the reference to the actual approver entity identified by APPROVER_TYPE.
• NOTIFICATION_TYPE — controls how the approver is notified when the approval step is reached.
• NOTIFICATION_TIMEOUT — the elapsed time after which a pending notification is considered overdue or subject to escalation.
• START_DATE_ACTIVE and END_DATE_ACTIVE — the effective dating window during which the approver assignment applies.
• ACTIVE_FLAG — enables or disables the approver row independently of the date range.
• SEEDED_FLAG — marks Oracle-seeded approver rows versus customer-defined rows.
• SECURITY_GROUP_ID — the foreign key to FND_SECURITY_GROUPS that scopes the row for multi-organization / security-group access.
• OBJECT_VERSION_NUMBER — the optimistic locking column used by the OAF framework.
• ZD_EDITION_NAME — the editioning column associated with Oracle EBS online patching (12.2.x).
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard EBS audit who-columns.

The unique index AMS_APPROVERS_U1 is defined on (APPROVER_ID, ZD_EDITION_NAME), reflecting the edition-aware uniqueness pattern used in 12.2.2.

Common Use Cases and Queries

Typical scenarios include reporting the approver chain for a given approval rule, auditing which approvers are active within a date window, and validating that every approval detail has at least one approver assigned. A representative query returning the ordered approver list for a specific approval detail is:

• SELECT a.APPROVER_ID, a.APPROVER_SEQ, a.APPROVER_TYPE, a.OBJECT_APPROVER_ID, a.NOTIFICATION_TYPE, a.ACTIVE_FLAG FROM AMS.AMS_APPROVERS a WHERE a.AMS_APPROVAL_DETAIL_ID = :detail_id AND a.ACTIVE_FLAG = 'Y' AND TRUNC(SYSDATE) BETWEEN a.START_DATE_ACTIVE AND NVL(a.END_DATE_ACTIVE, TRUNC(SYSDATE)) ORDER BY a.APPROVER_SEQ;

Additional uses include a diagnostic query to find active approver assignments for a security group, and a completeness check to identify approval details with no effective approver:

• SELECT AMS_APPROVAL_DETAIL_ID, COUNT(*) FROM AMS.AMS_APPROVERS WHERE ACTIVE_FLAG = 'Y' GROUP BY AMS_APPROVAL_DETAIL_ID HAVING COUNT(*) = 0;

Because the table carries standard audit columns and OVN, it can also be used to trace the configuration history of approval routing changes.

Related Objects

The following objects are the most significant points of interaction, based on the documented relationships and the AMS approval model:

• FND_SECURITY_GROUPS — referenced by AMS_APPROVERS.SECURITY_GROUP_ID. This is the only documented foreign key.
• AMS_APPROVAL_DETAILS — the parent object for AMS_APPROVAL_DETAIL_ID; holds the approval rule step definition that each approver row belongs to.
• AMS_APPROVAL_RULES / approval rule headers — the higher-level rule definition under which details and approvers are organized.
• AMS_APPROVAL_HISTORY (or equivalent AMS approval transaction tables) — records the runtime approval actions taken by the approvers defined here.
• FND_USER — resolves the OBJECT_APPROVER_ID when APPROVER_TYPE denotes a specific user approver.
• AMS_CAMPAIGNS, AMS_EVENTS, AMS_DELIVERABLES — the marketing entities that are routed through the approval rules configured by these approver assignments.
• WF_* workflow tables — Oracle Workflow underlies the notification and routing behavior configured through NOTIFICATION_TYPE and NOTIFICATION_TIMEOUT.

Join predicates for these relationships should be made on APPROVER_ID, AMS_APPROVAL_DETAIL_ID, SECURITY_GROUP_ID, and OBJECT_APPROVER_ID as appropriate to the query context.