Search Results cmpgn_fk




Overview
Explore in AI Chat

Overview

BIM_EDW_OPRNTIES_FCV is a marketing intelligence view in Oracle EBS that belongs to the BIM (Marketing Intelligence) product family. The suffix "FCV" indicates it is a fact-cache view used to feed the Enterprise Data Warehouse (EDW), normalizing operational opportunity data into a dimensional model keyed by surrogate foreign keys. The view converts raw campaign and lead-line records from Oracle Marketing Online and related modules into EDW-ready rows, where each business key is concatenated with an instance code to form a globally unique foreign key. Each row represents an opportunity line tied to a marketing campaign, source promotion, customer, and resource instance.

In ETRM documentation for 12.2.2, the view carries no documented base objects in the metadata and is marked "Not implemented in this database," indicating it is a delivered deployment artifact rather than an active object in every instance. It is typically created during Marketing Intelligence EDW setup when the dimensional warehouse schema is materialized.

Underlying Base Objects

The ETRM metadata lists no referenced base objects, but the view text reveals the source tables and helper packages. The primary source is a lead-line table aliased ASLL, joined to a lead or source table aliased ASL, a campaign detail table aliased BSCD, and an instance table aliased INST (referenced as INST.INSTANCE_CODE). Because the SELECT statement is provided rather than a formal dependency list, the exact physical table names must be confirmed by inspecting DBA_DEPENDENCIES on the target instance.

Business-key derivation relies heavily on packaged functions that generate surrogate keys at query time:

Key Columns

The column of interest in this search, CMPGN_STATUS_FK, is computed by EDW_BIM_CMPSTATS_PKG.GET_CAMPAIGN_STATUS_FK(ASLL.SOURCE_PROMOTION_ID, INST.INSTANCE_CODE). It serves as the foreign key to the campaign status dimension, allowing reporting on promotion status independent of the source transaction.

Other significant columns include:

Common Use Cases and Queries

The view supports EDW loading and marketing analytics such as campaign status analysis, opportunity pipeline reporting, and segment performance measurement. A typical query filters by campaign status key:

  • SELECT OPPR_LINE_PK, CMPGN_FK, CMPGN_STATUS_FK, CUSTOMER_FK, TRX_CURRENCY_FK FROM BIM_EDW_OPRNTIES_FCV WHERE CMPGN_STATUS_FK IS NOT NULL;
  • SELECT CMPGN_STATUS_FK, COUNT(*) FROM BIM_EDW_OPRNTIES_FCV GROUP BY CMPGN_STATUS_FK;
  • SELECT OPPR_LINE_PK, CMPGN_FK, TIME_FK FROM BIM_EDW_OPRNTIES_FCV WHERE TIME_FK = '01-01-2015-DAY';

Because several columns are computed by PL/SQL package functions, query performance depends on the efficiency of EDW_BIM_CMPSTATS_PKG and related packages. Queries should generally filter on indexed dimensional keys rather than on function-derived columns in large extracts.