pivotronics

CREATE OR REPLACE TYPE DATE_OBJ AS OBJECT(DATE_KEY VARCHAR2(8), DATE_DT DATE, DATE_TYPE VARCHAR2(10), DAY_OF_MONTH SMALLINT, CAL_DAY_OF_YEAR SMALLINT, DAY_OF_WEEK_FULL_NAME VARCHAR2(9)
, DAY_OF_WEEK_SHORT_NAME CHAR(3), CAL_MONTH_FULL_NAME VARCHAR2(9), CAL_MONTH_SHORT_NAME CHAR(3), CAL_MONTH_ENUM SMALLINT, CAL_QTR_NAME CHAR(2), CAL_QTR_ENUM SMALLINT, CAL_HYR_NAME CHAR(2)
, CAL_HYR_ENUM SMALLINT, CAL_YEAR INT, START_OF_MONTH DATE, END_OF_MONTH DATE);

CREATE OR REPLACE TYPE CALENDAR_DATES IS TABLE OF DATE_OBJ;

CREATE OR REPLACE FUNCTION UDF_CALENDAR_GENERATOR(START_DATE IN DATE, END_DATE IN DATE)
RETURN CALENDAR_DATES
IS
CALENDAR_DATE CALENDAR_DATES;
BEGIN
SELECT CAST(MULTISET(WITH RCTE_DATES (DATE_DT)
AS
(SELECT START_DATE AS DATE_DT
FROM DUAL
UNION ALL
SELECT DATE_DT + 1 AS DATE_DT
FROM RCTE_DATES
WHERE DATE_DT + 1 <= END_DATE
)
SELECT CAST(EXTRACT(YEAR FROM DATE_DT) AS CHAR(4)) || SUBSTR(’00’ || EXTRACT(MONTH FROM DATE_DT), -2,2) || SUBSTR(’00’ || EXTRACT(DAY FROM DATE_DT), -2, 2) AS DATE_KEY
, DATE_DT
, ‘DATE’ AS DATE_TYPE
, EXTRACT(DAY FROM DATE_DT) AS DAY_OF_MONTH
, CAST(TO_CHAR(DATE_DT, ‘DDD’) AS SMALLINT) AS CAL_DAY_OF_YEAR
, TO_CHAR(DATE_DT, ‘DAY’) AS DAY_OF_WEEK_FULL_NAME
, TO_CHAR(DATE_DT, ‘DY’) AS DAY_OF_WEEK_SHORT_NAME
, TO_CHAR(DATE_DT, ‘MONTH’) AS CAL_MONTH_FULL_NAME
, TO_CHAR(DATE_DT, ‘MON’) AS CAL_MONTH_SHORT_NAME
, EXTRACT(MONTH FROM DATE_DT) AS CAL_MONTH_ENUM
, ‘Q’ || TO_CHAR(DATE_DT, ‘Q’) AS CAL_QTR_NAME
, CAST(TO_CHAR(DATE_DT, ‘Q’) AS SMALLINT) AS CAL_QTR_ENUM
, ‘H’ || CASE WHEN EXTRACT(MONTH FROM DATE_DT) IN (1,2,3,4,5,6)
THEN 1
ELSE 2
END AS CAL_HYR_NAME
, CASE WHEN EXTRACT(MONTH FROM DATE_DT) IN (1,2,3,4,5,6)
THEN 1
ELSE 2
END AS CAL_HYR_ENUM
, EXTRACT(YEAR FROM DATE_DT) AS CAL_YEAR
, TRUNC(DATE_DT, ‘MONTH’) AS START_OF_MONTH
, TRUNC(ADD_MONTHS(DATE_DT, 1), ‘MONTH’)-1 AS END_OF_MONTH
FROM RCTE_DATES
UNION
SELECT ‘999999’ AS DATE_KEY
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS DATE_DT
, ‘NA’ AS DATE_TYPE
, 0 AS DAY_OF_MONTH
, 0 AS CAL_DAY_OF_YEAR
, ‘NA’ AS DAY_OF_WEEK_FULL_NAME
, ‘NA’ AS DAY_OF_WEEK_SHORT_NAME
, ‘NA’ AS CAL_MONTH_FULL_NAME
, ‘NA’ AS CAL_MONTH_SHORT_NAME
, 0 AS CAL_MONTH_ENUM
, ‘NA’ AS CAL_QTR_NAME
, 0 AS CAL_QTR_ENUM
, ‘NA’ AS CAL_HYR_NAME
, 0 AS CAL_HYR_ENUM
, 9999 AS CAL_YEAR
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS START_OF_MONTH
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS END_OF_MONTH
FROM DUAL
)
AS CALENDAR_DATES
)
INTO CALENDAR_DATE
FROM DUAL;
RETURN CALENDAR_DATE;
END;