1 package body PA_TASK_UTILS as
2 -- $Header: PAXTUTLB.pls 120.17.12020000.4 2013/04/02 07:07:18 speddi ship $
3
4
5 --
6 -- FUNCTION
7 -- get_wbs_level
8 -- PURPOSE
9 -- This function retrieves the wbs level of a task.
10 -- If no wbs level id is found, null is returned.
11 -- If Oracle error occurs, Oracle error number is returned.
12 -- HISTORY
13 -- 16-OCT-95 R. Chiu Created
14 --
15 function get_wbs_level (x_task_id IN number) return number
16 is
17 cursor c1 is
18 select wbs_level
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.wbs_level ;
33 end if;
34
35 exception
36 when others then
37 return SQLCODE ;
38
39 end get_wbs_level;
40
41
42 --
43 -- FUNCTION
44 -- get_top_task_id
45 -- PURPOSE
46 -- This function retrieves the top task id of a task.
47 -- If no top task id is found, null is returned.
48 -- If Oracle error occurs, Oracle error number is returned.
49 -- HISTORY
50 -- 16-OCT-95 R. Chiu Created
51 --
52 function get_top_task_id (x_task_id IN number) return number
53 is
54 cursor c1 is
55 select top_task_id
56 from pa_tasks
57 where task_id = x_task_id;
58
59 c1_rec c1%rowtype;
60
61 begin
62 open c1;
63 fetch c1 into c1_rec;
64 if c1%notfound then
65 close c1;
66 return(null);
67 else
68 close c1;
69 return( c1_rec.top_task_id );
70 end if;
71
72 exception
73 when others then
74 return(SQLCODE);
75
76 end get_top_task_id;
77
78
79 --
80 -- FUNCTION
81 -- get_parent_task_id
82 -- PURPOSE
83 -- This function retrieves the parent task id of a task.
84 -- If no parent task id is found, null is returned.
85 -- If Oracle error occurs, Oracle error number is returned.
86 --
87 -- HISTORY
88 -- 16-OCT-95 R. Chiu Created
89 --
90 function get_parent_task_id (x_task_id IN number) return number
91 is
92 cursor c1 is
93 select parent_task_id
94 from pa_tasks
95 where task_id = x_task_id;
96
97 c1_rec c1%rowtype;
98
99 begin
100 open c1;
101 fetch c1 into c1_rec;
102 if c1%notfound then
103 close c1;
104 return( null);
105 else
106 close c1;
107 return( c1_rec.parent_task_id );
108 end if;
109
110 exception
111 when others then
112 return(SQLCODE);
113
114 end get_parent_task_id;
115
116
117 /* Start of Bug 6497559 */
118
119 --
120 -- FUNCTION
121 -- get_resource_name
122 -- PURPOSE
123 -- This functions returns the resource name for a corresponding resource_list_member_id
124 --
125 -- HISTORY
126 -- 19-May-2009 rthumma Created
127 --
128
129 function get_resource_name (x_rlm_id IN number) return varchar2
130 is
131 cursor c1 is
132 select alias
133 from pa_resource_list_members
134 where resource_list_member_id = x_rlm_id;
135
136 l_rlm_name pa_resource_list_members.alias%type ;
137
138 begin
139 open c1;
140 fetch c1 into l_rlm_name;
141 if c1%notfound then
142 close c1;
143 return(null);
144 else
145 close c1;
146 return( l_rlm_name );
147 end if;
148
149 exception
150 when others then
151 return(SQLCODE);
152
153 end get_resource_name;
154
155 --
156 -- FUNCTION
157 -- get_task_name
158 -- PURPOSE
159 -- This functions returns the task name for a corresponding task_id
160 --
161 -- HISTORY
162 -- 19-May-2009 rthumma Created
163 --
164
165 function get_task_name (x_task_id IN number) return varchar2
166 is
167 cursor c1 is
168 select name
169 from pa_proj_elements
170 where proj_element_id = x_task_id;
171
172 l_task_name pa_proj_elements.name%type;
173
174 begin
175 open c1;
176 fetch c1 into l_task_name;
177 if c1%notfound then
178 close c1;
179 return(null);
180 else
181 close c1;
182 return( l_task_name );
183 end if;
184
185 exception
186 when others then
187 return(SQLCODE);
188
189 end get_task_name;
190
191 --
192 -- FUNCTION
193 -- get_task_number
194 -- PURPOSE
195 -- This functions returns the task number for a corresponding task_id
196 --
197 -- HISTORY
198 -- 19-May-2009 rthumma Created
199 --
200
201 function get_task_number (x_task_id IN number) return varchar2
202 is
203 cursor c1 is
204 select element_number
205 from pa_proj_elements
206 where proj_element_id = x_task_id;
207
208 l_task_number pa_proj_elements.element_number%type;
209
210 begin
211 open c1;
212 fetch c1 into l_task_number;
213 if c1%notfound then
214 close c1;
215 return(null);
216 else
217 close c1;
218 return( l_task_number );
219 end if;
220
221 exception
222 when others then
223 return(SQLCODE);
224
225 end get_task_number;
226
227 --
228 -- FUNCTION
229 -- get_project_name
230 -- PURPOSE
231 -- This functions returns the project name for a corresponding project_id
232 --
233 -- HISTORY
234 -- 19-May-2009 rthumma Created
235 --
236
237 function get_project_name (x_project_id IN number) return varchar2
238 is
239 cursor c1 is
240 select name
241 from pa_projects_all
242 where project_id = x_project_id;
243
244 l_project_name pa_projects_all.name%type;
245
246 begin
247 open c1;
248 fetch c1 into l_project_name;
249 if c1%notfound then
250 close c1;
251 return(null);
252 else
253 close c1;
254 return( l_project_name );
255 end if;
256
257 exception
258 when others then
259 return(SQLCODE);
260
261 end get_project_name;
262
263 --
264 -- FUNCTION
265 -- get_project_number
266 -- PURPOSE
267 -- This functions returns the project number for a corresponding project_id
268 --
269 -- HISTORY
270 -- 19-May-2009 rthumma Created
271 --
272
273 function get_project_number (x_project_id IN number) return varchar2
274 is
275 cursor c1 is
276 select segment1
277 from pa_projects_all
278 where project_id = x_project_id;
279
280 l_project_number pa_projects_all.segment1%type;
281
282 begin
283 open c1;
284 fetch c1 into l_project_number;
285 if c1%notfound then
286 close c1;
287 return(null);
288 else
289 close c1;
290 return( l_project_number );
291 end if;
292
293 exception
294 when others then
295 return(SQLCODE);
296
297 end get_project_number;
298
299 /* End of Bug 6497559 */
300
301
302 --
303 -- FUNCTION
304 -- check_unique_task_number
305 -- PURPOSE
306 -- This function returns 1 if a task number is not already
307 -- used in PA system for a specific project id and returns 0
308 -- if number is used.
309 -- If Oracle error occurs, Oracle error number is returned.
310 -- HISTORY
311 -- 20-OCT-95 R. Chiu Created
312 --
313 function check_unique_task_number (x_project_id IN number
314 , x_task_number IN varchar2
315 , x_rowid IN varchar2 ) return number
316 is
317 cursor c1 is
318 select task_id from pa_tasks
319 where project_id = x_project_id
320 and task_number = substrb(x_task_number,1,25) -- bug 5733285 added substrb
321 and (x_ROWID IS NULL OR x_rowid <> PA_TASKS.ROWID);
322
323 c1_rec c1%rowtype;
324
325 begin
326 if (x_project_id is null or x_task_number is null) then
327 return (null);
328 end if;
329
330 open c1;
331 fetch c1 into c1_rec;
332 if c1%notfound then
333 close c1;
334 return(1);
335 else
336 close c1;
337 return(0);
338 end if;
339
340 exception
341 when others then
342 return(SQLCODE);
343
344 end check_unique_task_number;
345
346
347 --
348 -- FUNCTION
349 -- check_last_task
350 -- PURPOSE
351 -- This function returns 1 if a task is the last task
352 -- and returns 0 otherwise.
353 -- If Oracle error occurs, Oracle error number is returned.
354 --
355 -- HISTORY
356 -- 20-OCT-95 R. Chiu Created
357 --
358 function check_last_task (x_task_id IN number ) return number
359 is
360 x_project_id number;
361
362 cursor c1 is
363 select 1
364 from sys.dual
365 where exists (SELECT null
366 FROM PA_TASKS
367 WHERE PROJECT_ID = x_project_id
368 and task_id <> x_task_id
369 and task_id = top_task_id);
370
371 c1_rec c1%rowtype;
372
373 begin
374 if (x_task_id is null) then
375 return(null);
376 end if;
377
378 x_project_id := pa_proj_tsk_utils.get_task_project_id(x_task_id);
379
380 if (x_project_id is null) then
381 return(null);
382 end if ;
383
384 open c1;
385 fetch c1 into c1_rec;
386 if c1%notfound then
387 close c1;
388 return(1);
389 else
390 close c1;
391 return(0);
392 end if;
393
394 exception
395 when others then
396 return(SQLCODE);
397 end check_last_task;
398
399 --
400 -- FUNCTION
401 -- check_last_child
402 -- PURPOSE
403 -- This function returns 1 if a task is the last child of branch
404 -- and returns 0 otherwise.
405 -- If Oracle error occurs, Oracle error number is returned.
406 --
407 -- HISTORY
408 -- 20-OCT-95 R. Chiu Created
409 --
410 function check_last_child (x_task_id IN number ) return number
411 is
412 x_parent_task_id number;
413
414 cursor c1 is
415 select 1
416 from sys.dual
417 where exists (SELECT null
418 FROM PA_TASKS
419 WHERE TASK_ID <> x_TASK_ID
420 AND PARENT_TASK_ID = x_PARENT_TASK_ID);
421
422 c1_rec c1%rowtype;
423
424 begin
425 if (x_task_id is null) then
426 return(null);
427 end if;
428
429 x_parent_task_id := pa_task_utils.get_parent_task_id(x_task_id);
430
431 open c1;
432 fetch c1 into c1_rec;
433 if c1%notfound then
434 close c1;
435 return(1);
436 else
437 close c1;
438 return(0);
439 end if;
440
441 exception
442 when others then
443 return(SQLCODE);
444 end check_last_child;
445
446
447 -- FUNCTION
448 -- check_pct_complete_exists
449 -- PURPOSE
450 -- This function returns 1 if percent complete exists for a
451 -- specific task and returns 0 if no percent complete is found
452 -- for that task.
453 --
454 -- If Oracle error occured, Oracle error code is returned.
455 --
456 -- HISTORY
457 -- 20-OCT-95 R. Chiu Created
458 --
459 function check_pct_complete_exists (x_task_id IN number ) return number
460 is
461
462 cursor c1 is
463 select 1
464 from sys.dual
465 where exists (SELECT null
466 FROM pa_percent_completes
467 where TASK_ID IN
468 (SELECT TASK_ID
469 FROM PA_TASKS
470 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
471 START WITH TASK_ID = x_TASK_ID));
472
473 c1_rec c1%rowtype;
474
475 begin
476 if (x_task_id is null) then
477 return(null);
478 end if;
479
480 open c1;
481 fetch c1 into c1_rec;
482 if c1%notfound then
483 close c1;
484 return(0);
485 else
486 close c1;
487 return(1);
488 end if;
489
490 exception
491 when others then
492 return(SQLCODE);
493 end check_pct_complete_exists;
494
495
496 -- FUNCTION
497 -- check_labor_cost_multiplier
498 -- PURPOSE
499 -- This function returns 1 if a task has labor cost multiplier
500 -- and returns 0 otherwise.
501 --
502 -- If Oracle error occured, Oracle error code is returned.
503 --
504 -- HISTORY
505 -- 20-OCT-95 R. Chiu Created
506 --
507 function check_labor_cost_multiplier
508 (x_task_id IN number ) return number
509 is
510 cursor c1 is
511 select LABOR_COST_MULTIPLIER_NAME
512 from pa_tasks
513 where task_id = x_task_id
514 and LABOR_COST_MULTIPLIER_NAME is not null;
515
516 c1_rec c1%rowtype;
517 begin
518 open c1;
519 fetch c1 into c1_rec;
520 if c1%notfound then
521 close c1;
522 return(0);
523 else
524 close c1;
525 return(1);
526 end if;
527
528 exception
529 when others then
530 return(SQLCODE);
531 end check_labor_cost_multiplier;
532
533
534 --
535 -- PROCEDURE
536 -- check_create_subtask_ok
537 -- PURPOSE
538 -- This API checks if a specific task has any transaction
539 -- control, burden schedule override, budget, billing,allocations
540 -- and other transaction information. If task has any of
541 -- these information, then it's not ok to create subtask for
542 -- that task. Specific reason will be returned.
543 -- If it's ok to create subtask, the x_err_code will be 0.
544 -- 14-AUG-2002 If the task is plannable then we cannot add subtasks.
545 --
546 -- HISTORY
547 -- 25-FEB-05 djoseph Bug 409938: Added the api pjm_projtask_deletion.CheckUse_ProjectTask
548 -- to check against PJM. Also changed the value of x_err_stage
549 -- for pa_proj_tsk_utils.check_ap_inv_dist_exists
550 -- 14-AUG-2002 Vejayara Bug# 2331201 - Financial planning development
551 -- related changes. If a task is present in
552 -- pa_fp_elements, then a sub-task cannot be added
553 -- to the task - check_create_subtask_ok
554 -- 06-APR-99 Ri. Singh Replaced call to pa_budget_utils.check_budget_exists
555 -- with pa_budget_utils2.check_task_lowest_in_budgets
556 -- Ref bug# 860607
557 -- 16-FEB-99 Ri. Singh Removed call to check_pct_complete_exists
558 -- 10-FEB-99 Ri. Singh Modified as explained below
559 -- 20-OCT-95 R. Chiu Created
560 --
561 procedure check_create_subtask_ok ( x_task_id IN number
562 , x_validation_mode IN VARCHAR2 DEFAULT 'U' --bug 2947492
563 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
564 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
565 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
566 is
567
568 old_stack varchar2(630);
569 status_code number;
570 x_top_task_id number;
571 x_project_id number;
572 x_proj_type_class_code varchar2(30);
573 dummy_null varchar2(30) default NULL;
574 l_OTL_timecard_exists boolean; -- Added for bug 3870364
575 /* Commented the cursor for bug#3512486
576 cursor p1 is select 1 from pa_project_types
577 where burden_sum_dest_task_id = x_task_id;*/
578 /* Added the below cursor for bug#3512486*/
579 cursor p1 is select 1 from pa_project_types_all
580 where burden_sum_dest_task_id = x_task_id
581 and org_id = (SELECT org_id --MOAC Changes: Bug 4363092: removed nvl usage with org_id
582 FROM pa_projects_all
583 where project_id = (select project_id from pa_tasks where task_id=x_task_id));
584
585 temp number;
586 l_return_val varchar2(1);
587
588 l_return_status varchar2(1);
589 l_msg_count NUMBER;
590 l_msg_data VARCHAR2(4000);
591
592 begin
593
594 x_err_code := 0;
595 old_stack := x_err_stack;
596
597 x_err_stack := x_err_stack || '->check_create_subtask_ok';
598
599 if (x_task_id is null) then
600 x_err_code := 10;
601 x_err_stage := 'PA_NO_TASK_ID';
602 return;
603 end if ;
604
605 -- Modified 02/10/99 Ri. Singh
606 -- all the following checks are applicable to lowest level tasks.
607 -- If the task for which subtask is being created is not the lowest level
608 -- task, these checks need not be performed. Ref Bug#: 773604
609
610 if(check_child_exists(x_task_id)=1) then
611 x_err_stack := old_stack;
612 return;
613 end if;
614
615 -- End of fix
616 x_project_id := pa_proj_tsk_utils.get_task_project_id(x_task_id);
617
618 --anuragag Bug 8566495 - No validation on task_id if the task is created through change management
619 if x_project_id is null then
620 select project_id into x_project_id
621 from pa_proj_elements
622 where proj_element_id = x_task_id
623 and task_status is not null;
624 end if;
625
626 open p1;
627 fetch p1 into temp;
628 if p1%notfound then null;
629 else
630 x_err_code := 250;
631 x_err_stage := 'PA_TASK_BURDEN_SUM_DEST';
632 return;
633 end if;
634
635 --anuragag Bug 8566495 - No validation on task_id if the task is created through change management
636 if PA_TASK_PVT1.G_CHG_DOC_CNTXT <> 1
637 then
638 -- Get top task id
639 x_err_stage := 'get top task id of '|| x_task_id;
640 x_top_task_id := get_top_task_id(x_task_id);
641 if (x_top_task_id is null) then
642 x_err_code := 20;
643 x_err_stage := 'PA_NO_TOP_TASK_ID';
644 return;
645 elsif ( x_top_task_id < 0 ) then
646 x_err_code := x_top_task_id;
647 return;
648 end if;
649 end if;
650 -- Check if task has transaction control.
651 x_err_stage := 'check txn control for '|| x_task_id;
652 status_code :=
653 pa_proj_tsk_utils.check_transaction_control(null, x_task_id);
654 if ( status_code = 1 ) then
655 x_err_code := 30;
656 x_err_stage := 'PA_TSK_TXN_CONT_EXIST';
657 return;
658 elsif ( status_code < 0 ) then
659 x_err_code := status_code;
660 return;
661 end if;
662
663 -- Check if task has burden schedule override
664 x_err_stage := 'check burden schedule override for '|| x_task_id;
665 status_code :=
666 pa_proj_tsk_utils.check_burden_sched_override(null, x_task_id);
667 if ( status_code = 1 ) then
668 x_err_code := 40;
669 x_err_stage := 'PA_TSK_BURDEN_SCH_OVRIDE_EXIST';
670 return;
671 elsif ( status_code < 0 ) then
672 x_err_code := status_code;
673 return;
674 end if;
675 /****
676 Bug# 2331201 - Subtask cannot be added if this task has been included in
677 the financial planning options for the project
678 ***/
679
680 /* Bug 2947492
681 Removed the code from here. Please refer the HLD
682
683 x_err_stage := 'check finplan options existence for ' || x_task_id;
684
685 -- Check if task has been selected for planning.
686 -- Subtask will not be allowed if task exists in pa_fp_elements with
687 -- planning level as "L" or "M".
688 -- In case of "M", the task should not be a single top task with no subtasks.
689 x_err_stage := 'check pa_fp_elements for task '|| x_task_id;
690 declare
691 cursor c1 is
692 select 1
693 from pa_fp_elements r,
694 pa_proj_fp_options m
695 where r.task_id = x_task_id
696 and (decode(r.element_type,
697 'COST',cost_fin_plan_level_code,
698 'REVENUE',revenue_fin_plan_level_code,
699 'ALL',all_fin_plan_level_code) = 'L'
700 or
701 (decode(r.element_type,
702 'COST',cost_fin_plan_level_code,
703 'REVENUE',revenue_fin_plan_level_code,
704 'ALL',all_fin_plan_level_code) = 'M'
705 and x_task_id <> x_top_task_id))
706 and m.proj_fp_options_id = r.proj_fp_options_id;
707
708 c1_rec c1%rowtype;
709 begin
710 open c1;
711 fetch c1 into c1_rec;
712 if c1%notfound then
713 close c1; -- this task is not part of the planning options
714 else
715 close c1;
716 x_err_code := 45;
717 x_err_stage := 'PA_FP_TSK_ELEMENTS_EXISTS';
718 return;
719 end if;
720 exception
721 when others then
722 close c1;
723 x_err_code := SQLCODE;
724 return;
725 end;
726 */ -- Bug 2947492. Not required anymore. Please see the HLD.
727
728 -- Begin Fix 860607 : Modified 04/03/99
729 -- Subtask will not be allowed if task exists in budgets with a budget entry method
730 -- as "L" or "M".
731 -- In case of "M", the task should not be a single top task with no subtasks.
732
733 -- Check if task has budget
734
735 x_err_stage := 'check task budget for '|| x_task_id;
736 status_code := pa_budget_utils2.check_task_lowest_in_budgets
737 ( x_task_id => x_task_id
738 , x_top_task_id => x_top_task_id
739 , x_validation_mode => x_validation_mode ); --bug 2947492
740
741 if ( status_code = 1 ) then
742 x_err_code := 50;
743 x_err_stage := 'PA_TSK_BUDGET_EXIST';
744 return;
745 elsif ( status_code < 0 ) then
746 x_err_code := status_code;
747 return;
748 end if;
749
750 -- End Fix 860607
751
752 /* if (x_task_id <> x_top_task_id) then ------------Commented for Bug 6063643*/
753
754 -- Begin Fix 860607
755 /* -- Check if task has budget
756 x_err_stage := 'check task budget for '|| x_task_id;
757 status_code := pa_budget_utils.check_task_budget_exists(x_task_id,
758 'A', dummy_null);
759 if ( status_code = 1 ) then
760 x_err_code := 50;
761 x_err_stage := 'PA_TSK_BUDGET_EXIST';
762 return;
763 elsif ( status_code < 0 ) then
764 x_err_code := status_code;
765 return;
766 end if;
767 */
768 -- End Fix 860607
769
770 -- Bug Fix# 773604 02/16/99 Ri. Singh
771 -- Removed check. Percent Complete can exist at any task level.
772 -- Subtask can be created for a task for which pct_complete exists .
773
774 /*
775 -- Check if task has percent complete
776 x_err_stage := 'check percent complete for '|| x_task_id;
777 status_code := check_pct_complete_exists(x_task_id);
778 if ( status_code = 1 ) then
779 x_err_code := 70;
780 x_err_stage := 'PA_TSK_PCT_COMPL_EXIST';
781 return;
782 elsif ( status_code < 0 ) then
783 x_err_code := status_code;
784 return;
785 end if;
786 */
787 -- End of bug fix 773604
788
789 -- Check if task has project asset assignment
790 x_err_stage := 'check asset assignment for '|| x_task_id;
791 status_code :=
792 pa_proj_tsk_utils.check_asset_assignmt_exists(null, x_task_id);
793 if ( status_code = 1 ) then
794 x_err_code := 100;
795 x_err_stage := 'PA_TSK_ASSET_ASSIGNMT_EXIST';
796 return;
797 elsif ( status_code < 0 ) then
798 x_err_code := status_code;
799 return;
800 end if;
801 /* end if; -------Commented for Bug 6063643 */
802
803 -- Get project id
804 x_err_stage := 'get project id of '|| x_task_id;
805 if (x_project_id is null) then
806 x_err_code := 160;
807 x_err_stage := 'PA_NO_PROJECT_ID';
808 return;
809 elsif ( x_top_task_id < 0 ) then
810 x_err_code := x_project_id;
811 return;
812 end if;
813
814 -- get project type class code
815 pa_project_utils.get_proj_type_class_code(null,
816 x_project_id,
817 x_proj_type_class_code,
818 x_err_code,
819 x_err_stage,
820 x_err_stack);
821 if (x_err_code <> 0) then -- Added the if block for bug bug#3512486
822 return;
823 end if;
824
825 if (x_proj_type_class_code = 'CONTRACT' ) then
826
827 -- Check if task has labor cost multiplier
828 x_err_stage := 'check labor cost multiplier for '|| x_task_id;
829 status_code := check_labor_cost_multiplier(x_task_id);
830 if ( status_code = 1 ) then
831 x_err_code := 170;
832 x_err_stage := 'PA_TSK_LABOR_COST_MUL_EXIST';
833 return;
834 elsif ( status_code < 0 ) then
835 x_err_code := status_code;
836 return;
837 end if;
838
839 -- Check if task has job bill rate override
840 x_err_stage := 'check job bill rate override for '|| x_task_id;
841 status_code :=
842 pa_proj_tsk_utils.check_job_bill_rate_override(null, x_task_id);
843 if ( status_code = 1 ) then
844 x_err_code := 180;
845 x_err_stage := 'PA_TSK_JOB_BILL_RATE_O_EXIST';
846 return;
847 elsif ( status_code < 0 ) then
848 x_err_code := status_code;
849 return;
850 end if;
851
852 -- Check if task has emp bill rate override
853 x_err_stage := 'check emp bill rate override for '|| x_task_id;
854 status_code :=
855 pa_proj_tsk_utils.check_emp_bill_rate_override(null, x_task_id);
856 if ( status_code = 1 ) then
857 x_err_code := 190;
858 x_err_stage := 'PA_TSK_EMP_BILL_RATE_O_EXIST';
859 return;
860 elsif ( status_code < 0 ) then
861 x_err_code := status_code;
862 return;
863 end if;
864
865 -- Check if task has labor multiplier
866 x_err_stage := 'check labor multiplier for '|| x_task_id;
867 status_code :=
868 pa_proj_tsk_utils.check_labor_multiplier(null, x_task_id);
869 if ( status_code = 1 ) then
870 x_err_code := 200;
871 x_err_stage := 'PA_TSK_LABOR_MULTIPLIER_EXIST';
872 return;
873 elsif ( status_code < 0 ) then
874 x_err_code := status_code;
875 return;
876 end if;
877
878 -- Check if task has nl bill rate override
879 x_err_stage := 'check nl bill rate override for '|| x_task_id;
880 status_code :=
881 pa_proj_tsk_utils.check_nl_bill_rate_override(null, x_task_id);
882 if ( status_code = 1 ) then
883 x_err_code := 210;
884 x_err_stage := 'PA_TSK_NL_BILL_RATE_O_EXIST';
885 return;
886 elsif ( status_code < 0 ) then
887 x_err_code := status_code;
888 return;
889 end if;
890
891 -- Check if task has job bill title override
892 x_err_stage := 'check job bill title override for '|| x_task_id;
893 status_code :=
894 pa_proj_tsk_utils.check_job_bill_title_override(null, x_task_id);
895 if ( status_code = 1 ) then
896 x_err_code := 230;
897 x_err_stage := 'PA_TSK_JOB_BILL_TITLE_O_EXIST';
898 return;
899 elsif ( status_code < 0 ) then
900 x_err_code := status_code;
901 return;
902 end if;
903
904 -- Check if task has job assignment override
905 x_err_stage := 'check job assignment override for '|| x_task_id;
906 status_code :=
907 pa_proj_tsk_utils.check_job_assignmt_override(null, x_task_id);
908 if ( status_code = 1 ) then
909 x_err_code := 240;
910 x_err_stage := 'PA_TSK_JOB_ASSIGNMENT_O_EXIST';
911 return;
912 elsif ( status_code < 0 ) then
913 x_err_code := status_code;
914 return;
915 end if;
916 end if;
917
918 -- Check if task has expenditure item
919 x_err_stage := 'check expenditure item for '|| x_task_id;
920 status_code :=
921 pa_proj_tsk_utils.check_exp_item_exists(x_project_id, x_task_id, FALSE);
922 if ( status_code = 1 ) then
923 x_err_code := 110;
924 x_err_stage := 'PA_TSK_EXP_ITEM_EXIST';
925 return;
926 elsif ( status_code < 0 ) then
927 x_err_code := status_code;
928 return;
929 end if;
930
931 -- Check if task has purchase order distribution
932 x_err_stage := 'check purchase order for '|| x_task_id;
933 status_code :=
934 pa_proj_tsk_utils.check_po_dist_exists(x_project_id, x_task_id, FALSE); -- 4903460
935 if ( status_code = 1 ) then
936 x_err_code := 120;
937 x_err_stage := 'PA_TSK_PO_DIST_EXIST';
938 return;
939 elsif ( status_code < 0 ) then
940 x_err_code := status_code;
941 return;
942 end if;
943
944 -- Check if task has purchase order requisition
945 x_err_stage := 'check purchase order req for '|| x_task_id;
946 status_code :=
947 pa_proj_tsk_utils.check_po_req_dist_exists(x_project_id, x_task_id, FALSE); -- 4903460
948 if ( status_code = 1 ) then
949 x_err_code := 130;
950 x_err_stage := 'PA_TSK_PO_REQ_DIST_EXIST';
951 return;
952 elsif ( status_code < 0 ) then
953 x_err_code := status_code;
954 return;
955 end if;
956
957 -- Check if task has ap invoice
958 x_err_stage := 'check ap invoice for '|| x_task_id;
959 status_code :=
960 pa_proj_tsk_utils.check_ap_invoice_exists(x_project_id, x_task_id, FALSE); -- 4903460
961 if ( status_code = 1 ) then
962 x_err_code := 140;
963 x_err_stage := 'PA_TSK_AP_INV_EXIST';
964 return;
965 elsif ( status_code < 0 ) then
966 x_err_code := status_code;
967 return;
968 end if;
969
970 -- Check if task has ap invoice distribution
971 x_err_stage := 'check ap inv distribution for '|| x_task_id;
972 status_code :=
973 pa_proj_tsk_utils.check_ap_inv_dist_exists(x_project_id, x_task_id, FALSE); -- 4903460
974 if ( status_code = 1 ) then
975 x_err_code := 150;
976 /*Changed for bug 4069938*/
977 -- x_err_stage := 'PA_TSK_AP_INV_DIST_EXIST';
978 x_err_stage := 'PA_TSK_AP_INV_EXIST';
979 return;
980 elsif ( status_code < 0 ) then
981 x_err_code := status_code;
982 return;
983 end if;
984
985 -- Check if task is considered lowest level task in allocations
986 x_err_stage := 'check if task is lowest in allocations for '|| x_task_id;
987 l_return_val :=
988 pa_alloc_utils.Is_Task_Lowest_In_Allocations(x_task_id);
989 if ( l_return_val = 'Y' ) then
990 x_err_code := 160;
991 x_err_stage := 'PA_TASK_LOW_IN_AllOC';
992 return;
993 end if;
994
995 -- Check if task has draft invoices
996 x_err_stage := 'check draft invoice for '|| x_task_id;
997 status_code :=
998 pa_proj_tsk_utils.check_draft_inv_details_exists(x_task_id,FALSE); -- 4903460
999 if ( status_code = 1 ) then
1000 x_err_code := 170;
1001 x_err_stage := 'PA_TSK_CC_DINV_EXIST';
1002 return;
1003 elsif ( status_code < 0 ) then
1004 x_err_code := status_code;
1005 return;
1006 end if;
1007
1008 -- Check if task has Project_customers
1009 x_err_stage := 'check Project Customers for '|| x_task_id;
1010 status_code :=
1011 pa_proj_tsk_utils.check_project_customer_exists(X_task_id,FALSE); -- 4903460
1012 if ( status_code = 1 ) then
1013 x_err_code := 180;
1014 x_err_stage := 'PA_TSK_CC_CUST_EXIST';
1015 return;
1016 elsif ( status_code < 0 ) then
1017 x_err_code := status_code;
1018 return;
1019 end if;
1020
1021 -- Check if task assign to projects table as a cc_tax_task_id
1022 x_err_stage := 'check task assign to projects table as a cc_tax_task_id '|| x_task_id;
1023 status_code :=
1024 pa_proj_tsk_utils.check_projects_exists(x_task_id,FALSE); -- 4903460
1025 if ( status_code = 1 ) then
1026 x_err_code := 190;
1027 x_err_stage := 'PA_TSK_CC_PROJ_EXIST';
1028 return;
1029 elsif ( status_code < 0 ) then
1030 x_err_code := status_code;
1031 return;
1032 end if;
1033
1034 -- Added for bug 3870364
1035
1036 -- Check if task has an OTL timecard entered against it or not
1037 x_err_stage := 'Check if task has an OTL timecard entered against it or not ,task_id = '|| x_task_id;
1038
1039 PA_OTC_API.ProjectTaskUsed ('TASK', x_task_id, l_OTL_timecard_exists);
1040
1041 if ( l_OTL_timecard_exists ) then
1042 x_err_code := 193;
1043 x_err_stage := 'PA_TSK_OTL_TIMECARD_EXIST';
1044 return;
1045 end if;
1046
1047 -- End of code added for bug 3870364
1048
1049 /* Start of code added for bug 4069938*/
1050
1051 -- Check if task is in used in PJM
1052 x_err_stage := 'check for task used in PJM for'|| x_task_id;
1053 status_code :=
1054 pjm_projtask_deletion.CheckUse_ProjectTask(null, x_task_id);
1055 if ( status_code = 1 ) then
1056 x_err_code := 195;
1057 x_err_stage := 'PA_PROJ_TASK_IN_USE_MFG';
1058 return;
1059 elsif ( status_code = 2 ) THEN
1060 x_err_code := 195;
1061 x_err_stage := 'PA_PROJ_TASK_IN_USE_AUTO';
1062 return;
1063 elsif ( status_code < 0 ) then
1064 x_err_code := status_code;
1065 return;
1066 elsif ( status_code <> 0) then -- Added else condition to display a generic error message.
1067 x_err_code := 195;
1068 x_err_stage := 'PA_PROJ_TASK_IN_USE_EXTERNAL';
1069 return;
1070 end if;
1071
1072 /*End of code added for bug 4069938*/
1073
1074 --Bug 3024607
1075
1076 BEGIN
1077 x_err_stage := 'PA_TASK_PUB1.Check_Task_Has_Association'||x_task_id;
1078
1079 PA_TASK_PUB1.Check_Task_Has_Association(
1080 p_task_id => x_task_id
1081 ,x_return_status => l_return_status
1082 ,x_msg_count => l_msg_count
1083 ,x_msg_data => l_msg_data
1084
1085 );
1086
1087 IF (l_return_status <> 'S') Then
1088 x_err_code := 260;
1089 x_err_stage := pa_project_core1.get_message_from_stack( l_msg_data );
1090 return;
1091 END IF;
1092 EXCEPTION WHEN OTHERS THEN
1093 x_err_stage := 'API PA_TASK_PUB1.Check_Task_Has_Association FAILED';
1094 END;
1095
1096 --End Bug 3024607 changes
1097
1098 --bug 3301192
1099 BEGIN
1100 x_err_stage := 'PA_PROJ_STRUC_MAPPING_UTILS.Check_Task_Has_Mapping'||x_task_id;
1101
1102 l_return_val := PA_PROJ_STRUC_MAPPING_UTILS.Check_Task_Has_Mapping(
1103 p_project_id => x_project_id
1104 , p_proj_element_id => x_task_id );
1105
1106 IF (l_return_val = 'Y') Then
1107 x_err_code := 265;
1108 x_err_stage :='PA_TSK_HAS_MAPPINGS';
1109 return;
1110 END IF;
1111 EXCEPTION WHEN OTHERS THEN
1112 x_err_stage := 'PA_PROJ_STRUC_MAPPING_UTILS.Check_Task_Has_Mapping FAILED';
1113 END;
1114 --end bug 3301192
1115
1116 -- Begin fix for Bug # 4266540.
1117
1118 BEGIN
1119
1120 x_err_stage := 'pa_relationship_utils.check_task_has_sub_proj'||x_task_id;
1121
1122 l_return_val := pa_relationship_utils.check_task_has_sub_proj(x_project_id
1123 , x_task_id
1124 , null);
1125
1126 if (l_return_val = 'Y') then
1127
1128 x_err_code := 270;
1129 x_err_stage := 'PA_PS_TASK_HAS_SUB_PROJ';
1130 return;
1131
1132 end if;
1133
1134 EXCEPTION WHEN OTHERS THEN
1135
1136 x_err_stage := 'pa_task_utils.check_task_has_sub_proj FAILED';
1137
1138 END;
1139
1140 -- End fix for Bug # 4266540.
1141 /* Start changes for Service Intgration bug#16535441*/
1142
1143 x_err_stage := 'check expenditure item for '|| x_project_id;
1144 status_code :=
1145 pa_proj_tsk_utils.check_service_order_exists(x_project_id,
1146 x_task_id);
1147 if ( status_code = 1 ) then
1148 x_err_code := 330;
1149 x_err_stage := 'PA_TSK_SER_ORD_EXIST';
1150 return;
1151 elsif ( status_code < 0 ) then
1152 x_err_code := status_code;
1153 return;
1154 end if;
1155 /* End changes for Service Intgration bug#16535441*/
1156
1157 x_err_stack := old_stack;
1158
1159 exception
1160 when others then
1161 x_err_code := SQLCODE;
1162 return;
1163 end check_create_subtask_ok;
1164
1165 --
1166 -- PROCEDURE
1167 -- change_lowest_task_num_ok
1168 -- PURPOSE
1169 -- This procedure checks if a specific task has expenditure items,
1170 -- Po req distributions,po distributions,ap invoices and ap
1171 -- invoice distributions. If task has any of
1172 -- these information, then it's not ok to change the task number
1173 -- and specific reason will be returned.
1174 -- If it's ok to change task number, the x_err_code will be 0.
1175 --
1176 -- HISTORY
1177 -- 24-FEB-05 djoseph Bug 409938: Changed the value of x_err_stage for
1178 -- pa_proj_tsk_utils.check_ap_inv_dist_exists
1179 -- 10-FEB-99 Ri. Singh Modified as explained below
1180 -- 29-DEC-95 R.Krishnamurthy Created
1181 --
1182 procedure change_lowest_task_num_ok ( x_task_id IN number
1183 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1184 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1185 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1186 IS
1187 old_stack varchar2(630);
1188 status_code number;
1189 x_top_task_id number;
1190 x_project_id number;
1191 BEGIN
1192 x_err_code := 0;
1193 old_stack := x_err_stack;
1194
1195 x_err_stack := x_err_stack || '->change_lowest_task_num_ok';
1196
1197 if (x_task_id is null) then
1198 x_err_code := 10;
1199 x_err_stage := 'PA_NO_TASK_ID';
1200 return;
1201 end if ;
1202
1203 -- Modified 02/10/99 Ri. Singh
1204 -- all the following checks are applicable to lowest level tasks.
1205 -- If the task for which subtask is being created is not the lowest level
1206 -- task, these checks need not be performed. Ref Bug#: 773604
1207
1208 if(check_child_exists(x_task_id)=1) then
1209 x_err_stack := old_stack;
1210 return;
1211 end if;
1212
1213 -- End of fix
1214
1215 x_project_id := pa_proj_tsk_utils.get_task_project_id(x_task_id); -- 4903460
1216 -- Check if task has expenditure item
1217 x_err_stage := 'check expenditure item for '|| x_task_id;
1218 status_code :=
1219 pa_proj_tsk_utils.check_exp_item_exists(x_project_id, x_task_id,FALSE); -- 4903460
1220 if ( status_code = 1 ) then
1221 x_err_code := 20;
1222 x_err_stage := 'PA_TSK_EXP_ITEM_EXIST';
1223 return;
1224 elsif ( status_code < 0 ) then
1225 x_err_code := status_code;
1226 return;
1227 end if;
1228
1229 -- Check if task has purchase order distribution
1230
1231 x_err_stage := 'check purchase order for '|| x_task_id;
1232 status_code :=
1233 pa_proj_tsk_utils.check_po_dist_exists(x_project_id, x_task_id,FALSE); -- 4903460
1234 if ( status_code = 1 ) then
1235 x_err_code := 30;
1236 x_err_stage := 'PA_TSK_PO_DIST_EXIST';
1237 return;
1238 elsif ( status_code < 0 ) then
1239 x_err_code := status_code;
1240 return;
1241 end if;
1242
1243 -- Check if task has purchase order requisition
1244 x_err_stage := 'check purchase order req for '|| x_task_id;
1245 status_code :=
1246 pa_proj_tsk_utils.check_po_req_dist_exists(x_project_id, x_task_id,FALSE); -- 4903460
1247 if ( status_code = 1 ) then
1248 x_err_code := 40;
1249 x_err_stage := 'PA_TSK_PO_REQ_DIST_EXIST';
1250 return;
1251 elsif ( status_code < 0 ) then
1252 x_err_code := status_code;
1253 return;
1254 end if;
1255
1256 -- Check if task has ap invoice
1257 x_err_stage := 'check ap invoice for '|| x_task_id;
1258 status_code :=
1259 pa_proj_tsk_utils.check_ap_invoice_exists(x_project_id, x_task_id,FALSE); -- 4903460
1260 if ( status_code = 1 ) then
1261 x_err_code := 50;
1262 x_err_stage := 'PA_TSK_AP_INV_EXIST';
1263 return;
1264 elsif ( status_code < 0 ) then
1265 x_err_code := status_code;
1266 return;
1267 end if;
1268
1269 -- Check if task has ap invoice distribution
1270 x_err_stage := 'check ap inv distribution for '|| x_task_id;
1271 status_code :=
1272 pa_proj_tsk_utils.check_ap_inv_dist_exists(x_project_id, x_task_id,FALSE); -- 4903460
1273 if ( status_code = 1 ) then
1274 x_err_code := 60;
1275 /*Changed for bug 4069938*/
1276 -- x_err_stage := 'PA_TSK_AP_INV_DIST_EXIST';
1277 x_err_stage := 'PA_TSK_AP_INV_EXIST';
1278 return;
1279 elsif ( status_code < 0 ) then
1280 x_err_code := status_code;
1281 return;
1282 end if;
1283
1284 x_err_stack := old_stack;
1285
1286 exception
1287 when others then
1288 x_err_code := SQLCODE;
1289 return;
1290 end change_lowest_task_num_ok;
1291
1292 /*
1293 --
1294 -- PROCEDURE
1295 -- change_task_org_ok
1296 -- PURPOSE
1297 -- This procedure checks if a specific task has CDLs,RDLs or
1298 -- Draft invoices.If task has any of
1299 -- these information, then it's not ok to change the task org
1300 -- and specific reason will be returned.
1301 -- If it's ok to change task org, the x_err_code will be 0.
1302 --
1303 -- HISTORY
1304 -- 29-DEC-95 R.Krishnamurthy Created
1305 --
1306 procedure change_task_org_ok ( x_task_id IN number
1307 , x_err_code IN OUT number
1308 , x_err_stage IN OUT varchar2
1309 , x_err_stack IN OUT varchar2)
1310 IS
1311
1312 CURSOR Cdl_Cur IS
1313 SELECT NULL
1314 FROM
1315 SYS.DUAL
1316 WHERE EXISTS
1317 (SELECT NULL
1318 FROM
1319 pa_expenditure_items_all paei,
1320 pa_cost_distribution_lines_all cdl
1321 WHERE
1322 paei.expenditure_item_id = cdl.expenditure_item_id
1323 AND paei.task_id = x_task_id );
1324
1325 CURSOR Rdl_Cur IS
1326 SELECT NULL
1327 FROM
1328 SYS.DUAL
1329 WHERE EXISTS
1330 (SELECT NULL
1331 FROM
1332 pa_expenditure_items_all paei,
1333 pa_cust_rev_dist_lines rdl
1334 WHERE
1335 paei.expenditure_item_id = rdl.expenditure_item_id
1336 AND paei.task_id = x_task_id );
1337
1338 CURSOR Draft_Inv_Cur IS
1339 SELECT NULL
1340 FROM
1341 SYS.DUAL
1342 WHERE EXISTS
1343 (SELECT NULL
1344 FROM
1345 pa_draft_invoice_items dii
1346 WHERE dii.Task_id = x_task_id );
1347
1348 V_ret_val Varchar2(1);
1349 old_stack Varchar2(630);
1350
1351 BEGIN
1352 x_err_code := 0;
1353 old_stack := x_err_stack;
1354 x_err_stage := 'Check CDLs for '||x_task_id;
1355
1356 OPEN Cdl_Cur;
1357 FETCH Cdl_Cur INTO V_ret_val;
1358 IF Cdl_Cur%FOUND THEN
1359 x_err_code := 10;
1360 x_err_stage := 'PA_TK_CANT_CHG_TASK_ORG';
1361 CLOSE Cdl_Cur;
1362 return;
1363 ELSE
1364 CLOSE Cdl_Cur;
1365 END IF;
1366
1367 OPEN Rdl_Cur;
1368 FETCH Rdl_Cur INTO V_ret_val;
1369 IF Rdl_Cur%FOUND THEN
1370 x_err_code := 20;
1371 x_err_stage := 'PA_TK_CANT_CHG_TASK_ORG';
1372 CLOSE Rdl_Cur;
1373 return;
1374 ELSE
1375 CLOSE Rdl_Cur;
1376 END IF;
1377
1378 OPEN Draft_Inv_Cur;
1379 FETCH Draft_Inv_Cur INTO V_ret_val;
1380 IF Draft_Inv_Cur%FOUND THEN
1381 x_err_code := 30;
1382 x_err_stage := 'PA_TK_CANT_CHG_TASK_ORG';
1383 CLOSE Draft_Inv_Cur;
1384 return;
1385 ELSE
1386 CLOSE Draft_Inv_Cur;
1387 END IF;
1388
1389 x_err_stack := old_stack;
1390
1391 Exception
1392 WHEN others then
1393 x_err_code := SQLCODE;
1394 return;
1395
1396 End change_task_org_ok;
1397
1398 */
1399
1400
1401 --
1402 -- PROCEDURE
1403 -- change_task_org_ok
1404 -- PURPOSE
1405 -- This procedure checks if a specific task has CDLs,RDLs or
1406 -- ERDLs.If task has any of
1407 -- these information, then it's not ok to change the task org
1408 -- and specific reason will be returned.
1409 -- If it's ok to change task org, the x_err_code will be 0.
1410 --
1411 -- HISTORY
1412 -- 29-DEC-95 R.Krishnamurthy Created
1413 -- 07-MAY-97 Rewrite this whole api to call other exsiting APIs and
1414 -- include erdl.... Charles Fong
1415 --
1416 procedure change_task_org_ok ( x_task_id IN number
1417 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1418 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1419 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1420 IS
1421
1422 old_stack Varchar2(630);
1423 status_code number;
1424
1425 BEGIN
1426 x_err_code := 0;
1427 old_stack := x_err_stack;
1428
1429 x_err_stage := 'Check CDLs for Task '||x_task_id;
1430
1431 status_code := pa_proj_tsk_utils.check_cdl_exists
1432 (Null, x_task_id);
1433
1434 if status_code <> 0 Then
1435 x_err_code := 10;
1436 x_err_stage := 'PA_TK_CANT_CHG_TASK_ORG';
1437 return;
1438 END IF;
1439
1440 -- Check RDLs for the Task
1441
1442 x_err_stage := 'check RDLs for Task '||x_task_id;
1443 status_code := pa_proj_tsk_utils.check_rdl_exists
1444 (Null, x_task_id);
1445
1446 if status_code <> 0 Then
1447 x_err_code := 20;
1448 x_err_stage := 'PA_TK_CANT_CHG_TASK_ORG';
1449 return;
1450 END IF;
1451
1452 -- Check ERDLs for the Task
1453
1454 x_err_stage := 'check ERDLs for Task '||x_task_id;
1455 status_code := pa_proj_tsk_utils.check_erdl_exists
1456 (Null, x_task_id, null);
1457
1458 if status_code <> 0 Then
1459 x_err_code := 30;
1460 x_err_stage := 'PA_TK_CANT_CHG_TASK_ORG';
1461 return;
1462 END IF;
1463
1464
1465 x_err_stack := old_stack;
1466
1467 Exception
1468 WHEN others then
1469 x_err_code := SQLCODE;
1470 return;
1471
1472 End change_task_org_ok;
1473
1474
1475 --
1476 -- PROCEDURE
1477 -- change_task_org_ok2
1478 -- PURPOSE
1479 -- This procedure receives a table of task Ids and org Ids along with other,
1480 -- other parameters , then in turn calls Procedure pa_task_utils.change_task_org_ok1
1481 -- for each set of task Id and org Id.
1482 --
1483 --
1484 -- HISTORY
1485 -- 26-DEC-07 Pallavi Jain Created
1486 --
1487 procedure change_task_org_ok2 ( p_task_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
1488 ,p_project_id IN number
1489 ,p_org_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE := NULL
1490 ,p_commit IN varchar2
1491 ,x_err_stage IN OUT NOCOPY varchar2) --File.Sql.39 GSCC Standard
1492
1493
1494 IS
1495
1496 BEGIN
1497
1498 IF p_task_id_tbl IS NOT NULL AND p_org_id_tbl IS NOT NULL THEN
1499
1500 FOR i IN p_task_id_tbl.FIRST .. p_task_id_tbl.LAST
1501 LOOP
1502 pa_task_utils.change_task_org_ok1(p_task_id_tbl(i),
1503 p_project_id,
1504 p_org_id_tbl(i),
1505 p_commit,
1506 x_err_stage);
1507
1508 END LOOP;
1509
1510 END IF;
1511
1512 Exception
1513 WHEN others then
1514 x_err_stage := 'Error Before Calling Expenditure Items Recalculation';
1515 return;
1516
1517 End change_task_org_ok2;
1518
1519 --
1520 -- PROCEDURE
1521 -- change_task_org_ok1
1522 -- PURPOSE
1523 -- This procedure checks if a specific task has CDLs,RDLs or
1524 -- ERDLs AND IF the user chooses to recalculate the future dated
1525 -- expenditure items for that task, it performs the recalculation.
1526 --
1527 --
1528 -- HISTORY
1529 -- 26-DEC-07 Pallavi Jain Created
1530 --
1531 procedure change_task_org_ok1 ( p_task_id IN number
1532 ,p_project_id IN number
1533 ,p_new_org_id IN number
1534 ,p_commit IN varchar2
1535 ,x_err_stage IN OUT NOCOPY varchar2) --File.Sql.39 GSCC Standard
1536
1537
1538 IS
1539
1540 status_code_cdl NUMBER;
1541 status_code_rdl NUMBER;
1542 status_code_erdl NUMBER;
1543 l_mass_adj_outcome VARCHAR2(30) := NULL;
1544 l_dummy1 NUMBER;
1545 l_dummy2 NUMBER;
1546 l_batch_name VARCHAR2(100);
1547 l_description VARCHAR2(100);
1548 l_batch_id NUMBER(25);
1549 l_row_id VARCHAR2(25);
1550 l_line_id NUMBER;
1551 l_project_currency_code VARCHAR2(15);
1552 l_project_rate_type VARCHAR2(30);
1553 l_project_rate_date DATE;
1554 l_last_update_date DATE;
1555 l_last_updated_by NUMBER(15);
1556 l_last_update_login NUMBER(15);
1557 l_old_org_id NUMBER(15);
1558
1559
1560 cursor c1
1561 is
1562 select project_currency_code,project_rate_type,project_rate_date
1563 from pa_projects_all
1564 where project_id = p_project_id;
1565
1566 cursor c2
1567 is
1568 select meaning
1569 from pa_lookups
1570 where lookup_type = 'TRANSLATION'
1571 and lookup_code = 'MASS_UPDATE_BATCH_DESC';
1572
1573 cursor c3
1574 is
1575 select meaning
1576 from pa_lookups
1577 where lookup_type = 'TRANSLATION'
1578 and lookup_code = 'MANUAL';
1579
1580 cursor c4
1581 is
1582 select last_update_date,last_updated_by,last_update_login,carrying_out_organization_id
1583 from pa_tasks
1584 where task_id = p_task_id
1585 and project_id = p_project_id;
1586
1587 BEGIN
1588
1589 status_code_cdl := pa_proj_tsk_utils.check_cdl_exists
1590 (Null, p_task_id);
1591 status_code_rdl := pa_proj_tsk_utils.check_rdl_exists
1592 (Null, p_task_id);
1593 status_code_erdl := pa_proj_tsk_utils.check_erdl_exists
1594 (Null, p_task_id, null);
1595
1596
1597 OPEN c1;
1598 FETCH c1 INTO l_project_currency_code,l_project_rate_type,l_project_rate_date;
1599 CLOSE c1;
1600
1601 IF (status_code_cdl <> 0 or status_code_rdl <> 0 or status_code_erdl <> 0) THEN
1602 PA_ADJUSTMENTS.MassAdjust(
1603 X_adj_action => 'COST AND REV RECALC',
1604 X_module => 'PAXTUTLB',
1605 X_user => fnd_global.user_id,
1606 X_login => fnd_global.login_id,
1607 X_project_id => p_project_id,
1608 X_dest_prj_id => null,
1609 X_dest_task_id => null,
1610 X_PROJECT_CURRENCY_CODE => l_project_currency_code,
1611 X_PROJECT_RATE_TYPE => l_project_rate_type,
1612 X_PROJECT_RATE_DATE => l_project_rate_date,
1613 X_PROJECT_EXCHANGE_RATE => NULL,
1614 X_task_id => null,
1615 X_inc_by_person_id => null,
1616 X_inc_by_org_id => null,
1617 X_ei_date_low => trunc(sysdate),
1618 X_ei_date_high => null,
1619 X_ex_end_date_low => null,
1620 X_ex_end_date_high => null,
1621 X_system_linkage => null,
1622 X_expenditure_type => null,
1623 X_expenditure_catg => null,
1624 X_expenditure_group => null,
1625 X_vendor_id => null,
1626 X_job_id => null,
1627 X_nl_resource_org_id => null,
1628 X_nl_resource => null,
1629 X_transaction_source => null,
1630 X_cost_distributed_flag => null,
1631 X_revenue_distributed_flag => null,
1632 X_grouped_cip_flag => null,
1633 X_bill_status => null,
1634 X_hold_flag => null,
1635 X_billable_flag => null,
1636 X_capitalizable_flag => null,
1637 X_net_zero_adjust_flag => null,
1638 X_inv_num => null,
1639 X_inv_line_num => null,
1640 X_outcome => l_mass_adj_outcome,
1641 X_num_processed => l_dummy1,
1642 X_num_rejected => l_dummy2 );
1643
1644
1645 OPEN c2;
1646 FETCH c2 INTO l_description;
1647 CLOSE c2;
1648
1649 OPEN c3;
1650 FETCH c3 INTO l_batch_name;
1651 CLOSE c3;
1652
1653 OPEN c4;
1654 FETCH c4 INTO l_last_update_date,l_last_updated_by,l_last_update_login,l_old_org_id;
1655 CLOSE c4;
1656
1657 pa_mu_batches_v_pkg.insert_row (
1658 X_ROWID => l_row_id,
1659 X_BATCH_ID => l_batch_id,
1660 X_CREATION_DATE => l_last_update_date,
1661 X_CREATED_BY => l_last_updated_by,
1662 X_LAST_UPDATED_BY => l_last_updated_by,
1663 X_LAST_UPDATE_DATE => l_last_update_date,
1664 X_LAST_UPDATE_LOGIN => l_last_update_login,
1665 X_BATCH_NAME => l_batch_name,
1666 X_BATCH_STATUS_CODE => 'C',
1667 X_DESCRIPTION => l_description,
1668 X_PROJECT_ATTRIBUTE => 'ORGANIZATION',
1669 X_EFFECTIVE_DATE => trunc(sysdate),
1670 X_ATTRIBUTE_CATEGORY => null,
1671 X_ATTRIBUTE1 => null,
1672 X_ATTRIBUTE2 => null,
1673 X_ATTRIBUTE3 => null,
1674 X_ATTRIBUTE4 => null,
1675 X_ATTRIBUTE5 => null,
1676 X_ATTRIBUTE6 => null,
1677 X_ATTRIBUTE7 => null,
1678 X_ATTRIBUTE8 => null,
1679 X_ATTRIBUTE9 => null,
1680 X_ATTRIBUTE10 => null,
1681 X_ATTRIBUTE11 => null,
1682 X_ATTRIBUTE12 => null,
1683 X_ATTRIBUTE13 => null,
1684 X_ATTRIBUTE14 => null,
1685 X_ATTRIBUTE15 => null
1686 );
1687
1688 update PA_MASS_UPDATE_BATCHES
1689 set batch_name = substr(l_batch_name,1,20)||'-'||to_char(l_batch_id)
1690 where rowid = l_row_id;
1691
1692 pa_mu_details_v_pkg.insert_row
1693 (
1694 X_ROWID => l_row_id,
1695 X_LINE_ID => l_line_id,
1696 X_BATCH_ID => l_batch_id,
1697 X_CREATION_DATE => l_last_update_date,
1698 X_CREATED_BY => l_last_updated_by,
1699 X_LAST_UPDATED_BY => l_last_updated_by,
1700 X_LAST_UPDATE_DATE => l_last_update_date,
1701 X_LAST_UPDATE_LOGIN => l_last_update_login,
1702 X_PROJECT_ID => p_project_id,
1703 X_TASK_ID => p_task_id,
1704 X_OLD_ATTRIBUTE_VALUE => l_old_org_id,
1705 X_NEW_ATTRIBUTE_VALUE => p_new_org_id,
1706 X_UPDATE_FLAG => 'Y',
1707 X_RECALCULATE_FLAG => 'Y'
1708 );
1709
1710 END IF;
1711
1712 IF ( p_commit = 'Y')
1713 THEN
1714 COMMIT;
1715 END IF;
1716
1717 return ;
1718
1719 Exception
1720 WHEN others then
1721 x_err_stage := 'Error on Expenditure Items Recalculation for Task: '||p_task_id;
1722 return;
1723
1724 End change_task_org_ok1;
1725
1726 -- Added below for the fix of Bug 7291217
1727 -- FUNCTION
1728 -- get_resource_list_name
1729 -- PURPOSE
1730 -- This function retrieves the resource list name
1731 -- If no resource_list_id found, null is returned.
1732 -- If Oracle error occurs, Oracle error number is returned.
1733 -- HISTORY
1734 -- 24-JUL-08 Sugupta Created
1735 --
1736
1737 function get_resource_list_name (p_resource_list_id IN number) return
1738 varchar2
1739 is
1740 cursor c1 is
1741 select description
1742 from pa_resource_lists_all_bg
1743 where resource_list_id = p_resource_list_id;
1744
1745 l_rl_name pa_resource_lists_all_bg.description%type ;
1746
1747 begin
1748 open c1;
1749 fetch c1 into l_rl_name;
1750 if c1%notfound then
1751 close c1;
1752 return(null);
1753 else
1754 close c1;
1755 return( l_rl_name );
1756 end if;
1757
1758 exception
1759 when others then
1760 return(SQLCODE);
1761
1762 end get_resource_list_name;
1763
1764 --
1765 -- PROCEDURE
1766 -- check_delete_task_ok
1767 -- PURPOSE
1768 -- This objective of this API is to check if the task is
1769 -- referenced by other tables.
1770 --
1771 -- To delete a top task and its subtasks, the following
1772 -- requirements must be met:
1773 -- * No event at top level task
1774 -- * No funding at top level tasks
1775 -- * No budget at top level task
1776 -- * Meet the following requirements for its children
1777 --
1778 -- To delete a mid level task, it involves checking its
1779 -- children and meeting the following requirements for
1780 -- its lowest level task.
1781 --
1782 -- To delete a lowest level task, the following requirements
1783 -- must be met:
1784 -- * No expenditure item at lowest level task
1785 -- * No puchase order line at lowest level task
1786 -- * No requisition line at lowest level task
1787 -- * No supplier invoice (ap invoice) at lowest level task
1788 -- * No budget at lowest level task
1789 --
1790 -- A task cannot be deleted if it is used in Allocations
1791 --
1792 -- HISTORY
1793 -- 14-AUG-02 vejayara Bug# 2331201 - Financial planning development
1794 -- changes - Added pa_fp_elements in the existence check
1795 -- in check_delete_task_ok
1796 -- 29-MAY-02 gjain added a call to pa_proj_tsk_utils.check_iex_task_charged
1797 -- for bug 2367945
1798 -- 22-JAN-02 bvarnasi After all the checks for EI,PO etc. are done,we
1799 -- need not check for the existance of cc_tax_task_id
1800 -- as there can not be any cross charge transactions
1801 -- for tasks that do not have any other transactions.
1802 -- 16-FEB-99 Ri. Singh Added call to check_pct_complete_exists
1803 -- 04-JAN-96 S. Lee Created
1804 --
1805 procedure check_delete_task_ok (x_task_id IN number
1806 , x_validation_mode IN VARCHAR2 DEFAULT 'U' --bug 2947492
1807 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
1808 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
1809 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
1810 is
1811
1812 old_stack varchar2(630);
1813 status_code number;
1814 l_return_val varchar2(1);
1815 x_top_task_id number;
1816
1817 ----Bug 2947492
1818 l_return_status varchar2(1);
1819 l_msg_count NUMBER;
1820 l_msg_data VARCHAR2(4000);
1821 ----Bug 2947492
1822
1823 cursor p1 is select 1 from pa_project_types
1824 where burden_sum_dest_task_id = x_task_id;
1825 temp number;
1826 --Ansari
1827 x_used_in_OTL BOOLEAN; --To pass to OTL API.
1828 --Ansari
1829 --local variable Is_IEX_Installed added for bug 2367945
1830 Is_IEX_Installed BOOLEAN;
1831 x_project_id number;
1832
1833 -- Progress Management Changes. Bug # 3420093.
1834 l_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE;
1835 -- Progress Management Changes. Bug # 3420093.
1836
1837 -- Bug 3662930
1838
1839 l_exists NUMBER;
1840 l_ship_exists VARCHAR2(1);
1841 l_proc_exists VARCHAR2(1);
1842
1843 CURSOR c_bill_event_exists(x_project_id IN NUMBER)
1844 IS
1845 SELECT count(1) from pa_events ev
1846 WHERE ev.project_id = x_project_id
1847 AND nvl(ev.task_id, -1) = x_task_id ;
1848 -- Bug 3662930
1849
1850 begin
1851 x_err_code := 0;
1852 old_stack := x_err_stack;
1853
1854 x_err_stack := x_err_stack || '->check_delete_task_ok';
1855
1856 -- Check task id
1857 if (x_task_id is null) then
1858 x_err_code := 10;
1859 x_err_stage := 'PA_NO_TASK_ID';
1860 return;
1861 end if ;
1862
1863 open p1;
1864 fetch p1 into temp;
1865 if p1%notfound then null;
1866 else
1867 x_err_code := 260;
1868 x_err_stage := 'PA_TASK_BURDEN_SUM_DEST';
1869 return;
1870 end if;
1871
1872 -- get top task id
1873 x_err_stage := 'get top task id for task '|| x_task_id;
1874 x_top_task_id := pa_task_utils.get_top_task_id(x_task_id);
1875
1876 if ( x_top_task_id < 0 ) then -- Oracle error
1877 x_err_code := x_top_task_id;
1878 return;
1879 end if;
1880
1881 if (x_task_id = x_top_task_id) then
1882 -- x_task_id is a top task
1883 -- Check if task has event
1884 x_err_stage := 'check event for '|| x_task_id;
1885 status_code :=
1886 pa_proj_tsk_utils.check_event_exists(null, x_task_id);
1887 if ( status_code = 1 ) then
1888 x_err_code := 30;
1889 x_err_stage := 'PA_TSK_EVENT_EXIST';
1890 return;
1891 elsif ( status_code < 0 ) then
1892 x_err_code := status_code;
1893 return;
1894 end if;
1895
1896 -- Check if task has funding
1897 x_err_stage := 'check funding for '|| x_task_id;
1898 status_code :=
1899 pa_proj_tsk_utils.check_funding_exists(null, x_task_id);
1900 if ( status_code = 1 ) then
1901 x_err_code := 40;
1902 x_err_stage := 'PA_TSK_FUND_EXIST';
1903 return;
1904 elsif ( status_code < 0 ) then
1905 x_err_code := status_code;
1906 return;
1907 end if;
1908 end if;
1909
1910
1911 /* bug 2947492
1912 Commented out the code out of here. Please see HLD for budgeting and Forecasting.
1913
1914 -- Check if task has been selected for planning at any level
1915 x_err_stage := 'check pa_fp_elements for task '|| x_task_id;
1916 declare
1917 cursor c1 is
1918 SELECT 1
1919 FROM sys.dual
1920 where exists (SELECT NULL
1921 FROM pa_fp_elements r
1922 where r.TASK_ID IN
1923 (SELECT TASK_ID
1924 FROM PA_TASKS
1925 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1926 START WITH TASK_ID = x_task_id));
1927
1928 c1_rec c1%rowtype;
1929 begin
1930 open c1;
1931 fetch c1 into c1_rec;
1932 if c1%notfound then
1933 close c1; -- this task is not part of the planning options
1934 else
1935 close c1;
1936 x_err_code := 90;
1937 x_err_stage := 'PA_FP_TSK_ELEMENTS_EXISTS';
1938 return;
1939 end if;
1940 exception
1941 when others then
1942 close c1;
1943 x_err_code := SQLCODE;
1944 return;
1945 end;
1946
1947 -- Check if task has any budget; both top and lowest level tasks
1948 x_err_stage := 'check budget for task '|| x_task_id;
1949 declare
1950 cursor c1 is
1951 SELECT 1
1952 FROM sys.dual
1953 where exists (SELECT NULL
1954 FROM pa_resource_assignments r
1955 where r.TASK_ID IN
1956 (SELECT TASK_ID
1957 FROM PA_TASKS
1958 CONNECT BY PRIOR TASK_ID = PARENT_TASK_ID
1959 START WITH TASK_ID = x_task_id));
1960
1961 c1_rec c1%rowtype;
1962 begin
1963 open c1;
1964 fetch c1 into c1_rec;
1965 if c1%notfound then
1966 close c1; -- no budget. continue
1967 else
1968 close c1;
1969 x_err_code := 100;
1970 x_err_stage := 'PA_TSK_BUDGET_EXIST';
1971 return;
1972 end if;
1973 exception
1974 when others then
1975 close c1;
1976 x_err_code := SQLCODE;
1977 return;
1978 end;
1979 */
1980 -- Start of code for Performance Fix 4903460
1981 -- All commented validations in this API for this perf fix are done in
1982 -- the following API : PA_PROJ_ELEMENTS_UTILS.perform_task_validations
1983
1984 -- Added the following api call to get the project_id and pass it to perform_task_validations. Done for Bug#4964992
1985 l_project_id := pa_proj_tsk_utils.get_task_project_id(x_task_id);
1986 -- End of changes for Bug#4964992
1987
1988 PA_PROJ_ELEMENTS_UTILS.perform_task_validations
1989 (
1990 p_project_id => l_project_id
1991 ,p_task_id => x_task_id
1992 ,x_error_code => x_err_code
1993 ,x_error_msg_code => x_err_stage
1994 );
1995
1996 IF x_err_code <> 0 THEN
1997 IF x_err_code < 0 THEN
1998 -- this is Unexpected error case
1999 -- Hence ,Get the x_err_stage from Pa_Debug.g_err_stage to know exact cause
2000 x_err_stage := Pa_Debug.g_err_stage ;
2001 END IF;
2002 -- Other case is > 0 case for which proper message code would have
2003 -- been populated in x_err_stage ,from x_error_msg_code OUT param of above API
2004 return;
2005 END IF;
2006 -- End of new code for Performance fix 4903460
2007
2008 -- Start of commenting for Performance Fix 4903460
2009 -- Check if task has expenditure item
2010 /*x_err_stage := 'check expenditure item for '|| x_task_id;
2011 status_code :=
2012 pa_proj_tsk_utils.check_exp_item_exists(null, x_task_id);
2013 if ( status_code = 1 ) then
2014 x_err_code := 50;
2015 x_err_stage := 'PA_TSK_EXP_ITEM_EXIST';
2016 return;
2017 elsif ( status_code < 0 ) then
2018 x_err_code := status_code;
2019 return;
2020 end if;
2021
2022 -- Check if task has purchase order distribution
2023 x_err_stage := 'check purchase order for '|| x_task_id;
2024 status_code :=
2025 pa_proj_tsk_utils.check_po_dist_exists(null, x_task_id);
2026 if ( status_code = 1 ) then
2027 x_err_code := 60;
2028 x_err_stage := 'PA_TSK_PO_DIST_EXIST';
2029 return;
2030 elsif ( status_code < 0 ) then
2031 x_err_code := status_code;
2032 return;
2033 end if;
2034
2035 -- Check if task has purchase order requisition
2036 x_err_stage := 'check purchase order requisition for '|| x_task_id;
2037 status_code :=
2038 pa_proj_tsk_utils.check_po_req_dist_exists(null, x_task_id);
2039 if ( status_code = 1 ) then
2040 x_err_code := 70;
2041 x_err_stage := 'PA_TSK_PO_REQ_DIST_EXIST';
2042 return;
2043 elsif ( status_code < 0 ) then
2044 x_err_code := status_code;
2045 return;
2046 end if;
2047
2048 -- Check if task has supplier invoices
2049 x_err_stage := 'check supplier invoice for '|| x_task_id;
2050 status_code :=
2051 pa_proj_tsk_utils.check_ap_invoice_exists(null, x_task_id);
2052 if ( status_code = 1 ) then
2053 x_err_code := 80;
2054 x_err_stage := 'PA_TSK_AP_INV_EXIST';
2055 return;
2056 elsif ( status_code < 0 ) then
2057 x_err_code := status_code;
2058 return;
2059 end if;
2060
2061 -- Check if task has supplier invoice distribution
2062 x_err_stage := 'check supplier inv distribution for '|| x_task_id;
2063 status_code :=
2064 pa_proj_tsk_utils.check_ap_inv_dist_exists(null, x_task_id);
2065 if ( status_code = 1 ) then
2066 x_err_code := 90;
2067 x_err_stage := 'PA_TSK_AP_INV_DIST_EXIST';
2068 return;
2069 elsif ( status_code < 0 ) then
2070 x_err_code := status_code;
2071 return;
2072 end if;
2073
2074 -- Check if task has commitment transaction
2075 x_err_stage := 'check commitment transaction for '|| x_task_id;
2076 status_code :=
2077 pa_proj_tsk_utils.check_commitment_txn_exists(null, x_task_id);
2078 if ( status_code = 1 ) then
2079 x_err_code := 110;
2080 x_err_stage := 'PA_TSK_CMT_TXN_EXIST';
2081 return;
2082 elsif ( status_code < 0 ) then
2083 x_err_code := status_code;
2084 return;
2085 end if;
2086
2087 -- Check if task has compensation rule set
2088 x_err_stage := 'check compensation rule set for '|| x_task_id;
2089 status_code :=
2090 pa_proj_tsk_utils.check_comp_rule_set_exists(null, x_task_id);
2091 if ( status_code = 1 ) then
2092 x_err_code := 120;
2093 x_err_stage := 'PA_TSK_COMP_RULE_SET_EXIST';
2094 return;
2095 elsif ( status_code < 0 ) then
2096 x_err_code := status_code;
2097 return;
2098 end if;
2099 -- ENd of commenting for Performance Fix 4903460
2100 */
2101 -- Check if task is in use in an external system
2102 x_err_stage := 'check for task used in external system for'|| x_task_id;
2103 status_code :=
2104 pjm_projtask_deletion.CheckUse_ProjectTask(null, x_task_id);
2105 if ( status_code = 1 ) then
2106 x_err_code := 130;
2107 /* Commented the existing error message and modified it to 'PA_PROJ_TASK_IN_USE_MFG' as below for bug 3600806
2108 x_err_stage := 'PA_PROJ_IN_USE_EXTERNAL'; */
2109 x_err_stage := 'PA_PROJ_TASK_IN_USE_MFG';
2110 return;
2111 elsif ( status_code = 2 ) THEN -- Added elseif condition for bug 3600806.
2112 x_err_code := 130;
2113 x_err_stage := 'PA_PROJ_TASK_IN_USE_AUTO';
2114 return;
2115 elsif ( status_code < 0 ) then
2116 x_err_code := status_code;
2117 return;
2118 elsif ( status_code <> 0) then -- Added else condition for bug 3600806 to display a generic error message.
2119 x_err_code := 130;
2120 x_err_stage := 'PA_PROJ_TASK_IN_USE_EXTERNAL';
2121 return;
2122 end if;
2123
2124 -- Check if task is used in allocations
2125 x_err_stage := 'check if project allocations uses task '|| x_task_id;
2126 l_return_val :=
2127 pa_alloc_utils.Is_Task_In_Allocations(x_task_id);
2128 if ( l_return_val = 'Y' ) then
2129 x_err_code := 140;
2130 x_err_stage := 'PA_TASK_IN_ALLOC';
2131 return;
2132 end if;
2133
2134 -- Bug Fix# 773604 02/16/99 Ri. Singh
2135 -- Task cannot be deleted if percent complete exists for any
2136 -- task in the WBS below the task being deleted.
2137
2138 -- Progress Management Changes. Bug # 3420093.
2139
2140 -- Check if task has progress
2141 x_err_stage := 'check object has progress for '|| x_task_id;
2142
2143 --l_project_id := pa_proj_tsk_utils.get_task_project_id(x_task_id); Commented this line as we have already retrieved the project_id of the task above for Bug#4964992
2144
2145 if (pa_progress_utils.check_object_has_prog(p_project_id => l_project_id
2146 -- ,p_proj_element_id => x_task_id
2147 , p_object_id => x_task_id
2148 ,p_structure_type => 'FINANCIAL') = 'Y') then
2149 x_err_code := 150;
2150 x_err_stage := 'PA_TSK_PCT_COMPL_EXIST';
2151 return;
2152 end if;
2153
2154 -- Progress Management Changes. Bug # 3420093.
2155
2156 -- End of fix 773604
2157
2158 /* Start of Commenting for Performance Fix 4903460
2159 -- Check if task has draft invoices
2160 x_err_stage := 'check draft invoice for '|| x_task_id;
2161 status_code :=
2162 pa_proj_tsk_utils.check_draft_inv_details_exists(x_task_id);
2163 if ( status_code = 1 ) then
2164 x_err_code := 160;
2165 x_err_stage := 'PA_TSK_CC_DINV_EXIST';
2166 return;
2167 elsif ( status_code < 0 ) then
2168 x_err_code := status_code;
2169 return;
2170 end if;
2171
2172 -- Check if task has Project_customers
2173 x_err_stage := 'check Project Customers for '|| x_task_id;
2174 status_code :=
2175 pa_proj_tsk_utils.check_project_customer_exists(x_task_id);
2176 if ( status_code = 1 ) then
2177 x_err_code := 170;
2178 x_err_stage := 'PA_TSK_CC_CUST_EXIST';
2179 return;
2180 elsif ( status_code < 0 ) then
2181 x_err_code := status_code;
2182 return;
2183 end if;
2184 -- End of Commenting for Performance Fix 4903460
2185 */
2186
2187 /* Commented for Bug # 2185521.
2188 -- Check if task assign to projects table as a cc_tax_task_id
2189 x_err_stage := 'check task assign to projects table as a cc_tax_task_id '|| x_task_id;
2190 status_code :=
2191 pa_proj_tsk_utils.check_projects_exists(x_task_id);
2192 if ( status_code = 1 ) then
2193 x_err_code := 180;
2194 x_err_stage := 'PA_TSK_CC_PROJ_EXIST';
2195 return;
2196 elsif ( status_code < 0 ) then
2197 x_err_code := status_code;
2198 return;
2199 end if;
2200 Comment ends : Bug # 2185521 .*/
2201
2202 -- HSIU added.
2203 -- Check if project contract is installed
2204 IF (pa_install.is_product_installed('OKE')) THEN
2205 x_err_stage := 'Check contract association for task '||x_task_id;
2206 IF (PA_PROJ_STRUCTURE_PUB.CHECK_TASK_CONTRACT_ASSO(x_task_id) <>
2207 FND_API.G_RET_STS_SUCCESS) THEN
2208 x_err_code := 190;
2209 x_err_stage := 'PA_STRUCT_TK_HAS_CONTRACT';
2210 return;
2211 END IF;
2212 END IF;
2213 -- Finished checking if project contract is installed.
2214 --Ansari
2215 --Check to see if the task has been used in OTL--Added by Ansari
2216 x_err_stage := 'Check OTL task exception';
2217 PA_OTC_API.ProjectTaskUsed( p_search_attribute => 'TASK',
2218 p_search_value => x_task_id,
2219 x_used => x_used_in_OTL );
2220 --If exists in OTL
2221 IF x_used_in_OTL
2222 THEN
2223 x_err_code := 200;
2224 x_err_stage := 'PA_TSK_EXP_ITEM_EXIST';
2225 return;
2226 END IF;
2227
2228 --end of OTL check.
2229 --Ansari
2230
2231 --fix for bug2367945 starts
2232 Is_IEX_Installed := pa_install.is_product_installed('IEX');
2233 If Is_IEX_Installed then
2234 x_err_stage := 'check if task '|| x_task_id || ' is charged in iexpense';
2235 status_code := pa_proj_tsk_utils.check_iex_task_charged(x_task_id);
2236 if ( status_code = 1 ) then
2237 x_err_code := 210;
2238 x_err_stage := 'PA_TSK_EXP_ITEM_EXIST';
2239 return;
2240 elsif ( status_code < 0 ) then
2241 x_err_code := status_code;
2242 return;
2243 end if;
2244 end if;
2245 --fix for bug2367945 ends
2246
2247 --Bug 2947492
2248
2249 BEGIN
2250 x_err_stage := 'PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK'||x_task_id;
2251
2252 PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK(
2253 p_task_id => x_task_id
2254 ,p_validation_mode => x_validation_mode
2255 ,x_return_status => l_return_status
2256 ,x_msg_count => l_msg_count
2257 ,x_msg_data => l_msg_data
2258
2259 );
2260
2261 IF (l_return_status <> 'S') Then
2262 x_err_code := 220;
2263 x_err_stage := pa_project_core1.get_message_from_stack( l_msg_data );
2264 return;
2265 END IF;
2266 EXCEPTION WHEN OTHERS THEN
2267 x_err_stage := 'API PA_FIN_PLAN_UTILS.CHECK_DELETE_TASK_OK FAILED';
2268 END;
2269
2270 --End Bug 2947492 changes
2271
2272 --bug 3301192
2273 --Bug 3617393
2274 DECLARE
2275 CURSOR get_task_project_id(c_task_id IN NUMBER) IS
2276 SELECT project_id
2277 FROM pa_proj_elements
2278 WHERE proj_element_id = c_task_id;
2279 BEGIN
2280
2281 x_err_stage := 'PA_PROJ_STRUC_MAPPING_UTILS.Check_Task_Has_Mapping'||x_task_id;
2282
2283 -- Get project id
2284 x_err_stage := 'get project id of '|| x_task_id;
2285 --Bug 3617393 : Retrieve project id from pa_proj_elements rather than pa_tasks since
2286 --the data has already been deleted from pa_tasks in delete_project flow
2287 /*x_project_id :=
2288 pa_proj_tsk_utils.get_task_project_id(x_task_id);*/
2289 OPEN get_task_project_id(x_task_id);
2290 FETCH get_task_project_id INTO x_project_id;
2291 CLOSE get_task_project_id;
2292 --Bug 3617393 end
2293
2294 if (x_project_id is null) then
2295 x_err_code := 160;
2296 x_err_stage := 'PA_NO_PROJECT_ID';
2297 return;
2298 elsif ( x_top_task_id < 0 ) then
2299 x_err_code := x_project_id;
2300 return;
2301 end if;
2302
2303 l_return_val := PA_PROJ_STRUC_MAPPING_UTILS.Check_Task_Has_Mapping(
2304 p_project_id => x_project_id
2305 , p_proj_element_id => x_task_id );
2306
2307 IF (l_return_val = 'Y') Then
2308 x_err_code := 230;
2309 x_err_stage :='PA_TSK_HAS_MAPPINGS';
2310 return;
2311 END IF;
2312
2313 --Bug 3662930 Deletion of Financial Task should not be allowed
2314 -- If it has transactions through billing events ,initiated shipping transactions,initiated procurement transactions etc
2315
2316 -- Check (1)
2317 -- Check for Existence of transactions through Billing Events
2318
2319 x_err_stage := 'Check for Transactions through Billing Events for ' || x_task_id ;
2320 OPEN c_bill_event_exists(x_project_id) ;
2321 FETCH c_bill_event_exists INTO l_exists ;
2322 CLOSE c_bill_event_exists ;
2323
2324 IF (l_exists > 0) THEN
2325 x_err_code := 250 ;
2326 x_err_stage := 'PA_FIN_TASK_BILL_TXN_EXISTS' ;
2327 return;
2328 END IF ;
2329
2330
2331 --Check (2)
2332 -- Check for Existence of transactions through initiated shipping transactions,initiated procurement transactions
2333
2334 x_err_stage := 'Check for initiated shipping transactions for ' ||x_task_id ;
2335 l_ship_exists := OKE_DELIVERABLE_UTILS_PUB.Task_Used_In_Wsh(
2336 P_Task_ID => x_task_id
2337 ) ;
2338 IF (l_ship_exists = 'Y') THEN
2339 x_err_code := 260 ;
2340 x_err_stage := 'PA_FIN_TASK_SHIP_TXN_EXISTS' ;
2341 return;
2342 END IF ;
2343
2344 x_err_stage := 'Check for initiated procurement transactions for ' ||x_task_id ;
2345 l_proc_exists :=OKE_DELIVERABLE_UTILS_PUB.Task_Used_In_Req(
2346 P_Task_ID => x_task_id
2347 ) ;
2348 IF (l_proc_exists = 'Y') THEN
2349 x_err_code := 270 ;
2350 x_err_stage := 'PA_FIN_TASK_PROC_TXN_EXISTS' ;
2351 return;
2352 END IF ;
2353 --End Bug 3662930
2354 EXCEPTION WHEN OTHERS THEN
2355 x_err_stage := 'PA_PROJ_STRUC_MAPPING_UTILS.Check_Task_Has_Mapping FAILED';
2356 END;
2357 --end bug 3301192
2358
2359 x_err_stack := old_stack;
2360
2361 exception
2362 when others then
2363 x_err_code := SQLCODE;
2364 --hsiu: commenting out rollback because this API should only be checking for
2365 -- errors. Rollback causes P1s with savepoint issues
2366 -- rollback;
2367 return;
2368 end check_delete_task_ok;
2369
2370 --
2371 -- FUNCTION
2372 -- sort_order_tree_walk
2373 -- PURPOSE
2374 -- This function does a reverse tree walk in the pa_task table
2375 -- to set up a sort order using input parent_task_id and
2376 -- task_number.
2377 -- If Oracle error occurs, Oracle error number is returned.
2378 --
2379 -- HISTORY
2380 -- 12-DEC-96 Charles Fong Created
2381 --
2382 function sort_order_tree_walk(x_parent_id IN number, x_sort_order_col IN varchar2) return varchar2
2383 is
2384
2385 cursor c1 (inid number) is
2386 select task_number parent_task_number
2387 from pa_tasks
2388 connect by prior parent_task_id = task_id
2389 start with task_id = c1.inid;
2390
2391 rv varchar2(2000) := x_sort_order_col;
2392
2393 begin
2394 for c1rec in c1(x_parent_id) loop
2395
2396 -- String length should not exceed 2000
2397 if 2000-length(rv) - length(c1rec.parent_task_number) >0 then
2398 rv := c1rec.parent_task_number||rv;
2399 else
2400 return rv;
2401 end if;
2402
2403 end loop;
2404 return rv;
2405 exception
2406 when others then
2407 return(SQLCODE);
2408
2409 end sort_order_tree_walk;
2410
2411
2412 --
2413 -- FUNCTION
2414 -- check_child_exists
2415 -- PURPOSE
2416 -- This function checks whether the task has any child or not and
2417 -- return 1 or 0 accordingly.
2418 -- If Oracle error occurs, Oracle error number is returned.
2419 --
2420 -- HISTORY
2421 -- 12-DEC-96 Charles Fong Created
2422 --
2423 function check_child_exists(x_task_id IN number) return number
2424 is
2425
2426 cursor c1 is
2427 select 1
2428 from sys.dual
2429 where exists (SELECT null
2430 FROM pa_tasks
2431 where parent_task_id = x_task_id);
2432
2433 c1_rec c1%rowtype;
2434
2435 begin
2436 if (x_task_id is null) then
2437 return(null);
2438 end if;
2439
2440 open c1;
2441 fetch c1 into c1_rec;
2442 IF c1%notfound THEN
2443 close c1;
2444 return(0);
2445 ELSE
2446 close c1;
2447 return(1);
2448 END IF;
2449
2450 EXCEPTION
2451 WHEN OTHERS THEN
2452 RETURN(SQLCODE);
2453 END check_child_exists;
2454
2455 --rtarway, 3908013, procedure to validate flex fields
2456
2457 PROCEDURE validate_flex_fields(
2458 p_desc_flex_name IN VARCHAR2
2459 ,p_attribute_category IN VARCHAR2 := null
2460 ,p_attribute1 IN VARCHAR2 := null
2461 ,p_attribute2 IN VARCHAR2 := null
2462 ,p_attribute3 IN VARCHAR2 := null
2463 ,p_attribute4 IN VARCHAR2 := null
2464 ,p_attribute5 IN VARCHAR2 := null
2465 ,p_attribute6 IN VARCHAR2 := null
2466 ,p_attribute7 IN VARCHAR2 := null
2467 ,p_attribute8 IN VARCHAR2 := null
2468 ,p_attribute9 IN VARCHAR2 := null
2469 ,p_attribute10 IN VARCHAR2 := null
2470 ,p_attribute11 IN VARCHAR2 := null
2471 ,p_attribute12 IN VARCHAR2 := null
2472 ,p_attribute13 IN VARCHAR2 := null
2473 ,p_attribute14 IN VARCHAR2 := null
2474 ,p_attribute15 IN VARCHAR2 := null
2475 ,p_RETURN_msg OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
2476 ,p_validate_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2477 IS
2478 l_dummy VARCHAR2(1);
2479 l_r VARCHAR2(2000);
2480 BEGIN
2481
2482 -- DEFINE ID COLUMNS
2483 fnd_flex_descval.set_context_value(p_attribute_category);
2484 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1);
2485 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attribute2);
2486 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attribute3);
2487 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attribute4);
2488 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attribute5);
2489 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attribute6);
2490 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attribute7);
2491 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attribute8);
2492 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attribute9);
2493 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attribute10);
2494 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_attribute11);
2495 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_attribute12);
2496 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_attribute13);
2497 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_attribute14);
2498 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_attribute15);
2499
2500 -- VALIDATE
2501 IF (fnd_flex_descval.validate_desccols( 'PA',p_desc_flex_name)) then
2502 p_RETURN_msg := 'VALID: ' || fnd_flex_descval.concatenated_ids;
2503 p_validate_status := 'Y';
2504 ELSE
2505 p_RETURN_msg := 'INVALID: ' || fnd_flex_descval.error_message;
2506 p_validate_status := 'N';
2507 END IF;
2508 EXCEPTION -- 4537865
2509 WHEN OTHERS THEN
2510 p_validate_status := 'N';
2511 Fnd_Msg_Pub.add_exc_msg
2512 ( p_pkg_name => 'PA_TASK_UTILS'
2513 , p_procedure_name => 'validate_flex_fields'
2514 , p_error_text => substrb(sqlerrm,1,100));
2515 RAISE ;
2516 END validate_flex_fields;
2517 --End rtarway, 3908013, procedure to validate flex fields
2518
2519
2520 --
2521 -- PROCEDURE
2522 -- check_set_nonchargeable_ok
2523 -- PURPOSE
2524 -- This procedure checks if a specific task has PO distributions,
2525 -- PO requisition distributions, AP invoice distributions
2526 -- and also if it is referenced in PJM. If the task has any of
2527 -- these information, then it's not ok to make the task nonchargeable
2528 -- and the specific reason will be returned.
2529 -- If it's ok to make the task nonchargeable, the x_err_code will be 0.
2530 --
2531 -- HISTORY
2532 --
2533 -- 24-FEB-05 Derrin Joseph Created for bug 4069938
2534 --
2535 procedure check_set_nonchargeable_ok ( x_task_id IN number
2536 , x_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
2537 , x_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
2538 , x_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
2539 IS
2540 old_stack varchar2(630);
2541 status_code number;
2542 x_top_task_id number;
2543 x_project_id number;
2544 BEGIN
2545 x_err_code := 0;
2546 old_stack := x_err_stack;
2547
2548 x_err_stack := x_err_stack || '->check_set_nonchargeable_ok';
2549
2550 if (x_task_id is null) then
2551 x_err_code := 10;
2552 x_err_stage := 'PA_NO_TASK_ID';
2553 return;
2554 end if ;
2555
2556
2557 -- All the following checks are applicable only to lowest level tasks.
2558 -- Hence if this is not a lowest level task these checks need not be performed.
2559
2560 if(check_child_exists(x_task_id)=1) then
2561 x_err_stack := old_stack;
2562 return;
2563 end if;
2564
2565 -- Check if the task has purchase order distributions
2566
2567 x_err_stage := 'check purchase order for '|| x_task_id;
2568 status_code :=
2569 pa_proj_tsk_utils.check_po_dist_exists(null, x_task_id);
2570 if ( status_code = 1 ) then
2571 x_err_code := 30;
2572 x_err_stage := 'PA_TSK_PO_DIST_EXIST';
2573 return;
2574 elsif ( status_code < 0 ) then
2575 x_err_code := status_code;
2576 return;
2577 end if;
2578
2579 -- Check if the task has purchase order requisitions
2580 x_err_stage := 'check purchase order req for '|| x_task_id;
2581 status_code :=
2582 pa_proj_tsk_utils.check_po_req_dist_exists(null, x_task_id);
2583 if ( status_code = 1 ) then
2584 x_err_code := 40;
2585 x_err_stage := 'PA_TSK_PO_REQ_DIST_EXIST';
2586 return;
2587 elsif ( status_code < 0 ) then
2588 x_err_code := status_code;
2589 return;
2590 end if;
2591
2592 -- Check if task has ap invoice distributions
2593 x_err_stage := 'check ap inv distribution for '|| x_task_id;
2594 status_code :=
2595 pa_proj_tsk_utils.check_ap_inv_dist_exists(null, x_task_id);
2596 if ( status_code = 1 ) then
2597 x_err_code := 60;
2598 x_err_stage := 'PA_TSK_AP_INV_EXIST';
2599 return;
2600 elsif ( status_code < 0 ) then
2601 x_err_code := status_code;
2602 return;
2603 end if;
2604
2605 -- Check if task is in used in PJM
2606 x_err_stage := 'check for task used in PJM for'|| x_task_id;
2607 status_code :=
2608 pjm_projtask_deletion.CheckUse_ProjectTask(null, x_task_id);
2609 if ( status_code = 1 ) then
2610 x_err_code := 195;
2611 x_err_stage := 'PA_PROJ_TASK_IN_USE_MFG';
2612 return;
2613 elsif ( status_code = 2 ) THEN
2614 x_err_code := 195;
2615 x_err_stage := 'PA_PROJ_TASK_IN_USE_AUTO';
2616 return;
2617 elsif ( status_code < 0 ) then
2618 x_err_code := status_code;
2619 return;
2620 elsif ( status_code <> 0) then -- Added else condition to display a generic error message.
2621 x_err_code := 195;
2622 x_err_stage := 'PA_PROJ_TASK_IN_USE_EXTERNAL';
2623 return;
2624 end if;
2625
2626 x_err_stack := old_stack;
2627
2628 exception
2629 when others then
2630 x_err_code := SQLCODE;
2631 return;
2632 end check_set_nonchargeable_ok;
2633
2634 --
2635 -- FUNCTION
2636 -- is_flex_field_defined
2637 -- PURPOSE
2638 -- This function checks whether the flex filed is defined for task or not and
2639 -- and return Y or N accordingly.
2640 --
2641 -- HISTORY
2642 --
2643 -- 23-Jul-12 Amit Sharma Created for bug Usability Enhancement
2644 function is_flex_field_defined(p_project_id IN PA_PROJ_ELEM_VER_SCHEDULE.PROJECT_ID%TYPE
2645 ,p_element_version_id IN PA_PROJ_ELEM_VER_SCHEDULE.ELEMENT_VERSION_ID%TYPE
2646 ,p_proj_element_id IN PA_PROJ_ELEM_VER_SCHEDULE.PROJ_ELEMENT_ID%TYPE) RETURN VARCHAR2
2647 is
2648 CURSOR c1
2649 IS
2650 SELECT 1
2651 FROM dual
2652 WHERE EXISTS
2653 (SELECT NULL
2654 FROM PA_PROJ_ELEM_VER_SCHEDULE
2655 WHERE PROJECT_ID = p_project_id
2656 AND ELEMENT_VERSION_ID = p_element_version_id
2657 AND PROJ_ELEMENT_ID = p_proj_element_id
2658 AND (attribute_category IS NOT NULL
2659 OR attribute1 IS NOT NULL
2660 OR attribute2 IS NOT NULL
2661 OR attribute3 IS NOT NULL
2662 OR attribute4 IS NOT NULL
2663 OR attribute5 IS NOT NULL
2664 OR attribute6 IS NOT NULL
2665 OR attribute7 IS NOT NULL
2666 OR attribute8 IS NOT NULL
2667 OR attribute9 IS NOT NULL
2668 OR attribute10 IS NOT NULL
2669 OR attribute11 IS NOT NULL
2670 OR attribute12 IS NOT NULL
2671 OR attribute13 IS NOT NULL
2672 OR attribute14 IS NOT NULL
2673 OR ATTRIBUTE15 IS NOT NULL));
2674 c1_rec c1%rowtype;
2675 begin
2676 if (p_project_id is NULL OR p_element_version_id IS NULL OR p_proj_element_id IS null) then
2677 return('N');
2678 end if;
2679 open c1;
2680 fetch c1 into c1_rec;
2681 IF c1%notfound THEN
2682 close c1;
2683 return('N');
2684 ELSE
2685 close c1;
2686 return('Y');
2687 END IF;
2688
2689 EXCEPTION
2690 WHEN OTHERS THEN
2691 RETURN('N');
2692 END is_flex_field_defined;
2693
2694 END PA_TASK_UTILS;
[Home] [Help]