Search Results ams_custom_setup_scr_v




Overview
Explore in AI Chat

Overview

AMS_CUSTOM_SETUP_SCR_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Oracle Marketing (AMS) product family. It is marked VALID in both the 12.1.1 and 12.2.2 releases and is documented in the E-Business Suite Technical Reference Manual (ETRM). The view is designed to return all custom setup details maintained within Oracle Marketing, presenting the setup definition alongside the descriptive meanings and media attributes that are otherwise stored in separate lookup and media tables. Because custom setups drive flexible behavioral configuration for marketing objects, the view exists primarily to support reporting, diagnostics, and integration extraction without requiring callers to reproduce the underlying join logic themselves.

Underlying Base Objects

The view is defined over three referenced base objects, all of which are themselves views in the APPS schema:

  • AMS_CUSTOM_SETUPS_VL — the VL (view-with-translation) base for custom setup definitions. It supplies the primary columns, aliased as A in the view text.
  • AMS_LOOKUPS — the AMS lookup view, joined twice (aliases B and D) to resolve lookup codes into their display meanings.
  • AMS_MEDIA_VL — the translated media view, aliased as C, providing the media name.

The join predicate restricts B to LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER' and matches A.OBJECT_TYPE to B.LOOKUP_CODE. Lookup D is joined with LOOKUP_TYPE (+) = 'AMS_MEDIA_TYPE' on A.ACTIVITY_TYPE_CODE = D.LOOKUP_CODE(+). Media is attached with an outer join (A.MEDIA_ID = C.MEDIA_ID(+)), so setups without an associated media record are still returned. The use of outer joins on the lookup and media legs means the view will not drop custom setup rows simply because a related lookup or media row is absent.

Key Columns

Common Use Cases and Queries

The view is typically queried to reconcile marketing custom setup configuration, to drive downstream integration extracts, and to validate lookup and media assignments during troubleshooting. A basic retrieval of all custom setups follows:

SELECT custom_setup_id,
       setup_name,
       description,
       object_type,
       object_meaning,
       activity_type_code,
       media_id,
       media_name,
       enabled_flag
  FROM apps.ams_custom_setup_scr_v
 WHERE enabled_flag = 'Y';

Because the view already resolves lookup meanings, joins to FND_LOOKUPS or AMS_LOOKUPS for the qualifier or media type are unnecessary. To audit setups that lack an associated media record, filter on the outer-joined columns:

SELECT custom_setup_id, setup_name, activity_type_code, media_name
  FROM apps.ams_custom_setup_scr_v
 WHERE media_id IS NULL
    OR media_name IS NULL;

Callers should treat the view as strictly read-only; all DML must be directed to the AMS_CUSTOM_SETUPS base entities. As with any APPS view, the AMS responsibility or appropriate grants are required for access.