SOP-5-I-MES0020-C Oracle-MES

42Q Home > Tools > Oracle-MES Integration

Tools Oracle-MES Integration Version MES15.3.3 Portal 1.0 Work Instruction

General Notes

Work Instruction alteration suggestions must be reviewed and approved by the site Documentation Manager prior to sending an alteration request.

This Work Instruction is a basic navigation-training guide and is not meant to teach the process flow for individual department functionality. Individual 42Q sites may have minor or specific process changes that will be documented at the site level.

Fields that reference "Optional" require System Administration to set up or update and may vary at the plant level. Individual plant setup and plant specific processes determine whether these fields are required.

Fields that do not reference "Optional" are currently considered required. A required field may be at the system level or a field that is required when the process was created.

Glossaries for Oracle terminology are available within the Oracle Application by selecting from the pull-down menu "Help" – Oracle Applications Library - Glossary.

Introduction

Note: This procedure covers a general scope; a customer-specific scenario should be developed at the plant location.

The Oracle to MDS interface for Work Orders using industry-standard Web Service will allow data to be processed more efficiently using standard XML and eliminate the complicated parsing and mapping inherent to an FTP process. Web services pass data seamlessly and get responses back easily and support centralization of troubleshooting and issue handling.

With Oracle ESB in the middle, we can create entire business flows without having to have a plethora of polling processes or scheduled jobs that are customized for each customer or plant.

With service-oriented architecture, multiple systems can easily access the same information without any knowledge of what the system is actually doing to retrieve or update the data.

Following requirements have been added to the interface,

Work Order Create / Change (Oracle - SFDC)
Work Order Move / Completion (SFDC - Oracle)
Work Order Cancellation / Quantity Decrease Online call (Oracle – SFDC, SFDC - Oracle)

Process Flow

Figure 1: Oracle-SFDC Process Flow

Note: OUR EXX Production Planner and MDS User (Ctrl Prod) can be same person, depending on each plant decision.

Design Considerations

Basic Setup

The main selection criteria default will be based on the assignment of either the category "SFDC.DEFAULT", "SFDC.SYSTEM-BUILD" or "SFDC.TRACED-ITEMS" from the "SANM INTERFACE" category-set to the Item used as the Assembly in the Oracle work-order. The difference between these two Categories is explained in section 6.1.

Figure 2: Interface Item´s Category Set Value

When creating a new work-order in Oracle (Discrete Job), the operator can override the defaults for that specific work-order. The override flag should be used on an exception basis only.

The following table explains the defaults and logic used by the program to determine if a specific work-order should be sent to the shop-floor via the interface.

The key is the item category setup. The org default determines if items that have the category assigned, should be included in the interface or not. Then the final check is at the work-order level; if the defaults say to send and the override flag is set, then the opposite will be done and we will not send it. The table shows all the different combinations and the resulting action.

Figure 3: Item´s Category Set VS Discrete Job Override Flag Criteria

General Process Rules

The selection criteria will be controlled at the Item attribute level although it could be overridden at the Discrete Job level. The decision to use the interface to create a Shop Order in SFDC is governed by the criteria indicated in the table represented in Figure 3.
When creating a new item, users will populate the Category Set for parts they want to be sent to SFDC.

Note: It is possible to use a data loader for a mass load update for new plant implementation.

