40:
41:
42: CURSOR C1(l_start_date DATE) IS
43: SELECT end_date
44: FROM pa_fcst_periods_tmp
45: WHERE start_date >= l_start_date
46: order by start_date;
47:
48:
171: pa_fcst_global.SetPeriodSetName;
172:
173: SELECT period_name, start_date, end_date
174: INTO l_start_period, l_start_date, l_end_date
175: FROM pa_fcst_periods_tmp_v
176: WHERE period_type = pa_fcst_global.Global_period_type
177: AND trunc(sysdate) between start_date and end_date
178: AND to_char(period_year) = to_char(sysdate,'YYYY');
179:
187: begin
188:
189: SELECT period_name, start_date, end_date
190: INTO l_start_period, l_start_date, l_end_date
191: FROM pa_fcst_periods_tmp_v
192: WHERE period_type = pa_fcst_global.Global_period_type
193: and start_date =
194: ( SELECT max(start_date) from pa_fcst_periods_tmp_v
195: WHERE period_type = pa_fcst_global.Global_period_type
190: INTO l_start_period, l_start_date, l_end_date
191: FROM pa_fcst_periods_tmp_v
192: WHERE period_type = pa_fcst_global.Global_period_type
193: and start_date =
194: ( SELECT max(start_date) from pa_fcst_periods_tmp_v
195: WHERE period_type = pa_fcst_global.Global_period_type
196: AND start_date < sysdate
197: );
198:
409:
410:
411: SELECT period_name
412: INTO x_start_period_name
413: FROM pa_fcst_periods_tmp_v
414: WHERE period_type = l_period_type
415: AND trunc(sysdate) between start_date and end_date
416: AND to_char(period_year) = to_char(sysdate,'YYYY');
417:
421: begin
422:
423: SELECT period_name
424: INTO x_start_period_name
425: FROM pa_fcst_periods_tmp_v
426: WHERE period_type = pa_fcst_global.Global_period_type
427: and start_date =
428: ( SELECT max(start_date) from pa_fcst_periods_tmp_v
429: WHERE period_type = pa_fcst_global.Global_period_type
424: INTO x_start_period_name
425: FROM pa_fcst_periods_tmp_v
426: WHERE period_type = pa_fcst_global.Global_period_type
427: and start_date =
428: ( SELECT max(start_date) from pa_fcst_periods_tmp_v
429: WHERE period_type = pa_fcst_global.Global_period_type
430: AND start_date < sysdate
431: );
432: /* NPE Changes Begin - Added displaying error messages for missing calendar */
516:
517: CURSOR C1(l_period_type VARCHAR2,l_start_date DATE) IS
518: -- Bug 4874283 - perf changes - remove trunc so index U2 is used
519: SELECT end_date
520: FROM pa_fcst_periods_tmp_v
521: WHERE period_type = l_period_type
522: -- AND trunc(start_date) >= trunc(l_start_date) -- 4874283
523: AND start_date >= trunc(l_start_date) -- 4874283
524: order by start_date;
600: End_Date
601: INTO
602: l_start_date,
603: l_end_date
604: FROM pa_fcst_periods_tmp_v
605: WHERE period_name = p_start_period
606: AND period_type = l_period_type;
607:
608:
672:
673: CURSOR C1(l_period_type VARCHAR2,l_start_date DATE) IS
674: -- Bug 4874283 - perf changes - remove trunc so index U2 is used
675: SELECT end_date
676: FROM pa_fcst_periods_tmp_v
677: WHERE period_type = l_period_type
678: -- AND trunc(start_date) >= trunc(l_start_date) -- 4874283
679: AND start_date >= trunc(l_start_date) -- 4874283
680: order by start_date;
711: SELECT min(p1.start_date),
712: max(p1.end_date)
713: INTO l_pl_start_date,
714: l_pl_end_date
715: FROM pa_fcst_periods_tmp_v p1,
716: pa_projects_all p2
717: WHERE p1.period_type = 'GL'
718: /* Commented the AND condition and modified for bug #3620818
719: AND p1.start_date between p2.start_date and
729: SELECT min(start_date),
730: max(end_date)
731: INTO l_pl_start_date,
732: l_pl_end_date
733: FROM pa_fcst_periods_tmp_v
734: WHERE period_type = 'PA'
735: AND (to_char(period_year) = to_char(sysdate,'YYYY')
736: OR start_date between add_months(sysdate,-3) and
737: add_months(sysdate,6));
776: End_Date
777: INTO
778: l_start_date,
779: l_end_date
780: FROM pa_fcst_periods_tmp_v
781: WHERE period_name = p_start_period
782: AND period_type = l_period_type;
783:
784: OPEN C1(l_period_type, l_start_date);
1183: l_period_type VARCHAR2(2):= FND_PROFILE.VALUE('PA_FORECASTING_PERIOD_TYPE');
1184:
1185: BEGIN
1186: BEGIN
1187: DELETE pa_fcst_periods_tmp;
1188: EXCEPTION
1189: WHEN NO_DATA_FOUND THEN
1190: NULL;
1191: WHEN OTHERS THEN
1193:
1194: END;
1195:
1196: IF l_period_type ='GL' THEN
1197: INSERT INTO pa_fcst_periods_tmp
1198: (PERIOD_NAME,
1199: START_DATE,
1200: END_DATE)
1201: SELECT distinct
1226: -- R12 MOAC changes and bug 4874283 perf fix.
1227: -- See previous version for old insert statement - I have deleted
1228: -- it so that it doesn't show up in grep for impact.
1229:
1230: INSERT INTO pa_fcst_periods_tmp
1231: (PERIOD_NAME,
1232: START_DATE,
1233: END_DATE)
1234: SELECT PER.PERIOD_NAME,