1 package body PA_PROJECT_STRUCTURE_UTILS as
2 /*$Header: PAXSTCUB.pls 120.23.12020000.2 2013/04/10 18:57:21 skkoppul 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_elem_ver_structure a, -- Bug 16325665
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 ---fix for bug 14783704
3780 FUNCTION get_current_working_ver_id(p_project_id NUMBER)
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 CURSOR get_struct_version_id
3799 IS
3800 SELECT pev.element_version_id
3801 FROM pa_proj_element_versions pev ,
3802 pa_proj_structure_types pst
3803 WHERE pev.project_id = p_project_id
3804 AND pev.object_type = 'PA_STRUCTURES'
3805 AND pev.proj_element_id = pst.proj_element_id
3806 AND pst.structure_type_id = 1; -- WORKPLAN
3807
3808 l_structure_version_id NUMBER;
3809
3810 l_versioned VARCHAR2(1) ;
3811
3812 BEGIN
3813 l_versioned := PA_WORKPLAN_ATTR_UTILS.Check_Wp_Versioning_Enabled(p_project_id);
3814
3815 IF nvl(l_versioned,'N') = 'Y' then
3816 --Version Enabled Find CWV
3817
3818 OPEN cur_cwv;
3819 FETCH cur_cwv into l_structure_version_id;
3820 CLOSE cur_cwv;
3821
3822 ELSE
3823 --Version Disabled - Get workplan structure id
3824 OPEN get_struct_version_id ;
3825 FETCH get_struct_version_id INTO l_structure_version_id ;
3826 CLOSE get_struct_version_id ;
3827 END IF ;
3828
3829 return l_structure_version_id;
3830
3831 END get_current_working_ver_id;
3832
3833
3834 --------------------------------------------------------------------------
3835 -- API name : Check_Struct_Has_Dep
3836 -- Type : Utils API
3837 -- Pre-reqs : None
3838 -- Return Value : Check the dependency of a structure version ID
3839 --
3840 -- Parameters
3841 -- P_Version_ID IN NUMBER
3842 --
3843 -- History
3844 --
3845 -- 6-Jan-04 Bhumesh K. -Created
3846 -- This is added for FP_M changes. Refer to tracking bug 3305199 for more details
3847
3848 FUNCTION Check_Struct_Has_Dep (
3849 P_Version_ID IN NUMBER
3850 )
3851 RETURN VARCHAR2
3852 IS
3853 l_Exist_Flag varchar2(2) := 'N';
3854
3855 BEGIN
3856 For Rec IN ( select a.ELEMENT_VERSION_ID
3857 from pa_proj_element_versions a
3858 -- where a.project_id = 1027
3859 -- This line is not reqd. Its used only for testing
3860 where a.Parent_Structure_Version_ID = P_Version_ID
3861 and a.Element_Version_ID IN (
3862 Select b.OBJECT_ID_FROM1
3863 from pa_object_relationships b
3864 where b.Relationship_Type = 'S'
3865 and b.Object_Type_From = 'PA_TASKS'
3866 ) )
3867 Loop
3868 Begin
3869 Select 'Y'
3870 Into l_Exist_Flag
3871 from pa_object_relationships b
3872 where Rec.Element_Version_ID IN ( b.Object_ID_From1, b.Object_ID_To1 )
3873 and b.Object_Type_From = 'PA_TASKS'
3874 and b.Object_Type_To = 'PA_TASKS'
3875 and b.Relationship_Type = 'D';
3876 Exception when No_Data_Found Then Null;
3877 End;
3878 If l_Exist_Flag = 'Y' then
3879 Return l_Exist_Flag;
3880 End IF;
3881 End Loop;
3882
3883 Return l_Exist_Flag;
3884
3885 END Check_Struct_Has_Dep;
3886
3887 --this function should return one of the followings:
3888 -- SHARE_FULL
3889 -- SHARE_PARTIAL
3890 -- SPLIT_MAPPING
3891 -- SPLIT_NO_MAPPING
3892 --
3893 -- null can also be returned
3894
3895 FUNCTION get_Structure_sharing_code(
3896 p_project_id IN NUMBER
3897 )
3898 RETURN VARCHAR2
3899 IS
3900 CURSOR c1 IS
3901 select STRUCTURE_SHARING_CODE
3902 from pa_projects_all
3903 where project_id = p_project_id;
3904 l_sharing_code varchar2(30);
3905 BEGIN
3906 OPEN c1;
3907 FETCH c1 into l_sharing_code;
3908 CLOSE c1;
3909 return l_sharing_code;
3910 END get_structure_sharing_code;
3911
3912
3913 FUNCTION check_third_party_sch_flag(
3914 p_project_id IN NUMBER
3915 )
3916 RETURN VARCHAR2
3917 IS
3918 cursor get_third_party_flag IS
3919 Select ppwa.SCHEDULE_THIRD_PARTY_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) := 'N';
3928 BEGIN
3929 OPEN get_third_party_flag;
3930 FETCH get_third_party_flag into l_flag;
3931 CLOSE get_third_party_flag;
3932 IF l_flag IS NULL THEN
3933 return 'N';
3934 END IF;
3935 return l_flag;
3936 END check_third_party_sch_flag;
3937
3938 FUNCTION check_dep_on_summary_tk_ok(
3939 p_project_id IN NUMBER
3940 )
3941 RETURN VARCHAR2
3942 IS
3943 cursor get_lowest_tsk_dep_flag IS
3944 Select ppwa.ALLOW_LOWEST_TSK_DEP_FLAG
3945 from pa_proj_workplan_attr ppwa,
3946 pa_proj_structure_types ppst,
3947 pa_structure_types pst
3948 where p_project_id = ppwa.project_id
3949 and ppwa.proj_element_id = ppst.proj_element_id
3950 and ppst.structure_type_id = pst.structure_type_id
3951 and pst.structure_type = 'WORKPLAN';
3952 l_flag VARCHAR2(1) := 'Y';
3953 BEGIN
3954 OPEN get_lowest_tsk_dep_flag;
3955 FETCH get_lowest_tsk_dep_flag into l_flag;
3956 CLOSE get_lowest_tsk_dep_flag;
3957 IF l_flag IS NULL THEN
3958 return 'Y';
3959 END IF;
3960
3961 IF (l_flag = 'Y') THEN
3962 return 'N';
3963 END IF;
3964 return 'Y';
3965 END check_dep_on_summary_tk_ok;
3966
3967 FUNCTION GET_LAST_UPD_WORK_VER_OLD
3968 ( p_structure_id IN NUMBER
3969 ) return NUMBER
3970 IS
3971 CURSOR c1 IS
3972 SELECT MAX(a.last_update_date), b.parent_structure_version_id
3973 FROM pa_proj_element_versions b,
3974 pa_proj_elem_ver_schedule a,
3975 pa_proj_elem_ver_structure c
3976 WHERE a.element_version_id (+)= b.element_version_id
3977 AND a.project_id (+) = b.project_id
3978 AND a.proj_element_id (+) = b.proj_element_id
3979 AND b.parent_structure_version_id = c.element_version_id
3980 AND b.project_id = c.project_id
3981 AND c.status_code <> 'STRUCTURE_PUBLISHED'
3982 AND b.proj_element_id = p_structure_id
3983 GROUP BY b.parent_structure_version_id
3984 ORDER BY MAX(a.last_update_date) desc;
3985 l_date DATE;
3986 l_structure_version_id NUMBER;
3987 BEGIN
3988 OPEN c1;
3989 FETCH c1 into l_date, l_structure_version_id;
3990 CLOSE c1;
3991 return l_structure_version_id;
3992 END GET_LAST_UPD_WORK_VER_OLD;
3993
3994 FUNCTION GET_STRUCT_VER_UPDATE_FLAG(
3995 p_structure_version_id NUMBER
3996 ) return VARCHAR2
3997 IS
3998 CURSOR c1 IS
3999 select project_id from pa_proj_element_versions
4000 where element_version_id = p_structure_version_id;
4001 l_proj_id NUMBER;
4002
4003 CURSOR c2(c_project_id NUMBER) IS
4004 select process_update_wbs_flag from pa_proj_elem_ver_structure
4005 where project_id = c_project_id and element_version_id = p_structure_Version_id;
4006 l_flag VARCHAR2(1);
4007 BEGIN
4008 OPEN c1;
4009 FETCH c1 into l_proj_id;
4010 CLOSE c1;
4011
4012 OPEN c2(l_proj_id);
4013 FETCH c2 into l_flag;
4014 CLOSE c2;
4015
4016 IF l_flag <> 'N' THEN
4017 IF get_process_status_code(l_proj_id, p_structure_version_id) <> 'WUP' THEN
4018 return 'Y';
4019 END IF;
4020 return 'C';
4021 END IF;
4022
4023 RETURN 'N';
4024 END GET_STRUCT_VER_UPDATE_FLAG;
4025 --
4026 FUNCTION Get_Baseline_Struct_Ver(p_project_id IN NUMBER)
4027 RETURN NUMBER
4028 IS
4029 --Bug No 3692992 Performance fix
4030 /* CURSOR get_baseline_wp_ver(cp_proj_id NUMBER) IS
4031 SELECT ppev.element_version_id
4032 FROM pa_proj_elements ppe,
4033 pa_proj_element_versions ppev,
4034 pa_proj_elem_ver_structure ppevs,
4035 pa_proj_structure_types ppst,
4036 pa_structure_types pst
4037 WHERE ppe.proj_element_id = ppev.proj_element_id
4038 AND ppe.project_id = ppev.project_id
4039 AND ppe.project_id = cp_proj_id
4040 AND ppe.object_type = 'PA_STRUCTURES'
4041 AND ppev.element_version_id = ppevs.ELEMENT_VERSION_ID
4042 AND ppevs.CURRENT_FLAG = 'Y'
4043 AND pst.structure_type_id = ppst.structure_type_id
4044 AND ppst.proj_element_id = ppe.proj_element_id
4045 AND pst.structure_type_class_code = 'WORKPLAN';*/
4046
4047 CURSOR get_baseline_wp_ver(cp_proj_id NUMBER) IS
4048 SELECT ppev.element_version_id
4049 FROM pa_proj_elements ppe,
4050 pa_proj_element_versions ppev,
4051 pa_proj_elem_ver_structure ppevs,
4052 pa_proj_structure_types ppst,
4053 pa_structure_types pst
4054 WHERE ppe.proj_element_id = ppev.proj_element_id
4055 AND ppe.project_id = ppev.project_id
4056 AND ppe.project_id = cp_proj_id
4057 AND ppe.object_type = 'PA_STRUCTURES'
4058 AND ppev.element_version_id = ppevs.ELEMENT_VERSION_ID
4059 AND ppev.project_id = ppevs.project_id
4060 AND ((ppevs.CURRENT_FLAG = 'Y') OR ((ppevs.CURRENT_FLAG = 'N') AND (PA_WORKPLAN_ATTR_UTILS.CHECK_WP_VERSIONING_ENABLED(cp_proj_id) = 'N') ) )
4061 --Added for the fix of 7658505
4062 AND pst.structure_type_id = ppst.structure_type_id
4063 AND ppst.proj_element_id = ppe.proj_element_id
4064 AND pst.structure_type_class_code = 'WORKPLAN';
4065
4066 l_baseln_wp_struct_ver_id NUMBER;
4067 --
4068 BEGIN
4069 OPEN get_baseline_wp_ver(p_project_id);
4070 FETCH get_baseline_wp_ver INTO l_baseln_wp_struct_ver_id;
4071 IF get_baseline_wp_ver%NOTFOUND THEN
4072 l_baseln_wp_struct_ver_id := -1;
4073 END IF;
4074 CLOSE get_baseline_wp_ver;
4075
4076 RETURN l_baseln_wp_struct_ver_id;
4077
4078 END GET_BASELINE_STRUCT_VER;
4079 --
4080 --
4081 FUNCTION Get_Sch_Dirty_fl
4082 ( p_project_id IN NUMBER
4083 ,p_structure_version_id IN NUMBER)
4084 RETURN VARCHAR2
4085 IS
4086 --
4087 CURSOR get_sch_dirty_fl(cp_project_id NUMBER,cp_structure_version_id NUMBER) IS
4088 SELECT schedule_dirty_flag
4089 FROM pa_proj_elem_ver_structure
4090 WHERE project_id = cp_project_id
4091 AND element_version_id = cp_structure_version_id;
4092 l_dirty_flag pa_proj_elem_ver_structure.schedule_dirty_flag%TYPE;
4093 --
4094 BEGIN
4095 --
4096 OPEN get_sch_dirty_fl(p_project_id,p_structure_version_id);
4097 FETCH get_sch_dirty_fl INTO l_dirty_flag;
4098 IF get_sch_dirty_fl%NOTFOUND THEN
4099 l_dirty_flag := NULL;
4100 END IF;
4101 CLOSE get_sch_dirty_fl;
4102 --
4103 RETURN l_dirty_flag;
4104 --
4105 END Get_Sch_Dirty_fl;
4106 --
4107 --
4108 -- API name : Check_Subproject_Exists
4109 -- Type : Utils API
4110 -- Pre-reqs : None
4111 -- Return Value : This API check if there is subproject association for the given
4112 -- sturcture version id and project_id. Return Y if there is subproject
4113 -- association or N if there is subproject association
4114 --
4115 -- Parameters
4116 -- p_project_id IN NUMBER
4117 -- p_structure_ver_id IN NUMBER
4118 --
4119 -- History
4120 --
4121 -- 29-Mar-04 SMUKKA -Created
4122 --
4123 FUNCTION Check_Subproject_Exists
4124 (
4125 p_project_id NUMBER,
4126 p_structure_ver_id NUMBER
4127 ,p_link_type VARCHAR2 default 'SHARED' --bug 4541039
4128 )RETURN VARCHAR2
4129 IS
4130 --bug 4087964: changed sql
4131 CURSOR check_subproj_exists IS
4132 SELECT '1'
4133 FROM pa_proj_element_Versions ppev,
4134 pa_object_relationships por
4135 where ppev.parent_structure_version_id = p_structure_ver_id
4136 and ppev.element_version_id = por.object_id_from1
4137 and ppev.project_id = p_project_id
4138 and por.relationship_type IN ('LW', 'LF');
4139
4140
4141 --bug 4541039
4142 CURSOR check_subproj_exists_wp IS
4143 SELECT '1'
4144 FROM pa_proj_element_Versions ppev,
4145 pa_object_relationships por
4146 where ppev.parent_structure_version_id = p_structure_ver_id
4147 and ppev.element_version_id = por.object_id_from1
4148 and ppev.project_id = p_project_id
4149 and por.relationship_type = 'LW';
4150
4151 CURSOR check_subproj_exists_fn IS
4152 SELECT '1'
4153 FROM pa_proj_element_Versions ppev,
4154 pa_object_relationships por
4155 where ppev.parent_structure_version_id = p_structure_ver_id
4156 and ppev.element_version_id = por.object_id_from1
4157 and ppev.project_id = p_project_id
4158 and por.relationship_type = 'LF';
4159 --bug 4541039
4160
4161
4162
4163 /*
4164 CURSOR chk_lnk_task(cp_structure_version_id NUMBER,cp_project_id NUMBER) IS
4165 SELECT '1'
4166 FROM pa_proj_element_versions ppev,
4167 pa_proj_elements ppe
4168 WHERE ppev.element_version_id = cp_structure_version_id
4169 AND ppev.project_id = cp_project_id
4170 AND ppev.project_id = ppe.project_id
4171 AND ppev.proj_element_id = ppe.parent_structure_id
4172 AND ppe.link_task_flag = 'Y';
4173 */
4174 l_dummy VARCHAR2(1);
4175 BEGIN
4176 IF p_link_type = 'SHARED' --bug 4541039
4177 THEN
4178 OPEN check_subproj_exists;
4179 FETCH check_subproj_exists INTO l_dummy;
4180 IF check_subproj_exists%NOTFOUND THEN
4181 CLOSE check_subproj_exists;
4182 RETURN 'N';
4183 ELSE
4184 CLOSE check_subproj_exists;
4185 RETURN 'Y';
4186 END IF;
4187 --bug 4541039
4188 ELSIF p_link_type = 'WORKPLAN'
4189 THEN
4190 OPEN check_subproj_exists_wp;
4191 FETCH check_subproj_exists_wp INTO l_dummy;
4192 IF check_subproj_exists_wp%NOTFOUND THEN
4193 CLOSE check_subproj_exists_wp;
4194 RETURN 'N';
4195 ELSE
4196 CLOSE check_subproj_exists_wp;
4197 RETURN 'Y';
4198 END IF;
4199 ELSIF p_link_type = 'FINANCIAL'
4200 THEN
4201 OPEN check_subproj_exists_fn;
4202 FETCH check_subproj_exists_fn INTO l_dummy;
4203 IF check_subproj_exists_fn%NOTFOUND THEN
4204 CLOSE check_subproj_exists_fn;
4205 RETURN 'N';
4206 ELSE
4207 CLOSE check_subproj_exists_fn;
4208 RETURN 'Y';
4209 END IF;
4210 END IF;
4211 --end bug 4541039
4212
4213 END Check_Subproject_Exists;
4214 --
4215 --
4216 -- API name : Check_Structure_Ver_Exists
4217 -- Type : Utils API
4218 -- Pre-reqs : None
4219 -- Return Value : This API check if there is structure version is valid exists for the
4220 -- given sturcture version id and project_id. Return Y if there is structure version
4221 -- or N if there is no structure version.
4222 --
4223 -- Parameters
4224 -- p_project_id IN NUMBER
4225 -- p_structure_ver_id IN NUMBER
4226 --
4227 -- History
4228 --
4229 -- 16-JUL-04 SMUKKA -Created
4230 --
4231 --
4232 FUNCTION Check_Structure_Ver_Exists
4233 (
4234 p_project_id NUMBER,
4235 p_structure_ver_id NUMBER
4236 )RETURN VARCHAR2
4237 IS
4238 CURSOR chk_structure_ver(cp_project_id NUMBER,cp_struct_ver_id NUMBER)
4239 IS
4240 SELECT 'Y'
4241 FROM pa_proj_element_versions
4242 WHERE project_id = cp_project_id
4243 AND element_version_id = cp_struct_ver_id
4244 AND object_type = 'PA_STRUCTURES';
4245 --
4246 l_dummy VARCHAR2(1) := 'N';
4247 --
4248 BEGIN
4249 --
4250 open chk_structure_ver(p_project_id, p_structure_ver_id);
4251 FETCH chk_structure_ver into l_dummy;
4252 IF chk_structure_ver%FOUND THEN
4253 l_dummy := 'Y';
4254 END IF;
4255 close chk_structure_ver;
4256 --
4257 Return l_dummy;
4258 --
4259 END Check_Structure_Ver_Exists;
4260 --
4261 --
4262 Function Check_Project_exists(p_project_id IN NUMBER)
4263 RETURN VARCHAR2
4264 IS
4265 CURSOR chk_project(cp_project_id NUMBER)
4266 IS
4267 SELECT 'Y'
4268 FROM pa_projects_all
4269 WHERE project_id = cp_project_id;
4270 --
4271 l_dummy VARCHAR2(1) := 'N';
4272 --
4273 BEGIN
4274 --
4275 open chk_project(p_project_id);
4276 FETCH chk_project into l_dummy;
4277 IF chk_project%FOUND THEN
4278 l_dummy := 'Y';
4279 END IF;
4280 close chk_project;
4281 --
4282 Return l_dummy;
4283 --
4284 END Check_Project_exists;
4285 --
4286 --
4287 -- Begin fix for Bug # 4373055.
4288
4289 PROCEDURE GET_PROCESS_STATUS_MSG(
4290 p_project_id IN pa_projects_all.project_id%TYPE
4291 , p_structure_type IN pa_structure_types.structure_type%TYPE := NULL
4292 , p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE := NULL
4293 , p_context IN VARCHAR2 := NULL
4294 , x_message_name OUT NOCOPY VARCHAR2 -- 4537865
4295 , x_message_type OUT NOCOPY VARCHAR2 -- 4537865
4296 , x_structure_version_id OUT NOCOPY NUMBER -- 4537865
4297 , x_conc_request_id OUT NOCOPY NUMBER) -- 4537865
4298 IS
4299
4300 /*
4301 This API fetches the message name of the message to be displayed based on the process status code
4302 of the workplan structure. These process status codes signify either that a process is in progress
4303 on the structure version / structure type or a process has failed with errors on the structure version /
4304 structure type.
4305 */
4306
4307 cursor cur_proc_ver(c_project_id pa_projects_all.project_id%TYPE
4308 , c_structure_type pa_structure_types.structure_type%TYPE) is
4309 select vs.process_code, vs.element_version_id, vs.conc_request_id
4310 from pa_proj_elem_ver_structure vs
4311 , pa_proj_structure_types pst
4312 , pa_structure_types st
4313 where vs.project_id = c_project_id
4314 and pst.PROJ_ELEMENT_ID = vs.proj_element_id
4315 and pst.structure_type_id = st.structure_type_id
4316 and st.structure_type = c_structure_type
4317 and vs.process_code is not null; -- Fix for Bug # 4373055.
4318
4319 cursor cur_process_code(c_project_id pa_projects_all.project_id%TYPE
4320 , c_structure_version_id pa_proj_element_versions.element_version_id%TYPE) is
4321 select process_code, element_version_id, conc_request_id
4322 from pa_proj_elem_ver_structure
4323 where project_id = c_project_id
4324 and element_version_id = c_structure_version_id;
4325
4326 l_process_code FND_LOOKUPS.lookup_code%TYPE := null;
4327 l_structure_version_id NUMBER:= null;
4328 l_conc_request_id NUMBER := null;
4329
4330 cursor cur_message(c_process_code FND_LOOKUPS.lookup_code%TYPE) is
4331 select meaning message_name, 'PROCESS' message_type
4332 from pa_lookups
4333 where lookup_code = c_process_code
4334 and c_process_code like '%P'
4335 union all
4336 select meaning message_name, 'ERROR_EDITABLE' message_type
4337 from pa_lookups
4338 where lookup_code = c_process_code
4339 and c_process_code like '%E'
4340 and c_process_code <> 'PUE'
4341 union all
4342 select meaning message_name, 'ERROR_NOT_EDITABLE' message_type
4343 from pa_lookups
4344 where lookup_code = c_process_code
4345 and c_process_code = 'PUE'
4346 ;
4347
4348 l_message_name VARCHAR2(30) := null;
4349 l_message_type VARCHAR2(30) := null;
4350
4351 BEGIN
4352
4353 l_process_code := null;
4354 l_message_name := null;
4355 l_message_type := null;
4356 x_structure_version_id := null;
4357 x_conc_request_id := null;
4358
4359 if (p_structure_type is not null) then
4360
4361 open cur_proc_ver(p_project_id,p_structure_type);
4362 fetch cur_proc_ver into l_process_code, l_structure_version_id
4363 , l_conc_request_id;
4364 close cur_proc_ver;
4365
4366 elsif (p_structure_version_id is not null) then
4367
4368 open cur_process_code(p_project_id, p_structure_version_id);
4369 fetch cur_process_code into l_process_code, l_structure_version_id
4370 , l_conc_request_id;
4371 close cur_process_code;
4372
4373 else
4374 l_process_code := null;
4375
4376 end if;
4377
4378 if l_process_code is not NULL then
4379
4380 -- Begin fix for Bug # 4475657.
4381
4382 -- The architecture team requires the meaning of lookup types to be translatable text.
4383 -- Hence, we have removed the message name from the meaning column of the lookup type and matched
4384 -- a lookup code to its corresponding message using the PL/SQL logic below.
4385
4386 if (l_process_code = 'CPI') then
4387
4388 l_message_name := 'PA_PS_PUBWBS_PRC_CHLDPUB';
4389
4390 l_message_type := 'INFORMATION';
4391
4392 elsif (l_process_code = 'APP') then
4393
4394 l_message_name := 'PA_PS_APLPRG_PRC_INPROC';
4395
4396 l_message_type := 'PROCESS';
4397
4398 elsif (l_process_code = 'APE') then
4399
4400 l_message_name := 'PA_PS_APLPRG_PRC_ERR';
4401
4402 l_message_type := 'ERROR_EDITABLE';
4403
4404 elsif (l_process_code = 'APS') then
4405
4406 l_message_name := null;
4407
4408 l_message_type := null;
4409
4410 elsif (l_process_code = 'PUP') then
4411
4412 l_message_name := 'PA_PS_PUBWBS_PRC_INPROC';
4413
4414 l_message_type := 'PROCESS';
4415
4416 elsif (l_process_code = 'PUE') then
4417
4418 l_message_name := 'PA_PS_PUBWBS_PRC_ERR';
4419
4420 l_message_type := 'ERROR_NOT_EDITABLE'; -- Bug # 4577934. -- 'ERROR_EDITABLE'; -- Bug # 4562309.
4421
4422 elsif (l_process_code = 'PUS') then
4423
4424 l_message_name := null;
4425
4426 l_message_type := null;
4427
4428 elsif (l_process_code = 'WUP') then
4429
4430 l_message_name := 'PA_PS_UDTWBS_PRC_INPROC';
4431
4432 l_message_type := 'PROCESS';
4433
4434 elsif (l_process_code = 'WUE') then
4435
4436 l_message_name := 'PA_PS_UDTWBS_PRC_ERR';
4437
4438 l_message_type := 'ERROR_EDITABLE';
4439
4440 elsif (l_process_code = 'WUS') then
4441
4442 l_message_name := null;
4443
4444 l_message_type := null;
4445
4446 /* Added For bug#5659575 */
4447 elsif (l_process_code = 'PRE' and p_context is NULL) then
4448
4449 l_message_name := 'PA_PS_UPDPERF_PRC_ERR';
4450
4451 l_message_type := 'ERROR_EDITABLE';
4452
4453 end if;
4454
4455 -- open cur_message(l_process_code);
4456 -- fetch cur_message into l_message_name, l_message_type;
4457 -- close cur_message;
4458
4459 -- End fix for Bug # 4475657.
4460
4461 end if;
4462
4463 if ((p_context = 'UPDATE_AMG') and (l_message_name is not null)) then -- Fix for Bug # 4373055.
4464
4465 l_message_name := l_message_name||'_AMG';
4466
4467 end if;
4468
4469 x_message_name := l_message_name;
4470 x_message_type := l_message_type;
4471 x_structure_version_id := l_structure_version_id;
4472 x_conc_request_id := l_conc_request_id;
4473 -- 4537865
4474 EXCEPTION
4475 WHEN OTHERS THEN
4476 x_message_name := NULL ;
4477 x_message_type := NULL ;
4478 x_structure_version_id := NULL ;
4479 x_conc_request_id := NULL ;
4480
4481 Fnd_Msg_Pub.add_exc_msg
4482 ( p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS'
4483 , p_procedure_name => 'GET_PROCESS_STATUS_MSG'
4484 , p_error_text => SUBSTRB(SQLERRM,1,240));
4485
4486 RAISE ;
4487
4488 END GET_PROCESS_STATUS_MSG;
4489
4490
4491 PROCEDURE SET_PROCESS_CODE_IN_PROC(
4492 p_project_id IN NUMBER
4493 , p_structure_version_id IN NUMBER
4494 , p_calling_context IN VARCHAR2
4495 , p_conc_request_id IN NUMBER
4496 , x_return_status OUT NOCOPY VARCHAR2) -- 4537865
4497 IS
4498
4499 l_return_status VARCHAR2(30) := NULL;
4500
4501 BEGIN
4502
4503 l_return_status := FND_API.G_RET_STS_SUCCESS;
4504
4505 update pa_proj_elem_ver_structure
4506 set process_code = decode(p_calling_context, 'APPLY_PROGRESS', 'APP'
4507 , 'CONC_PUBLISH', 'PUP'
4508 , 'CONC_UPDATE', 'WUP'
4509 , 'ASGMT_PLAN_CHANGE', 'WUP'
4510 ,null) ---4492493
4511 , conc_request_id = p_conc_request_id
4512 where element_version_id = p_structure_version_id
4513 and project_id = p_project_id;
4514
4515 x_return_status := l_return_status;
4516
4517 commit;
4518
4519 EXCEPTION
4520
4521 when others then
4522
4523 -- Changed l_return_status to x_return_status : 4537865
4524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4525
4526 raise;
4527
4528 END SET_PROCESS_CODE_IN_PROC;
4529
4530
4531 PROCEDURE SET_PROCESS_CODE_ERR(
4532 p_project_id IN NUMBER
4533 , p_structure_version_id IN NUMBER
4534 , p_calling_context IN VARCHAR2
4535 , p_conc_request_id IN NUMBER
4536 , x_return_status OUT NOCOPY VARCHAR2) -- 4537865
4537 IS
4538
4539 l_return_status VARCHAR2(30) := NULL;
4540
4541 BEGIN
4542
4543 l_return_status := FND_API.G_RET_STS_SUCCESS;
4544
4545 update pa_proj_elem_ver_structure
4546 set process_code = decode(p_calling_context, 'APPLY_PROGRESS', 'APE'
4547 , 'CONC_PUBLISH', 'PUE'
4548 , 'CONC_UPDATE', 'WUE', null)
4549 , conc_request_id = p_conc_request_id
4550 where element_version_id = p_structure_version_id
4551 and project_id = p_project_id;
4552
4553 x_return_status := l_return_status;
4554
4555 commit;
4556
4557 EXCEPTION
4558
4559 when others then
4560 -- 4537865 Changed l_return_status to x_return_status
4561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4562
4563 raise;
4564
4565 END SET_PROCESS_CODE_ERR;
4566
4567 -- End fix for Bug # 4373055.
4568
4569
4570 -- Begin fix for Bug # 5659575.
4571
4572 PROCEDURE SET_PROCESS_CODE(
4573 p_project_id IN NUMBER
4574 , p_structure_version_id IN NUMBER
4575 , p_process_code IN VARCHAR2
4576 , p_conc_request_id IN NUMBER
4577 , x_return_status OUT NOCOPY VARCHAR2)
4578 IS
4579
4580 l_return_status VARCHAR2(30) := NULL;
4581
4582 BEGIN
4583
4584 l_return_status := FND_API.G_RET_STS_SUCCESS;
4585 if p_process_code = 'PRE' then
4586 update pa_proj_elem_ver_structure
4587 set process_code = p_process_code
4588 , conc_request_id = p_conc_request_id
4589 where element_version_id = p_structure_version_id
4590 and project_id = p_project_id
4591 and process_code is null;
4592 else
4593 update pa_proj_elem_ver_structure
4594 set process_code = NULL
4595 where project_id = p_project_id
4596 and process_code = 'PRE';
4597 end if;
4598 x_return_status := l_return_status;
4599
4600 EXCEPTION
4601
4602 when others then
4603
4604 -- Changed l_return_status to x_return_status for NOCOPY
4605 x_return_status := FND_API.G_RET_STS_ERROR;
4606
4607 raise;
4608
4609 END SET_PROCESS_CODE;
4610
4611 -- End fix for Bug # 5659575.
4612
4613 -- Begin fix for Bug # 4502325.
4614
4615 procedure get_structure_msg(p_project_id IN NUMBER
4616 , p_structure_type IN VARCHAR2
4617 , p_structure_version_id IN NUMBER
4618 , p_context IN VARCHAR2 := NULL
4619 , x_message_name OUT NOCOPY VARCHAR2 -- 4537865
4620 , x_message_type OUT NOCOPY VARCHAR2 -- 4537865
4621 , x_structure_version_id OUT NOCOPY NUMBER -- 4537865
4622 , x_conc_request_id OUT NOCOPY NUMBER) -- 4537865
4623 is
4624
4625 cursor cur_str_sharing_code(c_project_id NUMBER) is
4626 select ppa.structure_sharing_code
4627 from pa_projects_all ppa
4628 where ppa.project_id = c_project_id;
4629
4630 l_str_sharing_code VARCHAR2(30) := null;
4631
4632 cursor cur_proc_upd_flag(c_project_id NUMBER, c_structure_version_id VARCHAR2) is
4633 select ppevs.process_update_wbs_flag, ppevs.process_code
4634 from pa_proj_elem_ver_structure ppevs, pa_proj_structure_types ppst, pa_structure_types pst
4635 where ppevs.proj_element_id = ppst.proj_element_id
4636 and ppst.structure_type_id = pst.structure_type_id
4637 and ppevs.project_id = c_project_id
4638 and ppevs.element_version_id = c_structure_version_id;
4639
4640 l_proc_upd_flag VARCHAR2(1) := null;
4641 l_process_code VARCHAR2(30) := null;
4642 l_message_name VARCHAR2(30) := null;
4643 l_message_type VARCHAR2(30) := null;
4644 l_structure_version_id NUMBER := null;
4645 l_conc_request_id NUMBER := null;
4646
4647 begin
4648
4649 l_proc_upd_flag := null;
4650 l_process_code := null;
4651 l_message_name := null;
4652 l_message_type := null;
4653 l_structure_version_id := null;
4654 l_conc_request_id := null;
4655
4656
4657 if ((p_project_id is null)
4658 or (p_structure_type is null)
4659 or (p_structure_version_id is null)) then
4660
4661 l_message_name := null;
4662 l_message_type := null;
4663 l_structure_version_id := null;
4664 l_conc_request_id := null;
4665
4666 else
4667
4668 open cur_str_sharing_code(p_project_id);
4669 fetch cur_str_sharing_code into l_str_sharing_code;
4670 close cur_str_sharing_code;
4671
4672 open cur_proc_upd_flag(p_project_id, p_structure_version_id);
4673 fetch cur_proc_upd_flag into l_proc_upd_flag, l_process_code;
4674 close cur_proc_upd_flag;
4675
4676 if (l_process_code is not null) then
4677
4678 PA_PROJECT_STRUCTURE_UTILS.GET_PROCESS_STATUS_MSG
4679 (p_project_id => p_project_id
4680 , p_structure_type => NULL
4681 , p_structure_version_id => p_structure_version_id
4682 , p_context => p_context
4683 , x_message_name => l_message_name
4684 , x_message_type => l_message_type
4685 , x_structure_version_id => l_structure_version_id
4686 , x_conc_request_id => l_conc_request_id);
4687
4688 elsif (l_proc_upd_flag = 'Y') then
4689
4690 if (p_structure_type = 'WORKPLAN') then
4691 -- Bug 5999999
4692 IF NVL(FND_PROFILE.value('PA_ROLLUP_PROGRAM_AMOUNTS'),'AUTOMATIC') = 'AUTOMATIC' THEN
4693
4694 if l_str_sharing_code in ('SHARE_FULL' , 'SHARE_PARTIAL') then
4695
4696 l_message_name := 'PA_PS_VD_SH_UPD_WBS_PRC_REQ';
4697 else
4698
4699 l_message_name := 'PA_PS_VD_SP_UPD_WBS_PRC_REQ';
4700
4701 end if;
4702 ELSE
4703
4704 l_message_name := 'PA_PS_DEF_RUP_UPD_WBS_PRC_REQ';
4705 END IF;
4706
4707 elsif (p_structure_type = 'FINANCIAL') then
4708
4709 IF NVL(FND_PROFILE.value('PA_ROLLUP_PROGRAM_AMOUNTS'),'AUTOMATIC') = 'AUTOMATIC' THEN
4710
4711 if l_str_sharing_code in ('SHARE_FULL' , 'SHARE_PARTIAL') then
4712
4713 l_message_name := 'PA_PS_VD_SH_UPD_WBS_PRC_REQ';
4714
4715 else
4716
4717 l_message_name := 'PA_PS_VD_SP_UPD_FBS_PRC_REQ';
4718
4719 end if;
4720 ELSE
4721 l_message_name := 'PA_PS_DEF_RUP_UPD_WBS_PRC_REQ';
4722 END IF;
4723
4724
4725 end if;
4726
4727 l_message_type := 'INFORMATION';
4728 l_structure_version_id := p_structure_version_id;
4729 l_conc_request_id := null;
4730
4731 else
4732
4733 l_message_name := null;
4734 l_message_type := null;
4735 l_structure_version_id := null;
4736 l_conc_request_id := null;
4737
4738 end if;
4739
4740 end if;
4741
4742 x_message_name := l_message_name;
4743 x_message_type := l_message_type;
4744 x_structure_version_id := l_structure_version_id;
4745 x_conc_request_id := l_conc_request_id;
4746 -- 4537865
4747 EXCEPTION
4748 WHEN OTHERS THEN
4749 x_message_name := NULL ;
4750 x_message_type := NULL ;
4751 x_structure_version_id := NULL ;
4752 x_conc_request_id := NULL ;
4753 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
4754 p_procedure_name => 'get_structure_msg',
4755 p_error_text => SUBSTRB(SQLERRM,1,240));
4756 RAISE;
4757 end get_structure_msg;
4758
4759 -- End fix for Bug # 4502325.
4760
4761 -- Begin Bug # 4582750.
4762
4763 procedure lock_unlock_wp_str_autonomous
4764 (p_project_id IN NUMBER
4765 ,p_structure_version_id IN NUMBER
4766 ,p_lock_status_code IN VARCHAR2 := 'LOCKED'
4767 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
4768 ,x_return_status OUT NOCOPY VARCHAR2
4769 ,x_msg_count OUT NOCOPY NUMBER
4770 ,x_msg_data OUT NOCOPY VARCHAR2)
4771 is
4772
4773 PRAGMA AUTONOMOUS_TRANSACTION;
4774
4775 l_module_name CONSTANT VARCHAR2(61) := 'PA_PROJECT_STRUCTURE_UTILS.LOCK_UNLOCK_WP_STR_AUTONOMOUS';
4776 l_msg_count NUMBER := 0;
4777 l_msg_data VARCHAR2(2000);
4778 l_debug_mode VARCHAR2(1);
4779 l_wp_str VARCHAR2(30);
4780
4781 cursor l_cur_ppevs(c_project_id NUMBER, c_str_ver_id NUMBER) is
4782 select ppevs.name, ppevs.pev_structure_id, ppevs.record_version_number
4783 from pa_proj_elem_ver_structure ppevs
4784 where ppevs.project_id = c_project_id
4785 and ppevs.element_version_id = c_str_ver_id;
4786
4787 l_rec_ppevs l_cur_ppevs%rowtype;
4788
4789 begin
4790
4791 savepoint lock_unlock_wp_str;
4792
4793 x_msg_count := 0;
4794 x_return_status := FND_API.G_RET_STS_SUCCESS;
4795 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
4796
4797 if l_debug_mode = 'Y' THEN
4798 pa_debug.set_curr_function( p_function => 'lock_unlock_wp_str_autonomous');
4799 pa_debug.write(l_module_name,'p_project_id = '||p_project_id,3);
4800 pa_debug.write(l_module_name,'p_structure_version_id = '||p_structure_version_id,3);
4801 pa_debug.write(l_module_name,'p_lock_status_code = '||p_lock_status_code,3);
4802 end if;
4803
4804 l_wp_str := PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(p_structure_version_id, 'WORKPLAN');
4805
4806 if l_debug_mode = 'Y' THEN
4807 pa_debug.write(l_module_name,'l_wp_str = '||l_wp_str,3);
4808 end if;
4809
4810 if (l_wp_str = 'Y') then
4811
4812 open l_cur_ppevs(p_project_id, p_structure_version_id);
4813 fetch l_cur_ppevs into l_rec_ppevs;
4814 close l_cur_ppevs;
4815
4816 if l_debug_mode = 'Y' THEN
4817 pa_debug.write(l_module_name,'Before Calling pa_project_structure_pub1.update_structure_version_attr',3);
4818 pa_debug.write(l_module_name,'l_rec_ppevs.pev_structure_id = '||l_rec_ppevs.pev_structure_id,3);
4819 pa_debug.write(l_module_name,'l_rec_ppevs.name= '||l_rec_ppevs.name,3);
4820 pa_debug.write(l_module_name,'l_rec_ppevs.record_version_number= '||l_rec_ppevs.record_version_number,3);
4821 end if;
4822
4823 pa_project_structure_pub1.update_structure_version_attr
4824 (p_pev_structure_id => l_rec_ppevs.pev_structure_id
4825 , p_locked_status_code => p_lock_status_code
4826 , p_structure_version_name => l_rec_ppevs.name
4827 , p_init_msg_list => FND_API.G_FALSE -- Added for bug 5130360
4828 , p_record_version_number => l_rec_ppevs.record_version_number
4829 , p_calling_module => p_calling_module
4830 , x_return_status => x_return_status
4831 , x_msg_count => x_msg_count
4832 , x_msg_data => x_msg_data);
4833
4834 if l_debug_mode = 'Y' THEN
4835 pa_debug.write(l_module_name,'After calling pa_project_structure_pub1.update_structure_version_attr x_return_status='||x_return_status,3);
4836 end if;
4837
4838
4839 end if;
4840
4841 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
4842 commit;
4843 else
4844 raise FND_API.G_EXC_ERROR;
4845 end if;
4846
4847 exception
4848
4849 when FND_API.G_EXC_ERROR then
4850 /*rollback to lock_unlock_wp_str; bug#6414944*/
4851 rollback; /*bug# 6414944*/
4852
4853 when others then
4854 /* rollback to lock_unlock_wp_str; bug#6414944*/
4855 rollback; /*Bug# 6414944*/
4856 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
4857 p_procedure_name => 'lock_unlock_wp_str_autonomous',
4858 p_error_text => SUBSTRB(SQLERRM,1,240));
4859
4860 end lock_unlock_wp_str_autonomous;
4861
4862 procedure lock_unlock_wp_str
4863 (p_project_id IN NUMBER
4864 ,p_structure_version_id IN NUMBER
4865 ,p_lock_status_code IN VARCHAR2 := 'LOCKED'
4866 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
4867 ,x_return_status OUT NOCOPY VARCHAR2
4868 ,x_msg_count OUT NOCOPY NUMBER
4869 ,x_msg_data OUT NOCOPY VARCHAR2)
4870 is
4871
4872 l_module_name CONSTANT VARCHAR2(61) := 'PA_PROJECT_STRUCTURE_UTILS.LOCK_UNLOCK_WP_STR';
4873 l_msg_count NUMBER := 0;
4874 l_msg_data VARCHAR2(2000);
4875 l_debug_mode VARCHAR2(1);
4876 l_wp_str VARCHAR2(30);
4877
4878 cursor l_cur_ppevs(c_project_id NUMBER, c_str_ver_id NUMBER) is
4879 select ppevs.name, ppevs.pev_structure_id, ppevs.record_version_number
4880 from pa_proj_elem_ver_structure ppevs
4881 where ppevs.project_id = c_project_id
4882 and ppevs.element_version_id = c_str_ver_id;
4883
4884 l_rec_ppevs l_cur_ppevs%rowtype;
4885
4886 begin
4887
4888 savepoint lock_unlock_wp_str2;
4889
4890 x_msg_count := 0;
4891 x_return_status := FND_API.G_RET_STS_SUCCESS;
4892 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
4893
4894 if l_debug_mode = 'Y' THEN
4895 pa_debug.set_curr_function( p_function => 'lock_unlock_wp_str');
4896 pa_debug.write(l_module_name,'p_project_id = '||p_project_id,3);
4897 pa_debug.write(l_module_name,'p_structure_version_id = '||p_structure_version_id,3);
4898 pa_debug.write(l_module_name,'p_lock_status_code = '||p_lock_status_code,3);
4899 end if;
4900
4901 l_wp_str := PA_PROJECT_STRUCTURE_UTILS.Get_Struc_Type_For_Version(p_structure_version_id, 'WORKPLAN');
4902
4903 if l_debug_mode = 'Y' THEN
4904 pa_debug.write(l_module_name,'l_wp_str = '||l_wp_str,3);
4905 end if;
4906
4907 if (l_wp_str = 'Y') then
4908
4909 open l_cur_ppevs(p_project_id, p_structure_version_id);
4910 fetch l_cur_ppevs into l_rec_ppevs;
4911 close l_cur_ppevs;
4912
4913 if l_debug_mode = 'Y' THEN
4914 pa_debug.write(l_module_name,'Before Calling pa_project_structure_pub1.update_structure_version_attr',3);
4915 pa_debug.write(l_module_name,'l_rec_ppevs.pev_structure_id = '||l_rec_ppevs.pev_structure_id,3);
4916 pa_debug.write(l_module_name,'l_rec_ppevs.name= '||l_rec_ppevs.name,3);
4917 pa_debug.write(l_module_name,'l_rec_ppevs.record_version_number= '||l_rec_ppevs.record_version_number,3);
4918 end if;
4919
4920 pa_project_structure_pub1.update_structure_version_attr
4921 (p_pev_structure_id => l_rec_ppevs.pev_structure_id
4922 , p_locked_status_code => p_lock_status_code
4923 , p_structure_version_name => l_rec_ppevs.name
4924 , p_init_msg_list => FND_API.G_FALSE -- Added for bug 5130360
4925 , p_record_version_number => l_rec_ppevs.record_version_number
4926 , p_calling_module => p_calling_module
4927 , x_return_status => x_return_status
4928 , x_msg_count => x_msg_count
4929 , x_msg_data => x_msg_data);
4930
4931 if l_debug_mode = 'Y' THEN
4932 pa_debug.write(l_module_name,'After calling pa_project_structure_pub1.update_structure_version_attr x_return_status='||x_return_status,3);
4933 end if;
4934
4935
4936 end if;
4937
4938 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
4939 --commit;
4940 null;
4941 else
4942 raise FND_API.G_EXC_ERROR;
4943 end if;
4944
4945 exception
4946
4947 when FND_API.G_EXC_ERROR then
4948 rollback to lock_unlock_wp_str2;
4949
4950 when others then
4951 rollback to lock_unlock_wp_str2;
4952 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_STRUCTURE_UTILS',
4953 p_procedure_name => 'lock_unlock_wp_str',
4954 p_error_text => SUBSTRB(SQLERRM,1,240));
4955
4956 end lock_unlock_wp_str;
4957
4958 -- End Bug # 4582750.
4959
4960 --bug 4597323
4961 FUNCTION check_program_flag_enable(
4962 p_project_id NUMBER
4963 ) RETURN VARCHAR2 IS
4964
4965 CURSOR cur_proj
4966 IS
4967 SELECT sys_program_flag
4968 FROM pa_projects_all
4969 WHERE project_id=p_project_id
4970 ;
4971 l_return_value VARCHAR2(1);
4972 BEGIN
4973
4974 IF p_project_id IS NOT NULL
4975 THEN
4976 OPEN cur_proj;
4977 FETCH cur_proj INTO l_return_value;
4978 CLOSE cur_proj;
4979 END IF;
4980
4981 return l_return_value;
4982
4983 END check_program_flag_enable;
4984 --end bug 4597323
4985
4986 -- API name : check_del_pub_struc_ver_ok
4987 -- Tracking Bug : 4925192
4988 -- Type : Utils API
4989 -- Pre-reqs : None
4990 -- Return Value : Check if ok to delete Published structure
4991 -- version
4992 -- Return Y or N
4993 --
4994 -- Parameters
4995 -- p_structure_version_id IN NUMBER
4996 --
4997 -- History
4998 --
4999 -- 20-OCT-06 Ram Namburi -Created
5000 --
5001 -- Purpose:
5002 -- This API will determine whether a published structure version can be deleted or not.
5003 --
5004 -- Business Rules:
5005 --
5006 -- The published version cannot be deleted if
5007 --
5008 -- 1. It is the current baseline version - because the metrics are calculated using
5009 -- the baselined values from the current baselined workplan structure version
5010 -- 2. It is the latest published version - because the PJI module uses the
5011 -- financial plan to rollup data on the latest published workplan structure version
5012 -- 3. It is a part of a program - because it is technically challenging to handle
5013 -- the deletion of published workplan structure versions that belong to the
5014 -- program itself or an intermediate sub-project in a program hierarchy
5015
5016
5017
5018 FUNCTION check_del_pub_struc_ver_ok
5019 ( p_structure_version_id IN NUMBER
5020 ,p_project_id IN NUMBER
5021 ) return VARCHAR2
5022 IS
5023 l_object_relationship_id NUMBER;
5024 l_current_flag pa_proj_elem_ver_structure.current_flag%TYPE;
5025 l_LATEST_EFF_PUBLISHED_FLAG pa_proj_elem_ver_structure.LATEST_EFF_PUBLISHED_FLAG%TYPE;
5026 l_version_name pa_proj_elem_ver_structure.name%TYPE;
5027 l_version_number pa_proj_elem_ver_structure.version_number%TYPE;
5028
5029 -- Checks whether the published workplan structure version is part of a program
5030
5031 CURSOR link_exists_cur IS
5032 SELECT 1 FROM DUAL WHERE EXISTS (
5033 SELECT por.object_relationship_id
5034 FROM pa_object_relationships por,
5035 pa_proj_element_versions ppev
5036 WHERE por.object_id_from1 = ppev.element_version_id
5037 AND por.relationship_type in ('LW', 'LF')
5038 AND ppev.parent_structure_version_id = p_structure_version_id
5039 UNION ALL
5040 SELECT por. object_relationship_id
5041 FROM pa_object_relationships por,
5042 pa_proj_element_versions ppev
5043 WHERE por.object_id_to1 = ppev.element_version_id
5044 AND por.relationship_type in ('LW', 'LF')
5045 AND ppev.parent_structure_version_id = p_structure_version_id);
5046
5047 -- Checks whether the published workplan structure version is CURRENT or Latest
5048 -- Published version.
5049
5050 CURSOR curr_lat_pub_ver_cur IS
5051 SELECT ppevs.current_flag,
5052 ppevs.LATEST_EFF_PUBLISHED_FLAG,
5053 ppevs.name,
5054 ppevs.version_number
5055 FROM pa_proj_elem_ver_structure ppevs
5056 WHERE ppevs.element_version_id = p_structure_version_id
5057 AND ppevs.project_id = p_project_id;
5058
5059 BEGIN
5060
5061 OPEN curr_lat_pub_ver_cur;
5062 FETCH curr_lat_pub_ver_cur into l_current_flag,l_LATEST_EFF_PUBLISHED_FLAG,l_version_name,l_version_number;
5063 CLOSE curr_lat_pub_ver_cur;
5064
5065 IF l_current_flag = 'Y' THEN
5066 FND_MESSAGE.set_name('PA','PA_PS_DEL_CURR_BASE_VER');
5067 -- Commenting out the following as the messages are changed in the FD.
5068 -- Now as per the latest FD the above message doesnt need any tokens.
5069 -- FND_MESSAGE.set_token('VER_NAME',l_version_name);
5070 -- FND_MESSAGE.set_token('VER_NUMBER',l_version_number);
5071 FND_MSG_PUB.add;
5072
5073 return 'N';
5074 END IF;
5075
5076 IF l_LATEST_EFF_PUBLISHED_FLAG = 'Y' THEN
5077 FND_MESSAGE.set_name('PA','PA_PS_DEL_LATEST_PUB_VER');
5078 -- Commenting out the following as the messages are changed in the FD.
5079 -- Now as per the latest FD the above message doesnt need any tokens.
5080 -- FND_MESSAGE.set_token('VER_NAME',l_version_name);
5081 -- FND_MESSAGE.set_token('VER_NUMBER',l_version_number);
5082 FND_MSG_PUB.add;
5083
5084 return 'N';
5085 END IF;
5086
5087 OPEN link_exists_cur;
5088 FETCH link_exists_cur into l_object_relationship_id;
5089 IF link_exists_cur%FOUND THEN
5090 FND_MESSAGE.set_name('PA','PA_PS_DEL_PART_OF_PROG');
5091 -- Commenting out the following as the messages are changed in the FD.
5092 -- Now as per the latest FD the above message doesnt need any tokens.
5093 -- FND_MESSAGE.set_token('VER_NAME',l_version_name);
5094 -- FND_MESSAGE.set_token('VER_NUMBER',l_version_number);
5095 FND_MSG_PUB.add;
5096 CLOSE link_exists_cur;
5097 return 'N';
5098 END IF;
5099 CLOSE link_exists_cur;
5100
5101 RETURN 'Y';
5102
5103 END check_del_pub_struc_ver_ok;
5104
5105
5106 -- bug 5183704
5107 -- API name : check_pending_link_changes
5108 -- Type : Utils API
5109 -- Pre-reqs : None
5110 -- Return Value : This API returns "Y" if pending link changes exist for a given project as per
5111 -- log type PRG_CHANGE in the pa_pji_proj_events_log, pji_pa_proj_events_log tables.
5112 -- It's created to use from Projects Self Services (sql from vijay r).
5113 --
5114 -- Parameters
5115 -- p_project_id IN NUMBER
5116 --
5117 -- History
5118 --
5119 -- 09-MAY-06 sliburd -Created
5120 --
5121 function CHECK_PENDING_LINK_CHANGES(p_project_id NUMBER
5122 ,p_version_id NUMBER)
5123 return VARCHAR2
5124 IS
5125 CURSOR c1 IS
5126 select 1
5127 from dual
5128 where exists (select log.event_id
5129 from pji_pa_proj_events_log log,pa_proj_element_versions ver
5130 where log.event_type='PRG_CHANGE'
5131 and log.event_object =to_char(ver.prg_group)
5132 and ver.project_id=p_project_id
5133 and ver.parent_structure_version_id = nvl(p_version_id,ver.parent_structure_version_id) --Bug#8889029.added nvl
5134 union all
5135 select log.event_id
5136 from pa_pji_proj_events_log log,pa_proj_element_versions ver
5137 where log.event_type='PRG_CHANGE'
5138 and log.event_object =to_char(ver.prg_group)
5139 and ver.project_id=p_project_id
5140 and ver.parent_structure_version_id = nvl(p_version_id,ver.parent_structure_version_id) --Bug#8889029.added nvl
5141 );
5142
5143 l_dummy VARCHAR2(1);
5144
5145 BEGIN
5146 OPEN c1;
5147 FETCH c1 into l_dummy;
5148 IF c1%NOTFOUND THEN
5149 CLOSE c1;
5150 return 'N';
5151 END IF;
5152 CLOSE c1;
5153 return 'Y';
5154 END CHECK_PENDING_LINK_CHANGES;
5155
5156 -- bug 8889029
5157 -- API name : CHECK_UPPD_RUNNING
5158 -- Type : Utils API
5159 -- Pre-reqs : None
5160 -- Return Value : This API returns "Y" if PRC: Update Project Performance Data
5161 -- is running for a given project
5162 -- Parameters
5163 -- p_project_id IN NUMBER
5164 -- p_version_id IN NUMBER
5165 --
5166 -- History
5167 --
5168 -- 26-APR-07 bnoorbha -Created
5169 --
5170 function CHECK_UPPD_RUNNING(p_project_id NUMBER
5171 ,p_version_id NUMBER)
5172 return VARCHAR2
5173 IS
5174 CURSOR c1 IS
5175 select distinct worker_id
5176 from pji_pjp_proj_batch_map
5177 where project_id = p_project_id;
5178
5179 l_worker_id NUMBER;
5180 l_request_id NUMBER;
5181 l_process VARCHAR2(30);
5182
5183 BEGIN
5184 OPEN c1;
5185 FETCH c1 into l_worker_id;
5186 IF c1%NOTFOUND THEN
5187 CLOSE c1;
5188 return 'N';
5189 END IF;
5190
5191 l_process := PJI_PJP_SUM_MAIN.g_process || l_worker_id;
5192
5193 l_request_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(l_process,
5194 l_process);
5195
5196 IF (PJI_PROCESS_UTIL.REQUEST_STATUS('RUNNING',
5197 l_request_id,
5198 PJI_PJP_SUM_MAIN.g_incr_disp_name) ) THEN
5199 return 'Y';
5200 ELSE
5201 return 'N';
5202 END IF;
5203 END CHECK_UPPD_RUNNING;
5204
5205 -- bug 8889029
5206 -- API name : CHECK_UPPD_RUNNING
5207 -- Type : Utils API
5208 -- Pre-reqs : None
5209 -- Return Value : To control the profile option PA: Program Amounts Rollup Processing
5210 -- when pending program changes exists.
5211 -- Parameters
5212 -- lookup_code IN VARCHAR2
5213 --
5214 -- History
5215 --
5216 -- 26-APR-07 bnoorbha -Created
5217 --
5218 function GET_ROLLUP_PROFILE_VAL(lookup_code IN VARCHAR2)
5219 return VARCHAR2
5220 IS
5221 CURSOR c1 IS
5222 select 1
5223 from dual
5224 where exists (select log.event_id
5225 from pji_pa_proj_events_log log
5226 where log.event_type='PRG_CHANGE'
5227 union all
5228 select log.event_id
5229 from pa_pji_proj_events_log log
5230 where log.event_type='PRG_CHANGE'
5231 );
5232
5233 l_dummy VARCHAR2(1);
5234
5235 BEGIN
5236 IF NVL(FND_PROFILE.value('PA_ROLLUP_PROGRAM_AMOUNTS'),'MANUAL') = 'AUTOMATIC' THEN --Bug 11803799
5237 return lookup_code;
5238 ELSE
5239 OPEN c1;
5240 FETCH c1 into l_dummy;
5241 IF c1%NOTFOUND THEN
5242 CLOSE c1;
5243 return lookup_code;
5244 END IF;
5245 return 'MANUAL';
5246 END IF;
5247 END GET_ROLLUP_PROFILE_VAL;
5248
5249
5250 -- 9072357 : Validate all the lowest level task dates against the expenditure item dates
5251 procedure check_exp_item_dates (
5252 p_project_id IN NUMBER
5253 ,p_structure_version_id IN NUMBER
5254 ,x_return_status OUT NOCOPY VARCHAR2
5255 ,x_msg_count OUT NOCOPY NUMBER
5256 ,x_msg_data OUT NOCOPY VARCHAR2
5257 )
5258 IS
5259
5260 l_msg_index_out NUMBER;
5261 l_msg_count NUMBER;
5262 l_return_status VARCHAR2(1);
5263 l_msg_data VARCHAR2(2000);
5264
5265 l_structure_id pa_proj_elem_ver_structure.proj_element_id%TYPE;
5266 l_buffer pa_proj_workplan_attr.txn_date_sync_buf_days%TYPE;
5267
5268 l_old_task_start_date DATE;
5269 l_old_task_finish_date DATE;
5270 l_task_start_date DATE;
5271 l_task_finish_date DATE;
5272
5273 CURSOR get_structure_id
5274 IS
5275 SELECT a.proj_element_id
5276 FROM pa_proj_elements a,
5277 pa_proj_structure_types b,
5278 pa_structure_types c
5279 WHERE a.proj_element_id = b.proj_element_id
5280 AND a.object_type = 'PA_STRUCTURES'
5281 AND a.project_id = p_project_id
5282 AND b.structure_type_id = c.structure_type_id
5283 AND c.structure_type = 'WORKPLAN';
5284
5285 CURSOR get_buffer(c_structure_id pa_proj_elem_ver_structure.proj_element_id%TYPE)
5286 IS
5287 SELECT NVL(txn_date_sync_buf_days, 0)
5288 FROM pa_proj_workplan_attr
5289 WHERE proj_element_id = c_structure_id;
5290
5291 CURSOR get_tasks
5292 IS
5293 SELECT ppe.proj_element_id,
5294 ppe.element_number,
5295 ppev.element_version_id
5296 FROM pa_proj_elements ppe,
5297 pa_proj_element_versions ppev,
5298 pa_proj_elem_ver_schedule ppevs
5299 WHERE ppe.project_id = p_project_id
5300 AND ppe.proj_element_id = ppev.proj_element_id
5301 AND ppev.parent_structure_version_id = p_structure_version_id
5302 AND ppev.object_type = 'PA_TASKS'
5303 AND ppev.element_version_id = ppevs.element_version_id
5304 AND NVL(ppe.link_task_flag, 'N') = 'N';
5305
5306 CURSOR get_transaction_dates(c_proj_element_id pa_proj_elements.proj_element_id%TYPE)
5307 IS
5308 SELECT pt.start_date,
5309 pt.completion_date
5310 FROM pa_tasks pt
5311 WHERE pt.task_id = c_proj_element_id;
5312
5313 BEGIN
5314
5315 x_return_status := FND_API.G_RET_STS_SUCCESS;
5316
5317 OPEN get_structure_id;
5318 FETCH get_structure_id INTO l_structure_id;
5319 CLOSE get_structure_id;
5320
5321 IF (PA_WORKPLAN_ATTR_UTILS.CHECK_AUTO_DATE_SYNC_ENABLED(l_structure_id) = 'N') THEN
5322 RETURN;
5323 END IF;
5324
5325 OPEN get_buffer(l_structure_id);
5326 FETCH get_buffer INTO l_buffer;
5327 CLOSE get_buffer;
5328
5329 FOR rec IN get_tasks LOOP
5330
5331 -- Validate expenditure item dates for lowest level tasks (financial) only
5332 IF ((PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(rec.proj_element_id) = 'Y') AND
5333 (PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_PROJ_TASK(rec.element_version_id, p_project_id) = 'Y')) THEN
5334
5335 l_old_task_start_date := null;
5336 l_old_task_finish_date := null;
5337 l_task_start_date := null;
5338 l_task_finish_date := null;
5339
5340 -- Get the new transaction start and end date
5341 pa_project_dates_utils.get_task_copy_dates(
5342 p_project_id => p_project_id,
5343 p_proj_element_id => rec.proj_element_id,
5344 p_parent_structure_version_id => p_structure_version_id,
5345 x_task_start_date => l_task_start_date,
5346 x_task_finish_date => l_task_finish_date,
5347 p_act_fin_date_flag => 'Y');
5348
5349 l_task_start_date := l_task_start_date - l_buffer;
5350 l_task_finish_date := l_task_finish_date + l_buffer;
5351
5352 -- Get the existing transaction dates from db
5353 OPEN get_transaction_dates(rec.proj_element_id);
5354 FETCH get_transaction_dates INTO l_old_task_start_date, l_old_task_finish_date;
5355 CLOSE get_transaction_dates;
5356
5357 IF (NVL(l_old_task_start_date, sysdate) <> NVL(l_task_start_date, sysdate)) THEN
5358
5359 PA_TASKS_MAINT_UTILS.Check_Start_Date_EI (
5360 p_project_id => p_project_id,
5361 p_task_id => rec.proj_element_id,
5362 p_start_date => l_task_start_date,
5363 x_return_status => l_return_status,
5364 x_msg_count => l_msg_count,
5365 x_msg_data => l_msg_data
5366 );
5367
5368 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5369 RAISE FND_API.G_EXC_ERROR;
5370 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5372 END IF;
5373
5374 ELSIF (NVL(l_old_task_finish_date, sysdate) <> NVL(l_task_finish_date, sysdate)) THEN
5375
5376 PA_TASKS_MAINT_UTILS.Check_End_Date_EI (
5377 p_project_id => p_project_id,
5378 p_task_id => rec.proj_element_id,
5379 p_end_date => l_task_finish_date,
5380 x_return_status => l_return_status,
5381 x_msg_count => l_msg_count,
5382 x_msg_data => l_msg_data
5383 );
5384
5385 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
5386 RAISE FND_API.G_EXC_ERROR;
5387 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
5388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5389 END IF;
5390
5391 END IF;
5392
5393 END IF; -- PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_PROJ_TASK
5394 END LOOP; -- get_tasks
5395
5396 EXCEPTION
5397 WHEN FND_API.G_EXC_ERROR THEN
5398 x_return_status := FND_API.G_RET_STS_ERROR;
5399 x_msg_count := FND_MSG_PUB.count_msg;
5400
5401 IF x_msg_count > 0 THEN
5402 PA_INTERFACE_UTILS_PUB.get_messages (
5403 p_encoded => FND_API.G_FALSE
5404 , p_msg_index => 1
5405 , p_msg_count => x_msg_count
5406 , p_msg_data => x_msg_data
5407 , p_data => x_msg_data
5408 , p_msg_index_out => l_msg_index_out);
5409 END IF;
5410
5411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5413 x_msg_count := FND_MSG_PUB.count_msg;
5414
5415 IF x_msg_count > 0 THEN
5416 PA_INTERFACE_UTILS_PUB.get_messages (
5417 p_encoded => FND_API.G_FALSE
5418 , p_msg_index => 1
5419 , p_msg_count => x_msg_count
5420 , p_msg_data => x_msg_data
5421 , p_data => x_msg_data
5422 , p_msg_index_out => l_msg_index_out);
5423 END IF;
5424
5425 WHEN OTHERS THEN
5426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5427 x_msg_count := 1;
5428 x_msg_data := SUBSTRB(SQLERRM, 1, 240);
5429 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5430 FND_MSG_PUB.add_exc_msg (
5431 p_pkg_name => 'pa_project_structure_utils'
5432 ,p_procedure_name => 'check_exp_item_dates');
5433 END IF;
5434
5435 END check_exp_item_dates;
5436 -----------------------------------------------------------------------
5437 -- API name : get_resource_class_flag
5438 -- Type : Utils API
5439 -- Pre-reqs : None
5440 -- Return Value : Resource class flag
5441 --
5442 -- Parameters
5443 -- p_project_id IN NUMBER
5444 --
5445 -- History
5446 --
5447 -- 10-Jan-12 Amit Sharma -Created
5448 -- This is added for CBS changes
5449 -----------------------------------------------------------------------
5450 FUNCTION get_resource_class_flag (
5451 p_project_id IN NUMBER
5452 ) Return VARCHAR2
5453 IS
5454 l_resource_class_flag VARCHAR2(1) := 'Y';
5455 BEGIN
5456 SELECT nvl(prla.resource_class_flag, 'Y') INTO l_resource_class_flag FROM pa_projects_all ppa,
5457 pa_resource_lists_all_bg prla, pa_resource_lists_tl prl, pa_proj_fp_options ppfo
5458 where ppa.project_id = p_project_id and prla.resource_list_id = ppfo.cost_resource_list_id
5459 AND ppfo.fin_plan_option_level_code = 'PLAN_TYPE'
5460 AND ppfo.project_id = ppa.project_id AND prl.resource_list_id = prla.resource_list_id
5461 AND prl.language = userenv('LANG') AND ppfo.fin_plan_type_id = 10;
5462 RETURN l_resource_class_flag;
5463 EXCEPTION
5464 WHEN OTHERS THEN
5465 RETURN l_resource_class_flag;
5466 END get_resource_class_flag;
5467 end PA_PROJECT_STRUCTURE_UTILS;
[Home] [Help]