Search Results leadstatus_fk

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:

• ASLL — the sales-lead line table (aliased SALES_LEAD_LINE_ID, SOURCE_PROMOTION_ID, OFFER_ID, BUDGET_AMOUNT, UOM_CODE, INVENTORY_ITEM_ID, interest-code columns).
• ASL — the parent sales-lead header (CUSTOMER_ID, STATUS_CODE, CHANNEL_CODE, CURRENCY_CODE, ADDRESS_ID, CREATION_DATE).
• BSCD — a marketing campaign/event staging source supplying CAMPAIGN_FK, EVENT_FK and MEDIA_CHANNEL_FK.
• INST — an instance-registry source providing INSTANCE_CODE, the discriminator appended to every key.

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

• LEAD_LINE_PK — composite primary key concatenating SALES_LEAD_LINE_ID and INSTANCE_CODE.
• CMPGN_STATUS_FK — the column of interest. It is generated by EDW_BIM_CMPSTATS_PKG.GET_CAMPAIGN_STATUS_FK, taking ASLL.SOURCE_PROMOTION_ID and the instance code to resolve the campaign-status dimension surrogate key.
• CMPGN_FK, EVENT_FK, MEDCHNL_FK — campaign, event and media-channel dimension keys sourced from BSCD.
• MKTSGMT_FK, TGTSGMT_FK, SRCLST_FK — market-segment, target-segment and source-list keys derived from customer/date/instance combinations.
• OFFER_FK, LEADLINE_FK, LEADSTATUS_FK, CUSTOMER_FK, ITEM_FK, UOM_FK, SLSCHNL_FK — additional conformed dimensions. Empty values are normalized to the sentinel NA_EDW.
• TIME_FK — derived as TO_CHAR(CREATION_DATE,'DD-MM-YYYY')||'-DAY'.
• LEAD_AMOUNT_G / LEAD_AMOUNT_B — global and base currency lead amounts, the latter returned as NULL.

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.