Oracle Workflow Timeouts design with Bank Holidays and Weekends Very often our client demand that they want to set reminder or timeout for Oracle Workflow notifications after 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)) THEN 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” Oracle workflow calls a procedure that in-turn calls a function names get_timeout_minutes. 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. Thanks, Anil Passi