Results for “lead_line_pk_key”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BIM_EDW_LEADS_F is a fact table owned by the BIM schema within the Oracle E-Business Suite Marketing Intelligence module. In the documented ETRM metadata, the product classification is listed as "BIM - Marketing Intelligence (Obsolete)," and the object is described as the "Lead fact table." The table is marked VALID and is documented with a physical schema of 56 columns on ETRM 12.2.2, with the same object also referenced in 12.1.1 contexts. Functionally, it serves as the analytical grain store that captures individual lead activity lines, aggregating measurable lead quantities and amounts against a dense set of dimensional foreign keys. This design is typical of an Oracle Business Intelligence / Enterprise Data Warehouse (EDW) star or snowflake model, where the fact table holds numeric measures and surrogate keys point outward to conformed dimension masters.

Based on the foreign-key structure mined heuristically from the metadata, the Data Vault classification of this object is a link. That classification is a modeling suggestion: the table's role in the EDW is to associate multiple dimension entities — leads, campaigns, offers, events, market segments, and channel descriptors — through surrogate FK columns rather than to act as a standalone business-key hub. Practitioners designing or reverse-engineering the BIM EDW may therefore treat BIM_EDW_LEADS_F as an intersection/fact construct linking those dimension masters, with the numeric columns acting as the measured outcome of those associations.

Key Information Stored

At the center of the table is the unique index business-key candidate LEAD_LINE_PK, supported by the unique index BIM_EDW_LEADS_F_U1. The related column LEAD_LINE_PK_KEY appears as the surrogate key reference into the parent master, and LEADLINE_FK_KEY joins back to EDW_BIM_LEADS_M. The most significant columns group as follows:

• Lead and status context: LEADLINE_FK_KEY and LEADSTATUS_FK_KEY point to EDW_BIM_LEADS_M and EDW_BIM_LEAD_STATUS_M respectively, establishing the lead identity and its lifecycle state.
• Campaign and offer context: CMPGN_FK_KEY, CMPGN_STATUS_FK_KEY, and OFFER_FK_KEY link the line to EDW_BIM_CMPGNS_M, EDW_BIM_CMPSTATS_M, and EDW_BIM_OFFERS_M.
• Segmentation and channel: MDCHNL_FK_KEY, MKTSGMT_FK_KEY, TGTSGMT_FK_KEY, and SRCLST_FK_KEY reference the medium channel, market segment, target segment, and source list masters.
• Numeric measures: LEAD_AMOUNT_G, LEAD_AMOUNT_B, and LEAD_AMOUNT_T hold the lead amount in global, base, and transaction currency terms, while LEAD_UNITS records unit volume. USER_MEASURE1 through USER_MEASURE5 provide extensibility slots.
• Flexible attributes: USER_ATTRIBUTE1 through USER_ATTRIBUTE15 and USER_FK1_KEY through USER_FK5_KEY allow site-specific dimensionality without schema change.
• Audit and security: CREATION_DATE, LAST_UPDATE_DATE, and SECURITY_GROUP_ID (which references FND_SECURITY_GROUPS) govern record lifecycle and row-level access.

Common Use Cases and Queries

The table is used for lead-generation analytics: forecasting pipeline value, measuring campaign contribution, and comparing lead volume against segmented audiences. Because the numeric measures are stored in three currency perspectives, OLAP-style rollups can be produced without a currency conversion at query time.

A representative query aggregates lead amounts by campaign while filtering on the current lead status:

SELECT l.CMPGN_FK_KEY, SUM(l.LEAD_AMOUNT_B) AS LEADS_BASE
FROM   BIM.BIM_EDW_LEADS_F l, BIM.EDW_BIM_LEAD_STATUS_M s
WHERE  l.LEADSTATUS_FK_KEY = s.LEADSTATUS_FK_KEY
GROUP BY l.CMPGN_FK_KEY;

Additional patterns include joining to EDW_BIM_MDCHN_M to evaluate channel effectiveness, restricting by SECURITY_GROUP_ID for partitioned access, and using LEAD_UNITS against LEAD_AMOUNT_T to derive average deal size in transaction currency. Reporting layers typically join the foreign-key columns rather than the raw numeric keys.

Related Objects

• EDW_BIM_LEADS_M — joined via LEADLINE_FK_KEY; the lead master dimension.
• EDW_BIM_LEAD_STATUS_M — joined via LEADSTATUS_FK_KEY; lead lifecycle states.
• EDW_BIM_CMPGNS_M — joined via CMPGN_FK_KEY; campaign master.
• EDW_BIM_CMPSTATS_M — joined via CMPGN_STATUS_FK_KEY; campaign status codes.
• EDW_BIM_OFFERS_M — joined via OFFER_FK_KEY; offer definitions.
• EDW_BIM_EVENTS_M — joined via EVENT_FK_KEY; event dimension.
• EDW_BIM_MDCHN_M — joined via MDCHNL_FK_KEY; medium/channel master.
• EDW_BIM_MKTSGMTS_M and EDW_BIM_TGSMT_M — joined via MKTSGMT_FK_KEY and TGTSGMT_FK_KEY; market and target segments.
• EDW_BIM_SRCLSTS_M — joined via SRCLST_FK_KEY; source list dimension.
• FND_SECURITY_GROUPS — joined via SECURITY_GROUP_ID; governs row-level security filtering.