Rem
Rem Copyright (c) 2000 by Oracle Corporation
Rem
Rem NAME
Rem   wbrttime.sql
Rem FUNCTION
Rem   Create data and time related objects
Rem NOTES
Rem   - This file can be tested using SQL*Plus.
Rem   - OWB developers must make sure this file is also runnable
Rem     using Runtime assistant. 
Rem
Rem MODIFIED
Rem  Yu.Gong        12/06/2000 -  Created.
Rem
Rem Rename table, sequences.

--------------
--  TABLES  --
--------------

CREATE TABLE wb_rt_time_src (
  id_col NUMBER PRIMARY KEY
)
ORGANIZATION INDEX;

---------------
-- SEQUENCES --
---------------

CREATE SEQUENCE wb_rt_week_seq start with 1;
CREATE SEQUENCE wb_rt_month_seq start with 1;
CREATE SEQUENCE wb_rt_quarter_seq start with 1;
CREATE SEQUENCE wb_rt_year_seq start with 1;

------------------------------
-- PUBLIC PACKAGE           --
-- PROCEDURES AND FUNCTIONS --
------------------------------

CREATE OR REPLACE PACKAGE wbrttime IS

  FUNCTION week_id_func(day_of_week IN NUMBER)
    RETURN NUMBER;

  FUNCTION month_id_func(day_of_month IN NUMBER)
  RETURN NUMBER;

  FUNCTION quarter_id_func(day_of_month IN NUMBER,
                           month_of_year IN NUMBER)
  RETURN NUMBER;

  FUNCTION year_id_func(day_of_year IN NUMBER)
  RETURN NUMBER;

  PROCEDURE time_gen_populate;

  FUNCTION create_smart_key(act_date IN DATE)
  RETURN NUMBER;
END wbrttime;
/

CREATE OR REPLACE PACKAGE BODY wbrttime IS

  FUNCTION week_id_func(day_of_week IN NUMBER)
  RETURN NUMBER IS
    week_id BINARY_INTEGER;
  BEGIN
    IF (day_of_week = 1) THEN
      SELECT wb_rt_week_seq.NEXTVAL INTO week_id FROM dual;
    ELSE
      SELECT wb_rt_week_seq.CURRVAL INTO week_id FROM dual;
    END IF;
    RETURN week_id;
  END;

  FUNCTION month_id_func(day_of_month IN NUMBER)
  RETURN NUMBER IS
    month_id BINARY_INTEGER;
  BEGIN
    IF (day_of_month = 1) THEN
      SELECT wb_rt_month_seq.NEXTVAL INTO month_id FROM dual;
    ELSE
      SELECT wb_rt_month_seq.CURRVAL INTO month_id FROM dual;
    END IF;
    RETURN month_id;
  END;


  FUNCTION quarter_id_func(day_of_month IN NUMBER,
                           month_of_year IN NUMBER)
    RETURN NUMBER IS
    quarter_id BINARY_INTEGER;
  BEGIN
    IF (month_of_year IN (1,4,7,10)) THEN
      IF (day_of_month = 1) THEN
        SELECT wb_rt_quarter_seq.NEXTVAL INTO quarter_id FROM dual;
      ELSE
        SELECT wb_rt_quarter_seq.CURRVAL INTO quarter_id FROM dual;
      END IF;
    ELSE
      SELECT wb_rt_quarter_seq.CURRVAL INTO quarter_id FROM dual;
    END IF;
    RETURN quarter_id;
  END;


  FUNCTION year_id_func(day_of_year IN NUMBER)
    RETURN NUMBER IS
    year_id BINARY_INTEGER;
  BEGIN
    IF (day_of_year = 1) THEN
      SELECT wb_rt_year_seq.NEXTVAL INTO year_id FROM dual;
    ELSE
      SELECT wb_rt_year_seq.CURRVAL INTO year_id FROM dual;
    END IF;
    RETURN year_id;
  END;


  PROCEDURE time_gen_populate IS
    seq NUMBER :=0;
  BEGIN
    FOR seq IN 0..37000 LOOP
      INSERT INTO wb_rt_time_src VALUES(seq);
    END LOOP;
  END time_gen_populate;


  FUNCTION create_smart_key(act_date IN DATE)
    RETURN NUMBER IS
    retstring  VARCHAR2(12);
    temp_value NUMBER;
  BEGIN
    retstring := to_char(act_date,'YYYY');
    temp_value := to_number(to_char(act_date, 'Q'));
    retstring := retstring||temp_value;
    temp_value := to_number(to_char(act_date, 'MM'));
    IF (temp_value < 10) THEN
      retstring := retstring||'0'||temp_value;
    ELSE
      retstring := retstring||temp_value;
    END IF;
    temp_value := to_number(to_char(act_date, 'WW'));
    IF (temp_value < 10) THEN
      retstring := retstring||'0'||temp_value;
    ELSE
      retstring := retstring||temp_value;
    END IF;
    temp_value := to_number(to_char(act_date, 'DD'));
    IF (temp_value < 10) THEN
      retstring := retstring||'0'||temp_value;
    ELSE
      retstring := retstring||temp_value;
    END IF;
    RETURN to_number(retstring);
  END;
END wbrttime;
/