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.

Sample Workflow with Bank Holiday timeout activity

 => 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 (10)

Subscribe to this comment's feed

...
written by Prabhat Jha , November 30, 2006

Ya Let me have a go at this and will get back.
Thanks
Prabhat Jha

...
written by Prabhat Jha , November 30, 2006

Ya Let me have a go at this and will get back.
Thanks
Prabhat Jha

...
written by Rajesh Jha , June 26, 2007

Nice article THANKS
One doubt... Is timeout always in minutes.... or we must do some setup for that...???

...
written by Anil Passi , June 26, 2007

Yes indeed, you have no choice but to calculate in minutes. I am not aware of an option to change that

thanks
anil

...
written by KK , July 11, 2007

Hi 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'
.otherwise i should not send notification to user. how to do it?
Thanks for your help
Kumar

...
written by APassi , July 11, 2007

Hi 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

Thanks,
Anil

...
written by Senthil.oracle , September 07, 2007

Hi Anil,

After setting timeout period and everything we need to schedule Workflow Background Process
concurrent program to complete the timedout activities, then only the this scenario will workout right?.. Please confirm this.

...
written by Anil Passi , September 07, 2007

You are right Senthil

You need to schedule the WF background process

Thanks,
Anil

Suppressing cancellation email in AP Invoice workflow
written by Senthil K Rajaram , October 02, 2007

Hi 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.

Thanks,
Senthil

...
written by ramkumar , October 03, 2007

i need workflow customizations and examples in oracle Hrms and oracle projects modules.