1 package body PA_PROJECT_STRUCTURE_UTILS as
2 /*$Header: PAXSTCUB.pls 120.14.12010000.4 2008/12/19 10:39:52 vgovvala ship $*/
3
4 -- API name : Check_Delete_Structure_Ver_Ok
5 -- Type : Utils API
6 -- Pre-reqs : None
7 -- Return Value : N/A
8 -- Parameters
9 -- p_structure_version_id IN NUMBER
10 -- x_return_status OUT VARCHAR2
11 -- x_error_message_code OUT VARCHAR2
12 --
13 -- History
14 --
15 -- 25-JUN-01 HSIU -Created
16 --
17
18
19 procedure Check_Delete_Structure_Ver_Ok
20 (
21 p_project_id IN NUMBER
22 ,p_structure_version_id IN NUMBER
23 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
24 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
25 )
26 IS
27 l_user_id NUMBER;
28 l_person_id NUMBER;
29 l_dummy VARCHAR2(1);
30
31 cursor get_person_id(p_user_id NUMBER) IS
32 select p.person_id
33 from per_all_people_f p, fnd_user f
34 where f.employee_id = p.person_id
35 and sysdate between p.effective_start_date and p.effective_end_date
36 and f.user_id = p_user_id;
37
38 cursor get_lock_user(p_person_id NUMBER) IS
39 select '1'
40 from pa_proj_element_versions v, pa_proj_elem_ver_structure s
41 where v.element_version_id = p_structure_version_id
42 and v.project_id = s.project_id
43 and v.element_version_id = s.element_version_id
44 and (locked_by_person_id IS NULL
45 or locked_by_person_id = p_person_id);
46
47 cursor get_link IS
48 select '1'
49 from pa_object_relationships
50 where (object_id_from1 = p_structure_version_id
51 or object_id_to1 = p_structure_version_id)
52 and relationship_type = 'L';
53
54 BEGIN
55 l_user_id := FND_GLOBAL.USER_ID;
56
57 --get the current user's person_id
58 open get_person_id(l_user_id);
59 fetch get_person_id into l_person_id;
60 if get_person_id%NOTFOUND then
61 l_person_id := -1;
62 end if;
63 close get_person_id;
64
65 open get_lock_user(l_person_id);
66 fetch get_lock_user into l_dummy;
67 if get_lock_user%NOTFOUND then
68 --the structure version is locked by another user.
69 close get_lock_user;
70 x_return_status := FND_API.G_RET_STS_ERROR;
71 x_error_message_code := 'PA_PS_STRUC_VER_LOCKED';
72 return;
73 end if;
74 close get_lock_user;
75
76 --Check if this is a published version
77
78 --Commented by hsiu
79 --for Advanced Structure changes
80 --
81 -- If (Check_Struc_Ver_Published(p_project_id, p_structure_version_id) = 'Y') THEN
82 --version is published. Error.
83 -- x_return_status := FND_API.G_RET_STS_ERROR;
84 -- x_error_message_code := 'PA_PS_DEL_PUB_STRUC_ERR';
85 -- return;
86 -- END IF;
87
88 --Check if this is a billing/costing structure version
89 -- if it is, error if this is the only structure version.
90
91
92 --Check if this structure version has any links
93 open get_link;
94 fetch get_link into l_dummy;
95 if get_link%FOUND then
96 --a link exists
97 close get_link;
98 x_return_status := FND_API.G_RET_STS_ERROR;
99 x_error_message_code := 'PA_PS_LINK_EXISTS';
100 return;
101 end if;
102 close get_link;
103
104
105 EXCEPTION
106 WHEN OTHERS THEN
107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
108
109 -- 4537865 RESET OUT PARAMS
110 x_error_message_code := SQLERRM ;
111
112 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
113 p_procedure_name => 'Check_Delete_Structure_Ver_Ok');
114 RAISE;
115 END Check_Delete_Structure_Ver_Ok;
116
117 -- API name : Check_Structure_Name_Unique
118 -- Type : Utils API
119 -- Pre-reqs : None
120 -- Return Value : Y if not exists; N if exists.
121 -- Parameters
122 -- p_structure_name IN VARCHAR2
123 -- p_structure_id IN NUMBER
124 -- p_project_id IN NUMBER
125 --
126 -- History
127 --
128 -- 25-JUN-01 HSIU -Created
129 --
130 --
131
132
133 function Check_Structure_Name_Unique
134 (
135 p_structure_name IN VARCHAR2
136 ,p_structure_id IN NUMBER
137 ,p_project_id IN NUMBER
138 ) return VARCHAR2
139 IS
140 cursor c1 is
141 select 1 from pa_proj_elements
142 where project_id = p_project_id
143 and object_type = 'PA_STRUCTURES'
144 and name = p_structure_name;
145
146 cursor c2 IS
147 select 1 from pa_proj_elements
148 where project_id = p_project_id
149 and object_type = 'PA_STRUCTURES'
150 and name = p_structure_name
151 and proj_element_id <> p_structure_id;
152
153 l_dummy NUMBER;
154
155 BEGIN
156 if (p_project_id IS NULL or p_structure_name is NULL) then
157 return (null);
158 end if;
159
160 IF (p_structure_id IS NULL) THEN
161 open c1;
162 fetch c1 into l_dummy;
163 if c1%notfound THEN
164 close c1;
165 return('Y');
166 else
167 close c1;
168 return('N');
169 end if;
170 ELSE
171 open c2;
172 fetch c2 into l_dummy;
173 if c2%notfound THEN
174 close c2;
175 return ('Y');
176 else
177 close c2;
178 return('N');
179 end if;
180 END IF;
181 EXCEPTION
182 when others then
183 return (SQLCODE);
184 END Check_Structure_Name_Unique;
185
186
187 -- API name : Check_Struc_Ver_Name_Unique
188 -- Type : Utils API
189 -- Pre-reqs : None
190 -- Return Value : Y if not exists; N if exists.
191 -- Parameters
192 -- p_structure_version_name IN VARCHAR2
193 -- p_pev_structure_id IN NUMBER
194 -- p_structure_id IN NUMBER
195 --
196 -- History
197 --
198 -- 25-JUN-01 HSIU -Created
199 --
200 --
201
202
203 function Check_Struc_Ver_Name_Unique
204 (
205 p_structure_version_name IN VARCHAR2
206 ,p_pev_structure_id IN NUMBER
207 ,p_project_id IN NUMBER
208 ,p_structure_id IN NUMBER
209 ) return varchar2
210 IS
211 cursor c1 is
212 select name from pa_proj_elem_ver_structure
213 where project_id = p_project_id
214 and proj_element_id = p_structure_id
215 and name = p_structure_version_name;
216 c1_rec c1%rowtype;
217
218 cursor c2 is
219 select name from pa_proj_elem_ver_structure
220 where project_id = p_project_id
221 and proj_element_id = p_structure_id
222 and name = p_structure_version_name
223 and pev_structure_id <> p_pev_structure_id;
224 c2_rec c2%rowtype;
225
226 BEGIN
227 if (p_project_id IS NULL or p_structure_version_name is NULL or p_structure_id is NULL) then
228 return (null);
229 end if;
230
231 IF (p_pev_structure_id IS NULL) THEN
232 open c1;
233 fetch c1 into c1_rec;
234 if c1%notfound THEN
235 close c1;
236 return('Y');
237 else
238 close c1;
239 return('N');
240 end if;
241 ELSE
242 open c2;
243 fetch c2 into c2_rec;
244 if c2%notfound THEN
245 close c2;
246 return('Y');
247 else
248 close c2;
249 return('N');
250 end if;
251 END IF;
252 EXCEPTION
253 when others then
254 return (SQLCODE);
255 END Check_Struc_Ver_Name_Unique;
256
257
258
259 -- API name : Check_Structure_Type_Exists
260 -- Type : Utils API
261 -- Pre-reqs : None
262 -- Return Value : N/A
263 -- Parameters
264 --
265 -- History
266 --
267 -- 25-JUN-01 HSIU -Created
268 --
269 --
270 -- 19-Dec-03 avaithia -Modified
271 --
272
273
274 procedure Check_Structure_Type_Exists
275 (
276 p_project_id IN NUMBER
277 ,p_structure_type IN VARCHAR2
278 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
279 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
280 )
281 IS
282
283 l_dummy VARCHAR2(1);
284
285 cursor c1 is
286 select '1'
287 from pa_proj_structure_types p, pa_proj_elements e, pa_structure_types t
288 where e.project_id = p_project_id
289 and e.proj_element_id = p.proj_element_id
290 and p.structure_type_id = t.structure_type_id
291 and t.structure_type_class_code = p_structure_type
292 and t.structure_type_class_code IN ('WORKPLAN', 'FINANCIAL','DELIVERABLE');
293
294 BEGIN
295
296 if (p_project_id IS NULL or p_structure_type is NULL) then
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 x_error_message_code := 'PA_PS_INPUT_NOT_SPECIFIC';
299 return;
300 end if;
301
302 open c1;
303 fetch c1 into l_dummy;
304 if c1%notfound THEN
305 close c1;
306 -- selected structure type does not exist
307 x_return_status := FND_API.G_RET_STS_SUCCESS;
308 else
309 close c1;
310 -- selected structure type exists
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 x_error_message_code := 'PA_PS_STRUC_TYPE_EXISTS';
313 end if;
314 EXCEPTION
315 when others then
316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
317 -- 4537865 RESET OUT PARAMS
318 x_error_message_code := SQLERRM ;
319
320 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
321 p_procedure_name => 'Check_Structure_Type_Exists');
322 RAISE;
323 END Check_Structure_Type_Exists;
324
325
326 -- API name : Get_Struc_Type_For_Structure
327 -- Type : Utils API
328 -- Pre-reqs : None
329 -- Return Value : N if not exists; Y if exists.
330 -- Parameters
331 -- p_structure_id IN NUMBER
332 -- p_structure_type IN VARCHAR2
333 --
334 -- History
335 --
336 -- 25-JUN-01 HSIU -Created
337 --
338 --
339
340
341 function Get_Struc_Type_For_Structure
342 (
343 p_structure_id IN NUMBER
344 ,p_structure_type IN VARCHAR2
345 ) return VARCHAR2
346 IS
347 l_dummy VARCHAR2(1);
348
349 cursor c1 is
350 select '1'
351 from pa_proj_structure_types p,
352 pa_structure_types t
353 where p.proj_element_id = p_structure_id
354 and p.structure_type_id = t.structure_type_id
355 and t.structure_type_class_code = p_structure_type
356 and t.structure_type_class_code IN ('WORKPLAN', 'FINANCIAL');
357
358
359 BEGIN
360
361 if (p_structure_id IS NULL or p_structure_type IS NULL) then
362 return (null);
363 end if;
364
365 open c1;
366 fetch c1 into l_dummy;
367 if c1%notfound THEN
368 close c1;
369 return('N');
370 else
371 close c1;
372 return('Y');
373 end if;
374 EXCEPTION
375 when others then
376 return (SQLCODE);
377 return null;
378 END Get_Struc_Type_For_Structure;
379
380
381
382
383 -- API name : Get_Struc_Type_For_Version
384 -- Type : Utils API
385 -- Pre-reqs : None
386 -- Return Value : Y if not exists; N if exists.
387 -- Parameters
388 -- p_structure_version_id IN NUMBER
389 -- p_structure_type IN VARCHAR2
390 --
391 -- History
392 --
393 -- 25-JUN-01 HSIU -Created
394 --
395 --
396
397
398 function Get_Struc_Type_For_Version
399 (
400 p_structure_version_id IN NUMBER
401 ,p_structure_type IN VARCHAR2
402 ) return VARCHAR2
403 IS
404 l_dummy VARCHAR2(1);
405
406 cursor c1 is
407 select '1'
408 from pa_proj_structure_types p, pa_proj_elements e,
409 pa_structure_types t, pa_proj_element_versions v
410 where v.element_version_id = p_structure_version_id
411 and v.proj_element_id = e.proj_element_id
412 and e.proj_element_id = p.proj_element_id
413 and p.structure_type_id = t.structure_type_id
414 and t.structure_type_class_code = p_structure_type
415 and t.structure_type_class_code IN ('WORKPLAN', 'FINANCIAL');
416
417
418 BEGIN
419
420 if (p_structure_version_id IS NULL or p_structure_type IS NULL) then
421 return (null);
422 end if;
423
424 open c1;
425 fetch c1 into l_dummy;
426 if c1%notfound THEN
427 close c1;
428 return('N');
429 else
430 close c1;
431 return('Y');
432 end if;
433 EXCEPTION
434 when others then
435 return (SQLCODE);
436 return null;
437 END Get_Struc_Type_For_Version;
438
439
440
441 -- API name : Check_Publish_Struc_Ver_Ok
442 -- Type : Utils API
443 -- Pre-reqs : None
444 -- Return Value : Y if ok; N if can't publish.
445 -- Purpose
446 -- Check if the structure version can be published (ie, if linked child are
447 -- published.)
448 --
449 -- Parameters
450 -- p_structure_version_id IN NUMBER
451 --
452 -- History
453 --
454 -- 25-JUN-01 HSIU -Created
455 --
456 --
457
458
459 function Check_Publish_Struc_Ver_Ok
460 (
461 p_structure_version_id IN NUMBER
462 ) return varchar2
463 IS
464 CURSOR get_linked_element_parent IS
465 select v1.project_id, v1.parent_structure_version_id
466 from pa_object_relationships r,
467 pa_proj_element_versions v1,
468 pa_proj_element_versions v2
469 where r.relationship_type = 'L'
470 and r.object_type_from IN ('PA_TASKS','PA_STRUCTURES')
471 and r.object_id_from1 = v2.element_version_id
472 and r.object_id_to1 = v1.element_version_id
473 and v2.parent_structure_version_id = p_structure_version_id;
474
475 l_project_id PA_PROJ_ELEMENT_VERSIONS.PROJECT_ID%TYPE;
476 l_struc_ver_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
477
478
479 cursor get_other_unpub_ver IS
480 select b.element_version_id
481 from pa_proj_element_versions a,
482 pa_proj_element_versions b,
483 pa_proj_elem_ver_structure c
484 where a.element_version_id = p_structure_version_id
485 and a.project_id = b.project_id
486 and a.proj_element_id = b.proj_element_id
487 and b.element_version_id <> p_structure_version_id
488 and b.object_type = 'PA_STRUCTURES'
489 and b.project_id = c.project_id
490 and b.element_version_id = c.element_version_id
491 and c.status_code <> 'STRUCTURE_PUBLISHED';
492
493 BEGIN
494 OPEN get_other_unpub_ver;
495 LOOP
496 FETCH get_other_unpub_ver into l_struc_ver_id;
497 EXIT WHEN get_other_unpub_ver%NOTFOUND;
498 IF ('O' = PA_PROJECT_STRUCTURE_UTILS.IS_STRUC_VER_LOCKED_BY_USER(
499 FND_GLOBAL.USER_ID, l_struc_ver_id)) THEN
500 CLOSE get_other_unpub_ver;
501 return 'N';
502 END IF;
503 END LOOP;
504 CLOSE get_other_unpub_ver;
505
506 /*
507 OPEN get_linked_element_parent;
508 LOOP
509 FETCH get_linked_element_parent into l_project_id, l_struc_ver_id;
510 EXIT WHEN get_linked_element_parent%NOTFOUND;
511 IF ('N' = PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(l_project_id,l_struc_ver_id)) THEN
512 CLOSE get_linked_element_parent;
513 return 'N';
514 END IF;
515 END LOOP;
516 CLOSE get_linked_element_parent;
517 */
518 return 'Y';
519 END Check_Publish_Struc_Ver_Ok;
520
521
522 -- API name : Check_Struc_Ver_Published
523 -- Type : Utils API
524 -- Pre-reqs : None
525 -- Return Value : N if not published; Y if published.
526 -- Parameters
527 -- p_structure_version_id IN NUMBER
528 --
529 -- History
530 --
531 -- 25-JUN-01 HSIU -Created
532 --
533 --
534
535
536 function Check_Struc_Ver_Published
537 (
538 p_project_id IN NUMBER
539 ,p_structure_version_id IN NUMBER
540 ) return VARCHAR2
541 IS
542 cursor c1 is
543 select '1'
544 from pa_proj_elem_ver_structure
545 where project_id = p_project_id
546 and element_version_id = p_structure_version_id
547 and status_code = 'STRUCTURE_PUBLISHED';
548 c1_rec c1%rowtype;
549
550 BEGIN
551 if (p_project_id IS NULL or p_structure_version_id IS NULL) then
552 return (null);
553 end if;
554
555 open c1;
556 fetch c1 into c1_rec;
557 if c1%notfound THEN
558 close c1;
559 return('N');
560 else
561 close c1;
562 return('Y');
563 end if;
564 EXCEPTION
565 when others then
566 return (SQLCODE);
567 END Check_Struc_Ver_Published;
568
569
570
571 -- API name : Get_New_Struc_Ver_Name
572 -- Type : Utils API
573 -- Pre-reqs : None
574 -- Return Value : N/A
575 -- Parameters
576 -- p_structure_version_id IN NUMBER
577 -- x_structure_version_name OUT VARCHAR2
578 -- x_return_status OUT VARCHAR2
579 -- x_error_message_code OUT VARCHAR2
580 --
581 -- History
582 --
583 -- 25-JUN-01 HSIU -Created
584 --
585 --
586
587
588 procedure Get_New_Struc_Ver_Name
589 (
590 p_structure_version_id IN NUMBER
591 ,x_structure_version_name OUT NOCOPY VARCHAR2 -- 4537865
592 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
593 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
594 )
595 IS
596 l_posA NUMBER;
597 l_name PA_PROJ_ELEM_VER_STRUCTURE.name%TYPE;
598 l_suffix NUMBER;
599 l_new_name PA_PROJ_ELEM_VER_STRUCTURE.name%TYPE;
600 l_project_id PA_PROJECTS_ALL.project_id%TYPE;
601 l_proj_element_id PA_PROJ_ELEMENTS.proj_element_id%TYPE;
602
603 l_dummy VARCHAR2(1);
604
605 cursor getname is
606 select s.name, v.project_id, v.proj_element_id
607 from pa_proj_element_versions v, pa_proj_elem_ver_structure s
608 where v.element_version_id = p_structure_version_id and
609 s.project_id = v.project_id and
610 s.element_version_id = v.element_version_id;
611
612 cursor new_name_valid(p_project_id PA_PROJECTS_ALL.project_id%TYPE,
613 p_structure_id PA_PROJ_ELEMENTS.proj_element_id%TYPE,
614 p_new_name VARCHAR2) is
615 select '1'
616 from pa_proj_elem_ver_structure
617 where project_id = p_project_id
618 and proj_element_id = p_structure_id
619 and name = p_new_name;
620 BEGIN
621
622 open getname;
623 fetch getname into l_name, l_project_id, l_proj_element_id;
624 if getname%NOTFOUND then
625 close getname;
626 RAISE NO_DATA_FOUND;
627 end if;
628 close getname;
629
630 l_posA := instr(l_name, ' - ', -1, 1);
631 if (l_posA = 0) then
632 x_structure_version_name := substrb(l_name||' - 1',0,240); -- 4537865 replaced substr usage with substrb
633 x_return_status:= FND_API.G_RET_STS_SUCCESS;
634 return;
635 end if;
636 if (l_posA > 0) then
637 --check if this is a number
638 begin
639 l_suffix := to_number(substrb(l_name, 3+l_posA)); -- 4537865 replaced substr usage with substrb
640 LOOP
641 --check if new name is unique
642 l_suffix := l_suffix + 1;
643 l_new_name := substrb(l_name, 0, l_posA - 1)||' - '||to_char(l_suffix); -- 4537865 replaced substr usage with substrb
644 open new_name_valid(l_project_id, l_proj_element_id, l_new_name);
645 fetch new_name_valid into l_dummy;
646
647 if new_name_valid%NOTFOUND then
648 --name is valid
649 x_structure_version_name := l_new_name;
650 x_return_status:= FND_API.G_RET_STS_SUCCESS;
651 close new_name_valid;
652 EXIT;
653 end if;
654 close new_name_valid;
655 END LOOP;
656 EXCEPTION
657 when VALUE_ERROR then
658 --character after dash.
659 --start off with 1 for suffix.
660 l_suffix := 0;
661 LOOP
662 --check if new name is unique
663 l_suffix := l_suffix + 1;
664 l_new_name := l_name||' - '||to_char(l_suffix);
665 open new_name_valid(l_project_id, l_proj_element_id, l_new_name);
666 fetch new_name_valid into l_dummy;
667
668 if new_name_valid%NOTFOUND then
669 --name is valid
670 x_structure_version_name := l_new_name;
671 x_return_status:= FND_API.G_RET_STS_SUCCESS;
672 close new_name_valid;
673 EXIT;
674 end if;
675 close new_name_valid;
676 END LOOP;
677 end;
678 end if;
679
680 EXCEPTION
681 WHEN NO_DATA_FOUND THEN
682
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 x_error_message_code := 'PA_PS_STRUC_VER_NAME_GEN_ERR';
685 -- 4537865 RESET OUT PARAMS
686 x_structure_version_name := NULL ;
687
688 WHEN OTHERS THEN
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
690
691 -- 4537865 RESET OUT PARAMS
692 x_structure_version_name := NULL ;
693 x_error_message_code := SQLERRM ;
694
695 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
696 p_procedure_name => 'Get_New_Struc_Ver_Name');
697 RAISE;
698 END;
699
700
701
702
703 -- API name : Structure_Version_Name_Or_Id
704 -- Type : Utils API
705 -- Pre-reqs : None
706 -- Return Value : N/A
707 -- Parameters
708 -- p_structure_id IN NUMBER
709 -- p_structure_version_name IN VARCHAR2
710 -- p_structure_version_id IN NUMBER
711 -- p_check_id_flag IN VARCHAR2 := PA_STARTUP.G_Check_ID_Flag
712 -- x_structure_version_id OUT NUMBER
713 -- x_return_status OUT VARCHAR2
714 -- x_error_message_code OUT VARCHAR2
715 --
716 -- History
717 --
718 -- 25-JUN-01 HSIU -Created
719 --
720 --
721
722
723 procedure Structure_Version_Name_Or_Id
724 (
725 p_structure_id IN NUMBER
726 ,p_structure_version_name IN VARCHAR2
727 ,p_structure_version_id IN NUMBER
728 ,p_check_id_flag IN VARCHAR2 := PA_STARTUP.G_Check_ID_Flag
729 ,x_structure_version_id OUT NOCOPY NUMBER -- 4537865
730 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
731 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
732 )
733 IS
734 l_current_id NUMBER := NULL;
735 l_rows NUMBER := 0;
736 l_id_found_flag VARCHAR2(1) := 'N';
737
738 BEGIN
739 IF (p_structure_version_id IS NOT NULL) THEN
740 IF (p_check_id_flag = 'Y') THEN
741 select pev.element_version_id
742 into x_structure_version_id
743 from pa_proj_element_versions pev
744 -- pa_proj_elem_ver_structure pevs,
745 -- pa_proj_elements pe
746 where
747 -- pe.proj_element_id = p_structure_id
748 pev.element_version_id = p_structure_version_id;
749 -- and pevs.project_id = pe.project_id
750 -- and pevs.element_version_id = pev.element_version_id;
751 ELSE
752 x_structure_version_id := p_structure_version_id;
753 END IF;
754 ELSE
755 --get by name
756 select pevs.element_version_id
757 into x_structure_version_id
758 from pa_proj_elem_ver_structure pevs,
759 pa_proj_elements pe
760 where pe.proj_element_id = p_structure_id
761 and pevs.project_id = pe.project_id
762 and pevs.proj_element_id = pe.proj_element_id
763 and pevs.name = p_structure_version_name;
764 END IF;
765
766 x_return_status := FND_API.G_RET_STS_SUCCESS;
767 EXCEPTION
768 WHEN no_data_found THEN
769 x_return_status:= FND_API.G_RET_STS_ERROR;
770 x_error_message_code:= 'PA_PS_INVAL_STRUC_VER_NAME';
771
772 -- 4537865 RESET OUT PARAMS
773 x_structure_version_id := NULL ;
774
775 WHEN too_many_rows THEN
776 x_return_status:= FND_API.G_RET_STS_ERROR;
777 x_error_message_code:= 'PA_PS_STRUC_V_NAME_NOT_UNIQUE';
778
779 -- 4537865 RESET OUT PARAMS
780 x_structure_version_id := NULL ;
781 WHEN OTHERS THEN
782 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
783
784 -- 4537865 RESET OUT PARAMS
785 x_structure_version_id := NULL ;
786 x_error_message_code := SQLERRM ;
787
788 RAISE;
789 END;
790
791
792 -- API name : Structure_Name_Or_Id
793 -- Type : Utils API
794 -- Pre-reqs : None
795 -- Return Value : N/A
796 -- Parameters
797 -- p_project_id IN NUMBER
798 -- p_structure_name IN VARCHAR2
799 -- p_structure_id IN NUMBER
800 -- p_check_id_flag IN VARCHAR2 := PA_STARTUP.G_Check_ID_Flag
801 -- x_structure_id OUT NUMBER
802 -- x_return_status OUT VARCHAR2
803 -- x_error_message_code OUT VARCHAR2
804 --
805 -- History
806 --
807 -- 25-JUN-01 HSIU -Created
808 --
809 --
810
811
812 procedure Structure_Name_Or_Id
813 (
814 p_project_id IN NUMBER
815 ,p_structure_name IN VARCHAR2
816 ,p_structure_id IN NUMBER
817 ,p_check_id_flag IN VARCHAR2 := PA_STARTUP.G_Check_ID_Flag
818 ,x_structure_id OUT NOCOPY NUMBER -- 4537865
819 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
820 ,x_error_message_code OUT NOCOPY VARCHAR2 -- 4537865
821 )
822 IS
823 BEGIN
824 IF (p_structure_id IS NOT NULL) THEN
825 IF (p_check_id_flag = 'Y') THEN
826 select proj_element_id
827 into x_structure_id
828 from pa_proj_elements
829 where proj_element_id = p_structure_id;
830 ELSE
831 x_structure_id := p_structure_id;
832 END IF;
833 ELSE
834 select proj_element_id
835 into x_structure_id
836 from pa_proj_elements
837 where project_id = p_project_id
838 and object_type = 'PA_STRUCTURES'
839 and name = p_structure_name;
840 END IF;
841 x_return_status := FND_API.G_RET_STS_SUCCESS;
842 EXCEPTION
843 WHEN no_data_found THEN
844 x_return_status:= FND_API.G_RET_STS_ERROR;
845 x_error_message_code:= 'PA_PS_INVALID_STRUCT_NAME';
846
847 -- 4537865 RESET OUT PARAM
848 x_structure_id := NULL ;
849 WHEN too_many_rows THEN
850 x_return_status:= FND_API.G_RET_STS_ERROR;
851 x_error_message_code:= 'PA_PS_STRUC_NAME_NOT_UNIQUE';
852
853 -- 4537865 RESET OUT PARAM
854 x_structure_id := NULL ;
855 WHEN OTHERS THEN
856 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
857
858 -- 4537865 RESET OUT PARAM
859 x_structure_id := NULL ;
860 x_error_message_code := SQLERRM ;
861 RAISE;
862 END;
863
864
865
866 -- API name : IS_STRUC_VER_LOCKED_BY_USER
867 -- Type : Utils API
868 -- Pre-reqs : None
869 -- Return Value : Y is locked by user - and returns Y also when locked by other users
870 -- and the current user has privelege to Unlock the structure version,
871 -- N is not locked,
872 -- O is locked by other user.
873 -- Parameters
874 -- p_user_id NUMBER
875 -- p_structure_version_id NUMBER
876 --
877 -- History
878 --
879 -- 01-NOV-01 HSIU -Created
880 --
881
882
883 function IS_STRUC_VER_LOCKED_BY_USER(p_user_id NUMBER,
884 p_structure_version_id NUMBER)
885 return VARCHAR2
886 IS
887 l_person_id number;
888 l_locked_person_id number;
889
890 cursor get_person_id IS
891 select p.person_id
892 from fnd_user f,
893 per_all_people_f p
894 where p_user_id = f.user_id
895 and f.employee_id = p.person_id
896 and sysdate between p.effective_start_date and p.effective_end_date;
897
898 cursor get_lock_user IS
899 select s.locked_by_person_id
900 from pa_proj_element_versions v,
901 pa_proj_elem_ver_structure s
902 where v.element_version_id = p_structure_version_id
903 and v.project_id = s.project_id
904 and v.element_version_id = s.element_version_id;
905
906 BEGIN
907
908 OPEN get_lock_user;
909 FETCH get_lock_user into l_locked_person_id;
910 IF (get_lock_user%NOTFOUND) THEN
911 CLOSE get_lock_user;
912 return NULL;
913 END IF;
914 CLOSE get_lock_user;
915
916 IF (l_locked_person_id IS NULL) THEN
917 return 'N';
918 END IF;
919
920 --structure version is locked. Continue.
921 OPEN get_person_id;
922 FETCH get_person_id into l_person_id;
923 IF (get_person_id%NOTFOUND) THEN
924 CLOSE get_person_id;
925 return NULL;
926 END IF;
927 CLOSE get_person_id;
928
929 --bug 3071008: adding the check back in Update_Structure_version_attr
930 --and is_structure_version_updatable
931 /*
932 IF (PA_SECURITY_PVT.check_user_privilege('PA_UNLOCK_ANY_STRUCTURE'
933 ,NULL
934 ,to_number(NULL))
935 = FND_API.G_TRUE) THEN
936 --current user is super user. can edit
937 return 'Y';
938 END IF;
939 */
940 --end bug 3071008
941
942 IF (l_person_id = l_locked_person_id) THEN
943 --locked by current user
944 return 'Y';
945 ELSE
946 --locked by other user
947 return 'O';
948 END IF;
949
950 EXCEPTION
951 WHEN OTHERS THEN
952 return (NULL);
953 END IS_STRUC_VER_LOCKED_BY_USER;
954
955 -- This function is same as above except for it returns 'O' when locked by other users
956 -- and current user has privelege to Unlock the structure version
957 -- API name : IS_STRUC_VER_LOCKED_BY_USER1
958 -- Type : Utils API
959 -- Pre-reqs : None
960 -- Return Value : Y is locked by user,
961 -- N is not locked,
962 -- O is locked by other user.
963 -- Parameters
964 -- p_user_id fnd_user.user_id%TYPE
965 -- p_structure_version_id pa_proj_element_versions.element_version_id%TYPE
966 --
967 -- History
968 --
969 -- 20-may-03 mrajput -Created
970 -- Added For bug 2964237
971
972
973 function IS_STRUC_VER_LOCKED_BY_USER1(p_user_id fnd_user.user_id%TYPE,
974 p_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
975 return VARCHAR2
976 IS
977
978 --Cursor to get the persion id from the user id passed to thia api
979 cursor get_person_id IS
980 select p.person_id
981 from fnd_user f,
982 per_all_people_f p
983 where p_user_id = f.user_id
984 and f.employee_id = p.person_id
985 and sysdate between p.effective_start_date and p.effective_end_date;
986
987 --Cursor to get the locked by person id for a structure version
988 cursor get_lock_user IS
989 select s.locked_by_person_id
990 from pa_proj_element_versions v,
991 pa_proj_elem_ver_structure s
992 where v.element_version_id = p_structure_version_id
993 and v.project_id = s.project_id
994 and v.element_version_id = s.element_version_id;
995
996 l_person_id per_all_people_f.person_id%TYPE;
997 l_locked_person_id pa_proj_elem_ver_structure.locked_by_person_id%TYPE;
998
999 l_module_name VARCHAR2(100) := 'pa.plsql.IS_STRUC_VER_LOCKED_BY_USER1';
1000 l_debug_mode varchar2(1);
1001
1002
1003 BEGIN
1004
1005 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
1006
1007 IF l_debug_mode = 'Y' THEN
1008
1009 pa_debug.g_err_stage := 'The value of passed user_id to this api is: ' || p_user_id;
1010 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1011
1012 pa_debug.g_err_stage := 'The value of passed structure version_id to this api is: ' || p_structure_version_id;
1013 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1014
1015 END IF;
1016
1017 OPEN get_lock_user;
1018 FETCH get_lock_user into l_locked_person_id;
1019 IF (get_lock_user%NOTFOUND) THEN
1020 CLOSE get_lock_user;
1021 return NULL;
1022 END IF;
1023 CLOSE get_lock_user;
1024
1025 IF (l_locked_person_id IS NULL) THEN
1026 return 'N';
1027 END IF;
1028
1029 IF l_debug_mode = 'Y' THEN
1030 pa_debug.g_err_stage := 'The Structure version passed is locked by person id : ' || l_locked_person_id;
1031 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1032 END IF;
1033
1034
1035 --structure version is locked. Continue.
1036 OPEN get_person_id;
1037 FETCH get_person_id into l_person_id;
1038 IF (get_person_id%NOTFOUND) THEN
1039 CLOSE get_person_id;
1040 return NULL;
1041 END IF;
1042 CLOSE get_person_id;
1043
1044 IF l_debug_mode = 'Y' THEN
1045 pa_debug.g_err_stage := 'The person id of the fnd user passed is : ' || l_person_id;
1046 pa_debug.write(l_module_name,pa_debug.g_err_stage,3);
1047 END IF;
1048
1049
1050 IF (l_person_id = l_locked_person_id) THEN
1051 --locked by current user
1052 return 'Y';
1053 ELSE
1054 --locked by other user
1055 return 'O';
1056 END IF;
1057
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 return (NULL);
1061 END IS_STRUC_VER_LOCKED_BY_USER1;
1062
1063
1064 -- API name : GET_APPROVAL_OPTION
1065 -- Type : Utils API
1066 -- Pre-reqs : None
1067 -- Return Value : N is no approval,
1068 -- M is approval with manual publish,
1069 -- A is approval with auto publish.
1070 -- Parameters
1071 -- p_project_id NUMBER
1072 --
1073 -- History
1074 --
1075 -- 06-NOV-01 HSIU -Created
1076 --
1077
1078 function GET_APPROVAL_OPTION(p_project_id NUMBER)
1079 return VARCHAR2
1080 IS
1081
1082 /* Bug 3946599 . This Cursor is Wrong.This will return both Financial as well as WP record
1083 CURSOR c1 IS
1084 select wp_approval_reqd_flag, wp_auto_publish_flag
1085 from pa_proj_workplan_attr
1086 where project_id = p_project_id; */
1087
1088 -- Bug 3946599 : Included an additional clause in above cursor that
1089 -- The Structure Type is Workplan.
1090
1091 CURSOR c1 IS
1092 select wp_approval_reqd_flag, wp_auto_publish_flag
1093 from pa_proj_workplan_attr ppw
1094 ,pa_proj_structure_types ppst
1095 ,pa_structure_types pst
1096 where ppw.project_id = p_project_id
1097 and ppw.proj_element_id = ppst.proj_element_id
1098 and ppst.structure_type_id = pst.structure_type_id
1099 and pst.structure_type = 'WORKPLAN' ;
1100
1101 --Bug 3946599 :End of Changes by avaithia
1102
1103 l_approval VARCHAR2(1);
1104 l_auto_pub VARCHAR2(1);
1105 BEGIN
1106 OPEN c1;
1107 FETCH c1 into l_approval, l_auto_pub;
1108 IF c1%NOTFOUND THEN
1109 CLOSE c1;
1110 return 'N';
1111 END IF;
1112
1113 CLOSE c1;
1114 IF (l_approval = 'N') THEN
1115 return 'N';
1116 ELSE
1117 IF (l_auto_pub = 'Y') THEN
1118 return 'A';
1119 ELSE
1120 return 'M';
1121 END IF;
1122 END IF;
1123 END GET_APPROVAL_OPTION;
1124
1125
1126 -- API name : IS_STRUC_TYPE_LICENSED
1127 -- Type : Utils API
1128 -- Pre-reqs : None
1129 -- Return Value : Y for licensed,
1130 -- N for not licensed.
1131 --
1132 -- Parameters
1133 -- p_structure_type VARCHAR2
1134 --
1135 -- History
1136 --
1137 -- 06-NOV-01 HSIU -Created
1138 --
1139
1140 function IS_STRUC_TYPE_LICENSED(p_structure_type VARCHAR2)
1141 return VARCHAR2
1142 IS
1143 BEGIN
1144 IF p_structure_type = 'WORKPLAN' THEN
1145 return pa_install.is_pjt_licensed;
1146 ELSIF p_structure_type = 'FINANCIAL' THEN
1147 return pa_install.is_costing_licensed;
1148 END IF;
1149 return 'N';
1150 END IS_STRUC_TYPE_LICENSED;
1151
1152 -- API name : CHECK_PUBLISHED_VER_EXISTS
1153 -- Type : Utils API
1154 -- Pre-reqs : None
1155 -- Return Value : Y for published version exists,
1156 -- N for not exist.
1157 --
1158 -- Parameters
1159 -- p_project_id NUMBER
1160 -- p_structure_id NUMBER
1161 --
1162 -- History
1163 --
1164 -- 16-JAN-02 HSIU -Created
1165 --
1166 function CHECK_PUBLISHED_VER_EXISTS(p_project_id NUMBER,
1167 p_structure_id NUMBER)
1168 return VARCHAR2
1169 IS
1170 CURSOR c1 IS
1171 select '1'
1172 from pa_proj_elem_ver_structure
1173 where project_id = p_project_id
1174 and proj_element_id = p_structure_id
1175 and status_code = 'STRUCTURE_PUBLISHED';
1176 l_dummy VARCHAR2(1);
1177 BEGIN
1178 OPEN c1;
1179 FETCH c1 into l_dummy;
1180 IF c1%NOTFOUND THEN
1181 CLOSE c1;
1182 return 'N';
1183 END IF;
1184 CLOSE c1;
1185 return 'Y';
1186 END CHECK_PUBLISHED_VER_EXISTS;
1187
1188
1189 -- API name : Product_Licensed
1190 -- Type : Utils API
1191 -- Pre-reqs : None
1192 -- Return Value : BOTH -- for workplan and (costing or billing)
1193 -- WORKPLAN -- for workplan only
1194 -- FINANCIAL -- for costing or billing only
1195 --
1196 --
1197 -- Parameters
1198 -- None
1199 --
1200 -- History
1201 --
1202 -- 14-MAR-02 HSIU -Created
1203 --
1204 function Product_Licensed
1205 return varchar2
1206 IS
1207 l_financial varchar2(1) := 'N';
1208 l_workplan varchar2(1):= 'N';
1209 retval VARCHAR2(10);
1210 BEGIN
1211 l_workplan := IS_STRUC_TYPE_LICENSED('WORKPLAN') ;
1212 l_financial := IS_STRUC_TYPE_LICENSED('FINANCIAL') ;
1213
1214 IF (l_workplan = 'Y' AND (l_financial = 'Y') ) THEN
1215 retval := 'BOTH';
1216 ELSIF (l_workplan = 'Y') THEN
1217 retval := 'WORKPLAN';
1218 ELSE
1219 retval := 'FINANCIAL';
1220 END IF;
1221
1222 return retval;
1223 END Product_Licensed;
1224
1225
1226
1227 -- Parameters
1228 -- p_project_id NUMBER
1229 --
1230 -- History
1231 --
1232 -- 14-MAR-02 HSIU -Created
1233 --
1234 function Associated_Structure(p_project_id NUMBER)
1235 return varchar2
1236 IS
1237 cursor c1 IS
1238 select proj_element_id from pa_proj_elements
1239 where project_id = p_project_id
1240 and object_type = 'PA_STRUCTURES';
1241 c1_rec c1%ROWTYPE;
1242
1243 l_financial varchar2(1) := 'N';
1244 l_workplan varchar2(1):= 'N';
1245 retval varchar2(10);
1246 BEGIN
1247 OPEN c1;
1248 LOOP
1249 FETCH c1 into c1_rec;
1250 EXIT when c1%NOTFOUND;
1251 IF (Get_Struc_Type_For_Structure(c1_rec.proj_element_id, 'WORKPLAN') = 'Y') THEN
1252 l_workplan := 'Y';
1253 END IF;
1254 IF (Get_Struc_Type_For_Structure(c1_rec.proj_element_id, 'FINANCIAL') = 'Y') THEN
1255 l_financial := 'Y';
1256 END IF;
1257 END LOOP;
1258 If c1%ROWCOUNT = 2 THEN
1259 retval := 'SPLIT';
1260 ELSE
1261 IF (l_workplan = 'Y' and l_financial = 'Y' ) THEN
1262 retval := 'BOTH';
1263 ELSIF (l_workplan = 'Y') THEN
1264 retval := 'WORKPLAN';
1265 ELSE
1266 retval := 'FINANCIAL';
1267 END IF;
1268 END IF;
1269 CLOSE c1;
1270
1271 return retval;
1272 END Associated_Structure;
1273
1274
1275 -- API name : Get_Rollup_Dates
1276 -- Type : Utils API
1277 -- Pre-reqs : None
1278 -- Return Value : None
1279 --
1280 -- Parameters
1281 -- p_element_version_id IN NUMBER
1282 -- p_min_sch_start_date OUT DATE
1283 -- p_max_sch_finish_date OUT DATE
1284 -- p_rollup_last_update_date OUT DATE
1285 --
1286 -- History
1287 --
1288 -- 25-MAR-02 HSIU -Created
1289 --
1290 procedure Get_Rollup_Dates
1291 (
1292 p_element_version_id IN NUMBER
1293 ,p_min_sch_start_date OUT NOCOPY DATE -- 4537865
1294 ,p_max_sch_finish_date OUT NOCOPY DATE -- 4537865
1295 ,p_rollup_last_update_date OUT NOCOPY DATE -- 4537865
1296 )
1297 IS
1298 CURSOR c1 IS
1299 select min(a.scheduled_start_date)
1300 from pa_proj_elem_ver_schedule a,
1301 pa_object_relationships b,
1302 pa_proj_element_versions c
1303 where relationship_type IN ('S', 'L')
1304 and b.object_id_from1 = p_element_version_id
1305 and b.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
1306 and b.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS')
1307 and b.object_id_to1 = c.element_version_id
1308 and c.project_id = a.project_id
1309 and c.element_version_id = a.element_version_id;
1310
1311 CURSOR c2 IS
1312 select max(a.scheduled_finish_date)
1313 from pa_proj_elem_ver_schedule a,
1314 pa_object_relationships b,
1315 pa_proj_element_versions c
1316 where relationship_type IN ('S', 'L')
1317 and b.object_id_from1 = p_element_version_id
1318 and b.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
1319 and b.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS')
1320 and b.object_id_to1 = c.element_version_id
1321 and c.project_id = a.project_id
1322 and c.element_version_id = a.element_version_id;
1323
1324 CURSOR c3 IS
1325 select '1'
1326 from pa_object_relationships a,
1327 pa_proj_elements b,
1328 pa_proj_element_versions c
1329 where a.object_id_from1 = p_element_version_id
1330 and a.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
1331 and a.object_type_to IN ('PA_STRUCTURES', 'PA_TASKS')
1332 and a.object_id_to1 = c.element_version_id
1333 and c.proj_element_id = b.proj_element_id
1334 and c.project_id = b.project_id
1335 and b.link_task_flag = 'Y';
1336
1337 --bug 3074706
1338 --add pa_proj_element_versions and add condition project_id to use index
1339 CURSOR c4 IS
1340 select max(a.last_update_date)
1341 from pa_proj_elem_ver_schedule a,
1342 pa_object_relationships b,
1343 pa_proj_element_versions c
1344 where a.element_version_id = c.element_version_id
1345 and a.project_id = c.project_id
1346 and c.element_version_id = b.object_id_to1
1347 and b.object_type_to = 'PA_TASKS'
1348 and b.object_type_from IN ('PA_TASKS', 'PA_STRUCTURES')
1349 and b.relationship_type = 'S'
1350 and b.object_id_from1 = p_element_version_id;
1351 --end bug 3074706
1352
1353 l_start_date DATE;
1354 l_finish_date DATE;
1355 l_last_update_date DATE;
1356 l_dummy VARCHAR2(1);
1357 BEGIN
1358 --Check if any children is a linking task.
1359 OPEN c3;
1360 FETCH c3 INTO l_dummy;
1361 IF c3%NOTFOUND THEN
1362 CLOSE c3;
1363 return;
1364 END IF;
1365 CLOSE c3;
1366
1367 OPEN c1;
1368 FETCH c1 INTO l_start_date;
1369 CLOSE c1;
1370
1371 OPEN c2;
1372 FETCH c2 INTO l_finish_date;
1373 CLOSE c2;
1374
1375 OPEN c4;
1376 FETCH c4 INTO l_last_update_date;
1377 CLOSE c4;
1378
1379 p_min_sch_start_date := l_start_date;
1380 p_max_sch_finish_date := l_finish_date;
1381 p_rollup_last_update_date := l_last_update_date;
1382 -- 4537865
1383 EXCEPTION
1384 WHEN OTHERS THEN
1385 p_min_sch_start_date := NULL ;
1386 p_rollup_last_update_date := NULL ;
1387 p_max_sch_finish_date := NULL ;
1388
1389 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
1390 p_procedure_name => 'Get_Rollup_Dates',
1391 p_error_text => SUBSTRB(SQLERRM,1,240)
1392 );
1393 RAISE ;
1394 END Get_Rollup_Dates;
1395
1396 -- API name : Get_Workplan_Version
1397 -- Type : Utils API
1398 -- Pre-reqs : None
1399 -- Return Value : None
1400 --
1401 -- Parameters
1402 -- p_project_id IN NUMBER
1403 -- p_structure_version_id OUT DATE
1404 --
1405 -- History
1406 --
1407 -- 10-MAY-02 HSIU -Created
1408 -- 30-NOV-04 AVAITHIA -3968091 : Performance Fix
1409 procedure Get_Workplan_Version
1410 (
1411 p_project_id IN NUMBER
1412 ,p_structure_version_id OUT NOCOPY NUMBER -- 4537865
1413 )
1414 IS
1415 CURSOR c1 IS
1416 /* Commented the following query for Performance Bug fix : 3968091
1417 select c.element_version_id
1418 from pa_proj_element_versions c,
1419 pa_proj_elements b,
1420 pa_proj_structure_types a,
1421 pa_structure_types d
1422 where d.structure_type_class_code = 'WORKPLAN'
1423 and d.structure_type_id = a.structure_type_id
1424 and a.proj_element_id = b.proj_element_id
1425 and b.project_id = p_project_id
1426 and b.proj_element_id = c.proj_element_id
1427 and b.project_id = c.project_id
1428 and c.object_type = 'PA_STRUCTURES';
1429 */
1430 select c.element_version_id /*New Query with improved Performance : 3968091 */
1431 from pa_proj_element_versions c,
1432 pa_proj_structure_types a,
1433 pa_structure_types d
1434 where d.structure_type_class_code = 'WORKPLAN'
1435 and d.structure_type_id = a.structure_type_id
1436 and a.proj_element_id = c.proj_element_id
1437 and c.project_id = p_project_id
1438 and c.object_type = 'PA_STRUCTURES';
1439 BEGIN
1440
1441 OPEN c1;
1442 FETCH c1 INTO p_structure_version_id;
1443 IF c1%NOTFOUND THEN
1444 p_structure_version_id := -1;
1445 END IF;
1446 CLOSE c1;
1447
1448 -- 4537865
1449 EXCEPTION
1450 WHEN OTHERS THEN
1451 p_structure_version_id := NULL;
1452
1453 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
1454 p_procedure_name => 'Get_Workplan_Version',
1455 p_error_text => SUBSTRB(SQLERRM,1,240)
1456 );
1457 RAISE ;
1458 END Get_Workplan_Version;
1459
1460
1461 -- API name : Get_Financial_Version
1462 -- Type : Utils API
1463 -- Pre-reqs : None
1464 -- Return Value : None
1465 --
1466 -- Parameters
1467 -- p_project_id IN NUMBER
1468 -- p_structure_version_id OUT NUMBER
1469 --
1470 -- History
1471 --
1472 -- 26-JAN-04 sdnambia -Created
1473 -- 30-NOV-04 AVAITHIA -3968091 : Performance Fix
1474 --
1475 procedure Get_Financial_Version
1476 (
1477 p_project_id IN NUMBER
1478 ,p_structure_version_id OUT NOCOPY NUMBER -- 4537865
1479 )
1480 IS
1481 CURSOR c1 IS
1482 /*Commented the following Query for Performance Bug fix : 3968091
1483 select c.element_version_id
1484 from pa_proj_element_versions c,
1485 pa_proj_elements b,
1486 pa_proj_structure_types a,
1487 pa_structure_types d
1488 where d.structure_type_class_code = 'FINANCIAL'
1489 and d.structure_type_id = a.structure_type_id
1490 and a.proj_element_id = b.proj_element_id
1491 and b.project_id = p_project_id
1492 and b.proj_element_id = c.proj_element_id
1493 and b.project_id = c.project_id
1494 and c.object_type = 'PA_STRUCTURES';
1495 */
1496 select c.element_version_id /*New Query with improved Performance : 3968091 */
1497 from pa_proj_element_versions c,
1498 pa_proj_structure_types a,
1499 pa_structure_types d
1500 where d.structure_type_class_code = 'FINANCIAL'
1501 and d.structure_type_id = a.structure_type_id
1502 and a.proj_element_id = c.proj_element_id
1503 and c.project_id = p_project_id
1504 and c.object_type = 'PA_STRUCTURES';
1505 BEGIN
1506
1507 OPEN c1;
1508 FETCH c1 INTO p_structure_version_id;
1509 IF c1%NOTFOUND THEN
1510 p_structure_version_id := -1;
1511 END IF;
1512 CLOSE c1;
1513 -- 4537865
1514 EXCEPTION
1515 WHEN OTHERS THEN
1516 p_structure_version_id := NULL;
1517
1518 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
1519 p_procedure_name => 'Get_Financial_Version',
1520 p_error_text => SUBSTRB(SQLERRM,1,240)
1521 );
1522 RAISE ;
1523 END Get_Financial_Version;
1524
1525
1526 -- API name : check_miss_transaction_tasks
1527 -- Type : Utils API
1528 -- Pre-reqs : None
1529 -- Return Value : Y if not missing any tasks with transactions
1530 -- N if tasks with transactions are missing
1531 -- Parameters
1532 -- p_structure_version_id IN NUMBER
1533 --
1534 -- History
1535 --
1536 -- 24-MAY-01 HSIU -Created
1537 --
1538 --
1539 PROCEDURE check_miss_transaction_tasks
1540 (
1541 p_structure_version_id IN NUMBER
1542 ,x_return_status OUT NOCOPY VARCHAR2
1543 ,x_msg_count OUT NOCOPY NUMBER
1544 ,x_msg_data OUT NOCOPY VARCHAR2
1545 )
1546 IS
1547 CURSOR cur_proj_elem_ver(c_element_version_id NUMBER)
1548 IS
1549 SELECT project_id, proj_element_id
1550 FROM pa_proj_element_versions
1551 WHERE element_version_id = c_element_version_id;
1552
1553 CURSOR get_latest_pub_ver IS
1554 select b.element_version_id
1555 from pa_proj_element_versions a,
1556 pa_proj_elem_ver_structure b
1557 where a.project_id = b.project_id
1558 and a.element_version_id = p_structure_version_id
1559 and a.proj_element_id = b.proj_element_id
1560 and b.LATEST_EFF_PUBLISHED_FLAG = 'Y'
1561 and b.status_code = 'STRUCTURE_PUBLISHED';
1562
1563 /* Bug 2680486 -- Performance changes -- Commented the following cursor definition. Restructured it to
1564 avoid Hash Join*/
1565
1566 /* CURSOR get_missing_tasks(c_struc_ver_id NUMBER, c_latest_struc_ver_id NUMBER) IS
1567 select project_id, element_version_id
1568 from pa_proj_element_versions
1569 where parent_structure_version_id = c_latest_struc_ver_id
1570 and proj_element_id IN (
1571 select proj_element_id
1572 from pa_proj_element_versions
1573 where parent_structure_version_id = c_latest_struc_ver_id
1574 minus
1575 select proj_element_id
1576 from pa_proj_element_versions
1577 where parent_structure_version_id = c_struc_ver_id);
1578 */
1579 CURSOR get_missing_tasks(c_struc_ver_id NUMBER, c_latest_struc_ver_id NUMBER) IS
1580 select a.project_id, a.element_version_id
1581 from pa_proj_element_versions a
1582 where a.parent_structure_version_id = c_latest_struc_ver_id
1583 and EXISTS (
1584 select b.proj_element_id
1585 from pa_proj_element_versions b
1586 where b.parent_structure_version_id = c_latest_struc_ver_id
1587 and b.proj_element_id = a.proj_element_id
1588 minus
1589 select c.proj_element_id
1590 from pa_proj_element_versions c
1591 where c.parent_structure_version_id = c_struc_ver_id
1592 and c.proj_element_id = a.proj_element_id);
1593
1594 CURSOR get_task_name_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
1595 select name, element_number
1596 from pa_proj_elements
1597 where project_id = c_project_id
1598 and proj_element_id = c_proj_element_id;
1599
1600 l_ret_stat VARCHAR2(1);
1601 l_err_code VARCHAR2(2000);
1602 l_err_stage VARCHAR2(2000);
1603 l_err_stack VARCHAR2(2000);
1604 l_elem_ver_id NUMBER;
1605 l_latest_struc_ver_id NUMBER;
1606 l_project_id NUMBER;
1607 l_proj_element_id NUMBER;
1608 l_elem_proj_id NUMBER;
1609 l_task_name PA_PROJ_ELEMENTS.NAME%TYPE;
1610 l_task_number PA_PROJ_ELEMENTS.ELEMENT_NUMBER%TYPE;
1611 BEGIN
1612 --check if new structure version missing tasks with transactions
1613 OPEN get_latest_pub_ver;
1614 FETCH get_latest_pub_ver into l_latest_struc_ver_id;
1615 IF get_latest_pub_ver%FOUND THEN
1616 --a published structure exists. Check if missing tasks have transactions.
1617 OPEN get_missing_tasks(p_structure_version_id, l_latest_struc_ver_id);
1618 LOOP
1619 FETCH get_missing_tasks into l_elem_proj_id, l_elem_ver_id;
1620 EXIT WHEN get_missing_tasks%NOTFOUND;
1621 --check for transactions.
1622
1623 OPEN cur_proj_elem_ver(l_elem_ver_id);
1624 FETCH cur_proj_elem_ver INTO l_project_id, l_proj_element_id;
1625 CLOSE cur_proj_elem_ver;
1626
1627 --Check for control items
1628 IF (1 = PA_CONTROL_ITEMS_UTILS.CHECK_CONTROL_ITEM_EXISTS(l_project_id, l_proj_element_id) ) THEN
1629 -- CLOSE get_missing_tasks;
1630 -- CLOSE get_latest_pub_ver;
1631 -- return 'N';
1632 OPEN get_task_name_num(l_project_id, l_proj_element_id);
1633 FETCH get_task_name_num into l_task_name, l_task_number;
1634 CLOSE get_task_name_num;
1635 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_MISS_TRANSAC_TASK',
1636 'TASK_NAME', l_task_name,
1637 'TASK_NUMBER', l_task_number);
1638 --Add error message to stack
1639 END IF;
1640
1641
1642 IF PA_PROJ_ELEMENTS_UTILS.structure_type(
1643 p_structure_version_id => p_structure_version_id
1644 ,p_task_version_id => null
1645 ,p_structure_type => 'WORKPLAN'
1646 ) = 'Y'
1647 THEN
1648 --call selvas API
1649 IF PA_PROJECT_STRUCTURE_UTILS.check_task_progress_exist(l_proj_element_id) = 'Y' THEN
1650 -- CLOSE get_missing_tasks;
1651 -- CLOSE get_latest_pub_ver;
1652 -- return 'N';
1653 --Add error message to stack
1654 OPEN get_task_name_num(l_project_id, l_proj_element_id);
1655 FETCH get_task_name_num into l_task_name, l_task_number;
1656 CLOSE get_task_name_num;
1657 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_MISS_TRANSAC_TASK',
1658 'TASK_NAME', l_task_name,
1659 'TASK_NUMBER', l_task_number);
1660 END IF;
1661
1662 END IF;
1663
1664 IF PA_PROJ_ELEMENTS_UTILS.structure_type(
1665 p_structure_version_id => p_structure_version_id
1666 ,p_task_version_id => null
1667 ,p_structure_type => 'FINANCIAL'
1668 ) = 'Y' THEN
1669 --Check if this is a financial structure version
1670 --if it is, check to see if it is the last version
1671
1672 --Bug 2947492:The following api call is modified to pass parameters by notation.
1673
1674 --Check if it is okay to delete task
1675 PA_TASK_UTILS.CHECK_DELETE_TASK_OK(
1676 x_task_id => l_proj_element_id,
1677 x_err_code => l_err_code,
1678 x_err_stage => l_err_stage,
1679 x_err_stack => l_err_stack);
1680
1681 IF (l_err_code <> 0) THEN
1682 -- CLOSE get_missing_tasks;
1683 -- CLOSE get_latest_pub_ver;
1684 -- return 'N';
1685 --Add error message to stack
1686 OPEN get_task_name_num(l_project_id, l_proj_element_id);
1687 FETCH get_task_name_num into l_task_name, l_task_number;
1688 CLOSE get_task_name_num;
1689 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_MISS_TRANSAC_TASK',
1690 'TASK_NAME', l_task_name,
1691 'TASK_NUMBER', l_task_number);
1692 END IF;
1693
1694 END IF;
1695
1696 END LOOP;
1697 CLOSE get_missing_tasks;
1698 END IF;
1699 CLOSE get_latest_pub_ver;
1700
1701 x_msg_count := FND_MSG_PUB.count_msg;
1702 IF (x_msg_count > 0) THEN
1703 raise FND_API.G_EXC_ERROR;
1704 END IF;
1705
1706 x_return_status := FND_API.G_RET_STS_SUCCESS;
1707 EXCEPTION
1708 WHEN FND_API.G_EXC_ERROR THEN
1709 x_return_status := FND_API.G_RET_STS_ERROR;
1710 WHEN OTHERS THEN
1711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1712 --4537865
1713 x_msg_count := 1;
1714 x_msg_data := SQLERRM ;
1715
1716 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
1717 p_procedure_name => 'check_miss_transaction_tasks',
1718 p_error_text => x_msg_data); -- 4537865
1719 RAISE;
1720 END check_miss_transaction_tasks;
1721
1722 -- API name : Get_Struc_Ver_Display_Text
1723 -- Type : Utils API
1724 -- Pre-reqs : None
1725 -- Return Value : display text for structure version dropdown
1726 -- list
1727 --
1728 -- Parameters
1729 --
1730 --
1731 -- History
1732 --
1733 -- 26-JUL-02 HSIU -Created
1734 --
1735 --
1736
1737 FUNCTION Get_Struc_Ver_Display_Text
1738 ( p_structure_version_name IN VARCHAR2
1739 ,p_structure_version_number IN VARCHAR2
1740 ,p_status IN VARCHAR2
1741 ,p_baseline_flag IN VARCHAR2
1742 ) return varchar2
1743 IS
1744 l_display_text VARCHAR2(2000);
1745 BEGIN
1746 IF (p_baseline_flag = 'Y') THEN
1747 fnd_message.set_name('PA', 'PA_PS_STRUC_INFO_POP_B');
1748 ELSE
1749 fnd_message.set_name('PA', 'PA_PS_STRUC_INFO_POP_NB');
1750 END IF;
1751 FND_MESSAGE.Set_token('STRUC_VER_NAME', p_structure_version_name);
1752 FND_MESSAGE.Set_token('STRUC_VER_NUM', p_structure_version_number);
1753 FND_MESSAGE.Set_token('STATUS', p_status);
1754 l_display_text := fnd_message.get;
1755 return l_display_text;
1756 END Get_Struc_Ver_Display_Text;
1757
1758
1759 -- API name : CHECK_WORKPLAN_ENABLED
1760 -- Type : Utils API
1761 -- Pre-reqs : None
1762 -- Return Value : Y if workplan is enabled
1763 --
1764 -- Parameters
1765 -- p_project_id IN NUMBER
1766 --
1767 -- History
1768 --
1769 -- 26-JUL-02 HSIU -Created
1770 --
1771 FUNCTION check_workplan_enabled
1772 ( p_project_id IN NUMBER
1773 ) return VARCHAR2
1774 IS
1775 CURSOR c1 IS
1776 SELECT 'Y'
1777 FROM pa_proj_elements a,
1778 pa_proj_structure_types b,
1779 pa_structure_types c
1780 WHERE c.structure_type_class_code = 'WORKPLAN'
1781 AND c.structure_type_id = b.structure_type_id
1782 AND b.proj_element_id = a.proj_element_id
1783 AND a.project_id = p_project_id;
1784
1785 l_dummy VARCHAR2(1);
1786 BEGIN
1787 OPEN c1;
1788 FETCH c1 into l_dummy;
1789 IF c1%NOTFOUND THEN
1790 l_dummy := 'N';
1791 END IF;
1792 CLOSE c1;
1793 return l_dummy;
1794 END check_workplan_enabled;
1795
1796
1797 -- API name : CHECK_FINANCIAL_ENABLED
1798 -- Type : Utils API
1799 -- Pre-reqs : None
1800 -- Return Value : Check if financial is enabled. Y if enabled,
1801 -- N if not.
1802 --
1803 -- Parameters
1804 -- p_project_id IN NUMBER
1805 --
1806 -- History
1807 --
1808 -- 26-JUL-02 HSIU -Created
1809 --
1810 FUNCTION check_financial_enabled
1811 ( p_project_id IN NUMBER
1812 ) return VARCHAR2
1813 IS
1814 CURSOR c1 IS
1815 SELECT 'Y'
1816 FROM pa_proj_elements a,
1817 pa_proj_structure_types b,
1818 pa_structure_types c
1819 WHERE c.structure_type_class_code = 'FINANCIAL'
1820 AND c.structure_type_id = b.structure_type_id
1821 AND b.proj_element_id = a.proj_element_id
1822 AND a.project_id = p_project_id;
1823
1824 l_dummy VARCHAR2(1);
1825 BEGIN
1826 OPEN c1;
1827 FETCH c1 into l_dummy;
1828 IF c1%NOTFOUND THEN
1829 l_dummy := 'N';
1830 END IF;
1831 CLOSE c1;
1832 return l_dummy;
1833 END check_financial_enabled;
1834
1835
1836 -- API name : CHECK_SHARING_ENABLED
1837 -- Type : Utils API
1838 -- Pre-reqs : None
1839 -- Return Value : Check if workplan and financial
1840 -- are sharing 1 structure. Y if shared,
1841 -- N if not.
1842 --
1843 -- Parameters
1844 -- p_project_id IN NUMBER
1845 --
1846 -- History
1847 --
1848 -- 26-JUL-02 HSIU -Created
1849 --
1850 FUNCTION check_sharing_enabled
1851 ( p_project_id IN NUMBER
1852 ) return VARCHAR2
1853 IS
1854 CURSOR c1 IS
1855 SELECT 'Y'
1856 FROM pa_proj_elements a,
1857 pa_proj_structure_types b,
1858 pa_structure_types c,
1859 pa_proj_structure_types d,
1860 pa_structure_types e
1861 WHERE c.structure_type_class_code = 'WORKPLAN'
1862 AND e.structure_type_class_code = 'FINANCIAL'
1863 AND c.structure_type_id = b.structure_type_id
1864 AND e.structure_type_id = d.structure_type_id
1865 AND b.proj_element_id = a.proj_element_id
1866 AND d.proj_element_id = a.proj_element_id
1867 AND a.project_id = p_project_id;
1868
1869 l_dummy VARCHAR2(1);
1870 BEGIN
1871 OPEN c1;
1872 FETCH c1 into l_dummy;
1873 IF c1%NOTFOUND THEN
1874 l_dummy := 'N';
1875 END IF;
1876 CLOSE c1;
1877 return l_dummy;
1878 END check_sharing_enabled;
1879
1880
1881 -- API name : CHECK_ENABLE_WP_OK
1882 -- Type : Utils API
1883 -- Pre-reqs : None
1884 -- Return Value : Check if ok to enable workplan
1885 -- Return Y or N
1886 --
1887 -- Parameters
1888 -- p_project_id IN NUMBER
1889 --
1890 -- History
1891 --
1892 -- 26-JUL-02 HSIU -Created
1893 --
1894 procedure check_enable_wp_ok
1895 ( p_project_id IN NUMBER
1896 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
1897 ,x_err_msg_code OUT NOCOPY VARCHAR2 -- 4537865
1898 )
1899 IS
1900 CURSOR get_calendar_id IS
1901 select CALENDAR_ID
1902 from pa_projects_all
1903 where project_id = p_project_id;
1904 l_cal_id NUMBER;
1905
1906 CURSOR get_hrs_per_day IS
1907 select FTE_DAY
1908 from pa_implementations;
1909 l_hrs_per_day NUMBER;
1910 BEGIN
1911 --check if calendar is available
1912 OPEN get_calendar_id;
1913 FETCH get_calendar_id INTO l_cal_id;
1914 CLOSE get_calendar_id;
1915
1916 IF (l_cal_id IS NULL) THEN
1917 x_err_msg_code := 'PA_PS_NO_CALENDAR_ERR';
1918 x_return_status := 'N';
1919 return;
1920 END IF;
1921
1922 OPEN get_hrs_per_day;
1923 FETCH get_hrs_per_day into l_hrs_per_day;
1924 CLOSE get_hrs_per_day;
1925
1926 IF (l_hrs_per_day IS NULL) OR (l_hrs_per_day = 0) THEN
1927 x_err_msg_code := 'PA_PS_INV_HRS_PER_DAY';
1928 x_return_status := 'N';
1929 return;
1930 END IF;
1931
1932 x_return_status := 'Y';
1933 -- 4537865
1934 EXCEPTION
1935 WHEN OTHERS THEN
1936 x_err_msg_code := SQLCODE;
1937 x_return_status := 'N'; -- Based on Usage of this API
1938
1939 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
1940 p_procedure_name => 'check_enable_wp_ok',
1941 p_error_text => SUBSTRB(SQLERRM,1,240)
1942 );
1943 RAISE ;
1944 END check_enable_wp_ok;
1945
1946
1947 -- API name : CHECK_DISABLE_WP_OK
1948 -- Type : Utils API
1949 -- Pre-reqs : None
1950 -- Return Value : Check if ok to disable workplan
1951 -- Return Y or N
1952 --
1953 -- Parameters
1954 -- p_project_id IN NUMBER
1955 --
1956 -- History
1957 --
1958 -- 26-JUL-02 HSIU -Created
1959 --
1960 Procedure check_disable_wp_ok
1961 ( p_project_id IN NUMBER
1962 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
1963 ,x_err_msg_code OUT NOCOPY VARCHAR2 -- 4537865
1964 )
1965 IS
1966 l_structure_id NUMBER;
1967 --Bug No 3692992 Performance fix
1968 /* CURSOR get_tasks IS
1969 SELECT proj_element_id
1970 from pa_proj_elements
1971 where project_id = p_project_id
1972 and object_type = 'PA_TASKS'
1973 and proj_element_id IN (
1974 select distinct ppev1.proj_element_id
1975 from pa_proj_element_versions ppev1,
1976 pa_proj_element_versions ppev2
1977 where ppev2.object_type = 'PA_STRUCTURES'
1978 and ppev2.project_id = p_project_id
1979 and ppev2.proj_element_id = l_structure_id
1980 and ppev1.parent_structure_version_id = ppev2.element_version_id);*/
1981
1982 CURSOR get_tasks IS
1983 select distinct ppev1.proj_element_id
1984 from pa_proj_element_versions ppev1,
1985 pa_proj_element_versions ppev2
1986 where ppev2.object_type = 'PA_STRUCTURES'
1987 and ppev2.project_id = p_project_id
1988 and ppev2.proj_element_id = l_structure_id
1989 and ppev1.object_type = 'PA_TASKS'
1990 and ppev1.parent_structure_version_id = ppev2.element_version_id;
1991
1992 l_task_id NUMBER;
1993
1994 CURSOR get_wp_id IS
1995 SELECT ppe.proj_element_id
1996 from pa_proj_elements ppe,
1997 pa_proj_structure_types ppst,
1998 pa_structure_types pst
1999 where ppe.project_id = p_project_id
2000 and ppe.proj_element_id = ppst.proj_element_id
2001 and ppst.structure_type_id = pst.structure_type_id
2002 and pst.structure_type_class_code = 'WORKPLAN';
2003 BEGIN
2004 OPEN get_wp_id;
2005 FETCH get_wp_id into l_structure_id;
2006 CLOSE get_wp_id;
2007 --check progress for project and all tasks
2008 IF (check_proj_progress_exist(p_project_id, l_structure_id,'WORKPLAN') = 'Y') THEN -- Bug 6914708
2009 x_return_status := 'N';
2010 x_err_msg_code := 'PA_PS_PROJ_PROGRESS_ERR';
2011 return;
2012 END IF;
2013
2014 OPEN get_tasks;
2015 LOOP
2016 FETCH get_tasks into l_task_id;
2017 EXIT when get_tasks%NOTFOUND;
2018 IF (check_task_progress_exist(l_task_id) = 'Y') THEN
2019 CLOSE get_tasks;
2020 x_return_status := 'N';
2021 x_err_msg_code := 'PA_PS_TASK_PROGRESS_ERR';
2022 return;
2023 END IF;
2024 END LOOP;
2025 CLOSE get_tasks;
2026
2027 x_return_status := 'Y';
2028 -- 4537865
2029 EXCEPTION
2030 WHEN OTHERS THEN
2031 x_err_msg_code := SQLCODE;
2032 x_return_status := 'N'; -- Based on Usage of this API
2033
2034 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
2035 p_procedure_name => 'check_disable_wp_ok',
2036 p_error_text => SUBSTRB(SQLERRM,1,240)
2037 );
2038 RAISE ;
2039 END check_disable_wp_ok;
2040
2041
2042 -- API name : CHECK_SHARING_ON_OK
2043 -- Type : Utils API
2044 -- Pre-reqs : None
2045 -- Return Value : Check if ok to share workplan
2046 -- Return Y or N
2047 --
2048 -- Parameters
2049 -- p_project_id IN NUMBER
2050 --
2051 -- History
2052 --
2053 -- 26-JUL-02 HSIU -Created
2054 --
2055 procedure check_sharing_on_ok
2056 ( p_project_id IN NUMBER
2057 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
2058 ,x_err_msg_code OUT NOCOPY VARCHAR2 -- 4537865
2059 )
2060 IS
2061 CURSOR get_tasks IS
2062 SELECT proj_element_id
2063 from pa_proj_elements
2064 where project_id = p_project_id
2065 and object_type = 'PA_TASKS'
2066 and proj_element_id NOT IN (
2067 select task_id
2068 from PA_TASKS
2069 where project_id = p_project_id);
2070 l_task_id NUMBER;
2071
2072 CURSOR get_wp_id IS
2073 SELECT ppe.proj_element_id
2074 from pa_proj_elements ppe,
2075 pa_proj_structure_types ppst,
2076 pa_structure_types pst
2077 where ppe.project_id = p_project_id
2078 and ppe.proj_element_id = ppst.proj_element_id
2079 and ppst.structure_type_id = pst.structure_type_id
2080 and pst.structure_type_class_code = 'WORKPLAN';
2081 l_structure_id NUMBER;
2082 BEGIN
2083 --check if progress exists for structure
2084 OPEN get_wp_id;
2085 FETCH get_wp_id into l_structure_id;
2086 CLOSE get_wp_id;
2087 IF (check_proj_progress_exist(p_project_id, l_structure_id) = 'Y') THEN
2088 x_return_status := 'N';
2089 x_err_msg_code := 'PA_PS_PROJ_PROGRESS_ERR';
2090 return;
2091 END IF;
2092
2093 --bug 2805602
2094 --hsiu: commented out because this is covered in check_del_all_task_ver_ok
2095 -- when enabling sharing.
2096 -- OPEN get_tasks;
2097 -- LOOP
2098 -- FETCH get_tasks into l_task_id;
2099 -- EXIT when get_tasks%NOTFOUND;
2100 -- IF (check_task_progress_exist(l_task_id) = 'Y') THEN
2101 -- CLOSE get_tasks;
2102 -- x_return_status := 'N';
2103 -- x_err_msg_code := 'PA_PS_TASK_PROGRESS_ERR';
2104 -- return;
2105 -- END IF;
2106 -- END LOOP;
2107 -- CLOSE get_tasks;
2108
2109 x_return_status := 'Y';
2110 -- 4537865
2111 EXCEPTION
2112 WHEN OTHERS THEN
2113 x_err_msg_code := SQLCODE;
2114 x_return_status := 'N'; -- Based on Usage of this API
2115
2116 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
2117 p_procedure_name => 'check_sharing_on_ok',
2118 p_error_text => SUBSTRB(SQLERRM,1,240)
2119 );
2120 RAISE ;
2121 END check_sharing_on_ok;
2122
2123 -- API name : CHECK_SHARING_OFF_OK
2124 -- Type : Utils API
2125 -- Pre-reqs : None
2126 -- Return Value : Check if ok to split workplan
2127 -- Return Y or N
2128 --
2129 -- Parameters
2130 -- p_project_id IN NUMBER
2131 --
2132 -- History
2133 --
2134 -- 26-JUL-02 HSIU -Created
2135 --
2136 procedure check_sharing_off_ok
2137 ( p_project_id IN NUMBER
2138 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
2139 ,x_err_msg_code OUT NOCOPY VARCHAR2 -- 4537865
2140 )
2141 IS
2142 CURSOR get_tasks IS
2143 SELECT proj_element_id
2144 from pa_proj_elements
2145 where project_id = p_project_id
2146 and object_type = 'PA_TASKS';
2147 l_task_id NUMBER;
2148 BEGIN
2149 --check if progress exists for current structure
2150 IF (check_proj_progress_exist(p_project_id, 0) = 'Y') THEN
2151 x_err_msg_code := 'PA_PS_PROJ_PROGRESS_ERR';
2152 x_return_status := 'N';
2153 return;
2154 END IF;
2155
2156 OPEN get_tasks;
2157 LOOP
2158 FETCH get_tasks into l_task_id;
2159 EXIT when get_tasks%NOTFOUND;
2160 IF (check_task_progress_exist(l_task_id) = 'Y') THEN
2161 CLOSE get_tasks;
2162 x_err_msg_code := 'PA_PS_TASK_PROGRESS_ERR';
2163 x_return_status := 'N';
2164 return;
2165 END IF;
2166 END LOOP;
2167 CLOSE get_tasks;
2168
2169 x_return_status := 'Y';
2170 -- 4537865
2171 EXCEPTION
2172 WHEN OTHERS THEN
2173 x_err_msg_code := SQLCODE;
2174 x_return_status := 'N'; -- Based on Usage of this API
2175
2176 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
2177 p_procedure_name => 'check_sharing_off_ok',
2178 p_error_text => SUBSTRB(SQLERRM,1,240)
2179 );
2180 RAISE ;
2181 END check_sharing_off_ok;
2182
2183
2184 -- API name : CHECK_PROJ_PROGRESS_EXIST
2185 -- Type : Utils API
2186 -- Pre-reqs : None
2187 -- Return Value : Check if progress exists for project
2188 -- Return Y or N
2189 --
2190 -- Parameters
2191 -- p_project_id IN NUMBER
2192 --
2193 -- History
2194 --
2195 -- 26-JUL-02 HSIU -Created
2196 --
2197 FUNCTION check_proj_progress_exist
2198 ( p_project_id IN NUMBER
2199 ,p_structure_id IN NUMBER
2200 ,p_structure_type IN VARCHAR2 := null
2201 ) return VARCHAR2
2202 IS
2203 BEGIN
2204 --get API from Saima
2205 IF (p_structure_type = 'WORKPLAN')
2206 THEN
2207 return PA_PROGRESS_UTILS.check_project_has_progress(p_project_id, p_structure_id,'WORKPLAN'); -- Added for the BUG 6914708
2208 ELSE
2209 return PA_PROGRESS_UTILS.check_project_has_progress(p_project_id, p_structure_id);
2210 END IF;
2211 END check_proj_progress_exist;
2212
2213
2214 -- API name : CHECK_TASK_PROGRESS_EXIST
2215 -- Type : Utils API
2216 -- Pre-reqs : None
2217 -- Return Value : Check if progress exists for task
2218 -- Return Y or N
2219 --
2220 -- Parameters
2221 -- p_task_id IN NUMBER
2222 --
2223 -- History
2224 --
2225 -- 26-JUL-02 HSIU -Created
2226 --
2227 -- 05-APR-2004 Rakesh Raghavan Progress Management Changes. Bug # 3420093.
2228 --
2229 FUNCTION check_task_progress_exist
2230 ( p_task_id IN NUMBER
2231 ) return VARCHAR2
2232 IS
2233 -- Progress Management Changes. Bug # 3420093.
2234 l_project_id NUMBER;
2235 -- Progress Management Changes. Bug # 3420093.
2236
2237 -- Bug 3933576 : Added cursor c_get_project_id
2238 CURSOR c_get_project_id IS
2239 SELECT project_id
2240 FROM pa_proj_elements
2241 where proj_element_id = p_task_id;
2242
2243 BEGIN
2244 --get API from Saima
2245
2246 -- Progress Management Changes. Bug # 3420093.
2247 -- Bug 3933576 : Call pa_proj_tsk_utils.get_task_project_id gives the project_id from pa_tasks
2248 -- It is wrong..
2249 --l_project_id := pa_proj_tsk_utils.get_task_project_id(p_task_id);
2250 OPEN c_get_project_id;
2251 FETCH c_get_project_id INTO l_project_id;
2252 CLOSE c_get_project_id;
2253
2254 return (pa_progress_utils.check_object_has_prog(p_project_id => l_project_id
2255 -- ,p_proj_element_id => p_task_id));
2256 ,p_object_id => p_task_id));
2257
2258 -- Progress Management Changes. Bug # 3420093.
2259
2260 END check_task_progress_exist;
2261
2262 -- API name : GET_LAST_UPDATED_WORKING_VER
2263 -- Type : Utils API
2264 -- Pre-reqs : None
2265 -- Return Value : Return last update working structure
2266 -- version id
2267 --
2268 -- Parameters
2269 -- p_proj_element_id IN NUMBER
2270 --
2271 -- History
2272 --
2273 -- 26-JUL-02 HSIU -Created
2274 --
2275 FUNCTION GET_LAST_UPDATED_WORKING_VER
2276 ( p_structure_id IN NUMBER
2277 ) return NUMBER
2278 IS
2279 CURSOR c1 IS
2280 select str.element_version_id
2281 from pa_proj_elem_ver_structure str,
2282 pa_proj_elements ppe
2283 where ppe.proj_element_id = p_structure_id
2284 and ppe.project_id = str.project_id
2285 and ppe.proj_element_id = str.proj_element_id
2286 and str.CURRENT_WORKING_FLAG = 'Y';
2287 /*
2288 CURSOR c1 IS
2289 SELECT MAX(a.last_update_date), b.parent_structure_version_id
2290 FROM pa_proj_element_versions b,
2291 pa_proj_elem_ver_schedule a,
2292 pa_proj_elem_ver_structure c
2293 WHERE a.element_version_id (+)= b.element_version_id
2294 AND a.project_id (+) = b.project_id
2295 AND a.proj_element_id (+) = b.proj_element_id
2296 AND b.parent_structure_version_id = c.element_version_id
2297 AND b.project_id = c.project_id
2298 AND c.status_code <> 'STRUCTURE_PUBLISHED'
2299 AND b.proj_element_id = p_structure_id
2300 GROUP BY b.parent_structure_version_id
2301 ORDER BY MAX(a.last_update_date) desc;
2302 */
2303 l_date DATE;
2304 l_structure_version_id NUMBER;
2305 BEGIN
2306 OPEN c1;
2307 FETCH c1 into l_structure_version_id;
2308 -- FETCH c1 into l_date, l_structure_version_id;
2309 CLOSE c1;
2310 return l_structure_version_id;
2311 END GET_LAST_UPDATED_WORKING_VER;
2312
2313
2314 -- API name : CHECK_VERSIONING_ON_OK
2315 -- Type : Utils API
2316 -- Pre-reqs : None
2317 -- Return Value : Check if ok to version workplan
2318 -- Return Y or N
2319 --
2320 -- Parameters
2321 -- p_project_id IN NUMBER
2322 --
2323 -- History
2324 --
2325 -- 26-JUL-02 HSIU -Created
2326 --
2327 procedure check_versioning_on_ok
2328 ( p_proj_element_id IN NUMBER
2329 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
2330 ,x_err_msg_code OUT NOCOPY VARCHAR2 -- 4537865
2331 )
2332 IS
2333 BEGIN
2334 x_return_status := 'Y';
2335 -- 4537865
2336 EXCEPTION
2337 WHEN OTHERS THEN
2338 x_err_msg_code := SQLCODE;
2339 x_return_status := 'N'; -- Based on Usage of this API
2340
2341 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
2342 p_procedure_name => 'check_versioning_on_ok',
2343 p_error_text => SUBSTRB(SQLERRM,1,240)
2344 );
2345 RAISE ;
2346 END check_versioning_on_ok;
2347
2348 -- API name : CHECK_VERSIONING_OFF_OK
2349 -- Type : Utils API
2350 -- Pre-reqs : None
2351 -- Return Value : Check if ok to turn off workplan
2352 -- versioning
2353 -- Return Y or N
2354 --
2355 -- Parameters
2356 -- p_project_id IN NUMBER
2357 --
2358 -- History
2359 --
2360 -- 26-JUL-02 HSIU -Created
2361 --
2362 procedure check_versioning_off_ok
2363 ( p_proj_element_id IN NUMBER
2364 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
2365 ,x_err_msg_code OUT NOCOPY VARCHAR2 -- 4537865
2366 )
2367 IS
2368 CURSOR get_project_id IS
2369 SELECT project_id
2370 FROM PA_PROJ_ELEMENTS
2371 WHERE proj_element_id = p_proj_element_id;
2372
2373 l_project_id NUMBER;
2374 BEGIN
2375 OPEN get_project_id;
2376 FETCH get_project_id INTO l_project_id;
2377 CLOSE get_project_id;
2378
2379 if PA_PROGRESS_UTILS.PROJ_TASK_PROG_EXISTS( l_project_id, 0 ) = 'Y' then
2380 x_return_status := 'N';
2381 x_err_msg_code := 'PA_PS_CANT_DIS_VER';
2382 else
2383 x_return_status := 'Y';
2384 end if;
2385
2386 -- 4537865
2387 EXCEPTION
2388 WHEN OTHERS THEN
2389 x_err_msg_code := SQLCODE;
2390 x_return_status := 'N'; -- Based on Usage of this API
2391
2392 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
2393 p_procedure_name => 'check_versioning_off_ok',
2394 p_error_text => SUBSTRB(SQLERRM,1,240)
2395 );
2396 RAISE ;
2397 END check_versioning_off_ok;
2398
2399
2400 -- API name : CHECK_FIN_TASK_PROG_EXIST
2401 -- Type : Utils API
2402 -- Pre-reqs : None
2403 -- Return Value : Check if progress exists for financial task
2404 -- Return Y or N
2405 --
2406 -- Parameters
2407 -- p_project_id IN NUMBER
2408 --
2409 -- History
2410 --
2411 -- 26-JUL-02 HSIU -Created
2412 --
2413 FUNCTION check_fin_task_prog_exist
2414 ( p_project_id IN NUMBER
2415 ) return VARCHAR2
2416 IS
2417
2418 /* Bug 2680486 -- Performance changes -- Commented the following cursor definition. Restructured it to
2419 avoid Hash Join*/
2420
2421 /* CURSOR c1 is
2422 select 1
2423 from pa_tasks a,
2424 pa_percent_completes b
2425 where b.project_id = a.project_id
2426 and b.task_id = a.task_id
2427 and a.project_id = p_project_id;
2428 */
2429
2430 CURSOR c1 is
2431 select 1
2432 from dual
2433 where exists
2434 ( select 1
2435 from pa_tasks a,
2436 pa_percent_completes b
2437 where b.project_id = a.project_id
2438 and b.task_id = a.task_id
2439 and a.project_id = p_project_id
2440 );
2441
2442 l_dummy NUMBER;
2443 BEGIN
2444 OPEN c1;
2445 FETCH c1 INTO l_dummy;
2446 IF c1%NOTFOUND THEN
2447 CLOSE c1;
2448 return 'N';
2449 ELSE
2450 CLOSE c1;
2451 return 'Y';
2452 END IF;
2453 END check_fin_task_prog_exist;
2454
2455
2456 -- API name : CHECK_WORKING_VERSION_EXIST
2457 -- Type : Utils API
2458 -- Pre-reqs : None
2459 -- Return Value : Check if working version exists for
2460 -- workplan structure
2461 -- Return Y or N
2462 --
2463 -- Parameters
2464 -- p_proj_element_id IN NUMBER
2465 --
2466 -- History
2467 --
2468 -- 26-JUL-02 HSIU -Created
2469 --
2470 FUNCTION check_working_version_exist
2471 ( p_proj_element_id IN NUMBER
2472 ) return VARCHAR2
2473 IS
2474 CURSOR c1 IS
2475 select 1
2476 from pa_proj_elements a,
2477 pa_proj_elem_ver_structure b
2478 where a.proj_element_id = p_proj_element_id
2479 and a.project_id = b.project_id
2480 and a.proj_element_id = b.proj_element_id
2481 and b.status_code <> 'STRUCTURE_PUBLISHED';
2482 l_dummy NUMBER;
2483 BEGIN
2484 OPEN c1;
2485 FETCH c1 into l_dummy;
2486 IF c1%NOTFOUND THEN
2487 CLOSE c1;
2488 return 'N';
2489 ELSE
2490 CLOSE c1;
2491 return 'Y';
2492 END IF;
2493 END check_working_version_exist;
2494
2495
2496 -- API name : CHECK_EDIT_WP_OK
2497 -- Type : Utils API
2498 -- Pre-reqs : None
2499 -- Return Value : Check if the workplan structure version
2500 -- can be edited
2501 -- Return Y or N
2502 --
2503 -- Parameters
2504 -- p_project_id IN NUMBER
2505 -- p_structure_version_id IN NUMBER
2506 --
2507 -- History
2508 --
2509 -- 26-JUL-02 HSIU -Created
2510 --
2511 FUNCTION check_edit_wp_ok
2512 ( p_project_id IN NUMBER
2513 ,p_structure_version_id IN NUMBER
2514 ) return VARCHAR2
2515 IS
2516 l_published VARCHAR2(1);
2517 l_versioned VARCHAR2(1);
2518 BEGIN
2519 l_published := PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(
2520 p_project_id,
2521 p_structure_version_id);
2522 l_versioned := PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(
2523 p_project_id);
2524 IF (l_published = 'Y' and l_versioned = 'Y') THEN
2525 return 'N';
2526 ELSE
2527 return 'Y';
2528 END IF;
2529 END check_edit_wp_ok;
2530
2531 -- API name : CHECK_EDIT_FIN_OK
2532 -- Type : Utils API
2533 -- Pre-reqs : None
2534 -- Return Value : Check if the financial structure version
2535 -- can be edited
2536 -- Return Y or N
2537 --
2538 -- Parameters
2539 -- p_project_id IN NUMBER
2540 -- p_structure_version_id IN NUMBER
2541 --
2542 -- History
2543 --
2544 -- 26-JUL-02 HSIU -Created
2545 --
2546 FUNCTION check_edit_fin_ok
2547 ( p_project_id IN NUMBER
2548 ,p_structure_version_id IN NUMBER
2549 ) return VARCHAR2
2550 IS
2551 l_published VARCHAR2(1);
2552 l_versioned VARCHAR2(1);
2553 l_shared VARCHAR2(1);
2554 BEGIN
2555 l_published := PA_PROJECT_STRUCTURE_UTILS.Check_Struc_Ver_Published(
2556 p_project_id,
2557 p_structure_version_id);
2558 l_versioned := PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(
2559 p_project_id);
2560
2561 l_shared := PA_PROJECT_STRUCTURE_UTILS.Check_Sharing_Enabled(
2562 p_project_id);
2563
2564 IF (l_shared = 'Y' AND l_published = 'N' AND l_versioned = 'Y') THEN
2565 return 'N';
2566 ELSE
2567 return 'Y';
2568 END IF;
2569 END check_edit_fin_ok;
2570
2571
2572
2573 -- API name : GET_FIN_STRUCTURE_ID
2574 -- Type : Utils API
2575 -- Pre-reqs : None
2576 -- Return Value : Get the financial structure id
2577 -- Return structure id for the financial
2578 -- structure
2579 --
2580 -- Parameters
2581 -- p_project_id IN NUMBER
2582 --
2583 -- History
2584 --
2585 -- 26-JUL-02 HSIU -Created
2586 --
2587 FUNCTION GET_FIN_STRUCTURE_ID
2588 ( p_project_id IN NUMBER
2589 ) return NUMBER
2590 IS
2591 CURSOR c1 IS
2592 select ppe.proj_element_id
2593 from pa_proj_elements ppe,
2594 pa_proj_structure_types ppst,
2595 pa_structure_types pst
2596 where ppe.project_id = p_project_id
2597 and ppe.proj_element_id = ppst.proj_element_id
2598 and ppst.structure_type_id = pst.structure_type_id
2599 and pst.structure_type_class_code = 'FINANCIAL';
2600 l_structure_id NUMBER;
2601 BEGIN
2602 OPEN c1;
2603 FETCH c1 into l_structure_id;
2604 CLOSE c1;
2605 return l_structure_id;
2606 END GET_FIN_STRUCTURE_ID;
2607
2608 -- API name : GET_LATEST_FIN_STRUC_VER_ID
2609 -- Type : Utils API
2610 -- Pre-reqs : None
2611 -- Return Value : Get the latest financial structure version
2612 -- id. Return structure version id for the
2613 -- latest financial structure version. Return
2614 -- NULL if no published version exists.
2615 --
2616 -- Parameters
2617 -- p_project_id IN NUMBER
2618 --
2619 -- History
2620 --
2621 -- 26-JUL-02 HSIU -Created
2622 --
2623 FUNCTION GET_LATEST_FIN_STRUC_VER_ID
2624 ( p_project_id IN NUMBER
2625 ) return NUMBER
2626 IS
2627 CURSOR c1 IS
2628 select ppevs.element_version_id
2629 from pa_proj_elements ppe,
2630 pa_proj_structure_types ppst,
2631 pa_structure_types pst,
2632 pa_proj_elem_ver_structure ppevs
2633 where ppe.project_id = p_project_id
2634 and ppe.proj_element_id = ppst.proj_element_id
2635 and ppe.project_id = ppevs.project_id
2636 and ppe.proj_element_id = ppevs.proj_element_id
2637 and ppevs.status_code = 'STRUCTURE_PUBLISHED'
2638 /* Commented for Bug 4998101
2639 and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'*/
2640 and ppst.structure_type_id = pst.structure_type_id
2641 -- and ppevs.process_code is null -- Added for Bug Bug 4998101
2642 and ( ppevs.process_code is null or ppevs.process_code = 'PRE') -- Added for Bug #5659575
2643 and pst.structure_type_class_code = 'FINANCIAL'
2644 order by ppevs.published_date desc; -- Added for Bug Bug 4998101
2645 l_structure_version_id NUMBER;
2646
2647 CURSOR c2 IS
2648 select ppevs.element_version_id
2649 from pa_proj_elements ppe,
2650 pa_proj_structure_types ppst,
2651 pa_structure_types pst,
2652 pa_proj_elem_ver_structure ppevs
2653 where ppe.project_id = p_project_id
2654 and ppe.proj_element_id = ppst.proj_element_id
2655 and ppe.project_id = ppevs.project_id
2656 and ppe.proj_element_id = ppevs.proj_element_id
2657 and ppevs.status_code <> 'STRUCTURE_PUBLISHED'
2658 and ppst.structure_type_id = pst.structure_type_id
2659 and pst.structure_type_class_code = 'FINANCIAL';
2660
2661 BEGIN
2662 OPEN c1;
2663 FETCH c1 into l_structure_version_id;
2664 IF c1%NOTFOUND THEN
2665 OPEN c2;
2666 FETCH c2 into l_structure_version_id;
2667 CLOSE c2;
2668 END IF;
2669 CLOSE c1;
2670
2671 return l_structure_version_id;
2672
2673 END GET_LATEST_FIN_STRUC_VER_ID;
2674
2675 -- API name : GET_LATEST_FIN_STRUC_VER_ID
2676 -- Type : Utils API
2677 -- Pre-reqs : None
2678 -- Return Value : 'FINANCIAL' for a financial only task or
2679 -- structure, 'WORKPLAN' for a workplan only
2680 -- or shared structure.
2681 --
2682 -- Parameters
2683 -- p_project_id IN NUMBER
2684 -- p_proj_element_id IN NUMBER
2685 -- p_object_type IN VARCHAR2
2686 --
2687 -- History
2688 --
2689 -- 23-OCT-02 HSIU -Created
2690 FUNCTION get_element_struc_type
2691 ( p_project_id IN NUMBER
2692 ,p_proj_element_id IN NUMBER
2693 ,p_object_type IN VARCHAR2
2694 ) return VARCHAR2
2695 IS
2696 CURSOR get_parent_structure IS
2697 select a.proj_element_id
2698 from pa_proj_element_versions a,
2699 pa_proj_element_versions b
2700 where b.proj_element_id = p_proj_element_id
2701 and b.project_id = p_project_id
2702 and b.parent_structure_version_id = a.element_version_id
2703 and a.project_id = p_project_id;
2704 l_structure_id NUMBER;
2705 l_ret VARCHAR2(30);
2706 BEGIN
2707 --Find structure
2708 IF (p_object_type = 'PA_TASKS') THEN
2709 OPEN get_parent_structure;
2710 FETCH get_parent_structure into l_structure_id;
2711 CLOSE get_parent_structure;
2712 ELSE --'PA_STRUCTURES'
2713 l_structure_id := p_proj_element_id;
2714 END IF;
2715
2716 --Get Type
2717 IF ('Y' = Get_Struc_Type_For_Structure(l_structure_id, 'WORKPLAN')) THEN
2718 l_ret := 'WORKPLAN';
2719 ELSE
2720 l_ret := 'FINANCIAL';
2721 END IF;
2722 return l_ret;
2723 END get_element_struc_type;
2724
2725
2726 FUNCTION GET_LATEST_WP_VERSION
2727 ( p_project_id IN NUMBER
2728 ) return NUMBER
2729 IS
2730 CURSOR c1 IS
2731 select ppevs.element_version_id
2732 from pa_proj_elements ppe,
2733 pa_proj_structure_types ppst,
2734 pa_structure_types pst,
2735 pa_proj_elem_ver_structure ppevs
2736 where ppe.project_id = p_project_id
2737 and ppe.proj_element_id = ppst.proj_element_id
2738 and ppe.project_id = ppevs.project_id
2739 and ppe.proj_element_id = ppevs.proj_element_id
2740 and ppevs.status_code = 'STRUCTURE_PUBLISHED'
2741 and ppevs.LATEST_EFF_PUBLISHED_FLAG = 'Y'
2742 and ppst.structure_type_id = pst.structure_type_id
2743 and pst.structure_type_class_code = 'WORKPLAN';
2744 l_structure_version_id NUMBER;
2745
2746 BEGIN
2747 OPEN c1;
2748 FETCH c1 into l_structure_version_id;
2749 CLOSE c1;
2750 return l_structure_version_id;
2751
2752 END GET_LATEST_WP_VERSION;
2753
2754
2755 -- API name : Check_del_work_struc_ver_ok
2756 -- Type : Utils API
2757 -- Pre-reqs : None
2758 -- Return Value : Check if ok to delete working structure
2759 -- version
2760 -- Return Y or N
2761 --
2762 -- Parameters
2763 -- p_structure_version_id IN NUMBER
2764 --
2765 -- History
2766 --
2767 -- 26-JUL-02 HSIU -Created
2768 --
2769 FUNCTION check_del_work_struc_ver_ok
2770 ( p_structure_version_id IN NUMBER
2771 ) return VARCHAR2
2772 IS
2773 l_project_id NUMBER;
2774 l_structure_id NUMBER;
2775 l_count NUMBER;
2776
2777 CURSOR c1 IS
2778 select project_id, proj_element_id
2779 from pa_proj_element_versions
2780 where element_version_id = p_structure_version_id;
2781
2782 CURSOR c2 IS
2783 select count(1)
2784 from pa_proj_elem_ver_structure
2785 where project_Id = l_project_id
2786 and proj_element_id = l_structure_id
2787 and status_code <> 'STRUCTURE_PUBLISHED';
2788
2789 BEGIN
2790 OPEN c1;
2791 FETCH c1 into l_project_id, l_structure_id;
2792 CLOSE c1;
2793
2794 IF ('N' = pa_project_structure_utils.check_published_ver_exists(l_project_id, l_structure_id)) THEN
2795 OPEN c2;
2796 FETCH c2 into l_count;
2797 CLOSE c2;
2798
2799 IF (l_count = 1) THEN
2800 return 'N';
2801 END IF;
2802 ELSE
2803 return 'Y';
2804 END IF;
2805 return 'Y';
2806 END check_del_work_struc_ver_ok;
2807
2808
2809 -- API name : Check_txn_on_summary_tasks
2810 -- Type : Utils API
2811 -- Pre-reqs : None
2812 -- Return Value : Y if transactions exist on summary task
2813 -- N if not
2814 -- Parameters
2815 -- p_structure_version_id IN NUMBER
2816 --
2817 -- History
2818 --
2819 -- 24-MAY-01 HSIU -Created
2820 --
2821 --
2822 PROCEDURE Check_txn_on_summary_tasks
2823 (
2824 p_structure_version_id IN NUMBER
2825 ,x_return_status OUT NOCOPY VARCHAR2
2826 ,x_msg_count OUT NOCOPY NUMBER
2827 ,x_msg_data OUT NOCOPY VARCHAR2
2828 )
2829 IS
2830 --cursor to select summary tasks
2831 CURSOR c1 IS
2832 select ppev.proj_element_id, ppev.element_version_id
2833 from pa_proj_element_versions ppev,
2834 pa_object_relationships por
2835 where ppev.parent_structure_version_id = p_structure_version_id
2836 and ppev.object_type = 'PA_TASKS'
2837 and ppev.element_version_id = por.object_id_from1
2838 and ppev.object_type = por.object_type_from
2839 and por.relationship_type = 'S';
2840 l_workplan VARCHAR2(1);
2841 l_financial VARCHAR2(1);
2842 l_err_code VARCHAR2(2000);
2843 l_err_stage VARCHAR2(2000);
2844 l_err_stack VARCHAR2(2000);
2845 l_proj_element_id NUMBER;
2846 l_element_version_id NUMBER;
2847
2848 --bug 4068736
2849 CURSOR c2(c_task_ver_id number) IS
2850 select 1
2851 from pa_proj_element_versions ppev,
2852 pa_object_relationships por
2853 where por.object_id_from1 = c_task_ver_id
2854 and por.relationship_type = 'S'
2855 and por.object_id_to1 = ppev.element_version_id
2856 and ppev.financial_task_flag = 'Y'
2857 and rownum = 1;
2858 --bug 4068736
2859
2860 CURSOR get_task_name_num(c_proj_element_id NUMBER) IS
2861 select name, element_number
2862 from pa_proj_elements
2863 where proj_element_id = c_proj_element_id;
2864 l_task_name PA_PROJ_ELEMENTS.NAME%TYPE;
2865 l_task_number PA_PROJ_ELEMENTS.ELEMENT_NUMBER%TYPE;
2866 l_message_text VARCHAR2(2000);
2867
2868 l_dummy NUMBER;
2869 BEGIN
2870 l_workplan := Get_Struc_Type_For_Version(p_structure_version_id, 'WORKPLAN');
2871 l_financial := Get_Struc_Type_For_Version(p_structure_version_id, 'FINANCIAL');
2872
2873 OPEN c1;
2874 LOOP
2875 FETCH c1 into l_proj_element_id, l_element_version_id;
2876 EXIT WHEN c1%NOTFOUND;
2877
2878 --removed for bug 2740802
2879 --summary task can have progress transactions
2880 -- IF (l_workplan = 'Y') THEN
2881 --check for progress
2882 -- IF pa_project_structure_utils.check_task_progress_exist(l_proj_element_id) = 'Y' THEN
2883 -- l_message_text := FND_MESSAGE.GET_STRING('PA','PA_PS_TASK_HAS_PROG');
2884 -- OPEN get_task_name_num(l_proj_element_id);
2885 -- FETCH get_task_name_num into l_task_name, l_task_number;
2886 -- CLOSE get_task_name_num;
2887 -- PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_TASK_NAME_NUM_ERR',
2888 -- 'TASK_NAME', l_task_name,
2889 -- 'TASK_NUMBER', l_task_number,
2890 -- 'MESSAGE', l_message_text);
2891 -- END IF;
2892 -- END IF;
2893
2894 IF (l_financial = 'Y') THEN
2895 --hsiu: changes for bug 2817687
2896 BEGIN
2897 select 1 into l_dummy
2898 from pa_tasks where task_id = l_proj_element_id;
2899
2900 --bug 4068736
2901 OPEN c2(l_element_version_id);
2902 fetch c2 into l_dummy;
2903 IF c2%FOUND THEN
2904 --Bug 2947492:The following api call is modified to pass parameters by notation.
2905 PA_TASK_UTILS.CHECK_CREATE_SUBTASK_OK(
2906 x_task_id => l_proj_element_id,
2907 x_err_code => l_err_code,
2908 x_err_stage => l_err_stage,
2909 x_err_stack => l_err_stack);
2910
2911 IF (l_err_code <> 0) THEN
2912 l_message_text := FND_MESSAGE.GET_STRING('PA',l_err_stage);
2913 OPEN get_task_name_num(l_proj_element_id);
2914 FETCH get_task_name_num into l_task_name, l_task_number;
2915 CLOSE get_task_name_num;
2916 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_TASK_NAME_NUM_ERR',
2917 'TASK_NAME', l_task_name,
2918 'TASK_NUMBER', l_task_number,
2919 'MESSAGE', l_message_text);
2920 END IF;
2921 END IF;
2922 CLOSE c2;
2923 --bug 4068736
2924 EXCEPTION
2925 WHEN NO_DATA_FOUND THEN
2926 NULL;
2927 WHEN OTHERS THEN
2928 NULL;
2929 END; --for bug 2817687
2930 END IF;
2931 END LOOP;
2932 CLOSE c1;
2933
2934 x_msg_count := FND_MSG_PUB.count_msg;
2935 IF (x_msg_count > 0) THEN
2936 raise FND_API.G_EXC_ERROR;
2937 END IF;
2938
2939 x_return_status := FND_API.G_RET_STS_SUCCESS;
2940 EXCEPTION
2941 WHEN FND_API.G_EXC_ERROR THEN
2942 x_return_status := FND_API.G_RET_STS_ERROR;
2943 x_msg_count := FND_MSG_PUB.count_msg; -- 4537865
2944 x_msg_data := l_message_text ; -- 4537865
2945
2946 WHEN OTHERS THEN
2947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2948 x_msg_count := 1; -- 4537865
2949 x_msg_data := SQLERRM ; -- 4537865
2950 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
2951 p_procedure_name => 'Check_txn_on_summary_tasks',
2952 p_error_text => x_msg_data); -- 4537865
2953 RAISE;
2954 END Check_txn_on_summary_tasks;
2955
2956
2957 PROCEDURE check_tasks_statuses_valid
2958 (
2959 p_structure_version_id IN NUMBER
2960 ,x_return_status OUT NOCOPY VARCHAR2
2961 ,x_msg_count OUT NOCOPY NUMBER
2962 ,x_msg_data OUT NOCOPY VARCHAR2
2963 )
2964 IS
2965 CURSOR c1 IS
2966 select ppev.proj_element_id, ppev.element_version_id
2967 from pa_proj_element_versions ppev,
2968 pa_object_relationships por
2969 where ppev.parent_structure_version_id = p_structure_version_id
2970 and ppev.object_type = 'PA_TASKS'
2971 and ppev.element_version_id = por.object_id_from1
2972 and ppev.object_type = por.object_type_from
2973 and por.relationship_type = 'S';
2974 l_proj_element_id NUMBER;
2975 l_element_version_id NUMBER;
2976
2977 CURSOR c2(c_proj_element_id NUMBER) IS
2978 select pps.project_system_status_code
2979 from pa_project_statuses pps,
2980 pa_proj_elements ppe
2981 where ppe.proj_element_id = c_proj_element_id
2982 and ppe.status_code = pps.project_status_code
2983 and pps.status_type = 'TASK'
2984 and pps.project_system_status_code = 'CANCELLED';
2985
2986 CURSOR c3(c_parent_task_ver_id NUMBER) IS
2987 select pps.project_system_status_code
2988 from pa_project_statuses pps,
2989 pa_proj_elements ppe,
2990 pa_proj_element_versions ppev,
2991 pa_object_relationships por
2992 where por.object_id_from1 = c_parent_task_ver_id
2993 and por.object_type_from = 'PA_TASKS'
2994 and por.relationship_type = 'S'
2995 and por.object_type_to = 'PA_TASKS'
2996 and por.object_id_to1 = ppev.element_version_id
2997 and ppev.project_id = ppe.project_id
2998 and ppev.proj_element_id = ppe.proj_element_id
2999 and ppev.object_type = ppe.object_type
3000 and ppe.status_code = pps.project_status_code
3001 and pps.status_type = 'TASK'
3002 and pps.project_system_status_code NOT IN ('CANCELLED','COMPLETED')
3003 and ppev.TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED';
3004 l_system_status_code pa_project_statuses.project_system_status_code%TYPE;
3005
3006 CURSOR get_task_name_num(c_proj_element_id NUMBER) IS
3007 select name, element_number
3008 from pa_proj_elements
3009 where proj_element_id = c_proj_element_id;
3010 l_task_name PA_PROJ_ELEMENTS.NAME%TYPE;
3011 l_task_number PA_PROJ_ELEMENTS.ELEMENT_NUMBER%TYPE;
3012
3013 BEGIN
3014 OPEN c1;
3015 LOOP
3016 FETCH c1 into l_proj_element_id, l_element_version_id;
3017 EXIT when c1%NOTFOUND;
3018 --if summary task exists, check if status is contsistent with child tasks
3019 OPEN c2(l_proj_element_id);
3020 FETCH c2 INTO l_system_status_code;
3021 IF c2%FOUND THEN
3022 --if any task is found, that means the statuses between
3023 --parent and child can be inconsistent. Check child statuses
3024 OPEN c3(l_element_version_id);
3025 FETCH c3 into l_system_status_code;
3026 IF (c3%FOUND) THEN
3027 --inconsistent statuses. Error
3028 -- x_error_message_code := 'PA_PS_PUB_TK_STATS_ERR';
3029 -- raise FND_API.G_EXC_ERROR;
3030 OPEN get_task_name_num(l_proj_element_id);
3031 FETCH get_task_name_num into l_task_name, l_task_number;
3032 CLOSE get_task_name_num;
3033 PA_UTILS.ADD_MESSAGE('PA', 'PA_PS_PUB_TK_STATS_ERR',
3034 'TASK_NAME', l_task_name,
3035 'TASK_NUMBER', l_task_number);
3036 END IF;
3037 CLOSE c3;
3038 END IF;
3039 CLOSE c2;
3040 END LOOP;
3041 CLOSE c1;
3042
3043 x_msg_count := FND_MSG_PUB.count_msg;
3044 IF (x_msg_count > 0) THEN
3045 raise FND_API.G_EXC_ERROR;
3046 END IF;
3047
3048 x_return_status := FND_API.G_RET_STS_SUCCESS;
3049
3050 EXCEPTION
3051 WHEN FND_API.G_EXC_ERROR THEN
3052 x_return_status := FND_API.G_RET_STS_ERROR;
3053 x_msg_count := FND_MSG_PUB.count_msg; -- 4537865
3054 x_msg_data := 'PA_PS_PUB_TK_STATS_ERR'; -- 4537865
3055
3056 WHEN OTHERS THEN
3057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3058 x_msg_count := 1; -- 4537865
3059 x_msg_data := SQLERRM ; -- 4537865
3060 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
3061 p_procedure_name => 'Check_tasks_statuses_valid',
3062 p_error_text => x_msg_data ); -- 4537865
3063 RAISE;
3064 END check_tasks_statuses_valid;
3065
3066
3067 -- API name : get_unpub_version_count
3068 -- Type : Utils API
3069 -- Pre-reqs : None
3070 -- Return Value : number of unpublished structure versions
3071 -- Parameters
3072 -- p_project_id IN NUMBER
3073 -- p_structure_id IN NUMBER
3074 --
3075 -- History
3076 --
3077 -- 25-JUN-01 HSIU -Created
3078 --
3079 --
3080
3081
3082 function get_unpub_version_count
3083 (
3084 p_project_id IN NUMBER
3085 ,p_structure_ver_id IN NUMBER
3086 ) return NUMBER
3087 IS
3088 CURSOR c1 IS
3089 select count(1)
3090 from pa_proj_elem_ver_structure
3091 where project_id = p_project_id
3092 and proj_element_id =
3093 (select proj_element_id
3094 from pa_proj_element_versions
3095 where element_version_id = p_structure_ver_id)
3096 and status_code <> 'STRUCTURE_PUBLISHED';
3097 l_cnt NUMBER;
3098 BEGIN
3099 OPEN c1;
3100 FETCH c1 into l_cnt;
3101 CLOSE c1;
3102 return l_cnt;
3103 END get_unpub_version_count;
3104
3105 -- API name : get_structrue_version_status
3106 -- Type : Utils API
3107 -- Pre-reqs : None
3108 -- Return Value : Get the status of a structure version
3109 -- Parameters
3110 -- p_project_id IN NUMBER
3111 -- p_structure_version_id IN NUMBER
3112 --
3113 -- History
3114 --
3115 -- 08-JAN-03 maansari -Created
3116 --
3117 --
3118
3119
3120 function get_structrue_version_status
3121 (
3122 p_project_id IN NUMBER
3123 ,p_structure_version_id IN NUMBER
3124 ) return VARCHAR2 IS
3125
3126 CURSOR cur_pa_proj_elem_ver_str
3127 IS
3128 SELECT status_code
3129 FROM pa_proj_elem_ver_structure
3130 WHERE project_id = p_project_id
3131 AND element_version_id = p_structure_version_id;
3132 l_str_status_code VARCHAR2(150);
3133 BEGIN
3134
3135 OPEN cur_pa_proj_elem_ver_str;
3136 FETCH cur_pa_proj_elem_ver_str INTO l_str_status_code;
3137 CLOSE cur_pa_proj_elem_ver_str;
3138
3139 RETURN l_str_status_code;
3140 END get_structrue_version_status;
3141
3142
3143 function is_structure_version_updatable
3144 (
3145 p_structure_version_id IN NUMBER
3146 ) return VARCHAR2 IS
3147 cursor c1 IS
3148 select ppa.project_id, ppa.template_flag
3149 from pa_projects_all ppa,
3150 pa_proj_element_versions ppev
3151 where ppev.element_version_id = p_structure_version_id
3152 and ppev.project_id = ppa.project_id;
3153 l_project_id NUMBER;
3154 l_template_flag VARCHAR2(1);
3155
3156 cursor c2 IS
3157 select 1
3158 from pa_proj_elem_ver_structure
3159 where project_id = l_project_id
3160 and element_version_id = p_structure_version_id
3161 and status_code = 'STRUCTURE_WORKING';
3162 l_dummy NUMBER;
3163 BEGIN
3164 OPEN c1;
3165 FETCH c1 into l_project_id, l_template_flag;
3166 CLOSE c1;
3167
3168 IF l_template_flag = 'Y' THEN
3169 return 'Y';
3170 END IF;
3171
3172 --check if shared or split
3173 IF (PA_PROJECT_STRUCTURE_UTILS.Check_Sharing_Enabled(l_project_id) = 'Y')
3174 THEN
3175 --shared; check structure version status
3176 IF (PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_project_id) = 'Y')
3177 THEN
3178 --versioning enabled. Check structure version status
3179 OPEN c2;
3180 FETCH c2 into l_dummy;
3181 CLOSE c2;
3182
3183 IF (l_dummy = 1) THEN
3184 --found; check if locked by another user
3185 IF (IS_STRUC_VER_LOCKED_BY_USER(FND_GLOBAL.USER_ID,
3186 p_structure_version_id) = 'O') THEN
3187 --added for bug 3071008
3188 IF (PA_SECURITY_PVT.check_user_privilege('PA_UNLOCK_ANY_STRUCTURE'
3189 ,NULL
3190 ,to_number(NULL))
3191 = FND_API.G_TRUE) THEN
3192 return 'Y';
3193 END IF;
3194 --end bug 3071008
3195 return 'N';
3196 ELSE
3197 return 'Y';
3198 END IF;
3199 ELSE
3200 --not found; not a working version
3201 return 'N';
3202 END IF;
3203 ELSE
3204 --versioning disabled
3205 return 'Y';
3206 END IF;
3207 ELSE
3208 --split; check structure type
3209 IF (GET_STRUC_TYPE_FOR_VERSION(p_structure_version_id, 'FINANCIAL') = 'Y') THEN
3210 --split financial; ok to edit
3211 return 'Y';
3212 ELSE
3213 --split workplan; check versioning
3214 IF (PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(l_project_id) = 'Y')
3215 THEN
3216 --versioning enabled. Check structure version status
3217 OPEN c2;
3218 FETCH c2 into l_dummy;
3219 CLOSE c2;
3220 IF (l_dummy = 1) THEN
3221 --found; check if locked by another user
3222 IF (IS_STRUC_VER_LOCKED_BY_USER(FND_GLOBAL.USER_ID,
3223 p_structure_version_id) = 'O') THEN
3224 --added for bug 3071008
3225 IF (PA_SECURITY_PVT.check_user_privilege('PA_UNLOCK_ANY_STRUCTURE'
3226 ,NULL
3227 ,to_number(NULL))
3228 = FND_API.G_TRUE) THEN
3229 return 'Y';
3230 END IF;
3231 --end bug 3071008
3232 return 'N';
3233 ELSE
3234 return 'Y';
3235 END IF;
3236 ELSE
3237 --not found; not working ver
3238 return 'N';
3239 END IF;
3240 ELSE
3241 --versioning disabled
3242 return 'Y';
3243 END IF;
3244
3245 END IF;
3246 END IF;
3247
3248 END is_structure_version_updatable;
3249
3250 -- Bug 3010538
3251 -- This function will return the process_update_wbs_flag of the structure version.
3252 FUNCTION GET_UPDATE_WBS_FLAG(
3253 p_project_id IN pa_projects_all.project_id%TYPE
3254 ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
3255 )
3256 return VARCHAR2 IS
3257 -- This cursor gets the process_update_wbs_flag for the structure version.
3258 cursor cur_update_wbs_flag(c_project_id pa_projects_all.project_id%TYPE,
3259 c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
3260 IS
3261 select process_update_wbs_flag
3262 from pa_proj_elem_ver_structure
3263 where project_id = c_project_id
3264 and element_version_id = c_structure_version_id;
3265
3266 l_update_wbs_flag pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE;
3267 BEGIN
3268 OPEN cur_update_wbs_flag(p_project_id,p_structure_version_id);
3269 fetch cur_update_wbs_flag into l_update_wbs_flag;
3270 close cur_update_wbs_flag;
3271 return nvl(l_update_wbs_flag,'N');
3272 END GET_UPDATE_WBS_FLAG;
3273
3274
3275 -- Bug 3010538
3276 -- This is a function that returns the status of the cocurrent program for any
3277 -- particular structure version. If the process code is null, the function returns
3278 -- null and the caller has to do suitable null handling.
3279 FUNCTION GET_PROCESS_STATUS_CODE(
3280 p_project_id IN pa_projects_all.project_id%TYPE
3281 ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
3282 )
3283 return VARCHAR2 IS
3284 -- This cursor fetches the process code for the structure version.
3285 cursor cur_process_code(c_project_id pa_projects_all.project_id%TYPE,
3286 c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
3287 is
3288 select process_code
3289 from pa_proj_elem_ver_structure
3290 where project_id = c_project_id
3291 and element_version_id = c_structure_version_id;
3292 l_process_code pa_proj_elem_ver_structure.process_code%TYPE;
3293 BEGIN
3294 open cur_process_code(p_project_id,p_structure_version_id);
3295 fetch cur_process_code into l_process_code;
3296 close cur_process_code;
3297 return l_process_code;
3298 END GET_PROCESS_STATUS_CODE;
3299
3300
3301 -- Bug 3010538
3302 -- This API will be used to determine from the profile vaues if we need to do concurrent
3303 -- processing or online processing. The function returns ONLINE if the process has to be
3304 -- launched online or CONCURRENT if the process is to be launched as a concurrent process.
3305 FUNCTION GET_PROCESS_WBS_UPDATES_OPTION(
3306 p_task_count IN NUMBER
3307 ,p_project_id IN NUMBER default null --bug 4370533
3308 )
3309 return VARCHAR2 IS
3310
3311 l_wbs_update_option fnd_lookups.meaning%TYPE;
3312 l_threshold_task_cnt number;
3313
3314 CURSOR cur_link_exists
3315 IS
3316 Select 'x' from pa_object_relationships
3317 where relationship_type = 'LW'
3318 and (object_id_from2 = p_project_id or object_id_to2 = p_project_id);
3319
3320 l_dummy_link_char VARCHAR2(1);
3321
3322 -- Begin Bug # 4611527.
3323 CURSOR cur_del_lnk_event
3324 IS
3325 SELECT 1
3326 FROM dual
3327 WHERE EXISTS (SELECT LOG.event_id
3328 FROM pji_pa_proj_events_log LOG,
3329 pa_proj_element_versions ver
3330 WHERE LOG.event_type='PRG_CHANGE'
3331 AND LOG.event_object =TO_CHAR(ver.prg_group)
3332 AND ver.project_id=p_project_id
3333 UNION ALL
3334 SELECT LOG.event_id
3335 FROM pa_pji_proj_events_log LOG,
3336 pa_proj_element_versions ver
3337 WHERE LOG.event_type='PRG_CHANGE'
3338 AND LOG.event_object =TO_CHAR(ver.prg_group)
3339 AND ver.project_id=p_project_id);
3340 -- End Bug # 4611527.
3341
3342 BEGIN
3343
3344 IF p_project_id IS NOT NULL
3345 THEN
3346 OPEN cur_link_exists;
3347 FETCH cur_link_exists INTO l_dummy_link_char;
3348 IF cur_link_exists%FOUND
3349 THEN
3350 close cur_link_exists; -- Bug#6678573
3351 return 'CONCURRENT';
3352 END IF;
3353 close cur_link_exists; -- Bug#6678573
3354
3355 -- Begin Bug # 4611527.
3356 OPEN cur_del_lnk_event;
3357 FETCH cur_del_lnk_event INTO l_dummy_link_char;
3358 IF cur_del_lnk_event%FOUND
3359 THEN
3360 close cur_del_lnk_event; -- Bug#6678573
3361 return 'CONCURRENT';
3362 END IF;
3363 CLOSE cur_del_lnk_event;
3364 -- End Bug # 4611527.
3365
3366 END IF;
3367
3368 l_wbs_update_option := nvl(FND_PROFILE.value('PA_PROCESS_WBS_UPDATES'),'STANDARD');
3369 IF l_wbs_update_option = 'ONLINE' THEN
3370 return 'ONLINE';
3371 ELSE
3372 l_threshold_task_cnt := nvl(FND_PROFILE.value('PA_PROC_WBS_UPD_THRESHOLD'),50);
3373 IF p_task_count > l_threshold_task_cnt THEN
3374 return 'CONCURRENT';
3375 ELSE
3376 return 'ONLINE';
3377 END IF;
3378 END IF;
3379
3380 EXCEPTION
3381
3382 WHEN OTHERS THEN
3383 close cur_link_exists; -- Bug#6678573
3384 close cur_del_lnk_event; -- Bug#6678573
3385
3386
3387 END GET_PROCESS_WBS_UPDATES_OPTION;
3388
3389
3390 -- Bug 3010538
3391 -- This is an overloaded function that will accept the project id and the structure type
3392 -- and return the status code of the only structure version that can be either in status WUP / WUE.
3393 -- If none of the structure versions are in the above mentioned statuses, the function will return NULL.
3394
3395 FUNCTION GET_PROCESS_STATUS_CODE(
3396 p_project_id IN pa_projects_all.project_id%TYPE
3397 ,p_structure_type IN pa_structure_types.structure_type%TYPE
3398 )
3399 return VARCHAR2 IS
3400 -- This cursor fetches the process code of WUP or WUE if the process is in
3401 -- progress for any of the structure versions or if the process has errored out.
3402 Cursor cur_proc_ver(c_project_id pa_projects_all.project_id%TYPE,
3403 c_structure_type pa_structure_types.structure_type%TYPE)
3404 is
3405 select vs.process_code
3406 from pa_proj_elem_ver_structure vs,
3407 pa_proj_structure_types pst,
3408 pa_structure_types st
3409 where
3410 vs.project_id = c_project_id
3411 and vs.process_code in ('WUP','WUE')
3412 and pst.PROJ_ELEMENT_ID = vs.proj_element_id
3413 and pst.structure_type_id = st.structure_type_id
3414 and st.structure_type = c_structure_type;
3415
3416 l_process_code FND_LOOKUPS.lookup_code%TYPE;
3417 BEGIN
3418
3419 open cur_proc_ver(p_project_id,p_structure_type);
3420 fetch cur_proc_ver into l_process_code;
3421 close cur_proc_ver;
3422
3423 return l_process_code;
3424
3425 END GET_PROCESS_STATUS_CODE;
3426
3427
3428 function GET_FIN_STRUC_VER_ID(p_project_id IN NUMBER) return NUMBER
3429 IS
3430 cursor projOrTemp IS
3431 select TEMPLATE_FLAG from pa_projects_all where project_id = p_project_id;
3432 l_template_flag VARCHAR2(1);
3433
3434 l_struc_ver_id NUMBER;
3435 cursor getFinOnlyStrucVer(c_project_id NUMBER) IS
3436 /*Commented the Following SQL Query for Performance Bug fix : 3968091
3437 select pev.element_version_id
3438 from pa_proj_element_versions pev, pa_proj_elements pe,
3439 pa_proj_structure_types pst
3440 where pe.project_id = c_project_id
3441 and pe.project_id = pev.project_id
3442 and pe.proj_element_id = pev.proj_element_id
3443 and pe.object_type = 'PA_STRUCTURES'
3444 and pe.proj_element_id = pst.proj_element_id
3445 and pst.structure_type_id = 6;
3446 */
3447 select pev.element_version_id /*New Query with improved Performance : 3968091 */
3448 from pa_proj_element_versions pev,
3449 pa_proj_structure_types pst
3450 where pev.project_id = c_project_id
3451 and pev.object_type = 'PA_STRUCTURES'
3452 and pev.proj_element_id = pst.proj_element_id
3453 and pst.structure_type_id = 6;
3454
3455 BEGIN
3456 OPEN projOrTemp;
3457 FETCH projOrTemp into l_template_flag;
3458 CLOSE projOrTemp;
3459
3460 IF l_template_flag = 'Y' THEN
3461 --this is a template; only has one version of financial structure
3462 OPEN getFinOnlyStrucVer(p_project_id);
3463 FETCH getFinOnlyStrucVer into l_struc_ver_id;
3464 CLOSE getFinOnlyStrucVer;
3465 return l_struc_ver_id;
3466 ELSE
3467 --this is a project; check if workplan is enabled
3468 IF (PA_PROJECT_STRUCTURE_UTILS.Check_Workplan_enabled(p_project_id) = 'N') THEN
3469 --workplan is not enabled; financial structure only
3470 OPEN getFinOnlyStrucVer(p_project_id);
3471 FETCH getFinOnlyStrucVer into l_struc_ver_id;
3472 CLOSE getFinOnlyStrucVer;
3473 return l_struc_ver_id;
3474 ELSE
3475 --Workplan enabled; check if it is shared of split
3476 IF (PA_PROJECT_STRUCTURE_UTILS.Check_Sharing_Enabled(p_project_id) = 'Y') THEN
3477 --this is a shared project; check if versioning is enabled
3478 IF (PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(p_project_id) = 'Y') THEN
3479 --get latest published, if published exist
3480 l_struc_ver_id := PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID(p_project_id);
3481 IF l_struc_ver_id IS NULL THEN
3482 --get working version, which is the only version
3483 OPEN getFinOnlyStrucVer(p_project_id);
3484 FETCH getFinOnlyStrucVer into l_struc_ver_id;
3485 CLOSE getFinOnlyStrucVer;
3486 END IF;
3487 return l_struc_ver_id;
3488 ELSE
3489 --get the only version
3490 OPEN getFinOnlyStrucVer(p_project_id);
3491 FETCH getFinOnlyStrucVer into l_struc_ver_id;
3492 CLOSE getFinOnlyStrucVer;
3493 return l_struc_ver_id;
3494 END IF;
3495 ELSE
3496 --this is a split project; should only have one structure version
3497 OPEN getFinOnlyStrucVer(p_project_id);
3498 FETCH getFinOnlyStrucVer into l_struc_ver_id;
3499 CLOSE getFinOnlyStrucVer;
3500 return l_struc_ver_id;
3501 END IF;
3502 END IF;
3503 END IF;
3504 EXCEPTION
3505 WHEN OTHERS THEN
3506 RETURN TO_NUMBER(NULL);
3507 end GET_FIN_STRUC_VER_ID;
3508
3509 -- Bug 3010538
3510 -- This is a procedure that will accept the project id and the structure type and
3511 -- return the request id, process status code and the structure version id of the
3512 -- concurrent program that is being run or that has errored out for any of the
3513 -- structure version of the type corresponding to structure_type. The structure
3514 -- type parameter is required as when the structure is split, there could be two
3515 -- concurrent programs running one for the FINANCIAL structure and the other for
3516 -- the WORKPLAN structure.
3517 PROCEDURE GET_CONC_REQUEST_DETAILS(
3518 p_project_id IN pa_projects_all.project_id%TYPE
3519 ,p_structure_type IN pa_structure_types.structure_type%TYPE
3520 ,x_request_id OUT NOCOPY pa_proj_elem_ver_structure.conc_request_id%TYPE -- 4537865
3521 ,x_process_code OUT NOCOPY pa_proj_elem_ver_structure.process_code%TYPE -- 4537865
3522 ,x_structure_version_id OUT NOCOPY pa_proj_elem_ver_structure.element_version_id%TYPE -- 4537865
3523 ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
3524 ,x_msg_count OUT NOCOPY NUMBER -- 4537865
3525 ,x_msg_data OUT NOCOPY VARCHAR2 -- 4537865
3526 )
3527 AS
3528 -- Cursor used in this API.
3529
3530 -- This cursor returns the concurrent request id, process code and the
3531 -- structure version for which the concurrent request was launched - This
3532 -- currently could be in process or could have completed with errors.
3533 cursor cur_request_details(c_project_id pa_projects_all.project_id%TYPE,
3534 c_structure_type pa_structure_types.structure_type%TYPE)
3535 IS
3536 select vs.conc_request_id, vs.process_code, vs.element_version_id
3537 from pa_proj_elem_ver_structure vs,
3538 pa_proj_structure_types pst,
3539 pa_structure_types st
3540 where vs.project_id = c_project_id
3541 and vs.process_code in ('WUP','WUE')
3542 and pst.PROJ_ELEMENT_ID = vs.proj_element_id
3543 and pst.structure_type_id = st.structure_type_id
3544 and st.structure_type = c_structure_type;
3545
3546 INVALID_ARG_EXC_WP EXCEPTION;
3547 l_module_name CONSTANT VARCHAR2(30) := 'PA_PROJECT_STRUCTURE_UTILS';
3548 l_msg_count NUMBER := 0;
3549 l_data VARCHAR2(2000);
3550 l_msg_data VARCHAR2(2000);
3551 l_msg_index_out NUMBER;
3552 l_debug_mode VARCHAR2(1);
3553
3554 l_debug_level2 CONSTANT NUMBER := 2;
3555 l_debug_level3 CONSTANT NUMBER := 3;
3556 l_debug_level4 CONSTANT NUMBER := 4;
3557 l_debug_level5 CONSTANT NUMBER := 5;
3558
3559 BEGIN
3560
3561 x_msg_count := 0;
3562 x_return_status := FND_API.G_RET_STS_SUCCESS;
3563 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
3564
3565 IF l_debug_mode = 'Y' THEN
3566 pa_debug.set_curr_function( p_function => 'GET_CONC_REQUEST_DETAILS',
3567 p_debug_mode => l_debug_mode );
3568 END IF;
3569
3570 -- Check for business rules violations
3571 IF l_debug_mode = 'Y' THEN
3572 pa_debug.g_err_stage:= 'Validating input parameters';
3573 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3574
3575 pa_debug.g_err_stage:= 'p_project_id = '|| p_project_id;
3576 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3577
3578 pa_debug.g_err_stage:= 'p_structure_type ='|| p_structure_type;
3579 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3580 END IF;
3581
3582 IF (p_project_id IS NULL) OR
3583 (p_structure_type IS NULL)
3584 THEN
3585 PA_UTILS.ADD_MESSAGE
3586 (p_app_short_name => 'PA',
3587 p_msg_name => 'PA_INV_PARAM_PASSED');
3588 RAISE Invalid_Arg_Exc_WP;
3589 END IF;
3590
3591 open cur_request_details(p_project_id,p_structure_type);
3592 fetch cur_request_details into
3593 x_request_id,
3594 x_process_code,
3595 x_structure_version_id;
3596 close cur_request_details;
3597
3598 IF l_debug_mode = 'Y' THEN
3599 pa_debug.g_err_stage:= 'request id : ' || x_request_id;
3600 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3601
3602 pa_debug.g_err_stage:= 'process code : ' || x_process_code;
3603 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3604
3605 pa_debug.g_err_stage:= 'structure version id : '||x_structure_version_id;
3606 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3607
3608 pa_debug.g_err_stage:= 'Exiting GET_CONC_REQUEST_DETAILS';
3609 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level3);
3610 pa_debug.reset_curr_function;
3611 END IF;
3612 EXCEPTION
3613
3614 WHEN Invalid_Arg_Exc_WP THEN
3615
3616 x_return_status := FND_API.G_RET_STS_ERROR;
3617 l_msg_count := FND_MSG_PUB.count_msg;
3618
3619 -- 4537865 RESET OUT PARAMS
3620 x_request_id := NULL ;
3621 x_process_code := NULL ;
3622 x_structure_version_id := NULL ;
3623
3624 IF cur_request_details%ISOPEN THEN
3625 CLOSE cur_request_details;
3626 END IF;
3627
3628 IF l_msg_count = 1 and x_msg_data IS NULL THEN
3629 PA_INTERFACE_UTILS_PUB.get_messages
3630 (p_encoded => FND_API.G_TRUE
3631 ,p_msg_index => 1
3632 ,p_msg_count => l_msg_count
3633 ,p_msg_data => l_msg_data
3634 ,p_data => l_data
3635 ,p_msg_index_out => l_msg_index_out);
3636 x_msg_data := l_data;
3637 x_msg_count := l_msg_count;
3638 ELSE
3639 x_msg_count := l_msg_count;
3640 END IF;
3641 IF l_debug_mode = 'Y' THEN
3642 pa_debug.reset_curr_function;
3643 END IF;
3644
3645 RETURN;
3646
3647 WHEN others THEN
3648
3649 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3650 x_msg_count := 1;
3651 x_msg_data := SQLERRM;
3652
3653 -- 4537865 RESET OUT PARAMS
3654 x_request_id := NULL ;
3655 x_process_code := NULL ;
3656 x_structure_version_id := NULL ;
3657
3658 IF cur_request_details%ISOPEN THEN
3659 CLOSE cur_request_details;
3660 END IF;
3661
3662 FND_MSG_PUB.add_exc_msg
3663 ( p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS'
3664 ,p_procedure_name => 'GET_CONC_REQUEST_DETAILS'
3665 ,p_error_text => x_msg_data);
3666
3667 IF l_debug_mode = 'Y' THEN
3668 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
3669 pa_debug.write(l_module_name,pa_debug.g_err_stage,l_debug_level5);
3670 pa_debug.reset_curr_function;
3671 END IF;
3672 RAISE;
3673 END GET_CONC_REQUEST_DETAILS;
3674
3675 --Bug 3010538
3676 -- This is a function that will accept the project id and the structure type and
3677 -- return the request id of the concurrent program that is being run or that has
3678 -- errored out for any of the structure version of the type corresponding to
3679 -- structure_type. The structure type parameter is required as when the structure
3680 -- is split, there could be two concurrent programs running one for the FINANCIAL
3681 -- structure and the other for the WORKPLAN structure.
3682 FUNCTION GET_CONC_REQUEST_ID(
3683 p_project_id IN pa_projects_all.project_id%TYPE
3684 ,p_structure_type IN pa_structure_types.structure_type%TYPE
3685 )
3686 return NUMBER IS
3687 -- This cursor returns the concurrent request id, process code and the
3688 -- structure version for which the concurrent request was launched - This
3689 -- currently could be in process or could have completed with errors.
3690 cursor cur_request_details(c_project_id pa_projects_all.project_id%TYPE,
3691 c_structure_type pa_structure_types.structure_type%TYPE)
3692 IS
3693 select vs.conc_request_id
3694 from pa_proj_elem_ver_structure vs,
3695 pa_proj_structure_types pst,
3696 pa_structure_types st
3697 where vs.project_id = c_project_id
3698 and vs.process_code in ('WUP','WUE')
3699 and pst.PROJ_ELEMENT_ID = vs.proj_element_id
3700 and pst.structure_type_id = st.structure_type_id
3701 and st.structure_type = c_structure_type;
3702
3703 l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
3704
3705 BEGIN
3706 open cur_request_details(p_project_id,p_structure_type);
3707 fetch cur_request_details into l_request_id;
3708 close cur_request_details;
3709
3710 return l_request_id;
3711 END GET_CONC_REQUEST_ID;
3712
3713 --Below function is added for bug#3049157
3714
3715 FUNCTION GET_STRUCT_CONC_ID(
3716 p_structure_version_id IN pa_proj_element_versions.parent_structure_version_id%TYPE
3717 ,p_project_id IN pa_projects_all.project_id%TYPE -- Included for Performance Fix : 3968091
3718 )
3719 return NUMBER IS
3720 -- Returns the conc. request id for the structure version id .
3721 cursor cur_request_details(c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE )
3722 IS
3723 select vs.conc_request_id
3724 from pa_proj_elem_ver_structure vs
3725 where vs.element_version_id = c_structure_version_id
3726 and vs.project_id = p_project_id ; -- Included for Performance Fix : 3968091
3727
3728 l_request_id pa_proj_elem_ver_structure.conc_request_id%TYPE;
3729 BEGIN
3730 open cur_request_details(p_structure_version_id);
3731 fetch cur_request_details into l_request_id;
3732 close cur_request_details;
3733 return l_request_id;
3734 END GET_STRUCT_CONC_ID ;
3735
3736
3737 -----------------------------------------------------------------------
3738 -- API name : CHECK_DELIVERABLE_ENABLED
3739 -- Type : Utils API
3740 -- Pre-reqs : None
3741 -- Return Value : Check if deliverable is enabled
3742 --
3743 -- Parameters
3744 -- p_project_id IN NUMBER
3745 --
3746 -- History
3747 --
3748 -- 17-Dec-03 Bhumesh K. -Created
3749 -- This is added for FP_M changes
3750 -----------------------------------------------------------------------
3751
3752 FUNCTION check_Deliverable_enabled (
3753 p_project_id IN NUMBER
3754 ) Return VARCHAR2
3755 IS
3756 CURSOR c1 IS
3757 SELECT 'Y'
3758 FROM pa_proj_elements a,
3759 pa_proj_structure_types b,
3760 pa_structure_types c
3761 WHERE c.structure_type_class_code = 'DELIVERABLE'
3762 AND c.structure_type_id = b.structure_type_id
3763 AND b.proj_element_id = a.proj_element_id
3764 AND a.project_id = p_project_id;
3765
3766 l_dummy VARCHAR2(1);
3767 BEGIN
3768 OPEN c1;
3769 FETCH c1 into l_dummy;
3770 IF c1%NOTFOUND THEN
3771 l_dummy := 'N';
3772 END IF;
3773 CLOSE c1;
3774 return l_dummy;
3775 END check_Deliverable_enabled;
3776
3777 --FPM changes bug 3301192
3778 --Function to get the WP current working version
3779 FUNCTION get_current_working_ver_id( p_project_id NUMBER
3780 )
3781 RETURN NUMBER AS
3782 CURSOR cur_cwv
3783 IS
3784 SELECT ppevs.element_version_id
3785 FROM pa_proj_elements ppe,
3786 pa_proj_structure_types ppst,
3787 pa_structure_types pst,
3788 pa_proj_elem_ver_structure ppevs
3789 WHERE ppe.project_id = p_project_id
3790 AND ppe.proj_element_id = ppst.proj_element_id
3791 AND ppe.project_id = ppevs.project_id
3792 AND ppe.proj_element_id = ppevs.proj_element_id
3793 AND ppevs.status_code = 'STRUCTURE_WORKING'
3794 AND ppevs.CURRENT_WORKING_FLAG = 'Y'
3795 AND ppst.structure_type_id = pst.structure_type_id
3796 AND pst.structure_type_class_code = 'WORKPLAN';
3797
3798 l_structure_version_id NUMBER;
3799 BEGIN
3800
3801 OPEN cur_cwv;
3802 FETCH cur_cwv into l_structure_version_id;
3803 CLOSE cur_cwv;
3804 return l_structure_version_id;
3805
3806 END get_current_working_ver_id;
3807
3808
3809 --------------------------------------------------------------------------
3810 -- API name : Check_Struct_Has_Dep
3811 -- Type : Utils API
3812 -- Pre-reqs : None
3813 -- Return Value : Check the dependency of a structure version ID
3814 --
3815 -- Parameters
3816 -- P_Version_ID IN NUMBER
3817 --
3818 -- History
3819 --
3820 -- 6-Jan-04 Bhumesh K. -Created
3821 -- This is added for FP_M changes. Refer to tracking bug 3305199 for more details
3822
3823 FUNCTION Check_Struct_Has_Dep (
3824 P_Version_ID IN NUMBER
3825 )
3826 RETURN VARCHAR2
3827 IS
3828 l_Exist_Flag varchar2(2) := 'N';
3829
3830 BEGIN
3831 For Rec IN ( select a.ELEMENT_VERSION_ID
3832 from pa_proj_element_versions a
3833 -- where a.project_id = 1027
3834 -- This line is not reqd. Its used only for testing
3835 where a.Parent_Structure_Version_ID = P_Version_ID
3836 and a.Element_Version_ID IN (
3837 Select b.OBJECT_ID_FROM1
3838 from pa_object_relationships b
3839 where b.Relationship_Type = 'S'
3840 and b.Object_Type_From = 'PA_TASKS'
3841 ) )
3842 Loop
3843 Begin
3844 Select 'Y'
3845 Into l_Exist_Flag
3846 from pa_object_relationships b
3847 where Rec.Element_Version_ID IN ( b.Object_ID_From1, b.Object_ID_To1 )
3848 and b.Object_Type_From = 'PA_TASKS'
3849 and b.Object_Type_To = 'PA_TASKS'
3850 and b.Relationship_Type = 'D';
3851 Exception when No_Data_Found Then Null;
3852 End;
3853 If l_Exist_Flag = 'Y' then
3854 Return l_Exist_Flag;
3855 End IF;
3856 End Loop;
3857
3858 Return l_Exist_Flag;
3859
3860 END Check_Struct_Has_Dep;
3861
3862 --this function should return one of the followings:
3863 -- SHARE_FULL
3864 -- SHARE_PARTIAL
3865 -- SPLIT_MAPPING
3866 -- SPLIT_NO_MAPPING
3867 --
3868 -- null can also be returned
3869
3870 FUNCTION get_Structure_sharing_code(
3871 p_project_id IN NUMBER
3872 )
3873 RETURN VARCHAR2
3874 IS
3875 CURSOR c1 IS
3876 select STRUCTURE_SHARING_CODE
3877 from pa_projects_all
3878 where project_id = p_project_id;
3879 l_sharing_code varchar2(30);
3880 BEGIN
3881 OPEN c1;
3882 FETCH c1 into l_sharing_code;
3883 CLOSE c1;
3884 return l_sharing_code;
3885 END get_structure_sharing_code;
3886
3887
3888 FUNCTION check_third_party_sch_flag(
3889 p_project_id IN NUMBER
3890 )
3891 RETURN VARCHAR2
3892 IS
3893 cursor get_third_party_flag IS
3894 Select ppwa.SCHEDULE_THIRD_PARTY_FLAG
3895 from pa_proj_workplan_attr ppwa,
3896 pa_proj_structure_types ppst,
3897 pa_structure_types pst
3898 where p_project_id = ppwa.project_id
3899 and ppwa.proj_element_id = ppst.proj_element_id
3900 and ppst.structure_type_id = pst.structure_type_id
3901 and pst.structure_type = 'WORKPLAN';
3902 l_flag VARCHAR2(1) := 'N';
3903 BEGIN
3904 OPEN get_third_party_flag;
3905 FETCH get_third_party_flag into l_flag;
3906 CLOSE get_third_party_flag;
3907 IF l_flag IS NULL THEN
3908 return 'N';
3909 END IF;
3910 return l_flag;
3911 END check_third_party_sch_flag;
3912
3913 FUNCTION check_dep_on_summary_tk_ok(
3914 p_project_id IN NUMBER
3915 )
3916 RETURN VARCHAR2
3917 IS
3918 cursor get_lowest_tsk_dep_flag IS
3919 Select ppwa.ALLOW_LOWEST_TSK_DEP_FLAG
3920 from pa_proj_workplan_attr ppwa,
3921 pa_proj_structure_types ppst,
3922 pa_structure_types pst
3923 where p_project_id = ppwa.project_id
3924 and ppwa.proj_element_id = ppst.proj_element_id
3925 and ppst.structure_type_id = pst.structure_type_id
3926 and pst.structure_type = 'WORKPLAN';
3927 l_flag VARCHAR2(1) := 'Y';
3928 BEGIN
3929 OPEN get_lowest_tsk_dep_flag;
3930 FETCH get_lowest_tsk_dep_flag into l_flag;
3931 CLOSE get_lowest_tsk_dep_flag;
3932 IF l_flag IS NULL THEN
3933 return 'Y';
3934 END IF;
3935
3936 IF (l_flag = 'Y') THEN
3937 return 'N';
3938 END IF;
3939 return 'Y';
3940 END check_dep_on_summary_tk_ok;
3941
3942 FUNCTION GET_LAST_UPD_WORK_VER_OLD
3943 ( p_structure_id IN NUMBER
3944 ) return NUMBER
3945 IS
3946 CURSOR c1 IS
3947 SELECT MAX(a.last_update_date), b.parent_structure_version_id
3948 FROM pa_proj_element_versions b,
3949 pa_proj_elem_ver_schedule a,
3950 pa_proj_elem_ver_structure c
3951 WHERE a.element_version_id (+)= b.element_version_id
3952 AND a.project_id (+) = b.project_id
3953 AND a.proj_element_id (+) = b.proj_element_id
3954 AND b.parent_structure_version_id = c.element_version_id
3955 AND b.project_id = c.project_id
3956 AND c.status_code <> 'STRUCTURE_PUBLISHED'
3957 AND b.proj_element_id = p_structure_id
3958 GROUP BY b.parent_structure_version_id
3959 ORDER BY MAX(a.last_update_date) desc;
3960 l_date DATE;
3961 l_structure_version_id NUMBER;
3962 BEGIN
3963 OPEN c1;
3964 FETCH c1 into l_date, l_structure_version_id;
3965 CLOSE c1;
3966 return l_structure_version_id;
3967 END GET_LAST_UPD_WORK_VER_OLD;
3968
3969 FUNCTION GET_STRUCT_VER_UPDATE_FLAG(
3970 p_structure_version_id NUMBER
3971 ) return VARCHAR2
3972 IS
3973 CURSOR c1 IS
3974 select project_id from pa_proj_element_versions
3975 where element_version_id = p_structure_version_id;
3976 l_proj_id NUMBER;
3977
3978 CURSOR c2(c_project_id NUMBER) IS
3979 select process_update_wbs_flag from pa_proj_elem_ver_structure
3980 where project_id = c_project_id and element_version_id = p_structure_Version_id;
3981 l_flag VARCHAR2(1);
3982 BEGIN
3983 OPEN c1;
3984 FETCH c1 into l_proj_id;
3985 CLOSE c1;
3986
3987 OPEN c2(l_proj_id);
3988 FETCH c2 into l_flag;
3989 CLOSE c2;
3990
3991 IF l_flag <> 'N' THEN
3992 IF get_process_status_code(l_proj_id, p_structure_version_id) <> 'WUP' THEN
3993 return 'Y';
3994 END IF;
3995 return 'C';
3996 END IF;
3997
3998 RETURN 'N';
3999 END GET_STRUCT_VER_UPDATE_FLAG;
4000 --
4001 FUNCTION Get_Baseline_Struct_Ver(p_project_id IN NUMBER)
4002 RETURN NUMBER
4003 IS
4004 --Bug No 3692992 Performance fix
4005 /* CURSOR get_baseline_wp_ver(cp_proj_id NUMBER) IS
4006 SELECT ppev.element_version_id
4007 FROM pa_proj_elements ppe,
4008 pa_proj_element_versions ppev,
4009 pa_proj_elem_ver_structure ppevs,
4010 pa_proj_structure_types ppst,
4011 pa_structure_types pst
4012 WHERE ppe.proj_element_id = ppev.proj_element_id
4013 AND ppe.project_id = ppev.project_id
4014 AND ppe.project_id = cp_proj_id
4015 AND ppe.object_type = 'PA_STRUCTURES'
4016 AND ppev.element_version_id = ppevs.ELEMENT_VERSION_ID
4017 AND ppevs.CURRENT_FLAG = 'Y'
4018 AND pst.structure_type_id = ppst.structure_type_id
4019 AND ppst.proj_element_id = ppe.proj_element_id
4020 AND pst.structure_type_class_code = 'WORKPLAN';*/
4021
4022 CURSOR get_baseline_wp_ver(cp_proj_id NUMBER) IS
4023 SELECT ppev.element_version_id
4024 FROM pa_proj_elements ppe,
4025 pa_proj_element_versions ppev,
4026 pa_proj_elem_ver_structure ppevs,
4027 pa_proj_structure_types ppst,
4028 pa_structure_types pst
4029 WHERE ppe.proj_element_id = ppev.proj_element_id
4030 AND ppe.project_id = ppev.project_id
4031 AND ppe.project_id = cp_proj_id
4032 AND ppe.object_type = 'PA_STRUCTURES'
4033 AND ppev.element_version_id = ppevs.ELEMENT_VERSION_ID
4034 AND ppev.project_id = ppevs.project_id
4035 AND ((ppevs.CURRENT_FLAG = 'Y') OR ((ppevs.CURRENT_FLAG = 'N') AND (PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(cp_proj_id) = 'N') ) )
4036 --Added for the fix of 7658505
4037 AND pst.structure_type_id = ppst.structure_type_id
4038 AND ppst.proj_element_id = ppe.proj_element_id
4039 AND pst.structure_type_class_code = 'WORKPLAN';
4040
4041 l_baseln_wp_struct_ver_id NUMBER;
4042 --
4043 BEGIN
4044 OPEN get_baseline_wp_ver(p_project_id);
4045 FETCH get_baseline_wp_ver INTO l_baseln_wp_struct_ver_id;
4046 IF get_baseline_wp_ver%NOTFOUND THEN
4047 l_baseln_wp_struct_ver_id := -1;
4048 END IF;
4049 CLOSE get_baseline_wp_ver;
4050
4051 RETURN l_baseln_wp_struct_ver_id;
4052
4053 END GET_BASELINE_STRUCT_VER;
4054 --
4055 --
4056 FUNCTION Get_Sch_Dirty_fl
4057 ( p_project_id IN NUMBER
4058 ,p_structure_version_id IN NUMBER)
4059 RETURN VARCHAR2
4060 IS
4061 --
4062 CURSOR get_sch_dirty_fl(cp_project_id NUMBER,cp_structure_version_id NUMBER) IS
4063 SELECT schedule_dirty_flag
4064 FROM pa_proj_elem_ver_structure
4065 WHERE project_id = cp_project_id
4066 AND element_version_id = cp_structure_version_id;
4067 l_dirty_flag pa_proj_elem_ver_structure.schedule_dirty_flag%TYPE;
4068 --
4069 BEGIN
4070 --
4071 OPEN get_sch_dirty_fl(p_project_id,p_structure_version_id);
4072 FETCH get_sch_dirty_fl INTO l_dirty_flag;
4073 IF get_sch_dirty_fl%NOTFOUND THEN
4074 l_dirty_flag := NULL;
4075 END IF;
4076 CLOSE get_sch_dirty_fl;
4077 --
4078 RETURN l_dirty_flag;
4079 --
4080 END Get_Sch_Dirty_fl;
4081 --
4082 --
4083 -- API name : Check_Subproject_Exists
4084 -- Type : Utils API
4085 -- Pre-reqs : None
4086 -- Return Value : This API check if there is subproject association for the given
4087 -- sturcture version id and project_id. Return Y if there is subproject
4088 -- association or N if there is subproject association
4089 --
4090 -- Parameters
4091 -- p_project_id IN NUMBER
4092 -- p_structure_ver_id IN NUMBER
4093 --
4094 -- History
4095 --
4096 -- 29-Mar-04 SMUKKA -Created
4097 --
4098 FUNCTION Check_Subproject_Exists
4099 (
4100 p_project_id NUMBER,
4101 p_structure_ver_id NUMBER
4102 ,p_link_type VARCHAR2 default 'SHARED' --bug 4541039
4103 )RETURN VARCHAR2
4104 IS
4105 --bug 4087964: changed sql
4106 CURSOR check_subproj_exists IS
4107 SELECT '1'
4108 FROM pa_proj_element_Versions ppev,
4109 pa_object_relationships por
4110 where ppev.parent_structure_version_id = p_structure_ver_id
4111 and ppev.element_version_id = por.object_id_from1
4112 and ppev.project_id = p_project_id
4113 and por.relationship_type IN ('LW', 'LF');
4114
4115
4116 --bug 4541039
4117 CURSOR check_subproj_exists_wp IS
4118 SELECT '1'
4119 FROM pa_proj_element_Versions ppev,
4120 pa_object_relationships por
4121 where ppev.parent_structure_version_id = p_structure_ver_id
4122 and ppev.element_version_id = por.object_id_from1
4123 and ppev.project_id = p_project_id
4124 and por.relationship_type = 'LW';
4125
4126 CURSOR check_subproj_exists_fn IS
4127 SELECT '1'
4128 FROM pa_proj_element_Versions ppev,
4129 pa_object_relationships por
4130 where ppev.parent_structure_version_id = p_structure_ver_id
4131 and ppev.element_version_id = por.object_id_from1
4132 and ppev.project_id = p_project_id
4133 and por.relationship_type = 'LF';
4134 --bug 4541039
4135
4136
4137
4138 /*
4139 CURSOR chk_lnk_task(cp_structure_version_id NUMBER,cp_project_id NUMBER) IS
4140 SELECT '1'
4141 FROM pa_proj_element_versions ppev,
4142 pa_proj_elements ppe
4143 WHERE ppev.element_version_id = cp_structure_version_id
4144 AND ppev.project_id = cp_project_id
4145 AND ppev.project_id = ppe.project_id
4146 AND ppev.proj_element_id = ppe.parent_structure_id
4147 AND ppe.link_task_flag = 'Y';
4148 */
4149 l_dummy VARCHAR2(1);
4150 BEGIN
4151 IF p_link_type = 'SHARED' --bug 4541039
4152 THEN
4153 OPEN check_subproj_exists;
4154 FETCH check_subproj_exists INTO l_dummy;
4155 IF check_subproj_exists%NOTFOUND THEN
4156 CLOSE check_subproj_exists;
4157 RETURN 'N';
4158 ELSE
4159 CLOSE check_subproj_exists;
4160 RETURN 'Y';
4161 END IF;
4162 --bug 4541039
4163 ELSIF p_link_type = 'WORKPLAN'
4164 THEN
4165 OPEN check_subproj_exists_wp;
4166 FETCH check_subproj_exists_wp INTO l_dummy;
4167 IF check_subproj_exists_wp%NOTFOUND THEN
4168 CLOSE check_subproj_exists_wp;
4169 RETURN 'N';
4170 ELSE
4171 CLOSE check_subproj_exists_wp;
4172 RETURN 'Y';
4173 END IF;
4174 ELSIF p_link_type = 'FINANCIAL'
4175 THEN
4176 OPEN check_subproj_exists_fn;
4177 FETCH check_subproj_exists_fn INTO l_dummy;
4178 IF check_subproj_exists_fn%NOTFOUND THEN
4179 CLOSE check_subproj_exists_fn;
4180 RETURN 'N';
4181 ELSE
4182 CLOSE check_subproj_exists_fn;
4183 RETURN 'Y';
4184 END IF;
4185 END IF;
4186 --end bug 4541039
4187
4188 END Check_Subproject_Exists;
4189 --
4190 --
4191 -- API name : Check_Structure_Ver_Exists
4192 -- Type : Utils API
4193 -- Pre-reqs : None
4194 -- Return Value : This API check if there is structure version is valid exists for the
4195 -- given sturcture version id and project_id. Return Y if there is structure version
4196 -- or N if there is no structure version.
4197 --
4198 -- Parameters
4199 -- p_project_id IN NUMBER
4200 -- p_structure_ver_id IN NUMBER
4201 --
4202 -- History
4203 --
4204 -- 16-JUL-04 SMUKKA -Created
4205 --
4206 --
4207 FUNCTION Check_Structure_Ver_Exists
4208 (
4209 p_project_id NUMBER,
4210 p_structure_ver_id NUMBER
4211 )RETURN VARCHAR2
4212 IS
4213 CURSOR chk_structure_ver(cp_project_id NUMBER,cp_struct_ver_id NUMBER)
4214 IS
4215 SELECT 'Y'
4216 FROM pa_proj_element_versions
4217 WHERE project_id = cp_project_id
4218 AND element_version_id = cp_struct_ver_id
4219 AND object_type = 'PA_STRUCTURES';
4220 --
4221 l_dummy VARCHAR2(1) := 'N';
4222 --
4223 BEGIN
4224 --
4225 open chk_structure_ver(p_project_id, p_structure_ver_id);
4226 FETCH chk_structure_ver into l_dummy;
4227 IF chk_structure_ver%FOUND THEN
4228 l_dummy := 'Y';
4229 END IF;
4230 close chk_structure_ver;
4231 --
4232 Return l_dummy;
4233 --
4234 END Check_Structure_Ver_Exists;
4235 --
4236 --
4237 Function Check_Project_exists(p_project_id IN NUMBER)
4238 RETURN VARCHAR2
4239 IS
4240 CURSOR chk_project(cp_project_id NUMBER)
4241 IS
4242 SELECT 'Y'
4243 FROM pa_projects_all
4244 WHERE project_id = cp_project_id;
4245 --
4246 l_dummy VARCHAR2(1) := 'N';
4247 --
4248 BEGIN
4249 --
4250 open chk_project(p_project_id);
4251 FETCH chk_project into l_dummy;
4252 IF chk_project%FOUND THEN
4253 l_dummy := 'Y';
4254 END IF;
4255 close chk_project;
4256 --
4257 Return l_dummy;
4258 --
4259 END Check_Project_exists;
4260 --
4261 --
4262 -- Begin fix for Bug # 4373055.
4263
4264 PROCEDURE GET_PROCESS_STATUS_MSG(
4265 p_project_id IN pa_projects_all.project_id%TYPE
4266 , p_structure_type IN pa_structure_types.structure_type%TYPE := NULL
4267 , p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE := NULL
4268 , p_context IN VARCHAR2 := NULL
4269 , x_message_name OUT NOCOPY VARCHAR2 -- 4537865
4270 , x_message_type OUT NOCOPY VARCHAR2 -- 4537865
4271 , x_structure_version_id OUT NOCOPY NUMBER -- 4537865
4272 , x_conc_request_id OUT NOCOPY NUMBER) -- 4537865
4273 IS
4274
4275 /*
4276 This API fetches the message name of the message to be displayed based on the process status code
4277 of the workplan structure. These process status codes signify either that a process is in progress
4278 on the structure version / structure type or a process has failed with errors on the structure version /
4279 structure type.
4280 */
4281
4282 cursor cur_proc_ver(c_project_id pa_projects_all.project_id%TYPE
4283 , c_structure_type pa_structure_types.structure_type%TYPE) is
4284 select vs.process_code, vs.element_version_id, vs.conc_request_id
4285 from pa_proj_elem_ver_structure vs
4286 , pa_proj_structure_types pst
4287 , pa_structure_types st
4288 where vs.project_id = c_project_id
4289 and pst.PROJ_ELEMENT_ID = vs.proj_element_id
4290 and pst.structure_type_id = st.structure_type_id
4291 and st.structure_type = c_structure_type
4292 and vs.process_code is not null; -- Fix for Bug # 4373055.
4293
4294 cursor cur_process_code(c_project_id pa_projects_all.project_id%TYPE
4295 , c_structure_version_id pa_proj_element_versions.element_version_id%TYPE) is
4296 select process_code, element_version_id, conc_request_id
4297 from pa_proj_elem_ver_structure
4298 where project_id = c_project_id
4299 and element_version_id = c_structure_version_id;
4300
4301 l_process_code FND_LOOKUPS.lookup_code%TYPE := null;
4302 l_structure_version_id NUMBER:= null;
4303 l_conc_request_id NUMBER := null;
4304
4305 cursor cur_message(c_process_code FND_LOOKUPS.lookup_code%TYPE) is
4306 select meaning message_name, 'PROCESS' message_type
4307 from pa_lookups
4308 where lookup_code = c_process_code
4309 and c_process_code like '%P'
4310 union all
4311 select meaning message_name, 'ERROR_EDITABLE' message_type
4312 from pa_lookups
4313 where lookup_code = c_process_code
4314 and c_process_code like '%E'
4315 and c_process_code <> 'PUE'
4316 union all
4317 select meaning message_name, 'ERROR_NOT_EDITABLE' message_type
4318 from pa_lookups
4319 where lookup_code = c_process_code
4320 and c_process_code = 'PUE'
4321 ;
4322
4323 l_message_name VARCHAR2(30) := null;
4324 l_message_type VARCHAR2(30) := null;
4325
4326 BEGIN
4327
4328 l_process_code := null;
4329 l_message_name := null;
4330 l_message_type := null;
4331 x_structure_version_id := null;
4332 x_conc_request_id := null;
4333
4334 if (p_structure_type is not null) then
4335
4336 open cur_proc_ver(p_project_id,p_structure_type);
4337 fetch cur_proc_ver into l_process_code, l_structure_version_id
4338 , l_conc_request_id;
4339 close cur_proc_ver;
4340
4341 elsif (p_structure_version_id is not null) then
4342
4343 open cur_process_code(p_project_id, p_structure_version_id);
4344 fetch cur_process_code into l_process_code, l_structure_version_id
4345 , l_conc_request_id;
4346 close cur_process_code;
4347
4348 else
4349 l_process_code := null;
4350
4351 end if;
4352
4353 if l_process_code is not NULL then
4354
4355 -- Begin fix for Bug # 4475657.
4356
4357 -- The architecture team requires the meaning of lookup types to be translatable text.
4358 -- Hence, we have removed the message name from the meaning column of the lookup type and matched
4359 -- a lookup code to its corresponding message using the PL/SQL logic below.
4360
4361 if (l_process_code = 'CPI') then
4362
4363 l_message_name := 'PA_PS_PUBWBS_PRC_CHLDPUB';
4364
4365 l_message_type := 'INFORMATION';
4366
4367 elsif (l_process_code = 'APP') then
4368
4369 l_message_name := 'PA_PS_APLPRG_PRC_INPROC';
4370
4371 l_message_type := 'PROCESS';
4372
4373 elsif (l_process_code = 'APE') then
4374
4375 l_message_name := 'PA_PS_APLPRG_PRC_ERR';
4376
4377 l_message_type := 'ERROR_EDITABLE';
4378
4379 elsif (l_process_code = 'APS') then
4380
4381 l_message_name := null;
4382
4383 l_message_type := null;
4384
4385 elsif (l_process_code = 'PUP') then
4386
4387 l_message_name := 'PA_PS_PUBWBS_PRC_INPROC';
4388
4389 l_message_type := 'PROCESS';
4390
4391 elsif (l_process_code = 'PUE') then
4392
4393 l_message_name := 'PA_PS_PUBWBS_PRC_ERR';
4394
4395 l_message_type := 'ERROR_NOT_EDITABLE'; -- Bug # 4577934. -- 'ERROR_EDITABLE'; -- Bug # 4562309.
4396
4397 elsif (l_process_code = 'PUS') then
4398
4399 l_message_name := null;
4400
4401 l_message_type := null;
4402
4403 elsif (l_process_code = 'WUP') then
4404
4405 l_message_name := 'PA_PS_UDTWBS_PRC_INPROC';
4406
4407 l_message_type := 'PROCESS';
4408
4409 elsif (l_process_code = 'WUE') then
4410
4411 l_message_name := 'PA_PS_UDTWBS_PRC_ERR';
4412
4413 l_message_type := 'ERROR_EDITABLE';
4414
4415 elsif (l_process_code = 'WUS') then
4416
4417 l_message_name := null;
4418
4419 l_message_type := null;
4420
4421 /* Added For bug#5659575 */
4422 elsif (l_process_code = 'PRE' and p_context is NULL) then
4423
4424 l_message_name := 'PA_PS_UPDPERF_PRC_ERR';
4425
4426 l_message_type := 'ERROR_EDITABLE';
4427
4428 end if;
4429
4430 -- open cur_message(l_process_code);
4431 -- fetch cur_message into l_message_name, l_message_type;
4432 -- close cur_message;
4433
4434 -- End fix for Bug # 4475657.
4435
4436 end if;
4437
4438 if ((p_context = 'UPDATE_AMG') and (l_message_name is not null)) then -- Fix for Bug # 4373055.
4439
4440 l_message_name := l_message_name||'_AMG';
4441
4442 end if;
4443
4444 x_message_name := l_message_name;
4445 x_message_type := l_message_type;
4446 x_structure_version_id := l_structure_version_id;
4447 x_conc_request_id := l_conc_request_id;
4448 -- 4537865
4449 EXCEPTION
4450 WHEN OTHERS THEN
4451 x_message_name := NULL ;
4452 x_message_type := NULL ;
4453 x_structure_version_id := NULL ;
4454 x_conc_request_id := NULL ;
4455
4456 Fnd_Msg_Pub.add_exc_msg
4457 ( p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS'
4458 , p_procedure_name => 'GET_PROCESS_STATUS_MSG'
4459 , p_error_text => SUBSTRB(SQLERRM,1,240));
4460
4461 RAISE ;
4462
4463 END GET_PROCESS_STATUS_MSG;
4464
4465
4466 PROCEDURE SET_PROCESS_CODE_IN_PROC(
4467 p_project_id IN NUMBER
4468 , p_structure_version_id IN NUMBER
4469 , p_calling_context IN VARCHAR2
4470 , p_conc_request_id IN NUMBER
4471 , x_return_status OUT NOCOPY VARCHAR2) -- 4537865
4472 IS
4473
4474 l_return_status VARCHAR2(30) := NULL;
4475
4476 BEGIN
4477
4478 l_return_status := FND_API.G_RET_STS_SUCCESS;
4479
4480 update pa_proj_elem_ver_structure
4481 set process_code = decode(p_calling_context, 'APPLY_PROGRESS', 'APP'
4482 , 'CONC_PUBLISH', 'PUP'
4483 , 'CONC_UPDATE', 'WUP'
4484 , 'ASGMT_PLAN_CHANGE', 'WUP'
4485 ,null) ---4492493
4486 , conc_request_id = p_conc_request_id
4487 where element_version_id = p_structure_version_id
4488 and project_id = p_project_id;
4489
4490 x_return_status := l_return_status;
4491
4492 commit;
4493
4494 EXCEPTION
4495
4496 when others then
4497
4498 -- Changed l_return_status to x_return_status : 4537865
4499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4500
4501 raise;
4502
4503 END SET_PROCESS_CODE_IN_PROC;
4504
4505
4506 PROCEDURE SET_PROCESS_CODE_ERR(
4507 p_project_id IN NUMBER
4508 , p_structure_version_id IN NUMBER
4509 , p_calling_context IN VARCHAR2
4510 , p_conc_request_id IN NUMBER
4511 , x_return_status OUT NOCOPY VARCHAR2) -- 4537865
4512 IS
4513
4514 l_return_status VARCHAR2(30) := NULL;
4515
4516 BEGIN
4517
4518 l_return_status := FND_API.G_RET_STS_SUCCESS;
4519
4520 update pa_proj_elem_ver_structure
4521 set process_code = decode(p_calling_context, 'APPLY_PROGRESS', 'APE'
4522 , 'CONC_PUBLISH', 'PUE'
4523 , 'CONC_UPDATE', 'WUE', null)
4524 , conc_request_id = p_conc_request_id
4525 where element_version_id = p_structure_version_id
4526 and project_id = p_project_id;
4527
4528 x_return_status := l_return_status;
4529
4530 commit;
4531
4532 EXCEPTION
4533
4534 when others then
4535 -- 4537865 Changed l_return_status to x_return_status
4536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4537
4538 raise;
4539
4540 END SET_PROCESS_CODE_ERR;
4541
4542 -- End fix for Bug # 4373055.
4543
4544
4545 -- Begin fix for Bug # 5659575.
4546
4547 PROCEDURE SET_PROCESS_CODE(
4548 p_project_id IN NUMBER
4549 , p_structure_version_id IN NUMBER
4550 , p_process_code IN VARCHAR2
4551 , p_conc_request_id IN NUMBER
4552 , x_return_status OUT NOCOPY VARCHAR2)
4553 IS
4554
4555 l_return_status VARCHAR2(30) := NULL;
4556
4557 BEGIN
4558
4559 l_return_status := FND_API.G_RET_STS_SUCCESS;
4560 if p_process_code = 'PRE' then
4561 update pa_proj_elem_ver_structure
4562 set process_code = p_process_code
4563 , conc_request_id = p_conc_request_id
4564 where element_version_id = p_structure_version_id
4565 and project_id = p_project_id
4566 and process_code is null;
4567 else
4568 update pa_proj_elem_ver_structure
4569 set process_code = NULL
4570 where project_id = p_project_id
4571 and process_code = 'PRE';
4572 end if;
4573 x_return_status := l_return_status;
4574
4575 EXCEPTION
4576
4577 when others then
4578
4579 -- Changed l_return_status to x_return_status for NOCOPY
4580 x_return_status := FND_API.G_RET_STS_ERROR;
4581
4582 raise;
4583
4584 END SET_PROCESS_CODE;
4585
4586 -- End fix for Bug # 5659575.
4587
4588 -- Begin fix for Bug # 4502325.
4589
4590 procedure get_structure_msg(p_project_id IN NUMBER
4591 , p_structure_type IN VARCHAR2
4592 , p_structure_version_id IN NUMBER
4593 , p_context IN VARCHAR2 := NULL
4594 , x_message_name OUT NOCOPY VARCHAR2 -- 4537865
4595 , x_message_type OUT NOCOPY VARCHAR2 -- 4537865
4596 , x_structure_version_id OUT NOCOPY NUMBER -- 4537865
4597 , x_conc_request_id OUT NOCOPY NUMBER) -- 4537865
4598 is
4599
4600 cursor cur_str_sharing_code(c_project_id NUMBER) is
4601 select ppa.structure_sharing_code
4602 from pa_projects_all ppa
4603 where ppa.project_id = c_project_id;
4604
4605 l_str_sharing_code VARCHAR2(30) := null;
4606
4607 cursor cur_proc_upd_flag(c_project_id NUMBER, c_structure_version_id VARCHAR2) is
4608 select ppevs.process_update_wbs_flag, ppevs.process_code
4609 from pa_proj_elem_ver_structure ppevs, pa_proj_structure_types ppst, pa_structure_types pst
4610 where ppevs.proj_element_id = ppst.proj_element_id
4611 and ppst.structure_type_id = pst.structure_type_id
4612 and ppevs.project_id = c_project_id
4613 and ppevs.element_version_id = c_structure_version_id;
4614
4615 l_proc_upd_flag VARCHAR2(1) := null;
4616 l_process_code VARCHAR2(30) := null;
4617 l_message_name VARCHAR2(30) := null;
4618 l_message_type VARCHAR2(30) := null;
4619 l_structure_version_id NUMBER := null;
4620 l_conc_request_id NUMBER := null;
4621
4622 begin
4623
4624 l_proc_upd_flag := null;
4625 l_process_code := null;
4626 l_message_name := null;
4627 l_message_type := null;
4628 l_structure_version_id := null;
4629 l_conc_request_id := null;
4630
4631
4632 if ((p_project_id is null)
4633 or (p_structure_type is null)
4634 or (p_structure_version_id is null)) then
4635
4636 l_message_name := null;
4637 l_message_type := null;
4638 l_structure_version_id := null;
4639 l_conc_request_id := null;
4640
4641 else
4642
4643 open cur_str_sharing_code(p_project_id);
4644 fetch cur_str_sharing_code into l_str_sharing_code;
4645 close cur_str_sharing_code;
4646
4647 open cur_proc_upd_flag(p_project_id, p_structure_version_id);
4648 fetch cur_proc_upd_flag into l_proc_upd_flag, l_process_code;
4649 close cur_proc_upd_flag;
4650
4651 if (l_process_code is not null) then
4652
4653 PA_PROJECT_STRUCTURE_UTILS.GET_PROCESS_STATUS_MSG
4654 (p_project_id => p_project_id
4655 , p_structure_type => NULL
4656 , p_structure_version_id => p_structure_version_id
4657 , p_context => p_context
4658 , x_message_name => l_message_name
4659 , x_message_type => l_message_type
4660 , x_structure_version_id => l_structure_version_id
4661 , x_conc_request_id => l_conc_request_id);
4662
4663 elsif (l_proc_upd_flag = 'Y') then
4664
4665 if (p_structure_type = 'WORKPLAN') then
4666
4667 if l_str_sharing_code in ('SHARE_FULL' , 'SHARE_PARTIAL') then
4668
4669 l_message_name := 'PA_PS_VD_SH_UPD_WBS_PRC_REQ';
4670 else
4671
4672 l_message_name := 'PA_PS_VD_SP_UPD_WBS_PRC_REQ';
4673
4674 end if;
4675
4676 elsif (p_structure_type = 'FINANCIAL') then
4677
4678 if l_str_sharing_code in ('SHARE_FULL' , 'SHARE_PARTIAL') then
4679
4680 l_message_name := 'PA_PS_VD_SH_UPD_WBS_PRC_REQ';
4681
4682 else
4683
4684 l_message_name := 'PA_PS_VD_SP_UPD_FBS_PRC_REQ';
4685
4686 end if;
4687
4688 end if;
4689
4690 l_message_type := 'INFORMATION';
4691 l_structure_version_id := p_structure_version_id;
4692 l_conc_request_id := null;
4693
4694 else
4695
4696 l_message_name := null;
4697 l_message_type := null;
4698 l_structure_version_id := null;
4699 l_conc_request_id := null;
4700
4701 end if;
4702
4703 end if;
4704
4705 x_message_name := l_message_name;
4706 x_message_type := l_message_type;
4707 x_structure_version_id := l_structure_version_id;
4708 x_conc_request_id := l_conc_request_id;
4709 -- 4537865
4710 EXCEPTION
4711 WHEN OTHERS THEN
4712 x_message_name := NULL ;
4713 x_message_type := NULL ;
4714 x_structure_version_id := NULL ;
4715 x_conc_request_id := NULL ;
4716 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
4717 p_procedure_name => 'get_structure_msg',
4718 p_error_text => SUBSTRB(SQLERRM,1,240));
4719 RAISE;
4720 end get_structure_msg;
4721
4722 -- End fix for Bug # 4502325.
4723
4724 -- Begin Bug # 4582750.
4725
4726 procedure lock_unlock_wp_str_autonomous
4727 (p_project_id IN NUMBER
4728 ,p_structure_version_id IN NUMBER
4729 ,p_lock_status_code IN VARCHAR2 := 'LOCKED'
4730 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
4731 ,x_return_status OUT NOCOPY VARCHAR2
4732 ,x_msg_count OUT NOCOPY NUMBER
4733 ,x_msg_data OUT NOCOPY VARCHAR2)
4734 is
4735
4736 PRAGMA AUTONOMOUS_TRANSACTION;
4737
4738 l_module_name CONSTANT VARCHAR2(61) := 'PA_PROJECT_STRUCTURE_UTILS.LOCK_UNLOCK_WP_STR_AUTONOMOUS';
4739 l_msg_count NUMBER := 0;
4740 l_msg_data VARCHAR2(2000);
4741 l_debug_mode VARCHAR2(1);
4742 l_wp_str VARCHAR2(30);
4743
4744 cursor l_cur_ppevs(c_project_id NUMBER, c_str_ver_id NUMBER) is
4745 select ppevs.name, ppevs.pev_structure_id, ppevs.record_version_number
4746 from pa_proj_elem_ver_structure ppevs
4747 where ppevs.project_id = c_project_id
4748 and ppevs.element_version_id = c_str_ver_id;
4749
4750 l_rec_ppevs l_cur_ppevs%rowtype;
4751
4752 begin
4753
4754 savepoint lock_unlock_wp_str;
4755
4756 x_msg_count := 0;
4757 x_return_status := FND_API.G_RET_STS_SUCCESS;
4758 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
4759
4760 if l_debug_mode = 'Y' THEN
4761 pa_debug.set_curr_function( p_function => 'lock_unlock_wp_str_autonomous');
4762 pa_debug.write(l_module_name,'p_project_id = '||p_project_id,3);
4763 pa_debug.write(l_module_name,'p_structure_version_id = '||p_structure_version_id,3);
4764 pa_debug.write(l_module_name,'p_lock_status_code = '||p_lock_status_code,3);
4765 end if;
4766
4767 l_wp_str := PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(p_structure_version_id, 'WORKPLAN');
4768
4769 if l_debug_mode = 'Y' THEN
4770 pa_debug.write(l_module_name,'l_wp_str = '||l_wp_str,3);
4771 end if;
4772
4773 if (l_wp_str = 'Y') then
4774
4775 open l_cur_ppevs(p_project_id, p_structure_version_id);
4776 fetch l_cur_ppevs into l_rec_ppevs;
4777 close l_cur_ppevs;
4778
4779 if l_debug_mode = 'Y' THEN
4780 pa_debug.write(l_module_name,'Before Calling pa_project_structure_pub1.update_structure_version_attr',3);
4781 pa_debug.write(l_module_name,'l_rec_ppevs.pev_structure_id = '||l_rec_ppevs.pev_structure_id,3);
4782 pa_debug.write(l_module_name,'l_rec_ppevs.name= '||l_rec_ppevs.name,3);
4783 pa_debug.write(l_module_name,'l_rec_ppevs.record_version_number= '||l_rec_ppevs.record_version_number,3);
4784 end if;
4785
4786 pa_project_structure_pub1.update_structure_version_attr
4787 (p_pev_structure_id => l_rec_ppevs.pev_structure_id
4788 , p_locked_status_code => p_lock_status_code
4789 , p_structure_version_name => l_rec_ppevs.name
4790 , p_init_msg_list => FND_API.G_FALSE -- Added for bug 5130360
4791 , p_record_version_number => l_rec_ppevs.record_version_number
4792 , p_calling_module => p_calling_module
4793 , x_return_status => x_return_status
4794 , x_msg_count => x_msg_count
4795 , x_msg_data => x_msg_data);
4796
4797 if l_debug_mode = 'Y' THEN
4798 pa_debug.write(l_module_name,'After calling pa_project_structure_pub1.update_structure_version_attr x_return_status='||x_return_status,3);
4799 end if;
4800
4801
4802 end if;
4803
4804 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
4805 commit;
4806 else
4807 raise FND_API.G_EXC_ERROR;
4808 end if;
4809
4810 exception
4811
4812 when FND_API.G_EXC_ERROR then
4813 /*rollback to lock_unlock_wp_str; bug#6414944*/
4814 rollback; /*bug# 6414944*/
4815
4816 when others then
4817 /* rollback to lock_unlock_wp_str; bug#6414944*/
4818 rollback; /*Bug# 6414944*/
4819 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
4820 p_procedure_name => 'lock_unlock_wp_str_autonomous',
4821 p_error_text => SUBSTRB(SQLERRM,1,240));
4822
4823 end lock_unlock_wp_str_autonomous;
4824
4825 procedure lock_unlock_wp_str
4826 (p_project_id IN NUMBER
4827 ,p_structure_version_id IN NUMBER
4828 ,p_lock_status_code IN VARCHAR2 := 'LOCKED'
4829 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
4830 ,x_return_status OUT NOCOPY VARCHAR2
4831 ,x_msg_count OUT NOCOPY NUMBER
4832 ,x_msg_data OUT NOCOPY VARCHAR2)
4833 is
4834
4835 l_module_name CONSTANT VARCHAR2(61) := 'PA_PROJECT_STRUCTURE_UTILS.LOCK_UNLOCK_WP_STR';
4836 l_msg_count NUMBER := 0;
4837 l_msg_data VARCHAR2(2000);
4838 l_debug_mode VARCHAR2(1);
4839 l_wp_str VARCHAR2(30);
4840
4841 cursor l_cur_ppevs(c_project_id NUMBER, c_str_ver_id NUMBER) is
4842 select ppevs.name, ppevs.pev_structure_id, ppevs.record_version_number
4843 from pa_proj_elem_ver_structure ppevs
4844 where ppevs.project_id = c_project_id
4845 and ppevs.element_version_id = c_str_ver_id;
4846
4847 l_rec_ppevs l_cur_ppevs%rowtype;
4848
4849 begin
4850
4851 savepoint lock_unlock_wp_str2;
4852
4853 x_msg_count := 0;
4854 x_return_status := FND_API.G_RET_STS_SUCCESS;
4855 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
4856
4857 if l_debug_mode = 'Y' THEN
4858 pa_debug.set_curr_function( p_function => 'lock_unlock_wp_str');
4859 pa_debug.write(l_module_name,'p_project_id = '||p_project_id,3);
4860 pa_debug.write(l_module_name,'p_structure_version_id = '||p_structure_version_id,3);
4861 pa_debug.write(l_module_name,'p_lock_status_code = '||p_lock_status_code,3);
4862 end if;
4863
4864 l_wp_str := PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(p_structure_version_id, 'WORKPLAN');
4865
4866 if l_debug_mode = 'Y' THEN
4867 pa_debug.write(l_module_name,'l_wp_str = '||l_wp_str,3);
4868 end if;
4869
4870 if (l_wp_str = 'Y') then
4871
4872 open l_cur_ppevs(p_project_id, p_structure_version_id);
4873 fetch l_cur_ppevs into l_rec_ppevs;
4874 close l_cur_ppevs;
4875
4876 if l_debug_mode = 'Y' THEN
4877 pa_debug.write(l_module_name,'Before Calling pa_project_structure_pub1.update_structure_version_attr',3);
4878 pa_debug.write(l_module_name,'l_rec_ppevs.pev_structure_id = '||l_rec_ppevs.pev_structure_id,3);
4879 pa_debug.write(l_module_name,'l_rec_ppevs.name= '||l_rec_ppevs.name,3);
4880 pa_debug.write(l_module_name,'l_rec_ppevs.record_version_number= '||l_rec_ppevs.record_version_number,3);
4881 end if;
4882
4883 pa_project_structure_pub1.update_structure_version_attr
4884 (p_pev_structure_id => l_rec_ppevs.pev_structure_id
4885 , p_locked_status_code => p_lock_status_code
4886 , p_structure_version_name => l_rec_ppevs.name
4887 , p_init_msg_list => FND_API.G_FALSE -- Added for bug 5130360
4888 , p_record_version_number => l_rec_ppevs.record_version_number
4889 , p_calling_module => p_calling_module
4890 , x_return_status => x_return_status
4891 , x_msg_count => x_msg_count
4892 , x_msg_data => x_msg_data);
4893
4894 if l_debug_mode = 'Y' THEN
4895 pa_debug.write(l_module_name,'After calling pa_project_structure_pub1.update_structure_version_attr x_return_status='||x_return_status,3);
4896 end if;
4897
4898
4899 end if;
4900
4901 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
4902 --commit;
4903 null;
4904 else
4905 raise FND_API.G_EXC_ERROR;
4906 end if;
4907
4908 exception
4909
4910 when FND_API.G_EXC_ERROR then
4911 rollback to lock_unlock_wp_str2;
4912
4913 when others then
4914 rollback to lock_unlock_wp_str2;
4915 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
4916 p_procedure_name => 'lock_unlock_wp_str',
4917 p_error_text => SUBSTRB(SQLERRM,1,240));
4918
4919 end lock_unlock_wp_str;
4920
4921 -- End Bug # 4582750.
4922
4923 --bug 4597323
4924 FUNCTION check_program_flag_enable(
4925 p_project_id NUMBER
4926 ) RETURN VARCHAR2 IS
4927
4928 CURSOR cur_proj
4929 IS
4930 SELECT sys_program_flag
4931 FROM pa_projects_all
4932 WHERE project_id=p_project_id
4933 ;
4934 l_return_value VARCHAR2(1);
4935 BEGIN
4936
4937 IF p_project_id IS NOT NULL
4938 THEN
4939 OPEN cur_proj;
4940 FETCH cur_proj INTO l_return_value;
4941 CLOSE cur_proj;
4942 END IF;
4943
4944 return l_return_value;
4945
4946 END check_program_flag_enable;
4947 --end bug 4597323
4948
4949 -- API name : check_del_pub_struc_ver_ok
4950 -- Tracking Bug : 4925192
4951 -- Type : Utils API
4952 -- Pre-reqs : None
4953 -- Return Value : Check if ok to delete Published structure
4954 -- version
4955 -- Return Y or N
4956 --
4957 -- Parameters
4958 -- p_structure_version_id IN NUMBER
4959 --
4960 -- History
4961 --
4962 -- 20-OCT-06 Ram Namburi -Created
4963 --
4964 -- Purpose:
4965 -- This API will determine whether a published structure version can be deleted or not.
4966 --
4967 -- Business Rules:
4968 --
4969 -- The published version cannot be deleted if
4970 --
4971 -- 1. It is the current baseline version - because the metrics are calculated using
4972 -- the baselined values from the current baselined workplan structure version
4973 -- 2. It is the latest published version - because the PJI module uses the
4974 -- financial plan to rollup data on the latest published workplan structure version
4975 -- 3. It is a part of a program - because it is technically challenging to handle
4976 -- the deletion of published workplan structure versions that belong to the
4977 -- program itself or an intermediate sub-project in a program hierarchy
4978
4979
4980
4981 FUNCTION check_del_pub_struc_ver_ok
4982 ( p_structure_version_id IN NUMBER
4983 ,p_project_id IN NUMBER
4984 ) return VARCHAR2
4985 IS
4986 l_object_relationship_id NUMBER;
4987 l_current_flag pa_proj_elem_ver_structure.current_flag%TYPE;
4988 l_LATEST_EFF_PUBLISHED_FLAG pa_proj_elem_ver_structure.LATEST_EFF_PUBLISHED_FLAG%TYPE;
4989 l_version_name pa_proj_elem_ver_structure.name%TYPE;
4990 l_version_number pa_proj_elem_ver_structure.version_number%TYPE;
4991
4992 -- Checks whether the published workplan structure version is part of a program
4993
4994 CURSOR link_exists_cur IS
4995 SELECT 1 FROM DUAL WHERE EXISTS (
4996 SELECT por.object_relationship_id
4997 FROM pa_object_relationships por,
4998 pa_proj_element_versions ppev
4999 WHERE por.object_id_from1 = ppev.element_version_id
5000 AND por.relationship_type in ('LW', 'LF')
5001 AND ppev.parent_structure_version_id = p_structure_version_id
5002 UNION ALL
5003 SELECT por. object_relationship_id
5004 FROM pa_object_relationships por,
5005 pa_proj_element_versions ppev
5006 WHERE por.object_id_to1 = ppev.element_version_id
5007 AND por.relationship_type in ('LW', 'LF')
5008 AND ppev.parent_structure_version_id = p_structure_version_id);
5009
5010 -- Checks whether the published workplan structure version is CURRENT or Latest
5011 -- Published version.
5012
5013 CURSOR curr_lat_pub_ver_cur IS
5014 SELECT ppevs.current_flag,
5015 ppevs.LATEST_EFF_PUBLISHED_FLAG,
5016 ppevs.name,
5017 ppevs.version_number
5018 FROM pa_proj_elem_ver_structure ppevs
5019 WHERE ppevs.element_version_id = p_structure_version_id
5020 AND ppevs.project_id = p_project_id;
5021
5022 BEGIN
5023
5024 OPEN curr_lat_pub_ver_cur;
5025 FETCH curr_lat_pub_ver_cur into l_current_flag,l_LATEST_EFF_PUBLISHED_FLAG,l_version_name,l_version_number;
5026 CLOSE curr_lat_pub_ver_cur;
5027
5028 IF l_current_flag = 'Y' THEN
5029 FND_MESSAGE.set_name('PA','PA_PS_DEL_CURR_BASE_VER');
5030 -- Commenting out the following as the messages are changed in the FD.
5031 -- Now as per the latest FD the above message doesnt need any tokens.
5032 -- FND_MESSAGE.set_token('VER_NAME',l_version_name);
5033 -- FND_MESSAGE.set_token('VER_NUMBER',l_version_number);
5034 FND_MSG_PUB.add;
5035
5036 return 'N';
5037 END IF;
5038
5039 IF l_LATEST_EFF_PUBLISHED_FLAG = 'Y' THEN
5040 FND_MESSAGE.set_name('PA','PA_PS_DEL_LATEST_PUB_VER');
5041 -- Commenting out the following as the messages are changed in the FD.
5042 -- Now as per the latest FD the above message doesnt need any tokens.
5043 -- FND_MESSAGE.set_token('VER_NAME',l_version_name);
5044 -- FND_MESSAGE.set_token('VER_NUMBER',l_version_number);
5045 FND_MSG_PUB.add;
5046
5047 return 'N';
5048 END IF;
5049
5050 OPEN link_exists_cur;
5051 FETCH link_exists_cur into l_object_relationship_id;
5052 IF link_exists_cur%FOUND THEN
5053 FND_MESSAGE.set_name('PA','PA_PS_DEL_PART_OF_PROG');
5054 -- Commenting out the following as the messages are changed in the FD.
5055 -- Now as per the latest FD the above message doesnt need any tokens.
5056 -- FND_MESSAGE.set_token('VER_NAME',l_version_name);
5057 -- FND_MESSAGE.set_token('VER_NUMBER',l_version_number);
5058 FND_MSG_PUB.add;
5059 CLOSE link_exists_cur;
5060 return 'N';
5061 END IF;
5062 CLOSE link_exists_cur;
5063
5064 RETURN 'Y';
5065
5066 END check_del_pub_struc_ver_ok;
5067
5068
5069 -- bug 5183704
5070 -- API name : check_pending_link_changes
5071 -- Type : Utils API
5072 -- Pre-reqs : None
5073 -- Return Value : This API returns "Y" if pending link changes exist for a given project as per
5074 -- log type PRG_CHANGE in the pa_pji_proj_events_log, pji_pa_proj_events_log tables.
5075 -- It's created to use from Projects Self Services (sql from vijay r).
5076 --
5077 -- Parameters
5078 -- p_project_id IN NUMBER
5079 --
5080 -- History
5081 --
5082 -- 09-MAY-06 sliburd -Created
5083 --
5084 function CHECK_PENDING_LINK_CHANGES(p_project_id NUMBER)
5085 return VARCHAR2
5086 IS
5087 CURSOR c1 IS
5088 select 1
5089 from dual
5090 where exists (select log.event_id
5091 from pji_pa_proj_events_log log,pa_proj_element_versions ver
5092 where log.event_type='PRG_CHANGE'
5093 and log.event_object =to_char(ver.prg_group)
5094 and ver.project_id=p_project_id
5095 union all
5096 select log.event_id
5097 from pa_pji_proj_events_log log,pa_proj_element_versions ver
5098 where log.event_type='PRG_CHANGE'
5099 and log.event_object =to_char(ver.prg_group)
5100 and ver.project_id=p_project_id);
5101
5102 l_dummy VARCHAR2(1);
5103
5104 BEGIN
5105 OPEN c1;
5106 FETCH c1 into l_dummy;
5107 IF c1%NOTFOUND THEN
5108 CLOSE c1;
5109 return 'N';
5110 END IF;
5111 CLOSE c1;
5112 return 'Y';
5113 END CHECK_PENDING_LINK_CHANGES;
5114
5115 end PA_PROJECT_STRUCTURE_UTILS;
[Home] [Help]