The Top Level Assembly item has to be setup in SFDC with a Route Assignment prior to using the interface. This is a one-time setup per item (SFDC standard setup).
Discrete Jobs in Oracle can be created manually or by using the MRP planner's work-bench.
A new button "Interface Override" was added to the "More" tab in the Discrete Job screen.
Before the Discrete Job is Released, Production Control will have the ability to override the SFDC attribute at the Discrete Job level.
"SFDC" Interface Name will be selected. The second field is not being used yet and can be left at the default (N).
Once the Discrete Job-status is changed to "Released" the "Interface Override" button is grayed out and can not be used anymore for that job.
Shop Order will be created in SFDC only when the Discrete Job is changed to Released status.
Oracle will use the Work Order selection criteria outlined in Figure 3 for determining which jobs are sent to SFDC.
Oracle Discrete Jobs and SFDC Shop Orders will have a one-to-one relationship.
Shop Order number will be the Discrete Job number.
Oracle Routing and SFDC Route should be mapped at this point, but only during the first time that an Assembly item is being sent through the interface. Future Discrete Jobs for the same Assembly item will have the Routing links defaults from previous jobs.
Shop Order will be released in SFDC to enable shop floor collector move operations.
All move operations in SFDC will be sent as WIP-Move transactions to Oracle periodically.
When the last move operation is recorded in SFDC a Completion transaction is sent to Oracle and the Discrete Job changes to Complete status.
Standard and Non-standard Job types can and will be sent to SFDC depending on the selection criteria in Figure 3.
Oracle is the system of record, only changes originating in Oracle are sent to SFDC, so in order to make changes to the Shop-Order in SFDC, changes need to be made in Oracle first.
Cancellation and quantity decrease request will trigger Oracle to perform an online call to SFDC, users will need to wait for a response back from SFDC before the request is executed or declined.

Note: Fields triggering a change transaction; "Header Level" - Discrete Job Status, Start Quantity, Start & Completion Date. "Routing Level" - Sequence, Operation Code, Department and Description. Changes will only be sent for fields just listed.

Oracle Discrete Job (Create / Change) Field data Transfer to SFDC

Job Number – sent to SFDC.

Job Type
Standard – sent to SFDC
Non-Standard – sent to SFDC

Assembly
Assembly Number – sent to SFDC. If the part number is not setup in SFDC the system will automatically create the part number.
Assembly Revision – sent to SFDC.

Discrete Job Status.

No WS Message Sent

Unreleased – will not be sent to SFDC
Complete – No Charges (From Complete Status) – If the work-order is complete (Qty completed = Initial Qty) Oracle will not send a message to SFDC.

Discrete Job Response to Create / Change WS (Oracle to SFDC)

Released – triggers Oracle to send the Work Order to SFDC via the WO Create / Change WS. IF the WO does not exist SFDC will create a Work Order in an ’

unreleased ’ status. IF the Work Order already exists and was ’ on hold ’ SFDC will release the order from hold. IF the WO already exists and was in a ’ Cancelled ’ status SFDC will reopen the WO with the quantity provided from Oracle in the message.

On Hold – triggers Oracle to send a ’

Hold ’ message to SFDC via the WO Create / Change WS. SFDC will put the Work Order on hold until the Oracle Discrete Job is changed back to a status of ’ Released ’ . SFDC should prevent the user from manually removing a hold. Holds will be controlled by Oracle.

Closed – triggers Oracle to send ’

Closed ’ change to SFDC via the WO Create / Change WS. SFDC will then put the order in a ’ Final Closed ’ status. Discrete Job Response to WIP Move / Completion WS (SFDC to Oracle)

Complete – SFDC will send WIP move to completion transaction to Oracle for the last serial number in the WO WIP Move / Completion WS. If a user manually changes the Work Order in Oracle to a status of Complete this change will not be sent to SFDC. If a user changes a Discrete Job from a ’

Complete ’ status back to ’ Released ’ the change will not be sent to SFDC.

Completion of a Work Order in SFDC moves it to a ready to close status.

Discrete Job Response to WO Cancel / Qty Decrease Online Call WS (Oracle to SFDC / SFDC to Oracle)

Cancelled – triggers Oracle to send an online call cancellation request to SFDC. SFDC in turn puts the order in a special status "X" so a user can not make a change to the order while the system is determining if the order can be cancelled.
IF the shop order has no material in WIP, SFDC will answer back that the order can be cancelled, Oracle will then cancel the order and in Jmds the shop order will display "X" status.
IF the order has material in WIP SFDC will return a message saying the WO can not be cancelled. Manual intervention is required on the material in WIP. Users should either;
Scrap the material in WIP, this will create a scrap transaction in Oracle. Once the last unit is scraped the Oracle WO will auto complete.
Finish building the assembly, once the assembly is complete move to Work Order Excess in SFDC. Users can use this excess on other orders and if required change the serial number.
Put the S/N units to a status of 64 in SFDC by moving the S/N off the job.

