Search Results pmi_prod_sum




Overview
Explore in AI Chat

Overview

PMI_PROD_SUM is a summary table owned by the PMI schema in Oracle E-Business Suite, classified under the Process Manufacturing Intelligence (PMI) product family. Within the ETRM 12.2.2 documentation, PMI is marked Obsolete, meaning the module is no longer actively shipped or enhanced, though the underlying table and its supporting objects frequently remain VALID in existing installations. The table stores pre-aggregated production transaction data derived from OPM (Oracle Process Manufacturing) activity, rolling detailed shop-floor and inventory movements up to the organization, warehouse, item, and accounting-period grain so that reporting and analytics do not need to scan high-volume transaction tables.

The documented physical schema contains 30 columns with a single unique index, PMI_PROD_SUM_PK. Heuristic Data Vault classification mined from the foreign-key structure identifies this object as standalone, with no declared parent-child links. As a modeling suggestion, this positions PMI_PROD_SUM as neither a classic hub nor a link but rather a consolidated satellite-style aggregate: a denormalized snapshot accumulating measures (usage and yield quantities and values) keyed by a composite business key. Because the object is obsolete, it should be treated as a read-only historical or migration source rather than a target for new integration work.

Key Information Stored

The primary key, PMI_PROD_SUM_PK, is a composite business key comprising five columns: FISCAL_YEAR, ORGN_CODE, WHSE_CODE, PERIOD_ID, and ITEM_ID. No separate surrogate key column is documented; the uniqueness of a production-summary row is therefore defined entirely by the combination of fiscal year, organization, warehouse, period, and item. The most significant columns fall into four groups:

Common Use Cases and Queries

The principal use case is period-over-period production reporting by organization, warehouse, and item without traversing OPM transaction history. A typical aggregation query retrieves yield and usage for an organization and item across periods:

  • SELECT period_name, SUM(whse_yield_qty), SUM(whse_usage_qty) FROM pmi.pmi_prod_sum WHERE orgn_code = :orgn AND item_id = :item AND fiscal_year = :yr GROUP BY period_name;
  • SELECT whse_code, SUM(whse_yield_value), SUM(whse_usage_value) FROM pmi.pmi_prod_sum WHERE period_id = :period GROUP BY whse_code;
  • SELECT * FROM pmi.pmi_prod_sum WHERE miss_conv_fact_cnt > 0 AND fiscal_year = :yr; — used to audit conversion gaps before trusting converted quantities.

Because GL_PERIOD_START_DATE and GL_PERIOD_END_DATE are persisted, reports can filter on GL date ranges directly rather than resolving the calendar. Data-quality checks should isolate rows where CONVERTIBLE_UOM is null or MISS_CONV_FACT_CNT is non-zero, since converted measures are unreliable in those cases.

Related Objects

The supplied relationship data classifies PMI_PROD_SUM as standalone, so no explicit foreign keys are documented to parent tables. In practice, the composite key columns resolve to standard OPM and GL master data at query time rather than through enforced constraints:

  • OPM organizations and warehouses: join ORGN_CODE and WHSE_CODE to the OPM organization and warehouse definition tables to obtain validated master attributes.
  • Items: join ITEM_ID to the OPM item master to reconcile ITEM_NO and ITEM_UM with current definitions.
  • GL calendars and periods: join PERIOD_ID or PERIOD_SET_NAME to the accounting calendar definitions to expand period attributes.
  • OPM production and inventory transaction tables: the detailed source records from which these summaries are derived, useful for drill-down reconciliation.
  • PMI reporting views and concurrent programs: the aggregation processes that populate LOG_END_DATE and refresh this table.

Given the Obsolete status of the PMI module, related objects should be verified as VALID in the target instance before relying on them for new development.