Search Results bim_sgmt_act_b_mv




Overview
Explore in AI Chat

Overview

BIM_SGMT_ACT_B_MV is an APPS-owned materialized view registered in Oracle E-Business Suite as a table object within the FND – Application Object Library product family. In the ETRM 12.1.1 inventory it is documented as VALID with twenty columns. The "B_MV" suffix follows the standard Oracle EBS BI/analytics convention: an "MV" object is a materialized view that pre-aggregates data from a base ("B") source, here activity-level segmentation facts derived from Oracle Marketing (AMS) and related CRM schemas. Because materialized views physically store their result set, BIM_SGMT_ACT_B_MV behaves like a denormalized reporting table rather than an OLTP entity, and it supports segmentation and campaign-response analysis without repeatedly scanning high-volume transactional tables.

Under the heuristic Data Vault classification mined from the foreign-key structure, the object is assessed as standalone. As a modeling suggestion, this means it is neither a pure hub, link, nor satellite: it is an aggregation artifact. The absence of a modeled composite relationship to a defining hub confirms that it should be treated as a derived reporting object, refreshed on a schedule, rather than a source-of-truth master table.

Key Information Stored

The view combines segment and activity identifiers with campaign-response and revenue counters. The most significant documented columns are:

  • SEGMENT_ID – the segmentation driver; the only documented foreign key, joining to CSF_TDS_SEGMENTS. This is the primary business-key candidate for constraining the view.
  • ACTIVITY_ID – identifies the marketing activity or campaign the aggregated metrics belong to.
  • SCHEDULE_PURPOSE – classifies the activity's intended purpose within the segment schedule.
  • CATEGORY_ID and SOURCE_CODE_ID – descriptive classification and source attribution for the activity.
  • TRANSACTION_CREATE_DATE – the effective date used for time-bucketing the aggregates.
  • ACTIVITIES_COUNT and COUNT_ALL – row-level counters for activity volume and total records.
  • RESPONSES and RESP_CNT – response tallies for the segment/activity combination.
  • LEADS – lead volumes generated.
  • NEW_OPPORTUNITY_AMT, BOOKED_AMT – opportunity and booked revenue amounts in the functional currency, with their _C counterparts representing converted amounts.
  • NEW_OPPORTUNITY_AMT_S, BOOKED_AMT_S and the corresponding _S_C variants – secondary (statistical or reporting) amount measures.
  • UMARK – a standard EBS row-identification marker used to distinguish records during bulk processing.

The view has no documented single-column primary key exposed in ETRM; SEGMENT_ID is the principal documented join and filtering key.

Common Use Cases and Queries

The view is typically used for segment-performance and campaign-ROI reporting. A typical aggregation query filters by segment and evaluates response and revenue efficiency:

  • Selecting RESPONSES, LEADS, NEW_OPPORTUNITY_AMT, and BOOKED_AMT for a given SEGMENT_ID to compute conversion and return on campaign spend.
  • Grouping by ACTIVITY_ID and TRANSACTION_CREATE_DATE to trend response and booked revenue over time.
  • Comparing NEW_OPPORTUNITY_AMT against BOOKED_AMT to derive pipeline-to-close ratios.
  • Joining to CSF_TDS_SEGMENTS to resolve segment names for dashboards and BI Publisher reports.
  • Using CATEGORY_ID or SOURCE_CODE_ID as filters to analyze which activity categories or sources yield the highest leads.

A representative pattern selects SEGMENT_ID, SUM(RESPONSES) AS total_responses, SUM(LEADS) AS total_leads, and SUM(BOOKED_AMT) AS booked FROM BIM_SGMT_ACT_B_MV GROUP BY SEGMENT_ID. Because the object is a materialized view, freshness depends on its refresh schedule, and reporting should account for stale windows.

Related Objects

The documented foreign key from BIM_SGMT_ACT_B_MV.SEGMENT_ID to CSF_TDS_SEGMENTS is the primary structural relationship, linking aggregated activity metrics to the segmentation definition. Because the object is standalone in the Data Vault sense, further dependencies are indirect: access layers typically join it to CRM segmentation and campaign metadata, while BI Publisher and OBIEE-style reporting models present it alongside the base activity and response tables it aggregates. Consumers should treat the view as a derived convenience object and reconcile amounts against the underlying marketing activity and transaction detail tables when auditing results.