Search Results ams_p_source_codes_v

Overview

The ASO_I_MKTG_SRC_CODES_V view is a reporting and integration object owned by the APPS schema in Oracle E-Business Suite. It belongs to the ASO – Order Capture product family and is documented as a VALID view in both EBS 12.1.1 and 12.2.2. Its stated purpose, per the ETRM metadata, is to present "the information related to a source code."

Source codes in Oracle EBS are the qualifiers used to identify the origin of a sales lead, opportunity, or order — for example, a campaign response, an inbound telephone inquiry, a web landing page, or a toll-free number. This view consolidates the source code record with its decoded source type, exposing a flattened, inquiry-friendly projection that can be consumed by concurrent programs, reports, Oracle Integration adapters, and custom PL/SQL. Because it is prefixed "ASO_I_" it conforms to the public interface-view naming convention, signalling that it is intended for external consumption rather than internal processing.

The user search term ams_p_source_codes_v reflects a common confusion: integrators frequently go looking for the AMS schema object directly, when the sanctioned access path in the Order Capture context is this ASO interface view, which sits on top of AMS_P_SOURCE_CODES_V.

Underlying Base Objects

The ETRM 12.2.2 metadata documents two referenced base objects, both of which are themselves views:

• AMS_P_SOURCE_CODES_V – the principal source of source-code data (aliased SC in the view text). It supplies the identifier, code, type, name, status, validity dates, and inbound contact attributes.
• AMS_LOOKUPS – the Oracle Marketing lookups view (aliased LK), used here strictly as a decode source for the source type.

The defining SQL joins the two objects on the condition LK.LOOKUP_TYPE = 'AMS_SYS_ARC_QUALIFIER' and LK.LOOKUP_CODE = SC.SOURCE_TYPE. In other words, the view does not introduce new data of its own; it enriches each source code row with the human-readable meaning of its type from the ARC (architecture/qualifier) lookup set. This is an inner join, so any source code whose SOURCE_TYPE is absent from the AMS_SYS_ARC_QUALIFIER lookup set will not appear in the view — a behaviour worth noting during reconciliation.

Key Columns

• SOURCE_CODE_ID – primary identifier of the source code record.
• SOURCE_CODE – the user-visible source code value.
• SOURCE_TYPE – the lookup code (from AMS_SYS_ARC_QUALIFIER) classifying the source.
• SOURCE_TYPE_NAME – the decoded lookup MEANING for the source type.
• OBJECT_ID – the identifier of the associated business object.
• NAME – the descriptive name of the source code.
• STATUS – active/inactive indicator governing whether the source code may be used.
• START_DATE / END_DATE – the effective date range during which the source code is valid.
• INBOUND_URL, INBOUND_EMAIL_ID, INBOUND_PHONE_NO – the inbound response channels (web URL, e-mail address, telephone number) mapped to the source code.

Common Use Cases and Queries

Typical scenarios include populating source-code LOVs in an external order-capture front end, validating a source code received from a web storefront before order import, and extracting the inbound URL or phone number associated with a marketing campaign for a reporting dashboard.

To list all active source codes with their decoded type:

SELECT source_code_id, source_code, source_type,
       source_type_name, name, start_date, end_date
FROM   apps.aso_i_mktg_src_codes_v
WHERE  status = 'A'
AND    TRUNC(SYSDATE) BETWEEN start_date
      AND NVL(end_date, TRUNC(SYSDATE));

To retrieve the inbound contact channel for a specific code:

SELECT source_code, source_type_name, inbound_url,
       inbound_email_id, inbound_phone_no
FROM   apps.aso_i_mktg_src_codes_v
WHERE  source_code = :p_source_code;

Because the view is defined over AMS views, it should be queried with the APPS schema context and never updated directly. All maintenance is performed through the Oracle Marketing source code setup UI.