Search Results pmi_rep_gross_margin_v




Overview
Explore in AI Chat

Overview

PMI_REP_GROSS_MARGIN_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the Process Manufacturing Intelligence (PMI) product family. It exists in both EBS 12.1.1 and 12.2.2 and carries a VALID status in the data dictionary. Its stated purpose is to serve as the data source for the PMI Gross Margin Report, which calculates realized margin on shipped sales orders by comparing revenue actually invoiced against the cost of goods sold for the same order lines.

The view is purely a presentation and calculation layer. It does not store data. Every time it is queried it re-derives revenue, discount, cost of goods sold (COGS), and margin for each qualifying sales detail row, which means results are always current but the view can be expensive to run over large date ranges. Within EBS reporting, it typically sits beneath an Oracle Reports or BI Publisher report definition, and it is often surfaced to external consumers through a custom concurrent program or a PL/SQL wrapper rather than being queried interactively.

Underlying Base Objects

The view is defined over a single documented base object, PMI_REP_SALES_DETAILS_V, from which it selects all of its rows. The margin calculation, however, calls several PL/SQL packages at runtime.

  • PMI_REP_SALES_DETAILS_V — the immediate source view, supplying operating unit, company, organization, calendar identifiers, item, warehouse, period start date, extended price in base currency, charges in base currency, and shipped order quantity.
  • PMI_COMMON_PKG — invoked per row through PMICO_GET_COST(ITEM_ID, WAREHOUSE_SHIPPED_FROM, NULL, PERIOD_START_DATE) to retrieve the period-specific cost of the item from the shipped warehouse. This is the component that makes the view expensive, because the cost function is executed once for every row returned.
  • PMI_SALES_PKG — referenced by the sales detail view lineage, providing the sales and order-shipment logic that populates revenue and quantity.
  • PMI_SECURITY_PKG and HR_SECURITY — supply the organization and responsibility-based access filtering that restricts rows to the operating units and organizations the querying user is permitted to see.
  • GMICUOM — the unit-of-measure conversion package used by the underlying sales and cost logic to normalize quantities and amounts.

Key Columns

  • OPERATING_UNIT_ID — the operating unit that owns the transaction; the primary partitioning dimension for multi-org reporting.
  • SALES_COMPANY / SALES_ORGANIZATION — descriptive company and inventory organization identifiers for the selling entity and the shipping warehouse.
  • YEAR_ID, QUARTER_ID, PERIOD_ID — the accounting calendar identifiers, enabling aggregation by fiscal year, quarter, or period.
  • REVENUE — extended selling price expressed in the base currency, aliased from EXTENDED_PRICE_BASE_CURRENCY.
  • DISCOUNT — charges in base currency, aliased from CHARGES_BASE_CURRENCY, representing reductions to the extended price such as freight, allowances, or discounts.
  • COGS — computed as ORDER_QUANTITY multiplied by the item cost returned by PMI_COMMON_PKG.PMICO_GET_COST for the shipped warehouse and the period start date.
  • MARGIN — computed as REVENUE minus DISCOUNT minus COGS. This is the gross margin at the order-line level, before any allocation of period or overhead costs.

Common Use Cases and Queries

The predominant use is period-over-period gross margin analysis by operating unit, company, or organization. A typical query aggregates margin and applies a margin percentage:

  • SELECT period_id, sales_organization, SUM(revenue) revenue, SUM(discount) discount, SUM(cogs) cogs, SUM(margin) margin FROM apps.pmi_rep_gross_margin_v WHERE period_id = :period GROUP BY period_id, sales_organization;
  • Quarterly roll-ups substituting QUARTER_ID or YEAR_ID for PERIOD_ID, useful for trend reporting and board-level margin analysis.
  • Margin-percentage reporting: SUM(margin)/NULLIF(SUM(revenue),0), commonly used to flag low-margin items or organizations.
  • Comparison of realized margin against standard or planned margin, driven by the same cost function used in the view.
  • Feeding an extract table or data warehouse, where the view is run for a closed period and the results are persisted because of the per-row cost calculation overhead.

Because results depend on PMI_COMMON_PKG.PMICO_GET_COST and on the security packages, the view should always be exercised under a responsibility that has the appropriate inventory organizations and cost visibility. Cost method changes, period-close activities, and item cost updates after the fact will retroactively alter the COGS and MARGIN figures returned, which is a material consideration when reconciling historical reports.