4 -- API name : Check_Delete_Structure_Ver_Ok
1 package PA_PROJECT_STRUCTURE_UTILS AUTHID CURRENT_USER as
2 /*$Header: PAXSTCUS.pls 120.12 2012/01/10 14:39:23 a5sharma ship $*/
3
5 -- Type : Utils API
6 -- Pre-reqs : None
7 -- Return Value : N/A
8 -- Parameters
9 -- p_project_id IN NUMBER
10 -- p_structure_version_id IN NUMBER
11 -- x_return_status OUT VARCHAR2
12 -- x_error_message_code OUT VARCHAR2
13 --
14 -- History
15 --
16 -- 25-JUN-01 HSIU -Created
17 --
18 --
19
20
21 procedure Check_Delete_Structure_Ver_Ok
22 (
23 p_project_id IN NUMBER
24 ,p_structure_version_id IN NUMBER
25 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
26 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
27 );
28
29 -- API name : Check_Structure_Name_Unique
30 -- Type : Utils API
31 -- Pre-reqs : None
32 -- Return Value : Y if not exists; N if exists.
33 -- Parameters
34 -- p_structure_name IN VARCHAR2
35 -- p_structure_id IN NUMBER
36 -- p_project_id IN NUMBER
37 --
38 -- History
39 --
40 -- 25-JUN-01 HSIU -Created
41 --
42 --
43
44
45 function Check_Structure_Name_Unique
46 (
47 p_structure_name IN VARCHAR2
48 ,p_structure_id IN NUMBER
49 ,p_project_id IN NUMBER
50 ) return varchar2;
51
52
53 -- API name : Check_Struc_Ver_Name_Unique
54 -- Type : Utils API
55 -- Pre-reqs : None
56 -- Return Value : Y if not exists; N if exists.
57 -- Parameters
58 -- p_structure_version_name IN VARCHAR2
59 -- p_pev_structure_id IN NUMBER
60 -- p_structure_id IN NUMBER
61 --
62 -- History
63 --
64 -- 25-JUN-01 HSIU -Created
65 --
66 --
67
68
69 function Check_Struc_Ver_Name_Unique
70 (
71 p_structure_version_name IN VARCHAR2
72 ,p_pev_structure_id IN NUMBER
73 ,p_project_id IN NUMBER
74 ,p_structure_id IN NUMBER
75 ) return VARCHAR2;
76
77
78
79 -- API name : Check_Structure_Type_Exists
80 -- Type : Utils API
81 -- Pre-reqs : None
82 -- Return Value : N/A
83 -- Parameters
84 --
85 -- History
86 --
87 -- 25-JUN-01 HSIU -Created
88 --
89 --
90
91
92 procedure Check_Structure_Type_Exists
93 (
94 p_project_id IN NUMBER
95 ,p_structure_type IN VARCHAR2
96 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
97 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
98 );
99
100
101 -- API name : Get_Struc_Type_For_Structure
102 -- Type : Utils API
103 -- Pre-reqs : None
104 -- Return Value : N if not exists; Y if exists.
105 -- Parameters
106 -- p_structure_id IN NUMBER
107 -- p_structure_type IN VARCHAR2
108 --
109 -- History
110 --
111 -- 25-JUN-01 HSIU -Created
112 --
113 --
114
115
116 function Get_Struc_Type_For_Structure
117 (
118 p_structure_id IN NUMBER
119 ,p_structure_type IN VARCHAR2
120 ) return VARCHAR2;
121
122
123 -- API name : Get_Struc_Type_For_Version
124 -- Type : Utils API
125 -- Pre-reqs : None
126 -- Return Value : Y if not exists; N if exists.
130 --
127 -- Parameters
128 -- p_structure_version_id IN NUMBER
129 -- p_structure_type IN VARCHAR2
131 -- History
132 --
133 -- 25-JUN-01 HSIU -Created
134 --
135 --
136
137
138 function Get_Struc_Type_For_Version
139 (
140 p_structure_version_id IN NUMBER
141 ,p_structure_type IN VARCHAR2
142 ) return VARCHAR2;
143
144
145
146 -- API name : Check_Publish_Struc_Ver_Ok
147 -- Type : Utils API
148 -- Pre-reqs : None
149 -- Return Value : Y if ok; N if can't publish.
150 -- Parameters
151 -- p_structure_version_id IN NUMBER
152 --
153 -- History
154 --
155 -- 25-JUN-01 HSIU -Created
156 --
157 --
158
159
160 function Check_Publish_Struc_Ver_Ok
161 (
162 p_structure_version_id IN NUMBER
163 ) return VARCHAR2;
164
165
166 -- API name : Check_Struc_Ver_Published
167 -- Type : Utils API
168 -- Pre-reqs : None
169 -- Return Value : N if not published; Y if published.
170 -- Parameters
171 -- p_structure_version_id IN NUMBER
172 --
173 -- History
174 --
175 -- 25-JUN-01 HSIU -Created
176 --
177 --
178
179
180 function Check_Struc_Ver_Published
181 (
182 p_project_id IN NUMBER
183 ,p_structure_version_id IN NUMBER
184 ) return VARCHAR2;
185
186
187
188 -- API name : Get_New_Struc_Ver_Name
189 -- Type : Utils API
190 -- Pre-reqs : None
191 -- Return Value : N/A
192 -- Parameters
193 -- p_structure_version_id IN NUMBER
194 -- x_structure_version_name OUT VARCHAR2
195 -- x_return_status OUT VARCHAR2
196 -- x_error_message_code OUT VARCHAR2
197 --
198 -- History
199 --
200 -- 25-JUN-01 HSIU -Created
201 --
202 --
203
204
205 procedure Get_New_Struc_Ver_Name
206 (
207 p_structure_version_id IN NUMBER
208 ,x_structure_version_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
209 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
210 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
211 );
212
213
214
215
216 -- API name : Structure_Version_Name_Or_Id
217 -- Type : Utils API
218 -- Pre-reqs : None
219 -- Return Value : N/A
220 -- Parameters
221 -- p_structure_id IN NUMBER
222 -- p_structure_version_name IN VARCHAR2
223 -- p_structure_version_id IN NUMBER
224 -- p_check_id_flag IN VARCHAR2 := PA_STARTUP.G_Check_ID_Flag
225 -- x_structure_version_id OUT NUMBER
226 -- x_return_status OUT VARCHAR2
227 -- x_error_message_code OUT VARCHAR2
228 --
229 -- History
230 --
231 -- 25-JUN-01 HSIU -Created
232 --
233 --
234
235
236 procedure Structure_Version_Name_Or_Id
237 (
238 p_structure_id IN NUMBER
239 ,p_structure_version_name IN VARCHAR2
240 ,p_structure_version_id IN NUMBER
241 ,p_check_id_flag IN VARCHAR2 := PA_STARTUP.G_Check_ID_Flag
242 ,x_structure_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
243 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
244 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
245 );
246
247
248 -- API name : Structure_Name_Or_Id
249 -- Type : Utils API
250 -- Pre-reqs : None
251 -- Return Value : N/A
252 -- Parameters
253 -- p_project_id IN NUMBER
254 -- p_structure_name IN VARCHAR2
255 -- p_structure_id IN NUMBER
256 -- p_check_id_flag IN VARCHAR2 := PA_STARTUP.G_Check_ID_Flag
257 -- x_structure_id OUT NUMBER
258 -- x_return_status OUT VARCHAR2
259 -- x_error_message_code OUT VARCHAR2
260 --
261 -- History
262 --
263 -- 25-JUN-01 HSIU -Created
264 --
265 --
266
267
268 procedure Structure_Name_Or_Id
269 (
270 p_project_id IN NUMBER
271 ,p_structure_name IN VARCHAR2
272 ,p_structure_id IN NUMBER
273 ,p_check_id_flag IN VARCHAR2 := PA_STARTUP.G_Check_ID_Flag
274 ,x_structure_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
275 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
276 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
277 );
278
279
280 -- API name : IS_STRUC_VER_LOCKED_BY_USER
281 -- Type : Utils API
285 -- N is not locked,
282 -- Pre-reqs : None
283 -- Return Value : Y is locked by user - and returns Y also when locked by other users
284 -- and the current user has privelege to Unlock the structure version,
286 -- O is locked by other user.
287 -- Parameters
288 -- p_user_id NUMBER
289 -- p_structure_version_id NUMBER
290 --
291 -- History
292 --
293 -- 01-NOV-01 HSIU -Created
294 --
295
296 function IS_STRUC_VER_LOCKED_BY_USER(p_user_id NUMBER,
297 p_structure_version_id NUMBER)
298 return VARCHAR2;
299
300 -- This function is same as above except for it returns 'O' when locked by other users
301 -- and current user has privelege to Unlock the structure version
302 -- API name : IS_STRUC_VER_LOCKED_BY_USER1
303 -- Type : Utils API
304 -- Pre-reqs : None
305 -- Return Value : Y is locked by user,
306 -- N is not locked,
307 -- O is locked by other user.
308 -- Parameters
309 -- p_user_id fnd_user.user_id%TYPE
310 -- p_structure_version_id pa_proj_element_versions.element_version_id%TYPE
311 --
312 --
313 -- History
314 --
315 -- 20-may-03 mrajput -Created
316 -- Added For bug 2964237
317
318 function IS_STRUC_VER_LOCKED_BY_USER1(p_user_id fnd_user.user_id%TYPE,
319 p_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
320 return VARCHAR2;
321
322
323
324 -- API name : GET_APPROVAL_OPTION
325 -- Type : Utils API
326 -- Pre-reqs : None
327 -- Return Value : N is no approval,
328 -- M is approval with manual publish,
329 -- A is approval with auto publish.
330 -- Parameters
331 -- p_project_id NUMBER
332 --
333 -- History
334 --
335 -- 06-NOV-01 HSIU -Created
336 --
337
338 function GET_APPROVAL_OPTION(p_project_id NUMBER)
339 return VARCHAR2;
340
341
342 -- API name : IS_STRUC_TYPE_LICENSED
343 -- Type : Utils API
344 -- Pre-reqs : None
345 -- Return Value : Y for licensed,
346 -- N for not licensed.
347 --
348 -- Parameters
349 -- p_structure_type VARCHAR2
350 --
351 -- History
352 --
353 -- 06-NOV-01 HSIU -Created
354 --
355
356 function IS_STRUC_TYPE_LICENSED(p_structure_type VARCHAR2)
357 return VARCHAR2;
358
359 -- API name : CHECK_PUBLISHED_VER_EXISTS
360 -- Type : Utils API
361 -- Pre-reqs : None
362 -- Return Value : Y for published version exists,
363 -- N for not exist.
364 --
365 -- Parameters
366 -- p_project_id NUMBER
367 -- p_structure_id NUMBER
368 --
369 -- History
370 --
371 -- 16-JAN-02 HSIU -Created
372 --
373 function CHECK_PUBLISHED_VER_EXISTS(p_project_id NUMBER,
374 p_structure_id NUMBER)
375 return VARCHAR2;
376
377 -- API name : Product_Licensed
378 -- Type : Utils API
379 -- Pre-reqs : None
380 -- Return Value : BOTH -- for workplan and (costing or billing)
381 -- WORKPLAN -- for workplan only
382 -- COSTING -- for costing or billing only
383 --
384 --
385 -- Parameters
386 -- None
387 --
388 -- History
389 --
390 -- 14-MAR-02 HSIU -Created
391 --
392 function Product_Licensed
393 return varchar2;
394
395
396 -- API name : Product_Licensed
397 -- Type : Utils API
398 -- Pre-reqs : None
399 -- Return Value : BOTH -- for having both workplan and (costing or billing)
400 -- in one structure
401 -- WORKPLAN -- for workplan structure only
402 -- COSTING -- for costing or billing structure only
403 -- SPLIT -- for having two structures, one for workplan
404 -- and one for costing.
405 --
406 -- Parameters
407 -- p_project_id NUMBER
408 --
409 -- History
410 --
411 -- 14-MAR-02 HSIU -Created
412 --
413 function Associated_Structure(p_project_id NUMBER)
414 return varchar2;
415
416
417 -- API name : Get_Rollup_Dates
418 -- Type : Utils API
419 -- Pre-reqs : None
420 -- Return Value : None
421 --
422 -- Parameters
423 -- p_element_version_id IN NUMBER
424 -- p_min_sch_start_date OUT DATE
425 -- p_max_sch_finish_date OUT DATE
426 -- p_rollup_last_update_date OUT DATE
427 --
428 -- History
429 --
430 -- 25-MAR-02 HSIU -Created
434 p_element_version_id IN NUMBER
431 --
432 procedure Get_Rollup_Dates
433 (
435 ,p_min_sch_start_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
436 ,p_max_sch_finish_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
437 ,p_rollup_last_update_date OUT NOCOPY DATE --File.Sql.39 bug 4440895
438 );
439
440 -- API name : Get_Workplan_Version
441 -- Type : Utils API
442 -- Pre-reqs : None
443 -- Return Value : None
444 --
445 -- Parameters
446 -- p_project_id IN NUMBER
447 -- p_structure_version_id OUT NUMBER
448 --
449 -- History
450 --
451 -- 10-MAY-02 HSIU -Created
452 --
453 procedure Get_Workplan_Version
454 (
455 p_project_id IN NUMBER
456 ,p_structure_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
457 );
458
459 -- API name : Get_Financial_Version
460 -- Type : Utils API
461 -- Pre-reqs : None
462 -- Return Value : None
463 --
464 -- Parameters
465 -- p_project_id IN NUMBER
466 -- p_structure_version_id OUT NUMBER
467 --
468 -- History
469 --
470 -- 26-JAN-04 sdnambia -Created
471 --
472 procedure Get_Financial_Version
473 (
474 p_project_id IN NUMBER
475 ,p_structure_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
476 );
477
478
479 -- API name : check_miss_transaction_tasks
480 -- Type : Utils API
481 -- Pre-reqs : None
482 -- Return Value :
483 --
484 -- Parameters
485 -- p_structure_version_id IN NUMBER
486 --
487 -- History
488 --
489 -- 24-MAY-01 HSIU -Created
490 --
491 --
492 PROCEDURE check_miss_transaction_tasks
493 (
494 p_structure_version_id IN NUMBER
495 ,x_return_status OUT NOCOPY VARCHAR2
496 ,x_msg_count OUT NOCOPY NUMBER
497 ,x_msg_data OUT NOCOPY VARCHAR2
498 );
499
500 -- API name : Get_Struc_Ver_Display_Text
501 -- Type : Utils API
502 -- Pre-reqs : None
503 -- Return Value : display text for structure version dropdown
504 -- list
505 --
506 -- Parameters
507 --
508 --
509 -- History
510 --
511 -- 26-JUL-02 HSIU -Created
512 --
513 --
514 FUNCTION Get_Struc_Ver_Display_Text
515 ( p_structure_version_name IN VARCHAR2
516 ,p_structure_version_number IN VARCHAR2
517 ,p_status IN VARCHAR2
518 ,p_baseline_flag IN VARCHAR2
519 ) return varchar2;
520
521
522 -- API name : CHECK_WORKPLAN_ENABLED
523 -- Type : Utils API
524 -- Pre-reqs : None
525 -- Return Value : Check if workplan is enabled
526 --
527 -- Parameters
528 -- p_project_id IN NUMBER
529 --
530 -- History
531 --
532 -- 26-JUL-02 HSIU -Created
533 --
534 FUNCTION check_workplan_enabled
535 ( p_project_id IN NUMBER
536 ) return VARCHAR2;
537
538
539 -- API name : CHECK_FINANCIAL_ENABLED
540 -- Type : Utils API
541 -- Pre-reqs : None
542 -- Return Value : Check if financial is enabled
543 --
544 -- Parameters
545 -- p_project_id IN NUMBER
546 --
547 -- History
548 --
549 -- 26-JUL-02 HSIU -Created
550 --
551 FUNCTION check_financial_enabled
552 ( p_project_id IN NUMBER
553 ) return VARCHAR2;
554
555
556 -- API name : CHECK_SHARING_ENABLED
557 -- Type : Utils API
558 -- Pre-reqs : None
559 -- Return Value : Check if workplan and financial
560 -- are sharing 1 structure
561 --
562 -- Parameters
563 -- p_project_id IN NUMBER
564 --
565 -- History
566 --
567 -- 26-JUL-02 HSIU -Created
568 --
569 FUNCTION check_sharing_enabled
570 ( p_project_id IN NUMBER
571 ) return VARCHAR2;
572
573
574 -- API name : CHECK_ENABLE_WP_OK
575 -- Type : Utils API
576 -- Pre-reqs : None
577 -- Return Value : Check if ok to enable workplan
578 -- Return Y or N
579 --
580 -- Parameters
581 -- p_project_id IN NUMBER
582 --
583 -- History
584 --
585 -- 26-JUL-02 HSIU -Created
586 --
587 procedure check_enable_wp_ok
588 ( p_project_id IN NUMBER
589 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
590 ,x_err_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
591 );
592
593
594 -- API name : CHECK_DISABLE_WP_OK
598 -- Return Y or N
595 -- Type : Utils API
596 -- Pre-reqs : None
597 -- Return Value : Check if ok to disable workplan
599 --
600 -- Parameters
601 -- p_project_id IN NUMBER
602 --
603 -- History
604 --
605 -- 26-JUL-02 HSIU -Created
606 --
607 procedure check_disable_wp_ok
608 ( p_project_id IN NUMBER
609 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
610 ,x_err_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
611 );
612
613
614 -- API name : CHECK_SHARING_ON_OK
615 -- Type : Utils API
616 -- Pre-reqs : None
617 -- Return Value : Check if ok to share workplan
618 -- Return Y or N
619 --
620 -- Parameters
621 -- p_project_id IN NUMBER
622 --
623 -- History
624 --
625 -- 26-JUL-02 HSIU -Created
626 --
627 procedure check_sharing_on_ok
628 ( p_project_id IN NUMBER
629 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
630 ,x_err_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
631 );
632
633 -- API name : CHECK_SHARING_OFF_OK
634 -- Type : Utils API
635 -- Pre-reqs : None
636 -- Return Value : Check if ok to split workplan
637 -- Return Y or N
638 --
639 -- Parameters
640 -- p_project_id IN NUMBER
641 --
642 -- History
643 --
644 -- 26-JUL-02 HSIU -Created
645 --
646 procedure check_sharing_off_ok
647 ( p_project_id IN NUMBER
648 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
649 ,x_err_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
650 );
651
652
653 -- API name : CHECK_PROJ_PROGRESS_EXIST
654 -- Type : Utils API
655 -- Pre-reqs : None
656 -- Return Value : Check if progress exists for project
657 -- Return Y or N
658 --
659 -- Parameters
660 -- p_project_id IN NUMBER
661 --
662 -- History
663 --
664 -- 26-JUL-02 HSIU -Created
665 --
666 FUNCTION check_proj_progress_exist
667 ( p_project_id IN NUMBER
668 ,p_structure_id IN NUMBER
669 ,p_structure_type IN VARCHAR2 := null -- Added a new parameter p_structure_type for the BUG 6914708
670 ) return VARCHAR2;
671
672
673 -- API name : CHECK_TASK_PROGRESS_EXIST
674 -- Type : Utils API
675 -- Pre-reqs : None
676 -- Return Value : Check if progress exists for task
677 -- Return Y or N
678 --
679 -- Parameters
680 -- p_project_id IN NUMBER
681 --
682 -- History
683 --
684 -- 26-JUL-02 HSIU -Created
685 --
686 FUNCTION check_task_progress_exist
687 ( p_task_id IN NUMBER
688 ) return VARCHAR2;
689
690
691 -- API name : GET_LAST_UPDATED_WORKING_VER
692 -- Type : Utils API
693 -- Pre-reqs : None
694 -- Return Value : Return last update working structure
695 -- version id
696 --
697 -- Parameters
698 -- p_proj_element_id IN NUMBER
699 --
700 -- History
701 --
702 -- 26-JUL-02 HSIU -Created
703 --
704 FUNCTION GET_LAST_UPDATED_WORKING_VER
705 ( p_structure_id IN NUMBER
706 ) return NUMBER;
707
708
709 -- API name : CHECK_VERSIONING_ON_OK
710 -- Type : Utils API
711 -- Pre-reqs : None
712 -- Return Value : Check if ok to version workplan
713 -- Return Y or N
714 --
715 -- Parameters
716 -- p_project_id IN NUMBER
717 --
718 -- History
719 --
720 -- 26-JUL-02 HSIU -Created
721 --
722 procedure check_versioning_on_ok
723 ( p_proj_element_id IN NUMBER
724 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
725 ,x_err_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
726 );
727
728 -- API name : CHECK_VERSIONING_OFF_OK
729 -- Type : Utils API
730 -- Pre-reqs : None
731 -- Return Value : Check if ok to turn off workplan
732 -- versioning
733 -- Return Y or N
734 --
735 -- Parameters
736 -- p_project_id IN NUMBER
737 --
738 -- History
739 --
740 -- 26-JUL-02 HSIU -Created
741 --
742 procedure check_versioning_off_ok
743 ( p_proj_element_id IN NUMBER
744 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
745 ,x_err_msg_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
746 );
747
748
749 -- API name : CHECK_FIN_TASK_PROG_EXIST
750 -- Type : Utils API
751 -- Pre-reqs : None
752 -- Return Value : Check if progress exists for financial task
756 -- p_project_id IN NUMBER
753 -- Return Y or N
754 --
755 -- Parameters
757 --
758 -- History
759 --
760 -- 26-JUL-02 HSIU -Created
761 --
762 FUNCTION check_fin_task_prog_exist
763 ( p_project_id IN NUMBER
764 ) return VARCHAR2;
765
766
767 -- API name : CHECK_WORKING_VERSION_EXIST
768 -- Type : Utils API
769 -- Pre-reqs : None
770 -- Return Value : Check if working version exists for
771 -- workplan structure
772 -- Return Y or N
773 --
774 -- Parameters
775 -- p_proj_element_id IN NUMBER
776 --
777 -- History
778 --
779 -- 26-JUL-02 HSIU -Created
780 --
781 FUNCTION check_working_version_exist
782 ( p_proj_element_id IN NUMBER
783 ) return VARCHAR2;
784
785 -- API name : CHECK_EDIT_WP_OK
786 -- Type : Utils API
787 -- Pre-reqs : None
788 -- Return Value : Check if the workplan structure version
789 -- can be edited
790 -- Return Y or N
791 --
792 -- Parameters
793 -- p_project_id IN NUMBER
794 -- p_structure_version_id IN NUMBER
795 --
796 -- History
797 --
798 -- 26-JUL-02 HSIU -Created
799 --
800 FUNCTION check_edit_wp_ok
801 ( p_project_id IN NUMBER
802 ,p_structure_version_id IN NUMBER
803 ) return VARCHAR2;
804
805
806 -- API name : CHECK_EDIT_FIN_OK
807 -- Type : Utils API
808 -- Pre-reqs : None
809 -- Return Value : Check if the financial structure version
810 -- can be edited
811 -- Return Y or N
812 --
813 -- Parameters
814 -- p_project_id IN NUMBER
815 -- p_structure_version_id IN NUMBER
816 --
817 -- History
818 --
819 -- 26-JUL-02 HSIU -Created
820 --
821 FUNCTION check_edit_fin_ok
822 ( p_project_id IN NUMBER
823 ,p_structure_version_id IN NUMBER
824 ) return VARCHAR2;
825
826 -- API name : GET_FIN_STRUCTURE_ID
827 -- Type : Utils API
828 -- Pre-reqs : None
829 -- Return Value : Get the financial structure id
830 -- Return structure id for the financial
831 -- structure
832 --
833 -- Parameters
834 -- p_project_id IN NUMBER
835 --
836 -- History
837 --
838 -- 26-JUL-02 HSIU -Created
839 --
840 FUNCTION GET_FIN_STRUCTURE_ID
841 ( p_project_id IN NUMBER
842 ) return NUMBER;
843
844 -- API name : GET_LATEST_FIN_STRUC_VER_ID
845 -- Type : Utils API
846 -- Pre-reqs : None
847 -- Return Value : Get the latest financial structure version
848 -- id. Return structure version id for the
849 -- latest financial structure version. Return
850 -- NULL if no published version exists.
851 --
852 -- Parameters
853 -- p_project_id IN NUMBER
854 --
855 -- History
856 --
857 -- 26-JUL-02 HSIU -Created
858 --
859 FUNCTION GET_LATEST_FIN_STRUC_VER_ID
860 ( p_project_id IN NUMBER
861 ) return NUMBER;
862
863 -- API name : GET_LATEST_FIN_STRUC_VER_ID
864 -- Type : Utils API
865 -- Pre-reqs : None
866 -- Return Value : 'FINANCIAL' for a financial only task or
867 -- structure, 'WORKPLAN' for a workplan only
868 -- or shared structure.
869 --
870 -- Parameters
871 -- p_project_id IN NUMBER
872 -- p_proj_element_id IN NUMBER
873 -- p_object_type IN VARCHAR2
874 --
875 -- History
876 --
877 -- 23-OCT-02 HSIU -Created
878 FUNCTION get_element_struc_type
879 ( p_project_id IN NUMBER
880 ,p_proj_element_id IN NUMBER
881 ,p_object_type IN VARCHAR2
882 ) return VARCHAR2;
883
884
885 FUNCTION get_latest_wp_version
886 ( p_project_id IN NUMBER
887 ) return NUMBER;
888
889
890 -- API name : Check_del_work_struc_ver_ok
891 -- Type : Utils API
892 -- Pre-reqs : None
893 -- Return Value : Check if ok to delete working structure
894 -- version
895 -- Return Y or N
896 --
897 -- Parameters
898 -- p_structure_version_id IN NUMBER
899 --
900 -- History
901 --
902 -- 26-JUL-02 HSIU -Created
903 --
904 FUNCTION check_del_work_struc_ver_ok
905 ( p_structure_version_id IN NUMBER
906 ) return VARCHAR2;
907
908
909 -- API name : Check_txn_on_summary_tasks
910 -- Type : Utils API
911 -- Pre-reqs : None
915 -- p_structure_version_id IN NUMBER
912 -- Return Value : Y if transactions exist on summary task
913 -- N if not
914 -- Parameters
916 -- x_return_status OUT VARCHAR2
917 -- x_error_message_code OUT VARCHAR2
918 --
919 -- History
920 --
921 -- 24-MAY-01 HSIU -Created
922 --
923 --
924 PROCEDURE Check_txn_on_summary_tasks
925 (
926 p_structure_version_id IN NUMBER
927 ,x_return_status OUT NOCOPY VARCHAR2
928 ,x_msg_count OUT NOCOPY NUMBER
929 ,x_msg_data OUT NOCOPY VARCHAR2
930 );
931
932
933 -- API name : check_tasks_statuses_valid
934 -- Type : Utils API
935 -- Pre-reqs : None
936 -- Return Value : Y if all statuses are valid in the structure version
937 -- N if not
938 -- Parameters
939 -- p_structure_version_id IN NUMBER
940 -- x_return_status OUT VARCHAR2
941 -- x_error_message_code OUT VARCHAR2
942 --
943 -- History
944 --
945 -- 24-MAY-01 HSIU -Created
946 --
947 --
948 PROCEDURE check_tasks_statuses_valid
949 (
950 p_structure_version_id IN NUMBER
951 ,x_return_status OUT NOCOPY VARCHAR2
952 ,x_msg_count OUT NOCOPY NUMBER
953 ,x_msg_data OUT NOCOPY VARCHAR2
954 );
955
956
957
958 -- API name : get_unpub_version_count
959 -- Type : Utils API
960 -- Pre-reqs : None
961 -- Return Value : number of unpublished structure versions
962 -- Parameters
963 -- p_project_id IN NUMBER
964 -- p_structure_id IN NUMBER
965 --
966 -- History
967 --
968 -- 25-JUN-01 HSIU -Created
969 --
970 --
971
972
973 function get_unpub_version_count
974 (
975 p_project_id IN NUMBER
976 ,p_structure_ver_id IN NUMBER
977 ) return NUMBER;
978
979 -- API name : get_structrue_version_status
980 -- Type : Utils API
981 -- Pre-reqs : None
982 -- Return Value : Get the status of a structure version
983 -- Parameters
984 -- p_project_id IN NUMBER
985 -- p_structure_version_id IN NUMBER
986 --
987 -- History
988 --
989 -- 08-JAN-03 maansari -Created
990 --
991 --
992
993
994 function get_structrue_version_status
995 (
996 p_project_id IN NUMBER
997 ,p_structure_version_id IN NUMBER
998 ) return VARCHAR2;
999
1000
1001 function is_structure_version_updatable
1002 (
1003 p_structure_version_id IN NUMBER
1004 ) return VARCHAR2;
1005
1006 --Bug 3010538
1007 FUNCTION GET_UPDATE_WBS_FLAG(
1008 p_project_id IN pa_projects_all.project_id%TYPE
1009 ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
1010 )
1011 return VARCHAR2;
1012
1013 --Bug 3010538
1014 FUNCTION GET_PROCESS_STATUS_CODE(
1015 p_project_id IN pa_projects_all.project_id%TYPE
1016 ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
1017 )
1018 return VARCHAR2;
1019
1020 --Bug 3010538
1021 FUNCTION GET_PROCESS_WBS_UPDATES_OPTION(
1022 p_task_count IN NUMBER
1023 ,p_project_id IN NUMBER default null --bug 4370533
1024 )
1025 return VARCHAR2;
1026
1027 --Bug 3010538
1028 FUNCTION GET_PROCESS_STATUS_CODE(
1029 p_project_id IN pa_projects_all.project_id%TYPE
1030 ,p_structure_type IN pa_structure_types.structure_type%TYPE
1031 )
1032 return VARCHAR2;
1033
1034 function GET_FIN_STRUC_VER_ID(p_project_id IN NUMBER) return NUMBER;
1035
1036 --Bug 3010538
1037 PROCEDURE GET_CONC_REQUEST_DETAILS(
1038 p_project_id IN pa_projects_all.project_id%TYPE
1039 ,p_structure_type IN pa_structure_types.structure_type%TYPE
1040 ,x_request_id OUT NOCOPY pa_proj_elem_ver_structure.conc_request_id%TYPE --File.Sql.39 bug 4440895
1041 ,x_process_code OUT NOCOPY pa_proj_elem_ver_structure.process_code%TYPE --File.Sql.39 bug 4440895
1042 ,x_structure_version_id OUT NOCOPY pa_proj_elem_ver_structure.element_version_id%TYPE --File.Sql.39 bug 4440895
1043 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1044 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1045 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1046 );
1047
1048 --Bug 3010538
1049
1050 --Below function is added for bug#3049157
1051 FUNCTION GET_CONC_REQUEST_ID(
1052 p_project_id IN pa_projects_all.project_id%TYPE
1053 ,p_structure_type IN pa_structure_types.structure_type%TYPE
1054 )
1055 return NUMBER;
1056
1057 FUNCTION GET_STRUCT_CONC_ID(
1058 p_structure_version_id IN pa_proj_element_versions.parent_structure_version_id%TYPE
1059 ,p_project_id IN pa_projects_all.project_id%TYPE -- Included for Perf. Bug fix 3968091
1060 )
1061 return NUMBER ;
1062
1066 -- Return Value : Check if deliverable is enabled
1063 -- API name : CHECK_DELIVERABLE_ENABLED
1064 -- Type : Utils API
1065 -- Pre-reqs : None
1067 --
1068 -- Parameters
1069 -- p_project_id IN NUMBER
1070 --
1071 -- History
1072 --
1073 -- 17-Dec-03 Bhumesh K. -Created
1074 -- This is added for FP_M changes
1075
1076 FUNCTION check_Deliverable_enabled (
1077 p_project_id IN NUMBER
1078 ) Return VARCHAR2;
1079
1080 -- API name : get_current_working_ver_id
1081 -- Type : Utils API
1082 -- Pre-reqs : None
1083 -- Return Value :get the WP current working version
1084 --
1085 -- Parameters
1086 -- p_project_id IN NUMBER
1087 --
1088 -- History
1089 --
1090 -- 17-Dec-03 maansari -Created
1091 -- This is added for FP_M changes
1092 --FPM changes bug 3301192
1093
1094 FUNCTION get_current_working_ver_id( p_project_id NUMBER
1095 )
1096 RETURN NUMBER;
1097
1098 --------------------------------------------------------------------------
1099 -- API name : Check_Struct_Has_Dep
1100 -- Type : Utils API
1101 -- Pre-reqs : None
1102 -- Return Value : Check the dependency of a structure version ID
1103 --
1104 -- Parameters
1105 -- P_Version_ID IN NUMBER
1106 --
1107 -- History
1108 --
1109 -- 6-Jan-04 Bhumesh K. -Created
1110 -- This is added for FP_M changes. Refer to tracking bug 3305199 for more details
1111
1112 FUNCTION Check_Struct_Has_Dep (
1113 P_Version_ID IN NUMBER
1114 )
1115 RETURN VARCHAR2;
1116
1117 FUNCTION get_Structure_sharing_code(
1118 p_project_id IN NUMBER
1119 )
1120 RETURN VARCHAR2;
1121
1122 FUNCTION check_third_party_sch_flag(
1123 p_project_id IN NUMBER
1124 )
1125 RETURN VARCHAR2;
1126
1127 FUNCTION check_dep_on_summary_tk_ok(
1128 p_project_id IN NUMBER
1129 )
1130 RETURN VARCHAR2;
1131
1132 FUNCTION GET_LAST_UPD_WORK_VER_OLD(
1133 p_structure_id IN NUMBER
1134 ) return NUMBER;
1135
1136 FUNCTION GET_STRUCT_VER_UPDATE_FLAG(
1137 p_structure_version_id NUMBER
1138 ) return VARCHAR2;
1139
1140 -- API name : Get_Baseline_Struct_Ver
1141 -- Type : Utils API
1142 -- Pre-reqs : None
1143 -- Return Value : Get the baseline structure version
1144 -- id. Return structure version id. Return
1145 -- NULL if no baseline version exists.
1146 --
1147 -- Parameters
1148 -- p_project_id IN NUMBER
1149 --
1150 -- History
1151 --
1152 -- 16-Mar-04 SMUKKA -Created
1153 --
1154 FUNCTION Get_Baseline_Struct_Ver
1155 ( p_project_id IN NUMBER
1156 ) return NUMBER;
1157 --
1158 --
1159 -- API name : Get_Sch_Dirty_fl
1160 -- Type : Utils API
1161 -- Pre-reqs : None
1162 -- Return Value : Get the Schedule_diryt_flag for the given sturcture version
1163 -- id and project_id. Return schedule_dirty_flag.Return
1164 -- NULL if no sturcture_version exists
1165 --
1166 -- Parameters
1167 -- p_project_id IN NUMBER
1168 -- p_structure_version_id IN NUMBER
1169 --
1170 -- History
1171 --
1172 -- 16-Mar-04 SMUKKA -Created
1173 --
1174 FUNCTION Get_Sch_Dirty_fl
1175 (
1176 p_project_id IN NUMBER
1177 ,p_structure_version_id IN NUMBER
1178 ) RETURN VARCHAR2;
1179 --
1180 --
1181 -- API name : Check_Subproject_Exists
1182 -- Type : Utils API
1183 -- Pre-reqs : None
1184 -- Return Value : This API check if there is subproject association for the given
1185 -- sturcture version id and project_id. Return Y if there is subproject
1186 -- association or N if there is subproject association
1187 --
1188 -- Parameters
1189 -- p_project_id IN NUMBER
1190 -- p_structure_ver_id IN NUMBER
1191 --
1192 -- History
1193 --
1194 -- 29-Mar-04 SMUKKA -Created
1195 --
1196 FUNCTION Check_Subproject_Exists
1197 (
1198 p_project_id NUMBER,
1199 p_structure_ver_id NUMBER
1200 ,p_link_type VARCHAR2 default 'SHARED' --bug 4541039
1201 )RETURN VARCHAR2;
1202 --
1203 --
1204 -- API name : Check_Structure_Ver_Exists
1205 -- Type : Utils API
1206 -- Pre-reqs : None
1207 -- Return Value : This API check if there is structure version is valid exists for the
1208 -- given sturcture version id and project_id. Return Y if there is structure version
1209 -- or N if there is no structure version.
1210 --
1211 -- Parameters
1212 -- p_project_id IN NUMBER
1213 -- p_structure_ver_id IN NUMBER
1214 --
1215 -- History
1216 --
1217 -- 16-JUL-04 SMUKKA -Created
1218 --
1219 FUNCTION Check_Structure_Ver_Exists
1220 (
1221 p_project_id NUMBER,
1222 p_structure_ver_id NUMBER
1226 Function Check_Project_exists(p_project_id IN NUMBER)
1223 )RETURN VARCHAR2;
1224 --
1225 --
1227 RETURN VARCHAR2;
1228 --
1229 --
1230 -- Begin fix for Bug # 4373055.
1231
1232 PROCEDURE GET_PROCESS_STATUS_MSG(
1233 p_project_id IN pa_projects_all.project_id%TYPE
1234 , p_structure_type IN pa_structure_types.structure_type%TYPE := NULL
1235 , p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE := NULL
1236 , p_context IN VARCHAR2 := NULL
1237 , x_message_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1238 , x_message_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1239 , x_structure_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1240 , x_conc_request_id OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
1241
1242 PROCEDURE SET_PROCESS_CODE_IN_PROC(
1243 p_project_id IN NUMBER
1244 , p_structure_version_id IN NUMBER
1245 , p_calling_context IN VARCHAR2
1246 , p_conc_request_id IN NUMBER
1247 , x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
1248
1249 PROCEDURE SET_PROCESS_CODE_ERR(
1250 p_project_id IN NUMBER
1251 , p_structure_version_id IN NUMBER
1252 , p_calling_context IN VARCHAR2
1253 , p_conc_request_id IN NUMBER
1254 , x_return_status OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
1255
1256 -- End fix for Bug # 4373055.
1257
1258 -- Begin fix for Bug#5659575
1259
1260 PROCEDURE SET_PROCESS_CODE(
1261 p_project_id IN NUMBER
1262 , p_structure_version_id IN NUMBER
1263 , p_process_code IN VARCHAR2
1264 , p_conc_request_id IN NUMBER
1265 , x_return_status OUT NOCOPY VARCHAR2);
1266
1267 -- End fix for Bug#5659575
1268
1269 -- Begin fix for Bug # 4502325.
1270
1271 procedure get_structure_msg(p_project_id IN NUMBER
1272 , p_structure_type IN VARCHAR2
1273 , p_structure_version_id IN NUMBER
1274 , p_context IN VARCHAR2 := NULL
1275 , x_message_name OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1276 , x_message_type OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1277 , x_structure_version_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1278 , x_conc_request_id OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
1279
1280 -- End fix for Bug # 4502325.
1281
1282 -- Begin Bug # 4582750.
1283
1284 procedure lock_unlock_wp_str_autonomous
1285 (p_project_id IN NUMBER
1286 ,p_structure_version_id IN NUMBER
1287 ,p_lock_status_code IN VARCHAR2 := 'LOCKED'
1288 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1289 ,x_return_status OUT NOCOPY VARCHAR2
1290 ,x_msg_count OUT NOCOPY NUMBER
1291 ,x_msg_data OUT NOCOPY VARCHAR2);
1292
1293 procedure lock_unlock_wp_str
1294 (p_project_id IN NUMBER
1295 ,p_structure_version_id IN NUMBER
1296 ,p_lock_status_code IN VARCHAR2 := 'LOCKED'
1297 ,p_calling_module IN VARCHAR2 := 'SELF_SERVICE'
1298 ,x_return_status OUT NOCOPY VARCHAR2
1299 ,x_msg_count OUT NOCOPY NUMBER
1300 ,x_msg_data OUT NOCOPY VARCHAR2);
1301
1302 -- End Bug # 4582750.
1303
1304 -- bug 4597323
1305 -- API name : check_program_flag_enable
1306 -- Type : Utils API
1307 -- Pre-reqs : None
1308 -- Return Value : This API returns sys_program_flag from pa_projects_all table for a given project.
1309 -- It's created to use from Projects Form.
1310 --
1311 -- Parameters
1312 -- p_project_id IN NUMBER
1313 --
1314 -- History
1315 --
1316 -- 08-SEP-05 maansari -Created
1317 --
1318 FUNCTION check_program_flag_enable(
1319 p_project_id IN NUMBER
1320 ) RETURN VARCHAR2;
1321
1322 -- API name : check_del_pub_struc_ver_ok
1323 -- Tracking Bug : 4925192
1324 -- Type : Utils API
1325 -- Pre-reqs : None
1326 -- Return Value : Check if ok to delete Published structure
1327 -- version
1328 -- Return Y or N
1329 --
1330 -- Parameters
1331 -- p_structure_version_id IN NUMBER
1332 --
1333 -- History
1334 --
1335 -- 20-OCT-06 Ram Namburi -Created
1336 --
1337 -- Purpose:
1338 -- This API will determine whether a published structure version can be deleted or not.
1339 --
1340 -- Business Rules:
1341 --
1342 -- The published version cannot be deleted if
1343 --
1344 -- 1. It is the current baseline version - because the metrics are calculated using
1345 -- the baselined values from the current baselined workplan structure version
1346 -- 2. It is the latest published version - because the PJI module uses the
1347 -- financial plan to rollup data on the latest published workplan structure version
1348 -- 3. It is a part of a program - because it is technically challenging to handle
1349 -- the deletion of published workplan structure versions that belong to the
1350 -- program itself or an intermediate sub-project in a program hierarchy
1351
1352
1353
1354 FUNCTION check_del_pub_struc_ver_ok
1358
1355 ( p_structure_version_id IN NUMBER
1356 ,p_project_id IN NUMBER
1357 ) return VARCHAR2;
1359
1360 -- bug 5183704
1361 -- API name : check_pending_link_changes
1362 -- Type : Utils API
1363 -- Pre-reqs : None
1364 -- Return Value : This API returns "Y" if pending link changes exist for a given project as per
1365 -- log type PRG_CHANGE in the pa_pji_proj_events_log, pji_pa_proj_events_log tables
1366 -- It's created to use from Projects Self Services (sql from vijay r).
1367 --
1368 -- Parameters
1369 -- p_project_id IN NUMBER
1370 --
1371 -- History
1372 --
1373 -- 09-MAY-06 sliburd -Created
1374 --
1375 FUNCTION CHECK_PENDING_LINK_CHANGES(
1376 p_project_id IN NUMBER
1377 ,p_version_id IN NUMBER --Added for bug 8889029
1378 ) RETURN VARCHAR2;
1379
1380 -- bug 8889029
1381 FUNCTION CHECK_UPPD_RUNNING(
1382 p_project_id IN NUMBER
1383 ,p_version_id IN NUMBER
1384 ) RETURN VARCHAR2;
1385
1386 -- bug 8889029
1387 FUNCTION GET_ROLLUP_PROFILE_VAL(
1388 lookup_code IN VARCHAR2
1389 ) RETURN VARCHAR2;
1390
1391
1392 -- API name : check_exp_item_dates
1393 -- Type : Utils API
1394 -- Pre-reqs : None
1395 -- Return Value : Validate all the lowest level task dates against
1396 -- the expenditure item dates
1397 --
1398 -- Parameters
1399 -- p_project_id IN NUMBER
1400 -- p_structure_version_id IN NUMBER
1401 --
1402 procedure check_exp_item_dates (
1403 p_project_id IN NUMBER
1404 ,p_structure_version_id IN NUMBER
1405 ,x_return_status OUT NOCOPY VARCHAR2
1406 ,x_msg_count OUT NOCOPY NUMBER
1407 ,x_msg_data OUT NOCOPY VARCHAR2
1408 );
1409 -----------------------------------------------------------------------
1410 -- API name : get_resource_class_flag
1411 -- Type : Utils API
1412 -- Pre-reqs : None
1413 -- Return Value : Resource class flag
1414 --
1415 -- Parameters
1416 -- p_project_id IN NUMBER
1417 --
1418 -- History
1419 --
1420 -- 10-Jan-12 Amit Sharma -Created
1421 -- This is added for CBS changes
1422 -----------------------------------------------------------------------
1423 FUNCTION get_resource_class_flag
1424 (p_project_id IN NUMBER)
1425 RETURN VARCHAR2;
1426
1427 end PA_PROJECT_STRUCTURE_UTILS;
[Home] [Help]