Results for “ams_custom_setups_bk”

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

Overview

AMS_CUSTOM_SETUPS_BK is a backup (or "history") table residing in the AMS schema, the database schema owned by the Oracle Marketing product family within Oracle E-Business Suite. The "_BK" suffix is a well-established Oracle Applications naming convention indicating that the table captures a snapshot, archival, or shadow copy of rows from its live counterpart, AMS_CUSTOM_SETUPS_B. In this role the object does not participate directly in online transactional processing; instead it supports data preservation, migration, upgrade, and reconciliation activities performed by administrators and concurrent programs.

The table stores configuration records for "custom setups" within the Marketing module. Custom setups typically govern how marketing objects—such as campaigns, activities, and media—are organized, grouped, and controlled against a set of user-defined business rules. The object is identified in the ETRM 12.2.2 documentation as a standalone table with a documented physical schema of sixteen columns under the AMS owner.

Applying a heuristic Data Vault classification, this table is best modeled as a satellite attached to an AMS_CUSTOM_SETUPS hub or a dependent link. However, because the ETRM metadata describes it as standalone—having only a single self-referencing-style foreign key (CUSTOM_SETUP_ID → AMS_CUSTOM_SETUPS_B) and no inbound references—this classification should be treated as a modeling suggestion rather than a confirmed integration pattern. Its standalone nature reflects its archive/backup purpose rather than a true subject-area role.

Key Information Stored

The table's sixteen documented columns include the following most significant attributes:

• CUSTOM_SETUP_ID — The surrogate primary key for each custom setup row. It is also the column carrying the single documented foreign key, referencing AMS_CUSTOM_SETUPS_B.CUSTOM_SETUP_ID, which ties each backup row to its live master record.
• SETUP_NAME — The user-defined business name of the custom setup. This is the principal business-key candidate, since users identify setups by name rather than by identifier.
• ACTIVITY_TYPE_CODE — A lookup code classifying the marketing activity type the setup applies to.
• MEDIA_ID — Identifies the media record associated with the setup, linking configuration to a channel or medium.
• OBJECT_TYPE and SOURCE_CODE_SUFFIX — Together these describe the specific marketing object the setup governs and any source-code discrimination required for the record.
• APPLICATION_ID — Identifies the owning application, used for multi-application partitioning within the EBS data model.
• ENABLED_FLAG — The standard EBS Yes/No flag controlling whether the setup is active.
• ALLOW_ESSENTIAL_GROUPING — A configuration flag determining whether essential grouping is permitted for the setup.
• USAGE — A descriptive or coded attribute recording how the setup is intended to be used.
• OBJECT_VERSION_NUMBER — The optimistic-locking version column used by EBS forms and APIs to detect concurrent updates.

The remaining columns are the mandatory audit-standard attributes: LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, and LAST_UPDATE_LOGIN. These five columns appear on virtually every transactional and setup table in EBS and provide full "who/when" traceability. The primary-key/business-key distinction is therefore: CUSTOM_SETUP_ID as the surrogate key, with SETUP_NAME acting as the natural business-key candidate.

Common Use Cases and Queries

Because the object is a backup table, its principal uses are recovery, auditing, and data-diff reporting. A typical reconciliation query joins the backup to its live master to detect drift:

• Row-count and gap analysis: SELECT b.custom_setup_id FROM ams.ams_custom_setups_bk b WHERE NOT EXISTS (SELECT 1 FROM ams.ams_custom_setups_b l WHERE l.custom_setup_id = b.custom_setup_id); — identifies backup rows with no corresponding live record.
• Value drift detection: Compare SETUP_NAME, ENABLED_FLAG, and OBJECT_VERSION_NUMBER between the backup and the live table to flag setups that have changed since the backup was taken.
• Point-in-time audit: Use LAST_UPDATE_DATE and LAST_UPDATED_BY on the backup to reconstruct who modified a setup and when, particularly where the live row has since been changed.
• Configuration reporting: Aggregate by ACTIVITY_TYPE_CODE, MEDIA_ID, or APPLICATION_ID to report how many custom setups are enabled per activity type or channel.
• Upgrade validation: Following an EBS upgrade (for example 12.1.1 to 12.2.2), compare the backup captured pre-upgrade with post-upgrade live data to verify that configuration survived conversion.

Queries should always be issued against the AMS schema or via a synonym, and read access should be restricted because backup tables are not tuned for interactive query performance.

Related Objects

The most significant related objects are:

• AMS_CUSTOM_SETUPS_B — The live base table; joined on CUSTOM_SETUP_ID. This is the direct parent of the backup, confirmed by the documented foreign key.
• AMS_CUSTOM_SETUPS_TL — The translation table holding language-specific setup names and descriptions, joined on CUSTOM_SETUP_ID.
• AMS_CUSTOM_SETUPS_VL — The multi-language view presenting translated setup data; a common reporting entry point.
• AMS_MEDIA — Referenced through MEDIA_ID to resolve the media/channel behind a setup.
• AMS_ACTIVITY_TYPES (or equivalent lookup view) — Resolves ACTIVITY_TYPE_CODE into a user-facing meaning.
• FND_APPLICATION — Joined on APPLICATION_ID to translate the owning application identifier.
• FND_USER — Joined on LAST_UPDATED_BY and CREATED_BY for audit attribution.
• AMS_CUSTOM_SETUPS_PKG / AMS setup APIs — PL/SQL packages whose DML against the base table ultimately produces the rows later captured in this backup.

In summary, AMS_CUSTOM_SETUPS_BK is a DBA-oriented backup object within the AMS Marketing schema. It is not a reporting table in its own right, but it is an important safeguard for auditing and reconciling custom setup configuration across upgrades, migrations, and routine administration.