1 PACKAGE BODY FII_TIME_API AS
2 /* $Header: FIICAT1B.pls 120.19 2006/01/25 19:06:19 vkazhipu noship $ */
3
4 -- -------------------------------------------------------------------
5 -- Name: global_start_date
6 -- Desc: Returns the global start date of the
7 -- enterprise calendar. Info is cached after initial access
8 -- Output: Global Start Date of the enterprise year.
9 -- Error: If any sql errors occurs, an exception is raised.
10 -- --------------------------------------------------------------------
11 Function global_start_date return DATE is
12 l_date date;
13 begin
14 select min(start_date)
15 into l_date
16 from fii_time_ent_year;
17
18 return l_date;
19 end;
20
21 -- -------------------------------------------------------------------
22 -- Name: global_end_date
23 -- Desc: Returns the global end date of the
24 -- enterprise calendar. Info is cached after initial access
25 -- Output: Global End Date of the enterprise year.
26 -- Error: If any sql errors occurs, an exception is raised.
27 -- --------------------------------------------------------------------
28 Function global_end_date return DATE is
29 l_date date;
30 begin
31 select max(end_date)
32 into l_date
33 from fii_time_ent_year;
34
35 return l_date;
36 end;
37
38 -- -------------------------------------------------------------------
39 -- Name: ent_sd_lyr_beg
40 -- Desc: Returns the same day last year, count from year start date in the
41 -- enterprise calendar. Info is cached after initial access
42 -- Output: Same date(as the pass in date) in previous enterprise year.
43 -- Error: If any sql errors occurs, an exception is raised.
44 -- --------------------------------------------------------------------
45 Function ent_sd_lyr_beg(as_of_date date) return DATE is
46 l_date date;
47 l_timespan number;
48 l_curr_year number;
49 l_as_of_date date;
50 begin
51
52 l_as_of_date := trunc(as_of_date);
53 select l_as_of_date-start_date, sequence
54 into l_timespan, l_curr_year
55 from fii_time_ent_year
56 where l_as_of_date between start_date and end_date;
57
58 select start_date+l_timespan
59 into l_date
60 from fii_time_ent_year
61 where sequence=l_curr_year-1;
62
63 return l_date;
64 end;
65
66 -- -------------------------------------------------------------------
67 -- Name: ent_sd_lyr_end
68 -- Desc: Returns the same day last year, count from year end date in the
69 -- enterprise calendar. Info is cached after initial access
70 -- Output: Same date(as the pass in date) in previous enterprise year.
71 -- Error: If any sql errors occurs, an exception is raised.
72 -- --------------------------------------------------------------------
73 Function ent_sd_lyr_end(as_of_date date) return DATE is
74 l_date date;
75 l_timespan number;
76 l_curr_year number;
77 l_as_of_date date;
78 begin
79
80 l_as_of_date := trunc(as_of_date);
81 select end_date-l_as_of_date, sequence
82 into l_timespan, l_curr_year
83 from fii_time_ent_year
84 where l_as_of_date between start_date and end_date;
85
86 select greatest(start_date, end_date-l_timespan)
87 into l_date
88 from fii_time_ent_year
89 where sequence=l_curr_year-1;
90
91 return l_date;
92 end;
93
94 -- -------------------------------------------------------------------
95 -- Name: ent_sd_lysqtr_beg
96 -- Desc: Returns the same day last year same quarter, count from quarter start
97 -- date in the enterprise calendar. Info is cached after initial access
98 -- Output: Same date(as the pass in date) same quarter in previous enterprise year.
99 -- Error: If any sql errors occurs, an exception is raised.
100 -- --------------------------------------------------------------------
101 Function ent_sd_lysqtr_beg(as_of_date date) return DATE is
102 l_date date;
103 l_timespan number;
104 l_curr_qtr number;
105 l_curr_year number;
106 l_as_of_date date;
107 begin
108 l_as_of_date := trunc(as_of_date);
109 select l_as_of_date-start_date, sequence, ent_year_id
110 into l_timespan, l_curr_qtr, l_curr_year
111 from fii_time_ent_qtr
112 where l_as_of_date between start_date and end_date;
113
114 select least(end_date, start_date+l_timespan)
115 into l_date
116 from fii_time_ent_qtr
117 where sequence=l_curr_qtr
118 and ent_year_id=l_curr_year-1;
119
120 return l_date;
121 end;
122
123 -- -------------------------------------------------------------------
124 -- Name: ent_sd_lysqtr_end
125 -- Desc: Returns the same day last year same quarter, count from quarter end
126 -- date in the enterprise calendar. Info is cached after initial access
127 -- Output: Same date(as the pass in date) same quarter in previous enterprise year.
128 -- Error: If any sql errors occurs, an exception is raised.
129 -- --------------------------------------------------------------------
130 Function ent_sd_lysqtr_end(as_of_date date) return DATE is
131 l_date date;
132 l_timespan number;
133 l_curr_qtr number;
134 l_curr_year number;
135 l_as_of_date date;
136 begin
137 l_as_of_date := trunc(as_of_date);
138 select end_date-l_as_of_date, sequence, ent_year_id
139 into l_timespan, l_curr_qtr, l_curr_year
140 from fii_time_ent_qtr
141 where l_as_of_date between start_date and end_date;
142
143 select greatest(start_date, end_date-l_timespan)
144 into l_date
145 from fii_time_ent_qtr
146 where sequence=l_curr_qtr
147 and ent_year_id=l_curr_year-1;
148
149 return l_date;
150 end;
151
152 -- -------------------------------------------------------------------
153 -- Name: ent_sd_pqtr_beg
154 -- Desc: Returns the same day prior quarter, count from quarter start
155 -- date in the enterprise calendar. Info is cached after initial access
156 -- Output: Same date(as the pass in date) in prior enterprise quarter.
157 -- Error: If any sql errors occurs, an exception is raised.
158 -- --------------------------------------------------------------------
159 Function ent_sd_pqtr_beg(as_of_date date) return DATE is
160 l_date date;
161 l_timespan number;
162 l_curr_qtr number;
163 l_curr_year number;
164 l_as_of_date date;
165 begin
166 l_as_of_date := trunc(as_of_date);
167 select l_as_of_date-start_date, sequence, ent_year_id
168 into l_timespan, l_curr_qtr, l_curr_year
169 from fii_time_ent_qtr
170 where l_as_of_date between start_date and end_date;
171
172 if l_curr_qtr=1 then
173 select least(end_date, start_date+l_timespan)
174 into l_date
175 from fii_time_ent_qtr
176 where sequence=4
177 and ent_year_id=l_curr_year-1;
178 else
179 select least(end_date, start_date+l_timespan)
180 into l_date
181 from fii_time_ent_qtr
182 where sequence=l_curr_qtr-1
183 and ent_year_id=l_curr_year;
184 end if;
185
186 return l_date;
187 end;
188
189 -- -------------------------------------------------------------------
190 -- Name: ent_sd_pqtr_end
191 -- Desc: Returns the same day prior quarter, count from quarter end date
192 -- in the enterprise calendar. Info is cached after initial access
193 -- Output: Same date(as the pass in date) in prior enterprise quarter.
194 -- Error: If any sql errors occurs, an exception is raised.
195 -- --------------------------------------------------------------------
196 Function ent_sd_pqtr_end(as_of_date date) return DATE is
197 l_date date;
198 l_timespan number;
202 begin
199 l_curr_qtr number;
200 l_curr_year number;
201 l_as_of_date date;
203 l_as_of_date := trunc(as_of_date);
204 select end_date-l_as_of_date, sequence, ent_year_id
205 into l_timespan, l_curr_qtr, l_curr_year
206 from fii_time_ent_qtr
207 where l_as_of_date between start_date and end_date;
208
209 if l_curr_qtr=1 then
210 select greatest(start_date, end_date-l_timespan)
211 into l_date
212 from fii_time_ent_qtr
213 where sequence=4
214 and ent_year_id=l_curr_year-1;
215 else
216 select greatest(start_date, end_date-l_timespan)
217 into l_date
218 from fii_time_ent_qtr
219 where sequence=l_curr_qtr-1
220 and ent_year_id=l_curr_year;
221 end if;
222
223 return l_date;
224 end;
225
226 -- -------------------------------------------------------------------
227 -- Name: ent_sd_lysper_beg
228 -- Desc: Returns the same day last year same period, count from period start
229 -- date in the enterprise calendar. Info is cached after initial access
230 -- Output: Same date(as the pass in date) same period in previous enterprise year.
231 -- Error: If any sql errors occurs, an exception is raised.
232 -- --------------------------------------------------------------------
233 Function ent_sd_lysper_beg(as_of_date date) return DATE is
234 l_date date;
235 l_timespan number;
236 l_curr_period number;
237 l_curr_year number;
238 l_as_of_date date;
239 begin
240 l_as_of_date := trunc(as_of_date);
241 select l_as_of_date-p.start_date, p.sequence, p.ent_year_id
242 into l_timespan, l_curr_period, l_curr_year
243 from fii_time_ent_period p
244 where l_as_of_date between p.start_date and p.end_date;
245
246 select least(p.end_date, p.start_date+l_timespan)
247 into l_date
248 from fii_time_ent_period p
249 where p.sequence=l_curr_period
250 and p.ent_year_id=l_curr_year-1;
251
252 return l_date;
253 end;
254
255 -- -------------------------------------------------------------------
256 -- Name: ent_sd_lysper_end
257 -- Desc: Returns the same day last year same period, count from period end
258 -- date in the enterprise calendar. Info is cached after initial access
259 -- Output: Same date(as the pass in date) same period in previous enterprise year.
260 -- Error: If any sql errors occurs, an exception is raised.
261 -- --------------------------------------------------------------------
262 Function ent_sd_lysper_end(as_of_date date) return DATE is
263 l_date date;
264 l_timespan number;
265 l_curr_period number;
266 l_curr_year number;
267 l_as_of_date date;
268 begin
269 l_as_of_date := trunc(as_of_date);
270 select p.end_date-l_as_of_date, p.sequence, p.ent_year_id
271 into l_timespan, l_curr_period, l_curr_year
272 from fii_time_ent_period p
273 where l_as_of_date between p.start_date and p.end_date;
274
275 select greatest(p.start_date, p.end_date-l_timespan)
276 into l_date
277 from fii_time_ent_period p
278 where p.sequence=l_curr_period
279 and p.ent_year_id=l_curr_year-1;
280
281 return l_date;
282 end;
283
284 -- -------------------------------------------------------------------
285 -- Name: ent_sd_pper_beg
286 -- Desc: Returns the same day prior period, count from period start
287 -- date in the enterprise calendar. Info is cached after initial access
288 -- Output: Same date(as the pass in date) in prior enterprise period.
289 -- Error: If any sql errors occurs, an exception is raised.
290 -- --------------------------------------------------------------------
291 Function ent_sd_pper_beg(as_of_date date) return DATE is
292 l_date date;
293 l_timespan number;
294 l_curr_period number;
295 l_curr_year number;
296 l_as_of_date date;
297 l_max_sequence number;
298 begin
299 l_as_of_date := trunc(as_of_date);
300 select l_as_of_date-p.start_date, p.sequence, p.ent_year_id
301 into l_timespan, l_curr_period, l_curr_year
302 from fii_time_ent_period p
303 where l_as_of_date between p.start_date and p.end_date;
304
305 if l_curr_period=1 then
306 -- Bug 4002034
307 select max(sequence) into l_max_sequence
308 from fii_time_ent_period
309 where ent_year_id = l_curr_year-1;
310
311 select least(p.end_date, p.start_date+l_timespan)
312 into l_date
313 from fii_time_ent_period p
314 where p.sequence=l_max_sequence
315 and p.ent_year_id=l_curr_year-1;
316 else
317 select least(p.end_date, p.start_date+l_timespan)
318 into l_date
319 from fii_time_ent_period p
320 where p.sequence=l_curr_period-1
321 and p.ent_year_id=l_curr_year;
322 end if;
323
324 return l_date;
325 end;
326
327 -- -------------------------------------------------------------------
328 -- Name: ent_sd_pper_end
329 -- Desc: Returns the same day prior period, count from period end date
330 -- in the enterprise calendar. Info is cached after initial access
334 Function ent_sd_pper_end(as_of_date date) return DATE is
331 -- Output: Same date(as the pass in date) in prior enterprise period.
332 -- Error: If any sql errors occurs, an exception is raised.
333 -- --------------------------------------------------------------------
335 l_date date;
336 l_timespan number;
337 l_curr_period number;
338 l_curr_year number;
339 l_as_of_date date;
340 l_max_sequence number;
341 begin
342 l_as_of_date := trunc(as_of_date);
343 select p.end_date-l_as_of_date, p.sequence, p.ent_year_id
344 into l_timespan, l_curr_period, l_curr_year
345 from fii_time_ent_period p
346 where l_as_of_date between p.start_date and p.end_date;
347
348 if l_curr_period=1 then
349 -- Bug 4002034
350 select max(sequence) into l_max_sequence
351 from fii_time_ent_period
352 where ent_year_id = l_curr_year-1;
353
354 select greatest(p.start_date, p.end_date-l_timespan)
355 into l_date
356 from fii_time_ent_period p
357 where p.sequence=l_max_sequence
358 and p.ent_year_id=l_curr_year-1;
359 else
360 select greatest(p.start_date, p.end_date-l_timespan)
361 into l_date
362 from fii_time_ent_period p
363 where p.sequence=l_curr_period-1
364 and p.ent_year_id=l_curr_year;
365 end if;
366
367 return l_date;
368 end;
369
370 -- -------------------------------------------------------------------
371 -- Name: sd_lyswk
372 -- Desc: Returns the same day last year same week in the enterprise calendar.
373 -- Info is cached after initial access
374 -- Output: Same date(as the pass in date) same week in previous enterprise year.
375 -- Error: If any sql errors occurs, an exception is raised.
376 -- --------------------------------------------------------------------
377 Function sd_lyswk(as_of_date date) return DATE is
378 l_date date;
379 l_timespan number;
380 l_curr_week number;
381 l_curr_year number;
382 l_as_of_date date;
383 l_max_sequence number;
384 begin
385 l_as_of_date := trunc(as_of_date);
386 select l_as_of_date-w.start_date, w.sequence, p.year445_id
387 into l_timespan, l_curr_week, l_curr_year
388 from fii_time_week w, fii_time_p445 p
389 where w.period445_id=p.period445_id
390 and l_as_of_date between w.start_date and w.end_date;
391
392 -- Bug 4099178. To find the maximum sequence of weeks in the
393 -- current year.
394 select max(w.sequence)
395 into l_max_sequence
396 from fii_time_week w, fii_time_p445 p
397 where w.period445_id=p.period445_id
398 and l_as_of_date between w.start_date and w.end_date;
399
400 -- Bug 4099178. If current year has 53 weeks then look for previous years
401 -- week 52, for current years week 52 look at week 51 .... for week 2 look at previous
402 -- years week 1 and for week 1 look into week 1 of previous year.
403 IF (l_max_sequence = 53) THEN
404 IF (l_curr_week = 1) THEN
405 -- If the sequence is 1 then look for the same week in the previous year
406 select w.start_date+l_timespan
407 into l_date
408 from fii_time_week w, fii_time_p445 p
409 where w.period445_id=p.period445_id
410 and w.sequence=l_curr_week
411 and p.year445_id=l_curr_year-1;
412
413 ELSE
414 -- If the sequence is between 2-53 then look for previous week in the previous year
415 select w.start_date+l_timespan
416 into l_date
417 from fii_time_week w, fii_time_p445 p
418 where w.period445_id=p.period445_id
419 and w.sequence=l_curr_week - 1
420 and p.year445_id=l_curr_year-1;
421
422 END IF;
423 ELSE
424 -- When the maximum sequnce is not 53 then look for the same week in the previous year
425 select w.start_date+l_timespan
426 into l_date
427 from fii_time_week w, fii_time_p445 p
428 where w.period445_id=p.period445_id
429 and w.sequence=l_curr_week
430 and p.year445_id=l_curr_year-1;
431
432 END IF;
433
434 return l_date;
435 end;
436
437 -- -------------------------------------------------------------------
438 -- Name: sd_pwk
439 -- Desc: Returns the same day prior week in the enterprise calendar.
440 -- Info is cached after initial access
441 -- Output: Same date(as the pass in date) in prior week.
442 -- Error: If any sql errors occurs, an exception is raised.
443 -- --------------------------------------------------------------------
444 Function sd_pwk(as_of_date date) return DATE is
445 l_date date;
446 l_timespan number;
447 l_curr_week number;
448 l_curr_year number;
449 l_as_of_date date;
450 begin
451 l_as_of_date := trunc(as_of_date);
452 select l_as_of_date-w.start_date, w.sequence, p.year445_id
453 into l_timespan, l_curr_week, l_curr_year
454 from fii_time_week w, fii_time_p445 p
455 where w.period445_id=p.period445_id
456 and l_as_of_date between w.start_date and w.end_date;
457
458 if l_curr_week=1 then
459 select max(w.sequence)+1
460 into l_curr_week
461 from fii_time_week w, fii_time_p445 p
462 where w.period445_id=p.period445_id
463 and p.year445_id=l_curr_year-1;
464
465 l_curr_year := l_curr_year-1;
466 end if;
467
468 select w.start_date+l_timespan
469 into l_date
470 from fii_time_week w, fii_time_p445 p
471 where w.period445_id=p.period445_id
472 and w.sequence=l_curr_week-1
473 and p.year445_id=l_curr_year;
474
475 return l_date;
476 end;
477
478 -- -------------------------------------------------------------------
479 -- Name: ent_cyr_start
480 -- Desc: Returns current enterprise year start date.
481 -- Info is cached after initial access
482 -- Output: Current Enterprise year start date
483 -- Error: If any sql errors occurs, an exception is raised.
484 -- --------------------------------------------------------------------
485 Function ent_cyr_start(as_of_date date) return DATE is
486 l_date date;
487 l_as_of_date date;
488 begin
489 l_as_of_date := trunc(as_of_date);
490 select start_date
491 into l_date
492 from fii_time_ent_year
493 where l_as_of_date between start_date and end_date;
494
495 return l_date;
496 end;
497
498 -- -------------------------------------------------------------------
499 -- Name: ent_cyr_end
500 -- Desc: Returns current enterprise year end date.
501 -- Info is cached after initial access
502 -- Output: Current Enterprise year end date
503 -- Error: If any sql errors occurs, an exception is raised.
504 -- --------------------------------------------------------------------
505 Function ent_cyr_end(as_of_date date) return DATE is
506 l_date date;
507 l_as_of_date date;
508 begin
509 l_as_of_date := trunc(as_of_date);
510 select end_date
511 into l_date
512 from fii_time_ent_year
513 where l_as_of_date between start_date and end_date;
514
515 return l_date;
516 end;
517
518 -- -------------------------------------------------------------------
519 -- Name: ent_pyr_start
520 -- Desc: Returns previous enterprise year start date.
521 -- Info is cached after initial access
522 -- Output: Previous Enterprise year start date
523 -- Error: If any sql errors occurs, an exception is raised except no data found
524 -- --------------------------------------------------------------------
525 Function ent_pyr_start(as_of_date date) return DATE is
526 l_date date;
527 l_as_of_date date;
528 begin
529 l_as_of_date := trunc(as_of_date);
530 -- Bug fix 4696282: Changed to catch No Data Found exception and return
531 -- 01/01/1900
532 BEGIN
533 select start_date
534 into l_date
535 from fii_time_ent_year
536 where sequence =
537 (select sequence -1
538 from fii_time_ent_year
539 where l_as_of_date between start_date and end_date);
540 EXCEPTION
541 WHEN NO_DATA_FOUND THEN
542 l_date := TO_DATE('01/01/1900', 'MM/DD/YYYY');
543 END;
544
545 return l_date;
546 end;
547
548 -- -------------------------------------------------------------------
549 -- Name: ent_pyr_end
550 -- Desc: Returns previous enterprise year end date.
551 -- Info is cached after initial access
552 -- Output: Previous Enterprise year end date
553 -- Error: If any sql errors occurs, an exception is raised except no data found
554 -- --------------------------------------------------------------------
558 begin
555 Function ent_pyr_end(as_of_date date) return DATE is
556 l_date date;
557 l_as_of_date date;
559 l_as_of_date := trunc(as_of_date);
560 -- Bug fix 4696282: Changed to catch No Data Found exception and return
561 -- 01/01/1900
562 BEGIN
563 select end_date
564 into l_date
565 from fii_time_ent_year
566 where sequence =
567 (select sequence -1
568 from fii_time_ent_year
569 where l_as_of_date between start_date and end_date);
570 EXCEPTION
571 WHEN NO_DATA_FOUND THEN
572 l_date := TO_DATE('01/01/1900', 'MM/DD/YYYY');
573 END;
574
575 return l_date;
576 end;
577
578 -- -------------------------------------------------------------------
579 -- Name: ent_cqtr_start
580 -- Desc: Returns current enterprise quarter start date.
581 -- Info is cached after initial access
582 -- Output: Current Enterprise quarter start date
583 -- Error: If any sql errors occurs, an exception is raised.
584 -- --------------------------------------------------------------------
585 Function ent_cqtr_start(as_of_date date) return DATE is
586 l_date date;
587 l_as_of_date date;
588 begin
589 l_as_of_date := trunc(as_of_date);
590 select start_date
591 into l_date
592 from fii_time_ent_qtr
593 where l_as_of_date between start_date and end_date;
594
595 return l_date;
596 end;
597
598 -- -------------------------------------------------------------------
599 -- Name: ent_cqtr_end
600 -- Desc: Returns current enterprise quarter end date.
601 -- Info is cached after initial access
602 -- Output: Current Enterprise quarter end date
603 -- Error: If any sql errors occurs, an exception is raised.
604 -- --------------------------------------------------------------------
605 Function ent_cqtr_end(as_of_date date) return DATE is
606 l_date date;
607 l_as_of_date date;
608 begin
609 l_as_of_date := trunc(as_of_date);
610 select end_date
611 into l_date
612 from fii_time_ent_qtr
613 where l_as_of_date between start_date and end_date;
614
615 return l_date;
616 end;
617
618 -- -------------------------------------------------------------------
619 -- Name: ent_lysqtr_start
620 -- Desc: Returns start date of same enterprise quarter in previous year.
621 -- Info is cached after initial access
622 -- Output: Last year same Enterprise quarter start date
623 -- Error: If any sql errors occurs, an exception is raised.
624 -- --------------------------------------------------------------------
625 Function ent_lysqtr_start(as_of_date date) return DATE is
626 l_date date;
627 l_curr_qtr number;
628 l_curr_year number;
629 l_as_of_date date;
630 begin
631 l_as_of_date := trunc(as_of_date);
632 select sequence, ent_year_id
633 into l_curr_qtr, l_curr_year
634 from fii_time_ent_qtr
635 where l_as_of_date between start_date and end_date;
636
637 select start_date
638 into l_date
639 from fii_time_ent_qtr
640 where sequence=l_curr_qtr
641 and ent_year_id=l_curr_year-1;
642
643 return l_date;
644 end;
645
646 -- -------------------------------------------------------------------
647 -- Name: ent_lysqtr_end
648 -- Desc: Returns end date of same enterprise quarter in previous year.
649 -- Info is cached after initial access
650 -- Output: Last year same Enterprise quarter end date
651 -- Error: If any sql errors occurs, an exception is raised.
652 -- --------------------------------------------------------------------
653 Function ent_lysqtr_end(as_of_date date) return DATE is
654 l_date date;
655 l_curr_qtr number;
656 l_curr_year number;
657 l_as_of_date date;
658 begin
659 l_as_of_date := trunc(as_of_date);
660 select sequence, ent_year_id
661 into l_curr_qtr, l_curr_year
662 from fii_time_ent_qtr
663 where l_as_of_date between start_date and end_date;
664
665 select end_date
666 into l_date
667 from fii_time_ent_qtr
668 where sequence=l_curr_qtr
669 and ent_year_id=l_curr_year-1;
670
671 return l_date;
672 end;
673
674 -- -------------------------------------------------------------------
675 -- Name: ent_pqtr_start
676 -- Desc: Returns previous enterprise quarter start date.
677 -- Info is cached after initial access
678 -- Output: Previous enterprise quarter start date
679 -- Error: If any sql errors occurs, an exception is raised except no data found
680 -- when querying previous quarter
681 -- --------------------------------------------------------------------
682 Function ent_pqtr_start(as_of_date date) return DATE is
683 l_date date;
684 l_curr_qtr number;
685 l_curr_year number;
686 l_as_of_date date;
687 begin
688 l_as_of_date := trunc(as_of_date);
689 select sequence, ent_year_id
690 into l_curr_qtr, l_curr_year
691 from fii_time_ent_qtr
692 where l_as_of_date between start_date and end_date;
693
694 -- Bug fix 4696282: Changed to catch No Data Found exception and return
695 -- 01/01/1900
696 BEGIN
697 if l_curr_qtr=1 then
698 select start_date
699 into l_date
703 else
700 from fii_time_ent_qtr
701 where sequence=4
702 and ent_year_id=l_curr_year-1;
704 select start_date
705 into l_date
706 from fii_time_ent_qtr
707 where sequence=l_curr_qtr-1
708 and ent_year_id=l_curr_year;
709 end if;
710 EXCEPTION
711 WHEN NO_DATA_FOUND THEN
712 l_date := TO_DATE('01/01/1900', 'MM/DD/YYYY');
713 END;
714
715 return l_date;
716 end;
717
718 -- -------------------------------------------------------------------
719 -- Name: ent_pqtr_end
720 -- Desc: Returns previous enterprise quarter end date.
721 -- Info is cached after initial access
722 -- Output: Previous enterprise quarter end date
723 -- Error: If any sql errors occurs, an exception is raised except no data found
724 -- when querying previous quarter
725 -- --------------------------------------------------------------------
726 Function ent_pqtr_end(as_of_date date) return DATE is
727 l_date date;
728 l_curr_qtr number;
729 l_curr_year number;
730 l_as_of_date date;
731 begin
732 l_as_of_date := trunc(as_of_date);
733 select sequence, ent_year_id
734 into l_curr_qtr, l_curr_year
735 from fii_time_ent_qtr
736 where l_as_of_date between start_date and end_date;
737
738 -- Bug fix 4696282: Changed to catch No Data Found exception and return
739 -- 01/01/1900
740 BEGIN
741 if l_curr_qtr=1 then
742 select end_date
743 into l_date
744 from fii_time_ent_qtr
745 where sequence=4
746 and ent_year_id=l_curr_year-1;
747 else
748 select end_date
749 into l_date
750 from fii_time_ent_qtr
751 where sequence=l_curr_qtr-1
752 and ent_year_id=l_curr_year;
753 end if;
754 EXCEPTION
755 WHEN NO_DATA_FOUND THEN
756 l_date := TO_DATE('01/01/1900', 'MM/DD/YYYY');
757 END;
758
759 return l_date;
760 end;
761
762 -- -------------------------------------------------------------------
763 -- Name: ent_cper_start
764 -- Desc: Returns current enterprise period start date.
765 -- Info is cached after initial access
766 -- Output: Current Enterprise period start date
767 -- Error: If any sql errors occurs, an exception is raised.
768 -- --------------------------------------------------------------------
769 Function ent_cper_start(as_of_date date) return DATE is
770 l_date date;
771 l_as_of_date date;
772 begin
773 l_as_of_date := trunc(as_of_date);
774 select start_date
775 into l_date
776 from fii_time_ent_period
777 where l_as_of_date between start_date and end_date;
778
779 return l_date;
780 end;
781
782 -- -------------------------------------------------------------------
783 -- Name: ent_cper_end
784 -- Desc: Returns current enterprise period end date.
785 -- Info is cached after initial access
786 -- Output: Current Enterprise period end date
787 -- Error: If any sql errors occurs, an exception is raised.
788 -- --------------------------------------------------------------------
789 Function ent_cper_end(as_of_date date) return DATE is
790 l_date date;
791 l_as_of_date date;
792 begin
793 l_as_of_date := trunc(as_of_date);
794 select end_date
795 into l_date
796 from fii_time_ent_period
797 where l_as_of_date between start_date and end_date;
798
799 return l_date;
800 end;
801
802 -- -------------------------------------------------------------------
803 -- Name: ent_lysper_start
804 -- Desc: Returns start date of same enterprise period in previous year.
805 -- Info is cached after initial access
806 -- Output: Last year same Enterprise period start date
807 -- Error: If any sql errors occurs, an exception is raised.
808 -- --------------------------------------------------------------------
809 Function ent_lysper_start(as_of_date date) return DATE is
810 l_date date;
811 l_curr_period number;
812 l_curr_year number;
813 l_as_of_date date;
814 begin
815 l_as_of_date := trunc(as_of_date);
816 select p.sequence, p.ent_year_id
817 into l_curr_period, l_curr_year
818 from fii_time_ent_period p
819 where l_as_of_date between p.start_date and p.end_date;
820
821 select p.start_date
822 into l_date
823 from fii_time_ent_period p
824 where p.sequence=l_curr_period
825 and p.ent_year_id=l_curr_year-1;
826
827 return l_date;
828 end;
829
830 -- -------------------------------------------------------------------
831 -- Name: ent_lysper_end
832 -- Desc: Returns end date of same enterprise period in previous year.
833 -- Info is cached after initial access
834 -- Output: Last year same Enterprise period end date
835 -- Error: If any sql errors occurs, an exception is raised.
836 -- --------------------------------------------------------------------
837 Function ent_lysper_end(as_of_date date) return DATE is
838 l_date date;
839 l_curr_period number;
840 l_curr_year number;
841 l_as_of_date date;
842 begin
843 l_as_of_date := trunc(as_of_date);
844 select p.sequence, p.ent_year_id
845 into l_curr_period, l_curr_year
849 select p.end_date
846 from fii_time_ent_period p
847 where l_as_of_date between p.start_date and p.end_date;
848
850 into l_date
851 from fii_time_ent_period p
852 where p.sequence=l_curr_period
853 and p.ent_year_id=l_curr_year-1;
854
855 return l_date;
856 end;
857
858 -- -------------------------------------------------------------------
859 -- Name: ent_pper_start
860 -- Desc: Returns previous enterprise period start date.
861 -- Info is cached after initial access
862 -- Output: Previous enterprise period start date
863 -- Error: If any sql errors occurs, an exception is raised except no data found
864 -- when querying previous period
865 -- --------------------------------------------------------------------
866 Function ent_pper_start(as_of_date date) return DATE is
867 l_date date;
868 l_curr_period number;
869 l_curr_year number;
870 l_as_of_date date;
871 l_max_sequence number;
872 begin
873 l_as_of_date := trunc(as_of_date);
874 select p.sequence, p.ent_year_id
875 into l_curr_period, l_curr_year
876 from fii_time_ent_period p
877 where l_as_of_date between p.start_date and p.end_date;
878
879 -- Bug fix 4696282: Changed to catch No Data Found exception and return
880 -- 01/01/1900
881 BEGIN
882 if l_curr_period=1 then
883 -- Bug 4002034
884 select max(sequence) into l_max_sequence
885 from fii_time_ent_period
886 where ent_year_id = l_curr_year-1;
887
888 select p.start_date
889 into l_date
890 from fii_time_ent_period p
891 where p.sequence=l_max_sequence
892 and p.ent_year_id=l_curr_year-1;
893 else
894 select p.start_date
895 into l_date
896 from fii_time_ent_period p
897 where p.sequence=l_curr_period-1
898 and p.ent_year_id=l_curr_year;
899 end if;
900 EXCEPTION
901 WHEN NO_DATA_FOUND THEN
902 l_date := TO_DATE('01/01/1900', 'MM/DD/YYYY');
903 END;
904
905 return l_date;
906 end;
907
908 -- -------------------------------------------------------------------
909 -- Name: ent_pper_end
910 -- Desc: Returns previous enterprise period end date.
911 -- Info is cached after initial access
912 -- Output: Previous enterprise period end date
913 -- Error: If any sql errors occurs, an exception is raised except no data found
914 -- when querying previous period
915 -- --------------------------------------------------------------------
916 Function ent_pper_end(as_of_date date) return DATE is
917 l_date date;
918 l_curr_period number;
919 l_curr_year number;
920 l_as_of_date date;
921 l_max_sequence number;
922 begin
923 l_as_of_date := trunc(as_of_date);
924 select p.sequence, p.ent_year_id
925 into l_curr_period, l_curr_year
926 from fii_time_ent_period p
927 where l_as_of_date between p.start_date and p.end_date;
928
929 -- Bug fix 4696282: Changed to catch No Data Found exception and return
930 -- 01/01/1900
931 BEGIN
932 if l_curr_period=1 then
933 -- Bug 4002034
934 select max(sequence) into l_max_sequence
935 from fii_time_ent_period
936 where ent_year_id = l_curr_year-1;
937
938 select p.end_date
939 into l_date
940 from fii_time_ent_period p
941 where p.sequence=l_max_sequence
942 and p.ent_year_id=l_curr_year-1;
943 else
944 select p.end_date
945 into l_date
946 from fii_time_ent_period p
947 where p.sequence=l_curr_period-1
948 and p.ent_year_id=l_curr_year;
949 end if;
950 EXCEPTION
951 WHEN NO_DATA_FOUND THEN
952 l_date := TO_DATE('01/01/1900', 'MM/DD/YYYY');
953 END;
954
955 return l_date;
956 end;
957
958 -- -------------------------------------------------------------------
959 -- Name: cwk_start
960 -- Desc: Returns current week start date.
961 -- Info is cached after initial access
962 -- Output: Current Week start date
963 -- Error: If any sql errors occurs, an exception is raised.
964 -- --------------------------------------------------------------------
965 Function cwk_start(as_of_date date) return DATE is
966 l_date date;
967 l_as_of_date date;
968 begin
969 l_as_of_date := trunc(as_of_date);
970 select start_date
971 into l_date
972 from fii_time_week
973 where l_as_of_date between start_date and end_date;
974
975 return l_date;
976 end;
977
978 -- -------------------------------------------------------------------
979 -- Name: cwk_end
980 -- Desc: Returns current week end date.
981 -- Info is cached after initial access
982 -- Output: Current Week end date
983 -- Error: If any sql errors occurs, an exception is raised.
984 -- --------------------------------------------------------------------
985 Function cwk_end(as_of_date date) return DATE is
986 l_date date;
987 l_as_of_date date;
988 begin
989 l_as_of_date := trunc(as_of_date);
990 select end_date
991 into l_date
992 from fii_time_week
993 where l_as_of_date between start_date and end_date;
994
995 return l_date;
996 end;
997
998 -- -------------------------------------------------------------------
999 -- Name: lyswk_start
1000 -- Desc: Returns start date of same week in previous year.
1001 -- Info is cached after initial access
1002 -- Output: Last year same week start date
1003 -- Error: If any sql errors occurs, an exception is raised.
1004 -- --------------------------------------------------------------------
1005 Function lyswk_start(as_of_date date) return DATE is
1006 l_date date;
1007 l_curr_week number;
1008 l_curr_year number;
1009 l_as_of_date date;
1010 begin
1011 l_as_of_date := trunc(as_of_date);
1012 select w.sequence, p.year445_id
1013 into l_curr_week, l_curr_year
1014 from fii_time_week w, fii_time_p445 p
1015 where w.period445_id=p.period445_id
1016 and l_as_of_date between w.start_date and w.end_date;
1017
1018 select w.start_date
1019 into l_date
1020 from fii_time_week w, fii_time_p445 p
1021 where w.period445_id=p.period445_id
1022 and w.sequence=l_curr_week
1023 and p.year445_id=l_curr_year-1;
1024
1025 return l_date;
1026 end;
1027
1028 -- -------------------------------------------------------------------
1029 -- Name: lyswk_end
1030 -- Desc: Returns end date of same week in previous year.
1031 -- Info is cached after initial access
1032 -- Output: Last year same week end date
1033 -- Error: If any sql errors occurs, an exception is raised.
1034 -- --------------------------------------------------------------------
1035 Function lyswk_end(as_of_date date) return DATE is
1036 l_date date;
1037 l_curr_week number;
1038 l_curr_year number;
1039 l_as_of_date date;
1040 begin
1041 l_as_of_date := trunc(as_of_date);
1042 select w.sequence, p.year445_id
1043 into l_curr_week, l_curr_year
1044 from fii_time_week w, fii_time_p445 p
1045 where w.period445_id=p.period445_id
1046 and l_as_of_date between w.start_date and w.end_date;
1047
1048 select w.end_date
1049 into l_date
1050 from fii_time_week w, fii_time_p445 p
1054
1051 where w.period445_id=p.period445_id
1052 and w.sequence=l_curr_week
1053 and p.year445_id=l_curr_year-1;
1055 return l_date;
1056 end;
1057
1058 -- -------------------------------------------------------------------
1059 -- Name: pwk_start
1060 -- Desc: Returns previous week start date.
1061 -- Info is cached after initial access
1062 -- Output: Previous Week start date
1063 -- Error: If any sql errors occurs, an exception is raised except no data found
1064 -- when querying previous week
1065 -- --------------------------------------------------------------------
1066 Function pwk_start(as_of_date date) return DATE is
1067 l_date date;
1068 l_curr_week number;
1069 l_curr_year number;
1070 l_as_of_date date;
1071 begin
1072 l_as_of_date := trunc(as_of_date);
1073 select w.sequence, p.year445_id
1074 into l_curr_week, l_curr_year
1075 from fii_time_week w, fii_time_p445 p
1076 where w.period445_id=p.period445_id
1077 and l_as_of_date between w.start_date and w.end_date;
1078
1079 -- Bug fix 4696282: Changed to catch No Data Found exception and return
1080 -- 01/01/1900
1081 BEGIN
1082 if l_curr_week=1 then
1083 select max(w.sequence)+1
1084 into l_curr_week
1085 from fii_time_week w, fii_time_p445 p
1086 where w.period445_id=p.period445_id
1087 and p.year445_id=l_curr_year-1;
1088
1089 l_curr_year := l_curr_year-1;
1090 end if;
1091
1092 select w.start_date
1093 into l_date
1094 from fii_time_week w, fii_time_p445 p
1095 where w.period445_id=p.period445_id
1096 and w.sequence=l_curr_week-1
1097 and p.year445_id=l_curr_year;
1098 EXCEPTION
1099 WHEN NO_DATA_FOUND THEN
1100 l_date := TO_DATE('01/01/1900', 'MM/DD/YYYY');
1101 END;
1102
1103 return l_date;
1104 end;
1105
1106 -- -------------------------------------------------------------------
1107 -- Name: pwk_end
1108 -- Desc: Returns previous week end date.
1109 -- Info is cached after initial access
1110 -- Output: Previous Week end date
1111 -- Error: If any sql errors occurs, an exception is raised except no data found
1112 -- when querying previous week
1113 -- --------------------------------------------------------------------
1114 Function pwk_end(as_of_date date) return DATE is
1115 l_date date;
1116 l_curr_week number;
1117 l_curr_year number;
1118 l_as_of_date date;
1119 begin
1120 l_as_of_date := trunc(as_of_date);
1121 select w.sequence, p.year445_id
1122 into l_curr_week, l_curr_year
1123 from fii_time_week w, fii_time_p445 p
1124 where w.period445_id=p.period445_id
1125 and l_as_of_date between w.start_date and w.end_date;
1126
1127 -- Bug fix 4696282: Changed to catch No Data Found exception and return
1128 -- 01/01/1900
1129 BEGIN
1130 if l_curr_week=1 then
1131 select max(w.sequence)+1
1132 into l_curr_week
1133 from fii_time_week w, fii_time_p445 p
1134 where w.period445_id=p.period445_id
1135 and p.year445_id=l_curr_year-1;
1136
1137 l_curr_year := l_curr_year-1;
1138 end if;
1139
1140 select w.end_date
1141 into l_date
1142 from fii_time_week w, fii_time_p445 p
1143 where w.period445_id=p.period445_id
1144 and w.sequence=l_curr_week-1
1145 and p.year445_id=l_curr_year;
1146 EXCEPTION
1147 WHEN NO_DATA_FOUND THEN
1148 l_date := TO_DATE('01/01/1900', 'MM/DD/YYYY');
1149 END;
1150
1151 return l_date;
1152 end;
1153
1154 -- -------------------------------------------------------------------
1155 -- Name: rmth_start
1156 -- Desc: Returns rolling month start date.
1157 -- Info is cached after initial access
1158 -- Output: Rolling Month start date
1159 -- Error: If any sql errors occurs, an exception is raised.
1160 -- --------------------------------------------------------------------
1161 Function rmth_start(as_of_date date) return DATE is
1162 l_date date;
1163 l_as_of_date date;
1164 begin
1165 l_as_of_date := trunc(as_of_date);
1166
1167 select l_as_of_date - 29
1168 into l_date
1169 from dual;
1170
1171 return l_date;
1172 end;
1173
1174 -- -------------------------------------------------------------------
1175 -- Name: rqtr_start
1176 -- Desc: Returns rolling quarter start date.
1177 -- Info is cached after initial access
1178 -- Output: Rolling Quarter start date
1179 -- Error: If any sql errors occurs, an exception is raised.
1180 -- --------------------------------------------------------------------
1181 Function rqtr_start(as_of_date date) return DATE is
1182 l_date date;
1183 l_as_of_date date;
1184 begin
1185 l_as_of_date := trunc(as_of_date);
1186
1187 select l_as_of_date - 89
1188 into l_date
1189 from dual;
1190
1191 return l_date;
1192 end;
1193
1194 -- -------------------------------------------------------------------
1195 -- Name: ryr_start
1196 -- Desc: Returns rolling year start date.
1197 -- Info is cached after initial access
1198 -- Output: Rolling Year start date
1199 -- Error: If any sql errors occurs, an exception is raised.
1200 -- --------------------------------------------------------------------
1201 Function ryr_start(as_of_date date) return DATE is
1202 l_date date;
1203 l_as_of_date date;
1204 begin
1205 l_as_of_date := trunc(as_of_date);
1206
1207 select l_as_of_date - 364
1208 into l_date
1209 from dual;
1210
1211 return l_date;
1212 end;
1213
1214 -- -------------------------------------------------------------------
1215 -- Name: rwk_start
1219 -- Error: If any sql errors occurs, an exception is raised.
1216 -- Desc: Returns rolling week start date.
1217 -- Info is cached after initial access
1218 -- Output: Rolling Week start date
1220 -- --------------------------------------------------------------------
1221 Function rwk_start(as_of_date date) return DATE is
1222 l_date date;
1223 l_as_of_date date;
1224 begin
1225 l_as_of_date := trunc(as_of_date);
1226
1227 select l_as_of_date-6
1228 into l_date
1229 from dual;
1230
1231 return l_date;
1232 end;
1233
1234 -- -------------------------------------------------------------------
1235 -- Name: day_left_in_qtr
1236 -- Desc: Returns number of days left in a quarter in a specific format.
1237 -- Info is cached after initial access
1238 -- Output: Number of days left in a quarter. e.g. given 08-Apr-2002, it returns
1239 -- Q4 FY02 Day: -54
1240 -- Error: If any sql errors occurs, an exception is raised.
1241 -- --------------------------------------------------------------------
1242 Function day_left_in_qtr(as_of_date date) return varchar2 is
1243 l_day_left varchar2(240);
1244 l_qtr varchar2(240);
1245 l_yr varchar2(240);
1246 l_label varchar2(4000);
1247 l_as_of_date date;
1248 begin
1249 l_as_of_date := trunc(as_of_date);
1250
1251 select to_char(ent_cqtr_end(l_as_of_date)-l_as_of_date)
1252 into l_day_left
1253 from dual;
1254
1255 select sequence
1256 into l_qtr
1257 from fii_time_ent_qtr
1258 where l_as_of_date between start_date and end_date;
1259
1260 select to_char(to_date(ent_year_id,'yyyy'),'yy')
1261 into l_yr
1262 from fii_time_ent_year
1263 where l_as_of_date between start_date and end_date;
1264
1265 --R12 Uptake on new messages (VKAZHIPU)
1266
1267 if (l_day_left = 1) then
1268 fnd_message.set_name('FII', 'FII_DATE_LABEL');
1269 else
1270 fnd_message.set_name('FII', 'FII_DATE_LABEL_PLURAL');
1271 end if;
1272
1273 fnd_message.set_token('DAYS',l_day_left,FALSE);
1274 fnd_message.set_token('QUARTER_NUMBER',l_qtr,FALSE);
1275 fnd_message.set_token('YEAR_NUMBER',l_yr,FALSE);
1276 l_label := fnd_message.get;
1277
1278 return l_label;
1279 end;
1280
1281 -- -------------------------------------------------------------------
1282 -- Name: ent_lysper_id
1283 -- Desc: Returns ID of same enterprise period in previous year.
1284 -- Info is cached after initial access
1285 -- Output: Last year same Enterprise period id
1286 -- Error: If any sql errors occurs, an exception is raised.
1287 -- --------------------------------------------------------------------
1288 Function ent_lysper_id(id number) return NUMBER is
1289 l_id number;
1290 l_curr_period number;
1291 l_curr_year number;
1292
1293 begin
1294
1295 select sequence, ent_year_id
1296 into l_curr_period, l_curr_year
1297 from fii_time_ent_period
1298 where ent_period_id=id;
1299
1300 select ent_period_id
1301 into l_id
1302 from fii_time_ent_period
1303 where sequence=l_curr_period
1304 and ent_year_id=l_curr_year-1;
1305
1306 return l_id;
1307 end;
1308
1309 --====================================================================--
1310
1311 -- -------------------------------------------------------------------
1312 -- Name: check_missing_date
1313 -- Desc: Check if there is any missing date in the time dimension
1314 -- for the range (via two input parameters): (from_date, to_date).
1315 -- It returns 1 boolean: has_missing_date
1316 -- It also prints out message in the output file if there's gap;
1317 -- in particular, the minimum and maximum of these missing dates.
1318 -- This procedure requires the setup of files and directory
1319 -- for fnd_file.
1320 -- Output: true/false
1321 -- Error: If any sql error occurs, will report it to the log file;
1322 -- and an exception is raised.
1323 -- --------------------------------------------------------------------
1324 procedure check_missing_date (p_from_date IN date,
1325 p_to_date IN date,
1326 p_has_missing_date OUT NOCOPY boolean) IS
1327 l_day date;
1328 l_has_missing_date boolean;
1329 l_min_missing_date date;
1330 l_max_missing_date date;
1331 l_count number;
1332 l_from_date date;
1333 l_to_date date;
1334
1335 begin
1336 l_from_date := trunc(p_from_date);
1337 l_to_date := trunc(p_to_date);
1338
1339 fnd_message.set_name('FII','FII_DBI_CHECK_TIME_GAPS');
1340 fnd_message.set_token('FROM_DATE', fnd_date.date_to_displayDT(l_from_date));
1341 fnd_message.set_token('TO_DATE', fnd_date.date_to_displayDT(l_to_date));
1342 fnd_file.put_line(FND_FILE.OUTPUT, fnd_message.get);
1343 fnd_file.put_line(FND_FILE.OUTPUT, '');
1344
1345 -- handle NULL dates input
1346 if (p_from_date is NULL) then
1347 raise_application_error(-20000,'Error in input for check_missing_date: ' ||
1348 'from_date can not be NULL');
1349 end if;
1350
1351 if (p_to_date is NULL) then
1352 raise_application_error(-20001,'Error in input for check_missing_date: ' ||
1353 'to_date can not be NULL');
1354 end if;
1355 -----
1356
1357 -- ---------------------------------------------------------
1358 -- Variable initialization
1359 -- ---------------------------------------------------------
1360 l_day := l_from_date;
1361 l_has_missing_date := false;
1362 l_min_missing_date := l_to_date; -- set to maximum first
1363 l_max_missing_date := l_from_date; -- set to minimum first
1364
1368 select count(*) into l_count
1365 while l_day <= p_to_date loop
1366
1367 -- check if the current day is loaded
1369 from fii_time_day
1370 where report_date = trunc(l_day);
1371
1372 if l_count = 0 then --this day is not loaded
1373 l_has_missing_date := true;
1374 l_min_missing_date := least (l_min_missing_date, l_day);
1375 l_max_missing_date := greatest (l_max_missing_date, l_day);
1376 end if;
1377
1378 -- move to the next day
1379 l_day := l_day+1;
1380 end loop;
1381
1382 p_has_missing_date := l_has_missing_date;
1383 if p_has_missing_date then
1384 fnd_message.set_name('FII','FII_DBI_TIME_HAS_GAPS');
1385 fnd_message.set_token('FROM_DATE',
1386 fnd_date.date_to_displayDT(l_min_missing_date));
1387 fnd_message.set_token('TO_DATE',
1388 fnd_date.date_to_displayDT(l_max_missing_date));
1389 fnd_file.put_line(FND_FILE.OUTPUT, fnd_message.get);
1390 fnd_file.put_line(FND_FILE.OUTPUT, '');
1391 else
1392 fnd_message.set_name('FII','FII_DBI_NO_TIME_GAPS');
1393 fnd_file.put_line(FND_FILE.OUTPUT, fnd_message.get);
1394 fnd_file.put_line(FND_FILE.OUTPUT, '');
1395 end if;
1396
1397 exception
1398 when others then
1399 fnd_file.put_line(FND_FILE.LOG, 'Error occurred in procedure: check_missing_date...');
1400 fnd_file.put_line(FND_FILE.LOG, sqlcode || ' : ' || sqlerrm);
1401 raise;
1402 end check_missing_date;
1403
1404 -- -------------------------------------------------------------------
1405 -- Name: check_missing_date (overload version)
1406 -- Desc: Check if there is any missing date in the time dimension
1407 -- for the range (via two input parameters): (from_date, to_date).
1408 -- It returns three output parameters: has_missing_date,
1409 -- min_missing_date, max_missing_date.
1410 -- No log will be generated.
1411 -- Error: If any sql error occurs, will report it to the log file;
1412 -- and an exception is raised.
1413 -- --------------------------------------------------------------------
1414 Procedure check_missing_date (p_from_date IN date,
1415 p_to_date IN date,
1416 p_has_missing_date OUT NOCOPY boolean,
1417 p_min_missing_date OUT NOCOPY date,
1418 p_max_missing_date OUT NOCOPY date) IS
1419
1420 l_from_date date;
1421 l_to_date date;
1422 l_day date;
1423 l_has_missing_date boolean;
1424 l_min_missing_date date;
1425 l_max_missing_date date;
1426 l_count number;
1427
1428
1429 begin
1430
1431 -- handle NULL dates input
1432 if (p_from_date is NULL) then
1433 raise_application_error(-20000,'Error in input for check_missing_date: ' ||
1434 'from_date can not be NULL');
1435 end if;
1436
1437 if (p_to_date is NULL) then
1438 raise_application_error(-20001,'Error in input for check_missing_date: ' ||
1439 'to_date can not be NULL');
1440 end if;
1441 -----
1442
1443 -- ---------------------------------------------------------
1444 -- Variable initialization
1445 -- ---------------------------------------------------------
1446 --l_from_date := p_from_date;
1447 --l_to_date := p_to_date;
1448 l_from_date := trunc(p_from_date);
1449 l_to_date := trunc(p_to_date);
1450 l_day := l_from_date;
1451 l_has_missing_date := false;
1452 l_min_missing_date := trunc(p_to_date); -- set to maximum first
1453 l_max_missing_date := trunc(p_from_date); -- set to minimum first
1454
1455 while l_day <= l_to_date loop
1456
1457 -- check if the current day is loaded
1458 select count(*) into l_count
1459 from fii_time_day
1460 where report_date = trunc(l_day);
1461
1462 if l_count = 0 then --this day is not loaded
1463 l_has_missing_date := true;
1464 l_min_missing_date := least (l_min_missing_date, l_day);
1465 l_max_missing_date := greatest (l_max_missing_date, l_day);
1466 end if;
1467
1468 -- move to the next day
1469 l_day := l_day+1;
1470 end loop;
1471
1472 p_has_missing_date := l_has_missing_date;
1473 if p_has_missing_date then
1474 p_min_missing_date := l_min_missing_date;
1475 p_max_missing_date := l_max_missing_date;
1476 end if;
1477
1478 exception
1479 when others then
1480 fnd_file.put_line(FND_FILE.LOG, 'Error occurred in procedure: check_missing_date...');
1481 fnd_file.put_line(FND_FILE.LOG, sqlcode || ' : ' || sqlerrm);
1482 raise;
1483 end check_missing_date;
1484
1485 -----------------------------------------------------------------------
1486 ----- Following 5 APIs are from PJI team
1487
1488 -- -------------------------------------------------------------------
1489 -- Name: cal_sd_lyr_end
1490 -- Desc: Returns the same day last year, count from year end date in the
1491 -- financial calendar. Info is cached after initial access
1492 -- Output: Same date(as the pass in date) in previous financial year.
1493 -- Error: If any sql errors occurs, an exception is raised.
1494 -- --------------------------------------------------------------------
1495 Function cal_sd_lyr_end(as_of_date date, p_calendar_id number) return DATE is
1496 l_date date;
1497 l_timespan number;
1498 l_curr_year number;
1499 l_as_of_date date;
1500 begin
1501 l_as_of_date := trunc(as_of_date);
1502 select end_date-l_as_of_date, sequence
1503 into l_timespan, l_curr_year
1504 from fii_time_cal_year
1505 where l_as_of_date between start_date and end_date
1509 into l_date
1506 and calendar_id=p_calendar_id;
1507
1508 select end_date-l_timespan
1510 from fii_time_cal_year
1511 where sequence=l_curr_year-1
1512 and calendar_id=p_calendar_id;
1513
1514 return l_date;
1515 end;
1516
1517 -- -------------------------------------------------------------------
1518 -- Name: cal_sd_lysqtr_end
1519 -- Desc: Returns the same day last year same quarter, count from quarter end
1520 -- date in the financial calendar. Info is cached after initial access
1521 -- Output: Same date(as the pass in date) same quarter in previous financial year.
1522 -- Error: If any sql errors occurs, an exception is raised.
1523 -- --------------------------------------------------------------------
1524 Function cal_sd_lysqtr_end(as_of_date date, p_calendar_id number) return DATE is
1525 l_date date;
1526 l_timespan number;
1527 l_curr_qtr number;
1528 l_curr_year number;
1529 l_as_of_date date;
1530 begin
1531 l_as_of_date := trunc(as_of_date);
1532 select end_date-l_as_of_date, sequence, cal_year_id
1533 into l_timespan, l_curr_qtr, l_curr_year
1534 from fii_time_cal_qtr
1535 where l_as_of_date between start_date and end_date
1536 and calendar_id=p_calendar_id;
1537
1538 select greatest(start_date, end_date-l_timespan)
1539 into l_date
1540 from fii_time_cal_qtr
1541 where sequence=l_curr_qtr
1542 and cal_year_id=l_curr_year-1
1543 and calendar_id=p_calendar_id;
1544
1545 return l_date;
1546 end;
1547
1548 -- -------------------------------------------------------------------
1549 -- Name: cal_sd_lysper_end
1550 -- Desc: Returns the same day last year same period, count from period end
1551 -- date in the financial calendar. Info is cached after initial access
1552 -- Output: Same date(as the pass in date) same period in previous financial year.
1553 -- Error: If any sql errors occurs, an exception is raised.
1554 -- --------------------------------------------------------------------
1555 Function cal_sd_lysper_end(as_of_date date, p_calendar_id number) return DATE is
1556 l_date date;
1557 l_timespan number;
1558 l_curr_period number;
1559 l_curr_year number;
1560 l_as_of_date date;
1561 begin
1562 l_as_of_date := trunc(as_of_date);
1563 select p.end_date-l_as_of_date, p.sequence, q.cal_year_id
1564 into l_timespan, l_curr_period, l_curr_year
1565 from fii_time_cal_period p, fii_time_cal_qtr q
1566 where p.cal_qtr_id=q.cal_qtr_id
1567 and q.calendar_id=p_calendar_id
1568 and p.calendar_id=q.calendar_id
1569 and l_as_of_date between p.start_date and p.end_date;
1570
1571 select greatest(p.start_date, p.end_date-l_timespan)
1572 into l_date
1573 from fii_time_cal_period p, fii_time_cal_qtr q
1574 where p.cal_qtr_id=q.cal_qtr_id
1575 and q.calendar_id=p_calendar_id
1576 and p.calendar_id=q.calendar_id
1577 and p.sequence=l_curr_period
1578 and q.cal_year_id=l_curr_year-1;
1579
1580 return l_date;
1581 end;
1582
1583 -- -------------------------------------------------------------------
1584 -- Name: cal_sd_pqtr_end
1585 -- Desc: Returns the same day prior quarter, count from quarter end date
1586 -- in the financial calendar. Info is cached after initial access
1587 -- Output: Same date(as the pass in date) in prior financial quarter.
1588 -- Error: If any sql errors occurs, an exception is raised.
1589 -- --------------------------------------------------------------------
1590 Function cal_sd_pqtr_end(as_of_date date, p_calendar_id number) return DATE is
1591 l_date date;
1592 l_timespan number;
1593 l_curr_qtr number;
1594 l_curr_year number;
1595 l_as_of_date date;
1596 begin
1597 l_as_of_date := trunc(as_of_date);
1598 select end_date-l_as_of_date, sequence, cal_year_id
1599 into l_timespan, l_curr_qtr, l_curr_year
1600 from fii_time_cal_qtr
1601 where l_as_of_date between start_date and end_date
1602 and calendar_id=p_calendar_id;
1603
1604 if l_curr_qtr=1 then
1605 select end_date-l_timespan
1606 into l_date
1607 from fii_time_cal_qtr
1608 where sequence=4
1609 and calendar_id=p_calendar_id
1610 and cal_year_id=l_curr_year-1;
1611 else
1612 select end_date-l_timespan
1613 into l_date
1614 from fii_time_cal_qtr
1615 where sequence=l_curr_qtr-1
1616 and calendar_id=p_calendar_id
1617 and cal_year_id=l_curr_year;
1618 end if;
1619
1620 return l_date;
1621 end;
1622
1623 -- -------------------------------------------------------------------
1624 -- Name: cal_sd_pper_end
1625 -- Desc: Returns the same day prior period, count from period end date
1626 -- in the financial calendar. Info is cached after initial access
1627 -- Output: Same date(as the pass in date) in prior financial period.
1628 -- Error: If any sql errors occurs, an exception is raised.
1629 -- --------------------------------------------------------------------
1630 Function cal_sd_pper_end(as_of_date date, p_calendar_id number) return DATE is
1631 l_date date;
1632 l_timespan number;
1633 l_curr_period number;
1634 l_curr_year number;
1635 l_as_of_date date;
1636 begin
1637 l_as_of_date := trunc(as_of_date);
1638 select p.end_date-l_as_of_date, p.sequence -1 , q.cal_year_id
1639 into l_timespan, l_curr_period, l_curr_year
1640 from fii_time_cal_period p, fii_time_cal_qtr q
1641 where p.cal_qtr_id=q.cal_qtr_id
1642 and q.calendar_id=p_calendar_id
1643 and p.calendar_id=q.calendar_id
1644 and l_as_of_date between p.start_date and p.end_date;
1645
1646 if l_curr_period=1 then
1647 l_curr_year:=l_curr_year-1;
1648
1649 select count(cal_period_id)
1653 and q.calendar_id=p_calendar_id
1650 into l_curr_period
1651 from fii_time_cal_period p, fii_time_cal_qtr q
1652 where p.cal_qtr_id=q.cal_qtr_id
1654 and p.calendar_id=q.calendar_id
1655 and q.cal_year_id=l_curr_year;
1656 end if;
1657
1658 select p.end_date-l_timespan
1659 into l_date
1660 from fii_time_cal_period p, fii_time_cal_qtr q
1661 where p.cal_qtr_id=q.cal_qtr_id
1662 and p.sequence=l_curr_period
1663 and q.calendar_id=p_calendar_id
1664 and p.calendar_id=q.calendar_id
1665 and q.cal_year_id=l_curr_year;
1666
1667 return l_date;
1668 end;
1669
1670 ------------------------------------------------------------------
1671
1672 -- -------------------------------------------------------------------
1673 -- Name: ent_rolling_start_date
1674 -- Desc: Returns the start date of the first rolling period/quarter/year in
1675 -- enterprise calendar. Info is cached after initial access
1676 -- Output: Start date of the first rolling period/quarter/year in enteprise calendar.
1677 -- Error: If any sql errors occurs, an exception is raised.
1678 -- --------------------------------------------------------------------
1679 Function ent_rolling_start_date(as_of_date date, period_type varchar2) return DATE is
1680 l_date date;
1681 l_curr_year number;
1682 l_as_of_date date;
1683 begin
1684 l_as_of_date := trunc(as_of_date);
1685 if(period_type = 'FII_TIME_ENT_YEAR') then
1686 select sequence
1687 into l_curr_year
1688 from fii_time_ent_year
1689 where l_as_of_date between start_date and end_date;
1690
1691 select min(start_date)
1692 into l_date
1693 from fii_time_ent_year
1694 where sequence>=l_curr_year-3;
1695 end if;
1696
1697 if(period_type = 'FII_TIME_ENT_QTR') then
1698 select min(start_date)
1699 into l_date
1700 from
1701 (select *
1702 from fii_time_ent_qtr
1703 where start_date <= l_as_of_date
1704 order by start_date desc)
1705 where rownum < 5;
1706 end if;
1707
1708 if(period_type = 'FII_TIME_ENT_PERIOD') then
1709 select min(start_date)
1710 into l_date
1711 from
1712 (select *
1713 from fii_time_ent_period
1714 where start_date <= l_as_of_date
1715 order by start_date desc)
1716 where rownum < 14;
1717 -- Bug 4002034
1718 end if;
1719
1720 if(period_type = 'FII_TIME_WEEK') then
1721 select start_date-7*12
1722 into l_date
1723 from fii_time_week
1724 where l_as_of_date between start_date and end_date;
1725 end if;
1726
1727 return l_date;
1728
1729 EXCEPTION
1730 WHEN NO_DATA_FOUND THEN
1731 return bis_common_parameters.get_global_start_date;
1732 end;
1733
1734 -- -------------------------------------------------------------------
1735 -- Name: next_period_end_date
1736 -- Desc: Returns the end date of the next week/period/quarter/year.
1737 -- Info is cached after initial access
1738 -- Output: End date of the next week/period/quarter/year.
1739 -- Error: If any sql errors occurs, an exception is raised.
1740 -- --------------------------------------------------------------------
1741 Function next_period_end_date(as_of_date date, period_type varchar2) return DATE is
1742 l_date date;
1743 l_as_of_date date;
1744 l_max_sequence number;
1745 begin
1746 l_as_of_date := trunc(as_of_date);
1747 if(period_type = 'FII_TIME_ENT_YEAR') then
1748 select end_date
1749 into l_date
1750 from fii_time_ent_year
1751 where sequence =
1752 (select sequence + 1
1753 from fii_time_ent_year
1754 where l_as_of_date between start_date and end_date);
1755 end if;
1756
1757 if(period_type = 'FII_TIME_ENT_QTR') then
1758 select end_date
1759 into l_date
1760 from fii_time_ent_qtr next,
1761 (select sequence, ent_year_id
1762 from fii_time_ent_qtr
1763 where l_as_of_date between start_date and end_date) curr
1764 where next.sequence = decode(curr.sequence, 4, 1, curr.sequence + 1)
1765 and next.ent_year_id = decode(curr.sequence, 4, curr.ent_year_id+1, curr.ent_year_id);
1766 end if;
1767
1768 if(period_type = 'FII_TIME_ENT_PERIOD') then
1769 -- Bug 4002034
1770 select max(sequence) into l_max_sequence
1771 from fii_time_ent_period
1772 where ent_year_id = (select ent_year_id
1773 from fii_time_ent_period
1774 where l_as_of_date between start_date and end_date);
1775
1776 select end_date
1777 into l_date
1778 from fii_time_ent_period next,
1779 (select sequence, ent_year_id
1780 from fii_time_ent_period
1781 where l_as_of_date between start_date and end_date) curr
1782 where next.sequence = decode(curr.sequence, l_max_sequence, 1, curr.sequence + 1)
1783 and next.ent_year_id = decode(curr.sequence, l_max_sequence, curr.ent_year_id+1, curr.ent_year_id);
1784 end if;
1785
1786 if(period_type = 'FII_TIME_WEEK') then
1787 select end_date+7
1788 into l_date
1789 from fii_time_week
1790 where l_as_of_date between start_date and end_date;
1791 end if;
1792
1793 return l_date;
1794
1795 EXCEPTION
1796 WHEN NO_DATA_FOUND THEN
1797 -- Bug fix 4696282: Changed to return 12/31/9999 instead of max. end date
1798 -- return global_end_date;
1799 return TO_DATE('12/31/9999', 'MM/DD/YYYY');
1800 end;
1801
1802 end;
[Home] [Help]