Search Results na_edw

Overview

BIM_EDW_CMPFRCST_FCV is a view within the Oracle E-Business Suite Marketing Intelligence (BIM) module, designed to expose campaign forecast and actual cost data in a denormalized form suitable for loading into an Enterprise Data Warehouse (EDW). The view serves as an integration layer between the operational Marketing Intelligence tables and the dimensional schema used by the EDW, projecting surrogate key components, foreign key concatenations, and metric measures into a single flattened record set. In Oracle EBS 12.1.1 and 12.2.2, this view is typically referenced by ETL processes that populate campaign forecasting fact tables, providing a stable interface that decouples downstream reporting from the underlying transactional model.

The view is documented in ETRM metadata but is marked "Not implemented in this database," indicating that in the queried environment the object is not instantiated, though its definition remains part of the product's data model. Its naming convention — the _FCV suffix — identifies it as a fact collection view, a common pattern in Marketing Intelligence EDW views where a primary key is synthesized by concatenating component identifiers and joining to a shared INSTANCE_CODE dimension.

Underlying Base Objects

The documented metadata does not enumerate referenced base objects, but the view text reveals its principal sources through column aliases. The core aliases are AMC, AAM, AMT, and INST, which correspond to campaign, activity metric, metric parent, and instance records respectively. In the standard Marketing Intelligence schema these map to campaign and activity metric tables, with the INSTANCE_CODE derived from the AMS instance dimension that isolates data by source instance.

The definition also invokes the package function EDW_BIM_MEDIA_PKG.GET_MEDIA_CHANNEL_FK, which resolves a media channel foreign key from media, channel, and instance inputs. Because no base objects are documented, administrators should treat the view definition itself as the authoritative reference and verify dependency chains against the AMS/BIM schema after any patching in 12.1.1 or 12.2.2.

Key Columns

• CMPFRCST_PK — Synthetic primary key formed from campaign ID, activity metric ID, and instance code.
• CMPGN_FK — Campaign foreign key combined with instance code and the suffix "-CMP".
• CMPGN_STATUS_FK — User status identifier joined to instance code.
• EVENT_FK / MEDCHNL_FK — Conditional foreign keys resolved through DECODE logic, defaulting to the literal "NA_EDW" when no value applies.
• TIME_FK — Derived from the actual execution start date formatted as DD-MM-YYYY with a "-DAY" suffix.
• TRX_CURRENCY_FK — Transaction currency code carried directly from the activity metric.
• CMPGN_ACTUAL_COST_T / CMPGN_FRCST_COST_T — Transaction-currency actual and forecasted cost measures.
• ROLLUP_METRIC_ID — Parent metric identifier supporting hierarchical rollup.
• LAST_CALCULATED_DATE — Timestamp of the most recent metric calculation.
• USER_MEASURE1–5 / USER_FK1–5 — Placeholder columns reserved for extension, all NULL in the delivered definition.

Notably, BASE_CURRENCY_FK, MKTSGMT_FK, TGTSGMT_FK, SRCLST_FK, CUSTOMER_FK, ITEM_FK, UOM_FK, BILL_TO_SITE_FK, and SHIP_TO_SITE_FK are all hardcoded to "NA_EDW," affirming that campaign forecasting in this context does not dimension across those attributes.

Common Use Cases and Queries

The view is primarily consumed by ETL jobs loading campaign forecast facts. A representative query selecting cost measures by campaign is:

SELECT CMPGN_FK,
       CAMPAIGN_ID,
       ROLLUP_METRIC_ID,
       CMPGN_ACTUAL_COST_T,
       CMPGN_FRCST_COST_T,
       LAST_CALCULATED_DATE
  FROM BIM_EDW_CMPFRCST_FCV
 WHERE TRX_CURRENCY_FK IS NOT NULL;

Analysts auditing metric freshness use LAST_CALCULATED_DATE to detect stale forecasts, while rollup reconciliations group by ROLLUP_METRIC_ID to validate parent-child totals. The "NA_EDW" sentinel in many foreign key columns signals to warehouse consumers that a conformed dimension member must be substituted during load, a standard practice in Marketing Intelligence EDW integration.