Search Results cst_bis_margin_analysis_v




Overview
Explore in AI Chat

Overview

The view CST_BIS_MARGIN_ANALYSIS_V is an APPS-owned database view in Oracle E-Business Suite 12.1.1 and 12.2.2, delivered under the Bills of Material (BOM) product family in support of Cost Management margin analysis. Its purpose is to expose pre-aggregated gross margin information — invoiced quantities, shipped quantities, invoiced revenue amounts, and cost of goods sold — enriched with descriptive dimensions such as category, item, operating unit, legal entity, customer, sales channel, territory, and time period. The view is the reporting-facing layer of the CST_BIS (Business Intelligence System) margin analysis schema, sitting on top of the summary and build staging objects that perform the underlying aggregation.

The _V suffix denotes a view used primarily for reporting, ad hoc query, and downstream integration rather than transactional data entry. Because it resolves surrogate keys and lookup codes into human-readable values, it is well suited as a source for BI Publisher reports, Discoverer workbooks, and custom margin dashboards.

Underlying Base Objects

The documented ETRM metadata lists the referenced objects as: AR_LOOKUPS (VIEW), CST_BIS_MARGIN_BUILD (SYNONYM), CST_BIS_MARGIN_SUMMARY (SYNONYM), FND_GLOBAL (PACKAGE), HZ_CUST_ACCOUNTS (SYNONYM), HZ_PARTIES (SYNONYM), MTL_CATEGORIES_KFV (SYNONYM), MTL_PARAMETERS (SYNONYM), MTL_SYSTEM_ITEMS (SYNONYM), MTL_SYSTEM_ITEMS_KFV (SYNONYM), RA_SALESREPS_ALL (SYNONYM), RA_TERRITORIES (SYNONYM), and SO_LOOKUPS (SYNONYM).

The core of the view is the pair CST_BIS_MARGIN_SUMMARY and CST_BIS_MARGIN_BUILD. The summary object holds the aggregated margin metrics keyed by parent organization, parent inventory item, period, and organizational context, while the build object supplies legal entity information. The view joins these to MTL_PARAMETERS to resolve the master organization, MTL_SYSTEM_ITEMS and its key flexfield variant MTL_SYSTEM_ITEMS_KFV for item descriptions and concatenated segment values, and MTL_CATEGORIES_KFV for category concatenated segments. Customer context is obtained through HZ_CUST_ACCOUNTS and HZ_PARTIES; sales representative and territory through RA_SALESREPS_ALL and RA_TERRITORIES; sales channel through SO_LOOKUPS; and customer class through AR_LOOKUPS. All such lookup joins use the outer-join operator, allowing transactions without a resolvable code to still appear in the result set.

Key Columns

The view returns a wide, denormalized result set. Category and item identification are exposed as NVL(MCV.CONCATENATED_SEGMENTS,'UNASSIGNED') and MSIV.CONCATENATED_SEGMENTS, with MSI.PRIMARY_UOM_CODE providing the unit of measure. Organizational and financial dimensions include CMB.LEGAL_ENTITY_NAME and the operating unit name, both defaulted to 'UNASSIGNED' when absent. Transactional context is captured by TEMP.ORDER_NUMBER, TEMP.PARENT_LINE_NUMBER, and TEMP.GL_DATE.

Descriptive attributes include sales channel (SL.MEANING), customer (HP.PARTY_NAME), sales representative (RSA.NAME), customer class (AL.MEANING), territory (RT.NAME), and geographic rollups for AREA_NAME, COUNTRY_NAME, and REGION_NAME. Time intelligence is substantially exposed: PERIOD_NAME_YEAR, PERIOD_NAME_QTR, PERIOD_NAME_MONTH, the corresponding numeric period counters (PERIOD_NUM_YEAR, PERIOD_NUM_QTR, PERIOD_NUM_MONTH), and quarter/month sequence values (PERIOD_SEQ_QTR, PERIOD_SEQ_MONTH) used for trend ordering. Two DECODE-derived labels build literal 'QUARTER n' and 'MONTH n' captions.

The quantitative measures are INVOICE_QUANTITY, SHIP_QUANTITY, INVOICED_AMOUNT, and COGS_AMOUNT, all wrapped in NVL(...,0) to guarantee numeric results. Gross margin is not stored and must be derived as invoiced amount minus COGS.

Common Use Cases and Queries

Typical applications include margin-by-item reporting, margin-by-customer or territory, channel profitability analysis, and period-over-period trending using the sequence columns.

  • Margin by item: SELECT item, SUM(invoiced_amount) rev, SUM(cogs_amount) cogs, SUM(invoiced_amount)-SUM(cogs_amount) margin FROM cst_bis_margin_analysis_v GROUP BY item;
  • Period trending using PERIOD_SEQ_MONTH for ordered month-over-month comparison.
  • Customer and territory rollups filtering on legal entity or operating unit.
  • Channel and class analysis joining or filtering on sales channel and customer class labels.

Because the view performs many outer joins and NVL substitutions, queries should filter early on period and organization to limit the aggregation cost, and margin percentages should guard against zero revenue.