[Home] [Help]
PACKAGE BODY: APPS.MTH_CALENDAR_PKG
Source
1 PACKAGE BODY MTH_CALENDAR_PKG AS
2 /*$Header: mthcalrb.pls 120.10.12020000.3 2012/10/07 15:10:03 sasuren noship $*/
3
4 /* ****************************************************************************
5 * Procedure :GENERATE_GREGORIAN_CALENDAR *
6 * Description :... *
7 * File Name :MTHCALRB.PLS *
8 * Visibility :Public *
9 * Parameters :P_START_YEAR - Year from which calendar has to be *
10 * generated *
11 * P_NUM_YEARS - Number of years for generation *
12 ******************************************************************************/
13
14 PROCEDURE GENERATE_GREGORIAN_CALENDAR (p_err_buff OUT NOCOPY VARCHAR2,
15 p_retcode OUT NOCOPY NUMBER,
16 p_start_year IN NUMBER,
17 p_num_years IN NUMBER)
18 IS
19 v_error_string VARCHAR2(255);
20 l_execution_id NUMBER;
21 BEGIN
22 mth_util_pkg.initialize_debug('Concurrent Program "MTH: Populate MOC Gregorian Calendar"', l_execution_id);
23 mth_util_pkg.log_msg('GENERATE_GREGORIAN_CALENDAR start', mth_util_pkg.G_DBG_PROC_FUN_START);
24 mth_util_pkg.log_msg('p_start_year = ' || p_start_year , mth_util_pkg.G_DBG_MAIN_PARAM);
25 mth_util_pkg.log_msg('p_num_years = ' || p_num_years , mth_util_pkg.G_DBG_MAIN_PARAM);
26
27 -- 1. Validate input parameters
28 v_error_string := '';
29 v_error_string := VALIDATE_GREG_CAL_INPUT_PARAMS(p_start_year, p_num_years);
30 IF (v_error_string is not NULL) THEN
31 mth_util_pkg.log_msg(v_error_string, mth_util_pkg.G_DBG_USER_INFO);
32 p_retcode := 1;
33 ELSE
34 -- 2. Populate Gregorian calendar into MTH_GREGORIAN_CALENDAR denorm table.
35 POPULATE_GREGORIAN_CAL_DENORM(p_start_year, p_num_years);
36 POPULATE_HOURS;
37 COMMIT;
38 END IF;
39
40 mth_util_pkg.log_msg('GENERATE_GREGORIAN_CALENDAR end', mth_util_pkg.G_DBG_PROC_FUN_END);
41
42 EXCEPTION
43 WHEN OTHERS THEN
44 --Call logging API and then throw exception
45 mth_util_pkg.log_msg('Exception OTHERS in GENERATE_GREGORIAN_CALENDAR', mth_util_pkg.G_DBG_EXCEPTION);
46 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
47 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
48 END;
49
50 /* ****************************************************************************
51 * Function :VALIDATE_GREG_CAL_INPUT_PARAMS *
52 * Description :... *
53 * File Name :MTHCALRB.PLS *
54 * Visibility :Public *
55 * Input Parameters :P_START_YEAR - Year from which calendar has to be *
56 * generated *
57 * P_NUM_YEARS - Number of years for generation *
58 ******************************************************************************/
59
60 FUNCTION VALIDATE_GREG_CAL_INPUT_PARAMS(P_START_YEAR IN NUMBER,
61 P_NUM_YEARS IN NUMBER) RETURN VARCHAR2
62 IS
63 v_err_code VARCHAR2(255);
64 v_latest_year NUMBER;
65 BEGIN
66 mth_util_pkg.log_msg('VALIDATE_GREG_CAL_INPUT_PARAMS start', mth_util_pkg.G_DBG_PROC_FUN_START);
67 v_err_code := '';
68
69 If (P_NUM_YEARS < 1) THEN
70 v_err_code := fnd_message.get_string('MTH','MTH_GREG_NUM_YEARS');
71 END IF;
72
73 SELECT Max(CAL_YEAR_NUMBER)
74 into v_latest_year
75 FROM MTH_GREGORIAN_CALENDAR
76 WHERE calendar_quarter_id IS NULL;
77
78 IF (v_latest_year is not null and v_latest_year <> P_START_YEAR - 1) THEN
79 FND_MESSAGE.SET_NAME('MTH','MTH_GREG_START_YEAR');
80 FND_MESSAGE.SET_TOKEN('YEAR',v_latest_year+1);
81 v_err_code := v_err_code || FND_MESSAGE.GET('MTH_GREG_START_YEAR');
82 END IF;
83
84 IF (v_latest_year is null AND P_START_YEAR < 1900) THEN
85 v_err_code := v_err_code || fnd_message.get_string('MTH','MTH_GREG_LATER_YEAR');
86 END IF;
87
88 RETURN v_err_code;
89 mth_util_pkg.log_msg('VALIDATE_GREG_CAL_INPUT_PARAMS end', mth_util_pkg.G_DBG_PROC_FUN_END);
90 END;
91
92 /* ****************************************************************************
93 * Procedure :POPULATE_GREGORIAN_CAL_DENORM *
94 * Description :... *
95 * File Name :MTHCALRB.PLS *
96 * Visibility :Public *
97 * Parameters :P_START_YEAR - Year from which calendar has to be *
98 * generated *
99 * P_NUM_YEARS - Number of years for generation *
100 ******************************************************************************/
101
102 PROCEDURE POPULATE_GREGORIAN_CAL_DENORM (P_START_YEAR IN NUMBER,
103 P_NUM_YEARS IN NUMBER)
104 IS
105
106 V_year_start DATE;
107 V_year_end DATE;
108
109 BEGIN
110
111 mth_util_pkg.log_msg('VALIDATE_GREG_CAL_INPUT_PARAMS start', mth_util_pkg.G_DBG_PROC_FUN_START);
112 -- 1. Insert year level first
113 mth_util_pkg.log_msg('Inserting Year level', mth_util_pkg.G_DBG_OTH);
114
115 FOR V_YEAR IN P_START_YEAR .. (P_START_YEAR + P_NUM_YEARS - 1)
116
117 LOOP
118
119 V_year_start := to_date('01/01/' || V_year, 'dd/mm/yyyy');
120 V_year_end := ADD_MONTHS(V_year_start, 12) - 1;
121
122 INSERT INTO MTH_GREGORIAN_CALENDAR
123 (DIMENSION_KEY,
124 CALENDAR_YEAR_END_DATE,
125 CALENDAR_YEAR_ID,
126 CAL_YEAR_NUMBER,
127 CALENDAR_YEAR_NAME,
128 CALENDAR_YEAR_TIME_SPAN,
129 CALENDAR_YEAR_CAL_YEAR_CODE,
130 CALENDAR_YEAR_DESCRIPTION,
131 CALENDAR_YEAR_START_DATE,
132 CREATION_DATE,
133 LAST_UPDATE_DATE)
134 VALUES
135 (MTH_GREGORIAN_CALENDAR_S.NEXTVAL * (-1),
136 V_year_end,
137 MTH_GREGORIAN_CALENDAR_S.CURRVAL * (-1),
138 V_YEAR,
139 V_YEAR,
140 (V_year_end - V_year_start + 1),
141 V_YEAR,
142 V_YEAR,
143 V_year_start,
144 SYSDATE,
145 SYSDATE);
146
147 END LOOP;
148
149 mth_util_pkg.log_msg('Inserting Year level Complete', mth_util_pkg.G_DBG_OTH);
150
151 /*
152 2. Insert quarter entries at the quarter level by joinning
153 MTH_GREGORIAN_CALENDAR that has the newly created year entries
154 with a SELECT subquery with four rows for each quarter in a year, then selecting year
155 level information from MTH_GREGORIAN_CALENDAR and constructing quarter
156 level information, and lastly inserting quarter entries into
157 MTH_GREGORIAN_CALENDAR.
158
159 */
160 mth_util_pkg.log_msg('Inserting Quarter level', mth_util_pkg.G_DBG_OTH);
161
162 INSERT INTO MTH_GREGORIAN_CALENDAR
163 (DIMENSION_KEY,
164 CALENDAR_YEAR_END_DATE,
165 CALENDAR_YEAR_ID,
166 CAL_YEAR_NUMBER,
167 CALENDAR_YEAR_NAME,
168 CALENDAR_YEAR_TIME_SPAN,
169 CALENDAR_YEAR_CAL_YEAR_CODE,
170 CALENDAR_YEAR_DESCRIPTION,
171 CALENDAR_YEAR_START_DATE,
172 CALENDAR_QUARTER_TIME_SPAN,
173 CALENDAR_QUART_CAL_QUARTER_CO,
174 CALENDAR_QUARTER_START_DATE,
175 CALENDAR_QUARTER_END_DATE,
176 CALENDAR_QUARTER_ID,
177 CALENDAR_QUARTER_DESCRIPTION,
178 CAL_QUARTER_NUMBER,
179 CALENDAR_QUARTER_NAME,
180 QUARTER_OF_YEAR,
181 CREATION_DATE, LAST_UPDATE_DATE)
182
183 SELECT
184 MTH_GREGORIAN_CALENDAR_S.NEXTVAL * (-1) AS DIMENSION_KEY,
185 CALENDAR_YEAR_END_DATE,
186 CALENDAR_YEAR_ID,
187 CAL_YEAR_NUMBER,
188 CALENDAR_YEAR_NAME,
189 CALENDAR_YEAR_TIME_SPAN,
190 CALENDAR_YEAR_CAL_YEAR_CODE,
191 CALENDAR_YEAR_DESCRIPTION,
192 CALENDAR_YEAR_START_DATE,
193
194 (ADD_MONTHS(CALENDAR_YEAR_START_DATE, quarter * 3) -
195 ADD_MONTHS(CALENDAR_YEAR_START_DATE, (quarter-1) *3))
196 AS CALENDAR_QUARTER_TIME_SPAN,
197
198 TO_CHAR(CALENDAR_YEAR_START_DATE, 'YYYY') || quarter || quarter
199 AS CALENDAR_QUART_CAL_QUARTER_CO,
200
201 ADD_MONTHS(CALENDAR_YEAR_START_DATE, (quarter-1) *3)
202 AS CALENDAR_QUARTER_START_DATE,
203
204 ADD_MONTHS(CALENDAR_YEAR_START_DATE, quarter * 3) - 1
205 AS CALENDAR_QUARTER_END_DATE,
206
207 MTH_GREGORIAN_CALENDAR_S.CURRVAL * (-1)
208 AS CALENDAR_QUARTER_ID,
209
210 'Quarter ' || quarter || ' ' || CALENDAR_YEAR_CAL_YEAR_CODE
211 AS CALENDAR_QUARTER_DESCRIPTION,
212
213 (Quarter * 10 + quarter) AS CAL_QUARTER_NUMBER,
214
215 'Q' || quarter || ' ' || CALENDAR_YEAR_CAL_YEAR_CODE
216 AS CALENDAR_QUARTER_NAME,
217
218 Quarter AS QUARTER_OF_YEAR,
219 SYSDATE,
220 SYSDATE
221
222 FROM MTH_GREGORIAN_CALENDAR CAL,
223
224 (SELECT LEVEL AS quarter FROM dual CONNECT BY LEVEL<=4) q
225
226 WHERE CAL.CALENDAR_QUARTER_ID IS NULL AND
227 CAL. CAL_YEAR_NUMBER >= P_START_YEAR;
228
229 mth_util_pkg.log_msg('Inserting Quarter level completed', mth_util_pkg.G_DBG_OTH);
230
231 /*
232 3. Insert month entries at the month level by joinning
233 MTH_GREGORIAN_CALENDAR that has the newly created quarter entries
234 with a SELECT subquery with three rows for each month in a quarter, then selecting
235 year and quarter level information from MTH_GREGORIAN_CALENDAR and
236 constructing month level information, and lastly inserting month entries into
237 MTH_GREGORIAN_CALENDAR.
238 */
239 mth_util_pkg.log_msg('Inserting Month level', mth_util_pkg.G_DBG_OTH);
240
241 INSERT INTO MTH_GREGORIAN_CALENDAR (DIMENSION_KEY,
242
243 CALENDAR_YEAR_END_DATE,
244 CALENDAR_YEAR_ID,
245 CAL_YEAR_NUMBER,
246 CALENDAR_YEAR_NAME,
247 CALENDAR_YEAR_TIME_SPAN,
248 CALENDAR_YEAR_CAL_YEAR_CODE,
249 CALENDAR_YEAR_DESCRIPTION,
250 CALENDAR_YEAR_START_DATE,
251 CALENDAR_QUARTER_TIME_SPAN,
252 CALENDAR_QUART_CAL_QUARTER_CO,
253 CALENDAR_QUARTER_START_DATE,
254 CALENDAR_QUARTER_END_DATE,
255 CALENDAR_QUARTER_ID,
256 CALENDAR_QUARTER_DESCRIPTION,
257 CAL_QUARTER_NUMBER,
258 CALENDAR_QUARTER_NAME,
259 QUARTER_OF_YEAR,
260
261 CALENDAR_MONTH_ID,
262 MONTH_OF_YEAR,
263 CALENDAR_MONTH_DESCRIPTION,
264 CAL_MONTH_NUMBER,
265 CALENDAR_MONTH_TIME_SPAN,
266 CALENDAR_MONTH_START_DATE,
267 CALENDAR_MONTH_CAL_MONTH_CODE,
268 CALENDAR_MONTH_NAME,
269 MONTH_OF_QUARTER,
270 CALENDAR_MONTH_END_DATE,
271 CREATION_DATE,
272 LAST_UPDATE_DATE)
273
274 SELECT MTH_GREGORIAN_CALENDAR_S.NEXTVAL * (-1) AS DIMENSION_KEY,
275
276 -- Year and Quarter Columns--
277 CALENDAR_YEAR_END_DATE,
278 CALENDAR_YEAR_ID,
279 CAL_YEAR_NUMBER,
280 CALENDAR_YEAR_NAME,
281 CALENDAR_YEAR_TIME_SPAN,
282 CALENDAR_YEAR_CAL_YEAR_CODE,
283 CALENDAR_YEAR_DESCRIPTION,
284 CALENDAR_YEAR_START_DATE,
285 CALENDAR_QUARTER_TIME_SPAN,
286 CALENDAR_QUART_CAL_QUARTER_CO,
287 CALENDAR_QUARTER_START_DATE,
288 CALENDAR_QUARTER_END_DATE,
289 CALENDAR_QUARTER_ID,
290 CALENDAR_QUARTER_DESCRIPTION,
291 CAL_QUARTER_NUMBER,
292 CALENDAR_QUARTER_NAME,
293 QUARTER_OF_YEAR,
294
295 -- Year and Quarter Columns--
296
297 MTH_GREGORIAN_CALENDAR_S.CURRVAL * (-1)
298 AS CALENDAR_MONTH_ID,
299
300 ((QUARTER_OF_YEAR - 1) * 3 + month)
301 AS MONTH_OF_YEAR,
302
303 TO_CHAR(ADD_MONTHS(CALENDAR_QUARTER_START_DATE, (month - 1)),'Month YYYY')
304 AS CALENDAR_MONTH_DESCRIPTION,
305
306 ((QUARTER_OF_YEAR - 1) * 3 + MONTH)
307 AS CAL_MONTH_NUMBER,
308
309 (ADD_MONTHS(CALENDAR_QUARTER_START_DATE, month ) -
310 ADD_MONTHS(CALENDAR_QUARTER_START_DATE, (month - 1)) )
311 AS CALENDAR_MONTH_TIME_SPAN,
312
313 ADD_MONTHS(CALENDAR_QUARTER_START_DATE, (month - 1))
314 AS CALENDAR_MONTH_START_DATE,
315
316 TO_NUMBER(
317 TO_CHAR(
318 ADD_MONTHS(
319 CALENDAR_QUARTER_START_DATE, (month - 1)
320 ),
321 'YYYYMM')
322 ) AS CALENDAR_MONTH_CAL_MONTH_CODE,
323
324 TO_CHAR(
325 ADD_MONTHS(
326 CALENDAR_QUARTER_START_DATE, (month - 1)
327 ),'Mon YYYY')
328 AS CALENDAR_MONTH_NAME,
329
330 MONTH AS MONTH_OF_QUARTER,
331
332 (ADD_MONTHS(CALENDAR_QUARTER_START_DATE, month ) - 1)
333 AS CALENDAR_MONTH_END_DATE,
334
335 SYSDATE AS CREATION_DATE,
336 SYSDATE AS LAST_UPDATE_DATE
337 FROM MTH_GREGORIAN_CALENDAR cal,
338
339 (SELECT LEVEL AS MONTH FROM dual CONNECT BY LEVEL<=3) MONTH
340
341 WHERE cal.CALENDAR_QUARTER_ID IS NOT NULL
342 AND cal.CALENDAR_MONTH_ID IS NULL
343 AND cal. CAL_YEAR_NUMBER >= P_START_YEAR;
344
345 mth_util_pkg.log_msg('Inserting Month level completed', mth_util_pkg.G_DBG_OTH);
346
347 /*
348 4. Insert day entries at the Day level by joinning
349 MTH_GREGORIAN_CALENDAR that has the newly created month level entries
350 with a SELECT subquery with maximal 31 rows for each day in a month, then selecting
354 */
351 year, quarter, and month level information from MTH_GREGORIAN_CALENDAR and
352 constructing day level information, and lastly inserting day entries into
353 MTH_GREGORIAN_CALENDAR.
355 mth_util_pkg.log_msg('Inserting Day level', mth_util_pkg.G_DBG_OTH);
356
357
358 INSERT INTO MTH_GREGORIAN_CALENDAR (DIMENSION_KEY,
359
360 -- Year, Quarter and Month Columns--
361
362 CALENDAR_YEAR_END_DATE,
363 CALENDAR_YEAR_ID,
364 CAL_YEAR_NUMBER,
365 CALENDAR_YEAR_NAME,
366 CALENDAR_YEAR_TIME_SPAN,
367 CALENDAR_YEAR_CAL_YEAR_CODE,
368 CALENDAR_YEAR_DESCRIPTION,
369 CALENDAR_YEAR_START_DATE,
370 CALENDAR_QUARTER_TIME_SPAN,
371 CALENDAR_QUART_CAL_QUARTER_CO,
372 CALENDAR_QUARTER_START_DATE,
373 CALENDAR_QUARTER_END_DATE,
374 CALENDAR_QUARTER_ID,
375 CALENDAR_QUARTER_DESCRIPTION,
376 CAL_QUARTER_NUMBER,
377 CALENDAR_QUARTER_NAME,
378 QUARTER_OF_YEAR,
379
380 CALENDAR_MONTH_ID,
381 MONTH_OF_YEAR,
382 CALENDAR_MONTH_DESCRIPTION,
383 CAL_MONTH_NUMBER,
384 CALENDAR_MONTH_TIME_SPAN,
385 CALENDAR_MONTH_START_DATE,
386 CALENDAR_MONTH_CAL_MONTH_CODE,
387 CALENDAR_MONTH_NAME,
388 MONTH_OF_QUARTER,
389 CALENDAR_MONTH_END_DATE,
390
391 -- Year, Quarter and Month Columns--
392
393 DAY_DESCRIPTION,
394 DAY_NAME,
395 DAY_START_DATE,
396 DAY,
397 DAY_END_DATE,
398 DAY_OF_CAL_YEAR,
399 DAY_ID,
400 DAY_OF_CAL_WEEK,
401 DAY_OF_CAL_QUARTER,
402 JULIAN_DATE,
403 DAY_TIME_SPAN,
404 DAY_OF_CAL_MONTH,
405 DAY_DAY_CODE,
406 CREATION_DATE,
407 LAST_UPDATE_DATE)
408
409 SELECT MTH_GREGORIAN_CALENDAR_S.NEXTVAL AS DIMENSION_KEY,
410
411 -- Year, Quarter and Month Columns--
412
413 CALENDAR_YEAR_END_DATE,
414 CALENDAR_YEAR_ID,
415 CAL_YEAR_NUMBER,
416 CALENDAR_YEAR_NAME,
417 CALENDAR_YEAR_TIME_SPAN,
418 CALENDAR_YEAR_CAL_YEAR_CODE,
419 CALENDAR_YEAR_DESCRIPTION,
420 CALENDAR_YEAR_START_DATE,
421 CALENDAR_QUARTER_TIME_SPAN,
422 CALENDAR_QUART_CAL_QUARTER_CO,
423 CALENDAR_QUARTER_START_DATE,
424 CALENDAR_QUARTER_END_DATE,
425 CALENDAR_QUARTER_ID,
426 CALENDAR_QUARTER_DESCRIPTION,
427 CAL_QUARTER_NUMBER,
428 CALENDAR_QUARTER_NAME,
429 QUARTER_OF_YEAR,
430
431 CALENDAR_MONTH_ID,
432 MONTH_OF_YEAR,
433 CALENDAR_MONTH_DESCRIPTION,
434 CAL_MONTH_NUMBER,
435 CALENDAR_MONTH_TIME_SPAN,
436 CALENDAR_MONTH_START_DATE,
437 CALENDAR_MONTH_CAL_MONTH_CODE,
438 CALENDAR_MONTH_NAME,
439 MONTH_OF_QUARTER,
440 CALENDAR_MONTH_END_DATE,
441
442 -- Year, Quarter and Month Columns--
443
444 TO_CHAR (CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1,'dd-MON-YYYY')
445 AS DAY_DESCRIPTION,
446
447 TO_CHAR (CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1,'dd-MON-YYYY')
448 AS DAY_NAME,
449
450 (CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1)
451 AS DAY_START_DATE,
452
453 (CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1)
454 AS DAY,
455
456 (CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1)
457 AS DAY_END_DATE,
458
459 (CALENDAR_MONTH_START_DATE - CALENDAR_YEAR_START_DATE + DAY_IN_MONTH)
460 AS DAY_OF_CAL_YEAR,
461
462 MTH_GREGORIAN_CALENDAR_S.CURRVAL
463 AS DAY_ID,
464
465 TO_CHAR (CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1,'D')
466 AS DAY_OF_CAL_WEEK,
467
468 /* 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.*/
469
470 (CALENDAR_MONTH_START_DATE - CALENDAR_QUARTER_START_DATE + DAY_IN_MONTH)
471 AS DAY_OF_CAL_QUARTER,
472
473 TO_NUMBER(
474 TO_CHAR(
475 CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1
476 , 'J'))
477 AS JULIAN_DATE,
478
479 1 AS DAY_TIME_SPAN,
480 DAY_IN_MONTH AS DAY_OF_CAL_MONTH,
481
482 TO_NUMBER(
483 TO_CHAR(CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1
484 , 'YYYYMMDD'))
485 AS DAY_DAY_CODE,
486
487 SYSDATE AS CREATION_DATE,
488 SYSDATE AS LAST_UPDATE_DATE
489
490 FROM MTH_GREGORIAN_CALENDAR cal,
491
492 (SELECT LEVEL AS DAY_IN_MONTH FROM dual CONNECT BY LEVEL<=31) days
493
494 WHERE cal.DAY_ID IS NULL
495 AND cal.CALENDAR_MONTH_ID IS NOT NULL
496 AND cal.CAL_YEAR_NUMBER >= P_START_YEAR
497 AND (cal.CALENDAR_MONTH_START_DATE + DAY_IN_MONTH - 1 < ADD_MONTHS(cal.CALENDAR_MONTH_START_DATE, 1)
498 );
499
500 mth_util_pkg.log_msg('Inserting Day level completed', mth_util_pkg.G_DBG_OTH);
501
502 mth_util_pkg.log_msg('VALIDATE_GREG_CAL_INPUT_PARAMS end', mth_util_pkg.G_DBG_PROC_FUN_END);
503
504 EXCEPTION
505 WHEN OTHERS THEN
506 --Call logging API and then throw exception
507 mth_util_pkg.log_msg('Exception OTHERS in VALIDATE_GREG_CAL_INPUT_PARAMS', mth_util_pkg.G_DBG_EXCEPTION);
508 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
509 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
510
511 END;
512
513 /* ****************************************************************************
514 * Procedure :POPULATE_HOURS *
515 * Description :... *
519 ******************************************************************************/
516 * File Name :MTHCALRB.PLS *
517 * Visibility :Public *
518 * Parameters : *
520
521 PROCEDURE POPULATE_HOURS
522 IS
523 v_ua_val VARCHAR2(100);
524 v_log_date DATE;
525 BEGIN
526
527 mth_util_pkg.log_msg('POPULATE_HOURS start', mth_util_pkg.G_DBG_PROC_FUN_START);
528
529 v_ua_val := MTH_UTIL_PKG.MTH_UA_GET_VAL();
530 v_log_date := SYSDATE;
531
532 MERGE
533 INTO MTH_HOUR_D mhd
534 USING
535 (
536 SELECT days.day + ((60*60*(hours.hour-1))/86400) from_time,
537 days.day + (((60*60*(hours.hour))-1)/86400) to_time,
538 To_Char(days.DAY,'DD-MON-YYYY')||'-'||hours.HOUR hour_pk
539 FROM (SELECT day
540 FROM mth_gregorian_calendar
541 WHERE day IS NOT NULL
542 UNION
543 SELECT day
544 FROM mth_445_period_calendar
545 WHERE day IS NOT NULL
546 UNION
547 SELECT report_date day
548 FROM mth_day_d) days,
549 (SELECT LEVEL HOUR
550 FROM dual
551 CONNECT BY LEVEL <= 24) hours
552 ) cal_hours
553 ON
554 ( mhd.hour_pk = cal_hours.hour_pk)
555
556 WHEN NOT MATCHED THEN
557 INSERT
558 ( mhd.hour_pk_key,
559 mhd.hour_pk,
560 mhd.from_time,
561 mhd.to_time,
562 mhd.system_fk_key,
563 mhd.creation_date,
564 mhd.last_update_date,
565 mhd.creation_system_id,
566 mhd.last_update_system_id
567 )
568 VALUES
569 ( MTH_TIME_S.NEXTVAL,
570 cal_hours.hour_pk,
571 cal_hours.from_time,
572 cal_hours.to_time,
573 v_ua_val,
574 v_log_date,
575 v_log_date,
576 v_ua_val,
577 v_ua_val
578 );
579
580 mth_util_pkg.log_msg('POPULATE_HOURS end', mth_util_pkg.G_DBG_PROC_FUN_END);
581
582 EXCEPTION
583 WHEN OTHERS THEN
584 --Call logging API and then throw exception
585 mth_util_pkg.log_msg('Exception OTHERS in VALIDATE_GREG_CAL_INPUT_PARAMS', mth_util_pkg.G_DBG_EXCEPTION);
586 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
587 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
588
589 END;
590
591
592 FUNCTION get_445_544_year_end( P_YEAR IN NUMBER, P_START_MONTH IN NUMBER, P_START_WEEK_DAY IN NUMBER , P_YEAR_END_VARIATION IN NUMBER) RETURN DATE
593 IS
594 v_next_yr_start_dte VARCHAR(20);
595 v_month_end_date DATE;
596 v_week_day_on_month_end NUMBER;
597 v_end_week_day NUMBER;
598 v_year_end_date DATE;
599 v_diff NUMBER;
600
601 BEGIN
602 mth_util_pkg.log_msg('get_445_544_year_end start', mth_util_pkg.G_DBG_PROC_FUN_START);
603 -- 1. Get the last day of the calendar month of next year in 445/544 calendar
604 v_next_yr_start_dte := (P_START_MONTH ) || '/1/' || (P_YEAR + 1);
605 v_month_end_date := to_date(v_next_yr_start_dte, 'mm/dd/yyyy' ) - 1;
606
607 -- 2. Find out the week day for the end of month of the year and end day of week
608 SELECT To_Number(To_Char(v_month_end_date, 'D')) into v_week_day_on_month_end FROM dual;
609 v_end_week_day := CASE WHEN P_START_WEEK_DAY = 1 THEN 7
610 ELSE P_START_WEEK_DAY - 1 END;
611
612 -- 3. Calculate the year end date by comparing the week day of the month end with
613 -- the end day of the week and then adjusting the year end date accordingly
614 -- to match the week day of the year end with the expected value
615 v_year_end_date := null;
616 v_diff := v_week_day_on_month_end - v_end_week_day;
617 If (v_week_day_on_month_end = v_end_week_day) THEN
618 -- The week day of the month end matches with the expected value.
619 -- It means that the month end happens to be the year end
620 v_year_end_date := v_month_end_date;
621 ELSIF (v_diff > 0) THEN
622 IF (P_YEAR_END_VARIATION = 1 OR v_diff <= 3) THEN
623 v_year_end_date := v_month_end_date - v_diff;
624 ELSE
625 v_year_end_date := v_month_end_date - v_diff + 7;
626 END IF;
627 ELSE -- for case where v_diff < 0
628 IF (P_YEAR_END_VARIATION = 1 OR v_diff < -3) THEN
629 v_year_end_date := v_month_end_date - v_diff - 7;
630 ELSE
631 v_year_end_date := v_month_end_date - v_diff;
632 END IF;
633 END IF;
634 RETURN v_year_end_date;
635 mth_util_pkg.log_msg('get_445_544_year_end end', mth_util_pkg.G_DBG_PROC_FUN_END);
636 END;
637
638
639 PROCEDURE Populate_445_cal_denrom(p_err_buff OUT NOCOPY VARCHAR2,
640 p_retcode OUT NOCOPY NUMBER,
641 P_START_YEAR IN NUMBER, P_NUM_YEARS IN NUMBER,
642 P_CAL_TYPE IN NUMBER, P_START_MONTH IN NUMBER,
643 P_START_DAY IN NUMBER, P_YEAR_END_VARIATION IN NUMBER)
644 IS
645 v_year NUMBER;
646 v_year_end_date DATE;
647 v_year_start_date DATE;
648 l_execution_id NUMBER;
649 v_num Number;
650
651
652 BEGIN
653 mth_util_pkg.initialize_debug('Concurrent Program "MTH: Populate MOC Gregorian Calendar"', l_execution_id);
654 mth_util_pkg.log_msg('Populate_445_cal_denrom start', mth_util_pkg.G_DBG_PROC_FUN_START);
655
656 mth_util_pkg.log_msg('P_START_YEAR = ' || P_START_YEAR , mth_util_pkg.G_DBG_MAIN_PARAM);
657 mth_util_pkg.log_msg('P_NUM_YEARS = ' || P_NUM_YEARS , mth_util_pkg.G_DBG_MAIN_PARAM);
658 mth_util_pkg.log_msg('P_CAL_TYPE = ' || P_CAL_TYPE , mth_util_pkg.G_DBG_MAIN_PARAM);
662
659 mth_util_pkg.log_msg('P_START_MONTH = ' || P_START_MONTH , mth_util_pkg.G_DBG_MAIN_PARAM);
660 mth_util_pkg.log_msg('P_START_DAY = ' || P_START_DAY , mth_util_pkg.G_DBG_MAIN_PARAM);
661 mth_util_pkg.log_msg('P_YEAR_END_VARIATION = ' || P_YEAR_END_VARIATION , mth_util_pkg.G_DBG_MAIN_PARAM);
663
664
665 -- 1. Populate yearly data into MTH_445_PERIOD_CALENDAR
666 -- 1.1 Find the start date of the year by finding the year end of last year plus one day
667 v_year_end_date := get_445_544_year_end(P_START_YEAR - 1, P_START_MONTH, P_START_DAY, P_YEAR_END_VARIATION);
668 --P_START_MONTH IN NUMBER, P_START_WEEK_DAY IN NUMBER , P_YEAR_END_VARIATION IN NUMBER
669 v_num:= 0;
670 --For each year v_year starting from year P_START_YEAR for P_NUM_YEARS number of years
671 FOR V_YEAR IN P_START_YEAR .. (P_START_YEAR + P_NUM_YEARS -1)
672 LOOP
673 -- 1.2. Find year beging and year end for 445/544 calendar
674 v_year_start_date := v_year_end_date + 1;
675 v_year_end_date := get_445_544_year_end( v_year, P_START_MONTH, P_START_DAY, P_YEAR_END_VARIATION);
676
677 -- 1.3. Populate yearly
678 INSERT INTO MTH_445_PERIOD_CALENDAR
679 (DIMENSION_KEY,
680 FISCAL_YEAR_ID,
681 FISCAL_YEAR_NAME,
682 FIS_YEAR_NUMBER,
683 FISCAL_YEAR_START_DATE,
684 FISCAL_YEAR_TIME_SPAN,
685 FISCAL_YEAR_FIS_YEAR_CODE,
686 FISCAL_YEAR_DESCRIPTION,
687 FISCAL_YEAR_END_DATE,
688 CREATION_DATE,
689 LAST_UPDATE_DATE)
690 values
691 (MTH_445_PERIOD_CALENDAR_S.NEXTVAL * (-1),
692 MTH_445_PERIOD_CALENDAR_S.CURRVAL * (-1),
693 v_year,
694 v_year,
695 v_year_start_date,
696 (v_year_end_date - v_year_start_date + 1),
697 v_year,
698 'Fiscal Year ' || v_year,
699 v_year_end_date,
700 SYSDATE,
701 SYSDATE);
702
703 v_num := v_num + 1;
704
705 END LOOP;
706
707 mth_util_pkg.log_msg('Inserted ' || v_num || ' rows.',mth_util_pkg.G_DBG_ROW_CNT);
708 mth_util_pkg.log_msg('Inserting Year level Complete',mth_util_pkg.G_DBG_OTH);
709 /*
710 2. Insert quarter entries at the quarter level by joinning
711 MTH_445_PERIOD_CALENDAR that has the newly created year level information
712 with a SELECT subquery with four rows for each quarter in a year, then selecting year
713 level information from MTH_445_PERIOD_CALENDAR and constructing quarter
714 level information, and lastly inserting quarter entries into
715 MTH_445_PERIOD_CALENDAR.
716 */
717 INSERT INTO MTH_445_PERIOD_CALENDAR (DIMENSION_KEY,
718 --<Year columns>,
719
720 FISCAL_YEAR_ID,
721 FISCAL_YEAR_NAME,
722 FIS_YEAR_NUMBER,
723 FISCAL_YEAR_START_DATE,
724 FISCAL_YEAR_TIME_SPAN,
725 FISCAL_YEAR_FIS_YEAR_CODE,
726 FISCAL_YEAR_DESCRIPTION,
727 FISCAL_YEAR_END_DATE,
728
729 FISCAL_QUARTER_ID,
730 FISCAL_QUARTER_NAME,
731 FIS_QUARTER_NUMBER,
732 FISCAL_QUARTER_START_DATE,
733 FISCAL_QUARTER_TIME_SPAN,
734 QUARTER_OF_FISCAL_YEAR,
735 FISCAL_QUARTER_FIS_QUARTER_CO,
736 FISCAL_QUARTER_END_DATE,
737 FISCAL_QUARTER_DESCRIPTION,
738 CREATION_DATE,
739 LAST_UPDATE_DATE)
740 SELECT MTH_445_PERIOD_CALENDAR_S.NEXTVAL * (-1) AS DIMENSION_KEY,
741 --<Year columns> ,
742
743 FISCAL_YEAR_ID,
744 FISCAL_YEAR_NAME,
745 FIS_YEAR_NUMBER,
746 FISCAL_YEAR_START_DATE,
747 FISCAL_YEAR_TIME_SPAN,
748 FISCAL_YEAR_FIS_YEAR_CODE,
749 FISCAL_YEAR_DESCRIPTION,
750 FISCAL_YEAR_END_DATE,
751
752 -- Need to calculate the span for the last quarter
753 MTH_445_PERIOD_CALENDAR_S.CURRVAL * (-1)
754 AS FISCAL_QUARTER_ID,
755 'Q' || quarter || ' ' || FISCAL_YEAR_FIS_YEAR_CODE
756 AS FISCAL_QUARTER_NAME,
757 quarter as FIS_QUARTER_NUMBER,
758 CASE WHEN quarter = 1 THEN FISCAL_YEAR_START_DATE
759 ELSE FISCAL_YEAR_START_DATE + (quarter - 1) * 13 * 7
760 END
761 AS FISCAL_QUARTER_START_DATE,
762
763 CASE WHEN quarter = 4 THEN (FISCAL_YEAR_END_DATE - FISCAL_YEAR_START_DATE) + 1 - (13 * 7 * 3 ) ELSE 13 * 7 END
764 AS FISCAL_QUARTER_TIME_SPAN,
765 quarter as QUARTER_OF_FISCAL_YEAR,
766 TO_CHAR(FISCAL_YEAR_START_DATE, 'YYYY') || quarter || quarter
767 AS FISCAL_QUARTER_FIS_QUARTER_CO,
768 CASE WHEN quarter = 4 THEN FISCAL_YEAR_END_DATE
769 ELSE FISCAL_YEAR_START_DATE + (quarter * 13 * 7) -1 END
770 AS FISCAL_QUARTER_END_DATE,
771 'Fiscal Quarter ' || quarter || ' ' || FISCAL_YEAR_NAME
772 AS FISCAL_QUARTER_DESCRIPTION,
773
774 SYSDATE,
775 SYSDATE
776 FROM MTH_445_PERIOD_CALENDAR cal,
777 (select LEVEL AS quarter from dual connect by LEVEL <= 4) q
778 WHERE cal.FISCAL_QUARTER_ID IS NULL
779 AND cal.FIS_YEAR_NUMBER >= P_START_YEAR;
780
781 /*
782 3. Insert month entries at the month level by joinning
783 MTH_445_PERIOD_CALENDAR that has the newly created quarter entries
784 with a SELECT subquery with three rows for each month in a quarter, then selecting
785 year and quarter level information from MTH_445_PERIOD_CALENDAR and
786 constructing month level information, and lastly inserting month entries into
787 MTH_445_PERIOD_CALENDAR.
788 */
789 mth_util_pkg.log_msg('Inserted ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_ROW_CNT);
790 mth_util_pkg.log_msg('Inserting Quarter level Complete',mth_util_pkg.G_DBG_OTH);
791
792 INSERT INTO MTH_445_PERIOD_CALENDAR (DIMENSION_KEY,
793 --<Year columns>,
794 FISCAL_YEAR_ID,
795 FISCAL_YEAR_NAME,
796 FIS_YEAR_NUMBER,
797 FISCAL_YEAR_START_DATE,
798 FISCAL_YEAR_TIME_SPAN,
799 FISCAL_YEAR_FIS_YEAR_CODE,
800 FISCAL_YEAR_DESCRIPTION,
801 FISCAL_YEAR_END_DATE,
805 FIS_QUARTER_NUMBER,
802
803 FISCAL_QUARTER_ID,
804 FISCAL_QUARTER_NAME,
806 FISCAL_QUARTER_START_DATE,
807 FISCAL_QUARTER_TIME_SPAN,
808 QUARTER_OF_FISCAL_YEAR,
809 FISCAL_QUARTER_FIS_QUARTER_CO,
810 FISCAL_QUARTER_END_DATE,
811 FISCAL_QUARTER_DESCRIPTION,
812 --<Quarter columns>,
813
814 FISCAL_MONTH_START_DATE,
815 FISCAL_MONTH_FIS_MONTH_CODE,
816 FISCAL_MONTH_DESCRIPTION,
817 MONTH_OF_FISCAL_QUARTER,
818 FISCAL_MONTH_END_DATE,
819 FISCAL_MONTH_ID,
820 FISCAL_MONTH_NAME,
821 FIS_MONTH_NUMBER,
822 FISCAL_MONTH_TIME_SPAN,
823 MONTH_OF_FISCAL_YEAR,
824 CREATION_DATE,
825 LAST_UPDATE_DATE)
826 SELECT MTH_445_PERIOD_CALENDAR_S.NEXTVAL * (-1) AS DIMENSION_KEY,
827 --<Year columns>,
828 FISCAL_YEAR_ID,
829 FISCAL_YEAR_NAME,
830 FIS_YEAR_NUMBER,
831 FISCAL_YEAR_START_DATE,
832 FISCAL_YEAR_TIME_SPAN,
833 FISCAL_YEAR_FIS_YEAR_CODE,
834 FISCAL_YEAR_DESCRIPTION,
835 FISCAL_YEAR_END_DATE,
836
837 FISCAL_QUARTER_ID,
838 FISCAL_QUARTER_NAME,
839 FIS_QUARTER_NUMBER,
840 FISCAL_QUARTER_START_DATE,
841 FISCAL_QUARTER_TIME_SPAN,
842 QUARTER_OF_FISCAL_YEAR,
843 FISCAL_QUARTER_FIS_QUARTER_CO,
844 FISCAL_QUARTER_END_DATE,
845 FISCAL_QUARTER_DESCRIPTION,
846 --<Quarter columns>,
847
848 FISCAL_QUARTER_START_DATE +
849 CASE WHEN MONTH = 1 THEN 0
850 WHEN MONTH = 2 AND P_CAL_TYPE = 445 THEN (4 * 7)
851 WHEN MONTH = 2 AND P_CAL_TYPE = 544 THEN (5 * 7)
852 WHEN MONTH = 3 AND P_CAL_TYPE = 445 THEN (8 * 7)
853 WHEN MONTH = 3 AND P_CAL_TYPE = 544 THEN (9 * 7)
854 END
855 AS FISCAL_MONTH_START_DATE,
856 TO_NUMBER(FISCAL_YEAR_NAME ||
857 ((FIS_QUARTER_NUMBER - 1) * 3 + MONTH) ||
858 ((FIS_QUARTER_NUMBER - 1) * 3 + MONTH))
859 AS FISCAL_MONTH_FIS_MONTH_CODE,
860 'Fiscal Month ' || ((FIS_QUARTER_NUMBER - 1) * 3 + MONTH) || ' ' || FISCAL_YEAR_NAME
861 AS FISCAL_MONTH_DESCRIPTION,
862 MONTH AS MONTH_OF_FISCAL_QUARTER,
863 CASE WHEN MONTH = 3 THEN FISCAL_QUARTER_END_DATE
864 WHEN MONTH = 2 AND P_CAL_TYPE = 445
865 THEN FISCAL_QUARTER_START_DATE + ((8 * 7) - 1)
866 WHEN MONTH = 2 AND P_CAL_TYPE = 544
867 THEN FISCAL_QUARTER_START_DATE + ((9 * 7) - 1)
868 WHEN MONTH = 1 AND P_CAL_TYPE = 445
869 THEN FISCAL_QUARTER_START_DATE + ((4 * 7) - 1)
870 WHEN MONTH = 1 AND P_CAL_TYPE = 544
871 THEN FISCAL_QUARTER_START_DATE + ((5 * 7) - 1) END
872 AS FISCAL_MONTH_END_DATE,
873 MTH_445_PERIOD_CALENDAR_S.CURRVAL * (-1) AS FISCAL_MONTH_ID,
874 'Month ' || ( (FIS_QUARTER_NUMBER - 1) * 3 + MONTH) || ' ' || FISCAL_YEAR_NAME
875 AS FISCAL_MONTH_NAME,
876 ((FIS_QUARTER_NUMBER - 1) * 3 + MONTH) AS FIS_MONTH_NUMBER,
877 CASE WHEN MONTH = 1 AND P_CAL_TYPE = 445 THEN 4 * 7
878 WHEN MONTH = 1 AND P_CAL_TYPE = 544 THEN 5 * 7
879 WHEN MONTH = 2 THEN 4 * 7
880 WHEN MONTH = 3 AND FIS_QUARTER_NUMBER < 4 AND P_CAL_TYPE = 544
881 THEN 4 * 7
882 WHEN MONTH = 3 AND FIS_QUARTER_NUMBER < 4 AND P_CAL_TYPE = 445
883 THEN 5 * 7
884 WHEN MONTH = 3 AND P_CAL_TYPE = 544
885 THEN 4 * 7 + (((FISCAL_YEAR_END_DATE - FISCAL_YEAR_START_DATE + 1) / 7) - 52) * 7
886 WHEN MONTH = 3 AND P_CAL_TYPE = 445
887 THEN 5 * 7 + (((FISCAL_YEAR_END_DATE - FISCAL_YEAR_START_DATE + 1) / 7) - 52) * 7
888 END
889 AS FISCAL_MONTH_TIME_SPAN,
890
891 ((FIS_QUARTER_NUMBER - 1) * 3 + MONTH) AS MONTH_OF_FISCAL_YEAR,
892 SYSDATE AS CREATION_DATE,
893 SYSDATE AS LAST_UPDATE_DATE
894 FROM MTH_445_PERIOD_CALENDAR cal,
895 (select LEVEL AS MONTH from dual connect by LEVEL <= 3) MONTHS
896 WHERE cal.FISCAL_QUARTER_ID IS NOT NULL
897 AND cal.FISCAL_MONTH_ID IS NULL
898 AND cal.FIS_YEAR_NUMBER >= P_START_YEAR;
899
900 mth_util_pkg.log_msg('Inserted ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_ROW_CNT);
901 mth_util_pkg.log_msg('Inserting Month level Complete',mth_util_pkg.G_DBG_OTH);
902
903 /* 4. Insert day entries at the Week level by joinning
904 MTH_445_PERIOD_CALENDAR that has the newly created month entries
905 with a SELECT subquery with maximal 6 rows for each week in a month, then
906 selecting year, quarter and month level information from
907 MTH_445_PERIOD_CALENDAR
908 and constructing week level information, and lastly inserting week entries into
909 MTH_445_PERIOD_CALENDAR.
910 */
911 INSERT INTO MTH_445_PERIOD_CALENDAR (DIMENSION_KEY,
912
913 FISCAL_YEAR_ID,
914 FISCAL_YEAR_NAME,
915 FIS_YEAR_NUMBER,
916 FISCAL_YEAR_START_DATE,
917 FISCAL_YEAR_TIME_SPAN,
918 FISCAL_YEAR_FIS_YEAR_CODE,
919 FISCAL_YEAR_DESCRIPTION,
920 FISCAL_YEAR_END_DATE,
921 --<Year columns>,
922
923 FISCAL_QUARTER_ID,
924 FISCAL_QUARTER_NAME,
925 FIS_QUARTER_NUMBER,
926 FISCAL_QUARTER_START_DATE,
927 FISCAL_QUARTER_TIME_SPAN,
928 QUARTER_OF_FISCAL_YEAR,
929 FISCAL_QUARTER_FIS_QUARTER_CO,
930 FISCAL_QUARTER_END_DATE,
931 FISCAL_QUARTER_DESCRIPTION,
932 --<Quarter columns>,
933
934 FISCAL_MONTH_START_DATE,
935 FISCAL_MONTH_FIS_MONTH_CODE,
936 FISCAL_MONTH_DESCRIPTION,
937 MONTH_OF_FISCAL_QUARTER,
938 FISCAL_MONTH_END_DATE,
939 FISCAL_MONTH_ID,
940 FISCAL_MONTH_NAME,
941 FIS_MONTH_NUMBER,
942 FISCAL_MONTH_TIME_SPAN,
943 MONTH_OF_FISCAL_YEAR,
944
945 --<Month columns>,
946
947 FISCAL_WEEK_START_DATE,
948 FISCAL_WEEK_FIS_WEEK_CODE,
949 FISCAL_WEEK_DESCRIPTION,
950 WEEK_OF_FISCAL_MONTH,
951 FISCAL_WEEK_TIME_SPAN,
955 FISCAL_WEEK_ID,
952 WEEK_OF_FISCAL_YEAR,
953 FIS_WEEK_NUMBER,
954 FISCAL_WEEK_END_DATE,
956 FISCAL_WEEK_NAME,
957 WEEK_OF_FISCAL_QUARTER,
958 CREATION_DATE,
959 LAST_UPDATE_DATE)
960
961 SELECT MTH_445_PERIOD_CALENDAR_S.NEXTVAL * (-1) AS DIMENSION_KEY,
962
963 FISCAL_YEAR_ID,
964 FISCAL_YEAR_NAME,
965 FIS_YEAR_NUMBER,
966 FISCAL_YEAR_START_DATE,
967 FISCAL_YEAR_TIME_SPAN,
968 FISCAL_YEAR_FIS_YEAR_CODE,
969 FISCAL_YEAR_DESCRIPTION,
970 FISCAL_YEAR_END_DATE,
971 --<Year columns>,
972
973 FISCAL_QUARTER_ID,
974 FISCAL_QUARTER_NAME,
975 FIS_QUARTER_NUMBER,
976 FISCAL_QUARTER_START_DATE,
977 FISCAL_QUARTER_TIME_SPAN,
978 QUARTER_OF_FISCAL_YEAR,
979 FISCAL_QUARTER_FIS_QUARTER_CO,
980 FISCAL_QUARTER_END_DATE,
981 FISCAL_QUARTER_DESCRIPTION,
982 --<Quarter columns>,
983
984 FISCAL_MONTH_START_DATE,
985 FISCAL_MONTH_FIS_MONTH_CODE,
986 FISCAL_MONTH_DESCRIPTION,
987 MONTH_OF_FISCAL_QUARTER,
988 FISCAL_MONTH_END_DATE,
989 FISCAL_MONTH_ID,
990 FISCAL_MONTH_NAME,
991 FIS_MONTH_NUMBER,
992 FISCAL_MONTH_TIME_SPAN,
993 MONTH_OF_FISCAL_YEAR,
994
995 --<Month columns>,
996
997 FISCAL_MONTH_START_DATE + (WEEKS.WEEK - 1) * 7
998 AS FISCAL_WEEK_START_DATE,
999 TO_NUMBER(FISCAL_YEAR_NAME || (ROUND((FISCAL_MONTH_START_DATE - FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK) ||
1000 (ROUND((FISCAL_MONTH_START_DATE - FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK))
1001 AS FISCAL_WEEK_FIS_WEEK_CODE,
1002 'Fiscal Week ' || (ROUND((FISCAL_MONTH_START_DATE -
1003 FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK) || ' ' ||
1004 FISCAL_YEAR_NAME AS FISCAL_WEEK_DESCRIPTION,
1005 WEEKS.WEEK
1006 AS WEEK_OF_FISCAL_MONTH,
1007 7 AS FISCAL_WEEK_TIME_SPAN,
1008 (ROUND((FISCAL_MONTH_START_DATE -
1009 FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK)
1010 AS WEEK_OF_FISCAL_YEAR,
1011 (ROUND((FISCAL_MONTH_START_DATE -
1012 FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK)
1013 AS FIS_WEEK_NUMBER,
1014 (FISCAL_MONTH_START_DATE + WEEKS.WEEK * 7) - 1
1015 AS FISCAL_WEEK_END_DATE,
1016 MTH_445_PERIOD_CALENDAR_S.CURRVAL * (-1)
1017 AS FISCAL_WEEK_ID,
1018 'Wk ' || (ROUND((FISCAL_MONTH_START_DATE -
1019 FISCAL_YEAR_START_DATE + 1) / 7) + WEEKS.WEEK) || ' ' || FISCAL_YEAR_NAME
1020 AS FISCAL_WEEK_NAME,
1021 ROUND((FISCAL_MONTH_START_DATE - FISCAL_QUARTER_START_DATE) / 7) + WEEKS.WEEK
1022 AS WEEK_OF_FISCAL_QUARTER,
1023 SYSDATE AS CREATION_DATE,
1024 SYSDATE AS LAST_UPDATE_DATE
1025
1026 FROM MTH_445_PERIOD_CALENDAR cal,
1027 (select LEVEL AS WEEK from dual connect by LEVEL <= 6) WEEKS
1028 --at most 6 weeks in one month
1029 WHERE cal.FISCAL_MONTH_ID IS NOT NULL AND
1030 cal.FISCAL_WEEK_ID IS NULL AND
1031 cal.FIS_YEAR_NUMBER >= P_START_YEAR AND
1032 ((cal.FISCAL_MONTH_START_DATE + WEEKS.WEEK * 7) - 1) <= cal.FISCAL_MONTH_END_DATE;
1033 mth_util_pkg.log_msg('Inserted ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_ROW_CNT);
1034 mth_util_pkg.log_msg('Inserting Week level Complete',mth_util_pkg.G_DBG_OTH);
1035 /*
1036 5. Insert day entries at the Day level by joinning
1037 MTH_445_PERIOD_CALENDAR that has the newly created week level information
1038 with a SELECT subquery with 7 rows for each day in a week, then selecting
1039 year, quarter, month, and week level information from MTH_445_PERIOD_CALENDAR
1040 and constructing day level information, and lastly inserting day entries into
1041 MTH_445_PERIOD_CALENDAR.
1042 */
1043 INSERT INTO MTH_445_PERIOD_CALENDAR (DIMENSION_KEY,
1044
1045 FISCAL_YEAR_ID,
1046 FISCAL_YEAR_NAME,
1047 FIS_YEAR_NUMBER,
1048 FISCAL_YEAR_START_DATE,
1049 FISCAL_YEAR_TIME_SPAN,
1050 FISCAL_YEAR_FIS_YEAR_CODE,
1051 FISCAL_YEAR_DESCRIPTION,
1052 FISCAL_YEAR_END_DATE,
1053 --<Year columns>,
1054
1055 FISCAL_QUARTER_ID,
1056 FISCAL_QUARTER_NAME,
1057 FIS_QUARTER_NUMBER,
1058 FISCAL_QUARTER_START_DATE,
1059 FISCAL_QUARTER_TIME_SPAN,
1060 QUARTER_OF_FISCAL_YEAR,
1061 FISCAL_QUARTER_FIS_QUARTER_CO,
1062 FISCAL_QUARTER_END_DATE,
1063 FISCAL_QUARTER_DESCRIPTION,
1064 --<Quarter columns>,
1065
1066 FISCAL_MONTH_START_DATE,
1067 FISCAL_MONTH_FIS_MONTH_CODE,
1068 FISCAL_MONTH_DESCRIPTION,
1069 MONTH_OF_FISCAL_QUARTER,
1070 FISCAL_MONTH_END_DATE,
1071 FISCAL_MONTH_ID,
1072 FISCAL_MONTH_NAME,
1073 FIS_MONTH_NUMBER,
1074 FISCAL_MONTH_TIME_SPAN,
1075 MONTH_OF_FISCAL_YEAR,
1076
1077 --<Month columns>,
1078
1079 FISCAL_WEEK_START_DATE,
1080 FISCAL_WEEK_FIS_WEEK_CODE,
1081 FISCAL_WEEK_DESCRIPTION,
1082 WEEK_OF_FISCAL_MONTH,
1083 FISCAL_WEEK_TIME_SPAN,
1084 WEEK_OF_FISCAL_YEAR,
1085 FIS_WEEK_NUMBER,
1086 FISCAL_WEEK_END_DATE,
1087 FISCAL_WEEK_ID,
1088 FISCAL_WEEK_NAME,
1089 WEEK_OF_FISCAL_QUARTER,
1090
1091
1092 DAY_START_DATE,
1093 DAY_OF_FISCAL_YEAR,
1094 JULIAN_DATE,
1095 DAY_OF_FISCAL_WEEK,
1096 DAY_TIME_SPAN,
1097 DAY,
1098 DAY_ID,
1099 DAY_DAY_CODE,
1100 DAY_DESCRIPTION,
1101 DAY_NAME,
1102 DAY_END_DATE,
1103 DAY_OF_FISCAL_QUARTER,
1104 DAY_OF_FISCAL_MONTH,
1105 CREATION_DATE,
1106 LAST_UPDATE_DATE)
1107
1108 SELECT MTH_445_PERIOD_CALENDAR_S.NEXTVAL AS DIMENSION_KEY,
1109
1110 FISCAL_YEAR_ID,
1111 FISCAL_YEAR_NAME,
1112 FIS_YEAR_NUMBER,
1113 FISCAL_YEAR_START_DATE,
1114 FISCAL_YEAR_TIME_SPAN,
1115 FISCAL_YEAR_FIS_YEAR_CODE,
1119
1116 FISCAL_YEAR_DESCRIPTION,
1117 FISCAL_YEAR_END_DATE,
1118 --<Year columns>,
1120 FISCAL_QUARTER_ID,
1121 FISCAL_QUARTER_NAME,
1122 FIS_QUARTER_NUMBER,
1123 FISCAL_QUARTER_START_DATE,
1124 FISCAL_QUARTER_TIME_SPAN,
1125 QUARTER_OF_FISCAL_YEAR,
1126 FISCAL_QUARTER_FIS_QUARTER_CO,
1127 FISCAL_QUARTER_END_DATE,
1128 FISCAL_QUARTER_DESCRIPTION,
1129 --<Quarter columns>,
1130
1131 FISCAL_MONTH_START_DATE,
1132 FISCAL_MONTH_FIS_MONTH_CODE,
1133 FISCAL_MONTH_DESCRIPTION,
1134 MONTH_OF_FISCAL_QUARTER,
1135 FISCAL_MONTH_END_DATE,
1136 FISCAL_MONTH_ID,
1137 FISCAL_MONTH_NAME,
1138 FIS_MONTH_NUMBER,
1139 FISCAL_MONTH_TIME_SPAN,
1140 MONTH_OF_FISCAL_YEAR,
1141
1142 --<Month columns>,
1143
1144 FISCAL_WEEK_START_DATE,
1145 FISCAL_WEEK_FIS_WEEK_CODE,
1146 FISCAL_WEEK_DESCRIPTION,
1147 WEEK_OF_FISCAL_MONTH,
1148 FISCAL_WEEK_TIME_SPAN,
1149 WEEK_OF_FISCAL_YEAR,
1150 FIS_WEEK_NUMBER,
1151 FISCAL_WEEK_END_DATE,
1152 FISCAL_WEEK_ID,
1153 FISCAL_WEEK_NAME,
1154 WEEK_OF_FISCAL_QUARTER,
1155
1156
1157 FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1 AS DAY_START_DATE,
1158 FISCAL_WEEK_START_DATE + DAY_IN_WEEK - FISCAL_YEAR_START_DATE
1159 AS DAY_OF_FISCAL_YEAR,
1160 TO_NUMBER(To_Char((FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1),'J')) AS JULIAN_DATE,
1161 DAY_IN_WEEK AS DAY_OF_FISCAL_WEEK,
1162 1 AS DAY_TIME_SPAN,
1163 (FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1) AS DAY,
1164 MTH_445_PERIOD_CALENDAR_S.CURRVAL AS DAY_ID,
1165 TO_NUMBER (To_Char(FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1, 'YYYYMMDD')) AS DAY_DAY_CODE,
1166 To_Char(FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1, 'DD-MON-YYYY') AS DAY_DESCRIPTION,
1167 To_Char(FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1, 'DD-MON-YYYY') AS DAY_NAME,
1168 FISCAL_WEEK_START_DATE + DAY_IN_WEEK - 1 AS DAY_END_DATE,
1169 FISCAL_WEEK_START_DATE + DAY_IN_WEEK - FISCAL_QUARTER_START_DATE
1170 AS DAY_OF_FISCAL_QUARTER,
1171 FISCAL_WEEK_START_DATE + DAY_IN_WEEK - FISCAL_MONTH_START_DATE
1172 AS DAY_OF_FISCAL_MONTH,
1173 SYSDATE AS CREATION_DATE,
1174 SYSDATE AS LAST_UPDATE_DATE
1175
1176 FROM MTH_445_PERIOD_CALENDAR cal,
1177 (select LEVEL AS DAY_IN_WEEK from dual connect by LEVEL <= 7) days
1178 WHERE cal.DAY_ID IS NULL
1179 AND cal.FISCAL_WEEK_ID IS NOT NULL
1180 AND cal.FIS_YEAR_NUMBER >= P_START_YEAR;
1181
1182 mth_util_pkg.log_msg('Inserted ' || SQL%ROWCOUNT || ' rows.',mth_util_pkg.G_DBG_ROW_CNT);
1183 mth_util_pkg.log_msg('Inserting Day level Complete',mth_util_pkg.G_DBG_OTH);
1184 POPULATE_HOURS;
1185 mth_util_pkg.log_msg('Inserting Hour level Complete', mth_util_pkg.G_DBG_OTH);
1186 mth_util_pkg.log_msg('POPULATE_445_CAL_DENROM end', mth_util_pkg.G_DBG_PROC_FUN_END);
1187 COMMIT;
1188 EXCEPTION
1189 WHEN OTHERS THEN
1190 --Call logging API and then throw exception
1191 mth_util_pkg.log_msg('Exception OTHERS in POPULATE_445_CAL_DENROM', mth_util_pkg.G_DBG_EXCEPTION);
1192 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1193 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1194 END;
1195
1196
1197 /* ****************************************************************************
1198 * * Procedure :VALIDATE_CUSTOM_CAL_STG_DATA *
1199 * * Description :...
1200 * *
1201 * * File Name :MTHCALRB.PLS
1202 * *
1203 * * Visibility :Public
1204 * *
1205 * * Parameters :
1206 * *
1207 * ******************************************************************************/
1208 PROCEDURE VALIDATE_CUSTOM_CAL_STG_DATA(P_SESSION_NO IN VARCHAR) IS
1209
1210 V_VALIDATION_STATUS NUMBER;
1211 l_execution_id NUMBER;
1212 TYPE err_name IS TABLE OF MTH_YEAR_ERR.HIERARCHY_NAME%TYPE
1213 INDEX BY PLS_INTEGER;
1214 TYPE err_userattr IS TABLE OF MTH_YEAR_ERR.USER_ATTR1%TYPE
1215 INDEX BY PLS_INTEGER;
1216 TYPE err_usermeasure IS TABLE OF MTH_YEAR_ERR.USER_MEASURE1%TYPE
1217 INDEX BY PLS_INTEGER;
1218 TYPE err_date IS TABLE OF MTH_YEAR_ERR.START_DATE%TYPE
1219 INDEX BY PLS_INTEGER;
1220 TYPE err_code IS TABLE OF MTH_YEAR_ERR.ERR_CODE%TYPE
1221 INDEX BY PLS_INTEGER;
1222 TYPE err_sysfk IS TABLE OF MTH_YEAR_ERR.SYSTEM_FK%TYPE
1223 INDEX BY PLS_INTEGER;
1224 TYPE err_runid IS TABLE OF MTH_YEAR_ERR.ERR$$$_AUDIT_RUN_ID%TYPE
1225 INDEX BY PLS_INTEGER;
1226
1227 /*c_mth_hryerr_HIERARCHY_NAME err_name;
1228 c_mth_hryerr_CHILD_FK err_name;
1229 c_mth_hryerr_PARENT_FK err_name;
1230 c_mth_hryerr_LEVEL_OF_CHILD err_runid;
1231 c_mth_hryerr_SYSTEM_FK err_sysfk;
1232 c_mth_hryerr_RUN_ID err_runid;
1233 c_mth_hryerr_ERR_CODE err_code;*/
1234
1235 c_mth_dayerr_REPORT_DATE err_date;
1236 c_mth_dayerr_USER_ATTR1 err_userattr;
1237 c_mth_dayerr_USER_ATTR2 err_userattr;
1238 c_mth_dayerr_USER_ATTR3 err_userattr;
1239 c_mth_dayerr_USER_ATTR4 err_userattr;
1240 c_mth_dayerr_USER_ATTR5 err_userattr;
1241 c_mth_dayerr_USER_MEASURE1 err_usermeasure;
1242 c_mth_dayerr_USER_MEASURE2 err_usermeasure;
1243 c_mth_dayerr_USER_MEASURE3 err_usermeasure;
1244 c_mth_dayerr_USER_MEASURE4 err_usermeasure;
1245 c_mth_dayerr_USER_MEASURE5 err_usermeasure;
1246 c_mth_dayerr_SYSTEM_FK err_sysfk;
1247 c_mth_dayerr_ERR_CODE err_code;
1248 c_mth_dayerr_RUN_ID err_runid;
1249
1250 c_mth_weekerr_HIERARCHY_NAME err_name;
1251 c_mth_weekerr_NAME err_name;
1252 c_mth_weekerr_USER_ATTR1 err_userattr;
1253 c_mth_weekerr_USER_ATTR2 err_userattr;
1254 c_mth_weekerr_USER_ATTR3 err_userattr;
1255 c_mth_weekerr_USER_ATTR4 err_userattr;
1256 c_mth_weekerr_USER_ATTR5 err_userattr;
1257 c_mth_weekerr_USER_MEASURE1 err_usermeasure;
1258 c_mth_weekerr_USER_MEASURE2 err_usermeasure;
1259 c_mth_weekerr_USER_MEASURE3 err_usermeasure;
1263 c_mth_weekerr_END_DATE err_date;
1260 c_mth_weekerr_USER_MEASURE4 err_usermeasure;
1261 c_mth_weekerr_USER_MEASURE5 err_usermeasure;
1262 c_mth_weekerr_SYSTEM_FK err_sysfk;
1264 c_mth_weekerr_START_DATE err_date;
1265 c_mth_weekerr_ERR_CODE err_code;
1266 c_mth_weekerr_RUN_ID err_runid;
1267
1268 c_mth_pererr_HIERARCHY_NAME err_name;
1269 c_mth_pererr_NAME err_name;
1270 c_mth_pererr_USER_ATTR1 err_userattr;
1271 c_mth_pererr_USER_ATTR2 err_userattr;
1272 c_mth_pererr_USER_ATTR3 err_userattr;
1273 c_mth_pererr_USER_ATTR4 err_userattr;
1274 c_mth_pererr_USER_ATTR5 err_userattr;
1275 c_mth_pererr_USER_MEASURE1 err_usermeasure;
1276 c_mth_pererr_USER_MEASURE2 err_usermeasure;
1277 c_mth_pererr_USER_MEASURE3 err_usermeasure;
1278 c_mth_pererr_USER_MEASURE4 err_usermeasure;
1279 c_mth_pererr_USER_MEASURE5 err_usermeasure;
1280 c_mth_pererr_SYSTEM_FK err_sysfk;
1281 c_mth_pererr_END_DATE err_date;
1282 c_mth_pererr_START_DATE err_date;
1283 c_mth_pererr_ERR_CODE err_code;
1284 c_mth_pererr_RUN_ID err_runid;
1285
1286 c_mth_qerr_HIERARCHY_NAME err_name;
1287 c_mth_qerr_NAME err_name;
1288 c_mth_qerr_USER_ATTR1 err_userattr;
1289 c_mth_qerr_USER_ATTR2 err_userattr;
1290 c_mth_qerr_USER_ATTR3 err_userattr;
1291 c_mth_qerr_USER_ATTR4 err_userattr;
1292 c_mth_qerr_USER_ATTR5 err_userattr;
1293 c_mth_qerr_USER_MEASURE1 err_usermeasure;
1294 c_mth_qerr_USER_MEASURE2 err_usermeasure;
1295 c_mth_qerr_USER_MEASURE3 err_usermeasure;
1296 c_mth_qerr_USER_MEASURE4 err_usermeasure;
1297 c_mth_qerr_USER_MEASURE5 err_usermeasure;
1298 c_mth_qerr_SYSTEM_FK err_sysfk;
1299 c_mth_qerr_END_DATE err_date;
1300 c_mth_qerr_START_DATE err_date;
1301 c_mth_qerr_ERR_CODE err_code;
1302 c_mth_qerr_RUN_ID err_runid;
1303
1304 c_mth_yearerr_HIERARCHY_NAME err_name;
1305 c_mth_yearerr_NAME err_name;
1306 c_mth_yearerr_USER_ATTR1 err_userattr;
1307 c_mth_yearerr_USER_ATTR2 err_userattr;
1308 c_mth_yearerr_USER_ATTR3 err_userattr;
1309 c_mth_yearerr_USER_ATTR4 err_userattr;
1310 c_mth_yearerr_USER_ATTR5 err_userattr;
1311 c_mth_yearerr_USER_MEASURE1 err_usermeasure;
1312 c_mth_yearerr_USER_MEASURE2 err_usermeasure;
1313 c_mth_yearerr_USER_MEASURE3 err_usermeasure;
1314 c_mth_yearerr_USER_MEASURE4 err_usermeasure;
1315 c_mth_yearerr_USER_MEASURE5 err_usermeasure;
1316 c_mth_yearerr_SYSTEM_FK err_sysfk;
1317 c_mth_yearerr_END_DATE err_date;
1318 c_mth_yearerr_START_DATE err_date;
1319 c_mth_yearerr_ERR_CODE err_code;
1320 c_mth_yearerr_RUN_ID err_runid;
1321
1322 v_year_err NUMBER;
1323 v_quarter_err NUMBER;
1324 v_period_err NUMBER;
1325 v_week_err NUMBER;
1326 v_day_err NUMBER;
1327 v_time_hrcy_err NUMBER;
1328 VALIDATION_FAIL EXCEPTION;
1329
1330 v_year_d NUMBER;
1331
1332 BEGIN
1333 l_execution_id := P_SESSION_NO;
1334 --mth_util_pkg.initialize_debug('MTH:Custom Calendar Validation procedure', l_execution_id);
1335 mth_util_pkg.log_msg('VALIDATE_CUSTOM_CAL_STG_DATA start', mth_util_pkg.G_DBG_PROC_FUN_START);
1336 mth_util_pkg.log_msg('UserEnv(''sessionid'') in pls - ' || UserEnv('sessionid') , mth_util_pkg.G_DBG_VAR_VAL);
1337
1338
1339 mth_util_pkg.log_msg('P_SESSION_NO = ' || P_SESSION_NO , mth_util_pkg.G_DBG_MAIN_PARAM);
1340
1341 V_VALIDATION_STATUS := 0;
1342
1343 select count(*)
1344 into v_year_err
1345 from MTH_YEAR_ERR
1346 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
1347
1348 select count(*)
1349 into v_quarter_err
1350 from MTH_QUARTER_ERR
1351 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
1352
1353 select count(*)
1354 into v_period_err
1355 from MTH_PERIOD_ERR
1356 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
1357
1358 select count(*)
1359 into v_week_err
1360 from MTH_WEEK_ERR
1361 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
1362
1363 select count(*)
1364 into v_day_err
1365 from MTH_DAY_ERR
1366 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
1367
1368 select count(*)
1369 into v_time_hrcy_err
1370 from MTH_TIME_HIERARCHY_ERR
1371 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;
1372
1373 /*SELECT
1374 HIERARCHY_NAME,CHILD_FK,PARENT_FK,LEVEL_NUMBER_OF_CHILD,
1375 SYSTEM_FK,ERR$$$_AUDIT_RUN_ID,ERR_CODE
1376 BULK COLLECT INTO
1377 c_mth_hryerr_HIERARCHY_NAME,
1378 c_mth_hryerr_CHILD_FK,
1379 c_mth_hryerr_PARENT_FK,
1380 c_mth_hryerr_LEVEL_OF_CHILD,
1381 c_mth_hryerr_SYSTEM_FK,
1382 c_mth_hryerr_RUN_ID,
1383 c_mth_hryerr_ERR_CODE
1384
1385 FROM MTH_TIME_HIERARCHY_ERR
1386 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;*/
1387 /*WHERE LEVEL_FK_KEY IN(
1388 SELECT LEVEL_FK_KEY
1389 FROM(
1390 SELECT LEVEL_FK_KEY,Count(PARENT_FK_KEY) AS MULTIPLE_PARENTS
1391 FROM MTH_TIME_HIERARCHY
1392 GROUP BY LEVEL_FK_KEY)
1393 WHERE MULTIPLE <> 1);*/
1394
1395 --Collect Day Data into array
1396 SELECT REPORT_DATE, USER_ATTR1, USER_ATTR2,
1397 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1398 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1399 USER_MEASURE5, SYSTEM_FK, ERR_CODE,RUN_ID
1400 BULK COLLECT INTO
1401 c_mth_dayerr_REPORT_DATE,
1402 c_mth_dayerr_USER_ATTR1,
1403 c_mth_dayerr_USER_ATTR2,
1404 c_mth_dayerr_USER_ATTR3,
1405 c_mth_dayerr_USER_ATTR4,
1406 c_mth_dayerr_USER_ATTR5,
1407 c_mth_dayerr_USER_MEASURE1,
1408 c_mth_dayerr_USER_MEASURE2,
1409 c_mth_dayerr_USER_MEASURE3,
1410 c_mth_dayerr_USER_MEASURE4,
1411 c_mth_dayerr_USER_MEASURE5,
1412 c_mth_dayerr_SYSTEM_FK,
1413 c_mth_dayerr_ERR_CODE,
1414 c_mth_dayerr_RUN_ID
1415 FROM
1416 (SELECT REPORT_DATE, USER_ATTR1, USER_ATTR2,
1417 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1418 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1419 USER_MEASURE5, SYSTEM_FK,
1423 'GAP'
1420 CASE WHEN PREV_REPORT_DATE >= REPORT_DATE THEN
1421 'OVP'
1422 ELSE
1424 END ERR_CODE,
1425 e.RUN_ID
1426 FROM
1427 (SELECT Lag(REPORT_DATE)
1428 over (ORDER BY REPORT_DATE) PREV_REPORT_DATE,
1429 s.*,
1430 mss.SYSTEM_PK as SYSTEM_FK,
1431 P_SESSION_NO as RUN_ID
1432 FROM mth_day_d s,
1433 mth_systems_setup mss
1434 WHERE s.system_fk_key = mss.system_pk_key)e
1435 WHERE PREV_REPORT_DATE + 1 <> REPORT_DATE);
1436 /*UNION
1437 SELECT REPORT_DATE, USER_ATTR1, USER_ATTR2,
1438 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1439 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1440 USER_MEASURE5, SYSTEM_FK, ERR_CODE, P_SESSION_NO as RUN_ID
1441 FROM MTH_DAY_ERR
1442 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO;*/
1443
1444 -- Collect Week Data into array
1445 SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1446 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1447 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1448 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,RUN_ID
1449 BULK COLLECT INTO
1450 c_mth_weekerr_HIERARCHY_NAME,
1451 c_mth_weekerr_NAME,
1452 c_mth_weekerr_USER_ATTR1,
1453 c_mth_weekerr_USER_ATTR2,
1454 c_mth_weekerr_USER_ATTR3,
1455 c_mth_weekerr_USER_ATTR4,
1456 c_mth_weekerr_USER_ATTR5,
1457 c_mth_weekerr_USER_MEASURE1,
1458 c_mth_weekerr_USER_MEASURE2,
1459 c_mth_weekerr_USER_MEASURE3,
1460 c_mth_weekerr_USER_MEASURE4,
1461 c_mth_weekerr_USER_MEASURE5,
1462 c_mth_weekerr_SYSTEM_FK,
1463 c_mth_weekerr_END_DATE,
1464 c_mth_weekerr_START_DATE,
1465 c_mth_weekerr_ERR_CODE,
1466 c_mth_weekerr_RUN_ID
1467 FROM
1468 (SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1469 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1470 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1471 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE,
1472 CASE WHEN PREV_END_DATE >= START_DATE THEN
1473 'OVP'
1474 ELSE 'GAP'
1475 END ERR_CODE,
1476 e.RUN_ID
1477 FROM (SELECT Lag(START_DATE) over (PARTITION BY
1478 mdh.HIERARCHY_NAME ORDER BY START_DATE) PREV_START_DATE,
1479 Lag(END_DATE) over (PARTITION BY mdh.HIERARCHY_NAME
1480 ORDER BY START_DATE) PREV_END_DATE,
1481 s.*,
1482 mdh.HIERARCHY_NAME,
1483 mss.SYSTEM_PK as SYSTEM_FK,
1484 P_SESSION_NO as RUN_ID
1485 FROM mth_week_d s,
1486 mth_dim_hierarchy mdh,
1487 mth_systems_setup mss
1488 WHERE mdh.hierarchy_id = s.hierarchy_id
1489 and mdh.dimension_name = 'TIME'
1490 and s.system_fk_key = mss.system_pk_key)e
1491 WHERE START_DATE <> PREV_END_DATE+1);
1492 /*UNION
1493 SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1494 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1495 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1496 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,
1497 P_SESSION_NO as RUN_ID
1498 from MTH_WEEK_ERR
1499 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO);*/
1500
1501 -- Collect Period Data into array
1502 SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1503 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1504 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1505 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,RUN_ID
1506 BULK COLLECT INTO
1507 c_mth_pererr_HIERARCHY_NAME,
1508 c_mth_pererr_NAME,
1509 c_mth_pererr_USER_ATTR1,
1510 c_mth_pererr_USER_ATTR2,
1511 c_mth_pererr_USER_ATTR3,
1512 c_mth_pererr_USER_ATTR4,
1513 c_mth_pererr_USER_ATTR5,
1514 c_mth_pererr_USER_MEASURE1,
1515 c_mth_pererr_USER_MEASURE2,
1516 c_mth_pererr_USER_MEASURE3,
1517 c_mth_pererr_USER_MEASURE4,
1518 c_mth_pererr_USER_MEASURE5,
1519 c_mth_pererr_SYSTEM_FK,
1520 c_mth_pererr_END_DATE,
1521 c_mth_pererr_START_DATE,
1522 c_mth_pererr_ERR_CODE,
1523 c_mth_pererr_RUN_ID
1524 FROM
1525 (SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1526 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1527 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1528 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE,
1529 CASE WHEN PREV_END_DATE >= START_DATE THEN
1530 'OVP'
1531 ELSE 'GAP'
1532 END ERR_CODE,
1533 e.RUN_ID
1534 FROM (SELECT Lag(START_DATE) over (PARTITION BY
1535 mdh.HIERARCHY_NAME ORDER BY START_DATE) PREV_START_DATE,
1536 Lag(END_DATE) over (PARTITION BY mdh.HIERARCHY_NAME
1537 ORDER BY START_DATE) PREV_END_DATE,
1538 s.*,
1539 mdh.HIERARCHY_NAME,
1540 mss.SYSTEM_PK as SYSTEM_FK,
1541 P_SESSION_NO as RUN_ID
1542 FROM mth_period_d s,
1543 mth_dim_hierarchy mdh,
1544 mth_systems_setup mss
1545 WHERE mdh.hierarchy_id = s.hierarchy_id
1546 and mdh.dimension_name = 'TIME'
1547 and s.system_fk_key = mss.system_pk_key)e
1548 WHERE START_DATE <> PREV_END_DATE+1);
1549 /*UNION
1550 SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1551 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1552 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1553 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,
1554 P_SESSION_NO as RUN_ID
1555 from MTH_PERIOD_ERR
1556 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO);*/
1557
1558 -- Collect Quarter Data into array
1559 SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1560 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1561 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1562 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,RUN_ID
1563 BULK COLLECT INTO
1564 c_mth_qerr_HIERARCHY_NAME,
1565 c_mth_qerr_NAME,
1569 c_mth_qerr_USER_ATTR4,
1566 c_mth_qerr_USER_ATTR1,
1567 c_mth_qerr_USER_ATTR2,
1568 c_mth_qerr_USER_ATTR3,
1570 c_mth_qerr_USER_ATTR5,
1571 c_mth_qerr_USER_MEASURE1,
1572 c_mth_qerr_USER_MEASURE2,
1573 c_mth_qerr_USER_MEASURE3,
1574 c_mth_qerr_USER_MEASURE4,
1575 c_mth_qerr_USER_MEASURE5,
1576 c_mth_qerr_SYSTEM_FK,
1577 c_mth_qerr_END_DATE,
1578 c_mth_qerr_START_DATE,
1579 c_mth_qerr_ERR_CODE,
1580 c_mth_qerr_RUN_ID
1581 FROM
1582 (SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1583 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1584 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1585 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE,
1586 CASE WHEN PREV_END_DATE >= START_DATE THEN
1587 'OVP'
1588 ELSE 'GAP'
1589 END ERR_CODE,
1590 e.RUN_ID
1591 FROM (SELECT Lag(START_DATE) over (PARTITION BY
1592 mdh.HIERARCHY_NAME ORDER BY START_DATE) PREV_START_DATE,
1593 Lag(END_DATE) over (PARTITION BY mdh.HIERARCHY_NAME
1594 ORDER BY START_DATE) PREV_END_DATE,
1595 s.*,
1596 mdh.HIERARCHY_NAME,
1597 mss.SYSTEM_PK as SYSTEM_FK,
1598 P_SESSION_NO as RUN_ID
1599 FROM mth_quarter_d s,
1600 mth_dim_hierarchy mdh,
1601 mth_systems_setup mss
1602 WHERE mdh.hierarchy_id = s.hierarchy_id
1603 and mdh.dimension_name = 'TIME'
1604 and s.system_fk_key = mss.system_pk_key)e
1605 WHERE START_DATE <> PREV_END_DATE+1);
1606 /*UNION
1607 SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1608 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1609 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1610 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,
1611 P_SESSION_NO as RUN_ID
1612 from MTH_QUARTER_ERR
1613 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO);*/
1614
1615 -- Collect Year Data into array
1616 SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1617 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1618 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1619 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,RUN_ID
1620 BULK COLLECT INTO
1621 c_mth_yearerr_HIERARCHY_NAME,
1622 c_mth_yearerr_NAME,
1623 c_mth_yearerr_USER_ATTR1,
1624 c_mth_yearerr_USER_ATTR2,
1625 c_mth_yearerr_USER_ATTR3,
1626 c_mth_yearerr_USER_ATTR4,
1627 c_mth_yearerr_USER_ATTR5,
1628 c_mth_yearerr_USER_MEASURE1,
1629 c_mth_yearerr_USER_MEASURE2,
1630 c_mth_yearerr_USER_MEASURE3,
1631 c_mth_yearerr_USER_MEASURE4,
1632 c_mth_yearerr_USER_MEASURE5,
1633 c_mth_yearerr_SYSTEM_FK,
1634 c_mth_yearerr_END_DATE,
1635 c_mth_yearerr_START_DATE,
1636 c_mth_yearerr_ERR_CODE,
1637 c_mth_yearerr_RUN_ID
1638 FROM
1639 (SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1640 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1641 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1642 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE,
1643 CASE WHEN PREV_END_DATE >= START_DATE THEN
1644 'OVP'
1645 ELSE 'GAP'
1646 END ERR_CODE,
1647 e.RUN_ID
1648 FROM (SELECT Lag(START_DATE) over (PARTITION BY
1649 mdh.HIERARCHY_NAME ORDER BY START_DATE) PREV_START_DATE,
1650 Lag(END_DATE) over (PARTITION BY mdh.HIERARCHY_NAME
1651 ORDER BY START_DATE) PREV_END_DATE,
1652 s.*,
1653 mdh.HIERARCHY_NAME,
1654 mss.SYSTEM_PK as SYSTEM_FK,
1655 P_SESSION_NO as RUN_ID
1656 FROM mth_year_d s,
1657 mth_dim_hierarchy mdh,
1658 mth_systems_setup mss
1659 WHERE mdh.hierarchy_id = s.hierarchy_id
1660 and mdh.dimension_name = 'TIME'
1661 and s.system_fk_key = mss.system_pk_key)e
1662 WHERE START_DATE <> PREV_END_DATE+1);
1663 /*UNION
1664 SELECT HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1665 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1666 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1667 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,
1668 P_SESSION_NO as RUN_ID
1669 from MTH_YEAR_ERR
1670 WHERE ERR$$$_AUDIT_RUN_ID = P_SESSION_NO);*/
1671
1672 IF c_mth_dayerr_REPORT_DATE.COUNT > 0 OR c_mth_weekerr_NAME.COUNT > 0 OR c_mth_pererr_NAME.COUNT > 0 OR c_mth_qerr_NAME.COUNT > 0 OR c_mth_yearerr_NAME.COUNT >0 THEN
1673 V_VALIDATION_STATUS := -1;
1674 END IF;
1675
1676 IF v_week_err > 0 OR v_period_err > 0 OR v_quarter_err > 0 OR v_year_err > 0 OR v_day_err > 0 OR v_time_hrcy_err > 0 THEN
1677 V_VALIDATION_STATUS := -1;
1678 END IF;
1679
1680 mth_util_pkg.log_msg('c_mth_weekerr_NAME.COUNT = ' || c_mth_weekerr_NAME.COUNT , mth_util_pkg.G_DBG_ROW_CNT);
1681 mth_util_pkg.log_msg('c_mth_pererr_NAME.COUNT = ' || c_mth_pererr_NAME.COUNT , mth_util_pkg.G_DBG_ROW_CNT);
1682 mth_util_pkg.log_msg('c_mth_qerr_NAME.COUNT = ' || c_mth_qerr_NAME.COUNT , mth_util_pkg.G_DBG_ROW_CNT);
1683 mth_util_pkg.log_msg('c_mth_yearerr_NAME.COUNT = ' || c_mth_yearerr_NAME.COUNT , mth_util_pkg.G_DBG_ROW_CNT);
1684 mth_util_pkg.log_msg('c_mth_dayerr_REPORT_DATE.COUNT = ' || c_mth_dayerr_REPORT_DATE.COUNT , mth_util_pkg.G_DBG_ROW_CNT);
1685
1686 mth_util_pkg.log_msg('V_VALIDATION_STATUS after checks before rollback = ' || V_VALIDATION_STATUS , mth_util_pkg.G_DBG_ROW_CNT);
1687
1688 IF V_VALIDATION_STATUS = -1 THEN
1689 --Rollback the conversion process that populated data from
1690 -- staging to fact
1691 ROLLBACK;
1692
1693 select count(*)
1694 into v_year_err
1695 from MTH_YEAR_D;
1696
1697 select count(*)
1698 into v_quarter_err
1699 from MTH_QUARTER_D;
1700
1701 select count(*)
1702 into v_period_err
1703 from MTH_PERIOD_D;
1704
1705 select count(*)
1706 into v_week_err
1707 from MTH_WEEK_D;
1708
1709 select count(*)
1713 select count(*)
1710 into v_day_err
1711 from MTH_DAY_D;
1712
1714 into v_time_hrcy_err
1715 from MTH_TIME_HIERARCHY;
1716
1717
1718 mth_util_pkg.log_msg('After Rollback v_year_d = ' || v_year_err , mth_util_pkg.G_DBG_ROW_CNT);
1719 mth_util_pkg.log_msg('After Rollback v_quarter_d = ' || v_quarter_err , mth_util_pkg.G_DBG_ROW_CNT);
1720 mth_util_pkg.log_msg('After Rollback v_period_d = ' || v_period_err , mth_util_pkg.G_DBG_ROW_CNT);
1721 mth_util_pkg.log_msg('After Rollback v_week_d = ' || v_week_err , mth_util_pkg.G_DBG_ROW_CNT);
1722 mth_util_pkg.log_msg('After Rollback v_day_d = ' || v_day_err , mth_util_pkg.G_DBG_ROW_CNT);
1723 mth_util_pkg.log_msg('After Rollback v_time_hrcy_d = ' || v_time_hrcy_err , mth_util_pkg.G_DBG_ROW_CNT);
1724
1725 FORALL i IN 1..c_mth_dayerr_REPORT_DATE.COUNT
1726 INSERT INTO MTH_DAY_ERR(
1727 REPORT_DATE, USER_ATTR1, USER_ATTR2,
1728 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1729 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1730 USER_MEASURE5, SYSTEM_FK, ERR_CODE,ERR$$$_AUDIT_RUN_ID)
1731 VALUES
1732 (c_mth_dayerr_REPORT_DATE(i),
1733 c_mth_dayerr_USER_ATTR1(i),
1734 c_mth_dayerr_USER_ATTR2(i),
1735 c_mth_dayerr_USER_ATTR3(i),
1736 c_mth_dayerr_USER_ATTR4(i),
1737 c_mth_dayerr_USER_ATTR5(i),
1738 c_mth_dayerr_USER_MEASURE1(i),
1739 c_mth_dayerr_USER_MEASURE2(i),
1740 c_mth_dayerr_USER_MEASURE3(i),
1741 c_mth_dayerr_USER_MEASURE4(i),
1742 c_mth_dayerr_USER_MEASURE5(i),
1743 c_mth_dayerr_SYSTEM_FK(i),
1744 c_mth_dayerr_ERR_CODE(i),
1745 c_mth_dayerr_RUN_ID(i));
1746
1747 mth_util_pkg.log_msg('c_mth_dayerr_REPORT_DATE.COUNT = ' || c_mth_dayerr_REPORT_DATE.COUNT , mth_util_pkg.G_DBG_ROW_CNT);
1748
1749 FORALL i IN 1..c_mth_weekerr_NAME.COUNT
1750 INSERT INTO MTH_WEEK_ERR(
1751 HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1752 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1753 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1754 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,ERR$$$_AUDIT_RUN_ID)
1755 VALUES
1756 (c_mth_weekerr_HIERARCHY_NAME(i),
1757 c_mth_weekerr_NAME(i),
1758 c_mth_weekerr_USER_ATTR1(i),
1759 c_mth_weekerr_USER_ATTR2(i),
1760 c_mth_weekerr_USER_ATTR3(i),
1761 c_mth_weekerr_USER_ATTR4(i),
1762 c_mth_weekerr_USER_ATTR5(i),
1763 c_mth_weekerr_USER_MEASURE1(i),
1764 c_mth_weekerr_USER_MEASURE2(i),
1765 c_mth_weekerr_USER_MEASURE3(i),
1766 c_mth_weekerr_USER_MEASURE4(i),
1767 c_mth_weekerr_USER_MEASURE5(i),
1768 c_mth_weekerr_SYSTEM_FK(i),
1769 c_mth_weekerr_END_DATE(i),
1770 c_mth_weekerr_START_DATE(i),
1771 c_mth_weekerr_ERR_CODE(i),
1772 c_mth_weekerr_RUN_ID(i));
1773
1774 mth_util_pkg.log_msg('c_mth_weekerr_NAME.COUNT = ' || c_mth_weekerr_NAME.COUNT , mth_util_pkg.G_DBG_ROW_CNT);
1775
1776 FORALL i IN 1..c_mth_pererr_NAME.COUNT
1777 INSERT INTO MTH_PERIOD_ERR(
1778 HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1779 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1780 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1781 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,ERR$$$_AUDIT_RUN_ID)
1782 VALUES
1783 (c_mth_pererr_HIERARCHY_NAME(i),
1784 c_mth_pererr_NAME(i),
1785 c_mth_pererr_USER_ATTR1(i),
1786 c_mth_pererr_USER_ATTR2(i),
1787 c_mth_pererr_USER_ATTR3(i),
1788 c_mth_pererr_USER_ATTR4(i),
1789 c_mth_pererr_USER_ATTR5(i),
1790 c_mth_pererr_USER_MEASURE1(i),
1791 c_mth_pererr_USER_MEASURE2(i),
1792 c_mth_pererr_USER_MEASURE3(i),
1793 c_mth_pererr_USER_MEASURE4(i),
1794 c_mth_pererr_USER_MEASURE5(i),
1795 c_mth_pererr_SYSTEM_FK(i),
1796 c_mth_pererr_END_DATE(i),
1797 c_mth_pererr_START_DATE(i),
1798 c_mth_pererr_ERR_CODE(i),
1799 c_mth_pererr_RUN_ID(i));
1800
1801 mth_util_pkg.log_msg('c_mth_pererr_NAME.COUNT = ' || c_mth_pererr_NAME.COUNT , mth_util_pkg.G_DBG_ROW_CNT);
1802
1803 FORALL i IN 1..c_mth_qerr_NAME.COUNT
1804 INSERT INTO MTH_QUARTER_ERR(
1805 HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1806 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1807 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1808 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,ERR$$$_AUDIT_RUN_ID)
1809 VALUES
1810 (c_mth_qerr_HIERARCHY_NAME(i),
1811 c_mth_qerr_NAME(i),
1812 c_mth_qerr_USER_ATTR1(i),
1813 c_mth_qerr_USER_ATTR2(i),
1814 c_mth_qerr_USER_ATTR3(i),
1815 c_mth_qerr_USER_ATTR4(i),
1816 c_mth_qerr_USER_ATTR5(i),
1817 c_mth_qerr_USER_MEASURE1(i),
1818 c_mth_qerr_USER_MEASURE2(i),
1819 c_mth_qerr_USER_MEASURE3(i),
1820 c_mth_qerr_USER_MEASURE4(i),
1821 c_mth_qerr_USER_MEASURE5(i),
1822 c_mth_qerr_SYSTEM_FK(i),
1823 c_mth_qerr_END_DATE(i),
1824 c_mth_qerr_START_DATE(i),
1825 c_mth_qerr_ERR_CODE(i),
1826 c_mth_qerr_RUN_ID(i));
1827
1828 mth_util_pkg.log_msg('c_mth_qerr_NAME.COUNT = ' || c_mth_qerr_NAME.COUNT , mth_util_pkg.G_DBG_ROW_CNT);
1829 FORALL i IN 1..c_mth_yearerr_NAME.COUNT
1830 INSERT INTO MTH_YEAR_ERR(
1831 HIERARCHY_NAME, NAME, USER_ATTR1, USER_ATTR2,
1832 USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1,
1833 USER_MEASURE2, USER_MEASURE3,USER_MEASURE4,
1834 USER_MEASURE5, SYSTEM_FK, END_DATE, START_DATE, ERR_CODE,ERR$$$_AUDIT_RUN_ID)
1835 VALUES
1836 (c_mth_yearerr_HIERARCHY_NAME(i),
1837 c_mth_yearerr_NAME(i),
1838 c_mth_yearerr_USER_ATTR1(i),
1839 c_mth_yearerr_USER_ATTR2(i),
1840 c_mth_yearerr_USER_ATTR3(i),
1841 c_mth_yearerr_USER_ATTR4(i),
1842 c_mth_yearerr_USER_ATTR5(i),
1843 c_mth_yearerr_USER_MEASURE1(i),
1844 c_mth_yearerr_USER_MEASURE2(i),
1845 c_mth_yearerr_USER_MEASURE3(i),
1846 c_mth_yearerr_USER_MEASURE4(i),
1847 c_mth_yearerr_USER_MEASURE5(i),
1848 c_mth_yearerr_SYSTEM_FK(i),
1849 c_mth_yearerr_END_DATE(i),
1850 c_mth_yearerr_START_DATE(i),
1851 c_mth_yearerr_ERR_CODE(i),
1852 c_mth_yearerr_RUN_ID(i));
1853
1854 mth_util_pkg.log_msg('c_mth_yearerr_NAME.COUNT = ' || c_mth_yearerr_NAME.COUNT , mth_util_pkg.G_DBG_ROW_CNT);
1855
1856 /*FORALL i in 1..c_mth_hryerr_CHILD_FK.COUNT
1857 INSERT INTO MTH_TIME_HIERARCHY_ERR(
1858 HIERARCHY_NAME,CHILD_FK,PARENT_FK,LEVEL_NUMBER_OF_CHILD,
1859 SYSTEM_FK,ERR_CODE,ERR$$$_AUDIT_RUN_ID)
1860 VALUES
1861 (c_mth_hryerr_HIERARCHY_NAME(i),
1862 c_mth_hryerr_CHILD_FK(i),
1863 c_mth_hryerr_PARENT_FK(i),
1864 c_mth_hryerr_LEVEL_OF_CHILD(i),
1865 c_mth_hryerr_SYSTEM_FK(i),
1866 c_mth_hryerr_ERR_CODE(i),
1867 c_mth_hryerr_RUN_ID(i));
1868
1869 mth_util_pkg.log_msg('c_mth_hryerr_CHILD_FK.COUNT = ' || c_mth_hryerr_CHILD_FK.COUNT , mth_util_pkg.G_DBG_ROW_CNT);*/
1870
1871 mth_util_pkg.log_msg('Before deleting Staging = ' || P_SESSION_NO , mth_util_pkg.G_DBG_ROW_CNT);
1872
1873 DELETE FROM MTH_DAY_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_DAY_STG');
1874 DELETE FROM MTH_WEEK_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_WEEK_STG');
1875 DELETE FROM MTH_PERIOD_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_PERIOD_STG');
1876 DELETE FROM MTH_QUARTER_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_QUARTER_STG');
1877 DELETE FROM MTH_YEAR_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_YEAR_STG');
1878 DELETE FROM MTH_TIME_HIERARCHY_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_TIME_HIERARCHY_STG');
1879 mth_util_pkg.log_msg('Processing Flag = ' || mth_util_pkg.Get_Processing_Flag() , mth_util_pkg.G_DBG_ROW_CNT);
1880 mth_util_pkg.log_msg('After deleting Staging = ' || P_SESSION_NO , mth_util_pkg.G_DBG_ROW_CNT);
1881
1882 COMMIT;
1883 RAISE VALIDATION_FAIL;
1884
1885 END IF;
1886
1887 mth_util_pkg.log_msg('Before deleting Staging = ' || P_SESSION_NO , mth_util_pkg.G_DBG_ROW_CNT);
1888
1889 DELETE FROM MTH_DAY_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_DAY_STG');
1890 DELETE FROM MTH_WEEK_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_WEEK_STG');
1891 DELETE FROM MTH_PERIOD_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_PERIOD_STG');
1892 DELETE FROM MTH_QUARTER_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_QUARTER_STG');
1893 DELETE FROM MTH_YEAR_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_YEAR_STG');
1894 DELETE FROM MTH_TIME_HIERARCHY_STG where PROCESSING_FLAG = mth_util_pkg.Get_Processing_Flag('MTH_TIME_HIERARCHY_STG');
1895 --mth_util_pkg.log_msg('Processing Flag = ' || mth_util_pkg.Get_Processing_Flag() , mth_util_pkg.G_DBG_ROW_CNT);
1896 mth_util_pkg.log_msg('After deleting Staging = ' || P_SESSION_NO , mth_util_pkg.G_DBG_ROW_CNT);
1897
1898 mth_util_pkg.log_msg('VALIDATE_CUSTOM_CAL_STG_DATA start', mth_util_pkg.G_DBG_PROC_FUN_END);
1899 POPULATE_HOURS;
1900 COMMIT;
1901
1902 EXCEPTION
1903 WHEN OTHERS THEN
1904 --Call logging API and then throw exception
1905 mth_util_pkg.log_msg('Exception OTHERS in VALIDATE_CUSTOM_CAL_STG_DATA', mth_util_pkg.G_DBG_EXCEPTION);
1906 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
1907 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
1908 RAISE VALIDATION_FAIL;
1909 END VALIDATE_CUSTOM_CAL_STG_DATA;
1910
1911 END MTH_CALENDAR_PKG;