Search Results ams_sys_arc_qualifier




Overview
Explore in AI Chat

Overview

The APPS.AMS_P_OSO_SOURCE_CODES_V view is a consolidated reporting and integration object within the Oracle Marketing (AMS) module of Oracle E-Business Suite. It presents active source codes that have been registered against marketing campaigns, event headers, and event offers, enriching each source code with the governing object's identity, status, dates, and inbound-response channel attributes. The view derives its functional name from the source-code concept: a "source code" is a token used to attribute an inbound response — web visit, email reply, or telephone enquiry — to the campaign, event, or offer that generated it.

Its principal design characteristic is the vertical union of three object families into a single result set, unified by the ARC_SOURCE_CODE_FOR discriminator. The view therefore acts as a single access point for applications, concurrent programs, and ad hoc reports that need a cross-object picture of active source codes rather than three separate queries. The AMS_SYS_ARC_QUALIFIER lookup type referenced by the user is central to this behaviour: the final join to AMS_LOOKUPS is restricted to LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER', and the lookup code is matched to the ARC_SOURCE_CODE_FOR value. This lookup supplies the human-readable MEANING for each source type and constrains the view to only those qualifier values that Oracle Marketing recognises as valid source-code owners.

Underlying Base Objects

The ETRM 12.2.2 metadata documents the following referenced objects: AMS_CAMPAIGNS_VL, AMS_CAMPAIGN_SCHEDULES_VL, AMS_EVENT_HEADERS_VL, AMS_EVENT_OFFERS_VL, AMS_LOOKUPS, AMS_OFFERS (synonym), AMS_SOURCE_CODES (synonym), and QP_LIST_HEADERS_VL.

The view text shows that each union branch is anchored on the AMS_SOURCE_CODES table, which holds SOURCE_CODE_ID, SOURCE_CODE, ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID, ACTIVE_FLAG, and the related-source columns. The first branch joins to AMS_CAMPAIGNS_VL on CAMPAIGN_ID; the second to AMS_EVENT_HEADERS_VL on EVENT_HEADER_ID; the third to AMS_EVENT_OFFERS_VL on EVENT_OFFER_ID. All branches join to AMS_LOOKUPS through the AMS_SYS_ARC_QUALIFIER lookup type. The AMS_CAMPAIGN_SCHEDULES_VL, AMS_OFFERS, and QP_LIST_HEADERS_VL references reflect the wider dependency tree of the underlying _VL views rather than direct joins in this view.

Key Columns

  • SOURCE_CODE_ID — primary identifier of the source-code record.
  • SOURCE_CODE — the token used to capture and attribute responses.
  • SOURCE_TYPE — alias of ARC_SOURCE_CODE_FOR; the owning object type, typically CAMP, EVEH, or EVEO.
  • OBJECT_ID — alias of SOURCE_CODE_FOR_ID; the identifier of the owning campaign, event header, or event offer.
  • NAME — owning object name (campaign name, event header name, or event offer name).
  • STATUS — object status; the view filters to ACTIVE, ON_HOLD, and COMPLETED.
  • START_DATE / END_DATE — execution or active dates of the owning object.
  • INBOUND_URL, INBOUND_EMAIL_ID, INBOUND_PHONE_NO — inbound response channels associated with the source.
  • RELATED_SOURCE_CODE, RELATED_SOURCE_OBJECT, RELATED_SOURCE_ID — linkage to a parent or associated source code.
  • MEANING — decoded source type from the AMS_SYS_ARC_QUALIFIER lookup.

Common Use Cases and Queries

Typical scenarios include validating that active source codes exist for live campaigns, driving inbound-response channel configuration, and building cross-object source-code inventories for analytics.

  • List active campaign source codes with their inbound channels.
  • Trace a source code back to its owning event header or offer.
  • Audit related source-code hierarchies.

A representative query:

SELECT source_code, source_type, meaning, name, status, start_date, end_date, inbound_url
FROM apps.ams_p_oso_source_codes_v
WHERE source_type = 'CAMP'
ORDER BY name;

A second pattern resolves a specific token to its owner:

SELECT source_code, name, object_id, status, meaning
FROM apps.ams_p_oso_source_codes_v
WHERE source_code = :p_source_code;