Search Results related_source_code

Overview

AST_OFFER_ONLY_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the AST (TeleSales) product family. It presents a consolidated, denormalized projection of marketing offers that have been defined as source codes within the Oracle Advanced Marketing (AMS) framework and that correspond to price list headers maintained by Oracle Advanced Pricing (QP). Because the view restricts its result set to source codes whose ARC_SOURCE_CODE_FOR attribute equals 'OFFR', it exposes only those source codes that represent offers; other source code types (for example campaigns, events, or interest codes) are filtered out. In EBS 12.1.1 and 12.2.2, this view serves reporting, integration, and lookup purposes where a caller needs a lightweight list of active offers joined with their descriptive price list header attributes, without navigating the full AMS_SOURCE_CODES or AMS_OFFERS base structures directly.

Underlying Base Objects

The view is defined over three documented base objects:

• AMS_SOURCE_CODES (SYNONYM) — the driving table, supplying the source code identity, destination type, active flag, and related source code references.
• AMS_OFFERS (SYNONYM) — the offers entity, providing the linkage between a source code and its underlying price list header through QP_LIST_HEADER_ID, as well as the offer status code.
• QP_LIST_HEADERS_VL (VIEW) — the translated price list header view, supplying the descriptive name or description and the effective start and end dates of the underlying list.

The join predicates require SOC.ARC_SOURCE_CODE_FOR = 'OFFR', SOC.ACTIVE_FLAG = 'Y', SOC.SOURCE_CODE_FOR_ID = OFFR.QP_LIST_HEADER_ID, and OFFR.QP_LIST_HEADER_ID = QPLV.LIST_HEADER_ID. Consequently, only offers whose source codes are active and whose associated price list header exists in QP_LIST_HEADERS_VL are returned. The view is dependent on the integrity and availability of the QP price list header data, so offers without a corresponding active price list header are silently excluded.

Key Columns

• SOURCE_CODE_ID — primary identifier of the AMS source code record.
• SOURCE_CODE — the user-visible code string used to identify the offer in TeleSales and marketing flows.
• SOURCE_TYPE — aliased from ARC_SOURCE_CODE_FOR; for this view it resolves to 'OFFR'.
• OBJECT_ID — aliased from SOURCE_CODE_FOR_ID; the price list header identifier that ties the source code to AMS_OFFERS and QP_LIST_HEADERS_VL.
• NAME — the description drawn from QP_LIST_HEADERS_VL, typically the offer or price list name.
• STATUS — the offer status code from AMS_OFFERS.
• START_DATE_ACTIVE / END_DATE_ACTIVE — the effective date range of the underlying price list header.
• RELATED_SOURCE_CODE, RELATED_SOURCE_OBJECT, RELATED_SOURCE_ID — identifiers that associate the offer with another source code or object.
• INBOUND_URL, INBOUND_EMAIL_ID, INBOUND_PHONE_NO — columns projected as NULL in the view text; they exist for structural compatibility but carry no data from this definition.

Common Use Cases and Queries

The view is typically used to populate offer selection lists, validate that a source code corresponds to an active offer, and report offer activity for a given effective period. A representative query retrieving active offers with their validity window is:

SELECT source_code, name, status, start_date_active, end_date_active
FROM apps.ast_offer_only_v
WHERE TRUNC(SYSDATE) BETWEEN start_date_active AND end_date_active
ORDER BY name;

Because the underlying price list header is a translated entity, the NAME column reflects the session language. Callers integrating with TeleSales should treat OBJECT_ID as the price list header key and SOURCE_CODE as the business-facing offer code. Queries filtering on START_DATE_ACTIVE and END_DATE_ACTIVE are standard when producing period-based offer reports, and joins back to QP_LIST_HEADERS_VL are unnecessary since the descriptive attributes are already projected.