[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select dg.delivery_id, st.stop_sequence_number,st.trip_id
from wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
where st.stop_id = p_stop_id
and dg.delivery_id = dl.delivery_id
and st.stop_location_id = dl.initial_pickup_location_id
and st.stop_id = dg.pick_up_stop_id
AND nvl(dl.shipment_direction,'O') IN ('O','IO') -- J-IB-NPARIKH
AND dl.delivery_type = 'STANDARD' --sperera, MDC
and dl.status_code IN ( 'OP', 'PA', 'SA') ;
stop_ids.delete;
del_ids.delete;
l_exceptions_tab.delete; --Bugfix 4017720
PROCEDURE Check_Update_Stops (
p_stop_rows IN wsh_util_core.id_tab_type,
p_action IN VARCHAR2,
--tkt
p_caller IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR stop_info (l_stop_id NUMBER) IS
SELECT trip_id,
status_code
FROM wsh_trip_stops
WHERE stop_id = l_stop_id;
SELECT dl.delivery_id
FROM wsh_delivery_legs dg,
wsh_new_deliveries dl
WHERE dg.delivery_id = dl.delivery_id AND
dg.drop_off_stop_id = l_stop_id AND
dl.status_code IN ('OP','PA', 'SA') -- sperera 940/945
AND nvl(dl.shipment_direction,'O') IN ('O','IO');
SELECT 1 from dual
WHERE exists ( select 1
FROM wsh_delivery_legs dg,
wsh_new_deliveries dl
WHERE dg.delivery_id = dl.delivery_id
AND dg.pick_up_stop_id = l_stop_id
AND dl.status_code in ('OP','PA', 'SA') -- sperera 940/945
AND nvl(dl.shipment_direction,'O') IN ('O','IO'));
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_UPDATE_STOPS';
FND_MESSAGE.SET_NAME('WSH','WSH_STOP_UPDATE_SAME_TRIP');
wsh_util_core.default_handler('WSH_TRIP_STOPS_ACTIONS.CHECK_UPDATE');
END Check_Update_Stops;
UPDATE wsh_trip_stops
SET status_code = 'AR',
actual_arrival_date = nvl(p_actual_date, SYSDATE)
WHERE stop_id IN (p_stop_rows(i), l_linked_stop_id) --wr
RETURNING stop_id BULK COLLECT INTO l_stop_tab; -- Added for DBI Project;
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
SELECT dg.delivery_id
FROM wsh_delivery_legs dg
WHERE dg.pick_up_stop_id = l_stop_id;
select st.status_code, tr.status_code ,
NVL(st.shipments_type_flag,'O') -- J-IB-NPARIKH
from wsh_trip_stops st, wsh_trips tr
where st.stop_id = l_stop_id
and st.trip_id = tr.trip_id;
select trip_id into dummy_trip_ids(i)
from wsh_trip_stops where stop_id=p_stop_rows(i);
l_trip_tab.delete;
UPDATE wsh_new_deliveries
SET ship_method_code = p_in_rec1.ship_method_code,
carrier_id = p_in_rec1.carrier_id,
service_level = p_in_rec1.service_level,
mode_of_transport = p_in_rec1.mode_of_transport,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_id = l_dlvy_id_tbl(i)
AND NVL(ship_method_code,' ') <> p_in_rec1.ship_method_code;
UPDATE wsh_trip_stops
SET pending_interface_flag = DECODE(l_cnt,0,pending_interface_flag,'Y'),
status_code = 'CL',
actual_departure_date = NVL(p_in_rec.actual_date, SYSDATE),
actual_arrival_date = NVL(actual_arrival_date,NVL(p_in_rec.actual_date,SYSDATE)), -- Bug 3901377
departure_seal_code = NVL(departure_seal_code,p_in_rec1.trip_seal_code),
tms_interface_flag = l_tms_interface_flag, --OTM R12 Org-Specific.
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE stop_id IN (p_in_rec.stop_id,
p_in_rec1.linked_stop_id) --wr
RETURNING stop_id BULK COLLECT INTO l_stop_tab; -- Added for DBI Project
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
SELECT stop_id, stop_sequence_number,
NVL(shipments_type_flag,'O') shipments_type_flag
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
AND stop_sequence_number > p_stop_sequence_number
AND NVL(shipments_type_flag,'O') IN ( 'I','M')
AND status_code = 'CL'
ORDER BY stop_sequence_number;
SELECT 1
FROM wsh_delivery_legs wdl,
wsh_new_deliveries wnd
WHERE wdl.pick_up_stop_id = p_pu_stop_id
AND wdl.drop_off_stop_id = p_do_stop_id
AND wdl.delivery_id = wnd.delivery_id
AND NVL(wnd.shipment_direction,'O') NOT IN ('O','IO')
AND rownum = 1;
SELECT wnd.delivery_id, wts.stop_location_id, wnd.ultimate_dropoff_location_id
FROM wsh_delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trip_stops wts
WHERE wdl.drop_off_stop_id = p_stop_id
AND wts.stop_id = p_stop_id
AND wdl.delivery_id = wnd.delivery_id
AND NVL(wnd.shipment_direction,'O') NOT IN ('O','IO');
SELECT next_leg_do_stop.status_code do_stop_statusCode,
NVL(next_leg_do_stop.shipments_type_flag,'O') do_stop_shipTypeFlag,
next_leg_do_stop.stop_location_id do_stop_locationId,
next_leg_do_stop.stop_id do_stop_id,
next_leg_pu_stop.status_code pu_stop_statusCode,
NVL(next_leg_pu_stop.shipments_type_flag,'O') pu_stop_shipTypeFlag,
next_leg_pu_stop.stop_location_id pu_stop_locationId,
next_leg_pu_stop.stop_id pu_stop_id,
NVL(wnd.shipment_direction,'O') shipment_direction,
wnd.status_code dlvy_status_code,
wnd.ultimatE_dropoff_location_id dlvy_ultimate_doLocationId
FROM wsh_trip_stops next_leg_do_stop,
wsh_trip_stops next_leg_pu_stop,
wsh_trip_stops curr_leg_do_stop,
wsh_delivery_legs next_leg,
wsh_delivery_legs curr_leg,
wsh_new_deliveries wnd
WHERE next_leg.drop_off_stop_id = next_leg_do_stop.stop_id
--AND st1.status_code = 'OP'
AND next_leg.pick_up_stop_id = next_leg_pu_stop.stop_id
AND next_leg_pu_stop.stop_location_id = curr_leg_do_stop.stop_location_id
AND next_leg.delivery_id = curr_leg.delivery_id
AND curr_leg_do_stop.stop_id = p_stop_id
AND curr_leg.drop_off_stop_id = p_stop_id
AND wnd.delivery_id = curr_leg.delivery_id
AND wnd.delivery_id = p_delivery_id;
UPDATE wsh_trip_stops
SET status_code = 'OP',
actual_departure_date = NULL,
actual_arrival_date = NULL, -- Bug 3901377
--actual_departure_date = nvl(p_in_rec.actual_date, SYSDATE),
--departure_seal_code = NVL(p_in_rec1.trip_seal_code, departure_seal_code),
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE stop_id = p_in_rec.stop_id
RETURNING stop_id BULK COLLECT INTO l_stop_tab; -- Added for DBI Project;
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
SELECT max(stop_sequence_number)
FROM wsh_trip_stops
WHERE trip_id = c_trip_id
AND status_code in ('AR', 'CL');
SELECT planned_arrival_date,
stop_id,
physical_stop_id -- SSN change
FROM wsh_trip_stops
WHERE trip_id = c_trip_id
and status_code = 'OP'
and (stop_sequence_number > c_sequence_number -- Update existing stops
OR stop_sequence_number < 1) -- Newly created stops
FOR update nowait
ORDER BY 1, 2;
l_stop_rec_tab.delete;
update wsh_trip_stops set stop_sequence_number = l_max_seq_number
where stop_id = l_stop_rec_tab(i).stop_id;
WSH_DEBUG_SV.logmsg(l_module_name,'updated stop '||l_stop_rec_tab(i).stop_id||' with sequence number '||to_char(l_max_seq_number));
SELECT stop_id
FROM ( SELECT wts.stop_id
FROM wsh_trip_stops wts
WHERE wts.trip_id = p_trip_id
AND wts.status_code IN ( 'OP', 'AR')
AND EXISTS ( SELECT 'c'
FROM wsh_delivery_legs wdl
WHERE wdl.pick_up_stop_id = wts.stop_id)
ORDER BY wts.stop_sequence_number DESC)
WHERE ROWNUM = 1;
SELECT 1
FROM wsh_trip_stops wts
,wsh_delivery_legs wdl
,wsh_new_deliveries wnd
WHERE wts.trip_id = p_trip_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wdl.delivery_id = wnd.delivery_id
AND EXISTS ( SELECT 1
FROM wsh_shipping_parameters wsp
WHERE wnd.organization_id = wsp.organization_id
AND wsp.otm_enabled = 'Y'
)
AND NOT EXISTS ( SELECT 1
FROM wsh_trip_stops wts
,wsh_delivery_legs wdl
,wsh_new_deliveries wnd
WHERE wts.trip_id = p_trip_id
AND wts.stop_id = wdl.pick_up_stop_id
AND wdl.delivery_id = wnd.delivery_id
AND ( EXISTS ( SELECT 1
FROM mtl_parameters mtlp
WHERE mtlp.organization_id = wnd.organization_id
AND mtlp.distributed_organization_flag = 'Y')
OR wnd.delivery_type = 'CONSOLIDATION'))
AND ROWNUM = 1;