Results for “ams_p_source_codes_v”

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

Overview

AMS_P_SOURCE_CODES_V is a public, VALID view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AMS (Marketing) product family. In EBS 12.1.1 and 12.2.2, it presents descriptive and operational information about Marketing Source Codes: the identifiers that Oracle Marketing uses to classify and attribute responses back to the campaigns, events, offers, and schedules that generated them. The view is a UNION ALL construct rather than a simple projection, so each row represents a source code joined to exactly one underlying marketing entity, with the entity type distinguished by the SOURCE_TYPE column. It exposes the source code itself alongside the "related" metadata — RELATED_SOURCE_CODE, RELATED_SOURCE_OBJECT, and RELATED_SOURCE_ID — which is the reason the view is frequently surfaced when users search for "related_source_id". Because the view is public and read-only, it is a supported surface for reporting, custom concurrent programs, and integration extracts without querying the base AMS_SOURCE_CODES table directly.

Underlying Base Objects

The documented base objects are AMS_SOURCE_CODES (SYNONYM), AMS_CAMPAIGNS_VL, AMS_CAMPAIGN_SCHEDULES_VL, AMS_EVENT_HEADERS_VL, AMS_EVENT_OFFERS_VL, AMS_OFFERS (SYNONYM), and QP_LIST_HEADERS_VL, all VIEWs except the two synonyms. The join logic is driven by AMS_SOURCE_CODES, which is the anchor table in every branch of the UNION ALL. The ARC_SOURCE_CODE_FOR column on the source code determines which entity view is joined and thus which branch the row originates from: 'CAMP' joins AMS_CAMPAIGNS_VL on SOURCE_CODE_FOR_ID = CAMPAIGN_ID; 'EVEH' joins AMS_EVENT_HEADERS_VL on SOURCE_CODE_FOR_ID = EVENT_HEADER_ID; 'EVEO' and 'EONE' join AMS_EVENT_OFFERS_VL on SOURCE_CODE_FOR_ID = EVENT_OFFER_ID; and additional branches cover campaign schedules and offers list headers. Only active source codes are returned, since every branch filters ACTIVE_FLAG = 'Y'.

Key Columns

• SOURCE_CODE_ID — primary identifier of the source code record.
• SOURCE_CODE — the user-visible source code value used for attribution.
• SOURCE_TYPE — sourced from ARC_SOURCE_CODE_FOR; indicates the entity class ('CAMP', 'EVEH', 'EVEO', 'EONE').
• OBJECT_ID — the identifier of the parent entity (campaign, event header, event offer, or schedule).
• NAME, STATUS, START_DATE, END_DATE — descriptive attributes of the parent entity, mapped from each entity view.
• INBOUND_URL, INBOUND_EMAIL_ID, INBOUND_PHONE_NO — channel response destinations for the parent entity.
• RELATED_SOURCE_CODE, RELATED_SOURCE_OBJECT, RELATED_SOURCE_ID — the linkage columns that associate a source code with a related marketing object; RELATED_SOURCE_ID holds the identifier of that related object and RELATED_SOURCE_OBJECT identifies its type.
• CUSTOM_SETUP_ID / SETUP_TYPE_ID — setup or classification identifiers carried from the campaign or event entity view.

Common Use Cases and Queries

Typical uses include response-attribution reports, source code inventories by campaign or event, and extracts feeding external marketing analytics. A frequent requirement is tracing the related object behind a source code:

• List active source codes by type: SELECT SOURCE_CODE, SOURCE_TYPE, OBJECT_ID, NAME FROM AMS_P_SOURCE_CODES_V WHERE SOURCE_TYPE = 'CAMP' ORDER BY NAME;
• Find records with a populated related object: SELECT SOURCE_CODE, RELATED_SOURCE_CODE, RELATED_SOURCE_OBJECT, RELATED_SOURCE_ID FROM AMS_P_SOURCE_CODES_V WHERE RELATED_SOURCE_ID IS NOT NULL;
• Report inbound response channels per campaign: SELECT NAME, SOURCE_CODE, INBOUND_URL, INBOUND_EMAIL_ID, INBOUND_PHONE_NO FROM AMS_P_SOURCE_CODES_V WHERE SOURCE_TYPE = 'CAMP';

Because the view filters to active source codes and performs UNION ALL joins, queries should filter on SOURCE_TYPE where a specific entity class is intended to avoid scanning all branches.