[Home] [Help]
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
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
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:
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:
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
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:
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
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:
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
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
1051: where w.period445_id=p.period445_id
1052: and w.sequence=l_curr_week
1053: and p.year445_id=l_curr_year-1;
1054:
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
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
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
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