CREATE OR REPLACE PROCEDURE WFSDW.SP_FILLDATE IS

/*

 -- @version $Id: loadWFSdim_DW.sql,v 1.1 2007/10/29 19:39:26 dweiner Exp $

*/

     vDate    date := to_date('01/01/1990','DD/MM/YYYY');
     endDate  date := to_date('31/12/2050','DD/MM/YYYY');

BEGIN

     LOOP

        INSERT INTO WFSDW.DIM_DATE (DATEWK,
                              CALENDERDATE,
                              DAYOFWEEKNAME,
                              DAYOFWEEKNUM,
                              DAYOFMONTH,
                              WEEKOFYEAR,
                              MONTHNAME,
                              MONTHNUM,
                              QUARTER,
                              YEARNUM,
                              CREATEDATE)
        VALUES( to_Char(vDate,'YYYYMMDD'),
                vDate,
                to_char(vDate,'Day'),
                to_char(vDate, 'D'),
                to_char(vDate,'DD'),
                to_char(vDate,'WW'),
                to_Char(vDate,'Month'),
                to_char(vDate,'MM'),
                to_char(vDate, 'Q'),
                to_char(vDate, 'YYYY'),
                sysdate);

        IF vDate = endDate Then

            COMMIT;

            EXIT;

        END IF;

        vDate := vDate + 1;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Data Loaded successfully');

EXCEPTION

   WHEN others THEN

        ROLLBACK;

        DBMS_OUTPUT.PUT_LINE('Wrong Date format please check the parameters');

END;
/


CREATE OR REPLACE PROCEDURE WFSDW.SP_FILLTIME IS

     vTime    date := to_date('01/01/1900 00:00:00','DD/MM/YYYY HH24:MI:SS');
     endTime  date := to_date('01/01/1900 23:59:59','DD/MM/YYYY HH24:MI:SS');

BEGIN

     LOOP

        INSERT INTO WFSDW.DIM_TIME (TIMEWK,
                              TIMEOFDAY,
                              STANDARDDISPLAY,
                              MILITARYDISPLAY,
                              STANDARDHOUR,
                              MILITARYHOUR,
                              MERIDIAN,
                              MINUTES,
                              MINUTESAFTERMIDNIGHT,
                              SECONDS,
                              SECONDSINHOUR,
                              SECONDSAFTERMIDNIGHT,
                              CREATEDATE)
        VALUES( to_Char(vTime,'HH24MISS'),
                vTime,
                to_char(vTime,'HH:MI:SS AM'),
                to_char(vTime, 'HH24:MI:SS'),
                to_char(vTime,'HH'),
                to_char(vTime,'HH24'),
                to_Char(vTime,'AM'),
                to_char(vTime,'MI'),
                (to_char(vTime, 'HH24')* to_char(vTime, 'MI')),
                to_char(vTime, 'SS'),
                (to_char(vTime, 'MI') * to_char(vTime, 'SS')),
                (to_char(vTime, 'HH24')* to_char(vTime, 'MI')* to_char(vTime, 'SS')),
                sysdate);

        IF vTime = endTime Then

            COMMIT;

            EXIT;

        END IF;

        vTime := vTime + 1/86400;

    END LOOP;

    DBMS_OUTPUT.PUT_LINE('Data Loaded successfully');

EXCEPTION

   WHEN others THEN

        ROLLBACK;

        DBMS_OUTPUT.PUT_LINE('Wrong Time format please check the parameters');

END;
/

exec WFSDW.SP_FILLTIME;

exec WFSDW.SP_FILLDATE;

INSERT INTO WFSDW.DIM_MONTH(MonthWK, MonthName, MonthNum, Quarter, YearNum, CreateDate)
SELECT to_Char(CalenderDate,'YYYYMM'), MonthName, MonthNum, Quarter, YearNum, sysdate
FROM WFSDW.DIM_DATE
GROUP BY to_Char(CalenderDate,'YYYYMM'), MonthName, MonthNum, Quarter, YearNum;

commit;
