[Home] [Help]
PACKAGE BODY: APPS.PJI_TIME_C
Source
1 PACKAGE BODY PJI_TIME_C AS
2 /*$Header: PJICMT1B.pls 120.3 2005/10/17 18:09:43 appldev noship $*/
3
4
5 -- ------------------------
6 -- Global Variables
7 -- ------------------------
8 g_timer_start DATE := NULL;
9 g_duration NUMBER := NULL;
10
11 g_period_set_name VARCHAR2(15) := NULL;
12 g_period_type VARCHAR2(15) := NULL;
13 g_week_start_day VARCHAR2(30) := NULL;
14 g_week_offset NUMBER;
15 g_user_id NUMBER;
16 g_login_id NUMBER;
17 g_debug_flag VARCHAR2(1) := NVL(Fnd_Profile.VALUE('PA_DEBUG_MODE'), 'N');
18 g_earliest_start_date DATE;
19 g_latest_end_date DATE;
20 g_min_date DATE;
21 g_cal_info_exists VARCHAR2(1):='Y';
22
23 G_LOGIN_INFO_NOT_FOUND EXCEPTION;
24 G_BIS_PARAMETER_NOT_SETUP EXCEPTION;
25 G_ENT_CALENDAR_NOT_FOUND EXCEPTION;
26
27 ---------------------------------------------------
28 -- Forward declarations of provide procedures
29 ---------------------------------------------------
30
31
32 ---------------------------------------------------
33 -- PRIVATE PROCEDURE start_timer
34 -- This procedure resets the elapsed time duration
35 -- and starts the clock.
36 ---------------------------------------------------
37 PROCEDURE start_timer IS
38 BEGIN
39 g_duration := 0;
40 g_timer_start := SYSDATE;
41 END start_timer;
42
43
44 ---------------------------------------------------
45 -- PRIVATE PROCEDURE stop_timer
46 -- This procedure computes the elapsed time since
47 -- call to to start_timer.
48 ---------------------------------------------------
49 PROCEDURE stop_timer IS
50 BEGIN
51 IF g_timer_start IS NULL THEN
52 g_duration := 0;
53 ELSE
54 g_duration := SYSDATE - g_timer_start;
55 END IF;
56 g_timer_start := NULL;
57 END stop_timer;
58
59 ---------------------------------------------------
60 -- PRIVATE PROCEDURE print_timer
61 -- This procedure prints the elapsed time stored
62 -- in the g_duration global variable.
63 ---------------------------------------------------
64 PROCEDURE print_timer(p_text VARCHAR2)
65 IS
66 BEGIN
67 IF (g_duration IS NOT NULL) THEN
68 pji_utils.write2log(p_text||' - '||
69 TO_CHAR(FLOOR(g_duration)) ||' Days '||
70 TO_CHAR(MOD(FLOOR(g_duration*24), 24))||':'||
71 TO_CHAR(MOD(FLOOR(g_duration*24*60), 60))||':'||
72 TO_CHAR(MOD(FLOOR(g_duration*24*60*60), 60)));
73 END IF;
74 END print_timer;
75
76 ---------------------------------------------------
77 -- PRIVATE FUNCTION get_week_offset
78 -- This function returns the week offset.
79 ---------------------------------------------------
80 FUNCTION GET_WEEK_OFFSET(p_week_start_day VARCHAR2) RETURN NUMBER IS
81 l_week_offset NUMBER;
82 BEGIN
83 IF p_week_start_day = '2' THEN
84 l_week_offset := 0;
85 ELSIF p_week_start_day = '3' THEN
86 l_week_offset := 1;
87 ELSIF p_week_start_day = '4' THEN
88 l_week_offset := 2;
89 ELSIF p_week_start_day = '5' THEN
90 l_week_offset := 3;
91 ELSIF p_week_start_day = '6' THEN
92 l_week_offset := -3;
93 ELSIF p_week_start_day = '7' THEN
94 l_week_offset := -2;
95 ELSIF p_week_start_day = '1' THEN
96 l_week_offset := -1;
97 END IF;
98 RETURN l_week_offset;
99 END GET_WEEK_OFFSET;
100
101 ---------------------------------------------------
102 -- PRIVATE FUNCTION get_period_num
103 -- This function returns 445 calendar's period
104 -- number for a given week number.
105 ---------------------------------------------------
106 FUNCTION GET_PERIOD_NUM(p_week_num NUMBER) RETURN NUMBER IS
107 l_period_num NUMBER;
108 BEGIN
109 IF p_week_num IN (1,2,3,4) THEN
110 l_period_num := 1;
111 ELSIF p_week_num IN (5,6,7,8) THEN
112 l_period_num := 2;
113 ELSIF p_week_num IN (9,10,11,12,13) THEN
114 l_period_num := 3;
115 ELSIF p_week_num IN (14,15,16,17) THEN
116 l_period_num := 4;
117 ELSIF p_week_num IN (18,19,20,21) THEN
118 l_period_num := 5;
119 ELSIF p_week_num IN (22,23,24,25,26) THEN
120 l_period_num := 6;
121 ELSIF p_week_num IN (27,28,29,30) THEN
122 l_period_num := 7;
123 ELSIF p_week_num IN (31,32,33,34) THEN
124 l_period_num := 8;
125 ELSIF p_week_num IN (35,36,37,38,39) THEN
126 l_period_num := 9;
127 ELSIF p_week_num IN (40,41,42,43) THEN
128 l_period_num := 10;
129 ELSIF p_week_num IN (44,45,46,47) THEN
130 l_period_num := 11;
131 ELSE
132 l_period_num := 12;
133 END IF;
134 RETURN l_period_num;
135 END GET_PERIOD_NUM;
136
137 ---------------------------------------------------
138 -- PRIVATE PROCEDURE init
139 -- This procedure initializes all global variables
140 -- used in the package.
141 ---------------------------------------------------
142 PROCEDURE INIT IS
143 BEGIN
144 -- -------------------------------
145 -- Initialize the global variables
146 -- -------------------------------
147 g_user_id := Fnd_Global.User_Id;
148 g_login_id := Fnd_Global.Login_Id;
149
150 IF (g_user_id IS NULL OR g_login_id IS NULL) THEN
151 g_user_id:=-1;
152 g_login_id:=-1;
153 RAISE G_LOGIN_INFO_NOT_FOUND;
154 END IF;
155
156 /*
157 ** replace Bis_Common_Parameters with pji stuff
158 */
159 g_period_set_name := pji_utils.get_period_set_name;
160 g_period_type := pji_utils.get_period_type;
161 g_week_start_day := pji_utils.get_start_day_of_week_id;
162 --g_global_start_date := pji_utils.get_GLOBAL_START_DATE;
163 g_week_offset := get_week_offset(g_week_start_day);
164
165 BEGIN
166 SELECT NVL(earliest_start_date,TRUNC(SYSDATE))
167 , NVL(latest_end_date,TRUNC(SYSDATE))
168 INTO
169 g_earliest_start_date
170 , g_latest_end_date
171 FROM pji_time_cal_extr_info
172 WHERE calendar_id = -1;
173 EXCEPTION
174 WHEN NO_DATA_FOUND THEN
175 g_cal_info_exists:='N';
176 g_earliest_start_date:=TRUNC(SYSDATE);
177 g_latest_end_date:=TRUNC(SYSDATE);
178 END;
179
180 IF g_debug_flag = 'Y' THEN
181 pji_utils.write2log('Enterprise Calendar = '||g_period_set_name||' ('||g_period_type||')');
182 pji_utils.write2log('Week Start Day = '||g_week_start_day);
183 pji_utils.write2log('Week offset = '||g_week_offset);
184 pji_utils.write2log('Earliest Start Date: '||Fnd_Date.date_to_displaydate(g_earliest_start_date));
185 pji_utils.write2log('Latest End Date: '||Fnd_Date.date_to_displaydate(g_latest_end_date));
186 END IF;
187
188 IF (g_period_set_name IS NULL OR g_period_type IS NULL) THEN
189 RAISE G_BIS_PARAMETER_NOT_SETUP;
190 END IF;
191 END INIT;
192
193 ---------------------------------------------------
194 -- PRIVATE PROCEDURE load_week
195 -- This procedure maintains week records for given
196 -- from and to dates.
197 ---------------------------------------------------
198 PROCEDURE LOAD_WEEK(p_from_date IN DATE, p_to_date IN DATE) IS
199 l_from_date DATE;
200 l_to_date DATE;
201 l_week DATE;
202 l_week_end DATE;
203 l_week_num NUMBER;
204 l_period_num NUMBER;
205 l_year_num NUMBER;
206 l_week_row NUMBER;
207 BEGIN
208
209 l_to_date := TRUNC(p_to_date-g_week_offset,'iw')+g_week_offset+6;
210 l_week := TRUNC(l_from_date-g_week_offset,'iw')+g_week_offset;
211 l_week_end := l_week+6;
212 l_week_num := TO_CHAR(l_week-g_week_offset,'iw');
213 l_period_num := get_period_num(l_week_num);
214 l_year_num := TO_CHAR(l_week-g_week_offset,'iyyy');
215 l_week_row := 0;
216
217 -- DELETE FROM PJI_TIME_WEEK WHERE start_date <= l_to_date AND end_date >= l_from_date;
218
219 WHILE l_week <= l_to_date LOOP
220
221 INSERT INTO pji_time_week
222 (week_id,
223 period445_id,
224 SEQUENCE,
225 NAME,
226 start_date,
227 end_date,
228 creation_date,
229 last_update_date,
230 last_updated_by,
231 created_by,
232 last_update_login)
233 VALUES
234 (
235 l_year_num||LPAD(l_period_num,2,'0')||LPAD(l_week_num,2,'0'),
236 l_year_num||LPAD(l_period_num,2,'0'),
237 TO_CHAR(l_week-g_week_offset,'iw'),
238 TO_CHAR(l_week_end,'dd-Mon-rr'),
239 l_week,
240 l_week_end,
241 SYSDATE,
242 SYSDATE,
243 g_user_id,
244 g_user_id,
245 g_login_id
246 );
247
248 l_week := l_week_end+1;
249 l_week_end := l_week+6;
250 l_period_num := get_period_num(TO_CHAR(l_week-g_week_offset,'iw'));
251 l_year_num := TO_CHAR(l_week-g_week_offset,'iyyy');
252 l_week_row := l_week_row+1;
253 END LOOP;
254 IF g_debug_flag = 'Y' THEN
255 pji_utils.write2log(TO_CHAR(l_week_row)||' records has been populated to Week Level');
256 END IF;
257 END LOAD_WEEK;
258
259 ---------------------------------------------------
260 -- PRIVATE PROCEDURE load_ent_period
261 -- This procedure maintains week records for given
262 -- from and to dates.
263 ---------------------------------------------------
264 FUNCTION LOAD_ENT_PERIOD RETURN BOOLEAN IS
265 l_no_rows_inserted NUMBER := 0;
266 l_min_date DATE;
267 l_max_date DATE;
268 BEGIN
269
270 DELETE pji_time_extr_tmp;
271
272 INSERT INTO pji_time_extr_tmp(period_year
273 , quarter_num
274 , period_num
275 , period_name
276 , start_date
277 , end_date)
278 SELECT period_year
279 , quarter_num
280 , period_num
281 , period_name
282 , start_date
283 , end_date
284 FROM gl_periods
285 WHERE 1=1
286 AND period_set_name = g_period_set_name
287 AND period_type = g_period_type
288 AND adjustment_period_flag='N'
289 AND (start_date < g_earliest_start_date
290 OR end_date > g_latest_end_date);
291
292 INSERT INTO pji_time_ent_period(
293 ent_period_id
294 , ent_qtr_id
295 , ent_year_id
296 , sequence
297 , name
298 , start_date
299 , end_date
300 , creation_date
301 , last_update_date
302 , last_updated_by
303 , created_by
304 , last_update_login)
305 SELECT TO_NUMBER(period_year||quarter_num||DECODE(LENGTH(period_num),1,'0'||period_num, period_num))
306 , TO_NUMBER(period_year||quarter_num)
307 , TO_NUMBER(period_year)
308 , period_num
309 , period_name
310 , start_date
311 , end_date
312 , SYSDATE
313 , SYSDATE
314 , g_user_id
315 , g_user_id
316 , g_login_id
317 FROM pji_time_extr_tmp;
318
319 l_no_rows_inserted := SQL%rowcount;
320
321 IF g_debug_flag = 'Y' THEN
322 pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_PERIOD table.');
323 END IF;
324
325 IF l_no_rows_inserted > 0 THEN
326 SELECT MIN(start_date), MAX(end_date)
327 INTO l_min_date, l_max_date
328 FROM pji_time_ent_period
329 WHERE (start_date < g_earliest_start_date
330 OR end_date > g_latest_end_date);
331
332 g_min_date:=l_min_date;
333
334 IF g_cal_info_exists = 'N' THEN
335 BEGIN
336 IF g_debug_flag = 'Y' THEN
337 pji_utils.write2log('Trying to insert record into PJI_TIME_EXTR_INFO table.');
338 END IF;
339 INSERT INTO pji_time_cal_extr_info
340 (calendar_id, earliest_start_date,
341 latest_end_date, creation_date,
342 last_update_date, last_updated_by,
343 created_by, last_update_login)
344 VALUES (-1, l_min_date, l_max_date,
345 sysdate, sysdate, -1, -1, -1);
346 EXCEPTION
347 WHEN DUP_VAL_ON_INDEX THEN
348 IF g_debug_flag = 'Y' THEN
349 pji_utils.write2log('Duplicate records. Now trying to update the record...');
350 END IF;
351 UPDATE pji_time_cal_extr_info
352 SET earliest_start_date = LEAST(l_min_date, earliest_start_date)
353 , latest_end_date = GREATEST(l_max_date, latest_end_date)
354 WHERE calendar_id = -1;
355 g_cal_info_exists:='Y';
356 END;
357 ELSE
358 UPDATE pji_time_cal_extr_info
359 SET earliest_start_date = LEAST(l_min_date, earliest_start_date)
360 , latest_end_date = GREATEST(l_max_date, latest_end_date)
361 WHERE calendar_id = -1;
362 END IF;
363
364 --g_earliest_start_date:=LEAST(l_min_date, g_earliest_start_date);
365 --g_latest_end_date:=GREATEST(l_max_date, g_latest_end_date);
366
367 IF g_debug_flag = 'Y' THEN
368 pji_utils.write2log('Calendar ID: -1');
369 pji_utils.write2log('New Earliest Start Date: '||Fnd_Date.date_to_displaydate(l_min_date));
370 pji_utils.write2log('New Latest End Date: '||Fnd_Date.date_to_displaydate(l_max_date));
371 END IF;
372
373 END IF;
374
375 IF l_no_rows_inserted >0 THEN
376 RETURN TRUE;
377 ELSE
378 RETURN FALSE;
379 END IF;
380 EXCEPTION
381 WHEN DUP_VAL_ON_INDEX THEN
382 IF g_debug_flag = 'Y' THEN
383 pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_PERIOD table.');
384 END IF;
385 RETURN FALSE;
386 END LOAD_ENT_PERIOD;
387
388 ---------------------------------------------------
389 -- PRIVATE PROCEDURE load_ent_qtr
390 -- This procedure incrementally maintains quarter
391 -- entries in PJI_TIME_ENT_QTR.
392 ---------------------------------------------------
393 PROCEDURE LOAD_ENT_QTR IS
394 l_no_rows_deleted NUMBER := 0;
395 l_no_rows_inserted NUMBER := 0;
396 l_start_qtr_id NUMBER;
397 l_end_qtr_id NUMBER;
398 l_earliest_qtr_end_date DATE;
399 l_latest_qtr_start_date DATE;
400 BEGIN
401 IF g_cal_info_exists = 'Y' THEN
402 SELECT
403 MIN(ent_qtr_id) start_qtr_id
404 , MAX(ent_qtr_id) end_qtr_id
405 , MIN(end_date) earliest_qtr_end_date
406 , MAX(start_date) latest_qtr_start_date
407 INTO
408 l_start_qtr_id
409 , l_end_qtr_id
410 , l_earliest_qtr_end_date
411 , l_latest_qtr_start_date
412 FROM pji_time_ent_qtr
413 WHERE 1=1
414 AND (g_earliest_start_date BETWEEN start_date AND end_date)
415 OR (g_latest_end_date BETWEEN start_date AND end_date);
416 IF g_debug_flag = 'Y' THEN
417 pji_utils.write2log('l_start_qtr_id: '||l_start_qtr_id);
418 pji_utils.write2log('l_end_qtr_id: '||l_end_qtr_id);
419 pji_utils.write2log('l_earliest_qtr_end_date: '||l_earliest_qtr_end_date);
420 pji_utils.write2log('l_latest_qtr_start_date: '||l_latest_qtr_start_date);
421 END IF;
422 END IF;
423
424 DELETE FROM pji_time_ent_qtr
425 WHERE ent_qtr_id in (SELECT DISTINCT period_year||quarter_num FROM pji_time_extr_tmp);
426
427 l_no_rows_deleted := SQL%rowcount;
428
429 IF g_debug_flag = 'Y' THEN
430 pji_utils.write2log('Some records have to be refreshed in PJI_TIME_ENT_QTR table.');
431 pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_QTR table.');
432 END IF;
433
434 INSERT INTO pji_time_ent_qtr
435 (ent_qtr_id
436 , ent_year_id
437 , SEQUENCE
438 , NAME
439 , start_date
440 , end_date
441 , creation_date
442 , last_update_date
443 , last_updated_by
444 , created_by
445 , last_update_login)
446 SELECT period_year||quarter_num
447 , period_year
448 , quarter_num
449 , REPLACE(Fnd_Message.get_string('PJI','PJI_QUARTER_LABEL'),'&QUARTER_NUMBER',quarter_num)
450 ||'-'||
451 TO_CHAR(TO_DATE(period_year,'yyyy'),'RR')
452 , DECODE(period_year||quarter_num,l_end_qtr_id,l_latest_qtr_start_date,MIN(start_date))
453 , DECODE(period_year||quarter_num,l_start_qtr_id,l_earliest_qtr_end_date,MAX(end_date))
454 , SYSDATE
455 , SYSDATE
456 , g_user_id
457 , g_user_id
458 , g_login_id
459 FROM pji_time_extr_tmp
460 GROUP BY
461 period_year||quarter_num
462 , period_year
463 , quarter_num
464 HAVING MAX(end_date)<g_earliest_start_date
465 OR MIN(start_date)>g_latest_end_date
466 OR g_cal_info_exists = 'N';
467
468 l_no_rows_inserted := SQL%rowcount;
469
470 IF g_debug_flag = 'Y' THEN
471 pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_QTR table.');
472 END IF;
473
474 EXCEPTION
475 WHEN DUP_VAL_ON_INDEX THEN
476 IF g_debug_flag = 'Y' THEN
477 pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_QTR table.');
478 END IF;
479 END LOAD_ENT_QTR;
480
481 ---------------------------------------------------
482 -- PRIVATE PROCEDURE load_ent_year
483 -- This procedure incrementally maintains year
484 -- entries in PJI_TIME_ENT_YEAR.
485 ---------------------------------------------------
486 PROCEDURE LOAD_ENT_YEAR IS
487 l_no_rows_deleted NUMBER := 0;
488 l_no_rows_inserted NUMBER := 0;
489 l_start_yr_id NUMBER;
490 l_end_yr_id NUMBER;
491 l_earliest_yr_end_date DATE;
492 l_latest_yr_start_date DATE;
493 BEGIN
494 IF g_cal_info_exists = 'Y' THEN
495 SELECT
496 MIN(ent_year_id) start_yr_id
497 , MAX(ent_year_id) end_yr_id
498 , MIN(end_date) earliest_yr_end_date
499 , MAX(start_date) latest_yr_start_date
500 INTO
501 l_start_yr_id
502 , l_end_yr_id
503 , l_earliest_yr_end_date
504 , l_latest_yr_start_date
505 FROM pji_time_ent_year
506 WHERE 1=1
507 AND (g_earliest_start_date BETWEEN start_date AND end_date)
508 OR (g_latest_end_date BETWEEN start_date AND end_date);
509 IF g_debug_flag = 'Y' THEN
510 pji_utils.write2log('l_start_yr_id: '||l_start_yr_id);
511 pji_utils.write2log('l_end_yr_id: '||l_end_yr_id);
512 pji_utils.write2log('l_earliest_yr_end_date: '||l_earliest_yr_end_date);
513 pji_utils.write2log('l_latest_yr_start_date: '||l_latest_yr_start_date);
514 END IF;
515 END IF;
516
517 DELETE FROM pji_time_ent_year
518 WHERE ent_year_id in (SELECT DISTINCT period_year from pji_time_extr_tmp);
519
520 l_no_rows_deleted := SQL%rowcount;
521
522 IF g_debug_flag = 'Y' THEN
523 pji_utils.write2log('Some records have to be refreshed in PJI_TIME_ENT_YEAR table.');
524 pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_ENT_YEAR table.');
525 END IF;
526
527 INSERT INTO pji_time_ent_year
528 (ent_year_id
529 , period_set_name
530 , period_type
531 , SEQUENCE
532 , NAME
533 , start_date
534 , end_date
535 , creation_date
536 , last_update_date
537 , last_updated_by
538 , created_by
539 , last_update_login)
540 SELECT period_year
541 , g_period_set_name
542 , g_period_type
543 , period_year
544 , period_year
545 , DECODE(period_year,l_end_yr_id,l_latest_yr_start_date,MIN(start_date))
546 , DECODE(period_year,l_start_yr_id,l_earliest_yr_end_date,MAX(end_date))
547 , SYSDATE
548 , SYSDATE
549 , g_user_id
550 , g_user_id
551 , g_login_id
552 FROM pji_time_extr_tmp
553 GROUP BY period_year
554 HAVING MAX(end_date)<g_earliest_start_date
555 OR MIN(start_date)>g_latest_end_date
556 OR g_cal_info_exists = 'N';
557
558 l_no_rows_inserted := SQL%rowcount;
559
560 IF g_debug_flag = 'Y' THEN
561 pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_ENT_YEAR table.');
562 END IF;
563
564 EXCEPTION
565 WHEN DUP_VAL_ON_INDEX THEN
566 IF g_debug_flag = 'Y' THEN
567 pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_ENT_YEAR table.');
568 END IF;
569 END LOAD_ENT_YEAR;
570
571 FUNCTION LOAD_CAL_PERIOD( p_calendar_id NUMBER
572 , p_period_set_name VARCHAR2
573 , p_period_type VARCHAR2
574 , p_earliest_start_date DATE
575 , p_latest_end_date DATE
576 , p_cal_info_exists VARCHAR2
577 , p_min_date IN OUT NOCOPY DATE ) RETURN BOOLEAN IS
578 l_no_rows_inserted NUMBER := 0;
579 l_min_date DATE;
580 l_max_date DATE;
581 BEGIN
582 DELETE pji_time_extr_tmp;
583
584 INSERT INTO pji_time_extr_tmp(period_year
585 , quarter_num
586 , period_num
587 , period_name
588 , start_date
589 , end_date)
590 SELECT period_year
591 , quarter_num
592 , period_num
593 , period_name
594 , start_date
595 , end_date
596 FROM gl_periods
597 WHERE 1=1
598 AND period_set_name = p_period_set_name
599 AND period_type = p_period_type
600 AND adjustment_period_flag='N'
601 AND (start_date < p_earliest_start_date
602 OR end_date > p_latest_end_date);
603
604 INSERT INTO pji_time_cal_period(cal_period_id
605 , cal_qtr_id
606 , calendar_id
607 , SEQUENCE
608 , NAME
609 , start_date
610 , end_date
611 , creation_date
612 , last_update_date
613 , last_updated_by
614 , created_by
615 , last_update_login)
616 SELECT LPAD(p_calendar_id,3,'0')||period_year||quarter_num
617 ||DECODE(LENGTH(period_num),1,'0'||period_num, period_num)
618 , LPAD(p_calendar_id,3,'0')||period_year||quarter_num
619 , LPAD(p_calendar_id,3,'0')
620 , period_num
621 , period_name
622 , start_date
623 , end_date
624 , SYSDATE
625 , SYSDATE
626 , g_user_id
627 , g_user_id
628 , g_login_id
629 FROM pji_time_extr_tmp;
630
631 l_no_rows_inserted := SQL%rowcount;
632
633 IF g_debug_flag = 'Y' THEN
634 pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_PERIOD table.');
635 END IF;
636
637 IF l_no_rows_inserted > 0 THEN
638 SELECT MIN(start_date), MAX(end_date)
639 INTO l_min_date, l_max_date
640 FROM pji_time_cal_period
641 WHERE (start_date < p_earliest_start_date
642 OR end_date > p_latest_end_date)
643 AND calendar_id = p_calendar_id;
644
645 p_min_date:=l_min_date;
646
647 IF p_cal_info_exists = 'N' THEN
648 BEGIN
649 IF g_debug_flag = 'Y' THEN
650 pji_utils.write2log('Trying to insert record into PJI_TIME_EXTR_INFO table.');
651 END IF;
652 INSERT INTO pji_time_cal_extr_info
653 (calendar_id, earliest_start_date,
654 latest_end_date, creation_date,
655 last_update_date, last_updated_by,
656 created_by, last_update_login)
657 VALUES (p_calendar_id, l_min_date, l_max_date,
658 sysdate, sysdate, -1, -1, -1);
659 EXCEPTION
660 WHEN DUP_VAL_ON_INDEX THEN
661 IF g_debug_flag = 'Y' THEN
662 pji_utils.write2log('Duplicate records. Now trying to update the record...');
663 END IF;
664 UPDATE pji_time_cal_extr_info
665 SET earliest_start_date = LEAST(l_min_date, earliest_start_date)
666 , latest_end_date = GREATEST(l_max_date, latest_end_date)
667 WHERE calendar_id = p_calendar_id;
668 END;
669 ELSE
670 UPDATE pji_time_cal_extr_info
671 SET earliest_start_date = LEAST(l_min_date, earliest_start_date)
672 , latest_end_date = GREATEST(l_max_date, latest_end_date)
673 WHERE calendar_id = p_calendar_id;
674 END IF;
675
676 IF g_debug_flag = 'Y' THEN
677 pji_utils.write2log('Calendar ID: '||p_calendar_id);
678 pji_utils.write2log('Earliest Start Date: '||Fnd_Date.date_to_displaydate(l_min_date));
679 pji_utils.write2log('Latest End Date: '||Fnd_Date.date_to_displaydate(l_max_date));
680 END IF;
681
682 END IF;
683 IF l_no_rows_inserted >0 THEN
684 RETURN TRUE;
685 ELSE
686 RETURN FALSE;
687 END IF;
688 EXCEPTION
689 WHEN DUP_VAL_ON_INDEX THEN
690 IF g_debug_flag = 'Y' THEN
691 pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_CAL_PERIOD table.');
692 END IF;
693 RETURN FALSE;
694 END LOAD_CAL_PERIOD;
695
696 ---------------------------------------------------
697 -- PRIVATE PROCEDURE load_cal_qtr
698 -- This procedure incrementally maintains quarter
699 -- entries in PJI_TIME_CAL_YEAR.
700 ---------------------------------------------------
701 PROCEDURE LOAD_CAL_QUARTER(p_calendar_id NUMBER
702 , p_earliest_start_date DATE
703 , p_latest_end_date DATE
704 , p_cal_info_exists VARCHAR2)
705 IS
706 l_no_rows_deleted NUMBER := 0;
707 l_no_rows_inserted NUMBER := 0;
708 l_start_qtr_id NUMBER;
709 l_end_qtr_id NUMBER;
710 l_earliest_qtr_end_date DATE;
711 l_latest_qtr_start_date DATE;
712 BEGIN
713 IF p_cal_info_exists = 'Y' THEN
714 SELECT
715 MIN(cal_qtr_id) start_qtr_id
716 , MAX(cal_qtr_id) end_qtr_id
717 , MIN(end_date) earliest_qtr_end_date
718 , MAX(start_date) latest_qtr_start_date
719 INTO
720 l_start_qtr_id
721 , l_end_qtr_id
722 , l_earliest_qtr_end_date
723 , l_latest_qtr_start_date
724 FROM pji_time_cal_qtr
725 WHERE 1=1
726 AND calendar_id = p_calendar_id
727 AND (p_earliest_start_date BETWEEN start_date AND end_date)
728 OR (p_latest_end_date BETWEEN start_date AND end_date);
729 IF g_debug_flag = 'Y' THEN
730 pji_utils.write2log('l_start_qtr_id: '||l_start_qtr_id);
731 pji_utils.write2log('l_end_qtr_id: '||l_end_qtr_id);
732 pji_utils.write2log('l_earliest_qtr_end_date: '||l_earliest_qtr_end_date);
733 pji_utils.write2log('l_latest_qtr_start_date: '||l_latest_qtr_start_date);
734 END IF;
735 END IF;
736
737 DELETE FROM pji_time_cal_qtr
738 WHERE cal_qtr_id in (SELECT DISTINCT LPAD(p_calendar_id,3,'0')||period_year||quarter_num FROM pji_time_extr_tmp);
739
740 l_no_rows_deleted := SQL%rowcount;
741
742 IF g_debug_flag = 'Y' THEN
743 pji_utils.write2log('Some records have to be refreshed in PJI_TIME_CAL_QTR table.');
744 pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_CAL_QTR table.');
745 END IF;
746
747 INSERT INTO pji_time_cal_qtr
748 (cal_qtr_id
749 , cal_year_id
750 , calendar_id
751 , SEQUENCE
752 , NAME
753 , start_date
754 , end_date
755 , creation_date
756 , last_update_date
757 , last_updated_by
758 , created_by
759 , last_update_login)
760 SELECT LPAD(p_calendar_id,3,'0')||period_year||quarter_num
761 , LPAD(p_calendar_id,3,'0')||period_year
762 , LPAD(p_calendar_id,3,'0')
763 , quarter_num
764 , TO_CHAR(quarter_num)||', '||TO_CHAR(period_year)
765 , DECODE(LPAD(p_calendar_id,3,'0')||period_year||quarter_num,l_end_qtr_id,l_latest_qtr_start_date,MIN(start_date))
766 , DECODE(LPAD(p_calendar_id,3,'0')||period_year||quarter_num,l_start_qtr_id,l_earliest_qtr_end_date,MAX(end_date))
767 , SYSDATE
768 , SYSDATE
769 , g_user_id
770 , g_user_id
771 , g_login_id
772 FROM pji_time_extr_tmp
773 GROUP BY period_year||quarter_num
774 , period_year
775 , quarter_num
776 HAVING MAX(end_date)<p_earliest_start_date
777 OR MIN(start_date)>p_latest_end_date
778 OR p_cal_info_exists = 'N';
779
780
781 l_no_rows_inserted := SQL%rowcount;
782
783 IF g_debug_flag = 'Y' THEN
784 pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_QTR table.');
785 END IF;
786
787 EXCEPTION
788 WHEN DUP_VAL_ON_INDEX THEN
789 IF g_debug_flag = 'Y' THEN
790 pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_CAL_QTR table.');
791 END IF;
792 END LOAD_CAL_QUARTER;
793
794 ---------------------------------------------------
795 -- PRIVATE PROCEDURE load_cal_year
796 -- This procedure incrementally maintains year
797 -- entries in PJI_TIME_CAL_YEAR.
798 ---------------------------------------------------
799 PROCEDURE LOAD_CAL_YEAR(p_calendar_id NUMBER
800 , p_earliest_start_date DATE
801 , p_latest_end_date DATE
802 , p_cal_info_exists VARCHAR2) IS
803 l_no_rows_deleted NUMBER := 0;
804 l_no_rows_inserted NUMBER := 0;
805 l_start_yr_id NUMBER;
806 l_end_yr_id NUMBER;
807 l_earliest_yr_end_date DATE;
808 l_latest_yr_start_date DATE;
809 BEGIN
810 IF g_cal_info_exists = 'Y' THEN
811 SELECT
812 MIN(cal_year_id) start_yr_id
813 , MAX(cal_year_id) end_yr_id
814 , MIN(end_date) earliest_yr_end_date
815 , MAX(start_date) latest_yr_start_date
816 INTO
817 l_start_yr_id
818 , l_end_yr_id
819 , l_earliest_yr_end_date
820 , l_latest_yr_start_date
821 FROM pji_time_cal_year
822 WHERE 1=1
823 AND calendar_id = p_calendar_id
824 AND (g_earliest_start_date BETWEEN start_date AND end_date)
825 OR (g_latest_end_date BETWEEN start_date AND end_date);
826
827 IF g_debug_flag = 'Y' THEN
828 pji_utils.write2log('l_start_yr_id: '||l_start_yr_id);
829 pji_utils.write2log('l_end_yr_id: '||l_end_yr_id);
830 pji_utils.write2log('l_earliest_yr_end_date: '||l_earliest_yr_end_date);
831 pji_utils.write2log('l_latest_yr_start_date: '||l_latest_yr_start_date);
832 END IF;
833 END IF;
834
835 DELETE FROM pji_time_cal_year
836 WHERE cal_year_id in (SELECT DISTINCT LPAD(p_calendar_id,3,'0')||period_year from pji_time_extr_tmp);
837
838 l_no_rows_deleted := SQL%rowcount;
839
840 IF g_debug_flag = 'Y' THEN
841 pji_utils.write2log('Some records have to be refreshed in PJI_TIME_CAL_YEAR table.');
842 pji_utils.write2log(TO_CHAR(l_no_rows_deleted)||' records have been deleted from PJI_TIME_CAL_YEAR table.');
843 END IF;
844
845 INSERT INTO PJI_TIME_CAL_YEAR
846 (cal_year_id
847 , calendar_id
848 , SEQUENCE
849 , NAME
850 , start_date
851 , end_date
852 , creation_date
853 , last_update_date
854 , last_updated_by
855 , created_by
856 , last_update_login)
857 SELECT LPAD(p_calendar_id,3,'0')||period_year
858 , LPAD(p_calendar_id,3,'0')
859 , period_year
860 , period_year
861 , DECODE(LPAD(p_calendar_id,3,'0')||period_year,l_end_yr_id,l_latest_yr_start_date,MIN(start_date))
862 , DECODE(LPAD(p_calendar_id,3,'0')||period_year,l_start_yr_id,l_earliest_yr_end_date,MAX(end_date))
863 , SYSDATE
864 , SYSDATE
865 , g_user_id
866 , g_user_id
867 , g_login_id
868 FROM pji_time_extr_tmp
869 GROUP BY period_year
870 HAVING MAX(end_date)<p_earliest_start_date
871 OR MIN(start_date)>p_latest_end_date
872 OR p_cal_info_exists = 'N';
873
874 l_no_rows_inserted := SQL%rowcount;
875
876 IF g_debug_flag = 'Y' THEN
877 pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_YEAR table.');
878 END IF;
879
880 EXCEPTION
881 WHEN DUP_VAL_ON_INDEX THEN
882 IF g_debug_flag = 'Y' THEN
883 pji_utils.write2log('Duplicate records. No records have been inserted into PJI_TIME_CAL_YEAR table.');
884 END IF;
885 END LOAD_CAL_YEAR;
886
887 ---------------------------------------------------
888 -- PRIVATE PROCEDURE load_time_rpt_struct
889 -- This procedure incrementally maintains
890 -- entries in PJI_TIME_RPT_STRUCT table.
891 ---------------------------------------------------
892 PROCEDURE LOAD_TIME_RPT_STRUCT IS
893 l_no_rows_inserted NUMBER;
894 l_year_start_date DATE;
895 l_qtr_start_date DATE;
896 l_period_start_date DATE;
897 l_year_end_date DATE;
898 l_qtr_end_date DATE;
899 l_period_end_date DATE;
900 BEGIN
901 IF g_min_date < g_earliest_start_date THEN
902 IF g_debug_flag = 'Y' THEN
903 pji_utils.write2log('The enterprise calendar has been extended before the earliest start date.');
904 END IF;
905 BEGIN
906 SELECT period.start_date period_start_date
907 , qtr.start_date qtr_start_date
908 , yr.start_date year_start_date
909 , period.end_date period_end_date
910 , qtr.end_date qtr_end_date
911 , yr.end_date year_end_date
912 INTO
913 l_period_start_date
914 , l_qtr_start_date
915 , l_year_start_date
916 , l_qtr_end_date
917 , l_period_end_date
918 , l_year_end_date
919 FROM pji_time_ent_period period
920 , pji_time_ent_qtr qtr
921 , pji_time_ent_year yr
922 WHERE 1=1
923 AND period.ent_qtr_id = qtr.ent_qtr_id
924 AND qtr.ent_year_id = yr.ent_year_id
925 AND period.start_date = g_earliest_start_date;
926
927 IF g_debug_flag = 'Y' THEN
928 pji_utils.write2log('Creating prior year records for time periods after the current year.');
929 END IF;
930
931 INSERT INTO PJI_TIME_RPT_STRUCT
932 (calendar_id
933 , calendar_type
934 , report_date
935 , time_id
936 , period_type_id
937 , record_type_id
938 , creation_date
939 , last_update_date
940 , last_updated_by
941 , created_by
942 , last_update_login)
943 SELECT
944 -1
945 , 'E'
946 , period.start_date
947 , year.ent_year_id
948 , 128
949 , 1024
950 , SYSDATE
951 , SYSDATE
952 , g_user_id
953 , g_user_id
954 , g_login_id
955 FROM PJI_TIME_ENT_YEAR year
956 , PJI_TIME_ENT_PERIOD period
957 WHERE year.end_date < l_year_start_date
958 AND period.start_date >= g_earliest_start_date;
959
960 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
961
962 IF g_debug_flag = 'Y' THEN
963 pji_utils.write2log('Creating prior quarter records for time periods records after current quarter.');
964 END IF;
965
966 INSERT INTO PJI_TIME_RPT_STRUCT
967 (calendar_id
968 , calendar_type
969 , report_date
970 , time_id
971 , period_type_id
972 , record_type_id
973 , creation_date
974 , last_update_date
975 , last_updated_by
976 , created_by
977 , last_update_login)
978 SELECT
979 -1
980 , 'E'
981 , period.start_date
982 , qtr.ent_qtr_id
983 , 64
984 , 64
985 , SYSDATE
986 , SYSDATE
987 , g_user_id
988 , g_user_id
989 , g_login_id
990 FROM PJI_TIME_ENT_QTR qtr
991 , PJI_TIME_ENT_PERIOD period
992 WHERE 1=1
993 AND qtr.end_date < l_qtr_start_date
994 AND qtr.start_date >= l_year_start_date
995 AND period.start_date >= g_earliest_start_date
996 AND period.end_date <= l_year_end_date;
997
998 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
999
1000 IF g_debug_flag = 'Y' THEN
1001 pji_utils.write2log('Creating prior period records for time periods records after current periods.');
1002 END IF;
1003
1004 INSERT INTO PJI_TIME_RPT_STRUCT
1005 (calendar_id
1006 , calendar_type
1007 , report_date
1008 , time_id
1009 , period_type_id
1010 , record_type_id
1011 , creation_date
1012 , last_update_date
1013 , last_updated_by
1014 , created_by
1015 , last_update_login)
1016 SELECT
1017 -1
1018 , 'E'
1019 , oldprd.start_date
1020 , newprd.ent_period_id
1021 , 32
1022 , 32
1023 , SYSDATE
1024 , SYSDATE
1025 , g_user_id
1026 , g_user_id
1027 , g_login_id
1028 FROM PJI_TIME_ENT_PERIOD newprd
1029 , PJI_TIME_ENT_PERIOD oldprd
1030 WHERE 1=1
1031 AND newprd.end_date < l_period_start_date
1032 AND newprd.start_date >= l_qtr_start_date
1033 AND oldprd.start_date >= g_earliest_start_date
1034 AND oldprd.end_date <= l_qtr_end_date;
1035
1036 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1037 EXCEPTION
1038 WHEN NO_DATA_FOUND THEN
1039 IF g_debug_flag = 'Y' THEN
1040 pji_utils.write2log('Unable to derive records for earliest start date.');
1041 END IF;
1042 END;
1043 END IF;
1044
1045 IF g_debug_flag = 'Y' THEN
1046 pji_utils.write2log('Creating entries in the enterprise reporting structure table for extracted records only.');
1047 END IF;
1048
1049 FOR extr_rec IN (SELECT extr.start_date report_date
1050 , period.start_date period_start_date
1051 , qtr.start_date qtr_start_date
1052 , YEAR.start_date year_start_date
1053 , period.end_date period_end_date
1054 , qtr.end_date qtr_end_date
1055 , YEAR.end_date year_end_date
1056 FROM pji_time_extr_tmp extr
1057 , pji_time_ent_period period
1058 , pji_time_ent_qtr qtr
1059 , pji_time_ent_year YEAR
1060 WHERE 1=1
1061 AND TO_NUMBER(extr.period_year||extr.quarter_num||DECODE(LENGTH(extr.period_num),1,'0'||extr.period_num, extr.period_num)) = period.ent_period_id
1062 AND period.ent_qtr_id = qtr.ent_qtr_id
1063 AND qtr.ent_year_id = YEAR.ent_year_id)
1064 LOOP
1065 INSERT INTO PJI_TIME_RPT_STRUCT
1066 (calendar_id
1067 , calendar_type
1068 , report_date
1069 , time_id
1070 , period_type_id
1071 , record_type_id
1072 , creation_date
1073 , last_update_date
1074 , last_updated_by
1075 , created_by
1076 , last_update_login)
1077 SELECT
1078 -1
1079 , 'E'
1080 , extr_rec.report_date
1081 , ent_period_id
1082 , 32
1083 , 32
1084 , SYSDATE
1085 , SYSDATE
1086 , g_user_id
1087 , g_user_id
1088 , g_login_id
1089 FROM PJI_TIME_ENT_PERIOD
1090 WHERE start_date >= extr_rec.qtr_start_date
1091 AND start_date <= extr_rec.period_start_date
1092 AND end_date < extr_rec.report_date
1093 UNION ALL
1094 SELECT
1095 -1
1096 , 'E'
1097 , extr_rec.report_date
1098 , ent_period_id
1099 , 32
1100 , 256
1101 , SYSDATE
1102 , SYSDATE
1103 , g_user_id
1104 , g_user_id
1105 , g_login_id
1106 FROM PJI_TIME_ENT_PERIOD
1107 WHERE start_date >= extr_rec.qtr_start_date
1108 AND start_date <= extr_rec.period_start_date
1109 AND end_date >= extr_rec.report_date;
1110
1111 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1112
1113 INSERT INTO PJI_TIME_RPT_STRUCT
1114 (calendar_id
1115 , calendar_type
1116 , report_date
1117 , time_id
1118 , period_type_id
1119 , record_type_id
1120 , creation_date
1121 , last_update_date
1122 , last_updated_by
1123 , created_by
1124 , last_update_login)
1125 SELECT
1126 -1
1127 , 'E'
1128 , extr_rec.report_date
1129 , ent_qtr_id
1130 , 64
1131 , 64
1132 , SYSDATE
1133 , SYSDATE
1134 , g_user_id
1135 , g_user_id
1136 , g_login_id
1137 FROM PJI_TIME_ENT_QTR
1138 WHERE start_date >= extr_rec.year_start_date
1139 AND start_date <= extr_rec.qtr_start_date
1140 AND end_date < extr_rec.report_date
1141 UNION ALL
1142 SELECT
1143 -1
1144 , 'E'
1145 , extr_rec.report_date
1146 , ent_qtr_id
1147 , 64
1148 , 512
1149 , SYSDATE
1150 , SYSDATE
1151 , g_user_id
1152 , g_user_id
1153 , g_login_id
1154 FROM PJI_TIME_ENT_QTR
1155 WHERE start_date >= extr_rec.year_start_date
1156 AND start_date <= extr_rec.qtr_start_date
1157 AND end_date >= extr_rec.report_date;
1158
1159 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1160
1161 INSERT INTO PJI_TIME_RPT_STRUCT
1162 (calendar_id
1163 , calendar_type
1164 , report_date
1165 , time_id
1166 , period_type_id
1167 , record_type_id
1168 , creation_date
1169 , last_update_date
1170 , last_updated_by
1171 , created_by
1172 , last_update_login)
1173 SELECT
1174 -1
1175 , 'E'
1176 , extr_rec.report_date
1177 , ent_year_id
1178 , 128
1179 , 128
1180 , SYSDATE
1181 , SYSDATE
1182 , g_user_id
1183 , g_user_id
1184 , g_login_id
1185 FROM PJI_TIME_ENT_YEAR
1186 WHERE extr_rec.report_date BETWEEN start_date AND end_date
1187 UNION ALL
1188 SELECT
1189 -1
1190 , 'E'
1191 , extr_rec.report_date
1192 , ent_year_id
1193 , 128
1194 , 1024
1195 , SYSDATE
1196 , SYSDATE
1197 , g_user_id
1198 , g_user_id
1199 , g_login_id
1200 FROM PJI_TIME_ENT_YEAR
1201 WHERE end_date < extr_rec.report_date;
1202
1203 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1204
1205 IF g_debug_flag = 'Y' THEN
1206 pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_RPT_STRUCT table for date : '||Fnd_Date.date_to_displaydate(extr_rec.report_date));
1207 END IF;
1208
1209 END LOOP;
1210 END LOAD_TIME_RPT_STRUCT;
1211
1212 ---------------------------------------------------
1213 -- PRIVATE PROCEDURE load_time_cal_rpt_struct
1214 -- This procedure incrementally maintains
1215 -- entries in PJI_TIME_CAL_RPT_STRUCT table.
1216 ---------------------------------------------------
1217 PROCEDURE LOAD_TIME_CAL_RPT_STRUCT ( p_calendar_id NUMBER
1218 , p_earliest_start_date DATE
1219 , p_min_date DATE
1220 , p_cal_info_exists VARCHAR2
1221 ) IS
1222 l_no_rows_inserted NUMBER;
1223 l_year_start_date DATE;
1224 l_qtr_start_date DATE;
1225 l_period_start_date DATE;
1226 l_year_end_date DATE;
1227 l_qtr_end_date DATE;
1228 l_period_end_date DATE;
1229 BEGIN
1230 IF p_min_date < p_earliest_start_date and p_cal_info_exists <> 'N' THEN
1231 IF g_debug_flag = 'Y' THEN
1232 pji_utils.write2log('The fiscal calendar has been extended before the earliest start date.');
1233 END IF;
1234 BEGIN
1235 SELECT period.start_date period_start_date
1236 , qtr.start_date qtr_start_date
1237 , yr.start_date year_start_date
1238 , period.end_date period_end_date
1239 , qtr.end_date qtr_end_date
1240 , yr.end_date year_end_date
1241 INTO
1242 l_period_start_date
1243 , l_qtr_start_date
1244 , l_year_start_date
1245 , l_qtr_end_date
1246 , l_period_end_date
1247 , l_year_end_date
1248 FROM pji_time_cal_period period
1249 , pji_time_cal_qtr qtr
1250 , pji_time_cal_year yr
1251 WHERE 1=1
1252 AND period.calendar_id = p_calendar_id
1253 AND period.cal_qtr_id = qtr.cal_qtr_id
1254 AND qtr.cal_year_id = yr.cal_year_id
1255 AND period.start_date = p_earliest_start_date;
1256
1257 IF g_debug_flag = 'Y' THEN
1258 pji_utils.write2log('Creating prior year records for time periods after the current year.');
1259 END IF;
1260
1261 INSERT INTO PJI_TIME_CAL_RPT_STRUCT
1262 (calendar_id
1263 , calendar_type
1264 , report_date
1265 , time_id
1266 , period_type_id
1267 , record_type_id
1268 , creation_date
1269 , last_update_date
1270 , last_updated_by
1271 , created_by
1272 , last_update_login)
1273 SELECT
1274 p_calendar_id
1275 , 'G'
1276 , period.start_date
1277 , year.cal_year_id
1278 , 128
1279 , 1024
1280 , SYSDATE
1281 , SYSDATE
1282 , g_user_id
1283 , g_user_id
1284 , g_login_id
1285 FROM PJI_TIME_CAL_YEAR year
1286 , PJI_TIME_CAL_PERIOD period
1287 WHERE year.end_date < l_year_start_date
1288 AND year.calendar_id = p_calendar_id
1289 AND period.calendar_id = p_calendar_id
1290 AND period.start_date > p_earliest_start_date;
1291
1292 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1293
1294 IF g_debug_flag = 'Y' THEN
1295 pji_utils.write2log('Creating prior quarter records for time periods records after current quarter.');
1296 END IF;
1297
1298 INSERT INTO PJI_TIME_CAL_RPT_STRUCT
1299 (calendar_id
1300 , calendar_type
1301 , report_date
1302 , time_id
1303 , period_type_id
1304 , record_type_id
1305 , creation_date
1306 , last_update_date
1307 , last_updated_by
1308 , created_by
1309 , last_update_login)
1310 SELECT
1311 p_calendar_id
1312 , 'G'
1313 , period.start_date
1314 , qtr.cal_qtr_id
1315 , 64
1316 , 64
1317 , SYSDATE
1318 , SYSDATE
1319 , g_user_id
1320 , g_user_id
1321 , g_login_id
1322 FROM PJI_TIME_CAL_QTR qtr
1323 , PJI_TIME_CAL_PERIOD period
1324 WHERE 1=1
1325 AND qtr.end_date < l_qtr_start_date
1326 AND qtr.start_date >= l_year_start_date
1327 AND qtr.calendar_id = p_calendar_id
1328 AND period.calendar_id = p_calendar_id
1329 AND period.start_date > p_earliest_start_date
1330 AND period.end_date <= l_year_end_date;
1331
1332 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1333
1334 IF g_debug_flag = 'Y' THEN
1335 pji_utils.write2log('Creating prior period records for time periods records after current periods.');
1336 END IF;
1337
1338 INSERT INTO PJI_TIME_CAL_RPT_STRUCT
1339 (calendar_id
1340 , calendar_type
1341 , report_date
1342 , time_id
1343 , period_type_id
1344 , record_type_id
1345 , creation_date
1346 , last_update_date
1347 , last_updated_by
1348 , created_by
1349 , last_update_login)
1350 SELECT
1351 p_calendar_id
1352 , 'G'
1353 , oldprd.start_date
1354 , newprd.cal_period_id
1355 , 32
1356 , 32
1357 , SYSDATE
1358 , SYSDATE
1359 , g_user_id
1360 , g_user_id
1361 , g_login_id
1362 FROM PJI_TIME_CAL_PERIOD newprd
1363 , PJI_TIME_CAL_PERIOD oldprd
1364 WHERE 1=1
1365 AND newprd.end_date < l_period_start_date
1366 AND newprd.start_date >= l_qtr_start_date
1367 AND newprd.calendar_id = p_calendar_id
1368 AND oldprd.calendar_id = p_calendar_id
1369 AND oldprd.start_date > p_earliest_start_date
1370 AND oldprd.end_date <= l_qtr_end_date;
1371
1372 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1373 EXCEPTION
1374 WHEN NO_DATA_FOUND THEN
1375 IF g_debug_flag = 'Y' THEN
1376 pji_utils.write2log('Unable to derive records for earliest start date.');
1377 END IF;
1378 END;
1379 END IF;
1380
1381 IF g_debug_flag = 'Y' THEN
1382 pji_utils.write2log('Creating entries in the fiscal reporting structure table for extracted records only.');
1383 END IF;
1384
1385 FOR extr_rec IN (SELECT extr.start_date report_date
1386 , period.start_date period_start_date
1387 , qtr.start_date qtr_start_date
1388 , YEAR.start_date year_start_date
1389 , period.end_date period_end_date
1390 , qtr.end_date qtr_end_date
1391 , YEAR.end_date year_end_date
1392 FROM pji_time_extr_tmp extr
1393 , pji_time_cal_period period
1394 , pji_time_cal_qtr qtr
1395 , pji_time_cal_year YEAR
1396 WHERE 1=1
1397 AND period.calendar_id = p_calendar_id
1398 AND TO_NUMBER(LPAD(p_calendar_id,3,'0')||period_year||quarter_num||DECODE(LENGTH(period_num),1,'0'||period_num, period_num)) = period.cal_period_id
1399 AND period.cal_qtr_id = qtr.cal_qtr_id
1400 AND qtr.cal_year_id = YEAR.cal_year_id)
1401 LOOP
1402 INSERT INTO PJI_TIME_CAL_RPT_STRUCT
1403 (calendar_id
1404 , calendar_type
1405 , report_date
1406 , time_id
1407 , period_type_id
1408 , record_type_id
1409 , creation_date
1410 , last_update_date
1411 , last_updated_by
1412 , created_by
1413 , last_update_login)
1414 SELECT
1415 p_calendar_id
1416 , 'G'
1417 , extr_rec.report_date
1418 , cal_period_id
1419 , 32
1420 , 32
1421 , SYSDATE
1422 , SYSDATE
1423 , g_user_id
1424 , g_user_id
1425 , g_login_id
1426 FROM PJI_TIME_CAL_PERIOD
1427 WHERE start_date >= extr_rec.qtr_start_date
1428 AND start_date <= extr_rec.period_start_date
1429 AND end_date < extr_rec.report_date
1430 AND calendar_id = p_calendar_id
1431 UNION ALL
1432 SELECT
1433 p_calendar_id
1434 , 'G'
1435 , extr_rec.report_date
1436 , cal_period_id
1437 , 32
1438 , 256
1439 , SYSDATE
1440 , SYSDATE
1441 , g_user_id
1442 , g_user_id
1443 , g_login_id
1444 FROM PJI_TIME_CAL_PERIOD
1445 WHERE start_date >= extr_rec.qtr_start_date
1446 AND start_date <= extr_rec.period_start_date
1447 AND end_date >= extr_rec.report_date
1448 AND calendar_id = p_calendar_id;
1449
1450 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1451
1452 INSERT INTO PJI_TIME_CAL_RPT_STRUCT
1453 (calendar_id
1454 , calendar_type
1455 , report_date
1456 , time_id
1457 , period_type_id
1458 , record_type_id
1459 , creation_date
1460 , last_update_date
1461 , last_updated_by
1462 , created_by
1463 , last_update_login)
1464 SELECT
1465 p_calendar_id
1466 , 'G'
1467 , extr_rec.report_date
1468 , cal_qtr_id
1469 , 64
1470 , 64
1471 , SYSDATE
1472 , SYSDATE
1473 , g_user_id
1474 , g_user_id
1475 , g_login_id
1476 FROM PJI_TIME_CAL_QTR
1477 WHERE start_date >= extr_rec.year_start_date
1478 AND start_date <= extr_rec.qtr_start_date
1479 AND end_date < extr_rec.report_date
1480 AND calendar_id = p_calendar_id
1481 UNION ALL
1482 SELECT
1483 p_calendar_id
1484 , 'G'
1485 , extr_rec.report_date
1486 , cal_qtr_id
1487 , 64
1488 , 512
1489 , SYSDATE
1490 , SYSDATE
1491 , g_user_id
1492 , g_user_id
1493 , g_login_id
1494 FROM PJI_TIME_CAL_QTR
1495 WHERE start_date >= extr_rec.year_start_date
1496 AND start_date <= extr_rec.qtr_start_date
1497 AND end_date >= extr_rec.report_date
1498 AND calendar_id = p_calendar_id;
1499
1500 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1501
1502 INSERT INTO PJI_TIME_CAL_RPT_STRUCT
1503 (calendar_id
1504 , calendar_type
1505 , report_date
1506 , time_id
1507 , period_type_id
1508 , record_type_id
1509 , creation_date
1510 , last_update_date
1511 , last_updated_by
1512 , created_by
1513 , last_update_login)
1514 SELECT
1515 p_calendar_id
1516 , 'G'
1517 , extr_rec.report_date
1518 , cal_year_id
1519 , 128
1520 , 128
1521 , SYSDATE
1522 , SYSDATE
1523 , g_user_id
1524 , g_user_id
1525 , g_login_id
1526 FROM PJI_TIME_CAL_YEAR
1527 WHERE extr_rec.report_date BETWEEN start_date AND end_date
1528 AND calendar_id = p_calendar_id
1529 UNION ALL
1530 SELECT
1531 p_calendar_id
1532 , 'G'
1533 , extr_rec.report_date
1534 , cal_year_id
1535 , 128
1536 , 1024
1537 , SYSDATE
1538 , SYSDATE
1539 , g_user_id
1540 , g_user_id
1541 , g_login_id
1542 FROM PJI_TIME_CAL_YEAR
1543 WHERE end_date < extr_rec.report_date
1544 AND calendar_id = p_calendar_id;
1545
1546 l_no_rows_inserted := l_no_rows_inserted + SQL%rowcount;
1547
1548 IF g_debug_flag = 'Y' THEN
1549 pji_utils.write2log(TO_CHAR(l_no_rows_inserted)||' records have been inserted into PJI_TIME_CAL_RPT_STRUCT table for date : '||Fnd_Date.date_to_displaydate(extr_rec.report_date));
1550 END IF;
1551
1552 END LOOP;
1553 END LOAD_TIME_CAL_RPT_STRUCT;
1554
1555 ---------------------------------------------------
1556 -- PUBLIC PROCEDURE load
1557 -- This is a public procedure that extracts
1558 -- GL period definitions into time summary tables.
1559 -- parameters :
1560 -- p_period_set_name - GL periodset name to extract
1561 -- p_period_type - GL periodset name to extract
1562 -- x_return_status - Standard return status:
1563 -- Success = Fnd_Api.G_RET_STS_SUCCESS
1564 -- Error = Fnd_Api.G_RET_STS_ERROR
1565 -- Unexp Error = G_RET_Fnd_Api.STS_UNEXP_ERROR
1566 -- x_msg_count - Standard message param used in FWK
1567 -- x_msg_data - Standard message param used in FWK
1568 ---------------------------------------------------
1569 PROCEDURE LOAD( p_period_set_name VARCHAR2 DEFAULT NULL
1570 , p_period_type VARCHAR2 DEFAULT NULL
1571 , x_return_status OUT NOCOPY VARCHAR2
1572 , x_msg_count OUT NOCOPY NUMBER
1573 , x_msg_data OUT NOCOPY VARCHAR2) IS
1574 PRAGMA AUTONOMOUS_TRANSACTION;
1575 l_calendar_id NUMBER;
1576 l_calendar_ids_tbl SYSTEM.PA_NUM_TBL_TYPE := SYSTEM.PA_NUM_TBL_TYPE();
1577 l_period_set_name_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
1578 l_period_type_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
1579 l_earliest_start_dates_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1580 l_latest_end_dates_tbl SYSTEM.PA_DATE_TBL_TYPE := SYSTEM.PA_DATE_TBL_TYPE();
1581 l_cal_info_exists_tbl SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
1582 l_earliest_start_date DATE;
1583 l_latest_end_date DATE;
1584 l_min_date DATE;
1585 l_process_ent_flag VARCHAR2(1);
1586 BEGIN
1587
1588 IF p_period_set_name IS NULL AND p_period_set_name IS NULL THEN
1589 --Call fii calendar load program.
1590 FII_NON_DBI_TIME_C.LOAD_CAL_NAME;
1591 ELSIF p_period_set_name IS NOT NULL AND p_period_set_name IS NOT NULL THEN
1592 BEGIN
1593 SELECT calendar_id
1594 INTO l_calendar_id
1595 FROM fii_time_cal_name
1596 WHERE period_set_name = p_period_set_name
1597 AND period_type = p_period_type;
1598 EXCEPTION
1599 WHEN NO_DATA_FOUND THEN
1600 -- Call fii calendar incremental load program.
1601 FII_NON_DBI_TIME_C.LOAD_CAL_NAME;
1602 END;
1603 END IF;
1604
1605 SELECT cal.calendar_id
1606 , period_set_name
1607 , period_type
1608 , NVL(info.earliest_start_date,TRUNC(SYSDATE))
1609 , NVL(info.latest_end_date,TRUNC(SYSDATE))
1610 , DECODE(NVL(info.earliest_start_date,TRUNC(SYSDATE)),info.earliest_start_date,'Y','N')
1611 BULK COLLECT INTO
1612 l_calendar_ids_tbl
1613 , l_period_set_name_tbl
1614 , l_period_type_tbl
1615 , l_earliest_start_dates_tbl
1616 , l_latest_end_dates_tbl
1617 , l_cal_info_exists_tbl
1618 FROM fii_time_cal_name cal
1619 , PJI_TIME_CAL_EXTR_INFO info
1620 WHERE 1=1
1621 AND cal.calendar_id = info.calendar_id (+)
1622 AND cal.calendar_id = NVL(l_calendar_id,cal.calendar_id)
1623 AND cal.period_set_name = NVL(p_period_set_name,cal.period_set_name)
1624 AND cal.period_type = NVL(p_period_type,cal.period_type);
1625
1626 --load week
1627 /*
1628 ** Defer the coding of week logic.
1629 */
1630 /*
1631 IF g_cal_info_exists = 'N' THEN
1632 LOAD_WEEK(g_earliest_start_date, g_latest_end_date);
1633 ELSE
1634 LOAD_WEEK(g_earliest_start_date, g_latest_end_date);
1635 LOAD_WEEK(g_earliest_start_date, g_latest_end_date);
1636 END IF;
1637 */
1638
1639 BEGIN
1640 l_process_ent_flag:='Y';
1641 INIT;
1642 EXCEPTION
1643 WHEN G_LOGIN_INFO_NOT_FOUND OR G_BIS_PARAMETER_NOT_SETUP THEN
1644 l_process_ent_flag:='N';
1645 NULL;
1646 END;
1647
1648 IF g_debug_flag = 'Y' THEN
1649 pji_utils.write2log(' ');
1650 pji_utils.write2log('Processing Enterprise calendar.');
1651 START_TIMER;
1652 END IF;
1653
1654 IF l_process_ent_flag='Y' AND LOAD_ENT_PERIOD THEN
1655 IF g_debug_flag = 'Y' THEN
1656 STOP_TIMER;
1657 PRINT_TIMER('Process Time for Enterprise Period API');
1658 START_TIMER;
1659 END IF;
1660
1661 LOAD_ENT_QTR;
1662 IF g_debug_flag = 'Y' THEN
1663 STOP_TIMER;
1664 PRINT_TIMER('Process Time for Enterprise Quarter API');
1665 START_TIMER;
1666 END IF;
1667
1668 LOAD_ENT_YEAR;
1669 IF g_debug_flag = 'Y' THEN
1670 STOP_TIMER;
1671 PRINT_TIMER('Process Time for Enterprise Year API');
1672 START_TIMER;
1673 END IF;
1674
1675 LOAD_TIME_RPT_STRUCT;
1676 IF g_debug_flag = 'Y' THEN
1677 STOP_TIMER;
1678 PRINT_TIMER('Process Time for Enterprise Reporting Structures API');
1679 pji_utils.write2log(' ');
1680 END IF;
1681 ELSE
1682 IF g_debug_flag = 'Y' THEN
1683 STOP_TIMER;
1684 PRINT_TIMER('Process Time for Enterprise Period API');
1685 pji_utils.write2log('No further changes to extract for enterprise calendar.'||
1686 'Skipping Quarter, Year and Reporting Struct APIs.');
1687 pji_utils.write2log(' ');
1688 END IF;
1689 END IF;
1690
1691 IF l_calendar_ids_tbl.COUNT > 0 THEN
1692 FOR i IN l_calendar_ids_tbl.FIRST..l_calendar_ids_tbl.LAST
1693 LOOP
1694 IF g_debug_flag = 'Y' THEN
1695 pji_utils.write2log('Processing Fiscal calendar '||l_period_set_name_tbl(i)||
1696 '('||l_period_type_tbl(i)||').');
1697 START_TIMER;
1698 END IF;
1699 IF LOAD_CAL_PERIOD( l_calendar_ids_tbl(i)
1700 , l_period_set_name_tbl(i)
1701 , l_period_type_tbl(i)
1702 , l_earliest_start_dates_tbl(i)
1703 , l_latest_end_dates_tbl(i)
1704 , l_cal_info_exists_tbl(i)
1705 , l_min_date) THEN
1706 IF g_debug_flag = 'Y' THEN
1707 STOP_TIMER;
1708 PRINT_TIMER('Process Time for Fiscal Period API');
1709 START_TIMER;
1710 END IF;
1711
1712 LOAD_CAL_QUARTER(l_calendar_ids_tbl(i)
1713 , l_earliest_start_dates_tbl(i)
1714 , l_latest_end_dates_tbl(i)
1715 , l_cal_info_exists_tbl(i));
1716 IF g_debug_flag = 'Y' THEN
1717 STOP_TIMER;
1718 PRINT_TIMER('Process Time for Fiscal Quarter API');
1719 START_TIMER;
1720 END IF;
1721
1722 LOAD_CAL_YEAR(l_calendar_ids_tbl(i)
1723 , l_earliest_start_dates_tbl(i)
1724 , l_latest_end_dates_tbl(i)
1725 , l_cal_info_exists_tbl(i));
1726 IF g_debug_flag = 'Y' THEN
1727 STOP_TIMER;
1728 PRINT_TIMER('Process Time for Fiscal Year API');
1729 START_TIMER;
1730 END IF;
1731
1732 LOAD_TIME_CAL_RPT_STRUCT(l_calendar_ids_tbl(i)
1733 , l_earliest_start_dates_tbl(i)
1734 , l_min_date
1735 , l_cal_info_exists_tbl(i));
1736 IF g_debug_flag = 'Y' THEN
1737 STOP_TIMER;
1738 PRINT_TIMER('Process Time for Fiscal Reporting Structures API');
1739 pji_utils.write2log(' ');
1740 END IF;
1741 ELSE
1742 IF g_debug_flag = 'Y' THEN
1743 STOP_TIMER;
1744 PRINT_TIMER('Process Time for Fiscal Period API');
1745 pji_utils.write2log('No further changes to extract for this calendar. '||
1746 'Skipping Quarter, Year and Reporting Struct APIs...');
1747 pji_utils.write2log(' ');
1748 END IF;
1749 END IF;
1750 NULL;
1751 END LOOP;
1752 END IF;
1753 x_return_status:=Fnd_Api.G_RET_STS_SUCCESS;
1754 COMMIT;
1755 EXCEPTION
1756 WHEN OTHERS THEN
1757 pji_utils.write2log(' ');
1758 pji_utils.write2log(' ');
1759 x_return_status:=Fnd_Api.G_RET_STS_SUCCESS;
1760 ROLLBACK;
1761 END LOAD;
1762
1763 BEGIN
1764 NULL;
1765 --INIT;
1766 END PJI_TIME_C;