Workflow - Common Errors and Solutions_rev

47
Common Errors and Solutions when Using Workflow in Order Management An Oracle White Paper March 2007

Transcript of Workflow - Common Errors and Solutions_rev

Page 1: Workflow - Common Errors and Solutions_rev

Common Errors and Solutions when Using Workflow in Order Management

An Oracle White PaperMarch 2007

Page 2: Workflow - Common Errors and Solutions_rev

Contents

Executive Summary ………..………………..…………………………………………11. Introduction …………………….…………..……….……………………………….22. Processing Orders Using Workflow ….….……….……………………………….73. Error Handling …….………..………….…………….…………………………….164. Data Corruption ……………………..….…………….……………………………255. Workflow Performance Degradation ……..………….…………………………..296. Data Needed to Request Data Fix ……………..…..….………………………..30Appendix A ……………………………………………….……………………………32References ……………………………………………….……………………………33

List of Illustrations:

Figure 1a. Generic Order Header Workflow Process …. …………………….…2 Figure 1b. Generic Order Line Workflow Process ………………………………….3Figure 1c. Generic Generate Default Account Process ……………………………3Figure 1d. Activities List Window ……………………………………………………..5Figure 1e. View Diagram Window …………………………………………………….6Figure 2a. Incomplete Result at Booking ………………………………………..9Figure 2b. Process Message Window for Activities in SO Form ……………… 10Figure 2c. Process Message Window for Deferred Activities ……………………11 Figure 2d. On Hold Result at Booking ………………………………………………12Figure 2e. Not Eligible Result at Invoice Interface ……………………………… 13Figure 3a. OMERROR Process …………………………………………………… 17Figure 3b. Open Messages Checkbox is Checked ………………………………. 18Figure 3c. View Open Messages from the Sales Order Form ………………… 19Figure 3d. Line “Stuck” at “Shipped” – Open Messages not Checked ………….20Figure 3e. Fulfill Activity in Error …………………………………………………….20Figure 3f. Workflow Errors for Fulfill Activity ………………………………………21Figure 3g. Retry of the Fulfill Activity in Error ………………………………………21Figure 3h. Successful Retry of the Fulfill Activity in Error ……………………….. 22Figure 3i. Line After Retry of the Fulfill Activity in Error ………………………….23

1

Page 3: Workflow - Common Errors and Solutions_rev

Executive Summary:

Workflow in Order Management is the engine that processes orders from start to finish. At times, order headers and lines encounter errors in either seeded or customized flows. When the workflow gets “stuck” it refuses to continue to the next activity. The result is that orders or RMAs cannot be booked, configuration items cannot be created, lines cannot be fulfilled, shipments cannot be invoiced, orders cannot be closed and so on. This presentation will discuss how to resolve and prevent these workflow issues and as a last resort what data is needed to request a data fix from Development.

1

Page 4: Workflow - Common Errors and Solutions_rev

1

Introduction

Workflow controls the sequence of events that occur in the processing of sales documents such as orders, quotes, blanket sales agreements, returns, order lines, and return lines. Oracle Workflow manages activities, executes functions, sends notifications, maintains completed activity history, detects errors, and initiates error processes. An Item Type is a grouping of workflow components into a high level category. All components of a workflow process must be associated with a specific item type. An item type can contain multiple processes.

OM Item Types

Order Management uses the following seeded workflow item types to process headers and lines:

OM Order Header (OEOH) - All Order Header level activities and sub-processes are seeded under this WF Item type. Header flows are started using this item type with the header ID as the Item Key. An order flow is started when an order header is created and saved.

OM Order Line (OEOL) - All order line level activities and sub-processes are seeded under this WF item type. Line flows are started using this item type with the line ID as the item key. An order line flow is started when an order line is created and saved.

OM: Generate Cost of Goods Sold Account (OECOGS) - This is the item type that encompasses all processes designed to build the COGS account when interfacing transactions from Order Management to Inventory.

The following is an example of a generic order header workflow process belonging to the OEOH item type:

2

Page 5: Workflow - Common Errors and Solutions_rev

Figure 1a: Generic Order Header Workflow Process

The Order Flow – Generic is the most commonly used header flow. This process includes activities that book and close the order header. It can be used with any line flow for any item type, with outbound lines and with return lines.The following is an example of a generic order line workflow process belonging to the OEOL item type:

Figure 1b: Generic Order Line Workflow Process

