Results for “ams_p_oso_source_codes_v”

32 results




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

Overview

The AMS_P_OSO_SOURCE_CODES_V view is a public (APPS-owner) database object within the Oracle E-Business Suite AMS – Marketing product. It exposes descriptive information about Marketing Source Codes, which are the identifiers used to attribute leads, responses, and other marketing transactions to their originating campaign, event, or offer. In release 12.1.1 and 12.2.2 the view resides in the APPS schema and is reported as VALID in ETRM.

The view is intended for reporting and integration rather than for transactional data entry. It consolidates marketing source code records with their associated source object (campaign, event header, or event offer) so that downstream reports, dashboards, and third-party integrations can present the source code alongside a human-readable name, status, dates, and inbound contact channels (URL, email address, phone number). Because it is a public view, it is safe to reference from custom concurrent programs, BI Publisher data templates, Oracle Discoverer/BI reports, and external systems that query the APPS schema directly or through a synonym. The _P_ naming convention signals a public interface object intended for customer consumption.

Underlying Base Objects

The view is defined as a compound UNION ALL query over three source-object families, joined in each branch to the source code and lookup tables. The documented base objects are:

  • AMS_SOURCE_CODES (synonym) – the central table holding source code identifiers and their relationships (SOURCE_CODE_ID, SOURCE_CODE, ARC_SOURCE_CODE_FOR, ACTIVE_FLAG, and the related-source columns).
  • AMS_CAMPAIGNS_VL (view) – supplies campaign name, status code, execution start/end dates, and inbound URL, email, and phone for campaign-based source codes where ARC_SOURCE_CODE_FOR = 'CAMP'.
  • AMS_EVENT_HEADERS_VL (view) – supplies event header name, system status, active-from/active-to dates, and inbound contact channels for event-based source codes where ARC_SOURCE_CODE_FOR = 'EVEH'.
  • AMS_EVENT_OFFERS_VL (view) – supplies event offer name, system status, event start/end dates, and inbound contact channels for offer-based source codes (the third UNION ALL branch).
  • AMS_LOOKUPS (view) – provides the lookup meaning for LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER', which decodes the source type qualifier.

Documented referenced objects also include AMS_CAMPAIGN_SCHEDULES_VL, AMS_OFFERS (synonym), and QP_LIST_HEADERS_VL, reflecting the broader Marketing dictionary the view participates in. Each branch enforces ACTIVE_FLAG = 'Y' on the source code and restricts the originating object to statuses ACTIVE, ON_HOLD, or COMPLETED, so the view returns only currently usable source codes attached to non-cancelled parents.

Key Columns

  • SOURCE_CODE_ID – primary identifier of the source code record in AMS_SOURCE_CODES.
  • SOURCE_CODE – the code value used to attribute a response or lead.
  • SOURCE_TYPE (aliased from ARC_SOURCE_CODE_FOR) – discriminates the parent object: CAMP, EVEH, or an offer-related qualifier.
  • OBJECT_ID – the identifier of the parent object (campaign ID, event header ID, or offer ID).
  • NAME – the parent object name (campaign name, event header name, or event offer name).
  • STATUS – the parent system status code.
  • START_DATE / END_DATE – execution or active date range of the parent object.
  • INBOUND_URL, INBOUND_EMAIL_ID, INBOUND_PHONE_NO – inbound contact channel attributes.
  • RELATED_SOURCE_CODE, RELATED_SOURCE_OBJECT, RELATED_SOURCE_ID – pointers to any parent/related source code hierarchy.
  • MEANING – decoded lookup meaning for the source type qualifier.

Common Use Cases and Queries

The view is typically used to build source-code reference listings, response attribution reports, and inbound-channel monitoring. A representative query retrieving all active source codes with their parent names and types:

  • SELECT source_code, source_type, name, status, start_date, end_date FROM ams_p_oso_source_codes_v ORDER BY source_type, source_code;

Filtering to campaign-driven source codes only is common when reconciling campaign response counts:

  • SELECT source_code, name, inbound_url, inbound_email_id FROM ams_p_oso_source_codes_v WHERE source_type = 'CAMP';

Because the view joins AMS_SOURCE_CODES to AMS_CAMPAIGNS_VL, AMS_EVENT_HEADERS_VL, and AMS_EVENT_OFFERS_VL, queries should apply selective predicates on SOURCE_TYPE, SOURCE_CODE, or OBJECT_ID and avoid full scans on large marketing datasets. The statuses ACTIVE, ON_HOLD, and COMPLETED are the only ones returned, so historical CANCELLED parents will not appear.