Search Results planned_qty




Overview
Explore in AI Chat

Overview

WIP_WS_PTPKPI_AGG_V is a Work in Process (WIP) reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to serve as the MES Production to Plan KPI aggregated View — that is, it consolidates the Production-to-Plan key performance indicator data used by the Manufacturing Execution System (MES) and Work in Process workbench functionality. The view produces a single row per combination of organization, department, work order (WIP entity), operation sequence number, and shift, comparing the quantity that was planned against the quantity that was actually moved, scrapped, or rejected on the shop floor. Because it is a reporting view rather than a base table, it holds no data of its own; it is a query-time aggregation and reconciliation of two supporting objects. The name "AGG" reflects that the parent objects are already aggregated at the shift/operation grain, and this view performs the final full outer join to reconcile planned versus actual figures.

Underlying Base Objects

The ETRM 12.2.2 metadata documents the view as being defined over two referenced objects, both exposed to APPS through synonyms:

  • WIP_WS_PTPKPI_PLAN (synonym) — the planned side of the KPI, aliased PLAN1 in the view text. This object supplies planned quantities, shift identifiers, UOM, operation lead time, and shift start time for each operation.
  • WIP_WS_PTPKPI_ACTUAL (synonym) — the actual execution side, aliased ACTUAL. This object supplies moved, scrapped, and rejected quantities recorded against the same organizational keys.

The two sources are combined with a FULL OUTER JOIN on the composite key ORGANIZATION_ID, DEPARTMENT_ID, WIP_ENTITY_ID, OPERATION_SEQ_NUM, and SHIFT_ID. The full outer join is the defining characteristic of the view: it preserves rows that exist in the plan but have no actual activity, rows that have actual activity but no plan (unplanned production), and rows where both sides exist. The NVL and CASE expressions in the select list are used to substitute zero or fallback values so that downstream KPI calculations do not encounter nulls.

Key Columns

The view exposes thirteen columns. The most significant, particularly given the search term planned_qty, are described below.

  • PLANNED_QTY — the quantity planned for the operation and shift, returned as NVL(PLAN1.PLANNED_QTY, 0). If the plan side is null for a given row (unplanned production), this column returns zero rather than null.
  • MOVED_AGAINST_PLANNED_QTY — the portion of moved quantity that is credited against the plan. It is populated only when the actual and planned shift IDs match and is capped at the planned quantity, ensuring performance against plan never exceeds 100% of the plan.
  • MOVED_AGAINST_UNPLANNED_QTY — the excess moved quantity over and above the plan. Where no plan shift exists, the entire moved quantity is treated as unplanned; otherwise the surplus above PLANNED_QTY is reported here.
  • SCRAPPED_QTY and REJECTED_QTY — quality-related actual quantities, defaulted to zero.
  • ORGANIZATION_ID, DEPARTMENT_ID, WIP_ENTITY_ID, OPERATION_SEQ_NUM, SHIFT_ID — the composite grain of the aggregation.
  • PRIMARY_UOM_CODE, OP_LEAD_TIME, SHIFT_START_TIME — supporting attributes coalesced from the plan or actual side as available.

Common Use Cases and Queries

This view is typically consumed by MES dashboards, Production-to-Plan KPI reports, and shop floor analytics that compare planned output to actual output by department and shift. It is not intended for transactional updates.

A representative query measuring plan attainment for a given organization and shift is:

SELECT organization_id, department_id, wip_entity_id, operation_seq_num, shift_id, planned_qty, moved_against_planned_qty, moved_against_unplanned_qty, scrapped_qty, rejected_qty, primary_uom_code FROM apps.wip_ws_ptpkpi_agg_v WHERE organization_id = :p_org_id AND shift_start_time >= :p_from_date ORDER BY shift_start_time, wip_entity_id, operation_seq_num;

A second common pattern isolates rows where production exceeded the plan, useful for identifying unplanned output:

SELECT wip_entity_id, operation_seq_num, planned_qty, moved_against_unplanned_qty FROM apps.wip_ws_ptpkpi_agg_v WHERE moved_against_unplanned_qty > 0;

Because both source objects are synonyms in the APPS schema, queries should be run with APPS or a reporting responsibility that has been granted select privilege on the view. Access to underlying WIP data remains governed by standard EBS security, so organizations visible to the user are limited by their assigned operating units and inventory organizations.