[Home] [Help]
PACKAGE BODY: APPS.CSF_GANTT_DATA_PKG
Source
1 PACKAGE BODY CSF_GANTT_DATA_PKG AS
2 /*$Header: CSFGTPLB.pls 120.52.12010000.3 2008/11/21 06:49:29 ramchint ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_GANTT_DATA_PKG';
5
6 g_use_custom_chromatics BOOLEAN := FALSE;
7 g_label_on_task BOOLEAN := FALSE;
8 g_uom_minutes VARCHAR2(60) := NULL;
9 g_uom_hours VARCHAR2(60) := NULL;
10 g_resource_id NUMBER(20);
11 g_resource_type VARCHAR2(250);
12
13 l_default_effort_uom VARCHAR2(3);
14 l_default_effort NUMBER(10);
15 l_rule_id NUMBER(30);
16 l_task_custom_color VARCHAR2(1);
17 l_task_dec_color NUMBER;
18 l_profile_value VARCHAR2(1);
19
20 -- Tooltip Labels
21 g_task_number VARCHAR2(80);
22 g_task_type VARCHAR2(80);
23 g_task_status VARCHAR2(80);
24 g_sr_number VARCHAR2(80);
25 g_sr_type VARCHAR2(80);
26 g_parts VARCHAR2(80);
27 g_serial VARCHAR2(80);
28 g_lot VARCHAR2(80);
29 g_address VARCHAR2(80);
30 g_contact VARCHAR2(80);
31 g_phone VARCHAR2(80);
32 g_planned_start VARCHAR2(80);
33 g_sched_start VARCHAR2(80);
34 g_actual_start VARCHAR2(80);
35 g_estimated_start VARCHAR2(80);
36 g_start VARCHAR2(80);
37 g_end VARCHAR2(80);
38 g_travel_time VARCHAR2(80);
39 g_departure VARCHAR2(80);
40 g_option VARCHAR2(80);
41 g_inc_add VARCHAR2(80);
42 g_cust_name VARCHAR2(80);
43 g_timezone VARCHAR2(80);
44 g_product_name VARCHAR2(80);
45
46 g_user_id NUMBER;
47 gl_custom_color_tbl g_custom_color_tbl;
48 g_date_format VARCHAR2(20);
49 l_language VARCHAR2(300);
50
51 g_server_tz varchar2(300) := null;
52 g_client_tz varchar2(300) := null;
53 g_tz_enabled varchar2(1) ;
54 g_dflt_tz_for_dc varchar2(3);
55 g_dflt_tz_for_sc varchar2(3);
56
57 PROCEDURE set_tooltip_labels;
58 PROCEDURE g_get_custom_color;
59 FUNCTION convert_to_min
60 ( p_duration number
61 , p_uom varchar2
62 , p_uom_min varchar2
63 )
64 return number;
65
66
67 -- ---------------------------------
68 -- private procedures and functions
69 -- ---------------------------------
70
71 -- set global variables to store labels for tooltip
72 PROCEDURE set_tooltip_labels IS
73 BEGIN
74 g_task_number := fnd_message.get_string('CSF', 'CSF_TASK_NUMBER');
75 g_task_type := fnd_message.get_string('CSF', 'CSF_TASK_TYPE');
76 g_task_status := fnd_message.get_string('CSF', 'CSF_TASK_STATUS');
77 g_sr_number := fnd_message.get_string('CSF', 'CSF_SR_NUMBER');
78 g_sr_type := fnd_message.get_string('CSF', 'CSF_SR_TYPE');
79 g_parts := fnd_message.get_string('CSF', 'CSF_PARTS_REQUIRED');
80 g_serial := fnd_message.get_string('CSF', 'CSF_SERIAL_NUMBER');
81 g_lot := fnd_message.get_string('CSF', 'CSF_LOT_NUMBER');
82 g_address := fnd_message.get_string('CSF', 'CSF_ADDRESS');
83 g_contact := fnd_message.get_string('CSF', 'CSF_CONTACT');
84 g_phone := fnd_message.get_string('CSF', 'CSF_PHONE');
85 g_planned_start := fnd_message.get_string('CSF', 'CSF_PLANNED_START');
86 g_sched_start := fnd_message.get_string('CSF', 'CSF_SCHEDULED_START');
87 g_actual_start := fnd_message.get_string('CSF', 'CSF_ACTUAL_START');
88 g_estimated_start := fnd_message.get_string('CSF', 'CSF_ESTIMATED_START');
89 g_start := fnd_message.get_string('CSF', 'CSF_START');
90 g_end := fnd_message.get_string('CSF', 'CSF_END');
91 g_travel_time := fnd_message.get_string('CSF', 'CSF_TRAVEL_TIME');
92 g_departure := fnd_message.get_string('CSF', 'CSF_DEPARTURE');
93 g_option := fnd_message.get_string('CSF', 'CSF_PLANOPTION');
94 g_inc_add := fnd_message.get_string('CSF', 'CSF_INCIDENT_ADDRESS');
95 g_cust_name := fnd_message.get_string('CSF', 'CSF_CUSTOMER_NAME');
96 g_timezone := fnd_message.get_string('CSF', 'CSF_TIMEZONE');
97 g_product_name := fnd_message.get_string('CSF', 'CSF_PRODUCT_NAME');
98
99 END set_tooltip_labels;
100
101 PROCEDURE get_message_text
102 ( p_api_version IN Number
103 , p_init_msg_list IN Varchar2 DEFAULT NULL
104 , x_return_status OUT NOCOPY Varchar2
105 , x_msg_count OUT NOCOPY Number
106 , x_msg_data OUT NOCOPY Varchar2
107 , p_message_text OUT NOCOPY jtf_varchar2_table_2000
108 , p_message_code OUT NOCOPY jtf_varchar2_table_2000
109 )
110 IS
111 Cursor c1
112 is
113 select trim(MESSAGE_TEXT),substr(message_name,5,3)
114 from fnd_new_messages
115 where application_id=513
116 AND language_code =l_language
117 and substr(message_name,1,4) = 'CSF_'
118 and translate(substr(message_name,6,2),'0123456789','xxxxxxxxxx') ='xx'
119 order by message_name;
120
121 BEGIN
122 p_message_text :=jtf_varchar2_table_2000();
123 p_message_code :=jtf_varchar2_table_2000();
124 OPEN c1;
125 FETCH c1
126 BULK COLLECT INTO p_message_text,p_message_code;
127 END get_message_text;
128
129 FUNCTION truncsec(p_str VARCHAR2)
130 RETURN VARCHAR2 IS
131 BEGIN
132 RETURN SUBSTR(p_str, 1, LENGTH(p_str) - 3);
133 END truncsec;
134
135 FUNCTION is_task_escalated(p_task_id NUMBER)
136 RETURN BOOLEAN IS
137 l_ref_task_id NUMBER;
138 l_escalated VARCHAR2(10);
139
140 CURSOR c_task_ref IS
141 SELECT task_id
142 FROM jtf_task_references_b r
143 WHERE r.reference_code = 'ESC'
144 AND r.object_type_code = 'TASK'
145 AND r.object_id = p_task_id;
146
147 CURSOR c_esc(b_task_id NUMBER) IS
148 SELECT DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y')
149 FROM jtf_tasks_b t
150 , jtf_task_statuses_b s
151 WHERE t.task_id = b_task_id
152 AND t.task_type_id = 22
153 AND s.task_status_id = t.task_status_id
154 AND NVL(s.closed_flag, 'N') <> 'Y'
155 AND NVL(t.deleted_flag, 'N') <> 'Y';
156 BEGIN
157 -- Get the Reference Task to the given Task
158 OPEN c_task_ref;
159 FETCH c_task_ref INTO l_ref_task_id;
160 CLOSE c_task_ref;
161
162 IF l_ref_task_id IS NULL THEN
163 RETURN FALSE;
164 END IF;
165
166 -- Check whether the Reference object is an Escalation Task
167 OPEN c_esc(l_ref_task_id);
168 FETCH c_esc INTO l_escalated;
169 CLOSE c_esc;
170
171 IF l_escalated = 'Y' THEN
172 RETURN TRUE;
173 ELSE
174 RETURN FALSE;
175 END IF;
176
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 IF c_task_ref%ISOPEN THEN
181 CLOSE c_task_ref;
182 END IF;
183 IF c_esc%ISOPEN THEN
184 CLOSE c_esc;
185 END IF;
186 RETURN FALSE;
187 END is_task_escalated;
188
189 -- this function returns the translated name of the UOM code
190 -- should support a name in plural form too
191 FUNCTION get_uom(p_code VARCHAR2, p_plural BOOLEAN DEFAULT FALSE)
192 RETURN VARCHAR2 IS
193 l_uom VARCHAR2(2000) := NULL;
194
195 CURSOR c_uom(p_code VARCHAR2) IS
196 SELECT unit_of_measure_tl
197 FROM mtl_units_of_measure_vl
198 WHERE uom_code = p_code;
199 BEGIN
200 OPEN c_uom(p_code);
201 FETCH c_uom INTO l_uom;
202 IF c_uom%NOTFOUND THEN
203 RAISE NO_DATA_FOUND;
204 END IF;
205 CLOSE c_uom;
206 RETURN l_uom;
207 EXCEPTION
208 WHEN OTHERS THEN
209 IF c_uom%ISOPEN THEN
210 CLOSE c_uom;
211 END IF;
212 RETURN p_code;
213 END get_uom;
214
215 FUNCTION get_tooltip_data_gantt(
216 p_task_id NUMBER
217 , p_resource_id NUMBER
218 , p_resource_type VARCHAR2
219 , p_start_date DATE
220 , p_end_date DATE
221 , p_inc_tz_code VARCHAR2
222 , p_server_tz_code VARCHAR2
223 , p_client_tz_code VARCHAR2
224 , p_timezone_enb boolean
225 )
226 RETURN VARCHAR2 IS
227 -- task and task assignment data
228
229
230
231
232 CURSOR c_task IS
233 SELECT /*+ ORDERED use_nl (a tb tt tl sb sl pi ps hl ft)
234 INDEX (t,JTF_TASKS_B_U3)
235 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
236 tb.task_id
237 , tl.task_name
238 , tb.task_number
239 , tb.source_object_type_code
240 , tb.source_object_id
241 , tt.NAME task_type
242 , sl.NAME task_status
243 , a.resource_id
244 , a.resource_type_code resource_type
245 , tb.planned_start_date
246 , tb.planned_end_date
247 , scheduled_start_date
248 , scheduled_end_date
249 , a.actual_start_date
250 , a.actual_end_date
251 , a.sched_travel_duration
252 , a.sched_travel_duration_uom
253 , tb.customer_id party_id
254 , NVL(sb.assigned_flag, 'N') assigned_flag
255 , tb.task_type_id
256 , csf_tasks_pub.get_task_address(tb.task_id,tb.address_id,tb.location_id,'Y') small_address
257 , pi.party_name party_name
258 , a.actual_travel_duration
259 , a.actual_travel_duration_uom
260 , tz.ACTIVE_TIMEZONE_CODE ic_tz_code
261 , tz.ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
262 FROM jtf_task_assignments a
263 , jtf_tasks_b tb
264 , jtf_task_types_tl tt
265 , jtf_tasks_tl tl
266 , jtf_task_statuses_b sb
267 , jtf_task_statuses_tl sl
268 , hz_party_sites ps
269 , hz_locations hl
270 , hz_parties pi
271 , fnd_timezones_b tz
272 WHERE a.task_id = p_task_id
273 AND resource_id = p_resource_id
274 AND resource_type_code = p_resource_type
275 AND tb.task_id = a.task_id
276 AND tt.LANGUAGE = l_language
277 AND tt.task_type_id = tb.task_type_id
278 AND sl.LANGUAGE = l_language
279 AND sb.task_status_id = a.assignment_status_id
280 AND sl.task_status_id = sb.task_status_id
281 AND tl.LANGUAGE = l_language
282 AND tl.task_id = tb.task_id
283 AND ps.party_site_id(+) = tb.address_id
284 AND hl.location_id(+) = ps.location_id
285 AND pi.party_id(+) = tb.customer_id
286 AND NVL(sb.cancelled_flag, 'N') <> 'Y'
287 AND tz.UPGRADE_TZ_ID(+) = hl.timezone_id;
288
289 CURSOR c_sr(b_incident_id NUMBER) IS
290 SELECT /*+ ORDERED USE_NL */
291 i.customer_product_id
292 , i.current_serial_number
293 , si.concatenated_segments product_name
294 FROM cs_incidents_all_b i, mtl_system_items_kfv si
295 WHERE si.inventory_item_id(+) = i.inventory_item_id
296 AND si.organization_id(+) = i.inv_organization_id
297 AND i.incident_id = b_incident_id;
298
299 l_uom VARCHAR2(2000) := NULL;
300 l_Res_Timezone_id Number;
301 l_res_tz_cd varchar2(100);
302 CURSOR c_uom(p_code VARCHAR2) IS
303 SELECT unit_of_measure_tl
304 FROM mtl_units_of_measure_vl
305 WHERE uom_code = p_code;
306
307 CURSOR c_parts(b_task_id NUMBER) IS
308 SELECT 'Y' required
309 FROM csp_requirement_headers
310 WHERE task_id = b_task_id;
311
312 CURSOR c_ib(b_customer_product_id NUMBER) IS
313 SELECT serial_number
314 , lot_number
315 FROM csi_item_instances
316 WHERE instance_id = b_customer_product_id;
317
318 Cursor C_Res_TimeZone Is
319 Select TIME_ZONE
320 From JTF_RS_RESOURCE_EXTNS
321 Where RESOURCE_ID = p_resource_id
322 ;
323 Cursor c_res_tz
324 IS
325 SELECT ACTIVE_TIMEZONE_CODE,ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
326 FROM fnd_timezones_vl ft
327 WHERE UPGRADE_TZ_ID =l_Res_TimeZone_id;
328
329
330
331 l_task_rec c_task%ROWTYPE;
332 l_sr_rec c_sr%ROWTYPE;
333 l_parts_rec c_parts%ROWTYPE;
334 l_ib_rec c_ib%ROWTYPE;
335 l_rec tooltip_data_rec_type := NULL;
336 p_color NUMBER := 255;
337 l_str VARCHAR2(2000) := NULL;
338 l_ic_planned_start_date date;
339 l_ic_planned_end_date date;
340 l_ic_scheduled_start_date date;
341 l_ic_scheduled_end_date date;
342 l_ic_actual_start_date date;
343 l_ic_actual_end_date date;
344
345
346 l_dc_planned_start_date date;
347 l_dc_planned_end_date date;
348 l_dc_scheduled_start_date date;
349 l_dc_scheduled_end_date date;
350 l_dc_actual_start_date date;
351 l_dc_actual_end_date date;
352 l_actual_start_date date;
353 l_scheduled_start_date date;
354 l_tz_desc varchar2(100);
355 l_rs_tz_desc varchar2(100);
356 l_rs_ic_tz_present boolean;
357 l_lines number; --bug no 5674408
358 BEGIN
359 OPEN c_task;
360 FETCH c_task INTO l_task_rec;
361 IF c_task%NOTFOUND THEN
362 CLOSE c_task;
363 RAISE NO_DATA_FOUND;
364 END IF;
365
366 IF l_task_rec.task_type_id NOT IN(20, 21)
367 THEN
368 IF l_task_rec.scheduled_start_date <> l_task_rec.scheduled_end_date
369 THEN
370 IF ( l_task_rec.scheduled_start_date <> p_start_date
371 OR l_task_rec.scheduled_end_date <> p_end_date
372 )
373 AND l_task_rec.actual_start_date is null
374 THEN
375 CLOSE c_task;
376 RAISE NO_DATA_FOUND;
377 END IF;
378 END IF;
379 OPEN c_sr(l_task_rec.source_object_id);
380 FETCH c_sr INTO l_sr_rec;
381
382 IF l_sr_rec.customer_product_id IS NOT NULL THEN
383 OPEN c_ib(l_sr_rec.customer_product_id);
384 FETCH c_ib INTO l_ib_rec;
385 CLOSE c_ib;
386 ELSE
387 l_ib_rec.serial_number := l_sr_rec.current_serial_number;
388 l_ib_rec.lot_number := NULL; -- not yet supported
389 END IF;
390 END IF;
391 l_rec.incident_customer_name := l_task_rec.party_name;
392
393 --begin addition for bug 5674408
394 IF (LENGTH(NVL(l_task_rec.party_name, 0)) > 80)
395 THEN
396 l_lines := ceil(length(l_task_rec.party_name)/80) - 1;
397 l_rec.incident_customer_name := null;
398
399 for i in 1..l_lines
400 loop
401 l_rec.incident_customer_name := l_rec.incident_customer_name || SUBSTRB (l_task_rec.party_name, 1, 80) || fnd_global.local_chr (10);
402 l_task_rec.party_name := substrb(l_task_rec.party_name,81);
403 end loop;
404
405 l_rec.incident_customer_name := l_rec.incident_customer_name || SUBSTRB (l_task_rec.party_name, 1);
406 END IF;
407 --end addition for bug 5674408
408
412 Open C_Res_TimeZone ;
409 l_Res_TimeZone_id:=NULL;
410 IF l_task_rec.task_type_id IN(20, 21)
411 THEN
413 Fetch C_Res_TimeZone into l_Res_TimeZone_id;
414 Close C_Res_TimeZone ;
415 if l_Res_TimeZone_id is not null
416 then
417 Open c_res_tz ;
418 Fetch c_res_tz into l_res_tz_cd,l_rs_tz_desc;
419 Close c_res_tz ;
420 end if;
421 if p_timezone_enb
422 then
423 if l_res_tz_cd is not null
424 then
425 l_ic_planned_start_date :=fnd_date.adjust_datetime( l_task_rec.planned_start_date,p_server_tz_code,l_res_tz_cd);
426 l_ic_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,l_res_tz_cd);
427 l_ic_scheduled_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,l_res_tz_cd);
428 l_ic_scheduled_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,l_res_tz_cd);
429 l_tz_desc :=l_rs_tz_desc;
430 end if;
431 else
432 l_ic_planned_start_date :=l_task_rec.planned_start_date;
433 l_ic_planned_end_date :=l_task_rec.planned_end_date;
434 l_ic_scheduled_start_date :=p_start_date;
435 l_ic_scheduled_end_date :=p_end_date;
436 end if;
437 END IF;
438
439 IF l_task_rec.actual_start_date is not null
440 THEN
441
442 if p_inc_tz_code ='UTZ'and p_timezone_enb
443 then
444 l_dc_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,p_client_tz_code);
445 l_dc_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,p_client_tz_code);
446 l_dc_scheduled_start_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_start_date,p_server_tz_code,p_client_tz_code);
447 l_dc_scheduled_end_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_end_date,p_server_tz_code,p_client_tz_code);
448 l_dc_actual_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,p_client_tz_code);
449 l_dc_actual_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,p_client_tz_code);
450 else
451 l_dc_planned_start_date :=l_task_rec.planned_start_date;
452 l_dc_planned_end_date :=l_task_rec.planned_end_date;
453 l_dc_scheduled_start_date :=l_task_rec.scheduled_start_date;
454 l_dc_scheduled_end_date :=l_task_rec.scheduled_end_date;
455 l_dc_actual_start_date :=p_start_date;
456 l_dc_actual_end_date :=p_end_date;
457 end if;
458 l_actual_start_date :=l_dc_actual_start_date;
459 if l_task_rec.ic_tz_code is not null and p_timezone_enb
460 then
461 l_ic_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
462 l_ic_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
463 l_ic_scheduled_start_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
464 l_ic_scheduled_end_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
465 l_ic_actual_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
466 l_ic_actual_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
467 l_actual_start_date :=l_ic_actual_start_date;
468 l_tz_desc :=l_task_rec.tz_desc;
469 end if;
470
471 l_rec.departure_time:=
472 TO_CHAR(l_actual_start_date - (
473 inv_convert.inv_um_convert(
474 0
475 , NULL
476 , NVL(l_task_rec.actual_travel_duration, 0)
477 , NVL(l_task_rec.actual_travel_duration_uom, g_uom_minutes)
478 , g_uom_hours
479 , NULL
480 , NULL
481 )
482 / 24
483 )
484 ,' hh24:mi'
485 );
486 OPEN c_uom(NVL(l_task_rec.actual_travel_duration_uom, g_uom_minutes));
487 FETCH c_uom INTO l_uom;
488 IF c_uom%NOTFOUND THEN
489 l_uom := NVL(l_task_rec.actual_travel_duration_uom, g_uom_minutes);
490 END IF;
491 CLOSE c_uom;
492 l_rec.travel_time := NVL(l_task_rec.actual_travel_duration, 0) || ' ' || l_uom;
493 ELSE
494 if p_inc_tz_code ='UTZ'and p_timezone_enb
495 then
496 l_dc_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,p_client_tz_code);
497 l_dc_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,p_client_tz_code);
498 l_dc_scheduled_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,p_client_tz_code);
499 l_dc_scheduled_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,p_client_tz_code);
500 else
501 l_dc_planned_start_date :=l_task_rec.planned_start_date;
502 l_dc_planned_end_date :=l_task_rec.planned_end_date;
503 l_dc_scheduled_start_date :=p_start_date;
504 l_dc_scheduled_end_date :=p_end_date;
505 end if;
506 l_scheduled_start_date :=l_dc_scheduled_start_date;
510 l_ic_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
507 if l_task_rec.ic_tz_code is not null and p_timezone_enb and l_task_rec.task_type_id not in (20, 21)
508 then
509 l_ic_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
511 l_ic_scheduled_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
512 l_ic_scheduled_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
513 l_scheduled_start_date :=l_ic_scheduled_start_date;
514 l_tz_desc :=l_task_rec.tz_desc;
515 elsif l_res_tz_cd is not null and p_timezone_enb
516 then
517 l_scheduled_start_date :=l_ic_scheduled_start_date;
518 end if;
519
520 l_rec.departure_time:=
521 TO_CHAR(l_scheduled_start_date - (
522 inv_convert.inv_um_convert(
523 0
524 , NULL
525 , NVL(l_task_rec.sched_travel_duration, 0)
526 , NVL(l_task_rec.sched_travel_duration_uom, g_uom_minutes)
527 , g_uom_hours
528 , NULL
529 , NULL
530 )
531 / 24
532 )
533 ,'hh24:mi'
534 );
535
536 OPEN c_uom(NVL(l_task_rec.sched_travel_duration_uom, g_uom_minutes));
537 FETCH c_uom INTO l_uom;
538 IF c_uom%NOTFOUND THEN
539 l_uom := NVL(l_task_rec.sched_travel_duration_uom, g_uom_minutes);
540 END IF;
541 CLOSE c_uom;
542 l_rec.travel_time := NVL(l_task_rec.sched_travel_duration, 0) || ' ' || l_uom;
543 END IF;
544 l_rec.assigned_flag := l_task_rec.assigned_flag;
545 l_rec.is_plan_option := 'N';
546 l_str :=
547 '<TOOLTIP>'
548 || '<CENTER fgColor='
549 || 0
550 || '>'
551 || l_task_rec.task_name
552 || '</CENTER>'
553 || '<LINE></LINE>'
554 || '<LABEL>'
555 || g_task_number
556 || '</LABEL>'
557 || '<VALUE fgColor='
558 || 0
559 || '>'
560 || l_task_rec.task_number
561 || '</VALUE>'
562 || '<LABEL>'
563 || g_task_type
564 || '</LABEL>'
565 || '<VALUE fgColor='
566 || 0
567 || '>'
568 || l_task_rec.task_type
569 || '</VALUE>'
570 || '<LABEL>'
571 || g_task_status
572 || '</LABEL>'
573 || '<VALUE fgColor='
574 || 0
575 || '>'
576 || l_task_rec.task_status
577 || '</VALUE>'
578 || '<LINE></LINE>'
579 || '<LABEL>'
580 || g_cust_name
581 || '</LABEL>'
582 || '<VALUE fgColor='
583 || 0
584 || '>'
585 || l_rec.incident_customer_name
586 || '</VALUE>'
587 || '<LABEL>'
588 || g_address
589 || '</LABEL>'
590 || '<VALUE fgColor='
591 || 0
592 || '>'
593 || l_task_rec.small_address
594 || '</VALUE>';
595
596 IF p_timezone_enb
597 THEN
598 l_str :=
599 l_str
600 || '<LABEL>'
601 || g_timezone
602 || '</LABEL>'
603 || '<VALUE fgColor='
604 || 0
605 || '>'
606 || l_tz_desc
607 || '</VALUE>';
608 END IF;
609 IF l_sr_rec.product_name IS NOT NULL THEN
610 l_str :=
611 l_str
612 || '<LABEL>'
613 || g_product_name
614 || '</LABEL>'
615 || '<VALUE fgColor='
616 || 0
617 || '>'
618 || l_sr_rec.product_name
619 || '</VALUE>';
620 IF l_ib_rec.serial_number IS NOT NULL THEN
621 l_str :=
622 l_str
623 || '<LABEL>'
624 || g_serial
625 || '</LABEL>'
626 || '<VALUE fgColor='
627 || 0
628 || '>'
629 || l_ib_rec.serial_number
630 || '</VALUE>';
631 END IF;
632 END IF;
633 IF p_timezone_enb
634 THEN
635 l_str :=
636 l_str
637 || '<LABEL>'
638 || g_planned_start
639 || '</LABEL>'
640 || '<VALUE fgColor='
641 || 0
642 || '>'
643 || to_char(l_ic_planned_start_date,g_date_format||' hh24:mi')
644 || '</VALUE>'
645 || '<LABEL>'
646 || g_end
647 || '</LABEL>'
648 || '<VALUE fgColor='
649 || 0
650 || '>'
654 || g_sched_start
651 || to_char(l_ic_planned_end_date,g_date_format||' hh24:mi')
652 || '</VALUE>'
653 || '<LABEL>'
655 || '</LABEL>'
656 || '<VALUE fgColor='
657 || 0
658 || '>'
659 || to_char(l_ic_scheduled_start_date,g_date_format||' hh24:mi')
660 || '</VALUE>'
661 || '<LABEL>'
662 || g_end
663 || '</LABEL>'
664 || '<VALUE fgColor='
665 || 0
666 || '>'
667 || to_char(l_ic_scheduled_end_date,g_date_format||' hh24:mi')
668 || '</VALUE>';
669 IF l_task_rec.actual_start_date IS NOT NULL THEN
670 l_str :=
671 l_str
672 || '<LABEL>'
673 || g_actual_start
674 || '</LABEL>'
675 || '<VALUE fgColor='
676 || 0
677 || '>'
678 || to_char(l_ic_actual_start_date,g_date_format||' hh24:mi')
679 || '</VALUE>'
680 || '<LABEL>'
681 || g_end
682 || '</LABEL>'
683 || '<VALUE fgColor='
684 || 0
685 || '>'
686 || to_char(l_ic_actual_end_date,g_date_format||' hh24:mi')
687 || '</VALUE>';
688 END IF;
689 l_rs_ic_tz_present :=true;
690 If (l_task_rec.ic_tz_code is null and l_task_rec.task_type_id not IN (20, 21)) or (l_res_tz_cd is null and l_task_rec.task_type_id IN(20, 21))
691 Then
692 l_str :=
693 l_str|| '<LINE></LINE>';
694 l_rs_ic_tz_present:=false;
695 end if;
696 ELSE
697 l_str :=
698 l_str|| '<LINE></LINE>';
699 END IF;
700 IF l_rs_ic_tz_present
701 THEN
702 l_str :=
703 l_str
704 || '<LABEL>'
705 || g_departure
706 || '</LABEL>'
707 || '<VALUE fgColor='
708 || 0
709 || '>'
710 || l_rec.departure_time
711 || '</VALUE>'
712 || '<LABEL>'
713 || g_travel_time
714 || '</LABEL>'
715 || '<VALUE fgColor='
716 || 0
717 || '>'
718 || l_rec.travel_time
719 || '</VALUE>';
720 l_str :=
721 l_str|| '<LINE></LINE>';
722 END IF;
723 l_str :=
724 l_str
725 || '<LABEL>'
726 || g_planned_start
727 || '</LABEL>'
728 || '<VALUE fgColor='
729 || 0
730 || '>'
731 || to_char(l_dc_planned_start_date,g_date_format||' hh24:mi')
732 || '</VALUE>'
733 || '<LABEL>'
734 || g_end
735 || '</LABEL>'
736 || '<VALUE fgColor='
737 || 0
738 || '>'
739 || to_char(l_dc_planned_end_date,g_date_format||' hh24:mi')
740 || '</VALUE>'
741 || '<LABEL>'
742 || g_sched_start
743 || '</LABEL>'
744 || '<VALUE fgColor='
745 || 0
746 || '>'
747 || to_char(l_dc_scheduled_start_date,g_date_format||' hh24:mi')
748 || '</VALUE>'
749 || '<LABEL>'
750 || g_end
751 || '</LABEL>'
752 || '<VALUE fgColor='
753 || 0
754 || '>'
755 || to_char(l_dc_scheduled_end_date,g_date_format||' hh24:mi')
756 || '</VALUE>';
757 IF l_task_rec.actual_start_date IS NOT NULL THEN
758 l_str :=
759 l_str
760 || '<LABEL>'
761 || g_actual_start
762 || '</LABEL>'
763 || '<VALUE fgColor='
764 || 0
765 || '>'
766 || to_char(l_dc_actual_start_date,g_date_format||' hh24:mi')
767 || '</VALUE>'
768 || '<LABEL>'
769 || g_end
770 || '</LABEL>'
771 || '<VALUE fgColor='
772 || 0
773 || '>'
774 || to_char(l_dc_actual_end_date,g_date_format||' hh24:mi')
775 || '</VALUE>';
776 END IF;
777 IF l_rs_ic_tz_present = false OR p_timezone_enb=False
778 THEN
779 l_str :=
780 l_str
781 || '<LABEL>'
782 || g_departure
783 || '</LABEL>'
784 || '<VALUE fgColor='
785 || 0
786 || '>'
787 || l_rec.departure_time
788 || '</VALUE>'
789 || '<LABEL>'
790 || g_travel_time
791 || '</LABEL>'
792 || '<VALUE fgColor='
793 || 0
794 || '>'
795 || l_rec.travel_time
796 || '</VALUE>';
797 END IF;
798 l_str :=l_str|| '</TOOLTIP>';
799 RETURN l_str;
800 END get_tooltip_data_gantt;
801 FUNCTION get_tooltip_data_sch_advise(
802 p_task_id NUMBER
803 , p_resource_id NUMBER
804 , p_resource_type VARCHAR2
805 , p_start_date DATE
806 , p_end_date DATE
807 , p_duration NUMBER
808 , sch_adv_tz varchar2
809 , p_server_tz_code VARCHAR2
813 , p_inc_tz_code VARCHAR2
810 , p_client_tz_code VARCHAR2
811 , p_timezone_enb boolean
812 , p_inc_tz_desc varchar2
814 )
815 RETURN VARCHAR2 IS
816 -- task and task assignment data
817 CURSOR c_task IS
818 SELECT /*+ ORDERED use_nl (a tb tt tl sb sl pi ps hl ft)
819 INDEX (t,JTF_TASKS_B_U3)
820 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
821 tb.task_id
822 , tl.task_name
823 , tb.task_number
824 , tb.source_object_type_code
825 , tb.source_object_id
826 , tt.NAME task_type
827 , sl.NAME task_status
828 , a.resource_id
829 , a.resource_type_code resource_type
830 , tb.planned_start_date
831 , tb.planned_end_date
832 , scheduled_start_date
833 , scheduled_end_date
834 , a.actual_start_date
835 , a.actual_end_date
836 , a.sched_travel_duration
837 , a.sched_travel_duration_uom
838 , tb.customer_id party_id
839 , NVL(sb.assigned_flag, 'N') assigned_flag
840 , tb.task_type_id
841 , csf_tasks_pub.get_task_address(tb.task_id,tb.address_id,tb.location_id,'Y') small_address
842 , pi.party_name party_name
843 , a.actual_travel_duration
844 , a.actual_travel_duration_uom
845 , tz.ACTIVE_TIMEZONE_CODE ic_tz_code
846 , tz.ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
847 FROM jtf_task_assignments a
848 , jtf_tasks_b tb
849 , jtf_task_types_tl tt
850 , jtf_tasks_tl tl
851 , jtf_task_statuses_b sb
852 , jtf_task_statuses_tl sl
853 , hz_party_sites ps
854 , hz_locations hl
855 , hz_parties pi
856 , fnd_timezones_b tz
857 WHERE a.task_id = p_task_id
858 AND resource_id = p_resource_id
859 AND resource_type_code = p_resource_type
860 AND tb.task_id = a.task_id
861 AND tt.LANGUAGE = l_language
862 AND tt.task_type_id = tb.task_type_id
863 AND sl.LANGUAGE = l_language
864 AND sb.task_status_id = a.assignment_status_id
865 AND sl.task_status_id = sb.task_status_id
866 AND tl.LANGUAGE = l_language
867 AND tl.task_id = tb.task_id
868 AND ps.party_site_id(+) = tb.address_id
869 AND hl.location_id(+) = ps.location_id
870 AND pi.party_id(+) = tb.customer_id
871 AND NVL(sb.cancelled_flag, 'N') <> 'Y'
872 AND tz.UPGRADE_TZ_ID(+) = hl.timezone_id;
873
874 CURSOR c_sr(b_incident_id NUMBER) IS
875 SELECT /*+ ORDERED USE_NL */
876 i.customer_product_id
877 , i.current_serial_number
878 , si.concatenated_segments product_name
879 FROM cs_incidents_all_b i, mtl_system_items_kfv si
880 WHERE si.inventory_item_id(+) = i.inventory_item_id
881 AND si.organization_id(+) = i.inv_organization_id
882 AND i.incident_id = b_incident_id;
883
884 l_uom VARCHAR2(2000) := NULL;
885 l_Res_Timezone_id Number;
886 l_res_tz_cd varchar2(100);
887 CURSOR c_uom(p_code VARCHAR2) IS
888 SELECT unit_of_measure_tl
889 FROM mtl_units_of_measure_vl
890 WHERE uom_code = p_code;
891
892 CURSOR c_parts(b_task_id NUMBER) IS
893 SELECT 'Y' required
894 FROM csp_requirement_headers
895 WHERE task_id = b_task_id;
896
897 CURSOR c_ib(b_customer_product_id NUMBER) IS
898 SELECT serial_number
899 , lot_number
900 FROM csi_item_instances
901 WHERE instance_id = b_customer_product_id;
902
903 Cursor C_Res_TimeZone Is
904 Select TIME_ZONE
905 From JTF_RS_RESOURCE_EXTNS
906 Where RESOURCE_ID = p_resource_id
907 ;
908 Cursor c_res_tz
909 IS
910 SELECT ACTIVE_TIMEZONE_CODE,ACTIVE_TIMEZONE_CODE|| ' (GMT ' ||to_char(trunc(gmt_offset),'S09') || ':' || to_char(abs(gmt_offset - trunc(gmt_offset))*60,'FM900') || ') ' tz_desc
911 FROM fnd_timezones_vl ft
912 WHERE UPGRADE_TZ_ID =l_Res_TimeZone_id;
913
914
915
916 l_task_rec c_task%ROWTYPE;
917 l_sr_rec c_sr%ROWTYPE;
918 l_parts_rec c_parts%ROWTYPE;
919 l_ib_rec c_ib%ROWTYPE;
920 l_rec tooltip_data_rec_type := NULL;
921 p_color NUMBER := 255;
922 l_str VARCHAR2(2000) := NULL;
923 l_ic_planned_start_date date;
924 l_ic_planned_end_date date;
925 l_ic_scheduled_start_date date;
926 l_ic_scheduled_end_date date;
927 l_ic_actual_start_date date;
928 l_ic_actual_end_date date;
929
930
931 l_dc_planned_start_date date;
932 l_dc_planned_end_date date;
933 l_dc_scheduled_start_date date;
934 l_dc_scheduled_end_date date;
935 l_dc_actual_start_date date;
936 l_dc_actual_end_date date;
937 l_actual_start_date date;
938 l_scheduled_start_date date;
939 l_tz_desc varchar2(100);
940 l_rs_tz_desc varchar2(100);
941 l_rs_ic_tz_present boolean;
945 OPEN c_task;
942 l_lines number; --bug no 5674408
943
944 BEGIN
946 FETCH c_task INTO l_task_rec;
947 IF c_task%NOTFOUND THEN
948 CLOSE c_task;
949 RAISE NO_DATA_FOUND;
950 END IF;
951
952 IF l_task_rec.task_type_id NOT IN(20, 21)
953 THEN
954
955 OPEN c_sr(l_task_rec.source_object_id);
956 FETCH c_sr INTO l_sr_rec;
957
958 IF l_sr_rec.customer_product_id IS NOT NULL THEN
959 OPEN c_ib(l_sr_rec.customer_product_id);
960 FETCH c_ib INTO l_ib_rec;
961 CLOSE c_ib;
962 ELSE
963 l_ib_rec.serial_number := l_sr_rec.current_serial_number;
964 l_ib_rec.lot_number := NULL; -- not yet supported
965 END IF;
966 END IF;
967 l_rec.incident_customer_name := l_task_rec.party_name;
968
969 --begin addition for bug 5674408
970 IF (LENGTH(NVL(l_task_rec.party_name, 0)) > 80)
971 THEN
972 l_lines := ceil(length(l_task_rec.party_name)/80) - 1;
973 l_rec.incident_customer_name := null;
974
975 for i in 1..l_lines
976 loop
977 l_rec.incident_customer_name := l_rec.incident_customer_name || SUBSTRB (l_task_rec.party_name, 1, 80) || fnd_global.local_chr (10);
978 l_task_rec.party_name := substrb(l_task_rec.party_name,81);
979 end loop;
980
981 l_rec.incident_customer_name := l_rec.incident_customer_name || SUBSTRB (l_task_rec.party_name, 1);
982 END IF;
983 --end addition for bug 5674408
984
985 IF l_task_rec.actual_start_date is not null
986 THEN
987
988 if sch_adv_tz ='UTZ' and p_timezone_enb
989 then
990 l_dc_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,p_client_tz_code);
991 l_dc_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,p_client_tz_code);
992 l_dc_scheduled_start_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_start_date,p_server_tz_code,p_client_tz_code);
993 l_dc_scheduled_end_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_end_date,p_server_tz_code,p_client_tz_code);
994 l_dc_actual_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,p_client_tz_code);
995 l_dc_actual_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,p_client_tz_code);
996 l_actual_start_date :=l_dc_actual_start_date;
997 if l_task_rec.ic_tz_code is not null and p_timezone_enb
998 then
999 l_ic_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1000 l_ic_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1001 l_ic_scheduled_start_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1002 l_ic_scheduled_end_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1003 l_ic_actual_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1004 l_ic_actual_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1005 l_actual_start_date :=l_ic_actual_start_date;
1006 l_tz_desc :=l_task_rec.tz_desc;
1007 end if;
1008 elsif sch_adv_tz ='CTZ' and p_timezone_enb
1009 then
1010 l_dc_planned_start_date :=l_task_rec.planned_start_date;
1011 l_dc_planned_end_date :=l_task_rec.planned_end_date;
1012 l_dc_scheduled_start_date :=l_task_rec.scheduled_start_date;
1013 l_dc_scheduled_end_date :=l_task_rec.scheduled_end_date;
1014 l_dc_actual_start_date :=p_start_date;
1015 l_dc_actual_end_date :=p_end_date;
1016
1017 l_actual_start_date :=l_dc_actual_start_date;
1018 if l_task_rec.ic_tz_code is not null and p_timezone_enb
1019 then
1020 l_ic_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1021 l_ic_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1022 l_ic_scheduled_start_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1023 l_ic_scheduled_end_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1024 l_ic_actual_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1025 l_ic_actual_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1026 l_actual_start_date :=l_ic_actual_start_date;
1027 l_tz_desc :=l_task_rec.tz_desc;
1028 end if;
1029 elsif sch_adv_tz ='ITZ' and p_timezone_enb
1030 then
1031 l_ic_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1032 l_ic_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1033 l_ic_scheduled_start_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1034 l_ic_scheduled_end_date :=fnd_date.adjust_datetime(l_task_rec.scheduled_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1035 l_ic_actual_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1039 end if;
1036 l_ic_actual_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1037 l_actual_start_date :=l_ic_actual_start_date;
1038 l_tz_desc := p_inc_tz_desc;
1040
1041 l_rec.departure_time:=
1042 TO_CHAR(l_actual_start_date - (
1043 inv_convert.inv_um_convert(
1044 0
1045 , NULL
1046 , NVL(l_task_rec.actual_travel_duration, 0)
1047 , NVL(l_task_rec.actual_travel_duration_uom, g_uom_minutes)
1048 , g_uom_hours
1049 , NULL
1050 , NULL
1051 )
1052 / 24
1053 )
1054 ,' hh24:mi'
1055 );
1056 OPEN c_uom(NVL(l_task_rec.actual_travel_duration_uom, g_uom_minutes));
1057 FETCH c_uom INTO l_uom;
1058 IF c_uom%NOTFOUND THEN
1059 l_uom := NVL(l_task_rec.actual_travel_duration_uom, g_uom_minutes);
1060 END IF;
1061 CLOSE c_uom;
1062 l_rec.travel_time := NVL(l_task_rec.actual_travel_duration, 0) || ' ' || l_uom;
1063 ELSE
1064 if sch_adv_tz ='UTZ' and p_timezone_enb
1065 then
1066 l_dc_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,p_client_tz_code);
1067 l_dc_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,p_client_tz_code);
1068 l_dc_scheduled_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,p_client_tz_code);
1069 l_dc_scheduled_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,p_client_tz_code);
1070 l_scheduled_start_date :=l_dc_scheduled_start_date;
1071 if l_task_rec.ic_tz_code is not null
1072 then
1073 l_ic_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1074 l_ic_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1075 l_ic_scheduled_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1076 l_ic_scheduled_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1077 l_scheduled_start_date :=l_ic_scheduled_start_date;
1078 l_tz_desc :=l_task_rec.tz_desc;
1079 end if;
1080 elsif sch_adv_tz ='CTZ' and p_timezone_enb
1081 then
1082 l_dc_planned_start_date :=l_task_rec.planned_start_date;
1083 l_dc_planned_end_date :=l_task_rec.planned_end_date;
1084 l_dc_scheduled_start_date :=p_start_date;
1085 l_dc_scheduled_end_date :=p_end_date;
1086 l_scheduled_start_date :=l_dc_scheduled_start_date;
1087 if l_task_rec.ic_tz_code is not null
1088 then
1089 l_ic_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1090 l_ic_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1091 l_ic_scheduled_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,l_task_rec.ic_tz_code);
1092 l_ic_scheduled_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,l_task_rec.ic_tz_code);
1093 l_scheduled_start_date :=l_ic_scheduled_start_date;
1094 l_tz_desc :=l_task_rec.tz_desc;
1095 end if;
1096 elsif sch_adv_tz ='ITZ' and p_timezone_enb
1097 then
1098 l_ic_planned_start_date :=fnd_date.adjust_datetime(l_task_rec.planned_start_date,p_server_tz_code,p_inc_tz_code);
1099 l_ic_planned_end_date :=fnd_date.adjust_datetime(l_task_rec.planned_end_date,p_server_tz_code,p_inc_tz_code);
1100 l_ic_scheduled_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,p_client_tz_code);
1101 l_ic_scheduled_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,p_client_tz_code);
1102 l_scheduled_start_date :=l_ic_scheduled_start_date;
1103 l_tz_desc := p_inc_tz_desc;
1104 end if;
1105
1106 /*l_rec.departure_time:=
1107 TO_CHAR(l_scheduled_start_date - (
1108 inv_convert.inv_um_convert(
1109 0
1110 , NULL
1111 , NVL(l_task_rec.sched_travel_duration, 0)
1112 , NVL(l_task_rec.sched_travel_duration_uom, g_uom_minutes)
1113 , g_uom_hours
1114 , NULL
1115 , NULL
1116 )
1117 / 24
1118 )
1119 ,'hh24:mi'
1120 );
1121 */
1122 l_rec.departure_time:=TO_CHAR((l_scheduled_start_date - (nvl(p_duration,0)/1440)),'hh24:mi');
1123 OPEN c_uom(NVL(l_task_rec.sched_travel_duration_uom, g_uom_minutes));
1124 FETCH c_uom INTO l_uom;
1125 IF c_uom%NOTFOUND THEN
1126 l_uom := NVL(l_task_rec.sched_travel_duration_uom, g_uom_minutes);
1127 END IF;
1128 CLOSE c_uom;
1129 --l_rec.travel_time := NVL(l_task_rec.sched_travel_duration, 0) || ' ' || l_uom;
1133 l_rec.is_plan_option := 'N';
1130 l_rec.travel_time := nvl(p_duration,0) || ' ' || l_uom;
1131 END IF;
1132 l_rec.assigned_flag := l_task_rec.assigned_flag;
1134 l_str :=
1135 '<TOOLTIP>'
1136 || '<CENTER fgColor='
1137 || 0
1138 || '>'
1139 || l_task_rec.task_name
1140 || '</CENTER>'
1141 || '<LINE></LINE>'
1142 || '<LABEL>'
1143 || g_task_number
1144 || '</LABEL>'
1145 || '<VALUE fgColor='
1146 || 0
1147 || '>'
1148 || l_task_rec.task_number
1149 || '</VALUE>'
1150 || '<LABEL>'
1151 || g_task_type
1152 || '</LABEL>'
1153 || '<VALUE fgColor='
1154 || 0
1155 || '>'
1156 || l_task_rec.task_type
1157 || '</VALUE>'
1158 || '<LABEL>'
1159 || g_task_status
1160 || '</LABEL>'
1161 || '<VALUE fgColor='
1162 || 0
1163 || '>'
1164 || l_task_rec.task_status
1165 || '</VALUE>'
1166 || '<LINE></LINE>'
1167 || '<LABEL>'
1168 || g_cust_name
1169 || '</LABEL>'
1170 || '<VALUE fgColor='
1171 || 0
1172 || '>'
1173 || l_rec.incident_customer_name
1174 || '</VALUE>'
1175 || '<LABEL>'
1176 || g_address
1177 || '</LABEL>'
1178 || '<VALUE fgColor='
1179 || 0
1180 || '>'
1181 || l_task_rec.small_address
1182 || '</VALUE>';
1183 IF p_timezone_enb
1184 THEN
1185 l_str :=
1186 l_str
1187 || '<LABEL>'
1188 || g_timezone
1189 || '</LABEL>'
1190 || '<VALUE fgColor='
1191 || 0
1192 || '>'
1193 || l_tz_desc
1194 || '</VALUE>';
1195 END IF;
1196 IF l_sr_rec.product_name IS NOT NULL THEN
1197 l_str :=
1198 l_str
1199 || '<LABEL>'
1200 || g_product_name
1201 || '</LABEL>'
1202 || '<VALUE fgColor='
1203 || 0
1204 || '>'
1205 || l_sr_rec.product_name
1206 || '</VALUE>';
1207 IF l_ib_rec.serial_number IS NOT NULL THEN
1208 l_str :=
1209 l_str
1210 || '<LABEL>'
1211 || g_serial
1212 || '</LABEL>'
1213 || '<VALUE fgColor='
1214 || 0
1215 || '>'
1216 || l_ib_rec.serial_number
1217 || '</VALUE>';
1218 END IF;
1219 END IF;
1220 IF p_timezone_enb
1221 THEN
1222 l_str :=
1223 l_str
1224 || '<LABEL>'
1225 || g_planned_start
1226 || '</LABEL>'
1227 || '<VALUE fgColor='
1228 || 0
1229 || '>'
1230 || to_char(l_ic_planned_start_date,g_date_format||' hh24:mi')
1231 || '</VALUE>'
1232 || '<LABEL>'
1233 || g_end
1234 || '</LABEL>'
1235 || '<VALUE fgColor='
1236 || 0
1237 || '>'
1238 || to_char(l_ic_planned_end_date,g_date_format||' hh24:mi')
1239 || '</VALUE>'
1240 || '<LABEL>'
1241 || g_sched_start
1242 || '</LABEL>'
1243 || '<VALUE fgColor='
1244 || 0
1245 || '>'
1246 || to_char(l_ic_scheduled_start_date,g_date_format||' hh24:mi')
1247 || '</VALUE>'
1248 || '<LABEL>'
1249 || g_end
1250 || '</LABEL>'
1251 || '<VALUE fgColor='
1252 || 0
1253 || '>'
1254 || to_char(l_ic_scheduled_end_date,g_date_format||' hh24:mi')
1255 || '</VALUE>';
1256 IF l_task_rec.actual_start_date IS NOT NULL THEN
1257 l_str :=
1258 l_str
1259 || '<LABEL>'
1260 || g_actual_start
1261 || '</LABEL>'
1262 || '<VALUE fgColor='
1263 || 0
1264 || '>'
1265 || to_char(l_ic_actual_start_date,g_date_format||' hh24:mi')
1266 || '</VALUE>'
1267 || '<LABEL>'
1268 || g_end
1269 || '</LABEL>'
1270 || '<VALUE fgColor='
1271 || 0
1272 || '>'
1273 || to_char(l_ic_actual_end_date,g_date_format||' hh24:mi')
1274 || '</VALUE>';
1275 END IF;
1276 l_rs_ic_tz_present :=true;
1277 If l_task_rec.ic_tz_code is null and sch_adv_tz <>'ITZ'
1278 Then
1279 l_str :=
1280 l_str|| '<LINE></LINE>';
1281 l_rs_ic_tz_present :=FALSE;
1282 end if;
1283 ELSE
1284 l_str :=
1285 l_str|| '<LINE></LINE>';
1286 END IF;
1287 If l_task_rec.ic_tz_code is not null
1288 then
1289 l_str :=
1290 l_str
1291 || '<LABEL>'
1292 || g_departure
1293 || '</LABEL>'
1294 || '<VALUE fgColor='
1295 || 0
1296 || '>'
1300 || g_travel_time
1297 || l_rec.departure_time
1298 || '</VALUE>'
1299 || '<LABEL>'
1301 || '</LABEL>'
1302 || '<VALUE fgColor='
1303 || 0
1304 || '>'
1305 || l_rec.travel_time
1306 || '</VALUE>';
1307 end if;
1308 if l_rs_ic_tz_present and sch_adv_tz <> 'ITZ'
1309 then
1310 l_str :=
1311 l_str|| '<LINE></LINE>';
1312 end if;
1313 If sch_adv_tz <> 'ITZ' --this condition is used to restrict the tooltip
1314 --according to logic we should show only incident timezone if schedule advise window is in same.
1315 then
1316 l_str :=
1317 l_str
1318 || '<LABEL>'
1319 || g_planned_start
1320 || '</LABEL>'
1321 || '<VALUE fgColor='
1322 || 0
1323 || '>'
1324 || to_char(l_dc_planned_start_date,g_date_format||' hh24:mi')
1325 || '</VALUE>'
1326 || '<LABEL>'
1327 || g_end
1328 || '</LABEL>'
1329 || '<VALUE fgColor='
1330 || 0
1331 || '>'
1332 || to_char(l_dc_planned_end_date,g_date_format||' hh24:mi')
1333 || '</VALUE>'
1334 || '<LABEL>'
1335 || g_sched_start
1336 || '</LABEL>'
1337 || '<VALUE fgColor='
1338 || 0
1339 || '>'
1340 || to_char(l_dc_scheduled_start_date,g_date_format||' hh24:mi')
1341 || '</VALUE>'
1342 || '<LABEL>'
1343 || g_end
1344 || '</LABEL>'
1345 || '<VALUE fgColor='
1346 || 0
1347 || '>'
1348 || to_char(l_dc_scheduled_end_date,g_date_format||' hh24:mi')
1349 || '</VALUE>';
1350 IF l_task_rec.actual_start_date IS NOT NULL THEN
1351 l_str :=
1352 l_str
1353 || '<LABEL>'
1354 || g_actual_start
1355 || '</LABEL>'
1356 || '<VALUE fgColor='
1357 || 0
1358 || '>'
1359 || to_char(l_dc_actual_start_date,g_date_format||' hh24:mi')
1360 || '</VALUE>'
1361 || '<LABEL>'
1362 || g_end
1363 || '</LABEL>'
1364 || '<VALUE fgColor='
1365 || 0
1366 || '>'
1367 || to_char(l_dc_actual_end_date,g_date_format||' hh24:mi')
1368 || '</VALUE>';
1369 END IF;
1370 End if;--for sch_adv_tz ='ITZ' and l_task_rec.ic_tz_code is not null
1371 If l_task_rec.ic_tz_code is null
1372 then
1373 l_str :=
1374 l_str
1375 || '<LABEL>'
1376 || g_departure
1377 || '</LABEL>'
1378 || '<VALUE fgColor='
1379 || 0
1380 || '>'
1381 || l_rec.departure_time
1382 || '</VALUE>'
1383 || '<LABEL>'
1384 || g_travel_time
1385 || '</LABEL>'
1386 || '<VALUE fgColor='
1387 || 0
1388 || '>'
1389 || l_rec.travel_time
1390 || '</VALUE>';
1391 end if;
1392 l_str :=l_str|| '</TOOLTIP>';
1393 RETURN l_str;
1394 END get_tooltip_data_sch_advise;
1395
1396 FUNCTION convert_to_days(p_duration NUMBER, p_uom VARCHAR2, p_uom_hours VARCHAR2)
1397 RETURN NUMBER IS
1398 l_value NUMBER;
1399 BEGIN
1400 l_value :=
1401 inv_convert.inv_um_convert(
1402 item_id => 0
1403 , PRECISION => 20
1404 , from_quantity => p_duration
1405 , from_unit => p_uom
1406 , to_unit => p_uom_hours
1407 , from_name => NULL
1408 , to_name => NULL
1409 );
1410 RETURN l_value / 24;
1411 END convert_to_days;
1412
1413 FUNCTION get_green
1414 RETURN NUMBER IS
1415 BEGIN
1416 RETURN green;
1417 END;
1418
1419
1420 FUNCTION get_gantt_task_color(
1421 p_task_id IN NUMBER
1422 , p_task_type_id IN NUMBER
1423 , p_task_priority_id IN NUMBER
1424 , p_assignment_status_id IN NUMBER
1425 , p_task_assignment_id IN NUMBER
1426 , p_actual_start_date IN DATE
1427 , p_actual_end_date IN DATE
1428 , p_actual_effort IN NUMBER
1429 , p_actual_effort_uom IN VARCHAR2
1430 , p_planned_effort IN NUMBER
1431 , p_planned_effort_uom IN VARCHAR2
1432 , p_scheduled_start_date IN DATE
1433 , p_scheduled_end_date IN DATE
1434 )
1435 RETURN NUMBER IS
1436 --variable for setting color code for gantt
1437
1438 --variable for storing Profile value set by user
1439 -- This line is commented by vakulkar fnd_profile.value('CSF_TASK_SIGNAL_COLOR');
1440 --when you call this function from gantt, it does not get refreshed value from the buffer
1441 --instead it gets null or old value.
1442 --but when you use fnd_profile.value_specific function it returns the current value.
1443 --variable for returning color code for a task
1444 BEGIN
1445 l_task_custom_color := 'N';
1446
1447 IF p_task_type_id NOT IN(20, 21) THEN
1448 IF p_actual_start_date IS NOT NULL THEN
1452 l_task_custom_color := 'Y';
1449 IF p_actual_end_date IS NOT NULL THEN
1450 IF p_actual_end_date = p_actual_start_date THEN
1451 --set flag for color code
1453 END IF; --end if for actual_end_date=actual_start_date
1454 ELSE
1455 -- This new case is introduced according the Mini-Design made by Peter
1456 -- Which was missing in HLD
1457 -- Changed on 17-dec-2003 for bug 3306656 by vakulkar
1458 IF NVL(p_actual_effort, 0) = 0 THEN
1459 IF NVL(p_planned_effort, 0) = 0 THEN
1460 l_task_custom_color := 'Y';
1461 END IF;
1462 END IF;
1463 -- End of the code added for the change in mini-design
1464 END IF; --end if for actual_end_date is not null
1465 ELSE --for actual start date is null
1466 IF p_scheduled_end_date IS NOT NULL THEN
1467 IF p_scheduled_end_date = p_scheduled_start_date THEN
1468 --set flag for color code
1469 l_task_custom_color := 'Y';
1470 END IF; --end if for scheduled_end_date=scheduled_start_date
1471 ELSE
1472 --set flag for color code
1473 l_task_custom_color := 'Y';
1474 END IF; --end if scheduled end_date is not null
1475 END IF; --end if for actual_start_date is not null
1476 END IF; --end if task_type_id
1477
1478 -- condition for departure and arrival task
1479 IF p_task_type_id IN(20, 21) THEN
1480 IF p_scheduled_start_date IS NOT NULL AND p_scheduled_end_date IS NOT NULL THEN
1481 IF p_scheduled_start_date <> p_scheduled_end_date THEN
1482 IF p_scheduled_end_date > p_scheduled_start_date THEN
1483 --set the color flag
1484 l_task_custom_color := 'Y';
1485 END IF; --if scheduled end_date > than start_date
1486 END IF; --end if for scheduled_end_date is not equal to start_date
1487 END IF; --end if for scheduled_start and end_dates are not null
1488 END IF; --end if for task_type_id
1489
1490 IF l_task_custom_color = 'Y' THEN
1491 --l_rule_id is the profile value set by the user
1492 IF l_rule_id IS NOT NULL THEN
1493 BEGIN
1494 SELECT background_col_dec
1495 INTO l_task_dec_color
1496 FROM jtf_task_custom_colors
1497 WHERE rule_id = l_rule_id;
1498
1499 RETURN(l_task_dec_color);
1500 EXCEPTION
1501 WHEN NO_DATA_FOUND THEN
1502 RETURN(
1503 jtf_task_custom_colors_pub.get_task_dec_bgcolor(p_task_id, p_task_type_id
1504 , p_task_priority_id, p_assignment_status_id)
1505 );
1506 END;
1507 ELSE
1508 -- if profile is not set then return JTF VALUE
1509 RETURN(
1510 jtf_task_custom_colors_pub.get_task_dec_bgcolor(p_task_id, p_task_type_id
1511 , p_task_priority_id, p_assignment_status_id)
1512 );
1513 END IF;
1514 ELSE
1515 RETURN(
1516 jtf_task_custom_colors_pub.get_task_dec_bgcolor(p_task_id, p_task_type_id
1517 , p_task_priority_id, p_assignment_status_id)
1518 );
1519 END IF;
1520 END get_gantt_task_color;
1521
1522 PROCEDURE get_planned_task(
1523 p_api_version IN NUMBER
1524 , p_init_msg_list IN VARCHAR2
1525 , p_request_id IN VARCHAR2
1526 , x_return_status OUT NOCOPY VARCHAR2
1527 , x_msg_count OUT NOCOPY NUMBER
1528 , x_msg_data OUT NOCOPY VARCHAR2
1529 , task_id OUT NOCOPY jtf_varchar2_table_100
1530 , start_date OUT NOCOPY jtf_date_table
1531 , end_date OUT NOCOPY jtf_date_table
1532 , color OUT NOCOPY jtf_number_table
1533 , NAME OUT NOCOPY jtf_varchar2_table_100
1534 , tooltip OUT NOCOPY jtf_varchar2_table_2000
1535 , DURATION OUT NOCOPY jtf_number_table
1536 , task_type_id OUT NOCOPY jtf_number_table
1537 , resource_key OUT NOCOPY jtf_varchar2_table_2000
1538 , sch_adv_tz In Varchar2
1539 ) IS
1540 l_rec tooltip_data_rec_type := NULL;
1541
1542 CURSOR c_planned_task IS
1543 SELECT DECODE(task_id, -1, ROWNUM, task_id) || plan_option_id
1544 , start_time
1545 , end_time
1546 , 0 color
1547 , ' ' NAME
1548 , ' ' tooltip
1549 , TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
1550 + TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5)) travel_time
1551 , NVL(task_type_id, 0)
1552 , resource_id || '-' || resource_type || '-' || plan_option_id
1553 FROM csf_plan_options_v
1554 WHERE sched_request_id = TO_NUMBER(SUBSTR(p_request_id, 1, INSTR(p_request_id, '-', 1) - 1))
1555 AND (
1556 task_id = -1
1557 OR task_id =
1558 TO_NUMBER(SUBSTR(p_request_id, INSTR(p_request_id, '-', 1) + 1
1559 , LENGTH(p_request_id)))
1560 )
1561 AND (task_type_id IS NULL OR(task_type_id <> 20 OR task_type_id <> 21));
1562
1563 l_uom VARCHAR2(2000) := NULL;
1564
1565 CURSOR c_uom(p_code VARCHAR2) IS
1566 SELECT unit_of_measure_tl
1567 FROM mtl_units_of_measure_vl
1568 WHERE uom_code = p_code;
1572
1569
1570 l_api_name CONSTANT VARCHAR2(30) := 'get_planned_task';
1571 l_return_status VARCHAR2(1);
1573 BEGIN
1574 task_id := jtf_varchar2_table_100();
1575 start_date := jtf_date_table();
1576 end_date := jtf_date_table();
1577 color := jtf_number_table();
1578 NAME := jtf_varchar2_table_100();
1579 tooltip := jtf_varchar2_table_2000();
1580 DURATION := jtf_number_table();
1581 task_type_id := jtf_number_table();
1582 resource_key := jtf_varchar2_table_2000();
1583
1584 OPEN c_planned_task;
1585 FETCH c_planned_task
1586 BULK COLLECT INTO task_id
1587 , start_date
1588 , end_date
1589 , color
1590 , NAME
1591 , tooltip
1592 , DURATION
1593 , task_type_id
1594 , resource_key;
1595
1596 if g_tz_enabled ='Y' and g_dflt_tz_for_sc='CTZ' and task_id.count > 0
1597 then
1598 FOR i IN task_id.FIRST .. task_id.LAST
1599 LOOP
1600 start_date(i) :=fnd_date.adjust_datetime(start_date(i),g_client_tz,g_server_tz );
1601 end_date(i) :=fnd_date.adjust_datetime(end_date(i) ,g_client_tz,g_server_tz);
1602 END LOOP;
1603 end if;
1604
1605 IF task_id.COUNT IS NULL THEN
1606 l_return_status := 'E';
1607 ELSE
1608 x_return_status := fnd_api.g_ret_sts_success;
1609 END IF;
1610
1611 IF NOT l_return_status = fnd_api.g_ret_sts_success THEN
1612 -- just return unexpected error, no message, does
1613 -- not matter, SchedulerResource.java will just skip this resource
1614 -- without message, and only generate an error message
1615 -- when all resources have failed
1616 x_return_status := fnd_api.g_ret_sts_unexp_error;
1617 RAISE fnd_api.g_exc_unexpected_error;
1618 END IF;
1619
1620 --standard call to get message count and the message information
1621 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1622 --
1623 EXCEPTION
1624 WHEN fnd_api.g_exc_error THEN
1625 x_return_status := fnd_api.g_ret_sts_error;
1626 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1627 WHEN fnd_api.g_exc_unexpected_error THEN
1628 x_return_status := fnd_api.g_ret_sts_unexp_error;
1629 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1630 WHEN OTHERS THEN
1631 x_return_status := fnd_api.g_ret_sts_unexp_error;
1632
1633 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1634 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1635 END IF;
1636 END get_planned_task;
1637
1638 FUNCTION get_tooltip_for_plan_task(
1639 p_task_id NUMBER
1640 , p_resource_id NUMBER
1641 , p_resource_type VARCHAR2
1642 , p_start_date DATE
1643 , p_end_date DATE
1644 , p_duration NUMBER
1645 , p_inc_tz_code VARCHAR2
1646 , p_server_tz_code VARCHAR2
1647 , p_client_tz_code VARCHAR2
1648 , p_timezone_enb boolean
1649 , sch_adv_tz varchar2
1650 , p_inc_tz_desc varchar2
1651 )
1652 RETURN VARCHAR2 IS
1653 l_scheduled_start Date;
1654 l_scheduled_end VARCHAR2(30);
1655 l_departure_time VARCHAR2(20);
1656 l_travel_time VARCHAR2(100);
1657 l_uom VARCHAR2(15);
1658 l_str VARCHAR2(2000) := NULL;
1659
1660 l_ic_scheduled_start_date date;
1661 l_ic_scheduled_end_date date;
1662 l_dc_scheduled_start_date date;
1663 l_dc_scheduled_end_date date;
1664 l_inc_tz_code VARCHAR2(100);
1665 l_inc_tz_desc VARCHAR2(100);
1666
1667 CURSOR c_uom(p_code VARCHAR2) IS
1668 SELECT unit_of_measure_tl
1669 FROM mtl_units_of_measure_vl
1670 WHERE uom_code = p_code;
1671
1672 BEGIN
1673 IF nvl(p_inc_tz_code,'ERROR') ='ERROR'
1674 THEN
1675 l_inc_tz_code :=NULL;
1676 ELSE
1677 l_inc_tz_code :=p_inc_tz_code;
1678 END IF;
1679
1680 if NVL(p_inc_tz_desc,'ERROR') = 'ERROR'
1681 then
1682 l_inc_tz_desc := null;
1683 else
1684 l_inc_tz_desc := p_inc_tz_desc;
1685 end if;
1686
1687 IF p_timezone_enb
1688 THEN
1689 IF sch_adv_tz ='ITZ'
1690 THEN
1691 l_ic_scheduled_start_date := fnd_date.adjust_datetime(p_start_date,p_server_tz_code,p_client_tz_code);
1692 l_ic_scheduled_end_date := fnd_date.adjust_datetime(p_end_date,p_server_tz_code,p_client_tz_code);
1693 l_scheduled_start := l_ic_scheduled_start_date;
1694 ELSIF sch_adv_tz = 'UTZ'
1695 THEN
1696 l_dc_scheduled_start_date :=fnd_date.adjust_datetime(p_start_date,p_server_tz_code,p_client_tz_code);
1697 l_dc_scheduled_end_date :=fnd_date.adjust_datetime(p_end_date,p_server_tz_code,p_client_tz_code);
1698 l_scheduled_start :=l_dc_scheduled_start_date;
1699 IF p_inc_tz_code is not null and l_inc_tz_code is not null
1700 THEN
1701 l_ic_scheduled_start_date := fnd_date.adjust_datetime(p_start_date,p_server_tz_code,p_inc_tz_code);
1702 l_ic_scheduled_end_date := fnd_date.adjust_datetime(p_end_date,p_server_tz_code,p_inc_tz_code);
1703 END IF;
1704 ELSE
1705 l_dc_scheduled_start_date := p_start_date;
1709 THEN
1706 l_dc_scheduled_end_date := p_end_date;
1707 l_scheduled_start := l_dc_scheduled_start_date;
1708 IF p_inc_tz_code is not null and l_inc_tz_code is not null
1710 l_ic_scheduled_start_date := fnd_date.adjust_datetime(p_start_date,p_server_tz_code,p_inc_tz_code);
1711 l_ic_scheduled_end_date := fnd_date.adjust_datetime(p_end_date,p_server_tz_code,p_inc_tz_code);
1712 END IF;
1713 END IF;
1714
1715 l_departure_time :=
1716 TO_CHAR
1717 (l_scheduled_start
1718 - (inv_convert.inv_um_convert(0, NULL, p_duration, g_uom_minutes, g_uom_hours, NULL
1719 , NULL)
1720 / 24
1721 )
1722 , 'hh24:mi'
1723 );
1724
1725 ELSE
1726 l_dc_scheduled_start_date := p_start_date;
1727 l_dc_scheduled_end_date := p_end_date;
1728 l_scheduled_start := l_dc_scheduled_start_date;
1729 l_departure_time :=
1730 TO_CHAR
1731 (l_scheduled_start
1732 - (inv_convert.inv_um_convert(0, NULL, p_duration, g_uom_minutes, g_uom_hours, NULL
1733 , NULL)
1734 / 24
1735 )
1736 , 'hh24:mi'
1737 );
1738 END IF;
1739 OPEN c_uom(g_uom_minutes);
1740 FETCH c_uom
1741 INTO l_uom;
1742 IF c_uom%NOTFOUND THEN
1743 l_uom := g_uom_minutes;
1744 END IF;
1745 CLOSE c_uom;
1746
1747 l_travel_time := p_duration || ' ' || l_uom;
1748 l_str :=
1749 '<TOOLTIP>'
1750 || '<CENTER fgColor='
1751 || 65280
1752 || '>'
1753 || g_option
1754 || '</CENTER>'
1755 || '<LINE></LINE>';
1756 IF p_timezone_enb
1757 THEN
1758 l_str :=
1759 l_str
1760 || '<LABEL>'
1761 || g_timezone
1762 || '</LABEL>'
1763 || '<VALUE fgColor='
1764 || 65280
1765 || '>'
1766 || l_inc_tz_desc
1767 || '</VALUE>'
1768 || '<LABEL>'
1769 || g_sched_start
1770 || '</LABEL>'
1771 || '<VALUE fgColor='
1772 || 65280
1773 || '>'
1774 || to_char(l_ic_scheduled_start_date,g_date_format||' hh24:mi')
1775 || '</VALUE>'
1776 || '<LABEL>'
1777 || g_end
1778 || '</LABEL>'
1779 || '<VALUE fgColor='
1780 || 65280
1781 || '>'
1782 || to_char(l_ic_scheduled_end_date,g_date_format||' hh24:mi')
1783 || '</VALUE>';
1784 END IF;
1785 If sch_adv_tz <> 'ITZ'
1786 then
1787 l_str :=
1788 l_str|| '<LINE></LINE>';
1789 end if;
1790
1791
1792 If sch_adv_tz <> 'ITZ'
1793 then
1794 l_str :=
1795 l_str
1796 || '<LABEL>'
1797 || g_sched_start
1798 || '</LABEL>'
1799 || '<VALUE fgColor='
1800 || 65280
1801 || '>'
1802 || to_char(l_dc_scheduled_start_date,g_date_format||' hh24:mi')
1803 || '</VALUE>'
1804 || '<LABEL>'
1805 || g_end
1806 || '</LABEL>'
1807 || '<VALUE fgColor='
1808 || 65280
1809 || '>'
1810 || to_char(l_dc_scheduled_end_date,g_date_format||' hh24:mi')
1811 || '</VALUE>';
1812 End If;
1813 l_str :=
1814 l_str
1815 || '<LABEL>'
1816 || g_departure
1817 || '</LABEL>'
1818 || '<VALUE fgColor='
1819 || 65280
1820 || '>'
1821 || l_departure_time
1822 || '</VALUE>'
1823 || '<LABEL>'
1824 || g_travel_time
1825 || '</LABEL>'
1826 || '<VALUE fgColor='
1827 || 65280
1828 || '>'
1829 || l_travel_time
1830 || '</VALUE>'
1831 || '</TOOLTIP>';
1832 RETURN l_str;
1833 END get_tooltip_for_plan_task;
1834
1835 FUNCTION get_skilled_resources(
1836 p_task_id NUMBER
1837 , p_start DATE
1838 , p_end DATE
1839 , p_resource_id NUMBER DEFAULT NULL
1840 , p_resource_type VARCHAR2 DEFAULT NULL
1841 )
1842 RETURN NUMBER IS
1843 j NUMBER := 0;
1844 l_levelmatch NUMBER := NULL;
1845 l_start date;
1846 l_end date;
1847
1848 --modified the following cursor to check for skill_active date range for bug 3418658
1849 CURSOR c_resource_plan_window(
1850 p_task_id NUMBER
1851 , p_start DATE
1852 , p_end DATE
1853 , p_resource_id NUMBER
1854 , p_resource_type VARCHAR2
1855 ) IS
1856 SELECT rs.resource_id
1857 , rs.resource_type
1858 , rs.winstart
1859 , rs.winend
1860 , rs.amount
1861 FROM (SELECT rs.resource_id
1862 , rs.resource_type
1863 , GREATEST(MAX(rs.start_date_active), NVL(MAX(ss.start_date_active), p_start)
1864 , p_start) winstart
1865 , LEAST(
1866 NVL(MIN(rs.end_date_active + 1), p_end)
1867 , NVL(MIN(ss.end_date_active + 1), p_end)
1871 FROM csf_resource_skills_b rs
1868 , p_end
1869 ) winend
1870 , COUNT(*) amount
1872 , csf_required_skills_b ts
1873 , csf_skill_levels_b rsl
1874 , csf_skill_levels_b tsl
1875 , csf_skills_b ss
1876 WHERE DECODE(
1877 SIGN(rsl.step_value - tsl.step_value)
1878 , -1, DECODE(l_levelmatch, 1, 'Y', 'N')
1879 , 0, 'Y'
1880 , 1, DECODE(l_levelmatch, 3, 'Y', 'N')
1881 ) = 'Y'
1882 AND rsl.skill_level_id = rs.skill_level_id
1883 AND tsl.skill_level_id = ts.skill_level_id
1884 AND ts.skill_id = rs.skill_id
1885 AND ts.skill_type_id = rs.skill_type_id
1886 AND TRUNC(rs.start_date_active) < p_end
1887 AND TRUNC(rs.start_date_active) <=trunc(p_start)
1888 AND (TRUNC(rs.end_date_active ) >=trunc(p_start) OR rs.end_date_active IS NULL)
1889 AND (rs.resource_id = p_resource_id OR p_resource_id IS NULL)
1890 AND (rs.resource_type = p_resource_type OR p_resource_type IS NULL)
1891 AND NVL(ts.disabled_flag, 'N') <> 'Y'
1892 AND ts.has_skill_type = 'TASK'
1893 AND ts.has_skill_id = p_task_id
1894 AND ss.skill_id(+) = rs.skill_id
1895 AND (
1896 (
1897 rs.skill_type_id <> 2
1898 AND TRUNC(ss.start_date_active) < p_end
1899 AND (
1900 TRUNC(ss.end_date_active + 1) > p_start
1901 OR ss.end_date_active IS NULL
1902 )
1903 )
1904 OR EXISTS(SELECT 1
1905 FROM mtl_system_items_kfv msi
1906 WHERE msi.inventory_item_id = ts.skill_id)
1907 )
1908 GROUP BY rs.resource_id, rs.resource_type) rs
1909 , (SELECT COUNT(*) amount
1910 FROM csf_required_skills_b
1911 WHERE NVL(disabled_flag, 'N') <> 'Y'
1912 AND has_skill_type = 'TASK'
1913 AND has_skill_id = p_task_id) ts
1914 WHERE rs.amount = ts.amount AND rs.winstart <= rs.winend;
1915
1916 --
1917 l_rec c_resource_plan_window%ROWTYPE;
1918 BEGIN
1919 -- Retrieving the profile value that will be used
1920 -- to determine the satifactory level of the skills
1921 -- needed to perform the task.
1922 -- The ff. are the possible profile values:
1923 -- 1 for EQUAL TO or SMALLER THAN
1924 -- 2 for EQUAL TO
1925 -- 3 for EQUAL TO or GREATER THAN
1926 -- In case the profile return a null, the default value
1927 -- will be 2 (EQUAL TO).
1928 l_levelmatch := NVL(fnd_profile.VALUE('CSF_SKILL_LEVEL_MATCH'), 2);
1929
1930 l_start := csf_timezones_pvt.date_to_client_tz_date(p_start);
1931 l_end := csf_timezones_pvt.date_to_client_tz_date(p_end);
1932 OPEN c_resource_plan_window(p_task_id, l_start, l_end, p_resource_id, p_resource_type);
1933 FETCH c_resource_plan_window
1934 INTO l_rec;
1935
1936 IF c_resource_plan_window%FOUND THEN
1937 RETURN 1;
1938 END IF;
1939
1940 CLOSE c_resource_plan_window;
1941
1942 RETURN 0;
1943 END get_skilled_resources;
1944
1945 FUNCTION get_resource_name(p_res_id NUMBER, p_res_type VARCHAR2)
1946 RETURN VARCHAR2 IS
1947 BEGIN
1948 RETURN csf_resource_pub.get_resource_name(p_res_id,p_res_type);
1949 EXCEPTION
1950 WHEN OTHERS THEN
1951 RETURN NULL;
1952 END get_resource_name;
1953
1954 FUNCTION get_resource_type_name(p_res_type VARCHAR2)
1955 RETURN VARCHAR2 IS
1956
1957 BEGIN
1958 RETURN CSF_RESOURCE_PUB.get_resource_type_name(p_res_type);
1959 EXCEPTION
1960 WHEN OTHERS THEN
1961 RETURN NULL;
1962 END get_resource_type_name;
1963
1964 PROCEDURE drag_n_drop(
1965 p_api_version IN NUMBER
1966 , p_init_msg_list IN VARCHAR2
1967 , p_commit IN VARCHAR2
1968 , p_task_id IN NUMBER
1969 , p_task_assignment_id IN NUMBER DEFAULT NULL
1970 , p_object_version_number IN OUT NOCOPY NUMBER
1971 , p_old_resource_type_code IN VARCHAR2
1972 , p_new_resource_type_code IN VARCHAR2
1973 , p_old_resource_id IN NUMBER
1974 , p_new_resource_id IN NUMBER
1975 , p_cancel_status_id IN NUMBER
1976 , p_assignment_status_id IN NUMBER
1977 , p_old_object_capacity_id IN NUMBER
1978 , p_new_object_capacity_id IN NUMBER
1979 , p_sched_travel_distance IN NUMBER DEFAULT NULL
1980 , p_sched_travel_duration IN NUMBER DEFAULT NULL
1981 , p_sched_travel_duration_uom IN VARCHAR2 DEFAULT NULL
1982 , p_old_shift_construct_id IN NUMBER DEFAULT NULL
1983 , p_new_shift_construct_id IN NUMBER DEFAULT NULL
1984 , p_shift_changed IN BOOLEAN
1985 , p_task_changed IN BOOLEAN
1989 , p_new_sched_end_date IN DATE
1986 , p_assignment_changed IN BOOLEAN
1987 , p_time_occupied IN NUMBER
1988 , p_new_sched_start_date IN DATE
1990 , p_update_plan_date in VARCHAR2 DEFAULT 'N'
1991 , p_planned_start_date IN DATE DEFAULT NULL
1992 , p_planned_end_date IN DATE DEFAULT NULL
1993 , p_planned_effort IN NUMBER DEFAULT NULL
1994 , p_planned_effort_uom IN VARCHAR2 DEFAULT NULL
1995 , x_return_status OUT NOCOPY VARCHAR2
1996 , x_msg_count OUT NOCOPY NUMBER
1997 , x_msg_data OUT NOCOPY VARCHAR2
1998 , x_task_assignment_id OUT NOCOPY NUMBER
1999 , x_task_object_version_number OUT NOCOPY NUMBER
2000 , x_task_status_id OUT NOCOPY NUMBER
2001 , x_task_status_name OUT NOCOPY VARCHAR2
2002 , x_task_type_id OUT NOCOPY NUMBER
2003 ) IS
2004 l_api_name CONSTANT VARCHAR2(30) := 'drag_n_drop';
2005 l_api_version CONSTANT NUMBER := 1.0;
2006 l_old_task_obj_ver_number NUMBER;
2007 l_task_status_id NUMBER;
2008 l_start_date DATE;
2009 l_end_date DATE;
2010 l_old_ta_obj_version NUMBER;
2011 l_obj_ver_number VARCHAR2(20);
2012 l_planned_effort NUMBER := fnd_api.g_miss_num;
2013 l_planned_effort_uom VARCHAR2(10) := fnd_api.g_miss_char;
2014 l_parent_task_id NUMBER;
2015
2016 CURSOR c_task(p_task_id NUMBER) IS
2017 SELECT object_version_number
2018 , task_status_id
2019 , scheduled_start_date
2020 , scheduled_end_date
2021 , parent_task_id
2022 FROM jtf_tasks_b
2023 WHERE task_id = p_task_id;
2024
2025 CURSOR c_assign_obj_ver IS
2026 SELECT object_version_number
2027 , actual_start_date
2028 , actual_end_date
2029 FROM JTF_TASK_ASSIGNMENTS
2030 WHERE task_id= p_task_id
2031 AND task_assignment_id =p_task_assignment_id;
2032
2033 CURSOR c_parent_ovn(p_task_id number) IS
2034 SELECT object_version_number
2035 FROM jtf_tasks_b
2036 WHERE task_id = p_task_id;
2037
2038 BEGIN
2039 -- Standard start of API savepoint
2040 SAVEPOINT drag_n_drop;
2041
2042 -- Standard call to check for call compatibility
2043 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name
2044 , 'csf_gantt_data_pkg') THEN
2045 RAISE fnd_api.g_exc_unexpected_error;
2046 END IF;
2047
2048 -- Initialize message list if p_init_msg_list is set to TRUE
2049 IF fnd_api.to_boolean(p_init_msg_list) THEN
2050 fnd_msg_pub.initialize;
2051 END IF;
2052
2053 -- Initialize API return status to success
2054 x_return_status := fnd_api.g_ret_sts_success;
2055 IF p_task_changed OR p_assignment_changed THEN
2056 OPEN c_task(p_task_id);
2057
2058 FETCH c_task
2059 INTO l_old_task_obj_ver_number
2060 , l_task_status_id
2061 , l_start_date
2062 , l_end_date
2063 , l_parent_task_id;
2064
2065 IF c_task%NOTFOUND THEN
2066 CLOSE c_task;
2067 RAISE NO_DATA_FOUND;
2068 END IF;
2069
2070 CLOSE c_task;
2071
2072 OPEN c_assign_obj_ver;
2073 FETCH c_assign_obj_ver
2074 INTO l_old_ta_obj_version,l_start_date,l_end_date;
2075 IF c_assign_obj_ver%NOTFOUND THEN
2076 CLOSE c_assign_obj_ver;
2077 RAISE NO_DATA_FOUND;
2078 END IF;
2079 CLOSE c_assign_obj_ver;
2080
2081 IF p_assignment_changed
2082 OR p_shift_changed
2083 OR l_start_date IS NOT NULL
2084 THEN
2085 l_task_status_id := p_assignment_status_id;
2086 END IF;
2087
2088 IF p_planned_effort IS NOT NULL
2089 AND p_planned_effort_uom IS NOT NULL
2090 THEN
2091 l_planned_effort := p_planned_effort;
2092 l_planned_effort_uom := p_planned_effort_uom;
2093 END IF;
2094
2095 csf_tasks_pub.update_task(
2096 p_api_version => 1.0
2097 , p_init_msg_list => fnd_api.g_true
2098 , p_commit => fnd_api.g_false
2099 , p_task_id => p_task_id
2100 , p_object_version_number => l_old_task_obj_ver_number
2101 , p_planned_start_date => nvl(p_planned_start_date,fnd_api.g_miss_date)
2102 , p_planned_end_date => nvl(p_planned_end_date,fnd_api.g_miss_date)
2103 , p_scheduled_start_date => p_new_sched_start_date
2104 , p_scheduled_end_date => p_new_sched_end_date
2105 , p_actual_start_date => fnd_api.g_miss_date
2106 , p_actual_end_date => fnd_api.g_miss_date
2107 , p_planned_effort => l_planned_effort
2108 , p_planned_effort_uom => l_planned_effort_uom
2109 , p_task_status_id => l_task_status_id
2110 , x_return_status => x_return_status
2111 , x_msg_count => x_msg_count
2112 , x_msg_data => x_msg_data
2113 );
2114
2115 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2116 RAISE fnd_api.g_exc_error;
2120
2117 END IF;
2118
2119 END IF;
2121 IF p_assignment_changed
2122 OR l_start_date IS NOT NULL
2123 THEN
2124 csf_task_assignments_pub.create_task_assignment(
2125 p_api_version => 1.0
2126 , p_init_msg_list => fnd_api.g_true
2127 , p_commit => fnd_api.g_false
2128 , p_task_id => p_task_id
2129 , p_resource_type_code => p_new_resource_type_code
2130 , p_resource_id => p_new_resource_id
2131 , p_assignment_status_id => p_assignment_status_id
2132 , p_shift_construct_id => p_new_shift_construct_id
2133 , p_object_capacity_id => p_new_object_capacity_id
2134 , p_sched_travel_distance => p_sched_travel_distance
2135 , p_sched_travel_duration => p_sched_travel_duration
2136 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
2137 , x_return_status => x_return_status
2138 , x_msg_count => x_msg_count
2139 , x_msg_data => x_msg_data
2140 , x_task_assignment_id => x_task_assignment_id
2141 , x_ta_object_version_number => p_object_version_number
2142 , x_task_object_version_number => x_task_object_version_number
2143 , x_task_status_id => x_task_status_id
2144 );
2145
2146 IF NVL(x_return_status, 'S') = fnd_api.g_ret_sts_success THEN
2147 csf_task_assignments_pub.update_task_assignment(
2148 p_api_version => 1.0
2149 , p_init_msg_list => fnd_api.g_true
2150 , p_commit => fnd_api.g_false
2151 , p_task_assignment_id => p_task_assignment_id
2152 , p_object_version_number => l_old_ta_obj_version
2153 , p_task_id => p_task_id
2154 , p_resource_type_code => p_old_resource_type_code
2155 , p_resource_id => p_old_resource_id
2156 , p_resource_territory_id => fnd_api.g_miss_num
2157 , p_assignment_status_id => p_cancel_status_id
2158 , p_actual_start_date => fnd_api.g_miss_date
2159 , p_actual_end_date => fnd_api.g_miss_date
2160 , p_sched_travel_distance => fnd_api.g_miss_num
2161 , p_sched_travel_duration => fnd_api.g_miss_num
2162 , p_sched_travel_duration_uom => fnd_api.g_miss_char
2163 , p_shift_construct_id => p_old_shift_construct_id
2164 , p_object_capacity_id => p_old_object_capacity_id
2165 , x_return_status => x_return_status
2166 , x_msg_count => x_msg_count
2167 , x_msg_data => x_msg_data
2168 , x_task_object_version_number => x_task_object_version_number
2169 , x_task_status_id => x_task_status_id
2170 );
2171 IF NVL(x_return_status, 'S') <> fnd_api.g_ret_sts_success THEN
2172 RAISE fnd_api.g_exc_error;
2173 END IF;
2174 ELSE
2175 RAISE fnd_api.g_exc_error;
2176 END IF;
2177 ELSIF p_shift_changed THEN
2178 csf_task_assignments_pub.update_task_assignment(
2179 p_api_version => 1.0
2180 , p_init_msg_list => fnd_api.g_true
2181 , p_commit => fnd_api.g_false
2182 , p_task_assignment_id => p_task_assignment_id
2183 , p_object_version_number => l_old_ta_obj_version
2184 , p_task_id => p_task_id
2185 , p_resource_type_code => p_old_resource_type_code
2186 , p_resource_id => p_old_resource_id
2187 , p_resource_territory_id => fnd_api.g_miss_num
2188 , p_assignment_status_id => p_assignment_status_id
2189 , p_actual_start_date => fnd_api.g_miss_date
2190 , p_actual_end_date => fnd_api.g_miss_date
2191 , p_sched_travel_distance => p_sched_travel_distance
2192 , p_sched_travel_duration => p_sched_travel_duration
2193 , p_sched_travel_duration_uom => p_sched_travel_duration_uom
2194 , p_shift_construct_id => p_new_shift_construct_id
2195 , p_object_capacity_id => p_new_object_capacity_id
2196 , x_return_status => x_return_status
2197 , x_msg_count => x_msg_count
2198 , x_msg_data => x_msg_data
2199 , x_task_object_version_number => x_task_object_version_number
2200 , x_task_status_id => x_task_status_id
2201 );
2202 IF NVL(x_return_status, 'S') <> fnd_api.g_ret_sts_success THEN
2203 RAISE fnd_api.g_exc_error;
2204 END IF;
2205 ELSIF p_task_changed and l_parent_task_id IS NOT NULL
2206 THEN
2207 OPEN c_parent_ovn(l_parent_task_id);
2208 FETCH c_parent_ovn INTO l_obj_ver_number;
2209 CLOSE c_parent_ovn;
2210
2211 -- Sync up the Parent and all the other Siblings
2212 csf_tasks_pub.update_task_longer_than_shift(
2213 p_api_version => 1.0
2214 , p_init_msg_list => fnd_api.g_true
2215 , p_commit => fnd_api.g_false
2216 , x_return_status => x_return_status
2217 , x_msg_count => x_msg_count
2218 , x_msg_data => x_msg_data
2219 , p_task_id => l_parent_task_id
2220 , p_object_version_number => l_obj_ver_number
2221 , p_action => csf_tasks_pub.g_action_normal_to_parent
2222 );
2226 END IF;
2223 IF NVL(x_return_status, 'S') <> fnd_api.g_ret_sts_success THEN
2224 RAISE fnd_api.g_exc_error;
2225 END IF;
2227
2228 -- Standard check of p_commit
2229 IF fnd_api.to_boolean(p_commit) THEN
2230 COMMIT WORK;
2231 END IF;
2232
2233 -- Standard call to get message count and if count is 1, get message info
2234 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2235 EXCEPTION
2236 WHEN fnd_api.g_exc_error THEN
2237 ROLLBACK TO drag_n_drop;
2238 x_return_status := fnd_api.g_ret_sts_error;
2239 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2240 WHEN fnd_api.g_exc_unexpected_error THEN
2241 ROLLBACK TO drag_n_drop;
2242 x_return_status := fnd_api.g_ret_sts_unexp_error;
2243 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2244 WHEN OTHERS THEN
2245 ROLLBACK TO drag_n_drop;
2246 x_return_status := fnd_api.g_ret_sts_unexp_error;
2247
2248 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2249 fnd_msg_pub.add_exc_msg('csf_gantt_data_pkg', l_api_name);
2250 END IF;
2251
2252 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2253 END drag_n_drop;
2254
2255 PROCEDURE g_get_custom_color IS
2256 CURSOR c_custom_color IS
2257 SELECT type_id
2258 , priority_id
2259 , assignment_status_id
2260 , escalated_task
2261 , background_col_dec
2262 , background_col_rgb
2263 FROM jtf_task_custom_colors
2264 WHERE active_flag = 'Y'
2265 ORDER BY color_determination_priority;
2266
2267 i BINARY_INTEGER := 0;
2268 BEGIN
2269 IF gl_custom_color_tbl.COUNT = 0 THEN
2270 FOR rec IN c_custom_color LOOP
2271 i := i + 1;
2272 gl_custom_color_tbl(i).task_type_id := rec.type_id;
2273 gl_custom_color_tbl(i).task_priority_id := rec.priority_id;
2274 gl_custom_color_tbl(i).assignment_status_id := rec.assignment_status_id;
2275 gl_custom_color_tbl(i).escalated_task := rec.escalated_task;
2276 gl_custom_color_tbl(i).background_col_dec := rec.background_col_dec;
2277 gl_custom_color_tbl(i).background_col_rgb := rec.background_col_rgb;
2278 END LOOP;
2279 END IF;
2280 END g_get_custom_color;
2281
2282 FUNCTION g_do_match(
2283 p_task_type_id IN NUMBER
2284 , p_task_priority_id IN NUMBER
2285 , p_assignment_status_id IN NUMBER
2286 , p_escalated_task IN VARCHAR2
2287 )
2288 RETURN NUMBER IS
2289 BEGIN
2290 FOR i IN gl_custom_color_tbl.FIRST .. gl_custom_color_tbl.LAST LOOP
2291 IF NVL(gl_custom_color_tbl(i).task_type_id, p_task_type_id) = p_task_type_id
2292 AND NVL(gl_custom_color_tbl(i).task_priority_id, p_task_priority_id) = p_task_priority_id
2293 AND NVL(gl_custom_color_tbl(i).assignment_status_id, p_assignment_status_id) =
2294 p_assignment_status_id
2295 AND NVL(gl_custom_color_tbl(i).escalated_task, p_escalated_task) = p_escalated_task THEN
2296 RETURN NVL(gl_custom_color_tbl(i).background_col_dec, 0);
2297 END IF;
2298 END LOOP;
2299
2300 RETURN 0;
2301 END g_do_match;
2302 function convert_to_min
2303 ( p_duration number
2304 , p_uom varchar2
2305 , p_uom_min varchar2
2306 )
2307 return number
2308 is
2309 l_value number;
2310 begin
2311 l_value := inv_convert.inv_um_convert
2312 ( item_id => 0
2313 , precision => null
2314 , from_quantity => p_duration
2315 , from_unit => nvl(p_uom,g_uom_minutes)
2316 , to_unit => nvl(p_uom_min,g_uom_minutes)
2317 , from_name => null
2318 , to_name => null
2319 );
2320 return l_value;
2321 end convert_to_min;
2322
2323 Procedure get_dispatch_task_dtls (
2324 p_api_version IN Number
2325 , p_init_msg_list IN Varchar2 DEFAULT NULL
2326 , x_return_status OUT NOCOPY Varchar2
2327 , x_msg_count OUT NOCOPY Number
2328 , x_msg_data OUT NOCOPY Varchar2
2329 , p_start_date_range IN DATE
2330 , p_end_date_range IN DATE
2331 , p_res_id OUT NOCOPY jtf_number_table
2332 , p_res_type OUT NOCOPY jtf_varchar2_table_2000
2333 , p_res_name OUT NOCOPY jtf_varchar2_table_2000
2334 , p_res_typ_name OUT NOCOPY jtf_varchar2_table_2000
2335 , p_res_key OUT NOCOPY jtf_varchar2_table_2000
2336 , p_trip_id OUT NOCOPY jtf_number_table
2337 , p_shift_start_date OUT NOCOPY jtf_date_table
2338 , p_shift_end_date OUT NOCOPY jtf_date_table
2339 , p_block_trip OUT NOCOPY jtf_number_table
2340 , p_shift_res_key OUT NOCOPY jtf_varchar2_table_2000
2341 , p_vir_task_id OUT NOCOPY jtf_varchar2_table_100
2342 , p_vir_start_date OUT NOCOPY jtf_date_table
2343 , p_vir_end_date OUT NOCOPY jtf_date_table
2347 , p_vir_task_type_id OUT NOCOPY jtf_number_table
2344 , p_vir_color OUT NOCOPY jtf_number_table
2345 , p_vir_name OUT NOCOPY jtf_varchar2_table_100
2346 , p_vir_duration OUT NOCOPY jtf_number_table
2348 , p_vir_tooltip OUT NOCOPY jtf_varchar2_table_2000
2349 , p_vir_resource_key OUT NOCOPY jtf_varchar2_table_2000
2350 , real_task_id OUT NOCOPY jtf_varchar2_table_100
2351 , real_start_date OUT NOCOPY jtf_date_table
2352 , real_end_date OUT NOCOPY jtf_date_table
2353 , real_color OUT NOCOPY jtf_number_table
2354 , real_NAME OUT NOCOPY jtf_varchar2_table_2000
2355 , real_tooltip OUT NOCOPY jtf_varchar2_table_2000
2356 , real_DURATION OUT NOCOPY jtf_number_table
2357 , real_task_type_id OUT NOCOPY jtf_number_table
2358 , real_resource_key OUT NOCOPY jtf_varchar2_table_2000
2359 , real_parts_required OUT NOCOPY jtf_varchar2_table_100
2360 , real_access_hours OUT NOCOPY jtf_varchar2_table_100
2361 , real_after_hours OUT NOCOPY jtf_varchar2_table_100
2362 , real_customer_conf OUT NOCOPY jtf_varchar2_table_100
2363 , real_task_depend OUT NOCOPY jtf_varchar2_table_100
2364 , real_child_task OUT NOCOPY jtf_varchar2_table_100
2365 , p_show_arr_dep_tasks IN varchar2 DEFAULT 'N'
2366 )
2367 IS
2368 l_assignment_id jtf_number_table;
2369 l_task_priority_id jtf_number_table;
2370 l_status_id jtf_number_table;
2371 l_planned_start_date jtf_date_table;
2372 l_planned_end_date jtf_date_table;
2373 l_actual_start_date jtf_date_table;
2374 l_actual_end_date jtf_date_table;
2375 l_actual_effort jtf_number_table;
2376 l_actual_effort_uom jtf_varchar2_table_100;
2377 l_planned_effort jtf_number_table;
2378 l_planned_effort_uom jtf_varchar2_table_100;
2379 l_escalated_task jtf_varchar2_table_100;
2380 l_scheduled_start_date jtf_date_table;
2381 l_scheduled_end_date jtf_date_table;
2382 ---------------------------------------------------
2383 --The below variables are used in color coding proc
2384 ---------------------------------------------------
2385 p_cur_task_type_id NUMBER(10);
2386 p_cur_task_priority_id NUMBER(10);
2387 p_cur_task_status_id NUMBER(10);
2388 p_cur_escalated_task VARCHAR2(1);
2389 p_color NUMBER(30);
2390 p_cur_color NUMBER(30);
2391 p_rule_id NUMBER(10);
2392
2393 CURSOR C_Terr_Resource
2394 IS SELECT DISTINCT TR.RESOURCE_ID RESOURCE_ID,
2395 TR.RESOURCE_TYPE RESOURCE_TYPE,
2396 TR.RESOURCE_NAME RESOURCE_NAME,
2397 CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME( TR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME,
2398 TR.RESOURCE_ID||'-'||TR.RESOURCE_TYPE
2399 FROM CSF_SELECTED_RESOURCES_V TR
2400 ORDER BY UPPER(TR.RESOURCE_NAME);
2401
2402 CURSOR C_Resource_Shift(p_start_date DATE, p_end_date DATE) IS
2403 SELECT object_capacity_id
2404 , start_date_time
2405 , end_date_time
2406 , status blocked_trip
2407 , res_info.resource_id||'-'||res_info.resource_type resource_key
2408 FROM cac_sr_object_capacity ca,(SELECT TO_NUMBER(
2409 SUBSTR(column_value
2410 , 1
2411 , INSTR(column_value, '-', 1, 1) - 1
2412 )
2413 )resource_id
2414 , SUBSTR(column_value
2415 , INSTR(column_value, '-', 1, 1) + 1
2416 ,LENGTH(column_value)
2417 ) resource_type
2418 FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
2419 ) res_info
2420 WHERE ca.object_type = res_info.resource_type
2421 AND ca.object_id = res_info.resource_id
2422 AND TRUNC(ca.start_date_time) >= (p_start_date -1)
2423 AND TRUNC(ca.end_date_time) <= p_end_date;
2424
2425 CURSOR get_tdu_color(t_rule_id NUMBER) IS
2426 SELECT background_col_dec
2427 FROM jtf_task_custom_colors
2428 WHERE rule_id = t_rule_id;
2429
2430 CURSOR C_Virtual_Tasks
2431 IS
2432 SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb)
2433 INDEX (t,JTF_TASKS_B_U3)
2434 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
2435 a.task_id || '-' || a.task_assignment_id
2436 , scheduled_start_date
2437 , scheduled_end_date
2438 , 0 color
2439 , ' ' task_name
2440 , NVL(sched_travel_duration, 0)
2441 , t.task_type_id
2442 , '0' tooltip
2443 , a.resource_id || '-' || a.resource_type_code resource_key
2444 , task_assignment_id
2445 , task_priority_id
2446 , assignment_status_id
2447 , planned_start_date
2448 , planned_end_date
2449 , a.actual_start_date
2450 , a.actual_end_date
2451 , t.actual_effort
2452 , t.actual_effort_uom
2453 , t.planned_effort
2457 , scheduled_end_date
2454 , t.planned_effort_uom
2455 , 'N' escalated_task
2456 , scheduled_start_date
2458 FROM (SELECT TO_NUMBER(SUBSTR(column_value
2459 , 1
2460 , INSTR(column_value, '-', 1, 1) - 1
2461 )
2462 )resource_id
2463 , SUBSTR(column_value
2464 , INSTR(column_value, '-', 1, 1) + 1
2465 ,LENGTH(column_value)
2466 ) resource_type
2467 FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
2468 ) res_info
2469 , jtf_task_assignments a
2470 , jtf_tasks_b t
2471 , jtf_task_types_b tt
2472 , jtf_task_statuses_b tsa
2473 , jtf_task_statuses_b tsb
2474 WHERE t.task_id = a.task_id
2475 AND t.task_type_id = tt.task_type_id
2476 AND (t.task_type_id = 20 OR t.task_type_id = 21)
2477 AND NVL(t.deleted_flag, 'N') <> 'Y'
2478 AND booking_end_date >= booking_start_date
2479 AND booking_start_date >= (p_start_date_range -1)
2480 AND TRUNC(booking_end_date) <= TRUNC(p_end_date_range)
2481 AND a.resource_id = res_info.resource_id
2482 AND a.resource_type_code = res_info.resource_type
2483 AND tsb.task_status_id = t.task_status_id
2484 AND tsa.task_status_id = a.assignment_status_id
2485 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
2486 AND NVL(tsb.cancelled_flag, 'N') <> 'Y';
2487
2488
2489 l_resource_dtls csf_resource_pub.resource_rec_type;
2490 l_cnt number (10);
2491
2492 --------------------------------------------------------------------
2493 --all the variable and cursor used for getting real task information
2494 --------------------------------------------------------------------
2495 l_task_depends varchar2(1);
2496 l_actual_travel_duration jtf_number_table;
2497 l_actual_travel_duration_uom jtf_varchar2_table_100;
2498 l_task_depend jtf_varchar2_table_100;
2499 l_csf_default_effort NUMBER;
2500
2501 CURSOR c_icon_setup
2502 IS
2503 SELECT active
2504 FROM csf_gnticons_setup_v
2505 WHERE seq_id = 6;
2506
2507 ---------------------------------------------------------------------------------------------
2508 --Cursor C1 introduced when show labels on taskbar is true i.e join for hz_parties for showing
2509 --party name on taskbar and this cursor is without task dependenciea join.
2510 ---------------------------------------------------------------------------------------------
2511
2512 CURSOR c1
2513 IS
2514 SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb pi ca cr)
2515 INDEX (t,JTF_TASKS_B_U3)
2516 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
2517 a.task_id || '-' || a.task_assignment_id
2518 , scheduled_start_date
2519 , scheduled_end_date
2520 , 0 color
2521 , NVL(sched_travel_duration, 0)
2522 , t.task_type_id
2523 , task_priority_id
2524 , a.assignment_status_id
2525 , '0' tooltip
2526 , a.resource_id || '-' || a.resource_type_code resource_key
2527 , nvl(pi.party_name,' ') incident_customer_name
2528 , planned_start_date
2529 , planned_end_date
2530 , a.actual_start_date
2531 , a.actual_end_date
2532 , NVL(a.actual_effort, t.actual_effort)
2533 , NVL(a.actual_effort_uom, t.actual_effort_uom)
2534 , t.planned_effort
2535 , t.planned_effort_uom
2536 , 'N' escalated_task
2537 , NVL(accesshour_required, 'N')
2538 , NVL(after_hours_flag, 'N')
2539 , NVL(task_confirmation_status, 'N')
2540 , 'N' task_dep
2541 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
2542 , NVL(child_position, 'N') child_task
2543 , a.actual_travel_duration
2544 , a.actual_travel_duration_uom
2545 FROM (SELECT TO_NUMBER(SUBSTR(column_value
2546 , 1
2547 , INSTR(column_value, '-', 1, 1) - 1
2548 )
2549 )resource_id
2550 ,SUBSTR(column_value
2551 , INSTR(column_value, '-', 1, 1) + 1
2552 , LENGTH(column_value)
2553 ) resource_type
2554 FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
2555 ) res_info
2556 , jtf_task_assignments a
2557 , jtf_tasks_b t
2558 , jtf_task_types_b tt
2559 , jtf_task_statuses_b tsb
2560 , jtf_task_statuses_b tsa
2561 , hz_parties pi
2562 , csf_access_hours_b ca
2563 , csp_requirement_headers cr
2564 WHERE t.task_id = a.task_id
2565 AND t.source_object_type_code in( 'SR','TASK')
2566 AND NVL(t.deleted_flag, 'N') <> 'Y'
2567 AND t.task_type_id NOT IN (20,21)
2568 AND t.task_type_id = tt.task_type_id
2572 --AND scheduled_end_date is not null
2569 AND booking_start_date >= (p_start_date_range -1)
2570 AND booking_end_date <= p_end_date_range
2571 --AND scheduled_start_date is not null -- commented for the bug 6729435
2573 AND a.resource_id = res_info.resource_id
2574 AND a.resource_type_code = res_info.resource_type
2575 AND tsa.task_status_id = t.task_status_id
2576 AND tsb.task_status_id = a.assignment_status_id
2577 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
2578 AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
2579 AND ca.task_id(+) = t.task_id
2580 AND pi.party_id(+) = t.customer_id
2581 AND cr.task_id(+) = t.task_id
2582 AND booking_end_date >= booking_start_date;
2583
2584 ---------------------------------------------------------------------------------------------
2585 --Cursor C3 introduced if task_dependency is set to active in scheduling chart icon setup form
2586 -- this cursor also has hz_parties join for party name to be shown on task bar
2587 ---------------------------------------------------------------------------------------------
2588 CURSOR c3 IS
2589 SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb pi ca jd jdd cr)
2590 INDEX (t,JTF_TASKS_B_U3)
2591 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
2592 DISTINCT
2593 a.task_id || '-' || a.task_assignment_id
2594 , scheduled_start_date
2595 , scheduled_end_date
2596 , 0 color
2597 , NVL(sched_travel_duration, 0)
2598 , t.task_type_id
2599 , task_priority_id
2600 , a.assignment_status_id
2601 , '0' tooltip
2602 , a.resource_id || '-' || a.resource_type_code resource_key
2603 , nvl(pi.party_name,' ') incident_customer_name
2604 , planned_start_date
2605 , planned_end_date
2606 , a.actual_start_date
2607 , a.actual_end_date
2608 , NVL(a.actual_effort, t.actual_effort)
2609 , NVL(a.actual_effort_uom, t.actual_effort_uom)
2610 , t.planned_effort
2611 , t.planned_effort_uom
2612 , 'N' escalated_task
2613 , NVL(accesshour_required, 'N')
2614 , NVL(after_hours_flag, 'N')
2615 , NVL(task_confirmation_status, 'N')
2616 , DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
2617 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
2618 , NVL(child_position, 'N') child_task
2619 , a.actual_travel_duration
2620 , a.actual_travel_duration_uom
2621 , nvl(jdd.dependent_on_task_id,0) task_dep1
2622 FROM (SELECT TO_NUMBER(SUBSTR(column_value
2623 , 1
2624 , INSTR(column_value, '-', 1, 1) - 1
2625 )
2626 )resource_id
2627 ,SUBSTR(column_value
2628 , INSTR(column_value, '-', 1, 1) + 1
2629 , LENGTH(column_value)
2630 ) resource_type
2631 FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
2632 ) res_info
2633 , jtf_task_assignments a
2634 , jtf_tasks_b t
2635 , jtf_task_types_b tt
2636 , jtf_task_statuses_b tsb
2637 , jtf_task_statuses_b tsa
2638 , hz_parties pi
2639 , csf_access_hours_b ca
2640 , jtf_task_depends jd
2641 , jtf_task_depends jdd
2642 , csp_requirement_headers cr
2643 WHERE t.task_id = a.task_id
2644 AND t.source_object_type_code in( 'SR','TASK')
2645 AND NVL(t.deleted_flag, 'N') <> 'Y'
2646 AND t.task_type_id NOT IN (20,21)
2647 AND t.task_type_id = tt.task_type_id
2648 AND booking_start_date >= (p_start_date_range -1)
2649 AND booking_end_date <= p_end_date_range
2650 --AND scheduled_start_date is not null --commented for the bug 6729435
2651 --AND scheduled_end_date is not null
2652 AND a.resource_id = res_info.resource_id
2653 AND a.resource_type_code = res_info.resource_type
2654 AND tsa.task_status_id = t.task_status_id
2655 AND tsb.task_status_id = a.assignment_status_id
2656 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
2657 AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
2658 AND ca.task_id(+) = t.task_id
2659 AND jd.task_id(+) = t.task_id
2660 AND jdd.dependent_on_task_id(+) = t.task_id
2661 AND pi.party_id(+) = t.customer_id
2662 AND cr.task_id(+) = t.task_id
2663 AND booking_end_date >= booking_start_date;
2664 ---------------------------------------------------------------------------------------------
2665 --Cursor C2 introduced when show labels on taskbar is false i.e remove join for hz_parties
2666 -- the diffrence between c1 and c2 join for hz_parties
2667 ---------------------------------------------------------------------------------------------
2668 CURSOR c2 IS
2669 SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb ca cr)
2670 INDEX (t,JTF_TASKS_B_U3)
2671 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
2672 a.task_id || '-' || a.task_assignment_id
2673 , scheduled_start_date
2674 , scheduled_end_date
2675 , 0 color
2676 , NVL(sched_travel_duration, 0)
2677 , t.task_type_id
2681 , a.resource_id || '-' || a.resource_type_code resource_key
2678 , task_priority_id
2679 , a.assignment_status_id
2680 , '0' tooltip
2682 , ' ' incident_customer_name
2683 , planned_start_date
2684 , planned_end_date
2685 , a.actual_start_date
2686 , a.actual_end_date
2687 , NVL(a.actual_effort, t.actual_effort)
2688 , NVL(a.actual_effort_uom, t.actual_effort_uom)
2689 , t.planned_effort
2690 , t.planned_effort_uom
2691 , 'N' escalated_task
2692 , NVL(accesshour_required, 'N')
2693 , NVL(after_hours_flag, 'N')
2694 , NVL(task_confirmation_status, 'N')
2695 , 'N' task_dep
2696 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
2697 , NVL(child_position, 'N') child_task
2698 , a.actual_travel_duration
2699 , a.actual_travel_duration_uom
2700 FROM (SELECT TO_NUMBER(SUBSTR(column_value
2701 , 1
2702 , INSTR(column_value, '-', 1, 1) - 1
2703 )
2704 )resource_id
2705 ,SUBSTR(column_value
2706 , INSTR(column_value, '-', 1, 1) + 1
2707 , LENGTH(column_value)
2708 ) resource_type
2709 FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
2710 ) res_info
2711 , jtf_task_assignments a
2712 , jtf_tasks_b t
2713 , jtf_task_types_b tt
2714 , jtf_task_statuses_b tsb
2715 , jtf_task_statuses_b tsa
2716 , csf_access_hours_b ca
2717 , csp_requirement_headers cr
2718 WHERE t.task_id = a.task_id
2719 AND t.source_object_type_code in( 'SR','TASK')
2720 AND NVL(t.deleted_flag, 'N') <> 'Y'
2721 AND t.task_type_id NOT IN (20,21)
2722 AND t.task_type_id = tt.task_type_id
2723 AND booking_start_date >= (p_start_date_range -1)
2724 AND booking_end_date <= p_end_date_range
2725 --AND scheduled_start_date is not null --commented for the bug 6729435
2726 --AND scheduled_end_date is not null
2727 AND a.resource_id = res_info.resource_id
2728 AND a.resource_type_code = res_info.resource_type
2729 AND tsa.task_status_id = t.task_status_id
2730 AND tsb.task_status_id = a.assignment_status_id
2731 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
2732 AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
2733 AND ca.task_id(+) = t.task_id
2734 AND cr.task_id(+) = t.task_id
2735 AND booking_end_date >= booking_start_date;
2736 ---------------------------------------------------------------------------------------------
2737 --Cursor C4 introduced if task_dependency is set to active in scheduling chart icon setup form
2738 -- this cursor does not have hz_parties join like c2 but has dependencies join
2739 ---------------------------------------------------------------------------------------------
2740 CURSOR C4
2741 IS
2742 SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb ca jd jdd cr)
2743 INDEX (t,JTF_TASKS_B_U3)
2744 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
2745 DISTINCT
2746 a.task_id || '-' || a.task_assignment_id
2747 , scheduled_start_date
2748 , scheduled_end_date
2749 , 0 color
2750 , NVL(sched_travel_duration, 0)
2751 , t.task_type_id
2752 , task_priority_id
2753 , a.assignment_status_id
2754 , '0' tooltip
2755 , a.resource_id || '-' || a.resource_type_code resource_key
2756 , ' ' incident_customer_name
2757 , planned_start_date
2758 , planned_end_date
2759 , a.actual_start_date
2760 , a.actual_end_date
2761 , NVL(a.actual_effort, t.actual_effort)
2762 , NVL(a.actual_effort_uom, t.actual_effort_uom)
2763 , t.planned_effort
2764 , t.planned_effort_uom
2765 , 'N' escalated_task
2766 , NVL(accesshour_required, 'N')
2767 , NVL(after_hours_flag, 'N')
2768 , NVL(task_confirmation_status, 'N')
2769 , DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
2770 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
2771 , NVL(child_position, 'N') child_task
2772 , a.actual_travel_duration
2773 , a.actual_travel_duration_uom
2774 , nvl(jdd.dependent_on_task_id,0) task_dep1
2775 FROM (SELECT TO_NUMBER(SUBSTR(column_value
2776 , 1
2777 , INSTR(column_value, '-', 1, 1) - 1
2778 )
2779 )resource_id
2780 ,SUBSTR(column_value
2781 , INSTR(column_value, '-', 1, 1) + 1
2782 , LENGTH(column_value)
2783 ) resource_type
2784 FROM TABLE(CAST(p_res_key AS jtf_varchar2_table_2000))
2785 ) res_info
2786 , jtf_task_assignments a
2787 , jtf_tasks_b t
2788 , jtf_task_types_b tt
2792 , jtf_task_depends jd
2789 , jtf_task_statuses_b tsb
2790 , jtf_task_statuses_b tsa
2791 , csf_access_hours_b ca
2793 , jtf_task_depends jdd
2794 , csp_requirement_headers cr
2795 WHERE t.task_id = a.task_id
2796 AND t.source_object_type_code in( 'SR','TASK')
2797 AND NVL(t.deleted_flag, 'N') <> 'Y'
2798 AND t.task_type_id NOT IN (20,21)
2799 AND t.task_type_id = tt.task_type_id
2800 AND booking_start_date >= (p_start_date_range -1)
2801 AND booking_end_date <= p_end_date_range
2802 -- AND scheduled_start_date is not null --commented for the bug 6729435
2803 --AND scheduled_end_date is not null
2804 AND a.resource_id = res_info.resource_id
2805 AND a.resource_type_code = res_info.resource_type
2806 AND tsa.task_status_id = t.task_status_id
2807 AND tsb.task_status_id = a.assignment_status_id
2808 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
2809 AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
2810 AND ca.task_id(+) = t.task_id
2811 AND jd.task_id(+) = t.task_id
2812 AND jdd.dependent_on_task_id(+) = t.task_id
2813 AND cr.task_id(+) = t.task_id
2814 AND booking_end_date >= booking_start_date;
2815
2816 TYPE custom_color_rec IS RECORD(
2817 task_type_id NUMBER
2818 , task_priority_id NUMBER
2819 , assignment_status_id NUMBER
2820 , escalated_task VARCHAR2(1)
2821 , background_col_dec NUMBER
2822 , background_col_rgb VARCHAR2(12)
2823 );
2824
2825 TYPE custom_color_tbl IS TABLE OF custom_color_rec
2826 INDEX BY BINARY_INTEGER;
2827
2828 g_custom_color_tbl custom_color_tbl;
2829
2830 PROCEDURE get_custom_color IS
2831 CURSOR c_custom_color IS
2832 SELECT type_id
2833 , priority_id
2834 , assignment_status_id
2835 , escalated_task
2836 , background_col_dec
2837 , background_col_rgb
2838 FROM jtf_task_custom_colors
2839 WHERE active_flag = 'Y'
2840 ORDER BY color_determination_priority;
2841
2842 i BINARY_INTEGER := 0;
2843 BEGIN
2844 IF g_custom_color_tbl.COUNT = 0
2845 THEN
2846 FOR rec IN c_custom_color
2847 LOOP
2848 i := i + 1;
2849 g_custom_color_tbl(i).task_type_id := rec.type_id;
2850 g_custom_color_tbl(i).task_priority_id := rec.priority_id;
2851 g_custom_color_tbl(i).assignment_status_id := rec.assignment_status_id;
2852 g_custom_color_tbl(i).escalated_task := rec.escalated_task;
2853 g_custom_color_tbl(i).background_col_dec := rec.background_col_dec;
2854 g_custom_color_tbl(i).background_col_rgb := rec.background_col_rgb;
2855 END LOOP;
2856 END IF;
2857 END get_custom_color;
2858
2859 FUNCTION do_match(
2860 p_task_type_id IN NUMBER
2861 , p_task_priority_id IN NUMBER
2862 , p_assignment_status_id IN NUMBER
2863 , p_escalated_task IN VARCHAR2
2864 )
2865 RETURN NUMBER IS
2866 BEGIN
2867 FOR i IN g_custom_color_tbl.FIRST .. g_custom_color_tbl.LAST
2868 LOOP
2869 IF NVL(g_custom_color_tbl(i).task_type_id, p_task_type_id) = p_task_type_id
2870 AND NVL(g_custom_color_tbl(i).task_priority_id, p_task_priority_id) = p_task_priority_id
2871 AND NVL(g_custom_color_tbl(i).assignment_status_id, p_assignment_status_id) =
2872 p_assignment_status_id
2873 AND NVL(g_custom_color_tbl(i).escalated_task, p_escalated_task) = p_escalated_task THEN
2874 RETURN g_custom_color_tbl(i).background_col_dec;
2875 END IF;
2876 END LOOP;
2877 RETURN 0;
2878 END do_match;
2879 BEGIN
2880 p_res_id :=jtf_number_table();
2881 p_res_type :=jtf_varchar2_table_2000();
2882 p_res_name :=jtf_varchar2_table_2000();
2883 p_res_typ_name :=jtf_varchar2_table_2000();
2884 p_res_key :=jtf_varchar2_table_2000();
2885 --------------------------------------
2886 --Tables for getting shift information
2887 --------------------------------------
2888 p_trip_id :=jtf_number_table();
2889 p_shift_start_date :=jtf_date_table();
2890 p_shift_end_date :=jtf_date_table();
2891 p_block_trip :=jtf_number_table();
2892 p_shift_res_key :=jtf_varchar2_table_2000();
2893 ----------------------------------------------------------
2894 --tables used for getting virtual task information
2895 ----------------------------------------------------------
2896 p_vir_task_id := jtf_varchar2_table_100() ;
2897 p_vir_start_date := jtf_date_table();
2898 p_vir_end_date := jtf_date_table();
2899 p_vir_color := jtf_number_table();
2900 p_vir_name := jtf_varchar2_table_100();
2901 p_vir_duration := jtf_number_table();
2902 p_vir_task_type_id := jtf_number_table();
2903 p_vir_tooltip := jtf_varchar2_table_2000();
2904 p_vir_resource_key := jtf_varchar2_table_2000();
2905
2906 l_assignment_id := jtf_number_table();
2910 l_planned_end_date := jtf_date_table();
2907 l_task_priority_id := jtf_number_table();
2908 l_status_id := jtf_number_table();
2909 l_planned_start_date := jtf_date_table();
2911 l_actual_start_date := jtf_date_table();
2912 l_actual_end_date := jtf_date_table();
2913 l_actual_effort := jtf_number_table();
2914 l_actual_effort_uom := jtf_varchar2_table_100();
2915 l_planned_effort := jtf_number_table();
2916 l_planned_effort_uom := jtf_varchar2_table_100();
2917 l_escalated_task := jtf_varchar2_table_100();
2918 l_scheduled_start_date := jtf_date_table();
2919 l_scheduled_end_date := jtf_date_table();
2920 -------------------------------------------------------
2921 --tables used for getting real task information
2922 -------------------------------------------------------
2923 real_task_id := jtf_varchar2_table_100();
2924 real_start_date := jtf_date_table();
2925 real_end_date := jtf_date_table();
2926 real_color := jtf_number_table();
2927 real_name := jtf_varchar2_table_2000();
2928 real_tooltip := jtf_varchar2_table_2000();
2929 real_duration := jtf_number_table();
2930 real_task_type_id := jtf_number_table();
2931 real_resource_key := jtf_varchar2_table_2000();
2932 real_parts_required := jtf_varchar2_table_100();
2933 real_access_hours := jtf_varchar2_table_100();
2934 real_after_hours := jtf_varchar2_table_100();
2935 real_customer_conf := jtf_varchar2_table_100();
2936 real_task_depend := jtf_varchar2_table_100();
2937 real_child_task := jtf_varchar2_table_100();
2938
2939 l_actual_travel_duration := jtf_number_table();
2940 l_actual_travel_duration_uom := jtf_varchar2_table_100();
2941 l_task_depend :=jtf_varchar2_table_100();
2942 --------------------------------------------------------------------------
2943 -- This was added because profile values were getting cached
2944 -- though we change profile values in application and re-login
2945 -- we were not able to see the changed effects.so decided to
2946 -- move these profiles intialisation in this procedure
2947 -- as this procedure gets executed first for populating dispatch
2948 -- cente gantt.
2949 ---------------------------------------------------------------------------
2950 l_default_effort_uom := fnd_profile.value_specific('CSF_DEFAULT_EFFORT_UOM', g_user_id);
2951 l_default_effort := fnd_profile.value_specific('CSF_DEFAULT_EFFORT', g_user_id);
2952 l_rule_id := fnd_profile.value_specific('CSF_TASK_SIGNAL_COLOR', g_user_id);
2953 l_profile_value := fnd_profile.value_specific('CSF_USE_CUSTOM_CHROMATICS', g_user_id);
2954 g_label_on_task := fnd_profile.value_specific('CSF_DISPLAY_LABEL_ON_TASK', g_user_id) = 'Y';
2955 g_dflt_tz_for_dc := fnd_profile.value_specific('CSF_DEFAULT_TIMEZONE_DC', g_user_id);
2956 g_dflt_tz_for_sc := fnd_profile.value_specific('CSF_DEFAULT_TIMEZONE_SC', g_user_id);
2957 -------------------------------------------------------------------------------
2958 -- End for adding profile values
2959 -------------------------------------------------------------------------------
2960 OPEN C_Terr_Resource;
2961 FETCH C_Terr_Resource
2962 BULK COLLECT INTO p_res_id,p_res_type,p_res_name,p_res_typ_name,p_res_key ;
2963
2964
2965 OPEN C_Resource_Shift(p_start_date_range,p_end_date_range);
2966 FETCH C_Resource_Shift
2967 BULK COLLECT INTO p_trip_id,p_shift_start_date,p_shift_end_date,p_block_trip,p_shift_res_key ;
2968
2969 if g_tz_enabled ='Y' and g_dflt_tz_for_dc='CTZ' and p_trip_id.count > 0
2970 then
2971 FOR i IN p_trip_id.FIRST .. p_trip_id.LAST
2972 LOOP
2973 p_shift_start_date(i) :=fnd_date.adjust_datetime(p_shift_start_date(i),g_client_tz,g_server_tz );
2974 p_shift_end_date(i) :=fnd_date.adjust_datetime(p_shift_end_date(i) ,g_client_tz,g_server_tz);
2975 END LOOP;
2976 end if;
2977
2978 IF g_use_custom_chromatics
2979 THEN
2980 get_custom_color;
2981 END IF;
2982
2983 IF p_show_arr_dep_tasks = 'Y'
2984 THEN /* Added this if condition for bug 6676658 */
2985
2986 OPEN C_Virtual_Tasks;
2987 FETCH C_Virtual_Tasks
2988 BULK COLLECT INTO
2989 p_vir_task_id
2990 , p_vir_start_date
2991 , p_vir_end_date
2992 , p_vir_color
2993 , p_vir_name
2994 , p_vir_duration
2995 , p_vir_task_type_id
2996 , p_vir_tooltip
2997 , p_vir_resource_key
2998 , l_assignment_id
2999 , l_task_priority_id
3000 , l_status_id
3001 , l_planned_start_date
3002 , l_planned_end_date
3003 , l_actual_start_date
3004 , l_actual_end_date
3005 , l_actual_effort
3006 , l_actual_effort_uom
3007 , l_planned_effort
3008 , l_planned_effort_uom
3009 , l_escalated_task
3010 , l_scheduled_start_date
3011 , l_scheduled_end_date;
3012
3013 IF p_vir_task_id.COUNT IS NOT NULL AND p_vir_task_id.COUNT > 0
3014 THEN
3015 FOR i IN p_vir_task_id.FIRST .. p_vir_task_id.LAST
3016 LOOP
3017 ------------------------------------------
3018 --for scheduled start dates
3019 ------------------------------------------
3023 THEN
3020 IF l_scheduled_start_date(i) IS NOT NULL AND l_scheduled_end_date(i) IS NOT NULL
3021 THEN
3022 IF l_scheduled_end_date(i) = l_scheduled_start_date(i)
3024 IF NVL(l_planned_effort(i), 0) = 0 AND p_vir_task_type_id(i) = 20
3025 THEN
3026 p_vir_start_date(i) :=(l_scheduled_start_date(i) - 5 / 1440);
3027 END IF; --planned effort
3028 ELSE
3029 IF l_scheduled_end_date(i) > l_scheduled_start_date(i) AND NVL(l_planned_effort(i), 0) = 0
3030 AND p_vir_task_type_id(i) = 20
3031 THEN
3032 p_vir_start_date(i) :=(l_scheduled_start_date(i) - 5 / 1440);
3033 END IF; --if scheduled_end_date > scheduled_start_date
3034 END IF; --if scheduled end_date = scheduled_start_date
3035 END IF;
3036 --end if for r.scheduled_start_date is not null and r.scheduled_end_date is not null
3037 ------------------------------------------
3038 --end for scheduled start dates
3039 ------------------------------------------
3040 ------------------------------------------
3041 --for scheduled end dates dates
3042 ------------------------------------------
3043 IF l_scheduled_start_date(i) IS NOT NULL AND l_scheduled_end_date(i) IS NOT NULL
3044 THEN
3045 IF l_scheduled_start_date(i) = l_scheduled_end_date(i)
3046 THEN
3047 IF NVL(l_planned_effort(i), 0) = 0 AND p_vir_task_type_id(i) = 21
3048 THEN
3049 p_vir_end_date(i) :=(l_scheduled_start_date(i) + 5 / 1440);
3050 ELSE
3051 IF l_planned_effort(i) IS NOT NULL AND l_planned_effort(i) > 0
3052 THEN
3053 l_planned_effort(i):=convert_to_days(l_planned_effort(i),NVL(l_planned_effort_uom(i),g_uom_hours),g_uom_hours);
3054 END IF;
3055 p_vir_end_date(i):=(l_scheduled_start_date(i) + NVL(l_planned_effort(i), 0));
3056 END IF; --planned effort
3057 ELSE
3058 IF l_scheduled_end_date(i) > l_scheduled_start_date(i)
3059 THEN
3060 IF NVL(l_planned_effort(i), 0) = 0 AND p_vir_task_type_id(i) = 21
3061 THEN
3062 p_vir_end_date(i) :=(l_scheduled_start_date(i) + 5 / 1440);
3063 ELSE
3064 IF l_planned_effort(i) IS NOT NULL AND l_planned_effort(i) > 0
3065 THEN
3066 l_planned_effort(i):=convert_to_days(l_planned_effort(i),NVL(l_planned_effort_uom(i),g_uom_hours),g_uom_hours);
3067 END IF;
3068 p_vir_end_date(i) :=(l_scheduled_start_date(i) + NVL(l_planned_effort(i), 0));
3069 END IF; --planned effort
3070 END IF; --if scheduled_end_date > scheduled_start_date
3071 END IF; --if scheduled end_date = scheduled_start_date
3072 END IF; --end if for r.scheduled_start_date is not null and r.scheduled_end_date is not null
3073 ------------------------------------------
3074 --end for scheduled end dates
3075 ------------------------------------------
3076 IF g_use_custom_chromatics
3077 THEN
3078 l_task_custom_color := 'N';
3079 IF l_rule_id IS NOT NULL
3080 THEN
3081 IF l_actual_start_date(i) IS NOT NULL
3082 THEN
3083 IF l_actual_end_date(i) IS NOT NULL
3084 THEN
3085 IF l_actual_end_date(i) = l_actual_start_date(i)
3086 THEN
3087 l_task_custom_color := 'Y';
3088 END IF; --end if for actual_end_date=actual_start_date
3089 ELSE
3090 IF NVL(l_actual_effort(i), 0) = 0
3091 THEN
3092 IF NVL(l_planned_effort(i), 0) = 0
3093 THEN
3094 l_task_custom_color := 'Y';
3095 END IF;
3096 END IF;
3097 END IF; --end if for actual_end_date is not null
3098 ELSE --for actual start date is null
3099 IF p_vir_end_date(i) IS NOT NULL
3100 THEN
3101 IF p_vir_end_date(i) = p_vir_start_date(i)
3102 THEN
3103 l_task_custom_color := 'Y';
3104 END IF;
3105 ELSE
3106 l_task_custom_color := 'Y';
3107 END IF; --end if scheduled end_date is not null
3108 END IF; --end if for actual_start_date is not null
3109 END IF; --rule id condition for task date usage
3110 IF l_task_custom_color = 'Y'
3111 THEN
3112 IF l_rule_id IS NOT NULL
3113 THEN
3114 IF NVL(p_rule_id, 1) <> l_rule_id
3115 THEN
3116 OPEN get_tdu_color(l_rule_id);
3117 FETCH get_tdu_color
3118 INTO p_color;
3119 IF get_tdu_color%NOTFOUND
3120 THEN
3121 CLOSE get_tdu_color;
3122 IF(NVL(p_cur_task_type_id, -1) <> p_vir_task_type_id(i)
3123 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
3124 OR NVL(p_cur_task_status_id, -1) <> l_status_id(i)
3125 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
3126 )
3127 THEN
3128 p_vir_color(i):=do_match(p_vir_task_type_id(i),l_task_priority_id(i),l_status_id(i),l_escalated_task(i));
3129 p_cur_color := p_vir_color(i);
3130 p_cur_task_type_id := p_vir_task_type_id(i);
3134 ELSE
3131 p_cur_task_priority_id := l_task_priority_id(i);
3132 p_cur_task_status_id := l_status_id(i);
3133 p_cur_escalated_task := l_escalated_task(i);
3135 p_vir_color(i) := p_cur_color;
3136 END IF;
3137 ELSE
3138 p_vir_color(i) := p_color;
3139 CLOSE get_tdu_color;
3140 END IF;
3141 ELSE
3142 p_vir_color(i) := p_color;
3143 END IF;
3144 ELSE
3145 IF(NVL(p_cur_task_type_id, -1) <> p_vir_task_type_id(i)
3146 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
3147 OR NVL(p_cur_task_status_id, -1) <> l_status_id(i)
3148 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
3149 )
3150 THEN
3151 p_vir_color(i):=do_match(p_vir_task_type_id(i), l_task_priority_id(i), l_status_id(i),l_escalated_task(i));
3152 p_cur_color := p_vir_color(i);
3153 p_cur_task_type_id := p_vir_task_type_id(i);
3154 p_cur_task_priority_id := l_task_priority_id(i);
3155 p_cur_task_status_id := l_status_id(i);
3156 p_cur_escalated_task := l_escalated_task(i);
3157 ELSE
3158 p_vir_color(i) := p_cur_color;
3159 END IF;
3160 END IF;
3161 ELSE
3162 IF (NVL(p_cur_task_type_id, -1) <> p_vir_task_type_id(i)
3163 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
3164 OR NVL(p_cur_task_status_id, -1) <> l_status_id(i)
3165 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
3166 )
3167 THEN
3168 p_vir_color(i):=do_match(p_vir_task_type_id(i), l_task_priority_id(i), l_status_id(i), l_escalated_task(i));
3169 p_cur_color := p_vir_color(i);
3170 p_cur_task_type_id := p_vir_task_type_id(i);
3171 p_cur_task_priority_id := l_task_priority_id(i);
3172 p_cur_task_status_id := l_status_id(i);
3173 p_cur_escalated_task := l_escalated_task(i);
3174 ELSE
3175 p_vir_color(i) := p_cur_color;
3176 END IF;
3177 END IF;
3178 ELSE
3179 IF l_actual_start_date(i) IS NOT NULL
3180 THEN
3181 p_vir_color(i) := yellow;
3182 ELSE
3183 p_vir_color(i) := blue;
3184 END IF;
3185 END IF;
3186
3187 IF g_tz_enabled ='Y' and g_dflt_tz_for_dc='CTZ'
3188 THEN
3189 p_vir_start_date(i) :=fnd_date.adjust_datetime(p_vir_start_date(i),g_client_tz,g_server_tz );
3190 p_vir_end_date(i) :=fnd_date.adjust_datetime(p_vir_end_date(i) ,g_client_tz,g_server_tz);
3191 END IF;
3192 END LOOP;
3193 END IF;
3194 END IF; -- for p_show_arr_dep_tasks = 'Y'
3195
3196 l_task_depends :='N';
3197 FOR i IN c_icon_setup
3198 LOOP
3199 l_task_depends :='Y';
3200 END LOOP;
3201
3202 IF l_task_depends ='Y'
3203 THEN
3204 IF g_label_on_task
3205 THEN
3206 OPEN c3;
3207 FETCH c3
3208 BULK COLLECT INTO real_task_id
3209 , real_start_date
3210 , real_end_date
3211 , real_color
3212 , real_duration
3213 , real_task_type_id
3214 , l_task_priority_id
3215 , l_status_id
3216 , real_tooltip
3217 , real_resource_key
3218 , real_name
3219 , l_planned_start_date
3220 , l_planned_end_date
3221 , l_actual_start_date
3222 , l_actual_end_date
3223 , l_actual_effort
3224 , l_actual_effort_uom
3225 , l_planned_effort
3226 , l_planned_effort_uom
3227 , l_escalated_task
3228 , real_access_hours
3229 , real_after_hours
3230 , real_customer_conf
3231 , real_task_depend
3232 , real_parts_required
3233 , real_child_task
3234 , l_actual_travel_duration
3235 , l_actual_travel_duration_uom
3236 , l_task_depend
3237 ;
3238 CLOSE c3;
3239 ELSE
3240 OPEN c4;
3241 FETCH c4
3242 BULK COLLECT INTO real_task_id
3243 , real_start_date
3244 , real_end_date
3245 , real_color
3246 , real_duration
3247 , real_task_type_id
3248 , l_task_priority_id
3249 , l_status_id
3250 , real_tooltip
3251 , real_resource_key
3252 , real_name
3253 , l_planned_start_date
3254 , l_planned_end_date
3255 , l_actual_start_date
3256 , l_actual_end_date
3257 , l_actual_effort
3258 , l_actual_effort_uom
3259 , l_planned_effort
3260 , l_planned_effort_uom
3261 , l_escalated_task
3262 , real_access_hours
3263 , real_after_hours
3264 , real_customer_conf
3265 , real_task_depend
3266 , real_parts_required
3267 , real_child_task
3268 , l_actual_travel_duration
3269 , l_actual_travel_duration_uom
3270 , l_task_depend
3271 ;
3272 CLOSE c4;
3273 END IF;
3277 THEN
3274 ELSIF nvl(l_task_depends,'N') ='N'
3275 THEN
3276 IF g_label_on_task
3278 OPEN c1;
3279 FETCH c1
3280 BULK COLLECT INTO real_task_id
3281 , real_start_date
3282 , real_end_date
3283 , real_color
3284 , real_duration
3285 , real_task_type_id
3286 , l_task_priority_id
3287 , l_status_id
3288 , real_tooltip
3289 , real_resource_key
3290 , real_name
3291 , l_planned_start_date
3292 , l_planned_end_date
3293 , l_actual_start_date
3294 , l_actual_end_date
3295 , l_actual_effort
3296 , l_actual_effort_uom
3297 , l_planned_effort
3298 , l_planned_effort_uom
3299 , l_escalated_task
3300 , real_access_hours
3301 , real_after_hours
3302 , real_customer_conf
3303 , real_task_depend
3304 , real_parts_required
3305 , real_child_task
3306 , l_actual_travel_duration
3307 , l_actual_travel_duration_uom
3308 ;
3309 CLOSE c1;
3310 ELSE
3311 OPEN c2;
3312 FETCH c2
3313 BULK COLLECT INTO real_task_id
3314 , real_start_date
3315 , real_end_date
3316 , real_color
3317 , real_duration
3318 , real_task_type_id
3319 , l_task_priority_id
3320 , l_status_id
3321 , real_tooltip
3322 , real_resource_key
3323 , real_name
3324 , l_planned_start_date
3325 , l_planned_end_date
3326 , l_actual_start_date
3327 , l_actual_end_date
3328 , l_actual_effort
3329 , l_actual_effort_uom
3330 , l_planned_effort
3331 , l_planned_effort_uom
3332 , l_escalated_task
3333 , real_access_hours
3334 , real_after_hours
3335 , real_customer_conf
3336 , real_task_depend
3337 , real_parts_required
3338 , real_child_task
3339 , l_actual_travel_duration
3340 , l_actual_travel_duration_uom
3341 ;
3342 CLOSE c2;
3343 END IF;
3344 END IF;
3345
3346 IF real_task_id.COUNT IS NOT NULL AND real_task_id.COUNT > 0
3347 THEN
3348 FOR i IN real_task_id.FIRST .. real_task_id.LAST
3349 LOOP
3350 IF nvl(l_task_depends,'N') ='Y'
3351 THEN
3352 IF to_number(substr(real_task_id(i),1,instr(real_task_id(i),'-')-1)) = l_task_depend(i)
3353 THEN
3354 real_task_depend(i) := 'Y';
3355 END IF;
3356 END IF;
3357 ------------------------------------------
3358 --for scheduled start dates
3359 ------------------------------------------
3360 IF l_actual_start_date(i) IS NOT NULL
3361 THEN
3362 real_start_date(i) := l_actual_start_date(i);
3363 END IF;
3364 ------------------------------------------
3365 --end for scheduled start dates
3366 ------------------------------------------
3367 ------------------------------------------
3368 --for scheduled end dates dates
3369 ------------------------------------------
3370 IF l_actual_start_date(i) IS NOT NULL
3371 THEN
3372 IF l_actual_end_date(i) IS NULL
3373 THEN
3374 IF l_actual_effort(i) IS NULL OR l_actual_effort(i) = 0
3375 THEN
3376 IF l_planned_effort(i) IS NOT NULL AND l_planned_effort(i) > 0
3377 THEN
3378 l_planned_effort(i):=csf_gantt_data_pkg.convert_to_days(l_planned_effort(i),NVL(l_planned_effort_uom(i),g_uom_hours),g_uom_hours);
3379 END IF;
3380 IF l_default_effort IS NOT NULL AND l_default_effort > 0
3381 THEN
3382 l_csf_default_effort:=csf_gantt_data_pkg.convert_to_days(l_default_effort,NVL(l_default_effort_uom,g_uom_hours),g_uom_hours);
3383 END IF;
3384 real_end_date(i):=(l_actual_start_date(i) + NVL(l_planned_effort(i), NVL(l_csf_default_effort, 0)));
3385 ELSE
3386 l_actual_effort(i):=csf_gantt_data_pkg.convert_to_days(l_actual_effort(i),NVL(l_actual_effort_uom(i),g_uom_hours),g_uom_hours);
3387 real_end_date(i):=(l_actual_start_date(i) + NVL(l_actual_effort(i), 0));
3388 END IF;
3389 ELSE
3390 IF l_actual_end_date(i) <= l_actual_start_date(i)
3391 THEN
3392 IF l_actual_effort(i) IS NULL OR l_actual_effort(i) = 0
3393 THEN -- this is true then calculate the actual end_date based uppon the profile values.
3394 IF l_planned_effort(i) IS NOT NULL AND l_planned_effort(i) > 0
3395 THEN
3396 l_planned_effort(i):=csf_gantt_data_pkg.convert_to_days(l_planned_effort(i),NVL(l_planned_effort_uom(i),g_uom_hours),g_uom_hours);
3397 END IF;
3398 IF l_default_effort IS NOT NULL AND l_default_effort > 0
3399 THEN
3400 l_csf_default_effort:=csf_gantt_data_pkg.convert_to_days(l_default_effort,NVL(l_default_effort_uom,g_uom_hours),g_uom_hours);
3401 END IF;
3402 real_end_date(i):=(l_actual_start_date(i) + NVL(l_planned_effort(i), NVL(l_csf_default_effort, 0)));
3406 END IF; --end if for actual effort is nul or zero
3403 ELSE -- if not null then actual effort to the actual_end_date
3404 l_actual_effort(i):=csf_gantt_data_pkg.convert_to_days(l_actual_effort(i),NVL(l_actual_effort_uom(i),g_uom_hours),g_uom_hours);
3405 real_end_date(i):=(l_actual_start_date(i) + NVL(l_actual_effort(i), 0));
3407 ELSE -- actual end date is not null, check if actual_end_date > actual_start_date
3408 IF l_actual_end_date(i) > l_actual_start_date(i)
3409 THEN
3410 real_end_date(i):= l_actual_end_date(i);
3411 END IF;-- end if
3412 END IF; --end if for r_sch_end_date.actual_end_date = r_sch_end_date.actual_start_date
3413 END IF; -- end if for actual_end_date is null
3414 IF l_actual_travel_duration(i) > 0
3415 THEN
3416 IF l_actual_travel_duration_uom(i) IS NOT NULL
3417 THEN
3418 real_duration(i):=convert_to_min(l_actual_travel_duration(i),l_actual_travel_duration_uom(i),g_uom_minutes);
3419 END IF;
3420 ELSE
3421 real_duration(i):=0;
3422 END IF;
3423 ELSE --else for actual_start_date is null
3424 IF real_start_date(i) IS NOT NULL
3425 THEN -- scheduled_start_date is not null then check if scheduled_end_date is null
3426 IF real_end_date(i) IS NOT NULL
3427 THEN -- scheduled_start_date is not null then check if scheduled_start_date=scheduled_end_date
3428 IF real_start_date(i) = real_end_date(i)
3429 THEN
3430 IF (l_planned_effort(i) IS NULL) OR(l_planned_effort(i) = 0)
3431 THEN
3432 IF l_default_effort IS NOT NULL AND l_default_effort > 0
3433 THEN
3434 l_csf_default_effort:=csf_gantt_data_pkg.convert_to_days(l_default_effort,NVL(l_default_effort_uom, g_uom_hours),g_uom_hours);
3435 END IF;
3436 real_end_date(i):=(real_start_date(i) + NVL(l_csf_default_effort, 0));
3437 ELSE
3438 IF l_planned_effort(i) IS NOT NULL AND l_planned_effort(i) > 0
3439 THEN
3440 l_planned_effort(i):=csf_gantt_data_pkg.convert_to_days(l_planned_effort(i),NVL(l_planned_effort_uom(i),g_uom_hours),g_uom_hours);
3441 END IF;
3442 real_end_date(i):=(real_start_date(i) + NVL(l_planned_effort(i), 0));
3443 END IF;
3444 END IF;
3445 ELSE -- scheduled_end_date is null then check for planned effort null
3446 IF (l_planned_effort(i) IS NULL) OR(l_planned_effort(i) = 0)
3447 THEN
3448 IF l_default_effort IS NOT NULL AND l_default_effort > 0
3449 THEN
3450 l_csf_default_effort:=csf_gantt_data_pkg.convert_to_days(l_default_effort,NVL(l_default_effort_uom,g_uom_hours),g_uom_hours);
3451 END IF;
3452 real_end_date(i):=(real_start_date(i) + NVL(l_csf_default_effort, 0));
3453 ELSE -- declar variable l_planned_effort to get uom coverted into days for actual effort for
3454 IF l_planned_effort(i) IS NOT NULL AND l_planned_effort(i) > 0
3455 THEN
3456 l_planned_effort(i):=csf_gantt_data_pkg.convert_to_days(l_planned_effort(i),NVL(l_planned_effort_uom(i),g_uom_hours),g_uom_hours);
3457 END IF;
3458 real_end_date(i) :=(real_start_date(i) + NVL(l_planned_effort(i), 0));
3459 END IF;
3460 END IF; -- end if for scheduled_end_date is null
3461 END IF; -- end if for scheduled_start_date is not null
3462 END IF; -- end if for actual_start_date is not null
3463 ------------------------------------------
3464 --end for scheduled end dates
3465 ------------------------------------------
3466 IF is_task_escalated(to_number(SUBSTR(real_task_id(i),1,instr(real_task_id(i),'-')-1))) --added for the bug 7307125
3467 THEN
3468 l_escalated_task(i) := 'Y';
3469 ELSE
3470 l_escalated_task(i) := 'N';
3471 END IF;
3472 IF g_use_custom_chromatics
3473 THEN
3474 l_task_custom_color := 'N';
3475 IF l_rule_id IS NOT NULL
3476 THEN
3477 IF l_actual_start_date(i) IS NOT NULL
3478 THEN
3479 IF l_actual_end_date(i) IS NOT NULL
3480 THEN
3481 IF l_actual_end_date(i) = l_actual_start_date(i)
3482 THEN
3483 l_task_custom_color := 'Y';
3484 END IF; --end if for actual_end_date=actual_start_date
3485 ELSE
3486 IF NVL(l_actual_effort(i), 0) = 0
3487 THEN
3488 IF NVL(l_planned_effort(i), 0) = 0
3489 THEN
3490 l_task_custom_color := 'Y';
3491 END IF;
3492 END IF;
3493 END IF; --end if for actual_end_date is not null
3494 ELSE --for actual start date is null
3495 IF real_end_date(i) IS NOT NULL
3496 THEN
3497 IF real_end_date(i) = real_start_date(i)
3498 THEN
3499 l_task_custom_color := 'Y';
3500 END IF;
3501 ELSE
3502 l_task_custom_color := 'Y';
3503 END IF; --end if scheduled end_date is not null
3504 END IF; --end if for actual_start_date is not null
3505 END IF; --rule id condition for task date usage
3506 IF l_task_custom_color = 'Y'
3507 THEN
3508 IF l_rule_id IS NOT NULL
3509 THEN
3513 FETCH get_tdu_color
3510 IF NVL(p_rule_id, 1) <> l_rule_id
3511 THEN
3512 OPEN get_tdu_color(l_rule_id);
3514 INTO p_color;
3515 IF get_tdu_color%NOTFOUND
3516 THEN
3517 CLOSE get_tdu_color;
3518 IF(NVL(p_cur_task_type_id, -1) <> real_task_type_id(i)
3519 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
3520 OR NVL(p_cur_task_status_id, -1) <> l_status_id(i)
3521 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
3522 )
3523 THEN
3524 real_color(i):=do_match(real_task_type_id(i),l_task_priority_id(i),l_status_id(i),l_escalated_task(i));
3525 p_cur_color := real_color(i);
3526 p_cur_task_type_id := real_task_type_id(i);
3527 p_cur_task_priority_id := l_task_priority_id(i);
3528 p_cur_task_status_id := l_status_id(i);
3529 p_cur_escalated_task := l_escalated_task(i);
3530 ELSE
3531 real_color(i) := p_cur_color;
3532 END IF;
3533 ELSE
3534 real_color(i) := p_color;
3535 CLOSE get_tdu_color;
3536 END IF;
3537 ELSE
3538 real_color(i) := p_color;
3539 END IF;
3540 ELSE
3541 IF(NVL(p_cur_task_type_id, -1) <> real_task_type_id(i)
3542 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
3543 OR NVL(p_cur_task_status_id, -1) <> l_status_id(i)
3544 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
3545 )
3546 THEN
3547 real_color(i):=do_match(real_task_type_id(i), l_task_priority_id(i), l_status_id(i),l_escalated_task(i));
3548 p_cur_color := real_color(i);
3549 p_cur_task_type_id := real_task_type_id(i);
3550 p_cur_task_priority_id := l_task_priority_id(i);
3551 p_cur_task_status_id := l_status_id(i);
3552 p_cur_escalated_task := l_escalated_task(i);
3553 ELSE
3554 real_color(i) := p_cur_color;
3555 END IF;
3556 END IF;
3557 ELSE
3558 IF (NVL(p_cur_task_type_id, -1) <> real_task_type_id(i)
3559 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
3560 OR NVL(p_cur_task_status_id, -1) <> l_status_id(i)
3561 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
3562 )
3563 THEN
3564 real_color(i):=do_match(real_task_type_id(i), l_task_priority_id(i), l_status_id(i), l_escalated_task(i));
3565 p_cur_color := real_color(i);
3566 p_cur_task_type_id := real_task_type_id(i);
3567 p_cur_task_priority_id := l_task_priority_id(i);
3568 p_cur_task_status_id := l_status_id(i);
3569 p_cur_escalated_task := l_escalated_task(i);
3570 ELSE
3571 real_color(i) := p_cur_color;
3572 END IF;
3573 END IF;
3574 ELSE
3575 IF l_actual_start_date(i) IS NOT NULL --added for the bug 7307125
3576 THEN
3577 real_color(i) := yellow;
3578 ELSE
3579 real_color(i) := blue;
3580 END IF;
3581 END IF;
3582 IF l_escalated_task(i) = 'Y'
3583 THEN
3584 real_color(i) := red;
3585 END IF;
3586 IF g_tz_enabled ='Y' and g_dflt_tz_for_dc='CTZ'
3587 THEN
3588 real_start_date(i) :=fnd_date.adjust_datetime(real_start_date(i),g_client_tz,g_server_tz );
3589 real_end_date(i) :=fnd_date.adjust_datetime(real_end_date(i) ,g_client_tz,g_server_tz);
3590 END IF;
3591 END LOOP;
3592 END IF;
3593 END;
3594
3595 PROCEDURE get_schedule_advise_options
3596 (
3597 p_api_version IN NUMBER
3598 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
3599 , x_return_status OUT NOCOPY VARCHAR2
3600 , x_msg_count OUT NOCOPY NUMBER
3601 , x_msg_data OUT NOCOPY VARCHAR2
3602 , p_display_option IN VARCHAR2
3603 , p_resource_id IN NUMBER
3604 , p_resource_type IN VARCHAR2
3605 , p_req_id IN NUMBER
3606 , p_par_task IN NUMBER
3607 , p_task_id IN NUMBER
3608 , p_res_id OUT NOCOPY jtf_number_table
3609 , p_res_type OUT NOCOPY jtf_varchar2_table_2000
3610 , p_res_name OUT NOCOPY jtf_varchar2_table_2000
3611 , p_res_typ_name OUT NOCOPY jtf_varchar2_table_2000
3612 , p_res_key OUT NOCOPY jtf_varchar2_table_2000
3613 , p_cost OUT NOCOPY jtf_number_table
3614 , p_start_date IN DATE
3615 , p_end_date IN DATE
3616 , sch_adv_tz IN Varchar2
3617 , inc_tz_code IN Varchar2
3618 , trip_id OUT NOCOPY jtf_number_table
3619 , start_date OUT NOCOPY jtf_date_table
3620 , end_date OUT NOCOPY jtf_date_table
3621 , block_trip OUT NOCOPY jtf_number_table
3622 , p_bck_res_key OUT NOCOPY jtf_varchar2_table_2000
3623 , plan_task_key OUT NOCOPY jtf_varchar2_table_100
3627 , plan_name OUT NOCOPY jtf_varchar2_table_2000
3624 , plan_start_date OUT NOCOPY jtf_date_table
3625 , plan_end_date OUT NOCOPY jtf_date_table
3626 , plan_color OUT NOCOPY jtf_number_table
3628 , plan_tooltip OUT NOCOPY jtf_varchar2_table_2000
3629 , plan_duration OUT NOCOPY jtf_number_table
3630 , plan_task_type_id OUT NOCOPY jtf_number_table
3631 , plan_resource_key OUT NOCOPY jtf_varchar2_table_2000
3632 , real_task_key OUT NOCOPY jtf_varchar2_table_100
3633 , real_start_date OUT NOCOPY jtf_date_table
3634 , real_end_date OUT NOCOPY jtf_date_table
3635 , real_color OUT NOCOPY jtf_number_table
3636 , real_name OUT NOCOPY jtf_varchar2_table_2000
3637 , real_tooltip OUT NOCOPY jtf_varchar2_table_2000
3638 , real_duration OUT NOCOPY jtf_number_table
3639 , real_task_type_id OUT NOCOPY jtf_number_table
3640 , real_resource_key OUT NOCOPY jtf_varchar2_table_2000
3641 , child_task OUT Nocopy jtf_varchar2_table_100
3642 , real_parts_required OUT NOCOPY jtf_varchar2_table_100
3643 , real_access_hours OUT NOCOPY jtf_varchar2_table_100
3644 , real_after_hours OUT NOCOPY jtf_varchar2_table_100
3645 , real_customer_conf OUT NOCOPY jtf_varchar2_table_100
3646 , real_task_depend OUT NOCOPY jtf_varchar2_table_100
3647 , oth_real_task_id OUT Nocopy jtf_varchar2_table_100
3648 , oth_real_start_date OUT Nocopy jtf_date_table
3649 , oth_real_end_date OUT Nocopy jtf_date_table
3650 , oth_real_color OUT Nocopy jtf_number_table
3651 , oth_real_Name OUT Nocopy jtf_varchar2_table_2000
3652 , oth_real_Duration OUT Nocopy jtf_number_table
3653 , oth_real_task_type_id OUT Nocopy jtf_number_table
3654 , oth_real_resource_key OUT Nocopy jtf_varchar2_table_2000
3655 , oth_real_child_task OUT Nocopy jtf_varchar2_table_100
3656 , oth_real_parts_required OUT NOCOPY jtf_varchar2_table_100
3657 , oth_real_access_hours OUT NOCOPY jtf_varchar2_table_100
3658 , oth_real_after_hours OUT NOCOPY jtf_varchar2_table_100
3659 , oth_real_customer_conf OUT NOCOPY jtf_varchar2_table_100
3660 , oth_real_task_depend OUT NOCOPY jtf_varchar2_table_100
3661 )
3662 IS
3663 L_RESOURCE_QUERY VARCHAR2(4000);
3664 L_COMMON_WHERE_RESOURCE_QUERY VARCHAR2(4000);
3665 L_COMMON_WHERE_PARENT VARCHAR2(4000);
3666 L_COMMON_ORDERBY VARCHAR2(4000);
3667 L_RESOURCE_QUERY_COST VARCHAR2(4000);
3668 L_COMMON_WHERE_RESOURCE_COST VARCHAR2(4000);
3669 L_RESOURCE_QUERY_COST_DAY VARCHAR2(4000);
3670 L_COMMON_WHERE_RESOURCE_SINGLE VARCHAR2(4000);
3671 L_RESOURCE_SINGLE_QUERY VARCHAR2(4000);
3672 L_QUERY VARCHAR2(8000);
3673 ---------------------------------------------------
3674 --The below variables are used in color coding proc
3675 ---------------------------------------------------
3676 p_cur_task_type_id NUMBER(10);
3677 p_cur_task_priority_id NUMBER(10);
3678 p_cur_task_status_id NUMBER(10);
3679 p_cur_escalated_task VARCHAR2(1);
3680 p_color NUMBER(30);
3681 p_cur_color NUMBER(30);
3682 p_rule_id NUMBER(10);
3683
3684 l_return_status VARCHAR2(1);
3685
3686 TYPE SchResType IS REF CURSOR;
3687 ResInfo SchResType;
3688
3689 tmp_trip_id jtf_number_table;
3690 l_task_priority_id jtf_number_table;
3691 l_assignment_status_id jtf_number_table;
3692 l_escalated_task jtf_varchar2_table_100;
3693
3694 l_planned_start_date jtf_date_table;
3695 l_planned_end_date jtf_date_table;
3696 l_actual_start_date jtf_date_table;
3697 l_actual_end_date jtf_date_table;
3698 l_actual_effort jtf_number_table;
3699 l_actual_effort_uom jtf_varchar2_table_100;
3700 l_planned_effort jtf_number_table;
3701 l_planned_effort_uom jtf_varchar2_table_100;
3702 l_actual_travel_duration jtf_number_table;
3703 l_actual_travel_duration_uom jtf_varchar2_table_100;
3704 l_status_id jtf_number_table;
3705 l_csf_default_effort NUMBER;
3706 l_task_depends varchar2(1);
3707 l_task_depend jtf_varchar2_table_100;
3708 oth_real_tooltip jtf_varchar2_table_2000;
3709
3710 CURSOR c_icon_setup
3711 IS
3712 SELECT active
3713 FROM csf_gnticons_setup_v
3714 WHERE seq_id = 6;
3715
3716 CURSOR get_tdu_color(t_rule_id NUMBER) IS
3717 SELECT background_col_dec
3718 FROM jtf_task_custom_colors
3719 WHERE rule_id = t_rule_id;
3720
3721
3722 CURSOR c_res_detail IS
3723 SELECT DISTINCT cs.object_capacity_id cs
3724 , ca.object_capacity_id
3725 , ca.start_date_time
3726 , ca.end_date_time
3727 , status blocked_trip
3728 , resource_id
3729 FROM cac_sr_object_capacity ca
3733 , resource_id res_id
3730 , (SELECT resource_id || '-' || resource_type || '-' || plan_option_id
3731 resource_id
3732 , object_capacity_id
3734 , resource_type res_typ
3735 FROM csf_plan_options_v
3736 WHERE sched_request_id = p_req_id
3737 AND task_type_id IN(20, 21)
3738 ) cs
3739 WHERE ca.object_id = cs.res_id
3740 AND ca.object_type = cs.res_typ
3741 AND ca.start_date_time >= p_start_date -1
3742 AND ca.end_date_time <= p_end_date;
3743
3744 ---------------------------------------------------------------------------------------------
3745 --Cursor c_real_task_1 introduced when show labels on taskbar is true i.e join for hz_parties for showing
3746 --party name on taskbar and this cursor is without task dependencies join.
3747 ---------------------------------------------------------------------------------------------
3748 CURSOR c_real_task_1 IS
3749 SELECT
3750 cpv.task_id || '-' || cpv.plan_option_id real_task_key
3751 , cpv.resource_id || '-' || cpv.resource_type || '-' || cpv.plan_option_id real_resource_key
3752 , cpv.start_time
3753 , cpv.end_time
3754 , 0 color
3755 , ' ' tooltip
3756 , NVL(
3757 TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
3758 + TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5))
3759 , 0
3760 ) travel_time
3761 , t.task_type_id
3762 , task_priority_id
3763 , a.assignment_status_id
3764 , a.actual_start_date
3765 , a.actual_end_date
3766 , nvl(pi.party_name,' ') incident_customer_name
3767 , NVL(
3768 DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
3769 , 'N'
3770 ) escalated_task
3771 , NVL(accesshour_required, 'N')
3772 , NVL(after_hours_flag, 'N')
3773 , NVL(task_confirmation_status, 'N')
3774 , 'N' task_dep
3775 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
3776 , NVL(child_position, 'N') child_task
3777 FROM csf_plan_options_v cpv
3778 , jtf_task_assignments a
3779 , jtf_tasks_b t
3780 , hz_parties pi
3781 , csf_access_hours_b ca
3782 , csp_requirement_headers cr
3783 , jtf_task_statuses_b tsa
3784 , jtf_task_statuses_b tsb
3785 WHERE cpv.sched_request_id = p_req_id
3786 AND NVL(cpv.task_type_id, 0) NOT IN(20, 21)
3787 AND cpv.start_time >= p_start_date
3788 AND cpv.end_time <= p_end_date
3789 AND cpv.task_id = t.task_id
3790 AND cpv.task_id = a.task_id
3791 AND ca.task_id(+) = t.task_id
3792 AND pi.party_id(+) = t.customer_id
3793 AND cr.task_id(+) = t.task_id
3794 AND (cpv.task_id <> -1 AND cpv.task_id <> p_task_id)
3795 AND tsb.task_status_id = t.task_status_id
3796 AND tsa.task_status_id = a.assignment_status_id
3797 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
3798 AND NVL(tsb.cancelled_flag, 'N') <> 'Y';
3799
3800 ---------------------------------------------------------------------------------------------
3801 --Cursor c_real_task_3 introduced if task_dependency is set to active in scheduling chart icon setup form
3802 -- this cursor also has hz_parties join for party name to be shown on task bar
3803 ---------------------------------------------------------------------------------------------
3804 CURSOR c_real_task_3 IS
3805 SELECT
3806 cpv.task_id || '-' || cpv.plan_option_id real_task_key
3807 , cpv.resource_id || '-' || cpv.resource_type || '-' || cpv.plan_option_id real_resource_key
3808 , cpv.start_time
3809 , cpv.end_time
3810 , 0 color
3811 , ' ' tooltip
3812 , NVL(
3813 TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
3814 + TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5))
3815 , 0
3816 ) travel_time
3817 , t.task_type_id
3818 , task_priority_id
3819 , a.assignment_status_id
3820 , a.actual_start_date
3821 , a.actual_end_date
3822 , nvl(pi.party_name,' ') incident_customer_name
3823 , NVL(
3824 DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
3825 , 'N'
3826 ) escalated_task
3827 , NVL(accesshour_required, 'N')
3828 , NVL(after_hours_flag, 'N')
3829 , NVL(task_confirmation_status, 'N')
3830 , DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
3831 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
3832 , NVL(child_position, 'N') child_task
3833 , nvl(jdd.dependent_on_task_id,0) || '-' || plan_option_id task_dep1
3834 FROM csf_plan_options_v cpv
3835 , jtf_task_assignments a
3836 , jtf_tasks_b t
3837 , hz_parties pi
3838 , csf_access_hours_b ca
3842 , jtf_task_statuses_b tsa
3839 , csp_requirement_headers cr
3840 , jtf_task_depends jd
3841 , jtf_task_depends jdd
3843 , jtf_task_statuses_b tsb
3844 WHERE cpv.sched_request_id = p_req_id
3845 AND NVL(cpv.task_type_id, 0) NOT IN(20, 21)
3846 AND cpv.start_time >= p_start_date
3847 AND cpv.end_time <= p_end_date
3848 AND cpv.task_id = t.task_id
3849 AND cpv.task_id = a.task_id
3850 AND ca.task_id(+) = t.task_id
3851 AND pi.party_id(+) = t.customer_id
3852 AND cr.task_id(+) = t.task_id
3853 AND (cpv.task_id <> -1 AND cpv.task_id <> p_task_id)
3854 AND jd.task_id(+) = t.task_id
3855 AND jdd.dependent_on_task_id(+) = t.task_id
3856 AND tsb.task_status_id = t.task_status_id
3857 AND tsa.task_status_id = a.assignment_status_id
3858 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
3859 AND NVL(tsb.cancelled_flag, 'N') <> 'Y';
3860
3861 ---------------------------------------------------------------------------------------------
3862 --Cursor c_real_task_2 introduced when show labels on taskbar is false i.e remove join for hz_parties
3863 -- the diffrence between c1 and c2 join for hz_parties
3864 ---------------------------------------------------------------------------------------------
3865 CURSOR c_real_task_2 IS
3866 SELECT
3867 cpv.task_id || '-' || cpv.plan_option_id real_task_key
3868 , cpv.resource_id || '-' || cpv.resource_type || '-' || cpv.plan_option_id real_resource_key
3869 , cpv.start_time
3870 , cpv.end_time
3871 , 0 color
3872 , ' ' tooltip
3873 , NVL(
3874 TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
3875 + TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5))
3876 , 0
3877 ) travel_time
3878 , t.task_type_id
3879 , task_priority_id
3880 , a.assignment_status_id
3881 , a.actual_start_date
3882 , a.actual_end_date
3883 , ' ' incident_customer_name
3884 , NVL(
3885 DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
3886 , 'N'
3887 ) escalated_task
3888 , NVL(accesshour_required, 'N')
3889 , NVL(after_hours_flag, 'N')
3890 , NVL(task_confirmation_status, 'N')
3891 , 'N' task_dep
3892 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
3893 , NVL(child_position, 'N') child_task
3894 FROM csf_plan_options_v cpv
3895 , jtf_task_assignments a
3896 , jtf_tasks_b t
3897 , csf_access_hours_b ca
3898 , csp_requirement_headers cr
3899 , jtf_task_statuses_b tsa
3900 , jtf_task_statuses_b tsb
3901 WHERE cpv.sched_request_id = p_req_id
3902 AND NVL(cpv.task_type_id, 0) NOT IN(20, 21)
3903 AND cpv.start_time >= p_start_date
3904 AND cpv.end_time <= p_end_date
3905 AND cpv.task_id = t.task_id
3906 AND cpv.task_id = a.task_id
3907 AND ca.task_id(+) = t.task_id
3908 AND cr.task_id(+) = t.task_id
3909 AND (cpv.task_id <> -1 AND cpv.task_id <> p_task_id)
3910 AND tsb.task_status_id = t.task_status_id
3911 AND tsa.task_status_id = a.assignment_status_id
3912 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
3913 AND NVL(tsb.cancelled_flag, 'N') <> 'Y';
3914
3915 ---------------------------------------------------------------------------------------------
3916 --Cursor c_real_task_4 introduced if task_dependency is set to active in scheduling chart icon setup form
3917 -- this cursor does not have hz_parties join like c2 but has dependencies join
3918 ---------------------------------------------------------------------------------------------
3919 CURSOR c_real_task_4 IS
3920 SELECT
3921 cpv.task_id || '-' || cpv.plan_option_id real_task_key
3922 , cpv.resource_id || '-' || cpv.resource_type || '-' || cpv.plan_option_id real_resource_key
3923 , cpv.start_time
3924 , cpv.end_time
3925 , 0 color
3926 , ' ' tooltip
3927 , NVL(
3928 TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
3929 + TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5))
3930 , 0
3931 ) travel_time
3932 , t.task_type_id
3933 , task_priority_id
3934 , a.assignment_status_id
3935 , a.actual_start_date
3936 , a.actual_end_date
3937 , ' ' incident_customer_name
3938 , NVL(
3939 DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
3940 , 'N'
3941 ) escalated_task
3942 , NVL(accesshour_required, 'N')
3943 , NVL(after_hours_flag, 'N')
3944 , NVL(task_confirmation_status, 'N')
3945 , DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
3946 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
3947 , NVL(child_position, 'N') child_task
3948 , NVL(jdd.dependent_on_task_id,0) || '-' || plan_option_id task_dep1
3949 FROM csf_plan_options_v cpv
3950 , jtf_task_assignments a
3951 , jtf_tasks_b t
3955 , jtf_task_depends jdd
3952 , csf_access_hours_b ca
3953 , csp_requirement_headers cr
3954 , jtf_task_depends jd
3956 , jtf_task_statuses_b tsa
3957 , jtf_task_statuses_b tsb
3958 WHERE cpv.sched_request_id = p_req_id
3959 AND NVL(cpv.task_type_id, 0) NOT IN(20, 21)
3960 AND cpv.start_time >= p_start_date
3961 AND cpv.end_time <= p_end_date
3962 AND cpv.task_id = t.task_id
3963 AND cpv.task_id = a.task_id
3964 AND ca.task_id(+) = t.task_id
3965 AND cr.task_id(+) = t.task_id
3966 AND (cpv.task_id <> -1 AND cpv.task_id <> p_task_id)
3967 AND jd.task_id(+) = t.task_id
3968 AND jdd.dependent_on_task_id(+) = t.task_id
3969 AND tsb.task_status_id = t.task_status_id
3970 AND tsa.task_status_id = a.assignment_status_id
3971 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
3972 AND NVL(tsb.cancelled_flag, 'N') <> 'Y';
3973
3974 CURSOR c_planned_task IS
3975 SELECT DECODE(task_id, -1, ROWNUM, task_id) || plan_option_id plan_task_key
3976 , resource_id || '-' || resource_type || '-' || plan_option_id plan_resource_key
3977 , start_time
3978 , end_time
3979 , 65280 color
3980 , ' ' NAME
3981 , ' ' tooltip
3982 , NVL(
3983 TO_NUMBER(SUBSTR(travel_time, 1, INSTR(travel_time, ':', 1) - 1)) * 60
3984 + TO_NUMBER(SUBSTR(travel_time, INSTR(travel_time, ':', 1) + 1, 5))
3985 , 0
3986 ) travel_time
3987 , NVL(task_type_id, 0)
3988 FROM csf_plan_options_v
3989 WHERE sched_request_id = p_req_id
3990 AND NVL(task_type_id, 0) NOT IN(20, 21)
3991 AND start_time >= p_start_date
3992 AND end_time <= p_end_date
3993 AND (task_id = -1 OR task_id = p_task_id);
3994
3995 ---------------------------------------------------------------------------------------------
3996 --Cursor C1 introduced when show labels on taskbar is true i.e join for hz_parties for showing
3997 --party name on taskbar and this cursor is without task dependenciea join.
3998 ---------------------------------------------------------------------------------------------
3999
4000 CURSOR c1
4001 IS
4002 SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb pi ca cr)
4003 INDEX (t,JTF_TASKS_B_U3)
4004 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
4005 a.task_id || '-'|| plan_option_id
4006 , scheduled_start_date
4007 , scheduled_end_date
4008 , 0 color
4009 , NVL(sched_travel_duration, 0)
4010 , t.task_type_id
4011 , task_priority_id
4012 , a.assignment_status_id
4013 , '0' tooltip
4014 , a.resource_id || '-' || a.resource_type_code || '-'|| plan_option_id resource_key
4015 , nvl(pi.party_name,' ') incident_customer_name
4016 , planned_start_date
4017 , planned_end_date
4018 , a.actual_start_date
4019 , a.actual_end_date
4020 , NVL(a.actual_effort, t.actual_effort)
4021 , NVL(a.actual_effort_uom, t.actual_effort_uom)
4022 , t.planned_effort
4023 , t.planned_effort_uom
4024 , NVL(
4025 DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
4026 , 'N'
4027 ) escalated_task
4028 , NVL(accesshour_required, 'N')
4029 , NVL(after_hours_flag, 'N')
4030 , NVL(task_confirmation_status, 'N')
4031 , 'N' task_dep
4032 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
4033 , NVL(child_position, 'N') child_task
4034 , a.actual_travel_duration
4035 , a.actual_travel_duration_uom
4036 FROM (SELECT distinct rr.resource_id, rr.resource_type,pop.plan_option_id,pt.object_capacity_id
4037 FROM csf_r_request_tasks rt,
4038 csf_r_resource_results rr,
4039 csf_r_plan_options pop,
4040 csf_r_plan_option_tasks pt,
4041 jtf_tasks_b t
4042 WHERE rt.request_task_id = rr.request_task_id
4043 AND rr.resource_result_id = POP.resource_result_id
4044 AND POP.plan_option_id = pt.plan_option_id
4045 AND pt.task_id = t.task_id(+)
4046 AND rt.sched_request_id = p_req_id
4047 AND nvl(t.task_type_id, 0) not in(20, 21)) res_info
4048 , jtf_task_assignments a
4049 , jtf_tasks_b t
4050 , jtf_task_types_b tt
4051 , jtf_task_statuses_b tsb
4052 , jtf_task_statuses_b tsa
4053 , hz_parties pi
4054 , csf_access_hours_b ca
4055 , csp_requirement_headers cr
4056 WHERE t.task_id = a.task_id
4057 AND t.source_object_type_code in( 'SR','TASK')
4058 AND NVL(t.deleted_flag, 'N') <> 'Y'
4059 AND t.task_type_id NOT IN (20,21)
4060 AND t.task_type_id = tt.task_type_id
4061 AND booking_start_date >= (p_start_date -1)
4062 AND booking_end_date <= p_end_date
4063 --AND scheduled_start_date is not null --commented for the bug 6729435
4064 --AND scheduled_end_date is not null
4065 AND a.resource_id = res_info.resource_id
4066 AND a.resource_type_code = res_info.resource_type
4067 AND tsa.task_status_id = t.task_status_id
4068 AND tsb.task_status_id = a.assignment_status_id
4072 AND pi.party_id(+) = t.customer_id
4069 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
4070 AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
4071 AND ca.task_id(+) = t.task_id
4073 AND cr.task_id(+) = t.task_id
4074 AND booking_end_date >= booking_start_date
4075 AND a.object_capacity_id <>res_info.object_capacity_id;
4076
4077 ---------------------------------------------------------------------------------------------
4078 --Cursor C3 introduced if task_dependency is set to active in scheduling chart icon setup form
4079 -- this cursor also has hz_parties join for party name to be shown on task bar
4080 ---------------------------------------------------------------------------------------------
4081 CURSOR c3 IS
4082 SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb pi ca jd jdd cr)
4083 INDEX (t,JTF_TASKS_B_U3)
4084 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
4085 DISTINCT
4086 a.task_id || '-'|| plan_option_id
4087 , scheduled_start_date
4088 , scheduled_end_date
4089 , 0 color
4090 , NVL(sched_travel_duration, 0)
4091 , t.task_type_id
4092 , task_priority_id
4093 , a.assignment_status_id
4094 , '0' tooltip
4095 , a.resource_id || '-' || a.resource_type_code || '-'|| plan_option_id resource_key
4096 , nvl(pi.party_name,' ') incident_customer_name
4097 , planned_start_date
4098 , planned_end_date
4099 , a.actual_start_date
4100 , a.actual_end_date
4101 , NVL(a.actual_effort, t.actual_effort)
4102 , NVL(a.actual_effort_uom, t.actual_effort_uom)
4103 , t.planned_effort
4104 , t.planned_effort_uom
4105 , NVL(
4106 DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
4107 , 'N'
4108 ) escalated_task
4109 , NVL(accesshour_required, 'N')
4110 , NVL(after_hours_flag, 'N')
4111 , NVL(task_confirmation_status, 'N')
4112 , DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
4113 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
4114 , NVL(child_position, 'N') child_task
4115 , a.actual_travel_duration
4116 , a.actual_travel_duration_uom
4117 , nvl(jdd.dependent_on_task_id,0) || '-' || plan_option_id task_dep1
4118 FROM (SELECT distinct rr.resource_id, rr.resource_type,pop.plan_option_id,pt.object_capacity_id
4119 FROM csf_r_request_tasks rt,
4120 csf_r_resource_results rr,
4121 csf_r_plan_options pop,
4122 csf_r_plan_option_tasks pt,
4123 jtf_tasks_b t
4124 WHERE rt.request_task_id = rr.request_task_id
4125 AND rr.resource_result_id = POP.resource_result_id
4126 AND POP.plan_option_id = pt.plan_option_id
4127 AND pt.task_id = t.task_id(+)
4128 AND rt.sched_request_id = p_req_id
4129 AND nvl(t.task_type_id, 0) not in(20, 21)) res_info
4130 , jtf_task_assignments a
4131 , jtf_tasks_b t
4132 , jtf_task_types_b tt
4133 , jtf_task_statuses_b tsb
4134 , jtf_task_statuses_b tsa
4135 , hz_parties pi
4136 , csf_access_hours_b ca
4137 , jtf_task_depends jd
4138 , jtf_task_depends jdd
4139 , csp_requirement_headers cr
4140 WHERE t.task_id = a.task_id
4141 AND t.source_object_type_code in( 'SR','TASK')
4142 AND NVL(t.deleted_flag, 'N') <> 'Y'
4143 AND t.task_type_id NOT IN (20,21)
4144 AND t.task_type_id = tt.task_type_id
4145 AND booking_start_date >= (p_start_date -1)
4146 AND booking_end_date <= p_end_date
4147 --AND scheduled_start_date is not null --commented for the bug 6729435
4148 --AND scheduled_end_date is not null
4149 AND a.resource_id = res_info.resource_id
4150 AND a.resource_type_code = res_info.resource_type
4151 AND tsa.task_status_id = t.task_status_id
4152 AND tsb.task_status_id = a.assignment_status_id
4153 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
4154 AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
4155 AND ca.task_id(+) = t.task_id
4156 AND jd.task_id(+) = t.task_id
4157 AND jdd.dependent_on_task_id(+) = t.task_id
4158 AND pi.party_id(+) = t.customer_id
4159 AND cr.task_id(+) = t.task_id
4160 AND booking_end_date >= booking_start_date
4161 AND a.object_capacity_id <>res_info.object_capacity_id;
4162 ---------------------------------------------------------------------------------------------
4163 --Cursor C2 introduced when show labels on taskbar is false i.e remove join for hz_parties
4164 -- the diffrence between c1 and c2 join for hz_parties
4165 ---------------------------------------------------------------------------------------------
4166 CURSOR c2 IS
4167 SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb ca cr)
4168 INDEX (t,JTF_TASKS_B_U3)
4169 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
4170 a.task_id || '-'|| plan_option_id
4171 , scheduled_start_date
4172 , scheduled_end_date
4173 , 0 color
4174 , NVL(sched_travel_duration, 0)
4175 , t.task_type_id
4176 , task_priority_id
4180 , ' ' incident_customer_name
4177 , a.assignment_status_id
4178 , '0' tooltip
4179 , a.resource_id || '-' || a.resource_type_code || '-'|| plan_option_id resource_key
4181 , planned_start_date
4182 , planned_end_date
4183 , a.actual_start_date
4184 , a.actual_end_date
4185 , NVL(a.actual_effort, t.actual_effort)
4186 , NVL(a.actual_effort_uom, t.actual_effort_uom)
4187 , t.planned_effort
4188 , t.planned_effort_uom
4189 , NVL(
4190 DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
4191 , 'N'
4192 ) escalated_task
4193 , NVL(accesshour_required, 'N')
4194 , NVL(after_hours_flag, 'N')
4195 , NVL(task_confirmation_status, 'N')
4196 , 'N' task_dep
4197 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
4198 , NVL(child_position, 'N') child_task
4199 , a.actual_travel_duration
4200 , a.actual_travel_duration_uom
4201 FROM (SELECT distinct rr.resource_id, rr.resource_type,pop.plan_option_id,pt.object_capacity_id
4202 FROM csf_r_request_tasks rt,
4203 csf_r_resource_results rr,
4204 csf_r_plan_options pop,
4205 csf_r_plan_option_tasks pt,
4206 jtf_tasks_b t
4207 WHERE rt.request_task_id = rr.request_task_id
4208 AND rr.resource_result_id = POP.resource_result_id
4209 AND POP.plan_option_id = pt.plan_option_id
4210 AND pt.task_id = t.task_id(+)
4211 AND rt.sched_request_id = p_req_id
4212 AND nvl(t.task_type_id, 0) not in(20, 21)) res_info
4213 , jtf_task_assignments a
4214 , jtf_tasks_b t
4215 , jtf_task_types_b tt
4216 , jtf_task_statuses_b tsb
4217 , jtf_task_statuses_b tsa
4218 , csf_access_hours_b ca
4219 , csp_requirement_headers cr
4220 WHERE t.task_id = a.task_id
4221 AND t.source_object_type_code in( 'SR','TASK')
4222 AND NVL(t.deleted_flag, 'N') <> 'Y'
4223 AND t.task_type_id NOT IN (20,21)
4224 AND t.task_type_id = tt.task_type_id
4225 AND booking_start_date >= (p_start_date -1)
4226 AND booking_end_date <= p_end_date
4227 -- AND scheduled_start_date is not null --commented for the bug 6729435
4228 --AND scheduled_end_date is not null
4229 AND a.resource_id = res_info.resource_id
4230 AND a.resource_type_code = res_info.resource_type
4231 AND tsa.task_status_id = t.task_status_id
4232 AND tsb.task_status_id = a.assignment_status_id
4233 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
4234 AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
4235 AND ca.task_id(+) = t.task_id
4236 AND cr.task_id(+) = t.task_id
4237 AND booking_end_date >= booking_start_date
4238 AND a.object_capacity_id <>res_info.object_capacity_id;
4239 ---------------------------------------------------------------------------------------------
4240 --Cursor C4 introduced if task_dependency is set to active in scheduling chart icon setup form
4241 -- this cursor does not have hz_parties join like c2 but has dependencies join
4242 ---------------------------------------------------------------------------------------------
4243 CURSOR C4
4244 IS
4245 SELECT /*+ ORDERED use_nl (res_info a t tt tsa tsb ca jd jdd cr)
4246 INDEX (t,JTF_TASKS_B_U3)
4247 INDEX (a,JTF_TASK_ASSIGNMENTS_N1) */
4248 DISTINCT
4249 a.task_id || '-'|| plan_option_id
4250 , scheduled_start_date
4251 , scheduled_end_date
4252 , 0 color
4253 , NVL(sched_travel_duration, 0)
4254 , t.task_type_id
4255 , task_priority_id
4256 , a.assignment_status_id
4257 , '0' tooltip
4258 , a.resource_id || '-' || a.resource_type_code || '-'|| plan_option_id resource_key
4259 , ' ' incident_customer_name
4260 , planned_start_date
4261 , planned_end_date
4262 , a.actual_start_date
4263 , a.actual_end_date
4264 , NVL(a.actual_effort, t.actual_effort)
4265 , NVL(a.actual_effort_uom, t.actual_effort_uom)
4266 , t.planned_effort
4267 , t.planned_effort_uom
4268 , NVL(
4269 DECODE(t.task_type_id, 22, DECODE(t.escalation_level, 'DE', 'N', 'NE', 'N', 'Y'))
4270 , 'N'
4271 ) escalated_task
4272 , NVL(accesshour_required, 'N')
4273 , NVL(after_hours_flag, 'N')
4274 , NVL(task_confirmation_status, 'N')
4275 , DECODE(nvl(t.task_id,0),jd.task_id,'Y','N') task_dep
4276 , DECODE(cr.task_id, t.task_id, 'Y', 'N') parts_req
4277 , NVL(child_position, 'N') child_task
4278 , a.actual_travel_duration
4279 , a.actual_travel_duration_uom
4280 , nvl(jdd.dependent_on_task_id,0) || '-' || plan_option_id task_dep1
4281 FROM (SELECT distinct rr.resource_id, rr.resource_type,POP.plan_option_id,pt.object_capacity_id
4282 FROM csf_r_request_tasks rt,
4283 csf_r_resource_results rr,
4284 csf_r_plan_options pop,
4285 csf_r_plan_option_tasks pt,
4289 AND POP.plan_option_id = pt.plan_option_id
4286 jtf_tasks_b t
4287 WHERE rt.request_task_id = rr.request_task_id
4288 AND rr.resource_result_id = POP.resource_result_id
4290 AND pt.task_id = t.task_id(+)
4291 AND rt.sched_request_id = p_req_id
4292 AND nvl(t.task_type_id, 0) not in(20, 21)) res_info
4293 , jtf_task_assignments a
4294 , jtf_tasks_b t
4295 , jtf_task_types_b tt
4296 , jtf_task_statuses_b tsb
4297 , jtf_task_statuses_b tsa
4298 , csf_access_hours_b ca
4299 , jtf_task_depends jd
4300 , jtf_task_depends jdd
4301 , csp_requirement_headers cr
4302 WHERE t.task_id = a.task_id
4303 AND t.source_object_type_code in( 'SR','TASK')
4304 AND NVL(t.deleted_flag, 'N') <> 'Y'
4305 AND t.task_type_id NOT IN (20,21)
4306 AND t.task_type_id = tt.task_type_id
4307 AND booking_start_date >= (p_start_date -1)
4308 AND booking_end_date <= p_end_date
4309 --AND scheduled_start_date is not null --commented for the bug 6729435
4310 --AND scheduled_end_date is not null
4311 AND a.resource_id = res_info.resource_id
4312 AND a.resource_type_code = res_info.resource_type
4313 AND tsa.task_status_id = t.task_status_id
4314 AND tsb.task_status_id = a.assignment_status_id
4315 AND NVL(tsa.cancelled_flag, 'N') <> 'Y'
4316 AND NVL(tsb.cancelled_flag, 'N') <> 'Y'
4317 AND ca.task_id(+) = t.task_id
4318 AND jd.task_id(+) = t.task_id
4319 AND jdd.dependent_on_task_id(+) = t.task_id
4320 AND cr.task_id(+) = t.task_id
4321 AND booking_end_date >= booking_start_date
4322 AND a.object_capacity_id <>res_info.object_capacity_id;
4323
4324
4325 TYPE custom_color_rec IS RECORD(
4326 task_type_id NUMBER
4327 , task_priority_id NUMBER
4328 , assignment_status_id NUMBER
4329 , escalated_task VARCHAR2(1)
4330 , background_col_dec NUMBER
4331 , background_col_rgb VARCHAR2(12)
4332 );
4333
4334 TYPE custom_color_tbl IS TABLE OF custom_color_rec
4335 INDEX BY BINARY_INTEGER;
4336
4337 g_custom_color_tbl custom_color_tbl;
4338
4339 PROCEDURE get_custom_color IS
4340 CURSOR c_custom_color IS
4341 SELECT type_id
4342 , priority_id
4343 , assignment_status_id
4344 , escalated_task
4345 , background_col_dec
4346 , background_col_rgb
4347 FROM jtf_task_custom_colors
4348 WHERE active_flag = 'Y'
4349 ORDER BY color_determination_priority;
4350
4351 i BINARY_INTEGER := 0;
4352 BEGIN
4353 IF g_custom_color_tbl.COUNT = 0
4354 THEN
4355 FOR rec IN c_custom_color
4356 LOOP
4357 i := i + 1;
4358 g_custom_color_tbl(i).task_type_id := rec.type_id;
4359 g_custom_color_tbl(i).task_priority_id := rec.priority_id;
4360 g_custom_color_tbl(i).assignment_status_id := rec.assignment_status_id;
4361 g_custom_color_tbl(i).escalated_task := rec.escalated_task;
4362 g_custom_color_tbl(i).background_col_dec := rec.background_col_dec;
4363 g_custom_color_tbl(i).background_col_rgb := rec.background_col_rgb;
4364 END LOOP;
4365 END IF;
4366 END get_custom_color;
4367
4368 FUNCTION do_match(
4369 p_task_type_id IN NUMBER
4370 , p_task_priority_id IN NUMBER
4371 , p_assignment_status_id IN NUMBER
4372 , p_escalated_task IN VARCHAR2
4373 )
4374 RETURN NUMBER IS
4375 BEGIN
4376 FOR i IN g_custom_color_tbl.FIRST .. g_custom_color_tbl.LAST
4377 LOOP
4378 IF NVL(g_custom_color_tbl(i).task_type_id, p_task_type_id) = p_task_type_id
4379 AND NVL(g_custom_color_tbl(i).task_priority_id, p_task_priority_id) = p_task_priority_id
4380 AND NVL(g_custom_color_tbl(i).assignment_status_id, p_assignment_status_id) =
4381 p_assignment_status_id
4382 AND NVL(g_custom_color_tbl(i).escalated_task, p_escalated_task) = p_escalated_task THEN
4383 RETURN g_custom_color_tbl(i).background_col_dec;
4384 END IF;
4385 END LOOP;
4386 RETURN 0;
4387 END do_match;
4388
4389 BEGIN
4390 p_res_id := jtf_number_table();
4391 p_res_type := jtf_varchar2_table_2000();
4392 p_res_name := jtf_varchar2_table_2000();
4393 p_res_typ_name := jtf_varchar2_table_2000();
4394 p_res_key := jtf_varchar2_table_2000();
4395 p_cost := jtf_number_table();
4396
4397 trip_id := jtf_number_table();
4398 start_date := jtf_date_table();
4399 end_date := jtf_date_table();
4400 block_trip := jtf_number_table();
4401 tmp_trip_id := jtf_number_table();
4402 p_bck_res_key := jtf_varchar2_table_2000();
4403
4404 ---------------------------------------------------------
4405 --tables used for getting real/to be plan task parameters
4406 ---------------------------------------------------------
4407 plan_task_key := jtf_varchar2_table_100();
4411 plan_name := jtf_varchar2_table_2000();
4408 plan_start_date := jtf_date_table();
4409 plan_end_date := jtf_date_table();
4410 plan_color := jtf_number_table();
4412 plan_tooltip := jtf_varchar2_table_2000();
4413 plan_duration := jtf_number_table();
4414 plan_task_type_id := jtf_number_table();
4415 plan_resource_key := jtf_varchar2_table_2000();
4416 real_task_key := jtf_varchar2_table_100();
4417 real_start_date := jtf_date_table();
4418 real_end_date := jtf_date_table();
4419 real_color := jtf_number_table();
4420 real_name := jtf_varchar2_table_2000();
4421 real_tooltip := jtf_varchar2_table_2000();
4422 real_duration := jtf_number_table();
4423 real_task_type_id := jtf_number_table();
4424 real_resource_key := jtf_varchar2_table_2000();
4425 child_task := jtf_varchar2_table_100();
4426 real_parts_required := jtf_varchar2_table_100();
4427 real_access_hours := jtf_varchar2_table_100();
4428 real_after_hours := jtf_varchar2_table_100();
4429 real_customer_conf := jtf_varchar2_table_100();
4430 real_task_depend := jtf_varchar2_table_100();
4431 l_task_priority_id := jtf_number_table();
4432 l_assignment_status_id := jtf_number_table();
4433 l_escalated_task := jtf_varchar2_table_100();
4434
4435 oth_real_task_id := jtf_varchar2_table_100();
4436 oth_real_start_date := jtf_date_table();
4437 oth_real_end_date := jtf_date_table();
4438 oth_real_color := jtf_number_table();
4439 oth_real_NAME := jtf_varchar2_table_2000();
4440 oth_real_tooltip := jtf_varchar2_table_2000();
4441 oth_real_DURATION := jtf_number_table();
4442 oth_real_task_type_id := jtf_number_table();
4443 oth_real_resource_key := jtf_varchar2_table_2000();
4444 oth_real_child_task := jtf_varchar2_table_100();
4445 oth_real_parts_required := jtf_varchar2_table_100();
4446 oth_real_access_hours := jtf_varchar2_table_100();
4447 oth_real_after_hours := jtf_varchar2_table_100();
4448 oth_real_customer_conf := jtf_varchar2_table_100();
4449 oth_real_task_depend := jtf_varchar2_table_100();
4450
4451 l_planned_start_date := jtf_date_table();
4452 l_planned_end_date := jtf_date_table();
4453 l_actual_start_date := jtf_date_table();
4454 l_actual_end_date := jtf_date_table();
4455 l_actual_effort := jtf_number_table();
4456 l_actual_effort_uom := jtf_varchar2_table_100();
4457 l_planned_effort := jtf_number_table();
4458 l_planned_effort_uom := jtf_varchar2_table_100();
4459 l_actual_travel_duration := jtf_number_table();
4460 l_actual_travel_duration_uom :=jtf_varchar2_table_100();
4461 l_status_id := jtf_number_table();
4462
4463 L_COMMON_WHERE_RESOURCE_SINGLE:= ' AND (NVL(PT.TASK_ID,0)= :4 OR NVL(PT.TASK_ID,0) = -1)';
4464 L_COMMON_WHERE_RESOURCE_QUERY := ' AND (NVL(PT.TASK_ID,0)= :2 OR NVL(PT.TASK_ID,0) = -1) ';
4465 L_COMMON_WHERE_PARENT := ' AND NVL(PT.TASK_ID,0) = -1 ';
4466 L_COMMON_ORDERBY := ' ORDER BY POP.PLAN_OPTION_ID, PT.SCHEDULED_START_DATE ';
4467 L_COMMON_WHERE_RESOURCE_COST := ' AND (NVL(PT.TASK_ID,0)= :3 OR NVL(PT.TASK_ID,0) = -1)';
4468
4469
4470 L_RESOURCE_QUERY := ' SELECT RR.RESOURCE_ID'
4471 ||',RR.RESOURCE_TYPE'
4472 ||',CSF_RESOURCE_PUB.GET_RESOURCE_NAME (RR.RESOURCE_ID,RR.RESOURCE_TYPE) RESOURCE_NAME'
4473 ||',CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME(RR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME'
4474 ||',RR.RESOURCE_ID||''-''||RR.RESOURCE_TYPE||''-''||POP.PLAN_OPTION_ID RESOURCE_KEY'
4475 ||',POP.COST'
4476 ||' FROM '
4477 ||' CSF_R_REQUEST_TASKS RT,'
4478 ||' CSF_R_RESOURCE_RESULTS RR,'
4479 ||' CSF_R_PLAN_OPTIONS POP,'
4480 ||' CSF_R_PLAN_OPTION_TASKS PT,'
4481 ||' JTF_TASKS_B T'
4482 ||' WHERE RT.SCHED_REQUEST_ID =:1'
4483 ||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21))'
4484 ||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID'
4485 ||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID'
4486 ||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID'
4487 ||' AND PT.TASK_ID = T.TASK_ID(+)';
4488
4489 L_RESOURCE_QUERY_COST := ' SELECT RR.RESOURCE_ID'
4490 ||',RR.RESOURCE_TYPE'
4491 ||',CSF_RESOURCE_PUB.GET_RESOURCE_NAME (RR.RESOURCE_ID,RR.RESOURCE_TYPE) RESOURCE_NAME'
4492 ||',CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME(RR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME'
4493 ||',RR.RESOURCE_ID||''-''||RR.RESOURCE_TYPE||''-''||POP.PLAN_OPTION_ID RESOURCE_KEY'
4494 ||',POP.COST'
4495 ||' FROM '
4496 ||' CSF_R_REQUEST_TASKS RT,'
4497 ||' CSF_R_RESOURCE_RESULTS RR,'
4498 ||' CSF_R_PLAN_OPTIONS POP,'
4499 ||' CSF_R_PLAN_OPTION_TASKS PT,'
4500 ||' JTF_TASKS_B T'
4501 ||' WHERE RT.SCHED_REQUEST_ID =:1'
4502 ||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID'
4503 ||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID'
4504 ||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID'
4505 ||' AND PT.TASK_ID = T.TASK_ID(+)'
4506 ||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21))'
4507 ||' AND (RR.RESOURCE_ID,POP.COST)'
4508 ||' IN '
4512 ||' CSF_R_RESOURCE_RESULTS RR,'
4509 ||' (SELECT RR.RESOURCE_ID,MIN(POP.COST)'
4510 ||' FROM'
4511 ||' CSF_R_REQUEST_TASKS RT,'
4513 ||' CSF_R_PLAN_OPTIONS POP,'
4514 ||' CSF_R_PLAN_OPTION_TASKS PT,'
4515 ||' JTF_TASKS_B T'
4516 ||' WHERE RT.SCHED_REQUEST_ID =:2'
4517 ||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID'
4518 ||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID'
4519 ||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID'
4520 ||' AND PT.TASK_ID = T.TASK_ID(+)'
4521 ||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21))'
4522 ||' GROUP BY RR.RESOURCE_ID)';
4523
4524 L_RESOURCE_QUERY_COST_DAY := ' SELECT RR.RESOURCE_ID '
4525 ||' ,RR.RESOURCE_TYPE '
4526 ||' ,CSF_RESOURCE_PUB.GET_RESOURCE_NAME (RR.RESOURCE_ID,RR.RESOURCE_TYPE) RESOURCE_NAME '
4527 ||' ,CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME(RR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME '
4528 ||' ,RR.RESOURCE_ID||''-''||RR.RESOURCE_TYPE||''-''||POP.PLAN_OPTION_ID RESOURCE_KEY '
4529 ||' ,POP.COST '
4530 ||' FROM '
4531 ||' CSF_R_REQUEST_TASKS RT, '
4532 ||' CSF_R_RESOURCE_RESULTS RR, '
4533 ||' CSF_R_PLAN_OPTIONS POP, '
4534 ||' CSF_R_PLAN_OPTION_TASKS PT, '
4535 ||' JTF_TASKS_B T '
4536 ||' WHERE RT.SCHED_REQUEST_ID = :1 '
4537 ||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID '
4538 ||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID '
4539 ||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID '
4540 ||' AND PT.TASK_ID = T.TASK_ID(+) '
4541 ||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21)) '
4542 ||' AND PT.PLAN_OPTION_ID IN ( '
4543 ||' SELECT PLAN_OPTION_ID FROM '
4544 ||' (SELECT PT.PLAN_OPTION_ID, MIN(trunc(PT.SCHEDULED_START_DATE)) START_TIME,MIN(POP.COST) COST '
4545 ||' FROM '
4546 ||' CSF_R_REQUEST_TASKS RT, '
4547 ||' CSF_R_RESOURCE_RESULTS RR, '
4548 ||' CSF_R_PLAN_OPTIONS POP, '
4549 ||' CSF_R_PLAN_OPTION_TASKS PT, '
4550 ||' JTF_TASKS_B T '
4551 ||' WHERE RT.SCHED_REQUEST_ID = :2 '
4552 ||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID '
4553 ||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID '
4554 ||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID '
4555 ||' AND PT.TASK_ID = T.TASK_ID(+) '
4556 ||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21)) '
4557 ||' GROUP BY PT.PLAN_OPTION_ID '
4558 ||' ) WHERE (TRUNC(START_TIME),COST) IN '
4559 ||' (select TRUNC(START_TIME), MIN(COST) from '
4560 ||' (SELECT MIN(trunc(PT.SCHEDULED_START_DATE)) START_TIME,MIN(POP.COST) COST '
4561 ||' FROM '
4562 ||' CSF_R_REQUEST_TASKS RT, '
4563 ||' CSF_R_RESOURCE_RESULTS RR, '
4564 ||' CSF_R_PLAN_OPTIONS POP, '
4565 ||' CSF_R_PLAN_OPTION_TASKS PT, '
4566 ||' JTF_TASKS_B T '
4567 ||' WHERE RT.SCHED_REQUEST_ID = :4 '
4568 ||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID '
4569 ||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID '
4570 ||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID '
4571 ||' AND PT.TASK_ID = T.TASK_ID(+) '
4572 ||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21)) '
4573 ||' GROUP BY PT.PLAN_OPTION_ID) '
4574 ||' GROUP BY TRUNC(START_TIME))) ';
4575
4576 L_RESOURCE_SINGLE_QUERY :=' SELECT RR.RESOURCE_ID'
4577 ||',RR.RESOURCE_TYPE'
4578 ||',CSF_RESOURCE_PUB.GET_RESOURCE_NAME (RR.RESOURCE_ID,RR.RESOURCE_TYPE) RESOURCE_NAME'
4579 ||',CSF_GANTT_DATA_PKG.GET_RESOURCE_TYPE_NAME(RR.RESOURCE_TYPE ) RESOURCE_TYPE_NAME'
4580 ||',RR.RESOURCE_ID||''-''||RR.RESOURCE_TYPE||''-''||POP.PLAN_OPTION_ID RESOURCE_KEY'
4581 ||',POP.COST'
4582 ||' FROM '
4583 ||' CSF_R_REQUEST_TASKS RT,'
4584 ||' CSF_R_RESOURCE_RESULTS RR,'
4585 ||' CSF_R_PLAN_OPTIONS POP,'
4586 ||' CSF_R_PLAN_OPTION_TASKS PT,'
4587 ||' JTF_TASKS_B T'
4588 ||' WHERE RT.SCHED_REQUEST_ID =:1'
4589 ||' AND RT.REQUEST_TASK_ID = RR.REQUEST_TASK_ID'
4590 ||' AND RR.RESOURCE_RESULT_ID = POP.RESOURCE_RESULT_ID'
4591 ||' AND POP.PLAN_OPTION_ID = PT.PLAN_OPTION_ID'
4592 ||' AND PT.TASK_ID = T.TASK_ID(+)'
4593 ||' AND (NVL(T.TASK_TYPE_ID,0) NOT IN (20,21))'
4594 ||' AND RR.RESOURCE_ID = :2'
4595 ||' AND RR.RESOURCE_TYPE = :3';
4596
4597 IF p_par_task is not null and p_par_task > 0
4598 THEN
4599 IF p_display_option = 'D'
4600 THEN
4601
4602 L_QUERY :=L_RESOURCE_QUERY_COST_DAY||L_COMMON_WHERE_PARENT||L_COMMON_ORDERBY;
4603
4604 ELSIF p_display_option = 'R'
4605 THEN
4606
4607 L_QUERY :=L_RESOURCE_QUERY_COST ||L_COMMON_WHERE_PARENT||L_COMMON_ORDERBY;
4608 ELSIF p_display_option = 'S'
4609 THEN
4610
4611 L_QUERY := L_RESOURCE_SINGLE_QUERY||L_COMMON_WHERE_PARENT||L_COMMON_ORDERBY;
4612 ELSE
4613 L_QUERY := L_RESOURCE_QUERY||L_COMMON_WHERE_PARENT||L_COMMON_ORDERBY;
4614 END IF;
4615 ELSE
4616 IF p_display_option = 'D'
4617 THEN
4618 L_QUERY := L_RESOURCE_QUERY_COST_DAY||L_COMMON_WHERE_RESOURCE_COST||L_COMMON_ORDERBY;
4619 ELSIF p_display_option = 'R'
4620 THEN
4621 L_QUERY := L_RESOURCE_QUERY_COST||L_COMMON_WHERE_RESOURCE_COST||L_COMMON_ORDERBY;
4622 ELSIF p_display_option = 'S'
4623 THEN
4624 L_QUERY := L_RESOURCE_SINGLE_QUERY||L_COMMON_WHERE_RESOURCE_SINGLE||L_COMMON_ORDERBY;
4628 END IF;
4625 ELSE
4626 L_QUERY := L_RESOURCE_QUERY||L_COMMON_WHERE_RESOURCE_QUERY||L_COMMON_ORDERBY;
4627 END IF;
4629 IF p_par_task is not null and p_par_task > 0
4630 THEN
4631 IF p_display_option = 'D'
4632 THEN
4633 OPEN ResInfo FOR L_QUERY USING p_req_id,p_req_id,p_req_id;
4634 FETCH ResInfo BULK COLLECT INTO p_res_id,p_res_type,p_res_name,p_res_typ_name,p_res_key,p_cost;
4635 CLOSE ResInfo;
4636 ELSIF p_display_option = 'R'
4637 THEN
4638 OPEN ResInfo FOR L_QUERY USING p_req_id,p_req_id;
4639 FETCH ResInfo BULK COLLECT INTO p_res_id,p_res_type,p_res_name,p_res_typ_name,p_res_key,p_cost;
4640 CLOSE ResInfo;
4641 ELSIF p_display_option = 'S'
4642 THEN
4643 OPEN ResInfo FOR L_QUERY USING p_req_id,p_resource_id,p_resource_type;
4644 FETCH ResInfo BULK COLLECT INTO p_res_id,p_res_type,p_res_name,p_res_typ_name,p_res_key,p_cost;
4645 CLOSE ResInfo;
4646 ELSE
4647 OPEN ResInfo FOR L_QUERY USING p_req_id;
4648 FETCH ResInfo BULK COLLECT INTO p_res_id,p_res_type,p_res_name,p_res_typ_name,p_res_key,p_cost;
4649 CLOSE ResInfo;
4650 END IF;
4651 ELSE
4652 IF p_display_option = 'D'
4653 THEN
4654 OPEN ResInfo FOR L_QUERY USING p_req_id,p_req_id,p_req_id,p_task_id;
4655 FETCH ResInfo BULK COLLECT INTO p_res_id,p_res_type,p_res_name,p_res_typ_name,p_res_key,p_cost;
4656 CLOSE ResInfo;
4657 ELSIF p_display_option = 'R'
4658 THEN
4659 OPEN ResInfo FOR L_QUERY USING p_req_id,p_req_id,p_task_id;
4660 FETCH ResInfo BULK COLLECT INTO p_res_id,p_res_type,p_res_name,p_res_typ_name,p_res_key,p_cost;
4661 CLOSE ResInfo;
4662 ELSIF p_display_option = 'S'
4663 THEN
4664 OPEN ResInfo FOR L_QUERY USING p_req_id,p_resource_id,p_resource_type ,p_task_id;
4665 FETCH ResInfo BULK COLLECT INTO p_res_id,p_res_type,p_res_name,p_res_typ_name,p_res_key,p_cost;
4666 CLOSE ResInfo;
4667 ELSE
4668 OPEN ResInfo FOR L_QUERY USING p_req_id,p_task_id;
4669 FETCH ResInfo BULK COLLECT INTO p_res_id,p_res_type,p_res_name,p_res_typ_name,p_res_key,p_cost;
4670 CLOSE ResInfo;
4671 END IF;
4672 END IF;
4673
4674 OPEN c_res_detail;
4675 FETCH c_res_detail
4676 BULK COLLECT INTO tmp_trip_id
4677 , trip_id
4678 , start_date
4679 , end_date
4680 , block_trip
4681 , p_bck_res_key;
4682 CLOSE c_res_detail;
4683
4684 if g_tz_enabled ='Y' and sch_adv_tz='CTZ' and trip_id.count > 0
4685 then
4686 IF trip_id.COUNT IS NOT NULL AND trip_id.COUNT > 0
4687 THEN
4688 FOR i IN trip_id.FIRST .. trip_id.LAST
4689 LOOP
4690 start_date(i) :=fnd_date.adjust_datetime(start_date(i),g_client_tz,g_server_tz );
4691 end_date(i) :=fnd_date.adjust_datetime(end_date(i) ,g_client_tz,g_server_tz);
4692 END LOOP;
4693 END IF;
4694 elsif g_tz_enabled ='Y' and sch_adv_tz='ITZ' and trip_id.count > 0
4695 then
4696 IF trip_id.COUNT IS NOT NULL AND trip_id.COUNT > 0
4697 THEN
4698 FOR i IN trip_id.FIRST .. trip_id.LAST
4699 LOOP
4700 start_date(i) :=fnd_date.adjust_datetime(start_date(i),g_client_tz,inc_tz_code );
4701 end_date(i) :=fnd_date.adjust_datetime(end_date(i) ,g_client_tz,inc_tz_code);
4702 END LOOP;
4703 END IF;
4704 end if;
4705
4706 OPEN c_planned_task;
4707 FETCH c_planned_task
4708 BULK COLLECT INTO plan_task_key
4709 , plan_resource_key
4710 , plan_start_date
4711 , plan_end_date
4712 , plan_color
4713 , plan_name
4714 , plan_tooltip
4715 , plan_duration
4716 , plan_task_type_id;
4717 CLOSE c_planned_task;
4718
4719 IF plan_task_key.COUNT IS NOT NULL AND plan_task_key.COUNT > 0
4720 THEN
4721 FOR i IN plan_task_key.first..plan_task_key.last
4722 LOOP
4723 IF g_tz_enabled ='Y' AND sch_adv_tz='CTZ'
4724 THEN
4725 plan_start_date(i):=fnd_date.adjust_datetime(plan_start_date(i),g_client_tz,g_server_tz );
4726 ELSIF g_tz_enabled ='Y' AND sch_adv_tz='ITZ'
4727 THEN
4728 plan_start_date(i):=fnd_date.adjust_datetime(plan_start_date(i),g_client_tz,inc_tz_code );
4729 END IF;
4730 IF g_tz_enabled ='Y' AND sch_adv_tz='CTZ'
4731 THEN
4732 plan_end_date(i):=fnd_date.adjust_datetime(plan_end_date(i),g_client_tz,g_server_tz);
4733 ELSIF g_tz_enabled ='Y' AND sch_adv_tz='ITZ'
4734 THEN
4735 plan_end_date(i):=fnd_date.adjust_datetime(plan_end_date(i),g_client_tz,inc_tz_code);
4736 END IF;
4737 END LOOP;
4738 END IF;
4739
4740 IF g_use_custom_chromatics
4741 THEN
4742 get_custom_color;
4743 END IF;
4744
4745 l_task_depends :='N';
4746 FOR i IN c_icon_setup
4747 LOOP
4748 l_task_depends :='Y';
4749 END LOOP;
4750
4751 IF l_task_depends ='Y'
4752 THEN
4753 IF g_label_on_task
4754 THEN
4755 OPEN c3;
4756 FETCH c3
4760 , oth_real_color
4757 BULK COLLECT INTO oth_real_task_id
4758 , oth_real_start_date
4759 , oth_real_end_date
4761 , oth_real_DURATION
4762 , oth_real_task_type_id
4763 , l_task_priority_id
4764 , l_status_id
4765 , oth_real_tooltip
4766 , oth_real_resource_key
4767 , oth_real_NAME
4768 , l_planned_start_date
4769 , l_planned_end_date
4770 , l_actual_start_date
4771 , l_actual_end_date
4772 , l_actual_effort
4773 , l_actual_effort_uom
4774 , l_planned_effort
4775 , l_planned_effort_uom
4776 , l_escalated_task
4777 , oth_real_access_hours
4778 , oth_real_after_hours
4779 , oth_real_customer_conf
4780 , oth_real_task_depend
4781 , oth_real_parts_required
4782 , oth_real_child_task
4783 , l_actual_travel_duration
4784 , l_actual_travel_duration_uom
4785 , l_task_depend
4786 ;
4787 CLOSE c3;
4788 ELSE
4789 OPEN c4;
4790 FETCH c4
4791 BULK COLLECT INTO oth_real_task_id
4792 , oth_real_start_date
4793 , oth_real_end_date
4794 , oth_real_color
4795 , oth_real_DURATION
4796 , oth_real_task_type_id
4797 , l_task_priority_id
4798 , l_status_id
4799 , oth_real_tooltip
4800 , oth_real_resource_key
4801 , oth_real_NAME
4802 , l_planned_start_date
4803 , l_planned_end_date
4804 , l_actual_start_date
4805 , l_actual_end_date
4806 , l_actual_effort
4807 , l_actual_effort_uom
4808 , l_planned_effort
4809 , l_planned_effort_uom
4810 , l_escalated_task
4811 , oth_real_access_hours
4812 , oth_real_after_hours
4813 , oth_real_customer_conf
4814 , oth_real_task_depend
4815 , oth_real_parts_required
4816 , oth_real_child_task
4817 , l_actual_travel_duration
4818 , l_actual_travel_duration_uom
4819 , l_task_depend
4820 ;
4821 CLOSE c4;
4822 END IF;
4823 ELSIF nvl(l_task_depends,'N') ='N'
4824 THEN
4825 IF g_label_on_task
4826 THEN
4827 OPEN c1;
4828 FETCH c1
4829 BULK COLLECT INTO oth_real_task_id
4830 , oth_real_start_date
4831 , oth_real_end_date
4832 , oth_real_color
4833 , oth_real_DURATION
4834 , oth_real_task_type_id
4835 , l_task_priority_id
4836 , l_status_id
4837 , oth_real_tooltip
4838 , oth_real_resource_key
4839 , oth_real_NAME
4840 , l_planned_start_date
4841 , l_planned_end_date
4842 , l_actual_start_date
4843 , l_actual_end_date
4844 , l_actual_effort
4845 , l_actual_effort_uom
4846 , l_planned_effort
4847 , l_planned_effort_uom
4848 , l_escalated_task
4849 , oth_real_access_hours
4850 , oth_real_after_hours
4851 , oth_real_customer_conf
4852 , oth_real_task_depend
4853 , oth_real_parts_required
4854 , oth_real_child_task
4855 , l_actual_travel_duration
4856 , l_actual_travel_duration_uom;
4857 CLOSE c1;
4858 ELSE
4859 OPEN c2;
4860 FETCH c2
4861 BULK COLLECT INTO oth_real_task_id
4862 , oth_real_start_date
4863 , oth_real_end_date
4864 , oth_real_color
4865 , oth_real_DURATION
4866 , oth_real_task_type_id
4867 , l_task_priority_id
4868 , l_status_id
4869 , oth_real_tooltip
4870 , oth_real_resource_key
4871 , oth_real_NAME
4872 , l_planned_start_date
4873 , l_planned_end_date
4874 , l_actual_start_date
4875 , l_actual_end_date
4876 , l_actual_effort
4877 , l_actual_effort_uom
4878 , l_planned_effort
4879 , l_planned_effort_uom
4880 , l_escalated_task
4881 , oth_real_access_hours
4882 , oth_real_after_hours
4883 , oth_real_customer_conf
4884 , oth_real_task_depend
4885 , oth_real_parts_required
4886 , oth_real_child_task
4887 , l_actual_travel_duration
4888 , l_actual_travel_duration_uom;
4889 CLOSE c2;
4890 END IF;
4891 END IF;
4892
4893 IF oth_real_task_id.COUNT IS NOT NULL AND oth_real_task_id.COUNT > 0
4894 THEN
4895 FOR i IN oth_real_task_id.FIRST..oth_real_task_id.LAST
4896 LOOP
4897 IF nvl(l_task_depends,'N') ='Y'
4898 THEN
4899 IF oth_real_task_id(i) = l_task_depend(i)
4900 THEN
4901 oth_real_task_depend(i) := 'Y';
4902 END IF;
4903 END IF;
4904 IF l_actual_start_date(i) IS NOT NULL THEN
4908 IF l_actual_start_date(i) IS NOT NULL THEN
4905 oth_real_start_date(i) := l_actual_start_date(i);
4906 END IF;
4907
4909 IF l_actual_end_date(i) IS NULL THEN
4910 IF l_actual_effort(i) IS NULL OR l_actual_effort(i) = 0 THEN
4911 IF l_planned_effort(i) IS NOT NULL AND l_planned_effort(i) > 0 THEN
4912 l_planned_effort(i) :=
4913 csf_gantt_data_pkg.convert_to_days(l_planned_effort(i)
4914 , NVL(l_planned_effort_uom(i), g_uom_hours), g_uom_hours);
4915 END IF;
4916
4917 IF l_default_effort IS NOT NULL AND l_default_effort > 0 THEN
4918 l_csf_default_effort :=
4919 csf_gantt_data_pkg.convert_to_days(l_default_effort
4920 , NVL(l_default_effort_uom, g_uom_hours), g_uom_hours);
4921 END IF;
4922
4923 oth_real_end_date(i) :=
4924 (
4925 l_actual_start_date(i) + NVL(l_planned_effort(i), NVL(l_csf_default_effort, 0))
4926 );
4927 ELSE
4928 l_actual_effort(i) :=
4929 csf_gantt_data_pkg.convert_to_days(l_actual_effort(i)
4930 , NVL(l_actual_effort_uom(i), g_uom_hours), g_uom_hours);
4931 oth_real_end_date(i) :=(l_actual_start_date(i) + NVL(l_actual_effort(i), 0));
4932 END IF;
4933 ELSE
4934 IF l_actual_end_date(i) <= l_actual_start_date(i) THEN
4935 IF l_actual_effort(i) IS NULL OR l_actual_effort(i) = 0 THEN
4936 -- this is true then calculate the actual end_date based uppon the profile values.
4937 IF l_planned_effort(i) IS NOT NULL AND l_planned_effort(i) > 0 THEN
4938 l_planned_effort(i) :=
4939 csf_gantt_data_pkg.convert_to_days(l_planned_effort(i)
4940 , NVL(l_planned_effort_uom(i), g_uom_hours), g_uom_hours);
4941 END IF;
4942
4943 IF l_default_effort IS NOT NULL AND l_default_effort > 0 THEN
4944 l_csf_default_effort :=
4945 csf_gantt_data_pkg.convert_to_days(l_default_effort
4946 , NVL(l_default_effort_uom, g_uom_hours), g_uom_hours);
4947 END IF;
4948
4949 oth_real_end_date(i) :=
4950 (
4951 l_actual_start_date(i) + NVL(l_planned_effort(i), NVL(l_csf_default_effort, 0))
4952 );
4953 ELSE
4954 -- if not null then actual effort to the actual_end_date
4955 l_actual_effort(i) :=
4956 csf_gantt_data_pkg.convert_to_days(l_actual_effort(i)
4957 , NVL(l_actual_effort_uom(i), g_uom_hours), g_uom_hours);
4958 oth_real_end_date(i) :=(l_actual_start_date(i) + NVL(l_actual_effort(i), 0));
4959 END IF; --end if for actual effort is nul or zero
4960 ELSE
4961 -- actual end date is not null, check if actual_end_date > actual_start_date
4962 IF l_actual_end_date(i) > l_actual_start_date(i) THEN
4963 oth_real_end_date(i) := l_actual_end_date(i);
4964 END IF; -- end if
4965 END IF;
4966 --end if for r_sch_end_date.actual_end_date = r_sch_end_date.actual_start_date
4967 END IF; -- end if for actual_end_date is null
4968 IF l_actual_travel_duration IS NOT NULL
4969 THEN
4970 IF l_actual_travel_duration_uom IS NOT NULL
4971 THEN
4972 oth_real_DURATION(i):=convert_to_min(l_actual_travel_duration(i),l_actual_travel_duration_uom(i),g_uom_minutes);
4973 END IF;
4974 ELSE
4975 oth_real_DURATION(i):=0;
4976 END IF;
4977 ELSE --else for actual_start_date is null
4978 IF oth_real_start_date(i) IS NOT NULL THEN
4979 -- scheduled_start_date is not null then check if scheduled_end_date is null
4980 IF oth_real_end_date(i) IS NOT NULL THEN
4981 -- scheduled_start_date is not null then check if scheduled_start_date=scheduled_end_date
4982 IF oth_real_start_date(i) = oth_real_end_date(i) THEN
4983 IF (l_planned_effort(i) IS NULL) OR(l_planned_effort(i) = 0) THEN
4984 IF l_default_effort IS NOT NULL AND l_default_effort > 0 THEN
4985 l_csf_default_effort :=
4986 csf_gantt_data_pkg.convert_to_days(l_default_effort
4987 , NVL(l_default_effort_uom, g_uom_hours), g_uom_hours);
4988 END IF;
4989
4990 oth_real_end_date(i) :=(oth_real_start_date(i) + NVL(l_csf_default_effort, 0));
4991 ELSE
4992 IF l_planned_effort(i) IS NOT NULL AND l_planned_effort(i) > 0 THEN
4993 l_planned_effort(i) :=
4994 csf_gantt_data_pkg.convert_to_days(l_planned_effort(i)
4995 , NVL(l_planned_effort_uom(i), g_uom_hours), g_uom_hours);
4996 END IF;
4997
4998 oth_real_end_date(i) :=(oth_real_start_date(i) + NVL(l_planned_effort(i), 0));
4999 END IF;
5000 END IF;
5001 ELSE
5002 -- scheduled_end_date is null then check for planned effort null
5003 IF (l_planned_effort(i) IS NULL) OR(l_planned_effort(i) = 0) THEN
5004 IF l_default_effort IS NOT NULL AND l_default_effort > 0 THEN
5005 l_csf_default_effort :=
5006 csf_gantt_data_pkg.convert_to_days(l_default_effort
5010 oth_real_end_date(i) :=(oth_real_start_date(i) + NVL(l_csf_default_effort, 0));
5007 , NVL(l_default_effort_uom, g_uom_hours), g_uom_hours);
5008 END IF;
5009
5011 ELSE
5012 -- declar variable l_planned_effort to get uom coverted into days for actual effort for
5013 IF l_planned_effort(i) IS NOT NULL AND l_planned_effort(i) > 0 THEN
5014 l_planned_effort(i) :=
5015 csf_gantt_data_pkg.convert_to_days(l_planned_effort(i)
5016 , NVL(l_planned_effort_uom(i), g_uom_hours), g_uom_hours);
5017 END IF;
5018
5019 oth_real_end_date(i) :=(oth_real_start_date(i) + NVL(l_planned_effort(i), 0));
5020 END IF;
5021 END IF; -- end if for scheduled_end_date is null
5022 END IF; -- end if for scheduled_start_date is not null
5023 END IF; -- end if for actual_start_date is not null
5024 ------------------------------------------
5025 --end for scheduled end dates
5026 ------------------------------------------
5027 IF g_use_custom_chromatics
5028 THEN
5029 l_task_custom_color := 'N';
5030 IF l_rule_id IS NOT NULL
5031 THEN
5032 IF l_actual_start_date(i) IS NOT NULL
5033 THEN
5034 IF l_actual_end_date(i) IS NOT NULL
5035 THEN
5036 IF l_actual_end_date(i) = l_actual_start_date(i)
5037 THEN
5038 l_task_custom_color := 'Y';
5039 END IF; --end if for actual_end_date=actual_start_date
5040 ELSE
5041 IF NVL(l_actual_effort(i), 0) = 0
5042 THEN
5043 IF NVL(l_planned_effort(i), 0) = 0
5044 THEN
5045 l_task_custom_color := 'Y';
5046 END IF;
5047 END IF;
5048 END IF; --end if for actual_end_date is not null
5049 ELSE --for actual start date is null
5050 IF oth_real_end_date(i) IS NOT NULL
5051 THEN
5052 IF oth_real_end_date(i) = oth_real_start_date(i)
5053 THEN
5054 l_task_custom_color := 'Y';
5055 END IF;
5056 ELSE
5057 l_task_custom_color := 'Y';
5058 END IF; --end if scheduled end_date is not null
5059 END IF; --end if for actual_start_date is not null
5060 END IF; --rule id condition for task date usage
5061 IF l_task_custom_color = 'Y'
5062 THEN
5063 IF l_rule_id IS NOT NULL
5064 THEN
5065 IF NVL(p_rule_id, 1) <> l_rule_id
5066 THEN
5067 OPEN get_tdu_color(l_rule_id);
5068 FETCH get_tdu_color
5069 INTO p_color;
5070 IF get_tdu_color%NOTFOUND
5071 THEN
5072 CLOSE get_tdu_color;
5073 IF(NVL(p_cur_task_type_id, -1) <> oth_real_task_type_id(i)
5074 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
5075 OR NVL(p_cur_task_status_id, -1) <> l_status_id(i)
5076 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i))
5077 THEN
5078 real_color(i):=do_match(oth_real_task_type_id(i),l_task_priority_id(i),l_status_id(i),l_escalated_task(i));
5079 p_cur_color := oth_real_color(i);
5080 p_cur_task_type_id := oth_real_task_type_id(i);
5081 p_cur_task_priority_id := l_task_priority_id(i);
5082 p_cur_task_status_id := l_status_id(i);
5083 p_cur_escalated_task := l_escalated_task(i);
5084 ELSE
5085 oth_real_color(i) := p_cur_color;
5086 END IF;
5087 ELSE
5088 oth_real_color(i) := p_color;
5089 CLOSE get_tdu_color;
5090 END IF;
5091 ELSE
5092 oth_real_color(i) := p_color;
5093 END IF;
5094 ELSE
5095 IF(NVL(p_cur_task_type_id, -1) <> oth_real_task_type_id(i)
5096 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
5097 OR NVL(p_cur_task_status_id, -1) <> l_status_id(i)
5098 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
5099 )
5100 THEN
5101 oth_real_color(i):=do_match(oth_real_task_type_id(i), l_task_priority_id(i), l_status_id(i),l_escalated_task(i));
5102 p_cur_color := oth_real_color(i);
5103 p_cur_task_type_id := oth_real_task_type_id(i);
5104 p_cur_task_priority_id := l_task_priority_id(i);
5105 p_cur_task_status_id := l_status_id(i);
5106 p_cur_escalated_task := l_escalated_task(i);
5107 ELSE
5108 oth_real_color(i) := p_cur_color;
5109 END IF;
5110 END IF;
5111 ELSE
5112 IF (NVL(p_cur_task_type_id, -1) <> oth_real_task_type_id(i)
5113 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
5114 OR NVL(p_cur_task_status_id, -1) <> l_status_id(i)
5115 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
5116 )
5117 THEN
5121 p_cur_task_priority_id := l_task_priority_id(i);
5118 oth_real_color(i):=do_match(oth_real_task_type_id(i), l_task_priority_id(i), l_status_id(i), l_escalated_task(i));
5119 p_cur_color := oth_real_color(i);
5120 p_cur_task_type_id := oth_real_task_type_id(i);
5122 p_cur_task_status_id := l_status_id(i);
5123 p_cur_escalated_task := l_escalated_task(i);
5124 ELSE
5125 oth_real_color(i) := p_cur_color;
5126 END IF;
5127 END IF;
5128 ELSE
5129 IF l_escalated_task(i) = 'Y'
5130 THEN
5131 oth_real_color(i) := red;
5132 ELSIF l_actual_start_date(i) IS NOT NULL
5133 THEN
5134 oth_real_color(i) := yellow;
5135 ELSE
5136 oth_real_color(i) := blue;
5137 END IF;
5138 END IF;
5139
5140 if g_tz_enabled ='Y' and sch_adv_tz='CTZ'
5141 then
5142 oth_real_start_date(i) :=fnd_date.adjust_datetime(oth_real_start_date(i),g_client_tz,g_server_tz );
5143 oth_real_end_date(i) :=fnd_date.adjust_datetime(oth_real_end_date(i) ,g_client_tz,g_server_tz);
5144 elsif g_tz_enabled ='Y' and sch_adv_tz='ITZ'
5145 then
5146 oth_real_start_date(i) :=fnd_date.adjust_datetime(oth_real_start_date(i),g_client_tz,inc_tz_code );
5147 oth_real_end_date(i) :=fnd_date.adjust_datetime(oth_real_end_date(i) ,g_client_tz,inc_tz_code);
5148 end if;
5149 END LOOP;
5150 END IF;
5151
5152 IF l_task_depends ='Y'
5153 THEN
5154 IF g_label_on_task
5155 THEN
5156 OPEN c_real_task_3;
5157 FETCH c_real_task_3
5158 BULK COLLECT INTO
5159 real_task_key
5160 , real_resource_key
5161 , real_start_date
5162 , real_end_date
5163 , real_color
5164 , real_tooltip
5165 , real_duration
5166 , real_task_type_id
5167 , l_task_priority_id
5168 , l_assignment_status_id
5169 , l_actual_start_date
5170 , l_actual_end_date
5171 , real_name
5172 , l_escalated_task
5173 , real_access_hours
5174 , real_after_hours
5175 , real_customer_conf
5176 , real_task_depend
5177 , real_parts_required
5178 , child_task
5179 , l_task_depend;
5180 CLOSE c_real_task_3;
5181 ELSE
5182 OPEN c_real_task_4;
5183 FETCH c_real_task_4
5184 BULK COLLECT INTO
5185 real_task_key
5186 , real_resource_key
5187 , real_start_date
5188 , real_end_date
5189 , real_color
5190 , real_tooltip
5191 , real_duration
5192 , real_task_type_id
5193 , l_task_priority_id
5194 , l_assignment_status_id
5195 , l_actual_start_date
5196 , l_actual_end_date
5197 , real_name
5198 , l_escalated_task
5199 , real_access_hours
5200 , real_after_hours
5201 , real_customer_conf
5202 , real_task_depend
5203 , real_parts_required
5204 , child_task
5205 , l_task_depend;
5206 CLOSE c_real_task_4;
5207 END IF;
5208 ELSIF nvl(l_task_depends,'N') ='N'
5209 THEN
5210 IF g_label_on_task
5211 THEN
5212 OPEN c_real_task_1;
5213 FETCH c_real_task_1
5214 BULK COLLECT INTO
5215 real_task_key
5216 , real_resource_key
5217 , real_start_date
5218 , real_end_date
5219 , real_color
5220 , real_tooltip
5221 , real_duration
5222 , real_task_type_id
5223 , l_task_priority_id
5224 , l_assignment_status_id
5225 , l_actual_start_date
5226 , l_actual_end_date
5227 , real_name
5228 , l_escalated_task
5229 , real_access_hours
5230 , real_after_hours
5231 , real_customer_conf
5232 , real_task_depend
5233 , real_parts_required
5234 , child_task;
5235 CLOSE c_real_task_1;
5236 ELSE
5237 OPEN c_real_task_2;
5238 FETCH c_real_task_2
5239 BULK COLLECT INTO
5240 real_task_key
5241 , real_resource_key
5242 , real_start_date
5243 , real_end_date
5244 , real_color
5245 , real_tooltip
5246 , real_duration
5247 , real_task_type_id
5248 , l_task_priority_id
5249 , l_assignment_status_id
5250 , l_actual_start_date
5251 , l_actual_end_date
5252 , real_name
5253 , l_escalated_task
5254 , real_access_hours
5255 , real_after_hours
5256 , real_customer_conf
5257 , real_task_depend
5258 , real_parts_required
5259 , child_task;
5260 CLOSE c_real_task_2;
5261 END IF;
5262 END IF;
5263
5264 IF real_task_key.COUNT IS NOT NULL AND real_task_key.COUNT > 0
5265 THEN
5266 FOR i IN real_task_key.first..real_task_key.last
5267 LOOP
5271 ELSIF g_tz_enabled ='Y' AND sch_adv_tz='ITZ'
5268 IF g_tz_enabled ='Y' AND sch_adv_tz='CTZ'
5269 THEN
5270 real_start_date(i) :=fnd_date.adjust_datetime(real_start_date(i),g_client_tz,g_server_tz );
5272 THEN
5273 real_start_date(i) :=fnd_date.adjust_datetime(real_start_date(i),g_client_tz,inc_tz_code );
5274 END IF;
5275 IF g_tz_enabled ='Y' AND sch_adv_tz='CTZ'
5276 THEN
5277 real_end_date(i) :=fnd_date.adjust_datetime(real_end_date(i),g_client_tz,g_server_tz);
5278 ELSIF g_tz_enabled ='Y' AND sch_adv_tz='ITZ'
5279 THEN
5280 real_end_date(i) :=fnd_date.adjust_datetime(real_end_date(i),g_client_tz,inc_tz_code);
5281 END IF;
5282
5283 IF nvl(l_task_depends,'N') ='Y'
5284 THEN
5285 IF real_task_key(i) = l_task_depend(i)
5286 THEN
5287 real_task_depend(i) := 'Y';
5288 END IF;
5289 END IF;
5290
5291 IF g_use_custom_chromatics
5292 THEN
5293 l_task_custom_color := 'N';
5294 IF l_rule_id IS NOT NULL
5295 THEN
5296 IF real_end_date(i) IS NOT NULL
5297 THEN
5298 IF real_end_date(i) = real_start_date(i)
5299 THEN
5300 l_task_custom_color := 'Y';
5301 END IF;
5302 ELSE
5303 l_task_custom_color := 'Y';
5304 END IF; --end if for scheduled_start_date is not null
5305 END IF; --rule id condition for task date usage
5306
5307 IF l_task_custom_color = 'Y'
5308 THEN
5309 IF l_rule_id IS NOT NULL
5310 THEN
5311 IF NVL(p_rule_id, 1) <> l_rule_id
5312 THEN
5313 OPEN get_tdu_color(l_rule_id);
5314 FETCH get_tdu_color
5315 INTO p_color;
5316 IF get_tdu_color%NOTFOUND
5317 THEN
5318 CLOSE get_tdu_color;
5319 IF(NVL(p_cur_task_type_id, -1) <> real_task_type_id(i)
5320 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
5321 OR NVL(p_cur_task_status_id, -1) <> l_assignment_status_id(i)
5322 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
5323 )
5324 THEN
5325 real_color(i):=do_match(real_task_type_id(i),l_task_priority_id(i),l_assignment_status_id(i),l_escalated_task(i));
5326 p_cur_color := real_color(i);
5327 p_cur_task_type_id := real_task_type_id(i);
5328 p_cur_task_priority_id := l_task_priority_id(i);
5329 p_cur_task_status_id := l_assignment_status_id(i);
5330 p_cur_escalated_task := l_escalated_task(i);
5331 ELSE
5332 real_color(i) := p_cur_color;
5333 END IF;
5334 ELSE
5335 real_color(i) := p_color;
5336 CLOSE get_tdu_color;
5337 END IF;
5338 ELSE
5339 real_color(i) := p_color;
5340 END IF;
5341 ELSE
5342 IF(NVL(p_cur_task_type_id, -1) <> real_task_type_id(i)
5343 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
5344 OR NVL(p_cur_task_status_id, -1) <> l_assignment_status_id(i)
5345 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
5346 )
5347 THEN
5348 real_color(i):=do_match(real_task_type_id(i), l_task_priority_id(i), l_assignment_status_id(i),l_escalated_task(i));
5349 p_cur_color := real_color(i);
5350 p_cur_task_type_id := real_task_type_id(i);
5351 p_cur_task_priority_id := l_task_priority_id(i);
5352 p_cur_task_status_id := l_assignment_status_id(i);
5353 p_cur_escalated_task := l_escalated_task(i);
5354 ELSE
5355 real_color(i) := p_cur_color;
5356 END IF;
5357 END IF;
5358 ELSE
5359 IF (NVL(p_cur_task_type_id, -1) <> real_task_type_id(i)
5360 OR NVL(p_cur_task_priority_id, -1) <> l_task_priority_id(i)
5361 OR NVL(p_cur_task_status_id, -1) <> l_assignment_status_id(i)
5362 OR NVL(p_cur_escalated_task, 'G') <> l_escalated_task(i)
5363 )
5364 THEN
5365 real_color(i):=do_match(real_task_type_id(i), l_task_priority_id(i), l_assignment_status_id(i), l_escalated_task(i));
5366 p_cur_color := real_color(i);
5367 p_cur_task_type_id := real_task_type_id(i);
5368 p_cur_task_priority_id := l_task_priority_id(i);
5369 p_cur_task_status_id := l_assignment_status_id(i);
5370 p_cur_escalated_task := l_escalated_task(i);
5371 ELSE
5372 real_color(i) := p_cur_color;
5373 END IF;
5374 END IF;
5375 ELSE
5376 IF l_escalated_task(i) = 'Y'
5377 THEN
5378 real_color(i) := red;
5379 ELSIF l_actual_start_date(i) IS NOT NULL
5380 THEN
5381 real_color(i) := yellow;
5382 ELSE
5383 real_color(i) := blue;
5384 END IF;
5385 END IF;
5386 END LOOP;
5387 END IF;
5388
5392 x_return_status := fnd_api.g_ret_sts_success;
5389 IF trip_id.COUNT IS NULL THEN
5390 l_return_status := 'E';
5391 ELSE
5393 END IF;
5394 END get_schedule_advise_options;
5395 BEGIN
5396 -- package instantiation
5397 set_tooltip_labels;
5398 g_use_custom_chromatics := fnd_profile.VALUE('CSF_USE_CUSTOM_CHROMATICS') = 'Y';
5399
5400 IF g_use_custom_chromatics THEN
5401 g_get_custom_color;
5402 END IF;
5403 g_user_id := fnd_global.user_id;
5404 g_uom_minutes := fnd_profile.value_specific('CSF_UOM_MINUTES', g_user_id);
5405 g_uom_hours := fnd_profile.value_specific('CSF_UOM_HOURS', g_user_id);
5406 g_date_format := fnd_profile.value_specific('ICX_DATE_FORMAT_MASK');
5407 l_language := USERENV('LANG');
5408 g_resource_id :=csf_resource_pub.resource_id;
5409 g_resource_type :=csf_resource_pub.resource_type;
5410 g_server_tz := fnd_timezones.get_server_timezone_code;
5411 g_client_tz := fnd_timezones.get_client_timezone_code;
5412 g_tz_enabled := 'N';
5413
5414 -- this function is currently not present in fnd_timezones 1158
5415 -- copied from AFTZONEB.pls 115.3 and modified
5416 if nvl(fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS'),'N') = 'Y'
5417 then
5418 g_tz_enabled := 'Y';
5419 end if;
5420
5421 END csf_gantt_data_pkg;