Results for “edw_bim_cmpgns_m_u1”

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

Overview

The table BIM.EDW_BIM_CMPGNS_M is an Enterprise Data Warehouse (EDW) campaign master dimension owned by the BIM schema within Oracle E-Business Suite 12.1.1 and 12.2.2. It is a physical table registered in the FND design data repository, stored in the APPS_TS_ARCHIVE tablespace with a status of VALID. Its purpose is to hold the flattened, denormalized hierarchy of marketing campaigns across up to ten levels (L0 through L10), with level L10 representing the campaign schedule, which is the lowest documented grain. Derived from the heuristic Data Vault classification, this object is best modeled as a hub: it carries a single surrogate primary key (L10_CAMPAIGN_SCHEDULE_PK_KEY) that anchors business keys and is referenced by numerous downstream fact and staging tables. This object is flagged as Oracle Internal Use Only, meaning direct customer access is not supported except through standard Oracle Applications programs.

Key Information Stored

The primary key column is L10_CAMPAIGN_SCHEDULE_PK_KEY, the system-generated unique identifier for the campaign schedule. Two unique indexes define the business-key candidates: EDW_BIM_CMPGNS_M_U1 on the composite of L10_CAMPAIGN_SCHEDULE_PK and L10_CAMPAIGN_SCHEDULE_PK_KEY, and EDW_BIM_CMPGNS_M_U2 on L10_CAMPAIGN_SCHEDULE_PK_KEY alone. Among the 245 columns, the following are the most significant:

• L10_CAMPAIGN_SCHEDULE_PK_KEY, L9_CAMPAIGN_PK_KEY through L1_CAMPAIGN_PK_KEY, L0_ALL_PK_KEY — surrogate identifiers, one per hierarchy level, enabling roll-up joins across campaign tiers.
• L10_CAMPAIGN_SCHEDULE_PK, L1_CAMPAIGN_PK — business-facing unique identifiers for the schedule and campaign levels.
• L10_CAMPAIGN_SCHEDULE_ID, L1_CAMPAIGN_ID — the source-system campaign identity values used in ETL reconciliation.
• L1_CAMPAIGN_NAME, L10_NAME and the parallel L1_NAME…L9_NAME columns — descriptive labels at each level.
• L1_FORECASTED_PLAN_START_DATE, L1_ACTUAL_EXEC_END_DATE, and the equivalent date sets at each level — planning and execution date tracking.
• L10_FREQUENCY and L10_FREQUENCY_UOM_CODE — schedule recurrence and unit of measure.
• L10_DELIVERABLE_ID, L10_ACTIVITY_OFFER_ID — links to deliverable and offer entities.
• SECURITY_GROUP_ID — foreign key to FND_SECURITY_GROUPS enforcing multi-org security.
• L10_LAST_UPDATE_DATE, L10_CREATION_DATE, CREATION_DATE, LAST_UPDATE_DATE — audit columns.

Common Use Cases and Queries

Because the table is a master dimension, typical queries join the surrogate PK to fact tables and filter by security group. A representative pattern aggregates forecast versus actual revenue by campaign level:

SELECT c.L1_CAMPAIGN_NAME, SUM(f.CMPGN_FK_KEY) FROM BIM.EDW_BIM_CMPGNS_M c JOIN BIM.EDW_RVCT_MTH_F f ON f.CMPGN_FK_KEY = c.L10_CAMPAIGN_SCHEDULE_PK_KEY WHERE c.SECURITY_GROUP_ID = :p_security_group GROUP BY c.L1_CAMPAIGN_NAME;

Common scenarios include campaign performance reporting across the ten-level hierarchy, forecast accuracy analysis by comparing forecasted and actual date columns, schedule frequency analysis using L10_FREQUENCY, and discoverer-style reporting that relies on the display columns (L1_CAMPAIGN_DP through L10_CAMPAIGN_SCHEDULE_DP) for unique record identification. ETL reconciliation queries match L1_CAMPAIGN_ID to source systems to validate loaded records.

Related Objects

This hub is referenced by multiple fact and staging tables through the CMPGN_FK_KEY column, and it references the security group lookup:

• BIM.EDW_RVCT_MTH_F — monthly revenue fact, joined on CMPGN_FK_KEY = L10_CAMPAIGN_SCHEDULE_PK_KEY.
• BIM.EDW_RVCT_DLY_F — daily revenue fact, same join key.
• BIM.EDW_CMPFRCST_F — campaign forecast fact.
• BIM.EDW_CMPFRCST_FSTG — campaign forecast staging table.
• BIM.EDW_EVTFRCST_F — event forecast fact.
• BIM.EDW_INTRCTNS_F — interaction fact.
• BIM.EDW_LEADS_F and BIM.EDW_OPRNTIES_F — lead and opportunity facts.
• FND_SECURITY_GROUPS — referenced by SECURITY_GROUP_ID for access control.