[Home] [Help]
PACKAGE BODY: APPS.CSF_PLANBOARD_TASKS
Source
1 PACKAGE BODY csf_planboard_tasks AS
2 /* $Header: CSFCTPLB.pls 120.18 2008/02/20 07:31:41 ramchint ship $ */
3
4 /* Change history
5 Date Userid Change
6 ---------- -------- ---------------------------------------------------
7 06-FEB-2006 srengana Re-Genesis
8
9
10 */
11
12 g_use_custom_chromatics boolean;
13 -- ================================ --
14 -- private functions and procedures --
15 -- ================================ --
16
17 ------------------------------------------------------------------------
18 -- get the customer and contract name for the SR of the task
19 ------------------------------------------------------------------------
20 procedure get_customer
21 ( p_incident_id in number
22 , p_customer out nocopy varchar2
23 , p_contract out nocopy varchar2
24 )
25 is
26 cursor c ( b_incident_id number ) is
27 select p.party_name
28 , o.name
29 from cs_incidents_all_b i
30 , hz_parties p
31 , okc_k_lines_tl o
32 where i.incident_id = b_incident_id
33 and i.customer_id = p.party_id(+)
34 and i.contract_service_id = o.id(+)
35 and o.language(+) = userenv('lang');
36 r c%rowtype;
37 begin
38 open c(p_incident_id);
39 fetch c into r;
40 if c%found then
41 close c;
42 -- could still both be null, though
43 p_customer := r.party_name;
44 p_contract := r.name;
45 return;
46 end if;
47 close c;
48 p_customer := null;
49 p_contract := null;
50 return;
51 end get_customer;
52
53 ------------------------------------------------------------------------
54 -- convert travel time to days
55 ------------------------------------------------------------------------
56 function convert_to_days
57 ( p_duration number
58 , p_uom varchar2
59 , p_uom_hours varchar2
60 )
61 return number
62 is
63 l_value number;
64 begin
65 l_value := inv_convert.inv_um_convert
66 ( item_id => 0
67 , precision => 2
68 , from_quantity => p_duration
69 , from_unit => p_uom
70 , to_unit => p_uom_hours
71 , from_name => null
72 , to_name => null
73 );
74 return l_value/24;
75 end convert_to_days;
76
77 ------------------------------------------------------------------------
78 -- see if this task (SR, Task) has notes attached
79 ------------------------------------------------------------------------
80
81 function task_has_notes
82 ( p_task_id number
83 , p_source_code varchar2
84 , p_source_id number
85 ) return boolean
86 is
87 --
88 cursor c_note ( b_id number, b_type varchar2 ) is
89 select null tmp
90 from jtf_notes_b
91 where source_object_code = b_type
92 and source_object_id = b_id;
93
94 l_tmp varchar2(1);
95 --
96
97 begin
98 --
99 -- task notes
100 open c_note(p_task_id, 'TASK');
101 fetch c_note into l_tmp;
102 if c_note%found then
103 close c_note;
104 return true;
105 end if;
106 close c_note;
107
108 --
109 -- source object notes
110 If p_source_code = 'SR'
111 then
112 open c_note(p_source_id, p_source_code);
113 fetch c_note into l_tmp;
114 if c_note%found then
115 close c_note;
116 return true;
117 end if;
118 close c_note;
119 end if;
120 -- nothing requested or found
121 return false;
122 end task_has_notes;
123
124
125 -- =============================== --
126 -- public functions and procedures --
127 -- =============================== --
128
129
130 ------------------------------------------------------------------------
131 -- populate the planboard
132 ------------------------------------------------------------------------
133 PROCEDURE populate_planboard_table
134 ( p_start_date in date
135 , p_end_date in date
136 , p_resource_id in number default null
137 , p_resource_type in varchar2 default null
138 , x_pb_tbl out nocopy pb_tbl_type
139 )
140 IS
141 l_uom_hours varchar2(3);
142 l_rule_id number;
143 l_tz varchar2(3);
144 k integer;
145 m integer;
146 l_pr csf_planboard_tasks.pb_rec_type;
147 l_cell varchar2(100);
148 l_line varchar2(100);
149 lf varchar2(2) ;
150 l_incident_id number;
151 l_task_custom_color varchar2(1);
152 l_depend_flag varchar2(2);
153 l_notes_flag varchar2(2);
154
155
156 task_id jtf_number_table;
157 real_task_id jtf_number_table;
158 task_number jtf_varchar2_table_100;
159 task_type_id jtf_number_table;
160 trip_task_indicator jtf_number_table;
161 task_priority_id jtf_number_table;
162 source_object_type_code jtf_varchar2_table_100;
163 source_object_name jtf_varchar2_table_100;
164 source_object_id jtf_number_table;
165 planned_start_date jtf_date_table;
166 planned_end_date jtf_date_table;
167 scheduled_start_date jtf_date_table;
168 scheduled_end_date jtf_date_table;
169 task_confirmation_status jtf_varchar2_table_100;
170 parent_task_id jtf_number_table;
171 task_split_flag jtf_varchar2_table_100;
172 assignment_status_id jtf_number_table;
173 actual_start_date jtf_date_table;
174 actual_end_date jtf_date_table;
175 city jtf_varchar2_table_2000;
176 customer jtf_varchar2_table_400;
177 contract jtf_varchar2_table_400;
178 type_name jtf_varchar2_table_100;
179 assignment_status jtf_varchar2_table_100;
180 escalated jtf_number_table;
181 actual_effort jtf_number_table;
182 planned_effort jtf_number_table;
183 resource_id jtf_number_table;
184 resource_type jtf_varchar2_table_100;
185 resource_name jtf_varchar2_table_2000;
186 status_schedulable_flag jtf_varchar2_table_100;
187 type_schedulable_flag jtf_varchar2_table_100;
188 trip_id jtf_number_table;
189
190
191 l_prev_resource_id NUMBER;
192 l_prev_resource_type VARCHAR2(30);
193 l_type_name VARCHAR2(100);
194 l_assignment_status VARCHAR2(100);
195 l_real_task_cnt number; -- added for recalculate all trips
196 l_real_task_trip_cnt number; -- added (for recalculate trip,optimize trip and commit trip)
197 l_task_confirm_ctr number; -- added (for commit_trip)
198 l_dep_task_position number;
199 l_dep_trip_id number;
200 l_dep_trip_status number;
201 l_dep_trip_task_ind number;
202 l_dep_source_type varchar2(60);
203 l_escalated NUMBER;
204 l_departure VARCHAR2(100);
205 l_arrival VARCHAR2(100);
206
207 TYPE number_tbl_type IS TABLE OF NUMBER
208 INDEX BY VARCHAR2(200); --changed the index from binary_integer to varchar2
209 --for frontporting bug 5944863
210 --the index will now be resource_id||resource_type
211 --instead of just resource_id
212
213 l_res_id_map_tbl number_tbl_type;
214
215
216 --Newly added code for performance inmprovement
217 CURSOR C_virtual_tsk_names
218 IS
219 Select task_type_id,tt.name
220 from jtf_Task_types_tl tt
221 where task_type_id in (20,21)
222 and language=userenv('LANG');
223
224
225 -- for trip status
226 TYPE trip_rec IS RECORD(
227 object_capacity_id NUMBER,
228 status NUMBER
229 );
230
231 TYPE trip_tbl IS TABLE OF trip_rec
232 INDEX BY BINARY_INTEGER;
233
234 g_trip_tbl trip_tbl;
235
236 -- for access hours / after hours
237 TYPE access_rec IS RECORD(
238 task_id NUMBER,
239 accesshr_set VARCHAR2(1),
240 afterhr_set VARCHAR2(1)
241 );
242
243 TYPE access_tbl IS TABLE OF access_rec
244 INDEX BY BINARY_INTEGER;
245
246 g_access_tbl access_tbl;
247
248 -- for parts requirement
249 TYPE parts_rec IS RECORD(
250 task_id NUMBER
251 );
252
253 TYPE parts_tbl IS TABLE OF parts_rec
254 INDEX BY BINARY_INTEGER;
255
256 g_parts_tbl parts_tbl ;
257
258 v_restab csf_resource_tbl := csf_resource_tbl();
259
260 -- cursors
261 ----------
262 --dependency check
263 CURSOR c_depend_check(p_task_id number)
264 IS
265 SELECT 'Y'
266 FROM jtf_task_depends
267 WHERE p_task_id in (task_id,dependent_on_task_id);
268
269
270 --Cursor to fetch the resources
271 CURSOR c_res
272 IS
273 SELECT resource_name
274 , resource_id
275 , resource_type
276 FROM csf_selected_resources_v
277 ORDER BY upper(resource_name);
278
279 -- Cursor to fetch the tasks for the set of resources.
280 CURSOR c_task
281 IS
282 SELECT /*+ leading(res) use_nl(res a) cardinality(res 5) */
283 t.task_id
284 , decode(t.task_type_id, 20, 0, 21, 0, t.task_id) real_task_id
285 , t.task_number
286 , t.task_type_id
287 , decode(t.task_type_id, 20, 0, 21, 2, 1) trip_task_ind
288 , t.task_priority_id
289 , t.source_object_type_code
290 , t.source_object_name
291 , t.source_object_id
292 , t.planned_start_date
293 , t.planned_end_date
294 , t.scheduled_start_date
295 , t.scheduled_end_date
296 , t.task_confirmation_status
297 , t.parent_task_id
298 , t.task_split_flag
299 , a.assignment_status_id
300 , a.actual_start_date
301 , a.actual_end_date
302 , l.city
303 , NULL customer
304 , NULL contract
305 , null type_name
306 , ts1.name
307 , 0 escalated
308 , a.actual_effort
309 , t.planned_effort
310 , res.resource_id
311 , res.resource_type
312 , res.resource_name
313 , ts1.schedulable_flag
314 , tt.schedule_flag
315 , a.object_capacity_id
316 FROM ( SELECT resource_id,
317 resource_type,
318 resource_name
319 FROM Table(Cast(v_restab As csf_resource_tbl))
320 ) res
321 , jtf_task_assignments a
322 , jtf_tasks_b t
323 , jtf_task_statuses_vl ts1
324 , jtf_task_statuses_b ts2
325 , jtf_task_types_b tt
326 , hz_locations l
327 WHERE a.assignee_role = 'ASSIGNEE'
328 AND a.resource_id = res.resource_id
329 AND a.resource_type_code = res.resource_type
330 AND a.booking_end_date >= p_start_date
331 AND a.booking_start_date < p_end_date
332 AND a.booking_end_date >= a.booking_start_date
333 AND a.assignment_status_id = ts1.task_status_id
334 AND nvl(ts1.cancelled_flag,'N') <> 'Y'
335 AND t.task_id = a.task_id
336 -- AND t.scheduled_start_date is not null --commented for the bug 6729435
337 -- AND t.scheduled_end_date is not null
338 AND NVL(t.deleted_flag, 'N') <> 'Y'
339 AND t.task_status_id = ts2.task_status_id
340 AND nvl(ts2.cancelled_flag,'N') <> 'Y'
341 AND t.task_type_id = tt.task_type_id
342 AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
343 ORDER BY res.resource_name
344 , nvl(a.actual_start_date,t.scheduled_start_date)
345 , DECODE(t.task_type_id, 20, 1, 21, 3, 2)
346 , a.task_assignment_id;
347
348
349 procedure set_task_custom_color( p_task_id in number
350 , p_type_id in number
351 , p_priority_id in number
352 , p_status_id in number
353 , p_item out nocopy varchar2)
354 is
355 l_color varchar2(60) := null;
356 begin
357 if g_use_custom_chromatics
358 then
359 if l_task_custom_color ='Y'
360 then
361 if l_rule_id is not null
362 then
363 begin
364 select background_col_rgb
365 into p_item
366 from jtf_task_custom_colors
367 where rule_id=l_rule_id;
368 exception
369 when no_data_found then
370 p_item := jtf_task_custom_colors_pub.get_task_rgb_bgcolor(
371 p_task_id,
372 p_type_id,
373 p_priority_id,
374 p_status_id);
375 end ;
376 else
377 p_item := jtf_task_custom_colors_pub.get_task_rgb_bgcolor(
378 p_task_id,
379 p_type_id,
380 p_priority_id,
381 p_status_id);
382 end if;
383 else
384 p_item := jtf_task_custom_colors_pub.get_task_rgb_bgcolor(
385 p_task_id,
386 p_type_id,
387 p_priority_id,
388 p_status_id);
389
390 end if;
391 end if;
392 end set_task_custom_color;
393
394 ---Newly added code for performance improvement
395 PROCEDURE get_trip_status IS
396 CURSOR c_trip_status IS
397 SELECT object_capacity_id,status
398 FROM cac_sr_object_capacity
399 WHERE object_capacity_id in (select DISTINCT column_value
400 FROM TABLE(CAST(trip_id AS jtf_NUMBER_table))
401 where column_value <> 0);
402 i BINARY_INTEGER := 0;
403 BEGIN
404 IF g_trip_tbl.COUNT = 0
405 THEN
406 FOR rec IN c_trip_status
407 LOOP
408 i := i + 1;
409 g_trip_tbl(i).object_capacity_id := rec.object_capacity_id;
410 g_trip_tbl(i).status := rec.status;
411 END LOOP;
412 END IF;
413 END get_trip_status;
414
415 ---Newly added code for performance improvement
416 PROCEDURE get_access_status IS
417 CURSOR c_access_status IS
418 SELECT task_id
419 , NVL(accesshour_required, 'N') access_flag
420 , NVL(after_hours_flag, 'N') after_flag
421 FROM csf_access_hours_b
422 WHERE task_id in (select DISTINCT column_value
423 FROM TABLE(CAST(real_task_id AS jtf_NUMBER_table))
424 where column_value <> 0);
425 i BINARY_INTEGER := 0;
426 BEGIN
427 IF g_access_tbl.COUNT = 0
428 THEN
429 FOR rec IN c_access_status
430 LOOP
431 i := i + 1;
432 g_access_tbl(i).task_id := rec.task_id;
436 END IF;
433 g_access_tbl(i).accesshr_set := rec.access_flag;
434 g_access_tbl(i).afterhr_set := rec.after_flag;
435 END LOOP;
437 END get_access_status;
438
439 ---Newly added code for performance improvement
440 PROCEDURE get_parts_status IS
441 CURSOR c_parts_status IS
442 SELECT task_id
443 FROM csp_requirement_headers
444 WHERE task_id in (select DISTINCT column_value
445 FROM TABLE(CAST(real_task_id AS jtf_NUMBER_table))
446 where column_value <> 0);
447 i BINARY_INTEGER := 0;
448 BEGIN
449 IF g_parts_tbl.COUNT = 0
450 THEN
451 FOR rec IN c_parts_status
452 LOOP
453 i := i + 1;
454 g_parts_tbl(i).task_id := rec.task_id;
455 END LOOP;
456 END IF;
457 END get_parts_status;
458
459 FUNCTION do_match(
460 p_id IN NUMBER,
461 p_match_type IN VARCHAR2
462 )
463 RETURN VARCHAR2 IS
464 BEGIN
465 IF p_match_type = 'TRIP'
466 THEN
467 IF g_trip_tbl.COUNT > 0
468 THEN
469 FOR i IN 1 .. g_trip_tbl.COUNT
470 LOOP
471 IF g_trip_tbl(i).object_capacity_id = p_id
472 THEN
473 RETURN g_trip_tbl(i).status;
474 END IF;
475 END LOOP;
476 END IF;
477 RETURN NULL;
478 ELSIF p_match_type = 'ACCESS'
479 THEN
480 IF g_access_tbl.COUNT > 0
481 THEN
482 FOR i IN 1 .. g_access_tbl.COUNT
483 LOOP
484 IF g_access_tbl(i).task_id = p_id
485 THEN
486 IF g_access_tbl(i).accesshr_set = 'Y'
487 THEN
488 RETURN 'A ';
489 ELSIF g_access_tbl(i).afterhr_set = 'Y'
490 THEN
491 RETURN 'F ';
492 END IF;
493 END IF;
494 END LOOP;
495 END IF;
496 RETURN ' ';
497 ELSIF p_match_type = 'PARTS'
498 THEN
499 IF g_parts_tbl.COUNT > 0
500 THEN
501 FOR i IN 1 .. g_parts_tbl.COUNT
502 LOOP
503 IF g_parts_tbl(i).task_id = p_id
504 THEN
505 RETURN 'S ';
506 END IF;
507 END LOOP;
508 END IF;
509 RETURN ' ';
510 END IF;
511 RETURN NULL;
512 END do_match;
513 begin
514
515 l_uom_hours := fnd_profile.value('CSF_UOM_HOURS');
516 l_rule_id := fnd_profile.value_specific('CSF_TASK_SIGNAL_COLOR',fnd_global.user_id);
517 lf := fnd_global.local_chr(10);
518 l_tz := fnd_profile.value('CSF_DEFAULT_TIMEZONE_DC');
519 l_real_task_cnt := 0;
520 l_task_custom_color := 'N';
521 k :=0;
522 m :=0;
523 task_id := jtf_number_table();
524 real_task_id := jtf_number_table();
525 task_number := jtf_varchar2_table_100();
526 task_type_id := jtf_number_table();
527 trip_task_indicator := jtf_number_table();
528 task_priority_id := jtf_number_table();
529 source_object_type_code := jtf_varchar2_table_100();
530 source_object_name := jtf_varchar2_table_100();
531 source_object_id := jtf_number_table();
532 planned_start_date := jtf_date_table();
533 planned_end_date := jtf_date_table();
534 scheduled_start_date := jtf_date_table();
535 scheduled_end_date := jtf_date_table();
536 task_confirmation_status := jtf_varchar2_table_100();
537 parent_task_id := jtf_number_table();
538 task_split_flag := jtf_varchar2_table_100();
539 assignment_status_id := jtf_number_table();
540 actual_start_date := jtf_date_table();
541 actual_end_date := jtf_date_table();
542 city := jtf_varchar2_table_2000();
543 customer := jtf_varchar2_table_400();
544 contract := jtf_varchar2_table_400();
545 type_name := jtf_varchar2_table_100();
546 assignment_status := jtf_varchar2_table_100();
547 escalated := jtf_number_table();
548 actual_effort := jtf_number_table();
549 planned_effort := jtf_number_table();
550 resource_id := jtf_number_table();
551 resource_type := jtf_varchar2_table_100();
552 resource_name := jtf_varchar2_table_2000();
553 status_schedulable_flag := jtf_varchar2_table_100();
554 type_schedulable_flag := jtf_varchar2_table_100();
555 trip_id := jtf_number_table();
556
557
558 OPEN c_res;
559 FETCH c_res
560 BULK COLLECT INTO
561 resource_name
562 , resource_id
563 , resource_type;
564
565 CLOSE c_res;
566
567 FOR i IN 1 .. resource_id.COUNT LOOP
568 k := k + 1;
569 v_restab.extend;
570 v_restab(v_restab.Last) := csf_resource(null,null,null,resource_id(i), resource_type(i), resource_name(i),null,null);
571 l_pr.resource_id := resource_id(i);
572 l_pr.resource_type := resource_type(i);
573 l_pr.resource_name := resource_name(i);
577 resource_id := jtf_number_table();
574 x_pb_tbl(k) := l_pr;
575 l_res_id_map_tbl(l_pr.resource_id||l_pr.resource_type) := k;
576 END LOOP;
578 resource_type := jtf_varchar2_table_100();
579 resource_name := jtf_varchar2_table_2000();
580
581 OPEN c_task;
582 FETCH c_task
583 BULK COLLECT INTO task_id
584 , real_task_id
585 , task_number
586 , task_type_id
587 , trip_task_indicator
588 , task_priority_id
589 , source_object_type_code
590 , source_object_name
591 , source_object_id
592 , planned_start_date
593 , planned_end_date
594 , scheduled_start_date
595 , scheduled_end_date
596 , task_confirmation_status
597 , parent_task_id
598 , task_split_flag
599 , assignment_status_id
600 , actual_start_date
601 , actual_end_date
602 , city
603 , customer
604 , contract
605 , type_name
606 , assignment_status
607 , escalated
608 , actual_effort
609 , planned_effort
610 , resource_id
611 , resource_type
612 , resource_name
613 , status_schedulable_flag
614 , type_schedulable_flag
615 , trip_id;
616
617 CLOSE c_task;
618
619
620 --NEWLY ADDED CURSOR FOR GETTING VIRTUAL TASKS NAMES
621 for i in c_virtual_tsk_names
622 loop
623 if i.task_type_id = 20
624 then
625 l_departure := i.name;
626 elsif i.task_type_id = 21
627 then
628 l_arrival := i.name;
629 end if;
630 end loop;
631
632 get_access_status;
633 get_trip_status;
634 get_parts_status;
635 --END FOR ADDITION
636
637
638 -- resources and tasks loop
639 -----------------------------
640 k := null;
641 FOR i IN 1 .. task_id.COUNT LOOP
642 IF l_prev_resource_id IS NULL THEN
643 k := l_res_id_map_tbl(resource_id(i)||resource_type(i));
644 l_pr := x_pb_tbl(k);
645 l_dep_task_position :=0;
646 l_dep_trip_id :=0;
647 l_dep_trip_status :=0;
648 l_dep_source_type :=null;
649 l_real_task_trip_cnt :=0;
650 l_task_confirm_ctr := 0;
651 m := 1;
652 ELSIF l_prev_resource_id||l_prev_resource_type = resource_id(i)||resource_type(i) THEN
653 m := m + 1;
654 ELSE
655 l_dep_task_position :=0;
656 l_dep_trip_id :=0;
657 l_dep_trip_status :=0;
658 l_dep_source_type :=null;
659 l_real_task_trip_cnt :=0;
660 l_task_confirm_ctr := 0;
661 m := 1;
662 l_pr.actual_indicator := RPAD(l_pr.actual_indicator, 15, '0');
663 x_pb_tbl(k) := l_pr;
664 k := l_res_id_map_tbl(resource_id(i)||resource_type(i));
665 l_pr := x_pb_tbl(k);
666 END IF;
667
668 IF m <= 15 THEN
669 -- for SR tasks get the customer and contract name
670 --------------------------------------------------
671 IF source_object_type_code(i) = 'SR' THEN
672 l_incident_id := source_object_id(i);
673 get_customer(l_incident_id, customer(i), contract(i));
674 ELSE
675 l_incident_id := NULL;
676 END IF;
677
678 l_task_custom_color := 'N';
679
680 IF task_type_id(i) NOT IN(20, 21) THEN
681 IF actual_start_date(i) IS NOT NULL THEN
682 IF actual_end_date(i) IS NOT NULL THEN
683 IF actual_end_date(i) = actual_start_date(i) THEN
684 --set flag for color code
685 l_task_custom_color := 'Y';
686 END IF; --end if for actual_end_date=actual_start_date
687 ELSE
688 IF NVL(actual_effort(i), 0) = 0 THEN
689 IF NVL(planned_effort(i), 0) = 0 THEN
690 l_task_custom_color := 'Y';
691 END IF;
692 END IF;
693 -- End of the code added for the change in mini-design
694 END IF; --end if for actual_end_date is not null
695 ELSE --for actual start date is null
696 IF scheduled_end_date(i) IS NOT NULL THEN
697 IF scheduled_end_date(i) = scheduled_start_date(i) THEN
698 --set flag for color code
699 l_task_custom_color := 'Y';
700 END IF; --end if for scheduled_end_date=scheduled_start_date
701 ELSE
702 --set flag for color code
703 l_task_custom_color := 'Y';
704 END IF; --end if scheduled end_date is not null
705 END IF; --end if for actual_start_date is not null
706 END IF; --end if task_type_id
707
708 -- condition for departure and arrival task
709 IF task_type_id(i) IN(20, 21) THEN
710 IF scheduled_start_date(i) IS NOT NULL AND scheduled_end_date(i) IS NOT NULL THEN
714 l_task_custom_color := 'Y';
711 IF scheduled_start_date(i) <> scheduled_end_date(i) THEN
712 IF scheduled_end_date(i) > scheduled_start_date(i) THEN
713 --set the color flag
715 END IF; --if scheduled end_date > than start_date
716 END IF; --end if for scheduled_end_date is not equal to start_date
717 END IF; --end if for scheduled_start and end_dates are not null
718 END IF; --end if for task_type_id
719
720 -------------------
721 -- format task cell
722 -------------------
723
724 -- row #1
725 ---------
726 -- escalated
727 IF csf_tasks_pub.is_task_escalated(task_id(i)) THEN
728 l_cell := '!! ';
729 ELSE
730 l_cell := NULL;
731 END IF;
732
733 IF task_type_id(i) IN(20, 21) THEN
734 IF task_type_id(i) = 20 THEN
735 l_type_name := l_departure;
736 l_cell := SUBSTRB(l_cell || l_type_name, 1, 25);
737 ELSIF task_type_id(i) = 21 THEN
738 l_type_name := l_arrival;
739 l_cell := SUBSTRB(l_cell || l_type_name, 1, 25);
740 END IF;
741 IF task_type_id(i) = 20 THEN
742 l_real_task_trip_cnt :=0;
743 l_task_confirm_ctr := 0;
744 l_dep_task_position := m;
745 l_dep_trip_id := trip_id(i);
746 l_dep_trip_status := do_match(trip_id(i),'TRIP');
747 l_dep_trip_task_ind :=trip_task_indicator(i);
748 l_dep_source_type := source_object_type_code(i);
749 END IF;
750 ELSE
751 -- a real task which has status schedulable Y and type schedulable Y
752 IF status_schedulable_flag(i) ='Y' AND type_schedulable_flag(i) = 'Y'
753 AND source_object_type_code(i) ='SR'
754 THEN
755 l_real_task_cnt := l_real_task_cnt + 1;
756 IF trip_id(i) = l_dep_trip_id
757 THEN
758 l_real_task_trip_cnt := l_real_task_trip_cnt + 1;
759 IF nvl(task_confirmation_status(i),'N') in ('N','C') THEN
760 l_task_confirm_ctr := l_task_confirm_ctr + 1;
761 END IF;
762 END IF;
763 END IF;
764 -- display task number instead of SR <nr> now
765 l_cell := SUBSTRB(l_cell || task_number(i), 1, 25);
766 END IF;
767
768 -- row #2
769 ---------
770 l_cell := l_cell || lf || SUBSTRB(customer(i), 1, 25);
771
772 -- row #3
773 ---------
774 IF scheduled_start_date(i) NOT BETWEEN p_start_date AND p_end_date THEN
775 l_line := '**:** ';
776 ELSE
777 -- this if is added to check if actual_start_date is not null then display actual_start_date time
778 --or else display scheduled_start_date
779 IF l_tz = 'UTZ' THEN
780 IF actual_start_date(i) IS NOT NULL THEN
781 l_line :='('|| csf_timezones_pvt.date_to_client_tz_chartime(actual_start_date(i), 'hh24:mi')||') ';
782 ELSE
783 l_line := csf_timezones_pvt.date_to_client_tz_chartime(scheduled_start_date(i), 'hh24:mi')|| ' ';
784 END IF;
785 ELSE
786 IF actual_start_date(i) IS NOT NULL THEN
787 l_line := '(' || to_char(actual_start_date(i),'hh24:mi')||') ';
788 ELSE
789 l_line := to_char(scheduled_start_date(i),'hh24:mi') || ' ';
790 END IF;
791 END IF;
792 END IF;
793
794 IF actual_start_date(i) IS NULL THEN
795 l_pr.actual_indicator := l_pr.actual_indicator || '0';
796 ELSE
797 l_pr.actual_indicator := l_pr.actual_indicator || '1';
798 END IF;
799
800 -- chosen to suppress the assignment status for dep/arr
801 IF task_type_id(i) NOT IN(20, 21) THEN
802 l_assignment_status := assignment_status(i);
803 l_line := l_line || ' ' || l_assignment_status;
804 END IF;
805
806 l_cell := l_cell || lf || SUBSTRB(l_line, 1, 25);
807
808
809 -- row #4
810 ---------
811 IF contract(i) IS NOT NULL THEN
812 l_line := SUBSTRB(contract(i), 1, 12) || ' ';
813 ELSE
814 l_line := NULL;
815 END IF;
816
817 IF city(i) IS NOT NULL THEN
818 l_line := l_line || city(i);
819 END IF;
820
821
822 IF l_line is not null THEN
823 l_cell := l_cell||lf||SUBSTRB(l_line,1,25);
824 END IF;
825
826 --row # 5 added for inspection/R12
827 if source_object_type_code(i) = 'SR' then
828 l_depend_flag :=null;
829 l_notes_flag :=null;
830
831 --access hours/after hours check
832 /* If nvl(access_hours(i),'N')='Y' then
833 l_line:='A ';
834 elsif nvl(after_hours(i),'N')='Y' then
835 l_line:='F ';
836 else
837 l_line:=' ';
838 end if; */
839
840 l_line := do_match(real_task_id(i),'ACCESS');
844 elsif nvl(task_confirmation_status(i),'N')='R' then
841 -- Customer Confirmation check
842 If nvl(task_confirmation_status(i),'N')='C' then
843 l_line:=l_line||'V ';
845 l_line:=l_line||'C ';
846 else
847 l_line:=l_line||' ';
848 end if;
849
850 -- Parts check
851 /*If nvl(parts_required(i),'N')='Y' then
852 l_line:=l_line||'S ';
853 else
854 l_line:=l_line||' ';
855 end if;*/
856 l_line := l_line || do_match(real_task_id(i),'PARTS');
857
858 -- Parent/child check
859 If nvl(task_split_flag(i),'N')='D' and parent_task_id(i) is not null then
860 l_line:=l_line||'D ';
861 elsif nvl(task_split_flag(i),'N')='M' and parent_task_id(i) is null then
862 l_line:=l_line||'M ';
863 else
864 l_line:=l_line||' ';
865 end if;
866 -- task dependencies check
867 Open c_depend_check(task_id(i));
868 Fetch c_depend_check into l_depend_flag;
869 close c_depend_check;
870
871 If nvl(l_depend_flag,'N')='Y' then
872 l_line:=l_line||'R ';
873 else
874 l_line:=l_line||' ';
875 end if;
876
877 --notes check
878 if task_has_notes(task_id(i), source_object_type_code(i),source_object_id(i))
879 then
880 l_line:=l_line||'N ';
881 else
882 l_line:=l_line||' ';
883 end if;
884
885 if l_line is not null then
886 l_cell := l_cell||lf||l_line;
887 end if;
888 end if;
889 --row # 5 ends here
890
891 -------------------------------------------
892 -- put queried record into planboard record
893 -------------------------------------------
894
895 IF m = 1 THEN
896 l_pr.task_id_1 := task_id(i);
897 l_pr.task_cell_1 := l_cell;
898 l_pr.other_info_1 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
899 set_task_custom_color(
900 task_id(i)
901 , task_type_id(i)
902 , task_priority_id(i)
903 , assignment_status_id(i)
904 , l_pr.rgb_color_1
905 );
906 ELSIF m = 2 THEN
907 l_pr.task_id_2 := task_id(i);
908 l_pr.task_cell_2 := l_cell;
909 l_pr.other_info_2 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
910 set_task_custom_color(
911 task_id(i)
912 , task_type_id(i)
913 , task_priority_id(i)
914 , assignment_status_id(i)
915 , l_pr.rgb_color_2
916 );
917 ELSIF m = 3 THEN
918 l_pr.task_id_3 := task_id(i);
919 l_pr.task_cell_3 := l_cell;
920 l_pr.other_info_3 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
921 set_task_custom_color(
922 task_id(i)
923 , task_type_id(i)
924 , task_priority_id(i)
925 , assignment_status_id(i)
926 , l_pr.rgb_color_3
927 );
928 ELSIF m = 4 THEN
929 l_pr.task_id_4 := task_id(i);
930 l_pr.task_cell_4 := l_cell;
931 l_pr.other_info_4 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
932 set_task_custom_color(
933 task_id(i)
934 , task_type_id(i)
935 , task_priority_id(i)
936 , assignment_status_id(i)
937 , l_pr.rgb_color_4
938 );
939 ELSIF m = 5 THEN
940 l_pr.task_id_5 := task_id(i);
941 l_pr.task_cell_5 := l_cell;
942 l_pr.other_info_5 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
943 set_task_custom_color(
944 task_id(i)
945 , task_type_id(i)
946 , task_priority_id(i)
947 , assignment_status_id(i)
948 , l_pr.rgb_color_5
949 );
950 ELSIF m = 6 THEN
951 l_pr.task_id_6 := task_id(i);
952 l_pr.task_cell_6 := l_cell;
953 l_pr.other_info_6 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
954 set_task_custom_color(
955 task_id(i)
956 , task_type_id(i)
957 , task_priority_id(i)
958 , assignment_status_id(i)
959 , l_pr.rgb_color_6
960 );
964 l_pr.other_info_7 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
961 ELSIF m = 7 THEN
962 l_pr.task_id_7 := task_id(i);
963 l_pr.task_cell_7 := l_cell;
965 set_task_custom_color(
966 task_id(i)
967 , task_type_id(i)
968 , task_priority_id(i)
969 , assignment_status_id(i)
970 , l_pr.rgb_color_7
971 );
972 ELSIF m = 8 THEN
973 l_pr.task_id_8 := task_id(i);
974 l_pr.task_cell_8 := l_cell;
975 l_pr.other_info_8 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
976 set_task_custom_color(
977 task_id(i)
978 , task_type_id(i)
979 , task_priority_id(i)
980 , assignment_status_id(i)
981 , l_pr.rgb_color_8
982 );
983 ELSIF m = 9 THEN
984 l_pr.task_id_9 := task_id(i);
985 l_pr.task_cell_9 := l_cell;
986 l_pr.other_info_9 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
987 set_task_custom_color(
988 task_id(i)
989 , task_type_id(i)
990 , task_priority_id(i)
991 , assignment_status_id(i)
992 , l_pr.rgb_color_9
993 );
994 ELSIF m = 10 THEN
995 l_pr.task_id_10 := task_id(i);
996 l_pr.task_cell_10 := l_cell;
997 l_pr.other_info_10 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
998 set_task_custom_color(
999 task_id(i)
1000 , task_type_id(i)
1001 , task_priority_id(i)
1002 , assignment_status_id(i)
1003 , l_pr.rgb_color_10
1004 );
1005 ELSIF m = 11 THEN
1006 l_pr.task_id_11 := task_id(i);
1007 l_pr.task_cell_11 := l_cell;
1008 l_pr.other_info_11 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
1009 set_task_custom_color(
1010 task_id(i)
1011 , task_type_id(i)
1012 , task_priority_id(i)
1013 , assignment_status_id(i)
1014 , l_pr.rgb_color_11
1015 );
1016 ELSIF m = 12 THEN
1017 l_pr.task_id_12 := task_id(i);
1018 l_pr.task_cell_12 := l_cell;
1019 l_pr.other_info_12 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
1020 set_task_custom_color(
1021 task_id(i)
1022 , task_type_id(i)
1023 , task_priority_id(i)
1024 , assignment_status_id(i)
1025 , l_pr.rgb_color_12
1026 );
1027 ELSIF m = 13 THEN
1028 l_pr.task_id_13 := task_id(i);
1029 l_pr.task_cell_13 := l_cell;
1030 l_pr.other_info_13 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
1031 set_task_custom_color(
1032 task_id(i)
1033 , task_type_id(i)
1034 , task_priority_id(i)
1035 , assignment_status_id(i)
1036 , l_pr.rgb_color_13
1037 );
1038 ELSIF m = 14 THEN
1039 l_pr.task_id_14 := task_id(i);
1040 l_pr.task_cell_14 := l_cell;
1041 l_pr.other_info_14 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
1042 set_task_custom_color(
1043 task_id(i)
1044 , task_type_id(i)
1045 , task_priority_id(i)
1046 , assignment_status_id(i)
1047 , l_pr.rgb_color_14
1048 );
1049 ELSIF m = 15 THEN
1050 l_pr.task_id_15 := task_id(i);
1051 l_pr.task_cell_15 := l_cell;
1052 l_pr.other_info_15 := nvl(trip_id(i),-1)||'!'||nvl(do_match(trip_id(i),'TRIP'),-1) || '!'||l_real_task_trip_cnt || '!' || trip_task_indicator(i) || '!' || l_task_confirm_ctr || '!' || source_object_type_code(i);
1053 set_task_custom_color(
1054 task_id(i)
1055 , task_type_id(i)
1056 , task_priority_id(i)
1057 , assignment_status_id(i)
1058 , l_pr.rgb_color_15
1059 );
1060 END IF;
1061
1062 IF l_dep_task_position > 0 and l_real_task_trip_cnt > 0 THEN
1063 IF l_dep_task_position = 1 THEN
1064 l_pr.other_info_1:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1065 ELSIF l_dep_task_position = 2 THEN
1066 l_pr.other_info_2:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1067 ELSIF l_dep_task_position = 3 THEN
1068 l_pr.other_info_3:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1069 ELSIF l_dep_task_position = 4 THEN
1070 l_pr.other_info_4:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1071 ELSIF l_dep_task_position = 5 THEN
1072 l_pr.other_info_5:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1073 ELSIF l_dep_task_position = 6 THEN
1074 l_pr.other_info_6:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1075 ELSIF l_dep_task_position = 7 THEN
1076 l_pr.other_info_7:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1077 ELSIF l_dep_task_position = 8 THEN
1078 l_pr.other_info_8:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1079 ELSIF l_dep_task_position = 9 THEN
1080 l_pr.other_info_9:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1081 ELSIF l_dep_task_position = 10 THEN
1082 l_pr.other_info_10:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1083 ELSIF l_dep_task_position = 11 THEN
1084 l_pr.other_info_11:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1085 ELSIF l_dep_task_position = 12 THEN
1086 l_pr.other_info_12:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1087 ELSIF l_dep_task_position = 13 THEN
1088 l_pr.other_info_13:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1089 ELSIF l_dep_task_position = 14 THEN
1090 l_pr.other_info_14:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1091 ELSIF l_dep_task_position = 15 THEN
1092 l_pr.other_info_15:= nvl(l_dep_trip_id,-1)||'!'||nvl(l_dep_trip_status,-1) || '!'||l_real_task_trip_cnt || '!'|| l_dep_trip_task_ind || '!' || l_task_confirm_ctr || '!' || l_dep_source_type;
1093 END IF;
1094 END IF;
1095 END IF;
1096 l_prev_resource_id := resource_id(i);
1097 l_prev_resource_type := resource_type(i);
1098 END LOOP;
1099
1100 if k is not null then
1101 l_pr.actual_indicator := RPAD(l_pr.actual_indicator, 15, '0');
1102 x_pb_tbl(k) := l_pr;
1103 end if;
1104 -- update the indicator in record 1 with the "real" task count
1105 if x_pb_tbl.COUNT > 0 then
1106 x_pb_tbl(x_pb_tbl.FIRST).real_task_cnt := l_real_task_cnt;
1107 end if;
1108 EXCEPTION
1109 -- there were no resources
1110 WHEN COLLECTION_IS_NULL THEN
1111 NULL;
1112 END populate_planboard_table;
1113 BEGIN
1114 -- getting the indicator if custom color coding will be used.
1115 g_use_custom_chromatics := fnd_profile.value('CSF_USE_CUSTOM_CHROMATICS') = 'Y' ;
1116 END csf_planboard_tasks;