ACS3 Data Model

     1: function get_start_week (v_start_date IN date)
     2: return date
     3: IS
     4:  v_date_round date;
     5:  v_date_next_sun date;
     6:  v_date_check date;
     7: BEGIN
     8:  select round(v_start_date, 'day') into v_date_round from dual;
     9:  select trunc(next_day(v_start_date, 'sunday'),'day') into v_date_next_sun from dual;
    10: 
    11:  IF v_date_round < v_date_next_sun THEN
    12:     -- we have the beginning of the week
    13:     return v_date_round;
    14:  END IF;
    15: 
    16:  v_date_check := v_start_date - 3;
    17:  select round(v_date_check, 'day') into v_date_round from dual;
    18:  IF v_date_round = v_date_next_sun THEN
    19:     --the day is saturday, so we need to subtract one more day
    20:     v_date_check := v_date_check - 1;
    21:     select round(v_date_check, 'day') into v_date_round from dual;
    22:  END IF;
    23: 
    24:  return v_date_round;
    25: END get_start_week;