1 package body JTF_AGENDA_CALCULATIONS as
2 /*$Header: JTFAGCAB.pls 120.2 2005/09/01 22:09:44 pseo ship $*/
3
4 -- get attributes of the task assignment prior to the current
5 -- task assignment, dependant on the type of the current task, that is
6 -- for an arrival virtual task all tasks in the shift, for a departure
7 -- nothing (because this is the first 'task' in the shift), and for a
8 -- real task all tasks before the current task including the departure
9 procedure get_prior_task_assignment
10 ( p_res_id in number
11 , p_res_type in varchar2
12 , p_shift_start in date
13 , p_shift_end in date
14 , p_sched_start in date
15 , p_ta_id in number
16 , p_task_type_id in number
17 , x_prior_ta_id out NOCOPY number
18 , x_prior_sched_end out NOCOPY date
19 , x_prior_found out NOCOPY boolean
20 )
21 is
22 -- get tasks for this resource in this shift which are before current
23 -- task or is the departure virtual task
24 cursor c_prior
25 ( p_res_id number
26 , p_res_type varchar2
27 , p_shift_start date
28 , p_shift_end date
29 , p_sched_start date
30 , p_ta_id number
31 )
32 is
33 select task_assignment_id
34 , scheduled_end_date
35 from jtf_agenda_ta_v
36 where resource_id = p_res_id
37 and resource_type_code = p_res_type
38 and shift_start = p_shift_start
39 and shift_end = p_shift_end
40 and ( task_type_id = 20 -- departure task
41 or ( task_type_id not in (20,21) -- real tasks
42 and ( scheduled_start_date < p_sched_start
43 or ( scheduled_start_date = p_sched_start
44 and task_assignment_id < p_ta_id ) ) ) )
45 order by decode(task_type_id,20,1,0) -- departure task last
46 , scheduled_start_date desc
47 , task_assignment_id desc;
48
49 -- get all tasks for this resource in this shift but without arrival
50 cursor c_prior_all
51 ( p_res_id number
52 , p_res_type varchar2
53 , p_shift_start date
54 , p_shift_end date
55 )
56 is
57 select task_assignment_id
58 , scheduled_end_date
59 from jtf_agenda_ta_v
60 where resource_id = p_res_id
61 and resource_type_code = p_res_type
62 and shift_start = p_shift_start
63 and shift_end = p_shift_end
64 and task_type_id <> 21 -- not arrival task
65 order by decode(task_type_id,20,1,0) -- departure task last
66 , scheduled_start_date desc
67 , task_assignment_id desc;
68
69 begin
70 x_prior_ta_id := null;
71 x_prior_sched_end := null;
72 x_prior_found := false;
73
74 -- real task
75 if p_task_type_id not in (20,21)
76 then
77 open c_prior
78 ( p_res_id
79 , p_res_type
80 , p_shift_start
81 , p_shift_end
82 , p_sched_start
83 , p_ta_id
84 );
85 fetch c_prior
86 into x_prior_ta_id
87 , x_prior_sched_end;
88
89 if c_prior%found
90 then
91 x_prior_found := true;
92 end if;
93 close c_prior;
94
95 -- virtual arrival task
96 elsif p_task_type_id = 21
97 then
98 open c_prior_all
99 ( p_res_id
100 , p_res_type
101 , p_shift_start
102 , p_shift_end
103 );
104 fetch c_prior_all
105 into x_prior_ta_id
106 , x_prior_sched_end;
107
108 if c_prior_all%found
109 then
110 x_prior_found := true;
111 end if;
112 close c_prior_all;
113 end if;
114 end get_prior_task_assignment;
115
116 function predict_time_difference
117 (
118 p_task_assignment_id number
119 )
120 return number
121 is
122 l_diff number := 0;
123 l_sched_start date := null;
124 l_sched_end date := null;
125 l_actua_start date := null;
126 l_actua_end date := null;
127 l_sched_travel number := 0;
128 l_res_id number := null;
129 l_res_type varchar2(30) := null;
130 l_shift_start date := null;
131 l_shift_end date := null;
132 l_prior_ta_id number := null;
133 l_prior_sched_end date := null;
134 l_min_start date := null;
135 l_free number := 0;
136 l_bmode varchar2(30) := null;
137 l_plan_start date := null;
138 l_plan_end date := null;
139 l_task_type_id number := null;
140 l_prior_found boolean := false;
141
142 cursor c_this ( p_ta_id number )
143 is
144 select scheduled_start_date
145 , scheduled_end_date
146 , actual_start_date
147 , actual_end_date
148 , sched_travel_duration
149 , resource_id
150 , resource_type_code
151 , shift_start
152 , shift_end
153 , bound_mode_code
154 , planned_start_date
155 , planned_end_date
156 , task_type_id
157 from jtf_agenda_ta_v
158 where task_assignment_id = p_ta_id;
159
160 begin
161 open c_this ( p_task_assignment_id );
162 fetch c_this
163 into l_sched_start
164 , l_sched_end
165 , l_actua_start
166 , l_actua_end
167 , l_sched_travel
168 , l_res_id
169 , l_res_type
170 , l_shift_start
171 , l_shift_end
172 , l_bmode
173 , l_plan_start
174 , l_plan_end
175 , l_task_type_id;
176
177 if c_this%found
178 then
179 -- validate shift
180 if l_shift_start is null
181 or l_shift_end is null
182 or l_shift_end < l_shift_start
183 then
184 -- exit
185 return 0;
186 end if;
187
188 -- compute difference
189 if l_actua_end is not null
190 then
191 l_diff := l_actua_end - l_sched_end;
192
193 elsif l_actua_start is not null
194 then
195 l_diff := l_actua_start - l_sched_start;
196 if sysdate > l_sched_end + l_diff
197 then
198 l_diff := sysdate - l_sched_end;
199 end if;
200
201 -- no actual dates are found, get the previous task in this trip to find
202 -- an actual date
203 else
204 get_prior_task_assignment ( l_res_id
205 , l_res_type
206 , l_shift_start
207 , l_shift_end
208 , l_sched_start
209 , p_task_assignment_id
210 , l_task_type_id
211 , l_prior_ta_id
212 , l_prior_sched_end
213 , l_prior_found );
214
215 if l_prior_found
216 then
217 -- this is a recursive function!
218 l_diff := predict_time_difference ( l_prior_ta_id );
219
220 -- no previous task found, this is the first task of the trip, take
221 -- system date into account
222 else
223 if sysdate > l_sched_start
224 then
225 l_diff := sysdate - l_sched_start;
226 end if;
227 end if;
228
229 -- validate travel time attributes
230 if l_sched_travel is null
231 or l_sched_travel < 0
232 then
233 l_sched_travel := 0;
234 end if;
235
236 -- compute minimal time resource has to leave in order to arrive
237 -- in time to start task (unit of measurement is minute)
238 l_min_start := l_sched_start - ( l_sched_travel / 1440 );
239
240 -- correct difference by amount of not scheduled, free time
241 l_free := l_min_start - nvl( l_prior_sched_end, l_shift_start );
242 l_diff := l_diff - l_free;
243
244 -- correct for time bounds
245 if l_bmode = 'BTS'
246 and l_plan_end >= l_plan_start
247 -- makes no sense for virtual tasks departure and arrival
248 and l_task_type_id not in (20,21)
249 then
250 if ( l_sched_start + l_diff ) < l_plan_start
251 then
252 l_diff := l_plan_start - l_sched_start;
253 end if;
254 end if;
255 end if;
256 end if;
257 close c_this;
258
259 return l_diff;
260 end predict_time_difference;
261
262 function set_sequence_flag
263 (
264 p_task_assignment_id number
265 )
266 return varchar2
267 is
268 l_flag varchar2(1) := 'N';
269 l_res_id number := null;
270 l_res_type varchar2(30) := null;
271 l_shift_start date := null;
272 l_shift_end date := null;
273 l_sched_start date := null;
274
275 cursor c_this ( p_ta_id number )
276 is
277 select scheduled_start_date
278 , resource_id
279 , resource_type_code
280 , shift_start
281 , shift_end
282 from jtf_agenda_ta_v
283 where task_assignment_id = p_ta_id
284 and actual_start_date is null
285 and actual_end_date is null;
286
287 cursor c_next
288 ( p_res_id number
289 , p_res_type varchar2
290 , p_shift_start date
291 , p_shift_end date
292 , p_sched_start date
293 , p_ta_id number
294 )
295 is
296 select 'Y'
297 from jtf_agenda_ta_v
298 where resource_id = p_res_id
299 and resource_type_code = p_res_type
300 and shift_start = p_shift_start
301 and shift_end = p_shift_end
302 and ( scheduled_start_date > p_sched_start
303 or ( scheduled_start_date = p_sched_start
304 and task_assignment_id > p_ta_id ) )
305 and nvl(actual_start_date,actual_end_date) is not null;
306
307 begin
308 open c_this ( p_task_assignment_id );
309 fetch c_this
310 into l_sched_start
311 , l_res_id
312 , l_res_type
313 , l_shift_start
314 , l_shift_end;
315
316 if c_this%found
317 then
318 open c_next
319 ( l_res_id
320 , l_res_type
321 , l_shift_start
322 , l_shift_end
323 , l_sched_start
324 , p_task_assignment_id
325 );
326 fetch c_next
327 into l_flag;
328
329 close c_next;
330 end if;
331 close c_this;
332
333 return l_flag;
334 end set_sequence_flag;
335
336 function get_progress_status
337 ( p_resource_id number
338 , p_resource_type_code varchar2
339 , p_date date
340 )
341 return number
342 is
343 -- get all escalated tasks in current trip
344 cursor c_esc
345 ( p_res_id number
346 , p_res_type varchar2
347 , p_date date
348 )
349 is
350 select ''
351 from jtf_agenda_v
352 where resource_id = p_res_id
353 and resource_type_code = p_res_type
354 and p_date between shift_start and shift_end
355 and escalation_flag = 'Y';
356
357 cursor c_max
358 (
359 p_res_id number
360 , p_res_type varchar2
361 , p_date date
362 )
363 is
364 select max(predicted_end_date)
365 , max(shift_end)
366 from jtf_agenda_v
367 where resource_id = p_res_id
368 and resource_type_code = p_res_type
369 and p_date between shift_start and shift_end
370 and task_type_id not in (20,21);
371
372 l_chk varchar2(1);
373 l_max_pred_end date := null;
374 l_shift_end date := null;
375 l_dif number := null;
376 l_uom constant number := 1440; /* unit of measurement is minutes */
377 l_margin number;
378
379 begin
380 /* see if any task in current trip is escalated */
381 open c_esc ( p_resource_id
382 , p_resource_type_code
383 , p_date );
384 fetch c_esc into l_chk;
385 if c_esc%found
386 then
387 close c_esc;
388 return 4; /* escalated */
389 end if;
390 close c_esc;
391
392 /* get highest predicted end date within trip */
393 open c_max
394 ( p_resource_id
395 , p_resource_type_code
396 , p_date
397 );
398 fetch c_max
399 into l_max_pred_end
400 , l_shift_end;
401
402 /* calculate difference with shift end */
403 l_dif := ( l_shift_end - l_max_pred_end ) * l_uom;
404
405 if c_max%found
406 and l_dif is not null
407 then
408 /* get margin profile option */
409 l_margin := to_number( fnd_profile.value(
410 'CSF_RESOURCE_PROGRESS_STATUS') );
411 if l_margin is null
412 or sqlcode <> 0
413 then
414 l_margin := 60; /* default value (60 minutes) */
415 end if;
416
417 close c_max;
418
419 if l_dif < ( l_margin * -1 )
420 then
421 return 3; /* behind schedule */
422 elsif l_dif > l_margin
423 then
424 return 1; /* ahead of schedule */
425 end if;
426 return 2; /* on schedule */
427 end if;
428 close c_max;
429
430 return 0; /* unknown */
431 end get_progress_status;
432
433 function get_assignment_status
434 (
435 p_resource_id number
436 , p_resource_type_code varchar2
437 )
438 return number
439 is
440 cursor c_currsta
441 is
442 select assignment_status_id
443 from jtf_agenda_ta_v
444 where resource_id = p_resource_id
445 and resource_type_code = p_resource_type_code
446 and task_type_id not in (20,21)
447 and ( actual_start_date =
448 ( select max( actual_start_date )
449 from jtf_agenda_ta_v
450 where resource_id = p_resource_id
451 and resource_type_code = p_resource_type_code
452 and task_type_id not in (20,21) )
453 or actual_start_date is null )
454 order by scheduled_start_date
455 , task_assignment_id;
456
457 l_status_id number;
458 begin
459 open c_currsta;
460 fetch c_currsta into l_status_id;
461 if c_currsta%notfound
462 then
463 l_status_id := null;
464 end if;
465 close c_currsta;
466
467 return l_status_id;
468 end get_assignment_status;
469
470 function get_status_name
471 (
472 p_status_id number
473 )
474 return varchar2
475 is
476 cursor c_name
477 is
478 select name
479 from jtf_task_statuses_vl
480 where task_status_id = p_status_id;
481 l_name varchar2(30);
482 begin
483 open c_name;
484 fetch c_name into l_name;
485 if c_name%notfound
486 then
487 l_name := null;
488 end if;
489 close c_name;
490
491 return l_name;
492 end get_status_name;
493
494 function get_current_task
495 (
496 p_resource_id number
497 , p_resource_type_code varchar2
498 )
499 return number
500 is
501 cursor c_currtask
502 is
503 select task_id
504 from jtf_agenda_ta_v
505 where resource_id = p_resource_id
506 and resource_type_code = p_resource_type_code
507 and task_type_id not in (20,21)
508 and ( actual_start_date =
509 ( select max( actual_start_date )
510 from jtf_agenda_ta_v
511 where resource_id = p_resource_id
512 and resource_type_code = p_resource_type_code
513 and task_type_id not in (20,21) )
514 or actual_start_date is null )
515 order by scheduled_start_date
516 , task_assignment_id;
517
518 l_task_id number;
519 begin
520 open c_currtask;
521 fetch c_currtask into l_task_id;
522 if c_currtask%notfound
523 then
524 l_task_id := null;
525 end if;
526 close c_currtask;
527
528 return l_task_id;
529 end get_current_task;
530
531 FUNCTION get_shift_start
532 ( p_shift_construct_id NUMBER DEFAULT NULL
533 , p_resource_id NUMBER DEFAULT NULL
534 , p_resource_type_code VARCHAR2 DEFAULT NULL
535 , p_date DATE DEFAULT NULL
536 )
537 RETURN DATE
538 IS
539 l_start DATE;
540 l_time NUMBER;
541 l_return_status VARCHAR2(1);
542 l_msg_count NUMBER;
543 l_msg_data VARCHAR2(2000);
544 l_shift JTF_CALENDAR_PUB.SHIFT_TBL_TYPE;
545
546 CURSOR c_shift_construct IS
547 SELECT begin_time
548 FROM jtf_cal_shift_constructs
549 WHERE shift_construct_id = p_shift_construct_id;
550
551 r_shift_construct c_shift_construct%ROWTYPE;
552
553 BEGIN
554 OPEN c_shift_construct;
555 FETCH c_shift_construct INTO r_shift_construct;
556 IF c_shift_construct%FOUND THEN
557 l_time := r_shift_construct.begin_time -
558 TRUNC(r_shift_construct.begin_time);
559 l_start := TRUNC(p_date) + l_time;
560 END IF;
561 CLOSE c_shift_construct;
562
563 -- No shift start was found using shift_construct_id
564 IF l_start IS NULL THEN
565 JTF_CALENDAR_PUB.Get_Resource_Shifts
566 ( p_api_version => 1.0 ,
567 p_resource_id => p_resource_id ,
568 p_resource_type => p_resource_type_code ,
569 p_start_date => p_date ,
570 p_end_date => p_date ,
571 x_return_status => l_return_status ,
572 x_msg_count => l_msg_count ,
573 x_msg_data => l_msg_data ,
574 x_shift => l_shift
575 );
576
577 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
578 l_start := TO_DATE(l_shift(1).start_time, 'hh24:mi');
579 l_time := l_start - TRUNC(l_start);
580 l_start := TRUNC(p_date) + l_time;
581 END IF;
582 END IF;
583
584 -- Temporary if Calendar DB objects are invalid
585 -- l_start := TRUNC(p_date) + ( 8 / 24 );
586 -- IF l_start > p_date THEN
587 -- l_start := l_start - 1;
588 -- END IF;
589
590 RETURN l_start;
591 END get_shift_start;
592
593 FUNCTION get_shift_end
594 ( p_shift_construct_id NUMBER DEFAULT NULL
595 , p_resource_id NUMBER DEFAULT NULL
596 , p_resource_type_code VARCHAR2 DEFAULT NULL
597 , p_date DATE DEFAULT NULL
598 )
599 RETURN DATE
600 IS
601 l_end DATE;
602 l_time NUMBER;
603 l_return_status VARCHAR2(1);
604 l_msg_count NUMBER;
605 l_msg_data VARCHAR2(2000);
606
607 l_shift JTF_CALENDAR_PUB.SHIFT_TBL_TYPE;
608
609 CURSOR c_shift_construct IS
610 SELECT end_time
611 FROM jtf_cal_shift_constructs
612 WHERE shift_construct_id = p_shift_construct_id;
613
614 r_shift_construct c_shift_construct%ROWTYPE;
615
616 BEGIN
617 OPEN c_shift_construct;
618 FETCH c_shift_construct INTO r_shift_construct;
619 IF c_shift_construct%FOUND THEN
620 l_time := r_shift_construct.end_time - TRUNC(r_shift_construct.end_time);
621 l_end := TRUNC(p_date) + l_time;
622 END IF;
623 CLOSE c_shift_construct;
624
625 -- No shift end was found using shift_construct_id
626 IF l_end IS NULL THEN
627 JTF_CALENDAR_PUB.Get_Resource_Shifts
628 ( p_api_version => 1.0 ,
629 p_resource_id => p_resource_id ,
630 p_resource_type => p_resource_type_code ,
631 p_start_date => p_date ,
632 p_end_date => p_date ,
633 x_return_status => l_return_status ,
634 x_msg_count => l_msg_count ,
635 x_msg_data => l_msg_data ,
636 x_shift => l_shift
637 );
638
639 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
640 l_end := TO_DATE(l_shift(1).end_time, 'hh24:mi');
641 l_time := l_end - TRUNC(l_end);
642 l_end := TRUNC(p_date) + l_time;
643 END IF;
644 END IF;
645
646 -- Temporary if Calendar DB objects are invalid
647 -- l_end := ( get_shift_start( p_shift_construct_id,
648 -- p_resource_id,
649 -- p_resource_type_code,
650 -- p_date
651 -- ) + ( 9 / 24 ) );
652
653 RETURN l_end;
654 END get_shift_end;
655
656 FUNCTION set_escalation_flag
657 (
658 p_task_id NUMBER
659 )
660 RETURN VARCHAR2
661 IS
662 l_return_value VARCHAR2(1) := 'N';
663 l_object_type_code VARCHAR2(30) := 'TASK';
664 l_object_id NUMBER := p_task_id;
665
666 CURSOR c_esc IS
667 SELECT NULL
668 FROM jtf_tasks_b t,
669 jtf_task_references_vl r,
670 jtf_ec_statuses_vl s
671 WHERE t.task_id = r.task_id
672 AND t.task_type_id = 22
673 AND t.task_status_id = s.task_status_id
674 AND (s.closed_flag = 'N'
675 OR s.closed_flag is null)
676 AND (s.completed_flag = 'N'
677 OR s.completed_flag is null)
678 AND (s.cancelled_flag = 'N'
679 OR s.cancelled_flag is null)
680 AND r.reference_code = 'ESC'
681 AND r.object_type_code = l_object_type_code
682 AND r.object_id = l_object_id;
683
684 CURSOR c_tsk IS
685 SELECT t.source_object_type_code,
686 t.source_object_id
687 FROM jtf_tasks_b t,
688 jtf_task_statuses_vl s
689 WHERE t.task_id = p_task_id
690 AND t.task_status_id = s.task_status_id
691 AND (s.closed_flag = 'N'
692 OR s.closed_flag is null)
693 AND (s.completed_flag = 'N'
694 OR s.completed_flag is null)
695 AND (s.cancelled_flag = 'N'
696 OR s.cancelled_flag is null);
697 r_esc c_esc%ROWTYPE;
698 r_tsk c_tsk%ROWTYPE;
699 BEGIN
700 -- Check if Task is escalated. Ignore completed/cancelled status
701 -- of Task.
702 OPEN c_esc;
703 FETCH c_esc INTO r_esc;
704 IF c_esc%FOUND THEN
705 l_return_value := 'Y';
706 END IF;
707
708 CLOSE c_esc;
709
710 -- If Task is not escalated then check if Service Request is
711 -- escalated. Only Tasks which are not completed/cancelled can be
712 -- escalated if the Service Request is escalated
713 IF l_return_value = 'N' THEN
714 OPEN c_tsk;
715 FETCH c_tsk INTO r_tsk;
716 IF c_tsk%FOUND THEN
717 l_object_type_code := r_tsk.source_object_type_code;
718 l_object_id := r_tsk.source_object_id;
719
720 OPEN c_esc;
721 FETCH c_esc INTO r_esc;
722 IF c_esc%FOUND THEN
723 l_return_value := 'Y';
724 END IF;
725 CLOSE c_esc;
726 END IF;
727 CLOSE c_tsk;
728 END IF;
729
730 RETURN l_return_value;
731 END set_escalation_flag;
732
733 end JTF_AGENDA_CALCULATIONS;
[Home] [Help]