The Line Flow – Generic line flow manages several item types. It can be used for all items including assemble-to-order (ATO) items, ATO models, kits, and pick-to-order (PTO) models. This flow will not work for configured items generated from assemble-to-order models.

The following is an example of the generic Generate Default Account process belonging to the OECOGS item type:

3

Page 6: Workflow - Common Errors and Solutions_rev

Figure 1c: Generic Generate Default Account Process

The Generic Generate Default Account Process derives the Cost of Goods Sold account for a transaction interfaced to Inventory from Order Management/Shipping. It comes seeded with the function Get CCID (account ID) for a line. This function will return the CCID from the Cost of Goods Sold assigned to the item on the sales order line within the shipping inventory organization. To generate the COGS Account from the Order Type, please review Note.414314.1 for detailed instructions.

Note: Workflow processes starting with UPG_** are upgraded workflows for use in processing upgraded orders from prior releases. These workflows should never be used for new orders after the upgrade as they contain inefficient or obsolete activities in 11i.

Process Dependencies

There is a parent and child dependency between orders and lines.

Example:

1. Order lines should wait for the order to book before progressing in their individual Line flows.

2. All order lines should close, before the order header closes.

4

Page 7: Workflow - Common Errors and Solutions_rev

Viewing Workflows in Oracle Order Management

From within Order Management you can view the active workflow processes discussed in this paper and their associated functions, messages, sub-processes, notifications, and properties. These processes must be in an active running state and also associated with a sales order or sales order line in order to view them.

To view processes for specific orders within Order Management, complete the following steps:

1. Open the desired order in Oracle Order Management.2. Navigate to the Tools menu and select Workflow Status. 3. A new window opens and displays the workflow status as an

activities list.

The following image depicts the Activities List window in Oracle Order Management:

Figure 1d: Activities List Window

5

Page 8: Workflow - Common Errors and Solutions_rev

4. Select the View Diagram button to view the actual workflow diagram.

The following image depicts the View Diagram window in Oracle Order Management:

Figure 1e: View Diagram Window

6

Page 9: Workflow - Common Errors and Solutions_rev

2

Processing Orders Using Workflow

There are three modes in which orders or lines can be progressed: Synchronous, Manual or Deferred. When an order (or line) is created, the application starts a header (or line) flow for it. The Workflow engine will push the order/line ahead as long as the activities are synchronous. The flow stops when it hits block activities, notification activities or wait activities. The flow gets deferred to the background when it hits a high-cost activity or an activity that explicitly defers the flow to the background.

Synchronous Completion

In this mode, once a flow is started or re-started the WF activities are executed synchronously or online until it reaches the end of the flow or reaches some kind of block activity or is deferred.

Example: You create a line that uses the seeded “Line Flow - Generic”. This has the seeded “Schedule - Line” sub-process. Provided that there are no holds, once the order is booked, the line will automatically schedule. The flow will then continue on and stop at the “Ship” activity, making the line eligible for Pick Release.

Manual Completion

If the order/line hits an “Eligibility Block”, then you can move the order/line forward via the Progress Order LOV (Actions > Progress Order). This LOV displays the functions that the order or line is eligible for (and can be manually completed).

Example: You create an order that uses the seeded “Book – Order, Manual” sub-process. When an order is eligible for Booking, the flow will stop at the “Book – Eligible” block. The Progress Order LOV on the Sales Order Header form displays that the order is eligible for Booking. Clicking Ok on the LOV will trigger completion of the “Book – Eligible” block. The flow

7

Page 10: Workflow - Common Errors and Solutions_rev

will move to the “Book” activity and execute it, thus booking the order.

Deferred CompletionAn order or line flow can also stop because the flow was deferred to the Background Engine.

Example: Fulfillment is deferred in all the seeded line flow. The Workflow Background Engine Process concurrent program needs to be scheduled to run at periodic intervals to be able to fulfill and invoice interface lines (otherwise it would have to be manually executed). The Workflow Background Engine also processes Wait activities and Timed-Out activities.

Activity Results

Most Order Management function activities use the standard seeded lookup “OM Sub-Process Results, handles Holds”. This lookup has the following result codes: Complete, Incomplete, On Hold and Not Eligible.

1. Complete - A function activity completes with the “Complete” result when it executes successfully.

Example: When the seeded “Book” function successfully books an order, it will complete with a “Complete” result. Please refer to Figure 1d.

