Results for “ams_approval_details”

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

Overview

AMS_APPROVAL_DETAILS is a core configuration table within the Oracle Marketing (AMS) module of Oracle E-Business Suite, valid in releases 12.1.1 and 12.2.2. As its description indicates, the table holds approval rule information that governs the marketing objects subject to approval workflows, the limits and conditions under which approvals apply, and the active date ranges for each rule. It functions as the transactional backbone for marketing approval processing, allowing organizations to define who may approve which campaigns, offers, or other marketing deliverables, and under what monetary or country-specific constraints.

From a heuristic Data Vault modeling perspective, the foreign-key topology classifies this object as hub-leaning. It carries a single-column surrogate primary key and a natural business-key candidate on (APPROVAL_DETAIL_ID, ZD_EDITION_NAME), with foreign keys reaching outward to shared reference hubs such as FND_SECURITY_GROUPS and AMS_CUSTOM_SETUPS_B. This suggests a design centered on a durable approval-rule hub surrounded by descriptive attributes, rather than a pure transactional link or satellite.

Key Information Stored

The table is physically owned by the AMS schema and contains 25 documented columns. Its surrogate primary key is APPROVAL_DETAIL_ID, maintained through the AMS_APPROVAL_DETAILS_PK constraint. The unique index AMS_APPROVAL_DETAILS_U1 on (APPROVAL_DETAIL_ID, ZD_EDITION_NAME) serves as the business-key candidate and supports the editioning model.

The most operationally significant columns include:

• APPROVAL_DETAIL_ID — surrogate primary key identifying each approval rule.
• APPROVAL_OBJECT / APPROVAL_OBJECT_TYPE — the marketing entity and its type to which the rule applies.
• APPROVAL_TYPE — classifies the approval rule behavior (for example, automatic versus list-based).
• APPROVAL_PRIORITY — ordering rank when multiple rules apply.
• APPROVAL_LIMIT_FROM / APPROVAL_LIMIT_TO — the monetary range within which the rule is effective.
• CURRENCY_CODE — currency in which the approval limits are expressed.
• USER_COUNTRY_CODE — country scoping for the rule.
• START_DATE_ACTIVE / END_DATE_ACTIVE / ACTIVE_FLAG — effective-dating and enablement controls.
• SEEDED_FLAG — indicates Oracle-seeded versus user-defined rules.
• SECURITY_GROUP_ID, BUSINESS_GROUP_ID, BUSINESS_UNIT_ID, ORGANIZATION_ID — multi-org and security context.
• CUSTOM_SETUP_ID — links to custom setup definitions in AMS_CUSTOM_SETUPS_B.
• ZD_EDITION_NAME — editioning attribute supporting online patching in 12.2.2.

Common Use Cases and Queries

Typical use cases center on diagnosing why a marketing object did or did not require approval, reviewing which rules are currently active for a given business unit, and auditing seeded versus custom configurations. Because the table is edition-aware, 12.2.2 queries should filter on ZD_EDITION_NAME to return current rows.

A common pattern retrieves active rules for a specific object and business unit:

• SELECT approval_detail_id, approval_object, approval_type, approval_priority, currency_code FROM ams_approval_details WHERE active_flag = 'Y' AND business_unit_id = :bu AND zd_edition_name = 'SET1';

For limit analysis, querying APPROVAL_LIMIT_FROM and APPROVAL_LIMIT_TO against a transaction amount identifies which rule governs. Joining to FND_SECURITY_GROUPS and AMS_CUSTOM_SETUPS_B enriches the result with security and setup context. Historical approval activity is traced by joining APPROVAL_DETAIL_ID to AMS_APPROVAL_HISTORY.

Related Objects

The most significant related objects are:

• AMS_APPROVAL_DETAILS_TL — translation table joined on APPROVAL_DETAIL_ID, holding language-specific rule text.
• AMS_APPROVAL_HISTORY — records approval events, joined on APPROVAL_DETAIL_ID.
• AMS_CUSTOM_SETUPS_B — referenced via CUSTOM_SETUP_ID.
• FND_SECURITY_GROUPS — referenced via SECURITY_GROUP_ID.
• AMS_APPROVAL_DETAILS_PK / AMS_APPROVAL_DETAILS_U1 — primary and unique constraints enforcing identity and edition uniqueness.

Together these objects form the approval configuration and audit framework for Oracle Marketing.