[Home] [Help]
PACKAGE BODY: APPS.PA_LIFECYCLES_PVT
Source
1 PACKAGE BODY PA_LIFECYCLES_PVT AS
2 /* $Header: PALCDFVB.pls 120.1 2005/08/19 16:35:32 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_commit IN VARCHAR2 :=FND_API.G_FALSE ,
11 P_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
12 P_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
13 P_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
14 P_debug_mode IN VARCHAR2 :='N' ,
15 P_max_msg_count IN NUMBER :=G_MISS_NUM ,
16 P_lifecycle_short_name IN VARCHAR2 ,
17 P_lifecycle_name IN VARCHAR2 ,
18 P_lifecycle_description IN VARCHAR2 ,
19 P_lifecycle_project_usage_type IN VARCHAR2 ,
20 P_lifecycle_product_usage_type IN VARCHAR2 ,
21 X_lifecycle_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
22 X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
23 X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
24 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
25 ) IS
26
27 l_api_name CONSTANT VARCHAR(30) := 'create_lifecycle';
28 l_api_version CONSTANT NUMBER := 1.0;
29 l_msg_count NUMBER;
30 l_msg_index_out NUMBER;
31 l_data VARCHAR2(2000);
32 l_msg_data VARCHAR2(2000);
33 l_return_status VARCHAR2(1);
34
35
36 l_is_uniq VARCHAR2(1) :='N';
37 l_row_id VARCHAR2(30);
38 l_lifecycle_id NUMBER;
39 l_pev_id NUMBER;
40 l_pev_struct_id NUMBER;
41 l_proj_struct_type_id NUMBER;
42 l_lcyl_usage_id NUMBER;
43 l_pev_sched_id NUMBER;
44 l_obj_relnship_id NUMBER;
45 l_structure_type_id NUMBER;
46
47
48 c_object_type CONSTANT VARCHAR(30) := 'PA_STRUCTURES';
49 c_project_id CONSTANT NUMBER := 0;
50 c_lifecycle CONSTANT VARCHAR(30) := 'LIFECYCLE';
51
52 CURSOR cur_struc_type_id
53 IS
54 SELECT structure_type_id
55 FROM pa_structure_types
56 WHERE structure_type_class_code = c_lifecycle;
57
58 BEGIN
59
60
61 IF(p_debug_mode = 'Y') THEN
62 pa_debug.debug('CREATE_LIFECYCLE PVT: Inside create_lifecycle...');
63 END IF;
64
65 IF(p_commit = FND_API.G_TRUE) THEN
66 SAVEPOINT LCYL_CREATE_LIFECYCLE_PVT;
67 END IF;
68
69 IF(p_debug_mode = 'Y') THEN
70 pa_debug.debug('CREATE_LIFECYCLE PVT: Checking api compatibility...');
71 END IF;
72
73 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version ,
74 p_api_version ,
75 l_api_name ,
76 g_pkg_name)
77 THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79 END IF;
80
81
82 x_return_status := FND_API.G_RET_STS_SUCCESS;
83
84 IF(p_debug_mode = 'Y') THEN
85 pa_debug.debug('CREATE_LIFECYCLE PVT: Checking for validations on parameters...');
86 END IF;
87
88 BEGIN
89 SELECT 'N'
90 INTO l_is_uniq
91 FROM dual
92 WHERE exists(Select 'XYZ' from pa_proj_elements
93 WHERE element_number = P_lifecycle_short_name
94 AND object_type=c_object_type
95 AND project_id=c_project_id);
96 EXCEPTION
97 when NO_DATA_FOUND then -- the short name is unique
98 l_is_uniq := 'Y';
99 END;
100 -- This short name is already in use
101
102 IF(l_is_uniq <> 'Y') THEN
103 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
104 p_msg_name => 'PA_LCYL_SHORT_NAME_EXISTS'
105 );
106 x_msg_data := 'PA_LCYL_SHORT_NAME_EXISTS';
107 x_return_status := FND_API.G_RET_STS_ERROR;
108 END IF;
109
110 IF (p_debug_mode = 'Y') THEN
111 pa_debug.debug('PA_LIFECYCLES_PVT.create_lifecycle : checking message count');
112 END IF;
113
114 l_msg_count := FND_MSG_PUB.count_msg;
115
116
117 If l_msg_count > 0 THEN
118 x_msg_count := l_msg_count;
119 If l_msg_count = 1 THEN
120 pa_interface_utils_pub.get_messages(
121 p_encoded => FND_API.G_TRUE ,
122 p_msg_index => 1 ,
123 p_msg_count => l_msg_count ,
124 p_msg_data => l_msg_data ,
125 p_data => l_data ,
126 p_msg_index_out => l_msg_index_out
127 );
128 x_msg_data := l_data;
129 End if;
130 RAISE FND_API.G_EXC_ERROR;
131 End if;
132
133
134 IF(p_debug_mode = 'Y') THEN
135 pa_debug.debug('CREATE_LIFECYCLE PVT:Obtaining lifecycle_id...');
136 END IF;
137
138 SELECT PA_TASKS_S.NEXTVAL
139 INTO l_lifecycle_id
140 FROM dual;
141
142 IF(p_debug_mode = 'Y') THEN
143 pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into pa_proj_elements...');
144 END IF;
145
146 PA_PROJ_ELEMENTS_PKG.Insert_Row(
147 X_ROW_ID => l_row_id ,
148 X_PROJ_ELEMENT_ID => l_lifecycle_id ,
149 X_PROJECT_ID => c_project_id ,
150 X_OBJECT_TYPE => c_object_type ,
151 X_ELEMENT_NUMBER => P_lifecycle_short_name ,
152 X_NAME => P_lifecycle_name ,
153 X_DESCRIPTION => P_lifecycle_description ,
154 X_STATUS_CODE => NULL ,
155 X_WF_STATUS_CODE => NULL ,
156 X_PM_PRODUCT_CODE => NULL ,
157 X_PM_TASK_REFERENCE => NULL ,
158 X_CLOSED_DATE => NULL ,
159 X_LOCATION_ID => NULL ,
160 X_MANAGER_PERSON_ID => NULL ,
161 X_CARRYING_OUT_ORGANIZATION_ID => NULL ,
162 X_TYPE_ID => NULL ,
163 X_PRIORITY_CODE => NULL ,
164 X_INC_PROJ_PROGRESS_FLAG => NULL ,
165 X_REQUEST_ID => NULL ,
166 X_PROGRAM_APPLICATION_ID => NULL ,
167 X_PROGRAM_ID => NULL ,
168 X_PROGRAM_UPDATE_DATE => NULL ,
169 X_LINK_TASK_FLAG => NULL ,
170 X_ATTRIBUTE_CATEGORY => NULL ,
171 X_ATTRIBUTE1 => NULL ,
172 X_ATTRIBUTE2 => NULL ,
173 X_ATTRIBUTE3 => NULL ,
174 X_ATTRIBUTE4 => NULL ,
175 X_ATTRIBUTE5 => NULL ,
176 X_ATTRIBUTE6 => NULL ,
177 X_ATTRIBUTE7 => NULL ,
178 X_ATTRIBUTE8 => NULL ,
179 X_ATTRIBUTE9 => NULL ,
180 X_ATTRIBUTE10 => NULL ,
181 X_ATTRIBUTE11 => NULL ,
182 X_ATTRIBUTE12 => NULL ,
183 X_ATTRIBUTE13 => NULL ,
184 X_ATTRIBUTE14 => NULL ,
185 X_ATTRIBUTE15 => NULL ,
186 X_TASK_WEIGHTING_DERIV_CODE => NULL ,
187 X_WORK_ITEM_CODE => NULL ,
188 X_UOM_CODE => NULL ,
189 X_WQ_ACTUAL_ENTRY_CODE => NULL ,
190 X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL ,
191 x_parent_structure_id => NULL ,
192 x_phase_code => NULL ,
193 x_phase_version_id => NULL,
194 X_SOURCE_OBJECT_ID => c_project_id,
195 X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
196 );
197
198 IF(p_debug_mode = 'Y') THEN
199 pa_debug.debug('CREATE_LIFECYCLE PVT:Obtaining proj_element_version_id...');
200 END IF;
201
202
203 SELECT PA_PROJ_ELEMENT_VERSIONS_S.NEXTVAL
204 INTO l_pev_id
205 FROM dual;
206
207 IF(p_debug_mode = 'Y') THEN
208 pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into pa_proj_element_versions...');
209 END IF;
210
211 PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
212 X_ROW_ID => l_row_id ,
213 X_ELEMENT_VERSION_ID => l_pev_id ,
214 X_PROJ_ELEMENT_ID => l_lifecycle_id ,
215 X_OBJECT_TYPE => c_object_type ,
216 X_PROJECT_ID => c_project_id ,
217 X_PARENT_STRUCTURE_VERSION_ID => NULL ,
218 X_DISPLAY_SEQUENCE => NULL ,
219 X_WBS_LEVEL => NULL ,
220 X_WBS_NUMBER => NULL ,
221 X_ATTRIBUTE_CATEGORY => NULL ,
222 X_ATTRIBUTE1 => NULL ,
223 X_ATTRIBUTE2 => NULL ,
224 X_ATTRIBUTE3 => NULL ,
225 X_ATTRIBUTE4 => NULL ,
226 X_ATTRIBUTE5 => NULL ,
227 X_ATTRIBUTE6 => NULL ,
228 X_ATTRIBUTE7 => NULL ,
229 X_ATTRIBUTE8 => NULL ,
230 X_ATTRIBUTE9 => NULL ,
231 X_ATTRIBUTE10 => NULL ,
232 X_ATTRIBUTE11 => NULL ,
233 X_ATTRIBUTE12 => NULL ,
234 X_ATTRIBUTE13 => NULL ,
235 X_ATTRIBUTE14 => NULL ,
236 X_ATTRIBUTE15 => NULL ,
237 X_TASK_UNPUB_VER_STATUS_CODE => 'Working',
238 X_SOURCE_OBJECT_ID => c_project_id ,
239 X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
240 );
241
242 IF(p_debug_mode = 'Y') THEN
243 pa_debug.debug('CREATE_LIFECYCLE PVT:Obtaining proj_element_version_structure_id...');
244 END IF;
245
246 SELECT PA_PROJ_ELEM_VER_STRUCTURE_S.NEXTVAL
247 INTO l_pev_struct_id
248 FROM dual;
249
250 IF(p_debug_mode = 'Y') THEN
251 pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into pa_proj_elem_version_structure...');
252 END IF;
253
254
255 PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
256 X_ROWID => l_row_id ,
257 X_PEV_STRUCTURE_ID => l_pev_struct_id ,
258 X_ELEMENT_VERSION_ID => l_pev_id ,
259 X_VERSION_NUMBER => 1 ,
260 X_NAME => P_lifecycle_name ,
261 X_PROJECT_ID => c_project_id ,
262 X_PROJ_ELEMENT_ID => l_lifecycle_id ,
263 X_DESCRIPTION => P_lifecycle_description ,
264 X_EFFECTIVE_DATE => NULL ,
265 X_PUBLISHED_DATE => NULL ,
266 X_PUBLISHED_BY => NULL ,
267 X_CURRENT_BASELINE_DATE => NULL ,
268 X_CURRENT_BASELINE_FLAG => 'Y' ,
269 X_CURRENT_BASELINE_BY => NULL ,
270 X_ORIGINAL_BASELINE_DATE => NULL ,
271 X_ORIGINAL_BASELINE_FLAG => 'Y' ,
272 X_ORIGINAL_BASELINE_BY => NULL ,
273 X_LOCK_STATUS_CODE => NULL ,
274 X_LOCKED_BY => NULL ,
275 X_LOCKED_DATE => NULL ,
276 X_STATUS_CODE => NULL ,
277 X_WF_STATUS_CODE => NULL ,
278 X_LATEST_EFF_PUBLISHED_FLAG => 'Y' ,
279 X_CHANGE_REASON_CODE => NULL ,
280 X_RECORD_VERSION_NUMBER => NULL ,
281 X_SOURCE_OBJECT_ID => c_project_id,
282 X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
283 );
284
285 IF(p_debug_mode = 'Y') THEN
286 pa_debug.debug('CREATE_LIFECYCLE PVT:Obtaining proj_structure_type_id...');
287 END IF;
288
289 SELECT PA_PROJ_STRUCTURE_TYPES_S.NEXTVAL
290 INTO l_proj_struct_type_id
291 FROM dual;
292
293 OPEN cur_struc_type_id;
294 FETCH cur_struc_type_id INTO l_structure_type_id;
295 CLOSE cur_struc_type_id;
296
297 IF(p_debug_mode = 'Y') THEN
298 pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into pa_proj_structure_types...');
299 END IF;
300
301
302 PA_PROJ_STRUCTURE_TYPES_PKG.insert_row(
303 X_ROWID => l_row_id ,
304 X_PROJ_STRUCTURE_TYPE_ID => l_proj_struct_type_id ,
305 X_PROJ_ELEMENT_ID => l_lifecycle_id ,
306 X_STRUCTURE_TYPE_ID => l_structure_type_id ,
307 X_RECORD_VERSION_NUMBER => NULL ,
308 X_ATTRIBUTE_CATEGORY => NULL ,
309 X_ATTRIBUTE1 => NULL ,
310 X_ATTRIBUTE2 => NULL ,
311 X_ATTRIBUTE3 => NULL ,
312 X_ATTRIBUTE4 => NULL ,
313 X_ATTRIBUTE5 => NULL ,
314 X_ATTRIBUTE6 => NULL ,
315 X_ATTRIBUTE7 => NULL ,
316 X_ATTRIBUTE8 => NULL ,
317 X_ATTRIBUTE9 => NULL ,
318 X_ATTRIBUTE10 => NULL ,
319 X_ATTRIBUTE11 => NULL ,
320 X_ATTRIBUTE12 => NULL ,
321 X_ATTRIBUTE13 => NULL ,
322 X_ATTRIBUTE14 => NULL ,
323 X_ATTRIBUTE15 => NULL
324 );
325
326 IF(p_debug_mode = 'Y') THEN
327 pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into PA_LIFECYCLE_USAGES..');
328 END IF;
329
330 IF (p_lifecycle_project_usage_type = 'Y') THEN
331 SELECT PA_LIFECYCLE_USAGES_S.NEXTVAL
332 INTO l_lcyl_usage_id
333 FROM dual;
334
335 PA_LIFECYCLE_USAGES_PKG.INSERT_ROW(
336 X_LIFECYCLE_USAGE_ID => l_lcyl_usage_id ,
337 X_RECORD_VERSION_NUMBER => 1 ,
338 X_LIFECYCLE_ID => l_lifecycle_id ,
339 X_USAGE_TYPE => 'PROJECTS'
340 );
341 END IF;
342
343 IF (p_lifecycle_product_usage_type = 'Y') THEN
344 SELECT PA_LIFECYCLE_USAGES_S.NEXTVAL
345 INTO l_lcyl_usage_id
346 FROM dual;
347
348 PA_LIFECYCLE_USAGES_PKG.INSERT_ROW(
349 X_LIFECYCLE_USAGE_ID => l_lcyl_usage_id ,
350 X_RECORD_VERSION_NUMBER => 1 ,
351 X_LIFECYCLE_ID => l_lifecycle_id ,
352 X_USAGE_TYPE => 'PRODUCTS'
353 );
354 END IF;
355
356 IF(p_debug_mode = 'Y') THEN
357 pa_debug.debug('CREATE_LIFECYCLE PVT:Inserting into PA_PROJ_ELEM_VER_SCHEDULE..');
358 END IF;
359
360 SELECT PA_PROJ_ELEM_VER_SCHEDULE_S.NEXTVAL
361 INTO l_pev_sched_id
362 FROM dual;
363
364
365 PA_PROJ_ELEMENT_SCH_PKG.Insert_Row (
366 X_ROW_ID => l_row_id ,
367 X_PEV_SCHEDULE_ID => l_pev_sched_id ,
368 X_ELEMENT_VERSION_ID => l_pev_id ,
369 X_PROJECT_ID => c_project_id ,
370 X_PROJ_ELEMENT_ID => l_lifecycle_id ,
371 X_SCHEDULED_START_DATE => sysdate ,
372 X_SCHEDULED_FINISH_DATE => sysdate ,
373 X_OBLIGATION_START_DATE => NULL ,
374 X_OBLIGATION_FINISH_DATE => NULL ,
375 X_ACTUAL_START_DATE => NULL ,
376 X_ACTUAL_FINISH_DATE => NULL ,
377 X_ESTIMATED_START_DATE => NULL ,
378 X_ESTIMATED_FINISH_DATE => NULL ,
379 X_DURATION => NULL ,
380 X_EARLY_START_DATE => NULL ,
381 X_EARLY_FINISH_DATE => NULL ,
382 X_LATE_START_DATE => NULL ,
383 X_LATE_FINISH_DATE => NULL ,
384 X_CALENDAR_ID => NULL ,
385 X_MILESTONE_FLAG => NULL ,
386 X_CRITICAL_FLAG => NULL ,
387 X_WQ_PLANNED_QUANTITY => NULL ,
388 X_PLANNED_EFFORT => NULL ,
389 X_ACTUAL_DURATION => NULL ,
390 X_ESTIMATED_DURATION => NULL,
391 X_SOURCE_OBJECT_ID => c_project_id,
392 X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
393 );
394
395 -- No need to populate pa_object relationship at thi stage, it will be populated at the time of phase creation
396
397 IF (p_debug_mode = 'Y') THEN
398 pa_debug.debug('PA_LIFECYCLES_PVT.create_lifecycle : checking message count');
399 END IF;
400
401 l_msg_count := FND_MSG_PUB.count_msg;
402
403 If l_msg_count > 0 THEN
404 x_msg_count := l_msg_count;
405 If l_msg_count = 1 THEN
406 pa_interface_utils_pub.get_messages(
407 p_encoded => FND_API.G_TRUE ,
408 p_msg_index => 1 ,
409 p_msg_count => l_msg_count ,
410 p_msg_data => l_msg_data ,
411 p_data => l_data ,
412 p_msg_index_out => l_msg_index_out
413 );
414 x_msg_data := l_data;
415 End if;
416 RAISE FND_API.G_EXC_ERROR;
417 End if;
418
419
420 x_lifecycle_id := l_lifecycle_id;
421 x_return_status := FND_API.G_RET_STS_SUCCESS;
422
423 IF FND_API.TO_BOOLEAN(P_COMMIT)
424 THEN
425 COMMIT;
426 END IF;
427
428 EXCEPTION
429
430 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
431 IF p_commit = FND_API.G_TRUE THEN
432 ROLLBACK TO LCYL_CREATE_LIFECYCLE_PVT;
433 END IF;
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
436 p_procedure_name => 'create_lifecycle',
437 p_error_text => SUBSTRB(SQLERRM,1,240));
438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439
440 WHEN FND_API.G_EXC_ERROR THEN
441 x_return_status := FND_API.G_RET_STS_ERROR;
442 IF p_commit = FND_API.G_TRUE THEN
443 ROLLBACK TO LCYL_CREATE_LIFECYCLE_PVT;
444 END IF;
445
446 WHEN OTHERS THEN
447 IF p_commit = FND_API.G_TRUE THEN
448 ROLLBACK TO LCYL_CREATE_LIFECYCLE_PVT;
449 END IF;
450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
451 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
452 p_procedure_name => 'create_lifecycle',
453 p_error_text => SUBSTRB(SQLERRM,1,240));
454 raise;
455
456 end create_lifecycle;
457
458 /*-----------------------------------------------------------+
459 | For Details/Comments Refer Package Specification Comments |
460 +-----------------------------------------------------------*/
461
462
463 PROCEDURE create_lifecycle_phase (
464 P_api_version IN NUMBER :=1.0 ,
465 p_commit IN VARCHAR2 :=FND_API.G_FALSE ,
466 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
467 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
468 p_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
469 p_debug_mode IN VARCHAR2 :='N' ,
470 P_max_msg_count IN NUMBER :=G_MISS_NUM ,
471 P_lifecycle_id IN NUMBER ,
472 P_phase_display_sequence IN NUMBER ,
473 P_phase_code IN VARCHAR2 ,
474 P_phase_short_name IN VARCHAR2 ,
475 P_phase_name IN VARCHAR2 ,
476 P_phase_description IN VARCHAR2 ,
477 X_lifecycle_phase_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
478 X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
479 X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
480 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
481 ) IS
482
483 l_api_name CONSTANT VARCHAR(30) := 'create_lifecycle_phase';
484 l_api_version CONSTANT NUMBER := 1.0;
485 l_msg_count NUMBER;
486 l_msg_index_out NUMBER;
487 l_data VARCHAR2(2000);
488 l_msg_data VARCHAR2(2000);
489 l_return_status VARCHAR2(1);
490
491 l_is_uniq VARCHAR2(1);
492 l_row_id VARCHAR2(30);
493 l_phase_id NUMBER;
494 l_pev_id NUMBER;
495 l_pev_struct_id NUMBER;
496 l_proj_struct_type_id NUMBER;
497 l_pev_sched_id NUMBER;
498 l_obj_relnship_id NUMBER;
499 l_life_elem_ver_id NUMBER;
500
501
502 c_object_type CONSTANT VARCHAR(30) := 'PA_TASKS';
503 c_project_id CONSTANT NUMBER := 0;
504
505
506 CURSOR l_check_phase_name_csr
507 IS
508 SELECT 'N'
509 FROM dual
510 WHERE exists(Select 'XYZ'
511 FROM pa_proj_elements pelem
512 , pa_proj_element_versions phasever
513 WHERE phasever.PARENT_STRUCTURE_VERSION_ID = l_life_elem_ver_id
514 AND phasever.PROJECT_ID = c_project_id
515 AND phasever.OBJECT_TYPE = c_object_type
516 AND phasever.PROJ_ELEMENT_ID = pelem.PROJ_ELEMENT_ID
517 AND pelem.element_number = p_phase_short_name
518 AND pelem.project_id = c_project_id
519 AND pelem.object_type = c_object_type);
520
521 CURSOR l_get_life_elem_ver_id
522 IS
523 SELECT ELEMENT_VERSION_ID
524 FROM pa_proj_element_versions
525 WHERE PROJ_ELEMENT_ID = p_lifecycle_id;
526
527 BEGIN
528
529 IF(p_debug_mode = 'Y') THEN
530 pa_debug.debug('CREATE_LIFECYCLE_PVT.create_lifecycle_phase: Inside create_lifecycle_phase ...');
531 END IF;
532
533 IF(p_commit = FND_API.G_TRUE) THEN
534 SAVEPOINT LCYL_CREATE_LCYL_PHASES_PVT;
535 END IF;
536
537 IF(p_debug_mode = 'Y') THEN
538 pa_debug.debug('CREATE_LIFECYCLE_PVT.create_lifecycle_phase: Calling api compatilbility check...');
539 END IF;
540
541 IF NOT FND_API.COMPATIBLE_API_CALL(
542 l_api_version ,
543 p_api_version ,
544 l_api_name ,
545 g_pkg_name
546 )
547 THEN
548 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
549 END IF;
550
551
552 x_return_status := FND_API.G_RET_STS_SUCCESS;
553
554 IF(p_debug_mode = 'Y') THEN
555 pa_debug.debug('CREATE_LIFECYCLE_PVT.create_lifecycle_phase: Checking for valid parameters..');
556 END IF;
557
558 OPEN l_get_life_elem_ver_id;
559 FETCH l_get_life_elem_ver_id INTO l_life_elem_ver_id;
560 CLOSE l_get_life_elem_ver_id;
561
562 l_is_uniq :='Y';
563 OPEN l_check_phase_name_csr;
564 FETCH l_check_phase_name_csr INTO l_is_uniq;
565 CLOSE l_check_phase_name_csr;
566
567 -- This check shd be done if validation level full
568 IF(l_is_uniq <> 'Y') THEN
569 PA_UTILS.ADD_MESSAGE(
570 p_app_short_name => 'PA' ,
571 p_msg_name => 'PA_LCYL_DUPLICATE_PHASE_SHNAME'
572 );
573 x_msg_data := 'PA_LCYL_DUPLICATE_PHASE_SHNAME';
574 x_return_status := FND_API.G_RET_STS_ERROR;
575 END IF;
576
577 IF (p_debug_mode = 'Y') THEN
578 pa_debug.debug('PA_LIFECYCLES_PVT.create_lifecycle_phase : checking message count');
579 END IF;
580
581 l_msg_count := FND_MSG_PUB.count_msg;
582
583 IF l_msg_count > 0 THEN
584 x_msg_count := l_msg_count;
585 IF l_msg_count = 1 THEN
586 pa_interface_utils_pub.get_messages
587 (p_encoded => FND_API.G_TRUE ,
588 p_msg_index => 1 ,
589 p_msg_count => l_msg_count ,
590 p_msg_data => l_msg_data ,
591 p_data => l_data ,
592 p_msg_index_out => l_msg_index_out
593 );
594 x_msg_data := l_data;
595 END IF;
596 RAISE FND_API.G_EXC_ERROR;
597 END IF;
598
599 IF(p_debug_mode = 'Y') THEN
600 pa_debug.debug('CREATE_LIFECYCLE_PVT.create_lifecycle_phase: Obtaining lifecycle_phase_id...');
601 END IF;
602
603 SELECT PA_TASKS_S.NEXTVAL
604 INTO l_phase_id
605 FROM dual;
606
607
608 if(p_debug_mode = 'Y') then
609 pa_debug.debug('CREATE_LIFECYCLE_PVT.create_lifecycle_phase:Inserting into pa_proj_elements...');
610 end if;
611
612 PA_PROJ_ELEMENTS_PKG.Insert_Row(
613 X_ROW_ID => l_row_id ,
614 x_proj_element_id => l_phase_id ,
615 x_project_id => c_project_id ,
616 x_object_type => c_object_type ,
617 x_element_number => p_phase_short_name ,
618 x_name => p_phase_name ,
619 X_DESCRIPTION => P_phase_description ,
620 X_STATUS_CODE => NULL ,
621 X_WF_STATUS_CODE => NULL ,
622 X_PM_PRODUCT_CODE => NULL ,
623 X_PM_TASK_REFERENCE => NULL ,
624 X_CLOSED_DATE => NULL ,
625 X_LOCATION_ID => NULL ,
626 X_MANAGER_PERSON_ID => NULL ,
627 X_CARRYING_OUT_ORGANIZATION_ID => NULL ,
628 X_TYPE_ID => NULL ,
629 X_PRIORITY_CODE => NULL ,
630 X_INC_PROJ_PROGRESS_FLAG => NULL ,
631 X_REQUEST_ID => NULL ,
632 X_PROGRAM_APPLICATION_ID => NULL ,
633 X_PROGRAM_ID => NULL ,
634 X_PROGRAM_UPDATE_DATE => NULL ,
635 X_LINK_TASK_FLAG => NULL ,
636 X_ATTRIBUTE_CATEGORY => NULL ,
637 X_ATTRIBUTE1 => NULL ,
638 X_ATTRIBUTE2 => NULL ,
639 X_ATTRIBUTE3 => NULL ,
640 X_ATTRIBUTE4 => NULL ,
641 X_ATTRIBUTE5 => NULL ,
642 X_ATTRIBUTE6 => NULL ,
643 X_ATTRIBUTE7 => NULL ,
644 X_ATTRIBUTE8 => NULL ,
645 X_ATTRIBUTE9 => NULL ,
646 X_ATTRIBUTE10 => NULL ,
647 X_ATTRIBUTE11 => NULL ,
648 X_ATTRIBUTE12 => NULL ,
649 X_ATTRIBUTE13 => NULL ,
650 X_ATTRIBUTE14 => NULL ,
651 X_ATTRIBUTE15 => NULL ,
652 X_TASK_WEIGHTING_DERIV_CODE => NULL ,
653 X_WORK_ITEM_CODE => NULL ,
654 X_UOM_CODE => NULL ,
655 X_WQ_ACTUAL_ENTRY_CODE => NULL ,
656 X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL ,
657 x_parent_structure_id => P_lifecycle_id ,
658 x_phase_code => p_phase_code ,
659 x_phase_version_id => NULL,
660 X_SOURCE_OBJECT_ID => c_project_id,
661 X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
662 );
663
664
665 IF(p_debug_mode = 'Y') THEN
666 pa_debug.debug('CREATE_LIFECYCLE PVT.create_lifecyle_phase:Obtaining proj_element_version_id...');
667 END IF;
668
669
670 SELECT PA_PROJ_ELEMENT_VERSIONS_S.NEXTVAL
671 INTO l_pev_id
672 FROM dual;
673
674 IF(p_debug_mode = 'Y') THEN
675 pa_debug.debug('CREATE_LIFECYCLE PVT.create_lifecyle_phas:Inserting into pa_proj_element_versions...');
676 END IF;
677
678 PA_PROJ_ELEMENT_VERSIONS_PKG.Insert_Row(
679 X_ROW_ID => l_row_id ,
680 X_ELEMENT_VERSION_ID => l_pev_id ,
681 X_PROJ_ELEMENT_ID => l_phase_id ,
682 X_OBJECT_TYPE => c_object_type ,
683 X_PROJECT_ID => c_project_id ,
684 X_PARENT_STRUCTURE_VERSION_ID => l_life_elem_ver_id ,
685 X_DISPLAY_SEQUENCE => p_phase_display_sequence ,
686 X_WBS_LEVEL => NULL ,
687 X_WBS_NUMBER => NULL ,
688 X_ATTRIBUTE_CATEGORY => NULL ,
689 X_ATTRIBUTE1 => NULL ,
690 X_ATTRIBUTE2 => NULL ,
691 X_ATTRIBUTE3 => NULL ,
692 X_ATTRIBUTE4 => NULL ,
693 X_ATTRIBUTE5 => NULL ,
694 X_ATTRIBUTE6 => NULL ,
695 X_ATTRIBUTE7 => NULL ,
696 X_ATTRIBUTE8 => NULL ,
697 X_ATTRIBUTE9 => NULL ,
698 X_ATTRIBUTE10 => NULL ,
699 X_ATTRIBUTE11 => NULL ,
700 X_ATTRIBUTE12 => NULL ,
701 X_ATTRIBUTE13 => NULL ,
702 X_ATTRIBUTE14 => NULL ,
703 X_ATTRIBUTE15 => NULL ,
704 X_TASK_UNPUB_VER_STATUS_CODE => 'Working',
705 X_SOURCE_OBJECT_ID => c_project_id,
706 X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
707 );
708
709 IF(p_debug_mode = 'Y') THEN
710 pa_debug.debug('CREATE_LIFECYCLE PVT.create_lifecyle_phase:Obtaining pev_schedule_id...');
711 END IF;
712
713 SELECT PA_PROJ_ELEM_VER_SCHEDULE_S.NEXTVAL
714 INTO l_pev_sched_id
715 FROM dual;
716
717 IF(p_debug_mode = 'Y') THEN
718 pa_debug.debug('CREATE_LIFECYCLE PVT.create_lifecyle_phas:Inserting into PA_PROJ_ELEMENT_VER_SCHEDULE...');
719 END IF;
720
721
722 PA_PROJ_ELEMENT_SCH_PKG.Insert_Row (
723 X_ROW_ID => l_row_id ,
724 X_PEV_SCHEDULE_ID => l_pev_sched_id ,
725 X_ELEMENT_VERSION_ID => l_pev_id ,
726 X_PROJECT_ID => c_project_id ,
727 X_PROJ_ELEMENT_ID => l_phase_id ,
728 X_SCHEDULED_START_DATE => sysdate ,
729 X_SCHEDULED_FINISH_DATE => sysdate ,
730 X_OBLIGATION_START_DATE => NULL ,
731 X_OBLIGATION_FINISH_DATE => NULL ,
732 X_ACTUAL_START_DATE => NULL ,
733 X_ACTUAL_FINISH_DATE => NULL ,
734 X_ESTIMATED_START_DATE => NULL ,
735 X_ESTIMATED_FINISH_DATE => NULL ,
736 X_DURATION => NULL ,
737 X_EARLY_START_DATE => NULL ,
738 X_EARLY_FINISH_DATE => NULL ,
739 X_LATE_START_DATE => NULL ,
740 X_LATE_FINISH_DATE => NULL ,
741 X_CALENDAR_ID => NULL ,
742 X_MILESTONE_FLAG => NULL ,
743 X_CRITICAL_FLAG => NULL ,
744 X_WQ_PLANNED_QUANTITY => NULL ,
745 X_PLANNED_EFFORT => NULL ,
746 X_ACTUAL_DURATION => NULL ,
747 X_ESTIMATED_DURATION => NULL,
748 X_SOURCE_OBJECT_ID => c_project_id,
749 X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
750 );
751
752
753 IF(p_debug_mode = 'Y') THEN
754 pa_debug.debug('CREATE_LIFECYCLE PVT.create_lifecyle_phase:Obtaining OBJECT_RELATIONSHIP_ID...');
755 END IF;
756
757 SELECT pa_object_relationships_s.NEXTVAL
758 INTO l_obj_relnship_id
759 FROM dual;
760
761 IF(p_debug_mode = 'Y') THEN
762 pa_debug.debug('CREATE_LIFECYCLE PVT.create_lifecyle_phas:Inserting into PA_OBJECT_RELATIONSHIPS...');
763 END IF;
764
765 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
766 p_user_id => FND_GLOBAL.USER_ID ,
767 p_object_type_from => 'PA_STRUCTURES' ,
768 p_object_id_from1 => l_life_elem_ver_id ,
769 p_object_id_from2 => NULL ,
770 p_object_id_from3 => NULL ,
771 p_object_id_from4 => NULL ,
772 p_object_id_from5 => NULL ,
773 p_object_type_to => 'PA_TASKS' ,
774 p_object_id_to1 => l_pev_id ,
775 p_object_id_to2 => NULL ,
776 p_object_id_to3 => NULL ,
777 p_object_id_to4 => NULL ,
778 p_object_id_to5 => NULL ,
779 p_relationship_type => 'S' ,
780 p_relationship_subtype => 'STRUCTURE_TO_TASK' ,
781 p_lag_day => NULL ,
782 p_imported_lag => NULL ,
783 p_priority => NULL ,
784 p_pm_product_code => NULL ,
785 x_object_relationship_id => l_obj_relnship_id ,
786 x_return_status => l_return_status
787 );
788
789 IF (p_debug_mode = 'Y') THEN
790 pa_debug.debug('PA_LIFECYCLES_PVT.create_lifecycle_phase : checking message count');
791 END IF;
792
793 l_msg_count := FND_MSG_PUB.count_msg;
794
795 If l_msg_count > 0 THEN
796 x_msg_count := l_msg_count;
797 If l_msg_count = 1 THEN
798 pa_interface_utils_pub.get_messages
799 (p_encoded => FND_API.G_TRUE ,
800 p_msg_index => 1 ,
801 p_msg_count => l_msg_count ,
802 p_msg_data => l_msg_data ,
803 p_data => l_data ,
804 p_msg_index_out => l_msg_index_out
805 );
806 x_msg_data := l_data;
807 End if;
808 RAISE FND_API.G_EXC_ERROR;
809 End if;
810
811 x_lifecycle_phase_id := l_phase_id;
812 x_return_status := FND_API.G_RET_STS_SUCCESS;
813
814 IF FND_API.TO_BOOLEAN(P_COMMIT)
815 THEN
816 COMMIT;
817 END IF;
818
819 EXCEPTION
820
821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
822 IF p_commit = FND_API.G_TRUE THEN
823 ROLLBACK TO LCYL_CREATE_LCYL_PHASES_PVT;
824 END IF;
825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
826 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
827 p_procedure_name => 'CREATE_LIFECYCLE_PHASE',
828 p_error_text => SUBSTRB(SQLERRM,1,240));
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
830
831 WHEN FND_API.G_EXC_ERROR THEN
832 x_return_status := FND_API.G_RET_STS_ERROR;
833 IF p_commit = FND_API.G_TRUE THEN
834 ROLLBACK TO LCYL_CREATE_LCYL_PHASES_PVT;
835 END IF;
836
837 WHEN OTHERS THEN
838 IF p_commit = FND_API.G_TRUE THEN
839 ROLLBACK TO LCYL_CREATE_LCYL_PHASES_PVT;
840 END IF;
841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
842 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
843 p_procedure_name => 'CREATE_LIFECYCLE_PHASE',
844 p_error_text => SUBSTRB(SQLERRM,1,240));
845 raise;
846
847 END create_lifecycle_phase;
848
849 /*-----------------------------------------------------------+
850 | For Details/Comments Refer Package Specification Comments |
851 +-----------------------------------------------------------*/
852
853 PROCEDURE delete_lifecycle(
854 P_api_version IN NUMBER :=1.0 ,
855 P_commit IN VARCHAR2 :=FND_API.G_FALSE ,
856 P_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
857 P_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
858 P_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
859 P_debug_mode IN VARCHAR2 :='N' ,
860 P_max_msg_count IN NUMBER :=G_MISS_NUM ,
861 P_lifecycle_id IN NUMBER ,
862 P_record_version_number IN NUMBER ,
863 X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
864 X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
865 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
866 ) IS
867
868 l_api_name CONSTANT VARCHAR(30) := 'delete_lifecycle';
869 l_api_version CONSTANT NUMBER := 1.0;
870 l_msg_count NUMBER;
871 l_msg_index_out NUMBER;
872 l_data VARCHAR2(2000);
873 l_msg_data VARCHAR2(2000);
874 l_return_status VARCHAR2(1);
875
876
877 l_rowid VARCHAR2(30);
878 l_element_version_id NUMBER;
879 l_record_version_number NUMBER;
880 l_lifecycle_usage_id NUMBER;
881
882
883 c_object_type CONSTANT VARCHAR(30) := 'PA_STRUCTURES';
884 c_project_id CONSTANT NUMBER := 0;
885
886
887 CURSOR l_row_proj_element_versions
888 IS
889 SELECT rowid,element_version_id
890 FROM pa_proj_element_versions
891 WHERE proj_element_id = P_lifecycle_id
892 AND project_id = c_project_id
893 AND object_type = c_object_type;
894
895
896 CURSOR l_row_proj_elem_ver_schedule
897 IS
898 SELECT rowid
899 FROM pa_proj_elem_ver_schedule
900 WHERE proj_element_id = P_lifecycle_id
901 AND element_version_id = l_element_version_id
902 AND project_id = c_project_id;
903
904
905 CURSOR l_row_proj_workplan_attrs
906 IS
907 SELECT rowid,record_version_number
908 FROM pa_proj_workplan_attr
909 WHERE proj_element_id = P_lifecycle_id
910 AND project_id = c_project_id;
911
912
913 CURSOR l_row_proj_elem_ver_structure
914 IS
915 SELECT rowid
916 FROM pa_proj_elem_ver_structure
917 WHERE element_version_id = l_element_version_id
918 AND proj_element_id = P_lifecycle_id
919 AND project_id = c_project_id;
920
921
922 CURSOR l_row_proj_structure_types
923 IS
924 SELECT rowid
925 FROM pa_proj_structure_types
926 WHERE proj_element_id = P_lifecycle_id;
927
928
929 cursor l_row_lifecycle_usages
930 IS
931 SELECT LIFECYCLE_USAGE_ID
932 FROM pa_lifecycle_usages
933 WHERE lifecycle_id = P_lifecycle_id;
934
935
936 Begin
937
938 IF(p_debug_mode = 'Y') THEN
939 pa_debug.debug('CREATE_LIFECYCLE_PVT: inside delete_lifecycle.....');
940 END IF;
941
942 IF(p_commit = FND_API.G_TRUE) THEN
943 SAVEPOINT LCYL_DEL_PVT;
944 END IF;
945
946 IF(p_debug_mode = 'Y') THEN
947 pa_debug.debug('CREATE_LIFECYCLE_PVT.delete_lifecycle_phase: Checking for call compatability...');
948 END IF;
949
950 IF NOT FND_API.COMPATIBLE_API_CALL(
951 l_api_version ,
952 p_api_version ,
953 l_api_name ,
954 g_pkg_name
955 )
956 THEN
957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
958 END IF;
959
960 x_return_status := FND_API.G_RET_STS_SUCCESS;
961
962 IF(p_debug_mode = 'Y') THEN
963 pa_debug.debug('CREATE_LIFECYCLE_PVT.delete_lifecycle_phase: Locking on pa_proj_elements...');
964 end if;
965
966
967 --Lock record
968 IF (p_validate_only <> FND_API.G_TRUE) THEN
969 BEGIN
970 -- BEGIN lock
971
972 SELECT rowid into l_rowid
973 FROM pa_proj_elements
974 WHERE proj_element_id = P_lifecycle_id
975 AND record_version_number = p_record_version_number
976 AND project_id = c_project_id
977 AND object_type = c_object_type
978
979 FOR update of record_version_number NOWAIT;
980
981 EXCEPTION
982
983 when TIMEOUT_ON_RESOURCE then
984 x_return_status := FND_API.G_RET_STS_ERROR;
985 PA_UTILS.ADD_MESSAGE(
986 p_app_short_name => 'PA',
987 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED'
988 );
989 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
990
991 when NO_DATA_FOUND then
992 x_return_status := FND_API.G_RET_STS_ERROR;
993 PA_UTILS.ADD_MESSAGE(
994 p_app_short_name => 'PA',
995 p_msg_name => 'PA_XC_RECORD_CHANGED'
996 );
997 l_msg_data := 'PA_XC_RECORD_CHANGED';
998
999 when OTHERS then
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 IF SQLCODE = -54 THEN
1002 PA_UTILS.ADD_MESSAGE(
1003 p_app_short_name => 'PA',
1004 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED'
1005 );
1006 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1007 ELSE
1008 raise;
1009 END IF;
1010 --END LOCK
1011 END;
1012
1013 ELSE
1014 -- IF p_validate_only IS true
1015 BEGIN
1016
1017 SELECT rowid into l_rowid
1018 FROM pa_proj_elements
1019 WHERE proj_element_id = P_lifecycle_id
1020 AND project_id = c_project_id
1021 AND object_type = c_object_type
1022 AND record_version_number = p_record_version_number;
1023
1024 EXCEPTION
1025
1026 when NO_DATA_FOUND then
1027 x_return_status := FND_API.G_RET_STS_ERROR;
1028 PA_UTILS.ADD_MESSAGE(
1029 p_app_short_name => 'PA',
1030 p_msg_name => 'PA_XC_RECORD_CHANGED'
1031 );
1032 l_msg_data := 'PA_XC_RECORD_CHANGED';
1033
1034 when OTHERS then
1035 raise;
1036 END;
1037 END IF;
1038
1039 IF (p_debug_mode = 'Y') THEN
1040 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle: checking message count');
1041 END IF;
1042
1043 l_msg_count := FND_MSG_PUB.count_msg;
1044
1045 IF l_msg_count > 0 THEN
1046 x_msg_count := l_msg_count;
1047 IF l_msg_count = 1 THEN
1048 pa_interface_utils_pub.get_messages
1049 (p_encoded => FND_API.G_TRUE ,
1050 p_msg_index => 1 ,
1051 p_msg_count => l_msg_count ,
1052 p_msg_data => l_msg_data ,
1053 p_data => l_data ,
1054 p_msg_index_out => l_msg_index_out
1055 );
1056 x_msg_data := l_data;
1057 End if;
1058 RAISE FND_API.G_EXC_ERROR;
1059 End if;
1060
1061 IF (p_debug_mode = 'Y') THEN
1062 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_PROJ_ELEMENTS_PKG.delete_Row');
1063 END IF;
1064
1065 PA_PROJ_ELEMENTS_PKG.delete_Row(l_rowid);
1066
1067
1068 IF (p_debug_mode = 'Y') THEN
1069 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_PROJ_ELEMENT_VERSIONS_PKG.delete_Row');
1070 END IF;
1071
1072 OPEN l_row_proj_element_versions;
1073 FETCH l_row_proj_element_versions into l_rowid,l_element_version_id;
1074 CLOSE l_row_proj_element_versions;
1075
1076 PA_PROJ_ELEMENT_VERSIONS_PKG.delete_Row(l_rowid);
1077
1078 IF (p_debug_mode = 'Y') THEN
1079 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_PROJ_ELEMENT_SCH_PKG.delete_Row');
1080 END IF;
1081
1082 OPEN l_row_proj_elem_ver_schedule;
1083 FETCH l_row_proj_elem_ver_schedule into l_rowid;
1084 CLOSE l_row_proj_elem_ver_schedule;
1085
1086 PA_PROJ_ELEMENT_SCH_PKG.delete_Row(l_rowid);
1087
1088
1089
1090 IF (p_debug_mode = 'Y') THEN
1091 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_PROJ_ELEM_VER_STRUCTURE_PKG.delete_Row');
1092 END IF;
1093
1094 OPEN l_row_proj_elem_ver_structure;
1095 FETCH l_row_proj_elem_ver_structure into l_rowid;
1096 CLOSE l_row_proj_elem_ver_structure;
1097
1098 PA_PROJ_ELEM_VER_STRUCTURE_PKG.delete_Row(l_rowid);
1099
1100 IF (p_debug_mode = 'Y') THEN
1101 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_PROJ_STRUCTURE_TYPES_PKG.delete_Row');
1102 END IF;
1103
1104 OPEN l_row_proj_structure_types;
1105 FETCH l_row_proj_structure_types into l_rowid;
1106 CLOSE l_row_proj_structure_types;
1107
1108 PA_PROJ_STRUCTURE_TYPES_PKG.delete_Row(l_rowid);
1109
1110 IF (p_debug_mode = 'Y') THEN
1111 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle:Calling PA_LIFECYCLE_USAGES_PKG.delete_Row');
1112 END IF;
1113
1114 OPEN l_row_lifecycle_usages;
1115 LOOP
1116 FETCH l_row_lifecycle_usages into l_LIFECYCLE_USAGE_ID;
1117 EXIT WHEN l_row_lifecycle_usages%NOTFOUND;
1118 PA_LIFECYCLE_USAGES_PKG.Delete_Row(l_LIFECYCLE_USAGE_ID);
1119 END LOOP;
1120 CLOSE l_row_lifecycle_usages;
1121
1122
1123 -- No need to delete object relationship here. It will be deleted in phase deletion
1124
1125
1126 IF (p_debug_mode = 'Y') THEN
1127 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle: checking message count');
1128 END IF;
1129
1130 l_msg_count := FND_MSG_PUB.count_msg;
1131
1132 If l_msg_count > 0 THEN
1133 x_msg_count := l_msg_count;
1134 If l_msg_count = 1 THEN
1135 pa_interface_utils_pub.get_messages(
1136 p_encoded => FND_API.G_TRUE ,
1137 p_msg_index => 1 ,
1138 p_msg_count => l_msg_count ,
1139 p_msg_data => l_msg_data ,
1140 p_data => l_data ,
1141 p_msg_index_out => l_msg_index_out
1142 );
1143 x_msg_data := l_data;
1144 End if;
1145 RAISE FND_API.G_EXC_ERROR;
1146 End if;
1147
1148 x_return_status := FND_API.G_RET_STS_SUCCESS;
1149
1150
1151 EXCEPTION
1152
1153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1154 IF p_commit = FND_API.G_TRUE THEN
1155 ROLLBACK TO LCYL_DEL_PVT;
1156 END IF;
1157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1158 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
1159 p_procedure_name => 'DELETE_LIFECYCLE',
1160 p_error_text => SUBSTRB(SQLERRM,1,240));
1161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1162
1163 WHEN FND_API.G_EXC_ERROR THEN
1164 x_return_status := FND_API.G_RET_STS_ERROR;
1165 IF p_commit = FND_API.G_TRUE THEN
1166 ROLLBACK TO LCYL_DEL_PVT;
1167 END IF;
1168
1169
1170 WHEN OTHERS THEN
1171 IF p_commit = FND_API.G_TRUE THEN
1172 ROLLBACK TO LCYL_DEL_PVT;
1173 END IF;
1174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1175 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
1176 p_procedure_name => 'DELETE_LIFECYCLE',
1177 p_error_text => SUBSTRB(SQLERRM,1,240));
1178 raise;
1179
1180 END delete_lifecycle;
1181
1182 /*-----------------------------------------------------------+
1183 | For Details/Comments Refer Package Specification Comments |
1184 +-----------------------------------------------------------*/
1185
1186
1187 PROCEDURE delete_lifecycle_phase (
1188 P_api_version IN NUMBER := 1.0 ,
1189 P_commit IN VARCHAR2 := FND_API.G_FALSE ,
1190 P_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
1191 P_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
1192 P_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
1193 P_debug_mode IN VARCHAR2 := 'N' ,
1194 P_max_msg_count IN NUMBER := G_MISS_NUM ,
1195 P_phase_id IN NUMBER ,
1196 p_record_version_number IN NUMBER ,
1197 X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1198 X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1199 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1200 ) IS
1201 l_api_name CONSTANT VARCHAR(30) := 'delete_lifecycle_phase';
1202 l_api_version CONSTANT NUMBER := 1.0;
1203 l_msg_count NUMBER;
1204 l_msg_index_out NUMBER;
1205 l_data VARCHAR2(2000);
1206 l_msg_data VARCHAR2(2000);
1207 l_return_status VARCHAR2(1);
1208
1209
1210 l_rowid VARCHAR2(30);
1211 l_element_version_id NUMBER;
1212 l_par_element_version_id NUMBER;
1213 l_record_version_number NUMBER;
1214 l_obj_rel_id NUMBER;
1215
1216
1217 c_object_type CONSTANT VARCHAR(30) := 'PA_TASKS';
1218 c_project_id CONSTANT NUMBER := 0;
1219
1220
1221 CURSOR l_row_proj_element_versions
1222 IS
1223 Select rowid,element_version_id,parent_structure_version_id
1224 From pa_proj_element_versions
1225 Where proj_element_id = P_phase_id
1226 AND project_id = c_project_id
1227 AND object_type = c_object_type;
1228
1229 CURSOR l_row_proj_elem_ver_schedule
1230 IS
1231 Select rowid
1232 From pa_proj_elem_ver_schedule
1233 Where proj_element_id = P_phase_id
1234 AND element_version_id = l_element_version_id
1235 AND project_id = c_project_id;
1236
1237 CURSOR l_row_object_relationship
1238 IS
1239 Select object_relationship_id, record_version_number
1240 From pa_object_relationships
1241 Where object_type_from = 'PA_STRUCTURES'
1242 and object_id_from1 = l_par_element_version_id
1243 and object_type_to = c_object_type
1244 and object_id_to1 = l_element_version_id
1245 and relationship_subtype = 'STRUCTURE_TO_TASK';
1246
1247 BEGIN
1248
1249 IF(p_debug_mode = 'Y') THEN
1250 pa_debug.debug('CREATE_LIFECYCLE_PVT.delete_lifecycle_phases:inside delete_lifecycle_phase..');
1251 END IF;
1252
1253
1254 IF(p_commit = FND_API.G_TRUE) THEN
1255 SAVEPOINT LCYL_DEL_PHASE_PVT;
1256 END IF;
1257
1258 IF NOT FND_API.COMPATIBLE_API_CALL(
1259 l_api_version ,
1260 p_api_version ,
1261 l_api_name ,
1262 g_pkg_name
1263 )
1264 THEN
1265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1266 END IF;
1267
1268 x_return_status := FND_API.G_RET_STS_SUCCESS;
1269
1270
1271 if(p_debug_mode = 'Y') then
1272 pa_debug.debug('PA_LIFECYCLES_PVT.delete_lifecycle_phase:Locking record for pa_proj_elements...');
1273 end if;
1274
1275 --Lock record
1276 IF (p_validate_only <> FND_API.G_TRUE) THEN
1277 BEGIN
1278 --lock
1279 SELECT rowid into l_rowid
1280 FROM pa_proj_elements
1281 WHERE proj_element_id = p_phase_id
1282 AND project_id = c_project_id
1283 AND object_type = c_object_type
1284 AND record_version_number = p_record_version_number
1285 FOR update of record_version_number NOWAIT;
1286
1287 EXCEPTION
1288
1289 when TIMEOUT_ON_RESOURCE then
1290 x_return_status := FND_API.G_RET_STS_ERROR;
1291 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1292 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1293 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1294
1295 when NO_DATA_FOUND then
1296 x_return_status := FND_API.G_RET_STS_ERROR;
1297 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1298 p_msg_name => 'PA_XC_RECORD_CHANGED');
1299 l_msg_data := 'PA_XC_RECORD_CHANGED';
1300
1301 when OTHERS then
1302 x_return_status := FND_API.G_RET_STS_ERROR;
1303 IF SQLCODE = -54 THEN
1304 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1305 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1306 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1307 ELSE
1308 raise;
1309 END IF;
1310 END;
1311 ELSE
1312 -- IF p_validate_only EQUALS TRUE
1313 BEGIN
1314 SELECT rowid into l_rowid
1315 FROM pa_proj_elements
1316 WHERE proj_element_id = p_phase_id
1317 AND project_id = c_project_id
1318 AND object_type = c_object_type
1319 AND record_version_number = p_record_version_number;
1320 EXCEPTION
1321 when NO_DATA_FOUND then
1322 x_return_status := FND_API.G_RET_STS_ERROR;
1323 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1324 p_msg_name => 'PA_XC_RECORD_CHANGED');
1325 l_msg_data := 'PA_XC_RECORD_CHANGED';
1326 when OTHERS then
1327 raise;
1328 END;
1329 END IF;
1330 IF (p_debug_mode = 'Y') THEN
1331 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle_phases: checking message count');
1332 END IF;
1333
1334 l_msg_count := FND_MSG_PUB.count_msg;
1335
1336 If l_msg_count > 0 THEN
1337 x_msg_count := l_msg_count;
1338 If l_msg_count = 1 THEN
1339 pa_interface_utils_pub.get_messages(
1340 p_encoded => FND_API.G_TRUE ,
1341 p_msg_index => 1 ,
1342 p_msg_count => l_msg_count ,
1343 p_msg_data => l_msg_data ,
1344 p_data => l_data ,
1345 p_msg_index_out => l_msg_index_out
1346 );
1347 x_msg_data := l_data;
1348 End if;
1349 RAISE FND_API.G_EXC_ERROR;
1350 End if;
1351
1352 IF (p_debug_mode = 'Y') THEN
1353 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle_phases:Calling PA_PROJ_ELEMENTS_PKG.delete_Row');
1354 END IF;
1355
1356
1357 PA_PROJ_ELEMENTS_PKG.delete_Row(l_rowid);
1358
1359 IF (p_debug_mode = 'Y') THEN
1360 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle_phases:Calling PA_PROJ_ELEMENT_VERSIONS_PKG.delete_Row');
1361 END IF;
1362
1363 OPEN l_row_proj_element_versions;
1364 FETCH l_row_proj_element_versions into l_rowid,l_element_version_id, l_par_element_version_id;
1365 CLOSE l_row_proj_element_versions;
1366
1367
1368 PA_PROJ_ELEMENT_VERSIONS_PKG.delete_Row(l_rowid);
1369
1370
1371
1372 IF (p_debug_mode = 'Y') THEN
1373 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle_phases:Calling PA_PROJ_ELEMENT_SCH_PKG.delete_Row');
1374 END IF;
1375
1376 OPEN l_row_proj_elem_ver_schedule;
1377 FETCH l_row_proj_elem_ver_schedule into l_rowid;
1378 CLOSE l_row_proj_elem_ver_schedule;
1379
1380 PA_PROJ_ELEMENT_SCH_PKG.delete_Row(l_rowid);
1381
1382
1383 IF (p_debug_mode = 'Y') THEN
1384 pa_debug.debug('DELETE_LIFECYCLE_PVT.delete_lifecycle_phases:Calling PA_OBJECT_RELATIONSHIPS_PKG.delete_Row');
1385 END IF;
1386
1387 OPEN l_row_object_relationship;
1388 FETCH l_row_object_relationship INTO l_obj_rel_id, l_record_version_number;
1389 CLOSE l_row_object_relationship;
1390
1391 PA_OBJECT_RELATIONSHIPS_PKG.DELETE_ROW(
1392 p_object_relationship_id => l_obj_rel_id ,
1393 p_object_type_from => NULL ,
1394 p_object_id_from1 => NULL ,
1395 p_object_id_from2 => NULL ,
1396 p_object_id_from3 => NULL ,
1397 p_object_id_from4 => NULL ,
1398 p_object_id_from5 => NULL ,
1399 p_object_type_to => NULL ,
1400 p_object_id_to1 => NULL ,
1401 p_object_id_to2 => NULL ,
1402 p_object_id_to3 => NULL ,
1403 p_object_id_to4 => NULL ,
1404 p_object_id_to5 => NULL ,
1405 p_record_version_number => l_record_version_number ,
1406 p_pm_product_code => NULL ,
1407 x_return_status => l_return_status
1408 );
1409
1410 IF (p_debug_mode = 'Y') THEN
1411 pa_debug.debug('CREATE_LIFECYCLE_PVT.delete_lifecycle_phases: checking message count');
1412 END IF;
1413
1414 l_msg_count := FND_MSG_PUB.count_msg;
1415
1416 IF l_msg_count > 0 THEN
1417 x_msg_count := l_msg_count;
1418 IF l_msg_count = 1 THEN
1419 pa_interface_utils_pub.get_messages(
1420 p_encoded => FND_API.G_TRUE ,
1421 p_msg_index => 1 ,
1422 p_msg_count => l_msg_count ,
1423 p_msg_data => l_msg_data ,
1424 p_data => l_data ,
1425 p_msg_index_out => l_msg_index_out
1426 );
1427 x_msg_data := l_data;
1428 End if;
1429 RAISE FND_API.G_EXC_ERROR;
1430 End if;
1431
1432 x_return_status := FND_API.G_RET_STS_SUCCESS;
1433
1434
1435
1436
1437 EXCEPTION
1438 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1439 IF p_commit = FND_API.G_TRUE THEN
1440 ROLLBACK TO LCYL_DEL_PHASE_PVT;
1441 END IF;
1442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1443 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
1444 p_procedure_name => 'delete_lifecycle_phase',
1445 p_error_text => SUBSTRB(SQLERRM,1,240));
1446 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1447
1448 WHEN FND_API.G_EXC_ERROR THEN
1449 x_return_status := FND_API.G_RET_STS_ERROR;
1450 IF p_commit = FND_API.G_TRUE THEN
1451 ROLLBACK TO LCYL_DEL_PHASE_PVT;
1452 END IF;
1453
1454
1455 WHEN OTHERS THEN
1456 IF p_commit = FND_API.G_TRUE THEN
1457 ROLLBACK TO LCYL_DEL_PHASE_PVT;
1458 END IF;
1459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1460 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
1461 p_procedure_name => 'delete_lifecycle_phase',
1462 p_error_text => SUBSTRB(SQLERRM,1,240));
1463 raise;
1464
1465 END delete_lifecycle_phase;
1466
1467 /*-----------------------------------------------------------+
1468 | For Details/Comments Refer Package Specification Comments |
1469 +-----------------------------------------------------------*/
1470
1471 PROCEDURE check_delete_lifecycle_ok(
1472 P_api_version IN NUMBER :=1.0 ,
1473 P_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
1474 P_debug_mode IN VARCHAR2 :='N' ,
1475 P_max_msg_count IN NUMBER :=G_MISS_NUM ,
1476 P_lifecycle_id IN NUMBER ,
1477 P_lifecycle_version_id IN NUMBER ,
1478 X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1479 X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1480 X_msg_data OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1481 x_del_lifecycle_ok OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1482 )
1483
1484 IS
1485
1486 l_api_name CONSTANT VARCHAR(30) := 'check_delete_lifecycle_ok';
1487 l_api_version CONSTANT NUMBER := 1.0;
1488 l_msg_count NUMBER;
1489 l_msg_index_out NUMBER;
1490 l_data VARCHAR2(2000);
1491 l_msg_data VARCHAR2(2000);
1492 l_return_status VARCHAR2(1);
1493
1494
1495 l_is_project VARCHAR2(1);
1496 l_is_product VARCHAR2(1);
1497 l_row_id VARCHAR2(30);
1498 l_del_lifecycle_ok VARCHAR2(1);
1499
1500
1501 c_object_type CONSTANT VARCHAR(30) := 'PA_TASKS';
1502 c_project_id CONSTANT NUMBER := 0;
1503 c_usage_project CONSTANT VARCHAR(30) := 'PROJECTS';
1504 c_usage_product CONSTANT VARCHAR(30) := 'PRODUCTS';
1505
1506 CURSOR l_check_project_csr
1507 IS
1508 Select 'Y'
1509 From dual
1510 Where exists(Select 'XYZ'
1511 From pa_lifecycle_usages
1512 Where lifecycle_id = P_lifecycle_id
1513 and usage_type = c_usage_project);
1514
1515 CURSOR l_check_product_csr
1516 IS
1517 Select 'Y'
1518 From dual
1519 Where exists(Select 'XYZ'
1520 From pa_lifecycle_usages
1521 Where lifecycle_id = P_lifecycle_id
1522 and usage_type = c_usage_product);
1523
1524 CURSOR l_project_workplan_csr
1525 IS
1526 Select 'Y'
1527 From dual
1528 Where exists(Select 'XYZ'
1529 From pa_proj_workplan_attr
1530 Where lifecycle_version_id = P_lifecycle_version_id);
1531 Begin
1532 IF(p_debug_mode = 'Y') THEN
1533 pa_debug.debug('CREATE_LIFECYCLE_PVT.check_delete_lifecycle_ok: Inside check_delete_lifecycle_ok...');
1534 END IF;
1535
1536
1537 IF NOT FND_API.COMPATIBLE_API_CALL(
1538 l_api_version ,
1539 p_api_version ,
1540 l_api_name ,
1541 g_pkg_name
1542 )
1543 THEN
1544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1545 END IF;
1546
1547 x_return_status := FND_API.G_RET_STS_SUCCESS;
1548
1549 IF(p_debug_mode = 'Y') THEN
1550 pa_debug.debug('CREATE_LIFECYCLE_PVT.check_delete_lifecycle_ok: Checking for valid parameters..');
1551 END IF;
1552
1553 l_is_project :='N';
1554 OPEN l_check_project_csr;
1555 FETCH l_check_project_csr INTO l_is_project;
1556 CLOSE l_check_project_csr;
1557
1558 IF(p_debug_mode = 'Y') THEN
1559 pa_debug.debug('CREATE_LIFECYCLE_PVT.check_delete_lifecycle_ok: Checking for assigned to workplan..');
1560 END IF;
1561
1562 IF(l_is_project= 'Y') THEN
1563 l_is_project :='N';
1564 OPEN l_project_workplan_csr;
1565 FETCH l_project_workplan_csr INTO l_is_project;
1566 CLOSE l_project_workplan_csr;
1567 IF(l_is_project ='Y') THEN
1568 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1569 p_msg_name => 'PA_LCYL_WORKPLAN_STRUCT_USED');
1570 x_msg_data := 'PA_LCYL_WORKPLAN_STRUCT_USED';
1571 x_return_status := 'E';
1572 END IF;
1573 END IF;
1574
1575 IF (p_debug_mode = 'Y') THEN
1576 pa_debug.debug('CREATE_LIFECYCLE_PVT.check_delete_lifecycle_ok: checking message count');
1577 END IF;
1578
1579 l_msg_count := FND_MSG_PUB.count_msg;
1580
1581 If l_msg_count > 0 THEN
1582 x_msg_count := l_msg_count;
1583 If l_msg_count = 1 THEN
1584 pa_interface_utils_pub.get_messages(
1585 p_encoded => FND_API.G_TRUE ,
1586 p_msg_index => 1 ,
1587 p_msg_count => l_msg_count ,
1588 p_msg_data => l_msg_data ,
1589 p_data => l_data ,
1590 p_msg_index_out => l_msg_index_out
1591 );
1592 x_msg_data := l_data;
1593 End if;
1594 RAISE FND_API.G_EXC_ERROR;
1595 End if;
1596
1597 x_del_lifecycle_ok:=FND_API.G_TRUE;
1598
1599 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1601 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
1602 p_procedure_name => 'delete_lifecycle_phase_ok',
1603 p_error_text => SUBSTRB(SQLERRM,1,240));
1604
1605
1606 WHEN FND_API.G_EXC_ERROR THEN
1607 x_return_status := FND_API.G_RET_STS_ERROR;
1608
1609 WHEN OTHERS THEN
1610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1611 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
1612 p_procedure_name => 'delete_lifecycle_phase_ok',
1613 p_error_text => SUBSTRB(SQLERRM,1,240));
1614 raise;
1615
1616 END check_delete_lifecycle_ok;
1617
1618 /*-----------------------------------------------------------+
1619 | For Details/Comments Refer Package Specification Comments |
1620 +-----------------------------------------------------------*/
1621
1622 PROCEDURE update_lifecycle (
1623 p_api_version IN NUMBER :=1.0 ,
1624 p_commit IN VARCHAR2 :=FND_API.G_FALSE ,
1625 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
1626 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
1627 p_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
1628 p_debug_mode IN VARCHAR2 :='N' ,
1629 p_max_msg_count IN NUMBER :=G_MISS_NUM ,
1630 P_lifecycle_id IN NUMBER ,
1631 P_lifecycle_short_name IN VARCHAR2 ,
1632 P_lifecycle_name IN VARCHAR2 ,
1633 P_lifecycle_description IN VARCHAR2 ,
1634 P_lifecycle_project_usage_type IN VARCHAR2 ,
1635 P_lifecycle_product_usage_type IN VARCHAR2 ,
1636 P_record_version_number IN NUMBER ,
1637 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1638 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1639 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1640 )
1641
1642 IS
1643
1644 l_api_name CONSTANT VARCHAR2(30) := 'update_lifecycle';
1645 l_api_version CONSTANT NUMBER := 1.0;
1646 l_rowid VARCHAR2(30);
1647 l_msg_count NUMBER;
1648 l_msg_index_out NUMBER;
1649 l_data VARCHAR2(2000);
1650 l_msg_data VARCHAR2(2000);
1651 l_return_status VARCHAR2(1);
1652
1653
1654 l_prj_lcyl_usage_id NUMBER;
1655 l_prd_lcyl_usage_id NUMBER;
1656 l_lifecycle_id NUMBER;
1657
1658 l_proj_usg_exists VARCHAR2(1):='N';
1659 l_prod_usg_exists VARCHAR2(1):='N';
1660 l_workplan_used VARCHAR2(1):='N';
1661 l_item_used VARCHAR2(1):='N';
1662 c_object_type CONSTANT VARCHAR2(30) := 'PA_STRUCTURES';
1663 c_project_type CONSTANT VARCHAR2(30) :='PROJECTS';
1664 c_product_type CONSTANT VARCHAR2(30) :='PRODUCTS';
1665 c_project_id CONSTANT NUMBER :=0;
1666 l_record_version_number NUMBER;
1667 l_element_version_id NUMBER;
1668
1669 l_pev_id NUMBER;
1670 l_elem_vers_id NUMBER;
1671 l_errorcode NUMBER;
1672
1673
1674 CURSOR l_row_proj_element_versions
1675 IS
1676 SELECT element_version_id
1677 FROM pa_proj_element_versions
1678 WHERE proj_element_id = P_lifecycle_id
1679 AND project_id = c_project_id
1680 AND object_type = c_object_type;
1681
1682 CURSOR get_project_usage
1683 IS
1684 SELECT lifecycle_usage_id
1685 from pa_lifecycle_usages
1686 where lifecycle_id = P_lifecycle_id
1687 AND usage_type=c_project_type;
1688
1689 CURSOR get_product_usage
1690 IS
1691 SELECT lifecycle_usage_id
1692 from pa_lifecycle_usages
1693 where lifecycle_id = P_lifecycle_id
1694 AND usage_type=c_product_type;
1695
1696 CURSOR l_row_proj_elem_ver_structure
1697 IS
1698 Select rowid,PEV_STRUCTURE_ID,element_version_id,record_version_number
1699 From pa_proj_elem_ver_structure
1700 WHERE element_version_id = l_element_version_id
1701 AND proj_element_id = P_lifecycle_id
1702 AND project_id = c_project_id;
1703
1704
1705 BEGIN
1706
1707 IF(p_debug_mode = 'Y') THEN
1708 pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: Inside update_lifecycle...');
1709 END IF;
1710
1711 IF(p_commit = FND_API.G_TRUE) THEN
1712 SAVEPOINT LCYL_UPDATE_LIFECYCLE_PVT;
1713 END IF;
1714
1715 IF(p_debug_mode = 'Y') THEN
1716 pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: Checking api compatibility...');
1717 END IF;
1718
1719 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1720 p_api_version,
1721 l_api_name,
1722 g_pkg_name)
1723 THEN
1724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1725 END IF;
1726
1727 x_return_status := FND_API.G_RET_STS_SUCCESS;
1728
1729
1730 OPEN l_row_proj_element_versions;
1731 FETCH l_row_proj_element_versions into l_element_version_id;
1732 CLOSE l_row_proj_element_versions;
1733
1734 OPEN get_project_usage;
1735 FETCH get_project_usage into l_prj_lcyl_usage_id;
1736 CLOSE get_project_usage;
1737
1738 IF l_prj_lcyl_usage_id is null THEN
1739 l_proj_usg_exists := 'N';
1740 ELSE
1741 l_proj_usg_exists := 'Y';
1742 END IF;
1743
1744 OPEN get_product_usage;
1745 FETCH get_product_usage into l_prd_lcyl_usage_id;
1746 CLOSE get_product_usage;
1747
1748 IF l_prd_lcyl_usage_id is null THEN
1749 l_prod_usg_exists := 'N';
1750 ELSE
1751 l_prod_usg_exists := 'Y';
1752 END IF;
1753
1754 IF(p_debug_mode = 'Y') THEN
1755 pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: Checking for used by workplan structure or not..');
1756 END IF;
1757
1758 IF l_proj_usg_exists = 'Y' THEN
1759 BEGIN
1760 SELECT 'Y'
1761 into l_workplan_used
1762 from SYS.DUAL
1763 where exists(select 'XYZ' from pa_proj_workplan_attr
1764 where lifecycle_version_id = l_element_version_id);
1765 exception
1766 when NO_DATA_FOUND then
1767 l_workplan_used := 'N';
1768 END;
1769
1770 -- No need to check for l_proj_usg_exists as if lifecycle is used in workplan structure. I means it has project usages.
1771 -- Can's make the usage to N if it is used in workplan structure
1772
1773 IF( l_workplan_used = 'Y' AND P_lifecycle_project_usage_type = 'N') then
1774 PA_UTILS.ADD_MESSAGE(
1775 p_app_short_name => 'PA',
1776 p_msg_name => 'PA_LCYL_PRJ_USG_CHG_NOT_ALWD');
1777 x_msg_data := 'PA_LCYL_PRJ_USG_CHG_NOT_ALWD';
1778 x_return_status := FND_API.G_RET_STS_ERROR;
1779 END IF;
1780 END IF;
1781
1782 -- Similar check for item category also has to be made as done above for workplan
1783
1784 IF (l_prod_usg_exists = 'Y' AND P_lifecycle_product_usage_type = 'N') THEN
1785 BEGIN
1786 PA_EGO_WRAPPER_PUB.check_delete_lifecycle_ok(
1787 p_api_version => P_api_version ,
1788 p_lifecycle_id => p_lifecycle_id ,
1789 x_delete_ok => l_item_used ,
1790 x_return_status => l_return_status ,
1791 x_errorcode => l_errorcode ,
1792 x_msg_count => l_msg_count ,
1793 x_msg_data => l_msg_data
1794 );
1795 END;
1796 END IF;
1797
1798 IF (p_debug_mode = 'Y') THEN
1799 pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: checking message count');
1800 END IF;
1801
1802 l_msg_count := FND_MSG_PUB.count_msg;
1803
1804 If l_msg_count > 0 THEN
1805 x_msg_count := l_msg_count;
1806 If l_msg_count = 1 THEN
1807 pa_interface_utils_pub.get_messages
1808 (p_encoded => FND_API.G_TRUE ,
1809 p_msg_index => 1,
1810 p_msg_count => l_msg_count ,
1811 p_msg_data => l_msg_data,
1812 p_data => l_data,
1813 p_msg_index_out => l_msg_index_out );
1814 x_msg_data := l_data;
1815 End if;
1816 RAISE FND_API.G_EXC_ERROR;
1817 End if;
1818
1819 IF (p_debug_mode = 'Y') THEN
1820 pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle: After checking message count and applying locking ');
1821 END IF;
1822
1823 IF (p_validate_only <> FND_API.G_TRUE) THEN
1824 BEGIN
1825 --lock
1826 select rowid into l_rowid
1827 from pa_proj_elements
1828 where proj_element_id = p_lifecycle_id
1829 AND record_version_number = p_record_version_number
1830 AND project_id = c_project_id
1831 AND object_type = c_object_type
1832 for update of record_version_number NOWAIT;
1833
1834 EXCEPTION
1835
1836 when TIMEOUT_ON_RESOURCE then
1837 x_return_status := FND_API.G_RET_STS_ERROR;
1838 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1839 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1840 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1841
1842 when NO_DATA_FOUND then
1843 x_return_status := FND_API.G_RET_STS_ERROR;
1844 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1845 p_msg_name => 'PA_XC_RECORD_CHANGED');
1846 l_msg_data := 'PA_XC_RECORD_CHANGED';
1847
1848 when OTHERS then
1849 x_return_status := FND_API.G_RET_STS_ERROR;
1850 IF SQLCODE = -54 then
1851
1852 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1853 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
1854 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
1855 ELSE
1856 raise;
1857 END IF;
1858 END;
1859 ELSE
1860 BEGIN
1861 SELECT rowid into l_rowid
1862 FROM pa_proj_elements
1863 WHERE proj_element_id = P_lifecycle_id
1864 AND project_id = c_project_id
1865 AND object_type = c_object_type
1866 AND record_version_number = p_record_version_number;
1867
1868 EXCEPTION
1869
1870 when NO_DATA_FOUND then
1871 x_return_status := FND_API.G_RET_STS_ERROR;
1872 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
1873 p_msg_name => 'PA_XC_RECORD_CHANGED');
1874 l_msg_data := 'PA_XC_RECORD_CHANGED';
1875
1876 when OTHERS then
1877 raise;
1878 END;
1879 END IF;
1880
1881 l_msg_count := FND_MSG_PUB.count_msg;
1882
1883 If l_msg_count > 0 THEN
1884 x_msg_count := l_msg_count;
1885 If l_msg_count = 1 THEN
1886 pa_interface_utils_pub.get_messages
1887 (p_encoded => FND_API.G_TRUE ,
1888 p_msg_index => 1,
1889 p_msg_count => l_msg_count ,
1890 p_msg_data => l_msg_data,
1891 p_data => l_data,
1892 p_msg_index_out => l_msg_index_out );
1893 x_msg_data := l_data;
1894 End if;
1895 RAISE FND_API.G_EXC_ERROR;
1896 End if;
1897
1898 IF(p_debug_mode = 'Y') THEN
1899 pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle:Inserting into pa_proj_elements...');
1900 END IF;
1901
1902
1903
1904 PA_PROJ_ELEMENTS_PKG.UPDATE_ROW(
1905 X_ROW_ID => l_rowid ,
1906 X_PROJ_ELEMENT_ID => P_lifecycle_id ,
1907 X_PROJECT_ID =>c_project_id ,
1908 X_OBJECT_TYPE =>c_object_type ,
1909 X_ELEMENT_NUMBER =>P_lifecycle_short_name ,
1910 X_NAME =>P_lifecycle_name ,
1911 X_DESCRIPTION =>P_lifecycle_description ,
1912 X_STATUS_CODE =>NULL ,
1913 X_WF_STATUS_CODE =>NULL ,
1914 X_PM_PRODUCT_CODE =>NULL ,
1915 X_PM_TASK_REFERENCE =>NULL ,
1916 X_CLOSED_DATE =>NULL ,
1917 X_LOCATION_ID =>NULL ,
1918 X_MANAGER_PERSON_ID => NULL ,
1919 X_CARRYING_OUT_ORGANIZATION_ID => NULL ,
1920 X_TYPE_ID => NULL ,
1921 X_PRIORITY_CODE => NULL ,
1922 X_INC_PROJ_PROGRESS_FLAG => NULL ,
1923 X_RECORD_VERSION_NUMBER => P_record_version_number ,
1924 X_REQUEST_ID => NULL ,
1925 X_PROGRAM_APPLICATION_ID => NULL ,
1926 X_PROGRAM_ID => NULL ,
1927 X_PROGRAM_UPDATE_DATE => NULL ,
1928 X_ATTRIBUTE_CATEGORY => NULL ,
1929 X_ATTRIBUTE1 => NULL ,
1930 X_ATTRIBUTE2 => NULL ,
1931 X_ATTRIBUTE3 => NULL ,
1932 X_ATTRIBUTE4 => NULL ,
1933 X_ATTRIBUTE5 => NULL ,
1934 X_ATTRIBUTE6 => NULL ,
1935 X_ATTRIBUTE7 => NULL ,
1936 X_ATTRIBUTE8 => NULL ,
1937 X_ATTRIBUTE9 => NULL ,
1938 X_ATTRIBUTE10 => NULL ,
1939 X_ATTRIBUTE11 => NULL ,
1940 X_ATTRIBUTE12 => NULL ,
1941 X_ATTRIBUTE13 => NULL ,
1942 X_ATTRIBUTE14 => NULL ,
1943 X_ATTRIBUTE15 => NULL ,
1944 X_TASK_WEIGHTING_DERIV_CODE => NULL ,
1945 X_WORK_ITEM_CODE => NULL ,
1946 X_UOM_CODE => NULL ,
1947 X_WQ_ACTUAL_ENTRY_CODE => NULL ,
1948 X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL ,
1949 X_parent_structure_id => NULL ,
1950 X_phase_code => NULL ,
1951 X_phase_version_id => NULL
1952 );
1953
1954
1955 OPEN l_row_proj_elem_ver_structure;
1956 FETCH l_row_proj_elem_ver_structure into l_rowid,l_pev_id,l_elem_vers_id,l_record_version_number;
1957 CLOSE l_row_proj_elem_ver_structure;
1958
1959 IF(p_debug_mode = 'Y') THEN
1960 pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle:Inserting into PA_PROJ_ELEM_VER_STRUCTURE...');
1961 END IF;
1962
1963 PA_PROJ_ELEM_VER_STRUCTURE_PKG.UPDATE_ROW (
1964 X_ROWID =>l_rowid ,
1965 X_PEV_STRUCTURE_ID =>l_pev_id ,
1966 X_ELEMENT_VERSION_ID =>l_elem_vers_id ,
1967 X_VERSION_NUMBER => 1 ,
1968 X_NAME => P_lifecycle_name ,
1969 X_PROJECT_ID => c_project_id ,
1970 X_PROJ_ELEMENT_ID => P_lifecycle_id ,
1971 X_DESCRIPTION => P_lifecycle_description ,
1972 X_EFFECTIVE_DATE => NULL ,
1973 X_PUBLISHED_DATE => NULL ,
1974 X_PUBLISHED_BY => NULL ,
1975 X_CURRENT_BASELINE_DATE => NULL ,
1976 X_CURRENT_BASELINE_FLAG => 'Y' ,
1977 X_CURRENT_BASELINE_BY => NULL ,
1978 X_ORIGINAL_BASELINE_DATE => NULL ,
1979 X_ORIGINAL_BASELINE_FLAG => 'Y' ,
1980 X_ORIGINAL_BASELINE_BY => NULL ,
1981 X_LOCK_STATUS_CODE => NULL ,
1982 X_LOCKED_BY => NULL ,
1983 X_LOCKED_DATE => NULL ,
1984 X_STATUS_CODE => NULL ,
1985 X_WF_STATUS_CODE => NULL ,
1986 X_LATEST_EFF_PUBLISHED_FLAG => 'Y' ,
1987 X_CHANGE_REASON_CODE => NULL ,
1988 X_RECORD_VERSION_NUMBER => l_record_version_number
1989 );
1990
1991
1992
1993
1994 IF(p_debug_mode = 'Y') THEN
1995 pa_debug.debug('PA_LIFECYCLES_PVT.Update_Lifecycle:Inserting into PA_LIFECYCLE_USAGES...');
1996 END IF;
1997
1998 IF (l_proj_usg_exists = 'N' AND P_lifecycle_project_usage_type = 'Y') THEN
1999 SELECT PA_LIFECYCLE_USAGES_S.NEXTVAL
2000 INTO l_prj_lcyl_usage_id
2001 FROM dual;
2002
2003 PA_LIFECYCLE_USAGES_PKG.insert_row(
2004 X_lifecycle_usage_id => l_prj_lcyl_usage_id ,
2005 X_RECORD_VERSION_NUMBER => 1 ,
2006 X_lifecycle_ID => P_lifecycle_id ,
2007 X_USAGE_TYPE => c_project_type
2008 );
2009
2010 ELSIF (l_proj_usg_exists = 'Y' AND P_lifecycle_project_usage_type = 'N') THEN
2011 PA_LIFECYCLE_USAGES_PKG.delete_Row(l_prj_lcyl_usage_id);
2012
2013 END IF;
2014
2015 IF (l_prod_usg_exists = 'N' AND P_lifecycle_product_usage_type = 'Y') THEN
2016 SELECT PA_LIFECYCLE_USAGES_S.NEXTVAL
2017 INTO l_prd_lcyl_usage_id
2018 FROM dual;
2019
2020 PA_LIFECYCLE_USAGES_PKG.insert_row(
2021 X_lifecycle_usage_id => l_prd_lcyl_usage_id ,
2022 X_RECORD_VERSION_NUMBER => 1 ,
2023 X_lifecycle_ID => P_lifecycle_id ,
2024 X_USAGE_TYPE => c_product_type
2025 );
2026 ELSIF (l_prod_usg_exists = 'Y' AND P_lifecycle_product_usage_type = 'N') THEN
2027 PA_LIFECYCLE_USAGES_PKG.delete_Row(l_prd_lcyl_usage_id);
2028 END IF;
2029
2030 EXCEPTION
2031
2032 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2033 IF p_commit = FND_API.G_TRUE THEN
2034 ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PVT;
2035 END IF;
2036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2037 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
2038 p_procedure_name => 'UPDATE_LIFECYCLE',
2039 p_error_text => SUBSTRB(SQLERRM,1,240));
2040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2041
2042 WHEN FND_API.G_EXC_ERROR THEN
2043 x_return_status := FND_API.G_RET_STS_ERROR;
2044 IF p_commit = FND_API.G_TRUE THEN
2045 ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PVT;
2046 END IF;
2047
2048
2049 WHEN OTHERS THEN
2050 IF p_commit = FND_API.G_TRUE THEN
2051 ROLLBACK TO LCYL_UPDATE_LIFECYCLE_PVT;
2052 END IF;
2053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2054 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
2055 p_procedure_name => 'UPDATE_LIFECYCLE',
2056 p_error_text => SUBSTRB(SQLERRM,1,240));
2057 raise;
2058
2059 END update_lifecycle;
2060
2061 /*-----------------------------------------------------------+
2062 | For Details/Comments Refer Package Specification Comments |
2063 +-----------------------------------------------------------*/
2064
2065 PROCEDURE update_lifecycle_phase (
2066 P_api_version IN NUMBER :=1.0 ,
2067 p_commit IN VARCHAR2 :=FND_API.G_FALSE ,
2068 p_validate_only IN VARCHAR2 :=FND_API.G_TRUE ,
2069 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL ,
2070 p_calling_module IN VARCHAR2 :='SELF_SERVICE' ,
2071 p_debug_mode IN VARCHAR2 :='N' ,
2072 P_max_msg_count IN NUMBER :=G_MISS_NUM ,
2073 P_lifecycle_id IN NUMBER ,
2074 P_lifecycle_phase_id IN NUMBER ,
2075 P_phase_display_sequence IN NUMBER ,
2076 P_phase_code IN VARCHAR2 ,
2077 P_phase_short_name IN VARCHAR2 ,
2078 P_phase_name IN VARCHAR2 ,
2079 P_phase_description IN VARCHAR2 ,
2080 P_record_version_number IN NUMBER ,
2081 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2082 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2083 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2084 )IS
2085
2086 l_api_name CONSTANT VARCHAR2(30) := 'update_lifecycle_phase';
2087 l_api_version CONSTANT NUMBER := 1.0;
2088 l_msg_count NUMBER;
2089 l_msg_index_out NUMBER;
2090 l_data VARCHAR2(2000);
2091 l_msg_data VARCHAR2(2000);
2092 l_return_status VARCHAR2(1);
2093
2094 l_project_usage_exists VARCHAR2(1):='N';
2095 l_product_usage_exists VARCHAR2(1):='N';
2096 c_project_type CONSTANT VARCHAR2(30) :='PROJECTS';
2097 c_product_type CONSTANT VARCHAR2(30) :='PRODUCTS';
2098 c_object_type CONSTANT VARCHAR(30) := 'PA_TASKS';
2099 c_project_id CONSTANT NUMBER :=0;
2100 l_lcyl_assigned VARCHAR2(1):='N';
2101 l_phas_assigned VARCHAR2(1):='N';
2102 l_lcyl_in_use VARCHAR2(1):='N';
2103
2104
2105 l_is_uniq VARCHAR2(1):='N';
2106 l_rowid VARCHAR2(30);
2107 l_pev_id NUMBER;
2108 l_elem_vers_id NUMBER;
2109 l_parent_elem_vers_id NUMBER;
2110 l_update_ok VARCHAR2(1);
2111 l_record_version_number NUMBER;
2112
2113 CURSOR l_row_proj_elem_ver_structure
2114 IS
2115 Select rowid,PEV_STRUCTURE_ID,element_version_id
2116 From pa_proj_elem_ver_structure
2117 Where proj_element_id = P_lifecycle_id
2118 AND project_id = c_project_id;
2119
2120 CURSOR l_row_proj_elem_vers
2121 IS
2122 Select rowid,element_version_id,parent_structure_version_id,record_version_number
2123 From pa_proj_element_versions
2124 Where proj_element_id = P_lifecycle_phase_id
2125 AND project_id = c_project_id
2126 AND object_type = c_object_type;
2127
2128
2129 BEGIN
2130
2131 IF(p_debug_mode = 'Y') THEN
2132 pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Inside update_lifecycle_phase...');
2133 END IF;
2134
2135 IF(p_debug_mode = 'Y') THEN
2136 pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Checking api compatibility...');
2137 END IF;
2138
2139 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2140 p_api_version,
2141 l_api_name,
2142 g_pkg_name)
2143 THEN
2144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2145 END IF;
2146
2147 IF(p_commit = FND_API.G_TRUE) THEN
2148 SAVEPOINT LCYL_UPD_LCYL_PHASE_PVT;
2149 END IF;
2150
2151 x_return_status := FND_API.G_RET_STS_SUCCESS;
2152
2153 /*** NOte that we shd not check existing phase code, short name and sequence here, bcos user may want to
2154 swap two phases. This is JAva layer responsibility to take care ***/
2155
2156 --Lock record
2157
2158 IF(p_debug_mode = 'Y') THEN
2159 pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Locking record ..');
2160 END IF;
2161
2162 IF (p_validate_only <> FND_API.G_TRUE) THEN
2163 BEGIN
2164 --lock
2165 select rowid into l_rowid
2166 from pa_proj_elements
2167 where proj_element_id = p_lifecycle_phase_id
2168 AND record_version_number = p_record_version_number
2169 AND project_id = c_project_id
2170 AND object_type = c_object_type
2171 for update of record_version_number NOWAIT;
2172
2173 EXCEPTION
2174
2175 when TIMEOUT_ON_RESOURCE then
2176 x_return_status := FND_API.G_RET_STS_ERROR;
2177 PA_UTILS.ADD_MESSAGE(
2178 p_app_short_name => 'PA',
2179 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
2180 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
2181
2182 when NO_DATA_FOUND then
2183 x_return_status := FND_API.G_RET_STS_ERROR;
2184 PA_UTILS.ADD_MESSAGE(
2185 p_app_short_name => 'PA',
2186 p_msg_name => 'PA_XC_RECORD_CHANGED');
2187 l_msg_data := 'PA_XC_RECORD_CHANGED';
2188
2189 when OTHERS then
2190 x_return_status := FND_API.G_RET_STS_ERROR;
2191 IF SQLCODE = -54 then
2192
2193 PA_UTILS.ADD_MESSAGE(
2194 p_app_short_name => 'PA',
2195 p_msg_name => 'PA_XC_ROW_ALREADY_LOCKED');
2196 l_msg_data := 'PA_XC_ROW_ALREADY_LOCKED';
2197 ELSE
2198 raise;
2199 END IF;
2200 END;
2201 ELSE
2202 BEGIN
2203 SELECT rowid into l_rowid
2204 FROM pa_proj_elements
2205 WHERE proj_element_id = p_lifecycle_phase_id
2206 AND project_id = c_project_id
2207 AND object_type = c_object_type
2208 AND record_version_number = p_record_version_number;
2209
2210 EXCEPTION
2211
2212 when NO_DATA_FOUND then
2213 x_return_status := FND_API.G_RET_STS_ERROR;
2214 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
2215 p_msg_name => 'PA_XC_RECORD_CHANGED');
2216 l_msg_data := 'PA_XC_RECORD_CHANGED';
2217 when OTHERS then
2218 raise;
2219 END;
2220 END IF;
2221
2222 IF(p_debug_mode = 'Y') THEN
2223 pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Checking message count...');
2224 END IF;
2225
2226 l_msg_count := FND_MSG_PUB.count_msg;
2227
2228 If l_msg_count > 0 THEN
2229 x_msg_count := l_msg_count;
2230 If l_msg_count = 1 THEN
2231 pa_interface_utils_pub.get_messages
2232 (p_encoded => FND_API.G_TRUE ,
2233 p_msg_index => 1,
2234 p_msg_count => l_msg_count ,
2235 p_msg_data => l_msg_data,
2236 p_data => l_data,
2237 p_msg_index_out => l_msg_index_out );
2238 x_msg_data := l_data;
2239 End if;
2240 RAISE FND_API.G_EXC_ERROR;
2241 End if;
2242
2243 IF(p_debug_mode = 'Y') THEN
2244 pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Updating PA_PROJ_ELEMENTS...');
2245 END IF;
2246
2247 PA_PROJ_ELEMENTS_PKG.UPDATE_ROW(
2248 X_ROW_ID => l_rowid ,
2249 X_PROJ_ELEMENT_ID => P_lifecycle_phase_id ,
2250 X_PROJECT_ID =>c_project_id ,
2251 X_OBJECT_TYPE =>c_object_type ,
2252 X_ELEMENT_NUMBER =>P_phase_short_name ,
2253 X_NAME =>P_phase_name ,
2254 X_DESCRIPTION =>P_phase_description ,
2255 X_STATUS_CODE => NULL ,
2256 X_WF_STATUS_CODE => NULL ,
2257 X_PM_PRODUCT_CODE => NULL ,
2258 X_PM_TASK_REFERENCE => NULL ,
2259 X_CLOSED_DATE => NULL ,
2260 X_LOCATION_ID => NULL ,
2261 X_MANAGER_PERSON_ID => NULL ,
2262 X_CARRYING_OUT_ORGANIZATION_ID => NULL ,
2263 X_TYPE_ID => NULL ,
2264 X_PRIORITY_CODE => NULL ,
2265 X_INC_PROJ_PROGRESS_FLAG => NULL ,
2266 X_RECORD_VERSION_NUMBER => P_record_version_number ,
2267 X_REQUEST_ID => NULL ,
2268 X_PROGRAM_APPLICATION_ID => NULL ,
2269 X_PROGRAM_ID => NULL ,
2270 X_PROGRAM_UPDATE_DATE => NULL ,
2271 X_ATTRIBUTE_CATEGORY => NULL ,
2272 X_ATTRIBUTE1 => NULL ,
2273 X_ATTRIBUTE2 => NULL ,
2274 X_ATTRIBUTE3 => NULL ,
2275 X_ATTRIBUTE4 => NULL ,
2276 X_ATTRIBUTE5 => NULL ,
2277 X_ATTRIBUTE6 => NULL ,
2278 X_ATTRIBUTE7 => NULL ,
2279 X_ATTRIBUTE8 => NULL ,
2280 X_ATTRIBUTE9 => NULL ,
2281 X_ATTRIBUTE10 => NULL ,
2282 X_ATTRIBUTE11 => NULL ,
2283 X_ATTRIBUTE12 => NULL ,
2284 X_ATTRIBUTE13 => NULL ,
2285 X_ATTRIBUTE14 => NULL ,
2286 X_ATTRIBUTE15 => NULL ,
2287 X_TASK_WEIGHTING_DERIV_CODE => NULL ,
2288 X_WORK_ITEM_CODE => NULL ,
2289 X_UOM_CODE => NULL ,
2290 X_WQ_ACTUAL_ENTRY_CODE => NULL ,
2291 X_TASK_PROGRESS_ENTRY_PAGE_ID => NULL ,
2292 x_parent_structure_id => P_lifecycle_id ,
2293 x_phase_code => p_phase_code ,
2294 x_phase_version_id => NULL
2295 );
2296
2297 if(P_phase_display_sequence is NOT NULL) then
2298 OPEN l_row_proj_elem_vers;
2299 FETCH l_row_proj_elem_vers into l_rowid, l_elem_vers_id,l_parent_elem_vers_id,l_record_version_number ;
2300 CLOSE l_row_proj_elem_vers;
2301
2302 IF(p_debug_mode = 'Y') THEN
2303 pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Updating PA_PROJ_ELEMENT_VERSIONS...');
2304 END IF;
2305
2306 PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row(
2307 X_ROW_ID => l_rowid ,
2308 X_ELEMENT_VERSION_ID =>l_elem_vers_id ,
2309 X_PROJ_ELEMENT_ID =>P_lifecycle_phase_id ,
2310 X_OBJECT_TYPE => c_object_type ,
2311 X_PROJECT_ID => c_project_id ,
2312 X_PARENT_STRUCTURE_VERSION_ID =>l_parent_elem_vers_id ,
2313 X_DISPLAY_SEQUENCE =>p_phase_display_sequence ,
2314 X_WBS_LEVEL => NULL ,
2315 X_WBS_NUMBER => NULL ,
2316 X_RECORD_VERSION_NUMBER => l_record_version_number ,
2317 X_ATTRIBUTE_CATEGORY => NULL ,
2318 X_ATTRIBUTE1 => NULL ,
2319 X_ATTRIBUTE2 => NULL ,
2320 X_ATTRIBUTE3 => NULL ,
2321 X_ATTRIBUTE4 => NULL ,
2322 X_ATTRIBUTE5 => NULL ,
2323 X_ATTRIBUTE6 => NULL ,
2324 X_ATTRIBUTE7 => NULL ,
2325 X_ATTRIBUTE8 => NULL ,
2326 X_ATTRIBUTE9 => NULL ,
2327 X_ATTRIBUTE10 => NULL ,
2328 X_ATTRIBUTE11 => NULL ,
2329 X_ATTRIBUTE12 => NULL ,
2330 X_ATTRIBUTE13 => NULL ,
2331 X_ATTRIBUTE14 => NULL ,
2332 X_ATTRIBUTE15 => NULL ,
2333 X_TASK_UNPUB_VER_STATUS_CODE => 'Working'
2334 );
2335 end if;
2336
2337
2338 IF(p_debug_mode = 'Y') THEN
2339 pa_debug.debug('PA_LIFECYCLES_PVT.update_lifecycle_phase: Checking message count after Updating tables.');
2340 END IF;
2341
2342 l_msg_count := FND_MSG_PUB.count_msg;
2343
2344 If l_msg_count > 0 THEN
2345 x_msg_count := l_msg_count;
2346 If l_msg_count = 1 THEN
2347 pa_interface_utils_pub.get_messages(
2348 p_encoded => FND_API.G_TRUE ,
2349 p_msg_index => 1 ,
2350 p_msg_count => l_msg_count ,
2351 p_msg_data => l_msg_data ,
2352 p_data => l_data ,
2353 p_msg_index_out => l_msg_index_out
2354 );
2355 x_msg_data := l_data;
2356 End if;
2357 RAISE FND_API.G_EXC_ERROR;
2358 End if;
2359
2360 x_return_status := FND_API.G_RET_STS_SUCCESS;
2361
2362 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
2363 COMMIT;
2364 END IF;
2365
2366 EXCEPTION
2367
2368 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2369 IF p_commit = FND_API.G_TRUE THEN
2370 ROLLBACK TO LCYL_UPD_LCYL_PHASE_PVT;
2371 END IF;
2372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2373 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT' ,
2374 p_procedure_name => 'update_lifecycle_phase' ,
2375 p_error_text => SUBSTRB(SQLERRM,1,240)
2376 );
2377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2378
2379 WHEN FND_API.G_EXC_ERROR THEN
2380 IF p_commit = FND_API.G_TRUE THEN
2381 ROLLBACK TO LCYL_UPD_LCYL_PHASE_PVT;
2382 END IF;
2383 x_return_status := FND_API.G_RET_STS_ERROR;
2384
2385 WHEN OTHERS THEN
2386 IF p_commit = FND_API.G_TRUE THEN
2387 ROLLBACK TO LCYL_UPD_LCYL_PHASE_PVT;
2388 END IF;
2389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2390 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT' ,
2391 p_procedure_name => 'update_lifecycle_phase' ,
2392 p_error_text => SUBSTRB(SQLERRM,1,240));
2393 raise;
2394
2395 END update_lifecycle_phase;
2396
2397
2398 /*-----------------------------------------------------------+
2399 | For Details/Comments Refer Package Specification Comments |
2400 +-----------------------------------------------------------*/
2401
2402 PROCEDURE check_delete_lcyl_phase_ok(
2403 P_api_version IN NUMBER := 1.0 ,
2404 p_calling_module IN VARCHAR2 := 'SELF_SERVICE' ,
2405 p_debug_mode IN VARCHAR2 := 'N' ,
2406 P_max_msg_count IN NUMBER := G_MISS_NUM ,
2407 P_lifecycle_id IN NUMBER ,
2408 P_lifecycle_phase_id IN NUMBER ,
2409 x_delete_ok OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2410 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2411 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2412 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2413 )
2414 IS
2415
2416 l_api_name CONSTANT VARCHAR2(30) := 'check_delete_lcyl_phase_ok';
2417 l_api_version CONSTANT NUMBER := 1.0;
2418 l_msg_count NUMBER;
2419 l_msg_index_out NUMBER;
2420 l_data VARCHAR2(2000);
2421 l_msg_data VARCHAR2(2000);
2422 l_return_status VARCHAR2(1);
2423
2424 l_project_usage_exists VARCHAR2(1):='N';
2425 l_product_usage_exists VARCHAR2(1):='N';
2426 c_project_type CONSTANT VARCHAR2(30) :='PROJECTS';
2427 c_product_type CONSTANT VARCHAR2(30) :='PRODUCTS';
2428 c_object_type CONSTANT VARCHAR2(30) := 'PA_TASKS';
2429 c_project_id CONSTANT NUMBER :=0;
2430 l_delete_ok VARCHAR2(1) := FND_API.G_TRUE;
2431 l_parent_elem_ver_id number;
2432 l_child_elem_ver_id number;
2433 BEGIN
2434
2435 IF(p_debug_mode = 'Y') THEN
2436 pa_debug.debug('PA_LIFECYCLE PVT.check_delete_lcyl_phase_ok: Checking api compatibility...');
2437 END IF;
2438
2439 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2440 p_api_version,
2441 l_api_name,
2442 g_pkg_name)
2443 THEN
2444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2445 END IF;
2446
2447 begin
2448 select 'Y'
2449 into l_project_usage_exists
2450 from SYS.DUAL
2451 where exists( select 'XYZ' from pa_lifecycle_usages
2452 where lifecycle_id=P_lifecycle_id AND
2453 usage_type=c_project_type);
2454 exception
2455 when NO_DATA_FOUND then
2456 l_project_usage_exists := 'N';
2457 END;
2458
2459
2460 begin
2461 select 'Y'
2462 into l_product_usage_exists
2463 from SYS.DUAL
2464 where exists( select 'XYZ' from pa_lifecycle_usages
2465 where lifecycle_id=P_lifecycle_id AND
2466 usage_type=c_product_type);
2467 exception
2468 when NO_DATA_FOUND then
2469 l_product_usage_exists := 'N';
2470 END;
2471
2472 begin
2473 select element_version_id
2474 into l_parent_elem_ver_id
2475 from pa_proj_element_versions
2476 where proj_element_id = P_lifecycle_id
2477 and object_type = 'PA_STRUCTURES'
2478 and project_id = c_project_id;
2479 exception
2480 when NO_DATA_FOUND then
2481 raise;
2482 END;
2483
2484 begin
2485 select element_version_id
2486 into l_child_elem_ver_id
2487 from pa_proj_element_versions
2488 where proj_element_id = P_lifecycle_phase_id
2489 and object_type = 'PA_TASKS'
2490 and project_id = c_project_id;
2491 exception
2492 when NO_DATA_FOUND then
2493 raise;
2494 END;
2495
2496 /* If the usage type is project and the following 2 conditions are satisfied
2497 1) Lifecycle has been assigned to a workplan
2498 2) **ANY** phase has been assigned to a top task
2499
2500 then we cannot allow update of sequence number and phase code
2501 */
2502
2503 IF(p_debug_mode = 'Y') THEN
2504 pa_debug.debug('PA_LIFECYCLE PVT.check_delete_lcyl_phase_ok: After checking for usuage type checking for top task assigned');
2505 END IF;
2506
2507 IF(l_project_usage_exists = 'Y') then
2508 begin
2509 select FND_API.G_FALSE into l_delete_ok
2510 from sys.dual
2511 where exists(
2512 select 'xyz'
2513 from pa_proj_workplan_attr
2514 where lifecycle_version_id = l_parent_elem_ver_id
2515 and current_phase_version_id = l_child_elem_ver_id);
2516
2517 exception
2518 when NO_DATA_FOUND then
2519 l_delete_ok := FND_API.G_TRUE;
2520 END;
2521
2522 IF l_delete_ok <> FND_API.G_TRUE THEN
2523 PA_UTILS.ADD_MESSAGE(
2524 p_app_short_name => 'PA',
2525 p_msg_name => 'PA_LCYL_USED_CURR_PHASE');
2526 l_msg_data := 'PA_LCYL_USED_CURR_PHASE';
2527 x_return_status := FND_API.G_RET_STS_ERROR;
2528 x_delete_ok := l_delete_ok;
2529
2530 ELSE
2531
2532 BEGIN
2533 select FND_API.G_FALSE into l_delete_ok
2534 from sys.dual
2535 where exists(
2536 select 'xyz'
2537 from pa_proj_element_versions child
2538 ,pa_proj_elements tasks
2539 where child.parent_structure_version_id = l_parent_elem_ver_id
2540 and child.project_id = c_project_id
2541 and child.object_type = 'PA_TASKS'
2542 and tasks.phase_version_id = child.element_version_id
2543 and tasks.project_id <> 0
2544 and tasks.object_type = 'PA_TASKS');
2545 exception
2546 when no_data_found then
2547 l_delete_ok := FND_API.G_TRUE;
2548 end;
2549 IF l_delete_ok <> FND_API.G_TRUE THEN
2550 PA_UTILS.ADD_MESSAGE(
2551 p_app_short_name => 'PA',
2552 p_msg_name => 'PA_LCYL_PHASE_TASK_USED');
2553 l_msg_data := 'PA_LCYL_PHASE_TASK_USED';
2554 x_return_status := FND_API.G_RET_STS_ERROR;
2555 x_delete_ok := l_delete_ok;
2556 END IF;
2557
2558 END IF;
2559
2560 END if;
2561
2562 /* Similarly it shd be done for PLM item category */
2563
2564 l_msg_count := FND_MSG_PUB.count_msg;
2565 IF (p_debug_mode = 'Y') THEN
2566 pa_debug.debug('PA_LIFECYCLES_PVT.check_delete_lcyl_phase_ok: checking message count ');
2567 END IF;
2568
2569 IF l_msg_count > 0 THEN
2570 x_msg_count := l_msg_count;
2571 IF l_msg_count = 1 THEN
2572 pa_interface_utils_pub.get_messages(
2573 p_encoded => FND_API.G_TRUE ,
2574 p_msg_index => 1 ,
2575 p_msg_count => l_msg_count ,
2576 p_msg_data => l_msg_data ,
2577 p_data => l_data ,
2578 p_msg_index_out => l_msg_index_out
2579 );
2580 x_msg_data := l_data;
2581 End if;
2582 RAISE FND_API.G_EXC_ERROR;
2583 End if;
2584
2585 x_return_status := FND_API.G_RET_STS_SUCCESS;
2586
2587
2588 EXCEPTION
2589
2590 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2592 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
2593 p_procedure_name => 'check_delete_lcyl_phase_ok',
2594 p_error_text => SUBSTRB(SQLERRM,1,240));
2595
2596 WHEN FND_API.G_EXC_ERROR THEN
2597 x_return_status := FND_API.G_RET_STS_ERROR;
2598
2599 WHEN OTHERS THEN
2600 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2601 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_LIFECYCLES_PVT',
2602 p_procedure_name => 'check_delete_lcyl_phase_ok',
2603 p_error_text => SUBSTRB(SQLERRM,1,240));
2604 raise;
2605
2606
2607 END check_delete_lcyl_phase_ok;
2608
2609
2610 END PA_LIFECYCLES_PVT;