1 package body PA_RELATIONSHIP_UTILS as
2 /*$Header: PAXRELUB.pls 120.19.12010000.3 2009/06/22 09:11:32 paljain ship $*/
3
4 -- API name : Check_Create_Link_Ok
5 -- Type : Private Procedure
6 -- Pre-reqs : None
7 -- Return Value : S if ok
8 -- E if error.
9 -- Parameters
10 -- p_element_version_id_from IN NUMBER
11 -- p_element_version_id_to IN NUMBER
12 -- x_return_status OUT VARCHAR2
13 -- x_error_message_code OUT VARCHAR2
14 --
15 --
16 -- History
17 --
18 -- 24-JAN-02 HSIU -Modified
19 -- Added logic for linking within project from
20 -- costing to workplan structure in
21 -- check_create_link_ok api.
22 -- 19-DEC-01 HSIU -Modified
23 -- Sutask is always created if task can be created.
24 -- 25-JUN-01 HSIU -Created
25 --
26 --
27
28
29 procedure Check_Create_Link_Ok
30 (
31 p_element_version_id_from IN NUMBER
32 ,p_element_version_id_to IN NUMBER
33 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
34 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
35 )
36 IS
37 l_create_new_task VARCHAR2(1);
38 l_dummy VARCHAR2(1);
39 l_err_code NUMBER := 0;
40 l_err_stack VARCHAR2(630);
41 l_err_stage VARCHAR2(80);
42 l_element_id NUMBER;
43 l_object_type VARCHAR2(30);
44
45 l_project_id_from NUMBER;
46 l_project_id_to NUMBER;
47
48 cursor Get_Element_Id(c_element_version_id NUMBER) IS
49 select proj_element_id, object_type
50 from pa_proj_element_versions
51 where element_version_id = c_element_version_id;
52
53 cursor Is_linked(c_element_version_id NUMBER) IS
54 select '1'
55 from pa_object_relationships
56 where object_id_from1 = c_element_version_id
57 and object_type_from = 'PA_TASKS'
58 and relationship_type = 'L';
59
60 cursor Is_structure(c_element_version_id NUMBER) IS
61 select '1'
62 from pa_object_relationships
63 where object_id_from1 = c_element_version_id
64 and object_type_from = 'PA_STRUCTURES';
65
66 cursor Get_Struc_Ver_Id(c_element_version_id NUMBER) IS
67 select parent_structure_version_id, project_id
68 from pa_proj_element_versions
69 where element_version_id = c_element_version_id;
70 l_from_struc_ver_id NUMBER;
71
72 cursor Check_PA_TASKS_Exists(c_task_id NUMBER) IS
73 select '1'
74 from PA_TASKS
75 where task_id = c_task_id;
76
77 cursor Is_Same_Struc(c_elem_ver_from NUMBER, c_elem_ver_to NUMBER) IS
78 select '1'
79 from pa_proj_elements pe1,
80 pa_proj_element_versions pev1a,
81 pa_proj_element_versions pev1b,
82 pa_proj_element_versions pev2a,
83 pa_proj_element_versions pev2b
84 where pev1b.element_version_id = c_elem_ver_from
85 and pev1b.parent_structure_version_id = pev1a.element_version_id
86 and pev1a.proj_element_id = pe1.proj_element_id
87 and pev2b.element_version_id = c_elem_ver_to
88 and pev2b.parent_structure_version_id = pev2a.element_version_id
89 and pev2a.proj_element_id = pe1.proj_element_id;
90
91 /* Bug 2680486 -- Performance changes -- Commented the following cursor definition. Restructured it to
92 avoid Non-mergable view issue and use EXISTS rather than IN */
93
94 /* cursor Is_Diff_Version_Linked(c_elem_ver_from NUMBER, c_elem_ver_to NUMBER) IS
95 select '1'
96 from pa_proj_elements pe1,
97 pa_proj_element_versions pev1a,
98 pa_proj_element_versions pev1b,
99 pa_object_relationships r
100 where pev1a.element_version_id = c_elem_ver_from
101 and pev1a.proj_element_id = pe1.proj_element_id
102 and pe1.project_id = pev1b.project_id
103 and pe1.proj_element_id = pev1b.proj_element_id
104 and pev1b.element_version_id = r.object_id_to1
105 and r.object_id_from1 IN
106 (select pev2b.element_version_id
107 from pa_proj_elements pe2,
108 pa_proj_element_versions pev2a,
109 pa_proj_element_versions pev2b
110 where pev2a.element_version_id = c_elem_ver_to
111 and pev2a.proj_element_id = pe2.proj_element_id
112 and pe2.project_id = pev2b.project_id
113 and pe2.proj_element_id = pev2b.proj_element_id);
114 */
115
116 cursor Is_Diff_Version_Linked(c_elem_ver_from NUMBER, c_elem_ver_to NUMBER) IS
117 select '1'
118 from pa_proj_elements pe1,
119 pa_proj_element_versions pev1a,
120 pa_proj_element_versions pev1b,
121 pa_object_relationships r
122 where pev1a.element_version_id = c_elem_ver_from
123 and pev1a.proj_element_id = pe1.proj_element_id
124 and pe1.project_id = pev1b.project_id
125 and pe1.proj_element_id = pev1b.proj_element_id
126 and pev1b.element_version_id = r.object_id_to1
127 and EXISTS
128 (select pev2b.element_version_id
129 from pa_proj_elements pe2,
130 pa_proj_element_versions pev2a,
131 pa_proj_element_versions pev2b
132 where pev2a.element_version_id = c_elem_ver_to
133 and pev2a.proj_element_id = pe2.proj_element_id
134 and pe2.project_id = pev2b.project_id
135 and pe2.proj_element_id = pev2b.proj_element_id
136 and r.object_id_from1 = pev2b.element_version_id);
137
138 cursor Is_Circular_Link(c_from NUMBER, c_to NUMBER) IS
139 select '1'
140 from pa_proj_element_versions a,
141 pa_proj_element_versions b
142 where a.element_version_id = c_from
143 and a.proj_element_id = b.proj_element_id
144 and a.project_id = b.project_id
145 and b.element_version_id IN (
146 select object_id_to1
147 from pa_object_relationships
148 start with object_id_from1 IN (
149 select b.element_version_id
150 from pa_proj_element_versions a,
151 pa_proj_element_versions b
152 where a.element_version_id = c_to
153 and a.proj_element_id = b.proj_element_id
154 and a.project_id = b.project_id
155 )
156 and object_type_from IN ('PA_TASKS','PA_STRUCTURES')
157 and object_type_to IN ('PA_TASKS','PA_STRUCTURES')
158 and relationship_type IN ('S','L')
159 connect by prior object_id_to1 = object_id_from1
160 and object_type_from IN ('PA_TASKS','PA_STRUCTURES')
161 and prior object_type_to IN ('PA_TASKS','PA_STRUCTURES')
162 and prior relationship_type IN ('S','L')
163 );
164
165
166 cursor Get_Top_Nodes(c_element_version_id NUMBER) IS
167 select a.object_id_from1
168 from pa_object_relationships a
169 where NOT EXISTS (select '1' from pa_object_relationships b
170 where b.object_id_to1 = a.object_id_from1)
171 start with a.object_id_to1 = c_element_version_id
172 and a.object_type_to IN ('PA_STRUCTURES','PA_TASKS')
173 connect by prior a.object_id_from1 = a.object_id_to1
174 and a.relationship_type IN ('S','L')
175 union
176 select a.object_id_from1
177 from pa_object_relationships a
178 where a.object_id_from1 = c_element_version_id
179 and object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
180 and relationship_type = 'S';
181 l_top_node_id NUMBER;
182
183 cursor Is_Version_Exist(c_top_node_id NUMBER, c_linking_node_id NUMBER) IS
184 select object_id_to1
185 from pa_object_relationships
186 where relationship_type IN ('S', 'L')
187 start with object_id_from1 = c_top_node_id
188 and object_type_from IN ('PA_STRUCTURES','PA_TASKS')
189 connect by object_id_from1 = prior object_id_to1
190 and relationship_type IN ('L','S')
191 intersect
192 (
193 select pev1b.element_version_id
194 from pa_proj_element_versions pev1b,
195 pa_proj_elements pe1,
196 pa_proj_element_versions pev1a
197 where pev1b.project_id = pe1.project_id
198 and pev1b.proj_element_id = pe1.proj_element_id
199 and pev1a.proj_element_id = pe1.proj_element_id
200 and pev1a.element_version_id IN
201 ( select object_id_to1
202 from pa_object_relationships
203 where relationship_type IN ('S','L')
204 start with object_id_from1 = c_linking_node_id
205 and object_type_from IN ('PA_STRUCTURES','PA_TASKS')
206 connect by object_id_from1 = prior object_id_to1
207 and relationship_type IN('L','S')
208 -- UNION
209 -- select object_id_from1
210 -- from pa_object_relationships
211 -- where relationship_type IN ('S','L')
212 -- start with object_id_to1 = c_linking_node_id
213 -- and object_type_to IN ('PA_STRUCTURES','PA_TASKS')
214 -- connect by prior object_id_from1 = object_id_to1
215 -- and relationship_type IN ('S','L')
216 UNION
217 select element_version_id
218 from pa_proj_element_versions
219 where element_version_id = c_linking_node_id
220 )
221 );
222 l_existing_elem_ver_id NUMBER;
223
224 l_struc_ver_id_from NUMBER;
225 l_struc_ver_id_to NUMBER;
226
227 l_workplan_from VARCHAR2(1);
228 l_workplan_to VARCHAR2(1);
229 l_financial_from VARCHAR2(1);
230 l_financial_to VARCHAR2(1);
231 BEGIN
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233
234 --1 Check if need to create new task.
235
236 --1a. check if link exist for this task
237 -- if yes, error.
238 -- If (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
239 OPEN Is_linked(p_element_version_id_from);
240 FETCH Is_linked into l_dummy;
241 IF Is_linked%FOUND THEN
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 x_error_message_code := 'PA_PS_LINK_EXISTS';
244 CLOSE Is_linked;
245 return;
246 END IF;
247 CLOSE Is_linked;
248 -- END IF;
249
250 --Removed, since subtask is always created now.
251 --1b. check if the from element version is a structure
252 -- if yes, create new task.
253 -- IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
254 -- OPEN Is_structure(p_element_version_id_from);
255 -- FETCH Is_structure into l_dummy;
256 -- IF Is_structure%FOUND THEN
257 -- x_return_status := 'T';
258 -- END IF;
259 -- CLOSE Is_structure;
260 -- END IF;
261
262 -- IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
263 --1c. check if from object is lowest task.
264 -- if no, create new task.
265
266 -- IF (PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(p_element_version_id_from) = 'N') THEN
267 -- --check if there is transaction to this task.
268 -- --if transaction exists, return error.
269 -- x_return_status := 'T';
270 -- ELSE
271
272 --1d. Lowest task. Check if lowest task has transaction
273 -- if yes, error.
274 --Get structure version id
275
276 OPEN Get_Struc_Ver_Id(p_element_version_id_from);
277 FETCH Get_Struc_Ver_id into l_from_struc_ver_id, l_project_id_from;
278 CLOSE Get_Struc_Ver_id;
279 OPEN Get_Element_Id(p_element_version_id_from);
280 FETCH Get_Element_Id into l_element_id, l_object_type;
281 CLOSE Get_Element_Id;
282 --Check if it has costing/billing structure type.
283 --changed to financial
284 --dbms_output.put_line('l_element_id = '||l_element_id);
285
286 If (PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(
287 l_from_struc_ver_id, 'FINANCIAL') = 'Y') THEN
288 --Check for transaction for this task
289
290
291 IF (l_object_type = 'PA_TASKS') THEN
292 --Bug 2183974
293 --Check if this task is valid in PA_TASKS first
294 OPEN Check_PA_TASKS_Exists(l_element_id);
295 FETCH Check_PA_TASKS_Exists into l_dummy;
296 IF Check_PA_TASKS_Exists%NOTFOUND THEN
297 CLOSE Check_PA_TASKS_Exists;
298 x_error_message_code := 'PA_PS_PA_TASKS_NOT_EXISTS';
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 return;
301 ELSE
302 --Task exists
303 CLOSE Check_PA_TASKS_Exists;
304 PA_TASK_UTILS.CHECK_CREATE_SUBTASK_OK(x_task_id => l_element_id,
305 x_err_code => l_err_code,
306 x_err_stack => l_err_stack,
310
307 x_err_stage => l_err_stage
308 );
309 IF (l_err_code <> 0) THEN
311 --There is transaction, error.
312 x_error_message_code := substrb(l_err_stage,0,30); -- 4537865 : Changed substr usage to substrb
313 x_return_status := FND_API.G_RET_STS_ERROR;
314 return;
315 END IF;
316 END IF;
317 END IF;
318 END IF;
319 -- END IF;
320 -- END IF;
321
322 --Check if ok to link elements from two different structures (structure types)
323 -- Get From Structure Version Id
324 OPEN get_struc_ver_id(p_element_version_id_from);
325 FETCH get_struc_ver_id into l_struc_ver_id_from, l_project_id_from;
326 CLOSE get_struc_ver_id;
327 l_financial_from := PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(
328 l_struc_ver_id_from,
329 'FINANCIAL');
330 l_workplan_from := PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(
331 l_struc_ver_id_from,
332 'WORKPLAN');
333
334 -- Get To Structure Version Id
335 OPEN get_struc_ver_id(p_element_version_id_to);
336 FETCH get_struc_ver_id into l_struc_ver_id_to, l_project_id_to;
337 CLOSE get_struc_ver_id;
338 l_financial_to := PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(
339 l_struc_ver_id_to,
340 'FINANCIAL');
341 l_workplan_to := PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(
342 l_struc_ver_id_to,
343 'WORKPLAN');
344 --Compare structure types
345 If (l_workplan_from = 'Y') and
346 (l_financial_from = 'N') and
347 (l_workplan_to = 'N') and
348 (l_financial_to = 'Y') THEN
349 x_return_status := FND_API.G_RET_STS_ERROR;
350 x_error_message_code := 'PA_PS_LINK_WP_TO_FIN_ERR';
351 return;
352 END IF;
353
354 If (l_financial_from = 'Y') and
355 (l_workplan_to = 'Y') and
356 (l_financial_to = 'N') THEN
357 If (l_workplan_from = 'N') THEN
358 IF (l_project_id_from <> l_project_id_to) THEN
359 x_return_status := FND_API.G_RET_STS_ERROR;
360 x_error_message_code := 'PA_PS_LINK_FIN_TO_WP_ERR';
361 return;
362 END IF;
363 ELSE
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 x_error_message_code := 'PA_PS_LINK_FIN_TO_WP_ERR';
366 return;
367 END IF;
368 END IF;
369
370 --dbms_output.put_line('checking linking within program:'||p_element_version_id_from||','||p_element_version_id_to);
371 --Check if linking within structure
372 OPEN Is_Same_Struc(p_element_version_id_from, p_element_version_id_to);
373 FETCH Is_Same_Struc into l_dummy;
374 If Is_Same_Struc%FOUND THEN
375 CLOSE Is_Same_Struc;
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 x_error_message_code := 'PA_PS_LINK_WITHIN_STRUCTURE';
378 return;
379 END IF;
380 CLOSE Is_Same_Struc;
381 --dbms_output.put_line('done checking linking within program');
382
383 --Check if a version of object A is linked to object B, not any version of object B
384 -- should be linked to any version of object A.
385 --dbms_output.put_line('checking linking versions');
386 OPEN Is_Diff_Version_Linked(p_element_version_id_from, p_element_version_id_to);
387 FETCH Is_Diff_Version_Linked into l_dummy;
388 IF Is_Diff_Version_Linked%FOUND THEN
389 CLOSE Is_Diff_Version_Linked;
390 x_return_status := FND_API.G_RET_STS_ERROR;
391 x_error_message_code := 'PA_PS_DIFF_VER_LINKED';
392 return;
393 END IF;
394 CLOSE Is_Diff_Version_Linked;
395
396 OPEN Is_Circular_Link(p_element_version_id_from, p_element_version_id_to);
397 FETCH Is_Circular_Link into l_dummy;
398 IF Is_Circular_Link%FOUND THEN
399 CLOSE Is_Circular_Link;
400 x_return_status := FND_API.G_RET_STS_ERROR;
401 x_error_message_code := 'PA_PS_DIFF_VER_LINKED';
402 return;
403 END IF;
404 CLOSE Is_Circular_Link;
405 --dbms_output.put_line('done checking linking versions');
406
407 --Check if any linking objects or its versions exist in the hierarchy.
408 -- needs to be enhanced in the future.
409 --dbms_output.put_line('Top node from => '||p_element_version_id_from);
410 OPEN Get_Top_Nodes(p_element_version_id_from);
411 LOOP
412 FETCH Get_Top_Nodes INTO l_top_node_id;
413 --dbms_output.put_line('top node = '||l_top_node_id);
414 EXIT WHEN Get_Top_Nodes%NOTFOUND;
415
416 --dbms_output.put_line('checking anything/version exists in hierarchy');
417 OPEN Is_Version_Exist(l_top_node_id, p_element_version_id_to);
418 FETCH Is_Version_Exist into l_existing_elem_ver_id;
419 IF Is_Version_Exist%FOUND THEN
420 CLOSE Is_Version_Exist;
421 CLOSE Get_Top_Nodes;
422 x_return_status := FND_API.G_RET_STS_ERROR;
423 x_error_message_code := 'PA_PS_LINK_ELEM_EX_IN_HIER';
424 return;
425 END IF;
426 CLOSE Is_Version_Exist;
427 --dbms_output.put_line('done checking anything/version exists in hierarchy');
428 END LOOP;
429 CLOSE Get_Top_Nodes;
430
431 -- 4537865
432 EXCEPTION
433 WHEN OTHERS THEN
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
435 x_error_message_code := SQLCODE ;
436
437 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIP_UTILS',
438 p_procedure_name => 'CHECK_CREATE_LINK_OK',
439 p_error_text => SUBSTRB(SQLERRM,1,240));
440 RAISE;
441 END CHECK_CREATE_LINK_OK;
442
443 -- API name : Check_Create_Dependency_Ok
444 -- Type : Private Procedure
445 -- Pre-reqs : None
446 -- Return Value : N/A
447 -- Parameters
448 -- p_element_version_id_from IN NUMBER
449 -- p_element_version_id_to IN NUMBER
450 -- x_return_status OUT VARCHAR2
451 -- x_error_message_code OUT VARCHAR2
452 --
453 --
454 -- History
455 --
456 -- 25-JUN-01 HSIU -Created
457 --
458 --
459
460
461 procedure Check_Create_Dependency_Ok
462 (
463 p_element_version_id_from IN NUMBER
464 ,p_element_version_id_to IN NUMBER
465 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
466 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
467 )
468 IS
469 BEGIN
470 x_return_status := FND_API.G_RET_STS_SUCCESS;
471 END CHECK_CREATE_DEPENDENCY_OK;
472
473
474 -- API name : Check_Create_Association_Ok
475 -- Type : Private Procedure
476 -- Pre-reqs : None
477 -- Return Value : N/A
478 -- Parameters
479 -- p_element_version_id_from IN NUMBER
480 -- p_element_version_id_to IN NUMBER
481 -- x_return_status OUT VARCHAR2
482 -- x_error_message_code OUT VARCHAR2
483 --
484 --
485 -- History
486 --
487 -- 25-JUN-01 HSIU -Created
488 --
489 --
490
491
492 procedure Check_Create_Association_Ok
493 (
494 p_element_version_id_from IN NUMBER
495 ,p_element_version_id_to IN NUMBER
496 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
497 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
498 )
499 IS
500 BEGIN
501 x_return_status := FND_API.G_RET_STS_SUCCESS;
502 END CHECK_CREATE_ASSOCIATION_OK;
503
504
505 -- API name : parent_LP_link_exists
506 -- Type : Private Function
507 -- Pre-reqs : None
508 -- Return Value : Y or N
509 -- Parameters
510 -- p_parent_project_id IN NUMBER
511 -- p_sub_project_id IN NUMBER
512 --
513 --
514 -- History
515 --
516 -- 05-DEC-03 Maansari -Created
517 --
518 -- Description
519 --
520 -- This fucntion returns 'Y' if there exists a Link from parent latest published version to the
521 -- subproject. This is used in view PA_STRUCTURES_LINKS_V to select working version if there is no
522 -- link from the parent latest published version to the subproject.
523 --
524 --
525
526
527 Function parent_LP_link_exists
528 (
529 p_parent_project_id IN NUMBER
530 ,p_sub_project_id IN NUMBER
531 ) RETURN VARCHAR2 IS
532
533 CURSOR cur_parent_lp_link
534 IS
535 SELECT 'Y'
536 FROM pa_proj_elements ppe
537 ,pa_proj_element_versions ppv1 /* to get link task version id */
538 ,pa_proj_element_versions ppv2 /* to get sub project structure version ids */
539 ,pa_object_relationships por
540 WHERE ppe.project_id = p_parent_project_id
541 AND ppe.link_task_flag = 'Y'
542 AND ppe.project_id = ppv1.project_id
543 AND ppe.proj_element_id = ppv1.proj_element_id
544 AND ppv1.parent_structure_version_id IN ( SELECT ppevs.element_version_id
545 FROM pa_proj_elem_ver_structure ppevs
546 WHERE ppevs.project_id = p_parent_project_id
547 AND ppevs.status_code = 'STRUCTURE_PUBLISHED'
548 AND ppevs.latest_eff_published_flag = 'Y' )
549 AND ppv2.project_id = p_sub_project_id
550 AND ppv2.object_type = 'PA_STRUCTURES'
551 AND ppv1.element_version_id = por.object_id_from1
552 AND por.relationship_type in ( 'LW', 'LF' ) -- ( 'WL', 'FL' ) -- Bug # 4760126.
553 AND ppv2.element_version_id = por.object_id_to1
554 AND object_type_from = 'PA_TASKS' --Bug 6429264
555 AND object_type_to = 'PA_STRUCTURES' --Bug 6429264
556 ;
557 l_return_value VARCHAR2(1) := 'N';
558 BEGIN
559
560 OPEN cur_parent_lp_link;
561 FETCH cur_parent_lp_link INTO l_return_value;
562 CLOSE cur_parent_lp_link;
563
564 RETURN l_return_value;
565 END parent_LP_link_exists;
566
567 -- API name : check_create_intra_dep_ok
568 -- Type : Private Check procedure
569 -- Pre-reqs : None
570 -- Return Value : Returns error status
571 -- Parameters
572 -- p_pre_project_id IN NUMBER
573 -- p_pre_task_ver_id IN NUMBER
574 -- p_project_id IN NUMBER
575 -- p_task_ver_id IN NUMBER
576 --
577 --
578 -- History
579 --
580 -- 19-DEC-03 Maansari -Created
581 --
582 -- Description
583 --
584 -- This check procedure check s the following business rules and returns status 'E' with proper
585 -- error message if any of the rules fails.
586 --a. No duplicates.
587 --b. No circular dependencies between two or more tasks.
588 --c. A task cannot depend on itself.
589 --d. You cannot create a dependency from an object (predecessor) to you (successor) if that object has
590 -- subtasks (successor) that depend on you (predecessor).
591 --e. You cannot create a dependency between objects that are in the same direct path from lowest
592 -- node to the top node.
593 --
594 -- Notes: The p_pre_<> paramaters are for predecessor tasks and stored in object_id_to1 colunmn
595 -- of pa_object_relationships.
596
597 procedure check_create_intra_dep_ok(
598 p_pre_project_id IN NUMBER
599 ,p_pre_task_ver_id IN NUMBER
600 ,p_project_id IN NUMBER
601 ,p_task_ver_id IN NUMBER
602 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
603 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
604 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
605 ) IS
606
607 CURSOR cur_a
608 IS
609 SELECT 'x'
610 FROM pa_object_relationships
611 WHERE object_id_from1 = p_task_ver_id
612 AND object_id_from2 = p_project_id
613 AND object_id_to1 = p_pre_task_ver_id
614 AND object_id_to2 = p_pre_project_id
615 AND relationship_type = 'D'
616 ;
617
618 CURSOR cur_b
619 IS
620 SELECT 'x'
621 FROM (
622 SELECT object_id_to1
623 FROM pa_object_relationships por2
624 WHERE relationship_type = 'D'
625 AND por2.object_id_from2 = por2.object_id_to2 --Bug 3629024
626 START WITH por2.object_id_from1 = p_pre_task_ver_id
627 AND relationship_type = 'D' --bug 3944567
628 CONNECT BY por2.object_id_from1 = PRIOR por2.object_id_to1
629 AND relationship_type = PRIOR relationship_type
630 AND relationship_type = 'D'
631 AND por2.object_id_from2 = PRIOR por2.object_id_from2 ) --Bug 3629024
632 where object_id_to1 = p_task_ver_id;
633
634 /* the successor is a prdecessor of the sub-tasks of the predecessor.*/
635 /*Commented out for bug 3629024
636 CURSOR cur_d
637 IS
638 SELECT 'x'
639 FROM pa_object_relationships por1
643 ( SELECT por2.object_id_to1
640 WHERE por1.relationship_type = 'D'
641 AND por1.object_id_to1 = p_task_ver_id
642 AND por1.object_id_from1 IN
644 FROM pa_object_relationships por2
645 START WITH por2.object_id_from1 = p_pre_task_ver_id
646 CONNECT BY por2.object_id_from1 = prior por2.object_id_to1
647 AND por2.relationship_type = prior por2.relationship_type
648 AND por2.relationship_type = 'S')
649 ;
650 */
651 CURSOR cur_e1_get_parent( c_child_task_ver_id NUMBER )
652 IS
653 SELECT object_id_from1
654 FROM pa_object_relationships
655 where object_id_to1 = c_child_task_ver_id
656 and relationship_type = 'S'
657 ;
658
659 l_dummy_char VARCHAR2(1);
660 l_child_task_ver_id NUMBER;
661 l_parent_task_ver_id NUMBER;
662 BEGIN
663
664 IF p_pre_project_id IS NULL OR
665 p_pre_task_ver_id IS NULL OR
666 p_project_id IS NULL OR
667 p_task_ver_id IS NULL
668 THEN
669 x_return_status := FND_API.G_RET_STS_ERROR;
670 x_msg_count := FND_MSG_PUB.count_msg;
671 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
672 ,p_msg_name => 'PA_PS_PARAMS_NULL');
673 raise FND_API.G_EXC_ERROR;
674 END IF;
675
676 IF p_pre_project_id <> p_project_id
677 THEN
678 x_return_status := FND_API.G_RET_STS_ERROR;
679 x_msg_count := FND_MSG_PUB.count_msg;
680 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
681 ,p_msg_name => 'PA_PS_NOT_INTRA_DEPND');
682 raise FND_API.G_EXC_ERROR;
683 END IF;
684
685 --c) a task cannot depend on it-self
686 IF p_pre_task_ver_id = p_task_ver_id
687 THEN
688 x_return_status := FND_API.G_RET_STS_ERROR;
689 x_msg_count := FND_MSG_PUB.count_msg;
690 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
691 ,p_msg_name => 'PA_PS_NO_SELF_DEPDN');
692 raise FND_API.G_EXC_ERROR;
693 END IF;
694
695 OPEN cur_a;
696 FETCH cur_a INTO l_dummy_char;
697 IF cur_a%FOUND
698 THEN
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 x_msg_count := FND_MSG_PUB.count_msg;
701 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
702 ,p_msg_name => 'PA_PS_DEPND_EXISTS');
703 raise FND_API.G_EXC_ERROR;
704 END IF;
705 CLOSE cur_a;
706
707 OPEN cur_b;
708 FETCH cur_b INTO l_dummy_char;
709 IF cur_b%FOUND
710 THEN
711 x_return_status := FND_API.G_RET_STS_ERROR;
712 x_msg_count := FND_MSG_PUB.count_msg;
713 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
714 ,p_msg_name => 'PA_PS_CIRCLR_DEPND_EXISTS');
715 raise FND_API.G_EXC_ERROR;
716 END IF;
717 CLOSE cur_b;
718
719 /* Following code commented out for bug 3629024
720 OPEN cur_d;
721 FETCH cur_d INTO l_dummy_char;
722 IF cur_d%FOUND
723 THEN
724 x_return_status := FND_API.G_RET_STS_ERROR;
725 x_msg_count := FND_MSG_PUB.count_msg;
726 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
727 ,p_msg_name => 'PA_PS_SUBTASKS_DEPND_EXISTS');
728 raise FND_API.G_EXC_ERROR;
729 END IF;
730 CLOSE cur_d;
731 */
732 --Bug 3629024 : Check for the existence of a closed path
733 --This bug fix is for rule D
734 IF get_parents_childs(p_pre_task_ver_id, p_task_ver_id) = TRUE THEN
735 x_return_status := FND_API.G_RET_STS_ERROR;
736 x_msg_count := FND_MSG_PUB.count_msg;
737 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
738 ,p_msg_name => 'PA_PS_SUBTASKS_DEPND_EXISTS');
739 raise FND_API.G_EXC_ERROR;
740 END IF;
741
742 /* --check if predecessor is parent in the same line of hierarchy.
743 OPEN cur_e1;
744 FETCH cur_e1 INTO l_dummy_char;
745 IF cur_e1%FOUND
746 THEN
747 x_return_status := FND_API.G_RET_STS_ERROR;
748 x_msg_count := FND_MSG_PUB.count_msg;
749 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
750 ,p_msg_name => 'PA_PS_NO_PARENT_PRED');
751 raise FND_API.G_EXC_ERROR;
752 END IF;
753 CLOSE cur_e1;
754
755 --check if predecessor is child in the same line of hierarchy.
756 OPEN cur_e2;
757 FETCH cur_e2 INTO l_dummy_char;
758 IF cur_e2%FOUND
759 THEN
760 x_return_status := FND_API.G_RET_STS_ERROR;
761 x_msg_count := FND_MSG_PUB.count_msg;
762 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
763 ,p_msg_name => 'PA_PS_NO_CHILD_PRED');
764 raise FND_API.G_EXC_ERROR;
765 END IF;
766 CLOSE cur_e2;
767 */
768
769 /* check for predecessor is parent */
770 l_child_task_ver_id := p_task_ver_id;
771 WHILE ( l_child_task_ver_id IS NOT NULL ) LOOP
772 OPEN cur_e1_get_parent( l_child_task_ver_id);
773 FETCH cur_e1_get_parent INTO l_parent_task_ver_id; /* predecessor is parent */
774 IF cur_e1_get_parent%NOTFOUND THEN
775 close cur_e1_get_parent;
776 exit;
777 END IF;
778 CLOSE cur_e1_get_parent;
779
783 x_return_status := FND_API.G_RET_STS_ERROR;
780 IF l_parent_task_ver_id IS NOT NULL AND
781 l_parent_task_ver_id = p_pre_task_ver_id
782 THEN
784 x_msg_count := FND_MSG_PUB.count_msg;
785 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
786 ,p_msg_name => 'PA_PS_NO_PARENT_PRED');
787 raise FND_API.G_EXC_ERROR;
788 ELSE
789 l_child_task_ver_id := l_parent_task_ver_id;
790 END IF;
791 END LOOP;
792
793
794 /* check for predecessor is child or successor is a parent */
795 /* starting from predecessor, find out the parent up the hierarchy and compare the
796 successor with the parent found. if successor is same as the parent found then it means
797 the predecessor is a child of the succeesor down the line in the hierarchy */
798 /* it is not possible to traverse down the hierarchy to find out whether the predecessor is a
799 child in the same line of successsor starting from the suceesor therefore traversing up
800 the hierarchy starting from predecessor*/
801
802 l_child_task_ver_id := p_pre_task_ver_id;
803 WHILE ( l_child_task_ver_id IS NOT NULL ) LOOP
804 OPEN cur_e1_get_parent( l_child_task_ver_id);
805 FETCH cur_e1_get_parent INTO l_parent_task_ver_id;
806 IF cur_e1_get_parent%NOTFOUND THEN
807 close cur_e1_get_parent;
808 exit;
809 END IF;
810 CLOSE cur_e1_get_parent;
811
812 IF l_parent_task_ver_id IS NOT NULL AND
813 l_parent_task_ver_id = p_task_ver_id /* is succssor a parent in the same line of hierarcgy*/
814 THEN
815 x_return_status := FND_API.G_RET_STS_ERROR;
816 x_msg_count := FND_MSG_PUB.count_msg;
817 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
818 ,p_msg_name => 'PA_PS_NO_CHILD_PRED');
819 raise FND_API.G_EXC_ERROR;
820 ELSE
821 l_child_task_ver_id := l_parent_task_ver_id;
822 END IF;
823 END LOOP;
824
825
826 EXCEPTION
827 when FND_API.G_EXC_ERROR then
828 x_return_status := FND_API.G_RET_STS_ERROR;
829 x_msg_count := FND_MSG_PUB.count_msg;
830 when OTHERS then
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
832 x_msg_count := 1; -- 4537865 : RESET OUT param
833 x_msg_data := SUBSTRB(SQLERRM,1,240); -- 4537865 : RESET OUT PARAM
834 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_UTILS',
835 p_procedure_name => 'check_create_intra_dep_ok',
836 p_error_text => x_msg_data ); -- 4537865
837 raise;
838
839 END check_create_intra_dep_ok;
840
841 -- API name : check_create_intra_dep_ok
842 -- Type : Private Check procedure
843 -- Pre-reqs : None
844 -- Return Value : Returns error status
845 -- Parameters
846 -- p_pre_project_id IN NUMBER
847 -- p_pre_task_ver_id IN NUMBER
848 -- p_project_id IN NUMBER
849 -- p_task_ver_id IN NUMBER
850 --
851 --
852 -- History
853 --
854 -- 19-DEC-03 Maansari -Created
855 --
856 -- Description
857 --
858 -- This check procedure check s the following business rules and returns status 'E' with proper
859 -- error message if any of the rules fails.
860 --a. No duplicates.
861 -- Notes: The p_pre_<> paramaters are for predecessor tasks and stored in object_id_to1 colunmn
862 -- of pa_object_relationships.
863
864 procedure check_create_inter_dep_ok(
865 p_pre_project_id IN NUMBER
866 ,p_pre_task_ver_id IN NUMBER
867 ,p_project_id IN NUMBER
868 ,p_task_ver_id IN NUMBER
869 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
870 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
871 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
872 ) IS
873
874 CURSOR cur_a
875 IS
876 SELECT 'x'
877 FROM pa_object_relationships
878 WHERE object_id_from1 = p_task_ver_id
879 AND object_id_from2 = p_project_id
880 AND object_id_to1 = p_pre_task_ver_id
881 AND object_id_to2 = p_pre_project_id
882 AND relationship_type = 'D'
883 ;
884
885 CURSOR CUR_valid_sucsr_proj_task
886 IS
887 SELECT 'x'
888 FROM pa_proj_element_versions
889 WHERE project_id = p_project_id
890 AND element_version_id = p_task_ver_id
891 ;
892
893
894 CURSOR CUR_valid_pred_proj_task
895 IS
896 SELECT 'x'
897 FROM pa_proj_element_versions
898 WHERE project_id = p_pre_project_id
899 AND element_version_id = p_pre_task_ver_id
900 ;
901
902 l_dummy_char VARCHAR2(1);
903
904 -- Begin fix for Bug # Bug # 4256435.
905
906 cursor cur_sub_proj_hierarchy(c_pre_project_id NUMBER) is
907 -- This query selects all the parent projects of the predecessor project.
908 select por.object_id_from1 task_ver_id, por.object_id_from2 project_id
909 from pa_object_relationships por
910 where por.relationship_type in ('LW', 'LF')
911 start with por.object_id_to2 = c_pre_project_id
912 connect by prior por.object_id_from2 = por.object_id_to2
913 and prior por.relationship_type = por.relationship_type
914 and por.relationship_type in ('LW', 'LF')
915 AND object_type_from = 'PA_TASKS' --Bug 6429264
916 AND object_type_to = 'PA_STRUCTURES' --Bug 6429264
917 union all
918 -- This query selects all the child projects of the predecessor project.
919 select por.object_id_to1 task_ver_id, por.object_id_to2 project_id
920 from pa_object_relationships por
921 where por.relationship_type in ('LW', 'LF')
922 start with por.object_id_from2 = c_pre_project_id
923 connect by prior por.object_id_to2 = por.object_id_from2
924 and prior por.relationship_type = por.relationship_type
925 and por.relationship_type in ('LW', 'LF')
926 AND object_type_from = 'PA_TASKS' --Bug 6429264
927 AND object_type_to = 'PA_STRUCTURES' --Bug 6429264
928 ;
929
930 rec_sub_proj_hierarchy cur_sub_proj_hierarchy%ROWTYPE;
931
932 cursor cur_linking_task(c_task_ver_id NUMBER, c_linking_task_ver_id NUMBER) is
933 select 'Y'
934 from pa_object_relationships por
935 where por.object_id_from1 = c_task_ver_id
936 and por.object_id_to1 = c_linking_task_ver_id
937 and por.relationship_type = 'S';
938
939 l_link_exists VARCHAR2(1) := 'N';
940
941 -- End fix for Bug # Bug # 4256435.
942
943 BEGIN
944
945 x_return_status := FND_API.G_RET_STS_SUCCESS;
946
947 --check valid for null parameters
948 IF p_pre_project_id IS NULL OR
949 p_pre_task_ver_id IS NULL OR
950 p_project_id IS NULL OR
951 p_task_ver_id IS NULL
952 THEN
953 x_return_status := FND_API.G_RET_STS_ERROR;
954 x_msg_count := FND_MSG_PUB.count_msg;
955 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
956 ,p_msg_name => 'PA_PS_PARAMS_NULL');
957 raise FND_API.G_EXC_ERROR;
958 END IF;
959
960 --check for inter projects. The successor project and predecssor projects should be different.
961 IF p_pre_project_id = p_project_id
962 THEN
963 x_return_status := FND_API.G_RET_STS_ERROR;
964 x_msg_count := FND_MSG_PUB.count_msg;
965 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
966 ,p_msg_name => 'PA_PS_NOT_INTER_DEPND');
967 raise FND_API.G_EXC_ERROR;
968 END IF;
969
970 -- Begin fix for Bug # Bug # 4256435.
971
972 for rec_sub_proj_hierarchy in cur_sub_proj_hierarchy(p_pre_project_id)
973 loop
974
975 if (rec_sub_proj_hierarchy.project_id = p_project_id) then
976
977 open cur_linking_task(p_task_ver_id, rec_sub_proj_hierarchy.task_ver_id);
978 fetch cur_linking_task into l_link_exists;
979 close cur_linking_task;
980
981 if (l_link_exists = 'Y') then
982
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 x_msg_count := FND_MSG_PUB.count_msg;
985
986 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
987 , p_msg_name => 'PA_WP_PRGM_EXISTS_NO_DEP');
988
989 raise FND_API.G_EXC_ERROR;
990
991 end if;
992 end if;
993
994 end loop;
995
996 -- End fix for Bug # Bug # 4256435.
997
998 /* do we really need this validation here?
999 --validate successor project id and task ver id combination.
1000 OPEN cur_valid_sucsr_proj_task;
1001 FETCH cur_valid_sucsr_proj_task INTO l_dummy;
1002 IF cur_valid_sucsr_proj_task%NOTFOUND
1003 THEN
1004 x_return_status := FND_API.G_RET_STS_ERROR;
1005 x_msg_count := FND_MSG_PUB.count_msg;
1006 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1007 ,p_msg_name => 'PA_PS_INV_SUCSR_PRJ_TSK');
1008 raise FND_API.G_EXC_ERROR;
1009 END IF;
1010 CLOSE cur_valid_sucsr_proj_task;
1011
1012 --validate predecessor project id and task ver id combination.
1013 OPEN cur_valid_pred_proj_task;
1014 FETCH cur_valid_pred_proj_task INTO l_dummy;
1015 IF cur_valid_pred_proj_task%NOTFOUND
1016 THEN
1017 x_return_status := FND_API.G_RET_STS_ERROR;
1018 x_msg_count := FND_MSG_PUB.count_msg;
1019 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1020 ,p_msg_name => 'PA_PS_INV_PRED_PRJ_TSK');
1021 raise FND_API.G_EXC_ERROR;
1022 END IF;
1023 CLOSE cur_valid_pred_proj_task;
1024 */
1025
1026 --check for duplicate dependency.
1027 OPEN cur_a;
1028 FETCH cur_a INTO l_dummy_char;
1029 IF cur_a%FOUND
1030 THEN
1031 x_return_status := FND_API.G_RET_STS_ERROR;
1032 x_msg_count := FND_MSG_PUB.count_msg;
1033 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1034 ,p_msg_name => 'PA_PS_DEPND_EXISTS');
1035 raise FND_API.G_EXC_ERROR;
1036 END IF;
1037 CLOSE cur_a;
1038
1039 EXCEPTION
1040 when FND_API.G_EXC_ERROR then
1041 x_return_status := FND_API.G_RET_STS_ERROR;
1042 x_msg_count := FND_MSG_PUB.count_msg;
1043 when OTHERS then
1044 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1045 x_msg_count := 1; -- 4537865 : RESET OUT param
1046 x_msg_data := SUBSTRB(SQLERRM,1,240); -- 4537865 : RESET OUT PARAM
1047 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_UTILS',
1048 p_procedure_name => 'check_create_inter_dep_ok',
1049 p_error_text => x_msg_data); -- 4537865
1050 raise;
1051
1052 END check_create_inter_dep_ok;
1053
1054
1055 FUNCTION DISPLAY_PREDECESSORS
1056 ( p_element_version_id IN NUMBER)
1057 RETURN VARCHAR2 IS
1058 --
1059 -- To modify this template, edit file FUNC.TXT in TEMPLATE
1060 -- directory of SQL Navigator
1061 --
1062 -- Purpose: Briefly explain the functionality of the function
1063 --
1064 -- MODIFICATION HISTORY
1065 -- Person Date Comments
1066 -- --------- ------ -------------------------------------------
1067 -- SMUKKA 01/28/2004 Initial Version
1068
1069 l_task_name_list VARCHAR2(1220):=NULL; --bug 4141897
1070 l_task_count NUMBER:=0;
1071 CURSOR cur_task_names( c_element_version_id NUMBER )
1072 IS
1073 SELECT ppe.name task_name
1074 FROM pa_proj_element_versions ppev,
1075 pa_proj_elements ppe
1076 WHERE ppev.proj_element_id = ppe.proj_element_id
1077 and ppev.project_id = ppe.project_id
1078 AND ppev.element_version_id IN (SELECT object_id_to1
1079 FROM pa_object_relationships por
1080 WHERE por.object_id_from1 = c_element_version_id
1081 --Bug8534395: Commented below predicate to allow tasks from other project to be shown as predecessors.
1082 --AND por.object_id_from2 = por.object_id_to2
1083 AND por.object_type_from = 'PA_TASKS' --4141109 Replaced LIKE with Equijoin
1084 AND por.object_type_to = 'PA_TASKS' --4141109 Replaced LIKE with Equijoin
1085 AND por.relationship_type = 'D'); --4141109 Replaced LIKE with Equijoin
1086 l_cur_task_names_rec cur_task_names%ROWTYPE;
1087 BEGIN
1088 OPEN cur_task_names(p_element_version_id);
1089 LOOP
1090 FETCH cur_task_names into l_cur_task_names_rec;
1091 IF cur_task_names%NOTFOUND THEN
1092 EXIT;
1093 ELSE
1094 l_task_count:=l_task_count+1;
1095 IF l_task_count = 1 THEN
1096 l_task_name_list:= l_task_name_list||l_cur_task_names_rec.task_name;
1097 ELSIF l_task_count > 5 THEN
1098 l_task_name_list:= l_task_name_list||'...';
1099 ELSE
1100 l_task_name_list := l_task_name_list||','||l_cur_task_names_rec.task_name;
1101 END IF;
1102 END IF;
1103 END LOOP;
1104 CLOSE cur_task_names;
1105 RETURN l_task_name_list ;
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 NULL ;
1109 END DISPLAY_PREDECESSORS;
1110
1111
1112 FUNCTION ChecK_dep_exists(p_element_version_id IN NUMBER)
1113 RETURN VARCHAR2
1114 IS
1115 CURSOR get_dependency IS
1116 SELECT 1 from pa_object_relationships
1117 where relationship_type = 'D'
1118 and (object_id_from1 = p_element_version_id OR
1119 object_id_to1 = p_element_version_id);
1120 l_dummy NUMBER;
1121 BEGIN
1122 OPEN get_Dependency;
1123 FETCH get_Dependency into l_dummy;
1124 IF get_dependency%FOUND THEN
1125 CLOSE get_dependency;
1126 return 'Y';
1127 END IF;
1128 CLOSE get_Dependency;
1129 return 'N';
1130 END CHECK_DEP_EXISTS;
1131
1132
1133 FUNCTION Is_Proj_Top_Program(p_project_id IN NUMBER)
1134 RETURN VARCHAR2
1135 IS
1136 --Bug No 3634315 Performance Fix, to avoid full table scan on pa_object_relationships table.
1137 /* CURSOR c1 IS
1138 select 1
1139 from pa_object_relationships
1140 where relationship_type IN ('LW', 'LF')
1141 and object_id_to2 = p_project_id;*/
1142 CURSOR c1 IS
1143 select 1
1144 from pa_object_relationships por,
1145 pa_proj_element_versions ppev
1146 where por.relationship_type IN ('LW', 'LF')
1147 and ppev.element_version_id = por.object_id_to1
1148 and por.object_id_to2 = ppev.project_id
1149 and por.object_id_to2 = p_project_id
1150 AND object_type_from = 'PA_TASKS' --Bug 6429264
1151 AND object_type_to = 'PA_STRUCTURES'; --Bug 6429264
1152
1153 CURSOR c2 IS
1154 select 1
1155 from pa_object_relationships
1156 where relationship_type IN ('LW', 'LF')
1157 and object_id_from2 = p_project_id
1158 AND object_type_from = 'PA_TASKS' --Bug 6429264
1159 AND object_type_to = 'PA_STRUCTURES'; --Bug 6429264
1160
1161 l_dummy number;
1162 BEGIN
1163 OPEN c1;
1164 FETCH c1 into l_dummy;
1165 IF c1%FOUND THEN
1166 CLOSE c1;
1167 return 'N';
1168 END IF;
1169 CLOSE c1;
1170
1171 OPEN c2;
1172 FETCH c2 into l_dummy;
1173 IF c2%NOTFOUND THEN
1174 CLOSE c2;
1175 return 'N';
1176 END IF;
1177 CLOSE c2;
1178
1179 return 'Y';
1180 END Is_Proj_Top_Program;
1181
1182 FUNCTION Is_Proj_Sub_Project(p_project_id IN NUMBER)
1183 RETURN VARCHAR2
1184 IS
1185 --Bug No 3634315 Performance Fix, to avoid full table scan on pa_object_relationships table.
1186 /* CURSOR c1 IS
1187 select 1
1188 from pa_object_relationships
1189 where relationship_type IN ('LW', 'LF')
1190 and object_id_to2 = p_project_id;*/
1191 CURSOR c1 IS
1192 select 1
1193 from pa_object_relationships por,
1194 pa_proj_element_versions ppev
1195 where por.relationship_type IN ('LW', 'LF')
1196 and ppev.element_version_id = por.object_id_to1
1197 and por.object_id_to2 = ppev.project_id
1198 and por.object_id_to2 = p_project_id
1199 AND object_type_from = 'PA_TASKS' --Bug 6429264
1200 AND object_type_to = 'PA_STRUCTURES'; --Bug 6429264
1201
1202 l_dummy NUMBER;
1203 BEGIN
1204 open c1;
1205 FETCH c1 into l_dummy;
1206 IF c1%NOTFOUND THEN
1207 CLOSE c1;
1208 return 'N';
1209 END IF;
1210 CLOSE c1;
1211
1212 return 'Y';
1213 END Is_Proj_Sub_Project;
1214
1215 FUNCTION DISABLE_SYS_PROG_OK(p_project_id NUMBER)
1216 RETURN varchar2
1217 IS
1218 CURSOR c1 IS
1219 select 1
1220 from pa_object_relationships
1221 where relationship_type IN ('LW', 'LF')
1222 and object_id_from2 = p_project_id;
1223 l_dummy NUMBER;
1224 BEGIN
1225 OPEN c1;
1226 FETCH c1 into l_dummy;
1227 IF c1%found then
1228 CLOSE c1;
1229 return 'N';
1230 END IF;
1231 CLOSE c1;
1232 return 'Y';
1233 END DISABLE_SYS_PROG_OK;
1234
1235 FUNCTION DISABLE_MULTI_PROG_OK(p_project_id NUMBER)
1236 RETURN varchar2
1237 IS
1238 --Bug No 3634315 Performance Fix, to avoid full table scan on pa_object_relationships table.
1239 /* CURSOR c1(c_parent_project_id NUMBER, c_child_project_id NUMBER) IS
1240 select count(1)
1241 from pa_object_relationships
1242 where relationship_type IN ('LW', 'LF')
1243 and object_id_to2 = c_child_project_id
1244 and object_id_from1 <> c_parent_project_id;*/
1245 CURSOR c1(c_parent_project_id NUMBER, c_child_project_id NUMBER) IS
1246 select count(1)
1247 from pa_object_relationships por,
1248 pa_proj_element_versions ppev
1249 where relationship_type IN ('LW', 'LF')
1250 and ppev.element_version_id = object_id_to1
1251 and por.object_id_to2 = ppev.project_id
1252 and object_id_to2 = c_child_project_id
1253 and object_id_from2 <> c_parent_project_id;--bug 4244482
1254
1255 CURSOR c2 IS
1256 select object_id_from2, object_Id_to2
1257 from pa_object_relationships
1258 where relationship_type IN ('LW') --bug 3962849
1259 start with object_id_from2 = p_project_id
1260 and relationship_type = 'LW'
1261 connect by prior object_id_to2 = object_id_from2
1262 and prior relationship_type = relationship_type;
1263
1264 CURSOR c3 IS
1265 select object_id_from2, object_Id_to2
1266 from pa_object_relationships
1267 where relationship_type IN ('LF') --bug 3962849
1268 start with object_id_from2 = p_project_id
1269 and relationship_type = 'LF'
1270 connect by prior object_id_to2 = object_id_from2
1271 and prior relationship_type = relationship_type;
1272
1273 l_parent_proj_id NUMBER;
1274 l_child_proj_id NUMBER;
1275 l_count NUMBER;
1276 BEGIN
1277 OPEN c2;
1278 LOOP
1279 FETCH c2 into l_parent_proj_id, l_child_proj_id;
1280 EXIT WHEN C2%NOTFOUND;
1281 OPEN c1(l_parent_proj_id, l_child_proj_id);
1282 FETCH c1 INTO l_count;
1283 IF l_count > 0 THEN
1284 CLOSE c1;
1285 CLOSE c2;
1286 return 'N';
1287 END IF;
1288 CLOSE c1;
1289 END LOOP;
1290 CLOSE c2;
1291
1292 OPEN c3;
1293 LOOP
1294 FETCH c3 into l_parent_proj_id, l_child_proj_id;
1295 EXIT WHEN C3%NOTFOUND;
1296 OPEN c1(l_parent_proj_id, l_child_proj_id);
1297 FETCH c1 INTO l_count;
1298 IF l_count > 0 THEN
1299 CLOSE c1;
1300 CLOSE c3;
1301 return 'N';
1302 END IF;
1303 CLOSE c1;
1304 END LOOP;
1305 CLOSE c3;
1306
1307 return 'Y';
1308 END DISABLE_MULTI_PROG_OK;
1309
1310 FUNCTION CREATE_SUB_PROJ_ASSO_OK(p_task_version_id NUMBER, p_project_id NUMBER,
1311 p_structure_type VARCHAR2 := 'WORKPLAN')
1312 RETURN VARCHAR2
1313 IS
1314 CURSOR get_project_id(c_element_version_id NUMBER) IS
1315 select project_id
1316 from pa_proj_element_versions
1317 where element_version_id = c_element_version_id;
1318
1319 CURSOR get_loop1(c_project_id NUMBER) IS
1320 select object_Id_to2, object_id_from2 -- Fix for Bug # 4297715.
1321 from pa_object_relationships
1322 where relationship_type IN ('LW')
1323 start with object_id_from2 = c_project_id
1324 connect by prior object_id_to2 = object_id_from2
1325 and prior relationship_type = relationship_type
1326 and relationship_type = 'LW';
1327
1328 CURSOR get_loop2(c_project_id NUMBER) IS
1329 select object_Id_to2, object_id_from2 -- Fix for Bug # 4297715.
1330 from pa_object_relationships
1331 where relationship_type IN ('LF')
1332 start with object_id_from2 = c_project_id
1333 connect by prior object_id_to2 = object_id_from2
1334 and prior relationship_type = relationship_type
1335 and relationship_type = 'LF';
1336 --
1337 -- Start of Bug 3621794
1338 CURSOR get_proj_prog_fl(c_project_id NUMBER) IS
1339 SELECT sys_program_flag
1340 FROM pa_projects_all
1341 WHERE project_id =c_project_id;
1342
1343 CURSOR get_parent_sub_proj(c_project_id NUMBER) IS
1344 SELECT ppa.sys_program_flag
1345 FROM pa_proj_element_versions ppev,
1346 pa_proj_elements ppe,
1347 pa_projects_all ppa
1348 WHERE ppe.project_id = ppev.project_id
1349 AND ppe.proj_element_id = ppev.proj_element_id
1350 AND ppev.object_type = 'PA_TASKS'
1351 AND ppe.object_type = 'PA_TASKS'
1352 AND ppe.project_id = ppa.project_id
1353 AND ppev.element_version_id IN (
1354 SELECT object_id_from1
1355 FROM pa_object_relationships
1356 WHERE relationship_type IN ('LW','LF')
1357 START WITH object_id_to2 = c_project_id
1358 AND object_type_to = 'PA_STRUCTURES'
1359 CONNECT BY object_id_from2 = prior object_id_to2
1360 and prior relationship_type = relationship_type
1361 AND relationship_type IN ('LW','LF')
1362 AND object_type_from = 'PA_TASKS');
1363 -- End of Bug 3621794
1364 --
1365 --bug 3893970
1366 CURSOR get_multi_rollup(c_project_id NUMBER) IS
1367 select nvl(ALLOW_MULTI_PROGRAM_ROLLUP,'N')
1368 from pa_projects_all
1369 where project_id = c_project_id;
1370
1371 CURSOR get_child_links(c_project_id NUMBER, c_parent_proj_id NUMBER) IS
1372 select distinct(object_id_from2)
1373 from pa_object_relationships a
1374 where a.relationship_type IN ('LW','LF')
1375 and a.object_id_to2 = c_project_id
1376 and a.object_id_from2 <> c_parent_proj_id -- Fix for Bug # 4297715.
1377 and exists (select 1 from PA_PROJ_ELEMENT_VERSIONS elv /* Added the exists for Bug 6148092 */
1378 where elv.element_version_id = a.object_id_from1
1379 and ((elv.PARENT_STRUCTURE_VERSION_ID =
1380 PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(elv.project_id))
1381 or (elv.PARENT_STRUCTURE_VERSION_ID =
1382 PA_PROJECT_STRUCTURE_UTILS.get_latest_wp_version(elv.project_id))
1383 )
1384 );
1385
1386 l_linked_parent_proj_id NUMBER;
1387 l_multi_rollup_flag VARCHAR2(1);
1388 l_dest_multi_rollup_flag VARCHAR2(1);
1389 --end bug 3893970
1390
1391 l_src_project_id NUMBER;
1392 l_dest_project_id NUMBER;
1393 l_proj_id NUMBER;
1394 l_proj_prog_fl VARCHAR2(1); --Bug 3621794
1395
1396 -- Begin Fix for Bug # 4297715.
1397
1398 cursor cur_get_status_code(c_task_version_id NUMBER) is
1399 select ppevs.status_code
1400 from pa_proj_elem_ver_structure ppevs, pa_proj_element_versions ppev
1401 where ppev.element_version_id = c_task_version_id
1402 and ppev.project_id = ppevs.project_id -- Bug # 4868867.
1403 and ppev.parent_structure_version_id = ppevs.element_version_id;
1404
1405 l_status_code VARCHAR2(150) := NULL;
1406
1407 l_proj_id_from NUMBER := NULL;
1408
1409 -- End Fix for Bug # 4297715.
1410
1411 -- Begin fix for Bug # Bug # 4256435.
1412
1413 cursor cur_dep_hierarchy(c_src_task_ver_id NUMBER) is
1414 -- This query selects all the successor projects of the source project.
1415 select por.object_id_from2 project_id
1416 from pa_object_relationships por
1417 where por.relationship_type = 'D'
1418 start with por.object_id_to1 = c_src_task_ver_id
1419 -- connect by prior por.object_id_from2 = por.object_id_to2 -- Fix for Bug # 4256435.
1420 connect by prior por.object_id_from1 = por.object_id_to1 -- Fix for Bug # 4256435.
1421 and prior por.relationship_type = por.relationship_type
1422 and por.relationship_type = 'D'
1423 union all
1424 -- This query selects all the predecessor projects of the source project.
1425 select por.object_id_to2 project_id
1426 from pa_object_relationships por
1427 where por.relationship_type = 'D'
1428 start with por.object_id_from1 = c_src_task_ver_id
1429 -- connect by prior por.object_id_to2 = por.object_id_from2 -- Fix for Bug # 4256435.
1430 connect by prior por.object_id_to1 = por.object_id_from1 -- Fix for Bug # 4256435.
1431 and prior por.relationship_type = por.relationship_type
1432 and por.relationship_type = 'D';
1433
1434 rec_dep_hierarchy cur_dep_hierarchy%ROWTYPE;
1435
1436 -- End fix for Bug # Bug # 4256435.
1437
1438 BEGIN
1439 OPEN get_project_id(p_task_version_id);
1440 FETCH get_project_id into l_src_project_id;
1441 CLOSE get_project_id;
1442
1443 -- Begin Fix for Bug # 4297715.
1444
1445 open cur_get_status_code(p_task_version_id);
1446 fetch cur_get_status_code into l_status_code;
1447 close cur_get_status_code;
1448
1449 -- End Fix for Bug # 4297715.
1450
1451
1452 l_dest_project_id := p_project_id;
1453
1454 IF (l_src_project_id = l_dest_project_id) THEN
1455 return 'N';
1456 END IF;
1457
1458 -- Begin fix for Bug # Bug # 4256435.
1459
1460 for rec_dep_hierarchy in cur_dep_hierarchy(p_task_version_id)
1461 loop
1462
1463 if (rec_dep_hierarchy.project_id = l_dest_project_id) then
1464
1465 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
1466 , p_msg_name => 'PA_WP_DEP_EXISTS_NO_PRGM');
1467
1468 return 'N';
1469
1470 end if;
1471
1472 end loop;
1473
1474 -- End fix for Bug # Bug # 4256435.
1475
1476 --bug 3893970
1477 OPEN get_multi_rollup(l_src_project_id);
1478 FETCH get_multi_rollup INTO l_multi_rollup_flag;
1479 CLOSE get_multi_rollup;
1480
1481 If l_multi_rollup_flag = 'N' THEN
1482 --need to check if new child is already linked
1483 OPEN get_child_links(l_dest_project_id, l_src_project_id);
1484 FETCH get_child_links INTO l_linked_parent_proj_id;
1485 IF get_child_links%FOUND THEN
1486 CLOSE get_child_links;
1487 return 'N';
1488 END IF;
1489 CLOSE get_child_links;
1490
1491 --need to check all new child to see if it has multi rollup = 'Y'
1492 FOR i IN get_loop1(l_dest_project_id) LOOP
1493 OPEN get_multi_rollup(i.object_id_to2);
1494 FETCH get_multi_rollup into l_dest_multi_rollup_flag;
1495 CLOSE get_multi_rollup;
1496
1497 IF (l_dest_multi_rollup_flag = 'Y') THEN
1498 return 'N';
1499 END IF;
1500 END LOOP;
1501
1502 FOR i IN get_loop2(l_dest_project_id) LOOP
1503 OPEN get_multi_rollup(i.object_id_to2);
1504 FETCH get_multi_rollup into l_dest_multi_rollup_flag;
1505 CLOSE get_multi_rollup;
1506
1507 IF (l_dest_multi_rollup_flag = 'Y') THEN
1508 return 'N';
1509 END IF;
1510 END LOOP;
1511 ELSE
1512 --If allow, need to check if new child has parent which does not allow
1513 --need to check all new child to see if its parent has multi rollup = 'N'
1514 OPEN get_child_links(l_dest_project_id, l_src_project_id);
1515 LOOP
1516 FETCH get_child_links INTO l_linked_parent_proj_id;
1517 EXIT WHEN get_child_links%NOTFOUND;
1518
1519 OPEN get_multi_rollup(l_linked_parent_proj_id);
1520 FETCH get_multi_rollup into l_dest_multi_rollup_flag;
1521 CLOSE get_multi_rollup;
1522
1523 IF (l_dest_multi_rollup_flag = 'N') THEN
1524 CLOSE get_child_links;
1525 return 'N';
1526 END IF;
1527 END LOOP;
1528 CLOSE get_child_links;
1529 END IF;
1530 --end bug 3893970
1531
1532 IF (p_structure_type = 'WORKPLAN') THEN
1533 OPEN get_loop1(l_src_project_id);
1534 LOOP
1535 FETCH get_loop1 INTO l_proj_id, l_proj_id_from; -- Fix for Bug # 4297715.
1536 EXIT when get_loop1%NOTFOUND;
1537
1538 IF ((l_proj_id = l_dest_project_id) -- Fix for Bug # 4297715.
1539
1540 -- Fix for Bug # 4297715. If the published versions of the source project and the destination
1541 -- project are linked in a parent and immediate child relationship respectively, we still allow
1542 -- the working version of the source project to be linked to the published version of the
1543 -- destination project.
1544
1545 and NOT((nvl(l_status_code,'X') = 'STRUCTURE_WORKING') -- Fix for Bug # 4297715.
1546 and (l_proj_id_from = l_src_project_id))) -- Fix for Bug # 4297715.
1547 THEN
1548 CLOSE get_loop1;
1549 return 'N';
1550 END If;
1551
1552 END LOOP;
1553 CLOSE get_loop1;
1554 END IF;
1555
1556 IF (p_structure_type = 'FINANCIAL') THEN
1557 OPEN get_loop2(l_src_project_id);
1558 LOOP
1559 FETCH get_loop2 INTO l_proj_id, l_proj_id_from; -- Fix for Bug # 4297715.
1560 EXIT when get_loop2%NOTFOUND;
1561
1562 IF ((l_proj_id = l_dest_project_id) -- Fix for Bug # 4297715.
1563
1564 -- Fix for Bug # 4297715. If the published versions of the source project and the destination
1565 -- project are linked in a parent and immediate child relationship respectively, we still allow
1566 -- the working version of the source project to be linked to the published version of the
1567 -- destination project.
1568
1569 and NOT((nvl(l_status_code,'X') = 'STRUCTURE_WORKING') -- Fix for Bug # 4297715.
1570 and (l_proj_id_from = l_src_project_id))) -- Fix for Bug # 4297715.
1571 THEN
1572 CLOSE get_loop2;
1573 return 'N';
1574 END If;
1575
1576 END LOOP;
1577 CLOSE get_loop2;
1578 END IF;
1579
1580 --IMP Note: Please add any new validation above this code
1581 --Imp Note: Let this be the last validation to be performed by this API.
1582 -- Start of Bug 3621794
1583 OPEN get_proj_prog_fl(l_src_project_id);
1584 FETCH get_proj_prog_fl INTO l_proj_prog_fl;
1585 IF l_proj_prog_fl = 'Y' THEN
1586 CLOSE get_proj_prog_fl;
1587 -- return 'N'; --Bug 3622177
1588 return 'Y'; --Bug 3622177
1589 END IF;
1590 CLOSE get_proj_prog_fl;
1591
1592 l_proj_prog_fl:='N';
1593 OPEN get_parent_sub_proj(l_src_project_id);
1594 LOOP
1595 FETCH get_parent_sub_proj INTO l_proj_prog_fl;
1596 IF get_parent_sub_proj%NOTFOUND THEN
1597 CLOSE get_parent_sub_proj;
1598 return 'N';
1599 END IF;
1600 -- EXIT WHEN get_parent_sub_proj%NOTFOUND;
1601 IF l_proj_prog_fl = 'Y' THEN
1602 CLOSE get_parent_sub_proj;
1603 -- return 'N'; --Bug 3622177
1604 return 'Y'; --Bug 3622177
1605 END IF;
1606 END LOOP;
1607 CLOSE get_parent_sub_proj;
1608 -- End of Bug 3621794
1609
1610 return 'Y';
1611 END CREATE_SUB_PROJ_ASSO_OK;
1612
1613 FUNCTION IS_AUTO_ROLLUP(p_project_id NUMBER)
1614 RETURN VARCHAR2
1615 IS
1616 cursor c1 is
1617 select ppwa.AUTO_ROLLUP_SUBPROJ_FLAG
1618 from pa_proj_workplan_attr ppwa,
1619 pa_proj_elements ppe,
1620 pa_proj_structure_types ppst,
1621 pa_structure_types pst
1622 where ppe.project_id = p_project_id
1623 and ppe.object_type = 'PA_STRUCTURES'
1624 and ppe.proj_element_id = ppst.proj_element_id
1625 and ppst.structure_type_id = pst.structure_type_id
1626 and pst.structure_type = 'WORKPLAN'
1627 and ppe.project_id = ppwa.project_id
1628 and ppe.proj_element_id = ppwa.proj_element_id;
1629 l_dummy VARCHAR2(1);
1630 BEGIN
1631 OPEN c1;
1632 FETCH c1 into l_dummy;
1633 CLOSE c1;
1634
1635 return l_dummy;
1636
1637 END IS_AUTO_ROLLUP;
1638
1639 FUNCTION Get_Latest_Parent_Ver_obj_Id(p_structure_ver_id NUMBER,
1640 p_task_id NUMBER
1641 , p_relationship_type VARCHAR2 := 'LW') -- Fix for Bug # 4471484.
1642 RETURN NUMBER
1643 IS
1644 CURSOR c1 IS
1645 select por2.object_relationship_id, por2.relationship_type, ppev.element_version_id
1646 from pa_object_relationships por1,
1647 pa_object_relationships por2,
1648 pa_proj_element_versions ppev,
1649 pa_proj_elements ppe
1650 where ppe.proj_element_id = p_task_id
1651 and ppe.proj_element_id = ppev.proj_element_id
1652 and ppe.project_id = ppev.project_id
1653 and ppev.element_version_id = por1.object_id_from1
1654 and por1.relationship_type = 'S'
1655 and por2.object_id_to1 = p_structure_ver_id
1656 and por2.object_id_from1 = por1.object_id_to1
1657 and por2.relationship_type = p_relationship_type -- IN ('LF', 'LW') -- Fix for Bug # 4471484.
1658 -- and rownum < 2 -- Fix for Bug # 4477118.
1659 order by ppev.element_version_id desc ; -- por2.relationship_type desc, -- Fix for Bug # 4477118.
1660
1661 /*
1662 select por1.object_id_from1
1663 from pa_object_relationships por1,
1664 pa_object_relationships por2
1665 where por2.object_id_to1 = p_structure_ver_id
1666 and por2.object_id_from1 = por1.object_id_to1
1667 and por2.relationship_type IN ('LF', 'LW')
1668 and rownum < 2
1669 order by por1.object_id_from1 desc;
1670 */
1671
1672 l_obj_rel_id NUMBER;
1673 l_element_ver_id NUMBER;
1674 l_obj_type VARCHAR2(30);
1675 BEGIN
1676 OPEN c1;
1677 FETCH c1 into l_obj_rel_id, l_obj_type, l_element_ver_id;
1678 CLOSE c1;
1679
1680 return l_obj_rel_id;
1681 END Get_Latest_Parent_Ver_obj_Id;
1682
1683 FUNCTION Get_Latest_Parent_Task_Ver_Id(p_structure_ver_id NUMBER,
1684 p_task_id NUMBER
1685 , p_relationship_type VARCHAR2 := 'LW')
1686 RETURN NUMBER
1687 IS
1688 CURSOR c1 IS
1689 select por2.object_relationship_id, por2.relationship_type, ppev.element_version_id
1690 from pa_object_relationships por1,
1691 pa_object_relationships por2,
1692 pa_proj_element_versions ppev,
1693 pa_proj_elements ppe
1694 where ppe.proj_element_id = p_task_id
1695 and ppe.proj_element_id = ppev.proj_element_id
1696 and ppe.project_id = ppev.project_id
1697 and ppev.element_version_id = por1.object_id_from1
1698 and por1.relationship_type = 'S'
1699 and por2.object_id_to1 = p_structure_ver_id
1700 and por2.object_id_from1 = por1.object_id_to1
1701 and por2.relationship_type = p_relationship_type
1702 order by ppev.element_version_id desc ;
1703
1704 l_obj_rel_id NUMBER;
1705 l_element_ver_id NUMBER;
1706 l_obj_type VARCHAR2(30);
1707 BEGIN
1708 OPEN c1;
1709 FETCH c1 into l_obj_rel_id, l_obj_type, l_element_ver_id;
1710 CLOSE c1;
1711
1712 return l_element_ver_id;
1713 END Get_Latest_Parent_Task_Ver_Id;
1714
1715
1716 FUNCTION Get_Latest_Child_Ver_Id(p_task_ver_id NUMBER)
1717 RETURN NUMBER
1718 IS
1719 CURSOR c1 IS
1720 select por2.object_id_to1
1721 from pa_object_relationships por1,
1722 pa_object_relationships por2
1723 where por1.object_id_from1 = p_task_ver_id
1724 and por1.object_id_to1 = por2.object_id_from1
1725 and por2.relationship_type IN ('LF', 'LW')
1726 and rownum < 2
1727 order by por2.object_id_to1 desc;
1728
1729 l_child_ver_id NUMBER;
1730 BEGIN
1731 OPEN c1;
1732 FETCH c1 into l_child_ver_id;
1733 CLOSE c1;
1734
1735 return l_child_ver_id;
1736 END Get_Latest_Child_Ver_Id;
1737
1738
1739 --============================================================================================
1740 /*Bug 3629024 : ## SHORT NOTE ON THE FOLL. TWO MUTUALLY RECURSIVE FUNCTIONS ##
1741 Treat the workplan structure as a directed graph, with nodes being the
1742 structure/tasks and links between the nodes being dependencies and/or
1743 parent/child task relationships.
1744 Dependency links are directed from sucessor to predecessor.
1745 Child Tasks are linked to their parents with a bi-directional link.
1746 Then, there should be no circular path for any node
1747
1748 The following two functions check for the existence of such a closed path.
1749 */
1750
1751 -- Function : get_predecessors
1752 -- Type : Mutually Recursive functions alongwith get_parents_childs
1753 -- Purpose : Retrieves tasks which are predecessor of the task passed to
1754 -- this function (p_src_task_ver_id)
1755 -- Return : Returns with TRUE if
1756 -- SUCCESSOR(p_orig_succ_task_ver_id) matches any of the retrieved
1757 -- predecessors
1758 -- Assumptions : We have started from the PREDECESSOR to which a dependency is
1759 -- trying to be created
1760 -- Parameters Type Required Description and Purpose
1761 -- --------------------------- ------ -------- --------------------------------------------------------
1762 -- p_src_task_ver_id NUMBER Y Task id for which predecessors are to be retieved and checked
1763 -- p_orig_succ_task_ver_id NUMBER Y Task id to which the retrieved predecessors are compared
1764 --
1765 -- Call the function as get_predecessors(PRE,SUCC) where
1766 -- PRE = predecessor to which a dependency is trying to be created
1767 -- SUCC = successor from which a dependency is trying to be created
1768 FUNCTION get_predecessors( p_src_task_ver_id IN NUMBER
1769 ,p_orig_succ_task_ver_id IN NUMBER ) RETURN BOOLEAN IS
1770 CURSOR cur_get_predecessors IS
1771 SELECT por1.object_id_to1 RELATED_TASK
1772 FROM pa_object_relationships por1
1773 WHERE por1.relationship_type = 'D'
1774 AND LEVEL = 1
1775 AND por1.object_id_from2 = por1.object_id_to2
1776 START WITH por1.object_id_from1 = p_src_task_ver_id
1777 CONNECT BY PRIOR por1.object_id_to1 = por1.object_id_from1
1778 AND PRIOR por1.relationship_type = por1.relationship_type
1779 AND PRIOR por1.object_id_from2 = por1.object_id_from2
1780 ;
1781 --NOTE : * PRIOR por1.relationship_type = por1.relationship_type
1782 -- is required so as to prevent traversing parent-child dependency relns.
1783 -- * PRIOR por1.object_id_from2 = por1.object_id_from2 in CONNECT clause
1784 -- is required to prevent traversing inter-project dependencies; which can be
1785 -- circular and can give a "ORA-01436: CONNECT BY loop in user data" error
1786 -- * por1.object_id_from2 = por1.object_id_to2 in WHERE clause
1787 -- is required to filter out only those rows which correspond to intra-proj dependencies
1788 BEGIN
1789 FOR rec_predecessors IN cur_get_predecessors LOOP
1790 IF rec_predecessors.related_task = p_orig_succ_task_ver_id
1791 OR get_parents_childs( rec_predecessors.related_task, p_orig_succ_task_ver_id ) = TRUE THEN
1792 RETURN TRUE;
1793 END IF;
1794 END LOOP;
1795
1796 RETURN FALSE;
1797 END get_predecessors;
1798
1799 -- Function : get_parents_childs
1800 -- Type : Mutually Recursive functions alongwith get_predecessors
1801 -- Purpose : Retrieves tasks which are parents or childs, of the task passed to
1802 -- this function (p_src_task_ver_id)
1803 -- Return : Returns with TRUE if
1804 -- SUCCESSOR(p_orig_succ_task_ver_id) matches any of the retrieved
1805 -- parents/childs
1806 -- Assumptions : We have started from the PREDECESSOR to which a dependency is
1807 -- trying to be created
1808 -- Parameters Type Required Description and Purpose
1809 -- --------------------------- ------ -------- --------------------------------------------------------
1810 -- p_src_task_ver_id NUMBER Y Task id for which parents/childs are to be retieved and checked
1811 -- p_orig_succ_task_ver_id NUMBER Y Task id to which the retrieved parents/childs are compared
1812 FUNCTION get_parents_childs( p_src_task_ver_id IN NUMBER
1813 ,p_orig_succ_task_ver_id IN NUMBER ) RETURN BOOLEAN IS
1814 CURSOR cur_get_parents_childs IS
1815 SELECT por1.object_id_to1 RELATED_TASK
1816 FROM pa_object_relationships por1
1817 WHERE por1.relationship_type = 'S'
1818 AND por1.relationship_subtype = 'TASK_TO_TASK'
1819 START WITH por1.object_id_from1 = p_src_task_ver_id
1820 AND relationship_type = 'S' --bug 3944567
1821 CONNECT BY PRIOR por1.object_id_to1 = por1.object_id_from1
1822 AND PRIOR por1.relationship_type = por1.relationship_type
1823 UNION
1824 SELECT por2.object_id_from1 RELATED_TASK
1825 FROM pa_object_relationships por2
1826 WHERE por2.relationship_type = 'S'
1827 AND por2.relationship_subtype = 'TASK_TO_TASK'
1828 START WITH por2.object_id_to1 = p_src_task_ver_id
1829 AND relationship_type = 'S' --bug 3944567
1830 CONNECT BY PRIOR por2.object_id_from1 = por2.object_id_to1
1831 AND PRIOR por2.relationship_type = por2.relationship_type
1832 UNION
1833 SELECT p_src_task_ver_id RELATED_TASK
1834 FROM dual
1835 ;
1836
1837 --bug 4145585
1838 Cursor check_intersect IS
1839 select object_id_from1 elem_ver_id
1840 from pa_object_relationships
1841 start with object_id_to1 = p_orig_succ_task_ver_id
1842 and relationship_type = 'D'
1843 connect by object_id_to1 = prior object_id_from1
1844 and relationship_type = prior relationship_type
1845 and object_id_to2 = object_id_from2
1846 INTERSECT
1847 select object_id_to1
1848 from pa_object_relationships
1849 start with object_id_to1 = p_src_task_ver_id
1850 and relationship_type = 'S'
1851 connect by prior object_id_to1 = object_id_from1
1852 and relationship_type = prior relationship_type;
1853 l_dummy NUMBER;
1854 --end bug 4145585
1855
1856 BEGIN
1857 --bug 4145585
1858 /*
1859 FOR rec_parents_childs IN cur_get_parents_childs LOOP
1860 IF rec_parents_childs.related_task = p_orig_succ_task_ver_id
1861 OR get_predecessors( rec_parents_childs.related_task, p_orig_succ_task_ver_id ) = TRUE THEN
1862 RETURN TRUE;
1863 END IF;
1864 END LOOP;
1865
1866 RETURN FALSE;
1867 */
1868 OPEN check_intersect;
1869 FETCH check_intersect into l_dummy;
1870 IF check_intersect%FOUND THEN
1871 CLOSE check_intersect;
1872 RETURN TRUE;
1873 END IF;
1874 CLOSE check_intersect;
1875
1876 RETURN FALSE;
1877 --end bug 4145585
1878
1879 END get_parents_childs;
1880 --============================================================================================
1881
1882 FUNCTION Check_link_exists(p_project_id number
1883 ,p_link_type VARCHAR2 DEFAULT 'SHARED' --bug 4532826
1884 ) return VARCHAR2
1885 IS
1886 CURSOR get_count IS
1887 Select count(1) from pa_object_relationships
1888 where relationship_type IN ('LW', 'LF')
1889 and (object_id_from2 = p_project_id or object_id_to2 = p_project_id);
1890
1891 -- bug 4532826
1892 CURSOR get_count_fn IS
1893 Select count(1) from pa_object_relationships
1894 where relationship_type = 'LF'
1895 and (object_id_from2 = p_project_id or object_id_to2 = p_project_id);
1896
1897 CURSOR get_count_wp IS
1898 Select count(1) from pa_object_relationships
1899 where relationship_type = 'LW'
1900 and (object_id_from2 = p_project_id or object_id_to2 = p_project_id);
1901 --end bug 4532826
1902
1903
1904 l_cnt NUMBER :=0;
1905 BEGIN
1906 IF p_link_type = 'SHARED' --bug 4532826
1907 THEN
1908 OPEN get_count;
1909 FETCH get_count into l_cnt;
1910 CLOSE get_count;
1911 --bug 4532826
1912 ELSIF p_link_type = 'FINANCIAL'
1913 THEN
1914
1915 OPEN get_count_fn;
1916 FETCH get_count_fn into l_cnt;
1917 CLOSE get_count_fn;
1918
1919 ELSIF p_link_type = 'WORKPLAN'
1920 THEN
1921
1922 OPEN get_count_wp;
1923 FETCH get_count_wp into l_cnt;
1924 CLOSE get_count_wp;
1925
1926 END IF;
1927 --bug end 4532826
1928
1929 IF l_cnt > 0 THEN
1930 return 'Y';
1931 END IF;
1932
1933 return 'N';
1934 END Check_link_exists;
1935
1936 FUNCTION Check_proj_currency_identical(p_src_project_id NUMBER
1937 , p_dest_project_id NUMBER) return VARCHAR2
1938 IS
1939 CURSOR get_proj_curr_code(c_project_id NUMBER) IS
1940 select project_currency_code
1941 from pa_projects_all
1942 where project_id = C_project_id;
1943 l_src_proj_currency_code VARCHAR2(15);
1944 l_dest_proj_currency_code VARCHAR2(15);
1945 BEGIN
1946 OPEN get_proj_curr_code(p_src_project_id);
1947 FETCH get_proj_curr_code INTO l_src_proj_currency_code;
1948 CLOSE get_proj_curr_code;
1949
1950 OPEN get_proj_curr_code(p_dest_project_id);
1951 FETCH get_proj_curr_code INTO l_dest_proj_currency_code;
1952 CLOSE get_proj_curr_code;
1953
1954 IF (l_src_proj_currency_code <> l_dest_proj_currency_code) THEN
1955 return 'N';
1956 END IF;
1957
1958 return 'Y';
1959 END check_proj_currency_identical;
1960
1961 FUNCTION check_dependencies_valid(p_new_parent_task_ver_id IN NUMBER
1962 ,p_task_ver_id IN NUMBER) RETURN VARCHAR2
1963 IS
1964 CURSOR get_parent_to_child IS
1965 select count(1)
1966 from pa_object_relationships
1967 where relationship_type = 'D'
1968 and object_id_from1 IN ( --get all tasks in upper branch
1969 select object_id_to1
1970 from pa_object_relationships
1971 start with object_id_to1 = p_new_parent_task_ver_id
1972 and relationship_type = 'S'
1973 connect by prior object_id_from1 = object_id_to1
1974 and relationship_type = prior relationship_type
1975 and prior object_type_from = object_type_to)
1976 and object_id_to1 IN ( --get all tasks in lower branch
1977 select object_id_to1
1978 from pa_object_relationships
1979 start with object_id_to1 = p_task_ver_id
1980 and relationship_type = 'S'
1981 connect by prior object_id_to1 = object_id_from1
1982 and relationship_type = prior relationship_type
1983 and prior object_type_to = object_type_from);
1984
1985 CURSOR get_child_to_parent IS
1986 select count(1)
1987 from pa_object_relationships
1988 where relationship_type = 'D'
1989 and object_id_from1 IN ( --get tasks in lower branch
1990 select object_id_to1
1991 from pa_object_relationships
1992 start with object_id_to1 = p_task_ver_id
1993 and relationship_type = 'S'
1994 connect by prior object_id_to1 = object_id_from1
1995 and relationship_type = prior relationship_type
1996 and prior object_type_to = object_type_from)
1997 and object_id_to1 IN ( --get tasks in upper branch
1998 select object_id_to1
1999 from pa_object_relationships
2000 start with object_id_to1 = p_new_parent_task_ver_id
2001 and relationship_type = 'S'
2002 connect by prior object_id_from1 = object_id_to1
2003 and relationship_type = prior relationship_type
2004 and prior object_type_from = object_type_to);
2005 l_cnt NUMBER;
2006
2007 BEGIN
2008 OPEN get_parent_to_child;
2009 FETCH get_parent_to_child INTO l_cnt;
2010 CLOSE get_parent_to_child;
2011 IF l_cnt > 0 THEN
2012 return 'N';
2013 END IF;
2014
2015 OPEN get_child_to_parent;
2016 FETCH get_child_to_parent INTO l_cnt;
2017 CLOSE get_child_to_parent;
2018 IF l_cnt > 0 THEN
2019 return 'N';
2020 END IF;
2021
2022 RETURN 'Y';
2023 END check_dependencies_valid;
2024
2025 -- Begin fix for Bug # 4266540.
2026
2027 FUNCTION check_task_has_sub_proj(p_project_id NUMBER
2028 , p_task_id NUMBER
2029 , p_task_version_id NUMBER := NULL)
2030 return VARCHAR2 is
2031
2032 cursor cur_sub_project (c_project_id NUMBER, c_task_id NUMBER, c_task_version_id NUMBER) is
2033 select count(pslv.sub_project_id)
2034 from pa_structures_links_v pslv
2035 where pslv.parent_project_id = c_project_id
2036 and pslv.parent_task_id = c_task_id
2037 and pslv.parent_task_version_id = c_task_version_id;
2038
2039 cursor cur_task_version_id (c_project_id NUMBER, c_task_id NUMBER, c_structure_version_id NUMBER) is
2040 select ppev.element_version_id
2041 from pa_proj_element_versions ppev
2042 where ppev.project_id = c_project_id
2043 and ppev.proj_element_id = c_task_id
2044 and ppev.parent_structure_version_id = c_structure_version_id;
2045
2046 l_cur_working_str_ver_id NUMBER := null;
2047
2048 l_task_version_id NUMBER := null;
2049
2050 l_count NUMBER := null;
2051
2052 l_return VARCHAR2(1) := null;
2053
2054 BEGIN
2055
2056 l_return := 'N';
2057
2058 if (p_task_version_id is null) then
2059
2060 -- The calling API in this case is: pa_task_utils.check_create_subtask_ok() which is only
2061 -- called for 'FINANCIAL' tasks.
2062
2063 l_cur_working_str_ver_id := pa_project_structure_utils.get_fin_struc_ver_id(p_project_id);
2064
2065 open cur_task_version_id(p_project_id, p_task_id, l_cur_working_str_ver_id);
2066 fetch cur_task_version_id into l_task_version_id;
2067 close cur_task_version_id;
2068
2069 else
2070
2071 l_task_version_id := p_task_version_id;
2072
2073 end if;
2074
2075 open cur_sub_project(p_project_id, p_task_id, l_task_version_id);
2076 fetch cur_sub_project into l_count;
2077 close cur_sub_project;
2078
2079 if nvl(l_count,0) > 0 then
2080
2081 l_return := 'Y';
2082
2083 end if;
2084
2085 return(l_return);
2086
2087 END check_task_has_sub_proj;
2088
2089 -- End fix for Bug # 4266540.
2090
2091 -- Begin fix for Bug # 4411603.
2092
2093 function is_str_linked_to_working_ver
2094 (p_project_id NUMBER
2095 , p_structure_version_id NUMBER
2096 , p_relationship_type VARCHAR2 := 'LW') return VARCHAR2
2097 is
2098
2099 l_return_value VARCHAR2(1) := null;
2100
2101 cursor cur_structure_version_ids (c_object_id_from NUMBER
2102 , c_relationship_type VARCHAR2) is
2103 -- Bug # 4757224.
2104
2105 select 1
2106 from dual
2107 where exists (select 1
2108 from pa_proj_element_versions pev, pa_object_relationships por, pa_proj_elem_ver_structure ppevs
2109 where pev.parent_structure_version_id = c_object_id_from
2110 and pev.element_version_id = por.object_id_from1
2111 and por.object_id_to1 = ppevs.element_version_id
2112 -- Bug Fix 5077552
2113 -- Adding the project id to avoid the FTS on ppevs.
2114 and por.object_id_to2 = ppevs.project_id
2115 and por.object_type_to = 'PA_STRUCTURES'
2116 and por.relationship_type in (c_relationship_type, 'S')
2117 and ppevs.status_code='STRUCTURE_WORKING');
2118
2119 /*
2120 -- Bug # 4737033.
2121
2122 select ppevs.status_code status_code
2123 from (select por.object_id_to1
2124 from pa_object_relationships por
2125 where por.object_type_to = 'PA_STRUCTURES'
2126 and relationship_type in (c_relationship_type, 'S')
2127 start with por.object_id_from1 = c_object_id_from
2128 connect by prior por.object_id_to1 = por.object_id_from1
2129 and prior relationship_type in (c_relationship_type, 'S')) por
2130 ,pa_proj_elem_ver_structure ppevs
2131 where
2132 por.object_id_to1 = ppevs.element_version_id (+);
2133
2134 select ppevs.status_code status_code
2135 from pa_object_relationships por, pa_proj_elem_ver_structure ppevs
2136 where por.object_id_to1 = ppevs.element_version_id (+)
2137 and por.object_type_to = 'PA_STRUCTURES'
2138 and relationship_type in (c_relationship_type, 'S')
2139 start with por.object_id_from1 = c_object_id_from
2140 connect by prior por.object_id_to1 = por.object_id_from1
2141 and prior relationship_type in (c_relationship_type, 'S');
2142
2143 -- Bug # 4737033.
2144 */
2145
2146 -- Bug # 4757224.
2147
2148 l_structure_working VARCHAR2(1) := null;
2149
2150 rec_structure_version_ids cur_structure_version_ids%rowtype;
2151
2152 begin
2153
2154 l_return_value := 'N';
2155
2156 if (pa_project_structure_utils.check_program_flag_enable(p_project_id) = 'Y') then -- Bug # 4742904.
2157
2158 -- Bug # 4757224.
2159
2160 open cur_structure_version_ids(p_structure_version_id, p_relationship_type);
2161 fetch cur_structure_version_ids into rec_structure_version_ids;
2162
2163 if cur_structure_version_ids%NOTFOUND then
2164 l_return_value:='N';
2165 else
2166 l_return_value:='Y';
2167 end if;
2168
2169 close cur_structure_version_ids;
2170
2171 /*
2172 for rec_structure_version_ids in cur_structure_version_ids(p_structure_version_id, p_relationship_type)
2173 loop
2174
2175 if (rec_structure_version_ids.status_code = 'STRUCTURE_WORKING') then
2176
2177 l_return_value := 'Y';
2178
2179 end if;
2180
2181 end loop;
2182 */
2183
2184 -- Bug # 4757224.
2185
2186 end if; -- Bug # 4742904.
2187
2188 return(l_return_value);
2189
2190 end is_str_linked_to_working_ver;
2191
2192 -- End fix for Bug # 4411603.
2193
2194 --bug 4541039
2195
2196 FUNCTION Check_parent_project_Exists
2197 (
2198 p_project_id NUMBER,
2199 p_structure_ver_id NUMBER
2200 ,p_link_type VARCHAR2 default 'SHARED' --bug 4541039
2201 )RETURN VARCHAR2
2202 IS
2203 CURSOR check_parentproj_exists IS
2204 SELECT '1'
2205 from pa_object_relationships por
2206 WHERE p_structure_ver_id = por.object_id_to1
2207 and por.object_id_to2 = p_project_id
2208 and por.relationship_type IN ('LW', 'LF');
2209
2210
2211 CURSOR check_parentproj_exists_wp IS
2212 SELECT '1'
2213 from pa_object_relationships por
2214 WHERE p_structure_ver_id = por.object_id_to1
2215 and por.object_id_to2 = p_project_id
2216 and por.relationship_type = 'LW';
2217
2218 CURSOR check_parentproj_exists_fn IS
2219 SELECT '1'
2220 from pa_object_relationships por
2221 WHERE p_structure_ver_id = por.object_id_to1
2222 and por.object_id_to2 = p_project_id
2223 and por.relationship_type = 'LF';
2224
2225 l_dummy VARCHAR2(1);
2226 BEGIN
2227 IF p_link_type = 'SHARED' --bug 4541039
2228 THEN
2229 OPEN check_parentproj_exists;
2230 FETCH check_parentproj_exists INTO l_dummy;
2231 IF check_parentproj_exists%NOTFOUND THEN
2232 CLOSE check_parentproj_exists;
2233 RETURN 'N';
2234 ELSE
2235 CLOSE check_parentproj_exists;
2236 RETURN 'Y';
2237 END IF;
2238 ELSIF p_link_type = 'WORKPLAN'
2239 THEN
2240 OPEN check_parentproj_exists_wp;
2241 FETCH check_parentproj_exists_wp INTO l_dummy;
2242 IF check_parentproj_exists_wp%NOTFOUND THEN
2243 CLOSE check_parentproj_exists_wp;
2244 RETURN 'N';
2245 ELSE
2246 CLOSE check_parentproj_exists_wp;
2247 RETURN 'Y';
2248 END IF;
2249 ELSIF p_link_type = 'FINANCIAL'
2250 THEN
2251 OPEN check_parentproj_exists_fn;
2252 FETCH check_parentproj_exists_fn INTO l_dummy;
2253 IF check_parentproj_exists_fn%NOTFOUND THEN
2254 CLOSE check_parentproj_exists_fn;
2255 RETURN 'N';
2256 ELSE
2257 CLOSE check_parentproj_exists_fn;
2258 RETURN 'Y';
2259 END IF;
2260 END IF;
2261
2262 END Check_parent_project_Exists;
2263
2264 --bug 4619824
2265 FUNCTION Check_subproject_link_exists(p_project_id number
2266 ,p_link_type VARCHAR2 DEFAULT 'SHARED' --bug 4532826
2267 ) return VARCHAR2
2268 IS
2269 CURSOR get_count IS
2270 Select count(1) from pa_object_relationships
2271 where relationship_type IN ('LW', 'LF')
2272 and object_id_from2 = p_project_id;
2273
2274 CURSOR get_count_fn IS
2275 Select count(1) from pa_object_relationships
2276 where relationship_type = 'LF'
2277 and object_id_from2 = p_project_id;
2278
2279 CURSOR get_count_wp IS
2280 Select count(1) from pa_object_relationships
2281 where relationship_type = 'LW'
2282 and (object_id_from2 = p_project_id);
2283
2284
2285 l_cnt NUMBER :=0;
2286 BEGIN
2287 IF p_link_type = 'SHARED'
2288 THEN
2289 OPEN get_count;
2290 FETCH get_count into l_cnt;
2291 CLOSE get_count;
2292 ELSIF p_link_type = 'FINANCIAL'
2293 THEN
2294
2295 OPEN get_count_fn;
2296 FETCH get_count_fn into l_cnt;
2297 CLOSE get_count_fn;
2298
2299 ELSIF p_link_type = 'WORKPLAN'
2300 THEN
2301
2302 OPEN get_count_wp;
2303 FETCH get_count_wp into l_cnt;
2304 CLOSE get_count_wp;
2305
2306 END IF;
2307
2308 IF l_cnt > 0 THEN
2309 return 'Y';
2310 END IF;
2311
2312 return 'N';
2313 END Check_subproject_link_exists;
2314
2315
2316 end PA_RELATIONSHIP_UTILS;
[Home] [Help]