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.

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_B grouped by MKTSGMT_FK_KEY to compare segment performance.
  • Channel effectiveness. Join MDCHNL_FK_KEY to EDW_BIM_MDCHN_M and OPPSTATUS_FK_KEY to EDW_BIM_OPP_STATUS_M to isolate won opportunities per channel.
  • Time-series trending. Join TIME_FK_KEY to the warehouse time dimension for month-over-month opportunity movement.
  • Source-list attribution. Use SRCLST_FK_KEY and EVENT_FK_KEY to 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 via CMPGN_FK_KEY.
  • EDW_BIM_CMPSTATS_M – joined via CMPGN_STATUS_FK_KEY.
  • EDW_BIM_EVENTS_M – joined via EVENT_FK_KEY.
  • EDW_BIM_MDCHN_M – joined via MDCHNL_FK_KEY.
  • EDW_BIM_MKTSGMTS_M – joined via MKTSGMT_FK_KEY.
  • EDW_BIM_OPPORTUNITIES_M – joined via OPPLINE_FK_KEY.
  • EDW_BIM_OFFERS_M – joined via OPPLINE_FK_KEY and OFFER_FK_KEY.
  • EDW_BIM_OPP_STATUS_M – joined via OPPSTATUS_FK_KEY.
  • EDW_BIM_SRCLSTS_M and EDW_BIM_TGSMT_M – joined via SRCLST_FK_KEY and TGTSGMT_FK_KEY.
  • FND_SECURITY_GROUPS – governs row visibility through SECURITY_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.