The following lines contain the word 'select', 'insert', 'update' or 'delete':
K_UPDATE CONSTANT VARCHAR2(30) := 'UPDATE';
SELECT lane_id, schedule_id,
carrier_id, mode_of_transport, service_level,
NVL(consolidation_allowed,'N') consolidation_allowed
FROM wsh_trips
WHERE trip_id = p_trip_id;
SELECT stop_location_id
FROM wsh_trip_stops
WHERE stop_id = p_stop_id;
SELECT stop_id, stop_sequence_number, stop_location_id
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
AND stop_sequence_number >= p_stop_seq
order by stop_sequence_number desc;
SELECT weight_uom_code,volume_uom_code
FROM wsh_new_deliveries
WHERE delivery_id = p_dlvy_id;
-- Null implies that this flag is not required to be updated.
l_reprice_required := NULL;
) -- Need to check if ship method is to be updated.
)
OR p_ui_page_name = GK_SEARCH_SEGMENTS_PAGE
THEN
--{
l_old_lane_id := FND_API.G_MISS_NUM;
l_wsh_trip_action := K_UPDATE;
AND l_wsh_trip_action = K_UPDATE
THEN
l_first_stop_new_location_id := p_pickup_location_id;
l_pickup_stop_action := K_UPDATE;
AND l_wsh_trip_action = K_UPDATE
THEN
l_last_stop_new_location_id := p_dropoff_location_id;
l_dropoff_stop_action := K_UPDATE;
IF l_wsh_trip_action IN ( K_CREATE, K_UPDATE )
THEN
--{
fte_mls_util.derive_ship_method
(
p_carrier_id => p_carrier_id,
p_mode_of_transport => p_mode_of_transport,
p_service_level => p_service_level,
p_carrier_name => p_carrier_name,
p_mode_of_transport_meaning => p_mode_of_transport_meaning,
p_service_level_meaning => p_service_level_meaning,
x_ship_method_code => l_ship_method_code,
x_return_status => l_return_status
);
fte_mls_wrapper.create_update_stop
(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => K_UPDATE,
p_trip_id => p_wsh_trip_id,
p_stop_location_id => stop_rec.stop_location_id,
pp_stop_location_id => stop_rec.stop_location_id,
pp_STOP_ID => stop_rec.stop_id,
pp_TRIP_ID => p_wsh_trip_id,
pp_STOP_SEQUENCE_NUMBER => FND_API.G_MISS_NUM,
x_stop_id => l_stop_id
);
p_api_name => 'FTE_MLS_WRAPPER.UPDATE_STOP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
p_trip_segment_id => p_wsh_trip_id,
p_trip_segment_name => p_wsh_trip_name,
p_trip_stop_id => stop_rec.stop_id,
p_stop_seq_number => stop_rec.stop_sequence_number,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
fte_mls_wrapper.create_update_trip
(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => l_wsh_trip_action,
p_rec_TRIP_ID => l_wsh_trip_id,
p_rec_NAME => p_wsh_trip_name,
-- adding vehicle info
p_rec_VEHICLE_ORGANIZATION_ID => p_veh_org_id,
p_rec_VEHICLE_NUMBER => p_veh_num,
p_rec_VEHICLE_NUM_PREFIX => p_veh_num_pre,
p_rec_VEHICLE_ITEM_ID => p_veh_item_id,
-- end adding vehicle info
p_rec_CARRIER_ID => p_carrier_id,
p_rec_SHIP_METHOD_CODE => l_ship_method_code,
p_rec_SERVICE_LEVEL => p_service_level,
p_rec_MODE_OF_TRANSPORT => p_mode_of_transport,
p_rec_LANE_ID => p_lane_id,
p_rec_SCHEDULE_ID => p_schedule_id,
p_rec_CONSOLIDATION_ALLOWED => 'N',
p_rec_APPEND_FLAG => 'N',
p_trip_name => l_wsh_trip_name,
x_trip_id => l_wsh_trip_id,
x_trip_name => l_wsh_trip_name,
p_rec_IGNORE_FOR_PLANNING => p_ignore_for_planning
);
p_api_name => 'FTE_MLS_WRAPPER.CREATE_UPDATE_TRIP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
ELSIF l_wsh_trip_action = K_UPDATE
THEN
--{
assign_service_to_segment
(
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_wsh_trip_id => l_wsh_trip_id,
p_wsh_trip_name => l_wsh_trip_name,
p_lane_id => p_lane_id,
p_schedule_id => p_schedule_id,
p_carrier_id => p_carrier_id,
p_mode_of_transport => p_mode_of_transport,
p_service_level => p_service_level,
p_carrier_name => p_carrier_name,
p_mode_of_transport_meaning => p_mode_of_transport_meaning,
p_service_level_meaning => p_service_level_meaning,
p_ship_method_code => l_ship_method_code,
p_first_stop_id => p_first_stop_id,
p_first_stop_seq => p_first_stop_seq,
p_first_stop_location_id => p_first_stop_location_id,
p_first_stop_new_location_id => l_first_stop_new_location_id,
p_first_stop_departure_date => p_first_stop_departure_date,
p_first_stop_arrival_date => p_first_stop_arrival_date,
p_last_stop_id => p_last_stop_id,
p_last_stop_seq => p_last_stop_seq,
p_last_stop_location_id => p_last_stop_location_id,
p_last_stop_new_location_id => l_last_stop_new_location_id,
p_last_stop_departure_date => p_last_stop_departure_date,
p_last_stop_arrival_date => p_last_stop_arrival_date,
p_veh_org_id => p_veh_org_id,
p_veh_item_id => p_veh_item_id
);
IF l_pickup_stop_action IN ( K_CREATE, K_UPDATE )
THEN
--{
-- PACK I : HBHAGAVA
-- Check if stop is getting created. If yes the
-- get the weight volume uom of delivery and
-- assign that to stop.
IF (l_pickup_stop_action = K_CREATE)
THEN
OPEN dlvy_weight_volume_cur(p_delivery_id);
fte_mls_wrapper.create_update_stop
(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => l_pickup_stop_action,
p_trip_id => l_wsh_trip_id,
p_stop_location_id => p_pickup_location_id,
p_planned_dep_date => p_pickup_departure_date,
pp_TRIP_ID => l_wsh_trip_id,
pp_STOP_SEQUENCE_NUMBER => FND_API.G_MISS_NUM,
pp_STOP_LOCATION_ID => p_pickup_location_id,
pp_PLANNED_ARRIVAL_DATE => p_pickup_arrival_date,
pp_PLANNED_DEPARTURE_DATE => p_pickup_departure_date,
pp_WEIGHT_UOM_CODE => l_dlvy_weight_uom,
pp_VOLUME_UOM_CODE => l_dlvy_volume_uom,
x_stop_id => l_pickup_stop_id
);
fte_mls_wrapper.create_update_stop
(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => l_pickup_stop_action,
p_trip_id => l_wsh_trip_id,
p_stop_location_id => p_pickup_location_id,
p_planned_dep_date => p_pickup_departure_date,
pp_TRIP_ID => l_wsh_trip_id,
pp_STOP_ID => l_pickup_stop_id,
pp_STOP_SEQUENCE_NUMBER => l_pickup_stop_seq,
pp_STOP_LOCATION_ID => p_pickup_location_id,
pp_PLANNED_ARRIVAL_DATE => p_pickup_arrival_date,
pp_PLANNED_DEPARTURE_DATE => p_pickup_departure_date,
x_stop_id => return_pickup_stop_id
);
p_api_name => 'FTE_MLS_WRAPPER.CREATE_UPDATE_STOP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
p_trip_segment_id => l_wsh_trip_id,
p_trip_segment_name => l_wsh_trip_name,
p_trip_stop_id => l_pickup_stop_id,
p_stop_seq_number => l_pickup_stop_seq,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
IF l_dropoff_stop_action IN ( K_CREATE, K_UPDATE )
THEN
--{
IF (l_dropoff_stop_action = K_CREATE)
THEN
OPEN dlvy_weight_volume_cur(p_delivery_id);
fte_mls_wrapper.create_update_stop
(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => l_dropoff_stop_action,
p_trip_id => l_wsh_trip_id,
p_stop_location_id => p_dropoff_location_id,
p_planned_dep_date => p_dropoff_departure_date,
pp_TRIP_ID => l_wsh_trip_id,
pp_STOP_SEQUENCE_NUMBER => FND_API.G_MISS_NUM,
pp_STOP_LOCATION_ID => p_dropoff_location_id,
pp_PLANNED_ARRIVAL_DATE => p_dropoff_arrival_date,
pp_PLANNED_DEPARTURE_DATE => p_dropoff_departure_date,
pp_WEIGHT_UOM_CODE => l_dlvy_weight_uom,
pp_VOLUME_UOM_CODE => l_dlvy_volume_uom,
x_stop_id => l_dropoff_stop_id
);
fte_mls_wrapper.create_update_stop
(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => l_dropoff_stop_action,
p_trip_id => l_wsh_trip_id,
p_stop_location_id => p_dropoff_location_id,
p_planned_dep_date => p_dropoff_departure_date,
pp_TRIP_ID => l_wsh_trip_id,
pp_STOP_ID => l_dropoff_stop_id,
pp_STOP_SEQUENCE_NUMBER => l_dropoff_stop_seq,
pp_STOP_LOCATION_ID => p_dropoff_location_id,
pp_PLANNED_ARRIVAL_DATE => p_dropoff_arrival_date,
pp_PLANNED_DEPARTURE_DATE => p_dropoff_departure_date,
pp_WEIGHT_UOM_CODE => l_dlvy_weight_uom,
pp_VOLUME_UOM_CODE => l_dlvy_volume_uom,
x_stop_id => return_dropoff_stop_id
);
p_api_name => 'FTE_MLS_WRAPPER.CREATE_UPDATE_STOP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
p_trip_segment_id => l_wsh_trip_id,
p_trip_segment_name => l_wsh_trip_name,
p_trip_stop_id => l_dropoff_stop_id,
p_stop_seq_number => l_dropoff_stop_seq,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
fte_trips_pvt.Create_Update_Delete_Fte_Trip
(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => l_fte_trip_action,
pp_fte_trip_id => l_fte_trip_id,
pp_NAME => l_fte_trip_name,
pp_private_trip => 'N',
pp_validation_required => 'Y',
x_trip_id => l_fte_trip_id,
x_name => l_fte_trip_name
);
p_api_name => 'FTE_TRIPS_PVT.CREATE_UPDATE_DELETE_FTE_TRIP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
fte_wsh_trips_pvt.Create_Update_Trip_Wrapper
(
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => l_fte_trip_action,
pp_fte_trip_id => l_fte_trip_id,
pp_wsh_trip_id => l_wsh_trip_id,
pp_sequence_number => l_fte_wsh_trip_seq,
p_validate_flag => 'N'
);
p_api_name => 'FTE_WSH_TRIPS_PVT.CREATE_UPDATE_TRIP_WRAPPER',
p_api_return_status => l_return_status,
p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
UPDATE wsh_delivery_legs
SET fte_trip_id = l_fte_trip_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE delivery_leg_id = l_delivery_leg_id;
p_api_name => 'FTE_DELIVERY_LEGS_PVT.UPDATE_DLEG_FTE_TRIP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
UPDATE wsh_delivery_legs
SET reprice_required = l_reprice_required,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE delivery_leg_id IN
( SELECT delivery_leg_id
FROM wsh_trip_stops wts,
wsh_delivery_legs wdl
WHERE wts.trip_id = p_wsh_trip_id
AND wdl.pick_up_stop_id = wts.stop_id
);
p_api_name => 'FTE_DELIVERY_LEGS_PVT.UPDATE_DLEG_REPRICE',
p_api_return_status => l_return_status,
p_message_name => 'FTE_DELIVERY_LEG_UNEXP_ERROR',
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
SELECT stop_id, stop_location_id, stop_sequence_number
FROM wsh_trip_stops
WHERE trip_id = p_trip_id
order by stop_sequence_number;
fte_mls_wrapper.create_update_stop
(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => 'UPDATE',
p_trip_id => p_wsh_trip_id,
p_stop_location_id => l_stop_location_id,
p_planned_dep_date => p_first_stop_departure_date,
pp_stop_location_id => l_stop_location_id,
pp_STOP_ID => p_first_stop_id,
pp_TRIP_ID => p_wsh_trip_id,
pp_STOP_SEQUENCE_NUMBER => p_first_stop_seq,
pp_PLANNED_ARRIVAL_DATE => p_first_stop_arrival_date,
pp_PLANNED_DEPARTURE_DATE => p_first_stop_departure_date,
x_stop_id => l_stop_id
);
p_api_name => 'FTE_MLS_WRAPPER.UPDATE_STOP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
p_trip_segment_id => p_wsh_trip_id,
p_trip_segment_name => p_wsh_trip_name,
p_trip_stop_id => p_first_stop_id,
p_stop_seq_number => p_first_stop_seq,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
fte_mls_wrapper.create_update_stop
(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => 'UPDATE',
p_trip_id => p_wsh_trip_id,
p_stop_location_id => l_stop_location_id,
p_planned_dep_date => p_last_stop_departure_date,
pp_stop_location_id => l_stop_location_id,
pp_STOP_ID => p_last_stop_id,
pp_TRIP_ID => p_wsh_trip_id,
pp_STOP_SEQUENCE_NUMBER => p_last_stop_seq,
pp_PLANNED_ARRIVAL_DATE => p_last_stop_arrival_date,
pp_PLANNED_DEPARTURE_DATE => p_last_stop_departure_date,
x_stop_id => l_stop_id
);
p_api_name => 'FTE_MLS_WRAPPER.UPDATE_STOP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_SEGMENT_STOP_UNEXP_ERROR',
p_trip_segment_id => p_wsh_trip_id,
p_trip_segment_name => p_wsh_trip_name,
p_trip_stop_id => p_last_stop_id,
p_stop_seq_number => p_last_stop_seq,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
fte_mls_wrapper.create_update_trip
(
p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_action_code => 'UPDATE',
p_rec_TRIP_ID => p_wsh_trip_id,
p_rec_NAME => p_wsh_trip_name,
p_rec_CARRIER_ID => p_carrier_id,
p_rec_SHIP_METHOD_CODE => p_ship_method_code,
p_rec_SERVICE_LEVEL => p_service_level,
p_rec_MODE_OF_TRANSPORT => p_mode_of_transport,
p_rec_LANE_ID => p_lane_id,
p_rec_SCHEDULE_ID => p_schedule_id,
p_trip_name => p_wsh_trip_name,
p_rec_VEHICLE_ORGANIZATION_ID => p_veh_org_id,
p_rec_VEHICLE_ITEM_ID => p_veh_item_id,
x_trip_id => l_trip_id,
x_trip_name => l_wsh_trip_name
);
FTE_MLS_TEST_NT.UPDATE_SERVICE_ON_TRIP(
p_API_VERSION_NUMBER => 1.0,
p_INIT_MSG_LIST => FND_API.G_TRUE,
p_COMMIT => FND_API.G_FALSE,
p_CALLER => 'FTE',
p_SERVICE_ACTION => 'UPDATE',
p_DELIVERY_ID => p_delivery_id,
p_DELIVERY_LEG_ID => p_delivery_leg_id,
p_TRIP_ID => p_wsh_trip_id,
p_LANE_ID => p_lane_id,
p_SCHEDULE_ID => p_schedule_id,
p_CARRIER_ID => p_carrier_id,
p_SERVICE_LEVEL => p_service_level,
p_MODE_OF_TRANSPORT => p_mode_of_transport,
p_VEHICLE_ITEM_ID => p_vehicle_item_id,
p_VEHICLE_ORG_ID => p_vehicle_org_id,
p_RANK_ID => null,
x_RETURN_STATUS => l_return_status,
x_MSG_COUNT => l_msg_count,
x_MSG_DATA => l_msg_data);
p_api_name => 'FTE_MLS_WRAPPER.CREATE_UPDATE_TRIP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_SEGMENT_UNEXP_ERROR',
p_trip_segment_id => p_wsh_trip_id,
p_trip_segment_name => p_wsh_trip_name,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
SELECT st.actual_arrival_date,st.actual_departure_date,
st.planned_arrival_date,st.planned_departure_date,
st.stop_sequence_number, st.status_code,
st.stop_location_id, st.trip_id, st.stop_id,
t.carrier_id carrier_id, t.lane_id lane_id,
t.schedule_id schedule_id, t.mode_of_transport mode_of_transport,
t.service_level service_level, t.ship_method_code ship_method_code,
t.name name, t.status_code trip_status,
t.load_tender_status, -- new 11/6 added by dmlewis
t.planned_flag, t.shipments_type_flag, t.vehicle_item_id,
t.vehicle_organization_id, dl.parent_delivery_leg_id,
t.rank_id, t.routing_rule_id, t.append_flag
FROM wsh_delivery_legs dl, wsh_trip_stops st, wsh_trips t
WHERE dl.delivery_leg_id = dLegId
AND dl.pick_up_stop_id = st.stop_id
AND t.trip_id = st.trip_id;
SELECT st.stop_id, st.physical_stop_id, st.trip_id,
st.stop_location_id, st.physical_location_id,
st.actual_arrival_date, st.actual_departure_date,
st.planned_arrival_date, st.planned_departure_date,
st.stop_sequence_number, st.status_code
FROM wsh_trip_stops st
WHERE physical_stop_id IS NULL
START WITH stop_id IN
(SELECT stop_id FROM wsh_trip_stops, wsh_delivery_legs
WHERE drop_off_stop_id = stop_id
AND delivery_leg_id = dlegId)
CONNECT BY PRIOR physical_stop_id = stop_id;
SELECT TOTAL_AMOUNT,CURRENCY_CODE FROM WSH_FREIGHT_COSTS
WHERE DELIVERY_LEG_ID = dLegId
AND LINE_TYPE_CODE = 'SUMMARY'
AND DELIVERY_DETAIL_ID IS NULL;
SELECT stop_id, status_code, stop_sequence_number,
stop_location_id,planned_departure_date,
planned_arrival_date
FROM wsh_trip_stops where trip_id = trip_seg_id
AND stop_sequence_number = (
SELECT min(stop_sequence_number)
FROM wsh_trip_stops where trip_id = trip_seg_id);
SELECT stop_id, status_code, stop_sequence_number,
stop_location_id,planned_departure_date,
planned_arrival_date, physical_location_id
FROM wsh_trip_stops where trip_id = trip_seg_id
AND stop_sequence_number = (
SELECT max(stop_sequence_number)
FROM wsh_trip_stops where trip_id = trip_seg_id);
SELECT meaning mode_of_transport_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'WSH_MODE_OF_TRANSPORT'
AND lookup_code = c_mode_code;
SELECT meaning service_type_meaning
FROM fnd_lookup_values_vl
WHERE lookup_type = 'WSH_SERVICE_LEVELS'
AND lookup_code = c_service_code;
SELECT lane_number
FROM fte_lanes
WHERE lane_id = c_lane_id;
SELECT sequence_number
FROM wsh_document_instances
WHERE entity_id = dLegId
AND entity_name = 'WSH_DELIVERY_LEGS'
AND document_type = 'BOL'
AND status = 'PLANNED';
SELECT wnd.delivery_id, wnd.name
FROM wsh_new_deliveries wnd, wsh_delivery_legs wdl
WHERE wdl.delivery_leg_id = c_parentDLegId
AND wdl.delivery_id = wnd.delivery_id;