[Home] [Help]
PACKAGE BODY: APPS.FII_TIME_C
Source
1 PACKAGE BODY FII_TIME_C AS
2 /*$Header: FIICMT1B.pls 120.49 2007/03/01 07:22:08 arcdixit ship $*/
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('FII_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 FII_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 => 'FII_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 FII_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 fii_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 FII_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 fii_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('FII', 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 fii_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 fii_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 fii_util.put_line('INIT : '||'Week offset = '||g_week_offset);
353 fii_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 fii_util.put_line('INIT : '||'Global Start Date = ' ||
362 fnd_date.date_to_displaydate(g_global_start_date));
363 fii_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 fii_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 fii_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 fii_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 fii_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('FII_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 fii_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 fii_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 fii_time_month;
770 END IF;
771
772 -- Bug 5624487
773 -- delete from FII_TIME_MONTH where start_date <= l_to_date and end_date >= l_from_date;
774 delete from FII_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 fii_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 fii_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 fii_time_qtr;
879 END IF;
880
881 -- Bug 5624487
882 -- delete from FII_TIME_QTR where start_date <= l_to_date and end_date >= l_from_date;
883 delete from FII_TIME_QTR
884 where start_date <= l_to_date and end_date >= l_from_date
885 or end_date >= g_unassigned_day;
886
887 -- ----------------------
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 fii_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 fii_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 fii_time_year;
986 END IF;
987
988 -- Bug 5624487
989 -- delete from FII_TIME_YEAR where start_date <= l_to_date and end_date >= l_from_date;
990 delete from FII_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 fii_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;
1048
1049 end loop;
1050 commit;
1051
1052 if g_debug_flag = 'Y' then
1053 fii_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 FII_TIME_WEEK where start_date <= l_to_date and end_date >= l_from_date;
1098 delete from FII_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 fii_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 fii_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 fii_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 FII_TIME_P445 where start_date <= l_to_date and end_date >= l_from_date;
1230 delete from FII_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 fii_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 fii_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 FII_TIME_YEAR445 where start_date <= l_to_date and end_date >= l_from_date;
1372 delete from FII_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 fii_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 fii_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 fii_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 fii_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 fii_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 FII_TIME_ENT_PERIOD where start_date <= l_to_date and end_date >= l_from_date;
1507 delete from FII_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 fii_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 fii_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 fii_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 fii_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 fii_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 fii_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 fii_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 fii_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 FII_TIME_ENT_QTR where start_date <= l_to_date and end_date >= l_from_date;
1691 delete from FII_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 fii_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('FII','FII_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 fii_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 fii_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 fii_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('FII','FII_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 fii_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 fii_time_ent_period
1845 where end_date < g_unassigned_day;
1846
1847 -- Bug 5624487
1848 delete from FII_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 fii_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 fii_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 fii_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 fii 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 fii_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 , fii_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 fii_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 fii_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 , fii_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 fii_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 FII_TIME_CAL_PERIOD
2042
2043 -- Bug 4966868: Changed not to count rows
2044 BEGIN
2045 select 1 into l_diff_rows
2046 from fii_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 fii_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('FII_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 fii_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, fii_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 fii_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('FII_TIME_CAL_PERIOD');
2115
2116 if g_debug_flag = 'Y' then
2117 fii_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 fii 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 , fii_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 fii_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 fii_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 , fii_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 fii_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('FII_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 fii_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, fii_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 fii_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('FII_TIME_CAL_QTR');
2327
2328 if g_debug_flag = 'Y' then
2329 fii_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 fii 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 , fii_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 fii_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 fii_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 , fii_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 fii_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('FII_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 fii_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, fii_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 fii_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('FII_TIME_CAL_YEAR');
2516
2517 if g_debug_flag = 'Y' then
2518 fii_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:=FII_UTIL.get_utl_file_dir;
2560 FII_UTIL.initialize('FII_DBI_TIME_M.log','FII_DBI_TIME_M.out',l_dir, 'FII_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 fii_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('FII_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 fii_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 fii_util.put_line('LOAD : '||'Time Dimension will be validated.');
2616 fii_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 fii_time_ent_period and fii_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('FII_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 fii_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 fii_util.put_line('LOAD : '||'This is an initial load, all tables will be truncated and re-populated.');
2716 end if;
2717
2718 truncate_table('FII_TIME_DAY');
2719 truncate_table('FII_TIME_MONTH');
2720 truncate_table('FII_TIME_QTR');
2721 truncate_table('FII_TIME_YEAR');
2722 truncate_table('FII_TIME_WEEK');
2723 truncate_table('FII_TIME_P445');
2724 truncate_table('FII_TIME_YEAR445');
2725 truncate_table('FII_TIME_ENT_PERIOD');
2726 truncate_table('FII_TIME_ENT_QTR');
2727 truncate_table('FII_TIME_ENT_YEAR');
2728 truncate_table('FII_TIME_CAL_NAME');
2729 truncate_table('FII_TIME_CAL_PERIOD');
2730 truncate_table('FII_TIME_CAL_QTR');
2731 truncate_table('FII_TIME_CAL_YEAR');
2732 truncate_table('FII_TIME_RPT_STRUCT');
2733 truncate_table('FII_TIME_CAL_RPT_STRUCT');
2734 truncate_table('FII_TIME_STRUCTURES');
2735 truncate_table('FII_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 fii_util.put_line('LOAD : '||'l_validated='||l_validated);
2741 fii_util.put_line('');
2742 end if;
2743 if l_validated = 'Y' then
2744 --truncate_table('FII_TIME_GL_PERIODS');
2745 --l_period_updated := period_updated(l_min_date, l_max_date);
2746 if g_debug_flag = 'Y' then
2747 fii_util.put_line('LOAD : '||'l_period_updated='||l_period_updated);
2748 fii_util.put_line('');
2749 end if;
2750 if l_period_updated = 'Y' then
2751 truncate_table('FII_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('FII_TIME_ENT_QTR');
2759 truncate_table('FII_TIME_ENT_YEAR');
2760 end if;
2761 end if;
2762
2763 g_phase := 'Load Day Level';
2764 if g_debug_flag = 'Y' then
2765 fii_util.put_line(' ');
2766 fii_util.start_timer;
2767 end if;
2768
2769 -- we compare the latest end date defined in GL and the latest end date in fii_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 fii_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 FII_TIME_DAY or the from date parameter, see which one is the earliest
2794 -- to the latest date in FII_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 fii_util.stop_timer;
2803 fii_util.print_timer('Process Time');
2804 fii_util.put_line(' ');
2805 end if;
2806
2807 g_phase := 'Load Month Level';
2808 if g_debug_flag = 'Y' then
2809 fii_util.start_timer;
2810 end if;
2811 LOAD_MONTH(l_from_date, l_to_date);
2812 if g_debug_flag = 'Y' then
2813 fii_util.stop_timer;
2814 fii_util.print_timer('Process Time');
2815 fii_util.put_line(' ');
2816 end if;
2817
2818 g_phase := 'Load Quarter Level';
2819 if g_debug_flag = 'Y' then
2820 fii_util.start_timer;
2821 end if;
2822 LOAD_QUARTER(l_from_date, l_to_date);
2823 if g_debug_flag = 'Y' then
2824 fii_util.stop_timer;
2825 fii_util.print_timer('Process Time');
2826 fii_util.put_line(' ');
2827 end if;
2828
2829 g_phase := 'Load Year Level';
2830 if g_debug_flag = 'Y' then
2831 fii_util.start_timer;
2832 end if;
2833 LOAD_YEAR(l_from_date, l_to_date);
2834 if g_debug_flag = 'Y' then
2835 fii_util.stop_timer;
2836 fii_util.print_timer('Process Time');
2837 fii_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 fii_util.start_timer;
2845 end if;
2846 LOAD_WEEK(l_from_date, l_to_date);
2847 if g_debug_flag = 'Y' then
2848 fii_util.stop_timer;
2849 fii_util.print_timer('Process Time');
2850 fii_util.put_line(' ');
2851 end if;
2852
2853 g_phase := 'Load Period 445 Level';
2854 if g_debug_flag = 'Y' then
2855 fii_util.start_timer;
2856 end if;
2857 LOAD_PERIOD_445(l_from_date, l_to_date);
2858 if g_debug_flag = 'Y' then
2859 fii_util.stop_timer;
2860 fii_util.print_timer('Process Time');
2861 fii_util.put_line(' ');
2862 end if;
2863
2864 g_phase := 'Load Year 445 Level';
2865 if g_debug_flag = 'Y' then
2866 fii_util.start_timer;
2867 end if;
2868 LOAD_YEAR_445(l_from_date, l_to_date);
2869 if g_debug_flag = 'Y' then
2870 fii_util.stop_timer;
2871 fii_util.print_timer('Process Time');
2872 fii_util.put_line(' ');
2873 end if;
2874
2875 g_phase := 'Load Enterprise Period Level';
2876 if g_debug_flag = 'Y' then
2877 fii_util.start_timer;
2878 end if;
2879 if l_period_updated = 'Y' then
2880 -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2881 -- to the latest date in FII_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 fii_util.stop_timer;
2889 fii_util.print_timer('Process Time');
2890 fii_util.put_line(' ');
2891 end if;
2892
2893 g_phase := 'Load Enterprise Quarter Level';
2894 if g_debug_flag = 'Y' then
2895 fii_util.start_timer;
2896 end if;
2897 -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2898 -- to the latest date in FII_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 fii_util.stop_timer;
2903 fii_util.print_timer('Process Time');
2904 fii_util.put_line(' ');
2905 end if;
2906
2907 g_phase := 'Load Enterprise Year Level';
2908 if g_debug_flag = 'Y' then
2909 fii_util.start_timer;
2910 end if;
2911 -- we populate data from the earliest date in FII_TIME_DAY or the from date parameter, see which one is the earliest
2912 -- to the latest date in FII_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 fii_util.stop_timer;
2917 fii_util.print_timer('Process Time');
2918 fii_util.put_line(' ');
2919 end if;
2920
2921 g_phase := 'Load Financial Calendar Level';
2922 if g_debug_flag = 'Y' then
2923 fii_util.start_timer;
2924 end if;
2925 LOAD_CAL_NAME;
2926 if g_debug_flag = 'Y' then
2927 fii_util.stop_timer;
2928 fii_util.print_timer('Process Time');
2929 fii_util.put_line(' ');
2930 end if;
2931
2932 g_phase := 'Load Financial Period Level';
2933 if g_debug_flag = 'Y' then
2934 fii_util.start_timer;
2935 end if;
2936 LOAD_CAL_PERIOD;
2937 if g_debug_flag = 'Y' then
2938 fii_util.stop_timer;
2939 fii_util.print_timer('Process Time');
2940 fii_util.put_line(' ');
2941 end if;
2942
2943 g_phase := 'Load Financial Quarter Level';
2944 if g_debug_flag = 'Y' then
2945 fii_util.start_timer;
2946 end if;
2947 LOAD_CAL_QUARTER;
2948 if g_debug_flag = 'Y' then
2949 fii_util.stop_timer;
2950 fii_util.print_timer('Process Time');
2951 fii_util.put_line(' ');
2952 end if;
2953
2954 g_phase := 'Load Financial Year Level';
2955 if g_debug_flag = 'Y' then
2956 fii_util.start_timer;
2957 end if;
2958 LOAD_CAL_YEAR;
2959 if g_debug_flag = 'Y' then
2960 fii_util.stop_timer;
2961 fii_util.print_timer('Process Time');
2962 fii_util.put_line(' ');
2963 end if;
2964
2965 g_phase := 'Refresh Materialized View';
2966 if g_debug_flag = 'Y' then
2967 fii_util.put_line('LOAD : '||'Refresh Materialized View');
2968 fii_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('FII_TIME_CAL_DAY_MV','C');
2976
2977 BIS_MV_REFRESH.refresh_wrapper ('FII_TIME_CAL_DAY_MV', 'C');
2978 ------------------------------------------------------------------------------
2979
2980 if g_debug_flag = 'Y' then
2981 fii_util.stop_timer;
2982 fii_util.print_timer('Process Time');
2983 fii_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 fii_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 fii_util.stop_timer;
2993 fii_util.print_timer('Process Time');
2994 fii_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 fii_util.start_timer;
3003 end if;
3004 LOAD_TIME_RPT_STRUCT;
3005 if g_debug_flag = 'Y' then
3006 fii_util.stop_timer;
3007 fii_util.print_timer('Process Time');
3008 fii_util.put_line(' ');
3009 end if;
3010
3011 g_phase := 'Load Time Structures Table';
3012 IF g_debug_flag = 'Y' THEN
3013 fii_util.start_timer;
3014 END IF;
3015 FII_TIME_STRUCTURE_C.LOAD_TIME_STRUCTURES;
3016 IF g_debug_flag = 'Y' THEN
3017 fii_util.stop_timer;
3018 fii_util.print_timer('Process Time');
3019 fii_util.put_line(' ');
3020 END IF;
3021
3022 else
3023
3024 begin
3025 select 1 into l_count
3026 from FII_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 fii_util.start_timer;
3040 END IF;
3041 FII_TIME_STRUCTURE_C.LOAD_TIME_STRUCTURES;
3042 IF g_debug_flag = 'Y' THEN
3043 fii_util.stop_timer;
3044 fii_util.print_timer('Process Time');
3045 fii_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 fii_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 fii_util.start_timer;
3067 end if;
3068 FII_TIME_ROLLING_PKG.Load_Rolling_Offsets(l_error_msg,l_error_code);
3069 if g_debug_flag = 'Y' then
3070 fii_util.stop_timer;
3071 fii_util.print_timer('Process Time');
3072 fii_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 fii_util.start_timer;
3082 end if;
3083
3084 -- note: we don't gather stats on FII_TIME_DAY
3085 -- as this should be done by RSG
3086 gather_table_stats('FII_TIME_MONTH');
3087
3088 if g_debug_flag = 'Y' then
3089 fii_util.put_line('Gathered statistics for Month Level');
3090 end if;
3091
3092 gather_table_stats('FII_TIME_QTR');
3093
3094 if g_debug_flag = 'Y' then
3095 fii_util.put_line('Gathered statistics for Quarter Level');
3096 end if;
3097
3098 gather_table_stats('FII_TIME_YEAR445');
3099
3100 if g_debug_flag = 'Y' then
3101 fii_util.put_line('Gathered statistics for Period 445 Level');
3102 end if;
3103
3104 IF g_debug_flag = 'Y' THEN
3105 fii_util.stop_timer;
3106 fii_util.print_timer('Process Time');
3107 fii_util.put_line(' ');
3108 END IF;
3109
3110 end if;
3111
3112 else
3113
3114 truncate_table('FII_TIME_WEEK');
3115 truncate_table('FII_TIME_P445');
3116 truncate_table('FII_TIME_YEAR445');
3117 truncate_table('FII_TIME_ENT_PERIOD');
3118 truncate_table('FII_TIME_ENT_QTR');
3119 truncate_table('FII_TIME_ENT_YEAR');
3120 truncate_table('FII_TIME_CAL_PERIOD');
3121 truncate_table('FII_TIME_CAL_QTR');
3122 truncate_table('FII_TIME_CAL_YEAR');
3123 truncate_table('FII_TIME_RPT_STRUCT');
3124 truncate_table('FII_TIME_CAL_RPT_STRUCT');
3125
3126 if g_debug_flag = 'Y' then
3127 fii_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 ('FII_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;
3158
3159 exception
3160 WHEN G_BIS_PARAMETER_NOT_SETUP THEN
3161 fii_util.put_line(fnd_message.get_string('FII', 'FII_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('FII', 'FII_BIS_PARAMETER_NOT_SETUP')
3168 );
3169 end if;
3170 WHEN G_LOGIN_INFO_NOT_FOUND THEN
3171 fii_util.put_line('Can not get User ID and Login ID, program exit');
3172 retcode := -1;
3173 rollback;
3174 if l_bis_setup = 'Y' then
3175 BIS_COLLECTION_UTILITIES.wrapup
3176 ( p_status => FALSE
3177 , p_message => 'Can not get User ID and Login ID, program exit'
3178 );
3179 end if;
3180 WHEN G_ENT_CALENDAR_NOT_FOUND THEN
3181 rollback;
3182 --Bug 3640141. Setting token as the message text has changed.
3183 fnd_message.set_name('FII','FII_ENT_CALENDAR_NOT_FOUND');
3184 fnd_message.set_token('DATE_NOT_DEFINED', fnd_date.date_to_displaydate(g_date_not_defined));
3185 l_mesg := fnd_message.get;
3186 fii_util.put_line(l_mesg);
3187 retcode := -1;
3188 if l_bis_setup = 'Y' then
3189 BIS_COLLECTION_UTILITIES.wrapup
3190 ( p_status => FALSE , p_message => l_mesg);
3191 end if;
3192 WHEN G_YEAR_NOT_DEFINED THEN
3193 --Added for bug 4454026
3194 rollback;
3195 fnd_message.set_name('FII','FII_FISCAL_YEAR_NOT_DEFINED');
3196 fnd_message.set_token('YEAR', l_year);
3197 fnd_message.set_token('START_DATE', fnd_date.date_to_displaydate(l_min_start_date));
3198 fnd_message.set_token('END_DATE', fnd_date.date_to_displaydate(l_max_end_date));
3199 l_mesg := fnd_message.get;
3200 fii_util.put_line(l_mesg);
3201 retcode := -1;
3202 if l_bis_setup = 'Y' then
3203 BIS_COLLECTION_UTILITIES.wrapup
3204 ( p_status => FALSE , p_message => l_mesg);
3205 end if;
3206 WHEN OTHERS THEN
3207 rollback;
3208 retcode := sqlcode;
3209 errbuf := sqlerrm;
3210 fii_util.put_line(retcode||' : '||errbuf);
3211 fii_util.put_line('
3212 -------------------------------------------
3213 Error occured in Procedure: LOAD
3214 Phase: ' || g_phase);
3215 if l_bis_setup = 'Y' then
3216 BIS_COLLECTION_UTILITIES.wrapup
3217 ( p_status => FALSE
3218 , p_message => substr(sqlerrm,1,4000)
3219 );
3220 end if;
3221
3222 end LOAD;
3223
3224 ---------------------------------------------------
3225 -- PUBLIC PROCEDURE LOAD_CAL_NAME
3226 -- The procedure was changed to public per PJI's request
3227 ---------------------------------------------------
3228 PROCEDURE LOAD_CAL_NAME IS
3229
3230 -- ---------------------------------------------------------
3231 -- Define local variables
3232 -- ---------------------------------------------------------
3233 l_status VARCHAR2(30);
3234 l_industry VARCHAR2(30);
3235 l_name_row number;
3236 l_max_cal_name number;
3237
3238 cursor new_cal is
3239 SELECT inline_view.period_set_name, inline_view.period_type, MAX(gp.creation_date)
3240 FROM ( SELECT DISTINCT glp.period_set_name , glp.period_type
3241 FROM gl_periods glp
3242 MINUS
3243 SELECT DISTINCT cal.period_set_name, cal.period_type
3244 FROM fii_time_cal_name cal
3245 ) inline_view,
3246 gl_periods gp
3247 WHERE inline_view.period_set_name = gp.period_set_name
3248 and inline_view.period_type = gp.period_type
3249
3250 GROUP BY inline_view.period_set_name, inline_view.period_type
3251 ORDER BY MAX(gp.creation_date);
3252
3253 begin
3254
3255 -- ---------------------------------------------------------
3256 -- Variable initialization
3257 -- ---------------------------------------------------------
3258 l_name_row := 0;
3259
3260 select nvl(max(calendar_id),0)
3261 into l_max_cal_name
3262 from fii_time_cal_name;
3263
3264 if g_schema is null then
3265 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_schema)) THEN
3266 NULL;
3267 END IF;
3268 end if;
3269
3270 -- ----------------------
3271 -- Populate Calendar Name Level
3272 -- ----------------------
3273 FOR new_cal_rec IN new_cal LOOP
3274
3275 insert into fii_time_cal_name
3276 (calendar_id,
3277 period_set_name,
3278 period_type,
3279 name,
3280 creation_date,
3281 last_update_date,
3282 last_updated_by,
3283 created_by,
3284 last_update_login)
3285 values(
3286 l_max_cal_name+1,
3287 new_cal_rec.period_set_name,
3288 new_cal_rec.period_type,
3289 new_cal_rec.period_set_name||' ('||new_cal_rec.period_type||')',
3290 sysdate,
3291 sysdate,
3292 g_user_id,
3293 g_user_id,
3294 g_login_id);
3295
3296 l_max_cal_name := l_max_cal_name+1;
3297 l_name_row := l_name_row+1;
3298
3299 end loop;
3300
3301 commit;
3302
3303 if g_debug_flag = 'Y' then
3304 fii_util.put_line('LOAD_CAL_NAME : '||to_char(l_name_row)||' records has been populated to Calendar Name Level');
3305 end if;
3306
3307 if l_name_row > 0 then
3308
3309 gather_table_stats('FII_TIME_CAL_NAME');
3310
3311 if g_debug_flag = 'Y' then
3312 fii_util.put_line('LOAD_CAL_NAME : '||'Gathered statistics for Calendar Name Level');
3313 end if;
3314
3315 end if;
3316
3317 end LOAD_CAL_NAME;
3318
3319 ---------------------------------------------------
3320 -- PUBLIC PROCEDURE LOAD_TIME_RPT_STRUCT
3321 -- the from and to dates are actually ignored.
3322 ---------------------------------------------------
3323 PROCEDURE LOAD_TIME_RPT_STRUCT(p_from_date in date, p_to_date in date) IS
3324
3325 begin
3326
3327 INIT;
3328
3329 LOAD_TIME_RPT_STRUCT;
3330
3331 end LOAD_TIME_RPT_STRUCT;
3332
3333 ---------------------------------------------------
3334 -- PRIVATE PROCEDURE LOAD_TIME_RPT_STRUCT
3335 ---------------------------------------------------
3336 PROCEDURE LOAD_TIME_RPT_STRUCT IS
3337
3338 -- ---------------------------------------------------------
3339 -- Define local variables
3340 -- ---------------------------------------------------------
3341 --l_from_date date;
3342 --l_to_date date;
3343 l_day date;
3344 l_week_start_date date;
3345 l_ptd_to_date date;
3346 l_period_start_date date;
3347 l_row number;
3348
3349 --* We should do a full refresh for FII_TIME_RPT_STRUCT
3350 -- Bug 5624487
3351 cursor c1 is
3352 select report_date, ent_period_start_date, ent_qtr_start_date,
3353 ent_year_start_date, week_start_date
3354 from FII_TIME_DAY
3355 where report_date <> g_unassigned_day;
3356 --*this would be incorrect: where report_date between l_from_date and l_to_date
3357
3358 begin
3359
3360 truncate_table('FII_TIME_RPT_STRUCT');
3361
3362 l_row := 0;
3363 --l_from_date := trunc(nvl(p_from_date,trunc(add_months(sysdate,-24),'YYYY')));
3364 --l_to_date := trunc(nvl(p_to_date,trunc(sysdate,'YYYY')));
3365
3366 FOR c1_rec IN c1 LOOP
3367 insert into FII_TIME_RPT_STRUCT
3368 (calendar_id,
3369 calendar_type,
3370 report_date,
3371 time_id,
3372 period_type_id,
3373 record_type_id,
3374 creation_date,
3375 last_update_date,
3376 last_updated_by,
3377 created_by,
3378 last_update_login)
3379 values
3380 (-1,
3381 'C',
3382 c1_rec.report_date,
3383 to_char(c1_rec.report_date,'j'),
3384 1,
3385 1,
3386 sysdate,
3387 sysdate,
3388 g_user_id,
3389 g_user_id,
3390 g_login_id);
3391
3392 l_row := l_row+1;
3393 l_day:=c1_rec.report_date-1;
3394 l_week_start_date:=c1_rec.week_start_date;
3395 l_period_start_date:=c1_rec.ent_period_start_date;
3396
3397 While l_day >= least(l_week_start_date,l_period_start_date) LOOP
3398 if l_day >= l_period_start_date then
3399 if l_day >= l_week_start_date then
3400 insert into FII_TIME_RPT_STRUCT
3401 (calendar_id,
3402 calendar_type,
3403 report_date,
3404 time_id,
3405 period_type_id,
3406 record_type_id,
3407 creation_date,
3408 last_update_date,
3409 last_updated_by,
3410 created_by,
3411 last_update_login)
3412 values
3413 (-1,
3414 'C',
3415 c1_rec.report_date,
3416 to_char(l_day,'j'),
3417 1,
3418 2,
3419 sysdate,
3420 sysdate,
3421 g_user_id,
3422 g_user_id,
3423 g_login_id);
3424
3425 l_row := l_row + sql%rowcount;
3426
3427 else
3428 select nvl(min(start_date),l_week_start_date) into l_ptd_to_date from FII_TIME_WEEK
3429 where start_date >= l_period_start_date
3430 and start_date < l_week_start_date;
3431
3432 if l_day < l_ptd_to_date then
3433 insert into FII_TIME_RPT_STRUCT
3434 (calendar_id,
3435 calendar_type,
3436 report_date,
3437 time_id,
3438 period_type_id,
3439 record_type_id,
3440 creation_date,
3441 last_update_date,
3442 last_updated_by,
3443 created_by,
3444 last_update_login)
3445 values
3446 (-1,
3447 'C',
3448 c1_rec.report_date,
3449 to_char(l_day,'j'),
3450 1,
3451 4,
3452 sysdate,
3453 sysdate,
3454 g_user_id,
3455 g_user_id,
3456 g_login_id);
3457
3458 l_row := l_row + sql%rowcount;
3459
3460 end if;
3461 end if;
3462 else
3463 if l_day >= l_week_start_date then
3464 insert into FII_TIME_RPT_STRUCT
3465 (calendar_id,
3466 calendar_type,
3467 report_date,
3468 time_id,
3469 period_type_id,
3470 record_type_id,
3471 creation_date,
3472 last_update_date,
3473 last_updated_by,
3474 created_by,
3475 last_update_login)
3476 values
3477 (-1,
3478 'C',
3479 c1_rec.report_date,
3480 to_char(l_day,'j'),
3481 1,
3482 8,
3483 sysdate,
3484 sysdate,
3485 g_user_id,
3486 g_user_id,
3487 g_login_id);
3488
3489 l_row := l_row + sql%rowcount;
3490
3491 end if;
3492 end if;
3493
3494 l_day:=l_day-1;
3495
3496 END LOOP;
3497
3498 commit;
3499
3500 insert into FII_TIME_RPT_STRUCT
3501 (calendar_id,
3502 calendar_type,
3503 report_date,
3504 time_id,
3505 period_type_id,
3506 record_type_id,
3507 creation_date,
3508 last_update_date,
3509 last_updated_by,
3510 created_by,
3511 last_update_login)
3512 select
3513 -1,
3514 'E',
3515 c1_rec.report_date,
3516 week_id,
3517 16,
3518 16,
3519 sysdate,
3520 sysdate,
3521 g_user_id,
3522 g_user_id,
3523 g_login_id
3524 from FII_TIME_WEEK
3525 where start_date >= c1_rec.ent_period_start_date
3526 and end_date < c1_rec.week_start_date
3527 union all
3528 select
3529 -1,
3530 'E',
3531 c1_rec.report_date,
3532 week_id,
3533 16,
3534 2048,
3535 sysdate,
3536 sysdate,
3537 g_user_id,
3538 g_user_id,
3539 g_login_id
3540 from FII_TIME_WEEK
3541 where c1_rec.report_date between start_date and end_date;
3542
3543 l_row := l_row + sql%rowcount;
3544
3545 commit;
3546
3547 insert into FII_TIME_RPT_STRUCT
3548 (calendar_id,
3549 calendar_type,
3550 report_date,
3551 time_id,
3552 period_type_id,
3553 record_type_id,
3554 creation_date,
3555 last_update_date,
3556 last_updated_by,
3557 created_by,
3558 last_update_login)
3559 select
3560 -1,
3561 'E',
3562 c1_rec.report_date,
3563 ent_period_id,
3564 32,
3565 32,
3566 sysdate,
3567 sysdate,
3568 g_user_id,
3569 g_user_id,
3570 g_login_id
3571 from FII_TIME_ENT_PERIOD
3572 where start_date >= c1_rec.ent_qtr_start_date
3573 and start_date <= c1_rec.ent_period_start_date
3574 and end_date < c1_rec.report_date
3575 union all
3576 select
3577 -1,
3578 'E',
3579 c1_rec.report_date,
3580 ent_period_id,
3581 32,
3582 256,
3583 sysdate,
3584 sysdate,
3585 g_user_id,
3586 g_user_id,
3587 g_login_id
3588 from FII_TIME_ENT_PERIOD
3589 where start_date >= c1_rec.ent_qtr_start_date
3590 and start_date <= c1_rec.ent_period_start_date
3591 and end_date >= c1_rec.report_date;
3592
3593 l_row := l_row + sql%rowcount;
3594
3595 commit;
3596
3597 insert into FII_TIME_RPT_STRUCT
3598 (calendar_id,
3599 calendar_type,
3600 report_date,
3601 time_id,
3602 period_type_id,
3603 record_type_id,
3604 creation_date,
3605 last_update_date,
3606 last_updated_by,
3607 created_by,
3608 last_update_login)
3609 select
3610 -1,
3611 'E',
3612 c1_rec.report_date,
3613 ent_qtr_id,
3614 64,
3615 64,
3616 sysdate,
3617 sysdate,
3618 g_user_id,
3619 g_user_id,
3620 g_login_id
3621 from FII_TIME_ENT_QTR
3622 where start_date >= c1_rec.ent_year_start_date
3623 and start_date <= c1_rec.ent_qtr_start_date
3624 and end_date < c1_rec.report_date
3625 union all
3626 select
3627 -1,
3628 'E',
3629 c1_rec.report_date,
3630 ent_qtr_id,
3631 64,
3632 512,
3633 --case when end_date >= c1_rec.report_date then 512 else 64 end,
3634 sysdate,
3635 sysdate,
3636 g_user_id,
3637 g_user_id,
3638 g_login_id
3639 from FII_TIME_ENT_QTR
3640 where start_date >= c1_rec.ent_year_start_date
3641 and start_date <= c1_rec.ent_qtr_start_date
3642 and end_date >= c1_rec.report_date;
3643
3644 l_row := l_row + sql%rowcount;
3645
3646 commit;
3647
3648 insert into FII_TIME_RPT_STRUCT
3649 (calendar_id,
3650 calendar_type,
3651 report_date,
3652 time_id,
3653 period_type_id,
3654 record_type_id,
3655 creation_date,
3656 last_update_date,
3657 last_updated_by,
3658 created_by,
3659 last_update_login)
3660 select
3661 -1,
3662 'E',
3663 c1_rec.report_date,
3664 ent_year_id,
3665 128,
3666 128,
3667 sysdate,
3668 sysdate,
3669 g_user_id,
3670 g_user_id,
3671 g_login_id
3672 from FII_TIME_ENT_YEAR
3673 where c1_rec.report_date between start_date and end_date;
3674
3675 l_row := l_row + sql%rowcount;
3676
3677 commit;
3678
3679 -- All prior years (report_type: 1024), for ITD
3680 insert into FII_TIME_RPT_STRUCT
3681 (calendar_id,
3682 calendar_type,
3683 report_date,
3684 time_id,
3685 period_type_id,
3686 record_type_id,
3687 creation_date,
3688 last_update_date,
3689 last_updated_by,
3690 created_by,
3691 last_update_login)
3692 select
3693 -1,
3694 'E',
3695 c1_rec.report_date,
3696 ent_year_id,
3697 128,
3698 1024,
3699 sysdate,
3700 sysdate,
3701 g_user_id,
3702 g_user_id,
3703 g_login_id
3704 from FII_TIME_ENT_YEAR
3705 where end_date >= g_global_start_date -- should we use start_date?
3706 and end_date < c1_rec.report_date;
3707
3708 l_row := l_row + sql%rowcount;
3709
3710 commit;
3711
3712 END LOOP; -- c1_rec
3713
3714 if g_debug_flag = 'Y' then
3715 fii_util.put_line('LOAD_TIME_RPT_STRUCT :'||to_char(l_row)||' records has been populated to the Reporting Structure table');
3716 end if;
3717
3718 gather_table_stats('FII_TIME_RPT_STRUCT');
3719
3720 if g_debug_flag = 'Y' then
3721 fii_util.put_line('LOAD_TIME_RPT_STRUCT :'||'Gathered statistics for the Reporting Structure table');
3722 end if;
3723
3724 end LOAD_TIME_RPT_STRUCT;
3725
3726
3727 ---------------------------------------------------
3728 -- PUBLIC PROCEDURE LOAD_TIME_CAL_RPT_STRUCT
3729 ---------------------------------------------------
3730 PROCEDURE LOAD_TIME_CAL_RPT_STRUCT(p_from_date in date, p_to_date in date) IS
3731
3732 -- ---------------------------------------------------------
3733 -- Define local variables
3734 -- ---------------------------------------------------------
3735 l_Day DATE;
3736 l_Week_Start_Date DATE;
3737 l_Earliest_Week DATE;
3738 l_Row NUMBER:=0;
3739 l_No_Week_Info VARCHAR2(1):='N';
3740 l_from_date DATE:=p_from_date;
3741 l_to_date DATE:=p_to_date;
3742 l_full_extraction_flag varchar2(1);
3743 l_max_date_v varchar2(100);
3744 l_min_date date;
3745 l_max_date date;
3746
3747 begin
3748
3749 --Bug 3543939. Get the minimum from date and maximum to date
3750 SELECT nvl(least(min(report_date),l_from_date),l_from_date),nvl(greatest(max(report_date),l_to_date),l_to_date)
3751 INTO l_min_date,l_max_date
3752 from FII_TIME_CAL_RPT_STRUCT;
3753
3754 --Bug 3543939. The Calendar id's to be picked from the temporary table populated in Load_Cal_Period
3755
3756 For cur_Fiscal_Calendar IN (
3757 SELECT distinct calendar_id
3758 FROM fii_time_cal_gt) LOOP
3759
3760 --Reset the l_from_date and l_to_date to the parameters passed
3761
3762 l_from_date:=p_from_date;
3763 l_to_date:=p_to_date;
3764
3765 --Check if the from date has been already extracted
3766
3767 BEGIN
3768
3769 SELECT 'T'
3770 INTO l_full_extraction_flag
3771 FROM FII_TIME_CAL_RPT_STRUCT
3772 WHERE REPORT_DATE >= p_from_date
3773 AND CALENDAR_ID = cur_Fiscal_Calendar.CALENDAR_ID
3774 AND ROWNUM <= 1;
3775 EXCEPTION
3776 WHEN NO_DATA_FOUND THEN
3777 l_full_extraction_flag := 'F';
3778 END;
3779
3780
3781 IF l_full_extraction_flag = 'T' THEN
3782
3783 -- Delete all records for the calendar
3784 -- and reset the from and to date for
3785 -- extraction
3786
3787 DELETE FII_TIME_CAL_RPT_STRUCT
3788 WHERE CALENDAR_ID = cur_Fiscal_Calendar.CALENDAR_ID;
3789 l_from_date := l_min_date;
3790 l_to_date := l_max_date;
3791
3792 END IF;
3793
3794 FOR cur_Fiscal_Days IN (
3795 SELECT calendar_id
3796 , report_date
3797 , cal_period_start_date period_start_date
3798 , cal_qtr_start_date qtr_start_date
3799 , cal_year_start_date year_start_date
3800 FROM fii_time_cal_day_mv
3801 WHERE calendar_id = cur_Fiscal_Calendar.calendar_id
3802 AND report_date BETWEEN l_from_date AND l_to_date) LOOP
3803
3804 INSERT INTO FII_TIME_CAL_RPT_STRUCT
3805 ( calendar_id
3806 , calendar_type
3807 , report_date
3808 , time_id
3809 , period_type_id
3810 , record_type_id
3811 , creation_date
3812 , last_update_date
3813 , last_updated_by
3814 , created_by
3815 , last_update_login)
3816 VALUES
3817 (cur_Fiscal_Days.calendar_id
3818 , 'C'
3819 , cur_Fiscal_Days.report_date
3820 , TO_CHAR(cur_Fiscal_Days.report_date,'j')
3821 , 1
3822 , 1
3823 , SYSDATE
3824 , SYSDATE
3825 , g_user_id
3826 , g_user_id
3827 , g_login_id);
3828
3829 l_Row := l_Row + SQL%ROWCOUNT;
3830
3831 COMMIT;
3832
3833 l_Day := cur_Fiscal_Days.report_date-1;
3834
3835 BEGIN
3836 SELECT week_start_date
3837 INTO l_Week_Start_Date
3838 FROM fii_time_day
3839 WHERE report_date = cur_Fiscal_Days.report_date;
3840
3841 l_No_Week_Info := 'N';
3842
3843 EXCEPTION
3844 WHEN NO_DATA_FOUND THEN
3845 l_No_Week_Info := 'Y';
3846 END;
3847
3848 IF l_No_Week_Info ='N' THEN
3849 SELECT NVL(MIN(start_date),l_Week_Start_Date)
3850 INTO l_Earliest_Week
3851 FROM fii_time_week
3852 WHERE start_date >= cur_Fiscal_Days.period_start_date
3853 AND start_date < l_Week_Start_Date;
3854
3855 WHILE l_Day >= cur_Fiscal_Days.period_start_date LOOP
3856 IF l_Day >= l_Week_Start_Date THEN
3857 INSERT INTO FII_TIME_CAL_RPT_STRUCT
3858 ( calendar_id
3859 , calendar_type
3860 , report_date
3861 , time_id
3862 , period_type_id
3863 , record_type_id
3864 , creation_date
3865 , last_update_date
3866 , last_updated_by
3867 , created_by
3868 , last_update_login)
3869 VALUES
3870 (cur_Fiscal_Days.calendar_id
3871 , 'C'
3872 , cur_Fiscal_Days.report_date
3873 , TO_CHAR(l_Day,'j')
3874 , 1
3875 , 2
3876 , SYSDATE
3877 , SYSDATE
3878 , g_user_id
3879 , g_user_id
3880 , g_login_id);
3881
3882 l_Row := l_Row + SQL%ROWCOUNT;
3883
3884 ELSIF l_Day >= cur_Fiscal_Days.period_start_date AND l_Day < l_Earliest_Week THEN
3885 INSERT INTO FII_TIME_CAL_RPT_STRUCT
3886 ( calendar_id
3887 , calendar_type
3888 , report_date
3889 , time_id
3890 , period_type_id
3891 , record_type_id
3892 , creation_date
3893 , last_update_date
3894 , last_updated_by
3895 , created_by
3896 , last_update_login)
3897 VALUES
3898 (cur_Fiscal_Days.calendar_id
3899 , 'C'
3900 , cur_Fiscal_Days.report_date
3901 , TO_CHAR(l_Day,'j')
3902 , 1
3903 , 4
3904 , SYSDATE
3905 , SYSDATE
3906 , g_user_id
3907 , g_user_id
3908 , g_login_id);
3909
3910 l_Row := l_Row + SQL%ROWCOUNT;
3911 END IF;
3912
3913 l_Day := l_Day-1;
3914
3915 END LOOP;
3916
3917 COMMIT;
3918
3919 INSERT INTO FII_TIME_CAL_RPT_STRUCT
3920 ( calendar_id
3921 , calendar_type
3922 , report_date
3923 , time_id
3924 , period_type_id
3925 , record_type_id
3926 , creation_date
3927 , last_update_date
3928 , last_updated_by
3929 , created_by
3930 , last_update_login)
3931 SELECT
3932 cur_Fiscal_Days.calendar_id
3933 , 'E'
3934 , cur_Fiscal_Days.report_date
3935 , week_id
3936 , 16
3937 , 16
3938 , SYSDATE
3939 , SYSDATE
3940 , g_user_id
3941 , g_user_id
3942 , g_login_id
3943 FROM FII_TIME_WEEK
3944 WHERE start_date >= cur_Fiscal_Days.period_start_date
3945 AND end_date < l_Week_Start_Date;
3946
3947 l_Row := l_Row + SQL%ROWCOUNT;
3948
3949 COMMIT;
3950
3951 ELSE
3952 INSERT INTO FII_TIME_CAL_RPT_STRUCT
3953 ( calendar_id
3954 , calendar_type
3955 , report_date
3956 , time_id
3957 , period_type_id
3958 , record_type_id
3959 , creation_date
3960 , last_update_date
3961 , last_updated_by
3962 , created_by
3963 , last_update_login)
3964 SELECT
3965 cur_Fiscal_Days.calendar_id
3966 , 'C'
3967 , cur_Fiscal_Days.report_date
3968 , report_date_julian
3969 , 1
3970 , 4
3971 , SYSDATE
3972 , SYSDATE
3973 , g_user_id
3974 , g_user_id
3975 , g_login_id
3976 FROM FII_TIME_CAL_DAY_MV
3977 WHERE calendar_id = cur_Fiscal_Days.calendar_id
3978 AND report_date BETWEEN cur_Fiscal_Days.period_start_date AND l_Day;
3979
3980 l_Row := l_Row + SQL%ROWCOUNT;
3981
3982 COMMIT;
3983 END IF;
3984
3985 INSERT INTO FII_TIME_CAL_RPT_STRUCT
3986 ( calendar_id
3987 , calendar_type
3988 , report_date
3989 , time_id
3990 , period_type_id
3991 , record_type_id
3992 , creation_date
3993 , last_update_date
3994 , last_updated_by
3995 , created_by
3996 , last_update_login)
3997 SELECT
3998 cur_Fiscal_Days.calendar_id
3999 , 'G'
4000 , cur_Fiscal_Days.report_date
4001 , cal_period_id
4002 , 32
4003 , 32
4004 , SYSDATE
4005 , SYSDATE
4006 , g_user_id
4007 , g_user_id
4008 , g_login_id
4009 FROM FII_TIME_CAL_PERIOD
4010 WHERE start_date >= cur_Fiscal_Days.qtr_start_date
4011 AND start_date <= cur_Fiscal_Days.period_start_date
4012 AND end_date < cur_Fiscal_Days.report_date
4013 AND calendar_id = cur_Fiscal_Days.calendar_id
4014 UNION ALL
4015 SELECT
4016 cur_Fiscal_Days.calendar_id
4017 , 'G'
4018 , cur_Fiscal_Days.report_date
4019 , cal_period_id
4020 , 32
4021 , 256
4022 , SYSDATE
4023 , SYSDATE
4024 , g_user_id
4025 , g_user_id
4026 , g_login_id
4027 FROM FII_TIME_CAL_PERIOD
4028 WHERE start_date >= cur_Fiscal_Days.qtr_start_date
4029 AND start_date <= cur_Fiscal_Days.period_start_date
4030 AND end_date >= cur_Fiscal_Days.report_date
4031 AND calendar_id = cur_Fiscal_Days.calendar_id;
4032
4033 l_Row := l_Row + SQL%ROWCOUNT;
4034
4035 COMMIT;
4036
4037 INSERT INTO FII_TIME_CAL_RPT_STRUCT
4038 ( calendar_id
4039 , calendar_type
4040 , report_date
4041 , time_id
4042 , period_type_id
4043 , record_type_id
4044 , creation_date
4045 , last_update_date
4046 , last_updated_by
4047 , created_by
4048 , last_update_login)
4049 SELECT
4050 cur_Fiscal_Days.calendar_id
4051 , 'G'
4052 , cur_Fiscal_Days.report_date
4053 , cal_qtr_id
4054 , 64
4055 , 64
4056 , SYSDATE
4057 , SYSDATE
4058 , g_user_id
4059 , g_user_id
4060 , g_login_id
4061 FROM FII_TIME_CAL_QTR
4062 WHERE start_date >= cur_Fiscal_Days.year_start_date
4063 AND start_date <= cur_Fiscal_Days.qtr_start_date
4064 AND end_date < cur_Fiscal_Days.report_date
4065 AND calendar_id = cur_Fiscal_Days.calendar_id
4066 UNION ALL
4067 SELECT
4068 cur_Fiscal_Days.calendar_id
4069 , 'G'
4070 , cur_Fiscal_Days.report_date
4071 , cal_qtr_id
4072 , 64
4073 , 512
4074 , SYSDATE
4075 , SYSDATE
4076 , g_user_id
4077 , g_user_id
4078 , g_login_id
4079 FROM FII_TIME_CAL_QTR
4080 WHERE start_date >= cur_Fiscal_Days.year_start_date
4081 AND start_date <= cur_Fiscal_Days.qtr_start_date
4082 AND end_date >= cur_Fiscal_Days.report_date
4083 AND calendar_id = cur_Fiscal_Days.calendar_id;
4084
4085 l_Row := l_Row + SQL%ROWCOUNT;
4086
4087 COMMIT;
4088
4089 INSERT INTO FII_TIME_CAL_RPT_STRUCT
4090 ( calendar_id
4091 , calendar_type
4092 , report_date
4093 , time_id
4094 , period_type_id
4095 , record_type_id
4096 , creation_date
4097 , last_update_date
4098 , last_updated_by
4099 , created_by
4100 , last_update_login)
4101 SELECT
4102 cur_Fiscal_Days.calendar_id
4103 , 'G'
4104 , cur_Fiscal_Days.report_date
4105 , cal_year_id
4106 , 128
4107 , 128
4108 , SYSDATE
4109 , SYSDATE
4110 , g_user_id
4111 , g_user_id
4112 , g_login_id
4113 FROM FII_TIME_CAL_YEAR
4114 WHERE calendar_id = cur_Fiscal_Days.calendar_id
4115 AND cur_Fiscal_Days.report_date BETWEEN start_date AND end_date
4116 UNION ALL
4117 SELECT
4118 cur_Fiscal_Days.calendar_id
4119 , 'G'
4120 , cur_Fiscal_Days.report_date
4121 , cal_year_id
4122 , 128
4123 , 1024
4124 , SYSDATE
4125 , SYSDATE
4126 , g_user_id
4127 , g_user_id
4128 , g_login_id
4129 FROM FII_TIME_CAL_YEAR
4130 WHERE calendar_id = cur_Fiscal_Days.calendar_id
4131 AND end_date < cur_Fiscal_Days.report_date
4132 AND end_date >= g_global_start_date;
4133
4134 l_Row := l_Row + SQL%ROWCOUNT;
4135
4136 COMMIT;
4137
4138 END LOOP;
4139
4140 END LOOP;
4141
4142 IF g_debug_flag = 'Y' THEN
4143 fii_util.put_line('LOAD_TIME_CAL_RPT_STRUCT : '||TO_CHAR(l_row)||' records has been populated to the Reporting Structure table for Financial Calendars');
4144 END IF;
4145
4146 if l_row > 0 then
4147
4148 gather_table_stats('FII_TIME_CAL_RPT_STRUCT');
4149
4150 if g_debug_flag = 'Y' then
4151 fii_util.put_line('LOAD_TIME_CAL_RPT_STRUCT :'||'Gathered statistics for Financial Calendars');
4152 end if;
4153
4154 end if;
4155
4156 end LOAD_TIME_CAL_RPT_STRUCT;
4157
4158 ---------------------------------------------------
4159 -- PUBLIC FUNCTION DEFAULT_LOAD_FROM_DATE
4160 -- this function is used to return the default load
4161 -- from date to the concurrent program parameter
4162 ---------------------------------------------------
4163 FUNCTION DEFAULT_LOAD_FROM_DATE(p_load_mode in varchar2)
4164 return varchar2
4165 is
4166
4167 l_return_date date;
4168
4169 begin
4170
4171 if p_load_mode = 'INCRE' then
4172 if check_validated = 'Y' then
4173 l_return_date := least(fnd_date.displaydt_to_date
4174 ( bis_collection_utilities.get_last_refresh_period
4175 ('FII_DBI_TIME_M')
4176 ) +1, fnd_date.displaydt_to_date(DEFAULT_LOAD_TO_DATE));
4177 else
4178 l_return_date := bis_common_parameters.get_global_start_date;
4179 end if;
4180 else
4181 select least(nvl(min(start_date),bis_common_parameters.get_global_start_date) , bis_common_parameters.get_global_start_date) into l_return_date
4182 from fii_time_day;
4183 end if;
4184
4185 return fnd_date.date_to_displaydt(l_return_date);
4186
4187 end DEFAULT_LOAD_FROM_DATE;
4188
4189 ---------------------------------------------------
4190 -- PUBLIC FUNCTION DEFAULT_LOAD_TO_DATE
4191 -- this function is used to return the default load
4192 -- to date to the concurrent program parameter
4193 ---------------------------------------------------
4194 FUNCTION DEFAULT_LOAD_TO_DATE
4195 return varchar2
4196 is
4197
4198 l_return_date date;
4199 l_period_set_name varchar2(15) := bis_common_parameters.get_period_set_name;
4200 l_period_type varchar2(15) := bis_common_parameters.get_period_type;
4201
4202 begin
4203
4204 select max(end_date)
4205 into l_return_date
4206 from gl_periods
4207 where adjustment_period_flag = 'N'
4208 and period_set_name = l_period_set_name
4209 and period_type = l_period_type;
4210
4211 return fnd_date.date_to_displaydt(l_return_date);
4212
4213 end DEFAULT_LOAD_TO_DATE;
4214
4215 END FII_TIME_C;