[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.8 2009/05/21 12:15:37 vgovvala 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 /* Commented code from here for 7563735, locking will be done in the procedure Maintain_Prj_Period_Profile now.
1594 FORALL ii IN 1 .. l_bv_id_tab.COUNT
1595 UPDATE pa_budget_versions SET
1596 plan_processing_code = 'PPP',
1597 locked_by_person_id = -98,
1598 request_id = l_rpt_request_id,
1599 record_version_number = nvl(record_version_number,0) + 1,
1600 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1601 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1602 LAST_UPDATE_DATE = sysdate
1603 WHERE
1604 budget_version_id = l_bv_id_tab(ii) AND
1605 locked_by_person_id IS NULL; */
1606
1607 IF P_PA_DEBUG_MODE = 'Y' THEN
1608 PA_DEBUG.g_err_stage := 'Exception Report Request Id : ' ||
1609 LTRIM(TO_CHAR(l_rpt_request_id )) ;
1610 PA_DEBUG.log_Message( p_message => PA_DEBUG.g_err_stage,
1611 p_write_file => 'OUT',
1612 p_write_mode => 1);
1613 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
1614 END IF;
1615 END IF;
1616 x_conc_req_id := LTRIM(RTRIM(TO_CHAR(l_rpt_request_id)));
1617 END IF;
1618 IF x_return_Status = FND_API.G_RET_STS_SUCCESS THEN
1619 COMMIT;
1620 ELSE
1621 ROLLBACK;
1622 END IF;
1623
1624 EXCEPTION
1625 WHEN OTHERS THEN
1626 px_pa_end_date := nc_pa_end_date;
1627 px_pa_period_profile_id := nc_pa_period_profile_id;
1628 px_pa_number_of_periods := nc_pa_number_of_periods;
1629 px_gl_end_date := nc_gl_end_date;
1630 px_gl_period_profile_id := nc_gl_period_profile_id;
1631 px_gl_number_of_periods := nc_gl_number_of_periods;
1632 RAISE;
1633
1634 END Maintain_Prj_Profile_wrp;
1635
1636
1637 --###
1638 --Name: Get_Prj_Defaults
1639 --Type: Procedure
1640 --
1641 --Description:
1642 --Called subprograms: none
1643 --
1644 --
1645 --
1646 --History:
1647 -- 14-NOV-2001 SManivannan - Created
1648 --
1649 -- 17-MAR-03 jwhite - Bug 2589885
1650 -- Add two new parameters to Get_Prj_Defaults:
1651 -- - x_prj_start_date OUT VARCHAR2
1652 -- - x_prj_end_date OUT VARCHAR2
1653 -- Also, add code to populate the project
1654 -- start- and end-date parameters.
1655 --
1656 -- 03-JUN-03 vejayara - Bug2987076 - Start period info assigned as
1657 -- end period whenever start period is derived.
1658
1659
1660
1661 Procedure Get_Prj_Defaults( p_project_id IN NUMBER,
1662 p_info_flag IN VARCHAR2,
1663 p_create_defaults IN VARCHAR2, --Y or N
1664 x_gl_start_period OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1665 x_gl_end_period OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1666 x_gl_start_Date OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1667 x_pa_start_period OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1668 x_pa_end_period OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1669 x_pa_start_date OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1670 x_plan_version_exists_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1671 x_prj_start_date OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1672 x_prj_end_date OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1673 ) IS
1674
1675 l_prj_Start_date DATE;
1676 l_prj_completion_date DATE;
1677 l_prj_org_id NUMBER;
1678 l_count NUMBER := 0;
1679 l_profile_count NUMBER := 0;
1680
1681 l_dummy_pa_end_date DATE;
1682 l_dummy_gl_end_date DATE;
1683 l_pd_gl_end_date DATE;
1684 l_pd_pa_end_date DATE;
1685
1686 l_dummy_pa_period_profile_id pa_proj_period_profiles.period_profile_id%TYPE;
1687 l_dummy_gl_period_profile_id pa_proj_period_profiles.period_profile_id%TYPE;
1688 l_dummy_pa_number_of_periods NUMBER;
1689 l_dummy_gl_number_of_periods NUMBER;
1690 l_dummy_pa_plan_start_date DATE;
1691 l_dummy_pa_plan_end_date DATE;
1692 l_dummy_gl_plan_start_date DATE;
1693 l_dummy_gl_plan_end_date DATE;
1694 l_dummy_pa_period_profile_type pa_proj_period_profiles.period_profile_type%TYPE;
1695 l_dummy_gl_period_profile_type pa_proj_period_profiles.period_profile_type%TYPE;
1696
1697 /* Commenting for bug 7578853
1698 CURSOR end_period_cur(c_period_set_name varchar2,
1699 c_period_type varchar2,
1700 c_start_Date date ) IS
1701 SELECT Period_Name,Start_Date,
1702 End_Date FROM Gl_Periods WHERE
1703 Period_Set_Name = c_period_set_name AND
1704 Period_Type = c_period_type AND Start_Date > c_start_date AND
1705 Adjustment_Period_Flag = 'N' AND ROWNUM < 52
1706 ORDER BY Start_Date;
1707 */
1708
1709 -- Added for bug 7578853
1710 CURSOR end_period_cur(c_period_set_name varchar2,
1711 c_period_type varchar2,
1712 c_start_Date date ) IS
1713 SELECT Period_Name,Start_Date, End_Date
1714 FROM
1715 (
1716 SELECT Period_Name,Start_Date, End_Date
1717 FROM Gl_Periods
1718 WHERE Period_Set_Name = c_period_set_name AND
1719 Period_Type = c_period_type AND Start_Date > c_start_date AND
1720 Adjustment_Period_Flag = 'N'
1721 ORDER BY Start_Date
1722 )
1723 WHERE ROWNUM < 52;
1724
1725 l_period_set_name Gl_Periods.Period_Set_Name%TYPE;
1726 l_gl_period_type Gl_Periods.Period_Type%TYPE;
1727 l_pa_period_type Gl_Periods.Period_Type%TYPE;
1728
1729 l_return_status VARCHAR2(2000);
1730 l_msg_count NUMBER := 0;
1731 l_msg_data VARCHAR2(2000);
1732 l_create_pa_profile VARCHAR2(1) := 'N';
1733 l_create_gl_profile VARCHAR2(1) := 'N';
1734 BEGIN
1735 x_gl_start_period := null;
1736 x_gl_end_period := null;
1737 x_gl_start_Date := null;
1738 x_pa_start_period := null;
1739 x_pa_end_period := null;
1740 x_pa_start_date := null;
1741 x_plan_version_exists_flag := 'N';
1742 SELECT COUNT(*) INTO l_count FROM Pa_Budget_Versions
1743 WHERE Project_Id = p_project_id AND
1744 Period_Profile_Id IS NOT NULL AND
1745 Period_Profile_Id > 0;
1746 IF l_count > 0 THEN
1747 x_plan_version_exists_flag := 'Y';
1748 END IF;
1749
1750 -- Bug 2589885, 17-MAR-03, jwhite, begin: --------------------------
1751
1752 /* -- Original Code -----------------------
1753 IF p_info_flag <> 'ALL' THEN
1754 RETURN;
1755 END IF;
1756 */
1757
1758 -- New Code, begin:-----------------------------
1759 -- MOVED original code BELOW since this procedure must now always run
1760 -- to populate the following filters for the page LOVs:
1761 -- 1) x_prj_start_date
1762 -- 2) x_prj_end_date
1763
1764
1765
1766 -- New Code, end: -----------------------------
1767
1768
1769 -- Bug 2589885, 17-MAR-03, jwhite, end: --------------------------
1770
1771
1772 SELECT COUNT(*) INTO l_profile_count
1773 FROM pa_proj_period_profiles
1774 WHERE
1775 project_id = p_project_id AND
1776 period_profile_type = 'FINANCIAL_PLANNING' AND
1777 plan_period_type = 'PA';
1778 IF l_profile_count = 0 THEN
1779 l_create_pa_profile := 'Y';
1780 END IF;
1781 SELECT COUNT(*) INTO l_profile_count
1782 FROM pa_proj_period_profiles
1783 WHERE
1784 project_id = p_project_id AND
1785 period_profile_type = 'FINANCIAL_PLANNING' AND
1786 plan_period_type = 'GL';
1787 IF l_profile_count = 0 THEN
1788 l_create_gl_profile := 'Y';
1789 END IF;
1790
1791
1792 BEGIN
1793
1794 -- Bug 2589885, 17-MAR-03, jwhite, begin: --------------------------
1795
1796 /* -- Original Code -----------------------
1797
1798 SELECT start_Date, completion_date, nvl(org_id,-99)
1799 INTO
1800 l_prj_start_date,l_prj_completion_date,l_prj_org_id FROM
1801 Pa_Projects_All WHERE
1802 Project_Id = p_project_id;
1803
1804 EXCEPTION
1805 WHEN NO_DATA_FOUND THEN
1806 RETURN;
1807 END;
1808
1809
1810
1811 */
1812
1813 -- New Code, begin:-----------------------------
1814
1815 /* Bug 3354518- FP.M -dbora- Modified the sql statement associated with the start_date select
1816 */
1817 SELECT nvl(start_Date, trunc(sysdate))
1818 , completion_date
1819 , nvl(org_id,-99)
1820 , decode (start_date, NULL, NULL, TO_CHAR(start_date,'rrrr/mm/dd') )
1821 , decode (completion_date, NULL, NULL, TO_CHAR(completion_date,'rrrr/mm/dd') )
1822 INTO l_prj_start_date
1823 ,l_prj_completion_date
1824 ,l_prj_org_id
1825 ,x_prj_start_date
1826 ,x_prj_end_date
1827 FROM Pa_Projects_All
1828 WHERE Project_Id = p_project_id;
1829
1830 EXCEPTION
1831 WHEN NO_DATA_FOUND THEN
1832 x_prj_start_date := NULL;
1833 x_prj_end_date := NULL;
1834 RETURN;
1835
1836 END; -- select start_date
1837
1838
1839 -- Original Code from ABOVE put here to make sure that the following
1840 -- OUT-parameters will always be populated for the page LOVs
1841 -- 1) x_prj_start_date
1842 -- 2) x_prj_end_date
1843
1844 IF p_info_flag <> 'ALL' THEN
1845 RETURN;
1846 END IF;
1847
1848
1849
1850 -- New Code, end: -----------------------------
1851
1852
1853 -- Bug 2589885, 17-MAR-03, jwhite, end: --------------------------
1854
1855
1856
1857 BEGIN
1858 SELECT imp.Period_Set_Name,imp.Pa_Period_Type,sob.Accounted_Period_Type
1859 INTO l_period_set_name , l_pa_period_type,l_gl_period_type
1860 FROM Pa_Implementations_All imp, Gl_Sets_Of_Books sob WHERE
1861 --NVL(imp.Org_Id,-99) = l_prj_org_id AND
1862 imp.Org_Id = l_prj_org_id AND -- Bug Ref # 6327662
1863 imp.Set_Of_Books_Id = sob.Set_Of_Books_Id;
1864 EXCEPTION
1865 WHEN NO_DATA_FOUND THEN
1866 RETURN;
1867 END;
1868
1869 IF l_prj_start_date IS NOT NULL THEN
1870 BEGIN
1871 SELECT gl.Period_Name,TO_CHAR(glp.start_Date,'rrrr/mm/dd'),
1872 glp.end_date
1873 INTO x_gl_start_period,x_gl_start_date,
1874 l_pd_gl_end_date
1875 FROM
1876 Gl_Date_Period_Map gl,
1877 gl_periods glp WHERE
1878 gl.Period_Set_Name = l_period_set_name AND
1879 gl.Period_Type = l_gl_period_type AND
1880 gl.Accounting_Date = l_prj_start_date AND
1881 glp.period_set_name = gl.Period_Set_Name AND
1882 glp.Period_Type = gl.Period_Type AND
1883 glp.adjustment_period_flag = 'N' AND
1884 glp.period_name = gl.period_name;
1885
1886 /* Assigned start period info as end period info for bug# 2987076 */
1887 x_gl_end_period := x_gl_start_period;
1888 l_dummy_gl_end_date := l_pd_gl_end_Date;
1889 /* modified the above assignment from x_gl_start_date to
1890 period end date (l_pd_gl_end_date ) for bug 3045693 */
1891
1892 EXCEPTION
1893 WHEN NO_DATA_FOUND THEN
1894 NULL;
1895 END;
1896 BEGIN
1897 SELECT gl.Period_Name,TO_CHAR(glp.start_Date,'rrrr/mm/dd'),
1898 glp.end_date
1899 INTO x_pa_start_period,x_pa_start_date,
1900 l_pd_pa_end_date
1901 FROM
1902 Gl_Date_Period_Map gl,
1903 gl_periods glp WHERE
1904 gl.Period_Set_Name = l_period_set_name AND
1905 gl.Period_Type = l_pa_period_type AND
1906 gl.Accounting_Date = l_prj_start_date AND
1907 glp.period_set_name = gl.Period_Set_Name AND
1908 glp.Period_Type = gl.Period_Type AND
1909 glp.adjustment_period_flag = 'N' AND
1910 glp.period_name = gl.period_name;
1911
1912 /* Assigned start period info as end period info for bug# 2987076 */
1913 x_pa_end_period := x_pa_start_period;
1914 l_dummy_pa_end_date := l_pd_pa_end_date;
1915 /* modified the above assignment from x_pa_start_date to
1916 period end date (l_pd_pa_end_date ) for bug 3045693 */
1917 EXCEPTION
1918 WHEN NO_DATA_FOUND THEN
1919 NULL;
1920 END;
1921 /* setting the end periods */
1922 /* check for prj_completion date to null removed
1923 for bug 2581913 */
1924 FOR cur_rec IN end_period_cur(l_period_Set_name,
1925 l_pa_period_type,
1926 l_prj_start_Date )
1927 LOOP
1928 IF cur_rec.start_date > l_prj_completion_date AND
1929 l_prj_completion_date IS NOT NULL THEN
1930 EXIT;
1931 END IF;
1932 x_pa_end_period := cur_rec.period_name;
1933
1934 /* review changes. msoundra 02-JAN-2003.
1935 End date should not be passed as NULL. If passed as NULL,
1936 the default profile would be created for the maximum periods
1937 ( 52 or less ) regardless of the proj completion date. */
1938
1939 l_dummy_pa_end_date := cur_rec.end_date;
1940 END LOOP;
1941
1942 FOR cur_rec IN end_period_cur(l_period_Set_name,
1943 l_gl_period_type,
1944 l_prj_start_Date )
1945 LOOP
1946 IF cur_rec.start_date > l_prj_completion_date AND
1947 l_prj_completion_date IS NOT NULL THEN
1948 EXIT;
1949 END IF;
1950 x_gl_end_period := cur_rec.period_name;
1951
1952 /* review changes. msoundra 02-JAN-2003.
1953 End date should not be passed as NULL. If passed as NULL,
1954 the default profile would be created for the maximum periods
1955 ( 52 or less ) regardless of the proj completion date. */
1956
1957 l_dummy_gl_end_date := cur_rec.end_date;
1958 END LOOP;
1959 END IF;
1960
1961 /* Bug 2689403 - If we are able derive a default for pa period profile dtls,
1962 create the same immediately and commit it before the period profile page is rendered.
1963 The period profile page would then fetch the queried record to be displayed on screen */
1964
1965 /* The default period profile info just derived needs to created (inserted) for the project
1966 only when p_create_defaults */
1967
1968 IF p_create_defaults = 'Y' THEN
1969
1970 IF x_pa_start_date IS NOT NULL AND
1971 l_create_pa_profile = 'Y' THEN
1972
1973 IF p_pa_debug_mode = 'Y' THEN
1974 pa_debug.g_err_stage := 'Calling Maintain_Prj_Period_Profile to create the PA period profile ....';
1975 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
1976 END IF;
1977
1978 Pa_Prj_Period_Profile_Utils.Maintain_Prj_Period_Profile(
1979 p_project_id => p_project_id,
1980 p_period_profile_type => 'FINANCIAL_PLANNING',
1981 p_plan_period_type => 'PA',
1982 p_period_set_name => l_period_set_name,
1983 p_gl_period_type => l_gl_period_type,
1984 p_pa_period_type => l_pa_period_type,
1985 p_start_date => to_date(x_pa_start_date,'rrrr/mm/dd'),
1986 px_end_date => l_dummy_pa_end_date ,
1987 px_period_profile_id => l_dummy_pa_period_profile_id,
1988 p_commit_flag => 'Y',
1989 px_number_of_periods => l_dummy_pa_number_of_periods,
1990 p_debug_mode => 'Y',
1991 p_add_msg_in_stack => 'Y',
1992 x_plan_start_date => l_dummy_pa_plan_start_date,
1993 x_plan_end_date => l_dummy_pa_plan_end_date,
1994 x_return_status => l_return_status,
1995 x_msg_count => l_msg_count,
1996 x_msg_data => l_msg_data );
1997
1998 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1999 IF p_pa_debug_mode = 'Y' THEN
2000 pa_debug.g_err_stage := ' Maintain_Prj_Period_Profile Errored for PA';
2001 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2002 END IF;
2003 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2004 END IF;
2005
2006 END IF;
2007
2008 /* Bug 2689403 - If we are able derive a default for gl period profile dtls,
2009 create the same immediately and commit it before the period profile page is rendered.
2010 The period profile page would then fetch the queried record to be displayed on screen */
2011
2012 IF x_gl_start_date IS NOT NULL AND
2013 l_create_gl_profile = 'Y' THEN
2014 IF p_pa_debug_mode = 'Y' THEN
2015 pa_debug.g_err_stage := 'Calling Maintain_Prj_Period_Profile to create the GL period profile ....';
2016 pa_debug.write(g_module_name,pa_debug.g_err_stage,3);
2017 END IF;
2018
2019 Pa_Prj_Period_Profile_Utils.Maintain_Prj_Period_Profile(
2020 p_project_id => p_project_id,
2021 p_period_profile_type => 'FINANCIAL_PLANNING',
2022 p_plan_period_type => 'GL',
2023 p_period_set_name => l_period_set_name,
2024 p_gl_period_type => l_gl_period_type,
2025 p_pa_period_type => l_pa_period_type,
2026 p_start_date => to_date(x_gl_start_date,'rrrr/mm/dd'),
2027 px_end_date => l_dummy_gl_end_date ,
2028 px_period_profile_id => l_dummy_gl_period_profile_id,
2029 p_commit_flag => 'Y',
2030 px_number_of_periods => l_dummy_gl_number_of_periods,
2031 p_debug_mode => 'Y',
2032 p_add_msg_in_stack => 'Y',
2033 x_plan_start_date => l_dummy_gl_plan_start_date,
2034 x_plan_end_date => l_dummy_gl_plan_end_date,
2035 x_return_status => l_return_status,
2036 x_msg_count => l_msg_count,
2037 x_msg_data => l_msg_data );
2038
2039 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2040 IF p_pa_debug_mode = 'Y' THEN
2041 pa_debug.g_err_stage := ' Maintain_Prj_Period_Profile Errored for PA';
2042 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2043 END IF;
2044 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2045 END IF;
2046 END IF;
2047 END IF;
2048 RETURN;
2049 EXCEPTION
2050
2051 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2052
2053 IF P_PA_DEBUG_MODE = 'Y' THEN
2054 pa_debug.g_err_stage:= l_msg_data;
2055 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2056 pa_debug.g_err_stage := sqlerrm;
2057 pa_debug.write(g_module_name,pa_debug.g_err_stage,5);
2058 END IF;
2059 RAISE;
2060 END Get_Prj_Defaults;
2061
2062 /*===================================================================
2063 This api returns the current period profile id,start period and end
2064 period for givenproject id, plan period type and period profile type
2065 ==================================================================*/
2066
2067 PROCEDURE Get_Curr_Period_Profile_Info(
2068 p_project_id IN VARCHAR2
2069 ,p_period_type IN VARCHAR2
2070 ,p_period_profile_type IN VARCHAR2
2071 ,x_period_profile_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2072 ,x_start_period OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2073 ,x_end_period OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2074 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2075 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
2076 ,x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
2077 AS
2078
2079
2080 l_return_status VARCHAR2(2000);
2081 l_msg_count NUMBER :=0;
2082 l_msg_data VARCHAR2(2000);
2083 l_data VARCHAR2(2000);
2084 l_msg_index_out NUMBER;
2085 l_debug_mode VARCHAR2(30);
2086
2087 l_period_profile_id pa_proj_period_profiles.period_profile_id%TYPE;
2088 l_start_period pa_proj_period_profiles.period_name1%TYPE;
2089 l_end_period pa_proj_period_profiles.profile_end_period_name%TYPE;
2090
2091 BEGIN
2092
2093 x_msg_count := 0;
2094 x_return_status := FND_API.G_RET_STS_SUCCESS;
2095
2096 pa_debug.set_err_stack('Get_Curr_Period_Profile_Info');
2097 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2098 l_debug_mode := NVL(l_debug_mode, 'Y');
2099 IF P_PA_DEBUG_MODE = 'Y' THEN
2100 pa_debug.set_process('Get_Curr_Period_Profile_Info: ' || 'PLSQL','LOG',l_debug_mode);
2101 END IF;
2102
2103 -- Check for not null parameters
2104
2105 pa_debug.g_err_stage := 'Checking for valid parameters:';
2106 IF P_PA_DEBUG_MODE = 'Y' THEN
2107 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,3);
2108 END IF;
2109
2110 IF (p_project_id IS NULL) OR
2111 (p_period_type NOT IN (PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_GL,PA_FP_CONSTANTS_PKG.G_PERIOD_TYPE_PA)) OR
2112 (p_period_profile_type IS NULL)
2113 THEN
2114
2115 pa_debug.g_err_stage := 'Project='||p_project_id;
2116 IF P_PA_DEBUG_MODE = 'Y' THEN
2117 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,5);
2118 END IF;
2119 pa_debug.g_err_stage := 'Period_type='||p_period_type;
2120 IF P_PA_DEBUG_MODE = 'Y' THEN
2121 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,5);
2122 END IF;
2123 pa_debug.g_err_stage := 'P_period_profile_type='||p_period_profile_type;
2124 IF P_PA_DEBUG_MODE = 'Y' THEN
2125 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,5);
2126 END IF;
2127
2128 PA_UTILS.ADD_MESSAGE(p_app_short_name=> 'PA',
2129 p_msg_name => 'PA_FP_INV_PARAM_PASSED');
2130
2131 RAISE PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc;
2132
2133 END IF;
2134
2135 pa_debug.g_err_stage := 'Parameter validation complete';
2136 IF P_PA_DEBUG_MODE = 'Y' THEN
2137 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,3);
2138 END IF;
2139
2140 --Fetch Profile Info
2141
2142 BEGIN
2143
2144 pa_debug.g_err_stage := 'Fetching Profile Info';
2145 IF P_PA_DEBUG_MODE = 'Y' THEN
2146 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,3);
2147 END IF;
2148
2149 SELECT period_profile_id
2150 ,period_name1
2151 ,profile_end_period_name
2152 INTO l_period_profile_id
2153 ,l_start_period
2154 ,l_end_period
2155 FROM pa_proj_period_profiles
2156 WHERE project_id = p_project_id
2157 AND current_flag = 'Y'
2158 AND period_profile_type = p_period_profile_type
2159 AND plan_period_type = p_period_type;
2160
2161 EXCEPTION
2162
2163 WHEN NO_DATA_FOUND THEN
2164
2165 --There is no current profile for project.return null
2166
2167 pa_debug.g_err_stage := 'Current period profile doesnt exist for project';
2168 IF P_PA_DEBUG_MODE = 'Y' THEN
2169 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,3);
2170 END IF;
2171
2172 l_period_profile_id := NULL;
2173 l_start_period := NULL;
2174 l_end_period := NULL;
2175
2176 END;
2177
2178 --Pass out_parameters to calling program
2179
2180 x_period_profile_id := l_period_profile_id;
2181 x_start_period := l_start_period;
2182 x_end_period := l_end_period;
2183
2184
2185 pa_debug.g_err_stage := ' Exiting Get_Curr_Period_Profile_Info';
2186 IF P_PA_DEBUG_MODE = 'Y' THEN
2187 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,3);
2188 END IF;
2189 pa_debug.reset_err_stack;
2190 EXCEPTION
2191
2192 WHEN PA_FP_CONSTANTS_PKG.Invalid_Arg_Exc THEN
2193
2194 l_msg_count := FND_MSG_PUB.count_msg;
2195
2196 IF l_msg_count = 1 THEN
2197
2198 PA_INTERFACE_UTILS_PUB.get_messages
2199 (p_encoded => FND_API.G_TRUE
2200 ,p_msg_index => 1
2201 ,p_msg_count => l_msg_count
2202 ,p_msg_data => l_msg_data
2203 ,p_data => l_data
2204 ,p_msg_index_out => l_msg_index_out);
2205
2206 x_msg_data := l_data;
2207 x_msg_count := l_msg_count;
2208
2209 ELSE
2210
2211 x_msg_count := l_msg_count;
2212
2213 END IF;
2214
2215 pa_debug.g_err_stage:='Invalid Arguments Passed';
2216 IF P_PA_DEBUG_MODE = 'Y' THEN
2217 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,5);
2218 END IF;
2219
2220 x_return_status:= FND_API.G_RET_STS_ERROR;
2221
2222 pa_debug.reset_err_stack;
2223
2224 RAISE;
2225
2226 WHEN Others THEN
2227
2228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2229 x_msg_count := 1;
2230 x_msg_data := SQLERRM;
2231
2232 FND_MSG_PUB.add_exc_msg( p_pkg_name=> 'PA_PRJ_PERIOD_PROFILE_UTILS'
2233 ,p_procedure_name => 'Get_Curr_Period_Profile_Info');
2234
2235 pa_debug.g_err_stage:='Unexpected Error' || SQLERRM;
2236 IF P_PA_DEBUG_MODE = 'Y' THEN
2237 pa_debug.write('Get_Curr_Period_Profile_Info: ' || g_module_name,pa_debug.g_err_stage,5);
2238 END IF;
2239
2240 pa_debug.reset_err_stack;
2241
2242 RAISE;
2243
2244 END Get_Curr_Period_Profile_Info;
2245
2246 /*
2247 NEED TO CUT THE FUNCTION AND THE PROCEDURE BELOW
2248 AND PASTE THESE IN THE PERIOD PROFILES PACKAGE
2249 */
2250 --This function is a local function and is not exposed to other APIs
2251 --This is used to calculate the amount type id based on the amount
2252 --type code passed to it
2253 FUNCTION GET_AMTTYPE_ID
2254 ( p_amt_typ_code IN pa_amount_types_b.amount_type_code%TYPE
2255 := NULL
2256 ) RETURN NUMBER IS
2257 l_amount_type_id pa_amount_types_b.amount_type_id%TYPE;
2258 l_amt_code pa_fp_org_fcst_gen_pub.char240_data_type_table;
2259 l_amt_id pa_fp_org_fcst_gen_pub.number_data_type_table;
2260
2261 l_debug_mode VARCHAR2(30);
2262
2263 CURSOR get_amt_det IS
2264 SELECT atb.amount_type_id
2265 ,atb.amount_type_code
2266 FROM pa_amount_types_b atb
2267 WHERE atb.amount_type_class = 'R';
2268
2269 l_stage number := 0;
2270
2271 BEGIN
2272 IF P_PA_DEBUG_MODE = 'Y' THEN
2273 pa_debug.init_err_stack('PA_PLAN_MATRIX.GET_AMTTYPE_ID');
2274 END IF;
2275
2276 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2277 l_debug_mode := NVL(l_debug_mode, 'Y');
2278
2279 IF P_PA_DEBUG_MODE = 'Y' THEN
2280 pa_debug.set_process('GET_AMTTYPE_ID: ' || 'PLSQL','LOG',l_debug_mode);
2281 END IF;
2282
2283 l_amount_type_id := -99;
2284
2285 IF l_amt_code.last IS NULL THEN
2286 OPEN get_amt_det;
2287 LOOP
2288 FETCH get_amt_det into l_amt_id(nvl(l_amt_id.last+1,1))
2289 ,l_amt_code(nvl(l_amt_code.last+1,1));
2290 EXIT WHEN get_amt_det%NOTFOUND;
2291 END LOOP;
2292 END IF;
2293
2294 IF l_amt_code.last IS NOT NULL THEN
2295 FOR i in l_amt_id.first..l_amt_id.last LOOP
2296 IF l_amt_code(i) = p_amt_typ_code THEN
2297 l_amount_type_id := l_amt_id(i);
2298 END IF;
2299 END LOOP;
2300 END IF;
2301 IF l_amount_type_id = -99 THEN
2302 pa_debug.g_err_stage := 'p_amt_typ_code ['||p_amt_typ_code ||']';
2303 IF P_PA_DEBUG_MODE = 'Y' THEN
2304 pa_debug.write_file('GET_AMTTYPE_ID: ' || pa_debug.g_err_stage);
2305 END IF;
2306 END IF;
2307 pa_debug.reset_err_stack;
2308 RETURN(l_amount_type_id);
2309
2310 EXCEPTION
2311 WHEN OTHERS THEN
2312 FND_MSG_PUB.add_exc_msg(
2313 p_pkg_name => 'PA_FP_ORG_FCST_GEN_PUB.get_amttype_id'
2314 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
2315
2316 IF P_PA_DEBUG_MODE = 'Y' THEN
2317 pa_debug.write_file('GET_AMTTYPE_ID: ' || SQLERRM);
2318 END IF;
2319 pa_debug.reset_err_stack;
2320 RAISE;
2321 END GET_AMTTYPE_ID;
2322
2323 --This API is called on refresh of period profiles.
2324 --The API deletes current record from the pa_proj_periods_denorm table
2325 --for the budget version passed to it. It then populates the
2326 --Pa_Fin_Plan_Lines_Tmp table from the records in the Pa_Budget_Lines
2327 --table. The API then calls the Maintain Plan Matrix API to populate
2328 --the budget lines table with preceding and succeeding period values and
2329 --inserts the current period amounts in the pa_proj_periods_denorm table
2330
2331 PROCEDURE Refresh_Period_Profile
2332 (
2333 p_budget_version_id IN NUMBER,
2334 p_period_profile_id IN NUMBER,
2335 p_project_id IN NUMBER,
2336 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2337 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2338 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2339 )
2340 IS
2341 -- Local Variable Declaration
2342 l_budget_version_id NUMBER;
2343 l_period_profile_id NUMBER;
2344 l_project_id NUMBER;
2345 l_version_type VARCHAR2(30);
2346 l_data_source VARCHAR2(30);
2347 l_debug_mode VARCHAR2(30);
2348 amt_rec PA_PLAN_MATRIX.AMOUNT_TYPE_TABTYP;
2349 l_request_id NUMBER;
2350 BEGIN
2351 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2352 -- Setting the Debug Statements
2353 fnd_profile.get('PA_DEBUG_MODE',l_debug_mode);
2354 l_debug_mode := NVL(l_debug_mode, 'N');
2355 IF P_PA_DEBUG_MODE = 'Y' THEN
2356 PA_DEBUG.Set_Curr_Function( p_function => 'Refresh_Period_Profile',
2357 p_debug_mode => l_debug_mode );
2358 END IF;
2359 x_return_status := FND_API.G_RET_STS_SUCCESS;
2360
2361 IF P_PA_DEBUG_MODE = 'Y' THEN
2362 PA_DEBUG.g_err_stage := 'Entering Refresh Period Profile ' ||
2363 'for Refreshing the Period Profile';
2364 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2365 PA_DEBUG.g_err_stage := 'Concurrent request id :' ||
2366 to_char(nvl(l_request_id,0));
2367 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2368 END IF;
2369
2370 -- Set the savepoint to return if any of the merges fail
2371 -- for control items
2372
2373 savepoint before_refresh_pd_profile;
2374
2375 --Checking for the budget version id to be null
2376 --If budget version id is null then no processing will take place as there is no
2377 --record in denorm for that budget version id. If no record then it is the case
2378 --of creating a new profile rather than refreshing an existing profile.
2379 IF p_budget_version_id IS NULL THEN
2380 IF P_PA_DEBUG_MODE = 'Y' THEN
2381 PA_DEBUG.g_err_stage := 'No Budget Version ID is specified ' ||
2382 'or budget version id is null';
2383 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2384 END IF;
2385 /* PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2386 p_msg_name => 'NULL_BDGT_VSN_ID');
2387 invalid message code, so commented */
2388 x_return_status := FND_API.G_RET_STS_ERROR;
2389 /* x_msg_data := 'NULL_BDGT_VSN_ID'; */
2390 IF P_PA_DEBUG_MODE = 'Y' THEN
2391 PA_DEBUG.Reset_Curr_Function;
2392 END IF;
2393 RETURN;
2394 END IF;
2395 -- Setting local variable values
2396 l_data_source := 'BUDGET_LINES';
2397 l_budget_version_id := p_budget_version_id;
2398 --l_period_profile_id := p_period_profile_id;
2399 l_version_type := NULL;
2400
2401 -- Fix for P1 bug 2682761
2402 -- Updating budget versions table for this budget version
2403 -- before calling call maintain plan matrix
2404
2405 UPDATE pa_budget_versions bv
2406 SET bv.period_profile_id = p_period_profile_id,
2407 record_version_number = nvl(record_version_number,0) + 1,
2408 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2409 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2410 LAST_UPDATE_DATE = sysdate
2411 WHERE bv.budget_version_id = l_budget_version_id
2412 AND bv.project_id = p_project_id;
2413
2414 /*
2415
2416 -- Calling the API to populate the Pa_Fin_Plan_Lines_Tmp table
2417 PA_FIN_PLAN_PUB.CALL_MAINTAIN_PLAN_MATRIX
2418 (
2419 p_budget_version_id => l_budget_version_id,
2420 p_data_source => l_data_source,
2421 x_return_status => x_return_status,
2422 x_msg_count => x_msg_count,
2423 x_msg_data => x_msg_data
2424 );
2425 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2426 ROLLBACK TO before_refresh_pd_profile;
2427 UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
2428 p_return_status => x_return_status,
2429 p_project_id => p_project_id,
2430 p_request_id => l_request_id );
2431 IF P_PA_DEBUG_MODE = 'Y' THEN
2432 PA_DEBUG.Reset_Curr_Function;
2433 END IF;
2434 RETURN;
2435 END IF;
2436
2437 --Calling the ROLL UP API for denorm amounts to aggregate all the records
2438 --The Roll up API that is being called, simply assumes that all parent
2439 --level records for the updated records are available in denorm table.
2440 --This API simply takes sum of amounts at child level records and
2441 --updates the amounts on the parents.
2442 PA_FP_ROLLUP_PKG.ROLLUP_DENORM_AMOUNTS
2443 (
2444 p_budget_version_id => l_budget_version_id
2445 ,x_return_status => x_return_status
2446 ,x_msg_count => x_msg_count
2447 ,x_msg_data => x_msg_data
2448 );
2449 Refresh period denorm API takes care of deleting all the
2450 period denorm records for the given budget version and
2451 then populate user entered and rollup records.
2452 */
2453
2454 PA_FP_ROLLUP_PKG.Refresh_Period_Denorm(
2455 p_budget_version_id => l_budget_version_id
2456 ,x_return_status => x_return_status
2457 ,x_msg_count => x_msg_count
2458 ,x_msg_data => x_msg_data );
2459
2460 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2461 ROLLBACK TO before_refresh_pd_profile;
2462 UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
2463 p_return_status => x_return_status,
2464 p_project_id => p_project_id,
2465 p_request_id => l_request_id );
2466 IF P_PA_DEBUG_MODE = 'Y' THEN
2467 PA_DEBUG.Reset_Curr_Function;
2468 END IF;
2469 RETURN;
2470 END IF;
2471
2472 /* updating budget version for Successful completion. */
2473 UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
2474 p_return_status => x_return_status,
2475 p_project_id => NULL,
2476 p_request_id => l_request_id );
2477
2478 COMMIT;
2479 EXCEPTION
2480 WHEN OTHERS THEN
2481 FND_MSG_PUB.add_exc_msg
2482 ( p_pkg_name => 'PA_PRJ_PERIOD_PROFILE_UTILS.refresh_period_profile'
2483 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
2484 IF P_PA_DEBUG_MODE = 'Y' THEN
2485 PA_DEBUG.g_err_stage := 'Unexpected error in refresh_period_profile ';
2486 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2487 END IF;
2488 ROLLBACK TO before_refresh_pd_profile;
2489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2490 UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
2491 p_return_status => x_return_status,
2492 p_project_id => p_project_id,
2493 p_request_id => l_request_id );
2494 IF P_PA_DEBUG_MODE = 'Y' THEN
2495 PA_DEBUG.Reset_Curr_Function;
2496 END IF;
2497 RAISE;
2498 END Refresh_Period_Profile;
2499
2500 PROCEDURE Wrapper_Refresh_Pd_Profile
2501 (
2502 errbuff OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2503 retcode OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2504 p_budget_version_id1 IN NUMBER,
2505 p_budget_version_id2 IN NUMBER,
2506 p_project_id IN NUMBER,
2507 p_refresh_option_code IN VARCHAR2,
2508 p_gl_period_profile_id IN NUMBER,
2509 p_pa_period_profile_id IN NUMBER,
2510 p_debug_mode IN VARCHAR2
2511 )
2512 IS
2513 -- Local Variable Declaration
2514 l_budget_version_id NUMBER;
2515 l_budget_version_id1 NUMBER;
2516 l_budget_version_id2 NUMBER;
2517 l_project_id NUMBER;
2518 l_refresh_option_code VARCHAR2(30);
2519 l_gl_period_profile_id NUMBER;
2520 l_pa_period_profile_id NUMBER;
2521 l_time_phased_code VARCHAR2(30);
2522 l_return_status VARCHAR2(2000);
2523 l_msg_count NUMBER;
2524 l_msg_data VARCHAR2(2000);
2525 l_count NUMBER;
2526 TYPE budget_version_id_tab IS TABLE OF PA_BUDGET_VERSIONS.budget_version_id%type
2527 INDEX BY BINARY_INTEGER;
2528 t_budget_version_id budget_version_id_tab;
2529 l_request_id NUMBER;
2530 l_locked_person_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2531 l_plan_proc_code_tab PA_PLSQL_DATATYPES.Char30TabTyp;
2532 l_req_id_tab PA_PLSQL_DATATYPES.IdTabTyp;
2533 --Bug 7563735 Locking the records in cursor
2534 cursor c is
2535 select budget_version_id
2536 FROM Pa_budget_versions
2537 WHERE project_id = l_project_id
2538 FOR UPDATE;
2539 BEGIN
2540 -- Setting the Debug Statements
2541 IF P_PA_DEBUG_MODE = 'Y' THEN
2542 PA_DEBUG.Set_Curr_Function( p_function => 'Wrapper_Refresh_Pd_Profile',
2543 p_debug_mode => p_debug_mode );
2544 END IF;
2545 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2546
2547 IF P_PA_DEBUG_MODE = 'Y' THEN
2548 PA_DEBUG.g_err_stage := 'Entering Refresh Period Profile WRAPPER ' ||
2549 'for Conc Request Id :'||to_char(nvl(l_Request_id,0));
2550 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2551 PA_DEBUG.g_err_stage := 'Parameters : ';
2552 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2553 PA_DEBUG.g_err_stage := 'Budget version id1 : '||
2554 to_char(nvl(p_budget_Version_id1,0));
2555 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2556 PA_DEBUG.g_err_stage := 'Budget version id2 : '||
2557 to_char(nvl(p_budget_Version_id2,0));
2558 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2559 PA_DEBUG.g_err_stage := 'Project Id : '||
2560 to_char(nvl(p_project_id,0));
2561 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2562 PA_DEBUG.g_err_stage := 'Refresh option code : '||
2563 nvl(p_refresh_option_code,'NULL');
2564 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2565 PA_DEBUG.g_err_stage := 'Pa Profile Id : '||
2566 to_char(nvl(p_pa_period_profile_id,0));
2567 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2568 PA_DEBUG.g_err_stage := 'Gl Profile Id : '||
2569 to_char(nvl(p_gl_period_profile_id,0));
2570 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2571 END IF;
2572 retcode := '0';
2573
2574 /* Setting local variable values */
2575 l_budget_version_id := NULL;
2576 l_budget_version_id1 := p_budget_version_id1;
2577 l_budget_version_id2 := p_budget_version_id2;
2578 l_project_id := p_project_id;
2579 l_refresh_option_code := p_refresh_option_code;
2580 l_gl_period_profile_id := p_gl_period_profile_id;
2581 l_pa_period_profile_id := p_pa_period_profile_id;
2582 l_time_phased_code := NULL;
2583 l_return_status := NULL;
2584 l_msg_count := NULL;
2585 l_msg_data := NULL;
2586 l_count := 1;
2587
2588 /* Deleting any records from the PL/SQL table */
2589 t_budget_version_id.DELETE;
2590 l_req_id_tab.DELETE;
2591 l_locked_person_id_tab.DELETE;
2592 l_plan_proc_code_tab.DELETE;
2593
2594 /* Changes for 7563735 - Setting the locked_by_person_id here, and opening cursor c so that sql lock is obtained on those*/
2595
2596 UPDATE pa_budget_versions SET
2597 plan_processing_code = 'PPP',
2598 locked_by_person_id = -98,
2599 request_id = l_request_id, /* 8338971 */
2600 record_version_number = nvl(record_version_number,0) + 1,
2601 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2602 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2603 LAST_UPDATE_DATE = sysdate
2604 WHERE
2605 project_id = l_project_id
2606 --locked_by_person_id IS NULL
2607 AND period_profile_id IS NOT NULL;
2608 /* added for locking the budget versions */
2609
2610 open c;
2611 close c;
2612 /* End Changes for 7563735 - Setting the locked_by_person_id here, and opening cursor c so that sql lock is obtained on those*/
2613 /* Would go inside this loop only if one of the budget version ids
2614 is not null */
2615 IF (l_budget_version_id1 IS NOT NULL OR l_budget_version_id2 IS NOT NULL) THEN
2616 -- For budget version id 1
2617 IF l_budget_version_id1 IS NOT NULL THEN
2618 t_budget_version_id(l_count) := l_budget_version_id1;
2619 select
2620 nvl(locked_by_person_id,0),
2621 nvl(plan_processing_code,'DUMMY'),
2622 nvl(request_id,0) into
2623 l_locked_person_id_tab(l_count),
2624 l_plan_proc_code_tab(l_count),
2625 l_req_id_tab(l_count)
2626 from pa_budget_versions where
2627 budget_version_id = l_budget_version_id1;
2628
2629
2630 l_count := l_count + 1;
2631 END IF;
2632 -- For budget version id 2
2633 IF l_budget_version_id2 IS NOT NULL THEN
2634 t_budget_version_id(l_count) := l_budget_version_id2;
2635 select
2636 nvl(locked_by_person_id,0),
2637 NVL(plan_processing_code,'DUMMY'),
2638 nvl(request_id,0) into
2639 l_locked_person_id_tab(l_count),
2640 l_plan_proc_code_tab(l_count),
2641 l_req_id_tab(l_count)
2642 from pa_budget_versions where
2643 budget_version_id = l_budget_version_id2;
2644
2645 l_count := l_count + 1;
2646 END IF;
2647 /* Checking for the refresh option code to be NOT null
2648 If refresh option code is null then the processing should
2649 transfer to checking the two budget version ids. If they are
2650 also null then the process should exit and do nothing in the program
2651
2652 Check for refresh option code. This WOULD BE NULL under following
2653 two conditions:
2654 1) If this API is being called from View Plans Page
2655 2) If the user chooses no plan version to refresh while refreshing period profiles */
2656
2657 ELSIF l_refresh_option_code IS NOT NULL THEN
2658 --Processing ahead only if project id is not null
2659 IF l_project_id IS NOT NULL THEN
2660 --Processing for refresh option code of ALL
2661 IF (l_refresh_option_code = 'ALL') THEN
2662 SELECT budget_version_id,
2663 nvl(locked_by_person_id,0),
2664 NVL(plan_processing_code,'DUMMY'),
2665 nvl(request_id,0)
2666 BULK COLLECT INTO
2667 t_budget_version_id,
2668 l_locked_person_id_tab,
2669 l_plan_proc_code_tab,
2670 l_req_id_tab
2671 FROM
2672 Pa_budget_versions
2673 WHERE
2674 project_id = l_project_id
2675 AND period_profile_id IS NOT NULL;
2676 ELSIF (l_refresh_option_code = 'SELECTED') THEN
2677 SELECT budget_version_id,
2678 nvl(locked_by_person_id,0),
2679 nvl(plan_processing_code,'DUMMY'),
2680 nvl(request_id,0)
2681 BULK COLLECT INTO
2682 t_budget_version_id,
2683 l_locked_person_id_tab,
2684 l_plan_proc_code_tab,
2685 l_req_id_tab
2686 FROM
2687 Pa_budget_versions
2688 WHERE
2689 project_id = l_project_id
2690 AND period_profile_id IS NOT NULL
2691 AND
2692 (
2693 (current_working_flag = 'Y' AND budget_status_code IN ('W','S'))
2694 OR (current_flag = 'Y' AND budget_status_code = 'B')
2695 OR (current_original_flag = 'Y' AND budget_status_code = 'B')
2696 );
2697 END IF;
2698 END IF;
2699 END IF;
2700 FOR l_cnt IN 1 .. t_budget_version_id.count
2701 LOOP
2702 l_budget_version_id := t_budget_version_id(l_cnt);
2703 BEGIN
2704 SELECT
2705 DECODE
2706 (po.fin_plan_preference_code,
2707 'COST_ONLY',po.cost_time_phased_code,
2708 'REVENUE_ONLY',po.revenue_time_phased_code,
2709 'COST_AND_REV_SAME',po.all_time_phased_code,
2710 DECODE
2711 (bv.version_type,
2712 'COST',po.cost_time_phased_code,
2713 'REVENUE',po.revenue_time_phased_code
2714 )
2715 )
2716 INTO
2717 l_time_phased_code
2718 FROM pa_budget_versions bv, pa_proj_fp_options po
2719 WHERE
2720 bv.budget_version_id = l_budget_version_id
2721 AND po.fin_plan_version_id = bv.budget_version_id
2722 AND po.fin_plan_option_level_code = 'PLAN_VERSION'
2723 AND bv.project_id = p_project_id
2724 AND po.project_id = bv.project_id;
2725 EXCEPTION
2726 WHEN NO_DATA_FOUND THEN
2727 IF P_PA_DEBUG_MODE = 'Y' THEN
2728 PA_DEBUG.g_err_stage := 'No data found while trying ' ||
2729 'to retrive data for time phased code FOR ' ||
2730 'l_refresh_option_code ALL';
2731 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2732 END IF;
2733 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
2734 p_msg_name => 'PA_FP_TM_PHSD_CODE_NOT_FOUND');
2735 retcode := '2';
2736 errbuff := 'PA_FP_TM_PHSD_CODE_NOT_FOUND';
2737 IF P_PA_DEBUG_MODE = 'Y' THEN
2738 PA_DEBUG.Reset_Curr_Function;
2739 END IF;
2740 END;
2741 IF (l_time_phased_code = 'P' and
2742 l_req_id_tab(l_cnt)= l_request_id and
2743 l_plan_proc_code_tab(l_cnt) = 'PPP' ) THEN
2744 --Knows that time phased code is PA
2745 --So, check for PA period profile passed to the API
2746 IF P_PA_DEBUG_MODE = 'Y' THEN
2747 PA_DEBUG.g_err_stage := 'calling PP refresh for ' ||
2748 'PA period : ';
2749 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2750 PA_DEBUG.g_err_stage := 'budget version id ' ||
2751 to_char(l_budget_version_id) ||
2752 ' period profile id :'||to_char(l_pa_period_profile_id);
2753 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2754 END IF;
2755 IF l_pa_period_profile_id IS NOT NULL THEN
2756 --Call the refresh period profile with PA profile ID
2757 REFRESH_PERIOD_PROFILE
2758 (
2759 p_budget_version_id => l_budget_version_id,
2760 p_period_profile_id => l_pa_period_profile_id,
2761 p_project_id => l_project_id,
2762 x_return_status => l_return_status,
2763 x_msg_count => l_msg_count,
2764 x_msg_data => l_msg_data
2765 ) ;
2766 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2767 l_msg_data := 'ERR_CALL_REFRESH_PERIOD_PROFILE';
2768 retcode := '2';
2769 errbuff := l_msg_data;
2770 END IF;
2771 END IF;
2772 ELSIF (l_time_phased_code = 'G' and
2773 l_req_id_tab(l_cnt)= l_request_id and
2774 l_plan_proc_code_tab(l_cnt) = 'PPP' ) THEN
2775 --Knows that time phased code is GL
2776 --So, check for GL period profile passed to the API
2777 IF l_gl_period_profile_id IS NOT NULL THEN
2778 --Call the refresh period profile with GL profile ID
2779 IF P_PA_DEBUG_MODE = 'Y' THEN
2780 PA_DEBUG.g_err_stage := 'calling PP refresh for ' ||
2781 'GL period : ';
2782 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2783 PA_DEBUG.g_err_stage := 'budget version id ' ||
2784 to_char(l_budget_version_id) ||
2785 ' period profile id :'||to_char(l_gl_period_profile_id);
2786 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2787 END IF;
2788 REFRESH_PERIOD_PROFILE
2789 (
2790 p_budget_version_id => l_budget_version_id,
2791 p_period_profile_id => l_gl_period_profile_id,
2792 p_project_id => l_project_id,
2793 x_return_status => l_return_status,
2794 x_msg_count => l_msg_count,
2795 x_msg_data => l_msg_data
2796 ) ;
2797 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2798 l_msg_data := 'ERR_CALL_REFRESH_PERIOD_PROFILE';
2799 retcode := '2';
2800 errbuff := l_msg_data;
2801 END IF;
2802 END IF;
2803 END IF;
2804 END LOOP;
2805 /* Changes for 7563735 - unsetting the locked_by_person_id here, depending on the return_status code*/
2806 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2807 UPDATE pa_budget_versions SET
2808 plan_processing_code = 'PPE',
2809 locked_by_person_id = null,
2810 record_version_number = nvl(record_version_number,0) + 1,
2811 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2812 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2813 LAST_UPDATE_DATE = sysdate,
2814 REQUEST_ID = l_request_id
2815 WHERE
2816 project_id = l_project_id AND
2817 locked_by_person_id ='-98'
2818 AND period_profile_id IS NOT NULL;
2819 ELSE
2820 UPDATE pa_budget_versions SET
2821 plan_processing_code = 'PPG',
2822 locked_by_person_id = null,
2823 record_version_number = nvl(record_version_number,0) + 1,
2824 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
2825 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
2826 LAST_UPDATE_DATE = sysdate,
2827 REQUEST_ID = l_request_id
2828 WHERE
2829 project_id = l_project_id AND
2830 locked_by_person_id ='-98'
2831 AND period_profile_id IS NOT NULL;
2832 end if;
2833
2834 commit;
2835 /*End Changes for 7563735 - unsetting the locked_by_person_id here, depending on the return_status code*/
2836 EXCEPTION
2837 WHEN OTHERS THEN
2838 FND_MSG_PUB.add_exc_msg
2839 ( p_pkg_name => 'PA_PRJ_PERIOD_PROFILE_UTILS.wrapper_refresh_pd_profile'
2840 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
2841 IF P_PA_DEBUG_MODE = 'Y' THEN
2842 PA_DEBUG.g_err_stage := 'Unexpected error in wrapper_refresh_pd_profile ';
2843 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
2844 END IF;
2845 retcode := '2';
2846 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2847 UPDATE_BUDGET_VERSION(p_budget_version_id => l_budget_version_id,
2848 p_return_status => l_return_status,
2849 p_project_id => p_project_id,
2850 p_request_id => l_request_id );
2851 IF P_PA_DEBUG_MODE = 'Y' THEN
2852 PA_DEBUG.Reset_Curr_Function;
2853 END IF;
2854 RAISE;
2855 END Wrapper_Refresh_Pd_Profile;
2856
2857 -- +-----------------------------------------------------------------
2858 -- PROCEDURE get_current_period_info - Revision History
2859 -- 06-JAN-03 dlai: created-takes as input period_profile_id, and returns
2860 -- the period in which sysdate falls
2861 -- 15-JAN-03 dlai: added a couple of flag values:
2862 -- x_cur_period_number = -2, then the current date is BEFORE first period start date
2863 -- x_cur_period_number = -1, then the current date is AFTER last period end date
2864 procedure get_current_period_info
2865 (p_period_profile_id IN pa_proj_period_profiles.period_profile_id%TYPE,
2866 x_cur_period_number OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2867 x_cur_period_name OUT NOCOPY pa_proj_period_profiles.period_name1%TYPE, --File.Sql.39 bug 4440895
2868 x_cur_period_start_date OUT NOCOPY pa_proj_period_profiles.period1_start_date%TYPE, --File.Sql.39 bug 4440895
2869 x_cur_period_end_date OUT NOCOPY pa_proj_period_profiles.period1_end_date%TYPE, --File.Sql.39 bug 4440895
2870 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2871 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2872 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2873 as
2874 l_current_date DATE;
2875 l_msg_data VARCHAR2(1000);
2876 l_msg_index_out NUMBER(15);
2877
2878 cursor period_profile_csr is
2879 select period_name1, period1_start_date, period1_end_date,
2880 period_name2, period2_start_date, period2_end_date,
2881 period_name3, period3_start_date, period3_end_date,
2882 period_name4, period4_start_date, period4_end_date,
2883 period_name5, period5_start_date, period5_end_date,
2884 period_name6, period6_start_date, period6_end_date,
2885 period_name7, period7_start_date, period7_end_date,
2886 period_name8, period8_start_date, period8_end_date,
2887 period_name9, period9_start_date, period9_end_date,
2888 period_name10, period10_start_date, period10_end_date,
2889 period_name11, period11_start_date, period11_end_date,
2890 period_name12, period12_start_date, period12_end_date,
2891 period_name13, period13_start_date, period13_end_date,
2892 period_name14, period14_start_date, period14_end_date,
2893 period_name15, period15_start_date, period15_end_date,
2894 period_name16, period16_start_date, period16_end_date,
2895 period_name17, period17_start_date, period17_end_date,
2896 period_name18, period18_start_date, period18_end_date,
2897 period_name19, period19_start_date, period19_end_date,
2898 period_name20, period20_start_date, period20_end_date,
2899 period_name21, period21_start_date, period21_end_date,
2900 period_name22, period22_start_date, period22_end_date,
2901 period_name23, period23_start_date, period23_end_date,
2902 period_name24, period24_start_date, period24_end_date,
2903 period_name25, period25_start_date, period25_end_date,
2904 period_name26, period26_start_date, period26_end_date,
2905 period_name27, period27_start_date, period27_end_date,
2906 period_name28, period28_start_date, period28_end_date,
2907 period_name29, period29_start_date, period29_end_date,
2908 period_name30, period30_start_date, period30_end_date,
2909 period_name31, period31_start_date, period31_end_date,
2910 period_name32, period32_start_date, period32_end_date,
2911 period_name33, period33_start_date, period33_end_date,
2912 period_name34, period34_start_date, period34_end_date,
2913 period_name35, period35_start_date, period35_end_date,
2914 period_name36, period36_start_date, period36_end_date,
2915 period_name37, period37_start_date, period37_end_date,
2916 period_name38, period38_start_date, period38_end_date,
2917 period_name39, period39_start_date, period39_end_date,
2918 period_name40, period40_start_date, period40_end_date,
2919 period_name41, period41_start_date, period41_end_date,
2920 period_name42, period42_start_date, period42_end_date,
2921 period_name43, period43_start_date, period43_end_date,
2922 period_name44, period44_start_date, period44_end_date,
2923 period_name45, period45_start_date, period45_end_date,
2924 period_name46, period46_start_date, period46_end_date,
2925 period_name47, period47_start_date, period47_end_date,
2926 period_name48, period48_start_date, period48_end_date,
2927 period_name49, period49_start_date, period49_end_date,
2928 period_name50, period50_start_date, period50_end_date,
2929 period_name51, period51_start_date, period51_end_date,
2930 period_name52, period52_start_date, period52_end_date
2931 from pa_proj_period_profiles
2932 where period_profile_id = p_period_profile_id;
2933 period_profile_rec period_profile_csr%ROWTYPE;
2934
2935 BEGIN
2936 x_return_status := FND_API.G_RET_STS_SUCCESS;
2937 select sysdate
2938 into l_current_date
2939 from dual;
2940 open period_profile_csr;
2941 fetch period_profile_csr into period_profile_rec;
2942 if period_profile_csr%NOTFOUND then
2943 x_return_status := FND_API.G_RET_STS_ERROR;
2944 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2945 p_msg_name => 'PA_FP_INVALID_PRJ_PROFILE');
2946 x_msg_count := fnd_msg_pub.count_msg;
2947 IF x_msg_count = 1 THEN
2948 PA_INTERFACE_UTILS_PUB.Get_Messages (
2949 p_encoded => FND_API.G_TRUE,
2950 p_msg_index => 1,
2951 p_msg_count => 1 ,
2952 p_msg_data => l_msg_data ,
2953 p_data => x_msg_data,
2954 p_msg_index_out => l_msg_index_out );
2955 END IF;
2956 else
2957 if l_current_date < period_profile_rec.period1_start_date then
2958 x_cur_period_number := -2;
2959 x_cur_period_name := null;
2960 x_cur_period_start_date := null;
2961 x_cur_period_end_date := null;
2962 elsif l_current_date >= period_profile_rec.period1_start_date and l_current_date <= period_profile_rec.period1_end_date then
2963 x_cur_period_number := 1;
2964 x_cur_period_name := period_profile_rec.period_name1;
2965 x_cur_period_start_date := period_profile_rec.period1_start_date;
2966 x_cur_period_end_date := period_profile_rec.period1_end_date;
2967 elsif l_current_date >= period_profile_rec.period2_start_date and l_current_date <= period_profile_rec.period2_end_date then
2968 x_cur_period_number := 2;
2969 x_cur_period_name := period_profile_rec.period_name2;
2970 x_cur_period_start_date := period_profile_rec.period2_start_date;
2971 x_cur_period_end_date := period_profile_rec.period2_end_date;
2972 elsif l_current_date >= period_profile_rec.period3_start_date and l_current_date <= period_profile_rec.period3_end_date then
2973 x_cur_period_number := 3;
2974 x_cur_period_name := period_profile_rec.period_name3;
2975 x_cur_period_start_date := period_profile_rec.period3_start_date;
2976 x_cur_period_end_date := period_profile_rec.period3_end_date;
2977 elsif l_current_date >= period_profile_rec.period4_start_date and l_current_date <= period_profile_rec.period4_end_date then
2978 x_cur_period_number := 4;
2979 x_cur_period_name := period_profile_rec.period_name4;
2980 x_cur_period_start_date := period_profile_rec.period4_start_date;
2981 x_cur_period_end_date := period_profile_rec.period4_end_date;
2982 elsif l_current_date >= period_profile_rec.period5_start_date and l_current_date <= period_profile_rec.period5_end_date then
2983 x_cur_period_number := 5;
2984 x_cur_period_name := period_profile_rec.period_name5;
2985 x_cur_period_start_date := period_profile_rec.period5_start_date;
2986 x_cur_period_end_date := period_profile_rec.period5_end_date;
2987 elsif l_current_date >= period_profile_rec.period6_start_date and l_current_date <= period_profile_rec.period6_end_date then
2988 x_cur_period_number := 6;
2989 x_cur_period_name := period_profile_rec.period_name6;
2990 x_cur_period_start_date := period_profile_rec.period6_start_date;
2991 x_cur_period_end_date := period_profile_rec.period6_end_date;
2992 elsif l_current_date >= period_profile_rec.period7_start_date and l_current_date <= period_profile_rec.period7_end_date then
2993 x_cur_period_number := 7;
2994 x_cur_period_name := period_profile_rec.period_name7;
2995 x_cur_period_start_date := period_profile_rec.period7_start_date;
2996 x_cur_period_end_date := period_profile_rec.period7_end_date;
2997 elsif l_current_date >= period_profile_rec.period8_start_date and l_current_date <= period_profile_rec.period8_end_date then
2998 x_cur_period_number := 8;
2999 x_cur_period_name := period_profile_rec.period_name8;
3000 x_cur_period_start_date := period_profile_rec.period8_start_date;
3001 x_cur_period_end_date := period_profile_rec.period8_end_date;
3002 elsif l_current_date >= period_profile_rec.period9_start_date and l_current_date <= period_profile_rec.period9_end_date then
3003 x_cur_period_number := 9;
3004 x_cur_period_name := period_profile_rec.period_name9;
3005 x_cur_period_start_date := period_profile_rec.period9_start_date;
3006 x_cur_period_end_date := period_profile_rec.period9_end_date;
3007 elsif l_current_date >= period_profile_rec.period10_start_date and l_current_date <= period_profile_rec.period10_end_date then
3008 x_cur_period_number := 10;
3009 x_cur_period_name := period_profile_rec.period_name10;
3010 x_cur_period_start_date := period_profile_rec.period10_start_date;
3011 x_cur_period_end_date := period_profile_rec.period10_end_date;
3012 elsif l_current_date >= period_profile_rec.period11_start_date and l_current_date <= period_profile_rec.period11_end_date then
3013 x_cur_period_number := 11;
3014 x_cur_period_name := period_profile_rec.period_name11;
3015 x_cur_period_start_date := period_profile_rec.period11_start_date;
3016 x_cur_period_end_date := period_profile_rec.period11_end_date;
3017 elsif l_current_date >= period_profile_rec.period12_start_date and l_current_date <= period_profile_rec.period12_end_date then
3018 x_cur_period_number := 12;
3019 x_cur_period_name := period_profile_rec.period_name12;
3020 x_cur_period_start_date := period_profile_rec.period12_start_date;
3021 x_cur_period_end_date := period_profile_rec.period12_end_date;
3022 elsif l_current_date >= period_profile_rec.period13_start_date and l_current_date <= period_profile_rec.period13_end_date then
3023 x_cur_period_number := 13;
3024 x_cur_period_name := period_profile_rec.period_name13;
3025 x_cur_period_start_date := period_profile_rec.period13_start_date;
3026 x_cur_period_end_date := period_profile_rec.period13_end_date;
3027 elsif l_current_date >= period_profile_rec.period14_start_date and l_current_date <= period_profile_rec.period14_end_date then
3028 x_cur_period_number := 14;
3029 x_cur_period_name := period_profile_rec.period_name14;
3030 x_cur_period_start_date := period_profile_rec.period14_start_date;
3031 x_cur_period_end_date := period_profile_rec.period14_end_date;
3032 elsif l_current_date >= period_profile_rec.period15_start_date and l_current_date <= period_profile_rec.period15_end_date then
3033 x_cur_period_number := 15;
3034 x_cur_period_name := period_profile_rec.period_name15;
3035 x_cur_period_start_date := period_profile_rec.period15_start_date;
3036 x_cur_period_end_date := period_profile_rec.period15_end_date;
3037 elsif l_current_date >= period_profile_rec.period16_start_date and l_current_date <= period_profile_rec.period16_end_date then
3038 x_cur_period_number := 16;
3039 x_cur_period_name := period_profile_rec.period_name16;
3040 x_cur_period_start_date := period_profile_rec.period16_start_date;
3041 x_cur_period_end_date := period_profile_rec.period16_end_date;
3042 elsif l_current_date >= period_profile_rec.period17_start_date and l_current_date <= period_profile_rec.period17_end_date then
3043 x_cur_period_number := 17;
3044 x_cur_period_name := period_profile_rec.period_name17;
3045 x_cur_period_start_date := period_profile_rec.period17_start_date;
3046 x_cur_period_end_date := period_profile_rec.period17_end_date;
3047 elsif l_current_date >= period_profile_rec.period18_start_date and l_current_date <= period_profile_rec.period18_end_date then
3048 x_cur_period_number := 18;
3049 x_cur_period_name := period_profile_rec.period_name18;
3050 x_cur_period_start_date := period_profile_rec.period18_start_date;
3051 x_cur_period_end_date := period_profile_rec.period18_end_date;
3052 elsif l_current_date >= period_profile_rec.period19_start_date and l_current_date <= period_profile_rec.period19_end_date then
3053 x_cur_period_number := 19;
3054 x_cur_period_name := period_profile_rec.period_name19;
3055 x_cur_period_start_date := period_profile_rec.period19_start_date;
3056 x_cur_period_end_date := period_profile_rec.period19_end_date;
3057 elsif l_current_date >= period_profile_rec.period20_start_date and l_current_date <= period_profile_rec.period20_end_date then
3058 x_cur_period_number := 20;
3059 x_cur_period_name := period_profile_rec.period_name20;
3060 x_cur_period_start_date := period_profile_rec.period20_start_date;
3061 x_cur_period_end_date := period_profile_rec.period20_end_date;
3062 elsif l_current_date >= period_profile_rec.period21_start_date and l_current_date <= period_profile_rec.period21_end_date then
3063 x_cur_period_number := 21;
3064 x_cur_period_name := period_profile_rec.period_name21;
3065 x_cur_period_start_date := period_profile_rec.period21_start_date;
3066 x_cur_period_end_date := period_profile_rec.period21_end_date;
3067 elsif l_current_date >= period_profile_rec.period22_start_date and l_current_date <= period_profile_rec.period22_end_date then
3068 x_cur_period_number := 22;
3069 x_cur_period_name := period_profile_rec.period_name22;
3070 x_cur_period_start_date := period_profile_rec.period22_start_date;
3071 x_cur_period_end_date := period_profile_rec.period22_end_date;
3072 elsif l_current_date >= period_profile_rec.period23_start_date and l_current_date <= period_profile_rec.period23_end_date then
3073 x_cur_period_number := 23;
3074 x_cur_period_name := period_profile_rec.period_name23;
3075 x_cur_period_start_date := period_profile_rec.period23_start_date;
3076 x_cur_period_end_date := period_profile_rec.period23_end_date;
3077 elsif l_current_date >= period_profile_rec.period24_start_date and l_current_date <= period_profile_rec.period24_end_date then
3078 x_cur_period_number := 24;
3079 x_cur_period_name := period_profile_rec.period_name24;
3080 x_cur_period_start_date := period_profile_rec.period24_start_date;
3081 x_cur_period_end_date := period_profile_rec.period24_end_date;
3082 elsif l_current_date >= period_profile_rec.period25_start_date and l_current_date <= period_profile_rec.period25_end_date then
3083 x_cur_period_number := 25;
3084 x_cur_period_name := period_profile_rec.period_name25;
3085 x_cur_period_start_date := period_profile_rec.period25_start_date;
3086 x_cur_period_end_date := period_profile_rec.period25_end_date;
3087 elsif l_current_date >= period_profile_rec.period26_start_date and l_current_date <= period_profile_rec.period26_end_date then
3088 x_cur_period_number := 26;
3089 x_cur_period_name := period_profile_rec.period_name26;
3090 x_cur_period_start_date := period_profile_rec.period26_start_date;
3091 x_cur_period_end_date := period_profile_rec.period26_end_date;
3092 elsif l_current_date >= period_profile_rec.period27_start_date and l_current_date <= period_profile_rec.period27_end_date then
3093 x_cur_period_number := 27;
3094 x_cur_period_name := period_profile_rec.period_name27;
3095 x_cur_period_start_date := period_profile_rec.period27_start_date;
3096 x_cur_period_end_date := period_profile_rec.period27_end_date;
3097 elsif l_current_date >= period_profile_rec.period28_start_date and l_current_date <= period_profile_rec.period28_end_date then
3098 x_cur_period_number := 28;
3099 x_cur_period_name := period_profile_rec.period_name28;
3100 x_cur_period_start_date := period_profile_rec.period28_start_date;
3101 x_cur_period_end_date := period_profile_rec.period28_end_date;
3102 elsif l_current_date >= period_profile_rec.period29_start_date and l_current_date <= period_profile_rec.period29_end_date then
3103 x_cur_period_number := 29;
3104 x_cur_period_name := period_profile_rec.period_name29;
3105 x_cur_period_start_date := period_profile_rec.period29_start_date;
3106 x_cur_period_end_date := period_profile_rec.period29_end_date;
3107 elsif l_current_date >= period_profile_rec.period30_start_date and l_current_date <= period_profile_rec.period30_end_date then
3108 x_cur_period_number := 30;
3109 x_cur_period_name := period_profile_rec.period_name30;
3110 x_cur_period_start_date := period_profile_rec.period30_start_date;
3111 x_cur_period_end_date := period_profile_rec.period30_end_date;
3112 elsif l_current_date >= period_profile_rec.period31_start_date and l_current_date <= period_profile_rec.period31_end_date then
3113 x_cur_period_number := 31;
3114 x_cur_period_name := period_profile_rec.period_name31;
3115 x_cur_period_start_date := period_profile_rec.period31_start_date;
3116 x_cur_period_end_date := period_profile_rec.period31_end_date;
3117 elsif l_current_date >= period_profile_rec.period32_start_date and l_current_date <= period_profile_rec.period32_end_date then
3118 x_cur_period_number := 32;
3119 x_cur_period_name := period_profile_rec.period_name32;
3120 x_cur_period_start_date := period_profile_rec.period32_start_date;
3121 x_cur_period_end_date := period_profile_rec.period32_end_date;
3122 elsif l_current_date >= period_profile_rec.period33_start_date and l_current_date <= period_profile_rec.period33_end_date then
3123 x_cur_period_number := 33;
3124 x_cur_period_name := period_profile_rec.period_name33;
3125 x_cur_period_start_date := period_profile_rec.period33_start_date;
3126 x_cur_period_end_date := period_profile_rec.period33_end_date;
3127 elsif l_current_date >= period_profile_rec.period34_start_date and l_current_date <= period_profile_rec.period34_end_date then
3128 x_cur_period_number := 34;
3129 x_cur_period_name := period_profile_rec.period_name34;
3130 x_cur_period_start_date := period_profile_rec.period34_start_date;
3131 x_cur_period_end_date := period_profile_rec.period34_end_date;
3132 elsif l_current_date >= period_profile_rec.period35_start_date and l_current_date <= period_profile_rec.period35_end_date then
3133 x_cur_period_number := 35;
3134 x_cur_period_name := period_profile_rec.period_name35;
3135 x_cur_period_start_date := period_profile_rec.period35_start_date;
3136 x_cur_period_end_date := period_profile_rec.period35_end_date;
3137 elsif l_current_date >= period_profile_rec.period36_start_date and l_current_date <= period_profile_rec.period36_end_date then
3138 x_cur_period_number := 36;
3139 x_cur_period_name := period_profile_rec.period_name36;
3140 x_cur_period_start_date := period_profile_rec.period36_start_date;
3141 x_cur_period_end_date := period_profile_rec.period36_end_date;
3142 elsif l_current_date >= period_profile_rec.period37_start_date and l_current_date <= period_profile_rec.period37_end_date then
3143 x_cur_period_number := 37;
3144 x_cur_period_name := period_profile_rec.period_name37;
3145 x_cur_period_start_date := period_profile_rec.period37_start_date;
3146 x_cur_period_end_date := period_profile_rec.period37_end_date;
3147 elsif l_current_date >= period_profile_rec.period38_start_date and l_current_date <= period_profile_rec.period38_end_date then
3148 x_cur_period_number := 38;
3149 x_cur_period_name := period_profile_rec.period_name38;
3150 x_cur_period_start_date := period_profile_rec.period38_start_date;
3151 x_cur_period_end_date := period_profile_rec.period38_end_date;
3152 elsif l_current_date >= period_profile_rec.period39_start_date and l_current_date <= period_profile_rec.period39_end_date then
3153 x_cur_period_number := 39;
3154 x_cur_period_name := period_profile_rec.period_name39;
3155 x_cur_period_start_date := period_profile_rec.period39_start_date;
3156 x_cur_period_end_date := period_profile_rec.period39_end_date;
3157 elsif l_current_date >= period_profile_rec.period40_start_date and l_current_date <= period_profile_rec.period40_end_date then
3158 x_cur_period_number := 40;
3159 x_cur_period_name := period_profile_rec.period_name40;
3160 x_cur_period_start_date := period_profile_rec.period40_start_date;
3161 x_cur_period_end_date := period_profile_rec.period40_end_date;
3162 elsif l_current_date >= period_profile_rec.period41_start_date and l_current_date <= period_profile_rec.period41_end_date then
3163 x_cur_period_number := 41;
3164 x_cur_period_name := period_profile_rec.period_name41;
3165 x_cur_period_start_date := period_profile_rec.period41_start_date;
3166 x_cur_period_end_date := period_profile_rec.period41_end_date;
3167 elsif l_current_date >= period_profile_rec.period42_start_date and l_current_date <= period_profile_rec.period42_end_date then
3168 x_cur_period_number := 42;
3169 x_cur_period_name := period_profile_rec.period_name42;
3170 x_cur_period_start_date := period_profile_rec.period42_start_date;
3171 x_cur_period_end_date := period_profile_rec.period42_end_date;
3172 elsif l_current_date >= period_profile_rec.period43_start_date and l_current_date <= period_profile_rec.period43_end_date then
3173 x_cur_period_number := 43;
3174 x_cur_period_name := period_profile_rec.period_name43;
3175 x_cur_period_start_date := period_profile_rec.period43_start_date;
3176 x_cur_period_end_date := period_profile_rec.period43_end_date;
3177 elsif l_current_date >= period_profile_rec.period44_start_date and l_current_date <= period_profile_rec.period44_end_date then
3178 x_cur_period_number := 44;
3179 x_cur_period_name := period_profile_rec.period_name44;
3180 x_cur_period_start_date := period_profile_rec.period44_start_date;
3181 x_cur_period_end_date := period_profile_rec.period44_end_date;
3182 elsif l_current_date >= period_profile_rec.period45_start_date and l_current_date <= period_profile_rec.period45_end_date then
3183 x_cur_period_number := 45;
3184 x_cur_period_name := period_profile_rec.period_name45;
3185 x_cur_period_start_date := period_profile_rec.period45_start_date;
3186 x_cur_period_end_date := period_profile_rec.period45_end_date;
3187 elsif l_current_date >= period_profile_rec.period46_start_date and l_current_date <= period_profile_rec.period46_end_date then
3188 x_cur_period_number := 46;
3189 x_cur_period_name := period_profile_rec.period_name46;
3190 x_cur_period_start_date := period_profile_rec.period46_start_date;
3191 x_cur_period_end_date := period_profile_rec.period46_end_date;
3192 elsif l_current_date >= period_profile_rec.period47_start_date and l_current_date <= period_profile_rec.period47_end_date then
3193 x_cur_period_number := 47;
3194 x_cur_period_name := period_profile_rec.period_name47;
3195 x_cur_period_start_date := period_profile_rec.period47_start_date;
3196 x_cur_period_end_date := period_profile_rec.period47_end_date;
3197 elsif l_current_date >= period_profile_rec.period48_start_date and l_current_date <= period_profile_rec.period48_end_date then
3198 x_cur_period_number := 48;
3199 x_cur_period_name := period_profile_rec.period_name48;
3200 x_cur_period_start_date := period_profile_rec.period48_start_date;
3201 x_cur_period_end_date := period_profile_rec.period48_end_date;
3202 elsif l_current_date >= period_profile_rec.period49_start_date and l_current_date <= period_profile_rec.period49_end_date then
3203 x_cur_period_number := 49;
3204 x_cur_period_name := period_profile_rec.period_name49;
3205 x_cur_period_start_date := period_profile_rec.period49_start_date;
3206 x_cur_period_end_date := period_profile_rec.period49_end_date;
3207 elsif l_current_date >= period_profile_rec.period50_start_date and l_current_date <= period_profile_rec.period50_end_date then
3208 x_cur_period_number := 50;
3209 x_cur_period_name := period_profile_rec.period_name50;
3210 x_cur_period_start_date := period_profile_rec.period50_start_date;
3211 x_cur_period_end_date := period_profile_rec.period50_end_date;
3212 elsif l_current_date >= period_profile_rec.period51_start_date and l_current_date <= period_profile_rec.period51_end_date then
3213 x_cur_period_number := 51;
3214 x_cur_period_name := period_profile_rec.period_name51;
3215 x_cur_period_start_date := period_profile_rec.period51_start_date;
3216 x_cur_period_end_date := period_profile_rec.period51_end_date;
3217 elsif l_current_date >= period_profile_rec.period52_start_date and l_current_date <= period_profile_rec.period52_end_date then
3218 x_cur_period_number := 52;
3219 x_cur_period_name := period_profile_rec.period_name52;
3220 x_cur_period_start_date := period_profile_rec.period52_start_date;
3221 x_cur_period_end_date := period_profile_rec.period52_end_date;
3222 else
3223 -- current date falls AFTER last period end date
3224 x_cur_period_number := -1;
3225 x_cur_period_name := null;
3226 x_cur_period_start_date := null;
3227 x_cur_period_end_date := null;
3228 end if;
3229 end if;
3230 close period_profile_csr;
3231 EXCEPTION
3232 WHEN OTHERS THEN
3233 FND_MSG_PUB.add_exc_msg
3234 ( p_pkg_name => 'PA_PRJ_PERIOD_PROFILE_UTILS.get_current_period_info'
3235 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
3236 RAISE;
3237 END get_current_period_info;
3238
3239
3240 function has_preceding_periods
3241 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE) RETURN VARCHAR2
3242 is
3243 l_return_value VARCHAR2(1);
3244 BEGIN
3245 l_return_value := 'N';
3246 select unique 'Y'
3247 into l_return_value
3248 from pa_budget_lines
3249 where budget_version_id = p_budget_version_id and
3250 bucketing_period_code = 'PD';
3251 return l_return_value;
3252 EXCEPTION
3253 WHEN NO_DATA_FOUND THEN
3254 return l_return_value;
3255 END has_preceding_periods;
3256
3257
3258 function has_succeeding_periods
3259 (p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE) RETURN VARCHAR2
3260 is
3261 l_return_value VARCHAR2(1);
3262 BEGIN
3263 l_return_value := 'N';
3264 select unique 'Y'
3265 into l_return_value
3266 from pa_budget_lines
3267 where budget_version_id = p_budget_version_id and
3268 bucketing_period_code = 'SD';
3269 return l_return_value;
3270 EXCEPTION
3271 WHEN NO_DATA_FOUND THEN
3272 return l_return_value;
3273 END has_succeeding_periods;
3274
3275 PROCEDURE UPDATE_BUDGET_VERSION(p_budget_version_id IN NUMBER,
3276 p_return_status IN VARCHAR2,
3277 p_project_id IN NUMBER,
3278 p_request_id IN NUMBER ) IS
3279 l_plan_proc_code pa_budget_versions.plan_processing_Code%type;
3280 BEGIN
3281 if p_return_status <> FND_API.G_RET_STS_SUCCESS then
3282 l_plan_proc_code := 'PPE';
3283 else
3284 l_plan_proc_code := 'PPG';
3285 end if;
3286 IF p_project_id IS NOT NULL THEN
3287 UPDATE PA_BUDGET_VERSIONS
3288 SET PLAN_PROCESSING_CODE = l_plan_proc_code,
3289 locked_by_person_id = NULL,
3290 record_version_number = nvl(record_version_number,0) + 1,
3291 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
3292 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3293 LAST_UPDATE_DATE = sysdate
3294 WHERE
3295 project_id = p_project_id and
3296 request_id = p_request_id and
3297 plan_processing_code = 'PPP';
3298 ELSE
3299 UPDATE PA_BUDGET_VERSIONS
3300 SET PLAN_PROCESSING_CODE = l_plan_proc_code,
3301 locked_by_person_id = NULL,
3302 record_version_number = nvl(record_version_number,0) + 1,
3303 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
3304 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
3305 LAST_UPDATE_DATE = sysdate
3306 WHERE
3307 budget_version_id = p_budget_version_id;
3308 END IF;
3309 COMMIT;
3310 END;
3311
3312
3313 END PA_PRJ_PERIOD_PROFILE_UTILS;