Search Results pmi_rep_prod_usg_varance_v




Overview
Explore in AI Chat

Overview

PMI_REP_PROD_USG_VARANCE_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, defined within the Process Manufacturing Intelligence (PMI) product family. It is documented as the "PMI Product Usage Variance Report view" and is classified as a VALID object in both EBS 12.1.1 and 12.2.2. The view is designed to present the variance between planned and actual product usage across a defined enterprise and calendar hierarchy, aggregating quantities by company, organization, and time period dimensions.

The view serves as a reporting and integration layer rather than a transactional entity. It exposes consolidated usage variance figures that underpin the Product Usage Variance report, allowing planners and cost analysts to compare planned consumption against actual consumption at a granular period level. Its definition restricts output to a single line type, which indicates the view logically partitions report data and this particular view retrieves only the usage variance segment.

Underlying Base Objects

The view is defined with the following SQL text:

Accordingly, its primary referenced base object is the view PMI_REP_PROD_ACTUAL_PLAN_V, which supplies the consolidated actual and plan quantities. The filter LINE_TYPE = -1 selects the subset of rows relevant to product usage variance, separating this view from other reporting views built on the same parent.

Documented references also include several supporting PL/SQL packages: FND_PROFILE, GMICUOM, and PMI_SECURITY_PKG. FND_PROFILE is used for profile option resolution, commonly to determine operating unit or reporting context. GMICUOM is the unit-of-measure conversion package, indicating that quantities may be normalized across units of measure before being exposed. PMI_SECURITY_PKG enforces organization-level security, ensuring that only organizations accessible to the querying user are returned. These packages are not direct data sources but are invoked through the view's dependency chain to control access and quantity interpretation.

Key Columns

  • COMPANY_ID — Identifier of the company (legal entity or reporting company) to which the variance row belongs.
  • ORGANIZATION_ID — The inventory or process organization in which the product usage occurred; subject to PMI_SECURITY_PKG filtering.
  • YEAR_ID — Calendar year identifier used for annual aggregation of variance data.
  • QUARTER_ID — Calendar quarter identifier for quarterly reporting.
  • PERIOD_ID — The accounting or reporting period, providing the finest calendar granularity exposed by the view.
  • ACTUAL_QTY — The actual quantity of product consumed or used, the column frequently targeted by searches for "actual_qty".
  • PLAN_QTY — The planned or expected product quantity against which the actual is compared.

Variance is not exposed as a separate column; it is derived by subtracting PLAN_QTY from ACTUAL_QTY in the consuming query or report.

Common Use Cases and Queries

Typical scenarios include period-end variance analysis, plan-versus-actual reporting for process manufacturing, and feeding downstream BI extracts. Because security is enforced at the view level, direct queries return only authorized organizations without additional filtering by the caller.

A representative query retrieving actual usage variance for a specific organization and period is:

  • SELECT organization_id, year_id, period_id, actual_qty, plan_qty, (actual_qty - plan_qty) AS usage_variance FROM apps.pmi_rep_prod_usg_varance_v WHERE organization_id = 101 AND period_id = 202401 ORDER BY organization_id, period_id;

For aggregate reporting across the calendar hierarchy, an analyst might group by year and quarter:

  • SELECT year_id, quarter_id, SUM(actual_qty) AS total_actual, SUM(plan_qty) AS total_plan, SUM(actual_qty - plan_qty) AS total_variance FROM apps.pmi_rep_prod_usg_varance_v GROUP BY year_id, quarter_id ORDER BY year_id, quarter_id;

Because ACTUAL_QTY and PLAN_QTY are exposed directly, these queries map cleanly to the underlying PMI_REP_PROD_ACTUAL_PLAN_V view and require no joins to dimension or fact tables, making the object suitable for lightweight reporting and data extraction within Process Manufacturing Intelligence.