Category Archives: OracleWorkflow

Workflow with Approval Notification

Sample Oracle workflow with an Approval Notification:

Open the existing workflow which is created earlier from the database or by using WFLOAD:

Save the file on the desktop:

Once the workflow file is saved on the desktop, Now we will be modifying that wft.

The target is to send an email notification to one person using Oracle workflow.

For this as step one we are creating an Item Attribute which is similar to a Global Variable in a PLSQL Package. There are different types of item attributes like Text, number, date, lookup, form, url, document, role, attribute and event. Here we are selecting a text. We can even go for a Role type but then we need to create a role and assign it then to the attribute.

Set the item attribute properties:

Once the attribute is created then create a new message which needs to be send in the notification.

Set the properties of the new message:

In the body the actual email notification Body and in the subject the email notification subject goes in. We can also modify using html body if we need to enter the data in tables with different color coding, .etc. Here in the message body we have appended the Item attribute just for reference on how to add item attributes which change dynamically in the workflow.

As we are using standard Approve/Reject notification we are selecting an existing lookup. Else if we want to create a new, we can always create a new lookup with its values and use it here.

Add the display name and description which are mandatory.

+127

Creating a Workflow from scratch

XAOATEST Code and Details

Workflow with Approval Notification

XAOATEST Code and Details

2 Replies

PLSQL Package used in XAOATEST Workflow:

CREATE OR REPLACE PACKAGE XXAOA_TEST_WF_PKG

PROCEDURE INSERT_PROC( itemtype IN VARCHAR2,

itemkey IN VARCHAR2,

actid IN NUMBER,

funcmode IN VARCHAR2,

resultout IN OUT NOCOPY VARCHAR2

);

PROCEDURE LAUNCH_WORKFLOW ( itemtype IN VARCHAR2,

itemkey IN VARCHAR2,

process IN VARCHAR2

);

END XXAOA_TEST_WF_PKG;

CREATE OR REPLACE PACKAGE BODY XXAOA_TEST_WF_PKG

PROCEDURE INSERT_PROC( itemtype IN VARCHAR2,

itemkey IN VARCHAR2,

actid IN NUMBER,

funcmode IN VARCHAR2,

resultout IN OUT NOCOPY VARCHAR2)

BEGIN

INSERT INTO xxaoa_test_wf VALUES (itemtype,itemkey,actid,funcmode,resultout);

resultout := 'Success';

EXCEPTION

WHEN OTHERS

THEN

resultout := 'Error';

END INSERT_PROC;

PROCEDURE LAUNCH_WORKFLOW ( itemtype IN VARCHAR2,

itemkey IN VARCHAR2,

process IN VARCHAR2

)

BEGIN

WF_ENGINE.Threshold := -1;

WF_ENGINE.CREATEPROCESS( itemtype,

itemkey ,

process

);

WF_ENGINE.STARTPROCESS ( itemtype,

itemkey

);

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('Error at LAUNCH_WORKFLOW: '||SQLERRM);

END LAUNCH_WORKFLOW;

END XXAOA_TEST_WF_PKG;

Sample Script to Kickoff or Trigger the workflow:

DECLARE

lvItemType VARCHAR2(80) := 'XAOATEST';

lvUserId NUMBER := -1;

lvItemKey VARCHAR2(10);

vErrorMsg VARCHAR2(2000);

vErrorCode NUMBER;

BEGIN

lvItemKey := 'XAOA-01'; -- This should be unique value

xxaoa_test_wf_pkg.launch_workflow( itemtype => lvItemType,

itemkey => lvItemKey,

process => 'AOAMAIN_PROCESS' -- Main Runnable process name

);

COMMIT; -- Use commit if we need to see the WF Status from Front End from workflow Admin Resp

EXCEPTION

WHEN OTHERS

THEN

vErrorCode := SQLCODE;

vErrorMsg := SQLERRM(SQLCODE);

RAISE_APPLICATION_ERROR(20001, vErrorMsg);

END;

Script to test the Workflow statuses:

SELECT *

FROM wf_items

WHERE item_type = 'XAOATEST';

Result:

ITEM_TYPE ITEM_KEY ROOT_ACTIVITY ROOT_ACTIVITY_VERSION OWNER_ROLE PARENT_ITEM_TYPE PARENT_ITEM_KEY PARENT_CONTEXT BEGIN_DATE END_DATE USER_KEY HA_MIGRATION_FLAG SECURITY_GROUP_ID

XAOATEST XAOA-01 AOAMAIN_PROCESS 1 08-JUN-13

SELECT *

FROM wf_item_activities_history_v

WHERE item_type = 'XAOATEST';

Result:

ITEM_TYPE ITEM_KEY ACTIVITY_DEF_BEGIN_DATE ACTIVITY_DEF_END_DATE BEGIN_DATE END_DATE EXECUTION_TIME BEGIN_DATE_TIME DURATION ACTIVITY_ITEM_TYPE ACTIVITY_TYPE PARENT_ACTIVITY_NAME ACTIVITY_NAME ACTIVITY_DISPLAY_NAME PARENT_DISPLAY_NAME ACTIVITY_STATUS NOTIFICATION_STATUS NOTIFICATION_ID RECIPIENT_ROLE RECIPIENT_ROLE_NAME ACTIVITY_STATUS_DISPLAY RESULT

