[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Create_Update_Carrier
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_action_code IN VARCHAR2,
p_rec_attr_tab IN Carrier_Rec_Type,
p_carrier_name IN VARCHAR2,
p_status IN VARCHAR2,
x_car_out_rec_tab OUT NOCOPY Carrier_Out_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR get_carrier_id(p_carrier_name VARCHAR2,fr_code VARCHAR2) is
SELECT carrier_id
FROM WSH_CARRIERS car, HZ_PARTIES par
WHERE car.carrier_id = par.PARTY_ID
AND par.PARTY_NAME = p_carrier_name
AND car.FREIGHT_CODE = fr_code;
SELECT carrier_id
FROM WSH_CARRIERS
WHERE FREIGHT_CODE = fr_code;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_CARRIER';
l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Carrier';
SELECT carrier_id
FROM wsh_carriers
WHERE SCAC_CODE = p_scac_code;
CURSOR Get_SCAC_Code_Update(p_carrier_id NUMBER, p_scac_code VARCHAR2) IS
SELECT carrier_id
FROM wsh_carriers
WHERE SCAC_CODE = p_scac_code
AND carrier_id <> p_carrier_id;
SELECT carrier_id
FROM wsh_carriers
WHERE upper(FREIGHT_CODE) = upper(p_freight_code);
CURSOR Get_Freight_Code_Update(p_carrier_id NUMBER, p_freight_code VARCHAR2) IS
SELECT carrier_id
FROM wsh_carriers
WHERE upper(FREIGHT_CODE) = upper(p_freight_code)
AND carrier_id <> p_carrier_id;
SELECT carrier_id
FROM wsh_carriers_v wc
WHERE wc.active= 'A' AND
upper(wc.carrier_name) = upper(p_carrier_name);
CURSOR Get_Carrier_Name_Update(p_carrier_id NUMBER,p_carrier_name VARCHAR2) IS
SELECT carrier_id
FROM wsh_carriers_v wc
WHERE wc.active = 'A'
AND upper(wc.carrier_name) = upper(p_carrier_name)
AND wc.carrier_id <> p_carrier_id;
SELECT carrier_name
FROM wsh_carriers_v
WHERE nvl(generic_flag, 'N') = 'Y' AND
freight_code <> nvl(p_freight_code, ' ') AND
active = 'A';
IF p_action_code = 'CREATE_UPDATE' THEN
IF l_carrier_id = 0 THEN
l_action_code := 'CREATE';
l_action_code := 'UPDATE';
ELSIF p_action_code = 'UPDATE' OR l_action_code = 'UPDATE' THEN
-- Pack J
l_Carrier_info.CARRIER_ID := l_carrier_id;
OPEN Get_SCAC_Code_Update(l_Carrier_Info.carrier_id, l_Carrier_Info.SCAC_CODE);
FETCH Get_SCAC_Code_Update INTO l_car_id;
IF Get_SCAC_Code_Update%FOUND THEN
FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_SCAC_CODE_EXISTS');
CLOSE Get_SCAC_Code_Update;
OPEN Get_Carrier_Name_Update(l_Carrier_Info.carrier_id, l_Carrier_Info.CARRIER_NAME);
FETCH Get_Carrier_Name_Update INTO l_car_id;
IF Get_Carrier_Name_Update%FOUND THEN
FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NAME_EXISTS');
CLOSE Get_Carrier_Name_Update;
WSH_CREATE_CARRIERS_PKG.UPDATE_CARRIERINFO
(
P_CARRIER_INFO => l_Carrier_Info,
P_COMMIT => p_commit,
X_RETURN_STATUS => l_return_status,
X_EXCEPTION_MSG => l_exception_msg,
X_POSITION => l_position,
X_PROCEDURE => l_call_procedure,
X_SQLERR => l_sqlerr,
X_SQL_CODE => l_sql_code
);
wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.UPDATE_CARRIERINFO',l_return_status);
END Create_Update_Carrier;
PROCEDURE Create_Update_Carrier_Service
( p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_action_code IN VARCHAR2,
p_rec_attr_tab IN Carrier_Service_Rec_Type,
x_car_ser_out_rec_tab OUT NOCOPY Carrier_Ser_Out_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_UPDATE_CARRIER_SERVICE';
l_api_name CONSTANT VARCHAR2(30):= 'Create_Update_Carrier_Service';
SELECT carrier_id
FROM WSH_CARRIER_SERVICES
WHERE carrier_id = p_carrier_id
AND service_level = p_service_level_code
AND MODE_OF_TRANSPORT = p_mode_of_transport;
SELECT freight_code
FROM WSH_CARRIERS
WHERE carrier_id = p_carrier_id;
SELECT rowid,ship_method_code
FROM wsh_carrier_services
WHERE carrier_id= p_carrier_id
AND service_level = p_service_level
AND mode_of_transport = p_mode_of_transport;
SELECT count(*)
FROM wsh_carrier_services
WHERE mode_of_transport = p_mode_of_transport AND
service_level = p_service_level AND
carrier_id = p_carrier_id;
SELECT rowid
FROM wsh_carrier_services
WHERE SHIP_METHOD_MEANING = p_ship_method_meaning;
CURSOR Check_Duplicate_SMM_Update(p_rowid VARCHAR2, p_ship_method_meaning VARCHAR2) IS
SELECT rowid
FROM wsh_carrier_services
WHERE SHIP_METHOD_MEANING = p_ship_method_meaning
AND rowid <> p_rowid;
IF p_action_code = 'CREATE_UPDATE' THEN
OPEN get_carrier_ser_id(p_rec_attr_tab.carrier_id,p_rec_attr_tab.SERVICE_LEVEL,p_rec_attr_tab.MODE_OF_TRANSPORT);
l_action_code := 'UPDATE';
l_carrier_ser_tab.Last_Update_Date := SYSDATE;
l_carrier_ser_tab.Last_Updated_By := fnd_global.user_id;
ELSIF p_action_code = 'UPDATE' OR l_action_code = 'UPDATE' THEN
OPEN get_rowid_shpcode(p_rec_attr_tab.CARRIER_ID, p_rec_attr_tab.SERVICE_LEVEL, p_rec_attr_tab.MODE_OF_TRANSPORT);
OPEN Check_Duplicate_SMM_Update(l_rowid, l_carrier_ser_tab.ship_method_meaning);
FETCH Check_Duplicate_SMM_Update INTO l_serv_rowid;
IF Check_Duplicate_SMM_Update%FOUND THEN
fnd_message.set_name('WSH','WSH_SHIP_METHOD_EXISTS');
CLOSE Check_Duplicate_SMM_Update;
WSH_CARRIER_SERVICES_PKG.Update_Carrier_Service
(
p_Carrier_Service_Info => l_carrier_ser_tab,
P_ROWID => l_rowid,
P_COMMIT => p_commit,
X_RETURN_STATUS => l_return_status,
X_POSITION => l_position,
X_PROCEDURE => l_call_procedure,
X_SQLERR => l_sqlerr,
X_SQL_CODE => l_sql_code,
X_EXCEPTION_MSG => l_exception_msg
);
wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.Update_Carrier_Service',l_return_status);
END Create_Update_Carrier_Service;
SELECT CARRIER_ID,SERVICE_LEVEL,SHIP_METHOD_CODE
FROM WSH_CARRIER_SERVICES
WHERE carrier_service_id = p_carrier_service_id
AND enabled_flag = 'Y';
SELECT car.freight_code,par.PARTY_NAME
FROM WSH_CARRIERS car, HZ_PARTIES par
WHERE car.carrier_id = p_carrier_id
AND car.carrier_id = par.PARTY_ID;
SELECT org_carrier_service_id,rowid
FROM wsh_Org_Carrier_Services
WHERE carrier_service_id = p_carrier_service_id
AND organization_id = p_org_id;
l_org_car_ser_tab.LAST_UPDATE_DATE := SYSDATE;
l_org_car_ser_tab.LAST_UPDATED_BY := fnd_global.user_id;
l_car_info_tab.LAST_UPDATE_DATE := SYSDATE;
l_car_info_tab.LAST_UPDATED_BY := fnd_global.user_id;
l_shp_method_rec.LAST_UPDATE_DATE := SYSDATE;
l_shp_method_rec.LAST_UPDATED_BY := fnd_global.user_id;
SELECT carrier_service_id
FROM WSH_CARRIER_SERVICES
WHERE carrier_id = p_carrier_id
AND enabled_flag = 'Y';
p_rec_org_car_ser_tab.LAST_UPDATE_DATE := SYSDATE;
p_rec_org_car_ser_tab.LAST_UPDATED_BY :=fnd_global.user_id;
p_rec_car_info_tab.LAST_UPDATE_DATE := SYSDATE;
p_rec_car_info_tab.LAST_UPDATED_BY :=fnd_global.user_id;
p_car_shp_methods.LAST_UPDATE_DATE := SYSDATE;
p_car_shp_methods.LAST_UPDATED_BY := fnd_global.user_id;
SELECT meaning
INTO x_service_level
FROM WSH_LOOKUPS
WHERE LOOKUP_TYPE = 'WSH_SERVICE_LEVELS'
AND LOOKUP_CODE = SERVICE_LEVEL_CODE;
SELECT meaning
INTO x_mode_of_transport
FROM WSH_LOOKUPS
WHERE LOOKUP_TYPE = 'WSH_MODE_OF_TRANSPORT'
AND LOOKUP_CODE = MODE_OF_TRANS_CODE;
select UNIT_OF_MEASURE
INTO x_sl_time_uom_desc
FROM MTL_UNITS_OF_MEASURE_VL
WHERE UOM_CLASS LIKE 'Time'
AND UOM_CODE = SL_TIME_UOM;
SELECT count(*)
FROM fnd_lookup_values_vl
WHERE lookup_code = p_ship_method_code
AND lookup_type = 'SHIP_METHOD';
SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE meaning = p_ship_method_meaning
AND lookup_type = 'SHIP_METHOD';
SELECT max(to_number(substr(lookup_code,1,6)))
INTO l_maxnum
FROM fnd_lookup_values_vl
WHERE substr(lookup_code,7, length(lookup_code)) = substr(l_code,7,length(l_code))
AND lookup_type = 'SHIP_METHOD';
SELECT lpad(to_char(l_maxnum),6,'0') into l_code1 from dual;
SELECT party_name, freight_code
FROM wsh_carriers, hz_parties
WHERE carrier_id =party_id
AND carrier_id= p_carrier_id;
SELECT wc.carrier_id,
wc.freight_code,
wc.scac_code,
wc.manifesting_enabled_flag,
wc.currency_code,
nvl(wc.generic_flag, 'N'),
wcs.carrier_service_id,
wcs.service_level,
wcs.mode_of_transport,
wcs.ship_method_code
FROM wsh_carriers wc,
wsh_carrier_services wcs
WHERE wc.carrier_id = wcs.carrier_id
AND ( wcs.ship_method_code = p_carrier_service_inout_rec.ship_method_code OR
wcs.carrier_service_id = p_carrier_service_inout_rec.carrier_service_id);