1 PACKAGE BODY csf_map_pvt AS
2 /* $Header: CSFVMAPB.pls 120.0 2005/09/15 21:36:44 sseshaiy noship $ */
3 FUNCTION set_escalation_flag (p_task_id NUMBER)
4 RETURN VARCHAR2
5 IS
6 l_return_value VARCHAR2 (1);
7 l_object_type_code VARCHAR2 (30);
8 l_object_id NUMBER;
9
10 CURSOR c_esc
11 IS
12 SELECT NULL
13 FROM jtf_tasks_b t, jtf_task_references_vl r, jtf_ec_statuses_vl s
14 WHERE t.task_id = r.task_id
15 AND t.task_type_id = 22
16 AND t.task_status_id = s.task_status_id
17 AND (s.closed_flag = 'N' OR s.closed_flag IS NULL)
18 AND (s.completed_flag = 'N' OR s.completed_flag IS NULL)
19 AND (s.cancelled_flag = 'N' OR s.cancelled_flag IS NULL)
20 AND r.reference_code = 'ESC'
21 AND r.object_type_code = l_object_type_code
22 AND r.object_id = l_object_id;
23
24 CURSOR c_tsk
25 IS
26 SELECT t.source_object_type_code,
27 t.source_object_id
28 FROM jtf_tasks_b t, jtf_task_statuses_vl s
29 WHERE t.task_id = p_task_id
30 AND t.task_status_id = s.task_status_id
31 AND (s.closed_flag = 'N' OR s.closed_flag IS NULL)
32 AND (s.completed_flag = 'N' OR s.completed_flag IS NULL)
33 AND (s.cancelled_flag = 'N' OR s.cancelled_flag IS NULL);
34
35 r_esc c_esc%ROWTYPE;
36 r_tsk c_tsk%ROWTYPE;
37 BEGIN
38 -- Check if Task is escalated. Ignore completed/cancelled status
39 -- of Task.
40 l_return_value := 'N';
41 l_object_type_code := 'TASK';
42 l_object_id := p_task_id;
43
44 OPEN c_esc;
45
46 FETCH c_esc
47 INTO r_esc;
48
49 IF c_esc%FOUND
50 THEN
51 l_return_value := 'Y';
52 END IF;
53
54 CLOSE c_esc;
55
56 -- If Task is not escalated then check if Service Request is
57 -- escalated. Only Tasks which are not completed/cancelled can be
58 -- escalated if the Service Request is escalated
59 IF l_return_value = 'N'
60 THEN
61 OPEN c_tsk;
62
63 FETCH c_tsk
64 INTO r_tsk;
65
66 IF c_tsk%FOUND
67 THEN
68 l_object_type_code := r_tsk.source_object_type_code;
69 l_object_id := r_tsk.source_object_id;
70
71 OPEN c_esc;
72
73 FETCH c_esc
74 INTO r_esc;
75
76 IF c_esc%FOUND
77 THEN
78 l_return_value := 'Y';
79 END IF;
80
81 CLOSE c_esc;
82 END IF;
83
84 CLOSE c_tsk;
85 END IF;
86
87 RETURN l_return_value;
88 END set_escalation_flag;
89
90 -- get attributes of the task assignment prior to the current
91 -- task assignment, dependant on the type of the current task, that is
92 -- for an arrival virtual task all tasks in the shift, for a departure
93 -- nothing (because this is the first 'task' in the shift), and for a
94 -- real task all tasks before the current task including the departure
95 PROCEDURE get_prior_task_assignment (
96 p_res_id IN NUMBER,
97 p_res_type IN VARCHAR2,
98 p_shift_start IN DATE,
99 p_shift_end IN DATE,
100 p_sched_start IN DATE,
101 p_ta_id IN NUMBER,
102 p_task_type_id IN NUMBER,
103 x_prior_ta_id OUT NOCOPY NUMBER,
104 x_prior_sched_end OUT NOCOPY DATE,
105 x_prior_found OUT NOCOPY BOOLEAN
106 )
107 IS
108 -- get tasks for this resource in this shift which are before current
109 -- task or is the departure virtual task
110 CURSOR c_prior (
111 p_res_id NUMBER,
112 p_res_type VARCHAR2,
113 p_shift_start DATE,
114 p_shift_end DATE,
115 p_sched_start DATE,
116 p_ta_id NUMBER
117 )
118 IS
119 SELECT task_assignment_id,
120 scheduled_end_date
121 FROM jtf_tasks_b jtb,
122 jtf_task_assignments jta,
123 cac_sr_object_capacity cso
124 WHERE jta.resource_id = p_res_id
125 AND jta.resource_type_code = p_res_type
126 AND jta.object_capacity_id = cso.object_capacity_id
127 AND jta.task_id = jtb.task_id
128 AND cso.start_date_time = p_shift_start
129 AND cso.end_date_time = p_shift_end
130 AND ( jtb.task_type_id = 20 -- departure task
131 OR ( jtb.task_type_id NOT IN (20, 21) -- real tasks
132 AND ( jtb.scheduled_start_date < p_sched_start
133 OR ( jtb.scheduled_start_date = p_sched_start
134 AND jta.task_assignment_id < p_ta_id
135 )
136 )
137 )
138 )
139 ORDER BY DECODE (jtb.task_type_id, 20, 1, 0) -- departure task last
140 ,
141 jtb.scheduled_start_date DESC,
142 jta.task_assignment_id DESC;
143
144 -- get all tasks for this resource in this shift but without arrival
145 CURSOR c_prior_all (
146 p_res_id NUMBER,
147 p_res_type VARCHAR2,
148 p_shift_start DATE,
149 p_shift_end DATE
150 )
151 IS
152 SELECT jta.task_assignment_id,
153 jtb.scheduled_end_date
154 FROM jtf_tasks_b jtb,
155 jtf_task_assignments jta,
156 cac_sr_object_capacity cso
157 WHERE jta.resource_id = p_res_id
158 AND jta.resource_type_code = p_res_type
159 AND jta.object_capacity_id = cso.object_capacity_id
160 AND jta.task_id = jtb.task_id
161 AND cso.start_date_time = p_shift_start
162 AND cso.end_date_time = p_shift_end
163 AND jtb.task_type_id <> 21 -- not arrival task
164 ORDER BY DECODE (jtb.task_type_id, 20, 1, 0) -- departure task last
165 ,
166 jtb.scheduled_start_date DESC,
167 jta.task_assignment_id DESC;
168 BEGIN
169 x_prior_ta_id := NULL;
170 x_prior_sched_end := NULL;
171 x_prior_found := FALSE;
172
173 -- real task
174 IF p_task_type_id NOT IN (20, 21)
175 THEN
176 OPEN c_prior (p_res_id,
177 p_res_type,
178 p_shift_start,
179 p_shift_end,
180 p_sched_start,
181 p_ta_id
182 );
183
184 FETCH c_prior
185 INTO x_prior_ta_id,
186 x_prior_sched_end;
187
188 IF c_prior%FOUND
189 THEN
190 x_prior_found := TRUE;
191 END IF;
192
193 CLOSE c_prior;
194 -- virtual arrival task
195 ELSIF p_task_type_id = 21
196 THEN
197 OPEN c_prior_all (p_res_id, p_res_type, p_shift_start, p_shift_end);
198
199 FETCH c_prior_all
200 INTO x_prior_ta_id,
201 x_prior_sched_end;
202
203 IF c_prior_all%FOUND
204 THEN
205 x_prior_found := TRUE;
206 END IF;
207
208 CLOSE c_prior_all;
209 END IF;
210 END get_prior_task_assignment;
211
212 FUNCTION predict_time_difference (p_task_assignment_id NUMBER)
213 RETURN NUMBER
214 IS
215 l_diff NUMBER := 0;
216 l_sched_start DATE := NULL;
217 l_sched_end DATE := NULL;
218 l_actua_start DATE := NULL;
219 l_actua_end DATE := NULL;
220 l_sched_travel NUMBER := 0;
221 l_res_id NUMBER := NULL;
222 l_res_type VARCHAR2 (30) := NULL;
223 l_shift_start DATE := NULL;
224 l_shift_end DATE := NULL;
225 l_prior_ta_id NUMBER := NULL;
226 l_prior_sched_end DATE := NULL;
227 l_min_start DATE := NULL;
228 l_free NUMBER := 0;
229 l_bmode VARCHAR2 (30) := NULL;
230 l_plan_start DATE := NULL;
231 l_plan_end DATE := NULL;
232 l_task_type_id NUMBER := NULL;
233 l_prior_found BOOLEAN := FALSE;
234
235 CURSOR c_this (p_ta_id NUMBER)
236 IS
237 SELECT jtb.scheduled_start_date,
238 jtb.scheduled_end_date,
239 jtb.actual_start_date,
240 jtb.actual_end_date,
241 jta.sched_travel_duration,
242 jta.resource_id,
243 jta.resource_type_code,
244 cso.start_date_time,
245 cso.end_date_time,
246 jtb.bound_mode_code,
247 jtb.planned_start_date,
248 jtb.planned_end_date,
249 jtb.task_type_id
250 FROM jtf_tasks_b jtb,
251 jtf_task_assignments jta,
252 cac_sr_object_capacity cso
253 WHERE jta.object_capacity_id = cso.object_capacity_id
254 AND jta.task_id = jtb.task_id
255 AND jta.task_assignment_id = p_ta_id;
256 BEGIN
257 OPEN c_this (p_task_assignment_id);
258
259 FETCH c_this
260 INTO l_sched_start,
261 l_sched_end,
262 l_actua_start,
263 l_actua_end,
264 l_sched_travel,
265 l_res_id,
266 l_res_type,
267 l_shift_start,
268 l_shift_end,
269 l_bmode,
270 l_plan_start,
271 l_plan_end,
272 l_task_type_id;
273
274 IF c_this%FOUND
275 THEN
276 -- validate shift
277 IF l_shift_start IS NULL
278 OR l_shift_end IS NULL
279 OR l_shift_end < l_shift_start
280 THEN
281 -- exit
282 RETURN 0;
283 END IF;
284
285 -- compute difference
286 IF l_actua_end IS NOT NULL
287 THEN
288 l_diff := l_actua_end - l_sched_end;
289 ELSIF l_actua_start IS NOT NULL
290 THEN
291 l_diff := l_actua_start - l_sched_start;
292
293 IF SYSDATE > l_sched_end + l_diff
294 THEN
295 l_diff := SYSDATE - l_sched_end;
296 END IF;
297 -- no actual dates are found, get the previous task in this trip to find
298 -- an actual date
299 ELSE
300 get_prior_task_assignment (l_res_id,
301 l_res_type,
302 l_shift_start,
303 l_shift_end,
304 l_sched_start,
305 p_task_assignment_id,
306 l_task_type_id,
307 l_prior_ta_id,
308 l_prior_sched_end,
309 l_prior_found
310 );
311
312 IF l_prior_found
313 THEN
314 -- this is a recursive function!
315 l_diff := predict_time_difference (l_prior_ta_id);
316 -- no previous task found, this is the first task of the trip, take
317 -- system date into account
318 ELSE
319 IF SYSDATE > l_sched_start
320 THEN
321 l_diff := SYSDATE - l_sched_start;
322 END IF;
323 END IF;
324
325 -- validate travel time attributes
326 IF l_sched_travel IS NULL OR l_sched_travel < 0
327 THEN
328 l_sched_travel := 0;
329 END IF;
330
331 -- compute minimal time resource has to leave in order to arrive
332 -- in time to start task (unit of measurement is minute)
333 l_min_start := l_sched_start - (l_sched_travel / 1440);
334 -- correct difference by amount of not scheduled, free time
335 l_free := l_min_start - NVL (l_prior_sched_end, l_shift_start);
336 l_diff := l_diff - l_free;
337
338 -- correct for time bounds
339 IF l_bmode = 'BTS'
340 AND l_plan_end >= l_plan_start
341 -- makes no sense for virtual tasks departure and arrival
342 AND l_task_type_id NOT IN (20, 21)
343 THEN
344 IF (l_sched_start + l_diff) < l_plan_start
345 THEN
346 l_diff := l_plan_start - l_sched_start;
347 END IF;
348 END IF;
349 END IF;
350 END IF;
351
352 CLOSE c_this;
353
354 RETURN l_diff;
355 END predict_time_difference;
356
357 FUNCTION get_progress_status (
358 p_resource_id NUMBER,
359 p_resource_type_code VARCHAR2,
360 p_date DATE
361 )
362 RETURN NUMBER
363 IS
364 -- get all escalated tasks in current trip
365 CURSOR c_escalated_tasks (
366 p_res_id NUMBER,
367 p_res_type VARCHAR2,
368 p_date DATE
369 )
370 IS
371 SELECT task_id
372 FROM jtf_task_assignments jta
373 WHERE jta.resource_id = p_res_id
374 AND jta.resource_type_code = p_res_type
375 AND TRUNC (p_date) BETWEEN TRUNC (jta.booking_start_date)
376 AND TRUNC (jta.booking_end_date);
377
378 CURSOR c_task_details (p_res_id NUMBER, p_res_type VARCHAR2, p_date DATE)
379 IS
380 SELECT --task_id,
381 -- start_date_time shift_start,
382 MAX (NVL (jta.actual_start_date,
383 jta.booking_start_date
387 MAX (end_date_time) shift_end
384 + predict_time_difference (jta.task_assignment_id)
385 )
386 ) predicted_start_date,
388 -- NVL (jta.actual_end_date, jtb.scheduled_end_date + predict_time_difference (jta.task_assignment_id)) predicted_end_date
389 FROM jtf_task_assignments jta, cac_sr_object_capacity cso
390 WHERE jta.resource_id = p_res_id
391 AND jta.resource_type_code = p_res_type
392 AND TRUNC (p_date) BETWEEN TRUNC (jta.booking_start_date)
393 AND TRUNC (jta.booking_end_date)
394 AND cso.object_capacity_id = jta.object_capacity_id;
395
396 l_chk VARCHAR2 (1);
397 l_max_pred_end DATE := NULL;
398 l_shift_end DATE := NULL;
399 l_dif NUMBER := NULL;
400 l_uom CONSTANT NUMBER := 1440;
401 /* unit of measurement is minutes */
402 l_margin NUMBER;
403 l_task NUMBER;
404 BEGIN
405 /* see if any task in current trip is escalated */
406 OPEN c_escalated_tasks (p_resource_id, p_resource_type_code, p_date);
407
408 LOOP
409 FETCH c_escalated_tasks
410 INTO l_task;
411
412 IF c_escalated_tasks%NOTFOUND
413 THEN
414 EXIT;
415 END IF;
416
417 IF set_escalation_flag (l_task) = 'Y'
418 THEN
419 CLOSE c_escalated_tasks;
420
421 RETURN 4; /* escalated */
422 END IF;
423 END LOOP;
424
425 CLOSE c_escalated_tasks;
426
427 /* get highest predicted end date within trip */
428 OPEN c_task_details (p_resource_id, p_resource_type_code, p_date);
429
430 FETCH c_task_details
431 INTO l_max_pred_end,
432 l_shift_end;
433
434 /* calculate difference with shift end */
435 l_dif := (l_shift_end - l_max_pred_end) * l_uom;
436
437 IF c_task_details%FOUND AND l_dif IS NOT NULL
438 THEN
439 /* get margin profile option */
440 l_margin :=
441 TO_NUMBER (fnd_profile.VALUE ('CSF_RESOURCE_PROGRESS_STATUS'));
442
443 IF l_margin IS NULL OR SQLCODE <> 0
444 THEN
445 l_margin := 60; /* default value (60 minutes) */
446 END IF;
447
448 CLOSE c_task_details;
449
450 IF l_dif < (l_margin * -1)
451 THEN
452 RETURN 3; /* behind schedule */
453 ELSIF l_dif > l_margin
454 THEN
455 RETURN 1; /* ahead of schedule */
456 END IF;
457
458 RETURN 2; /* on schedule */
459 END IF;
460
461 CLOSE c_task_details;
462
463 RETURN 0; /* unknown */
464 END get_progress_status;
465 END csf_map_pvt;
[Home] [Help]