Complete – No Charges (From Released Status) – triggers Oracle to send an online call request to SFDC. SFDC in turn puts the order in a special status so a user can not make a change to the order while the system is determining if the order can be put on hold.
IF the shop order has no material in WIP SFDC will answer back that the order can be changed to a Complete – No Charges status. Oracle will then change the status of the order.
IF the order has material in WIP SFDC will return a message to Oracle not allowing the status change. Users will need to remove the qty ’

s from WIP in order to change the status.

Quantities
Start – sent to SFDC
MRP Net – not sent to SFDC

Dates
Start – sent to SFDC
Completion – sent to SFDC.

Flex Field (Header level)
Sales Order Number – sent to SFDC
Sales Order Line Number – sent to SFDC
Priority Code – sent to SFDC
Comments – sent to SFDC
KronosStat – sent to SFDC

Ship To Location – sent to SFDC

Context – sent to SFDC

Schedule Group, Project (TAB)
Schedule Group – sent to SFDC
Line – sent to SFDC
Project – sent to SFDC
Task – sent to SFDC
Unit Number – sent to SFDC
Build Seq – sent to SFDC

Routing (TAB): routing steps must be setup and mapped in SFDC
If the mapping is inaccurate the DJ will end up in the SFDC error queue.
SFDC will create a project setup flag for plants that want to put serial numbers associated with the shop orders on hold due to sequence routing changes.
If routing sequence changes are sent to SFDC, SFDC will send an email alert to PC/Planning department. PC / Planning will be responsible for working with Engineering and Quality to ensure the routing is mapped correctly.
Routing Revision – sent to SFDC
Routing : Operations
Seq – sent to SFDC
Operation Code – sent to SFDC
Department – sent to SFDC
Description – sent to SFDC

Sales Order (Button)– sent to SFDC
Line – sent to SFDC
Demand Class – sent to SFDC
Primary Qty Reserved – sent to SFDC
Primary UOM – sent to SFDC
Quantity Reserved – sent to SFDC
Line UOM – sent to SFDC

The business requested to include resource and time data from Oracle to SFDC interface so in the future we can run reports to track actual vs. standard.
Resources – sent to SFDC.
UOM – sent to SFDC.
Basis – sent to SFDC.
Usage – sent to SFDC.
Inverse – sent to SFDC.
Available 24 Hours – sent to SFDC.
Assigned Units – sent to SFDC.
Schedule – sent to SFDC.

Oracle-SFDC Routing Mapping

Route assignment should be done in SFDC before Shop Order Release. The Routing mapping can be performed after the Shop Order is created in SFDC and it is needed in order to establish a relationship between Oracle Routing and SFDC Route.

It will be a one time setup per Assembly number, once defined it will not be required for new Discrete Jobs for the same Assembly number unless there´s a change at the routing level.

Note: if a Non-standard Discrete Job is created, the resources should have to be edited at the Discrete Job level to comply with the proper Lead Time and Labor charges.

This work instruction does not cover the creation of the route in SFDC, consult the proper documentation for that purpose.

SFDC Item Parameters ( MES )

Fill in the proper Item values as per MDS definition, refer to MDS documentation.

Figure 4: Part Number Form

Route Assignment

In order to perform below instructions, the Assembly item should already exist in SFDC.

Note: SFDC Route should be done in advance as per MES standard workaround, refer to MES documentation.

Figure 5: SFDC Configuration

Navigate to Shop Floor Control, select Configuration, and then select SFDC Configuration to access the SFDC Modules.

Click on the Product Route Maintenance icon.

Figure 6: List Route

Click on Part x Route Assignment icon.

Click on the proper line to select the route.

Figure 7: Part x Route Assignment

Click on Add Part x Route Assignment icon.

Figure 8: Add Part x Route Assignment Form

Select the Shop Floor ID, Part Number and Route and then select Save.

Figure 9: Confirmation

A confirmation to create that Part x Route Assignment appears. Click Yes.

Click on Close button.

Routing Mapping

In order to perform below instructions, the Shop Order should be listed for Release in SFDC as a prerequisite.

