[Home] [Help]
PACKAGE BODY: APPS.WSH_U_RASS
Source
1 PACKAGE BODY WSH_U_RASS AS
2 /* $Header: WSHURASB.pls 120.0.12020000.4 2013/02/15 00:01:27 adagur ship $ */
3
4 -- standard global constants
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WSH_U_RASS';
6 p_message_type CONSTANT VARCHAR2(1) := 'E';
7
8
9
10 -- -------------------------------------------------------------------
11 -- Start of comments
12 -- API name : FindServiceRate
13 -- Type : public
14 -- Function : compose the input string, call UPS APIs and parse
15 -- the L_OUTPUT string, place them in the returning
16 -- record
17 -- Version : Initial version 1.0
18 -- Notes : please use :set tabstop=3 to view this file in vi
19 -- to get proper alignment
20 --
21 -- End of comments
22 -- ---------------------------------------------------------------------
23
24 FUNCTION FindServiceRate
25 ( p_api_version IN NUMBER
26 , p_init_msg_list IN VARCHAR2
27 , x_return_status OUT NOCOPY VARCHAR2
28 , x_msg_count OUT NOCOPY NUMBER
29 , x_msg_data OUT NOCOPY VARCHAR2
30 , p_AppVersion IN VARCHAR2
31 , p_AcceptLicenseAgreement IN VARCHAR2
32 , p_ResponseType IN VARCHAR2
33 , p_request_in IN RateServiceInRec)
34 RETURN RateServTableTyp
35 IS
36
37 -- standard version infermation
38 l_api_version CONSTANT NUMBER := 1.0;
39 l_api_name CONSTANT VARCHAR2(30) := 'FindServiceRate';
40
41
42 -- standard variable for calling subroutines
43 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
44 l_msg_count NUMBER := 0;
45 l_msg_data VARCHAR2(2000) := NULL;
46 l_msg_summary VARCHAR2(2000) := NULL;
47 l_msg_details VARCHAR2(4000) := NULL;
48 l_rate_request_in RateServiceInRec; --Added for Carrier Services Integration with Shipping project Bug 16095594
49 WSH_U_SHIPFROMTO EXCEPTION; --Added for Carrier Services Integration with Shipping project Bug 16095594
50 L_OUTPUT RateServTableTyp;
51 /* Commented for Carrier Services Integration with Shipping project Bug 16095594
52 l_request_in RateServiceInRec;
53 L_UPS_URL VARCHAR2(1000) := NULL;
54 L_INTERNET_PROXY VARCHAR2(1000) := NULL;
55
56 L_INPUT_STR VARCHAR2(2000) := NULL;
57 L_OUTPUT_STR VARCHAR2(10000) := NULL;
58
59 l_output_data utl_http.html_pieces;
60 L_Rate_Message VARCHAR2(200) := NULL;
61
62
63 l_boundary_string_start NUMBER := 0;
64 l_boundary_string_end NUMBER := 0;
65 l_boundary_string VARCHAR2(100) := NULL;
66 L_UPSONLINE CONSTANT VARCHAR2(9) := 'UPSOnLine';
67
68 L_Locate_Boundry NUMBER := 1;
69 L_Locate_Str_Len NUMBER := 1;
70 L_Locate_Str_Len_End NUMBER := 1;
71 L_Content_Str_Len NUMBER := 1;
72 L_Locate_Begin NUMBER := 1;
73 L_Locate_Boundry_End NUMBER := 1;
74 L_Token_Start NUMBER := 1;
75 L_Token_End NUMBER := 0;
76 L_Str_Len NUMBER := 0;
77 l_outrec_index BINARY_INTEGER := 0;
78 l_find_error NUMBER := 0;
79 j NUMBER := 0;
80
81 WSH_U_CAR_URL exception;
82 WSH_U_PROXY exception;
83 WSH_U_APPVER exception;
84 WSH_U_LICAGRE exception;
85 WSH_U_RESTYP exception;
86 WSH_U_ACTIONCODE exception;
87 WSH_U_SRVLEVCODE exception;
88 WSH_U_RATECHART exception;
89 WSH_U_SPOSTALCODE exception;
90 WSH_U_CPOSTALCODE exception;
91 WSH_U_CCOUNTRY exception;
92 WSH_U_PKGACTWT exception;
93 WSH_U_RESDIND exception;
94 WSH_U_PKGTYPE exception;
95 WSH_U_NO_HOST exception;
96 REQUEST_FAILED exception;
97 INIT_FAILED exception;
98
99 --Bug 2993856 : Added new exception
100 WSH_U_HOST_FAILED exception;
101 End of Comment for Carrier Services Integration with Shipping project Bug 16095594 */
102
103
104 --
105 l_debug_on BOOLEAN;
106 --
107 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FINDSERVICERATE';
108 --
109 BEGIN
110 --
111 --
112 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
113 --
114 IF l_debug_on IS NULL
115 THEN
116 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
117 END IF;
118 --
119 IF l_debug_on THEN
120 WSH_DEBUG_SV.push(l_module_name);
121 --
122 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',P_API_VERSION);
123 WSH_DEBUG_SV.log(l_module_name,'P_INIT_MSG_LIST',P_INIT_MSG_LIST);
124 WSH_DEBUG_SV.log(l_module_name,'P_APPVERSION',P_APPVERSION);
125 WSH_DEBUG_SV.log(l_module_name,'P_ACCEPTLICENSEAGREEMENT',P_ACCEPTLICENSEAGREEMENT);
126 WSH_DEBUG_SV.log(l_module_name,'P_RESPONSETYPE',P_RESPONSETYPE);
127 END IF;
128 --
129 l_output.delete;
130 -- Standard call to check for call compatibility.
131 IF NOT FND_API.compatible_api_call
132 ( l_api_version
133 , p_api_version
134 , l_api_name
135 , G_PKG_NAME)
136 THEN
137 IF l_debug_on
138 THEN
139 WSH_DEBUG_SV.log(l_module_name,'Not Compatible');
140 END IF;
141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
142 END IF;
143
144 -- Check p_init_msg_list
145 IF FND_API.to_boolean(p_init_msg_list)
146 THEN
147 FND_MSG_PUB.initialize;
148 END IF;
149
150 -- initialize API return status to success
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152
153 x_msg_count := 0;
154 x_msg_data := NULL;
155
156 -- program specific logic begins here
157 --Code starts here for Carrier Services Integration with Shipping project Bug 16095594
158 IF p_request_in.ship_from_location_id IS NULL OR
159 p_request_in.ship_to_location_id IS NULL
160 THEN
161 RAISE WSH_U_SHIPFROMTO;
162 END IF;
163
164 l_rate_request_in := p_request_in;
165
166 IF l_debug_on THEN
167 WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||l_rate_request_in.Entity);
168 WSH_DEBUG_SV.log(l_module_name,'EntityId is '||l_rate_request_in.Entity_Id);
169 END IF;
170
171 wsh_custom_pub.rate_shipment_services
172 ( p_request_in => l_rate_request_in
173 , x_output => l_output
174 , x_msg_count => l_msg_count
175 , x_msg_data => l_msg_data
176 , x_return_status => l_return_status);
177
178 IF l_return_status = FND_API.G_RET_STS_SUCCESS
179 THEN
180 WSH_DEBUG_SV.log(l_module_name,' Inside RETURN status :'||l_return_status);
181 ELSE
182 WSH_DEBUG_SV.log(l_module_name,' Inside RETURN status :'||l_return_status);
183 x_return_status := l_return_status;
184 x_msg_data := l_msg_data;
185 RETURN l_output;
186 END IF;
187
188 /* -- Commented for Carrier Services Integration with Shipping project Bug 16095594
189 L_UPS_URL := WSH_U_UTIL.Get_Carrier_API_URL(
190 p_api_version => 1.0,
191 p_init_msg_list => FND_API.G_TRUE,
192 x_return_status => l_return_status,
193 x_msg_count => l_msg_count,
194 x_msg_data => l_msg_data,
195 p_Carrier_Name => 'UPS',
196 p_API_Name => 'RATING_AND_SERVICE_SELECTION');
197 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
198 raise WSH_U_CAR_URL;
199 end if;
200
201 -- constructing the request sent to UPS API
202 L_INPUT_STR := L_UPS_URL || '?';
203
204 IF l_debug_on THEN
205 WSH_DEBUG_SV.log(l_module_name,'L_UPS_URL',SUBSTR(L_UPS_URL,1,250));
206 END IF;
207 -- ---------------------------------------------------------------
208 -- UPS standard parameters: AppVersion
209 -- AcceptLicenseAgreement
210 -- ResponseType
211 -- ---------------------------------------------------------------
212
213 -- AppVersion ---------------------------------------------------------
214 if( p_AppVersion IS NULL) then
215 raise WSH_U_APPVER;
216 -- l_request_in.AppVersion := '1.1';
217 end if;
218 L_INPUT_STR := L_INPUT_STR || 'AppVersion' ||'='|| p_AppVersion;
219
220 -- AcceptLicenseAgreement ---------------------------------------------
221 if( p_AcceptLicenseAgreement IS NULL) then
222 raise WSH_U_LICAGRE;
223 -- l_request_in.AcceptLicenseAgreement := 'YES';
224 end if;
225 L_INPUT_STR := L_INPUT_STR || '&' ||'AcceptUPSLicenseAgreement'||'=' || p_AcceptLicenseAgreement;
226
227
228 -- ResponseType --------------------------------------------------------
229 if( p_ResponseType IS NULL) then
230 raise WSH_U_RESTYP;
231 -- l_request_in.ResponseType := 'application/x-ups-rss';
232 end if;
233 L_INPUT_STR := L_INPUT_STR || '&' || 'ResponseType' ||'='|| p_ResponseType;
234
235
236
237 -- ---------------------------------------------------------------
238 -- Program specific parameters start here
239 -- ---------------------------------------------------------------
240
241 -- ActionCode --------------------------------------------------------
242 if(l_request_in.ActionCode IS NULL) then
243 raise WSH_U_ACTIONCODE;
244 -- l_request_in.ActionCode := '3';
245 end if;
246 L_INPUT_STR := L_INPUT_STR || '&' || 'ActionCode' ||'='|| l_request_in.ActionCode;
247
248
249 -- ServiceLevelCode ---------------------------------------------------
250 if(l_request_in.ServiceLevelCode IS NULL) then
251 raise WSH_U_SRVLEVCODE;
252 -- l_request_in.ServiceLevelCode := '1DA';
253 end if;
254 L_INPUT_STR := L_INPUT_STR || '&' || 'ServiceLevelCode'||'=' || l_request_in.ServiceLevelCode;
255
256
257 -- RateChart ---------------------------------------------------
258 if(l_request_in.RateChart IS NULL) then
259 raise WSH_U_RATECHART;
260 -- l_request_in.RateChart := 'Regular+Daily+Pickup';
261 end if;
262
263 L_INPUT_STR := L_INPUT_STR || '&' || 'RateChart' ||'='|| l_request_in.RateChart;
264
265
266 -- ShipperPostalCode ---------------------------------------------------
267 if(l_request_in.ShipperPostalCode IS NULL) then
268 raise WSH_U_SPOSTALCODE;
269 -- l_request_in.ShipperPostalCode := '94065';
270 end if;
271
272 L_INPUT_STR := L_INPUT_STR || '&' || 'ShipperPostalCode' ||'='|| l_request_in.ShipperPostalCode;
273
274
275 -- ConsigneePostalCode --------------------------------------------------
276 if(l_request_in.ConsigneePostalCode IS NULL) then
277 raise WSH_U_CPOSTALCODE;
278 -- l_request_in.ConsigneePostalCode := '60089';
279 end if;
280
281 L_INPUT_STR := L_INPUT_STR || '&' || 'ConsigneePostalCode' ||'='|| l_request_in.ConsigneePostalCode;
282
283
284 -- ConsigneeCountry --------------------------------------------------
285 if(l_request_in.ConsigneeCountry IS NULL) then
286
287 raise WSH_U_CCOUNTRY;
288 -- l_request_in.ConsigneeCountry := 'US';
289 end if;
290 L_INPUT_STR := L_INPUT_STR || '&' || 'ConsigneeCountry' ||'='|| l_request_in.ConsigneeCountry;
291
292 -- PackageActualWeight --------------------------------------------------
293 if(l_request_in.PackageActualWeight IS NULL) then
294 raise WSH_U_PKGACTWT;
295 -- l_request_in.PackageActualWeight := 25;
296 end if;
297 L_INPUT_STR := L_INPUT_STR || '&' || 'PackageActualWeight'||'=' ||
298 fnd_number.number_to_canonical(l_request_in.PackageActualWeight);
299
300
301 -- DeclaredValueInsurance --------------------------------------------------
302 if(l_request_in.DeclaredValueInsurance IS NOT NULL) then
303 L_INPUT_STR := L_INPUT_STR || '&' || 'DeclaredValueInsurance'||'=' || fnd_number.number_to_canonical(l_request_in.DeclaredValueInsurance);
304 end if;
305
306
307 -- PackageLength --------------------------------------------------
308 if(l_request_in.PackageLength IS NOT NULL) then
309 L_INPUT_STR := L_INPUT_STR || '&' || 'Length' ||'='|| fnd_number.number_to_canonical(l_request_in.PackageLength);
310 end if;
311
312
313 -- PackageWidth --------------------------------------------------
314 if(l_request_in.PackageWidth IS NOT NULL) then
315 L_INPUT_STR := L_INPUT_STR || '&' || 'Width' ||'='|| fnd_number.number_to_canonical(l_request_in.PackageWidth);
316 end if;
317
318
319
320 -- PackageHight --------------------------------------------------
321 if(l_request_in.PackageHight IS NOT NULL) then
322 L_INPUT_STR := L_INPUT_STR || '&' || 'Hight' ||'='|| fnd_number.number_to_canonical(l_request_in.PackageHight);
323 end if;
324
325
326 -- OverSizeIndicator --------------------------------------------------
327 if(l_request_in.OverSizeIndicator IS NOT NULL) then
328 L_INPUT_STR := L_INPUT_STR || '&' || 'OversizeInd' ||'='|| l_request_in.OverSizeIndicator;
329 end if;
330
331
332 -- CODIndicator --------------------------------------------------
333 if(l_request_in.CODIndicator IS NOT NULL) then
334 L_INPUT_STR := L_INPUT_STR || '&' || 'CODInd' ||'='|| l_request_in.CODIndicator;
335 end if;
336
337
338 -- HazMat --------------------------------------------------
339 if(l_request_in.HazMat IS NOT NULL) then
340 L_INPUT_STR := L_INPUT_STR || '&' || 'HazMat' ||'='|| l_request_in.HazMat;
341 end if;
342
343
344
345
346 -- AdditionalHandlingInd --------------------------------------------------
347 if(l_request_in.AdditionalHandlingInd IS NOT NULL) then
348 L_INPUT_STR := L_INPUT_STR || '&' || 'AdditionalHandlingInd'||'=' || l_request_in.AdditionalHandlingInd;
349 end if;
350
351
352
353 -- CallTagARSInd --------------------------------------------------
354 if(l_request_in.CallTagARSInd IS NOT NULL) then
355 L_INPUT_STR := L_INPUT_STR || '&' || 'CallTagARSInd' ||'='|| l_request_in.CallTagARSInd;
356 end if;
357
358
359 -- SatDeliveryInd --------------------------------------------------
360 if(l_request_in.SatDeliveryInd IS NOT NULL) then
361 L_INPUT_STR := L_INPUT_STR || '&' || 'SatDeliveryInd' ||'='|| l_request_in.SatDeliveryInd;
362 end if;
363
364
365 -- SatPickupInd --------------------------------------------------
366 if(l_request_in.SatPickupInd IS NOT NULL) then
367 L_INPUT_STR := L_INPUT_STR || '&' || 'SatPickupInd' ||'='|| l_request_in.SatPickupInd;
368 end if;
369
370
371 -- DCISInd --------------------------------------------------
372 if(l_request_in.DCISInd IS NOT NULL) then
373 L_INPUT_STR := L_INPUT_STR || '&' || 'DCISInd' ||'='|| l_request_in.DCISInd;
374 end if;
375
376
377 -- VerbalConfirmationInd ------------------------------------------------
378 if(l_request_in.VerbalConfirmationInd IS NOT NULL) then
379 L_INPUT_STR := L_INPUT_STR || '&' || 'VerbalConfirmationInd' ||'='|| l_request_in.VerbalConfirmationInd;
380 end if;
381
382
383
384 -- SNDestinationInd1 --------------------------------------------------
385 if(l_request_in.SNDestinationInd1 IS NOT NULL) then
386 L_INPUT_STR := L_INPUT_STR || '&' || 'SNDestinationInd1' ||'='|| l_request_in.SNDestinationInd1;
387 end if;
388
389
390 -- SNDestinationInd2 --------------------------------------------------
391 if(l_request_in.SNDestinationInd2 IS NOT NULL) then
392 L_INPUT_STR := L_INPUT_STR || '&' || 'SNDestinationInd2' ||'='|| l_request_in.SNDestinationInd2;
393 end if;
394
395
396 -- ResidentialInd --------------------------------------------------
397 if(l_request_in.ResidentialInd IS NULL) then
398 raise WSH_U_RESDIND;
399 -- l_request_in.ResidentialInd := '0';
400 end if;
401
402 L_INPUT_STR := L_INPUT_STR || '&' || 'ResidentialInd' ||'='|| l_request_in.ResidentialInd;
403
404
405 -- PackagingType --------------------------------------------------
406 if(l_request_in.PackagingType IS NULL) then
407 raise WSH_U_PKGTYPE;
408 -- l_request_in.PackagingType := '00';
409 end if;
410
411 L_INPUT_STR := L_INPUT_STR || '&' || 'PackagingType' ||'='|| l_request_in.PackagingType;
412 L_INPUT_STR := REPLACE(L_INPUT_STR, ' ', '+');
413
414 /*DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,0,50));
415 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,51,50));
416 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,101,50));
417 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,151,50));
418 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,201,50));
419 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,251,50));
420 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,301,50));
421 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,351,50));
422 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,401,50));
423 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,451,50));
424 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,501,50));
425 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,551,50));
426 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,601,50));
427 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,651,50));
428 DBMS_OUTPUT.PUT_LINE('INPUT:'||SUBSTR(L_INPUT_STR,701,50));
429
430 IF l_debug_on THEN
431 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,0,50));
432 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,51,50));
433 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,101,50));
434 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,151,50));
435 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,201,50));
436 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,251,50));
437 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,301,50));
438 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,351,50));
439 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,401,50));
440 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,451,50));
441 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,501,50));
442 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,551,50));
443 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,601,50));
444 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,651,50));
445 WSH_DEBUG_SV.log(l_module_name,'Input',SUBSTR(L_INPUT_STR,701,50));
446 END IF;
447 -- clear variables before calling subroutine
448 l_return_status := FND_API.G_RET_STS_SUCCESS;
449 l_msg_count := 0;
450 l_msg_data := NULL;
451
452 -- get proxy server URL
453 --
454 L_INTERNET_PROXY := WSH_U_UTIL.Get_PROXY(
455 p_api_version => 1.0,
456 p_init_msg_list => FND_API.G_TRUE,
457 x_return_status => l_return_status,
458 x_msg_count => l_msg_count,
459 x_msg_data => l_msg_data);
460
461 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
462 raise WSH_U_PROXY;
463 end if;
464
465 -- send request to UPS site
466 if L_INTERNET_PROXY is not NULL then
467 l_output_data := utl_http.request_pieces(L_INPUT_STR,100,L_INTERNET_PROXY);
468 else
469 l_output_data := utl_http.request_pieces(L_INPUT_STR,100);
470 end if;
471 -- when no response is received from a request to a given URL
472 -- then a formatted HTML error message may be returned, it contains the
473 -- following error message
474 l_find_error := INSTR(l_output_data(1), 'Can''t locate remote host');
475 if l_find_error <> 0 then
476 raise WSH_U_NO_HOST;
477 end if;
478
479 -- It is only good for up to 10 iteration else it will fail.
480 FOR i in 1 .. l_output_data.count LOOP
481 L_OUTPUT_STR := L_OUTPUT_STR || l_output_data(i);
482 END LOOP;
483
484
485 l_boundary_string_start := INSTR(L_OUTPUT_STR, 'boundary=', 1 , 1);
486 if l_boundary_string_start <> 0 then
487 l_boundary_string_start := l_boundary_string_start + 9;
488 -- l_boundary_string_end := INSTR(L_OUTPUT_STR, '--', l_boundary_string_start,1);
489 l_boundary_string_end := INSTR(L_OUTPUT_STR, FND_GLOBAL.LOCAL_CHR(13), l_boundary_string_start,1);
490 l_boundary_string := SUBSTR(L_OUTPUT_STR,
491 l_boundary_string_start ,
492 l_boundary_string_end - l_boundary_string_start);
493 else
494
495 l_boundary_string := 'UPSBOUNDARY';
496 end if;
497 l_boundary_string := '--' || l_boundary_string;
498 -- DBMS_OUTPUT.PUT_LINE('Boundary String:'|| l_boundary_string);
499 IF l_debug_on THEN
500 WSH_DEBUG_SV.log(l_module_name,'Boundary String',l_boundary_string);
501 END IF;
502
503 LOOP
504 L_Locate_Boundry := INSTR(L_OUTPUT_STR, l_boundary_string,
505 L_Locate_Boundry ,1);
506
507 L_Locate_Str_Len := INSTR(L_OUTPUT_STR, 'Content-length',L_Locate_Boundry,1);
508 -- DBMS_OUTPUT.PUT_LINE('l_locate_str_len:' ||to_char( L_Locate_Str_Len));
509 IF l_debug_on THEN
510 WSH_DEBUG_SV.log(l_module_name,'l_locate_str_len',l_locate_str_len);
511 END IF;
512 L_Locate_Begin := INSTR(L_OUTPUT_STR, L_UPSONLINE ,L_Locate_Boundry,1);
513 L_Locate_Boundry_End := INSTR(L_OUTPUT_STR, l_boundary_string, L_Locate_Boundry,2);
514
515 IF(L_Locate_Boundry_End > L_Locate_Begin) THEN
516
517 IF l_debug_on THEN
518 WSH_DEBUG_SV.log(l_module_name,'J BEGIN',l_outrec_index);
519 END IF;
520 --DBMS_OUTPUT.PUT_LINE('J BEGIN:' || to_char(l_outrec_index));
521 l_outrec_index := l_outrec_index + 1;
522 --DBMS_OUTPUT.PUT_LINE('l_outrec_index END:' || to_char(l_outrec_index));
523 IF l_debug_on THEN
524 WSH_DEBUG_SV.log(l_module_name,'l_outrec_index',l_outrec_index);
525 END IF;
526
527 L_Locate_Str_Len_End := INSTR(L_OUTPUT_STR, FND_GLOBAL.LOCAL_CHR(13), L_Locate_Str_Len, 1);
528 -- DBMS_OUTPUT.PUT_LINE('l_locate_str_len_end:' ||to_char( L_Locate_Str_Len_end));
529 IF l_debug_on THEN
530 WSH_DEBUG_SV.log(l_module_name,'l_locate_str_len_end',l_locate_str_len_end);
531 END IF;
532 L_Content_Str_Len := TO_NUMBER(SUBSTR(L_OUTPUT_STR,(L_Locate_Str_Len+16),L_Locate_Str_Len_End - L_Locate_Str_Len - 16));
533
534 -- DBMS_OUTPUT.PUT_LINE('string length:' || to_char(L_Content_Str_Len));
535 IF l_debug_on THEN
536 WSH_DEBUG_SV.log(l_module_name,'L_Content_Str_Len',L_Content_Str_Len);
537 END IF;
538
539 L_Rate_Message := SUBSTR(L_OUTPUT_STR, L_Locate_Begin, L_Content_Str_Len);
540 L_Token_Start := 0;
541 L_Token_End := 0;
542
543 L_OUTPUT(l_outrec_index).UPSOnLine := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
544 L_OUTPUT(l_outrec_index).AppVersion := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
545 L_OUTPUT(l_outrec_index).ReturnCode := TO_NUMBER(WSH_U_UTIL.Calculate_Token(L_Rate_Message, L_Token_Start,L_Token_End));
546 L_OUTPUT(l_outrec_index).MessageText := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
547 L_OUTPUT(l_outrec_index).MessageNumber := TO_NUMBER(SUBSTR(L_OUTPUT(l_outrec_index).MessageText,1,4));
548 L_OUTPUT(l_outrec_index).MessageText := SUBSTR(L_OUTPUT(l_outrec_index).MessageText,5,(LENGTH(L_OUTPUT(l_outrec_index).MessageText)-4));
549
550
551 IF(L_OUTPUT(l_outrec_index).ReturnCode = 0) THEN
552 L_OUTPUT(l_outrec_index).ActionCode := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
553 L_OUTPUT(l_outrec_index).ServiceLevelCode := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
554 L_OUTPUT(l_outrec_index).ShipperPostalCode := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
555 L_OUTPUT(l_outrec_index).ShipperCountry := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
556 L_OUTPUT(l_outrec_index).ConsigneePostalCode := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
557 L_OUTPUT(l_outrec_index).ConsigneeCountry := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
558 L_OUTPUT(l_outrec_index).DeliverZone := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
559 L_OUTPUT(l_outrec_index).PackageActualWeight := FND_NUMBER.canonical_to_number(WSH_U_UTIL.Calculate_Token(L_Rate_Message, L_Token_Start,L_Token_End));
560 L_OUTPUT(l_outrec_index).ProductCharge := FND_NUMBER.canonical_to_number(WSH_U_UTIL.Calculate_Token(L_Rate_Message, L_Token_Start,L_Token_End));
561 L_OUTPUT(l_outrec_index).AccessorySurcharge := FND_NUMBER.canonical_to_number(WSH_U_UTIL.Calculate_Token(L_Rate_Message, L_Token_Start,L_Token_End));
562 L_OUTPUT(l_outrec_index).TotalCharge := FND_NUMBER.canonical_to_number(WSH_U_UTIL.Calculate_Token(L_Rate_Message, L_Token_Start,L_Token_End));
563 L_OUTPUT(l_outrec_index).CommitTime := WSH_U_UTIL.Calculate_Token(L_Rate_Message,L_Token_Start,L_Token_End);
564
565 /* if L_OUTPUT(l_outrec_index).MessageText is not NULL then
566 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
567 FND_MESSAGE.SET_NAME('WSH',L_OUTPUT(l_outrec_index).MessageText);
568 WSH_UTIL_CORE.Add_Message('W',l_module_name);
569 end if;
570
571 ELSE
572 FND_MESSAGE.SET_NAME('WSH',L_OUTPUT(l_outrec_index).MessageText);
573 WSH_UTIL_CORE.Add_Message('E',l_module_name);
574
575 -- Bug #2993856 : Adding the error message to the message stack
576 x_return_status := FND_API.G_RET_STS_ERROR;
577 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_E');
578 FND_MESSAGE.SET_TOKEN('MSG_TEXT',L_OUTPUT(l_outrec_index).MessageText);
579 WSH_UTIL_CORE.Add_Message(p_message_type);
580 L_OUTPUT(l_outrec_index).ActionCode := NULL;
581 L_OUTPUT(l_outrec_index).ServiceLevelCode := NULL;
582 L_OUTPUT(l_outrec_index).ShipperPostalCode := NULL;
583 L_OUTPUT(l_outrec_index).ShipperCountry := NULL;
584 L_OUTPUT(l_outrec_index).ConsigneePostalCode := NULL;
585 L_OUTPUT(l_outrec_index).ConsigneeCountry := NULL;
586 L_OUTPUT(l_outrec_index).DeliverZone := NULL;
587 L_OUTPUT(l_outrec_index).PackageActualWeight := NULL;
588 L_OUTPUT(l_outrec_index).ProductCharge := NULL;
589 L_OUTPUT(l_outrec_index).AccessorySurcharge := NULL;
590 L_OUTPUT(l_outrec_index).TotalCharge := NULL;
591 L_OUTPUT(l_outrec_index).CommitTime := NULL;
592 END IF;
593 END IF;
594
595 L_Locate_Boundry := L_Locate_Boundry_End;
596 EXIT WHEN L_Locate_Begin = 0;
597
598 END LOOP;
599 -- Bug 2993856: Handling the error message returned by the HOST. This exception sends the error text returned from the HOST
600 IF ( x_return_status = FND_API.G_RET_STS_ERROR ) THEN
601 raise WSH_U_HOST_FAILED;
602 END IF;
603
604 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
605 */ -- Commented for Carrier Services Integration with Shipping project Bug 16095594
606 --
607 IF l_debug_on THEN
608 WSH_DEBUG_SV.pop(l_module_name);
609 END IF;
610 --
611 RETURN L_OUTPUT;
612
613
614 EXCEPTION
615 WHEN WSH_U_SHIPFROMTO
616 THEN
617 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_SHIPFROMTO');
618 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Ship to/from Location Id IS null');
619 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
620 x_RETURN_status := FND_API.G_RET_STS_ERROR;
621
622 WSH_UTIL_CORE.get_messages
623 ( 'Y'
624 , l_msg_summary
625 , l_msg_details
626 , x_msg_count);
627
628 IF x_msg_count > 1
629 THEN
630 x_msg_data := l_msg_summary || l_msg_details;
631 ELSE
632 x_msg_data := l_msg_summary;
633 END IF;
634 --
635 -- Debug Statements
636 --
637 IF l_debug_on
638 THEN
639 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_DELDETID exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
640 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_DELDETID');
641 END IF;
642 RETURN l_output;
643 /* Commented for Carrier Services Integration with Shipping project Bug 16095594
644 WHEN WSH_U_CAR_URL THEN
645 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CAR_URL');
646 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
647 x_return_status := FND_API.G_RET_STS_ERROR;
648 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
649 if x_msg_count > 1 then
650 x_msg_data := l_msg_summary || l_msg_details;
651 else
652 x_msg_data := l_msg_summary;
653 end if;
654 --
655 IF l_debug_on THEN
656 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CAR_URL exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
657 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
658 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CAR_URL');
659 END IF;
660 --
661 return L_OUTPUT;
662 WHEN WSH_U_PROXY THEN
663 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_PROXY');
664 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
665 x_return_status := FND_API.G_RET_STS_ERROR;
666 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
667 if x_msg_count > 1 then
668 x_msg_data := l_msg_summary || l_msg_details;
669 else
670 x_msg_data := l_msg_summary;
671 end if;
672 --
673 IF l_debug_on THEN
674 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_PROXY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
675 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
676 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_PROXY');
677 END IF;
678 --
679 return L_OUTPUT;
680
681 -- this exception is produced by UTL_HTTP.REQUEST_PIECES
682 -- The http call fails(for example, bacause of failure of the HTTP
683 -- daemon, or bacause the argument to REQUEST_PIECES cannot be interpreted
684 -- as a URL because it is NULL or has non-HTTP syntax)
685 WHEN REQUEST_FAILED THEN
686 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_REQ_FAILED');
687 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
688 x_return_status := FND_API.G_RET_STS_ERROR;
689 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
690 if x_msg_count > 1 then
691 x_msg_data := l_msg_summary || l_msg_details;
692 else
693 x_msg_data := l_msg_summary;
694 end if;
695 --
696 IF l_debug_on THEN
697 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
698 WSH_DEBUG_SV.logmsg(l_module_name,'REQUEST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
699 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:REQUEST_FAILED');
700 END IF;
701 --
702 return L_OUTPUT;
703
704 -- this exception is produced by UTL_HTTP.REQUEST_PIECES
705 -- Initialization of the HTTP callout subsystem failed
706 -- for invironmental reasons such as lack of available memory
707 WHEN INIT_FAILED THEN
708 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_INIT_FAILED');
709 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
710 x_return_status := FND_API.G_RET_STS_ERROR;
711 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
712 if x_msg_count > 1 then
713 x_msg_data := l_msg_summary || l_msg_details;
714 else
715 x_msg_data := l_msg_summary;
716 end if;
717 --
718 IF l_debug_on THEN
719 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
720 WSH_DEBUG_SV.logmsg(l_module_name,'INIT_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
721 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:INIT_FAILED');
722 END IF;
723 --
724 return L_OUTPUT;
725 WHEN WSH_U_NO_HOST THEN
726 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_NO_HOST');
727 WSH_UTIL_CORE.ADD_MESSAGE('E',l_module_name);
728 x_return_status := FND_API.G_RET_STS_ERROR;
729 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
730 if x_msg_count > 1 then
731 x_msg_data := l_msg_summary || l_msg_details;
732 else
733 x_msg_data := l_msg_summary;
734 end if;
735 --
736 IF l_debug_on THEN
737 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
738 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_NO_HOST exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
739 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_NO_HOST');
740 END IF;
741 --
742 return L_OUTPUT;
743 WHEN WSH_U_APPVER THEN
744 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_APPVER');
745 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
746 x_return_status := FND_API.G_RET_STS_ERROR;
747 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
748 if x_msg_count > 1 then
749 x_msg_data := l_msg_summary || l_msg_details;
750 else
751 x_msg_data := l_msg_summary;
752 end if;
753 --
754 IF l_debug_on THEN
755 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
756 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_APPVER exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
757 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_APPVER');
758 END IF;
759 --
760 return L_OUTPUT;
761 WHEN WSH_U_LICAGRE THEN
762 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_LICAGRE');
763 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
764 x_return_status := FND_API.G_RET_STS_ERROR;
765 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
766 if x_msg_count > 1 then
767 x_msg_data := l_msg_summary || l_msg_details;
768 else
769 x_msg_data := l_msg_summary;
770 end if;
771 --
772 IF l_debug_on THEN
773 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
774 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_LICAGRE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
775 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_LICAGRE');
776 END IF;
777 --
778 return L_OUTPUT;
779 WHEN WSH_U_RESTYP THEN
780 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_RESTYP');
781 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
782 x_return_status := FND_API.G_RET_STS_ERROR;
783 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
784 if x_msg_count > 1 then
785 x_msg_data := l_msg_summary || l_msg_details;
786 else
787 x_msg_data := l_msg_summary;
788 end if;
789 --
790 IF l_debug_on THEN
791 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
792 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_RESTYP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
793 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_RESTYP');
794 END IF;
795 --
796 return L_OUTPUT;
797 WHEN WSH_U_ACTIONCODE THEN
798 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_ACTIONCODE');
799 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
800 x_return_status := FND_API.G_RET_STS_ERROR;
801 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
802 if x_msg_count > 1 then
803 x_msg_data := l_msg_summary || l_msg_details;
804 else
805 x_msg_data := l_msg_summary;
806 end if;
807 --
808 IF l_debug_on THEN
809 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
810 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_ACTIONCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
811 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_ACTIONCODE');
812 END IF;
813 --
814 return L_OUTPUT;
815 WHEN WSH_U_SRVLEVCODE THEN
816 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_SRVLEVCODE');
817 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
818 x_return_status := FND_API.G_RET_STS_ERROR;
819 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
820 if x_msg_count > 1 then
821 x_msg_data := l_msg_summary || l_msg_details;
822 else
823 x_msg_data := l_msg_summary;
824 end if;
825 --
826 IF l_debug_on THEN
827 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
828 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_SRVLEVCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
829 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_SRVLEVCODE');
830 END IF;
831 --
832 return L_OUTPUT;
833 WHEN WSH_U_RATECHART THEN
834 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_RATECHART');
835 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
838 if x_msg_count > 1 then
839 x_msg_data := l_msg_summary || l_msg_details;
840 else
841 x_msg_data := l_msg_summary;
842 end if;
843 --
844 IF l_debug_on THEN
845 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
846 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_RATECHART exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
847 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_RATECHART');
848 END IF;
849 --
850 return L_OUTPUT;
851 WHEN WSH_U_SPOSTALCODE THEN
852 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_SPOSTALCODE');
853 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
854 x_return_status := FND_API.G_RET_STS_ERROR;
855 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
856 if x_msg_count > 1 then
857 x_msg_data := l_msg_summary || l_msg_details;
858 else
859 x_msg_data := l_msg_summary;
860 end if;
861 --
862 IF l_debug_on THEN
863 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
864 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_SPOSTALCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
865 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_SPOSTALCODE');
866 END IF;
867 --
868 return L_OUTPUT;
869 WHEN WSH_U_CPOSTALCODE THEN
870 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CPOSTALCODE');
871 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
872 x_return_status := FND_API.G_RET_STS_ERROR;
873 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
874 if x_msg_count > 1 then
875 x_msg_data := l_msg_summary || l_msg_details;
876 else
877 x_msg_data := l_msg_summary;
878 end if;
879 --
880 IF l_debug_on THEN
881 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
882 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CPOSTALCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
883 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CPOSTALCODE');
884 END IF;
885 --
886 return L_OUTPUT;
887 WHEN WSH_U_CCOUNTRY THEN
888 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CCOUNTRY');
889 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
890 x_return_status := FND_API.G_RET_STS_ERROR;
891 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
892 if x_msg_count > 1 then
893 x_msg_data := l_msg_summary || l_msg_details;
894 else
895 x_msg_data := l_msg_summary;
896 end if;
897 --
898 IF l_debug_on THEN
899 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
900 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CCOUNTRY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
901 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CCOUNTRY');
902 END IF;
903 --
904 return L_OUTPUT;
905 WHEN WSH_U_PKGACTWT THEN
906 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_PKGACTWT');
907 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
908 x_return_status := FND_API.G_RET_STS_ERROR;
909 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
910 if x_msg_count > 1 then
911 x_msg_data := l_msg_summary || l_msg_details;
912 else
913 x_msg_data := l_msg_summary;
914 end if;
915 --
916 IF l_debug_on THEN
917 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
918 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_PKGACTWT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
919 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_PKGACTWT');
920 END IF;
921 --
922 return L_OUTPUT;
923 WHEN WSH_U_RESDIND THEN
924 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_RESDIND');
925 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
926 x_return_status := FND_API.G_RET_STS_ERROR;
927 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
928 if x_msg_count > 1 then
929 x_msg_data := l_msg_summary || l_msg_details;
930 else
931 x_msg_data := l_msg_summary;
932 end if;
933 --
934 IF l_debug_on THEN
935 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
936 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_RESDIND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
937 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_RESDIND');
938 END IF;
939 --
940 return L_OUTPUT;
941 WHEN WSH_U_PKGTYPE THEN
942 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_PKGTYPE');
943 WSH_UTIL_CORE.ADD_MESSAGE(p_message_type,l_module_name);
944 x_return_status := FND_API.G_RET_STS_ERROR;
945 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
946 if x_msg_count > 1 then
947 x_msg_data := l_msg_summary || l_msg_details;
948 else
949 x_msg_data := l_msg_summary;
950 end if;
951 --
952 IF l_debug_on THEN
953 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
954 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_PKGTYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
955 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_PKGTYPE');
956 END IF;
957 --
958 return L_OUTPUT;
959 -- Bug 2993856 :Handling the exception WSH_U_HOST_FAILED
960 WHEN WSH_U_HOST_FAILED THEN
961 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
962 if x_msg_count > 1 then
963 x_msg_data := l_msg_summary || l_msg_details;
964 else
965 x_msg_data := l_msg_summary;
966 end if;
967 -- To remove the prefix word "Error:" from the original error message
968 x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
969
970 --
971 IF l_debug_on THEN
972 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
973 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
974 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
975 END IF;
976 --
977 return L_OUTPUT;
978 End of Comment for Carrier Services Integration with Shipping project Bug 16095594 */
979 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
980 THEN
981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
982 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
983 THEN
984 FND_MSG_PUB.Add_Exc_Msg
985 ( G_PKG_NAME
986 , l_api_name);
987 END IF;
988 FND_MSG_PUB.count_and_get
989 ( p_count => x_msg_count
990 , p_data => x_msg_data);
991 --
992 IF l_debug_on
993 THEN
994 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_UNEXPECTED_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
995 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
996 END IF;
997 --
998 RETURN L_OUTPUT;
999 WHEN OTHERS
1000 THEN
1001 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1002 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1003 THEN
1004 FND_MSG_PUB.Add_Exc_Msg
1005 ( G_PKG_NAME
1006 , l_api_name);
1007 END IF;
1008
1009 FND_MSG_PUB.count_and_get
1010 ( p_count => x_msg_count
1011 , p_data => x_msg_data);
1012 --
1013 IF l_debug_on
1014 THEN
1015 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1016 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1017 END IF;
1018 --
1019 RETURN L_OUTPUT;
1020 END FindServiceRate;
1021
1022
1023 -- -------------------------------------------------------------------
1024 -- Start of comments
1025 -- API name : Load_Headers
1026 -- Type : public
1027 -- Function : This procedure is used by the form to populate
1028 -- the UPS_SRV_HEADER block, the select statement
1029 -- is passed as a parameter, which is dynamically
1030 -- constructed as the transaction form passes the
1031 -- selected delivery_detail_id to the UPS Rate
1032 -- and Service Selection form
1033 -- Output : a table of ship_from_location_id and
1034 -- ship_to_location_id
1035 -- Version : Initial version 1.0
1036 -- Notes
1037 --
1038 --
1039 -- End of comments
1040 -- ---------------------------------------------------------------------
1041 PROCEDURE load_headers
1042 ( p_select_statement IN VARCHAR2
1043 , x_headers IN OUT NOCOPY wsh_u_rass.HeaderRecTableTyp)
1044 IS
1045
1046 l_cursorid INTEGER;
1047 l_SelectStmt VARCHAR2(3000) := NULL;
1048 l_ship_from_location_id NUMBER := 0;
1049 l_ship_to_location_id NUMBER := 0;
1050 l_dummy INTEGER;
1051 l_index INTEGER := 0;
1052
1053 --
1054 l_debug_on BOOLEAN;
1055 --
1056 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOAD_HEADERS';
1057 --
1058 BEGIN
1059
1060
1061 /* construct a dynamic sql select statement and get the
1062 records */
1063
1064 /* the sql statement should look like
1065 select distinct ship_from_location_id, ship_to_location_id
1066 from (
1067 select * from wsh_delivery_detail_id where delivery_detail_id
1068 in (4941, 4942))
1069 */
1070
1071 --
1072 --
1073 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1074 --
1075 IF l_debug_on IS NULL
1076 THEN
1077 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1078 END IF;
1079 --
1080 IF l_debug_on
1081 THEN
1082 WSH_DEBUG_SV.push(l_module_name);
1083 --
1084 WSH_DEBUG_SV.log(l_module_name,'P_SELECT_STATEMENT',P_SELECT_STATEMENT);
1085 END IF;
1086 --
1087 l_cursorid := DBMS_SQL.OPEN_CURSOR;
1088 l_SelectStmt := p_select_statement;
1089 DBMS_SQL.PARSE(l_CursorID, l_SelectStmt, DBMS_SQL.V7);
1090 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 1, l_ship_from_location_id);
1091 DBMS_SQL.DEFINE_COLUMN(l_CursorID, 2, l_ship_to_location_id);
1092 l_dummy := DBMS_SQL.EXECUTE(l_CursorID);
1093
1094 LOOP
1095 IF DBMS_SQL.FETCH_ROWS(l_CursorID) = 0
1096 THEN
1097 EXIT;
1098 END IF;
1099 DBMS_SQL.COLUMN_VALUE(l_CursorID, 1, l_ship_from_location_id);
1100 DBMS_SQL.COLUMN_VALUE(l_CursorID, 2, l_ship_to_location_id);
1101 l_index := l_index + 1;
1102 x_headers(l_index).ship_from_location_id := l_ship_from_location_id;
1103 x_headers(l_index).ship_to_location_id := l_ship_to_location_id;
1104 IF l_debug_on
1105 THEN
1106 WSH_DEBUG_SV.log(l_module_name,'l_ship_from_location_id',l_ship_from_location_id);
1107 WSH_DEBUG_SV.log(l_module_name,'l_ship_to_location_id',l_ship_to_location_id);
1108 END IF;
1109 END LOOP;
1110 --
1111 IF l_debug_on
1112 THEN
1113 WSH_DEBUG_SV.pop(l_module_name);
1114 END IF;
1115 --
1116 END load_headers;
1117
1118 END WSH_U_RASS;
1119