Results for “l10_source_code”

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

Overview

The EDW_BIM_CMPGNS_M table is a campaign dimension table owned by the BIM schema (BIM - Marketing Intelligence module) within Oracle E-Business Suite 12.1.1 and 12.2.2. It resides in the enterprise data warehouse layer of the Marketing Intelligence product, providing a denormalized, multi-level view of the campaign hierarchy used for analytical reporting and forecasting. The table contains 245 columns and is structured to support dimensional star-schema querying against fact tables such as revenue, leads, opportunities, interactions, and forecasts.

From a Data Vault modeling perspective, the heuristic classification of this object is a hub. Its surrogate primary key EDW_BIM_CMPGNS_M_PK on L10_CAMPAIGN_SCHEDULE_PK_KEY anchors the grain, while the numerous fact tables referencing it via CMPGN_FK_KEY reinforce its role as a central dimension (hub) from which satellites and links would conceptually branch. This classification is a modeling suggestion; the object is physically implemented as a flat dimension table.

Key Information Stored

The table stores the campaign schedule at its finest grain (level 10), with progressively rolled-up campaign attributes across levels L1 through L10. A 10-level hierarchy is embedded within a single denormalized row, so reporting tools can slice without additional joins.

• L10_CAMPAIGN_SCHEDULE_PK_KEY — surrogate primary key (EDW_BIM_CMPGNS_M_PK), the unique row identifier.
• L10_CAMPAIGN_SCHEDULE_PK — natural/business key for the campaign schedule; also part of unique index EDW_BIM_CMPGNS_M_U1.
• L10_NAME / L10_LEVEL_NAME — display name and hierarchy level label.
• L10_CAMPAIGN_SCHEDULE_ID, L10_SOURCE_CODE — source system identifiers.
• L10_FREQUENCY and L10_FREQUENCY_UOM_CODE — recurrence and unit of measure.
• L10_FORECASTED_START_DATE_TIME / L10_ACTUAL_START_DATE_TIME (and end-date counterparts) — planned vs. actual execution timestamps.
• L10_DELIVERABLE_ID, L10_ACTIVITY_OFFER_ID — links to deliverable and offer entities.
• L1–L9 campaign rollup columns — each level carries PK, DP, NAME, CAMPAIGN_ID, TYPE, rollup, forecast/actual dates, and USER_ATTRIBUTE1–5, enabling family-tree reporting.
• L0_ALL_PK_KEY / L0_ALL_PK / L0_NAME — the root "All Campaigns" anchor.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS for data-level access control.
• CREATION_DATE / LAST_UPDATE_DATE and L10_CREATION_DATE / L10_LAST_UPDATE_DATE — audit columns.

Unique indexes EDW_BIM_CMPGNS_M_U1 (L10_CAMPAIGN_SCHEDULE_PK, L10_CAMPAIGN_SCHEDULE_PK_KEY) and EDW_BIM_CMPGNS_M_U2 (L10_CAMPAIGN_SCHEDULE_PK_KEY) confirm the business-key candidates.

Common Use Cases and Queries

Typical usage revolves around campaign attribution, ROI analysis, and forecast reconciliation. A simple lookup joins a fact table to the dimension on CMPGN_FK_KEY:

• Campaign performance dashboard:
  SELECT c.L1_CAMPAIGN_NAME, c.L10_NAME, SUM(f.amount)
FROM BIM_EDW_RVCT_MTH_F f
JOIN BIM.EDW_BIM_CMPGNS_M c ON f.CMPGN_FK_KEY = c.L10_CAMPAIGN_SCHEDULE_PK_KEY
GROUP BY c.L1_CAMPAIGN_NAME, c.L10_NAME;
• Hierarchy drill-down: group by L1 through L10 names to roll revenue from schedule to top campaign.
• Forecast vs. actual variance: compare L10_FORECASTED_START_DATE_TIME against L10_ACTUAL_START_DATE_TIME across the schedule set.
• Lead and opportunity funnel: join BIM_EDW_LEADS_F and BIM_EDW_OPRNTIES_F on CMPGN_FK_KEY to analyze conversion.
• Security-scoped reporting: filter on SECURITY_GROUP_ID for operating-unit isolation.

Related Objects

The table is referenced by eight documented fact tables through CMPGN_FK_KEY, and it references FND_SECURITY_GROUPS via SECURITY_GROUP_ID.

• BIM_EDW_CMPFRCST_F and BIM_EDW_CMPFRCST_FSTG — campaign forecast fact and staging (CMPGN_FK_KEY).
• BIM_EDW_EVTFRCST_F — event forecast fact.
• BIM_EDW_INTRCTNS_F — interaction fact.
• BIM_EDW_LEADS_F and BIM_EDW_OPRNTIES_F — leads and opportunities facts.
• BIM_EDW_RVCT_DLY_F and BIM_EDW_RVCT_MTH_F — daily and monthly revenue facts.
• FND_SECURITY_GROUPS — referenced parent for row-level security.