Results for “ast_source_only_v”

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

Overview

AST_SOURCE_ONLY_V is a consolidated, read-only view owned by the APPS schema within the Oracle E-Business Suite TeleSales (AST) module. It presents a unified list of active "source codes" together with the descriptive attributes of the business object each source code points to. In the TeleSales and Marketing (AMS) data model, a source code is a trackable identifier used to attribute an inbound or outbound response to a specific campaign, event, or schedule. The AST_SOURCE_ONLY_V view flattens this relationship by joining the source-code definition table to the four principal source-bearing objects, so that reporting and integration layers can resolve a source code to its parent entity in a single query.

The view is significant in the context of Oracle EBS 12.1.1 and 12.2.2 because it abstracts the underlying multi-table relationship and exposes a consistent column set across heterogeneous source types. It is a dependency of the AMS_CAMPAIGNS_VL view family, which is frequently referenced when reporting on campaign source codes.

Underlying Base Objects

The view is defined as a UNION ALL over four branches, each joining AMS_SOURCE_CODES to a different source-bearing view. The documented referenced base objects are:

• AMS_SOURCE_CODES (SYNONYM) — the driving table holding the source code, its active flag, the source-type discriminator, and the foreign key to the related object.
• AMS_CAMPAIGNS_VL (VIEW) — supplies campaign name, status, execution start/end dates, and inbound response channel attributes for source codes where ARC_SOURCE_CODE_FOR = 'CAMP'.
• AMS_EVENT_HEADERS_VL (VIEW) — supplies event header name, system status, active dates, and inbound URL, email, and phone for 'EVEH' source codes.
• AMS_EVENT_OFFERS_VL (VIEW) — supplies event offer name, status, event dates, and inbound channel data for 'EVEO' source codes.
• AMS_CAMPAIGN_SCHEDULES_VL (VIEW) — supplies schedule name, status, and start/end date-times for schedule-based source codes; channel columns are returned as NULL for this branch.

Each branch is restricted to records where SOC.ACTIVE_FLAG = 'Y', ensuring that only currently active source codes are surfaced. The join key is SOC.SOURCE_CODE_FOR_ID matched to the primary identifier of the relevant object (for example, CAMP.CAMPAIGN_ID).

Key Columns

• SOURCE_CODE_ID — surrogate primary key of the source code record.
• SOURCE_CODE — the human-readable source code value.
• SOURCE_TYPE — the source-object discriminator (aliased from ARC_SOURCE_CODE_FOR), with values such as CAMP, EVEH, and EVEO.
• OBJECT_ID — the identifier of the underlying business object the source code references.
• NAME — the object's display name (campaign, event header, event offer, or schedule name).
• STATUS — the object's status or system status code.
• START_DATE / END_DATE — the object's active or execution date range.
• INBOUND_URL, INBOUND_EMAIL_ID, INBOUND_PHONE_NO — inbound response channel attributes; these are populated for campaign, event header, and event offer branches and NULL for schedules.
• RELATED_SOURCE_CODE, RELATED_SOURCE_OBJECT, RELATED_SOURCE_ID — metadata linking a source code to a related source, supporting hierarchical or referral attribution.

Common Use Cases and Queries

Typical uses include resolving active source codes for campaign attribution, validating inbound channel configuration, and feeding list-generation or response-tracking reports. A common query retrieves all active campaign source codes with their campaign names:

SELECT source_code_id, source_code, name, status, start_date, end_date
FROM   apps.ast_source_only_v
WHERE  source_type = 'CAMP'
ORDER  BY name;

To audit inbound channel configuration across source types:

SELECT source_type, source_code, name, inbound_url, inbound_email_id, inbound_phone_no
FROM   apps.ast_source_only_v
WHERE  inbound_url IS NOT NULL
   OR  inbound_email_id IS NOT NULL
   OR  inbound_phone_no IS NOT NULL;

To relate a source code to a campaign record for cross-checking:

SELECT s.source_code, s.name, c.campaign_id
FROM   apps.ast_source_only_v s, apps.ams_campaigns_vl c
WHERE  s.source_type = 'CAMP'
AND    s.object_id = c.campaign_id;

Because the view already filters on ACTIVATE_FLAG and the four source-type discriminators, reporting logic can avoid repeating those predicates and rely on SOURCE_TYPE for segmentation.