[Home] [Help]
PACKAGE BODY: APPS.CSF_TRIPS_PUB
Source
1 PACKAGE BODY csf_trips_pub AS
2 /* $Header: CSFPTRPB.pls 120.59.12020000.7 2012/12/20 15:30:10 aditysin ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSF_TRIPS_PUB';
5 g_debug VARCHAR2(1);
6 g_debug_level NUMBER;
7 g_level_cp_output CONSTANT NUMBER := fnd_log.level_unexpected + 1;
8
9 g_hours_in_day CONSTANT NUMBER := 24;
10 g_mins_in_day CONSTANT NUMBER := 24 * 60;
11 g_secs_in_day CONSTANT NUMBER := 24 * 60 * 60;
12
13 g_dep_task_type_id CONSTANT NUMBER := 20;
14 g_arr_task_type_id CONSTANT NUMBER := 21;
15 g_dep_task_name VARCHAR2(30);
16 g_arr_task_name VARCHAR2(30);
17
18 g_tz_enabled VARCHAR2(1);
19 g_server_tz_code fnd_timezones_b.timezone_code%TYPE;
20 g_client_tz_code fnd_timezones_b.timezone_code%TYPE;
21 g_datetime_fmt_mask fnd_profile_option_values.profile_option_value%TYPE;
22 g_duration_uom mtl_uom_conversions.uom_code%TYPE;
23 g_overtime NUMBER;
24
25 g_assigned_status_id NUMBER;
26 g_planned_status_id NUMBER;
27 g_blocked_planned_status_id NUMBER;
28 g_blocked_assigned_status_id NUMBER;
29 g_closed_status_id NUMBER;
30 g_res_add_prof VARCHAR2(200);
31 G_SHIFT_TYPE VARCHAR2(200);
32 g_init_timezone BOOLEAN := FALSE;
33 TYPE message_rec_type IS RECORD(
34 message_name fnd_new_messages.message_name%TYPE
35 , message_type VARCHAR2(1)
36 , resource_id NUMBER
37 , resource_type jtf_objects_b.object_code%TYPE
38 , start_datetime DATE
39 , end_datetime DATE
40 , trip_id NUMBER
41 , error_reason fnd_new_messages.message_text%TYPE
42 );
43
44 TYPE message_tbl_type IS TABLE OF message_rec_type
45 INDEX BY BINARY_INTEGER;
46
47 TYPE number_tbl_type IS TABLE OF NUMBER
48 INDEX BY BINARY_INTEGER;
49
50 g_error_message CONSTANT VARCHAR2(1) := 'E';
51 g_warning_message CONSTANT VARCHAR2(1) := 'W';
52 g_success_message CONSTANT VARCHAR2(1) := 'S';
53
54 g_messages message_tbl_type;
55 g_suppress_res_info BOOLEAN;
56
57
58 FUNCTION check_dst(p_resource_id IN number ,p_start_server IN date,p_end_server IN date)
59 RETURN VARCHAR2;
60 Function Get_Res_Timezone_Id ( P_Resource_Id IN Number ) RETURN Number;
61 Function ServerDT_To_ResourceDt ( P_Server_DtTime IN date, P_Server_TZ_Id IN Number , p_Resource_TZ_id IN Number ) RETURN date;
62
63 PROCEDURE check_dangling_tasks(p_resource_tbl IN csf_resource_pub.resource_tbl_type
64 , p_start IN DATE
65 , p_end IN DATE
66 , x_return_status OUT NOCOPY VARCHAR2
67 , x_msg_data OUT NOCOPY VARCHAR2
68 , x_msg_count OUT NOCOPY NUMBER);
69
70 PROCEDURE check_duplicate_tasks(p_resource_tbl IN csf_resource_pub.resource_tbl_type
71 , p_start IN DATE
72 , p_end IN DATE
73 , x_return_status OUT NOCOPY VARCHAR2
74 , x_msg_data OUT NOCOPY VARCHAR2
75 , x_msg_count OUT NOCOPY NUMBER);
76 PROCEDURE check_multiple_trip_tasks(p_resource_tbl IN csf_resource_pub.resource_tbl_type
77 , p_start IN DATE
78 , p_end IN DATE
79 , x_return_status OUT NOCOPY VARCHAR2
80 , x_msg_data OUT NOCOPY VARCHAR2
81 , x_msg_count OUT NOCOPY NUMBER);
82
83 /******************************************************************************************
84 * *
85 * Private Utility Functions and Procedures *
86 * *
87 *******************************************************************************************/
88
89 PROCEDURE debug(p_message VARCHAR2, p_module VARCHAR2, p_level NUMBER) IS
90 BEGIN
91 IF p_level = g_level_cp_output AND fnd_file.output > 0 THEN
92 fnd_file.put_line(fnd_file.output, p_message);
93 END IF;
94
95 IF g_debug = 'Y' AND p_level >= g_debug_level THEN
96 IF fnd_file.log > 0 THEN
97 IF p_message = ' ' THEN
98 fnd_file.put_line(fnd_file.log, '');
99 ELSE
100 fnd_file.put_line(fnd_file.log, rpad(p_module, 20) || ': ' || p_message);
101 END IF;
102 END IF;
103 IF ( p_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
104 THEN
105 fnd_log.string(p_level, 'csf.plsql.CSF_TRIPS_PUB.' || p_module, p_message);
106 END IF;
107 END IF;
108 --dbms_output.put_line(rpad(p_module, 20) || ': ' || p_message);
109 END debug;
110
111 FUNCTION format_date(p_date IN DATE, p_convert_to_client_tz VARCHAR2 DEFAULT NULL)
112 RETURN VARCHAR2 IS
113 l_date DATE;
114 BEGIN
115 l_date := p_date;
116 IF p_convert_to_client_tz IS NULL OR p_convert_to_client_tz = fnd_api.g_true THEN
117 -- AOL doesnt initialize FND_DATE package properly. Refer bugs 3183418 and 3115188.
118 -- Because of this, dates werent printed with TZ Conversion. Bypassing FND_DATE.
119 IF g_tz_enabled = 'Y' THEN
120 l_date := fnd_timezones_pvt.adjust_datetime(
121 date_time => p_date
122 , from_tz => g_server_tz_code
123 , to_tz => g_client_tz_code
124 );
125 END IF;
126 END IF;
127 RETURN to_char(l_date, g_datetime_fmt_mask);
128 END format_date;
129
130 FUNCTION get_resource_info(p_resource_id NUMBER, p_resource_type VARCHAR2)
131 RETURN VARCHAR2 IS
132 l_resource_info csf_resource_pub.resource_rec_type;
133 BEGIN
134 l_resource_info := csf_resource_pub.get_resource_info(p_resource_id, p_resource_type);
135
136 RETURN l_resource_info.resource_name
137 || ' ('
138 || csf_resource_pub.get_resource_type_name(l_resource_info.resource_type)
139 || ', '
140 || l_resource_info.resource_number
141 || ')';
142 END get_resource_info;
143
144 FUNCTION time_overlaps(p_trip trip_rec_type, p_shift csf_resource_pub.shift_rec_type)
145 RETURN BOOLEAN IS
146 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_TIME_OVERLAPS';
147 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
148 BEGIN
149 IF l_debug THEN
150 debug( ' Inside procedure CHECK TIME OVERLAP', l_api_name, fnd_log.level_statement);
151 debug( ' Overtime value # '||g_overtime, l_api_name, fnd_log.level_statement);
152 debug( ' Checking for overlap in trip start#' || format_date(p_trip.start_date_time) ||
153 ' is <= shift endtime + overtime #'|| format_date(p_shift.end_datetime + g_overtime) ||
154 ' and overlap in trip end # '||format_date(p_trip.end_date_time + g_overtime)||
155 ' is >= starttime + overtime # '||format_date(p_shift.start_datetime), l_api_name, fnd_log.level_statement);
156 debug( ' Outside procedure CHECK TIME OVERLAP', l_api_name, fnd_log.level_statement);
157 END IF;
158 RETURN p_trip.start_date_time <= (p_shift.end_datetime + g_overtime)
159 AND (p_trip.end_date_time + g_overtime) >= p_shift.start_datetime;
160 END time_overlaps;
161
162 FUNCTION time_overlaps(p_trip trip_rec_type, p_start DATE, p_end DATE)
163 RETURN BOOLEAN IS
164 BEGIN
165 RETURN p_trip.start_date_time < p_end
166 AND (p_trip.end_date_time + g_overtime) > p_start;
167 END time_overlaps;
168
169 PROCEDURE add_message(
170 p_trip trip_rec_type
171 , p_reason VARCHAR2 DEFAULT NULL
172 , p_msg_name VARCHAR2 DEFAULT NULL
173 , p_msg_type VARCHAR2 DEFAULT NULL
174 ) IS
175 i PLS_INTEGER;
176 BEGIN
177 i := g_messages.COUNT + 1;
178 g_messages(i).message_name := p_msg_name;
179 g_messages(i).message_type := NVL(p_msg_type, g_success_message);
180 g_messages(i).error_reason := p_reason;
181 g_messages(i).resource_id := p_trip.resource_id;
182 g_messages(i).resource_type := p_trip.resource_type;
183 g_messages(i).start_datetime := p_trip.start_date_time;
184 g_messages(i).end_datetime := p_trip.end_date_time;
185 g_messages(i).trip_id := p_trip.trip_id;
186 END add_message;
187
188 PROCEDURE add_message(
189 p_res_id NUMBER
190 , p_res_type VARCHAR2
191 , p_start DATE
192 , p_end DATE
193 , p_reason VARCHAR2 DEFAULT NULL
194 , p_msg_name VARCHAR2 DEFAULT NULL
195 , p_msg_type VARCHAR2 DEFAULT NULL
196 ) IS
197 i PLS_INTEGER;
198 BEGIN
199 i := g_messages.COUNT + 1;
200 g_messages(i).resource_id := p_res_id;
201 g_messages(i).resource_type := p_res_type;
202 g_messages(i).start_datetime := p_start;
203 g_messages(i).end_datetime := p_end;
204 g_messages(i).message_name := p_msg_name;
205 g_messages(i).message_type := NVL(p_msg_type, g_success_message);
206 g_messages(i).error_reason := p_reason;
207 END add_message;
208
209 PROCEDURE process_messages(
210 p_init_msg_list IN VARCHAR2
211 , x_return_status OUT NOCOPY VARCHAR2
212 , p_action IN VARCHAR2
213 , p_trip_id IN NUMBER
214 , p_start_date IN DATE
215 , p_end_date IN DATE
216 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
217 ) IS
218 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
219 l_success NUMBER;
220 l_failed NUMBER;
221 l_action_name fnd_flex_values_tl.flex_value_meaning%TYPE;
222 l_res_name jtf_rs_resource_extns_tl.resource_name%TYPE;
223
224 CURSOR c_action_name IS
225 SELECT v.flex_value_meaning meaning
226 FROM fnd_flex_value_sets s, fnd_flex_values_vl v
227 WHERE s.flex_value_set_name = 'CSF_GTR_ACTIONS'
228 AND s.flex_value_set_id = v.flex_value_set_id
229 AND v.flex_value = p_action;
230 BEGIN
231
232 x_return_status := fnd_api.g_ret_sts_success;
233
234 -- First Clear the Message Stack if the API is given the permission to clear stack.
235 IF fnd_api.to_boolean(p_init_msg_list) THEN
236 fnd_msg_pub.initialize;
237 END IF;
238
239 l_success := 0;
240 l_failed := 0;
241
242 FOR i IN 1..g_messages.COUNT LOOP
243 IF g_messages(i).message_type IN (g_error_message, g_warning_message) THEN
244 fnd_message.set_name('CSF', NVL(g_messages(i).message_name, 'CSF_PROCESS_TRIP_FAILED'));
245
246 IF g_messages(i).resource_id IS NOT NULL THEN
247 IF g_suppress_res_info = TRUE THEN
248 fnd_message.set_token('RESOURCE', '');
249 ELSE
250 fnd_message.set_token('RESOURCE', get_resource_info(g_messages(i).resource_id, g_messages(i).resource_type));
251 END IF;
252 END IF;
253
254 IF g_messages(i).start_datetime IS NOT NULL THEN
255 fnd_message.set_token('START_TIME', format_date(g_messages(i).start_datetime));
256 END IF;
257
258 IF g_messages(i).end_datetime IS NOT NULL THEN
259 fnd_message.set_token('END_TIME', format_date(g_messages(i).end_datetime));
260 END IF;
261
262 IF g_messages(i).error_reason IS NOT NULL THEN
263 fnd_message.set_token('REASON', g_messages(i).error_reason);
264 END IF;
265
266 fnd_msg_pub.ADD;
267
268 IF g_messages(i).message_type = g_error_message THEN
269 l_failed := l_failed + 1;
270 ELSE
271 l_success := l_success + 1;
272 END IF;
273 ELSE
274 IF l_debug THEN
275 debug( 'Trip#' || g_messages(i).trip_id
276 || ' for resource ' || get_resource_info(g_messages(i).resource_id, g_messages(i).resource_type)
277 || ' between ' || format_date(g_messages(i).start_datetime)
278 || ' and ' || format_date(g_messages(i).end_datetime)
279 || ' processed successfully'
280 , 'PROCESS_ACTION'
281 , fnd_log.level_event
282 );
283 END IF;
284 l_success := l_success + 1;
285 END IF;
286 END LOOP;
287
288 IF l_failed > 0 THEN
289 x_return_status := fnd_api.g_ret_sts_error;
290 END IF;
291
292 -- There is only trip involved... and therefore no need to status message.
293 IF p_trip_id IS NOT NULL THEN
294 RETURN;
295 END IF;
296
297 OPEN c_action_name;
298 FETCH c_action_name INTO l_action_name;
299 CLOSE c_action_name;
300
301 IF p_resource_tbl.COUNT = 1 THEN
302 l_res_name := csf_resource_pub.get_resource_name(p_resource_tbl(1).resource_id, p_resource_tbl(1).resource_type);
303 ELSE
304 l_res_name := '';
305 END IF;
306
307 IF l_failed > 0 THEN
308 fnd_message.set_name('CSF', 'CSF_TRIPS_ACTION_WARN');
309 fnd_message.set_token('FAILED', l_failed);
310 ELSE
311 fnd_message.set_name('CSF', 'CSF_TRIPS_ACTION_SUCC');
312 END IF;
313
314 fnd_message.set_token('SUCCESS', l_success);
315 fnd_message.set_token('ACTION', l_action_name);
316 fnd_message.set_token('RESOURCE', l_res_name);
317 fnd_message.set_token('START_DATE', p_start_date);
318 fnd_message.set_token('END_DATE', p_end_date);
319 fnd_msg_pub.ADD;
320 END process_messages;
321
322 PROCEDURE init_package IS
323 BEGIN
324 g_duration_uom := fnd_profile.value('CSF_UOM_MINUTES');
325 g_overtime := NVL(CSR_SCHEDULER_PUB.GET_SCH_PARAMETER_VALUE('spMaxOvertime'), 0) / g_mins_in_day;
326
327
328 g_debug := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
329 g_debug_level := NVL(fnd_profile.value_specific('AFLOG_LEVEL'), fnd_log.level_event);
330 g_datetime_fmt_mask := NVL(fnd_profile.value('ICX_DATE_FORMAT_MASK'), 'DD-MON-YYYY') || ' HH24:MI';
331 g_tz_enabled := fnd_timezones.timezones_enabled;
332 g_server_tz_code := fnd_timezones.get_server_timezone_code;
333 g_client_tz_code := fnd_timezones.get_client_timezone_code;
334
335 g_planned_status_id := fnd_profile.value('CSF_DEFAULT_TASK_PLANNED_STATUS');
336 g_assigned_status_id := fnd_profile.value('CSF_DEFAULT_TASK_ASSIGNED_STATUS');
337 g_blocked_planned_status_id := fnd_profile.value('CSF_DEFAULT_TASK_BLOCKED_PLAN_STATUS');
338 g_blocked_assigned_status_id := fnd_profile.value('CSF_DEFAULT_TASK_BLOCKEDASS_STATUS');
339 g_closed_status_id := fnd_profile.value('CSF_DFLT_AUTO_CLOSE_TASK_STATUS');
340
341 SELECT name INTO g_dep_task_name
342 FROM jtf_task_types_vl WHERE task_type_id = g_dep_task_type_id;
343
344 SELECT name INTO g_arr_task_name
345 FROM jtf_task_types_vl WHERE task_type_id = g_arr_task_type_id;
346
347 --EXECUTE IMMEDIATE 'alter session set timed_statistics=true';
348 --EXECUTE IMMEDIATE 'alter session set statistics_level=all';
349 --EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
350 EXCEPTION
351 WHEN OTHERS THEN
352 debug('Unable to initialize the Package - SQLCODE = ' || SQLCODE || ' : SQLERRM = ' || SQLERRM, 'INIT', fnd_log.level_unexpected);
353 END;
354
355 FUNCTION trip_has_active_tasks(p_trip_id NUMBER)
356 RETURN BOOLEAN IS
357 CURSOR c_active_tasks_exist IS
358 SELECT 1
359 FROM cac_sr_object_capacity oc
360 WHERE object_capacity_id = p_trip_id
361 AND EXISTS (SELECT 1
362 FROM jtf_task_assignments ta
363 , jtf_task_statuses_b ts
364 , jtf_tasks_b t
365 WHERE ta.object_capacity_id = oc.object_capacity_id
366 AND ts.task_status_id = ta.assignment_status_id
367 AND NVL(ts.closed_flag, 'N') = 'N'
368 AND NVL(ts.completed_flag, 'N') = 'N'
369 AND NVL(ts.cancelled_flag, 'N') = 'N'
370 AND NVL(ts.rejected_flag, 'N') = 'N'
371 AND t.task_id = ta.task_id
372 AND NVL(t.deleted_flag, 'N') = 'N'
373 AND t.task_type_id NOT IN (20, 21));
374 l_result NUMBER;
375 BEGIN
376 OPEN c_active_tasks_exist;
377 FETCH c_active_tasks_exist INTO l_result;
378 CLOSE c_active_tasks_exist;
379
380 RETURN l_result IS NOT NULL;
381 END trip_has_active_tasks;
382
383 -- Returns all the Trips which overlaps with the Passed Timings for the Resource
384 FUNCTION find_trips(
385 p_resource_tbl IN csf_resource_pub.resource_tbl_type
386 , p_start_date_time IN DATE
387 , p_end_date_time IN DATE
388 , p_overtime_flag IN VARCHAR2 DEFAULT NULL
389 ) RETURN trip_tbl_type IS
390
391 l_trips_count NUMBER;
392 l_trips trip_tbl_type;
393 l_overtime NUMBER;
394 i PLS_INTEGER;
395
396 CURSOR c_trips (p_resource_id NUMBER, p_resource_type VARCHAR2) IS
397 SELECT *
398 FROM cac_sr_object_capacity
399 WHERE object_id = p_resource_id
400 AND object_type = p_resource_type
401 AND p_start_date_time <= (end_date_time + l_overtime)
402 AND p_end_date_time >= start_date_time
403 ORDER BY start_date_time, object_capacity_id;
404
405 BEGIN
406 l_overtime := 0;
407 IF p_overtime_flag IS NULL OR p_overtime_flag = fnd_api.g_true THEN
408 l_overtime := g_overtime;
409 END IF;
410
411 l_trips_count := 0;
412
413 i := p_resource_tbl.FIRST;
414 -- Find Trips for each resource and add it to the output table.
415 WHILE i IS NOT NULL LOOP
416 -- Loop through all the Trips found for the criteria specified.
417 FOR v_trip IN c_trips(p_resource_tbl(i).resource_id, p_resource_tbl(i).resource_type) LOOP
418 l_trips_count := l_trips_count + 1;
419
420 l_trips(l_trips_count).trip_id := v_trip.object_capacity_id;
421 l_trips(l_trips_count).object_version_number := v_trip.object_version_number;
422 l_trips(l_trips_count).resource_type := v_trip.object_type;
423 l_trips(l_trips_count).resource_id := v_trip.object_id;
424 l_trips(l_trips_count).start_date_time := v_trip.start_date_time;
425 l_trips(l_trips_count).end_date_time := v_trip.end_date_time;
426 l_trips(l_trips_count).available_hours := v_trip.available_hours;
427 l_trips(l_trips_count).available_hours_before := v_trip.available_hours_before;
428 l_trips(l_trips_count).available_hours_after := v_trip.available_hours_after;
429 l_trips(l_trips_count).schedule_detail_id := v_trip.schedule_detail_id;
430 l_trips(l_trips_count).status := v_trip.status;
431 l_trips(l_trips_count).availability_type := v_trip.availability_type;
432 END LOOP;
433
434 i := p_resource_tbl.NEXT(i);
435 END LOOP;
436
437 RETURN l_trips;
438 END find_trips;
439
440 FUNCTION get_trip(p_trip_id IN NUMBER) RETURN trip_rec_type AS
441 l_trip trip_rec_type;
442 CURSOR c_trip IS
443 SELECT *
444 FROM cac_sr_object_capacity
445 WHERE object_capacity_id = p_trip_id;
446 BEGIN
447 FOR v_trip IN c_trip LOOP
448 l_trip.trip_id := v_trip.object_capacity_id;
449 l_trip.object_version_number := v_trip.object_version_number;
450 l_trip.resource_type := v_trip.object_type;
451 l_trip.resource_id := v_trip.object_id;
452 l_trip.start_date_time := v_trip.start_date_time;
453 l_trip.end_date_time := v_trip.end_date_time;
454 l_trip.available_hours := v_trip.available_hours;
455 l_trip.available_hours_before := v_trip.available_hours_before;
456 l_trip.available_hours_after := v_trip.available_hours_after;
457 l_trip.schedule_detail_id := v_trip.schedule_detail_id;
458 l_trip.status := v_trip.status;
459 l_trip.availability_type := v_trip.availability_type;
460 END LOOP;
461
462 RETURN l_trip;
463 END get_trip;
464
465 PROCEDURE create_shift_tasks(
466 p_api_version IN NUMBER
467 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
468 , p_commit IN VARCHAR2 DEFAULT NULL
469 , x_return_status OUT NOCOPY VARCHAR2
470 , x_msg_data OUT NOCOPY VARCHAR2
471 , x_msg_count OUT NOCOPY NUMBER
472 , p_resource_id IN NUMBER
473 , p_resource_type IN VARCHAR2
474 , p_start_date_time IN DATE
475 , p_end_date_time IN DATE
476 , p_create_dep_task IN BOOLEAN
477 , p_create_arr_task IN BOOLEAN
478 , p_res_shift_add IN VARCHAR2 default null
479 , x_dep_task_id OUT NOCOPY NUMBER
480 , x_arr_task_id OUT NOCOPY NUMBER
481 ) IS
482 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SHIFT_TASKS';
483 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
484 l_address csf_resource_address_pvt.address_rec_type;
485 l_task_assign_tbl jtf_tasks_pub.task_assign_tbl;
486
487 CURSOR c_obj_capacity_det
488 IS
489 select object_id,object_type,start_date_time,end_date_time
490 from cac_sr_object_capacity
491 where object_id = p_resource_id
492 and object_type = p_resource_type
493 and start_date_time = p_start_date_time
494 and end_date_time = p_end_date_time;
495
496 l_row_obj_cap c_obj_capacity_det%rowtype;
497 BEGIN
498
499 IF p_create_dep_task = FALSE AND p_create_arr_task = FALSE THEN
500 RETURN;
501 END IF;
502
503 -- Get the Resource's Address for this Date
504 l_address := csf_resource_pub.get_resource_party_address(
505 p_res_id => p_resource_id
506 , p_res_type => p_resource_type
507 , p_date => p_start_date_time
508 , p_res_shift_add => g_res_add_prof
509 );
510
511 IF l_debug THEN
512 debug(' Got the Party Site ID ' || l_address.party_site_id || ' for the resource on ' || p_start_date_time, l_api_name, fnd_log.level_statement);
513 END IF;
514
515 IF l_address.party_site_id IS NULL THEN
516 IF l_debug THEN
517 x_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
518 debug(' CSF_RESOURCE_ADDRESS_PVT failed to give Party Site ID' || x_msg_data, l_api_name, fnd_log.level_error);
519 END IF;
520 fnd_message.set_name('CSF', 'CSF_RESOURCE_NO_ACTIVE_PARTY');
521 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
522 fnd_message.set_token('DATE', format_date(p_start_date_time));
523 fnd_msg_pub.ADD;
524 RAISE fnd_api.g_exc_error;
525 END IF;
526
527 -- Departure and Arrival Task Resource Assignment
528 l_task_assign_tbl(1).resource_id := p_resource_id;
529 l_task_assign_tbl(1).resource_type_code := p_resource_type;
530 l_task_assign_tbl(1).assignment_status_id := g_assigned_status_id;
531
532
533
534 -- Create the Departure Task
535 IF p_create_dep_task THEN
536
537 open c_obj_capacity_det;
538 fetch c_obj_capacity_det into l_row_obj_cap;
539 close c_obj_capacity_det;
540
541 IF l_row_obj_cap.start_date_time is null
542 THEN
543
544 jtf_tasks_pub.create_task(
545 p_api_version => 1.0
546 , p_task_name => g_dep_task_name
547 , p_task_type_id => g_dep_task_type_id
548 , p_task_status_id => g_assigned_status_id
549 , p_owner_id => p_resource_id
550 , p_owner_type_code => p_resource_type
551 , p_address_id => l_address.party_site_id
552 , p_customer_id => l_address.party_id
553 , p_planned_start_date => p_start_date_time
554 , p_planned_end_date => p_start_date_time
555 , p_scheduled_start_date => p_start_date_time
556 , p_scheduled_end_date => p_start_date_time
557 , p_duration => 0
558 , p_duration_uom => g_duration_uom
559 , p_bound_mode_code => 'BTS'
560 , p_soft_bound_flag => 'Y'
561 , p_task_assign_tbl => l_task_assign_tbl
562 , x_return_status => x_return_status
563 , x_msg_count => x_msg_count
564 , x_msg_data => x_msg_data
565 , x_task_id => x_dep_task_id
566 );
567
568 IF x_return_status <> fnd_api.g_ret_sts_success THEN
569 fnd_message.set_name('CSF', 'CSF_TASK_CREATE_FAIL');
570 fnd_message.set_token('TASK_NAME', g_dep_task_name);
571 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
572 fnd_msg_pub.ADD;
573
574 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
575 RAISE fnd_api.g_exc_unexpected_error;
576 END IF;
577 RAISE fnd_api.g_exc_error;
578 END IF;
579
580 IF l_debug THEN
581 debug(' Created Departure Task - Task ID = ' || x_dep_task_id, l_api_name, fnd_log.level_statement);
582 END IF;
583 END IF; -- end if for l_row_obj_cap
584 END IF;
585
586 -- Create the Arrival Task
587 IF p_create_arr_task THEN
588
589 open c_obj_capacity_det;
590 fetch c_obj_capacity_det into l_row_obj_cap;
591 close c_obj_capacity_det;
592
593 IF l_row_obj_cap.end_date_time is null
594 THEN
595
596 jtf_tasks_pub.create_task(
597 p_api_version => 1.0
598 , p_task_name => g_arr_task_name
599 , p_task_type_id => g_arr_task_type_id
600 , p_task_status_id => g_assigned_status_id
601 , p_owner_id => p_resource_id
602 , p_owner_type_code => p_resource_type
603 , p_address_id => l_address.party_site_id
604 , p_customer_id => l_address.party_id
605 , p_planned_start_date => p_end_date_time
606 , p_planned_end_date => p_end_date_time
607 , p_scheduled_start_date => p_end_date_time
608 , p_scheduled_end_date => p_end_date_time
609 , p_duration => 0
610 , p_duration_uom => g_duration_uom
611 , p_bound_mode_code => 'BTS'
612 , p_soft_bound_flag => 'Y'
613 , p_task_assign_tbl => l_task_assign_tbl
614 , x_return_status => x_return_status
615 , x_msg_count => x_msg_count
616 , x_msg_data => x_msg_data
617 , x_task_id => x_arr_task_id
618 );
619
620 IF x_return_status <> fnd_api.g_ret_sts_success THEN
621 fnd_message.set_name('CSF', 'CSF_TASK_CREATE_FAIL');
622 fnd_message.set_token('TASK_NAME', g_arr_task_name);
623 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
624 fnd_msg_pub.ADD;
625
626 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
627 RAISE fnd_api.g_exc_unexpected_error;
628 END IF;
629 RAISE fnd_api.g_exc_error;
630 END IF;
631
632 IF l_debug THEN
633 debug(' Created Arrival Task - Task ID = ' || x_arr_task_id, l_api_name, fnd_log.level_statement);
634 END IF;
635 END IF;-- end if for l_row_obj_cap
636 END IF;
637 EXCEPTION
638 WHEN fnd_api.g_exc_error THEN
639 x_return_status := fnd_api.g_ret_sts_error;
640 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
641 WHEN fnd_api.g_exc_unexpected_error THEN
642 if SQLCODE =1 then
643 x_return_status := fnd_api.g_ret_sts_error;
644 else
645 x_return_status := fnd_api.g_ret_sts_unexp_error;
646 end if;
647 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
648 WHEN OTHERS THEN
649 x_return_status := fnd_api.g_ret_sts_unexp_error;
650 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
651 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
652 END IF;
653 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
654 END create_shift_tasks;
655
656 FUNCTION get_new_task_status(p_action VARCHAR2, p_current_status NUMBER)
657 RETURN NUMBER IS
658 BEGIN
659 IF p_action = g_action_block_trip THEN
660 IF p_current_status = g_planned_status_id THEN
661 RETURN g_blocked_planned_status_id;
662 ELSIF p_current_status = g_assigned_status_id THEN
663 RETURN g_blocked_assigned_status_id;
664 ELSE
665 RETURN NULL;
666 END IF;
667 ELSIF p_action = g_action_unblock_trip THEN
668 IF p_current_status = g_blocked_planned_status_id THEN
669 RETURN g_planned_status_id;
670 ELSIF p_current_status = g_blocked_assigned_status_id THEN
671 RETURN g_assigned_status_id;
672 ELSE
673 RETURN NULL;
674 END IF;
675 ELSIF p_action = g_action_close_trip THEN
676 RETURN g_closed_status_id;
677 ELSE
678 RETURN NULL;
679 END IF;
680 END get_new_task_status;
681
682 PROCEDURE new_trip(
683 x_return_status OUT NOCOPY VARCHAR2
684 , x_msg_data OUT NOCOPY VARCHAR2
685 , x_msg_count OUT NOCOPY NUMBER
686 , p_resource_id IN NUMBER
687 , p_resource_type IN VARCHAR2
688 , p_start_date_time IN DATE
689 , p_end_date_time IN DATE
690 , p_status IN NUMBER DEFAULT NULL
691 , p_schedule_detail_id IN NUMBER DEFAULT NULL
692 , p_find_tasks IN VARCHAR2 DEFAULT NULL
693 , p_dep_task_id IN NUMBER DEFAULT NULL
694 , p_arr_task_id IN NUMBER DEFAULT NULL
695 , x_trip OUT NOCOPY trip_rec_type
696 ) IS
697 l_api_name CONSTANT VARCHAR2(30) := 'NEW_TRIP';
698 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
699
700 l_dep_task_id NUMBER;
701 l_arr_task_id NUMBER;
702
703 CURSOR c_linkable_tasks IS
704 SELECT ta.task_assignment_id
705 , ta.object_version_number
706 , ta.task_id
707 , ta.booking_start_date
708 , ta.booking_end_date
709 , csf_util_pvt.convert_to_minutes(
710 ta.sched_travel_duration
711 , ta.sched_travel_duration_uom
712 ) travel_time
713 FROM jtf_task_assignments ta
714 , jtf_task_statuses_b ts
715 , jtf_tasks_b t
716 WHERE ta.resource_id = p_resource_id
717 AND ta.resource_type_code = p_resource_type
718 AND ta.assignee_role = 'ASSIGNEE'
719 AND ts.task_status_id = ta.assignment_status_id
720 AND NVL(ts.closed_flag, 'N') = 'N'
721 AND NVL(ts.completed_flag, 'N') = 'N'
722 AND NVL(ts.cancelled_flag, 'N') = 'N'
723 AND t.task_id = ta.task_id
724 AND NVL(t.deleted_flag, 'N') <> 'Y'
725 AND ta.booking_start_date <= (p_end_date_time + g_overtime)
726 AND ta.booking_end_date >= p_start_date_time
727 AND (t.task_type_id NOT IN (20, 21) OR t.task_id IN (l_dep_task_id, l_arr_task_id));
728
729 l_available_hours NUMBER;
730 l_time_occupied NUMBER;
731
732 i PLS_INTEGER;
733 l_object_capacity_tbl cac_sr_object_capacity_pub.object_capacity_tbl_type;
734 l_object_tasks_tbl cac_sr_object_capacity_pub.object_tasks_tbl_type;
735 BEGIN
736 SAVEPOINT csf_new_trip;
737
738 x_return_status := fnd_api.g_ret_sts_success;
739
740 IF l_debug THEN
741 debug(' Creating Trip between ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
742 END IF;
743
744 -- Trip Available Hours
745 l_available_hours := (p_end_date_time - p_start_date_time) * g_hours_in_day;
746
747 -- Check#3 - The Trip Duration should be lesser than 24 Hours.
748 IF l_available_hours > g_hours_in_day THEN
749 IF check_dst(p_resource_id,p_start_date_time,p_end_date_time) = 'FALSE'
750 THEN
751 IF l_debug THEN
752 debug(' The specified Trip Length is greater than one day', l_api_name, fnd_log.level_error);
753 END IF;
754 fnd_message.set_name('CSF', 'CSF_TRIP_LENGTH_MORE_THAN_DAY');
755 fnd_msg_pub.ADD;
756 RAISE fnd_api.g_exc_error;
757 END IF;
758
759 END IF;
760
761
762 -- Create new Shift Tasks for the Trip to be created.
763 IF p_dep_task_id IS NULL OR p_arr_task_id IS NULL THEN
764 create_shift_tasks(
765 p_api_version => 1.0
766 , p_init_msg_list => fnd_api.g_false
767 , p_commit => fnd_api.g_false
768 , x_return_status => x_return_status
769 , x_msg_data => x_msg_data
770 , x_msg_count => x_msg_count
771 , p_resource_id => p_resource_id
772 , p_resource_type => p_resource_type
773 , p_start_date_time => p_start_date_time
774 , p_end_date_time => p_end_date_time
775 , p_create_dep_task => p_dep_task_id IS NULL
776 , p_create_arr_task => p_arr_task_id IS NULL
777 , x_dep_task_id => l_dep_task_id
778 , x_arr_task_id => l_arr_task_id
779 );
780
781 IF x_return_status <> fnd_api.g_ret_sts_success THEN
782 IF l_debug THEN
783 debug(' Unable to Create Shift Tasks: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
784 END IF;
785 IF x_return_status = fnd_api.g_ret_sts_error THEN
786 RAISE fnd_api.g_exc_error;
787 ELSE
788 RAISE fnd_api.g_exc_unexpected_error;
789 END IF;
790 END IF;
791 IF l_debug THEN
792 debug(' Created new Shift Tasks - Dep#' || l_dep_task_id || ' : Arr#' || l_arr_task_id, l_api_name, fnd_log.level_statement);
793 END IF;
794 l_dep_task_id := NVL(p_dep_task_id, l_dep_task_id);
795 l_arr_task_id := NVL(p_arr_task_id, l_arr_task_id);
796 ELSE
797 -- Use the existing ones.
798 l_dep_task_id := p_dep_task_id;
799 l_arr_task_id := p_arr_task_id;
800 IF l_debug THEN
801 debug(' Using existing Shift Tasks - Dep#' || l_dep_task_id || ' : Arr#' || l_arr_task_id, l_api_name, fnd_log.level_statement);
802 END IF;
803 END IF;
804 IF l_dep_task_id IS NOT NULL AND l_arr_task_id IS NOT NULL
805 THEN
806 IF p_find_tasks IS NULL OR p_find_tasks = fnd_api.g_true THEN
807 i := 1;
808 FOR v_task IN c_linkable_tasks LOOP
809 --l_time_occupied := v_task.booking_end_date - v_task.booking_start_date; -- Scheduled Task Duration
810 --l_time_occupied := l_time_occupied + NVL(v_task.travel_time, 0) / g_mins_in_day; -- Scheduled Travel Duration
811 --l_available_hours := l_available_hours - l_time_occupied * g_hours_in_day;
812
813 IF l_debug THEN
814 debug(' Linking TaskID #' || v_task.task_id || ' : Time Used = ' || l_time_occupied * g_hours_in_day, l_api_name, fnd_log.level_statement);
815 END IF;
816
817 l_object_tasks_tbl(i).task_assignment_id := v_task.task_assignment_id;
818 l_object_tasks_tbl(i).task_assignment_ovn := v_task.object_version_number;
819 l_object_tasks_tbl(i).object_capacity_tbl_idx := 1;
820 i := i + 1;
821 END LOOP;
822 ELSE
823
824
825 l_object_tasks_tbl(1).task_assignment_id := l_dep_task_id;
826 l_object_tasks_tbl(1).object_capacity_tbl_idx := 1;
827 l_object_tasks_tbl(2).task_assignment_id := l_arr_task_id;
828 l_object_tasks_tbl(2).object_capacity_tbl_idx := 1;
829 END IF;
830
831 -- Create the Object Capacity Record
832 l_object_capacity_tbl(1).object_type := p_resource_type;
833 l_object_capacity_tbl(1).object_id := p_resource_id;
834 l_object_capacity_tbl(1).start_date_time := p_start_date_time;
835 l_object_capacity_tbl(1).end_date_time := p_end_date_time;
836 l_object_capacity_tbl(1).available_hours := l_available_hours;
837 l_object_capacity_tbl(1).status := p_status;
838 l_object_capacity_tbl(1).schedule_detail_id := p_schedule_detail_id;
839 l_object_capacity_tbl(1).availability_type := g_shift_type;
840
841 IF l_debug THEN
842 debug(' Trip Available Hours = ' || l_available_hours, l_api_name, fnd_log.level_statement);
843 END IF;
844 IF l_debug THEN
845 debug(' No departure Arrival for dates ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
846 debug(' No departure Arrival for Resource ' || p_resource_id || ' and Resource Type' ||p_resource_type , l_api_name, fnd_log.level_statement);
847 END IF;
848
849 -- Create the Trip by calling Object Capacity Table Handlers
850 cac_sr_object_capacity_pub.insert_object_capacity(
851 p_api_version => 1.0
852 , p_init_msg_list => fnd_api.g_false
853 , x_return_status => x_return_status
854 , x_msg_count => x_msg_count
855 , x_msg_data => x_msg_data
856 , p_object_capacity => l_object_capacity_tbl
857 , p_update_tasks => fnd_api.g_true
858 , p_object_tasks => l_object_tasks_tbl
859 );
860
861 IF x_return_status <> fnd_api.g_ret_sts_success THEN
862 IF l_debug THEN
863 x_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
864 debug(' Unable to Create the Object Capacity: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
865 END IF;
866 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
867 RAISE fnd_api.g_exc_unexpected_error;
868 END IF;
869 RAISE fnd_api.g_exc_error;
870 END IF;
871
872 x_trip.trip_id := l_object_capacity_tbl(1).object_capacity_id;
873 x_trip.object_version_number := 1;
874 x_trip.resource_id := p_resource_id;
875 x_trip.resource_type := p_resource_type;
876 x_trip.start_date_time := p_start_date_time;
877 x_trip.end_date_time := p_end_date_time;
878 x_trip.available_hours := l_available_hours;
879 x_trip.status := p_status;
880 x_trip.schedule_detail_id := p_schedule_detail_id;
881
882 IF l_debug THEN
883 debug(' Created Trip - TripID#' || x_trip.trip_id, l_api_name, fnd_log.level_statement);
884 END IF;
885 ELSE
886 IF l_debug THEN
887 debug(' No departure Arrival for dates ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
888 debug(' No departure Arrival for Resource ' || p_resource_id || ' and Resource Type' ||p_resource_type , l_api_name, fnd_log.level_statement);
889 END IF;
890
891 END IF; --END IF FOR l_dep_task_id is null
892
893 EXCEPTION
894 WHEN fnd_api.g_exc_error THEN
895 ROLLBACK TO csf_new_trip;
896 x_return_status := fnd_api.g_ret_sts_error;
897 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
898 WHEN fnd_api.g_exc_unexpected_error THEN
899 debug('Unepected error occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
900 ROLLBACK TO csf_new_trip;
901 if SQLCODE =1 then
902 x_return_status := fnd_api.g_ret_sts_error;
903 else
904 x_return_status := fnd_api.g_ret_sts_unexp_error;
905 end if;
906 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
907 WHEN OTHERS THEN
908 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
909 x_return_status := fnd_api.g_ret_sts_unexp_error;
910 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
911 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
912 END IF;
913 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
914 ROLLBACK TO csf_new_trip;
915 END new_trip;
916
917 PROCEDURE change_trip(
918 x_return_status OUT NOCOPY VARCHAR2
919 , x_msg_data OUT NOCOPY VARCHAR2
920 , x_msg_count OUT NOCOPY NUMBER
921 , p_trip IN trip_rec_type
922 , p_object_version_number IN NUMBER
923 , p_available_hours IN NUMBER DEFAULT NULL
924 , p_upd_available_hours IN NUMBER DEFAULT NULL
925 , p_available_hours_before IN NUMBER DEFAULT NULL
926 , p_available_hours_after IN NUMBER DEFAULT NULL
927 , p_status IN NUMBER DEFAULT NULL
928 , p_availability_type IN VARCHAR2 DEFAULT NULL
929 , p_update_tasks IN VARCHAR2 DEFAULT NULL
930 , p_task_action IN VARCHAR2 DEFAULT NULL
931 , p_start_date IN DATE DEFAULT NULL
932 , p_end_date IN DATE DEFAULT NULL
933 ) IS
934 l_api_name CONSTANT VARCHAR2(30) := 'CHANGE_TRIP';
935 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
936
937 l_new_task_status NUMBER;
938 l_shift_length NUMBER;
939 l_available_hours NUMBER;
940 l_available_hours_before NUMBER;
941 l_available_hours_after NUMBER;
942
943 CURSOR c_tasks (p_task_type VARCHAR2) IS
944 SELECT ta.task_assignment_id
945 , ta.object_version_number ta_object_version_number
946 , ta.assignment_status_id
947 , t.task_id
948 , t.task_number
949 , t.object_version_number task_ovn
950 , t.task_status_id
951 FROM cac_sr_object_capacity cac
952 , jtf_task_assignments ta
953 , jtf_tasks_b t
954 , jtf_task_statuses_b ts
955 WHERE cac.object_capacity_id = p_trip.trip_id
956 AND ta.resource_id = cac.object_id
957 AND ta.resource_type_code = cac.object_type
958 AND ( (ta.object_capacity_id IS NOT NULL AND ta.object_capacity_id = cac.object_capacity_id)
959 OR (ta.booking_start_date <= (cac.end_date_time + g_overtime) AND ta.booking_end_date >= cac.start_date_time) )
960 AND t.task_id = ta.task_id
961 AND ts.task_status_id = ta.assignment_status_id
962 AND NVL(ts.closed_flag, 'N') = 'N'
963 AND NVL(ts.completed_flag, 'N') = 'N'
964 AND NVL(ts.cancelled_flag, 'N') = 'N'
965 AND NVL(ts.working_flag, 'N') = 'N'
966 AND NVL(t.deleted_flag, 'N') = 'N'
967 AND ta.actual_start_date IS NULL
968 AND (t.source_object_type_code = 'SR' OR t.task_type_id IN (20, 21))
969 AND (p_task_type = 'ALL' OR t.task_type_id IN (20, 21));
970
971 l_task_type VARCHAR2(10);
972 l_validation_level NUMBER;
973
974 BEGIN
975 SAVEPOINT csf_change_trip;
976
977 x_return_status := fnd_api.g_ret_sts_success;
978
979 l_shift_length := (p_trip.end_date_time - p_trip.start_date_time) * g_hours_in_day;
980 l_available_hours := p_trip.available_hours;
981 l_available_hours_before := p_trip.available_hours_before;
982 l_available_hours_after := p_trip.available_hours_after;
983
984 IF p_available_hours IS NOT NULL THEN
985 l_available_hours := p_available_hours;
986 ELSIF p_upd_available_hours IS NOT NULL THEN
987 l_available_hours := p_trip.available_hours + p_upd_available_hours;
988 END IF;
989
990 -- If Available Hours (either as value or as inc/dec) is passed, and Avl Before/After
991 -- is not passed, they should be nulled out.
992 IF p_available_hours IS NOT NULL OR p_upd_available_hours IS NOT NULL THEN
993 l_available_hours_before := NVL(p_available_hours_before, fnd_api.g_miss_num);
994 l_available_hours_after := NVL(p_available_hours_after, fnd_api.g_miss_num);
995 ELSE
996 l_available_hours_before := p_available_hours_before;
997 l_available_hours_after := p_available_hours_after;
998 END IF;
999
1000 /* IF l_available_hours > l_shift_length
1001 OR (l_available_hours_before <> fnd_api.g_miss_num AND l_available_hours_before > l_shift_length)
1002 OR (l_available_hours_after <> fnd_api.g_miss_num AND l_available_hours_after > l_shift_length)
1003 THEN
1004 -- Trip Availability is more than the Shift Length
1005 IF l_debug THEN
1006 debug(' Trip Availability is more than Shift Length', l_api_name, fnd_log.level_error);
1007 END IF;
1008
1009 fnd_message.set_name('CSF', 'CSF_TRIP_WRONG_AVAILABILITY');
1010 fnd_message.set_token('AVAILABLE', l_available_hours);
1011 fnd_message.set_token('AVLBEFORE', l_available_hours_before);
1012 fnd_message.set_token('AVLAFTER', l_available_hours_after);
1013 fnd_msg_pub.ADD;
1014 RAISE fnd_api.g_exc_error;
1015 END IF;*/
1016
1017 IF l_available_hours = l_shift_length
1018 AND ( ( l_available_hours_before IS NOT NULL AND l_available_hours_before <> fnd_api.g_miss_num )
1019 OR ( l_available_hours_after IS NOT NULL AND l_available_hours_after <> fnd_api.g_miss_num )
1020 )
1021 THEN
1022 -- Trip Availability is equal to the Shift Length and Before and Afters are not NULL
1023 IF l_debug THEN
1024 debug(' Available Hours Before and After must be NULL when Availability is Trip Length', l_api_name, fnd_log.level_error);
1025 END IF;
1026
1027 fnd_message.set_name('CSF', 'CSF_TRIP_WRONG_AVL_BEFOREAFTER');
1028 fnd_message.set_token('AVLBEFORE', l_available_hours_before);
1029 fnd_message.set_token('AVLAFTER', l_available_hours_after);
1030 fnd_message.set_token('AVAILABLE', l_available_hours);
1031 fnd_msg_pub.ADD;
1032 RAISE fnd_api.g_exc_error;
1033 END IF;
1034
1035 cac_sr_object_capacity_pub.update_object_capacity(
1036 p_api_version => 1.0
1037 , x_return_status => x_return_status
1038 , x_msg_count => x_msg_count
1039 , x_msg_data => x_msg_data
1040 , p_object_capacity_id => p_trip.trip_id
1041 , p_object_version_number => p_object_version_number
1042 , p_available_hours => l_available_hours
1043 , p_available_hours_before => l_available_hours_before
1044 , p_available_hours_after => l_available_hours_after
1045 , p_availability_type => p_availability_type
1046 , p_status => p_status
1047 , p_start_date_time => p_start_date
1048 , p_end_date_time => p_end_date
1049 );
1050
1051 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1052 IF l_debug THEN
1053 debug(' Unable to Update the Object Capacity', l_api_name, fnd_log.level_error);
1054 END IF;
1055 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1056 RAISE fnd_api.g_exc_unexpected_error;
1057 END IF;
1058 RAISE fnd_api.g_exc_error;
1059 END IF;
1060
1061 -- If Tasks need not be updated.... nothing more to be done. Exit
1062 IF p_update_tasks = fnd_api.g_false THEN
1063 RETURN;
1064 END IF;
1065
1066 -- If New Trip Status equals Old Trip Status.... nothing more to be done. Exit
1067 IF NVL(p_status, p_trip.status) = p_trip.status THEN
1068 RETURN;
1069 END IF;
1070
1071 IF p_task_action = g_action_close_trip THEN
1072 l_validation_level := fnd_api.g_valid_level_none;
1073 l_task_type := 'SHIFTS';
1074 ELSE
1075 l_validation_level := fnd_api.g_valid_level_full;
1076 l_task_type := 'ALL';
1077 END IF;
1078
1079 FOR v_task IN c_tasks(l_task_type) LOOP
1080 l_new_task_status := get_new_task_status(p_task_action, v_task.assignment_status_id);
1081 IF l_new_task_status IS NOT NULL THEN
1082 IF l_debug THEN
1083 debug(' Updating the Task - TaskID# ' || v_task.task_id, l_api_name, fnd_log.level_statement);
1084 END IF;
1085 csf_task_assignments_pub.update_assignment_status(
1086 p_api_version => 1.0
1087 , p_init_msg_list => fnd_api.g_false
1088 , p_validation_level => l_validation_level
1089 , p_commit => fnd_api.g_false
1090 , x_return_status => x_return_status
1091 , x_msg_count => x_msg_count
1092 , x_msg_data => x_msg_data
1093 , p_task_assignment_id => v_task.task_assignment_id
1094 , p_object_version_number => v_task.ta_object_version_number
1095 , p_assignment_status_id => l_new_task_status
1096 , x_task_object_version_number => v_task.task_ovn
1097 , x_task_status_id => v_task.task_status_id
1098 );
1099
1100 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1101 -- Somehow direct population of the Token using fnd_msg_pub is not working
1102 -- Therefore populating it in x_msg_data and using it to populate the Token REASON.
1103 x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false);
1104 IF l_debug THEN
1105 debug(' Unable to update the Assignment: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
1106 END IF;
1107 fnd_message.set_name('CSF', 'CSF_ASSIGNMENT_UPDATE_FAIL');
1108 fnd_message.set_token('TASK', v_task.task_number);
1109 fnd_message.set_token('REASON', x_msg_data);
1110 fnd_msg_pub.ADD;
1111 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1112 RAISE fnd_api.g_exc_unexpected_error;
1113 END IF;
1114 RAISE fnd_api.g_exc_error;
1115 END IF;
1116 END IF;
1117 END LOOP;
1118 EXCEPTION
1119 WHEN fnd_api.g_exc_error THEN
1120 ROLLBACK TO csf_change_trip;
1121 x_return_status := fnd_api.g_ret_sts_error;
1122 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1123 WHEN fnd_api.g_exc_unexpected_error THEN
1124 ROLLBACK TO csf_change_trip;
1125 x_return_status := fnd_api.g_ret_sts_unexp_error;
1126 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1127 WHEN OTHERS THEN
1128 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1129 x_return_status := fnd_api.g_ret_sts_unexp_error;
1130 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1131 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1132 END IF;
1133 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1134 ROLLBACK TO csf_change_trip;
1135 END change_trip;
1136
1137 PROCEDURE remove_trip(
1138 x_return_status OUT NOCOPY VARCHAR2
1139 , x_msg_data OUT NOCOPY VARCHAR2
1140 , x_msg_count OUT NOCOPY NUMBER
1141 , p_trip IN trip_rec_type
1142 , p_object_version_number IN NUMBER
1143 , p_check_active_tasks IN VARCHAR2 DEFAULT NULL
1144 ) IS
1145 l_api_name CONSTANT VARCHAR2(30) := 'REMOVE_TRIP';
1146 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1147
1148 -- No need to check Task Assignment Status as Task itself will reflect it for Shift Tasks.
1149 CURSOR c_shift_tasks IS
1150 SELECT t.task_id
1151 , t.object_version_number
1152 , t.task_name
1153 , t.task_number
1154 FROM jtf_task_assignments ta
1155 , jtf_tasks_vl t
1156 WHERE ta.object_capacity_id = p_trip.trip_id
1157 AND t.task_id = ta.task_id
1158 AND NVL(t.deleted_flag, 'N') = 'N'
1159 AND t.task_type_id IN (g_dep_task_type_id, g_arr_task_type_id);
1160
1161 BEGIN
1162 SAVEPOINT csf_remove_trip;
1163
1164 x_return_status := fnd_api.g_ret_sts_success;
1165
1166 IF l_debug THEN
1167 debug( ' Deleting the Trip #' || p_trip.trip_id
1168 || ' between ' || format_date(p_trip.start_date_time)
1169 || ' and ' || format_date(p_trip.end_date_time)
1170 , l_api_name, fnd_log.level_procedure
1171 );
1172 END IF;
1173
1174 -- Check whether the Trip is blocked
1175 IF p_trip.status = g_trip_unavailable THEN
1176 IF l_debug THEN
1177 debug(' The Trip is unavailable and so cant be deleted', l_api_name, fnd_log.level_error);
1178 END IF;
1179
1180 fnd_message.set_name('CSF', 'CSF_TRIP_IS_BLOCKED');
1181 fnd_msg_pub.ADD;
1182 RAISE fnd_api.g_exc_error;
1183 END IF;
1184
1185 -- Check whether there are active Task Assignments in the Trip
1186 IF NVL(p_check_active_tasks, fnd_api.g_true) = fnd_api.g_true THEN
1187 IF trip_has_active_tasks(p_trip.trip_id) THEN
1188 -- There are Active Task Assignments for the Trip.
1189 IF l_debug THEN
1190 debug(' Trip has active Tasks and so cant be deleted', l_api_name, fnd_log.level_error);
1191 END IF;
1192
1193 fnd_message.set_name('CSF', 'CSF_TRIP_HAS_ACTIVE_TASKS');
1194 fnd_msg_pub.ADD;
1195 RAISE fnd_api.g_exc_error;
1196 END IF;
1197 END IF;
1198
1199 -- Delete the Shift Tasks
1200 FOR v_shift_task IN c_shift_tasks LOOP
1201 IF l_debug THEN
1202 debug(' Deleting the Shift Task #' || v_shift_task.task_id ||' object version number ' || v_shift_task.object_version_number , l_api_name, fnd_log.level_statement);
1203 END IF;
1204 jtf_tasks_pub.delete_task(
1205 p_api_version => 1.0
1206 , x_return_status => x_return_status
1207 , x_msg_count => x_msg_count
1208 , x_msg_data => x_msg_data
1209 , p_task_id => v_shift_task.task_id
1210 , p_object_version_number => v_shift_task.object_version_number
1211 );
1212 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1213 IF l_debug THEN
1214 debug(' Unable to Delete the Shift Task id - ' || v_shift_task.task_id , l_api_name, fnd_log.level_error);
1215 END IF;
1216
1217 fnd_message.set_name('CSF', 'CSF_TASK_DELETE_FAIL');
1218 fnd_message.set_token('TASK', v_shift_task.task_number);
1219 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
1220 fnd_msg_pub.ADD;
1221 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1222 RAISE fnd_api.g_exc_unexpected_error;
1223 END IF;
1224 RAISE fnd_api.g_exc_error;
1225 END IF;
1226 END LOOP;
1227
1228 -- Delete the Object Capacity
1229 cac_sr_object_capacity_pub.delete_object_capacity(
1230 p_api_version => 1.0
1231 , x_return_status => x_return_status
1232 , x_msg_count => x_msg_count
1233 , x_msg_data => x_msg_data
1234 , p_object_capacity_id => p_trip.trip_id
1235 , p_object_version_number => p_object_version_number
1236 , p_update_tasks => fnd_api.g_false
1237 );
1238
1239 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1240 IF l_debug THEN
1241 debug(' Unable to Delete the Object Capacity', l_api_name, fnd_log.level_error);
1242 END IF;
1243 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1244 RAISE fnd_api.g_exc_unexpected_error;
1245 END IF;
1246 RAISE fnd_api.g_exc_error;
1247 END IF;
1248
1249 IF l_debug THEN
1250 debug(' Deleted the Trip', l_api_name, fnd_log.level_statement);
1251 END IF;
1252 EXCEPTION
1253 WHEN fnd_api.g_exc_error THEN
1254 ROLLBACK TO csf_remove_trip;
1255 x_return_status := fnd_api.g_ret_sts_error;
1256 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1257 WHEN fnd_api.g_exc_unexpected_error THEN
1258 ROLLBACK TO csf_remove_trip;
1259 IF l_debug THEN
1260 debug('Unable to Delete the Object Capacity: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1261 END IF;
1262 if SQLCODE =1 then
1263 x_return_status := fnd_api.g_ret_sts_error;
1264 else
1265 x_return_status := fnd_api.g_ret_sts_unexp_error;
1266 end if;
1267 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1268 WHEN OTHERS THEN
1269 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1270 x_return_status := fnd_api.g_ret_sts_unexp_error;
1271 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1272 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1273 END IF;
1274 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1275 ROLLBACK TO csf_remove_trip;
1276 END remove_trip;
1277
1278 PROCEDURE correct_trip(
1279 x_return_status OUT NOCOPY VARCHAR2
1280 , x_msg_data OUT NOCOPY VARCHAR2
1281 , x_msg_count OUT NOCOPY NUMBER
1282 , p_trip IN trip_rec_type
1283 , p_object_version_number IN NUMBER
1284 ) IS
1285 l_api_name CONSTANT VARCHAR2(30) := 'CORRECT_TRIP';
1286 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1287 l_available_hours NUMBER;
1288 l_total_task_time NUMBER;
1289 l_total_travel_time NUMBER;
1290
1291 l_dep_task_exists BOOLEAN;
1292 l_arr_task_exists BOOLEAN;
1293 l_dep_task_id NUMBER;
1294 l_arr_task_id NUMBER;
1295
1296 CURSOR c_tasks IS
1297 SELECT ta.task_id
1298 , t.task_number
1299 , ta.task_assignment_id
1300 , ta.object_version_number
1301 , ta.object_capacity_id wrong_trip_id
1302 , oc.object_capacity_id correct_trip_id
1303 FROM cac_sr_object_capacity oc
1304 , jtf_task_assignments ta
1305 , jtf_tasks_b t
1306 , jtf_task_statuses_b ts
1307 WHERE oc.object_capacity_id = p_trip.trip_id
1308 AND ta.resource_id = oc.object_id
1309 AND ta.resource_type_code = oc.object_type
1310 AND ta.assignee_role = 'ASSIGNEE'
1311 AND t.task_id = ta.task_id
1312 AND t.task_type_id NOT IN (20, 21)
1313 AND ts.task_status_id = ta.assignment_status_id
1314 AND NVL(ts.closed_flag, 'N') = 'N'
1315 AND NVL(ts.completed_flag, 'N') = 'N'
1316 AND NVL(ts.cancelled_flag, 'N') = 'N'
1317 AND NVL(ta.object_capacity_id, -1) <> oc.object_capacity_id
1318 AND ta.booking_start_date < (oc.end_date_time + g_overtime)
1319 AND ta.booking_end_date > oc.start_date_time
1320 UNION ALL
1321 SELECT ta.task_id
1322 , t.task_number
1323 , ta.task_assignment_id
1324 , ta.object_version_number
1325 , p_trip.trip_id wrong_trip_id
1326 , oc.object_capacity_id correct_trip_id
1327 FROM cac_sr_object_capacity oc
1328 , jtf_task_assignments ta
1329 , jtf_tasks_b t
1330 , jtf_task_statuses_b ts
1331 WHERE ta.object_capacity_id = p_trip.trip_id
1332 AND oc.object_id = ta.resource_id
1333 AND oc.object_type = ta.resource_type_code
1334 AND oc.object_capacity_id <> ta.object_capacity_id
1335 AND t.task_id = ta.task_id
1336 AND t.task_type_id NOT IN (20, 21)
1337 AND ts.task_status_id = ta.assignment_status_id
1338 AND NVL(ts.closed_flag, 'N') = 'N'
1339 AND NVL(ts.completed_flag, 'N') = 'N'
1340 AND NVL(ts.cancelled_flag, 'N') = 'N'
1341 AND ta.booking_start_date < (oc.end_date_time + g_overtime)
1342 AND ta.booking_end_date > oc.start_date_time
1343 UNION ALL
1344 SELECT ta.task_id
1345 , t.task_number
1346 , ta.task_assignment_id
1347 , ta.object_version_number
1348 , to_number(NULL) wrong_trip_id
1349 , p_trip.trip_id correct_trip_id
1350 FROM jtf_task_assignments ta
1351 , jtf_tasks_b t
1352 WHERE ta.task_id IN (l_dep_task_id, l_arr_task_id)
1353 AND t.task_id = ta.task_id;
1354
1355 CURSOR c_used_time IS
1356 SELECT SUM (ta.booking_end_date - ta.booking_start_date) used_time
1357 , SUM (NVL(csf_util_pvt.convert_to_minutes(
1358 ta.sched_travel_duration
1359 , ta.sched_travel_duration_uom
1360 ), 0)) travel_time
1361 FROM jtf_task_assignments ta
1362 , jtf_task_statuses_b ts
1363 WHERE ta.object_capacity_id = p_trip.trip_id
1364 AND ts.task_status_id = ta.assignment_status_id
1365 AND NVL(ts.closed_flag, 'N') = 'N'
1366 AND NVL(ts.completed_flag, 'N') = 'N'
1367 AND NVL(ts.cancelled_flag, 'N') = 'N';
1368
1369 CURSOR c_shift_tasks IS
1370 SELECT t.task_id
1371 , t.task_type_id
1372 , t.object_version_number
1373 , t.task_name
1374 , t.task_number
1375 , LAG(t.task_id) OVER (PARTITION BY t.task_type_id
1376 ORDER BY t.scheduled_start_date) duplicate
1377 FROM jtf_task_assignments ta
1378 , jtf_tasks_vl t
1379 WHERE ta.object_capacity_id = p_trip.trip_id
1380 AND t.task_id = ta.task_id
1381 AND NVL(t.deleted_flag, 'N') = 'N'
1382 AND t.task_type_id IN (20, 21);
1383 BEGIN
1384 SAVEPOINT csf_correct_trip;
1385
1386 x_return_status := fnd_api.g_ret_sts_success;
1387
1388 IF l_debug THEN
1389 debug(' Checking Shift Tasks', l_api_name, fnd_log.level_statement);
1390 END IF;
1391
1392 -- Clean up the Shift Tasks for the Trip.
1393 l_dep_task_exists := FALSE;
1394 l_arr_task_exists := FALSE;
1395 FOR v_shift_task IN c_shift_tasks LOOP
1396 IF v_shift_task.duplicate IS NOT NULL THEN
1397 IF l_debug THEN
1398 debug(' Deleting the Duplicate Shift Task #' || v_shift_task.task_id, l_api_name, fnd_log.level_statement);
1399 END IF;
1400 -- Departure Task already exists... Delete this Duplicate.
1401 jtf_tasks_pub.delete_task(
1402 p_api_version => 1.0
1403 , x_return_status => x_return_status
1404 , x_msg_count => x_msg_count
1405 , x_msg_data => x_msg_data
1406 , p_task_id => v_shift_task.task_id
1407 , p_object_version_number => v_shift_task.object_version_number
1408 );
1409 END IF;
1410 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1411 IF l_debug THEN
1412 debug(' Unable to Delete the Task', l_api_name, fnd_log.level_error);
1413 END IF;
1414
1415 fnd_message.set_name('CSF', 'CSF_TASK_DELETE_FAIL');
1416 fnd_message.set_token('TASK', v_shift_task.task_number);
1417 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
1418 fnd_msg_pub.ADD;
1419 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1420 RAISE fnd_api.g_exc_unexpected_error;
1421 END IF;
1422 RAISE fnd_api.g_exc_error;
1423 END IF;
1424
1425 IF v_shift_task.task_type_id = 20 THEN
1426 l_dep_task_exists := TRUE;
1427 ELSE
1428 l_arr_task_exists := TRUE;
1429 END IF;
1430 END LOOP;
1431
1432 IF NOT(l_dep_task_exists) OR NOT(l_arr_task_exists) THEN
1433 IF l_debug THEN
1434 debug(' Either Departure or Arrival Task is absent. Creating them', l_api_name, fnd_log.level_statement);
1435 END IF;
1436
1437 create_shift_tasks(
1438 p_api_version => 1.0
1439 , x_return_status => x_return_status
1440 , x_msg_data => x_msg_data
1441 , x_msg_count => x_msg_count
1442 , p_resource_id => p_trip.resource_id
1443 , p_resource_type => p_trip.resource_type
1444 , p_start_date_time => p_trip.start_date_time
1445 , p_end_date_time => p_trip.end_date_time
1446 , p_create_dep_task => NOT(l_dep_task_exists)
1447 , p_create_arr_task => NOT(l_arr_task_exists)
1448 , x_dep_task_id => l_dep_task_id
1449 , x_arr_task_id => l_arr_task_id
1450 );
1451
1452 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1453 IF l_debug THEN
1454 debug(' Creation of Shift Tasks failed', l_api_name, fnd_log.level_error);
1455 END IF;
1456 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1457 RAISE fnd_api.g_exc_unexpected_error;
1458 END IF;
1459 RAISE fnd_api.g_exc_error;
1460 END IF;
1461 END IF;
1462
1463 FOR v_task IN c_tasks LOOP
1464 IF l_debug THEN
1465 debug(' TaskID#' || v_task.task_id || ' is part of Trip#' || v_task.wrong_trip_id || '. But should be in Trip#' || v_task.correct_trip_id || '. Fixing the Task', l_api_name, fnd_log.level_statement);
1466 END IF;
1467
1468 jtf_task_assignments_pub.update_task_assignment(
1469 p_api_version => 1.0
1470 , x_return_status => x_return_status
1471 , x_msg_data => x_msg_data
1472 , x_msg_count => x_msg_count
1473 , p_task_assignment_id => v_task.task_assignment_id
1474 , p_object_version_number => v_task.object_version_number
1475 , p_object_capacity_id => v_task.correct_trip_id
1476 , p_enable_workflow => fnd_api.g_miss_char
1477 , p_abort_workflow => fnd_api.g_miss_char
1478 );
1479
1480 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1481 fnd_message.set_name('CSF', 'CSF_ASSIGNMENT_UPDATE_FAIL');
1482 fnd_message.set_token('TASK', v_task.task_number);
1483 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
1484 fnd_msg_pub.ADD;
1485 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1486 RAISE fnd_api.g_exc_unexpected_error;
1487 END IF;
1488 RAISE fnd_api.g_exc_error;
1489 END IF;
1490 END LOOP;
1491
1492 -- Update the Availability of the Trip.
1493 OPEN c_used_time;
1494 FETCH c_used_time INTO l_total_task_time, l_total_travel_time;
1495 CLOSE c_used_time;
1496
1497 --l_available_hours := (p_trip.end_date_time - p_trip.start_date_time) - l_total_task_time - l_total_travel_time / g_mins_in_day;
1498 l_available_hours := p_trip.end_date_time - p_trip.start_date_time ;
1499
1500 cac_sr_object_capacity_pub.update_object_capacity(
1501 p_api_version => 1.0
1502 , x_return_status => x_return_status
1503 , x_msg_count => x_msg_count
1504 , x_msg_data => x_msg_data
1505 , p_object_capacity_id => p_trip.trip_id
1506 , p_object_version_number => p_object_version_number
1507 , p_available_hours => l_available_hours * g_hours_in_day
1508 );
1509
1510 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1511 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1512 RAISE fnd_api.g_exc_unexpected_error;
1513 END IF;
1514 RAISE fnd_api.g_exc_error;
1515 END IF;
1516 EXCEPTION
1517 WHEN fnd_api.g_exc_error THEN
1518 ROLLBACK TO csf_correct_trip;
1519 x_return_status := fnd_api.g_ret_sts_error;
1520 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1521 WHEN fnd_api.g_exc_unexpected_error THEN
1522 ROLLBACK TO csf_correct_trip;
1523 x_return_status := fnd_api.g_ret_sts_unexp_error;
1524 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1525 WHEN OTHERS THEN
1526 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1527 x_return_status := fnd_api.g_ret_sts_unexp_error;
1528 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1529 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1530 END IF;
1531 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1532 ROLLBACK TO csf_correct_trip;
1533 END correct_trip;
1534
1535
1536 /******************************************************************************************
1537 * *
1538 * Private Functions and Procedures dealing with Multiple Trips *
1539 * *
1540 *******************************************************************************************/
1541 /**
1542 * Creates Trips for the passed Resource between the Start and End Dates
1543 * based on the Shift Definitions existing for the resource between the dates.
1544 * <br>
1545 * Validations done in addition to the ones in CREATE_TRIP
1546 * 1. If any one trip exists without any Dep/Arr, then the API errors out asking
1547 * to use FIX TRIPS to fix the Trips in the range first.
1548 * 2. If there exists no Shift Definitions for the Resource between the given
1549 * dates, the API errors out with No Shift Defn message.
1550 * 3. If there exists atleast one Shift Task not tied to any Trip between the
1551 * the dates, the API errors out asking to use UPGRADE_TRIPS to upgrade
1552 * from Shift Model to Trips Model.
1553 *
1554 * @param p_api_version API Version (1.0)
1555 * @param p_init_msg_list Initialize Message List
1556 * @param p_commit Commits the Database
1557 * @param x_return_status Return Status of the Procedure.
1558 * @param x_msg_data Stack of Error Messages.
1559 * @param x_msg_count Number of Messages in the Stack.
1560 * @param p_resource_id Resource ID
1561 * @param p_resource_type Resource Type
1562 * @param p_start_date Start Date
1563 * @param p_end_date End Date
1564 *
1565 * @see create_trip Create Trip API
1566 **/
1567
1568 PROCEDURE delete_trips(
1569 x_return_status OUT NOCOPY VARCHAR2
1570 , x_msg_data OUT NOCOPY VARCHAR2
1571 , x_msg_count OUT NOCOPY NUMBER
1572 , p_trips IN trip_tbl_type
1573 )IS
1574 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRIPS';
1575 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1576 BEGIN
1577 x_return_status := fnd_api.g_ret_sts_success;
1578
1579 IF l_debug THEN
1580 debug('Deleting the given Trips', l_api_name, fnd_log.level_procedure);
1581 END IF;
1582
1583 FOR i IN 1..p_trips.COUNT LOOP
1584 IF p_trips(i).availability_type = g_shift_type OR g_shift_type is null
1585 THEN
1586 remove_trip(
1587 x_return_status => x_return_status
1588 , x_msg_data => x_msg_data
1589 , x_msg_count => x_msg_count
1590 , p_trip => p_trips(i)
1591 , p_object_version_number => p_trips(i).object_version_number
1592 );
1593
1594 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1595 add_message(
1596 p_trip => p_trips(i)
1597 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
1598 , p_msg_name => 'CSF_TRIP_DELETE_FAIL_OTHER'
1599 , p_msg_type => g_error_message
1600 );
1601 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1602 RAISE fnd_api.g_exc_unexpected_error;
1603 END IF;
1604 ELSE
1605 add_message(p_trips(i));
1606 END IF;
1607 END IF;
1608 END LOOP;
1609 EXCEPTION
1610 WHEN fnd_api.g_exc_error THEN
1611 x_return_status := fnd_api.g_ret_sts_error;
1612 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1613 WHEN fnd_api.g_exc_unexpected_error THEN
1614 x_return_status := fnd_api.g_ret_sts_unexp_error;
1615 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1616 WHEN OTHERS THEN
1617 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1618 x_return_status := fnd_api.g_ret_sts_unexp_error;
1619 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1620 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1621 END IF;
1622 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1623 END delete_trips;
1624
1625 PROCEDURE fix_trips(
1626 x_return_status OUT NOCOPY VARCHAR2
1627 , x_msg_data OUT NOCOPY VARCHAR2
1628 , x_msg_count OUT NOCOPY NUMBER
1629 , p_trips IN trip_tbl_type
1630 ) IS
1631 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRIPS';
1632 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1633 BEGIN
1634 x_return_status := fnd_api.g_ret_sts_success;
1635
1636 IF l_debug THEN
1637 debug('Fixing the given Trips', l_api_name, fnd_log.level_procedure);
1638 END IF;
1639
1640 FOR i IN 1..p_trips.COUNT LOOP
1641 correct_trip(
1642 x_return_status => x_return_status
1643 , x_msg_data => x_msg_data
1644 , x_msg_count => x_msg_count
1645 , p_trip => p_trips(i)
1646 , p_object_version_number => p_trips(i).object_version_number
1647 );
1648
1649 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1650 add_message(
1651 p_trip => p_trips(i)
1652 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
1653 , p_msg_name => 'CSF_TRIP_FIX_FAIL_OTHER'
1654 , p_msg_type => g_error_message
1655 );
1656 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1657 RAISE fnd_api.g_exc_unexpected_error;
1658 END IF;
1659 ELSE
1660 add_message(p_trips(i));
1661 END IF;
1662 END LOOP;
1663 EXCEPTION
1664 WHEN fnd_api.g_exc_error THEN
1665 x_return_status := fnd_api.g_ret_sts_error;
1666 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1667 WHEN fnd_api.g_exc_unexpected_error THEN
1668 x_return_status := fnd_api.g_ret_sts_unexp_error;
1669 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1670 WHEN OTHERS THEN
1671 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1672 x_return_status := fnd_api.g_ret_sts_unexp_error;
1673 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1674 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1675 END IF;
1676 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1677 END fix_trips;
1678
1679 PROCEDURE create_trips(
1680 x_return_status OUT NOCOPY VARCHAR2
1681 , x_msg_data OUT NOCOPY VARCHAR2
1682 , x_msg_count OUT NOCOPY NUMBER
1683 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
1684 , p_start_date IN DATE
1685 , p_end_date IN DATE
1686 , P_SHIFT_TYPE IN VARCHAR2 DEFAULT NULL
1687 , p_delete_trips IN BOOLEAN DEFAULT FALSE
1688 ) IS
1689 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TRIPS';
1690 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1691
1692 l_check_failed VARCHAR2(1);
1693 l_res_id NUMBER;
1694 l_res_type jtf_objects_b.object_code%TYPE;
1695 l_start DATE;
1696 l_end DATE;
1697
1698 l_shifts csf_resource_pub.shift_tbl_type;
1699 l_shift_idx PLS_INTEGER;
1700
1701 l_trip_idx PLS_INTEGER;
1702 l_new_trip trip_rec_type;
1703 l_old_trips trip_tbl_type;
1704 l_new_trips trip_tbl_type;
1705
1706 -- Bug 13072931
1707 l_old_trips_temp trip_tbl_type;
1708 l_trip_length NUMBER;
1709 l_prev_trip_id NUMBER;
1710 l_temp_trip_tbl number_tbl_type;
1711 l_del_trip_tbl jtf_number_table;
1712 l_old_new_trip_map number_tbl_type;
1713
1714 l_msg_name fnd_new_messages.message_name%TYPE;
1715 l_reason fnd_new_messages.message_text%TYPE;
1716
1717 l_shift_old_start DATE;
1718 l_shift_old_end DATE;
1719
1720 -- Query to check for the existence of Stray Shift Tasks
1721 CURSOR c_shift_tasks_exist (p_res_id NUMBER, p_res_type VARCHAR2, p_start DATE, p_end DATE) IS
1722 SELECT 'Y'
1723 FROM jtf_tasks_b t
1724 , jtf_task_assignments ta
1725 WHERE t.owner_id = p_res_id
1726 AND t.owner_type_code = p_res_type
1727 AND t.planned_start_date BETWEEN p_start AND p_end
1728 AND t.task_type_id IN (20, 21)
1729 AND NVL(t.deleted_flag, 'N') <> 'Y'
1730 AND ta.task_id = t.task_id
1731 AND ta.assignee_role = 'ASSIGNEE'
1732 AND ta.object_capacity_id IS NULL
1733 AND ROWNUM = 1;
1734
1735 -- Cursor to retrive tasks still linked to old trip
1736 CURSOR c_unlinked_tasks IS
1737 SELECT /*+ cardinality (oc 1) */
1738 ta.task_assignment_id
1739 , ta.object_version_number
1740 , ta.object_capacity_id
1741 , ta.task_id
1742 , ta.booking_start_date
1743 , ta.booking_end_date
1744 , csf_util_pvt.convert_to_minutes(
1745 ta.sched_travel_duration
1746 , ta.sched_travel_duration_uom
1747 ) travel_time
1748 FROM TABLE ( CAST(l_del_trip_tbl AS jtf_number_table) ) oc
1749 , jtf_task_assignments ta
1750 , jtf_task_statuses_b ts
1751 , jtf_tasks_b t
1752 WHERE ta.object_capacity_id = oc.COLUMN_VALUE
1753 AND ts.task_status_id = ta.assignment_status_id
1754 AND NVL(ts.closed_flag, 'N') = 'N'
1755 AND NVL(ts.completed_flag, 'N') = 'N'
1756 AND NVL(ts.cancelled_flag, 'N') = 'N'
1757 AND NVL(ts.rejected_flag, 'N') = 'N'
1758 AND t.task_id = ta.task_id
1759 AND NVL(t.deleted_flag, 'N') <> 'Y'
1760 AND t.task_type_id NOT IN (20, 21)
1761 ORDER BY ta.object_capacity_id;
1762
1763
1764 CURSOR c_linkable_tasks(l_trip number) IS
1765 SELECT tb.task_id
1766 FROM jtf_task_assignments jta,
1767 jtf_tasks_b tb,
1768 jtf_task_statuses_b jts
1769 WHERE jta.object_capacity_id=l_trip
1770 AND jta.task_id=tb.task_id
1771 AND jts.task_status_id = tb.task_status_id
1772 AND NVL(jts.closed_flag, 'N') = 'N'
1773 AND NVL(jts.completed_flag, 'N') = 'N'
1774 AND NVL(jts.cancelled_flag, 'N') = 'N'
1775 AND NVL(jts.rejected_flag, 'N') = 'N'
1776 AND NVL(tb.deleted_flag, 'N') <> 'Y'
1777 AND tb.task_type_id not in (20,21);
1778
1779 CURSOR c_trip_info(p_trip_id NUMBER) IS
1780 SELECT oc.object_version_number
1781 , oc.available_hours
1782 FROM cac_sr_object_capacity oc
1783 WHERE oc.object_capacity_id = p_trip_id;
1784
1785 l_trip_info c_trip_info%ROWTYPE;
1786 l_links c_linkable_tasks%ROWTYPE;
1787
1788
1789 BEGIN
1790 SAVEPOINT csf_create_trips;
1791
1792 x_return_status := fnd_api.g_ret_sts_success;
1793
1794 l_res_id := p_resource_tbl(1).resource_id;
1795 l_res_type := p_resource_tbl(1).resource_type;
1796
1797 IF l_debug THEN
1798 IF p_delete_trips THEN
1799 debug('Replacing Trips for Resource#' || l_res_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
1800 ELSE
1801 debug('Creating Trips for Resource#' || l_res_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
1802 END IF;
1803 END IF;
1804
1805 -- Get the Resource's Shifts
1806 csf_resource_pub.get_resource_shifts(
1807 p_api_version => 1.0
1808 , x_return_status => x_return_status
1809 , x_msg_count => x_msg_count
1810 , x_msg_data => x_msg_data
1811 , p_resource_id => l_res_id
1812 , p_resource_type => l_res_type
1813 , p_start_date => p_start_date - 1
1814 , p_end_date => p_end_date
1815 , p_shift_type => p_shift_type
1816 , x_shifts => l_shifts
1817 );
1818
1819 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1820 IF l_debug THEN
1821 debug(' No Shifts were found for the resource between the timeframe', l_api_name, fnd_log.level_error);
1822 END IF;
1823 add_message(
1824 p_res_id => l_res_id
1825 , p_res_type => l_res_type
1826 , p_start => p_start_date
1827 , p_end => p_end_date
1828 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
1829 , p_msg_name => 'CSF_RETRIEVE_SHIFTS_FAIL'
1830 , p_msg_type => g_error_message
1831 );
1832 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1833 RAISE fnd_api.g_exc_unexpected_error;
1834 END IF;
1835 --RAISE fnd_api.g_exc_error;
1836 END IF;
1837 if l_shifts.count >0 then
1838 -- Check whether Shift Tasks are already there between the given Time Frame.
1839 l_start := l_shifts(l_shifts.FIRST).start_datetime;
1840 l_end := l_shifts(l_shifts.LAST).end_datetime;
1841
1842 debug('Shift Start time : '|| to_char(l_start,'dd/mm/yyyy hh24:mi') || 'Shift End time : '|| to_char(l_end,'dd/mm/yyyy hh24:mi'), l_api_name, fnd_log.level_procedure);
1843 OPEN c_shift_tasks_exist(l_res_id, l_res_type, l_start, l_end);
1844 FETCH c_shift_tasks_exist INTO l_check_failed;
1845 IF c_shift_tasks_exist%NOTFOUND THEN
1846 l_check_failed := 'N';
1847 END IF;
1848 CLOSE c_shift_tasks_exist;
1849
1850 IF l_check_failed = 'Y' THEN
1851 -- Shift Tasks exists. Should use "Upgrade to Trips" API rather than "Create Trip".
1852 IF l_debug THEN
1853 debug(' Shift tasks are present between the timeframe', l_api_name, fnd_log.level_error);
1854 END IF;
1855 add_message(
1856 p_res_id => l_res_id
1857 , p_res_type => l_res_type
1858 , p_start => p_start_date
1859 , p_end => p_end_date
1860 , p_msg_name => 'CSF_USE_UPGRADE_TRIPS'
1861 , p_msg_type => g_error_message
1862 );
1863 RAISE fnd_api.g_exc_error;
1864 END IF;
1865
1866 -- Get all the trips in the required interval
1867 l_start := LEAST(p_start_date, l_shifts(l_shifts.FIRST).start_datetime);
1868 l_end := GREATEST(p_end_date, l_shifts(l_shifts.LAST).end_datetime);
1869 l_shift_old_start := l_start;
1870 l_shift_old_end :=l_end;
1871 l_old_trips := find_trips(p_resource_tbl, l_start, l_end);
1872 -- Bug 13072931
1873 l_old_trips_temp := find_trips(p_resource_tbl, l_start, l_end);
1874 IF l_debug THEN
1875 debug(' Current Trips existing: Count = ' || l_old_trips.COUNT, l_api_name, fnd_log.level_statement);
1876 FOR i IN 1..l_old_trips.COUNT LOOP
1877 debug( ' Trip ID = ' || l_old_trips(i).trip_id
1878 || ' Start Time = ' || format_date(l_old_trips(i).start_date_time)
1879 || ' End Time = ' || format_date(l_old_trips(i).end_date_time)
1880 , l_api_name, fnd_log.level_statement);
1881 END LOOP;
1882 END IF;
1883
1884 l_del_trip_tbl := jtf_number_table();
1885
1886 -- Loop through each Shift to create a new Trip
1887 l_shift_idx := l_shifts.FIRST;
1888 WHILE l_shift_idx IS NOT NULL LOOP
1889 IF l_debug THEN
1890 debug( ' Trying to create trip for shift between '
1891 || format_date(l_shifts(l_shift_idx).start_datetime) || ' and '
1892 || format_date(l_shifts(l_shift_idx).end_datetime)
1893 , l_api_name, fnd_log.level_statement
1894 );
1895 END IF;
1896 IF l_shifts(l_shift_idx).start_datetime > p_start_date
1897 THEN
1898 BEGIN
1899 SAVEPOINT csf_process_shift;
1900 l_old_trips := find_trips(p_resource_tbl, l_shift_old_start, l_shift_old_end);
1901 x_return_status := fnd_api.g_ret_sts_success;
1902
1903 l_start := l_shifts(l_shift_idx).start_datetime;
1904 l_end := l_shifts(l_shift_idx).end_datetime;
1905 g_shift_type := l_shifts(l_shift_idx).availability_type;
1906 l_msg_name := NULL;
1907 IF l_debug THEN
1908 debug( ' Trying to create trip for shift between '
1909 || format_date(l_start) || ' and '
1910 || format_date(l_end) || ' and shift type :'
1911 || g_shift_type
1912 , l_api_name, fnd_log.level_statement
1913 );
1914 END IF;
1915 -- Loop through each trip and check for overlap with any of the current trips
1916 l_trip_idx := l_old_trips.FIRST;
1917 WHILE l_trip_idx IS NOT NULL LOOP
1918 IF l_debug THEN
1919 debug(' Checking for overlap with old trip ' || l_old_trips(l_trip_idx).trip_id ||
1920 ' Object version number :'|| l_old_trips(l_trip_idx).object_version_number , l_api_name, fnd_log.level_statement);
1921 END IF;
1922
1923
1924
1925 IF time_overlaps(l_old_trips(l_trip_idx), l_shifts(l_shift_idx)) THEN
1926 -- If Trips can be deleted, then we can avoid the error "Duplicate Trip"
1927 -- by deleting the overlapping trip and only when it falls within the range.
1928 IF NOT p_delete_trips
1929 OR NOT time_overlaps(l_old_trips(l_trip_idx), p_start_date, p_end_date)
1930 THEN
1931 IF l_debug THEN
1932 debug(' Error : CSF_TRIP_CREATE_FAIL_DUP ' , l_api_name, fnd_log.level_error);
1933 END IF;
1934 l_msg_name := 'CSF_TRIP_CREATE_FAIL_DUP';
1935 RAISE fnd_api.g_exc_error;
1936 END IF;
1937 IF l_debug THEN
1938 debug(' Time Overlaps so calling remove trip ' , l_api_name, fnd_log.level_statement);
1939 END IF;
1940 remove_trip(
1941 x_return_status => x_return_status
1942 , x_msg_data => x_msg_data
1943 , x_msg_count => x_msg_count
1944 , p_trip => l_old_trips(l_trip_idx)
1945 , p_object_version_number => l_old_trips(l_trip_idx).object_version_number
1946 , p_check_active_tasks => fnd_api.g_false
1947 );
1948
1949 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1950 IF l_debug THEN
1951 debug(' remove_trip Error : CSF_TRIP_DELETE_FAIL_OTHER ' , l_api_name, fnd_log.level_error);
1952 END IF;
1953 l_msg_name := 'CSF_TRIP_DELETE_FAIL_OTHER';
1954 RAISE fnd_api.g_exc_error;
1955 END IF;
1956
1957 -- Since Trip is not present in DB... it should be removed from memory too.
1958 l_temp_trip_tbl(l_temp_trip_tbl.COUNT+1) := l_old_trips(l_trip_idx).trip_id;
1959
1960 ELSIF l_old_trips(l_trip_idx).start_date_time > (l_end + g_overtime) THEN
1961 -- Since Trips and Shifts are ordered by time, there is no point in searching forward
1962 EXIT;
1963 END IF;
1964
1965
1966 l_trip_idx := l_old_trips.NEXT(l_trip_idx);
1967
1968 END LOOP;
1969
1970 -- Loop through each trip and check for overlap with any of the new trips
1971 l_trip_idx := l_new_trips.LAST;
1972 WHILE l_trip_idx IS NOT NULL LOOP
1973 IF l_debug THEN
1974 debug(' Checking for overlap with new trip ' || l_new_trips(l_trip_idx).trip_id
1975 , l_api_name, fnd_log.level_statement);
1976 END IF;
1977 IF time_overlaps(l_new_trips(l_trip_idx), l_shifts(l_shift_idx)) THEN
1978 l_msg_name := 'CSF_TRIP_CREATE_FAIL_DUP';
1979 RAISE fnd_api.g_exc_error;
1980 ELSIF (l_new_trips(l_trip_idx).end_date_time + g_overtime) < l_start THEN
1981 -- Since Trips and Shifts are ordered by time, there is no point in searching forward
1982 EXIT;
1983 END IF;
1984 l_trip_idx := l_new_trips.PRIOR(l_trip_idx);
1985 END LOOP;
1986
1987 new_trip(
1988 x_return_status => x_return_status
1989 , x_msg_data => x_msg_data
1990 , x_msg_count => x_msg_count
1991 , p_resource_id => l_res_id
1992 , p_resource_type => l_res_type
1993 , p_start_date_time => l_start
1994 , p_end_date_time => l_end
1995 , p_find_tasks => fnd_api.g_true
1996 , x_trip => l_new_trip
1997 );
1998
1999 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2000 l_msg_name := 'CSF_TRIP_CREATE_FAIL_OTHER';
2001 RAISE fnd_api.g_exc_error;
2002 END IF;
2003
2004 -- Since the Old Trips are removed from Database and there is no error
2005 -- encountered we can remove the Old Trips from Memory also
2006 FOR i in 1..l_temp_trip_tbl.COUNT LOOP
2007 l_trip_idx := l_old_trips.FIRST;
2008 WHILE l_trip_idx IS NOT NULL LOOP
2009 IF l_temp_trip_tbl(i) = l_old_trips(l_trip_idx).trip_id THEN
2010 l_old_trips.DELETE(l_trip_idx);
2011 END IF;
2012 l_trip_idx := l_old_trips.NEXT(l_trip_idx);
2013 END LOOP;
2014 -- Bug 13072931
2015 l_trip_idx := l_old_trips_temp.FIRST;
2016 WHILE l_trip_idx IS NOT NULL LOOP
2017 IF l_temp_trip_tbl(i) = l_old_trips_temp(l_trip_idx).trip_id THEN
2018 l_old_trips_temp.DELETE(l_trip_idx);
2019 END IF;
2020 l_trip_idx := l_old_trips_temp.NEXT(l_trip_idx);
2021 END LOOP;
2022
2023 l_del_trip_tbl.extend(1);
2024 l_del_trip_tbl(l_del_trip_tbl.LAST) := l_temp_trip_tbl(i);
2025 l_old_new_trip_map(l_temp_trip_tbl(i)) := l_new_trips.COUNT + 1;
2026 END LOOP;
2027
2028 l_temp_trip_tbl.DELETE;
2029 l_new_trips(l_new_trips.COUNT + 1) := l_new_trip;
2030
2031 EXCEPTION
2032 WHEN OTHERS THEN
2033 ROLLBACK TO csf_process_shift;
2034 l_new_trip.trip_id := -1;
2035 l_new_trips(l_new_trips.COUNT + 1) := l_new_trip;
2036 IF l_msg_name = 'CSF_TRIP_DELETE_FAIL_OTHER' THEN
2037 l_start := l_old_trips(l_trip_idx).start_date_time;
2038 l_end := l_old_trips(l_trip_idx).end_date_time;
2039 END IF;
2040 IF l_msg_name <> 'CSF_TRIP_CREATE_FAIL_DUP' THEN
2041 l_reason := fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false);
2042 ELSE
2043 l_reason := NULL;
2044 END IF;
2045
2046 IF l_debug THEN
2047 IF l_msg_name = 'CSF_TRIP_CREATE_FAIL_DUP' THEN
2048 IF NOT p_delete_trips THEN
2049 debug( ' Since delete trips not allowed.. we have overlap with existing trip'
2050 , l_api_name, fnd_log.level_error
2051 );
2052 ELSE
2053 debug( ' Delete trips allowed.. but we have conflict with new trip'
2054 , l_api_name, fnd_log.level_error
2055 );
2056 END IF;
2057 ELSIF l_msg_name = 'CSF_TRIP_CREATE_FAIL_OTHER' THEN
2058 debug( ' Error occurred while creating the trip between '
2059 || format_date(l_start) || ' and ' || format_date(l_end)
2060 || ' : Error = ' || l_reason
2061 , l_api_name, fnd_log.level_error
2062 );
2063 ELSIF l_msg_name = 'CSF_TRIP_DELETE_FAIL_OTHER' THEN
2064 debug( ' Error occurred while deleting the trip between '
2065 || format_date(l_start) || ' and ' || format_date(l_end)
2066 || ' : Error = ' || l_reason
2067 , l_api_name, fnd_log.level_error
2068 );
2069 END IF;
2070 END IF;
2071
2072 add_message(
2073 p_res_id => l_res_id
2074 , p_res_type => l_res_type
2075 , p_start => l_start
2076 , p_end => l_end
2077 , p_reason => l_reason
2078 , p_msg_name => l_msg_name
2079 , p_msg_type => g_error_message
2080 );
2081 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2082 RAISE fnd_api.g_exc_unexpected_error;
2083 END IF;
2084 END;
2085 END IF;
2086 l_shift_idx := l_shifts.NEXT(l_shift_idx);
2087 END LOOP;
2088
2089 IF p_delete_trips THEN
2090 -- Link all the Unlinked Task Assignments to the corresponding shifts
2091 IF l_debug THEN
2092 debug(' Linking unlinked Task Assignments if any of old trips to new trips', l_api_name, fnd_log.level_statement);
2093 END IF;
2094
2095 l_trip_length := 0;
2096 FOR v_task IN c_unlinked_tasks LOOP
2097 l_trip_idx := l_old_new_trip_map(v_task.object_capacity_id);
2098
2099 -- Moment we have processed all Tasks linked to old trip.. update Prev Trip's Capacity.
2100 IF l_prev_trip_id <> l_new_trips(l_trip_idx).trip_id THEN
2101 IF l_debug THEN
2102 debug( ' Decreasing Trip#' || l_prev_trip_id
2103 || ' Capacity to be lesser by ' || l_trip_length*g_hours_in_day
2104 , l_api_name, fnd_log.level_statement
2105 );
2106 END IF;
2107
2108 OPEN c_trip_info(l_prev_trip_id);
2109 FETCH c_trip_info INTO l_trip_info;
2110 CLOSE c_trip_info;
2111
2112 l_trip_info.available_hours := l_trip_info.available_hours
2113 - l_trip_length * g_hours_in_day;
2114 -- Update the new Trip Capacity of the new trip created (Always OVN is 1)
2115 cac_sr_object_capacity_pub.update_object_capacity(
2116 p_api_version => 1.0
2117 , x_return_status => x_return_status
2118 , x_msg_count => x_msg_count
2119 , x_msg_data => x_msg_data
2120 , p_object_capacity_id => l_prev_trip_id
2121 , p_object_version_number => l_trip_info.object_version_number
2122 , p_available_hours => l_trip_info.available_hours
2123 );
2124
2125 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2126 IF l_debug THEN
2127 debug( ' Error updating Trip. ' || fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false), l_api_name, fnd_log.level_error);
2128 END IF;
2129 END IF;
2130
2131 l_trip_length := 0;
2132 END IF;
2133
2134 IF l_debug THEN
2135 debug( ' Linking Task ' || v_task.task_id
2136 || ' : Old Trip = ' || v_task.object_capacity_id
2137 || ' : New Trip = ' || l_new_trips(l_trip_idx).trip_id
2138 , l_api_name, fnd_log.level_statement
2139 );
2140 END IF;
2141
2142 l_trip_length := l_trip_length
2143 + v_task.booking_end_date - v_task.booking_start_date
2144 + NVL(v_task.travel_time, 0) / g_mins_in_day;
2145
2146 jtf_task_assignments_pub.update_task_assignment(
2147 p_api_version => 1.0
2148 , x_return_status => x_return_status
2149 , x_msg_data => x_msg_data
2150 , x_msg_count => x_msg_count
2151 , p_task_assignment_id => v_task.task_assignment_id
2152 , p_object_version_number => v_task.object_version_number
2153 , p_object_capacity_id => l_new_trips(l_trip_idx).trip_id
2154 , p_enable_workflow => fnd_api.g_miss_char
2155 , p_abort_workflow => fnd_api.g_miss_char
2156 );
2157
2158 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2159 IF l_debug THEN
2160 debug( ' Error updating Task Assignment', l_api_name, fnd_log.level_error);
2161 END IF;
2162 add_message(
2163 p_res_id => l_res_id
2164 , p_res_type => l_res_type
2165 , p_start => p_start_date
2166 , p_end => p_end_date
2167 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2168 , p_msg_name => 'CSF_TRIP_REPLACE_FAIL_RELINK'
2169 , p_msg_type => g_error_message
2170 );
2171 RAISE fnd_api.g_exc_error;
2172 END IF;
2173
2174 -- Moment we encounter a Task linked to old trip.. we have a conflict.
2175 IF l_prev_trip_id IS NULL OR l_prev_trip_id <> l_new_trips(l_trip_idx).trip_id THEN
2176 -- Notify the user that the new trip has conflicts. Note its not an error.
2177 add_message(
2178 p_res_id => l_res_id
2179 , p_res_type => l_res_type
2180 , p_start => l_shifts(l_trip_idx).start_datetime
2181 , p_end => l_shifts(l_trip_idx).end_datetime
2182 , p_msg_name => 'CSF_TRIP_CREATED_CONFLICTS'
2183 , p_msg_type => g_warning_message
2184 );
2185 END IF;
2186
2187 l_prev_trip_id := l_new_trips(l_trip_idx).trip_id;
2188 END LOOP;
2189
2190 -- Delete the remaining trips not replaced during Create Operation
2191
2192 l_trip_idx := l_old_trips_temp.FIRST;
2193 WHILE l_trip_idx IS NOT NULL LOOP
2194 -- Delete only those trips falling within the given dates.
2195 IF time_overlaps(l_old_trips_temp(l_trip_idx), p_start_date, p_end_date) THEN
2196
2197 IF l_debug THEN
2198 debug( ' Deleting the non-overlapping Trip ' || l_old_trips_temp(l_trip_idx).trip_id
2199 || ' between ' || format_date(l_old_trips_temp(l_trip_idx).start_date_Time)
2200 || ' and ' || format_date(l_old_trips_temp(l_trip_idx).end_date_Time)
2201 , l_api_name, fnd_log.level_statement
2202 );
2203 END IF;
2204
2205
2206 IF trip_has_active_tasks(l_old_trips_temp(l_trip_idx).trip_id) THEN
2207 IF l_debug THEN
2208 debug( ' Cant delete trip' || l_old_trips_temp(l_trip_idx).trip_id
2209 || ' between ' || format_date(l_old_trips_temp(l_trip_idx).start_date_Time)
2210 || ' and ' || format_date(l_old_trips_temp(l_trip_idx).end_date_Time)
2211 || ' as there active tasks present'
2212 , l_api_name, fnd_log.level_error
2213 );
2214 END IF;
2215
2216 add_message(l_old_trips_temp(l_trip_idx), NULL, 'CSF_TRIP_REPLACE_FAIL_ACTIVE', g_error_message);
2217 ELSE
2218 IF l_old_trips_temp(l_trip_idx).availability_type = g_shift_type OR g_shift_type is null
2219 THEN
2220 remove_trip(
2221 x_return_status => x_return_status
2222 , x_msg_data => x_msg_data
2223 , x_msg_count => x_msg_count
2224 , p_trip => l_old_trips_temp(l_trip_idx)
2225 , p_object_version_number => l_old_trips_temp(l_trip_idx).object_version_number
2226 , p_check_active_tasks => fnd_api.g_true
2227 );
2228
2229 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2230 add_message(
2231 p_trip => l_old_trips_temp(l_trip_idx)
2232 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2233 , p_msg_name => 'CSF_TRIP_DELETE_FAIL_OTHER'
2234 , p_msg_type => g_error_message
2235 );
2236
2237 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2238 RAISE fnd_api.g_exc_unexpected_error;
2239 END IF;
2240 ELSE
2241 add_message(l_old_trips_temp(l_trip_idx));
2242 END IF;
2243 END IF;
2244
2245 END IF;
2246 END IF;
2247 l_trip_idx := l_old_trips_temp.NEXT(l_trip_idx);
2248 END LOOP;
2249 END IF;
2250
2251 -- Now populate the Message Table so that the caller will
2252 -- get correct picture of Success vs Failure. Note that the failures
2253 -- are already accounted for in the above logic. Only Success needs to be
2254 -- stored. In case of REPLACE Action, we are bothered about how many trips
2255 -- created successfully and not how many deleted successfully.
2256 FOR i IN 1..l_new_trips.COUNT LOOP
2257 IF l_new_trips(i).trip_id <> -1 THEN
2258 add_message(l_res_id, l_res_type, l_shifts(i).start_datetime, l_shifts(i).end_datetime);
2259 END IF;
2260 END LOOP;
2261 -- If there are no shifts in new Shift definition
2262 ELSE
2263 -- Get all the old trips in the required interval
2264 l_start := p_start_date;
2265 l_end := p_end_date;
2266
2267 l_old_trips := find_trips(p_resource_tbl, l_start, l_end);
2268
2269 l_trip_idx := l_old_trips.FIRST;
2270 WHILE l_trip_idx IS NOT NULL LOOP
2271
2272 IF l_debug THEN
2273 debug( ' Deleting the Trip ' || l_old_trips(l_trip_idx).trip_id
2274 || ' between ' || format_date(l_old_trips(l_trip_idx).start_date_Time)
2275 || ' and ' || format_date(l_old_trips(l_trip_idx).end_date_Time)
2276 , l_api_name, fnd_log.level_statement
2277 );
2278 END IF;
2279
2280 IF trip_has_active_tasks(l_old_trips(l_trip_idx).trip_id) THEN
2281 IF l_debug THEN
2282 debug( ' Cant delete trip' || l_old_trips(l_trip_idx).trip_id
2283 || ' between ' || format_date(l_old_trips(l_trip_idx).start_date_Time)
2284 || ' and ' || format_date(l_old_trips(l_trip_idx).end_date_Time)
2285 || ' as there active tasks present'
2286 , l_api_name, fnd_log.level_error
2287 );
2288 END IF;
2289
2290 add_message(l_old_trips(l_trip_idx), NULL, 'CSF_TRIP_REPLACE_FAIL_ACTIVE', g_error_message);
2291 ELSE
2292 IF l_old_trips(l_trip_idx).availability_type = g_shift_type OR g_shift_type is null
2293 THEN
2294 remove_trip(
2295 x_return_status => x_return_status
2296 , x_msg_data => x_msg_data
2297 , x_msg_count => x_msg_count
2298 , p_trip => l_old_trips(l_trip_idx)
2299 , p_object_version_number => l_old_trips(l_trip_idx).object_version_number
2300 , p_check_active_tasks => fnd_api.g_true
2301 );
2302 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2303 add_message(
2304 p_trip => l_old_trips(l_trip_idx)
2305 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2306 , p_msg_name => 'CSF_TRIP_DELETE_FAIL_OTHER'
2307 , p_msg_type => g_error_message
2308 );
2309 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2310 RAISE fnd_api.g_exc_unexpected_error;
2311 END IF;
2312 ELSE
2313 add_message(
2314 p_res_id => l_res_id
2315 , p_res_type => l_res_type
2316 , p_start => p_start_date
2317 , p_end => p_end_date
2318 , p_msg_name => 'CSF_GTP_NO_SHIFT'
2319 , p_msg_type => g_warning_message
2320 );
2321 END IF;
2322 END IF;
2323 END IF;
2324 l_trip_idx := l_old_trips.NEXT(l_trip_idx);
2325 END LOOP;
2326 end if;
2327 EXCEPTION
2328 WHEN fnd_api.g_exc_error THEN
2329 ROLLBACK TO csf_create_trips;
2330 x_return_status := fnd_api.g_ret_sts_error;
2331 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2332 WHEN fnd_api.g_exc_unexpected_error THEN
2333 ROLLBACK TO csf_create_trips;
2334 x_return_status := fnd_api.g_ret_sts_unexp_error;
2335 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2336 WHEN OTHERS THEN
2337 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2338 x_return_status := fnd_api.g_ret_sts_unexp_error;
2339 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2340 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2341 END IF;
2342 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2343 ROLLBACK TO csf_create_trips;
2344 END create_trips;
2345
2346 /**
2347 * Upgrades the current data based on Shift Tasks Model to Trips Model.
2348 * Upgrades all the Trips which exists in the system demarcated by Shift Tasks to the
2349 * actual Trips Model by creating new records in Trips Table for the Resource
2350 * identified by P_RESOURCE_ID by querying for all the Departure and Arrival Shift Tasks
2351 * between the Start and End Dates and creating Trips for those Shift Tasks.
2352 * <br>
2353 * The major difference between CREATE_TRIPS and UPGRADE_TO_TRIPS is that the former
2354 * creates the Trips based on the current Shift Definitions. The Later creates Trips
2355 * based on the current Shift Tasks position.
2356 * <br>
2357 * For each trip to be created, it inturn calls CREATE_TRIP and so all
2358 * the validation that are done for CREATE_TRIP is applicable here also.
2359 * Since this API already has Shift Tasks and is creating Trips for those Shift Tasks
2360 * it fills the parameters P_DEP_TASK_ID and P_ARR_TASK_ID of CREATE_TRIP API.
2361 * <br>
2362 * If there are no fatal errors encountered, x_msg_data will contain the number
2363 * of Trips upgraded successfully and the number of Trips failed to be upgraded
2364 * because of possible overlap with existing trips. Note that this message is
2365 * not put in the Message Stack. So API users should not rely on the value of
2366 * x_msg_data to determine whether the API failed or not. Rather they should
2367 * rely only on standard way of checking x_return_status.
2368 *
2369 * @param p_api_version API Version (1.0)
2370 * @param p_init_msg_list Initialize Message List
2371 * @param p_commit Commits the Database
2372 * @param x_return_status Return Status of the Procedure.
2373 * @param x_msg_data Stack of Error Messages.
2374 * @param x_msg_count Number of Messages in the Stack.
2375 * @param p_resource_id Resource ID
2376 * @param p_resource_type Resource Type
2377 * @param p_start_date Start Date
2378 * @param p_end_date End Date
2379 *
2380 * @see create_trip Create Trip API
2381 * @see create_trips Create Trips API
2382 **/
2383 PROCEDURE upgrade_to_trips(
2384 x_return_status OUT NOCOPY VARCHAR2
2385 , x_msg_data OUT NOCOPY VARCHAR2
2386 , x_msg_count OUT NOCOPY NUMBER
2387 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
2388 , p_start_date IN DATE
2389 , p_end_date IN DATE
2390 ) IS
2391 l_api_name CONSTANT VARCHAR2(30) := 'UPGRADE_TO_TRIPS';
2392 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2393
2394 l_dep_task_tbl jtf_number_table;
2395 l_arr_task_tbl jtf_number_table;
2396 l_start_time_tbl jtf_date_table;
2397 l_end_time_tbl jtf_date_table;
2398 l_new_trip trip_rec_type;
2399
2400 CURSOR c_shift_tasks IS
2401 SELECT d.task_id dep_task_id
2402 , a.task_id arr_task_id
2403 , d.planned_start_date start_time
2404 , a.planned_end_date end_time
2405 FROM jtf_tasks_b d
2406 , jtf_task_assignments dta
2407 , jtf_tasks_b a
2408 , jtf_task_assignments ata
2409 WHERE d.owner_id = p_resource_tbl(1).resource_id
2410 AND d.owner_type_code = p_resource_tbl(1).resource_type
2411 AND d.planned_start_date BETWEEN p_start_date AND p_end_date
2412 AND d.task_type_id = 20
2413 AND NVL(d.deleted_flag, 'N') = 'N'
2414 AND dta.task_id = d.task_id
2415 AND dta.assignee_role = 'ASSIGNEE'
2416 AND dta.object_capacity_id IS NULL
2417 AND a.owner_id = d.owner_id
2418 AND a.owner_type_code = d.owner_type_code
2419 AND a.planned_end_date BETWEEN d.planned_start_date AND (d.planned_start_date + 1)
2420 AND a.task_type_id = 21
2421 AND NVL(a.deleted_flag, 'N') = 'N'
2422 AND ata.task_id = a.task_id
2423 AND ata.assignee_role = 'ASSIGNEE'
2424 AND ata.object_capacity_id IS NULL
2425 AND dta.shift_construct_id = ata.shift_construct_id
2426 ORDER BY d.planned_start_date;
2427
2428 BEGIN
2429 x_return_status := fnd_api.g_ret_sts_success;
2430
2431 IF l_debug THEN
2432 debug('Upgrading to Trips for Resource#' || p_resource_tbl(1).resource_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
2433 END IF;
2434
2435 -- Bulk Collecting all information about Shift Tasks.
2436 OPEN c_shift_tasks;
2437 FETCH c_shift_tasks BULK COLLECT INTO l_dep_task_tbl, l_arr_task_tbl, l_start_time_tbl, l_end_time_tbl;
2438 CLOSE c_shift_tasks;
2439
2440 FOR i IN 1..l_dep_task_tbl.COUNT LOOP
2441 IF l_debug THEN
2442 debug(' Found Shift Tasks - Dep #' || l_dep_task_tbl(i) || ' : Arr # ' || l_arr_task_tbl(i), l_api_name, fnd_log.level_procedure);
2443 END IF;
2444 -- Create a Trip between the Shift Tasks.
2445 new_trip(
2446 x_return_status => x_return_status
2447 , x_msg_data => x_msg_data
2448 , x_msg_count => x_msg_count
2449 , p_resource_id => p_resource_tbl(1).resource_id
2450 , p_resource_type => p_resource_tbl(1).resource_type
2451 , p_start_date_time => l_start_time_tbl(i)
2452 , p_end_date_time => l_end_time_tbl(i)
2453 , p_dep_task_id => l_dep_task_tbl(i)
2454 , p_arr_task_id => l_arr_task_tbl(i)
2455 , x_trip => l_new_trip
2456 );
2457
2458 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2459 add_message(
2460 p_res_id => p_resource_tbl(1).resource_id
2461 , p_res_type => p_resource_tbl(1).resource_type
2462 , p_start => l_start_time_tbl(i)
2463 , p_end => l_end_time_tbl(i)
2464 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2465 , p_msg_name => 'CSF_TRIP_CREATE_FAIL_OTHER'
2466 , p_msg_type => g_error_message
2467 );
2468 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2469 RAISE fnd_api.g_exc_unexpected_error;
2470 END IF;
2471 ELSE
2472 add_message(
2473 p_res_id => p_resource_tbl(1).resource_id
2474 , p_res_type => p_resource_tbl(1).resource_type
2475 , p_start => l_start_time_tbl(i)
2476 , p_end => l_end_time_tbl(i)
2477 );
2478 END IF;
2479 END LOOP;
2480 EXCEPTION
2481 WHEN fnd_api.g_exc_error THEN
2482 x_return_status := fnd_api.g_ret_sts_error;
2483 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2484 WHEN fnd_api.g_exc_unexpected_error THEN
2485 x_return_status := fnd_api.g_ret_sts_unexp_error;
2486 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2487 WHEN OTHERS THEN
2488 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2489 x_return_status := fnd_api.g_ret_sts_unexp_error;
2490 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2491 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2492 END IF;
2493 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2494 END upgrade_to_trips;
2495
2496 PROCEDURE update_trip_status(
2497 x_return_status OUT NOCOPY VARCHAR2
2498 , x_msg_data OUT NOCOPY VARCHAR2
2499 , x_msg_count OUT NOCOPY NUMBER
2500 , p_trip_action IN VARCHAR2
2501 , p_trips IN trip_tbl_type
2502 ) IS
2503 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP_STATUS';
2504 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2505
2506 l_new_trip_status NUMBER;
2507 l_trip_action VARCHAR2(30);
2508 BEGIN
2509 x_return_status := fnd_api.g_ret_sts_success;
2510
2511 IF l_debug THEN
2512 debug('Updating the status of the given trips', l_api_name, fnd_log.level_procedure);
2513 END IF;
2514
2515 IF p_trip_action IN (g_action_block_trip, g_action_close_trip) THEN
2516 l_new_trip_status := g_trip_unavailable;
2517 ELSIF p_trip_action = g_action_unblock_trip THEN
2518 l_new_trip_status := g_trip_available;
2519 END IF;
2520
2521 FOR i IN 1..p_trips.COUNT LOOP
2522 IF l_debug THEN
2523 debug('Updating Trip# ' || p_trips(i).trip_id, l_api_name, fnd_log.level_statement);
2524 END IF;
2525
2526 IF l_new_trip_status = p_trips(i).status THEN
2527 IF l_debug THEN
2528 debug(' Trip is already in correct status ' || p_trips(i).status, l_api_name, fnd_log.level_statement);
2529 END IF;
2530 GOTO NEXT_TRIP;
2531 END IF;
2532
2533 IF p_trip_action = g_action_close_trip AND (p_trips(i).end_date_time + g_overtime) > SYSDATE THEN
2534 IF l_debug THEN
2535 debug(' Trip is present or future dated. Cant close', l_api_name, fnd_log.level_error);
2536 END IF;
2537 add_message(
2538 p_trip => p_trips(i)
2539 , p_msg_name => 'CSF_TRIP_CLOSE_FAIL_ACTIVE'
2540 , p_msg_type => g_error_message
2541 );
2542 GOTO NEXT_TRIP;
2543 END IF;
2544
2545 IF p_trip_action = g_action_block_trip AND (p_trips(i).end_date_time + g_overtime) < SYSDATE THEN
2546 IF l_debug THEN
2547 debug(' Trip is past dated. Close it rather than blocking', l_api_name, fnd_log.level_statement);
2548 END IF;
2549 l_trip_action := g_action_close_trip;
2550 ELSE
2551 l_trip_action := p_trip_action;
2552 END IF;
2553
2554 change_trip(
2555 x_return_status => x_return_status
2556 , x_msg_data => x_msg_data
2557 , x_msg_count => x_msg_count
2558 , p_trip => p_trips(i)
2559 , p_object_version_number => p_trips(i).object_version_number
2560 , p_status => l_new_trip_status
2561 , p_update_tasks => fnd_api.g_true
2562 , p_task_action => l_trip_action
2563 );
2564
2565 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2566 add_message(
2567 p_trip => p_trips(i)
2568 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2569 , p_msg_name => 'CSF_TRIP_UPDATE_FAIL_OTHER'
2570 , p_msg_type => g_error_message
2571 );
2572 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2573 RAISE fnd_api.g_exc_unexpected_error;
2574 END IF;
2575 ELSE
2576 add_message(p_trips(i));
2577 END IF;
2578
2579 <<NEXT_TRIP>>
2580 NULL;
2581 END LOOP;
2582 EXCEPTION
2583 WHEN fnd_api.g_exc_error THEN
2584 x_return_status := fnd_api.g_ret_sts_error;
2585 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2586 WHEN fnd_api.g_exc_unexpected_error THEN
2587 x_return_status := fnd_api.g_ret_sts_unexp_error;
2588 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2589 WHEN OTHERS THEN
2590 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2591 x_return_status := fnd_api.g_ret_sts_unexp_error;
2592 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2593 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2594 END IF;
2595 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2596 END update_trip_status;
2597
2598 /******************************************************************************************
2599 * *
2600 * Public Functions and Procedures dealing with a Single Trip *
2601 * *
2602 *******************************************************************************************/
2603 /**
2604 * Refer to the Package Spec for documentation of this procedure
2605 */
2606 PROCEDURE create_trip(
2607 p_api_version IN NUMBER
2608 , p_init_msg_list IN VARCHAR2
2609 , p_commit IN VARCHAR2
2610 , x_return_status OUT NOCOPY VARCHAR2
2611 , x_msg_data OUT NOCOPY VARCHAR2
2612 , x_msg_count OUT NOCOPY NUMBER
2613 , p_resource_id IN NUMBER
2614 , p_resource_type IN VARCHAR2
2615 , p_start_date_time IN DATE
2616 , p_end_date_time IN DATE
2617 , p_schedule_detail_id IN NUMBER
2618 , p_status IN NUMBER
2619 , p_find_tasks IN VARCHAR2
2620 , x_trip_id OUT NOCOPY NUMBER
2621 ) IS
2622 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TRIP';
2623 l_api_version CONSTANT NUMBER := 1.0;
2624 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2625
2626 l_trips trip_tbl_type;
2627 l_resource csf_resource_pub.resource_tbl_type;
2628 l_new_trip trip_rec_type;
2629
2630 l_shift_tasks_exist VARCHAR2(1);
2631
2632 -- Query for the existence of any Shift Task in the Trip Inteval for the Resource.
2633 CURSOR c_st_exist IS
2634 SELECT 'Y'
2635 FROM jtf_tasks_b t
2636 WHERE t.owner_id = p_resource_id
2637 AND t.owner_type_code = p_resource_type
2638 AND t.scheduled_start_date BETWEEN p_start_date_time AND p_end_date_time
2639 AND t.task_type_id IN (20, 21)
2640 AND NVL(t.deleted_flag, 'N') = 'N'
2641 AND ROWNUM = 1;
2642 BEGIN
2643 -- Check for API Compatibility
2644 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2645 RAISE fnd_api.g_exc_unexpected_error;
2646 END IF;
2647
2648 -- Initialize Message Stack if required
2649 IF p_init_msg_list = fnd_api.g_true THEN
2650 fnd_msg_pub.initialize;
2651 END IF;
2652
2653 -- Initialize Return Status
2654 x_return_status := fnd_api.g_ret_sts_success;
2655
2656 IF l_debug THEN
2657 debug('Creating a Trip for Resource#' || p_resource_id || ' between '
2658 || to_char(p_start_date_time, 'DD-MON-YYYY HH24:MI:SS') || ' and '
2659 || to_char(p_end_date_time, 'DD-MON-YYYY HH24:MI:SS'), l_api_name, fnd_log.level_procedure);
2660 END IF;
2661
2662 l_resource := csf_resource_pub.resource_tbl_type();
2663 l_resource.extend();
2664 l_resource(1).resource_id := p_resource_id;
2665 l_resource(1).resource_type := p_resource_type;
2666 l_trips := find_trips(l_resource, p_start_date_time, p_end_date_time);
2667
2668 -- Check#1 - No Trips should be found for the given criteria
2669 IF l_trips.COUNT > 0 THEN
2670 IF l_debug THEN
2671 debug(' Trips already exists for the Resource in the specified interval', l_api_name, fnd_log.level_error);
2672 END IF;
2673 fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_DUP');
2674 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
2675 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
2676 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
2677 fnd_msg_pub.ADD;
2678 RAISE fnd_api.g_exc_error;
2679 END IF;
2680
2681 -- Check#2 - No Shift Tasks in the Interval where the Trip is going to be created.
2682 IF l_debug THEN
2683 debug(' Searching for existence of any Shift Tasks in that interval', l_api_name, fnd_log.level_statement);
2684 END IF;
2685
2686 OPEN c_st_exist;
2687 FETCH c_st_exist INTO l_shift_tasks_exist;
2688 IF c_st_exist%NOTFOUND THEN
2689 l_shift_tasks_exist := 'N';
2690 END IF;
2691 CLOSE c_st_exist;
2692
2693 IF l_shift_tasks_exist = 'Y' THEN
2694 IF l_debug THEN
2695 debug(' Shift Tasks exist for the Resource in the specified interval', l_api_name, fnd_log.level_error);
2696 END IF;
2697 fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_ST_EXIST');
2698 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
2699 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
2700 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
2701 fnd_msg_pub.ADD;
2702 RAISE fnd_api.g_exc_error;
2703 END IF;
2704
2705 -- All validations passed. Create the Trip.
2706 new_trip(
2707 x_return_status => x_return_status
2708 , x_msg_data => x_msg_data
2709 , x_msg_count => x_msg_count
2710 , p_resource_id => p_resource_id
2711 , p_resource_type => p_resource_type
2712 , p_start_date_time => p_start_date_time
2713 , p_end_date_time => p_end_date_time
2714 , p_status => p_status
2715 , p_schedule_detail_id => p_schedule_detail_id
2716 , p_find_tasks => p_find_tasks
2717 , x_trip => l_new_trip
2718 );
2719
2720 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2721 fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_OTHER');
2722 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
2723 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
2724 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
2725 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
2726 fnd_msg_pub.ADD;
2727 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2728 RAISE fnd_api.g_exc_unexpected_error;
2729 END IF;
2730 RAISE fnd_api.g_exc_error;
2731 END IF;
2732
2733 x_trip_id := l_new_trip.trip_id;
2734
2735 IF fnd_api.to_boolean(p_commit) THEN
2736 COMMIT;
2737 END IF;
2738
2739 EXCEPTION
2740 WHEN fnd_api.g_exc_error THEN
2741 x_return_status := fnd_api.g_ret_sts_error;
2742 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2743 WHEN fnd_api.g_exc_unexpected_error THEN
2744 x_return_status := fnd_api.g_ret_sts_unexp_error;
2745 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2746 WHEN OTHERS THEN
2747 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2748 x_return_status := fnd_api.g_ret_sts_unexp_error;
2749 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2750 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2751 END IF;
2752 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2753 END create_trip;
2754
2755 /**
2756 * Refer to the Package Spec for documentation of this procedure
2757 */
2758 PROCEDURE update_trip(
2759 p_api_version IN NUMBER
2760 , p_init_msg_list IN VARCHAR2
2761 , p_commit IN VARCHAR2
2762 , x_return_status OUT NOCOPY VARCHAR2
2763 , x_msg_data OUT NOCOPY VARCHAR2
2764 , x_msg_count OUT NOCOPY NUMBER
2765 , p_trip_id IN NUMBER
2766 , p_object_version_number IN NUMBER
2767 , p_available_hours IN NUMBER
2768 , p_upd_available_hours IN NUMBER
2769 , p_available_hours_before IN NUMBER
2770 , p_available_hours_after IN NUMBER
2771 , p_status IN NUMBER
2772 ) IS
2773 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP';
2774 l_api_version CONSTANT NUMBER := 1.0;
2775 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2776
2777 l_trip trip_rec_type;
2778 BEGIN
2779 -- Check for API Compatibility
2780 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2781 RAISE fnd_api.g_exc_unexpected_error;
2782 END IF;
2783
2784 -- Initialize Message Stack if required
2785 IF fnd_api.to_boolean(p_init_msg_list) THEN
2786 fnd_msg_pub.initialize;
2787 END IF;
2788
2789 -- Initialize Return Status
2790 x_return_status := fnd_api.g_ret_sts_success;
2791
2792 IF p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num THEN
2793 -- Invalid Trip ID passed.
2794 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2795 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2796 fnd_message.set_token('PARAM_NAME', 'P_TRIP_ID');
2797 fnd_msg_pub.ADD;
2798 RAISE fnd_api.g_exc_error;
2799 END IF;
2800
2801 IF p_object_version_number IS NULL OR p_object_version_number = fnd_api.g_miss_num THEN
2802 -- Invalid Object Version Number passed.
2803 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2804 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2805 fnd_message.set_token('PARAM_NAME', 'P_OBJECT_VERSION_NUMBER');
2806 fnd_msg_pub.ADD;
2807 RAISE fnd_api.g_exc_error;
2808 END IF;
2809
2810 IF p_available_hours IS NOT NULL AND p_upd_available_hours IS NOT NULL THEN
2811 -- Error out as both cant be passed.
2812 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2813 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2814 fnd_message.set_token('PARAM_NAME', 'P_UPD_AVAILABLE_HOURS');
2815 fnd_msg_pub.ADD;
2816 RAISE fnd_api.g_exc_error;
2817 END IF;
2818
2819 l_trip := get_trip(p_trip_id);
2820 IF l_trip.trip_id IS NULL THEN
2821 fnd_message.set_name('CSF', 'CSF_INVALID_TRIP_ID');
2822 fnd_message.set_token('TRIP_ID', p_trip_id);
2823 fnd_msg_pub.ADD;
2824 RAISE fnd_api.g_exc_error;
2825 END IF;
2826
2827 change_trip(
2828 x_return_status => x_return_status
2829 , x_msg_data => x_msg_data
2830 , x_msg_count => x_msg_count
2831 , p_trip => l_trip
2832 , p_object_version_number => p_object_version_number
2833 , p_available_hours => p_available_hours
2834 , p_upd_available_hours => p_upd_available_hours
2835 , p_available_hours_before => p_available_hours_before
2836 , p_available_hours_after => p_available_hours_after
2837 , p_status => p_status
2838 );
2839
2840 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2841 fnd_message.set_name('CSF', 'CSF_TRIP_UPDATE_FAIL_OTHER');
2842 fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id, l_trip.resource_type));
2843 fnd_message.set_token('START_TIME', format_date(l_trip.start_date_time));
2844 fnd_message.set_token('END_TIME', format_date(l_trip.end_date_time));
2845 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
2846 fnd_msg_pub.ADD;
2847 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2848 RAISE fnd_api.g_exc_unexpected_error;
2849 END IF;
2850 RAISE fnd_api.g_exc_error;
2851 END IF;
2852
2853 IF fnd_api.to_boolean(p_commit) THEN
2854 COMMIT;
2855 END IF;
2856
2857 EXCEPTION
2858 WHEN fnd_api.g_exc_error THEN
2859 x_return_status := fnd_api.g_ret_sts_error;
2860 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2861 WHEN fnd_api.g_exc_unexpected_error THEN
2862 x_return_status := fnd_api.g_ret_sts_unexp_error;
2863 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2864 WHEN OTHERS THEN
2865 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2866 x_return_status := fnd_api.g_ret_sts_unexp_error;
2867 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2868 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2869 END IF;
2870 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2871 END;
2872
2873 /**
2874 * Refer to the Package Spec for documentation of this procedure
2875 */
2876 PROCEDURE delete_trip (
2877 p_api_version IN NUMBER
2878 , p_init_msg_list IN VARCHAR2
2879 , p_commit IN VARCHAR2
2880 , x_return_status OUT NOCOPY VARCHAR2
2881 , x_msg_data OUT NOCOPY VARCHAR2
2882 , x_msg_count OUT NOCOPY NUMBER
2883 , p_trip_id IN NUMBER
2884 , p_object_version_number IN NUMBER
2885 ) IS
2886 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRIP';
2887 l_api_version CONSTANT NUMBER := 1.0;
2888 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2889 l_trip trip_rec_type;
2890 BEGIN
2891 SAVEPOINT delete_trip;
2892
2893 -- Check for API Compatibility
2894 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2895 RAISE fnd_api.g_exc_unexpected_error;
2896 END IF;
2897
2898 -- Initialize Message Stack if required
2899 IF fnd_api.to_boolean(p_init_msg_list) THEN
2900 fnd_msg_pub.initialize;
2901 END IF;
2902
2903 -- Initialize Return Status
2904 x_return_status := fnd_api.g_ret_sts_success;
2905
2906 IF l_debug THEN
2907 debug('Deleting the Trip #' || p_trip_id, l_api_name, fnd_log.level_procedure);
2908 END IF;
2909
2910 IF p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num THEN
2911 -- Invalid Trip ID passed.
2912 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2913 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2914 fnd_message.set_token('PARAM_NAME', 'P_TRIP_ID');
2915 fnd_msg_pub.ADD;
2916 RAISE fnd_api.g_exc_error;
2917 END IF;
2918
2919 IF p_object_version_number IS NULL OR p_object_version_number = fnd_api.g_miss_num THEN
2920 -- Invalid Object Version Number passed.
2921 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2922 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2923 fnd_message.set_token('PARAM_NAME', 'P_OBJECT_VERSION_NUMBER');
2924 fnd_msg_pub.ADD;
2925 RAISE fnd_api.g_exc_error;
2926 END IF;
2927
2928 l_trip := get_trip(p_trip_id);
2929 -- No Trips found for the given Trip ID
2930 IF l_trip.trip_id IS NULL THEN
2931 fnd_message.set_name('CSF', 'CSF_INVALID_TRIP_ID');
2932 fnd_message.set_token('TRIP_ID', p_trip_id);
2933 fnd_msg_pub.ADD;
2934 RAISE fnd_api.g_exc_error;
2935 END IF;
2936
2937 remove_trip(
2938 x_return_status => x_return_status
2939 , x_msg_data => x_msg_data
2940 , x_msg_count => x_msg_count
2941 , p_trip => l_trip
2942 , p_object_version_number => p_object_version_number
2943 );
2944
2945 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2946 IF l_debug THEN
2947 debug( ' Unable to delete the Trip: Error = '
2948 || fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2949 , l_api_name, fnd_log.level_error);
2950 END IF;
2951 fnd_message.set_name('CSF', 'CSF_TRIP_DELETE_FAIL_OTHER');
2952 fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id, l_trip.resource_type));
2953 fnd_message.set_token('START_TIME', format_date(l_trip.start_date_time));
2954 fnd_message.set_token('END_TIME', format_date(l_trip.end_date_time));
2955 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
2956 fnd_msg_pub.ADD;
2957 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2958 RAISE fnd_api.g_exc_unexpected_error;
2959 END IF;
2960 RAISE fnd_api.g_exc_error;
2961 END IF;
2962
2963 IF fnd_api.to_boolean(p_commit) THEN
2964 COMMIT;
2965 END IF;
2966 EXCEPTION
2967 WHEN fnd_api.g_exc_error THEN
2968 ROLLBACK TO delete_trip;
2969 x_return_status := fnd_api.g_ret_sts_error;
2970 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2971 WHEN fnd_api.g_exc_unexpected_error THEN
2972 ROLLBACK TO delete_trip;
2973 x_return_status := fnd_api.g_ret_sts_unexp_error;
2974 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2975 WHEN OTHERS THEN
2976 ROLLBACK TO delete_trip;
2977 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2978 x_return_status := fnd_api.g_ret_sts_unexp_error;
2979 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2980 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2981 END IF;
2982 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2983 END delete_trip;
2984
2985 /**
2986 * Refer to the Package Spec for documentation of this procedure
2987 */
2988 PROCEDURE fix_trip(
2989 p_api_version IN NUMBER
2990 , p_init_msg_list IN VARCHAR2
2991 , p_commit IN VARCHAR2
2992 , x_return_status OUT NOCOPY VARCHAR2
2993 , x_msg_data OUT NOCOPY VARCHAR2
2994 , x_msg_count OUT NOCOPY NUMBER
2995 , p_trip_id IN NUMBER
2996 , p_object_version_number IN NUMBER
2997 ) IS
2998 l_api_name CONSTANT VARCHAR2(30) := 'FIX_TRIPS';
2999 l_api_version CONSTANT NUMBER := 1.0;
3000 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3001
3002 l_trip trip_rec_type;
3003 BEGIN
3004 -- Check for API Compatibility
3005 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3006 RAISE fnd_api.g_exc_unexpected_error;
3007 END IF;
3008
3009 -- Initialize Message Stack if required
3010 IF fnd_api.to_boolean(p_init_msg_list) THEN
3011 fnd_msg_pub.initialize;
3012 END IF;
3013
3014 -- Initialize Return Status
3015 x_return_status := fnd_api.g_ret_sts_success;
3016
3017 IF l_debug THEN
3018 debug('Fixing the Trip #' || p_trip_id, l_api_name, fnd_log.level_procedure);
3019 END IF;
3020
3021 IF p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num THEN
3022 -- Invalid Trip ID passed.
3023 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
3024 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
3025 fnd_message.set_token('PARAM_NAME', 'P_TRIP_ID');
3026 fnd_msg_pub.ADD;
3027 RAISE fnd_api.g_exc_error;
3028 END IF;
3029
3030 IF p_object_version_number IS NULL OR p_object_version_number = fnd_api.g_miss_num THEN
3031 -- Invalid Object Version Number passed.
3032 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
3033 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
3034 fnd_message.set_token('PARAM_NAME', 'P_OBJECT_VERSION_NUMBER');
3035 fnd_msg_pub.ADD;
3036 RAISE fnd_api.g_exc_error;
3037 END IF;
3038
3039 l_trip := get_trip(p_trip_id);
3040 IF l_trip.trip_id IS NULL THEN
3041 fnd_message.set_name('CSF', 'CSF_INVALID_TRIP_ID');
3042 fnd_message.set_token('TRIP_ID', p_trip_id);
3043 fnd_msg_pub.ADD;
3044 RAISE fnd_api.g_exc_error;
3045 END IF;
3046
3047 correct_trip(
3048 x_return_status => x_return_status
3049 , x_msg_data => x_msg_data
3050 , x_msg_count => x_msg_count
3051 , p_trip => l_trip
3052 , p_object_version_number => p_object_version_number
3053 );
3054
3055 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3056 IF l_debug THEN
3057 debug(' Unable to fix the Trip', l_api_name, fnd_log.level_error);
3058 END IF;
3059 fnd_message.set_name('CSF', 'CSF_TRIP_FIX_FAIL_OTHER');
3060 fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id, l_trip.resource_type));
3061 fnd_message.set_token('START_TIME', format_date(l_trip.start_date_time));
3062 fnd_message.set_token('END_TIME', format_date(l_trip.end_date_time));
3063 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
3064 fnd_msg_pub.ADD;
3065 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3066 RAISE fnd_api.g_exc_unexpected_error;
3067 END IF;
3068 RAISE fnd_api.g_exc_error;
3069 END IF;
3070
3071 IF fnd_api.to_boolean(p_commit) THEN
3072 COMMIT;
3073 END IF;
3074 EXCEPTION
3075 WHEN fnd_api.g_exc_error THEN
3076 x_return_status := fnd_api.g_ret_sts_error;
3077 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3078 WHEN fnd_api.g_exc_unexpected_error THEN
3079 x_return_status := fnd_api.g_ret_sts_unexp_error;
3080 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3081 WHEN OTHERS THEN
3082 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3083 x_return_status := fnd_api.g_ret_sts_unexp_error;
3084 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3085 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3086 END IF;
3087 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3088 END fix_trip;
3089
3090 /******************************************************************************************
3091 * *
3092 * Public Functions and Procedures dealing generally on Trips *
3093 * *
3094 *******************************************************************************************/
3095
3096 /**
3097 * Refer to the Package Spec for documentation of this procedure
3098 */
3099 PROCEDURE find_trip(
3100 p_api_version IN NUMBER
3101 , p_init_msg_list IN VARCHAR2
3102 , x_return_status OUT NOCOPY VARCHAR2
3103 , x_msg_data OUT NOCOPY VARCHAR2
3104 , x_msg_count OUT NOCOPY NUMBER
3105 , p_resource_id IN NUMBER
3106 , p_resource_type IN VARCHAR2
3107 , p_start_date_time IN DATE
3108 , p_end_date_time IN DATE
3109 , p_overtime_flag IN VARCHAR2
3110 , x_trip OUT NOCOPY trip_rec_type
3111 ) IS
3112 l_api_name CONSTANT VARCHAR2(30) := 'FIND_TRIP';
3113 l_api_version CONSTANT NUMBER := 1.0;
3114 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3115 l_resource_tbl csf_resource_pub.resource_tbl_type;
3116 l_trips trip_tbl_type;
3117 BEGIN
3118 -- Check for API Compatibility
3119 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3120 RAISE fnd_api.g_exc_unexpected_error;
3121 END IF;
3122
3123 -- Initialize Message Stack if required
3124 IF fnd_api.to_boolean(p_init_msg_list) THEN
3125 fnd_msg_pub.initialize;
3126 END IF;
3127
3128 -- Initialize Return Status
3129 x_return_status := fnd_api.g_ret_sts_success;
3130
3131 l_resource_tbl := csf_resource_pub.resource_tbl_type();
3132 l_resource_tbl.extend();
3133 l_resource_tbl(1).resource_id := p_resource_id;
3134 l_resource_tbl(1).resource_type := p_resource_type;
3135
3136 l_trips := find_trips(l_resource_tbl, p_start_date_time, p_end_date_time, p_overtime_flag);
3137
3138 IF l_trips.COUNT = 0 OR l_trips.COUNT > 1 THEN
3139 IF l_trips.COUNT = 0 THEN
3140 fnd_message.set_name('CSF', 'CSF_NO_TRIPS_FOUND');
3141 ELSE
3142 fnd_message.set_name('CSF', 'CSF_MULTIPLE_TRIPS_FOUND');
3143 END IF;
3144 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
3145 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
3146 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
3147 fnd_msg_pub.add;
3148 RAISE fnd_api.g_exc_error;
3149 END IF;
3150
3151 x_trip := l_trips(l_trips.FIRST);
3152 EXCEPTION
3153 WHEN fnd_api.g_exc_error THEN
3154 x_return_status := fnd_api.g_ret_sts_error;
3155 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3156 WHEN fnd_api.g_exc_unexpected_error THEN
3157 x_return_status := fnd_api.g_ret_sts_unexp_error;
3158 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3159 WHEN OTHERS THEN
3160 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3161 x_return_status := fnd_api.g_ret_sts_unexp_error;
3162 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3163 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3164 END IF;
3165 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3166 END find_trip;
3167
3168 /**
3169 * Refer to the Package Spec for documentation of this procedure
3170 */
3171 PROCEDURE find_trip(
3172 p_api_version IN NUMBER
3173 , p_init_msg_list IN VARCHAR2
3174 , x_return_status OUT NOCOPY VARCHAR2
3175 , x_msg_data OUT NOCOPY VARCHAR2
3176 , x_msg_count OUT NOCOPY NUMBER
3177 , p_resource_id IN NUMBER
3178 , p_resource_type IN VARCHAR2
3179 , p_start_date_time IN DATE
3180 , p_end_date_time IN DATE
3181 , p_overtime_flag IN VARCHAR2
3182 , x_trip_id OUT NOCOPY NUMBER
3183 ) IS
3184 l_api_name CONSTANT VARCHAR2(30) := 'FIND_TRIP';
3185 l_api_version CONSTANT NUMBER := 1.0;
3186 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3187 l_trip trip_rec_type;
3188 BEGIN
3189 -- Check for API Compatibility
3190 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3191 RAISE fnd_api.g_exc_unexpected_error;
3192 END IF;
3193
3194 -- Initialize Message Stack if required
3195 IF fnd_api.to_boolean(p_init_msg_list) THEN
3196 fnd_msg_pub.initialize;
3197 END IF;
3198
3199 -- Initialize Return Status
3200 x_return_status := fnd_api.g_ret_sts_success;
3201
3202 find_trip(
3203 p_api_version => p_api_version
3204 , p_init_msg_list => p_init_msg_list
3205 , x_return_status => x_return_status
3206 , x_msg_data => x_msg_data
3207 , x_msg_count => x_msg_count
3208 , p_resource_id => p_resource_id
3209 , p_resource_type => p_resource_type
3210 , p_start_date_time => p_start_date_time
3211 , p_end_date_time => p_end_date_time
3212 , p_overtime_flag => p_overtime_flag
3213 , x_trip => l_trip
3214 );
3215
3216 IF x_return_status = fnd_api.g_ret_sts_success THEN
3217 x_trip_id := l_trip.trip_id;
3218 END IF;
3219
3220 EXCEPTION
3221 WHEN fnd_api.g_exc_error THEN
3222 x_return_status := fnd_api.g_ret_sts_error;
3223 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3224 WHEN fnd_api.g_exc_unexpected_error THEN
3225 x_return_status := fnd_api.g_ret_sts_unexp_error;
3226 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3227 WHEN OTHERS THEN
3228 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3229 x_return_status := fnd_api.g_ret_sts_unexp_error;
3230 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3231 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3232 END IF;
3233 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3234 END find_trip;
3235
3236 /**
3237 * Refer to the Package Spec for documentation of this procedure
3238 */
3239 PROCEDURE find_trips(
3240 p_api_version IN NUMBER
3241 , p_init_msg_list IN VARCHAR2
3242 , x_return_status OUT NOCOPY VARCHAR2
3243 , x_msg_data OUT NOCOPY VARCHAR2
3244 , x_msg_count OUT NOCOPY NUMBER
3245 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
3246 , p_start_date_time IN DATE
3247 , p_end_date_time IN DATE
3248 , p_overtime_flag IN VARCHAR2
3249 , x_trips OUT NOCOPY trip_tbl_type
3250 ) IS
3251 l_api_name CONSTANT VARCHAR2(30) := 'FIND_TRIP';
3252 l_api_version CONSTANT NUMBER := 1.0;
3253 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3254 BEGIN
3255 -- Check for API Compatibility
3256 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3257 RAISE fnd_api.g_exc_unexpected_error;
3258 END IF;
3259
3260 -- Initialize Message Stack if required
3261 IF fnd_api.to_boolean(p_init_msg_list) THEN
3262 fnd_msg_pub.initialize;
3263 END IF;
3264
3265 -- Initialize Return Status
3266 x_return_status := fnd_api.g_ret_sts_success;
3267
3268 x_trips := find_trips(p_resource_tbl, p_start_date_time, p_end_date_time, p_overtime_flag);
3269
3270 EXCEPTION
3271 WHEN fnd_api.g_exc_error THEN
3272 x_return_status := fnd_api.g_ret_sts_error;
3273 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3274 WHEN fnd_api.g_exc_unexpected_error THEN
3275 x_return_status := fnd_api.g_ret_sts_unexp_error;
3276 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3277 WHEN OTHERS THEN
3278 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3279 x_return_status := fnd_api.g_ret_sts_unexp_error;
3280 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3281 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3282 END IF;
3283 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3284 END find_trips;
3285
3286 /**
3287 * Refer to the Package Spec for documentation of this procedure
3288 */
3289 PROCEDURE process_action(
3290 p_api_version IN NUMBER
3291 , p_init_msg_list IN VARCHAR2
3292 , p_commit IN VARCHAR2
3293 , x_return_status OUT NOCOPY VARCHAR2
3294 , x_msg_data OUT NOCOPY VARCHAR2
3295 , x_msg_count OUT NOCOPY NUMBER
3296 , p_action IN VARCHAR2
3297 , p_trip_id IN NUMBER
3298 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
3299 , p_shift_type IN VARCHAR2
3300 , p_start_date IN DATE
3301 , p_end_date IN DATE
3302 ) IS
3303 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ACTION';
3304 l_api_version CONSTANT NUMBER := 1.0;
3305 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3306
3307 l_trips trip_tbl_type;
3308 l_trip trip_rec_type;
3309 l_param_name VARCHAR2(30);
3310 l_shift_type VARCHAR2(30);
3311
3312
3313
3314 BEGIN
3315 -- Check for API Compatibility
3316 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3317 RAISE fnd_api.g_exc_unexpected_error;
3318 END IF;
3319
3320 -- Initialize Message Stack if required
3321 IF fnd_api.to_boolean(p_init_msg_list) THEN
3322 fnd_msg_pub.initialize;
3323 END IF;
3324
3325 -- Initialize Return Status
3326 x_return_status := fnd_api.g_ret_sts_success;
3327 l_shift_type := p_shift_type;
3328 IF l_debug THEN
3329 debug('Generating Resource Trips for a Resource', l_api_name, fnd_log.level_procedure);
3330 debug(' Action = ' || p_action, l_api_name, fnd_log.level_statement);
3331 IF p_trip_id IS NOT NULL THEN
3332 debug(' Trip ID = ' || p_trip_id, l_api_name, fnd_log.level_statement);
3333 END IF;
3334
3335 IF p_start_date IS NOT NULL THEN
3336 debug(' Time Frame = ' || p_start_date || ' to ' || p_end_date, l_api_name, fnd_log.level_statement);
3337 END IF;
3338
3339 IF p_resource_tbl IS NOT NULL AND p_resource_tbl.COUNT = 1 THEN
3340 debug(' Resource = ' || p_resource_tbl(p_resource_tbl.FIRST).resource_id, l_api_name, fnd_log.level_statement);
3341 END IF;
3342 END IF;
3343
3344 -- Checking whether all required parameters are passed.
3345 IF p_action IN (g_action_block_trip, g_action_unblock_trip) THEN
3346 IF p_trip_id IS NULL AND (p_resource_tbl IS NULL OR p_start_date IS NULL OR p_end_date IS NULL) THEN
3347 IF (p_resource_tbl IS NOT NULL OR p_start_date IS NOT NULL OR p_end_date IS NOT NULL) THEN
3348 IF p_resource_tbl IS NULL THEN
3349 l_param_name := 'P_RESOURCE_TBL';
3350 ELSIF p_start_date IS NULL THEN
3351 l_param_name := 'P_START_DATE';
3352 ELSE
3353 l_param_name := 'P_END_DATE';
3354 END IF;
3355 ELSE
3356 l_param_name := 'P_TRIP_ID';
3357 END IF;
3358 END IF;
3359 ELSIF p_resource_tbl IS NULL THEN
3360 l_param_name := 'P_RESOURCE_TBL';
3361 ELSIF p_start_date IS NULL THEN
3362 l_param_name := 'P_START_DATE';
3363 ELSIF p_end_date IS NULL THEN
3364 l_param_name := 'P_END_DATE';
3365 END IF;
3366
3367 IF l_param_name IS NOT NULL THEN
3368 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
3369 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
3370 fnd_message.set_token('PARAM_NAME', l_param_name);
3371 fnd_msg_pub.ADD;
3372 RAISE fnd_api.g_exc_error;
3373 END IF;
3374
3375 -- Getting the Trips only for actions like DELETE, FIX, BLOCK, UNBLOCK.
3376 IF p_action NOT IN (g_action_create_trip, g_action_upgrade_trip, g_action_replace_trip) THEN
3377 IF p_trip_id IS NOT NULL THEN
3378 l_trips(1) := get_trip(p_trip_id);
3379 ELSE
3380 l_trips := find_trips(p_resource_tbl, p_start_date, p_end_date);
3381 END IF;
3382
3383 IF l_trips.COUNT = 0 THEN
3384 IF p_trip_id IS NOT NULL THEN
3385 fnd_message.set_name('CSF', 'CSF_INVALID_TRIP_ID');
3386 fnd_message.set_token('TRIP_ID', p_trip_id);
3387 ELSE
3388 fnd_message.set_name('CSF', 'CSF_NO_TRIPS_FOUND');
3389 IF p_resource_tbl.COUNT = 1 THEN
3390 fnd_message.set_token('RESOURCE', get_resource_info(
3391 p_resource_tbl(1).resource_id
3392 , p_resource_tbl(1).resource_type
3393 ));
3394 END IF;
3395 fnd_message.set_token('START_TIME', format_date(p_start_date));
3396 fnd_message.set_token('END_TIME', format_date(p_end_date));
3397 fnd_msg_pub.add;
3398 IF p_action = g_action_delete_trip
3399 THEN
3400 check_dangling_tasks(p_resource_tbl => p_resource_tbl
3401 , p_start => p_start_date
3402 , p_end => p_end_date
3403 , x_return_status => x_return_status
3404 , x_msg_data => x_msg_data
3405 , x_msg_count => x_msg_count);
3406 END IF;
3407 END IF;
3408 fnd_msg_pub.add;
3409 RAISE fnd_api.g_exc_error;
3410 END IF;
3411 END IF;
3412 g_messages.DELETE;
3413 IF p_action IN (g_action_create_trip, g_action_replace_trip) THEN
3414
3415
3416 create_trips(
3417 x_return_status => x_return_status
3418 , x_msg_data => x_msg_data
3419 , x_msg_count => x_msg_count
3420 , p_resource_tbl => p_resource_tbl
3421 , p_start_date => p_start_date
3422 , p_end_date => p_end_date
3423 , P_SHIFT_TYPE => l_shift_type
3424 , p_delete_trips => (p_action = g_action_replace_trip)
3425 );
3426 ELSIF p_action = g_action_upgrade_trip THEN
3427 upgrade_to_trips(
3428 x_return_status => x_return_status
3429 , x_msg_data => x_msg_data
3430 , x_msg_count => x_msg_count
3431 , p_resource_tbl => p_resource_tbl
3432 , p_start_date => p_start_date
3433 , p_end_date => p_end_date
3434 );
3435 ELSIF p_action = g_action_delete_trip THEN
3436 delete_trips(
3437 x_return_status => x_return_status
3438 , x_msg_data => x_msg_data
3439 , x_msg_count => x_msg_count
3440 , p_trips => l_trips
3441 );
3442 ELSIF p_action = g_action_fix_trip THEN
3443 fix_trips(
3444 x_return_status => x_return_status
3445 , x_msg_data => x_msg_data
3446 , x_msg_count => x_msg_count
3447 , p_trips => l_trips
3448 );
3449 ELSIF p_action IN (g_action_block_trip, g_action_unblock_trip, g_action_close_trip) THEN
3450 update_trip_status(
3451 x_return_status => x_return_status
3452 , x_msg_data => x_msg_data
3453 , x_msg_count => x_msg_count
3454 , p_trip_action => p_action
3455 , p_trips => l_trips
3456 );
3457 END IF;
3458
3459 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3460 RAISE fnd_api.g_exc_unexpected_error;
3461 END IF;
3462 IF p_action NOT IN (g_action_block_trip, g_action_unblock_trip)
3463 THEN
3464 check_dangling_tasks(p_resource_tbl => p_resource_tbl
3465 , p_start => p_start_date
3466 , p_end => p_end_date
3467 , x_return_status => x_return_status
3468 , x_msg_data => x_msg_data
3469 , x_msg_count => x_msg_count);
3470 check_duplicate_tasks(p_resource_tbl => p_resource_tbl
3471 , p_start => p_start_date
3472 , p_end => p_end_date
3473 , x_return_status => x_return_status
3474 , x_msg_data => x_msg_data
3475 , x_msg_count => x_msg_count);
3476 check_multiple_trip_tasks(p_resource_tbl => p_resource_tbl
3477 , p_start => p_start_date
3478 , p_end => p_end_date
3479 , x_return_status => x_return_status
3480 , x_msg_data => x_msg_data
3481 , x_msg_count => x_msg_count);
3482 END IF;
3483 process_messages(
3484 p_init_msg_list => p_init_msg_list
3485 , x_return_status => x_return_status
3486 , p_action => p_action
3487 , p_trip_id => p_trip_id
3488 , p_start_date => p_start_date
3489 , p_end_date => p_end_date
3490 , p_resource_tbl => p_resource_tbl
3491 );
3492
3493 IF fnd_api.to_boolean(p_commit) THEN
3494 COMMIT;
3495 END IF;
3496
3497 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3498 EXCEPTION
3499 WHEN fnd_api.g_exc_error THEN
3500 x_return_status := fnd_api.g_ret_sts_error;
3501 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3502 WHEN fnd_api.g_exc_unexpected_error THEN
3503 x_return_status := fnd_api.g_ret_sts_unexp_error;
3504 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3505 WHEN OTHERS THEN
3506 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3507 x_return_status := fnd_api.g_ret_sts_unexp_error;
3508 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3509 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3510 END IF;
3511 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3512 END process_action;
3513
3514 /******************************************************************************************
3515 * *
3516 * Functions and Procedures dealing with Generate Trips Concurrent Program *
3517 * *
3518 *******************************************************************************************/
3519
3520 PROCEDURE generate_trips(
3521 errbuf OUT NOCOPY VARCHAR2
3522 , retcode OUT NOCOPY VARCHAR2
3523 , p_action IN VARCHAR2
3524 , p_start_date IN VARCHAR2
3525 , p_num_days IN NUMBER
3526 , p_resource_type IN VARCHAR2
3527 , p_resource_id IN NUMBER
3528 , p_shift_type IN VARCHAR2 DEFAULT NULL
3529 , p_res_shift_add IN VARCHAR2 DEFAULT NULL
3530 ) IS
3531 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_TRIPS';
3532
3533 l_msg_data VARCHAR2(2000);
3534 l_msg_count NUMBER;
3535 l_return_status VARCHAR2(1);
3536 l_start_date DATE;
3537 l_end_date DATE;
3538 l_num_days NUMBER;
3539 l_resources_failed NUMBER;
3540 l_resources_success NUMBER;
3541 l_resource csf_resource_pub.resource_tbl_type;
3542 l_resource_info VARCHAR2(500);
3543 l_resource_id_tbl jtf_number_table;
3544 l_resource_type_tbl jtf_varchar2_table_100;
3545 l_shift_type VARCHAR2(100);
3546 l_shift_parameter varchar2(100);
3547 l_conv_end_date DATE;
3548
3549 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3550
3551 CURSOR C_RESOURCES IS
3552 SELECT RESOURCE_ID,
3553 RESOURCE_TYPE
3554 FROM(
3555 SELECT RESOURCE_ID,
3556 RESOURCE_TYPE
3557 FROM CSF_SELECTED_RESOURCES_V
3558 MINUS
3559 SELECT DISTINCT
3560 A.RESOURCE_ID,
3561 A.RESOURCE_TYPE
3562 FROM CSF_SELECTED_RESOURCES_V A,
3563 JTF_RS_DEFRESROLES_VL B,
3564 JTF_RS_ALL_RESOURCES_VL C,
3565 JTF_RS_ROLES_B D
3566 WHERE B.ROLE_RESOURCE_ID=A.RESOURCE_ID
3567 AND C.RESOURCE_ID = B.ROLE_RESOURCE_ID
3568 AND C.RESOURCE_TYPE =A.RESOURCE_TYPE
3569 AND D.ROLE_ID = B.ROLE_ID
3570 AND B.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
3571 AND NVL( B.DELETE_FLAG, 'N') = 'N'
3572 AND (SYSDATE >= TRUNC (B.RES_RL_START_DATE) OR B.RES_RL_START_DATE IS NULL)
3573 AND (SYSDATE <= TRUNC (B.RES_RL_END_DATE) + 1 OR B.RES_RL_END_DATE IS NULL)
3574 AND ROLE_CODE IN ( 'CSF_THIRD_PARTY_SERVICE_PROVID', 'CSF_THIRD_PARTY_ADMINISTRATOR')
3575 )
3576 ORDER BY RESOURCE_TYPE, RESOURCE_ID;
3577
3578
3579 BEGIN
3580 /******************* Concurrent Program Start Message *******************/
3581
3582 fnd_message.set_name('CSF', 'CSF_GTR_CP_STARTED');
3583 debug(fnd_message.get, 'GENERATE_TRIPS', g_level_cp_output);
3584
3585 init_package;
3586 g_suppress_res_info := TRUE;
3587
3588 g_res_add_prof :=p_res_shift_add;
3589 g_shift_type := p_shift_type;
3590 l_shift_parameter := p_shift_type;
3591
3592
3593
3594
3595 /************* Concurrent Program Input Parameters Validation *************/
3596
3597 -- Get the Start Date (with Timezone Conversions) from the passed Start Date
3598 IF p_start_date IS NOT NULL THEN
3599 l_start_date := fnd_date.canonical_to_date(p_start_date);
3600
3601 IF l_start_date < SYSDATE AND p_action IN (g_action_create_trip, g_action_replace_trip) THEN
3602 l_start_date := NULL;
3603 END IF;
3604 END IF;
3605 IF NOT (g_init_timezone)
3606 THEN
3607 fnd_date_tz.init_timezones_for_fnd_date;
3608 g_init_timezone:=TRUE;
3609 END IF;
3610
3611 IF l_start_date IS NULL THEN
3612 -- Get the System Date in Client Timezone
3613 l_start_date := csf_timezones_pvt.date_to_client_tz_date(SYSDATE);
3614 -- Convert the time to System Timezone
3615 l_start_date := csf_timezones_pvt.date_to_server_tz_date(TRUNC(l_start_date));
3616 END IF;
3617
3618 IF p_num_days IS NULL OR p_num_days <= 0 THEN
3619 l_num_days := CSR_SCHEDULER_PUB.GET_SCH_PARAMETER_VALUE('spPlanScope');
3620 IF l_num_days IS NULL OR l_num_days <=0 THEN
3621 l_num_days := 7;
3622 END IF;
3623 ELSE
3624 l_num_days := p_num_days;
3625 END IF;
3626
3627 IF p_action = g_action_close_trip THEN
3628 l_end_date := l_start_date;
3629 l_start_date := l_end_date - l_num_days + 1;
3630 ELSE
3631 l_end_date := l_start_date + l_num_days - 1;
3632 END IF;
3633 if p_shift_type = 'REGULAR AND STANDBY'
3634 then
3635 g_shift_type := null;
3636 l_shift_parameter := null;
3637 end if;
3638
3639 -- End Date will be 00:00 hours of the Start Date. So making it 23:59.
3640 l_end_date := l_end_date + (g_secs_in_day - 1) / g_secs_in_day;
3641
3642 -- added this for the bug 8410630
3643 /* l_conv_end_date:= fnd_timezones_pvt.adjust_datetime(
3644 date_time => l_end_date
3645 , from_tz => g_client_tz_code
3646 , to_tz => g_server_tz_code
3647 );
3648
3649 -- added if condition for the bug 8410630
3650 IF l_conv_end_date > l_end_date
3651 then
3652 l_end_date := l_conv_end_date;
3653 end if;*/
3654
3655 -- Concurrent Program Parameters
3656 IF p_resource_id IS NOT NULL AND p_resource_type IS NOT NULL THEN
3657 fnd_message.set_name('CSF', 'CSF_GTR_CP_PARAMS_RESOURCE');
3658 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
3659
3660 l_resource_info := fnd_message.get;
3661 l_resource_id_tbl := jtf_number_table();
3662 l_resource_id_tbl.extend(1);
3663 l_resource_id_tbl(1) := p_resource_id;
3664
3665 l_resource_type_tbl := jtf_varchar2_table_100();
3666 l_resource_type_tbl.extend(1);
3667 l_resource_type_tbl(1) := p_resource_type;
3668 ELSE
3669 l_resource_info := '';
3670
3671 OPEN c_resources;
3672 FETCH c_resources BULK COLLECT INTO l_resource_id_tbl, l_resource_type_tbl;
3673 CLOSE c_resources;
3674 END IF;
3675
3676 fnd_message.set_name('CSF', 'CSF_GTR_CP_PARAMS');
3677 fnd_message.set_token('ACTION', p_action);
3678 fnd_message.set_token('START_DATE', l_start_date);
3679 fnd_message.set_token('END_DATE', l_end_date);
3680 fnd_message.set_token('RESOURCE_INFO', l_resource_info);
3681 debug(fnd_message.get, 'GENERATE_TRIPS', g_level_cp_output);
3682
3683 /********************* Concurrent Program Execution *********************/
3684 l_resources_failed := 0;
3685 l_resources_success := 0;
3686 IF l_resource_id_tbl IS NOT NULL THEN
3687 l_resource := csf_resource_pub.resource_tbl_type();
3688 l_resource.extend(1);
3689
3690 FOR i IN 1..l_resource_id_tbl.COUNT LOOP
3691 l_resource(1).resource_id := l_resource_id_tbl(i);
3692 l_resource(1).resource_type := l_resource_type_tbl(i);
3693
3694 l_resource_info := get_resource_info(l_resource(1).resource_id, l_resource(1).resource_type);
3695 fnd_message.set_name('CSF', 'CSF_RESOURCE_PROCESSED');
3696 fnd_message.set_token('RESOURCE', l_resource_info);
3697 debug(fnd_message.get, 'GEN_RESOURCE_TRIPS', g_level_cp_output);
3698 IF l_debug THEN
3699 debug('*****Starting generating Trips for Resource ID #' || l_resource(1).resource_id||
3700 ' Resource Type #'|| l_resource(1).resource_type, l_api_name, fnd_log.level_statement);
3701 END IF;
3702 process_action(
3703 p_api_version => 1.0
3704 , p_init_msg_list => fnd_api.g_true
3705 , p_commit => fnd_api.g_true
3706 , x_return_status => l_return_status
3707 , x_msg_data => l_msg_data
3708 , x_msg_count => l_msg_count
3709 , p_action => p_action
3710 , p_resource_tbl => l_resource
3711 , p_shift_type => l_shift_parameter
3712 , p_start_date => l_start_date
3713 , p_end_date => l_end_date
3714 );
3715
3716 -- Print all the messages encountered
3717 FOR i IN 1..l_msg_count LOOP
3718 debug(' ' || fnd_msg_pub.get(i, fnd_api.g_false), l_api_name, g_level_cp_output);
3719 END LOOP;
3720 debug(' ', l_api_name, g_level_cp_output);
3721
3722 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3723 l_resources_failed := l_resources_failed + 1;
3724 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3725 RAISE fnd_api.g_exc_unexpected_error;
3726 END IF;
3727 ELSE
3728 l_resources_success := l_resources_success + 1;
3729 END IF;
3730 END LOOP;
3731 END IF;
3732
3733 /**************** Concurrent Program Completion Message ****************/
3734
3735 debug(' ', '', g_level_cp_output);
3736
3737 IF l_resources_failed > 0 THEN
3738 retcode := 1;
3739 fnd_message.set_name('CSF', 'CSF_CP_DONE_WARNING');
3740 ELSE
3741 retcode := 0;
3742 fnd_message.set_name('CSF', 'CSF_CP_DONE_SUCCESS');
3743 END IF;
3744
3745 errbuf := fnd_message.get;
3746 debug(errbuf, l_api_name, g_level_cp_output);
3747
3748 debug(' ', '', g_level_cp_output);
3749 fnd_message.set_name('CSF', 'CSF_RESOURCES_DONE_SUCCESS');
3750 fnd_message.set_token('NUMBER', l_resources_success);
3751 debug(fnd_message.get, l_api_name, g_level_cp_output);
3752
3753 fnd_message.set_name('CSF', 'CSF_RESOURCES_DONE_FAILED');
3754 fnd_message.set_token('NUMBER', l_resources_failed);
3755 debug(fnd_message.get, l_api_name, g_level_cp_output);
3756
3757 fnd_message.set_name('CSF', 'CSF_RESOURCES_DONE_TOTAL');
3758 fnd_message.set_token('NUMBER', l_resources_success + l_resources_failed);
3759 debug(fnd_message.get, l_api_name, g_level_cp_output);
3760 EXCEPTION
3761 WHEN OTHERS THEN
3762 IF SQLERRM IS NOT NULL THEN
3763
3764 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3765 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3766 debug(fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false), l_api_name, g_level_cp_output);
3767 END IF;
3768 END IF;
3769
3770 retcode := 2;
3771 fnd_message.set_name('CSF', 'CSF_CP_DONE_ERROR');
3772 errbuf := fnd_message.get;
3773 debug(errbuf, l_api_name, g_level_cp_output);
3774 END generate_trips;
3775
3776
3777 PROCEDURE optimize_across_trips(
3778 p_api_version IN NUMBER
3779 , p_init_msg_list IN VARCHAR2
3780 , p_commit IN VARCHAR2
3781 , x_return_status OUT NOCOPY VARCHAR2
3782 , x_msg_data OUT NOCOPY VARCHAR2
3783 , x_msg_count OUT NOCOPY NUMBER
3784 , x_conc_request_id OUT NOCOPY NUMBER
3785 , p_resource_tbl IN csf_requests_pvt.resource_tbl_type
3786 , p_start_date IN DATE
3787 , p_end_date IN DATE
3788 ) IS
3789 l_api_name CONSTANT VARCHAR2(30) := 'OPTIMIZE_ACROSS_TRIPS';
3790 l_api_version CONSTANT NUMBER := 1.0;
3791 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3792
3793 l_sched_request_id NUMBER DEFAULT NULL;
3794 l_conc_request_id NUMBER DEFAULT NULL;
3795 l_oat_string VARCHAR2(100) DEFAULT NULL;
3796 --
3797 l_resources_tbl csf_requests_pvt.resource_tbl_type;
3798 BEGIN
3799 -- Check for API Compatibility
3800 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3801 RAISE fnd_api.g_exc_unexpected_error;
3802 END IF;
3803
3804 -- Initialize Message Stack if required
3805 IF fnd_api.to_boolean(p_init_msg_list) THEN
3806 fnd_msg_pub.initialize;
3807 END IF;
3808
3809 -- Initialize Return Status
3810 x_return_status := fnd_api.g_ret_sts_success;
3811
3812 IF l_debug THEN
3813 debug('CSF_TRIPS_PUB.Optimize Across Trips', l_api_name, fnd_log.level_procedure);
3814 debug(' No of resources in list = ' || p_resource_tbl.COUNT, l_api_name, fnd_log.level_statement);
3815 debug(' Time Frame = ' || p_start_date || ' to ' || p_end_date, l_api_name, fnd_log.level_statement);
3816 END IF;
3817
3818 l_resources_tbl := p_resource_tbl;
3819 FOR i IN 1..l_resources_tbl.COUNT LOOP
3820 l_resources_tbl(i).planwin_start := p_start_date;
3821 l_resources_tbl(i).planwin_end := p_end_date;
3822 END LOOP;
3823
3824 -- create a scheduler request
3825 csf_requests_pvt.create_scheduler_request (
3826 p_api_version => 1.0
3827 , x_return_status => x_return_status
3828 , x_msg_count => x_msg_count
3829 , x_msg_data => x_msg_data
3830 , p_name => 'OptimizeAcrossTrips'
3831 , p_object_id => -1
3832 , p_resource_tbl => l_resources_tbl
3833 , x_request_id => l_sched_request_id
3834 );
3835
3836 -- Standard check of the return status for the API call
3837 IF x_return_status = fnd_api.g_ret_sts_error THEN
3838 RAISE fnd_api.g_exc_error;
3839 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3840 RAISE fnd_api.g_exc_unexpected_error;
3841 END IF;
3842
3843 fnd_message.set_name('CSR','OPTIMIZE_ACROSS_TRIPS');
3844 l_oat_string := fnd_message.get;
3845
3846 -- submit the concurrent request 'Optimize Across Trips'
3847 x_conc_request_id := fnd_request.submit_request (
3848 application => 'CSR'
3849 , program => 'OPTIMIZE_ACROSS_TRIPS'
3850 , sub_request => FALSE
3851 , argument1 => l_sched_request_id
3852 );
3853
3854 IF x_conc_request_id = 0 THEN
3855 -- FND_REQUEST.SUBMIT_REQUEST should have populated the Message Stack.
3856 x_return_status := fnd_api.g_ret_sts_error;
3857 RAISE fnd_api.g_exc_error;
3858 END IF;
3859
3860 -- needed to submit the request properly
3861 COMMIT;
3862
3863 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3864 EXCEPTION
3865 WHEN fnd_api.g_exc_error THEN
3866 ROLLBACK;
3867 x_return_status := fnd_api.g_ret_sts_error;
3868 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3869 WHEN fnd_api.g_exc_unexpected_error THEN
3870 ROLLBACK;
3871 x_return_status := fnd_api.g_ret_sts_unexp_error;
3872 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3873 WHEN OTHERS THEN
3874 ROLLBACK;
3875 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3876 x_return_status := fnd_api.g_ret_sts_unexp_error;
3877 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3878 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3879 END IF;
3880 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3881 END optimize_across_trips;
3882
3883 PROCEDURE create_trip1(
3884 p_api_version IN NUMBER
3885 , p_init_msg_list IN VARCHAR2
3886 , p_commit IN VARCHAR2
3887 , x_return_status OUT NOCOPY VARCHAR2
3888 , x_msg_data OUT NOCOPY VARCHAR2
3889 , x_msg_count OUT NOCOPY NUMBER
3890 , p_resource_id IN NUMBER
3891 , p_resource_type IN VARCHAR2
3892 , p_start_date_time IN DATE
3893 , p_end_date_time IN DATE
3894 , p_schedule_detail_id IN NUMBER
3895 , p_status IN NUMBER
3896 , p_find_tasks IN VARCHAR2
3897 , p_arr_party_site IN NUMBER
3898 , p_arr_party IN NUMBER
3899 , p_dep_party_site IN NUMBER
3900 , p_dep_party IN NUMBER
3901 , p_shift_type IN VARCHAR2
3902 , x_trip_id OUT NOCOPY NUMBER
3903 ) IS
3904 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TRIP';
3905 l_api_version CONSTANT NUMBER := 1.0;
3906 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3907
3908 l_trips trip_tbl_type;
3909 l_resource csf_resource_pub.resource_tbl_type;
3910 l_new_trip trip_rec_type;
3911
3912 l_shift_tasks_exist VARCHAR2(1);
3913 l_trip_exist VARCHAR2(1);
3914 l_overtime NUMBER;
3915
3916 -- Query for the existence of any Shift Task in the Trip Inteval for the Resource.
3917 CURSOR c_st_exist IS
3918 SELECT 'Y'
3919 FROM jtf_tasks_b t
3920 WHERE t.owner_id = p_resource_id
3921 AND t.owner_type_code = p_resource_type
3922 AND t.scheduled_start_date BETWEEN p_start_date_time AND p_end_date_time
3923 AND t.task_type_id IN (20, 21)
3924 AND NVL(t.deleted_flag, 'N') = 'N'
3925 AND ROWNUM = 1;
3926
3927 CURSOR c_trip_exist
3928 is
3929 SELECT 'Y'
3930 FROM cac_sr_object_capacity
3931 WHERE p_start_date_time <= (end_date_time + g_overtime)
3932 AND p_end_date_time >= start_date_time
3933 AND object_id=p_resource_id;
3934
3935 BEGIN
3936 -- Check for API Compatibility
3937 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3938 RAISE fnd_api.g_exc_unexpected_error;
3939 END IF;
3940
3941 -- Initialize Message Stack if required
3942 IF p_init_msg_list = fnd_api.g_true THEN
3943 fnd_msg_pub.initialize;
3944 END IF;
3945
3946 -- Initialize Return Status
3947 x_return_status := fnd_api.g_ret_sts_success;
3948
3949
3950
3951 OPEN c_st_exist;
3952 FETCH c_st_exist INTO l_shift_tasks_exist;
3953 IF c_st_exist%NOTFOUND THEN
3954 l_shift_tasks_exist := 'N';
3955 END IF;
3956 CLOSE c_st_exist;
3957
3958 OPEN c_trip_exist;
3959 FETCH c_trip_exist INTO l_trip_exist;
3960 IF c_trip_exist%NOTFOUND THEN
3961 l_trip_exist := 'N';
3962 END IF;
3963 CLOSE c_trip_exist;
3964
3965 IF (l_shift_tasks_exist = 'Y' or l_trip_exist = 'Y') THEN
3966 IF l_debug THEN
3967 debug(' Shift Tasks exist for the Resource in the specified interval', l_api_name, fnd_log.level_error);
3968 END IF;
3969 fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_ST_EXIST');
3970 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
3971 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
3972 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
3973 fnd_msg_pub.ADD;
3974 RAISE fnd_api.g_exc_error;
3975 END IF;
3976
3977 -- All validations passed. Create the Trip.
3978 new_trip1(
3979 x_return_status => x_return_status
3980 , x_msg_data => x_msg_data
3981 , x_msg_count => x_msg_count
3982 , p_resource_id => p_resource_id
3983 , p_resource_type => p_resource_type
3984 , p_start_date_time => p_start_date_time
3985 , p_end_date_time => p_end_date_time
3986 , p_status => p_status
3987 , p_schedule_detail_id => p_schedule_detail_id
3988 , p_find_tasks => p_find_tasks
3989 , p_arr_party_site => p_arr_party_site
3990 , p_arr_party => p_arr_party
3991 , p_dep_party_site => p_dep_party_site
3992 , p_dep_party => p_dep_party
3993 , p_shift_type => p_shift_type
3994 , x_trip => l_new_trip
3995 );
3996
3997 l_resource := csf_resource_pub.resource_tbl_type();
3998 l_resource.extend(1);
3999 l_resource(1).resource_id := p_resource_id;
4000 l_resource(1).resource_type := p_resource_type;
4001
4002 check_multiple_trip_tasks(p_resource_tbl => l_resource
4003 , p_start => p_start_date_time
4004 , p_end => p_end_date_time
4005 , x_return_status => x_return_status
4006 , x_msg_data => x_msg_data
4007 , x_msg_count => x_msg_count);
4008
4009 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4010 fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_OTHER');
4011 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
4012 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
4013 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
4014 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
4015 fnd_msg_pub.ADD;
4016 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4017 RAISE fnd_api.g_exc_unexpected_error;
4018 END IF;
4019 RAISE fnd_api.g_exc_error;
4020 END IF;
4021
4022 x_trip_id := l_new_trip.trip_id;
4023
4024 IF fnd_api.to_boolean(p_commit) THEN
4025 COMMIT;
4026 END IF;
4027
4028 EXCEPTION
4029 WHEN fnd_api.g_exc_error THEN
4030 x_return_status := fnd_api.g_ret_sts_error;
4031 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4032 WHEN fnd_api.g_exc_unexpected_error THEN
4033 x_return_status := fnd_api.g_ret_sts_unexp_error;
4034 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4035 WHEN OTHERS THEN
4036 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
4037 x_return_status := fnd_api.g_ret_sts_unexp_error;
4038 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4039 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4040 END IF;
4041 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4042 END create_trip1;
4043
4044
4045 PROCEDURE new_trip1(
4046 x_return_status OUT NOCOPY VARCHAR2
4047 , x_msg_data OUT NOCOPY VARCHAR2
4048 , x_msg_count OUT NOCOPY NUMBER
4049 , p_resource_id IN NUMBER
4050 , p_resource_type IN VARCHAR2
4051 , p_start_date_time IN DATE
4052 , p_end_date_time IN DATE
4053 , p_status IN NUMBER DEFAULT NULL
4054 , p_schedule_detail_id IN NUMBER DEFAULT NULL
4055 , p_find_tasks IN VARCHAR2 DEFAULT NULL
4056 , p_dep_task_id IN NUMBER DEFAULT NULL
4057 , p_arr_task_id IN NUMBER DEFAULT NULL
4058 , p_arr_party_site IN NUMBER
4059 , p_arr_party IN NUMBER
4060 , p_dep_party_site IN NUMBER
4061 , p_dep_party IN NUMBER
4062 , p_shift_type IN VARCHAR2
4063 , x_trip OUT NOCOPY trip_rec_type
4064 ) IS
4065 l_api_name CONSTANT VARCHAR2(30) := 'NEW_TRIP';
4066 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
4067
4068 l_available_hours NUMBER;
4069 l_time_occupied NUMBER;
4070 l_dep_task_id NUMBER;
4071 l_arr_task_id NUMBER;
4072 i PLS_INTEGER;
4073 l_object_capacity_tbl cac_sr_object_capacity_pub.object_capacity_tbl_type;
4074 l_object_tasks_tbl cac_sr_object_capacity_pub.object_tasks_tbl_type;
4075
4076 CURSOR c_linkable_tasks IS
4077 SELECT ta.task_assignment_id
4078 , ta.object_version_number
4079 , ta.task_id
4080 , ta.booking_start_date
4081 , ta.booking_end_date
4082 , csf_util_pvt.convert_to_minutes(
4083 ta.sched_travel_duration
4084 , ta.sched_travel_duration_uom
4085 ) travel_time
4086 FROM jtf_task_assignments ta
4087 , jtf_task_statuses_b ts
4088 , jtf_tasks_b t
4089 WHERE ta.resource_id = p_resource_id
4090 AND ta.resource_type_code = p_resource_type
4091 AND ta.assignee_role = 'ASSIGNEE'
4092 AND ts.task_status_id = ta.assignment_status_id
4093 AND NVL(ts.closed_flag, 'N') = 'N'
4094 AND NVL(ts.completed_flag, 'N') = 'N'
4095 AND NVL(ts.cancelled_flag, 'N') = 'N'
4096 AND t.task_id = ta.task_id
4097 AND NVL(t.deleted_flag, 'N') <> 'Y'
4098 AND ta.booking_start_date <= (p_end_date_time + g_overtime)
4099 AND ta.booking_end_date >= p_start_date_time
4100 AND (t.task_type_id NOT IN (20, 21) OR t.task_id IN (l_dep_task_id, l_arr_task_id));
4101
4102 CURSOR c_shift_tasks_info IS
4103 SELECT ta.task_assignment_id, ta.object_version_number, ta.task_id
4104 FROM jtf_task_assignments ta
4105 WHERE ta.task_id IN (l_dep_task_id, l_arr_task_id);
4106
4107 BEGIN
4108 SAVEPOINT csf_new_trip;
4109
4110 x_return_status := fnd_api.g_ret_sts_success;
4111
4112 IF l_debug THEN
4113 debug(' Creating Trip between ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
4114 END IF;
4115
4116 -- Trip Available Hours
4117 l_available_hours := (p_end_date_time - p_start_date_time) * g_hours_in_day;
4118
4119 -- Check#3 - The Trip Duration should be lesser than 24 Hours.
4120 IF l_available_hours > g_hours_in_day THEN
4121 IF check_dst(p_resource_id,p_start_date_time,p_end_date_time) = 'FALSE'
4122 THEN
4123 IF l_debug THEN
4124 debug(' The specified Trip Length is greater than one day', l_api_name, fnd_log.level_error);
4125 END IF;
4126 fnd_message.set_name('CSF', 'CSF_TRIP_LENGTH_MORE_THAN_DAY');
4127 fnd_msg_pub.ADD;
4128 RAISE fnd_api.g_exc_error;
4129 END IF;
4130
4131 END IF;
4132
4133
4134 -- Create new Shift Tasks for the Trip to be created.
4135 IF p_dep_task_id IS NULL OR p_arr_task_id IS NULL THEN
4136 create_shift_tasks1(
4137 p_api_version => 1.0
4138 , p_init_msg_list => fnd_api.g_false
4139 , p_commit => fnd_api.g_false
4140 , x_return_status => x_return_status
4141 , x_msg_data => x_msg_data
4142 , x_msg_count => x_msg_count
4143 , p_resource_id => p_resource_id
4144 , p_resource_type => p_resource_type
4145 , p_start_date_time => p_start_date_time
4146 , p_end_date_time => p_end_date_time
4147 , p_create_dep_task => p_dep_task_id IS NULL
4148 , p_create_arr_task => p_arr_task_id IS NULL
4149 , p_arr_party_site => p_arr_party_site
4150 , p_arr_party => p_arr_party
4151 , p_dep_party_site => p_dep_party_site
4152 , p_dep_party => p_dep_party
4153 , x_dep_task_id => l_dep_task_id
4154 , x_arr_task_id => l_arr_task_id
4155 );
4156
4157 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4158 IF l_debug THEN
4159 debug(' Unable to Create Shift Tasks: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
4160 END IF;
4161 IF x_return_status = fnd_api.g_ret_sts_error THEN
4162 RAISE fnd_api.g_exc_error;
4163 ELSE
4164 RAISE fnd_api.g_exc_unexpected_error;
4165 END IF;
4166 END IF;
4167 IF l_debug THEN
4168 debug(' Created new Shift Tasks - Dep#' || l_dep_task_id || ' : Arr#' || l_arr_task_id, l_api_name, fnd_log.level_statement);
4169 END IF;
4170 l_dep_task_id := NVL(p_dep_task_id, l_dep_task_id);
4171 l_arr_task_id := NVL(p_arr_task_id, l_arr_task_id);
4172 ELSE
4173 -- Use the existing ones.
4174 l_dep_task_id := p_dep_task_id;
4175 l_arr_task_id := p_arr_task_id;
4176 IF l_debug THEN
4177 debug(' Using existing Shift Tasks - Dep#' || l_dep_task_id || ' : Arr#' || l_arr_task_id, l_api_name, fnd_log.level_statement);
4178 END IF;
4179 END IF;
4180
4181 i := 0;
4182 IF p_find_tasks IS NULL OR p_find_tasks = fnd_api.g_true THEN
4183 FOR v_task IN c_linkable_tasks LOOP
4184 --l_time_occupied := v_task.booking_end_date - v_task.booking_start_date; -- Scheduled Task Duration
4185 --l_time_occupied := l_time_occupied + NVL(v_task.travel_time, 0) / g_mins_in_day; -- Scheduled Travel Duration
4186 --l_available_hours := l_available_hours - l_time_occupied * g_hours_in_day;
4187
4188 IF l_debug THEN
4189 debug(' Linking TaskID #' || v_task.task_id || ' : Time Used = ' || l_time_occupied * g_hours_in_day, l_api_name, fnd_log.level_statement);
4190 END IF;
4191
4192 i := i + 1;
4193 l_object_tasks_tbl(i).task_assignment_id := v_task.task_assignment_id;
4194 l_object_tasks_tbl(i).task_assignment_ovn := v_task.object_version_number;
4195 l_object_tasks_tbl(i).object_capacity_tbl_idx := 1;
4196 END LOOP;
4197 ELSE
4198 FOR v_task IN c_shift_tasks_info LOOP
4199 IF l_debug THEN
4200 debug(' Linking Shift TaskID #' || v_task.task_id, l_api_name, fnd_log.level_statement);
4201 END IF;
4202
4203 i := i + 1;
4204 l_object_tasks_tbl(i).task_assignment_id := v_task.task_assignment_id;
4205 l_object_tasks_tbl(i).task_assignment_ovn := v_task.object_version_number;
4206 l_object_tasks_tbl(i).object_capacity_tbl_idx := 1;
4207 END LOOP;
4208 END IF;
4209
4210 -- Create the Object Capacity Record
4211 l_object_capacity_tbl(1).object_type := p_resource_type;
4212 l_object_capacity_tbl(1).object_id := p_resource_id;
4213 l_object_capacity_tbl(1).start_date_time := p_start_date_time;
4214 l_object_capacity_tbl(1).end_date_time := p_end_date_time;
4215 l_object_capacity_tbl(1).available_hours := l_available_hours;
4216 l_object_capacity_tbl(1).status := p_status;
4217 l_object_capacity_tbl(1).availability_type := p_shift_type;
4218 l_object_capacity_tbl(1).schedule_detail_id := p_schedule_detail_id;
4219
4220 IF l_debug THEN
4221 debug(' Trip Available Hours = ' || l_available_hours, l_api_name, fnd_log.level_statement);
4222 END IF;
4223
4224 -- Create the Trip by calling Object Capacity Table Handlers
4225 cac_sr_object_capacity_pub.insert_object_capacity(
4226 p_api_version => 1.0
4227 , p_init_msg_list => fnd_api.g_false
4228 , x_return_status => x_return_status
4229 , x_msg_count => x_msg_count
4230 , x_msg_data => x_msg_data
4231 , p_object_capacity => l_object_capacity_tbl
4232 , p_update_tasks => fnd_api.g_true
4233 , p_object_tasks => l_object_tasks_tbl
4234 );
4235
4236 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4237 IF l_debug THEN
4238 x_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
4239 debug(' Unable to Create the Object Capacity: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
4240 END IF;
4241 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4242 RAISE fnd_api.g_exc_unexpected_error;
4243 END IF;
4244 RAISE fnd_api.g_exc_error;
4245 END IF;
4246
4247 x_trip.trip_id := l_object_capacity_tbl(1).object_capacity_id;
4248 x_trip.object_version_number := 1;
4249 x_trip.resource_id := p_resource_id;
4250 x_trip.resource_type := p_resource_type;
4251 x_trip.start_date_time := p_start_date_time;
4252 x_trip.end_date_time := p_end_date_time;
4253 x_trip.available_hours := l_available_hours;
4254 x_trip.status := p_status;
4255 x_trip.schedule_detail_id := p_schedule_detail_id;
4256
4257 IF l_debug THEN
4258 debug(' Created Trip - TripID#' || x_trip.trip_id, l_api_name, fnd_log.level_statement);
4259 END IF;
4260 EXCEPTION
4261 WHEN fnd_api.g_exc_error THEN
4262 ROLLBACK TO csf_new_trip;
4263 x_return_status := fnd_api.g_ret_sts_error;
4264 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4265 WHEN fnd_api.g_exc_unexpected_error THEN
4266 ROLLBACK TO csf_new_trip;
4267 x_return_status := fnd_api.g_ret_sts_unexp_error;
4268 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4269 WHEN OTHERS THEN
4270 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
4271 x_return_status := fnd_api.g_ret_sts_unexp_error;
4272 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4273 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4274 END IF;
4275 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4276 ROLLBACK TO csf_new_trip;
4277 END new_trip1;
4278
4279
4280
4281 PROCEDURE create_shift_tasks1(
4282 p_api_version IN NUMBER
4283 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
4284 , p_commit IN VARCHAR2 DEFAULT NULL
4285 , x_return_status OUT NOCOPY VARCHAR2
4286 , x_msg_data OUT NOCOPY VARCHAR2
4287 , x_msg_count OUT NOCOPY NUMBER
4288 , p_resource_id IN NUMBER
4289 , p_resource_type IN VARCHAR2
4290 , p_start_date_time IN DATE
4291 , p_end_date_time IN DATE
4292 , p_create_dep_task IN BOOLEAN
4293 , p_create_arr_task IN BOOLEAN
4294 , p_arr_party_site IN NUMBER
4295 , p_arr_party IN NUMBER
4296 , p_dep_party_site IN NUMBER
4297 , p_dep_party IN NUMBER
4298 , x_dep_task_id OUT NOCOPY NUMBER
4299 , x_arr_task_id OUT NOCOPY NUMBER
4300 ) IS
4301 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SHIFT_TASKS';
4302 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
4303 l_address csf_resource_address_pvt.address_rec_type;
4304 l_task_assign_tbl jtf_tasks_pub.task_assign_tbl;
4305 BEGIN
4306
4307 IF p_create_dep_task = FALSE AND p_create_arr_task = FALSE THEN
4308 RETURN;
4309 END IF;
4310
4311
4312 -- Departure and Arrival Task Resource Assignment
4313 l_task_assign_tbl(1).resource_id := p_resource_id;
4314 l_task_assign_tbl(1).resource_type_code := p_resource_type;
4315 l_task_assign_tbl(1).assignment_status_id := g_assigned_status_id;
4316
4317 -- Create the Departure Task
4318 IF p_create_dep_task THEN
4319 jtf_tasks_pub.create_task(
4320 p_api_version => 1.0
4321 , p_task_name => g_dep_task_name
4322 , p_task_type_id => g_dep_task_type_id
4323 , p_task_status_id => g_assigned_status_id
4324 , p_owner_id => p_resource_id
4325 , p_owner_type_code => p_resource_type
4326 , p_address_id => p_dep_party_site
4327 , p_customer_id => p_dep_party
4328 , p_planned_start_date => p_start_date_time
4329 , p_planned_end_date => p_start_date_time
4330 , p_scheduled_start_date => p_start_date_time
4331 , p_scheduled_end_date => p_start_date_time
4332 , p_duration => 0
4333 , p_duration_uom => g_duration_uom
4334 , p_bound_mode_code => 'BTS'
4335 , p_soft_bound_flag => 'Y'
4336 , p_task_assign_tbl => l_task_assign_tbl
4337 , x_return_status => x_return_status
4338 , x_msg_count => x_msg_count
4339 , x_msg_data => x_msg_data
4340 , x_task_id => x_dep_task_id
4341 );
4342
4343 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4344 fnd_message.set_name('CSF', 'CSF_TASK_CREATE_FAIL');
4345 fnd_message.set_token('TASK_NAME', g_dep_task_name);
4346 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
4347 fnd_msg_pub.ADD;
4348
4349 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4350 RAISE fnd_api.g_exc_unexpected_error;
4351 END IF;
4352 RAISE fnd_api.g_exc_error;
4353 END IF;
4354
4355 IF l_debug THEN
4356 debug(' Created Departure Task - Task ID = ' || x_dep_task_id, l_api_name, fnd_log.level_statement);
4357 END IF;
4358 END IF;
4359
4360 -- Create the Arrival Task
4361 IF p_create_arr_task THEN
4362 jtf_tasks_pub.create_task(
4363 p_api_version => 1.0
4364 , p_task_name => g_arr_task_name
4365 , p_task_type_id => g_arr_task_type_id
4366 , p_task_status_id => g_assigned_status_id
4367 , p_owner_id => p_resource_id
4368 , p_owner_type_code => p_resource_type
4369 , p_address_id => p_arr_party_site
4370 , p_customer_id => p_arr_party
4371 , p_planned_start_date => p_end_date_time
4372 , p_planned_end_date => p_end_date_time
4373 , p_scheduled_start_date => p_end_date_time
4374 , p_scheduled_end_date => p_end_date_time
4375 , p_duration => 0
4376 , p_duration_uom => g_duration_uom
4377 , p_bound_mode_code => 'BTS'
4378 , p_soft_bound_flag => 'Y'
4379 , p_task_assign_tbl => l_task_assign_tbl
4380 , x_return_status => x_return_status
4381 , x_msg_count => x_msg_count
4382 , x_msg_data => x_msg_data
4383 , x_task_id => x_arr_task_id
4384 );
4385
4386 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4387 fnd_message.set_name('CSF', 'CSF_TASK_CREATE_FAIL');
4388 fnd_message.set_token('TASK_NAME', g_arr_task_name);
4389 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
4390 fnd_msg_pub.ADD;
4391
4392 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4393 RAISE fnd_api.g_exc_unexpected_error;
4394 END IF;
4395 RAISE fnd_api.g_exc_error;
4396 END IF;
4397 IF l_debug THEN
4398 debug(' Created Arrival Task - Task ID = ' || x_arr_task_id, l_api_name, fnd_log.level_statement);
4399 END IF;
4400 END IF;
4401 EXCEPTION
4402 WHEN fnd_api.g_exc_error THEN
4403 x_return_status := fnd_api.g_ret_sts_error;
4404 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4405 WHEN fnd_api.g_exc_unexpected_error THEN
4406 x_return_status := fnd_api.g_ret_sts_unexp_error;
4407 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4408 WHEN OTHERS THEN
4409 x_return_status := fnd_api.g_ret_sts_unexp_error;
4410 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4411 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4412 END IF;
4413 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4414 END create_shift_tasks1;
4415
4416 PROCEDURE update_dc_trip(
4417 p_api_version IN NUMBER
4418 , p_init_msg_list IN VARCHAR2
4419 , p_commit IN VARCHAR2
4420 , x_return_status OUT NOCOPY VARCHAR2
4421 , x_msg_data OUT NOCOPY VARCHAR2
4422 , x_msg_count OUT NOCOPY NUMBER
4423 , p_trip_id IN NUMBER
4424 , p_object_version_number IN NUMBER
4425 , p_available_hours IN NUMBER
4426 , p_upd_available_hours IN NUMBER
4427 , p_available_hours_before IN NUMBER
4428 , p_available_hours_after IN NUMBER
4429 , p_status IN NUMBER
4430 , p_availability_type in varchar2 default null
4431 , p_start_date_time in date
4432 , p_end_date_time in date
4433 ) IS
4434 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP';
4435 l_api_version CONSTANT NUMBER := 1.0;
4436 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
4437
4438 l_trip trip_rec_type;
4439 l_overtime NUMBER;
4440
4441
4442
4443
4444 CURSOR c_trip_exist(p_resource_id number)
4445 is
4446 SELECT 'Y'
4447 FROM cac_sr_object_capacity
4448 WHERE p_start_date_time <= (end_date_time + g_overtime)
4449 AND p_end_date_time >= start_date_time
4450 AND object_id=p_resource_id
4451 AND OBJECT_CAPACITY_ID NOT IN (p_trip_id);
4452
4453 l_shift_tasks_exist VARCHAR2(1);
4454 l_trip_exist VARCHAR2(1);
4455
4456 BEGIN
4457 -- Check for API Compatibility
4458 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
4459 RAISE fnd_api.g_exc_unexpected_error;
4460 END IF;
4461
4462 -- Initialize Message Stack if required
4463 IF fnd_api.to_boolean(p_init_msg_list) THEN
4464 fnd_msg_pub.initialize;
4465 END IF;
4466
4467 -- Initialize Return Status
4468 x_return_status := fnd_api.g_ret_sts_success;
4469
4470 IF p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num THEN
4471 -- Invalid Trip ID passed.
4472 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
4473 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
4474 fnd_message.set_token('PARAM_NAME', 'P_TRIP_ID');
4475 fnd_msg_pub.ADD;
4476 RAISE fnd_api.g_exc_error;
4477 END IF;
4478
4479 IF p_object_version_number IS NULL OR p_object_version_number = fnd_api.g_miss_num THEN
4480 -- Invalid Object Version Number passed.
4481 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
4482 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
4483 fnd_message.set_token('PARAM_NAME', 'P_OBJECT_VERSION_NUMBER');
4484 fnd_msg_pub.ADD;
4485 RAISE fnd_api.g_exc_error;
4486 END IF;
4487
4488 IF p_available_hours IS NOT NULL AND p_upd_available_hours IS NOT NULL THEN
4489 -- Error out as both cant be passed.
4490 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
4491 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
4492 fnd_message.set_token('PARAM_NAME', 'P_UPD_AVAILABLE_HOURS');
4493 fnd_msg_pub.ADD;
4494 RAISE fnd_api.g_exc_error;
4495 END IF;
4496
4497
4498
4499 l_trip := get_trip(p_trip_id);
4500 IF l_trip.trip_id IS NULL THEN
4501 fnd_message.set_name('CSF', 'CSF_INVALID_TRIP_ID');
4502 fnd_message.set_token('TRIP_ID', p_trip_id);
4503 fnd_msg_pub.ADD;
4504 RAISE fnd_api.g_exc_error;
4505 END IF;
4506
4507
4508
4509 OPEN c_trip_exist(l_trip.resource_id);
4510 FETCH c_trip_exist INTO l_trip_exist;
4511 IF c_trip_exist%NOTFOUND THEN
4512 l_trip_exist := 'N';
4513 END IF;
4514 CLOSE c_trip_exist;
4515
4516 IF (l_trip_exist = 'Y') THEN
4517 IF l_debug THEN
4518 debug(' Shift Tasks exist for the Resource in the specified interval', l_api_name, fnd_log.level_error);
4519 END IF;
4520 fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_ST_EXIST');
4521 fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id,l_trip.resource_type));
4522 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
4523 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
4524 fnd_msg_pub.ADD;
4525 RAISE fnd_api.g_exc_error;
4526 END IF;
4527
4528 change_trip(
4529 x_return_status => x_return_status
4530 , x_msg_data => x_msg_data
4531 , x_msg_count => x_msg_count
4532 , p_trip => l_trip
4533 , p_object_version_number => p_object_version_number
4534 , p_available_hours => p_available_hours
4535 , p_upd_available_hours => p_upd_available_hours
4536 , p_available_hours_before => p_available_hours_before
4537 , p_available_hours_after => p_available_hours_after
4538 , p_status => p_status
4539 , p_availability_type => p_availability_type
4540 , p_start_date => p_start_date_time
4541 , p_end_date => p_end_date_time
4542 );
4543
4544 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4545 fnd_message.set_name('CSF', 'CSF_TRIP_UPDATE_FAIL_OTHER');
4546 fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id, l_trip.resource_type));
4547 fnd_message.set_token('START_TIME', format_date(l_trip.start_date_time));
4548 fnd_message.set_token('END_TIME', format_date(l_trip.end_date_time));
4549 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
4550 fnd_msg_pub.ADD;
4551 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4552 RAISE fnd_api.g_exc_unexpected_error;
4553 END IF;
4554 RAISE fnd_api.g_exc_error;
4555 END IF;
4556
4557 IF fnd_api.to_boolean(p_commit) THEN
4558 COMMIT;
4559 END IF;
4560
4561 EXCEPTION
4562 WHEN fnd_api.g_exc_error THEN
4563 x_return_status := fnd_api.g_ret_sts_error;
4564 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4565 WHEN fnd_api.g_exc_unexpected_error THEN
4566 x_return_status := fnd_api.g_ret_sts_unexp_error;
4567 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4568 WHEN OTHERS THEN
4569 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
4570 x_return_status := fnd_api.g_ret_sts_unexp_error;
4571 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4572 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4573 END IF;
4574 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4575 END;
4576
4577 PROCEDURE update_shift_tasks(
4578 p_api_version IN NUMBER
4579 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
4580 , p_commit IN VARCHAR2 DEFAULT NULL
4581 , p_object_version_number in out nocopy number
4582 , p_task_id IN NUMBER
4583 , p_Task_type_id IN NUMBER
4584 , x_return_status OUT NOCOPY VARCHAR2
4585 , x_msg_data OUT NOCOPY VARCHAR2
4586 , x_msg_count OUT NOCOPY NUMBER
4587 , p_resource_id IN NUMBER
4588 , p_resource_type IN VARCHAR2
4589 , p_start_date_time IN DATE
4590 , p_end_date_time IN DATE
4591 , p_arr_party_site IN NUMBER
4592 , p_arr_party IN NUMBER
4593 , p_dep_party_site IN NUMBER
4594 , p_dep_party IN NUMBER
4595 , p_update_dep_task IN BOOLEAN default null
4596 , p_update_arr_task IN BOOLEAN default null
4597 ) IS
4598 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SHIFT_TASKS';
4599 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
4600 l_address csf_resource_address_pvt.address_rec_type;
4601 l_task_assign_tbl jtf_tasks_pub.task_assign_tbl;
4602 BEGIN
4603
4604 IF p_update_dep_task = FALSE AND p_update_arr_task = FALSE THEN
4605 RETURN;
4606 END IF;
4607
4608
4609 -- Departure and Arrival Task Resource Assignment
4610 l_task_assign_tbl(1).resource_id := p_resource_id;
4611 l_task_assign_tbl(1).resource_type_code := p_resource_type;
4612 l_task_assign_tbl(1).assignment_status_id := g_assigned_status_id;
4613
4614 -- Create the Departure Task
4615 IF p_update_dep_task THEN
4616 jtf_tasks_pub.update_task(
4617 p_api_version => 1.0
4618 , p_task_id => p_task_id
4619 , p_object_version_number => p_object_version_number
4620 , p_task_type_id => p_task_type_id
4621 , p_task_status_id => g_assigned_status_id
4622 , p_owner_id => p_resource_id
4623 , p_owner_type_code => p_resource_type
4624 , p_address_id => p_dep_party_site
4625 , p_customer_id => p_dep_party
4626 , p_planned_start_date => p_start_date_time
4627 , p_planned_end_date => p_start_date_time
4628 , p_scheduled_start_date => p_start_date_time
4629 , p_scheduled_end_date => p_start_date_time
4630 , p_duration => 0
4631 , p_duration_uom => g_duration_uom
4632 , p_bound_mode_code => 'BTS'
4633 , p_soft_bound_flag => 'Y'
4634 , x_return_status => x_return_status
4635 , x_msg_count => x_msg_count
4636 , x_msg_data => x_msg_data
4637 );
4638
4639 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4640
4641
4642 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4643 RAISE fnd_api.g_exc_unexpected_error;
4644 END IF;
4645 RAISE fnd_api.g_exc_error;
4646 END IF;
4647
4648
4649 END IF;
4650
4651 -- Create the Arrival Task
4652 IF p_update_arr_task THEN
4653 jtf_tasks_pub.update_task(
4654 p_api_version => 1.0
4655 , p_task_id => p_task_id
4656 , p_object_version_number => p_object_version_number
4657 , p_task_type_id => p_task_type_id
4658 , p_task_status_id => g_assigned_status_id
4659 , p_owner_id => p_resource_id
4660 , p_owner_type_code => p_resource_type
4661 , p_address_id => p_arr_party_site
4662 , p_customer_id => p_arr_party
4663 , p_planned_start_date => p_end_date_time
4664 , p_planned_end_date => p_end_date_time
4665 , p_scheduled_start_date => p_end_date_time
4666 , p_scheduled_end_date => p_end_date_time
4667 , p_duration => 0
4668 , p_duration_uom => g_duration_uom
4669 , p_bound_mode_code => 'BTS'
4670 , p_soft_bound_flag => 'Y'
4671 , x_return_status => x_return_status
4672 , x_msg_count => x_msg_count
4673 , x_msg_data => x_msg_data
4674 );
4675
4676 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4677
4678
4679 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4680 RAISE fnd_api.g_exc_unexpected_error;
4681 END IF;
4682 RAISE fnd_api.g_exc_error;
4683 END IF;
4684
4685 END IF;
4686 IF fnd_api.to_boolean(p_commit) THEN
4687 COMMIT;
4688 END IF;
4689
4690
4691 EXCEPTION
4692 WHEN fnd_api.g_exc_error THEN
4693 x_return_status := fnd_api.g_ret_sts_error;
4694 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4695 WHEN fnd_api.g_exc_unexpected_error THEN
4696 x_return_status := fnd_api.g_ret_sts_unexp_error;
4697 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4698 WHEN OTHERS THEN
4699 x_return_status := fnd_api.g_ret_sts_unexp_error;
4700 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4701 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4702 END IF;
4703 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4704 END update_shift_tasks;
4705
4706 PROCEDURE create_dc_trip( p_api_version IN NUMBER
4707 , p_init_msg_list IN VARCHAR2
4708 , p_commit IN VARCHAR2
4709 , x_return_status OUT NOCOPY VARCHAR2
4710 , x_msg_data OUT NOCOPY VARCHAR2
4711 , x_msg_count OUT NOCOPY NUMBER
4712 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
4713 , p_start_date IN DATE
4714 , p_end_date IN DATE
4715 , p_delete_trips IN BOOLEAN DEFAULT FALSE)
4716 IS
4717 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DC_TRIPS';
4718 l_api_version CONSTANT NUMBER := 1.0;
4719 BEGIN
4720 SAVEPOINT csf_dc_trip;
4721
4722 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
4723 RAISE fnd_api.g_exc_unexpected_error;
4724 END IF;
4725
4726 -- Initialize Message Stack if required
4727 IF fnd_api.to_boolean(p_init_msg_list) THEN
4728 fnd_msg_pub.initialize;
4729 END IF;
4730
4731 -- Initialize Return Status
4732 x_return_status := fnd_api.g_ret_sts_success;
4733
4734 create_trips(
4735 x_return_status => x_return_status
4736 , x_msg_data => x_msg_data
4737 , x_msg_count => x_msg_count
4738 , p_resource_tbl => p_resource_tbl
4739 , p_start_date => p_start_date
4740 , p_end_date => p_end_date
4741 , p_delete_trips => null
4742 );
4743
4744 check_multiple_trip_tasks(p_resource_tbl => p_resource_tbl
4745 , p_start => p_start_date
4746 , p_end => p_end_date
4747 , x_return_status => x_return_status
4748 , x_msg_data => x_msg_data
4749 , x_msg_count => x_msg_count);
4750
4751 --IF x_return_status <> fnd_api.g_ret_sts_success THEN
4752 --IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4753 --RAISE fnd_api.g_exc_unexpected_error;
4754 --END IF;
4755 --RAISE fnd_api.g_exc_error;
4756 --END IF;
4757
4758 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4759 fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_OTHER');
4760 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_tbl(1).resource_id, p_resource_tbl(1).resource_type));
4761 fnd_message.set_token('START_TIME', format_date(p_start_date));
4762 fnd_message.set_token('END_TIME', format_date(p_end_date));
4763 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
4764 fnd_msg_pub.ADD;
4765 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
4766 RAISE fnd_api.g_exc_unexpected_error;
4767 END IF;
4768 RAISE fnd_api.g_exc_error;
4769 END IF;
4770
4771 IF fnd_api.to_boolean(p_commit) THEN
4772 COMMIT;
4773 END IF;
4774 EXCEPTION
4775 WHEN fnd_api.g_exc_error THEN
4776 ROLLBACK TO csf_dc_trip;
4777 x_return_status := fnd_api.g_ret_sts_error;
4778 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4779 WHEN fnd_api.g_exc_unexpected_error THEN
4780 ROLLBACK TO csf_dc_trip;
4781 x_return_status := fnd_api.g_ret_sts_unexp_error;
4782 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4783 WHEN OTHERS THEN
4784 ROLLBACK TO csf_dc_trip;
4785 x_return_status := fnd_api.g_ret_sts_unexp_error;
4786 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4787 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
4788 END IF;
4789 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
4790 END create_dc_trip;
4791
4792
4793 FUNCTION check_dst(p_resource_id IN number ,p_start_server IN date,p_end_server IN date)
4794 RETURN VARCHAR2
4795 IS
4796 l_api_name CONSTANT VARCHAR2(30) := 'check_dst';
4797 l_API_VERSION Number := 1.0 ;
4798 p_API_VERSION Number := 1.0 ;
4799 l_INIT_MSG_LIST varchar2(1) := 'F';
4800 p_INIT_MSG_LIST varchar2(1) := 'F';
4801 l_res_Timezone_id number;
4802
4803 l_start_res date;
4804 l_end_res date;
4805 l_server_diff number;
4806 l_res_diff number;
4807 l_main_diff number;
4808 l_tz_enabled VARCHAR2(10):=fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS');
4809 l_server_tz_id Number := to_number (fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
4810
4811 X_RETURN_STATUS Varchar2(10);
4812 x_msg_count number;
4813 x_msg_data Varchar2(2000);
4814 BEGIN
4815
4816
4817 -- Standard call to check for call compatibility.
4818 IF NOT FND_API.Compatible_API_Call (l_api_version ,
4819 p_api_version ,
4820 l_api_name ,
4821 G_PKG_NAME )
4822 THEN
4823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4824 END IF;
4825
4826 -- Initialize message list if p_init_msg_list is set to TRUE.
4827 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4828 FND_MSG_PUB.initialize;
4829 END IF;
4830
4831 -- Initialize API return status to success
4832 x_return_status := FND_API.G_RET_STS_SUCCESS;
4833 -- Get the dates in Server Timezone
4834 -- Calculate diff ,if greater than 24 convert back to Resoruce timezone.
4835 -- check the diff in Resource timezone
4836 -- if >24 error out else Convert back to Server timezone again and
4837 -- check if >24 .If yes its DST change so allow the change otherwise
4838 -- error out
4839
4840 l_server_diff := trunc ((p_end_server - p_start_server ) * g_hours_in_day,2);
4841 IF l_server_diff > 24
4842 THEN
4843 If fnd_profile.value_specific('ENABLE_TIMEZONE_CONVERSIONS') = 'Y'
4844 Then
4845 l_res_Timezone_id := Get_Res_Timezone_Id (p_resource_id);
4846 l_start_res := ServerDT_To_ResourceDt(p_start_server,l_server_tz_id,l_res_Timezone_id);
4847 l_end_res := ServerDT_To_ResourceDt(p_end_server,l_server_tz_id,l_res_Timezone_id);
4848
4849 l_res_diff := trunc((l_end_res - l_start_res ) * g_hours_in_day,2);
4850 l_main_diff := (l_server_diff - l_res_diff);
4851 if l_res_diff < 24 and l_main_diff = 1
4852 then
4853 return 'TRUE';
4854 else
4855 return 'FALSE';
4856 end if;
4857 else
4858 return 'FALSE';
4859 end if;
4860 END IF;
4861 EXCEPTION
4862 when others then
4863
4864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4865
4866 FND_MSG_PUB.Count_And_Get
4867 ( p_count => x_msg_count,
4868 p_data => x_msg_data
4869 );
4870 RETURN 'FALSE';
4871 END;
4872
4873
4874 Function ServerDT_To_ResourceDt ( P_Server_DtTime IN date, P_Server_TZ_Id IN Number , p_Resource_TZ_id IN Number ) RETURN date IS
4875
4876 x_Server_time Date := P_Server_DtTime;
4877
4878 l_api_name CONSTANT VARCHAR2(30) := 'ServerDT_To_ResourceDt';
4879 l_API_VERSION Number := 1.0 ;
4880 p_API_VERSION Number := 1.0 ;
4881 l_INIT_MSG_LIST varchar2(1) := 'F';
4882 p_INIT_MSG_LIST varchar2(1) := 'F';
4883 X_msg_count Number;
4884 X_msg_data Varchar2(2000);
4885 X_RETURN_STATUS Varchar2(10);
4886
4887 BEGIN
4888
4889 -- Standard call to check for call compatibility.
4890 IF NOT FND_API.Compatible_API_Call (l_api_version ,
4891 p_api_version ,
4892 l_api_name ,
4893 G_PKG_NAME )
4894 THEN
4895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4896 END IF;
4897
4898 -- Initialize message list if p_init_msg_list is set to TRUE.
4899 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4900 FND_MSG_PUB.initialize;
4901 END IF;
4902
4903 -- Initialize API return status to success
4904 x_return_status := FND_API.G_RET_STS_SUCCESS;
4905
4906 HZ_TIMEZONE_PUB.Get_Time( l_API_VERSION
4907 , l_INIT_MSG_LIST
4908 , P_Server_TZ_Id
4909 , p_Resource_TZ_id
4910 , P_Server_DtTime
4911 , x_Server_time
4912 , X_RETURN_STATUS
4913 , X_msg_count
4914 , X_msg_data);
4915
4916 Return x_Server_time;
4917
4918 EXCEPTION
4919 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4921 FND_MSG_PUB.Count_And_Get
4922 ( p_count => x_msg_count,
4923 p_data => x_msg_data
4924 );
4925
4926 WHEN OTHERS THEN
4927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4928
4929 FND_MSG_PUB.Count_And_Get
4930 ( p_count => x_msg_count,
4931 p_data => x_msg_data
4932 );
4933
4934 END ServerDT_To_ResourceDT;
4935
4936 Function Get_Res_Timezone_Id ( P_Resource_Id IN Number ) RETURN Number IS
4937
4938 Cursor C_Res_TimeZone Is
4939 Select TIME_ZONE
4940 From JTF_RS_RESOURCE_EXTNS
4941 Where RESOURCE_ID = p_resource_id
4942 And trunc(sysdate) between trunc(nvl(START_DATE_ACTIVE,sysdate))
4943 and trunc(nvl(END_DATE_ACTIVE,sysdate));
4944
4945 l_Res_Timezone_id Number;
4946
4947 Begin
4948
4949 Open C_Res_TimeZone ;
4950 Fetch C_Res_TimeZone into l_Res_TimeZone_id;
4951 Close C_Res_TimeZone ;
4952
4953 l_Res_TimeZone_id := nvl(l_Res_TimeZone_id,fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
4954
4955 Return (l_Res_TimeZone_id);
4956
4957 End Get_Res_Timezone_Id;
4958
4959 PROCEDURE check_dangling_tasks(p_resource_tbl IN csf_resource_pub.resource_tbl_type
4960 , p_start IN DATE
4961 , p_end IN DATE
4962 , x_return_status OUT NOCOPY VARCHAR2
4963 , x_msg_data OUT NOCOPY VARCHAR2
4964 , x_msg_count OUT NOCOPY NUMBER)
4965 IS
4966
4967 CURSOR c_dangling_tasks(p_res_id number,p_res_type varchar2)
4968 IS
4969 SELECT t.task_id,t.object_version_number,scheduled_start_date,scheduled_end_date
4970 FROM jtf_tasks_b t
4971 , jtf_task_assignments ta
4972 WHERE t.owner_id = p_res_id
4973 AND t.owner_type_code = p_res_type
4974 AND t.planned_start_date BETWEEN p_start AND p_end
4975 AND t.task_type_id IN (20, 21)
4976 AND NVL(t.deleted_flag, 'N') <> 'Y'
4977 AND ta.task_id = t.task_id
4978 AND ta.assignee_role = 'ASSIGNEE'
4979 AND ta.object_capacity_id IS NULL
4980 UNION
4981 SELECT t.task_id,t.object_version_number,scheduled_start_date,scheduled_end_date
4982 FROM jtf_tasks_b t
4983 WHERE t.owner_id = p_res_id
4984 AND t.owner_type_code = p_res_type
4985 AND t.task_type_id IN (20, 21)
4986 AND NVL(t.deleted_flag, 'N') <> 'Y'
4987 AND( t.planned_start_date BETWEEN p_start AND p_end )
4988 AND TASK_ID NOT IN (SELECT ta.TASK_ID FROM JTF_TASK_ASSIGNMENTS ta WHERE ta.task_id=t.task_id and RESOURCE_ID = p_res_id
4989 AND RESOURCE_TYPE_CODE = p_res_type);
4990
4991 CURSOR c_alone_trip_tasks(p_res_id number,p_res_type varchar2)
4992 IS
4993 SELECT task_number,
4994 scheduled_start_date,
4995 scheduled_end_date,
4996 owner_id,
4997 owner_type_code
4998 FROM JTF_TASKS_B
4999 WHERE TASK_ID IN
5000 (SELECT TASK_ID
5001 FROM JTF_TASK_ASSIGNMENTS
5002 WHERE OBJECT_CAPACITY_ID IN
5003 (SELECT co.object_capacity_id
5004 FROM cac_sr_object_capacity co,
5005 jtf_tasks_b jtb,
5006 jtf_task_assignments jta,
5007 jtf_task_statuses_b jts,
5008 jtf_Task_statuses_b jtsa
5009 WHERE jtb.task_id =jta.task_id
5010 AND jta.assignment_status_id =jts.task_status_id
5011 AND NVL(jts.cancelled_flag,'N') <> 'Y'
5012 AND jtb.task_status_id =jtsa.task_status_id
5013 AND NVL(jtsa.cancelled_flag,'N') <> 'Y'
5014 AND NVL(jtb.deleted_flag,'N') <> 'Y'
5015 AND co.object_capacity_id =jta.object_capacity_id
5016 AND resource_id =object_id
5017 AND resource_type_code =object_type
5018 AND jtb.task_type_id IN (20,21)
5019 AND resource_id =p_res_id
5020 AND resource_type_code =p_res_type
5021 AND( co.start_date_time BETWEEN p_start AND p_end )
5022 GROUP BY co.object_capacity_id
5023 HAVING COUNT(jta.task_id) =1
5024 )
5025 );
5026 l_tasks c_dangling_tasks%rowtype;
5027 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_DANGLING_TASKS';
5028 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
5029
5030 BEGIN
5031
5032 IF l_debug THEN
5033 debug(' Inside Dangling Procedure # ', l_api_name, fnd_log.level_statement);
5034 debug(' Checking Dangling Trip between ' || format_date(p_start) || ' and ' || format_date(p_end), l_api_name, fnd_log.level_statement);
5035
5036 END IF;
5037 FOR i in p_resource_tbl.first .. p_resource_tbl.last
5038 LOOP
5039 FOR l_tasks IN c_dangling_tasks(p_resource_tbl(i).resource_id,p_resource_tbl(i).resource_type)
5040 LOOP
5041
5042 IF l_debug THEN
5043 debug(' Deleting the Dangling Shift Task #' || l_tasks.task_id, l_api_name, fnd_log.level_statement);
5044 END IF;
5045 -- Departure Task already exists... Delete this Duplicate.
5046 jtf_tasks_pub.delete_task(
5047 p_api_version => 1.0
5048 , x_return_status => x_return_status
5049 , x_msg_count => x_msg_count
5050 , x_msg_data => x_msg_data
5051 , p_task_id => l_tasks.task_id
5052 , p_object_version_number => l_tasks.object_version_number
5053 );
5054
5055 IF x_return_status <> fnd_api.g_ret_sts_success THEN
5056 IF l_debug THEN
5057 debug(' Unable to Delete the dangling shift Task', l_api_name, fnd_log.level_error);
5058 END IF;
5059
5060 add_message( p_resource_tbl(i).resource_id
5061 , p_resource_tbl(i).resource_type
5062 , l_tasks.scheduled_start_date
5063 , l_tasks.scheduled_end_date
5064 , fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
5065 , 'CSF_TASK_DELETE_FAIL'
5066 , g_error_message );
5067
5068 END IF;
5069 END LOOP;
5070 FOR l_alone_trip_task in c_alone_trip_tasks(p_resource_tbl(i).resource_id,p_resource_tbl(i).resource_type)
5071 LOOP
5072 IF l_debug THEN
5073 debug(' Details of Trip which have only one Departure/Arrival Task for given below period # ', l_api_name, fnd_log.level_statement);
5074 debug(' From Date # ' || format_date(p_start) || ' TO Date # ' || format_date(p_end), l_api_name, fnd_log.level_statement);
5075 debug(' Task Number # ' ||l_alone_trip_task.task_number||' Scheduled Start Date #'||format_date(l_alone_trip_task.scheduled_start_date) ||
5076 ' Scheduled End Date #'||format_date(l_alone_trip_task.scheduled_end_date) ||' Owner Id #'||l_alone_trip_task.owner_id||
5077 ' Owner Type #'||l_alone_trip_task.owner_type_code, l_api_name, fnd_log.level_statement);
5078 END IF;
5079 END LOOP;
5080 END LOOP;
5081
5082
5083 IF l_debug THEN
5084 debug(' OutSide Dangling Procedure # ', l_api_name, fnd_log.level_statement);
5085 END IF;
5086
5087 END check_dangling_tasks;
5088
5089 PROCEDURE check_duplicate_tasks(p_resource_tbl IN csf_resource_pub.resource_tbl_type
5090 , p_start IN DATE
5091 , p_end IN DATE
5092 , x_return_status OUT NOCOPY VARCHAR2
5093 , x_msg_data OUT NOCOPY VARCHAR2
5094 , x_msg_count OUT NOCOPY NUMBER)
5095 IS
5096
5097 CURSOR c_duplicate_tasks(p_res_id number,p_res_type varchar2)
5098 IS
5099 SELECT task_id
5100 , task_type_id
5101 , object_version_number
5102 , task_name
5103 , task_number FROM (
5104 SELECT t.task_id
5105 , t.task_type_id
5106 , t.object_version_number
5107 , t.task_name
5108 , t.task_number
5109 , LAG(t.task_id) OVER (PARTITION BY t.task_type_id,resource_id,resource_type_code,scheduled_start_date
5110 ORDER BY t.scheduled_start_date) duplicate
5111 FROM jtf_task_assignments ta
5112 , jtf_tasks_vl t
5113 WHERE t.task_id = ta.task_id
5114 AND NVL(t.deleted_flag, 'N') = 'N'
5115 AND t.task_type_id IN (20, 21)
5116 AND resource_id =p_res_id
5117 AND resource_type_code=p_res_type
5118 AND booking_start_date <= p_end + 1
5119 AND booking_end_date >= p_start - 1
5120 ) WHERE duplicate IS NOT NULL;
5121
5122 l_tasks c_duplicate_tasks%rowtype;
5123 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_DUPLICATE_TASKS';
5124 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
5125
5126 BEGIN
5127
5128 IF l_debug THEN
5129 debug(' Inside Duplicate Procedure # ', l_api_name, fnd_log.level_statement);
5130 END IF;
5131 FOR i in p_resource_tbl.first .. p_resource_tbl.last
5132 LOOP
5133
5134 FOR l_tasks IN c_duplicate_tasks(p_resource_tbl(i).resource_id,p_resource_tbl(i).resource_type)
5135 LOOP
5136 IF l_debug THEN
5137 debug(' Deleting the Duplicate Shift Task #' || l_tasks.task_id, l_api_name, fnd_log.level_statement);
5138 END IF;
5139 -- Departure Task already exists... Delete this Duplicate.
5140 jtf_tasks_pub.delete_task(
5141 p_api_version => 1.0
5142 , x_return_status => x_return_status
5143 , x_msg_count => x_msg_count
5144 , x_msg_data => x_msg_data
5145 , p_task_id => l_tasks.task_id
5146 , p_object_version_number => l_tasks.object_version_number
5147 );
5148
5149 IF x_return_status <> fnd_api.g_ret_sts_success THEN
5150 IF l_debug THEN
5151 debug(' Unable to Delete the Duplicate shift Task', l_api_name, fnd_log.level_error);
5152 END IF;
5153
5154 add_message( p_resource_tbl(i).resource_id
5155 , p_resource_tbl(i).resource_type
5156 , null
5157 , null
5158 , fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
5159 , 'CSF_TASK_DELETE_FAIL'
5160 , g_error_message );
5161
5162
5163 END IF;
5164 END LOOP;
5165 END LOOP;
5166 IF l_debug THEN
5167 debug(' OutSide Duplicate Procedure # ', l_api_name, fnd_log.level_statement);
5168 END IF;
5169
5170 END check_duplicate_tasks;
5171
5172 PROCEDURE check_multiple_trip_tasks(p_resource_tbl IN csf_resource_pub.resource_tbl_type
5173 , p_start IN DATE
5174 , p_end IN DATE
5175 , x_return_status OUT NOCOPY VARCHAR2
5176 , x_msg_data OUT NOCOPY VARCHAR2
5177 , x_msg_count OUT NOCOPY NUMBER)
5178 IS
5179
5180 CURSOR c_linkable_tasks(p_resource_id number,p_resource_type varchar2) IS
5181 SELECT ta.task_assignment_id
5182 , ta.object_version_number
5183 , ta.task_id
5184 , ta.booking_start_date
5185 , ta.booking_end_date
5186 , ta.object_capacity_id
5187 , csf_util_pvt.convert_to_minutes(
5188 ta.sched_travel_duration
5189 , ta.sched_travel_duration_uom
5190 ) travel_time
5191 FROM jtf_task_assignments ta
5192 , jtf_task_statuses_b ts
5193 , jtf_tasks_b t
5194 WHERE ta.resource_id = p_resource_id
5195 AND ta.resource_type_code = p_resource_type
5196 AND ta.assignee_role = 'ASSIGNEE'
5197 AND ts.task_status_id = ta.assignment_status_id
5198 AND NVL(ts.closed_flag, 'N') = 'N'
5199 AND NVL(ts.completed_flag, 'N') = 'N'
5200 AND NVL(ts.cancelled_flag, 'N') = 'N'
5201 AND t.task_id = ta.task_id
5202 AND NVL(t.deleted_flag, 'N') <> 'Y'
5203 AND ta.booking_start_date <= (p_end + g_overtime)
5204 AND ta.booking_end_date >= p_start
5205 AND (t.task_type_id NOT IN (20, 21));
5206
5207 CURSOR c_trip_count(p_start_date date,p_end_date date,p_res_id number,p_res_type varchar2) IS
5208 select count(distinct object_capacity_id)
5209 FROM cac_sr_object_capacity co
5210 WHERE co.start_date_time <= p_end_date
5211 and co.end_date_time >= p_start_date
5212 and object_id= p_res_id
5213 and object_type= p_res_type;
5214
5215 CURSOR c_trip_info(p_trip_id NUMBER) IS
5216 SELECT oc.object_version_number
5217 , oc.available_hours
5218 FROM cac_sr_object_capacity oc
5219 WHERE oc.object_capacity_id = p_trip_id;
5220
5221 l_tasks c_linkable_tasks%rowtype;
5222 l_trip_count number;
5223 l_trip_info c_trip_info%ROWTYPE;
5224 l_available_hours number;
5225 l_time_occupied NUMBER;
5226 l_api_name CONSTANT VARCHAR2(40) := 'CHECK_MULTIPLE_TRIP_TASKS';
5227 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
5228
5229 BEGIN
5230 IF l_debug THEN
5231 debug(' Inside Procedure check_multiple_trip_tasks # ', l_api_name, fnd_log.level_statement);
5232 debug(' Checking Trips for Tasks between ' || format_date(p_start) || ' and ' || format_date(p_end), l_api_name, fnd_log.level_statement);
5233 END IF;
5234
5235 FOR i in p_resource_tbl.first .. p_resource_tbl.last
5236 LOOP
5237 FOR l_tasks IN c_linkable_tasks(p_resource_tbl(i).resource_id,p_resource_tbl(i).resource_type)
5238 LOOP
5239 OPEN c_trip_count(l_tasks.booking_start_date,l_tasks.booking_end_date,p_resource_tbl(i).resource_id,p_resource_tbl(i).resource_type);
5240 FETCH c_trip_count INTO l_trip_count;
5241 CLOSE c_trip_count;
5242
5243 IF l_debug THEN
5244 debug(' Resource Id # ' || p_resource_tbl(i).resource_id || ' . ' || ' The Trip Count for Task Id # ' || l_tasks.task_id || ' is # ' || l_trip_count, l_api_name, fnd_log.level_statement);
5245 END IF;
5246
5247 IF l_trip_count > 1 THEN
5248 IF l_debug THEN
5249 debug('Updating Task Assignment for Task Id # ' || l_tasks.task_id || ' booking_start_date # ' || format_date(l_tasks.booking_start_date) || ' booking_end_date # ' || format_date(l_tasks.booking_end_date),l_api_name, fnd_log.level_statement);
5250 END IF;
5251
5252 IF l_tasks.object_capacity_id IS NOT NULL THEN
5253 OPEN c_trip_info(l_tasks.object_capacity_id);
5254 FETCH c_trip_info INTO l_trip_info;
5255 CLOSE c_trip_info;
5256
5257 JTF_TASK_ASSIGNMENTS_PUB.UPDATE_TASK_ASSIGNMENT(
5258 p_api_version => 1.0,
5259 p_commit => fnd_api.G_FALSE,
5260 p_object_version_number => l_tasks.OBJECT_VERSION_NUMBER,
5261 p_task_assignment_id => l_tasks.TASK_ASSIGNMENT_ID,
5262 p_enable_workflow => NULL,
5263 p_abort_workflow => NULL,
5264 p_object_capacity_id => NULL,
5265 x_return_status => x_return_status,
5266 x_msg_count => x_msg_count,
5267 x_msg_data => x_msg_data
5268 );
5269
5270 IF x_return_status <> fnd_api.g_ret_sts_success THEN
5271 IF l_debug THEN
5272 debug('Unable to Update the Object Capacity Id for Task ' || l_tasks.task_id, l_api_name, fnd_log.level_error);
5273 END IF;
5274 END IF;
5275 END IF;
5276 ELSIF l_trip_count = 1 THEN
5277 IF l_debug THEN
5278 debug(' Updating the Available Hours for Object Capacity Id # ' || l_tasks.object_capacity_id || ' . ' || ' Task Id # ' || l_tasks.task_id, l_api_name, fnd_log.level_statement);
5279 END IF;
5280
5281 OPEN c_trip_info(l_tasks.object_capacity_id);
5282 FETCH c_trip_info INTO l_trip_info;
5283 CLOSE c_trip_info;
5284
5285 l_available_hours := l_trip_info.available_hours;
5286 l_time_occupied := l_tasks.booking_end_date - l_tasks.booking_start_date; -- Scheduled Task Duration
5287 l_time_occupied := l_time_occupied + NVL(l_tasks.travel_time, 0) / g_mins_in_day; -- Scheduled Travel Duration
5288 l_available_hours := l_available_hours - l_time_occupied * g_hours_in_day;
5289
5290 cac_sr_object_capacity_pub.update_object_capacity(
5291 p_api_version => 1.0
5292 , x_return_status => x_return_status
5293 , x_msg_count => x_msg_count
5294 , x_msg_data => x_msg_data
5295 , p_object_capacity_id => l_tasks.object_capacity_id
5296 , p_object_version_number => l_trip_info.object_version_number
5297 , p_available_hours => l_available_hours
5298 );
5299
5300 IF x_return_status <> fnd_api.g_ret_sts_success THEN
5301 IF l_debug THEN
5302 debug('Unable to update the available hours for Object Capacity Id ' || l_tasks.task_id, l_api_name, fnd_log.level_error);
5303 END IF;
5304 END IF;
5305 END IF;
5306 END LOOP;
5307
5308 END LOOP;
5309
5310 IF l_debug THEN
5311 debug(' OutSide Procedure check_multiple_trip_tasks # ', l_api_name, fnd_log.level_statement);
5312 END IF;
5313
5314 END check_multiple_trip_tasks;
5315
5316
5317 BEGIN
5318 -- Package Initialization
5319 init_package;
5320 END csf_trips_pub;
5321