Oracle Workflow Above and Beyond

Oracle Workflow – Above and Beyond

Shreyas Shah

Abacus Business Solutions, Inc.

Introduction

Oracle Workflow is an excellent non-invasive tool to extend the capabilities of the applications. You may model your process and information flow using this tool. Like most software in its early releases, Oracle Workflow has some shortcomings. This paper highlights some of the shortcomings and proposes elegant solutions. A complex engineering change order (ECO) process, as implemented and live at a Fortune 500 customer, is used to exemplify these solutions. The emphasis is on technical details. Each of these solutions is easily applicable to any workflow and may be designed to accommodate all your workflow processes.

Please note that code snippets provided are only for guidance, and not necessarily complete working versions.

Dynamic Notification Roles

Our requirement was for the Engineering Manager to have the ability to edit the default roles assigned to the notifications during the ECO workflow execution. The defaults are determined based on several criteria like material impact, priority of the ECO …

Oracle workflow works well with predefined lists or roles for every notification activity. There is no provision to manage the roles and names within the roles for each instantiation of a workflow process. Specifically, the ability to update user names within roles to impact only specific instance of the process is non-existent.

A custom form to auto-populate local roles and users is provided for each instance of the workflow. Relevant users during the process execution may update the roles to impact only the one instance of the process.

Steps required are as follows:

Define/setup your default lists using any standard mechanism like PO Approval hierarchy or Engineering Approval Lists. In our case, we used the Engineering Approval Lists.

Create a custom table to store a copy of the default role and users for each notification activity in the process.

CREATE TABLE CUST_WF_LISTS

(

LIST_ID NUMBER NOT NULL,

ROLE_TYPE VARCHAR2(150) NOT NULL,

EMPLOYEE_ID NUMBER NOT NULL,

CREATED_BY NUMBER,

CREATION_DATE DATE,

LAST_UPDATED_BY NUMBER,

LAST_UPDATE_DATE DATE,

LAST_UPDATE_LOGIN NUMBER

)

Modify the WF_ROLES view to include the custom table.

Add the following to your WF_ROLES view

UNION ALL

SELECT DISTINCT ‘ECO:’ || LIST_ID,

ROLE_TYPE DISPLAY_NAME,

ROLE_TYPE DESCRIPTION,

'QUERY' NOTIFICATION_PREFERENCE,

'AMERICAN' LANGUAGE,

'AMERICA' TERRITORY,

NULL EMAIL_ADDRESS,

NULL FAX,

ROLE_TYPE ORIG_SYSTEM,

LIST_ID ORIG_SYSTEM_ID

FROM CUST_WF_LISTS

Create a custom form to allow updates to the roles and users for specific process. You may build the necessary security on this form to allow/disallow updates to the roles by specific responsibilities. We designed the form to represent the notification activities as ‘Participant Type’ alternate regions, the default user list on the right, and the instance specific list on the left. Eligible users within the process are allowed to modify the list on the left. The modified list is stored in the custom table CUST_WF_LISTS. Additionally, we provided capability to refresh all defaults or reset specific lists to the default.

Each notification activity is assigned a role determined by item attribute. These item attributes are set at the start of the workflow as roles names populated in the WF_CUST_LISTS table for each process.

Open Notification Reassignments

Our business requirement was to provide capability to do a ‘permanent’ reassignment of the user in any ‘Participant Type’ role. For example, if an Approver reassigned his notification to a different user, then the new assignee must receive any future notifications to the Approvers role for that ECO.

Oracle Workflow provides very primitive forward/reassign capability. The reassign capability merely allows forwarding that specific notification to a new user. You cannot make permanent reassignment of users to allow any future notifications, for the specific workflow instance, to be sent to the new assignee.

A custom form provides query capability to display open notifications. Managers have the ability to reassign one or more open notifications, which makes permanent change to the workflow specific role. This action reassigns the open notification to the new member, and any new notifications using the same role, are automatically sent to the new assignee.

Clicking the Reassign button, updates the custom assignment list table in order to make the permanent reassignment.

UPDATE CUST_WF_LISTS

SET EMPLOYEE_ID = :NEW_ASSIGNEE

WHERE LIST_ID = :LIST_ID

AND EMPLOYEE_ID = :OLD_ASSIGNEE

Manufacturing Calendar Based Time Outs

Our requirement was to provide people sufficient time to respond to notifications before escalating. Hence, we needed to account for weekends, holidays, and shutdown days before escalation procedures kicked in.

Oracle Workflow provides some basic timeout activities. This does not take into account any weekend, holidays or non-work days. Also, there is no mechanism to set the timeout dynamically.

