Results for “ams_custom_setups_b_u1”

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

Overview

AMS_CUSTOM_SETUPS_B is a core base table in the Oracle E-Business Suite Marketing (AMS) module. As its name and the ETRM metadata indicate, it stores all custom setup definitions used throughout Oracle Marketing, Oracle Trade Management (OZF) and Oracle Partner Management (PV). In release 12.1.1 and 12.2.2, the table resides in the AMS schema and is a partitioned, multi-organization-enabled object, carrying the standard EBS audit columns and a ZD_EDITION_NAME column that supports Edition-Based Redefinition (EBR) in 12.2.x.

The table functions as a configuration registry: each row defines a custom setup that controls how marketing objects (campaigns, offers, dialogs, deliverables, event offers, price lists, partner programs and funds) are created, displayed or processed. When a user defines a custom setup through the Marketing or Trade Management setup UI, a row is written here and the surrounding AMS/OZF tables reference it by CUSTOM_SETUP_ID.

From a dimensional-modelling perspective, the Data Vault classification is hub-leaning. CUSTOM_SETUP_ID is a stable business key, and the table is referenced by a large fan-out of dependent tables, which is characteristic of a hub surrounded by links and satellites rather than a pure satellite itself. Treating it as a hub in a Data Vault model is a reasonable heuristic.

Key Information Stored

The table contains 18 documented columns. The most significant are listed below; the remainder are standard audit and EBR control columns.

• CUSTOM_SETUP_ID — the surrogate primary key, defined by AMS_CUSTOM_SETUPS_B_PK. It also participates in the unique index AMS_CUSTOM_SETUPS_B_U1 together with ZD_EDITION_NAME, making that pair the business-key candidate for EBR-aware environments.
• ACTIVITY_TYPE_CODE — classifies the marketing activity type (campaign, event, offer, etc.) that the custom setup applies to.
• MEDIA_ID — foreign key to AMS_MEDIA_B, linking the setup to the media/channel definition.
• ENABLED_FLAG — indicates whether the custom setup is active and selectable.
• OBJECT_TYPE — identifies the type of marketing object the setup controls (offer, dialog, deliverable, price list, etc.).
• SOURCE_CODE_SUFFIX — suffix used when generating source codes for objects created from the setup.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS, enforcing EBS security group access.
• APPLICATION_ID — foreign key to FND_APPLICATION, identifying the owning application (typically AMS, OZF or PV).
• ALLOW_ESSENTIAL_GROUPING — controls whether essential grouping rules are permitted for the setup.
• USAGE — describes how the setup is intended to be used.
• MIGRATED_CUSTOM_SETUP_ID — records the legacy setup ID from a prior migration, used for reconciliation.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER and ZD_EDITION_NAME — standard audit, concurrency-control and EBR columns.

Common Use Cases and Queries

Blog and reporting queries on AMS_CUSTOM_SETUPS_B typically retrieve active setups for a given activity type or object type, join to the translation table for a display name, or join to dependent AMS tables to confirm which objects were created from a setup.

Example — list enabled setups for an activity type with the translated name:

SELECT cs.custom_setup_id,
       cs.activity_type_code,
       cs.object_type,
       t.custom_setup_name
FROM   ams.ams_custom_setups_b  cs,
       ams.ams_custom_setups_tl t
WHERE  cs.custom_setup_id = t.custom_setup_id
AND    cs.enabled_flag     = 'Y'
AND    cs.activity_type_code = :p_activity_type
AND    t.language = USERENV('LANG');

Example — identify marketing objects created from a specific setup:

SELECT o.offer_id, o.offer_code, o.offer_name
FROM   ams.ams_offers o
WHERE  o.custom_setup_id = :p_custom_setup_id;

These patterns support setup audits, migration validation (comparing MIGRATED_CUSTOM_SETUP_ID to legacy systems), and impact analysis before disabling a setup.

Related Objects

The following are the most significant dependents and references, based on the ETRM foreign-key metadata.

• AMS_CUSTOM_SETUPS_TL — stores translated setup names/descriptions; joined on CUSTOM_SETUP_ID. Mandatory for any user-facing report.
• AMS_CUSTOM_SETUP_ATTR — holds the attribute values that make up each custom setup; joined on CUSTOM_SETUP_ID.
• AMS_MEDIA_B — parent definition for MEDIA_ID.
• FND_APPLICATION — parent definition for APPLICATION_ID.
• FND_SECURITY_GROUPS — parent definition for SECURITY_GROUP_ID.
• AMS_OFFERS and OZF_OFFERS — offers created using a setup; both reference CUSTOM_SETUP_ID.
• AMS_DIALOGS_ALL_B — dialogs that inherit behaviour from the setup.
• PV_PARTNER_PROGRAM_B, PV_PG_ENRL_REQUESTS — partner program definitions and enrolment requests tied to a setup.
• AMS_EVENT_OFFERS_ALL_B — event offers linked via SETUP_TYPE_ID.
• AMS_CUSTOM_SETUPS_BK — the backup table maintained by EBS for the setups interface; references CUSTOM_SETUP_ID.

Because CUSTOM_SETUP_ID is referenced by dozens of AMS, OZF and PV tables, the table sits at the centre of the Marketing setup data model, and any purge or update must account for its wide transitive closure.