[Home] [Help]
PACKAGE BODY: APPS.PA_PROJ_STRUCTURE_PVT
Source
1 package body PA_PROJ_STRUCTURE_PVT as
2 /* $Header: PAXSTRVB.pls 120.4 2007/02/06 10:24:17 dthakker ship $ */
3 --
4 --
5 function CHECK_ASSO_PROJ_OK
6 (
7 p_task_id IN NUMBER,
8 p_project_id IN NUMBER
9 )
10 return VARCHAR2
11 IS
12 l_looped VARCHAR2(1);
13 l_linked VARCHAR2(1);
14 l_msgcnt NUMBER;
15 l_msg VARCHAR2(2000);
16 BEGIN
17 pa_debug.set_err_stack('CHECK_ASSO_PROJ_OK');
18 --
19 BEGIN
20 PA_PROJ_STRUCTURE_UTILS.CHECK_LOOPED_PROJECT(
21 p_task_id => p_task_id,
22 p_project_id => p_project_id,
23 x_return_status => l_looped,
24 x_msg_count => l_msgcnt,
25 x_msg_data => l_msg
26 );
27 END;
28 --
29 BEGIN
30 PA_PROJ_STRUCTURE_UTILS.CHECK_MERGED_PROJECT(
31 p_task_id => p_task_id,
32 p_project_id => p_project_id,
33 x_return_status => l_linked,
34 x_msg_count => l_msgcnt,
35 x_msg_data => l_msg
36 );
37 END;
38 --
39 IF ((l_looped = FND_API.G_RET_STS_SUCCESS) AND
40 (l_linked = FND_API.G_RET_STS_SUCCESS)) THEN
41 return FND_API.G_RET_STS_SUCCESS;
42 ELSE
43 return FND_API.G_RET_STS_ERROR;
44 END IF;
45 --
46 pa_debug.reset_err_stack;
47 EXCEPTION
48 WHEN OTHERS THEN
49 RAISE;
50 -- return FND_API.G_RET_STS_UNEXP_ERROR;
51 END CHECK_ASSO_PROJ_OK;
52 --
53 --
54 --
55 procedure CREATE_RELATIONSHIP
56 (
57 p_api_version IN NUMBER := 1.0,
58 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
59 p_commit IN VARCHAR2 := FND_API.G_FALSE,
60 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
61 p_debug_mode IN VARCHAR2 := 'N',
62 p_task_id IN NUMBER,
63 p_project_id IN NUMBER,
64 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
65 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
66 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
67 )
68 IS
69 l_owning_proj_id NUMBER;
70 l_create_ok VARCHAR2(1);
71 l_user_id NUMBER;
72 l_object_relationships_id NUMBER;
73 l_msg_index_out NUMBER;
74 l_cc_prvdr VARCHAR2(1);
75 --
76 l_proj_start_date DATE;
77 l_proj_end_date DATE;
78 l_task_start_date DATE;
79 l_task_end_date DATE;
80 -- added for Bug: 4537865
81 l_new_msg_count NUMBER;
82 -- added for Bug: 4537865
83 --
84 CURSOR get_owning_project_id(l_task_id NUMBER) IS
85 select project_id
86 from pa_tasks
87 where task_id = l_task_id;
88 --
89 CURSOR get_task_dates(l_task_id NUMBER) IS
90 select start_date, completion_date
91 from pa_tasks
92 where task_id = l_task_id;
93 --
94 CURSOR get_project_dates(l_project_id NUMBER) IS
95 select start_date, completion_date
96 from pa_projects_all
97 where project_id = l_project_id;
98 --
99 CURSOR IS_CC_PRVDR(l_project_id NUMBER) IS
100 select cc_prvdr_flag
101 from pa_project_types_all t, pa_projects_all p
102 --Added the org_id join for bug 5561054
103 where t.org_id = p.org_id
104 and t.project_type = p.project_type and
105 p.project_id = l_project_id;
106 --
107 --This is cursor is used when source task id and dest project id are passed to this api.
108 --This cursor returns subproject association relationships ids for the
109 --given source task id and destination project id.
110 CURSOR get_src_task_det(cp_src_task_id NUMBER) IS
111 SELECT ppev.element_version_id src_task_ver_id,ppe.project_id src_proj_id
112 FROM pa_proj_elements ppe,
113 pa_proj_element_versions ppev,
114 pa_tasks pt
115 WHERE ppe.proj_element_id = cp_src_task_id
116 AND ppe.project_id = ppev.project_id
117 AND ppe.proj_element_id = ppev.proj_element_id
118 AND pt.task_id = ppe.proj_element_id
119 -- Added for bug 4999937
120 AND ppev.parent_structure_version_id =
121 PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID(ppev.project_id);
122 --
123 l_src_task_det_rec get_src_task_det%ROWTYPE;
124 l_dest_proj_name VARCHAR2(50);
125 l_src_task_id NUMBER;
126 l_dest_proj_id NUMBER;
127 -- added for Bug: 4537865
128 l_new_dest_proj_id NUMBER;
129 -- added for Bug: 4537865
130 l_error_msg_code VARCHAR2(250);
131 l_data VARCHAR2(250);
132 l_msg_count NUMBER;
133 l_msg_data VARCHAR2(250);
134 --
135 Begin
136 pa_debug.set_err_stack('CREATE_RELATIONSHIP');
137 --
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139 l_user_id := FND_GLOBAL.USER_ID;
140 --
141 l_src_task_id:= p_task_id;
142 l_dest_proj_id:=p_project_id;
143 IF ((l_dest_proj_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) OR
144 (l_dest_proj_id IS NULL)) THEN
145 PA_UTILS.ADD_MESSAGE( 'PA', 'PA_PS_DEST_PROJ_NULL');
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148 --
149 OPEN IS_CC_PRVDR(p_project_id);
150 LOOP
151 FETCH IS_CC_PRVDR INTO l_cc_prvdr;
152 EXIT WHEN IS_CC_PRVDR%NOTFOUND;
153 END LOOP;
154 CLOSE IS_CC_PRVDR;
155 --
156 IF (l_cc_prvdr <> 'Y') AND (l_cc_prvdr <> 'y') THEN
157 l_create_ok := CHECK_ASSO_PROJ_OK(p_task_id => p_task_id, p_project_id => p_project_id);
158 --
159 IF (l_create_ok = FND_API.G_RET_STS_SUCCESS) THEN
160 --
161 OPEN get_src_task_det(p_task_id);
162 FETCH get_src_task_det INTO l_src_task_det_rec;
163 IF get_src_task_det%NOTFOUND THEN
164 PA_UTILS.ADD_Message('PA','PA_STRUCT_CREATE_ERR');
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 END IF;
167 CLOSE get_src_task_det;
168 PA_PROJ_ELEMENTS_UTILS.Project_Name_Or_Id(
169 p_project_name => l_dest_proj_name
170 ,p_project_id => l_dest_proj_id
171 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
172 --,x_project_id => l_dest_proj_id * commented for Bug: 4537865
173 ,x_project_id => l_new_dest_proj_id -- added for Bug: 4537865
174 ,x_return_status => x_return_status
175 ,x_error_msg_code => l_error_msg_code
176 );
177
178 -- added for Bug: 4537865
179 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
180 l_dest_proj_id := l_new_dest_proj_id;
181 END IF;
182 -- added for Bug: 4537865
183
184 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
185 PA_UTILS.ADD_MESSAGE( 'PA', l_error_msg_code);
186 x_return_status := FND_API.G_RET_STS_ERROR;
187 END IF;
188 --
189 /* Find owning project id */
190 OPEN get_owning_project_id(p_task_id);
191 LOOP
192 FETCH get_owning_project_id INTO l_owning_proj_id;
193 EXIT WHEN get_owning_project_id%NOTFOUND;
194 END LOOP;
195 CLOSE get_owning_project_id;
196 --
197 IF (l_owning_proj_id is NULL) THEN
198 /* Selected task does not belong to a valid project */
199 PA_UTILS.ADD_Message('PA','PA_STRUCT_CREATE_ERR');
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 ELSE
202 /* call table handler */
203 /*pa_object_relationships_pkg.insert_row(
204 p_user_id => l_user_id,
205 p_object_type_from => 'PA_TASKS',
206 p_object_id_from1 => p_task_id,
207 p_object_id_from2 => l_owning_proj_id,
208 p_object_id_from3 => null,
209 p_object_id_from4 => null,
210 p_object_id_from5 => null,
211 p_object_type_to => 'PA_PROJECTS',
212 p_object_id_to1 => p_project_id,
213 p_object_id_to2 => null,
214 p_object_id_to3 => null,
215 p_object_id_to4 => null,
216 p_object_id_to5 => null,
217 p_relationship_type => 'H',
218 p_relationship_subtype => null,
219 p_lag_day => null,
220 p_imported_lag => null,
221 p_priority => null,
222 p_pm_product_code => null,
223 X_OBJECT_RELATIONSHIP_ID => l_object_relationships_id,
224 X_RETURN_STATUS => x_return_status
225 );*/ --commented the call to implement new functionality
226 --
227 PA_RELATIONSHIP_PVT.create_subproject_association(
228 p_api_version => p_api_version,
229 p_init_msg_list => p_init_msg_list,
230 p_validate_only => p_validate_only,
231 -- p_validation_level => p_validation_level,
232 -- p_calling_module => p_calling_module,
233 p_commit => p_commit,
234 p_debug_mode => p_debug_mode,
235 p_max_msg_count => x_msg_count,
236 p_src_proj_id => l_src_task_det_rec.src_proj_id,
237 p_task_ver_id => l_src_task_det_rec.src_task_ver_id,
238 p_dest_proj_id => l_dest_proj_id,
239 p_dest_proj_name => l_dest_proj_name,
240 p_comment => NULL,
241 x_return_status => x_return_status,
242 --x_msg_count => x_msg_count, * commented for Bug Fix: 4537865
243 x_msg_count => l_new_msg_count, --added for Bug: 4537865
244 x_msg_data => x_msg_data);
245 --
246 /* Check for date warning */
247 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
248 --
249 --added for Bug: 4537865
250 x_msg_count := l_new_msg_count;
251 --added for Bug: 4537865
252 OPEN get_task_dates(p_task_id);
253 LOOP
254 FETCH get_task_dates INTO l_task_start_date, l_task_end_date;
255 EXIT WHEN get_task_dates%NOTFOUND;
256 END LOOP;
257 CLOSE get_task_dates;
258 --
259 OPEN get_project_dates(p_project_id);
260 LOOP
261 FETCH get_project_dates INTO l_proj_start_date, l_proj_end_date;
262 EXIT WHEN get_project_dates%NOTFOUND;
263 END LOOP;
264 CLOSE get_project_dates;
265 --
266 /* Check for date warning */
267 /* Check if subproject start date is earlier than task start date */
268 IF (l_task_start_date IS NOT NULL) THEN
269 IF (l_proj_start_date IS NULL) THEN
270 x_return_status := 'W';
271 ELSIF (l_proj_start_date < l_task_start_date) THEN
272 x_return_status := 'W';
273 END IF;
274 END IF;
275 /* Check if subproject end date is later than task end date */
276 IF (l_task_end_date IS NOT NULL) THEN
277 IF (l_proj_end_date IS NULL) THEN
278 x_return_status := 'W';
279 ELSIF (l_proj_end_date > l_task_end_date) THEN
280 x_return_status := 'W';
281 END IF;
282 END IF;
283 --
284 IF (x_return_status = 'W') THEN
285 PA_UTILS.Add_Message('PA','PA_STRUCT_DATE_WARNING');
286 END IF;
287 --
288 ELSE
289 PA_UTILS.ADD_Message('PA','PA_STRUCT_CREATE_ERR');
290 END IF; /* End Date warning check */
291 --
292 END IF; /* End Create Relationship */
293 ELSE
294 PA_UTILS.Add_Message('PA','PA_STRUCT_PJ_EXT_IN_STRUCT');
295 x_return_status := FND_API.G_RET_STS_ERROR;
296 END IF;
297 ELSE
298 PA_UTILS.Add_Message('PA','PA_STRUCT_PROJ_BILLABLE');
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 END IF;
301 --
302 x_msg_count := FND_MSG_PUB.Count_Msg;
303 --
304 IF (x_msg_count = 1) THEN
305 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
306 ,p_msg_index => 1
307 ,p_data => x_msg_data
308 ,p_msg_index_out => l_msg_index_out
309 );
310
311 ELSE
312 x_msg_data := null;
313 END IF;
314 pa_debug.reset_err_stack;
315 EXCEPTION
316 When OTHERS Then
317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
318 RAISE;
319 END CREATE_RELATIONSHIP;
320 --
321 --
322 --
323 procedure DELETE_RELATIONSHIP
324 (
325 p_api_version IN NUMBER := 1.0,
326 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
327 p_commit IN VARCHAR2 := FND_API.G_FALSE,
328 p_validate_only IN VARCHAR2 := FND_API.G_TRUE,
329 p_debug_mode IN VARCHAR2 := 'N',
330 p_task_id IN NUMBER,
331 p_project_id IN NUMBER,
332 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
333 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
334 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
335 )
336 IS
337 l_project_id NUMBER;
338 l_task_id NUMBER;
339 l_project_from_id NUMBER;
340 l_object_relationship_id NUMBER;
341 l_msg_index_out NUMBER;
342 l_disassociation VARCHAR2(1);
343 --
344 CURSOR get_all_projects_for_task(l_task_id NUMBER) IS
345 select
346 object_id_to1, object_relationship_id
347 from
348 pa_object_relationships
349 where
350 object_id_from1 = l_task_id and
351 OBJECT_TYPE_FROM = 'PA_TASKS' and
352 relationship_type = 'H';
353 --
354 CURSOR get_all_tasks_for_project(l_project_id NUMBER) IS
355 select
356 object_id_from1, object_relationship_id
357 from
358 pa_object_relationships
359 where
360 (object_id_to1 = l_project_id and
361 relationship_type = 'H')
362 or
363 (object_id_from2 = l_project_id and
364 relationship_type = 'H');
365 --
366 CURSOR get_selected_relationship(l_task_id NUMBER, l_project_id NUMBER) IS
367 select
368 object_relationship_id
369 from
370 pa_object_relationships
371 where
372 object_type_to = 'PA_PROJECTS' and
373 object_type_from = 'PA_TASKS' and
374 object_id_to1 = l_project_id and
375 object_id_from1 = l_task_id and
376 relationship_type = 'H';
377 --
378 --
379 CURSOR get_src_task_det(cp_src_task_id NUMBER) IS
380 SELECT ppev.element_version_id src_task_ver_id,ppe.project_id src_proj_id
381 FROM pa_proj_elements ppe,
382 pa_proj_element_versions ppev,
383 pa_tasks pt
384 WHERE ppe.proj_element_id = cp_src_task_id
385 AND ppe.project_id = ppev.project_id
386 AND ppe.proj_element_id = ppev.proj_element_id
387 AND pt.task_id = ppe.proj_element_id
388 AND ppev.parent_structure_version_id =
389 PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID(ppev.project_id); -- Added for Bug 5687144
390 --
391 --This is cursor is used when source task id and dest project id are passed to this api.
392 --This cursor returns subproject association relationships ids for the
393 --given source task id and destination project id.
394 --
395 CURSOR get_relationship_det1(cp_src_task_ver_id NUMBER,
396 cp_src_proj_id NUMBER,
397 cp_dest_proj_id NUMBER) IS
398 SELECT porb.object_relationship_id lnk_obj_rel_id,
399 porb.record_version_number lnk_rel_rec_ver_number
400 FROM pa_proj_element_versions ppev,
401 pa_object_relationships pora,
402 pa_object_relationships porb,
403 pa_proj_elements ppe
404 WHERE pora.relationship_type = 'S'
405 AND ppev.project_id = cp_src_proj_id
406 AND pora.OBJECT_ID_FROM1 = cp_src_task_ver_id
407 AND pora.object_type_from = 'PA_TASKS'
408 AND pora.OBJECT_ID_to1 = ppev.ELEMENT_VERSION_ID
409 AND ppe.proj_element_id = ppev.proj_element_id
410 AND pora.object_id_to1=porb.object_id_from1
411 AND porb.object_id_to2 <> porb.object_id_from2
412 AND porb.object_id_to2 = cp_dest_proj_id -- Added for Bug 5687144
413 AND porb.object_type_to = 'PA_STRUCTURES'
414 AND porb.relationship_type = 'LF'
415 AND ppe.link_task_flag = 'Y';
416 --
417 --This cursor is used when the destination project id is not passed to this api
418 --This cursor will return all the relationship ids that are going out of the
419 --source task version id.
420 CURSOR get_all_sub_proj_ass_det(cp_src_task_ver_id NUMBER,
421 cp_src_proj_id NUMBER) IS
422 SELECT porb.object_relationship_id lnk_obj_rel_id,
423 porb.record_version_number lnk_rel_rec_ver_number
424 FROM pa_proj_element_versions ppev,
425 pa_object_relationships pora,
426 pa_object_relationships porb,
427 pa_proj_elements ppe
428 WHERE pora.relationship_type = 'S'
429 AND ppev.project_id = cp_src_proj_id
430 AND pora.OBJECT_ID_FROM1 = cp_src_task_ver_id
431 AND pora.object_type_from = 'PA_TASKS'
432 AND pora.OBJECT_ID_to1 = ppev.ELEMENT_VERSION_ID
433 AND ppe.proj_element_id = ppev.proj_element_id
434 AND pora.object_id_to1=porb.object_id_from1
435 AND porb.object_id_to2 <> porb.object_id_from2
436 AND porb.object_id_from2 = cp_src_proj_id
437 AND porb.object_type_to = 'PA_STRUCTURES'
438 AND porb.object_type_from = 'PA_TASKS'
439 AND porb.relationship_type ='LF'
440 AND ppe.link_task_flag = 'Y';
441 --
442 --This is cursor is used when the source task id is not passed to this api
443 --Cursor will return all the incoming links to the financial structure
444 --on the p_project_id, which is destination project id.
445 CURSOR get_str_sub_proj_ass_det(cp_dest_struct_ver_id NUMBER,
446 cp_dest_proj_id NUMBER) IS
447 SELECT porb.object_relationship_id lnk_obj_rel_id,
448 porb.record_version_number lnk_rel_rec_ver_number
449 FROM pa_object_relationships porb
450 WHERE porb.OBJECT_ID_to1 = cp_dest_struct_ver_id
451 AND porb.object_id_from2 = cp_dest_proj_id
452 AND porb.object_id_to2 <> porb.object_id_from2
453 AND porb.object_type_to = 'PA_STRUCTURES'
454 AND porb.object_type_from = 'PA_TASKS'
455 AND porb.relationship_type ='LF';
456 --
457 l_rel_id NUMBER;
458 l_rec_ver_num NUMBER;
459 l_src_task_ver_id NUMBER;
460 l_src_proj_id NUMBER;
461 l_dest_proj_name VARCHAR2(50);
462 l_error_msg_code VARCHAR2(250);
463 l_dest_fin_str_ver_id NUMBER;
464 l_data VARCHAR2(250);
465 l_msg_count NUMBER;
466 l_msg_data VARCHAR2(250);
467 --
468 Begin
469 pa_debug.set_err_stack('DELETE_RELATIONSHIP');
470 x_return_status := FND_API.G_RET_STS_SUCCESS;
471 --
472 IF p_task_id IS NOT NULL THEN
473 OPEN get_src_task_det(p_task_id);
474 FETCH get_src_task_det INTO l_src_task_ver_id,l_src_proj_id;
475 IF get_src_task_det%NOTFOUND THEN
476 PA_UTILS.ADD_Message('PA','PA_STRUCT_CREATE_ERR');
477 x_return_status := FND_API.G_RET_STS_ERROR;
478 END IF;
479 CLOSE get_src_task_det;
480 END IF;
481 --
482 /* new */
483 IF (p_task_id IS NOT NULL) AND (p_project_id IS NOT NULL) THEN
484 --
485 --
486 OPEN get_relationship_det1(l_src_task_ver_id,l_src_proj_id,p_project_id);
487 FETCH get_relationship_det1 INTO l_rel_id,l_rec_ver_num;
488 IF get_relationship_det1%NOTFOUND THEN
489 PA_UTILS.ADD_Message('PA','PA_STRUCT_CREATE_ERR');
490 x_return_status := FND_API.G_RET_STS_ERROR;
491 END IF;
492 CLOSE get_relationship_det1;
493 --
494 --
495 --
496 /* check if any children has contract associated to it */
497 -- Get task owning project
498 /* select project_id
499 into l_project_from_id
500 from pa_tasks
501 where task_id = p_task_id;*/
502 --
503 /* IF (pa_install.is_product_installed('OKE')) THEN
504 OKE_PA_CHECKS_PUB.Disassociation_Allowed(p_api_version => 1.0,
505 x_msg_count => x_msg_count,
506 x_msg_data => x_msg_data,
507 x_return_status => x_return_status,
508 From_Project_ID => l_project_from_id,
509 From_Task_ID => p_task_id,
510 To_Project_ID => p_project_id,
511 X_Result => l_disassociation
512 );
513 ELSE
514 l_disassociation := 'Y';
515 END IF;*/ --Commented if block
516 l_disassociation := 'Y'; --Moved this line here from the above if block
517 --
518 If ('Y' = l_disassociation) THEN
519 /* disassociation allowed */
520 --
521 /*OPEN get_selected_relationship(p_task_id, p_project_id);
522 LOOP
523 FETCH get_selected_relationship INTO l_object_relationship_id;
524 EXIT WHEN get_selected_relationship%NOTFOUND;
525 BEGIN
526 pa_object_relationships_pkg.delete_row(
527 P_OBJECT_RELATIONSHIP_ID => l_object_relationship_id,
528 P_OBJECT_TYPE_FROM => null,
529 P_OBJECT_ID_FROM1 => p_task_id,
530 P_OBJECT_ID_FROM2 => null,
531 P_OBJECT_ID_FROM3 => null,
532 P_OBJECT_ID_FROM4 => null,
533 P_OBJECT_ID_FROM5 => null,
534 P_OBJECT_TYPE_TO => null,
535 P_OBJECT_ID_TO1 => p_project_id,
536 P_OBJECT_ID_TO2 => null,
537 P_OBJECT_ID_TO3 => null,
538 P_OBJECT_ID_TO4 => null,
539 P_OBJECT_ID_TO5 => null,
540 P_RECORD_VERSION_NUMBER => null,
541 P_PM_PRODUCT_CODE => null,
542 X_RETURN_STATUS => x_return_status
543 );
544 EXCEPTION
545 WHEN OTHERS THEN
546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
547 PA_UTILS.ADD_Message('PA','PA_STRUCT_DELETE_ERR');
548 END;
549 EXIT WHEN (x_return_status <> FND_API.G_RET_STS_SUCCESS);
550 END LOOP;*/ --Commented the code to implement new functionality for subproject association
551 PA_RELATIONSHIP_PVT.Delete_SubProject_Association(
552 p_commit => p_commit
553 ,p_validate_only => p_validate_only
554 ,p_debug_mode => p_debug_mode
555 ,p_object_relationships_id => l_rel_id
556 ,p_record_version_number => l_rec_ver_num
557 ,x_return_status => x_return_status
558 ,x_msg_count => x_msg_count
559 ,x_msg_data => x_msg_data);
560
561 ELSE
562 /* Can't delete relationship due to other contracts*/
563 x_return_status := FND_API.G_RET_STS_ERROR;
564 PA_UTILS.ADD_Message('PA','PA_STRUCT_HAS_SUB_CONT');
565 END IF;
566 ELSIF (p_task_id IS NOT NULL) AND (p_project_id IS NULL) THEN
567 /* If task_id is not null, then delete all relationships that the task is associated with */
568 -- Get task owning project
569 select project_id
570 into l_project_from_id
571 from pa_tasks
572 where task_id = p_task_id;
573 --
574 OPEN get_all_sub_proj_ass_det(l_src_task_ver_id,l_src_proj_id);
575 LOOP
576 l_rec_ver_num:=NULL;
577 l_rel_id:=NULL;
578 FETCH get_all_sub_proj_ass_det INTO l_rel_id,l_rec_ver_num;
579 EXIT WHEN get_all_sub_proj_ass_det%NOTFOUND;
580 --
581 -- OPEN get_all_projects_for_task(p_task_id);
582 -- LOOP
583 -- FETCH get_all_projects_for_task INTO l_project_id, l_object_relationship_id;
584 -- EXIT WHEN get_all_projects_for_task%NOTFOUND;
585 --
586 /* check if this relationship can be removed */
587 /*IF (pa_install.is_product_installed('OKE')) THEN
588 OKE_PA_CHECKS_PUB.Disassociation_Allowed(p_api_version => 1.0,
589 x_msg_count => x_msg_count,
590 x_msg_data => x_msg_data,
591 x_return_status => x_return_status,
592 From_Project_ID => l_project_from_id,
593 From_Task_ID => p_task_id,
594 To_Project_ID => l_project_id,
595 X_Result => l_disassociation
596 );
597 ELSE
598 l_disassociation := 'Y';
599 END IF;*/ --Commented if block
600 l_disassociation := 'Y'; --Moved this line here from the above if block
601 if (l_disassociation = 'N') THEN
602 x_return_status := FND_API.G_RET_STS_ERROR;
603 PA_UTILS.ADD_Message('PA','PA_STRUCT_HAS_SUB_CONT');
604 end if;
605 --EXIT when l_disassociation = 'N';
606 --
607 -- call table handler api
608 BEGIN
609 /*pa_object_relationships_pkg.delete_row(
610 P_OBJECT_RELATIONSHIP_ID => l_object_relationship_id,
611 P_OBJECT_TYPE_FROM => null,
612 P_OBJECT_ID_FROM1 => p_task_id,
613 P_OBJECT_ID_FROM2 => null,
614 P_OBJECT_ID_FROM3 => null,
615 P_OBJECT_ID_FROM4 => null,
616 P_OBJECT_ID_FROM5 => null,
617 P_OBJECT_TYPE_TO => null,
618 P_OBJECT_ID_TO1 => l_project_id,
619 P_OBJECT_ID_TO2 => null,
620 P_OBJECT_ID_TO3 => null,
621 P_OBJECT_ID_TO4 => null,
622 P_OBJECT_ID_TO5 => null,
623 P_RECORD_VERSION_NUMBER => null,
624 P_PM_PRODUCT_CODE => null,
625 X_RETURN_STATUS => x_return_status
626 );*/ --Commented the code to implement new functionality for subproject association
627 --
628 PA_RELATIONSHIP_PVT.Delete_SubProject_Association(
629 p_commit => p_commit
630 ,p_validate_only => p_validate_only
631 ,p_debug_mode => p_debug_mode
632 ,p_object_relationships_id => l_rel_id
633 ,p_record_version_number => l_rec_ver_num
634 ,x_return_status => x_return_status
635 ,x_msg_count => x_msg_count
636 ,x_msg_data => x_msg_data);
637 EXCEPTION
638 WHEN OTHERS THEN
639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
640 PA_UTILS.ADD_Message('PA','PA_STRUCT_DELETE_ERR');
641 END;
642 --EXIT WHEN (x_return_status <> FND_API.G_RET_STS_SUCCESS);
643
644 END LOOP;
645 -- CLOSE get_all_projects_for_task;
646 CLOSE get_all_sub_proj_ass_det;
647 ELSIF (p_project_id IS NOT NULL) AND (p_task_id IS NULL) THEN
648 --
649 l_dest_fin_str_ver_id:=PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID(p_project_id);
650 IF l_dest_fin_str_ver_id IS NULL THEN
651 --get only version for fin
652 PA_PROJECT_STRUCTURE_UTILS.Get_Financial_Version(p_project_id,l_dest_fin_str_ver_id);
653 END IF;
654 --
655 /* If project_id is not null, then delete all relationships that the project is associated with */
656
657 -- OPEN get_all_tasks_for_project(p_project_id);
658 -- LOOP
659 -- FETCH get_all_tasks_for_project INTO l_task_id, l_object_relationship_id;
660 -- EXIT WHEN get_all_tasks_for_project%NOTFOUND;
661 OPEN get_str_sub_proj_ass_det(l_dest_fin_str_ver_id,
662 p_project_id);
663 LOOP
664 l_rec_ver_num:=NULL;
665 l_rel_id:=NULL;
666 FETCH get_str_sub_proj_ass_det INTO l_rel_id,l_rec_ver_num;
667 EXIT WHEN get_str_sub_proj_ass_det%NOTFOUND;
668 /* do not need to check subproject relationship because delete task */
669 /* will check them. do not need to check current project relationship */
670 /* with other tasks because we check if contract is associated to it */
671 /* call table handler api */
672 BEGIN
673 /*pa_object_relationships_pkg.delete_row(
674 P_OBJECT_RELATIONSHIP_ID => l_object_relationship_id,
675 P_OBJECT_TYPE_FROM => null,
676 P_OBJECT_ID_FROM1 => l_task_id,
677 P_OBJECT_ID_FROM2 => null,
678 P_OBJECT_ID_FROM3 => null,
679 P_OBJECT_ID_FROM4 => null,
680 P_OBJECT_ID_FROM5 => null,
681 P_OBJECT_TYPE_TO => null,
682 P_OBJECT_ID_TO1 => p_project_id,
683 P_OBJECT_ID_TO2 => null,
684 P_OBJECT_ID_TO3 => null,
685 P_OBJECT_ID_TO4 => null,
686 P_OBJECT_ID_TO5 => null,
687 P_RECORD_VERSION_NUMBER => null,
688 P_PM_PRODUCT_CODE => null,
689 X_RETURN_STATUS => x_return_status
690 );*/ --Commented the code to implement new functionality for subproject association
691 PA_RELATIONSHIP_PVT.Delete_SubProject_Association(
692 p_commit => p_commit
693 ,p_validate_only => p_validate_only
694 ,p_debug_mode => p_debug_mode
695 ,p_object_relationships_id => l_rel_id
696 ,p_record_version_number => l_rec_ver_num
697 ,x_return_status => x_return_status
698 ,x_msg_count => x_msg_count
699 ,x_msg_data => x_msg_data);
700 --
701 EXCEPTION
702 WHEN OTHERS THEN
703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
704 PA_UTILS.ADD_Message('PA','PA_STRUCT_DELETE_ERR');
705 END;
706 EXIT WHEN (x_return_status <> FND_API.G_RET_STS_SUCCESS);
707 END LOOP;
708 -- CLOSE get_all_tasks_for_project;
709 CLOSE get_str_sub_proj_ass_det; --SMukka Added
710 ELSE
711 x_return_status := FND_API.G_RET_STS_ERROR;
712 PA_UTILS.ADD_Message('PA','PA_STRUCT_DELETE_ERR');
713 END IF;
714 --
715 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
716 x_return_status := FND_API.G_RET_STS_ERROR;
717 END IF;
718 --
719 x_msg_count := FND_MSG_PUB.Count_Msg;
720 IF (x_msg_count = 1) THEN
721 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
722 ,p_msg_index => 1
723 ,p_data => x_msg_data
724 ,p_msg_index_out => l_msg_index_out
725 );
726 ELSE
727 x_msg_data := null;
728 END IF;
729 pa_debug.reset_err_stack;
730 EXCEPTION
731 When OTHERS Then
732 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
733 Raise;
734 END DELETE_RELATIONSHIP;
735
736
737
738 end PA_PROJ_STRUCTURE_PVT;
739