XAOATEST XAOA-01 08-JUN-13 08-JUN-13 1 08-JUN-13 11:25:12 162 XAOATEST PROCESS ROOT AOAMAIN_PROCESS All Oracle Apps Main Process All Oracle Apps Test ACTIVE WF_ENGINE Workflow Engine Active

If you are running this Script outside of Oracle Applications like Running from any Oracle IDE then run the Workflow Background Process for your Item type to save time 🙂 to Kickoff the workflow.

+137

Creating a Workflow from scratch

Workflow with Approval Notification

Workflow Tables

Creating workflow from scratch 2

5 Replies

Creating workflow from scratch Part 2

Once the Process is created then we need to design the Process:

To Design any Process in Workflow it should start with One start Function and we get it from WFSTD. Just we need to drag and drop the start and end functions from the Standard Functions:

Even when we import the Start function go to its properties and we need to mention it should be a start:

Note: Any Process can have only one Start but can have any number of ends depending upon the Business logic’s complexity.

Similarly change the properties of the end function:

Click on OK button.

Now we need to create a Custom Function that has the business logic. You can have the Function call your plsql function or Java Class or any external application. We can mention the PLSQL Function Name in the Function Name field in the properties of the Function.

We can mention the result type if any Result is returned from the plsql Function:

Click OK.

Once the function is created just drag and drop the function between START and END Functions. Once the Function is placed just by using right click of the mouse draw the Flow how the process to be. Always The Process would be starting from Start and No Functions can be in the Process without coupling to another in the flow. Always the flow should end using and END function.

Now Verify the workflow and save in the data base or the (desktop and upload using wfload).

Once the workflow is saved in the data base which usually takes time we can test it using below queries:

SELECT *

FROM wf_item_types

WHERE name = 'XAOATEST';

Result:

NAME PROTECT_LEVEL CUSTOM_LEVEL WF_SELECTOR READ_ROLE WRITE_ROLE EXECUTE_ROLE PERSISTENCE_TYPE PERSISTENCE_DAYS SECURITY_GROUP_ID NUM_ACTIVE NUM_ERROR NUM_DEFER NUM_SUSPEND NUM_COMPLETE NUM_PURGEABLE

XAOATEST 1000 0 TEMP 0

SELECT *

FROM WF_PROCESS_ACTIVITIES

WHERE process_item_type = 'XAOATEST';

Result:

PROCESS_ITEM_TYPE PROCESS_NAME PROCESS_VERSION ACTIVITY_ITEM_TYPE ACTIVITY_NAME INSTANCE_ID INSTANCE_LABEL PERFORM_ROLE_TYPE PROTECT_LEVEL CUSTOM_LEVEL START_END DEFAULT_RESULT ICON_GEOMETRY PERFORM_ROLE USER_COMMENT SECURITY_GROUP_ID

XAOATEST AOAMAIN_PROCESS 1 WFSTD START 803292 START CONSTANT 0 0 START -560,-160

XAOATEST AOAMAIN_PROCESS 1 WFSTD END 803294 END CONSTANT 0 0 END -208,-160

XAOATEST AOAMAIN_PROCESS 1 XAOATEST XXAOA_TEST_WF_FUNC 803296 XXAOA_TEST_WF_FUNC CONSTANT 0 0 -400,-160

XAOATEST ROOT 1 XAOATEST AOAMAIN_PROCESS 803291 AOAMAIN_PROCESS CONSTANT 1000 0

The data in WF_PROCESS_ACTIVITIES table explains the complete details of the workflow item type. Like how many Process, Functions, etc. with its versions.

PLSQL Code of the package and Trigger the workflow:

+160

Creating a Workflow from scratch

Workflow with Approval Notification

Workflow Tables

Creating a Workflow from scratch

Creating a New workflow using WFSTD:

Download the Standard Workflow for creating a new workflow item type:

To download the workflow file we can download using wfload or from Oracle Workflow Builder.

Navigation:

File –> Open –> Database –> Please give Apps user credentials. It usually takes time to load all the workflows.

Select Standard from the list of workflows and press OK button.

When the WFSTD is Loaded then save on your desktop as STANDARD.wft:

Right click on Standard and create a new Item Type

The Item Type Internal name can have at the max of 8 Characters:

There are 3 types of Persistence’s as Temporary, Permanent and Synchronization.

The access levels are important to modify the workflow and can be set at workflow builder help.

Click OK

Now save the Workflow file as XAOATEST.wft on your local desktop:

Once a new Item Type is created then a new process needs to be created where the logic needs to go in:

We need to create a new Runnable Process so that we can call it from PLSQL/Form/OAF/Java. Under a Process there can be any number of sub process depending upon the complexity of the workflow logic that needs to be implemented.