Search Results ship_to_site_fk




Overview
Explore in AI Chat

Overview

BIM_EDW_LEADS_FCV is a foreign-key (FK) resolution view belonging to the Oracle E-Business Suite BIM - Marketing Intelligence product family. In the ETRM documentation the module is flagged as Obsolete and the view is explicitly recorded as "Not implemented in this database." The physical object is therefore a data-warehouse staging artifact intended to feed an external Enterprise Data Warehouse (EDW) or business-intelligence layer rather than a transactional reporting view consumed directly by Oracle EBS forms.

The view presents marketing sales-lead data in a star-schema-ready form. Its purpose is surrogate-key generation: rather than returning raw operational identifiers, it emits composite _FK columns of the form <business_value>-<INSTANCE_CODE>, resolving natural business keys into conformed EDW dimension keys. Because the user's search term is cmpgn_status_fk, the view is of interest primarily to anyone inspecting how campaign-status dimensionality is derived for lead-fact loading.

Underlying Base Objects

ETRM records no documented base objects for this view, so the definition must be inferred from the embedded SQL text. The principal sources are:

Several dimension keys are not table-derived at all but are computed on the fly by PL/SQL package functions: EDW_BIM_CMPSTATS_PKG.GET_CAMPAIGN_STATUS_FK, EDW_BIM_MKTSGMTS_PKG.GET_MKTSGMT_FK, EDW_BIM_TRGTSGMTS_PKG.GET_TRGTSGMT_FK, EDW_BIM_SRCLISTS_PKG.GET_SRCLIST_FK, EDW_ITEMS_PKG.GET_ITEM_FK, and the utility routines EDW_UTIL.GET_EDW_BASE_UOM and EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT. The view thus depends on these packages remaining valid in the database.

Key Columns

Common Use Cases and Queries

The view is typically queried during EDW extract validation, FK-resolution troubleshooting, or impact analysis on the campaign-status package. Because the object is marked not implemented, queries on a 12.1.1/12.2.2 instance will normally fail with ORA-00942 unless a custom copy exists.

To inspect campaign-status resolution for a lead:

  • SELECT LEAD_LINE_PK, CMPGN_FK, CMPGN_STATUS_FK, EVENT_FK, CUSTOMER_FK FROM BIM_EDW_LEADS_FCV WHERE CMPGN_STATUS_FK = 'NA_EDW';
  • SELECT CMPGN_STATUS_FK, COUNT(*) FROM BIM_EDW_LEADS_FCV GROUP BY CMPGN_STATUS_FK;

To verify the view exists before reliance, query ALL_VIEWS or DBA_OBJECTS for the object name, and confirm package validity for EDW_BIM_CMPSTATS_PKG in ALL_OBJECTS.