[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_NONE CONSTANT VARCHAR2(30) := 'NONE'; -- meaningful for insert opeation
SELECT
STOP_ID,
STOP_LOCATION_ID,
STOP_SEQUENCE_NUMBER,
PLANNED_ARRIVAL_DATE,
PLANNED_DEPARTURE_DATE,
G_NO_CHANGE ACTION_TYPE
FROM wsh_trip_stops
WHERE trip_id = p_segment_id
UNION ALL
SELECT
l_current_stop_id stop_id,
p_current_stop_rec.STOP_LOCATION_ID stop_location_id,
p_current_stop_rec.STOP_SEQUENCE_NUMBER stop_sequencE_number,
p_current_stop_rec.PLANNED_ARRIVAL_DATE planned_arrival_date,
p_current_stop_rec.PLANNED_DEPARTURE_DATE planned_departure_date,
p_action_type action_type
FROM DUAL
order by 3;
SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
FROM fte_wsh_trips a, fte_trips b
WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
AND a.fte_trip_id = b.fte_trip_id;
UPDATE fte_trips
SET validation_required = 'Y'
WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
FROM fte_wsh_trips a, fte_trips b
WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
AND a.fte_trip_id = b.fte_trip_id;
UPDATE fte_trips
SET validation_required = 'Y'
WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
FROM fte_wsh_trips a, fte_trips b
WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
AND a.fte_trip_id = b.fte_trip_id;
PROCEDURE validate_stop_update
(
P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
P_trip_stop_tab IN Trip_Stop_Tab_type,
p_current_stop_index IN NUMBER,
p_curr_stop_old_position IN VARCHAR2,
p_old_stop_rec IN WSH_TRIP_STOPS_PVT.trip_stop_rec_type,
p_schedule_start_datetime IN DATE,
p_schedule_end_datetime IN DATE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--
--
l_new_stop_rec trip_stop_rec_type;
SELECT
dg.delivery_id, dl.name
FROM wsh_new_deliveries dl,
wsh_delivery_legs dg,
wsh_trip_stops st
WHERE 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 st.stop_id = p_stop_id;
SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
FROM fte_wsh_trips a, fte_trips b
WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
AND a.fte_trip_id = b.fte_trip_id;
wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_UPDATE');
END validate_stop_update;
PROCEDURE validate_stop_delete
(
P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
P_trip_stop_tab IN Trip_Stop_Tab_type,
p_current_stop_index IN NUMBER,
p_schedule_start_datetime IN DATE,
p_schedule_end_datetime IN DATE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
l_deleted_stop_rec trip_stop_rec_type;
SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
FROM fte_wsh_trips a, fte_trips b
WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
AND a.fte_trip_id = b.fte_trip_id;
l_deleted_stop_rec := p_trip_stop_tab(p_current_stop_index);
UPDATE fte_trips
SET validation_required = 'Y'
WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
UPDATE fte_trips
SET validation_required = 'Y'
WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
FND_MESSAGE.SET_NAME('FTE', 'FTE_STOP_DELETE_ERROR');
wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_DELETE');
END validate_stop_delete;
IF p_action_type IN ( G_ADD, G_UPDATE )
THEN
l_current_stop_rec := p_new_segment_stop_rec;
ELSIF p_action_type = G_DELETE
THEN
l_current_stop_rec := p_old_segment_stop_rec;
ELSIF p_action_type = G_TRIP_SEGMENT_DELETE
THEN
NULL;
ELSIF p_action_type = G_UPDATE
THEN
--{
l_program_name := 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_UPDATE';
validate_stop_update
(
P_trip_segment_rec => p_trip_segment_rec,
P_trip_stop_tab => l_trip_stop_tab,
p_current_stop_index => l_current_stop_index,
p_curr_stop_old_position => l_curr_stop_old_position,
p_old_stop_rec => p_old_segment_stop_rec,
p_schedule_start_datetime => l_schedule_start_datetime,
p_schedule_end_datetime => l_schedule_end_datetime,
X_return_status => l_return_status
);
wsh_debug_sv.log (l_module_name,' After validate stop update');
ELSIF p_action_type = G_DELETE
THEN
--{
l_program_name := 'FTE_WSH_INTERFACE_PKG.VALIDATE_STOP_DELETE';
validate_stop_delete
(
P_trip_segment_rec => p_trip_segment_rec,
P_trip_stop_tab => l_trip_stop_tab,
p_current_stop_index => l_current_stop_index,
p_schedule_start_datetime => l_schedule_start_datetime,
p_schedule_end_datetime => l_schedule_end_datetime,
X_return_status => l_return_status
);
PROCEDURE validate_segment_update
(
P_new_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
P_old_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--
--
l_next_segment_id NUMBER;
SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
FROM fte_wsh_trips a, fte_trips b
WHERE a.wsh_trip_id = p_new_trip_segment_rec.trip_id
AND a.fte_trip_id = b.fte_trip_id;
SELECT b.status_code, b.trip_id, b.name wsh_trip_name
FROM fte_wsh_trips a, wsh_trips b
WHERE a.fte_trip_id = p_trip_id
AND a.sequence_number < p_sequence_number
AND a.wsh_trip_id = b.trip_id;
SELECT status_code, name
FROM wsh_trips
WHERE trip_id = p_segment_id;
-- Release 12: Added so that FTE can update lane_id without first having to null out lane_id
IF p_new_trip_segment_rec.lane_id = p_old_trip_segment_rec.lane_id
AND NVL(p_new_trip_segment_rec.ship_method_code, '!-') <> NVL(p_old_trip_segment_rec.ship_method_code,'!-')
THEN
FND_MESSAGE.SET_NAME('FTE', 'FTE_SEGMENT_CSM_CHANGE_ERROR');
UPDATE fte_trips
SET status_code = 'IT',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id
AND status_code = 'OP';
UPDATE fte_trips
SET status_code = 'CL',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_SEGMENT_UPDATE');
END validate_segment_update;
PROCEDURE validate_segment_delete
(
P_trip_segment_rec IN WSH_TRIPS_PVT.Trip_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
--{
--
l_next_segment_id NUMBER;
SELECT b.fte_trip_id, sequence_number, b.name fte_trip_name
FROM fte_wsh_trips a, fte_trips b
WHERE a.wsh_trip_id = p_trip_segment_rec.trip_id
AND a.fte_trip_id = b.fte_trip_id;
SELECT status_code, name
FROM wsh_trips
WHERE trip_id = p_segment_id;
fte_wsh_trips_pvt.delete_trip
(
p_fte_trip_id => get_segment_trips_rec.fte_trip_id,
P_wsh_trip_id => p_trip_segment_rec.trip_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
p_api_name => 'FTE_WSH_TRIPS_PUB.DELETE_TRIP',
p_api_return_status => l_return_status,
p_message_name => 'FTE_TRIP_SEGMENT_UNEXP_ERROR',
p_trip_segment_id => p_trip_segment_rec.trip_id,
p_trip_segment_name => p_trip_segment_rec.name,
p_trip_id => get_segment_trips_rec.fte_trip_id,
p_trip_name => get_segment_trips_rec.fte_trip_name,
x_number_of_errors => l_number_of_errors,
x_number_of_warnings => l_number_of_warnings,
x_return_status => x_return_status
);
UPDATE fte_trips
SET status_code = 'CL',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE fte_trip_id = get_segment_trips_rec.fte_trip_id;
wsh_util_core.default_handler('FTE_WSH_INTERFACE_PKG.VALIDATE_SEGMENT_DELETE');
END validate_segment_delete;
ELSIF p_action_type = G_UPDATE
THEN
--{
l_program_name := 'FTE_WSH_INTERFACE_PKG.VALIDATE_SEGMENT_UPDATE';
validate_segment_update
(
P_old_trip_segment_rec => p_old_trip_segment_rec,
P_new_trip_segment_rec => p_new_trip_segment_rec,
X_return_status => l_return_status
);
ELSIF p_action_type = G_DELETE
THEN
--{
l_program_name := 'FTE_WSH_INTERFACE_PKG.VALIDATE_SEGMENT_DELETE';
validate_segment_delete
(
P_trip_segment_rec => p_old_trip_segment_rec,
X_return_status => l_return_status
);
SELECT DECODE(FPG.MULTI_ORG_FLAG,'Y',
DECODE(HOI2.ORG_INFORMATION_CONTEXT,'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION3),
TO_NUMBER(NULL)),
TO_NUMBER(NULL)) OPERATING_UNIT
FROM HR_ORGANIZATION_UNITS HOU,
HR_ORGANIZATION_INFORMATION HOI2,
FND_PRODUCT_GROUPS FPG
WHERE HOU.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
AND( HOI2.ORG_INFORMATION_CONTEXT || '')='Accounting Information'
AND HOU.ORGANIZATION_ID =c_organization_id;
SELECT TRIP_ID INTO l_trip_id
FROM WSH_TRIP_STOPS
WHERE STOP_ID = p_entity_id;
SELECT ORGANIZATION_ID INTO X_ORGANIZATION_ID
FROM WSH_NEW_DELIVERIES
WHERE DELIVERY_ID = p_entity_id;
SELECT ORGANIZATION_ID,
ORG_ID INTO X_ORGANIZATION_ID, L_ORG_ID
FROM WSH_DELIVERY_DETAILS
WHERE DELIVERY_DETAIL_ID = p_entity_id;