2. Incomplete - A function activity completes with the “Incomplete” result when it runs into expected errors that it is built to handle. The “Incomplete” result normally transitions to an “Eligibility” block or a Wait Activity.

Example: When the seeded “Book” function finds that the order line does not have a Ship To on the order line, it will complete with an “Incomplete” result and transition to the “Book -Eligible” activity.

The following image depicts the Incomplete Result at Booking:

8

Page 11: Workflow - Common Errors and Solutions_rev

Figure 2a – Incomplete Result at Booking

When an activity is completed via the Sales Order window, the Processing Messages window appears to display messages that indicate errors. The error message can be retrieved from the Header or the Line using Actions > View Open Messages.

The following image depicts the Process Message Window for the Incomplete Result at Booking:

9

Page 12: Workflow - Common Errors and Solutions_rev

Figure 2b – Process Message Window for Activities Completed via the Sales Order

After providing the missing information you can repeat the Book activity via the “Book” button or the Progress Order LOV.

For activities processed by the Workflow Background Engine (the activity is deferred), the error messages are stored in the Order Management processing message table. View these messages via the Processing Message window (OM Responsibility > Orders, Returns > Process Messages) using the concurrent program request number, the workflow activity, and/or order or line basis.

The following image depicts the Process Messages window error messages for the Workflow Activity Purchase Release:

10

Page 13: Workflow - Common Errors and Solutions_rev

Figure 2c – Process Message Window for Deferred Activities

When an activity is completed via a concurrent program, its output file lists all the error messages that were posted.

3. On Hold - A function activity completes the “On Hold” result when it runs into a generic or activity specific Hold. The “On Hold” result normally transitions to an “Eligibility” block or a Wait Activity. Example: When the seeded “Book” function finds that there is generic hold on the order, it will complete with an “On Hold” result and transition to the “Book -Eligible” activity.

The following image depicts the “On Hold” result at Booking:

11

Page 14: Workflow - Common Errors and Solutions_rev

Figure 2d – “On Hold” Result at Booking

After releasing the hold you can repeat the Book activity via the “Book” button or the Progress Order LOV.

5. Not Eligible - A function activity completes with the “Not Eligible” result when it does not make sense for the Order Header or Line to be processed by a that activity.

Example: When the Invoice Interface Activity processes a non-invoiceable item line, it completes with the “Not Eligible” result and transitions to the end of the Invoice Interface sub-process.

The following image depicts the “Not Eligible” result at Invoice Interface:

12

Page 15: Workflow - Common Errors and Solutions_rev

Figure 2e - “Not Eligible” Result at Invoice Interface

Only on a successful completion (Complete or Not Eligible result) of a business function, does a flow exit out of the respective functional sub-process and continues on to the next activity.

Order or Line Processes that are Workflow Enabled

Some functions are seeded in multiple variants (Synchronous, Manual & Deferred). Following is list (not comprehensive) of business functions that are workflow enabled:

1. Booking: An order can be booked manually or it can be deferred to the Workflow Background Process. Order Management uses native WF co-ordination activities to ensure that order lines wait for the order to book before progressing

2. Scheduling: The Scheduling function allocates supply to demand and makes the Order line visible to MRP (as

13

Page 16: Workflow - Common Errors and Solutions_rev

demand). Order Management provides two variations on Scheduling, one where it is performed synchronously and another one where it is deferred.

3. Create Supply: The seeded “Create Supply” sub-process has the intelligence to route Order Lines differently based on the item type and whether they are internally or externally sourced.

4. ATO Processing: The entire ATO process is workflow enabled. There are various seeded sub-processes to support functions such as creating the configuration item, the BOM & routings, calculating lead time and rolling-up cost, creating the work order, etc.

5. Ship: Pick Release, Ship Confirm and Inventory Interface are part of the seeded “Ship - Line, Manual” sub-process. Each of the shipping functions is not Workflow enabled. The “Ship” activity is a block activity that waits until the line is picked, shipped and interfaced to Inventory and Shipping and communicates that information to Order Management.

6. Purchase Release: The seeded Purchase Release sub-process interfaces information to Purchasing when order lines need to be drop-shipped.

7. RMAs: The Returns receipt and acceptance function is workflow enabled using block activities.

8. Fulfillment: The seeded “Fulfill” activity ensures that lines do not move forward in their flows until they are fulfilled.

9. Invoice Interface: The seeded ‘Invoice Interface – Line’ sub-process interfaces line information to Invoicing.

