[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_parameter_list.DELETE;
l_parameter_list.DELETE;
-- should delete tender snap shot
-- Standard call to get message count and if count is 1,get message info.
--
FND_MSG_PUB.Count_And_Get
(
p_count => x_msg_count,
p_data => x_msg_data,
p_encoded => FND_API.G_FALSE
);
l_parameter_list.DELETE;
PROCEDURE RAISE_TENDER_UPDATE(
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_item_key IN VARCHAR2,
p_tender_id IN NUMBER,
p_contact_perf IN VARCHAR2) IS
--{
l_parameter_list wf_parameter_list_t;
l_api_name CONSTANT VARCHAR2(30) := 'RAISE_TENDER_UPDATE';
SAVEPOINT RAISE_TENDER_UPDATE_PUB;
p_event_name => 'oracle.apps.fte.lt.tenderupdate',
p_event_key => GET_ITEM_KEY(p_tender_id),--p_item_key,
p_parameters => l_parameter_list
);
l_parameter_list.DELETE;
ROLLBACK TO RAISE_TENDER_UPDATE_PUB;
ROLLBACK TO RAISE_TENDER_UPDATE_PUB;
ROLLBACK TO RAISE_TENDER_UPDATE_PUB;
wsh_util_core.default_handler('FTE_TENDER_PVT.RAISE_TENDER_UPDATE');
END RAISE_TENDER_UPDATE;
l_parameter_list.DELETE;
AND (l_tender_action <> FTE_TENDER_PVT.S_SHIPPER_UPDATED))
THEN
l_return_value := FALSE;
SELECT fte_tender_id_s.nextval
INTO l_next_seq
FROM DUAL;
select trip_id, name,load_tender_status
from wsh_trips
where trip_id = c_trip_id;
PROCEDURE DELETE_TENDER_SNAPSHOT(
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_tender_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
--{
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_TENDER_SNAPSHOT';
SAVEPOINT DELETE_TENDER_SNAPSHOT_PUB;
-- first delete snapshot then create
SELECT count(*) INTO l_temp_id FROM FTE_TENDER_SNAPSHOT
WHERE load_tender_number = p_tender_id and rownum = 1;
DELETE FROM FTE_TENDER_SNAPSHOT
WHERE LOAD_TENDER_NUMBER = p_tender_id;
ROLLBACK TO DELETE_TENDER_SNAPSHOT_PUB;
ROLLBACK TO DELETE_TENDER_SNAPSHOT_PUB;
ROLLBACK TO DELETE_TENDER_SNAPSHOT_PUB;
wsh_util_core.default_handler('FTE_TENDER_PVT.DELETE_TENDER_SNAPSHOT');
END DELETE_TENDER_SNAPSHOT;
INSERT INTO FTE_TENDER_SNAPSHOT(
LOAD_TENDER_NUMBER,
TRIP_ID,
STOP_ID,
TOTAL_WEIGHT,
TOTAL_VOLUME,
WEIGHT_UOM,
VOLUME_UOM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SESSION_VALUE)
VALUES(
p_tender_id,
p_trip_id,
p_stop_id,
p_total_weight,
p_total_volume,
p_weight_uom,
p_volume_uom,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
p_session_value);
ELSIF (p_action = 'UPDATE') THEN
UPDATE FTE_TENDER_SNAPSHOT SET
TOTAL_WEIGHT = decode(p_total_weight,
NULL,total_weight,
FND_API.G_MISS_NUM,NULL,
p_total_weight),
TOTAL_VOLUME = decode(p_total_volume,
NULL,total_volume,
FND_API.G_MISS_NUM,NULL,
p_total_volume),
WEIGHT_UOM =decode(p_weight_uom,
NULL,weight_uom,
FND_API.G_MISS_CHAR,NULL,
p_weight_uom),
VOLUME_UOM =decode(p_volume_uom,
NULL,volume_uom,
FND_API.G_MISS_CHAR,NULL,
p_volume_uom),
SESSION_VALUE =decode(p_session_value,
NULL,SESSION_VALUE,
FND_API.G_MISS_CHAR,null,
p_session_value),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
WHERE TRIP_ID = p_trip_id
AND STOP_ID = p_stop_id
AND LOAD_TENDER_NUMBER = p_tender_id;
ELSIF (p_action = 'DELETE') THEN
DELETE FTE_TENDER_SNAPSHOT
WHERE LOAD_TENDER_NUMBER = p_tender_id;
SELECT STOP_ID, DEPARTURE_GROSS_WEIGHT, WEIGHT_UOM_CODE,
DEPARTURE_VOLUME, VOLUME_UOM_CODE
FROM WSH_TRIP_STOPS
WHERE TRIP_ID = p_trip_id
ORDER BY PLANNED_DEPARTURE_DATE, STOP_SEQUENCE_NUMBER,STOP_ID;
DELETE_TENDER_SNAPSHOT(
p_init_msg_list => FND_API.G_FALSE,
p_tender_id => p_trip_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
l_send_update BOOLEAN := FALSE;
select trip_id, stop_id, load_tender_number,session_value,
total_weight,total_volume,weight_uom,volume_uom
from fte_tender_snapshot
where trip_id = c_trip_id
and stop_id = c_stop_id;
select load_tender_number, wf_item_key,carrier_contact_id
from wsh_trips
where trip_id = c_trip_id;
select session_value
from fte_tender_snapshot
where trip_id = c_trip_id
and load_tender_number = c_tender_number
and session_value = c_session_value;
FTE_MLS_WRAPPER.CREATE_UPDATE_TRIP
( p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_action_code => 'UPDATE',
p_action => FTE_TENDER_PVT.S_SHIPPER_CANCELLED,
p_rec_TRIP_ID => l_trip_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);
-- Delete tender snapshot
TAKE_TENDER_SNAPSHOT(
p_init_msg_list => FND_API.G_FALSE,
p_tender_id => l_tender_number,
p_trip_id => l_trip_id,
p_stop_id => l_stop_id,
p_total_weight => null,
p_total_volume => null,
p_weight_uom => null,
p_volume_uom => null,
p_session_value => null,
p_action => 'DELETE',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
-- do we update the snapshot of this stop and just return back
RETURN;
-- if stop has them then update with stop values
-- if stop does not have them then technically we should throw
-- error. but still need to be decided so for time being
-- we just check the threshold value and see if it crossed with out
-- uom conversions.
l_stop_weight_uom := p_new_segment_stop_rec.weight_uom_code;
WSH_DEBUG_SV.logmsg(l_module_name,' Snapshot total weight is null send update ',WSH_DEBUG_SV.C_PROC_LEVEL);
l_send_update := TRUE;
WSH_DEBUG_SV.logmsg(l_module_name,' Snapshot total weight is not null send update ',WSH_DEBUG_SV.C_PROC_LEVEL);
l_send_update := TRUE;
WSH_DEBUG_SV.logmsg(l_module_name,' Snapshot total volume is null send update ',WSH_DEBUG_SV.C_PROC_LEVEL);
l_send_update := TRUE;
WSH_DEBUG_SV.logmsg(l_module_name,' Snapshot total volume is not null send update ',WSH_DEBUG_SV.C_PROC_LEVEL);
l_send_update := TRUE;
IF (NOT l_send_update)
THEN
-- check threshold value
IF (l_site_trans_rec.WEIGHT_THRESHOLD_LOWER <> FND_API.G_MISS_NUM)
THEN
-- Fix for Bug 2783938
IF ( (ABS(l_snap_tot_weight - (l_snap_tot_weight*l_site_trans_rec.WEIGHT_THRESHOLD_LOWER*0.01)))
> p_new_segment_stop_rec.departure_gross_weight)
THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,' Send Message Because of WEIGHT_THRESHOLD_LOWER ',WSH_DEBUG_SV.C_PROC_LEVEL);
l_send_update := TRUE;
l_send_update := TRUE;
IF (NOT l_send_update)
THEN
-- check threshold value
IF (l_site_trans_rec.VOLUME_THRESHOLD_LOWER <> FND_API.G_MISS_NUM)
THEN
-- Fix for Bug 2783938
IF ( (ABS(l_snap_tot_volume - (l_snap_tot_volume*l_site_trans_rec.VOLUME_THRESHOLD_LOWER*0.01)))
> p_new_segment_stop_rec.departure_volume)
THEN
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,' Send Message Because of VOLUME_THRESHOLD_LOWER ',WSH_DEBUG_SV.C_PROC_LEVEL);
l_send_update := TRUE;
l_send_update := TRUE;
-- update the snapshot with current information
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,' Tender Number ' || l_tender_number,WSH_DEBUG_SV.C_PROC_LEVEL);
WSH_DEBUG_SV.logmsg(l_module_name,'Update Snapshot with stop info ',WSH_DEBUG_SV.C_PROC_LEVEL);
p_action => 'UPDATE',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IF (l_send_update)
THEN
l_is_session_found := FALSE;
-- update tender snapshot with session value
-- so that we do not send another message to carrier
TAKE_TENDER_SNAPSHOT(
p_init_msg_list => FND_API.G_FALSE,
p_tender_id => l_tender_number,
p_trip_id => l_trip_id,
p_stop_id => l_stop_id,
p_total_weight => NULL,
p_total_volume => NULL,
p_weight_uom => NULL,
p_volume_uom => NULL,
p_session_value => userenv('SESSIONID') || '',
p_action => 'UPDATE',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
FTE_MLS_WRAPPER.CREATE_UPDATE_TRIP
( p_api_version_number => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_action_code => 'UPDATE',
p_action => FTE_TENDER_PVT.S_SHIPPER_UPDATED,
p_rec_TRIP_ID => l_trip_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name);
CREATE_UPDATE_TRIP ' || x_return_status,WSH_DEBUG_SV.C_PROC_LEVEL);
CREATE_UPDATE_TRIP ' || x_msg_data,WSH_DEBUG_SV.C_PROC_LEVEL);
SELECT user_name
FROM fnd_user
WHERE customer_id = carrier_contact_id;
SELECT user_name
FROM fnd_user
WHERE person_party_id = carrier_contact_id;
--This Section Updates the display name of Newly/Existing Fte_Tender: --
--with the display name of Existing HZ_PARTY: . --
------------------------------------------------------------------------------------
l_orig_system := 'HZ_PARTY';
' Display Name of '||l_role_name||' has been updated',
WSH_DEBUG_SV.C_PROC_LEVEL);
p_action = FTE_TENDER_PVT.S_SHIPPER_UPDATED)
THEN
HANDLE_WF_ROLES(
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,
x_role_name => l_role_name,
p_contact_name => p_contact_perf);
ELSIF (p_action = FTE_TENDER_PVT.S_SHIPPER_UPDATED) THEN
RAISE_TENDER_UPDATE(
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_item_key => p_item_key,
p_tender_id => p_tender_id,
--p_contact_perf => p_contact_perf);
SELECT PLANNED_DEPARTURE_DATE, CARRIER_EST_DEPARTURE_DATE, STOP_LOCATION_ID FROM wsh_trip_stops
WHERE trip_id = p_tender_id AND
PLANNED_DEPARTURE_DATE = (SELECT MIN(PLANNED_DEPARTURE_DATE)
FROM wsh_trip_stops
WHERE trip_id = p_tender_id );
SELECT PLANNED_ARRIVAL_DATE, CARRIER_EST_ARRIVAL_DATE, STOP_LOCATION_ID FROM wsh_trip_stops
WHERE trip_id = p_tender_id AND
PLANNED_ARRIVAL_DATE = (SELECT MAX(PLANNED_ARRIVAL_DATE)
FROM wsh_trip_stops
WHERE trip_id = p_tender_id );
select exception_id, status from
wsh_exceptions we, wsh_trips wt where we.trip_id = wt.trip_id and
(we.exception_name = 'FTE_CARRIER_PTIME' OR
we.exception_name = 'FTE_CARRIER_DTIME') AND
we.status <> 'CLOSED' AND
wt.trip_id = p_tender_id;
UPDATE wsh_trip_stops
SET CARRIER_EST_DEPARTURE_DATE = null
where stop_id in
(
select stop_id from wsh_trip_stops
where trip_id = p_tender_id and
PLANNED_DEPARTURE_DATE = (
select min(PLANNED_DEPARTURE_DATE) from wsh_trip_stops
where trip_id = p_tender_id
)
);
UPDATE wsh_trip_stops
SET CARRIER_EST_ARRIVAL_DATE = null
where stop_id in
(
select stop_id from wsh_trip_stops
where trip_id = p_tender_id and
PLANNED_ARRIVAL_DATE = (
select max(PLANNED_ARRIVAL_DATE) from wsh_trip_stops
where trip_id = p_tender_id
)
);
l_parameter_list.DELETE;
PROCEDURE UPDATE_CARRIER_RESPONSE(
p_init_msg_list IN VARCHAR2 ,
p_carrier_response_rec IN FTE_TENDER_ATTR_REC,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name VARCHAR2(30) := 'UPDATE_CARRIER_RESPONSE';
l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'UPDATE_CARRIER_RESPONSE';
SAVEPOINT UPDATE_CARRIER_RESPONSE_PUB;
UPDATE wsh_trip_stops
SET CARRIER_EST_DEPARTURE_DATE =
p_carrier_response_rec.carrier_pickup_date
where stop_id in
(
select stop_id from wsh_trip_stops
where trip_id = p_carrier_response_rec.trip_id and
PLANNED_DEPARTURE_DATE = (
select min(PLANNED_DEPARTURE_DATE) from wsh_trip_stops
where trip_id = p_carrier_response_rec.trip_id
)
);
UPDATE wsh_trip_stops
SET CARRIER_EST_ARRIVAL_DATE =
p_carrier_response_rec.carrier_dropoff_date
where stop_id in
(
select stop_id from wsh_trip_stops
where trip_id = p_carrier_response_rec.trip_id and
PLANNED_ARRIVAL_DATE = (
select max(PLANNED_ARRIVAL_DATE) from wsh_trip_stops
where trip_id = p_carrier_response_rec.trip_id
)
);
-- Update trip information
p_trip_info.TRIP_ID := p_carrier_response_rec.trip_id;
p_trip_in_rec.action_code :='UPDATE';
WSH_INTERFACE_GRP.Create_Update_Trip
(
p_api_version_number =>1.0,
p_init_msg_list =>FND_API.G_FALSE,
p_commit =>FND_API.G_FALSE,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_trip_info_tab =>p_trip_info_tab,
p_in_rec =>p_trip_in_rec,
x_out_tab =>x_out_tab
);
WSH_DEBUG_SV.logmsg(l_module_name,' Return Status aftere calling WSH_INTERFACE_GRP.CREATE_UPDATE_TRIP ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
-- Update trip informatio
FTE_MLS_WRAPPER.INITIALIZE_TRIP_REC(x_trip_info => p_trip_info);
p_trip_in_rec.action_code :='UPDATE';
WSH_INTERFACE_GRP.Create_Update_Trip
(
p_api_version_number =>1.0,
p_init_msg_list =>FND_API.G_FALSE,
p_commit =>FND_API.G_FALSE,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_trip_info_tab =>p_trip_info_tab,
p_in_rec =>p_trip_in_rec,
x_out_tab =>x_out_tab
);
WSH_DEBUG_SV.logmsg(l_module_name,' Return Status aftere calling WSH_INTERFACE_GRP.CREATE_UPDATE_TRIP ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
ROLLBACK TO UPDATE_CARRIER_RESPONSE_PUB;
ROLLBACK TO UPDATE_CARRIER_RESPONSE_PUB;
ROLLBACK TO UPDATE_CARRIER_RESPONSE_PUB;
wsh_util_core.default_handler('FTE_TENDER_PVT.UPDATE_CARRIER_RESPONSE');
END UPDATE_CARRIER_RESPONSE;
UPDATE_CARRIER_RESPONSE(
p_init_msg_list => FND_API.G_FALSE,
p_carrier_response_rec => p_trip_info,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
WSH_DEBUG_SV.logmsg(l_module_name,' Return Status aftere calling CREATE_UPDATE_TRIP ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
l_parameter_list.DELETE;
-- Update trip information
p_wsh_trip_info.TRIP_ID := p_trip_info.trip_id;
p_trip_in_rec.action_code :='UPDATE';
WSH_INTERFACE_GRP.Create_Update_Trip
(
p_api_version_number =>1.0,
p_init_msg_list =>FND_API.G_FALSE,
p_commit =>FND_API.G_FALSE,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data,
p_trip_info_tab =>p_trip_info_tab,
p_in_rec =>p_trip_in_rec,
x_out_tab =>x_out_tab
);
WSH_DEBUG_SV.logmsg(l_module_name,' Return Status aftere calling WSH_INTERFACE_GRP.CREATE_UPDATE_TRIP ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
l_parameter_list.DELETE;
PROCEDURE HANDLE_UPDATE_TENDER(
p_init_msg_list IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_trip_info IN FTE_TENDER_ATTR_REC) IS
--{
l_parameter_list wf_parameter_list_t;
l_api_name CONSTANT VARCHAR2(30) := 'HANDLE_UPDATE_TENDER';
SAVEPOINT RAISE_TENDER_UPDATE_PUB;
WSH_DEBUG_SV.logmsg(l_module_name,' Sending update tender notification. ',
WSH_DEBUG_SV.C_PROC_LEVEL);
p_event_name => 'oracle.apps.fte.lt.tenderupdate',
p_event_key => GET_ITEM_KEY(p_trip_info.trip_id),--p_item_key,
p_parameters => l_parameter_list
);
ROLLBACK TO HANDLE_UPDATE_TENDER_PUB;
ROLLBACK TO HANDLE_UPDATE_TENDER_PUB;
ROLLBACK TO HANDLE_UPDATE_TENDER_PUB;
wsh_util_core.default_handler('FTE_TENDER_PVT.HANDLE_UPDATE_TENDER');
END HANDLE_UPDATE_TENDER;