[Home] [Help]
PACKAGE BODY: APPS.PA_PRJ_PERIOD_PROFILE_UTILS
Source
1 PACKAGE BODY Pa_Prj_Period_Profile_Utils as
2 /* $Header: PAPJPDPB.pls 120.1.12010000.5 2008/11/18 18:00:05 bifernan ship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5 g_module_name VARCHAR2(100) := 'pa.plsql.pa_prj_period_profile_utils';
6
7 Procedure Maintain_Prj_Period_Profile(
8 p_project_id IN NUMBER,
9 p_period_profile_type IN VARCHAR2,
10 p_plan_period_type IN VARCHAR2,
11 p_period_set_name IN VARCHAR2,
12 p_gl_period_type IN VARCHAR2,
13 p_pa_period_type IN VARCHAR2,
14 p_start_date IN DATE,
15 px_end_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
16 px_period_profile_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
17 p_commit_flag IN VARCHAR2,
18 px_number_of_periods IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
19 p_debug_mode IN VARCHAR2,
20 p_add_msg_in_stack IN VARCHAR2,
21 x_plan_start_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
22 x_plan_end_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
23 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
24 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
25 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
26 IS
27 l_cnt number;
28 l_period_name_tab PA_PLSQL_DATATYPES.Char50TabTyp;
29 l_st_dt_tab PA_PLSQL_DATATYPES.DateTabTyp;
30 l_end_dt_tab PA_PLSQL_DATATYPES.DateTabTyp;
31 l_period_type Gl_Periods.Period_Type%TYPE;
32 l_tab_count number(10);
33 l_temp number(10);
34 CURSOR c1(c_period_set_name varchar2,
35 c_period_type varchar2,
36 c_st_dt date ,
37 c_end_dt date) is
38 SELECT G.period_name,
39 G.start_date,
40 G.end_date FROM
41 Gl_Periods G
42 WHERE
43 G.start_date >= c_st_dt and
44 G.end_date <= c_end_dt and
45 G.period_set_name = c_period_set_name and
46 G.period_type = c_period_type and
47 G.adjustment_period_flag = 'N'
48 ORDER BY G.start_date;
49 l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
50 l_created_by NUMBER := FND_GLOBAL.USER_ID;
51 l_creation_date DATE := SYSDATE;
52 l_last_update_date DATE := l_creation_date;
53 l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
54 l_program_application_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
55 l_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
56 l_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
57
58 l_plan_end_period VARCHAR2(30);
59 BEGIN
60 /* If all the periods needs to be populated for the project,
61 then p_end_date parameter should be passed as NULL, and the
62 API will calculate the end date by adding 10 yrs.
63 If less than 52 periods ( max pds ) to be populated then
64 the p_end_date should be passed with the actual date. */
65
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68 PA_DEBUG.Set_Curr_Function( p_function => 'Maintain_Prj_Period_Profile',
69 p_debug_mode => p_debug_mode );
70
71 IF px_end_date IS NULL THEN
72 IF P_PA_DEBUG_MODE = 'Y' THEN
73 PA_DEBUG.g_err_stage := 'end date is null, setting the end dt';
74 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
75 END IF;
76 px_end_date := ADD_MONTHS(p_start_date,120);
77 END IF;
78 IF p_plan_period_type = 'PA' THEN
79 l_period_type := p_pa_period_type;
80 ELSIF p_plan_period_type = 'GL' THEN
81 l_period_type := p_gl_period_type;
82 END IF;
83 OPEN c1(p_period_set_name,
84 l_period_type,
85 p_start_date,
86 px_end_date);
87
88 FETCH c1 BULK COLLECT INTO
89 l_period_name_tab,
90 l_st_dt_tab,
91 l_end_dt_tab LIMIT 52;
92 IF c1%notfound THEN
93 null;
94 END IF;
95 CLOSE c1;
96
97 IF P_PA_DEBUG_MODE = 'Y' THEN
98 PA_DEBUG.g_err_stage := 'after fetching pds';
99 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
100 END IF;
101 l_plan_end_period := NULL;
102 l_tab_count := l_period_name_tab.count;
103 px_number_of_periods := l_tab_count;
104 IF l_tab_count = 0 THEN
105 IF P_PA_DEBUG_MODE = 'Y' THEN
106 PA_DEBUG.g_err_stage := 'table count is zero for the given dt range, returning ';
107 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
108 END IF;
109 x_return_status := FND_API.G_RET_STS_ERROR;
110 IF p_add_msg_in_stack = 'Y' THEN
111 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
112 p_msg_name => 'PA_FP_INVALID_DATE_RANGE');
113 ELSE
114 x_msg_data := 'PA_FP_INVALID_DATE_RANGE';
115 END IF;
116 PA_DEBUG.Reset_Curr_Function;
117 RETURN;
118 END IF;
119
120 l_plan_end_period := l_period_name_tab(l_tab_count);
121 l_temp := l_tab_count + 1;
122 x_plan_start_date := l_st_dt_tab(1);
123 x_plan_end_date := l_end_dt_tab(l_tab_count);
124
125 WHILE l_temp <= 52 LOOP
126 l_period_name_tab(l_temp) := null;
127 l_st_dt_tab(l_temp) := null;
128 l_end_dt_tab(l_temp) := null;
129
130 l_temp := l_temp + 1;
131 END LOOP;
132
133 l_temp := 0;
134 IF px_period_profile_id IS NOT NULL THEN
135 UPDATE pa_proj_period_profiles SET
136 number_of_periods = l_tab_count,
137 period_name1 = l_period_name_tab(1),
138 period_name2 = l_period_name_tab(2),
139 period_name3 = l_period_name_tab(3),
140 period_name4 = l_period_name_tab(4),
141 period_name5 = l_period_name_tab(5),
142 period_name6 = l_period_name_tab(6),
143 period_name7 = l_period_name_tab(7),
144 period_name8 = l_period_name_tab(8),
145 period_name9 = l_period_name_tab(9),
146 period_name10 = l_period_name_tab(10),
147 period_name11 = l_period_name_tab(11),
148 period_name12 = l_period_name_tab(12),
149 period_name13 = l_period_name_tab(13),
150 period_name14 = l_period_name_tab(14),
151 period_name15 = l_period_name_tab(15),
152 period_name16 = l_period_name_tab(16),
153 period_name17 = l_period_name_tab(17),
154 period_name18 = l_period_name_tab(18),
155 period_name19 = l_period_name_tab(19),
156 period_name20 = l_period_name_tab(20),
157 period_name21 = l_period_name_tab(21),
158 period_name22 = l_period_name_tab(22),
159 period_name23 = l_period_name_tab(23),
160 period_name24 = l_period_name_tab(24),
161 period_name25 = l_period_name_tab(25),
162 period_name26 = l_period_name_tab(26),
163 period_name27 = l_period_name_tab(27),
164 period_name28 = l_period_name_tab(28),
165 period_name29 = l_period_name_tab(29),
166 period_name30 = l_period_name_tab(30),
167 period_name31 = l_period_name_tab(31),
168 period_name32 = l_period_name_tab(32),
169 period_name33 = l_period_name_tab(33),
170 period_name34 = l_period_name_tab(34),
171 period_name35 = l_period_name_tab(35),
172 period_name36 = l_period_name_tab(36),
173 period_name37 = l_period_name_tab(37),
174 period_name38 = l_period_name_tab(38),
175 period_name39 = l_period_name_tab(39),
176 period_name40 = l_period_name_tab(40),
177 period_name41 = l_period_name_tab(41),
178 period_name42 = l_period_name_tab(42),
179 period_name43 = l_period_name_tab(43),
180 period_name44 = l_period_name_tab(44),
181 period_name45 = l_period_name_tab(45),
182 period_name46 = l_period_name_tab(46),
183 period_name47 = l_period_name_tab(47),
184 period_name48 = l_period_name_tab(48),
185 period_name49 = l_period_name_tab(49),
186 period_name50 = l_period_name_tab(50),
187 period_name51 = l_period_name_tab(51),
188 period_name52 = l_period_name_tab(52),
189 period1_start_date = l_st_dt_tab(1),
190 period2_start_date = l_st_dt_tab(2),
191 period3_start_date = l_st_dt_tab(3),
192 period4_start_date = l_st_dt_tab(4),
193 period5_start_date = l_st_dt_tab(5),
194 period6_start_date = l_st_dt_tab(6),
195 period7_start_date = l_st_dt_tab(7),
196 period8_start_date = l_st_dt_tab(8),
197 period9_start_date = l_st_dt_tab(9),
198 period10_start_date = l_st_dt_tab(10),
199 period11_start_date = l_st_dt_tab(11),
200 period12_start_date = l_st_dt_tab(12),
201 period13_start_date = l_st_dt_tab(13),
202 period14_start_date = l_st_dt_tab(14),
203 period15_start_date = l_st_dt_tab(15),
204 period16_start_date = l_st_dt_tab(16),
205 period17_start_date = l_st_dt_tab(17),
206 period18_start_date = l_st_dt_tab(18),
207 period19_start_date = l_st_dt_tab(19),
208 period20_start_date = l_st_dt_tab(20),
209 period21_start_date = l_st_dt_tab(21),
210 period22_start_date = l_st_dt_tab(22),
211 period23_start_date = l_st_dt_tab(23),
212 period24_start_date = l_st_dt_tab(24),
213 period25_start_date = l_st_dt_tab(25),
214 period26_start_date = l_st_dt_tab(26),
215 period27_start_date = l_st_dt_tab(27),
216 period28_start_date = l_st_dt_tab(28),
217 period29_start_date = l_st_dt_tab(29),
218 period30_start_date = l_st_dt_tab(30),
219 period31_start_date = l_st_dt_tab(31),
220 period32_start_date = l_st_dt_tab(32),
221 period33_start_date = l_st_dt_tab(33),
222 period34_start_date = l_st_dt_tab(34),
223 period35_start_date = l_st_dt_tab(35),
224 period36_start_date = l_st_dt_tab(36),
225 period37_start_date = l_st_dt_tab(37),
226 period38_start_date = l_st_dt_tab(38),
227 period39_start_date = l_st_dt_tab(39),
228 period40_start_date = l_st_dt_tab(40),
229 period41_start_date = l_st_dt_tab(41),
230 period42_start_date = l_st_dt_tab(42),
231 period43_start_date = l_st_dt_tab(43),
232 period44_start_date = l_st_dt_tab(44),
233 period45_start_date = l_st_dt_tab(45),
234 period46_start_date = l_st_dt_tab(46),
235 period47_start_date = l_st_dt_tab(47),
236 period48_start_date = l_st_dt_tab(48),
237 period49_start_date = l_st_dt_tab(49),
238 period50_start_date = l_st_dt_tab(50),
239 period51_start_date = l_st_dt_tab(51),
240 period52_start_date = l_st_dt_tab(52),
241 period1_end_date = l_end_dt_tab(1),
242 period2_end_date = l_end_dt_tab(2),
243 period3_end_date = l_end_dt_tab(3),
244 period4_end_date = l_end_dt_tab(4),
245 period5_end_date = l_end_dt_tab(5),
246 period6_end_date = l_end_dt_tab(6),
247 period7_end_date = l_end_dt_tab(7),
248 period8_end_date = l_end_dt_tab(8),
249 period9_end_date = l_end_dt_tab(9),
250 period10_end_date = l_end_dt_tab(10),
251 period11_end_date = l_end_dt_tab(11),
252 period12_end_date = l_end_dt_tab(12),
253 period13_end_date = l_end_dt_tab(13),
254 period14_end_date = l_end_dt_tab(14),
255 period15_end_date = l_end_dt_tab(15),
256 period16_end_date = l_end_dt_tab(16),
257 period17_end_date = l_end_dt_tab(17),
258 period18_end_date = l_end_dt_tab(18),
259 period19_end_date = l_end_dt_tab(19),
260 period20_end_date = l_end_dt_tab(20),
261 period21_end_date = l_end_dt_tab(21),
262 period22_end_date = l_end_dt_tab(22),
263 period23_end_date = l_end_dt_tab(23),
264 period24_end_date = l_end_dt_tab(24),
265 period25_end_date = l_end_dt_tab(25),
266 period26_end_date = l_end_dt_tab(26),
267 period27_end_date = l_end_dt_tab(27),
268 period28_end_date = l_end_dt_tab(28),
269 period29_end_date = l_end_dt_tab(29),
270 period30_end_date = l_end_dt_tab(30),
271 period31_end_date = l_end_dt_tab(31),
272 period32_end_date = l_end_dt_tab(32),
273 period33_end_date = l_end_dt_tab(33),
274 period34_end_date = l_end_dt_tab(34),
275 period35_end_date = l_end_dt_tab(35),
276 period36_end_date = l_end_dt_tab(36),
277 period37_end_date = l_end_dt_tab(37),
278 period38_end_date = l_end_dt_tab(38),
279 period39_end_date = l_end_dt_tab(39),
280 period40_end_date = l_end_dt_tab(40),
281 period41_end_date = l_end_dt_tab(41),
282 period42_end_date = l_end_dt_tab(42),
283 period43_end_date = l_end_dt_tab(43),
284 period44_end_date = l_end_dt_tab(44),
285 period45_end_date = l_end_dt_tab(45),
286 period46_end_date = l_end_dt_tab(46),
287 period47_end_date = l_end_dt_tab(47),
288 period48_end_date = l_end_dt_tab(48),
289 period49_end_date = l_end_dt_tab(49),
290 period50_end_date = l_end_dt_tab(50),
291 period51_end_date = l_end_dt_tab(51),
292 period52_end_date = l_end_dt_tab(52),
293 LAST_UPDATE_LOGIN = l_last_update_login,
294 LAST_UPDATED_BY = l_last_updated_by,
295 LAST_UPDATE_DATE = l_last_update_date,
296 PROFILE_END_PERIOD_NAME = l_plan_end_period
297 WHERE period_profile_id = px_period_profile_id;
298
299 IF SQL%ROWCOUNT = 0 THEN
300 IF P_PA_DEBUG_MODE = 'Y' THEN
301 PA_DEBUG.g_err_stage := 'no record updated for the gievn id';
302 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
303 END IF;
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 IF p_add_msg_in_stack = 'Y' THEN
306 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
307 p_msg_name => 'PA_FP_INVALID_PRJ_PROFILE');
308 ELSE
309 x_msg_data := 'PA_FP_INVALID_PRJ_PROFILE';
310 END IF;
311 PA_DEBUG.Reset_Curr_Function;
312 RETURN;
313 END IF;
314 ELSE
315 INSERT INTO pa_proj_period_profiles (
316 CREATION_DATE ,
317 CREATED_BY ,
318 LAST_UPDATE_LOGIN ,
319 LAST_UPDATED_BY ,
320 LAST_UPDATE_DATE ,
321 period_profile_id ,
322 period_profile_type ,
323 period_set_name ,
324 gl_period_type ,
325 plan_period_type,
326 project_id ,
327 period_name1 ,
328 period_name2 ,
329 period_name3 ,
330 period_name4 ,
331 period_name5 ,
332 period_name6 ,
333 period_name7 ,
334 period_name8 ,
335 period_name9 ,
336 period_name10 ,
337 period_name11 ,
338 period_name12 ,
339 period_name13 ,
340 period_name14 ,
341 period_name15 ,
342 period_name16 ,
343 period_name17 ,
344 period_name18 ,
345 period_name19 ,
346 period_name20 ,
347 period_name21 ,
348 period_name22 ,
349 period_name23 ,
350 period_name24 ,
351 period_name25 ,
352 period_name26 ,
353 period_name27 ,
354 period_name28 ,
355 period_name29 ,
356 period_name30 ,
357 period_name31 ,
358 period_name32 ,
359 period_name33 ,
360 period_name34 ,
361 period_name35 ,
362 period_name36 ,
363 period_name37 ,
364 period_name38 ,
365 period_name39 ,
366 period_name40 ,
367 period_name41 ,
368 period_name42 ,
369 period_name43 ,
370 period_name44 ,
371 period_name45 ,
372 period_name46 ,
373 period_name47 ,
374 period_name48 ,
375 period_name49 ,
376 period_name50 ,
377 period_name51 ,
378 period_name52 ,
379 period1_start_date ,
380 period2_start_date ,
381 period3_start_date ,
382 period4_start_date ,
383 period5_start_date ,
384 period6_start_date ,
385 period7_start_date ,
386 period8_start_date ,
387 period9_start_date ,
388 period10_start_date ,
389 period11_start_date ,
390 period12_start_date ,
391 period13_start_date ,
392 period14_start_date ,
393 period15_start_date ,
394 period16_start_date ,
395 period17_start_date ,
396 period18_start_date ,
397 period19_start_date ,
398 period20_start_date ,
399 period21_start_date ,
400 period22_start_date ,
401 period23_start_date ,
402 period24_start_date ,
403 period25_start_date ,
404 period26_start_date ,
405 period27_start_date ,
406 period28_start_date ,
407 period29_start_date ,
408 period30_start_date ,
409 period31_start_date ,
410 period32_start_date ,
411 period33_start_date ,
412 period34_start_date ,
413 period35_start_date ,
414 period36_start_date ,
415 period37_start_date ,
416 period38_start_date ,
417 period39_start_date ,
418 period40_start_date ,
419 period41_start_date ,
420 period42_start_date ,
421 period43_start_date ,
422 period44_start_date ,
423 period45_start_date ,
424 period46_start_date ,
425 period47_start_date ,
426 period48_start_date ,
427 period49_start_date ,
428 period50_start_date ,
429 period51_start_date ,
430 period52_start_date ,
431 period1_end_date ,
432 period2_end_date ,
433 period3_end_date ,
434 period4_end_date ,
435 period5_end_date ,
436 period6_end_date ,
437 period7_end_date ,
438 period8_end_date ,
439 period9_end_date ,
440 period10_end_date ,
441 period11_end_date ,
442 period12_end_date ,
443 period13_end_date ,
444 period14_end_date ,
445 period15_end_date ,
446 period16_end_date ,
447 period17_end_date ,
448 period18_end_date ,
449 period19_end_date ,
450 period20_end_date ,
451 period21_end_date ,
452 period22_end_date ,
453 period23_end_date ,
454 period24_end_date ,
455 period25_end_date ,
456 period26_end_date ,
457 period27_end_date ,
458 period28_end_date ,
459 period29_end_date ,
460 period30_end_date ,
461 period31_end_date ,
462 period32_end_date ,
463 period33_end_date ,
464 period34_end_date ,
465 period35_end_date ,
466 period36_end_date ,
467 period37_end_date ,
468 period38_end_date ,
469 period39_end_date ,
470 period40_end_date ,
471 period41_end_date ,
472 period42_end_date ,
473 period43_end_date ,
474 period44_end_date ,
475 period45_end_date ,
476 period46_end_date ,
477 period47_end_date ,
478 period48_end_date ,
479 period49_end_date ,
480 period50_end_date ,
481 period51_end_date ,
482 period52_end_date ,
483 number_of_periods,
484 PROFILE_END_PERIOD_NAME )
485 VALUES (
486 l_creation_date ,
487 l_created_by ,
488 l_last_update_login ,
489 l_last_updated_by ,
490 l_last_update_date ,
491 PA_PROJ_PERIOD_PROFILES_S.nextval,
492 p_period_profile_type,
493 p_period_set_name,
494 p_gl_period_type,
495 p_plan_period_type,
496 p_project_id,
497 l_period_name_tab(1),
498 l_period_name_tab(2),
499 l_period_name_tab(3),
500 l_period_name_tab(4),
501 l_period_name_tab(5),
502 l_period_name_tab(6),
503 l_period_name_tab(7),
504 l_period_name_tab(8),
505 l_period_name_tab(9),
506 l_period_name_tab(10),
507 l_period_name_tab(11),
508 l_period_name_tab(12),
509 l_period_name_tab(13),
510 l_period_name_tab(14),
511 l_period_name_tab(15),
512 l_period_name_tab(16),
513 l_period_name_tab(17),
514 l_period_name_tab(18),
515 l_period_name_tab(19),
516 l_period_name_tab(20),
517 l_period_name_tab(21),
518 l_period_name_tab(22),
519 l_period_name_tab(23),
520 l_period_name_tab(24),
521 l_period_name_tab(25),
522 l_period_name_tab(26),
523 l_period_name_tab(27),
524 l_period_name_tab(28),
525 l_period_name_tab(29),
526 l_period_name_tab(30),
527 l_period_name_tab(31),
528 l_period_name_tab(32),
529 l_period_name_tab(33),
530 l_period_name_tab(34),
531 l_period_name_tab(35),
532 l_period_name_tab(36),
533 l_period_name_tab(37),
534 l_period_name_tab(38),
535 l_period_name_tab(39),
536 l_period_name_tab(40),
537 l_period_name_tab(41),
538 l_period_name_tab(42),
539 l_period_name_tab(43),
540 l_period_name_tab(44),
541 l_period_name_tab(45),
542 l_period_name_tab(46),
543 l_period_name_tab(47),
544 l_period_name_tab(48),
545 l_period_name_tab(49),
546 l_period_name_tab(50),
547 l_period_name_tab(51),
548 l_period_name_tab(52),
549 l_st_dt_tab(1),
550 l_st_dt_tab(2),
551 l_st_dt_tab(3),
552 l_st_dt_tab(4),
553 l_st_dt_tab(5),
554 l_st_dt_tab(6),
555 l_st_dt_tab(7),
556 l_st_dt_tab(8),
557 l_st_dt_tab(9),
558 l_st_dt_tab(10),
559 l_st_dt_tab(11),
560 l_st_dt_tab(12),
561 l_st_dt_tab(13),
562 l_st_dt_tab(14),
563 l_st_dt_tab(15),
564 l_st_dt_tab(16),
565 l_st_dt_tab(17),
566 l_st_dt_tab(18),
567 l_st_dt_tab(19),
568 l_st_dt_tab(20),
569 l_st_dt_tab(21),
570 l_st_dt_tab(22),
571 l_st_dt_tab(23),
572 l_st_dt_tab(24),
573 l_st_dt_tab(25),
574 l_st_dt_tab(26),
575 l_st_dt_tab(27),
576 l_st_dt_tab(28),
577 l_st_dt_tab(29),
578 l_st_dt_tab(30),
579 l_st_dt_tab(31),
580 l_st_dt_tab(32),
581 l_st_dt_tab(33),
582 l_st_dt_tab(34),
583 l_st_dt_tab(35),
584 l_st_dt_tab(36),
585 l_st_dt_tab(37),
586 l_st_dt_tab(38),
587 l_st_dt_tab(39),
588 l_st_dt_tab(40),
589 l_st_dt_tab(41),
590 l_st_dt_tab(42),
591 l_st_dt_tab(43),
592 l_st_dt_tab(44),
593 l_st_dt_tab(45),
594 l_st_dt_tab(46),
595 l_st_dt_tab(47),
596 l_st_dt_tab(48),
597 l_st_dt_tab(49),
598 l_st_dt_tab(50),
599 l_st_dt_tab(51),
600 l_st_dt_tab(52),
601 l_end_dt_tab(1),
602 l_end_dt_tab(2),
603 l_end_dt_tab(3),
604 l_end_dt_tab(4),
605 l_end_dt_tab(5),
606 l_end_dt_tab(6),
607 l_end_dt_tab(7),
608 l_end_dt_tab(8),
609 l_end_dt_tab(9),
610 l_end_dt_tab(10),
611 l_end_dt_tab(11),
612 l_end_dt_tab(12),
613 l_end_dt_tab(13),
614 l_end_dt_tab(14),
615 l_end_dt_tab(15),
616 l_end_dt_tab(16),
617 l_end_dt_tab(17),
618 l_end_dt_tab(18),
619 l_end_dt_tab(19),
620 l_end_dt_tab(20),
621 l_end_dt_tab(21),
622 l_end_dt_tab(22),
623 l_end_dt_tab(23),
624 l_end_dt_tab(24),
625 l_end_dt_tab(25),
626 l_end_dt_tab(26),
627 l_end_dt_tab(27),
628 l_end_dt_tab(28),
629 l_end_dt_tab(29),
630 l_end_dt_tab(30),
631 l_end_dt_tab(31),
632 l_end_dt_tab(32),
633 l_end_dt_tab(33),
634 l_end_dt_tab(34),
635 l_end_dt_tab(35),
636 l_end_dt_tab(36),
637 l_end_dt_tab(37),
638 l_end_dt_tab(38),
639 l_end_dt_tab(39),
640 l_end_dt_tab(40),
641 l_end_dt_tab(41),
642 l_end_dt_tab(42),
643 l_end_dt_tab(43),
644 l_end_dt_tab(44),
645 l_end_dt_tab(45),
646 l_end_dt_tab(46),
647 l_end_dt_tab(47),
648 l_end_dt_tab(48),
649 l_end_dt_tab(49),
650 l_end_dt_tab(50),
651 l_end_dt_tab(51),
652 l_end_dt_tab(52),
653 l_tab_count ,
654 l_plan_end_period ) returning period_profile_id into
655 px_period_profile_id;
656
657 IF P_PA_DEBUG_MODE = 'Y' THEN
658 PA_DEBUG.g_err_stage := 'after inserting pd profile';
659 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
660 END IF;
661
662 UPDATE PA_PROJ_PERIOD_PROFILES SET CURRENT_FLAG = 'N' WHERE
663 Period_Profile_Type = p_period_profile_type AND
664 Plan_Period_Type = p_plan_period_type AND
665 Project_Id = p_project_id AND
666 Current_Flag = 'Y';
667
668 UPDATE PA_PROJ_PERIOD_PROFILES SET CURRENT_FLAG = 'Y' WHERE
669 Period_Profile_Id = px_period_profile_id;
670
671 END IF;
672 IF NVL(p_commit_flag,'N') = 'Y' THEN
673 COMMIT;
674 END IF;
675 PA_DEBUG.Reset_Curr_Function;
676 RETURN;
677 EXCEPTION
678 WHEN OTHERS THEN
679 RAISE;
680 END Maintain_Prj_Period_Profile;
681
682 Procedure Get_Prj_Period_Profile_Dtls(
683 p_period_profile_id IN NUMBER,
684 p_debug_mode IN VARCHAR2,
685 p_add_msg_in_stack IN VARCHAR2,
686 x_period_profile_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
687 x_plan_period_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
688 x_period_set_name OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
689 x_gl_period_type OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
690 x_plan_start_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
691 x_plan_end_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
692 x_number_of_periods OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
693 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
694 x_msg_data OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
695 l_cursor_id integer;
696 l_plan_end_date date;
697 l_stmt varchar2(1000);
698 l_column_name varchar2(100);
699 l_dummy integer;
700 BEGIN
701 x_return_status := FND_API.G_RET_STS_SUCCESS;
702 PA_DEBUG.Set_Curr_Function( p_function => 'Get_Prj_Period_Profile_Dtls',
703 p_debug_mode => p_debug_mode );
704 BEGIN
705 SELECT Period_Profile_Type,
706 Plan_Period_Type,
707 Period_Set_Name,
708 Gl_Period_Type,
709 Number_Of_Periods,
710 Period1_Start_Date INTO
711 x_period_profile_type,
712 x_plan_period_type,
713 x_period_set_name,
714 x_gl_period_type,
715 x_number_of_periods,
716 x_plan_start_date FROM Pa_Proj_Period_Profiles
717 WHERE Period_Profile_Id = NVL(p_period_profile_id,0);
718
719 l_column_name := 'PERIOD'||LTRIM(TO_CHAR(x_number_of_periods))||'_END_DATE';
720 l_cursor_id := dbms_sql.open_cursor;
721 l_stmt := 'select ' ||l_column_name
722 || ' from pa_proj_period_profiles where ' ||
723 ' period_profile_id = '||to_char(p_period_profile_id);
724
725 dbms_sql.parse(l_cursor_id,l_stmt,dbms_sql.native);
726 dbms_sql.define_column(l_cursor_id,1,l_plan_end_date);
727
728 l_dummy := dbms_sql.execute_and_fetch(l_cursor_id);
729
730
731 dbms_sql.column_value(l_cursor_id,1,l_plan_end_date);
732 dbms_sql.close_cursor(l_cursor_id);
733 x_plan_end_date := l_plan_end_date;
734
735 EXCEPTION
736 WHEN NO_DATA_FOUND THEN
737 IF P_PA_DEBUG_MODE = 'Y' THEN
738 PA_DEBUG.g_err_stage := 'no data found for the given pd profile id';
739 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
740 END IF;
741 x_return_status := FND_API.G_RET_STS_ERROR;
742 IF p_add_msg_in_stack = 'Y' THEN
743 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
744 p_msg_name => 'PA_FP_INVALID_PRJ_PROFILE');
745 ELSE
746 x_msg_data := 'PA_FP_INVALID_PRJ_PROFILE';
747 END IF;
748 END;
749 PA_DEBUG.Reset_Curr_Function;
750 RETURN;
751
752 END Get_Prj_Period_Profile_Dtls;
753
754 PROCEDURE Get_Date_Details(
755 p_project_id IN NUMBER,
756 p_period_name IN VARCHAR2,
757 p_plan_period_type IN VARCHAR2,
758 x_start_date OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
759 x_end_date OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
760 l_start_Date VARCHAR2(30);
761 l_end_date VARCHAR2(30);
762 BEGIN
763 l_start_Date := NULL;
764 l_end_date := NULL;
765 IF p_plan_period_type = 'GL' THEN
766 BEGIN
767 SELECT TO_CHAR(gl.start_date,'rrrr/mm/dd'),
768 TO_CHAR(gl.end_date,'rrrr/mm/dd') INTO l_start_date,l_end_Date FROM
769 Gl_Periods gl, Pa_Implementations_All imp ,Pa_Projects_All p ,
770 Gl_Sets_Of_Books sob WHERE
771 p.Project_Id = p_project_id AND
772 nvl(p.Org_Id,-99) = NVL(imp.Org_Id,-99) AND
773 imp.Set_Of_Books_Id = sob.Set_Of_Books_Id AND
774 gl.Period_Set_Name = imp.Period_Set_Name AND
775 gl.Period_Type = sob.Accounted_Period_Type AND
776 gl.Period_Name = p_period_name AND
777 gl.Adjustment_Period_Flag = 'N' ;
778 EXCEPTION
779 WHEN NO_DATA_FOUND THEN
780 NULL;
781 END;
782 ELSIF p_plan_period_type = 'PA' THEN
783 BEGIN
784 SELECT TO_CHAR(gl.start_date,'rrrr/mm/dd'),
785 TO_CHAR(gl.end_date,'rrrr/mm/dd') INTO l_start_date,l_end_Date FROM
786 Gl_Periods gl, Pa_Implementations_All imp ,Pa_Projects_All p WHERE
787 p.Project_Id = p_project_id AND
788 nvl(p.Org_Id,-99) = nvl(imp.Org_Id,-99) AND
789 gl.Period_Set_Name = imp.Period_Set_Name AND
790 gl.Period_Type = imp.Pa_Period_Type AND
791 gl.Period_Name = p_period_name AND
792 gl.Adjustment_Period_Flag = 'N' ;
793 EXCEPTION
794 WHEN NO_DATA_FOUND THEN
795 NULL;
796 END;
797 END IF;
798 x_start_date := l_start_date;
799
800 x_end_Date := l_end_Date;
801
802 END;
803
804
805 --
806 --Name: Maintain_Prj_Profile_wrp
807 --Type: Procedure
808 --
809 --Description:
810 --Called subprograms: none
811 --
812 --
813 --
814 --History:
815 -- 14-NOV-2001 SManivannan - Created
816 --
817 -- 17-MAR-03 jwhite - Bug 2589885
818 -- Add logic and edits to enforce entry of GL/PA periods
819 -- within project duration.
820 --
821
822 Procedure Maintain_Prj_Profile_wrp(
823 p_project_id IN NUMBER,
824 p_period_profile_type IN VARCHAR2,
825 p_pa_start_date IN DATE,
826 px_pa_end_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
827 px_pa_period_profile_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
828 p_commit_flag IN VARCHAR2,
829 px_pa_number_of_periods IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
830 p_debug_mode IN VARCHAR2,
831 p_add_msg_in_stack IN VARCHAR2,
832 x_pa_plan_start_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
833 x_pa_plan_end_date OUT NOCOPY DATE, --File.Sql.39 bug 4440895
834 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
835 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
836 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
837 p_pa_start_period IN VARCHAR2,
838 p_pa_end_period IN VARCHAR2,
839 p_gl_start_period IN VARCHAR2,
840 p_gl_end_period IN VARCHAR2,
841 p_gl_start_date IN DATE,
842 px_gl_end_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
843 px_gl_period_profile_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
844 px_gl_number_of_periods IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
845 p_old_pa_profile_id IN NUMBER ,
846 p_old_gl_profile_id IN NUMBER ,
847 p_refresh_option_code IN VARCHAR2,
848 x_conc_req_id OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
849 l_para_plan_start_Date DATE;
850 l_para_plan_end_Date DATE;
851 l_pa_start_Date DATE;
852 l_pa_end_date DATE;
853 l_gl_start_Date DATE;
854 l_gl_end_date DATE;
855 l_pa_start_Date1 DATE;
856 l_pa_end_date1 DATE;
857 l_gl_start_Date1 DATE;
858 l_gl_end_date1 DATE;
859 l_gl_period_set_name Gl_Periods.Period_Set_Name%TYPE;
860 l_gl_period_type Gl_Periods.Period_Type%TYPE;
861 l_pa_period_type Gl_Periods.Period_Type%TYPE;
862 l_pa_plan_start_date DATE;
863 l_pa_plan_end_date DATE;
864 l_gl_plan_start_date DATE;
865 l_gl_plan_end_date DATE;
866 l_plan_period_type Pa_Proj_Period_Profiles.Plan_Period_Type%TYPE;
867 l_para_start_Date DATE;
868 l_para_end_Date DATE;
869 l_para_period_profile_id NUMBER;
870 l_para_number_of_periods NUMBER;
871 l_old_pa_profile_id NUMBER;
872 l_old_gl_profile_id NUMBER;
873 l_return_status VARCHAR2(30);
874 l_pa_return_status VARCHAR2(30);
875 l_gl_return_status VARCHAR2(30);
876 l_old_upd_profile_id NUMBER;
877 l_valid_pa_period_flag VARCHAR2(1);
878 l_valid_gl_period_flag VARCHAR2(1);
879 l_call_profile_pa_flag VARCHAR2(1);
880 l_call_profile_gl_flag VARCHAR2(1);
881 l_old_pa_start_period Pa_Proj_Period_Profiles.Period_Name1%TYPE;
882 l_old_gl_start_period Pa_Proj_Period_Profiles.Period_Name1%TYPE;
883 l_old_pa_end_period Pa_Proj_Period_Profiles.Period_Name1%TYPE;
884 l_old_gl_end_period Pa_Proj_Period_Profiles.Period_Name1%TYPE;
885 l_periods_count NUMBER;
886 l_msg_count NUMBER :=0;
887 l_msg_data VARCHAR2(2000);
888 l_data VARCHAR2(2000);
889 l_msg_index_out NUMBER;
890 l_rpt_request_id NUMBER;
891 l_debug_mode VARCHAR2(30);
892 l_conc_pa_profile_id NUMBER;
893 l_conc_gl_profile_id NUMBER;
894 l_bv_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
895 l_locked_person_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
896 l_plan_proc_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
897
898
899 -- Bug 2589885, 17-MAR-2003, jwhite, Begin: --------------------
900
901 l_prj_start_date DATE := NULL;
902 l_prj_completion_date DATE := NULL;
903
904 l_prj_PAper_start_date DATE := NULL;
905 l_prj_PAper_end_date DATE := NULL;
906 l_prj_GLper_start_date DATE := NULL;
907 l_prj_GLper_end_date DATE := NULL;
908
909 l_invalid_prj_dur_PA VARCHAR2(1) := 'N';
910 l_invalid_prj_dur_GL VARCHAR2(1) := 'N';
911
912
913 -- Bug 2589885, End: ---------------------------------------------
914
915 nc_pa_end_date DATE;
916 nc_pa_period_profile_id NUMBER;
917 nc_pa_number_of_periods NUMBER;
918 nc_gl_end_date DATE;
919 nc_gl_period_profile_id NUMBER;
920 nc_gl_number_of_periods NUMBER;
921
922 BEGIN
923 nc_pa_end_date := px_pa_end_date;
924 nc_pa_period_profile_id := px_pa_period_profile_id;
925 nc_pa_number_of_periods := px_pa_number_of_periods;
926 nc_gl_end_date := px_gl_end_date;
927 nc_gl_period_profile_id := px_gl_period_profile_id;
928 nc_gl_number_of_periods := px_gl_number_of_periods;
929
930 x_conc_req_id := '0';
931 FND_MSG_PUB.Initialize;
932 x_return_status := FND_API.G_RET_STS_SUCCESS;
933 l_gl_return_status := FND_API.G_RET_STS_SUCCESS;
934 l_pa_return_status := FND_API.G_RET_STS_SUCCESS;
935 x_msg_count := 0;
936 l_valid_pa_period_flag := 'Y';
937 l_valid_gl_period_flag := 'Y';
938 l_old_pa_profile_id := px_pa_period_profile_id;
939 l_old_gl_profile_id := px_gl_period_profile_id;
940 l_call_profile_pa_flag := 'Y';
941 l_call_profile_gl_flag := 'Y';
942
943 /* Bug 2689403 - Start of validations based on the bug */
944
945 /* Validating i/p start and end dates.
946 Either both should be null or both should be not null */
947
948 IF (p_pa_start_period IS NULL and p_pa_end_period IS NOT NULL) or
949 (p_pa_start_period IS NOT NULL and p_pa_end_period IS NULL) THEN
950 IF P_PA_DEBUG_MODE = 'Y' THEN
951 PA_DEBUG.g_err_stage := 'Only one of the pa start or end periods are not null..';
952 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
953 END IF;
954 x_return_status := FND_API.G_RET_STS_ERROR;
955 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
956 p_msg_name => 'PA_FP_ENTER_PA_PP_ST_END_DT');
957 END IF;
958
959 IF (p_gl_start_period IS NULL and p_gl_end_period IS NOT NULL) or
960 (p_gl_start_period IS NOT NULL and p_gl_end_period IS NULL) THEN
961 IF P_PA_DEBUG_MODE = 'Y' THEN
962 PA_DEBUG.g_err_stage := 'Only one of the gl start or end periods are not null..';
963 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
964 END IF;
965 x_return_status := FND_API.G_RET_STS_ERROR;
966 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
967 p_msg_name => 'PA_FP_ENTER_GL_PP_ST_END_DT');
968 END IF;
969
970 /* If start and end periods are null then there should not have been an existing period profile */
971
972 IF (p_pa_start_period IS NULL and p_pa_end_period IS NULL) THEN
973 IF P_PA_DEBUG_MODE = 'Y' THEN
974 PA_DEBUG.g_err_stage := 'Both pa start and end periods are null...';
975 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
976 END IF;
977 l_valid_pa_period_flag := 'N';
978 IF p_old_pa_profile_id IS NOT NULL THEN
979 x_return_status := FND_API.G_RET_STS_ERROR;
980 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
981 p_msg_name => 'PA_FP_ENTER_PA_PERIODS');
982 ELSE
983 l_call_profile_pa_flag := 'N';
984 END IF;
985 END IF;
986
987 IF (p_gl_start_period IS NULL and p_gl_end_period IS NULL) THEN
988 IF P_PA_DEBUG_MODE = 'Y' THEN
989 PA_DEBUG.g_err_stage := 'Both gl start and end periods are null...';
990 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
991 END IF;
992 l_valid_gl_period_flag := 'N';
993 IF p_old_gl_profile_id IS NOT NULL THEN
994 x_return_status := FND_API.G_RET_STS_ERROR;
995 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
996 p_msg_name => 'PA_FP_ENTER_GL_PERIODS');
997 ELSE
998 l_call_profile_gl_flag := 'N';
999 END IF;
1000 END IF;
1001
1002 SELECT imp.Pa_Period_Type,
1003 imp.Period_Set_Name,
1004 sob.Accounted_Period_Type INTO
1005 l_pa_period_type,
1006 l_gl_period_set_name,
1007 l_gl_period_type FROM
1008 Pa_Implementations_All imp ,Pa_Projects_All p ,
1009 Gl_Sets_Of_Books sob WHERE
1010 p.Project_Id = p_project_id AND
1011 nvl(p.Org_Id,-99) = NVL(imp.Org_Id,-99) AND
1012 imp.Set_Of_Books_id = sob.Set_Of_Books_Id;
1013
1014 /* The following validations need to be done only if the start and end dates are not null
1015 and there was not validation failure before this. */
1016
1017 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1018
1019 IF l_valid_pa_period_flag = 'Y' THEN
1020
1021 BEGIN
1022 SELECT gl.Start_Date,gl.End_Date INTO l_pa_start_date,l_pa_end_Date FROM
1023 Gl_Periods gl WHERE
1024 gl.Period_Set_Name = l_gl_period_set_name AND
1025 gl.Period_Type = l_pa_period_type and
1026 gl.Period_Name = p_pa_start_period AND
1027 gl.Adjustment_Period_Flag = 'N' ;
1028 EXCEPTION
1029 WHEN NO_DATA_FOUND THEN
1030 x_return_status := FND_API.G_RET_STS_ERROR;
1031 l_valid_pa_period_flag := 'N';
1032 x_msg_count := x_msg_count + 1;
1033 l_call_profile_pa_flag := 'N';
1034 l_pa_return_status := FND_API.G_RET_STS_ERROR;
1035 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1036 p_msg_name => 'PA_FP_PP_INVALID_PERIOD',
1037 p_token1 => 'PERIOD',
1038 p_value1 => p_pa_start_period);
1039 END;
1040
1041 BEGIN
1042 SELECT gl.Start_Date,gl.End_Date INTO l_pa_start_date1,l_pa_end_Date1 FROM
1043 Gl_Periods gl WHERE
1044 gl.Period_Set_Name = l_gl_period_set_name AND
1045 gl.Period_Type = l_pa_period_type AND
1046 gl.Period_Name = p_pa_end_period AND
1047 gl.Adjustment_Period_Flag = 'N' ;
1048 EXCEPTION
1049 WHEN NO_DATA_FOUND THEN
1050 x_return_status := FND_API.G_RET_STS_ERROR;
1051 l_valid_pa_period_flag := 'N';
1052 x_msg_count := x_msg_count + 1;
1053 l_call_profile_pa_flag := 'N';
1054 l_pa_return_status := FND_API.G_RET_STS_ERROR;
1055 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1056 p_msg_name => 'PA_FP_PP_INVALID_PERIOD',
1057 p_token1 => 'PERIOD',
1058 p_value1 => p_pa_end_period);
1059 END;
1060
1061 END IF;
1062
1063 IF l_valid_gl_period_flag = 'Y' THEN
1064
1065 BEGIN
1066 SELECT gl.Start_Date,gl.End_Date INTO l_gl_start_date,l_gl_end_Date FROM
1067 Gl_Periods gl WHERE
1068 gl.Period_Set_Name = l_gl_period_set_name AND
1069 gl.Period_Type = l_gl_period_type AND
1070 gl.Period_Name = p_gl_start_period AND
1071 gl.Adjustment_Period_Flag = 'N' ;
1072 EXCEPTION
1073 WHEN NO_DATA_FOUND THEN
1074 x_return_status := FND_API.G_RET_STS_ERROR;
1075 x_msg_count := x_msg_count + 1;
1076 l_valid_gl_period_flag := 'N';
1077 l_call_profile_gl_flag := 'N';
1078 l_gl_return_status := FND_API.G_RET_STS_ERROR;
1079 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1080 p_msg_name => 'PA_FP_PP_INVALID_PERIOD',
1081 p_token1 => 'PERIOD',
1082 p_value1 => p_gl_start_period);
1083 END;
1084
1085 BEGIN
1086 SELECT gl.Start_Date,gl.End_Date INTO l_gl_start_date1,l_gl_end_Date1 FROM
1087 Gl_Periods gl WHERE
1088 gl.Period_Set_Name = l_gl_period_set_name AND
1089 gl.Period_Type = l_gl_period_type AND
1090 gl.Period_Name = p_gl_end_period AND
1091 gl.Adjustment_Period_Flag = 'N' ;
1092
1093 l_para_period_profile_id := px_gl_period_profile_id;
1094 l_para_number_of_periods := px_gl_number_of_periods;
1095 EXCEPTION
1096 WHEN NO_DATA_FOUND THEN
1097 x_return_status := FND_API.G_RET_STS_ERROR;
1098 l_valid_gl_period_flag := 'N';
1099 x_msg_count := x_msg_count + 1;
1100 l_call_profile_gl_flag := 'N';
1101 l_gl_return_status := FND_API.G_RET_STS_ERROR;
1102 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1103 p_msg_name => 'PA_FP_PP_INVALID_PERIOD',
1104 p_token1 => 'PERIOD',
1105 p_value1 => p_gl_end_period);
1106 END;
1107
1108 END IF;
1109
1110 IF l_valid_pa_period_flag = 'Y' THEN
1111 SELECT COUNT(*) INTO l_periods_count FROM Gl_Periods gl
1112 WHERE
1113 Start_Date BETWEEN l_pa_start_date AND l_pa_start_date1 AND
1114 gl.Period_Set_Name = l_gl_period_set_name AND
1115 gl.Period_Type = l_pa_period_type AND
1116 gl.Adjustment_Period_Flag = 'N' ;
1117 IF l_periods_count > 52 THEN
1118 x_return_status := FND_API.G_RET_STS_ERROR;
1119 x_msg_count := x_msg_count + 1;
1120 l_call_profile_pa_flag := 'N';
1121 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1122 p_msg_name => 'PA_FP_PP_EXCEED_MAX_PDS');
1123 END IF;
1124 IF l_pa_start_date > l_pa_start_date1 THEN
1125 x_return_status := FND_API.G_RET_STS_ERROR;
1126 x_msg_count := x_msg_count + 1;
1127 l_call_profile_pa_flag := 'N';
1128 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1129 p_msg_name => 'PA_FP_PP_INVALID_PERIOD_RANGE');
1130 END IF;
1131 END IF;
1132
1133 IF l_valid_gl_period_flag = 'Y' THEN
1134 SELECT COUNT(*) INTO l_periods_count FROM Gl_Periods gl
1135 WHERE
1136 Start_Date BETWEEN l_gl_start_date AND l_gl_start_date1 AND
1137 gl.Period_Set_Name = l_gl_period_set_name AND
1138 gl.Period_Type = l_gl_period_type AND
1139 gl.Adjustment_Period_Flag = 'N' ;
1140 IF l_periods_count > 52 THEN
1141 x_return_status := FND_API.G_RET_STS_ERROR;
1142 x_msg_count := x_msg_count + 1;
1143 l_call_profile_gl_flag := 'N';
1144 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1145 p_msg_name => 'PA_FP_PP_EXCEED_MAX_PDS_GL');
1146 END IF;
1147 IF l_gl_start_date > l_gl_start_date1 THEN
1148 x_return_status := FND_API.G_RET_STS_ERROR;
1149 x_msg_count := x_msg_count + 1;
1150 l_call_profile_gl_flag := 'N';
1151 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1152 p_msg_name => 'PA_FP_PP_INVALID_PD_RANGE_GL');
1153 END IF;
1154 END IF;
1155
1156
1157
1158 -- Bug 2589885, 17-MAR-2003, jwhite, Begin: --------------------
1159 --
1160 -- Edit to Enforce Entered Periods within Project Duration
1161
1162 -- All New Code for this Bug Fix --
1163
1164
1165 IF ( (l_valid_pa_period_flag = 'Y') OR (l_valid_gl_period_flag = 'Y') )
1166 THEN
1167
1168 -- Find Start and End Dates for Project, If ANy
1169
1170 SELECT start_Date
1171 , completion_date
1172 INTO l_prj_start_date
1173 , l_prj_completion_date
1174 FROM Pa_Projects_All
1175 WHERE Project_Id = p_project_id;
1176
1177
1178 -- PA Period Validation
1179 IF (l_valid_PA_period_flag = 'Y')
1180 THEN
1181
1182
1183 IF (l_prj_start_date IS NOT NULL)
1184 THEN
1185
1186 -- STARTING Period Edit ----------------------------------------------
1187
1188 -- Find the Corresponding PA Period for the Project START Date
1189 -- AND save the PA Period's START date for subsequent processing
1190
1191 -- Issue: If project start date does not have corresponding
1192 -- period in GL_Periods, use the project start date.
1193
1194 BEGIN
1195
1196 SELECT Start_Date
1197 INTO l_prj_PAper_start_date
1198 FROM Gl_Periods
1199 WHERE Period_Set_Name = l_gl_period_set_name
1200 AND Period_Type = l_PA_period_type
1201 AND Adjustment_Period_Flag = 'N'
1202 and l_prj_start_date between start_date and end_date;
1203
1204 EXCEPTION
1205 WHEN NO_DATA_FOUND THEN
1206 l_prj_PAper_start_date := l_prj_start_date;
1207
1208 END;
1209
1210
1211 -- IF the Start Date of the Entered PA Start Period is EARLIER than the Start Date
1212 -- of the PA Period Corresponding to the Start Date of the Project
1213 -- THEN Issue error message.
1214
1215
1216
1217 IF ( l_PA_START_date < l_prj_PAper_START_date)
1218 THEN
1219
1220 x_return_status := FND_API.G_RET_STS_ERROR;
1221 x_msg_count := x_msg_count + 1;
1222 l_call_profile_pa_flag := 'N';
1223 l_invalid_prj_dur_PA := 'Y';
1224 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1225 , p_msg_name => 'PA_FP_PP_INVALID_PRJ_DUR_PA');
1226 END IF;
1227
1228
1229
1230 END IF; -- l_prj_start_date IS NOT NULL
1231
1232
1233
1234
1235 -- ENDING Period Edit ----------------------------------------------
1236
1237 -- IF Project Completion Date Exists AND PA Periods Passed Previous Edit,
1238 -- THEN
1239 -- IF the End Date of the Entered PA End Period is LATER than the End Date
1240 -- of the PA Period Corresponding to the Completion Date of the Project
1241 -- THEN
1242 -- Issue error message.
1243
1244 IF ( (l_invalid_prj_dur_PA = 'N') AND (l_prj_completion_date IS NOT NULL) )
1245 THEN
1246
1247
1248 -- Find the Corresponding PA Period for the Project COMPLETION Date
1249 -- AND save the PA Period's END date for subsequent processing
1250
1251 -- Issue: If project completion date does not have corresponding
1252 -- period in GL_Periods, use the completion date.
1253
1254 BEGIN
1255
1256 SELECT End_Date
1257 INTO l_prj_PAper_end_date
1258 FROM Gl_Periods
1259 WHERE Period_Set_Name = l_gl_period_set_name
1260 AND Period_Type = l_PA_period_type
1261 AND Adjustment_Period_Flag = 'N'
1262 and l_prj_completion_date between start_date and end_date;
1263
1264
1265 EXCEPTION
1266 WHEN NO_DATA_FOUND THEN
1267 l_prj_PAper_END_date := l_prj_completion_date;
1268
1269 END;
1270
1271
1272 -- IF the END Date of the Entered PA Ending Period is LATER than the END Date
1273 -- of the PA Period Corresponding to the COMPLETION Date of the Project
1274 -- THEN Issue error message.
1275
1276
1277
1278 IF ( l_PA_END_Date1 > l_prj_PAper_END_date)
1279 THEN
1280
1281 x_return_status := FND_API.G_RET_STS_ERROR;
1282 x_msg_count := x_msg_count + 1;
1283 l_call_profile_pa_flag := 'N';
1284 l_invalid_prj_dur_PA := 'Y';
1285 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1286 , p_msg_name => 'PA_FP_PP_INVALID_PRJ_DUR_PA');
1287
1288 END IF;
1289
1290 END IF; -- ENDING Period Edit
1291
1292 END IF; -- (l_valid_PA_period_flag = 'Y
1293
1294
1295 -- GL Period Validation
1296 IF (l_valid_GL_period_flag = 'Y')
1297 THEN
1298
1299
1300 IF (l_prj_start_date IS NOT NULL)
1301 THEN
1302
1303 -- STARTING Period Edit ----------------------------------------------
1304
1305 -- Find the Corresponding GL Period for the Project START Date
1306 -- AND save the GL Period's START date for subsequent processing
1307
1308
1309 -- Issue: If project start date does not have corresponding
1310 -- period in GL_Periods, use the project start date.
1311
1312 BEGIN
1313
1314 SELECT Start_Date
1315 INTO l_prj_GLper_start_date
1316 FROM Gl_Periods
1317 WHERE Period_Set_Name = l_gl_period_set_name
1318 AND Period_Type = l_GL_period_type
1319 AND Adjustment_Period_Flag = 'N'
1320 and l_prj_start_date between start_date and end_date;
1321
1322
1323 EXCEPTION
1324 WHEN NO_DATA_FOUND THEN
1325 l_prj_GLper_start_date := l_prj_start_date;
1326
1327 END;
1328
1329
1330 -- IF the Start Date of the Entered GL Start Period is EARLIER than the Start Date
1331 -- of the GL Period Corresponding to the Start Date of the Project
1332 -- THEN Issue error message.
1333
1334 IF ( l_GL_START_date < l_prj_GLper_START_date)
1335 THEN
1336
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338 x_msg_count := x_msg_count + 1;
1339 l_call_profile_gl_flag := 'N';
1340 l_invalid_prj_dur_GL := 'Y';
1341 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1342 , p_msg_name => 'PA_FP_PP_INVALID_PRJ_DUR_GL');
1343
1344 END IF;
1345
1346 END IF; -- l_prj_start_date IS NOT NULL
1347
1348
1349 -- ENDING Period Edit ----------------------------------------------
1350
1351 -- IF Project Completion Date Exists AND GL Periods Passed Previous Edit,
1352 -- THEN
1353 -- IF the End Date of the Entered GL End Period is LATER than the End Date
1354 -- of the GL Period Corresponding to the Completion Date of the Project
1355 -- THEN
1356 -- Issue error message.
1357
1358 IF ( (l_invalid_prj_dur_GL = 'N') AND (l_prj_completion_date IS NOT NULL) )
1359 THEN
1360
1361
1362 -- Find the Corresponding GL Period for the Project COMPLETION Date
1363 -- AND save the GL Period's END date for subsequent processing
1364
1365
1366 -- Issue: If project completion date does not have corresponding
1367 -- period in GL_Periods, use the completion date.
1368
1369 BEGIN
1370
1371 SELECT End_Date
1372 INTO l_prj_GLper_end_date
1373 FROM Gl_Periods
1374 WHERE Period_Set_Name = l_gl_period_set_name
1375 AND Period_Type = l_GL_period_type
1376 AND Adjustment_Period_Flag = 'N'
1377 and l_prj_completion_date between start_date and end_date;
1378
1379
1380 EXCEPTION
1381 WHEN NO_DATA_FOUND THEN
1382 l_prj_GLper_END_date := l_prj_completion_date;
1383
1384 END;
1385
1386 -- IF the END Date of the Entered GL Ending Period is LATER than the END Date
1387 -- of the GL Period Corresponding to the COMPLETION Date of the Project
1388 -- THEN Issue error message.
1389
1390 IF ( l_GL_END_Date1 > l_prj_GLper_END_date)
1391 THEN
1392
1393 x_return_status := FND_API.G_RET_STS_ERROR;
1394 x_msg_count := x_msg_count + 1;
1395 l_call_profile_gl_flag := 'N';
1396 l_invalid_prj_dur_GL := 'Y';
1397 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1398 , p_msg_name => 'PA_FP_PP_INVALID_PRJ_DUR_GL');
1399
1400 END IF;
1401
1402 END IF; -- ENDING Period Edit
1403
1404 END IF; -- GL Validation
1405
1406
1407 END IF; -- ( if pa/gl flags are Y ...
1408
1409
1410 -- Bug 2589885, End: ---------------------------------------------
1411
1412 END IF; -- x_return_status = FND_API.G_RET_STS_SUCCESS
1413
1414
1415 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1416 x_msg_count := fnd_msg_pub.count_msg;
1417 IF x_msg_count = 1 THEN
1418 PA_INTERFACE_UTILS_PUB.Get_Messages (
1419 p_encoded => FND_API.G_TRUE,
1420 p_msg_index => 1,
1421 p_msg_count => 1 ,
1422 p_msg_data => l_msg_data ,
1423 p_data => x_msg_data,
1424 p_msg_index_out => l_msg_index_out );
1425 END IF;
1426 RETURN;
1427 END IF;
1428
1429 IF p_old_pa_profile_id IS NOT NULL THEN
1430 SELECT PERIOD_NAME1 , PROFILE_END_PERIOD_NAME INTO
1431 l_old_pa_start_period,l_old_pa_end_period FROM
1432 Pa_Proj_Period_Profiles WHERE
1433 Period_Profile_Id = p_old_pa_profile_id;
1434 IF l_old_pa_start_period = p_pa_start_period AND
1435 l_old_pa_end_period = p_pa_end_period THEN
1436 l_call_profile_pa_flag := 'N';
1437 END IF;
1438 END IF;
1439
1440 IF p_old_gl_profile_id IS NOT NULL THEN
1441 SELECT PERIOD_NAME1 , PROFILE_END_PERIOD_NAME INTO
1442 l_old_gl_start_period,l_old_gl_end_period FROM
1443 Pa_Proj_Period_Profiles WHERE
1444 Period_Profile_Id = p_old_gl_profile_id;
1445 IF l_old_gl_start_period = p_gl_start_period AND
1446 l_old_gl_end_period = p_gl_end_period THEN
1447 l_call_profile_gl_flag := 'N';
1448 END IF;
1449 END IF;
1450
1451 IF l_call_profile_gl_flag = 'Y' THEN
1452 Pa_Prj_Period_Profile_Utils.Maintain_Prj_Period_Profile(
1453 p_project_id => p_project_id,
1454 p_period_profile_type => p_period_profile_type,
1455 p_plan_period_type => 'GL',
1456 p_period_set_name => l_gl_period_set_name,
1457 p_gl_period_type => l_gl_period_type,
1458 p_pa_period_type => l_pa_period_type,
1459 p_start_date => l_gl_start_Date,
1460 px_end_date => l_gl_end_date1,
1461 px_period_profile_id => px_gl_period_profile_id,
1462 p_commit_flag => p_commit_flag,
1463 px_number_of_periods => px_gl_number_of_periods,
1464 p_debug_mode => p_debug_mode,
1465 p_add_msg_in_stack => p_add_msg_in_stack,
1466 x_plan_start_date => l_para_plan_start_date,
1467 x_plan_end_date => l_para_plan_end_date,
1468 x_return_status => x_return_status,
1469 x_msg_count => x_msg_count,
1470 x_msg_data => x_msg_data );
1471 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1472 ROLLBACK;
1473 RETURN;
1474 END IF;
1475 END IF;
1476 IF l_call_profile_pa_flag = 'Y' THEN
1477 Pa_Prj_Period_Profile_Utils.Maintain_Prj_Period_Profile(
1478 p_project_id => p_project_id,
1479 p_period_profile_type => p_period_profile_type,
1480 p_plan_period_type => 'PA',
1481 p_period_set_name => l_gl_period_set_name,
1482 p_gl_period_type => l_gl_period_type,
1483 p_pa_period_type => l_pa_period_type,
1484 p_start_date => l_pa_start_Date,
1485 px_end_date => l_pa_end_date1,
1486 px_period_profile_id => px_pa_period_profile_id,
1487 p_commit_flag => p_commit_flag,
1488 px_number_of_periods => px_pa_number_of_periods,
1489 p_debug_mode => p_debug_mode,
1490 p_add_msg_in_stack => p_add_msg_in_stack,
1491 x_plan_start_date => l_para_plan_start_date,
1492 x_plan_end_date => l_para_plan_end_date,
1493 x_return_status => x_return_status,
1494 x_msg_count => x_msg_count,
1495 x_msg_data => x_msg_data );
1496 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1497 ROLLBACK;
1498 RETURN;
1499 END IF;
1500 END IF;
1501
1502 /* even if the concurrent program request fails, the modified period
1503 information should be saved */
1504
1505 IF l_call_profile_gl_flag = 'Y' OR
1506 l_call_profile_pa_flag = 'Y' THEN
1507 COMMIT;
1508 END IF;
1509
1510 IF p_refresh_option_code <> 'NONE' THEN
1511 /* calling the concurrent program */
1512 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
1513
1514 IF l_call_profile_gl_flag = 'Y' THEN
1515 l_conc_gl_profile_id := px_gl_period_profile_id;
1516 ELSE
1517 l_conc_gl_profile_id := p_old_gl_profile_id;
1518 END IF;
1519 IF l_call_profile_pa_flag = 'Y' THEN
1520 l_conc_pa_profile_id := px_pa_period_profile_id;
1521 ELSE
1522 l_conc_pa_profile_id := p_old_pa_profile_id;
1523 END IF;
1524
1525 l_rpt_request_id := FND_REQUEST.submit_request
1526 (application => 'PA',
1527 program => 'PAPDPROF',
1528 description => 'PRC: Refresh Plan Versions Period Profile',
1529 start_time => NULL,
1530 sub_request => false,
1531 argument1 => NULL,
1532 argument2 => NULL,
1533 argument3 => p_project_id,
1534 argument4 => p_refresh_option_code,
1535 argument5 => l_conc_gl_profile_id,
1536 argument6 => l_conc_pa_profile_id,
1537 argument7 => l_debug_mode );
1538
1539 IF l_rpt_request_id = 0 then
1540 IF P_PA_DEBUG_MODE = 'Y' THEN
1541 PA_DEBUG.g_err_stage := 'Error while submitting Report [PAFPEXRP]';
1542 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1543 END IF;
1544 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
1545 p_msg_name => 'PA_FP_PP_CONC_PGM_ERR');
1546 x_return_status := FND_API.G_RET_STS_ERROR;
1547 ROLLBACK;
1548 RETURN;
1549 ELSE
1550 /* added for locking the budget versions */
1551 l_bv_id_tab.DELETE;
1552 l_locked_person_id_tab.DELETE;
1553 l_plan_proc_code_tab.DELETE;
1554 IF (p_refresh_option_code = 'ALL') THEN
1555 SELECT budget_version_id,
1556 locked_by_person_id,
1557 plan_processing_Code
1558 BULK COLLECT INTO
1559 l_bv_id_tab,
1560 l_locked_person_id_tab,
1561 l_plan_proc_code_tab
1562 FROM
1563 Pa_budget_versions
1564 WHERE
1565 project_id = p_project_id
1566 AND period_profile_id IS NOT NULL;
1567 ELSIF (p_refresh_option_code = 'SELECTED') THEN
1568 SELECT budget_version_id,
1569 locked_by_person_id,
1570 plan_processing_Code
1571 BULK COLLECT INTO
1572 l_bv_id_tab,
1573 l_locked_person_id_tab,
1574 l_plan_proc_code_tab
1575 FROM
1576 Pa_budget_versions
1577 WHERE
1578 project_id = p_project_id
1579 AND period_profile_id IS NOT NULL
1580 AND
1581 (
1582 (current_working_flag = 'Y' AND budget_status_code IN ('W','S'))
1583 OR (current_flag = 'Y' AND budget_status_code = 'B')
1584 OR (current_original_flag = 'Y' AND budget_status_code = 'B')
1585 );
1586 END IF;
1587 /* FOR l_idx IN 1 .. l_bv_id_tab.COUNT LOOP
1588 IF l_locked_person_id_tab IS NULL THEN
1589 UPDATE
1590 END IF;
1591 END LOOP; */
1592 /* PPP - Period Profile refresh in Process */
1593 FORALL ii IN 1 .. l_bv_id_tab.COUNT
1594 UPDATE pa_budget_versions SET
1595 plan_processing_code = 'PPP',
1596 locked_by_person_id = -98,
1597 request_id = l_rpt_request_id,
1598 record_version_number = nvl(record_version_number,0) + 1,
1599 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1600 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1601 LAST_UPDATE_DATE = sysdate
1602 WHERE
1603 budget_version_id = l_bv_id_tab(ii) AND
1604 locked_by_person_id IS NULL;
1605 /* added for locking the budget versions */
1606 IF P_PA_DEBUG_MODE = 'Y' THEN
1607 PA_DEBUG.g_err_stage := 'Exception Report Request Id : ' ||
1608 LTRIM(TO_CHAR(l_rpt_request_id )) ;
1609 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
1610 p_write_file => 'OUT',
1611 p_write_mode => 1);
1612 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1613 END IF;
1614 END IF;
1615 x_conc_req_id := LTRIM(RTRIM(TO_CHAR(l_rpt_request_id)));
1616 END IF;
1617 IF x_return_Status = FND_API.G_RET_STS_SUCCESS THEN
1618 COMMIT;
1619 ELSE
1620 ROLLBACK;
1621 END IF;
1622
1623 EXCEPTION
1624 WHEN OTHERS THEN
1625 px_pa_end_date := nc_pa_end_date;
1626 px_pa_period_profile_id := nc_pa_period_profile_id;
1627 px_pa_number_of_periods := nc_pa_number_of_periods;
1628 px_gl_end_date := nc_gl_end_date;
1629 px_gl_period_profile_id := nc_gl_period_profile_id;
1630 px_gl_number_of_periods := nc_gl_number_of_periods;
1631 RAISE;
1632
1633 END Maintain_Prj_Profile_wrp;
1634
1635
1636 --###
1637 --Name: Get_Prj_Defaults
1638 --Type: Procedure
1639 --
1640 --Description:
1641 --Called subprograms: none
1642 --
1643 --
1644 --
1645 --History:
1646 -- 14-NOV-2001 SManivannan - Created
1647 --
1648 -- 17-MAR-03 jwhite - Bug 2589885
1649 -- Add two new parameters to Get_Prj_Defaults:
1650 -- - x_prj_start_date OUT VARCHAR2
1651 -- - x_prj_end_date OUT VARCHAR2
1652 -- Also, add code to populate the project
1653 -- start- and end-date parameters.
1654 --
1655 -- 03-JUN-03 vejayara - Bug2987076 - Start period info assigned as
1656 -- end period whenever start period is derived.
1657
1658
1659
1660 Procedure Get_Prj_Defaults( p_project_id IN NUMBER,
1661 p_info_flag IN VARCHAR2,
1662 p_create_defaults IN VARCHAR2, --Y or N
1663 x_gl_start_period OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1664 x_gl_end_period OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1665 x_gl_start_Date OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1666 x_pa_start_period OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1667 x_pa_end_period OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1668 x_pa_start_date OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1669 x_plan_version_exists_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1670 x_prj_start_date OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1671 x_prj_end_date OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1672 ) IS
1673
1674 l_prj_Start_date DATE;
1675 l_prj_completion_date DATE;
1676 l_prj_org_id NUMBER;
1677 l_count NUMBER := 0;
1678 l_profile_count NUMBER := 0;
1679
1680 l_dummy_pa_end_date DATE;
1681 l_dummy_gl_end_date DATE;
1682 l_pd_gl_end_date DATE;
1683 l_pd_pa_end_date DATE;
1684
1685 l_dummy_pa_period_profile_id pa_proj_period_profiles.period_profile_id%TYPE;
1686 l_dummy_gl_period_profile_id pa_proj_period_profiles.period_profile_id%TYPE;
1687 l_dummy_pa_number_of_periods NUMBER;
1688 l_dummy_gl_number_of_periods NUMBER;
1689 l_dummy_pa_plan_start_date DATE;
1690 l_dummy_pa_plan_end_date DATE;
1691 l_dummy_gl_plan_start_date DATE;
1692 l_dummy_gl_plan_end_date DATE;
1693 l_dummy_pa_period_profile_type pa_proj_period_profiles.period_profile_type%TYPE;
1694 l_dummy_gl_period_profile_type pa_proj_period_profiles.period_profile_type%TYPE;
1695
1696 /* Commenting for bug 7578853
1697 CURSOR end_period_cur(c_period_set_name varchar2,
1698 c_period_type varchar2,
1699 c_start_Date date ) IS
1700 SELECT Period_Name,Start_Date,
1701 End_Date FROM Gl_Periods WHERE
1702 Period_Set_Name = c_period_set_name AND
1703 Period_Type = c_period_type AND Start_Date > c_start_date AND
1704 Adjustment_Period_Flag = 'N' AND ROWNUM < 52
1705 ORDER BY Start_Date;
1706 */
1707
1708 -- Added for bug 7578853
1709 CURSOR end_period_cur(c_period_set_name varchar2,
1710 c_period_type varchar2,
1711 c_start_Date date ) IS
1712 SELECT Period_Name,Start_Date, End_Date
1713 FROM
1714 (
1715 SELECT Period_Name,Start_Date, End_Date
1716 FROM Gl_Periods
1717 WHERE Period_Set_Name = c_period_set_name AND
1718 Period_Type = c_period_type AND Start_Date > c_start_date AND
1719 Adjustment_Period_Flag = 'N'
1720 ORDER BY Start_Date
1721 )
1722 WHERE ROWNUM < 52;
1723
1724 l_period_set_name Gl_Periods.Period_Set_Name%TYPE;
1725 l_gl_period_type Gl_Periods.Period_Type%TYPE;
1726 l_pa_period_type Gl_Periods.Period_Type%TYPE;
1727
1728 l_return_status VARCHAR2(2000);
1729 l_msg_count NUMBER := 0;
1730 l_msg_data VARCHAR2(2000);
1731 l_create_pa_profile VARCHAR2(1) := 'N';
1732 l_create_gl_profile VARCHAR2(1) := 'N';
1733 BEGIN
1734 x_gl_start_period := null;
1735 x_gl_end_period := null;
1736 x_gl_start_Date := null;
1737 x_pa_start_period := null;
1738 x_pa_end_period := null;
1739 x_pa_start_date := null;
1740 x_plan_version_exists_flag := 'N';
1741 SELECT COUNT(*) INTO l_count FROM Pa_Budget_Versions
1742 WHERE Project_Id = p_project_id AND
1743 Period_Profile_Id IS NOT NULL AND
1744 Period_Profile_Id > 0;
1745 IF l_count > 0 THEN
1746 x_plan_version_exists_flag := 'Y';
1747 END IF;
1748
1749 -- Bug 2589885, 17-MAR-03, jwhite, begin: --------------------------
1750
1751 /* -- Original Code -----------------------
1752 IF p_info_flag <> 'ALL' THEN
1753 RETURN;
1754 END IF;
1755 */
1756
1757 -- New Code, begin:-----------------------------
1758 -- MOVED original code BELOW since this procedure must now always run
1759 -- to populate the following filters for the page LOVs:
1760 -- 1) x_prj_start_date
1761 -- 2) x_prj_end_date
1762
1763
1764
1765 -- New Code, end: -----------------------------
1766
1767
1768 -- Bug 2589885, 17-MAR-03, jwhite, end: --------------------------
1769
1770
1771 SELECT COUNT(*) INTO l_profile_count
1772 FROM pa_proj_period_profiles
1773 WHERE
1774 project_id = p_project_id AND
1775 period_profile_type = 'FINANCIAL_PLANNING' AND
1776 plan_period_type = 'PA';
1777 IF l_profile_count = 0 THEN
1778 l_create_pa_profile := 'Y';
1779 END IF;
1780 SELECT COUNT(*) INTO l_profile_count
1781 FROM pa_proj_period_profiles
1782 WHERE
1783 project_id = p_project_id AND
1784 period_profile_type = 'FINANCIAL_PLANNING' AND
1785 plan_period_type = 'GL';
1786 IF l_profile_count = 0 THEN
1787 l_create_gl_profile := 'Y';
1788 END IF;
1789
1790
1791 BEGIN
1792
1793 -- Bug 2589885, 17-MAR-03, jwhite, begin: --------------------------
1794
1795 /* -- Original Code -----------------------
1796
1797 SELECT start_Date, completion_date, nvl(org_id,-99)
1798 INTO
1799 l_prj_start_date,l_prj_completion_date,l_prj_org_id FROM
1800 Pa_Projects_All WHERE
1801 Project_Id = p_project_id;
1802
1803 EXCEPTION
1804 WHEN NO_DATA_FOUND THEN
1805 RETURN;
1806 END;
1807
1808
1809
1810 */
1811
1812 -- New Code, begin:-----------------------------
1813
1814 /* Bug 3354518- FP.M -dbora- Modified the sql statement associated with the start_date select
1815 */
1816 SELECT nvl(start_Date, trunc(sysdate))
1817 , completion_date
1818 , nvl(org_id,-99)
1819 , decode (start_date, NULL, NULL, TO_CHAR(start_date,'rrrr/mm/dd') )
1820 , decode (completion_date, NULL, NULL, TO_CHAR(completion_date,'rrrr/mm/dd') )
1821 INTO l_prj_start_date
1822 ,l_prj_completion_date
1823 ,l_prj_org_id
1824 ,x_prj_start_date
1825 ,x_prj_end_date
1826 FROM Pa_Projects_All
1827 WHERE Project_Id = p_project_id;
1828
1829 EXCEPTION
1830 WHEN NO_DATA_FOUND THEN
1831 x_prj_start_date := NULL;
1832 x_prj_end_date := NULL;
1833 RETURN;
1834
1835 END; -- select start_date
1836
1837
1838 -- Original Code from ABOVE put here to make sure that the following
1839 -- OUT-parameters will always be populated for the page LOVs
1840 -- 1) x_prj_start_date
1841 -- 2) x_prj_end_date
1842
1843 IF p_info_flag <> 'ALL' THEN
1844 RETURN;
1845 END IF;
1846
1847
1848
1849 -- New Code, end: -----------------------------
1850
1851
1852 -- Bug 2589885, 17-MAR-03, jwhite, end: --------------------------
1853
1854
1855
1856 BEGIN
1857 SELECT imp.Period_Set_Name,imp.Pa_Period_Type,sob.Accounted_Period_Type
1858 INTO l_period_set_name , l_pa_period_type,l_gl_period_type
1859 FROM Pa_Implementations_All imp, Gl_Sets_Of_Books sob WHERE
1860 --NVL(imp.Org_Id,-99) = l_prj_org_id AND
1861 imp.Org_Id = l_prj_org_id AND -- Bug Ref # 6327662
1862 imp.Set_Of_Books_Id = sob.Set_Of_Books_Id;
1863 EXCEPTION
1864 WHEN NO_DATA_FOUND THEN
1865 RETURN;
1866 END;
1867
1868 IF l_prj_start_date IS NOT NULL THEN
1869 BEGIN
1870 SELECT gl.Period_Name,TO_CHAR(glp.start_Date,'rrrr/mm/dd'),
1871 glp.end_date
1872 INTO x_gl_start_period,x_gl_start_date,
1873 l_pd_gl_end_date
1874 FROM
1875 Gl_Date_Period_Map gl,
1876 gl_periods glp WHERE
1877 gl.Period_Set_Name = l_period_set_name AND
1878 gl.Period_Type = l_gl_period_type AND
1879 gl.Accounting_Date = l_prj_start_date AND
1880 glp.period_set_name = gl.Period_Set_Name AND
1881 glp.Period_Type = gl.Period_Type AND
1882 glp.adjustment_period_flag = 'N' AND
1883 glp.period_name = gl.period_name;
1884
1885 /* Assigned start period info as end period info for bug# 2987076 */
1886 x_gl_end_period := x_gl_start_period;
1887 l_dummy_gl_end_date := l_pd_gl_end_Date;
1888 /* modified the above assignment from x_gl_start_date to
1889 period end date (l_pd_gl_end_date ) for bug 3045693 */
1890
1891 EXCEPTION
1892 WHEN NO_DATA_FOUND THEN
1893 NULL;
1894 END;
1895 BEGIN
1896 SELECT gl.Period_Name,TO_CHAR(glp.start_Date,'rrrr/mm/dd'),
1897 glp.end_date
1898 INTO x_pa_start_period,x_pa_start_date,
1899 l_pd_pa_end_date
1900 FROM
1901 Gl_Date_Period_Map gl,
1902 gl_periods glp WHERE
1903 gl.Period_Set_Name = l_period_set_name AND
1904 gl.Period_Type = l_pa_period_type AND
1905 gl.Accounting_Date = l_prj_start_date AND
1906 glp.period_set_name = gl.Period_Set_Name AND
1907 glp.Period_Type = gl.Period_Type AND
1908 glp.adjustment_period_flag = 'N' AND
1909 glp.period_name = gl.period_name;
1910
1911 /* Assigned start period info as end period info for bug# 2987076 */
1912 x_pa_end_period := x_pa_start_period;
1913 l_dummy_pa_end_date := l_pd_pa_end_date;
1914 /* modified the above assignment from x_pa_start_date to
1915 period end date (l_pd_pa_end_date ) for bug 3045693 */
1916 EXCEPTION
1917 WHEN NO_DATA_FOUND THEN
1918 NULL;
1919 END;
1920 /* setting the end periods */
1921 /* check for prj_completion date to null removed
1922 for bug 2581913 */
1923 FOR cur_rec IN end_period_cur(l_period_Set_name,
1924 l_pa_period_type,
1925 l_prj_start_Date )
1926 LOOP
1927 IF cur_rec.start_date > l_prj_completion_date AND
1928 l_prj_completion_date IS NOT NULL THEN
1929 EXIT;
1930 END IF;
1931 x_pa_end_period := cur_rec.period_name;
1932
1933 /* review changes. msoundra 02-JAN-2003.
1934 End date should not be passed as NULL. If passed as NULL,
1935 the default profile would be created for the maximum periods
1936 ( 52 or less ) regardless of the proj completion date. */
1937
1938 l_dummy_pa_end_date := cur_rec.end_date;
1939 END LOOP;
1940
1941 FOR cur_rec IN end_period_cur(l_period_Set_name,
1942 l_gl_period_type,
1943 l_prj_start_Date )
1944 LOOP
1945 IF cur_rec.start_date > l_prj_completion_date AND
1946 l_prj_completion_date IS NOT NULL THEN
1947 EXIT;
1948 END IF;
1949 x_gl_end_period := cur_rec.period_name;
1950
1951 /* review changes. msoundra 02-JAN-2003.
1952 End date should not be passed as NULL. If passed as NULL,
1953 the default profile would be created for the maximum periods
1954 ( 52 or less ) regardless of the proj completion date. */
1955
1956 l_dummy_gl_end_date := cur_rec.end_date;
1957 END LOOP;
1958 END IF;
1959
1960 /* Bug 2689403 - If we are able derive a default for pa period profile dtls,
1961 create the same immediately and commit it before the period profile page is rendered.
1962 The period profile page would then fetch the queried record to be displayed on screen */
1963
1964 /* The default period profile info just derived needs to created (inserted) for the project
1965 only when p_create_defaults */
1966
1967 IF p_create_defaults = 'Y' THEN
1968
1969 IF x_pa_start_date IS NOT NULL AND
1970 l_create_pa_profile = 'Y' THEN
1971
1972 IF p_pa_debug_mode = 'Y' THEN
1973 pa_debug.g_err_stage := 'Calling Maintain_Prj_Period_Profile to create the PA period profile ....';
1974 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1975 END IF;
1976
1977 Pa_Prj_Period_Profile_Utils.Maintain_Prj_Period_Profile(
1978 p_project_id => p_project_id,
1979 p_period_profile_type => 'FINANCIAL_PLANNING',
1980 p_plan_period_type => 'PA',
1981 p_period_set_name => l_period_set_name,
1982 p_gl_period_type => l_gl_period_type,
1983 p_pa_period_type => l_pa_period_type,
1984 p_start_date => to_date(x_pa_start_date,'rrrr/mm/dd'),
1985 px_end_date => l_dummy_pa_end_date ,
1986 px_period_profile_id => l_dummy_pa_period_profile_id,
1987 p_commit_flag => 'Y',
1988 px_number_of_periods => l_dummy_pa_number_of_periods,
1989 p_debug_mode => 'Y',
1990 p_add_msg_in_stack => 'Y',
1991 x_plan_start_date => l_dummy_pa_plan_start_date,
1992 x_plan_end_date => l_dummy_pa_plan_end_date,
1993 x_return_status => l_return_status,
1994 x_msg_count => l_msg_count,
1995 x_msg_data => l_msg_data );
1996
1997 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1998 IF p_pa_debug_mode = 'Y' THEN
1999 pa_debug.g_err_stage := ' Maintain_Prj_Period_Profile Errored for PA';
2000 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2001 END IF;
2002 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2003 END IF;
2004
2005 END IF;
2006
2007 /* Bug 2689403 - If we are able derive a default for gl period profile dtls,
2008 create the same immediately and commit it before the period profile page is rendered.
2009 The period profile page would then fetch the queried record to be displayed on screen */
2010
2011 IF x_gl_start_date IS NOT NULL AND
2012 l_create_gl_profile = 'Y' THEN
2013 IF p_pa_debug_mode = 'Y' THEN
2014 pa_debug.g_err_stage := 'Calling Maintain_Prj_Period_Profile to create the GL period profile ....';
2015 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2016 END IF;
2017
2018 Pa_Prj_Period_Profile_Utils.Maintain_Prj_Period_Profile(
2019 p_project_id => p_project_id,
2020 p_period_profile_type => 'FINANCIAL_PLANNING',
2021 p_plan_period_type => 'GL',
2022 p_period_set_name => l_period_set_name,
2023 p_gl_period_type => l_gl_period_type,
2024 p_pa_period_type => l_pa_period_type,
2025 p_start_date => to_date(x_gl_start_date,'rrrr/mm/dd'),
2026 px_end_date => l_dummy_gl_end_date ,
2027 px_period_profile_id => l_dummy_gl_period_profile_id,
2028 p_commit_flag => 'Y',
2029 px_number_of_periods => l_dummy_gl_number_of_periods,
2030 p_debug_mode => 'Y',
2031 p_add_msg_in_stack => 'Y',
2032 x_plan_start_date => l_dummy_gl_plan_start_date,
2033 x_plan_end_date => l_dummy_gl_plan_end_date,
2034 x_return_status => l_return_status,
2035 x_msg_count => l_msg_count,
2036 x_msg_data => l_msg_data );
2037
2038 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2039 IF p_pa_debug_mode = 'Y' THEN
2040 pa_debug.g_err_stage := ' Maintain_Prj_Period_Profile Errored for PA';
2041 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2042 END IF;
2043 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2044 END IF;
2045 END IF;
2046 END IF;
2047 RETURN;
2048 EXCEPTION
2049
2050 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2051
2052 IF P_PA_DEBUG_MODE = 'Y' THEN
2053 pa_debug.g_err_stage:= l_msg_data;
2054 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2055 pa_debug.g_err_stage := sqlerrm;
2056 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2057 END IF;
2058 RAISE;
2059 END Get_Prj_Defaults;
2060
2061 /*===================================================================
2062 This api returns the current period profile id,start period and end
2063 period for givenproject id, plan period type and period profile type
2064 ==================================================================*/
2065
2066 PROCEDURE Get_Curr_Period_Profile_Info(
2067 p_project_id IN VARCHAR2
2068 ,p_period_type IN VARCHAR2
2069 ,p_period_profile_type IN VARCHAR2
2070 ,x_period_profile_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2071 ,x_start_period OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2072 ,x_end_period OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2073 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2074 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2075 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2076 AS
2077
2078
2079 l_return_status VARCHAR2(2000);
2080 l_msg_count NUMBER :=0;
2081 l_msg_data VARCHAR2(2000);
2082 l_data VARCHAR2(2000);
2083 l_msg_index_out NUMBER;
2084 l_debug_mode VARCHAR2(30);
2085
2086 l_period_profile_id pa_proj_period_profiles.period_profile_id%TYPE;
2087 l_start_period pa_proj_period_profiles.period_name1%TYPE;
2088 l_end_period pa_proj_period_profiles.profile_end_period_name%TYPE;
2089
2090 BEGIN
2091
2092 x_msg_count := 0;
2093 x_return_status := FND_API.G_RET_STS_SUCCESS;
2094
2095 pa_debug.set_err_stack('Get_Curr_Period_Profile_Info');
2096 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2097 l_debug_mode := NVL(l_debug_mode, 'Y');
2098 IF P_PA_DEBUG_MODE = 'Y' THEN
2099 pa_debug.set_process('Get_Curr_Period_Profile_Info: ' || 'PLSQL','LOG',l_debug_mode);
2100 END IF;
2101
2102 -- Check for not null parameters
2103
2104 pa_debug.g_err_stage := 'Checking for valid parameters:';
2105 IF P_PA_DEBUG_MODE = 'Y' THEN
2106 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,3);
2107 END IF;
2108
2109 IF (p_project_id IS NULL) OR
2110 (p_period_type NOT IN (PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL,PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA)) OR
2111 (p_period_profile_type IS NULL)
2112 THEN
2113
2114 pa_debug.g_err_stage := 'Project='||p_project_id;
2115 IF P_PA_DEBUG_MODE = 'Y' THEN
2116 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,5);
2117 END IF;
2118 pa_debug.g_err_stage := 'Period_type='||p_period_type;
2119 IF P_PA_DEBUG_MODE = 'Y' THEN
2120 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,5);
2121 END IF;
2122 pa_debug.g_err_stage := 'P_period_profile_type='||p_period_profile_type;
2123 IF P_PA_DEBUG_MODE = 'Y' THEN
2124 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,5);
2125 END IF;
2126
2127 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
2128 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2129
2130 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2131
2132 END IF;
2133
2134 pa_debug.g_err_stage := 'Parameter validation complete';
2135 IF P_PA_DEBUG_MODE = 'Y' THEN
2136 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,3);
2137 END IF;
2138
2139 --Fetch Profile Info
2140
2141 BEGIN
2142
2143 pa_debug.g_err_stage := 'Fetching Profile Info';
2144 IF P_PA_DEBUG_MODE = 'Y' THEN
2145 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,3);
2146 END IF;
2147
2148 SELECT period_profile_id
2149 ,period_name1
2150 ,profile_end_period_name
2151 INTO l_period_profile_id
2152 ,l_start_period
2153 ,l_end_period
2154 FROM pa_proj_period_profiles
2155 WHERE project_id = p_project_id
2156 AND current_flag = 'Y'
2157 AND period_profile_type = p_period_profile_type
2158 AND plan_period_type = p_period_type;
2159
2160 EXCEPTION
2161
2162 WHEN NO_DATA_FOUND THEN
2163
2164 --There is no current profile for project.return null
2165
2166 pa_debug.g_err_stage := 'Current period profile doesnt exist for project';
2167 IF P_PA_DEBUG_MODE = 'Y' THEN
2168 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,3);
2169 END IF;
2170
2171 l_period_profile_id := NULL;
2172 l_start_period := NULL;
2173 l_end_period := NULL;
2174
2175 END;
2176
2177 --Pass out_parameters to calling program
2178
2179 x_period_profile_id := l_period_profile_id;
2180 x_start_period := l_start_period;
2181 x_end_period := l_end_period;
2182
2183
2184 pa_debug.g_err_stage := ' Exiting Get_Curr_Period_Profile_Info';
2185 IF P_PA_DEBUG_MODE = 'Y' THEN
2186 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,3);
2187 END IF;
2188 pa_debug.reset_err_stack;
2189 EXCEPTION
2190
2191 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2192
2193 l_msg_count := FND_MSG_PUB.count_msg;
2194
2195 IF l_msg_count = 1 THEN
2196
2197 PA_INTERFACE_UTILS_PUB.get_messages
2198 (p_encoded => FND_API.G_TRUE
2199 ,p_msg_index => 1
2200 ,p_msg_count => l_msg_count
2201 ,p_msg_data => l_msg_data
2202 ,p_data => l_data
2203 ,p_msg_index_out => l_msg_index_out);
2204
2205 x_msg_data := l_data;
2206 x_msg_count := l_msg_count;
2207
2208 ELSE
2209
2210 x_msg_count := l_msg_count;
2211
2212 END IF;
2213
2214 pa_debug.g_err_stage:='Invalid Arguments Passed';
2215 IF P_PA_DEBUG_MODE = 'Y' THEN
2216 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,5);
2217 END IF;
2218
2219 x_return_status:= FND_API.G_RET_STS_ERROR;
2220
2221 pa_debug.reset_err_stack;
2222
2223 RAISE;
2224
2225 WHEN Others THEN
2226
2227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2228 x_msg_count := 1;
2229 x_msg_data := SQLERRM;
2230
2231 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_PRJ_PERIOD_PROFILE_UTILS'
2232 ,p_procedure_name => 'Get_Curr_Period_Profile_Info');
2233
2234 pa_debug.g_err_stage:='Unexpected Error' || SQLERRM;
2235 IF P_PA_DEBUG_MODE = 'Y' THEN
2236 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,5);
2237 END IF;
2238
2239 pa_debug.reset_err_stack;
2240
2241 RAISE;
2242
2243 END Get_Curr_Period_Profile_Info;
2244
2245 /*
2246 NEED TO CUT THE FUNCTION AND THE PROCEDURE BELOW
2247 AND PASTE THESE IN THE PERIOD PROFILES PACKAGE
2248 */
2249 --This function is a local function and is not exposed to other APIs
2250 --This is used to calculate the amount type id based on the amount
2251 --type code passed to it
2252 FUNCTION GET_AMTTYPE_ID
2253 ( p_amt_typ_code IN pa_amount_types_b.amount_type_code%TYPE
2254 := NULL
2255 ) RETURN NUMBER IS
2256 l_amount_type_id pa_amount_types_b.amount_type_id%TYPE;
2257 l_amt_code pa_fp_org_fcst_gen_pub.char240_data_type_table;
2258 l_amt_id pa_fp_org_fcst_gen_pub.number_data_type_table;
2259
2260 l_debug_mode VARCHAR2(30);
2261
2262 CURSOR get_amt_det IS
2263 SELECT atb.amount_type_id
2264 ,atb.amount_type_code
2265 FROM pa_amount_types_b atb
2266 WHERE atb.amount_type_class = 'R';
2267
2268 l_stage number := 0;
2269
2270 BEGIN
2271 IF P_PA_DEBUG_MODE = 'Y' THEN
2272 pa_debug.init_err_stack('PA_PLAN_MATRIX.GET_AMTTYPE_ID');
2273 END IF;
2274
2275 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2276 l_debug_mode := NVL(l_debug_mode, 'Y');
2277
2278 IF P_PA_DEBUG_MODE = 'Y' THEN
2279 pa_debug.set_process('GET_AMTTYPE_ID: ' || 'PLSQL','LOG',l_debug_mode);
2280 END IF;
2281
2282 l_amount_type_id := -99;
2283
2284 IF l_amt_code.last IS NULL THEN
2285 OPEN get_amt_det;
2286 LOOP
2287 FETCH get_amt_det into l_amt_id(nvl(l_amt_id.last+1,1))
2288 ,l_amt_code(nvl(l_amt_code.last+1,1));
2289 EXIT WHEN get_amt_det%NOTFOUND;
2290 END LOOP;
2291 END IF;
2292
2293 IF l_amt_code.last IS NOT NULL THEN
2294 FOR i in l_amt_id.first..l_amt_id.last LOOP
2295 IF l_amt_code(i) = p_amt_typ_code THEN
2296 l_amount_type_id := l_amt_id(i);
2297 END IF;
2298 END LOOP;
2299 END IF;
2300 IF l_amount_type_id = -99 THEN
2301 pa_debug.g_err_stage := 'p_amt_typ_code ['||p_amt_typ_code ||']';
2302 IF P_PA_DEBUG_MODE = 'Y' THEN
2303 pa_debug.write_file('GET_AMTTYPE_ID: ' || pa_debug.g_err_stage);
2304 END IF;
2305 END IF;
2306 pa_debug.reset_err_stack;
2307 RETURN(l_amount_type_id);
2308
2309 EXCEPTION
2310 WHEN OTHERS THEN
2311 FND_MSG_PUB.add_exc_msg(
2312 p_pkg_name => 'PA_FP_ORG_FCST_GEN_PUB.get_amttype_id'
2313 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
2314
2315 IF P_PA_DEBUG_MODE = 'Y' THEN
2316 pa_debug.write_file('GET_AMTTYPE_ID: ' || SQLERRM);
2317 END IF;
2318 pa_debug.reset_err_stack;
2319 RAISE;
2320 END GET_AMTTYPE_ID;
2321
2322 --This API is called on refresh of period profiles.
2323 --The API deletes current record from the pa_proj_periods_denorm table
2324 --for the budget version passed to it. It then populates the
2325 --Pa_Fin_Plan_Lines_Tmp table from the records in the Pa_Budget_Lines
2326 --table. The API then calls the Maintain Plan Matrix API to populate
2327 --the budget lines table with preceding and succeeding period values and
2328 --inserts the current period amounts in the pa_proj_periods_denorm table
2329
2330 PROCEDURE Refresh_Period_Profile
2331 (
2332 p_budget_version_id IN NUMBER,
2333 p_period_profile_id IN NUMBER,
2334 p_project_id IN NUMBER,
2335 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2336 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2337 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2338 )
2339 IS
2340 -- Local Variable Declaration
2341 l_budget_version_id NUMBER;
2342 l_period_profile_id NUMBER;
2343 l_project_id NUMBER;
2344 l_version_type VARCHAR2(30);
2345 l_data_source VARCHAR2(30);
2346 l_debug_mode VARCHAR2(30);
2347 amt_rec PA_PLAN_MATRIX.AMOUNT_TYPE_TABTYP;
2348 l_request_id NUMBER;
2349 BEGIN
2350 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2351 -- Setting the Debug Statements
2352 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2353 l_debug_mode := NVL(l_debug_mode, 'N');
2354 IF P_PA_DEBUG_MODE = 'Y' THEN
2355 PA_DEBUG.Set_Curr_Function( p_function => 'Refresh_Period_Profile',
2356 p_debug_mode => l_debug_mode );
2357 END IF;
2358 x_return_status := FND_API.G_RET_STS_SUCCESS;
2359
2360 IF P_PA_DEBUG_MODE = 'Y' THEN
2361 PA_DEBUG.g_err_stage := 'Entering Refresh Period Profile ' ||
2362 'for Refreshing the Period Profile';
2363 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2364 PA_DEBUG.g_err_stage := 'Concurrent request id :' ||
2365 to_char(nvl(l_request_id,0));
2366 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2367 END IF;
2368
2369 -- Set the savepoint to return if any of the merges fail
2370 -- for control items
2371
2372 savepoint before_refresh_pd_profile;
2373
2374 --Checking for the budget version id to be null
2375 --If budget version id is null then no processing will take place as there is no
2376 --record in denorm for that budget version id. If no record then it is the case
2377 --of creating a new profile rather than refreshing an existing profile.
2378 IF p_budget_version_id IS NULL THEN
2379 IF P_PA_DEBUG_MODE = 'Y' THEN
2380 PA_DEBUG.g_err_stage := 'No Budget Version ID is specified ' ||
2381 'or budget version id is null';
2382 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2383 END IF;
2384 /* PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2385 p_msg_name => 'NULL_BDGT_VSN_ID');
2386 invalid message code, so commented */
2387 x_return_status := FND_API.G_RET_STS_ERROR;
2388 /* x_msg_data := 'NULL_BDGT_VSN_ID'; */
2389 IF P_PA_DEBUG_MODE = 'Y' THEN
2390 PA_DEBUG.Reset_Curr_Function;
2391 END IF;
2392 RETURN;
2393 END IF;
2394 -- Setting local variable values
2395 l_data_source := 'BUDGET_LINES';
2396 l_budget_version_id := p_budget_version_id;
2397 --l_period_profile_id := p_period_profile_id;
2398 l_version_type := NULL;
2399
2400 -- Fix for P1 bug 2682761
2401 -- Updating budget versions table for this budget version
2402 -- before calling call maintain plan matrix
2403
2404 UPDATE pa_budget_versions bv
2405 SET bv.period_profile_id = p_period_profile_id,
2406 record_version_number = nvl(record_version_number,0) + 1,
2407 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2408 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2409 LAST_UPDATE_DATE = sysdate
2410 WHERE bv.budget_version_id = l_budget_version_id
2411 AND bv.project_id = p_project_id;
2412
2413 /*
2414
2415 -- Calling the API to populate the Pa_Fin_Plan_Lines_Tmp table
2416 PA_FIN_PLAN_PUB.CALL_MAINTAIN_PLAN_MATRIX
2417 (
2418 p_budget_version_id => l_budget_version_id,
2419 p_data_source => l_data_source,
2420 x_return_status => x_return_status,
2421 x_msg_count => x_msg_count,
2422 x_msg_data => x_msg_data
2423 );
2424 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2425 ROLLBACK TO before_refresh_pd_profile;
2426 UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
2427 p_return_status => x_return_status,
2428 p_project_id => p_project_id,
2429 p_request_id => l_request_id );
2430 IF P_PA_DEBUG_MODE = 'Y' THEN
2431 PA_DEBUG.Reset_Curr_Function;
2432 END IF;
2433 RETURN;
2434 END IF;
2435
2436 --Calling the ROLL UP API for denorm amounts to aggregate all the records
2437 --The Roll up API that is being called, simply assumes that all parent
2438 --level records for the updated records are available in denorm table.
2439 --This API simply takes sum of amounts at child level records and
2440 --updates the amounts on the parents.
2441 PA_FP_ROLLUP_PKG.ROLLUP_DENORM_AMOUNTS
2442 (
2443 p_budget_version_id => l_budget_version_id
2444 ,x_return_status => x_return_status
2445 ,x_msg_count => x_msg_count
2446 ,x_msg_data => x_msg_data
2447 );
2448 Refresh period denorm API takes care of deleting all the
2449 period denorm records for the given budget version and
2450 then populate user entered and rollup records.
2451 */
2452
2453 PA_FP_ROLLUP_PKG.Refresh_Period_Denorm(
2454 p_budget_version_id => l_budget_version_id
2455 ,x_return_status => x_return_status
2456 ,x_msg_count => x_msg_count
2457 ,x_msg_data => x_msg_data );
2458
2459 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2460 ROLLBACK TO before_refresh_pd_profile;
2461 UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
2462 p_return_status => x_return_status,
2463 p_project_id => p_project_id,
2464 p_request_id => l_request_id );
2465 IF P_PA_DEBUG_MODE = 'Y' THEN
2466 PA_DEBUG.Reset_Curr_Function;
2467 END IF;
2468 RETURN;
2469 END IF;
2470
2471 /* updating budget version for Successful completion. */
2472 UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
2473 p_return_status => x_return_status,
2474 p_project_id => NULL,
2475 p_request_id => l_request_id );
2476
2477 COMMIT;
2478 EXCEPTION
2479 WHEN OTHERS THEN
2480 FND_MSG_PUB.add_exc_msg
2481 ( p_pkg_name => 'PA_PRJ_PERIOD_PROFILE_UTILS.refresh_period_profile'
2482 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
2483 IF P_PA_DEBUG_MODE = 'Y' THEN
2484 PA_DEBUG.g_err_stage := 'Unexpected error in refresh_period_profile ';
2485 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2486 END IF;
2487 ROLLBACK TO before_refresh_pd_profile;
2488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2489 UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
2490 p_return_status => x_return_status,
2491 p_project_id => p_project_id,
2492 p_request_id => l_request_id );
2493 IF P_PA_DEBUG_MODE = 'Y' THEN
2494 PA_DEBUG.Reset_Curr_Function;
2495 END IF;
2496 RAISE;
2497 END Refresh_Period_Profile;
2498
2499 PROCEDURE Wrapper_Refresh_Pd_Profile
2500 (
2501 errbuff OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2502 retcode OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2503 p_budget_version_id1 IN NUMBER,
2504 p_budget_version_id2 IN NUMBER,
2505 p_project_id IN NUMBER,
2506 p_refresh_option_code IN VARCHAR2,
2507 p_gl_period_profile_id IN NUMBER,
2508 p_pa_period_profile_id IN NUMBER,
2509 p_debug_mode IN VARCHAR2
2510 )
2511 IS
2512 -- Local Variable Declaration
2513 l_budget_version_id NUMBER;
2514 l_budget_version_id1 NUMBER;
2515 l_budget_version_id2 NUMBER;
2516 l_project_id NUMBER;
2517 l_refresh_option_code VARCHAR2(30);
2518 l_gl_period_profile_id NUMBER;
2519 l_pa_period_profile_id NUMBER;
2520 l_time_phased_code VARCHAR2(30);
2521 l_return_status VARCHAR2(2000);
2522 l_msg_count NUMBER;
2523 l_msg_data VARCHAR2(2000);
2524 l_count NUMBER;
2525 TYPE budget_version_id_tab IS TABLE OF PA_BUDGET_VERSIONS.budget_version_id%type
2526 INDEX BY BINARY_INTEGER;
2527 t_budget_version_id budget_version_id_tab;
2528 l_request_id NUMBER;
2529 l_locked_person_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2530 l_plan_proc_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2531 l_req_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2532 BEGIN
2533 -- Setting the Debug Statements
2534 IF P_PA_DEBUG_MODE = 'Y' THEN
2535 PA_DEBUG.Set_Curr_Function( p_function => 'Wrapper_Refresh_Pd_Profile',
2536 p_debug_mode => p_debug_mode );
2537 END IF;
2538 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2539
2540 IF P_PA_DEBUG_MODE = 'Y' THEN
2541 PA_DEBUG.g_err_stage := 'Entering Refresh Period Profile WRAPPER ' ||
2542 'for Conc Request Id :'||to_char(nvl(l_Request_id,0));
2543 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2544 PA_DEBUG.g_err_stage := 'Parameters : ';
2545 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2546 PA_DEBUG.g_err_stage := 'Budget version id1 : '||
2547 to_char(nvl(p_budget_Version_id1,0));
2548 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2549 PA_DEBUG.g_err_stage := 'Budget version id2 : '||
2550 to_char(nvl(p_budget_Version_id2,0));
2551 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2552 PA_DEBUG.g_err_stage := 'Project Id : '||
2553 to_char(nvl(p_project_id,0));
2554 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2555 PA_DEBUG.g_err_stage := 'Refresh option code : '||
2556 nvl(p_refresh_option_code,'NULL');
2557 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2558 PA_DEBUG.g_err_stage := 'Pa Profile Id : '||
2559 to_char(nvl(p_pa_period_profile_id,0));
2560 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2561 PA_DEBUG.g_err_stage := 'Gl Profile Id : '||
2562 to_char(nvl(p_gl_period_profile_id,0));
2563 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2564 END IF;
2565 retcode := '0';
2566
2567 /* Setting local variable values */
2568 l_budget_version_id := NULL;
2569 l_budget_version_id1 := p_budget_version_id1;
2570 l_budget_version_id2 := p_budget_version_id2;
2571 l_project_id := p_project_id;
2572 l_refresh_option_code := p_refresh_option_code;
2573 l_gl_period_profile_id := p_gl_period_profile_id;
2574 l_pa_period_profile_id := p_pa_period_profile_id;
2575 l_time_phased_code := NULL;
2576 l_return_status := NULL;
2577 l_msg_count := NULL;
2578 l_msg_data := NULL;
2579 l_count := 1;
2580
2581 /* Deleting any records from the PL/SQL table */
2582 t_budget_version_id.DELETE;
2583 l_req_id_tab.DELETE;
2584 l_locked_person_id_tab.DELETE;
2585 l_plan_proc_code_tab.DELETE;
2586
2587 /* Would go inside this loop only if one of the budget version ids
2588 is not null */
2589 IF (l_budget_version_id1 IS NOT NULL OR l_budget_version_id2 IS NOT NULL) THEN
2590 -- For budget version id 1
2591 IF l_budget_version_id1 IS NOT NULL THEN
2592 t_budget_version_id(l_count) := l_budget_version_id1;
2593 select
2594 nvl(locked_by_person_id,0),
2595 nvl(plan_processing_code,'DUMMY'),
2596 nvl(request_id,0) into
2597 l_locked_person_id_tab(l_count),
2598 l_plan_proc_code_tab(l_count),
2599 l_req_id_tab(l_count)
2600 from pa_budget_versions where
2601 budget_version_id = l_budget_version_id1;
2602
2603
2604 l_count := l_count + 1;
2605 END IF;
2606 -- For budget version id 2
2607 IF l_budget_version_id2 IS NOT NULL THEN
2608 t_budget_version_id(l_count) := l_budget_version_id2;
2609 select
2610 nvl(locked_by_person_id,0),
2611 NVL(plan_processing_code,'DUMMY'),
2612 nvl(request_id,0) into
2613 l_locked_person_id_tab(l_count),
2614 l_plan_proc_code_tab(l_count),
2615 l_req_id_tab(l_count)
2616 from pa_budget_versions where
2617 budget_version_id = l_budget_version_id2;
2618
2619 l_count := l_count + 1;
2620 END IF;
2621 /* Checking for the refresh option code to be NOT null
2622 If refresh option code is null then the processing should
2623 transfer to checking the two budget version ids. If they are
2624 also null then the process should exit and do nothing in the program
2625
2626 Check for refresh option code. This WOULD BE NULL under following
2627 two conditions:
2628 1) If this API is being called from View Plans Page
2629 2) If the user chooses no plan version to refresh while refreshing period profiles */
2630
2631 ELSIF l_refresh_option_code IS NOT NULL THEN
2632 --Processing ahead only if project id is not null
2633 IF l_project_id IS NOT NULL THEN
2634 --Processing for refresh option code of ALL
2635 IF (l_refresh_option_code = 'ALL') THEN
2636 SELECT budget_version_id,
2637 nvl(locked_by_person_id,0),
2638 NVL(plan_processing_code,'DUMMY'),
2639 nvl(request_id,0)
2640 BULK COLLECT INTO
2641 t_budget_version_id,
2642 l_locked_person_id_tab,
2643 l_plan_proc_code_tab,
2644 l_req_id_tab
2645 FROM
2646 Pa_budget_versions
2647 WHERE
2648 project_id = l_project_id
2649 AND period_profile_id IS NOT NULL;
2650 ELSIF (l_refresh_option_code = 'SELECTED') THEN
2651 SELECT budget_version_id,
2652 nvl(locked_by_person_id,0),
2653 nvl(plan_processing_code,'DUMMY'),
2654 nvl(request_id,0)
2655 BULK COLLECT INTO
2656 t_budget_version_id,
2657 l_locked_person_id_tab,
2658 l_plan_proc_code_tab,
2659 l_req_id_tab
2660 FROM
2661 Pa_budget_versions
2662 WHERE
2663 project_id = l_project_id
2664 AND period_profile_id IS NOT NULL
2665 AND
2666 (
2667 (current_working_flag = 'Y' AND budget_status_code IN ('W','S'))
2668 OR (current_flag = 'Y' AND budget_status_code = 'B')
2669 OR (current_original_flag = 'Y' AND budget_status_code = 'B')
2670 );
2671 END IF;
2672 END IF;
2673 END IF;
2674 FOR l_cnt IN 1 .. t_budget_version_id.count
2675 LOOP
2676 l_budget_version_id := t_budget_version_id(l_cnt);
2677 BEGIN
2678 SELECT
2679 DECODE
2680 (po.fin_plan_preference_code,
2681 'COST_ONLY',po.cost_time_phased_code,
2682 'REVENUE_ONLY',po.revenue_time_phased_code,
2683 'COST_AND_REV_SAME',po.all_time_phased_code,
2684 DECODE
2685 (bv.version_type,
2686 'COST',po.cost_time_phased_code,
2687 'REVENUE',po.revenue_time_phased_code
2688 )
2689 )
2690 INTO
2691 l_time_phased_code
2692 FROM pa_budget_versions bv, pa_proj_fp_options po
2693 WHERE
2694 bv.budget_version_id = l_budget_version_id
2695 AND po.fin_plan_version_id = bv.budget_version_id
2696 AND po.fin_plan_option_level_code = 'PLAN_VERSION'
2697 AND bv.project_id = p_project_id
2698 AND po.project_id = bv.project_id;
2699 EXCEPTION
2700 WHEN NO_DATA_FOUND THEN
2701 IF P_PA_DEBUG_MODE = 'Y' THEN
2702 PA_DEBUG.g_err_stage := 'No data found while trying ' ||
2703 'to retrive data for time phased code FOR ' ||
2704 'l_refresh_option_code ALL';
2705 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2706 END IF;
2707 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2708 p_msg_name => 'PA_FP_TM_PHSD_CODE_NOT_FOUND');
2709 retcode := '2';
2710 errbuff := 'PA_FP_TM_PHSD_CODE_NOT_FOUND';
2711 IF P_PA_DEBUG_MODE = 'Y' THEN
2712 PA_DEBUG.Reset_Curr_Function;
2713 END IF;
2714 END;
2715 IF (l_time_phased_code = 'P' and
2716 l_req_id_tab(l_cnt)= l_request_id and
2717 l_plan_proc_code_tab(l_cnt) = 'PPP' ) THEN
2718 --Knows that time phased code is PA
2719 --So, check for PA period profile passed to the API
2720 IF P_PA_DEBUG_MODE = 'Y' THEN
2721 PA_DEBUG.g_err_stage := 'calling PP refresh for ' ||
2722 'PA period : ';
2723 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2724 PA_DEBUG.g_err_stage := 'budget version id ' ||
2725 to_char(l_budget_version_id) ||
2726 ' period profile id :'||to_char(l_pa_period_profile_id);
2727 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2728 END IF;
2729 IF l_pa_period_profile_id IS NOT NULL THEN
2730 --Call the refresh period profile with PA profile ID
2731 REFRESH_PERIOD_PROFILE
2732 (
2733 p_budget_version_id => l_budget_version_id,
2734 p_period_profile_id => l_pa_period_profile_id,
2735 p_project_id => l_project_id,
2736 x_return_status => l_return_status,
2737 x_msg_count => l_msg_count,
2738 x_msg_data => l_msg_data
2739 ) ;
2740 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2741 l_msg_data := 'ERR_CALL_REFRESH_PERIOD_PROFILE';
2742 retcode := '2';
2743 errbuff := l_msg_data;
2744 END IF;
2745 END IF;
2746 ELSIF (l_time_phased_code = 'G' and
2747 l_req_id_tab(l_cnt)= l_request_id and
2748 l_plan_proc_code_tab(l_cnt) = 'PPP' ) THEN
2749 --Knows that time phased code is GL
2750 --So, check for GL period profile passed to the API
2751 IF l_gl_period_profile_id IS NOT NULL THEN
2752 --Call the refresh period profile with GL profile ID
2753 IF P_PA_DEBUG_MODE = 'Y' THEN
2754 PA_DEBUG.g_err_stage := 'calling PP refresh for ' ||
2755 'GL period : ';
2756 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2757 PA_DEBUG.g_err_stage := 'budget version id ' ||
2758 to_char(l_budget_version_id) ||
2759 ' period profile id :'||to_char(l_gl_period_profile_id);
2760 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2761 END IF;
2762 REFRESH_PERIOD_PROFILE
2763 (
2764 p_budget_version_id => l_budget_version_id,
2765 p_period_profile_id => l_gl_period_profile_id,
2766 p_project_id => l_project_id,
2767 x_return_status => l_return_status,
2768 x_msg_count => l_msg_count,
2769 x_msg_data => l_msg_data
2770 ) ;
2771 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2772 l_msg_data := 'ERR_CALL_REFRESH_PERIOD_PROFILE';
2773 retcode := '2';
2774 errbuff := l_msg_data;
2775 END IF;
2776 END IF;
2777 END IF;
2778 END LOOP;
2779 EXCEPTION
2780 WHEN OTHERS THEN
2781 FND_MSG_PUB.add_exc_msg
2782 ( p_pkg_name => 'PA_PRJ_PERIOD_PROFILE_UTILS.wrapper_refresh_pd_profile'
2783 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
2784 IF P_PA_DEBUG_MODE = 'Y' THEN
2785 PA_DEBUG.g_err_stage := 'Unexpected error in wrapper_refresh_pd_profile ';
2786 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2787 END IF;
2788 retcode := '2';
2789 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2790 UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
2791 p_return_status => l_return_status,
2792 p_project_id => p_project_id,
2793 p_request_id => l_request_id );
2794 IF P_PA_DEBUG_MODE = 'Y' THEN
2795 PA_DEBUG.Reset_Curr_Function;
2796 END IF;
2797 RAISE;
2798 END Wrapper_Refresh_Pd_Profile;
2799
2800 -- +-----------------------------------------------------------------
2801 -- PROCEDURE get_current_period_info - Revision History
2802 -- 06-JAN-03 dlai: created-takes as input period_profile_id, and returns
2803 -- the period in which sysdate falls
2804 -- 15-JAN-03 dlai: added a couple of flag values:
2805 -- x_cur_period_number = -2, then the current date is BEFORE first period start date
2806 -- x_cur_period_number = -1, then the current date is AFTER last period end date
2807 procedure get_current_period_info
2808 (p_period_profile_id IN pa_proj_period_profiles.period_profile_id%TYPE,
2809 x_cur_period_number OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2810 x_cur_period_name OUT NOCOPY pa_proj_period_profiles.period_name1%TYPE, --File.Sql.39 bug 4440895
2811 x_cur_period_start_date OUT NOCOPY pa_proj_period_profiles.period1_start_date%TYPE, --File.Sql.39 bug 4440895
2812 x_cur_period_end_date OUT NOCOPY pa_proj_period_profiles.period1_end_date%TYPE, --File.Sql.39 bug 4440895
2813 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2814 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2815 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2816 as
2817 l_current_date DATE;
2818 l_msg_data VARCHAR2(1000);
2819 l_msg_index_out NUMBER(15);
2820
2821 cursor period_profile_csr is
2822 select period_name1, period1_start_date, period1_end_date,
2823 period_name2, period2_start_date, period2_end_date,
2824 period_name3, period3_start_date, period3_end_date,
2825 period_name4, period4_start_date, period4_end_date,
2826 period_name5, period5_start_date, period5_end_date,
2827 period_name6, period6_start_date, period6_end_date,
2828 period_name7, period7_start_date, period7_end_date,
2829 period_name8, period8_start_date, period8_end_date,
2830 period_name9, period9_start_date, period9_end_date,
2831 period_name10, period10_start_date, period10_end_date,
2832 period_name11, period11_start_date, period11_end_date,
2833 period_name12, period12_start_date, period12_end_date,
2834 period_name13, period13_start_date, period13_end_date,
2835 period_name14, period14_start_date, period14_end_date,
2836 period_name15, period15_start_date, period15_end_date,
2837 period_name16, period16_start_date, period16_end_date,
2838 period_name17, period17_start_date, period17_end_date,
2839 period_name18, period18_start_date, period18_end_date,
2840 period_name19, period19_start_date, period19_end_date,
2841 period_name20, period20_start_date, period20_end_date,
2842 period_name21, period21_start_date, period21_end_date,
2843 period_name22, period22_start_date, period22_end_date,
2844 period_name23, period23_start_date, period23_end_date,
2845 period_name24, period24_start_date, period24_end_date,
2846 period_name25, period25_start_date, period25_end_date,
2847 period_name26, period26_start_date, period26_end_date,
2848 period_name27, period27_start_date, period27_end_date,
2849 period_name28, period28_start_date, period28_end_date,
2850 period_name29, period29_start_date, period29_end_date,
2851 period_name30, period30_start_date, period30_end_date,
2852 period_name31, period31_start_date, period31_end_date,
2853 period_name32, period32_start_date, period32_end_date,
2854 period_name33, period33_start_date, period33_end_date,
2855 period_name34, period34_start_date, period34_end_date,
2856 period_name35, period35_start_date, period35_end_date,
2857 period_name36, period36_start_date, period36_end_date,
2858 period_name37, period37_start_date, period37_end_date,
2859 period_name38, period38_start_date, period38_end_date,
2860 period_name39, period39_start_date, period39_end_date,
2861 period_name40, period40_start_date, period40_end_date,
2862 period_name41, period41_start_date, period41_end_date,
2863 period_name42, period42_start_date, period42_end_date,
2864 period_name43, period43_start_date, period43_end_date,
2865 period_name44, period44_start_date, period44_end_date,
2866 period_name45, period45_start_date, period45_end_date,
2867 period_name46, period46_start_date, period46_end_date,
2868 period_name47, period47_start_date, period47_end_date,
2869 period_name48, period48_start_date, period48_end_date,
2870 period_name49, period49_start_date, period49_end_date,
2871 period_name50, period50_start_date, period50_end_date,
2872 period_name51, period51_start_date, period51_end_date,
2873 period_name52, period52_start_date, period52_end_date
2874 from pa_proj_period_profiles
2875 where period_profile_id = p_period_profile_id;
2876 period_profile_rec period_profile_csr%ROWTYPE;
2877
2878 BEGIN
2879 x_return_status := FND_API.G_RET_STS_SUCCESS;
2880 select sysdate
2881 into l_current_date
2882 from dual;
2883 open period_profile_csr;
2884 fetch period_profile_csr into period_profile_rec;
2885 if period_profile_csr%NOTFOUND then
2886 x_return_status := FND_API.G_RET_STS_ERROR;
2887 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2888 p_msg_name => 'PA_FP_INVALID_PRJ_PROFILE');
2889 x_msg_count := fnd_msg_pub.count_msg;
2890 IF x_msg_count = 1 THEN
2891 PA_INTERFACE_UTILS_PUB.Get_Messages (
2892 p_encoded => FND_API.G_TRUE,
2893 p_msg_index => 1,
2894 p_msg_count => 1 ,
2895 p_msg_data => l_msg_data ,
2896 p_data => x_msg_data,
2897 p_msg_index_out => l_msg_index_out );
2898 END IF;
2899 else
2900 if l_current_date < period_profile_rec.period1_start_date then
2901 x_cur_period_number := -2;
2902 x_cur_period_name := null;
2903 x_cur_period_start_date := null;
2904 x_cur_period_end_date := null;
2905 elsif l_current_date >= period_profile_rec.period1_start_date and l_current_date <= period_profile_rec.period1_end_date then
2906 x_cur_period_number := 1;
2907 x_cur_period_name := period_profile_rec.period_name1;
2908 x_cur_period_start_date := period_profile_rec.period1_start_date;
2909 x_cur_period_end_date := period_profile_rec.period1_end_date;
2910 elsif l_current_date >= period_profile_rec.period2_start_date and l_current_date <= period_profile_rec.period2_end_date then
2911 x_cur_period_number := 2;
2912 x_cur_period_name := period_profile_rec.period_name2;
2913 x_cur_period_start_date := period_profile_rec.period2_start_date;
2914 x_cur_period_end_date := period_profile_rec.period2_end_date;
2915 elsif l_current_date >= period_profile_rec.period3_start_date and l_current_date <= period_profile_rec.period3_end_date then
2916 x_cur_period_number := 3;
2917 x_cur_period_name := period_profile_rec.period_name3;
2918 x_cur_period_start_date := period_profile_rec.period3_start_date;
2919 x_cur_period_end_date := period_profile_rec.period3_end_date;
2920 elsif l_current_date >= period_profile_rec.period4_start_date and l_current_date <= period_profile_rec.period4_end_date then
2921 x_cur_period_number := 4;
2922 x_cur_period_name := period_profile_rec.period_name4;
2923 x_cur_period_start_date := period_profile_rec.period4_start_date;
2924 x_cur_period_end_date := period_profile_rec.period4_end_date;
2925 elsif l_current_date >= period_profile_rec.period5_start_date and l_current_date <= period_profile_rec.period5_end_date then
2926 x_cur_period_number := 5;
2927 x_cur_period_name := period_profile_rec.period_name5;
2928 x_cur_period_start_date := period_profile_rec.period5_start_date;
2929 x_cur_period_end_date := period_profile_rec.period5_end_date;
2930 elsif l_current_date >= period_profile_rec.period6_start_date and l_current_date <= period_profile_rec.period6_end_date then
2931 x_cur_period_number := 6;
2932 x_cur_period_name := period_profile_rec.period_name6;
2933 x_cur_period_start_date := period_profile_rec.period6_start_date;
2934 x_cur_period_end_date := period_profile_rec.period6_end_date;
2935 elsif l_current_date >= period_profile_rec.period7_start_date and l_current_date <= period_profile_rec.period7_end_date then
2936 x_cur_period_number := 7;
2937 x_cur_period_name := period_profile_rec.period_name7;
2938 x_cur_period_start_date := period_profile_rec.period7_start_date;
2939 x_cur_period_end_date := period_profile_rec.period7_end_date;
2940 elsif l_current_date >= period_profile_rec.period8_start_date and l_current_date <= period_profile_rec.period8_end_date then
2941 x_cur_period_number := 8;
2942 x_cur_period_name := period_profile_rec.period_name8;
2943 x_cur_period_start_date := period_profile_rec.period8_start_date;
2944 x_cur_period_end_date := period_profile_rec.period8_end_date;
2945 elsif l_current_date >= period_profile_rec.period9_start_date and l_current_date <= period_profile_rec.period9_end_date then
2946 x_cur_period_number := 9;
2947 x_cur_period_name := period_profile_rec.period_name9;
2948 x_cur_period_start_date := period_profile_rec.period9_start_date;
2949 x_cur_period_end_date := period_profile_rec.period9_end_date;
2950 elsif l_current_date >= period_profile_rec.period10_start_date and l_current_date <= period_profile_rec.period10_end_date then
2951 x_cur_period_number := 10;
2952 x_cur_period_name := period_profile_rec.period_name10;
2953 x_cur_period_start_date := period_profile_rec.period10_start_date;
2954 x_cur_period_end_date := period_profile_rec.period10_end_date;
2955 elsif l_current_date >= period_profile_rec.period11_start_date and l_current_date <= period_profile_rec.period11_end_date then
2956 x_cur_period_number := 11;
2957 x_cur_period_name := period_profile_rec.period_name11;
2958 x_cur_period_start_date := period_profile_rec.period11_start_date;
2959 x_cur_period_end_date := period_profile_rec.period11_end_date;
2960 elsif l_current_date >= period_profile_rec.period12_start_date and l_current_date <= period_profile_rec.period12_end_date then
2961 x_cur_period_number := 12;
2962 x_cur_period_name := period_profile_rec.period_name12;
2963 x_cur_period_start_date := period_profile_rec.period12_start_date;
2964 x_cur_period_end_date := period_profile_rec.period12_end_date;
2965 elsif l_current_date >= period_profile_rec.period13_start_date and l_current_date <= period_profile_rec.period13_end_date then
2966 x_cur_period_number := 13;
2967 x_cur_period_name := period_profile_rec.period_name13;
2968 x_cur_period_start_date := period_profile_rec.period13_start_date;
2969 x_cur_period_end_date := period_profile_rec.period13_end_date;
2970 elsif l_current_date >= period_profile_rec.period14_start_date and l_current_date <= period_profile_rec.period14_end_date then
2971 x_cur_period_number := 14;
2972 x_cur_period_name := period_profile_rec.period_name14;
2973 x_cur_period_start_date := period_profile_rec.period14_start_date;
2974 x_cur_period_end_date := period_profile_rec.period14_end_date;
2975 elsif l_current_date >= period_profile_rec.period15_start_date and l_current_date <= period_profile_rec.period15_end_date then
2976 x_cur_period_number := 15;
2977 x_cur_period_name := period_profile_rec.period_name15;
2978 x_cur_period_start_date := period_profile_rec.period15_start_date;
2979 x_cur_period_end_date := period_profile_rec.period15_end_date;
2980 elsif l_current_date >= period_profile_rec.period16_start_date and l_current_date <= period_profile_rec.period16_end_date then
2981 x_cur_period_number := 16;
2982 x_cur_period_name := period_profile_rec.period_name16;
2983 x_cur_period_start_date := period_profile_rec.period16_start_date;
2984 x_cur_period_end_date := period_profile_rec.period16_end_date;
2985 elsif l_current_date >= period_profile_rec.period17_start_date and l_current_date <= period_profile_rec.period17_end_date then
2986 x_cur_period_number := 17;
2987 x_cur_period_name := period_profile_rec.period_name17;
2988 x_cur_period_start_date := period_profile_rec.period17_start_date;
2989 x_cur_period_end_date := period_profile_rec.period17_end_date;
2990 elsif l_current_date >= period_profile_rec.period18_start_date and l_current_date <= period_profile_rec.period18_end_date then
2991 x_cur_period_number := 18;
2992 x_cur_period_name := period_profile_rec.period_name18;
2993 x_cur_period_start_date := period_profile_rec.period18_start_date;
2994 x_cur_period_end_date := period_profile_rec.period18_end_date;
2995 elsif l_current_date >= period_profile_rec.period19_start_date and l_current_date <= period_profile_rec.period19_end_date then
2996 x_cur_period_number := 19;
2997 x_cur_period_name := period_profile_rec.period_name19;
2998 x_cur_period_start_date := period_profile_rec.period19_start_date;
2999 x_cur_period_end_date := period_profile_rec.period19_end_date;
3000 elsif l_current_date >= period_profile_rec.period20_start_date and l_current_date <= period_profile_rec.period20_end_date then
3001 x_cur_period_number := 20;
3002 x_cur_period_name := period_profile_rec.period_name20;
3003 x_cur_period_start_date := period_profile_rec.period20_start_date;
3004 x_cur_period_end_date := period_profile_rec.period20_end_date;
3005 elsif l_current_date >= period_profile_rec.period21_start_date and l_current_date <= period_profile_rec.period21_end_date then
3006 x_cur_period_number := 21;
3007 x_cur_period_name := period_profile_rec.period_name21;
3008 x_cur_period_start_date := period_profile_rec.period21_start_date;
3009 x_cur_period_end_date := period_profile_rec.period21_end_date;
3010 elsif l_current_date >= period_profile_rec.period22_start_date and l_current_date <= period_profile_rec.period22_end_date then
3011 x_cur_period_number := 22;
3012 x_cur_period_name := period_profile_rec.period_name22;
3013 x_cur_period_start_date := period_profile_rec.period22_start_date;
3014 x_cur_period_end_date := period_profile_rec.period22_end_date;
3015 elsif l_current_date >= period_profile_rec.period23_start_date and l_current_date <= period_profile_rec.period23_end_date then
3016 x_cur_period_number := 23;
3017 x_cur_period_name := period_profile_rec.period_name23;
3018 x_cur_period_start_date := period_profile_rec.period23_start_date;
3019 x_cur_period_end_date := period_profile_rec.period23_end_date;
3020 elsif l_current_date >= period_profile_rec.period24_start_date and l_current_date <= period_profile_rec.period24_end_date then
3021 x_cur_period_number := 24;
3022 x_cur_period_name := period_profile_rec.period_name24;
3023 x_cur_period_start_date := period_profile_rec.period24_start_date;
3024 x_cur_period_end_date := period_profile_rec.period24_end_date;
3025 elsif l_current_date >= period_profile_rec.period25_start_date and l_current_date <= period_profile_rec.period25_end_date then
3026 x_cur_period_number := 25;
3027 x_cur_period_name := period_profile_rec.period_name25;
3028 x_cur_period_start_date := period_profile_rec.period25_start_date;
3029 x_cur_period_end_date := period_profile_rec.period25_end_date;
3030 elsif l_current_date >= period_profile_rec.period26_start_date and l_current_date <= period_profile_rec.period26_end_date then
3031 x_cur_period_number := 26;
3032 x_cur_period_name := period_profile_rec.period_name26;
3033 x_cur_period_start_date := period_profile_rec.period26_start_date;
3034 x_cur_period_end_date := period_profile_rec.period26_end_date;
3035 elsif l_current_date >= period_profile_rec.period27_start_date and l_current_date <= period_profile_rec.period27_end_date then
3036 x_cur_period_number := 27;
3037 x_cur_period_name := period_profile_rec.period_name27;
3038 x_cur_period_start_date := period_profile_rec.period27_start_date;
3039 x_cur_period_end_date := period_profile_rec.period27_end_date;
3040 elsif l_current_date >= period_profile_rec.period28_start_date and l_current_date <= period_profile_rec.period28_end_date then
3041 x_cur_period_number := 28;
3042 x_cur_period_name := period_profile_rec.period_name28;
3043 x_cur_period_start_date := period_profile_rec.period28_start_date;
3044 x_cur_period_end_date := period_profile_rec.period28_end_date;
3045 elsif l_current_date >= period_profile_rec.period29_start_date and l_current_date <= period_profile_rec.period29_end_date then
3046 x_cur_period_number := 29;
3047 x_cur_period_name := period_profile_rec.period_name29;
3048 x_cur_period_start_date := period_profile_rec.period29_start_date;
3049 x_cur_period_end_date := period_profile_rec.period29_end_date;
3050 elsif l_current_date >= period_profile_rec.period30_start_date and l_current_date <= period_profile_rec.period30_end_date then
3051 x_cur_period_number := 30;
3052 x_cur_period_name := period_profile_rec.period_name30;
3053 x_cur_period_start_date := period_profile_rec.period30_start_date;
3054 x_cur_period_end_date := period_profile_rec.period30_end_date;
3055 elsif l_current_date >= period_profile_rec.period31_start_date and l_current_date <= period_profile_rec.period31_end_date then
3056 x_cur_period_number := 31;
3057 x_cur_period_name := period_profile_rec.period_name31;
3058 x_cur_period_start_date := period_profile_rec.period31_start_date;
3059 x_cur_period_end_date := period_profile_rec.period31_end_date;
3060 elsif l_current_date >= period_profile_rec.period32_start_date and l_current_date <= period_profile_rec.period32_end_date then
3061 x_cur_period_number := 32;
3062 x_cur_period_name := period_profile_rec.period_name32;
3063 x_cur_period_start_date := period_profile_rec.period32_start_date;
3064 x_cur_period_end_date := period_profile_rec.period32_end_date;
3065 elsif l_current_date >= period_profile_rec.period33_start_date and l_current_date <= period_profile_rec.period33_end_date then
3066 x_cur_period_number := 33;
3067 x_cur_period_name := period_profile_rec.period_name33;
3068 x_cur_period_start_date := period_profile_rec.period33_start_date;
3069 x_cur_period_end_date := period_profile_rec.period33_end_date;
3070 elsif l_current_date >= period_profile_rec.period34_start_date and l_current_date <= period_profile_rec.period34_end_date then
3071 x_cur_period_number := 34;
3072 x_cur_period_name := period_profile_rec.period_name34;
3073 x_cur_period_start_date := period_profile_rec.period34_start_date;
3074 x_cur_period_end_date := period_profile_rec.period34_end_date;
3075 elsif l_current_date >= period_profile_rec.period35_start_date and l_current_date <= period_profile_rec.period35_end_date then
3076 x_cur_period_number := 35;
3077 x_cur_period_name := period_profile_rec.period_name35;
3078 x_cur_period_start_date := period_profile_rec.period35_start_date;
3079 x_cur_period_end_date := period_profile_rec.period35_end_date;
3080 elsif l_current_date >= period_profile_rec.period36_start_date and l_current_date <= period_profile_rec.period36_end_date then
3081 x_cur_period_number := 36;
3082 x_cur_period_name := period_profile_rec.period_name36;
3083 x_cur_period_start_date := period_profile_rec.period36_start_date;
3084 x_cur_period_end_date := period_profile_rec.period36_end_date;
3085 elsif l_current_date >= period_profile_rec.period37_start_date and l_current_date <= period_profile_rec.period37_end_date then
3086 x_cur_period_number := 37;
3087 x_cur_period_name := period_profile_rec.period_name37;
3088 x_cur_period_start_date := period_profile_rec.period37_start_date;
3089 x_cur_period_end_date := period_profile_rec.period37_end_date;
3090 elsif l_current_date >= period_profile_rec.period38_start_date and l_current_date <= period_profile_rec.period38_end_date then
3091 x_cur_period_number := 38;
3092 x_cur_period_name := period_profile_rec.period_name38;
3093 x_cur_period_start_date := period_profile_rec.period38_start_date;
3094 x_cur_period_end_date := period_profile_rec.period38_end_date;
3095 elsif l_current_date >= period_profile_rec.period39_start_date and l_current_date <= period_profile_rec.period39_end_date then
3096 x_cur_period_number := 39;
3097 x_cur_period_name := period_profile_rec.period_name39;
3098 x_cur_period_start_date := period_profile_rec.period39_start_date;
3099 x_cur_period_end_date := period_profile_rec.period39_end_date;
3100 elsif l_current_date >= period_profile_rec.period40_start_date and l_current_date <= period_profile_rec.period40_end_date then
3101 x_cur_period_number := 40;
3102 x_cur_period_name := period_profile_rec.period_name40;
3103 x_cur_period_start_date := period_profile_rec.period40_start_date;
3104 x_cur_period_end_date := period_profile_rec.period40_end_date;
3105 elsif l_current_date >= period_profile_rec.period41_start_date and l_current_date <= period_profile_rec.period41_end_date then
3106 x_cur_period_number := 41;
3107 x_cur_period_name := period_profile_rec.period_name41;
3108 x_cur_period_start_date := period_profile_rec.period41_start_date;
3109 x_cur_period_end_date := period_profile_rec.period41_end_date;
3110 elsif l_current_date >= period_profile_rec.period42_start_date and l_current_date <= period_profile_rec.period42_end_date then
3111 x_cur_period_number := 42;
3112 x_cur_period_name := period_profile_rec.period_name42;
3113 x_cur_period_start_date := period_profile_rec.period42_start_date;
3114 x_cur_period_end_date := period_profile_rec.period42_end_date;
3115 elsif l_current_date >= period_profile_rec.period43_start_date and l_current_date <= period_profile_rec.period43_end_date then
3116 x_cur_period_number := 43;
3117 x_cur_period_name := period_profile_rec.period_name43;
3118 x_cur_period_start_date := period_profile_rec.period43_start_date;
3119 x_cur_period_end_date := period_profile_rec.period43_end_date;
3120 elsif l_current_date >= period_profile_rec.period44_start_date and l_current_date <= period_profile_rec.period44_end_date then
3121 x_cur_period_number := 44;
3122 x_cur_period_name := period_profile_rec.period_name44;
3123 x_cur_period_start_date := period_profile_rec.period44_start_date;
3124 x_cur_period_end_date := period_profile_rec.period44_end_date;
3125 elsif l_current_date >= period_profile_rec.period45_start_date and l_current_date <= period_profile_rec.period45_end_date then
3126 x_cur_period_number := 45;
3127 x_cur_period_name := period_profile_rec.period_name45;
3128 x_cur_period_start_date := period_profile_rec.period45_start_date;
3129 x_cur_period_end_date := period_profile_rec.period45_end_date;
3130 elsif l_current_date >= period_profile_rec.period46_start_date and l_current_date <= period_profile_rec.period46_end_date then
3131 x_cur_period_number := 46;
3132 x_cur_period_name := period_profile_rec.period_name46;
3133 x_cur_period_start_date := period_profile_rec.period46_start_date;
3134 x_cur_period_end_date := period_profile_rec.period46_end_date;
3135 elsif l_current_date >= period_profile_rec.period47_start_date and l_current_date <= period_profile_rec.period47_end_date then
3136 x_cur_period_number := 47;
3137 x_cur_period_name := period_profile_rec.period_name47;
3138 x_cur_period_start_date := period_profile_rec.period47_start_date;
3139 x_cur_period_end_date := period_profile_rec.period47_end_date;
3140 elsif l_current_date >= period_profile_rec.period48_start_date and l_current_date <= period_profile_rec.period48_end_date then
3141 x_cur_period_number := 48;
3142 x_cur_period_name := period_profile_rec.period_name48;
3143 x_cur_period_start_date := period_profile_rec.period48_start_date;
3144 x_cur_period_end_date := period_profile_rec.period48_end_date;
3145 elsif l_current_date >= period_profile_rec.period49_start_date and l_current_date <= period_profile_rec.period49_end_date then
3146 x_cur_period_number := 49;
3147 x_cur_period_name := period_profile_rec.period_name49;
3148 x_cur_period_start_date := period_profile_rec.period49_start_date;
3149 x_cur_period_end_date := period_profile_rec.period49_end_date;
3150 elsif l_current_date >= period_profile_rec.period50_start_date and l_current_date <= period_profile_rec.period50_end_date then
3151 x_cur_period_number := 50;
3152 x_cur_period_name := period_profile_rec.period_name50;
3153 x_cur_period_start_date := period_profile_rec.period50_start_date;
3154 x_cur_period_end_date := period_profile_rec.period50_end_date;
3155 elsif l_current_date >= period_profile_rec.period51_start_date and l_current_date <= period_profile_rec.period51_end_date then
3156 x_cur_period_number := 51;
3157 x_cur_period_name := period_profile_rec.period_name51;
3158 x_cur_period_start_date := period_profile_rec.period51_start_date;
3159 x_cur_period_end_date := period_profile_rec.period51_end_date;
3160 elsif l_current_date >= period_profile_rec.period52_start_date and l_current_date <= period_profile_rec.period52_end_date then
3161 x_cur_period_number := 52;
3162 x_cur_period_name := period_profile_rec.period_name52;
3163 x_cur_period_start_date := period_profile_rec.period52_start_date;
3164 x_cur_period_end_date := period_profile_rec.period52_end_date;
3165 else
3166 -- current date falls AFTER last period end date
3167 x_cur_period_number := -1;
3168 x_cur_period_name := null;
3169 x_cur_period_start_date := null;
3170 x_cur_period_end_date := null;
3171 end if;
3172 end if;
3173 close period_profile_csr;
3174 EXCEPTION
3175 WHEN OTHERS THEN
3176 FND_MSG_PUB.add_exc_msg
3177 ( p_pkg_name => 'PA_PRJ_PERIOD_PROFILE_UTILS.get_current_period_info'
3178 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
3179 RAISE;
3180 END get_current_period_info;
3181
3182
3183 function has_preceding_periods
3184 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE) RETURN VARCHAR2
3185 is
3186 l_return_value VARCHAR2(1);
3187 BEGIN
3188 l_return_value := 'N';
3189 select unique 'Y'
3190 into l_return_value
3191 from pa_budget_lines
3192 where budget_version_id = p_budget_version_id and
3193 bucketing_period_code = 'PD';
3194 return l_return_value;
3195 EXCEPTION
3196 WHEN NO_DATA_FOUND THEN
3197 return l_return_value;
3198 END has_preceding_periods;
3199
3200
3201 function has_succeeding_periods
3202 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE) RETURN VARCHAR2
3203 is
3204 l_return_value VARCHAR2(1);
3205 BEGIN
3206 l_return_value := 'N';
3207 select unique 'Y'
3208 into l_return_value
3209 from pa_budget_lines
3210 where budget_version_id = p_budget_version_id and
3211 bucketing_period_code = 'SD';
3212 return l_return_value;
3213 EXCEPTION
3214 WHEN NO_DATA_FOUND THEN
3215 return l_return_value;
3216 END has_succeeding_periods;
3217
3218 PROCEDURE UPDATE_BUDGET_VERSION(p_budget_version_id IN NUMBER,
3219 p_return_status IN VARCHAR2,
3220 p_project_id IN NUMBER,
3221 p_request_id IN NUMBER ) IS
3222 l_plan_proc_code pa_budget_versions.plan_processing_Code%type;
3223 BEGIN
3224 if p_return_status <> FND_API.G_RET_STS_SUCCESS then
3225 l_plan_proc_code := 'PPE';
3226 else
3227 l_plan_proc_code := 'PPG';
3228 end if;
3229 IF p_project_id IS NOT NULL THEN
3230 UPDATE PA_BUDGET_VERSIONS
3231 SET PLAN_PROCESSING_CODE = l_plan_proc_code,
3232 locked_by_person_id = NULL,
3233 record_version_number = nvl(record_version_number,0) + 1,
3234 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
3235 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3236 LAST_UPDATE_DATE = sysdate
3237 WHERE
3238 project_id = p_project_id and
3239 request_id = p_request_id and
3240 plan_processing_code = 'PPP';
3241 ELSE
3242 UPDATE PA_BUDGET_VERSIONS
3243 SET PLAN_PROCESSING_CODE = l_plan_proc_code,
3244 locked_by_person_id = NULL,
3245 record_version_number = nvl(record_version_number,0) + 1,
3246 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
3247 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3248 LAST_UPDATE_DATE = sysdate
3249 WHERE
3250 budget_version_id = p_budget_version_id;
3251 END IF;
3252 COMMIT;
3253 END;
3254
3255
3256 END PA_PRJ_PERIOD_PROFILE_UTILS;