Very often our client demand that they want to set reminder or timeout for Oracle Workflow notifications after <xyz>
number of working days. Well surely, they always wish to exclude not only the weekends but also the bank holidays.
Having implemented an approach for various Oracle based Workflows, I am now in a position to share a very simple
methodology which uses recursion in a pl/sql function.
A site level profile option is assigned to global variable g_number_of_wait_days to capture the offset days.
This happens during the package initialization of the pl/sql code.
For example, let’s assume that we need to escalate/timeout a workflow notification after 3 Working Days.
In this case g_number_of_wait_days will be assigned a value of 3 via a profile option.
=> Ensure that you have a table say xxx_holiday_list, in which you capture bank holidays.
This needs once per year maintenance only.
=>In the Oracle workflow, create a activity names “Set Wait Days for Timeout” that executes a procedure
named set_wait_days_for_timeout within a pl/sql package.
FUNCTION is_this_date_bank_holiday(p_date IN DATE) RETURN BOOLEAN IS
CURSOR c_get IS
SELECT 'x'
FROM xxx_holiday_list ibh
WHERE ibh.bank_holiday_date = p_date;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
IF c_get%FOUND
THEN
CLOSE c_get;
RETURN TRUE;
END IF;
CLOSE c_get;
RETURN FALSE;
END is_this_date_bank_holiday;
FUNCTION get_next_working_date(p_estimated_next_working_date IN DATE)
RETURN DATE IS
BEGIN
IF is_this_date_bank_holiday(trunc(p_estimated_next_working_date))
T HEN
RETURN get_next_working_date(p_estimated_next_working_date + 1);
END IF;
IF to_char(p_estimated_next_working_date, 'DY') = 'SAT'
THEN
RETURN get_next_working_date(p_estimated_next_working_date + 2);
END IF;
IF to_char(p_estimated_next_working_date, 'DY') = 'SUN'
THEN
RETURN get_next_working_date(p_estimated_next_working_date + 1);
END IF;
RETURN p_estimated_next_working_date;
END get_next_working_date;
FUNCTION get_next_working_date(
p_estimated_next_working_date IN DATE
,p_offset_days INTEGER )
RETURN DATE
IS
v_next_working_date DATE;
BEGIN
IF p_offset_days < 2
THEN
RETURN get_next_working_date( p_estimated_next_working_date + 1 );
END IF;
v_next_working_date := get_next_working_date( p_estimated_next_working_date + 1 );
RETURN get_next_working_date( v_next_working_date, p_offset_days - 1 );
END get_next_working_date;
FUNCTION get_timeout_minutes RETURN NUMBER IS
BEGIN
RETURN(get_next_working_date(SYSDATE
,g_number_of_wait_days) -
SYSDATE) * 24 * 60;
END get_timeout_minutes;
PROCEDURE set_wait_days_for_timeout
(
itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2
) IS
n_timeout_in_minutes NUMBER := get_timeout_minutes;
BEGIN
IF (funcmode != 'RUN')
THEN
RETURN;
END IF;
wf_engine.setitemattrnumber(itemtype => itemtype
,itemkey => itemkey
,aname => 'WAIT_DAYS_INCLUSIVE_WEEEKEND_H'
,avalue => n_timeout_in_minutes);
RESULT := 'COMPLETE:Y';
END set_wait_days_for_timeout;
Rather than spoon feeding you to explain the code, let me simply list the broader logic
End result of the above procedures and functions is to assign a value in minutes to an attribute workflow
“WAIT_DAYS_INCLUSIVE_WEEEKEND_H”
Above function then makes a call to get_next_working_date. On a closer look you will notice that the function
get_next_working_date is overloaded and gets passed in either a Date or Date and an offset. It recursively
keeps skipping the days until it has gone past all the bank holidays and also all the weekends that might be
encountered plus the offset days as defined in a site level profile option.
Finally, the approval notification is assigned the Workflow Attributes that dictates the timeout in minutes.
My 2 cents, to my knowledge I am not yet aware of an out of the box solution from Oracle.
Until Oracle Workflow delivers one out of the box, please feel free to use this methodology.
Comments
Thanks
Prabhat Jha
Thanks
Prabhat Jha
One doubt... Is timeout always in minutes.... or we must do some setup for that...???
thanks
anil
could you please tell me how to send a notification to user based on a condition. eg: I want to send notification to the user only when the custom attribute(Is previous approver timed out?) is set to 'true'
.otherwi se i should not send notification to user. how to do it?
Thanks for your help
Kumar
You need to do the steps below
1. create an activity in process, and attach it a pl/sql function
2. in the pl/sql function, check if the timeout attribute/flag is Y or N
3. Attach a Yes/No lookup to this activity
4. the pl/sql will either return COMPLETE:Y or COMPLETE:N
This will cause the Wf to branch properly
Thank s,
Anil
After setting timeout period and everything we need to schedule Workflow Background Process
concurr ent program to complete the timedout activities, then only the this scenario will workout right?.. Please confirm this.
You need to schedule the WF background process
Thanks ,
Anil
I have a requirement for AP Invoice workflow customization. The scenario is like this.
Approval notification email goes to an approver. If there is no response from the approver after 2 days, a reminder email notification is sent. Parallely a cancellation notification email corresponding to the original approval notification goes to the approver. If there is no response from the approver after another 2 days then an escalation notification email goes to the next approver. Parallely a cancellation notification email corresponding to the first reminder notification goes to the original approver.
Now the requirement here is to suppress only the first cancellation and not the second one.
Is this possible? If so please share your thoughts.
Than ks,
Senthil
I am creating a custom invoice hold workflow using workflow builder. It sends the notification to approver and there are two response actions 1) Release Hold 2) Retain Hold
If the approver press the Retain hold button, I am capturing the approver comments, but the notification is getting closed.
Is there any way to keep that notification open after pressing the Retain notification button? I tried a lot on this, but not able to find a solution to keep the notification s open. This notification has timeout functiaonlity attached, so I don't want to close this notification and send a new one.
Thanks
An il C
I have an requirement for ap invoice approval notification,
U ser want approval notification reminder should go to approvar after every 3 working days.
what method should i follow. or will time out work for that.
I am facing a problem that Second notification is not getting triggered even ater timeout for first notification is over. Please help.
Thanks in Advance
Gurleen Kaur
RSS feed for comments to this post