Oracle Workflows

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.

Anil Passi

Add comment