[Home] [Help]
PACKAGE BODY: APPS.PA_FIN_PLAN_TYPES_UTILS
Source
1 PACKAGE BODY pa_fin_plan_types_utils as
2 /* $Header: PAFTYPUB.pls 120.1 2005/08/19 16:32:29 mwasowic noship $ */
3
4 /*********************************************************************
5 Important : The appropriate procedures that make a call to the below
6 procedures must make a call to FND_MSG_PUB.initialize.
7 **********************************************************************/
8
9 g_module_name VARCHAR2(100) := 'pa.plsql.pa_fin_plan_types_utils';
10
11 procedure name_val
12 (p_name IN pa_fin_plan_types_tl.name%TYPE,
13 p_fin_plan_type_id IN
14 pa_fin_plan_types_tl.fin_plan_type_id%TYPE,
15 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
16 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
17 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
18 is
19 l_exists VARCHAR2(1);
20 l_msg_count NUMBER;
21 l_msg_index_out NUMBER;
22 l_data VARCHAR2(2000);
23 l_msg_data VARCHAR2(2000);
24 l_name_exists boolean;--for bug 2625505
25
26
27
28 l_language_code FND_LANGUAGES.LANGUAGE_CODE%TYPE ;
29 l_curr_language FND_LANGUAGES.LANGUAGE_CODE%TYPE ;
30
31
32 begin
33
34 if p_name is NULL then
35 /* Name must be entered */
36 x_return_status := FND_API.G_RET_STS_ERROR;
37 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
38 p_msg_name => 'PA_MANDATORY_INFO_MISSING');
39 end if;
40
41 /*Commenting out this code as it is not used any more (bug 2625505) and
42 initialising
43 l_name_exists to false*/
44 /*
45 l_msg_count := FND_MSG_PUB.count_msg; --For bug 2625505
46 */
47 l_name_exists:=false;
48
49 Begin
50 select 'Y'
51 into l_exists
52 from pa_fin_plan_types_vl
53 where upper(name) = upper(p_name)
54 and fin_plan_type_id <> p_fin_plan_type_id
55 and rownum < 2;
56
60 p_msg_name => 'PA_ALL_UNIQUE_NAME_EXISTS');
57 /* Duplicate Name should not be entered */
58 x_return_status := FND_API.G_RET_STS_ERROR;
59 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
61 /*Initialise the boolean variable here. bug 2625505)*/
62 l_name_exists:=true;
63 exception
64 when NO_DATA_FOUND then
65 null;
66 end;
67 -- 26-SEP-2002
68
69 Select language_code
70 ,userenv('LANG')
71 into l_language_code
72 ,l_curr_language
73 from fnd_languages
74 where installed_flag = 'B';
75
76 /*Display this message only if the budget type is not already upgraded and a plan
77 type for
78 that budget type already exists. Modifed the if below for this check(bug
79 2625505)
80 */
81
82 /*IF (upper(l_language_code) = upper(l_curr_language)) THEN
83 */
84 IF ( (upper(l_language_code) = upper(l_curr_language)) AND
85 /* (FND_MSG_PUB.count_msg = l_msg_count) )THEN*/ --This condition is not used any more (bug 2625505)
86 (l_name_exists=false)) THEN
87 -- This comparison is only required when the
88 -- currently installed language is same as
89 -- base language.
90
91 BEGIN
92 /* Bug 2755795 - We should checking if the fin plan type name is
93 being updated for an upgraded budget type. The check should be
94 done in such a way to exclude checking the budget type from
95 which this plan type was upgraded */
96
97 /*Commented out the sql for bug 2774573 */
98 /*
99 SELECT 'Y'
100 INTO l_exists
101 FROM pa_budget_types a, pa_fin_plan_types_b b
102 WHERE upper(budget_type) = upper(p_name)
103 AND b.fin_plan_type_id = p_fin_plan_type_id
104 AND a.budget_type_code <> b.migrated_frm_bdgt_typ_code;
105 */
106 /* The error should be thrown when
107 1. A budget type exists with the name of the plan type about to be created
108 The error should not be thrown when
109 1. updating an upgraded plan type
110 */
111 SELECT 'Y'
112 INTO l_exists
113 FROM pa_budget_types a
114 WHERE upper(a.budget_type) = upper(p_name)
115 AND NOT EXISTS( SELECT 'x'
116 FROM pa_fin_plan_types_b f
117 WHERE f.fin_plan_type_id=p_fin_plan_type_id
118 AND nvl
119 (f.migrated_frm_bdgt_typ_code,'-99')=a.budget_type_code);
120
121 /* Duplicate Name should not be entered */
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
124 p_msg_name =>
125 'PA_FP_RESERVED_PLAN_TYPE_NAME');/*Changed the message name (bug 2625505)*/
126 EXCEPTION
127 when NO_DATA_FOUND then
128 null;
129 END;
130 END IF;
131
132 -- 26-SEP-2002
133 l_msg_count := FND_MSG_PUB.count_msg;
134
135 if l_msg_count > 0 then
136 if l_msg_count = 1 then
137 PA_INTERFACE_UTILS_PUB.get_messages
138 (p_encoded => FND_API.G_TRUE,
139 p_msg_index => 1,
140 p_msg_count => l_msg_count,
141 p_msg_data => l_msg_data,
142 p_data => l_data,
143 p_msg_index_out => l_msg_index_out);
144 x_msg_data := l_data;
145 x_msg_count := l_msg_count;
146 else
147 x_msg_count := l_msg_count;
148 end if;
149 return;
150 else
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152 end if;
153
154
155 Exception
156 when OTHERS then
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158 x_msg_count := 1;
159 x_msg_data := SQLERRM;
160 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPES_UTILS',
161 p_procedure_name => 'name_val');
162 raise FND_API.G_EXC_UNEXPECTED_ERROR;
163 end name_val;
164
165 /****************************************************************************************
166 Commented as part of Dusan changes and moved the api to PA_FIN_PLAN_UTILS
167 procedure end_date_active_val
168 (p_start_date_active IN
169 pa_fin_plan_types_b.start_date_active%type,
170 p_end_date_active IN
171 pa_fin_plan_types_b.end_date_active%type,
172 x_return_status OUT VARCHAR2,
173 x_msg_count OUT NUMBER,
174 x_msg_data OUT VARCHAR2)
175 is
176 l_msg_count NUMBER;
177 l_msg_index_out NUMBER;
178 l_data VARCHAR2(2000);
179 l_msg_data VARCHAR2(2000);
180 begin
181
182 if p_start_date_active is null then
183 -- Start date must be entered
184 x_return_status := FND_API.G_RET_STS_ERROR;
185 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
186 p_msg_name => 'PA_MANDATORY_INFO_MISSING');
187 end if;
188
189 if p_start_date_active > nvl(p_end_date_active,p_start_date_active) then
190 -- The End Date cannot be earlier than the Start Date.
191 x_return_status := FND_API.G_RET_STS_ERROR;
192 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
193 p_msg_name => 'PA_INVALID_END_DATE');
194 end if;
195
196 l_msg_count := FND_MSG_PUB.count_msg;
197
198 if l_msg_count > 0 then
199 if l_msg_count = 1 then
200 PA_INTERFACE_UTILS_PUB.get_messages
201 (p_encoded => FND_API.G_TRUE,
202 p_msg_index => 1,
203 p_msg_count => l_msg_count,
204 p_msg_data => l_msg_data,
205 p_data => l_data,
206 p_msg_index_out => l_msg_index_out);
207 x_msg_data := l_data;
208 x_msg_count := l_msg_count;
209 else
210 x_msg_count := l_msg_count;
211 end if;
212 return;
213 else
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215 end if;
216
217 exception
218 when OTHERS then
219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
220 x_msg_count := 1;
221 x_msg_data := SQLERRM;
222 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPES_UTILS',
223 p_procedure_name => 'end_date_active_val');
224 raise FND_API.G_EXC_UNEXPECTED_ERROR;
225 end end_date_active_val;
226 ***********************************************************************************/
227
228 /************************************************************************************
229 *
230 Commented since generated_flag and used_in_billing_flag
231 are obsolete after change in functionality
232
233 procedure generated_flag_val
234 (p_fin_plan_type_id IN
235 pa_fin_plan_types_b.fin_plan_type_id%type,
236 p_generated_flag IN pa_fin_plan_types_b.generated_flag%type,
237 p_pre_defined_flag IN
238 pa_fin_plan_types_b.pre_defined_flag%type,
239 p_fin_plan_type_code IN
240 pa_fin_plan_types_b.fin_plan_type_code%type,
241 p_name IN pa_fin_plan_types_tl.name%type,
242 x_return_status OUT VARCHAR2,
243 x_msg_count OUT NUMBER,
244 x_msg_data OUT VARCHAR2)
245 is
246 l_msg_count NUMBER;
247 l_msg_index_out NUMBER;
248 l_data VARCHAR2(2000);
249 l_msg_data VARCHAR2(2000);
250 begin
251
252 if p_fin_plan_type_code = 'ORG_FORECAST' and
253 p_pre_defined_flag = 'Y' and
254 p_generated_flag = 'N' then
255 -- Generated_flag should be 'Y' for ORG_FORECAST finplantype
256 x_return_status := FND_API.G_RET_STS_ERROR;
257 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
258 p_msg_name => 'PA_FPTYPE_GENFLAG_NOUPD',
259 p_token1 => 'PLAN_TYPE',
260 p_value1 => p_name);
261
262 end if;
263
264 l_msg_count := FND_MSG_PUB.count_msg;
265
266 if l_msg_count > 0 then
267 if l_msg_count = 1 then
268 PA_INTERFACE_UTILS_PUB.get_messages
269 (p_encoded => FND_API.G_TRUE,
270 p_msg_index => 1,
271 p_msg_count => l_msg_count,
272 p_msg_data => l_msg_data,
273 p_data => l_data,
274 p_msg_index_out => l_msg_index_out);
275 x_msg_data := l_data;
276 x_msg_count := l_msg_count;
277 else
278 x_msg_count := l_msg_count;
279 end if;
280 return;
281 else
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283 end if;
284
285 exception
286 when others then
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 x_msg_count := 1;
289 x_msg_data := SQLERRM;
290 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPES_UTILS',
291 p_procedure_name => 'generated_flag_val');
292 raise FND_API.G_EXC_UNEXPECTED_ERROR;
293 end generated_flag_val;
294
295 procedure used_in_billing_flag_val
296 (p_fin_plan_type_id IN
297 pa_fin_plan_types_b.fin_plan_type_id%type,
298 p_used_in_billing_flag IN
299 pa_fin_plan_types_b.used_in_billing_flag%type,
300 x_return_status OUT VARCHAR2,
301 x_msg_count OUT NUMBER,
302 x_msg_data OUT VARCHAR2)
303 is
304 l_count NUMBER;
305 l_msg_count NUMBER;
306 l_msg_index_out NUMBER;
307 l_data VARCHAR2(2000);
308 l_msg_data VARCHAR2(2000);
309 begin
310
311 Select count(*)
312 into l_count
313 from pa_fin_plan_types_b
314 where fin_plan_type_id <> p_fin_plan_type_id
315 and used_in_billing_flag = 'Y';
316
317 if p_used_in_billing_flag = 'Y' and l_count > 0 then
318 -- Only one financial plan type should have USED_IN_BILLING_FLAG as 'Y'
319 x_return_status := FND_API.G_RET_STS_ERROR;
320 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
321 p_msg_name =>
322 'PA_FPTYPE_USED_IN_BILL_NOUPD');
323 end if;
324
325 l_msg_count := FND_MSG_PUB.count_msg;
326
327 if l_msg_count > 0 then
331 p_msg_index => 1,
328 if l_msg_count = 1 then
329 PA_INTERFACE_UTILS_PUB.get_messages
330 (p_encoded => FND_API.G_TRUE,
332 p_msg_count => l_msg_count,
333 p_msg_data => l_msg_data,
334 p_data => l_data,
335 p_msg_index_out => l_msg_index_out);
336 x_msg_data := l_data;
337 x_msg_count := l_msg_count;
338 else
339 x_msg_count := l_msg_count;
340 end if;
341 return;
342 else
343 x_return_status := FND_API.G_RET_STS_SUCCESS;
344 end if;
345
346 exception
347 when others then
348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
349 x_msg_count := 1;
350 x_msg_data := SQLERRM;
351 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPES_UTILS',
352 p_procedure_name => 'used_in_billing_flag_val');
353 raise FND_API.G_EXC_UNEXPECTED_ERROR;
354 end used_in_billing_flag_val;
355
356 ************************************************************************************/
357
358 procedure delete_val
359 (p_fin_plan_type_id IN
360 pa_fin_plan_types_b.fin_plan_type_id%type,
361 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
362 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
363 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
364 is
365 l_msg_count NUMBER;
366 l_msg_index_out NUMBER;
367 l_data VARCHAR2(2000);
368 l_msg_data VARCHAR2(2000);
369 begin
370
371 if pa_fin_plan_types_utils.isfptypeused(p_fin_plan_type_id => p_fin_plan_type_id) =
372 'Y' then
373 /* If a plan type has already been used by a project, that plan type should not
374 be deleted */
375 x_return_status := FND_API.G_RET_STS_ERROR;
376 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
377 p_msg_name => 'PA_FPTYPE_IN_USE');
378 end if;
379
380 l_msg_count := FND_MSG_PUB.count_msg;
381
382 if l_msg_count > 0 then
383 if l_msg_count = 1 then
384 PA_INTERFACE_UTILS_PUB.get_messages
385 (p_encoded => FND_API.G_TRUE,
386 p_msg_index => 1,
387 p_msg_count => l_msg_count,
388 p_msg_data => l_msg_data,
389 p_data => l_data,
390 p_msg_index_out => l_msg_index_out);
391 x_msg_data := l_data;
392 x_msg_count := l_msg_count;
393 else
394 x_msg_count := l_msg_count;
395 end if;
396 return;
397 else
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399 end if;
400
401 exception
402 when others then
403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
404 x_msg_count := 1;
405 x_msg_data := SQLERRM;
406 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPES_UTILS',
407 p_procedure_name => 'delete_val');
408 raise FND_API.G_EXC_UNEXPECTED_ERROR;
409 end delete_val;
410
411
412 procedure validate
413 (p_fin_plan_type_id IN
414 pa_fin_plan_types_b.fin_plan_type_id%type,
415 p_name IN pa_fin_plan_types_tl.name%type,
416 p_start_date_active IN
417 pa_fin_plan_types_b.start_date_active%type,
418 p_end_date_active IN
419 pa_fin_plan_types_b.end_date_active%type,
420 p_generated_flag IN pa_fin_plan_types_b.generated_flag%type,
421 p_used_in_billing_flag IN pa_fin_plan_types_b.used_in_billing_flag%type,
422 p_record_version_number IN
423 pa_fin_plan_types_b.record_version_number%type,
424 p_fin_plan_type_code IN
425 pa_fin_plan_types_b.fin_plan_type_code%type,
426 p_pre_defined_flag IN
427 pa_fin_plan_types_b.pre_defined_flag%type,
428 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
429 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
430 x_msg_data OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
431 l_msg_count NUMBER;
432 l_msg_index_out NUMBER;
433 l_data VARCHAR2(2000);
434 l_msg_data VARCHAR2(2000);
435 begin
436
437 x_return_status := FND_API.G_RET_STS_SUCCESS;
438
439 pa_fin_plan_types_utils.name_val
440 (p_name => p_name,
441 p_fin_plan_type_id => p_fin_plan_type_id,
442 x_return_status => x_return_status,
443 x_msg_count => x_msg_count,
444 x_msg_data => x_msg_data);
445
446 pa_fin_plan_utils.end_date_active_val
447 (p_start_date_active => p_start_date_active,
448 p_end_date_active => p_end_date_active,
449 x_return_status => x_return_status,
450 x_msg_count => x_msg_count,
451 x_msg_data => x_msg_data);
452
453 /***********************************************************************
454 Commented since generated_flag and used_in_billing_flag
455 are obsolete after change in functionality
456
457 pa_fin_plan_types_utils.generated_flag_val
458 (p_fin_plan_type_id => p_fin_plan_type_id,
462 p_name => p_name,
459 p_generated_flag => p_generated_flag,
460 p_pre_defined_flag => p_pre_defined_flag,
461 p_fin_plan_type_code => p_fin_plan_type_code,
463 x_return_status => x_return_status,
464 x_msg_count => x_msg_count,
465 x_msg_data => x_msg_data);
466
467 pa_fin_plan_types_utils.used_in_billing_flag_val
468 (p_fin_plan_type_id => p_fin_plan_type_id,
469 p_used_in_billing_flag => p_used_in_billing_flag,
470 x_return_status => x_return_status,
471 x_msg_count => x_msg_count,
472 x_msg_data => x_msg_data);
473
474 ***********************************************************************/
475 l_msg_count := FND_MSG_PUB.count_msg;
476
477 if l_msg_count > 0 then
478 if l_msg_count = 1 then
479 PA_INTERFACE_UTILS_PUB.get_messages
480 (p_encoded => FND_API.G_TRUE,
481 p_msg_index => 1,
482 p_msg_count => l_msg_count,
483 p_msg_data => l_msg_data,
484 p_data => l_data,
485 p_msg_index_out => l_msg_index_out);
486 x_msg_data := l_data;
487 x_msg_count := l_msg_count;
488 else
489 x_msg_count := l_msg_count;
490 end if;
491 return;
492 end if;
493 exception
494 when others then
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496 x_msg_count := 1;
497 x_msg_data := SQLERRM;
498 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPES_UTILS',
499 p_procedure_name => 'validate');
500 raise FND_API.G_EXC_UNEXPECTED_ERROR;
501 end validate;
502
503 function isfptypeused
504 (p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%type)
505 return VARCHAR2 is
506 l_return VARCHAR2(1) := 'N';
507 begin
508
509 -- Modified SQL below for perf fix - 3961675.
510
511 BEGIN
512 SELECT 'Y'
513 INTO l_return
514 FROM DUAL
515 WHERE EXISTS (SELECT 1
516 FROM PA_PROJ_FP_OPTIONS
517 WHERE FIN_PLAN_TYPE_ID = p_fin_plan_type_id);
518
519 EXCEPTION
520 WHEN NO_DATA_FOUND THEN
521 l_return := 'N';
522
523 END;
524
525 return l_return;
526
527 end;
528
529 /* FP M - dbora - Function to check for any partially implemented COs in a plan type
530 */
531 FUNCTION partially_impl_cos_exist
532 (p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE,
533 p_ci_type_id IN pa_control_items.ci_type_id%TYPE)
534 RETURN VARCHAR2 is
535
536 l_return_flag VARCHAR2(1);
537 l_partial_cos_exist VARCHAR2(1);
538
539 l_debug_mode VARCHAR2(1);
540 l_debug_level3 CONSTANT NUMBER := 3;
541 l_module_name VARCHAR2(100) := 'partially_impl_cos_exist:' || g_module_name;
542
543 BEGIN
544
545 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
546
547 IF l_debug_mode = 'Y' THEN
548 pa_debug.set_curr_function( p_function => 'partially_impl_cos_exist',
549 p_debug_mode => l_debug_mode );
550 END IF;
551
552 IF l_debug_mode = 'Y' THEN
553 pa_debug.g_err_stage:= 'Entering PARTIALLY_IMPL_COS_ESIST';
554 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
555 END IF;
556
557 IF l_debug_mode = 'Y' THEN
558 pa_debug.g_err_stage:= 'The plan type Id : ' || p_fin_plan_type_id;
559 pa_debug.write(l_module_name,pa_debug.g_err_stage,
560 l_debug_level3);
561 END IF;
562
563 IF p_fin_plan_type_id IS NULL THEN
564 pa_debug.reset_curr_function;
565 RETURN 'N';
566
567 ELSIF p_ci_type_id IS NOT NULL THEN
568
569 BEGIN
570 SELECT 'Y'
571 INTO l_partial_cos_exist
572 FROM DUAL
573 WHERE
574 EXISTS (SELECT 'X'
575 FROM pa_budget_versions bv,
576 pa_control_items ci
577 WHERE ci.ci_id=bv.ci_id
578 AND bv.fin_plan_type_id= p_fin_plan_type_id
579 AND bv.rev_partially_impl_flag='Y'
580 AND ci.ci_type_id = p_ci_type_id);
581
582 EXCEPTION
583 WHEN NO_DATA_FOUND THEN
584 l_return_flag := 'N';
585
586 pa_debug.reset_curr_function;
587 RETURN l_return_flag;
588 END;
589
590 IF l_partial_cos_exist IS NULL THEN
591 IF l_debug_mode= 'Y' THEN
592 pa_debug.reset_curr_function;
593 END IF;
594
595 RETURN 'N';
596 ELSE
597 IF l_debug_mode='Y' THEN
598 pa_debug.reset_curr_function;
599 END IF;
600
601 RETURN 'Y' ;
602 END IF;
603
604 ELSE
605 BEGIN
606
607 SELECT 'Y'
611 AND rev_partially_impl_flag = 'Y'
608 INTO l_return_flag
609 FROM pa_budget_versions
610 WHERE fin_plan_type_id = p_fin_plan_type_id
612 AND ci_id is not null
613 AND ROWNUM = 1;
614
615 IF l_debug_mode= 'Y' THEN
616 pa_debug.reset_curr_function;
617 END IF;
618
619 RETURN l_return_flag;
620
621 EXCEPTION
622 WHEN NO_DATA_FOUND THEN
623 l_return_flag := 'N';
624
625 IF l_debug_mode= 'Y' THEN
626 pa_debug.reset_curr_function;
627 END IF;
628
629 RETURN l_return_flag;
630 END;
631 END IF;
632
633 IF l_debug_mode= 'Y' THEN
634 pa_debug.reset_curr_function;
635 END IF;
636
637 IF l_debug_mode= 'Y' THEN
638 pa_debug.g_err_stage:= 'Leaving PARTIALLY_IMPL_COS_EXIST' ;
639 pa_debug.write(l_module_name,pa_debug.g_err_stage,
640 l_debug_level3);
641 END IF;
642
643 END partially_impl_cos_exist;
644
645 /* FP M - dbora - Returns the concatenated string containing all the cost/rev
646 statuses.
647 */
648
649 FUNCTION GET_CONCAT_STATUSES
650 (p_fin_plan_type_id IN pa_fin_plan_types_b.fin_plan_type_id%TYPE,
651 p_ci_type_id IN pa_pt_co_impl_statuses.ci_type_id%TYPE,
652 p_impact_type_code IN pa_pt_co_impl_statuses.version_type%TYPE)
653 RETURN VARCHAR2 IS
654
655 l_concat_status VARCHAR2(2000);
656
657 l_debug_mode VARCHAR2(1);
658 l_debug_level3 CONSTANT NUMBER := 3;
659 l_module_name VARCHAR2(100) := 'GET_CONCAT_STATUSES' || g_module_name;
660
661 CURSOR c_status_csr (c_impact_type_code VARCHAR2) IS
662 SELECT ci.project_status_name
663 FROM PA_CI_STATUSES_V ci,
664 PA_PT_CO_IMPL_STATUSES ptco
665 WHERE ptco.ci_type_id=p_ci_type_id
666 AND ptco.fin_plan_type_id=p_fin_plan_type_id
667 AND ptco.version_type=c_impact_type_code
668 AND ci.ci_type_id = ptco.ci_type_id
669 AND ci.project_status_code = ptco.status_code;
670
671 c_status_rec c_status_csr%ROWTYPE;
672
673 BEGIN
674
675 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
676
677 IF l_debug_mode = 'Y' THEN
678 pa_debug.set_curr_function( p_function => 'get_concat_statuses',
679 p_debug_mode => l_debug_mode );
680 END IF;
681
682 IF l_debug_mode = 'Y' THEN
683 pa_debug.g_err_stage:= 'Entering GET_CONCAT_STATUSES';
684 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
685 END IF;
686
687 OPEN c_status_csr(p_impact_type_code);
688
689 LOOP
690 FETCH c_status_csr INTO c_status_rec;
691 EXIT WHEN c_status_csr%NOTFOUND;
692
693 IF l_concat_status IS NOT NULL THEN
694 l_concat_status := l_concat_status || ',' ;
695 END IF;
696
697 l_concat_status := l_concat_status || c_status_rec.project_status_name;
698 END LOOP;
699
700 IF l_debug_mode = 'Y' THEN
701 pa_debug.g_err_stage:= 'The concatened status : ' || l_concat_status;
702 pa_debug.write(l_module_name,pa_debug.g_err_stage,
703 l_debug_level3);
704 END IF;
705
706 CLOSE c_status_csr;
707
708 IF l_debug_mode = 'Y' THEN
709 pa_debug.reset_curr_function;
710 END IF;
711
712 RETURN l_concat_status;
713
714 EXCEPTION
715 WHEN OTHERS THEN
716 IF l_debug_mode = 'Y' THEN
717 pa_debug.reset_curr_function;
718 END IF;
719
720 FND_MSG_PUB.add_exc_msg( p_pkg_name
721 => 'PA_FIN_PLAN_TYPES_UTILS'
722 ,p_procedure_name
723 => 'Get_concate_statuses');
724 RAISE;
725
726 IF l_debug_mode = 'Y' THEN
727 pa_debug.g_err_stage:= 'Leaving GET_CONCAT_STATUSES';
728 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
729 END IF;
730
731 END GET_CONCAT_STATUSES;
732
733 PROCEDURE GET_WORKPLAN_PT_DETAILS
734 (x_workplan_pt_id OUT NOCOPY pa_fin_plan_types_b.fin_plan_type_id%TYPE, --File.Sql.39 bug 4440895
735 x_w_pt_attached_to_proj OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
736 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
737 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
738 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
739 IS
740
741 l_debug_mode VARCHAR2(1);
742 l_debug_level3 CONSTANT NUMBER := 3;
743 l_module_name VARCHAR2(100) := 'GET_WORKPLAN_PT_DETAILS' || g_module_name;
744
745 BEGIN
746
747 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
748
749 IF l_debug_mode = 'Y' THEN
753
750 pa_debug.set_curr_function( p_function => 'get_workplan_pt_details',
751 p_debug_mode => l_debug_mode );
752 END IF;
754 IF l_debug_mode = 'Y' THEN
755 pa_debug.g_err_stage:= 'Entering GET_WORKPLAN_PT_DETAILS';
756 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
757 END IF;
758
759 x_return_status := FND_API.G_RET_STS_SUCCESS;
760
761 BEGIN
762
763 SELECT fin_plan_type_id
764 INTO x_workplan_pt_id
765 FROM pa_fin_plan_types_b
766 WHERE use_for_workplan_flag = 'Y';
767
768 EXCEPTION
769 WHEN NO_DATA_FOUND THEN
770 x_workplan_pt_id := -99;
771 x_w_pt_attached_to_proj := 'N';
772 END;
773
774 IF NVL(x_workplan_pt_id,-99) <> -99 THEN
775 BEGIN
776
777 SELECT 'Y'
778 INTO x_w_pt_attached_to_proj
779 FROM DUAL
780 WHERE
781 EXISTS (SELECT 'X'
782 FROM pa_proj_fp_options
783 WHERE fin_plan_type_id = x_workplan_pt_id);
784
785 EXCEPTION
786 WHEN NO_DATA_FOUND THEN
787 x_w_pt_attached_to_proj := 'N';
788 END;
789 END IF;
790
791
792 IF l_debug_mode = 'Y' THEN
793 pa_debug.g_err_stage:= 'Leaving GET_WORKPLAN_PT_DETAILS';
794 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
795 pa_debug.reset_curr_function;
796 END IF;
797
798
799 EXCEPTION
800 WHEN OTHERS THEN
801 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
802 x_msg_count := 1;
803 x_msg_data := SQLERRM;
804 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FIN_PLAN_TYPES_UTILS',
805 p_procedure_name => 'get_workplan_pt_details');
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807
808 END GET_WORKPLAN_PT_DETAILS;
809
810 /* The following function returns the value 'Y' or 'N' depending upon
811 * if revenue impact has been implemented partially for the given CI.
812 * This function is different from the function 'partially_impl_cos_exist'
813 * as it checks for partial revenue implementation for CI_ID
814 */
815 FUNCTION Is_Rev_Impl_Partially
816 (p_ci_id IN pa_budget_versions.ci_id%TYPE,
817 p_project_id IN pa_budget_versions.project_id%TYPE)
818 RETURN VARCHAR2
819
820 IS
821 l_rev_partial_impl_flag VARCHAR2(1);
822 l_debug_mode VARCHAR2(1);
823 l_debug_level3 CONSTANT NUMBER := 3;
824 l_module_name VARCHAR2(100) := 'Is_Rev_Impl_Partially';
825
826 BEGIN
827
828 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
829
830 pa_debug.set_curr_function( p_function => 'Is_Rev_Impl_Partially',
831 p_debug_mode => l_debug_mode );
832
833 IF l_debug_mode = 'Y' THEN
834 pa_debug.g_err_stage:= 'Entering Is_Rev_Impl_Partially';
835 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
836 END IF;
837
838 IF l_debug_mode = 'Y' THEN
839 pa_debug.g_err_stage:= 'The input Ci Id : ' || p_ci_id;
840 pa_debug.write(l_module_name,pa_debug.g_err_stage,
841 l_debug_level3);
842 END IF;
843 IF l_debug_mode = 'Y' THEN
844 pa_debug.g_err_stage:= 'The input Project Id : ' || p_project_id;
845 pa_debug.write(l_module_name,pa_debug.g_err_stage,
846 l_debug_level3);
847 END IF;
848
849 BEGIN
850 SELECT Nvl(rev_partially_impl_flag, 'N')
851 INTO l_rev_partial_impl_flag
852 FROM pa_budget_versions
853 WHERE ci_id = p_ci_id
854 AND project_id = p_project_id
855 AND version_type IN ('REVENUE','ALL');
856
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 l_rev_partial_impl_flag := 'N';
860 IF l_debug_mode = 'Y' THEN
861 pa_debug.g_err_stage:= 'No Impact exists';
862 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
863 END IF;
864
865 pa_debug.reset_curr_function;
866 RETURN l_rev_partial_impl_flag;
867 END;
868
869 IF l_debug_mode = 'Y' THEN
870 pa_debug.g_err_stage:= 'l_rev_partial_impl_flag is: ' || l_rev_partial_impl_flag;
871 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
872 END IF;
873
874 IF l_debug_mode = 'Y' THEN
875 pa_debug.g_err_stage:= 'Leaving Is_Rev_Impl_Partially';
876 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
877 END IF;
878 pa_debug.reset_curr_function;
879 RETURN l_rev_partial_impl_flag;
880
881 EXCEPTION
882 WHEN OTHERS THEN
883 IF l_debug_mode = 'Y' THEN
884 pa_debug.g_err_stage:= 'Unexpected Error' || SQLERRM;
885 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
886 END IF;
887 pa_debug.reset_curr_function;
888 RAISE;
889
890
891 END Is_Rev_Impl_Partially;
892
893 END pa_fin_plan_types_utils;