Results for “oppline_fk_key”
16 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
BIM_EDW_OPRNTIES_F is the Opportunity fact table within the BIM – Marketing Intelligence product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It resides in the BIM schema and holds the transactional grain of the marketing data warehouse, capturing one row per opportunity line together with the full set of dimensional foreign keys needed to slice that measure across campaign, channel, market, product, customer, and time. As a fact table, it is the central quantitative object against which the surrounding BIM dimension and master tables (EDW_BIM_*_M) are joined for analytical reporting.
The ETRM metadata classifies this object heuristically as a link in Data Vault terms. In practice, the object is a classic star-schema fact table: it carries the composite business key and the numeric measures, while the foreign keys act as references into conformed dimensions. A Data Vault modeler would treat the opportunity line as the grain at which the hub references and the measure-bearing satellite are linked, with the many _FK_KEY columns acting as link-to-hub relationships rather than denormalized attributes.
Key Information Stored
The table contains 56 documented columns. The most operationally significant are the following.
- OPPR_LINE_PK – the business key of the opportunity line, enforced by the unique index
BIM_EDW_OPRNTIES_F_U1. - OPPR_LINE_PK_KEY – the surrogate primary key column used for warehouse joins and for the self-referencing FK entries in the metadata.
- OPPSTATUS_FK_KEY – points to
EDW_BIM_OPP_STATUS_M, allowing open, won, and lost opportunity analysis. - CMPGN_FK_KEY / CMPGN_STATUS_FK_KEY – link to
EDW_BIM_CMPGNS_MandEDW_BIM_CMPSTATS_Mfor campaign attribution and campaign lifecycle status. - EVENT_FK_KEY – references
EDW_BIM_EVENTS_Mfor seminar, trade show, and event-driven opportunities. - MDCHNL_FK_KEY – maps to
EDW_BIM_MDCHN_M, the marketing channel dimension. - MKTSGMT_FK_KEY / TGTSGMT_FK_KEY – market segment and target segment keys (
EDW_BIM_MKTSGMTS_M,EDW_BIM_TGSMT_M). - SRCLST_FK_KEY – source list reference into
EDW_BIM_SRCLSTS_M. - OFFER_FK_KEY / OPPLINE_FK_KEY – references to
EDW_BIM_OFFERS_MandEDW_BIM_OPPORTUNITIES_M. - CUSTOMER_FK_KEY, ITEM_FK_KEY, UOM_FK_KEY, ORG_FK_KEY, SLSCHNL_FK_KEY – party, product, unit of measure, operating unit, and sales channel references.
- OPPR_AMOUNT_G / OPPR_AMOUNT_B / OPPR_AMOUNT_T – opportunity amount in global, base, and transaction currency respectively.
- OPPR_UNITS – the unit quantity associated with the opportunity line.
- TIME_FK_KEY – reference to the warehouse time dimension.
- USER_MEASURE1 through USER_MEASURE5 and USER_FK1_KEY through USER_FK5_KEY – extension slots for customer-specific measures and dimensions.
- SECURITY_GROUP_ID – Multi-Org / security group partitioning, with a documented reference to
FND_SECURITY_GROUPS.
The remaining USER_ATTRIBUTE1 through USER_ATTRIBUTE15 columns, plus CREATION_DATE and LAST_UPDATE_DATE, provide descriptive extension capacity, audit stamps, and loader metadata.
Common Use Cases and Queries
Typical usage centers on opportunity pipeline, campaign ROI, and segmentation reporting.
- Pipeline by campaign status.
SELECT c.cmpgn_name, COUNT(*) FROM bim.bim_edw_oprnties_f f JOIN edw_bim_cmpstats_m s ON f.cmpgn_status_fk_key = s.cmpgn_status_fk_key JOIN edw_bim_cmpgns_m c ON f.cmpgn_fk_key = c.cmpgn_fk_key GROUP BY c.cmpgn_name; - Revenue by market segment. Aggregate
OPPR_AMOUNT_Bgrouped byMKTSGMT_FK_KEYto compare segment performance. - Channel effectiveness. Join
MDCHNL_FK_KEYtoEDW_BIM_MDCHN_MandOPPSTATUS_FK_KEYtoEDW_BIM_OPP_STATUS_Mto isolate won opportunities per channel. - Time-series trending. Join
TIME_FK_KEYto the warehouse time dimension for month-over-month opportunity movement. - Source-list attribution. Use
SRCLST_FK_KEYandEVENT_FK_KEYto measure lead-source and event-driven contribution.
Because SECURITY_GROUP_ID partitions data, every query should be filtered by the active security group in a Multi-Org-enabled environment.
Related Objects
EDW_BIM_CMPGNS_M– joined viaCMPGN_FK_KEY.EDW_BIM_CMPSTATS_M– joined viaCMPGN_STATUS_FK_KEY.EDW_BIM_EVENTS_M– joined viaEVENT_FK_KEY.EDW_BIM_MDCHN_M– joined viaMDCHNL_FK_KEY.EDW_BIM_MKTSGMTS_M– joined viaMKTSGMT_FK_KEY.EDW_BIM_OPPORTUNITIES_M– joined viaOPPLINE_FK_KEY.EDW_BIM_OFFERS_M– joined viaOPPLINE_FK_KEYandOFFER_FK_KEY.EDW_BIM_OPP_STATUS_M– joined viaOPPSTATUS_FK_KEY.EDW_BIM_SRCLSTS_MandEDW_BIM_TGSMT_M– joined viaSRCLST_FK_KEYandTGTSGMT_FK_KEY.FND_SECURITY_GROUPS– governs row visibility throughSECURITY_GROUP_ID.
These relationships define the primary join paths for BIM Marketing Intelligence reporting and should be preserved when building custom extracts or OBIEE repository layers.
-
Opportunity fact table .
-
Opportunity fact table .
-
Interface table for opportunity fact .
-
View: BIM_EDW_OPRNTIES_F_IV 12.1.1
Instead of Views
Not implemented in this database·Explore BIM module →
-
View: BIM_EDW_OPRNTIES_F_IV 12.2.2
Instead of Views
Not implemented in this database·Explore BIM module →
-
eTRM - BIM Tables and Views 12.1.1
Target segment level table .
-
eTRM - BIM Tables and Views 12.2.2
Target segment level table .