OPM Routing Upload

➜ Back to Library
XMLXML import file

Description

OPM Routing Upload creates and updates Oracle Process Manufacturing (OPM) routings from Excel through the Oracle routing APIs: the routing header, its routing steps, each referencing an existing operation, and the dependencies between steps. Each spreadsheet row is one routing step. You can create new routings or new routing versions, change steps of existing routings, add steps and dependencies, and delete steps and dependencies.

When to use it

  • Mass-create OPM routings with their steps instead of keying them into the Routings window, for example during an implementation or a new plant rollout.
  • Create a new version of an existing routing from the same or different operations.
  • Add steps or step dependencies to existing routings in bulk.
  • Change step quantity, release type or minimum transfer quantity on many routing steps.
  • Remove obsolete routing steps or individual step dependencies across many routings.
  • Maintain the routing and routing step descriptive flexfields in bulk.

Before you start

  • Your responsibility has organization access to the process-enabled organization that owns the routings. The Organization Code list and the download only include process-enabled organizations your responsibility can access.
  • The operations you reference already exist in that organization with the operation version you enter. The upload does not create operations.
  • Routing classes, units of measure and owners you enter exist; choose them from the lists in the Excel file.

Step 1 – Set the parameters

Open OPM Routing Upload in Blitz Report and set the parameters:

ParameterMeaning
Upload ModeCreate (default) opens an empty sheet for new routings; Create, Update downloads existing routings for editing and lets you add new ones.
Organization CodeRestricts the download to routings owned by this process-enabled organization.
RoutingRestricts the download to one routing number, with all its versions. Enter the exact routing number in upper case.

The example downloads routing C-DECAFF ROAST of process organization PR1.

Blitz Report run screen for OPM Routing Upload with template Default, Upload Mode Create, Update, Organization Code PR1 and Routing C-DECAFF ROAST

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens, with one row per step of each existing routing (routings marked for deletion are not downloaded), or an empty sheet in Create mode. Existing step dependencies are not downloaded, so Depends On Step No is empty on downloaded rows.

Excel file with the four steps of routing C-DECAFF ROAST version 1 in organization PR1, one row per step with its operation, step quantity and release type

Step 3 – Enter or change the data

Each row is one routing step. The header columns (Organization Code, Routing, Routing Version, Routing Description, Routing Class, Routing Uom, Routing Quantity, process loss, effective dates, Enforce Step Dependency, Contiguous, Routing Status, Owner) repeat on every step of the same routing, and the rows of one routing must be kept together. Organization Code, Routing and Routing Version identify a routing: a combination that does not exist yet creates a new routing, or a new version when the routing number already exists. Routing numbers are stored in upper case.

  • New routing or version: enter the header columns with at least Routing Description and Routing Uom, and one row per step with Step No, Operation, Operation Version, Step Quantity and Step Release Type (Automatic Release, Manual Release or Incremental Release). A new routing gets the Routing Status you enter, or New when it is empty, and starts today when Effective Start Date is empty.
  • Existing routing: on downloaded steps you can change Step Quantity, Step Release Type, Minimum Transfer Quantity and the step flexfield. Of the header columns, Routing Class, Routing Uom, Fixed Process Loss Uom and Owner are updated; the description, quantities, process loss, effective dates, step dependency flags and the status of an existing routing are not changed. Add rows with a new Step No to add steps.
  • Step dependencies: to make a step follow another, enter the predecessor in Depends On Step No on the step’s row, optionally with Standard Delay, Minimum Delay, Maximum Delay, Transfer Percent, Rework Code and Chargeable. For a second predecessor add a row that repeats the Step No with Operation empty and a different Depends On Step No. Dependencies are finish-to-start. Add the dependencies of a new routing in a second upload, after the routing has been created (see Troubleshooting).
  • Deleting: set Delete Step to Yes on a downloaded step to remove it with its dependencies, or enter Depends On Step No and set Delete Dependency to Yes to remove one dependency.

In the example the minimum transfer quantity of step 30 of version 1 is set to 500, and four rows add version 2 with a new description and the same four operations.

Step 30 of version 1 with minimum transfer quantity 500, marked Update, and four rows for the new version 2 with a new description, marked Create

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and saves the file. Correct any rows it flags before uploading.

All edited rows show status Valid after Validate and Save

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which processes the rows routing by routing: it creates a new routing with all its steps, or updates the header and steps of an existing routing, adds steps and dependencies, and then deletes the flagged dependencies and steps.

File Upload page with the saved Excel file selected for upload

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status and message, Routing created. or Routing updated., or the error that stopped the routing.

Result report showing routing version 1 updated and the four steps of version 2 created, all with status Success

What’s produced

  • Routings created or updated in Oracle Process Manufacturing, with their steps and step dependencies; steps and dependencies deleted where flagged.
  • A result report listing every row with a status (success or error) and a message.

Common questions

Can I create operations with this upload?
No. Operations must already exist; the upload references them by operation number and version.

Why are the description and quantity of an existing routing not changed?
The Oracle routing API only updates Routing Class, Routing Uom, Fixed Process Loss Uom and Owner on an existing routing. Description, quantity, process loss and effective dates are set when the routing is created; to change them, create a new routing version.

Can I change the routing status?
Only when the routing is created. The status of an existing routing is kept; change it in the Oracle Routings window.

What happens if one row of a routing fails?
All rows of that routing version are rolled back and show the same error message. Other routings in the file are still processed.

How do I give a step two predecessors?
Add a second row that repeats the Step No, leave Operation empty, and enter the other predecessor in Depends On Step No.

Why can’t I choose Yes in Delete Step or Delete Dependency?
Delete Step is offered only on downloaded steps, and Delete Dependency only on rows with a Depends On Step No.

Troubleshooting

MessageCauseWhat to do
ORA-20001: Organization “<code>” is not a valid process-enabled organization.The Organization Code is not a process-enabled inventory organization.Use a process organization from the list.
ORA-20001: Operation “<operation>” version “<version>” not found in organization “<code>”.The operation and version do not exist in the routing’s organization, or the operation is marked for deletion.Check the Operation, Operation Version and Organization Code.
ORA-20001: A routing must have at least one step.The rows of a routing contain no step with an Operation.Add at least one row with Step No, Operation and Operation Version.
ORA-20001: Field Validation : Missing ROUTINGSTEP_NO_UOM User-Defined Exception in Package GMD_ROUTINGS_PUB Procedure INSERT_ROUTINGA new routing was uploaded with Depends On Step No filled.Upload the new routing without Depends On Step No. Then download it in Create, Update mode, enter the dependencies on its steps and upload again.
ORA-20001: Routing attributes: … / Routing step attributes: …A routing or routing step descriptive flexfield value failed validation.Correct the attribute values for the chosen context.

SQL 215 lines

Parameters 3

ParameterTypeList of values SQL
Upload ModeLOV
:p_upload_mode like '%' || xxen_upload.action_update
Organization CodeLOV
x.organization_code=:p_organization_code
RoutingChar
x.routing=:p_routing