The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_RANK(
p_rank_info_rec IN carrier_rank_list_rec,
x_return_status OUT NOCOPY VARCHAR2
) IS
BEGIN
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
UPDATE FTE_CARRIER_RANK_LIST SET
CARRIER_ID = decode(p_rank_info_rec.CARRIER_ID,NULL,CARRIER_ID,
FND_API.G_MISS_NUM,NULL,
p_rank_info_rec.CARRIER_ID),
SERVICE_LEVEL =decode(p_rank_info_rec.SERVICE_LEVEL,NULL,SERVICE_LEVEL,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.SERVICE_LEVEL),
MODE_OF_TRANSPORT = decode(p_rank_info_rec.MODE_OF_TRANSPORT,NULL,MODE_OF_TRANSPORT,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.MODE_OF_TRANSPORT),
LANE_ID = decode(p_rank_info_rec.LANE_ID,NULL,LANE_ID,
FND_API.G_MISS_NUM,NULL,
p_rank_info_rec.LANE_ID),
--SOURCE = decode(p_rank_info_rec.SOURCE,NULL,SOURCE,
-- FND_API.G_MISS_CHAR,NULL,
-- p_rank_info_rec.SOURCE),
ENABLED = decode(p_rank_info_rec.ENABLED,NULL,ENABLED,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ENABLED),
ESTIMATED_RATE = decode(p_rank_info_rec.ESTIMATED_RATE,NULL,ESTIMATED_RATE,
FND_API.G_MISS_NUM,NULL,
p_rank_info_rec.ESTIMATED_RATE),
CURRENCY_CODE = decode(p_rank_info_rec.CURRENCY_CODE,NULL,CURRENCY_CODE,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.CURRENCY_CODE),
VEHICLE_ITEM_ID = decode(p_rank_info_rec.VEHICLE_ITEM_ID,NULL,VEHICLE_ITEM_ID,
FND_API.G_MISS_NUM,NULL,
p_rank_info_rec.VEHICLE_ITEM_ID),
VEHICLE_ORG_ID = decode(p_rank_info_rec.VEHICLE_ORG_ID,NULL,VEHICLE_ORG_ID,
FND_API.G_MISS_NUM,NULL,
p_rank_info_rec.VEHICLE_ORG_ID),
ESTIMATED_TRANSIT_TIME = decode(p_rank_info_rec.ESTIMATED_TRANSIT_TIME,NULL,ESTIMATED_TRANSIT_TIME,
FND_API.G_MISS_NUM,NULL,
p_rank_info_rec.ESTIMATED_TRANSIT_TIME),
TRANSIT_TIME_UOM = decode(p_rank_info_rec.TRANSIT_TIME_UOM,NULL,TRANSIT_TIME_UOM,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.TRANSIT_TIME_UOM),
VERSION = decode(p_rank_info_rec.VERSION,NULL,VERSION,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.VERSION),
CONSIGNEE_CARRIER_AC_NO = decode(p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO,NULL,CONSIGNEE_CARRIER_AC_NO,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO),
FREIGHT_TERMS_CODE = decode(p_rank_info_rec.FREIGHT_TERMS_CODE,NULL,FREIGHT_TERMS_CODE,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.FREIGHT_TERMS_CODE),
CALL_RG_FLAG = decode(p_rank_info_rec.CALL_RG_FLAG,NULL,CALL_RG_FLAG,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.CALL_RG_FLAG),
ATTRIBUTE_CATEGORY = decode(p_rank_info_rec.ATTRIBUTE_CATEGORY,NULL,ATTRIBUTE_CATEGORY,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE_CATEGORY),
ATTRIBUTE1 = decode(p_rank_info_rec.ATTRIBUTE1,NULL,ATTRIBUTE1,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE1),
ATTRIBUTE2 = decode(p_rank_info_rec.ATTRIBUTE2,NULL,ATTRIBUTE2,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE2),
ATTRIBUTE3 = decode(p_rank_info_rec.ATTRIBUTE3,NULL,ATTRIBUTE3,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE3),
ATTRIBUTE4 = decode(p_rank_info_rec.ATTRIBUTE4,NULL,ATTRIBUTE4,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE4),
ATTRIBUTE5 = decode(p_rank_info_rec.ATTRIBUTE5,NULL,ATTRIBUTE5,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE5),
ATTRIBUTE6 = decode(p_rank_info_rec.ATTRIBUTE6,NULL,ATTRIBUTE6,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE6),
ATTRIBUTE7 = decode(p_rank_info_rec.ATTRIBUTE7,NULL,ATTRIBUTE7,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE7),
ATTRIBUTE8 = decode(p_rank_info_rec.ATTRIBUTE8,NULL,ATTRIBUTE8,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE8),
ATTRIBUTE9 = decode(p_rank_info_rec.ATTRIBUTE9,NULL,ATTRIBUTE9,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE9),
ATTRIBUTE10 = decode(p_rank_info_rec.ATTRIBUTE10,NULL,ATTRIBUTE10,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE10),
ATTRIBUTE11 = decode(p_rank_info_rec.ATTRIBUTE11,NULL,ATTRIBUTE11,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE11),
ATTRIBUTE12 = decode(p_rank_info_rec.ATTRIBUTE12,NULL,ATTRIBUTE12,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE12),
ATTRIBUTE13 = decode(p_rank_info_rec.ATTRIBUTE13,NULL,ATTRIBUTE13,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE13),
ATTRIBUTE14 = decode(p_rank_info_rec.ATTRIBUTE14,NULL,ATTRIBUTE14,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE14),
ATTRIBUTE15 = decode(p_rank_info_rec.ATTRIBUTE15,NULL,ATTRIBUTE15,
FND_API.G_MISS_CHAR,NULL,
p_rank_info_rec.ATTRIBUTE15),
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE rank_id = p_rank_info_rec.rank_id;
wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.UPDATE');
END UPDATE_RANK;
SELECT trip_id INTO l_trip_id FROM FTE_CARRIER_RANK_LIST
WHERE TRIP_ID = p_trip_id
AND ROWNUM = 1;
Log(l_module_name,' Calling Update Rank to update version ' ,
WSH_DEBUG_SV.C_PROC_LEVEL);
UPDATE_RANK(p_rank_info_rec => p_rank_info_rec,
x_return_status => l_return_status);
-- Update trip with this rank id
IF (l_return_status = 'E')
THEN
RAISE FND_API.G_EXC_ERROR;
Log(l_module_name,' Update trip with rank id ' || p_rank_info_rec.rank_id,
WSH_DEBUG_SV.C_PROC_LEVEL);
-- Update trip information
p_trip_info.RANK_ID := p_rank_info_rec.rank_id;
p_trip_in_rec.action_code :='UPDATE';
WSH_DEBUG_SV.logmsg(l_module_name,' Before calling CREATE_UPDATE_TRIP ',
WSH_DEBUG_SV.C_PROC_LEVEL);
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 value from Create update trip ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
PROCEDURE DELETE_RANK_LIST(
p_trip_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RANK';
SELECT LOAD_TENDER_STATUS FROM WSH_TRIPS
WHERE TRIP_ID = p_trip_id;
-- Check tender status of trip if it is tendered / accepted we cannot delete rank list
OPEN GET_TENDER_STATUS;
FND_MESSAGE.SET_NAME('FTE','FTE_CNT_DELETE_TENDER_CHECK');
DELETE FROM fte_carrier_rank_list
WHERE trip_id = p_trip_id;
SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_trip_id;
-- Update trip information
p_trip_info.RANK_ID := NULL;
p_trip_in_rec.action_code :='UPDATE';
WSH_DEBUG_SV.logmsg(l_module_name,' Before calling CREATE_UPDATE_TRIP ',
WSH_DEBUG_SV.C_PROC_LEVEL);
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 value from Create update trip ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
Log(l_module_name,' Return value from DELETE_RANK_LIST ' || x_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.DELETE_RANK_LIST');
END DELETE_RANK_LIST;
SELECT fte_carrier_rank_list_s.nextval
FROM sys.dual;
insert into FTE_CARRIER_RANK_LIST
(
RANK_ID ,
TRIP_ID ,
RANK_SEQUENCE ,
CARRIER_ID ,
SERVICE_LEVEL ,
MODE_OF_TRANSPORT ,
LANE_ID ,
SOURCE ,
ENABLED ,
ESTIMATED_RATE ,
CURRENCY_CODE ,
VEHICLE_ITEM_ID ,
VEHICLE_ORG_ID ,
ESTIMATED_TRANSIT_TIME ,
TRANSIT_TIME_UOM ,
VERSION ,
CONSIGNEE_CARRIER_AC_NO ,
FREIGHT_TERMS_CODE ,
INITSMCONFIG ,
CALL_RG_FLAG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
values
(
x_rank_id,
p_rank_info_rec.TRIP_ID,
p_rank_info_rec.RANK_SEQUENCE,
decode(p_rank_info_rec.CARRIER_ID,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.CARRIER_ID),
decode(p_rank_info_rec.SERVICE_LEVEL,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.SERVICE_LEVEL),
decode(p_rank_info_rec.MODE_OF_TRANSPORT,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.MODE_OF_TRANSPORT),
decode(p_rank_info_rec.LANE_ID,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.LANE_ID),
decode(p_rank_info_rec.SOURCE,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.SOURCE),
'Y',
decode(p_rank_info_rec.ESTIMATED_RATE,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.ESTIMATED_RATE),
decode(p_rank_info_rec.CURRENCY_CODE,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.CURRENCY_CODE),
decode(p_rank_info_rec.VEHICLE_ITEM_ID,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.VEHICLE_ITEM_ID),
decode(p_rank_info_rec.VEHICLE_ORG_ID,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.VEHICLE_ORG_ID),
decode(p_rank_info_rec.ESTIMATED_TRANSIT_TIME,FND_API.G_MISS_NUM,NULL,p_rank_info_rec.ESTIMATED_TRANSIT_TIME),
decode(p_rank_info_rec.TRANSIT_TIME_UOM,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.TRANSIT_TIME_UOM),
0,
decode(p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.CONSIGNEE_CARRIER_AC_NO),
decode(p_rank_info_rec.FREIGHT_TERMS_CODE,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.FREIGHT_TERMS_CODE),
l_config,
decode(p_rank_info_rec.CALL_RG_FLAG,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.CALL_RG_FLAG),
decode(p_rank_info_rec.ATTRIBUTE_CATEGORY,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE_CATEGORY),
decode(p_rank_info_rec.ATTRIBUTE1,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE1),
decode(p_rank_info_rec.ATTRIBUTE2,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE2),
decode(p_rank_info_rec.ATTRIBUTE3,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE3),
decode(p_rank_info_rec.ATTRIBUTE4,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE4),
decode(p_rank_info_rec.ATTRIBUTE5,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE5),
decode(p_rank_info_rec.ATTRIBUTE6,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE6),
decode(p_rank_info_rec.ATTRIBUTE7,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE7),
decode(p_rank_info_rec.ATTRIBUTE8,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE8),
decode(p_rank_info_rec.ATTRIBUTE9,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE9),
decode(p_rank_info_rec.ATTRIBUTE10,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE10),
decode(p_rank_info_rec.ATTRIBUTE11,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE11),
decode(p_rank_info_rec.ATTRIBUTE12,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE12),
decode(p_rank_info_rec.ATTRIBUTE13,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE13),
decode(p_rank_info_rec.ATTRIBUTE14,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE14),
decode(p_rank_info_rec.ATTRIBUTE15,FND_API.G_MISS_CHAR,NULL,p_rank_info_rec.ATTRIBUTE15),
decode(p_rank_info_rec.creation_date,NULL,SYSDATE,FND_API.G_MISS_DATE,SYSDATE,p_rank_info_rec.creation_date),
decode(p_rank_info_rec.created_by,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,p_rank_info_rec.created_by),
decode(p_rank_info_rec.last_update_date,NULL,SYSDATE,FND_API.G_MISS_DATE,SYSDATE, p_rank_info_rec.last_update_date),
decode(p_rank_info_rec.last_updated_by,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.USER_ID,p_rank_info_rec.last_updated_by),
decode(p_rank_info_rec.last_update_login,NULL,FND_GLOBAL.USER_ID,FND_API.G_MISS_NUM,FND_GLOBAL.LOGIN_ID,p_rank_info_rec.last_update_login)
);
SELECT trip_id INTO l_trip_id FROM FTE_CARRIER_RANK_LIST
WHERE TRIP_ID = p_trip_id
AND ROWNUM = 1;
SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_trip_id;
SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_trip_id;
PROCEDURE UPDATE_RANK_LIST(
p_ranklist IN OUT NOCOPY carrier_rank_list_tbl_type,
p_trip_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RANK_LIST';
SELECT trip_id INTO l_trip_id FROM FTE_CARRIER_RANK_LIST
WHERE TRIP_ID = p_trip_id
AND ROWNUM = 1;
SAVEPOINT UPDATE_RANK_LIST_PUB;
SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_trip_id;
UPDATE_RANK(p_rank_info_rec => p_ranklist(i),
x_return_status => l_return_status);
ROLLBACK TO UPDATE_RANK_LIST_PUB;
ROLLBACK TO UPDATE_RANK_LIST_PUB;
ROLLBACK TO UPDATE_RANK_LIST_PUB;
WSH_UTIL_CORE.DEFAULT_HANDLER('FTE_CARRIER_RANK_LIST_PVT.UPDATE_RANK_LIST');
END UPDATE_RANK_LIST;
SELECT MAX(RANK_SEQUENCE)
FROM FTE_CARRIER_RANK_LIST
WHERE TRIP_ID = p_trip_id;
SELECT MAX(RANK_SEQUENCE) INTO l_max_rank_seq
FROM FTE_CARRIER_RANK_LIST
WHERE TRIP_ID = p_trip_id;
SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_trip_id;
ELSIF (p_action_code = S_UPDATE) THEN
IF l_debug_on
THEN
Log(l_module_name,' Updating Rank List for trip. TBD ' || p_trip_id,
WSH_DEBUG_SV.C_PROC_LEVEL);
UPDATE_RANK_LIST(
p_ranklist => p_ranklist,
p_trip_id => p_trip_id,
x_return_status => l_return_status);
Log(l_module_name,' l_return_status after UPDATE_RANK_LIST ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
ELSIF (p_action_code = S_DELETE) THEN
IF l_debug_on
THEN
Log(l_module_name,' Delete Rank List for trip ' || p_trip_id,
WSH_DEBUG_SV.C_PROC_LEVEL);
DELETE_RANK_LIST(p_trip_id => p_trip_id,
x_return_status => l_return_status);
Log(l_module_name,' l_return_status after DELETE_RANK_LIST ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
SELECT VERSION INTO l_current_version
FROM FTE_CARRIER_RANK_LIST
WHERE RANK_ID = p_rank_id;
UPDATE_RANK(p_rank_info_rec => l_carrier_rank_list_rec,
x_return_status => l_return_status);
Log(l_module_name,' After calling UPDATE_RANK ' || l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
Log(l_module_name,' Update trip with rank id ' ,
WSH_DEBUG_SV.C_PROC_LEVEL);
-- Update trip information
p_trip_info.RANK_ID := p_rank_id;
p_trip_in_rec.action_code :='UPDATE';
WSH_DEBUG_SV.logmsg(l_module_name,' Before calling CREATE_UPDATE_TRIP ' ||
' Rank list Action ',
WSH_DEBUG_SV.C_PROC_LEVEL);
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 value from Create update trip ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
Log(l_module_name,' Replace existing rank list. Calling Delete first ' || p_trip_id,
WSH_DEBUG_SV.C_PROC_LEVEL);
SELECT count(*) INTO l_temp_id FROM FTE_CARRIER_RANK_LIST
WHERE TRIP_ID = p_trip_id and rownum = 1;
-- First call delete on rank list
DELETE_RANK_LIST(p_trip_id => p_trip_id,
x_return_status => l_return_status);
Log(l_module_name,' l_return_status after DELETE_RANK_LIST ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
SELECT RANK_ID INTO l_temp_id
FROM WSH_TRIPS
where TRIP_ID = p_trip_id;
SELECT RANK_ID ,
TRIP_ID ,
RANK_SEQUENCE ,
CARRIER_ID ,
SERVICE_LEVEL ,
MODE_OF_TRANSPORT ,
LANE_ID ,
SOURCE ,
ENABLED ,
ESTIMATED_RATE ,
CURRENCY_CODE ,
VEHICLE_ITEM_ID ,
ESTIMATED_TRANSIT_TIME ,
TRANSIT_TIME_UOM ,
VERSION ,
CONSIGNEE_CARRIER_AC_NO ,
FREIGHT_TERMS_CODE ,
INITSMCONFIG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
'N',0,'SORT',NULL,NULL,-99,VEHICLE_ORG_ID,
CALL_RG_FLAG
FROM FTE_CARRIER_RANK_LIST WHERE RANK_ID = p_rank_id;
SELECT RANK_ID ,
TRIP_ID ,
RANK_SEQUENCE ,
CARRIER_ID ,
SERVICE_LEVEL ,
MODE_OF_TRANSPORT ,
LANE_ID ,
SOURCE ,
ENABLED ,
ESTIMATED_RATE ,
CURRENCY_CODE ,
VEHICLE_ITEM_ID ,
ESTIMATED_TRANSIT_TIME ,
TRANSIT_TIME_UOM ,
VERSION ,
CONSIGNEE_CARRIER_AC_NO ,
FREIGHT_TERMS_CODE ,
INITSMCONFIG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
'N' IS_CURRENT,NULL SINGLE_CURR_RATE,NULL SORT,NULL SCHEDULE_FROM,NULL SCHEDULE_TO,NULL SCHEDULE_ID,
VEHICLE_ORG_ID,
CALL_RG_FLAG
FROM FTE_CARRIER_RANK_LIST WHERE trip_id = p_trip_id
ORDER BY RANK_ID, RANK_SEQUENCE;
l_carrier_rank_list_rec.LAST_UPDATE_DATE := get_rank_list_rec.LAST_UPDATE_DATE ;
l_carrier_rank_list_rec.LAST_UPDATED_BY := get_rank_list_rec.LAST_UPDATED_BY ;
l_carrier_rank_list_rec.LAST_UPDATE_LOGIN := get_rank_list_rec.LAST_UPDATE_LOGIN ;
SELECT RANK_ID ,
TRIP_ID ,
RANK_SEQUENCE ,
CARRIER_ID ,
SERVICE_LEVEL ,
MODE_OF_TRANSPORT ,
LANE_ID ,
SOURCE ,
ENABLED ,
ESTIMATED_RATE ,
CURRENCY_CODE ,
VEHICLE_ITEM_ID ,
ESTIMATED_TRANSIT_TIME ,
TRANSIT_TIME_UOM ,
VERSION ,
CONSIGNEE_CARRIER_AC_NO ,
FREIGHT_TERMS_CODE ,
INITSMCONFIG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
'N' IS_CURRENT,NULL SINGLE_CURR_RATE,NULL SORT,NULL SCHEDULE_FROM,NULL SCHEDULE_TO,NULL SCHEDULE_ID,
VEHICLE_ORG_ID,CALL_RG_FLAG
FROM FTE_CARRIER_RANK_LIST rank_list
WHERE rank_list.trip_id = p_trip_id
AND rank_list.RANK_SEQUENCE = (p_current_sequence+1);
SELECT RANK_SEQUENCE INTO l_current_rank_sequence
FROM FTE_CARRIER_RANK_LIST rank_list, WSH_TRIPS trips
WHERE trips.trip_id = p_trip_id
AND trips.RANK_ID = rank_list.RANK_ID;
SELECT NAME INTO l_trip_name from WSH_TRIPS
WHERE TRIP_ID = p_trip_id;
Log(l_module_name,' Now update trip with next shipmethod ',
WSH_DEBUG_SV.C_PROC_LEVEL);
-- Call shipping API to update trip information with next shipmethod
/**
UPDATE WSH_TRIPS
SET CARRIER_ID = l_rank_rec.CARRIER_ID,
SERVICE_LEVEL = l_rank_rec.SERVICE_LEVEL,
MODE_OF_TRANSPORT = l_rank_rec.MODE_OF_TRANSPORT,
VEHICLE_ITEM_ID = l_rank_rec.VEHICLE_ITEM_ID,
VEHICLE_ORGANIZATION_ID = l_rank_rec.VEHICLE_ORG_ID,
LANE_ID = l_rank_rec.LANE_ID,
FREIGHT_TERMS_CODE = l_rank_rec.FREIGHT_TERMS_CODE,
CONSIGNEE_CARRIER_AC_NO = l_rank_rec.CONSIGNEE_CARRIER_AC_NO
WHERE TRIP_ID = p_trip_id;
WSH_DEBUG_SV.logmsg(l_module_name,' Calling UPDATE_SERVICE_ON_TRIP ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
FTE_MLS_WRAPPER.UPDATE_SERVICE_ON_TRIP(
p_API_VERSION_NUMBER => 1.0,
p_INIT_MSG_LIST => FND_API.G_FALSE,
p_COMMIT => FND_API.G_FALSE,
p_CALLER => 'FTE_LOAD_TENDER',
p_SERVICE_ACTION => 'UPDATE',
p_DELIVERY_ID => null,
p_DELIVERY_LEG_ID => null,
p_TRIP_ID => p_trip_id,
p_LANE_ID => l_rank_rec.LANE_ID,
p_SCHEDULE_ID => l_rank_rec.SCHEDULE_ID,
p_CARRIER_ID => l_rank_rec.CARRIER_ID,
p_SERVICE_LEVEL => l_rank_rec.SERVICE_LEVEL,
p_MODE_OF_TRANSPORT => l_rank_rec.MODE_OF_TRANSPORT,
p_VEHICLE_ITEM_ID => l_rank_rec.VEHICLE_ITEM_ID,
p_VEHICLE_ORG_ID => l_rank_rec.VEHICLE_ORG_ID,
p_CONSIGNEE_CARRIER_AC_NO => l_rank_rec.CONSIGNEE_CARRIER_AC_NO,
p_FREIGHT_TERMS_CODE => l_rank_rec.FREIGHT_TERMS_CODE,
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 value from UPDATE_SERVICE_ON_TRIP ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
SELECT COUNT(*) INTO l_count FROM FTE_CARRIER_RANK_LIST
WHERE (VERSION IS NULL OR VERSION = 0)
AND trip_id = p_trip_id;
SELECT RANK_ID ,
TRIP_ID ,
RANK_SEQUENCE ,
CARRIER_ID ,
SERVICE_LEVEL ,
MODE_OF_TRANSPORT ,
LANE_ID ,
SOURCE ,
ENABLED ,
ESTIMATED_RATE ,
CURRENCY_CODE ,
VEHICLE_ITEM_ID ,
VEHICLE_ORG_ID ,
ESTIMATED_TRANSIT_TIME ,
TRANSIT_TIME_UOM ,
VERSION ,
CONSIGNEE_CARRIER_AC_NO ,
FREIGHT_TERMS_CODE ,
INITSMCONFIG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
'N'
FROM FTE_CARRIER_RANK_LIST WHERE trip_id = p_trip_id
ORDER BY RANK_ID , RANK_SEQUENCE;
PROCEDURE DELETE_RANK_LIST_UIWRAPPER(
p_api_version_number IN NUMBER,
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_id IN FTE_ID_TAB_TYPE)
IS
l_warning_tab FTE_ID_TAB_TYPE;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RANK_LIST_UIWRAPPER';
SAVEPOINT DELETE_RANK_LIST_UIWRAPPER_PUB;
Log(l_module_name,' Delete rank list ',
WSH_DEBUG_SV.C_PROC_LEVEL);
-- loop through each trip and call delete
FOR i IN p_trip_id.FIRST..p_trip_id.LAST LOOP
IF l_debug_on
THEN
Log(l_module_name,' Calling DELETE_RANK_LIST for trip ' || p_trip_id(i) ,
WSH_DEBUG_SV.C_PROC_LEVEL);
DELETE_RANK_LIST(p_trip_id => p_trip_id(i),
x_return_status => l_return_status);
Log(l_module_name,' Return message after delete rank for trip ' || p_trip_id(i) || ' ' ||
l_return_status,
WSH_DEBUG_SV.C_PROC_LEVEL);
SELECT NAME INTO l_temp_name FROM WSH_TRIPS
WHERE TRIP_ID = p_trip_id(i);
ROLLBACK TO DELETE_RANK_LIST_UIWRAPPER_PUB;
ROLLBACK TO DELETE_RANK_LIST_UIWRAPPER_PUB;
ROLLBACK TO DELETE_RANK_LIST_UIWRAPPER_PUB;
wsh_util_core.default_handler('FTE_CARRIER_RANK_LIST_PVT.DELETE_RANK_LIST_UIWRAPPER');
END DELETE_RANK_LIST_UIWRAPPER;
SELECT trip_id FROM FTE_CARRIER_RANK_LIST
WHERE TRIP_ID = trip_id
AND ROWNUM = 1;
SELECT trip_id INTO l_trip_id
FROM FTE_CARRIER_RANK_LIST
WHERE TRIP_ID = p_ranklist.TRIP_ID(i)
AND ROWNUM = 1;
SELECT NAME INTO l_trip_name FROM WSH_TRIPS WHERE TRIP_ID = p_ranklist.TRIP_ID(i);
SELECT FTE_CARRIER_RANK_LIST_S.NEXTVAL INTO
p_ranklist.RANK_ID(i) FROM DUAL;
insert into FTE_CARRIER_RANK_LIST
( RANK_ID ,
TRIP_ID ,
RANK_SEQUENCE ,
CARRIER_ID ,
SERVICE_LEVEL ,
MODE_OF_TRANSPORT ,
LANE_ID ,
SOURCE ,
ENABLED ,
ESTIMATED_RATE ,
CURRENCY_CODE ,
VEHICLE_ITEM_ID ,
VEHICLE_ORG_ID ,
ESTIMATED_TRANSIT_TIME ,
TRANSIT_TIME_UOM ,
VERSION ,
CONSIGNEE_CARRIER_AC_NO ,
FREIGHT_TERMS_CODE ,
INITSMCONFIG ,
CALL_RG_FLAG ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
values
(
l_temp_table.RANK_ID(i),
l_temp_table.TRIP_ID(i),
l_temp_table.RANK_SEQUENCE(i),
decode(l_temp_table.CARRIER_ID(i),FND_API.G_MISS_NUM,NULL,l_temp_table.CARRIER_ID(i)),
decode(l_temp_table.SERVICE_LEVEL(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.SERVICE_LEVEL(i)),
decode(l_temp_table.MODE_OF_TRANSPORT(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.MODE_OF_TRANSPORT(i)),
decode(l_temp_table.LANE_ID(i),FND_API.G_MISS_NUM,NULL,l_temp_table.LANE_ID(i)),
decode(l_temp_table.SOURCE(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.SOURCE(i)),
'Y',
decode(l_temp_table.ESTIMATED_RATE(i),FND_API.G_MISS_NUM,NULL,l_temp_table.ESTIMATED_RATE(i)),
decode(l_temp_table.CURRENCY_CODE(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.CURRENCY_CODE(i)),
decode(l_temp_table.VEHICLE_ITEM_ID(i),FND_API.G_MISS_NUM,NULL,l_temp_table.VEHICLE_ITEM_ID(i)),
decode(l_temp_table.VEHICLE_ORG_ID(i),FND_API.G_MISS_NUM,NULL,l_temp_table.VEHICLE_ORG_ID(i)),
decode(l_temp_table.ESTIMATED_TRANSIT_TIME(i),FND_API.G_MISS_NUM,NULL,l_temp_table.ESTIMATED_TRANSIT_TIME(i)),
decode(l_temp_table.TRANSIT_TIME_UOM(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.TRANSIT_TIME_UOM(i)),
0,
decode(l_temp_table.CONSIGNEE_CARRIER_AC_NO(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.CONSIGNEE_CARRIER_AC_NO(i)),
decode(l_temp_table.FREIGHT_TERMS_CODE(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.FREIGHT_TERMS_CODE(i)),
'CSM',
'Y',
decode(l_temp_table.ATTRIBUTE_CATEGORY(i),FND_API.G_MISS_CHAR,NULL,
l_temp_table.ATTRIBUTE_CATEGORY(i)),
decode(l_temp_table.ATTRIBUTE1(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE1(i)),
decode(l_temp_table.ATTRIBUTE2(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE2(i)),
decode(l_temp_table.ATTRIBUTE3(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE3(i)),
decode(l_temp_table.ATTRIBUTE4(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE4(i)),
decode(l_temp_table.ATTRIBUTE5(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE5(i)),
decode(l_temp_table.ATTRIBUTE6(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE6(i)),
decode(l_temp_table.ATTRIBUTE7(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE7(i)),
decode(l_temp_table.ATTRIBUTE8(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE8(i)),
decode(l_temp_table.ATTRIBUTE9(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE9(i)),
decode(l_temp_table.ATTRIBUTE10(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE10(i)),
decode(l_temp_table.ATTRIBUTE11(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE11(i)),
decode(l_temp_table.ATTRIBUTE12(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE12(i)),
decode(l_temp_table.ATTRIBUTE13(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE13(i)),
decode(l_temp_table.ATTRIBUTE14(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE14(i)),
decode(l_temp_table.ATTRIBUTE15(i),FND_API.G_MISS_CHAR,NULL,l_temp_table.ATTRIBUTE15(i)),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID
);
Log(l_module_name,' After Insert ',WSH_DEBUG_SV.C_PROC_LEVEL);
UPDATE WSH_TRIPS
SET RANK_ID = l_temp_table.RANK_ID(i)
WHERE TRIP_ID = l_temp_table.TRIP_ID(i);