1 package body PA_PROJ_TSK_UTILS as
2 -- $Header: PAXPTUTB.pls 120.9.12020000.3 2013/04/02 07:11:05 speddi ship $
3
4
5 --
6 -- FUNCTION
7 -- get_task_project_id
8 -- PURPOSE
9 -- This function retrieves the project id of a task.
10 -- If no project id is found, null is returned.
11 -- If Oracle error occurs, Oracle error number is returned.
12 -- HISTORY
13 -- 20-OCT-95 R. Chiu Created
14 --
15 function get_task_project_id (x_task_id IN number) return number
16 is
17 cursor c1 is
18 select project_id
19 from pa_tasks
20 where task_id = x_task_id;
21
22 c1_rec c1%rowtype;
23
24 begin
25 open c1;
26 fetch c1 into c1_rec;
27 if c1%notfound then
28 close c1;
29 return( null);
30 else
31 close c1;
32 return( c1_rec.project_id );
33 end if;
34
35 exception
36 when others then
37 return(SQLCODE);
38
39 end get_task_project_id;
40
41
42 -- FUNCTION
43 -- check_event_exists
44 -- PURPOSE
45 -- This function returns 1 if event exists for project id or
46 -- task id and returns 0 if no event is found.
47 --
48 -- User can pass either project id or task id. If both
49 -- project id and task id are provided, function treated
50 -- as if only task were passed. Event can exist at project and
51 -- top tasks level.
52 -- If Oracle error occured, Oracle error code is returned.
53 --
54 -- HISTORY
55 -- 20-OCT-95 R. Chiu Created
56 --
57 function check_event_exists (x_project_id IN number
58 , x_task_id IN number ) return number
59 is
60 x_proj_id number;
61
62 cursor c1 is
63 select 1
64 from sys.dual
65 where exists (select event_num
66 from pa_events
67 where project_id = x_proj_id
68 and (x_task_id is null or
69 task_id = x_task_id));
70
71 c1_rec c1%rowtype;
72
73 begin
74 if (x_task_id is null and x_project_id is null) then
75 return(null);
76 end if;
77
78 if (x_task_id is not null ) then
79 x_proj_id := get_task_project_id(x_task_id);
80 else
81 x_proj_id := x_project_id;
82 end if;
83
84 open c1;
85 fetch c1 into c1_rec;
86 if c1%notfound then
87 close c1;
88 return(0);
89 else
90 close c1;
91 return(1);
92 end if;
93
94 exception
95 when others then
96 return (SQLCODE);
97 end check_event_exists;
98
99
100 -- FUNCTION
101 -- check_exp_item_exists
102 -- PURPOSE
103 -- This function returns 1 if expenditure item exists for
104 -- a project or a task and returns 0 if no expenditure item
105 -- is found.
106 --
107 -- User can pass either project id or task id. If both
108 -- project id and task id are provided, function treated
109 -- as if only task were passed. Expenditure items exist
110 -- at lowest level tasks.
111 -- If Oracle error occured, Oracle error code is returned.
112 --
113 -- HISTORY
114 -- 09-FEB-99 Ri.Singh Modified.
115 -- 1. Removed comments in cursor c2
116 -- done during bugfix 773604. CONNECT BY
117 -- clause is required for task deletion
118 -- 2. Removed join to PA_TASKS in cursor c3
119 -- as task_id is a not null column in
120 -- pa_expenditures_all
121 -- 3. Added reference to pa_ei_denorm for
122 -- checking if expenditures exist
123 --
124 -- 01-JAN-97 T. Saifee Modified
125 -- Condition project id = null incorporated into the task id = null
126 -- body. Bug 434421.
127 -- 20-OCT-95 R. Chiu Created
128 --
129 function check_exp_item_exists (x_project_id IN number
130 , x_task_id IN number
131 , x_check_subtasks IN boolean default TRUE)
132 return number
133 is
134 cursor c1 is
135 SELECT 1
136 FROM sys.dual
137 WHERE EXISTS (SELECT NULL
138 FROM pa_expenditure_items_all i
139 WHERE i.project_id = x_project_id)
140 OR EXISTS
141 (SELECT NULL
142 FROM pa_ei_denorm e
143 WHERE e.project_id = x_project_id);
144
145 cursor c2 is
146 SELECT 1
147 FROM sys.dual
148 where exists (SELECT NULL
149 FROM PA_EXPENDITURE_ITEMS_all
150 WHERE TASK_ID IN
151 (SELECT TASK_ID
152 FROM PA_TASKS
153 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
154 START WITH TASK_ID = x_TASK_ID))
155 or exists (SELECT NULL
156 FROM PA_EI_DENORM
157 WHERE TASK_ID IN
158 (SELECT TASK_ID
159 FROM PA_TASKS
160 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
161 START WITH TASK_ID = x_TASK_ID));
162 cursor c3 is
163 SELECT 1
164 FROM sys.dual
165 where exists (SELECT NULL
166 FROM PA_EXPENDITURE_ITEMS_all
167 WHERE TASK_ID = x_TASK_ID)
168 or exists (SELECT NULL
169 FROM PA_EI_DENORM
170 WHERE TASK_ID = x_TASK_ID);
171
172 /* cursor c2 is
173 SELECT 1
174 FROM sys.dual
175 where exists (SELECT NULL
176 FROM PA_EXPENDITURE_ITEMS_all
177 WHERE TASK_ID IN
178 (SELECT TASK_ID
179 FROM PA_TASKS
180 WHERE TASK_ID = x_TASK_ID));
181
182 cursor c3 is
183 SELECT 1
184 FROM sys.dual
185 where exists (SELECT NULL
186 FROM PA_EXPENDITURE_ITEMS_all
187 WHERE TASK_ID IN
188 (SELECT TASK_ID
189 FROM PA_TASKS
190 WHERE TASK_ID = x_TASK_ID));
191 */
192 c1_rec c1%rowtype;
193 c2_rec c2%rowtype;
194 c3_rec c3%rowtype;
195
196 begin
197 if (x_task_id is null) then
198 if (x_project_id is null) then
199 return (null);
200 end if;
201 open c1;
202 fetch c1 into c1_rec;
203 if c1%notfound then
204 close c1;
205 return(0);
206 else
207 close c1;
208 return(1);
209 end if;
210 elsif (x_check_subtasks) then
211 open c2;
212 fetch c2 into c2_rec;
213 if c2%notfound then
214 close c2;
215 return(0);
216 else
217 close c2;
218 return(1);
219 end if;
220 else
221 open c3;
222 fetch c3 into c3_rec;
223 if c3%notfound then
224 close c3;
225 return(0);
226 else
227 close c3;
228 return(1);
229 end if;
230 end if;
231
232 exception
233 when others then
234 return(SQLCODE);
235 end check_exp_item_exists;
236
237
238 -- FUNCTION
239 -- check_po_dist_exists
240 -- PURPOSE
241 -- This function returns 1 if purchase order distribution exists
242 -- for a project or a task and returns 0 if no purchase order
243 -- distribution is found.
244 --
245 -- User can pass either project id or task id. If both
246 -- project id and task id are provided, function treated
247 -- as if only task were passed. Purchase order exists
248 -- at lowest level tasks.
249 -- If Oracle error occured, Oracle error code is returned.
250 --
251 -- HISTORY
252 -- 28-JUN-07 prabsing added encumbered_flag to where clause of c1, c2
253 -- 09-FEB-99 Ri.Singh Modified.
254 -- Removed comments in cursor c2
255 -- done during bugfix 773604. CONNECT BY
256 -- clause is required for task deletion
257 -- 20-OCT-95 R. Chiu Created
258 --
259 function check_po_dist_exists (x_project_id IN number
260 , x_task_id IN number
261 , x_check_subtasks IN boolean default TRUE) -- Added for Performance Fix 4903460
262 return number
263 is
264 x_proj_id number;
265
266 cursor c1 is
267 SELECT 1
268 FROM sys.dual
269 WHERE EXISTS (SELECT NULL
270 FROM po_distributions_all
271 WHERE project_id = x_project_id
272 AND nvl(encumbered_flag,'N') = 'Y'); -- Bug 6153950: added encumbered_flag
273
274 cursor c2 is
275 SELECT 1
276 FROM sys.dual
277 where exists (SELECT NULL
278 FROM po_distributions_all
279 where project_id = x_proj_id
280 AND nvl(encumbered_flag,'N') = 'Y' -- Bug 6153950: added encumbered_flag
281 AND TASK_ID IN
282 (SELECT TASK_ID
283 FROM PA_TASKS
284 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
285 START WITH TASK_ID = x_TASK_ID));
286
287 /* cursor c2 is
288 SELECT 1
289 FROM sys.dual
290 where exists (SELECT NULL
291 FROM po_distributions_all
292 where project_id = x_proj_id
293 AND TASK_ID IN
294 (SELECT TASK_ID
295 FROM PA_TASKS
296 WHERE TASK_ID = x_TASK_ID));
297 */
298 c1_rec c1%rowtype;
299 c2_rec c2%rowtype;
300
301 -- New cursor c3 added for Performance Fix 4903460
302 cursor c3 is
303 SELECT 1
304 FROM sys.dual
305 where exists (SELECT NULL
306 FROM po_distributions_all
307 where project_id = x_proj_id
308 AND TASK_ID = x_TASK_ID);
309 c3_rec c3%rowtype;
310 begin
311 if (x_project_id is null and x_task_id is null) then
312 return(null);
313 end if;
314
315 if (x_task_id is not null) then
316 if x_project_id IS NULL THEN -- 4903460
317 x_proj_id := get_task_project_id(x_task_id);
318 else
319 x_proj_id := x_project_id;
320 END IF;
321 end if;
322
323 if (x_task_id is null) then
324 open c1;
325 fetch c1 into c1_rec;
326 if c1%notfound then
327 close c1;
328 return(0);
329 else
330 close c1;
331 return(1);
332 end if;
333 -- Performance Fix 4903460 : By default this flag is TRUE
334 elsif (x_check_subtasks) then
335 open c2;
336 fetch c2 into c2_rec;
337 if c2%notfound then
338 close c2;
339 return(0);
340 else
341 close c2;
342 return(1);
343 end if;
344 else -- Newly introduced for Performance Fix 4903460
345 open c3;
346 fetch c3 into c3_rec;
347 if c3%notfound then
348 close c3;
349 return(0);
350 else
351 close c3;
352 return(1);
353 end if;
354 end if;
355
356 exception
357 when others then
358 return(SQLCODE);
359 end check_po_dist_exists;
360
361
362 -- FUNCTION
363 -- check_po_req_dist_exists
364 -- PURPOSE
365 -- This function returns 1 if purchase requisition exists
366 -- for a project or a task and returns 0 if no purchase
367 -- requisition is found.
368 --
369 -- User can pass either project id or task id. If both
370 -- project id and task id are provided, function treated
371 -- as if only task were passed. Purchase requisition exists
372 -- at lowest level tasks.
373 -- If Oracle error occured, Oracle error code is returned.
374 --
375 -- HISTORY
376 -- 28-JUN-07 prabsing added encumbered_flag to where clause of c1, c2
377 -- 09-FEB-99 Ri.Singh Modified.
378 -- Removed comments in cursor c2
379 -- done during bugfix 773604. CONNECT BY
380 -- clause is required for task deletion
381 -- 20-OCT-95 R. Chiu Created
382 --
383 function check_po_req_dist_exists (x_project_id IN number
384 , x_task_id IN number
385 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
386 ) return number
387 is
388 x_proj_id number;
389
390 cursor c1 is
391 SELECT 1
392 FROM sys.dual
393 WHERE EXISTS (SELECT NULL
394 FROM po_req_distributions_all
395 WHERE project_id = x_project_id
396 AND nvl(encumbered_flag,'N') = 'Y'); -- Bug 6153950: added encumbered_flag
397
398 cursor c2 is
399 SELECT 1
400 FROM sys.dual
401 where exists (SELECT NULL
402 FROM po_req_distributions_all
403 where project_id = x_proj_id
404 AND nvl(encumbered_flag,'N') = 'Y' -- Bug 6153950: added encumbered_flag
405 AND TASK_ID IN
406 (SELECT TASK_ID
407 FROM PA_TASKS
408 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
409 START WITH TASK_ID = x_TASK_ID));
410
411 /* cursor c2 is
412 SELECT 1
413 FROM sys.dual
414 where exists (SELECT NULL
415 FROM po_req_distributions_all
416 where project_id = x_proj_id
417 AND TASK_ID IN
418 (SELECT TASK_ID
419 FROM PA_TASKS
420 WHERE TASK_ID = x_TASK_ID));
421 */
422 c1_rec c1%rowtype;
423 c2_rec c2%rowtype;
424
425 -- New cursor c3 added for Performance Fix 4903460
426 cursor c3 is
427 SELECT 1
428 FROM sys.dual
429 where exists (SELECT NULL
430 FROM po_req_distributions_all
431 where project_id = x_proj_id
432 AND TASK_ID = x_TASK_ID);
433 c3_rec c3%rowtype;
434 begin
435 if (x_project_id is null and x_task_id is null) then
436 return(null);
437 end if;
438
439 if (x_task_id is not null) then
440 if x_project_id IS NULL THEN -- 4903460
441 x_proj_id := get_task_project_id(x_task_id);
442 else
443 x_proj_id := x_project_id;
444 END IF;
445 end if;
446
447 if (x_task_id is null) then
448 open c1;
449 fetch c1 into c1_rec;
450 if c1%notfound then
451 close c1;
452 return(0);
453 else
454 close c1;
455 return(1);
456 end if;
457 -- Performance Fix 4903460 : By default this flag is TRUE
458 elsif(x_check_subtasks) then
459 open c2;
460 fetch c2 into c2_rec;
461 if c2%notfound then
462 close c2;
463 return(0);
464 else
465 close c2;
466 return(1);
467 end if;
468 else -- Newly introduced for Performance Fix 4903460
469 open c3;
470 fetch c3 into c3_rec;
471 if c3%notfound then
472 close c3;
473 return(0);
474 else
475 close c3;
476 return(1);
477 end if;
478 end if;
479
480 exception
481 when others then
482 return(SQLCODE);
483 end check_po_req_dist_exists;
484
485
486 -- FUNCTION
487 -- check_ap_invoice_exists
488 -- PURPOSE
489 -- This function returns 1 if supplier invoice exists
490 -- for a project or a task and returns 0 if no supplier
491 -- invoice is found.
492 --
493 -- User can pass either project id or task id. If both
494 -- project id and task id are provided, function treated
495 -- as if only task were passed. Supplier invoice exists
496 -- at lowest level tasks.
497 -- If Oracle error occured, Oracle error code is returned.
498 --
499 -- HISTORY
500 -- 09-FEB-99 Ri.Singh Modified.
501 -- Removed comments in cursor c2
502 -- done during bugfix 773604. CONNECT BY
503 -- clause is required for task deletion
504 -- 20-OCT-95 R. Chiu Created
505 --
506 function check_ap_invoice_exists (x_project_id IN number
507 , x_task_id IN number
508 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
509 ) return number
510 is
511 x_proj_id number;
512
513 cursor c1 is
514 SELECT 1
515 FROM sys.dual
516 WHERE EXISTS (SELECT NULL
517 FROM ap_invoices_all
518 WHERE project_id = x_project_id);
519
520 cursor c2 is
521 SELECT 1
522 FROM sys.dual
523 where exists (SELECT NULL
524 FROM ap_invoices_all
525 where project_id = x_proj_id
526 AND TASK_ID IN
527 (SELECT TASK_ID
528 FROM PA_TASKS
529 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
530 START WITH TASK_ID = x_TASK_ID));
531
532 /* cursor c2 is
533 SELECT 1
534 FROM sys.dual
535 where exists (SELECT NULL
536 FROM ap_invoices_all
537 where project_id = x_proj_id
538 AND TASK_ID IN
539 (SELECT TASK_ID
540 FROM PA_TASKS
541 WHERE TASK_ID = x_TASK_ID));
542 */
543 c1_rec c1%rowtype;
544 c2_rec c2%rowtype;
545
546 -- New cursor c3 added for Performance Fix 4903460
547 cursor c3 is
548 SELECT 1
549 FROM sys.dual
550 where exists (SELECT NULL
551 FROM ap_invoices_all
552 where project_id = x_proj_id
553 AND TASK_ID = x_task_id);
554 c3_rec c3%rowtype;
555
556 begin
557 if (x_project_id is null and x_task_id is null) then
558 return(null);
559 end if;
560
561 if (x_task_id is not null) then
562 if x_project_id IS NULL THEN -- 4903460
563 x_proj_id := get_task_project_id(x_task_id);
564 else
565 x_proj_id := x_project_id;
566 END IF;
567 end if;
568
569 if (x_task_id is null) then
570 open c1;
571 fetch c1 into c1_rec;
572 if c1%notfound then
573 close c1;
574 return(0);
575 else
576 close c1;
577 return(1);
578 end if;
579 -- Performance Fix 4903460 : By default this flag is TRUE
580 elsif(x_check_subtasks) then
581 open c2;
582 fetch c2 into c2_rec;
583 if c2%notfound then
584 close c2;
585 return(0);
586 else
587 close c2;
588 return(1);
589 end if;
590 else -- Newly introduced for Performance Fix 4903460
591 open c3;
592 fetch c3 into c3_rec;
593 if c3%notfound then
594 close c3;
595 return(0);
596 else
597 close c3;
598 return(1);
599 end if;
600 end if;
601
602 exception
603 when others then
604 return(SQLCODE);
605 end check_ap_invoice_exists;
606
607
608 -- FUNCTION
609 -- check_ap_inv_dist_exists
610 -- PURPOSE
611 -- This function returns 1 if supplier invoice distribution
612 -- exists for a project or a task and returns 0 if no supplier
613 -- invoice distribution is found.
614 --
615 -- User can pass either project id or task id. If both
616 -- project id and task id are provided, function treated
617 -- as if only task were passed. Supplier invoice distribution
618 -- exists at lowest level tasks.
619 -- If Oracle error occured, Oracle error code is returned.
620 --
621 -- HISTORY
622 -- 28-JUN-07 prabsing added encumbered_flag to where clause of c1, c2
623 -- 09-FEB-99 Ri.Singh Modified.
624 -- Removed comments in cursor c2
625 -- done during bugfix 773604. CONNECT BY
626 -- clause is required for task deletion
627 -- 20-OCT-95 R. Chiu Created
628 --
629 function check_ap_inv_dist_exists (x_project_id IN number
630 , x_task_id IN number
631 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
632 ) return number
633 is
634 x_proj_id number;
635
636 cursor c1 is
637 SELECT 1
638 FROM sys.dual
639 WHERE EXISTS (SELECT NULL
640 FROM ap_invoice_distributions_all
641 WHERE project_id = x_project_id
642 AND nvl(encumbered_flag,'N') = 'Y'); -- Bug 6153950: added encumbered_flag
643
644 cursor c2 is
645 SELECT 1
646 FROM sys.dual
647 where exists (SELECT NULL
648 FROM ap_invoice_distributions_all
649 where project_id = x_proj_id
650 AND nvl(encumbered_flag,'N') = 'Y' -- Bug 6153950: added encumbered_flag
651 AND TASK_ID IN
652 (SELECT TASK_ID
653 FROM PA_TASKS
654 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
655 START WITH TASK_ID = x_TASK_ID));
656
657 /* cursor c2 is
658 SELECT 1
659 FROM sys.dual
660 where exists (SELECT NULL
661 FROM ap_invoice_distributions_all
662 where project_id = x_proj_id
663 AND TASK_ID IN
664 (SELECT TASK_ID
665 FROM PA_TASKS
666 WHERE TASK_ID = x_TASK_ID));
667 */
668 c1_rec c1%rowtype;
669 c2_rec c2%rowtype;
670
671 -- New cursor c3 added for Performance Fix 4903460
672 cursor c3 is
673 SELECT 1
674 FROM sys.dual
675 where exists (SELECT NULL
676 FROM ap_invoice_distributions_all
677 where project_id = x_proj_id
678 AND TASK_ID = x_TASK_ID);
679 c3_rec c3%rowtype;
680
681 begin
682 if (x_project_id is null and x_task_id is null) then
683 return(null);
684 end if;
685
686 if (x_task_id is not null) then
687 if x_project_id IS NULL THEN -- 4903460
688 x_proj_id := get_task_project_id(x_task_id);
689 else
690 x_proj_id := x_project_id;
691 END IF;
692 end if;
693
694 if (x_task_id is null) then
695 open c1;
696 fetch c1 into c1_rec;
697 if c1%notfound then
698 close c1;
699 return(0);
700 else
701 close c1;
702 return(1);
703 end if;
704 -- Performance Fix 4903460 : By default this flag is TRUE
705 elsif(x_check_subtasks) then
706 open c2;
707 fetch c2 into c2_rec;
708 if c2%notfound then
709 close c2;
710 return(0);
711 else
712 close c2;
713 return(1);
714 end if;
715 else -- Newly introduced for Performance Fix 4903460
716 open c3;
717 fetch c3 into c3_rec;
718 if c3%notfound then
719 close c3;
720 return(0);
721 else
722 close c3;
723 return(1);
724 end if;
725 end if;
726
727 exception
728 when others then
729 return(SQLCODE);
730 end check_ap_inv_dist_exists;
731
732
733 -- FUNCTION
734 -- check_funding_exists
735 -- PURPOSE
736 -- This function returns 1 if funding exists for a project
737 -- or a task and returns 0 if no funding is found.
738 --
739 -- User can pass either project id or task id. If both
740 -- project id and task id are provided, function treated
741 -- as if only task were passed. Funding can exist at project
742 -- and top task levels.
743 -- If Oracle error occured, Oracle error code is returned.
744 --
745 -- HISTORY
746 -- 20-OCT-95 R. Chiu Created
747 --
748 function check_funding_exists (x_project_id IN number
749 , x_task_id IN number ) return number
750 is
751 x_proj_id number;
752
753 cursor c1 is
754 SELECT 1
755 FROM sys.dual
756 WHERE EXISTS (SELECT NULL
757 FROM pa_project_fundings
758 WHERE project_id = x_project_id);
759
760 cursor c2 is
761 SELECT 1
762 FROM sys.dual
763 where exists (SELECT NULL
764 FROM pa_project_fundings
765 where project_id = x_proj_id
766 AND TASK_ID = x_task_id);
767
768 c1_rec c1%rowtype;
769 c2_rec c2%rowtype;
770
771 begin
772 if (x_project_id is null and x_task_id is null) then
773 return(null);
774 end if;
775
776 if (x_task_id is not null) then
777 if x_project_id IS NULL THEN -- 4903460
778 x_proj_id := get_task_project_id(x_task_id);
779 else
780 x_proj_id := x_project_id;
781 END IF;
782 end if;
783
784 if (x_task_id is null) then
785 open c1;
786 fetch c1 into c1_rec;
787 if c1%notfound then
788 close c1;
789 return(0);
790 else
791 close c1;
792 return(1);
793 end if;
794
795 else
796 open c2;
797 fetch c2 into c2_rec;
798 if c2%notfound then
799 close c2;
800 return(0);
801 else
802 close c2;
803 return(1);
804 end if;
805 end if;
806
807 exception
808 when others then
809 return(SQLCODE);
810 end check_funding_exists;
811
812
813 -- FUNCTION
814 -- check_cdl_exists
815 -- PURPOSE
816 -- This function returns 1 if cost distribution lines exists
817 -- for a specified project or task and returns 0 if no
818 -- cost distribution line is found.
819 --
820 -- User can pass either project id or task id. If both
821 -- project id and task id are provided, function treated
822 -- as if only task were passed.
823 -- If Oracle error occured, Oracle error code is returned.
824 --
825 -- HISTORY
826 -- 20-OCT-95 R. Chiu Created
827 --
828 function check_cdl_exists (x_project_id IN number
829 , x_task_id IN number ) return number
830 is
831 cursor c1 is
832 select 1
833 from sys.dual
834 where exists (SELECT NULL
835 FROM PA_EXPENDITURE_ITEMS_all PAI,
836 PA_COST_DISTRIBUTION_LINES_all PCD
837 WHERE PAI.PROJECT_ID = x_PROJECT_ID
838 AND PAI.EXPENDITURE_ITEM_ID = PCD.EXPENDITURE_ITEM_ID); -- Bug 3461664
839
840
841 cursor c2 is
842 select 1
843 from sys.dual
844 where exists (SELECT NULL
845 FROM PA_EXPENDITURE_ITEMS_all PAI,
846 PA_COST_DISTRIBUTION_LINES_all PCD
847 WHERE PAI.EXPENDITURE_ITEM_ID
848 = PCD.EXPENDITURE_ITEM_ID
849 AND PAI.TASK_ID = x_TASK_ID);
850
851 c1_rec c1%rowtype;
852 c2_rec c2%rowtype;
853
854 begin
855 if (x_project_id is null and x_task_id is null) then
856 return(null);
857 end if;
858
859 if (x_task_id is null) then
860 open c1;
861 fetch c1 into c1_rec;
862 if c1%notfound then
863 close c1;
864 return(0);
865 else
866 close c1;
867 return(1);
868 end if;
869 else
870 open c2;
871 fetch c2 into c2_rec;
872 if c2%notfound then
873 close c2;
874 return(0);
875 else
876 close c2;
877 return(1);
878 end if;
879 end if;
880
881 exception
882 when others then
883 return(SQLCODE);
884 end check_cdl_exists;
885
886
887 -- FUNCTION
888 -- check_rdl_exists
889 -- PURPOSE
890 -- This function returns 1 if revenue distribution lines exists
891 -- for a specified project or task and returns 0 if no
892 -- revenue distribution line is found.
893 --
894 -- User can pass either project id or task id. If both
895 -- project id and task id are provided, function treated
896 -- as if only task were passed.
897 -- If Oracle error occured, Oracle error code is returned.
898 --
899 -- HISTORY
900 -- 20-OCT-95 R. Chiu Created
901 --
902 function check_rdl_exists (x_project_id IN number
903 , x_task_id IN number ) return number
904 is
905
906 cursor c1 is
907 select 1
908 from sys.dual
909 where exists (SELECT NULL
910 FROM pa_cust_rev_dist_lines rdl
911 where rdl.project_id = x_project_id);
912
913 cursor c2 is
914 select 1
915 from sys.dual
916 where exists (SELECT NULL
917 from pa_cust_rev_dist_lines rdl
918 , pa_expenditure_items i
919 where i.expenditure_item_id = rdl.expenditure_item_id
920 and i.task_id = x_task_Id);
921
922 c1_rec c1%rowtype;
923 c2_rec c2%rowtype;
924
925 begin
926 if (x_project_id is null and x_task_id is null) then
927 return(null);
928 end if;
929
930 if (x_task_id is null) then
931 open c1;
932 fetch c1 into c1_rec;
933 if c1%notfound then
934 close c1;
935 return(0);
936 else
937 close c1;
938 return(1);
939 end if;
940 else
941 open c2;
942 fetch c2 into c2_rec;
943 if c2%notfound then
944 close c2;
945 return(0);
946 else
947 close c2;
948 return(1);
949 end if;
950 end if;
951
952 exception
953 when others then
954 return(SQLCODE);
955 end check_rdl_exists;
956
957
958 -- FUNCTION
959 -- check_erdl_exists
960 -- PURPOSE
961 -- This function returns 1 if event revenue distribution
962 -- lines exists for a specified project or task and returns 0
963 -- if no event revenue distribution line is found for project
964 -- or task.
965 --
966 -- User can pass either project id or task id. If both
967 -- project id and task id are provided, function treated
968 -- as if only task were passed. User can also pass in a
969 -- specific event number.
970 -- If Oracle error occured, Oracle error code is returned.
971 --
972 -- HISTORY
973 -- 20-OCT-95 R. Chiu Created
974 --
975 function check_erdl_exists (x_project_id IN number
976 , x_task_id IN number
977 , x_event_num IN number ) return number
978 is
979
980 x_proj_id number;
981
982 cursor c1 is
983 select 1
984 from sys.dual
985 where exists (SELECT NULL
986 FROM pa_cust_event_rev_dist_lines
987 where PROJECT_ID = x_PROJECT_ID
988 AND nvl(x_event_num, event_num) = event_num );
989
990 cursor c2 is
991 select 1
992 from sys.dual
993 where exists (SELECT NULL
994 FROM pa_cust_event_rev_dist_lines
995 where project_id = x_proj_id
996 AND TASK_ID = x_TASK_ID
997 AND nvl(x_event_num, event_num) = event_num );
998
999 c1_rec c1%rowtype;
1000 c2_rec c2%rowtype;
1001
1002 begin
1003 if (x_project_id is null and x_task_id is null) then
1004 return(null);
1005 end if;
1006
1007 if (x_task_id is not null) then
1008 if x_project_id IS NULL THEN -- 4903460
1009 x_proj_id := get_task_project_id(x_task_id);
1010 else
1011 x_proj_id := x_project_id;
1012 END IF;
1013 end if;
1014
1015 if (x_task_id is null) then
1016 open c1;
1017 fetch c1 into c1_rec;
1018 if c1%notfound then
1019 close c1;
1020 return(0);
1021 else
1022 close c1;
1023 return(1);
1024 end if;
1025 else
1026 open c2;
1027 fetch c2 into c2_rec;
1028 if c2%notfound then
1029 close c2;
1030 return(0);
1031 else
1032 close c2;
1033 return(1);
1034 end if;
1035 end if;
1036
1037 exception
1038 when others then
1039 return(SQLCODE);
1040 end check_erdl_exists;
1041
1042
1043 -- FUNCTION
1044 -- check_draft_inv_item_exists
1045 -- PURPOSE
1046 -- This function returns 1 if draft invoice item exists
1047 -- for a project or a task and returns 0 if no draft
1048 -- invoice item is found for that project or task.
1049 --
1050 -- User can pass either project id or task id. If both
1051 -- project id and task id are provided, function treated
1052 -- as if only task were passed. Draft invoice item can exist
1053 -- at project or lowest level tasks.
1054 -- If Oracle error occured, Oracle error code is returned.
1055 --
1056 -- HISTORY
1057 -- 09-FEB-99 Ri.Singh Modified.
1058 -- Removed comments in cursor c2
1059 -- done during bugfix 773604. CONNECT BY
1060 -- clause is required for task deletion
1061 -- 20-OCT-95 R. Chiu Created
1062 --
1063 function check_draft_inv_item_exists (x_project_id IN number
1064 , x_task_id IN number
1065 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
1066 ) return number
1067 is
1068 x_proj_id number;
1069
1070 cursor c1 is
1071 SELECT 1
1072 FROM sys.dual
1073 WHERE EXISTS (SELECT NULL
1074 FROM pa_draft_invoice_items
1075 WHERE project_id = x_project_id);
1076
1077 cursor c2 is
1078 SELECT 1
1079 FROM sys.dual
1080 where exists (SELECT NULL
1081 FROM pa_draft_invoice_items
1082 where project_id = x_proj_id
1083 AND TASK_ID IN
1084 (SELECT TASK_ID
1085 FROM PA_TASKS
1086 CONNECT BY PRIOR PARENT_TASK_ID = TASK_ID /* Bug 6511941 */
1087 START WITH TASK_ID = x_TASK_ID));
1088
1089 /* cursor c2 is
1090 SELECT 1
1091 FROM sys.dual
1092 where exists (SELECT NULL
1093 FROM pa_draft_invoice_items
1094 where project_id = x_proj_id
1095 AND TASK_ID IN
1096 (SELECT TASK_ID
1097 FROM PA_TASKS
1098 WHERE TASK_ID = x_TASK_ID));
1099 */
1100 c1_rec c1%rowtype;
1101 c2_rec c2%rowtype;
1102
1103 -- New cursor c3 added for Performance Fix 4903460
1104 cursor c3 is
1105 SELECT 1
1106 FROM sys.dual
1107 where exists (SELECT NULL
1108 FROM pa_draft_invoice_items
1109 where project_id = x_proj_id
1110 AND TASK_ID = x_TASK_ID);
1111 c3_rec c3%rowtype;
1112 begin
1113 if (x_project_id is null and x_task_id is null) then
1114 return(null);
1115 end if;
1116
1117 if (x_task_id is not null) then
1118 if x_project_id IS NULL THEN -- 4903460
1119 x_proj_id := get_task_project_id(x_task_id);
1120 else
1121 x_proj_id := x_project_id;
1122 END IF;
1123 end if;
1124
1125 if (x_task_id is null) then
1126 open c1;
1127 fetch c1 into c1_rec;
1128 if c1%notfound then
1129 close c1;
1130 return(0);
1131 else
1132 close c1;
1133 return(1);
1134 end if;
1135 -- Performance Fix 4903460 : By default this flag is TRUE
1136 elsif(x_check_subtasks) then
1137 open c2;
1138 fetch c2 into c2_rec;
1139 if c2%notfound then
1140 close c2;
1141 return(0);
1142 else
1143 close c2;
1144 return(1);
1145 end if;
1146 else -- Newly introduced for Performance Fix 4903460
1147 open c3;
1148 fetch c3 into c3_rec;
1149 if c3%notfound then
1150 close c3;
1151 return(0);
1152 else
1153 close c3;
1154 return(1);
1155 end if;
1156 end if;
1157
1158 exception
1159 when others then
1160 return(SQLCODE);
1161 end check_draft_inv_item_exists;
1162
1163 -- FUNCTION
1164 -- check_draft_inv_details_exists
1165 -- PURPOSE
1166 -- This function returns 1 if draft invoice details exists
1167 -- for a task and returns 0 if no draft
1168 -- invoice details is found for that task.
1169 --
1170 -- User can pass task id.Draft invoice details can exist
1171 -- at lowest level tasks. If Oracle error occured,
1172 -- Oracle error code is returned.
1173 --
1174 -- HISTORY
1175 -- 28-AUG-99 sbalasub Created
1176 --
1177 function check_draft_inv_details_exists (x_task_id IN number
1178 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
1179 )return number
1180 is
1181 cursor c1 is
1182 SELECT 1
1183 FROM sys.dual
1184 where exists (SELECT NULL
1185 FROM pa_draft_invoice_details_all
1186 where CC_TAX_TASK_ID IN
1187 (SELECT TASK_ID
1188 FROM PA_TASKS
1189 CONNECT BY PRIOR PARENT_TASK_ID = TASK_ID /* Bug 6511941 */
1190 START WITH TASK_ID = x_TASK_ID));
1191
1192 c1_rec c1%rowtype;
1193 -- New cursor c2 added for Performance Fix 4903460
1194 cursor c2 is
1195 SELECT 1
1196 FROM sys.dual
1197 where exists (SELECT NULL
1198 FROM pa_draft_invoice_details_all
1199 where CC_TAX_TASK_ID = x_TASK_ID);
1200 c2_rec c2%rowtype;
1201
1202 begin
1203
1204 if (x_task_id is null) then
1205 return(null);
1206 -- Performance Fix 4903460 : By default this flag is TRUE
1207 elsif(x_check_subtasks) then
1208
1209 open c1;
1210 fetch c1 into c1_rec;
1211 if c1%notfound then
1212 close c1;
1213 return(0);
1214 else
1215 close c1;
1216 return(1);
1217 end if;
1218 else -- Newly added for Performance Fix 4903460
1219 open c2;
1220 fetch c2 into c2_rec;
1221 if c2%notfound then
1222 close c2;
1223 return(0);
1224 else
1225 close c2;
1226 return(1);
1227 end if;
1228 end if;
1229 exception
1230 when others then
1231 return(SQLCODE);
1232 end check_draft_inv_details_exists;
1233
1234 -- FUNCTION
1235 -- check_project_customer_exists
1236 -- PURPOSE
1237 -- This function returns 1 if project_customer_exists
1238 -- for a task and returns 0 if no project_customer_exists
1239 -- is found for that task.
1240 --
1241 -- User can pass task id. Project_customer_exists
1242 -- at lowest level tasks. If Oracle error occured,
1243 -- Oracle error code is returned.
1244 --
1245 -- HISTORY
1246 -- 28-AUG-99 sbalasub Created
1247 --
1248 function check_project_customer_exists (x_task_id IN number
1249 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
1250 ) return number
1251 is
1252 cursor c1 is
1253 SELECT 1
1254 FROM sys.dual
1255 where exists (SELECT NULL
1256 FROM pa_project_customers
1257 where receiver_task_id IN
1258 (SELECT TASK_ID
1259 FROM PA_TASKS
1260 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1261 START WITH TASK_ID = X_TASK_ID));
1262
1263 c1_rec c1%rowtype;
1264 -- New cursor c2 added for Performance Fix 4903460
1265 cursor c2 is
1266 SELECT 1
1267 FROM sys.dual
1268 where exists (SELECT NULL
1269 FROM pa_project_customers
1270 where receiver_task_id = X_TASK_ID);
1271 c2_rec c2%rowtype;
1272
1273 begin
1274
1275 if (x_task_id is null) then
1276 return(null);
1277 -- Performance Fix 4903460 : By default this flag is TRUE
1278 elsif(x_check_subtasks) then
1279
1280 open c1;
1281 fetch c1 into c1_rec;
1282 if c1%notfound then
1283 close c1;
1284 return(0);
1285 else
1286 close c1;
1287 return(1);
1288 end if;
1289
1290 else -- Newly added for Performance Fix 4903460
1291 open c2;
1292 fetch c2 into c2_rec;
1293 if c2%notfound then
1294 close c2;
1295 return(0);
1296 else
1297 close c2;
1298 return(1);
1299 end if;
1300 end if;
1301 exception
1302 when others then
1303 return(SQLCODE);
1304 end check_project_customer_exists;
1305
1306 -- FUNCTION
1307 -- check_projects_exists
1308 -- PURPOSE
1309 -- This function returns 1 if projects_exists
1310 -- for a task and returns 0 if no projects_exists
1311 -- is found for that task.
1312 --
1313 -- User can pass task id.projects can exist
1314 -- at lowest level tasks. If Oracle error occured,
1315 -- Oracle error code is returned.
1316 --
1317 -- HISTORY
1318 -- 28-AUG-99 sbalasub Created
1319 -- 07-Feb-03 gjain Bug 2784241: Modified the cursor c1
1320 -- for performance improvement
1321 function check_projects_exists (x_task_id IN number
1322 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
1323 )return number
1324 is
1325 cursor c1 is
1326 SELECT 1
1327 FROM sys.dual
1328 where exists (SELECT NULL
1329 FROM pa_projects_all -- Modified pa_projects to pa_projects_all for bug#3512486
1330 where project_id = (select project_id from pa_tasks where task_id=X_TASK_ID)
1331 and CC_TAX_TASK_ID IN
1332 (SELECT TASK_ID
1333 FROM PA_TASKS
1334 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1335 START WITH TASK_ID = X_TASK_ID));
1336
1337 c1_rec c1%rowtype;
1338 -- New cursor c2 added for Performance Fix 4903460
1339 x_proj_id NUMBER;
1340 cursor c2 is
1341 SELECT 1
1342 FROM sys.dual
1343 where exists (SELECT NULL
1344 FROM pa_projects_all
1345 where project_id = x_proj_id
1346 and CC_TAX_TASK_ID = x_task_id);
1347 c2_rec c2%rowtype;
1348
1349 begin
1350
1351 if (x_task_id is null) then
1352 return(null);
1353 else
1354 x_proj_id := get_task_project_id(x_task_id);
1355 -- Performance Fix 4903460 : By default this flag is TRUE
1356 if (x_check_subtasks) then
1357 open c1;
1358 fetch c1 into c1_rec;
1359 if c1%notfound then
1360 close c1;
1361 return(0);
1362 else
1363 close c1;
1364 return(1);
1365 end if;
1366 else -- Newly added for Performance Fix 4903460
1367 open c2;
1368 fetch c2 into c2_rec;
1369 if c2%notfound then
1370 close c2;
1371 return(0);
1372 else
1373 close c2;
1374 return(1);
1375 end if;
1376 end if;
1377 end if;
1378 exception
1379 when others then
1380 return(SQLCODE);
1381 end check_projects_exists;
1382
1383 -- FUNCTION
1384 -- check_draft_rev_item_exists
1385 -- PURPOSE
1386 -- This function returns 1 if draft revenue item exists
1387 -- for a project or a task and returns 0 if no draft
1388 -- revenue item is found for that project or task.
1389 --
1390 -- User can pass either project id or task id. If both
1391 -- project id and task id are provided, function treated
1392 -- as if only task were passed. Draft revenue item can exist
1393 -- at project or lowest level tasks.
1394 -- If Oracle error occured, Oracle error code is returned.
1395 --
1396 -- HISTORY
1397 -- 09-FEB-99 Ri.Singh Modified.
1398 -- Removed comments in cursor c2
1399 -- done during bugfix 773604. CONNECT BY
1400 -- clause is required for task deletion
1401 -- 20-OCT-95 R. Chiu Created
1402 --
1403 function check_draft_rev_item_exists (x_project_id IN number
1404 , x_task_id IN number
1405 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
1406 ) return number
1407 is
1408 x_proj_id number;
1409
1410 cursor c1 is
1411 SELECT 1
1412 FROM sys.dual
1413 WHERE EXISTS (SELECT NULL
1414 FROM pa_draft_revenue_items
1415 WHERE project_id = x_project_id);
1416
1417 cursor c2 is
1418 SELECT 1
1419 FROM sys.dual
1420 where exists (SELECT NULL
1421 FROM pa_draft_revenue_items
1422 where project_id = x_proj_id
1423 AND TASK_ID IN
1424 (SELECT TASK_ID
1425 FROM PA_TASKS
1426 CONNECT BY PRIOR PARENT_TASK_ID = TASK_ID /* Bug 6511941 */
1427 START WITH TASK_ID = x_TASK_ID));
1428
1429 /* cursor c2 is
1430 SELECT 1
1431 FROM sys.dual
1432 where exists (SELECT NULL
1433 FROM pa_draft_revenue_items
1434 where project_id = x_proj_id
1435 AND TASK_ID IN
1436 (SELECT TASK_ID
1437 FROM PA_TASKS
1438 WHERE TASK_ID = x_TASK_ID));
1439 */
1440
1441 c1_rec c1%rowtype;
1442 c2_rec c2%rowtype;
1443 -- New cursor c3 added for Performance Fix 4903460
1444 cursor c3 is
1445 SELECT 1
1446 FROM sys.dual
1447 where exists (SELECT NULL
1448 FROM pa_draft_revenue_items
1449 where project_id = x_proj_id
1450 AND TASK_ID =x_task_id);
1451 c3_rec c3%rowtype;
1452 begin
1453 if (x_project_id is null and x_task_id is null) then
1454 return(null);
1455 end if;
1456
1457 if (x_task_id is not null) then
1458 if x_project_id IS NULL THEN -- 4903460
1459 x_proj_id := get_task_project_id(x_task_id);
1460 else
1461 x_proj_id := x_project_id;
1462 END IF;
1463 end if;
1464
1465 if (x_task_id is null) then
1466 open c1;
1467 fetch c1 into c1_rec;
1468 if c1%notfound then
1469 close c1;
1470 return(0);
1471 else
1472 close c1;
1473 return(1);
1474 end if;
1475
1476 -- Performance Fix 4903460 : By default this flag is TRUE
1477 elsif(x_check_subtasks) then
1478 open c2;
1479 fetch c2 into c2_rec;
1480 if c2%notfound then
1481 close c2;
1482 return(0);
1483 else
1484 close c2;
1485 return(1);
1486 end if;
1487 else -- Newly introduced for Performance Fix 4903460
1488 open c3;
1489 fetch c3 into c3_rec;
1490 if c3%notfound then
1491 close c3;
1492 return(0);
1493 else
1494 close c3;
1495 return(1);
1496 end if;
1497 end if;
1498
1499 exception
1500 when others then
1501 return(SQLCODE);
1502 end check_draft_rev_item_exists;
1503
1504
1505 -- FUNCTION
1506 -- check_commitment_txn_exists
1507 -- PURPOSE
1508 -- This function returns 1 if commitment transaction exists
1509 -- for a project or a task and returns 0 if no commitment
1510 -- transaction is found for that project or task.
1511 --
1512 -- User can pass either project id or task id. If both
1513 -- project id and task id are provided, function treated
1514 -- as if only task were passed. commitment transaction can
1515 -- exist at project or lowest level tasks.
1516 -- If Oracle error occured, Oracle error code is returned.
1517 --
1518 -- HISTORY
1519 -- 09-FEB-99 Ri.Singh Modified.
1520 -- Removed comments in cursor c2
1521 -- done during bugfix 773604. CONNECT BY
1522 -- clause is required for task deletion
1523 -- 20-OCT-95 R. Chiu Created
1524 --
1525 function check_commitment_txn_exists (x_project_id IN number
1526 , x_task_id IN number
1527 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
1528 ) return number
1529 is
1530 x_proj_id number;
1531
1532 cursor c1 is
1533 SELECT 1
1534 FROM sys.dual
1535 WHERE EXISTS (SELECT NULL
1536 FROM pa_commitment_txns
1537 WHERE project_id = x_project_id);
1538
1539 cursor c2 is
1540 SELECT 1
1541 FROM sys.dual
1542 where exists (SELECT NULL
1543 FROM pa_commitment_txns
1544 where project_id = x_proj_id
1545 AND TASK_ID IN
1546 (SELECT TASK_ID
1547 FROM PA_TASKS
1548 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1549 START WITH TASK_ID = x_TASK_ID));
1550
1551
1552 /* cursor c2 is
1553 SELECT 1
1554 FROM sys.dual
1555 where exists (SELECT NULL
1556 FROM pa_commitment_txns
1557 where project_id = x_proj_id
1558 AND TASK_ID IN
1559 (SELECT TASK_ID
1560 FROM PA_TASKS
1561 WHERE TASK_ID = x_TASK_ID));
1562 */
1563 c1_rec c1%rowtype;
1564 c2_rec c2%rowtype;
1565
1566 -- New cursor c3 added for Performance Fix 4903460
1567 cursor c3 is
1568 SELECT 1
1569 FROM sys.dual
1570 where exists (SELECT NULL
1571 FROM pa_commitment_txns
1572 where project_id = x_proj_id
1573 AND TASK_ID = x_TASK_ID);
1574 c3_rec c3%rowtype;
1575 begin
1576 if (x_project_id is null and x_task_id is null) then
1577 return(null);
1578 end if;
1579
1580 if (x_task_id is not null) then
1581 if x_project_id IS NULL THEN -- 4903460
1582 x_proj_id := get_task_project_id(x_task_id);
1583 else
1584 x_proj_id := x_project_id;
1585 END IF;
1586 end if;
1587
1588 if (x_task_id is null) then
1589 open c1;
1590 fetch c1 into c1_rec;
1591 if c1%notfound then
1592 close c1;
1593 return(0);
1594 else
1595 close c1;
1596 return(1);
1597 end if;
1598 -- Performance Fix 4903460 : By default this flag is TRUE
1599 elsif(x_check_subtasks) then
1600 open c2;
1601 fetch c2 into c2_rec;
1602 if c2%notfound then
1603 close c2;
1604 return(0);
1605 else
1606 close c2;
1607 return(1);
1608 end if;
1609 else -- Newly introduced for Performance Fix 4903460
1610 open c3;
1611 fetch c3 into c3_rec;
1612 if c3%notfound then
1613 close c3;
1614 return(0);
1615 else
1616 close c3;
1617 return(1);
1618 end if;
1619 end if;
1620
1621 exception
1622 when others then
1623 return(SQLCODE);
1624 end check_commitment_txn_exists;
1625
1626
1627 -- FUNCTION
1628 -- check_comp_rule_set_exists
1629 -- PURPOSE
1630 -- This function returns 1 if compensation rule set exists
1631 -- for a project or a task and returns 0 if no compensation
1632 -- rule set is found for that project or task.
1633 --
1634 -- User can pass either project id or task id. If both
1635 -- project id and task id are provided, function treated
1636 -- as if only task were passed. Compensation rule set can
1637 -- exist at project or lowest level tasks.
1638 -- If Oracle error occured, Oracle error code is returned.
1639 --
1640 -- HISTORY
1641 -- 09-FEB-99 Ri.Singh Modified.
1642 -- Removed comments in cursor c2
1643 -- done during bugfix 773604. CONNECT BY
1644 -- clause is required for task deletion
1645 -- 20-OCT-95 R. Chiu Created
1646 --
1647 function check_comp_rule_set_exists (x_project_id IN number
1648 , x_task_id IN number
1649 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
1650 ) return number
1651 is
1652 x_proj_id number;
1653
1654 cursor c1 is
1655 SELECT 1
1656 FROM sys.dual
1657 WHERE EXISTS (SELECT NULL
1658 FROM pa_comp_rule_ot_defaults_all -- Bug 4680097: pa_compensation_rule_sets
1659 WHERE project_id = x_project_id)
1660 UNION
1661 SELECT 1
1662 FROM sys.dual
1663 WHERE EXISTS (SELECT NULL
1664 FROM pa_org_labor_sch_rule
1665 WHERE overtime_project_id = x_project_id);
1666
1667 cursor c2 is
1668 SELECT 1
1669 FROM sys.dual
1670 where exists (SELECT NULL
1671 FROM pa_comp_rule_ot_defaults_all -- Bug 4680097: pa_compensation_rule_sets
1672 where project_id = x_proj_id
1673 AND TASK_ID IN
1674 (SELECT TASK_ID
1675 FROM PA_TASKS
1676 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1677 START WITH TASK_ID = x_TASK_ID))
1678 UNION
1679 SELECT 1
1680 FROM sys.dual
1681 where exists (SELECT NULL
1682 FROM pa_org_labor_sch_rule
1683 where overtime_project_id = x_proj_id
1684 AND overtime_TASK_ID IN
1685 (SELECT TASK_ID
1686 FROM PA_TASKS
1687 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1688 START WITH TASK_ID = x_TASK_ID));
1689
1690 /* cursor c2 is
1691 SELECT 1
1692 FROM sys.dual
1693 where exists (SELECT NULL
1694 FROM pa_compensation_rule_sets
1695 where project_id = x_proj_id
1696 AND TASK_ID IN
1697 (SELECT TASK_ID
1698 FROM PA_TASKS
1699 WHERE TASK_ID = x_TASK_ID));
1700 */
1701 c1_rec c1%rowtype;
1702 c2_rec c2%rowtype;
1703
1704 -- New cursor c3 added for Performance Fix 4903460
1705 cursor c3 is
1706 SELECT 1
1707 FROM sys.dual
1708 WHERE
1709 exists (SELECT NULL
1710 FROM pa_comp_rule_ot_defaults_all
1711 where project_id = x_proj_id
1712 AND TASK_ID = x_task_id)
1713 or exists (SELECT NULL
1714 FROM pa_org_labor_sch_rule
1715 where overtime_project_id = x_proj_id
1716 AND overtime_TASK_ID = x_task_id);
1717 c3_rec c3%rowtype;
1718
1719 begin
1720 if (x_project_id is null and x_task_id is null) then
1721 return(null);
1722 end if;
1723
1724 if (x_task_id is not null) then
1725 if x_project_id IS NULL THEN -- 4903460
1726 x_proj_id := get_task_project_id(x_task_id);
1727 else
1728 x_proj_id := x_project_id;
1729 END IF;
1730 end if;
1731
1732 if (x_task_id is null) then
1733 open c1;
1734 fetch c1 into c1_rec;
1735 if c1%notfound then
1736 close c1;
1737 return(0);
1738 else
1739 close c1;
1740 return(1);
1741 end if;
1742 -- Performance Fix 4903460 : By default this flag is TRUE
1743 elsif(x_check_subtasks) then
1744 open c2;
1745 fetch c2 into c2_rec;
1746 if c2%notfound then
1747 close c2;
1748 return(0);
1749 else
1750 close c2;
1751 return(1);
1752 end if;
1753 else -- Newly introduced for Performance Fix 4903460
1754 open c3;
1755 fetch c3 into c3_rec;
1756 if c3%notfound then
1757 close c3;
1758 return(0);
1759 else
1760 close c3;
1761 return(1);
1762 end if;
1763 end if;
1764
1765 exception
1766 when others then
1767 return(SQLCODE);
1768 end check_comp_rule_set_exists;
1769
1770
1771 -- FUNCTION
1772 -- check_asset_assignmt_exists
1773 -- PURPOSE
1774 -- This function returns 1 if asset assignment exists
1775 -- for a specific project or task and returns 0 if no asset
1776 -- assignment is found for that project or task.
1777 --
1778 -- Note that for a 'Common Cost' capital project/task function returns zero
1779 -- since records would exist in pa_project_asset_assignment even
1780 -- when there are assets without being assigne to specific proj or task.
1781 --
1782 -- User can pass either project id or task id. If both
1783 -- project id and task id are provided, function treated
1784 -- as if only task were passed. Asset assignment can
1785 -- exist at project or lowest level tasks.
1786 -- If Oracle error occured, Oracle error code is returned.
1787 --
1788 -- HISTORY
1789 -- 09-FEB-99 Ri.Singh Modified.
1790 -- Removed comments in cursor c2
1791 -- done during bugfix 773604. CONNECT BY
1792 -- clause is required for task deletion
1793 -- 20-OCT-95 R. Chiu Created
1794 --
1795 function check_asset_assignmt_exists (x_project_id IN number
1796 , x_task_id IN number
1797 , x_check_subtasks IN boolean default TRUE -- Added for Performance Fix 4903460
1798 ) return number
1799 is
1800 x_proj_id number;
1801
1802 cursor c1 is
1803 SELECT 1
1804 FROM sys.dual
1805 WHERE EXISTS (SELECT NULL
1806 FROM pa_project_asset_assignments
1807 WHERE project_id = x_project_id);
1808
1809 /*Added for bug 6063643*/
1810 Cursor c2 is
1811 select 1 from pa_tasks
1812 where task_id = x_task_id
1813 and wbs_level > 1
1814 and exists (select null from pa_project_asset_assignments
1815 where task_id = x_task_id
1816 and project_asset_id <> 0); /* Added for bug 6245714 */
1817
1818 /*Commented for bug6063643
1819
1820 cursor c2 is
1821 SELECT 1
1822 FROM sys.dual
1823 where exists (SELECT NULL
1824 FROM pa_project_asset_assignments
1825 where project_id = x_proj_id
1826 AND TASK_ID IN
1827 (SELECT TASK_ID
1828 FROM PA_TASKS
1829 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1830 START WITH TASK_ID = x_TASK_ID));
1831
1832 commented for bug6063643*/
1833
1834 /* cursor c2 is
1835 SELECT 1
1836 FROM sys.dual
1837 where exists (SELECT NULL
1838 FROM pa_project_asset_assignments
1839 where project_id = x_proj_id
1840 AND TASK_ID IN
1841 (SELECT TASK_ID
1842 FROM PA_TASKS
1843 WHERE TASK_ID = x_TASK_ID));
1844 */
1845 c1_rec c1%rowtype;
1846 c2_rec c2%rowtype;
1847
1848 -- New cursor c3 added for Performance Fix 4903460
1849 cursor c3 is
1850 SELECT 1
1851 FROM sys.dual
1852 where exists (SELECT NULL
1853 FROM pa_project_asset_assignments
1854 where project_id = x_proj_id
1855 AND TASK_ID = x_task_id);
1856 c3_rec c3%rowtype;
1857 begin
1858 if (x_project_id is null and x_task_id is null) then
1859 return(null);
1860 end if;
1861
1862 if (x_task_id is not null) then
1863 if x_project_id IS NULL THEN -- 4903460
1864 x_proj_id := get_task_project_id(x_task_id);
1865 else
1866 x_proj_id := x_project_id;
1867 END IF;
1868 end if;
1869
1870 if (x_task_id is null) then
1871 open c1;
1872 fetch c1 into c1_rec;
1873 if c1%notfound then
1874 close c1;
1875 return(0);
1876 else
1877 close c1;
1878 return(1);
1879 end if;
1880 -- Performance Fix 4903460 : By default this flag is TRUE
1881 elsif(x_check_subtasks) then
1882 open c2;
1883 fetch c2 into c2_rec;
1884 if c2%notfound then
1885 close c2;
1886 return(0);
1887 else
1888 close c2;
1889 return(1);
1890 end if;
1891 else -- Newly introduced for Performance Fix 4903460
1892 open c3;
1893 fetch c3 into c3_rec;
1894 if c3%notfound then
1895 close c3;
1896 return(0);
1897 else
1898 close c3;
1899 return(1);
1900 end if;
1901 end if;
1902
1903 exception
1904 when others then
1905 return(SQLCODE);
1906 end check_asset_assignmt_exists;
1907
1908
1909 -- FUNCTION
1910 -- check_job_bill_rate_override
1911 -- PURPOSE
1912 -- This function returns 1 if job bill rate override exists
1913 -- for a specific project or task and returns 0 if no
1914 -- job bill rate override is found for that project or task.
1915 --
1916 -- User can pass either project id or task id. If both
1917 -- project id and task id are provided, function treated
1918 -- as if only task were passed.
1919 -- If Oracle error occured, Oracle error code is returned.
1920 --
1921 -- HISTORY
1922 -- 20-OCT-95 R. Chiu Created
1923 --
1924 function check_job_bill_rate_override (x_project_id IN number
1925 , x_task_id IN number ) return number
1926 is
1927 cursor c1 is
1928 SELECT 1
1929 FROM sys.dual
1930 where exists (SELECT NULL
1931 FROM PA_JOB_BILL_RATE_OVERRIDES
1932 WHERE project_id = x_project_id
1933 and task_id is null);
1934
1935 cursor c2 is
1936 SELECT 1
1937 FROM sys.dual
1938 where exists (SELECT NULL
1939 FROM PA_JOB_BILL_RATE_OVERRIDES
1940 WHERE TASK_ID = x_task_ID);
1941
1942 c1_rec c1%rowtype;
1943 c2_rec c2%rowtype;
1944
1945 begin
1946 if (x_project_id is null and x_task_id is null) then
1947 return(null);
1948 end if;
1949
1950 if (x_task_id is null) then
1951 open c1;
1952 fetch c1 into c1_rec;
1953 if c1%notfound then
1954 close c1;
1955 return(0);
1956 else
1957 close c1;
1958 return(1);
1959 end if;
1960 else
1961 open c2;
1962 fetch c2 into c2_rec;
1963 if c2%notfound then
1964 close c2;
1965 return(0);
1966 else
1967 close c2;
1968 return(1);
1969 end if;
1970 end if;
1971
1972 exception
1973 when others then
1974 return(SQLCODE);
1975 end check_job_bill_rate_override;
1976
1977
1978 -- FUNCTION
1979 -- check_burden_sched_override
1980 -- PURPOSE
1981 -- This function returns 1 if burden schedule override exists
1982 -- for a specific project or task and returns 0 if no
1983 -- burden schedule override is found for that project or task.
1984 --
1985 -- User can pass either project id or task id. If both
1986 -- project id and task id are provided, function treated
1987 -- as if only task were passed.
1988 -- If Oracle error occured, Oracle error code is returned.
1989 --
1990 -- HISTORY
1991 -- 20-OCT-95 R. Chiu Created
1992 --
1993 function check_burden_sched_override (x_project_id IN number
1994 , x_task_id IN number ) return number
1995 is
1996 cursor c1 is
1997 SELECT 1
1998 FROM sys.dual
1999 where exists (SELECT NULL
2000 FROM pa_ind_rate_schedules
2001 WHERE project_id = x_project_id);
2002
2003 cursor c2 is
2004 SELECT 1
2005 FROM sys.dual
2006 where exists (SELECT NULL
2007 FROM pa_ind_rate_schedules
2008 WHERE TASK_ID = x_task_ID);
2009
2010 c1_rec c1%rowtype;
2011 c2_rec c2%rowtype;
2012
2013 begin
2014 if (x_project_id is null and x_task_id is null) then
2015 return(null);
2016 end if;
2017
2018 if (x_task_id is null) then
2019 open c1;
2020 fetch c1 into c1_rec;
2021 if c1%notfound then
2022 close c1;
2023 return(0);
2024 else
2025 close c1;
2026 return(1);
2027 end if;
2028 else
2029 open c2;
2030 fetch c2 into c2_rec;
2031 if c2%notfound then
2032 close c2;
2033 return(0);
2034 else
2035 close c2;
2036 return(1);
2037 end if;
2038 end if;
2039
2040 exception
2041 when others then
2042 return(SQLCODE);
2043 end check_burden_sched_override;
2044
2045
2046 -- FUNCTION
2047 -- check_emp_bill_rate_override
2048 -- PURPOSE
2049 -- This function returns 1 if emp bill rate override exists
2050 -- for a specific project or task and returns 0 if no
2051 -- emp bill rate override is found for that project or task.
2052 --
2053 -- User can pass either project id or task id. If both
2054 -- project id and task id are provided, function treated
2055 -- as if only task were passed.
2056 -- If Oracle error occured, Oracle error code is returned.
2057 --
2058 -- HISTORY
2059 -- 20-OCT-95 R. Chiu Created
2060 -- 05-SEP-02 GJAIN For bug 2550288 changed cursor c2 to refer PA_EMP_BILL_RATE_OVERRIDES
2061 -- instead of PA_JOB_BILL_RATE_OVERRIDES
2062 --
2063 function check_emp_bill_rate_override (x_project_id IN number
2064 , x_task_id IN number ) return number
2065 is
2066 cursor c1 is
2067 SELECT 1
2068 FROM sys.dual
2069 where exists (SELECT NULL
2070 FROM PA_EMP_BILL_RATE_OVERRIDES
2071 WHERE project_id = x_project_id);
2072
2073 /* bug 2550288 changed below cursor to refer PA_EMP_BILL_RATE_OVERRIDES instead of PA_JOB_BILL_RATE_OVERRIDES */
2074 cursor c2 is
2075 SELECT 1
2076 FROM sys.dual
2077 where exists (SELECT NULL
2078 FROM PA_EMP_BILL_RATE_OVERRIDES
2079 WHERE TASK_ID = x_task_ID);
2080
2081 c1_rec c1%rowtype;
2082 c2_rec c2%rowtype;
2083
2084 begin
2085 if (x_project_id is null and x_task_id is null) then
2086 return(null);
2087 end if;
2088
2089 if (x_task_id is null) then
2090 open c1;
2091 fetch c1 into c1_rec;
2092 if c1%notfound then
2093 close c1;
2094 return(0);
2095 else
2096 close c1;
2097 return(1);
2098 end if;
2099 else
2100 open c2;
2101 fetch c2 into c2_rec;
2102 if c2%notfound then
2103 close c2;
2104 return(0);
2105 else
2106 close c2;
2107 return(1);
2108 end if;
2109 end if;
2110
2111 exception
2112 when others then
2113 return(SQLCODE);
2114 end check_emp_bill_rate_override;
2115
2116
2117 -- FUNCTION
2118 -- check_labor_multiplier
2119 -- PURPOSE
2120 -- This function returns 1 if labor multiplier exists
2121 -- for a specific project or task and returns 0 if no
2122 -- labor multiplier is found for that project or task.
2123 --
2124 -- User can pass either project id or task id. If both
2125 -- project id and task id are provided, function treated
2126 -- as if only task were passed.
2127 -- If Oracle error occured, Oracle error code is returned.
2128 --
2129 -- HISTORY
2130 -- 20-OCT-95 R. Chiu Created
2131 --
2132 function check_labor_multiplier (x_project_id IN number
2133 , x_task_id IN number ) return number
2134 is
2135 cursor c1 is
2136 SELECT 1
2137 FROM sys.dual
2138 where exists (SELECT NULL
2139 FROM PA_LABOR_MULTIPLIERS
2140 WHERE project_id = x_project_id);
2141
2142 cursor c2 is
2143 SELECT 1
2144 FROM sys.dual
2145 where exists (SELECT NULL
2146 FROM PA_LABOR_MULTIPLIERS
2147 WHERE TASK_ID = x_task_id);
2148
2149 c1_rec c1%rowtype;
2150 c2_rec c2%rowtype;
2151
2152 begin
2153 if (x_project_id is null and x_task_id is null) then
2154 return(null);
2155 end if;
2156
2157 if (x_task_id is null) then
2158 open c1;
2159 fetch c1 into c1_rec;
2160 if c1%notfound then
2161 close c1;
2162 return(0);
2163 else
2164 close c1;
2165 return(1);
2166 end if;
2167
2168 else
2169 open c2;
2170 fetch c2 into c2_rec;
2171 if c2%notfound then
2172 close c2;
2173 return(0);
2174 else
2175 close c2;
2176 return(1);
2177 end if;
2178
2179 end if;
2180
2181 exception
2182 when others then
2183 return(SQLCODE);
2184 end check_labor_multiplier;
2185
2186
2187 -- FUNCTION
2188 -- check_transaction_control
2189 -- PURPOSE
2190 -- This function returns 1 if transaction control exists
2191 -- for a specific project or task and returns 0 if no
2192 -- transaction control is found for that project or task.
2193 --
2194 -- User can pass either project id or task id. If both
2195 -- project id and task id are provided, function treated
2196 -- as if only task were passed.
2197 -- If Oracle error occured, Oracle error code is returned.
2198 --
2199 -- HISTORY
2200 -- 20-OCT-95 R. Chiu Created
2201 --
2202 function check_transaction_control (x_project_id IN number
2203 , x_task_id IN number ) return number
2204 is
2205 task_project_id number;
2206 cursor c1 is
2207 SELECT 1
2208 FROM sys.dual
2209 where exists (SELECT NULL
2210 FROM PA_TRANSACTION_CONTROLS
2211 WHERE project_id = x_project_id);
2212
2213 cursor c2 is
2214 SELECT 1
2215 FROM sys.dual
2216 where exists (SELECT NULL
2217 FROM PA_TRANSACTION_CONTROLS
2218 WHERE TASK_ID = x_task_ID
2219 AND PROJECT_ID = task_project_id);
2220
2221 c1_rec c1%rowtype;
2222 c2_rec c2%rowtype;
2223 begin
2224 if (x_project_id is null and x_task_id is null) then
2225 return(null);
2226 end if;
2227
2228 if (x_task_id is null) then
2229 open c1;
2230 fetch c1 into c1_rec;
2231 if c1%notfound then
2232 close c1;
2233 return(0);
2234 else
2235 close c1;
2236 return(1);
2237 end if;
2238
2239 else
2240 task_project_id :=
2241 pa_proj_tsk_utils.get_task_project_id(x_task_id);
2242 if ( (task_project_id < 0)
2243 or (task_project_id is null)) then
2244 return(null);
2245 end if;
2246
2247 open c2;
2248 fetch c2 into c2_rec;
2249 if c2%notfound then
2250 close c2;
2251 return(0);
2252 else
2253 close c2;
2254 return(1);
2255 end if;
2256
2257 end if;
2258
2259 exception
2260 when others then
2261 return(SQLCODE);
2262 end check_transaction_control;
2263
2264
2265 -- FUNCTION
2266 -- check_nl_bill_rate_override
2267 -- PURPOSE
2268 -- This function returns 1 if non-labor bill rate override
2269 -- exists for a specific project or task and returns 0 if no
2270 -- non-labor bill rate override is found for that project or task.
2271 --
2272 -- User can pass either project id or task id. If both
2273 -- project id and task id are provided, function treated
2274 -- as if only task were passed.
2275 -- If Oracle error occured, Oracle error code is returned.
2276 --
2277 -- HISTORY
2278 -- 20-OCT-95 R. Chiu Created
2279 --
2280 function check_nl_bill_rate_override (x_project_id IN number
2281 , x_task_id IN number ) return number
2282 is
2283 cursor c1 is
2284 SELECT 1
2285 FROM sys.dual
2286 where exists (SELECT NULL
2287 FROM PA_NL_BILL_RATE_OVERRIDES
2288 WHERE project_id = x_project_id);
2289
2290 cursor c2 is
2291 SELECT 1
2292 FROM sys.dual
2293 where exists (SELECT NULL
2294 FROM PA_NL_BILL_RATE_OVERRIDES
2295 WHERE TASK_ID = x_task_ID);
2296
2297 c1_rec c1%rowtype;
2298 c2_rec c2%rowtype;
2299 begin
2300 if (x_project_id is null and x_task_id is null) then
2301 return(null);
2302 end if;
2303
2304 if (x_task_id is null) then
2305 open c1;
2306 fetch c1 into c1_rec;
2307 if c1%notfound then
2308 close c1;
2309 return(0);
2310 else
2311 close c1;
2312 return(1);
2313 end if;
2314
2315 else
2316 open c2;
2317 fetch c2 into c2_rec;
2318 if c2%notfound then
2319 close c2;
2320 return(0);
2321 else
2322 close c2;
2323 return(1);
2324 end if;
2325 end if;
2326
2327 exception
2328 when others then
2329 return(SQLCODE);
2330 end check_nl_bill_rate_override;
2331
2332 -- FUNCTION
2333 -- check_job_bill_title_override
2334 -- PURPOSE
2335 -- This function returns 1 if job bill title override
2336 -- exists for a specific project or task and returns 0 if no
2337 -- job bill title override is found for that project or task.
2338 --
2339 -- User can pass either project id or task id. If both
2340 -- project id and task id are provided, function treated
2341 -- as if only task were passed.
2342 -- If Oracle error occured, Oracle error code is returned.
2343 --
2344 -- HISTORY
2345 -- 20-OCT-95 R. Chiu Created
2346 --
2347 function check_job_bill_title_override (x_project_id IN number
2348 , x_task_id IN number ) return number
2349 is
2350 cursor c1 is
2351 SELECT 1
2352 FROM sys.dual
2353 where exists (SELECT NULL
2354 FROM PA_JOB_BILL_TITLE_OVERRIDES
2355 WHERE project_id = x_project_id);
2356
2357 cursor c2 is
2358 SELECT 1
2359 FROM sys.dual
2360 where exists (SELECT NULL
2361 FROM PA_JOB_BILL_TITLE_OVERRIDES
2362 WHERE TASK_ID = x_task_ID);
2363
2364 c1_rec c1%rowtype;
2365 c2_rec c2%rowtype;
2366 begin
2367 if (x_project_id is null and x_task_id is null) then
2368 return(null);
2369 end if;
2370
2371 if (x_task_id is null) then
2372 open c1;
2373 fetch c1 into c1_rec;
2374 if c1%notfound then
2375 close c1;
2376 return(0);
2377 else
2378 close c1;
2379 return(1);
2380 end if;
2381
2382 else
2383 open c2;
2384 fetch c2 into c2_rec;
2385 if c2%notfound then
2386 close c2;
2387 return(0);
2388 else
2389 close c2;
2390 return(1);
2391 end if;
2392
2393 end if;
2394
2395 exception
2396 when others then
2397 return(SQLCODE);
2398 end check_job_bill_title_override;
2399
2400
2401 -- FUNCTION
2402 -- check_job_assignmt_override
2403 -- PURPOSE
2404 -- This function returns 1 if job assignment override
2405 -- exists for a specific project or task and returns 0 if no
2406 -- job assignment override is found for that project or task.
2407 --
2408 -- User can pass either project id or task id. If both
2409 -- project id and task id are provided, function treated
2410 -- as if only task were passed.
2411 -- If Oracle error occured, Oracle error code is returned.
2412 --
2413 -- HISTORY
2414 -- 20-OCT-95 R. Chiu Created
2415 --
2416 function check_job_assignmt_override (x_project_id IN number
2417 , x_task_id IN number ) return number
2418 is
2419 cursor c1 is
2420 SELECT 1
2421 FROM sys.dual
2422 where exists (SELECT NULL
2423 FROM PA_JOB_ASSIGNMENT_OVERRIDES
2424 WHERE project_id = x_project_id);
2425
2426 cursor c2 is
2427 SELECT 1
2428 FROM sys.dual
2429 where exists (SELECT NULL
2430 FROM PA_JOB_ASSIGNMENT_OVERRIDES
2431 WHERE TASK_ID = x_task_id);
2432
2433 c1_rec c1%rowtype;
2434 c2_rec c2%rowtype;
2435 begin
2436 if (x_project_id is null and x_task_id is null) then
2437 return(null);
2438 end if;
2439
2440 if (x_task_id is null) then
2441 open c1;
2442 fetch c1 into c1_rec;
2443 if c1%notfound then
2444 close c1;
2445 return(0);
2446 else
2447 close c1;
2448 return(1);
2449 end if;
2450
2451 else
2452 open c2;
2453 fetch c2 into c2_rec;
2454 if c2%notfound then
2455 close c2;
2456 return(0);
2457 else
2458 close c2;
2459 return(1);
2460 end if;
2461 end if;
2462
2463 exception
2464 when others then
2465 return(SQLCODE);
2466 end check_job_assignmt_override;
2467
2468 /* below function added for bug 2367945 */
2469 FUNCTION check_iex_task_charged( x_task_id IN NUMBER) return NUMBER
2470 is
2471 x_exist NUMBER := 0;
2472 l_task_cdate date; /* Added for bug 4060239*/
2473 begin
2474 -- anlee
2475 /* Commented out for bug 2790785
2476 select 1 into x_exist
2477 from dual
2478 where EXISTS (select * from ap_expense_report_lines_all a, ap_expense_report_headers_all b
2479 where a.task_id = x_task_id
2480 and a.REPORT_HEADER_ID = b.REPORT_HEADER_ID
2481 and b.source <> 'Oracle Project Accounting');
2482 */
2483 /* Commented for bug 4060239
2484 -- Fix from bug 2790785
2485 SELECT 1 into x_exist
2486 FROM DUAL
2487 WHERE EXISTS (SELECT * FROM AP_EXPENSE_REPORT_LINES_ALL A, AP_EXPENSE_REPORT_HEADERS_ALL B
2488 WHERE A.TASK_ID = x_task_id
2489 AND A.REPORT_HEADER_ID = B.REPORT_HEADER_ID
2490 AND B.SOURCE <> 'Oracle Project Accounting'
2491 AND B.VOUCHNO = 0);
2492 */
2493 -- Added for bug 4060239
2494
2495 /* fetch task creation_date into l_task_cdate */
2496 /* start of bug 4060239 */
2497 select trunc(creation_date) into l_task_cdate
2498 from pa_tasks
2499 where task_id = x_task_id;
2500
2501 /* Commented and modfied for Bug#5839405
2502 SELECT 1
2503 INTO x_exist
2504 FROM AP_EXPENSE_REPORT_LINES_ALL A,
2505 AP_EXPENSE_REPORT_HEADERS_ALL B
2506 WHERE A.TASK_ID = x_task_id
2507 AND A.REPORT_HEADER_ID = B.REPORT_HEADER_ID
2508 AND A.CREATION_DATE >= l_task_cdate
2509 AND B.CREATION_DATE >= l_task_cdate
2510 AND B.SOURCE <> 'Oracle Project Accounting'
2511 AND B.VOUCHNO = 0
2512 AND rownum = 1;
2513 */
2514
2515 SELECT 1
2516 INTO x_exist
2517 FROM AP_EXPENSE_REPORT_LINES_ALL A
2518 WHERE A.TASK_ID IS NOT NULL
2519 AND A.TASK_ID = x_task_id
2520 AND A.CREATION_DATE >= l_task_cdate
2521 AND EXISTS ( SELECT 1 FROM AP_EXPENSE_REPORT_HEADERS_ALL B
2522 WHERE A.REPORT_HEADER_ID = B.REPORT_HEADER_ID
2523 AND B.CREATION_DATE >= l_task_cdate
2524 AND B.SOURCE <> 'Oracle Project Accounting'
2525 AND B.VOUCHNO = 0)
2526 AND rownum = 1;
2527 /* Changes end for Bug#5839405 */
2528
2529 /* end of bug 4060239 */
2530
2531 If x_exist = 1
2532 then
2533 return (1);
2534 else
2535 return(0);
2536 end if;
2537 exception
2538 when others then
2539 return(SQLCODE);
2540 end check_iex_task_charged;
2541
2542 /* Start changes for Service Intgration bug#16535441*/
2543
2544 -- FUNCTION
2545 -- check_service_order_exists
2546 -- PURPOSE
2547 -- This function returns 1 if service order exists for project id or
2548 -- task id and returns 0 if no event is found.
2549 --
2550 -- User can pass either project id or task id. If both
2551 -- project id and task id are provided, function treated
2552 -- as if only task were passed. Service order can exist at project and
2553 -- top tasks level.
2554 -- If Oracle error occured, Oracle error code is returned.
2555 --
2556 -- HISTORY
2557 -- 25-MAR-13 speddi Created
2558 --
2559
2560 function check_service_order_exists (x_project_id IN number
2561 , x_task_id IN number
2562 , x_check_subtasks IN boolean default TRUE)
2563 return number
2564 is
2565 x_proj_id number;
2566
2567 cursor c1 is
2568 SELECT 1
2569 FROM sys.dual
2570 WHERE EXISTS (SELECT NULL
2571 FROM cs_estimate_details
2572 WHERE project_id = x_project_id
2573 UNION
2574 SELECT NULL
2575 FROM CSF_DEBRIEF_LINES
2576 WHERE project_id = x_project_id
2577 UNION
2578 SELECT NULL
2579 FROM CSD_REPAIRS
2580 WHERE project_id = x_project_id
2581 );
2582
2583 cursor c2 is
2584 SELECT 1
2585 FROM sys.dual
2586 where exists (SELECT NULL
2587 FROM cs_estimate_details
2588 where project_id = x_proj_id
2589 AND project_TASK_ID IN
2590 (SELECT TASK_ID
2591 FROM PA_TASKS
2592 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
2593 START WITH TASK_ID = x_TASK_ID)
2594 UNION
2595 SELECT NULL
2596 FROM CSF_DEBRIEF_LINES
2597 where project_id = x_proj_id
2598 AND PROJECT_TASK_ID IN
2599 (SELECT TASK_ID
2600 FROM PA_TASKS
2601 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
2602 START WITH TASK_ID = x_TASK_ID)
2603 UNION
2604 SELECT NULL
2605 FROM CSD_REPAIRS
2606 where project_id = x_proj_id
2607 AND TASK_ID IN
2608 (SELECT TASK_ID
2609 FROM PA_TASKS
2610 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
2611 START WITH TASK_ID = x_TASK_ID)
2612 );
2613
2614
2615 c1_rec c1%rowtype;
2616 c2_rec c2%rowtype;
2617
2618 cursor c3 is
2619 SELECT 1
2620 FROM sys.dual
2621 where exists (SELECT NULL
2622 FROM cs_estimate_details
2623 where project_id = x_proj_id
2624 AND project_TASK_ID = x_TASK_ID
2625 UNION
2626 SELECT NULL
2627 FROM CSF_DEBRIEF_LINES
2628 where project_id = x_proj_id
2629 AND PROJECT_TASK_ID = x_TASK_ID
2630 UNION
2631 SELECT NULL
2632 FROM CSD_REPAIRS
2633 where project_id = x_proj_id
2634 AND TASK_ID = x_TASK_ID
2635 );
2636
2637 c3_rec c3%rowtype;
2638 begin
2639
2640 if (x_project_id is null and x_task_id is null) then
2641 return(null);
2642 end if;
2643
2644 if (x_task_id is not null) then
2645 if x_project_id IS NULL THEN
2646 x_proj_id := get_task_project_id(x_task_id);
2647 else
2648 x_proj_id := x_project_id;
2649 END IF;
2650 end if;
2651
2652 if (x_task_id is null) then
2653 open c1;
2654 fetch c1 into c1_rec;
2655 if c1%notfound then
2656 close c1;
2657 return(0);
2658 else
2659 close c1;
2660 return(1);
2661 end if;
2662 elsif (x_check_subtasks) then
2663 open c2;
2664 fetch c2 into c2_rec;
2665 if c2%notfound then
2666 close c2;
2667 return(0);
2668 else
2669 close c2;
2670 return(1);
2671 end if;
2672 else
2673 open c3;
2674 fetch c3 into c3_rec;
2675 if c3%notfound then
2676 close c3;
2677 return(0);
2678 else
2679 close c3;
2680 return(1);
2681 end if;
2682 end if;
2683
2684 exception
2685 when others then
2686 return(SQLCODE);
2687 end check_service_order_exists;
2688
2689 /* End changes for Service Intgration bug#16535441 */
2690
2691 END PA_PROJ_TSK_UTILS;
[Home] [Help]