[Home] [Help]
170:
171: IF not l_input_param_flag THEN
172: FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
173: FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
174: x_return_status := wsh_util_core.g_ret_sts_error;
175: wsh_util_core.add_message(x_return_status,l_module_name);
176: RAISE FND_API.G_EXC_ERROR;
177: END IF;
178: --
171: IF not l_input_param_flag THEN
172: FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
173: FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
174: x_return_status := wsh_util_core.g_ret_sts_error;
175: wsh_util_core.add_message(x_return_status,l_module_name);
176: RAISE FND_API.G_EXC_ERROR;
177: END IF;
178: --
179: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
175: wsh_util_core.add_message(x_return_status,l_module_name);
176: RAISE FND_API.G_EXC_ERROR;
177: END IF;
178: --
179: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
180: --
181: OPEN get_carrier_id(p_carrier_name,p_rec_attr_tab.FREIGHT_CODE);
182: FETCH get_carrier_id INTO l_carrier_id;
183:
278: OPEN Get_SCAC_Code_Create(l_Carrier_Info.SCAC_CODE);
279: FETCH Get_SCAC_Code_Create INTO l_car_id;
280: IF Get_SCAC_Code_Create%FOUND THEN
281: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_SCAC_CODE_EXISTS');
282: x_return_status := wsh_util_core.g_ret_sts_error;
283: wsh_util_core.add_message(x_return_status,l_module_name);
284: RAISE FND_API.G_EXC_ERROR;
285: END IF;
286: CLOSE Get_SCAC_Code_Create;
279: FETCH Get_SCAC_Code_Create INTO l_car_id;
280: IF Get_SCAC_Code_Create%FOUND THEN
281: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_SCAC_CODE_EXISTS');
282: x_return_status := wsh_util_core.g_ret_sts_error;
283: wsh_util_core.add_message(x_return_status,l_module_name);
284: RAISE FND_API.G_EXC_ERROR;
285: END IF;
286: CLOSE Get_SCAC_Code_Create;
287:
289: OPEN Get_Freight_Code_Create(l_Carrier_Info.FREIGHT_CODE);
290: FETCH Get_Freight_Code_Create INTO l_car_id;
291: IF Get_Freight_Code_Create%FOUND THEN
292: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_FRG_CODE_EXISTS');
293: x_return_status := wsh_util_core.g_ret_sts_error;
294: wsh_util_core.add_message(x_return_status,l_module_name);
295: RAISE FND_API.G_EXC_ERROR;
296: END IF;
297: CLOSE Get_Freight_Code_Create;
290: FETCH Get_Freight_Code_Create INTO l_car_id;
291: IF Get_Freight_Code_Create%FOUND THEN
292: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_FRG_CODE_EXISTS');
293: x_return_status := wsh_util_core.g_ret_sts_error;
294: wsh_util_core.add_message(x_return_status,l_module_name);
295: RAISE FND_API.G_EXC_ERROR;
296: END IF;
297: CLOSE Get_Freight_Code_Create;
298:
301: FETCH Get_Carrier_Name_Create INTO l_car_id;
302:
303: IF Get_Carrier_Name_Create%FOUND THEN
304: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NAME_EXISTS');
305: x_return_status := wsh_util_core.g_ret_sts_error;
306: wsh_util_core.add_message(x_return_status,l_module_name);
307: RAISE FND_API.G_EXC_ERROR;
308: END IF;
309: CLOSE Get_Carrier_Name_Create;
302:
303: IF Get_Carrier_Name_Create%FOUND THEN
304: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NAME_EXISTS');
305: x_return_status := wsh_util_core.g_ret_sts_error;
306: wsh_util_core.add_message(x_return_status,l_module_name);
307: RAISE FND_API.G_EXC_ERROR;
308: END IF;
309: CLOSE Get_Carrier_Name_Create;
310:
318:
319: IF l_carrier_name IS NOT NULL THEN
320: FND_MESSAGE.SET_NAME('WSH','WSH_GENERIC_CARRIER_EXISTS');
321: FND_MESSAGE.SET_TOKEN('CARRIER_NAME', l_carrier_name);
322: x_return_status := wsh_util_core.g_ret_sts_error;
323: wsh_util_core.add_message(x_return_status,l_module_name);
324: RAISE FND_API.G_EXC_ERROR;
325: END IF;
326:
319: IF l_carrier_name IS NOT NULL THEN
320: FND_MESSAGE.SET_NAME('WSH','WSH_GENERIC_CARRIER_EXISTS');
321: FND_MESSAGE.SET_TOKEN('CARRIER_NAME', l_carrier_name);
322: x_return_status := wsh_util_core.g_ret_sts_error;
323: wsh_util_core.add_message(x_return_status,l_module_name);
324: RAISE FND_API.G_EXC_ERROR;
325: END IF;
326:
327: END IF;
340: X_SQLERR => l_sqlerr,
341: X_SQL_CODE => l_sql_code
342: );
343:
344: WSH_UTIL_CORE.api_post_call
345: (
346: p_return_status =>l_return_status,
347: x_num_warnings =>l_num_warnings,
348: x_num_errors =>l_num_errors
362: OPEN Get_SCAC_Code_Update(l_Carrier_Info.carrier_id, l_Carrier_Info.SCAC_CODE);
363: FETCH Get_SCAC_Code_Update INTO l_car_id;
364: IF Get_SCAC_Code_Update%FOUND THEN
365: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_SCAC_CODE_EXISTS');
366: x_return_status := wsh_util_core.g_ret_sts_error;
367: wsh_util_core.add_message(x_return_status,l_module_name);
368: RAISE FND_API.G_EXC_ERROR;
369: END IF;
370: CLOSE Get_SCAC_Code_Update;
363: FETCH Get_SCAC_Code_Update INTO l_car_id;
364: IF Get_SCAC_Code_Update%FOUND THEN
365: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_SCAC_CODE_EXISTS');
366: x_return_status := wsh_util_core.g_ret_sts_error;
367: wsh_util_core.add_message(x_return_status,l_module_name);
368: RAISE FND_API.G_EXC_ERROR;
369: END IF;
370: CLOSE Get_SCAC_Code_Update;
371:
374: FETCH Get_Carrier_Name_Update INTO l_car_id;
375:
376: IF Get_Carrier_Name_Update%FOUND THEN
377: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NAME_EXISTS');
378: x_return_status := wsh_util_core.g_ret_sts_error;
379: wsh_util_core.add_message(x_return_status,l_module_name);
380: RAISE FND_API.G_EXC_ERROR;
381: END IF;
382: CLOSE Get_Carrier_Name_Update;
375:
376: IF Get_Carrier_Name_Update%FOUND THEN
377: FND_MESSAGE.SET_NAME('WSH','WSH_CARRIER_NAME_EXISTS');
378: x_return_status := wsh_util_core.g_ret_sts_error;
379: wsh_util_core.add_message(x_return_status,l_module_name);
380: RAISE FND_API.G_EXC_ERROR;
381: END IF;
382: CLOSE Get_Carrier_Name_Update;
383:
403: X_SQL_CODE => l_sql_code
404: );
405: END IF;
406:
407: WSH_UTIL_CORE.api_post_call
408: (
409: p_return_status =>l_return_status,
410: x_num_warnings =>l_num_warnings,
411: x_num_errors =>l_num_errors
428: wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.UPDATE_CARRIERINFO',l_return_status);
429: END IF;
430:
431: END IF;
432: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
433: x_num_warnings =>l_num_warnings,
434: x_num_errors =>l_num_errors
435: );
436: x_car_out_rec_tab.rowid := l_rowid;
442: EXCEPTION
443: --
444: --
445: WHEN FND_API.G_EXC_ERROR THEN
446: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
447: x_msg_data := l_sqlerr;
448: FND_MSG_PUB.Count_And_Get
449: (
450: p_count => x_msg_count,
459: wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
460: wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
461: END IF;
462: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
463: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
464: x_msg_data := l_sqlerr;
465: FND_MSG_PUB.Count_And_Get
466: (
467: p_count => x_msg_count,
483: END IF;
484:
485: WHEN OTHERS THEN
486:
487: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
488: x_msg_data := l_sqlerr;
489: FND_MSG_PUB.Count_And_Get
490: (
491: p_count => x_msg_count,
662:
663: IF not l_input_param_flag THEN
664: FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
665: FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
666: x_return_status := wsh_util_core.g_ret_sts_error;
667: wsh_util_core.add_message(x_return_status,l_module_name);
668: RAISE FND_API.G_EXC_ERROR;
669: END IF;
670: --
663: IF not l_input_param_flag THEN
664: FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
665: FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
666: x_return_status := wsh_util_core.g_ret_sts_error;
667: wsh_util_core.add_message(x_return_status,l_module_name);
668: RAISE FND_API.G_EXC_ERROR;
669: END IF;
670: --
671: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
667: wsh_util_core.add_message(x_return_status,l_module_name);
668: RAISE FND_API.G_EXC_ERROR;
669: END IF;
670: --
671: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
672: --
673:
674: IF p_action_code = 'CREATE_UPDATE' THEN
675: OPEN get_carrier_ser_id(p_rec_attr_tab.carrier_id,p_rec_attr_tab.SERVICE_LEVEL,p_rec_attr_tab.MODE_OF_TRANSPORT);
782: CLOSE Check_Duplicate;
783:
784: IF l_count >0 THEN
785: fnd_message.set_name('WSH','WSH_DUPLICATE_RECORD');
786: x_return_status := wsh_util_core.g_ret_sts_error;
787: wsh_util_core.add_message(x_return_status,l_module_name);
788: RAISE FND_API.G_EXC_ERROR;
789: END IF;
790: END IF;
783:
784: IF l_count >0 THEN
785: fnd_message.set_name('WSH','WSH_DUPLICATE_RECORD');
786: x_return_status := wsh_util_core.g_ret_sts_error;
787: wsh_util_core.add_message(x_return_status,l_module_name);
788: RAISE FND_API.G_EXC_ERROR;
789: END IF;
790: END IF;
791:
793: OPEN Check_Duplicate_SMM_Create(l_carrier_ser_tab.ship_method_meaning);
794: FETCH Check_Duplicate_SMM_Create INTO l_serv_rowid;
795: IF Check_Duplicate_SMM_Create%FOUND THEN
796: fnd_message.set_name('WSH','WSH_SHIP_METHOD_EXISTS');
797: x_return_status := wsh_util_core.g_ret_sts_error;
798: wsh_util_core.add_message(x_return_status,l_module_name);
799: RAISE FND_API.G_EXC_ERROR;
800: END IF;
801: CLOSE Check_Duplicate_SMM_Create;
794: FETCH Check_Duplicate_SMM_Create INTO l_serv_rowid;
795: IF Check_Duplicate_SMM_Create%FOUND THEN
796: fnd_message.set_name('WSH','WSH_SHIP_METHOD_EXISTS');
797: x_return_status := wsh_util_core.g_ret_sts_error;
798: wsh_util_core.add_message(x_return_status,l_module_name);
799: RAISE FND_API.G_EXC_ERROR;
800: END IF;
801: CLOSE Check_Duplicate_SMM_Create;
802: --- Bug 3392826 End: Validation for Services
829: OPEN Check_Duplicate_SMM_Update(l_rowid, l_carrier_ser_tab.ship_method_meaning);
830: FETCH Check_Duplicate_SMM_Update INTO l_serv_rowid;
831: IF Check_Duplicate_SMM_Update%FOUND THEN
832: fnd_message.set_name('WSH','WSH_SHIP_METHOD_EXISTS');
833: x_return_status := wsh_util_core.g_ret_sts_error;
834: wsh_util_core.add_message(x_return_status,l_module_name);
835: RAISE FND_API.G_EXC_ERROR;
836: END IF;
837: CLOSE Check_Duplicate_SMM_Update;
830: FETCH Check_Duplicate_SMM_Update INTO l_serv_rowid;
831: IF Check_Duplicate_SMM_Update%FOUND THEN
832: fnd_message.set_name('WSH','WSH_SHIP_METHOD_EXISTS');
833: x_return_status := wsh_util_core.g_ret_sts_error;
834: wsh_util_core.add_message(x_return_status,l_module_name);
835: RAISE FND_API.G_EXC_ERROR;
836: END IF;
837: CLOSE Check_Duplicate_SMM_Update;
838: --- Bug 3392826 End: Validation for Services
858: wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.Update_Carrier_Service',l_return_status);
859: END IF;
860:
861: END IF;
862: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
863: x_num_warnings =>l_num_warnings,
864: x_num_errors =>l_num_errors
865: );
866: x_car_ser_out_rec_tab.rowid := l_rowid;
874: EXCEPTION
875: --
876: --
877: WHEN FND_API.G_EXC_ERROR THEN
878: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
879: x_msg_data := l_sqlerr;
880: FND_MSG_PUB.Count_And_Get
881: (
882: p_count => x_msg_count,
891: wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
892: wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
893: END IF;
894: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
895: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
896: x_msg_data := l_sqlerr;
897: FND_MSG_PUB.Count_And_Get
898: (
899: p_count => x_msg_count,
915: END IF;
916:
917: WHEN OTHERS THEN
918:
919: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
920: x_msg_data := l_sqlerr;
921: FND_MSG_PUB.Count_And_Get
922: (
923: p_count => x_msg_count,
1062:
1063: IF not l_input_param_flag THEN
1064: FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
1065: FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
1066: x_return_status := wsh_util_core.g_ret_sts_error;
1067: wsh_util_core.add_message(x_return_status,l_module_name);
1068: RAISE FND_API.G_EXC_ERROR;
1069: END IF;
1070: --
1063: IF not l_input_param_flag THEN
1064: FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
1065: FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
1066: x_return_status := wsh_util_core.g_ret_sts_error;
1067: wsh_util_core.add_message(x_return_status,l_module_name);
1068: RAISE FND_API.G_EXC_ERROR;
1069: END IF;
1070: --
1071: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1067: wsh_util_core.add_message(x_return_status,l_module_name);
1068: RAISE FND_API.G_EXC_ERROR;
1069: END IF;
1070: --
1071: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1072: --
1073: OPEN get_carrier_ser_info(p_rec_org_car_ser_tab.Carrier_Service_id);
1074: FETCH get_carrier_ser_info INTO l_carrier_id,l_service_level,l_shp_method_code;
1075: if(get_carrier_ser_info%FOUND) THEN
1185: );
1186: IF l_debug_on THEN
1187: wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.assign_org_carrier_service',l_return_status);
1188: END IF;
1189: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1190: x_num_warnings =>l_num_warnings,
1191: x_num_errors =>l_num_errors
1192: );
1193: END IF;
1204: EXCEPTION
1205: --
1206: --
1207: WHEN FND_API.G_EXC_ERROR THEN
1208: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1209: x_msg_data := l_sqlerr;
1210: FND_MSG_PUB.Count_And_Get
1211: (
1212: p_count => x_msg_count,
1220: wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
1221: wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
1222: END IF;
1223: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1224: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1225: x_msg_data := l_sqlerr;
1226: FND_MSG_PUB.Count_And_Get
1227: (
1228: p_count => x_msg_count,
1244: END IF;
1245:
1246: WHEN OTHERS THEN
1247:
1248: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1249: x_msg_data := l_sqlerr;
1250: FND_MSG_PUB.Count_And_Get
1251: (
1252: p_count => x_msg_count,
1374:
1375: IF not l_input_param_flag THEN
1376: FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
1377: FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
1378: x_return_status := wsh_util_core.g_ret_sts_error;
1379: wsh_util_core.add_message(x_return_status,l_module_name);
1380: RAISE FND_API.G_EXC_ERROR;
1381: END IF;
1382: --
1375: IF not l_input_param_flag THEN
1376: FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
1377: FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
1378: x_return_status := wsh_util_core.g_ret_sts_error;
1379: wsh_util_core.add_message(x_return_status,l_module_name);
1380: RAISE FND_API.G_EXC_ERROR;
1381: END IF;
1382: --
1383: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1379: wsh_util_core.add_message(x_return_status,l_module_name);
1380: RAISE FND_API.G_EXC_ERROR;
1381: END IF;
1382: --
1383: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1384: --
1385: OPEN get_carrier_service_ids(p_carrier_id);
1386: LOOP
1387: FETCH get_carrier_service_ids INTO l_carrier_service_id;
1419: );
1420: IF l_debug_on THEN
1421: wsh_debug_sv.log(l_module_name,'return status WSH_CREATE_CARRIERS_PKG.Assign_Org_Carrier_Service',l_return_status);
1422: END IF;
1423: WSH_UTIL_CORE.api_post_call(p_return_status =>l_return_status,
1424: x_num_warnings =>l_num_warnings,
1425: x_num_errors =>l_num_errors
1426: );
1427: x_orgcar_ser_out_tab(l_index) := p_car_ser_out_rec;
1434: EXCEPTION
1435: --
1436: --
1437: WHEN FND_API.G_EXC_ERROR THEN
1438: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1439: x_msg_data := l_sqlerr;
1440: FND_MSG_PUB.Count_And_Get
1441: (
1442: p_count => x_msg_count,
1450: wsh_debug_sv.log (l_module_name,'l_sqlerr',l_sqlerr);
1451: wsh_debug_sv.log (l_module_name,'l_sql_code',l_sql_code);
1452: END IF;
1453: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1454: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1455: x_msg_data := l_sqlerr;
1456: FND_MSG_PUB.Count_And_Get
1457: (
1458: p_count => x_msg_count,
1474: END IF;
1475:
1476: WHEN OTHERS THEN
1477:
1478: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1479: x_msg_data := l_sqlerr;
1480: FND_MSG_PUB.Count_And_Get
1481: (
1482: p_count => x_msg_count,
1736: WSH_DEBUG_SV.log(l_module_name,'p_carrier_service_inout_rec.ship_method_code',p_carrier_service_inout_rec.ship_method_code);
1737: WSH_DEBUG_SV.log(l_module_name,'p_carrier_service_inout_rec.carrier_service_id',p_carrier_service_inout_rec.carrier_service_id);
1738: END IF;
1739:
1740: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1741:
1742: OPEN get_carrier_service_mode;
1743: FETCH get_carrier_service_mode INTO p_carrier_service_inout_rec;
1744: IF get_carrier_service_mode%NOTFOUND THEN
1751: END IF;
1752:
1753: EXCEPTION
1754: WHEN Invalid_ship_method THEN
1755: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1756: fnd_message.set_name('WSH', 'WSH_GET_SERVICE_MODE_ERROR');
1757: wsh_util_core.add_message(wsh_util_core.g_ret_sts_success);
1758: --
1759: -- Debug Statements
1753: EXCEPTION
1754: WHEN Invalid_ship_method THEN
1755: x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1756: fnd_message.set_name('WSH', 'WSH_GET_SERVICE_MODE_ERROR');
1757: wsh_util_core.add_message(wsh_util_core.g_ret_sts_success);
1758: --
1759: -- Debug Statements
1760: --
1761: IF l_debug_on THEN
1763: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:invalid_ship_method');
1764: END IF;
1765: --
1766: WHEN OTHERS THEN
1767: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1768: --
1769: -- Debug Statements
1770: --
1771: IF l_debug_on THEN