10.Close Order and Close Line: Closing Orders and Lines is workflow enabled. The seeded “Close - Order” sub-process uses native WF co-ordination activities (wait for Flow) to ensure that the order header closes after all the lines have closed. It is designed to close the order at the end of the month.

Extending Workflow

Instead of providing flows and activities to meet every possible business rule and need, Order Management integrates with Workflow to provide easy customizing capability. Oracle Workflow provides a complete set of PL/SQL APIs and public

14

Page 17: Workflow - Common Errors and Solutions_rev

views that can be used to make any application function workflow enabled. Care must be taken to follow certain guidelines when modifying workflow. Following are some examples:

Do not modify seeded data. Instead, always copy the seeded process and rename both the internal and display names before modifying the workflow.

Dependencies should not be violated. Order lines wait for booking before continuing their flow. Order lines should not invoice until after booking occurs.

Always specify RETRY_ONLY as the default error process for any workflow activity you define.

Use seeded WF functions whenever possible, like the “Reprice – Line” process which enables you to reprice an order line at any point in the order life cycle.

Do not issue a commit or rollback with a custom WF function as this can lead to data corruption.

Use Wait activities with very small relative times carefully as these can cause the Workflow Background Process to go into an infinite loop. See Note.345090.1 for more information.

15

Page 18: Workflow - Common Errors and Solutions_rev

3

Error Handling

There are two kinds of errors that Order and Line flows can run into: Expected Errors and Unexpected Errors.

Expected Errors

These are errors that business processes expect to run into and handle. These errors are easy to recover from.

The Incomplete Result at Booking (See Figure 2a) example is an expected error handled by Order Management with the Incomplete Activity result. The missing information can be provided via the form and the order or line can be progressed from Actions > Progress Order.

Unexpected Errors

These are errors that a business process does not expect under normal circumstances. Causes of unexpected errors include the following:

Data Integrity errors The table does not exist Rollback segments cannot be extended The package does not exist

All Order Management seed WF data is defined to use the “Retry Only” error process. This process determines the notification flow that Oracle Workflow starts when a workflow activity runs into an unexpected error.

The activity that encountered an unexpected error is marked with an Error status (in WF_ITEM_ACTIVITY_STATUSES) and a notification listing the error details is sent to the role specified in the OM Workflow Administrator item attribute. The Role item attribute WF_ADMINISTRATOR is defined for the header and

16

Page 19: Workflow - Common Errors and Solutions_rev

line work item, and is set as SYSADMIN. The error process sends notifications about the error to this role.

Once the problems have been corrected, the administrator can select the Retry option on the notification and complete it. This initiates a retry of the activity in error. The administrator can also opt to retry the activity from the Workflow Monitor.

Exception Management Feature

The Exception Management feature provides visibility of the Workflow errors to functional users. After correction of the issues recorded in the Message Framework, you can “Retry” the Workflow activity from the Sales Order/Quick Sales Order Window. It is available for Release 11.5.9 with Patch.3731146 and comes seeded in Release 11.5.10. All seeded flows started after this patch will use the OM Standard Error Process with Retry (OMERROR Item Type) as the Error Process. Following are some of the activities of this Order Management specific error handling process:

Update Process Messages – Adds the concurrent request ID to the message stack.

Check if Error Still Active – Check to see if the Error is still active.

Retry Error Activity – If the activity is still in error, it retries the activity.

The OMERROR flow must be added for customized activities in order to use the “Retry Activities in Error” functionality.

The following image depicts the OMERROR Item Type:

17

Page 20: Workflow - Common Errors and Solutions_rev

Figure 3a – OMERROR (OM Standard Error Process with Retry) Process

View Open Messages

When the OM Profile Option “OM: Show Process Messages Flag” is set to Yes, the Open Messages checkbox will display a checked value indicating that at least one message exists for the order.

The following image depicts the Open Messages checkbox:

Figure 3b – Open Messages Check Box is Checked

The following image depicts the Open Messages viewed from Actions > View Open Messages:

18

Page 21: Workflow - Common Errors and Solutions_rev

Figure 3c – View Open Messages from the Sales Order form

Some lines do not have Open Messages but are still in error when viewed from the Activities window.

Example: Order 2245 line 5.1 is at “Shipped” Status and has not progressed to “Closed” despite the Workflow Background Process for Deferred Activities being scheduled to run at periodic intervals.

The following image depicts an order line “stuck” at “Shipped”:

19

