Search Results arc_source_code_for

Overview

BIM_DIMV_SOURCE_CODES_V is a marketing intelligence view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the BIM (Marketing Intelligence) product family and exposes details for all marketing source codes configured in Oracle Marketing. The view consolidates source code records together with their associated campaign, campaign schedule, media, channel, event, and offer context, producing a single denormalized dimension-style result set suitable for reporting, analytics, and data warehouse loads.

Because the object is a view rather than a table, it holds no data of its own; it derives all output at runtime from the underlying AMS (Advanced Marketing) base objects. This makes it a convenient abstraction layer for BI Publisher reports, Discoverer workbooks, OBIEE repositories, and ad hoc SQL queries that need consistent source-code-to-campaign attribution without replicating the UNION-based logic in every query.

Underlying Base Objects

The documented base objects referenced by the view are:

• AMS_SOURCE_CODES (SYNONYM) — the driving table supplying SOURCE_CODE_ID, SOURCE_CODE, SOURCE_CODE_FOR_ID, ARC_SOURCE_CODE_FOR, and CREATION_DATE.
• AMS_CAMPAIGNS_ALL_B (SYNONYM) — supplies CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, and ARC_CHANNEL_FROM.
• AMS_CAMPAIGN_SCHEDULES (SYNONYM) — joined when the source code is attached at the campaign schedule level (ARC_SOURCE_CODE_FOR = 'CSCH').
• AMS_ACT_OFFERS (SYNONYM) — supplies ACTIVITY_OFFER_ID for the OFFER_ID column in the offer-level branch of the UNION.
• AMS_EVENT_OFFERS_ALL_B (SYNONYM) — supports event offer resolution in the event offer branches.

The view text is composed of three UNION branches keyed on SRC.ARC_SOURCE_CODE_FOR. The first branch ('CAMP') links a source code to a campaign. The second ('CSCH') links a source code to a campaign schedule, and by extension to its parent campaign. The third branch resolves offer-level associations, populating OFFER_ID from AMS_ACT_OFFERS. Throughout, sentinel values of -999 are used to indicate that no value applies for a given level, so report authors must treat -999 as a null placeholder rather than a real identifier.

Key Columns

• SOURCE_CODE_ID — Primary identifier of the marketing source code.
• SOURCE_CODE — The user-visible source code value used in campaign responses.
• CAMPAIGN_SCHEDULE_ID — Schedule to which the code is attached; -999 when the code applies at campaign or offer level.
• CAMPAIGN_ID — Parent campaign identifier derived from AMS_CAMPAIGNS_ALL_B.
• MEDIA_ID — Media associated with the campaign, defaulting to -999 when unassigned.
• CHANNEL_ID — Constructed from ARC_CHANNEL_FROM concatenated with CHANNEL_ID, or the -999 sentinel.
• OFFER_ID — Offer identifier; -999 in campaign and schedule branches, populated via ACTIVITY_OFFER_ID where offers apply.
• EVENT_ID — Prefixed with 'EVEH' when ARC_CHANNEL_FROM indicates an event hierarchy.
• EVENT_OFFER_ID — Prefixed with 'EVEO' when ARC_CHANNEL_FROM indicates an event offer.
• CREATION_DATE — Date the source code record was created.

Common Use Cases and Queries

The view is typically used to report source code performance and attribution, and to reconcile marketing codes back to campaigns, schedules, and offers. A search for "offer_id" commonly reflects a need to isolate rows where an offer is actually attached:

• Listing all source codes with full campaign and channel context for a BI report.
• Filtering to offer-level source codes by testing OFFER_ID <> -999.
• Joining to response or activity tables on SOURCE_CODE_ID to measure campaign effectiveness.

Representative query:

SELECT source_code_id, source_code, campaign_id,
       channel_id, offer_id, creation_date
FROM   apps.bim_dimv_source_codes_v
WHERE  offer_id <> -999
ORDER  BY campaign_id, source_code;

Because the view issues UNION operations across several AMS tables, queries should always filter on selective columns such as CAMPAIGN_ID, SOURCE_CODE, or OFFER_ID, and should explicitly exclude the -999 sentinel where a real relationship is required.