Results for “booked_amt_s_c”

50+ results




Overview
Explore in AI Chat

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

Overview

BIM_SGMT_VAL_B_MV is an APPS-owned materialized view registered as a table object within the FND – Application Object Library product in Oracle E-Business Suite 12.1.1 and 12.2.2. The object resides in the APPS schema with VALID status and exposes an 18-column physical schema. Its name and column profile indicate it is a segment value summary or aggregation view, most likely maintained by the Business Intelligence Management (BIM) subsystem or an Oracle Marketing segmentation feature, which consolidates customer cohort and revenue metrics by segment. Because the "_MV" suffix denotes a materialized view, the object is a physically stored snapshot of data derived from one or more base tables, refreshed either on demand or on a schedule. This design makes it suitable for high-volume analytical queries without incurring the join cost of the underlying transactional sources.

The ETRM metadata assigns a heuristic Data Vault classification of "standalone," meaning the object neither references nor is referenced by other tables through composite hub-link structures. In Data Vault modeling terms, this object behaves most like a satellite, capturing descriptive and measuring attributes related to a segment. The single documented foreign key, SEGMENT_ID referencing CSF_TDS_SEGMENTS, anchors the view to a segment dimension, which supports interpreting BIM_SGMT_VAL_B_MV as a satellite keyed on that segment identifier.

Key Information Stored

The columns fall into three logical groups. SEGMENT_ID is the business-key candidate and the sole documented foreign key, linking each row to a segment definition in CSF_TDS_SEGMENTS. SEGEMENT_SIZE_C appears to be a corrected or alternate spelling of a segment size column, while SEGMENT_SIZE records the population count of that segment. TRANSACTION_CREATE_DATE provides the time dimension, enabling trend and period comparisons. UMARK is a standard EBS change-tracking column.

The remaining columns are quantitative measures. ACQUIRED_CUSTOMERS, TOTAL_CUSTOMERS, and LOST_CUSTOMERS capture cohort movement, while COUNT_ALL provides a row or record count. BOOKED_AMT, BOOKED_AMT_C, BOOKED_AMT_S, and BOOKED_AMT_S_C hold booked amounts in base currency, currency, and secondary-currency variants. REVENUE, REVENUE_C, REVENUE_S, and REVENUE_S_C mirror the same currency treatment for revenue. The paired "_C" and "_S" suffixes indicate currency conversion and secondary-ledger equivalents. No surrogate primary key column is documented in the ETRM excerpt, so uniqueness likely derives from the combination of SEGMENT_ID and TRANSACTION_CREATE_DATE.

Common Use Cases and Queries

Typical usage centers on segmentation analytics, cohort retention reporting, and revenue attribution by segment. A representative query aggregates revenue and booked amounts by segment over a date range:

  • SELECT segment_id, transaction_create_date, SUM(booked_amt), SUM(revenue) FROM apps.bim_sgmt_val_b_mv WHERE transaction_create_date BETWEEN :from_date AND :to_date GROUP BY segment_id, transaction_create_date;
  • Joining to CSF_TDS_SEGMENTS to obtain segment descriptive attributes: SELECT m.segment_id, s.segment_name, m.total_customers, m.revenue FROM apps.bim_sgmt_val_b_mv m, apps.csf_tds_segments s WHERE m.segment_id = s.segment_id;
  • Cohort movement analysis comparing acquired and lost customers: SELECT segment_id, SUM(acquired_customers) - SUM(lost_customers) net_change FROM apps.bim_sgmt_val_b_mv GROUP BY segment_id;

These patterns support dashboards, customer-lifetime-value studies, and financial reconciliation between base and secondary currency amounts.

Related Objects

  • CSF_TDS_SEGMENTS — the only documented foreign-key parent, joined on SEGMENT_ID.
  • The base tables underlying the materialized view (undocumented in the excerpt) that supply the pre-aggregated segment and currency measures.
  • FND currency and GL-related tables supplying the "_C" and "_S" conversion rates.
  • Oracle Marketing or Trade Management segment definitions that populate CSF_TDS_SEGMENTS.
  • BI Publisher and Discoverer reports built on the view for segment performance reporting.

Because refresh scheduling governs data currency, developers should verify the materialized view's refresh interval before relying on it for real-time reporting.