Page 22: Workflow - Common Errors and Solutions_rev

Figure 3d – Line “Stuck” at “Shipped” – Open Messages not Checked

The Workflow status view from the Activities Window shows the Fulfill activity in error status:

Figure 3e – Fulfill Activity in Error

20

Page 23: Workflow - Common Errors and Solutions_rev

Clicking on the red X to the left of the status column lists the error:

Figure 3f – Workflow Errors for the Fulfill Activity

It is unknown how the Fulfill activity resulted in error. However, Retrying the Activity in Error from the Sales Order form worked without taking any further action:

Figure 3g - Retry of the Fulfill Activity in Error

21

Page 24: Workflow - Common Errors and Solutions_rev

If the activity completes successfully, you will get a success message. Otherwise, you will see the error messages and the Retry will have to be repeated after fixing the errors.

The following image depicts a successful Retry of the Fulfill Activity in Error:

Figure 3h - Successful Retry of the Fulfill Activity in Error

The following image depicts the line in closed status after successfully retrying the Fulfill Activity in Error:

22

Page 25: Workflow - Common Errors and Solutions_rev

Figure 3i – Line after Retry of the Fulfill Activity in Error

For more information on the Exception Management Feature, please refer to Note. Note.113492.1.

Retry Activities in Error Concurrent Program

When a large number of workflows go into error, the Retry Activities Concurrent Program provides the ability to retry more than one workflow across orders in batch mode. It also purges WFERROR/OMERROR child workflows of OEOH/OEOL that are active but shouldn’t be (when the parent OEOH/OEOL don’t currently have any open errors). This enhancement to the Exception Management functionality was introduced in Patch.4420026 for Release 11.5.10. For Releases 11.5.9 and below, Patch.5248424 provides this functionality.

The Retry Activities in Error Concurrent Program can be run in Preview and Execute mode. The Preview mode is the default

23

Page 26: Workflow - Common Errors and Solutions_rev

and never executes the errored flow. It gives a report and count of the errored activities along with the errors in the log file. Users should use the messages included in the log output to identify and remedy as many errors as possible before resubmitting the program.

In the Execute Mode the errored flows are actually retried and then it displays the result of the Retry and any error messages in the log file.

Example: When there is an extent problem with the table ASO.ASO_ORDER_FEEDBACK_T large numbers of workflows can go into error upon booking and the following exception message will be logged:

ORA-20001:OE_Order_WF.Book_Order(OEOH,29058, 110960, RUN)Wf_Engine_Util.Function_Call (OE_BOOK_WF.BOOK_ORDER, OEOH, 29058,110960.RUN)

After fixing the table extent problem new records will successfully book. For the existent records in error, the Retry Activities in Error Concurrent Program can be used to mass retry the Book Activity.

Note: COGS Account generation can fail during Interface Trip Stop or RMA Receipt. The cause is usually improper setup or improper customization of the Generate Default Account process. In this case, use the cogs_11i.sql diagnostic script, available in Note.159998.1, to debug the process.

24

Page 27: Workflow - Common Errors and Solutions_rev

4

Data Corruption

Data corruption occurs when Order management Status is not in sync with OEOH or OEOL workflow item status. It means that the integrity of the data has been corrupted in some way. Order headers or lines with data corruption cannot be retried successfully using the Exception Management functionality. The invalid data must be fixed via direct update using SQL. In such cases, it is important to identify the exact nature of the data corruption and the number of affected transactions. Customers should not attempt to fix the data themselves but request a data fix script from Development/Support.

Note: Data fixes received from Support/Development via bugs are not generic, meaning they should not be applied for other similar issues without approval of Support/Development.

Causes of Data Corruption

Any process that is Workflow enabled may theoretically encounter data integrity issues. See the listing of line and header processes that are workflow enabled in Chapter 2. Without a reproduceable test case it is not possible to determine root cause. Often, the issue only becomes evident after the fact when the activity that needs to be performed cannot be completed (e.g. invoice not generated or inventory not decremented). Proactively, you can run the data validation script, BDE_OMSuiteDataChk.sql, available in Note.353991.1 for a comprehensive list of most known data integrity issues and take action before they become critical.

Data Corruption can be caused by not following standard customization practices when extending seeded OM Workflow Processes. Development has introduced a functionality called “OM Workflow Validation” to help OM customers evaluate and validate their proposed workflow extensions and customizations before implementing them. The validation can be done in the Transactions form or through the concurrent program “Validate