Navigate to Shop Floor Control > Production Control > Shop Order Control > Shop Order Release to list the Releasable Shop Orders
Select the Shop Order where the new Assembly item is and click on the "blue arrow" icon on the right to start the Routing Mapping.

Figure 10: Shop Order Release List

Select the Mapping Type that better fits your Manufacturing process and click on "OK" button.

Figure 11: Mapping Type

Define the Mapping operations; select from the "Operation Sequence" (Oracle Routing Operations) list of values the first Operation to map versus "Location" (SFDC) section then click on "Add Map" icon.

Figure 12: Shop Order Mapping

Repeat previous instruction to map as many operations required.

Note: the important Operations to Map are those ones where you have material allocation.

Click on "Save" button.

A confirmation screen will pop up asking whether to release the Shop Order at the same time. For this explanation select "No", in the section 6.2 will be explained the way to Release the Shop Order.

Click on "No" button.

Oracle-SFDC Process

Below step process shows the sequence of actions for a regular order, Owner and Feedback columns may differ among the plants.

Figure 13: Step Process

Discrete Job Create

The WO create explanation below is for the predefined setup scenario which will be used in more frequently.

Item Category Set setup = SANM_INTERFACE
Discrete Job Override flag = SFDC – No

Note: the different combination of scenarios can be found on the table in Figure 3.

Setup the Assembly Item Category Set that better fits your process with the OUR Doc Control.

SANM_INTERFACE (SFDC.DEFAULT): Oracle Discrete Job Routing Header and Resource information are sent to jMDS.
SANM_INTERFACE (SFDC.SYSTEM-BUILD): BOM information is also sent to jMDS in addition to previous choice.
SANM_INTERFACE (SFDC. TRACED-ITEMS): In addition to the Job Routing Header and Resource information, this option will sent to JMDS just the components in the BOM with the category (SANM-TRACEABILITY) in any of its combinations (Date, Lot, MPN, Serial, Label), to send the traceability information to PTS and SFDC.

Figure 14: Item Category Set – SANM_INTERFACE Value (Subcategory for Trace components)

Create the Discrete Job as normal process and save it up to Unreleased status. Notice the "Interface Override" button that has been added to the form in the "More" TAB.

Figure 15: Discrete Job Unreleased

If you want to Override the Item Category definition, click on the "Interface Override" button, select Interface Name = SFDC and Yes/No field = Y, close the window. Otherwise jump to below step to create the DJ based on the Item Category definition.

Figure 16: Override Values

Change the Discrete Job status to Released and Save your work.

Figure 17: Discrete Job Released

Follow the Routing Mapping process explained in section 5.3.

Make sure the Discrete Job has been sent to SFDC, verify section 7.1.

Shop Order Release

Return to the MES Modules screen and select Shop Order Control.
Shop Orders will be listed based on the filter settings selected.

Figure 18: Shop Order Release

Select the Shop Order to be Released and click on the "green check" icon on the right side.

Click on "Next" button.

Figure 19: Release Wizard

Optionally check the "Manually assign a revision to the Shop Order" box and click on "Next" button to continue with the Release process.

Figure 20: Full / Partial Release

Optionally perform a Full Release or a Partial Release (enter in Quantity field the partial amount to release) and click on "Next" button to continue with the Release process.

Figure 21: Revision

If "Manually assign a revision to the Shop Order" box was previously checked then enter a Revision for the Shop Order and click on "Next" button to continue with the Release process.

Figure 22: Release Summary

Optionally check the "Create text file with the serial numbers" box and click on "Next" button to continue with the Release process.

Note: If Partial Releases are going to be performed, notice the text file name will default to the same Shop Order number so make sure a prefix or suffix is added otherwise it will be overwritten and previous Serial Numbers in the file will be lost.

Figure 23: Save File

Specify the path on your computer to save the text file which contains the Serial Numbers

Figure 24: Release Results

A confirmation screen will show a successfully file save, then click on "OK" button.
A new screen will confirm a successful Shop Order Release, click on "Finish" button.

Figure 25: Shop Order in Release Status

Serial Numbers can be found through Shop Order browser and clicking on "Information" blue icon.

Figure 26: Mapping and Serial Number Information

