Results for “bim_cmpgn_revcost_summ”

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

Overview

BIM.BIM_CMPGN_REVCOST_SUMM is a summary table within the Oracle E-Business Suite Marketing Intelligence (BIM) module. Its stated purpose is to hold summarized revenue and cost data aggregated by user-defined period, campaigns, media, and channels. The table is populated by the BIM_CMPGN_RVCST_SUMM_PKG concurrent program, which reads detailed transactional campaign and activity data and consolidates it into this period-bucketed form for faster analytical reporting. It is a reporting-oriented aggregate rather than a transactional base table, so its grain is one row per campaign / media / channel / period combination rather than one row per individual business event.

From a Data Vault modeling perspective, the metadata classifies this object heuristically as standalone (it has no foreign keys to other tables beyond the security group reference), so it is best treated as a satellite-like aggregate rather than a hub or link. It depends conceptually on campaign, media, and channel dimensions, but those dependencies are not enforced through database constraints, which is typical of ETRM summary tables.

Key Information Stored

The table contains 21 documented columns. The most significant fall into three groups:

• Business-key candidates (unique index BIM_CMPGN_REVCOST_SUMM_U1): PERIOD_START_DATE, PERIOD_END_DATE, CAMPAIGN_ID, MEDIA_ID, and CHANNEL_ID. These five columns together define uniqueness and represent the natural grain of the summary.
• Descriptive period attribute: PERIOD_NAME provides a user-friendly label for the user-defined reporting period (e.g., a fiscal or custom accounting period), complementing the explicit start and end dates.
• Measures: FORECASTED_REVENUE, FORECASTED_COST, ACTUAL_COST, INITIATED_REVENUE, and NUM_OF_LEADS hold the aggregated metrics produced by the summarization program. Together they support revenue-versus-cost comparison, margin analysis, and lead-generation reporting.
• Audit / concurrency columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN follow the standard Oracle WHO-column pattern.
• Concurrent program traceability: REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE identify the concurrent request that last populated the row.
• Security: SECURITY_GROUP_ID references FND_SECURITY_GROUPS, enabling multi-organization / operating-unit data segregation.

No separate surrogate primary key is documented beyond the composite unique index, so the business key effectively also serves as the row identifier.

Common Use Cases and Queries

Typical uses include campaign profitability analysis, period-over-period revenue and cost trending, channel and media effectiveness comparison, and lead-to-revenue funnel summaries. Because the data is pre-aggregated, queries are simple and fast.

• Campaign-level rollup: aggregate FORECASTED_REVENUE and ACTUAL_COST by CAMPAIGN_ID filtered on a period range using PERIOD_START_DATE and PERIOD_END_DATE.
• Channel/media performance: group by MEDIA_ID and CHANNEL_ID to compare INITIATED_REVENUE against FORECASTED_COST.
• Lead effectiveness: order by NUM_OF_LEADS descending to identify high-yield campaigns.
• Reprocessing lineage: join REQUEST_ID to FND_CONCURRENT_REQUESTS to trace which run produced a given summary row.
• Security filtering: constrain by SECURITY_GROUP_ID when building multi-org reports.

A representative query might select CAMPAIGN_ID, PERIOD_NAME, FORECASTED_REVENUE, ACTUAL_COST, and NUM_OF_LEADS from this table where the period dates fall within a desired range, ordered by revenue descending.

Related Objects

• BIM_CMPGN_RVCST_SUMM_PKG — the concurrent package that populates and refreshes this table; the primary inbound dependency.
• FND_SECURITY_GROUPS — referenced through SECURITY_GROUP_ID; governs row-level security.
• FND_CONCURRENT_REQUESTS — joined via REQUEST_ID for program-run lineage.
• FND_APPLICATION — resolved through PROGRAM_APPLICATION_ID to identify the owning application.
• FND_CONCURRENT_PROGRAMS — linked via PROGRAM_ID to identify the summary program definition.
• BIM_CMPGN_REVCOST_DETL (detail counterpart) — the transactional source feeding the summarization package.
• BIM campaign, media, and channel dimension tables — resolved through CAMPAIGN_ID, MEDIA_ID, and CHANNEL_ID for descriptive reporting, though not enforced by FK constraints.

Together these objects position BIM_CMPGN_REVCOST_SUMM as the analytical output layer of the Marketing Intelligence revenue-cost reporting pipeline.