[Home] [Help]
PACKAGE BODY: APPS.CSF_PLANBOARD_TASKS
Source
1 PACKAGE BODY csf_planboard_tasks AS
2 /* $Header: CSFCTPLB.pls 120.36.12020000.3 2012/10/30 11:49:17 aditysin 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 , p_shift_reg in varchar2 default null
139 , p_shift_std in varchar2 default null
140 , x_pb_tbl out nocopy pb_tbl_type
141 )
142 IS
143 l_uom_hours varchar2(3);
144 l_rule_id number;
145 l_tz varchar2(3);
146 k integer;
147 m integer;
148 l_pr csf_planboard_tasks.pb_rec_type;
149 l_cell varchar2(150);
150 l_line varchar2(100);
151 lf varchar2(2) ;
152 l_incident_id number;
153 l_task_custom_color varchar2(1);
154 l_depend_flag varchar2(2);
155 l_notes_flag varchar2(2);
156
157
158 task_id jtf_number_table;
159 real_task_id jtf_number_table;
160 task_number jtf_varchar2_table_100;
161 task_type_id jtf_number_table;
162 trip_task_indicator jtf_number_table;
163 task_priority_id jtf_number_table;
164 source_object_type_code jtf_varchar2_table_100;
165 source_object_name jtf_varchar2_table_100;
166 source_object_id jtf_number_table;
167 planned_start_date jtf_date_table;
168 planned_end_date jtf_date_table;
169 scheduled_start_date jtf_date_table;
170 scheduled_end_date jtf_date_table;
171 task_confirmation_status jtf_varchar2_table_100;
172 parent_task_id jtf_number_table;
173 task_split_flag jtf_varchar2_table_100;
174 assignment_status_id jtf_number_table;
175 actual_start_date jtf_date_table;
176 actual_end_date jtf_date_table;
177 city jtf_varchar2_table_2000;
178 customer jtf_varchar2_table_400;
179 contract jtf_varchar2_table_400;
180 type_name jtf_varchar2_table_100;
181 assignment_status jtf_varchar2_table_100;
182 escalated jtf_number_table;
183 actual_effort jtf_number_table;
184 planned_effort jtf_number_table;
185 resource_id jtf_number_table;
186 resource_type jtf_varchar2_table_100;
187 resource_name jtf_varchar2_table_2000;
188 status_schedulable_flag jtf_varchar2_table_100;
189 type_schedulable_flag jtf_varchar2_table_100;
190 trip_id jtf_number_table;
191 l_avail_type jtf_varchar2_table_100;
192 l_assign_id jtf_number_table;
193
194 l_prev_resource_id NUMBER;
195 l_prev_resource_type VARCHAR2(30);
196 l_type_name VARCHAR2(100);
197 l_assignment_status VARCHAR2(100);
198 l_real_task_cnt number; -- added for recalculate all trips
199 l_real_task_trip_cnt number; -- added (for recalculate trip,optimize trip and commit trip)
200 l_task_confirm_ctr number; -- added (for commit_trip)
201 l_dep_task_position number;
202 l_dep_trip_id number;
203 l_dep_trip_status number;
204 l_dep_trip_task_ind number;
205 l_dep_source_type varchar2(60);
206 l_escalated NUMBER;
207 l_departure VARCHAR2(100);
208 l_arrival VARCHAR2(100);
209 l_shift_type VARCHAR2(50);
210 TYPE number_tbl_type IS TABLE OF NUMBER
211 INDEX BY VARCHAR2(200); --changed the index from binary_integer to varchar2
212 --for frontporting bug 5944863
213 --the index will now be resource_id||resource_type
214 --instead of just resource_id
215
216 l_res_id_map_tbl number_tbl_type;
217
218
219 --Newly added code for performance inmprovement
220 CURSOR C_virtual_tsk_names
221 IS
222 Select task_type_id,tt.name
223 from jtf_Task_types_tl tt
224 where task_type_id in (20,21)
225 and language=userenv('LANG');
226
227
228 -- for trip status
229 TYPE trip_rec IS RECORD(
230 object_capacity_id NUMBER,
231 status NUMBER
232 );
233
234 TYPE trip_tbl IS TABLE OF trip_rec
235 INDEX BY BINARY_INTEGER;
236
237 g_trip_tbl trip_tbl;
238
239 -- for access hours / after hours
240 TYPE access_rec IS RECORD(
241 task_id NUMBER,
242 accesshr_set VARCHAR2(1),
243 afterhr_set VARCHAR2(1)
244 );
245
246 TYPE access_tbl IS TABLE OF access_rec
247 INDEX BY BINARY_INTEGER;
248
249 g_access_tbl access_tbl;
250
251 -- for parts requirement
252 TYPE parts_rec IS RECORD(
253 task_id NUMBER
254 );
255
256 TYPE parts_tbl IS TABLE OF parts_rec
257 INDEX BY BINARY_INTEGER;
258
259 g_parts_tbl parts_tbl ;
260
261 v_restab csf_resource_tbl := csf_resource_tbl();
262
263 -- cursors
264 ----------
265 --dependency check
266 CURSOR c_depend_check(p_task_id number)
267 IS
268 SELECT 'Y'
269 FROM jtf_task_depends
270 WHERE p_task_id in (task_id,dependent_on_task_id);
271
272
273 -- cursor to fetch resources
274 CURSOR c_res
275 IS
276 SELECT RESOURCE_NAME,
277 RESOURCE_ID,
278 RESOURCE_TYPE
279 FROM (
280 SELECT RESOURCE_NAME,
281 RESOURCE_ID,
282 RESOURCE_TYPE
283 FROM CSF_SELECTED_RESOURCES_V
284 MINUS
285 SELECT DISTINCT
286 A.RESOURCE_NAME ,
287 A.RESOURCE_ID ,
288 A.RESOURCE_TYPE
289 FROM CSF_SELECTED_RESOURCES_V A,
290 JTF_RS_DEFRESROLES_VL B,
291 JTF_RS_ALL_RESOURCES_VL C,
292 JTF_RS_ROLES_B D
293 WHERE B.ROLE_RESOURCE_ID=A.RESOURCE_ID
294 AND C.RESOURCE_ID = B.ROLE_RESOURCE_ID
295 AND C.RESOURCE_TYPE =A.RESOURCE_TYPE
296 AND D.ROLE_ID = B.ROLE_ID
297 AND B.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
298 AND NVL( B.DELETE_FLAG, 'N') = 'N'
299 AND (SYSDATE >= TRUNC (B.RES_RL_START_DATE) OR B.RES_RL_START_DATE IS NULL)
300 AND (SYSDATE <= TRUNC (B.RES_RL_END_DATE) + 1 OR B.RES_RL_END_DATE IS NULL)
301 AND ROLE_CODE IN ( 'CSF_THIRD_PARTY_SERVICE_PROVID', 'CSF_THIRD_PARTY_ADMINISTRATOR')
302 )
303 ORDER BY UPPER (RESOURCE_NAME);
304
305 -- cursor to fetch resources having active 'Field Service Representative' role
306 CURSOR c_res_technician
307 IS
308 SELECT RESOURCE_NAME,
309 RESOURCE_ID,
310 RESOURCE_TYPE
311 FROM(
312 SELECT DISTINCT
313 RES.RESOURCE_NAME,
314 RES.RESOURCE_ID,
315 RES.RESOURCE_TYPE
316 FROM CSF_SELECTED_RESOURCES_V RES ,
317 JTF_RS_ROLE_RELATIONS RR,
318 JTF_RS_ROLES_B RL ,
319 FND_LOOKUPS LKP
320 WHERE RR.ROLE_ID = RL.ROLE_ID
321 AND RL.ROLE_TYPE_CODE = LKP.LOOKUP_CODE
322 AND LKP.LOOKUP_TYPE = 'JTF_RS_ROLE_TYPE'
323 AND RES.RESOURCE_ID = RR.ROLE_RESOURCE_ID
324 AND RL.ROLE_TYPE_CODE = 'CSF_REPRESENTATIVE'
325 AND (SYSDATE >= TRUNC (RR.start_date_active) OR RR.start_date_active IS NULL)
326 AND (SYSDATE <= TRUNC (RR.end_date_active) + 1 OR RR.end_date_active IS NULL)
327 AND NVL(RR.DELETE_FLAG, 'N') = 'N'
328 MINUS
329 SELECT DISTINCT
330 A.RESOURCE_NAME,
331 A.RESOURCE_ID ,
332 A.RESOURCE_TYPE
333 FROM CSF_SELECTED_RESOURCES_V A,
334 JTF_RS_ROLE_RELATIONS RR,
335 JTF_RS_ROLES_B RL ,
336 FND_LOOKUPS LKP
337 WHERE RR.ROLE_RESOURCE_ID = A.RESOURCE_ID
338 AND RL.ROLE_ID = RR.ROLE_ID
339 AND LKP.LOOKUP_CODE = RL.ROLE_TYPE_CODE
340 AND LKP.LOOKUP_TYPE = 'JTF_RS_ROLE_TYPE'
341 AND RL.ROLE_CODE IN ( 'CSF_THIRD_PARTY_SERVICE_PROVID', 'CSF_THIRD_PARTY_ADMINISTRATOR')
342 AND RL.ROLE_TYPE_CODE = 'CSF_THIRD_PARTY'
343 AND (SYSDATE >= TRUNC (RR.START_DATE_ACTIVE) OR RR.START_DATE_ACTIVE IS NULL)
344 AND (SYSDATE <= TRUNC (RR.END_DATE_ACTIVE) + 1 OR RR.END_DATE_ACTIVE IS NULL)
345 AND NVL( RR.DELETE_FLAG, 'N') = 'N'
346 )
347 ORDER BY UPPER (resource_name);
348
349 -- Cursor to fetch the tasks for the set of resources.
350 CURSOR c_task_all
351 IS
352 SELECT /*+ cardinality(res 1) leading(res,a,cs,ts1,t) use_nl(res a) index(a,JTF_TASK_ASSIGNMENTS_N1) */
353 t.task_id
354 , decode(t.task_type_id, 20, 0, 21, 0, t.task_id) real_task_id
355 , t.task_number
356 , t.task_type_id
357 , decode(t.task_type_id, 20, 0, 21, 2, 1) trip_task_ind
358 , t.task_priority_id
359 , t.source_object_type_code
360 , t.source_object_name
361 , t.source_object_id
362 , t.planned_start_date
363 , t.planned_end_date
364 , t.scheduled_start_date
365 , t.scheduled_end_date
366 , t.task_confirmation_status
367 , t.parent_task_id
368 , t.task_split_flag
369 , a.assignment_status_id
370 , a.actual_start_date
371 , a.actual_end_date
372 , l.city
373 , NULL customer
374 , NULL contract
375 , null type_name
376 , ts1.name
377 , 0 escalated
378 , a.actual_effort
379 , t.planned_effort
380 , res.resource_id
381 , res.resource_type
382 , res.resource_name
383 , ts1.schedulable_flag
384 , tt.schedule_flag
385 , a.object_capacity_id
386 , a.task_assignment_id
387 , cs.availability_type
388 FROM ( SELECT resource_id,
389 resource_type,
390 resource_name
391 FROM Table(Cast(v_restab As csf_resource_tbl))
392 ) res
393 , jtf_task_assignments a
394 , jtf_tasks_b t
395 , jtf_task_statuses_vl ts1
396 , jtf_task_statuses_b ts2
397 , jtf_task_types_b tt
398 , hz_locations l
399 , cac_sr_object_capacity cs
400 WHERE a.assignee_role = 'ASSIGNEE'
401 AND a.resource_id = res.resource_id
402 AND a.resource_type_code = res.resource_type
403 AND a.booking_end_date >= p_start_date
404 AND a.booking_start_date < p_end_date
405 AND a.booking_end_date >= a.booking_start_date
406 AND a.assignment_status_id = ts1.task_status_id
407 AND nvl(ts1.cancelled_flag,'N') <> 'Y'
408 AND t.task_id = a.task_id
409 -- AND t.scheduled_start_date is not null --commented for the bug 6729435
410 -- AND t.scheduled_end_date is not null
411 AND NVL(t.deleted_flag, 'N') <> 'Y'
412 AND t.task_status_id = ts2.task_status_id
413 AND nvl(ts2.cancelled_flag,'N') <> 'Y'
414 and cs.object_capacity_id(+)= a.object_capacity_id
415 AND t.task_type_id = tt.task_type_id
416 AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
417 ORDER BY res.resource_name
418 , nvl(a.actual_start_date,t.scheduled_start_date)
419 , DECODE(t.task_type_id, 20, 1, 21, 3, 2)
420 , a.task_assignment_id;
421
422 CURSOR c_task
423 IS
424 SELECT *
425 FROM
426 (SELECT /*+ cardinality(res 1) leading(res,a,cs,ts1,t) use_nl(res a) index(a,JTF_TASK_ASSIGNMENTS_N1) */
427 t.task_id
428 , decode(t.task_type_id, 20, 0, 21, 0, t.task_id) real_task_id
429 , t.task_number
430 , t.task_type_id
431 , decode(t.task_type_id, 20, 0, 21, 2, 1) trip_task_ind
432 , t.task_priority_id
433 , t.source_object_type_code
434 , t.source_object_name
435 , t.source_object_id
436 , t.planned_start_date
437 , t.planned_end_date
438 , t.scheduled_start_date
439 , t.scheduled_end_date
440 , t.task_confirmation_status
441 , t.parent_task_id
442 , t.task_split_flag
443 , a.assignment_status_id
444 , a.actual_start_date
445 , a.actual_end_date
446 , l.city
447 , NULL customer
448 , NULL contract
449 , null type_name
450 , ts1.name
451 , 0 escalated
452 , a.actual_effort
453 , t.planned_effort
454 , res.resource_id
455 , res.resource_type
456 , res.resource_name
457 , ts1.schedulable_flag
458 , tt.schedule_flag
459 , a.object_capacity_id
460 , a.task_assignment_id
461 , cs.availability_type
462 FROM ( SELECT resource_id,
463 resource_type,
464 resource_name
465 FROM Table(Cast(v_restab As csf_resource_tbl))
466 ) res
467 , jtf_task_assignments a
468 , jtf_tasks_b t
469 , jtf_task_statuses_vl ts1
470 , jtf_task_statuses_b ts2
471 , jtf_task_types_b tt
472 , hz_locations l
473 , cac_sr_object_capacity cs
474 WHERE a.assignee_role = 'ASSIGNEE'
475 AND a.resource_id = res.resource_id
476 AND a.resource_type_code = res.resource_type
477 AND a.booking_end_date >= p_start_date
478 AND a.booking_start_date < p_end_date
479 AND a.booking_end_date >= a.booking_start_date
480 AND a.assignment_status_id = ts1.task_status_id
481 AND nvl(ts1.cancelled_flag,'N') <> 'Y'
482 AND t.task_id = a.task_id
483 AND NVL(t.deleted_flag, 'N') <> 'Y'
484 and cs.object_capacity_id(+)= a.object_capacity_id
485 AND t.task_type_id not in (20,21)
486 AND t.task_status_id = ts2.task_status_id
487 AND nvl(ts2.cancelled_flag,'N') <> 'Y'
488 AND t.task_type_id = tt.task_type_id
489 AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
490 UNION
491 SELECT /*+ cardinality(res 1) leading(res,a,cs,ts1,t) use_nl(res a) index(a,JTF_TASK_ASSIGNMENTS_N1) */
492 t.task_id
493 , decode(t.task_type_id, 20, 0, 21, 0, t.task_id) real_task_id
494 , t.task_number
495 , t.task_type_id
496 , decode(t.task_type_id, 20, 0, 21, 2, 1) trip_task_ind
497 , t.task_priority_id
498 , t.source_object_type_code
499 , t.source_object_name
500 , t.source_object_id
501 , t.planned_start_date
502 , t.planned_end_date
503 , t.scheduled_start_date
504 , t.scheduled_end_date
505 , t.task_confirmation_status
506 , t.parent_task_id
507 , t.task_split_flag
508 , a.assignment_status_id
509 , a.actual_start_date
510 , a.actual_end_date
511 , l.city
512 , NULL customer
513 , NULL contract
514 , null type_name
515 , ts1.name
516 , 0 escalated
517 , a.actual_effort
518 , t.planned_effort
519 , res.resource_id
520 , res.resource_type
521 , res.resource_name
522 , ts1.schedulable_flag
523 , tt.schedule_flag
524 , a.object_capacity_id
525 , a.task_assignment_id
526 ,csr.availability_type
527 FROM ( SELECT resource_id,
528 resource_type,
529 resource_name
530 FROM Table(Cast(v_restab As csf_resource_tbl))
531 ) res
532 , jtf_task_assignments a
533 , jtf_tasks_b t
534 , jtf_task_statuses_vl ts1
535 , jtf_task_statuses_b ts2
536 , jtf_task_types_b tt
537 , hz_locations l
538 , cac_sr_object_capacity csr
539 WHERE a.assignee_role = 'ASSIGNEE'
540 AND a.resource_id = res.resource_id
541 AND a.resource_type_code = res.resource_type
542 AND a.booking_end_date >= p_start_date
543 AND a.booking_start_date < p_end_date
544 AND a.booking_end_date >= a.booking_start_date
545 AND a.assignment_status_id = ts1.task_status_id
546 AND nvl(ts1.cancelled_flag,'N') <> 'Y'
547 AND t.task_id = a.task_id
548 AND NVL(t.deleted_flag, 'N') <> 'Y'
549 AND t.task_status_id = ts2.task_status_id
550 AND nvl(ts2.cancelled_flag,'N') <> 'Y'
551 AND t.task_type_id in (20,21)
552 AND t.task_type_id = tt.task_type_id
553 AND csr.object_capacity_id(+)=a.object_capacity_id
554 AND (NVL(csr.availability_type,'REGULAR') = decode (nvl(p_shift_reg,'N'),'R','REGULAR')
555 or NVL(csr.availability_type,NULL) = decode (nvl(p_shift_std,'N'),'S','STANDBY') )
556 AND l.location_id(+) = csf_tasks_pub.get_task_location_id(t.task_id,t.address_id,t.location_id)
557 )
558 ORDER BY resource_name
559 , nvl(actual_start_date,scheduled_start_date)
560 , DECODE(task_type_id, 20, 1, 21, 3, 2)
561 , task_assignment_id;
562
563
564 procedure set_task_custom_color( p_task_id in number
565 , p_type_id in number
566 , p_priority_id in number
567 , p_status_id in number
568 , p_avail_type in varchar2
569 , p_item out nocopy varchar2)
570 is
571 l_color varchar2(60) := null;
572
573 begin
574 if g_use_custom_chromatics
575 then
576 if p_type_id in (20,21) and p_avail_type = 'STANDBY'
577 then
578 p_item := 'R255G217B255';
579 else
580 if l_task_custom_color ='Y'
581 then
582 if l_rule_id is not null
583 then
584 begin
585 select background_col_rgb
586 into p_item
587 from jtf_task_custom_colors
588 where rule_id=l_rule_id;
589 exception
590 when no_data_found then
591 p_item := jtf_task_custom_colors_pub.get_task_rgb_bgcolor(
592 p_task_id,
593 p_type_id,
594 p_priority_id,
595 p_status_id);
596 end ;
597
598 else
599
600 p_item := jtf_task_custom_colors_pub.get_task_rgb_bgcolor(
601 p_task_id,
602 p_type_id,
603 p_priority_id,
604 p_status_id);
605 end if;
606 else
607 p_item := jtf_task_custom_colors_pub.get_task_rgb_bgcolor(
608 p_task_id,
609 p_type_id,
610 p_priority_id,
611 p_status_id);
612 end if;
613 end if;-- end if for standy by shift
614 end if; -- end if for custom chromatics
615 end set_task_custom_color;
616
617
618 ---Newly added code for performance improvement
619 PROCEDURE get_trip_status IS
620 CURSOR c_trip_status IS
621 SELECT object_capacity_id,status
622 FROM cac_sr_object_capacity
623 WHERE object_capacity_id in (select DISTINCT column_value
624 FROM TABLE(CAST(trip_id AS jtf_NUMBER_table))
625 where column_value <> 0);
626 i BINARY_INTEGER := 0;
627 BEGIN
628 IF g_trip_tbl.COUNT = 0
629 THEN
630 FOR rec IN c_trip_status
631 LOOP
632 i := i + 1;
633 g_trip_tbl(i).object_capacity_id := rec.object_capacity_id;
634 g_trip_tbl(i).status := rec.status;
635 END LOOP;
636 END IF;
637 END get_trip_status;
638
639 ---Newly added code for performance improvement
640 PROCEDURE get_access_status IS
641 CURSOR c_access_status IS
642 SELECT task_id
643 , NVL(accesshour_required, 'N') access_flag
644 , NVL(after_hours_flag, 'N') after_flag
645 FROM csf_access_hours_b
646 WHERE task_id in (select DISTINCT column_value
647 FROM TABLE(CAST(real_task_id AS jtf_NUMBER_table))
648 where column_value <> 0);
649 i BINARY_INTEGER := 0;
650 BEGIN
651 IF g_access_tbl.COUNT = 0
652 THEN
653 FOR rec IN c_access_status
654 LOOP
655 i := i + 1;
656 g_access_tbl(i).task_id := rec.task_id;
657 g_access_tbl(i).accesshr_set := rec.access_flag;
658 g_access_tbl(i).afterhr_set := rec.after_flag;
659 END LOOP;
660 END IF;
661 END get_access_status;
662
663 ---Newly added code for performance improvement
664 PROCEDURE get_parts_status IS
665 CURSOR c_parts_status IS
666 SELECT task_id
667 FROM csp_requirement_headers
668 WHERE task_id in (select DISTINCT column_value
669 FROM TABLE(CAST(real_task_id AS jtf_NUMBER_table))
670 where column_value <> 0);
671 i BINARY_INTEGER := 0;
672 BEGIN
673 IF g_parts_tbl.COUNT = 0
674 THEN
675 FOR rec IN c_parts_status
676 LOOP
677 i := i + 1;
678 g_parts_tbl(i).task_id := rec.task_id;
679 END LOOP;
680 END IF;
681 END get_parts_status;
682
683 FUNCTION do_match(
684 p_id IN NUMBER,
685 p_match_type IN VARCHAR2
686 )
687 RETURN VARCHAR2 IS
688 BEGIN
689 IF p_match_type = 'TRIP'
690 THEN
691 IF g_trip_tbl.COUNT > 0
692 THEN
693 FOR i IN 1 .. g_trip_tbl.COUNT
694 LOOP
695 IF g_trip_tbl(i).object_capacity_id = p_id
696 THEN
697 RETURN g_trip_tbl(i).status;
698 END IF;
699 END LOOP;
700 END IF;
701 RETURN NULL;
702 ELSIF p_match_type = 'ACCESS'
703 THEN
704 IF g_access_tbl.COUNT > 0
705 THEN
706 FOR i IN 1 .. g_access_tbl.COUNT
707 LOOP
708 IF g_access_tbl(i).task_id = p_id
709 THEN
710 IF g_access_tbl(i).accesshr_set = 'Y'
711 THEN
712 RETURN 'A ';
713 ELSIF g_access_tbl(i).afterhr_set = 'Y'
714 THEN
715 RETURN 'F ';
716 END IF;
717 END IF;
718 END LOOP;
719 END IF;
720 RETURN ' ';
721 ELSIF p_match_type = 'PARTS'
722 THEN
723 IF g_parts_tbl.COUNT > 0
724 THEN
725 FOR i IN 1 .. g_parts_tbl.COUNT
726 LOOP
727 IF g_parts_tbl(i).task_id = p_id
728 THEN
729 RETURN 'S ';
730 END IF;
731 END LOOP;
732 END IF;
733 RETURN ' ';
734 END IF;
735 RETURN NULL;
736 END do_match;
737
738
739 begin
740
741 l_uom_hours := fnd_profile.value('CSF_UOM_HOURS');
742 l_rule_id := fnd_profile.value_specific('CSF_TASK_SIGNAL_COLOR',fnd_global.user_id);
743 lf := fnd_global.local_chr(10);
744 l_tz := fnd_profile.value('CSF_DEFAULT_TIMEZONE_DC');
745 l_real_task_cnt := 0;
746 l_task_custom_color := 'N';
747 k :=0;
748 m :=0;
749 task_id := jtf_number_table();
750 real_task_id := jtf_number_table();
751 task_number := jtf_varchar2_table_100();
752 task_type_id := jtf_number_table();
753 trip_task_indicator := jtf_number_table();
754 task_priority_id := jtf_number_table();
755 source_object_type_code := jtf_varchar2_table_100();
756 source_object_name := jtf_varchar2_table_100();
757 source_object_id := jtf_number_table();
758 planned_start_date := jtf_date_table();
759 planned_end_date := jtf_date_table();
760 scheduled_start_date := jtf_date_table();
761 scheduled_end_date := jtf_date_table();
762 task_confirmation_status := jtf_varchar2_table_100();
763 parent_task_id := jtf_number_table();
764 task_split_flag := jtf_varchar2_table_100();
765 assignment_status_id := jtf_number_table();
766 actual_start_date := jtf_date_table();
767 actual_end_date := jtf_date_table();
768 city := jtf_varchar2_table_2000();
769 customer := jtf_varchar2_table_400();
770 contract := jtf_varchar2_table_400();
771 type_name := jtf_varchar2_table_100();
772 assignment_status := jtf_varchar2_table_100();
773 escalated := jtf_number_table();
774 actual_effort := jtf_number_table();
775 planned_effort := jtf_number_table();
776 resource_id := jtf_number_table();
777 resource_type := jtf_varchar2_table_100();
778 resource_name := jtf_varchar2_table_2000();
779 status_schedulable_flag := jtf_varchar2_table_100();
780 type_schedulable_flag := jtf_varchar2_table_100();
781 trip_id := jtf_number_table();
782 l_avail_type := jtf_varchar2_table_100();
783 l_assign_id := jtf_number_table();
784
785
786 -- fetch resources
787 IF NVL(FND_PROFILE.value('CSF_DC_DISPLAY_ONLY_TECHNICIANS'), 'N') = 'Y' THEN
788 OPEN c_res_technician;
789 FETCH c_res_technician
790 BULK COLLECT INTO
791 resource_name
792 , resource_id
793 , resource_type;
794 CLOSE c_res_technician;
795 ELSE
796 OPEN c_res;
797 FETCH c_res
798 BULK COLLECT INTO
799 resource_name
800 , resource_id
801 , resource_type;
802 CLOSE c_res;
803 END IF;
804
805 FOR i IN 1 .. resource_id.COUNT LOOP
806 k := k + 1;
807 v_restab.extend;
808 v_restab(v_restab.Last) := csf_resource(null,null,null,resource_id(i), resource_type(i), resource_name(i),null,null);
809 l_pr.resource_id := resource_id(i);
810 l_pr.resource_type := resource_type(i);
811 l_pr.resource_name := resource_name(i);
812 x_pb_tbl(k) := l_pr;
813 l_res_id_map_tbl(l_pr.resource_id||l_pr.resource_type) := k;
814 END LOOP;
815 resource_id := jtf_number_table();
816 resource_type := jtf_varchar2_table_100();
817 resource_name := jtf_varchar2_table_2000();
818
819 --if p_shift_reg is null and p_shift_std is null
820 --then
821 OPEN c_task_all;
822 FETCH c_task_all
823 BULK COLLECT INTO task_id
824 , real_task_id
825 , task_number
826 , task_type_id
827 , trip_task_indicator
828 , task_priority_id
829 , source_object_type_code
830 , source_object_name
831 , source_object_id
832 , planned_start_date
833 , planned_end_date
834 , scheduled_start_date
835 , scheduled_end_date
836 , task_confirmation_status
837 , parent_task_id
838 , task_split_flag
839 , assignment_status_id
840 , actual_start_date
841 , actual_end_date
842 , city
843 , customer
844 , contract
845 , type_name
846 , assignment_status
847 , escalated
848 , actual_effort
849 , planned_effort
850 , resource_id
851 , resource_type
852 , resource_name
853 , status_schedulable_flag
854 , type_schedulable_flag
855 , trip_id
856 , l_assign_id
857 , l_avail_type;
858 CLOSE c_task_all;
859 /*else
860 OPEN c_task;
861 FETCH c_task
862 BULK COLLECT INTO task_id
863 , real_task_id
864 , task_number
865 , task_type_id
866 , trip_task_indicator
867 , task_priority_id
868 , source_object_type_code
869 , source_object_name
870 , source_object_id
871 , planned_start_date
872 , planned_end_date
873 , scheduled_start_date
874 , scheduled_end_date
875 , task_confirmation_status
876 , parent_task_id
877 , task_split_flag
878 , assignment_status_id
879 , actual_start_date
880 , actual_end_date
881 , city
882 , customer
883 , contract
884 , type_name
885 , assignment_status
886 , escalated
887 , actual_effort
888 , planned_effort
889 , resource_id
890 , resource_type
891 , resource_name
892 , status_schedulable_flag
893 , type_schedulable_flag
894 , trip_id
895 , l_assign_id
896 , l_avail_type;
897
898 CLOSE c_task;
899 */
900 -- end if;
901
902
903 --NEWLY ADDED CURSOR FOR GETTING VIRTUAL TASKS NAMES
904 for i in c_virtual_tsk_names
905 loop
906 if i.task_type_id = 20
907 then
908 l_departure := i.name;
909 elsif i.task_type_id = 21
910 then
911 l_arrival := i.name;
912 end if;
913 end loop;
914
915 get_access_status;
916 get_trip_status;
917 get_parts_status;
918 --END FOR ADDITION
919
920 IF nvl(p_shift_std,'N') ='S' and nvl(p_shift_reg,'N') ='R'
921 THEN
922 l_shift_type:=null;
923 elsif nvl(p_shift_reg,'N') ='R' then
924 l_shift_type:='REGULAR';
925 elsif nvl(p_shift_std,'N') ='S' then
926 l_shift_type:='STANDBY';
927 elsif (p_shift_std ='H' and p_shift_reg ='H' )
928 OR (p_shift_std IS NULL AND p_shift_reg IS NULL)
929 then
930 l_shift_type:='HIDE';
931 else
932 l_shift_type:='HIDE';
933 end if;
934 -- resources and tasks loop
935 -----------------------------
936 k := null;
937 FOR i IN 1 .. task_id.COUNT LOOP
938 IF (task_type_id(i) NOT IN (20, 21)) OR
939 (l_shift_type is Null OR l_shift_type =nvl(l_avail_type(i),'REGULAR'))
940 THEN
941
942 IF l_prev_resource_id IS NULL THEN
943 k := l_res_id_map_tbl(resource_id(i)||resource_type(i));
944 l_pr := x_pb_tbl(k);
945 l_dep_task_position :=0;
946 l_dep_trip_id :=0;
947 l_dep_trip_status :=0;
948 l_dep_source_type :=null;
949 l_real_task_trip_cnt :=0;
950 l_task_confirm_ctr := 0;
951 m := 1;
952 ELSIF l_prev_resource_id||l_prev_resource_type = resource_id(i)||resource_type(i) THEN
953 m := m + 1;
954 ELSE
955 l_dep_task_position :=0;
956 l_dep_trip_id :=0;
957 l_dep_trip_status :=0;
958 l_dep_source_type :=null;
959 l_real_task_trip_cnt :=0;
960 l_task_confirm_ctr := 0;
961 m := 1;
962 l_pr.actual_indicator := RPAD(l_pr.actual_indicator, 15, '0');
963 x_pb_tbl(k) := l_pr;
964 k := l_res_id_map_tbl(resource_id(i)||resource_type(i));
965 l_pr := x_pb_tbl(k);
966 END IF;
967
968 IF m <= 15 THEN
969 -- for SR tasks get the customer and contract name
970 --------------------------------------------------
971 IF source_object_type_code(i) = 'SR' THEN
972 l_incident_id := source_object_id(i);
973 get_customer(l_incident_id, customer(i), contract(i));
974 ELSE
975 l_incident_id := NULL;
976 END IF;
977
978 l_task_custom_color := 'N';
979
980 IF task_type_id(i) NOT IN(20, 21) THEN
981 IF actual_start_date(i) IS NOT NULL THEN
982 IF actual_end_date(i) IS NOT NULL THEN
983 IF actual_end_date(i) = actual_start_date(i) THEN
984 --set flag for color code
985 l_task_custom_color := 'Y';
986 END IF; --end if for actual_end_date=actual_start_date
987 ELSE
988 IF NVL(actual_effort(i), 0) = 0 THEN
989 IF NVL(planned_effort(i), 0) = 0 THEN
990 l_task_custom_color := 'Y';
991 END IF;
992 END IF;
993 -- End of the code added for the change in mini-design
994 END IF; --end if for actual_end_date is not null
995 ELSE --for actual start date is null
996 IF scheduled_end_date(i) IS NOT NULL THEN
997 IF scheduled_end_date(i) = scheduled_start_date(i) THEN
998 --set flag for color code
999 l_task_custom_color := 'Y';
1000 END IF; --end if for scheduled_end_date=scheduled_start_date
1001 ELSE
1002 --set flag for color code
1003 l_task_custom_color := 'Y';
1004 END IF; --end if scheduled end_date is not null
1005 END IF; --end if for actual_start_date is not null
1006 END IF; --end if task_type_id
1007
1008 -- condition for departure and arrival task
1009 IF task_type_id(i) IN(20, 21) THEN
1010 IF scheduled_start_date(i) IS NOT NULL AND scheduled_end_date(i) IS NOT NULL THEN
1011 IF scheduled_start_date(i) <> scheduled_end_date(i) THEN
1012 IF scheduled_end_date(i) > scheduled_start_date(i) THEN
1013 --set the color flag
1014 l_task_custom_color := 'Y';
1015 END IF; --if scheduled end_date > than start_date
1016 END IF; --end if for scheduled_end_date is not equal to start_date
1017 END IF; --end if for scheduled_start and end_dates are not null
1018 END IF; --end if for task_type_id
1019
1020 -------------------
1021 -- format task cell
1022 -------------------
1023
1024 -- row #1
1025 ---------
1026 -- escalated
1027 IF csf_tasks_pub.is_task_escalated(task_id(i)) THEN
1028 l_cell := '!! ';
1029 ELSE
1030 l_cell := NULL;
1031 END IF;
1032
1033 IF task_type_id(i) IN(20, 21) THEN
1034 IF task_type_id(i) = 20 THEN
1035 l_type_name := l_departure;
1036 l_cell := SUBSTRB(l_cell || l_type_name, 1, 25);
1037 ELSIF task_type_id(i) = 21 THEN
1038 l_type_name := l_arrival;
1039 l_cell := SUBSTRB(l_cell || l_type_name, 1, 25);
1040 END IF;
1041 IF task_type_id(i) = 20 THEN
1042 l_real_task_trip_cnt :=0;
1043 l_task_confirm_ctr := 0;
1044 l_dep_task_position := m;
1045 l_dep_trip_id := trip_id(i);
1046 l_dep_trip_status := do_match(trip_id(i),'TRIP');
1047 l_dep_trip_task_ind :=trip_task_indicator(i);
1048 l_dep_source_type := source_object_type_code(i);
1049 END IF;
1050 ELSE
1051 -- a real task which has status schedulable Y and type schedulable Y
1052 IF status_schedulable_flag(i) ='Y' AND type_schedulable_flag(i) = 'Y'
1053 AND source_object_type_code(i) ='SR'
1054 THEN
1055 l_real_task_cnt := l_real_task_cnt + 1;
1056 IF trip_id(i) = l_dep_trip_id
1057 THEN
1058 l_real_task_trip_cnt := l_real_task_trip_cnt + 1;
1059 IF nvl(task_confirmation_status(i),'N') in ('N','C') THEN
1060 l_task_confirm_ctr := l_task_confirm_ctr + 1;
1061 END IF;
1062 END IF;
1063 END IF;
1064 -- display task number instead of SR <nr> now
1065 l_cell := SUBSTRB(l_cell || task_number(i), 1, 25);
1066 END IF;
1067
1068 -- row #2
1069 ---------
1070 l_cell := l_cell || lf || SUBSTRB(customer(i), 1, 25);
1071
1072 -- row #3
1073 ---------
1074 IF scheduled_start_date(i) NOT BETWEEN p_start_date AND p_end_date THEN
1075 l_line := '**:** ';
1076 ELSE
1077 -- this if is added to check if actual_start_date is not null then display actual_start_date time
1078 --or else display scheduled_start_date
1079 IF l_tz = 'UTZ' THEN
1080 IF actual_start_date(i) IS NOT NULL THEN
1081 l_line :='('|| csf_timezones_pvt.date_to_client_tz_chartime(actual_start_date(i), 'hh24:mi')||') ';
1082 ELSE
1083 l_line := csf_timezones_pvt.date_to_client_tz_chartime(scheduled_start_date(i), 'hh24:mi')|| ' ';
1084 END IF;
1085 ELSE
1086 IF actual_start_date(i) IS NOT NULL THEN
1087 l_line := '(' || to_char(actual_start_date(i),'hh24:mi')||') ';
1088 ELSE
1089 l_line := to_char(scheduled_start_date(i),'hh24:mi') || ' ';
1090 END IF;
1091 END IF;
1092 END IF;
1093
1094 IF actual_start_date(i) IS NULL THEN
1095 l_pr.actual_indicator := l_pr.actual_indicator || '0';
1096 ELSE
1097 l_pr.actual_indicator := l_pr.actual_indicator || '1';
1098 END IF;
1099
1100 -- chosen to suppress the assignment status for dep/arr
1101 IF task_type_id(i) NOT IN(20, 21) THEN
1102 l_assignment_status := assignment_status(i);
1103 l_line := l_line || ' ' || l_assignment_status;
1104 END IF;
1105
1106 l_cell := l_cell || lf || SUBSTRB(l_line, 1, 25);
1107
1108
1109 -- row #4
1110 ---------
1111 IF contract(i) IS NOT NULL THEN
1112 l_line := SUBSTRB(contract(i), 1, 12) || ' ';
1113 ELSE
1114 l_line := NULL;
1115 END IF;
1116
1117 IF city(i) IS NOT NULL THEN
1118 l_line := l_line || city(i);
1119 END IF;
1120
1121
1122 IF l_line is not null THEN
1123 l_cell := l_cell||lf||SUBSTRB(l_line,1,25);
1124 END IF;
1125
1126 --row # 5 added for inspection/R12
1127 if source_object_type_code(i) = 'SR' then
1128 l_depend_flag :=null;
1129 l_notes_flag :=null;
1130
1131 --access hours/after hours check
1132 /* If nvl(access_hours(i),'N')='Y' then
1133 l_line:='A ';
1134 elsif nvl(after_hours(i),'N')='Y' then
1135 l_line:='F ';
1136 else
1137 l_line:=' ';
1138 end if; */
1139
1140 l_line := do_match(real_task_id(i),'ACCESS');
1141 -- Customer Confirmation check
1142 If nvl(task_confirmation_status(i),'N')='C' then
1143 l_line:=l_line||'V ';
1144 elsif nvl(task_confirmation_status(i),'N')='R' then
1145 l_line:=l_line||'C ';
1146 else
1147 l_line:=l_line||' ';
1148 end if;
1149
1150 -- Parts check
1151 /*If nvl(parts_required(i),'N')='Y' then
1152 l_line:=l_line||'S ';
1153 else
1154 l_line:=l_line||' ';
1155 end if;*/
1156 l_line := l_line || do_match(real_task_id(i),'PARTS');
1157
1158 -- Parent/child check
1159 If nvl(task_split_flag(i),'N')='D' and parent_task_id(i) is not null then
1160 l_line:=l_line||'D ';
1161 elsif nvl(task_split_flag(i),'N')='M' and parent_task_id(i) is null then
1162 l_line:=l_line||'M ';
1163 else
1164 l_line:=l_line||' ';
1165 end if;
1166 -- task dependencies check
1167 Open c_depend_check(task_id(i));
1168 Fetch c_depend_check into l_depend_flag;
1169 close c_depend_check;
1170
1171 If nvl(l_depend_flag,'N')='Y' then
1172 l_line:=l_line||'R ';
1173 else
1174 l_line:=l_line||' ';
1175 end if;
1176
1177 --notes check
1178 if task_has_notes(task_id(i), source_object_type_code(i),source_object_id(i))
1179 then
1180 l_line:=l_line||'N ';
1181 else
1182 l_line:=l_line||' ';
1183 end if;
1184
1185 if l_line is not null then
1186 l_cell := l_cell||lf||l_line;
1187 end if;
1188 end if;
1189 --row # 5 ends here
1190
1191 -------------------------------------------
1192 -- put queried record into planboard record
1193 -------------------------------------------
1194
1195 IF m = 1 THEN
1196 l_pr.task_id_1 := task_id(i);
1197 l_pr.task_cell_1 := l_cell;
1198 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);
1199
1200
1201 set_task_custom_color(
1202 task_id(i)
1203 , task_type_id(i)
1204 , task_priority_id(i)
1205 , assignment_status_id(i)
1206 , l_avail_type(i)
1207 , l_pr.rgb_color_1
1208 );
1209
1210 ELSIF m = 2 THEN
1211 l_pr.task_id_2 := task_id(i);
1212 l_pr.task_cell_2 := l_cell;
1213 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);
1214
1215 set_task_custom_color(
1216 task_id(i)
1217 , task_type_id(i)
1218 , task_priority_id(i)
1219 , assignment_status_id(i)
1220 , l_avail_type(i)
1221 , l_pr.rgb_color_2
1222 );
1223 ELSIF m = 3 THEN
1224 l_pr.task_id_3 := task_id(i);
1225 l_pr.task_cell_3 := l_cell;
1226 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);
1227 set_task_custom_color(
1228 task_id(i)
1229 , task_type_id(i)
1230 , task_priority_id(i)
1231 , assignment_status_id(i)
1232 , l_avail_type(i)
1233 , l_pr.rgb_color_3
1234 );
1235 ELSIF m = 4 THEN
1236 l_pr.task_id_4 := task_id(i);
1237 l_pr.task_cell_4 := l_cell;
1238 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);
1239 set_task_custom_color(
1240 task_id(i)
1241 , task_type_id(i)
1242 , task_priority_id(i)
1243 , assignment_status_id(i)
1244 , l_avail_type(i)
1245 , l_pr.rgb_color_4
1246 );
1247 ELSIF m = 5 THEN
1248 l_pr.task_id_5 := task_id(i);
1249 l_pr.task_cell_5 := l_cell;
1250 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);
1251 set_task_custom_color(
1252 task_id(i)
1253 , task_type_id(i)
1254 , task_priority_id(i)
1255 , assignment_status_id(i)
1256 , l_avail_type(i)
1257 , l_pr.rgb_color_5
1258 );
1259 ELSIF m = 6 THEN
1260 l_pr.task_id_6 := task_id(i);
1261 l_pr.task_cell_6 := l_cell;
1262 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);
1263 set_task_custom_color(
1264 task_id(i)
1265 , task_type_id(i)
1266 , task_priority_id(i)
1267 , assignment_status_id(i)
1268 , l_avail_type(i)
1269 , l_pr.rgb_color_6
1270 );
1271 ELSIF m = 7 THEN
1272 l_pr.task_id_7 := task_id(i);
1273 l_pr.task_cell_7 := l_cell;
1274 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);
1275 set_task_custom_color(
1276 task_id(i)
1277 , task_type_id(i)
1278 , task_priority_id(i)
1279 , assignment_status_id(i)
1280 , l_avail_type(i)
1281 , l_pr.rgb_color_7
1282 );
1283 ELSIF m = 8 THEN
1284 l_pr.task_id_8 := task_id(i);
1285 l_pr.task_cell_8 := l_cell;
1286 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);
1287 set_task_custom_color(
1288 task_id(i)
1289 , task_type_id(i)
1290 , task_priority_id(i)
1291 , assignment_status_id(i)
1292 , l_avail_type(i)
1293 , l_pr.rgb_color_8
1294 );
1295 ELSIF m = 9 THEN
1296 l_pr.task_id_9 := task_id(i);
1297 l_pr.task_cell_9 := l_cell;
1298 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);
1299 set_task_custom_color(
1300 task_id(i)
1301 , task_type_id(i)
1302 , task_priority_id(i)
1303 , assignment_status_id(i)
1304 , l_avail_type(i)
1305 , l_pr.rgb_color_9
1306 );
1307 ELSIF m = 10 THEN
1308 l_pr.task_id_10 := task_id(i);
1309 l_pr.task_cell_10 := l_cell;
1310 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);
1311 set_task_custom_color(
1312 task_id(i)
1313 , task_type_id(i)
1314 , task_priority_id(i)
1315 , assignment_status_id(i)
1316 , l_avail_type(i)
1317 , l_pr.rgb_color_10
1318 );
1319 ELSIF m = 11 THEN
1320 l_pr.task_id_11 := task_id(i);
1321 l_pr.task_cell_11 := l_cell;
1322 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);
1323 set_task_custom_color(
1324 task_id(i)
1325 , task_type_id(i)
1326 , task_priority_id(i)
1327 , assignment_status_id(i)
1328 , l_avail_type(i)
1329 , l_pr.rgb_color_11
1330 );
1331 ELSIF m = 12 THEN
1332 l_pr.task_id_12 := task_id(i);
1333 l_pr.task_cell_12 := l_cell;
1334 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);
1335 set_task_custom_color(
1336 task_id(i)
1337 , task_type_id(i)
1338 , task_priority_id(i)
1339 , assignment_status_id(i)
1340 , l_avail_type(i)
1341 , l_pr.rgb_color_12
1342 );
1343 ELSIF m = 13 THEN
1344 l_pr.task_id_13 := task_id(i);
1345 l_pr.task_cell_13 := l_cell;
1346 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);
1347 set_task_custom_color(
1348 task_id(i)
1349 , task_type_id(i)
1350 , task_priority_id(i)
1351 , assignment_status_id(i)
1352 , l_avail_type(i)
1353 , l_pr.rgb_color_13
1354 );
1355 ELSIF m = 14 THEN
1356 l_pr.task_id_14 := task_id(i);
1357 l_pr.task_cell_14 := l_cell;
1358 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);
1359 set_task_custom_color(
1360 task_id(i)
1361 , task_type_id(i)
1362 , task_priority_id(i)
1363 , assignment_status_id(i)
1364 , l_avail_type(i)
1365 , l_pr.rgb_color_14
1366 );
1367 ELSIF m = 15 THEN
1368 l_pr.task_id_15 := task_id(i);
1369 l_pr.task_cell_15 := l_cell;
1370 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);
1371 set_task_custom_color(
1372 task_id(i)
1373 , task_type_id(i)
1374 , task_priority_id(i)
1375 , assignment_status_id(i)
1376 , l_avail_type(i)
1377 , l_pr.rgb_color_15
1378 );
1379 END IF;
1380
1381 IF l_dep_task_position > 0 and l_real_task_trip_cnt > 0 THEN
1382 IF l_dep_task_position = 1 THEN
1383 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;
1384 ELSIF l_dep_task_position = 2 THEN
1385 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;
1386 ELSIF l_dep_task_position = 3 THEN
1387 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;
1388 ELSIF l_dep_task_position = 4 THEN
1389 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;
1390 ELSIF l_dep_task_position = 5 THEN
1391 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;
1392 ELSIF l_dep_task_position = 6 THEN
1393 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;
1394 ELSIF l_dep_task_position = 7 THEN
1395 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;
1396 ELSIF l_dep_task_position = 8 THEN
1397 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;
1398 ELSIF l_dep_task_position = 9 THEN
1399 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;
1400 ELSIF l_dep_task_position = 10 THEN
1401 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;
1402 ELSIF l_dep_task_position = 11 THEN
1403 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;
1404 ELSIF l_dep_task_position = 12 THEN
1405 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;
1406 ELSIF l_dep_task_position = 13 THEN
1407 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;
1408 ELSIF l_dep_task_position = 14 THEN
1409 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;
1410 ELSIF l_dep_task_position = 15 THEN
1411 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;
1412 END IF;
1413 END IF;
1414 END IF;
1415 l_prev_resource_id := resource_id(i);
1416 l_prev_resource_type := resource_type(i);
1417 END IF;-- This end if is for resource trip type
1418 END LOOP;
1419
1420 if k is not null then
1421 l_pr.actual_indicator := RPAD(l_pr.actual_indicator, 15, '0');
1422 x_pb_tbl(k) := l_pr;
1423 end if;
1424 -- update the indicator in record 1 with the "real" task count
1425 if x_pb_tbl.COUNT > 0 then
1426 x_pb_tbl(x_pb_tbl.FIRST).real_task_cnt := l_real_task_cnt;
1427 end if;
1428 EXCEPTION
1429 -- there were no resources
1430 WHEN COLLECTION_IS_NULL THEN
1431 NULL;
1432 END populate_planboard_table;
1433 BEGIN
1434 -- getting the indicator if custom color coding will be used.
1435 g_use_custom_chromatics := fnd_profile.value('CSF_USE_CUSTOM_CHROMATICS') = 'Y' ;
1436 END csf_planboard_tasks;
1437