25

Page 28: Workflow - Common Errors and Solutions_rev

OM Workflow”. For a detailed description of the functionality, please review Note.312535.1. It is highly recommended that customers run this validation when implementing customized Workflows.

Workflow should never be expedited or skipped on problematic lines or headers. Workflow Activities that have been expedited will show a result of Force in the Status Monitor. Expediting or skipping workflow forces a violation of business rules and frequently causes data corruption (e.g. order lines close without shipping or invoicing). Improperly retrying (rewinding) the workflow activity can also lead to data corruption.

Workflow data or any other OM data should not be manually purged via SQL, as the link between OM and Workflow data will be lost. Furthermore, item properties should not be modified when there are open sales orders.

Patches to Prevent Skipping of Workflow

Patch.3968068 prevents users from skipping activities from the Workflow Status Monitor. After application of this patch, Users will only be able to skip activities of type Notification and Compare Activities such as Compare Date, Compare Time, etc. This patch is included in Release 11.5.10

Patch.2979522 sends a Workflow notification to the sysadmin and User when he/she skips a Workflow activity. It alerts and advises them not to skip activities in the future. This patch is included in Release 11.5.10 CU2.

Patches to Fix Known Data Corruption Issues

OM Development has released patches to fix known data corruption issues – these are not root cause fixes. Note.398822.1 maintains a current listing of all the data fix patches available.

26

Page 29: Workflow - Common Errors and Solutions_rev

Sometimes when an order line is cancelled the corresponding delivery line is not canceled. This results in data corruption between the Order Management and Shipping tables. Order Management Development has created Patch.5590106 for Release 11.5.10. It ensures that all dependent transactions are consistently marked canceled when a Sales Order line is canceled.

For the case where the Ship Line activity errors out (or is progressed to a state other than “Notified”) after the line is progressed AND has a corresponding closed delivery detail, retrying the activity will always fail. The Retry Activities in Error Concurrent Program for the SHIP_LINE Activity in Error will set the activity to Notified instead of retrying.

27

Page 30: Workflow - Common Errors and Solutions_rev

Data Corruption in Order Management

Following is a list of some of the known data corruption issues in Order Management:

Cannot book orders. Included ATO/PTO option lines cannot be progressed. RMA lines cannot be progressed: RMAs are stuck at

‘Awaiting Return’ or ‘Awaiting Return Disposition’ status when the quantity has already been received in Inventory. Apply Patch.5358811.

Lines in Fulfillment and Shipping Sets cannot be progressed.

Missing line flows: Workflow is created but not started. The order lines will stay in Entered or Book status and cannot be progressed. Data Fix needed.

Orphan OEOL workflows: Workflows exist for non-existing order lines. The headers will wait for non-existing lines to close resulting in orders that cannot be closed. Apply Patch.5601973.

Cannot invoice order/lines: Invoice Interface populates the ra_interface tables. The workflow error when this process fails looks like this:

Invoice Interface – Line, Deferred Invoice InterfaceException Error –2001 ORA-20001:OE_Invoice_WF.Invoice_Interface (OEOL,114252, 165668,RUN)WF_Engine_Util.Function_Call (OE_INVOICE_WF.INVOICE_INTERFACE,OEOL, 114252, 165668, RUN)

Data Corruption in Shipping

The concurrent program Interface Trip Stop (ITS) interfaces delivery details to Order Management and Inventory. Data integrity issues will result in ITS failing leaving pending transactions in OM, Inventory interface or both. Following is a list of some of the known data integrity issues resulting in failure of the Interface Trip Stop concurrent process:

28

Page 31: Workflow - Common Errors and Solutions_rev

Order line is closed but the delivery details are not yet interfaced.

Order line is cancelled but the delivery detail is not yet cancelled.

Lines with SHIP activity in ERROR status. The error message looks like this:

ORA-20002: 3133: Activity instance 'SHIP_LINE' is not a notified activity for item 'OEOL/9341448’.

Delivery details are shipped but the order lines are in “Awaiting Shipping” status.

Delivery details release status is ‘Shipped’ but the shipped quantity is 0 or null. ITS will complete with the ‘Batch is not yet fully interfaced to OM’ error.

Unprocessed Shipping Transactions