A simple, parallel branch was set up for the dynamic wait. The workflow snippet below illustrates the parallel process. The Set Timeout function activity computes the relative wait time based on the manufacturing calendar and the priority on the notification. The Wait activity relative wait time is set based on the item attribute value computed in the Set Timeout activity. A “race” is then started between the two branches. Workflow automatically terminates activities in the parallel branch either on arrival of the wait time or response to the notification.

In order to determine the next work day based on the time out period, use the following sql statement.

SELECT bcd1.calendar_date

FROM bom_calendar_dates bcd1,

bom_calendar_dates bcd2

WHERE bcd1.calendar_code = 'SCLA MFG'

AND bcd1.calendar_code = bcd2.calendar_code

AND bcd1.seq_num = bcd2.next_seq_num + p_days

AND bcd2.calendar_date = trunc(<START_date>)

You may add an additional level of sophistication by incorporating manufacturing shift information.

Comments and Issues Log

Our business requirement was to provide essentially any employee the ability to log a comment or an issue on any ECO. A comment should serve as a mere feedback/opinion; whereas an issue should halt further processing of the ECO until the issue is either resolved or rejected.

Oracle Workflow provides no capability for users not directly assigned to any workflow roles to provide feedback during the process. Only people with open notifications may either log comments or hold up the processing.

A custom form provides the ability, for any valid user, to log issues that put a block on the specific process. For each issue/comment, user must select the people to be notified. The issue/comment is stored in a custom table (CUST_ECO_ISSUES). An asynchronous workflow process records and documents resolution to the specific issues. Open issues prevent further processing until resolved, which leads to greater control, flexibility, and reduced cycle times with fewer outright rejections.

Workflow Process Log

Workflow provides utility SQL scripts to status the process. Additionally, some workflow enabled application modules provide forms to view the workflow status. Yet, these provide mostly technical workflow execution information. For example, most function activities used to model conditionals are not important to the user. In fact, they merely confuse the end user. Users must have a concise and understandable view of the process. The Java workflow monitor is too slow and cumbersome to use.

A custom form to display user friendly status of the workflow was designed. It only displays notification activities with data that are easily understood and process steps that users may relate to. Additionally, we also combined statusing of the issues/comments workflow, with the ECO workflow, to provide a comprehensive picture of the audit trail of the process.

The custom form is based on a view that combines notification status of the ECO workflow and Issues workflow.

CREATE OR REPLACE VIEW cust_eng_eco_history_v ASSELECT /* ECO workflow */

<column list>,

wn.context context

FROM fnd_user usr,

wf_notification_attributes wna1,

wf_messages_tl wmtl,

wf_notifications wn,

wf_message_attributes Wma,

wf_lookups_tl wl

WHERE wn.notification_id = wna1.notification_id(+)

AND wn.recipient_role = usr.user_name

AND wna1.name(+) = 'RESULT'

AND wmtl.type = wn.message_type

AND wmtl.name = wn.message_name

AND wma.message_type(+) = wn.message_type

AND wma.message_name(+) = wn.message_name

AND wma.name(+) = 'RESULT'

AND (wma.format = wl.lookup_type OR wl.lookup_type is null)

AND wna1.text_value = wl.lookup_code(+)

UNION

SELECT /* issues workflow */

<column list>,

wn.context context

FROM fnd_user usr,

wf_notification_attributes wna1,

wf_messages_tl wmtl,

wf_notifications wn,

wf_message_attributes Wma,

wf_lookups_tl wl,

xxam_eng_eco_issues xeei

WHERE wn.notification_id = wna1.notification_id

AND wn.recipient_role = usr.user_name

AND wna1.name = 'RESULT'

AND wmtl.type = wn.message_type

AND wmtl.name = wn.message_name

AND wma.message_type = wn.message_type

AND wma.message_name = wn.message_name

AND wma.name = 'RESULT'

AND wma.format = wl.lookup_type

AND wna1.text_value = wl.lookup_code

AND wn.context LIKE 'XXAM_EIL:'||xeei.issue_cmt_id||'-%'

Dynamic Notification Priorities

Our business requirement was to set priorities on notifications based on the ECO priority. For example, ECO related to safety must have the highest priority.

In Oracle Workflow you must set priority on the notifications at design time. There is no provision to set priorities at run time. Yet, in real life, certain activities may be expedited and require higher priority and attention. In order to set the priority during the execution of the workflow, we update the reserved attribute that stores the priority information.

wf_engine.setItemAttrNumber (p_itemtype, p_itemkey, '#PRIORITY', <priority_value>)

Alternatively, you may set the priority by updating the priority column in the WF_NOTIFICATIONS table.

Notification Due Dates

