Category Archives: OracleWorkflow

Cancel open Notifications and Abort Workflow

Cancel open Notifications and Abort Workflow

Below script is used to cancel all open workflow notifications and abort the workflow by passing Workflow Item Type and also Message type. Continue reading

Related posts:

Workflow Tables

Important Oracle Work Flow Related tables:

Workflow Tables:

–Workflow Users/Roles
SELECT * FROM WF_USERS where name = ‘XXUSER’;
SELECT * FROM WF_ROLES where name = ‘XXUSER’;
SELECT * FROM WF_USER_ROLES where user_name = ‘XXUSER’;
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS where user_name = ‘XXUSER’;

–Workflow Item Name and Attributes
SELECT * FROM WF_ITEM_TYPES where name = ‘OKCAUKAP’; –PO Approval (POAPPRV) ; PO Requisition Approval (REQAPPRV);
–OM Order Header (OEOH); OM Order Line (OEOL)
SELECT * FROM WF_ITEM_TYPES_VL where name = ‘OKCAUKAP’;
SELECT * FROM WF_ITEM_TYPES_VL where display_name like ‘%Approval’;
SELECT * FROM WF_ITEM_TYPES_VL where description like ‘%Order%’;
SELECT * FROM WF_ITEM_ATTRIBUTES where item_type = ‘OKCAUKAP’;
SELECT * FROM WF_ITEM_ATTRIBUTES_TL;

–Workflow Functions
SELECT * FROM WF_ACTIVITIES where item_type = ‘OKCAUKAP’ and version = 1;
SELECT * FROM WF_ACTIVITIES_TL where item_type = ‘OKCAUKAP’;
SELECT * FROM WF_ACTIVITY_ATTRIBUTES where activity_name = ‘CREATE_SOA’;
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL;
SELECT * FROM WF_ACTIVITY_TRANSITIONS;

–Workflow Lookups
SELECT * FROM WF_LOOKUPS_TL where lookup_type = ‘OKC_DB_RETRY’;

–Workflow Messages
SELECT * FROM WF_MESSAGES where type = ‘OKCAUKAP’ and name = ‘APPROVE_CONTRACT’;
SELECT * FROM WF_MESSAGES_TL where type = ‘OKCAUKAP’ and name = ‘APPROVE_CONTRACT’;
SELECT * FROM WF_MESSAGE_ATTRIBUTES where message_type = ‘OKCAUKAP’ and message_name = ‘APPROVE_CONTRACT’;
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL where message_type = ‘OKCAUKAP’ and message_name = ‘APPROVE_CONTRACT’;

–Workflow Particular item run and values
SELECT * FROM WF_ITEMS where item_type = ‘OKCAUKAP’ order by begin_date desc;
–USER_KEY/ITEM_KEY => select * from okc_k_headers_b where contract_number = ‘20683312’;
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES where item_type = ‘OKCAUKAP’ and item_key=’20683312′;
SELECT * FROM WF_PROCESS_ACTIVITIES where process_item_type = ‘OKCAUKAP’ and process_version = 1;

SELECT * FROM WF_NOTIFICATIONS WHERE MESSAGE_TYPE = ‘OKCAUKAP’ AND MESSAGE_NAME = ‘APPROVE_CONTRACT’;
SELECT * FROM WF_NOTIFICATION_ATTRIBUTES where notification_id = ;

SELECT * FROM WF_DEFERRED;


SELECT * FROM wf_user_role_assignments
SELECT * FROM wf_user_roles
SELECT * FROM wf_roles
SELECT * FROM wf_items
SELECT * FROM wf_item_attributes
SELECT * FROM wf_item_attribute_values
SELECT * FROM wf_item_attributes_tl
SELECT * FROM wf_activities
SELECT * FROM wf_activities_tl
SELECT * FROM wf_activity_attributes
SELECT * FROM wf_activity_attributes_tl
SELECT * FROM wf_activity_transitions
SELECT * FROM wf_deferred–wf_control

SELECT * FROM WF_ACTIVITY_ATTR_VALUES
WHERE NAME LIKE ‘%MAST%’
AND PROCESS_ACTIVITY_ID
IN(
SELECT *– PROCESS_ACTIVITY
FROM WF_ITEM_ACTIVITY_STATUSES
WHERE ITEM_TYPE = ‘OEOH’
AND ITEM_KEY =’62348′
)