Unprocessed Shipping Transactions is perhaps the most critical type of data corruption in Shipping Execution because it prevents users from closing the Inventory Accounting Period at the end of the month. These are records that have been Ship Confirmed in the period being closed, but have not yet been interfaced to Inventory. When this occurs, order lines are closed but the Inventory Interface flag is set to P (pending). Running Interface Trip Stop with debug on will provide Users visibility to the exception/error. Please refer to Note.290432.1 for instructions on enabling debug.

Providing the missing information can result in successfully clearing the records. If re-executing Inventory Interface fails to clear the transactions, the issue would most likely require a data fix from Development.

For a full detailed listing of the pending Shipping Transactions use the diagnostic script BDEprdcls.sql available in Note.246467.1.

29

Page 32: Workflow - Common Errors and Solutions_rev

5

Workflow Performance Degradation

Oracle Workflow accesses several tables that can grow quite large over time. The size of these tables and indexes can adversely affect performance. Order Management leaves a lot runtime obsolete data in the Workflow tables that needs to be purged on a periodic basis using the ‘Purge Obsolete Workflow Runtime Data’ concurrent request. Only completed workflows that have no pending parent and children workflow can be purged. For example, the OEOH item type is the parent and OEOL is the child. Any WFERROR for the line or header is a child.

Improper workflow customization can lead to the workflow tables growing rapidly in size affecting performance. When care is not taken during customization, the workflow may iterate or loop unnecessarily creating a row in the tables wf_item_activity_statuses and wf_item_activity_statuses_h each time. This results in large activity status item keys and the tables growing rapidly in size.

Example: Wait activities with very small relative wait times can cause the WFBG to go into an infinite loop. Please refer to Note.345090.1. The following excerpt from the Oracle Order Management Workflow Validation White Paper explains (See Note.312535.1):

“Workflow Background process for deferred activities operates in a loop that dequeues activities from the deferred activity queue as long as there are any available. If a WAIT activity is defined in a loop within its parent process, and if there are only a few activities in the loop with it, they may all complete very quickly, and the same WAIT activity will be enqueued again in the deferred activity queue. If the Relative Time (for the Wait Mode of Relative Time) of the activity is short enough, it is plausible that the time will have expired before that very same Workflow Background process is done processing all other deferred activities ahead in the queue. In that case, it will process the WAIT activity again. If there are enough active

30

Page 33: Workflow - Common Errors and Solutions_rev

workflow items in the same situation, it can lead to the infinite loop and the concurrent program never completing, just like in the previous scenario.”

31

Page 34: Workflow - Common Errors and Solutions_rev

6

Data Needed to Request Data Fix

Should you encounter data integrity issues that cannot be resolved using Exception Management or the Retry Activities in Error concurrent program, please review Note.398822.1 for available data fix patches that might address your issue. Otherwise, please log a Service Request with Support and request a data fix script.

You will be asked to provide the following:

The Diagnostics AppsCheck for Order Management, Shipping Execution and Advanced Pricing.

The Diagnostics OM Order Information output and/or HTMomse11i for the entire order. Use Note.133464.1.

The OM Debug file for the failing process. See Note.121054.1 for enabling debug instructions.

The Interface Trip Stop debug file if the issue deals with ITS failing. See Note. 290432.1 for instructions for enabling debug in Shipping.

The BDEprdcl.sql output, if issue deals with inability to close inventory period because of unprocessed Shipping Transactions. Use Note.246467.1.

The workflow definition files (.wft) if encountering issue in custom workflow. Execute the following command to retrieve the .wft files: WFLOAD <apps/pwd>@<database name> 0 Y DOWNLOAD temp.wft OEOH OEOL.

The cogs_11i.sql output, if issue deals with COGS account generation failure.

Provide the status of the following patches in your system. For Releases 11.5.9 and below, following is the current list (subject to change):

Patch.3234019: ‘OM Prerequisite Patch’. Patch.4186964: ‘Jan 05 Shipping Integration, Logistics and

Fulfillment Patch’. Patch.3978133: ‘March 2005, Pricing and Accounting

Patch’.

32

Page 35: Workflow - Common Errors and Solutions_rev

Patch.3731146: ‘Order Management Exception Management Patch for 11i.ONT.I/11.5.9’.

Patch.5248424: ‘Order Management Exception Management Retry Activities in Error Concurrent Program’.

Patch.3968068: ‘Prevent Skip on Activities that are not of Type Compare or Notification’.

Patch.3381280: ‘Shipping Execution Rollup Patch – I’.

For Release 11.5.10, following is the current list (subject to change):

