1 PACKAGE CSF_GANTT_DATA_PKG As
2 /*$Header: CSFGTPLS.pls 120.14.12010000.1 2008/07/28 07:37:06 appldev 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 Varchar2 (30)
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 PROCEDURE get_message_text
121 ( p_api_version IN Number
122 , p_init_msg_list IN Varchar2 DEFAULT NULL
123 , x_return_status OUT NOCOPY Varchar2
124 , x_msg_count OUT NOCOPY Number
125 , x_msg_data OUT NOCOPY Varchar2
126 , p_message_text OUT NOCOPY jtf_varchar2_table_2000
127 , p_message_code OUT NOCOPY jtf_varchar2_table_2000
128 );
129
130 Procedure get_dispatch_task_dtls (
131 p_api_version IN Number
132 , p_init_msg_list IN Varchar2 DEFAULT NULL
133 , x_return_status OUT NOCOPY Varchar2
134 , x_msg_count OUT NOCOPY Number
135 , x_msg_data OUT NOCOPY Varchar2
136 , p_start_date_range IN DATE
137 , p_end_date_range IN DATE
138 , p_res_id OUT NOCOPY jtf_number_table
139 , p_res_type OUT NOCOPY jtf_varchar2_table_2000
140 , p_res_name OUT NOCOPY jtf_varchar2_table_2000
141 , p_res_typ_name OUT NOCOPY jtf_varchar2_table_2000
142 , p_res_key OUT NOCOPY jtf_varchar2_table_2000
143 , p_trip_id OUT NOCOPY jtf_number_table
144 , p_shift_start_date OUT NOCOPY jtf_date_table
145 , p_shift_end_date OUT NOCOPY jtf_date_table
146 , p_block_trip OUT NOCOPY jtf_number_table
147 , p_shift_res_key OUT NOCOPY jtf_varchar2_table_2000
148 , p_vir_task_id OUT NOCOPY jtf_varchar2_table_100
149 , p_vir_start_date OUT NOCOPY jtf_date_table
150 , p_vir_end_date OUT NOCOPY jtf_date_table
151 , p_vir_color OUT NOCOPY jtf_number_table
152 , p_vir_name OUT NOCOPY jtf_varchar2_table_100
153 , p_vir_duration OUT NOCOPY jtf_number_table
154 , p_vir_task_type_id OUT NOCOPY jtf_number_table
155 , p_vir_tooltip OUT NOCOPY jtf_varchar2_table_2000
156 , p_vir_resource_key OUT NOCOPY jtf_varchar2_table_2000
157 , real_task_id OUT NOCOPY jtf_varchar2_table_100
158 , real_start_date OUT NOCOPY jtf_date_table
159 , real_end_date OUT NOCOPY jtf_date_table
160 , real_color OUT NOCOPY jtf_number_table
161 , real_NAME OUT NOCOPY jtf_varchar2_table_2000
162 , real_tooltip OUT NOCOPY jtf_varchar2_table_2000
163 , real_DURATION OUT NOCOPY jtf_number_table
164 , real_task_type_id OUT NOCOPY jtf_number_table
165 , real_resource_key OUT NOCOPY jtf_varchar2_table_2000
166 , real_parts_required OUT NOCOPY jtf_varchar2_table_100
167 , real_access_hours OUT NOCOPY jtf_varchar2_table_100
168 , real_after_hours OUT NOCOPY jtf_varchar2_table_100
169 , real_customer_conf OUT NOCOPY jtf_varchar2_table_100
170 , real_task_depend OUT NOCOPY jtf_varchar2_table_100
171 , real_child_task OUT NOCOPY jtf_varchar2_table_100
172 , p_show_arr_dep_tasks IN varchar2 DEFAULT 'N');
173
174 PROCEDURE get_schedule_advise_options
175 (
176 p_api_version IN NUMBER
177 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
178 , x_return_status OUT NOCOPY VARCHAR2
179 , x_msg_count OUT NOCOPY NUMBER
180 , x_msg_data OUT NOCOPY VARCHAR2
181 , p_display_option IN VARCHAR2
182 , p_resource_id IN NUMBER
183 , p_resource_type IN VARCHAR2
184 , p_req_id IN NUMBER
185 , p_par_task IN NUMBER
186 , p_task_id IN NUMBER
187 , p_res_id OUT NOCOPY jtf_number_table
188 , p_res_type OUT NOCOPY jtf_varchar2_table_2000
189 , p_res_name OUT NOCOPY jtf_varchar2_table_2000
190 , p_res_typ_name OUT NOCOPY jtf_varchar2_table_2000
191 , p_res_key OUT NOCOPY jtf_varchar2_table_2000
192 , p_cost OUT NOCOPY jtf_number_table
193 , p_start_date IN DATE
194 , p_end_date IN DATE
195 , sch_adv_tz In Varchar2
196 , inc_tz_code In Varchar2
197 , trip_id OUT NOCOPY jtf_number_table
198 , start_date OUT NOCOPY jtf_date_table
199 , end_date OUT NOCOPY jtf_date_table
200 , block_trip OUT NOCOPY jtf_number_table
201 , p_bck_res_key OUT NOCOPY jtf_varchar2_table_2000
202 , plan_task_key OUT NOCOPY jtf_varchar2_table_100
203 , plan_start_date OUT NOCOPY jtf_date_table
204 , plan_end_date OUT NOCOPY jtf_date_table
205 , plan_color OUT NOCOPY jtf_number_table
206 , plan_name OUT NOCOPY jtf_varchar2_table_2000
207 , plan_tooltip OUT NOCOPY jtf_varchar2_table_2000
208 , plan_duration OUT NOCOPY jtf_number_table
209 , plan_task_type_id OUT NOCOPY jtf_number_table
210 , plan_resource_key OUT NOCOPY jtf_varchar2_table_2000
211 , real_task_key OUT NOCOPY jtf_varchar2_table_100
212 , real_start_date OUT NOCOPY jtf_date_table
213 , real_end_date OUT NOCOPY jtf_date_table
214 , real_color OUT NOCOPY jtf_number_table
215 , real_name OUT NOCOPY jtf_varchar2_table_2000
216 , real_tooltip OUT NOCOPY jtf_varchar2_table_2000
217 , real_duration OUT NOCOPY jtf_number_table
218 , real_task_type_id OUT NOCOPY jtf_number_table
219 , real_resource_key OUT NOCOPY jtf_varchar2_table_2000
220 , child_task OUT Nocopy jtf_varchar2_table_100
221 , real_parts_required OUT NOCOPY jtf_varchar2_table_100
222 , real_access_hours OUT NOCOPY jtf_varchar2_table_100
223 , real_after_hours OUT NOCOPY jtf_varchar2_table_100
224 , real_customer_conf OUT NOCOPY jtf_varchar2_table_100
225 , real_task_depend OUT NOCOPY jtf_varchar2_table_100
226 , oth_real_task_id Out Nocopy jtf_varchar2_table_100
227 , oth_real_start_date Out Nocopy jtf_date_table
228 , oth_real_end_date Out Nocopy jtf_date_table
229 , oth_real_color Out Nocopy jtf_number_table
230 , oth_real_Name Out Nocopy jtf_varchar2_table_2000
231 , oth_real_Duration Out Nocopy jtf_number_table
232 , oth_real_task_type_id Out Nocopy jtf_number_table
233 , oth_real_resource_key Out Nocopy jtf_varchar2_table_2000
234 , oth_real_child_task Out Nocopy jtf_varchar2_table_100
235 , oth_real_parts_required OUT NOCOPY jtf_varchar2_table_100
236 , oth_real_access_hours OUT NOCOPY jtf_varchar2_table_100
237 , oth_real_after_hours OUT NOCOPY jtf_varchar2_table_100
238 , oth_real_customer_conf OUT NOCOPY jtf_varchar2_table_100
239 , oth_real_task_depend OUT NOCOPY jtf_varchar2_table_100
240 );
241
242 FUNCTION get_tooltip_data_sch_advise(
243 p_task_id NUMBER
244 , p_resource_id NUMBER
245 , p_resource_type VARCHAR2
246 , p_start_date DATE
247 , p_end_date DATE
248 , p_duration NUMBER
249 , sch_adv_tz varchar2
250 , p_server_tz_code VARCHAR2
251 , p_client_tz_code VARCHAR2
252 , p_timezone_enb boolean
253 , p_inc_tz_desc varchar2
254 , p_inc_tz_code VARCHAR2
255 )
256 Return varchar2;
257
258 FUNCTION get_tooltip_data_gantt(
259 p_task_id number
260 ,p_resource_id number
261 ,p_resource_type varchar2
262 ,p_start_date date
263 ,p_end_date date
264 ,p_inc_tz_code VARCHAR2
265 ,p_server_tz_code VARCHAR2
266 ,p_client_tz_code VARCHAR2
267 ,p_timezone_enb boolean
268 ) return varchar2;
269
270 FUNCTION get_tooltip_for_plan_task(
271 p_task_id number
272 , p_resource_id number
273 , p_resource_type varchar2
274 , p_start_date date
275 , p_end_date date
276 , p_duration Number default 0
277 , p_inc_tz_code VARCHAR2
278 , p_server_tz_code VARCHAR2
279 , p_client_tz_code VARCHAR2
280 , p_timezone_enb boolean
281 , sch_adv_tz varchar2
282 , p_inc_tz_desc varchar2)
283 Return varchar2;
284
285 Function get_green
286 Return Number;
287
288 Function get_gantt_task_color (
289 p_task_id In Number
290 , p_task_type_id In Number
291 , p_task_priority_id In Number
292 , p_assignment_status_id In Number
293 , p_task_assignment_id In Number
294 , p_actual_start_date In Date
295 , p_actual_end_date In Date
296 , p_actual_effort In Number
297 , p_actual_effort_uom In Varchar2
298 , p_planned_effort In Number
299 , p_planned_effort_uom In Varchar2
300 , p_scheduled_start_date In Date
301 , p_scheduled_end_date In Date
302 )
303 Return Number;
304
305 Procedure get_planned_task (
306 p_api_version In Number
307 , p_init_msg_list In Varchar2 DEFAULT NULL
308 , p_request_id In varchar2
309 , x_return_status Out Nocopy Varchar2
310 , x_msg_count Out Nocopy Number
311 , x_msg_data Out Nocopy Varchar2
312 , task_id Out Nocopy jtf_varchar2_table_100
313 , start_date Out Nocopy jtf_date_table
314 , end_date Out Nocopy jtf_date_table
315 , color Out Nocopy jtf_number_table
316 , Name Out Nocopy jtf_varchar2_table_100
317 , tooltip Out Nocopy jtf_varchar2_table_2000
318 , Duration Out Nocopy jtf_number_table
319 , task_type_id Out Nocopy jtf_number_table
320 , resource_key Out Nocopy jtf_varchar2_table_2000
321 , sch_adv_tz In Varchar2
322 );
323
324 Function convert_to_days (
325 p_duration Number
326 , p_uom Varchar2
327 , p_uom_hours Varchar2
328 )
329 Return Number;
330
331 Procedure drag_n_drop
332 ( p_api_version in number
333 , p_init_msg_list in varchar2 DEFAULT NULL
334 , p_commit in varchar2 DEFAULT NULL
335 , p_task_id in number
336 , p_task_assignment_id in number default null
337 , p_object_version_number in out nocopy number
338 , p_old_resource_type_code in varchar2
339 , p_new_resource_type_code in varchar2
340 , p_old_resource_id in number
341 , p_new_resource_id in number
342 , p_cancel_status_id in number
343 , p_assignment_status_id in number
344 , p_old_object_capacity_id in number
345 , p_new_object_capacity_id in number
346 , p_sched_travel_distance in number default null
347 , p_sched_travel_duration in number default null
348 , p_sched_travel_duration_uom in varchar2 default null
349 , p_old_shift_construct_id in number default null
350 , p_new_shift_construct_id in number default null
351 , p_shift_changed in boolean
352 , p_task_changed in boolean
353 , p_assignment_changed in boolean
354 , p_time_occupied in number
355 , p_new_sched_start_date in date
356 , p_new_sched_end_date in date
357 , p_update_plan_date in varchar2 default 'N'
358 , p_planned_start_date IN DATE DEFAULT NULL
359 , p_planned_end_date IN DATE DEFAULT NULL
360 , p_planned_effort IN NUMBER DEFAULT NULL
361 , p_planned_effort_uom IN VARCHAR2 DEFAULT NULL
362 , x_return_status out nocopy varchar2
363 , x_msg_count out nocopy number
364 , x_msg_data out nocopy varchar2
365 , x_task_assignment_id out nocopy number
366 , x_task_object_version_number out nocopy number
367 , x_task_status_id out nocopy number
368 , x_task_status_name out nocopy varchar2
369 , x_task_type_id out nocopy number
370 );
371
372 FUNCTION get_skilled_resources
373 ( p_task_id number
374 , p_start date
375 , p_end date
376 , p_resource_id number default null
377 , p_resource_type varchar2 default null
378 ) return Number;
379
380 FUNCTION get_resource_name ( p_res_id number
381 , p_res_type varchar2 ) return varchar2;
382 FUNCTION get_resource_type_name ( p_res_type varchar2 ) return varchar2;
383 FUNCTION g_do_match (
384 p_task_type_id IN NUMBER,
385 p_task_priority_id IN NUMBER,
386 p_assignment_status_id IN NUMBER,
387 p_escalated_task IN VARCHAR2
388 )
389 RETURN NUMBER;
390
391 END CSF_GANTT_DATA_PKG;
[Home] [Help]