CREATE OR REPLACE FUNCTION get_working_days_between_woh(start_date DATE,

                                                    end_date   DATE)

  RETURN VARCHAR2 AS

  counter                 PLS_INTEGER := 0;

  date_range              NUMBER;

  date_range1             PLS_INTEGER := 0;

  lv_date_range_into_secs NUMBER;

  lv_holidays_into_secs   NUMBER;

  lv_working_busi_secs    NUMBER;

  lv_days                 NUMBER;

  lv_hours                NUMBER;

  lv_mins                 NUMBER;

  lv_sec                  NUMBER;

  lv_remaining_hrs        VARCHAR(100);

  lv_result               VARCHAR2(100);

BEGIN

 

  date_range1 := TRUNC(end_date - start_date);

 

  ----dbms_output.put_line(end_date - start_date);

 

  ----dbms_output.put_line('date_range1' || date_range1);

 

  FOR dys IN 1 .. ABS(date_range1) LOOP

    IF to_char(start_date + (dys * sign(date_range1)), 'fmdy') IN

       ('sat', 'sun') THEN

      counter := counter + 1;

    END IF;

  END LOOP;

 

  --dbms_output.put_line('Count of holidays :' || counter);

 

  IF (to_char(end_date, 'fmdy') IN ('sat', 'sun')) THEN

 

    counter    := counter - 1;

    date_range := TRUNC(end_date - start_date) - 1;

    --dbms_output.put_line('Inside End Date Validation' || counter || ',' ||

                        -- date_range);

 

  ELSIF (to_char(start_date, 'fmdy') IN ('sat', 'sun')) THEN

 

    counter    := counter + 1;

    date_range := end_date - TRUNC(start_date);

    --dbms_output.put_line('Inside End Date Validation' || counter || ',' ||

                         --date_range);

 

  ELSE

 

    date_range := end_date - start_date;

 

  END IF;

 

  --dbms_output.put_line('date_range' || date_range);

 

  lv_date_range_into_secs := date_range * 60 * 60 * 24;

 

  --dbms_output.put_line('lv_date_range_into_secs :' ||

                       --lv_date_range_into_secs);

 

  ----dbms_output.put_line('Count of holidays :' || abs(date_range1)||' '||date_range||to_char(start_date + (1 * sign(date_range1)), 'fmdy'));

 

  lv_holidays_into_secs := counter * 60 * 60 * 24;

 

  --dbms_output.put_line('lv_holidays_into_secs :' || lv_holidays_into_secs);

 

  lv_working_busi_secs := ABS(lv_date_range_into_secs -

                              lv_holidays_into_secs);

 

  --dbms_output.put_line('lv_working_busi_secs :' || lv_working_busi_secs);

 

  lv_days := TRUNC(lv_working_busi_secs / (24 * 3600));

 

  --dbms_output.put_line('lv_days :' || lv_days);

 

  lv_hours := TRUNC(MOD(lv_working_busi_secs, (24 * 3600)) / 3600);

 

  --dbms_output.put_line('lv_hours :' || lv_hours);

 

  lv_mins := TRUNC(MOD(lv_working_busi_secs, 3600) / 60);

 

  --dbms_output.put_line('lv_mins :' || lv_mins);

 

  lv_sec := TRUNC(MOD(lv_working_busi_secs, 60));

 

  --dbms_output.put_line('lv_sec :' || lv_sec);

 

  lv_result := to_char(lv_days);

 

  RETURN lv_result;

EXCEPTION

  WHEN OTHERS THEN

    --dbms_output.put_line(SQLERRM);

    RETURN NULL;

END get_working_days_between_woh;