[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT delivery_id, name,
organization_id --AD Trip Consolidation heali
FROM wsh_new_del_interface
WHERE delivery_interface_id = p_delivery_interface_id
AND INTERFACE_ACTION_CODE ='94X_INBOUND';
SELECT name FROM wsh_new_deliveries
WHERE delivery_id = c_delivery_id;
SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details wdd
where wdd.delivery_detail_id = wda.delivery_detail_id
and wdd.container_flag = 'Y'
and wda.parent_delivery_detail_id IS NULL
and wda.delivery_id IS NOT NULL
and wda.delivery_id = l_delivery_id
MINUS
SELECT wdai.delivery_detail_id
FROM wsh_del_assgn_interface wdai,
wsh_del_details_interface wddi
where wddi.delivery_detail_interface_id = wdai.delivery_detail_interface_id
and wddi.container_flag = 'Y'
and wdai.parent_delivery_detail_id IS NULL
and wdai.delivery_interface_id IS NOT NULL
and wdai.delivery_interface_id = l_delivery_interface_id
AND WDAI.INTERFACE_ACTION_CODE = '94X_INBOUND'
AND WDDI.INTERFACE_ACTION_CODE = '94X_INBOUND';
SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments_v wda
WHERE parent_delivery_detail_id IS NOT NULL
START WITH wda.delivery_detail_id = l_del_detail_id
CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id;
SELECT requested_quantity, picked_quantity, container_flag
FROM wsh_delivery_details
WHERE delivery_detail_id = l_del_detail_id;
SELECT nvl(IGNORE_INBOUND_TRIP,'N')
FROM WSH_SHIPPING_PARAMETERS
WHERE organization_id = p_organization_id;
SELECT nvl(defer_interface,'N')
FROM WSH_GLOBAL_PARAMETERS;
create_update_trip_failed exception;
update_dlvy_status_failed exception;
others_update_dlvy_det exception;
others_create_update_trip exception;
SAVEPOINT before_update_dlvy_det;
p_action_code => 'UPDATE',
x_delivery_id => l_dummy,
x_return_status => l_return_status);
RAISE others_update_dlvy_det;
WSH_INTERFACE_COMMON_ACTIONS.Create_Update_Trip_For_Dlvy(
p_delivery_id =>l_delivery_id,
x_pickup_stop_id => l_pickup_stop_id,
x_dropoff_stop_id => l_dropoff_stop_id,
x_trip_id => l_trip_id,
x_return_status => l_return_status);
wsh_debug_sv.log (l_module_name, 'Return status from create-update-trip', l_return_status);
raise create_update_trip_failed;
-- 1. Need to update the txn. history record to success
-- Because all data(dlvy, details, trip, stop) have been successfully moved to base tables
UPDATE wsh_transactions_history
SET transaction_status = 'SC',
entity_number = l_delivery_name,
entity_type = 'DLVY'
WHERE entity_type = 'DLVY_INT'
AND entity_number = to_char(p_delivery_interface_id)
AND document_type = 'SA';
-- 2. Need to delete the records in interface tables
WSH_PROCESS_INTERFACED_PKG.Delete_Interface_Records(
p_delivery_interface_id => p_delivery_interface_id,
x_return_status => l_return_status);
wsh_debug_sv.log(l_module_name,'Return status after delete interface records', l_return_status);
WHEN create_update_trip_failed THEN
RAISE create_update_trip_failed;
WHEN update_dlvy_status_failed THEN
RAISE update_dlvy_status_failed;
RAISE others_create_update_trip;
ROLLBACK TO before_update_dlvy_det;
WHEN create_update_trip_failed THEN
FND_MESSAGE.SET_NAME('WSH', 'WSH_TRIP_PROCESS_ERROR');
ROLLBACK TO before_update_dlvy_det;
WSH_DEBUG_SV.logmsg(l_module_name,'create_update_trip_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:create_update_trip_failed');
WHEN update_dlvy_status_failed THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
ROLLBACK TO before_update_dlvy_det;
WSH_DEBUG_SV.logmsg(l_module_name,'update_dlvy_status_failed exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:update_dlvy_status_failed');
WHEN others_update_dlvy_det THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
ROLLBACK TO before_update_dlvy_det;
WSH_DEBUG_SV.logmsg(l_module_name,'others_update_dlvy_det exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:others_update_dlvy_det');
WHEN others_create_update_trip THEN
x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
ROLLBACK TO before_update_dlvy_det;
WSH_DEBUG_SV.logmsg(l_module_name,'others_create_update_trip exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:others_create_update_trip');