Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\rtasst\wb_rt_timeseries_init.sql
CREATE OR REPLACE PROCEDURE EXEC_DONT_FAIL( P_CMD IN VARCHAR2 ) IS e_table_or_view_does_not_exist exception; pragma exception_init(e_table_or_view_does_not_exist, -942); e_type_does_not_exist exception; pragma exception_init(e_type_does_not_exist, -4043); e_sequence_does_not_exist exception; pragma exception_init(e_sequence_does_not_exist, -2289); BEGIN EXECUTE IMMEDIATE P_CMD; EXCEPTION WHEN e_table_or_view_does_not_exist OR e_type_does_not_exist OR e_sequence_does_not_exist THEN NULL; END; / DECLARE BEGIN -- -- -- (PACKAGED VERSION OWB_TIMESERIES_INIT.SQL) -- CONTAINS TIMESERIES DATA GENERATING TABLE FUNCTIONS AND HELPER FUNCTIONS. -- HISTORY -- SROYCHOW, August 11, 2003, Created. -- SROYCHOW, August 18, 2003, Add code for Fiscal Year. (with 544 + 445) -- -- ======================================================== ----------------------------------------------------------- --- --- DROP TYPES AND TABLE TYPES --- ----------------------------------------------------------- ------------------------------------------------- --- --- DAY IS COMMON TO CALENDAR AND FISCAL YEARS --- ------------------------------------------------- EXEC_DONT_FAIL('DROP FUNCTION DAY_GENERATOR_TFN'); EXEC_DONT_FAIL('DROP TYPE DAY_TYPE_TABLE'); EXEC_DONT_FAIL('DROP TYPE DAY_TYPE'); ------------------------------------------------- --- --- FISCAL CALENDAR: WEEK, MONTH, QUARTER, YEAR --- ------------------------------------------------- EXEC_DONT_FAIL('DROP FUNCTION FISCAL_WEEK_GENERATOR_TFN'); EXEC_DONT_FAIL('DROP TYPE FISCAL_WEEK_TYPE_TABLE'); EXEC_DONT_FAIL('DROP TYPE FISCAL_WEEK_TYPE'); EXEC_DONT_FAIL('DROP FUNCTION FISCAL_MONTH_GENERATOR_TFN'); EXEC_DONT_FAIL('DROP TYPE FISCAL_MONTH_TYPE_TABLE'); EXEC_DONT_FAIL('DROP TYPE FISCAL_MONTH_TYPE'); EXEC_DONT_FAIL('DROP FUNCTION FISCAL_QUARTER_GENERATOR_TFN'); EXEC_DONT_FAIL('DROP TYPE FISCAL_QUARTER_TYPE_TABLE'); EXEC_DONT_FAIL('DROP TYPE FISCAL_QUARTER_TYPE'); EXEC_DONT_FAIL('DROP FUNCTION FISCAL_YEAR_GENERATOR_TFN'); EXEC_DONT_FAIL('DROP TYPE FISCAL_YEAR_TYPE_TABLE'); EXEC_DONT_FAIL('DROP TYPE FISCAL_YEAR_TYPE'); ------------------------------------------------- --- --- CALENDAR: WEEK, MONTH, QUARTER, YEAR --- ------------------------------------------------- EXEC_DONT_FAIL('DROP FUNCTION CAL_WEEK_GENERATOR_TFN'); EXEC_DONT_FAIL('DROP TYPE CAL_WEEK_TYPE_TABLE'); EXEC_DONT_FAIL('DROP TYPE CAL_WEEK_TYPE'); EXEC_DONT_FAIL('DROP FUNCTION CAL_MONTH_GENERATOR_TFN'); EXEC_DONT_FAIL('DROP TYPE CAL_MONTH_TYPE_TABLE'); EXEC_DONT_FAIL('DROP TYPE CAL_MONTH_TYPE'); EXEC_DONT_FAIL('DROP FUNCTION CAL_QUARTER_GENERATOR_TFN'); EXEC_DONT_FAIL('DROP TYPE CAL_QUARTER_TYPE_TABLE'); EXEC_DONT_FAIL('DROP TYPE CAL_QUARTER_TYPE'); EXEC_DONT_FAIL('DROP FUNCTION CAL_YEAR_GENERATOR_TFN'); EXEC_DONT_FAIL('DROP TYPE CAL_YEAR_TYPE_TABLE'); EXEC_DONT_FAIL('DROP TYPE CAL_YEAR_TYPE'); ------------------------------------------------- --- --- OWB TIME SERIES --- ------------------------------------------------- EXEC_DONT_FAIL('DROP PACKAGE OWB_TIMESERIES'); END; / DROP PROCEDURE EXEC_DONT_FAIL; -- ======================================================== -- END OF DROPS -- ======================================================== -- ======================================================== -- ======================================================== -- BEGIN CREATING (TYPES AND TABLE TYPES) -- ======================================================== ------------------------------------------------- --- --- LEVEL 1 --- DAY IS COMMON TO CALENDAR AND FISCAL YEARS --- ------------------------------------------------- CREATE TYPE DAY_TYPE AS OBJECT ( -- -- GENERAL DAY INFO -- DAY_ID NUMBER, DAY DATE, DAY_CODE NUMBER, DAY_JULIANDATE NUMBER, DAY_DESCRIPTION VARCHAR2 (2000), DAY_NAME VARCHAR2 (25), DAY_STARTDATE DATE, DAY_ENDDATE DATE, DAY_TIMESPAN NUMBER, -- -- CALENDAR YEAR DAY INFO -- DAY_OF_CAL_WEEK NUMBER, DAY_OF_CAL_MONTH NUMBER, DAY_OF_CAL_QUARTER NUMBER, DAY_OF_CAL_YEAR NUMBER, -- -- FISCAL CALENDAR YEAR DAY INFO -- DAY_OF_FISCAL_WEEK NUMBER, DAY_OF_FISCAL_MONTH NUMBER, DAY_OF_FISCAL_QUARTER NUMBER, DAY_OF_FISCAL_YEAR NUMBER, -- -- CALENNDAR AND FISCAL NUMBERS -- CAL_YEAR_NUMBER NUMBER, CAL_QUARTER_NUMBER NUMBER, CAL_MONTH_NUMBER NUMBER, CAL_WEEK_NUMBER NUMBER, FISCAL_YEAR_NUMBER NUMBER, FISCAL_QUARTER_NUMBER NUMBER, FISCAL_MONTH_NUMBER NUMBER, FISCAL_WEEK_NUMBER NUMBER, -- -- LEVEL-RELATIONSHIP FIELDS -- CAL_YEAR_CODE NUMBER, CAL_QUARTER_CODE NUMBER, CAL_MONTH_CODE NUMBER, CAL_WEEK_CODE NUMBER, FISCAL_YEAR_CODE NUMBER, FISCAL_QUARTER_CODE NUMBER, FISCAL_MONTH_CODE NUMBER, FISCAL_WEEK_CODE NUMBER, CAL_YEAR_STARTDATE DATE, CAL_QUARTER_STARTDATE DATE, CAL_MONTH_STARTDATE DATE, CAL_WEEK_STARTDATE DATE, FISCAL_YEAR_STARTDATE DATE, FISCAL_QUARTER_STARTDATE DATE, FISCAL_MONTH_STARTDATE DATE, FISCAL_WEEK_STARTDATE DATE, -- -- ADDITIONAL INFO and IDs -- CAL_YID NUMBER, CAL_WID NUMBER, CAL_MID NUMBER, CAL_QID NUMBER, FISCAL_WID NUMBER, FISCAL_MID NUMBER, FISCAL_QID NUMBER, FISCAL_YID NUMBER, FISCAL_SDATE DATE, FISCAL_DOW NUMBER, FISCAL_TYPE NUMBER ); / CREATE TYPE DAY_TYPE_TABLE AS TABLE OF DAY_TYPE ; / ------------------------------------------------- --- --- LEVEL 2.1 --- FISCAL WEEK TYPE --- ------------------------------------------------- CREATE TYPE FISCAL_WEEK_TYPE AS OBJECT ( FISCAL_WEEK_ID NUMBER, FISCAL_WEEK_CODE NUMBER, FISCAL_WEEK_NUMBER NUMBER, FISCAL_WEEK_DESCRIPTION VARCHAR2 (2000), FISCAL_WEEK_NAME VARCHAR2 (25), FISCAL_WEEK_STARTDATE DATE, FISCAL_WEEK_ENDDATE DATE, FISCAL_WEEK_TIMESPAN NUMBER, FISCAL_WEEK_OF_MONTH NUMBER, FISCAL_WEEK_OF_QUARTER NUMBER, FISCAL_WEEK_OF_YEAR NUMBER, FISCAL_SDATE DATE, FISCAL_DOW NUMBER, FISCAL_TYPE NUMBER, FISCAL_MID NUMBER, FISCAL_QID NUMBER, FISCAL_YID NUMBER, -- -- OTHER LEVEL FISCAL NUMBERS -- FISCAL_YEAR_NUMBER NUMBER, FISCAL_QUARTER_NUMBER NUMBER, FISCAL_MONTH_NUMBER NUMBER, -- -- LEVEL-RELATIONSHIP FIELDS -- FISCAL_YEAR_CODE NUMBER, FISCAL_QUARTER_CODE NUMBER, FISCAL_MONTH_CODE NUMBER, FISCAL_YEAR_STARTDATE DATE, FISCAL_QUARTER_STARTDATE DATE, FISCAL_MONTH_STARTDATE DATE ); / CREATE TYPE FISCAL_WEEK_TYPE_TABLE AS TABLE OF FISCAL_WEEK_TYPE ; / ------------------------------------------------- --- --- LEVEL 2.2 --- FISCAL MONTH TYPE --- ------------------------------------------------- CREATE TYPE FISCAL_MONTH_TYPE AS OBJECT ( FISCAL_MONTH_ID NUMBER, FISCAL_MONTH_CODE NUMBER, FISCAL_MONTH_NUMBER NUMBER, FISCAL_MONTH_DESCRIPTION VARCHAR2 (2000), FISCAL_MONTH_NAME VARCHAR2 (25), FISCAL_MONTH_STARTDATE DATE, FISCAL_MONTH_ENDDATE DATE, FISCAL_MONTH_TIMESPAN NUMBER, FISCAL_MONTH_OF_QUARTER NUMBER, FISCAL_MONTH_OF_YEAR NUMBER, FISCAL_SDATE DATE, FISCAL_DOW NUMBER, FISCAL_TYPE NUMBER, FISCAL_QID NUMBER, FISCAL_YID NUMBER, -- -- FISCAL NUMBERS -- FISCAL_YEAR_NUMBER NUMBER, FISCAL_QUARTER_NUMBER NUMBER, -- -- LEVEL-RELATIONSHIP FIELDS -- FISCAL_YEAR_CODE NUMBER, FISCAL_QUARTER_CODE NUMBER, FISCAL_YEAR_STARTDATE DATE, FISCAL_QUARTER_STARTDATE DATE ); / CREATE TYPE FISCAL_MONTH_TYPE_TABLE AS TABLE OF FISCAL_MONTH_TYPE ; / ------------------------------------------------- --- --- LEVEL 2.3 --- FISCAL QUARTER TYPE --- ------------------------------------------------- CREATE TYPE FISCAL_QUARTER_TYPE AS OBJECT ( FISCAL_QUARTER_ID NUMBER, FISCAL_QUARTER_CODE NUMBER, FISCAL_QUARTER_NUMBER NUMBER, FISCAL_QUARTER_DESCRIPTION VARCHAR2 (2000), FISCAL_QUARTER_NAME VARCHAR2 (25), FISCAL_QUARTER_STARTDATE DATE, FISCAL_QUARTER_ENDDATE DATE, FISCAL_QUARTER_TIMESPAN NUMBER, FISCAL_QUARTER_OF_YEAR NUMBER, FISCAL_SDATE DATE, FISCAL_DOW NUMBER, FISCAL_TYPE NUMBER, FISCAL_YID NUMBER, -- -- FISCAL NUMBERS -- FISCAL_YEAR_NUMBER NUMBER, -- -- LEVEL-RELATIONSHIP FIELDS -- FISCAL_YEAR_CODE NUMBER, FISCAL_YEAR_STARTDATE DATE ); / CREATE TYPE FISCAL_QUARTER_TYPE_TABLE AS TABLE OF FISCAL_QUARTER_TYPE ; / ------------------------------------------------- --- --- LEVEL 2.4 --- FISCAL YEAR TYPE --- ------------------------------------------------- CREATE TYPE FISCAL_YEAR_TYPE AS OBJECT ( FISCAL_YEAR_ID NUMBER, FISCAL_YEAR_CODE NUMBER, FISCAL_YEAR_NUMBER NUMBER, FISCAL_YEAR_DESCRIPTION VARCHAR2 (2000), FISCAL_YEAR_NAME VARCHAR2 (25), FISCAL_YEAR_STARTDATE DATE, FISCAL_YEAR_ENDDATE DATE, FISCAL_YEAR_TIMESPAN NUMBER ); / CREATE TYPE FISCAL_YEAR_TYPE_TABLE AS TABLE OF FISCAL_YEAR_TYPE ; / ------------------------------------------------- --- --- LEVEL 1.1 --- CALENDAR WEEK TYPE --- ------------------------------------------------- CREATE TYPE CAL_WEEK_TYPE AS OBJECT ( CAL_WEEK_ID NUMBER, CAL_WEEK_CODE NUMBER, CAL_WEEK_NUMBER NUMBER, CAL_WEEK_DESCRIPTION VARCHAR2 (2000), CAL_WEEK_NAME VARCHAR2 (25), CAL_WEEK_STARTDATE DATE, CAL_WEEK_ENDDATE DATE, CAL_WEEK_TIMESPAN NUMBER ); / CREATE TYPE CAL_WEEK_TYPE_TABLE AS TABLE OF CAL_WEEK_TYPE ; / ------------------------------------------------- --- --- LEVEL 1.2 --- CALENDAR MONTH TYPE --- ------------------------------------------------- CREATE TYPE CAL_MONTH_TYPE AS OBJECT ( CAL_MONTH_ID NUMBER, CAL_MONTH_CODE NUMBER, CAL_MONTH_NUMBER NUMBER, CAL_MONTH_DESCRIPTION VARCHAR2 (2000), CAL_MONTH_NAME VARCHAR2 (2000), CAL_MONTH_STARTDATE DATE, CAL_MONTH_ENDDATE DATE, CAL_MONTH_TIMESPAN NUMBER, CAL_MONTH_OF_QUARTER NUMBER, CAL_MONTH_OF_YEAR NUMBER, CAL_YID NUMBER, CAL_QID NUMBER, -- -- OTHER LEVELS NUMBER -- CAL_YEAR_NUMBER NUMBER, CAL_QUARTER_NUMBER NUMBER, -- -- LEVEL-RELATIONSHIP FIELDS -- CAL_YEAR_CODE NUMBER, CAL_QUARTER_CODE NUMBER, CAL_YEAR_STARTDATE DATE, CAL_QUARTER_STARTDATE DATE ); / CREATE TYPE CAL_MONTH_TYPE_TABLE AS TABLE OF CAL_MONTH_TYPE ; / ------------------------------------------------- --- --- LEVEL 1.3 --- CALENDAR QUARTER TYPE --- ------------------------------------------------- CREATE TYPE CAL_QUARTER_TYPE AS OBJECT ( CAL_QUARTER_ID NUMBER, CAL_QUARTER_CODE NUMBER, CAL_QUARTER_NUMBER NUMBER, CAL_QUARTER_OF_YEAR NUMBER, CAL_QUARTER_DESCRIPTION VARCHAR2 (2000), CAL_QUARTER_NAME VARCHAR2 (25), CAL_QUARTER_STARTDATE DATE, CAL_QUARTER_ENDDATE DATE, CAL_QUARTER_TIMESPAN NUMBER, CAL_YID NUMBER, -- -- YEAR NUMBER -- CAL_YEAR_NUMBER NUMBER, -- -- LEVEL-RELATIONSHIP FIELDS -- CAL_YEAR_CODE NUMBER, CAL_YEAR_STARTDATE DATE ); / CREATE TYPE CAL_QUARTER_TYPE_TABLE AS TABLE OF CAL_QUARTER_TYPE ; / ------------------------------------------------- --- --- LEVEL 1.4 --- CALENDAR YEAR TYPE --- ------------------------------------------------- CREATE TYPE CAL_YEAR_TYPE AS OBJECT ( CAL_YEAR_ID NUMBER, CAL_YEAR_CODE NUMBER, CAL_YEAR_NUMBER NUMBER, CAL_YEAR_DESCRIPTION VARCHAR2 (2000), CAL_YEAR_NAME VARCHAR2 (25), CAL_YEAR_STARTDATE DATE, CAL_YEAR_ENDDATE DATE, CAL_YEAR_TIMESPAN NUMBER ); / CREATE TYPE CAL_YEAR_TYPE_TABLE AS TABLE OF CAL_YEAR_TYPE ; / -- ======================================================== -- END CREATING (TYPES AND TABLE TYPES) -- ========================================================
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de