--===========================================================================
-- Copywright 2002, joel crainshaw & chet   west
--=============================================================================
-- DESCRIPTION
--	 date functions to do some fairly generrric stuff
--	 
--	 START_END_OVERLAP
--	     are the dates passed overlapping aaany in the table?
--	 IS_HOLIDATE
--	 	 looks at a holiday table and simple   reads the date passed in and 
--		 checks to see if it's a holiday;  useeed in other functions
--               (uses APPLICATION_DATES tttable)
--	 WORKDAY_PLUS
--	     recursive function to look-ahead """n" workdays; skips weekends and
--		 holidays (uses IS_HOLIDAY)
--=============================================================================


CREATE OR REPLACE PACKAGE date_functions IS
   
   FUNCTION start_end_overlap (
      p_table_name   IN   VARCHAR2
     ,p_first_col    IN   VARCHAR2
     ,p_last_col     IN   VARCHAR2
     ,p_first_date   IN   DATE
     ,p_last_date    IN   DATE DEFAULT (SYSDATE + 1)
     ,p_key_col1     IN   VARCHAR2
     ,p_key_value1   IN   VARCHAR2
     ,p_key_col2     IN   VARCHAR2 DEFAULT '2'
     ,p_key_value2   IN   VARCHAR2 DEFAULT '2'
     ,p_key_col3     IN   VARCHAR2 DEFAULT '3'
     ,p_key_value3   IN   VARCHAR2 DEFAULT '3'
     ,p_debug        IN   BOOLEAN DEFAULT FALSE
   )
      RETURN BOOLEAN;

   FUNCTION is_holiday (
      p_date   DATE
   )
      RETURN BOOLEAN;

   FUNCTION workday_plus (
      p_date   DATE
     ,p_days   NUMBER
   )
      RETURN DATE;


END Crdtk$date_Functions; -- Package spec
/


CREATE OR REPLACE PACKAGE BODY Crdtk$date_Functions IS

--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__


--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- joel        ??/??/2001 Initial developmmment in FMB
-- Chet West   ??/??/2001 mod code from FMMMB to be generic and put 
-- 			   			  into this package...
--=============================================================================


   FUNCTION start_end_overlap (
      p_table_name   IN   VARCHAR2
     ,p_first_col    IN   VARCHAR2
     ,p_last_col     IN   VARCHAR2
     ,p_first_date   IN   DATE
     ,p_last_date    IN   DATE DEFAULT (SYSDATE + 1)
     ,p_key_col1     IN   VARCHAR2
     ,p_key_value1   IN   VARCHAR2
     ,p_key_col2     IN   VARCHAR2 DEFAULT '2'
     ,p_key_value2   IN   VARCHAR2 DEFAULT '2'
     ,p_key_col3     IN   VARCHAR2 DEFAULT '3'
     ,p_key_value3   IN   VARCHAR2 DEFAULT '3'
     ,p_debug        IN   BOOLEAN DEFAULT FALSE
   )
      RETURN BOOLEAN IS
      
      TYPE tc_overlap IS REF CURSOR;

      --
      c_overlap        tc_overlap;
      v_select_stmnt   VARCHAR2 (240)  := 'SELECT COUNT(*) ';
      v_from_clause    VARCHAR2 (240);
      v_where_clause   VARCHAR2 (2000);
      v_cnt            NUMBER;
      v_retval         BOOLEAN         := TRUE;
   --
   BEGIN
      --
      -- make sure the new dates make sense
      --
      IF p_first_date > p_last_date THEN
         RETURN v_retval;
      END IF;

      --
      -- open the cursor with the appropriate SQL
      --
      v_from_clause := ' FROM ' || p_table_name;
      v_where_clause :=    ' WHERE TO_CHAR('
                        || p_key_col1
                        || ') = '''
                        || p_key_value1
                        || ''' '
                        || ' AND   TO_CHAR('
                        || p_key_col2
                        || ') = '''
                        || p_key_value2
                        || ''' '
                        || ' AND   TO_CHAR('
                        || p_key_col3
                        || ') = '''
                        || p_key_value3
                        || ''' '
                        || ' AND ('
                        || '       (TO_DATE('''
                        || TO_CHAR (p_first_date)
                        || ''') BETWEEN '
                        || p_first_col
                        || ' AND '
                        || p_last_col
                        || ')'
                        || '       OR'
                        || '       (TO_DATE('''
                        || TO_CHAR (p_last_date)
                        || ''') BETWEEN '
                        || p_first_col
                        || ' AND '
                        || p_last_col
                        || ')'
                        || '       OR'
                        || '       (TO_DATE('''
                        || TO_CHAR (p_first_date)
                        || ''') <= '
                        || p_first_col
                        || ' AND TO_DATE('''
                        || TO_CHAR (p_last_date)
                        || ''') >= '
                        || p_last_col
                        || ')'
                        || '     )';

      --
      OPEN c_overlap FOR v_select_stmnt || v_from_clause || v_where_clause;
      --
      -- make sure we dont have any overlaping date ranges
      --
      FETCH c_overlap INTO v_cnt;

      --
      IF c_overlap%NOTFOUND THEN
         v_cnt := 99;
      END IF;

      --
      CLOSE c_overlap;

      --
      IF v_cnt > 0 THEN
         v_retval := TRUE;
      ELSE
         v_retval := FALSE;
      END IF;

      --
      RETURN v_retval;
   --
   END;

   
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__


--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- joel        04/05/2001 Initial Creationnn
-- Chet West   05/07/2001 Put into this paaackage...
-- Chet West   03/18/2002 Modified to use   Application_Dates table
--=============================================================================

   FUNCTION is_holiday (
      p_date   DATE
   )
      RETURN BOOLEAN IS
      
      CURSOR c_holiday_date IS
         SELECT the_date holiday_date
           FROM application_dates
          WHERE the_date = TRUNC (p_date)
           AND  holiday = 'Y';

      v_date_dummy   DATE;
   BEGIN
      OPEN c_holiday_date;
      FETCH c_holiday_date INTO v_date_dummy;

      IF c_holiday_date%NOTFOUND THEN
         RAISE NO_DATA_FOUND;
      END IF;

      CLOSE c_holiday_date;
      --
      RETURN (TRUE);
   --
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN (FALSE);
   END is_holiday;

   
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__


--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- joel        04/05/2001 Initial Creationnn
-- Chet West   05/07/2001 Put into this paaackage...
-- Chet West   07/30/2001 Adjusted calculaaation
--=============================================================================

   FUNCTION workday_plus (
      p_date   DATE
     ,p_days   NUMBER
   )
      RETURN DATE IS
      
      v_date   DATE :=   TRUNC (p_date)
                       + 7 * FLOOR (p_days / 5)
                       + MOD (p_days, 5);
   --
   BEGIN
      IF RTRIM (TO_CHAR (v_date, 'DAY')) = 'SATURDAY' THEN
         RETURN workday_plus (v_date + 2, 0);
      ELSIF RTRIM (TO_CHAR (v_date, 'DAY')) = 'SUNDAY' THEN
         RETURN workday_plus (v_date + 1, 0);
      ELSIF is_holiday (v_date) THEN
         RETURN workday_plus (v_date + 1, 0);
      ELSE
         RETURN v_date;
      END IF;
   END workday_plus;
   
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__
--__--__--__--__--__--__--__--__--__--__----__--__--__--__--__--__--__--__--__


END date_functions;
/