Our business requirement was to for the due dates on notifications to be dynamically derived based priority. Highest priority notifications require responses in a day, while the lowest priority in 5 days.

Oracle Workflow provides no mechanism to set due dates at run time. Oracle Workflow sets due dates only on notifications that have timeouts and computes the due date based on time outs.

In order to set the priority, set up a parallel activity to update the notification attributes directly. The following displays a section of the workflow. The Set Notification Columns runs a procedure to update the due date on the notifications that just generated. This activity must be done in parallel for response notifications.

UPDATE WF_NOTIFICATIONS

SET DUE_DATE = DECODE(<PRIORITY>, 1, SYSDATE+1, 2, SYSDATE+3, SYSDATE+5)

WHERE notification_id IN (SELECT wn.notification_id

FROM wf_activity_transitions wat1,

wf_activity_transitions wat2,

wf_item_activity_statuses wias,

wf_notifications wn

WHERE wn.group_id = wias.notification_id

AND wias.item_type = <p_itemtype>

AND wias.item_key = <p_itemkey>

AND wias.process_activity = wat2.to_process_activity

AND wat1.from_process_activity = wat2.from_process_activity

AND wat2.to_process_activity != wat1.to_process_activity

AND wat1.to_process_activity = <p_actid>);

Purging

With the volume of workflow processes, our workflow tables were growing at an alarming rate. In order to manage the tables, and yet keep relevant approval history, we needed to purge selective workflow process data.

Workflow provides a utility to purge completed workflow information. This utility, though, purges the entire audit trail of the workflow process.

In order to minimize growth of the workflow tables, and retain sufficient data to review history and run key performance indicators (KPIs), we developed a custom purge script. This custom purge script helped us curtail the table size growth by a factor of greater than 100!

Delete from the history table

delete from WF_ITEM_ACTIVITY_STATUSES_H WIASwhere ITEM_TYPE like <itemtype>and ITEM_KEY like <itemkey>

And not exists (select null

from WF_ITEMS WI

where WI.ITEM_TYPE = WIAS.ITEM_TYPE

and WI.ITEM_KEY = WIAS.ITEM_KEY

and WI.END_DATE is null)

and not (exists (select 1

from wf_notifications wn,

wf_notification_attributes wna

where wn.notification_id = wias.notification_id

and wn.notification_id = wna.notification_id

and wna.name = 'RESULT')

and wias.item_type in (<your wf item types>))

Delete from the activity statuses table

delete from WF_ITEM_ACTIVITY_STATUSES WIAS

where ITEM_TYPE like <itemtype>

and ITEM_KEY like <itemkey>

and not exists (select null

from WF_ITEMS WI

where WI.ITEM_TYPE = WIAS.ITEM_TYPE

and WI.ITEM_KEY = WIAS.ITEM_KEY

and WI.END_DATE is null)

Delete from the item attribute values table

delete from WF_ITEM_ATTRIBUTE_VALUES WIAVwhere item_type like <itemtype>

and item_key like <itemkey>

and not exists (select null from WF_ITEMS WI

where WI.ITEM_TYPE = WIAV.ITEM_TYPE

and WI.ITEM_KEY = WIAV.ITEM_KEY

and WI.END_DATE is null)

Delete from the notification attributes table

delete from WF_NOTIFICATION_ATTRIBUTES WNAwhere WNA.NOTIFICATION_ID in

(select wn.notification_id

From wf_notifications wn where wn.CONTEXT like

<itemtype> || ':' || <itemkey> ||':%'

and not exists (select NULL from WF_ITEMS WI where WI.ITEM_TYPE =

SUBSTR(WN.CONTEXT, 1,

INSTR(WN.CONTEXT, ':')-1) and WI.ITEM_KEY = SUBSTR(WN.CONTEXT,

INSTR(WN.CONTEXT, ':')+1, INSTR(WN.CONTEXT, ':', 1, 2)-INSTR(WN.CONTEXT, ':')-1)

and WI.END_DATE IS NULL)

and not exists (select null

from WF_NOTIFICATIONS WN

where WN.NOTIFICATION_ID = WNA.NOTIFICATION_ID

and WN.MESSAGE_TYPE IN ('ECO_APP', 'XXAM_EIL'))

and WNA.NAME IN ('RESULT', <any other custom results>))

A longer-term strategy should involve archiving of appropriate data.

Conclusion

Oracle Workflow, with some customizations, provides a very flexible and robust mechanism to build non-invasive customizations to Oracle Applications. Many of the techniques discussed in this paper are easily extended to other workflows. With some customizations, you can leverage the power of workflow to enhance the applications to better fit your business needs. Go above and beyond what workflow provides to implement flexible solutions!