Search Results slschnl_fk

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:

• EDW_BIM_CMPSTATS_PKG.GET_CAMPAIGN_STATUS_FK — derives CMPGN_STATUS_FK from the source promotion ID and instance code.
• EDW_BIM_MKTSGMTS_PKG.GET_MKTSGMT_FK — derives the marketing segment key from creation date, customer, and instance.
• EDW_BIM_TRGTSGMTS_PKG.GET_TRGTSGMT_FK — derives the target segment key.
• EDW_BIM_SRCLISTS_PKG.GET_SRCLIST_FK — derives the source list key.
• EDW_ITEMS_PKG.GET_ITEM_FK and EDW_UTIL.GET_EDW_BASE_UOM — resolve item and unit-of-measure dimensional keys.
• EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT — converts transactional amounts to the warehouse currency basis.

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:

• OPPR_LINE_PK — the primary key, formed as LEAD_LINE_ID concatenated with INSTANCE_CODE.
• CMPGN_FK — foreign key to the campaign dimension, sourced from BSCD.CAMPAIGN_FK.
• EVENT_FK and MEDCHNL_FK — keys to the event and media channel dimensions.
• MKTSGMT_FK, TGTSGMT_FK, SRCLST_FK — segment and source-list dimensional keys.
• OFFER_FK and OPPLINE_FK — offer and opportunity line keys, defaulting to 'NA_EDW' when the offer ID is null.
• OPPSTATUS_FK — status key built from ASL.STATUS and the instance code.
• CUSTOMER_FK, BILL_TO_SITE_FK, SHIP_TO_SITE_FK — party and site dimensional keys.
• TIME_FK — date key formatted DD-MM-YYYY with a '-DAY' suffix.
• ORG_FK, INSTANCE_FK, ITEM_FK, UOM_FK, SLSCHNL_FK, BASE_CURRENCY_FK, TRX_CURRENCY_FK — organizational, instance, product, channel, and currency keys.

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.