[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sc_SELECT VARCHAR2(2000);
SELECT t.id, d.ignore_for_planning
FROM wsh_tmp t,
wsh_new_deliveries d
WHERE d.delivery_id = t.id
AND NOT EXISTS (
SELECT WDA.delivery_detail_id
FROM wsh_delivery_assignments_v WDA,
wsh_delivery_details WDD
WHERE WDA.delivery_detail_id = WDD.delivery_detail_id
AND WDA.delivery_id = t.id
AND WDA.delivery_id is not NULL
AND WDD.source_code <> 'OE'
AND wdd.container_flag = 'N')
AND NOT EXISTS (
-- deliveries in consolidations are ineligible
SELECT 1
FROM WSH_DELIVERY_LEGS WDL
WHERE WDL.delivery_id = t.id
AND WDL.parent_delivery_leg_id IS NOT NULL
)
ORDER BY d.creation_date;
l_tms_update VARCHAR2(1);
l_cc_in_ids.delete;
l_sc_SELECT := NULL;
l_cc_line_groups.delete;
l_cc_group_ids.delete;
l_group_info.delete;
l_attr_tab.delete;
l_details_in_cc_group.delete;
l_tms_update := 'N';
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED)) THEN
l_tms_update := 'Y';
l_new_interface_flag_tab(1) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
p_line_rows.delete(j);
WSH_DEBUG_SV.log(l_module_name, 'l_tms_update', l_tms_update);
IF (l_tms_update = 'Y') THEN
WSH_DEBUG_SV.log(l_module_name, 'l_new_interface_flag_tab', l_new_interface_flag_tab(1));
UPDATE wsh_new_deliveries
SET intmed_ship_to_location_id = NVL(intmed_ship_to_location_id,l_attr_tab(1).intmed_ship_to_location_id),
fob_code = NVL(fob_code,l_attr_tab(1).fob_code),
freight_terms_code = NVL(freight_terms_code,l_attr_tab(1).freight_terms_code),
ship_method_code = NVL(ship_method_code,l_attr_tab(1).ship_method_code),
carrier_id = NVL(carrier_id,l_attr_tab(1).carrier_id),
initial_pickup_date = least(initial_pickup_date, l_date_scheduled),
-- bug 2466054 - switch between date_scheduled and date_requested
ultimate_dropoff_date = greatest(least(initial_pickup_date, l_date_scheduled),
least(ultimate_dropoff_date,l_date_requested)),
service_level = NVL(service_level,l_attr_tab(1).service_level),
mode_of_transport = NVL(mode_of_transport,l_attr_tab(1).mode_of_transport),
source_header_id = NVL(source_header_id, l_attr_tab(1).source_header_id),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
-- OTM R12
TMS_INTERFACE_FLAG = decode(l_tms_update, 'Y', l_new_interface_flag_tab(1), nvl(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
TMS_VERSION_NUMBER = decode(l_tms_update, 'Y', l_tms_version_number, nvl(tms_version_number, 1))
-- End of OTM R12
WHERE delivery_id = l_delivery_id;
IF (l_gc3_is_installed = 'Y' AND l_tms_update = 'Y') THEN
WSH_XC_UTIL.LOG_OTM_EXCEPTION(
p_delivery_info_tab => l_delivery_info_tab,
p_new_interface_flag_tab => l_new_interface_flag_tab,
x_return_status => l_otm_return_status);
l_cc_group_ids.delete(i);
p_line_rows.delete(j);
l_details_in_cc_group.delete(k);
WSH_DEBUG_SV.log(l_module_name, 'l_tms_update', l_tms_update);
IF (l_tms_update = 'Y') THEN
WSH_DEBUG_SV.log(l_module_name, 'l_new_interface_flag_tab', l_new_interface_flag_tab(1));
UPDATE wsh_new_deliveries
SET intmed_ship_to_location_id = NVL(intmed_ship_to_location_id,l_attr_tab(1).intmed_ship_to_location_id),
fob_code = NVL(fob_code,l_attr_tab(1).fob_code),
freight_terms_code = NVL(freight_terms_code,l_attr_tab(1).freight_terms_code),
ship_method_code = NVL(ship_method_code,l_attr_tab(1).ship_method_code),
carrier_id = NVL(carrier_id,l_attr_tab(1).carrier_id),
initial_pickup_date = least(initial_pickup_date,l_date_scheduled),
-- bug 2466054 - switch between date_scheduled and date_requested
ultimate_dropoff_date = greatest(least(initial_pickup_date,l_date_scheduled),
least(ultimate_dropoff_date,l_date_requested)),
service_level = NVL(service_level,l_attr_tab(1).service_level),
mode_of_transport = NVL(mode_of_transport,l_attr_tab(1).mode_of_transport),
source_header_id = NVL(source_header_id, l_attr_tab(1).source_header_id),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id,
-- OTM R12
TMS_INTERFACE_FLAG = decode(l_tms_update, 'Y', l_new_interface_flag_tab(1), nvl(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
TMS_VERSION_NUMBER = decode(l_tms_update, 'Y', l_tms_version_number, nvl(tms_version_number, 1))
-- End of OTM R12
WHERE delivery_id = l_delivery_id;
IF (l_gc3_is_installed = 'Y' AND l_tms_update = 'Y') THEN
WSH_XC_UTIL.LOG_OTM_EXCEPTION(
p_delivery_info_tab => l_delivery_info_tab,
p_new_interface_flag_tab => l_new_interface_flag_tab,
x_return_status => l_otm_return_status);
l_cc_group_ids.delete(i);
p_line_rows.delete(i);
l_cc_group_ids.delete(i);
p_line_rows.delete(j);
l_cc_group_ids.delete(i);
SELECT wdd.delivery_detail_id,
wdd.ship_to_location_id,
wdd.ship_from_location_id,
wdd.customer_id,
wdd.intmed_ship_to_location_id,
wdd.fob_code,
wdd.freight_terms_code,
wdd.ship_method_code,
wdd.carrier_id,
wdd.source_header_id,
wdd.deliver_to_location_id,
wdd.organization_id,
wdd.date_scheduled,
wdd.date_requested,
wdd.released_status,
wdd.container_flag,
wdd.shipping_control,
wdd.party_id,
wdd.line_direction,
wdd.inventory_item_id,
wdd.source_code,
wdd.lpn_id,
wsp.appending_limit,
wdd.ignore_for_planning --bugfix 7164767
FROM wsh_delivery_details wdd,
wsh_delivery_assignments_v wda,
wsh_shipping_parameters wsp
WHERE wdd.delivery_detail_id = c_detail_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id is NULL
AND NVL(wdd.line_direction, 'O') in ('O', 'IO')
AND wdd.source_code = 'OE'
AND wdd.container_flag = 'N'
AND wsp.organization_id = wdd.organization_id;
x_unappended_det_tbl.delete; -- unappended list
x_appended_det_tbl.delete; -- successfully appended list
x_appended_del_tbl.delete; -- appended deliveries
l_detail_info.delete(l_index);
select delivery_detail_id into l_delivery_detail_id from wsh_delivery_details where
delivery_detail_id = p_delivery_detail_tbl(l_index) for update nowait;
l_detail_info.delete(l_index);
l_unassigned_rows.delete;