[Home] [Help]
PACKAGE BODY: APPS.CSF_TRIPS_PUB
Source
1 PACKAGE BODY csf_trips_pub AS
2 /* $Header: CSFPTRPB.pls 120.19.12010000.2 2008/10/06 11:50:00 venjayar 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
31 TYPE message_rec_type IS RECORD(
32 message_name fnd_new_messages.message_name%TYPE
33 , message_type VARCHAR2(1)
34 , resource_id NUMBER
35 , resource_type jtf_objects_b.object_code%TYPE
36 , start_datetime DATE
37 , end_datetime DATE
38 , trip_id NUMBER
39 , error_reason fnd_new_messages.message_text%TYPE
40 );
41
42 TYPE message_tbl_type IS TABLE OF message_rec_type
43 INDEX BY BINARY_INTEGER;
44
45 TYPE number_tbl_type IS TABLE OF NUMBER
46 INDEX BY BINARY_INTEGER;
47
48 g_error_message CONSTANT VARCHAR2(1) := 'E';
49 g_warning_message CONSTANT VARCHAR2(1) := 'W';
50 g_success_message CONSTANT VARCHAR2(1) := 'S';
51
52 g_messages message_tbl_type;
53 g_suppress_res_info BOOLEAN;
54
55
56 /******************************************************************************************
57 * *
58 * Private Utility Functions and Procedures *
59 * *
60 *******************************************************************************************/
61
62 PROCEDURE debug(p_message VARCHAR2, p_module VARCHAR2, p_level NUMBER) IS
63 BEGIN
64 IF p_level = g_level_cp_output AND fnd_file.output > 0 THEN
65 fnd_file.put_line(fnd_file.output, p_message);
66 END IF;
67
68 IF g_debug = 'Y' AND p_level >= g_debug_level THEN
69 IF fnd_file.log > 0 THEN
70 IF p_message = ' ' THEN
71 fnd_file.put_line(fnd_file.log, '');
72 ELSE
73 fnd_file.put_line(fnd_file.log, rpad(p_module, 20) || ': ' || p_message);
74 END IF;
75 ELSE
76 fnd_log.string(p_level, 'csf.plsql.CSF_TRIPS_PUB.' || p_module, p_message);
77 END IF;
78 END IF;
79 --dbms_output.put_line(rpad(p_module, 20) || ': ' || p_message);
80 END debug;
81
82 FUNCTION format_date(p_date IN DATE, p_convert_to_client_tz VARCHAR2 DEFAULT NULL)
83 RETURN VARCHAR2 IS
84 l_date DATE;
85 BEGIN
86 l_date := p_date;
87 IF p_convert_to_client_tz IS NULL OR p_convert_to_client_tz = fnd_api.g_true THEN
88 -- AOL doesnt initialize FND_DATE package properly. Refer bugs 3183418 and 3115188.
89 -- Because of this, dates werent printed with TZ Conversion. Bypassing FND_DATE.
90 IF g_tz_enabled = 'Y' THEN
91 l_date := fnd_timezones_pvt.adjust_datetime(
92 date_time => p_date
93 , from_tz => g_server_tz_code
94 , to_tz => g_client_tz_code
95 );
96 END IF;
97 END IF;
98 RETURN to_char(l_date, g_datetime_fmt_mask);
99 END format_date;
100
101 FUNCTION get_resource_info(p_resource_id NUMBER, p_resource_type VARCHAR2)
102 RETURN VARCHAR2 IS
103 l_resource_info csf_resource_pub.resource_rec_type;
104 BEGIN
105 l_resource_info := csf_resource_pub.get_resource_info(p_resource_id, p_resource_type);
106
107 RETURN l_resource_info.resource_name
108 || ' ('
109 || csf_resource_pub.get_resource_type_name(l_resource_info.resource_type)
110 || ', '
111 || l_resource_info.resource_number
112 || ')';
113 END get_resource_info;
114
115 FUNCTION time_overlaps(p_trip trip_rec_type, p_shift csf_resource_pub.shift_rec_type)
116 RETURN BOOLEAN IS
117 BEGIN
118 RETURN p_trip.start_date_time < (p_shift.end_datetime + g_overtime)
119 AND (p_trip.end_date_time + g_overtime) > p_shift.start_datetime;
120 END time_overlaps;
121
122 FUNCTION time_overlaps(p_trip trip_rec_type, p_start DATE, p_end DATE)
123 RETURN BOOLEAN IS
124 BEGIN
125 RETURN p_trip.start_date_time < p_end
126 AND (p_trip.end_date_time + g_overtime) > p_start;
127 END time_overlaps;
128
129 PROCEDURE add_message(
130 p_trip trip_rec_type
131 , p_reason VARCHAR2 DEFAULT NULL
132 , p_msg_name VARCHAR2 DEFAULT NULL
133 , p_msg_type VARCHAR2 DEFAULT NULL
134 ) IS
135 i PLS_INTEGER;
136 BEGIN
137 i := g_messages.COUNT + 1;
138 g_messages(i).message_name := p_msg_name;
139 g_messages(i).message_type := NVL(p_msg_type, g_success_message);
140 g_messages(i).error_reason := p_reason;
141 g_messages(i).resource_id := p_trip.resource_id;
142 g_messages(i).resource_type := p_trip.resource_type;
143 g_messages(i).start_datetime := p_trip.start_date_time;
144 g_messages(i).end_datetime := p_trip.end_date_time;
145 g_messages(i).trip_id := p_trip.trip_id;
146 END add_message;
147
148 PROCEDURE add_message(
149 p_res_id NUMBER
150 , p_res_type VARCHAR2
151 , p_start DATE
152 , p_end DATE
153 , p_reason VARCHAR2 DEFAULT NULL
154 , p_msg_name VARCHAR2 DEFAULT NULL
155 , p_msg_type VARCHAR2 DEFAULT NULL
156 ) IS
157 i PLS_INTEGER;
158 BEGIN
159 i := g_messages.COUNT + 1;
160 g_messages(i).resource_id := p_res_id;
161 g_messages(i).resource_type := p_res_type;
162 g_messages(i).start_datetime := p_start;
163 g_messages(i).end_datetime := p_end;
164 g_messages(i).message_name := p_msg_name;
165 g_messages(i).message_type := NVL(p_msg_type, g_success_message);
166 g_messages(i).error_reason := p_reason;
167 END add_message;
168
169 PROCEDURE process_messages(
170 p_init_msg_list IN VARCHAR2
171 , x_return_status OUT NOCOPY VARCHAR2
172 , p_action IN VARCHAR2
173 , p_trip_id IN NUMBER
174 , p_start_date IN DATE
175 , p_end_date IN DATE
176 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
177 ) IS
178 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
179 l_success NUMBER;
180 l_failed NUMBER;
181 l_action_name fnd_flex_values_tl.flex_value_meaning%TYPE;
182 l_res_name jtf_rs_resource_extns_tl.resource_name%TYPE;
183
184 CURSOR c_action_name IS
185 SELECT v.flex_value_meaning meaning
186 FROM fnd_flex_value_sets s, fnd_flex_values_vl v
187 WHERE s.flex_value_set_name = 'CSF_GTR_ACTIONS'
188 AND s.flex_value_set_id = v.flex_value_set_id
189 AND v.flex_value = p_action;
190 BEGIN
191
192 x_return_status := fnd_api.g_ret_sts_success;
193
194 -- First Clear the Message Stack if the API is given the permission to clear stack.
195 IF fnd_api.to_boolean(p_init_msg_list) THEN
196 fnd_msg_pub.initialize;
197 END IF;
198
199 l_success := 0;
200 l_failed := 0;
201
202 FOR i IN 1..g_messages.COUNT LOOP
203 IF g_messages(i).message_type IN (g_error_message, g_warning_message) THEN
204 fnd_message.set_name('CSF', NVL(g_messages(i).message_name, 'CSF_PROCESS_TRIP_FAILED'));
205
206 IF g_messages(i).resource_id IS NOT NULL THEN
207 IF g_suppress_res_info = TRUE THEN
208 fnd_message.set_token('RESOURCE', '');
209 ELSE
210 fnd_message.set_token('RESOURCE', get_resource_info(g_messages(i).resource_id, g_messages(i).resource_type));
211 END IF;
212 END IF;
213
214 IF g_messages(i).start_datetime IS NOT NULL THEN
215 fnd_message.set_token('START_TIME', format_date(g_messages(i).start_datetime));
216 END IF;
217
218 IF g_messages(i).end_datetime IS NOT NULL THEN
219 fnd_message.set_token('END_TIME', format_date(g_messages(i).end_datetime));
220 END IF;
221
222 IF g_messages(i).error_reason IS NOT NULL THEN
223 fnd_message.set_token('REASON', g_messages(i).error_reason);
224 END IF;
225
226 fnd_msg_pub.ADD;
227
228 IF g_messages(i).message_type = g_error_message THEN
229 l_failed := l_failed + 1;
230 ELSE
231 l_success := l_success + 1;
232 END IF;
233 ELSE
234 IF l_debug THEN
235 debug( 'Trip#' || g_messages(i).trip_id
236 || ' for resource ' || get_resource_info(g_messages(i).resource_id, g_messages(i).resource_type)
237 || ' between ' || format_date(g_messages(i).start_datetime)
238 || ' and ' || format_date(g_messages(i).end_datetime)
239 || ' processed successfully'
240 , 'PROCESS_ACTION'
241 , fnd_log.level_event
242 );
243 END IF;
244 l_success := l_success + 1;
245 END IF;
246 END LOOP;
247
248 IF l_failed > 0 THEN
249 x_return_status := fnd_api.g_ret_sts_error;
250 END IF;
251
252 -- There is only trip involved... and therefore no need to status message.
253 IF p_trip_id IS NOT NULL THEN
254 RETURN;
255 END IF;
256
257 OPEN c_action_name;
258 FETCH c_action_name INTO l_action_name;
259 CLOSE c_action_name;
260
261 IF p_resource_tbl.COUNT = 1 THEN
262 l_res_name := csf_resource_pub.get_resource_name(p_resource_tbl(1).resource_id, p_resource_tbl(1).resource_type);
263 ELSE
264 l_res_name := '';
265 END IF;
266
267 IF l_failed > 0 THEN
268 fnd_message.set_name('CSF', 'CSF_TRIPS_ACTION_WARN');
269 fnd_message.set_token('FAILED', l_failed);
270 ELSE
271 fnd_message.set_name('CSF', 'CSF_TRIPS_ACTION_SUCC');
272 END IF;
273
274 fnd_message.set_token('SUCCESS', l_success);
275 fnd_message.set_token('ACTION', l_action_name);
276 fnd_message.set_token('RESOURCE', l_res_name);
277 fnd_message.set_token('START_DATE', p_start_date);
278 fnd_message.set_token('END_DATE', p_end_date);
279 fnd_msg_pub.ADD;
280 END process_messages;
281
282 PROCEDURE init_package IS
283 BEGIN
284 g_duration_uom := fnd_profile.value('CSF_UOM_MINUTES');
285 g_overtime := NVL(fnd_profile.value('MAXOVERTIME'), 0) / g_mins_in_day;
286 g_debug := NVL(fnd_profile.value('AFLOG_ENABLED'), 'N');
287 g_debug_level := NVL(fnd_profile.value_specific('AFLOG_LEVEL'), fnd_log.level_event);
288 g_datetime_fmt_mask := NVL(fnd_profile.value('ICX_DATE_FORMAT_MASK'), 'DD-MON-YYYY') || ' HH24:MI';
289 g_tz_enabled := fnd_timezones.timezones_enabled;
290 g_server_tz_code := fnd_timezones.get_server_timezone_code;
291 g_client_tz_code := fnd_timezones.get_client_timezone_code;
292
293 g_planned_status_id := fnd_profile.value('CSF_DEFAULT_TASK_PLANNED_STATUS');
294 g_assigned_status_id := fnd_profile.value('CSF_DEFAULT_TASK_ASSIGNED_STATUS');
295 g_blocked_planned_status_id := fnd_profile.value('CSF_DEFAULT_TASK_BLOCKED_PLAN_STATUS');
296 g_blocked_assigned_status_id := fnd_profile.value('CSF_DEFAULT_TASK_BLOCKEDASS_STATUS');
297 g_closed_status_id := fnd_profile.value('CSF_DFLT_AUTO_CLOSE_TASK_STATUS');
298
299 SELECT name INTO g_dep_task_name
300 FROM jtf_task_types_vl WHERE task_type_id = g_dep_task_type_id;
301
302 SELECT name INTO g_arr_task_name
303 FROM jtf_task_types_vl WHERE task_type_id = g_arr_task_type_id;
304
305 --EXECUTE IMMEDIATE 'alter session set timed_statistics=true';
306 --EXECUTE IMMEDIATE 'alter session set statistics_level=all';
307 --EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
308 EXCEPTION
309 WHEN OTHERS THEN
310 debug('Unable to initialize the Package - SQLCODE = ' || SQLCODE || ' : SQLERRM = ' || SQLERRM, 'INIT', fnd_log.level_unexpected);
311 END;
312
313 FUNCTION trip_has_active_tasks(p_trip_id NUMBER)
314 RETURN BOOLEAN IS
315 CURSOR c_active_tasks_exist IS
316 SELECT 1
317 FROM cac_sr_object_capacity oc
318 WHERE object_capacity_id = p_trip_id
319 AND EXISTS (SELECT 1
320 FROM jtf_task_assignments ta
321 , jtf_task_statuses_b ts
322 , jtf_tasks_b t
323 WHERE ta.object_capacity_id = oc.object_capacity_id
324 AND ts.task_status_id = ta.assignment_status_id
325 AND NVL(ts.closed_flag, 'N') = 'N'
326 AND NVL(ts.completed_flag, 'N') = 'N'
327 AND NVL(ts.cancelled_flag, 'N') = 'N'
328 AND NVL(ts.rejected_flag, 'N') = 'N'
329 AND t.task_id = ta.task_id
330 AND NVL(t.deleted_flag, 'N') = 'N'
331 AND t.task_type_id NOT IN (20, 21));
332 l_result NUMBER;
333 BEGIN
334 OPEN c_active_tasks_exist;
335 FETCH c_active_tasks_exist INTO l_result;
336 CLOSE c_active_tasks_exist;
337
338 RETURN l_result IS NOT NULL;
339 END trip_has_active_tasks;
340
341 -- Returns all the Trips which overlaps with the Passed Timings for the Resource
342 FUNCTION find_trips(
343 p_resource_tbl IN csf_resource_pub.resource_tbl_type
344 , p_start_date_time IN DATE
345 , p_end_date_time IN DATE
346 , p_overtime_flag IN VARCHAR2 DEFAULT NULL
347 ) RETURN trip_tbl_type IS
348
349 l_trips_count NUMBER;
350 l_trips trip_tbl_type;
351 l_overtime NUMBER;
352 i PLS_INTEGER;
353
354 CURSOR c_trips (p_resource_id NUMBER, p_resource_type VARCHAR2) IS
355 SELECT *
356 FROM cac_sr_object_capacity
357 WHERE object_id = p_resource_id
358 AND object_type = p_resource_type
359 AND p_start_date_time <= (end_date_time + l_overtime)
360 AND p_end_date_time >= start_date_time
361 ORDER BY start_date_time, object_capacity_id;
362
363 BEGIN
364 l_overtime := 0;
365 IF p_overtime_flag IS NULL OR p_overtime_flag = fnd_api.g_true THEN
366 l_overtime := g_overtime;
367 END IF;
368
369 l_trips_count := 0;
370
371 i := p_resource_tbl.FIRST;
372 -- Find Trips for each resource and add it to the output table.
373 WHILE i IS NOT NULL LOOP
374 -- Loop through all the Trips found for the criteria specified.
375 FOR v_trip IN c_trips(p_resource_tbl(i).resource_id, p_resource_tbl(i).resource_type) LOOP
376 l_trips_count := l_trips_count + 1;
377
378 l_trips(l_trips_count).trip_id := v_trip.object_capacity_id;
379 l_trips(l_trips_count).object_version_number := v_trip.object_version_number;
380 l_trips(l_trips_count).resource_type := v_trip.object_type;
381 l_trips(l_trips_count).resource_id := v_trip.object_id;
382 l_trips(l_trips_count).start_date_time := v_trip.start_date_time;
383 l_trips(l_trips_count).end_date_time := v_trip.end_date_time;
384 l_trips(l_trips_count).available_hours := v_trip.available_hours;
385 l_trips(l_trips_count).available_hours_before := v_trip.available_hours_before;
386 l_trips(l_trips_count).available_hours_after := v_trip.available_hours_after;
387 l_trips(l_trips_count).schedule_detail_id := v_trip.schedule_detail_id;
388 l_trips(l_trips_count).status := v_trip.status;
389 END LOOP;
390
391 i := p_resource_tbl.NEXT(i);
392 END LOOP;
393
394 RETURN l_trips;
395 END find_trips;
396
397 FUNCTION get_trip(p_trip_id IN NUMBER) RETURN trip_rec_type AS
398 l_trip trip_rec_type;
399 CURSOR c_trip IS
400 SELECT *
401 FROM cac_sr_object_capacity
402 WHERE object_capacity_id = p_trip_id;
403 BEGIN
404 FOR v_trip IN c_trip LOOP
405 l_trip.trip_id := v_trip.object_capacity_id;
406 l_trip.object_version_number := v_trip.object_version_number;
407 l_trip.resource_type := v_trip.object_type;
408 l_trip.resource_id := v_trip.object_id;
409 l_trip.start_date_time := v_trip.start_date_time;
410 l_trip.end_date_time := v_trip.end_date_time;
411 l_trip.available_hours := v_trip.available_hours;
412 l_trip.available_hours_before := v_trip.available_hours_before;
413 l_trip.available_hours_after := v_trip.available_hours_after;
414 l_trip.schedule_detail_id := v_trip.schedule_detail_id;
415 l_trip.status := v_trip.status;
416 END LOOP;
417
418 RETURN l_trip;
419 END get_trip;
420
421 PROCEDURE create_shift_tasks(
422 p_api_version IN NUMBER
423 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
424 , p_commit IN VARCHAR2 DEFAULT NULL
425 , x_return_status OUT NOCOPY VARCHAR2
426 , x_msg_data OUT NOCOPY VARCHAR2
427 , x_msg_count OUT NOCOPY NUMBER
428 , p_resource_id IN NUMBER
429 , p_resource_type IN VARCHAR2
430 , p_start_date_time IN DATE
431 , p_end_date_time IN DATE
432 , p_create_dep_task IN BOOLEAN
433 , p_create_arr_task IN BOOLEAN
434 , x_dep_task_id OUT NOCOPY NUMBER
435 , x_arr_task_id OUT NOCOPY NUMBER
436 ) IS
437 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_SHIFT_TASKS';
438 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
439 l_address csf_resource_address_pvt.address_rec_type;
440 l_task_assign_tbl jtf_tasks_pub.task_assign_tbl;
441 BEGIN
442
443 IF p_create_dep_task = FALSE AND p_create_arr_task = FALSE THEN
444 RETURN;
445 END IF;
446
447 -- Get the Resource's Address for this Date
448 l_address := csf_resource_pub.get_resource_party_address(
449 p_res_id => p_resource_id
450 , p_res_type => p_resource_type
451 , p_date => p_start_date_time
452 );
453
454 IF l_debug THEN
455 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);
456 END IF;
457
458 IF l_address.party_site_id IS NULL THEN
459 IF l_debug THEN
460 x_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
461 debug(' CSF_RESOURCE_ADDRESS_PVT failed to give Party Site ID' || x_msg_data, l_api_name, fnd_log.level_error);
462 END IF;
463 fnd_message.set_name('CSF', 'CSF_RESOURCE_NO_ACTIVE_PARTY');
464 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
465 fnd_message.set_token('DATE', format_date(p_start_date_time));
466 fnd_msg_pub.ADD;
467 RAISE fnd_api.g_exc_error;
468 END IF;
469
470 -- Departure and Arrival Task Resource Assignment
471 l_task_assign_tbl(1).resource_id := p_resource_id;
472 l_task_assign_tbl(1).resource_type_code := p_resource_type;
473 l_task_assign_tbl(1).assignment_status_id := g_assigned_status_id;
474
475 -- Create the Departure Task
476 IF p_create_dep_task THEN
477 jtf_tasks_pub.create_task(
478 p_api_version => 1.0
479 , p_task_name => g_dep_task_name
480 , p_task_type_id => g_dep_task_type_id
481 , p_task_status_id => g_assigned_status_id
482 , p_owner_id => p_resource_id
483 , p_owner_type_code => p_resource_type
484 , p_address_id => l_address.party_site_id
485 , p_customer_id => l_address.party_id
486 , p_planned_start_date => p_start_date_time
487 , p_planned_end_date => p_start_date_time
488 , p_scheduled_start_date => p_start_date_time
489 , p_scheduled_end_date => p_start_date_time
490 , p_duration => 0
491 , p_duration_uom => g_duration_uom
492 , p_bound_mode_code => 'BTS'
493 , p_soft_bound_flag => 'Y'
494 , p_task_assign_tbl => l_task_assign_tbl
495 , x_return_status => x_return_status
496 , x_msg_count => x_msg_count
497 , x_msg_data => x_msg_data
498 , x_task_id => x_dep_task_id
499 );
500
501 IF x_return_status <> fnd_api.g_ret_sts_success THEN
502 fnd_message.set_name('CSF', 'CSF_TASK_CREATE_FAIL');
503 fnd_message.set_token('TASK_NAME', g_dep_task_name);
504 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
505 fnd_msg_pub.ADD;
506
507 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
508 RAISE fnd_api.g_exc_unexpected_error;
509 END IF;
510 RAISE fnd_api.g_exc_error;
511 END IF;
512
513 IF l_debug THEN
514 debug(' Created Departure Task - Task ID = ' || x_dep_task_id, l_api_name, fnd_log.level_statement);
515 END IF;
516 END IF;
517
518 -- Create the Arrival Task
519 IF p_create_arr_task THEN
520 jtf_tasks_pub.create_task(
521 p_api_version => 1.0
522 , p_task_name => g_arr_task_name
523 , p_task_type_id => g_arr_task_type_id
524 , p_task_status_id => g_assigned_status_id
525 , p_owner_id => p_resource_id
526 , p_owner_type_code => p_resource_type
527 , p_address_id => l_address.party_site_id
528 , p_customer_id => l_address.party_id
529 , p_planned_start_date => p_end_date_time
530 , p_planned_end_date => p_end_date_time
531 , p_scheduled_start_date => p_end_date_time
532 , p_scheduled_end_date => p_end_date_time
533 , p_duration => 0
534 , p_duration_uom => g_duration_uom
535 , p_bound_mode_code => 'BTS'
536 , p_soft_bound_flag => 'Y'
537 , p_task_assign_tbl => l_task_assign_tbl
538 , x_return_status => x_return_status
539 , x_msg_count => x_msg_count
540 , x_msg_data => x_msg_data
541 , x_task_id => x_arr_task_id
542 );
543
544 IF x_return_status <> fnd_api.g_ret_sts_success THEN
545 fnd_message.set_name('CSF', 'CSF_TASK_CREATE_FAIL');
546 fnd_message.set_token('TASK_NAME', g_arr_task_name);
547 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
548 fnd_msg_pub.ADD;
549
550 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
551 RAISE fnd_api.g_exc_unexpected_error;
552 END IF;
553 RAISE fnd_api.g_exc_error;
554 END IF;
555 IF l_debug THEN
556 debug(' Created Arrival Task - Task ID = ' || x_arr_task_id, l_api_name, fnd_log.level_statement);
557 END IF;
558 END IF;
559 EXCEPTION
560 WHEN fnd_api.g_exc_error THEN
561 x_return_status := fnd_api.g_ret_sts_error;
562 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
563 WHEN fnd_api.g_exc_unexpected_error THEN
564 x_return_status := fnd_api.g_ret_sts_unexp_error;
565 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
566 WHEN OTHERS THEN
567 x_return_status := fnd_api.g_ret_sts_unexp_error;
568 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
569 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
570 END IF;
571 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
572 END create_shift_tasks;
573
574 FUNCTION get_new_task_status(p_action VARCHAR2, p_current_status NUMBER)
575 RETURN NUMBER IS
576 BEGIN
577 IF p_action = g_action_block_trip THEN
578 IF p_current_status = g_planned_status_id THEN
579 RETURN g_blocked_planned_status_id;
580 ELSIF p_current_status = g_assigned_status_id THEN
581 RETURN g_blocked_assigned_status_id;
582 ELSE
583 RETURN NULL;
584 END IF;
585 ELSIF p_action = g_action_unblock_trip THEN
586 IF p_current_status = g_blocked_planned_status_id THEN
587 RETURN g_planned_status_id;
588 ELSIF p_current_status = g_blocked_assigned_status_id THEN
589 RETURN g_assigned_status_id;
590 ELSE
591 RETURN NULL;
592 END IF;
593 ELSIF p_action = g_action_close_trip THEN
594 RETURN g_closed_status_id;
595 ELSE
596 RETURN NULL;
597 END IF;
598 END get_new_task_status;
599
600 PROCEDURE new_trip(
601 x_return_status OUT NOCOPY VARCHAR2
602 , x_msg_data OUT NOCOPY VARCHAR2
603 , x_msg_count OUT NOCOPY NUMBER
604 , p_resource_id IN NUMBER
605 , p_resource_type IN VARCHAR2
606 , p_start_date_time IN DATE
607 , p_end_date_time IN DATE
608 , p_status IN NUMBER DEFAULT NULL
609 , p_schedule_detail_id IN NUMBER DEFAULT NULL
610 , p_find_tasks IN VARCHAR2 DEFAULT NULL
611 , p_dep_task_id IN NUMBER DEFAULT NULL
612 , p_arr_task_id IN NUMBER DEFAULT NULL
613 , x_trip OUT NOCOPY trip_rec_type
614 ) IS
615 l_api_name CONSTANT VARCHAR2(30) := 'NEW_TRIP';
616 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
617
618 l_available_hours NUMBER;
619 l_time_occupied NUMBER;
620 l_dep_task_id NUMBER;
621 l_arr_task_id NUMBER;
622 i PLS_INTEGER;
623 l_object_capacity_tbl cac_sr_object_capacity_pub.object_capacity_tbl_type;
624 l_object_tasks_tbl cac_sr_object_capacity_pub.object_tasks_tbl_type;
625
626 CURSOR c_linkable_tasks IS
627 SELECT ta.task_assignment_id
628 , ta.object_version_number
629 , ta.task_id
630 , ta.booking_start_date
631 , ta.booking_end_date
632 , csf_util_pvt.convert_to_minutes(
633 ta.sched_travel_duration
634 , ta.sched_travel_duration_uom
635 ) travel_time
636 FROM jtf_task_assignments ta
637 , jtf_task_statuses_b ts
638 , jtf_tasks_b t
639 WHERE ta.resource_id = p_resource_id
640 AND ta.resource_type_code = p_resource_type
641 AND ta.assignee_role = 'ASSIGNEE'
642 AND ts.task_status_id = ta.assignment_status_id
643 AND NVL(ts.closed_flag, 'N') = 'N'
644 AND NVL(ts.completed_flag, 'N') = 'N'
645 AND NVL(ts.cancelled_flag, 'N') = 'N'
646 AND t.task_id = ta.task_id
647 AND NVL(t.deleted_flag, 'N') <> 'Y'
648 AND ta.booking_start_date <= (p_end_date_time + g_overtime)
649 AND ta.booking_end_date >= p_start_date_time
650 AND (t.task_type_id NOT IN (20, 21) OR t.task_id IN (l_dep_task_id, l_arr_task_id));
651
652 CURSOR c_shift_tasks_info IS
653 SELECT ta.task_assignment_id, ta.object_version_number, ta.task_id
654 FROM jtf_task_assignments ta
655 WHERE ta.task_id IN (l_dep_task_id, l_arr_task_id);
656
657 BEGIN
658 SAVEPOINT csf_new_trip;
659
660 x_return_status := fnd_api.g_ret_sts_success;
661
662 IF l_debug THEN
663 debug(' Creating Trip between ' || format_date(p_start_date_time) || ' and ' || format_date(p_end_date_time), l_api_name, fnd_log.level_statement);
664 END IF;
665
666 -- Trip Available Hours
667 l_available_hours := (p_end_date_time - p_start_date_time) * g_hours_in_day;
668
669 -- Check#3 - The Trip Duration should be lesser than 24 Hours.
670 IF l_available_hours > g_hours_in_day THEN
671 IF l_debug THEN
672 debug(' The specified Trip Length is greater than one day', l_api_name, fnd_log.level_error);
673 END IF;
674 fnd_message.set_name('CSF', 'CSF_TRIP_LENGTH_MORE_THAN_DAY');
675 fnd_msg_pub.ADD;
676 RAISE fnd_api.g_exc_error;
677 END IF;
678
679
680 -- Create new Shift Tasks for the Trip to be created.
681 IF p_dep_task_id IS NULL OR p_arr_task_id IS NULL THEN
682 create_shift_tasks(
683 p_api_version => 1.0
684 , p_init_msg_list => fnd_api.g_false
685 , p_commit => fnd_api.g_false
686 , x_return_status => x_return_status
687 , x_msg_data => x_msg_data
688 , x_msg_count => x_msg_count
689 , p_resource_id => p_resource_id
690 , p_resource_type => p_resource_type
691 , p_start_date_time => p_start_date_time
692 , p_end_date_time => p_end_date_time
693 , p_create_dep_task => p_dep_task_id IS NULL
694 , p_create_arr_task => p_arr_task_id IS NULL
695 , x_dep_task_id => l_dep_task_id
696 , x_arr_task_id => l_arr_task_id
697 );
698
699 IF x_return_status <> fnd_api.g_ret_sts_success THEN
700 IF l_debug THEN
701 debug(' Unable to Create Shift Tasks: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
702 END IF;
703 IF x_return_status = fnd_api.g_ret_sts_error THEN
704 RAISE fnd_api.g_exc_error;
705 ELSE
706 RAISE fnd_api.g_exc_unexpected_error;
707 END IF;
708 END IF;
709 IF l_debug THEN
710 debug(' Created new Shift Tasks - Dep#' || l_dep_task_id || ' : Arr#' || l_arr_task_id, l_api_name, fnd_log.level_statement);
711 END IF;
712 l_dep_task_id := NVL(p_dep_task_id, l_dep_task_id);
713 l_arr_task_id := NVL(p_arr_task_id, l_arr_task_id);
714 ELSE
715 -- Use the existing ones.
716 l_dep_task_id := p_dep_task_id;
717 l_arr_task_id := p_arr_task_id;
718 IF l_debug THEN
719 debug(' Using existing Shift Tasks - Dep#' || l_dep_task_id || ' : Arr#' || l_arr_task_id, l_api_name, fnd_log.level_statement);
720 END IF;
721 END IF;
722
723 i := 0;
724 IF p_find_tasks IS NULL OR p_find_tasks = fnd_api.g_true THEN
725 FOR v_task IN c_linkable_tasks LOOP
726 l_time_occupied := v_task.booking_end_date - v_task.booking_start_date; -- Scheduled Task Duration
727 l_time_occupied := l_time_occupied + NVL(v_task.travel_time, 0) / g_mins_in_day; -- Scheduled Travel Duration
728 l_available_hours := l_available_hours - l_time_occupied * g_hours_in_day;
729
730 IF l_debug THEN
731 debug(' Linking TaskID #' || v_task.task_id || ' : Time Used = ' || l_time_occupied * g_hours_in_day, l_api_name, fnd_log.level_statement);
732 END IF;
733
734 i := i + 1;
735 l_object_tasks_tbl(i).task_assignment_id := v_task.task_assignment_id;
736 l_object_tasks_tbl(i).task_assignment_ovn := v_task.object_version_number;
737 l_object_tasks_tbl(i).object_capacity_tbl_idx := 1;
738 END LOOP;
739 ELSE
740 FOR v_task IN c_shift_tasks_info LOOP
741 IF l_debug THEN
742 debug(' Linking Shift TaskID #' || v_task.task_id, l_api_name, fnd_log.level_statement);
743 END IF;
744
745 i := i + 1;
746 l_object_tasks_tbl(i).task_assignment_id := v_task.task_assignment_id;
747 l_object_tasks_tbl(i).task_assignment_ovn := v_task.object_version_number;
748 l_object_tasks_tbl(i).object_capacity_tbl_idx := 1;
749 END LOOP;
750 END IF;
751
752 -- Create the Object Capacity Record
753 l_object_capacity_tbl(1).object_type := p_resource_type;
754 l_object_capacity_tbl(1).object_id := p_resource_id;
755 l_object_capacity_tbl(1).start_date_time := p_start_date_time;
756 l_object_capacity_tbl(1).end_date_time := p_end_date_time;
757 l_object_capacity_tbl(1).available_hours := l_available_hours;
758 l_object_capacity_tbl(1).status := p_status;
759 l_object_capacity_tbl(1).schedule_detail_id := p_schedule_detail_id;
760
761 IF l_debug THEN
762 debug(' Trip Available Hours = ' || l_available_hours, l_api_name, fnd_log.level_statement);
763 END IF;
764
765 -- Create the Trip by calling Object Capacity Table Handlers
766 cac_sr_object_capacity_pub.insert_object_capacity(
767 p_api_version => 1.0
768 , p_init_msg_list => fnd_api.g_false
769 , x_return_status => x_return_status
770 , x_msg_count => x_msg_count
771 , x_msg_data => x_msg_data
772 , p_object_capacity => l_object_capacity_tbl
773 , p_update_tasks => fnd_api.g_true
774 , p_object_tasks => l_object_tasks_tbl
775 );
776
777 IF x_return_status <> fnd_api.g_ret_sts_success THEN
778 IF l_debug THEN
779 x_msg_data := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_last, p_encoded => fnd_api.g_false);
780 debug(' Unable to Create the Object Capacity: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
781 END IF;
782 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
783 RAISE fnd_api.g_exc_unexpected_error;
784 END IF;
785 RAISE fnd_api.g_exc_error;
786 END IF;
787
788 x_trip.trip_id := l_object_capacity_tbl(1).object_capacity_id;
789 x_trip.object_version_number := 1;
790 x_trip.resource_id := p_resource_id;
791 x_trip.resource_type := p_resource_type;
792 x_trip.start_date_time := p_start_date_time;
793 x_trip.end_date_time := p_end_date_time;
794 x_trip.available_hours := l_available_hours;
795 x_trip.status := p_status;
796 x_trip.schedule_detail_id := p_schedule_detail_id;
797
798 IF l_debug THEN
799 debug(' Created Trip - TripID#' || x_trip.trip_id, l_api_name, fnd_log.level_statement);
800 END IF;
801 EXCEPTION
802 WHEN fnd_api.g_exc_error THEN
803 ROLLBACK TO csf_new_trip;
804 x_return_status := fnd_api.g_ret_sts_error;
805 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
806 WHEN fnd_api.g_exc_unexpected_error THEN
807 ROLLBACK TO csf_new_trip;
808 x_return_status := fnd_api.g_ret_sts_unexp_error;
809 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
810 WHEN OTHERS THEN
811 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
812 x_return_status := fnd_api.g_ret_sts_unexp_error;
813 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
814 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
815 END IF;
816 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
817 ROLLBACK TO csf_new_trip;
818 END new_trip;
819
820 PROCEDURE change_trip(
821 x_return_status OUT NOCOPY VARCHAR2
822 , x_msg_data OUT NOCOPY VARCHAR2
823 , x_msg_count OUT NOCOPY NUMBER
824 , p_trip IN trip_rec_type
825 , p_object_version_number IN NUMBER
826 , p_available_hours IN NUMBER DEFAULT NULL
827 , p_upd_available_hours IN NUMBER DEFAULT NULL
828 , p_available_hours_before IN NUMBER DEFAULT NULL
829 , p_available_hours_after IN NUMBER DEFAULT NULL
830 , p_status IN NUMBER DEFAULT NULL
831 , p_update_tasks IN VARCHAR2 DEFAULT NULL
832 , p_task_action IN VARCHAR2 DEFAULT NULL
833 ) IS
834 l_api_name CONSTANT VARCHAR2(30) := 'CHANGE_TRIP';
835 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
836
837 l_new_task_status NUMBER;
838 l_shift_length NUMBER;
839 l_available_hours NUMBER;
840 l_available_hours_before NUMBER;
841 l_available_hours_after NUMBER;
842
843 CURSOR c_tasks (p_task_type VARCHAR2) IS
844 SELECT ta.task_assignment_id
845 , ta.object_version_number ta_object_version_number
846 , ta.assignment_status_id
847 , t.task_id
848 , t.task_number
849 , t.object_version_number task_ovn
850 , t.task_status_id
851 FROM cac_sr_object_capacity cac
852 , jtf_task_assignments ta
853 , jtf_tasks_b t
854 , jtf_task_statuses_b ts
855 WHERE cac.object_capacity_id = p_trip.trip_id
856 AND ta.resource_id = cac.object_id
857 AND ta.resource_type_code = cac.object_type
858 AND ( (ta.object_capacity_id IS NOT NULL AND ta.object_capacity_id = cac.object_capacity_id)
859 OR (ta.booking_start_date <= (cac.end_date_time + g_overtime) AND ta.booking_end_date >= cac.start_date_time) )
860 AND t.task_id = ta.task_id
861 AND ts.task_status_id = ta.assignment_status_id
862 AND NVL(ts.closed_flag, 'N') = 'N'
863 AND NVL(ts.completed_flag, 'N') = 'N'
864 AND NVL(ts.cancelled_flag, 'N') = 'N'
865 AND NVL(ts.working_flag, 'N') = 'N'
866 AND NVL(t.deleted_flag, 'N') = 'N'
867 AND ta.actual_start_date IS NULL
868 AND (t.source_object_type_code = 'SR' OR t.task_type_id IN (20, 21))
869 AND (p_task_type = 'ALL' OR t.task_type_id IN (20, 21));
870
871 l_task_type VARCHAR2(10);
872 l_validation_level NUMBER;
873 BEGIN
874 SAVEPOINT csf_change_trip;
875
876 x_return_status := fnd_api.g_ret_sts_success;
877
878 l_shift_length := (p_trip.end_date_time - p_trip.start_date_time) * g_hours_in_day;
879 l_available_hours := p_trip.available_hours;
880 l_available_hours_before := p_trip.available_hours_before;
881 l_available_hours_after := p_trip.available_hours_after;
882
883 IF p_available_hours IS NOT NULL THEN
884 l_available_hours := p_available_hours;
885 ELSIF p_upd_available_hours IS NOT NULL THEN
886 l_available_hours := p_trip.available_hours + p_upd_available_hours;
887 END IF;
888
889 -- If Available Hours (either as value or as inc/dec) is passed, and Avl Before/After
890 -- is not passed, they should be nulled out.
891 IF p_available_hours IS NOT NULL OR p_upd_available_hours IS NOT NULL THEN
892 l_available_hours_before := NVL(p_available_hours_before, fnd_api.g_miss_num);
893 l_available_hours_after := NVL(p_available_hours_after, fnd_api.g_miss_num);
894 ELSE
895 l_available_hours_before := p_available_hours_before;
896 l_available_hours_after := p_available_hours_after;
897 END IF;
898
899 IF l_available_hours > l_shift_length
900 OR (l_available_hours_before <> fnd_api.g_miss_num AND l_available_hours_before > l_shift_length)
901 OR (l_available_hours_after <> fnd_api.g_miss_num AND l_available_hours_after > l_shift_length)
902 THEN
903 -- Trip Availability is more than the Shift Length
904 IF l_debug THEN
905 debug(' Trip Availability is more than Shift Length', l_api_name, fnd_log.level_error);
906 END IF;
907
908 fnd_message.set_name('CSF', 'CSF_TRIP_WRONG_AVAILABILITY');
909 fnd_message.set_token('AVAILABLE', l_available_hours);
910 fnd_message.set_token('AVLBEFORE', l_available_hours_before);
911 fnd_message.set_token('AVLAFTER', l_available_hours_after);
912 fnd_msg_pub.ADD;
913 RAISE fnd_api.g_exc_error;
914 END IF;
915
916 IF l_available_hours = l_shift_length
917 AND ( ( l_available_hours_before IS NOT NULL AND l_available_hours_before <> fnd_api.g_miss_num )
918 OR ( l_available_hours_after IS NOT NULL AND l_available_hours_after <> fnd_api.g_miss_num )
919 )
920 THEN
921 -- Trip Availability is equal to the Shift Length and Before and Afters are not NULL
922 IF l_debug THEN
923 debug(' Available Hours Before and After must be NULL when Availability is Trip Length', l_api_name, fnd_log.level_error);
924 END IF;
925
926 fnd_message.set_name('CSF', 'CSF_TRIP_WRONG_AVL_BEFOREAFTER');
927 fnd_message.set_token('AVLBEFORE', l_available_hours_before);
928 fnd_message.set_token('AVLAFTER', l_available_hours_after);
929 fnd_message.set_token('AVAILABLE', l_available_hours);
930 fnd_msg_pub.ADD;
931 RAISE fnd_api.g_exc_error;
932 END IF;
933
934 cac_sr_object_capacity_pub.update_object_capacity(
935 p_api_version => 1.0
936 , x_return_status => x_return_status
937 , x_msg_count => x_msg_count
938 , x_msg_data => x_msg_data
939 , p_object_capacity_id => p_trip.trip_id
940 , p_object_version_number => p_object_version_number
941 , p_available_hours => l_available_hours
942 , p_available_hours_before => l_available_hours_before
943 , p_available_hours_after => l_available_hours_after
944 , p_status => p_status
945 );
946
947 IF x_return_status <> fnd_api.g_ret_sts_success THEN
948 IF l_debug THEN
949 debug(' Unable to Update the Object Capacity', l_api_name, fnd_log.level_error);
950 END IF;
951 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
952 RAISE fnd_api.g_exc_unexpected_error;
953 END IF;
954 RAISE fnd_api.g_exc_error;
955 END IF;
956
957 -- If Tasks need not be updated.... nothing more to be done. Exit
958 IF p_update_tasks = fnd_api.g_false THEN
959 RETURN;
960 END IF;
961
962 -- If New Trip Status equals Old Trip Status.... nothing more to be done. Exit
963 IF NVL(p_status, p_trip.status) = p_trip.status THEN
964 RETURN;
965 END IF;
966
967 IF p_task_action = g_action_close_trip THEN
968 l_validation_level := fnd_api.g_valid_level_none;
969 l_task_type := 'SHIFTS';
970 ELSE
971 l_validation_level := fnd_api.g_valid_level_full;
972 l_task_type := 'ALL';
973 END IF;
974
975 FOR v_task IN c_tasks(l_task_type) LOOP
976 l_new_task_status := get_new_task_status(p_task_action, v_task.assignment_status_id);
977 IF l_new_task_status IS NOT NULL THEN
978 IF l_debug THEN
979 debug(' Updating the Task - TaskID# ' || v_task.task_id, l_api_name, fnd_log.level_statement);
980 END IF;
981 csf_task_assignments_pub.update_assignment_status(
982 p_api_version => 1.0
983 , p_init_msg_list => fnd_api.g_false
984 , p_validation_level => l_validation_level
985 , p_commit => fnd_api.g_false
986 , x_return_status => x_return_status
987 , x_msg_count => x_msg_count
988 , x_msg_data => x_msg_data
989 , p_task_assignment_id => v_task.task_assignment_id
990 , p_object_version_number => v_task.ta_object_version_number
991 , p_assignment_status_id => l_new_task_status
992 , x_task_object_version_number => v_task.task_ovn
993 , x_task_status_id => v_task.task_status_id
994 );
995
996 IF x_return_status <> fnd_api.g_ret_sts_success THEN
997 -- Somehow direct population of the Token using fnd_msg_pub is not working
998 -- Therefore populating it in x_msg_data and using it to populate the Token REASON.
999 x_msg_data := fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false);
1000 IF l_debug THEN
1001 debug(' Unable to update the Assignment: Error = ' || x_msg_data, l_api_name, fnd_log.level_error);
1002 END IF;
1003 fnd_message.set_name('CSF', 'CSF_ASSIGNMENT_UPDATE_FAIL');
1004 fnd_message.set_token('TASK', v_task.task_number);
1005 fnd_message.set_token('REASON', x_msg_data);
1006 fnd_msg_pub.ADD;
1007 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1008 RAISE fnd_api.g_exc_unexpected_error;
1009 END IF;
1010 RAISE fnd_api.g_exc_error;
1011 END IF;
1012 END IF;
1013 END LOOP;
1014 EXCEPTION
1015 WHEN fnd_api.g_exc_error THEN
1016 ROLLBACK TO csf_change_trip;
1017 x_return_status := fnd_api.g_ret_sts_error;
1018 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1019 WHEN fnd_api.g_exc_unexpected_error THEN
1020 ROLLBACK TO csf_change_trip;
1021 x_return_status := fnd_api.g_ret_sts_unexp_error;
1022 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1023 WHEN OTHERS THEN
1024 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1025 x_return_status := fnd_api.g_ret_sts_unexp_error;
1026 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1027 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1028 END IF;
1029 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1030 ROLLBACK TO csf_change_trip;
1031 END change_trip;
1032
1033 PROCEDURE remove_trip(
1034 x_return_status OUT NOCOPY VARCHAR2
1035 , x_msg_data OUT NOCOPY VARCHAR2
1036 , x_msg_count OUT NOCOPY NUMBER
1037 , p_trip IN trip_rec_type
1038 , p_object_version_number IN NUMBER
1039 , p_check_active_tasks IN VARCHAR2 DEFAULT NULL
1040 ) IS
1041 l_api_name CONSTANT VARCHAR2(30) := 'REMOVE_TRIP';
1042 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1043
1044 -- No need to check Task Assignment Status as Task itself will reflect it for Shift Tasks.
1045 CURSOR c_shift_tasks IS
1046 SELECT t.task_id
1047 , t.object_version_number
1048 , t.task_name
1049 , t.task_number
1050 FROM jtf_task_assignments ta
1051 , jtf_tasks_vl t
1052 WHERE ta.object_capacity_id = p_trip.trip_id
1053 AND t.task_id = ta.task_id
1054 AND NVL(t.deleted_flag, 'N') = 'N'
1055 AND t.task_type_id IN (g_dep_task_type_id, g_arr_task_type_id);
1056
1057 BEGIN
1058 SAVEPOINT csf_remove_trip;
1059
1060 x_return_status := fnd_api.g_ret_sts_success;
1061
1062 IF l_debug THEN
1063 debug( ' Deleting the Trip #' || p_trip.trip_id
1064 || ' between ' || format_date(p_trip.start_date_time)
1065 || ' and ' || format_date(p_trip.end_date_time)
1066 , l_api_name, fnd_log.level_procedure
1067 );
1068 END IF;
1069
1070 -- Check whether the Trip is blocked
1071 IF p_trip.status = g_trip_unavailable THEN
1072 IF l_debug THEN
1073 debug(' The Trip is unavailable and so cant be deleted', l_api_name, fnd_log.level_error);
1074 END IF;
1075
1076 fnd_message.set_name('CSF', 'CSF_TRIP_IS_BLOCKED');
1077 fnd_msg_pub.ADD;
1078 RAISE fnd_api.g_exc_error;
1079 END IF;
1080
1081 -- Check whether there are active Task Assignments in the Trip
1082 IF NVL(p_check_active_tasks, fnd_api.g_true) = fnd_api.g_true THEN
1083 IF trip_has_active_tasks(p_trip.trip_id) THEN
1084 -- There are Active Task Assignments for the Trip.
1085 IF l_debug THEN
1086 debug(' Trip has active Tasks and so cant be deleted', l_api_name, fnd_log.level_error);
1087 END IF;
1088
1089 fnd_message.set_name('CSF', 'CSF_TRIP_HAS_ACTIVE_TASKS');
1090 fnd_msg_pub.ADD;
1091 RAISE fnd_api.g_exc_error;
1092 END IF;
1093 END IF;
1094
1095 -- Delete the Shift Tasks
1096 FOR v_shift_task IN c_shift_tasks LOOP
1097 IF l_debug THEN
1098 debug(' Deleting the Shift Task #' || v_shift_task.task_id, l_api_name, fnd_log.level_statement);
1099 END IF;
1100 jtf_tasks_pub.delete_task(
1101 p_api_version => 1.0
1102 , x_return_status => x_return_status
1103 , x_msg_count => x_msg_count
1104 , x_msg_data => x_msg_data
1105 , p_task_id => v_shift_task.task_id
1106 , p_object_version_number => v_shift_task.object_version_number
1107 );
1108 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1109 IF l_debug THEN
1110 debug(' Unable to Delete the Shift Task - ' || v_shift_task.task_name, l_api_name, fnd_log.level_error);
1111 END IF;
1112
1113 fnd_message.set_name('CSF', 'CSF_TASK_DELETE_FAIL');
1114 fnd_message.set_token('TASK', v_shift_task.task_number);
1115 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
1116 fnd_msg_pub.ADD;
1117 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1118 RAISE fnd_api.g_exc_unexpected_error;
1119 END IF;
1120 RAISE fnd_api.g_exc_error;
1121 END IF;
1122 END LOOP;
1123
1124 -- Delete the Object Capacity
1125 cac_sr_object_capacity_pub.delete_object_capacity(
1126 p_api_version => 1.0
1127 , x_return_status => x_return_status
1128 , x_msg_count => x_msg_count
1129 , x_msg_data => x_msg_data
1130 , p_object_capacity_id => p_trip.trip_id
1131 , p_object_version_number => p_object_version_number
1132 , p_update_tasks => fnd_api.g_false
1133 );
1134
1135 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1136 IF l_debug THEN
1137 debug(' Unable to Delete the Object Capacity', l_api_name, fnd_log.level_error);
1138 END IF;
1139 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1140 RAISE fnd_api.g_exc_unexpected_error;
1141 END IF;
1142 RAISE fnd_api.g_exc_error;
1143 END IF;
1144
1145 IF l_debug THEN
1146 debug(' Deleted the Trip', l_api_name, fnd_log.level_statement);
1147 END IF;
1148 EXCEPTION
1149 WHEN fnd_api.g_exc_error THEN
1150 ROLLBACK TO csf_remove_trip;
1151 x_return_status := fnd_api.g_ret_sts_error;
1152 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1153 WHEN fnd_api.g_exc_unexpected_error THEN
1154 ROLLBACK TO csf_remove_trip;
1155 x_return_status := fnd_api.g_ret_sts_unexp_error;
1156 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1157 WHEN OTHERS THEN
1158 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1159 x_return_status := fnd_api.g_ret_sts_unexp_error;
1160 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1161 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1162 END IF;
1163 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1164 ROLLBACK TO csf_remove_trip;
1165 END remove_trip;
1166
1167 PROCEDURE correct_trip(
1168 x_return_status OUT NOCOPY VARCHAR2
1169 , x_msg_data OUT NOCOPY VARCHAR2
1170 , x_msg_count OUT NOCOPY NUMBER
1171 , p_trip IN trip_rec_type
1172 , p_object_version_number IN NUMBER
1173 ) IS
1174 l_api_name CONSTANT VARCHAR2(30) := 'CORRECT_TRIP';
1175 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1176 l_available_hours NUMBER;
1177 l_total_task_time NUMBER;
1178 l_total_travel_time NUMBER;
1179
1180 l_dep_task_exists BOOLEAN;
1181 l_arr_task_exists BOOLEAN;
1182 l_dep_task_id NUMBER;
1183 l_arr_task_id NUMBER;
1184
1185 CURSOR c_tasks IS
1186 SELECT ta.task_id
1187 , t.task_number
1188 , ta.task_assignment_id
1189 , ta.object_version_number
1190 , ta.object_capacity_id wrong_trip_id
1191 , oc.object_capacity_id correct_trip_id
1192 FROM cac_sr_object_capacity oc
1193 , jtf_task_assignments ta
1194 , jtf_tasks_b t
1195 , jtf_task_statuses_b ts
1196 WHERE oc.object_capacity_id = p_trip.trip_id
1197 AND ta.resource_id = oc.object_id
1198 AND ta.resource_type_code = oc.object_type
1199 AND ta.assignee_role = 'ASSIGNEE'
1200 AND t.task_id = ta.task_id
1201 AND t.task_type_id NOT IN (20, 21)
1202 AND ts.task_status_id = ta.assignment_status_id
1203 AND NVL(ts.closed_flag, 'N') = 'N'
1204 AND NVL(ts.completed_flag, 'N') = 'N'
1205 AND NVL(ts.cancelled_flag, 'N') = 'N'
1206 AND NVL(ta.object_capacity_id, -1) <> oc.object_capacity_id
1207 AND ta.booking_start_date < (oc.end_date_time + g_overtime)
1208 AND ta.booking_end_date > oc.start_date_time
1209 UNION ALL
1210 SELECT ta.task_id
1211 , t.task_number
1212 , ta.task_assignment_id
1213 , ta.object_version_number
1214 , p_trip.trip_id wrong_trip_id
1215 , oc.object_capacity_id correct_trip_id
1216 FROM cac_sr_object_capacity oc
1217 , jtf_task_assignments ta
1218 , jtf_tasks_b t
1219 , jtf_task_statuses_b ts
1220 WHERE ta.object_capacity_id = p_trip.trip_id
1221 AND oc.object_id = ta.resource_id
1222 AND oc.object_type = ta.resource_type_code
1223 AND oc.object_capacity_id <> ta.object_capacity_id
1224 AND t.task_id = ta.task_id
1225 AND t.task_type_id NOT IN (20, 21)
1226 AND ts.task_status_id = ta.assignment_status_id
1227 AND NVL(ts.closed_flag, 'N') = 'N'
1228 AND NVL(ts.completed_flag, 'N') = 'N'
1229 AND NVL(ts.cancelled_flag, 'N') = 'N'
1230 AND ta.booking_start_date < (oc.end_date_time + g_overtime)
1231 AND ta.booking_end_date > oc.start_date_time
1232 UNION ALL
1233 SELECT ta.task_id
1234 , t.task_number
1235 , ta.task_assignment_id
1236 , ta.object_version_number
1237 , to_number(NULL) wrong_trip_id
1238 , p_trip.trip_id correct_trip_id
1239 FROM jtf_task_assignments ta
1240 , jtf_tasks_b t
1241 WHERE ta.task_id IN (l_dep_task_id, l_arr_task_id)
1242 AND t.task_id = ta.task_id;
1243
1244 CURSOR c_used_time IS
1245 SELECT SUM (ta.booking_end_date - ta.booking_start_date) used_time
1246 , SUM (NVL(csf_util_pvt.convert_to_minutes(
1247 ta.sched_travel_duration
1248 , ta.sched_travel_duration_uom
1249 ), 0)) travel_time
1250 FROM jtf_task_assignments ta
1251 , jtf_task_statuses_b ts
1252 WHERE ta.object_capacity_id = p_trip.trip_id
1253 AND ts.task_status_id = ta.assignment_status_id
1254 AND NVL(ts.closed_flag, 'N') = 'N'
1255 AND NVL(ts.completed_flag, 'N') = 'N'
1256 AND NVL(ts.cancelled_flag, 'N') = 'N';
1257
1258 CURSOR c_shift_tasks IS
1259 SELECT t.task_id
1260 , t.task_type_id
1261 , t.object_version_number
1262 , t.task_name
1263 , t.task_number
1264 , LAG(t.task_id) OVER (PARTITION BY t.task_type_id
1265 ORDER BY t.scheduled_start_date) duplicate
1266 FROM jtf_task_assignments ta
1267 , jtf_tasks_vl t
1268 WHERE ta.object_capacity_id = p_trip.trip_id
1269 AND t.task_id = ta.task_id
1270 AND NVL(t.deleted_flag, 'N') = 'N'
1271 AND t.task_type_id IN (20, 21);
1272 BEGIN
1273 SAVEPOINT csf_correct_trip;
1274
1275 x_return_status := fnd_api.g_ret_sts_success;
1276
1277 IF l_debug THEN
1278 debug(' Checking Shift Tasks', l_api_name, fnd_log.level_statement);
1279 END IF;
1280
1281 -- Clean up the Shift Tasks for the Trip.
1282 l_dep_task_exists := FALSE;
1283 l_arr_task_exists := FALSE;
1284 FOR v_shift_task IN c_shift_tasks LOOP
1285 IF v_shift_task.duplicate IS NOT NULL THEN
1286 IF l_debug THEN
1287 debug(' Deleting the Duplicate Shift Task #' || v_shift_task.task_id, l_api_name, fnd_log.level_statement);
1288 END IF;
1289 -- Departure Task already exists... Delete this Duplicate.
1290 jtf_tasks_pub.delete_task(
1291 p_api_version => 1.0
1292 , x_return_status => x_return_status
1293 , x_msg_count => x_msg_count
1294 , x_msg_data => x_msg_data
1295 , p_task_id => v_shift_task.task_id
1296 , p_object_version_number => v_shift_task.object_version_number
1297 );
1298 END IF;
1299 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1300 IF l_debug THEN
1301 debug(' Unable to Delete the Task', l_api_name, fnd_log.level_error);
1302 END IF;
1303
1304 fnd_message.set_name('CSF', 'CSF_TASK_DELETE_FAIL');
1305 fnd_message.set_token('TASK', v_shift_task.task_number);
1306 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
1307 fnd_msg_pub.ADD;
1308 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1309 RAISE fnd_api.g_exc_unexpected_error;
1310 END IF;
1311 RAISE fnd_api.g_exc_error;
1312 END IF;
1313
1314 IF v_shift_task.task_type_id = 20 THEN
1315 l_dep_task_exists := TRUE;
1316 ELSE
1317 l_arr_task_exists := TRUE;
1318 END IF;
1319 END LOOP;
1320
1321 IF NOT(l_dep_task_exists) OR NOT(l_arr_task_exists) THEN
1322 IF l_debug THEN
1323 debug(' Either Departure or Arrival Task is absent. Creating them', l_api_name, fnd_log.level_statement);
1324 END IF;
1325
1326 create_shift_tasks(
1327 p_api_version => 1.0
1328 , x_return_status => x_return_status
1329 , x_msg_data => x_msg_data
1330 , x_msg_count => x_msg_count
1331 , p_resource_id => p_trip.resource_id
1332 , p_resource_type => p_trip.resource_type
1333 , p_start_date_time => p_trip.start_date_time
1334 , p_end_date_time => p_trip.end_date_time
1335 , p_create_dep_task => NOT(l_dep_task_exists)
1336 , p_create_arr_task => NOT(l_arr_task_exists)
1337 , x_dep_task_id => l_dep_task_id
1338 , x_arr_task_id => l_arr_task_id
1339 );
1340
1341 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1342 IF l_debug THEN
1343 debug(' Creation of Shift Tasks failed', l_api_name, fnd_log.level_error);
1344 END IF;
1345 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1346 RAISE fnd_api.g_exc_unexpected_error;
1347 END IF;
1348 RAISE fnd_api.g_exc_error;
1349 END IF;
1350 END IF;
1351
1352 FOR v_task IN c_tasks LOOP
1353 IF l_debug THEN
1354 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);
1355 END IF;
1356
1357 jtf_task_assignments_pub.update_task_assignment(
1358 p_api_version => 1.0
1359 , x_return_status => x_return_status
1360 , x_msg_data => x_msg_data
1361 , x_msg_count => x_msg_count
1362 , p_task_assignment_id => v_task.task_assignment_id
1363 , p_object_version_number => v_task.object_version_number
1364 , p_object_capacity_id => v_task.correct_trip_id
1365 , p_enable_workflow => fnd_api.g_miss_char
1366 , p_abort_workflow => fnd_api.g_miss_char
1367 );
1368
1369 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1370 fnd_message.set_name('CSF', 'CSF_ASSIGNMENT_UPDATE_FAIL');
1371 fnd_message.set_token('TASK', v_task.task_number);
1372 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
1373 fnd_msg_pub.ADD;
1374 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1375 RAISE fnd_api.g_exc_unexpected_error;
1376 END IF;
1377 RAISE fnd_api.g_exc_error;
1378 END IF;
1379 END LOOP;
1380
1381 -- Update the Availability of the Trip.
1382 OPEN c_used_time;
1383 FETCH c_used_time INTO l_total_task_time, l_total_travel_time;
1384 CLOSE c_used_time;
1385
1386 l_available_hours := (p_trip.end_date_time - p_trip.start_date_time)
1387 - l_total_task_time
1388 - l_total_travel_time / g_mins_in_day;
1389
1390 cac_sr_object_capacity_pub.update_object_capacity(
1391 p_api_version => 1.0
1392 , x_return_status => x_return_status
1393 , x_msg_count => x_msg_count
1394 , x_msg_data => x_msg_data
1395 , p_object_capacity_id => p_trip.trip_id
1396 , p_object_version_number => p_object_version_number
1397 , p_available_hours => l_available_hours * g_hours_in_day
1398 );
1399
1400 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1401 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1402 RAISE fnd_api.g_exc_unexpected_error;
1403 END IF;
1404 RAISE fnd_api.g_exc_error;
1405 END IF;
1406 EXCEPTION
1407 WHEN fnd_api.g_exc_error THEN
1408 ROLLBACK TO csf_correct_trip;
1409 x_return_status := fnd_api.g_ret_sts_error;
1410 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1411 WHEN fnd_api.g_exc_unexpected_error THEN
1412 ROLLBACK TO csf_correct_trip;
1413 x_return_status := fnd_api.g_ret_sts_unexp_error;
1414 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1415 WHEN OTHERS THEN
1416 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1417 x_return_status := fnd_api.g_ret_sts_unexp_error;
1418 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1419 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1420 END IF;
1421 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1422 ROLLBACK TO csf_correct_trip;
1423 END correct_trip;
1424
1425
1426 /******************************************************************************************
1427 * *
1428 * Private Functions and Procedures dealing with Multiple Trips *
1429 * *
1430 *******************************************************************************************/
1431 /**
1432 * Creates Trips for the passed Resource between the Start and End Dates
1433 * based on the Shift Definitions existing for the resource between the dates.
1434 * <br>
1435 * Validations done in addition to the ones in CREATE_TRIP
1436 * 1. If any one trip exists without any Dep/Arr, then the API errors out asking
1437 * to use FIX TRIPS to fix the Trips in the range first.
1438 * 2. If there exists no Shift Definitions for the Resource between the given
1439 * dates, the API errors out with No Shift Defn message.
1440 * 3. If there exists atleast one Shift Task not tied to any Trip between the
1441 * the dates, the API errors out asking to use UPGRADE_TRIPS to upgrade
1442 * from Shift Model to Trips Model.
1443 *
1444 * @param p_api_version API Version (1.0)
1445 * @param p_init_msg_list Initialize Message List
1446 * @param p_commit Commits the Database
1447 * @param x_return_status Return Status of the Procedure.
1448 * @param x_msg_data Stack of Error Messages.
1449 * @param x_msg_count Number of Messages in the Stack.
1450 * @param p_resource_id Resource ID
1451 * @param p_resource_type Resource Type
1452 * @param p_start_date Start Date
1453 * @param p_end_date End Date
1454 *
1455 * @see create_trip Create Trip API
1456 **/
1457
1458 PROCEDURE delete_trips(
1459 x_return_status OUT NOCOPY VARCHAR2
1460 , x_msg_data OUT NOCOPY VARCHAR2
1461 , x_msg_count OUT NOCOPY NUMBER
1462 , p_trips IN trip_tbl_type
1463 )IS
1464 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRIPS';
1465 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1466 BEGIN
1467 x_return_status := fnd_api.g_ret_sts_success;
1468
1469 IF l_debug THEN
1470 debug('Deleting the given Trips', l_api_name, fnd_log.level_procedure);
1471 END IF;
1472
1473 FOR i IN 1..p_trips.COUNT LOOP
1474 remove_trip(
1475 x_return_status => x_return_status
1476 , x_msg_data => x_msg_data
1477 , x_msg_count => x_msg_count
1478 , p_trip => p_trips(i)
1479 , p_object_version_number => p_trips(i).object_version_number
1480 );
1481
1482 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1483 add_message(
1484 p_trip => p_trips(i)
1485 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
1486 , p_msg_name => 'CSF_TRIP_DELETE_FAIL_OTHER'
1487 , p_msg_type => g_error_message
1488 );
1489 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1490 RAISE fnd_api.g_exc_unexpected_error;
1491 END IF;
1492 ELSE
1493 add_message(p_trips(i));
1494 END IF;
1495 END LOOP;
1496 EXCEPTION
1497 WHEN fnd_api.g_exc_error THEN
1498 x_return_status := fnd_api.g_ret_sts_error;
1499 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1500 WHEN fnd_api.g_exc_unexpected_error THEN
1501 x_return_status := fnd_api.g_ret_sts_unexp_error;
1502 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1503 WHEN OTHERS THEN
1504 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1505 x_return_status := fnd_api.g_ret_sts_unexp_error;
1506 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1507 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1508 END IF;
1509 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1510 END delete_trips;
1511
1512 PROCEDURE fix_trips(
1513 x_return_status OUT NOCOPY VARCHAR2
1514 , x_msg_data OUT NOCOPY VARCHAR2
1515 , x_msg_count OUT NOCOPY NUMBER
1516 , p_trips IN trip_tbl_type
1517 ) IS
1518 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRIPS';
1519 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1520 BEGIN
1521 x_return_status := fnd_api.g_ret_sts_success;
1522
1523 IF l_debug THEN
1524 debug('Fixing the given Trips', l_api_name, fnd_log.level_procedure);
1525 END IF;
1526
1527 FOR i IN 1..p_trips.COUNT LOOP
1528 correct_trip(
1529 x_return_status => x_return_status
1530 , x_msg_data => x_msg_data
1531 , x_msg_count => x_msg_count
1532 , p_trip => p_trips(i)
1533 , p_object_version_number => p_trips(i).object_version_number
1534 );
1535
1536 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1537 add_message(
1538 p_trip => p_trips(i)
1539 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
1540 , p_msg_name => 'CSF_TRIP_FIX_FAIL_OTHER'
1541 , p_msg_type => g_error_message
1542 );
1543 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1544 RAISE fnd_api.g_exc_unexpected_error;
1545 END IF;
1546 ELSE
1547 add_message(p_trips(i));
1548 END IF;
1549 END LOOP;
1550 EXCEPTION
1551 WHEN fnd_api.g_exc_error THEN
1552 x_return_status := fnd_api.g_ret_sts_error;
1553 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1554 WHEN fnd_api.g_exc_unexpected_error THEN
1555 x_return_status := fnd_api.g_ret_sts_unexp_error;
1556 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1557 WHEN OTHERS THEN
1558 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
1559 x_return_status := fnd_api.g_ret_sts_unexp_error;
1560 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1561 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1562 END IF;
1563 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
1564 END fix_trips;
1565
1566 PROCEDURE create_trips(
1567 x_return_status OUT NOCOPY VARCHAR2
1568 , x_msg_data OUT NOCOPY VARCHAR2
1569 , x_msg_count OUT NOCOPY NUMBER
1570 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
1571 , p_start_date IN DATE
1572 , p_end_date IN DATE
1573 , p_delete_trips IN BOOLEAN DEFAULT FALSE
1574 ) IS
1575 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TRIPS';
1576 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
1577
1578 l_check_failed VARCHAR2(1);
1579 l_res_id NUMBER;
1580 l_res_type jtf_objects_b.object_code%TYPE;
1581 l_start DATE;
1582 l_end DATE;
1583
1584 l_shifts csf_resource_pub.shift_tbl_type;
1585 l_shift_idx PLS_INTEGER;
1586
1587 l_trip_idx PLS_INTEGER;
1588 l_new_trip trip_rec_type;
1589 l_old_trips trip_tbl_type;
1590 l_new_trips trip_tbl_type;
1591
1592 l_trip_length NUMBER;
1593 l_prev_trip_id NUMBER;
1594 l_temp_trip_tbl number_tbl_type;
1595 l_del_trip_tbl jtf_number_table;
1596 l_old_new_trip_map number_tbl_type;
1597
1598 l_msg_name fnd_new_messages.message_name%TYPE;
1599 l_reason fnd_new_messages.message_text%TYPE;
1600
1601 -- Query to check for the existence of Stray Shift Tasks
1602 CURSOR c_shift_tasks_exist (p_res_id NUMBER, p_res_type VARCHAR2, p_start DATE, p_end DATE) IS
1603 SELECT 'Y'
1604 FROM jtf_tasks_b t
1605 , jtf_task_assignments ta
1606 WHERE t.owner_id = p_res_id
1607 AND t.owner_type_code = p_res_type
1608 AND t.planned_start_date BETWEEN p_start AND p_end
1609 AND t.task_type_id IN (20, 21)
1610 AND NVL(t.deleted_flag, 'N') <> 'Y'
1611 AND ta.task_id = t.task_id
1612 AND ta.assignee_role = 'ASSIGNEE'
1613 AND ta.object_capacity_id IS NULL
1614 AND ROWNUM = 1;
1615
1616 -- Cursor to retrive tasks still linked to old trip
1617 CURSOR c_unlinked_tasks IS
1618 SELECT /*+ cardinality (oc 1) */
1619 ta.task_assignment_id
1620 , ta.object_version_number
1621 , ta.object_capacity_id
1622 , ta.task_id
1623 , ta.booking_start_date
1624 , ta.booking_end_date
1625 , csf_util_pvt.convert_to_minutes(
1626 ta.sched_travel_duration
1627 , ta.sched_travel_duration_uom
1628 ) travel_time
1629 FROM TABLE ( CAST(l_del_trip_tbl AS jtf_number_table) ) oc
1630 , jtf_task_assignments ta
1631 , jtf_task_statuses_b ts
1632 , jtf_tasks_b t
1633 WHERE ta.object_capacity_id = oc.COLUMN_VALUE
1634 AND ts.task_status_id = ta.assignment_status_id
1635 AND NVL(ts.closed_flag, 'N') = 'N'
1636 AND NVL(ts.completed_flag, 'N') = 'N'
1637 AND NVL(ts.cancelled_flag, 'N') = 'N'
1638 AND NVL(ts.rejected_flag, 'N') = 'N'
1639 AND t.task_id = ta.task_id
1640 AND NVL(t.deleted_flag, 'N') <> 'Y'
1641 AND t.task_type_id NOT IN (20, 21)
1642 ORDER BY ta.object_capacity_id;
1643
1644 CURSOR c_trip_info(p_trip_id NUMBER) IS
1645 SELECT oc.object_version_number
1646 , oc.available_hours
1647 FROM cac_sr_object_capacity oc
1648 WHERE oc.object_capacity_id = p_trip_id;
1649
1650 l_trip_info c_trip_info%ROWTYPE;
1651
1652 BEGIN
1653 SAVEPOINT csf_create_trips;
1654
1655 x_return_status := fnd_api.g_ret_sts_success;
1656
1657 l_res_id := p_resource_tbl(1).resource_id;
1658 l_res_type := p_resource_tbl(1).resource_type;
1659
1660 IF l_debug THEN
1661 IF p_delete_trips THEN
1662 debug('Replacing Trips for Resource#' || l_res_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
1663 ELSE
1664 debug('Creating Trips for Resource#' || l_res_id || ' between ' || p_start_date || ' and ' || p_end_date, l_api_name, fnd_log.level_procedure);
1665 END IF;
1666 END IF;
1667
1668 -- Get the Resource's Shifts
1669 csf_resource_pub.get_resource_shifts(
1670 p_api_version => 1.0
1671 , x_return_status => x_return_status
1672 , x_msg_count => x_msg_count
1673 , x_msg_data => x_msg_data
1674 , p_resource_id => l_res_id
1675 , p_resource_type => l_res_type
1676 , p_start_date => p_start_date
1677 , p_end_date => p_end_date
1678 , x_shifts => l_shifts
1679 );
1680
1681 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1682 IF l_debug THEN
1683 debug(' No Shifts were found for the resource between the timeframe', l_api_name, fnd_log.level_error);
1684 END IF;
1685 add_message(
1686 p_res_id => l_res_id
1687 , p_res_type => l_res_type
1688 , p_start => p_start_date
1689 , p_end => p_end_date
1690 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
1691 , p_msg_name => 'CSF_RETRIEVE_SHIFTS_FAIL'
1692 , p_msg_type => g_error_message
1693 );
1694 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1695 RAISE fnd_api.g_exc_unexpected_error;
1696 END IF;
1697 RAISE fnd_api.g_exc_error;
1698 END IF;
1699
1700 -- Check whether Shift Tasks are already there between the given Time Frame.
1701 l_start := l_shifts(l_shifts.FIRST).start_datetime;
1702 l_end := l_shifts(l_shifts.LAST).end_datetime;
1703
1704 OPEN c_shift_tasks_exist(l_res_id, l_res_type, l_start, l_end);
1705 FETCH c_shift_tasks_exist INTO l_check_failed;
1706 IF c_shift_tasks_exist%NOTFOUND THEN
1707 l_check_failed := 'N';
1708 END IF;
1709 CLOSE c_shift_tasks_exist;
1710
1711 IF l_check_failed = 'Y' THEN
1712 -- Shift Tasks exists. Should use "Upgrade to Trips" API rather than "Create Trip".
1713 IF l_debug THEN
1714 debug(' Shift tasks are present between the timeframe', l_api_name, fnd_log.level_error);
1715 END IF;
1716 add_message(
1717 p_res_id => l_res_id
1718 , p_res_type => l_res_type
1719 , p_start => p_start_date
1720 , p_end => p_end_date
1721 , p_msg_name => 'CSF_USE_UPGRADE_TRIPS'
1722 , p_msg_type => g_error_message
1723 );
1724 RAISE fnd_api.g_exc_error;
1725 END IF;
1726
1727 -- Get all the trips in the required interval
1728 l_start := LEAST(p_start_date, l_shifts(l_shifts.FIRST).start_datetime);
1729 l_end := GREATEST(p_end_date, l_shifts(l_shifts.LAST).end_datetime);
1730
1731 IF l_debug THEN
1732 debug(' Querying for the Trips between ' || format_date(l_start) || ' and ' || format_date(l_end), l_api_name, fnd_log.level_statement);
1733 END IF;
1734
1735 l_old_trips := find_trips(p_resource_tbl, l_start, l_end);
1736
1737 IF l_debug THEN
1738 debug(' Current Trips existing: Count = ' || l_old_trips.COUNT, l_api_name, fnd_log.level_statement);
1739 FOR i IN 1..l_old_trips.COUNT LOOP
1740 debug( ' Trip ID = ' || l_old_trips(i).trip_id
1741 || ' Start Time = ' || format_date(l_old_trips(i).start_date_time)
1742 || ' End Time = ' || format_date(l_old_trips(i).end_date_time)
1743 , l_api_name, fnd_log.level_statement);
1744 END LOOP;
1745 END IF;
1746
1747 l_del_trip_tbl := jtf_number_table();
1748
1749 -- Loop through each Shift to create a new Trip
1750 l_shift_idx := l_shifts.FIRST;
1751 WHILE l_shift_idx IS NOT NULL LOOP
1752 IF l_debug THEN
1753 debug( ' Trying to create trip for shift between '
1754 || format_date(l_shifts(l_shift_idx).start_datetime) || ' and '
1755 || format_date(l_shifts(l_shift_idx).end_datetime)
1756 , l_api_name, fnd_log.level_statement
1757 );
1758 END IF;
1759 BEGIN
1760 SAVEPOINT csf_process_shift;
1761
1762 x_return_status := fnd_api.g_ret_sts_success;
1763
1764 l_start := l_shifts(l_shift_idx).start_datetime;
1765 l_end := l_shifts(l_shift_idx).end_datetime;
1766 l_msg_name := NULL;
1767
1768 -- Loop through each trip and check for overlap with any of the current trips
1769 l_trip_idx := l_old_trips.FIRST;
1770 WHILE l_trip_idx IS NOT NULL LOOP
1771 IF l_debug THEN
1772 debug(' Checking for overlap with old trip ' || l_old_trips(l_trip_idx).trip_id
1773 , l_api_name, fnd_log.level_statement);
1774 END IF;
1775
1776 IF time_overlaps(l_old_trips(l_trip_idx), l_shifts(l_shift_idx)) THEN
1777 -- If Trips can be deleted, then we can avoid the error "Duplicate Trip"
1778 -- by deleting the overlapping trip and only when it falls within the range.
1779 IF NOT p_delete_trips
1780 OR NOT time_overlaps(l_old_trips(l_trip_idx), p_start_date, p_end_date)
1781 THEN
1782 l_msg_name := 'CSF_TRIP_CREATE_FAIL_DUP';
1783 RAISE fnd_api.g_exc_error;
1784 END IF;
1785
1786 remove_trip(
1787 x_return_status => x_return_status
1788 , x_msg_data => x_msg_data
1789 , x_msg_count => x_msg_count
1790 , p_trip => l_old_trips(l_trip_idx)
1791 , p_object_version_number => l_old_trips(l_trip_idx).object_version_number
1792 , p_check_active_tasks => fnd_api.g_false
1793 );
1794
1795 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1796 l_msg_name := 'CSF_TRIP_DELETE_FAIL_OTHER';
1797 RAISE fnd_api.g_exc_error;
1798 END IF;
1799
1800 -- Since Trip is not present in DB... it should be removed from memory too.
1801 l_temp_trip_tbl(l_temp_trip_tbl.COUNT+1) := l_old_trips(l_trip_idx).trip_id;
1802
1803 ELSIF l_old_trips(l_trip_idx).start_date_time > (l_end + g_overtime) THEN
1804 -- Since Trips and Shifts are ordered by time, there is no point in searching forward
1805 EXIT;
1806 END IF;
1807 l_trip_idx := l_old_trips.NEXT(l_trip_idx);
1808 END LOOP;
1809
1810 -- Loop through each trip and check for overlap with any of the new trips
1811 l_trip_idx := l_new_trips.LAST;
1812 WHILE l_trip_idx IS NOT NULL LOOP
1813 IF l_debug THEN
1814 debug(' Checking for overlap with new trip ' || l_new_trips(l_trip_idx).trip_id
1815 , l_api_name, fnd_log.level_statement);
1816 END IF;
1817 IF time_overlaps(l_new_trips(l_trip_idx), l_shifts(l_shift_idx)) THEN
1818 l_msg_name := 'CSF_TRIP_CREATE_FAIL_DUP';
1819 RAISE fnd_api.g_exc_error;
1820 ELSIF (l_new_trips(l_trip_idx).end_date_time + g_overtime) < l_start THEN
1821 -- Since Trips and Shifts are ordered by time, there is no point in searching forward
1822 EXIT;
1823 END IF;
1824 l_trip_idx := l_new_trips.PRIOR(l_trip_idx);
1825 END LOOP;
1826
1827 new_trip(
1828 x_return_status => x_return_status
1829 , x_msg_data => x_msg_data
1830 , x_msg_count => x_msg_count
1831 , p_resource_id => l_res_id
1832 , p_resource_type => l_res_type
1833 , p_start_date_time => l_start
1834 , p_end_date_time => l_end
1835 , p_find_tasks => fnd_api.g_true
1836 , x_trip => l_new_trip
1837 );
1838
1839 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1840 l_msg_name := 'CSF_TRIP_CREATE_FAIL_OTHER';
1841 RAISE fnd_api.g_exc_error;
1842 END IF;
1843
1844 -- Since the Old Trips are removed from Database and there is no error
1845 -- encountered we can remove the Old Trips from Memory also
1846 FOR i in 1..l_temp_trip_tbl.COUNT LOOP
1847 l_trip_idx := l_old_trips.FIRST;
1848 WHILE l_trip_idx IS NOT NULL LOOP
1849 IF l_temp_trip_tbl(i) = l_old_trips(l_trip_idx).trip_id THEN
1850 l_old_trips.DELETE(l_trip_idx);
1851 END IF;
1852 l_trip_idx := l_old_trips.NEXT(l_trip_idx);
1853 END LOOP;
1854
1855 l_del_trip_tbl.extend(1);
1856 l_del_trip_tbl(l_del_trip_tbl.LAST) := l_temp_trip_tbl(i);
1857 l_old_new_trip_map(l_temp_trip_tbl(i)) := l_new_trips.COUNT + 1;
1858 END LOOP;
1859
1860 l_temp_trip_tbl.DELETE;
1861 l_new_trips(l_new_trips.COUNT + 1) := l_new_trip;
1862
1863 EXCEPTION
1864 WHEN OTHERS THEN
1865 ROLLBACK TO csf_process_shift;
1866 l_new_trip.trip_id := -1;
1867 l_new_trips(l_new_trips.COUNT + 1) := l_new_trip;
1868 IF l_msg_name = 'CSF_TRIP_DELETE_FAIL_OTHER' THEN
1869 l_start := l_old_trips(l_trip_idx).start_date_time;
1870 l_end := l_old_trips(l_trip_idx).end_date_time;
1871 END IF;
1872 IF l_msg_name <> 'CSF_TRIP_CREATE_FAIL_DUP' THEN
1873 l_reason := fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false);
1874 ELSE
1875 l_reason := NULL;
1876 END IF;
1877
1878 IF l_debug THEN
1879 IF l_msg_name = 'CSF_TRIP_CREATE_FAIL_DUP' THEN
1880 IF NOT p_delete_trips THEN
1881 debug( ' Since delete trips not allowed.. we have overlap with existing trip'
1882 , l_api_name, fnd_log.level_error
1883 );
1884 ELSE
1885 debug( ' Delete trips allowed.. but we have conflict with new trip'
1886 , l_api_name, fnd_log.level_error
1887 );
1888 END IF;
1889 ELSIF l_msg_name = 'CSF_TRIP_CREATE_FAIL_OTHER' THEN
1890 debug( ' Error occurred while creating the trip between '
1891 || format_date(l_start) || ' and ' || format_date(l_end)
1892 || ' : Error = ' || l_reason
1893 , l_api_name, fnd_log.level_error
1894 );
1895 ELSIF l_msg_name = 'CSF_TRIP_DELETE_FAIL_OTHER' THEN
1896 debug( ' Error occurred while deleting the trip between '
1897 || format_date(l_start) || ' and ' || format_date(l_end)
1898 || ' : Error = ' || l_reason
1899 , l_api_name, fnd_log.level_error
1900 );
1901 END IF;
1902 END IF;
1903
1904 add_message(
1905 p_res_id => l_res_id
1906 , p_res_type => l_res_type
1907 , p_start => l_start
1908 , p_end => l_end
1909 , p_reason => l_reason
1910 , p_msg_name => l_msg_name
1911 , p_msg_type => g_error_message
1912 );
1913 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1914 RAISE fnd_api.g_exc_unexpected_error;
1915 END IF;
1916 END;
1917
1918 l_shift_idx := l_shifts.NEXT(l_shift_idx);
1919 END LOOP;
1920
1921 IF p_delete_trips THEN
1922 -- Link all the Unlinked Task Assignments to the corresponding shifts
1923 IF l_debug THEN
1924 debug(' Linking unlinked Task Assignments if any of old trips to new trips', l_api_name, fnd_log.level_statement);
1925 END IF;
1926
1927 l_trip_length := 0;
1928 FOR v_task IN c_unlinked_tasks LOOP
1929 l_trip_idx := l_old_new_trip_map(v_task.object_capacity_id);
1930
1931 -- Moment we have processed all Tasks linked to old trip.. update Prev Trip's Capacity.
1932 IF l_prev_trip_id <> l_new_trips(l_trip_idx).trip_id THEN
1933 IF l_debug THEN
1934 debug( ' Decreasing Trip#' || l_prev_trip_id
1935 || ' Capacity to be lesser by ' || l_trip_length*g_hours_in_day
1936 , l_api_name, fnd_log.level_statement
1937 );
1938 END IF;
1939
1940 OPEN c_trip_info(l_prev_trip_id);
1941 FETCH c_trip_info INTO l_trip_info;
1942 CLOSE c_trip_info;
1943
1944 l_trip_info.available_hours := l_trip_info.available_hours
1945 - l_trip_length * g_hours_in_day;
1946 -- Update the new Trip Capacity of the new trip created (Always OVN is 1)
1947 cac_sr_object_capacity_pub.update_object_capacity(
1948 p_api_version => 1.0
1949 , x_return_status => x_return_status
1950 , x_msg_count => x_msg_count
1951 , x_msg_data => x_msg_data
1952 , p_object_capacity_id => l_prev_trip_id
1953 , p_object_version_number => l_trip_info.object_version_number
1954 , p_available_hours => l_trip_info.available_hours
1955 );
1956
1957 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1958 IF l_debug THEN
1959 debug( ' Error updating Trip. ' || fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false), l_api_name, fnd_log.level_error);
1960 END IF;
1961 END IF;
1962
1963 l_trip_length := 0;
1964 END IF;
1965
1966 IF l_debug THEN
1967 debug( ' Linking Task ' || v_task.task_id
1968 || ' : Old Trip = ' || v_task.object_capacity_id
1969 || ' : New Trip = ' || l_new_trips(l_trip_idx).trip_id
1970 , l_api_name, fnd_log.level_statement
1971 );
1972 END IF;
1973
1974 l_trip_length := l_trip_length
1975 + v_task.booking_end_date - v_task.booking_start_date
1976 + NVL(v_task.travel_time, 0) / g_mins_in_day;
1977
1978 jtf_task_assignments_pub.update_task_assignment(
1979 p_api_version => 1.0
1980 , x_return_status => x_return_status
1981 , x_msg_data => x_msg_data
1982 , x_msg_count => x_msg_count
1983 , p_task_assignment_id => v_task.task_assignment_id
1984 , p_object_version_number => v_task.object_version_number
1985 , p_object_capacity_id => l_new_trips(l_trip_idx).trip_id
1986 , p_enable_workflow => fnd_api.g_miss_char
1987 , p_abort_workflow => fnd_api.g_miss_char
1988 );
1989
1990 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1991 IF l_debug THEN
1992 debug( ' Error updating Task Assignment', l_api_name, fnd_log.level_error);
1993 END IF;
1994 add_message(
1995 p_res_id => l_res_id
1996 , p_res_type => l_res_type
1997 , p_start => p_start_date
1998 , p_end => p_end_date
1999 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2000 , p_msg_name => 'CSF_TRIP_REPLACE_FAIL_RELINK'
2001 , p_msg_type => g_error_message
2002 );
2003 RAISE fnd_api.g_exc_error;
2004 END IF;
2005
2006 -- Moment we encounter a Task linked to old trip.. we have a conflict.
2007 IF l_prev_trip_id IS NULL OR l_prev_trip_id <> l_new_trips(l_trip_idx).trip_id THEN
2008 -- Notify the user that the new trip has conflicts. Note its not an error.
2009 add_message(
2010 p_res_id => l_res_id
2011 , p_res_type => l_res_type
2012 , p_start => l_shifts(l_trip_idx).start_datetime
2013 , p_end => l_shifts(l_trip_idx).end_datetime
2014 , p_msg_name => 'CSF_TRIP_CREATED_CONFLICTS'
2015 , p_msg_type => g_warning_message
2016 );
2017 END IF;
2018
2019 l_prev_trip_id := l_new_trips(l_trip_idx).trip_id;
2020 END LOOP;
2021
2022 -- Delete the remaining trips not replaced during Create Operation
2023 l_trip_idx := l_old_trips.FIRST;
2024 WHILE l_trip_idx IS NOT NULL LOOP
2025 -- Delete only those trips falling within the given dates.
2026 IF time_overlaps(l_old_trips(l_trip_idx), p_start_date, p_end_date) THEN
2027
2028 IF l_debug THEN
2029 debug( ' Deleting the non-overlapping Trip ' || l_old_trips(l_trip_idx).trip_id
2030 || ' between ' || format_date(l_old_trips(l_trip_idx).start_date_Time)
2031 || ' and ' || format_date(l_old_trips(l_trip_idx).end_date_Time)
2032 , l_api_name, fnd_log.level_statement
2033 );
2034 END IF;
2035
2036 IF trip_has_active_tasks(l_old_trips(l_trip_idx).trip_id) THEN
2037 IF l_debug THEN
2038 debug( ' Cant delete trip' || l_old_trips(l_trip_idx).trip_id
2039 || ' between ' || format_date(l_old_trips(l_trip_idx).start_date_Time)
2040 || ' and ' || format_date(l_old_trips(l_trip_idx).end_date_Time)
2041 || ' as there active tasks present'
2042 , l_api_name, fnd_log.level_error
2043 );
2044 END IF;
2045 add_message(l_old_trips(l_trip_idx), NULL, 'CSF_TRIP_REPLACE_FAIL_ACTIVE', g_error_message);
2046 ELSE
2047 remove_trip(
2048 x_return_status => x_return_status
2049 , x_msg_data => x_msg_data
2050 , x_msg_count => x_msg_count
2051 , p_trip => l_old_trips(l_trip_idx)
2052 , p_object_version_number => l_old_trips(l_trip_idx).object_version_number
2053 , p_check_active_tasks => fnd_api.g_true
2054 );
2055 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2056 add_message(
2057 p_trip => l_old_trips(l_trip_idx)
2058 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2059 , p_msg_name => 'CSF_TRIP_DELETE_FAIL_OTHER'
2060 , p_msg_type => g_error_message
2061 );
2062 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2063 RAISE fnd_api.g_exc_unexpected_error;
2064 END IF;
2065 END IF;
2066 END IF;
2067 END IF;
2068 l_trip_idx := l_old_trips.NEXT(l_trip_idx);
2069 END LOOP;
2070 END IF;
2071
2072 -- Now populate the Message Table so that the caller will
2073 -- get correct picture of Success vs Failure. Note that the failures
2074 -- are already accounted for in the above logic. Only Success needs to be
2075 -- stored. In case of REPLACE Action, we are bothered about how many trips
2076 -- created successfully and not how many deleted successfully.
2077 FOR i IN 1..l_new_trips.COUNT LOOP
2078 IF l_new_trips(i).trip_id <> -1 THEN
2079 add_message(l_res_id, l_res_type, l_shifts(i).start_datetime, l_shifts(i).end_datetime);
2080 END IF;
2081 END LOOP;
2082 EXCEPTION
2083 WHEN fnd_api.g_exc_error THEN
2084 ROLLBACK TO csf_create_trips;
2085 x_return_status := fnd_api.g_ret_sts_error;
2086 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2087 WHEN fnd_api.g_exc_unexpected_error THEN
2088 ROLLBACK TO csf_create_trips;
2089 x_return_status := fnd_api.g_ret_sts_unexp_error;
2090 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2091 WHEN OTHERS THEN
2092 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2093 x_return_status := fnd_api.g_ret_sts_unexp_error;
2094 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2095 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2096 END IF;
2097 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2098 ROLLBACK TO csf_create_trips;
2099 END create_trips;
2100
2101 /**
2102 * Upgrades the current data based on Shift Tasks Model to Trips Model.
2103 * Upgrades all the Trips which exists in the system demarcated by Shift Tasks to the
2104 * actual Trips Model by creating new records in Trips Table for the Resource
2105 * identified by P_RESOURCE_ID by querying for all the Departure and Arrival Shift Tasks
2106 * between the Start and End Dates and creating Trips for those Shift Tasks.
2107 * <br>
2108 * The major difference between CREATE_TRIPS and UPGRADE_TO_TRIPS is that the former
2109 * creates the Trips based on the current Shift Definitions. The Later creates Trips
2110 * based on the current Shift Tasks position.
2111 * <br>
2112 * For each trip to be created, it inturn calls CREATE_TRIP and so all
2113 * the validation that are done for CREATE_TRIP is applicable here also.
2114 * Since this API already has Shift Tasks and is creating Trips for those Shift Tasks
2115 * it fills the parameters P_DEP_TASK_ID and P_ARR_TASK_ID of CREATE_TRIP API.
2116 * <br>
2117 * If there are no fatal errors encountered, x_msg_data will contain the number
2118 * of Trips upgraded successfully and the number of Trips failed to be upgraded
2119 * because of possible overlap with existing trips. Note that this message is
2120 * not put in the Message Stack. So API users should not rely on the value of
2121 * x_msg_data to determine whether the API failed or not. Rather they should
2122 * rely only on standard way of checking x_return_status.
2123 *
2124 * @param p_api_version API Version (1.0)
2125 * @param p_init_msg_list Initialize Message List
2126 * @param p_commit Commits the Database
2127 * @param x_return_status Return Status of the Procedure.
2128 * @param x_msg_data Stack of Error Messages.
2129 * @param x_msg_count Number of Messages in the Stack.
2130 * @param p_resource_id Resource ID
2131 * @param p_resource_type Resource Type
2132 * @param p_start_date Start Date
2133 * @param p_end_date End Date
2134 *
2135 * @see create_trip Create Trip API
2136 * @see create_trips Create Trips API
2137 **/
2138 PROCEDURE upgrade_to_trips(
2139 x_return_status OUT NOCOPY VARCHAR2
2140 , x_msg_data OUT NOCOPY VARCHAR2
2141 , x_msg_count OUT NOCOPY NUMBER
2142 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
2143 , p_start_date IN DATE
2144 , p_end_date IN DATE
2145 ) IS
2146 l_api_name CONSTANT VARCHAR2(30) := 'UPGRADE_TO_TRIPS';
2147 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2148
2149 l_dep_task_tbl jtf_number_table;
2150 l_arr_task_tbl jtf_number_table;
2151 l_start_time_tbl jtf_date_table;
2152 l_end_time_tbl jtf_date_table;
2153 l_new_trip trip_rec_type;
2154
2155 CURSOR c_shift_tasks IS
2156 SELECT d.task_id dep_task_id
2157 , a.task_id arr_task_id
2158 , d.planned_start_date start_time
2159 , a.planned_end_date end_time
2160 FROM jtf_tasks_b d
2161 , jtf_task_assignments dta
2162 , jtf_tasks_b a
2163 , jtf_task_assignments ata
2164 WHERE d.owner_id = p_resource_tbl(1).resource_id
2165 AND d.owner_type_code = p_resource_tbl(1).resource_type
2166 AND d.planned_start_date BETWEEN p_start_date AND p_end_date
2167 AND d.task_type_id = 20
2168 AND NVL(d.deleted_flag, 'N') = 'N'
2169 AND dta.task_id = d.task_id
2170 AND dta.assignee_role = 'ASSIGNEE'
2171 AND dta.object_capacity_id IS NULL
2172 AND a.owner_id = d.owner_id
2173 AND a.owner_type_code = d.owner_type_code
2174 AND a.planned_end_date BETWEEN d.planned_start_date AND (d.planned_start_date + 1)
2175 AND a.task_type_id = 21
2176 AND NVL(a.deleted_flag, 'N') = 'N'
2177 AND ata.task_id = a.task_id
2178 AND ata.assignee_role = 'ASSIGNEE'
2179 AND ata.object_capacity_id IS NULL
2180 AND dta.shift_construct_id = ata.shift_construct_id
2181 ORDER BY d.planned_start_date;
2182
2183 BEGIN
2184 x_return_status := fnd_api.g_ret_sts_success;
2185
2186 IF l_debug THEN
2187 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);
2188 END IF;
2189
2190 -- Bulk Collecting all information about Shift Tasks.
2191 OPEN c_shift_tasks;
2192 FETCH c_shift_tasks BULK COLLECT INTO l_dep_task_tbl, l_arr_task_tbl, l_start_time_tbl, l_end_time_tbl;
2193 CLOSE c_shift_tasks;
2194
2195 FOR i IN 1..l_dep_task_tbl.COUNT LOOP
2196 IF l_debug THEN
2197 debug(' Found Shift Tasks - Dep #' || l_dep_task_tbl(i) || ' : Arr # ' || l_arr_task_tbl(i), l_api_name, fnd_log.level_procedure);
2198 END IF;
2199 -- Create a Trip between the Shift Tasks.
2200 new_trip(
2201 x_return_status => x_return_status
2202 , x_msg_data => x_msg_data
2203 , x_msg_count => x_msg_count
2204 , p_resource_id => p_resource_tbl(1).resource_id
2205 , p_resource_type => p_resource_tbl(1).resource_type
2206 , p_start_date_time => l_start_time_tbl(i)
2207 , p_end_date_time => l_end_time_tbl(i)
2208 , p_dep_task_id => l_dep_task_tbl(i)
2209 , p_arr_task_id => l_arr_task_tbl(i)
2210 , x_trip => l_new_trip
2211 );
2212
2213 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2214 add_message(
2215 p_res_id => p_resource_tbl(1).resource_id
2216 , p_res_type => p_resource_tbl(1).resource_type
2217 , p_start => l_start_time_tbl(i)
2218 , p_end => l_end_time_tbl(i)
2219 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2220 , p_msg_name => 'CSF_TRIP_CREATE_FAIL_OTHER'
2221 , p_msg_type => g_error_message
2222 );
2223 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2224 RAISE fnd_api.g_exc_unexpected_error;
2225 END IF;
2226 ELSE
2227 add_message(
2228 p_res_id => p_resource_tbl(1).resource_id
2229 , p_res_type => p_resource_tbl(1).resource_type
2230 , p_start => l_start_time_tbl(i)
2231 , p_end => l_end_time_tbl(i)
2232 );
2233 END IF;
2234 END LOOP;
2235 EXCEPTION
2236 WHEN fnd_api.g_exc_error THEN
2237 x_return_status := fnd_api.g_ret_sts_error;
2238 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2239 WHEN fnd_api.g_exc_unexpected_error THEN
2240 x_return_status := fnd_api.g_ret_sts_unexp_error;
2241 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2242 WHEN OTHERS THEN
2243 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2244 x_return_status := fnd_api.g_ret_sts_unexp_error;
2245 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2246 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2247 END IF;
2248 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2249 END upgrade_to_trips;
2250
2251 PROCEDURE update_trip_status(
2252 x_return_status OUT NOCOPY VARCHAR2
2253 , x_msg_data OUT NOCOPY VARCHAR2
2254 , x_msg_count OUT NOCOPY NUMBER
2255 , p_trip_action IN VARCHAR2
2256 , p_trips IN trip_tbl_type
2257 ) IS
2258 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP_STATUS';
2259 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2260
2261 l_new_trip_status NUMBER;
2262 l_trip_action VARCHAR2(30);
2263 BEGIN
2264 x_return_status := fnd_api.g_ret_sts_success;
2265
2266 IF l_debug THEN
2267 debug('Updating the status of the given trips', l_api_name, fnd_log.level_procedure);
2268 END IF;
2269
2270 IF p_trip_action IN (g_action_block_trip, g_action_close_trip) THEN
2271 l_new_trip_status := g_trip_unavailable;
2272 ELSIF p_trip_action = g_action_unblock_trip THEN
2273 l_new_trip_status := g_trip_available;
2274 END IF;
2275
2276 FOR i IN 1..p_trips.COUNT LOOP
2277 IF l_debug THEN
2278 debug('Updating Trip# ' || p_trips(i).trip_id, l_api_name, fnd_log.level_statement);
2279 END IF;
2280
2281 IF l_new_trip_status = p_trips(i).status THEN
2282 IF l_debug THEN
2283 debug(' Trip is already in correct status ' || p_trips(i).status, l_api_name, fnd_log.level_statement);
2284 END IF;
2285 GOTO NEXT_TRIP;
2286 END IF;
2287
2288 IF p_trip_action = g_action_close_trip AND (p_trips(i).end_date_time + g_overtime) > SYSDATE THEN
2289 IF l_debug THEN
2290 debug(' Trip is present or future dated. Cant close', l_api_name, fnd_log.level_error);
2291 END IF;
2292 add_message(
2293 p_trip => p_trips(i)
2294 , p_msg_name => 'CSF_TRIP_CLOSE_FAIL_ACTIVE'
2295 , p_msg_type => g_error_message
2296 );
2297 GOTO NEXT_TRIP;
2298 END IF;
2299
2300 IF p_trip_action = g_action_block_trip AND (p_trips(i).end_date_time + g_overtime) < SYSDATE THEN
2301 IF l_debug THEN
2302 debug(' Trip is past dated. Close it rather than blocking', l_api_name, fnd_log.level_statement);
2303 END IF;
2304 l_trip_action := g_action_close_trip;
2305 ELSE
2306 l_trip_action := p_trip_action;
2307 END IF;
2308
2309 change_trip(
2310 x_return_status => x_return_status
2311 , x_msg_data => x_msg_data
2312 , x_msg_count => x_msg_count
2313 , p_trip => p_trips(i)
2314 , p_object_version_number => p_trips(i).object_version_number
2315 , p_status => l_new_trip_status
2316 , p_update_tasks => fnd_api.g_true
2317 , p_task_action => l_trip_action
2318 );
2319
2320 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2321 add_message(
2322 p_trip => p_trips(i)
2323 , p_reason => fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2324 , p_msg_name => 'CSF_TRIP_UPDATE_FAIL_OTHER'
2325 , p_msg_type => g_error_message
2326 );
2327 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2328 RAISE fnd_api.g_exc_unexpected_error;
2329 END IF;
2330 ELSE
2331 add_message(p_trips(i));
2332 END IF;
2333
2334 <<NEXT_TRIP>>
2335 NULL;
2336 END LOOP;
2337 EXCEPTION
2338 WHEN fnd_api.g_exc_error THEN
2339 x_return_status := fnd_api.g_ret_sts_error;
2340 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2341 WHEN fnd_api.g_exc_unexpected_error THEN
2342 x_return_status := fnd_api.g_ret_sts_unexp_error;
2343 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2344 WHEN OTHERS THEN
2345 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2346 x_return_status := fnd_api.g_ret_sts_unexp_error;
2347 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2348 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2349 END IF;
2350 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2351 END update_trip_status;
2352
2353 /******************************************************************************************
2354 * *
2355 * Public Functions and Procedures dealing with a Single Trip *
2356 * *
2357 *******************************************************************************************/
2358 /**
2359 * Refer to the Package Spec for documentation of this procedure
2360 */
2361 PROCEDURE create_trip(
2362 p_api_version IN NUMBER
2363 , p_init_msg_list IN VARCHAR2
2364 , p_commit IN VARCHAR2
2365 , x_return_status OUT NOCOPY VARCHAR2
2366 , x_msg_data OUT NOCOPY VARCHAR2
2367 , x_msg_count OUT NOCOPY NUMBER
2368 , p_resource_id IN NUMBER
2369 , p_resource_type IN VARCHAR2
2370 , p_start_date_time IN DATE
2371 , p_end_date_time IN DATE
2372 , p_schedule_detail_id IN NUMBER
2373 , p_status IN NUMBER
2374 , p_find_tasks IN VARCHAR2
2375 , x_trip_id OUT NOCOPY NUMBER
2376 ) IS
2377 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TRIP';
2378 l_api_version CONSTANT NUMBER := 1.0;
2379 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2380
2381 l_trips trip_tbl_type;
2382 l_resource csf_resource_pub.resource_tbl_type;
2383 l_new_trip trip_rec_type;
2384
2385 l_shift_tasks_exist VARCHAR2(1);
2386
2387 -- Query for the existence of any Shift Task in the Trip Inteval for the Resource.
2388 CURSOR c_st_exist IS
2389 SELECT 'Y'
2390 FROM jtf_tasks_b t
2391 WHERE t.owner_id = p_resource_id
2392 AND t.owner_type_code = p_resource_type
2393 AND t.scheduled_start_date BETWEEN p_start_date_time AND p_end_date_time
2394 AND t.task_type_id IN (20, 21)
2395 AND NVL(t.deleted_flag, 'N') = 'N'
2396 AND ROWNUM = 1;
2397 BEGIN
2398 -- Check for API Compatibility
2399 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2400 RAISE fnd_api.g_exc_unexpected_error;
2401 END IF;
2402
2403 -- Initialize Message Stack if required
2404 IF p_init_msg_list = fnd_api.g_true THEN
2405 fnd_msg_pub.initialize;
2406 END IF;
2407
2408 -- Initialize Return Status
2409 x_return_status := fnd_api.g_ret_sts_success;
2410
2411 IF l_debug THEN
2412 debug('Creating a Trip for Resource#' || p_resource_id || ' between '
2413 || to_char(p_start_date_time, 'DD-MON-YYYY HH24:MI:SS') || ' and '
2414 || to_char(p_end_date_time, 'DD-MON-YYYY HH24:MI:SS'), l_api_name, fnd_log.level_procedure);
2415 END IF;
2416
2417 l_resource := csf_resource_pub.resource_tbl_type();
2418 l_resource.extend();
2419 l_resource(1).resource_id := p_resource_id;
2420 l_resource(1).resource_type := p_resource_type;
2421 l_trips := find_trips(l_resource, p_start_date_time, p_end_date_time);
2422
2423 -- Check#1 - No Trips should be found for the given criteria
2424 IF l_trips.COUNT > 0 THEN
2425 IF l_debug THEN
2426 debug(' Trips already exists for the Resource in the specified interval', l_api_name, fnd_log.level_error);
2427 END IF;
2428 fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_DUP');
2429 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
2430 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
2431 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
2432 fnd_msg_pub.ADD;
2433 RAISE fnd_api.g_exc_error;
2434 END IF;
2435
2436 -- Check#2 - No Shift Tasks in the Interval where the Trip is going to be created.
2437 IF l_debug THEN
2438 debug(' Searching for existence of any Shift Tasks in that interval', l_api_name, fnd_log.level_statement);
2439 END IF;
2440
2441 OPEN c_st_exist;
2442 FETCH c_st_exist INTO l_shift_tasks_exist;
2443 IF c_st_exist%NOTFOUND THEN
2444 l_shift_tasks_exist := 'N';
2445 END IF;
2446 CLOSE c_st_exist;
2447
2448 IF l_shift_tasks_exist = 'Y' THEN
2449 IF l_debug THEN
2450 debug(' Shift Tasks exist for the Resource in the specified interval', l_api_name, fnd_log.level_error);
2451 END IF;
2452 fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_ST_EXIST');
2453 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
2454 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
2455 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
2456 fnd_msg_pub.ADD;
2457 RAISE fnd_api.g_exc_error;
2458 END IF;
2459
2460 -- All validations passed. Create the Trip.
2461 new_trip(
2462 x_return_status => x_return_status
2463 , x_msg_data => x_msg_data
2464 , x_msg_count => x_msg_count
2465 , p_resource_id => p_resource_id
2466 , p_resource_type => p_resource_type
2467 , p_start_date_time => p_start_date_time
2468 , p_end_date_time => p_end_date_time
2469 , p_status => p_status
2470 , p_schedule_detail_id => p_schedule_detail_id
2471 , p_find_tasks => p_find_tasks
2472 , x_trip => l_new_trip
2473 );
2474
2475 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2476 fnd_message.set_name('CSF', 'CSF_TRIP_CREATE_FAIL_OTHER');
2477 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
2478 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
2479 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
2480 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
2481 fnd_msg_pub.ADD;
2482 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2483 RAISE fnd_api.g_exc_unexpected_error;
2484 END IF;
2485 RAISE fnd_api.g_exc_error;
2486 END IF;
2487
2488 x_trip_id := l_new_trip.trip_id;
2489
2490 IF fnd_api.to_boolean(p_commit) THEN
2491 COMMIT;
2492 END IF;
2493
2494 EXCEPTION
2495 WHEN fnd_api.g_exc_error THEN
2496 x_return_status := fnd_api.g_ret_sts_error;
2497 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2498 WHEN fnd_api.g_exc_unexpected_error THEN
2499 x_return_status := fnd_api.g_ret_sts_unexp_error;
2500 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2501 WHEN OTHERS THEN
2502 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2503 x_return_status := fnd_api.g_ret_sts_unexp_error;
2504 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2505 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2506 END IF;
2507 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2508 END create_trip;
2509
2510 /**
2511 * Refer to the Package Spec for documentation of this procedure
2512 */
2513 PROCEDURE update_trip(
2514 p_api_version IN NUMBER
2515 , p_init_msg_list IN VARCHAR2
2516 , p_commit IN VARCHAR2
2517 , x_return_status OUT NOCOPY VARCHAR2
2518 , x_msg_data OUT NOCOPY VARCHAR2
2519 , x_msg_count OUT NOCOPY NUMBER
2520 , p_trip_id IN NUMBER
2521 , p_object_version_number IN NUMBER
2522 , p_available_hours IN NUMBER
2523 , p_upd_available_hours IN NUMBER
2524 , p_available_hours_before IN NUMBER
2525 , p_available_hours_after IN NUMBER
2526 , p_status IN NUMBER
2527 ) IS
2528 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRIP';
2529 l_api_version CONSTANT NUMBER := 1.0;
2530 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2531
2532 l_trip trip_rec_type;
2533 BEGIN
2534 -- Check for API Compatibility
2535 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2536 RAISE fnd_api.g_exc_unexpected_error;
2537 END IF;
2538
2539 -- Initialize Message Stack if required
2540 IF fnd_api.to_boolean(p_init_msg_list) THEN
2541 fnd_msg_pub.initialize;
2542 END IF;
2543
2544 -- Initialize Return Status
2545 x_return_status := fnd_api.g_ret_sts_success;
2546
2547 IF p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num THEN
2548 -- Invalid Trip ID passed.
2549 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2550 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2551 fnd_message.set_token('PARAM_NAME', 'P_TRIP_ID');
2552 fnd_msg_pub.ADD;
2553 RAISE fnd_api.g_exc_error;
2554 END IF;
2555
2556 IF p_object_version_number IS NULL OR p_object_version_number = fnd_api.g_miss_num THEN
2557 -- Invalid Object Version Number passed.
2558 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2559 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2560 fnd_message.set_token('PARAM_NAME', 'P_OBJECT_VERSION_NUMBER');
2561 fnd_msg_pub.ADD;
2562 RAISE fnd_api.g_exc_error;
2563 END IF;
2564
2565 IF p_available_hours IS NOT NULL AND p_upd_available_hours IS NOT NULL THEN
2566 -- Error out as both cant be passed.
2567 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2568 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2569 fnd_message.set_token('PARAM_NAME', 'P_UPD_AVAILABLE_HOURS');
2570 fnd_msg_pub.ADD;
2571 RAISE fnd_api.g_exc_error;
2572 END IF;
2573
2574 l_trip := get_trip(p_trip_id);
2575 IF l_trip.trip_id IS NULL THEN
2576 fnd_message.set_name('CSF', 'CSF_INVALID_TRIP_ID');
2577 fnd_message.set_token('TRIP_ID', p_trip_id);
2578 fnd_msg_pub.ADD;
2579 RAISE fnd_api.g_exc_error;
2580 END IF;
2581
2582 change_trip(
2583 x_return_status => x_return_status
2584 , x_msg_data => x_msg_data
2585 , x_msg_count => x_msg_count
2586 , p_trip => l_trip
2587 , p_object_version_number => p_object_version_number
2588 , p_available_hours => p_available_hours
2589 , p_upd_available_hours => p_upd_available_hours
2590 , p_available_hours_before => p_available_hours_before
2591 , p_available_hours_after => p_available_hours_after
2592 , p_status => p_status
2593 );
2594
2595 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2596 fnd_message.set_name('CSF', 'CSF_TRIP_UPDATE_FAIL_OTHER');
2597 fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id, l_trip.resource_type));
2598 fnd_message.set_token('START_TIME', format_date(l_trip.start_date_time));
2599 fnd_message.set_token('END_TIME', format_date(l_trip.end_date_time));
2600 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
2601 fnd_msg_pub.ADD;
2602 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2603 RAISE fnd_api.g_exc_unexpected_error;
2604 END IF;
2605 RAISE fnd_api.g_exc_error;
2606 END IF;
2607
2608 IF fnd_api.to_boolean(p_commit) THEN
2609 COMMIT;
2610 END IF;
2611
2612 EXCEPTION
2613 WHEN fnd_api.g_exc_error THEN
2614 x_return_status := fnd_api.g_ret_sts_error;
2615 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2616 WHEN fnd_api.g_exc_unexpected_error THEN
2617 x_return_status := fnd_api.g_ret_sts_unexp_error;
2618 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2619 WHEN OTHERS THEN
2620 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2621 x_return_status := fnd_api.g_ret_sts_unexp_error;
2622 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2623 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2624 END IF;
2625 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2626 END;
2627
2628 /**
2629 * Refer to the Package Spec for documentation of this procedure
2630 */
2631 PROCEDURE delete_trip (
2632 p_api_version IN NUMBER
2633 , p_init_msg_list IN VARCHAR2
2634 , p_commit IN VARCHAR2
2635 , x_return_status OUT NOCOPY VARCHAR2
2636 , x_msg_data OUT NOCOPY VARCHAR2
2637 , x_msg_count OUT NOCOPY NUMBER
2638 , p_trip_id IN NUMBER
2639 , p_object_version_number IN NUMBER
2640 ) IS
2641 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TRIP';
2642 l_api_version CONSTANT NUMBER := 1.0;
2643 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2644 l_trip trip_rec_type;
2645 BEGIN
2646 SAVEPOINT delete_trip;
2647
2648 -- Check for API Compatibility
2649 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2650 RAISE fnd_api.g_exc_unexpected_error;
2651 END IF;
2652
2653 -- Initialize Message Stack if required
2654 IF fnd_api.to_boolean(p_init_msg_list) THEN
2655 fnd_msg_pub.initialize;
2656 END IF;
2657
2658 -- Initialize Return Status
2659 x_return_status := fnd_api.g_ret_sts_success;
2660
2661 IF l_debug THEN
2662 debug('Deleting the Trip #' || p_trip_id, l_api_name, fnd_log.level_procedure);
2663 END IF;
2664
2665 IF p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num THEN
2666 -- Invalid Trip ID passed.
2667 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2668 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2669 fnd_message.set_token('PARAM_NAME', 'P_TRIP_ID');
2670 fnd_msg_pub.ADD;
2671 RAISE fnd_api.g_exc_error;
2672 END IF;
2673
2674 IF p_object_version_number IS NULL OR p_object_version_number = fnd_api.g_miss_num THEN
2675 -- Invalid Object Version Number passed.
2676 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2677 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2678 fnd_message.set_token('PARAM_NAME', 'P_OBJECT_VERSION_NUMBER');
2679 fnd_msg_pub.ADD;
2680 RAISE fnd_api.g_exc_error;
2681 END IF;
2682
2683 l_trip := get_trip(p_trip_id);
2684 -- No Trips found for the given Trip ID
2685 IF l_trip.trip_id IS NULL THEN
2686 fnd_message.set_name('CSF', 'CSF_INVALID_TRIP_ID');
2687 fnd_message.set_token('TRIP_ID', p_trip_id);
2688 fnd_msg_pub.ADD;
2689 RAISE fnd_api.g_exc_error;
2690 END IF;
2691
2692 remove_trip(
2693 x_return_status => x_return_status
2694 , x_msg_data => x_msg_data
2695 , x_msg_count => x_msg_count
2696 , p_trip => l_trip
2697 , p_object_version_number => p_object_version_number
2698 );
2699
2700 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2701 IF l_debug THEN
2702 debug( ' Unable to delete the Trip: Error = '
2703 || fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false)
2704 , l_api_name, fnd_log.level_error);
2705 END IF;
2706 fnd_message.set_name('CSF', 'CSF_TRIP_DELETE_FAIL_OTHER');
2707 fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id, l_trip.resource_type));
2708 fnd_message.set_token('START_TIME', format_date(l_trip.start_date_time));
2709 fnd_message.set_token('END_TIME', format_date(l_trip.end_date_time));
2710 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
2711 fnd_msg_pub.ADD;
2712 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2713 RAISE fnd_api.g_exc_unexpected_error;
2714 END IF;
2715 RAISE fnd_api.g_exc_error;
2716 END IF;
2717
2718 IF fnd_api.to_boolean(p_commit) THEN
2719 COMMIT;
2720 END IF;
2721 EXCEPTION
2722 WHEN fnd_api.g_exc_error THEN
2723 ROLLBACK TO delete_trip;
2724 x_return_status := fnd_api.g_ret_sts_error;
2725 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2726 WHEN fnd_api.g_exc_unexpected_error THEN
2727 ROLLBACK TO delete_trip;
2728 x_return_status := fnd_api.g_ret_sts_unexp_error;
2729 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2730 WHEN OTHERS THEN
2731 ROLLBACK TO delete_trip;
2732 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2733 x_return_status := fnd_api.g_ret_sts_unexp_error;
2734 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2735 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2736 END IF;
2737 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2738 END delete_trip;
2739
2740 /**
2741 * Refer to the Package Spec for documentation of this procedure
2742 */
2743 PROCEDURE fix_trip(
2744 p_api_version IN NUMBER
2745 , p_init_msg_list IN VARCHAR2
2746 , p_commit IN VARCHAR2
2747 , x_return_status OUT NOCOPY VARCHAR2
2748 , x_msg_data OUT NOCOPY VARCHAR2
2749 , x_msg_count OUT NOCOPY NUMBER
2750 , p_trip_id IN NUMBER
2751 , p_object_version_number IN NUMBER
2752 ) IS
2753 l_api_name CONSTANT VARCHAR2(30) := 'FIX_TRIPS';
2754 l_api_version CONSTANT NUMBER := 1.0;
2755 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2756
2757 l_trip trip_rec_type;
2758 BEGIN
2759 -- Check for API Compatibility
2760 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2761 RAISE fnd_api.g_exc_unexpected_error;
2762 END IF;
2763
2764 -- Initialize Message Stack if required
2765 IF fnd_api.to_boolean(p_init_msg_list) THEN
2766 fnd_msg_pub.initialize;
2767 END IF;
2768
2769 -- Initialize Return Status
2770 x_return_status := fnd_api.g_ret_sts_success;
2771
2772 IF l_debug THEN
2773 debug('Fixing the Trip #' || p_trip_id, l_api_name, fnd_log.level_procedure);
2774 END IF;
2775
2776 IF p_trip_id IS NULL OR p_trip_id = fnd_api.g_miss_num THEN
2777 -- Invalid Trip ID passed.
2778 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2779 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2780 fnd_message.set_token('PARAM_NAME', 'P_TRIP_ID');
2781 fnd_msg_pub.ADD;
2782 RAISE fnd_api.g_exc_error;
2783 END IF;
2784
2785 IF p_object_version_number IS NULL OR p_object_version_number = fnd_api.g_miss_num THEN
2786 -- Invalid Object Version Number passed.
2787 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
2788 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
2789 fnd_message.set_token('PARAM_NAME', 'P_OBJECT_VERSION_NUMBER');
2790 fnd_msg_pub.ADD;
2791 RAISE fnd_api.g_exc_error;
2792 END IF;
2793
2794 l_trip := get_trip(p_trip_id);
2795 IF l_trip.trip_id IS NULL THEN
2796 fnd_message.set_name('CSF', 'CSF_INVALID_TRIP_ID');
2797 fnd_message.set_token('TRIP_ID', p_trip_id);
2798 fnd_msg_pub.ADD;
2799 RAISE fnd_api.g_exc_error;
2800 END IF;
2801
2802 correct_trip(
2803 x_return_status => x_return_status
2804 , x_msg_data => x_msg_data
2805 , x_msg_count => x_msg_count
2806 , p_trip => l_trip
2807 , p_object_version_number => p_object_version_number
2808 );
2809
2810 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2811 IF l_debug THEN
2812 debug(' Unable to fix the Trip', l_api_name, fnd_log.level_error);
2813 END IF;
2814 fnd_message.set_name('CSF', 'CSF_TRIP_FIX_FAIL_OTHER');
2815 fnd_message.set_token('RESOURCE', get_resource_info(l_trip.resource_id, l_trip.resource_type));
2816 fnd_message.set_token('START_TIME', format_date(l_trip.start_date_time));
2817 fnd_message.set_token('END_TIME', format_date(l_trip.end_date_time));
2818 fnd_message.set_token('REASON', fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false));
2819 fnd_msg_pub.ADD;
2820 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
2821 RAISE fnd_api.g_exc_unexpected_error;
2822 END IF;
2823 RAISE fnd_api.g_exc_error;
2824 END IF;
2825
2826 IF fnd_api.to_boolean(p_commit) THEN
2827 COMMIT;
2828 END IF;
2829 EXCEPTION
2830 WHEN fnd_api.g_exc_error THEN
2831 x_return_status := fnd_api.g_ret_sts_error;
2832 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2833 WHEN fnd_api.g_exc_unexpected_error THEN
2834 x_return_status := fnd_api.g_ret_sts_unexp_error;
2835 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2836 WHEN OTHERS THEN
2837 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2838 x_return_status := fnd_api.g_ret_sts_unexp_error;
2839 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2840 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2841 END IF;
2842 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2843 END fix_trip;
2844
2845 /******************************************************************************************
2846 * *
2847 * Public Functions and Procedures dealing generally on Trips *
2848 * *
2849 *******************************************************************************************/
2850
2851 /**
2852 * Refer to the Package Spec for documentation of this procedure
2853 */
2854 PROCEDURE find_trip(
2855 p_api_version IN NUMBER
2856 , p_init_msg_list IN VARCHAR2
2857 , x_return_status OUT NOCOPY VARCHAR2
2858 , x_msg_data OUT NOCOPY VARCHAR2
2859 , x_msg_count OUT NOCOPY NUMBER
2860 , p_resource_id IN NUMBER
2861 , p_resource_type IN VARCHAR2
2862 , p_start_date_time IN DATE
2863 , p_end_date_time IN DATE
2864 , p_overtime_flag IN VARCHAR2
2865 , x_trip OUT NOCOPY trip_rec_type
2866 ) IS
2867 l_api_name CONSTANT VARCHAR2(30) := 'FIND_TRIP';
2868 l_api_version CONSTANT NUMBER := 1.0;
2869 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2870 l_resource_tbl csf_resource_pub.resource_tbl_type;
2871 l_trips trip_tbl_type;
2872 BEGIN
2873 -- Check for API Compatibility
2874 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2875 RAISE fnd_api.g_exc_unexpected_error;
2876 END IF;
2877
2878 -- Initialize Message Stack if required
2879 IF fnd_api.to_boolean(p_init_msg_list) THEN
2880 fnd_msg_pub.initialize;
2881 END IF;
2882
2883 -- Initialize Return Status
2884 x_return_status := fnd_api.g_ret_sts_success;
2885
2886 l_resource_tbl := csf_resource_pub.resource_tbl_type();
2887 l_resource_tbl.extend();
2888 l_resource_tbl(1).resource_id := p_resource_id;
2889 l_resource_tbl(1).resource_type := p_resource_type;
2890
2891 l_trips := find_trips(l_resource_tbl, p_start_date_time, p_end_date_time, p_overtime_flag);
2892
2893 IF l_trips.COUNT = 0 OR l_trips.COUNT > 1 THEN
2894 IF l_trips.COUNT = 0 THEN
2895 fnd_message.set_name('CSF', 'CSF_NO_TRIPS_FOUND');
2896 ELSE
2897 fnd_message.set_name('CSF', 'CSF_MULTIPLE_TRIPS_FOUND');
2898 END IF;
2899 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
2900 fnd_message.set_token('START_TIME', format_date(p_start_date_time));
2901 fnd_message.set_token('END_TIME', format_date(p_end_date_time));
2902 fnd_msg_pub.add;
2903 RAISE fnd_api.g_exc_error;
2904 END IF;
2905
2906 x_trip := l_trips(l_trips.FIRST);
2907 EXCEPTION
2908 WHEN fnd_api.g_exc_error THEN
2909 x_return_status := fnd_api.g_ret_sts_error;
2910 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2911 WHEN fnd_api.g_exc_unexpected_error THEN
2912 x_return_status := fnd_api.g_ret_sts_unexp_error;
2913 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2914 WHEN OTHERS THEN
2915 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2916 x_return_status := fnd_api.g_ret_sts_unexp_error;
2917 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2918 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2919 END IF;
2920 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2921 END find_trip;
2922
2923 /**
2924 * Refer to the Package Spec for documentation of this procedure
2925 */
2926 PROCEDURE find_trip(
2927 p_api_version IN NUMBER
2928 , p_init_msg_list IN VARCHAR2
2929 , x_return_status OUT NOCOPY VARCHAR2
2930 , x_msg_data OUT NOCOPY VARCHAR2
2931 , x_msg_count OUT NOCOPY NUMBER
2932 , p_resource_id IN NUMBER
2933 , p_resource_type IN VARCHAR2
2934 , p_start_date_time IN DATE
2935 , p_end_date_time IN DATE
2936 , p_overtime_flag IN VARCHAR2
2937 , x_trip_id OUT NOCOPY NUMBER
2938 ) IS
2939 l_api_name CONSTANT VARCHAR2(30) := 'FIND_TRIP';
2940 l_api_version CONSTANT NUMBER := 1.0;
2941 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
2942 l_trip trip_rec_type;
2943 BEGIN
2944 -- Check for API Compatibility
2945 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2946 RAISE fnd_api.g_exc_unexpected_error;
2947 END IF;
2948
2949 -- Initialize Message Stack if required
2950 IF fnd_api.to_boolean(p_init_msg_list) THEN
2951 fnd_msg_pub.initialize;
2952 END IF;
2953
2954 -- Initialize Return Status
2955 x_return_status := fnd_api.g_ret_sts_success;
2956
2957 find_trip(
2958 p_api_version => p_api_version
2959 , p_init_msg_list => p_init_msg_list
2960 , x_return_status => x_return_status
2961 , x_msg_data => x_msg_data
2962 , x_msg_count => x_msg_count
2963 , p_resource_id => p_resource_id
2964 , p_resource_type => p_resource_type
2965 , p_start_date_time => p_start_date_time
2966 , p_end_date_time => p_end_date_time
2967 , p_overtime_flag => p_overtime_flag
2968 , x_trip => l_trip
2969 );
2970
2971 IF x_return_status = fnd_api.g_ret_sts_success THEN
2972 x_trip_id := l_trip.trip_id;
2973 END IF;
2974
2975 EXCEPTION
2976 WHEN fnd_api.g_exc_error THEN
2977 x_return_status := fnd_api.g_ret_sts_error;
2978 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2979 WHEN fnd_api.g_exc_unexpected_error THEN
2980 x_return_status := fnd_api.g_ret_sts_unexp_error;
2981 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2982 WHEN OTHERS THEN
2983 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
2984 x_return_status := fnd_api.g_ret_sts_unexp_error;
2985 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2986 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2987 END IF;
2988 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
2989 END find_trip;
2990
2991 /**
2992 * Refer to the Package Spec for documentation of this procedure
2993 */
2994 PROCEDURE find_trips(
2995 p_api_version IN NUMBER
2996 , p_init_msg_list IN VARCHAR2
2997 , x_return_status OUT NOCOPY VARCHAR2
2998 , x_msg_data OUT NOCOPY VARCHAR2
2999 , x_msg_count OUT NOCOPY NUMBER
3000 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
3001 , p_start_date_time IN DATE
3002 , p_end_date_time IN DATE
3003 , p_overtime_flag IN VARCHAR2
3004 , x_trips OUT NOCOPY trip_tbl_type
3005 ) IS
3006 l_api_name CONSTANT VARCHAR2(30) := 'FIND_TRIP';
3007 l_api_version CONSTANT NUMBER := 1.0;
3008 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3009 BEGIN
3010 -- Check for API Compatibility
3011 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3012 RAISE fnd_api.g_exc_unexpected_error;
3013 END IF;
3014
3015 -- Initialize Message Stack if required
3016 IF fnd_api.to_boolean(p_init_msg_list) THEN
3017 fnd_msg_pub.initialize;
3018 END IF;
3019
3020 -- Initialize Return Status
3021 x_return_status := fnd_api.g_ret_sts_success;
3022
3023 x_trips := find_trips(p_resource_tbl, p_start_date_time, p_end_date_time, p_overtime_flag);
3024
3025 EXCEPTION
3026 WHEN fnd_api.g_exc_error THEN
3027 x_return_status := fnd_api.g_ret_sts_error;
3028 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3029 WHEN fnd_api.g_exc_unexpected_error THEN
3030 x_return_status := fnd_api.g_ret_sts_unexp_error;
3031 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3032 WHEN OTHERS THEN
3033 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3034 x_return_status := fnd_api.g_ret_sts_unexp_error;
3035 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3036 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3037 END IF;
3038 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3039 END find_trips;
3040
3041 /**
3042 * Refer to the Package Spec for documentation of this procedure
3043 */
3044 PROCEDURE process_action(
3045 p_api_version IN NUMBER
3046 , p_init_msg_list IN VARCHAR2
3047 , p_commit IN VARCHAR2
3048 , x_return_status OUT NOCOPY VARCHAR2
3049 , x_msg_data OUT NOCOPY VARCHAR2
3050 , x_msg_count OUT NOCOPY NUMBER
3051 , p_action IN VARCHAR2
3052 , p_trip_id IN NUMBER
3053 , p_resource_tbl IN csf_resource_pub.resource_tbl_type
3054 , p_start_date IN DATE
3055 , p_end_date IN DATE
3056 ) IS
3057 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ACTION';
3058 l_api_version CONSTANT NUMBER := 1.0;
3059 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3060
3061 l_trips trip_tbl_type;
3062 l_trip trip_rec_type;
3063 l_param_name VARCHAR2(30);
3064
3065 BEGIN
3066 -- Check for API Compatibility
3067 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3068 RAISE fnd_api.g_exc_unexpected_error;
3069 END IF;
3070
3071 -- Initialize Message Stack if required
3072 IF fnd_api.to_boolean(p_init_msg_list) THEN
3073 fnd_msg_pub.initialize;
3074 END IF;
3075
3076 -- Initialize Return Status
3077 x_return_status := fnd_api.g_ret_sts_success;
3078
3079 IF l_debug THEN
3080 debug('Generating Resource Trips for a Resource', l_api_name, fnd_log.level_procedure);
3081 debug(' Action = ' || p_action, l_api_name, fnd_log.level_statement);
3082 IF p_trip_id IS NOT NULL THEN
3083 debug(' Trip ID = ' || p_trip_id, l_api_name, fnd_log.level_statement);
3084 END IF;
3085
3086 IF p_start_date IS NOT NULL THEN
3087 debug(' Time Frame = ' || format_date(p_start_date) || ' to ' || format_date(p_end_date), l_api_name, fnd_log.level_statement);
3088 END IF;
3089
3090 IF p_resource_tbl IS NOT NULL AND p_resource_tbl.COUNT = 1 THEN
3091 debug(' Resource = ' || p_resource_tbl(p_resource_tbl.FIRST).resource_id, l_api_name, fnd_log.level_statement);
3092 END IF;
3093 END IF;
3094
3095 -- Checking whether all required parameters are passed.
3096 IF p_action IN (g_action_block_trip, g_action_unblock_trip) THEN
3097 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
3098 IF (p_resource_tbl IS NOT NULL OR p_start_date IS NOT NULL OR p_end_date IS NOT NULL) THEN
3099 IF p_resource_tbl IS NULL THEN
3100 l_param_name := 'P_RESOURCE_TBL';
3101 ELSIF p_start_date IS NULL THEN
3102 l_param_name := 'P_START_DATE';
3103 ELSE
3104 l_param_name := 'P_END_DATE';
3105 END IF;
3106 ELSE
3107 l_param_name := 'P_TRIP_ID';
3108 END IF;
3109 END IF;
3110 ELSIF p_resource_tbl IS NULL THEN
3111 l_param_name := 'P_RESOURCE_TBL';
3112 ELSIF p_start_date IS NULL THEN
3113 l_param_name := 'P_START_DATE';
3114 ELSIF p_end_date IS NULL THEN
3115 l_param_name := 'P_END_DATE';
3116 END IF;
3117
3118 IF l_param_name IS NOT NULL THEN
3119 fnd_message.set_name('CSF', 'CSF_API_INVALID_PARAM');
3120 fnd_message.set_token('API_NAME', g_pkg_name || '.' || l_api_name);
3121 fnd_message.set_token('PARAM_NAME', l_param_name);
3122 fnd_msg_pub.ADD;
3123 RAISE fnd_api.g_exc_error;
3124 END IF;
3125
3126 -- Getting the Trips only for actions like DELETE, FIX, BLOCK, UNBLOCK.
3127 IF p_action NOT IN (g_action_create_trip, g_action_upgrade_trip, g_action_replace_trip) THEN
3128 IF p_trip_id IS NOT NULL THEN
3129 l_trips(1) := get_trip(p_trip_id);
3130 ELSE
3131 l_trips := find_trips(p_resource_tbl, p_start_date, p_end_date);
3132 END IF;
3133
3134 IF l_trips.COUNT = 0 THEN
3135 IF p_trip_id IS NOT NULL THEN
3136 fnd_message.set_name('CSF', 'CSF_INVALID_TRIP_ID');
3137 fnd_message.set_token('TRIP_ID', p_trip_id);
3138 ELSE
3139 fnd_message.set_name('CSF', 'CSF_NO_TRIPS_FOUND');
3140 IF p_resource_tbl.COUNT = 1 THEN
3141 fnd_message.set_token('RESOURCE', get_resource_info(
3142 p_resource_tbl(1).resource_id
3143 , p_resource_tbl(1).resource_type
3144 ));
3145 END IF;
3146 fnd_message.set_token('START_TIME', format_date(p_start_date));
3147 fnd_message.set_token('END_TIME', format_date(p_end_date));
3148 END IF;
3149 fnd_msg_pub.add;
3150 RAISE fnd_api.g_exc_error;
3151 END IF;
3152 END IF;
3153
3154 g_messages.DELETE;
3155 IF p_action IN (g_action_create_trip, g_action_replace_trip) THEN
3156 create_trips(
3157 x_return_status => x_return_status
3158 , x_msg_data => x_msg_data
3159 , x_msg_count => x_msg_count
3160 , p_resource_tbl => p_resource_tbl
3161 , p_start_date => p_start_date
3162 , p_end_date => p_end_date
3163 , p_delete_trips => (p_action = g_action_replace_trip)
3164 );
3165 ELSIF p_action = g_action_upgrade_trip THEN
3166 upgrade_to_trips(
3167 x_return_status => x_return_status
3168 , x_msg_data => x_msg_data
3169 , x_msg_count => x_msg_count
3170 , p_resource_tbl => p_resource_tbl
3171 , p_start_date => p_start_date
3172 , p_end_date => p_end_date
3173 );
3174 ELSIF p_action = g_action_delete_trip THEN
3175 delete_trips(
3176 x_return_status => x_return_status
3177 , x_msg_data => x_msg_data
3178 , x_msg_count => x_msg_count
3179 , p_trips => l_trips
3180 );
3181 ELSIF p_action = g_action_fix_trip THEN
3182 fix_trips(
3183 x_return_status => x_return_status
3184 , x_msg_data => x_msg_data
3185 , x_msg_count => x_msg_count
3186 , p_trips => l_trips
3187 );
3188 ELSIF p_action IN (g_action_block_trip, g_action_unblock_trip, g_action_close_trip) THEN
3189 update_trip_status(
3190 x_return_status => x_return_status
3191 , x_msg_data => x_msg_data
3192 , x_msg_count => x_msg_count
3193 , p_trip_action => p_action
3194 , p_trips => l_trips
3195 );
3196 END IF;
3197
3198 IF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3199 RAISE fnd_api.g_exc_unexpected_error;
3200 END IF;
3201
3202 process_messages(
3203 p_init_msg_list => p_init_msg_list
3204 , x_return_status => x_return_status
3205 , p_action => p_action
3206 , p_trip_id => p_trip_id
3207 , p_start_date => p_start_date
3208 , p_end_date => p_end_date
3209 , p_resource_tbl => p_resource_tbl
3210 );
3211
3212 IF fnd_api.to_boolean(p_commit) THEN
3213 COMMIT;
3214 END IF;
3215
3216 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3217 EXCEPTION
3218 WHEN fnd_api.g_exc_error THEN
3219 x_return_status := fnd_api.g_ret_sts_error;
3220 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3221 WHEN fnd_api.g_exc_unexpected_error THEN
3222 x_return_status := fnd_api.g_ret_sts_unexp_error;
3223 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3224 WHEN OTHERS THEN
3225 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3226 x_return_status := fnd_api.g_ret_sts_unexp_error;
3227 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3228 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3229 END IF;
3230 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3231 END process_action;
3232
3233 /******************************************************************************************
3234 * *
3235 * Functions and Procedures dealing with Generate Trips Concurrent Program *
3236 * *
3237 *******************************************************************************************/
3238
3239 PROCEDURE generate_trips(
3240 errbuf OUT NOCOPY VARCHAR2
3241 , retcode OUT NOCOPY VARCHAR2
3242 , p_action IN VARCHAR2
3243 , p_start_date IN VARCHAR2
3244 , p_num_days IN NUMBER
3245 , p_resource_type IN VARCHAR2
3246 , p_resource_id IN NUMBER
3247 ) IS
3248 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_TRIPS';
3249
3250 l_msg_data VARCHAR2(2000);
3251 l_msg_count NUMBER;
3252 l_return_status VARCHAR2(1);
3253 l_start_date DATE;
3254 l_end_date DATE;
3255 l_num_days NUMBER;
3256 l_resources_failed NUMBER;
3257 l_resources_success NUMBER;
3258 l_resource csf_resource_pub.resource_tbl_type;
3259 l_resource_info VARCHAR2(500);
3260 l_resource_id_tbl jtf_number_table;
3261 l_resource_type_tbl jtf_varchar2_table_100;
3262
3263 CURSOR c_resources IS
3264 SELECT resource_id, resource_type
3265 FROM csf_selected_resources_v
3266 ORDER BY resource_type, resource_id;
3267 BEGIN
3268 /******************* Concurrent Program Start Message *******************/
3269 fnd_message.set_name('CSF', 'CSF_GTR_CP_STARTED');
3270 debug(fnd_message.get, 'GENERATE_TRIPS', g_level_cp_output);
3271
3272 init_package;
3273 g_suppress_res_info := TRUE;
3274
3275 /************* Concurrent Program Input Parameters Validation *************/
3276
3277 -- Get the Start Date (with Timezone Conversions) from the passed Start Date
3278 IF p_start_date IS NOT NULL THEN
3279 l_start_date := fnd_date.canonical_to_date(p_start_date);
3280
3281 IF l_start_date < SYSDATE AND p_action IN (g_action_create_trip, g_action_replace_trip) THEN
3282 l_start_date := NULL;
3283 END IF;
3284 END IF;
3285
3286 IF l_start_date IS NULL THEN
3287 -- Get the System Date in Client Timezone
3288 l_start_date := csf_timezones_pvt.date_to_client_tz_date(SYSDATE);
3289 -- Convert the time to System Timezone
3290 l_start_date := csf_timezones_pvt.date_to_server_tz_date(TRUNC(l_start_date));
3291 END IF;
3292
3293 IF p_num_days IS NULL OR p_num_days <= 0 THEN
3294 l_num_days := fnd_profile.value('PLANSCOPE');
3295 IF l_num_days IS NULL OR l_num_days <=0 THEN
3296 l_num_days := 7;
3297 END IF;
3298 ELSE
3299 l_num_days := p_num_days;
3300 END IF;
3301
3302 IF p_action = g_action_close_trip THEN
3303 l_end_date := l_start_date;
3304 l_start_date := l_end_date - l_num_days + 1;
3305 ELSE
3306 l_end_date := l_start_date + l_num_days - 1;
3307 END IF;
3308
3309 -- End Date will be 00:00 hours of the Start Date. So making it 23:59.
3310 l_end_date := l_end_date + (g_secs_in_day - 1) / g_secs_in_day;
3311
3312 -- Concurrent Program Parameters
3313 IF p_resource_id IS NOT NULL AND p_resource_type IS NOT NULL THEN
3314 fnd_message.set_name('CSF', 'CSF_GTR_CP_PARAMS_RESOURCE');
3315 fnd_message.set_token('RESOURCE', get_resource_info(p_resource_id, p_resource_type));
3316
3317 l_resource_info := fnd_message.get;
3318 l_resource_id_tbl := jtf_number_table();
3319 l_resource_id_tbl.extend(1);
3320 l_resource_id_tbl(1) := p_resource_id;
3321
3322 l_resource_type_tbl := jtf_varchar2_table_100();
3323 l_resource_type_tbl.extend(1);
3324 l_resource_type_tbl(1) := p_resource_type;
3325 ELSE
3326 l_resource_info := '';
3327
3328 OPEN c_resources;
3329 FETCH c_resources BULK COLLECT INTO l_resource_id_tbl, l_resource_type_tbl;
3330 CLOSE c_resources;
3331 END IF;
3332
3333 fnd_message.set_name('CSF', 'CSF_GTR_CP_PARAMS');
3334 fnd_message.set_token('ACTION', p_action);
3335 fnd_message.set_token('START_DATE', l_start_date);
3336 fnd_message.set_token('END_DATE', l_end_date);
3337 fnd_message.set_token('RESOURCE_INFO', l_resource_info);
3338 debug(fnd_message.get, 'GENERATE_TRIPS', g_level_cp_output);
3339
3340 /********************* Concurrent Program Execution *********************/
3341 l_resources_failed := 0;
3342 l_resources_success := 0;
3343 IF l_resource_id_tbl IS NOT NULL THEN
3344 l_resource := csf_resource_pub.resource_tbl_type();
3345 l_resource.extend(1);
3346
3347 FOR i IN 1..l_resource_id_tbl.COUNT LOOP
3348 l_resource(1).resource_id := l_resource_id_tbl(i);
3349 l_resource(1).resource_type := l_resource_type_tbl(i);
3350
3351 l_resource_info := get_resource_info(l_resource(1).resource_id, l_resource(1).resource_type);
3352 fnd_message.set_name('CSF', 'CSF_RESOURCE_PROCESSED');
3353 fnd_message.set_token('RESOURCE', l_resource_info);
3354 debug(fnd_message.get, 'GEN_RESOURCE_TRIPS', g_level_cp_output);
3355
3356 process_action(
3357 p_api_version => 1.0
3358 , p_init_msg_list => fnd_api.g_true
3359 , p_commit => fnd_api.g_true
3360 , x_return_status => l_return_status
3361 , x_msg_data => l_msg_data
3362 , x_msg_count => l_msg_count
3363 , p_action => p_action
3364 , p_resource_tbl => l_resource
3365 , p_start_date => l_start_date
3366 , p_end_date => l_end_date
3367 );
3368
3369 -- Print all the messages encountered
3370 FOR i IN 1..l_msg_count LOOP
3371 debug(' ' || fnd_msg_pub.get(i, fnd_api.g_false), l_api_name, g_level_cp_output);
3372 END LOOP;
3373 debug(' ', l_api_name, g_level_cp_output);
3374
3375 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3376 l_resources_failed := l_resources_failed + 1;
3377 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3378 RAISE fnd_api.g_exc_unexpected_error;
3379 END IF;
3380 ELSE
3381 l_resources_success := l_resources_success + 1;
3382 END IF;
3383 END LOOP;
3384 END IF;
3385
3386 /**************** Concurrent Program Completion Message ****************/
3387
3388 debug(' ', '', g_level_cp_output);
3389
3390 IF l_resources_failed > 0 THEN
3391 retcode := 1;
3392 fnd_message.set_name('CSF', 'CSF_CP_DONE_WARNING');
3393 ELSE
3394 retcode := 0;
3395 fnd_message.set_name('CSF', 'CSF_CP_DONE_SUCCESS');
3396 END IF;
3397
3398 errbuf := fnd_message.get;
3399 debug(errbuf, l_api_name, g_level_cp_output);
3400
3401 debug(' ', '', g_level_cp_output);
3402 fnd_message.set_name('CSF', 'CSF_RESOURCES_DONE_SUCCESS');
3403 fnd_message.set_token('NUMBER', l_resources_success);
3404 debug(fnd_message.get, l_api_name, g_level_cp_output);
3405
3406 fnd_message.set_name('CSF', 'CSF_RESOURCES_DONE_FAILED');
3407 fnd_message.set_token('NUMBER', l_resources_failed);
3408 debug(fnd_message.get, l_api_name, g_level_cp_output);
3409
3410 fnd_message.set_name('CSF', 'CSF_RESOURCES_DONE_TOTAL');
3411 fnd_message.set_token('NUMBER', l_resources_success + l_resources_failed);
3412 debug(fnd_message.get, l_api_name, g_level_cp_output);
3413 EXCEPTION
3414 WHEN OTHERS THEN
3415 IF SQLERRM IS NOT NULL THEN
3416 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3417 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3418 debug(fnd_msg_pub.get(fnd_msg_pub.g_last, fnd_api.g_false), l_api_name, g_level_cp_output);
3419 END IF;
3420 END IF;
3421
3422 retcode := 2;
3423 fnd_message.set_name('CSF', 'CSF_CP_DONE_ERROR');
3424 errbuf := fnd_message.get;
3425 debug(errbuf, l_api_name, g_level_cp_output);
3426 END generate_trips;
3427
3428
3429 PROCEDURE optimize_across_trips(
3430 p_api_version IN NUMBER
3431 , p_init_msg_list IN VARCHAR2
3432 , p_commit IN VARCHAR2
3433 , x_return_status OUT NOCOPY VARCHAR2
3434 , x_msg_data OUT NOCOPY VARCHAR2
3435 , x_msg_count OUT NOCOPY NUMBER
3436 , x_conc_request_id OUT NOCOPY NUMBER
3437 , p_resource_tbl IN csf_requests_pvt.resource_tbl_type
3438 , p_start_date IN DATE
3439 , p_end_date IN DATE
3440 ) IS
3441 l_api_name CONSTANT VARCHAR2(30) := 'OPTIMIZE_ACROSS_TRIPS';
3442 l_api_version CONSTANT NUMBER := 1.0;
3443 l_debug CONSTANT BOOLEAN := g_debug = 'Y';
3444
3445 l_sched_request_id NUMBER DEFAULT NULL;
3446 l_conc_request_id NUMBER DEFAULT NULL;
3447 l_oat_string VARCHAR2(100) DEFAULT NULL;
3448 --
3449 l_resources_tbl csf_requests_pvt.resource_tbl_type;
3450 BEGIN
3451 -- Check for API Compatibility
3452 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3453 RAISE fnd_api.g_exc_unexpected_error;
3454 END IF;
3455
3456 -- Initialize Message Stack if required
3457 IF fnd_api.to_boolean(p_init_msg_list) THEN
3458 fnd_msg_pub.initialize;
3459 END IF;
3460
3461 -- Initialize Return Status
3462 x_return_status := fnd_api.g_ret_sts_success;
3463
3464 IF l_debug THEN
3465 debug('CSF_TRIPS_PUB.Optimize Across Trips', l_api_name, fnd_log.level_procedure);
3466 debug(' No of resources in list = ' || p_resource_tbl.COUNT, l_api_name, fnd_log.level_statement);
3467 debug(' Time Frame = ' || p_start_date || ' to ' || p_end_date, l_api_name, fnd_log.level_statement);
3468 END IF;
3469
3470 l_resources_tbl := p_resource_tbl;
3471 FOR i IN 1..l_resources_tbl.COUNT LOOP
3472 l_resources_tbl(i).planwin_start := p_start_date;
3473 l_resources_tbl(i).planwin_end := p_end_date;
3474 END LOOP;
3475
3476 -- create a scheduler request
3477 csf_requests_pvt.create_scheduler_request (
3478 p_api_version => 1.0
3479 , x_return_status => x_return_status
3480 , x_msg_count => x_msg_count
3481 , x_msg_data => x_msg_data
3482 , p_name => 'OptimizeAcrossTrips'
3483 , p_object_id => -1
3484 , p_resource_tbl => l_resources_tbl
3485 , x_request_id => l_sched_request_id
3486 );
3487
3488 -- Standard check of the return status for the API call
3489 IF x_return_status = fnd_api.g_ret_sts_error THEN
3490 RAISE fnd_api.g_exc_error;
3491 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3492 RAISE fnd_api.g_exc_unexpected_error;
3493 END IF;
3494
3495 fnd_message.set_name('CSR','OPTIMIZE_ACROSS_TRIPS');
3496 l_oat_string := fnd_message.get;
3497
3498 -- submit the concurrent request 'Optimize Across Trips'
3499 x_conc_request_id := fnd_request.submit_request (
3500 application => 'CSR'
3501 , program => 'OPTIMIZE_ACROSS_TRIPS'
3502 , sub_request => FALSE
3503 , argument1 => l_sched_request_id
3504 );
3505
3506 IF x_conc_request_id = 0 THEN
3507 -- FND_REQUEST.SUBMIT_REQUEST should have populated the Message Stack.
3508 x_return_status := fnd_api.g_ret_sts_error;
3509 RAISE fnd_api.g_exc_error;
3510 END IF;
3511
3512 -- needed to submit the request properly
3513 COMMIT;
3514
3515 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3516 EXCEPTION
3517 WHEN fnd_api.g_exc_error THEN
3518 ROLLBACK;
3519 x_return_status := fnd_api.g_ret_sts_error;
3520 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3521 WHEN fnd_api.g_exc_unexpected_error THEN
3522 ROLLBACK;
3523 x_return_status := fnd_api.g_ret_sts_unexp_error;
3524 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3525 WHEN OTHERS THEN
3526 ROLLBACK;
3527 debug('Fatal Exception occurred: Code = ' || SQLCODE || ' Error = ' || SQLERRM, l_api_name, fnd_log.level_unexpected);
3528 x_return_status := fnd_api.g_ret_sts_unexp_error;
3529 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3530 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3531 END IF;
3532 fnd_msg_pub.count_and_get(p_data => x_msg_data, p_count => x_msg_count);
3533 END optimize_across_trips;
3534
3535 BEGIN
3536 -- Package Initialization
3537 init_package;
3538 END csf_trips_pub;
3539