Useful Tricks for Oracle Workflow

1
Useful Tricks for Oracle Workflow

• Robert J. Rentz
• Senior Director
• Oracle Practice

2
Overview

• Oracle Workflow is a critical part of all 11i
  implementations
• Processes may error and result in notifications
  that are looking for a response
• Owner of the process may not be the recipient of
  the notification telling them of the error
• The actual problem may be difficult to determine
• The solution to the problem may be even more
  difficult to determine
• Solutions that require changes to the attributes
  that govern the process do not have obvious
  solutions
• Large numbers of errors are tedious to fix

3
Agenda

• Process and Function Definition
• Notification definition
• How notifications are linked to processes
• How to find process in error
• How to find notifications that contain the errors
• How to fix process errors using Admin Screen
• How to respond to notifications using Admin
  Screen
• What happens under the covers
• How can you write scripts to handle mass errors
  or responses
• Best practices for staying on top of errors
• Questions

4
Process and Function Definition

• Function
• A workflow activity linked to a PLSQL function
  that returns a result. That result can be NULL
  (NULL) or a result from a Lookup Type
• Process
• A series of functions and/or processes linked
  together containing at least one START and one
  END activity

5
Notification Definition

• A workflow activity that is tied to a message.
  The recipient of the message is linked to the
  PERFORMER of the notification.
• Processes that error out with Oracle error
  messages are assigned to the workflow Item Type
  called SystemError (WFERROR)
• The SYSADMIN user (role in workflow lingo) is the
  performer of all SystemError notifications

6
How notifications are linked to processes

• Notifications are functions within a process
• Messages are tied to notifications
• Normal messages occur when a notification is
  part of the process.
• The performer of normal processes is the role
  that initiated the process
• Error messages occur when a function encounters
  a database error (ORA-xxxxx)
• Error messages are NOT part of a defined
  process but are by-products of the error handling
  definition of a function

7
How notifications are linked to
processes(WF_ITEM_ACTIVITY_STATUSES_V)
8
How to find process in error

• Sign onto the Workflow Admin Screen
• Find Processes
• Highlight Active, All, and Error

9
How to find process in error
10
How to find process in error
11
How to find notifications that contain the errors

• Sign onto the Workflow Admin Screen
• Find Notifications
• Choose System Error

12
How to find notifications that contain the errors
13
How to find notifications that contain the errors
14
How to fix process errors using Admin Screen

• Owner of process and error
• Navigate to the process Error message appears
• Click on the envelope icon
• Reply with Retry
• Corrects process and closes notification
• Owner of process error does not appear
• Navigate to the process and View Diagram
• Zoom down to the process in error (red box)
• Reply with Retry
• Corrects process, leaves notification open

15
How to fix process errors using Admin Screen
(Owner of process and error)
16
How to fix process errors using Admin Screen
(Owner of process and error)
17
How to fix process errors using Admin Screen
(Owner of process, not error)
18
How to fix process errors using Admin Screen
(Owner of process, not error)
19
How to respond to notifications using Admin Screen

• Owner of process and/or error
• Navigate to the notification
• Click on the Subject link
• Reply with Retry
• Corrects process and closes notification

20
How to respond to notifications using Admin Screen
21
How to respond to notifications using Admin Screen
22
What happens under the covers

• WFERROR process receives the RESULT called RETRY
• WFERROR will link back to the Parent Item and Key
  and execute the function linked to the Error
  activity
• If function succeeds, WFERRORs Parent Item Type
  and Key continue to the subsequent workflow steps
• If function fails again, another WFERROR workflow
  is created with a different WFERROR item key that
  is linked again to the same Parent Item Type and
  Key of the Error activity

23
What happens under the covers(WF_ITEM_ACTIVITY_
STATUSES_V)
24
How can you write scripts to handle mass errors

• Errors
• The necessary keys to the Parent Item Type and
  Key are contained in the Subject of the
  SystemError notification along with the Oracle
  Error Message (e.g. ORA-01403)