Patch.4957570: ‘April, 2006 Order Management Cumulative (11.5.10) Patch’.

Patch.5092890: ‘Consolidated ARU for 11.5.10 RMA issues’.

Patch.4420026: ‘Order Management Exception Management: Retry Activities in Error Concurrent Program. Included in OMRRP (4665900)’.

Conclusion

Workflow in Order Management provides you with powerful order processing and fulfillment capabilities. When Order Management’s seeded workflow processes do not meet your business needs care must be taken when extending workflow as improper customizations can lead to Data Corruption and/or Workflow Performance Degradation.

The Exception Management feature provides visibility of the Workflow errors to functional users. After correction of the issues recorded in the Message Framework, you can “Retry” the Workflow activity from the Sales Order/Quick Sales Order Window. The Retry Activities in Error concurrent program provides the ability to retry more than one workflow across orders in batch mode.

When the OM data is corrupted, it must be fixed via direct SQL updates. Customers should not attempt to fix the data using SQL, as this is an unsupported action. Furthermore, improper updates can lead to more data corruption. Instead, please log a

33

Page 36: Workflow - Common Errors and Solutions_rev

Service Request with Support providing the information outlined herein.

34

Page 37: Workflow - Common Errors and Solutions_rev

Appendix A

Patch.2979522: If user skips WF activity after applying this patch, workflow notifications will be sent to sysadmin and the skipper to alert them of the skip activity and advise them to not skip activity in the future. Recommended for all customers.

Patch.3234019: OM Prerequisite Patch.

Patch.3381280: Shipping Execution Rollup Patch – I.

Patch.3731146: Order Management Exception Management Patch for 11i.ONT/11.5.9.

Patch.3968068: Prevent Skip on Activities that are not of Type Compare or Notification.

Patch.3978133: March 2005, Pricing and Accounting Patch.

Patch.4186964: Jan 05 Shipping Integration, Logistics and Fulfillment Patch.

Patch.4420026: Order Management Exception Management: Retry Activities in Error Concurrent Program. Included in OMRRP (4665900).

Patch.4957570: April, 2006 Order Management Cumulative (11.5.10) Patch.

Patch.5092890: Consolidated ARU for 11.5.10 RMA issues.

Patch.5248424: Order Management Exception Management Retry Activities in Error Concurrent Program.

Patch.5358811: This patch contains generic data fix scripts for known issues in RMA Receiving.

Patch.5590106: Ensure data consistency while canceling Sales Order lines. This patch is recommended, if it is randomly observed that Interface Trip Stop process fails because the corresponding Sales Order line is already canceled.

35

Page 38: Workflow - Common Errors and Solutions_rev

Patch.5601973: This patch updates Open Flag and Flow Status Code to close open orders with no open lines or workflows.

Patch.5604904: This patch purges out-of-date OMERROR and WFERROR items referencing OEOH/OEOL workflows.

36

Page 39: Workflow - Common Errors and Solutions_rev

References

1. Note.113492.1: Oracle Order Management Exception Management

2. Note.121054.1: How to Generate a Debug file in OM3. Note.133464.1: OMSE11i.SQL release 11i script.4. Note.130038.1: Using Workflow in Oracle Order Management5. Note.159998.1: Debugging Cost of Goods Sold Account

(cogs_11i.sql).6. Note.246467.1: BDEprdcls.sql – CstCheck.sql Diagnostics

Scripts7. Note.290432.1: How to Create a Debug File in Shipping

Execution8. Note.312535.1: Oracle Order Management Workflow

Validation9. Note.345090.1: Workflow Background Process for OEOH is

Looping in the Workflow Deferred Queue.10. Note. 353991.1: OMSuiteDataChk.sql11. Note.398822.1: Order Management Suite Data Fix Script

Patch Library12. Note.402144.1: FAQ: Best Practices for Custom Order

Entry Workflow Design13. Note.414314.1: How to Generate the COGS Account from

the Order Type

37

Page 40: Workflow - Common Errors and Solutions_rev

Common Errors and Solutions when Using Workflow in Order Management

Created March 2007Author: Mercedes VizuetaReviewer: Robert David

This document is provided for informational purposesonly and the information herein is subject to changewithout notice. Please report any errors herein toOracle Corporation. Oracle Corporation does notprovide any warranties covering and specificallydisclaims any liability in connection with this document.Oracle is a registered trademark, and Oracle Order Management is atrademark(s) or registered trademark(s) of Oracle corporation.

38