Search Results ams_source_codes_details_v




Overview
Explore in AI Chat

Overview

The AMS_SOURCE_CODES_DETAILS_V view resides in the APPS schema and belongs to the Oracle Marketing (AMS) product family. It consolidates descriptive information about the various source code records maintained within Oracle Marketing, resolving each source code to the specific marketing object it references — campaigns, event headers, event offers, campaign schedules, and offers/list headers. The view is defined as a UNION ALL of five distinct queries, each targeting a different source-code category as identified by the ARC_SOURCE_CODE_FOR discriminator column. Its principal role is to provide a single, denormalized reporting surface that joins source codes to their owning entities while exposing a common set of descriptive attributes, including the PRIVATE_FLAG that determines whether the referenced object is publicly visible or restricted. Because it joins the translatable _VL views, it returns language-appropriate names and descriptions, making it suitable for user-facing inquiries, LOV-style validation, and integration extracts.

Underlying Base Objects

The view is defined over the following documented base objects:

In every branch the join is driven by SOC.ARC_SOURCE_CODE_FOR equal to the relevant literal and SOC.SOURCE_CODE_FOR_ID equal to the target object's primary key, with the additional restriction that SOC.ACTIVE_FLAG = 'Y'.

Key Columns

  • SOURCE_CODE_ID — primary key of the underlying source code record.
  • SOURCE_CODE — the user-visible source code value.
  • SOURCE_TYPE — alias for ARC_SOURCE_CODE_FOR; indicates the category ('CAMP', 'EVEH', 'EVEO', 'CSCH', or the offers branch).
  • OBJECT_ID — the identifier of the referenced marketing object.
  • NAME — the name of the campaign, event header, event offer, schedule, or list header.
  • DESCRIPTION — descriptive text of the referenced object.
  • PRIVATE_FLAG — indicates whether the object is private/restricted. For campaigns, events and offers it is inherited from the owning entity; for campaign schedules it is fixed at 'N'; for offers it derives from CONFIDENTIAL_FLAG.
  • CUSTOM_SETUP_ID / SETUP_TYPE_ID — setup identifiers used by the various branches to link to their configuration definitions.

Common Use Cases and Queries

The view is typically queried to list active source codes alongside the objects they reference, or to filter objects by their private/confidential status.

To retrieve all active source codes referencing campaigns, including visibility:

  • SELECT source_code, source_type, name, private_flag FROM ams_source_codes_details_v WHERE source_type = 'CAMP' ORDER BY source_code;

To identify private or confidential marketing objects exposed through source codes:

  • SELECT source_code, source_type, object_id, name FROM ams_source_codes_details_v WHERE private_flag = 'Y';

To resolve a specific source code to its target object across all categories:

  • SELECT source_code, source_type, object_id, name, description FROM ams_source_codes_details_v WHERE source_code = :p_source_code;

Because the view only returns rows where ACTIVE_FLAG = 'Y', it is well suited to populating selection lists and to integration extracts that must exclude retired source codes.