[Home] [Help]
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(
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;
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;
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
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;
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));
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;
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;
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;
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;
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:
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);
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;
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;
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;
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
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;
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;
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);
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;
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
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);
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;
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.
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
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
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
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;
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;
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);
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;
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
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
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;
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;
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;
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:
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;
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
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;
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
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;
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);
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;
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;
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;
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
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);
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 )
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
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
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
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
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;
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:
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
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
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';
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
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
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
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
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');
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;
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;
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
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
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;
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
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;
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);
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)
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
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
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:
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;
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;
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;
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
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
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
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;
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:
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);
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
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;
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
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;
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);
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;
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:
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;
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;
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:
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;
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
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;
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;
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
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
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');
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;
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;
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;
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;
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.
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;
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;
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
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;
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
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;
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
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;
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);
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;
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'
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
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));
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;
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;
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);
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);
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;
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);
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;
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'
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
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));
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;
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;
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);
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);
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;
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);
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:
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
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;
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:
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;
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);
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;
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';
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:
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;
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;
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
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:
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
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:
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:
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);
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;
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;
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
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
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;
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
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)
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'
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
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;
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;
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
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;
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
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;
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);
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;
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)
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
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(
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
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;
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);
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);
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;
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);
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;
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
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'
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
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));
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;
2334: <
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;
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);
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);
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;
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);
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
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
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
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 '
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
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(
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));
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;
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;
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:
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:
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
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;
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);
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);
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;
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);
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
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
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
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');
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');
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.
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');
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
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
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));
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;
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;
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:
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;
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
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;
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);
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);
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;
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);
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
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
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
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;
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');
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.
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');
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
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);
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));
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;
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;
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:
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;
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
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
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;
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
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);
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);
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
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
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
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;
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');
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.
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');
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
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
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');
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;
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;
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:
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;
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
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;
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);
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);
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;
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);
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
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
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
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;
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
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;
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);
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);
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;
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);
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
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
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
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
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
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;
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);
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);
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;
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);
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
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
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
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
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;
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);
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);
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;
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);
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
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
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
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);
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
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;
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(
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
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);
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;
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);
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);
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;
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);
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
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
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
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;
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;
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;
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;
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
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
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
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);
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;
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:
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');
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;
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
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;
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
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;
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
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);
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);