1 PACKAGE BODY FII_TIME_STRUCTURE_C AS
2 /*$Header: FIICMT4B.pls 120.5 2007/03/01 07:14:06 arcdixit ship $*/
3
4 g_schema varchar2(30);
5 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
6 g_global_start_date date := bis_common_parameters.get_GLOBAL_START_DATE;
7
8 -- Bug 5624487
9 g_unassigned_day date := to_date('12/31/4712', 'MM/DD/YYYY');
10
11 G_TABLE_NOT_EXIST EXCEPTION;
12 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
13
14 ---------------------------------------------------
15 -- PRIVATE PROCEDURE TRUNCATE_TABLE
16 ---------------------------------------------------
17 procedure truncate_table (p_table_name in varchar2) is
18 l_stmt varchar2(400);
19
20 begin
21
22 l_stmt := 'truncate table '||g_schema||'.'||p_table_name;
23 if g_debug_flag = 'Y' then
24 fii_util.put_line(l_stmt);
25 end if;
26 execute immediate l_stmt;
27
28 exception
29 WHEN G_TABLE_NOT_EXIST THEN
30 null; -- Oracle 942, table does not exist, no actions
31 WHEN OTHERS THEN
32 raise;
33
34 end truncate_table;
35
36 ----------------------------------------------------------------
37 -- PRIVATE FUNCTION NOT_WITHIN
38 -- This function check that the enterprice start and end date
39 -- is within the rolling start and end date. The function will
40 -- return TRUE if this condition is satisfied.
41 ----------------------------------------------------------------
42 FUNCTION NOT_WITHIN ( p_x_start date, -- ent start date
43 p_x_end date, -- ent end date
44 p_r_start date, -- roll start date
45 p_r_end date -- roll end date
46 ) RETURN BOOLEAN IS
47 BEGIN
48 RETURN NOT ( p_x_start >= p_r_start and
49 p_x_end <= p_r_end );
50 END NOT_WITHIN;
51
52 ---------------------------------------------------
53 -- PRIVATE FUNCTION INCLUDES_XTD
54 -- this function determines whether or not a record_type_id
55 -- value contains any XTD bits
56 ---------------------------------------------------
57 FUNCTION INCLUDES_XTD
58 ( p_sum number
59 , p_rolling number
60 )
61 return varchar2 is
62
63 l_bit number := 1;
64
65 begin
66
67 loop
68
69 if l_bit >= p_rolling then
70 exit;
71 end if;
72
73 if bitand(p_sum,l_bit) = l_bit then
74 return 'Y';
75 end if;
76
77 l_bit := l_bit *2;
78
79 end loop;
80
81 return 'N';
82
83 end INCLUDES_XTD;
84
85 -----------------------------------------------------------------------
86 -- PRIVATE PROCEDURE INSERT_ROW
87 -- Given the report_date, time_id, period_type_id and record_type_id
88 -- passed in as parameter, this procedure will insert a row with
89 -- these values into FII_TIME_STRUCTURES
90 -----------------------------------------------------------------------
91 PROCEDURE INSERT_ROW(p_report_date DATE,
92 p_time_id NUMBER,
93 p_period_type_id NUMBER,
94 p_record_type_id NUMBER,
95 p_xtd_flag VARCHAR2 ) IS
96 BEGIN
97
98 INSERT INTO FII_TIME_STRUCTURES
99 ( report_date,
100 time_id,
101 period_type_id,
102 record_type_id,
103 xtd_flag,
104 creation_date,
105 created_by,
106 last_update_date,
107 last_updated_by,
108 last_update_login )
109 VALUES
110 ( p_report_date,
111 p_time_id,
112 p_period_type_id,
113 p_record_type_id,
114 p_xtd_flag,
115 sysdate,
116 fnd_global.user_id,
117 sysdate,
118 fnd_global.user_id,
119 fnd_global.login_id);
120
121 END INSERT_ROW;
122
123 ---------------------------------------------------
124 -- PUBLIC PROCEDURE LOAD_TIME_STRUCTURES
125 ---------------------------------------------------
126 PROCEDURE LOAD_TIME_STRUCTURES IS
127
128 l_status VARCHAR2(30);
129 l_industry VARCHAR2(30);
130 l_max_day DATE;
131 l_current_day DATE;
132 l_roll_year_start DATE;
133 l_roll_year_end DATE;
134 l_roll_qtr_start DATE;
135 l_roll_qtr_end DATE;
136 l_roll_month_start DATE;
137 l_roll_month_end DATE;
138 l_roll_week_start DATE;
139 l_roll_week_end DATE;
140 l_xtd_year_start DATE;
141 l_xtd_year_end DATE;
142 l_xtd_qtr_start DATE;
143 l_xtd_qtr_end DATE;
144 l_xtd_period_start DATE;
145 l_xtd_period_end DATE;
146 l_xtd_week_start DATE;
147 l_xtd_week_end DATE;
148 -- AR DBI Changes: Added for new rolling DSO periods
149 l_roll_180_start DATE;
150 l_roll_180_end DATE;
151 l_roll_60_start DATE;
152 l_roll_60_end DATE;
153 l_roll_45_start DATE;
154 l_roll_45_end DATE;
155
156 ------------------------------------
157 -- Pre-defined period_type_id values
158 ------------------------------------
159 l_nested_day NUMBER := 1;
160 l_nested_week NUMBER := 16;
161 l_nested_ent_period NUMBER := 32;
162 l_nested_ent_qtr NUMBER := 64;
163 l_nested_ent_year NUMBER := 128;
164
165 ------------------------------------
166 -- Pre-defined usage types
167 ------------------------------------
168 l_day NUMBER := 1;
169 l_current_week NUMBER := 2;
170 l_current_month NUMBER := 4;
171 l_current_qtr NUMBER := 8;
172 l_current_year NUMBER := 16;
173 l_xtd_week NUMBER := 32;
174 l_xtd_period NUMBER := 64;
175 l_xtd_qtr NUMBER := 128;
176 l_xtd_year NUMBER := 256;
177 l_itd_year NUMBER := 512;
178 l_rolling_week NUMBER := 1024;
179 l_rolling_month NUMBER := 2048;
180 l_rolling_qtr NUMBER := 4096;
181 l_rolling_year NUMBER := 8192;
182 -- AR DBI Changes: Added for new rolling DSO periods
183 l_rolling_45 NUMBER := 16384;
184 l_rolling_60 NUMBER := 32768;
185 l_rolling_180 NUMBER := 65536;
186
187 ------------------------------------------------------------------------
188 -- Variables to capture the use of each time row as an aggregate number
189 ------------------------------------------------------------------------
190 l_ent_year_id NUMBER;
191 l_year_sum NUMBER;
192 l_ent_qtr_id NUMBER;
193 l_qtr_sum NUMBER;
194 l_ent_period_id NUMBER;
195 l_month_sum NUMBER;
196 l_week_id NUMBER;
197 l_week_sum NUMBER;
198 l_day_id NUMBER;
199 l_day_sum NUMBER;
200
201 -- Cursor to retrieve all days defined in the calendar
202 -- Bug 5624487
203 CURSOR calendar_days IS
204 -- *** Why do we need next_ent_period_start_date and next_ent_period_end_date?
205 -- *** All columns can be found in fii_time_day. Should we change to select all
206 -- these columns from fii_time_day?
207 SELECT
208 report_date,
209 week_start_date,
210 week_end_date,
211 ent_period_start_date,
212 ent_period_end_date,
213 ent_qtr_start_date,
214 ent_qtr_end_date,
215 ent_year_start_date,
216 ent_year_end_date,
217 report_date_julian day_id,
218 week_id,
219 ent_period_id,
220 ent_qtr_id,
221 ent_year_id
222 FROM
223 fii_time_day
224 where report_date <> g_unassigned_day;
225 /* SELECT
226 d.report_date,
227 d.week_start_date,
228 d.week_start_date+6 week_end_date,
229 m.start_date ent_period_start_date,
230 m.end_date ent_period_end_date,
231 q.start_date ent_qtr_start_date,
232 q.end_date ent_qtr_end_date,
233 y.start_date ent_year_start_date,
234 y.end_date ent_year_end_date,
235 d.report_date_julian day_id,
236 d.week_id,
237 m.ent_period_id,
238 m.ent_qtr_id,
239 m.ent_year_id,
240 m.next_start_date next_ent_period_start_date,
241 m.next_end_date next_ent_period_end_date
242 FROM
243 fii_time_day d,
244 ( select
245 start_date
246 , end_date
247 , ent_period_id
248 , ent_qtr_id
249 , ent_year_id
250 , lead(start_date,1) over(order by start_date) next_start_date
251 , lead(end_date,1) over(order by start_date) next_end_date
252 from
253 fii_time_ent_period
254 ) m,
255 fii_time_ent_qtr q,
256 fii_time_ent_year y
257 WHERE
258 d.ent_period_id = m.ent_period_id
259 AND m.ent_qtr_id = q.ent_qtr_id
260 AND q.ent_year_id = y.ent_year_id; */
261
262 -- Bug 5624487
263 CURSOR calendar_max_day IS
264 SELECT max(report_date) max_report_date
265 FROM fii_time_day
266 where report_date <> g_unassigned_day;
267
268 l_row_cnt number := 0;
269
270 type l_number_tbl is table of number;
271 type l_date_tbl is table of date;
272
273 l_ent_year_id_tbl l_number_tbl;
274 l_ent_year_date_tbl l_date_tbl;
275
276 BEGIN
277
278 if g_schema is null then
279 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, g_schema)) THEN
280 NULL;
281 END IF;
282 end if;
283
284 truncate_table('FII_TIME_STRUCTURES');
285
286 -- Find out the max report_date defined in the calendar
287 OPEN calendar_max_day;
288 FETCH calendar_max_day INTO l_max_day;
289 CLOSE calendar_max_day;
290
291 -- load the ent year id and end date plsql tables
292 -- used by itd inside the loop
293 -- Bug 5624487
294 select
295 ent_year_id
296 , end_date
297 bulk collect into
298 l_ent_year_id_tbl
299 , l_ent_year_date_tbl
300 from fii_time_ent_year
301 where end_date < g_unassigned_day;
302
303 -- Loop for each day defined in the calendar
304 FOR d IN calendar_days LOOP
305
306 -- Confirmed with Keith Reardon that 375 will be safe high bound that we
307 -- won't miss a day
308 FOR i IN 0..375 LOOP
309
310 l_current_day := d.report_date + i;
311
312 -- Exit the loop if we have processed all days defined in the calendar.
313 IF l_current_day > l_max_day THEN
314 exit;
315 END IF;
316
317 -- Initialize time IDs and aggregate record_type_ids for each time period
318 l_ent_year_id := null;
319 l_year_sum := 0;
320 l_ent_qtr_id := null;
321 l_qtr_sum := 0;
322 l_ent_period_id := null;
323 l_month_sum := 0;
324 l_week_id := null;
325 l_week_sum := 0;
326 l_day_id := null;
327 l_day_sum := 0;
328
329 ----------------------------------------------------------------------
330 -- Processing rolling year calculations
331 ----------------------------------------------------------------------
332 IF l_current_day - 364 <= d.report_date THEN
333
334 -- Find out rolling year start and end date
335 l_roll_year_start := l_current_day - 364;
336 l_roll_year_end := l_current_day;
337
338 --------------------------------------------------------------
339 -- 1. If report_date is at the end of the enterprise year and the
340 -- enterprise year is fully within the rolling year then
341 -- add the pre-defined rolling year usage to l_year_sum
342 --------------------------------------------------------------
343 IF d.report_date = d.ent_year_end_date AND
344 d.ent_year_start_date >= l_roll_year_start AND
345 d.ent_year_end_date <= l_roll_year_end THEN
346
347 l_ent_year_id := d.ent_year_id;
348 l_year_sum := l_year_sum + l_rolling_year;
349
350 --------------------------------------------------------------
351 -- 2. If report_date is at the end of enterprise qtr and
352 -- the enterprise qtr is fully within the rolling year and
353 -- the enterprise year is not fully within rolling year then
354 -- add the pre-defined rolling year usage to l_qtr_sum
355 --------------------------------------------------------------
356 ELSIF d.report_date = d.ent_qtr_end_date AND
357 d.ent_qtr_start_date >= l_roll_year_start AND
358 -- *** Should we check ent_qtr_end_date instead of ent_period_end_date?
359 -- d.ent_period_end_date <= l_roll_year_end AND
360 d.ent_qtr_end_date <= l_roll_year_end AND
361 NOT_WITHIN( d.ent_year_start_date,
362 d.ent_year_end_date,
363 l_roll_year_start,
364 l_roll_year_end ) THEN
365
366 l_ent_qtr_id := d.ent_qtr_id;
367 l_qtr_sum := l_qtr_sum + l_rolling_year;
368
369 --------------------------------------------------------------
370 -- 3. If report_date is at the end of enterprise period and
371 -- the enterprise period fully within the rolling year and
372 -- the enterprise year is not fully within rolling year and
373 -- the enterprise qtr is not fully within rolling year then
374 -- add the pre-defined rolling year usage to l_month_sum
375 --------------------------------------------------------------
376 ELSIF d.report_date = d.ent_period_end_date AND
377 d.ent_period_start_date >= l_roll_year_start AND
378 d.ent_period_end_date <= l_roll_year_end AND
379 NOT_WITHIN( d.ent_year_start_date,
380 d.ent_year_end_date,
381 l_roll_year_start,
382 l_roll_year_end ) AND
383 NOT_WITHIN( d.ent_qtr_start_date,
384 d.ent_qtr_end_date,
385 l_roll_year_start,
386 l_roll_year_end ) THEN
387
388 l_ent_period_id := d.ent_period_id;
389 l_month_sum := l_month_sum + l_rolling_year;
390
391 --------------------------------------------------------------
392 -- 4. If report_date is at the end of a week and
393 -- the week is fully within rolling year and
394 -- the week is fully within enterprise period and
395 -- the enterprise year is not fully within rolling year and
396 -- the enterprise qtr is not fully within rolling year and
397 -- the enterprise period is not fully within rolling year then
398 -- add the pre-defined rolling year usage to l_week_sum
399 --------------------------------------------------------------
400 ELSIF d.report_date = d.week_end_date AND
401 d.week_start_date >= l_roll_year_start AND
402 d.week_end_date <= l_roll_year_end AND
403 d.week_start_date >= d.ent_period_start_date AND
404 d.week_end_date <= d.ent_period_end_date AND
405 NOT_WITHIN( d.ent_year_start_date,
406 d.ent_year_end_date,
407 l_roll_year_start,
408 l_roll_year_end ) AND
409 NOT_WITHIN( d.ent_qtr_start_date,
410 d.ent_qtr_end_date,
411 l_roll_year_start,
415 l_roll_year_start,
412 l_roll_year_end ) AND
413 NOT_WITHIN( d.ent_period_start_date,
414 d.ent_period_end_date,
416 l_roll_year_end ) THEN
417
418 l_week_id := d.week_id;
419 l_week_sum := l_week_sum + l_rolling_year;
420
421 --------------------------------------------------------------
422 -- 5. If the enterprise year is not fully within rolling year and
423 -- the enterprise qtr is not fully within rolling year and
424 -- the enterprise period is not fully within rolling year and
425 -- (the week is not fully within the rolling year or
426 -- the week is not fully within the enterprise period) then
427 -- add the pre-defined rolling year usage to l_day_sum
428 --------------------------------------------------------------
429 ELSIF NOT_WITHIN( d.ent_year_start_date,
430 d.ent_year_end_date,
431 l_roll_year_start,
432 l_roll_year_end ) AND
433 NOT_WITHIN( d.ent_qtr_start_date,
434 d.ent_qtr_end_date,
435 l_roll_year_start,
436 l_roll_year_end ) AND
437 NOT_WITHIN( d.ent_period_start_date,
438 d.ent_period_end_date,
439 l_roll_year_start,
440 l_roll_year_end ) AND
441 (NOT_WITHIN( d.week_start_date,
442 d.week_end_date,
443 l_roll_year_start,
444 l_roll_year_end ) OR
445 NOT_WITHIN( d.week_start_date,
446 d.week_end_date,
447 d.ent_period_start_date,
448 d.ent_period_end_date )) THEN
449
450 l_day_id := d.day_id;
451 l_day_sum := l_day_sum + l_rolling_year;
452
453 END IF;
454
455 END IF;
456
457 -- AR DBI Changes: Added codes to process Rolling 180-day DSO period
458 ----------------------------------------------------------------------
459 -- Processing rolling 180-day calcuations
460 ----------------------------------------------------------------------
461 IF l_current_day - 179 <= d.report_date THEN
462
463 -- Find out rolling 180-day start and end date
464 l_roll_180_start := l_current_day - 179;
465 l_roll_180_end := l_current_day;
466
467 --------------------------------------------------------------
468 -- 1.IF report_date is at the end of the enterprise qtr
469 -- AND the enterprise qtr is fully within the rolling 180-day
470 -- THEN add the pre-defined rolling 180-day usage to l_qtr_sum
471 --------------------------------------------------------------
472 IF (d.report_date = d.ent_qtr_end_date AND
473 d.ent_qtr_start_date >= l_roll_180_start AND
474 d.ent_qtr_end_date <= l_roll_180_end) THEN
475
476 l_ent_qtr_id := d.ent_qtr_id;
477 l_qtr_sum := l_qtr_sum + l_rolling_180;
478
479 -----------------------------------------------------------------
480 -- 2.IF report_date is at the end of enterprise period
481 -- AND the enterprise period is fully within the rolling 180-day
482 -- AND the enterprise qtr is not fully within the rolling 180-day
483 -- THEN add the pre-defined rolling 180-day usage to l_month_sum
484 -----------------------------------------------------------------
485 ELSIF d.report_date = d.ent_period_end_date AND
486 d.ent_period_start_date >= l_roll_180_start AND
487 d.ent_period_end_date <= l_roll_180_end AND
488 NOT_WITHIN( d.ent_qtr_start_date,
489 d.ent_qtr_end_date,
490 l_roll_180_start,
491 l_roll_180_end ) THEN
492
493 l_ent_period_id := d.ent_period_id;
494 l_month_sum := l_month_sum + l_rolling_180;
495
496 --------------------------------------------------------------------
497 -- 3.IF report_date is at the end of a week
498 -- AND the week is fully within the rolling 180-day
499 -- AND the week is fully within enterprise period
500 -- AND the enterprise qtr is not fully within the rolling 180-day
501 -- AND the enterprise period is not fully within the rolling 180-day
502 -- THEN add the pre-defined rolling 180-day usage to l_week_sum
503 --------------------------------------------------------------------
504 ELSIF d.report_date = d.week_end_date AND
505 d.week_start_date >= l_roll_180_start AND
506 d.week_end_date <= l_roll_180_end AND
507 d.week_start_date >= d.ent_period_start_date AND
508 d.week_end_date <= d.ent_period_end_date AND
509 NOT_WITHIN( d.ent_qtr_start_date,
510 d.ent_qtr_end_date,
511 l_roll_180_start,
512 l_roll_180_end ) AND
513 NOT_WITHIN( d.ent_period_start_date,
514 d.ent_period_end_date,
515 l_roll_180_start,
516 l_roll_180_end ) THEN
517
518 l_week_id := d.week_id;
519 l_week_sum := l_week_sum + l_rolling_180;
520
521 --------------------------------------------------------------------
522 -- 4.IF enterprise qtr is not fully within the rolling 180-day
523 -- AND the enterprise period is not fully within the rolling 180-day
524 -- AND ( the week is not fully within the rolling 180-day
525 -- OR the week is not fully within the enterprise period)
526 -- THEN add the pre-defined rolling 180-day usage to l_day_sum
527 --------------------------------------------------------------------
528 ELSIF NOT_WITHIN( d.ent_qtr_start_date,
529 d.ent_qtr_end_date,
530 l_roll_180_start,
531 l_roll_180_end ) AND
532 NOT_WITHIN( d.ent_period_start_date,
533 d.ent_period_end_date,
534 l_roll_180_start,
535 l_roll_180_end ) AND
536 (NOT_WITHIN( d.week_start_date,
537 d.week_end_date,
538 l_roll_180_start,
539 l_roll_180_end ) OR
540 NOT_WITHIN( d.week_start_date,
541 d.week_end_date,
542 d.ent_period_start_date,
543 d.ent_period_end_date )) THEN
544
545 l_day_id := d.day_id;
546 l_day_sum := l_day_sum + l_rolling_180;
547
548 END IF;
549
550 END IF;
551
552 ----------------------------------------------------------------------
553 -- Processing rolling quarter calculations
554 ----------------------------------------------------------------------
555 IF l_current_day - 89 <= d.report_date THEN
556
557 -- Find out rolling quarter start and end date
558 l_roll_qtr_start := l_current_day - 89;
559 l_roll_qtr_end := l_current_day;
560
561 -----------------------------------------------------------
562 -- 1. If report_date is at the end of the enterprise qtr and
563 -- the enterprise qtr is fully within the rolling qtr then
564 -- add the pre-defined rolling quarter usage to l_qtr_sum
565 -----------------------------------------------------------
566 IF (d.report_date = d.ent_qtr_end_date AND
567 d.ent_qtr_start_date >= l_roll_qtr_start AND
568 d.ent_qtr_end_date <= l_roll_qtr_end) THEN
569
570 l_ent_qtr_id := d.ent_qtr_id;
571 l_qtr_sum := l_qtr_sum + l_rolling_qtr;
572
573 -----------------------------------------------------------
574 -- 2. If report_date is at the end of enterprise period and
575 -- the enterprise period is fully within the rolling qtr and
576 -- the enterprise qtr is not fully within the rolling qtr then
577 -- add the pre-defined rolling quarter usage to l_month_sum
578 -----------------------------------------------------------
579 ELSIF d.report_date = d.ent_period_end_date AND
580 d.ent_period_start_date >= l_roll_qtr_start AND
584 l_roll_qtr_start,
581 d.ent_period_end_date <= l_roll_qtr_end AND
582 NOT_WITHIN( d.ent_qtr_start_date,
583 d.ent_qtr_end_date,
585 l_roll_qtr_end ) THEN
586
587 l_ent_period_id := d.ent_period_id;
588 l_month_sum := l_month_sum + l_rolling_qtr;
589
590 -----------------------------------------------------------
591 -- 3. If report_date is at the end of a week and
592 -- the week is fully within the rolling qtr and
593 -- the week is fully within enterprise period and
594 -- the enterprise qtr is not fully within the rolling qtr and
595 -- the enterprise period is not fully within the rolling qtr then
596 -- add the pre-defined rolling quarter usage to l_week_sum
597 -----------------------------------------------------------
598 ELSIF d.report_date = d.week_end_date AND
599 d.week_start_date >= l_roll_qtr_start AND
600 d.week_end_date <= l_roll_qtr_end AND
601 d.week_start_date >= d.ent_period_start_date AND
602 d.week_end_date <= d.ent_period_end_date AND
603 NOT_WITHIN( d.ent_qtr_start_date,
604 d.ent_qtr_end_date,
605 l_roll_qtr_start,
606 l_roll_qtr_end ) AND
607 NOT_WITHIN( d.ent_period_start_date,
608 d.ent_period_end_date,
609 l_roll_qtr_start,
610 l_roll_qtr_end ) THEN
611
612 l_week_id := d.week_id;
613 l_week_sum := l_week_sum + l_rolling_qtr;
614
615 -----------------------------------------------------------
616 -- 4. If enterprise qtr is not fully within the rolling qtr and
617 -- the enterprise period is not fully within the rolling qtr and
618 -- (the week is not fully within the rolling qtr or
619 -- the week is not fully within the enterprise period) then
620 -- add the pre-defined rolling quarter usage to l_day_sum
621 -----------------------------------------------------------
622 ELSIF NOT_WITHIN( d.ent_qtr_start_date,
623 d.ent_qtr_end_date,
624 l_roll_qtr_start,
625 l_roll_qtr_end ) AND
626 NOT_WITHIN( d.ent_period_start_date,
627 d.ent_period_end_date,
628 l_roll_qtr_start,
629 l_roll_qtr_end ) AND
630 (NOT_WITHIN( d.week_start_date,
631 d.week_end_date,
632 l_roll_qtr_start,
633 l_roll_qtr_end ) OR
634 NOT_WITHIN( d.week_start_date,
635 d.week_end_date,
636 d.ent_period_start_date,
637 d.ent_period_end_date )) THEN
638
639 l_day_id := d.day_id;
640 l_day_sum := l_day_sum + l_rolling_qtr;
641
642 END IF;
643
644 END IF;
645
646 -- AR DBI Changes: Added codes to process Rolling 60-day DSO period
647 ----------------------------------------------------------------------
648 -- Processing rolling 60-day calculations
649 ----------------------------------------------------------------------
650 IF l_current_day - 59 <= d.report_date THEN
651
652 -- Find out rolling 60-day start and end date
653 l_roll_60_start := l_current_day - 59;
654 l_roll_60_end := l_current_day;
655
656 ---------------------------------------------------------------
657 -- 1.IF report_date is at the end of the enterprise period
658 -- AND the enterprise period is fully with the rolling 60-day
659 -- THEN add the pre-defined rolling 60-day usage to l_month_sum
660 ---------------------------------------------------------------
661 IF (d.report_date = d.ent_period_end_date AND
662 d.ent_period_start_date >= l_roll_60_start AND
663 d.ent_period_end_date <= l_roll_60_end )THEN
664
665 l_ent_period_id := d.ent_period_id;
666 l_month_sum := l_month_sum + l_rolling_60;
667
668 -------------------------------------------------------------------
669 -- 2.IF report_date is the end of a week
670 -- AND the week is fully within the rolling 60-day
671 -- AND the week is fully within enterprise period
672 -- AND the enterprise period is not fully within the rolling 60-day
673 -- THEN add the pre-defined rolling 60-day usage to l_week_sum
674 -------------------------------------------------------------------
675 ELSIF d.report_date = d.week_end_date AND
676 d.week_start_date >= l_roll_60_start AND
677 d.week_end_date <= l_roll_60_end AND
678 d.week_start_date >= d.ent_period_start_date AND
679 d.week_end_date <= d.ent_period_end_date AND
680 NOT_WITHIN( d.ent_period_start_date,
681 d.ent_period_end_date,
682 l_roll_60_start,
683 l_roll_60_end )
684 THEN
685
689 --------------------------------------------------------------------
686 l_week_id := d.week_id;
687 l_week_sum := l_week_sum + l_rolling_60;
688
690 -- 3.IF the enterprise period is not fully within the rolling 60-day
691 -- AND ( the week is not fully within the rolling 60-day
692 -- OR the week is not fully within the enterprise period)
693 -- THEN add the pre-defined rolling 60-day usage to l_day_sum
694 --------------------------------------------------------------------
695 ELSIF NOT_WITHIN( d.ent_period_start_date,
696 d.ent_period_end_date,
697 l_roll_60_start,
698 l_roll_60_end ) AND
699 (NOT_WITHIN( d.week_start_date,
700 d.week_end_date,
701 l_roll_60_start,
702 l_roll_60_end) OR
703 NOT_WITHIN( d.week_start_date,
704 d.week_end_date,
705 d.ent_period_start_date,
706 d.ent_period_end_date )) THEN
707
708 l_day_id := d.day_id;
709 l_day_sum := l_day_sum + l_rolling_60;
710
711 END IF;
712
713 END IF;
714
715 -- AR DBI Changes: Added codes to process Rolling 45-day DSO period
716 ----------------------------------------------------------------------
717 -- Processing rolling 45-day calculations
718 ----------------------------------------------------------------------
719 IF l_current_day - 44 <= d.report_date THEN
720
721 -- Find out rolling 45-day start and end date
722 l_roll_45_start := l_current_day - 44;
723 l_roll_45_end := l_current_day;
724
725 ---------------------------------------------------------------
726 -- 1.IF report_date is at the end of the enterprise period
727 -- AND the enterprise period is fully with the rolling 60-day
728 -- THEN add the pre-defined rolling 45-day usage to l_month_sum
729 ---------------------------------------------------------------
730 IF (d.report_date = d.ent_period_end_date AND
731 d.ent_period_start_date >= l_roll_45_start AND
732 d.ent_period_end_date <= l_roll_45_end )THEN
733
734 l_ent_period_id := d.ent_period_id;
735 l_month_sum := l_month_sum + l_rolling_45;
736
737 -------------------------------------------------------------------
738 -- 2.IF report_date is the end of a week
739 -- AND the week is fully within the rolling 45-day
740 -- AND the week is fully within enterprise period
741 -- AND the enterprise period is not fully within the rolling 45-day
742 -- THEN add the pre-defined rolling 45-day usage to l_week_sum
743 -------------------------------------------------------------------
744 ELSIF d.report_date = d.week_end_date AND
745 d.week_start_date >= l_roll_45_start AND
746 d.week_end_date <= l_roll_45_end AND
747 d.week_start_date >= d.ent_period_start_date AND
748 d.week_end_date <= d.ent_period_end_date AND
749 NOT_WITHIN( d.ent_period_start_date,
750 d.ent_period_end_date,
751 l_roll_45_start,
752 l_roll_45_end )
753 THEN
754
755 l_week_id := d.week_id;
756 l_week_sum := l_week_sum + l_rolling_45;
757
758 --------------------------------------------------------------------
759 -- 3.IF the enterprise period is not fully within the rolling 45-day
760 -- AND ( the week is not fully within the rolling 45-day
761 -- OR the week is not fully within the enterprise period)
762 -- THEN add the pre-defined rolling 45-day usage to l_day_sum
763 --------------------------------------------------------------------
764 ELSIF NOT_WITHIN( d.ent_period_start_date,
765 d.ent_period_end_date,
766 l_roll_45_start,
767 l_roll_45_end ) AND
768 (NOT_WITHIN( d.week_start_date,
769 d.week_end_date,
770 l_roll_45_start,
771 l_roll_45_end) OR
772 NOT_WITHIN( d.week_start_date,
773 d.week_end_date,
774 d.ent_period_start_date,
775 d.ent_period_end_date )) THEN
776
777 l_day_id := d.day_id;
778 l_day_sum := l_day_sum + l_rolling_45;
779
780 END IF;
781
782 END IF;
783
784 ----------------------------------------------------------------------
785 -- Processing rolling month calculations
786 ----------------------------------------------------------------------
787 IF l_current_day - 29 <= d.report_date THEN
788
789 -- Find out rolling month start and end date
790 l_roll_month_start := l_current_day - 29;
791 l_roll_month_end := l_current_day;
792
793 -----------------------------------------------------------
797 -----------------------------------------------------------
794 -- 1. If report_date is at the end of the enterprise period and
795 -- the enterprise period is fully with the rolling month then
796 -- add the pre-defined rolling month usage to l_month_sum
798 IF d.report_date = d.ent_period_end_date AND
799 d.ent_period_start_date >= l_roll_month_start AND
800 d.ent_period_end_date <= l_roll_month_end /*AND
801 -- *** Why do we need to check the enterprise period is not fully within the
802 -- rolling month?
803 NOT_WITHIN( d.ent_period_start_date,
804 d.ent_period_end_date,
805 l_roll_month_start,
806 l_roll_month_end) */THEN
807
808 l_ent_period_id := d.ent_period_id;
809 l_month_sum := l_month_sum + l_rolling_month;
810
811 -----------------------------------------------------------
812 -- 2. If report_date is the end of a week and
813 -- the week is fully within the rolling month and
814 -- the enterprise period is not fully within the rolling month then
815 -- add the pre-defined rolling month usage to l_week_sum
816 -----------------------------------------------------------
817 ELSIF d.report_date = d.week_end_date AND
818 d.week_start_date >= l_roll_month_start AND
819 d.week_end_date <= l_roll_month_end AND
820 -- *** We need to check its enterprise period is not fully within the rolling
821 -- month
822 NOT_WITHIN( d.ent_period_start_date,
823 d.ent_period_end_date,
824 l_roll_month_start,
825 l_roll_month_end) THEN
826
827 l_week_id := d.week_id;
828 l_week_sum := l_week_sum + l_rolling_month;
829
830 -----------------------------------------------------------
831 -- 3. If the enterprise period is not fully within the rolling month
832 -- and the week is not fully within the rolling month then
833 -- add the pre-defined rolling month usage to l_day_sum
834 -----------------------------------------------------------
835 ELSIF NOT_WITHIN( d.ent_period_start_date,
836 d.ent_period_end_date,
837 l_roll_month_start,
838 l_roll_month_end) AND
839 NOT_WITHIN( d.week_start_date,
840 d.week_end_date,
841 l_roll_month_start,
842 l_roll_month_end) THEN
843
844 l_day_id := d.day_id;
845 l_day_sum := l_day_sum + l_rolling_month;
846
847 END IF;
848
849 END IF;
850
851 ----------------------------------------------------------------------
852 -- Processing rolling week calculations
853 ----------------------------------------------------------------------
854 IF l_current_day-6 <= d.report_date THEN
855
856 -- Find out rolling week start and end date
857 l_roll_week_start := l_current_day-6;
858 l_roll_week_end := l_current_day;
859
860 -----------------------------------------------------------
861 -- 1. If report_date is the end of week and
862 -- the week is fully within the rolling week then
863 -- add the pre-defined rolling week usage to l_week_sum
864 -----------------------------------------------------------
865 IF d.report_date = d.week_end_date AND
866 d.week_start_date >= l_roll_week_start AND
867 d.week_end_date <= l_roll_week_end THEN
868
869 l_week_id := d.week_id;
870 l_week_sum := l_week_sum + l_rolling_week;
871
872 -----------------------------------------------------------
873 -- 2. If the week is not fully with the rolling week then
874 -- add the pre-defined rolling week usage to l_day_sum
875 -----------------------------------------------------------
876 ELSIF NOT_WITHIN( d.week_start_date,
877 d.week_end_date,
878 l_roll_week_start,
879 l_roll_week_end ) THEN
880
881 l_day_id := d.day_id;
882 l_day_sum := l_day_sum + l_rolling_week;
883
884 END IF;
885
886 END IF;
887
888 ----------------------------------------------------------------------
889 -- Processing days
890 ----------------------------------------------------------------------
891 IF l_current_day = d.report_date THEN
892
893 l_day_id := d.day_id;
894 l_day_sum := l_day_sum + l_day;
895
896 END IF;
897
898 ----------------------------------------------------------------------
899 -- Processing XTD year calculations
900 ----------------------------------------------------------------------
901 IF l_current_day <= d.ent_year_end_date THEN
902
903 -- Find out xtd year start and end date
904 l_xtd_year_start := d.ent_year_start_date;
905 l_xtd_year_end := l_current_day;
906
910 -----------------------------------------------------------
907 -----------------------------------------------------------
908 -- 1. if report_date is the end of enterprise year then
909 -- add the pre-defined YTD calculation usage to l_year_sum
911 IF d.report_date = d.ent_year_end_date THEN
912
913 l_ent_year_id := d.ent_year_id;
914 l_year_sum := l_year_sum + l_xtd_year;
915 l_year_sum := l_year_sum + l_itd_year;
916 l_year_sum := l_year_sum + l_current_year;
917
918 -----------------------------------------------------------
919 -- 2. If report_date is the end of enterprise qtr and
920 -- report_date is not the end of enterprise year and
921 -- xtd year end < the end of the enterprise year then
922 -- add the pre-defined YTD calculation usage to l_qtr_sum
923 -----------------------------------------------------------
924 ELSIF d.report_date = d.ent_qtr_end_date AND
925 d.report_date <> d.ent_year_end_date AND
926 l_xtd_year_end < d.ent_year_end_date THEN
927
928 l_ent_qtr_id := d.ent_qtr_id;
929 l_qtr_sum := l_qtr_sum + l_xtd_year;
930 l_qtr_sum := l_qtr_sum + l_itd_year;
931
932 -----------------------------------------------------------
933 -- 3. If report_date is the end of enterprise period and
934 -- report_date is not the end of enterprise year and
935 -- report_date is not the end of enterprise qtr and
936 -- xtd year end < the end of the enterprise year and
937 -- xtd year end < the end of the enterprise qtr then
938 -- add the pre-defined YTD calculation usage to l_month_sum
939 -----------------------------------------------------------
940 ELSIF d.report_date = d.ent_period_end_date AND
941 d.report_date <> d.ent_year_end_date AND
942 d.report_date <> d.ent_qtr_end_date AND
943 l_xtd_year_end < d.ent_year_end_date AND
944 l_xtd_year_end < d.ent_qtr_end_date THEN
945
946 l_ent_period_id := d.ent_period_id;
947 l_month_sum := l_month_sum + l_xtd_year;
948 l_month_sum := l_month_sum + l_itd_year;
949
950 -----------------------------------------------------------
951 -- 4. If report_date is the end of a week and
952 -- report_date is not the end of enterprise year and
953 -- report_date is not the end of enterprise qtr and
954 -- report_date is not the end of enterprise period and
955 -- xtd year end < the end of the enterprise year and
956 -- xtd year end < the end of the enterprise qtr and
957 -- xtd year end < the end of the enterprise period and
958 -- week is within the enterprise period and
959 -- week is within the xtd year end then
960 -- add the pre-defined YTD calculation usage to l_week_sum
961 -----------------------------------------------------------
962 ELSIF d.report_date = d.week_end_date AND
963 d.report_date <> d.ent_year_end_date AND
964 d.report_date <> d.ent_qtr_end_date AND
965 d.report_date <> d.ent_period_end_date AND
966 l_xtd_year_end < d.ent_year_end_date AND
967 l_xtd_year_end < d.ent_qtr_end_date AND
968 l_xtd_year_end < d.ent_period_end_date AND
969 d.week_start_date >= d.ent_period_start_date AND
970 d.week_end_date <= d.ent_period_end_date AND
971 d.week_start_date >= l_xtd_year_start AND
972 d.week_end_date <= l_xtd_year_end THEN
973
974 l_week_id := d.week_id;
975 l_week_sum := l_week_sum + l_xtd_year;
976 l_week_sum := l_week_sum + l_itd_year;
977
978 -----------------------------------------------------------
979 -- 5. If report_date is not the end of enterprise year and
980 -- report_date is not the end of enterprise qtr and
981 -- report_date is not the end of enterprise period and
982 -- enterprise period is not within xtd year and
983 -- (week is not within xtd year or
984 -- week is not within enterprise period) then
985 -- add the pre-defined YTD calculation usage to l_day_sum
986 -----------------------------------------------------------
987 ELSIF d.report_date <> d.ent_year_end_date AND
988 d.report_date <> d.ent_qtr_end_date AND
989 d.report_date <> d.ent_period_end_date AND
990 NOT_WITHIN( d.ent_period_start_date,
991 d.ent_period_end_date,
992 l_xtd_year_start,
993 l_xtd_year_end ) AND
994 (NOT_WITHIN( d.week_start_date,
995 d.week_end_date,
996 l_xtd_year_start,
997 l_xtd_year_end ) OR
998 NOT_WITHIN( d.week_start_date,
999 d.week_end_date,
1000 d.ent_period_start_date,
1001 d.ent_period_end_date )) THEN
1002
1003 l_day_id := d.day_id;
1004 l_day_sum := l_day_sum + l_xtd_year;
1005 l_day_sum := l_day_sum + l_itd_year;
1006
1007 END IF;
1008
1012 -- Processing XTD quarter calculations
1009 END IF;
1010
1011 ----------------------------------------------------------------------
1013 ----------------------------------------------------------------------
1014 IF l_current_day <= d.ent_qtr_end_date THEN
1015
1016 -- Find out xtd quarter start and end date
1017 l_xtd_qtr_start := d.ent_qtr_start_date;
1018 l_xtd_qtr_end := l_current_day;
1019
1020 -----------------------------------------------------------
1021 -- 1. If report_date is the end of enterprise qtr then
1022 -- add the pre-defined QTD calculation usage to l_qtr_sum
1023 -----------------------------------------------------------
1024 IF d.report_date = d.ent_qtr_end_date THEN
1025
1026 l_ent_qtr_id := d.ent_qtr_id;
1027 l_qtr_sum := l_qtr_sum + l_xtd_qtr;
1028 l_qtr_sum := l_qtr_sum + l_current_qtr;
1029
1030 -----------------------------------------------------------
1031 -- 2. If report_date is the end of enterprise period and
1032 -- report_date is not the end of enterprise qtr and
1033 -- xtd qtr < end of enterprise qtr then
1034 -- add the pre-defined QTD calculation usage to l_month_sum
1035 -----------------------------------------------------------
1036 ELSIF d.report_date = d.ent_period_end_date AND
1037 d.report_date <> d.ent_qtr_end_date AND
1038 l_xtd_qtr_end < d.ent_qtr_end_date THEN
1039
1040 l_ent_period_id := d.ent_period_id;
1041 l_month_sum := l_month_sum + l_xtd_qtr;
1042
1043 -----------------------------------------------------------
1044 -- 3. If report_date is the end of a week and
1045 -- report_date is not the end of enterprise qtr and
1046 -- report_date is not the end of enterprise period and
1047 -- xtd qtr end < the end of the enterprise qtr and
1048 -- xtd qtr end < the end of the enterprise period and
1049 -- week is within the enterprise period and
1050 -- week is within the xtd qtr then
1051 -- add the pre-defined QTD calculation usage to l_week_sum
1052 -----------------------------------------------------------
1053 ELSIF d.report_date = d.week_end_date AND
1054 d.report_date <> d.ent_qtr_end_date AND
1055 d.report_date <> d.ent_period_end_date AND
1056 l_xtd_qtr_end < d.ent_qtr_end_date AND
1057 l_xtd_qtr_end < d.ent_period_end_date AND
1058 d.week_start_date >= d.ent_period_start_date AND
1059 d.week_end_date <= d.ent_period_end_date AND
1060 d.week_start_date >= l_xtd_qtr_start AND
1061 d.week_end_date <= l_xtd_qtr_end THEN
1062
1063 l_week_id := d.week_id;
1064 l_week_sum := l_week_sum + l_xtd_qtr;
1065
1066 -----------------------------------------------------------
1067 -- 4. If report_date is not the end of enterprise qtr and
1068 -- report_date is not the end of enterprise period and
1069 -- enterprise period is not within xtd qtr and
1070 -- (week is not within xtd qtr or
1071 -- week is not within enterprise period) then
1072 -- add the pre-defined QTD calculation usage to l_day_sum
1073 -----------------------------------------------------------
1074 ELSIF d.report_date <> d.ent_qtr_end_date AND
1075 d.report_date <> d.ent_period_end_date AND
1076 NOT_WITHIN( d.ent_period_start_date,
1077 d.ent_period_end_date,
1078 l_xtd_qtr_start,
1079 l_xtd_qtr_end ) AND
1080 (NOT_WITHIN( d.week_start_date,
1081 d.week_end_date,
1082 l_xtd_qtr_start,
1083 l_xtd_qtr_end ) OR
1084 NOT_WITHIN( d.week_start_date,
1085 d.week_end_date,
1086 d.ent_period_start_date,
1087 d.ent_period_end_date )) THEN
1088
1089 l_day_id := d.day_id;
1090 l_day_sum := l_day_sum + l_xtd_qtr;
1091
1092 END IF;
1093
1094 END IF;
1095
1096 ----------------------------------------------------------------------
1097 -- Processing XTD month calculations
1098 ----------------------------------------------------------------------
1099 IF l_current_day <= d.ent_period_end_date THEN
1100
1101 -- Find out xtd period start and end date
1102 l_xtd_period_start := d.ent_period_start_date;
1103 l_xtd_period_end := l_current_day;
1104
1105 -----------------------------------------------------------
1106 -- 1. If report_date is the end of enterprise period then
1107 -- add the pre-defined PTD calculation usage to l_month_sum
1108 -----------------------------------------------------------
1109 IF d.report_date = d.ent_period_end_date THEN
1110
1111 l_ent_period_id := d.ent_period_id;
1112 l_month_sum := l_month_sum + l_xtd_period;
1113 l_month_sum := l_month_sum + l_current_month;
1114
1118 -- xtd period end < the end of the enterprise period and
1115 -----------------------------------------------------------
1116 -- 2. If report_date is the end of a week and
1117 -- report_date is not the end of enterprise period and
1119 -- week is within the xtd period and then
1120 -- add the pre-defined PTD calculation usage to l_week_sum
1121 -----------------------------------------------------------
1122 ELSIF d.report_date = d.week_end_date AND
1123 d.report_date <> d.ent_period_end_date AND
1124 l_xtd_period_end < d.ent_period_end_date AND
1125 d.week_start_date >= l_xtd_period_start AND
1126 d.week_end_date <= l_xtd_period_end THEN
1127
1128 l_week_id := d.week_id;
1129 l_week_sum := l_week_sum + l_xtd_period;
1130
1131 -----------------------------------------------------------
1132 -- 3. If report_date is not the end of enterprise period and
1133 -- xtd period end < the end of the period and
1134 -- (week is not within xtd period or
1135 -- week is not within enterprise period) then
1136 -- add the pre-defined PTD calculation usage to l_day_sum
1137 -----------------------------------------------------------
1138 ELSIF d.report_date <> d.ent_period_end_date AND
1139 l_xtd_period_end < d.ent_period_end_date AND
1140 (NOT_WITHIN( d.week_start_date,
1141 d.week_end_date,
1142 l_xtd_period_start,
1143 l_xtd_period_end ) OR
1144 NOT_WITHIN( d.week_start_date,
1145 d.week_end_date,
1146 d.ent_period_start_date,
1147 d.ent_period_end_date )) THEN
1148
1149 l_day_id := d.day_id;
1150 l_day_sum := l_day_sum + l_xtd_period;
1151
1152 END IF;
1153
1154 END IF;
1155
1156 ----------------------------------------------------------------------
1157 -- Processing XTD week calculations
1158 ----------------------------------------------------------------------
1159 IF l_current_day <= d.week_end_date THEN
1160
1161 -- Find out xtd week start and end date
1162 l_xtd_week_start := d.week_start_date;
1163 l_xtd_week_end := l_current_day;
1164
1165 -----------------------------------------------------------
1166 -- 1. If report_date is the end of a week then
1167 -- add the pre-defined WTD calculation usage to l_week_sum
1168 -----------------------------------------------------------
1169 IF d.report_date = d.week_end_date THEN
1170
1171 l_week_id := d.week_id;
1172 l_week_sum := l_week_sum + l_xtd_week;
1173 l_week_sum := l_week_sum + l_current_week;
1174
1175 -----------------------------------------------------------
1176 -- 2. If report_date is not the end of the week and
1177 -- the current day is not the end of the week then
1178 -- add the pre-defined WTD calculation usage to l_day_sum
1179 -----------------------------------------------------------
1180 ELSIF d.report_date <> d.week_end_date AND
1181 l_current_day <> d.week_end_date THEN
1182
1183 l_day_id := d.day_id;
1184 l_day_sum := l_day_sum + l_xtd_week;
1185
1186 END IF;
1187
1188 END IF;
1189
1190 ----------------------------------------------------------------------
1191 -- Inserting rows into FII_TIME_STRUCTURES for Rolling and XTD calendar
1192 ----------------------------------------------------------------------
1193 IF l_ent_year_id IS NOT NULL THEN
1194 insert_row(l_current_day, l_ent_year_id, l_nested_ent_year,
1195 l_year_sum, INCLUDES_XTD(l_year_sum,l_rolling_week));
1196 l_row_cnt := l_row_cnt+1;
1197 END IF;
1198
1199 IF l_ent_qtr_id IS NOT NULL THEN
1200 insert_row(l_current_day, l_ent_qtr_id, l_nested_ent_qtr,
1201 l_qtr_sum, INCLUDES_XTD(l_qtr_sum,l_rolling_week));
1202 l_row_cnt := l_row_cnt+1;
1203 END IF;
1204
1205 IF l_ent_period_id IS NOT NULL THEN
1206 insert_row(l_current_day, l_ent_period_id, l_nested_ent_period,
1207 l_month_sum, INCLUDES_XTD(l_month_sum,l_rolling_week));
1208 l_row_cnt := l_row_cnt+1;
1209 END IF;
1210
1211 IF l_week_id IS NOT NULL THEN
1212 insert_row(l_current_day, l_week_id, l_nested_week,
1213 l_week_sum, INCLUDES_XTD(l_week_sum,l_rolling_week));
1214 l_row_cnt := l_row_cnt+1;
1215 END IF;
1216
1217 IF l_day_id IS NOT NULL THEN
1218 insert_row(l_current_day, l_day_id, l_nested_day,
1219 l_day_sum, INCLUDES_XTD(l_day_sum,l_rolling_week));
1220 l_row_cnt := l_row_cnt+1;
1221 END IF;
1222
1223 END LOOP;
1224
1225 -- Initialize time IDs and aggregate record_type_ids for each time period
1226 l_ent_year_id := null;
1227 l_year_sum := 0;
1228 l_ent_qtr_id := null;
1229 l_qtr_sum := 0;
1230 l_ent_period_id := null;
1231 l_month_sum := 0;
1232 l_week_id := null;
1233 l_week_sum := 0;
1234 l_day_id := null;
1235 l_day_sum := 0;
1236
1237 ----------------------------------------------------------------------
1238 -- Processing Current year calculations
1239 ----------------------------------------------------------------------
1240 IF d.report_date BETWEEN d.ent_year_start_date
1241 AND d.ent_year_end_date and
1242 d.report_date <> d.ent_year_end_date THEN
1243
1244 l_ent_year_id := d.ent_year_id;
1245 l_year_sum := l_year_sum + l_current_year;
1246
1247 END IF;
1248
1249 ----------------------------------------------------------------------
1250 -- Processing Current quarter calculations
1251 ----------------------------------------------------------------------
1252 IF d.report_date BETWEEN d.ent_qtr_start_date
1253 AND d.ent_qtr_end_date and
1254 d.report_date <> d.ent_qtr_end_date THEN
1255
1256 l_ent_qtr_id := d.ent_qtr_id;
1257 l_qtr_sum := l_qtr_sum + l_current_qtr;
1258
1259 END IF;
1260
1261 ----------------------------------------------------------------------
1262 -- Processing Current month calculations
1263 ----------------------------------------------------------------------
1264 IF d.report_date BETWEEN d.ent_period_start_date
1265 AND d.ent_period_end_date and
1266 d.report_date <> d.ent_period_end_date THEN
1267
1268 l_ent_period_id := d.ent_period_id;
1269 l_month_sum := l_month_sum + l_current_month;
1270
1271 END IF;
1272
1273 ----------------------------------------------------------------------
1274 -- Processing Current week calculations
1275 ----------------------------------------------------------------------
1276 IF d.report_date BETWEEN d.week_start_date
1277 AND d.week_end_date and
1278 d.report_date <> d.week_end_date THEN
1279
1280 l_week_id := d.week_id;
1281 l_week_sum := l_week_sum + l_current_week;
1282
1283 END IF;
1284
1285 ----------------------------------------------------------------------
1286 -- Inserting rows into FII_TIME_STRUCTURES for Current periods
1287 ----------------------------------------------------------------------
1288 IF l_ent_year_id IS NOT NULL THEN
1289 insert_row(d.report_date, l_ent_year_id, l_nested_ent_year, l_year_sum, 'Y');
1290 l_row_cnt := l_row_cnt+1;
1291 END IF;
1292
1293 IF l_ent_qtr_id IS NOT NULL THEN
1294 insert_row(d.report_date, l_ent_qtr_id, l_nested_ent_qtr, l_qtr_sum, 'Y');
1295 l_row_cnt := l_row_cnt+1;
1296 END IF;
1297
1298 IF l_ent_period_id IS NOT NULL THEN
1299 insert_row(d.report_date, l_ent_period_id, l_nested_ent_period,
1300 l_month_sum, 'Y');
1301 l_row_cnt := l_row_cnt+1;
1302 END IF;
1303
1304 IF l_week_id IS NOT NULL THEN
1305 insert_row(d.report_date, l_week_id, l_nested_week, l_week_sum, 'Y');
1306 l_row_cnt := l_row_cnt+1;
1307 END IF;
1308
1309 IF l_day_id IS NOT NULL THEN
1310 insert_row(d.report_date, l_day_id, l_nested_day, l_day_sum, 'Y');
1311 l_row_cnt := l_row_cnt+1;
1312 END IF;
1313
1314 ----------------------------------------------------------------------
1315 -- Processing Inception-To-Date
1316 ----------------------------------------------------------------------
1317 FOR y in 1..l_ent_year_id_tbl.count LOOP
1318
1319 l_ent_year_id := null;
1320 l_year_sum := 0;
1321
1322 IF (l_ent_year_date_tbl(y) >= g_global_start_date AND
1323 l_ent_year_date_tbl(y) < d.report_date) THEN
1324
1325 insert_row(d.report_date, l_ent_year_id_tbl(y), l_nested_ent_year,
1326 l_itd_year, 'Y');
1327 l_row_cnt := l_row_cnt+1;
1328
1329 END IF;
1330
1331 END LOOP;
1332
1333 COMMIT;
1334
1335 END LOOP;
1336
1337 IF g_debug_flag = 'Y' THEN
1338 fii_util.put_line(TO_CHAR(l_row_cnt)||' records has been populated to the Reporting Structure table for XTD and Rolling Periods');
1339 END IF;
1340
1341 fnd_stats.gather_table_stats(ownname => g_schema
1342 , tabname => 'FII_TIME_STRUCTURES'
1343 );
1344
1345 if g_debug_flag = 'Y' then
1346 fii_util.put_line('Gathered statistics for Reporting Structure table for XTD and Rolling Periods');
1347 end if;
1348
1349 END LOAD_TIME_STRUCTURES;
1350
1351 END FII_TIME_STRUCTURE_C;
[Home] [Help]