ACS3 Data Model

     1: function im_delinquent_employee_p ( v_employee_id IN integer, v_report_date IN date, v_interval IN integer)
     2: RETURN integer
     3: IS
     4:   v_vacation_days integer;
     5:   v_total_days integer;
     6:   v_work_days integer;
     7:   v_employee_start_date date;
     8:   v_delinquent_employee_p integer;
     9: BEGIN
    10: 
    11:   v_delinquent_employee_p := 0;
    12: 
    13:   -- fetch the number of vacation days between (trunc(v_report_date) - v_interval)
    14:   --   and (trunc(v_report_date))
    15:   SELECT nvl(sum(least(end_date,trunc(v_report_date)) - greatest(start_date,trunc(v_report_date)-v_interval) + 1),0)
    16:     INTO v_vacation_days
    17:     FROM user_vacations
    18:     where (start_date between trunc(v_report_date)-v_interval and trunc(v_report_date)
    19:            or end_date between trunc(v_report_date)-v_interval and trunc(v_report_date))
    20:     and user_id = v_employee_id;
    21:   v_work_days := v_interval - v_vacation_days;
    22:   v_total_days := v_interval + v_vacation_days;
    23: 
    24:   -- while there are < v_interval valid work days, look for more valid work days
    25:   WHILE v_work_days < v_interval LOOP
    26:      -- fetch the number of vacation days between (trunc(v_report_date) - v_total_days)
    27:      --   and (trunc(v_report_date))
    28:     SELECT nvl(sum(least(end_date,trunc(v_report_date)) - greatest(start_date,trunc(v_report_date)-v_total_days) + 1),0)
    29:       INTO v_vacation_days
    30:       FROM user_vacations
    31:       where (start_date between trunc(v_report_date)-v_total_days and trunc(v_report_date)
    32:              or end_date between trunc(v_report_date)-v_total_days and trunc(v_report_date))
    33:       and user_id = v_employee_id;
    34:     v_work_days := v_total_days - v_vacation_days;
    35:     v_total_days := v_interval + v_vacation_days;
    36:   END LOOP;
    37: 
    38:   SELECT start_date
    39:     INTO v_employee_start_date
    40:     from im_employee_info
    41:     where user_id = v_employee_id;
    42: 
    43:   v_total_days := greatest(least(v_total_days,trunc(v_report_date)-v_employee_start_date+1),0);
    44: 
    45: 
    46:   select nvl(count(1),0)
    47:   into v_delinquent_employee_p
    48:   from users u
    49:   where user_id = v_employee_id
    50:   and exists (select 1
    51:       from im_employee_percentage_time pt
    52:       where user_id = u.user_id
    53:       and start_block between trunc(sysdate)-7 and trunc(sysdate)
    54:       and percentage_time > 0)
    55:   and not exists (select 1
    56:       from im_hours h
    57:       where user_id = u.user_id
    58:       and day between trunc(v_report_date)-v_total_days and trunc(v_report_date)
    59:       and day > v_employee_start_date)
    60:   and trunc(v_report_date) - v_total_days - 7  > v_employee_start_date;
    61: 
    62:   return v_delinquent_employee_p;
    63: 
    64: END;