[Home] [Help]
PACKAGE BODY: APPS.HRI_TIME_C
Source
1 PACKAGE BODY HRI_TIME_C AS
2 /*$Header: HRICMT1B.pls 120.0 2011/11/18 10:15:05 vepravee noship $*/
3
4 g_schema varchar2(30);
5 g_period_set_name varchar2(15) := null;
6 g_period_type varchar2(15) := null;
7 g_week_start_day varchar2(30) := null;
8 g_phase varchar2(500);
9 g_week_offset number;
10 g_user_id number := FND_GLOBAL.User_Id;
11 g_login_id number := FND_GLOBAL.Login_Id;
12 g_all_level varchar2(1);
13 g_date_not_defined date;
14 g_global_start_date date;
15 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('HRI_DEBUG_MODE'), 'N');
16 --Adding for sba content
17 g_load_mode varchar2(10);
18
19 -- Bug 5624487
20 g_unassigned_day date := to_date('12/31/4712', 'MM/DD/YYYY');
21 g_una_ent_period_year number := to_number(to_char(g_unassigned_day,'yyyy'));
22 g_una_ent_quarter_num number := 1;
23 g_una_ent_period_num number := 1;
24
25 G_TABLE_NOT_EXIST EXCEPTION;
26 G_LOGIN_INFO_NOT_FOUND EXCEPTION;
27 G_BIS_PARAMETER_NOT_SETUP EXCEPTION;
28 G_ENT_CALENDAR_NOT_FOUND EXCEPTION;
29 G_YEAR_NOT_DEFINED EXCEPTION;
30
31 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
32
33 g_day_row_cnt number := 0;
34
35 ---------------------------------------------------
36 -- Forward declarations of provide procedures
37 ---------------------------------------------------
38 PROCEDURE LOAD_TIME_RPT_STRUCT;
39
40 ---------------------------------------------------
41 -- PRIVATE FUNCTION get_week_offset
42 ---------------------------------------------------
43 function get_week_offset(p_week_start_day varchar2) return number is
44
45 l_week_offset number;
46
47 begin
48
49 /*case p_week_start_day
50 when '2' then l_week_offset := 0;
51 when '3' then l_week_offset := 1;
52 when '4' then l_week_offset := 2;
53 when '5' then l_week_offset := 3;
54 when '6' then l_week_offset := -3;
55 when '7' then l_week_offset := -2;
56 when '1' then l_week_offset := -1;
57 end case;*/
58
59 if p_week_start_day = '2' then
60 l_week_offset := 0;
61 elsif p_week_start_day = '3' then
62 l_week_offset := 1;
63 elsif p_week_start_day = '4' then
64 l_week_offset := 2;
65 elsif p_week_start_day = '5' then
66 l_week_offset := 3;
67 elsif p_week_start_day = '6' then
68 l_week_offset := -3;
69 elsif p_week_start_day = '7' then
70 l_week_offset := -2;
71 elsif p_week_start_day = '1' then
72 l_week_offset := -1;
73 end if;
74
75 return l_week_offset;
76
77 end get_week_offset;
78
79 ---------------------------------------------------
80 -- PRIVATE FUNCTION get_week_num
81 ---------------------------------------------------
82 function get_week_num(p_date date, p_week_offset number) return number is
83
84 l_week_num number;
85
86 begin
87
88 l_week_num := to_char(p_date-p_week_offset,'iw');
89 return l_week_num;
90
91 end get_week_num;
92
93 ---------------------------------------------------
94 -- PRIVATE FUNCTION get_period_num
95 ---------------------------------------------------
96 function get_period_num(week_num number) return number is
97
98 l_period_num number;
99
100 begin
101
102 if week_num in (1,2,3,4) then
103 l_period_num := 1;
104 elsif week_num in (5,6,7,8) then
105 l_period_num := 2;
106 elsif week_num in (9,10,11,12,13) then
107 l_period_num := 3;
108 elsif week_num in (14,15,16,17) then
109 l_period_num := 4;
110 elsif week_num in (18,19,20,21) then
111 l_period_num := 5;
112 elsif week_num in (22,23,24,25,26) then
113 l_period_num := 6;
114 elsif week_num in (27,28,29,30) then
115 l_period_num := 7;
116 elsif week_num in (31,32,33,34) then
117 l_period_num := 8;
118 elsif week_num in (35,36,37,38,39) then
119 l_period_num := 9;
120 elsif week_num in (40,41,42,43) then
121 l_period_num := 10;
122 elsif week_num in (44,45,46,47) then
123 l_period_num := 11;
124 else
125 l_period_num := 12;
126 end if;
127 return l_period_num;
128
129 end get_period_num;
130
131 ---------------------------------------------------
132 -- PRIVATE FUNCTION get_period_start
133 ---------------------------------------------------
134 function get_period_start(p_date date) return date is
135
136 l_week_start date;
137 l_period_start date;
138 l_week_num number;
139 l_week_sequence number;
140 l_period_num number;
141
142 begin
143
144 l_week_start := trunc(p_date-g_week_offset,'iw')+g_week_offset;
145 l_week_num := get_week_num(l_week_start,g_week_offset);
146 l_period_num := get_period_num(l_week_num);
147
148 if l_week_num in (1,5,9,14,18,22,27,31,35,40,44,48) then
149 l_week_sequence := 0;
150 elsif l_week_num in (2,6,10,15,19,23,28,32,36,41,45,49) then
151 l_week_sequence := 1;
152 elsif l_week_num in (3,7,11,16,20,24,29,33,37,42,46,50) then
153 l_week_sequence := 2;
154 elsif l_week_num in (4,8,12,17,21,25,30,34,38,43,47,51) then
155 l_week_sequence := 3;
156 else
157 l_week_sequence := 4;
158 end if;
159
160 l_period_start := l_week_start-l_week_sequence*7;
161 return l_period_start;
162
163 end get_period_start;
164
165 ----------------------------------------------------------------
166 -- PRIVATE function check_validated
167 -- This function check that the HRI_TIME_* tables have
168 -- been validated.
169 ----------------------------------------------------------------
170 FUNCTION CHECK_VALIDATED
171 return varchar2
172 is
173 l_att_tbl DBMS_SQL.VARCHAR2_TABLE;
174 l_att_cnt number;
175 l_validated varchar2(1) := 'N';
176
177 begin
178
179 BIS_COLLECTION_UTILITIES.GET_LAST_USER_ATTRIBUTES
180 ( P_OBJECT_NAME => 'HRI_DBI_TIME_M'
181 , P_ATTRIBUTE_TABLE => l_att_tbl
182 , P_COUNT => l_att_cnt
183 );
184
185 if l_att_cnt > 0 then
186 if l_att_tbl(1) = 'Y' then
187 l_validated := 'Y';
188 end if;
189 end if;
190
191 return l_validated;
192
193 end CHECK_VALIDATED;
194
195 ---------------------------------------------------
196 -- PRIVATE FUNCTION period_updated
197 ---------------------------------------------------
198 FUNCTION period_updated(p_from_date in date, p_to_date in date) return varchar2 is
199
200 l_updated varchar2(1);
201
202 begin
203
204 insert into HRI_TIME_GL_PERIODS
205 (ent_period_id,
206 ent_qtr_id,
207 ent_year_id,
208 sequence,
209 name,
210 start_date,
211 end_date,
212 creation_date,
213 last_update_date,
214 last_updated_by,
215 created_by,
216 last_update_login)
217 select to_number(period_year||quarter_num||decode(length(period_num),1,'0'||period_num, period_num)),
218 to_number(period_year||quarter_num),
219 to_number(period_year),
220 period_num,
221 period_name,
222 start_date,
223 end_date,
224 sysdate,
225 sysdate,
226 g_user_id,
227 g_user_id,
228 g_login_id
229 from gl_periods
230 where period_set_name = g_period_set_name
231 and period_type = g_period_type
232 and adjustment_period_flag='N'
233 and start_date <= p_to_date
234 and end_date >= p_from_date;
235
236 -- Bug 4995016. Sequence should not be used for diffing after fix of bug 3961336.
237 -- Bug 4966868: Changed not to count rows
238 BEGIN
239 -- Bug 5624487
240 select 'Y'
241 into l_updated
242 from
243 (select ent_period_id,
244 ent_qtr_id,
245 ent_year_id,
246 --sequence,
247 name,
248 start_date,
249 end_date
250 from hri_time_ent_period
251 where end_date < g_unassigned_day
252 minus
253 select ent_period_id,
254 ent_qtr_id,
255 ent_year_id,
256 --sequence,
257 name,
258 start_date,
259 end_date
260 from HRI_TIME_GL_PERIODS)
261 where rownum = 1;
262 EXCEPTION
263 WHEN NO_DATA_FOUND THEN
264 l_updated := 'N';
265 END;
266
267 return l_updated;
268
269 end period_updated;
270
271 ---------------------------------------------------
272 -- PRIVATE PROCEDURE GATHER_TABLE_STATS
273 ---------------------------------------------------
274 procedure gather_table_stats
275 ( p_table_name in varchar2
276 , p_schema_name in varchar := g_schema
277 ) is
278
279 begin
280
281 fnd_stats.gather_table_stats( ownname => p_schema_name
282 , tabname => p_table_name
283 );
284
285 end gather_table_stats;
286
287 ---------------------------------------------------
288 -- PRIVATE PROCEDURE TRUNCATE_TABLE
289 ---------------------------------------------------
290 procedure truncate_table (p_table_name in varchar2) is
291 l_stmt varchar2(400);
292
293 begin
294
295 l_stmt := 'truncate table '||g_schema||'.'||p_table_name;
296 if g_debug_flag = 'Y' then
297 hri_util.put_line('TRUNCATE_TABLE : '||l_stmt);
298 end if;
299 execute immediate l_stmt;
300
301 exception
302 WHEN G_TABLE_NOT_EXIST THEN
303 null; -- Oracle 942, table does not exist, no actions
304 WHEN OTHERS THEN
305 raise;
306
307 end truncate_table;
308
309 ---------------------------------------------------
310 -- PRIVATE PROCEDURE INIT
311 ---------------------------------------------------
312 PROCEDURE INIT IS
313 l_status VARCHAR2(30);
314 l_industry VARCHAR2(30);
315 l_period_type VARCHAR2(15);
316
317 begin
318
319 -- ----------------------
320 -- Initialize the global variables
321 -- ----------------------
322
323
324 IF(FND_INSTALLATION.GET_APP_INFO('HRI', l_status, l_industry,
325 g_schema)) THEN
326 NULL;
327 END IF;
328
329 --g_user_id := FND_GLOBAL.User_Id;
330 --g_login_id := FND_GLOBAL.Login_Id;
331
332 IF (g_user_id IS NULL OR g_login_id IS NULL) THEN
333 RAISE G_LOGIN_INFO_NOT_FOUND;
334 END IF;
335
336 if (g_all_level = 'Y') then
337 g_period_set_name := bis_common_parameters.get_period_set_name;
338 g_period_type := bis_common_parameters.get_period_type;
339 if g_debug_flag = 'Y' then
340 hri_util.put_line('INIT : '||'Enterprise Calendar = '||g_period_set_name||' ('||g_period_type||')');
341 end if;
342 g_week_start_day := bis_common_parameters.get_start_day_of_week_id;
343 if (g_period_set_name is null or g_period_type is null or g_week_start_day is null) then
344 raise G_BIS_PARAMETER_NOT_SETUP;
345 end if;
346
347 if g_debug_flag = 'Y' then
348 hri_util.put_line('INIT : '||'Week Start Day = '||g_week_start_day);
349 end if;
350 g_week_offset := get_week_offset(g_week_start_day);
351 if g_debug_flag = 'Y' then
352 hri_util.put_line('INIT : '||'Week offset = '||g_week_offset);
353 hri_util.put_line(' ');
354 end if;
355
356 g_global_start_date := bis_common_parameters.get_GLOBAL_START_DATE;
357 if (g_global_start_date is null) then
358 raise G_BIS_PARAMETER_NOT_SETUP;
359 end if;
360 if g_debug_flag = 'Y' then
361 hri_util.put_line('INIT : '||'Global Start Date = ' ||
362 fnd_date.date_to_displaydate(g_global_start_date));
363 hri_util.put_line(' ');
364 end if;
365 end if;
366
367 end INIT;
368
369 ---------------------------------------------------
370 -- PRIVATE PROCEDURE LOAD_DAY_INC
371 -- >> Load Day Level Incrementally
372 ---------------------------------------------------
373 PROCEDURE LOAD_DAY_INC (p_from_date in date, p_to_date in date) IS
374
375 -- ---------------------------------------------------------
376 -- Define local variables
377 -- ---------------------------------------------------------
378 l_from_date date;
379 l_to_date date;
380 l_day date;
381 l_week_num number;
382 l_p445_num number;
383 l_year_num number;
384 l_day_row number;
385 l_period_year gl_periods.period_year%TYPE;
386 l_quarter_num gl_periods.quarter_num%TYPE;
387 l_period_num gl_periods.period_num%TYPE;
388 l_start_date gl_periods.start_date%TYPE;
389 l_end_date gl_periods.end_date%TYPE;
390 l_quarter_start_date gl_periods.quarter_start_date%TYPE;
391 l_quarter_end_date gl_periods.quarter_start_date%TYPE;
392 l_year_start_date gl_periods.year_start_date%TYPE;
393 l_year_end_date gl_periods.year_start_date%TYPE;
394 l_count number;
395
396 cursor ent_period_cur (day date) is
397 select period_year, quarter_num, period_num, start_date, end_date, quarter_start_date, year_start_date
398 from gl_periods
399 where adjustment_period_flag='N'
400 and period_set_name=g_period_set_name
401 and period_type=g_period_type
402 and day between start_date and end_date;
403
404 begin
405
406 -- ---------------------------------------------------------
407 -- Variable initialization
408 -- ---------------------------------------------------------
409 l_from_date := p_from_date;
410 l_to_date := p_to_date;
411 l_day := l_from_date;
412 l_day_row := 0;
413
414 -- ----------------------
415 -- Populate Day Level
416 -- ----------------------
417 -- Bug 5624487
418 -- while l_day <= l_to_date loop
419 if l_day > l_to_date then
420 l_day := g_unassigned_day;
421 end if;
422 while l_day <= l_to_date or l_day = g_unassigned_day loop
423
424 -- Bug 5624487
425 -- if (g_all_level='Y') then
426 if (g_all_level='Y' and l_day <> g_unassigned_day) then
427
428 open ent_period_cur(l_day);
429 fetch ent_period_cur into l_period_year, l_quarter_num, l_period_num,
430 l_start_date, l_end_date, l_quarter_start_date, l_year_start_date;
431 if (ent_period_cur%notfound) then
432 g_date_not_defined := l_day;
433
434 raise G_ENT_CALENDAR_NOT_FOUND;
435 else
436 l_week_num := get_week_num(l_day,g_week_offset);
437 l_p445_num := get_period_num(l_week_num);
438 l_year_num := to_char(l_day-g_week_offset,'iyyy');
439
440 select max(end_date) into l_quarter_end_date
441 from gl_periods
442 where period_set_name=g_period_set_name
443 and period_type=g_period_type
444 and adjustment_period_flag='N'
445 and period_year=l_period_year
446 and quarter_num=l_quarter_num;
447
448 select max(end_date) into l_year_end_date
449 from gl_periods
450 where period_set_name=g_period_set_name
451 and period_type=g_period_type
452 and adjustment_period_flag='N'
453 and period_year=l_period_year;
454 end if;
455 else
456 l_period_year := -1;
457 l_quarter_num := null;
458 l_period_num := null;
459 l_start_date := trunc(sysdate);
460 l_end_date := trunc(sysdate);
461 l_quarter_start_date := trunc(sysdate);
462 l_quarter_end_date := trunc(sysdate);
463 l_year_start_date := trunc(sysdate);
464 l_year_end_date := trunc(sysdate);
465 l_week_num := null;
466 l_p445_num := null;
467 l_year_num := -1;
468 end if;
469
470 -- first check if the current day is loaded
471 -- Bug 4966868: Changed not to count rows
472 BEGIN
473 select 1 into l_count
474 from hri_time_day
475 where report_date = trunc(l_day)
476 and rownum = 1;
477 EXCEPTION
478 WHEN NO_DATA_FOUND THEN
479 l_count := 0;
480 END;
481
482 -- do an incremental update/insert
483 if l_count = 0 then -- new record, insert
484
485 insert into hri_time_day
486 (report_date,
487 report_date_julian,
488 start_date,
489 end_date,
490 month_id,
491 ent_period_id,
492 ent_period_start_date,
493 ent_period_end_date,
494 ent_qtr_id,
495 ent_qtr_start_date,
496 ent_qtr_end_date,
497 ent_year_id,
498 ent_year_start_date,
499 ent_year_end_date,
500 week_id,
501 week_start_date,
502 week_end_date,
503 creation_date,
504 last_update_date,
505 last_updated_by,
506 created_by,
507 last_update_login)
508 values(
509 trunc(l_day),
510 to_char(l_day,'j'),
511 l_day,
512 l_day,
513 to_number(to_char(l_day,'yyyyqmm')),
514 l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
515 -- lpad(l_period_num,2,'0'), bug 3370185
516 l_start_date,
517 l_end_date,
518 l_period_year||l_quarter_num,
519 l_quarter_start_date,
520 l_quarter_end_date,
521 l_period_year,
522 l_year_start_date,
523 l_year_end_date,
524 l_year_num||lpad(l_p445_num,2,'0')||lpad(l_week_num,2,'0'),
525 nvl(trunc(l_day-g_week_offset,'iw')+g_week_offset,trunc(sysdate)),
526 nvl(trunc(l_day-g_week_offset,'iw')+g_week_offset,trunc(sysdate))+6, --week end date,
527 sysdate,
528 sysdate,
529 g_user_id,
530 g_user_id,
531 g_login_id
532 );
533
534 l_day_row := l_day_row+1;
535
536 else -- the day has been loaded, update those changed records only
537
538 update hri_time_day
539 set
540 ent_period_id = l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
541 -- lpad(l_period_num,2,'0'), bug 3370185
542 ent_period_start_date = l_start_date,
543 ent_period_end_date = l_end_date,
544 ent_qtr_id = l_period_year||l_quarter_num,
545 ent_qtr_start_date = l_quarter_start_date,
546 ent_qtr_end_date = l_quarter_end_date,
547 ent_year_id = l_period_year,
548 ent_year_start_date = l_year_start_date,
549 ent_year_end_date = l_year_end_date,
550 week_id = l_year_num||lpad(l_p445_num,2,'0')||lpad(l_week_num,2,'0'),
551 week_start_date = nvl(trunc(l_day-g_week_offset,'iw')+g_week_offset,trunc(sysdate)),
552 week_end_date = nvl(trunc(l_day-g_week_offset,'iw')+g_week_offset,trunc(sysdate))+6,
553 last_update_date = sysdate,
554 last_updated_by = g_user_id,
555 last_update_login = g_login_id
556 where report_date = trunc (l_day)
557 and (ent_period_id <> l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num)
558 -- lpad(l_period_num,2,'0') bug 3370185
559 or
560 ent_period_start_date <> l_start_date or
561 ent_period_end_date <> l_end_date or
562 NVL(ent_qtr_start_date, to_date('01/01/1000', 'DD/MM/YYYY')) <>
563 NVL(l_quarter_start_date, to_date('01/01/1000', 'DD/MM/YYYY')) or
564 NVL(ent_qtr_end_date, to_date('01/01/1000', 'DD/MM/YYYY')) <>
565 NVL(l_quarter_end_date, to_date('01/01/1000', 'DD/MM/YYYY')) or
566 NVL(ent_year_start_date, to_date('01/01/1000', 'DD/MM/YYYY')) <>
567 NVL(l_year_start_date, to_date('01/01/1000', 'DD/MM/YYYY')) or
568 NVL(ent_year_end_date, to_date('01/01/1000', 'DD/MM/YYYY')) <>
569 NVL(l_year_end_date, to_date('01/01/1000', 'DD/MM/YYYY')));
570
571 l_day_row := l_day_row + sql%rowcount;
572
573 end if; --for: if l_count = 0
574
575 -- Bug 5624487
576 -- if (g_all_level='Y') then
577 if (g_all_level='Y' and l_day <> g_unassigned_day) then
578
579 close ent_period_cur;
580 end if;
581
582 l_period_year := null;
583 l_quarter_num := null;
584 l_period_num := null;
585 l_start_date := null;
586 l_end_date := null;
587 l_quarter_start_date := null;
588 l_quarter_end_date := null;
589 l_year_start_date := null;
590 l_year_end_date := null;
591
592 -- move to the next day
593 -- Bug 5624487
594 -- l_day := l_day+1;
595 exit when l_day = g_unassigned_day;
596 if l_day < l_to_date then
597 l_day := l_day + 1;
598 else
599 l_day := g_unassigned_day;
600 end if;
601
602 end loop;
603
604 commit;
605
606 if g_debug_flag = 'Y' then
607 hri_util.put_line('LOAD_DAY_INC : '||to_char(l_day_row)||' records has been populated or updated to Day Level');
608 end if;
609
610 g_day_row_cnt := l_day_row;
611
612 end LOAD_DAY_INC;
613
614 ---------------------------------------------------
615 -- PRIVATE PROCEDURE LOAD_DAY
616 -- this procedure is no longer used
617 ---------------------------------------------------
618 /*
619 PROCEDURE LOAD_DAY(p_from_date in date, p_to_date in date) IS
620
621 -- ---------------------------------------------------------
622 -- Define local variables
623 -- ---------------------------------------------------------
624 l_from_date date;
625 l_to_date date;
626 l_day date;
627 l_week_num number;
628 l_p445_num number;
629 l_year_num number;
630 l_day_row number;
631 l_period_year gl_periods.period_year%TYPE;
632 l_quarter_num gl_periods.quarter_num%TYPE;
633 l_period_num gl_periods.period_num%TYPE;
634 l_start_date gl_periods.start_date%TYPE;
635 l_quarter_start_date gl_periods.quarter_start_date%TYPE;
636 l_year_start_date gl_periods.year_start_date%TYPE;
637
638 cursor ent_period_cur (day date) is
639 select period_year, quarter_num, period_num, start_date, quarter_start_date, year_start_date
640 from gl_periods
641 where adjustment_period_flag='N'
642 and period_set_name=g_period_set_name
643 and period_type=g_period_type
644 and day between start_date and end_date;
645
646 begin
647
648 truncate_table('HRI_TIME_DAY');
649
650 -- ---------------------------------------------------------
651 -- Variable initialization
652 -- ---------------------------------------------------------
653 l_from_date := p_from_date;
654 l_to_date := p_to_date;
655 l_day := l_from_date;
656 l_day_row := 0;
657
658 -- ----------------------
659 -- Populate Day Level
660 -- ----------------------
661 while l_day <= l_to_date loop
662
663 open ent_period_cur(l_day);
664 fetch ent_period_cur into l_period_year, l_quarter_num, l_period_num, l_start_date, l_quarter_start_date, l_year_start_date;
665
666 if ent_period_cur%notfound then
667 raise G_ENT_CALENDAR_NOT_FOUND;
668 else
669 l_week_num := get_week_num(l_day,g_week_offset);
670 l_p445_num := get_period_num(l_week_num);
671 l_year_num := to_char(l_day-g_week_offset,'iyyy');
672 insert into hri_time_day
673 (report_date,
674 report_date_julian,
675 start_date,
676 end_date,
677 month_id,
678 ent_period_id,
679 ent_period_start_date,
680 ent_qtr_id,
681 ent_qtr_start_date,
682 ent_year_id,
683 ent_year_start_date,
684 week_id,
685 week_start_date,
686 creation_date,
687 last_update_date,
688 last_updated_by,
689 created_by,
690 last_update_login)
691 values(
692 trunc(l_day),
693 to_char(l_day,'j'),
694 l_day,
695 l_day,
696 to_number(to_char(l_day,'yyyyqmm')),
697 l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
698 -- lpad(l_period_num,2,'0'), bug 3370185
699 l_start_date,
700 l_period_year||l_quarter_num,
701 l_quarter_start_date,
702 l_period_year,
703 l_year_start_date,
704 l_year_num||lpad(l_p445_num,2,'0')||lpad(l_week_num,2,'0'),
705 trunc(l_day-g_week_offset,'iw')+g_week_offset,
706 sysdate,
707 sysdate,
708 g_user_id,
709 g_user_id,
710 g_login_id
711 );
712
713 l_day_row := l_day_row+1;
714
715 end if;
716
717 close ent_period_cur;
718 l_period_year := null;
719 l_quarter_num := null;
720 l_period_num := null;
721 l_start_date := null;
722 l_quarter_start_date := null;
723 l_year_start_date := null;
724 l_day := l_day+1;
725
726 end loop;
727
728 commit;
729
730 if g_debug_flag = 'Y' then
731 hri_util.put_line('LOAD_DAY : '||to_char(l_day_row)||' records has been populated to Day Level');
732 end if;
733
734 end LOAD_DAY;
735 */
736
737 ---------------------------------------------------
738 -- PRIVATE PROCEDURE LOAD_MONTH
739 ---------------------------------------------------
740 PROCEDURE LOAD_MONTH(p_from_date in date, p_to_date in date) IS
741
742 -- ---------------------------------------------------------
743 -- Define local variables
744 -- ---------------------------------------------------------
745 l_from_date date;
746 l_to_date date;
747 l_month date;
748 l_month_end date;
749 l_month_row number;
750 l_min_date date;
751
752 begin
753
754 -- ---------------------------------------------------------
755 -- Variable initialization
756 -- ---------------------------------------------------------
757 l_from_date := p_from_date;
758 l_to_date := p_to_date;
759 l_month := trunc(l_from_date,'mm');
760 l_month_end := last_day(l_month);
761 l_month_row := 0;
762
763 IF g_load_mode = 'INIT' THEN
764 --If it is initial load the first year should not be populated for the prior_year_month_id column
765 --Used a decode for the same
766 l_min_date := p_from_date;
767 ELSE
768 -- Incremental run
769 select min(start_date) into l_min_date from hri_time_month;
770 END IF;
771
772 -- Bug 5624487
773 -- delete from HRI_TIME_MONTH where start_date <= l_to_date and end_date >= l_from_date;
774 delete from HRI_TIME_MONTH
775 where start_date <= l_to_date and end_date >= l_from_date
776 or end_date >= g_unassigned_day;
777
778 -- ----------------------
779 -- Populate Month Level
780 -- ----------------------
781
782 -- Bug 5624487
783 -- while l_month <= l_to_date loop
784 if l_month > l_to_date then
785 l_month := trunc(g_unassigned_day,'mm');
786 l_month_end := g_unassigned_day;
787 end if;
788 while l_month <= l_to_date or l_month_end = g_unassigned_day loop
789
790 -- Bug 5624487
791 insert into HRI_time_month
792 (month_id,
793 quarter_id,
794 name,
795 start_date,
796 end_date,
797 prior_year_month_id,
798 creation_date,
799 last_update_date,
800 last_updated_by,
801 created_by,
802 last_update_login)
803 values
804 (
805 to_number(to_char(l_month,'yyyyqmm')),
806 to_number(to_char(l_month,'yyyyq')),
807 decode(l_month_end, g_unassigned_day, null,
808 to_char(l_month,'Mon YYYY')
809 ),
810 l_month,
811 l_month_end,
812 decode(l_month_end, g_unassigned_day, null,
813 decode(to_char (l_min_date, 'YYYY'), to_char (l_month, 'YYYY'), NULL, to_number(to_char(add_months(l_month, -12),'yyyyqmm')))
814 ),
815 sysdate,
816 sysdate,
817 g_user_id,
818 g_user_id,
819 g_login_id
820 );
821
822 -- Bug 5624487
823 /*
824 l_month := l_month_end+1;
825 l_month_end := last_day(l_month);
826 l_month_row := l_month_row+1;
827 */
828 l_month_row := l_month_row+1;
829 exit when l_month_end = g_unassigned_day;
830 l_month := l_month_end+1;
831 l_month_end := last_day(l_month);
832 if l_month > l_to_date then
833 l_month := trunc(g_unassigned_day,'mm');
834 l_month_end := g_unassigned_day;
835 end if;
836
837 end loop;
838 commit;
839
840 if g_debug_Flag = 'Y' then
841 HRI_util.put_line('LOAD_MONTH : '||to_char(l_month_row)||' records has been populated to Month Level');
842 end if;
843
844 end LOAD_MONTH;
845
846 ---------------------------------------------------
847 -- PRIVATE PROCEDURE LOAD_QUARTER
848 ---------------------------------------------------
849 PROCEDURE LOAD_QUARTER(p_from_date in date, p_to_date in date) IS
850
851 -- ---------------------------------------------------------
852 -- Define local variables
853 -- ---------------------------------------------------------
854 l_from_date date;
855 l_to_date date;
856 l_qtr date;
857 l_qtr_end date;
858 l_qtr_row number;
859 l_min_date date;
860
861 begin
862
863 -- ---------------------------------------------------------
864 -- Variable initialization
865 -- ---------------------------------------------------------
866 l_from_date := p_from_date;
867 l_to_date := p_to_date;
868 l_qtr := trunc(l_from_date,'q');
869 l_qtr_end := add_months(last_day(l_qtr),2);
870 l_qtr_row := 0;
871
872 IF g_load_mode = 'INIT' THEN
873 --If it is initial load the first year should not be populated for the prior_year_month_id column
874 --Used a decode for the same
875 l_min_date := p_from_date;
876 ELSE
877 -- Incremental run
878 select min(start_date) into l_min_date from HRI_time_qtr;
879 END IF;
880
881 -- Bug 5624487
882 -- delete from HRI_TIME_QTR where start_date <= l_to_date and end_date >= l_from_date;
883 delete from HRI_TIME_QTR
887 -- ----------------------
884 where start_date <= l_to_date and end_date >= l_from_date
885 or end_date >= g_unassigned_day;
886
888 -- Populate Quarter Level
889 -- ----------------------
890 -- Bug 5624487
891 -- while l_qtr <= l_to_date loop
892 if l_qtr > l_to_date then
893 l_qtr := trunc(g_unassigned_day,'q');
894 l_qtr_end := g_unassigned_day;
895 end if;
896 while l_qtr <= l_to_date or l_qtr_end = g_unassigned_day loop
897
898 -- Bug 5624487
899 insert into HRI_time_qtr
900 (quarter_id,
901 year_id,
902 name,
903 start_date,
904 end_date,
905 prior_year_quarter_id,
906 creation_date,
907 last_update_date,
908 last_updated_by,
909 created_by,
910 last_update_login)
911 values(
912 to_number(to_char(l_qtr,'yyyyq')),
913 to_number(to_char(l_qtr,'yyyy')),
914 decode(l_qtr_end, g_unassigned_day, null,
915 to_char(l_qtr,'q,yyyy')
916 ),
917 l_qtr,
918 l_qtr_end,
919 decode(l_qtr_end, g_unassigned_day, null,
920 decode(to_char (l_min_date, 'YYYY'), to_char (l_qtr, 'YYYY'), NULL, to_number(to_char(add_months(l_qtr, -12),'yyyyq')))
921 ),
922 sysdate,
923 sysdate,
924 g_user_id,
925 g_user_id,
926 g_login_id
927 );
928
929 -- Bug 5624487
930 /*
931 l_qtr := l_qtr_end+1;
932 l_qtr_end := add_months(last_day(l_qtr),2);
933 l_qtr_row := l_qtr_row+1;
934 */
935 l_qtr_row := l_qtr_row+1;
936 exit when l_qtr_end = g_unassigned_day;
937 l_qtr := l_qtr_end+1;
938 l_qtr_end := add_months(last_day(l_qtr),2);
939 if l_qtr > l_to_date then
940 l_qtr := trunc(g_unassigned_day,'q');
941 l_qtr_end := g_unassigned_day;
942 end if;
943
944 end loop;
945 commit;
946
947 if g_debug_flag = 'Y' then
948 HRI_util.put_line('LOAD_QUARTER : '||to_char(l_qtr_row)||' records has been populated to Quarter Level');
949 end if;
950
951 end LOAD_QUARTER;
952
953 ---------------------------------------------------
954 -- PRIVATE PROCEDURE LOAD_YEAR
955 ---------------------------------------------------
956 PROCEDURE LOAD_YEAR(p_from_date in date, p_to_date in date) IS
957
958 -- ---------------------------------------------------------
959 -- Define local variables
960 -- ---------------------------------------------------------
961 l_from_date date;
962 l_to_date date;
963 l_year date;
964 l_year_end date;
965 l_year_row number;
966 l_min_date date;
967
968 begin
969
970 -- ---------------------------------------------------------
971 -- Variable initialization
972 -- ---------------------------------------------------------
973 l_from_date := p_from_date;
974 l_to_date := p_to_date;
975 l_year := trunc(l_from_date,'yyyy');
976 l_year_end := add_months(last_day(l_year),11);
977 l_year_row := 0;
978
979 IF g_load_mode = 'INIT' THEN
980 --If it is initial load the first year should not be populated for the prior_year_month_id column
981 --Used a decode for the same
982 l_min_date := p_from_date;
983 ELSE
984 -- Incremental run
985 select min(start_date) into l_min_date from HRI_time_year;
986 END IF;
987
988 -- Bug 5624487
989 -- delete from HRI_TIME_YEAR where start_date <= l_to_date and end_date >= l_from_date;
990 delete from HRI_TIME_YEAR
991 where start_date <= l_to_date and end_date >= l_from_date
992 or end_date >= g_unassigned_day;
993
994 -- ----------------------
995 -- Populate Year Level
996 -- ----------------------
997 -- Bug 5624487
998 -- while l_year <= l_to_date loop
999 if l_year > l_to_date then
1000 l_year := trunc(g_unassigned_day,'yyyy');
1001 l_year_end := g_unassigned_day;
1002 end if;
1003 while l_year <= l_to_date or l_year_end = g_unassigned_day loop
1004
1005 -- Bug 5624487
1006 insert into HRI_time_year
1007 (year_id,
1008 name,
1009 start_date,
1010 end_date,
1011 prior_year_id,
1012 creation_date,
1013 last_update_date,
1014 last_updated_by,
1015 created_by,
1016 last_update_login)
1017 values(
1018 to_number(to_char(l_year,'yyyy')),
1019 decode(l_year_end, g_unassigned_day, null,
1020 to_number(to_char(l_year,'yyyy'))
1021 ),
1022 l_year,
1023 l_year_end,
1024 decode(l_year_end, g_unassigned_day, null,
1025 decode(to_char (l_min_date, 'YYYY'), to_char (l_year, 'YYYY'), NULL, to_number(to_char(add_months(l_year, -12),'yyyy')))
1026 ),
1027 sysdate,
1028 sysdate,
1029 g_user_id,
1030 g_user_id,
1031 g_login_id
1032 );
1033
1034 -- Bug 5624487
1035 /*
1036 l_year := l_year_end+1;
1037 l_year_end := add_months(last_day(l_year),11);
1038 l_year_row := l_year_row+1;
1039 */
1040 l_year_row := l_year_row+1;
1041 exit when l_year_end = g_unassigned_day;
1042 l_year := l_year_end+1;
1043 l_year_end := add_months(last_day(l_year),11);
1044 if l_year > l_to_date then
1045 l_year := trunc(g_unassigned_day,'yyyy');
1046 l_year_end := g_unassigned_day;
1047 end if;
1051
1048
1049 end loop;
1050 commit;
1052 if g_debug_flag = 'Y' then
1053 HRI_util.put_line('LOAD_YEAR : '||to_char(l_year_row)||' records has been populated to Year Level');
1054 end if;
1055
1056 end LOAD_YEAR;
1057
1058 ---------------------------------------------------
1059 -- PRIVATE PROCEDURE LOAD_WEEK
1060 ---------------------------------------------------
1061 PROCEDURE LOAD_WEEK(p_from_date in date, p_to_date in date) IS
1062
1063 -- ---------------------------------------------------------
1064 -- Define local variables
1065 -- ---------------------------------------------------------
1066 l_from_date date;
1067 l_to_date date;
1068 l_week date;
1069 l_week_end date;
1070 l_week_num number;
1071 l_period_num number;
1072 l_year_num number;
1073 l_week_row number;
1074
1075 begin
1076
1077 -- ---------------------------------------------------------
1078 -- Variable initialization
1079 -- ---------------------------------------------------------
1080 l_from_date := p_from_date;
1081 l_to_date := trunc(p_to_date-g_week_offset,'iw')+g_week_offset+6;
1082 l_week := trunc(l_from_date-g_week_offset,'iw')+g_week_offset;
1083 l_week_end := l_week+6;
1084
1085 -- Bug 5624487
1086 if l_week > l_to_date then
1087 l_week := trunc(g_unassigned_day-g_week_offset,'iw')+g_week_offset;
1088 l_week_end := g_unassigned_day;
1089 end if;
1090
1091 l_week_num := get_week_num(l_week,g_week_offset);
1092 l_period_num := get_period_num(l_week_num);
1093 l_year_num := to_char(l_week-g_week_offset,'iyyy');
1094 l_week_row := 0;
1095
1096 -- Bug 5624487
1097 -- delete from HRI_TIME_WEEK where start_date <= l_to_date and end_date >= l_from_date;
1098 delete from HRI_TIME_WEEK
1099 where start_date <= l_to_date and end_date >= l_from_date
1100 or end_date >= g_unassigned_day;
1101
1102 -- ----------------------
1103 -- Populate Week Level
1104 -- ----------------------
1105
1106 -- Bug 5624487
1107 -- while l_week <= l_to_date loop
1108 while l_week <= l_to_date or l_week_end = g_unassigned_day loop
1109
1110 -- Bug 5624487
1111 insert into HRI_time_week
1112 (week_id,
1113 period445_id,
1114 sequence,
1115 name,
1116 start_date,
1117 end_date,
1118 creation_date,
1119 last_update_date,
1120 last_updated_by,
1121 created_by,
1122 last_update_login)
1123 values
1124 (
1125 l_year_num||lpad(l_period_num,2,'0')||lpad(l_week_num,2,'0'),
1126 l_year_num||lpad(l_period_num,2,'0'),
1127 l_week_num,
1128 decode(l_week_end, g_unassigned_day, null,
1129 to_char(l_week_end,'dd-Mon-rr')
1130 ),
1131 l_week,
1132 l_week_end,
1133 sysdate,
1134 sysdate,
1135 g_user_id,
1136 g_user_id,
1137 g_login_id
1138 );
1139
1140 -- Bug 5624487
1141 /*
1142 l_week := l_week_end+1;
1143 l_week_end := l_week+6;
1144 l_week_num := get_week_num(l_week,g_week_offset);
1145 l_period_num := get_period_num(l_week_num);
1146 l_year_num := to_char(l_week-g_week_offset,'iyyy');
1147 l_week_row := l_week_row+1;
1148 */
1149 l_week_row := l_week_row+1;
1150 exit when l_week_end = g_unassigned_day;
1151 l_week := l_week_end+1;
1152 l_week_end := l_week+6;
1153 if l_week > l_to_date then
1154 l_week := trunc(g_unassigned_day-g_week_offset,'iw')+g_week_offset;
1155 l_week_end := g_unassigned_day;
1156 end if;
1157 l_week_num := get_week_num(l_week,g_week_offset);
1158 l_period_num := get_period_num(l_week_num);
1159 l_year_num := to_char(l_week-g_week_offset,'iyyy');
1160
1161 end loop;
1162
1163 commit;
1164 if g_debug_flag = 'Y' then
1165 HRI_util.put_line('LOAD_WEEK : '||to_char(l_week_row)||' records has been populated to Week Level');
1166 end if;
1167
1168 -- Bug 5624487
1169 update HRI_time_day
1170 set week_id = l_year_num||lpad(l_period_num,2,'0')||lpad(l_week_num,2,'0'),
1171 week_start_date = l_week,
1172 week_end_date = l_week_end,
1173 last_update_date = sysdate,
1174 last_updated_by = g_user_id,
1175 last_update_login = g_login_id
1176 where report_date = g_unassigned_day;
1177
1178 commit;
1179
1180 end LOAD_WEEK;
1181
1182 ---------------------------------------------------
1183 -- PRIVATE PROCEDURE LOAD_PERIOD_445
1184 ---------------------------------------------------
1185 PROCEDURE LOAD_PERIOD_445(p_from_date in date, p_to_date in date) IS
1186
1187 -- ---------------------------------------------------------
1188 -- Define local variables
1189 -- ---------------------------------------------------------
1190 l_from_date date;
1191 l_to_date date;
1192 l_period date;
1193 l_period_end date;
1194 l_period_num number;
1195 l_year_num number;
1196 l_period_row number;
1197
1198 begin
1199
1200 -- ---------------------------------------------------------
1201 -- Variable initialization
1202 -- ---------------------------------------------------------
1203 l_from_date := p_from_date;
1204 l_to_date := trunc(p_to_date-g_week_offset,'iw')+g_week_offset+6;
1205 l_period := get_period_start(l_from_date);
1206 l_period_num := get_period_num(get_week_num(l_period,g_week_offset));
1207 if l_period_num in (3,6,9,12) then
1208 l_period_end := l_period+34;
1209 if l_period_num=12 then
1210 while get_period_num(get_week_num(l_period_end+7,g_week_offset)) = 12 loop
1211 l_period_end := l_period_end + 7;
1212 end loop;
1213 end if;
1214 else
1215 l_period_end := l_period+27;
1216 end if;
1217
1218 -- Bug 5624487
1219 if l_period > l_to_date then
1220 l_period := get_period_start(g_unassigned_day);
1221 l_period_end := g_unassigned_day;
1222 l_period_num := get_period_num(get_week_num(l_period,g_week_offset));
1223 end if;
1224
1225 l_year_num := to_char(l_period-g_week_offset,'iyyy');
1226 l_period_row := 0;
1227
1228 -- Bug 5624487
1229 -- delete from HRI_TIME_P445 where start_date <= l_to_date and end_date >= l_from_date;
1230 delete from HRI_TIME_P445
1231 where start_date <= l_to_date and end_date >= l_from_date
1232 or end_date >= g_unassigned_day;
1233
1234 -- ----------------------
1235 -- Populate Period 445 Level
1236 -- ----------------------
1237
1238 -- Bug 5624487
1239 -- while l_period <= l_to_date loop
1240 while l_period <= l_to_date or l_period_end = g_unassigned_day loop
1241
1242 -- Bug 5624487
1243 insert into HRI_time_p445
1244 (period445_id,
1245 year445_id,
1246 sequence,
1247 name,
1248 start_date,
1249 end_date,
1250 creation_date,
1251 last_update_date,
1252 last_updated_by,
1253 created_by,
1254 last_update_login)
1255 values
1256 (
1257 l_year_num||lpad(l_period_num,2,'0'),
1258 l_year_num,
1259 l_period_num,
1260 decode(l_period_end, g_unassigned_day, null,
1261 lpad(l_period_num,2,'0')||' '||l_year_num
1262 ),
1263 l_period,
1264 l_period_end,
1265 sysdate,
1266 sysdate,
1267 g_user_id,
1268 g_user_id,
1269 g_login_id
1270 );
1271
1272 -- Bug 5624487
1273 /*
1274 l_period := l_period_end+1;
1275 l_period_num := get_period_num(get_week_num(l_period,g_week_offset));
1276
1277 if l_period_num in (3,6,9,12) then
1278 l_period_end := l_period+34;
1279 if l_period_num=12 then
1280 while get_period_num(get_week_num(l_period_end+7,g_week_offset)) = 12 loop
1281 l_period_end := l_period_end + 7;
1282 end loop;
1283 end if;
1284 else
1285 l_period_end := l_period+27;
1286 end if;
1287
1288 l_year_num := to_char(l_period-g_week_offset,'iyyy');
1289 l_period_row := l_period_row+1;
1290 */
1291 l_period_row := l_period_row+1;
1292 exit when l_period_end = g_unassigned_day;
1293
1294 l_period := l_period_end+1;
1295 l_period_num := get_period_num(get_week_num(l_period,g_week_offset));
1296
1297 if l_period_num in (3,6,9,12) then
1298 l_period_end := l_period+34;
1299 if l_period_num=12 then
1300 while get_period_num(get_week_num(l_period_end+7,g_week_offset)) = 12 loop
1301 l_period_end := l_period_end + 7;
1302 end loop;
1303 end if;
1304 else
1305 l_period_end := l_period+27;
1306 end if;
1307
1308 if l_period > l_to_date then
1309 l_period := get_period_start(g_unassigned_day);
1310 l_period_end := g_unassigned_day;
1311 l_period_num := get_period_num(get_week_num(l_period,g_week_offset));
1312 end if;
1313
1314 l_year_num := to_char(l_period-g_week_offset,'iyyy');
1315
1316 end loop;
1317
1318 commit;
1319
1320 if g_debug_flag = 'Y' then
1321 HRI_util.put_line('LOAD_PERIOD_445 : '||to_char(l_period_row)||' records has been populated to Period 445 Level');
1322 end if;
1323
1324 end LOAD_PERIOD_445;
1325
1326 ---------------------------------------------------
1327 -- PRIVATE PROCEDURE LOAD_YEAR_445
1328 ---------------------------------------------------
1329 PROCEDURE LOAD_YEAR_445(p_from_date in date, p_to_date in date) IS
1330
1331 -- ---------------------------------------------------------
1332 -- Define local variables
1333 -- ---------------------------------------------------------
1334 l_from_date date;
1335 l_to_date date;
1336 l_year date;
1337 l_year_end date;
1338 l_year_num number;
1339 l_year_row number;
1340
1341 begin
1342
1343 -- ---------------------------------------------------------
1344 -- Variable initialization
1345 -- ---------------------------------------------------------
1346 l_from_date := trunc(p_from_date-g_week_offset,'iw')+g_week_offset;
1347 l_to_date := trunc(p_to_date,'iw')+6;
1348 --l_year := trunc(l_from_date,'iyyy')+g_week_offset;
1349 l_year := l_from_date;
1350 l_year_num := to_char(l_year-g_week_offset,'iyyy');
1351 l_year_end := l_year+6;
1352
1353 while to_char(l_year_end+7-g_week_offset,'iyyy') = l_year_num loop
1354 l_year_end := l_year_end+7;
1355 end loop;
1356
1357 -- Bug 5624487
1358 if l_year > l_to_date then
1359 l_year := trunc(g_unassigned_day-g_week_offset,'iw')+g_week_offset;
1360 l_year_end := g_unassigned_day;
1361 l_year_num := to_char(l_year-g_week_offset,'iyyy');
1362 end if;
1363
1364 while to_char(l_year-7-g_week_offset,'iyyy') = l_year_num loop
1365 l_year := l_year-7;
1366 end loop;
1367
1368 l_year_row := 0;
1369
1370 -- Bug 5624487
1371 -- delete from HRI_TIME_YEAR445 where start_date <= l_to_date and end_date >= l_from_date;
1372 delete from HRI_TIME_YEAR445
1373 where start_date <= l_to_date and end_date >= l_from_date
1374 or end_date >= g_unassigned_day;
1375
1376 -- ----------------------
1377 -- Populate Year 445 Level
1378 -- ----------------------
1379
1380 -- Bug 5624487
1381 -- while l_year <= l_to_date loop
1382 while l_year <= l_to_date or l_year_end = g_unassigned_day loop
1383
1384 -- Bug 5624487
1385 insert into HRI_time_year445
1386 (year445_id,
1387 name,
1388 start_date,
1389 end_date,
1390 creation_date,
1391 last_update_date,
1392 last_updated_by,
1393 created_by,
1394 last_update_login)
1395 values(
1396 l_year_num,
1397 decode(l_year_end, g_unassigned_day, null,
1398 l_year_num
1399 ),
1400 l_year,
1401 l_year_end,
1402 sysdate,
1403 sysdate,
1404 g_user_id,
1405 g_user_id,
1406 g_login_id
1407 );
1408
1409 -- Bug 5624487
1410 /*
1411 l_year := l_year_end+1;
1412 l_year_num := to_char(l_year-g_week_offset,'iyyy');
1413 l_year_end := l_year+6;
1414
1415 while to_char(l_year_end+7-g_week_offset,'iyyy') = l_year_num loop
1416 l_year_end := l_year_end+7;
1417 end loop;
1418
1419 l_year_row := l_year_row+1;
1420 */
1421 l_year_row := l_year_row+1;
1422 exit when l_year_end = g_unassigned_day;
1423
1424 l_year := l_year_end+1;
1425 l_year_num := to_char(l_year-g_week_offset,'iyyy');
1426 l_year_end := l_year+6;
1427
1428 while to_char(l_year_end+7-g_week_offset,'iyyy') = l_year_num loop
1429 l_year_end := l_year_end+7;
1430 end loop;
1431
1432 if l_year > l_to_date then
1433 l_year := trunc(g_unassigned_day-g_week_offset,'iw')+g_week_offset;
1434 l_year_end := g_unassigned_day;
1435 l_year_num := to_char(l_year-g_week_offset,'iyyy');
1436 end if;
1437
1438 while to_char(l_year-7-g_week_offset,'iyyy') = l_year_num loop
1439 l_year := l_year-7;
1440 end loop;
1441
1442 end loop;
1443
1444 commit;
1445
1446 if g_debug_flag = 'Y' then
1447 HRI_util.put_line('LOAD_YEAR_445 : '||to_char(l_year_row)||' records has been populated to Year 445 Level');
1448 end if;
1449
1450 end LOAD_YEAR_445;
1451
1452 ---------------------------------------------------
1453 -- PRIVATE PROCEDURE LOAD_ENT_PERIOD
1454 ---------------------------------------------------
1455 PROCEDURE LOAD_ENT_PERIOD(p_from_date in date, p_to_date in date) IS
1456
1457 -- ---------------------------------------------------------
1458 -- Define local variables
1459 -- ---------------------------------------------------------
1460 l_from_date date;
1461 l_to_date date;
1462 l_period_row number;
1463 l_period_seq number;
1464 l_period_year number;
1465
1466 cursor get_years is
1467 select distinct ent_year_id
1468 from HRI_time_ent_period
1469 where start_date <= p_to_date
1470 and end_date >= p_from_date;
1471
1472 cursor get_periods (p_year number) is
1473 select ent_period_id
1474 from HRI_time_ent_period
1475 where ent_year_id = p_year
1476 order by start_date;
1477
1478 begin
1479
1480 -- ---------------------------------------------------------
1481 -- Variable initialization
1482 -- ---------------------------------------------------------
1483 l_from_date := p_from_date;
1484 l_to_date := p_to_date;
1485 l_period_row := 0;
1486
1487 IF g_load_mode = 'INIT' THEN
1488 --If it is initial load the first year should not be populated for the prior_year_ent_period_id column
1489 --Used a decode for the same
1490 select period_year into l_period_year from gl_periods
1491 where l_from_date between start_date and end_date
1492 and period_set_name = g_period_set_name
1493 and period_type = g_period_type
1494 and adjustment_period_flag='N';
1495
1496 ELSE
1497 --Incremental run
1498 select period_year into l_period_year from gl_periods
1499 where (select nvl(min(start_date), l_from_date) from HRI_time_ent_period) between start_date and end_date
1500 and period_set_name = g_period_set_name
1501 and period_type = g_period_type
1502 and adjustment_period_flag='N';
1503 END IF;
1504
1505 -- Bug 5624487
1506 -- delete from HRI_TIME_ENT_PERIOD where start_date <= l_to_date and end_date >= l_from_date;
1507 delete from HRI_TIME_ENT_PERIOD
1508 where start_date <= l_to_date and end_date >= l_from_date
1509 or end_date >= g_unassigned_day;
1510
1511 -- ----------------------
1512 -- Populate Enterprise Period Level
1513 -- ----------------------
1514 insert into HRI_time_ent_period
1515 (ent_period_id,
1516 ent_qtr_id,
1517 ent_year_id,
1518 sequence,
1519 name,
1520 start_date,
1521 end_date,
1522 prior_year_ent_period_id,
1523 creation_date,
1524 last_update_date,
1525 last_updated_by,
1526 created_by,
1527 last_update_login)
1528 select to_number(period_year||quarter_num||decode(length(period_num),1,'0'||period_num, period_num)),
1529 -- lpad(period_num,2,'0')), bug 3370185
1530 to_number(period_year||quarter_num),
1531 to_number(period_year),
1532 period_num,
1533 period_name,
1534 start_date,
1535 end_date,
1536 decode(l_period_year, period_year, null, to_number((period_year-1)||quarter_num||decode(length(period_num),1,'0'||period_num, period_num))),
1537 sysdate,
1538 sysdate,
1539 g_user_id,
1540 g_user_id,
1541 g_login_id
1542 from gl_periods
1543 where period_set_name = g_period_set_name
1544 and period_type = g_period_type
1545 and adjustment_period_flag='N'
1546 and start_date <= l_to_date
1547 and end_date >= l_from_date;
1548
1549 l_period_row := sql%rowcount;
1550
1551 if g_debug_flag = 'Y' then
1552 HRI_util.put_line('LOAD_ENT_PERIOD : '||to_char(l_period_row)||' records has been populated to Enterprise Period Level');
1553 end if;
1554
1555
1556 for i in get_years
1557 loop
1558 -- update period sequence
1559 l_period_seq := 1;
1560 for j in get_periods (i.ent_year_id)
1561 loop
1562 update HRI_time_ent_period
1563 set sequence = l_period_seq
1564 where ent_period_id = j.ent_period_id;
1565 l_period_seq :=l_period_seq + 1;
1566 end loop;
1567 end loop;
1568
1569 commit;
1570
1571 -- Bug 5624487
1572 insert into HRI_time_ent_period
1573 (ent_period_id,
1574 ent_qtr_id,
1575 ent_year_id,
1576 sequence,
1577 name,
1578 start_date,
1579 end_date,
1580 prior_year_ent_period_id,
1581 creation_date,
1582 last_update_date,
1583 last_updated_by,
1584 created_by,
1585 last_update_login)
1586 select to_number(period_year||quarter_num||decode(length(period_num),1,'0'||period_num, period_num)),
1587 -- lpad(period_num,2,'0')), bug 3370185
1588 to_number(period_year||quarter_num),
1589 to_number(period_year),
1590 period_num,
1591 decode(end_date, g_unassigned_day, null,
1592 period_name
1593 ),
1594 start_date,
1595 end_date,
1596 decode(end_date, g_unassigned_day, null,
1597 decode(l_period_year, period_year, null, to_number((period_year-1)||quarter_num||decode(length(period_num),1,'0'||period_num, period_num)))
1598 ),
1599 sysdate,
1600 sysdate,
1601 g_user_id,
1602 g_user_id,
1603 g_login_id
1604 from ( select
1605 g_una_ent_period_year period_year,
1606 g_una_ent_quarter_num quarter_num,
1607 g_una_ent_period_num period_num,
1608 null period_name,
1609 g_unassigned_day start_date,
1610 g_unassigned_day end_date
1611 from dual );
1612
1613 commit;
1614
1615 -- Bug 5624487
1616 update HRI_time_day
1617 set ent_period_id = to_number(g_una_ent_period_year||g_una_ent_quarter_num||decode(length(g_una_ent_period_num),1,'0'||g_una_ent_period_num, g_una_ent_period_num)),
1618 ent_period_start_date = g_unassigned_day,
1619 ent_period_end_date = g_unassigned_day,
1620 ent_qtr_id = to_number(g_una_ent_period_year||g_una_ent_quarter_num),
1621 ent_qtr_start_date = g_unassigned_day,
1622 ent_qtr_end_date = g_unassigned_day,
1623 ent_year_id = to_number(g_una_ent_period_year),
1624 ent_year_start_date = g_unassigned_day,
1625 ent_year_end_date = g_unassigned_day,
1626 last_update_date = sysdate,
1627 last_updated_by = g_user_id,
1628 last_update_login = g_login_id
1629 where report_date = g_unassigned_day;
1630
1631 commit;
1632
1633 end LOAD_ENT_PERIOD;
1634
1635 ---------------------------------------------------
1636 -- PRIVATE PROCEDURE LOAD_ENT_QUARTER
1637 ---------------------------------------------------
1638 PROCEDURE LOAD_ENT_QUARTER(p_from_date in date, p_to_date in date) IS
1639
1640 -- ---------------------------------------------------------
1641 -- Define local variables
1642 -- ---------------------------------------------------------
1643 l_from_date date;
1644 l_to_date date;
1645 l_qtr_row number;
1646 l_qtr_seq number;
1647 l_period_year number;
1648
1649 cursor get_years is
1650 select distinct ent_year_id
1651 from HRI_time_ent_period
1652 where start_date <= p_to_date
1653 and end_date >= p_from_date;
1654
1655 cursor get_quarters (p_year number) is
1656 select distinct sequence, start_date, ent_qtr_id
1657 from HRI_time_ent_qtr
1658 where ent_year_id = p_year
1659 order by start_date;
1660
1661 begin
1662
1663 -- ---------------------------------------------------------
1664 -- Variable initialization
1665 -- ---------------------------------------------------------
1666 l_from_date := p_from_date;
1667 l_to_date := p_to_date;
1668 l_qtr_row := 0;
1669
1670 IF g_load_mode = 'INIT' THEN
1671 --If it is initial load the first year should not be populated for the prior_year_ent_period_id column
1672 --Used a decode for the same
1673 select period_year into l_period_year from gl_periods
1674 where l_from_date between start_date and end_date
1675 and period_set_name = g_period_set_name
1676 and period_type = g_period_type
1677 and adjustment_period_flag='N';
1678
1679 ELSE
1680 --Incremental run
1681 select period_year into l_period_year from gl_periods
1682 where (select nvl(min(start_date), l_from_date) from HRI_time_ent_qtr) between start_date and end_date
1683 and period_set_name = g_period_set_name
1684 and period_type = g_period_type
1685 and adjustment_period_flag='N';
1686
1687 END IF;
1688
1689 -- Bug 5624487
1690 -- delete from HRI_TIME_ENT_QTR where start_date <= l_to_date and end_date >= l_from_date;
1691 delete from HRI_TIME_ENT_QTR
1692 where start_date <= l_to_date and end_date >= l_from_date
1693 or end_date >= g_unassigned_day;
1694
1695 -- ----------------------
1696 -- Populate Enterprise Quarter Level
1697 -- ----------------------
1698 insert into HRI_time_ent_qtr
1699 (ent_qtr_id,
1700 ent_year_id,
1701 sequence,
1702 name,
1703 start_date,
1704 end_date,
1705 prior_year_ent_qtr_id,
1706 creation_date,
1707 last_update_date,
1708 last_updated_by,
1709 created_by,
1710 last_update_login)
1711 select distinct glp.period_year||glp.quarter_num,
1712 glp.period_year,
1713 glp.quarter_num,
1714 replace(fnd_message.get_string('HRI','HRI_QUARTER_LABEL'),'&QUARTER_NUMBER',glp.quarter_num)||'-'||to_char(to_date(glp.period_year,'yyyy'),'RR'),
1715 gl2.start_date,
1716 gl2.end_date,
1717 decode(l_period_year, period_year, NULL, (glp.period_year - 1)||glp.quarter_num),
1718 sysdate,
1719 sysdate,
1720 g_user_id,
1721 g_user_id,
1722 g_login_id
1723 from gl_periods glp,
1724 (select period_year||quarter_num ent_qtr_pk_key, min(start_date) start_date, max(end_date) end_date
1725 from gl_periods
1726 where period_set_name=g_period_set_name
1727 and period_type=g_period_type
1728 and adjustment_period_flag='N'
1729 group by period_year||quarter_num) gl2
1730 where glp.period_year||glp.quarter_num = gl2.ent_qtr_pk_key
1731 and glp.period_set_name = g_period_set_name
1732 and glp.period_type = g_period_type
1733 and glp.adjustment_period_flag='N'
1734 and glp.start_date <= l_to_date
1735 and glp.end_date >= l_from_date;
1736
1737 l_qtr_row := sql%rowcount;
1738
1739 for i in get_years
1740 loop
1741 -- update quarter sequence
1742 l_qtr_seq := 1;
1743 for k in get_quarters (i.ent_year_id)
1744 loop
1745 update HRI_time_ent_qtr
1746 set sequence = l_qtr_seq
1747 where ent_qtr_id = k.ent_qtr_id;
1748 l_qtr_seq := l_qtr_seq + 1;
1749 end loop;
1750 end loop;
1751
1752 commit;
1753
1754 if g_debug_flag = 'Y' then
1755 HRI_util.put_line('LOAD_ENT_QUARTER : '||to_char(l_qtr_row)||' records has been populated to Enterprise Quarter Level');
1756 end if;
1757
1758 -- Bug 5624487
1759 insert into HRI_time_ent_qtr
1760 (ent_qtr_id,
1761 ent_year_id,
1762 sequence,
1763 name,
1764 start_date,
1765 end_date,
1766 prior_year_ent_qtr_id,
1767 creation_date,
1768 last_update_date,
1769 last_updated_by,
1770 created_by,
1771 last_update_login)
1772 select distinct glp.period_year||glp.quarter_num,
1773 glp.period_year,
1774 glp.quarter_num,
1775 decode(end_date, g_unassigned_day, null,
1776 replace(fnd_message.get_string('HRI','HRI_QUARTER_LABEL'),'&QUARTER_NUMBER',glp.quarter_num)||'-'||to_char(to_date(glp.period_year,'yyyy'),'RR')
1777 ),
1778 glp.start_date,
1779 glp.end_date,
1780 decode(end_date, g_unassigned_day, null,
1781 decode(l_period_year, period_year, NULL, (glp.period_year - 1)||glp.quarter_num)
1782 ),
1783 sysdate,
1784 sysdate,
1785 g_user_id,
1786 g_user_id,
1787 g_login_id
1788 from ( select
1789 g_una_ent_period_year period_year,
1790 g_una_ent_quarter_num quarter_num,
1791 g_unassigned_day start_date,
1792 g_unassigned_day end_date
1793 from dual ) glp;
1794
1795 commit;
1796
1797 end LOAD_ENT_QUARTER;
1798
1799 ---------------------------------------------------
1800 -- PRIVATE PROCEDURE LOAD_ENT_YEAR
1801 ---------------------------------------------------
1802 PROCEDURE LOAD_ENT_YEAR(p_from_date in date, p_to_date in date) IS
1803
1804 -- ---------------------------------------------------------
1805 -- Define local variables
1806 -- ---------------------------------------------------------
1807 l_from_date date;
1808 l_to_date date;
1809 l_year_row number;
1810 l_end_date date;
1811 l_period_year number;
1812
1813 begin
1814
1815 -- ---------------------------------------------------------
1816 -- Variable initialization
1817 -- ---------------------------------------------------------
1818 l_from_date := p_from_date;
1819 l_to_date := p_to_date;
1820 l_year_row := 0;
1821
1822 IF g_load_mode = 'INIT' THEN
1823 --If it is initial load the first year should not be populated for the prior_year_ent_period_id column
1824 --Used a decode for the same
1825 select period_year into l_period_year from gl_periods
1826 where l_from_date between start_date and end_date
1827 and period_set_name = g_period_set_name
1828 and period_type = g_period_type
1829 and adjustment_period_flag='N';
1830
1831 ELSE
1832 --Incremental run
1833 select period_year into l_period_year from gl_periods where (select nvl(min(start_date), l_from_date) from HRI_time_ent_year)
1834 between start_date and end_date
1835 and period_set_name = g_period_set_name
1836 and period_type = g_period_type
1837 and adjustment_period_flag='N';
1838
1839 END IF;
1840
1841 -- Bug 5624487
1842 select nvl(max(end_date), l_to_date)
1843 into l_end_date
1844 from HRI_time_ent_period
1845 where end_date < g_unassigned_day;
1846
1847 -- Bug 5624487
1848 delete from HRI_TIME_ENT_YEAR where ent_year_id in
1849 (select period_year
1850 from gl_periods
1851 where period_set_name = g_period_set_name
1852 and period_type = g_period_type
1853 and adjustment_period_flag='N'
1854 and start_date <= l_to_date
1855 and end_date >= l_from_date)
1856 or end_date >= g_unassigned_day;
1857
1858 -- ----------------------
1859 -- Populate Enterprise Year Level
1860 -- ----------------------
1861 insert into HRI_time_ent_year
1862 (ent_year_id,
1863 period_set_name,
1864 period_type,
1865 sequence,
1866 name,
1867 start_date,
1868 end_date,
1869 prior_ent_year_id,
1870 creation_date,
1871 last_update_date,
1872 last_updated_by,
1873 created_by,
1874 last_update_login)
1875 select distinct glp.period_year ent_year_pk_key,
1876 glp.period_set_name period_set_name,
1877 glp.period_type period_type,
1878 glp.period_year,
1879 glp.period_year name,
1880 gl2.start_date start_date,
1881 gl2.end_date end_date,
1882 decode(l_period_year, glp.period_year, NULL, (glp.period_year - 1)),
1883 sysdate creation_date,
1884 sysdate last_update_date,
1885 g_user_id last_updated_by,
1886 g_user_id created_by,
1887 g_login_id last_update_login
1888 from gl_periods glp,
1889 (select period_year period_year, min(start_date) start_date, max(end_date) end_date
1890 from gl_periods
1891 where period_set_name=g_period_set_name
1892 and period_type=g_period_type
1893 and adjustment_period_flag='N'
1894 and end_date <= l_end_date
1895 group by period_year) gl2
1896 where glp.period_year=gl2.period_year
1897 and glp.period_set_name = g_period_set_name
1898 and glp.period_type = g_period_type
1899 and glp.adjustment_period_flag='N'
1900 and glp.start_date <= l_to_date
1901 and glp.end_date >= l_from_date;
1902
1903 l_year_row := sql%rowcount;
1904
1905 commit;
1906
1907 if g_debug_flag = 'Y' then
1908 HRI_util.put_line('LOAD_ENT_YEAR : '||to_char(l_year_row)||' records has been populated to Enterprise Year Level');
1909 end if;
1910
1911 -- Bug 5624487
1912 insert into HRI_time_ent_year
1913 (ent_year_id,
1914 period_set_name,
1915 period_type,
1916 sequence,
1917 name,
1918 start_date,
1919 end_date,
1920 prior_ent_year_id,
1921 creation_date,
1922 last_update_date,
1923 last_updated_by,
1924 created_by,
1925 last_update_login)
1926 select distinct glp.period_year ent_year_pk_key,
1927 glp.period_set_name period_set_name,
1928 glp.period_type period_type,
1929 glp.period_year,
1930 decode(end_date, g_unassigned_day, null,
1931 glp.period_year
1932 ),
1933 glp.start_date start_date,
1934 glp.end_date end_date,
1935 decode(end_date, g_unassigned_day, null,
1936 decode(l_period_year, glp.period_year, NULL, (glp.period_year - 1))
1937 ),
1938 sysdate creation_date,
1939 sysdate last_update_date,
1940 g_user_id last_updated_by,
1941 g_user_id created_by,
1942 g_login_id last_update_login
1943 from ( select
1944 g_una_ent_period_year period_year,
1945 g_period_set_name period_set_name,
1946 g_period_type period_type,
1947 g_unassigned_day start_date,
1948 g_unassigned_day end_date
1949 from dual ) glp;
1950
1951 commit;
1952
1953 end LOAD_ENT_YEAR;
1954
1955 ---------------------------------------------------
1956 -- PRIVATE PROCEDURE LOAD_CAL_PERIOD
1957 ---------------------------------------------------
1958 PROCEDURE LOAD_CAL_PERIOD IS
1959
1960 -- ---------------------------------------------------------
1961 -- Define local variables
1962 -- ---------------------------------------------------------
1963 l_period_row number;
1964
1965 l_diff_rows number;
1966 l_period_changed number;
1967
1968 begin
1969
1970 -- determine if any rows will be different between current set per
1971 -- gl tables and current set per HRI tables
1972 --Bug 3543939. Get the Calendar id's into a temporary table for use in Load_Time_Cal_Rpt_Struct
1973
1974 Insert into HRI_time_cal_gt(calendar_id)
1975 select calendar_id
1976 from (
1977 (select
1978 to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num||decode(length(glp.period_num),1,'0'||glp.period_num, glp.period_num))
1979 -- lpad(gl.period_num,2,'0')) bug 3370185
1980 , to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
1981 , to_number(lpad(cal_name.calendar_id,3,'0')) calendar_id
1982 , glp.period_num
1983 , glp.period_name
1984 , glp.start_date
1985 , glp.end_date
1986 from
1987 gl_periods glp
1988 , HRI_time_cal_name cal_name
1989 where glp.adjustment_period_flag = 'N'
1990 and glp.period_set_name = cal_name.period_set_name
1991 and glp.period_type = cal_name.period_type
1992 minus
1993 select
1994 cal_period_id
1995 , cal_qtr_id
1996 , calendar_id
1997 , sequence
1998 , name
1999 , start_date
2000 , end_date
2001 from
2002 HRI_time_cal_period
2003 )
2004 union all
2005 (select
2006 cal_period_id
2007 , cal_qtr_id
2008 , calendar_id
2009 , sequence
2010 , name
2011 , start_date
2012 , end_date
2013 from
2014 HRI_time_cal_period
2015 minus
2016 select
2017 to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num||decode(length(glp.period_num),1,'0'||glp.period_num, glp.period_num))
2018 -- lpad(gl.period_num,2,'0')) bug 3370185
2019 , to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
2020 , to_number(lpad(cal_name.calendar_id,3,'0')) calendar_id
2021 , glp.period_num
2022 , glp.period_name
2023 , glp.start_date
2024 , glp.end_date
2025 from
2026 gl_periods glp
2027 , HRI_time_cal_name cal_name
2028 where glp.adjustment_period_flag = 'N'
2029 and glp.period_set_name = cal_name.period_set_name
2030 and glp.period_type = cal_name.period_type
2031 )
2032 );
2033
2034 --For Bug 3640141.
2035 l_period_changed := sql%rowcount;
2036
2037 if g_debug_flag = 'Y' then
2038 HRI_util.put_line('LOAD_CAL_PERIOD : '||to_char(l_period_changed)||' Calendars have changed in GL');
2039 end if;
2040
2041 --For Bug 3543939. If there is a difference then truncate and repopulate HRI_TIME_CAL_PERIOD
2042
2043 -- Bug 4966868: Changed not to count rows
2044 BEGIN
2045 select 1 into l_diff_rows
2046 from HRI_time_cal_gt
2047 where rownum = 1;
2048 EXCEPTION
2049 WHEN NO_DATA_FOUND THEN
2050 l_diff_rows := 0;
2051 END;
2052
2053 -- all or nothing!
2054 -- if there are no differences then there is no more work to do
2055 if l_diff_rows = 0 then
2056
2057 if g_debug_flag = 'Y' then
2058 HRI_util.put_line('LOAD_CAL_PERIOD : '||'0 records has been populated to Financial Period Level');
2059 end if;
2060
2061 return;
2062
2063 end if;
2064
2065 truncate_table('HRI_TIME_CAL_PERIOD');
2066
2067 -- ---------------------------------------------------------
2068 -- Variable initialization
2069 -- ---------------------------------------------------------
2070 l_period_row := 0;
2071
2072 -- ----------------------
2073 -- Populate Financial Period Level
2074 -- ----------------------
2075 insert into HRI_time_cal_period
2076 (cal_period_id,
2077 cal_qtr_id,
2078 calendar_id,
2079 sequence,
2080 name,
2081 start_date,
2082 end_date,
2083 creation_date,
2084 last_update_date,
2085 last_updated_by,
2086 created_by,
2087 last_update_login)
2088 select lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num||decode(length(glp.period_num),1,'0'||glp.period_num, glp.period_num),
2089 -- lpad(gl.period_num,2,'0'), bug 3370185
2090 lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num,
2091 lpad(cal_name.calendar_id,3,'0'),
2092 glp.period_num,
2093 glp.period_name,
2094 glp.start_date,
2095 glp.end_date,
2096 sysdate,
2097 sysdate,
2098 g_user_id,
2099 g_user_id,
2100 g_login_id
2101 from gl_periods glp, HRI_time_cal_name cal_name
2102 where glp.adjustment_period_flag='N'
2103 and glp.period_set_name=cal_name.period_set_name
2104 and glp.period_type=cal_name.period_type;
2105
2106 l_period_row := sql%rowcount;
2107
2108 commit;
2109
2110 if g_debug_flag = 'Y' then
2111 HRI_util.put_line('LOAD_CAL_PERIOD : '||to_char(l_period_row)||' records has been populated to Financial Period Level');
2112 end if;
2113
2114 gather_table_stats('HRI_TIME_CAL_PERIOD');
2115
2116 if g_debug_flag = 'Y' then
2117 HRI_util.put_line('LOAD_CAL_PERIOD : '||'Gathered statistics for Financial Period Level');
2118 end if;
2119
2120 end LOAD_CAL_PERIOD;
2121
2122 ---------------------------------------------------
2123 -- PRIVATE PROCEDURE LOAD_CAL_QUARTER
2124 ---------------------------------------------------
2125 PROCEDURE LOAD_CAL_QUARTER IS
2126
2127 -- ---------------------------------------------------------
2128 -- Define local variables
2129 -- ---------------------------------------------------------
2130 l_qtr_row number;
2131
2132 l_diff_rows number;
2133
2134 begin
2135
2136 -- determine if any rows will be different between current set per
2137 -- gl tables and current set per HRI tables
2138 -- Bug 4966868: Changed not to count rows
2139 begin
2140 select 1
2141 into l_diff_rows
2142 from (
2143 (select
2144 distinct to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
2145 , to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
2146 , to_number(lpad(cal_name.calendar_id,3,'0'))
2147 , glp.quarter_num
2148 , to_char(glp.quarter_num)||', '||to_char(glp.period_year)
2149 , min(gl2.start_date)
2150 , max(gl2.end_date)
2151 from
2152 gl_periods glp
2153 , HRI_time_cal_name cal_name
2154 , (select period_set_name
2155 , period_type
2156 , period_year
2157 , quarter_num
2158 , min(start_date) start_date
2159 , max(end_date) end_date
2160 from
2161 gl_periods
2162 where adjustment_period_flag='N'
2163 group by
2164 period_set_name
2165 , period_type
2166 , period_year
2167 , quarter_num
2168 ) gl2
2169 where glp.adjustment_period_flag='N'
2170 and glp.period_set_name=cal_name.period_set_name
2171 and glp.period_type=cal_name.period_type
2172 and glp.period_set_name=gl2.period_set_name
2173 and glp.period_type=gl2.period_type
2174 and glp.period_year=gl2.period_year
2175 and glp.quarter_num=gl2.quarter_num
2176 group by
2177 to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
2178 , to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
2179 , to_number(lpad(cal_name.calendar_id,3,'0'))
2180 , glp.quarter_num
2181 , to_char(glp.quarter_num)||', '||to_char(glp.period_year)
2182 minus
2183 select
2184 cal_qtr_id
2185 , cal_year_id
2186 , calendar_id
2187 , sequence
2188 , name
2189 , start_date
2190 , end_date
2191 from
2192 HRI_time_cal_qtr
2193 )
2194 union all
2195 (select
2196 cal_qtr_id
2197 , cal_year_id
2198 , calendar_id
2199 , sequence
2200 , name
2201 , start_date
2202 , end_date
2203 from
2204 HRI_time_cal_qtr
2205 minus
2206 select
2207 distinct to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
2208 , to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
2209 , to_number(lpad(cal_name.calendar_id,3,'0'))
2210 , glp.quarter_num
2211 , to_char(glp.quarter_num)||', '||to_char(glp.period_year)
2212 , min(gl2.start_date)
2213 , max(gl2.end_date)
2214 from
2215 gl_periods glp
2216 , HRI_time_cal_name cal_name
2217 , (select period_set_name
2218 , period_type
2219 , period_year
2220 , quarter_num
2221 , min(start_date) start_date
2222 , max(end_date) end_date
2223 from
2224 gl_periods
2225 where adjustment_period_flag = 'N'
2226 group by
2227 period_set_name
2228 , period_type
2229 , period_year
2230 , quarter_num
2231 ) gl2
2232 where glp.adjustment_period_flag = 'N'
2233 and glp.period_set_name = cal_name.period_set_name
2234 and glp.period_type = cal_name.period_type
2235 and glp.period_set_name = gl2.period_set_name
2236 and glp.period_type = gl2.period_type
2237 and glp.period_year = gl2.period_year
2238 and glp.quarter_num = gl2.quarter_num
2239 group by
2240 to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num)
2241 , to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
2242 , to_number(lpad(cal_name.calendar_id,3,'0'))
2243 , glp.quarter_num
2244 , to_char(glp.quarter_num)||', '||to_char(glp.period_year)
2245 )
2246 )
2247 where rownum = 1;
2248 exception
2249 when NO_DATA_FOUND then
2250 l_diff_rows := 0;
2251 end;
2252
2253 -- all or nothing!
2254 -- if there are no differences then there is no more work to do
2255 if l_diff_rows = 0 then
2256
2257 if g_debug_flag = 'Y' then
2258 HRI_util.put_line('LOAD_CAL_QUARTER : '||'0 records has been populated to Financial Quarter Level');
2259 end if;
2260
2261 return;
2262
2263 end if;
2264
2265 truncate_table('HRI_TIME_CAL_QTR');
2266
2267 -- ---------------------------------------------------------
2268 -- Variable initialization
2269 -- ---------------------------------------------------------
2270 l_qtr_row := 0;
2271
2272 -- ----------------------
2273 -- Populate Financial Quarter Level
2274 -- ----------------------
2275 insert into HRI_time_cal_qtr
2276 (cal_qtr_id,
2277 cal_year_id,
2278 calendar_id,
2279 sequence,
2280 name,
2281 start_date,
2282 end_date,
2283 creation_date,
2284 last_update_date,
2285 last_updated_by,
2286 created_by,
2287 last_update_login)
2288 select distinct lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num,
2289 lpad(cal_name.calendar_id,3,'0')||glp.period_year,
2290 lpad(cal_name.calendar_id,3,'0'),
2291 glp.quarter_num,
2292 to_char(glp.quarter_num)||', '||to_char(glp.period_year),
2293 min(gl2.start_date),
2294 max(gl2.end_date),
2295 sysdate,
2296 sysdate,
2297 g_user_id,
2298 g_user_id,
2299 g_login_id
2300 from gl_periods glp, HRI_time_cal_name cal_name,
2301 (select period_set_name, period_type, period_year, quarter_num, min(start_date) start_date, max(end_date) end_date
2302 from gl_periods
2303 where adjustment_period_flag='N'
2304 group by period_set_name, period_type, period_year, quarter_num) gl2
2305 where glp.adjustment_period_flag='N'
2306 and glp.period_set_name=cal_name.period_set_name
2307 and glp.period_type=cal_name.period_type
2308 and glp.period_set_name=gl2.period_set_name
2309 and glp.period_type=gl2.period_type
2310 and glp.period_year=gl2.period_year
2311 and glp.quarter_num=gl2.quarter_num
2312 group by lpad(cal_name.calendar_id,3,'0')||glp.period_year||glp.quarter_num,
2313 lpad(cal_name.calendar_id,3,'0')||glp.period_year,
2314 lpad(cal_name.calendar_id,3,'0'),
2315 glp.quarter_num,
2316 to_char(glp.quarter_num)||', '||to_char(glp.period_year);
2317
2318 l_qtr_row := sql%rowcount;
2319
2320 commit;
2321
2322 if g_debug_flag = 'Y' then
2323 HRI_util.put_line('LOAD_CAL_QUARTER : '||to_char(l_qtr_row)||' records has been populated to Financial Quarter Level');
2324 end if;
2325
2326 gather_table_stats('HRI_TIME_CAL_QTR');
2327
2328 if g_debug_flag = 'Y' then
2329 HRI_util.put_line('LOAD_CAL_QUARTER : '||'Gathered statistics for Financial Quarter Level');
2330 end if;
2331
2332 end LOAD_CAL_QUARTER;
2333
2334 ---------------------------------------------------
2335 -- PRIVATE PROCEDURE LOAD_CAL_YEAR
2336 ---------------------------------------------------
2337 PROCEDURE LOAD_CAL_YEAR IS
2338
2339 -- ---------------------------------------------------------
2340 -- Define local variables
2341 -- ---------------------------------------------------------
2342 l_year_row number;
2343
2344 l_diff_rows number;
2345
2346 begin
2347
2348 -- determine if any rows will be different between current set per
2349 -- gl tables and current set per HRI tables
2350 -- Bug 4966868: Changed not to count rows
2351 begin
2352 select 1
2353 into l_diff_rows
2354 from (
2355 (select
2356 distinct to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
2357 , to_number(lpad(cal_name.calendar_id,3,'0'))
2358 , glp.period_year
2359 , to_char(glp.period_year)
2360 , min(gl2.start_date)
2361 , max(gl2.end_date)
2362 from
2363 gl_periods glp
2364 , HRI_time_cal_name cal_name
2365 , (select period_set_name
2366 , period_type
2367 , period_year
2368 , min(start_date) start_date
2369 , max(end_date) end_date
2370 from
2371 gl_periods
2372 where adjustment_period_flag='N'
2373 group by
2374 period_set_name
2375 , period_type
2376 , period_year
2377 ) gl2
2378 where glp.adjustment_period_flag = 'N'
2379 and glp.period_set_name = cal_name.period_set_name
2380 and glp.period_type = cal_name.period_type
2381 and glp.period_set_name = gl2.period_set_name
2382 and glp.period_type = gl2.period_type
2383 and glp.period_year = gl2.period_year
2384 group by
2385 cal_name.calendar_id
2386 , glp.period_year
2387 minus
2388 select
2389 cal_year_id
2390 , calendar_id
2391 , sequence
2392 , name
2393 , start_date
2394 , end_date
2395 from
2396 HRI_time_cal_year
2397 )
2398 union all
2399 (select
2400 cal_year_id
2401 , calendar_id
2402 , sequence
2403 , name
2404 , start_date
2405 , end_date
2406 from
2407 HRI_time_cal_year
2408 minus
2409 select
2410 distinct to_number(lpad(cal_name.calendar_id,3,'0')||glp.period_year)
2411 , to_number(lpad(cal_name.calendar_id,3,'0'))
2412 , glp.period_year
2413 , to_char(glp.period_year)
2414 , min(gl2.start_date)
2415 , max(gl2.end_date)
2416 from
2417 gl_periods glp
2418 , HRI_time_cal_name cal_name
2419 , (select period_set_name
2420 , period_type
2421 , period_year
2422 , min(start_date) start_date
2423 , max(end_date) end_date
2424 from
2425 gl_periods
2426 where adjustment_period_flag = 'N'
2427 group by
2428 period_set_name
2429 , period_type
2430 , period_year
2431 ) gl2
2432 where glp.adjustment_period_flag = 'N'
2433 and glp.period_set_name = cal_name.period_set_name
2434 and glp.period_type = cal_name.period_type
2435 and glp.period_set_name = gl2.period_set_name
2436 and glp.period_type = gl2.period_type
2437 and glp.period_year = gl2.period_year
2438 group by
2439 cal_name.calendar_id
2440 , glp.period_year
2441 )
2442 )
2443 where rownum = 1;
2444 exception
2445 when NO_DATA_FOUND then
2446 l_diff_rows := 0;
2447 end;
2448
2449 -- all or nothing!
2450 -- if there are no differences then there is no more work to do
2451 if l_diff_rows = 0 then
2452
2453 if g_debug_flag = 'Y' then
2454 HRI_util.put_line('LOAD_CAL_YEAR : '||'0 records has been populated to Financial Year Level');
2455 end if;
2456
2457 return;
2458
2459 end if;
2460
2461 truncate_table('HRI_TIME_CAL_YEAR');
2462
2463 -- ---------------------------------------------------------
2464 -- Variable initialization
2465 -- ---------------------------------------------------------
2466 l_year_row := 0;
2467
2468 -- ----------------------
2469 -- Populate Financial Year Level
2470 -- ----------------------
2471 insert into HRI_time_cal_year
2472 (cal_year_id,
2473 calendar_id,
2474 sequence,
2475 name,
2476 start_date,
2477 end_date,
2478 creation_date,
2479 last_update_date,
2480 last_updated_by,
2481 created_by,
2482 last_update_login)
2483 select distinct lpad(cal_name.calendar_id,3,'0')||glp.period_year,
2484 lpad(cal_name.calendar_id,3,'0'),
2485 glp.period_year,
2486 glp.period_year,
2487 min(gl2.start_date),
2488 max(gl2.end_date),
2489 sysdate,
2490 sysdate,
2491 g_user_id,
2492 g_user_id,
2493 g_login_id
2494 from gl_periods glp, HRI_time_cal_name cal_name,
2495 (select period_set_name, period_type, period_year, min(start_date) start_date, max(end_date) end_date
2496 from gl_periods
2497 where adjustment_period_flag='N'
2498 group by period_set_name, period_type, period_year) gl2
2499 where glp.adjustment_period_flag='N'
2500 and glp.period_set_name=cal_name.period_set_name
2501 and glp.period_type=cal_name.period_type
2502 and glp.period_set_name=gl2.period_set_name
2503 and glp.period_type=gl2.period_type
2504 and glp.period_year=gl2.period_year
2505 group by cal_name.calendar_id, glp.period_year;
2506
2507 l_year_row := sql%rowcount;
2508
2509 commit;
2510
2511 if g_debug_flag = 'Y' then
2512 HRI_util.put_line('LOAD_CAL_YEAR : '||to_char(l_year_row)||' records has been populated to Financial Year Level');
2513 end if;
2514
2515 gather_table_stats('HRI_TIME_CAL_YEAR');
2516
2517 if g_debug_flag = 'Y' then
2518 HRI_util.put_line('LOAD_CAL_YEAR : '||'Gathered statistics for Financial Year Level');
2519 end if;
2520
2521 end LOAD_CAL_YEAR;
2522
2523 ---------------------------------------------------
2524 -- PUBLIC PROCEDURE LOAD
2525 ---------------------------------------------------
2526 PROCEDURE LOAD(errbuf out NOCOPY varchar2,
2527 retcode out NOCOPY Varchar2,
2528 p_from_date in varchar2,
2529 p_to_date in varchar2,
2530 p_all_level in varchar2,
2531 p_load_mode in varchar2) IS
2532
2533 -- ---------------------------------------------------------
2534 -- Define local variables
2535 -- ---------------------------------------------------------
2536 l_from_date date;
2537 l_to_date date;
2538
2539 l_global_param_list dbms_sql.varchar2_table;
2540
2541 l_bis_setup varchar2(1) := 'N';
2542 l_min_date date;
2543 l_max_date date;
2544 l_max_gl_date date;
2545 l_validated varchar2(1) := 'N';
2546 l_period_updated varchar2(1) := 'N';
2547 l_count number := 0;
2548 l_error_msg varchar2(5000);
2549 l_error_code varchar2(5000);
2550 l_dir VARCHAR2(400);
2551 l_mesg varchar2(2048) ;
2552 --Bug 4995016
2553 l_min_start_date date;
2554 l_max_end_date date;
2555 l_year number;
2556 l_start_date date;
2557 begin
2558
2559 l_dir:=HRI_UTIL.get_utl_file_dir;
2560 HRI_UTIL.initialize('HRI_DBI_TIME_M.log','HRI_DBI_TIME_M.out',l_dir, 'HRI_DBI_TIME_M');
2561
2562
2563 l_from_date := trunc(to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS'));
2564 l_to_date := trunc(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'));
2565 g_all_level := nvl(p_all_level,'Y');
2566 g_load_mode := p_load_mode;
2567
2568 if g_debug_flag = 'Y' then
2569 HRI_util.put_line('LOAD : '||'Data loads from ' ||
2570 fnd_date.date_to_displaydate(l_from_date) ||
2571 ' to ' ||
2572 fnd_date.date_to_displaydate(l_to_date));
2573 end if;
2574
2575 -----------------------------------------------------
2576 -- Calling BIS API to do common set ups
2577 -- If it returns false, then program should error out
2578 -----------------------------------------------------
2579 g_phase := 'Call BIS API to do common set ups';
2580 l_global_param_list(1) := 'BIS_GLOBAL_START_DATE';
2581 IF (NOT bis_common_parameters.check_global_parameters(l_global_param_list)) THEN
2582 retcode := 1;
2583 raise G_BIS_PARAMETER_NOT_SETUP;
2584 ELSIF (NOT BIS_COLLECTION_UTILITIES.setup('HRI_DBI_TIME_M')) THEN
2585 errbuf := sqlerrm;
2586 raise_application_error(-20000, 'Error for Time setup: ' || errbuf);
2587 END IF;
2588
2589 -- exception handler needs to know whether to perform a wrapup
2590 -- with error
2591 l_bis_setup := 'Y';
2592
2593 g_phase := 'Retrieve the BIS common parameters';
2594 INIT;
2595
2596 if g_all_level = 'Y' then
2597
2598 l_validated := check_validated;
2599
2600 -- Bug 5624487
2601 select max(report_date), min(report_date)
2602 into l_max_date, l_min_date
2603 from HRI_time_day
2604 where report_date <> g_unassigned_day;
2605
2606 if p_load_mode = 'INIT' then
2607
2608 l_validated := 'N';
2609 l_max_date := null;
2610 end if;
2611
2612 if l_validated = 'N' then
2613
2614 if g_debug_flag = 'Y' then
2615 HRI_util.put_line('LOAD : '||'Time Dimension will be validated.');
2616 HRI_util.put_line(' ');
2617 end if;
2618
2619 if l_from_date > g_global_start_date then
2620 l_from_date := g_global_start_date;
2621 end if;
2622
2623 l_to_date := greatest(nvl(l_max_date,l_to_date),l_to_date);
2624
2625 end if;
2626
2627 if l_max_date is not null and
2628 l_from_date > l_max_date+1 then
2629
2630 l_from_date := l_max_date+1;
2631
2632 end if;
2633
2634 --l_start_date is used for finding out the year of the date for which time dimension will run.
2635 l_start_date := l_from_date;
2636
2637 -- Bug 4454026
2638 -- We should default the from date to the start of the fiscal year so that fix of bug 3961336 wroks fine
2639 -- which is to make the sequence in HRI_time_ent_period and HRI_time_ent_qtr independent of GL
2640 -- This is done for initial load and in case the last run was not successfull
2641
2642 g_phase := 'Checking if all the years in the calendar are fully defined';
2643
2644 -- This is necessary because once initial load is over, there can be some changes in the already defined calendar
2645 -- which may result in a calendar not complying with the no. of days range allowed (365 +/- 14)
2646
2647 IF l_validated = 'Y' THEN
2648 truncate_table('HRI_TIME_GL_PERIODS');
2649 l_period_updated := period_updated(l_min_date, l_max_date);
2650 IF (l_period_updated = 'Y' and p_load_mode <> 'INIT') THEN
2651 l_start_date := l_min_date;
2652 END IF; --l_period_updated
2653 END if; --l_validated
2654
2655 begin
2656 select period_year into l_year
2657 from gl_periods a
2658 where a.period_set_name = g_period_set_name
2659 and a.period_type = g_period_type
2660 and a.adjustment_period_flag = 'N'
2661 and l_start_date between a.start_date and a.end_date;
2662 exception
2663 when no_data_found then
2664 g_date_not_defined := l_from_date;
2665 raise G_ENT_CALENDAR_NOT_FOUND;
2666 end;
2667
2668 select min(a.start_date), max(end_date) into l_min_start_date, l_max_end_date
2669 from gl_periods a
2670 where a.period_set_name = g_period_set_name
2671 and a.period_type = g_period_type
2672 and a.adjustment_period_flag = 'N'
2673 and a.period_year = l_year;
2674
2675
2676 IF p_load_mode = 'INIT' then
2677 l_from_date := l_min_start_date;
2678 END IF;
2679
2680 -- Bug 4995016. Check for every year if its fully defined or not.
2681 -- Allowed limit is 365 + or - 14 days in a year.
2682 -- Bug 5284046. Added l_min_start_date <= l_to_date clause to the while loop
2683
2684 While (l_min_start_date is NOT NULL and l_min_start_date <= l_to_date) LOOP
2685
2686 IF (l_max_end_date - l_min_start_date) +1 < 352 OR (l_max_end_date - l_min_start_date) +1 > 379 THEN
2687 raise G_YEAR_NOT_DEFINED;
2688 END IF;
2689
2690 l_year := l_year + 1;
2691
2692 select min(a.start_date), max(end_date) into l_min_start_date, l_max_end_date
2693 from gl_periods a
2694 where a.period_set_name = g_period_set_name
2695 and a.period_type = g_period_type
2696 and a.adjustment_period_flag = 'N'
2697 and a.period_year = l_year;
2698
2699 END LOOP;
2700
2701 if g_debug_flag = 'Y' and
2702 l_from_date <> to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS') or
2703 l_to_date <> to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS') then
2704 HRI_util.put_line('LOAD : '||'Resetting data loads as from ' ||
2705 fnd_date.date_to_displaydate(l_from_date) ||
2706 ' to ' ||
2707 fnd_date.date_to_displaydate(l_to_date));
2708 end if;
2709
2710 end if; --if g_all_level = 'Y'
2711
2712 -- if it is initial loading, all the tables will be truncated and re-populated
2713 if p_load_mode = 'INIT' then
2714 if g_debug_flag = 'Y' then
2715 HRI_util.put_line('LOAD : '||'This is an initial load, all tables will be truncated and re-populated.');
2716 end if;
2717
2718 truncate_table('HRI_TIME_DAY');
2719 truncate_table('HRI_TIME_MONTH');
2720 truncate_table('HRI_TIME_QTR');
2721 truncate_table('HRI_TIME_YEAR');
2722 truncate_table('HRI_TIME_WEEK');
2723 truncate_table('HRI_TIME_P445');
2724 truncate_table('HRI_TIME_YEAR445');
2725 truncate_table('HRI_TIME_ENT_PERIOD');
2726 truncate_table('HRI_TIME_ENT_QTR');
2727 truncate_table('HRI_TIME_ENT_YEAR');
2728 truncate_table('HRI_TIME_CAL_NAME');
2729 truncate_table('HRI_TIME_CAL_PERIOD');
2730 truncate_table('HRI_TIME_CAL_QTR');
2731 truncate_table('HRI_TIME_CAL_YEAR');
2732 truncate_table('HRI_TIME_RPT_STRUCT');
2733 truncate_table('HRI_TIME_CAL_RPT_STRUCT');
2734 truncate_table('HRI_TIME_STRUCTURES');
2735 truncate_table('HRI_TIME_ROLLING_OFFSETS');
2736 else
2737 -- if it is incremental loading, check if there is any modified period
2738 -- if there is any modified period, do initial load of the enterprise tables
2739 if g_debug_flag = 'Y' then
2740 HRI_util.put_line('LOAD : '||'l_validated='||l_validated);
2741 HRI_util.put_line('');
2742 end if;
2743 if l_validated = 'Y' then
2744 --truncate_table('HRI_TIME_GL_PERIODS');
2745 --l_period_updated := period_updated(l_min_date, l_max_date);
2746 if g_debug_flag = 'Y' then
2747 HRI_util.put_line('LOAD : '||'l_period_updated='||l_period_updated);
2748 HRI_util.put_line('');
2749 end if;
2750 if l_period_updated = 'Y' then
2751 truncate_table('HRI_TIME_ENT_PERIOD');
2752 end if;
2753 -- both enterprise quarter and enterprise year tables are truncated because function period_updated
2754 -- can only detect if there is any changes of the existing periods populated into time dimension
2755 -- If there is a new period added to a quarter, then requires the end date of that quarter to be updated
2756 -- function period_updated is not able to indicate that, that's why we choose to always truncate the
2757 -- tables to avoid any further unique constraint violation
2758 truncate_table('HRI_TIME_ENT_QTR');
2759 truncate_table('HRI_TIME_ENT_YEAR');
2760 end if;
2761 end if;
2762
2763 g_phase := 'Load Day Level';
2764 if g_debug_flag = 'Y' then
2765 HRI_util.put_line(' ');
2766 HRI_util.start_timer;
2767 end if;
2768
2769 -- we compare the latest end date defined in GL and the latest end date in HRI_time_day
2770 -- to determine if there is new gl period added or not
2771 select max(end_date)
2772 into l_max_gl_date
2773 from gl_periods
2774 where period_set_name = g_period_set_name
2775 and period_type = g_period_type
2776 and adjustment_period_flag='N';
2777
2778 -- we will populate the end date columns if they haven't been populated
2779 -- Bug 4966868: Changed not to count rows
2780 begin
2781 -- Bug 5624487
2782 select 1 into l_count
2783 from HRI_time_day
2784 where ent_period_end_date is null
2785 and report_date <> g_unassigned_day
2786 and rownum = 1;
2787 exception
2788 when NO_DATA_FOUND then
2789 l_count := 0;
2790 end;
2791
2792 if l_period_updated = 'Y' or l_max_gl_date > l_max_date or l_count > 0 then
2793 -- we populate data from the earliest date in HRI_TIME_DAY or the from date parameter, see which one is the earliest
2794 -- to the latest date in HRI_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2795 -- we will insert new records as well as modify existing records if necessary. If new gl period has been added to
2796 -- an existing quarter, the quarter end date and the year end date needs to be updated.
2797 LOAD_DAY_INC(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2798 else
2799 LOAD_DAY_INC(l_from_date, l_to_date); -- incremental refresh
2800 end if;
2801 if g_debug_flag = 'Y' then
2802 HRI_util.stop_timer;
2803 HRI_util.print_timer('Process Time');
2804 HRI_util.put_line(' ');
2805 end if;
2806
2807 g_phase := 'Load Month Level';
2808 if g_debug_flag = 'Y' then
2809 HRI_util.start_timer;
2810 end if;
2811 LOAD_MONTH(l_from_date, l_to_date);
2812 if g_debug_flag = 'Y' then
2813 HRI_util.stop_timer;
2814 HRI_util.print_timer('Process Time');
2815 HRI_util.put_line(' ');
2816 end if;
2817
2818 g_phase := 'Load Quarter Level';
2819 if g_debug_flag = 'Y' then
2820 HRI_util.start_timer;
2821 end if;
2822 LOAD_QUARTER(l_from_date, l_to_date);
2823 if g_debug_flag = 'Y' then
2824 HRI_util.stop_timer;
2825 HRI_util.print_timer('Process Time');
2826 HRI_util.put_line(' ');
2827 end if;
2828
2829 g_phase := 'Load Year Level';
2830 if g_debug_flag = 'Y' then
2831 HRI_util.start_timer;
2832 end if;
2833 LOAD_YEAR(l_from_date, l_to_date);
2834 if g_debug_flag = 'Y' then
2835 HRI_util.stop_timer;
2836 HRI_util.print_timer('Process Time');
2837 HRI_util.put_line(' ');
2838 end if;
2839
2840 if (g_all_level = 'Y') then
2841
2842 g_phase := 'Load Week Level';
2843 if g_debug_flag = 'Y' then
2844 HRI_util.start_timer;
2845 end if;
2846 LOAD_WEEK(l_from_date, l_to_date);
2847 if g_debug_flag = 'Y' then
2848 HRI_util.stop_timer;
2849 HRI_util.print_timer('Process Time');
2850 HRI_util.put_line(' ');
2851 end if;
2852
2853 g_phase := 'Load Period 445 Level';
2854 if g_debug_flag = 'Y' then
2855 HRI_util.start_timer;
2856 end if;
2857 LOAD_PERIOD_445(l_from_date, l_to_date);
2858 if g_debug_flag = 'Y' then
2859 HRI_util.stop_timer;
2860 HRI_util.print_timer('Process Time');
2861 HRI_util.put_line(' ');
2862 end if;
2863
2864 g_phase := 'Load Year 445 Level';
2865 if g_debug_flag = 'Y' then
2866 HRI_util.start_timer;
2867 end if;
2868 LOAD_YEAR_445(l_from_date, l_to_date);
2869 if g_debug_flag = 'Y' then
2870 HRI_util.stop_timer;
2871 HRI_util.print_timer('Process Time');
2872 HRI_util.put_line(' ');
2873 end if;
2874
2875 g_phase := 'Load Enterprise Period Level';
2876 if g_debug_flag = 'Y' then
2877 HRI_util.start_timer;
2878 end if;
2879 if l_period_updated = 'Y' then
2880 -- we populate data from the earliest date in HRI_TIME_DAY or the from date parameter, see which one is the earliest
2881 -- to the latest date in HRI_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2882 -- we will insert new records as well as existing records that we have been truncated
2883 LOAD_ENT_PERIOD(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2884 else
2885 LOAD_ENT_PERIOD(l_from_date, l_to_date);
2886 end if;
2887 if g_debug_flag = 'Y' then
2888 HRI_util.stop_timer;
2889 HRI_util.print_timer('Process Time');
2890 HRI_util.put_line(' ');
2891 end if;
2892
2893 g_phase := 'Load Enterprise Quarter Level';
2894 if g_debug_flag = 'Y' then
2895 HRI_util.start_timer;
2896 end if;
2897 -- we populate data from the earliest date in HRI_TIME_DAY or the from date parameter, see which one is the earliest
2898 -- to the latest date in HRI_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2899 -- we will insert new records as well as existing records that we have been truncated
2900 LOAD_ENT_QUARTER(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2901 if g_debug_flag = 'Y' then
2902 HRI_util.stop_timer;
2903 HRI_util.print_timer('Process Time');
2904 HRI_util.put_line(' ');
2905 end if;
2906
2907 g_phase := 'Load Enterprise Year Level';
2908 if g_debug_flag = 'Y' then
2909 HRI_util.start_timer;
2910 end if;
2911 -- we populate data from the earliest date in HRI_TIME_DAY or the from date parameter, see which one is the earliest
2912 -- to the latest date in HRI_TIME_DAY or the to date parameter, see which one is the latest. This is to make sure
2913 -- we will insert new records as well as existing records that we have been truncated
2914 LOAD_ENT_YEAR(least(nvl(l_min_date,l_from_date),l_from_date), greatest(nvl(l_max_date,l_to_date),l_to_date));
2915 if g_debug_flag = 'Y' then
2916 HRI_util.stop_timer;
2917 HRI_util.print_timer('Process Time');
2918 HRI_util.put_line(' ');
2919 end if;
2920
2921 g_phase := 'Load Financial Calendar Level';
2922 if g_debug_flag = 'Y' then
2923 HRI_util.start_timer;
2924 end if;
2925 LOAD_CAL_NAME;
2926 if g_debug_flag = 'Y' then
2927 HRI_util.stop_timer;
2928 HRI_util.print_timer('Process Time');
2929 HRI_util.put_line(' ');
2930 end if;
2931
2932 g_phase := 'Load Financial Period Level';
2933 if g_debug_flag = 'Y' then
2934 HRI_util.start_timer;
2935 end if;
2936 LOAD_CAL_PERIOD;
2937 if g_debug_flag = 'Y' then
2938 HRI_util.stop_timer;
2939 HRI_util.print_timer('Process Time');
2940 HRI_util.put_line(' ');
2941 end if;
2942
2943 g_phase := 'Load Financial Quarter Level';
2944 if g_debug_flag = 'Y' then
2945 HRI_util.start_timer;
2946 end if;
2947 LOAD_CAL_QUARTER;
2948 if g_debug_flag = 'Y' then
2949 HRI_util.stop_timer;
2950 HRI_util.print_timer('Process Time');
2951 HRI_util.put_line(' ');
2952 end if;
2953
2954 g_phase := 'Load Financial Year Level';
2955 if g_debug_flag = 'Y' then
2956 HRI_util.start_timer;
2957 end if;
2958 LOAD_CAL_YEAR;
2959 if g_debug_flag = 'Y' then
2960 HRI_util.stop_timer;
2961 HRI_util.print_timer('Process Time');
2962 HRI_util.put_line(' ');
2963 end if;
2964
2965 g_phase := 'Refresh Materialized View';
2966 if g_debug_flag = 'Y' then
2967 HRI_util.put_line('LOAD : '||'Refresh Materialized View');
2968 HRI_util.start_timer;
2969 end if;
2970
2971 commit;
2972
2973 ------------------------------------------------------------------------------
2974 --Bug 3155474: call BIS wrapper to handle force parallel on MVs
2975 ----- dbms_mview.REFRESH('HRI_TIME_CAL_DAY_MV','C');
2976
2977 BIS_MV_REFRESH.refresh_wrapper ('HRI_TIME_CAL_DAY_MV', 'C');
2978 ------------------------------------------------------------------------------
2979
2980 if g_debug_flag = 'Y' then
2981 HRI_util.stop_timer;
2982 HRI_util.print_timer('Process Time');
2983 HRI_util.put_line(' ');
2984 end if;
2985
2986 g_phase := 'Load Reporting Structure Table for Financial Calendars';
2987 if g_debug_flag = 'Y' then
2988 HRI_util.start_timer;
2989 end if;
2990 LOAD_TIME_CAL_RPT_STRUCT(l_from_date, l_to_date);
2991 if g_debug_flag = 'Y' then
2992 HRI_util.stop_timer;
2993 HRI_util.print_timer('Process Time');
2994 HRI_util.put_line(' ');
2995 end if;
2996
2997 if g_day_row_cnt > 0 or
2998 l_validated = 'N' then
2999
3000 g_phase := 'Load Reporting Structure Table';
3001 if g_debug_flag = 'Y' then
3002 HRI_util.start_timer;
3003 end if;
3004 LOAD_TIME_RPT_STRUCT;
3005 if g_debug_flag = 'Y' then
3006 HRI_util.stop_timer;
3007 HRI_util.print_timer('Process Time');
3008 HRI_util.put_line(' ');
3009 end if;
3010
3011 g_phase := 'Load Time Structures Table';
3012 IF g_debug_flag = 'Y' THEN
3013 HRI_util.start_timer;
3014 END IF;
3015 HRI_TIME_STRUCTURE_C.LOAD_TIME_STRUCTURES;
3016 IF g_debug_flag = 'Y' THEN
3017 HRI_util.stop_timer;
3018 HRI_util.print_timer('Process Time');
3019 HRI_util.put_line(' ');
3020 END IF;
3021
3022 else
3023
3024 begin
3025 select 1 into l_count
3026 from HRI_TIME_STRUCTURES
3027 where bitand( record_type_id, 16384+32768+65536 ) <> 0
3028 and rownum = 1;
3029 exception
3030 when NO_DATA_FOUND then
3031 l_count := 0;
3032 end;
3033
3034 -- No new rolling DSO periods detected
3035 if l_count = 0 then
3036
3037 g_phase := 'Load Time Structures Table';
3038 IF g_debug_flag = 'Y' THEN
3039 HRI_util.start_timer;
3040 END IF;
3041 HRI_TIME_STRUCTURE_C.LOAD_TIME_STRUCTURES;
3042 IF g_debug_flag = 'Y' THEN
3043 HRI_util.stop_timer;
3044 HRI_util.print_timer('Process Time');
3045 HRI_util.put_line(' ');
3046 END IF;
3047
3048 end if;
3049
3050 end if;
3051
3052 l_count := 0;
3053 -- Bug 4966868: Changed not to count rows
3054 begin
3055 select 1 into l_count
3056 from HRI_time_rolling_offsets
3057 where rownum = 1;
3058 exception
3059 when no_data_found then
3060 l_count := 0;
3061 end;
3062
3063 if l_count = 0 then
3064 g_phase := 'Load Rolling Period Offsets Table';
3065 if g_debug_flag = 'Y' then
3066 HRI_util.start_timer;
3067 end if;
3068 HRI_TIME_ROLLING_PKG.Load_Rolling_Offsets(l_error_msg,l_error_code);
3069 if g_debug_flag = 'Y' then
3070 HRI_util.stop_timer;
3071 HRI_util.print_timer('Process Time');
3072 HRI_util.put_line(' ');
3073 end if;
3074 end if;
3075
3076 if g_day_row_cnt > 0 or
3077 l_validated = 'N' then
3078
3079 g_phase := 'Gather statistics';
3080 if g_debug_flag = 'Y' then
3081 HRI_util.start_timer;
3082 end if;
3083
3084 -- note: we don't gather stats on HRI_TIME_DAY
3085 -- as this should be done by RSG
3086 gather_table_stats('HRI_TIME_MONTH');
3087
3088 if g_debug_flag = 'Y' then
3089 HRI_util.put_line('Gathered statistics for Month Level');
3090 end if;
3091
3092 gather_table_stats('HRI_TIME_QTR');
3093
3094 if g_debug_flag = 'Y' then
3095 HRI_util.put_line('Gathered statistics for Quarter Level');
3096 end if;
3097
3098 gather_table_stats('HRI_TIME_YEAR445');
3099
3100 if g_debug_flag = 'Y' then
3101 HRI_util.put_line('Gathered statistics for Period 445 Level');
3102 end if;
3103
3104 IF g_debug_flag = 'Y' THEN
3105 HRI_util.stop_timer;
3106 HRI_util.print_timer('Process Time');
3107 HRI_util.put_line(' ');
3108 END IF;
3109
3110 end if;
3111
3112 else
3113
3114 truncate_table('HRI_TIME_WEEK');
3115 truncate_table('HRI_TIME_P445');
3116 truncate_table('HRI_TIME_YEAR445');
3117 truncate_table('HRI_TIME_ENT_PERIOD');
3118 truncate_table('HRI_TIME_ENT_QTR');
3119 truncate_table('HRI_TIME_ENT_YEAR');
3120 truncate_table('HRI_TIME_CAL_PERIOD');
3121 truncate_table('HRI_TIME_CAL_QTR');
3122 truncate_table('HRI_TIME_CAL_YEAR');
3123 truncate_table('HRI_TIME_RPT_STRUCT');
3124 truncate_table('HRI_TIME_CAL_RPT_STRUCT');
3125
3126 if g_debug_flag = 'Y' then
3127 HRI_util.put_line(' ');
3128 end if;
3129
3130 end if;
3131
3132 ----------------------------------------------------------------
3133 -- Calling BIS API to record the range we load. Only do this
3134 -- when we have a successful loading
3135 ----------------------------------------------------------------
3136 /* if g_all_level = 'Y' then
3137
3138 BIS_COLLECTION_UTILITIES.wrapup
3139 ( p_status => TRUE
3140 , p_period_from => l_from_date
3141 , p_period_to => greatest( l_to_date
3142 , fnd_date.displaydt_to_date
3143 ( bis_collection_utilities.get_last_refresh_period
3144 ('HRI_DBI_TIME_M_F')
3145 )
3146 )
3147 , p_attribute1 => 'Y'
3148 );
3149
3150 else
3151
3152 BIS_COLLECTION_UTILITIES.wrapup (p_status => TRUE,
3153 p_period_from => l_from_date,
3154 p_period_to => l_to_date);
3155
3156
3157 end if;*/ -- Commented for HRI obsoletion
3158
3159 exception
3160 WHEN G_BIS_PARAMETER_NOT_SETUP THEN
3161 HRI_util.put_line(fnd_message.get_string('HRI', 'HRI_BIS_PARAMETER_NOT_SETUP'));
3162 retcode := -1;
3163 rollback;
3164 /*if l_bis_setup = 'Y' then
3165 BIS_COLLECTION_UTILITIES.wrapup
3166 ( p_status => FALSE
3167 , p_message => fnd_message.get_string('HRI', 'HRI_BIS_PARAMETER_NOT_SETUP')
3168 );
3169 end if;*/ -- Commented for HRI obsoletion
3170
3171 WHEN G_LOGIN_INFO_NOT_FOUND THEN
3172 HRI_util.put_line('Can not get User ID and Login ID, program exit');
3173 retcode := -1;
3174 rollback;
3175 /*if l_bis_setup = 'Y' then
3176 BIS_COLLECTION_UTILITIES.wrapup
3177 ( p_status => FALSE
3178 , p_message => 'Can not get User ID and Login ID, program exit'
3179 );
3180 end if;*/ -- Commented for HRI obsoletion
3181 WHEN G_ENT_CALENDAR_NOT_FOUND THEN
3182 rollback;
3183 --Bug 3640141. Setting token as the message text has changed.
3184 fnd_message.set_name('HRI','HRI_ENT_CALENDAR_NOT_FOUND');
3185 fnd_message.set_token('DATE_NOT_DEFINED', fnd_date.date_to_displaydate(g_date_not_defined));
3186 l_mesg := fnd_message.get;
3187 HRI_util.put_line(l_mesg);
3188 retcode := -1;
3189 /* if l_bis_setup = 'Y' then
3190 BIS_COLLECTION_UTILITIES.wrapup
3191 ( p_status => FALSE , p_message => l_mesg);
3192 end if;*/ -- Commented for HRI obsoletion
3193 WHEN G_YEAR_NOT_DEFINED THEN
3194 --Added for bug 4454026
3195 rollback;
3196 fnd_message.set_name('HRI','HRI_FISCAL_YEAR_NOT_DEFINED');
3197 fnd_message.set_token('YEAR', l_year);
3198 fnd_message.set_token('START_DATE', fnd_date.date_to_displaydate(l_min_start_date));
3199 fnd_message.set_token('END_DATE', fnd_date.date_to_displaydate(l_max_end_date));
3200 l_mesg := fnd_message.get;
3201 HRI_util.put_line(l_mesg);
3202 retcode := -1;
3203 /* if l_bis_setup = 'Y' then
3204 BIS_COLLECTION_UTILITIES.wrapup
3205 ( p_status => FALSE , p_message => l_mesg);
3206 end if;*/
3207 WHEN OTHERS THEN
3208 rollback;
3209 retcode := sqlcode;
3210 errbuf := sqlerrm;
3211 HRI_util.put_line(retcode||' : '||errbuf);
3212 HRI_util.put_line('
3213 -------------------------------------------
3214 Error occured in Procedure: LOAD
3215 Phase: ' || g_phase);
3216 /*if l_bis_setup = 'Y' then
3217 BIS_COLLECTION_UTILITIES.wrapup
3218 ( p_status => FALSE
3219 , p_message => substr(sqlerrm,1,4000)
3220 );
3221 end if;*/
3222
3223 end LOAD;
3224
3225 ---------------------------------------------------
3226 -- PUBLIC PROCEDURE LOAD_CAL_NAME
3227 -- The procedure was changed to public per PJI's request
3228 ---------------------------------------------------
3229 PROCEDURE LOAD_CAL_NAME IS
3230
3231 -- ---------------------------------------------------------
3232 -- Define local variables
3233 -- ---------------------------------------------------------
3234 l_status VARCHAR2(30);
3235 l_industry VARCHAR2(30);
3236 l_name_row number;
3237 l_max_cal_name number;
3238
3239 cursor new_cal is
3240 SELECT inline_view.period_set_name, inline_view.period_type, MAX(gp.creation_date)
3241 FROM ( SELECT DISTINCT glp.period_set_name , glp.period_type
3242 FROM gl_periods glp
3243 MINUS
3244 SELECT DISTINCT cal.period_set_name, cal.period_type
3245 FROM HRI_time_cal_name cal
3246 ) inline_view,
3247 gl_periods gp
3248 WHERE inline_view.period_set_name = gp.period_set_name
3249 and inline_view.period_type = gp.period_type
3250
3251 GROUP BY inline_view.period_set_name, inline_view.period_type
3252 ORDER BY MAX(gp.creation_date);
3253
3254 begin
3255
3256 -- ---------------------------------------------------------
3257 -- Variable initialization
3258 -- ---------------------------------------------------------
3259 l_name_row := 0;
3260
3261 select nvl(max(calendar_id),0)
3262 into l_max_cal_name
3263 from HRI_time_cal_name;
3264
3265 if g_schema is null then
3266 IF(FND_INSTALLATION.GET_APP_INFO('HRI', l_status, l_industry, g_schema)) THEN
3267 NULL;
3268 END IF;
3269 end if;
3270
3271 -- ----------------------
3272 -- Populate Calendar Name Level
3273 -- ----------------------
3274 FOR new_cal_rec IN new_cal LOOP
3275
3276 insert into HRI_time_cal_name
3277 (calendar_id,
3278 period_set_name,
3279 period_type,
3280 name,
3281 creation_date,
3282 last_update_date,
3283 last_updated_by,
3284 created_by,
3285 last_update_login)
3286 values(
3287 l_max_cal_name+1,
3288 new_cal_rec.period_set_name,
3289 new_cal_rec.period_type,
3290 new_cal_rec.period_set_name||' ('||new_cal_rec.period_type||')',
3291 sysdate,
3292 sysdate,
3293 g_user_id,
3294 g_user_id,
3295 g_login_id);
3296
3297 l_max_cal_name := l_max_cal_name+1;
3298 l_name_row := l_name_row+1;
3299
3300 end loop;
3301
3302 commit;
3303
3304 if g_debug_flag = 'Y' then
3305 HRI_util.put_line('LOAD_CAL_NAME : '||to_char(l_name_row)||' records has been populated to Calendar Name Level');
3306 end if;
3307
3308 if l_name_row > 0 then
3309
3310 gather_table_stats('HRI_TIME_CAL_NAME');
3311
3312 if g_debug_flag = 'Y' then
3313 HRI_util.put_line('LOAD_CAL_NAME : '||'Gathered statistics for Calendar Name Level');
3314 end if;
3315
3316 end if;
3317
3318 end LOAD_CAL_NAME;
3319
3320 ---------------------------------------------------
3321 -- PUBLIC PROCEDURE LOAD_TIME_RPT_STRUCT
3322 -- the from and to dates are actually ignored.
3323 ---------------------------------------------------
3324 PROCEDURE LOAD_TIME_RPT_STRUCT(p_from_date in date, p_to_date in date) IS
3325
3326 begin
3327
3328 INIT;
3329
3330 LOAD_TIME_RPT_STRUCT;
3331
3332 end LOAD_TIME_RPT_STRUCT;
3333
3334 ---------------------------------------------------
3335 -- PRIVATE PROCEDURE LOAD_TIME_RPT_STRUCT
3336 ---------------------------------------------------
3337 PROCEDURE LOAD_TIME_RPT_STRUCT IS
3338
3339 -- ---------------------------------------------------------
3340 -- Define local variables
3341 -- ---------------------------------------------------------
3342 --l_from_date date;
3343 --l_to_date date;
3344 l_day date;
3345 l_week_start_date date;
3346 l_ptd_to_date date;
3347 l_period_start_date date;
3348 l_row number;
3349
3350 --* We should do a full refresh for HRI_TIME_RPT_STRUCT
3351 -- Bug 5624487
3352 cursor c1 is
3353 select report_date, ent_period_start_date, ent_qtr_start_date,
3354 ent_year_start_date, week_start_date
3355 from HRI_TIME_DAY
3356 where report_date <> g_unassigned_day;
3357 --*this would be incorrect: where report_date between l_from_date and l_to_date
3358
3359 begin
3360
3361 truncate_table('HRI_TIME_RPT_STRUCT');
3362
3363 l_row := 0;
3364 --l_from_date := trunc(nvl(p_from_date,trunc(add_months(sysdate,-24),'YYYY')));
3365 --l_to_date := trunc(nvl(p_to_date,trunc(sysdate,'YYYY')));
3366
3367 FOR c1_rec IN c1 LOOP
3368 insert into HRI_TIME_RPT_STRUCT
3369 (calendar_id,
3370 calendar_type,
3371 report_date,
3372 time_id,
3373 period_type_id,
3374 record_type_id,
3375 creation_date,
3376 last_update_date,
3377 last_updated_by,
3378 created_by,
3379 last_update_login)
3383 c1_rec.report_date,
3380 values
3381 (-1,
3382 'C',
3384 to_char(c1_rec.report_date,'j'),
3385 1,
3386 1,
3387 sysdate,
3388 sysdate,
3389 g_user_id,
3390 g_user_id,
3391 g_login_id);
3392
3393 l_row := l_row+1;
3394 l_day:=c1_rec.report_date-1;
3395 l_week_start_date:=c1_rec.week_start_date;
3396 l_period_start_date:=c1_rec.ent_period_start_date;
3397
3398 While l_day >= least(l_week_start_date,l_period_start_date) LOOP
3399 if l_day >= l_period_start_date then
3400 if l_day >= l_week_start_date then
3401 insert into HRI_TIME_RPT_STRUCT
3402 (calendar_id,
3403 calendar_type,
3404 report_date,
3405 time_id,
3406 period_type_id,
3407 record_type_id,
3408 creation_date,
3409 last_update_date,
3410 last_updated_by,
3411 created_by,
3412 last_update_login)
3413 values
3414 (-1,
3415 'C',
3416 c1_rec.report_date,
3417 to_char(l_day,'j'),
3418 1,
3419 2,
3420 sysdate,
3421 sysdate,
3422 g_user_id,
3423 g_user_id,
3424 g_login_id);
3425
3426 l_row := l_row + sql%rowcount;
3427
3428 else
3429 select nvl(min(start_date),l_week_start_date) into l_ptd_to_date from HRI_TIME_WEEK
3430 where start_date >= l_period_start_date
3431 and start_date < l_week_start_date;
3432
3433 if l_day < l_ptd_to_date then
3434 insert into HRI_TIME_RPT_STRUCT
3435 (calendar_id,
3436 calendar_type,
3437 report_date,
3438 time_id,
3439 period_type_id,
3440 record_type_id,
3441 creation_date,
3442 last_update_date,
3443 last_updated_by,
3444 created_by,
3445 last_update_login)
3446 values
3447 (-1,
3448 'C',
3449 c1_rec.report_date,
3450 to_char(l_day,'j'),
3451 1,
3452 4,
3453 sysdate,
3454 sysdate,
3455 g_user_id,
3456 g_user_id,
3457 g_login_id);
3458
3459 l_row := l_row + sql%rowcount;
3460
3461 end if;
3462 end if;
3463 else
3464 if l_day >= l_week_start_date then
3465 insert into HRI_TIME_RPT_STRUCT
3466 (calendar_id,
3467 calendar_type,
3468 report_date,
3469 time_id,
3470 period_type_id,
3471 record_type_id,
3472 creation_date,
3473 last_update_date,
3474 last_updated_by,
3475 created_by,
3476 last_update_login)
3477 values
3478 (-1,
3479 'C',
3480 c1_rec.report_date,
3481 to_char(l_day,'j'),
3482 1,
3483 8,
3484 sysdate,
3485 sysdate,
3486 g_user_id,
3487 g_user_id,
3488 g_login_id);
3489
3490 l_row := l_row + sql%rowcount;
3491
3492 end if;
3493 end if;
3494
3495 l_day:=l_day-1;
3496
3497 END LOOP;
3498
3499 commit;
3500
3501 insert into HRI_TIME_RPT_STRUCT
3502 (calendar_id,
3503 calendar_type,
3504 report_date,
3505 time_id,
3506 period_type_id,
3507 record_type_id,
3508 creation_date,
3509 last_update_date,
3510 last_updated_by,
3511 created_by,
3512 last_update_login)
3513 select
3514 -1,
3515 'E',
3516 c1_rec.report_date,
3517 week_id,
3518 16,
3519 16,
3520 sysdate,
3521 sysdate,
3522 g_user_id,
3523 g_user_id,
3524 g_login_id
3525 from HRI_TIME_WEEK
3526 where start_date >= c1_rec.ent_period_start_date
3527 and end_date < c1_rec.week_start_date
3528 union all
3529 select
3530 -1,
3531 'E',
3532 c1_rec.report_date,
3533 week_id,
3534 16,
3535 2048,
3536 sysdate,
3537 sysdate,
3538 g_user_id,
3539 g_user_id,
3540 g_login_id
3541 from HRI_TIME_WEEK
3542 where c1_rec.report_date between start_date and end_date;
3543
3544 l_row := l_row + sql%rowcount;
3545
3546 commit;
3547
3548 insert into HRI_TIME_RPT_STRUCT
3549 (calendar_id,
3550 calendar_type,
3551 report_date,
3552 time_id,
3553 period_type_id,
3554 record_type_id,
3555 creation_date,
3556 last_update_date,
3557 last_updated_by,
3558 created_by,
3559 last_update_login)
3560 select
3561 -1,
3562 'E',
3563 c1_rec.report_date,
3564 ent_period_id,
3565 32,
3566 32,
3567 sysdate,
3568 sysdate,
3569 g_user_id,
3570 g_user_id,
3571 g_login_id
3572 from HRI_TIME_ENT_PERIOD
3573 where start_date >= c1_rec.ent_qtr_start_date
3574 and start_date <= c1_rec.ent_period_start_date
3575 and end_date < c1_rec.report_date
3576 union all
3577 select
3578 -1,
3579 'E',
3580 c1_rec.report_date,
3581 ent_period_id,
3582 32,
3583 256,
3584 sysdate,
3585 sysdate,
3586 g_user_id,
3587 g_user_id,
3588 g_login_id
3589 from HRI_TIME_ENT_PERIOD
3590 where start_date >= c1_rec.ent_qtr_start_date
3591 and start_date <= c1_rec.ent_period_start_date
3592 and end_date >= c1_rec.report_date;
3593
3594 l_row := l_row + sql%rowcount;
3595
3596 commit;
3597
3598 insert into HRI_TIME_RPT_STRUCT
3599 (calendar_id,
3600 calendar_type,
3601 report_date,
3602 time_id,
3603 period_type_id,
3604 record_type_id,
3605 creation_date,
3606 last_update_date,
3607 last_updated_by,
3608 created_by,
3609 last_update_login)
3610 select
3611 -1,
3612 'E',
3613 c1_rec.report_date,
3614 ent_qtr_id,
3615 64,
3616 64,
3617 sysdate,
3618 sysdate,
3619 g_user_id,
3620 g_user_id,
3621 g_login_id
3622 from HRI_TIME_ENT_QTR
3623 where start_date >= c1_rec.ent_year_start_date
3624 and start_date <= c1_rec.ent_qtr_start_date
3625 and end_date < c1_rec.report_date
3626 union all
3627 select
3628 -1,
3629 'E',
3630 c1_rec.report_date,
3631 ent_qtr_id,
3632 64,
3633 512,
3634 --case when end_date >= c1_rec.report_date then 512 else 64 end,
3635 sysdate,
3636 sysdate,
3637 g_user_id,
3638 g_user_id,
3639 g_login_id
3640 from HRI_TIME_ENT_QTR
3641 where start_date >= c1_rec.ent_year_start_date
3642 and start_date <= c1_rec.ent_qtr_start_date
3643 and end_date >= c1_rec.report_date;
3644
3645 l_row := l_row + sql%rowcount;
3646
3647 commit;
3648
3649 insert into HRI_TIME_RPT_STRUCT
3650 (calendar_id,
3651 calendar_type,
3652 report_date,
3653 time_id,
3654 period_type_id,
3655 record_type_id,
3656 creation_date,
3657 last_update_date,
3658 last_updated_by,
3659 created_by,
3660 last_update_login)
3661 select
3662 -1,
3663 'E',
3664 c1_rec.report_date,
3665 ent_year_id,
3666 128,
3667 128,
3668 sysdate,
3669 sysdate,
3670 g_user_id,
3671 g_user_id,
3672 g_login_id
3673 from HRI_TIME_ENT_YEAR
3674 where c1_rec.report_date between start_date and end_date;
3675
3676 l_row := l_row + sql%rowcount;
3677
3678 commit;
3679
3680 -- All prior years (report_type: 1024), for ITD
3681 insert into HRI_TIME_RPT_STRUCT
3682 (calendar_id,
3683 calendar_type,
3684 report_date,
3685 time_id,
3686 period_type_id,
3687 record_type_id,
3688 creation_date,
3689 last_update_date,
3690 last_updated_by,
3691 created_by,
3692 last_update_login)
3693 select
3694 -1,
3695 'E',
3696 c1_rec.report_date,
3697 ent_year_id,
3698 128,
3699 1024,
3700 sysdate,
3701 sysdate,
3702 g_user_id,
3703 g_user_id,
3704 g_login_id
3705 from HRI_TIME_ENT_YEAR
3706 where end_date >= g_global_start_date -- should we use start_date?
3707 and end_date < c1_rec.report_date;
3708
3709 l_row := l_row + sql%rowcount;
3710
3711 commit;
3712
3713 END LOOP; -- c1_rec
3714
3715 if g_debug_flag = 'Y' then
3716 HRI_util.put_line('LOAD_TIME_RPT_STRUCT :'||to_char(l_row)||' records has been populated to the Reporting Structure table');
3717 end if;
3718
3719 gather_table_stats('HRI_TIME_RPT_STRUCT');
3720
3721 if g_debug_flag = 'Y' then
3722 HRI_util.put_line('LOAD_TIME_RPT_STRUCT :'||'Gathered statistics for the Reporting Structure table');
3723 end if;
3724
3725 end LOAD_TIME_RPT_STRUCT;
3726
3727
3728 ---------------------------------------------------
3729 -- PUBLIC PROCEDURE LOAD_TIME_CAL_RPT_STRUCT
3730 ---------------------------------------------------
3731 PROCEDURE LOAD_TIME_CAL_RPT_STRUCT(p_from_date in date, p_to_date in date) IS
3732
3733 -- ---------------------------------------------------------
3734 -- Define local variables
3735 -- ---------------------------------------------------------
3736 l_Day DATE;
3737 l_Week_Start_Date DATE;
3738 l_Earliest_Week DATE;
3739 l_Row NUMBER:=0;
3740 l_No_Week_Info VARCHAR2(1):='N';
3741 l_from_date DATE:=p_from_date;
3742 l_to_date DATE:=p_to_date;
3743 l_full_extraction_flag varchar2(1);
3744 l_max_date_v varchar2(100);
3745 l_min_date date;
3746 l_max_date date;
3747
3748 begin
3749
3750 --Bug 3543939. Get the minimum from date and maximum to date
3751 SELECT nvl(least(min(report_date),l_from_date),l_from_date),nvl(greatest(max(report_date),l_to_date),l_to_date)
3752 INTO l_min_date,l_max_date
3753 from HRI_TIME_CAL_RPT_STRUCT;
3754
3755 --Bug 3543939. The Calendar id's to be picked from the temporary table populated in Load_Cal_Period
3756
3757 For cur_Fiscal_Calendar IN (
3758 SELECT distinct calendar_id
3759 FROM HRI_time_cal_gt) LOOP
3760
3761 --Reset the l_from_date and l_to_date to the parameters passed
3762
3763 l_from_date:=p_from_date;
3764 l_to_date:=p_to_date;
3765
3766 --Check if the from date has been already extracted
3767
3768 BEGIN
3769
3770 SELECT 'T'
3771 INTO l_full_extraction_flag
3772 FROM HRI_TIME_CAL_RPT_STRUCT
3773 WHERE REPORT_DATE >= p_from_date
3774 AND CALENDAR_ID = cur_Fiscal_Calendar.CALENDAR_ID
3775 AND ROWNUM <= 1;
3776 EXCEPTION
3777 WHEN NO_DATA_FOUND THEN
3778 l_full_extraction_flag := 'F';
3779 END;
3780
3781
3782 IF l_full_extraction_flag = 'T' THEN
3783
3784 -- Delete all records for the calendar
3785 -- and reset the from and to date for
3786 -- extraction
3787
3788 DELETE HRI_TIME_CAL_RPT_STRUCT
3789 WHERE CALENDAR_ID = cur_Fiscal_Calendar.CALENDAR_ID;
3790 l_from_date := l_min_date;
3791 l_to_date := l_max_date;
3792
3793 END IF;
3794
3795 FOR cur_Fiscal_Days IN (
3796 SELECT calendar_id
3797 , report_date
3798 , cal_period_start_date period_start_date
3799 , cal_qtr_start_date qtr_start_date
3800 , cal_year_start_date year_start_date
3801 FROM HRI_time_cal_day_mv
3802 WHERE calendar_id = cur_Fiscal_Calendar.calendar_id
3803 AND report_date BETWEEN l_from_date AND l_to_date) LOOP
3804
3805 INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3806 ( calendar_id
3807 , calendar_type
3808 , report_date
3809 , time_id
3810 , period_type_id
3811 , record_type_id
3812 , creation_date
3813 , last_update_date
3814 , last_updated_by
3815 , created_by
3816 , last_update_login)
3817 VALUES
3818 (cur_Fiscal_Days.calendar_id
3819 , 'C'
3820 , cur_Fiscal_Days.report_date
3821 , TO_CHAR(cur_Fiscal_Days.report_date,'j')
3822 , 1
3823 , 1
3824 , SYSDATE
3825 , SYSDATE
3826 , g_user_id
3827 , g_user_id
3828 , g_login_id);
3829
3830 l_Row := l_Row + SQL%ROWCOUNT;
3831
3832 COMMIT;
3833
3834 l_Day := cur_Fiscal_Days.report_date-1;
3835
3836 BEGIN
3837 SELECT week_start_date
3838 INTO l_Week_Start_Date
3839 FROM HRI_time_day
3840 WHERE report_date = cur_Fiscal_Days.report_date;
3841
3842 l_No_Week_Info := 'N';
3843
3844 EXCEPTION
3845 WHEN NO_DATA_FOUND THEN
3846 l_No_Week_Info := 'Y';
3847 END;
3848
3849 IF l_No_Week_Info ='N' THEN
3850 SELECT NVL(MIN(start_date),l_Week_Start_Date)
3851 INTO l_Earliest_Week
3852 FROM HRI_time_week
3853 WHERE start_date >= cur_Fiscal_Days.period_start_date
3854 AND start_date < l_Week_Start_Date;
3855
3856 WHILE l_Day >= cur_Fiscal_Days.period_start_date LOOP
3857 IF l_Day >= l_Week_Start_Date THEN
3858 INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3859 ( calendar_id
3860 , calendar_type
3861 , report_date
3862 , time_id
3863 , period_type_id
3864 , record_type_id
3865 , creation_date
3866 , last_update_date
3867 , last_updated_by
3868 , created_by
3869 , last_update_login)
3870 VALUES
3871 (cur_Fiscal_Days.calendar_id
3872 , 'C'
3873 , cur_Fiscal_Days.report_date
3874 , TO_CHAR(l_Day,'j')
3875 , 1
3876 , 2
3877 , SYSDATE
3878 , SYSDATE
3879 , g_user_id
3880 , g_user_id
3881 , g_login_id);
3882
3883 l_Row := l_Row + SQL%ROWCOUNT;
3884
3885 ELSIF l_Day >= cur_Fiscal_Days.period_start_date AND l_Day < l_Earliest_Week THEN
3886 INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3887 ( calendar_id
3888 , calendar_type
3889 , report_date
3890 , time_id
3891 , period_type_id
3892 , record_type_id
3893 , creation_date
3894 , last_update_date
3895 , last_updated_by
3896 , created_by
3897 , last_update_login)
3898 VALUES
3899 (cur_Fiscal_Days.calendar_id
3900 , 'C'
3901 , cur_Fiscal_Days.report_date
3902 , TO_CHAR(l_Day,'j')
3903 , 1
3904 , 4
3905 , SYSDATE
3906 , SYSDATE
3907 , g_user_id
3908 , g_user_id
3909 , g_login_id);
3910
3914 l_Day := l_Day-1;
3911 l_Row := l_Row + SQL%ROWCOUNT;
3912 END IF;
3913
3915
3916 END LOOP;
3917
3918 COMMIT;
3919
3920 INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3921 ( calendar_id
3922 , calendar_type
3923 , report_date
3924 , time_id
3925 , period_type_id
3926 , record_type_id
3927 , creation_date
3928 , last_update_date
3929 , last_updated_by
3930 , created_by
3931 , last_update_login)
3932 SELECT
3933 cur_Fiscal_Days.calendar_id
3934 , 'E'
3935 , cur_Fiscal_Days.report_date
3936 , week_id
3937 , 16
3938 , 16
3939 , SYSDATE
3940 , SYSDATE
3941 , g_user_id
3942 , g_user_id
3943 , g_login_id
3944 FROM HRI_TIME_WEEK
3945 WHERE start_date >= cur_Fiscal_Days.period_start_date
3946 AND end_date < l_Week_Start_Date;
3947
3948 l_Row := l_Row + SQL%ROWCOUNT;
3949
3950 COMMIT;
3951
3952 ELSE
3953 INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3954 ( calendar_id
3955 , calendar_type
3956 , report_date
3957 , time_id
3958 , period_type_id
3959 , record_type_id
3960 , creation_date
3961 , last_update_date
3962 , last_updated_by
3963 , created_by
3964 , last_update_login)
3965 SELECT
3966 cur_Fiscal_Days.calendar_id
3967 , 'C'
3968 , cur_Fiscal_Days.report_date
3969 , report_date_julian
3970 , 1
3971 , 4
3972 , SYSDATE
3973 , SYSDATE
3974 , g_user_id
3975 , g_user_id
3976 , g_login_id
3977 FROM HRI_TIME_CAL_DAY_MV
3978 WHERE calendar_id = cur_Fiscal_Days.calendar_id
3979 AND report_date BETWEEN cur_Fiscal_Days.period_start_date AND l_Day;
3980
3981 l_Row := l_Row + SQL%ROWCOUNT;
3982
3983 COMMIT;
3984 END IF;
3985
3986 INSERT INTO HRI_TIME_CAL_RPT_STRUCT
3987 ( calendar_id
3988 , calendar_type
3989 , report_date
3990 , time_id
3991 , period_type_id
3992 , record_type_id
3993 , creation_date
3994 , last_update_date
3995 , last_updated_by
3996 , created_by
3997 , last_update_login)
3998 SELECT
3999 cur_Fiscal_Days.calendar_id
4000 , 'G'
4001 , cur_Fiscal_Days.report_date
4002 , cal_period_id
4003 , 32
4004 , 32
4005 , SYSDATE
4006 , SYSDATE
4007 , g_user_id
4008 , g_user_id
4009 , g_login_id
4010 FROM HRI_TIME_CAL_PERIOD
4011 WHERE start_date >= cur_Fiscal_Days.qtr_start_date
4012 AND start_date <= cur_Fiscal_Days.period_start_date
4013 AND end_date < cur_Fiscal_Days.report_date
4014 AND calendar_id = cur_Fiscal_Days.calendar_id
4015 UNION ALL
4016 SELECT
4017 cur_Fiscal_Days.calendar_id
4018 , 'G'
4019 , cur_Fiscal_Days.report_date
4020 , cal_period_id
4021 , 32
4022 , 256
4023 , SYSDATE
4024 , SYSDATE
4025 , g_user_id
4026 , g_user_id
4027 , g_login_id
4028 FROM HRI_TIME_CAL_PERIOD
4029 WHERE start_date >= cur_Fiscal_Days.qtr_start_date
4030 AND start_date <= cur_Fiscal_Days.period_start_date
4031 AND end_date >= cur_Fiscal_Days.report_date
4032 AND calendar_id = cur_Fiscal_Days.calendar_id;
4033
4034 l_Row := l_Row + SQL%ROWCOUNT;
4035
4036 COMMIT;
4037
4038 INSERT INTO HRI_TIME_CAL_RPT_STRUCT
4039 ( calendar_id
4040 , calendar_type
4041 , report_date
4042 , time_id
4043 , period_type_id
4044 , record_type_id
4045 , creation_date
4046 , last_update_date
4047 , last_updated_by
4048 , created_by
4049 , last_update_login)
4050 SELECT
4051 cur_Fiscal_Days.calendar_id
4052 , 'G'
4053 , cur_Fiscal_Days.report_date
4054 , cal_qtr_id
4055 , 64
4056 , 64
4057 , SYSDATE
4058 , SYSDATE
4059 , g_user_id
4060 , g_user_id
4061 , g_login_id
4062 FROM HRI_TIME_CAL_QTR
4063 WHERE start_date >= cur_Fiscal_Days.year_start_date
4064 AND start_date <= cur_Fiscal_Days.qtr_start_date
4065 AND end_date < cur_Fiscal_Days.report_date
4066 AND calendar_id = cur_Fiscal_Days.calendar_id
4067 UNION ALL
4068 SELECT
4069 cur_Fiscal_Days.calendar_id
4070 , 'G'
4071 , cur_Fiscal_Days.report_date
4072 , cal_qtr_id
4073 , 64
4074 , 512
4075 , SYSDATE
4076 , SYSDATE
4077 , g_user_id
4078 , g_user_id
4079 , g_login_id
4080 FROM HRI_TIME_CAL_QTR
4081 WHERE start_date >= cur_Fiscal_Days.year_start_date
4082 AND start_date <= cur_Fiscal_Days.qtr_start_date
4086 l_Row := l_Row + SQL%ROWCOUNT;
4083 AND end_date >= cur_Fiscal_Days.report_date
4084 AND calendar_id = cur_Fiscal_Days.calendar_id;
4085
4087
4088 COMMIT;
4089
4090 INSERT INTO HRI_TIME_CAL_RPT_STRUCT
4091 ( calendar_id
4092 , calendar_type
4093 , report_date
4094 , time_id
4095 , period_type_id
4096 , record_type_id
4097 , creation_date
4098 , last_update_date
4099 , last_updated_by
4100 , created_by
4101 , last_update_login)
4102 SELECT
4103 cur_Fiscal_Days.calendar_id
4104 , 'G'
4105 , cur_Fiscal_Days.report_date
4106 , cal_year_id
4107 , 128
4108 , 128
4109 , SYSDATE
4110 , SYSDATE
4111 , g_user_id
4112 , g_user_id
4113 , g_login_id
4114 FROM HRI_TIME_CAL_YEAR
4115 WHERE calendar_id = cur_Fiscal_Days.calendar_id
4116 AND cur_Fiscal_Days.report_date BETWEEN start_date AND end_date
4117 UNION ALL
4118 SELECT
4119 cur_Fiscal_Days.calendar_id
4120 , 'G'
4121 , cur_Fiscal_Days.report_date
4122 , cal_year_id
4123 , 128
4124 , 1024
4125 , SYSDATE
4126 , SYSDATE
4127 , g_user_id
4128 , g_user_id
4129 , g_login_id
4130 FROM HRI_TIME_CAL_YEAR
4131 WHERE calendar_id = cur_Fiscal_Days.calendar_id
4132 AND end_date < cur_Fiscal_Days.report_date
4133 AND end_date >= g_global_start_date;
4134
4135 l_Row := l_Row + SQL%ROWCOUNT;
4136
4137 COMMIT;
4138
4139 END LOOP;
4140
4141 END LOOP;
4142
4143 IF g_debug_flag = 'Y' THEN
4144 HRI_util.put_line('LOAD_TIME_CAL_RPT_STRUCT : '||TO_CHAR(l_row)||' records has been populated to the Reporting Structure table for Financial Calendars');
4145 END IF;
4146
4147 if l_row > 0 then
4148
4149 gather_table_stats('HRI_TIME_CAL_RPT_STRUCT');
4150
4151 if g_debug_flag = 'Y' then
4152 HRI_util.put_line('LOAD_TIME_CAL_RPT_STRUCT :'||'Gathered statistics for Financial Calendars');
4153 end if;
4154
4155 end if;
4156
4157 end LOAD_TIME_CAL_RPT_STRUCT;
4158
4159 ---------------------------------------------------
4160 -- PUBLIC FUNCTION DEFAULT_LOAD_FROM_DATE
4161 -- this function is used to return the default load
4162 -- from date to the concurrent program parameter
4163 ---------------------------------------------------
4164 FUNCTION DEFAULT_LOAD_FROM_DATE(p_load_mode in varchar2)
4165 return varchar2
4166 is
4167
4168 l_return_date date;
4169
4170 begin
4171
4172 if p_load_mode = 'INCRE' then
4173 if check_validated = 'Y' then
4174 l_return_date := least(fnd_date.displaydt_to_date
4175 ( bis_collection_utilities.get_last_refresh_period
4176 ('HRI_DBI_TIME_M')
4177 ) +1, fnd_date.displaydt_to_date(DEFAULT_LOAD_TO_DATE));
4178 else
4179 l_return_date := bis_common_parameters.get_global_start_date;
4180 end if;
4181 else
4182 select least(nvl(min(start_date),bis_common_parameters.get_global_start_date) , bis_common_parameters.get_global_start_date) into l_return_date
4183 from HRI_time_day;
4184 end if;
4185
4186 return fnd_date.date_to_displaydt(l_return_date);
4187
4188 end DEFAULT_LOAD_FROM_DATE;
4189
4190 ---------------------------------------------------
4191 -- PUBLIC FUNCTION DEFAULT_LOAD_TO_DATE
4192 -- this function is used to return the default load
4193 -- to date to the concurrent program parameter
4194 ---------------------------------------------------
4195 FUNCTION DEFAULT_LOAD_TO_DATE
4196 return varchar2
4197 is
4198
4199 l_return_date date;
4200 l_period_set_name varchar2(15) := bis_common_parameters.get_period_set_name;
4201 l_period_type varchar2(15) := bis_common_parameters.get_period_type;
4202
4203 begin
4204
4205 select max(end_date)
4206 into l_return_date
4207 from gl_periods
4208 where adjustment_period_flag = 'N'
4209 and period_set_name = l_period_set_name
4210 and period_type = l_period_type;
4211
4212 return fnd_date.date_to_displaydt(l_return_date);
4213
4214 end DEFAULT_LOAD_TO_DATE;
4215
4216 END HRI_TIME_C;