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