1 package body PA_RELATIONSHIP_UTILS as
2 /*$Header: PAXRELUB.pls 120.18.12000000.2 2007/06/28 16:19:32 rmandali 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,
307 x_err_stage => l_err_stage
308 );
309 IF (l_err_code <> 0) THEN
310
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;
356 (l_financial_to = 'N') THEN
353
354 If (l_financial_from = 'Y') and
355 (l_workplan_to = 'Y') and
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
495 ,p_element_version_id_to IN NUMBER
492 procedure Check_Create_Association_Ok
493 (
494 p_element_version_id_from 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 ;
555 l_return_value VARCHAR2(1) := 'N';
556 BEGIN
557
558 OPEN cur_parent_lp_link;
559 FETCH cur_parent_lp_link INTO l_return_value;
560 CLOSE cur_parent_lp_link;
561
562 RETURN l_return_value;
563 END parent_LP_link_exists;
564
565 -- API name : check_create_intra_dep_ok
566 -- Type : Private Check procedure
567 -- Pre-reqs : None
568 -- Return Value : Returns error status
569 -- Parameters
570 -- p_pre_project_id IN NUMBER
571 -- p_pre_task_ver_id IN NUMBER
572 -- p_project_id IN NUMBER
573 -- p_task_ver_id IN NUMBER
574 --
575 --
576 -- History
577 --
578 -- 19-DEC-03 Maansari -Created
579 --
580 -- Description
581 --
582 -- This check procedure check s the following business rules and returns status 'E' with proper
583 -- error message if any of the rules fails.
584 --a. No duplicates.
585 --b. No circular dependencies between two or more tasks.
586 --c. A task cannot depend on itself.
587 --d. You cannot create a dependency from an object (predecessor) to you (successor) if that object has
588 -- subtasks (successor) that depend on you (predecessor).
589 --e. You cannot create a dependency between objects that are in the same direct path from lowest
590 -- node to the top node.
591 --
592 -- Notes: The p_pre_<> paramaters are for predecessor tasks and stored in object_id_to1 colunmn
593 -- of pa_object_relationships.
594
595 procedure check_create_intra_dep_ok(
596 p_pre_project_id IN NUMBER
597 ,p_pre_task_ver_id IN NUMBER
598 ,p_project_id IN NUMBER
599 ,p_task_ver_id IN NUMBER
600 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
601 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
602 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
603 ) IS
604
605 CURSOR cur_a
606 IS
607 SELECT 'x'
608 FROM pa_object_relationships
609 WHERE object_id_from1 = p_task_ver_id
610 AND object_id_from2 = p_project_id
611 AND object_id_to1 = p_pre_task_ver_id
612 AND object_id_to2 = p_pre_project_id
613 AND relationship_type = 'D'
614 ;
615
616 CURSOR cur_b
617 IS
618 SELECT 'x'
619 FROM (
620 SELECT object_id_to1
621 FROM pa_object_relationships por2
625 AND relationship_type = 'D' --bug 3944567
622 WHERE relationship_type = 'D'
623 AND por2.object_id_from2 = por2.object_id_to2 --Bug 3629024
624 START WITH por2.object_id_from1 = p_pre_task_ver_id
626 CONNECT BY por2.object_id_from1 = PRIOR por2.object_id_to1
627 AND relationship_type = PRIOR relationship_type
628 AND relationship_type = 'D'
629 AND por2.object_id_from2 = PRIOR por2.object_id_from2 ) --Bug 3629024
630 where object_id_to1 = p_task_ver_id;
631
632 /* the successor is a prdecessor of the sub-tasks of the predecessor.*/
633 /*Commented out for bug 3629024
634 CURSOR cur_d
635 IS
636 SELECT 'x'
637 FROM pa_object_relationships por1
638 WHERE por1.relationship_type = 'D'
639 AND por1.object_id_to1 = p_task_ver_id
640 AND por1.object_id_from1 IN
641 ( SELECT por2.object_id_to1
642 FROM pa_object_relationships por2
643 START WITH por2.object_id_from1 = p_pre_task_ver_id
644 CONNECT BY por2.object_id_from1 = prior por2.object_id_to1
645 AND por2.relationship_type = prior por2.relationship_type
646 AND por2.relationship_type = 'S')
647 ;
648 */
649 CURSOR cur_e1_get_parent( c_child_task_ver_id NUMBER )
650 IS
651 SELECT object_id_from1
652 FROM pa_object_relationships
653 where object_id_to1 = c_child_task_ver_id
654 and relationship_type = 'S'
655 ;
656
657 l_dummy_char VARCHAR2(1);
658 l_child_task_ver_id NUMBER;
659 l_parent_task_ver_id NUMBER;
660 BEGIN
661
662 IF p_pre_project_id IS NULL OR
663 p_pre_task_ver_id IS NULL OR
664 p_project_id IS NULL OR
665 p_task_ver_id IS NULL
666 THEN
667 x_return_status := FND_API.G_RET_STS_ERROR;
668 x_msg_count := FND_MSG_PUB.count_msg;
669 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
670 ,p_msg_name => 'PA_PS_PARAMS_NULL');
671 raise FND_API.G_EXC_ERROR;
672 END IF;
673
674 IF p_pre_project_id <> p_project_id
675 THEN
676 x_return_status := FND_API.G_RET_STS_ERROR;
677 x_msg_count := FND_MSG_PUB.count_msg;
678 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
679 ,p_msg_name => 'PA_PS_NOT_INTRA_DEPND');
680 raise FND_API.G_EXC_ERROR;
681 END IF;
682
683 --c) a task cannot depend on it-self
684 IF p_pre_task_ver_id = p_task_ver_id
685 THEN
686 x_return_status := FND_API.G_RET_STS_ERROR;
687 x_msg_count := FND_MSG_PUB.count_msg;
688 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
689 ,p_msg_name => 'PA_PS_NO_SELF_DEPDN');
690 raise FND_API.G_EXC_ERROR;
691 END IF;
692
693 OPEN cur_a;
694 FETCH cur_a INTO l_dummy_char;
695 IF cur_a%FOUND
696 THEN
697 x_return_status := FND_API.G_RET_STS_ERROR;
698 x_msg_count := FND_MSG_PUB.count_msg;
699 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
700 ,p_msg_name => 'PA_PS_DEPND_EXISTS');
701 raise FND_API.G_EXC_ERROR;
702 END IF;
703 CLOSE cur_a;
704
705 OPEN cur_b;
706 FETCH cur_b INTO l_dummy_char;
707 IF cur_b%FOUND
708 THEN
709 x_return_status := FND_API.G_RET_STS_ERROR;
710 x_msg_count := FND_MSG_PUB.count_msg;
711 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
712 ,p_msg_name => 'PA_PS_CIRCLR_DEPND_EXISTS');
713 raise FND_API.G_EXC_ERROR;
714 END IF;
715 CLOSE cur_b;
716
717 /* Following code commented out for bug 3629024
718 OPEN cur_d;
719 FETCH cur_d INTO l_dummy_char;
720 IF cur_d%FOUND
721 THEN
722 x_return_status := FND_API.G_RET_STS_ERROR;
723 x_msg_count := FND_MSG_PUB.count_msg;
724 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
725 ,p_msg_name => 'PA_PS_SUBTASKS_DEPND_EXISTS');
726 raise FND_API.G_EXC_ERROR;
727 END IF;
728 CLOSE cur_d;
729 */
730 --Bug 3629024 : Check for the existence of a closed path
731 --This bug fix is for rule D
732 IF get_parents_childs(p_pre_task_ver_id, p_task_ver_id) = TRUE THEN
733 x_return_status := FND_API.G_RET_STS_ERROR;
734 x_msg_count := FND_MSG_PUB.count_msg;
735 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
736 ,p_msg_name => 'PA_PS_SUBTASKS_DEPND_EXISTS');
737 raise FND_API.G_EXC_ERROR;
738 END IF;
739
740 /* --check if predecessor is parent in the same line of hierarchy.
741 OPEN cur_e1;
742 FETCH cur_e1 INTO l_dummy_char;
743 IF cur_e1%FOUND
744 THEN
745 x_return_status := FND_API.G_RET_STS_ERROR;
746 x_msg_count := FND_MSG_PUB.count_msg;
747 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
751 CLOSE cur_e1;
748 ,p_msg_name => 'PA_PS_NO_PARENT_PRED');
749 raise FND_API.G_EXC_ERROR;
750 END IF;
752
753 --check if predecessor is child in the same line of hierarchy.
754 OPEN cur_e2;
755 FETCH cur_e2 INTO l_dummy_char;
756 IF cur_e2%FOUND
757 THEN
758 x_return_status := FND_API.G_RET_STS_ERROR;
759 x_msg_count := FND_MSG_PUB.count_msg;
760 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
761 ,p_msg_name => 'PA_PS_NO_CHILD_PRED');
762 raise FND_API.G_EXC_ERROR;
763 END IF;
764 CLOSE cur_e2;
765 */
766
767 /* check for predecessor is parent */
768 l_child_task_ver_id := p_task_ver_id;
769 WHILE ( l_child_task_ver_id IS NOT NULL ) LOOP
770 OPEN cur_e1_get_parent( l_child_task_ver_id);
771 FETCH cur_e1_get_parent INTO l_parent_task_ver_id; /* predecessor is parent */
772 IF cur_e1_get_parent%NOTFOUND THEN
773 close cur_e1_get_parent;
774 exit;
775 END IF;
776 CLOSE cur_e1_get_parent;
777
778 IF l_parent_task_ver_id IS NOT NULL AND
779 l_parent_task_ver_id = p_pre_task_ver_id
780 THEN
781 x_return_status := FND_API.G_RET_STS_ERROR;
782 x_msg_count := FND_MSG_PUB.count_msg;
783 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
784 ,p_msg_name => 'PA_PS_NO_PARENT_PRED');
785 raise FND_API.G_EXC_ERROR;
786 ELSE
787 l_child_task_ver_id := l_parent_task_ver_id;
788 END IF;
789 END LOOP;
790
791
792 /* check for predecessor is child or successor is a parent */
793 /* starting from predecessor, find out the parent up the hierarchy and compare the
794 successor with the parent found. if successor is same as the parent found then it means
795 the predecessor is a child of the succeesor down the line in the hierarchy */
796 /* it is not possible to traverse down the hierarchy to find out whether the predecessor is a
797 child in the same line of successsor starting from the suceesor therefore traversing up
798 the hierarchy starting from predecessor*/
799
800 l_child_task_ver_id := p_pre_task_ver_id;
801 WHILE ( l_child_task_ver_id IS NOT NULL ) LOOP
802 OPEN cur_e1_get_parent( l_child_task_ver_id);
803 FETCH cur_e1_get_parent INTO l_parent_task_ver_id;
804 IF cur_e1_get_parent%NOTFOUND THEN
805 close cur_e1_get_parent;
806 exit;
807 END IF;
808 CLOSE cur_e1_get_parent;
809
810 IF l_parent_task_ver_id IS NOT NULL AND
811 l_parent_task_ver_id = p_task_ver_id /* is succssor a parent in the same line of hierarcgy*/
812 THEN
813 x_return_status := FND_API.G_RET_STS_ERROR;
814 x_msg_count := FND_MSG_PUB.count_msg;
815 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
816 ,p_msg_name => 'PA_PS_NO_CHILD_PRED');
817 raise FND_API.G_EXC_ERROR;
818 ELSE
819 l_child_task_ver_id := l_parent_task_ver_id;
820 END IF;
821 END LOOP;
822
823
824 EXCEPTION
825 when FND_API.G_EXC_ERROR then
826 x_return_status := FND_API.G_RET_STS_ERROR;
827 x_msg_count := FND_MSG_PUB.count_msg;
828 when OTHERS then
829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
830 x_msg_count := 1; -- 4537865 : RESET OUT param
831 x_msg_data := SUBSTRB(SQLERRM,1,240); -- 4537865 : RESET OUT PARAM
832 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_UTILS',
833 p_procedure_name => 'check_create_intra_dep_ok',
834 p_error_text => x_msg_data ); -- 4537865
835 raise;
836
837 END check_create_intra_dep_ok;
838
839 -- API name : check_create_intra_dep_ok
840 -- Type : Private Check procedure
841 -- Pre-reqs : None
842 -- Return Value : Returns error status
843 -- Parameters
844 -- p_pre_project_id IN NUMBER
845 -- p_pre_task_ver_id IN NUMBER
846 -- p_project_id IN NUMBER
847 -- p_task_ver_id IN NUMBER
848 --
849 --
850 -- History
851 --
852 -- 19-DEC-03 Maansari -Created
853 --
854 -- Description
855 --
856 -- This check procedure check s the following business rules and returns status 'E' with proper
857 -- error message if any of the rules fails.
858 --a. No duplicates.
859 -- Notes: The p_pre_<> paramaters are for predecessor tasks and stored in object_id_to1 colunmn
860 -- of pa_object_relationships.
861
862 procedure check_create_inter_dep_ok(
863 p_pre_project_id IN NUMBER
864 ,p_pre_task_ver_id IN NUMBER
865 ,p_project_id IN NUMBER
866 ,p_task_ver_id IN NUMBER
867 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
868 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
869 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
870 ) IS
871
872 CURSOR cur_a
873 IS
874 SELECT 'x'
878 AND object_id_to1 = p_pre_task_ver_id
875 FROM pa_object_relationships
876 WHERE object_id_from1 = p_task_ver_id
877 AND object_id_from2 = p_project_id
879 AND object_id_to2 = p_pre_project_id
880 AND relationship_type = 'D'
881 ;
882
883 CURSOR CUR_valid_sucsr_proj_task
884 IS
885 SELECT 'x'
886 FROM pa_proj_element_versions
887 WHERE project_id = p_project_id
888 AND element_version_id = p_task_ver_id
889 ;
890
891
892 CURSOR CUR_valid_pred_proj_task
893 IS
894 SELECT 'x'
895 FROM pa_proj_element_versions
896 WHERE project_id = p_pre_project_id
897 AND element_version_id = p_pre_task_ver_id
898 ;
899
900 l_dummy_char VARCHAR2(1);
901
902 -- Begin fix for Bug # Bug # 4256435.
903
904 cursor cur_sub_proj_hierarchy(c_pre_project_id NUMBER) is
905 -- This query selects all the parent projects of the predecessor project.
906 select por.object_id_from1 task_ver_id, por.object_id_from2 project_id
907 from pa_object_relationships por
908 where por.relationship_type in ('LW', 'LF')
909 start with por.object_id_to2 = c_pre_project_id
910 connect by prior por.object_id_from2 = por.object_id_to2
911 and prior por.relationship_type = por.relationship_type
912 and por.relationship_type in ('LW', 'LF')
913 union all
914 -- This query selects all the child projects of the predecessor project.
915 select por.object_id_to1 task_ver_id, por.object_id_to2 project_id
916 from pa_object_relationships por
917 where por.relationship_type in ('LW', 'LF')
918 start with por.object_id_from2 = c_pre_project_id
919 connect by prior por.object_id_to2 = por.object_id_from2
920 and prior por.relationship_type = por.relationship_type
921 and por.relationship_type in ('LW', 'LF');
922
923 rec_sub_proj_hierarchy cur_sub_proj_hierarchy%ROWTYPE;
924
925 cursor cur_linking_task(c_task_ver_id NUMBER, c_linking_task_ver_id NUMBER) is
926 select 'Y'
927 from pa_object_relationships por
928 where por.object_id_from1 = c_task_ver_id
929 and por.object_id_to1 = c_linking_task_ver_id
930 and por.relationship_type = 'S';
931
932 l_link_exists VARCHAR2(1) := 'N';
933
934 -- End fix for Bug # Bug # 4256435.
935
936 BEGIN
937
938 x_return_status := FND_API.G_RET_STS_SUCCESS;
939
940 --check valid for null parameters
941 IF p_pre_project_id IS NULL OR
942 p_pre_task_ver_id IS NULL OR
943 p_project_id IS NULL OR
944 p_task_ver_id IS NULL
945 THEN
946 x_return_status := FND_API.G_RET_STS_ERROR;
947 x_msg_count := FND_MSG_PUB.count_msg;
948 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
949 ,p_msg_name => 'PA_PS_PARAMS_NULL');
950 raise FND_API.G_EXC_ERROR;
951 END IF;
952
953 --check for inter projects. The successor project and predecssor projects should be different.
954 IF p_pre_project_id = p_project_id
955 THEN
956 x_return_status := FND_API.G_RET_STS_ERROR;
957 x_msg_count := FND_MSG_PUB.count_msg;
958 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
959 ,p_msg_name => 'PA_PS_NOT_INTER_DEPND');
960 raise FND_API.G_EXC_ERROR;
961 END IF;
962
963 -- Begin fix for Bug # Bug # 4256435.
964
965 for rec_sub_proj_hierarchy in cur_sub_proj_hierarchy(p_pre_project_id)
966 loop
967
968 if (rec_sub_proj_hierarchy.project_id = p_project_id) then
969
970 open cur_linking_task(p_task_ver_id, rec_sub_proj_hierarchy.task_ver_id);
971 fetch cur_linking_task into l_link_exists;
972 close cur_linking_task;
973
974 if (l_link_exists = 'Y') then
975
976 x_return_status := FND_API.G_RET_STS_ERROR;
977 x_msg_count := FND_MSG_PUB.count_msg;
978
979 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
980 , p_msg_name => 'PA_WP_PRGM_EXISTS_NO_DEP');
981
982 raise FND_API.G_EXC_ERROR;
983
984 end if;
985 end if;
986
987 end loop;
988
989 -- End fix for Bug # Bug # 4256435.
990
991 /* do we really need this validation here?
992 --validate successor project id and task ver id combination.
993 OPEN cur_valid_sucsr_proj_task;
994 FETCH cur_valid_sucsr_proj_task INTO l_dummy;
995 IF cur_valid_sucsr_proj_task%NOTFOUND
996 THEN
997 x_return_status := FND_API.G_RET_STS_ERROR;
998 x_msg_count := FND_MSG_PUB.count_msg;
999 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1000 ,p_msg_name => 'PA_PS_INV_SUCSR_PRJ_TSK');
1001 raise FND_API.G_EXC_ERROR;
1002 END IF;
1003 CLOSE cur_valid_sucsr_proj_task;
1004
1005 --validate predecessor project id and task ver id combination.
1006 OPEN cur_valid_pred_proj_task;
1007 FETCH cur_valid_pred_proj_task INTO l_dummy;
1011 x_msg_count := FND_MSG_PUB.count_msg;
1008 IF cur_valid_pred_proj_task%NOTFOUND
1009 THEN
1010 x_return_status := FND_API.G_RET_STS_ERROR;
1012 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1013 ,p_msg_name => 'PA_PS_INV_PRED_PRJ_TSK');
1014 raise FND_API.G_EXC_ERROR;
1015 END IF;
1016 CLOSE cur_valid_pred_proj_task;
1017 */
1018
1019 --check for duplicate dependency.
1020 OPEN cur_a;
1021 FETCH cur_a INTO l_dummy_char;
1022 IF cur_a%FOUND
1023 THEN
1024 x_return_status := FND_API.G_RET_STS_ERROR;
1025 x_msg_count := FND_MSG_PUB.count_msg;
1026 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
1027 ,p_msg_name => 'PA_PS_DEPND_EXISTS');
1028 raise FND_API.G_EXC_ERROR;
1029 END IF;
1030 CLOSE cur_a;
1031
1032 EXCEPTION
1033 when FND_API.G_EXC_ERROR then
1034 x_return_status := FND_API.G_RET_STS_ERROR;
1035 x_msg_count := FND_MSG_PUB.count_msg;
1036 when OTHERS then
1037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1038 x_msg_count := 1; -- 4537865 : RESET OUT param
1039 x_msg_data := SUBSTRB(SQLERRM,1,240); -- 4537865 : RESET OUT PARAM
1040 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_RELATIONSHIOP_UTILS',
1041 p_procedure_name => 'check_create_inter_dep_ok',
1042 p_error_text => x_msg_data); -- 4537865
1043 raise;
1044
1045 END check_create_inter_dep_ok;
1046
1047
1048 FUNCTION DISPLAY_PREDECESSORS
1049 ( p_element_version_id IN NUMBER)
1050 RETURN VARCHAR2 IS
1051 --
1052 -- To modify this template, edit file FUNC.TXT in TEMPLATE
1053 -- directory of SQL Navigator
1054 --
1055 -- Purpose: Briefly explain the functionality of the function
1056 --
1057 -- MODIFICATION HISTORY
1058 -- Person Date Comments
1059 -- --------- ------ -------------------------------------------
1060 -- SMUKKA 01/28/2004 Initial Version
1061
1062 l_task_name_list VARCHAR2(1220):=NULL; --bug 4141897
1063 l_task_count NUMBER:=0;
1064 CURSOR cur_task_names( c_element_version_id NUMBER )
1065 IS
1066 SELECT ppe.name task_name
1067 FROM pa_proj_element_versions ppev,
1068 pa_proj_elements ppe
1069 WHERE ppev.proj_element_id = ppe.proj_element_id
1070 and ppev.project_id = ppe.project_id
1071 AND ppev.element_version_id IN (SELECT object_id_to1
1072 FROM pa_object_relationships por
1073 WHERE por.object_id_from1 = c_element_version_id
1074 AND por.object_id_from2 = por.object_id_to2
1075 AND por.object_type_from = 'PA_TASKS' --4141109 Replaced LIKE with Equijoin
1076 AND por.object_type_to = 'PA_TASKS' --4141109 Replaced LIKE with Equijoin
1077 AND por.relationship_type = 'D'); --4141109 Replaced LIKE with Equijoin
1078 l_cur_task_names_rec cur_task_names%ROWTYPE;
1079 BEGIN
1080 OPEN cur_task_names(p_element_version_id);
1081 LOOP
1082 FETCH cur_task_names into l_cur_task_names_rec;
1083 IF cur_task_names%NOTFOUND THEN
1084 EXIT;
1085 ELSE
1086 l_task_count:=l_task_count+1;
1087 IF l_task_count = 1 THEN
1088 l_task_name_list:= l_task_name_list||l_cur_task_names_rec.task_name;
1089 ELSIF l_task_count > 5 THEN
1090 l_task_name_list:= l_task_name_list||'...';
1091 ELSE
1092 l_task_name_list := l_task_name_list||','||l_cur_task_names_rec.task_name;
1093 END IF;
1094 END IF;
1095 END LOOP;
1096 CLOSE cur_task_names;
1097 RETURN l_task_name_list ;
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 NULL ;
1101 END DISPLAY_PREDECESSORS;
1102
1103
1104 FUNCTION ChecK_dep_exists(p_element_version_id IN NUMBER)
1105 RETURN VARCHAR2
1106 IS
1107 CURSOR get_dependency IS
1108 SELECT 1 from pa_object_relationships
1109 where relationship_type = 'D'
1110 and (object_id_from1 = p_element_version_id OR
1111 object_id_to1 = p_element_version_id);
1112 l_dummy NUMBER;
1113 BEGIN
1114 OPEN get_Dependency;
1115 FETCH get_Dependency into l_dummy;
1116 IF get_dependency%FOUND THEN
1117 CLOSE get_dependency;
1118 return 'Y';
1119 END IF;
1120 CLOSE get_Dependency;
1121 return 'N';
1122 END CHECK_DEP_EXISTS;
1123
1124
1125 FUNCTION Is_Proj_Top_Program(p_project_id IN NUMBER)
1126 RETURN VARCHAR2
1127 IS
1128 --Bug No 3634315 Performance Fix, to avoid full table scan on pa_object_relationships table.
1129 /* CURSOR c1 IS
1130 select 1
1131 from pa_object_relationships
1132 where relationship_type IN ('LW', 'LF')
1133 and object_id_to2 = p_project_id;*/
1134 CURSOR c1 IS
1135 select 1
1136 from pa_object_relationships por,
1137 pa_proj_element_versions ppev
1138 where por.relationship_type IN ('LW', 'LF')
1139 and ppev.element_version_id = por.object_id_to1
1140 and por.object_id_to2 = ppev.project_id
1144 select 1
1141 and por.object_id_to2 = p_project_id;
1142
1143 CURSOR c2 IS
1145 from pa_object_relationships
1146 where relationship_type IN ('LW', 'LF')
1147 and object_id_from2 = p_project_id;
1148
1149 l_dummy number;
1150 BEGIN
1151 OPEN c1;
1152 FETCH c1 into l_dummy;
1153 IF c1%FOUND THEN
1154 CLOSE c1;
1155 return 'N';
1156 END IF;
1157 CLOSE c1;
1158
1159 OPEN c2;
1160 FETCH c2 into l_dummy;
1161 IF c2%NOTFOUND THEN
1162 CLOSE c2;
1163 return 'N';
1164 END IF;
1165 CLOSE c2;
1166
1167 return 'Y';
1168 END Is_Proj_Top_Program;
1169
1170 FUNCTION Is_Proj_Sub_Project(p_project_id IN NUMBER)
1171 RETURN VARCHAR2
1172 IS
1173 --Bug No 3634315 Performance Fix, to avoid full table scan on pa_object_relationships table.
1174 /* CURSOR c1 IS
1175 select 1
1176 from pa_object_relationships
1177 where relationship_type IN ('LW', 'LF')
1178 and object_id_to2 = p_project_id;*/
1179 CURSOR c1 IS
1180 select 1
1181 from pa_object_relationships por,
1182 pa_proj_element_versions ppev
1183 where por.relationship_type IN ('LW', 'LF')
1184 and ppev.element_version_id = por.object_id_to1
1185 and por.object_id_to2 = ppev.project_id
1186 and por.object_id_to2 = p_project_id;
1187 l_dummy NUMBER;
1188 BEGIN
1189 open c1;
1190 FETCH c1 into l_dummy;
1191 IF c1%NOTFOUND THEN
1192 CLOSE c1;
1193 return 'N';
1194 END IF;
1195 CLOSE c1;
1196
1197 return 'Y';
1198 END Is_Proj_Sub_Project;
1199
1200 FUNCTION DISABLE_SYS_PROG_OK(p_project_id NUMBER)
1201 RETURN varchar2
1202 IS
1203 CURSOR c1 IS
1204 select 1
1205 from pa_object_relationships
1206 where relationship_type IN ('LW', 'LF')
1207 and object_id_from2 = p_project_id;
1208 l_dummy NUMBER;
1209 BEGIN
1210 OPEN c1;
1211 FETCH c1 into l_dummy;
1212 IF c1%found then
1213 CLOSE c1;
1214 return 'N';
1215 END IF;
1216 CLOSE c1;
1217 return 'Y';
1218 END DISABLE_SYS_PROG_OK;
1219
1220 FUNCTION DISABLE_MULTI_PROG_OK(p_project_id NUMBER)
1221 RETURN varchar2
1222 IS
1223 --Bug No 3634315 Performance Fix, to avoid full table scan on pa_object_relationships table.
1224 /* CURSOR c1(c_parent_project_id NUMBER, c_child_project_id NUMBER) IS
1225 select count(1)
1226 from pa_object_relationships
1227 where relationship_type IN ('LW', 'LF')
1228 and object_id_to2 = c_child_project_id
1229 and object_id_from1 <> c_parent_project_id;*/
1230 CURSOR c1(c_parent_project_id NUMBER, c_child_project_id NUMBER) IS
1231 select count(1)
1232 from pa_object_relationships por,
1233 pa_proj_element_versions ppev
1234 where relationship_type IN ('LW', 'LF')
1235 and ppev.element_version_id = object_id_to1
1236 and por.object_id_to2 = ppev.project_id
1237 and object_id_to2 = c_child_project_id
1238 and object_id_from2 <> c_parent_project_id;--bug 4244482
1239
1240 CURSOR c2 IS
1241 select object_id_from2, object_Id_to2
1242 from pa_object_relationships
1243 where relationship_type IN ('LW') --bug 3962849
1244 start with object_id_from2 = p_project_id
1245 and relationship_type = 'LW'
1246 connect by prior object_id_to2 = object_id_from2
1247 and prior relationship_type = relationship_type;
1248
1249 CURSOR c3 IS
1250 select object_id_from2, object_Id_to2
1251 from pa_object_relationships
1252 where relationship_type IN ('LF') --bug 3962849
1253 start with object_id_from2 = p_project_id
1254 and relationship_type = 'LF'
1255 connect by prior object_id_to2 = object_id_from2
1256 and prior relationship_type = relationship_type;
1257
1258 l_parent_proj_id NUMBER;
1259 l_child_proj_id NUMBER;
1260 l_count NUMBER;
1261 BEGIN
1262 OPEN c2;
1263 LOOP
1264 FETCH c2 into l_parent_proj_id, l_child_proj_id;
1265 EXIT WHEN C2%NOTFOUND;
1266 OPEN c1(l_parent_proj_id, l_child_proj_id);
1267 FETCH c1 INTO l_count;
1268 IF l_count > 0 THEN
1269 CLOSE c1;
1270 CLOSE c2;
1271 return 'N';
1272 END IF;
1273 CLOSE c1;
1274 END LOOP;
1275 CLOSE c2;
1276
1277 OPEN c3;
1278 LOOP
1279 FETCH c3 into l_parent_proj_id, l_child_proj_id;
1280 EXIT WHEN C3%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 c3;
1286 return 'N';
1287 END IF;
1288 CLOSE c1;
1289 END LOOP;
1290 CLOSE c3;
1291
1292 return 'Y';
1293 END DISABLE_MULTI_PROG_OK;
1294
1295 FUNCTION CREATE_SUB_PROJ_ASSO_OK(p_task_version_id NUMBER, p_project_id NUMBER,
1296 p_structure_type VARCHAR2 := 'WORKPLAN')
1297 RETURN VARCHAR2
1298 IS
1299 CURSOR get_project_id(c_element_version_id NUMBER) IS
1300 select project_id
1301 from pa_proj_element_versions
1302 where element_version_id = c_element_version_id;
1303
1307 where relationship_type IN ('LW')
1304 CURSOR get_loop1(c_project_id NUMBER) IS
1305 select object_Id_to2, object_id_from2 -- Fix for Bug # 4297715.
1306 from pa_object_relationships
1308 start with object_id_from2 = c_project_id
1309 connect by prior object_id_to2 = object_id_from2
1310 and prior relationship_type = relationship_type
1311 and relationship_type = 'LW';
1312
1313 CURSOR get_loop2(c_project_id NUMBER) IS
1314 select object_Id_to2, object_id_from2 -- Fix for Bug # 4297715.
1315 from pa_object_relationships
1316 where relationship_type IN ('LF')
1317 start with object_id_from2 = c_project_id
1318 connect by prior object_id_to2 = object_id_from2
1319 and prior relationship_type = relationship_type
1320 and relationship_type = 'LF';
1321 --
1322 -- Start of Bug 3621794
1323 CURSOR get_proj_prog_fl(c_project_id NUMBER) IS
1324 SELECT sys_program_flag
1325 FROM pa_projects_all
1326 WHERE project_id =c_project_id;
1327
1328 CURSOR get_parent_sub_proj(c_project_id NUMBER) IS
1329 SELECT ppa.sys_program_flag
1330 FROM pa_proj_element_versions ppev,
1331 pa_proj_elements ppe,
1332 pa_projects_all ppa
1333 WHERE ppe.project_id = ppev.project_id
1334 AND ppe.proj_element_id = ppev.proj_element_id
1335 AND ppev.object_type = 'PA_TASKS'
1336 AND ppe.object_type = 'PA_TASKS'
1337 AND ppe.project_id = ppa.project_id
1338 AND ppev.element_version_id IN (
1339 SELECT object_id_from1
1340 FROM pa_object_relationships
1341 WHERE relationship_type IN ('LW','LF')
1342 START WITH object_id_to2 = c_project_id
1343 AND object_type_to = 'PA_STRUCTURES'
1344 CONNECT BY object_id_from2 = prior object_id_to2
1345 and prior relationship_type = relationship_type
1346 AND relationship_type IN ('LW','LF')
1347 AND object_type_from = 'PA_TASKS');
1348 -- End of Bug 3621794
1349 --
1350 --bug 3893970
1351 CURSOR get_multi_rollup(c_project_id NUMBER) IS
1352 select nvl(ALLOW_MULTI_PROGRAM_ROLLUP,'N')
1353 from pa_projects_all
1354 where project_id = c_project_id;
1355
1356 CURSOR get_child_links(c_project_id NUMBER, c_parent_proj_id NUMBER) IS
1357 select distinct(object_id_from2)
1358 from pa_object_relationships a
1359 where a.relationship_type IN ('LW','LF')
1360 and a.object_id_to2 = c_project_id
1361 and a.object_id_from2 <> c_parent_proj_id -- Fix for Bug # 4297715.
1362 and exists (select 1 from PA_PROJ_ELEMENT_VERSIONS elv /* Added the exists for Bug 6148092 */
1363 where elv.element_version_id = a.object_id_from1
1364 and ((elv.PARENT_STRUCTURE_VERSION_ID =
1365 PA_PROJECT_STRUCTURE_UTILS.get_current_working_ver_id(elv.project_id))
1366 or (elv.PARENT_STRUCTURE_VERSION_ID =
1367 PA_PROJECT_STRUCTURE_UTILS.get_latest_wp_version(elv.project_id))
1368 )
1369 );
1370
1371 l_linked_parent_proj_id NUMBER;
1372 l_multi_rollup_flag VARCHAR2(1);
1373 l_dest_multi_rollup_flag VARCHAR2(1);
1374 --end bug 3893970
1375
1376 l_src_project_id NUMBER;
1377 l_dest_project_id NUMBER;
1378 l_proj_id NUMBER;
1379 l_proj_prog_fl VARCHAR2(1); --Bug 3621794
1380
1381 -- Begin Fix for Bug # 4297715.
1382
1383 cursor cur_get_status_code(c_task_version_id NUMBER) is
1384 select ppevs.status_code
1385 from pa_proj_elem_ver_structure ppevs, pa_proj_element_versions ppev
1386 where ppev.element_version_id = c_task_version_id
1387 and ppev.project_id = ppevs.project_id -- Bug # 4868867.
1388 and ppev.parent_structure_version_id = ppevs.element_version_id;
1389
1390 l_status_code VARCHAR2(150) := NULL;
1391
1392 l_proj_id_from NUMBER := NULL;
1393
1394 -- End Fix for Bug # 4297715.
1395
1396 -- Begin fix for Bug # Bug # 4256435.
1397
1398 cursor cur_dep_hierarchy(c_src_task_ver_id NUMBER) is
1399 -- This query selects all the successor projects of the source project.
1400 select por.object_id_from2 project_id
1401 from pa_object_relationships por
1402 where por.relationship_type = 'D'
1403 start with por.object_id_to1 = c_src_task_ver_id
1404 -- connect by prior por.object_id_from2 = por.object_id_to2 -- Fix for Bug # 4256435.
1405 connect by prior por.object_id_from1 = por.object_id_to1 -- Fix for Bug # 4256435.
1406 and prior por.relationship_type = por.relationship_type
1407 and por.relationship_type = 'D'
1408 union all
1409 -- This query selects all the predecessor projects of the source project.
1410 select por.object_id_to2 project_id
1411 from pa_object_relationships por
1412 where por.relationship_type = 'D'
1413 start with por.object_id_from1 = c_src_task_ver_id
1414 -- connect by prior por.object_id_to2 = por.object_id_from2 -- Fix for Bug # 4256435.
1415 connect by prior por.object_id_to1 = por.object_id_from1 -- Fix for Bug # 4256435.
1416 and prior por.relationship_type = por.relationship_type
1417 and por.relationship_type = 'D';
1418
1419 rec_dep_hierarchy cur_dep_hierarchy%ROWTYPE;
1420
1421 -- End fix for Bug # Bug # 4256435.
1422
1423 BEGIN
1424 OPEN get_project_id(p_task_version_id);
1428 -- Begin Fix for Bug # 4297715.
1425 FETCH get_project_id into l_src_project_id;
1426 CLOSE get_project_id;
1427
1429
1430 open cur_get_status_code(p_task_version_id);
1431 fetch cur_get_status_code into l_status_code;
1432 close cur_get_status_code;
1433
1434 -- End Fix for Bug # 4297715.
1435
1436
1437 l_dest_project_id := p_project_id;
1438
1439 IF (l_src_project_id = l_dest_project_id) THEN
1440 return 'N';
1441 END IF;
1442
1443 -- Begin fix for Bug # Bug # 4256435.
1444
1445 for rec_dep_hierarchy in cur_dep_hierarchy(p_task_version_id)
1446 loop
1447
1448 if (rec_dep_hierarchy.project_id = l_dest_project_id) then
1449
1450 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA'
1451 , p_msg_name => 'PA_WP_DEP_EXISTS_NO_PRGM');
1452
1453 return 'N';
1454
1455 end if;
1456
1457 end loop;
1458
1459 -- End fix for Bug # Bug # 4256435.
1460
1461 --bug 3893970
1462 OPEN get_multi_rollup(l_src_project_id);
1463 FETCH get_multi_rollup INTO l_multi_rollup_flag;
1464 CLOSE get_multi_rollup;
1465
1466 If l_multi_rollup_flag = 'N' THEN
1467 --need to check if new child is already linked
1468 OPEN get_child_links(l_dest_project_id, l_src_project_id);
1469 FETCH get_child_links INTO l_linked_parent_proj_id;
1470 IF get_child_links%FOUND THEN
1471 CLOSE get_child_links;
1472 return 'N';
1473 END IF;
1474 CLOSE get_child_links;
1475
1476 --need to check all new child to see if it has multi rollup = 'Y'
1477 FOR i IN get_loop1(l_dest_project_id) LOOP
1478 OPEN get_multi_rollup(i.object_id_to2);
1479 FETCH get_multi_rollup into l_dest_multi_rollup_flag;
1480 CLOSE get_multi_rollup;
1481
1482 IF (l_dest_multi_rollup_flag = 'Y') THEN
1483 return 'N';
1484 END IF;
1485 END LOOP;
1486
1487 FOR i IN get_loop2(l_dest_project_id) LOOP
1488 OPEN get_multi_rollup(i.object_id_to2);
1489 FETCH get_multi_rollup into l_dest_multi_rollup_flag;
1490 CLOSE get_multi_rollup;
1491
1492 IF (l_dest_multi_rollup_flag = 'Y') THEN
1493 return 'N';
1494 END IF;
1495 END LOOP;
1496 ELSE
1497 --If allow, need to check if new child has parent which does not allow
1498 --need to check all new child to see if its parent has multi rollup = 'N'
1499 OPEN get_child_links(l_dest_project_id, l_src_project_id);
1500 LOOP
1501 FETCH get_child_links INTO l_linked_parent_proj_id;
1502 EXIT WHEN get_child_links%NOTFOUND;
1503
1504 OPEN get_multi_rollup(l_linked_parent_proj_id);
1505 FETCH get_multi_rollup into l_dest_multi_rollup_flag;
1506 CLOSE get_multi_rollup;
1507
1508 IF (l_dest_multi_rollup_flag = 'N') THEN
1509 CLOSE get_child_links;
1510 return 'N';
1511 END IF;
1512 END LOOP;
1513 CLOSE get_child_links;
1514 END IF;
1515 --end bug 3893970
1516
1517 IF (p_structure_type = 'WORKPLAN') THEN
1518 OPEN get_loop1(l_src_project_id);
1519 LOOP
1520 FETCH get_loop1 INTO l_proj_id, l_proj_id_from; -- Fix for Bug # 4297715.
1521 EXIT when get_loop1%NOTFOUND;
1522
1523 IF ((l_proj_id = l_dest_project_id) -- Fix for Bug # 4297715.
1524
1525 -- Fix for Bug # 4297715. If the published versions of the source project and the destination
1526 -- project are linked in a parent and immediate child relationship respectively, we still allow
1527 -- the working version of the source project to be linked to the published version of the
1528 -- destination project.
1529
1530 and NOT((nvl(l_status_code,'X') = 'STRUCTURE_WORKING') -- Fix for Bug # 4297715.
1531 and (l_proj_id_from = l_src_project_id))) -- Fix for Bug # 4297715.
1532 THEN
1533 CLOSE get_loop1;
1534 return 'N';
1535 END If;
1536
1537 END LOOP;
1538 CLOSE get_loop1;
1539 END IF;
1540
1541 IF (p_structure_type = 'FINANCIAL') THEN
1542 OPEN get_loop2(l_src_project_id);
1543 LOOP
1544 FETCH get_loop2 INTO l_proj_id, l_proj_id_from; -- Fix for Bug # 4297715.
1545 EXIT when get_loop2%NOTFOUND;
1546
1547 IF ((l_proj_id = l_dest_project_id) -- Fix for Bug # 4297715.
1548
1549 -- Fix for Bug # 4297715. If the published versions of the source project and the destination
1550 -- project are linked in a parent and immediate child relationship respectively, we still allow
1551 -- the working version of the source project to be linked to the published version of the
1552 -- destination project.
1553
1554 and NOT((nvl(l_status_code,'X') = 'STRUCTURE_WORKING') -- Fix for Bug # 4297715.
1555 and (l_proj_id_from = l_src_project_id))) -- Fix for Bug # 4297715.
1556 THEN
1557 CLOSE get_loop2;
1558 return 'N';
1559 END If;
1560
1561 END LOOP;
1562 CLOSE get_loop2;
1563 END IF;
1564
1565 --IMP Note: Please add any new validation above this code
1566 --Imp Note: Let this be the last validation to be performed by this API.
1567 -- Start of Bug 3621794
1571 CLOSE get_proj_prog_fl;
1568 OPEN get_proj_prog_fl(l_src_project_id);
1569 FETCH get_proj_prog_fl INTO l_proj_prog_fl;
1570 IF l_proj_prog_fl = 'Y' THEN
1572 -- return 'N'; --Bug 3622177
1573 return 'Y'; --Bug 3622177
1574 END IF;
1575 CLOSE get_proj_prog_fl;
1576
1577 l_proj_prog_fl:='N';
1578 OPEN get_parent_sub_proj(l_src_project_id);
1579 LOOP
1580 FETCH get_parent_sub_proj INTO l_proj_prog_fl;
1581 IF get_parent_sub_proj%NOTFOUND THEN
1582 CLOSE get_parent_sub_proj;
1583 return 'N';
1584 END IF;
1585 -- EXIT WHEN get_parent_sub_proj%NOTFOUND;
1586 IF l_proj_prog_fl = 'Y' THEN
1587 CLOSE get_parent_sub_proj;
1588 -- return 'N'; --Bug 3622177
1589 return 'Y'; --Bug 3622177
1590 END IF;
1591 END LOOP;
1592 CLOSE get_parent_sub_proj;
1593 -- End of Bug 3621794
1594
1595 return 'Y';
1596 END CREATE_SUB_PROJ_ASSO_OK;
1597
1598 FUNCTION IS_AUTO_ROLLUP(p_project_id NUMBER)
1599 RETURN VARCHAR2
1600 IS
1601 cursor c1 is
1602 select ppwa.AUTO_ROLLUP_SUBPROJ_FLAG
1603 from pa_proj_workplan_attr ppwa,
1604 pa_proj_elements ppe,
1605 pa_proj_structure_types ppst,
1606 pa_structure_types pst
1607 where ppe.project_id = p_project_id
1608 and ppe.object_type = 'PA_STRUCTURES'
1609 and ppe.proj_element_id = ppst.proj_element_id
1610 and ppst.structure_type_id = pst.structure_type_id
1611 and pst.structure_type = 'WORKPLAN'
1612 and ppe.project_id = ppwa.project_id
1613 and ppe.proj_element_id = ppwa.proj_element_id;
1614 l_dummy VARCHAR2(1);
1615 BEGIN
1616 OPEN c1;
1617 FETCH c1 into l_dummy;
1618 CLOSE c1;
1619
1620 return l_dummy;
1621
1622 END IS_AUTO_ROLLUP;
1623
1624 FUNCTION Get_Latest_Parent_Ver_obj_Id(p_structure_ver_id NUMBER,
1625 p_task_id NUMBER
1626 , p_relationship_type VARCHAR2 := 'LW') -- Fix for Bug # 4471484.
1627 RETURN NUMBER
1628 IS
1629 CURSOR c1 IS
1630 select por2.object_relationship_id, por2.relationship_type, ppev.element_version_id
1631 from pa_object_relationships por1,
1632 pa_object_relationships por2,
1633 pa_proj_element_versions ppev,
1634 pa_proj_elements ppe
1635 where ppe.proj_element_id = p_task_id
1636 and ppe.proj_element_id = ppev.proj_element_id
1637 and ppe.project_id = ppev.project_id
1638 and ppev.element_version_id = por1.object_id_from1
1639 and por1.relationship_type = 'S'
1640 and por2.object_id_to1 = p_structure_ver_id
1641 and por2.object_id_from1 = por1.object_id_to1
1642 and por2.relationship_type = p_relationship_type -- IN ('LF', 'LW') -- Fix for Bug # 4471484.
1643 -- and rownum < 2 -- Fix for Bug # 4477118.
1644 order by ppev.element_version_id desc ; -- por2.relationship_type desc, -- Fix for Bug # 4477118.
1645
1646 /*
1647 select por1.object_id_from1
1648 from pa_object_relationships por1,
1649 pa_object_relationships por2
1650 where por2.object_id_to1 = p_structure_ver_id
1651 and por2.object_id_from1 = por1.object_id_to1
1652 and por2.relationship_type IN ('LF', 'LW')
1653 and rownum < 2
1654 order by por1.object_id_from1 desc;
1655 */
1656
1657 l_obj_rel_id NUMBER;
1658 l_element_ver_id NUMBER;
1659 l_obj_type VARCHAR2(30);
1660 BEGIN
1661 OPEN c1;
1662 FETCH c1 into l_obj_rel_id, l_obj_type, l_element_ver_id;
1663 CLOSE c1;
1664
1665 return l_obj_rel_id;
1666 END Get_Latest_Parent_Ver_obj_Id;
1667
1668 FUNCTION Get_Latest_Parent_Task_Ver_Id(p_structure_ver_id NUMBER,
1669 p_task_id NUMBER
1670 , p_relationship_type VARCHAR2 := 'LW')
1671 RETURN NUMBER
1672 IS
1673 CURSOR c1 IS
1674 select por2.object_relationship_id, por2.relationship_type, ppev.element_version_id
1675 from pa_object_relationships por1,
1676 pa_object_relationships por2,
1677 pa_proj_element_versions ppev,
1678 pa_proj_elements ppe
1679 where ppe.proj_element_id = p_task_id
1680 and ppe.proj_element_id = ppev.proj_element_id
1681 and ppe.project_id = ppev.project_id
1682 and ppev.element_version_id = por1.object_id_from1
1683 and por1.relationship_type = 'S'
1684 and por2.object_id_to1 = p_structure_ver_id
1685 and por2.object_id_from1 = por1.object_id_to1
1686 and por2.relationship_type = p_relationship_type
1687 order by ppev.element_version_id desc ;
1688
1689 l_obj_rel_id NUMBER;
1690 l_element_ver_id NUMBER;
1691 l_obj_type VARCHAR2(30);
1692 BEGIN
1693 OPEN c1;
1694 FETCH c1 into l_obj_rel_id, l_obj_type, l_element_ver_id;
1695 CLOSE c1;
1696
1697 return l_element_ver_id;
1698 END Get_Latest_Parent_Task_Ver_Id;
1699
1700
1701 FUNCTION Get_Latest_Child_Ver_Id(p_task_ver_id NUMBER)
1702 RETURN NUMBER
1703 IS
1704 CURSOR c1 IS
1705 select por2.object_id_to1
1706 from pa_object_relationships por1,
1707 pa_object_relationships por2
1708 where por1.object_id_from1 = p_task_ver_id
1712 order by por2.object_id_to1 desc;
1709 and por1.object_id_to1 = por2.object_id_from1
1710 and por2.relationship_type IN ('LF', 'LW')
1711 and rownum < 2
1713
1714 l_child_ver_id NUMBER;
1715 BEGIN
1716 OPEN c1;
1717 FETCH c1 into l_child_ver_id;
1718 CLOSE c1;
1719
1720 return l_child_ver_id;
1721 END Get_Latest_Child_Ver_Id;
1722
1723
1724 --============================================================================================
1725 /*Bug 3629024 : ## SHORT NOTE ON THE FOLL. TWO MUTUALLY RECURSIVE FUNCTIONS ##
1726 Treat the workplan structure as a directed graph, with nodes being the
1727 structure/tasks and links between the nodes being dependencies and/or
1728 parent/child task relationships.
1729 Dependency links are directed from sucessor to predecessor.
1730 Child Tasks are linked to their parents with a bi-directional link.
1731 Then, there should be no circular path for any node
1732
1733 The following two functions check for the existence of such a closed path.
1734 */
1735
1736 -- Function : get_predecessors
1737 -- Type : Mutually Recursive functions alongwith get_parents_childs
1738 -- Purpose : Retrieves tasks which are predecessor of the task passed to
1739 -- this function (p_src_task_ver_id)
1740 -- Return : Returns with TRUE if
1741 -- SUCCESSOR(p_orig_succ_task_ver_id) matches any of the retrieved
1742 -- predecessors
1743 -- Assumptions : We have started from the PREDECESSOR to which a dependency is
1744 -- trying to be created
1745 -- Parameters Type Required Description and Purpose
1746 -- --------------------------- ------ -------- --------------------------------------------------------
1747 -- p_src_task_ver_id NUMBER Y Task id for which predecessors are to be retieved and checked
1748 -- p_orig_succ_task_ver_id NUMBER Y Task id to which the retrieved predecessors are compared
1749 --
1750 -- Call the function as get_predecessors(PRE,SUCC) where
1751 -- PRE = predecessor to which a dependency is trying to be created
1752 -- SUCC = successor from which a dependency is trying to be created
1753 FUNCTION get_predecessors( p_src_task_ver_id IN NUMBER
1754 ,p_orig_succ_task_ver_id IN NUMBER ) RETURN BOOLEAN IS
1755 CURSOR cur_get_predecessors IS
1756 SELECT por1.object_id_to1 RELATED_TASK
1757 FROM pa_object_relationships por1
1758 WHERE por1.relationship_type = 'D'
1759 AND LEVEL = 1
1760 AND por1.object_id_from2 = por1.object_id_to2
1761 START WITH por1.object_id_from1 = p_src_task_ver_id
1762 CONNECT BY PRIOR por1.object_id_to1 = por1.object_id_from1
1763 AND PRIOR por1.relationship_type = por1.relationship_type
1764 AND PRIOR por1.object_id_from2 = por1.object_id_from2
1765 ;
1766 --NOTE : * PRIOR por1.relationship_type = por1.relationship_type
1767 -- is required so as to prevent traversing parent-child dependency relns.
1768 -- * PRIOR por1.object_id_from2 = por1.object_id_from2 in CONNECT clause
1769 -- is required to prevent traversing inter-project dependencies; which can be
1770 -- circular and can give a "ORA-01436: CONNECT BY loop in user data" error
1771 -- * por1.object_id_from2 = por1.object_id_to2 in WHERE clause
1772 -- is required to filter out only those rows which correspond to intra-proj dependencies
1773 BEGIN
1774 FOR rec_predecessors IN cur_get_predecessors LOOP
1775 IF rec_predecessors.related_task = p_orig_succ_task_ver_id
1776 OR get_parents_childs( rec_predecessors.related_task, p_orig_succ_task_ver_id ) = TRUE THEN
1777 RETURN TRUE;
1778 END IF;
1779 END LOOP;
1780
1781 RETURN FALSE;
1782 END get_predecessors;
1783
1784 -- Function : get_parents_childs
1785 -- Type : Mutually Recursive functions alongwith get_predecessors
1786 -- Purpose : Retrieves tasks which are parents or childs, of the task passed to
1787 -- this function (p_src_task_ver_id)
1788 -- Return : Returns with TRUE if
1789 -- SUCCESSOR(p_orig_succ_task_ver_id) matches any of the retrieved
1790 -- parents/childs
1791 -- Assumptions : We have started from the PREDECESSOR to which a dependency is
1792 -- trying to be created
1793 -- Parameters Type Required Description and Purpose
1794 -- --------------------------- ------ -------- --------------------------------------------------------
1795 -- p_src_task_ver_id NUMBER Y Task id for which parents/childs are to be retieved and checked
1796 -- p_orig_succ_task_ver_id NUMBER Y Task id to which the retrieved parents/childs are compared
1797 FUNCTION get_parents_childs( p_src_task_ver_id IN NUMBER
1798 ,p_orig_succ_task_ver_id IN NUMBER ) RETURN BOOLEAN IS
1799 CURSOR cur_get_parents_childs IS
1800 SELECT por1.object_id_to1 RELATED_TASK
1801 FROM pa_object_relationships por1
1802 WHERE por1.relationship_type = 'S'
1803 AND por1.relationship_subtype = 'TASK_TO_TASK'
1804 START WITH por1.object_id_from1 = p_src_task_ver_id
1805 AND relationship_type = 'S' --bug 3944567
1806 CONNECT BY PRIOR por1.object_id_to1 = por1.object_id_from1
1810 FROM pa_object_relationships por2
1807 AND PRIOR por1.relationship_type = por1.relationship_type
1808 UNION
1809 SELECT por2.object_id_from1 RELATED_TASK
1811 WHERE por2.relationship_type = 'S'
1812 AND por2.relationship_subtype = 'TASK_TO_TASK'
1813 START WITH por2.object_id_to1 = p_src_task_ver_id
1814 AND relationship_type = 'S' --bug 3944567
1815 CONNECT BY PRIOR por2.object_id_from1 = por2.object_id_to1
1816 AND PRIOR por2.relationship_type = por2.relationship_type
1817 UNION
1818 SELECT p_src_task_ver_id RELATED_TASK
1819 FROM dual
1820 ;
1821
1822 --bug 4145585
1823 Cursor check_intersect IS
1824 select object_id_from1 elem_ver_id
1825 from pa_object_relationships
1826 start with object_id_to1 = p_orig_succ_task_ver_id
1827 and relationship_type = 'D'
1828 connect by object_id_to1 = prior object_id_from1
1829 and relationship_type = prior relationship_type
1830 and object_id_to2 = object_id_from2
1831 INTERSECT
1832 select object_id_to1
1833 from pa_object_relationships
1834 start with object_id_to1 = p_src_task_ver_id
1835 and relationship_type = 'S'
1836 connect by prior object_id_to1 = object_id_from1
1837 and relationship_type = prior relationship_type;
1838 l_dummy NUMBER;
1839 --end bug 4145585
1840
1841 BEGIN
1842 --bug 4145585
1843 /*
1844 FOR rec_parents_childs IN cur_get_parents_childs LOOP
1845 IF rec_parents_childs.related_task = p_orig_succ_task_ver_id
1846 OR get_predecessors( rec_parents_childs.related_task, p_orig_succ_task_ver_id ) = TRUE THEN
1847 RETURN TRUE;
1848 END IF;
1849 END LOOP;
1850
1851 RETURN FALSE;
1852 */
1853 OPEN check_intersect;
1854 FETCH check_intersect into l_dummy;
1855 IF check_intersect%FOUND THEN
1856 CLOSE check_intersect;
1857 RETURN TRUE;
1858 END IF;
1859 CLOSE check_intersect;
1860
1861 RETURN FALSE;
1862 --end bug 4145585
1863
1864 END get_parents_childs;
1865 --============================================================================================
1866
1867 FUNCTION Check_link_exists(p_project_id number
1868 ,p_link_type VARCHAR2 DEFAULT 'SHARED' --bug 4532826
1869 ) return VARCHAR2
1870 IS
1871 CURSOR get_count IS
1872 Select count(1) from pa_object_relationships
1873 where relationship_type IN ('LW', 'LF')
1874 and (object_id_from2 = p_project_id or object_id_to2 = p_project_id);
1875
1876 -- bug 4532826
1877 CURSOR get_count_fn IS
1878 Select count(1) from pa_object_relationships
1879 where relationship_type = 'LF'
1880 and (object_id_from2 = p_project_id or object_id_to2 = p_project_id);
1881
1882 CURSOR get_count_wp IS
1883 Select count(1) from pa_object_relationships
1884 where relationship_type = 'LW'
1885 and (object_id_from2 = p_project_id or object_id_to2 = p_project_id);
1886 --end bug 4532826
1887
1888
1889 l_cnt NUMBER :=0;
1890 BEGIN
1891 IF p_link_type = 'SHARED' --bug 4532826
1892 THEN
1893 OPEN get_count;
1894 FETCH get_count into l_cnt;
1895 CLOSE get_count;
1896 --bug 4532826
1897 ELSIF p_link_type = 'FINANCIAL'
1898 THEN
1899
1900 OPEN get_count_fn;
1901 FETCH get_count_fn into l_cnt;
1902 CLOSE get_count_fn;
1903
1904 ELSIF p_link_type = 'WORKPLAN'
1905 THEN
1906
1907 OPEN get_count_wp;
1908 FETCH get_count_wp into l_cnt;
1909 CLOSE get_count_wp;
1910
1911 END IF;
1912 --bug end 4532826
1913
1914 IF l_cnt > 0 THEN
1915 return 'Y';
1916 END IF;
1917
1918 return 'N';
1919 END Check_link_exists;
1920
1921 FUNCTION Check_proj_currency_identical(p_src_project_id NUMBER
1922 , p_dest_project_id NUMBER) return VARCHAR2
1923 IS
1924 CURSOR get_proj_curr_code(c_project_id NUMBER) IS
1925 select project_currency_code
1926 from pa_projects_all
1927 where project_id = C_project_id;
1928 l_src_proj_currency_code VARCHAR2(15);
1929 l_dest_proj_currency_code VARCHAR2(15);
1930 BEGIN
1931 OPEN get_proj_curr_code(p_src_project_id);
1932 FETCH get_proj_curr_code INTO l_src_proj_currency_code;
1933 CLOSE get_proj_curr_code;
1934
1935 OPEN get_proj_curr_code(p_dest_project_id);
1936 FETCH get_proj_curr_code INTO l_dest_proj_currency_code;
1937 CLOSE get_proj_curr_code;
1938
1939 IF (l_src_proj_currency_code <> l_dest_proj_currency_code) THEN
1940 return 'N';
1941 END IF;
1942
1943 return 'Y';
1944 END check_proj_currency_identical;
1945
1946 FUNCTION check_dependencies_valid(p_new_parent_task_ver_id IN NUMBER
1947 ,p_task_ver_id IN NUMBER) RETURN VARCHAR2
1948 IS
1949 CURSOR get_parent_to_child IS
1950 select count(1)
1951 from pa_object_relationships
1952 where relationship_type = 'D'
1953 and object_id_from1 IN ( --get all tasks in upper branch
1954 select object_id_to1
1955 from pa_object_relationships
1956 start with object_id_to1 = p_new_parent_task_ver_id
1960 and prior object_type_from = object_type_to)
1957 and relationship_type = 'S'
1958 connect by prior object_id_from1 = object_id_to1
1959 and relationship_type = prior relationship_type
1961 and object_id_to1 IN ( --get all tasks in lower branch
1962 select object_id_to1
1963 from pa_object_relationships
1964 start with object_id_to1 = p_task_ver_id
1965 and relationship_type = 'S'
1966 connect by prior object_id_to1 = object_id_from1
1967 and relationship_type = prior relationship_type
1968 and prior object_type_to = object_type_from);
1969
1970 CURSOR get_child_to_parent IS
1971 select count(1)
1972 from pa_object_relationships
1973 where relationship_type = 'D'
1974 and object_id_from1 IN ( --get tasks in lower branch
1975 select object_id_to1
1976 from pa_object_relationships
1977 start with object_id_to1 = p_task_ver_id
1978 and relationship_type = 'S'
1979 connect by prior object_id_to1 = object_id_from1
1980 and relationship_type = prior relationship_type
1981 and prior object_type_to = object_type_from)
1982 and object_id_to1 IN ( --get tasks in upper branch
1983 select object_id_to1
1984 from pa_object_relationships
1985 start with object_id_to1 = p_new_parent_task_ver_id
1986 and relationship_type = 'S'
1987 connect by prior object_id_from1 = object_id_to1
1988 and relationship_type = prior relationship_type
1989 and prior object_type_from = object_type_to);
1990 l_cnt NUMBER;
1991
1992 BEGIN
1993 OPEN get_parent_to_child;
1994 FETCH get_parent_to_child INTO l_cnt;
1995 CLOSE get_parent_to_child;
1996 IF l_cnt > 0 THEN
1997 return 'N';
1998 END IF;
1999
2000 OPEN get_child_to_parent;
2001 FETCH get_child_to_parent INTO l_cnt;
2002 CLOSE get_child_to_parent;
2003 IF l_cnt > 0 THEN
2004 return 'N';
2005 END IF;
2006
2007 RETURN 'Y';
2008 END check_dependencies_valid;
2009
2010 -- Begin fix for Bug # 4266540.
2011
2012 FUNCTION check_task_has_sub_proj(p_project_id NUMBER
2013 , p_task_id NUMBER
2014 , p_task_version_id NUMBER := NULL)
2015 return VARCHAR2 is
2016
2017 cursor cur_sub_project (c_project_id NUMBER, c_task_id NUMBER, c_task_version_id NUMBER) is
2018 select count(pslv.sub_project_id)
2019 from pa_structures_links_v pslv
2020 where pslv.parent_project_id = c_project_id
2021 and pslv.parent_task_id = c_task_id
2022 and pslv.parent_task_version_id = c_task_version_id;
2023
2024 cursor cur_task_version_id (c_project_id NUMBER, c_task_id NUMBER, c_structure_version_id NUMBER) is
2025 select ppev.element_version_id
2026 from pa_proj_element_versions ppev
2027 where ppev.project_id = c_project_id
2028 and ppev.proj_element_id = c_task_id
2029 and ppev.parent_structure_version_id = c_structure_version_id;
2030
2031 l_cur_working_str_ver_id NUMBER := null;
2032
2033 l_task_version_id NUMBER := null;
2034
2035 l_count NUMBER := null;
2036
2037 l_return VARCHAR2(1) := null;
2038
2039 BEGIN
2040
2041 l_return := 'N';
2042
2043 if (p_task_version_id is null) then
2044
2045 -- The calling API in this case is: pa_task_utils.check_create_subtask_ok() which is only
2046 -- called for 'FINANCIAL' tasks.
2047
2048 l_cur_working_str_ver_id := pa_project_structure_utils.get_fin_struc_ver_id(p_project_id);
2049
2050 open cur_task_version_id(p_project_id, p_task_id, l_cur_working_str_ver_id);
2051 fetch cur_task_version_id into l_task_version_id;
2052 close cur_task_version_id;
2053
2054 else
2055
2056 l_task_version_id := p_task_version_id;
2057
2058 end if;
2059
2060 open cur_sub_project(p_project_id, p_task_id, l_task_version_id);
2061 fetch cur_sub_project into l_count;
2062 close cur_sub_project;
2063
2064 if nvl(l_count,0) > 0 then
2065
2066 l_return := 'Y';
2067
2068 end if;
2069
2070 return(l_return);
2071
2072 END check_task_has_sub_proj;
2073
2074 -- End fix for Bug # 4266540.
2075
2076 -- Begin fix for Bug # 4411603.
2077
2078 function is_str_linked_to_working_ver
2079 (p_project_id NUMBER
2080 , p_structure_version_id NUMBER
2081 , p_relationship_type VARCHAR2 := 'LW') return VARCHAR2
2082 is
2083
2084 l_return_value VARCHAR2(1) := null;
2085
2086 cursor cur_structure_version_ids (c_object_id_from NUMBER
2087 , c_relationship_type VARCHAR2) is
2088 -- Bug # 4757224.
2089
2090 select 1
2091 from dual
2092 where exists (select 1
2093 from pa_proj_element_versions pev, pa_object_relationships por, pa_proj_elem_ver_structure ppevs
2094 where pev.parent_structure_version_id = c_object_id_from
2095 and pev.element_version_id = por.object_id_from1
2096 and por.object_id_to1 = ppevs.element_version_id
2097 -- Bug Fix 5077552
2098 -- Adding the project id to avoid the FTS on ppevs.
2099 and por.object_id_to2 = ppevs.project_id
2100 and por.object_type_to = 'PA_STRUCTURES'
2101 and por.relationship_type in (c_relationship_type, 'S')
2102 and ppevs.status_code='STRUCTURE_WORKING');
2103
2104 /*
2105 -- Bug # 4737033.
2106
2107 select ppevs.status_code status_code
2108 from (select por.object_id_to1
2109 from pa_object_relationships por
2110 where por.object_type_to = 'PA_STRUCTURES'
2111 and relationship_type in (c_relationship_type, 'S')
2112 start with por.object_id_from1 = c_object_id_from
2113 connect by prior por.object_id_to1 = por.object_id_from1
2114 and prior relationship_type in (c_relationship_type, 'S')) por
2115 ,pa_proj_elem_ver_structure ppevs
2116 where
2117 por.object_id_to1 = ppevs.element_version_id (+);
2118
2119 select ppevs.status_code status_code
2120 from pa_object_relationships por, pa_proj_elem_ver_structure ppevs
2121 where por.object_id_to1 = ppevs.element_version_id (+)
2122 and por.object_type_to = 'PA_STRUCTURES'
2123 and relationship_type in (c_relationship_type, 'S')
2124 start with por.object_id_from1 = c_object_id_from
2125 connect by prior por.object_id_to1 = por.object_id_from1
2126 and prior relationship_type in (c_relationship_type, 'S');
2127
2128 -- Bug # 4737033.
2129 */
2130
2131 -- Bug # 4757224.
2132
2133 l_structure_working VARCHAR2(1) := null;
2134
2135 rec_structure_version_ids cur_structure_version_ids%rowtype;
2136
2137 begin
2138
2139 l_return_value := 'N';
2140
2141 if (pa_project_structure_utils.check_program_flag_enable(p_project_id) = 'Y') then -- Bug # 4742904.
2142
2143 -- Bug # 4757224.
2144
2145 open cur_structure_version_ids(p_structure_version_id, p_relationship_type);
2146 fetch cur_structure_version_ids into rec_structure_version_ids;
2147
2148 if cur_structure_version_ids%NOTFOUND then
2149 l_return_value:='N';
2150 else
2151 l_return_value:='Y';
2152 end if;
2153
2154 close cur_structure_version_ids;
2155
2156 /*
2157 for rec_structure_version_ids in cur_structure_version_ids(p_structure_version_id, p_relationship_type)
2158 loop
2159
2160 if (rec_structure_version_ids.status_code = 'STRUCTURE_WORKING') then
2161
2162 l_return_value := 'Y';
2163
2164 end if;
2165
2166 end loop;
2167 */
2168
2169 -- Bug # 4757224.
2170
2171 end if; -- Bug # 4742904.
2172
2173 return(l_return_value);
2174
2175 end is_str_linked_to_working_ver;
2176
2177 -- End fix for Bug # 4411603.
2178
2179 --bug 4541039
2180
2181 FUNCTION Check_parent_project_Exists
2182 (
2183 p_project_id NUMBER,
2184 p_structure_ver_id NUMBER
2185 ,p_link_type VARCHAR2 default 'SHARED' --bug 4541039
2186 )RETURN VARCHAR2
2187 IS
2188 CURSOR check_parentproj_exists IS
2189 SELECT '1'
2190 from pa_object_relationships por
2191 WHERE p_structure_ver_id = por.object_id_to1
2192 and por.object_id_to2 = p_project_id
2193 and por.relationship_type IN ('LW', 'LF');
2194
2195
2196 CURSOR check_parentproj_exists_wp IS
2197 SELECT '1'
2198 from pa_object_relationships por
2199 WHERE p_structure_ver_id = por.object_id_to1
2200 and por.object_id_to2 = p_project_id
2201 and por.relationship_type = 'LW';
2202
2203 CURSOR check_parentproj_exists_fn 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 = 'LF';
2209
2210 l_dummy VARCHAR2(1);
2211 BEGIN
2212 IF p_link_type = 'SHARED' --bug 4541039
2213 THEN
2214 OPEN check_parentproj_exists;
2215 FETCH check_parentproj_exists INTO l_dummy;
2216 IF check_parentproj_exists%NOTFOUND THEN
2217 CLOSE check_parentproj_exists;
2218 RETURN 'N';
2219 ELSE
2220 CLOSE check_parentproj_exists;
2221 RETURN 'Y';
2222 END IF;
2223 ELSIF p_link_type = 'WORKPLAN'
2224 THEN
2225 OPEN check_parentproj_exists_wp;
2226 FETCH check_parentproj_exists_wp INTO l_dummy;
2227 IF check_parentproj_exists_wp%NOTFOUND THEN
2228 CLOSE check_parentproj_exists_wp;
2229 RETURN 'N';
2230 ELSE
2231 CLOSE check_parentproj_exists_wp;
2232 RETURN 'Y';
2233 END IF;
2234 ELSIF p_link_type = 'FINANCIAL'
2235 THEN
2236 OPEN check_parentproj_exists_fn;
2237 FETCH check_parentproj_exists_fn INTO l_dummy;
2238 IF check_parentproj_exists_fn%NOTFOUND THEN
2239 CLOSE check_parentproj_exists_fn;
2240 RETURN 'N';
2241 ELSE
2242 CLOSE check_parentproj_exists_fn;
2243 RETURN 'Y';
2244 END IF;
2245 END IF;
2246
2247 END Check_parent_project_Exists;
2248
2249 --bug 4619824
2250 FUNCTION Check_subproject_link_exists(p_project_id number
2251 ,p_link_type VARCHAR2 DEFAULT 'SHARED' --bug 4532826
2252 ) return VARCHAR2
2253 IS
2254 CURSOR get_count IS
2255 Select count(1) from pa_object_relationships
2256 where relationship_type IN ('LW', 'LF')
2257 and object_id_from2 = p_project_id;
2258
2259 CURSOR get_count_fn IS
2260 Select count(1) from pa_object_relationships
2261 where relationship_type = 'LF'
2262 and object_id_from2 = p_project_id;
2263
2264 CURSOR get_count_wp IS
2265 Select count(1) from pa_object_relationships
2266 where relationship_type = 'LW'
2267 and (object_id_from2 = p_project_id);
2268
2269
2270 l_cnt NUMBER :=0;
2271 BEGIN
2272 IF p_link_type = 'SHARED'
2273 THEN
2274 OPEN get_count;
2275 FETCH get_count into l_cnt;
2276 CLOSE get_count;
2277 ELSIF p_link_type = 'FINANCIAL'
2278 THEN
2279
2280 OPEN get_count_fn;
2281 FETCH get_count_fn into l_cnt;
2282 CLOSE get_count_fn;
2283
2284 ELSIF p_link_type = 'WORKPLAN'
2285 THEN
2286
2287 OPEN get_count_wp;
2288 FETCH get_count_wp into l_cnt;
2289 CLOSE get_count_wp;
2290
2291 END IF;
2292
2293 IF l_cnt > 0 THEN
2294 return 'Y';
2295 END IF;
2296
2297 return 'N';
2298 END Check_subproject_link_exists;
2299
2300
2301 end PA_RELATIONSHIP_UTILS;
[Home] [Help]