Search Results cst_bis_margin_summary_pk

Overview

CST_BIS_MARGIN_SUMMARY is a reporting table owned by the BOM schema in Oracle E-Business Suite, documented as part of the Bills of Material product family. Its documented description identifies it as a "margin analysis summary table for reporting purposes," indicating that it serves as a pre-aggregated or denormalized data store that supports margin and profitability reporting rather than transactional order entry. The table contains 77 documented columns and is classified as VALID in ETRM 12.2.2, with the same structure expected in the 12.1.1 environment.

The object carries no foreign-key dependencies to transactional child tables and is mined from the FK structure as standalone under the heuristic Data Vault classification. In Data Vault modeling terms, this suggests treating CST_BIS_MARGIN_SUMMARY as a self-contained artifact that behaves most like a reporting satellite or flat summary mart rather than a hub or link. Because its foreign keys point outward to reference and transactional masters (legal entities, order types, RMA lines), it is best modeled as a dependent reporting construct, not a source of business keys.

Key Information Stored

The table stores margin, cost, and revenue metrics at the invoice and order line level, enriched with organizational, customer, and period attributes. The most significant columns are:

• MARGIN_PK — the surrogate primary key, defined by CST_BIS_MARGIN_SUMMARY_PK. It is the only documented unique identifier and is not a business key.
• MARGIN and MARGIN_PCT — the core profitability measures representing absolute margin and margin percentage per summarized row.
• INVOICED_AMOUNT, COGS_AMOUNT, INVOICE_LINE_QUANTITY, and INVOICE_QUANTITY — revenue, cost of goods sold, and invoiced quantity drivers that feed the margin calculation.
• CUSTOMER_TRX_ID and CUSTOMER_TRX_LINE_ID — links to the AR invoice and invoice line from which the sales transaction originates.
• HEADER_ID, LINE_ID, ORDER_NUMBER, and LINE_NUMBER — order header and line references from the sales order flow.
• INVENTORY_ITEM_ID and ORGANIZATION_ID — item and inventory organization context for the summarized line.
• LEGAL_ENTITY_ID — the legal entity under which the transaction is reported.
• PERIOD_NAME_MONTH, PERIOD_NUM_MONTH, PERIOD_NAME_QTR, PERIOD_NUM_QTR, PERIOD_NAME_YEAR, and PERIOD_NUM_YEAR — accounting period dimensions enabling time-series margin reporting at month, quarter, and year grain.
• RMA_HEADER_ID, RMA_LINE_ID, and RETURN_REFERENCE_TYPE_CODE — return merchandise authorization context, distinguishing return-related margin adjustments.
• AREA_CODE, COUNTRY_CODE, REGION_CODE, and their corresponding name columns — geographic summarization dimensions.
• SOURCE, ROW_TYPE, ORIGIN, and INVOICE_SOURCE — classification flags indicating how each row was derived.

Common Use Cases and Queries

The principal use case is margin and profitability analysis by period, customer, item, and geography. Typical queries aggregate MARGIN and MARGIN_PCT across period and organizational dimensions:

• Monthly margin trend by legal entity: SELECT PERIOD_NAME_MONTH, LEGAL_ENTITY_ID, SUM(MARGIN), AVG(MARGIN_PCT) FROM CST_BIS_MARGIN_SUMMARY GROUP BY PERIOD_NAME_MONTH, LEGAL_ENTITY_ID;
• Margin by customer and region: join CUSTOMER_ID and REGION_NAME to produce a customer profitability matrix.
• Return impact analysis: filter on RETURN_REFERENCE_TYPE_CODE to isolate RMA-driven margin erosion.
• Invoice reconciliation: join CUSTOMER_TRX_ID/CUSTOMER_TRX_LINE_ID back to AR to validate summarized INVOICED_AMOUNT against posted invoice lines.

Because the table is a summary construct, it is well suited to BI Publisher reports and Oracle Discoverer/Essbase extracts without touching high-volume transactional tables.

Related Objects

The documented FK relationships identify the following significant related objects:

• FV_LEGAL_ENTITIES — joined via LEGAL_ENTITY_ID for legal entity reporting context.
• SO_ORDER_TYPES_115_ALL — joined via ORDER_TYPE_ID to classify order types in margin reporting.
• JAI_OM_OE_RMA_LINES — joined via RMA_LINE_ID for return line detail.

Additional dependencies are implied through the business columns rather than declared FKs, including RA_CUSTOMER_TRX_ALL/RA_CUSTOMER_TRX_LINES_ALL (via CUSTOMER_TRX_ID and CUSTOMER_TRX_LINE_ID), OE_ORDER_HEADERS_ALL/OE_ORDER_LINES_ALL (via HEADER_ID and LINE_ID), MTL_SYSTEM_ITEMS_B (via INVENTORY_ITEM_ID), and the GL period tables underlying the PERIOD_* columns.