• The standard workflow function called
  WF_NOTIFICATION.RESPOND is the function called
  when manually performing a Retry
• Write a script that locates the error based on
  subject
• Run WF_NOTIFICATION.RESPOND with the RETRY
  result for all notifications that meet the
  criteria
• Commit your transactions

25
How can you write scripts to handle mass errors
(Sample Code)
declare p_order_number number 1
p_item_type varchar2(20) '2' p_act_name
varchar2(60) '3' p_result
varchar2(60) '4' cursor c3 is select from
wf_notifications where SUBJECT like 'Error in
Workflow'p_act_name'' and
substr(SUBJECT,19,4)'OEOL and status'OPEN'
c3rec c3rowtype begin if p_item_type'WFERROR'
then if p_act_name'ORA-01403' or
p_act_name'ORA-20001' or p_act_name'ORA-06502'
or p_act_name'ORA-04063' or p_act_name
'ORA-04021' or p_act_name 'ORA-04045'then
open c3 loop fetch c3 into c3rec exit when
c3notfound wf_notification.SetAttrText(c3rec.n
otification_id,'RESULT',p_result)
wf_notification.respond(c3rec.notification_id,null
,null) dbms_output.put_line('Notification
updated 'to_char(c3rec.notification_id)) end
loop close c3 end ifend if end
26
How can you write scripts to handle mass errors
(Sample Code)

• SQLPlus Command in the APPS account
• _at_cusntrep 0 WFERROR ORA-01403 RETRY

27
How can you write scripts to handle mass responses

• Responses
• Identify the internal names for the function and
  valid responses using Workflow Admin Screen
• Write a script that identifies the records with
  the function that is in a status called
  NOTIFIED
• Value the Workflow Attribute called RESULT with
  the internal name of the response you wish to
  pass
• Invoke the WF_NOTIFICATION.RESPOND to pass the
  result to the workflow engine
• Commit your transactions

28
How can you write scripts to handle mass
responses (Sample Code)
declare p_order_number number 1
p_item_type varchar2(20) '2' p_act_name
varchar2(60) '3' p_result
varchar2(60) '4' cursor c1 is select
item_type,item_key,activity_name,notification_id
from wf_item_activity_statuses_v where
item_typep_item_type and item_key in (select
to_char( decode( item_type, 'OEOL', ol.line_id,
ol.header_id)) from oe_order_headers_all
oh,oe_order_lines_all ol where oh.order_number
decode(p_order_number,0,oh.order_number,p_order_nu
mber) and ol.header_idoh.header_id) and
activity_namep_act_name and
activity_status_code'NOTIFIED' c1rec
c1rowtype begin if p_item_type in
('OEOH','OEOL') then open c1 loop fetch c1
into c1rec exit when c1notfound
wf_notification.SetAttrText(c1rec.notification_id,
'RESULT',p_result) wf_notification.respond(c1re
c.notification_id,null,null) end loop close
c1end if end
29
How can you write scripts to handle mass
responses (Sample Code)

• SQLPlus Command in the APPS account
• _at_cusnotrp 12345 OEOL APPRV_ORD APPROVE
• Single Order
• _at_cusnotrp 0 OEOL APPRV_ORD APPROVE
• All Orders

30
Best practices for staying on top of errors

• Route your messages to someone who is responsible
  for the particular workflow process
• Check failed workflows on a daily basis
• Check System Error notifications on a daily
  basis
• Have scripts ready to handle mass Retry
• Never Skip unless you know what the adverse
  consequences are
• Write an Alert to e-mail a manager if the number
  of SystemError messages exceed an acceptable
  threshold
• Tailor your sign-in page to display notifications
  (work list) automatically

31
(No Transcript)
32
Questions
33
Useful Tricks for Oracle Workflow

• Robert J. Rentz
• Senior Director
• Oracle Practice

robert.rentz _at_ us.ssiworldwide.com [phone]