Search Results cst_margin_temp_pk

Overview

CST_MARGIN_TEMP is a table owned by the BOM schema within Oracle E-Business Suite, classified under the Bills of Material product family. Per the Oracle ETRM documentation, it is described as a "Margin analysis summary table for reporting purposes." Despite its name suggesting a temporary (GTT-style) construct, the object is documented as a persistent, VALID table in both Oracle EBS 12.1.1 and 12.2.2, and is referenced through a formal primary key constraint, CST_MARGIN_TEMP_PK. The table consolidates order, invoice, and cost-of-goods-sold information to support margin reporting and analysis, drawing together shipping, billing, returns (RMA), and receivables data into a single denormalized reporting structure.

From a Data Vault modeling perspective, the mined foreign key structure suggests this table behaves as a link entity. It records associations between sales orders (SO_HEADERS_ALL, SO_LINES_ALL), receivables transactions (RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL), order types, legal entities, and RMA lines, rather than acting purely as a descriptive satellite. Analysts should treat it as a transient or staging-style aggregate populated for a given build rather than a durable transaction-of-record table.

Key Information Stored

The documented schema contains 76 columns in the 12.1.1 model. The most operationally significant are summarized below.

• BUILD_ID — the surrogate primary key defined by CST_MARGIN_TEMP_PK; it scopes each reporting build/population run.
• HEADER_ID, LINE_ID, PARENT_LINE_ID — foreign keys tying rows to SO_HEADERS_ALL and SO_LINES_ALL, capturing the order header, detail line, and its parent (e.g., configuration) line.
• CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID — foreign keys to RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL, linking margin data to the specific receivable transaction and line.
• INVENTORY_ITEM_ID, ORGANIZATION_ID, PARENT_INVENTORY_ITEM_ID, PARENT_ORGANIZATION_ID — the item and organization context for both the entry and its parent configuration.
• INVOICED_AMOUNT, COGS_AMOUNT, MARGIN, MARGIN_PCT — the core financial measures: revenue recognized, cost of goods sold, computed gross margin, and margin percentage.
• SALES_ACCOUNT, COGS_ACCOUNT — the general ledger accounts attributed to the margin calculation.
• ORIGINAL_GL_DATE, GL_DATE, and the period columns (PERIOD_NAME_YEAR, PERIOD_NAME_QTR, PERIOD_NAME_MONTH and their numeric/sequence counterparts) — the date and period buckets enabling time-based rollups.
• LEGAL_ENTITY_ID, ORDER_TYPE_ID, SALES_CHANNEL_CODE, CUSTOMER_ID, SOLD_TO_CUSTOMER_NAME — organizational and customer dimensions for slicing margin by entity, channel, order type, and customer.
• RMA_HEADER_ID, RMA_LINE_ID, RMA_NUMBER, RETURN_REFERENCE_TYPE_CODE, LINK_TO_LINE_ID — return-merchandise-authorization context to support credit and return-related margin adjustments.

Business-key candidates for uniqueness center on the combination of BUILD_ID plus the order/invoice identifiers (for example, HEADER_ID, LINE_ID, CUSTOMER_TRX_LINE_ID), although the documented primary key remains the surrogate BUILD_ID.

Common Use Cases and Queries

The principal use case is gross margin reporting by period, customer, item, channel, or legal entity. A typical aggregation groups the financial measures across period buckets:

• Summarize margin by month: SELECT period_name_month, SUM(invoiced_amount), SUM(cogs_amount), SUM(margin) FROM bom.cst_margin_temp WHERE build_id = :p_build GROUP BY period_name_month;
• Margin by customer class or sales channel to evaluate profitability segmentation.
• Reconcile invoiced amounts against RA_CUSTOMER_TRX_LINES_ALL for a given CUSTOMER_TRX_LINE_ID, and against SO_LINES_ALL for order-side quantity and pricing checks.
• Return/RMA impact analysis by joining RMA_LINE_ID to JAI_OM_OE_RMA_LINES to isolate credit-driven margin erosion.
• Item-level margin trends using INVENTORY_ITEM_ID and ORGANIZATION_ID across PERIOD_SEQ markers for ranking or exception reporting.

Queries should always constrain on BUILD_ID, since the table is populated in discrete builds and may retain multiple generations of data.

Related Objects

• SO_HEADERS_ALL — joined via HEADER_ID.
• SO_LINES_ALL — joined via LINE_ID and PARENT_LINE_ID.
• RA_CUSTOMER_TRX_ALL — joined via CUSTOMER_TRX_ID.
• RA_CUSTOMER_TRX_LINES_ALL — joined via CUSTOMER_TRX_LINE_ID.
• FV_LEGAL_ENTITIES — joined via LEGAL_ENTITY_ID.
• SO_ORDER_TYPES_115_ALL — joined via ORDER_TYPE_ID.
• JAI_OM_OE_RMA_LINES — joined via RMA_LINE_ID for return analysis.

These relationships confirm CST_MARGIN_TEMP as a cross-functional reporting bridge linking order management, receivables, and cost accounting data within the Oracle EBS margin analysis process.