[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct st.stop_id, st.stop_location_id
from wsh_trip_stops st,
wsh_delivery_legs dg
where st.trip_id = c_trip_id
and dg.pick_up_stop_id = st.stop_id;
select '1'
from dual
where exists (select dl.delivery_id
from wsh_new_deliveries dl,
wsh_delivery_legs dg
where dg.pick_up_stop_id = c_stop_id
and dl.initial_pickup_location_id = c_stop_location_id
AND nvl(dl.shipment_direction,'O') IN ('O','IO') -- J-IB-NPARIKH
and dl.delivery_id = dg.delivery_id );
select distinct dl.organization_id
from wsh_trip_stops st, wsh_delivery_legs dg, wsh_new_deliveries dl
where st.trip_id = c_trip_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;
select name
from wsh_report_sets
where report_set_id = c_report_set_id;
select stop_id
from wsh_trip_stops
where trip_id = p_trip_id
and stop_id = NVL(c_stop_id, stop_id)
and status_code IN ('OP','AR')
and nvl(SHIPMENTS_TYPE_FLAG, 'O') IN ('O', 'M') -- J Inbound Logistics jckwok
order by stop_sequence_number asc ;
select t.stop_id
from wsh_trip_stops t,
wsh_delivery_legs dg
where t.trip_id = p_trip_id
and dg.pick_up_stop_id = t.stop_id
and t.status_code IN ('OP','AR')
and nvl(SHIPMENTS_TYPE_FLAG, 'O') IN ('O', 'M'); -- J Inbound Logistics jckwok
SELECT 1
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st,
wsh_trips t
WHERE dl.delivery_id = dg.delivery_id AND
(dg.pick_up_stop_id = st.stop_id OR dg.drop_off_stop_id = st.stop_id) AND
st.trip_id = t.trip_id AND
dl.STATUS_CODE <> 'CO' and
st.stop_id = p_stop_id;
SELECT status_code
FROM wsh_trips
WHERE trip_id = p_trip_id ;
l_exceptions_tab.delete;
select status_code,
NVL(shipments_type_flag,'O') -- J-IB-NPARIKH
from wsh_trips
where trip_id = c_trip_id;
select 'Y'
from wsh_trip_stops
where trip_id = c_trip_id
and status_code <> 'OP'
and rownum = 1;
select 'Y'
from wsh_trips
where trip_id=p_tripid AND
planned_flag='F';
l_exceptions_tab.delete;
p_action => 'UPDATE',
x_return_status => l_return_status);
UPDATE wsh_trips
SET status_code = p_status_code,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE trip_id = p_trip_id;
SELECT delivery_id
FROM wsh_delivery_legs wdl, wsh_trip_stops wts
WHERE wdl.pick_up_stop_id=wts.stop_id AND
wts.trip_id=p_in_rec.trip_id;
p_action => 'UPDATE',
x_return_status => l_return_status);
UPDATE wsh_trips
SET status_code = p_in_rec.new_status_code,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE trip_id = p_in_rec.trip_id;
l_del_tmp_rows.delete;
l_trip_id_tab.delete;
SELECT wdl.delivery_id, wt.name
FROM wsh_trip_stops wts,
wsh_Delivery_legs wdl,
wsh_new_deliveries wnd,
wsh_trips wt
WHERE wt.trip_id = p_trip_id
AND wts.trip_id = p_trip_id
AND wdl.pick_up_stop_id = wts.stop_id
AND wnd.delivery_id = wdl.delivery_id
AND nvl(wnd.shipment_direction,'O') NOT IN ('O','IO') -- J-IB-NPARIKH
AND wnd.initial_pickup_location_id = wts.stop_location_id;
SELECT id,
column1,
column2,
column3,
column4,
column5
FROM WSH_TMP
WHERE flag = '~'
order by column1, column2, column3, column4, column5;
DELETE FROM wsh_tmp where flag = '~';
INSERT INTO wsh_tmp (id,
flag,
column1,
column2,
column3,
column4,
column5 )
VALUES(p_del_tbl.delivery_id(i),
'~',
p_del_tbl.MODE_OF_TRANSPORT(i),
p_del_tbl.service_level(i),
p_del_tbl.carrier_id(i),
p_del_tbl.INITIAL_PICKUP_LOCATION_ID(i),
p_del_tbl.ULTIMATE_DROPOFF_LOCATION_ID(i)
);
SELECT delivery_id,
INITIAL_PICKUP_LOCATION_ID,
ULTIMATE_DROPOFF_LOCATION_ID,
MODE_OF_TRANSPORT,
service_level,
carrier_id
FROM wsh_new_deliveries
WHERE delivery_id = v_delivery_id;
l_del_rows.DELETE;
SELECT NVL(ignore_for_planning,p_ignore_flag) ignore_for_planning
FROM wsh_new_deliveries
WHERE delivery_id=p_delid;
l_trip_ids.DELETE;
l_trip_names.DELETE;
SELECT delivery_id
FROM wsh_delivery_legs
WHERE delivery_id = p_del_id;
SELECT ship_method_code, carrier_id, mode_of_transport, service_level,
status_code,
--OTM R12, glog proj
NVL(ignore_for_planning,p_ignore_flag),
initial_pickup_date, ultimate_dropoff_date , nvl(shipment_direction,'O')
FROM wsh_new_deliveries
WHERE delivery_id = p_del_id;
SELECT stop_id,
planned_departure_date,
planned_arrival_date,
NVL(shipments_type_flag,'O') shipments_type_flag --J-IB-NPARIKH
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
ORDER BY stop_sequence_number; -- J-IB-NPARIKH
l_trip_info.last_update_date := SYSDATE;
l_trip_info.last_updated_by := fnd_global.user_id;
l_trip_info.last_update_login := fnd_global.login_id;
p_action => 'UPDATE',
x_return_status => l_return_status);
UPDATE wsh_trip_stops
SET planned_departure_date = nvl(planned_arrival_date, SYSDATE)
WHERE stop_id = st.stop_id;
p_action => 'UPDATE',
x_return_status => l_return_status);
UPDATE wsh_trip_stops
SET planned_arrival_date = nvl(planned_departure_date, SYSDATE)
WHERE stop_id = st.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 SHIP_METHOD_CODE, INTMED_SHIP_TO_LOCATION_ID
FROM wsh_new_deliveries
WHERE delivery_id = p_dlvy_id;
SELECT SHIP_METHOD_CODE
FROM wsh_trips
WHERE trip_id = p_trip_id;
update wsh_new_deliveries set INTMED_SHIP_TO_LOCATION_ID=null
where delivery_id=l_id_tab_temp(i);
update wsh_new_deliveries
set SHIP_METHOD_CODE=null,
CARRIER_ID = null,
MODE_OF_TRANSPORT = null,
SERVICE_LEVEL = null
where delivery_id=l_id_tab_temp(i);
update wsh_new_deliveries set INTMED_SHIP_TO_LOCATION_ID=null
where delivery_id=x_del_rows(i);
update wsh_new_deliveries
set SHIP_METHOD_CODE=null,
CARRIER_ID = null,
MODE_OF_TRANSPORT = null,
SERVICE_LEVEL = null
where delivery_id=x_del_rows(i);
l_cc_failed_records.DELETE;
l_cc_line_groups.DELETE;
l_cc_group_ids.DELETE;
l_id_tab_temp.DELETE;
update wsh_trips
set SHIP_METHOD_CODE=null,
CARRIER_ID = null,
MODE_OF_TRANSPORT = null,
SERVICE_LEVEL = null
where trip_id=l_trip_ids(l_tripindex);
update wsh_trips
set SHIP_METHOD_CODE=null,
CARRIER_ID = null,
MODE_OF_TRANSPORT = null,
SERVICE_LEVEL = null
where trip_id=l_trip_id_tab(l_tripindex);
SELECT dg.delivery_leg_id,
st.stop_id
FROM wsh_trip_stops st,
wsh_delivery_legs dg
WHERE dg.delivery_id = l_delivery_id AND
dg.pick_up_stop_id = st.stop_id AND
st.stop_location_id = p_pickup_location_id;
SELECT dg.delivery_leg_id,
st.stop_id
FROM wsh_trip_stops st,
wsh_delivery_legs dg
WHERE dg.delivery_id = l_delivery_id AND
dg.drop_off_stop_id = st.stop_id AND
st.stop_location_id = p_dropoff_location_id;
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_delivery_leg_id, x_return_status);
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_delivery_leg_id, x_return_status);
SELECT stop_location_id
FROM wsh_trip_stops
WHERE stop_id = l_trip_stop_id;
SELECT '1'
FROM wsh_delivery_legs wdl
WHERE (wdl.pick_up_stop_id=p_stop_id OR wdl.drop_off_stop_id=p_stop_id)
AND rownum=1;
SELECT stop_id, stop_location_id, status_code
FROM wsh_trip_stops
WHERE trip_id=p_trip_id
ORDER BY stop_sequence_number;
SELECT initial_pickup_location_id,
ultimate_dropoff_location_id
FROM wsh_new_deliveries
WHERE delivery_id = del_id;
SELECT 'Y'
FROM wsh_new_deliveries
WHERE delivery_id = del_id
AND ultimate_dropoff_location_id=loc_id;
b_physical_loc_updated BOOLEAN:=FALSE;
b_checkstopupdate BOOLEAN:=TRUE;
SELECT ts2.stop_sequence_number
FROM wsh_trip_stops ts1, -- get this stop's SSN
wsh_trip_stops ts2 -- then look for SSN and next SSN
WHERE ts1.stop_id = p_stop_id
AND ts2.trip_id = p_trip_id
AND ts2.stop_sequence_number >= ts1.stop_sequence_number
AND rownum <= 2
ORDER BY ts2.stop_sequence_number;
UPDATE wsh_trip_stops
SET stop_location_id= l_phys_trip_pickup_loc_id,
physical_location_id=null,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID
WHERE stop_id=p_pickup_stop_id;
b_checkstopupdate:=FALSE;
IF NOT(b_checkstopupdate) THEN
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'Have to create new internal stop as del with physical dropoff has been passed',l_dropoff_location_id);
UPDATE wsh_trip_stops
SET stop_location_id= l_dropoff_location_id,
physical_location_id=l_trip_dropoff_loc_id,
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID
WHERE stop_id=p_dropoff_stop_id;
SELECT st.stop_id
FROM wsh_trip_stops st,
wsh_delivery_legs dg
WHERE st.stop_location_id = l_loc_id AND
dg.pick_up_stop_id = st.stop_id AND
dg.delivery_id = l_del_id;
SELECT st.stop_id
FROM wsh_trip_stops st,
wsh_delivery_legs dg
WHERE st.stop_location_id = l_loc_id AND
dg.drop_off_stop_id = st.stop_id AND
dg.delivery_id = l_del_id;
SELECT trip_id,
weight_uom_code,
volume_uom_code
FROM wsh_trip_stops
WHERE stop_id = c_stop_id;
SELECT vehicle_item_id,
vehicle_organization_id
FROM wsh_trips
WHERE trip_id = c_trip_id;
SELECT msi.maximum_load_weight,
msi.internal_volume,
msi.minimum_fill_percent,
msi.weight_uom_code,
msi.volume_uom_code,
msi.organization_id
FROM mtl_system_items msi,
wsh_trips t
WHERE msi.organization_id = t.vehicle_organization_id AND
t.vehicle_item_id = msi.inventory_item_id AND
t.trip_id = c_trip_id;
SELECT wsp.percent_fill_basis_flag
FROM wsh_shipping_parameters wsp,
wsh_trips t
WHERE wsp.organization_id = t.vehicle_organization_id AND
t.trip_id = c_trip_id;
SELECT stop_id,
weight_uom_code,
volume_uom_code,
status_code,
departure_gross_weight,
departure_net_weight,
departure_volume,
nvl(shipments_type_flag,'O'),
nvl(wv_frozen_flag,'Y')
FROM wsh_trip_stops
WHERE trip_id = l_trip_id AND
nvl(planned_departure_date, nvl(p_start_departure_date, FND_API.G_MISS_DATE)) >= nvl(p_start_departure_date, FND_API.G_MISS_DATE)
ORDER BY stop_sequence_number;
SELECT stop_id
FROM wsh_trip_stops
WHERE stop_id = c_stop_id
FOR UPDATE NOWAIT;
SELECT stop_id,
departure_gross_weight,
departure_net_weight,
departure_volume,
weight_uom_code,
volume_uom_code
FROM wsh_trip_stops
WHERE trip_id = l_trip_id AND
nvl(planned_departure_date, nvl(p_start_departure_date, FND_API.G_MISS_DATE)) <= nvl(p_start_departure_date, FND_API.G_MISS_DATE) AND
rownum = 1
ORDER BY stop_sequence_number DESC;
SELECT stop_id
FROM wsh_trip_stops
WHERE trip_id = l_trip_id
AND stop_sequence_number = ( SELECT MAX(wts.stop_sequence_number)
FROM wsh_trip_stops wts
WHERE wts.trip_id = l_trip_id );
SELECT dl.delivery_id d_id,
dl.weight_uom_code wt_uom,
dl.volume_uom_code vol_uom,
dl.gross_weight,
dl.net_weight,
dl.volume,
dl.organization_id
FROM wsh_trip_stops t,
wsh_delivery_legs dg,
wsh_new_deliveries dl
WHERE t.stop_id = l_stop_id AND
dg.pick_up_stop_id = t.stop_id AND
dl.delivery_id = dg.delivery_id AND
dg.parent_delivery_leg_id is NULL;
SELECT dl.delivery_id d_id,
dl.organization_id
FROM wsh_trip_stops t,
wsh_delivery_legs dg,
wsh_new_deliveries dl
WHERE t.stop_id = l_stop_id AND
dg.drop_off_stop_id = t.stop_id AND
dl.delivery_id = dg.delivery_id AND
dg.parent_delivery_leg_id is NULL;
SELECT msi.maximum_load_weight,
msi.internal_volume,
msi.minimum_fill_percent,
msi.weight_uom_code,
msi.volume_uom_code,
msi.organization_id
FROM mtl_system_items msi,
wsh_trips t
WHERE msi.organization_id = t.vehicle_organization_id AND
t.vehicle_item_id = msi.inventory_item_id AND
t.trip_id = l_trip_id;
SELECT wsp.percent_fill_basis_flag
FROM wsh_shipping_parameters wsp,
wsh_trips t
WHERE wsp.organization_id = t.vehicle_organization_id AND
t.trip_id = l_trip_id;
SELECT vehicle_item_id,
vehicle_organization_id
FROM wsh_trips
WHERE trip_id = l_trip_id;
so use l_trip_num_warn to update for warnings and then if l_trip_num_warn is >1 at loop end,
increase l_num_warn by 1. In some cases, l_num_warn is directly updated and since it is directly
redirected to end of loop, those are okay.
*/
l_trip_num_warn:=0;
pickup_del_tab.delete;
p_update_flag => 'Y',
p_calc_wv_if_frozen => p_calc_wv_if_frozen,
x_gross_weight => l_gross_weight,
x_net_weight => l_net_weight,
x_volume => l_volume,
x_return_status => l_return_status);
UPDATE wsh_trip_stops
SET pick_up_weight = l_total_pick_up_weight,
drop_off_weight = l_total_drop_off_weight,
pick_up_volume = l_total_pick_up_volume,
drop_off_volume = l_total_drop_off_volume
WHERE stop_id = l_stop_id;
UPDATE wsh_trip_stops
SET departure_gross_weight = l_total_gross_weight,
departure_net_weight = l_total_net_weight,
departure_volume = l_total_volume,
departure_fill_percent = l_stop_fill_percent,
wv_frozen_flag = 'N'
WHERE stop_id = l_stop_id;
SELECT stop_id,
stop_sequence_number,
planned_arrival_date,
planned_departure_date
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
ORDER BY stop_sequence_number;
stmt_str := 'select st.trip_id, count(*) '||
'from wsh_trip_stops st, wsh_delivery_legs dg, wsh_trips tr '||
'where dg.pick_up_stop_id = st.stop_id AND '||
'tr.trip_id = st.trip_id AND tr.planned_flag IN (''N'',''Y'') AND ' ||
'dg.delivery_id IN (' || del_str || ') ' ||
'group by st.trip_id '||
'having count(*) = '||to_char(p_del_rows.count);
SELECT name, planned_flag
FROM wsh_trips
WHERE trip_id = p_trip_id;
select l.delivery_id from wsh_delivery_legs l, wsh_trip_stops s
where l.delivery_id = p_delivery_id
and l.parent_delivery_leg_id is not null
and l.pick_up_stop_id = s.stop_id
and s.trip_id = p_trip_id;
l_mdc_del_tab.delete(j);
l_trip_tab.delete;
p_action => 'UPDATE',
x_return_status => l_return_status);
SELECT stop_id
FROM wsh_trip_stops
WHERE stop_id = c_stop_id
FOR UPDATE NOWAIT;
SELECT wst.stop_sequence_number,
wst.stop_id,
wst.status_code,
wst.planned_arrival_date,
wst.planned_departure_date,
wst.actual_arrival_date,
wst.actual_departure_date
FROM wsh_trip_stops wst,
wsh_trips wtp
WHERE wtp.status_code in ('OP', 'IT')
AND wtp.trip_id = c_trip_id
AND wtp.trip_id = wst.trip_id
ORDER BY wst.stop_sequence_number,wst.stop_id;
l_update_flag BOOLEAN;
l_update_flag := FALSE;
l_update_flag := TRUE;
l_update_flag := TRUE;
IF l_update_flag THEN
IF l_stop_rec.planned_departure_date < l_stop_rec.planned_arrival_date THEN
l_stop_rec.planned_departure_date := l_stop_rec.planned_arrival_date;
UPDATE wsh_trip_stops
SET planned_arrival_date = l_stop_rec.planned_arrival_date,
planned_departure_date = l_stop_rec.planned_departure_date,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.user_id,
last_update_login = FND_GLOBAL.login_id
WHERE stop_id = l_stop_rec.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);
WSH_DEBUG_SV.logmsg(l_module_name,'ERROR: stop is locked, cannot update it');
SELECT wts.stop_id,
wts.stop_location_id,
wts.physical_location_id,
wts.physical_stop_id,
wts.stop_sequence_number,
-- conditions to keep the link to synchronize the columns:
-- a. physical stop exists
-- b. (physical location matches or dummy stop's status is Arrived)
--
-- The same DECODE structure must be maintained for these columns
-- in this cursor:
-- PLANNED_ARRIVAL_DATE,
-- PLANNED_DEPARTURE_DATE,
-- BREAK_LINK_FLAG,
-- and ORDER BY first clause
DECODE(
DECODE(wts.physical_stop_id,
NULL, 0,
NVL(pts.stop_id,-1),
DECODE(wts.physical_location_id,
pts.stop_location_id, 1,
DECODE(wts.status_code,'AR',1, 0)
),
0 -- linked to a non-existent physical stop
),
1, (pts.planned_arrival_date - C_TEN_SECONDS),
wts.planned_arrival_date) PLANNED_ARRIVAL_DATE,
DECODE(
DECODE(wts.physical_stop_id,
NULL, 0,
NVL(pts.stop_id,-1),
DECODE(wts.physical_location_id,
pts.stop_location_id, 1,
DECODE(wts.status_code,'AR',1, 0)
),
0 -- linked to a non-existent physical stop
),
1, (pts.planned_arrival_date - C_TEN_SECONDS),
wts.planned_departure_date) PLANNED_DEPARTURE_DATE,
wts.stop_sequence_number org_stop_seq_num,
wts.planned_arrival_date org_pl_arr_date,
wts.planned_departure_date org_pl_dep_date,
wts.status_code,
-- break_link_flag: 'Y' or 'N'
-- The DECODE structure is slightly modified
-- to decide whether link needs breaking.
-- If stop is not linked (NULL), there is no link to break.
-- If physical stop_id matches, check for need to break link.
DECODE(
DECODE(wts.physical_stop_id,
NULL, 1, -- modified value because there is no link to break
NVL(pts.stop_id,-1),
DECODE(wts.physical_location_id,
pts.stop_location_id, 1,
DECODE(wts.status_code,'AR',1, 0)
),
0 -- linked to a non-existent physical stop
),
1, 'N',
'Y') BREAK_LINK_FLAG
FROM wsh_trip_stops wts,
wsh_trip_stops pts
WHERE wts.trip_id = c_trip_id
and wts.physical_stop_id = pts.stop_id(+)
ORDER BY
-- we need to order by PAD;
SELECT wts.stop_id,
wts.stop_location_id,
wts.physical_location_id,
wts.physical_stop_id,
-- conditions to keep the link to synchronize the columns:
-- a. physical stop exists/matches
-- b. (physical location matches or dummy stop's status is Arrived)
--
-- The same DECODE structure must be maintained for these columns
-- in this cursor:
-- STOP_SEQUENCE_NUMBER
-- PLANNED_ARRIVAL_DATE,
-- PLANNED_DEPARTURE_DATE,
-- BREAK_LINK_FLAG,
-- and ORDER BY first clause
DECODE(
DECODE(wts.physical_stop_id,
NULL, 0,
NVL(pts.stop_id,-1),
DECODE(wts.physical_location_id,
pts.stop_location_id, 1,
DECODE(wts.status_code,'AR',1, 0)
),
0 -- linked to a non-existent physical stop
),
1, (pts.stop_sequence_number - 1),
wts.stop_sequence_number) STOP_SEQUENCE_NUMBER,
DECODE(
DECODE(wts.physical_stop_id,
NULL, 0,
NVL(pts.stop_id,-1),
DECODE(wts.physical_location_id,
pts.stop_location_id, 1,
DECODE(wts.status_code,'AR',1, 0)
),
0 -- linked to a non-existent physical stop
),
1, (pts.planned_arrival_date - C_TEN_SECONDS),
wts.planned_arrival_date) PLANNED_ARRIVAL_DATE,
DECODE(
DECODE(wts.physical_stop_id,
NULL, 0,
NVL(pts.stop_id,-1),
DECODE(wts.physical_location_id,
pts.stop_location_id, 1,
DECODE(wts.status_code,'AR',1, 0)
),
0 -- linked to a non-existent physical stop
),
1, (pts.planned_arrival_date - C_TEN_SECONDS),
wts.planned_departure_date) PLANNED_DEPARTURE_DATE,
wts.stop_sequence_number org_stop_seq_num,
wts.planned_arrival_date org_pl_arr_date,
wts.planned_departure_date org_pl_dep_date,
wts.status_code,
-- break_link_flag: 'Y' or 'N'
-- The DECODE structure is modified
-- to decide whether link needs breaking.
-- If stop is not linked (NULL), there is no link to break.
-- If physical stop_id matches, check for delinking.
-- Otherwise, the physical stop must have been deleted.
DECODE(
DECODE(wts.physical_stop_id,
NULL, 1, -- no link to break
NVL(pts.stop_id,-1),
DECODE(wts.physical_location_id,
pts.stop_location_id, 1,
DECODE(wts.status_code,'AR',1, 0)
),
0 -- linked to a non-existent physical stop
),
1, 'N',
'Y') BREAK_LINK_FLAG
FROM wsh_trip_stops wts,
wsh_trip_stops pts
WHERE wts.trip_id = c_trip_id
and wts.physical_stop_id = pts.stop_id(+)
ORDER BY
-- we need to order by SSN;
select delivery_leg_id
from
wsh_delivery_legs wlg
where
pick_up_stop_id = c_physical_stop_id
AND drop_off_stop_id = c_dummy_stop_id
AND rownum = 1;
l_update_flag BOOLEAN;
x_success_trip_ids.delete;
l_update_flag := FALSE;
l_update_flag := TRUE;
l_update_flag := TRUE;
l_update_flag := TRUE;
l_update_flag := TRUE;
l_update_flag := TRUE;
l_update_flag := TRUE;
IF l_update_flag or p_caller = 'WSH_CREATE_TRIP_STOP' THEN
IF l_debug_on THEN
WSH_DEBUG_SV.log(l_module_name,'updating stops ' , l_getstops_stop_id.count);
UPDATE WSH_TRIP_STOPS
SET
PHYSICAL_LOCATION_ID = l_getstops_phys_loc_id(j),
PHYSICAL_STOP_ID = l_getstops_phys_stop_id(j),
PLANNED_ARRIVAL_DATE = l_getstops_pl_arr_date(j),
PLANNED_DEPARTURE_DATE = l_getstops_pl_dep_date(j),
STOP_SEQUENCE_NUMBER = l_getstops_stop_seq_num(j),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE STOP_ID = l_getstops_stop_id(j);
WSH_DEBUG_SV.log(l_module_name,'done update' , l_getstops_stop_id.count);
WSH_INTEGRATION.DBI_Update_Trip_Stop_Log
(p_stop_id_tab => l_getstops_stop_id,
p_dml_type => 'UPDATE',
x_return_status => l_dbi_rs);
PROCEDURE PROCESS_CARRIER_SELECTION (
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_trip_id_tab IN wsh_util_core.id_tab_type,
p_caller IN VARCHAR2 DEFAULT NULL, -- WSH_FSTRX / WSH_PUB / WSH_GROUP/ FTE
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
-- Cursor to get trip level information
CURSOR c_get_trip_details(c_tripid IN NUMBER) is
SELECT trip_id
, name
, planned_flag
, status_code
, carrier_id
, mode_of_transport
, service_level
, ship_method_code
-- , track_only_flag,
, consignee_carrier_ac_no
, freight_terms_code
, Load_tender_status
, lane_id
, rank_id
FROM WSH_TRIPS
WHERE trip_id = c_tripid;
SELECT wts.STOP_ID
, wts.TRIP_ID
--To handle dummy locations #DUM_LOC(S)
, wts.STOP_LOCATION_ID
, wts.STATUS_CODE
, wts.STOP_SEQUENCE_NUMBER
, wts.PLANNED_ARRIVAL_DATE
, wts.PLANNED_DEPARTURE_DATE
, wts.ACTUAL_ARRIVAL_DATE
, wts.ACTUAL_DEPARTURE_DATE
--#DUM_LOC(S)
, wts.PHYSICAL_LOCATION_ID
--#DUM_LOC(E)
, wts.PHYSICAL_STOP_ID
, wts.pick_up_weight
, wts.weight_uom_code
, wts.pick_up_volume
, wts.volume_uom_code
FROM wsh_trip_stops wts
WHERE wts.trip_id = c_tripid
order by wts.STOP_SEQUENCE_NUMBER;
SELECT wnd.DELIVERY_ID
, wnd.CUSTOMER_ID
, wnd.ORGANIZATION_ID
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts1
WHERE wnd.delivery_id = wdl.delivery_id
AND wdl.pick_up_stop_id = wts1.stop_id
AND wnd.initial_pickup_location_id = wts1.stop_location_id
AND nvl(wnd.shipping_control,'BUYER') <> 'SUPPLIER'
AND wts1.stop_id = c_stop_id;
SELECT wnd.DELIVERY_ID
, wnd.CUSTOMER_ID
, wnd.ORGANIZATION_ID
FROM wsh_new_deliveries wnd,
wsh_delivery_legs wdl,
wsh_trip_stops wts1
WHERE wnd.delivery_id = wdl.delivery_id
AND wdl.drop_off_stop_id = wts1.stop_id
AND wnd.ultimate_dropoff_location_id = wts1.stop_location_id
AND nvl(wnd.shipping_control,'BUYER') <> 'SUPPLIER'
AND wts1.stop_id = c_stop_id;
SELECT MOVE_ID
FROM FTE_TRIP_MOVES
WHERE TRIP_ID = c_trip_id;
SELECT SITE.SITE_USE_ID
FROM HZ_CUST_ACCT_SITES_ALL ACCT_SITE,
HZ_PARTY_SITES PARTY_SITE,
HZ_LOCATIONS LOC,
HZ_CUST_SITE_USES_ALL SITE
WHERE
SITE.SITE_USE_CODE = 'SHIP_TO'
AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
AND LOC.LOCATION_ID = c_location_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_CARRIER_SELECTION';
SAVEPOINT before_trip_update;
l_cs_tripstops_tab.DELETE;
l_stop_delivery_id_tab.DELETE;
l_stop_customer_id_tab.DELETE;
l_stop_organization_id_tab.DELETE;
WSH_FTE_INTEGRATION.CARRIER_SELECTION(
p_format_cs_tab => l_cs_input_tab,
p_messaging_yn => 'Y',
p_caller => p_caller,
p_entity => 'TRIP',
x_cs_output_tab => l_cs_result_tab,
x_cs_output_message_tab => l_cs_output_message_tab,
x_return_message => l_return_message,
x_return_status => l_return_status);
l_trip_info_tab.DELETE;
l_trip_in_rec.action_code := 'UPDATE';
WSH_TRIPS_GRP.Create_Update_Trip(
p_api_version_number => 1.0,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trip_info_tab => l_trip_info_tab,
p_in_rec => l_trip_in_rec,
x_out_tab => l_trip_out_rec_tab);
l_ranked_list.DELETE;
WSH_DEBUG_SV.logmsg(l_module_name, 'NO RANKED RESULT FOUND FROM PROCESS CARRIER SELECTION ' );
FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_CARRIER_SELECTED');
WSH_DEBUG_SV.logmsg(l_module_name, 'NO CARRIER FOUND FROM PROCESS CARRIER SELECTION ' );
WSH_DEBUG_SV.logmsg(l_module_name, 'NO VALID TRIP FOUND FOR PROCESS CARRIER SELECTION ' );
ROLLBACK TO before_trip_update;
ROLLBACK TO before_trip_update;
wsh_util_core.default_handler('WSH_TRIPS_ACTIONS.PROCESS_CARRIER_SELECTION');
END PROCESS_CARRIER_SELECTION;
select d.delivery_id, d.delivery_type
from wsh_new_deliveries d, wsh_trip_stops s, wsh_delivery_legs l
where d.delivery_id = l.delivery_id
and l.pick_up_stop_id = s.stop_id
and s.trip_id = p_trip_id
order by d.delivery_type;
l_delivery_tab.delete;
l_consol_delivery_tab.delete;