h) How to Send Reminder Notification to Approver

Oracle Workflow provide us several standrad functions which helps us to implement our business requirement.

Here we will discuss about two such standard functions to implement our requirement.

Basic Requirement

Our old requirement looks like "when a person applies for a leave it should go his/her supervisor for approval.Approver must be able to provide his/her approval/rejection comments. The comments must be entered by the approver while rejecting the leave requisition.If he/she rejects the leave requisition without entering comments, then it should raise an application error.If he/she approves the requisition it should be optional.Approver must also be able to select theOrganization Leave Type from a list of values . If the leave gets rejected, don’t store any information in database.The approver should able to view/edit the person "Special Information Types" before approving the leave request.Before approving the sick leave request the approver must able to see the submitted medical document."

Apart from the above requirement, We will introduce an additional requirement that "A reminder notification(email) must go to approver in every 2 days untill he/she takes action (Approve or Reject) on the leave notification."

Solution Approach

First we will design discuss about the seeded function that we are going to use.

1) Compare Text

This function is use to compare the value of two Activity attribute.

Test Value:- A value which will be compared with the referenced value.This can be a constant value or can reference a item attribute.

Reference Value:- a text value which will be used as a reference value to compare.This can also be a constant value or can reference a item attribute.

The comparison activities use the Comparison lookup type (Internal Name:- WFSTD_COMPARISON) for a result code. Possible values are "Greater Than," "Less Than," "Equal," or "Null,". You can guide your workflow process based on how the value of an item type attribute compares to a given value that one set.

1) Wait

This function pauses the process for the time that we specified in the activity attribute during the design of the workflow.The wiat functionality can work for

a) A specific date

b) A given day of Month

c) A given day of the week

d) Relative time( A period of time after this activity is encountered).

Note:-1) When the process hit the Wait function, it goes to Deferred status. We must run background engine for deferred activities to determine when the wait time has passed. The background engine then completes the Wait activity so that the process can continue.

1) All the standard functions are available under itemtype Standard(Internal Name WFSTD).

Now Lets design/enhance the existing workflow model for the same.

1) Create a item attribute of type Text

Internal Name:- REMINDER_NOTIF_VAL_ATTR

Display Name:- Reminder Notification Attribute Val

Type:- Text

This attribute will be set when approver acts on his/her notification. Intially the value of the attribute will remain Null. Thus untill approver acts on his/her notification, it will remain null and follow the path as shown above in the flowchart,i.e, will send notification.

2) Load the itme type "standard" (Internal Name:- WFSTD) workflow defintion in a separate workflow builder window.To copy the standrad functions please follow the following sequence.

a) Copy(ctrl+C) the following lookup type Wait Mode (Internal Name:-WFSTD_WAIT_MODE) from the standard workflow(WFSTD) and Paste(ctrl+v) it in our custom

workflow.

b) Follow the same steps for "Day of Month"(WFSTD_DAY_OF_MONTH), "Day of Week"(WFSTD_DAY_OF_WEEK),"Comparison"(WFSTD_COMPARISON)

Copying of these lookup types is required as this lookup types are referenced by Wait and Comparison function.

3) Now in the similar way copy the Wait(Internal Name:-WAIT) and "Compare Text" (Internal Name:-COMPARETEXT) function.

4) Now drag and drop the copied function from navigator window to process window.

5) Now double click on the WAIT function in process window--> Go to "Node Attribute Tab"

Select the Node attribute 1) Wait Mode:- Select value as "Relative Time" from the drop down

2) Relative Time:- Enter the Value as 2 ( since as per business requirement business requirement, every 2 days we have to send the reminder).

6) Similarly Double click on COMPARETEXT function --> Go to Node Attribute

a) Select Node attribute "Test Value" --> The "Test Value" will determine whether approver acted on the notification or not, so we need reference a item attribute

which will be set when approver approves/Reject notification.

Select Type:- Item Attribute

Value:- Select the item attribute "Reminder Notification Attribute Val"(Internal Name:-REMINDER_NOTIF_VAL_ATTR)

b) Select Node Attribute "Reference Value":- This is the reference value, hence keep this static and enter "APPROVE_REJECT" in the Value field.

7) Now Create a notification. The performer of the notification will be same as the performer of the leave notification.

Internal Name:- REMINDER_EMAIL_NOTIF

Display Name:- Reminder Notification

Remember:- This is just a reminder notification/email,hence it will be a FYI notification.Hence no Result type is required.

8) Now create a message with the subitable text and subject Line

Internal Name:- REMINDER_MSG

Display Name:- Reminder Message

Subject :- Reminder:- Leave Approval for Person &REQUESTER_EMP_NAME (&REQUESTER_EMP_NO) is pending with you.

Body:- Dear &SUP_EMP_NAME,

The person &REQUESTER_EMP_NAME(&REQUESTER_EMP_NO) has submitted leave requisition. Request you please take necessary action on the requisition.

Regards

HR Team

As we did earlier(Refer earlier workflow tutorial/s) copy the desired item attribute and make them as message attribute (Highlighted in blue above).

9) Attach the message with the newly created notification.

10) Now join all the nodes in the process window as shown below

11) Validate and save your work in database.

Pl/SQL PART

12) we have created an item attribute of type Text which we need to set once approver acts on notification.

We will use the existing post notification function used in approval notification "Test Leave Notification"(Internal Name:-XX_TEST_LEAVE_NOTIF).

[for details of Post Notification function see the previous tutorial]

Now Compile your code and test the functionality.

Reminder notification

Here is a sample reminder notification that we created with the help of our above said modifications

Note:- 1) Ensure that Workflow background process is scheduled and running.

2) Once approver approves/reject the leave request, the wait function will automatically gets completed.Workflow will take care of that.See the screen

shot below.

3) The point 2 holds true only when after execution of approval it hits "End" function. If it doesn't hit "END", Then we have to use combination of OR and AND function as shown below.

[Only flow diagram is given.Rest is kept unsolved for reader's interest...]

4) There are several ways to implement this requirement. We have discussed here about only one possible solution.If you are planning to modify the existing workflow, sometime it may not be possible to add anything in the existing Post Notification function. The attribute can be set by adding a custom function immediately after approval and/or rejection of the notification.

References:- 1) https://metalink.oracle.com

2) Oracle Workflow Developer's Guide ( Release 12) B31433-04

3) Oracle. Workflow API Reference Release 2.6.3.5 Part No. B12163–02

Disclaimer:- This is a knowledge sharing site. This topic talks about a custom solution. Oracle Corporation may not provide you a support for any data corruption or any other problem in your custom code/problem arises because of the custom code.

The author is not responsible for any kind of system/data problem appears because of usages of this code.Reader/implementer must do it on his/her own risk/responsibility.

Oracle Corporation may change API definition/usages. User must read the necessary documentation before using Oracle provided APIs.