[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 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;
UPDATE wsh_transactions_history
SET transaction_status = 'ER'
WHERE entity_type = 'DLVY_INT'
AND entity_number = to_char(p_delivery_interface_id)
AND document_type = 'SA';
SAVEPOINT before_update_dlvy_det;
/* Patchset I: Update of Delivery Status to SA and line status to Y
is done before calling the 'delivery_interface_wrapper' API.
Reason: Unless the status is updated before hand, various attributes
are disabled by the 'get_disabled_list' api, which is called by
the group api for create-update of delivery details */
-- Need to update the delivery status to SA
-- 2394893. This change has been made as a part of this bug because of
-- the change made in the API Update_Dlvy_Status ( Autonomous Transaction ).
update wsh_new_deliveries
set status_code ='SA'
where delivery_id = l_delivery_id
and status_code IN ('SR','SC');
-- Update released status only for TPW because only this needs the update to staged status
-- For Carrier Manifesting systems, status would already be staged
-- and hence no update is needed for CMS
IF (nvl(l_warehouse_type, '!') = 'TPW') THEN
-- This update is only for transactions from TPW
UPDATE wsh_delivery_details
SET released_status = 'Y'
WHERE delivery_detail_id IN (
SELECT delivery_detail_id
FROM wsh_delivery_assignments_v
WHERE delivery_id = l_delivery_id)
AND released_status IN ('R', 'B', 'X')
AND container_flag = 'N'
RETURNING delivery_detail_id BULK COLLECT INTO l_detail_tab; -- Added for DBI Project;
WSH_INTEGRATION.DBI_Update_Detail_Log
(p_delivery_detail_id_tab => l_detail_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
RAISE others_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');