CREATE OR REPLACE VIEW APB_CALENDAR AS
WITH
N1 AS (SELECT 1 AS X FROM DUAL UNION ALL SELECT 1 AS X FROM DUAL),
N2 AS (SELECT A.X FROM N1 A, N1 B),
N3 AS (SELECT A.X FROM N2 A, N2 B),
N4 AS (SELECT A.X FROM N3 A, N3 B),
N5 AS (SELECT A.X FROM N4 A, N4 B), NUMS AS 
(SELECT ROW_NUMBER() OVER(ORDER BY X) AS N FROM N5) 
SELECT
TO_NUMBER(TO_CHAR(X.COL_DATE,'YYYY')) AS COL_YEAR,
TO_NUMBER(TO_CHAR(X.COL_DATE,'MM')) AS COL_MONTH,
TO_NUMBER(TO_CHAR(X.COL_DATE,'IW')) AS COL_WEEK,
TO_NUMBER(TO_CHAR(X.COL_DATE,'DD')) AS COL_DAY,
X.COL_DATE
FROM 
(
SELECT 
TRUNC(TO_DATE('1999-12-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) + N AS COL_DATE
FROM 
NUMS 
) X
WHERE 
X.COL_DATE >= TRUNC(TO_DATE('2000-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) AND
X.COL_DATE < TRUNC(TO_DATE('2050-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;

-- LEFT OUTER JOIN YOURTABLE ON TO_CHAR(APB_CALENDAR.COL_DATE,'YYYY-MM-DD') = TO_CHAR(YOURTABLE.ORDER_DATE,'YYYY-MM-DD')