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