Search Results bim_source_code_details_u1

Overview

BIM.BIM_SOURCE_CODE_DETAILS is a reference table in the Oracle E-Business Suite Business Intelligence (BIM) marketing analytics module. Its purpose is to link marketing source codes to the core marketing dimensions tracked by the warehouse: campaigns, media, channels, offers, events, and event offerings. Each row associates a single marketing source code with the dimensional keys that describe how and where that source was used, allowing downstream BI reporting and attribution analysis to resolve a source code into its full marketing context.

The table is owned by the BIM schema, carries the FND design data reference BIM.BIM_SOURCE_CODE_DETAILS, and is reported as VALID in the ETRM catalog. It resides in the APPS_TS_ARCHIVE tablespace with a PCT FREE of 10. Records are populated by the BIM_SRC_CODE_DETAILS_PKG concurrent program, which means the table is effectively a materialized dimensional bridge refreshed during the ETL cycle rather than a user-maintained transactional entity. From a Data Vault modeling perspective, the mined relationships suggest the table behaves as a link, connecting source codes to multiple marketing dimension hubs; it is not modeled as a pure hub or satellite, though this classification is a heuristic recommendation rather than a documented declaration.

Key Information Stored

The table contains 20 documented columns. The most significant are summarized below.

• SOURCE_CODE_ID — Numeric surrogate identifier for the marketing source code. This is the primary key (BIM_SOURCE_CODE_DETAILS_PK) and is also the single column of the unique index BIM_SOURCE_CODE_DETAILS_U1, making it the business-key candidate as well as the technical key.
• SOURCE_CODE — The human-readable marketing source code, up to 30 characters. This is the value most commonly exposed in reports and extracts.
• SOURCE_CODE_CREATION_DATE — Date the source code was originally created, useful for aging and lifecycle analysis.
• CAMPAIGN_ID — Campaign identifier, with the sentinel value -999 indicating no campaign. Resolved through the view BIM_DIMV_CAMPAIGNS.
• CAMPAIGN_SCHEDULE_ID — Foreign key to AMS_CAMPAIGN_SCHEDULES, tying the source code to a specific campaign schedule.
• MEDIA_ID — Marketing media identifier, with -999 for unknown; resolved through BIM_DIMV_MEDIA.
• CHANNEL_ID — Character-based channel identifier stored as VARCHAR2(80). Channel prefixes distinguish channel types: CHLS for media channels, EVEH for events, and EVEO for event offerings. The value -999 denotes unknown. Resolved through BIM_DIMV_CHANNELS.
• OFFER_ID — Marketing offer identifier, -999 if unknown or not applicable; resolved through BIM_DIMV_OFFERS.
• EVENT_ID and EVENT_OFFER_ID — Event and event-offering identifiers. EVENT_OFFER_ID is a foreign key to AMS_EVENT_OFFERS_ALL_B.
• SECURITY_GROUP_ID — Foreign key to FND_SECURITY_GROUPS, supporting Oracle EBS multi-org and data-security filtering.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Standard concurrent program audit columns identifying which request last touched the row and when.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard WHO audit columns maintained by the EBS framework.

Common Use Cases and Queries

The principal use case is marketing attribution: determining which source codes map to which campaign, channel, media, and offer so that response and revenue data can be sliced by marketing dimension. A typical query resolves a source code string to its dimensional keys:

• SELECT source_code, campaign_id, media_id, channel_id, offer_id FROM bim.bim_source_code_details WHERE source_code = :p_source_code;
• Filtering out sentinel values is a common requirement, since -999 is used in place of NULL for many dimensional keys: WHERE campaign_id <> -999.
• Reporting by channel type can exploit the documented prefix convention: WHERE channel_id LIKE 'EVE%' isolates event-related channels.
• ETL monitoring queries join on REQUEST_ID and PROGRAM_UPDATE_DATE to verify the last successful run of BIM_SRC_CODE_DETAILS_PKG.
• Security-aware extracts apply a predicate on SECURITY_GROUP_ID to restrict rows to the appropriate operating unit or responsibility group.

Related Objects

• BIM_DIMV_CAMPAIGNS — Dimension view used to resolve CAMPAIGN_ID into campaign attributes.
• BIM_DIMV_MEDIA — Dimension view resolving MEDIA_ID.
• BIM_DIMV_CHANNELS — Dimension view resolving CHANNEL_ID, including the CHLS/EVEH/EVEO prefix interpretation.
• BIM_DIMV_OFFERS — Dimension view resolving OFFER_ID.
• AMS_CAMPAIGN_SCHEDULES — Referenced by the CAMPAIGN_SCHEDULE_ID foreign key.
• AMS_EVENT_OFFERS_ALL_B — Referenced by the EVENT_OFFER_ID foreign key.
• FND_SECURITY_GROUPS — Referenced by SECURITY_GROUP_ID for row-level security.
• BIM_SRC_CODE_DETAILS_PKG — Concurrent program package that populates and maintains the table during the ETL cycle.