1 PACKAGE CSF_GANTT_DATA_PKG AUTHID CURRENT_USER As
2 /*$Header: CSFGTPLS.pls 120.19 2011/04/26 13:44:04 ramchint ship $*/
3
4 /* Change history
5 Date Userid Change
6 ---------- -------- ---------------------------------------------------
7 05/10/2004 vakulkar created new package for Gantt
8 14/10/2004 vakulkar addded global variable for userenv('lang')
9 changed the get_scheduled_task/virtual_tasks
10 procedures parameters
11 30/10/2004 vakulkar addded 2 extra parametrs to procedure
12 get_planned_shitfs.
13 created one new procedure for showing
14 real tasks in plantoption window
15 get_advise_real_tasks
16 09/12/2004 vakulkar modified get_scheduled_real_task,virtual_tasks
17 get_planned_shifts,get_planned_tasks procedure
18
19 In get_scheduled_real/virtual task removed
20 the code for populating tooltip.
21 Redesign the cursor for getting the data
22 like removed join cs_incident_all_b and other
23 tabels related to it.
24
25 Removed all uneccessary code for tooltip and
26 color and get_contact_point.
27
28 Created 3 new procedures for tooltip
29 get_tooltip_data_gantt
30 get_tooltip_data_sch_adv
31 get_gantt_task_color
32 28/12/2004 vakulkar Added Drag n Drop procedure for Gantt
33 23/02/2005 vakulkar Added Procedure get_skilled_resources
34 used when drag n drop is done from
35 one resource to another then check if
36 new resource has the same skills and they
37 are active on that day.
38 And if task is drag and drop to same resource
39 but diffrent date then validate the skills
40 same as above
41 Added get_resource_name, get_resource_type_name
42 Procedures which will be used by CSFDataSource.java
43 to populate Resources in Left pane of Gantt.
44
45 25/04/2005 vakulkar introduced new 3 new procedure for schedule advise gantt
46 1. g_do_match for matchin the color for task
47 2.g_get_custom_color for populating pl/sql table
48 which willbe used by g_do_match procedure for
49 getting the color
50 3.get_tooltip_for_plan_task this is used to show
51 tooltip for planned task
52
53 17/02/2006 vakulkar removed seven procedures and introduced two procedures for
54 doing the same task. The procedures removed are
55 1. get_resource_shifts
56 2. get_planned_shifts
57 3. get_scheduled_virtual_tasks
58 4. get_advise_real_tasks
59 5. get_scheduled_real_tasks
60 6. get_schedule_advise_tasks
61 7. get_adv_real_tasks
62 The procedure that are introduced are
63 1. get_dispatch_task_dtls
64 2. get_schedule_advise_options
65 */
66 -- ---------------------------------------------------------------------------
67 -- Public variable, constants, funtions
68 -- ---------------------------------------------------------------------------
69 blue Constant Number (3) := 255; -- color for regular tasks
70 red Constant Number (8) := 16711680;
71 -- color for escalated tasks
72 green Constant Number (5) := 65280;
73 -- color for task plan option
74 yellow Constant Number (8) := 16776960;
75
76
77 TYPE g_custom_color_rec IS RECORD (
78 task_type_id NUMBER,
79 task_priority_id NUMBER,
80 assignment_status_id NUMBER,
81 escalated_task VARCHAR2 (1),
82 background_col_dec NUMBER,
83 background_col_rgb VARCHAR2 (12)
84 );
85
86 TYPE g_custom_color_tbl IS TABLE OF g_custom_color_rec
87 INDEX BY BINARY_INTEGER;
88 -- color for task with actual times
89 Type tooltip_data_rec_type Is Record (
90 task_name jtf_tasks_tl.task_name%Type
91 , task_number jtf_tasks_b.task_number%Type
92 , task_type jtf_task_types_tl.Name%Type
93 , task_status jtf_task_statuses_vl.Name%Type
94 , address Varchar2 (2000)
95 , parts Varchar2 (30)
96 , incident_number cs_incidents_all_b.incident_number%Type
97 , incident_type_name cs_incident_types_tl.Name%Type
98 , incident_customer_name varchar2(1000) --bug no 5674408
99 , contact Varchar2 (2000)
100 , phone Varchar2 (2000)
101 , product_name mtl_system_items_kfv.concatenated_segments%Type
102 , serial_number csi_item_instances.serial_number%Type
103 , lot_number csi_item_instances.lot_number%Type
104 , resource_name Varchar2 (2000)
105 , planned_start Varchar2 (30)
106 , planned_end Varchar2 (30)
107 , scheduled_start_date Date
108 , scheduled_start Varchar2 (30)
109 , scheduled_end Varchar2 (30)
110 , actual_start Varchar2 (30)
111 , actual_end Varchar2 (30)
112 , departure_time Date
113 , travel_time Varchar2 (300)
114 , estimated_start Varchar2 (30)
115 , estimated_end Varchar2 (30)
116 , assigned_flag Varchar2 (30)
117 , is_plan_option Varchar2 (1)
118 );
119
120 type tooltip_setup_type is record
121 ( seq_no number
122 , field_name varchar2(50)
123 , field_value varchar2(50));
124
125 type tooltip_setup_tbl is table of tooltip_setup_type INDEX BY BINARY_INTEGER;
126
127
128 PROCEDURE get_message_text
129 ( p_api_version IN Number
130 , p_init_msg_list IN Varchar2 DEFAULT NULL
131 , x_return_status OUT NOCOPY Varchar2
132 , x_msg_count OUT NOCOPY Number
133 , x_msg_data OUT NOCOPY Varchar2
134 , p_message_text OUT NOCOPY jtf_varchar2_table_2000
135 , p_message_code OUT NOCOPY jtf_varchar2_table_2000
136 );
137
138 Procedure get_dispatch_task_dtls (
139 p_api_version IN Number
140 , p_init_msg_list IN Varchar2 DEFAULT NULL
141 , x_return_status OUT NOCOPY Varchar2
142 , x_msg_count OUT NOCOPY Number
143 , x_msg_data OUT NOCOPY Varchar2
144 , p_start_date_range IN DATE
145 , p_end_date_range IN DATE
146 , p_res_id OUT NOCOPY jtf_number_table
147 , p_res_type OUT NOCOPY jtf_varchar2_table_2000
148 , p_res_name OUT NOCOPY jtf_varchar2_table_2000
149 , p_res_typ_name OUT NOCOPY jtf_varchar2_table_2000
150 , p_res_key OUT NOCOPY jtf_varchar2_table_2000
151 , p_trip_id OUT NOCOPY jtf_number_table
152 , p_shift_start_date OUT NOCOPY jtf_date_table
153 , p_shift_end_date OUT NOCOPY jtf_date_table
154 , p_block_trip OUT NOCOPY jtf_number_table
155 , p_shift_res_key OUT NOCOPY jtf_varchar2_table_2000
156 , p_vir_task_id OUT NOCOPY jtf_varchar2_table_100
157 , p_vir_start_date OUT NOCOPY jtf_date_table
158 , p_vir_end_date OUT NOCOPY jtf_date_table
159 , p_vir_color OUT NOCOPY jtf_number_table
160 , p_vir_name OUT NOCOPY jtf_varchar2_table_100
161 , p_vir_duration OUT NOCOPY jtf_number_table
162 , p_vir_task_type_id OUT NOCOPY jtf_number_table
163 , p_vir_tooltip OUT NOCOPY jtf_varchar2_table_2000
164 , p_vir_resource_key OUT NOCOPY jtf_varchar2_table_2000
165 , real_task_id OUT NOCOPY jtf_varchar2_table_100
166 , real_start_date OUT NOCOPY jtf_date_table
167 , real_end_date OUT NOCOPY jtf_date_table
168 , real_color OUT NOCOPY jtf_number_table
169 , real_NAME OUT NOCOPY jtf_varchar2_table_2000
170 , real_tooltip OUT NOCOPY jtf_varchar2_table_2000
171 , real_DURATION OUT NOCOPY jtf_number_table
172 , real_task_type_id OUT NOCOPY jtf_number_table
173 , real_resource_key OUT NOCOPY jtf_varchar2_table_2000
174 , real_parts_required OUT NOCOPY jtf_varchar2_table_100
175 , real_access_hours OUT NOCOPY jtf_varchar2_table_100
176 , real_after_hours OUT NOCOPY jtf_varchar2_table_100
177 , real_customer_conf OUT NOCOPY jtf_varchar2_table_100
178 , real_task_depend OUT NOCOPY jtf_varchar2_table_100
179 , real_child_task OUT NOCOPY jtf_varchar2_table_100
180 , p_vir_avail_type OUT NOCOPY jtf_varchar2_table_2000
181 , p_show_arr_dep_tasks IN varchar2 DEFAULT 'N'
182 , p_terr_name OUT NOCOPY jtf_varchar2_table_2000
183 , p_contact OUT NOCOPY jtf_varchar2_table_2000
184 );
185
186
187 PROCEDURE get_schedule_advise_options
188 (
189 p_api_version IN NUMBER
190 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
191 , x_return_status OUT NOCOPY VARCHAR2
192 , x_msg_count OUT NOCOPY NUMBER
193 , x_msg_data OUT NOCOPY VARCHAR2
194 , p_display_option IN VARCHAR2
195 , p_resource_id IN NUMBER
196 , p_resource_type IN VARCHAR2
197 , p_req_id IN NUMBER
198 , p_par_task IN NUMBER
199 , p_task_id IN NUMBER
200 , p_res_id OUT NOCOPY jtf_number_table
201 , p_res_type OUT NOCOPY jtf_varchar2_table_2000
202 , p_res_name OUT NOCOPY jtf_varchar2_table_2000
203 , p_res_typ_name OUT NOCOPY jtf_varchar2_table_2000
204 , p_res_key OUT NOCOPY jtf_varchar2_table_2000
205 , p_cost OUT NOCOPY jtf_number_table
206 , p_start_date IN DATE
207 , p_end_date IN DATE
208 , sch_adv_tz In Varchar2
209 , inc_tz_code In Varchar2
210 , trip_id OUT NOCOPY jtf_number_table
211 , start_date OUT NOCOPY jtf_date_table
212 , end_date OUT NOCOPY jtf_date_table
213 , block_trip OUT NOCOPY jtf_number_table
214 , p_bck_res_key OUT NOCOPY jtf_varchar2_table_2000
215 , plan_task_key OUT NOCOPY jtf_varchar2_table_100
216 , plan_start_date OUT NOCOPY jtf_date_table
217 , plan_end_date OUT NOCOPY jtf_date_table
218 , plan_color OUT NOCOPY jtf_number_table
219 , plan_name OUT NOCOPY jtf_varchar2_table_2000
220 , plan_tooltip OUT NOCOPY jtf_varchar2_table_2000
221 , plan_duration OUT NOCOPY jtf_number_table
222 , plan_task_type_id OUT NOCOPY jtf_number_table
223 , plan_resource_key OUT NOCOPY jtf_varchar2_table_2000
224 , real_task_key OUT NOCOPY jtf_varchar2_table_100
225 , real_start_date OUT NOCOPY jtf_date_table
226 , real_end_date OUT NOCOPY jtf_date_table
227 , real_color OUT NOCOPY jtf_number_table
228 , real_name OUT NOCOPY jtf_varchar2_table_2000
229 , real_tooltip OUT NOCOPY jtf_varchar2_table_2000
230 , real_duration OUT NOCOPY jtf_number_table
231 , real_task_type_id OUT NOCOPY jtf_number_table
232 , real_resource_key OUT NOCOPY jtf_varchar2_table_2000
233 , child_task OUT Nocopy jtf_varchar2_table_100
234 , real_parts_required OUT NOCOPY jtf_varchar2_table_100
235 , real_access_hours OUT NOCOPY jtf_varchar2_table_100
236 , real_after_hours OUT NOCOPY jtf_varchar2_table_100
237 , real_customer_conf OUT NOCOPY jtf_varchar2_table_100
238 , real_task_depend OUT NOCOPY jtf_varchar2_table_100
239 , oth_real_task_id Out Nocopy jtf_varchar2_table_100
240 , oth_real_start_date Out Nocopy jtf_date_table
241 , oth_real_end_date Out Nocopy jtf_date_table
242 , oth_real_color Out Nocopy jtf_number_table
243 , oth_real_Name Out Nocopy jtf_varchar2_table_2000
244 , oth_real_Duration Out Nocopy jtf_number_table
245 , oth_real_task_type_id Out Nocopy jtf_number_table
246 , oth_real_resource_key Out Nocopy jtf_varchar2_table_2000
247 , oth_real_child_task Out Nocopy jtf_varchar2_table_100
248 , oth_real_parts_required OUT NOCOPY jtf_varchar2_table_100
249 , oth_real_access_hours OUT NOCOPY jtf_varchar2_table_100
250 , oth_real_after_hours OUT NOCOPY jtf_varchar2_table_100
251 , oth_real_customer_conf OUT NOCOPY jtf_varchar2_table_100
252 , oth_real_task_depend OUT NOCOPY jtf_varchar2_table_100
253 , p_vir_avail_type OUT NOCOPY jtf_varchar2_table_2000
254 );
255
256 FUNCTION get_tooltip_data_sch_advise(
257 p_task_id NUMBER
258 , p_resource_id NUMBER
259 , p_resource_type VARCHAR2
260 , p_start_date DATE
261 , p_end_date DATE
262 , p_duration NUMBER
263 , sch_adv_tz varchar2
264 , p_server_tz_code VARCHAR2
265 , p_client_tz_code VARCHAR2
266 , p_timezone_enb boolean
267 , p_inc_tz_desc varchar2
268 , p_inc_tz_code VARCHAR2
269 )
270 Return varchar2;
271 FUNCTION get_tooltip_data_sch_advise_cu(
272 p_task_id NUMBER
273 , p_resource_id NUMBER
274 , p_resource_type VARCHAR2
275 , p_start_date DATE
276 , p_end_date DATE
277 , p_duration NUMBER
278 , sch_adv_tz varchar2
279 , p_server_tz_code VARCHAR2
280 , p_client_tz_code VARCHAR2
281 , p_timezone_enb boolean
282 , p_inc_tz_desc varchar2
283 , p_inc_tz_code VARCHAR2
284 )
285 Return varchar2;
286
287
288
289 FUNCTION get_tooltip_data_gantt(
290 p_task_id number
291 ,p_resource_id number
292 ,p_resource_type varchar2
293 ,p_start_date date
294 ,p_end_date date
295 ,p_inc_tz_code VARCHAR2
296 ,p_server_tz_code VARCHAR2
297 ,p_client_tz_code VARCHAR2
298 ,p_timezone_enb boolean
299 ) return varchar2;
300
301
302 FUNCTION get_tooltip_data_gantt_cust(
303 p_task_id number
304 ,p_resource_id number
305 ,p_resource_type varchar2
306 ,p_start_date date
307 ,p_end_date date
308 ,p_inc_tz_code VARCHAR2
309 ,p_server_tz_code VARCHAR2
310 ,p_client_tz_code VARCHAR2
311 ,p_timezone_enb boolean
312 ) return varchar2;
313
314
315 FUNCTION get_tooltip_for_plan_task(
316 p_task_id number
317 , p_resource_id number
318 , p_resource_type varchar2
319 , p_start_date date
320 , p_end_date date
321 , p_duration Number default 0
322 , p_inc_tz_code VARCHAR2
323 , p_server_tz_code VARCHAR2
324 , p_client_tz_code VARCHAR2
325 , p_timezone_enb boolean
326 , sch_adv_tz varchar2
327 , p_inc_tz_desc varchar2)
328 Return varchar2;
329
330 Function get_green
331 Return Number;
332
333 Function get_gantt_task_color (
334 p_task_id In Number
335 , p_task_type_id In Number
336 , p_task_priority_id In Number
337 , p_assignment_status_id In Number
338 , p_task_assignment_id In Number
339 , p_actual_start_date In Date
340 , p_actual_end_date In Date
341 , p_actual_effort In Number
342 , p_actual_effort_uom In Varchar2
343 , p_planned_effort In Number
344 , p_planned_effort_uom In Varchar2
345 , p_scheduled_start_date In Date
346 , p_scheduled_end_date In Date
347 )
348 Return Number;
349
350 Procedure get_planned_task (
351 p_api_version In Number
352 , p_init_msg_list In Varchar2 DEFAULT NULL
353 , p_request_id In varchar2
354 , x_return_status Out Nocopy Varchar2
355 , x_msg_count Out Nocopy Number
356 , x_msg_data Out Nocopy Varchar2
357 , task_id Out Nocopy jtf_varchar2_table_100
358 , start_date Out Nocopy jtf_date_table
359 , end_date Out Nocopy jtf_date_table
360 , color Out Nocopy jtf_number_table
361 , Name Out Nocopy jtf_varchar2_table_100
362 , tooltip Out Nocopy jtf_varchar2_table_2000
363 , Duration Out Nocopy jtf_number_table
364 , task_type_id Out Nocopy jtf_number_table
365 , resource_key Out Nocopy jtf_varchar2_table_2000
366 , sch_adv_tz In Varchar2
367 );
368
369 Function convert_to_days (
370 p_duration Number
371 , p_uom Varchar2
372 , p_uom_hours Varchar2
373 )
374 Return Number;
375
376 Procedure drag_n_drop
377 ( p_api_version in number
378 , p_init_msg_list in varchar2 DEFAULT NULL
379 , p_commit in varchar2 DEFAULT NULL
380 , p_task_id in number
381 , p_task_assignment_id in number default null
382 , p_object_version_number in out nocopy number
383 , p_old_resource_type_code in varchar2
384 , p_new_resource_type_code in varchar2
385 , p_old_resource_id in number
386 , p_new_resource_id in number
387 , p_cancel_status_id in number
388 , p_assignment_status_id in number
389 , p_old_object_capacity_id in number
390 , p_new_object_capacity_id in number
391 , p_sched_travel_distance in number default null
392 , p_sched_travel_duration in number default null
393 , p_sched_travel_duration_uom in varchar2 default null
394 , p_old_shift_construct_id in number default null
395 , p_new_shift_construct_id in number default null
396 , p_shift_changed in boolean
397 , p_task_changed in boolean
398 , p_assignment_changed in boolean
399 , p_time_occupied in number
400 , p_new_sched_start_date in date
401 , p_new_sched_end_date in date
402 , p_update_plan_date in varchar2 default 'N'
403 , p_planned_start_date IN DATE DEFAULT NULL
404 , p_planned_end_date IN DATE DEFAULT NULL
405 , p_planned_effort IN NUMBER DEFAULT NULL
406 , p_planned_effort_uom IN VARCHAR2 DEFAULT NULL
407 , x_return_status out nocopy varchar2
408 , x_msg_count out nocopy number
409 , x_msg_data out nocopy varchar2
410 , x_task_assignment_id out nocopy number
411 , x_task_object_version_number out nocopy number
412 , x_task_status_id out nocopy number
413 , x_task_status_name out nocopy varchar2
414 , x_task_type_id out nocopy number
415 );
416
417 FUNCTION get_skilled_resources
418 ( p_task_id number
419 , p_start date
420 , p_end date
421 , p_resource_id number default null
422 , p_resource_type varchar2 default null
423 ) return Number;
424
425 FUNCTION get_resource_name ( p_res_id number
426 , p_res_type varchar2 ) return varchar2;
427 FUNCTION get_resource_type_name ( p_res_type varchar2 ) return varchar2;
428 FUNCTION g_do_match (
429 p_task_type_id IN NUMBER,
430 p_task_priority_id IN NUMBER,
431 p_assignment_status_id IN NUMBER,
432 p_escalated_task IN VARCHAR2
433 )
434 RETURN NUMBER;
435
436 PROCEDURE insert_rows
437 ( p_setup_type IN varchar2
438 , p_tooltip_setup_tbl IN tooltip_setup_tbl
439 , p_delete_rows IN boolean
440 , p_user_id IN number
441 , p_login_id IN number
442 );
443
444 PROCEDURE delete_rows(p_user_id number);
445
446
447
448 END CSF_GANTT_DATA_PKG;
[Home] [Help]