[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT Max(CAL_YEAR_NUMBER)
into v_latest_year
FROM MTH_GREGORIAN_CALENDAR
WHERE calendar_quarter_id IS NULL;
mth_util_pkg.log_msg('Inserting Year level', mth_util_pkg.G_DBG_OTH);
INSERT INTO MTH_GREGORIAN_CALENDAR
(DIMENSION_KEY,
CALENDAR_YEAR_END_DATE,
CALENDAR_YEAR_ID,
CAL_YEAR_NUMBER,
CALENDAR_YEAR_NAME,
CALENDAR_YEAR_TIME_SPAN,
CALENDAR_YEAR_CAL_YEAR_CODE,
CALENDAR_YEAR_DESCRIPTION,
CALENDAR_YEAR_START_DATE,
CREATION_DATE,
LAST_UPDATE_DATE)
VALUES
(MTH_GREGORIAN_CALENDAR_S.NEXTVAL * (-1),
V_year_end,
MTH_GREGORIAN_CALENDAR_S.CURRVAL * (-1),
V_YEAR,
V_YEAR,
(V_year_end - V_year_start + 1),
V_YEAR,
V_YEAR,
V_year_start,
SYSDATE,
SYSDATE);
mth_util_pkg.log_msg('Inserting Year level Complete', mth_util_pkg.G_DBG_OTH);
2. Insert quarter entries at the quarter level by joinning
MTH_GREGORIAN_CALENDAR that has the newly created year entries
with a SELECT subquery with four rows for each quarter in a year, then selecting year
level information from MTH_GREGORIAN_CALENDAR and constructing quarter
level information, and lastly inserting quarter entries into
MTH_GREGORIAN_CALENDAR.
*/
mth_util_pkg.log_msg('Inserting Quarter level', mth_util_pkg.G_DBG_OTH);
INSERT INTO MTH_GREGORIAN_CALENDAR
(DIMENSION_KEY,
CALENDAR_YEAR_END_DATE,
CALENDAR_YEAR_ID,
CAL_YEAR_NUMBER,
CALENDAR_YEAR_NAME,
CALENDAR_YEAR_TIME_SPAN,
CALENDAR_YEAR_CAL_YEAR_CODE,
CALENDAR_YEAR_DESCRIPTION,
CALENDAR_YEAR_START_DATE,
CALENDAR_QUARTER_TIME_SPAN,
CALENDAR_QUART_CAL_QUARTER_CO,
CALENDAR_QUARTER_START_DATE,
CALENDAR_QUARTER_END_DATE,
CALENDAR_QUARTER_ID,
CALENDAR_QUARTER_DESCRIPTION,
CAL_QUARTER_NUMBER,
CALENDAR_QUARTER_NAME,
QUARTER_OF_YEAR,
CREATION_DATE, LAST_UPDATE_DATE)
SELECT
MTH_GREGORIAN_CALENDAR_S.NEXTVAL * (-1) AS DIMENSION_KEY,
CALENDAR_YEAR_END_DATE,
CALENDAR_YEAR_ID,
CAL_YEAR_NUMBER,
CALENDAR_YEAR_NAME,
CALENDAR_YEAR_TIME_SPAN,
CALENDAR_YEAR_CAL_YEAR_CODE,
CALENDAR_YEAR_DESCRIPTION,
CALENDAR_YEAR_START_DATE,
(ADD_MONTHS(CALENDAR_YEAR_START_DATE, quarter * 3) -
ADD_MONTHS(CALENDAR_YEAR_START_DATE, (quarter-1) *3))
AS CALENDAR_QUARTER_TIME_SPAN,
TO_CHAR(CALENDAR_YEAR_START_DATE, 'YYYY') || quarter || quarter
AS CALENDAR_QUART_CAL_QUARTER_CO,
ADD_MONTHS(CALENDAR_YEAR_START_DATE, (quarter-1) *3)
AS CALENDAR_QUARTER_START_DATE,
ADD_MONTHS(CALENDAR_YEAR_START_DATE, quarter * 3) - 1
AS CALENDAR_QUARTER_END_DATE,
MTH_GREGORIAN_CALENDAR_S.CURRVAL * (-1)
AS CALENDAR_QUARTER_ID,
'Quarter ' || quarter || ' ' || CALENDAR_YEAR_CAL_YEAR_CODE
AS CALENDAR_QUARTER_DESCRIPTION,
(Quarter * 10 + quarter) AS CAL_QUARTER_NUMBER,
'Q' || quarter || ' ' || CALENDAR_YEAR_CAL_YEAR_CODE
AS CALENDAR_QUARTER_NAME,
Quarter AS QUARTER_OF_YEAR,
SYSDATE,
SYSDATE
FROM MTH_GREGORIAN_CALENDAR CAL,
(SELECT LEVEL AS quarter FROM dual CONNECT BY LEVEL<=4) q
WHERE CAL.CALENDAR_QUARTER_ID IS NULL AND
CAL. CAL_YEAR_NUMBER >= P_START_YEAR;
mth_util_pkg.log_msg('Inserting Quarter level completed', mth_util_pkg.G_DBG_OTH);
3. Insert month entries at the month level by joinning
MTH_GREGORIAN_CALENDAR that has the newly created quarter entries
with a SELECT subquery with three rows for each month in a quarter, then selecting
year and quarter level information from MTH_GREGORIAN_CALENDAR and
constructing month level information, and lastly inserting month entries into
MTH_GREGORIAN_CALENDAR.
*/
mth_util_pkg.log_msg('Inserting Month level', mth_util_pkg.G_DBG_OTH);
INSERT INTO MTH_GREGORIAN_CALENDAR (DIMENSION_KEY,
CALENDAR_YEAR_END_DATE,
CALENDAR_YEAR_ID,
CAL_YEAR_NUMBER,
CALENDAR_YEAR_NAME,
CALENDAR_YEAR_TIME_SPAN,
CALENDAR_YEAR_CAL_YEAR_CODE,
CALENDAR_YEAR_DESCRIPTION,
CALENDAR_YEAR_START_DATE,
CALENDAR_QUARTER_TIME_SPAN,
CALENDAR_QUART_CAL_QUARTER_CO,
CALENDAR_QUARTER_START_DATE,
CALENDAR_QUARTER_END_DATE,
CALENDAR_QUARTER_ID,
CALENDAR_QUARTER_DESCRIPTION,
CAL_QUARTER_NUMBER,
CALENDAR_QUARTER_NAME,
QUARTER_OF_YEAR,
CALENDAR_MONTH_ID,
MONTH_OF_YEAR,
CALENDAR_MONTH_DESCRIPTION,
CAL_MONTH_NUMBER,
CALENDAR_MONTH_TIME_SPAN,
CALENDAR_MONTH_START_DATE,
CALENDAR_MONTH_CAL_MONTH_CODE,
CALENDAR_MONTH_NAME,
MONTH_OF_QUARTER,
CALENDAR_MONTH_END_DATE,
CREATION_DATE,
LAST_UPDATE_DATE)
SELECT MTH_GREGORIAN_CALENDAR_S.NEXTVAL * (-1) AS DIMENSION_KEY,
-- Year and Quarter Columns--
CALENDAR_YEAR_END_DATE,
CALENDAR_YEAR_ID,
CAL_YEAR_NUMBER,
CALENDAR_YEAR_NAME,
CALENDAR_YEAR_TIME_SPAN,
CALENDAR_YEAR_CAL_YEAR_CODE,
CALENDAR_YEAR_DESCRIPTION,
CALENDAR_YEAR_START_DATE,
CALENDAR_QUARTER_TIME_SPAN,
CALENDAR_QUART_CAL_QUARTER_CO,
CALENDAR_QUARTER_START_DATE,
CALENDAR_QUARTER_END_DATE,
CALENDAR_QUARTER_ID,
CALENDAR_QUARTER_DESCRIPTION,
CAL_QUARTER_NUMBER,
CALENDAR_QUARTER_NAME,
QUARTER_OF_YEAR,
-- Year and Quarter Columns--
MTH_GREGORIAN_CALENDAR_S.CURRVAL * (-1)
AS CALENDAR_MONTH_ID,
((QUARTER_OF_YEAR - 1) * 3 + month)
AS MONTH_OF_YEAR,
TO_CHAR(ADD_MONTHS(CALENDAR_QUARTER_START_DATE, (month - 1)),'Month YYYY')
AS CALENDAR_MONTH_DESCRIPTION,
((QUARTER_OF_YEAR - 1) * 3 + MONTH)
AS CAL_MONTH_NUMBER,
(ADD_MONTHS(CALENDAR_QUARTER_START_DATE, month ) -
ADD_MONTHS(CALENDAR_QUARTER_START_DATE, (month - 1)) )
AS CALENDAR_MONTH_TIME_SPAN,
ADD_MONTHS(CALENDAR_QUARTER_START_DATE, (month - 1))
AS CALENDAR_MONTH_START_DATE,
TO_NUMBER(
TO_CHAR(
ADD_MONTHS(
CALENDAR_QUARTER_START_DATE, (month - 1)
),
'YYYYMM')
) AS CALENDAR_MONTH_CAL_MONTH_CODE,
TO_CHAR(
ADD_MONTHS(
CALENDAR_QUARTER_START_DATE, (month - 1)
),'Mon YYYY')
AS CALENDAR_MONTH_NAME,
MONTH AS MONTH_OF_QUARTER,
(ADD_MONTHS(CALENDAR_QUARTER_START_DATE, month ) - 1)
AS CALENDAR_MONTH_END_DATE,
SYSDATE AS CREATION_DATE,
SYSDATE AS LAST_UPDATE_DATE
FROM MTH_GREGORIAN_CALENDAR cal,
(SELECT LEVEL AS MONTH FROM dual CONNECT BY LEVEL<=3) MONTH
WHERE cal.CALENDAR_QUARTER_ID IS NOT NULL
AND cal.CALENDAR_MONTH_ID IS NULL
AND cal. CAL_YEAR_NUMBER >= P_START_YEAR;
mth_util_pkg.log_msg('Inserting Month level completed', mth_util_pkg.G_DBG_OTH);
4. Insert day entries at the Day level by joinning
MTH_GREGORIAN_CALENDAR that has the newly created month level entries
with a SELECT subquery with maximal 31 rows for each day in a month, then selecting
year, quarter, and month level information from MTH_GREGORIAN_CALENDAR and
constructing day level information, and lastly inserting day entries into
MTH_GREGORIAN_CALENDAR.
*/
mth_util_pkg.log_msg('Inserting Day level', mth_util_pkg.G_DBG_OTH);
INSERT INTO MTH_GREGORIAN_CALENDAR (DIMENSION_KEY,
-- Year, Quarter and Month Columns--
CALENDAR_YEAR_END_DATE,
CALENDAR_YEAR_ID,
CAL_YEAR_NUMBER,
CALENDAR_YEAR_NAME,
CALENDAR_YEAR_TIME_SPAN,
CALENDAR_YEAR_CAL_YEAR_CODE,
CALENDAR_YEAR_DESCRIPTION,
CALENDAR_YEAR_START_DATE,
CALENDAR_QUARTER_TIME_SPAN,
CALENDAR_QUART_CAL_QUARTER_CO,
CALENDAR_QUARTER_START_DATE,
CALENDAR_QUARTER_END_DATE,
CALENDAR_QUARTER_ID,
CALENDAR_QUARTER_DESCRIPTION,
CAL_QUARTER_NUMBER,
CALENDAR_QUARTER_NAME,
QUARTER_OF_YEAR,
CALENDAR_MONTH_ID,
MONTH_OF_YEAR,
CALENDAR_MONTH_DESCRIPTION,
CAL_MONTH_NUMBER,
CALENDAR_MONTH_TIME_SPAN,
CALENDAR_MONTH_START_DATE,
CALENDAR_MONTH_CAL_MONTH_CODE,
CALENDAR_MONTH_NAME,
MONTH_OF_QUARTER,
CALENDAR_MONTH_END_DATE,
-- Year, Quarter and Month Columns--
DAY_DESCRIPTION,
DAY_NAME,
DAY_START_DATE,
DAY,
DAY_END_DATE,
DAY_OF_CAL_YEAR,
DAY_ID,
DAY_OF_CAL_WEEK,
DAY_OF_CAL_QUARTER,
JULIAN_DATE,
DAY_TIME_SPAN,
DAY_OF_CAL_MONTH,
DAY_DAY_CODE,
CREATION_DATE,
LAST_UPDATE_DATE)
SELECT MTH_GREGORIAN_CALENDAR_S.NEXTVAL AS DIMENSION_KEY,
-- Year, Quarter and Month Columns--
CALENDAR_YEAR_END_DATE,
CALENDAR_YEAR_ID,
CAL_YEAR_NUMBER,
CALENDAR_YEAR_NAME,
CALENDAR_YEAR_TIME_SPAN,
CALENDAR_YEAR_CAL_YEAR_CODE,
CALENDAR_YEAR_DESCRIPTION,
CALENDAR_YEAR_START_DATE,
CALENDAR_QUARTER_TIME_SPAN,
CALENDAR_QUART_CAL_QUARTER_CO,
CALENDAR_QUARTER_START_DATE,
CALENDAR_QUARTER_END_DATE,
CALENDAR_QUARTER_ID,
CALENDAR_QUARTER_DESCRIPTION,
CAL_QUARTER_NUMBER,
CALENDAR_QUARTER_NAME,
QUARTER_OF_YEAR,
CALENDAR_MONTH_ID,
MONTH_OF_YEAR,
CALENDAR_MONTH_DESCRIPTION,
CAL_MONTH_NUMBER,
CALENDAR_MONTH_TIME_SPAN,
CALENDAR_MONTH_START_DATE,
CALENDAR_MONTH_CAL_MONTH_CODE,
CALENDAR_MONTH_NAME,
MONTH_OF_QUARTER,
CALENDAR_MONTH_END_DATE,
-- Year, Quarter and Month Columns--
TO_CHAR (CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1,'dd-MON-YYYY')
AS DAY_DESCRIPTION,
TO_CHAR (CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1,'dd-MON-YYYY')
AS DAY_NAME,
(CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1)
AS DAY_START_DATE,
(CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1)
AS DAY,
(CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1)
AS DAY_END_DATE,
(CALENDAR_MONTH_START_DATE - CALENDAR_YEAR_START_DATE + DAY_IN_MONTH)
AS DAY_OF_CAL_YEAR,
MTH_GREGORIAN_CALENDAR_S.CURRVAL
AS DAY_ID,
TO_CHAR (CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1,'D')
AS DAY_OF_CAL_WEEK,
/* In OWB implementation, the week starts on Saturday (Day 1) and ends on Sunday (Day 7). In Oracle database, the week starts on Sunday (Day 1). Here we use the default used by Oracle database.*/
(CALENDAR_MONTH_START_DATE - CALENDAR_QUARTER_START_DATE + DAY_IN_MONTH)
AS DAY_OF_CAL_QUARTER,
TO_NUMBER(
TO_CHAR(
CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1
, 'J'))
AS JULIAN_DATE,
1 AS DAY_TIME_SPAN,
DAY_IN_MONTH AS DAY_OF_CAL_MONTH,
TO_NUMBER(
TO_CHAR(CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1
, 'YYYYMMDD'))
AS DAY_DAY_CODE,
SYSDATE AS CREATION_DATE,
SYSDATE AS LAST_UPDATE_DATE
FROM MTH_GREGORIAN_CALENDAR cal,
(SELECT LEVEL AS DAY_IN_MONTH FROM dual CONNECT BY LEVEL<=31) days
WHERE cal.DAY_ID IS NULL
AND cal.CALENDAR_MONTH_ID IS NOT NULL
AND cal.CAL_YEAR_NUMBER >= P_START_YEAR
AND (cal.CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1 < ADD_MONTHS(cal.CALENDAR_MONTH_START_DATE, 1)
);
mth_util_pkg.log_msg('Inserting Day level completed', mth_util_pkg.G_DBG_OTH);
SELECT days.day + ((60*60*(hours.hour-1))/86400) from_time,
days.day + (((60*60*(hours.hour))-1)/86400) to_time,
To_Char(days.DAY,'DD-MON-YYYY')||'-'||hours.HOUR hour_pk
FROM (SELECT day
FROM mth_gregorian_calendar
WHERE day IS NOT NULL
UNION
SELECT day
FROM mth_445_period_calendar
WHERE day IS NOT NULL
UNION
SELECT report_date day
FROM mth_day_d) days,
(SELECT LEVEL HOUR
FROM dual
CONNECT BY LEVEL <= 24) hours
) cal_hours
ON
( mhd.hour_pk = cal_hours.hour_pk)
WHEN NOT MATCHED THEN
INSERT
( mhd.hour_pk_key,
mhd.hour_pk,
mhd.from_time,
mhd.to_time,
mhd.system_fk_key,
mhd.creation_date,
mhd.last_update_date,
mhd.creation_system_id,
mhd.last_update_system_id
)
VALUES
( MTH_TIME_S.NEXTVAL,
cal_hours.hour_pk,
cal_hours.from_time,
cal_hours.to_time,
v_ua_val,
v_log_date,
v_log_date,
v_ua_val,
v_ua_val
);
SELECT To_Number(To_Char(v_month_end_date, 'D')) into v_week_day_on_month_end FROM dual;
INSERT INTO MTH_445_PERIOD_CALENDAR
(DIMENSION_KEY,
FISCAL_YEAR_ID,
FISCAL_YEAR_NAME,
FIS_YEAR_NUMBER,
FISCAL_YEAR_START_DATE,
FISCAL_YEAR_TIME_SPAN,
FISCAL_YEAR_FIS_YEAR_CODE,
FISCAL_YEAR_DESCRIPTION,
FISCAL_YEAR_END_DATE,
CREATION_DATE,
LAST_UPDATE_DATE)
values
(MTH_445_PERIOD_CALENDAR_S.NEXTVAL * (-1),
MTH_445_PERIOD_CALENDAR_S.CURRVAL * (-1),
v_year,
v_year,
v_year_start_date,
(v_year_end_date - v_year_start_date + 1),
v_year,
'Fiscal Year ' || v_year,
v_year_end_date,
SYSDATE,
SYSDATE);
mth_util_pkg.log_msg('Inserted ' || v_num || ' rows.',mth_util_pkg.G_DBG_ROW_CNT);
mth_util_pkg.log_msg('Inserting Year level Complete',mth_util_pkg.G_DBG_OTH);
2. Insert quarter entries at the quarter level by joinning
MTH_445_PERIOD_CALENDAR that has the newly created year level information
with a SELECT subquery with four rows for each quarter in a year, then selecting year
level information from MTH_445_PERIOD_CALENDAR and constructing quarter
level information, and lastly inserting quarter entries into
MTH_445_PERIOD_CALENDAR.
*/
INSERT INTO MTH_445_PERIOD_CALENDAR (DIMENSION_KEY,
--,
FISCAL_YEAR_ID,
FISCAL_YEAR_NAME,
FIS_YEAR_NUMBER,
FISCAL_YEAR_START_DATE,
FISCAL_YEAR_TIME_SPAN,
FISCAL_YEAR_FIS_YEAR_CODE,
FISCAL_YEAR_DESCRIPTION,
FISCAL_YEAR_END_DATE,
FISCAL_QUARTER_ID,
FISCAL_QUARTER_NAME,
FIS_QUARTER_NUMBER,
FISCAL_QUARTER_START_DATE,
FISCAL_QUARTER_TIME_SPAN,
QUARTER_OF_FISCAL_YEAR,
FISCAL_QUARTER_FIS_QUARTER_CO,
FISCAL_QUARTER_END_DATE,
FISCAL_QUARTER_DESCRIPTION,
CREATION_DATE,
LAST_UPDATE_DATE)
SELECT MTH_445_PERIOD_CALENDAR_S.NEXTVAL * (-1) AS DIMENSION_KEY,
-- ,
FISCAL_YEAR_ID,
FISCAL_YEAR_NAME,
FIS_YEAR_NUMBER,
FISCAL_YEAR_START_DATE,
FISCAL_YEAR_TIME_SPAN,
FISCAL_YEAR_FIS_YEAR_CODE,
FISCAL_YEAR_DESCRIPTION,
FISCAL_YEAR_END_DATE,
-- Need to calculate the span for the last quarter
MTH_445_PERIOD_CALENDAR_S.CURRVAL * (-1)
AS FISCAL_QUARTER_ID,
'Q' || quarter || ' ' || FISCAL_YEAR_FIS_YEAR_CODE
AS FISCAL_QUARTER_NAME,
quarter as FIS_QUARTER_NUMBER,
CASE WHEN quarter = 1 THEN FISCAL_YEAR_START_DATE
ELSE FISCAL_YEAR_START_DATE + (quarter - 1) * 13 * 7
END
AS FISCAL_QUARTER_START_DATE,
CASE WHEN quarter = 4 THEN (FISCAL_YEAR_END_DATE - FISCAL_YEAR_START_DATE) + 1 - (13 * 7 * 3 ) ELSE 13 * 7 END
AS FISCAL_QUARTER_TIME_SPAN,
quarter as QUARTER_OF_FISCAL_YEAR,
TO_CHAR(FISCAL_YEAR_START_DATE, 'YYYY') || quarter || quarter
AS FISCAL_QUARTER_FIS_QUARTER_CO,
CASE WHEN quarter = 4 THEN FISCAL_YEAR_END_DATE
ELSE FISCAL_YEAR_START_DATE + (quarter * 13 * 7) -1 END
AS FISCAL_QUARTER_END_DATE,
'Fiscal Quarter ' || quarter || ' ' || FISCAL_YEAR_NAME
AS FISCAL_QUARTER_DESCRIPTION,
SYSDATE,
SYSDATE
FROM MTH_445_PERIOD_CALENDAR cal,
(select LEVEL AS quarter from dual connect by LEVEL <= 4) q
WHERE cal.FISCAL_QUARTER_ID IS NULL
AND cal.FIS_YEAR_NUMBER >= P_START_YEAR;
3. Insert month entries at the month level by joinning
MTH_445_PERIOD_CALENDAR that has the newly created quarter entries
with a SELECT subquery with three rows for each month in a quarter, then selecting
year and quarter level information from MTH_445_PERIOD_CALENDAR and
constructing month level information, and lastly inserting month entries into
MTH_445_PERIOD_CALENDAR.
*/
mth_util_pkg.log_msg('Inserted ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_ROW_CNT);
mth_util_pkg.log_msg('Inserting Quarter level Complete',mth_util_pkg.G_DBG_OTH);
INSERT INTO MTH_445_PERIOD_CALENDAR (DIMENSION_KEY,
--,
FISCAL_YEAR_ID,
FISCAL_YEAR_NAME,
FIS_YEAR_NUMBER,
FISCAL_YEAR_START_DATE,
FISCAL_YEAR_TIME_SPAN,
FISCAL_YEAR_FIS_YEAR_CODE,
FISCAL_YEAR_DESCRIPTION,
FISCAL_YEAR_END_DATE,
FISCAL_QUARTER_ID,
FISCAL_QUARTER_NAME,
FIS_QUARTER_NUMBER,
FISCAL_QUARTER_START_DATE,
FISCAL_QUARTER_TIME_SPAN,
QUARTER_OF_FISCAL_YEAR,
FISCAL_QUARTER_FIS_QUARTER_CO,
FISCAL_QUARTER_END_DATE,
FISCAL_QUARTER_DESCRIPTION,
--,
FISCAL_MONTH_START_DATE,
FISCAL_MONTH_FIS_MONTH_CODE,
FISCAL_MONTH_DESCRIPTION,
MONTH_OF_FISCAL_QUARTER,
FISCAL_MONTH_END_DATE,
FISCAL_MONTH_ID,
FISCAL_MONTH_NAME,
FIS_MONTH_NUMBER,
FISCAL_MONTH_TIME_SPAN,
MONTH_OF_FISCAL_YEAR,
CREATION_DATE,
LAST_UPDATE_DATE)
SELECT MTH_445_PERIOD_CALENDAR_S.NEXTVAL * (-1) AS DIMENSION_KEY,
--,
FISCAL_YEAR_ID,
FISCAL_YEAR_NAME,
FIS_YEAR_NUMBER,
FISCAL_YEAR_START_DATE,
FISCAL_YEAR_TIME_SPAN,
FISCAL_YEAR_FIS_YEAR_CODE,
FISCAL_YEAR_DESCRIPTION,
FISCAL_YEAR_END_DATE,
FISCAL_QUARTER_ID,
FISCAL_QUARTER_NAME,
FIS_QUARTER_NUMBER,
FISCAL_QUARTER_START_DATE,
FISCAL_QUARTER_TIME_SPAN,
QUARTER_OF_FISCAL_YEAR,
FISCAL_QUARTER_FIS_QUARTER_CO,
FISCAL_QUARTER_END_DATE,
FISCAL_QUARTER_DESCRIPTION,
--,
FISCAL_QUARTER_START_DATE +
CASE WHEN MONTH = 1 THEN 0
WHEN MONTH = 2 AND P_CAL_TYPE = 445 THEN (4 * 7)
WHEN MONTH = 2 AND P_CAL_TYPE = 544 THEN (5 * 7)
WHEN MONTH = 3 AND P_CAL_TYPE = 445 THEN (8 * 7)
WHEN MONTH = 3 AND P_CAL_TYPE = 544 THEN (9 * 7)
END
AS FISCAL_MONTH_START_DATE,
TO_NUMBER(FISCAL_YEAR_NAME ||
((FIS_QUARTER_NUMBER - 1) * 3 + MONTH) ||
((FIS_QUARTER_NUMBER - 1) * 3 + MONTH))
AS FISCAL_MONTH_FIS_MONTH_CODE,
'Fiscal Month ' || ((FIS_QUARTER_NUMBER - 1) * 3 + MONTH) || ' ' || FISCAL_YEAR_NAME
AS FISCAL_MONTH_DESCRIPTION,
MONTH AS MONTH_OF_FISCAL_QUARTER,
CASE WHEN MONTH = 3 THEN FISCAL_QUARTER_END_DATE
WHEN MONTH = 2 AND P_CAL_TYPE = 445
THEN FISCAL_QUARTER_START_DATE + ((8 * 7) - 1)
WHEN MONTH = 2 AND P_CAL_TYPE = 544
THEN FISCAL_QUARTER_START_DATE + ((9 * 7) - 1)
WHEN MONTH = 1 AND P_CAL_TYPE = 445
THEN FISCAL_QUARTER_START_DATE + ((4 * 7) - 1)
WHEN MONTH = 1 AND P_CAL_TYPE = 544
THEN FISCAL_QUARTER_START_DATE + ((5 * 7) - 1) END
AS FISCAL_MONTH_END_DATE,
MTH_445_PERIOD_CALENDAR_S.CURRVAL * (-1) AS FISCAL_MONTH_ID,
'Month ' || ( (FIS_QUARTER_NUMBER - 1) * 3 + MONTH) || ' ' || FISCAL_YEAR_NAME
AS FISCAL_MONTH_NAME,
((FIS_QUARTER_NUMBER - 1) * 3 + MONTH) AS FIS_MONTH_NUMBER,
CASE WHEN MONTH = 1 AND P_CAL_TYPE = 445 THEN 4 * 7
WHEN MONTH = 1 AND P_CAL_TYPE = 544 THEN 5 * 7
WHEN MONTH = 2 THEN 4 * 7
WHEN MONTH = 3 AND FIS_QUARTER_NUMBER < 4 AND P_CAL_TYPE = 544
THEN 4 * 7
WHEN MONTH = 3 AND FIS_QUARTER_NUMBER < 4 AND P_CAL_TYPE = 445
THEN 5 * 7
WHEN MONTH = 3 AND P_CAL_TYPE = 544
THEN 4 * 7 + (((FISCAL_YEAR_END_DATE - FISCAL_YEAR_START_DATE + 1) / 7) - 52) * 7
WHEN MONTH = 3 AND P_CAL_TYPE = 445
THEN 5 * 7 + (((FISCAL_YEAR_END_DATE - FISCAL_YEAR_START_DATE + 1) / 7) - 52) * 7
END
AS FISCAL_MONTH_TIME_SPAN,
((FIS_QUARTER_NUMBER - 1) * 3 + MONTH) AS MONTH_OF_FISCAL_YEAR,
SYSDATE AS CREATION_DATE,
SYSDATE AS LAST_UPDATE_DATE
FROM MTH_445_PERIOD_CALENDAR cal,
(select LEVEL AS MONTH from dual connect by LEVEL <= 3) MONTHS
WHERE cal.FISCAL_QUARTER_ID IS NOT NULL
AND cal.FISCAL_MONTH_ID IS NULL
AND cal.FIS_YEAR_NUMBER >= P_START_YEAR;
mth_util_pkg.log_msg('Inserted ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_ROW_CNT);
mth_util_pkg.log_msg('Inserting Month level Complete',mth_util_pkg.G_DBG_OTH);
/* 4. Insert day entries at the Week level by joinning
MTH_445_PERIOD_CALENDAR that has the newly created month entries
with a SELECT subquery with maximal 6 rows for each week in a month, then
selecting year, quarter and month level information from
MTH_445_PERIOD_CALENDAR
and constructing week level information, and lastly inserting week entries into
MTH_445_PERIOD_CALENDAR.
*/
INSERT INTO MTH_445_PERIOD_CALENDAR (DIMENSION_KEY,
FISCAL_YEAR_ID,
FISCAL_YEAR_NAME,
FIS_YEAR_NUMBER,
FISCAL_YEAR_START_DATE,
FISCAL_YEAR_TIME_SPAN,
FISCAL_YEAR_FIS_YEAR_CODE,
FISCAL_YEAR_DESCRIPTION,
FISCAL_YEAR_END_DATE,
--,
FISCAL_QUARTER_ID,
FISCAL_QUARTER_NAME,
FIS_QUARTER_NUMBER,
FISCAL_QUARTER_START_DATE,
FISCAL_QUARTER_TIME_SPAN,
QUARTER_OF_FISCAL_YEAR,
FISCAL_QUARTER_FIS_QUARTER_CO,
FISCAL_QUARTER_END_DATE,
FISCAL_QUARTER_DESCRIPTION,
--,
FISCAL_MONTH_START_DATE,
FISCAL_MONTH_FIS_MONTH_CODE,
FISCAL_MONTH_DESCRIPTION,
MONTH_OF_FISCAL_QUARTER,
FISCAL_MONTH_END_DATE,
FISCAL_MONTH_ID,
FISCAL_MONTH_NAME,
FIS_MONTH_NUMBER,
FISCAL_MONTH_TIME_SPAN,
MONTH_OF_FISCAL_YEAR,
--,
FISCAL_WEEK_START_DATE,
FISCAL_WEEK_FIS_WEEK_CODE,
FISCAL_WEEK_DESCRIPTION,
WEEK_OF_FISCAL_MONTH,
FISCAL_WEEK_TIME_SPAN,
WEEK_OF_FISCAL_YEAR,
FIS_WEEK_NUMBER,
FISCAL_WEEK_END_DATE,
FISCAL_WEEK_ID,
FISCAL_WEEK_NAME,
WEEK_OF_FISCAL_QUARTER,
CREATION_DATE,
LAST_UPDATE_DATE)
SELECT MTH_445_PERIOD_CALENDAR_S.NEXTVAL * (-1) AS DIMENSION_KEY,
FISCAL_YEAR_ID,
FISCAL_YEAR_NAME,
FIS_YEAR_NUMBER,
FISCAL_YEAR_START_DATE,
FISCAL_YEAR_TIME_SPAN,
FISCAL_YEAR_FIS_YEAR_CODE,
FISCAL_YEAR_DESCRIPTION,
FISCAL_YEAR_END_DATE,
--,
FISCAL_QUARTER_ID,
FISCAL_QUARTER_NAME,
FIS_QUARTER_NUMBER,
FISCAL_QUARTER_START_DATE,
FISCAL_QUARTER_TIME_SPAN,
QUARTER_OF_FISCAL_YEAR,
FISCAL_QUARTER_FIS_QUARTER_CO,
FISCAL_QUARTER_END_DATE,
FISCAL_QUARTER_DESCRIPTION,
--,
FISCAL_MONTH_START_DATE,
FISCAL_MONTH_FIS_MONTH_CODE,
FISCAL_MONTH_DESCRIPTION,
MONTH_OF_FISCAL_QUARTER,
FISCAL_MONTH_END_DATE,
FISCAL_MONTH_ID,
FISCAL_MONTH_NAME,
FIS_MONTH_NUMBER,
FISCAL_MONTH_TIME_SPAN,
MONTH_OF_FISCAL_YEAR,
--,
FISCAL_MONTH_START_DATE + (WEEKS.WEEK - 1) * 7
AS FISCAL_WEEK_START_DATE,
TO_NUMBER(FISCAL_YEAR_NAME || (ROUND((FISCAL_MONTH_START_DATE - FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK) ||
(ROUND((FISCAL_MONTH_START_DATE - FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK))
AS FISCAL_WEEK_FIS_WEEK_CODE,
'Fiscal Week ' || (ROUND((FISCAL_MONTH_START_DATE -
FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK) || ' ' ||
FISCAL_YEAR_NAME AS FISCAL_WEEK_DESCRIPTION,
WEEKS.WEEK
AS WEEK_OF_FISCAL_MONTH,
7 AS FISCAL_WEEK_TIME_SPAN,
(ROUND((FISCAL_MONTH_START_DATE -
FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK)
AS WEEK_OF_FISCAL_YEAR,
(ROUND((FISCAL_MONTH_START_DATE -
FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK)
AS FIS_WEEK_NUMBER,
(FISCAL_MONTH_START_DATE + WEEKS.WEEK * 7) - 1
AS FISCAL_WEEK_END_DATE,
MTH_445_PERIOD_CALENDAR_S.CURRVAL * (-1)
AS FISCAL_WEEK_ID,
'Wk ' || (ROUND((FISCAL_MONTH_START_DATE -
FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK) || ' ' || FISCAL_YEAR_NAME
AS FISCAL_WEEK_NAME,
ROUND((FISCAL_MONTH_START_DATE - FISCAL_QUARTER_START_DATE) / 7) + WEEKS.WEEK
AS WEEK_OF_FISCAL_QUARTER,
SYSDATE AS CREATION_DATE,
SYSDATE AS LAST_UPDATE_DATE
FROM MTH_445_PERIOD_CALENDAR cal,
(select LEVEL AS WEEK from dual connect by LEVEL <= 6) WEEKS
--at most 6 weeks in one month
WHERE cal.FISCAL_MONTH_ID IS NOT NULL AND
cal.FISCAL_WEEK_ID IS NULL AND
cal.FIS_YEAR_NUMBER >= P_START_YEAR AND
((cal.FISCAL_MONTH_START_DATE + WEEKS.WEEK * 7) - 1) <= cal.FISCAL_MONTH_END_DATE;
mth_util_pkg.log_msg('Inserted ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_ROW_CNT);
mth_util_pkg.log_msg('Inserting Week level Complete',mth_util_pkg.G_DBG_OTH);
5. Insert day entries at the Day level by joinning
MTH_445_PERIOD_CALENDAR that has the newly created week level information
with a SELECT subquery with 7 rows for each day in a week, then selecting
year, quarter, month, and week level information from MTH_445_PERIOD_CALENDAR
and constructing day level information, and lastly inserting day entries into
MTH_445_PERIOD_CALENDAR.
*/
INSERT INTO MTH_445_PERIOD_CALENDAR (DIMENSION_KEY,
FISCAL_YEAR_ID,
FISCAL_YEAR_NAME,
FIS_YEAR_NUMBER,
FISCAL_YEAR_START_DATE,
FISCAL_YEAR_TIME_SPAN,
FISCAL_YEAR_FIS_YEAR_CODE,
FISCAL_YEAR_DESCRIPTION,
FISCAL_YEAR_END_DATE,
--,
FISCAL_QUARTER_ID,
FISCAL_QUARTER_NAME,
FIS_QUARTER_NUMBER,
FISCAL_QUARTER_START_DATE,
FISCAL_QUARTER_TIME_SPAN,
QUARTER_OF_FISCAL_YEAR,
FISCAL_QUARTER_FIS_QUARTER_CO,
FISCAL_QUARTER_END_DATE,
FISCAL_QUARTER_DESCRIPTION,
--,
FISCAL_MONTH_START_DATE,
FISCAL_MONTH_FIS_MONTH_CODE,
FISCAL_MONTH_DESCRIPTION,
MONTH_OF_FISCAL_QUARTER,
FISCAL_MONTH_END_DATE,
FISCAL_MONTH_ID,
FISCAL_MONTH_NAME,
FIS_MONTH_NUMBER,
FISCAL_MONTH_TIME_SPAN,
MONTH_OF_FISCAL_YEAR,
--,
FISCAL_WEEK_START_DATE,
FISCAL_WEEK_FIS_WEEK_CODE,
FISCAL_WEEK_DESCRIPTION,
WEEK_OF_FISCAL_MONTH,
FISCAL_WEEK_TIME_SPAN,
WEEK_OF_FISCAL_YEAR,
FIS_WEEK_NUMBER,
FISCAL_WEEK_END_DATE,
FISCAL_WEEK_ID,
FISCAL_WEEK_NAME,
WEEK_OF_FISCAL_QUARTER,
DAY_START_DATE,
DAY_OF_FISCAL_YEAR,
JULIAN_DATE,
DAY_OF_FISCAL_WEEK,
DAY_TIME_SPAN,
DAY,
DAY_ID,
DAY_DAY_CODE,
DAY_DESCRIPTION,
DAY_NAME,
DAY_END_DATE,
DAY_OF_FISCAL_QUARTER,
DAY_OF_FISCAL_MONTH,
CREATION_DATE,
LAST_UPDATE_DATE)
SELECT MTH_445_PERIOD_CALENDAR_S.NEXTVAL AS DIMENSION_KEY,
FISCAL_YEAR_ID,
FISCAL_YEAR_NAME,
FIS_YEAR_NUMBER,
FISCAL_YEAR_START_DATE,
FISCAL_YEAR_TIME_SPAN,
FISCAL_YEAR_FIS_YEAR_CODE,
FISCAL_YEAR_DESCRIPTION,
FISCAL_YEAR_END_DATE,
--,
FISCAL_QUARTER_ID,
FISCAL_QUARTER_NAME,
FIS_QUARTER_NUMBER,
FISCAL_QUARTER_START_DATE,
FISCAL_QUARTER_TIME_SPAN,
QUARTER_OF_FISCAL_YEAR,
FISCAL_QUARTER_FIS_QUARTER_CO,
FISCAL_QUARTER_END_DATE,
FISCAL_QUARTER_DESCRIPTION,
--,
FISCAL_MONTH_START_DATE,
FISCAL_MONTH_FIS_MONTH_CODE,
FISCAL_MONTH_DESCRIPTION,
MONTH_OF_FISCAL_QUARTER,
FISCAL_MONTH_END_DATE,
FISCAL_MONTH_ID,
FISCAL_MONTH_NAME,
FIS_MONTH_NUMBER,
FISCAL_MONTH_TIME_SPAN,
MONTH_OF_FISCAL_YEAR,
--,
FISCAL_WEEK_START_DATE,
FISCAL_WEEK_FIS_WEEK_CODE,
FISCAL_WEEK_DESCRIPTION,
WEEK_OF_FISCAL_MONTH,
FISCAL_WEEK_TIME_SPAN,
WEEK_OF_FISCAL_YEAR,
FIS_WEEK_NUMBER,
FISCAL_WEEK_END_DATE,
FISCAL_WEEK_ID,
FISCAL_WEEK_NAME,
WEEK_OF_FISCAL_QUARTER,
FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1 AS DAY_START_DATE,
FISCAL_WEEK_START_DATE + DAY_IN_WEEK - FISCAL_YEAR_START_DATE
AS DAY_OF_FISCAL_YEAR,
TO_NUMBER(To_Char((FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1),'J')) AS JULIAN_DATE,
DAY_IN_WEEK AS DAY_OF_FISCAL_WEEK,
1 AS DAY_TIME_SPAN,
(FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1) AS DAY,
MTH_445_PERIOD_CALENDAR_S.CURRVAL AS DAY_ID,
TO_NUMBER (To_Char(FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1, 'YYYYMMDD')) AS DAY_DAY_CODE,
To_Char(FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1, 'DD-MON-YYYY') AS DAY_DESCRIPTION,
To_Char(FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1, 'DD-MON-YYYY') AS DAY_NAME,
FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1 AS DAY_END_DATE,
FISCAL_WEEK_START_DATE + DAY_IN_WEEK - FISCAL_QUARTER_START_DATE
AS DAY_OF_FISCAL_QUARTER,
FISCAL_WEEK_START_DATE + DAY_IN_WEEK - FISCAL_MONTH_START_DATE
AS DAY_OF_FISCAL_MONTH,
SYSDATE AS CREATION_DATE,
SYSDATE AS LAST_UPDATE_DATE
FROM MTH_445_PERIOD_CALENDAR cal,
(select LEVEL AS DAY_IN_WEEK from dual connect by LEVEL <= 7) days
WHERE cal.DAY_ID IS NULL
AND cal.FISCAL_WEEK_ID IS NOT NULL
AND cal.FIS_YEAR_NUMBER >= P_START_YEAR;
mth_util_pkg.log_msg('Inserted ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_ROW_CNT);
mth_util_pkg.log_msg('Inserting Day level Complete',mth_util_pkg.G_DBG_OTH);
mth_util_pkg.log_msg('Inserting Hour level Complete', mth_util_pkg.G_DBG_OTH);
select count(*)
into v_year_err
from MTH_YEAR_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
select count(*)
into v_quarter_err
from MTH_QUARTER_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
select count(*)
into v_period_err
from MTH_PERIOD_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
select count(*)
into v_week_err
from MTH_WEEK_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
select count(*)
into v_day_err
from MTH_DAY_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
select count(*)
into v_time_hrcy_err
from MTH_TIME_HIERARCHY_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
/*SELECT
HIERARCHY_NAME,CHILD_FK,PARENT_FK,LEVEL_NUMBER_OF_CHILD,
SYSTEM_FK,ERR$$$_AUDIT_RUN_ID,ERR_CODE
BULK COLLECT INTO
c_mth_hryerr_HIERARCHY_NAME,
c_mth_hryerr_CHILD_FK,
c_mth_hryerr_PARENT_FK,
c_mth_hryerr_LEVEL_OF_CHILD,
c_mth_hryerr_SYSTEM_FK,
c_mth_hryerr_RUN_ID,
c_mth_hryerr_ERR_CODE
FROM MTH_TIME_HIERARCHY_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;*/
SELECT LEVEL_FK_KEY
FROM(
SELECT LEVEL_FK_KEY,Count(PARENT_FK_KEY) AS MULTIPLE_PARENTS
FROM MTH_TIME_HIERARCHY
GROUP BY LEVEL_FK_KEY)
WHERE MULTIPLE <> 1);*/
SELECT REPORT_DATE, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, ERR_CODE,RUN_ID
BULK COLLECT INTO
c_mth_dayerr_REPORT_DATE,
c_mth_dayerr_USER_ATTR1,
c_mth_dayerr_USER_ATTR2,
c_mth_dayerr_USER_ATTR3,
c_mth_dayerr_USER_ATTR4,
c_mth_dayerr_USER_ATTR5,
c_mth_dayerr_USER_MEASURE1,
c_mth_dayerr_USER_MEASURE2,
c_mth_dayerr_USER_MEASURE3,
c_mth_dayerr_USER_MEASURE4,
c_mth_dayerr_USER_MEASURE5,
c_mth_dayerr_SYSTEM_FK,
c_mth_dayerr_ERR_CODE,
c_mth_dayerr_RUN_ID
FROM
(SELECT REPORT_DATE, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK,
CASE WHEN PREV_REPORT_DATE >= REPORT_DATE THEN
'OVP'
ELSE
'GAP'
END ERR_CODE,
e.RUN_ID
FROM
(SELECT Lag(REPORT_DATE)
over (ORDER BY REPORT_DATE) PREV_REPORT_DATE,
s.*,
mss.SYSTEM_PK as SYSTEM_FK,
P_SESSION_NO as RUN_ID
FROM mth_day_d s,
mth_systems_setup mss
WHERE s.system_fk_key = mss.system_pk_key)e
WHERE PREV_REPORT_DATE + 1 <> REPORT_DATE);
SELECT REPORT_DATE, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, ERR_CODE, P_SESSION_NO as RUN_ID
FROM MTH_DAY_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;*/
SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,RUN_ID
BULK COLLECT INTO
c_mth_weekerr_HIERARCHY_NAME,
c_mth_weekerr_NAME,
c_mth_weekerr_USER_ATTR1,
c_mth_weekerr_USER_ATTR2,
c_mth_weekerr_USER_ATTR3,
c_mth_weekerr_USER_ATTR4,
c_mth_weekerr_USER_ATTR5,
c_mth_weekerr_USER_MEASURE1,
c_mth_weekerr_USER_MEASURE2,
c_mth_weekerr_USER_MEASURE3,
c_mth_weekerr_USER_MEASURE4,
c_mth_weekerr_USER_MEASURE5,
c_mth_weekerr_SYSTEM_FK,
c_mth_weekerr_END_DATE,
c_mth_weekerr_START_DATE,
c_mth_weekerr_ERR_CODE,
c_mth_weekerr_RUN_ID
FROM
(SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE,
CASE WHEN PREV_END_DATE >= START_DATE THEN
'OVP'
ELSE 'GAP'
END ERR_CODE,
e.RUN_ID
FROM (SELECT Lag(START_DATE) over (PARTITION BY
mdh.HIERARCHY_NAME ORDER BY START_DATE) PREV_START_DATE,
Lag(END_DATE) over (PARTITION BY mdh.HIERARCHY_NAME
ORDER BY START_DATE) PREV_END_DATE,
s.*,
mdh.HIERARCHY_NAME,
mss.SYSTEM_PK as SYSTEM_FK,
P_SESSION_NO as RUN_ID
FROM mth_week_d s,
mth_dim_hierarchy mdh,
mth_systems_setup mss
WHERE mdh.hierarchy_id = s.hierarchy_id
and mdh.dimension_name = 'TIME'
and s.system_fk_key = mss.system_pk_key)e
WHERE START_DATE <> PREV_END_DATE+1);
SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,
P_SESSION_NO as RUN_ID
from MTH_WEEK_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO);*/
SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,RUN_ID
BULK COLLECT INTO
c_mth_pererr_HIERARCHY_NAME,
c_mth_pererr_NAME,
c_mth_pererr_USER_ATTR1,
c_mth_pererr_USER_ATTR2,
c_mth_pererr_USER_ATTR3,
c_mth_pererr_USER_ATTR4,
c_mth_pererr_USER_ATTR5,
c_mth_pererr_USER_MEASURE1,
c_mth_pererr_USER_MEASURE2,
c_mth_pererr_USER_MEASURE3,
c_mth_pererr_USER_MEASURE4,
c_mth_pererr_USER_MEASURE5,
c_mth_pererr_SYSTEM_FK,
c_mth_pererr_END_DATE,
c_mth_pererr_START_DATE,
c_mth_pererr_ERR_CODE,
c_mth_pererr_RUN_ID
FROM
(SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE,
CASE WHEN PREV_END_DATE >= START_DATE THEN
'OVP'
ELSE 'GAP'
END ERR_CODE,
e.RUN_ID
FROM (SELECT Lag(START_DATE) over (PARTITION BY
mdh.HIERARCHY_NAME ORDER BY START_DATE) PREV_START_DATE,
Lag(END_DATE) over (PARTITION BY mdh.HIERARCHY_NAME
ORDER BY START_DATE) PREV_END_DATE,
s.*,
mdh.HIERARCHY_NAME,
mss.SYSTEM_PK as SYSTEM_FK,
P_SESSION_NO as RUN_ID
FROM mth_period_d s,
mth_dim_hierarchy mdh,
mth_systems_setup mss
WHERE mdh.hierarchy_id = s.hierarchy_id
and mdh.dimension_name = 'TIME'
and s.system_fk_key = mss.system_pk_key)e
WHERE START_DATE <> PREV_END_DATE+1);
SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,
P_SESSION_NO as RUN_ID
from MTH_PERIOD_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO);*/
SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,RUN_ID
BULK COLLECT INTO
c_mth_qerr_HIERARCHY_NAME,
c_mth_qerr_NAME,
c_mth_qerr_USER_ATTR1,
c_mth_qerr_USER_ATTR2,
c_mth_qerr_USER_ATTR3,
c_mth_qerr_USER_ATTR4,
c_mth_qerr_USER_ATTR5,
c_mth_qerr_USER_MEASURE1,
c_mth_qerr_USER_MEASURE2,
c_mth_qerr_USER_MEASURE3,
c_mth_qerr_USER_MEASURE4,
c_mth_qerr_USER_MEASURE5,
c_mth_qerr_SYSTEM_FK,
c_mth_qerr_END_DATE,
c_mth_qerr_START_DATE,
c_mth_qerr_ERR_CODE,
c_mth_qerr_RUN_ID
FROM
(SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE,
CASE WHEN PREV_END_DATE >= START_DATE THEN
'OVP'
ELSE 'GAP'
END ERR_CODE,
e.RUN_ID
FROM (SELECT Lag(START_DATE) over (PARTITION BY
mdh.HIERARCHY_NAME ORDER BY START_DATE) PREV_START_DATE,
Lag(END_DATE) over (PARTITION BY mdh.HIERARCHY_NAME
ORDER BY START_DATE) PREV_END_DATE,
s.*,
mdh.HIERARCHY_NAME,
mss.SYSTEM_PK as SYSTEM_FK,
P_SESSION_NO as RUN_ID
FROM mth_quarter_d s,
mth_dim_hierarchy mdh,
mth_systems_setup mss
WHERE mdh.hierarchy_id = s.hierarchy_id
and mdh.dimension_name = 'TIME'
and s.system_fk_key = mss.system_pk_key)e
WHERE START_DATE <> PREV_END_DATE+1);
SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,
P_SESSION_NO as RUN_ID
from MTH_QUARTER_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO);*/
SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,RUN_ID
BULK COLLECT INTO
c_mth_yearerr_HIERARCHY_NAME,
c_mth_yearerr_NAME,
c_mth_yearerr_USER_ATTR1,
c_mth_yearerr_USER_ATTR2,
c_mth_yearerr_USER_ATTR3,
c_mth_yearerr_USER_ATTR4,
c_mth_yearerr_USER_ATTR5,
c_mth_yearerr_USER_MEASURE1,
c_mth_yearerr_USER_MEASURE2,
c_mth_yearerr_USER_MEASURE3,
c_mth_yearerr_USER_MEASURE4,
c_mth_yearerr_USER_MEASURE5,
c_mth_yearerr_SYSTEM_FK,
c_mth_yearerr_END_DATE,
c_mth_yearerr_START_DATE,
c_mth_yearerr_ERR_CODE,
c_mth_yearerr_RUN_ID
FROM
(SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE,
CASE WHEN PREV_END_DATE >= START_DATE THEN
'OVP'
ELSE 'GAP'
END ERR_CODE,
e.RUN_ID
FROM (SELECT Lag(START_DATE) over (PARTITION BY
mdh.HIERARCHY_NAME ORDER BY START_DATE) PREV_START_DATE,
Lag(END_DATE) over (PARTITION BY mdh.HIERARCHY_NAME
ORDER BY START_DATE) PREV_END_DATE,
s.*,
mdh.HIERARCHY_NAME,
mss.SYSTEM_PK as SYSTEM_FK,
P_SESSION_NO as RUN_ID
FROM mth_year_d s,
mth_dim_hierarchy mdh,
mth_systems_setup mss
WHERE mdh.hierarchy_id = s.hierarchy_id
and mdh.dimension_name = 'TIME'
and s.system_fk_key = mss.system_pk_key)e
WHERE START_DATE <> PREV_END_DATE+1);
SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,
P_SESSION_NO as RUN_ID
from MTH_YEAR_ERR
WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO);*/
select count(*)
into v_year_err
from MTH_YEAR_D;
select count(*)
into v_quarter_err
from MTH_QUARTER_D;
select count(*)
into v_period_err
from MTH_PERIOD_D;
select count(*)
into v_week_err
from MTH_WEEK_D;
select count(*)
into v_day_err
from MTH_DAY_D;
select count(*)
into v_time_hrcy_err
from MTH_TIME_HIERARCHY;
INSERT INTO MTH_DAY_ERR(
REPORT_DATE, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, ERR_CODE,ERR$$$_AUDIT_RUN_ID)
VALUES
(c_mth_dayerr_REPORT_DATE(i),
c_mth_dayerr_USER_ATTR1(i),
c_mth_dayerr_USER_ATTR2(i),
c_mth_dayerr_USER_ATTR3(i),
c_mth_dayerr_USER_ATTR4(i),
c_mth_dayerr_USER_ATTR5(i),
c_mth_dayerr_USER_MEASURE1(i),
c_mth_dayerr_USER_MEASURE2(i),
c_mth_dayerr_USER_MEASURE3(i),
c_mth_dayerr_USER_MEASURE4(i),
c_mth_dayerr_USER_MEASURE5(i),
c_mth_dayerr_SYSTEM_FK(i),
c_mth_dayerr_ERR_CODE(i),
c_mth_dayerr_RUN_ID(i));
INSERT INTO MTH_WEEK_ERR(
HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,ERR$$$_AUDIT_RUN_ID)
VALUES
(c_mth_weekerr_HIERARCHY_NAME(i),
c_mth_weekerr_NAME(i),
c_mth_weekerr_USER_ATTR1(i),
c_mth_weekerr_USER_ATTR2(i),
c_mth_weekerr_USER_ATTR3(i),
c_mth_weekerr_USER_ATTR4(i),
c_mth_weekerr_USER_ATTR5(i),
c_mth_weekerr_USER_MEASURE1(i),
c_mth_weekerr_USER_MEASURE2(i),
c_mth_weekerr_USER_MEASURE3(i),
c_mth_weekerr_USER_MEASURE4(i),
c_mth_weekerr_USER_MEASURE5(i),
c_mth_weekerr_SYSTEM_FK(i),
c_mth_weekerr_END_DATE(i),
c_mth_weekerr_START_DATE(i),
c_mth_weekerr_ERR_CODE(i),
c_mth_weekerr_RUN_ID(i));
INSERT INTO MTH_PERIOD_ERR(
HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,ERR$$$_AUDIT_RUN_ID)
VALUES
(c_mth_pererr_HIERARCHY_NAME(i),
c_mth_pererr_NAME(i),
c_mth_pererr_USER_ATTR1(i),
c_mth_pererr_USER_ATTR2(i),
c_mth_pererr_USER_ATTR3(i),
c_mth_pererr_USER_ATTR4(i),
c_mth_pererr_USER_ATTR5(i),
c_mth_pererr_USER_MEASURE1(i),
c_mth_pererr_USER_MEASURE2(i),
c_mth_pererr_USER_MEASURE3(i),
c_mth_pererr_USER_MEASURE4(i),
c_mth_pererr_USER_MEASURE5(i),
c_mth_pererr_SYSTEM_FK(i),
c_mth_pererr_END_DATE(i),
c_mth_pererr_START_DATE(i),
c_mth_pererr_ERR_CODE(i),
c_mth_pererr_RUN_ID(i));
INSERT INTO MTH_QUARTER_ERR(
HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,ERR$$$_AUDIT_RUN_ID)
VALUES
(c_mth_qerr_HIERARCHY_NAME(i),
c_mth_qerr_NAME(i),
c_mth_qerr_USER_ATTR1(i),
c_mth_qerr_USER_ATTR2(i),
c_mth_qerr_USER_ATTR3(i),
c_mth_qerr_USER_ATTR4(i),
c_mth_qerr_USER_ATTR5(i),
c_mth_qerr_USER_MEASURE1(i),
c_mth_qerr_USER_MEASURE2(i),
c_mth_qerr_USER_MEASURE3(i),
c_mth_qerr_USER_MEASURE4(i),
c_mth_qerr_USER_MEASURE5(i),
c_mth_qerr_SYSTEM_FK(i),
c_mth_qerr_END_DATE(i),
c_mth_qerr_START_DATE(i),
c_mth_qerr_ERR_CODE(i),
c_mth_qerr_RUN_ID(i));
INSERT INTO MTH_YEAR_ERR(
HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,ERR$$$_AUDIT_RUN_ID)
VALUES
(c_mth_yearerr_HIERARCHY_NAME(i),
c_mth_yearerr_NAME(i),
c_mth_yearerr_USER_ATTR1(i),
c_mth_yearerr_USER_ATTR2(i),
c_mth_yearerr_USER_ATTR3(i),
c_mth_yearerr_USER_ATTR4(i),
c_mth_yearerr_USER_ATTR5(i),
c_mth_yearerr_USER_MEASURE1(i),
c_mth_yearerr_USER_MEASURE2(i),
c_mth_yearerr_USER_MEASURE3(i),
c_mth_yearerr_USER_MEASURE4(i),
c_mth_yearerr_USER_MEASURE5(i),
c_mth_yearerr_SYSTEM_FK(i),
c_mth_yearerr_END_DATE(i),
c_mth_yearerr_START_DATE(i),
c_mth_yearerr_ERR_CODE(i),
c_mth_yearerr_RUN_ID(i));
INSERT INTO MTH_TIME_HIERARCHY_ERR(
HIERARCHY_NAME,CHILD_FK,PARENT_FK,LEVEL_NUMBER_OF_CHILD,
SYSTEM_FK,ERR_CODE,ERR$$$_AUDIT_RUN_ID)
VALUES
(c_mth_hryerr_HIERARCHY_NAME(i),
c_mth_hryerr_CHILD_FK(i),
c_mth_hryerr_PARENT_FK(i),
c_mth_hryerr_LEVEL_OF_CHILD(i),
c_mth_hryerr_SYSTEM_FK(i),
c_mth_hryerr_ERR_CODE(i),
c_mth_hryerr_RUN_ID(i));
DELETE FROM MTH_DAY_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_DAY_STG');
DELETE FROM MTH_WEEK_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_WEEK_STG');
DELETE FROM MTH_PERIOD_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_PERIOD_STG');
DELETE FROM MTH_QUARTER_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_QUARTER_STG');
DELETE FROM MTH_YEAR_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_YEAR_STG');
DELETE FROM MTH_TIME_HIERARCHY_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_TIME_HIERARCHY_STG');
DELETE FROM MTH_DAY_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_DAY_STG');
DELETE FROM MTH_WEEK_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_WEEK_STG');
DELETE FROM MTH_PERIOD_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_PERIOD_STG');
DELETE FROM MTH_QUARTER_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_QUARTER_STG');
DELETE FROM MTH_YEAR_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_YEAR_STG');
DELETE FROM MTH_TIME_HIERARCHY_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_TIME_HIERARCHY_STG');