[Home] [Help]
PACKAGE BODY: APPS.OZF_TIME_PVT
Source
1 PACKAGE BODY OZF_TIME_PVT AS
2 /*$Header: ozfvtimb.pls 120.2 2005/09/26 16:41:19 mkothari noship $*/
3
4 g_schema varchar2(30);
5 g_phase varchar2(500);
6 g_week_offset number;
7 g_user_id number;
8 g_login_id number;
9 g_all_level varchar2(1);
10 g_debug_flag VARCHAR2(1) := 'Y'; -- always show this LOG
11
12 g_global_start_date date;
13 g_period_set_name varchar2(15) := null;
14 g_period_type varchar2(15) := null;
15 g_week_start_day varchar2(30) := null;
16
17
18 G_TABLE_NOT_EXIST EXCEPTION;
19 G_LOGIN_INFO_NOT_FOUND EXCEPTION;
20 G_OZF_PARAMETER_NOT_SETUP EXCEPTION;
21 G_ENT_CALENDAR_NOT_FOUND EXCEPTION;
22
23 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
24
25 ---------------------------------------------------
26 -- PRIVATE FUNCTION get_week_offset
27 ---------------------------------------------------
28 function get_week_offset(p_week_start_day varchar2) return number is
29
30 l_week_offset number;
31
32 begin
33
34 /*case p_week_start_day
35 when '2' then l_week_offset := 0;
36 when '3' then l_week_offset := 1;
37 when '4' then l_week_offset := 2;
38 when '5' then l_week_offset := 3;
39 when '6' then l_week_offset := -3;
40 when '7' then l_week_offset := -2;
41 when '1' then l_week_offset := -1;
42 end case;*/
43
44 if p_week_start_day = '2' then
45 l_week_offset := 0;
46 elsif p_week_start_day = '3' then
47 l_week_offset := 1;
48 elsif p_week_start_day = '4' then
49 l_week_offset := 2;
50 elsif p_week_start_day = '5' then
51 l_week_offset := 3;
52 elsif p_week_start_day = '6' then
53 l_week_offset := -3;
54 elsif p_week_start_day = '7' then
55 l_week_offset := -2;
56 elsif p_week_start_day = '1' then
57 l_week_offset := -1;
58 end if;
59
60 return l_week_offset;
61
62 end get_week_offset;
63
64 ---------------------------------------------------
65 -- PRIVATE FUNCTION get_week_num
66 ---------------------------------------------------
67 function get_week_num(p_date date, p_week_offset number) return number is
68
69 l_week_num number;
70
71 begin
72
73 l_week_num := to_char(p_date-p_week_offset,'iw');
74 return l_week_num;
75
76 end get_week_num;
77
78 ---------------------------------------------------
79 -- PRIVATE FUNCTION get_period_num
80 ---------------------------------------------------
81 function get_period_num(week_num number) return number is
82
83 l_period_num number;
84
85 begin
86
87 if week_num in (1,2,3,4) then
88 l_period_num := 1;
89 elsif week_num in (5,6,7,8) then
90 l_period_num := 2;
91 elsif week_num in (9,10,11,12,13) then
92 l_period_num := 3;
93 elsif week_num in (14,15,16,17) then
94 l_period_num := 4;
95 elsif week_num in (18,19,20,21) then
96 l_period_num := 5;
97 elsif week_num in (22,23,24,25,26) then
98 l_period_num := 6;
99 elsif week_num in (27,28,29,30) then
100 l_period_num := 7;
101 elsif week_num in (31,32,33,34) then
102 l_period_num := 8;
103 elsif week_num in (35,36,37,38,39) then
104 l_period_num := 9;
105 elsif week_num in (40,41,42,43) then
106 l_period_num := 10;
107 elsif week_num in (44,45,46,47) then
108 l_period_num := 11;
109 else
110 l_period_num := 12;
111 end if;
112 return l_period_num;
113 end get_period_num;
114
115 ---------------------------------------------------
116 -- PRIVATE FUNCTION get_period_start
117 ---------------------------------------------------
118 function get_period_start(p_date date) return date is
119
120 l_week_start date;
121 l_period_start date;
122 l_week_num number;
123 l_week_sequence number;
124 l_period_num number;
125
126 begin
127
128 l_week_start := trunc(p_date-g_week_offset,'iw')+g_week_offset;
129 l_week_num := get_week_num(l_week_start,g_week_offset);
130 l_period_num := get_period_num(l_week_num);
131
132 if l_week_num in (1,5,9,14,18,22,27,31,35,40,44,48) then
133 l_week_sequence := 0;
134 elsif l_week_num in (2,6,10,15,19,23,28,32,36,41,45,49) then
135 l_week_sequence := 1;
136 elsif l_week_num in (3,7,11,16,20,24,29,33,37,42,46,50) then
137 l_week_sequence := 2;
138 elsif l_week_num in (4,8,12,17,21,25,30,34,38,43,47,51) then
139 l_week_sequence := 3;
140 else
141 l_week_sequence := 4;
142 end if;
143
144 l_period_start := l_week_start-l_week_sequence*7;
145 return l_period_start;
146
147 end get_period_start;
148
149 ---------------------------------------------------
150 -- PRIVATE PROCEDURE TRUNCATE_TABLE
151 ---------------------------------------------------
152 procedure truncate_table (p_table_name in varchar2) is
153 l_stmt varchar2(400);
154 Begin
155
156 l_stmt := 'truncate table '||g_schema||'.'||p_table_name;
157 if g_debug_flag = 'Y' then
158 OZF_TP_UTIL_PVT.put_line(l_stmt);
159 end if;
160 execute immediate l_stmt;
161
162 Exception
163 WHEN G_TABLE_NOT_EXIST THEN
164 null; -- Oracle 942, table does not exist, no actions
165 WHEN OTHERS THEN
166 raise;
167 End truncate_table;
168
169 ---------------------------------------------------
170 -- PRIVATE PROCEDURE INIT
171 ---------------------------------------------------
172 PROCEDURE INIT IS
173 l_status VARCHAR2(30);
174 l_industry VARCHAR2(30);
175 begin
176
177 -- ----------------------
178 -- Initialize the global variables
179 -- ----------------------
180
181 OZF_TP_UTIL_PVT.initialize;
182
183 IF(FND_INSTALLATION.GET_APP_INFO('OZF', l_status, l_industry, g_schema))
184 THEN NULL;
185 END IF;
186
187 g_user_id := FND_GLOBAL.User_Id;
188 g_login_id := FND_GLOBAL.Login_Id;
189
190 IF (g_user_id IS NULL OR g_login_id IS NULL) THEN
191 RAISE G_LOGIN_INFO_NOT_FOUND;
192 END IF;
193
194 if (g_all_level = 'Y') then
195 g_period_set_name := ozf_common_parameters_pvt.get_period_set_name;
196 g_period_type := ozf_common_parameters_pvt.get_period_type;
197 if g_debug_flag = 'Y' then
198 OZF_TP_UTIL_PVT.put_line('Enterprise Calendar = '||g_period_set_name||' ('||g_period_type||')');
199 end if;
200 g_week_start_day := ozf_common_parameters_pvt.get_start_day_of_week_id;
201 if (g_period_set_name is null or g_period_type is null or g_week_start_day is null) then
202 raise G_OZF_PARAMETER_NOT_SETUP;
203 end if;
204 if g_debug_flag = 'Y' then
205 OZF_TP_UTIL_PVT.put_line('Week Start Day = '||g_week_start_day);
206 end if;
207 g_week_offset := get_week_offset(g_week_start_day);
208 if g_debug_flag = 'Y' then
209 OZF_TP_UTIL_PVT.put_line('Week offset = '||g_week_offset);
210 OZF_TP_UTIL_PVT.put_line(' ');
211 end if;
212
213 -- g_global_start_date := to_date('01/01/1997','MM/DD/YYYY');
214
215 g_global_start_date := ozf_common_parameters_pvt.GET_GLOBAL_START_DATE;
216 if (g_global_start_date is null) then
217 if g_debug_flag = 'Y' then
218 OZF_TP_UTIL_PVT.put_line('Global Start Date is not setup!');
219 end if;
220 raise G_OZF_PARAMETER_NOT_SETUP;
221 end if;
222
223 if g_debug_flag = 'Y' then
224 OZF_TP_UTIL_PVT.put_line('Global Start Date = ' || g_global_start_date);
225 OZF_TP_UTIL_PVT.put_line(' ');
226 end if;
227 end if;
228
229 end INIT;
230
231 ---------------------------------------------------
232 -- PRIVATE PROCEDURE LOAD_DAY_INC
233 -- >> Load Day Level Incrementally
234 ---------------------------------------------------
235 PROCEDURE LOAD_DAY_INC (p_from_date in date, p_to_date in date) IS
236
237 -- ---------------------------------------------------------
238 -- Define local variables
239 -- ---------------------------------------------------------
240 l_from_date date;
241 l_to_date date;
242 l_day date;
243 l_week_num number;
244 l_p445_num number;
245 l_year_num number;
246 l_day_row number;
247 l_period_year gl_periods.period_year%TYPE;
248 l_quarter_num gl_periods.quarter_num%TYPE;
249 l_period_num gl_periods.period_num%TYPE;
250 l_start_date gl_periods.start_date%TYPE;
251 l_quarter_start_date gl_periods.quarter_start_date%TYPE;
252 l_year_start_date gl_periods.year_start_date%TYPE;
253 l_count number;
254
255 cursor ent_period_cur (day date) is
256 select period_year, quarter_num, period_num, start_date, quarter_start_date, year_start_date
257 from gl_periods
258 where adjustment_period_flag='N'
259 and period_set_name=g_period_set_name
260 and period_type=g_period_type
261 and day between start_date and end_date;
262
263 begin
264
265 -- ---------------------------------------------------------
266 -- Variable initialization
267 -- ---------------------------------------------------------
268 l_from_date := p_from_date;
269 l_to_date := p_to_date;
270 l_day := l_from_date;
271 l_day_row := 0;
272
273 -- ----------------------
274 -- Populate Day Level
275 -- ----------------------
276 while l_day <= l_to_date loop
277 if (g_all_level='Y') then
278 open ent_period_cur(l_day);
279 fetch ent_period_cur into l_period_year, l_quarter_num, l_period_num,
280 l_start_date, l_quarter_start_date, l_year_start_date;
281 if (ent_period_cur%notfound) then
282 raise G_ENT_CALENDAR_NOT_FOUND;
283 else
284 l_week_num := get_week_num(l_day,g_week_offset);
285 l_p445_num := get_period_num(l_week_num);
286 l_year_num := to_char(l_day-g_week_offset,'iyyy');
287 end if;
288 else
289 l_period_year := -1;
290 l_quarter_num := null;
291 l_period_num := null;
292 l_start_date := trunc(sysdate);
293 l_quarter_start_date := trunc(sysdate);
294 l_year_start_date := trunc(sysdate);
295 l_week_num := null;
296 l_p445_num := null;
297 l_year_num := -1;
298 end if;
299
300 -- first check if the current day is loaded
301 SELECT count(*) into l_count
302 FROM OZF_TIME_DAY
303 WHERE report_date = trunc(l_day);
304
305 -- do an incremental update/insert
306 if l_count = 0 then -- new record, insert
307
308 insert into OZF_TIME_DAY
309 (report_date,
310 report_date_julian,
311 start_date,
312 end_date,
313 month_id,
314 ent_period_id,
315 ent_period_start_date,
316 ent_qtr_id,
317 ent_qtr_start_date,
318 ent_year_id,
319 ent_year_start_date,
320 week_id,
321 week_start_date,
322 creation_date,
323 last_update_date,
324 last_updated_by,
325 created_by,
326 last_update_login)
327 values(
328 trunc(l_day),
329 to_char(l_day,'j'),
330 l_day,
331 l_day,
332 to_number(to_char(l_day,'yyyyqmm')),
333 --l_period_year||l_quarter_num||lpad(l_period_num,2,'0'),
334 l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
335 l_start_date,
336 l_period_year||l_quarter_num,
337 l_quarter_start_date,
338 l_period_year,
339 l_year_start_date,
340 l_year_num||lpad(l_p445_num,2,'0')||lpad(l_week_num,2,'0'),
341 nvl(trunc(l_day-g_week_offset,'iw')+g_week_offset,trunc(sysdate)),
342 sysdate,
343 sysdate,
344 g_user_id,
345 g_user_id,
346 g_login_id
347 );
348
349 l_day_row := l_day_row+1;
350
351 else -- the day has been loaded, update those changed records only
352
353 update OZF_TIME_DAY
354 set
355 ent_period_id = --l_period_year||l_quarter_num||lpad(l_period_num,2,'0'),
356 l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
357 ent_period_start_date = l_start_date,
358 ent_qtr_id = l_period_year||l_quarter_num,
359 ent_qtr_start_date = l_quarter_start_date,
360 ent_year_id = l_period_year,
361 ent_year_start_date = l_year_start_date,
362 week_id = l_year_num||lpad(l_p445_num,2,'0')||lpad(l_week_num,2,'0'),
363 week_start_date = nvl(trunc(l_day-g_week_offset,'iw')+g_week_offset,trunc(sysdate)),
364 last_update_date = sysdate,
365 last_updated_by = g_user_id,
366 last_update_login = g_login_id
367 where report_date = trunc (l_day)
368 and (ent_period_id <> --l_period_year||l_quarter_num||lpad(l_period_num,2,'0')
369 l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num) or
370 ent_period_start_date <> l_start_date or
371 NVL(ent_qtr_start_date, to_date('01/01/1000', 'DD/MM/YYYY')) <>
372 NVL(l_quarter_start_date, to_date('01/01/1000', 'DD/MM/YYYY')) or
373 NVL(ent_year_start_date, to_date('01/01/1000', 'DD/MM/YYYY')) <>
374 NVL(l_year_start_date, to_date('01/01/1000', 'DD/MM/YYYY')));
375
376 l_day_row := l_day_row + sql%rowcount;
377
378 end if; --for: if l_count = 0
379
380 if (g_all_level='Y') then
381 close ent_period_cur;
382 end if;
383
384 l_period_year := null;
385 l_quarter_num := null;
386 l_period_num := null;
387 l_start_date := null;
388 l_quarter_start_date := null;
389 l_year_start_date := null;
390
391 -- move to the next day
392 l_day := l_day+1;
393 end loop;
394
395 commit;
396 if g_debug_flag = 'Y' then
397 OZF_TP_UTIL_PVT.put_line(to_char(l_day_row)||' records has been populated or updated to Day Level');
398 end if;
399
400 end LOAD_DAY_INC;
401
402 ---------------------------------------------------
403 -- PRIVATE PROCEDURE LOAD_DAY
404 -- even though this procedure is not used currently,
405 -- it is coded as a backup of load_day_inc
406 ---------------------------------------------------
407 PROCEDURE LOAD_DAY(p_from_date in date, p_to_date in date) IS
408
409 -- ---------------------------------------------------------
410 -- Define local variables
411 -- ---------------------------------------------------------
412 l_from_date date;
413 l_to_date date;
414 l_day date;
415 l_week_num number;
416 l_p445_num number;
417 l_year_num number;
418 l_day_row number;
419 l_period_year gl_periods.period_year%TYPE;
420 l_quarter_num gl_periods.quarter_num%TYPE;
421 l_period_num gl_periods.period_num%TYPE;
422 l_start_date gl_periods.start_date%TYPE;
423 l_quarter_start_date gl_periods.quarter_start_date%TYPE;
424 l_year_start_date gl_periods.year_start_date%TYPE;
425
426 cursor ent_period_cur (day date) is
427 select period_year, quarter_num, period_num, start_date, quarter_start_date, year_start_date
428 from gl_periods
429 where adjustment_period_flag='N'
430 and period_set_name=g_period_set_name
431 and period_type=g_period_type
432 and day between start_date and end_date;
433
434 begin
435
436 truncate_table('OZF_TIME_DAY');
437
438 -- ---------------------------------------------------------
439 -- Variable initialization
440 -- ---------------------------------------------------------
441 l_from_date := p_from_date;
442 l_to_date := p_to_date;
443 l_day := l_from_date;
444 l_day_row := 0;
445
446 -- ----------------------
447 -- Populate Day Level
448 -- ----------------------
449 while l_day <= l_to_date loop
450 open ent_period_cur(l_day);
451 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;
452 if ent_period_cur%notfound then
453 raise G_ENT_CALENDAR_NOT_FOUND;
454 else
455 l_week_num := get_week_num(l_day,g_week_offset);
456 l_p445_num := get_period_num(l_week_num);
457 l_year_num := to_char(l_day-g_week_offset,'iyyy');
458 insert into OZF_TIME_DAY
459 (report_date,
460 report_date_julian,
461 start_date,
462 end_date,
463 month_id,
464 ent_period_id,
465 ent_period_start_date,
466 ent_qtr_id,
467 ent_qtr_start_date,
468 ent_year_id,
469 ent_year_start_date,
470 week_id,
471 week_start_date,
472 creation_date,
473 last_update_date,
474 last_updated_by,
475 created_by,
476 last_update_login)
477 values(
478 trunc(l_day),
479 to_char(l_day,'j'),
480 l_day,
481 l_day,
482 to_number(to_char(l_day,'yyyyqmm')),
483 l_period_year||l_quarter_num||decode(length(l_period_num),1,'0'||l_period_num, l_period_num),
484 --l_period_year||l_quarter_num||lpad(l_period_num,2,'0'),
485 l_start_date,
486 l_period_year||l_quarter_num,
487 l_quarter_start_date,
488 l_period_year,
489 l_year_start_date,
490 l_year_num||lpad(l_p445_num,2,'0')||lpad(l_week_num,2,'0'),
491 trunc(l_day-g_week_offset,'iw')+g_week_offset,
492 sysdate,
493 sysdate,
494 g_user_id,
495 g_user_id,
496 g_login_id
497 );
498
499 l_day_row := l_day_row+1;
500 end if;
501 close ent_period_cur;
502 l_period_year := null;
503 l_quarter_num := null;
504 l_period_num := null;
505 l_start_date := null;
506 l_quarter_start_date := null;
507 l_year_start_date := null;
508 l_day := l_day+1;
509 end loop;
510
511 commit;
512 if g_debug_flag = 'Y' then
513 OZF_TP_UTIL_PVT.put_line(to_char(l_day_row)||' records has been populated to Day Level');
514 end if;
515
516 end LOAD_DAY;
517
518 ---------------------------------------------------
519 -- PRIVATE PROCEDURE LOAD_WEEK
520 ---------------------------------------------------
521 PROCEDURE LOAD_WEEK(p_from_date in date, p_to_date in date) IS
522
523 -- ---------------------------------------------------------
524 -- Define local variables
525 -- ---------------------------------------------------------
526 l_from_date date;
527 l_to_date date;
528 l_week date;
529 l_week_end date;
530 l_week_num number;
531 l_period_num number;
532 l_year_num number;
533 l_week_row number;
534
535 begin
536
537 -- ---------------------------------------------------------
538 -- Variable initialization
539 -- ---------------------------------------------------------
540 l_from_date := p_from_date;
541 l_to_date := trunc(p_to_date-g_week_offset,'iw')+g_week_offset+6;
542 l_week := trunc(l_from_date-g_week_offset,'iw')+g_week_offset;
543 l_week_end := l_week+6;
544 l_week_num := get_week_num(l_week,g_week_offset);
545 l_period_num := get_period_num(l_week_num);
546 l_year_num := to_char(l_week-g_week_offset,'iyyy');
547 l_week_row := 0;
548
549 delete from OZF_TIME_WEEK where start_date <= l_to_date and end_date >= l_from_date;
550
551 -- ----------------------
552 -- Populate Week Level
553 -- ----------------------
554 while l_week <= l_to_date loop
555 insert into OZF_TIME_WEEK
556 (week_id,
557 period445_id,
558 sequence,
559 name,
560 start_date,
561 end_date,
562 creation_date,
563 last_update_date,
564 last_updated_by,
565 created_by,
566 last_update_login)
567 values
568 (
569 l_year_num||lpad(l_period_num,2,'0')||lpad(l_week_num,2,'0'),
570 l_year_num||lpad(l_period_num,2,'0'),
571 l_week_num,
572 to_char(l_week_end,'dd-Mon-rr'),
573 l_week,
574 l_week_end,
575 sysdate,
576 sysdate,
577 g_user_id,
578 g_user_id,
579 g_login_id
580 );
581
582 l_week := l_week_end+1;
583 l_week_end := l_week+6;
584 l_week_num := get_week_num(l_week,g_week_offset);
585 l_period_num := get_period_num(l_week_num);
586 l_year_num := to_char(l_week-g_week_offset,'iyyy');
587 l_week_row := l_week_row+1;
588 end loop;
589
590 commit;
591 if g_debug_flag = 'Y' then
592 OZF_TP_UTIL_PVT.put_line(to_char(l_week_row)||' records has been populated to Week Level');
593 end if;
594
595 end LOAD_WEEK;
596
597 ---------------------------------------------------
598 -- PRIVATE PROCEDURE LOAD_ENT_PERIOD
599 ---------------------------------------------------
600 PROCEDURE LOAD_ENT_PERIOD(p_from_date in date, p_to_date in date) IS
601
602 -- ---------------------------------------------------------
603 -- Define local variables
604 -- ---------------------------------------------------------
605 l_from_date date;
606 l_to_date date;
607 l_period_row number;
608
609 begin
610
611 -- ---------------------------------------------------------
612 -- Variable initialization
613 -- ---------------------------------------------------------
614 l_from_date := p_from_date;
615 l_to_date := p_to_date;
616 l_period_row := 0;
617
618 delete from OZF_TIME_ENT_PERIOD where start_date <= l_to_date and end_date >= l_from_date;
619
620 -- ----------------------
621 -- Populate Enterprise Period Level
622 -- ----------------------
623 insert into OZF_TIME_ENT_PERIOD
624 (ent_period_id,
625 ent_qtr_id,
626 ent_year_id,
627 sequence,
628 name,
629 start_date,
630 end_date,
631 creation_date,
632 last_update_date,
633 last_updated_by,
634 created_by,
635 last_update_login)
636 select
637 to_number(period_year||quarter_num||decode(length(period_num),1,'0'||period_num, period_num)),
638 --to_number(period_year||quarter_num||lpad(period_num,2,'0')),
639 to_number(period_year||quarter_num),
640 to_number(period_year),
641 period_num,
642 period_name,
643 start_date,
644 end_date,
645 sysdate,
646 sysdate,
647 g_user_id,
648 g_user_id,
649 g_login_id
650 from gl_periods
651 where period_set_name = g_period_set_name
652 and period_type = g_period_type
653 and adjustment_period_flag='N'
654 and start_date <= l_to_date
655 and end_date >= l_from_date;
656
657 l_period_row := sql%rowcount;
658 commit;
659 if g_debug_flag = 'Y' then
660 OZF_TP_UTIL_PVT.put_line(to_char(l_period_row)||' records has been populated to Enterprise Period Level');
661 end if;
662
663 end LOAD_ENT_PERIOD;
664
665 ---------------------------------------------------
666 -- PRIVATE PROCEDURE LOAD_ENT_QUARTER
667 ---------------------------------------------------
668 PROCEDURE LOAD_ENT_QUARTER(p_from_date in date, p_to_date in date) IS
669
670 -- ---------------------------------------------------------
671 -- Define local variables
672 -- ---------------------------------------------------------
673 l_from_date date;
674 l_to_date date;
675 l_qtr_row number;
676
677 begin
678
679 -- ---------------------------------------------------------
680 -- Variable initialization
681 -- ---------------------------------------------------------
682 l_from_date := p_from_date;
683 l_to_date := p_to_date;
684 l_qtr_row := 0;
685
686 delete from OZF_TIME_ENT_QTR where start_date <= l_to_date and end_date >= l_from_date;
687
688 -- ----------------------
689 -- Populate Enterprise Quarter Level
690 -- ----------------------
691 insert into OZF_TIME_ENT_QTR
692 (ent_qtr_id,
693 ent_year_id,
694 sequence,
695 name,
696 start_date,
697 end_date,
698 creation_date,
699 last_update_date,
700 last_updated_by,
701 created_by,
702 last_update_login)
703 select distinct gl.period_year||gl.quarter_num,
704 gl.period_year,
705 gl.quarter_num,
706 replace(fnd_message.get_string('OZF','OZF_TP_QUARTER_LABEL'),'&QUARTER_NUMBER',gl.quarter_num)||'-'||to_char(to_date(gl.period_year,'yyyy'),'RR'),
707 gl2.start_date,
708 gl2.end_date,
709 sysdate,
710 sysdate,
711 g_user_id,
712 g_user_id,
713 g_login_id
714 from gl_periods gl,
715 (select period_year||quarter_num ent_qtr_pk_key, min(start_date) start_date, max(end_date) end_date
716 from gl_periods
717 where period_set_name=g_period_set_name
718 and period_type=g_period_type
719 and adjustment_period_flag='N'
720 group by period_year||quarter_num) gl2
721 where gl.period_year||gl.quarter_num = gl2.ent_qtr_pk_key
722 and gl.period_set_name = g_period_set_name
723 and gl.period_type = g_period_type
724 and gl.adjustment_period_flag='N'
725 and gl.start_date <= l_to_date
726 and gl.end_date >= l_from_date;
727
728 l_qtr_row := sql%rowcount;
729 commit;
730 if g_debug_flag = 'Y' then
731 OZF_TP_UTIL_PVT.put_line(to_char(l_qtr_row)||' records has been populated to Enterprise Quarter Level');
732 end if;
733
734 end LOAD_ENT_QUARTER;
735
736 ---------------------------------------------------
737 -- PRIVATE PROCEDURE LOAD_ENT_YEAR
738 ---------------------------------------------------
739 PROCEDURE LOAD_ENT_YEAR(p_from_date in date, p_to_date in date) IS
740
741 -- ---------------------------------------------------------
742 -- Define local variables
743 -- ---------------------------------------------------------
744 l_from_date date;
745 l_to_date date;
746 l_year_row number;
747 l_end_date date;
748
749 begin
750
751 -- ---------------------------------------------------------
752 -- Variable initialization
753 -- ---------------------------------------------------------
754 l_from_date := p_from_date;
755 l_to_date := p_to_date;
756 l_year_row := 0;
757
758 select nvl(max(end_date), l_to_date)
759 into l_end_date
760 from ozf_time_ent_period;
761
762 delete from OZF_TIME_ENT_YEAR where ent_year_id in
763 (select period_year
764 from gl_periods
765 where period_set_name = g_period_set_name
766 and period_type = g_period_type
767 and adjustment_period_flag='N'
768 and start_date <= l_to_date
769 and end_date >= l_from_date);
770
771 -- ----------------------
772 -- Populate Enterprise Year Level
773 -- ----------------------
774 insert into OZF_TIME_ENT_YEAR
775 (ent_year_id,
776 period_set_name,
777 period_type,
778 sequence,
779 name,
780 start_date,
781 end_date,
782 creation_date,
783 last_update_date,
784 last_updated_by,
785 created_by,
786 last_update_login)
787 select distinct gl.period_year ent_year_pk_key,
788 gl.period_set_name period_set_name,
789 gl.period_type period_type,
790 gl.period_year,
791 gl.period_year name,
792 gl2.start_date start_date,
793 gl2.end_date end_date,
794 sysdate creation_date,
795 sysdate last_update_date,
796 g_user_id last_updated_by,
797 g_user_id created_by,
798 g_login_id last_update_login
799 from gl_periods gl,
800 (select period_year period_year, min(start_date) start_date, max(end_date) end_date
801 from gl_periods
802 where period_set_name=g_period_set_name
803 and period_type=g_period_type
804 and adjustment_period_flag='N'
805 and end_date <= l_end_date
806 group by period_year) gl2
807 where gl.period_year=gl2.period_year
808 and gl.period_set_name = g_period_set_name
809 and gl.period_type = g_period_type
810 and gl.adjustment_period_flag='N'
811 and gl.start_date <= l_to_date
812 and gl.end_date >= l_from_date;
813
814 l_year_row := sql%rowcount;
815 commit;
816 if g_debug_flag = 'Y' then
817 OZF_TP_UTIL_PVT.put_line(to_char(l_year_row)||' records has been populated to Enterprise Year Level');
818 end if;
819
820 end LOAD_ENT_YEAR;
821
822
823 ---------------------------------------------------
824 -- PUBLIC PROCEDURE LOAD
825 ---------------------------------------------------
826 PROCEDURE LOAD(x_errbuf out NOCOPY varchar2,
827 x_retcode out NOCOPY Varchar2,
828 p_from_date in varchar2,
829 p_to_date in varchar2,
830 p_all_level in varchar2) IS
831
832 -- ---------------------------------------------------------
833 -- Define local variables
834 -- ---------------------------------------------------------
835 l_from_date date;
836 l_to_date date;
837
838 l_global_param_list dbms_sql.varchar2_table;
839
840 begin
841
842 --l_from_date := trunc(to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY');
843 --l_to_date := last_day(add_months(trunc(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'),'YYYY'),11));
844
845 l_from_date := trunc(to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS'));
846 l_to_date := trunc(to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS'));
847 g_all_level := nvl(p_all_level,'Y');
848 if g_debug_flag = 'Y' then
849 OZF_TP_UTIL_PVT.put_line('Data loads from '||l_from_date||' to '||l_to_date);
850 end if;
851
852 ----------------------------------------------------------
853 -- Calling ozf common parameters api to do common set ups
854 -- If it returns false, then program should error out
855 ----------------------------------------------------------
856 g_phase := 'Call ozf common parameters api to do common set ups';
857 l_global_param_list(1) := 'OZF_TP_GLOBAL_START_DATE';
858
859
860 IF (NOT ozf_common_parameters_pvt.check_global_parameters(l_global_param_list)) THEN
861 if g_debug_flag = 'Y' then
862 OZF_TP_UTIL_PVT.put_line('Global Start Date has not been set up. ' ||
863 'Program will exit with error status.');
864 end if;
865 x_retcode := 1;
866 raise G_OZF_PARAMETER_NOT_SETUP;
867 END IF;
868
869
870 g_phase := 'Retrieve the ozf common parameters';
871 INIT;
872
873 g_phase := 'Load Day Level';
874 if g_debug_flag = 'Y' then
875 OZF_TP_UTIL_PVT.start_timer;
876 end if;
877 --*** LOAD_DAY(l_from_date, l_to_date); -- full refresh
878 LOAD_DAY_INC(l_from_date, l_to_date); -- incremental refresh
879 if g_debug_flag = 'Y' then
880 OZF_TP_UTIL_PVT.stop_timer;
881 OZF_TP_UTIL_PVT.print_timer('Process Time');
882 OZF_TP_UTIL_PVT.put_line(' ');
883 end if;
884
885 if (g_all_level = 'Y') then
886 g_phase := 'Load Week Level';
887 if g_debug_flag = 'Y' then
888 OZF_TP_UTIL_PVT.start_timer;
889 end if;
890 LOAD_WEEK(l_from_date, l_to_date);
891 if g_debug_flag = 'Y' then
892 OZF_TP_UTIL_PVT.stop_timer;
893 OZF_TP_UTIL_PVT.print_timer('Process Time');
894 OZF_TP_UTIL_PVT.put_line(' ');
895 end if;
896
897 g_phase := 'Load Enterprise Period Level';
898 if g_debug_flag = 'Y' then
899 OZF_TP_UTIL_PVT.start_timer;
900 end if;
901 LOAD_ENT_PERIOD(l_from_date, l_to_date);
902 if g_debug_flag = 'Y' then
903 OZF_TP_UTIL_PVT.stop_timer;
904 OZF_TP_UTIL_PVT.print_timer('Process Time');
905 OZF_TP_UTIL_PVT.put_line(' ');
906 end if;
907
908 g_phase := 'Load Enterprise Quarter Level';
909 if g_debug_flag = 'Y' then
910 OZF_TP_UTIL_PVT.start_timer;
911 end if;
912 LOAD_ENT_QUARTER(l_from_date, l_to_date);
913 if g_debug_flag = 'Y' then
914 OZF_TP_UTIL_PVT.stop_timer;
915 OZF_TP_UTIL_PVT.print_timer('Process Time');
916 OZF_TP_UTIL_PVT.put_line(' ');
917 end if;
918
919 g_phase := 'Load Enterprise Year Level';
920 if g_debug_flag = 'Y' then
921 OZF_TP_UTIL_PVT.start_timer;
922 end if;
923 LOAD_ENT_YEAR(l_from_date, l_to_date);
924 if g_debug_flag = 'Y' then
925 OZF_TP_UTIL_PVT.stop_timer;
926 OZF_TP_UTIL_PVT.print_timer('Process Time');
927 OZF_TP_UTIL_PVT.put_line(' ');
928 end if;
929
930 g_phase := 'Load Reporting Structure Table';
931 if g_debug_flag = 'Y' then
932 OZF_TP_UTIL_PVT.start_timer;
933 end if;
934 LOAD_TIME_RPT_STRUCT(l_from_date, l_to_date);
935 if g_debug_flag = 'Y' then
936 OZF_TP_UTIL_PVT.stop_timer;
937 OZF_TP_UTIL_PVT.print_timer('Process Time');
938 OZF_TP_UTIL_PVT.put_line(' ');
939 end if;
940
941
942 else
943 truncate_table('OZF_TIME_WEEK');
944 truncate_table('OZF_TIME_ENT_PERIOD');
945 truncate_table('OZF_TIME_ENT_QTR');
946 truncate_table('OZF_TIME_ENT_YEAR');
947 truncate_table('OZF_TIME_RPT_STRUCT');
948 if g_debug_flag = 'Y' then
949 OZF_TP_UTIL_PVT.put_line(' ');
950 end if;
951 end if;
952
953
954 EXCEPTION
955
956 WHEN G_OZF_PARAMETER_NOT_SETUP THEN
957 if g_debug_flag = 'Y' then
958 OZF_TP_UTIL_PVT.put_line(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
959 end if;
960 x_retcode := -1;
961 WHEN G_LOGIN_INFO_NOT_FOUND THEN
962 if g_debug_flag = 'Y' then
963 OZF_TP_UTIL_PVT.put_line('Can not get User ID and Login ID, program exit');
964 end if;
965 x_retcode := -1;
966 WHEN G_ENT_CALENDAR_NOT_FOUND THEN
967 rollback;
968 if g_debug_flag = 'Y' then
969 OZF_TP_UTIL_PVT.put_line(fnd_message.get_string('OZF', 'OZF_TP_ENT_CALENDAR_NOT_FOUND'));
970 end if;
971 x_retcode := -1;
972 WHEN OTHERS THEN
973 rollback;
974 x_retcode := sqlcode;
975 x_errbuf := sqlerrm;
976 if g_debug_flag = 'Y' then
977 OZF_TP_UTIL_PVT.put_line(x_retcode||' : '||x_errbuf);
978 OZF_TP_UTIL_PVT.put_line('
979 -------------------------------------------
980 Error occured in Procedure: LOAD
981 Phase: ' || g_phase);
982 end if;
983 end LOAD;
984
985 ---------------------------------------------------
986 -- PUBLIC PROCEDURE LOAD_TIME_RPT_STRUCT
987 ---------------------------------------------------
988 PROCEDURE LOAD_TIME_RPT_STRUCT(p_from_date in date, p_to_date in date) IS
989
990 -- ---------------------------------------------------------
991 -- Define local variables
992 -- ---------------------------------------------------------
993 l_from_date date;
994 l_to_date date;
995 l_day date;
996 l_week_start_date date;
997 l_ptd_to_date date;
998 l_period_start_date date;
999 l_row number;
1000
1001 --* We should do a full refresh for OZF_TIME_RPT_STRUCT
1002 cursor c1 is
1003 select report_date, ent_period_start_date, ent_qtr_start_date,
1004 ent_year_start_date, week_start_date
1005 from OZF_TIME_DAY;
1006 --*this would be incorrect: where report_date between l_from_date and l_to_date
1007
1008 begin
1009
1010 truncate_table('OZF_TIME_RPT_STRUCT');
1011
1012 l_row := 0;
1013 l_from_date := trunc(nvl(p_from_date,trunc(add_months(sysdate,-24),'YYYY')));
1014 l_to_date := trunc(nvl(p_to_date,trunc(sysdate,'YYYY')));
1015
1016 FOR c1_rec IN c1 LOOP
1017 insert into OZF_TIME_RPT_STRUCT
1018 (calendar_id,
1019 calendar_type,
1020 report_date,
1021 time_id,
1022 period_type_id,
1023 record_type_id,
1024 creation_date,
1025 last_update_date,
1026 last_updated_by,
1027 created_by,
1028 last_update_login)
1029 values
1030 (-1,
1031 'C',
1032 c1_rec.report_date,
1033 to_char(c1_rec.report_date,'j'),
1034 1,
1035 1,
1036 sysdate,
1037 sysdate,
1038 g_user_id,
1039 g_user_id,
1040 g_login_id);
1041
1042 l_row := l_row+1;
1043 l_day:=c1_rec.report_date-1;
1044 l_week_start_date:=c1_rec.week_start_date;
1045 l_period_start_date:=c1_rec.ent_period_start_date;
1046
1047 While l_day >= least(l_week_start_date,l_period_start_date) LOOP
1048 if l_day >= l_period_start_date then
1049 if l_day >= l_week_start_date then
1050 insert into OZF_TIME_RPT_STRUCT
1051 (calendar_id,
1052 calendar_type,
1053 report_date,
1054 time_id,
1055 period_type_id,
1056 record_type_id,
1057 creation_date,
1058 last_update_date,
1059 last_updated_by,
1060 created_by,
1061 last_update_login)
1062 values
1063 (-1,
1064 'C',
1065 c1_rec.report_date,
1066 to_char(l_day,'j'),
1067 1,
1068 2,
1069 sysdate,
1070 sysdate,
1071 g_user_id,
1072 g_user_id,
1073 g_login_id);
1074
1075 l_row := l_row + sql%rowcount;
1076
1077 else
1078 select nvl(min(start_date),l_week_start_date) into l_ptd_to_date from OZF_TIME_WEEK
1079 where start_date >= l_period_start_date
1080 and start_date < l_week_start_date;
1081
1082 if l_day < l_ptd_to_date then
1083 insert into OZF_TIME_RPT_STRUCT
1084 (calendar_id,
1085 calendar_type,
1086 report_date,
1087 time_id,
1088 period_type_id,
1089 record_type_id,
1090 creation_date,
1091 last_update_date,
1092 last_updated_by,
1093 created_by,
1094 last_update_login)
1095 values
1096 (-1,
1097 'C',
1098 c1_rec.report_date,
1099 to_char(l_day,'j'),
1100 1,
1101 4,
1102 sysdate,
1103 sysdate,
1104 g_user_id,
1105 g_user_id,
1106 g_login_id);
1107
1108 l_row := l_row + sql%rowcount;
1109
1110 end if;
1111 end if;
1112 else
1113 if l_day >= l_week_start_date then
1114 insert into OZF_TIME_RPT_STRUCT
1115 (calendar_id,
1116 calendar_type,
1117 report_date,
1118 time_id,
1119 period_type_id,
1120 record_type_id,
1121 creation_date,
1122 last_update_date,
1123 last_updated_by,
1124 created_by,
1125 last_update_login)
1126 values
1127 (-1,
1128 'C',
1129 c1_rec.report_date,
1130 to_char(l_day,'j'),
1131 1,
1132 8,
1133 sysdate,
1134 sysdate,
1135 g_user_id,
1136 g_user_id,
1137 g_login_id);
1138
1139 l_row := l_row + sql%rowcount;
1140
1141 end if;
1142 end if;
1143
1144 l_day:=l_day-1;
1145
1146 END LOOP;
1147
1148 commit;
1149
1150 insert into OZF_TIME_RPT_STRUCT
1151 (calendar_id,
1152 calendar_type,
1153 report_date,
1154 time_id,
1155 period_type_id,
1156 record_type_id,
1157 creation_date,
1158 last_update_date,
1159 last_updated_by,
1160 created_by,
1161 last_update_login)
1162 select
1163 -1,
1164 'E',
1165 c1_rec.report_date,
1166 week_id,
1167 16,
1168 16,
1169 sysdate,
1170 sysdate,
1171 g_user_id,
1172 g_user_id,
1173 g_login_id
1174 from OZF_TIME_WEEK
1175 where start_date >= c1_rec.ent_period_start_date
1176 and end_date < c1_rec.week_start_date;
1177
1178 l_row := l_row + sql%rowcount;
1179 commit;
1180
1181 insert into OZF_TIME_RPT_STRUCT
1182 (calendar_id,
1183 calendar_type,
1184 report_date,
1185 time_id,
1186 period_type_id,
1187 record_type_id,
1188 creation_date,
1189 last_update_date,
1190 last_updated_by,
1191 created_by,
1192 last_update_login)
1193 select
1194 -1,
1195 'E',
1196 c1_rec.report_date,
1197 ent_period_id,
1198 32,
1199 32,
1200 sysdate,
1201 sysdate,
1202 g_user_id,
1203 g_user_id,
1204 g_login_id
1205 from OZF_TIME_ENT_PERIOD
1206 where start_date >= c1_rec.ent_qtr_start_date
1207 and start_date <= c1_rec.ent_period_start_date
1208 and end_date < c1_rec.report_date
1209 union all
1210 select
1211 -1,
1212 'E',
1213 c1_rec.report_date,
1214 ent_period_id,
1215 32,
1216 256,
1217 --case when end_date >= c1_rec.report_date then 256 else 32 end,
1218 sysdate,
1219 sysdate,
1220 g_user_id,
1221 g_user_id,
1222 g_login_id
1223 from OZF_TIME_ENT_PERIOD
1224 where start_date >= c1_rec.ent_qtr_start_date
1225 and start_date <= c1_rec.ent_period_start_date
1226 and end_date >= c1_rec.report_date;
1227
1228 l_row := l_row + sql%rowcount;
1229 commit;
1230
1231 insert into OZF_TIME_RPT_STRUCT
1232 (calendar_id,
1233 calendar_type,
1234 report_date,
1235 time_id,
1236 period_type_id,
1237 record_type_id,
1238 creation_date,
1239 last_update_date,
1240 last_updated_by,
1241 created_by,
1242 last_update_login)
1243 select
1244 -1,
1245 'E',
1246 c1_rec.report_date,
1247 ent_qtr_id,
1248 64,
1249 64,
1250 sysdate,
1251 sysdate,
1252 g_user_id,
1253 g_user_id,
1254 g_login_id
1255 from OZF_TIME_ENT_QTR
1256 where start_date >= c1_rec.ent_year_start_date
1257 and start_date <= c1_rec.ent_qtr_start_date
1258 and end_date < c1_rec.report_date
1259 union all
1260 select
1261 -1,
1262 'E',
1263 c1_rec.report_date,
1264 ent_qtr_id,
1265 64,
1266 512,
1267 --case when end_date >= c1_rec.report_date then 512 else 64 end,
1268 sysdate,
1269 sysdate,
1270 g_user_id,
1271 g_user_id,
1272 g_login_id
1273 from OZF_TIME_ENT_QTR
1274 where start_date >= c1_rec.ent_year_start_date
1275 and start_date <= c1_rec.ent_qtr_start_date
1276 and end_date >= c1_rec.report_date;
1277
1278 l_row := l_row + sql%rowcount;
1279 commit;
1280
1281 insert into OZF_TIME_RPT_STRUCT
1282 (calendar_id,
1283 calendar_type,
1284 report_date,
1285 time_id,
1286 period_type_id,
1287 record_type_id,
1288 creation_date,
1289 last_update_date,
1290 last_updated_by,
1291 created_by,
1292 last_update_login)
1293 select
1294 -1,
1295 'E',
1296 c1_rec.report_date,
1297 ent_year_id,
1298 128,
1299 128,
1300 sysdate,
1301 sysdate,
1302 g_user_id,
1303 g_user_id,
1304 g_login_id
1305 from OZF_TIME_ENT_YEAR
1306 where c1_rec.report_date between start_date and end_date;
1307
1308 l_row := l_row + sql%rowcount;
1309 commit;
1310
1311 -- All prior years (report_type: 1024), for ITD
1312 insert into OZF_TIME_RPT_STRUCT
1313 (calendar_id,
1314 calendar_type,
1315 report_date,
1316 time_id,
1317 period_type_id,
1318 record_type_id,
1319 creation_date,
1320 last_update_date,
1321 last_updated_by,
1322 created_by,
1323 last_update_login)
1324 select
1325 -1,
1326 'E',
1327 c1_rec.report_date,
1328 ent_year_id,
1329 128,
1330 1024,
1331 sysdate,
1332 sysdate,
1333 g_user_id,
1334 g_user_id,
1335 g_login_id
1336 from OZF_TIME_ENT_YEAR
1337 where end_date >= g_global_start_date -- should we use start_date?
1338 and end_date < c1_rec.report_date;
1339
1340 l_row := l_row + sql%rowcount;
1341 commit;
1342
1343 END LOOP; -- c1_rec
1344
1345 if g_debug_flag = 'Y' then
1346 OZF_TP_UTIL_PVT.put_line(to_char(l_row)||' records has been populated to the Reporting Structure table');
1347 end if;
1348
1349 end LOAD_TIME_RPT_STRUCT;
1350
1351 ---------------------------------------------------
1352 -- PUBLIC FUNCTION DEFAULT_LOAD_FROM_DATE
1353 -- this function is used to return the default load
1354 -- from date to the concurrent program parameter
1355 ---------------------------------------------------
1356 FUNCTION DEFAULT_LOAD_FROM_DATE(p_load_mode in varchar2)
1357 return varchar2
1358 is
1359
1360 l_return_date date;
1361
1362 begin
1363 if p_load_mode = 'INCRE' then
1364
1365 select greatest(NVL(max(end_date)+1,ozf_common_parameters_pvt.get_global_start_date),
1366 ozf_common_parameters_pvt.get_global_start_date)
1367 into l_return_date
1368 from ozf_time_day;
1369
1370 else
1371
1372 select least(nvl(min(start_date),ozf_common_parameters_pvt.get_global_start_date) ,
1373 ozf_common_parameters_pvt.get_global_start_date)
1374 into l_return_date
1375 from ozf_time_day;
1376 end if;
1377
1378 return fnd_date.date_to_displaydt(l_return_date);
1379
1380 end DEFAULT_LOAD_FROM_DATE;
1381
1382 ---------------------------------------------------
1383 -- PUBLIC FUNCTION DEFAULT_LOAD_TO_DATE
1384 -- this function is used to return the default load
1385 -- to date to the concurrent program parameter
1386 ---------------------------------------------------
1387 FUNCTION DEFAULT_LOAD_TO_DATE
1388 return varchar2
1389 is
1390
1391 l_return_date date;
1392 l_period_set_name varchar2(15) := ozf_common_parameters_pvt.get_period_set_name;
1393 l_period_type varchar2(15) := ozf_common_parameters_pvt.get_period_type;
1394
1395 begin
1396
1397 select max(end_date)
1398 into l_return_date
1399 from gl_periods
1400 where adjustment_period_flag = 'N'
1401 and period_set_name = l_period_set_name
1402 and period_type = l_period_type;
1403
1404 return fnd_date.date_to_displaydt(l_return_date);
1405
1406 end DEFAULT_LOAD_TO_DATE;
1407
1408
1409 END OZF_TIME_PVT;