create or replace package times as
  function prev_quarter_start(date_in IN DATE) return date;
  function prev_quarter_end(date_in IN DATE) return date;
  function prev_month_start(date_in DATE) return date;
  function prev_month_end(date_in DATE) return date;
  function prev_week_start(date_in DATE) return date;
  function prev_week_end(date_in DATE) return date;
  function prev_day_start(date_in DATE) return date;
  function prev_day_end(date_in DATE) return date;
  function gmt_date(date_in DATE default sysdate) return date;
  PRAGMA RESTRICT_REFERENCES (prev_quarter_end, WNDS, WNPS);
  PRAGMA RESTRICT_REFERENCES (prev_quarter_start, WNDS, WNPS);
  PRAGMA RESTRICT_REFERENCES (prev_month_start, WNDS, WNPS);
  PRAGMA RESTRICT_REFERENCES (prev_month_end, WNDS, WNPS);
  PRAGMA RESTRICT_REFERENCES (prev_week_start, WNDS, WNPS);
  PRAGMA RESTRICT_REFERENCES (prev_week_end, WNDS, WNPS);
  PRAGMA RESTRICT_REFERENCES (prev_day_start, WNDS, WNPS);
  PRAGMA RESTRICT_REFERENCES (prev_day_end, WNDS, WNPS);
  PRAGMA RESTRICT_REFERENCES (gmt_date, WNDS, WNPS);
end times;
/

create or replace package body times as
  function prev_quarter_start(date_in IN DATE) return date is
    v_prev_quarter_start date;
  begin
    v_prev_quarter_start := trunc(last_day(add_months(date_in,-4))) + 1;
    return (v_prev_quarter_start); 
  end prev_quarter_start;
--
  function prev_quarter_end(date_in IN DATE) return date is
    v_prev_quarter_end date;
  begin
    v_prev_quarter_end := trunc(last_day(add_months(date_in,-1))) + 1;
    return (v_prev_quarter_end); 
  end prev_quarter_end;
--
  function prev_month_start(date_in IN DATE) return date is
    v_prev_month_start date;
  begin
    v_prev_month_start := trunc(last_day(add_months(date_in,-2))) + 1;
    return (v_prev_month_start); 
  end prev_month_start;
--
  function prev_month_end(date_in IN DATE) return date is
    v_prev_month_end date;
  begin
    v_prev_month_end := trunc(last_day(add_months(date_in,-1))+1);
    return (v_prev_month_end);
  end prev_month_end;
--
  function prev_week_start(date_in IN DATE) return date is
    v_prev_week_start date;
  begin
    v_prev_week_start := trunc(date_in) - 7;
    return (v_prev_week_start);
  end prev_week_start;
--
  function prev_week_end(date_in IN DATE) return date is
    v_prev_week_end date;
  begin
    v_prev_week_end := trunc(date_in);
    return (v_prev_week_end);
  end prev_week_end;
--
  function prev_day_start(date_in IN DATE) return date is
    v_prev_day_start date;
  begin
    v_prev_day_start := trunc(date_in) - 1;
    return (v_prev_day_start);
  end prev_day_start;
--
  function prev_day_end(date_in IN DATE) return date is
    v_prev_day_end date;
  begin
    v_prev_day_end := trunc(date_in);
    return (v_prev_day_end);
  end prev_day_end;
--
  function gmt_date(date_in in DATE default sysdate) return date is
    v_gmt_time date;
    v_gmt_delta number;
    v_daylight number;
  begin
    select cur_value into v_gmt_delta from instance_preferences
      where pref_code = 'DELGMT';
    select to_number(cur_value) into v_daylight from instance_preferences
      where pref_code = 'DAYLON';
    v_gmt_time := date_in - ((v_gmt_delta + v_daylight) / 24);
    return (v_gmt_time);
  end gmt_date;
--
end times;
/

drop public synonym times;
create public synonym times for ops$oracle.times;
grant execute on times to public;
