1) Create database function to populate the time table in the database.
CREATE OR REPLACE PROCEDURE update_day_dim (start_date IN DATE, no_of_days IN NUMBER,Delete_data IN varchar2)
AS
mcount NUMBER;
mdate DATE;
BEGIN
if delete_data = 'Y' then
delete from xx_bi_time_day_d;
commit;
end if;
BEGIN
mcount := 1;
mdate := start_date;
FOR mcount IN 1 .. no_of_days
LOOP
INSERT INTO xx_bi_time_day_d
VALUES (TO_CHAR (mdate, 'MM/DD/YYYY'), mdate
, TO_CHAR (mdate, 'DD'), TO_CHAR (mdate, 'Day')
, TO_CHAR (mdate, 'MM-YYYY'), TO_CHAR (mdate, 'MM')
, TO_CHAR (mdate, 'W'), 'W' || TO_CHAR (mdate, 'W')
, TO_CHAR (mdate, 'YYYY'), TO_CHAR (mdate, 'Q')
, 'Q' || TO_CHAR (mdate, 'Q'), TO_CHAR (mdate, 'Month')
, TO_CHAR (mdate, 'MM')
, DECODE (TO_CHAR (mdate, 'Q')
, '1', 'H1'
, '2', 'H1'
, 'H2'
)
, DECODE (TO_CHAR (mdate, 'Q'), '1', '1', '2', '1', '2'));
mdate := mdate + 1;
END LOOP;
commit;
END;
END;*/
2) Create below table in the data base.
create table xx_bi_time_day_d
(current_date varchar2(15),
current_date_date date,
day_num number(15),
day_name varchar2(25),
period_name varchar2(15),
period_num number(15),
week_num number(15),
week_name varchar2(15),
period_year varchar2(4),
quater_num number(15),
quater_name varchar2(3),
month_name varchar2(25),
month_num number(15),
half_year_name varchar2(15),
half_year_num number(15)
);
3) To Populate the data in the time table, Execute the below function.
execute update_day_dim('01-JAN-1980',50000,'Y');
