[Home] [Help]
PACKAGE BODY: APPS.PA_LIFECYCLES_PUB
Source
1 PACKAGE BODY PA_LIFECYCLES_PUB AS
2 /* $Header: PALCDFPB.pls 120.1 2005/08/19 16:35:24 mwasowic noship $ */
3
4 /*-----------------------------------------------------------+
5 | For Details/Comments Refer Package Specification Comments |
6 +-----------------------------------------------------------*/
7
8 PROCEDURE create_lifecycle (
9 P_api_version IN NUMBER := 1.0 ,
10 P_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
11 P_commit IN VARCHAR2 := FND_API.G_FALSE ,
12 P_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
13 P_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
14 P_calling_module IN VARCHAR2 := 'SELF-SERVICE' ,
15 P_debug_mode IN VARCHAR2 := 'N' ,
16 P_max_msg_count IN NUMBER := G_MISS_NUM ,
17 P_lifecycle_short_name IN VARCHAR2 ,
18 P_lifecycle_name IN VARCHAR2 ,
19 P_lifecycle_description IN VARCHAR2 := G_MISS_CHAR ,
20 P_lifecycle_project_usage_type IN VARCHAR2 := 'Y' ,
21 P_lifecycle_product_usage_type IN VARCHAR2 := G_MISS_CHAR ,
22 X_lifecycle_id OUT NOCOPY NUMBER , --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
28 l_api_name CONSTANT VARCHAR(30) := 'create_lifecycle';
29 l_api_version CONSTANT NUMBER := 1.0;
30 l_msg_count NUMBER;
31 l_msg_index_out NUMBER;
32 l_data VARCHAR2(2000);
33 l_msg_data VARCHAR2(2000);
34 l_return_status VARCHAR2(1);
35
36 l_lifecycle_project_usage_type VARCHAR2(1);
37 l_lifecycle_product_usage_type VARCHAR2(1);
38 l_lifecycle_id NUMBER;
39 l_lifecycle_description VARCHAR2(250);
40
41 BEGIN
42
43 IF(p_debug_mode = 'Y') then
44 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle : Entered create_lifecycle...');
45 END IF;
46
47 IF (p_commit = FND_API.G_TRUE) then
48 SAVEPOINT LCYL_CREATE_LIFECYCLE_PUB;
49 END IF;
50
51
52 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
53 p_api_version,
54 l_api_name,
55 g_pkg_name)
56 THEN
57 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
58 END IF;
59
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61
62 -- Initialise message stack
63
64 IF( p_debug_mode = 'Y') THEN
65 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle : Initialising message stack...');
66 END IF;
67
68 pa_debug.init_err_stack('PA_LIFECYCLES_PUB.CREATE_LIFECYCLE');
69
70 IF FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
71 fnd_msg_pub.initialize;
72 END IF;
73
74 IF(p_debug_mode = 'Y') THEN
75 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle :After initialising the stack...');
76 END IF;
77
78 IF(p_debug_mode = 'Y') THEN
79 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle :Checking for checkboxes...');
80 END IF;
81
82 IF (P_lifecycle_name is NULL)
83 THEN
84 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
85 p_msg_name => 'PA_LCYL_NAME_REQUIRED');
86 x_msg_data := 'PA_LCYL_NAME_REQUIRED';
87 x_return_status := FND_API.G_RET_STS_ERROR;
88 END IF;
89
90 IF (P_lifecycle_short_name is NULL)
91 THEN
92 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
93 p_msg_name => 'PA_LCYL_SHORT_NAME_REQUIRED');
94 x_msg_data := 'PA_LCYL_SHORT_NAME_REQUIRED';
95 x_return_status := FND_API.G_RET_STS_ERROR;
96 END IF;
97
98
99 IF (P_lifecycle_project_usage_type is NULL) then
100 l_lifecycle_project_usage_type := 'N';
101 ELSE
102 l_lifecycle_project_usage_type := P_lifecycle_project_usage_type;
103 END IF;
104
105
106 IF (P_lifecycle_product_usage_type is NULL OR
107 P_lifecycle_product_usage_type = G_MISS_CHAR) then
108 l_lifecycle_product_usage_type := 'N';
109 ELSE
110 l_lifecycle_product_usage_type := P_lifecycle_product_usage_type;
111 END IF;
112
113 IF(p_debug_mode = 'Y') THEN
114 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle :Checking for validity of required parameters...');
115 END IF;
116
117
118 IF (nvl(l_lifecycle_project_usage_type,'N') = 'N' AND
119 nvl(l_lifecycle_product_usage_type,'N') = 'N')
120 THEN
121 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
122 p_msg_name => 'PA_LCYL_USAGE_REQUIRED');
123 x_msg_data := 'PA_LCYL_USAGE_REQUIRED';
124 x_return_status := FND_API.G_RET_STS_ERROR;
125 END IF;
126
127 IF P_lifecycle_description = G_MISS_CHAR THEN
128 l_lifecycle_description := null;
129 ELSE
130 l_lifecycle_description := P_lifecycle_description;
131 END IF;
132
133 IF (p_debug_mode = 'Y') THEN
134 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle : checking message count');
135 END IF;
136
137 l_msg_count := FND_MSG_PUB.count_msg;
138
139 If l_msg_count > 0 THEN
140 x_msg_count := l_msg_count;
141 If l_msg_count = 1 THEN
142 pa_interface_utils_pub.get_messages
143 (p_encoded => FND_API.G_TRUE ,
144 p_msg_index => 1 ,
145 p_msg_count => l_msg_count ,
146 p_msg_data => l_msg_data ,
147 p_data => l_data ,
148 p_msg_index_out => l_msg_index_out
149 );
150 x_msg_data := l_data;
151 End if;
152 RAISE FND_API.G_EXC_ERROR;
153 End if;
154
155
156 IF(p_debug_mode = 'Y') THEN
157 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle : Before call to private create API');
158 END IF;
159
160 pa_lifecycles_pvt.create_lifecycle (
161 P_api_version => P_api_version ,
162 p_commit => p_commit ,
163 p_validate_only => p_validate_only ,
164 p_validation_level => p_validation_level ,
165 p_calling_module => p_calling_module ,
166 p_debug_mode => p_debug_mode ,
167 p_max_msg_count => p_max_msg_count ,
168 P_lifecycle_short_name => P_lifecycle_short_name ,
169 P_lifecycle_name => P_lifecycle_name ,
170 P_lifecycle_description => l_lifecycle_description ,
171 P_lifecycle_project_usage_type => l_lifecycle_project_usage_type ,
172 P_lifecycle_product_usage_type => l_lifecycle_product_usage_type ,
173 X_lifecycle_id => l_lifecycle_id ,
174 X_return_status => l_return_status ,
175 X_msg_count => l_msg_count ,
176 X_msg_data => l_msg_data
177 );
178
179 IF (p_debug_mode = 'Y') THEN
180 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle : checking message count after call to private create API ');
181 END IF;
182
183 l_msg_count := FND_MSG_PUB.count_msg;
184
185 If l_msg_count > 0 THEN
186 x_msg_count := l_msg_count;
187 If l_msg_count = 1 THEN
188 pa_interface_utils_pub.get_messages
189 (p_encoded => FND_API.G_TRUE ,
190 p_msg_index => 1 ,
191 p_msg_count => l_msg_count ,
192 p_msg_data => l_msg_data ,
193 p_data => l_data ,
194 p_msg_index_out => l_msg_index_out
195 );
196 x_msg_data := l_data;
197 End if;
198 RAISE FND_API.G_EXC_ERROR;
199 End if;
200
201 x_lifecycle_id := l_lifecycle_id;
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203
204
205 IF FND_API.TO_BOOLEAN(P_COMMIT)
206 THEN
207 COMMIT;
208 END IF;
209
210 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
212 IF p_commit = FND_API.G_TRUE THEN
213 ROLLBACK TO LCYL_CREATE_LIFECYCLE_PUB;
214 END IF;
215
216 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
217 p_procedure_name => 'CREATE_LIFECYCLE',
218 p_error_text => SUBSTRB(SQLERRM,1,240));
219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
220
221 WHEN FND_API.G_EXC_ERROR THEN
222 x_return_status := FND_API.G_RET_STS_ERROR;
223 IF p_commit = FND_API.G_TRUE THEN
224 ROLLBACK TO LCYL_CREATE_LIFECYCLE_PUB;
225 END IF;
226
227 WHEN OTHERS THEN
228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 IF p_commit = FND_API.G_TRUE THEN
230 ROLLBACK TO LCYL_CREATE_LIFECYCLE_PUB;
231 END IF;
232 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
233 p_procedure_name => 'CREATE_LIFECYCLE',
234 p_error_text => SUBSTRB(SQLERRM,1,240));
235
236
237
238 END create_lifecycle;
239
240
241 /*-----------------------------------------------------------+
242 | For Details/Comments Refer Package Specification Comments |
243 +-----------------------------------------------------------*/
244
245
246 PROCEDURE create_lifecycle_phase(
247 P_api_version IN NUMBER := 1.0 ,
248 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE ,
249 p_commit IN VARCHAR2 :=FND_API.G_FALSE ,
250 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
251 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
252 p_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
253 p_debug_mode IN VARCHAR2 :='N' ,
254 P_max_msg_count IN NUMBER :=G_MISS_NUM ,
255 P_lifecycle_id IN NUMBER ,
256 P_phase_status_name IN VARCHAR2 ,
257 P_phase_short_name IN VARCHAR2 ,
258 P_phase_name IN VARCHAR2 ,
259 P_phase_display_sequence IN NUMBER ,
260 P_phase_description IN VARCHAR2 :=G_MISS_CHAR ,
261 X_lifecycle_phase_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
262 X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
263 X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
264 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
265 ) IS
266
267 l_api_name CONSTANT VARCHAR(30) := 'create_lifecycle_phase';
268 l_api_version CONSTANT NUMBER := 1.0;
269 l_msg_count NUMBER;
270 l_msg_index_out NUMBER;
271 l_data VARCHAR2(2000);
272 l_msg_data VARCHAR2(2000);
273 l_return_status VARCHAR2(1);
274
275 l_lifecycle_phase_id NUMBER;
276 l_phase_description VARCHAR2(250);
277 l_lifecycle_exists VARCHAR2(1);
278 l_phase_code VARCHAR2(30);
279
280 c_object_type CONSTANT VARCHAR(30) := 'PA_TASKS';
281 c_project_id CONSTANT NUMBER := 0;
282
283
284
285 CURSOR l_get_lifecycle_csr
286 IS
287 Select 'Y'
288 From dual
289 Where exists(SELECT 'XYZ' from pa_proj_elements
290 WHERE proj_element_id = P_lifecycle_id
291 AND object_type='PA_STRUCTURES'
292 AND project_id=c_project_id);
293
294 CURSOR l_get_phase_csr
295 IS
296 SELECT project_status_code
297 from pa_project_statuses
298 WHERE project_status_name = P_phase_status_name
299 AND status_type = 'PHASE';
300
301
302 BEGIN
303
304 /* This procedure does not take care of uniqueness of phase sequence. This is responsibility of
305 calling environment */
306
307 IF(p_debug_mode = 'Y') THEN
308 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle_phase :Entered create_lifecycle_phase...');
309 END IF;
310
311 IF (p_commit = FND_API.G_TRUE) THEN
312 SAVEPOINT CREATE_LCYL_PHASES_PUB;
313 END IF;
314
315 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version ,
316 p_api_version ,
317 l_api_name ,
318 g_pkg_name)
319 THEN
320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
321 END IF;
322
323
324 x_return_status := FND_API.G_RET_STS_SUCCESS;
325
326
327 IF( p_debug_mode = 'Y') THEN
328 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle_phase : Initialising message stack...');
329 END IF;
330
331 pa_debug.init_err_stack('PA_LIFECYCLES_PUB.CREATE_LIFECYCLE_PHASES');
332
333 IF FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
334 fnd_msg_pub.initialize;
335 END IF;
336
337 IF(p_debug_mode = 'Y') THEN
338 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle_phase :After initialising the stack...');
339 END IF;
340
341 IF (P_phase_name is NULL )
342 THEN
343 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
344 p_msg_name => 'PA_LCYL_PHASE_NAME_REQUIRED');
345 x_msg_data := 'PA_LCYL_PHASE_NAME_REQUIRED';
346 x_return_status := FND_API.G_RET_STS_ERROR;
347 END IF;
348
349 IF (P_phase_short_name is NULL )
350 THEN
351 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
352 p_msg_name => 'PA_LCYL_PHASE_SHORT_NAME_REQ');
353 x_msg_data := 'PA_LCYL_PHASE_SHORT_NAME_REQ';
354 x_return_status := FND_API.G_RET_STS_ERROR;
355 END IF;
356
357 IF (P_phase_display_sequence is NULL )
358 THEN
359 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
360 p_msg_name => 'PA_LCYL_PHASE_SEQ_NO_REQ');
361 x_msg_data := 'PA_LCYL_PHASE_SEQ_NO_REQ';
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 END IF;
364
365 l_lifecycle_exists:='N';
366 OPEN l_get_lifecycle_csr;
367 FETCH l_get_lifecycle_csr INTO l_lifecycle_exists;
368 CLOSE l_get_lifecycle_csr;
369
370 IF(l_lifecycle_exists <> 'Y') THEN
371 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
372 p_msg_name => 'PA_LCYL_NOT_VALID');
373 x_msg_data := 'PA_LCYL_NOT_VALID';
374 x_return_status := FND_API.G_RET_STS_ERROR;
375 END IF;
376
377 OPEN l_get_phase_csr;
378 FETCH l_get_phase_csr INTO l_phase_code;
379 CLOSE l_get_phase_csr;
380
381 IF(l_phase_code is null) THEN
382 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
383 p_msg_name => 'PA_LCYL_PHASE_NOT_VALID');
384 x_msg_data := 'PA_LCYL_PHASE_NOT_VALID';
385 x_return_status := FND_API.G_RET_STS_ERROR;
386 END IF;
387
388
389 IF p_phase_description = G_MISS_CHAR THEN
390 l_phase_description := null;
391 ELSE
392 l_phase_description := P_phase_description;
393 END IF;
394
395 IF (p_debug_mode = 'Y') THEN
396 pa_debug.debug('PA_LIFECYCLES_PUB.create_lifecycle_phase : checking message count');
397 END IF;
398
399 l_msg_count := FND_MSG_PUB.count_msg;
400
401 If l_msg_count > 0 THEN
402 x_msg_count := l_msg_count;
403 If l_msg_count = 1 THEN
404 pa_interface_utils_pub.get_messages
405 (p_encoded => FND_API.G_TRUE ,
406 p_msg_index => 1,
407 p_msg_count => l_msg_count ,
408 p_msg_data => l_msg_data,
409 p_data => l_data,
410 p_msg_index_out => l_msg_index_out );
411 x_msg_data := l_data;
412 End if;
413 RAISE FND_API.G_EXC_ERROR;
414 End if;
415
416
417
418 IF(p_debug_mode = 'Y') THEN
419 pa_debug.debug('CREATE_LIFECYCLE_PHASES PUB: Before call to private API');
420 END IF;
421
422 pa_lifecycles_pvt.create_lifecycle_phase (
423 P_api_version => P_api_version
424 ,p_commit => p_commit
425 ,p_validate_only => p_validate_only
426 ,p_validation_level => p_validation_level
427 ,p_calling_module => p_calling_module
428 ,p_debug_mode => p_debug_mode
429 ,p_max_msg_count => p_max_msg_count
430 ,P_lifecycle_id => p_lifecycle_id
431 ,P_phase_display_sequence => p_phase_display_sequence
432 ,P_phase_code => l_phase_code
433 ,P_phase_short_name => P_phase_short_name
434 ,P_phase_name => P_phase_name
435 ,P_phase_description => l_phase_description
436 ,X_lifecycle_phase_id => l_lifecycle_phase_id
437 ,x_return_status => l_return_status
438 ,x_msg_count => l_msg_count
439 ,X_msg_data => l_msg_data
440 );
441
442 IF (p_debug_mode = 'Y') THEN
443 pa_debug.debug(' PA_LIFECYCLES_PUB.create_lifecycle_phase :: checking message count');
444 END IF;
445
446 l_msg_count := FND_MSG_PUB.count_msg;
447
448 If l_msg_count > 0 THEN
449 x_msg_count := l_msg_count;
450 If l_msg_count = 1 THEN
451 pa_interface_utils_pub.get_messages
452 (p_encoded => FND_API.G_TRUE ,
453 p_msg_index => 1,
454 p_msg_count => l_msg_count ,
455 p_msg_data => l_msg_data,
456 p_data => l_data,
457 p_msg_index_out => l_msg_index_out );
458 x_msg_data := l_data;
459 End if;
460 RAISE FND_API.G_EXC_ERROR;
461 End if;
462
463 x_lifecycle_phase_id := l_lifecycle_phase_id;
464 x_return_status := FND_API.G_RET_STS_SUCCESS;
465
466
467 IF FND_API.TO_BOOLEAN(P_COMMIT)
468 THEN
469 COMMIT;
470 END IF;
471
472
473 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
475 IF p_commit = FND_API.G_TRUE THEN
476 ROLLBACK TO CREATE_LCYL_PHASES_PUB;
477 END IF;
478
479 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
480 p_procedure_name => 'CREATE_LIFECYCLE_PHASE',
481 p_error_text => SUBSTRB(SQLERRM,1,240));
482 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
483
484 WHEN FND_API.G_EXC_ERROR THEN
485 x_return_status := FND_API.G_RET_STS_ERROR;
486 IF p_commit = FND_API.G_TRUE THEN
487 ROLLBACK TO CREATE_LCYL_PHASES_PUB;
488 END IF;
489
490 WHEN OTHERS THEN
491 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
492 IF p_commit = FND_API.G_TRUE THEN
493 ROLLBACK TO CREATE_LCYL_PHASES_PUB;
494 END IF;
495 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
496 p_procedure_name => 'CREATE_LIFECYCLE_PHASE',
497 p_error_text => SUBSTRB(SQLERRM,1,240));
498
499
500
501 end create_lifecycle_phase;
502
503 /*-----------------------------------------------------------+
504 | For Details/Comments Refer Package Specification Comments |
505 +-----------------------------------------------------------*/
506
507 PROCEDURE delete_lifecycle (
508 P_api_version IN NUMBER := 1.0 ,
509 P_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
510 P_commit IN VARCHAR2 := FND_API.G_FALSE ,
511 P_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
512 P_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
513 P_calling_module IN VARCHAR2 := 'SELF-SERVICE' ,
514 P_debug_mode IN VARCHAR2 := 'N' ,
515 P_max_msg_count IN NUMBER := G_MISS_NUM ,
516 P_lifecycle_id IN NUMBER ,
517 p_record_version_number IN NUMBER ,
518 X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
519 X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
520 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
521 )
522 IS
523
524 l_api_name CONSTANT VARCHAR(30) := 'delete_lifecycle';
525 l_api_version CONSTANT NUMBER := 1.0;
526 l_msg_count NUMBER;
527 l_msg_index_out NUMBER;
528 l_data VARCHAR2(2000);
529 l_msg_data VARCHAR2(2000);
530 l_return_status VARCHAR2(1);
531 l_errorcode NUMBER;
532
533 l_lifecycle_exists VARCHAR2(1);
534 l_lifecycle_id NUMBER;
535 l_phase_id NUMBER;
536 l_del_lifecycle_ok VARCHAR2(1);
537 l_life_elem_ver_id NUMBER;
538 l_project_usage_exists VARCHAR2(1):='N';
539 l_product_usage_exists VARCHAR2(1):='N';
540 c_project_type CONSTANT VARCHAR(30) := 'PROJECTS';
541 c_product_type CONSTANT VARCHAR(30) := 'PRODUCTS';
542
543
544
545 c_object_type CONSTANT VARCHAR(30) := 'PA_STRUCTURES';
546 c_project_id CONSTANT NUMBER := 0;
547
548 CURSOR l_get_lifecycle_csr
549 IS
550 Select 'Y'
551 From dual
552 Where exists(SELECT 'XYZ' from pa_proj_elements
553 WHERE proj_element_id = P_lifecycle_id
554 AND object_type= c_object_type
555 AND project_id=c_project_id
556 );
557
558 CURSOR l_phases_csr
559 IS
560 Select elem.proj_element_id, elem.record_version_number
561 From pa_proj_element_versions ever
562 , pa_proj_elements elem
563 Where ever.parent_structure_version_id = l_life_elem_ver_id
564 AND ever.object_type= 'PA_TASKS'
565 AND ever.project_id=c_project_id
566 AND ever.proj_element_id = elem.proj_element_id
567 AND elem.project_id = c_project_id
568 AND elem.object_type = 'PA_TASKS';
569
570 CURSOR l_get_life_elem_ver_id
571 IS
572 Select ELEMENT_VERSION_ID
573 From pa_proj_element_versions
574 Where PROJ_ELEMENT_ID = p_lifecycle_id;
575
576
577 BEGIN
578
579 IF(p_debug_mode = 'Y') then
580 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : Entered delete_lifecycle...');
581 END IF;
582
583 IF (p_commit = FND_API.G_TRUE) then
584 SAVEPOINT LCYL_DELETE_LIFECYCLE_PUB;
585 END IF;
586
587
588 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
589 p_api_version,
590 l_api_name,
591 g_pkg_name)
592 THEN
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594 END IF;
595
596 x_return_status := FND_API.G_RET_STS_SUCCESS;
597
598 -- Initialise message stack
599
600 IF( p_debug_mode = 'Y') THEN
601 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : Initialising message stack...');
602 END IF;
603
604 pa_debug.init_err_stack('PA_LIFECYCLES_PUB.DELETE_LIFECYCLE');
605
606 IF FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
607 fnd_msg_pub.initialize;
608 END IF;
609
610 IF(p_debug_mode = 'Y') THEN
611 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle :After initialising the stack...');
612 END IF;
613
614 -- dbms_output.put_line('After initializing the stack');
615
616
617 l_lifecycle_exists:='N';
618 OPEN l_get_lifecycle_csr;
619 FETCH l_get_lifecycle_csr INTO l_lifecycle_exists;
620 CLOSE l_get_lifecycle_csr;
621
622 IF(l_lifecycle_exists <> 'Y') THEN
623 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
624 p_msg_name => 'PA_LCYL_NOT_VALID');
625 x_msg_data := 'PA_LCYL_NOT_VALID';
626 x_return_status := FND_API.G_RET_STS_ERROR;
627 END IF;
628
629 begin
630 select 'Y'
631 into l_project_usage_exists
632 from SYS.DUAL
633 where exists( select 'XYZ' from pa_lifecycle_usages
634 where lifecycle_id=P_lifecycle_id AND
635 usage_type=c_project_type);
636 exception
637 when NO_DATA_FOUND then
638 l_project_usage_exists := 'N';
639 END;
640
641
642 begin
643 select 'Y'
644 into l_product_usage_exists
645 from SYS.DUAL
646 where exists( select 'XYZ' from pa_lifecycle_usages
647 where lifecycle_id=P_lifecycle_id AND
648 usage_type=c_product_type);
649 exception
650 when NO_DATA_FOUND then
651 l_product_usage_exists := 'N';
652 END;
653
654 IF (p_debug_mode = 'Y') THEN
655 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : checking message count');
656 END IF;
657
658
659 l_msg_count := FND_MSG_PUB.count_msg;
660
661 If l_msg_count > 0 THEN
662 x_msg_count := l_msg_count;
663 If l_msg_count = 1 THEN
664 pa_interface_utils_pub.get_messages
665 (p_encoded => FND_API.G_TRUE ,
666 p_msg_index => 1,
667 p_msg_count => l_msg_count ,
668 p_msg_data => l_msg_data,
669 p_data => l_data,
670 p_msg_index_out => l_msg_index_out );
671 x_msg_data := l_data;
672 End if;
673 RAISE FND_API.G_EXC_ERROR;
674 End if;
675
676 OPEN l_get_life_elem_ver_id;
677 FETCH l_get_life_elem_ver_id INTO l_life_elem_ver_id;
678 CLOSE l_get_life_elem_ver_id;
679
680 IF(p_debug_mode = 'Y') THEN
681 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : Before call to private API check_delete_phase_ok');
682 END IF;
683
684
685 IF(l_project_usage_exists = 'Y') then
686 pa_lifecycles_pvt.check_delete_lifecycle_ok(
687 P_api_version => P_api_version ,
688 p_calling_module => p_calling_module ,
689 p_debug_mode => p_debug_mode ,
690 p_max_msg_count => p_max_msg_count ,
691 P_lifecycle_id => p_lifecycle_id ,
692 P_lifecycle_version_id => l_life_elem_ver_id ,
693 X_return_status => l_return_status ,
694 X_msg_count => l_msg_count ,
695 X_msg_data => l_msg_data ,
696 x_del_lifecycle_ok => l_del_lifecycle_ok
697 );
698 END IF;
699
700 IF(l_product_usage_exists = 'Y') then
701
702 PA_EGO_WRAPPER_PUB.check_delete_lifecycle_ok(
703 p_api_version => P_api_version ,
704 p_lifecycle_id => p_lifecycle_id ,
705 p_init_msg_list => p_init_msg_list ,
706 x_delete_ok => l_del_lifecycle_ok ,
707 x_return_status => l_return_status ,
708 x_errorcode => l_errorcode ,
709 x_msg_count => l_msg_count ,
710 x_msg_data => l_msg_data
711 );
712
713 END IF;
714
715
716 IF (p_debug_mode = 'Y') THEN
717 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : checking message count');
718 END IF;
719
720 l_msg_count := FND_MSG_PUB.count_msg;
721
722 If l_msg_count > 0 THEN
723 x_msg_count := l_msg_count;
724 If l_msg_count = 1 THEN
725 pa_interface_utils_pub.get_messages
726 (p_encoded => FND_API.G_TRUE ,
727 p_msg_index => 1,
728 p_msg_count => l_msg_count ,
729 p_msg_data => l_msg_data,
730 p_data => l_data,
731 p_msg_index_out => l_msg_index_out );
732 x_msg_data := l_data;
733 End if;
734 RAISE FND_API.G_EXC_ERROR;
735 End if;
736
737 IF (l_del_lifecycle_ok <> FND_API.G_TRUE) THEN
738 RAISE FND_API.G_EXC_ERROR;
739 END IF;
740
741 IF (p_debug_mode = 'Y') THEN
742 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : calling pa_lifecycles_pvt.delete_lifecycle_phase for each phase');
743 END IF;
744
745 FOR r IN l_phases_csr LOOP
746 pa_lifecycles_pub.delete_lifecycle_phase(
747 P_api_version => P_api_version ,
748 p_init_msg_list => p_init_msg_list ,
749 p_commit => p_commit ,
750 p_validate_only => p_validate_only ,
751 p_validation_level => p_validation_level ,
752 p_calling_module => p_calling_module ,
753 p_debug_mode => p_debug_mode ,
754 p_max_msg_count => p_max_msg_count ,
755 P_lifecycle_id => p_lifecycle_id ,
756 p_phase_id => r.proj_element_id ,
757 p_record_version_number => r.record_version_number ,
758 X_return_status => l_return_status ,
759 X_msg_count => l_msg_count ,
760 X_msg_data => l_msg_data
761 );
762
763 END LOOP;
764
765 IF (p_debug_mode = 'Y') THEN
766 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : checking message count');
767 END IF;
768
769
770 l_msg_count := FND_MSG_PUB.count_msg;
771 If l_msg_count > 0 THEN
772 x_msg_count := l_msg_count;
773 If l_msg_count = 1 THEN
774 pa_interface_utils_pub.get_messages
775 (p_encoded => FND_API.G_TRUE ,
776 p_msg_index => 1,
777 p_msg_count => l_msg_count ,
778 p_msg_data => l_msg_data,
779 p_data => l_data,
780 p_msg_index_out => l_msg_index_out );
781 x_msg_data := l_data;
782 End if;
783 RAISE FND_API.G_EXC_ERROR;
784 End if;
785
786
787 IF(l_product_usage_exists = 'Y') then
788
789 PA_EGO_WRAPPER_PUB.delete_stale_data_for_lc(
790 p_api_version => P_api_version ,
791 p_lifecycle_id => p_lifecycle_id ,
792 p_init_msg_list => p_init_msg_list ,
793 p_commit => p_commit ,
794 x_errorcode => l_errorcode ,
795 x_return_status => l_return_status ,
796 x_msg_count => l_msg_count ,
797 x_msg_data => l_msg_data);
798 END IF;
799 IF(p_debug_mode = 'Y') THEN
800 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : Before call to private API delete_lifecycle');
801 END IF;
802
803 pa_lifecycles_pvt.delete_lifecycle(
804 P_api_version => P_api_version,
805 p_commit => p_commit,
806 p_validate_only => p_validate_only,
807 p_validation_level => p_validation_level,
808 p_calling_module => p_calling_module,
809 p_debug_mode => p_debug_mode,
810 p_max_msg_count => p_max_msg_count,
811 p_lifecycle_id => p_lifecycle_id,
812 p_record_version_number => p_record_version_number,
813 X_return_status => l_return_status,
814 X_msg_count => l_msg_count,
815 X_msg_data => l_msg_data
816 );
817
818 IF (p_debug_mode = 'Y') THEN
819 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : checking message count');
820 END IF;
821
822 l_msg_count := FND_MSG_PUB.count_msg;
823
824 If l_msg_count > 0 THEN
825 x_msg_count := l_msg_count;
826 If l_msg_count = 1 THEN
827 pa_interface_utils_pub.get_messages
828 (p_encoded => FND_API.G_TRUE ,
829 p_msg_index => 1,
830 p_msg_count => l_msg_count ,
831 p_msg_data => l_msg_data,
832 p_data => l_data,
833 p_msg_index_out => l_msg_index_out );
834 x_msg_data := l_data;
835 End if;
836 RAISE FND_API.G_EXC_ERROR;
837 End if;
838
839 x_return_status := FND_API.G_RET_STS_SUCCESS;
840
841
842 IF FND_API.TO_BOOLEAN(P_COMMIT)
843 THEN
844 COMMIT;
845 END IF;
846
847
848 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
850 IF p_commit = FND_API.G_TRUE THEN
851 ROLLBACK TO LCYL_DELETE_LIFECYCLE_PUB;
852 END IF;
853
854 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
855 p_procedure_name => 'DELETE_LIFECYCLE',
856 p_error_text => SUBSTRB(SQLERRM,1,240));
857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
858
859 WHEN FND_API.G_EXC_ERROR THEN
860 x_return_status := FND_API.G_RET_STS_ERROR;
861 IF p_commit = FND_API.G_TRUE THEN
862 ROLLBACK TO LCYL_DELETE_LIFECYCLE_PUB;
863 END IF;
864
865 WHEN OTHERS THEN
866 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
867 IF p_commit = FND_API.G_TRUE THEN
868 ROLLBACK TO LCYL_DELETE_LIFECYCLE_PUB;
869 END IF;
870 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
871 p_procedure_name => 'DELETE_LIFECYCLE',
872 p_error_text => SUBSTRB(SQLERRM,1,240));
873
874 END delete_lifecycle;
875
876
877 /*-----------------------------------------------------------+
878 | For Details/Comments Refer Package Specification Comments |
879 +-----------------------------------------------------------*/
880
881 PROCEDURE delete_lifecycle_phase (
882 P_api_version IN NUMBER := 1.0 ,
883 P_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
884 P_commit IN VARCHAR2 := FND_API.G_FALSE ,
885 P_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
886 P_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
887 P_calling_module IN VARCHAR2 := 'SELF-SERVICE' ,
888 P_debug_mode IN VARCHAR2 := 'N' ,
889 P_max_msg_count IN NUMBER := G_MISS_NUM ,
890 P_lifecycle_id IN NUMBER ,
891 P_phase_id IN NUMBER ,
892 p_record_version_number IN NUMBER ,
893 X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
894 X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
895 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
896 ) IS
897
898 l_api_name CONSTANT VARCHAR(30) := 'delete_lifecycle_phase';
899 l_api_version CONSTANT NUMBER := 1.0;
900 l_msg_count NUMBER;
901 l_msg_index_out NUMBER;
902 l_data VARCHAR2(2000);
903 l_msg_data VARCHAR2(2000);
904 l_return_status VARCHAR2(1);
905 l_errorcode NUMBER;
906
907 l_phase_exists VARCHAR2(1) :='N';
908 l_lifecycle_exists VARCHAR2(1);
909 l_phase_id NUMBER;
910 l_lifecycle_id NUMBER;
911 l_del_phase_ok VARCHAR2(1);
912
913
914 l_project_usage_exists VARCHAR2(1) :='N';
915 l_product_usage_exists VARCHAR2(1) :='N';
916 c_project_type CONSTANT VARCHAR2(10) :='PROJECTS';
917 c_product_type CONSTANT VARCHAR2(10) :='PRODUCTS';
918 c_object_type CONSTANT VARCHAR(30) := 'PA_TASKS';
919 c_project_id CONSTANT NUMBER :=0;
920
921 CURSOR l_get_phaseexist_csr
922 IS
923 Select 'Y'
924 From dual
925 Where exists(
926 Select 'XYZ'
927 from pa_proj_element_versions child
928 , pa_proj_element_versions parent
929 where child.proj_element_id = P_phase_id
930 and child.parent_structure_version_id = parent.element_version_id
931 and parent.proj_element_id = P_lifecycle_id
932 AND child.object_type= c_object_type
933 AND child.project_id=c_project_id
934 and parent.object_type = 'PA_STRUCTURES'
935 and parent.project_id = c_project_id);
936
937
938 BEGIN
939
940 IF(p_debug_mode = 'Y') then
941 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase : Entered delete_lifecycle_phase...');
942 END IF;
943
944 IF (p_commit = FND_API.G_TRUE) then
945 SAVEPOINT LCYL_DELETE_LCYL_PHASE_PUB;
946 END IF;
947
948
949 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
950 p_api_version,
951 l_api_name,
952 g_pkg_name)
953 THEN
954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
955 END IF;
956
957 x_return_status := FND_API.G_RET_STS_SUCCESS;
958
959 -- Initialise message stack
960
961 IF( p_debug_mode = 'Y') THEN
962 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase : Initialising message stack...');
963 END IF;
964
965 pa_debug.init_err_stack('PA_LIFECYCLES_PUB.DELETE_LIFECYCLE_PHASE');
966
967 IF FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
968 fnd_msg_pub.initialize;
969 END IF;
970
971 IF(p_debug_mode = 'Y') THEN
972 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase :After initialising the stack...');
973 END IF;
974
975 -- dbms_output.put_line('After initializing the stack');
976
977 begin
978 Select 'Y' into l_phase_exists
979 From dual
980 Where exists(
981 Select 'XYZ' from pa_proj_element_versions child
982 , pa_proj_element_versions parent
983 where child.proj_element_id = P_phase_id
984 and child.parent_structure_version_id = parent.element_version_id
985 and parent.proj_element_id = P_lifecycle_id
986 AND child.object_type= c_object_type
987 AND child.project_id=c_project_id
988 and parent.object_type = 'PA_STRUCTURES'
989 and parent.project_id = c_project_id);
990
991 exception
992 when NO_DATA_FOUND then
993 l_phase_exists :='N';
994 end;
995
996
997 IF(l_phase_exists <> 'Y') THEN
998 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
999 p_msg_name => 'PA_LCYL_PHASE_NOT_VALID');
1000 x_msg_data := 'PA_LCYL_PHASE_NOT_VALID';
1001 x_return_status := FND_API.G_RET_STS_ERROR;
1002 END IF;
1003
1004 begin
1005 select 'Y'
1006 into l_project_usage_exists
1007 from SYS.DUAL
1008 where exists( select 'XYZ' from pa_lifecycle_usages
1009 where lifecycle_id=P_lifecycle_id AND
1010 usage_type=c_project_type);
1011 exception
1012 when NO_DATA_FOUND then
1013 l_project_usage_exists := 'N';
1014 END;
1015
1016
1017 begin
1018 select 'Y'
1019 into l_product_usage_exists
1020 from SYS.DUAL
1021 where exists(select 'XYZ' from pa_lifecycle_usages
1022 where lifecycle_id=P_lifecycle_id AND
1023 usage_type=c_product_type);
1024 exception
1025 when NO_DATA_FOUND then
1026 l_product_usage_exists := 'N';
1027 END;
1028
1029 IF (p_debug_mode = 'Y') THEN
1030 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase: checking message count');
1031 END IF;
1032
1033 l_msg_count := FND_MSG_PUB.count_msg;
1034
1035 If l_msg_count > 0 THEN
1036 x_msg_count := l_msg_count;
1037 If l_msg_count = 1 THEN
1038 pa_interface_utils_pub.get_messages
1039 (p_encoded => FND_API.G_TRUE ,
1040 p_msg_index => 1,
1041 p_msg_count => l_msg_count ,
1042 p_msg_data => l_msg_data,
1043 p_data => l_data,
1044 p_msg_index_out => l_msg_index_out );
1045 x_msg_data := l_data;
1046 End if;
1047 RAISE FND_API.G_EXC_ERROR;
1048 End if;
1049
1050 IF(l_project_usage_exists = 'Y') then
1051
1052 IF(p_debug_mode = 'Y') THEN
1053 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : Before call to private API check_delete_lcyl_phase_ok');
1054 END IF;
1055
1056 pa_lifecycles_pvt.check_delete_lcyl_phase_ok(
1057 P_api_version => P_api_version ,
1058 p_calling_module => p_calling_module ,
1059 p_debug_mode => p_debug_mode ,
1060 p_max_msg_count => p_max_msg_count ,
1061 P_lifecycle_id => P_lifecycle_id ,
1062 P_lifecycle_phase_id => p_phase_id ,
1063 X_return_status => l_return_status ,
1064 X_msg_count => l_msg_count ,
1065 X_msg_data => l_msg_data ,
1066 x_delete_ok => l_del_phase_ok
1067 );
1068
1069 END IF;
1070
1071 IF(l_product_usage_exists = 'Y') then
1072
1073 IF(p_debug_mode = 'Y') THEN
1074 pa_debug.debug('PA_EGO_WRAPPER_PUB.check_delete_phase_ok : Before call to private API check_delete_lcyl_phase_ok');
1075 END IF;
1076
1077 PA_EGO_WRAPPER_PUB.check_delete_phase_ok (
1078 p_api_version => P_api_version ,
1079 p_phase_id => p_phase_id ,
1080 p_init_msg_list => p_init_msg_list ,
1081 x_delete_ok => l_del_phase_ok ,
1082 x_return_status => l_return_status ,
1083 x_errorcode => l_errorcode ,
1084 x_msg_count => l_msg_count ,
1085 x_msg_data => l_msg_data
1086 );
1087
1088 END IF;
1089
1090 IF (p_debug_mode = 'Y') THEN
1091 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle : checking message count');
1092 END IF;
1093
1094 l_msg_count := FND_MSG_PUB.count_msg;
1095
1096 If l_msg_count > 0 THEN
1097 x_msg_count := l_msg_count;
1098 If l_msg_count = 1 THEN
1099 pa_interface_utils_pub.get_messages
1100 (p_encoded => FND_API.G_TRUE ,
1101 p_msg_index => 1,
1102 p_msg_count => l_msg_count ,
1103 p_msg_data => l_msg_data,
1104 p_data => l_data,
1105 p_msg_index_out => l_msg_index_out );
1106 x_msg_data := l_data;
1107 End if;
1108 RAISE FND_API.G_EXC_ERROR;
1109 End if;
1110
1111 IF (l_del_phase_ok <> FND_API.G_TRUE) THEN
1112 RAISE FND_API.G_EXC_ERROR;
1113 END IF;
1114
1115
1116 IF(l_product_usage_exists = 'Y') then
1117 PA_EGO_WRAPPER_PUB.process_phase_delete(
1118 p_api_version => P_api_version ,
1119 p_phase_id => p_phase_id ,
1120 p_init_msg_list => p_init_msg_list ,
1121 p_commit => p_commit ,
1122 x_errorcode => l_errorcode ,
1123 x_return_status => l_return_status ,
1124 x_msg_count => l_msg_count ,
1125 x_msg_data => l_msg_data);
1126 END IF;
1127
1128 IF (p_debug_mode = 'Y') THEN
1129 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase : calling pa_lifecycles_pvt.delete_lifecycle_phase for each phase');
1130 END IF;
1131
1132
1133 pa_lifecycles_pvt.delete_lifecycle_phase(
1134 P_api_version => P_api_version ,
1135 p_commit => p_commit ,
1136 p_validate_only => p_validate_only ,
1137 p_validation_level => p_validation_level ,
1138 p_calling_module => p_calling_module ,
1139 p_debug_mode => p_debug_mode ,
1140 p_max_msg_count => p_max_msg_count ,
1141 p_phase_id => p_phase_id ,
1142 p_record_version_number => p_record_version_number ,
1143 X_return_status => l_return_status ,
1144 X_msg_count => l_msg_count ,
1145 X_msg_data => l_msg_data
1146 );
1147
1148 IF (p_debug_mode = 'Y') THEN
1149 pa_debug.debug('PA_LIFECYCLES_PUB.delete_lifecycle_phase : checking message count');
1150 END IF;
1151
1152 l_msg_count := FND_MSG_PUB.count_msg;
1153
1154 If l_msg_count > 0 THEN
1155 x_msg_count := l_msg_count;
1156 If l_msg_count = 1 THEN
1157 pa_interface_utils_pub.get_messages
1158 (p_encoded => FND_API.G_TRUE ,
1159 p_msg_index => 1 ,
1160 p_msg_count => l_msg_count ,
1161 p_msg_data => l_msg_data ,
1162 p_data => l_data ,
1163 p_msg_index_out => l_msg_index_out );
1164 x_msg_data := l_data;
1165 End if;
1166 RAISE FND_API.G_EXC_ERROR;
1167 End if;
1168
1169 x_return_status := FND_API.G_RET_STS_SUCCESS;
1170
1171
1172 IF FND_API.TO_BOOLEAN(P_COMMIT)
1173 THEN
1174 COMMIT;
1175 END IF;
1176
1177
1178 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1180 IF p_commit = FND_API.G_TRUE THEN
1181 ROLLBACK TO LCYL_DELETE_LCYL_PHASE_PUB;
1182 END IF;
1183
1184 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
1185 p_procedure_name => 'DELETE_LIFECYCLE_PHASE',
1186 p_error_text => SUBSTRB(SQLERRM,1,240));
1187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1188
1189 WHEN FND_API.G_EXC_ERROR THEN
1190 x_return_status := FND_API.G_RET_STS_ERROR;
1191 IF p_commit = FND_API.G_TRUE THEN
1192 ROLLBACK TO LCYL_DELETE_LCYL_PHASE_PUB;
1193 END IF;
1194
1195 WHEN OTHERS THEN
1196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1197 IF p_commit = FND_API.G_TRUE THEN
1198 ROLLBACK TO LCYL_DELETE_LCYL_PHASE_PUB;
1199 END IF;
1200 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
1201 p_procedure_name => 'DELETE_LIFECYCLE_PHASE',
1202 p_error_text => SUBSTRB(SQLERRM,1,240));
1203
1204
1205 END delete_lifecycle_phase;
1206
1207
1208 /*-----------------------------------------------------------+
1209 | For Details/Comments Refer Package Specification Comments |
1210 +-----------------------------------------------------------*/
1211
1212 PROCEDURE update_lifecycle (
1213 P_api_version IN NUMBER :=1.0 ,
1214 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1215 p_commit IN VARCHAR2 :=FND_API.G_FALSE ,
1216 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
1217 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
1218 p_calling_module IN VARCHAR2 :='SELF SERVICE' ,
1219 p_debug_mode IN VARCHAR2 :='N' ,
1220 p_max_msg_count IN NUMBER :=G_MISS_NUM ,
1221 P_lifecycle_id IN NUMBER ,
1222 P_lifecycle_short_name IN VARCHAR2 :=G_MISS_CHAR ,
1223 P_lifecycle_name IN VARCHAR2 :=G_MISS_CHAR ,
1224 P_lifecycle_description IN VARCHAR2 :=G_MISS_CHAR ,
1225 P_lifecycle_project_usage_type IN VARCHAR2 :=G_MISS_CHAR ,
1226 P_lifecycle_product_usage_type IN VARCHAR2 :=G_MISS_CHAR ,
1227 P_record_version_number IN NUMBER ,
1228 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1229 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1230 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1231 )
1232 IS
1233
1234 l_api_name CONSTANT VARCHAR(30) := 'update_lifecycle';
1235 l_api_version CONSTANT NUMBER := 1.0;
1236 l_msg_count NUMBER;
1237 l_msg_index_out NUMBER;
1238 l_data VARCHAR2(2000);
1239 l_msg_data VARCHAR2(2000);
1240 l_return_status VARCHAR2(1);
1241
1242 l_lifecycle_project_usage_type VARCHAR2(1);
1243 l_lifecycle_product_usage_type VARCHAR2(1);
1244 l_lifecycle_id NUMBER(15);
1245 l_short_name VARCHAR2(100);
1246 l_lcyl_name VARCHAR2(100);
1247 l_lifecycle_description VARCHAR2(250);
1248 c_object_type CONSTANT VARCHAR(30) := 'PA_STRUCTURES';
1249 c_project_id CONSTANT NUMBER := 0;
1250 l_shname_uniq VARCHAR2(1):='N';
1251 l_data_changed boolean := false;
1252
1253
1254 BEGIN
1255
1256 IF(p_debug_mode = 'Y') THEN
1257 pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle : Entered update_lifecycle...');
1258 END IF;
1259
1260 IF (p_commit = FND_API.G_TRUE) then
1261 SAVEPOINT LCYL_UPDATE_LIFECYCLE_PUB;
1262 END IF;
1263
1264 IF(p_debug_mode = 'Y') THEN
1265 pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle: Checking api compatibility...');
1266 END IF;
1267
1268 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version ,
1269 p_api_version ,
1270 l_api_name ,
1271 g_pkg_name)
1272 THEN
1273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1274 END IF;
1275
1276 x_return_status := FND_API.G_RET_STS_SUCCESS;
1277
1278
1279 l_lifecycle_id := P_lifecycle_id;
1280
1281 -- Initialise message stack
1282
1283 IF( p_debug_mode = 'Y') THEN
1284 pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle : Initialising message stack...');
1285 END IF;
1286
1287 pa_debug.init_err_stack('PA_LIFECYCLES_PUB.UPDATE_LIFECYCLE');
1288
1289 IF FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
1290 fnd_msg_pub.initialize;
1291 END IF;
1292
1293
1294
1295 IF(p_debug_mode = 'Y') THEN
1296 pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle:After initialising the stack..getting the data from database.');
1297 END IF;
1298
1299
1300
1301 BEGIN
1302 select name,element_number,description
1303 into l_lcyl_name,l_short_name,l_lifecycle_description
1304 from pa_proj_elements
1305 where proj_element_id=l_lifecycle_id
1306 AND object_type = c_object_type
1307 AND project_id = c_project_id;
1308 EXCEPTION
1309 when NO_DATA_FOUND then
1310 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1311 p_msg_name => 'PA_LCYL_NOT_VALID');
1312 x_msg_data := 'PA_LCYL_NOT_VALID';
1313 x_return_status := FND_API.G_RET_STS_ERROR;
1314 END;
1315
1316 IF(p_debug_mode = 'Y') THEN
1317 pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle:Checking for null values assigning the updated values');
1318 END IF;
1319
1320 -- Explicit NUlling of name not allowed
1321 IF (P_lifecycle_name is NULL)
1322 THEN
1323 pa_debug.debug('Lifecycle name is NULL....');
1324 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1325 p_msg_name => 'PA_LCYL_NAME_REQUIRED');
1326 x_msg_data := 'PA_LCYL_NAME_REQUIRED';
1327 x_return_status := FND_API.G_RET_STS_ERROR;
1328 ELSIF(P_lifecycle_name <> G_MISS_CHAR) then
1329 IF l_lcyl_name <> P_lifecycle_name THEN
1330 l_data_changed := true;
1331 END IF;
1332 l_lcyl_name := P_lifecycle_name;
1333 END IF;
1334
1335
1336 IF (P_lifecycle_short_name is NULL)
1337 THEN
1338 pa_debug.debug('Lifecycle short name is NULL....');
1339 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1340 p_msg_name => 'PA_LCYL_SHORT_NAME_REQUIRED');
1341 x_msg_data := 'PA_LCYL_SHORT_NAME_REQUIRED';
1342 x_return_status := FND_API.G_RET_STS_ERROR;
1343 ELSIF (P_lifecycle_short_name <> G_MISS_CHAR)
1344 THEN
1345 IF(p_debug_mode = 'Y') THEN
1346 pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: Checking for existing parameters for uniqueness..');
1347 END IF;
1348
1349 IF l_short_name <> P_lifecycle_short_name THEN
1350 BEGIN
1351 Select 'N'
1352 Into l_shname_uniq
1353 from SYS.DUAL
1354 Where exists(Select 'XYZ' from pa_proj_elements
1355 where element_number = P_lifecycle_short_name
1356 AND object_type=c_object_type
1357 AND project_id = c_project_id);
1358 exception
1359 when NO_DATA_FOUND then
1360 l_shname_uniq := 'Y';
1361 END;
1362
1363 IF(l_shname_uniq <> 'Y') THEN
1364 pa_debug.debug('The short name is in use...');
1365 PA_UTILS.ADD_MESSAGE(
1366 p_app_short_name => 'PA',
1367 p_msg_name => 'PA_LCYL_SHORT_NAME_EXISTS');
1368 x_msg_data := 'PA_LCYL_SHORT_NAME_EXISTS';
1369 x_return_status := FND_API.G_RET_STS_ERROR;
1370 ELSE
1371 IF l_short_name <> P_lifecycle_short_name THEN
1372 l_data_changed := true;
1373 END IF;
1374 END IF;
1375 END IF;
1376 l_short_name := P_lifecycle_short_name;
1377 END IF;
1378
1379 IF P_lifecycle_description is null THEN
1380 l_lifecycle_description := null;
1381 ELSIF(P_lifecycle_description <> G_MISS_CHAR) then
1382 IF l_lifecycle_description <> P_lifecycle_description THEN
1383 l_data_changed := true;
1384 END IF;
1385 l_lifecycle_description := P_lifecycle_description;
1386 END IF;
1387
1388 IF (p_debug_mode = 'Y') THEN
1389 pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle : checking message count'||FND_MSG_PUB.count_msg);
1390 END IF;
1391
1392
1393 IF(p_debug_mode = 'Y') THEN
1394 pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle : Before call to private API');
1395 END IF;
1396
1397
1398
1399 IF (P_lifecycle_project_usage_type is null OR P_lifecycle_product_usage_type is null)
1400 THEN
1401 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1402 p_msg_name => 'PA_LCYL_USAGE_REQUIRED');
1403 x_msg_data := 'PA_LCYL_USAGE_REQUIRED';
1404 x_return_status := FND_API.G_RET_STS_ERROR;
1405 END IF;
1406
1407 BEGIN
1408 select 'Y'
1409 into l_lifecycle_project_usage_type
1410 from SYS.DUAL
1411 where exists ( select '1' from pa_lifecycle_usages
1412 where lifecycle_id=l_lifecycle_id
1413 AND usage_type='PROJECTS');
1414 EXCEPTION
1415 when NO_DATA_FOUND then
1416 l_lifecycle_project_usage_type := 'N';
1417 END;
1418
1419 IF (P_lifecycle_project_usage_type <> G_MISS_CHAR) THEN
1420 IF l_lifecycle_project_usage_type <> P_lifecycle_project_usage_type THEN
1421 l_data_changed := true;
1422 END IF;
1423 l_lifecycle_project_usage_type := P_lifecycle_project_usage_type;
1424 END IF;
1425
1426 BEGIN
1427 select 'Y'
1428 into l_lifecycle_product_usage_type
1429 from SYS.DUAL
1430 where exists ( select '1' from pa_lifecycle_usages
1431 where lifecycle_id=l_lifecycle_id
1432 AND usage_type='PRODUCTS');
1433 EXCEPTION
1434 when NO_DATA_FOUND then
1435 l_lifecycle_product_usage_type := 'N';
1436 END;
1437
1438 IF (P_lifecycle_product_usage_type <> G_MISS_CHAR) THEN
1439 IF l_lifecycle_product_usage_type <> P_lifecycle_product_usage_type THEN
1440 l_data_changed := true;
1441 END IF;
1442 l_lifecycle_product_usage_type := P_lifecycle_product_usage_type;
1443 END IF;
1444
1445 IF (l_lifecycle_project_usage_type ='N' AND l_lifecycle_product_usage_type ='N')
1446 THEN
1447 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1448 p_msg_name => 'PA_LCYL_USAGE_REQUIRED');
1449 x_msg_data := 'PA_LCYL_USAGE_REQUIRED';
1450 x_return_status := FND_API.G_RET_STS_ERROR;
1451 END IF;
1452
1453
1454 /* The below code could be used to dod not hit the data if nothing is changed
1455 IF (NOT (l_data_changed)) THEN
1456 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1457 p_msg_name => 'PA_NO_CHANGES_TO_UPDATE');
1458 x_msg_data := 'PA_NO_CHANGES_TO_UPDATE';
1459 x_return_status := FND_API.G_RET_STS_ERROR;
1460 END IF;
1461 */
1462
1463 IF(p_debug_mode = 'Y') THEN
1464 pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle:After checking for null values assigning the updated values');
1465 END IF;
1466
1467 IF (p_debug_mode = 'Y') THEN
1468 pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle: checking message count');
1469 END IF;
1470
1471 l_msg_count := FND_MSG_PUB.count_msg;
1472
1473 If l_msg_count > 0 THEN
1474 x_msg_count := l_msg_count;
1475 If l_msg_count = 1 THEN
1476 pa_interface_utils_pub.get_messages
1477 (p_encoded => FND_API.G_TRUE ,
1478 p_msg_index => 1,
1479 p_msg_count => l_msg_count ,
1480 p_msg_data => l_msg_data,
1481 p_data => l_data,
1482 p_msg_index_out => l_msg_index_out );
1483 x_msg_data := l_data;
1484 End if;
1485 RAISE FND_API.G_EXC_ERROR;
1486 End if;
1487
1488
1489 IF(p_debug_mode = 'Y') THEN
1490 pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle: Before call to private update API');
1491 END IF;
1492
1493 pa_lifecycles_pvt.update_lifecycle (
1494 P_api_version => P_api_version ,
1495 p_commit => p_commit ,
1496 p_validate_only => p_validate_only ,
1497 p_validation_level => p_validation_level ,
1498 p_calling_module => p_calling_module ,
1499 p_debug_mode => p_debug_mode ,
1500 p_max_msg_count => p_max_msg_count ,
1501 p_lifecycle_id => l_lifecycle_id ,
1502 P_lifecycle_short_name => l_short_name ,
1503 P_lifecycle_name => l_lcyl_name ,
1504 P_lifecycle_description => l_lifecycle_description ,
1505 P_lifecycle_project_usage_type => l_lifecycle_project_usage_type ,
1506 P_lifecycle_product_usage_type => l_lifecycle_product_usage_type ,
1507 P_record_version_number => p_record_version_number ,
1508 X_return_status => l_return_status ,
1509 X_msg_count => l_msg_count ,
1510 X_msg_data => l_msg_data
1511 );
1512
1513
1514 IF (p_debug_mode = 'Y') THEN
1515 pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle : checking message count After call to private update API');
1516 END IF;
1517
1518 IF (p_debug_mode = 'Y') THEN
1519 pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle: checking message count');
1520 END IF;
1521
1522 l_msg_count := FND_MSG_PUB.count_msg;
1523
1524 If l_msg_count > 0 THEN
1525 x_msg_count := l_msg_count;
1526 If l_msg_count = 1 THEN
1527 pa_interface_utils_pub.get_messages
1528 (p_encoded => FND_API.G_TRUE ,
1529 p_msg_index => 1 ,
1530 p_msg_count => l_msg_count ,
1531 p_msg_data => l_msg_data ,
1532 p_data => l_data ,
1533 p_msg_index_out => l_msg_index_out );
1534 x_msg_data := l_data;
1535 End if;
1536 RAISE FND_API.G_EXC_ERROR;
1537 End if;
1538
1539 x_return_status := FND_API.G_RET_STS_SUCCESS;
1540
1541
1542 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
1543 COMMIT;
1544 END IF;
1545
1546
1547 EXCEPTION
1548
1549 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1551 IF p_commit = FND_API.G_TRUE THEN
1552 ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PUB;
1553 END IF;
1554
1555 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
1556 p_procedure_name => 'UPDATE_LIFECYCLE',
1557 p_error_text => SUBSTRB(SQLERRM,1,240));
1558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1559
1560 WHEN FND_API.G_EXC_ERROR THEN
1561 x_return_status := FND_API.G_RET_STS_ERROR;
1562 IF p_commit = FND_API.G_TRUE THEN
1563 ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PUB;
1564 END IF;
1565
1566 WHEN OTHERS THEN
1567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1568 IF p_commit = FND_API.G_TRUE THEN
1569 ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PUB;
1570 END IF;
1571 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
1572 p_procedure_name => 'UPDATE_LIFECYCLE',
1573 p_error_text => SUBSTRB(SQLERRM,1,240));
1574
1575
1576 END update_lifecycle;
1577
1578
1579 /*-----------------------------------------------------------+
1580 | For Details/Comments Refer Package Specification Comments |
1581 +-----------------------------------------------------------*/
1582
1583
1584 PROCEDURE update_lifecycle_phase (
1585 P_api_version IN NUMBER :=1.0 ,
1586 p_init_msg_list IN VARCHAR2 :=FND_API.G_TRUE ,
1587 p_commit IN VARCHAR2 :=FND_API.G_FALSE ,
1588 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
1589 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
1590 p_calling_module IN VARCHAR2 :='SELF SERVICE' ,
1591 p_debug_mode IN VARCHAR2 :='N' ,
1592 P_max_msg_count IN NUMBER :=G_MISS_NUM ,
1593 P_lifecycle_id IN NUMBER ,
1594 P_lifecycle_phase_id IN NUMBER ,
1595 P_phase_status_name IN VARCHAR2 :=G_MISS_CHAR ,
1596 P_phase_short_name IN VARCHAR2 :=G_MISS_CHAR ,
1597 P_phase_name IN VARCHAR2 :=G_MISS_CHAR ,
1598 P_phase_display_sequence IN NUMBER :=G_MISS_NUM ,
1599 P_phase_description IN VARCHAR2 :=G_MISS_CHAR ,
1600 P_record_version_number IN NUMBER ,
1601 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1602 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1603 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1604 )IS
1605
1606 l_api_name CONSTANT VARCHAR(30) := 'update_lifecycle_phase';
1607 l_api_version CONSTANT NUMBER := 1.0;
1608 l_msg_count NUMBER;
1609 l_msg_index_out NUMBER;
1610 l_data VARCHAR2(2000);
1611 l_msg_data VARCHAR2(2000);
1612 l_return_status VARCHAR2(1);
1613
1614 l_lifecycle_id NUMBER;
1615 l_phase_short_name VARCHAR2(100);
1616 l_phase_name VARCHAR2(240);
1617 l_seqn NUMBER;
1618 l_org_seq NUMBER;
1619 l_phase_code VARCHAR2(30);
1620 l_new_phase_code VARCHAR2(30);
1621 l_org_phase_code VARCHAR2(30);
1622 l_phase_description VARCHAR2(250);
1623 c_object_type CONSTANT VARCHAR(30) := 'PA_TASKS';
1624 c_project_id CONSTANT NUMBER :=0;
1625 l_update_ok VARCHAR2(1) := FND_API.G_FALSE;
1626 l_data_changed boolean := false;
1627
1628
1629 CURSOR l_get_phase_csr
1630 IS
1631 SELECT project_status_code
1632 from pa_project_statuses
1633 WHERE project_status_name = P_phase_status_name
1634 AND status_type = 'PHASE';
1635
1636
1637 BEGIN
1638 /* Note that check for duplicate shortname and sequence can not be done here. as user may want to update
1639 two records at the same time, swapping the values */
1640
1641 IF(p_debug_mode = 'Y') then
1642 pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle_phase : Entered update_lifecycle_phase...');
1643 END IF;
1644
1645 IF (p_commit = FND_API.G_TRUE) then
1646 SAVEPOINT LCYL_UPD_LC_PHASE_PUB;
1647 END IF;
1648
1649 IF(p_debug_mode = 'Y') THEN
1650 pa_debug.debug('PA_LIFECYCLES_PUB.Update_Lifecycle_phases: Checking api compatibility...');
1651 END IF;
1652
1653 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version ,
1654 p_api_version ,
1655 l_api_name ,
1656 g_pkg_name)
1657 THEN
1658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1659 END IF;
1660
1661 x_return_status := FND_API.G_RET_STS_SUCCESS;
1662
1663
1664
1665 -- Initialise message stack
1666
1667 IF( p_debug_mode = 'Y') THEN
1668 pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle_phase : Initialising message stack...');
1669 END IF;
1670
1671 pa_debug.init_err_stack('PA_LIFECYCLES_PUB.UPDATE_LIFECYCLE_PHASE');
1672
1673 IF FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) THEN
1674 fnd_msg_pub.initialize;
1675 END IF;
1676
1677 IF(p_debug_mode = 'Y') THEN
1678 pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle_phase :After initialising the stack..and getting the old data from database.');
1679 END IF;
1680
1681 BEGIN
1682 select prj.name,prj.element_number,prj.description,prj.phase_code,elem.display_sequence
1683 into l_phase_name,l_phase_short_name,l_phase_description,l_phase_code,l_seqn
1684 from pa_proj_elements prj
1685 , pa_proj_element_versions elem
1686 where prj.proj_element_id=P_lifecycle_phase_id
1687 and prj.project_id=c_project_id
1688 and prj.object_type=c_object_type
1689 and prj.proj_element_id=elem.proj_element_id;
1690
1691 l_org_phase_code := l_phase_code;
1692 l_org_seq := l_seqn;
1693
1694 EXCEPTION
1695 when NO_DATA_FOUND then
1696 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1697 p_msg_name => 'PA_LCYL_PHASE_NOT_VALID');
1698 x_msg_data := 'PA_LCYL_PHASE_NOT_VALID';
1699 x_return_status := FND_API.G_RET_STS_ERROR;
1700 END;
1701
1702
1703 IF(p_debug_mode = 'Y') THEN
1704 pa_debug.debug('PA_LIFECYCLES_PUB.Update_lifecycle_phase:Checking for null values assigning the updated values');
1705 END IF;
1706
1707 -- Explicit NULLing not allowed
1708 IF (P_phase_name is NULL)
1709 THEN
1710 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1711 p_msg_name => 'PA_LCYL_PHASE_NAME_REQUIRED');
1712 x_msg_data := 'PA_LCYL_PHASE_NAME_REQUIRED';
1713 x_return_status := FND_API.G_RET_STS_ERROR;
1714 ELSIF(P_phase_name <> G_MISS_CHAR) then
1715 IF l_phase_name <> P_phase_name THEN
1716 l_data_changed := true;
1717 END IF;
1718 l_phase_name := P_phase_name;
1719 END IF;
1720
1721 IF (P_phase_short_name is NULL)
1722 THEN
1723 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1724 p_msg_name => 'PA_LCYL_PHASE_SHORT_NAME_REQ');
1725 x_msg_data := 'PA_LCYL_PHASE_SHORT_NAME_REQ';
1726 x_return_status := FND_API.G_RET_STS_ERROR;
1727 elsif(P_phase_short_name <> G_MISS_CHAR) then
1728 IF l_phase_short_name <> P_phase_short_name THEN
1729 l_data_changed := true;
1730 END IF;
1731 l_phase_short_name := P_phase_short_name;
1732 END IF;
1733
1734 IF (P_phase_status_name is NULL)
1735 THEN
1736 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1737 p_msg_name => 'PA_LCYL_PHASE_NOT_VALID');
1738 x_msg_data := 'PA_LCYL_PHASE_NOT_VALID';
1739 x_return_status := FND_API.G_RET_STS_ERROR;
1740 ELSIF (P_phase_status_name <> G_MISS_CHAR)
1741 THEN
1742 OPEN l_get_phase_csr;
1743 FETCH l_get_phase_csr INTO l_new_phase_code;
1744 CLOSE l_get_phase_csr;
1745
1746 IF(l_new_phase_code is null) THEN
1747 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1748 p_msg_name => 'PA_LCYL_PHASE_NOT_VALID');
1749 x_msg_data := 'PA_LCYL_PHASE_NOT_VALID';
1750 x_return_status := FND_API.G_RET_STS_ERROR;
1751 END IF;
1752
1753 IF l_phase_code <> l_new_phase_code THEN
1754 l_data_changed := true;
1755 END IF;
1756 l_phase_code := l_new_phase_code;
1757 END IF;
1758
1759 IF (P_phase_display_sequence is NULL)
1760 THEN
1761 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1762 p_msg_name => 'PA_LCYL_PHASE_SEQ_NO_REQ');
1763 x_msg_data := 'PA_LCYL_PHASE_SEQ_NO_REQ';
1764 x_return_status := FND_API.G_RET_STS_ERROR;
1765 elsif(P_phase_display_sequence <> G_MISS_NUM) then
1766 IF l_seqn <> P_phase_display_sequence THEN
1767 l_data_changed := true;
1768 END IF;
1769 l_seqn := P_phase_display_sequence;
1770 END IF;
1771
1772 IF (P_phase_description IS NULL) THEN
1773 l_phase_description := null;
1774 elsif(P_phase_description <> G_MISS_CHAR) then
1775 IF l_phase_description <> P_phase_description THEN
1776 l_data_changed := true;
1777 END IF;
1778 l_phase_description := P_phase_description;
1779 END IF;
1780
1781 IF(p_debug_mode = 'Y') THEN
1782 pa_debug.debug('PA_LIFECYCLES_PUB.Update_lifecycle_phase:After checking for null values assigning the updated values');
1783 END IF;
1784
1785 /* The below code could be used to dod not hit the data if nothing is changed
1786 IF (NOT (l_data_changed)) THEN
1787 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1788 p_msg_name => 'PA_NO_CHANGES_TO_UPDATE');
1789 x_msg_data := 'PA_NO_CHANGES_TO_UPDATE';
1790 x_return_status := FND_API.G_RET_STS_ERROR;
1791 END IF;
1792 */
1793
1794 IF (p_debug_mode = 'Y') THEN
1795 pa_debug.debug('PA_LIFECYCLES_PUB.Update_lifecycle_phase: checking message count');
1796 END IF;
1797
1798 l_msg_count := FND_MSG_PUB.count_msg;
1799
1800 If l_msg_count > 0 THEN
1801 x_msg_count := l_msg_count;
1802 If l_msg_count = 1 THEN
1803 pa_interface_utils_pub.get_messages
1804 (p_encoded => FND_API.G_TRUE ,
1805 p_msg_index => 1 ,
1806 p_msg_count => l_msg_count ,
1807 p_msg_data => l_msg_data ,
1808 p_data => l_data ,
1809 p_msg_index_out => l_msg_index_out );
1810 x_msg_data := l_data;
1811 End if;
1812 RAISE FND_API.G_EXC_ERROR;
1813 End if;
1814
1815 IF ((P_phase_display_sequence <> G_MISS_NUM AND l_seqn <> l_org_seq)
1816 OR (P_phase_status_name <> G_MISS_CHAR AND l_phase_code <> l_org_phase_code))
1817 THEN
1818 IF (p_debug_mode = 'Y') THEN
1819 pa_debug.debug('PA_LIFECYCLES_PUB.Update_lifecycle_phase: Calling private api check_delete_lcyl_phase_ok');
1820 END IF;
1821
1822 pa_lifecycles_pvt.check_delete_lcyl_phase_ok(
1823 P_api_version =>l_api_version ,
1824 p_calling_module =>p_calling_module ,
1825 p_debug_mode =>p_debug_mode ,
1826 P_max_msg_count =>P_max_msg_count ,
1827 P_lifecycle_id =>P_lifecycle_id ,
1828 P_lifecycle_phase_id =>P_lifecycle_phase_id ,
1829 x_delete_ok =>l_update_ok ,
1830 x_return_status =>l_return_status ,
1831 x_msg_count =>l_msg_count ,
1832 X_msg_data =>l_data
1833 );
1834
1835 l_msg_count := FND_MSG_PUB.count_msg;
1836 IF (p_debug_mode = 'Y') THEN
1837 pa_debug.debug('PA_LIFECYCLES_PUB.Update_lifecycle_phase: checking message count');
1838 END IF;
1839 If l_msg_count > 0 THEN
1840 x_msg_count := l_msg_count;
1841 If l_msg_count = 1 THEN
1842 pa_interface_utils_pub.get_messages
1843 (p_encoded => FND_API.G_TRUE ,
1844 p_msg_index => 1,
1845 p_msg_count => l_msg_count ,
1846 p_msg_data => l_msg_data,
1847 p_data => l_data,
1848 p_msg_index_out => l_msg_index_out );
1849 x_msg_data := l_data;
1850 End if;
1851 RAISE FND_API.G_EXC_ERROR;
1852 End if;
1853
1854 IF (l_update_ok <>FND_API.G_TRUE) THEN
1855 RAISE FND_API.G_EXC_ERROR;
1856 END IF;
1857
1858 END IF;
1859
1860
1861 IF(p_debug_mode = 'Y') THEN
1862 pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle_phase : Before call to private API update_lifecycle_phase');
1863 END IF;
1864
1865 pa_lifecycles_pvt.update_lifecycle_phase (
1866 P_api_version =>1.0 ,
1867 p_commit =>p_commit ,
1868 p_validate_only =>p_validate_only ,
1869 p_validation_level =>p_validation_level ,
1870 p_calling_module =>p_calling_module ,
1871 p_debug_mode =>p_debug_mode ,
1872 P_max_msg_count =>P_max_msg_count ,
1873 P_lifecycle_id => P_lifecycle_id ,
1874 P_lifecycle_phase_id =>P_lifecycle_phase_id ,
1875 P_phase_display_sequence =>l_seqn ,
1876 P_phase_code =>l_phase_code ,
1877 P_phase_short_name =>l_phase_short_name ,
1878 P_phase_name =>l_phase_name ,
1879 P_phase_description =>l_phase_description ,
1880 P_record_version_number =>P_record_version_number ,
1881 x_return_status =>l_return_status ,
1882 x_msg_count =>l_msg_count ,
1883 X_msg_data =>l_data
1884 );
1885
1886 IF (p_debug_mode = 'Y') THEN
1887 pa_debug.debug('PA_LIFECYCLES_PUB.update_lifecycle_phase : checking message count after call to private API update_lifecycle_phase');
1888 END IF;
1889
1890 l_msg_count := FND_MSG_PUB.count_msg;
1891
1892 If l_msg_count > 0 THEN
1893 x_msg_count := l_msg_count;
1894 If l_msg_count = 1 THEN
1895 pa_interface_utils_pub.get_messages
1896 (p_encoded => FND_API.G_TRUE ,
1897 p_msg_index => 1,
1898 p_msg_count => l_msg_count ,
1899 p_msg_data => l_msg_data,
1900 p_data => l_data,
1901 p_msg_index_out => l_msg_index_out );
1902 x_msg_data := l_data;
1903 End if;
1904 RAISE FND_API.G_EXC_ERROR;
1905 End if;
1906
1907 x_return_status := FND_API.G_RET_STS_SUCCESS;
1908
1909
1910 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
1911 COMMIT;
1912 END IF;
1913
1914
1915 EXCEPTION
1916 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1918 IF p_commit = FND_API.G_TRUE THEN
1919 ROLLBACK TO LCYL_UPD_LC_PHASE_PUB;
1920 END IF;
1921
1922 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
1923 p_procedure_name => 'UPDATE_LIFECYCLE_PHASE',
1924 p_error_text => SUBSTRB(SQLERRM,1,240));
1925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1926
1927 WHEN FND_API.G_EXC_ERROR THEN
1928 x_return_status := FND_API.G_RET_STS_ERROR;
1929 IF p_commit = FND_API.G_TRUE THEN
1930 ROLLBACK TO LCYL_UPD_LC_PHASE_PUB;
1931 END IF;
1932
1933 WHEN OTHERS THEN
1934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1935 IF p_commit = FND_API.G_TRUE THEN
1936 ROLLBACK TO LCYL_UPD_LC_PHASE_PUB;
1937 END IF;
1938 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PUB',
1939 p_procedure_name => 'UPDATE_LIFECYCLE_PHASE',
1940 p_error_text => SUBSTRB(SQLERRM,1,240));
1941
1942
1943 END update_lifecycle_phase;
1944
1945
1946 END PA_LIFECYCLES_PUB;