WIP Job Upload – Oracle EBS SQL Report

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

Overview

Upload to create and update WIP Discrete Jobs (Standard and Non-Standard) including operations, resources, and material requirements (components). Multiple inventory organizations can be combined in one upload file; the upload submits a separate WIP Mass Load run per organization.

Job headers support all standard fields including assembly, class code, status, quantities, dates, scheduling, BOM/routing references (non-standard jobs), completion subinventory/locator, schedule group, line, project/task, and descriptive flexfield attributes.

Operations, resources, and material requirements (components) can be added on new and existing jobs and modified on existing jobs. A single upload row can carry header, operation/resource, and component fields simultaneously, so compact entry does not require separate row types.

Explode Bom and Routing. By default, Bom and Routing are automatically exploded only for new jobs (create) where the job does not have detail entries (operations/resources/components) included in the upload. This is to preserve the manually entered details on upload. If uploading Job Details for a new job, then all the detail entries for the Job should be included in the upload. The Bom and Routing are not automatically re-exploded when updating an existing job. There is a hidden ‘Explode Bom and Routing’ column in the upload that can be used to override this default behaviour.

Scheduled Start and Completion Dates. For jobs with a routing (the assembly’s own routing for Standard jobs, or a Routing Reference for Non-Standard jobs), supply one of Scheduled Start Date or Scheduled Completion Date — providing the start forward-schedules, providing the completion backward-schedules. The system computes the other end from the routing lead times. If both are supplied, forward-scheduling from the start is used. For jobs without a routing, both dates are required and are stored verbatim. The same rules apply on update: changing only the start triggers a forward reschedule, changing only the completion triggers a backward reschedule.

Job creation supports both automatic job name generation (via the WIP Job Prefix profile and sequence) and manual job naming. The Upload Job Identifier column uniquely identifies a job in the upload and provides traceability between uploaded rows and the eventual job name when automatic naming is used. It must be unique per logical job within a single upload. For manual job naming, the Upload Job Identifier will also be used as the Job Name if a Job Name is not specifically entered in the upload.

Non-standard jobs without an assembly or routing reference cannot be updated through WIP Mass Load; such jobs must be edited in the Discrete Jobs form directly. The upload reports a clear error if attempted.

Processing uses the WIP Mass Load interface (wip_job_schedule_interface and wip_job_dtls_interface) with the WICMLP concurrent program. Validation errors are detected per job and cascaded to all rows of the affected job so the job is either processed in its entirety or not at all.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Upload Mode, Auto Generate Job Name, Download Operations, Download Components, Organization Code, Job, Job From, Job To, Job Status, Type, Assembly, Schedule Group, Build Sequence From, Build Sequence To, Class, Start Date From, Start Date To, Completion Date From, Completion Date To, Line, Project Number, Task Number

Oracle EBS Tables Used

mtl_system_items_vl, mtl_item_locations_kfv, wip_schedule_groups, wip_lines, pjm_projects_mtll_v, pa_tasks, wip_discrete_jobs, bom_standard_operations, bom_departments, bom_resources, cst_activities, mtl_parameters, wip_entities, wip_operations, wip_operation_resources, org_access_view, wip_requirement_operations

Report Categories

Enginatics, Upload

WIP Entities, CAC Manufacturing Variance, CAC WIP Resource Efficiency, WIP Account Distribution, WIP Discrete Job Shortage, WIP Required Components, GL Account Distribution Analysis

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import WIP_Job_Upload.xml
SQL Source WIP Job Upload.sql
Full Report on Enginatics www.enginatics.com/reports/wip-job-upload/

Case Study & Technical Analysis: WIP Job Upload

Executive Summary

The WIP Job Upload creates and updates Oracle Work in Process discrete jobs, both standard and non-standard, together with their operations, resources and material requirements (components). Several inventory organizations can be combined in one file, and the upload submits a separate WIP Mass Load run for each organization. Production planners and manufacturing engineers use it to release large numbers of jobs from a schedule, to load job details from a planning spreadsheet and to change the dates, quantities and status of existing jobs. Blitz Report downloads the jobs with their details, so the spreadsheet can be edited and uploaded again.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. MTL_PARAMETERS is joined to WIP_DISCRETE_JOBS on the organization id, and the job to WIP_ENTITIES on the entity id.
  2. Operations are outer joined to the job, and resources to the operations, on entity, operation sequence and organization.
  3. Components are read from WIP_REQUIREMENT_OPERATIONS for the same job, excluding supply type 6 and operation sequence zero.
  4. The operation branch and the component branch are combined with UNION ALL, so a header-only job still returns one row.
  5. The assembly, routing reference and BOM reference are scalar lookups on MTL_SYSTEM_ITEMS_VL through the inventory item ids.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I release many jobs with their operations and components in one file? A: Download with Download Operations and Download Components set to Y, add the rows for the new jobs, and upload. Each job’s rows are processed together, so a job is either processed in full or not at all.

Q: Why are the operations of a new job not exploded automatically? A: By default the bill of material and routing are exploded only for new jobs with no detail lines in the upload. The hidden Explode Bom and Routing column overrides this default.

© Enginatics GmbH