[Home] [Help]
PACKAGE BODY: APPS.FII_TIME_M_C
Source
1 Package Body FII_TIME_M_C AS
2 /* $Header: FIICMCAB.pls 120.3 2004/11/22 17:44:03 phu ship $ */
3
4 c_from_date Date:=Null;
5 c_to_date Date:=Null;
6 g_rows_inserted Number:=0;
7
8 Procedure Push(Errbuf out NOCOPY Varchar2,
9 Retcode out NOCOPY Varchar2,
10 p_from_date IN Varchar2,
11 p_to_date IN Varchar2) IS
12
13 l_dimension_name Varchar2(30):='EDW_TIME_M';
14 l_temp_date Date:=Null;
15 l_duration Number:=0;
16 l_exception_msg Varchar2(2000):=Null;
17 l_from_date Date:=Null;
18 l_to_date Date:=Null;
19 rows_inserted number:=0;
20
21 Begin
22 Errbuf :=NULL;
23 Retcode:=0;
24 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
25 l_to_date :=to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
26
27 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
28 errbuf := fnd_message.get;
29
30 /* Added by S.Bhattal, 21-NOV-01 */
31 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
32
33 Return;
34 END IF;
35
36 FII_TIME_M_C.g_push_date_range1 := nvl(l_from_date,EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
37
38 FII_TIME_M_C.g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
39
40 edw_log.put_line(' ');
41 edw_log.put_line('Pushing GL calendar and Enterprise calendar');
42
43 edw_log.put_line( 'The collection range is from '||
44 to_char(FII_TIME_M_C.g_push_date_range1,'dd-MON-yyyy')||' to '||
45 to_char(FII_TIME_M_C.g_push_date_range2,'dd-MON-yyyy'));
46
47 -- -----------------------------------------------------------------------------
48 -- Start to push data into staging table
49 -- -----------------------------------------------------------------------------
50
51 /* Push GL calendar and Enterprise calendar*/
52
53 edw_log.put_line(' ');
54 l_temp_date := sysdate;
55
56 Push_gl_and_ent_calendar(FII_TIME_M_C.g_push_date_range1, FII_TIME_M_C.g_push_date_range2);
57
58 l_duration := sysdate - l_temp_date;
59 edw_log.put_line('GL calendar and Enterprise calendar has been pushed successfully!');
60 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
61 edw_log.put_line('-------------------------------------------------------------------------');
62 edw_log.put_line(' ');
63
64 /* Push Gregorian calendar */
65
66 edw_log.put_line('Pushing Gregorian calendar');
67 l_temp_date :=sysdate;
68
69 FII_POPULATE_TIME.Push(errbuf,retcode,rows_inserted,c_from_date,c_to_date);
70
71 l_duration := sysdate - l_temp_date;
72 edw_log.put_line('Gregorian calendar has been pushed successfully!');
73 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
74
75 g_rows_inserted:=g_rows_inserted+rows_inserted;
76
77 EDW_COLLECTION_UTIL.wrapup(TRUE, g_rows_inserted, null,
78 FII_TIME_M_C.g_push_date_range1, FII_TIME_M_C.g_push_date_range2);
79
80 commit;
81
82 Exception When others then
83
84 Errbuf:=sqlerrm;
85 Retcode:=sqlcode;
86 l_exception_msg := Retcode || ':' || Errbuf;
87 FII_TIME_M_C.g_exception_msg := l_exception_msg;
88 rollback;
89
90 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, FII_TIME_M_C.g_exception_msg,
91 FII_TIME_M_C.g_push_date_range1, FII_TIME_M_C.g_push_date_range2);
92
93 END Push;
94
95
96 -- -----------------------------------------------------------------------------
97 -- Procedure to push GL calendar and Enterprise calendar
98 -- -----------------------------------------------------------------------------
99
100 PROCEDURE push_gl_and_ent_calendar(
101 p_from_date IN Date,
102 p_to_date IN Date) is
103
104 -- -----------------------------------------------------------------------------
105 -- Define variable
106 -- -----------------------------------------------------------------------------
107 l_period_set_name Varchar2(15);
108 l_gl_start_date Date;
109 l_gl_end_date Date;
110 l_start_date Date;
111 l_end_date Date;
112 l_instance Varchar2(40);
113 l_gl_date Date;
114 l_period_year number(15); --Bug 4006773: changed from NUMBER(4)
115 l_quarter number(15); --Bug 4006773: changed from NUMBER(4)
116 l_pa_period_name Varchar2(30);
117 l_period_name Varchar2(30);
118 l_period_type Varchar2(30);
119 l_pa_period_fk varchar2(120);
120 l_gl_period_fk varchar2(120);
121 l_master_instance VARCHAR2(30);
122 l_row_cday number:=0;
123 l_row_cal number:=0;
124 l_row_pa number:=0;
125 l_row_qtr number:=0;
126 l_row_year number:=0;
127 l_row_name number:=0;
128 l_row_ep_cal_period NUMBER := 0;
129 l_row_ep_cal_qtr NUMBER := 0;
130 l_row_ep_cal_year NUMBER := 0;
131 l_row_ep_cal_name NUMBER := 0;
132 l_quarter_name varchar2(20);
133 e_start_date date;
134 e_end_date date;
135 l_effective_period_num number;
136
137 -- -----------------------------------------------------------------------------
138 -- Define cursor
139 -- -----------------------------------------------------------------------------
140
141 /* Cursor to fetch GL calendar days */
142
143 Cursor gl_date_cursor is
144 Select per.period_set_name,
145 per.period_type,
146 min(start_date),
147 max(end_date)
148 From gl_periods per,
149 gl_sets_of_books book
150 where per.ADJUSTMENT_PERIOD_FLAG = 'N'
151 and per.period_set_name = book.period_set_name
152 and per.period_type = book.accounted_period_type
153 and per.end_date >= NVL(p_from_date, per.end_date)
154 and per.start_date <= NVL(p_to_date, per.start_date)
155 Group By per.period_set_name,
156 per.period_type;
157
158 /* Cursor to fetch future GL calendar days */
159 Cursor gl_future_date_cursor is
160 Select per.period_set_name,
161 per.period_type,
162 min(start_date),
163 max(end_date)
164 From gl_periods per,
165 gl_sets_of_books book
166 where
167 per.period_set_name = book.period_set_name
168 and per.period_type = book.accounted_period_type
169 Group By per.period_set_name,
170 per.period_type;
171
172 /* Cursor to fetch PA calendar days */
173
174 Cursor pa_date_cursor is
175 select distinct sob.period_set_name,
176 paprd.period_name,
177 imp.pa_period_type,
178 map.accounting_date,
179 paprd.start_date,
180 paprd.end_date,
181 paprd.gl_period_name,
182 (glprd.period_year * 10000) + glprd.period_num
183 from gl_date_period_map map,
184 pa_implementations_all imp,
185 gl_sets_of_books sob,
186 pa_periods_all paprd,
187 gl_periods glprd
188 where imp.set_of_books_id = sob.set_of_books_id
189 and paprd.org_id = imp.org_id
190 and map.period_type = imp.pa_period_type
191 and map.period_name = paprd.period_name
192 and map.period_set_name = sob.period_set_name
193 and map.accounting_date >= NVL(p_from_date, map.accounting_date)
194 and map.accounting_date <= NVL(p_to_date, map.accounting_date)
195 and map.period_name <> 'NOT ASSIGNED'
196 and map.period_set_name = glprd.period_set_name
197 and map.period_name = glprd.period_name;
198
199 /* Cursor to fetch PA periods */
200
201 Cursor pa_period_cursor is
202 select distinct sob.period_set_name,
203 paprd.period_name,
204 paprd.start_date,
205 paprd.end_date,
206 paprd.gl_period_name,
207 imp.pa_period_type,
208 (glprd.period_year * 10000) + glprd.period_num
209 from pa_implementations_all imp,
210 gl_sets_of_books sob,
211 pa_periods_all paprd,
212 gl_periods glprd
213 where imp.set_of_books_id = sob.set_of_books_id
214 and paprd.org_id = imp.org_id
215 and paprd.end_date >= NVL(p_from_date, paprd.end_date)
216 and paprd.start_date <= NVL(p_to_date, paprd.start_date)
217 and paprd.period_name <> 'NOT ASSIGNED'
218 and paprd.period_name = glprd.period_name
219 and sob.period_set_name = glprd.period_set_name;
220
221 /* Cursor to fetch GL periods */
222
223 Cursor cal_period_cursor is
224 select distinct
225 per.period_set_name,
226 per.period_type,
227 per.period_name,
228 per.period_year,
229 per.quarter_num,
230 per.start_date,
231 per.end_date,
232 (per.period_year * 10000) + per.period_num
233 from gl_periods per,
234 gl_sets_of_books book
235 where per.adjustment_period_flag = 'N'
236 and per.period_set_name = book.period_set_name
237 and per.period_type = book.accounted_period_type;
238
239 /* Cursor to fetch quarters */
240
241 Cursor gl_quarters is
242 Select per.period_set_name,
243 per.period_type,
244 per.period_year,
245 per.quarter_num,
246 min(per.start_date),
247 max(per.end_date)
248 from gl_periods per,
249 gl_sets_of_books book
250 where per.period_set_name = book.period_set_name
251 and per.period_type = book.accounted_period_type
252 group by per.period_set_name,
253 per.period_type,
254 per.period_year,
255 per.quarter_num;
256
257 /* Cursor to fetch years */
258
259 Cursor gl_years is
260 Select per.period_set_name,
261 period_type,
262 period_year,
263 min(start_date),
264 max(end_date)
265 from gl_periods per,
266 gl_sets_of_books book
267 where per.period_set_name = book.period_set_name
268 and per.period_type = book.accounted_period_type
269 group by per.period_set_name,
270 per.period_type,
271 per.period_year;
272
273 BEGIN
274
275 c_from_date:=p_from_date;
276 c_to_date:=p_to_date;
277
278 e_start_date :=p_from_date;
279 e_end_date :=p_to_date;
280
281 l_instance := edw_instance.get_code;
282
283 select instance_code
284 into l_master_instance
285 from edw_local_system_parameters;
286
287 -- -----------------------------------------------------------------------------
288 -- Clear the staging table before pushing data
289 -- -----------------------------------------------------------------------------
290 edw_log.put_line('Purging the existing staging tables');
291
292 delete edw_time_cal_period_lstg
293 where instance = l_instance;
294
295 delete edw_time_pa_period_lstg
296 where instance = l_instance;
297
298 delete edw_time_cal_qtr_lstg
299 where instance = l_instance;
300
301 delete edw_time_cal_year_lstg
302 where instance = l_instance;
303
304 delete edw_time_cal_name_lstg
305 where instance = l_instance;
306
307 commit;
308
309 delete edw_time_cal_day_lstg
310 where instance = l_instance;
311
312 commit;
313
314 -- ------------------------------------------------------
315 -- Only purge Enterprise Calendar only from master source
316 -- ------------------------------------------------------
317
318 IF (l_instance = l_master_instance) then
319
320 delete edw_time_ep_cal_period_lstg
321 where instance = l_instance;
322
323 delete edw_time_ep_cal_qtr_lstg
324 where instance = l_instance;
325
326 delete edw_time_ep_cal_year_lstg
327 where instance = l_instance;
328
329 delete edw_time_ep_cal_name_lstg
330 where instance = l_instance;
331
332 commit;
333 END IF;
334
335 edw_log.put_line('Purge completed');
336 edw_log.put_line(' ');
337
338 -- -----------------------------------------------------------------------------
339 -- Pushing GL calendar
340 -- Note: 'oracle_source' is populated in enterprise foreign key in order for
341 -- pre collection hook to differentiate between oracle source and other source
342 -- -----------------------------------------------------------------------------
343 edw_log.put_line('Pushing GL calendar');
344 edw_log.put_line('===================');
345
346 -- --------------------------------------------------------------
347 -- Populating different parts into calendar day level
348 -- --------------------------------------------------------------
349
350 /* Population of GL Calendar Day */
351
352 edw_log.put_line('Pushing Calendar Day');
353 fii_util.start_timer;
354
355 OPEN gl_date_cursor;
356
357 LOOP
358
359 FETCH gl_date_cursor
360 INTO l_period_set_name,
361 l_period_type,
362 l_gl_start_date,
363 l_gl_end_date;
364
365 EXIT WHEN gl_date_cursor%NOTFOUND;
366
367 l_gl_date := l_gl_start_date;
368 l_start_date := to_date('01/02/1950', 'DD/MM/YYYY');
369 l_end_date := to_date('01/01/1950', 'DD/MM/YYYY');
370
371
372 WHILE (l_gl_date <= l_gl_end_date)
373 LOOP
374
375 if (l_start_date <= l_gl_date and l_end_date >= l_gl_date) then
376 null;
377 else
378
379 /* Get period details */
380
381 begin
382 Select period_name,
383 start_date,
384 end_date,
385 (period_year * 10000) + period_num
386 into l_period_name,
387 l_start_date,
388 l_end_date,
389 l_effective_period_num
390 from gl_periods
391 where period_set_name = l_period_set_name
392 and period_type = l_period_type
393 and start_date <= l_gl_date
394 and end_date >= l_gl_date
395 and adjustment_period_flag = 'N';
396
397 exception when no_data_found then
398 /* This can happen if there are gaps/holes in gl_periods */
399 l_period_name := 'NA_EDW';
400 end;
401
402 end if;
403
404 if l_period_name = 'NA_EDW'
405 then
406 l_gl_period_fk := 'NA_EDW';
407 l_pa_period_fk := 'NA_EDW';
408 else
409 l_gl_period_fk := l_period_set_name||'-'||l_period_name ||'-' ||l_instance;
410 l_pa_period_fk := l_period_set_name||'-'||l_period_name ||'-' ||l_instance ||'-GL';
411 end if;
412
413 Insert into EDW_TIME_CAL_DAY_LSTG
414 (
415 CAL_DAY_PK,
416 FA_PERIOD_FK,
417 PA_PERIOD_FK,
418 DAY_FK,
419 CAL_PERIOD_FK,
420 HOLIDAY_FLAG,
421 INSTANCE,
422 NAME,
423 WORK_DAY_FLAG,
424 CALENDAR_DATE,
425 END_DATE,
426 TIMESPAN,
427 SEQ_NUMBER,
428 COLLECTION_STATUS,
429 EP_CAL_PERIOD_FK,
430 EFFECTIVE_PERIOD_NUM,
431 PERIOD_SET_NAME,
432 PERIOD_TYPE
433 )
434 values(
435 to_char(l_gl_date,'dd-mm-yyyy') ||'-'||
436 l_period_set_name ||'-'||
437 l_period_type ||'-'||
438 l_instance|| '-CD', --cal_day_pk
442 l_gl_period_fk, --cal_period_fk
439 'NA_EDW', --fa_period_fk
440 l_pa_period_fk, --pa_period_fk
441 to_char(l_gl_date,'dd-mm-yyyy'), --day_fk
443 null, --holiday_flag
444 l_instance, --instance
445 to_char(l_gl_date,'fmdd Month yyyy')||' ('||l_period_set_name||')', --name
446 null,
447 l_gl_date,
448 l_gl_date,
449 1,
450 null,
451 'READY',
452 'oracle_source',
453 l_effective_period_num,
454 l_period_set_name,
455 l_period_type
456 );
457
458 l_gl_date := l_gl_date + 1;
459 l_row_cday := l_row_cday + 1;
460
461 END LOOP;
462
463 if l_gl_start_date < c_from_date then
464 c_from_date:=l_gl_start_date;
465 end if;
466
467 if l_gl_end_date > c_to_date then
468 c_to_date:=l_gl_end_date;
469 end if;
470
471 commit;
472
473 END LOOP;
474
475 fii_util.stop_timer;
476 fii_util.print_timer('Process Time');
477 edw_log.put_line(' ');
478
479 CLOSE gl_date_cursor;
480
481
482
483 edw_log.put_line('Pushing Future Calendar Days');
484
485 fii_util.start_timer;
486
487
488 OPEN gl_future_date_cursor;
489
490 LOOP
491
492 FETCH gl_future_date_cursor
493 INTO l_period_set_name,
494 l_period_type,
495 l_gl_start_date,
496 l_gl_end_date;
497
498 EXIT WHEN gl_future_date_cursor%NOTFOUND;
499
500 e_start_date :=p_from_date;
501 e_end_date :=p_to_date;
502
503
504 IF e_start_date > e_end_date THEN
505 NULL;
506 ELSIF e_end_date <= l_gl_end_date THEN
507 NULL;
508 ELSIF e_end_date > l_gl_end_date THEN
509 IF e_start_date < l_gl_end_date THEN
510 e_start_date := l_gl_end_date + 1;
511 END IF;
512
513
514 WHILE (e_start_date <= e_end_date)
515 LOOP
516 Insert into EDW_TIME_CAL_DAY_LSTG
517 (
518 CAL_DAY_PK,
519 FA_PERIOD_FK,
520 PA_PERIOD_FK,
521 DAY_FK,
522 CAL_PERIOD_FK,
523 HOLIDAY_FLAG,
524 INSTANCE,
525 NAME,
526 WORK_DAY_FLAG,
527 CALENDAR_DATE,
528 END_DATE,
529 TIMESPAN,
530 SEQ_NUMBER,
531 COLLECTION_STATUS,
532 EP_CAL_PERIOD_FK,
533 EFFECTIVE_PERIOD_NUM,
534 PERIOD_SET_NAME,
535 PERIOD_TYPE
536 )
537 VALUES(
538 to_char(e_start_date,'dd-mm-yyyy') ||'-'||
539 l_period_set_name ||'-'||
540 l_period_type ||'-'||
541 l_instance|| '-CD', --cal_day_pk
542 'NA_EDW', --fa_period_fk
543 'NA_EDW', --pa_period_fk
544 to_char(e_start_date,'dd-mm-yyyy'), --day_fk
545 'NA_EDW', --cal_period_fk
546 null, --holiday_flag
547 l_instance, --instance
548 to_char(e_start_date,'fmdd Month yyyy')||' ('||l_period_set_name||')', --name
549 null,
550 e_start_date,
551 e_start_date,
552 1,
553 null,
554 'READY',
555 'oracle_source',
556 -1,
557 l_period_set_name,
558 l_period_type
559 );
560
561 e_start_date := e_start_date + 1;
562 l_row_cday := l_row_cday + 1;
563
564 END LOOP;
565
566
567 commit;
568
569 END IF;
570
571
572 END LOOP;
573
574
575 fii_util.stop_timer;
576 fii_util.print_timer('Process Time');
577 edw_log.put_line(' ');
578
579 CLOSE gl_future_date_cursor;
580
581
582
583 /* Population of PA Calendar Day */
584
585 edw_log.put_line('Pushing PA Calendar Day');
586 fii_util.start_timer;
587
588 OPEN pa_date_cursor;
589
590 LOOP
591
592 FETCH pa_date_cursor
593 INTO l_period_set_name,
594 l_pa_period_name,
595 l_period_type,
596 l_gl_date,
597 l_gl_start_date,
598 l_gl_end_date,
599 l_period_name,
600 l_effective_period_num;
601
602 EXIT WHEN pa_date_cursor%NOTFOUND;
603
604 Insert into EDW_TIME_CAL_DAY_LSTG
605 (CAL_DAY_PK,
606 FA_PERIOD_FK,
607 PA_PERIOD_FK,
611 INSTANCE,
608 DAY_FK,
609 CAL_PERIOD_FK,
610 HOLIDAY_FLAG,
612 NAME,
613 WORK_DAY_FLAG,
614 CALENDAR_DATE,
615 END_DATE,
616 TIMESPAN,
617 SEQ_NUMBER,
618 COLLECTION_STATUS,
619 EP_CAL_PERIOD_FK,
620 EFFECTIVE_PERIOD_NUM,
621 PERIOD_SET_NAME,
622 PERIOD_TYPE
623 )
624 values(to_char(l_gl_date,'dd-mm-yyyy')||'-'||
625 l_period_set_name||'-'||
626 l_period_type||'-'||
627 l_instance||'-PD', --cal_day_pk
628 'NA_EDW', --fa_period_fk
629 l_period_set_name||'-'||
630 l_pa_period_name||'-'||
631 l_instance||'-PA', --pa_period_fk
632 'NA_EDW', --day_fk
633 l_period_set_name||'-'||
634 l_period_name||'-'||l_instance, --cal_period_fk
635 null, --holiday_flag
636 l_instance, --instance
637 to_char(l_gl_date, 'fmdd Month yyyy')||
638 ' ('||l_period_set_name||')', --name
639 null, --work_day_flag
640 l_gl_date, --calendar_date
641 l_gl_date, --end_date
642 1, --timespan
643 null, --seq_number
644 'READY', --collection_status
645 'oracle_source', --ep_cal_period_fk
646 l_effective_period_num,
647 l_period_set_name,
648 l_period_type
649 );
650
651 l_row_cday:=l_row_cday+1;
652
653 if l_gl_start_date < c_from_date then
654 c_from_date:=l_gl_start_date;
655 end if;
656
657 if l_gl_end_date > c_to_date then
658 c_to_date:=l_gl_end_date;
659 end if;
660
661 END LOOP;
662
663 commit;
664 fii_util.stop_timer;
665 fii_util.print_timer('Process Time');
666 edw_log.put_line(' ');
667
668 CLOSE pa_date_cursor;
669
670
671 /* Population of PA Period push down */
672
673 edw_log.put_line('Pushing PA Period push down');
674 fii_util.start_timer;
675
676 OPEN pa_period_cursor;
677
678 LOOP
679
680 FETCH pa_period_cursor
681 INTO l_period_set_name,
682 l_pa_period_name,
683 l_gl_start_date,
684 l_gl_end_date,
685 l_period_name,
686 l_period_type,
687 l_effective_period_num;
688
689 EXIT WHEN pa_period_cursor%NOTFOUND;
690
691 Insert into EDW_TIME_CAL_DAY_LSTG
692 (CAL_DAY_PK,
693 FA_PERIOD_FK,
694 PA_PERIOD_FK,
695 DAY_FK,
696 CAL_PERIOD_FK,
697 EP_CAL_PERIOD_FK,
698 HOLIDAY_FLAG,
699 INSTANCE,
700 NAME,
701 WORK_DAY_FLAG,
702 CALENDAR_DATE,
703 END_DATE,
704 TIMESPAN,
705 SEQ_NUMBER,
706 COLLECTION_STATUS,
707 EFFECTIVE_PERIOD_NUM,
708 PERIOD_SET_NAME,
709 PERIOD_TYPE
710 )
711 values(l_period_set_name||'-'||
712 l_pa_period_name||'-'||
713 l_instance||'-PPER', --cal_day_pk
714 'NA_EDW',
715 l_period_set_name||'-'||
716 l_pa_period_name||'-'||
717 l_instance||'-PA', --pa_period_fk
718 to_char(l_gl_start_date,'dd-mm-yyyy'), --day_fk
719 l_period_set_name||'-'||
720 l_period_name||'-'||l_instance, --cal_period_fk
721 'oracle_source', --ep_cal_period_fk
722 '',
723 l_instance,
724 l_pa_period_name||' ('||l_period_set_name||')', --name
725 '',
726 l_gl_start_date,
727 l_gl_end_date,
728 l_gl_end_date - l_gl_start_date + 1,
729 '',
730 'READY',
731 l_effective_period_num,
732 l_period_set_name,
733 l_period_type
734 );
735
736 l_row_cday:=l_row_cday+1;
737
738 END LOOP;
739
740 commit;
741 fii_util.stop_timer;
742 fii_util.print_timer('Process Time');
743 edw_log.put_line(' ');
744
745 CLOSE pa_period_cursor;
746
747
751 fii_util.start_timer;
748 /* Population of GL Period push down*/
749
750 edw_log.put_line('Pushing GL Period push down to Calendar Day');
752
753 OPEN cal_period_cursor;
754
755 LOOP
756
757 FETCH cal_period_cursor
758 INTO l_period_set_name,
759 l_period_type,
760 l_period_name,
761 l_period_year,
762 l_quarter,
763 l_start_date,
764 l_end_date,
765 l_effective_period_num;
766
767 EXIT WHEN cal_period_cursor%NOTFOUND;
768
769 Insert into EDW_TIME_CAL_DAY_LSTG
770 (CAL_DAY_PK,
771 FA_PERIOD_FK,
772 PA_PERIOD_FK,
773 DAY_FK,
774 CAL_PERIOD_FK,
775 EP_CAL_PERIOD_FK,
776 HOLIDAY_FLAG,
777 INSTANCE,
778 NAME,
779 WORK_DAY_FLAG ,
780 CALENDAR_DATE,
781 END_DATE,
782 TIMESPAN,
783 SEQ_NUMBER,
784 COLLECTION_STATUS,
785 EFFECTIVE_PERIOD_NUM,
786 PERIOD_SET_NAME,
787 PERIOD_TYPE
788 )
789 Values (
790 l_period_set_name||'-'||l_period_name||'-'||l_instance||'-CPER', -- CAL_DAY_PK
791 'NA_EDW', -- FA_PERIOD_FK
792 l_period_set_name||'-'||l_period_name||'-'||l_instance||'-GL', -- PA_PERIOD_FK
793 'NA_EDW', -- DAY_FK
794 l_period_set_name||'-'||l_period_name||'-'||l_instance, -- CAL_PERIOD_FK
795 'oracle_source', -- EP_CAL_PERIOD_FK
796 '',
797 l_instance,
798 l_period_name||' ('||l_period_set_name||')', --name
799 '', --work_day_flag
800 l_start_date, --calendar_date
801 l_end_date, --end_date
802 l_end_date - l_start_date + 1, --timespan
803 1, --seq_number
804 'READY', --collection_status
805 l_effective_period_num,
806 l_period_set_name,
807 l_period_type
808 );
809
810 l_row_cday:=l_row_cday+1;
811
812 END LOOP;
813
814 commit;
815 fii_util.stop_timer;
816 fii_util.print_timer('Process Time');
817 edw_log.put_line(' ');
818
819 CLOSE cal_period_cursor;
820
821
822 -- --------------------------------------------------
823 -- Populating different parts into PA Period Level
824 -- --------------------------------------------------
825
826 /* Population of PA Period */
827
828 edw_log.put_line('Pushing PA Period');
829 fii_util.start_timer;
830
831 OPEN pa_period_cursor;
832
833 LOOP
834 FETCH pa_period_cursor
835 INTO l_period_set_name,
836 l_pa_period_name,
837 l_gl_start_date,
838 l_gl_end_date,
839 l_period_name,
840 l_period_type,
841 l_effective_period_num;
842
843 EXIT WHEN pa_period_cursor%NOTFOUND;
844
845 Insert into EDW_TIME_PA_PERIOD_LSTG
846 (PA_PERIOD_PK,
847 CAL_PERIOD_FK,
848 INSTANCE,
849 PA_PERIOD,
850 NAME,
851 END_DATE,
852 START_DATE,
853 TIMESPAN,
854 COLLECTION_STATUS )
855 values(l_period_set_name||'-'||
856 l_pa_period_name||'-'||l_instance||'-PA', --pa_period_pk
857 l_period_set_name||'-'||
858 l_period_name||'-'||l_instance, --cal_period_fk
859 l_instance, --instance
860 l_pa_period_name, --pa_period
861 l_pa_period_name||' ('||l_period_set_name||')', --name
862 l_gl_end_date, --end_date
863 l_gl_start_date, --start_date
864 l_gl_end_date - l_gl_start_date + 1, --timespan
865 'READY'); --collection_status
866
867 l_row_pa:=l_row_pa+1;
868
869 END LOOP;
870
871 commit;
872 fii_util.stop_timer;
873 fii_util.print_timer('Process Time');
874 edw_log.put_line(' ');
875
876 CLOSE pa_period_cursor;
877
878
879 /* Population of GL Period pushed down to PA period level*/
880
881 edw_log.put_line('Pushing GL Period push down to PA Period');
882 fii_util.start_timer;
883
884 OPEN cal_period_cursor;
885
886 LOOP
887 FETCH cal_period_cursor
888 INTO l_period_set_name,
889 l_period_type,
893 l_start_date,
890 l_period_name,
891 l_period_year,
892 l_quarter,
894 l_end_date,
895 l_effective_period_num;
896
897 EXIT WHEN cal_period_cursor%NOTFOUND;
898
899 Insert into EDW_TIME_PA_PERIOD_LSTG
900 (PA_PERIOD_PK,
901 CAL_PERIOD_FK,
902 INSTANCE,
903 PA_PERIOD,
904 NAME,
905 END_DATE,
906 START_DATE,
907 TIMESPAN,
908 COLLECTION_STATUS)
909 Values (
910 l_period_set_name||'-'||l_period_name||'-'||
911 l_instance||'-GL', --pa_period_pk
912 l_period_set_name||'-'||l_period_name||'-'||
913 l_instance, --cal_period_fk
914 l_instance, --instance
915 l_period_name, --pa_period
916 l_period_name||' ('||l_period_set_name||')', --name
917 l_end_date,
918 l_start_date,
919 l_end_date - l_start_date + 1,
920 'READY');
921
922 l_row_pa:=l_row_pa+1;
923
924 END LOOP;
925
926 commit;
927 fii_util.stop_timer;
928 fii_util.print_timer('Process Time');
929 edw_log.put_line(' ');
930
931 CLOSE cal_period_cursor;
932
933
934 -- --------------------------------------------------
935 -- Populating GL Period Level
936 -- --------------------------------------------------
937
938 edw_log.put_line('Pushing GL Period');
939 fii_util.start_timer;
940
941 OPEN cal_period_cursor;
942
943 LOOP
944 FETCH cal_period_cursor
945 INTO l_period_set_name,
946 l_period_type,
947 l_period_name,
948 l_period_year,
949 l_quarter,
950 l_gl_start_date,
951 l_gl_end_date,
952 l_effective_period_num;
953
954 EXIT WHEN cal_period_cursor%NOTFOUND;
955
956 insert into edw_time_cal_period_lstg
957 (CAL_PERIOD_PK,
958 CAL_QTR_FK,
959 INSTANCE,
960 NAME,
961 CAL_PERIOD,
962 PERIOD_NAME,
963 END_DATE,
964 START_DATE,
965 TIMESPAN,
966 COLLECTION_STATUS)
967 values(l_period_set_name||'-'||l_period_name||'-'||l_instance, --cal_period_pk
968 l_period_set_name||'-'||l_period_type||'-'||l_period_year||
969 '-Q-'||l_quarter||'-'||l_instance, --cal_qtr_fk
970 l_instance,
971 l_period_name||' ('||l_period_set_name ||')', --name
972 l_period_name,
973 l_period_name,
974 l_gl_end_date,
975 l_gl_start_date,
976 l_gl_end_date - l_gl_start_date + 1,
977 'READY');
978
979 l_row_cal:=l_row_cal+1;
980
981 END LOOP;
982
983 commit;
984 fii_util.stop_timer;
985 fii_util.print_timer('Process Time');
986 edw_log.put_line(' ');
987
988 CLOSE cal_period_cursor;
989
990 -- --------------------------------------------------
991 -- Populating GL Quarter Level
992 -- --------------------------------------------------
993
994 edw_log.put_line('Pushing GL Quarter');
995 fii_util.start_timer;
996 l_quarter_name:=fnd_message.get_string('FII', 'FII_AR_QUARTER');
997
998 OPEN gl_quarters;
999
1000 LOOP
1001 FETCH gl_quarters
1002 INTO l_period_set_name,
1003 l_period_type,
1004 l_period_year,
1005 l_quarter,
1006 l_start_date,
1007 l_end_date;
1008
1009 EXIT WHEN gl_quarters%NOTFOUND;
1010
1011 insert into EDW_TIME_CAL_QTR_LSTG
1012 (CAL_QTR_PK ,
1013 CAL_YEAR_FK ,
1014 CAL_QTR,
1015 INSTANCE,
1016 NAME ,
1017 END_DATE,
1018 START_DATE,
1019 TIMESPAN,
1020 COLLECTION_STATUS )
1021 values
1022 (l_period_set_name||'-'||l_period_type||'-'||
1023 l_period_year||'-Q-'||l_quarter||'-'||l_instance, --CAL_QTR_PK
1024 l_period_set_name||'-'||l_period_type||'-'||
1025 l_period_year||'-'||l_instance, --CAL_YEAR_FK
1026 l_quarter_name||' '||l_quarter||', '||l_period_year||
1027 ' ('||l_period_set_name||')', --CAL_QTR
1028 l_instance,
1029 l_quarter_name||' '||l_quarter||', '||l_period_year||
1030 ' ('||l_period_set_name||')', --name
1031 l_end_date,
1032 l_start_date,
1033 l_end_date - l_start_date + 1,
1034 'READY');
1035
1036 l_row_qtr := l_row_qtr + 1;
1037
1038 END LOOP;
1039
1040 commit;
1041 fii_util.stop_timer;
1042 fii_util.print_timer('Process Time');
1046
1043 edw_log.put_line(' ');
1044
1045 CLOSE gl_quarters;
1047
1048 -- --------------------------------------------------
1049 -- Populating GL Year Level
1050 -- --------------------------------------------------
1051
1052 edw_log.put_line('Pushing GL Year');
1053 fii_util.start_timer;
1054
1055 OPEN gl_years;
1056
1057 LOOP
1058 FETCH gl_years
1059 INTO l_period_set_name,
1060 l_period_type,
1061 l_period_year,
1062 l_start_date,
1063 l_end_date;
1064
1065 EXIT WHEN gl_years%NOTFOUND;
1066
1067 insert into EDW_TIME_CAL_YEAR_LSTG
1068 (
1069 CAL_YEAR_PK ,
1070 CAL_NAME_FK ,
1071 CAL_YEAR,
1072 INSTANCE,
1073 NAME ,
1074 END_DATE,
1075 START_DATE,
1076 TIMESPAN,
1077 COLLECTION_STATUS
1078 )
1079 values(
1080 l_period_set_name||'-'||l_period_type||'-'||
1081 l_period_year||'-'||l_instance, -- cal_year_pk
1082 l_period_set_name||'-'||l_period_type||'-'||l_instance, -- cal_name_fk
1083 l_period_year, -- cal_year
1084 l_instance,
1085 l_period_year||' ('||l_period_set_name||')', --name
1086 l_end_date,
1087 l_start_date,
1088 l_end_date - l_start_date + 1,
1089 'READY' );
1090
1091 l_row_year := l_row_year + 1;
1092 END LOOP;
1093
1094 commit;
1095 fii_util.stop_timer;
1096 fii_util.print_timer('Process Time');
1097 edw_log.put_line(' ');
1098
1099 CLOSE gl_years;
1100
1101
1102 -- --------------------------------------------------
1103 -- Populating GL Calendar Name Level
1104 -- --------------------------------------------------
1105
1106 edw_log.put_line('Pushing GL Calendar Name');
1107 fii_util.start_timer;
1108
1109 insert into EDW_TIME_CAL_NAME_LSTG(
1110 CAL_NAME_PK ,
1111 ALL_FK,
1112 CAL_NAME,
1113 CALENDAR_TYPE ,
1114 DESCRIPTION ,
1115 INSTANCE ,
1116 NAME ,
1117 end_date ,
1118 timespan,
1119 COLLECTION_STATUS )
1120 Select distinct sob.period_set_name ||'-'||
1121 sob.accounted_period_type||'-'||l_instance, --cal_name_pk
1122 'ALL', --all_fk
1123 sob.period_set_name, --cal_name
1124 'Financial', --calendar_type
1125 sets.description, --description
1126 l_instance, --instance
1127 sob.period_set_name, --name
1128 sysdate, --end_date
1129 1, --timespan
1130 'READY' --collection_status
1131 from gl_sets_of_books sob,
1132 gl_period_sets sets
1133 where sob.period_set_name = sets.period_set_name;
1134
1135 l_row_name:=l_row_name+sql%rowcount;
1136 commit;
1137 fii_util.stop_timer;
1138 fii_util.print_timer('Process Time');
1139 edw_log.put_line(' ');
1140
1141 -- -----------------------------------------------------------------------------
1142 -- Write result to log file
1143 -- -----------------------------------------------------------------------------
1144
1145 /* for Status Viewer to verify the pushed record number */
1146 g_rows_inserted:=l_row_cday;
1147
1148 edw_log.put_line(' ');
1149 edw_log.put_line(to_char(l_row_cday)||' records has been pushed to Calendar Day');
1150 edw_log.put_line(to_char(l_row_pa)||' records has been pushed to PA Period');
1151 edw_log.put_line(to_char(l_row_cal)||' records has been pushed to GL Period');
1152 edw_log.put_line(to_char(l_row_qtr)||' records has been pushed to GL Quarter');
1153 edw_log.put_line(to_char(l_row_year)||' records has been pushed to GL Year');
1154 edw_log.put_line(to_char(l_row_name)||' records has been pushed to Calendar Name');
1155 edw_log.put_line(' ');
1156
1157 -- -----------------------------------------------------------------------------
1158 -- Pushing Enterprise calendar
1159 -- -----------------------------------------------------------------------------
1160 select period_set_name, period_type, instance_code
1161 into l_period_set_name, l_period_type, l_master_instance
1162 from edw_local_system_parameters;
1163
1164 /* Enterprise hierarchy will only be populated if this is the master instance*/
1165 if l_master_instance=l_instance then
1166 edw_log.put_line('Pushing Enterprise calendar');
1167 edw_log.put_line('===========================');
1168
1169 /* Population of Enterprise Calendar Period */
1170 edw_log.put_line('Pushing Enterprise Calendar Period');
1171 fii_util.start_timer;
1172
1176 INSTANCE,
1173 INSERT INTO EDW_TIME_EP_CAL_PERIOD_LSTG
1174 (CAL_PERIOD_PK,
1175 CAL_QTR_FK,
1177 NAME,
1178 CAL_PERIOD,
1179 PERIOD_NAME,
1180 END_DATE,
1181 START_DATE,
1182 TIMESPAN,
1183 COLLECTION_STATUS)
1184 select period_name,
1185 to_char(quarter_num)||'-'||to_char(period_year),
1186 l_instance,
1187 period_name,
1188 period_name,
1189 period_name,
1190 end_date,
1191 start_date,
1192 end_date-start_date+1,
1193 'READY'
1194 FROM gl_periods
1195 where period_set_name = l_period_set_name
1196 and period_type = l_period_type
1197 and adjustment_period_flag='N';
1198
1199 l_row_ep_cal_period:=l_row_ep_cal_period+sql%rowcount;
1200 fii_util.stop_timer;
1201 fii_util.print_timer('Process Time');
1202 edw_log.put_line(' ');
1203
1204 /* Population of Enterprise Calendar Quarter */
1205
1206 edw_log.put_line('Pushing Enterprise Calendar Quarter');
1207 fii_util.start_timer;
1208 l_quarter_name:=fnd_message.get_string('FII', 'FII_AR_QUARTER');
1209
1210 INSERT INTO EDW_TIME_EP_CAL_QTR_LSTG
1211 (CAL_QTR_PK,
1212 CAL_YEAR_FK,
1213 CAL_QTR,
1214 INSTANCE,
1215 NAME,
1216 END_DATE,
1217 START_DATE,
1218 TIMESPAN,
1219 COLLECTION_STATUS)
1220 Select to_char(quarter_num)||'-'||to_char(period_year), --CAL_QTR_PK
1221 to_char(period_year), --CAL_YEAR_FK
1222 l_quarter_name||' '||to_char(quarter_num)||', '||to_char(period_year), --CAL_QTR
1223 l_instance, --INSTANCE
1224 l_quarter_name||' '||to_char(quarter_num)||', '||to_char(period_year), --NAME
1225 max(end_date), --END_DATE
1226 min(start_date), --START_DATE
1227 max(end_date)-min(start_date)+1, --TIMESPAN
1228 'READY' --COLLECTION_STATUS
1229 FROM gl_periods
1230 where period_set_name=l_period_set_name
1231 and period_type = l_period_type
1232 and adjustment_period_flag='N'
1233 group by period_year, quarter_num;
1234
1235 l_row_ep_cal_qtr:=l_row_ep_cal_qtr+sql%rowcount;
1236 fii_util.stop_timer;
1237 fii_util.print_timer('Process Time');
1238 edw_log.put_line(' ');
1239
1240 /* Population of Enterprise Calendar Year */
1241
1242 edw_log.put_line('Pushing Enterprise Calendar Year');
1243 fii_util.start_timer;
1244
1245 INSERT INTO EDW_TIME_EP_CAL_YEAR_LSTG
1246 (CAL_YEAR_PK,
1247 CAL_NAME_FK,
1248 CAL_YEAR,
1249 INSTANCE,
1250 NAME,
1251 END_DATE,
1252 START_DATE,
1253 TIMESPAN,
1254 COLLECTION_STATUS)
1255 Select to_char(period_year), --CAL_YEAR_PK
1256 l_period_set_name, --CAL_NAME_FK
1257 to_char(period_year), --CAL_YEAR
1258 l_instance, --INSTANCE
1259 to_char(period_year), --NAME
1260 max(end_date), --END_DATE
1261 min(start_date), --START_DATE
1262 max(end_date)-min(start_date)+1, --TIMESPAN
1263 'READY' --COLLECTION_STATUS
1264 FROM gl_periods
1265 where period_set_name=l_period_set_name
1266 and period_type = l_period_type
1267 and adjustment_period_flag='N'
1268 group by period_year;
1269
1270 l_row_ep_cal_year:=l_row_ep_cal_year+sql%rowcount;
1271 fii_util.stop_timer;
1272 fii_util.print_timer('Process Time');
1273 edw_log.put_line(' ');
1274
1275 /* Population of Enterprise Calendar Name */
1276
1277 edw_log.put_line('Pushing Enterprise Calendar Name');
1278 fii_util.start_timer;
1279
1280 INSERT INTO EDW_TIME_EP_CAL_NAME_LSTG
1281 (CAL_NAME_PK,
1282 ALL_FK,
1283 CAL_NAME,
1284 CALENDAR_TYPE,
1285 DESCRIPTION,
1286 INSTANCE,
1287 NAME,
1288 end_date,
1289 timespan,
1290 COLLECTION_STATUS)
1291 Select sets.period_set_name,
1292 'ALL',
1293 sets.period_set_name,
1294 'Financial',
1295 sets.description,
1296 l_instance,
1297 sets.period_set_name,
1298 sysdate,
1299 1,
1300 'READY'
1301 FROM gl_period_sets sets
1302 WHERE sets.period_set_name = l_period_set_name;
1303
1304 l_row_ep_cal_name:=l_row_ep_cal_name+sql%rowcount;
1305 fii_util.stop_timer;
1306 fii_util.print_timer('Process Time');
1307 edw_log.put_line(' ');
1308
1309 -- -----------------------------------------------------------------------------
1310 -- Write result to log file
1311 -- -----------------------------------------------------------------------------
1312
1313 edw_log.put_line(' ');
1314 edw_log.put_line(to_char(l_row_ep_cal_period)||' records has been pushed to Enterprise Calendar Period');
1315 edw_log.put_line(to_char(l_row_ep_cal_qtr)||' records has been pushed to Enterprise Calendar Quarter');
1316 edw_log.put_line(to_char(l_row_ep_cal_year)||' records has been pushed to Enterprise Calendar Year');
1317 edw_log.put_line(to_char(l_row_ep_cal_name)||' records has been pushed to Enterprise Calendar Name');
1318 edw_log.put_line(' ');
1319
1320 end if;
1321
1322 Exception When others then
1323 raise;
1324
1325 END Push_gl_and_ent_calendar;
1326
1327 END FII_TIME_M_C;