Results for “l1_vendor_fk_key”

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

Overview

The table BIM.EDW_BIM_SRCLSTS_M is a source list dimension table within the Oracle E-Business Suite Marketing Intelligence (BIM) module. It stores descriptive and hierarchical attributes associated with source lists — the marketing and lead-generation constructs that categorize the origin of leads, opportunities, interactions, and revenue events. In Oracle EBS 12.1.1 and 12.2.2, this table serves as a conformed dimension in the Marketing Intelligence data warehouse, enabling consistent slicing of fact data by source list across all downstream analytics.

Heuristic Data Vault modeling suggests classifying EDW_BIM_SRCLSTS_M as a hub. Its role as a central reference of source list business keys, referenced by seven fact tables through their SRCLST_FK_KEY columns, is consistent with hub semantics. The primary key EDW_BIM_SRCLSTS_M_PK on L1_SRCLST_PK_KEY anchors that hub identity.

Key Information Stored

The table contains 37 documented columns. The most significant are summarized below.

• L1_SRCLST_PK_KEY — surrogate primary key and unique index (EDW_BIM_SRCLSTS_M_U1, EDW_BIM_SRCLSTS_M_U2); the fact tables join on this value via their SRCLST_FK_KEY columns.
• L1_SRCLST_PK — business key of the source list; together with L1_SRCLST_PK_KEY it forms the EDW_BIM_SRCLSTS_M_U1 unique index, distinguishing surrogate from business-key identity.
• L0_ALL_PK_KEY, L0_ALL_PK, L0_NAME — higher-level rollup ("L0") keys and name, enabling aggregation above the source-list grain.
• L1_SRCLST_DP, L1_NAME — display descriptor and descriptive name of the source list.
• L1_IMPORT_LIST_HEADER_ID, L1_LIST_SOURCE_TYPE_ID, L1_LIST_SOURCE_TYPE — the import list header and source-type classification.
• L1_USER_STATUS_ID, L1_STATUS_CODE, L1_STATUS_DATE — status attributes and the date the status was applied.
• L1_VENDOR_ID, L1_VENDOR_FK, L1_VENDOR_FK_KEY — vendor association for purchased lists.
• L1_TRANSACTIONAL_COST, L1_TRANSACTIONAL_CURRENCY_CODE, L1_FUNCTIONAL_COST, L1_FUNCTIONAL_CURRENCY_CODE — cost and currency attributes for list acquisition.
• L1_SOURCE_LIST_NAME, L1_DESCRIPTION, L1_KEYWORDS — descriptive and search metadata.
• SECURITY_GROUP_ID, L1_ORG_ID — multi-org and security-group scoping.
• L1_LAST_UPDATE_DATE, L1_CREATION_DATE, LAST_UPDATE_DATE, CREATION_DATE — audit timestamps.
• L1_USER_ATTRIBUTE1 through L1_USER_ATTRIBUTE5, L1_SOURCE_SYSTEM, L1_INSTANCE — extensibility and source provenance.

Common Use Cases and Queries

Typical usage joins this dimension to the fact tables on SRCLST_FK_KEY to slice marketing and revenue metrics by source list, status, vendor, or cost.

SELECT f.LEAD_ID, d.L1_SOURCE_LIST_NAME, d.L1_STATUS_CODE
FROM   BIM.EDW_LEADS_F f, BIM.EDW_BIM_SRCLSTS_M d
WHERE  f.SRCLST_FK_KEY = d.L1_SRCLST_PK_KEY
AND    d.L1_STATUS_CODE = 'ACTIVE';

Reporting scenarios include source-list performance dashboards, vendor cost analysis (using transactional and functional cost columns), attribution of opportunities and interactions to originating lists, and revenue rollups using the monthly and daily fact tables. Security-group and org filters are typically applied via SECURITY_GROUP_ID and L1_ORG_ID. Analysts also use the L0 rollup columns to report above the source-list grain.

Related Objects

The dimension is referenced by seven BIM fact tables, all joining on SRCLST_FK_KEY to L1_SRCLST_PK_KEY:

• BIM.EDW_LEADS_F — lead facts by source list.
• BIM.EDW_OPRNTIES_F — opportunity facts by source list.
• BIM.EDW_INTRCTNS_F — interaction facts by source list.
• BIM.EDW_EVTFRCST_F — event forecast facts by source list.
• BIM.EDW_CMPFRCST_F — campaign forecast facts by source list.
• BIM.EDW_RVCT_DLY_F — daily revenue facts by source list.
• BIM.EDW_RVCT_MTH_F — monthly revenue facts by source list.

Additionally, EDW_BIM_SRCLSTS_M.SECURITY_GROUP_ID references FND_SECURITY_GROUPS, tying the dimension to Oracle's security infrastructure.