This chapter covers the following topics:
Mass Search and Replace
Process Manufacturing Product Development enables you to perform a mass search and replace for ingredients in formulas. The Mass Search and Replace window enables the identification and replacement of key components in formulas, routings, operations, recipes, and validity rules for single or multiple organizations. This permits the query for fundamental components such as the name, version, description, owner, and status. Advanced search and replace features facilitate the search and replacement of components using conditions and specific values in formulas, routings, operations, recipes, and validity rules.
Change Management Control
Mass Search and Replace supports change management control by enabling status and version updates. As part of change management control, mass search and replace enables rapid component substitution while supporting status and version control updates for each formula, recipe, routing, operation, or validity rule affected by the substitution.
Formula Ingredient Substitution
Formula ingredient substitution is a critical business need for process industries to meet stringent product specifications during batch production. The tool enables rapid identification and replacement of approved ingredient substitutions
Ingredients are replaced for various business reasons such as obsolescence, customizations, pricing, or regulatory compliance. Mass search and replace takes into account the affected formula and status in order to recalculate step quantities, technical parameters, and formula contribution by class and subclass. As part of change management control, this tool assists in the management of status and version for each changed component.
Item Substitutions by Date
Date-driven item substitutions are supported. Create a date-specific list of replacement items for raw materials, intermediate items, or packaging components. A common reason for use of item substitution is use of a new or updated packaging component or informational insert.
Item Substitution Status Change Approval Workflow
Formulators define item substitutions to a prescribed list of alternative item replacements. Use the Item Substitution Status Change Approval Workflow to approve a list of substitute items with specified start and substitution effective dates. Associate formulas to the list to manage this date-specific list of replacement items. The new or updated packaging component or insert is substituted during the effective date range.
Use the Mass Search and Replace window to search for and replace records in:
Operations
Routings
Formulas
Recipes
Validity Rules
The Find list enables you to select the target for the search, such as an operation or a routing. You can only search for one target at a time. The Basic and Advanced search regions let you specify conditions to narrow the search. Once you have selected a target, and initiated a search, clear the window before searching for a new target.
Use the Basic search region to specify any combination of Name, Version, Description, Organization, Owner, or Status. Enter specific search data, or use the wildcard % for search string matching.
For example, if you search for:
Owner JDOE (as User ID)
Name A%
The results contain all entries where the Owner is JDOE and that start with the letter A.
Use the Advanced search region to specify conditions to narrow the search for one or more Item field entries. Specify different conditions for each search target.
For Operations you can specify search conditions for:
Activity
End Date
Operation Class
Operation
Operation Version
Resource
Start Date
Status
For Routings you can specify search conditions for:
End Date
Operation
Owner
Routing Class
Routing
Routing Version
Start Date
Status
For Formulas you can specify search conditions for:
Byproduct
Formula Class
Formula
Formula Version
Ingredient
Owner
Product
Status
For Recipes you can specify search conditions for:
Formula
Owner
Owner Organization
Recipe
Recipe Version
Routing
Status
For Validity Rules you can specify search conditions for:
Validity Rule Status
Validity Rule Orgn
Maximum Qty
Minimum Qty
Planned Process Loss
Preference
Product
Recipe Use
Start Date
End Date
Status
Standard Qty
In an advanced search of operations, routings, formulas, recipes, or validity rules, select any of the following conditions for items entered:
Equal To finds targets equal to the specified Value entered.
Greater Than finds targets greater than the specified Value entered.
Less Than finds targets less than the specified Value entered.
Greater Than or Equal To finds targets greater than or equal to the specified Value entered.
Less Than or Equal To finds targets less than or equal to the specified Value entered.
In finds targets that contain the information specified.
For example, if you specify the Item as Owner, select the Condition In, and enter JDOE as the Value, the application finds all targets whose owners are JDOE. Separate each Value entry with a comma.
For example, if you specify the Item as Product, select the Condition In, and enter FORMULA 101,ROUTING 100 the application finds each target product that contains the entries specified.
Not In finds targets that do not contain the information specified.
For example, if you specify the Item as Owner, select the Condition Not In, and enter JDOE as the Value, you find all targets whose owners are not JDOE. Separate each Value entry with a comma.
For example, if you specify the Item as Product, select the Condition Not In, and enter FORMULA 101,ROUTING 100 the application finds each target product that does not contain entries specified.
Like finds targets similar to the information specified using the wildcard %.
For example, if you specify the Item as Product, select the Condition Like, and enter FORM% the application finds each target Product that contains entries with the letters FORM.
Your System Administrator sets up the Mass Search and Replace function security. When functions are registered for the appropriate responsibility, two parameters are passed with the following settings:
Enable_status=No disables the ability to make a change of status using Mass Search and Replace. If you cannot change status, then contact your System Administrator. The default is Yes.
Enable_replace=No disables the replace capability. If you cannot use the replace capability, then contact your System Administrator. The default is Yes.
When new versions of operations, routings, formulas, recipes, and validity rules are created during the Mass Search and Replace process, the default status is assigned according to the status set up for default configuration rules. If a new formula is created, then a new recipe is also created if the recipe configuration rules are set up for the formula owner organization.
Use the Mass Search and Replace window to search for and replace components in recipes, formulas, operations, routings, and validity rules.
You cannot replace components in recipes, formulas, operations, routings, or validity rules that have a status of obsolete/archived, approved for general use, or on hold. If you attempt to replace any components in these entities, then a message displays indicating that you can only change the status. You can search for recipes, formulas, operations, routings, or validity rules that are obsolete/archived or on hold. The Update indicator is cleared for these entities. If you attempt to select the Update indicator, then a message displays that indicates you can only update the status. Click the Replace tab, choose Status, and select the Update indicator.
You cannot replace components in recipes, formulas, operations, routings, or validity rules with a status of pending. If workflow, e-records, and e-signatures are enabled, and a status is changed through mass search and replace, then the entity changes to a pending status. You cannot update an entity with a pending status until it is approved. Go to the advanced worklist to approve a pending status through Oracle Workflow or Oracle E-Records, and respond to the notification. If you attempt to replace any components for such entities, then you receive a message.
To perform a mass search and replace:
Determine if your responsibility can use the Mass Search and Replace function.
Note: To replace the specification and validity rules for specs using the Mass Search and Replace functionality, select the Quality Manager responsibility.
Important: Upon entering the Mass Search and Replace window, the context organization is used to filter for process-enabled items. You can also search across organizations by leaving the Organization field blank.
To search for and replace components
Navigate to the Mass Search and Replace window.
Select Formulas, Operations, Recipes, Routings, or Validity Rules from the Find list to specify the type of records to search for and replace.
Additional Information: If you select Formulas, then you can enter Validity Rule Criteria for Start Date, End Date, Recipe Use, and Status. You can also search for global validity rules.
Additional Information: If you select Validity Rules, then you can enter the Effective Dates for the validity rule. You can also enter the Standard quantity and the Minimum and Maximum quantities that the validity rule covers.
Note: Use the Basic and Advanced regions to specify the search criteria.
Enter one or more of the search parameters. It is not necessary to enter them all. You can use string search matching by entering % as wildcard.
Basic
Enter the Name of the record type to search and replace.
Enter the Version number of the record type to search and replace.
Enter the Description of the record type to search and replace.
Enter the Organization of the record type to search and replace. Search across organizations by leaving this field blank.
Enter the Owner of the record type to search and replace.
Select a Status of the record type to search and replace.
Click Find to search for all records that match the Basic search criteria. If you do this, then proceed to step 12, or click the Advanced region to enter additional search criteria. If you do this, then proceed to step 11.
Advanced
Enter an Item, Condition, and Value to specify a condition for the search. For example, to search for all formulas with a version of 1, select Formula Version as the Item, Equal To as the Condition, and enter a Value of 1.
Click Find to search for all records that match Basic and Advanced search criteria. The Results of the search display. If results are not found, then you cannot use GoTo or Replace. To review the details for each record found perform one of the following:
Double-click the drill down indicator next to the appropriate Name.
Select the appropriate Name and click Go To.
Return to the Product Development Search and Replace window to continue.
Additional Information: You cannot use the Replace capability unless you have proper authorization. Contact your system Administrator if you have questions regarding this authorization.
Replace
Click the Replace region.
Select the component to replace from the Replace list. Enter the Old component Name and, if required, its Version.
Enter the New component Name to replace in selected Results. Enter a new Version if required. If the component being replaced is a formula, then enter an appropriate Scale Factor. You can replace ingredients using a scale factor greater than zero. However, you cannot replace ingredients using a negative scale factor.
Select the Update indicator next to each Name to replace with the entry made in the new Name field. To update the entire list, choose Select All from the Actions menu. To clear all selections, choose Deselect All from the Actions menu.
Click Replace to perform the specified changes. The message "Are you sure you want to replace this?" displays:
Click Yes to proceed with replacement of the selected records with the New Name field.
Click No to cancel replacement of the selected records with the New Name field.
If automatic version control is active, then the message "Create new version?" displays.
Enter Yes to indicate that you want to create a new version, or enter No to keep the same version. If records are not found that meet the replace criteria, then the message "No records were found that meet the replace criteria." displays.
Note the Request ID number.
Choose View from the menu.
Choose Requests to view the status of your request in the concurrent program manager.
Select View Log to determine if there were any problems after your Request ID completes normally. The log displays all successful and failed updates, and it indicates if no records were found for the replacement operation.
Availability or date-driven item substitutions are often required in the manufacturing process. Formulators define item substitutions in a prescribed list of alternative item replacements with usage managed either by defined start and stop dates or by a lack of original item availability. The item substitution list is approved through the Item Substitution Status Change Approval Workflow with separate version and status control. Following summarizes the process to create and approve the item substitution list:
Create an item substitution list to specify an original formula item and its substitute, with the substitution based either on substitution validity dates or on the original formula item's availability.
Associate one or more formulas to this item substitution list.
Optionally, use the Find Formula feature to select the list of formulas to associate to the item substitution list.
Associate any formula you have privileges to access except those with a status of Obsolete/Archived.
Apply for a change of status for this item substitution list to start the approval workflow that obtains e-signature approvals. Acquisition of the e-signature finalizes the item substitution list setup. When the list is approved, the item substitution becomes effective in the Oracle Process Manufacturing (OPM) Planning, Costing and Process Execution applications where the substituted item is referenced. The item substitution list cannot be used by a formula unless it has an Approved for General Use status.
Companies use item substitutions to manage an availability or date-specific list of replacement items. This includes raw materials, intermediate items, or packaging components. For example, a product formulator can create a standard formula and include a label as one of the packaging ingredients. As special promotions expire, the labels need to change. Rather than changing the formula each time a label change is required, item substitution lists are created with start dates and end dates so a specific label is assigned to a specific date range. Alternatively, define an availability-based substitute label for use only when original label supplies are exhausted. Planning and production have visibility to the item substitution list.
After setting up and approving an item substitution list, associate formulas to each list created. Click Associate Formulas on the Item Substitution List window, and select each formula to use from the LOV or click Find Formula.
Use Change Status to approve, audit, and manage changes made to an item substitution list. The default status is set to New. Other available statuses are:
Approved for General Use
Obsolete/Archived
If you decide to select Approved for General Use and the e-signature workflow is activated, then the electronic record described in "Item Substitution Status Change Approval Workflow" displays. The list status is set to Pending for Approved General Use until all e-signature approvals are collected. When the approval of the item substitution list is complete, the associate list of formulas and the original formula item line substitution effective date are set. The item substitution list cannot be used by a formula unless it is approved for general use.
For example, substitution of SACCHARIN with ASPARTAME requires an analysis to determine use of the original or replacement UOM. Select the appropriate Replacement UOM Source on the Item Substitution List window. Select Original Item to maintain the original UOM, or select Replacement Item to use the UOM of the substitution item.
The Replacement Qty field represents the quantity relationship of the replacement item to its original item. For example, one pound of original item SACCHARIN has a primary UOM of kg. It can be replaced by 0.6 kg of ASPARTAME. Therefore, enter 0.6 as the Replacement Qty.
Example Calculation of Replacement Quantity
When you substitute an item, the substitute quantity is based on the original unit quantity expressed in its primary UOM as displayed on the Item Substitution List window.
Here is an example:
1 unit quantity of master item = 0.6 units replacement item quantity
If the quantity of master item is 100 kg, then
Replacement Qty = 0.6 * 100 kg
= 60 kg ASPARTAME
Use 60 kg ASPARTAME as a substitution for 100 kg SACCHARIN.
Set the Version Control parameter to Yes, No, or Optional. When this parameter is set to:
Yes, and an approved item substitution list is updated, then a new version of the list is generated each time a change is made.
No, and an approved item substitution list is updated, then a version change is not made.
Optional, and an approved item substitution list is updated, then you can change the version. Change the existing version prior to saving the item substitution list.
Access the Formula Substitution List window from the Actions menu on the Formula Details window. Substituted items display in the Substitution Effective Date field on that window.
Once an item substitution becomes part of a formula, the OPM Planning, Costing, and Process Execution applications access the substitution as the active, effective item, either while the original item is unavailable or for the defined date range, depending upon whether the item substitution is availability or date-based. If an item substitution affects the quality specifications required for a batch, then there are utilities to change the specification association for a sample during sample creation or specification comparison in the OPM Quality Management application.
Use the Item Substitution List window to create a list of formulas that you set up for availability-based or date-based item substitutions. For date-based item substitutions, the substitutions occur based on dates you enter to start and stop the item substitution.
To create item substitution lists:
Create and approve formulas.
Optionally, set up the Item Substitution Status Change Approval Workflow.
To create an item substitution list
Enter the Name of the item substitution list. Required.
Enter the Version number of the item substitution list. The default is 1. Required.
Enter a Description of the item substitution list. Required.
Status is restricted to:
New for a new list that requires approval. Default.
Approved for General Use for an approved list name and version.
Obsolete/Archived for a list no longer approved for general use.
Choose Change Status from the Actions menu to initiate the Item Substitution Status Change Approval Workflow after entering all required fields.
Enter Original Item as the original item to substitute using this list.
Description is the description for the item entered on the Item Master.
Enter Qty as the quantity of the original item to use when compared to the Replacement Qty in a formula.
Item UOM is the original item's primary UOM entered on the Item Master.
Owner Organization is the default organization code. Required.
Enter Preference as the desirability assigned to this list. A value of 1 is the most desirable list, followed by 2, 3, 4 and so on. If substitution lists have overlapping effective date periods, then the list with the lowest number in this field is used. The Preference field is always unique within a given date range. Required.
Enter Replacement UOM Source as:
Original to maintain the original item UOM (Default).
Replacement to use the substitution item UOM.
Select the Substitution Type:
Date Based Substitution (default), to substitute the item based on the date range entered.
Availability Based Substitution, to substitute the item if the quantity needed for the original item exceeds the quantity available.
To enter valid dates if the Date Based Substitution Type is selected
These date fields only appear when a Substitution Type of Date Based Substitution is selected.
Enter Valid From Date as the approved starting date and time for the item substitution list. This is the beginning of the period for which the item substitution is effective, and defaults to the system date. Required.
Enter Valid To Date as the approved ending date and time for the item substitution list. If a date is not entered, then this is an open-ended item substitution list. If the system date is greater than the last entry for this field, then the original item in the formula is used for subsequent batches.
Enter Item as the replacement item. The Description defaults from the Item Master.
Enter Replacement Qty as the quantity relationship of the replacement item to its original item. See: Calculating the Replacement Quantity in Managing Item Substitutions in Formulas.
UOM defaults to the primary unit of measure for the replacement item. If a UOM conversion exists on the Item Master between the new UOM entered and the primary UOM for the replacement item, then change this field.
Enter Formula as the formula to associate to this item substitution list, or use the Find Formula window to enter formulas as described in the task that follows this procedure.
Enter Version as the formula version to associate to this item substitution list.
Description is a brief description of the formula as entered on the Formula Details window.
Status is the status of the formula association. The status displays New until the status of the completed list is obtained.
Item Substitution Effective Date is the date on which the first substitution becomes effective in the associate formula. This date is derived by the application.
To find formulas for item substitution lists
See: Finding Formulas for Item Substitutions.
To change the status of the item substitution list
Choose Change Status from the Actions menu.
Choose one of the following:
Approved for General Use to approve the item substitution list for general use.
Obsolete/Archived to obsolete an existing item substitution list.
Click OK.
Use the Find Formulas window to select formulas you have entered and saved, for item substitutions.
To find formulas for an item substitution
Navigate to the Item Substitution List window.
Enter all required fields. See: Creating and Approving Item Substitution Lists.
Click Associate Formulas.
Click Find Formula to navigate to the Find Formula window.
Enter as many parameters as needed to filter formula search results:
Enter search parameters in the Basic region for Formula Name, Version, Description, Class, Product, or Item.
Enter Start Date, End Date, Recipe Use, Organization, and Validity Status.
Enter search parameters in the Advanced region to enter a specific Item, Condition, or Value.
Click Find.
Review the results of the search.
Select the formulas for use with this item substitution list.
Click OK to display the Associate Formulas window.
The Find Item Substitution List window retrieves substitution lists you have entered and saved based on a set of criteria you enter. Narrow the search based on a variety of factors, such as list status, original and replacement item, and substitution type.
To find an item substitution list
Navigate to the Find Item Substitution Lists window.
Enter any of the following criteria to narrow your search:
Status is restricted to New for a new list that still requires approval, Approved for General Use for an approved list name and version, and Obsolete/Archived for a list no longer approved for general use.
Original Ingredient is the original item to substitute using this list.
Replacement Item is the item that replaces the original item.
Name is the name of the item substitution list.
Version is the version of the item substitution list.
Description is a description of the item substitution list.
Substitution Type is either Availability Based Substitution or Date Based Substitution.
Valid From Date is the approved starting date and time for the item substitution list. This is the beginning of the period for which the item substitution is effective, and defaults to the system date.
Valid End Date is the approved ending date and time for the item substitution list. If a date is not entered, then this is an open-ended item substitution list.
Preference is the desirability assigned to this list. A value of 1 is the most desirable, followed by 2, 3, 4 and so on. If substitution lists have overlapping effective date periods, then the list with the lowest number in this field is used. The Preference field is always unique within a given date range.
Click Find.
To clear the Find Item Substitution List window
Click Clear to clear the item substitution list. The Original Ingredient continues to display.
The Formula Substitution Lists window displays all substitution lists you have created, approved and associated to a formula item in the Formula Details window. The substitution list details are display only, and cannot be modified, overridden, or deleted on this window.
To display formula substitutions from the Formula Details window
Navigate to the Formula Details window.
Select an ingredient line with effective date for item substitution.
Choose Substitution Lists from the Tools menu.
The following fields are display only on the Formula Substitution Lists window:
Ingredient is the original item to substitute using this list.
Description is a description of the original item as entered on the Item Master.
Name is the name of the item substitution list.
Version is the version of the item substitution list.
Description is the description of the substitution list.
Status is status of the item substitution list.
Preference is the desirability assigned to this list. A value of 1 is the most desirable, followed by 2, 3, 4 and so on. If substitution lists have overlapping effective date periods, then the list with the lowest number in this field is used. The Preference field is always unique within a given date range.
Organization is the default organization code.
Original Qty the quantity of the original item to use when compared to the Replacement Qty in a formula.
Replacement Uom Type is either Original (the original item's UOM) or Replacement (the replacement item's UOM).
Valid From is the approved starting date and time for the item substitution list. This is the beginning of the period for which the item substitution is effective, and defaults to the system date.
Valid To is the approved ending date and time for the item substitution list. If a date is not entered, then this is an open-ended item substitution list.
Replacement Item is the replacement item for the original item.
Description is the description of the replacement item.
Unit Qty is the quantity relationship of the replacement item to its original item. See: Calculating the Replacement Quantity in Managing Item Substitutions in Formulas.
Uom is the primary UOM for the replacement item.
To display an item substitution list
Click the desired Name of the item substitution list.
Click Details.
The Oracle Process Manufacturing (OPM) Product Development Item Substitution Status Change Approval Workflow notifies the workflow recipient that an item substitution list was created and requires approval for general use or approval to make obsolete or archived. The item substitution list cannot be used with its associate formulas unless it has a status of Approved for General Use.
Refer to the Oracle E-Records Implementation Guide to set up the following:
Set up approvals in the Approvals Management Application Administrator responsibility.
Set up the business event in Workflow Administrator Web Applications.
Optionally, set up the ERES Administrator configuration variables to require e-record and e-signature.
When you request a status change to Approved for General Use or Obsolete/Archived, this business event is triggered for an item substitution.
Following summarizes the subscription for the oracle.apps.gmd.itemsub.sts business event:
| Field | Value |
|---|---|
| Event Name | oracle.apps.gmd.itemsub.sts |
| Display Name | GMD ERES Item Substitution Status Change Approval |
| Owner Name | Product Development |
| Owner Tag | GMD |
| Description | Item Substitution Status Change Approval |
The workflow sends a notification to the contact person to approve the item substitution list. The e-record event can be set to run ON-LINE or in DEFERRED mode.
Changes to substitution status on the Item Substitution Lists window usually run in the ON-LINE mode. After approval, control returns to the original setup window with the status set to Approved for General Use.
Use the Change Status API to approve the e-record in the DEFERRED mode.
The body of the notification for the Item Substitution Status Change Approval transaction follows:
Substitution Name: Substitution 455-1234
Version: 2
Description: Substitution for Item 455-1234
Original Item: 455-1234
Description: Original Item Used
Original Item Qty: 1.0000
Original Item UOM: KG
Substitution Type: Date Based Substitution
Valid From Date: 11-Feb-2005
Valid To Date: 28-FEB-2005
Preference: 1
New Status: Approved for General Use
Item Substitution Descriptions:
================================================================
Language Description
================================================================
US Item 455-1234 - English
F Item 455-1234 - French
ESP Item 455-1234 - Spanish
Replacement Item
================================================================
Item Description Unit Quantity UOM
================================================================
421-999E Ing. 421-999 2 LB ================================================================
Associated Formulas
================================================================
Formula Version Description
================================================================
Formula 1 190.0 Formula 1 Description
Formula 2 290.0 Formula 2 Description
=================================================================