WIP Required Components – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Detailed project WIP report that lists discrete jobs and required components. The parameter ‘Show Shortage List’ can be used to show a shortage list of existing discrete jobs (similar to Oracle’s ‘Discrete Job Shortage Report’.  

Report Parameters

Organization Code, Scheduled Start Date From, Scheduled Start Date To, Job, Job Status, Date Required to, Show Shortage List, Project, Assembly, Component, Show Phantom Components, MRP Net

Oracle EBS Tables Used

org_organization_definitions, wip_entities, mtl_reservations, mtl_sales_orders, wip_discrete_jobs, wip_lines, mtl_system_items_vl, bom_departments, wip_schedule_groups, pa_projects_all, wip_requirement_operations, bom_components_b, mtl_units_of_measure_tl, mtl_item_locations_kfv, mtl_planners, per_people_x, mtl_onhand_quantities_detail, mtl_secondary_inventories, org_access_view

Report Categories

Enginatics

Related Reports

WIP Discrete Job Shortage, WIP Entities, CAC Receiving Value (Period-End), GL Account Analysis

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource	Link
Excel Example Output	WIP Required Components 17-Nov-2020 002450.xlsx
Blitz Report™ XML Import	WIP_Required_Components.xml
Full SQL on Enginatics	www.enginatics.com/reports/wip-required-components/

Case Study & Technical Analysis: WIP Required Components Report

Executive Summary

The WIP Required Components report is a critical manufacturing planning and shortage analysis tool for Oracle Work in Process (WIP). It provides a detailed listing of all components required for discrete jobs, along with their on-hand quantities and any potential shortages. This report is indispensable for production planners, material managers, and shop floor supervisors to manage material availability, identify component shortages proactively, ensure timely order fulfillment, and prevent production delays, thereby optimizing manufacturing efficiency and inventory utilization.

Business Challenge

Ensuring that all required components are available at the right time and place for manufacturing jobs is a fundamental challenge in production planning. Organizations often face significant hurdles:

• Material Shortages: Unexpected component shortages can halt production lines, leading to missed delivery dates, increased costs (e.g., expediting fees), and customer dissatisfaction. Identifying these shortages proactively is crucial.
• Lack of Component Visibility: While Bill of Materials (BOM) define components, getting a consolidated view of all required components for all active jobs, combined with their current inventory status, is difficult with standard Oracle forms.
• Inefficient Shortage Identification: Manually comparing required components against on-hand inventory for numerous jobs is a time-consuming and error-prone process, making it difficult to prioritize material procurement efforts.
• Impact of Phantom Assemblies: Phantom components (assemblies that are consumed and never stocked) add another layer of complexity, as their sub-components must also be tracked and available.
• Integration with Planning Systems: Reconciling material needs from WIP with recommendations from Material Requirements Planning (MRP) systems is essential for robust material planning.

The Solution

This report offers a powerful, detailed, and actionable solution for managing required components and identifying shortages in WIP, enhancing production planning and material control.

• Comprehensive Component Listing: It provides a detailed list of all components required for discrete jobs, including item details, quantities required, and their current on-hand availability.
• Proactive Shortage Identification: The Show Shortage List parameter is a key feature, transforming the report into a powerful tool that explicitly flags components for which there is insufficient on-hand quantity to meet job demand, similar to Oracle’s standard ‘Discrete Job Shortage Report’.
• Visibility into Phantom Components: The Show Phantom Components parameter allows users to “explode” phantom assemblies within the BOM, revealing the lowest-level components actually needed, which is critical for accurate material planning.
• Integrated Inventory Data: By linking directly to on-hand inventory, reservations, and sales orders, the report provides a real-time picture of material availability against current production demand.
• Supports Project Manufacturing: The inclusion of Project and Task parameters enables material planning and shortage analysis for project-specific manufacturing jobs.

Technical Architecture (High Level)

The report queries core Oracle Work in Process, Inventory, and Bills of Material tables to identify required components and assess their availability.

• Primary Tables Involved:
  • wip_discrete_jobs and wip_entities (for WIP job details).
  • wip_requirement_operations (the central table defining components required for a job’s operations).
  • mtl_system_items_vl (for item master details of both assemblies and components).
  • bom_components_b (for Bill of Material structure, used to explode phantoms).
  • mtl_onhand_quantities_detail (for current on-hand inventory balances).
  • mtl_reservations and mtl_sales_orders (to account for reserved inventory or sales order demand).
  • pa_projects_all (for project context).
• Logical Relationships: The report starts with wip_discrete_jobs to identify active manufacturing jobs. It then links to wip_requirement_operations to find the required components for each job. For each component, it queries mtl_onhand_quantities_detail to get the current inventory. The Show Shortage List logic performs a calculation to determine if (Required Quantity - On-Hand Quantity) > 0. The Show Phantom Components parameter involves recursive joins through bom_components_b to identify components of phantom assemblies.

Parameters & Filtering

The report offers an extensive set of parameters for precise filtering and detailed data inclusion:

• Organizational Context: Organization Code filters the report to a specific manufacturing organization.
• Job and Assembly Identification: Job, Assembly, Component allow for granular targeting of specific production orders or items.
• Date Ranges: Scheduled Start Date From/To and Date Required to are crucial for analyzing material needs for jobs scheduled within specific periods or requiring components by a certain date.
• Status and Shortage Flags: Job Status and Show Shortage List are vital for focusing on active or problematic jobs and directly identifying material shortfalls.
• BOM/Planning Options: Show Phantom Components and MRP Net (if applicable) provide advanced control over how the BOM is exploded and how inventory is considered in the shortage calculation.
• Project Filter: Project allows for focusing on jobs linked to a particular project.

Performance & Optimization

As a detailed transactional report integrating data across multiple modules (WIP, Inventory, BOM), it is optimized through strong filtering and efficient joining strategies.

• Parameter-Driven Efficiency: The use of Organization Code, Scheduled Start Date ranges, Job, Assembly, and Component filters is critical for performance, allowing the database to efficiently narrow down the large transactional datasets to relevant WIP jobs and their components using existing indexes.
• Conditional Shortage Calculation: The Show Shortage List parameter triggers the shortage calculation only when explicitly requested, preventing unnecessary processing for a full component list.
• Indexed Joins: Queries leverage standard Oracle indexes on wip_entity_id, organization_id, inventory_item_id, component_item_id, and project_id for efficient data retrieval across WIP, Inventory, BOM, and Projects tables.

FAQ

1. What is the significance of the ‘Date Required to’ parameter? The Date Required to parameter allows you to specify a cutoff date. The report will then identify shortages for all components that are required by or before that date. This is crucial for prioritizing material procurement and expediting efforts based on immediate production needs.

2. How does the report calculate the ‘Shortage List’? The report calculates a shortage by comparing the Quantity Required for a component on a WIP job against its currently Available On-Hand Quantity (which may also factor in existing reservations or future supply, depending on the MRP Net parameter). If Quantity Required > Available On-Hand, a shortage is identified and typically flagged with the deficit amount.

3. Can this report help identify components that are needed for multiple jobs? Yes. By running the report for a broad range of Jobs and then analyzing the Component column, users can identify common components that are required across multiple production orders. This insight is valuable for consolidating material procurement or identifying high-demand components that might become bottlenecks.

---

Useful Links

• Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
• Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
• Oracle EBS Reporting Toolkits by Blitz Report™
• Blitz Report™ FAQ & Community Q&A
• Supply Chain Hub by Blitz Report™
• Blitz Report™ Customer Case Studies
• Oracle EBS Reporting Blog
• Oracle EBS Reporting Resource Centre

© 2026 Enginatics