Search Results cst_margin_summary




Overview
Explore in AI Chat

Overview

The CST_MARGIN_SUMMARY table resides in the BOM (Bills of Material) schema and serves as a margin analysis summary table for reporting purposes within Oracle E-Business Suite releases 12.1.1 and 12.2.2. Its primary function is to consolidate order, invoice, cost of goods sold (COGS), and margin data into a denormalized reporting structure that supports profitability analysis across sales orders, invoices, and returns. The table is classified as VALID in the ETRM metadata and contains 76 documented columns on the 12.2.2 schema.

From a Data Vault modeling perspective, the metadata heuristic classifies this table as standalone. This suggests the table is best modeled as an independent reporting construct rather than as a traditional hub, link, or satellite. Its structure combines descriptive attributes (customer names, period information, geography) with transactional measures (quantities, amounts, margin), placing it closer in nature to a derived reporting table or a wide satellite spanning multiple business processes. Because it stores no natural business key that is unique across contexts in the documented metadata, it should not be treated as a canonical hub.

Key Information Stored

The table captures a rich set of margin-relevant attributes. Among the most significant columns are:

No dedicated surrogate primary key is documented; HEADER_ID or LINE_ID combinations act as functional identifiers for joining purposes. The Data Vault classification as standalone reflects the absence of a single enforced unique business key.

Common Use Cases and Queries

The table supports margin and profitability reporting without runtime recalculation of COGS and revenue. Typical queries aggregate margin by customer, item, geography, or period:

  • Margin by item and period: SELECT inventory_item_id, period_name_month, SUM(margin), AVG(margin_pct) FROM cst_margin_summary GROUP BY inventory_item_id, period_name_month;
  • Customer profitability: filter by sold_to_customer_name or customer_id and sum invoiced_amount against cogs_amount.
  • Return impact analysis: restrict to rows where rma_line_id IS NOT NULL to measure return-driven margin dilution.
  • Geographic roll-up: group by region_name, country_name, or area_name for regional profitability dashboards.

Because the table retains ORDER_LINE_QUANTITY, SHIP_QUANTITY, and INVOICE_QUANTITY, it also supports volume-versus-value variance reporting.

Related Objects

The documented foreign key relationships identify several dependent and referenced objects:

  • FV_LEGAL_ENTITIES – joined via CST_MARGIN_SUMMARY.LEGAL_ENTITY_ID = FV_LEGAL_ENTITIES.LEGAL_ENTITY_ID for legal entity context.
  • SO_ORDER_TYPES_115_ALL – joined via ORDER_TYPE_ID to classify order types.
  • JAI_OM_OE_RMA_LINES – referenced by RMA_LINE_ID for return authorization details.
  • OE_ORDER_HEADERS_ALL / OE_ORDER_LINES_ALL – logically related through HEADER_ID and LINE_ID for source order detail.
  • RA_CUSTOMER_TRX_ALL / RA_CUSTOMER_TRX_LINES_ALL – related through CUSTOMER_TRX_ID and CUSTOMER_TRX_LINE_ID for invoice verification.
  • MTL_SYSTEM_ITEMS_B – joined via INVENTORY_ITEM_ID and ORGANIZATION_ID for item attributes.

These relationships make CST_MARGIN_SUMMARY a central reporting hub for cost and margin analytics spanning order management, receivables, and inventory.