Shop Floor Collector Move Operation

User will proceed with the SFDC move operation based on the standard process, this work instruction does not cover it, consult the proper documentation for that purpose.

Whenever an Assembly Unit is moved to the next Shop Floor Operation, it is also recorded in the Oracle Discrete Job. In order to guarantee data integrity between the two systems, that WIP Move transaction will involve two actions,

1. the shop order in jMDS will go on hold while the transaction is sent to and performed in Oracle

2. once the transaction is done in Oracle, a WIP Move Acknowledgement will be sent back from Oracle to jMDS clearing the hold.

No manual move operation transactions should be done in Oracle for work orders using the interface or else the interface will try to process it again. If someone makes a manual WIP Move or WIP Completion by mistake, the corrective action will be to do a reversal of the manual transaction to allow the automatic transaction from the interface to process.

Note: Moves backwards are NOT sent to Oracle for cost accounting reasons.

Figure 27: Move Operation Recorded in Oracle

Serial Number can be tracked in jMDS through the "Information" icon.

Figure 28: Serial Number Operation Status in jMDS

Once the Assembly units reach the last operation through SFDC a Completion transaction is triggered to Oracle and the Discrete Job changes its status to Complete. Verify the way to validate the Complete transaction from SFDC to Oracle through the "SANM WIP transactions to Oracle" explained in section 7.2.

Figure 29: Serial Numbers in Complete Status

Figure 30: Discrete Job in Complete Status

Discrete Job Component Issue

There is no change on the Component Issue transaction to the Discrete Job, it will happen as the regular process in Oracle depending on the WIP Supply Type involved.

If for some reason there is no enough inventory in the proper sub-inventory, the Discrete Job will not be completed until the component issue is resolved (standard Oracle).

Discrete Job Exceptions

Immediate ECO Implementation including rework on process

The workaround is explained as follows,

SFDC: change shop order revision as usual and if needed the items to be scanned.
Oracle: change the material requirements of the discrete job to reflect the change and ensure accurate material management.
Floor: physically swap the parts in the kit and transact in Oracle.

Oracle Related Process Exceptions

Physical Inventory

If the plant wants to suspend the "WIP Move" and "WIP Completion" transactions (disconnect Oracle-SFDC Interface) during physical inventory, an approval will be required from Corporate Finance department, until then a trouble ticket could be submitted to the MES support with the approval attached as reference. To reestablish the service, another trouble ticked should be submitted by the plant as well.

Month End

Interface should not be turned off during Month End.

Oracle Shop-Floor Monitoring Screens

SANM SFDC WIP Jobs Creation and Change to SFDC

This screen helps the user to verify the Discrete Job in Released status was sent via the Web Services to SFDC for shop order create.

Open the form.

Figure 31: Shop-Floor Interfaces Monitoring Path

Select the Plant Org Code and click on "Find" button, the form will display the orders to be sent for creation to SFDC.

Figure 32: SANM SFDC WIP Jobs Creation and Change to SFDC Screen

"Resources" and "Routings" button displays the current values of the Assembly item by the time the Discrete Job was created.

"Status" button shows the Discrete Job status after it is Released, below the different Process Status.

0 > Ready To Process
2 > Being Processed
3 > Failed
7 > Processed
8 > Ack Error
10 > Ack Successful
Figure 33: "Resources" from SANM SFDC WIP Jobs Creation and Change to SFDC Screen

Figure 34: "Routing" from SANM SFDC WIP Jobs Creation and Change to SFDC Screen

When the Shop Order has been created in SFDC, process status will report "7" as shown below.

Figure 35: Status Processed

SANM SFDC WIP Transactions to Oracle

This screen will display the To Move transactions based on the steps mapped between Oracle Routing and SFDC Route as well as Assembly Complete transactions.

Open the form.

Figure 36: SANM SFDC WIP Transactions to Oracle Screen

The Process Status is available for this form are listed below.

1 > In Process 2 > Pre-Validation Error 3 > Move Error 4 > Processed 9 > Ignored Blank > Pending 11 > In Process 33 > Backflush Error
Remarks:

