Search Results ams_approval_rules_u1

Overview

AMS.AMS_APPROVAL_RULES is a configuration and transactional metadata table in the Oracle E-Business Suite Marketing (AMS) schema. It stores the rule definitions that determine which approvals are required when a major marketing activity changes status in the system. Activities in scope include campaigns, events, promotions, and deliverables. When an activity transitions between lifecycle statuses — for example, from "New" to "Submit for Budget Approval" — one or more approvals may be triggered, and the rows in this table define those conditions.

The table records both the object type to which a rule applies and the approval type required, together with escalation timing thresholds for different priority levels and flags controlling specific approval authorities. Documented examples of approval authorities include the owner of a parent activity (for example, a campaign owner approving promotion changes), a sign-off authority empowered to authorize a monetary amount, and an HR manager representing the direct-report chain. Different rule sets may be defined per activity type, allowing an organization to tailor its approval matrix by object and by activity classification.

From a heuristic Data Vault modeling perspective, this object classifies as a standalone structure. It has no dependent child relationships in the documented FK graph; the sole foreign key reference points outward to FND_SECURITY_GROUPS rather than to a parent business entity, which is consistent with hub or reference-table treatment rather than a link or satellite.

Key Information Stored

The table physically resides in the APPS_TS_ARCHIVE tablespace with a PCTFREE of 10 and comprises 17 documented columns. The most significant columns are:

• APPROVAL_RULE_ID — the surrogate primary key (AMS_APPROVAL_RULES_PK) and the column behind the unique index AMS_APPROVAL_RULES_U1, which is the business-key candidate the user searched on.
• ARC_APPROVAL_FOR_OBJECT — the object type for which the approval rule applies; indexed non-uniquely via AMS_APPROVAL_RULES_N2.
• ACTIVITY_TYPE_CODE — the activity classification governing the rule; indexed non-uniquely via AMS_APPROVAL_RULES_N3.
• APPROVAL_TYPE — the category of approval required.
• TIMEOUT_DAYS_LOW_PRIO, TIMEOUT_DAYS_STD_PRIO, TIMEOUT_DAYS_MEDIUM_PRIO, TIMEOUT_DAYS_HIGH_PRIO — escalation thresholds in days for each priority band.
• MGR_APPROVAL_NEEDED_FLAG — indicates whether HR manager approval is required.
• PARENT_OWNER_APPROVAL_FLAG — indicates whether the parent activity owner must approve.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, controlling multi-organization data visibility.
• OBJECT_VERSION_NUMBER — used for optimistic locking during concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — standard WHO audit columns.

Common Use Cases and Queries

Typical usage includes auditing the configured approval matrix, diagnosing why an activity routed to an unexpected approver, and reporting escalation timing policy. Because the primary access paths are the unique rule identifier and the two non-unique indexes, queries filtering on object type or activity type benefit from the existing index coverage.

• Retrieve all rules for a given object type: SELECT approval_rule_id, approval_type, activity_type_code FROM ams.ams_approval_rules WHERE arc_approval_for_object = :object_type;
• Retrieve rules for a specific activity classification: SELECT * FROM ams.ams_approval_rules WHERE activity_type_code = :activity_type;
• Identify rules requiring HR manager or parent owner approval: SELECT approval_rule_id FROM ams.ams_approval_rules WHERE mgr_approval_needed_flag = 'Y' OR parent_owner_approval_flag = 'Y';
• Audit escalation policy by priority: select the timeout day columns to compare low, standard, medium, and high priority windows across rules.
• Direct lookup by primary key or business-key candidate: SELECT * FROM ams.ams_approval_rules WHERE approval_rule_id = :rule_id;

Related Objects

The documented relationship data identifies the following key dependencies and references:

• FND_SECURITY_GROUPS — referenced by AMS_APPROVAL_RULES.SECURITY_GROUP_ID; joins resolve the security group governing record visibility.
• AMS_APPROVAL_RULES_U1 — unique index on APPROVAL_RULE_ID.
• AMS_APPROVAL_RULES_N2 — non-unique index on ARC_APPROVAL_FOR_OBJECT.
• AMS_APPROVAL_RULES_N3 — non-unique index on ACTIVITY_TYPE_CODE.
• AMS_APPROVAL_RULES_PK — primary key constraint on APPROVAL_RULE_ID.

Because the object is classified as standalone in the mined FK structure, no dependent child tables are documented; the primary integration points are the security group reference and the AMS activity and approval-processing logic that consumes these rule definitions at status-transition time.