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)
-- ========================================================