[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select delivery_leg_id
from wsh_delivery_legs l, wsh_trip_stops s
where s.trip_id = p_trip_id
and l.delivery_id = p_delivery_id
and s.stop_id = l.pick_up_stop_id
and s.stop_location_id = p_pickup_loc_id;
SELECT status_code ,
NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG, -- J-IB-NPARIKH
stop_location_id
FROM wsh_trip_stops
WHERE stop_id = l_trip_stop_id;
SELECT initial_pickup_location_id,
initial_pickup_date,
ultimate_dropoff_location_id,
ultimate_dropoff_date,
organization_id,
status_code,
nvl(shipment_direction,'O') shipment_direction, -- J-IB-NPARIKH
-- J: W/V Changes
gross_weight,
net_weight,
volume,
mode_of_transport,
freight_terms_code,
name,
customer_id
FROM wsh_new_deliveries
WHERE delivery_id = del_id;
select status_code, planned_flag,
NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG, -- J-IB-NPARIKH
mode_of_transport
from wsh_trips
where trip_id = c_trip_id
FOR UPDATE NOWAIT;
SELECT dg.delivery_leg_id,
st1.stop_location_id,
st2.stop_location_id
FROM wsh_trip_stops st1,
wsh_trip_stops st2,
wsh_delivery_legs dg
WHERE st1.stop_id = dg.pick_up_stop_id AND
st2.stop_id = dg.drop_off_stop_id AND
st1.trip_id = p_trip_id AND
st2.trip_id = p_trip_id AND
dg.delivery_id = del_id
FOR UPDATE NOWAIT;
SELECT stop_id, stop_sequence_number,
NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG -- J-IB-NPARIKH
FROM wsh_trip_stops
WHERE stop_location_id = l_location_id AND
trip_id = p_trip_id AND
status_code <> 'CL'
AND PLANNED_ARRIVAL_DATE = NVL(l_PLANNED_ARRIVAL_DATE,PLANNED_ARRIVAL_DATE)
AND PLANNED_DEPARTURE_DATE = NVL(l_PLANNED_DEPARTURE_DATE,PLANNED_DEPARTURE_DATE)
FOR UPDATE NOWAIT;
SELECT stop_id, stop_sequence_number,
NVL(SHIPMENTS_TYPE_FLAG,'O') SHIPMENTS_TYPE_FLAG -- J-IB-NPARIKH
FROM wsh_trip_stops
WHERE stop_location_id = l_location_id AND
trip_id = p_trip_id AND
stop_sequence_number = nvl(l_stop_sequence,stop_sequence_number) AND
status_code <> 'CL'
FOR UPDATE NOWAIT;
SELECT max(dg.sequence_number)
FROM wsh_delivery_legs dg
WHERE dg.delivery_id = del_id;
SELECT stop_sequence_number
FROM wsh_trip_stops
WHERE stop_id = l_stop_id;
SELECT 'Y'
FROM wsh_trip_stops wts1, wsh_trip_stops wts2
WHERE wts1.trip_id=p_trip_id
AND wts2.trip_id=p_trip_id
AND wts1.stop_id<>wts2.stop_id
AND wts1.stop_id=p_stop_id
AND ((wts2.physical_location_id=wts1.stop_location_id AND wts2.physical_stop_id IS NULL)
OR (wts1.physical_location_id=wts2.stop_location_id AND wts1.physical_stop_id IS NULL)
);
select id,rownum*10
from(
select id
from wsh_tmp
order by to_date(column1,'DD-MM-RRRR HH24:MI:SS'),flag desc
);
select wsh_wv_utils.convert_uom( WEIGHT_UOM_CODE, p_wt_uom, DEPARTURE_GROSS_WEIGHT, null) gross_weight,
wsh_wv_utils.convert_uom( WEIGHT_UOM_CODE, p_wt_uom, DEPARTURE_NET_WEIGHT, null) net_weight,
wsh_wv_utils.convert_uom( VOLUME_UOM_CODE, p_vol_uom, DEPARTURE_VOLUME, null) volume
from wsh_trip_stops
where trip_id = p_trip_id
and stop_sequence_number < p_stop_seq
order by stop_sequence_number desc;
select 'x'
from wsh_trip_stops
where trip_id = p_trip_id
and stop_sequence_number > p_stop_seq;
DELETE FROM WSH_TMP;
INSERT INTO wsh_tmp(
ID,
FLAG,
COLUMN1)
SELECT l_pickup_location_id_tbl(i), 'P', to_char(nvl(p_sc_pickup_date,l_sysdate), 'DD-MM-RRRR HH24:MI:SS')
FROM dual
WHERE NOT EXISTS (
select 'x'
from wsh_tmp wt1
where wt1.id = l_pickup_location_id_tbl(i)
and wt1.flag='P'
and wt1.column1 = to_char(nvl(p_sc_pickup_date,l_sysdate), 'DD-MM-RRRR HH24:MI:SS'))
UNION
SELECT l_dropoff_location_id_tbl(i), 'D', to_char(nvl(p_sc_dropoff_date,l_sysdate), 'DD-MM-RRRR HH24:MI:SS')
FROM dual
WHERE NOT EXISTS (
select 'x'
from wsh_tmp wt1
where wt1.id = l_dropoff_location_id_tbl(i)
and wt1.flag='D'
and wt1.column1 = to_char(nvl(p_sc_dropoff_date,l_sysdate), 'DD-MM-RRRR HH24:MI:SS'));
INSERT INTO wsh_tmp(
ID,
FLAG,
COLUMN1)
SELECT l_pickup_location_id_tbl(i), 'P', to_char(nvl(l_pickup_date_tbl(i),l_sysdate), 'DD-MM-RRRR HH24:MI:SS')
FROM dual
WHERE NOT EXISTS (
select 'x'
from wsh_tmp wt1
where wt1.id = l_pickup_location_id_tbl(i)
and wt1.flag='P'
and wt1.column1 = to_char(nvl(l_pickup_date_tbl(i),l_sysdate), 'DD-MM-RRRR HH24:MI:SS'))
UNION
SELECT l_dropoff_location_id_tbl(i), 'D', to_char(nvl(l_dropoff_date_tbl(i),l_sysdate), 'DD-MM-RRRR HH24:MI:SS')
FROM dual
WHERE NOT EXISTS (
select 'x'
from wsh_tmp wt1
where wt1.id = l_dropoff_location_id_tbl(i)
and wt1.flag='D'
and wt1.column1 = to_char(nvl(l_dropoff_date_tbl(i),l_sysdate), 'DD-MM-RRRR HH24:MI:SS'));
DELETE FROM wsh_tmp wt1
WHERE wt1.flag = 'P'
AND exists (
select 'x'
from wsh_tmp wt2
where wt1.id = wt2.id
and wt2.flag = 'P'
and to_date(wt2.column1,'DD-MM-RRRR HH24:MI:SS') < to_date(wt1.column1,'DD-MM-RRRR HH24:MI:SS'));
DELETE FROM wsh_tmp wt1
WHERE wt1.flag = 'D'
AND exists (
select 'x'
from wsh_tmp wt2
where wt1.id = wt2.id
and wt2.flag = 'D'
and to_date(wt2.column1,'DD-MM-RRRR HH24:MI:SS') > to_date(wt1.column1,'DD-MM-RRRR HH24:MI:SS'));
UPDATE wsh_tmp
set column2 = l_seq(i)
WHERE id = l_id(i);
select column2, to_date(column1,'DD-MM-RRRR HH24:MI:SS')
into l_pickup_stop_seq,l_tmp_date
from wsh_tmp
where id = l_pickup_location_id
and flag = 'P';
p_action => 'UPDATE',
x_return_status => l_return_status);
update wsh_trip_stops
set planned_departure_date=least(nvl(l_pickup_date,sysdate),planned_departure_date),
planned_arrival_date=least(nvl(l_pickup_date,sysdate),planned_arrival_date),
shipments_type_flag = l_stop_shipments_type_flag , -- J-IB-NPARIKH
last_update_date = SYSDATE, -- J-IB-NPARIKH
last_updated_by = FND_GLOBAL.USER_ID, -- J-IB-NPARIKH
last_update_login = FND_GLOBAL.LOGIN_ID -- J-IB-NPARIKH
where stop_id=l_pickup_stop_id;
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 column2,to_date(column1,'DD-MM-RRRR HH24:MI:SS')
into l_dropoff_stop_seq,l_tmp_date
from wsh_tmp
where id = l_dropoff_location_id
and flag = 'D';
p_action => 'UPDATE',
x_return_status => l_return_status);
update wsh_trip_stops
set shipments_type_flag = l_stop_shipments_type_flag , -- J-IB-NPARIKH
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where stop_id=l_dropoff_stop_id;
update wsh_delivery_legs
set pick_up_stop_id = l_pickup_stop_id
where delivery_leg_id = l_leg_id;
l_stop_tab.delete;
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
update wsh_delivery_legs
set drop_off_stop_id = l_dropoff_stop_id
where delivery_leg_id = l_leg_id;
l_stop_tab.delete;
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
update wsh_trip_stops
set planned_departure_date=p_pickup_dep_date,
planned_arrival_date=p_pickup_arr_date,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where stop_id=p_pickup_stop_id;
l_stop_tab.delete;
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
update wsh_trip_stops
set planned_departure_date=p_dropoff_dep_date,
planned_arrival_date=p_dropoff_arr_date,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where stop_id=p_dropoff_stop_id;
l_stop_tab.delete;
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_stop_tab,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
p_action => 'UPDATE',
x_return_status => l_return_status);
update wsh_trip_stops
set shipments_type_flag = l_pu_stop_shipments_type_flag, -- J-IB-NPARIKH
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where stop_id=l_pickup_stop_id;
p_action => 'UPDATE',
x_return_status => l_return_status);
update wsh_trip_stops
set shipments_type_flag = l_do_stop_shipments_type_flag, -- J-IB-NPARIKH
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
where stop_id=l_dropoff_stop_id;
UPDATE WSH_TRIPS
SET shipments_type_flag = l_trip_shipments_type_flag_new, -- 'M',
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE trip_id = p_trip_id;
UPDATE WSH_TRIP_STOPS
SET shipments_type_flag = l_trip_shipments_type_flag_new,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE trip_id = p_trip_id
and shipments_type_flag <> l_trip_shipments_type_flag_new;
p_update_del_flag => 'Y',
p_update_leg_flag => 'N',
x_leg_complete => l_leg_complete,
x_return_status => l_return_status);
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE pick_up_stop_id = p_pickup_stop_id
FOR UPDATE NOWAIT;
SELECT delivery_leg_id
FROM wsh_delivery_legs
WHERE drop_off_stop_id = p_dropoff_stop_id
FOR UPDATE NOWAIT;
SELECT dg.delivery_leg_id, st1.stop_location_id
FROM wsh_trip_stops st1,
wsh_trip_stops st2,
wsh_delivery_legs dg
WHERE st1.stop_id = dg.pick_up_stop_id AND
st2.stop_id = dg.drop_off_stop_id AND
st1.trip_id = p_trip_id AND
st2.trip_id = p_trip_id AND
dg.delivery_id = p_del_id
FOR UPDATE NOWAIT;
select status_code,
nvl(shipment_direction,'O') shipment_direction
from wsh_new_deliveries
where delivery_id = c_del_id;
select status_code,
nvl(shipment_direction,'O') shipment_direction
from wsh_new_deliveries
where delivery_id = c_del_id;
select status_code,
nvl(shipment_direction,'O') shipment_direction,
gross_weight,
net_weight,
volume, initial_pickup_location_id,
organization_id
from wsh_new_deliveries
where delivery_id = c_del_id;
SELECT stop_id
FROM wsh_trip_stops
WHERE trip_id = p_trip_id;
SELECT count(*)
FROM wsh_trip_stops a,wsh_delivery_legs b
WHERE a.stop_id = b.pick_up_stop_id
AND a.trip_id = p_trip_id
AND rownum = 1 ;
l_del_for_update_load_seq VARCHAR2(1); -- bug 6700792:OTM Dock Door App Sched Proj
l_del_for_update_load_seq := 'N';
SELECT 'Y'
INTO l_del_for_update_load_seq
FROM wsh_delivery_legs wdl,
wsh_trip_stops wts,
wsh_trips wt
WHERE wdl.delivery_id = p_del_rows(i)
AND wdl.drop_off_stop_id = wts.stop_id
AND wts.trip_id = wt.trip_id
AND wt.ignore_for_planning = 'N'
AND rownum = 1; --Bug 6884545
l_del_for_update_load_seq := 'N';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_DELIVERY_LEGS_PVT.DELETE_DELIVERY_LEG',WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_delivery_legs_pvt.delete_delivery_leg(NULL,
l_del_leg_id, l_return_status);
IF (l_del_for_update_load_seq = 'Y' AND g_wms_installed(l_org_id) = 'Y') THEN
DECLARE
CURSOR c_lock_delivery_details (c_delivery_id IN number) IS
SELECT wdd.delivery_detail_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd
WHERE wda.delivery_id = c_delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
FOR UPDATE OF wdd.load_seq_number NOWAIT;
l_del_det_tab.DELETE;
UPDATE wsh_new_deliveries
SET loading_sequence = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_id = p_del_rows(i);
UPDATE wsh_delivery_details
SET load_seq_number = NULL,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE delivery_detail_id = l_del_det_tab(i);
SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
trip.lane_id, trip.mode_of_transport
FROM wsh_delivery_legs dlg,
wsh_delivery_assignments_v da,
wsh_trips trip,
wsh_trip_stops st
WHERE da.delivery_detail_id = c_delivery_detail_id AND
da.delivery_id = dlg.delivery_id AND
dlg.pick_up_stop_id = st.stop_id AND
st.trip_id = trip.trip_id;
SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
trip.lane_id, trip.mode_of_transport
FROM wsh_delivery_legs dlg,
wsh_trip_stops stop,
wsh_trips trip
WHERE dlg.delivery_id = c_delivery_id and
dlg.pick_up_stop_id = stop.stop_id and
stop.trip_id = trip.trip_id;
SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
trip.lane_id, trip.mode_of_transport
FROM wsh_delivery_legs dlg,
wsh_trip_stops stop,
wsh_trips trip
WHERE dlg.delivery_leg_id = c_leg_id AND
dlg.pick_up_stop_id = stop.stop_id AND
stop.trip_id = trip.trip_id;
SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
trip.lane_id, trip.mode_of_transport
FROM wsh_trip_stops stop,
wsh_trips trip,
wsh_delivery_legs dlg,
wsh_trip_stops ts
WHERE stop.stop_id = c_stop_id AND
stop.trip_id = trip.trip_id AND
ts.trip_id=trip.trip_id AND
dlg.pick_up_stop_id = ts.stop_id;
SELECT dlg.delivery_leg_id, trip.trip_id, trip.consolidation_allowed,
trip.lane_id, trip.mode_of_transport
FROM wsh_trip_stops ts,
wsh_delivery_legs dlg,
wsh_trips trip
WHERE ts.trip_id = c_trip_id AND
dlg.pick_up_stop_id = ts.stop_id AND
ts.trip_id = trip.trip_id;
select 1
from wsh_freight_costs wfc,
wsh_trip_stops wts,
wsh_delivery_legs wdl
where wts.trip_id = c_trip_id
and wdl.pick_up_stop_id = wts.stop_id
and wfc.delivery_leg_id = wdl.delivery_leg_id
and wfc.line_type_code = 'SUMMARY'
and wfc.delivery_detail_id is null
and NVL(wfc.total_amount,0) > 0;
select NVL(wfc.total_amount,0) price
from wsh_freight_costs wfc
where wfc.delivery_leg_id = c_dleg_id
and wfc.line_type_code = 'SUMMARY'
and wfc.delivery_detail_id is null
and NVL(wfc.total_amount,0) > 0;
SELECT delivery_leg_id, status_code, reprice_required, parent_delivery_leg_id
FROM wsh_delivery_legs
WHERE delivery_leg_id = c_delivery_leg_id FOR UPDATE OF reprice_required NOWAIT;
SELECT mode_of_transport, lane_id
--SELECT consolidation_allowed, lane_id
FROM wsh_trips
WHERE trip_id = c_trip_id;
l_del_leg_tab.delete;
l_final_leg_tab.delete;
UPDATE wsh_delivery_legs
SET reprice_required = 'Y'
WHERE CURRENT OF c_lock_delivery_leg;
UPDATE wsh_delivery_legs
SET reprice_required = 'Y'
WHERE delivery_leg_id = l_lock_delivery_leg_rec.parent_delivery_leg_id
AND NVL(reprice_required, 'N') <> 'Y';
select freight_terms_code, nvl(shipment_direction, 'O')
from wsh_new_deliveries where delivery_id = p_del_id;
select wnd.delivery_id
FROM wsh_new_deliveries wnd,
wsh_trip_stops wts,
wsh_delivery_legs wdl
WHERE wts.stop_id = wdl.pick_up_stop_id AND
wts.trip_id = p_trip_id AND
wdl.delivery_id = wnd.delivery_id AND
wnd.freight_terms_code <> p_freight_terms_code_o and
NVL(wnd.shipment_direction, 'O') IN ('O', 'IO')
and rownum = 1
UNION
select wnd.delivery_id
FROM wsh_new_deliveries wnd,
wsh_trip_stops wts,
wsh_delivery_legs wdl
WHERE wts.stop_id = wdl.pick_up_stop_id AND
wts.trip_id = p_trip_id AND
wdl.delivery_id = wnd.delivery_id AND
wnd.freight_terms_code <> p_freight_terms_code_i and
wnd.shipment_direction = 'I'
and rownum = 1
UNION
select wnd.delivery_id
FROM wsh_new_deliveries wnd,
wsh_trip_stops wts,
wsh_delivery_legs wdl
WHERE wts.stop_id = wdl.pick_up_stop_id AND
wts.trip_id = p_trip_id AND
wdl.delivery_id = wnd.delivery_id AND
wnd.freight_terms_code <> p_freight_terms_code_d and
wnd.shipment_direction = 'D'
and rownum = 1;
select wnd.delivery_id
FROM wsh_new_deliveries wnd,
wsh_trip_stops wts,
wsh_delivery_legs wdl
WHERE wts.stop_id = wdl.pick_up_stop_id AND
wts.trip_id = p_trip_id AND
wdl.delivery_id = wnd.delivery_id AND
wnd.freight_terms_code = p_freight_terms_code_o and
NVL(wnd.shipment_direction, 'O') IN ('O', 'IO')
and rownum = 1
UNION
select wnd.delivery_id
FROM wsh_new_deliveries wnd,
wsh_trip_stops wts,
wsh_delivery_legs wdl
WHERE wts.stop_id = wdl.pick_up_stop_id AND
wts.trip_id = p_trip_id AND
wdl.delivery_id = wnd.delivery_id AND
wnd.freight_terms_code <> p_freight_terms_code_i and
wnd.shipment_direction = 'I'
and rownum = 1
UNION
select wnd.delivery_id
FROM wsh_new_deliveries wnd,
wsh_trip_stops wts,
wsh_delivery_legs wdl
WHERE wts.stop_id = wdl.pick_up_stop_id AND
wts.trip_id = p_trip_id AND
wdl.delivery_id = wnd.delivery_id AND
wnd.freight_terms_code <> p_freight_terms_code_d and
wnd.shipment_direction = 'D'
and rownum = 1;