1 PACKAGE BODY WSH_CARRIERS_PUB AS
2 /* $Header: WSHCAPBB.pls 120.0.12010000.2 2010/06/14 12:43:57 gbhargav noship $ */
3
4 --===================
5 -- CONSTANTS
6 --===================
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_CARRIERS_PUB';
8
9 --===================
10 -- PROCEDURES
11 --===================
12
13 --========================================================================
14 -- PROCEDURE : Assign_Org_Carrier_Service PUBLIC
15 --
16 -- PARAMETERS: p_api_version_number known api version error number
17 -- p_init_msg_list FND_API.G_TRUE to reset list
18 -- p_commit FND_API.G_TRUE to commit.
19 -- p_action_code Valid action codes are
20 -- 'ASSIGN','UNASSIGN'
21 -- p_org_info_tab Input table variable containing org_id and org_code for which needs to be assigned/unassigned
22 -- p_carrier_id Carrier Id of the carrier
23 -- p_freight_code Freight code
24 -- p_carrier_service_id Carrier service Id to be assigned/unassigned to Organization
25 -- p_ship_method_code Ship Method code
26 -- p_ship_method Ship Method meaning
27 -- x_car_out_rec_tab Out table variable containing carrier_service_id and org_carrier_service_id updated/inserted
28 -- x_return_status return status
29 -- x_msg_count number of messages in the list
30 -- x_msg_data text of messages
31 --
32 -- VERSION : current version 1.0
33 -- initial version 1.0
34 -- COMMENT : This procedure is used to perform an action specified in p_action_code on the carrier service
35 --
36 -- If p_action_code is 'ASSIGN' then new record will be inserted in WCSM ,WOCS and org_fieight_tl,
37 -- if records are already existing then existing records will be updated
38 -- If p_action_code is 'UNASSIGN' then existing record in WCSM and WOCS will be disabled .
39 -- if records are not existing then records will be inserted in disabled status
40 -- in WCSM ,WOCS and org_fieight_tl
41 --
42 -- If org_id and org_code are both passed in p_org_info_tab then only org_id is considered than org_code.
43 --
44 -- If p_carrier_service_id is passed then p_carrier_id , p_freight_code , p_ship_method_code and p_ship_method parameters are ignored
45 --
46 -- If p_ship_method_code or p_ship_method is passed then on associated carrier service action will be performed.
47 -- If p_ship_method_code and p_ship_method both are passed then only p_ship_method_code is used.
48 -- If p_ship_method_code or p_ship_method is passed then p_carrier_id/p_freight_code are ignored
49
50 -- If p_carrier_id or p_freight_code is passed then action will be performed on all associated carrier services.
51 -- If p_carrier_id and p_freight_code both are passed then only p_carrier_id is considered.
52 --
53 --DESCRIPTION: Organization Assignment/Unassignment for a carrier / carrier service / ship method is possible from Application .
54 -- This Public API is created to fulfill the same requirement.
55 --========================================================================
56
57 PROCEDURE Assign_Org_Carrier_Service
58 ( p_api_version_number IN NUMBER,
59 p_init_msg_list IN VARCHAR2,
60 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
61 p_action_code IN VARCHAR2,
62 p_org_info_tab IN WSH_CARRIERS_PUB.Org_Info_Tab_Type,
63 p_carrier_id IN NUMBER DEFAULT NULL,
64 p_freight_code IN VARCHAR2 DEFAULT NULL,
65 p_carrier_service_id IN NUMBER DEFAULT NULL,
66 p_ship_method_code IN VARCHAR2 DEFAULT NULL,
67 p_ship_method IN VARCHAR2 DEFAULT NULL,
68 x_car_out_rec_tab OUT NOCOPY wsh_carriers_grp.Org_Carrier_Ser_Out_Tab_Type,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_msg_count OUT NOCOPY NUMBER,
71 x_msg_data OUT NOCOPY VARCHAR2 )
72 IS
73 l_api_version_number CONSTANT NUMBER :=1.0 ;
74 l_api_name CONSTANT VARCHAR2(30):= 'Assign_Org_Carrier_Service';
75 l_carrier_id NUMBER ;
76 l_freight_code WSH_CARRIERS.Freight_code%TYPE ;
77 l_org_info_tab WSH_CARRIERS_PUB.Org_Info_Tab_Type;
78 l_carrier_service_id NUMBER ;
79 l_carrier_service_info_tab Carrier_Ser_DFF_Tab_Type;
80 l_ship_method_code WSH_CARRIER_SERVICES.Ship_Method_Code%TYPE ;
81 l_ship_method WSH_CARRIER_SERVICES.Ship_Method_Meaning%TYPE ;
82 l_carrier_service_exists VARCHAR2(1);
83 l_shp_methods_dff WSH_CARRIERS_GRP.Ship_Method_Dff_Type;
84 l_rec_org_car_ser_tab WSH_CARRIERS_GRP.Org_Carrier_Service_Rec_Type;
85 l_rec_car_info_tab WSH_CARRIERS_GRP.Carrier_Info_Dff_Type; --Passed as null in grp API
86 l_car_ser_out_rec WSH_CARRIERS_GRP.Org_Carrier_Ser_Out_Rec_Type;
87 l_param_name VARCHAR2(100);
88 l_num_errors NUMBER ;
89 l_num_warnings NUMBER ;
90 l_return_status VARCHAR2(10);
91 l_msg_count NUMBER;
92 l_msg_data VARCHAR2(32767);
93 l_input_param_flag BOOLEAN ;
94 l_temp NUMBER;
95
96 --Cursor to fetch carrier service DFF.
97 CURSOR c_check_carrier_ser_exists(l_carrier_service_id NUMBER) IS
98 SELECT CARRIER_SERVICE_ID,
99 ATTRIBUTE_CATEGORY ,
100 ATTRIBUTE1 ,
101 ATTRIBUTE2 ,
102 ATTRIBUTE3 ,
103 ATTRIBUTE4 ,
104 ATTRIBUTE5 ,
105 ATTRIBUTE6 ,
106 ATTRIBUTE7 ,
107 ATTRIBUTE8 ,
108 ATTRIBUTE9 ,
109 ATTRIBUTE10,
110 ATTRIBUTE11,
111 ATTRIBUTE12,
112 ATTRIBUTE13,
113 ATTRIBUTE14,
114 ATTRIBUTE15
115 FROM WSH_CARRIER_SERVICES
116 WHERE carrier_service_id = l_carrier_service_id
117 AND enabled_flag = 'Y';
118
119 --Cursor to fetch carrier_service_id and DFF.
120 CURSOR c_check_carrier_sm_exists(l_ship_method_code VARCHAR2) IS
121 SELECT CARRIER_SERVICE_ID,
122 ATTRIBUTE_CATEGORY ,
123 ATTRIBUTE1 ,
124 ATTRIBUTE2 ,
125 ATTRIBUTE3 ,
126 ATTRIBUTE4 ,
127 ATTRIBUTE5 ,
128 ATTRIBUTE6 ,
129 ATTRIBUTE7 ,
130 ATTRIBUTE8 ,
131 ATTRIBUTE9 ,
132 ATTRIBUTE10,
133 ATTRIBUTE11,
134 ATTRIBUTE12,
135 ATTRIBUTE13,
136 ATTRIBUTE14,
137 ATTRIBUTE15
138 FROM WSH_CARRIER_SERVICES
139 WHERE ship_method_code = l_ship_method_code
140 AND enabled_flag = 'Y';
141
142 --Cursor to fetch carrier service details
143 CURSOR get_carrier_ser_info(l_carrier_id NUMBER) IS
144 SELECT CARRIER_SERVICE_ID,
145 ATTRIBUTE_CATEGORY,
146 ATTRIBUTE1,
147 ATTRIBUTE2,
148 ATTRIBUTE3,
149 ATTRIBUTE4,
150 ATTRIBUTE5,
151 ATTRIBUTE6,
152 ATTRIBUTE7,
153 ATTRIBUTE8,
154 ATTRIBUTE9,
155 ATTRIBUTE10,
156 ATTRIBUTE11,
157 ATTRIBUTE12,
158 ATTRIBUTE13,
159 ATTRIBUTE14,
160 ATTRIBUTE15
161 FROM WSH_CARRIER_SERVICES
162 WHERE carrier_id = l_carrier_id
163 AND enabled_flag = 'Y';
164
165 --
166 l_debug_on BOOLEAN;
167 --
168 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Assign_Org_Carrier_Service';
169
170 BEGIN
171
172 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
173
174 IF l_debug_on IS NULL
175 THEN
176 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
177 END IF;
178 IF NOT FND_API.Compatible_API_Call
179 (
180 l_api_version_number,
181 p_api_version_number,
182 l_api_name,
183 G_PKG_NAME
184 )
185 THEN
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 END IF;
188
189 IF FND_API.to_Boolean(p_init_msg_list) THEN
190 FND_MSG_PUB.initialize;
191 END IF;
192
193 IF l_debug_on THEN
194 wsh_debug_sv.push (l_module_name);
195 wsh_debug_sv.log (l_module_name,'Action_code',p_action_code);
196 wsh_debug_sv.log (l_module_name,'p_org_info_tab.count',p_org_info_tab.count);
197 wsh_debug_sv.log (l_module_name,'Carrier_id',p_carrier_id);
198 wsh_debug_sv.log (l_module_name,'Freight_code',p_freight_code);
199 wsh_debug_sv.log (l_module_name,'Carrier_service_id',p_carrier_service_id);
200 wsh_debug_sv.log (l_module_name,'Ship_method_code',p_ship_method_code);
201 wsh_debug_sv.log (l_module_name,'Ship_method',p_ship_method);
202 END IF;
203
204 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
205 l_carrier_id := p_carrier_id ;
206 l_freight_code := p_freight_code;
207 l_carrier_service_id := p_carrier_service_id;
208 l_ship_method_code := p_ship_method_code ;
209 l_ship_method := p_ship_method ;
210 l_num_errors := 0;
211 l_num_warnings := 0;
212 l_input_param_flag := TRUE;
213 l_temp := 0;
214
215 l_temp := p_org_info_tab.FIRST;
216
217 IF l_temp is not null THEN
218 LOOP
219 -- Exchange the org values from p_org_info_tab to l_org_info_tab only if either org_id / org_code is not null.
220 IF ( NVL(p_org_info_tab(l_temp).org_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM ) OR
221 ( NVL(p_org_info_tab(l_temp).org_code,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR ) THEN
222 --{
223 l_org_info_tab(l_org_info_tab.count+1).org_id := p_org_info_tab(l_temp).org_id;
224 l_org_info_tab(l_org_info_tab.count).org_code := p_org_info_tab(l_temp).org_code;
225 --}
226 END IF;
227
228 EXIT WHEN ( l_temp = p_org_info_tab.LAST );
229 l_temp := p_org_info_tab.NEXT(l_temp);
230 END LOOP;
231 END IF;
232
233 IF p_action_code IS NULL THEN
234 --{
235 l_param_name := 'action_code';
236 l_input_param_flag := FALSE;
237 --}
238 ELSIF (l_org_info_tab.count = 0 ) THEN
239 --{
240 l_param_name := 'organization_id or organization_code';
241 l_input_param_flag := FALSE;
242 --}
243 ELSIF (l_carrier_service_id IS NULL AND l_ship_method_code IS NULL AND
244 l_ship_method IS NULL AND l_carrier_id IS NULL AND l_freight_code is NULL) THEN
245 --{
246 l_param_name := 'Carrier_service_id/Ship_method_code/Ship_method/Carrier_id/Freight_code';
247 l_input_param_flag := FALSE;
248 --}
249 END IF;
250
251 IF NOT l_input_param_flag THEN
252 --{
253 FND_MESSAGE.SET_NAME('WSH','WSH_REQUIRED_FIELD_NULL');
254 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_param_name);
255 x_return_status := wsh_util_core.g_ret_sts_error;
256 wsh_util_core.add_message(x_return_status,l_module_name);
257 RAISE FND_API.G_EXC_ERROR;
258 --}
259 END IF;
260
261 IF p_action_code NOT IN ('ASSIGN','UNASSIGN') THEN
262 --{
263 FND_MESSAGE.SET_NAME('WSH','WSH_INVALID_ACTION_CODE');
264 FND_MESSAGE.SET_TOKEN('ACT_CODE',p_action_code);
265 x_return_status := wsh_util_core.g_ret_sts_error;
266 wsh_util_core.add_message(x_return_status,l_module_name);
267 RAISE FND_API.G_EXC_ERROR;
268 --}
269 END IF;
270
271 IF l_debug_on THEN
272 wsh_debug_sv.log(l_module_name,'l_org_info_tab.count',l_org_info_tab.count);
273 END IF;
274
275 --Organization validation
276 IF ( l_org_info_tab.count > 0 ) THEN
277 --{
278
279 FOR i in l_org_info_tab.FIRST..l_org_info_tab.LAST LOOP
280 --{
281 IF l_debug_on THEN
282 wsh_debug_sv.logmsg(l_module_name,'Calling wsh_util_validate.validate_org',WSH_DEBUG_SV.C_PROC_LEVEL);
283 wsh_debug_sv.log(l_module_name,'l_org_info_tab('||i||').org_id',l_org_info_tab(i).org_id);
284 wsh_debug_sv.log(l_module_name,'l_org_info_tab('||i||').org_code',l_org_info_tab(i).org_code);
285 END IF;
286
287 WSH_UTIL_VALIDATE.validate_org(
288 p_org_id => l_org_info_tab(i).org_id, --Out param
289 p_org_code => l_org_info_tab(i).org_code,
290 x_return_status => l_return_status );
291
292 IF l_debug_on THEN
293 wsh_debug_sv.log(l_module_name,'Return Status After Calling wsh_util_validate.validate_org',l_return_status);
294 END IF;
295
296 WSH_UTIL_CORE.api_post_call(
297 p_return_status => l_return_status,
298 x_num_warnings => l_num_warnings,
299 x_num_errors => l_num_errors);
300 --}
301 END LOOP;
302
303 --Carrier service validation
304 IF (l_carrier_service_id IS NOT NULL ) THEN
305 --{
306 IF l_debug_on THEN
307 wsh_debug_sv.log(l_module_name,'Fetching wsh_carrier_services data for Carrier Service Id',l_carrier_service_id);
308 END IF;
309 OPEN c_check_carrier_ser_exists(l_carrier_service_id);
310 FETCH c_check_carrier_ser_exists INTO l_carrier_service_info_tab(1);
311
312 IF (c_check_carrier_ser_exists%NOTFOUND) THEN
313 --{
314 CLOSE c_check_carrier_ser_exists;
315 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_ATTRIBUTE'); --Bug 9706100 added error message
316 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Carrier Service Id');
317 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
318 wsh_util_core.add_message(x_return_status,l_module_name);
319 RAISE FND_API.G_EXC_ERROR;
320 --}
321 END IF;
322
323 CLOSE c_check_carrier_ser_exists;
324 --}End of Carrier service validation
325 --Ship method validation
326 ELSIF ((l_ship_method_code IS NOT NULL ) OR (l_ship_method IS NOT NULL )) THEN
327 --{
328
329 IF l_debug_on THEN
330 WSH_DEBUG_SV.logmsg(l_module_name,'Calling wsh_util_validate.validate_ship_method',WSH_DEBUG_SV.C_PROC_LEVEL);
331 END IF;
332
333 WSH_UTIL_VALIDATE.validate_ship_method(
334 p_ship_method_code => l_ship_method_code, --Out param
335 p_ship_method_name => l_ship_method, --Out param
336 x_return_status => l_return_status);
337
338 IF l_debug_on THEN
339 wsh_debug_sv.log(l_module_name,'Return Status After Calling wsh_util_validate.validate_ship_method',l_return_status);
340 END IF;
341
342 WSH_UTIL_CORE.api_post_call(
343 p_return_status => l_return_status,
344 x_num_warnings => l_num_warnings,
345 x_num_errors => l_num_errors);
346
347 IF l_debug_on THEN
348 wsh_debug_sv.log(l_module_name,'Fetching wsh_carrier_services data for ship method code',l_ship_method_code);
349 END IF;
350
351 --Fetch carrier_serivce_id and corresponding DFF.
352 OPEN c_check_carrier_sm_exists (l_ship_method_code);
353 FETCH c_check_carrier_sm_exists INTO l_carrier_service_info_tab(1) ;
354
355 IF (c_check_carrier_sm_exists%NOTFOUND) THEN
356 --{
357 CLOSE c_check_carrier_sm_exists;
358 FND_MESSAGE.SET_NAME('WSH','WSH_OI_INVALID_SHIP_METHOD'); --Bug 9706100 added error message
359 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
360 wsh_util_core.add_message(x_return_status,l_module_name);
361 RAISE FND_API.G_EXC_ERROR;
362 --}
363 END IF;
364
365 CLOSE c_check_carrier_sm_exists;
366 --}--End of Ship method validation
367 --Carrier validation
368 ELSIF ((l_carrier_id IS NOT NULL ) OR (l_freight_code IS NOT NULL)) THEN
369 --{
370 IF l_debug_on THEN
371 wsh_debug_sv.logmsg(l_module_name,'Calling WSH_UTIL_VALIDATE.Validate_Freight_Code',WSH_DEBUG_SV.C_PROC_LEVEL);
372 END IF;
373
374 WSH_UTIL_VALIDATE.Validate_Freight_Code(
375 x_Carrier_id => l_carrier_id, --out param
376 p_freight_code => l_freight_code,
377 x_return_status => l_return_status);
378
379 IF l_debug_on THEN
380 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_UTIL_VALIDATE.Validate_Freight_Code',l_return_status);
381 END IF;
382
383 WSH_UTIL_CORE.api_post_call(
384 p_return_status => l_return_status,
385 x_num_warnings => l_num_warnings,
386 x_num_errors => l_num_errors);
387
388 OPEN get_carrier_ser_info(l_carrier_id);
389 FETCH get_carrier_ser_info BULK COLLECT INTO l_carrier_service_info_tab;
390 CLOSE get_carrier_ser_info;
391 --}
392 END IF;
393 --End of Carrier validation
394
395 IF l_carrier_service_info_tab.COUNT > 0 THEN
396 --{
397 --Call Group API
398 FOR l_index IN l_carrier_service_info_tab.FIRST..l_carrier_service_info_tab.LAST LOOP
399 --{
400 --Not populating WHO columns in l_shp_methods_dff as they are overridden in grp API.
401 l_shp_methods_dff.ATTRIBUTE_CATEGORY := l_carrier_service_info_tab(l_index).ATTRIBUTE_CATEGORY;
402 l_shp_methods_dff.ATTRIBUTE1 := l_carrier_service_info_tab(l_index).ATTRIBUTE1 ;
403 l_shp_methods_dff.ATTRIBUTE2 := l_carrier_service_info_tab(l_index).ATTRIBUTE2 ;
404 l_shp_methods_dff.ATTRIBUTE3 := l_carrier_service_info_tab(l_index).ATTRIBUTE3;
405 l_shp_methods_dff.ATTRIBUTE4 := l_carrier_service_info_tab(l_index).ATTRIBUTE4 ;
406 l_shp_methods_dff.ATTRIBUTE5 := l_carrier_service_info_tab(l_index).ATTRIBUTE5 ;
407 l_shp_methods_dff.ATTRIBUTE6 := l_carrier_service_info_tab(l_index).ATTRIBUTE6 ;
408 l_shp_methods_dff.ATTRIBUTE7 := l_carrier_service_info_tab(l_index).ATTRIBUTE7 ;
409 l_shp_methods_dff.ATTRIBUTE8 := l_carrier_service_info_tab(l_index).ATTRIBUTE8 ;
410 l_shp_methods_dff.ATTRIBUTE9 := l_carrier_service_info_tab(l_index).ATTRIBUTE9 ;
411 l_shp_methods_dff.ATTRIBUTE10 := l_carrier_service_info_tab(l_index).ATTRIBUTE10;
412 l_shp_methods_dff.ATTRIBUTE11 := l_carrier_service_info_tab(l_index).ATTRIBUTE11;
413 l_shp_methods_dff.ATTRIBUTE12 := l_carrier_service_info_tab(l_index).ATTRIBUTE12 ;
414 l_shp_methods_dff.ATTRIBUTE13 := l_carrier_service_info_tab(l_index).ATTRIBUTE13 ;
415 l_shp_methods_dff.ATTRIBUTE14 := l_carrier_service_info_tab(l_index).ATTRIBUTE14 ;
416 l_shp_methods_dff.ATTRIBUTE15 := l_carrier_service_info_tab(l_index).ATTRIBUTE15 ;
417
418 l_rec_org_car_ser_tab.CARRIER_SERVICE_ID := l_carrier_service_info_tab(l_index).carrier_service_id;
419
420 -- Assigning the carrier service to all orgs which are passed in the parameter.
421 -- Org Loop
422 FOR j in l_org_info_tab.FIRST..l_org_info_tab.LAST LOOP
423 --{
424 l_rec_org_car_ser_tab.ORGANIZATION_ID := l_org_info_tab(j).org_id; -- This keeps changing as we iterate through l_org_info_tab
425 -- removing the reference of previous buffer.
426 l_car_ser_out_rec := null;
427
428 IF l_debug_on THEN
429 wsh_debug_sv.log(l_module_name,'Carrier_service_id',l_carrier_service_info_tab(l_index).carrier_service_id);
430 wsh_debug_sv.log(l_module_name,'Organization_id',l_rec_org_car_ser_tab.ORGANIZATION_ID);
431 wsh_debug_sv.logmsg(l_module_name,'Calling WSH_CARRIERS_GRP.Assign_Org_Carrier_Service',WSH_DEBUG_SV.C_PROC_LEVEL);
432 END IF;
433
434 WSH_CARRIERS_GRP.Assign_Org_Carrier_Service(
435 p_api_version_number => p_api_version_number,
436 p_init_msg_list => p_init_msg_list,
437 p_commit => p_commit,
438 p_action_code => p_action_code,
439 p_rec_org_car_ser_tab => l_rec_org_car_ser_tab,
440 p_rec_car_dff_tab => l_rec_car_info_tab,
441 p_shp_methods_dff => l_shp_methods_dff,
442 x_orgcar_ser_out_rec_tab => l_car_ser_out_rec,
443 x_return_status => l_return_status,
444 x_msg_count => l_msg_count,
445 x_msg_data => l_msg_data );
446
447 IF l_debug_on THEN
448 wsh_debug_sv.log(l_module_name,'Return Status After Calling WSH_CARRIERS_GRP.Assign_Org_Carrier_Service',l_return_status);
449 END IF;
450
451 WSH_UTIL_CORE.api_post_call(
452 p_return_status => l_return_status,
453 x_num_warnings => l_num_warnings,
454 x_num_errors => l_num_errors);
455
456 x_car_out_rec_tab(x_car_out_rec_tab.count + 1) := l_car_ser_out_rec;
457
458 IF l_debug_on THEN
459 wsh_debug_sv.log(l_module_name,'x_car_out_rec_tab(' || To_Char (x_car_out_rec_tab.count) || ').carrier_service_id',x_car_out_rec_tab(x_car_out_rec_tab.count).carrier_service_id);
460 wsh_debug_sv.log(l_module_name,'x_car_out_rec_tab(' || To_Char (x_car_out_rec_tab.count) || ').org_carrier_service_id',x_car_out_rec_tab(x_car_out_rec_tab.count).org_carrier_service_id);
461 END IF;
462 --}
463 END LOOP;
464 --Org Loop
465 --}
466 END LOOP;
467
468 --} If l_carrier_service_info_tab.COUNT > 0
469 ELSE
470 --{
471 IF l_debug_on THEN
472 wsh_debug_sv.logmsg(l_module_name,'** No eligible carrier services found **');
473 END IF;
474 --}
475 END IF;
476 --}
477 END IF; --End of Organization validation
478
479 IF l_num_warnings > 0 THEN
480 x_return_status := wsh_util_core.g_ret_sts_warning;
481 ELSE
482 x_return_status := wsh_util_core.g_ret_sts_success;
483 END IF;
484 --
485 FND_MSG_PUB.Count_And_Get (
486 p_count => x_msg_count,
487 p_data => x_msg_data );
488 --
489 IF l_debug_on THEN
490 WSH_DEBUG_SV.pop(l_module_name);
491 END IF;
492
493 EXCEPTION
494 WHEN FND_API.G_EXC_ERROR THEN
495 --{
496 IF c_check_carrier_ser_exists%ISOPEN THEN
497 CLOSE c_check_carrier_ser_exists;
498 END IF;
499
500 IF c_check_carrier_sm_exists%ISOPEN THEN
501 CLOSE c_check_carrier_sm_exists;
502 END IF;
503
504 IF get_carrier_ser_info%ISOPEN THEN
505 CLOSE get_carrier_ser_info;
506 END IF;
507
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 -- Get message count and data
510 FND_MSG_PUB.Count_And_Get (
511 p_count => x_msg_count,
512 p_data => x_msg_data );
513 --
514 IF l_debug_on THEN
515 WSH_DEBUG_SV.logmsg(l_module_name,'Expected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
516 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
517 END IF;
518 --
519 --}
520 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
521 --{
522 IF c_check_carrier_ser_exists%ISOPEN THEN
523 CLOSE c_check_carrier_ser_exists;
524 END IF;
525
526 IF c_check_carrier_sm_exists%ISOPEN THEN
527 CLOSE c_check_carrier_sm_exists;
528 END IF;
529
530 IF get_carrier_ser_info%ISOPEN THEN
531 CLOSE get_carrier_ser_info;
532 END IF;
533
534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
535 -- Get message count and data
536 FND_MSG_PUB.Count_And_Get(
537 p_count => x_msg_count ,
538 p_data => x_msg_data );
539 --
540 IF l_debug_on THEN
541 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
542 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
543 END IF;
544 --}
545 WHEN OTHERS THEN
546 --{
547 IF c_check_carrier_ser_exists%ISOPEN THEN
548 CLOSE c_check_carrier_ser_exists;
549 END IF;
550
551 IF c_check_carrier_sm_exists%ISOPEN THEN
552 CLOSE c_check_carrier_sm_exists;
553 END IF;
554
555 IF get_carrier_ser_info%ISOPEN THEN
556 CLOSE get_carrier_ser_info;
557 END IF;
558
559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
560 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
561 FND_MSG_PUB.Add_Exc_Msg
562 (
563 G_PKG_NAME
564 , '_x_'
565 );
566 END IF;
567
568 -- Get message count and data
569 FND_MSG_PUB.Count_And_Get (
570 p_count => x_msg_count ,
571 p_data => x_msg_data );
572 --
573 IF l_debug_on THEN
574 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
575 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
576 END IF;
577 --}
578
579 END Assign_Org_Carrier_Service;
580
581 END WSH_CARRIERS_PUB;
[Home] [Help]