No one at the plant should ever do a manual WIP-Move, WIP-Completion or reversal of any of these transactions for items that are set to use the interface, because it will cause the interface to stop working and transactions to fail.
Each plant or program should clearly identify which work-orders go through the interface by defining a naming convention, a suffix is recommended. This is critical especially during the initial transition from a manual process to the interface.
When an error happens in the processing of a transaction in Oracle such as when there is a shortage of back-flush components that does not allow completing the units; for example, it is expected that the user will resolve this issue immediately. The interface programs will attempt to re-process the transaction automatically every few minutes for one full day and then put it in error state, the transaction will remain in status 3 (error) until they manually re-process the transaction again after fixing the underlying issue.

Standard Oracle Transaction Errors

Figure 37: Oracle Transaction Errors

Error	Description	Possible Causes	Solution
1	No charges allowed for this job/schedule	These will be transactions where the job is not in Released or Completed status. You can perform transactions only against jobs which have status of Released or Complete.	Change the status of the job to either Released or Complete. If the job is closed, then unclose the job. Then resubmit the transaction.
2	No open period found for date entered	The Inventory period of the transaction date is not opened yet (Next Period).	Open a P2 Trouble Ticket to the help-desk. To open the corresponding inventory period and resubmit the transactions. 1. Check that the period is open. 2. Check that the date is a date between the open and close period.
3	No open period found for date entered	Transaction stuck in the previous Inventory period already closed (Previous Period).	Open a P2 Trouble Ticket to the help-desk to change the transaction date to the current inventory period and resubmit the transactions (standard pending transaction solution).
4	The transaction quantity exceeds the over completion tolerance set for the Job, Repetitive Line/Assembly, or the Organization	The transaction quantity exceeded the over completion tolerance defined in the ’ More ’ tab of the Discrete Jobs form or the over completion tolerance set in the WIP Parameters.	Go to pending move transactions form and reduce the transaction quantity to a quantity that is within over completion tolerance limit and also change the over completion quantity accordingly and then resubmit.
5	Negative Balances not allowed		This message will be prompted if there is not enough on hand quantity to satisfy all the transactions within a batch. Therefore, ensure that the transaction quantity is enough to satisfy the sum of all the transaction quantities for a particular batch.
6	Transaction quantity must be less than or equal to quantity available to move	This also happens when you try to process a move transaction from say op 20 to op 30 even before the transaction from op 10 to op 20 got processed.	Make sure that enough quantity is present in the from operation sequence and from interoperation step and then resubmit the error transaction.

Figure 38: Oracle Transaction Errors

Error	Description	Possible Causes	Solution
7	TRANSACTION_QUANTITY must be greater than zero		Go to pending move transactions form and enter the transaction quantity you actually want to move instead of zero and then resubmit. Resubmit the transactions through forms only.
8	You cannot move assemblies within the same operation and step. Cause: You tried to perform a shop floor move within a single operation and interoperation step. You can only move assemblies between distinct operations	You tried to perform a shop floor move within a single operation and interoperation step.	Change the operation sequence / interoperation step appropriately and then resubmit the transactions. Resubmit the transactions through forms only.
9	Transaction date precedes release date for the job/schedule	You would have entered a transaction date that precedes the release date for your job or schedule. The transaction date for any WIP transaction must always be greater than the Released date of the work order.	Change the transaction date of that error transaction in the pending resource transactions form and then resubmit. Resubmit the transactions through forms only.
9	Invalid transaction UOM		Check the UOM measure being used and find out if valid for the organization source. If not set up the UOM. It may also be a case where the conversion classes have not been set-up.

Business Requirements – Service Level Agreement

Oracle ESB On Fault Processing

If Oracle or SFDC is unable to send/receive a message through Oracle ESB after 30minutes, a help desk trouble ticket should be opened.

Abbreviations

ESB – Enterprise Service Bus
Dataloader – tool used to massively update Oracle Item parameters
MDS – Manufacturing Data Systems
jMDS – MDS supported in JAVA
DJ – Discrete Job
WO – Work Order
SO – Shop Order
WS – Web Services

Further Information

This completes the Oracle-SFDC information. For further information on a specific functional area, refer to Intranet.