SELECT * FROM WF_ITEM_TYPES
SELECT * FROM WF_LOOKUPS_TL
SELECT * FROM WF_NOTIFICATIONS
WHERE MESSAGE_TYPE =’TEST’
ORDER BY BEGIN_DATE DESC

SELECT * FROM WF_NOTIFICATION_ATTRIBUTES
SELECT * FROM WF_MESSAGES
SELECT * FROM WF_MESSAGES_TL
SELECT * FROM WF_MESSAGE_ATTRIBUTES
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL
SELECT * FROM WF_ETS
SELECT * FROM WF_PROCESS_ACTIVITIES

Related posts:

Workflow with Approval Notification

Creating WF Local Roles:

Adhoc roles can be created through PL/SQL from database or they can be created from Applications using User Management Responsibility. If PL/SQL is used to create roles, all user names and role names in UPPER case to avoid some errors:

Script to Create an Adhoc Role:

 Script to validate WF Roles:

 Script to Add user to an already existing Adhoc Role:

 Script to Remove user from an existing Adhoc Role:

Using Adhoc roles in workflow notifications:
Once the Roles are created in the database we can load the Roles from database as discussed from Oracle Worflow Builder as below:

Navigation: File > Load Roles from Database

Select roles you want to use and then click OK.

NEW_XAOA_NF028

NEW_XAOA_NF029

Open the notification properties and then navigate to node tab, select performer as the role you just created and loaded from database.

Modified Package Code:

Sample Code to test the workflow:

 Sample scripts to test the Workflow:

Once triggered See the below Approval Notification for either Workflow –> Notifications or directly on the screen depending on the Profile Option setup:

NEW_XAOA_NF023

Open the Notification:

NEW_XAOA_NF024

Once Approved see the Activity history or Status Diagram from Status Monitor. This can be done at any stage of the workflow once kicked off. Usually while debugging this is the first place we need to check and then check in wf tables.

NEW_XAOA_NF025

NEW_XAOA_NF026

NEW_XAOA_NF027

 

Some of the workflow tables:

WF_LOOKUP_TYPES_TL
WF_MESSAGES
WF_MESSAGE_ATTRIBUTES
WF_NOTIFICATION_ATTRIBUTES
WF_ITEM_ATTRIBUTES
WF_ITEM_ACTIVITY_STATUSES

WF_ITEM_ATTRIBUTE_VALUES

Please respond if any questions.

Related posts:

Workflow with Approval Notification

Sample workflow with Approval notification:

In the node change the Performer type to Item attribute as we intent to set the attribute value from function. We can also load the roles from database and add it here. The timeout is also important in a notification as if the user does not respond in a particular time period we can control the workflow path. The Time out can also be an item attribute.

NEW_XAOA_NF019

Drag another end so that when in the notification when user selects another result which is Reject, it should also flow till the end. Use can also use default as when there are multiple results, then other than the result mentioned in the flow rest all comes under this flow. Any can also be chosen if the workflow should also follow this path in either case.

NEW_XAOA_NF020

Validate and save the workflow in the database or on the desktop and upload it using WFLOAD.

NEW_XAOA_NF021

NEW_XAOA_NF022

Once the Workflow is saved in the database then validate using following scripts:

Next

Related posts:

Workflow with Approval Notification

Sample workflow with Approval Notification:

Create a new notification by using right click on the notifications menu in Custom workflow:

NEW_XAOA_NF010

Set the Notification properties:

NEW_XAOA_NF011

Select the Message which we have created.

Drag the item attribute to the message as we are using the item attribute in the message:

NEW_XAOA_NF012

Now edit the Main Process so to add the new Notification:

NEW_XAOA_NF013

Delete the link between existing Function and End to add the Notification in between:

NEW_XAOA_NF014

NEW_XAOA_NF015

Form a link between Function and notification:

NEW_XAOA_NF016

Now when creating the link between Notification and end, The workflow asks for the result type based upon the Result selected in the workflow notification. If the result of the notification is custom lookup it prompts for the custom lookup values in the lookup:

NEW_XAOA_NF017

Edit the properties of the Notification to add the performer:

NEW_XAOA_NF018

Next

Related posts: