Results for “l1_status_code”

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

Overview

EDW_BIM_LEAD_STATUS_M is a dimension table owned by the BIM schema (Marketing Intelligence) within Oracle EBS 12.1.1 and 12.2.2. It stores lead status reference data used by the Marketing Intelligence analytics model, holding the descriptive attributes of lead status codes so that fact tables can join to a compact surrogate key. The table carries a documented status of VALID, but the BIM product line is flagged as Obsolete in the ETRM metadata; organizations running later releases should treat this object as legacy and verify whether it is still populated or actively referenced by their ETL before building dependencies on it.

Based on the mined foreign key structure, the heuristic Data Vault classification for this table is hub-leaning. In modeling terms, it functions as a dimension (conformed lookup) rather than a transactional hub, because the fact tables reference it through a single surrogate key column. The metadata records one outbound reference to FND_SECURITY_GROUPS via SECURITY_GROUP_ID, which is consistent with standard multi-org and security-group partitioning used across EBS BI/EDW objects.

Key Information Stored

The table contains 21 documented columns. The most significant are:

• L1_LEADSTATUS_PK_KEY — surrogate primary key, enforced by EDW_BIM_LEAD_STATUS_M_PK and reinforced by unique indexes EDW_BIM_LEAD_STATUS_M_U1 and EDW_BIM_LEAD_STATUS_M_U2.
• L1_LEADSTATUS_PK — the business-key identifier for the lead status; a candidate unique key documented in EDW_BIM_LEAD_STATUS_M_U1.
• L1_LEADSTATUS_DP — the descriptive/display value associated with the lead status.
• L1_NAME — the name of the lead status.
• L1_STATUS_CODE — the operational status code used in source systems.
• L1_MEANING — the semantic meaning of the status code.
• L1_DESCRIPTION — the long description of the status.
• L1_INSTANCE — the instance or source-system identifier for the row.
• L1_USER_ATTRIBUTE1 through L1_USER_ATTRIBUTE5 — five extensibility columns for customer-specific attributes.
• L1_LAST_UPDATE_DATE and L1_CREATION_DATE — change-tracking timestamps within the L1 layer.
• SECURITY_GROUP_ID — the security-group/operating-unit partition, joined to FND_SECURITY_GROUPS.
• LAST_UPDATE_DATE and CREATION_DATE — table-level audit timestamps.
• L0_ALL_PK_KEY, L0_ALL_PK, and L0_NAME — the L0 (out-of-the-box) origin keys carried forward into the L1 layer.

Common Use Cases and Queries

This dimension is typically joined from the fact tables BIM_EDW_LEADS_F, BIM_EDW_RVCT_MTH_F, and BIM_EDW_RVCT_DLY_F on the LEADSTATUS_FK_KEY column. A standard enrichment query joins the fact to the dimension on the surrogate key:

• SELECT f.*, s.L1_NAME, s.L1_MEANING FROM BIM_EDW_LEADS_F f JOIN EDW_BIM_LEAD_STATUS_M s ON f.LEADSTATUS_FK_KEY = s.L1_LEADSTATUS_PK_KEY;
• Lead conversion and funnel reporting grouped by L1_STATUS_CODE or L1_MEANING.
• Month-over-month lead pipeline analysis using BIM_EDW_RVCT_MTH_F joined on LEADSTATUS_FK_KEY.
• Daily lead velocity reporting using BIM_EDW_RVCT_DLY_F joined on LEADSTATUS_FK_KEY.
• Security-group filtered extracts using SECURITY_GROUP_ID = :p_security_group_id.
• Refresh-incremental extraction filtered on L1_LAST_UPDATE_DATE.

Related Objects

• BIM_EDW_LEADS_F — joins via LEADSTATUS_FK_KEY to L1_LEADSTATUS_PK_KEY.
• BIM_EDW_RVCT_MTH_F — monthly lead/revenue fact; joins via LEADSTATUS_FK_KEY.
• BIM_EDW_RVCT_DLY_F — daily lead/revenue fact; joins via LEADSTATUS_FK_KEY.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID.
• EDW_BIM_LEAD_STATUS_M_PK / _U1 / _U2 — primary key and unique indexes enforcing the surrogate and business keys.