Search Results cogs_amount

Overview

ONT_PRT_CST_MARGIN_SUMMARY_V is a reporting view owned by the APPS schema within the Oracle E-Business Suite Order Management (ONT) product. Its purpose is to expose order-level profitability information by joining order management context (orders, lines, customers, sales representatives, and invoice references) with cost and margin data derived from the Cost Management (CST) schema. The view name carries the "PRT" designation commonly associated with Oracle's Profitability Manager and margin analysis features, indicating that it is intended as a supporting reporting object rather than a transactional entity.

The view presents a flattened, denormalized result set suitable for ad hoc queries, custom reports, Oracle Discoverer worksheets, Oracle Business Intelligence Publisher data models, and interface extracts. Because it is a view rather than a table, it stores no data itself; all content is materialized at query time from the underlying margin summary tables and a controlling build record.

Underlying Base Objects

The documented ETRM metadata lists two referenced base objects, both resolved as synonyms in the APPS schema:

• CST_MARGIN_SUMMARY — the primary fact source supplying order lines, invoice lines, quantities, cost of goods sold, margin amounts, and margin percentages.
• CST_MARGIN_BUILD — the build or run header that identifies a margin collection process instance and its date boundaries.

The view is defined as SELECT ... FROM CST_MARGIN_SUMMARY CMS, CST_MARGIN_BUILD CMB with the join condition CMS.BUILD_ID = CMB.BUILD_ID, restricted further by CMS.GL_DATE >= CMB.TO_DATE - 90 and a subquery that retains only the most recent build (CMS.BUILD_ID = (SELECT MAX(CMS.BUILD_ID) FROM CST_MARGIN_SUMMARY)). This construction ties every returned row to the latest margin build and limits the returned general ledger dates to a trailing ninety-day window, keeping result sets current and manageable.

Key Columns

The view exposes a broad set of identifiers, descriptors, and measures. Significant columns include:

• BUILD_ID, SOURCE, ROW_TYPE, ORIGIN — provenance columns that identify which margin build and process produced the row and how the row should be interpreted.
• ORDER_NUMBER, HEADER_ID, ORDER_TYPE_ID, ORG_ID — sales order header context.
• LINE_ID, LINE_NUMBER, LINE_TYPE_CODE, INVENTORY_ITEM_ID, ORGANIZATION_ID — order line detail, including the item and inventory organization.
• PARENT_LINE_ID, PARENT_INVENTORY_ITEM_ID, PARENT_ITEM_TYPE_CODE, PARENT_ATO_FLAG — parent line and configuration information, supporting ATO and configured-item analysis.
• CUSTOMER_ID, SOLD_TO_CUSTOMER_NAME, SHIP_TO_CUSTOMER_NAME, BILL_TO_CUSTOMER_NAME — customer and site context.
• INVOICE_LINE_QUANTITY, INVOICE_QUANTITY, ORDER_LINE_QUANTITY, SHIP_QUANTITY — the quantity measures that drive margin calculations.
• INVOICED_AMOUNT, COGS_AMOUNT, MARGIN, MARGIN_PCT — the core financial measures, with MARGIN representing revenue less cost of goods sold and MARGIN_PCT the derived margin percentage.
• SALES_ACCOUNT, COGS_ACCOUNT, GL_DATE, ORIGINAL_GL_DATE, CUSTOMER_TRX_ID, CUSTOMER_TRX_LINE_ID — accounting and receivables linkage.
• RMA_HEADER_ID, RMA_NUMBER, RMA_LINE_ID, CR_TRX_ID, CR_TRX_LINE_ID, RETURN_REFERENCE_TYPE_CODE, LINK_TO_LINE_ID — return and credit memo correlation, allowing original and returned business to be analyzed together.
• PRIMARY_SALESREP_ID, SALES_CHANNEL_CODE, TERRITORY_ID, CUSTOMER_CLASS_CODE — sales and segmentation attributes used in margin analysis by representative, channel, or territory.

Common Use Cases and Queries

Typical scenarios include margin analysis by customer, item, sales representative, or channel; reconciliation of invoiced revenue against cost of goods sold; and drill-down from order line to invoice and credit memo. A representative query retrieving the highest-margin lines for the most recent build is:

SELECT order_number, line_number, inventory_item_id,
       sold_to_customer_name, invoiced_amount,
       cogs_amount, margin, margin_pct
  FROM apps.ont_prt_cst_margin_summary_v
 WHERE margin_pct IS NOT NULL
 ORDER BY margin_pct DESC;

Because the view already filters to the latest build and a ninety-day GL window, callers do not need to add build selection logic. Queries intended for historical comparison should instead target CST_MARGIN_SUMMARY directly with an explicit BUILD_ID, since ONT_PRT_CST_MARGIN_SUMMARY_V always resolves to the most recent build. Users should also expect the view to be relatively expensive when queried without predicates, and should constrain by ORDER_NUMBER, CUSTOMER_ID, or GL_DATE where possible.