[Home] [Help]
PACKAGE BODY: APPS.WSH_CUSTOM_PUB
Source
1 PACKAGE BODY WSH_CUSTOM_PUB as
2 /* $Header: WSHCSPBB.pls 120.6.12020000.2 2013/02/15 02:19:39 adagur ship $ */
6 G_UPS_ACCESS_TOKEN CONSTANT VARCHAR2(100) := 'UPS_WS_ACCESS_TOKEN';
3 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_CUSTOM_PUB';
4 G_UPS_WS_USERNAME CONSTANT VARCHAR2(100) := 'UPS_WS_USERNAME';
5 G_UPS_WS_PASSWD CONSTANT VARCHAR2(100) := 'UPS_WS_PASSWORD';
7 G_ORA_WALLET_PATH CONSTANT VARCHAR2(200) := 'WALLET_PATH';
8 G_ORA_WALLET_PASSSWD CONSTANT VARCHAR2(200) := 'WALLET_PASSWORD';
9
10 --
11 -- Procedure: Delivery_Name
12 -- Parameters: All Attributes of a Delivery Record
13 -- Description: This procedure will create a delivery. It will
14 -- return to the use the delivery_id and name (if
15 -- not provided as a parameter.
16 --
17
18 FUNCTION Delivery_Name
19 (
20 p_delivery_id IN NUMBER,
21 p_delivery_info IN WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type
22 ) RETURN VARCHAR2 IS
23
24 BEGIN
25
26 RETURN (to_char(p_delivery_id));
27
28 END Delivery_Name;
29
30 FUNCTION Trip_Name
31 (
32 p_trip_id IN NUMBER,
33 p_trip_info IN wsh_trips_pvt.trip_rec_type
34 ) RETURN VARCHAR2 IS
35 BEGIN
36 RETURN (to_char(p_trip_id));
37 END Trip_Name;
38
39 --
40 -- Function: Run_PR_SMC_SS_Parallel
41 -- Description: This function is designed for the user to
42 -- customize the running of Pick Release for Ship Sets and SMCs
43 -- in parallel with Regular Items.
44 -- If this is set to 'Y', then Ship Sets/SMCs are not given a
45 -- priority over Regular Items. This can lead to scenarios where
46 -- Ship Sets/SMCs are backordered while Regular Items are picked.
47 -- Oracle Default: Ship Sets/SMCs are not run in Parallel
48 -- Function Default: 'N'
49 --
50
51 FUNCTION Run_PR_SMC_SS_Parallel RETURN VARCHAR2
52 IS
53
54 BEGIN
55 RETURN 'N';
56 END Run_PR_SMC_SS_Parallel;
57
58
59 --
60 -- Function: Credit_Check_Details_Option
61 -- Description: This function is designed for the user to
62 -- customize credit checking for details.
63 -- By default, credit check will be done for all details ('A')
64 -- If the credit check is to be run only for Non-Backordered details,
65 -- then this is set to 'R'.
66 -- If the credit check is to be run only for Backordered details,
67 -- then this is set to 'B'.
68 -- Oracle Default: Credit check for all details.
69 -- Function Default: 'A'
70 --
71
72 FUNCTION Credit_Check_Details_Option RETURN VARCHAR2
73 IS
74
75 BEGIN
76 RETURN 'A';
77 END Credit_Check_Details_Option;
78
79 --Added as a part of bugfix 4995478
80 -- Procedure: ui_location_code
81 -- Parameters:
82
83 -- 1. p_location_type 'HR' stands for internal location
84 -- p_location_type 'HZ' Stands for external location
85 -- 2. p_party_site_numberTbl corresponds to party_site_number in HZ tables (External location)
86 -- 3. p_location_codeTbl corresponds to Location_code in HR tables (Internal location)
87 -- 4. p_address_1Tbl/2Tbl corresponds to address1/2 in HZ table and address_line_1/2 in HR table.
88 -- 5. p_cityTbl corresponds to city in HZ table and town_or_city in HR table.
89 -- 6. p_stateTbl corresponds to state in HZ table region_2 in HR table
90 -- 7. p_provinceTbl corresponds to province in HZ table and region3 in HR table
91 -- 8. p_countyTbl corresponds to region1 in HR table
92 -- 9. p_postal_codeTbl and p_countryTbl corresponds to postal_code and country in HZ/HR table
93 --10. For p_location_type 'HR', p_party_site_numberTbl will be passed as NULL and
94 -- for p_location_type 'HZ', p_location_codeTbl will be passed as NULL.
95
96 -- Description :
97 -- 1) The procedure is designed for the user to customize the location (ui_location_code) information
98 --- displayed in Shipping Forms.
99 -- 2) All required parameter are passed for external and internal location.
100 -- 3)To use this procedure user has to set the value of PL/SQL variable
101 -- x_use_custom_ui_location to 'Y'.
102 -- 4) The function must not return more than 500 characters(or 500 bytes in multi-byte character set).
103 -- Please use substrb function to limit the total length and/or each individual component length..
104 --5) For custom changes to take affect,user has to run 'Import Shipping Location' Concurrent Program.
105
106 PROCEDURE ui_location_code (
107 p_location_type IN VARCHAR2,
108 p_location_idTbl IN WSH_LOCATIONS_PKG.ID_Tbl_Type,
109 p_address_1Tbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type,
110 p_address_2Tbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
111 p_countryTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
112 p_stateTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
113 p_provinceTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
114 p_countyTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
115 p_cityTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
116 p_postal_codeTbl IN WSH_LOCATIONS_PKG.Address_Tbl_Type ,
117 p_party_site_numberTbl IN WSH_LOCATIONS_PKG.LocationCode_Tbl_Type,
118 p_location_codeTbl IN WSH_LOCATIONS_PKG.LocationCode_Tbl_Type,
119 x_use_custom_ui_location OUT NOCOPY VARCHAR2,
120 x_custom_ui_loc_codeTbl OUT NOCOPY WSH_LOCATIONS_PKG.LocationCode_Tbl_Type
121 ) IS
122 l_sqlcode NUMBER;
123 l_sqlerr VARCHAR2(2000);
124 BEGIN
125
126 x_use_custom_ui_location := 'N';
127 ---Sample code start--
128 /*
129 IF p_location_type = 'HZ' THEN
130
131 FOR i IN p_location_idTbl.FIRST..p_location_idTbl.LAST
132 LOOP
133 x_custom_ui_loc_codeTbl(i) := substrb((p_party_site_numberTbl(i)||' : '||p_address_1Tbl(i)||'-'||p_address_2Tbl(i)||'-'||p_cityTbl(i)||'-'||nvl(p_stateTbl(i),p_provinceTbl(i))||'-'|| p_postal_codeTbl(i)||'-'||p_countryTbl(i)),1,500);
134 END LOOP;
135 ELSIF p_location_type = 'HR' THEN
136
137 FOR i IN p_location_idTbl.FIRST..p_location_idTbl.LAST
138 LOOP
139 x_custom_ui_loc_codeTbl(i) := substrb((p_location_codeTbl(i)||' : '||p_address_1Tbl(i)||'-'||p_address_2Tbl(i)||'-'||p_cityTbl(i)||'-'||p_stateTbl(i)||'-'|| p_postal_codeTbl(i)||'-'||p_countryTbl(i)),1,500);
140 END LOOP;
141 END IF;
142
143 EXCEPTION
144 WHEN others THEN
145 l_sqlcode := SQLCODE;
146 l_sqlerr := SQLERRM;
147 WSH_UTIL_CORE.printmsg('In the Others Exception of WSH_CUSTOM_PUB.ui_location_code');
148 WSH_UTIL_CORE.printmsg(l_sqlcode);
149 WSH_UTIL_CORE.printmsg(l_sqlerr);
150 ---Sample code end --
151 */
152 END ui_location_code;
153
154
155
156 -- CUSTOMIZE THE PROCEDURE
157 PROCEDURE Shipped_Lines(
158 p_source_header_id in number,
159 p_source_code in varchar2,
160 p_contact_type in varchar2,
161 p_contact_id in number,
162 p_last_notif_date in date,
163 p_shipped out NOCOPY boolean,
164 p_shipped_lines out NOCOPY varchar2) IS
165
166 CURSOR c_shipped_lines(
167 p_source_header_id in number,
168 p_source_code in varchar2,
169 p_contact_type in varchar2,
170 p_contact_id in number,
171 p_last_notif_date in date) is
172 SELECT
173 msi.segment1,
174 msi.description,
175 lpad(to_char(wnd.initial_pickup_date,'MM/DD/YYYY'),12),
176 wnd.waybill,
177 sum(nvl(wdd.shipped_quantity,0))
178 FROM
179 wsh_delivery_details wdd,
180 wsh_delivery_assignments wda,
181 wsh_new_deliveries wnd,
182 mtl_system_items msi
183 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
184 AND wda.delivery_id = wnd.delivery_id
185 AND wnd.status_code in ('IT','CL')
186 AND wnd.initial_pickup_date > p_last_notif_date
187 AND wdd.inventory_item_id = msi.inventory_item_id
188 AND wdd.organization_id = msi.organization_id
189 AND wdd.source_header_id = p_source_header_id
190 AND wdd.source_code = p_source_code
191 AND nvl(wnd.shipment_direction, 'O') IN ('O','IO') --J Inbound Logistics jckwok
192 AND decode(p_contact_type,
193 'SHIP_TO',wdd.ship_to_contact_id,
194 'SOLD_TO',wdd.sold_to_contact_id,
195 wdd.customer_id) = p_contact_id
196 GROUP BY
197 wdd.source_header_number,
198 wdd.source_header_type_id,
199 wdd.source_line_id,
200 wdd.inventory_item_id,
201 msi.segment1,
202 msi.description,
203 wdd.src_requested_quantity,
204 wnd.initial_pickup_date,
205 wnd.waybill
206 HAVING
207 sum(nvl(wdd.shipped_quantity,0)) > 0;
208
209 l_shipped boolean;
210 l_shipped_lines varchar2(32750);
211 l_part_number varchar2(40);
212 l_part_desc varchar2(240);
213 l_ship_qty number;
214 l_ship_date varchar2(12);
215 l_waybill varchar2(30);
216
217 BEGIN
218
219
220 -- 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
221 l_shipped_lines := ' Quantites';
222 l_shipped_lines := l_shipped_lines|| fnd_global.newline;
223 l_shipped_lines := l_shipped_lines|| 'Part Number Part Description Ship Date Shipped Waybill ';
224 l_shipped_lines := l_shipped_lines|| fnd_global.newline;
225 l_shipped_lines := l_shipped_lines|| '------------------- --------------------------------- --------- ------------ ------------';
226
227 l_shipped := FALSE;
228 open c_shipped_lines(p_source_header_id, p_source_code, p_contact_type, p_contact_id, p_last_notif_date);
229 LOOP
230 fetch c_shipped_lines
231 into l_part_number,
232 l_part_desc,
233 l_ship_date,
234 l_waybill,
235 l_ship_qty;
236 exit when c_shipped_lines%NOTFOUND;
237 l_shipped := TRUE;
238
239 l_shipped_lines := l_shipped_lines|| fnd_global.newline;
243 || lpad(substr(to_char(l_ship_qty),1,9),9) ||' '
240 l_shipped_lines := l_shipped_lines
241 || rpad(substr(l_part_number,1,19) ,19) ||' '
242 || rpad(substr(l_part_desc,1,33) ,33) ||' '
244 || rpad(substr(l_ship_date,1,12) ,12) ||' '
245 || rpad(substr(l_waybill,1,12) ,12);
246 END LOOP;
247 close c_shipped_lines;
248
249 p_shipped := l_shipped;
250 if (l_shipped) then
251 p_shipped_lines := l_shipped_lines;
252 else
253 p_shipped_lines := NULL;
254 end if;
255
256 return;
257 END Shipped_Lines;
258
259 -- CUSTOMIZE THE PROCEDURE
260 PROCEDURE Backordered_Lines(
261 p_source_header_id in number,
262 p_source_code in varchar2,
263 p_contact_type in varchar2,
264 p_contact_id in number,
265 p_last_notif_date in date,
266 p_backordered out NOCOPY boolean,
267 p_backordered_lines out NOCOPY varchar2) IS
268 CURSOR c_backordered_lines(
269 p_source_header_id in number,
270 p_source_code in varchar2,
271 p_contact_type in varchar2,
272 p_contact_id in number,
273 p_last_notif_date in date) is
274 SELECT
275 msi.segment1,
276 msi.description,
277 sum(nvl(wdd.requested_quantity,0))
278 FROM
279 wsh_delivery_details wdd,
280 wsh_delivery_assignments wda,
281 wsh_new_deliveries wnd,
282 mtl_system_items msi
283 WHERE wdd.delivery_detail_id = wda.delivery_detail_id
284 AND wdd.date_scheduled < sysdate
285 --AND wdd.date_scheduled > p_last_notif_date
286 --AND wdd.released_status != 'C'
287 AND wda.delivery_id = wnd.delivery_id (+)
288 AND nvl(wnd.status_code,'XX') not in ('IT','CL')
289 AND wdd.inventory_item_id = msi.inventory_item_id
290 AND wdd.organization_id = msi.organization_id
291 AND wdd.source_header_id = p_source_header_id
292 AND wdd.source_code = p_source_code
293 AND nvl(wdd.line_direction, 'O') IN ('O','IO') --J Inbound Logistics jckwok
294 AND decode(p_contact_type,
295 'SHIP_TO',wdd.ship_to_contact_id,
296 'SOLD_TO',wdd.sold_to_contact_id,
297 wdd.customer_id) = p_contact_id
298 GROUP BY
299 wdd.source_header_number,
300 wdd.source_header_type_id,
301 wdd.source_line_id,
302 wdd.inventory_item_id,
303 msi.segment1,
304 msi.description,
305 wdd.src_requested_quantity
306 HAVING
307 sum(nvl(wdd.requested_quantity,0)) > 0;
308
309 l_backordered boolean;
310 l_backordered_lines varchar2(32750);
311 l_part_number varchar2(40);
312 l_part_desc varchar2(240);
313 l_backorder_qty number;
314
315 BEGIN
316 -- 12345678901234567890123456789012345678901234567890123456789012345678901234567890
317 l_backordered_lines := ' Quantity';
318 l_backordered_lines := l_backordered_lines|| fnd_global.newline;
319 l_backordered_lines := l_backordered_lines|| 'Part Number Part Description Backordered';
320 l_backordered_lines := l_backordered_lines|| fnd_global.newline;
321 l_backordered_lines := l_backordered_lines|| '------------------- --------------------------------- -----------';
322
323 l_backordered := FALSE;
324 open c_backordered_lines(p_source_header_id, p_source_code, p_contact_type, p_contact_id, p_last_notif_date);
325 LOOP
326 fetch c_backordered_lines
327 into l_part_number,
328 l_part_desc,
329 l_backorder_qty;
330 exit when c_backordered_lines%NOTFOUND;
331 l_backordered := TRUE;
332
333 l_backordered_lines := l_backordered_lines|| fnd_global.newline;
334 l_backordered_lines := l_backordered_lines
335 || rpad(substr(l_part_number,1,19) ,19) ||' '
336 || rpad(substr(l_part_desc,1,33) ,33) ||' '
337 || lpad(substr(to_char(l_backorder_qty),1,11),11);
338 END LOOP;
339 close c_backordered_lines;
340
341 p_backordered := l_backordered;
342 if (l_backordered) then
343 p_backordered_lines := l_backordered_lines ;
344 else
345 p_backordered_lines := NULL;
346 end if;
347
348 END Backordered_Lines ;
349
350 -- CUSTOMIZE THIS PROCEDURE
351 PROCEDURE Start_Workflow(
352 p_source_header_id in number,
353 p_source_code in varchar2,
354 p_contact_type in varchar2,
355 p_contact_id in number,
356 p_result out NOCOPY boolean) IS
357 BEGIN
358 p_result := FALSE;
359 END Start_Workflow;
360
361 --PROCEDURE calculate_tp_dates
362 --Based on different parameters from OM, customers can customize their
363 --calculation of the TP dates (Earliest/Latest Ship Dates and Earliest/Latest Delivery Dates).
364 --These will be then used for population at the delivery detail level and will
365 --get propogated upto container or delivery levels at action points such as
366 --assign/pack etc.
367 --NOTE : x_modified out parameter must be returned as 'Y' in order to use this
368 --customized calculation
369
370 PROCEDURE calculate_tp_dates(
371 p_source_line_id NUMBER,
372 p_source_code IN VARCHAR2,
373 x_earliest_pickup_date OUT NOCOPY DATE,
374 x_latest_pickup_date OUT NOCOPY DATE,
375 x_earliest_dropoff_date OUT NOCOPY DATE,
376 x_latest_dropoff_date OUT NOCOPY DATE,
377 x_modified OUT NOCOPY VARCHAR2
378 ) IS
379
380 l_earliest_pickup_date DATE;
381 l_latest_pickup_date DATE;
382 l_earliest_dropoff_date DATE;
383 l_latest_dropoff_date DATE;
384
385 BEGIN
386 --x_modified must be changed to 'Y' if you're customizing this procedure
387 --to calculate dates on your own
388 x_modified:='N';
389 x_earliest_pickup_date :=l_earliest_pickup_date;
390 x_latest_pickup_date :=l_latest_pickup_date;
391 x_earliest_dropoff_date:=l_earliest_dropoff_date;
392 x_latest_dropoff_date :=l_latest_dropoff_date;
393
394 END calculate_tp_dates;
395
396 -- Procedure Override_RIQ_XML_Attributes
397 -- Provides a way to override the attributes: Weight, Volume, Item Dimensions: Length, Width and Height
398 -- for any of the following RIQ actions:
399 -- 1) Choose Ship Method
400 -- 2) Get Ship Method
401 -- 3) Get Ship Method and Rates
402 -- 4) Get Freight Rates
403 -- All the attributes values should be Non-Negative.
404 -- For the Header Level (Consolidation), p_line_id_tab will have more than 1
405 -- record containing all the order line_ids that have been consolidated at the header level
406 -- The only attributes that can be overridden at the Header Level are Weight and Volume.
407 -- For the Line Level/Ship Unit Level, p_line_id_tab will have only 1 record
408 -- with the order line_id and all the attributes can be overridden.
409 -- For Item Dimensions values to be sent as part of RIQ XML, the OTM Item Dimension UOM must be defined
410 -- and the Item Dimensions (Length, Width and Height) should all have valid values.
411 PROCEDURE Override_RIQ_XML_Attributes(
412 p_line_id_tab IN WSH_UTIL_CORE.Id_Tab_Type,
413 x_weight IN OUT NOCOPY NUMBER,
414 x_volume IN OUT NOCOPY NUMBER,
415 x_length IN OUT NOCOPY NUMBER,
416 x_height IN OUT NOCOPY NUMBER,
417 x_width IN OUT NOCOPY NUMBER,
418 x_return_status OUT NOCOPY VARCHAR2
419 ) IS
420
421 BEGIN
422
423 -- Initializing API return status to Success, please do not change this
424 x_return_status := FND_API.G_RET_STS_SUCCESS;
425
426 -- p_line_tab contains the Order Line_id(s) from oe_order_lines_all table
427 -- This can be used to derive the corresponding line level information
428
429 /* Sample Code : Please ensure that values are passed correctly back to caller
430 -- Header Level (p_line_id_tab contains all the order line_ids that are consolidated)
431 IF p_line_id_tab.COUNT > 1 THEN
432 x_weight :=
433 x_volume :=
434
435 -- Line Level for a specific order line_id
436 ELSIF p_line_id_tab.COUNT = 1 THEN
437 x_weight :=
438 x_volume :=
439 x_length :=
440 x_height :=
441 x_width :=
442
443 END IF;
444 */
445
446 EXCEPTION
447 WHEN others THEN
448 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
449 END Override_RIQ_XML_Attributes;
450
451 -- Bug 7131800
452 FUNCTION Cancel_Unpicked_Details_At_ITS(
453 p_source_header_id IN NUMBER,
454 p_source_line_id IN NUMBER,
455 p_source_line_set_id IN NUMBER,
456 p_remain_details_id IN WSH_UTIL_CORE.Id_Tab_Type
457 ) RETURN VARCHAR2 IS
458 l_debug_on BOOLEAN;
459 l_cancel_flag VARCHAR2(1):= 'Y'; -- default Value is to Cancel (old Behaviour)
460 --
461 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||G_PKG_NAME || '.' ||'Cancel_Unpicked_Details_At_ITS';
462 --
463 BEGIN
464 --
465 -- Debug Statements
466 --
467 --
468 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
469 --
470 IF l_debug_on IS NULL
471 THEN
472 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
473 END IF;
474 --
475 IF l_debug_on THEN
476 WSH_DEBUG_SV.push(l_module_name);
477 --
478 WSH_DEBUG_SV.log(l_module_name,'p_source_line_id ', p_source_line_id);
479 WSH_DEBUG_SV.log(l_module_name,'p_source_line_set_id ', p_source_line_set_id);
480 WSH_DEBUG_SV.log(l_module_name,'p_source_header_id ', p_source_header_id);
481 END IF;
482 --
483 -- { Section to be Modified by Customers
484
485 l_cancel_flag := 'Y';
486
487 -- } End Section to be Modified by Customers
488
489 ---
490 IF l_debug_on THEN
491 WSH_DEBUG_SV.log(l_module_name,'l_cancel_flag ', l_cancel_flag);
492 WSH_DEBUG_SV.pop(l_module_name);
493 END IF;
494
495 RETURN (l_cancel_flag);
496
497 END Cancel_Unpicked_Details_At_ITS;
498
499 -- Standalone Project - Start
500 -- This Procedure is the Custom Hook provided to Customers to return default values.
501 -- Purpose : Customer should set default values for Order Type, Price List,
502 -- Payment Term and Currency Code.
503 -- Parameters: x_order_type_id - Order Type
504 -- : x_price_list_id - Price List
505 -- : x_payment_term_id - Payment Term
506 -- : x_currency_code - Currency Code
507 PROCEDURE Get_Standalone_WMS_Defaults (
508 p_transaction_id IN NUMBER,
509 x_order_type_id OUT NOCOPY NUMBER,
510 x_price_list_id OUT NOCOPY NUMBER,
511 x_payment_term_id OUT NOCOPY NUMBER,
512 x_currency_code OUT NOCOPY VARCHAR2 )
513 IS
514 l_order_type_id NUMBER;
515 l_price_list_id NUMBER;
516 l_payment_term_id NUMBER;
517 l_currency_code VARCHAR2(15);
518
519 l_debug_on BOOLEAN;
520 --
521 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||G_PKG_NAME || '.' ||'Get_Standalone_WMS_Defaults';
522 --
523 BEGIN
524 --
525 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
526 --
527 IF l_debug_on IS NULL
528 THEN
529 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
530 END IF;
531 --
532 IF l_debug_on THEN
533 WSH_DEBUG_SV.push(l_module_name);
534 WSH_DEBUG_SV.log(l_module_name, 'p_transaction_id', p_transaction_id);
535 END IF;
536 --
537
538 -- { Section to be Modified by Customers
539
540 l_order_type_id := null;
541 l_price_list_id := null;
542 l_payment_term_id := null;
543 l_currency_code := null;
544
545 -- } End Section to be Modified by Customers
546
547 x_order_type_id := l_order_type_id;
548 x_price_list_id := l_price_list_id;
549 x_payment_term_id := l_payment_term_id;
550 x_currency_code := l_currency_code;
551
552 ---
553 IF l_debug_on THEN
554 WSH_DEBUG_SV.log(l_module_name,'l_order_type_id ', l_order_type_id);
555 WSH_DEBUG_SV.log(l_module_name,'l_price_list_id ', l_price_list_id);
556 WSH_DEBUG_SV.log(l_module_name,'l_payment_term_id ', l_payment_term_id);
557 WSH_DEBUG_SV.log(l_module_name,'l_currency_code ', l_currency_code);
558 WSH_DEBUG_SV.pop(l_module_name);
559 END IF;
560 --
561 END Get_Standalone_WMS_Defaults;
562
563 -- This Procedure is the Custom Hook provided to Customers to handle post process
564 -- of Shipment Request processing.
565 -- If there are any errors then this custom API should rollback to savepoint
566 -- Post_Process_Shipment_Request.
567 -- API should not issue ROLLBACK without Savepoint Post_Process_Shipment_Request.
568 PROCEDURE Post_Process_Shipment_Request (
569 p_transaction_id IN NUMBER,
570 x_return_status OUT NOCOPY VARCHAR2 )
571 IS
572 l_debug_on BOOLEAN;
573 --
574 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' ||G_PKG_NAME || '.' ||'Post_Process_Shipment_Request';
575 --
576 BEGIN
577 --
578 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
579 --
580 IF l_debug_on IS NULL
581 THEN
582 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
583 END IF;
584 --
585 IF l_debug_on THEN
586 WSH_DEBUG_SV.push(l_module_name);
587 WSH_DEBUG_SV.log(l_module_name, 'p_transaction_id', p_transaction_id);
588 END IF;
589 --
590
591 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
592 SAVEPOINT Post_Process_Shipment_Request;
593
594 -- { Section to be Modified by Customers
595
596 -- } End Section to be Modified by Customers
597
598 ---
599 IF l_debug_on THEN
600 WSH_DEBUG_SV.log(l_module_name,'x_return_status', x_return_status);
601 WSH_DEBUG_SV.pop(l_module_name);
602 END IF;
603 --
604 EXCEPTION
605 WHEN OTHERS THEN
606 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
607 ROLLBACK TO Post_Process_Shipment_Request;
608 --
609 IF l_debug_on THEN
610 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
611 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
612 END IF;
613 --
614 END Post_Process_Shipment_Request;
615
616 -- Standalone Project - End
617
618 -- 8424489
619 -- Function Name: Dsno_Output_File_Prefix
620 -- Purpose :
621 -- This function is the custom hook provided for customers to customize
622 -- DSNO output file name. Value returned from this custom function will
623 -- be used as prefix for DSNO output file name to be generated
624 --
625 -- DEFAULT RETURN VALUE IS => DSNO
626 --
627 -- Parameters:
628 -- p_trip_stop_id - Trip Stop Id for which ITS/Outbound Trigerring process is being submitted
629 -- p_doc_number - Document Number suffixed to DSNO output file name
630 -- p_dsno_file_ext - File Extension for DSNO output file to be generated
631 -- Parameter value will be NULL, if Profile 'WSH: DSNO Output File Extension'
632 -- (WSH_DSNO_OUTPUT_FILE_EXT) is NOT set.
633 -- Return:
634 -- If value returned is NULL then DSNO will be prefixed for DSNO Output Filename
635 -- Return value should be VARCHAR2. While customizing customer should take care that length of
636 -- "return value || p_doc_number || '.' || p_dsno_file_ext" is NOT greater than 30 Characters.
637 -- Example:
638 -- Return Value => CUSTOM_FILE_NAME
639 -- p_doc_number => 123456789
640 -- p_dsno_file_ext => txt
641 -- DSNO Output File Name => CUSTOM_FILE_NAME123456789.txt
642 -- length(CUSTOM_FILE_NAME123456789.txt) should not be greater than 30 Characters.
643 --
644 FUNCTION Dsno_Output_File_Prefix(
645 p_trip_stop_id IN NUMBER,
646 p_doc_number IN NUMBER,
647 p_dsno_file_ext IN VARCHAR2 ) RETURN VARCHAR2
648 IS
649 l_dsno_file_prefix VARCHAR2(30);
650
651 --
652 l_debug_on BOOLEAN;
653 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Dsno_Output_File_Prefix';
654 BEGIN
655 --
656 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
657 --
658 IF l_debug_on IS NULL
659 THEN
660 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
661 END IF;
662 --
663 IF l_debug_on THEN
664 WSH_DEBUG_SV.push(l_module_name);
665 WSH_DEBUG_SV.log(l_module_name,'p_trip_stop_id', p_trip_stop_id);
666 WSH_DEBUG_SV.log(l_module_name,'p_doc_number', p_doc_number);
667 WSH_DEBUG_SV.log(l_module_name,'p_dsno_file_ext', p_dsno_file_ext);
668 END IF;
669 --
670
671 -- { Section to be Modified by Customers
672
673 l_dsno_file_prefix := 'DSNO';
674
675 -- } End Section to be Modified by Customers
676
677 -- Make sure that
678 -- length( l_dsno_file_prefix || to_char(p_doc_number) || '.' || p_dsno_file_ext ) <= 30;
679
680 --
681 IF l_debug_on THEN
682 WSH_DEBUG_SV.log(l_module_name,'l_dsno_file_prefix', l_dsno_file_prefix);
683 WSH_DEBUG_SV.pop(l_module_name);
684 END IF;
685 --
686
687 RETURN l_dsno_file_prefix;
688 EXCEPTION
689 WHEN OTHERS THEN
690 --
691 IF l_debug_on THEN
692 WSH_DEBUG_SV.log(l_module_name,'Inside Exception', sqlerrm);
693 WSH_DEBUG_SV.log(l_module_name,'l_dsno_file_prefix', l_dsno_file_prefix);
694 WSH_DEBUG_SV.pop(l_module_name);
695 END IF;
696 --
697 RETURN l_dsno_file_prefix;
698 END Dsno_Output_File_Prefix;
699
700
701 -- TPW - Distributed Organization Changes - Start
702 -- Procedure Name: Shipment_Batch_Group_Criteria
703 -- Purpose :
704 -- This procedure is the custom hook provided for customers to customize
705 -- grouping criteria for Shipment Batches to be generated.
706 --
707 -- Possible return values for all parameter is either 'Y' or 'N'
708 -- 1) If NULL value is returned then it will be treated as 'Y'
709 -- 2) If value returned is other than Y/N then it will be treated as 'N'
710 --
711 -- By Default value for all Grouping criteria is set to 'Y'.
712 --
713 -- Parameters:
714 -- x_grp_by_invoice_to_site - Group By Invoice To Site
715 -- x_grp_by_deliver_to_site - Group By Deliver To Site
716 -- x_grp_by_ship_to_contact - Group By Ship To Contact
717 -- x_grp_by_invoice_to_contact - Group By Invoice To Contact
718 -- x_grp_by_deliver_to_contact - Group By Deliver To Contact
719 -- x_grp_by_ship_method - Group By Ship Method Code
720 -- x_grp_by_freight_terms - Group By Freight Terms
721 -- x_grp_by_fob_code - Group By FOB Code
722 -- x_grp_by_within_order - Group Lines Within(Y)/Across(N) Sales Order
723 --
724 PROCEDURE Shipment_Batch_Group_Criteria(
725 x_grp_by_invoice_to_site OUT NOCOPY VARCHAR2,
726 x_grp_by_deliver_to_site OUT NOCOPY VARCHAR2,
727 x_grp_by_ship_to_contact OUT NOCOPY VARCHAR2,
728 x_grp_by_invoice_to_contact OUT NOCOPY VARCHAR2,
729 x_grp_by_deliver_to_contact OUT NOCOPY VARCHAR2,
730 x_grp_by_ship_method OUT NOCOPY VARCHAR2,
731 x_grp_by_freight_terms OUT NOCOPY VARCHAR2,
732 x_grp_by_fob_code OUT NOCOPY VARCHAR2,
733 x_grp_by_within_order OUT NOCOPY VARCHAR2 )
734 IS
735 l_grp_by_invoice_to_site VARCHAR2(1);
736 l_grp_by_deliver_to_site VARCHAR2(1);
737 l_grp_by_ship_to_contact VARCHAR2(1);
738 l_grp_by_invoice_to_contact VARCHAR2(1);
739 l_grp_by_deliver_to_contact VARCHAR2(1);
740 l_grp_by_ship_method VARCHAR2(1);
741 l_grp_by_freight_terms VARCHAR2(1);
742 l_grp_by_fob_code VARCHAR2(1);
743 l_grp_by_within_order VARCHAR2(1);
744 --
745 l_debug_on BOOLEAN;
746 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Shipment_Batch_Group_Criteria';
747 BEGIN
748 --
749 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
750 --
751 IF l_debug_on IS NULL
752 THEN
753 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
754 END IF;
755 --
756 IF l_debug_on THEN
757 WSH_DEBUG_SV.push(l_module_name);
758 END IF;
759 --
760
761 -- { Section to be Modified by Customers
762
763 l_grp_by_invoice_to_site := 'Y';
764 l_grp_by_deliver_to_site := 'Y';
765 l_grp_by_ship_to_contact := 'Y';
766 l_grp_by_invoice_to_contact := 'Y';
767 l_grp_by_deliver_to_contact := 'Y';
768 l_grp_by_ship_method := 'Y';
769 l_grp_by_freight_terms := 'Y';
770 l_grp_by_fob_code := 'Y';
771 l_grp_by_within_order := 'Y';
772
773 -- } End Section to be Modified by Customers
774
775 x_grp_by_invoice_to_site := l_grp_by_invoice_to_site;
776 x_grp_by_deliver_to_site := l_grp_by_deliver_to_site;
777 x_grp_by_ship_to_contact := l_grp_by_ship_to_contact;
778 x_grp_by_invoice_to_contact := l_grp_by_invoice_to_contact;
779 x_grp_by_deliver_to_contact := l_grp_by_deliver_to_contact;
780 x_grp_by_ship_method := l_grp_by_ship_method;
781 x_grp_by_freight_terms := l_grp_by_freight_terms;
782 x_grp_by_fob_code := l_grp_by_fob_code;
783 x_grp_by_within_order := l_grp_by_within_order;
784
785 --
786 IF l_debug_on THEN
787 WSH_DEBUG_SV.pop(l_module_name);
788 END IF;
789 --
790 EXCEPTION
791 WHEN OTHERS THEN
792 --
793 IF l_debug_on THEN
794 WSH_DEBUG_SV.log(l_module_name,'Inside Exception', sqlerrm);
795 WSH_DEBUG_SV.pop(l_module_name);
796 END IF;
797 --
798 END Shipment_Batch_Group_Criteria;
799
800 -- TPW - Distributed Organization Changes - End
801 -- Carrier Services Shipping integration Project 16095594 start
802 --Set the below global variables
803 --G_UPS_WS_USERNAME
804 --G_UPS_WS_PASSWD
805 --G_UPS_ACCESS_TOKEN
806 --G_ORA_WALLET_PATH
807 --G_ORA_WALLET_PASSSWD
808 PROCEDURE csp_shipment_services
809 ( p_request_in IN WSH_U_CSPV.CSPValidateInRec
810 , x_cspvalidate_out OUT NOCOPY WSH_U_CSPV.CSPValidateOutTblTyp
811 , x_msg_count OUT NOCOPY NUMBER
815 doc DBMS_XMLDOM.DOMDocument;
812 , x_msg_data OUT NOCOPY VARCHAR2
813 , x_return_status OUT NOCOPY VARCHAR2 )
814 IS
816 clobdoc CLOB;
817 l_resp_txt CLOB;
818 xdata XMLTYPE;
819 l_xml XMLTYPE;
820 l_http_req utl_http.req;
821 l_http_resp utl_http.resp;
822 v_read_line VARCHAR2(32767);
823 l_my_scheme VARCHAR2(256);
824 l_my_realm VARCHAR2(256);
825 l_my_proxy BOOLEAN;
826 l_debug_on BOOLEAN;
827
828 l_length NUMBER;
829
830 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
831 l_msg_count NUMBER := 0;
832 l_msg_data VARCHAR2(2000) := NULL;
833 l_internet_proxy VARCHAR2(1000) := NULL;
834 l_carrier_api_url VARCHAR2(500) := NULL;
835 l_msg_summary VARCHAR2(2000) := NULL;
836 l_msg_details VARCHAR2(4000) := NULL;
837 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'csp_shipment_services';
838
839
840 l_index PLS_INTEGER;
841 l_act_index PLS_INTEGER;
842 l_xml_index PLS_INTEGER;
843 l_csp_request_in WSH_U_CSPV.CSPValidateInRec;
844 l_rate_request_in WSH_U_RASS.RateServiceInRec;
845 wsh_u_proxy EXCEPTION;
846 wsh_u_host_failed EXCEPTION;
847 wsh_u_cityorzip EXCEPTION;
848 wsh_u_csp EXCEPTION;
849 wsh_u_ccountry EXCEPTION;
850 wsh_u_car_url EXCEPTION;
851 l_av_soap_request VARCHAR2(32767);
852 l_namespace VARCHAR2(1000);
853 l_commit_time VARCHAR2(100);
854 l_errmsg_txt VARCHAR2(1000);
855
856 BEGIN
857 ---- initialize API return status to null
858 -- if the below code is commented then the return status should be null
859 x_return_status := NULL;
860
861 -- Commenting the below UPS WS Integeration code
862 --If Customer want to use UPS WS Integ, they need to uncomment the below code
863 /*
864 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
865
866
867 IF l_debug_on IS NULL
868 THEN
869 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
870 END IF;
871 IF l_debug_on THEN
872 WSH_DEBUG_SV.push(l_module_name);
873 END IF;
874 l_carrier_api_url := 'https://onlinetools.ups.com/ups.app/xml/AV'; --UPS Prod URL
875 dbms_lob.createTemporary(clobdoc,TRUE);
876 x_msg_count := 0;
877 x_msg_data := NULL;
878 -- Added by SHTADEPA
879 IF l_debug_on THEN
880 WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
881 WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
882 END IF;
883
884
885 l_csp_request_in := p_request_in;
886
887 l_av_soap_request := '<?xml version="1.0"?>
888 <AccessRequest>
889 <AccessLicenseNumber>'||G_UPS_ACCESS_TOKEN||'</AccessLicenseNumber>
890 <UserId>'||G_UPS_WS_USERNAME||'</UserId>
891 <Password>'||G_UPS_WS_PASSWD||'</Password>
892 </AccessRequest>
893 <?xml version="1.0"?>
894 <AddressValidationRequest xml:lang="en-US">
895 <Request>
896 <TransactionReference>
897 <CustomerContext>Customer Data</CustomerContext>
898 <XpciVersion>1.0001</XpciVersion>
899 </TransactionReference>
900 <RequestAction>AV</RequestAction>
901 </Request>
902 <Address> ';
903 IF (l_csp_request_in.City IS NULL AND
904 l_csp_request_in.StateProv IS NULL AND
905 l_csp_request_in.PostalCode IS NULL)
906 THEN
907 RAISE WSH_U_CSP;
908 END IF;
909 IF l_csp_request_in.City IS NULL
910 THEN
911 IF l_csp_request_in.StateProv IS NOT NULL AND
912 l_csp_request_in.PostalCode IS NULL
913 THEN
914 RAISE wsh_u_cityorzip;
915 END IF;
916 ELSE
917 l_av_soap_request := l_av_soap_request||' <City>'||l_csp_request_in.city||'</City> ';
918 END IF;
919
920 IF l_csp_request_in.stateprov IS NOT NULL
921 THEN
922 IF l_csp_request_in.City IS NULL AND
923 l_csp_request_in.PostalCode is NULL
924 THEN
925 raise wsh_u_cityorzip;
926 END IF;
927 l_av_soap_request := l_av_soap_request||' <StateProvinceCode>'||l_csp_request_in.stateprov||'</StateProvinceCode> ';
928 END IF;
929
930 IF l_csp_request_in.PostalCode IS NULL
931 THEN
932 IF l_csp_request_in.StateProv is NOT NULL AND
933 l_csp_request_in.City is NULL
934 THEN
935 raise wsh_u_cityorzip;
936 END IF;
937 ELSE
938 l_av_soap_request := l_av_soap_request||' <PostalCode>'||l_csp_request_in.postalcode||'</PostalCode> ';
939 END IF;
940
941 l_av_soap_request := l_av_soap_request ||' </Address>
942 </AddressValidationRequest>';
943
944
945
946 IF l_debug_on THEN
947 WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
948 WSH_DEBUG_SV.log(l_module_name,l_av_soap_request);
949 END IF;
950
951 IF l_debug_on THEN
952 WSH_DEBUG_SV.log(l_module_name,'**************Now sending request to UPS Url '||l_carrier_api_url);
953 END IF;
954
955 l_internet_proxy := wsh_u_util.get_proxy
956 ( p_api_version => 1.0
957 , p_init_msg_list => FND_API.G_TRUE
958 , x_return_status => l_return_status
959 , x_msg_count => l_msg_count
960 , x_msg_data => l_msg_data);
961
962 IF l_debug_on
963 THEN
964 WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
965 WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
966 END IF;
967
968 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
969 THEN
970 RAISE wsh_u_proxy;
971 END IF;
972
973 utl_http.set_wallet(
974 'file:'||G_ORA_WALLET_PATH
975 , G_ORA_WALLET_PASSSWD);
976 utl_http.set_detailed_excp_support ( enable => true );
977 utl_http.set_response_error_check(enable =>true);
978 --Only if there is l_internet_proxy then we need to set it up.
979 IF l_internet_proxy IS NOT NULL
980 THEN
981 utl_http.set_proxy
982 ( l_internet_proxy
983 , NULL);
984 END IF;
985
986 l_http_req := utl_http.begin_request
987 ( l_carrier_api_url
988 , 'POST'
989 ,'HTTP/1.1');
990
991 utl_http.set_header
992 ( l_http_req
993 , 'Content-Type'
994 , 'text/xml') ;
995 utl_http.set_header(l_http_req, 'Content-Length', length(l_av_soap_request)) ;
996 utl_http.set_header(l_http_req, 'SOAPAction', 'initiate');
997
998 utl_http.write_text(l_http_req, l_av_soap_request) ;
999 l_http_resp := utl_http.get_response (l_http_req);
1000
1001 IF l_debug_on
1002 THEN
1003 WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
1004 WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
1005 WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
1006 END IF;
1007
1008 BEGIN
1009 LOOP
1010 utl_http.read_line(
1011 l_http_resp
1012 ,v_read_line
1013 ,TRUE);
1014
1015 l_resp_txt := l_resp_txt || v_read_line;
1016
1017 END LOOP;
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 IF l_debug_on THEN
1021 WSH_DEBUG_SV.log(l_module_name,'Catch while handling response '||sqlerrm);
1022 END IF;
1023 END;
1024
1025 IF l_debug_on THEN
1026 WSH_DEBUG_SV.log(l_module_name,'Debug l_resp_txt is '||l_resp_txt);
1027 END IF;
1028
1029 -- Look for client-side error and report it.
1030 IF l_http_resp.status_code >= 400 AND
1031 l_http_resp.status_code <= 499
1032 THEN
1033 IF l_http_resp.status_code = utl_http.http_unauthorized
1034 THEN
1035 utl_http.get_authentication(
1036 l_http_resp
1037 ,l_my_scheme
1038 ,l_my_realm
1039 ,l_my_proxy);
1040
1041 IF l_my_proxy
1042 THEN
1043 IF l_debug_on THEN
1044 WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
1045 WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
1046 ||l_my_scheme
1047 ||' authentication username/password for realm '
1048 ||l_my_realm
1049 ||' for the proxy server.');
1050 END IF;
1051 ELSE
1052 IF l_debug_on THEN
1053 WSH_DEBUG_SV.log(l_module_name,'Web page '
1054 ||l_carrier_api_url
1055 ||' is protected.');
1056 WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
1057 ||l_my_scheme
1058 ||' authentication username/password for realm '
1059 ||l_my_realm
1060 ||' for the Web page.');
1061 END IF;
1062 END IF;
1063 ELSE
1064 IF l_debug_on THEN
1065 WSH_DEBUG_SV.log(l_module_name,'Please Check the URL.');
1066 END IF;
1067 END IF;
1068
1069 utl_http.end_response(l_http_resp);
1070 RETURN;
1071 -- Look for server-side error and report it.
1072 ELSIF l_http_resp.status_code >= 500 AND
1073 l_http_resp.status_code <= 599
1074 THEN
1075 IF l_debug_on THEN
1076 WSH_DEBUG_SV.log(l_module_name,'Check if the URL is up.');
1077 END IF;
1078
1079 utl_http.end_response(l_http_resp);
1080 RETURN;
1081 END IF;
1082
1083 utl_http.end_response (l_http_resp);
1084
1085 IF l_debug_on THEN
1086 WSH_DEBUG_SV.log(l_module_name,'Parsing Response l_resp_txt');
1087 END IF;
1088
1089 l_xml := xmltype(l_resp_txt);
1090
1091 l_index := 1;
1092 IF l_xml.Existsnode('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']') > 0
1093 THEN
1094 WHILE l_xml.Existsnode('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']') > 0
1095 LOOP
1096 BEGIN
1097 x_cspvalidate_out(l_index).Rank := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Rank/text()').getStringVal();
1098 EXCEPTION
1099 WHEN OTHERS THEN
1100 IF l_debug_on THEN
1101 WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').Rank'||sqlerrm);
1102 END IF;
1103 END;
1104 BEGIN
1105 x_cspvalidate_out(l_index).Quality := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Quality/text()').getStringVal();
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 IF l_debug_on THEN
1109 WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').Quality'||sqlerrm);
1110 END IF;
1111 END;
1112 BEGIN
1113 x_cspvalidate_out(l_index).City := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Address/City/text()').getStringVal();
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 IF l_debug_on THEN
1117 WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').City'||sqlerrm);
1118 END IF;
1119 END;
1120 BEGIN
1121 x_cspvalidate_out(l_index).StateProv := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/Address/StateProvinceCode/text()').getStringVal();
1122 EXCEPTION
1123 WHEN OTHERS THEN
1124 IF l_debug_on THEN
1125 WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').StateProv'||sqlerrm);
1126 END IF;
1127 END;
1128 BEGIN
1129 x_cspvalidate_out(l_index).PostalCodeLow := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/PostalCodeLowEnd/text()').getStringVal();
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 IF l_debug_on THEN
1133 WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').PostalCodeLow'||sqlerrm);
1134 END IF;
1135 END;
1136 BEGIN
1137 x_cspvalidate_out(l_index).PostalCodeHigh := l_xml.extract('/AddressValidationResponse/'||'AddressValidationResult[' || To_Char(l_index) || ']/PostalCodeHighEnd/text()').getStringVal();
1138 EXCEPTION
1139 WHEN OTHERS THEN
1140 IF l_debug_on THEN
1141 WSH_DEBUG_SV.log(l_module_name,'x_cspvalidate_out('||l_index||').PostalCodeHigh'||sqlerrm);
1142 END IF;
1143 END;
1144
1145 l_index := l_index + 1;
1146 END LOOP;
1147 ELSE
1148 BEGIN
1149 l_errmsg_txt := l_xml.extract('/AddressValidationResponse/Response/Error/ErrorDescription/text()').getStringVal();
1150 EXCEPTION
1151 WHEN OTHERS THEN
1152 IF l_debug_on THEN
1153 WSH_DEBUG_SV.log(l_module_name,'Exception while fetching the Address Validation error message :'||sqlerrm);
1154 END IF;
1158 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_E');
1155 l_errmsg_txt := 'Exception while fetching the Address Validation error message';
1156 END;
1157 x_return_status := FND_API.G_RET_STS_ERROR;
1159 FND_MESSAGE.SET_TOKEN('MSG_TEXT',l_errmsg_txt);
1160 WSH_UTIL_CORE.Add_Message(FND_API.G_RET_STS_ERROR);
1161 END IF;
1162
1163
1164
1165 IF x_return_status = FND_API.G_RET_STS_ERROR
1166 THEN
1167 RAISE wsh_u_host_failed;
1168 END IF;
1169
1170 x_return_status := l_return_status ;
1171 */--Commenting the comment UPS WS Integeration code End
1172 EXCEPTION
1173 WHEN WSH_U_PROXY THEN
1174 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_PROXY');
1175 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1176 x_return_status := FND_API.G_RET_STS_ERROR;
1177 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
1178 IF x_msg_count > 1 THEN
1179 x_msg_data := l_msg_summary || l_msg_details;
1180 ELSE
1181 x_msg_data := l_msg_summary;
1182 END IF;
1183 IF l_debug_on THEN
1184 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1185 WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
1186 END IF;
1187 WHEN WSH_U_CSP THEN
1188 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CSP');
1189 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1190 x_return_status := FND_API.G_RET_STS_ERROR;
1191 wsh_util_core.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
1192 IF x_msg_count > 1 THEN
1193 x_msg_data := l_msg_summary || l_msg_details;
1194 ELSE
1195 x_msg_data := l_msg_summary;
1196 END IF;
1197 IF l_debug_on THEN
1198 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CSP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1199 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CSP');
1200 END IF;
1201 WHEN WSH_U_HOST_FAILED THEN
1202 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
1203 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1204 x_return_status := FND_API.G_RET_STS_ERROR;
1205 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
1206 IF x_msg_count > 1 then
1207 x_msg_data := l_msg_summary || l_msg_details;
1208 ELSE
1209 x_msg_data := l_msg_summary;
1210 END IF;
1211 -- To remove the prefix word "Error:" from the original error message
1212 x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
1213
1214 --
1215 IF l_debug_on THEN
1216 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
1217 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1218 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
1219 END IF;
1220
1221 WHEN WSH_U_CITYORZIP THEN
1222 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CITYORZIP');
1223 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1224 x_return_status := FND_API.G_RET_STS_ERROR;
1225 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, l_msg_count);
1226 IF l_msg_count > 1 THEN
1227 x_msg_data := l_msg_summary || l_msg_details;
1228 ELSE
1229 x_msg_data := l_msg_summary;
1230 END IF;
1231
1232 IF l_debug_on THEN
1233 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CITYORZIP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1234 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CITYORZIP');
1235 END IF;
1236 --
1237 WHEN OTHERS THEN
1238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1239 IF sqlcode ='-12543' THEN
1240 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_TNS');
1241 FND_MESSAGE.SET_TOKEN('MSG_TEXT','TNS:destination host unreachable');
1242 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1243 x_return_status := FND_API.G_RET_STS_ERROR;
1244 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
1245 IF x_msg_count > 1 THEN
1246 x_msg_data := l_msg_summary || l_msg_details;
1247 ELSE
1248 x_msg_data := l_msg_summary;
1249 END IF;
1250 IF l_debug_on THEN
1251 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1252 END IF;
1253 RETURN;
1254 ELSIF sqlcode ='-29106'
1255 THEN
1256 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_WALLET_PASSWD');
1257 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Please check oracle wallet password');
1258 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1259 x_return_status := FND_API.G_RET_STS_ERROR;
1260 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
1261 IF x_msg_count > 1 THEN
1262 x_msg_data := l_msg_summary || l_msg_details;
1263 ELSE
1264 x_msg_data := l_msg_summary;
1265 END IF;
1266 IF l_debug_on THEN
1267 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1268 END IF;
1269 RETURN;
1270 ELSIF sqlcode ='-28759'
1271 THEN
1272 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_WALLET_PATH');
1273 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Please check oracle wallet path or its privileges');
1274 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1275 x_return_status := FND_API.G_RET_STS_ERROR;
1276 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
1277 IF x_msg_count > 1 THEN
1278 x_msg_data := l_msg_summary || l_msg_details;
1279 ELSE
1280 x_msg_data := l_msg_summary;
1281 END IF;
1282 IF l_debug_on THEN
1283 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
1284 END IF;
1285 RETURN;
1286
1287
1288 END IF;
1289 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1290 THEN
1291 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'csp_shipment_services');
1292 END IF;
1293 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
1294 --
1295 IF l_debug_on THEN
1296 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1297 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1298 END IF;
1299 END csp_shipment_services;
1300 --Set the below global variables
1301 --G_UPS_WS_USERNAME
1302 --G_UPS_WS_PASSWD
1303 --G_UPS_ACCESS_TOKEN
1304 --G_ORA_WALLET_PATH
1305 --G_ORA_WALLET_PASSSWD
1306 PROCEDURE rate_shipment_services
1307 ( p_request_in IN WSH_U_RASS.RateServiceInRec
1308 , x_output OUT NOCOPY WSH_U_RASS.RateServTableTyp
1309 , x_msg_count OUT NOCOPY NUMBER
1310 , x_msg_data OUT NOCOPY VARCHAR2
1311 , x_return_status OUT NOCOPY VARCHAR2 )
1312 IS
1313 doc DBMS_XMLDOM.DOMDocument;
1314 clobdoc CLOB;
1315 l_resp_txt CLOB;
1316 xdata XMLTYPE;
1317 l_xml XMLTYPE;
1318 l_http_req utl_http.req;
1319 l_http_resp utl_http.resp;
1320 v_read_line VARCHAR2(32767);
1321 l_my_scheme VARCHAR2(256);
1322 l_my_realm VARCHAR2(256);
1323 l_my_proxy BOOLEAN;
1324 l_debug_on BOOLEAN;
1325
1326 l_length NUMBER;
1327 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1328 l_msg_count NUMBER := 0;
1329 l_msg_data VARCHAR2(2000) := NULL;
1330 l_internet_proxy VARCHAR2(1000) := NULL;
1331 l_carrier_api_url VARCHAR2(500) := NULL;
1332 l_msg_summary VARCHAR2(2000) := NULL;
1333 l_msg_details VARCHAR2(4000) := NULL;
1334 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'rate_shipment_services';
1335
1336
1337 l_index PLS_INTEGER;
1338 l_act_index PLS_INTEGER;
1339 l_xml_index PLS_INTEGER;
1340 l_csp_request_in WSH_U_CSPV.CSPValidateInRec;
1341 l_rate_request_in WSH_U_RASS.RateServiceInRec;
1342 wsh_u_proxy EXCEPTION;
1343 wsh_u_host_failed EXCEPTION;
1344 wsh_u_srvlevcode EXCEPTION;
1345 wsh_u_pkgactwt EXCEPTION;
1346 wsh_u_ratechart EXCEPTION;
1347 wsh_u_spostalcode EXCEPTION;
1348 wsh_u_cpostalcode EXCEPTION;
1349 wsh_u_ccountry EXCEPTION;
1350 l_namespace VARCHAR2(1000);
1351 l_commit_time VARCHAR2(100);
1352 l_errmsg_txt VARCHAR2(1000);
1353 l_rate_query VARCHAR2(32767);
1354
1355 TYPE dynamic_cursor IS REF CURSOR;
1356 rate_xml_cur dynamic_cursor;
1357 l_total_weight VARCHAR2(30);
1358 v_decimal VARCHAR2(30);
1359
1360 BEGIN
1361
1362 ---- initialize API return status to null
1363 -- if the below code is commented then the return status should be null
1364 x_return_status := NULL;
1365 -- Commenting the below UPS WS Integeration code
1366 --If Customer want to use UPS WS Integ, they need to uncomment the below code
1367 /*
1368 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1369
1370
1371 IF l_debug_on IS NULL
1372 THEN
1373 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1374 END IF;
1375 IF l_debug_on THEN
1376 WSH_DEBUG_SV.push(l_module_name);
1377 END IF;
1378
1379 dbms_lob.createTemporary(clobdoc,TRUE);
1380 x_msg_count := 0;
1381 x_msg_data := NULL;
1382
1383 -- Added by SHTADEPA
1384 IF l_debug_on THEN
1385 WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
1386 WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
1387 END IF;
1388
1389
1390
1391 -- Valid Pickup code values
1392 -- Default value is 01. Valid values
1393 -- are:
1394 -- 01 - Daily Pickup;
1395 -- 03 - Customer Counter;
1396 -- 06 - One Time Pickup;
1397 -- 07 - On Call Air;
1398 -- 19 - Letter Center;
1399 -- 20 - Air Service Center.
1400
1401 -- Valid Service codes
1402 -- 01 = Next Day Air
1403 -- 02 = 2nd Day Air
1404 -- 03 = Ground
1405 -- 12 = 3 Day Select
1406 -- 13 = Next Day Air Saver
1407 -- 14 = Next Day Air Early AM
1408 -- 59 = 2nd Day Air AM
1409 -- Valid international values:
1410 -- 07 = Worldwide Express
1411 -- 08 = Worldwide Expedited
1412 -- 11= Standard
1413 -- 54 = Worldwide Express Plus
1414 -- 65 = UPS Saver. Required for
1415 -- Rating and Ignored for
1416 -- Shopping
1417 -- Valid Poland to Poland Same
1418 -- Day values:
1419 -- 82 = UPS Today Standard
1420 -- 83 = UPS Today Dedicated
1421 -- Courier
1422 -- 84 = UPS Today Intercity
1423 -- 85 = UPS Today Express
1424 -- 86 = UPS Today Express Saver
1425 -- Note: The valid service code for
1426 -- a FRS Rating Request is
1427 -- 03=Ground
1428
1429 -- Valid Packaging Type code
1430 -- Valid values:
1431 -- 00 = Unknown
1432 -- 01 = UPS Letter
1433 -- 02 = Package/customer
1434 -- supplied
1435 -- 03 = UPS Tube
1436 -- 04 = UPS Pak
1437 -- 21 = Express Box
1438 -- 24 = 25KG Box
1439 -- 25 = 10KG Box
1440 -- 30 = Pallet
1441 -- 2a = Small Express Box
1442 -- 2b = Medium Express Box
1443 -- 2c = Large Express Box
1444 -- For FRS rating requests the
1445 -- only valid value is customer
1446 -- supplied packaging 02.
1447
1448
1449
1450 l_rate_request_in := p_request_in;
1451
1452 IF l_debug_on THEN
1453
1454 WSH_DEBUG_SV.log(l_module_name,'ActionCode :'||l_rate_request_in.ActionCode);
1455 WSH_DEBUG_SV.log(l_module_name,'ServiceLevelCode :'||l_rate_request_in.ServiceLevelCode);
1456 WSH_DEBUG_SV.log(l_module_name,'RateChart :'||l_rate_request_in.RateChart);
1457 WSH_DEBUG_SV.log(l_module_name,'ShipperPostalCode :'||l_rate_request_in.ShipperPostalCode);
1458 WSH_DEBUG_SV.log(l_module_name,'ConsigneePostalCode :'||l_rate_request_in.ConsigneePostalCode);
1459 WSH_DEBUG_SV.log(l_module_name,'ConsigneeCountry :'||l_rate_request_in.ConsigneeCountry);
1460 WSH_DEBUG_SV.log(l_module_name,'PackageActualWeight :'||l_rate_request_in.PackageActualWeight);
1461 WSH_DEBUG_SV.log(l_module_name,'DeclaredValueInsurance :'||l_rate_request_in.DeclaredValueInsurance);
1462 WSH_DEBUG_SV.log(l_module_name,'PackageLength :'||l_rate_request_in.PackageLength);
1463 WSH_DEBUG_SV.log(l_module_name,'PackageWidth :'||l_rate_request_in.PackageWidth);
1464 WSH_DEBUG_SV.log(l_module_name,'PackageHight :'||l_rate_request_in.PackageHight);
1465 WSH_DEBUG_SV.log(l_module_name,'OverSizeIndicator :'||l_rate_request_in.OverSizeIndicator);
1466 WSH_DEBUG_SV.log(l_module_name,'CODIndicator :'||l_rate_request_in.CODIndicator);
1467 WSH_DEBUG_SV.log(l_module_name,'HazMat :'||l_rate_request_in.HazMat);
1468 WSH_DEBUG_SV.log(l_module_name,'AdditionalHandlingInd :'||l_rate_request_in.AdditionalHandlingInd);
1469 WSH_DEBUG_SV.log(l_module_name,'CallTagARSInd :'||l_rate_request_in.CallTagARSInd);
1470 WSH_DEBUG_SV.log(l_module_name,'SatDeliveryInd :'||l_rate_request_in.SatDeliveryInd);
1471 WSH_DEBUG_SV.log(l_module_name,'SatPickupInd :'||l_rate_request_in.SatPickupInd);
1472 WSH_DEBUG_SV.log(l_module_name,'DCISInd :'||l_rate_request_in.DCISInd);
1473 WSH_DEBUG_SV.log(l_module_name,'VerbalConfirmationInd :'||l_rate_request_in.VerbalConfirmationInd);
1474 WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd1 :'||l_rate_request_in.SNDestinationInd1);
1475
1476 WSH_DEBUG_SV.log(l_module_name,'SNDestinationInd2 :'||l_rate_request_in.SNDestinationInd2);
1477 WSH_DEBUG_SV.log(l_module_name,'ResidentialInd :'||l_rate_request_in.ResidentialInd);
1478 WSH_DEBUG_SV.log(l_module_name,'PackagingType :'||l_rate_request_in.PackagingType);
1479 END IF;
1480
1481 IF l_rate_request_in.ServiceLevelCode IS NULL THEN
1482 RAISE wsh_u_srvlevcode;
1483 ELSE
1484 IF l_rate_request_in.ServiceLevelCode = '1DM'
1485 THEN
1486 l_rate_request_in.ServiceLevelCode := '14';
1487 ELSIF l_rate_request_in.ServiceLevelCode = '1DA'
1488 THEN
1489 l_rate_request_in.ServiceLevelCode := '01';
1490 ELSIF l_rate_request_in.ServiceLevelCode = '1DP'
1491 THEN
1492 l_rate_request_in.ServiceLevelCode := '13';
1493 ELSIF l_rate_request_in.ServiceLevelCode = '2DM'
1494 THEN
1495 l_rate_request_in.ServiceLevelCode := '59';
1496 ELSIF l_rate_request_in.ServiceLevelCode = '2DA'
1497 THEN
1498 l_rate_request_in.ServiceLevelCode := '02';
1499 ELSIF l_rate_request_in.ServiceLevelCode = '3DS'
1500 THEN
1501 l_rate_request_in.ServiceLevelCode := '12';
1502 ELSIF l_rate_request_in.ServiceLevelCode = 'GND'
1503 THEN
1504 l_rate_request_in.ServiceLevelCode := '03';
1505 ELSIF l_rate_request_in.ServiceLevelCode = 'STD'
1506 THEN
1507 l_rate_request_in.ServiceLevelCode := '11';
1508 ELSIF l_rate_request_in.ServiceLevelCode = 'XPR'
1509 THEN
1510 l_rate_request_in.ServiceLevelCode := '07';
1511 ELSIF l_rate_request_in.ServiceLevelCode = 'XDM'
1512 THEN
1513 l_rate_request_in.ServiceLevelCode := '54';
1514 ELSIF l_rate_request_in.ServiceLevelCode = 'XPD'
1515 THEN
1516 l_rate_request_in.ServiceLevelCode := '08';
1517 ELSE
1518 l_rate_request_in.ServiceLevelCode := '65';
1519 END IF;
1520 END IF;
1521
1522 IF l_debug_on THEN
1523 WSH_DEBUG_SV.log(l_module_name,'Derived ServiceLevelCode :'||l_rate_request_in.ServiceLevelCode);
1524 END IF;
1525 IF l_rate_request_in.PackageActualWeight IS NULL
1526 THEN
1527 RAISE wsh_u_pkgactwt;
1528 END IF;
1529
1530 IF l_rate_request_in.RateChart IS NULL
1531 THEN
1532 raise WSH_U_RATECHART;
1533 ELSIF l_rate_request_in.RateChart = 'Regular+Daily+Pickup'
1534 THEN
1535 l_rate_request_in.RateChart := '01';
1536 ELSIF l_rate_request_in.RateChart = 'One+Time+Pickup'
1537 THEN
1538 l_rate_request_in.RateChart := '06';
1539 ELSIF l_rate_request_in.RateChart = 'On+Call+Air'
1540 THEN
1541 l_rate_request_in.RateChart := '07';
1542 ELSIF l_rate_request_in.RateChart = 'Letter+Center'
1543 THEN
1544 l_rate_request_in.RateChart := '19';
1545 ELSIF l_rate_request_in.RateChart = 'Customer+Counter'
1546 THEN
1547 l_rate_request_in.RateChart := '03';
1548 ELSE
1549 l_rate_request_in.RateChart := '01';
1550 END IF;
1551 IF l_debug_on THEN
1552 WSH_DEBUG_SV.log(l_module_name,'Derived RateChart :'||l_rate_request_in.RateChart);
1553 END IF;
1554 IF l_rate_request_in.ShipperPostalCode IS NULL
1555 THEN
1556 RAISE wsh_u_spostalcode;
1557 END IF;
1558
1559 IF l_rate_request_in.ConsigneePostalCode IS NULL
1560 THEN
1561 RAISE wsh_u_cpostalcode;
1562 END IF;
1563
1564 IF l_rate_request_in.ConsigneeCountry IS NULL
1565 THEN
1566 RAISE wsh_u_ccountry;
1567 END IF;
1568 IF l_rate_request_in.AdditionalHandlingInd = '1'
1569 THEN
1570 l_rate_request_in.AdditionalHandlingInd := 'Y';
1571 ELSE
1572 l_rate_request_in.AdditionalHandlingInd := 'N';
1573 END IF;
1574 IF l_rate_request_in.SatDeliveryInd = '1'
1575 THEN
1576 l_rate_request_in.SatDeliveryInd := 'Y';
1577 ELSE
1578 l_rate_request_in.SatDeliveryInd := 'N';
1579 END IF;
1580 IF l_rate_request_in.SatPickupInd = '1'
1581 THEN
1582 l_rate_request_in.SatPickupInd := 'Y';
1583 ELSE
1584 l_rate_request_in.SatPickupInd := 'N';
1585 END IF;
1586 IF l_rate_request_in.VerbalConfirmationInd = '1'
1587 THEN
1588 l_rate_request_in.VerbalConfirmationInd := 'Y';
1589 ELSE
1590 l_rate_request_in.VerbalConfirmationInd := 'N';
1591 END IF;
1592 IF l_rate_request_in.ResidentialInd = '1'
1593 THEN
1594 l_rate_request_in.ResidentialInd := 'Y';
1595 ELSE
1596 l_rate_request_in.ResidentialInd := 'N';
1597 END IF;
1598 IF l_rate_request_in.DCISInd >1 THEN
1599 l_rate_request_in.DCISInd := 2;
1600 -- possible values from form are
1601 --0 None
1602 --1 Basic
1603 --2 Signature required
1604 --3 Alternate return address
1605 --4 All available info
1606 -- but with this new integ we can only have below values
1607 -- Valid values are:
1608 -- 1 - Delivery Confirmation;
1609 -- 2 - Delivery Confirmation Signature Required
1613 END IF;
1610 -- 3 - Delivery Confirmation Adult Signature Required
1611 -- so from the form if I am getting the value >1 I am keeping as 2
1612
1614
1615 l_rate_query := 'SELECT xmlelement("env:Envelope",XMLAttributes(''http://schemas.xmlsoap.org/soap/envelope/'' AS "xmlns:env"
1616 ,''http://www.ups.com/XMLSchema/XOLTWS/UPSS/v1.0'' AS " xmlns:ns1"
1617 ,''http://www.ups.com/XMLSchema/XOLTWS/Rate/v1.1'' AS " xmlns:ns2"
1618 ,''http://www.ups.com/XMLSchema/XOLTWS/Common/v1.0'' AS " xmlns:ns3")
1619 ,xmlelement("env:Header",xmlelement("ns1:UPSSecurity",
1620 xmlelement("ns1:UsernameToken",
1621 xmlelement("ns1:Username",:b1 ),
1622 xmlelement("ns1:Password", :b2 )
1623 ),
1624 xmlelement("ns1:ServiceAccessToken",
1625 xmlelement("ns1:AccessLicenseNumber",:b3)
1626 )
1627 )
1628 )
1629 ,xmlelement("env:Body",xmlelement("ns2:RateRequest",
1630 xmlelement("ns3:Request",xmlelement("ns3:RequestOption",''Rate'')
1631 ,xmlelement("ns3:TransactionReference",xmlelement("ns3:CustomerContext",''Rating and Service''))),
1632 xmlelement("ns2:PickupType", xmlelement("ns2:Code",:b4)),
1633 xmlelement("ns2:Shipment", xmlelement("ns2:Shipper",xmlelement("ns2:Address",xmlelement("ns2:CountryCode",w1.country)))
1634 , xmlelement("ns2:ShipTo",xmlelement("ns2:Address", xmlelement("ns2:City",w2.city)
1635 , xmlelement("ns2:StateProvinceCode",w2.state)
1636 , xmlelement("ns2:PostalCode", :b5)
1637 , xmlelement("ns2:CountryCode",:b6)';
1638 IF l_rate_request_in.ResidentialInd = 'Y'
1639 THEN
1640 l_rate_query := l_rate_query|| ', xmlelement("ns2:ResidentialAddressIndicator", ''Y'' ) '; --l_rate_request_in.ResidentialInd
1641 END IF;
1642 l_rate_query := l_rate_query|| ' )) , xmlelement("ns2:ShipFrom",xmlelement("ns2:Address", xmlelement("ns2:City",w1.city)
1643 , xmlelement("ns2:StateProvinceCode",w1.state)
1644 , xmlelement("ns2:PostalCode",:b7)
1645 , xmlelement("ns2:CountryCode",w1.country))) , xmlelement("ns2:Service",xmlelement("ns2:Code", :b8))
1646 , xmlelement("ns2:Package",xmlelement("ns2:PackagingType",xmlelement("ns2:Code",''00''))
1647 , xmlelement("ns2:PackageWeight",xmlelement("ns2:UnitOfMeasurement",xmlelement("ns2:Code",''LBS''))
1648 ,xmlelement("ns2:Weight", :b9)) ';
1649 IF l_rate_request_in.DCISInd >0
1650 THEN
1651 l_rate_query := l_rate_query||' , xmlelement("ns2:PackageServiceOptions",xmlelement("ns2:DeliveryConfirmation",xmlelement("ns2:DCISType",'||l_rate_request_in.DCISInd||'))';
1652 IF l_rate_request_in.VerbalConfirmationInd ='Y'
1653 THEN
1654 l_rate_query := l_rate_query ||' , xmlelement("ns2:VerbalConfirmationIndicator",''Y''))';
1655 ELSE
1656 l_rate_query := l_rate_query ||')' ;
1657 END IF;
1658 ELSE
1659 IF l_rate_request_in.VerbalConfirmationInd ='Y'
1660 THEN
1661 l_rate_query := l_rate_query ||' , xmlelement("ns2:PackageServiceOptions",xmlelement("ns2:VerbalConfirmationIndicator",''Y''))';
1662 END IF;
1663 END IF;
1664 IF l_rate_request_in.AdditionalHandlingInd ='Y'
1665 THEN
1666 l_rate_query := l_rate_query|| ', xmlelement("ns2:AdditionalHandlingIndicator",''Y'') ';
1667 END IF;
1668
1669 l_rate_query := l_rate_query|| ' ) , xmlelement("ns2:ShipmentServiceOptions",xmlelement("ns2:SaturdayPickupIndicator",:b11)
1670 ,xmlelement("ns2:SaturdayDeliveryIndicator",:b12 ) )';
1671
1672 l_rate_query := l_rate_query ||'))))
1673 FROM wsh_locations w1
1674 , wsh_locations w2
1675 WHERE w1.source_location_id = :b13
1676 AND w2.source_location_id = :b14 ';
1677
1678
1679 WSH_DEBUG_SV.log(l_module_name,'dynamic rate query :'||l_rate_query);
1680 WSH_DEBUG_SV.log(l_module_name,'Rate ship_from_location_id'||p_request_in.ship_from_location_id);
1681 WSH_DEBUG_SV.log(l_module_name,'Rate ship_to_location_id'||p_request_in.ship_to_location_id);
1682
1683 OPEN rate_xml_cur
1684 FOR l_rate_query
1685 USING g_ups_ws_username
1686 , g_ups_ws_passwd
1687 , g_ups_access_token
1688 , l_rate_request_in.RateChart
1689 , l_rate_request_in.ConsigneePostalCode
1690 , l_rate_request_in.ConsigneeCountry
1691 , l_rate_request_in.ShipperPostalCode
1692 , l_rate_request_in.ServiceLevelCode
1693 , l_rate_request_in.PackageActualWeight
1694 , l_rate_request_in.SatPickupInd
1695 , l_rate_request_in.SatDeliveryInd
1696 , p_request_in.ship_from_location_id
1697 , p_request_in.ship_to_location_id;
1698
1699 WSH_DEBUG_SV.log(l_module_name,'cursor opened');
1700 FETCH rate_xml_cur INTO xdata;
1701 WSH_DEBUG_SV.log(l_module_name,'fetched from rate cursor');
1702 IF rate_xml_cur%NOTFOUND
1703 THEN
1704 IF l_debug_on THEN
1705 WSH_DEBUG_SV.log(l_module_name,'No Rows Fetched for the ship_from_location_id/ship_to_location_id');
1706 END IF;
1707 RETURN;
1708 END IF;
1709 CLOSE rate_xml_cur;
1710
1711
1712 doc := DBMS_XMLDOM.NewDOMDocument(xdata);
1713
1714 --dbms_xmldom.writetofile(doc, '/usr/tmp/TimeIntransit.xml');
1715
1716 dbms_xmldom.writeToClob(doc,clobdoc) ;
1717
1718 IF l_debug_on THEN
1719 WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
1720 WSH_DEBUG_SV.log(l_module_name,clobdoc);
1721 END IF;
1722 l_carrier_api_url := 'https://onlinetools.ups.com/webservices/Rate'; --UPS Prod URL
1723
1724 IF l_debug_on THEN
1725 WSH_DEBUG_SV.log(l_module_name,'**************Now sending request to UPS Url '||l_carrier_api_url);
1726 END IF;
1727
1728 l_internet_proxy := wsh_u_util.get_proxy
1729 ( p_api_version => 1.0
1730 , p_init_msg_list => FND_API.G_TRUE
1731 , x_return_status => l_return_status
1732 , x_msg_count => l_msg_count
1733 , x_msg_data => l_msg_data);
1734
1735 IF l_debug_on
1736 THEN
1737 WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
1738 WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
1739 END IF;
1740
1741 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1742 THEN
1743 RAISE wsh_u_proxy;
1744 END IF;
1745
1746 utl_http.set_wallet(
1747 'file:'||G_ORA_WALLET_PATH
1748 , G_ORA_WALLET_PASSSWD);
1749
1750 utl_http.set_detailed_excp_support ( enable => true );
1751 utl_http.set_response_error_check(enable =>true);
1752
1753 --Only if there is l_internet_proxy then we need to set it up.
1754 IF l_internet_proxy IS NOT NULL
1755 THEN
1756 utl_http.set_proxy
1757 ( l_internet_proxy
1758 , NULL);
1759 END IF;
1760
1761 l_http_req := utl_http.begin_request
1762 ( l_carrier_api_url
1763 , 'POST'
1764 ,'HTTP/1.1');
1765
1766 utl_http.set_header
1767 ( l_http_req
1768 , 'Content-Type'
1769 , 'text/xml') ;
1770
1771 utl_http.set_header(l_http_req, 'Content-Length', length(clobdoc)) ;
1772 utl_http.set_header(l_http_req, 'SOAPAction', 'initiate');
1773 utl_http.write_text(l_http_req, clobdoc) ;
1774
1775 l_http_resp := utl_http.get_response (l_http_req);
1776
1777 IF l_debug_on
1778 THEN
1779 WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
1780 WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
1781 WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
1782 END IF;
1783
1784 BEGIN
1785 LOOP
1786 utl_http.read_line(
1787 l_http_resp
1788 ,v_read_line
1789 ,TRUE);
1790
1791 l_resp_txt := l_resp_txt || v_read_line;
1792
1793 END LOOP;
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796 IF l_debug_on THEN
1797 WSH_DEBUG_SV.log(l_module_name,'Catch while handling response '||sqlerrm);
1798 END IF;
1799 END;
1800
1801 IF l_debug_on THEN
1802 WSH_DEBUG_SV.log(l_module_name,'Debug l_resp_txt is '||l_resp_txt);
1803 END IF;
1804
1805 -- Look for client-side error and report it.
1806 IF l_http_resp.status_code >= 400 AND
1807 l_http_resp.status_code <= 499
1808 THEN
1809 IF l_http_resp.status_code = utl_http.http_unauthorized
1810 THEN
1811 utl_http.get_authentication(
1812 l_http_resp
1813 ,l_my_scheme
1814 ,l_my_realm
1815 ,l_my_proxy);
1816
1817 IF l_my_proxy
1818 THEN
1819 IF l_debug_on THEN
1820 WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
1821 WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
1822 ||l_my_scheme
1823 ||' authentication username/password for realm '
1824 ||l_my_realm
1825 ||' for the proxy server.');
1826 END IF;
1827 ELSE
1828 IF l_debug_on THEN
1829 WSH_DEBUG_SV.log(l_module_name,'Web page '
1830 ||l_carrier_api_url
1831 ||' is protected.');
1832 WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
1833 ||l_my_scheme
1834 ||' authentication username/password for realm '
1835 ||l_my_realm
1836 ||' for the Web page.');
1837 END IF;
1838 END IF;
1839 ELSE
1840 IF l_debug_on THEN
1841 WSH_DEBUG_SV.log(l_module_name,'Please Check the URL.');
1842 END IF;
1843 END IF;
1844
1845 utl_http.end_response(l_http_resp);
1846 RETURN;
1847 -- Look for server-side error and report it.
1848 ELSIF l_http_resp.status_code >= 500 AND
1849 l_http_resp.status_code <= 599
1850 THEN
1851 IF l_debug_on THEN
1852 WSH_DEBUG_SV.log(l_module_name,'Check if the URL is up.');
1853 END IF;
1854
1855 utl_http.end_response(l_http_resp);
1856 RETURN;
1857 END IF;
1858
1859 utl_http.end_response (l_http_resp);
1860
1861 IF l_debug_on THEN
1862 WSH_DEBUG_SV.log(l_module_name,'Parsing Response l_resp_txt');
1863 END IF;
1864
1865 l_xml := xmltype(l_resp_txt);
1866
1867 SELECT VALUE
1868 INTO v_decimal
1869 FROM NLS_SESSION_PARAMETERS
1870 WHERE PARAMETER = 'NLS_NUMERIC_CHARACTERS';
1871
1872 WSH_DEBUG_SV.log(l_module_name,'v_decimal NLS_NUMERIC_CHARACTERS:'||v_decimal);
1873
1874 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''';
1875
1876
1877 l_namespace := 'xmlns:rate="http://www.ups.com/XMLSchema/XOLTWS/Rate/v1.1"';
1878 l_index := 1;
1879 IF l_xml.Existsnode('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']',l_namespace) > 0
1880 THEN
1881 WHILE l_xml.Existsnode('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']',l_namespace) > 0
1882 LOOP
1883 BEGIN
1884 x_output(l_index).PackageActualWeight := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:RatedPackage/rate:BillingWeight/rate:Weight/text()', l_namespace).getStringVal();
1885 EXCEPTION
1886 WHEN OTHERS THEN
1887 IF l_debug_on THEN
1888 WSH_DEBUG_SV.log(l_module_name,'x_output('||l_index||').PackageActualWeight :'||sqlerrm);
1889 END IF;
1890 END;
1891 BEGIN
1892 x_output(l_index).ProductCharge := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:TransportationCharges/rate:MonetaryValue/text()', l_namespace).getStringVal();
1893 EXCEPTION
1894 WHEN OTHERS THEN
1895 IF l_debug_on THEN
1896 WSH_DEBUG_SV.log(l_module_name,'x_output('||l_index||').ProductCharge :'||sqlerrm);
1897 END IF;
1898 END;
1899 BEGIN
1900 x_output(l_index).AccessorySurcharge := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:ServiceOptionsCharges/rate:MonetaryValue/text()', l_namespace).getStringVal();
1901 EXCEPTION
1902 WHEN OTHERS THEN
1903 IF l_debug_on THEN
1904 WSH_DEBUG_SV.log(l_module_name,'x_output('||l_index||').AccessorySurcharge :'||sqlerrm);
1905 END IF;
1906 END;
1907 BEGIN
1908 x_output(l_index).TotalCharge := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:TotalCharges/rate:MonetaryValue/text()', l_namespace).getStringVal();
1909 EXCEPTION
1910 WHEN OTHERS THEN
1911 IF l_debug_on THEN
1912 WSH_DEBUG_SV.log(l_module_name,'x_output('||l_index||').TotalCharge :'||sqlerrm);
1913 END IF;
1914 END;
1915 BEGIN
1916 l_commit_time := l_xml.extract('//rate:RateResponse/'||'rate:RatedShipment[' || To_Char(l_index) || ']/rate:GuaranteedDelivery/rate:BusinessDaysInTransit/text()', l_namespace).getStringVal();
1917 x_output(l_index).CommitTime := substr(l_commit_time,0,instr(l_commit_time,' ')-1);
1918 EXCEPTION
1919 WHEN NO_DATA_FOUND THEN
1920 x_output(l_index).CommitTime := '-1';
1921 WHEN OTHERS THEN
1922 IF l_debug_on THEN
1923 WSH_DEBUG_SV.log(l_module_name,'x_output('||l_index||').CommitTime :'||sqlerrm);
1924 END IF;
1925 END;
1926 l_index := l_index + 1;
1927 END LOOP;
1928 ELSE
1929 l_namespace := 'xmlns:err="http://www.ups.com/XMLSchema/XOLTWS/Error/v1.1"';
1930 BEGIN
1931 l_errmsg_txt := l_xml.extract('//err:Errors/err:ErrorDetail/err:PrimaryErrorCode/err:Description/text()', l_namespace).getStringVal();
1932 EXCEPTION
1933 WHEN OTHERS THEN
1934 IF l_debug_on THEN
1935 WSH_DEBUG_SV.log(l_module_name,'Exception while fetching the Rate and Services error message :'||sqlerrm);
1936 END IF;
1937 l_errmsg_txt := 'Exception while fetching the Rate and Services error message';
1938 END;
1939 x_return_status := FND_API.G_RET_STS_ERROR;
1940 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_E');
1941 FND_MESSAGE.SET_TOKEN('MSG_TEXT',l_errmsg_txt);
1942 WSH_UTIL_CORE.Add_Message(FND_API.G_RET_STS_ERROR);
1943
1944 x_output(l_index).PackageActualWeight := NULL;
1945 x_output(l_index).ProductCharge := NULL;
1946 x_output(l_index).AccessorySurcharge := NULL;
1947 x_output(l_index).TotalCharge := NULL;
1948
1949 END IF;
1950
1951
1952 IF x_return_status = FND_API.G_RET_STS_ERROR
1953 THEN
1954 RAISE wsh_u_host_failed;
1955 END IF;
1956
1957 x_return_status := l_return_status ;
1958 */
1959 -- End of the comment UPS WS Integeration code
1960 EXCEPTION
1961 WHEN WSH_U_PROXY THEN
1962 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_PROXY');
1963 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1964 x_return_status := FND_API.G_RET_STS_ERROR;
1965 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
1966 IF x_msg_count > 1 THEN
1967 x_msg_data := l_msg_summary || l_msg_details;
1968 ELSE
1969 x_msg_data := l_msg_summary;
1970 END IF;
1971 IF l_debug_on THEN
1972 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1973 WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
1974 END IF;
1975 WHEN WSH_U_SRVLEVCODE THEN
1976 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_SRVLEVCODE');
1977 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1978 x_return_status := FND_API.G_RET_STS_ERROR;
1979 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
1980 IF x_msg_count > 1 THEN
1981 x_msg_data := l_msg_summary || l_msg_details;
1982 ELSE
1983 x_msg_data := l_msg_summary;
1984 END IF;
1985 --
1986 IF l_debug_on THEN
1987 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
1988 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_SRVLEVCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1989 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_SRVLEVCODE');
1990 END IF;
1991 WHEN WSH_U_PKGACTWT THEN
1992 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_PKGACTWT');
1993 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
1994 x_return_status := FND_API.G_RET_STS_ERROR;
1995 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
1996 IF x_msg_count > 1 then
1997 x_msg_data := l_msg_summary || l_msg_details;
1998 ELSE
1999 x_msg_data := l_msg_summary;
2000 END IF;
2001 --
2002 IF l_debug_on THEN
2003 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2004 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_PKGACTWT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2005 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_PKGACTWT');
2006 END IF;
2007 WHEN WSH_U_SPOSTALCODE THEN
2008 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_SPOSTALCODE');
2009 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2010 x_return_status := FND_API.G_RET_STS_ERROR;
2011 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2012 IF x_msg_count > 1 THEN
2013 x_msg_data := l_msg_summary || l_msg_details;
2014 ELSE
2015 x_msg_data := l_msg_summary;
2016 END IF;
2017 --
2018 IF l_debug_on THEN
2019 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2020 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_SPOSTALCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2021 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_SPOSTALCODE');
2022 END IF;
2023 --
2024 WHEN WSH_U_CPOSTALCODE THEN
2025 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CPOSTALCODE');
2026 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2027 x_return_status := FND_API.G_RET_STS_ERROR;
2028 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2029 IF x_msg_count > 1 THEN
2030 x_msg_data := l_msg_summary || l_msg_details;
2031 ELSE
2032 x_msg_data := l_msg_summary;
2033 END IF;
2034 --
2035 IF l_debug_on THEN
2036 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2037 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CPOSTALCODE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2038 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CPOSTALCODE');
2039 END IF;
2040 --
2041 WHEN WSH_U_CCOUNTRY THEN
2042 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_CCOUNTRY');
2043 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2044 x_return_status := FND_API.G_RET_STS_ERROR;
2045 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2046 IF x_msg_count > 1 THEN
2047 x_msg_data := l_msg_summary || l_msg_details;
2048 ELSE
2049 x_msg_data := l_msg_summary;
2050 END IF;
2051 --
2052 IF l_debug_on THEN
2053 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2054 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_CCOUNTRY exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2055 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_CCOUNTRY');
2056 END IF;
2057
2058 WHEN WSH_U_RATECHART THEN
2059 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_RATECHART');
2060 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2061 x_return_status := FND_API.G_RET_STS_ERROR;
2062 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2063 if x_msg_count > 1 then
2064 x_msg_data := l_msg_summary || l_msg_details;
2065 else
2066 x_msg_data := l_msg_summary;
2067 end if;
2068 --
2069 IF l_debug_on THEN
2070 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2071 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_RATECHART exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2072 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_RATECHART');
2073 END IF;
2074 WHEN WSH_U_HOST_FAILED THEN
2075 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
2076 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2077 x_return_status := FND_API.G_RET_STS_ERROR;
2078 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2079 IF x_msg_count > 1 then
2080 x_msg_data := l_msg_summary || l_msg_details;
2081 ELSE
2082 x_msg_data := l_msg_summary;
2083 END IF;
2084 -- To remove the prefix word "Error:" from the original error message
2085 x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
2086
2087 --
2088 IF l_debug_on THEN
2089 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2090 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2091 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
2092 END IF;
2093
2094 WHEN OTHERS THEN
2095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2096 IF sqlcode ='-12543' THEN
2097 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_TNS');
2098 FND_MESSAGE.SET_TOKEN('MSG_TEXT','TNS:destination host unreachable');
2099 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2103 x_msg_data := l_msg_summary || l_msg_details;
2100 x_return_status := FND_API.G_RET_STS_ERROR;
2101 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2102 IF x_msg_count > 1 THEN
2104 ELSE
2105 x_msg_data := l_msg_summary;
2106 END IF;
2107 IF l_debug_on THEN
2108 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2109 END IF;
2110 RETURN;
2111 ELSIF sqlcode ='-29106'
2112 THEN
2113 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_WALLET_PASSWD');
2114 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Please check oracle wallet password');
2115 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2116 x_return_status := FND_API.G_RET_STS_ERROR;
2117 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2118 IF x_msg_count > 1 THEN
2119 x_msg_data := l_msg_summary || l_msg_details;
2120 ELSE
2121 x_msg_data := l_msg_summary;
2122 END IF;
2123 IF l_debug_on THEN
2124 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2125 END IF;
2126 RETURN;
2127 ELSIF sqlcode ='-28759'
2128 THEN
2129 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_WALLET_PATH');
2130 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Please check oracle wallet path or its privileges');
2131 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2132 x_return_status := FND_API.G_RET_STS_ERROR;
2133 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2134 IF x_msg_count > 1 THEN
2135 x_msg_data := l_msg_summary || l_msg_details;
2136 ELSE
2137 x_msg_data := l_msg_summary;
2138 END IF;
2139 IF l_debug_on THEN
2140 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2141 END IF;
2142 RETURN;
2143
2144
2145 END IF;
2146 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2147 THEN
2148 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'rate_shipment_services');
2149 END IF;
2150 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2151 --
2152 IF l_debug_on THEN
2153 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2154 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2155 END IF;
2156 END rate_shipment_services;
2157 --Set the below global variables
2158 --G_UPS_WS_USERNAME
2159 --G_UPS_WS_PASSWD
2160 --G_UPS_ACCESS_TOKEN
2161 --G_ORA_WALLET_PATH
2162 --G_ORA_WALLET_PASSSWD
2163 PROCEDURE trk_shipment_services
2164 ( p_request_in IN WSH_U_TRACK.EnhancedTrackInRec
2165 , x_track_address OUT NOCOPY WSH_U_TRACK.TrackAddressTblTyp
2166 , x_pkg_detail_segment OUT NOCOPY WSH_U_TRACK.PkgDtlSegTblTyp
2167 , x_activity_detail OUT NOCOPY WSH_U_TRACK.ActivityDetailTblTyp
2168 , x_msg_count OUT NOCOPY NUMBER
2169 , x_msg_data OUT NOCOPY VARCHAR2
2170 , x_return_status OUT NOCOPY VARCHAR2 )
2171 IS
2172 doc DBMS_XMLDOM.DOMDocument;
2173 clobdoc CLOB;
2174 l_resp_txt CLOB;
2175 xdata XMLTYPE;
2176 l_xml XMLTYPE;
2177 l_http_req utl_http.req;
2178 l_http_resp utl_http.resp;
2179 v_read_line VARCHAR2(32767);
2180 l_my_scheme VARCHAR2(256);
2181 l_my_realm VARCHAR2(256);
2182 l_my_proxy BOOLEAN;
2183 l_debug_on BOOLEAN;
2184
2185 l_length NUMBER;
2186 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2187 l_msg_count NUMBER := 0;
2188 l_msg_data VARCHAR2(2000) := NULL;
2189 l_internet_proxy VARCHAR2(1000) := NULL;
2190 l_carrier_api_url VARCHAR2(500) := NULL;
2191
2192
2193 l_msg_summary VARCHAR2(2000) := NULL;
2194 l_msg_details VARCHAR2(4000) := NULL;
2195 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'trk_shipment_services';
2196
2197
2198 l_index PLS_INTEGER;
2199 l_act_index PLS_INTEGER;
2200 l_xml_index PLS_INTEGER;
2201 l_csp_request_in WSH_U_CSPV.CSPValidateInRec;
2202 l_rate_request_in WSH_U_RASS.RateServiceInRec;
2203 wsh_u_proxy EXCEPTION;
2204 wsh_u_host_failed EXCEPTION;
2205 wsh_u_no_trk EXCEPTION;
2206 l_namespace VARCHAR2(1000);
2207 l_errmsg_txt VARCHAR2(1000);
2208 CURSOR trk_xml_cur IS
2209 SELECT xmlelement("env:Envelope",XMLAttributes('http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:env",
2210 'http://www.ups.com/XMLSchema/XOLTWS/UPSS/v1.0' AS " xmlns:ns1",
2211 'http://www.ups.com/XMLSchema/XOLTWS/Track/v2.0' AS " xmlns:ns2",
2212 'http://www.ups.com/XMLSchema/XOLTWS/Common/v1.0' AS " xmlns:ns3")
2213 ,xmlelement("env:Header",xmlelement("ns1:UPSSecurity",
2214 xmlelement("ns1:UsernameToken",
2215 xmlelement("ns1:Username",G_UPS_WS_USERNAME),
2216 xmlelement("ns1:Password",G_UPS_WS_PASSWD)
2217 ),
2218 xmlelement("ns1:ServiceAccessToken",
2219 xmlelement("ns1:AccessLicenseNumber",G_UPS_ACCESS_TOKEN)
2220 )
2221 )
2222 )
2223 ,xmlelement("env:Body",xmlelement("ns2:TrackRequest"
2224 ,xmlelement("ns3:Request",xmlelement("ns3:RequestOption",'1'))
2225 ,xmlelement("ns2:InquiryNumber",wdd.TRACKING_NUMBER
2226 )
2227 )
2228 )
2229 )
2230 FROM wsh_delivery_details wdd
2231 WHERE wdd.tracking_number= p_request_in.InquiryNumber;
2232
2233
2234 BEGIN
2235
2236 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2237 -- Commenting the below UPS WS Integeration code
2238 --If Customer want to use UPS WS Integ, they need to uncomment the below code
2239 /*
2240 IF l_debug_on IS NULL
2241 THEN
2242 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2243 END IF;
2244 IF l_debug_on
2245 THEN
2246 WSH_DEBUG_SV.push(l_module_name);
2247 END IF;
2248 x_msg_count := 0;
2249 x_msg_data := NULL;
2250
2251 IF l_debug_on THEN
2252 WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
2253 WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
2254 END IF;
2255
2256 dbms_lob.createTemporary(clobdoc,TRUE);
2257
2258 OPEN trk_xml_cur;
2259 FETCH trk_xml_cur INTO xdata;
2260 IF trk_xml_cur%NOTFOUND
2261 THEN
2262 IF l_debug_on THEN
2263 WSH_DEBUG_SV.log(l_module_name,'No Rows Fetched for the given Delivery Detail Id');
2264 END IF;
2265 RAISE wsh_u_no_trk;
2266 --x_return_status := FND_API.G_RET_STS_ERROR;
2267 --RETURN;
2268 END IF;
2269 CLOSE trk_xml_cur;
2270
2271 doc := DBMS_XMLDOM.NewDOMDocument(xdata);
2272
2273 --dbms_xmldom.writetofile(doc, '/usr/tmp/TimeIntransit.xml');
2274
2275 dbms_xmldom.writeToClob(doc,clobdoc) ;
2276
2277 IF l_debug_on THEN
2278 WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
2279 WSH_DEBUG_SV.log(l_module_name,clobdoc);
2280 END IF;
2281
2282 l_carrier_api_url := 'https://onlinetools.ups.com/webservices/Track'; --UPS Prod URL
2283
2284 IF l_debug_on THEN
2285 WSH_DEBUG_SV.log(l_module_name,'**************Now sending request to UPS Url '||l_carrier_api_url);
2286 END IF;
2287
2288 l_internet_proxy := wsh_u_util.get_proxy
2289 ( p_api_version => 1.0
2290 , p_init_msg_list => FND_API.G_TRUE
2291 , x_return_status => l_return_status
2292 , x_msg_count => l_msg_count
2293 , x_msg_data => l_msg_data);
2294
2295 IF l_debug_on
2296 THEN
2297 WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
2298 WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
2299 END IF;
2300
2301 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2302 THEN
2303 RAISE wsh_u_proxy;
2304 END IF;
2305
2306 utl_http.set_wallet(
2307 'file:'||G_ORA_WALLET_PATH
2308 , G_ORA_WALLET_PASSSWD);
2309
2310 utl_http.set_detailed_excp_support ( enable => true );
2311 utl_http.set_response_error_check(enable =>true);
2312
2313 --Only if there is l_internet_proxy then we need to set it up.
2314 IF l_internet_proxy IS NOT NULL
2315 THEN
2316 utl_http.set_proxy
2317 ( l_internet_proxy
2318 , NULL);
2319 END IF;
2320
2321 l_http_req := utl_http.begin_request
2322 ( l_carrier_api_url
2323 , 'POST'
2324 ,'HTTP/1.1');
2325
2326 utl_http.set_header
2327 ( l_http_req
2328 , 'Content-Type'
2329 , 'text/xml') ;
2330
2331 utl_http.set_header(l_http_req, 'Content-Length', length(clobdoc)) ;
2332 utl_http.set_header(l_http_req, 'SOAPAction', 'initiate');
2333 utl_http.write_text(l_http_req, clobdoc) ;
2334 l_http_resp := utl_http.get_response (l_http_req);
2335
2336 IF l_debug_on
2337 THEN
2338 WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
2339 WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
2340 WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
2341 END IF;
2342
2343 BEGIN
2344 LOOP
2345 utl_http.read_line(
2346 l_http_resp
2347 ,v_read_line
2348 ,TRUE);
2349
2350 l_resp_txt := l_resp_txt || v_read_line;
2351
2352 END LOOP;
2353 EXCEPTION
2354 WHEN OTHERS THEN
2355 IF l_debug_on THEN
2356 WSH_DEBUG_SV.log(l_module_name,'Catch while handling response '||sqlerrm);
2357 END IF;
2358 END;
2359
2360 IF l_debug_on THEN
2361 WSH_DEBUG_SV.log(l_module_name,'Debug l_resp_txt is '||l_resp_txt);
2362 END IF;
2363
2364 -- Look for client-side error and report it.
2365 IF l_http_resp.status_code >= 400 AND
2366 l_http_resp.status_code <= 499
2367 THEN
2368 IF l_http_resp.status_code = utl_http.http_unauthorized
2369 THEN
2370 utl_http.get_authentication(
2371 l_http_resp
2372 ,l_my_scheme
2373 ,l_my_realm
2374 ,l_my_proxy);
2375
2376 IF l_my_proxy
2377 THEN
2378 IF l_debug_on THEN
2379 WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
2380 WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
2381 ||l_my_scheme
2382 ||' authentication username/password for realm '
2383 ||l_my_realm
2384 ||' for the proxy server.');
2385 END IF;
2386 ELSE
2387 IF l_debug_on THEN
2388 WSH_DEBUG_SV.log(l_module_name,'Web page '
2389 ||l_carrier_api_url
2390 ||' is protected.');
2391 WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
2392 ||l_my_scheme
2393 ||' authentication username/password for realm '
2394 ||l_my_realm
2395 ||' for the Web page.');
2396 END IF;
2397 END IF;
2398 ELSE
2399 IF l_debug_on THEN
2400 WSH_DEBUG_SV.log(l_module_name,'Please Check the URL.');
2401 END IF;
2402 END IF;
2403
2404 utl_http.end_response(l_http_resp);
2405 RETURN;
2406 -- Look for server-side error and report it.
2407 ELSIF l_http_resp.status_code >= 500 AND
2408 l_http_resp.status_code <= 599
2409 THEN
2410 IF l_debug_on THEN
2411 WSH_DEBUG_SV.log(l_module_name,'Check if the URL is up.');
2412 END IF;
2413
2414 utl_http.end_response(l_http_resp);
2415 RETURN;
2416 END IF;
2417
2418 utl_http.end_response (l_http_resp);
2419
2420 IF l_debug_on THEN
2421 WSH_DEBUG_SV.log(l_module_name,'Parsing Response l_resp_txt');
2422 END IF;
2423
2424 l_xml := xmltype(l_resp_txt);
2425
2426 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''';
2427
2428 l_namespace := 'xmlns:trk="http://www.ups.com/XMLSchema/XOLTWS/Track/v2.0"';
2429 l_index := 1;
2430 WSH_DEBUG_SV.log(l_module_name,'AD 1');
2431 IF l_xml.Existsnode('//trk:TrackResponse/'||'trk:Shipment[' || To_Char(l_index) || ']',l_namespace) > 0
2432 THEN
2433 WHILE l_xml.Existsnode('//trk:TrackResponse/'||'trk:Shipment[' || To_Char(l_index) || ']',l_namespace) > 0
2434 LOOP
2435 BEGIN
2436 x_pkg_detail_segment(l_index).ServiceLevelDescription := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Service/trk:Description/text()',l_namespace).getStringVal();
2437 EXCEPTION
2438 WHEN OTHERS THEN
2439 IF l_debug_on THEN
2440 WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').ServiceLevelDescription :'||sqlerrm);
2441 END IF;
2442 END;
2443 x_pkg_detail_segment(l_index).ConsigneeAddressIndex := l_index;
2444 BEGIN
2445 x_pkg_detail_segment(l_index).SignedForByName := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:SignedForByName/text()',l_namespace).getStringVal();
2446 EXCEPTION
2447 WHEN OTHERS THEN
2448 IF l_debug_on THEN
2449 WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').SignedForByName :'||sqlerrm);
2450 END IF;
2451 END;
2452 BEGIN
2453 x_pkg_detail_segment(l_index).PickupDate := to_date(l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:PickupDate/text()',l_namespace).getStringVal(),'YYYYMMDD');
2454 EXCEPTION
2455 WHEN OTHERS THEN
2456 IF l_debug_on THEN
2457 WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').PickupDate :'||sqlerrm);
2458 END IF;
2459 END;
2460 BEGIN
2464 IF l_debug_on THEN
2461 x_pkg_detail_segment(l_index).CusotmerReferenceNumber := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:ReferenceNumber/trk:Value/text()',l_namespace).getStringVal();
2462 EXCEPTION
2463 WHEN OTHERS THEN
2465 WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').CusotmerReferenceNumber :'||sqlerrm);
2466 END IF;
2467 END;
2468 BEGIN
2469 x_pkg_detail_segment(l_index).PackageWeight := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:PackageWeight/trk:Weight/text()',l_namespace).getStringVal();
2470 EXCEPTION
2471 WHEN OTHERS THEN
2472 IF l_debug_on THEN
2473 WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').PackageWeight :'||sqlerrm);
2474 END IF;
2475 END;
2476 BEGIN
2477 x_pkg_detail_segment(l_index).weightuom := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:PackageWeight/trk:UnitOfMeasurement/trk:Code/text()',l_namespace).getStringVal();
2478 EXCEPTION
2479 WHEN OTHERS THEN
2480 IF l_debug_on THEN
2481 WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').weightuom :'||sqlerrm);
2482 END IF;
2483 END;
2484 BEGIN
2485 x_pkg_detail_segment(l_index).location := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:Activity/trk:ActivityLocation/trk:Description/text()',l_namespace).getStringVal();
2486 EXCEPTION
2487 WHEN OTHERS THEN
2488 IF l_debug_on THEN
2489 WSH_DEBUG_SV.log(l_module_name,'x_pkg_detail_segment('||l_index||').location :'||sqlerrm);
2490 END IF;
2491 END;
2492 l_index := l_index +1;
2493 END LOOP;
2494 l_act_index := 1;
2495 l_xml_index := 1;
2496
2497 WHILE l_xml.Existsnode('//trk:TrackResponse/trk:Shipment/trk:Package/'||'trk:Activity[' || To_Char(l_xml_index) || ']',l_namespace) > 0
2498 LOOP
2499 BEGIN
2500 x_activity_detail(l_act_index).ActivityAddressIndex := l_act_index;
2501 x_activity_detail(l_act_index).ActivityDate := TO_DATE(l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:Activity[' || To_Char(l_xml_index) || ']/trk:Date/text()',l_namespace).getStringVal()||' '||
2502 l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:Activity[' || To_Char(l_xml_index) || ']/trk:Time/text()',l_namespace).getStringVal() ,'YYYYMMDD HH24:MI:SS');
2503
2504 x_activity_detail(l_act_index).StatusLongDescription := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:Activity[' || To_Char(l_xml_index) || ']/trk:Status/trk:Description/text()',l_namespace).getStringVal();
2505
2506 x_activity_detail(l_act_index).StatusType := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:Activity[' || To_Char(l_xml_index) || ']/trk:Status/trk:Type/text()',l_namespace).getStringVal();
2507 x_track_address(l_act_index).City := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:Activity[' || To_Char(l_xml_index) || ']/trk:ActivityLocation/trk:Address/trk:City/text()',l_namespace).getStringVal();
2508 x_track_address(l_act_index).StateProv := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:Activity[' || To_Char(l_xml_index) || ']/trk:ActivityLocation/trk:Address/trk:StateProvinceCode/text()',l_namespace).getStringVal();
2509 x_track_address(l_act_index).Country := l_xml.extract('//trk:TrackResponse/trk:Shipment/trk:Package/trk:Activity[' || To_Char(l_xml_index) || ']/trk:ActivityLocation/trk:Address/trk:CountryCode/text()',l_namespace).getStringVal();
2510
2511 l_act_index := l_act_index +1;
2512 l_xml_index := l_xml_index +1;
2513 EXCEPTION
2514 WHEN SELF_IS_NULL THEN
2515 x_activity_detail.DELETE(l_act_index);
2516 x_track_address.DELETE(l_act_index);
2517 l_xml_index := l_xml_index +1;
2518 END;
2519 END LOOP;
2520 ELSE
2521 l_namespace := 'xmlns:err="http://www.ups.com/XMLSchema/XOLTWS/Error/v1.1"';
2522 BEGIN
2523 l_errmsg_txt := l_xml.extract('//err:Errors/err:ErrorDetail/err:PrimaryErrorCode/err:Description/text()', l_namespace).getStringVal();
2524 EXCEPTION
2525 WHEN OTHERS THEN
2526 IF l_debug_on THEN
2527 WSH_DEBUG_SV.log(l_module_name,'Exception while fetching the tracking error message :'||sqlerrm);
2528 END IF;
2529 l_errmsg_txt := 'Exception while fetching the tracking error message';
2530 END;
2531
2532 x_return_status := FND_API.G_RET_STS_ERROR;
2533 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_E');
2534 FND_MESSAGE.SET_TOKEN('MSG_TEXT',l_errmsg_txt);
2535 WSH_UTIL_CORE.Add_Message(FND_API.G_RET_STS_ERROR);
2536
2537 END IF;
2538 IF l_debug_on THEN
2539 WSH_DEBUG_SV.log(l_module_name,'Count x_activity_detail:'||x_activity_detail.count);
2540 WSH_DEBUG_SV.log(l_module_name,'Count x_pkg_detail_segment:'||x_pkg_detail_segment.count);
2541 WSH_DEBUG_SV.log(l_module_name,'Count x_track_address:'||x_track_address.count);
2542 END IF;
2543
2544 IF x_return_status = FND_API.G_RET_STS_ERROR
2545 THEN
2546 RAISE wsh_u_host_failed;
2547 END IF;
2548 x_return_status := l_return_status ;
2549 EXCEPTION
2550 WHEN WSH_U_PROXY THEN
2551 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_PROXY');
2552 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Could not find proxy settings');
2553 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2554 x_return_status := FND_API.G_RET_STS_ERROR;
2555 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2559 x_msg_data := l_msg_summary;
2556 IF x_msg_count > 1 THEN
2557 x_msg_data := l_msg_summary || l_msg_details;
2558 ELSE
2560 END IF;
2561 IF l_debug_on THEN
2562 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2563 WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
2564 END IF;
2565 WHEN WSH_U_HOST_FAILED THEN
2566 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
2567 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2568 x_return_status := FND_API.G_RET_STS_ERROR;
2569 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2570 IF x_msg_count > 1 then
2571 x_msg_data := l_msg_summary || l_msg_details;
2572 ELSE
2573 x_msg_data := l_msg_summary;
2574 END IF;
2575 -- To remove the prefix word "Error:" from the original error message
2576 x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
2577
2578 --
2579 IF l_debug_on THEN
2580 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2581 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2582 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
2583 END IF;
2584 WHEN WSH_U_NO_TRK THEN
2585 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_NO_TRK');
2586 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Invalid Tracking Number');
2587 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2588 x_return_status := FND_API.G_RET_STS_ERROR;
2589 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2590 IF x_msg_count > 1 then
2591 x_msg_data := l_msg_summary || l_msg_details;
2592 ELSE
2593 x_msg_data := l_msg_summary;
2594 END IF;
2595 -- To remove the prefix word "Error:" from the original error message
2596 x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
2597
2598 --
2599 IF l_debug_on THEN
2600 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
2601 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_NO_TRK exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2602 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_NO_TRK');
2603 END IF; --
2604 WHEN OTHERS THEN
2605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2606 IF sqlcode ='-12543' THEN
2607 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_TNS');
2608 FND_MESSAGE.SET_TOKEN('MSG_TEXT','TNS:destination host unreachable');
2609 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2610 x_return_status := FND_API.G_RET_STS_ERROR;
2611 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2612 IF x_msg_count > 1 THEN
2613 x_msg_data := l_msg_summary || l_msg_details;
2614 ELSE
2615 x_msg_data := l_msg_summary;
2616 END IF;
2617 IF l_debug_on THEN
2618 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2619 END IF;
2620 RETURN;
2621 ELSIF sqlcode ='-29106'
2622 THEN
2623 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_WALLET_PASSWD');
2624 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Please check oracle wallet password');
2625 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2626 x_return_status := FND_API.G_RET_STS_ERROR;
2627 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2628 IF x_msg_count > 1 THEN
2629 x_msg_data := l_msg_summary || l_msg_details;
2630 ELSE
2631 x_msg_data := l_msg_summary;
2632 END IF;
2633 IF l_debug_on THEN
2634 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2635 END IF;
2636 RETURN;
2637 ELSIF sqlcode ='-28759'
2638 THEN
2639 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_WALLET_PATH');
2640 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Please check oracle wallet path or its privileges');
2641 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2642 x_return_status := FND_API.G_RET_STS_ERROR;
2643 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
2644 IF x_msg_count > 1 THEN
2645 x_msg_data := l_msg_summary || l_msg_details;
2646 ELSE
2647 x_msg_data := l_msg_summary;
2648 END IF;
2649 IF l_debug_on THEN
2650 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
2651 END IF;
2652 RETURN;
2653
2654
2655 END IF;
2656
2657 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_UNEXPECTED ERROR');
2658 FND_MESSAGE.SET_TOKEN('MSG_TEXT',substr(sqlerrm,1,50));
2659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2660 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
2661 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2662 THEN
2663 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'trk_shipment_services');
2664 END IF;
2665 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
2666 --
2667 IF l_debug_on THEN
2668 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2669 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS xmsg count'||x_msg_count||' x msg data:'||x_msg_data);
2673 -- End of Comment the UPS WS Integeration code
2670 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2671 END IF;
2672 */
2674
2675
2676 END trk_shipment_services;
2677 --Set the below global variables
2678 --G_UPS_WS_USERNAME
2679 --G_UPS_WS_PASSWD
2680 --G_UPS_ACCESS_TOKEN
2681 --G_ORA_WALLET_PATH
2682 --G_ORA_WALLET_PASSSWD
2683 PROCEDURE tnt_shipment_services
2684 ( p_request_in IN WSH_U_GTT.tnt_request_rec
2685 , x_timeintransit_out OUT NOCOPY WSH_U_GTT.timeintransitoutrec
2686 , x_msg_count OUT NOCOPY NUMBER
2687 , x_msg_data OUT NOCOPY VARCHAR2
2688 , x_return_status OUT NOCOPY VARCHAR2 )
2689 IS
2690 doc DBMS_XMLDOM.DOMDocument;
2691 clobdoc CLOB;
2692 l_resp_txt CLOB;
2693 xdata XMLTYPE;
2694 l_xml XMLTYPE;
2695 l_http_req utl_http.req;
2696 l_http_resp utl_http.resp;
2697 v_read_line VARCHAR2(32767);
2698 l_my_scheme VARCHAR2(256);
2699 l_my_realm VARCHAR2(256);
2700 l_my_proxy BOOLEAN;
2701 l_debug_on BOOLEAN;
2702
2703 l_length NUMBER;
2704 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2705 l_msg_count NUMBER := 0;
2706 l_msg_data VARCHAR2(2000) := NULL;
2707 l_internet_proxy VARCHAR2(1000) := NULL;
2708 l_carrier_api_url VARCHAR2(500) := NULL;
2709 l_msg_summary VARCHAR2(2000) := NULL;
2710 l_msg_details VARCHAR2(4000) := NULL;
2711 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'tnt_shipment_services';
2712
2713
2714 l_index PLS_INTEGER;
2715 wsh_u_proxy EXCEPTION;
2716 wsh_u_host_failed EXCEPTION;
2717 l_av_soap_request VARCHAR2(32767);
2718 l_namespace VARCHAR2(1000);
2719
2720 l_errmsg_txt VARCHAR2(1000);
2721
2722
2723 CURSOR tnt_xml_cur IS
2724 SELECT xmlelement("env:Envelope",XMLAttributes('http://schemas.xmlsoap.org/soap/envelope/' AS "xmlns:env"
2725 ,'http://www.ups.com/XMLSchema/XOLTWS/UPSS/v1.0' AS " xmlns:ns1"
2726 ,'http://www.ups.com/XMLSchema/XOLTWS/tnt/v1.0' AS " xmlns:ns2"
2727 ,'http://www.ups.com/XMLSchema/XOLTWS/Common/v1.0' AS " xmlns:ns3")
2728 ,xmlelement("env:Header",xmlelement("ns1:UPSSecurity",
2729 xmlelement("ns1:UsernameToken",
2730 xmlelement("ns1:Username",G_UPS_WS_USERNAME),
2731 xmlelement("ns1:Password",G_UPS_WS_PASSWD)
2732 ),
2733 xmlelement("ns1:ServiceAccessToken",
2734 xmlelement("ns1:AccessLicenseNumber",G_UPS_ACCESS_TOKEN)
2735 )
2736 )
2737 )
2738 ,xmlelement("env:Body",xmlelement("ns2:TimeInTransitRequest",
2739 xmlelement("ns3:Request",NULL),
2740 xmlelement("ns2:ShipFrom",
2741 xmlelement("ns2:Address",
2742 xmlelement("ns2:City",wl.city),
2743 xmlelement("ns2:CountryCode",wl.country),
2744 xmlelement("ns2:PostalCode",wl.postal_code)
2745 )
2746 ),
2747 xmlelement("ns2:ShipTo",
2748 xmlelement("ns2:Address",
2749 xmlelement("ns2:City",w2.city),
2750 xmlelement("ns2:CountryCode",wl.country),
2751 xmlelement("ns2:PostalCode",w2.postal_code)
2752 )
2753 ),
2754 xmlelement("ns2:Pickup",
2755 --xmlelement("ns2:Date",TO_CHAR((CASE WHEN NVL(wdd.earliest_pickup_date,SYSDATE)< SYSDATE THEN SYSDATE ELSE NVL(wdd.earliest_pickup_date,SYSDATE) END),'YYYYMMDD'))
2756 xmlelement("ns2:Date",TO_CHAR(SYSDATE,'YYYYMMDD'))
2757 )
2758 )
2759 )
2760 )
2761 FROM wsh_locations wl
2762 , wsh_locations w2
2763 WHERE wl.source_location_id = p_request_in.ship_from_location_id
2764 AND w2.source_location_id = p_request_in.ship_to_location_id;
2765
2766 BEGIN
2767 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2771 IF l_debug_on IS NULL
2768 -- Commenting the below UPS WS Integeration code
2769 --If Customer want to use UPS WS Integ, they need to uncomment the below code
2770 /*
2772 THEN
2773 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2774 END IF;
2775 IF l_debug_on THEN
2776 WSH_DEBUG_SV.push(l_module_name);
2777 END IF;
2778 x_msg_count := 0;
2779 x_msg_data := NULL;
2780 -- Added by SHTADEPA
2781 IF l_debug_on THEN
2782 WSH_DEBUG_SV.log(l_module_name,'Entity Called is '||p_request_in.Entity);
2783 WSH_DEBUG_SV.log(l_module_name,'EntityId is '||p_request_in.Entity_Id);
2784 END IF;
2785
2786 dbms_lob.createTemporary(clobdoc,TRUE);
2787
2788 OPEN tnt_xml_cur;
2789 FETCH tnt_xml_cur INTO xdata;
2790 IF tnt_xml_cur%NOTFOUND
2791 THEN
2792 IF l_debug_on THEN
2793 WSH_DEBUG_SV.log(l_module_name,'No Rows Fetched for the given Delivery Detail Id');
2794 END IF;
2795 x_return_status := FND_API.G_RET_STS_ERROR;
2796 RETURN;
2797 END IF;
2798 CLOSE tnt_xml_cur;
2799 l_carrier_api_url := 'https://onlinetools.ups.com/webservices/TimeInTransit'; --UPS Prod URL
2800
2801 doc := DBMS_XMLDOM.NewDOMDocument(xdata);
2802
2803 --dbms_xmldom.writetofile(doc, '/usr/tmp/SriTimeIntransit.xml');
2804
2805 dbms_xmldom.writeToClob(doc,clobdoc) ;
2806
2807 IF l_debug_on THEN
2808 WSH_DEBUG_SV.log(l_module_name,'Created xml file and the content is ');
2809 WSH_DEBUG_SV.log(l_module_name,clobdoc);
2810 END IF;
2811
2812
2813 IF l_debug_on THEN
2814 WSH_DEBUG_SV.log(l_module_name,'**************Now sending request to UPS Url '||l_carrier_api_url);
2815 END IF;
2816
2817 l_internet_proxy := wsh_u_util.get_proxy
2818 ( p_api_version => 1.0
2819 , p_init_msg_list => FND_API.G_TRUE
2820 , x_return_status => l_return_status
2821 , x_msg_count => l_msg_count
2822 , x_msg_data => l_msg_data);
2823
2824 IF l_debug_on
2825 THEN
2826 WSH_DEBUG_SV.log(l_module_name,'l_internet_proxy '||l_internet_proxy);
2827 WSH_DEBUG_SV.log(l_module_name,'l_return_status '||l_return_status);
2828 END IF;
2829
2830 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2831 THEN
2832 RAISE WSH_U_PROXY;
2833 END IF;
2834
2835 utl_http.set_wallet(
2836 'file:'||G_ORA_WALLET_PATH
2837 , G_ORA_WALLET_PASSSWD);
2838
2839 utl_http.set_detailed_excp_support ( enable => true );
2840 utl_http.set_response_error_check(enable =>true);
2841
2842 --Only if there is l_internet_proxy then we need to set it up.
2843 IF l_internet_proxy IS NOT NULL
2844 THEN
2845 utl_http.set_proxy ( l_internet_proxy , NULL);
2846 END IF;
2847
2848 l_http_req := utl_http.begin_request ( l_carrier_api_url, 'POST','HTTP/1.1');
2849 utl_http.set_header ( l_http_req, 'Content-Type', 'text/xml') ;
2850 utl_http.set_header(l_http_req, 'Content-Length', length(clobdoc)) ;
2851 utl_http.set_header(l_http_req, 'SOAPAction', 'initiate');
2852 utl_http.write_text(l_http_req, clobdoc) ;
2853
2854 l_http_resp := utl_http.get_response (l_http_req);
2855
2856 IF l_debug_on
2857 THEN
2858 WSH_DEBUG_SV.log(l_module_name,'Status Code of response is '||l_http_resp.status_code);
2859 WSH_DEBUG_SV.log(l_module_name,'reason phrase of response is '||l_http_resp.reason_phrase);
2860 WSH_DEBUG_SV.log(l_module_name,'http_version is '||l_http_req.http_version);
2861 END IF;
2862
2863 BEGIN
2864 LOOP
2865 utl_http.read_line(
2866 l_http_resp
2867 ,v_read_line
2868 ,TRUE);
2869
2870 l_resp_txt := l_resp_txt || v_read_line;
2871
2872 END LOOP;
2873 EXCEPTION
2874 WHEN OTHERS THEN
2875 IF l_debug_on THEN
2876 WSH_DEBUG_SV.log(l_module_name,'Catch while handling response '||sqlerrm);
2877 END IF;
2878 END;
2879
2880 IF l_debug_on THEN
2881 WSH_DEBUG_SV.log(l_module_name,'Debug l_resp_txt is '||l_resp_txt);
2882 END IF;
2883
2884 -- Look for client-side error and report it.
2885 IF l_http_resp.status_code >= 400 AND
2886 l_http_resp.status_code <= 499
2887 THEN
2888 IF l_http_resp.status_code = utl_http.http_unauthorized
2889 THEN
2890 utl_http.get_authentication(
2891 l_http_resp
2892 ,l_my_scheme
2893 ,l_my_realm
2894 ,l_my_proxy);
2895
2896 IF l_my_proxy
2897 THEN
2898 IF l_debug_on THEN
2899 WSH_DEBUG_SV.log(l_module_name,'Web proxy server is protected.');
2900 WSH_DEBUG_SV.log(l_module_name,'Please supply the required '
2901 ||l_my_scheme
2902 ||' authentication username/password for realm '
2903 ||l_my_realm
2904 ||' for the proxy server.');
2905 END IF;
2906 ELSE
2907 IF l_debug_on THEN
2908 WSH_DEBUG_SV.log(l_module_name,'Web page '
2909 ||l_carrier_api_url
2910 ||' is protected.');
2911 WSH_DEBUG_SV.log(l_module_name,'Please supplied the required '
2912 ||l_my_scheme
2913 ||' authentication username/password for realm '
2914 ||l_my_realm
2915 ||' for the Web page.');
2916 END IF;
2917 END IF;
2918 ELSE
2919 IF l_debug_on THEN
2920 WSH_DEBUG_SV.log(l_module_name,'Please Check the URL.');
2921 END IF;
2922 END IF;
2923
2924 utl_http.end_response(l_http_resp);
2925 RETURN;
2926 -- Look for server-side error and report it.
2927 ELSIF l_http_resp.status_code >= 500 AND
2928 l_http_resp.status_code <= 599
2929 THEN
2930 IF l_debug_on THEN
2931 WSH_DEBUG_SV.log(l_module_name,'Check if the URL is up.');
2932 END IF;
2933
2934 utl_http.end_response(l_http_resp);
2935 RETURN;
2936 END IF;
2937
2938 utl_http.end_response (l_http_resp);
2939
2940 IF l_debug_on THEN
2941 WSH_DEBUG_SV.log(l_module_name,'Parsing Response l_resp_txt');
2942 END IF;
2943
2944 l_xml := xmltype(l_resp_txt);
2945 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''.,''';
2946
2947 l_namespace := 'xmlns:tnt="http://www.ups.com/XMLSchema/XOLTWS/tnt/v1.0"';
2948 l_index := 1;
2949 IF l_xml.Existsnode('//tnt:TransitResponse/'||'tnt:ServiceSummary[' || To_Char(l_index) || ']',l_namespace) > 0
2950 THEN
2951 WHILE l_xml.Existsnode('//tnt:TransitResponse/'||'tnt:ServiceSummary[' || To_Char(l_index) || ']',l_namespace) > 0
2952 LOOP
2953 BEGIN
2954 x_TimeInTransit_out.TransitTime := l_xml.extract('//tnt:TransitResponse/tnt:ServiceSummary[' || To_Char(l_index) || ']/tnt:EstimatedArrival/tnt:BusinessDaysInTransit/text()',l_namespace).getStringVal()||' ';
2955 EXCEPTION
2956 WHEN OTHERS THEN
2957 IF l_debug_on THEN
2958 WSH_DEBUG_SV.log(l_module_name,'x_TimeInTransit_out.TransitTime:'||sqlerrm);
2959 END IF;
2960 END;
2961 BEGIN
2962 x_TimeInTransit_out.OriginCity := l_xml.extract('//tnt:TransitResponse/tnt:ShipFrom/tnt:Address/tnt:City/text()',l_namespace).getStringVal();
2963 EXCEPTION
2964 WHEN OTHERS THEN
2965 IF l_debug_on THEN
2966 WSH_DEBUG_SV.log(l_module_name,'x_TimeInTransit_out.OriginCity:'||sqlerrm);
2967 END IF;
2968 END;
2969 BEGIN
2970 x_TimeInTransit_out.OriginStateProv := l_xml.extract('//tnt:TransitResponse/tnt:ShipFrom/tnt:Address/tnt:StateProvinceCode/text()',l_namespace).getStringVal();
2971 EXCEPTION
2972 WHEN OTHERS THEN
2973 IF l_debug_on THEN
2974 WSH_DEBUG_SV.log(l_module_name,'x_TimeInTransit_out.OriginStateProv:'||sqlerrm);
2975 END IF;
2976 END;
2977 BEGIN
2981 IF l_debug_on THEN
2978 x_TimeInTransit_out.DestinationStateProv := l_xml.extract('//tnt:TransitResponse/tnt:ShipTo/tnt:Address/tnt:StateProvinceCode/text()',l_namespace).getStringVal();
2979 EXCEPTION
2980 WHEN OTHERS THEN
2982 WSH_DEBUG_SV.log(l_module_name,'x_TimeInTransit_out.DestinationStateProv:'||sqlerrm);
2983 END IF;
2984 END;
2985 BEGIN
2986 x_TimeInTransit_out.DestinationCity := l_xml.extract('//tnt:TransitResponse/tnt:ShipTo/tnt:Address/tnt:City/text()',l_namespace).getStringVal();
2987 EXCEPTION
2988 WHEN OTHERS THEN
2989 IF l_debug_on THEN
2990 WSH_DEBUG_SV.log(l_module_name,'x_TimeInTransit_out.DestinationCity:'||sqlerrm);
2991 END IF;
2992 END;
2993 l_index := l_index + 1;
2994
2995 EXIT;
2996 END LOOP;
2997 ELSE
2998 -- handling error messages
2999 l_namespace := 'xmlns:err="http://www.ups.com/XMLSchema/XOLTWS/Error/v1.1"';
3000 BEGIN
3001 l_errmsg_txt := l_xml.extract('//err:Errors/err:ErrorDetail/err:PrimaryErrorCode/err:Description/text()', l_namespace).getStringVal();
3002 EXCEPTION
3003 WHEN OTHERS THEN
3004 IF l_debug_on THEN
3005 WSH_DEBUG_SV.log(l_module_name,'Exception while fetching the time in Transit error message :'||sqlerrm);
3006 END IF;
3007 l_errmsg_txt := 'Exception while fetching the Time In Transit error message';
3008 END;
3009 x_return_status := FND_API.G_RET_STS_ERROR;
3010 FND_MESSAGE.SET_NAME('WSH','WSH_UTIL_MESSAGE_E');
3011 FND_MESSAGE.SET_TOKEN('MSG_TEXT',l_errmsg_txt);
3012 WSH_UTIL_CORE.Add_Message(FND_API.G_RET_STS_ERROR);
3013
3014 END IF;
3015
3016 IF l_debug_on THEN
3017 WSH_DEBUG_SV.log(l_module_name,'TransitTime from UPS is '||x_TimeInTransit_out.TransitTime);
3018 WSH_DEBUG_SV.log(l_module_name,'Origin State Province returned from UPS is '||x_TimeInTransit_out.OriginStateProv);
3019 WSH_DEBUG_SV.log(l_module_name,'Destination State Prov returned from UPS is '||x_TimeInTransit_out.DestinationStateProv);
3020 WSH_DEBUG_SV.log(l_module_name,'OriginCity returned from UPS is '||x_TimeInTransit_out.OriginCity);
3021 WSH_DEBUG_SV.log(l_module_name,'Destination City returned from UPS is '||x_TimeInTransit_out.DestinationCity);
3022 END IF;
3023
3024 IF x_return_status = FND_API.G_RET_STS_ERROR
3025 THEN
3026 RAISE wsh_u_host_failed;
3027 END IF;
3028 x_return_status := l_return_status ;
3029 */ -- End of Comment the UPS WS Integeration code
3030
3031 EXCEPTION
3032 WHEN WSH_U_PROXY THEN
3033 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_PROXY');
3034 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Could not find proxy settings');
3035 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
3036 x_return_status := FND_API.G_RET_STS_ERROR;
3037 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
3038 IF x_msg_count > 1 THEN
3039 x_msg_data := l_msg_summary || l_msg_details;
3040 ELSE
3041 x_msg_data := l_msg_summary;
3042 END IF;
3043 IF l_debug_on THEN
3044 WSH_DEBUG_SV.logmsg(l_module_name,'wsh_u_proxy exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3045 WSH_DEBUG_SV.pop(l_module_name,'exception:wsh_u_proxy');
3046 END IF;
3047 WHEN WSH_U_HOST_FAILED THEN
3048 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_HOST_FAILED');
3049 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
3050 x_return_status := FND_API.G_RET_STS_ERROR;
3051 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
3052 IF x_msg_count > 1 then
3053 x_msg_data := l_msg_summary || l_msg_details;
3054 ELSE
3055 x_msg_data := l_msg_summary;
3056 END IF;
3057 -- To remove the prefix word "Error:" from the original error message
3058 x_msg_data := SUBSTR(x_msg_data,INSTR(x_msg_data,':') + 2);
3059
3060 --
3061 IF l_debug_on THEN
3062 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',SUBSTR(x_msg_data,1,200));
3063 WSH_DEBUG_SV.logmsg(l_module_name,'WSH_U_HOST_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3064 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:WSH_U_HOST_FAILED');
3065 END IF;
3066 WHEN OTHERS THEN
3067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3068 IF sqlcode ='-12543' THEN
3069 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_TNS');
3070 FND_MESSAGE.SET_TOKEN('MSG_TEXT','TNS:destination host unreachable');
3071 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
3072 x_return_status := FND_API.G_RET_STS_ERROR;
3073 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
3074 IF x_msg_count > 1 THEN
3075 x_msg_data := l_msg_summary || l_msg_details;
3076 ELSE
3077 x_msg_data := l_msg_summary;
3078 END IF;
3079 IF l_debug_on THEN
3080 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
3081 END IF;
3082 RETURN;
3083 ELSIF sqlcode ='-29106'
3084 THEN
3085 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_WALLET_PASSWD');
3086 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Please check oracle wallet password');
3087 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
3088 x_return_status := FND_API.G_RET_STS_ERROR;
3089 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
3090 IF x_msg_count > 1 THEN
3091 x_msg_data := l_msg_summary || l_msg_details;
3092 ELSE
3093 x_msg_data := l_msg_summary;
3094 END IF;
3095 IF l_debug_on THEN
3096 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
3097 END IF;
3098 RETURN;
3099 ELSIF sqlcode ='-28759'
3100 THEN
3101 FND_MESSAGE.SET_NAME('WSH', 'WSH_U_WALLET_PATH');
3102 FND_MESSAGE.SET_TOKEN('MSG_TEXT','Please check oracle wallet path or its privileges');
3103 WSH_UTIL_CORE.ADD_MESSAGE(FND_API.G_RET_STS_ERROR,l_module_name);
3104 x_return_status := FND_API.G_RET_STS_ERROR;
3105 WSH_UTIL_CORE.get_messages( 'Y', l_msg_summary, l_msg_details, x_msg_count);
3106 IF x_msg_count > 1 THEN
3107 x_msg_data := l_msg_summary || l_msg_details;
3108 ELSE
3109 x_msg_data := l_msg_summary;
3110 END IF;
3111 IF l_debug_on THEN
3112 WSH_DEBUG_SV.logmsg(l_module_name,'error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_EXCEP_LEVEL);
3113 END IF;
3114 RETURN;
3115 END IF;
3116 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3117 THEN
3118 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'tnt_shipment_services');
3119 END IF;
3120 FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data);
3121 --
3122 IF l_debug_on THEN
3123 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3124 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3125 END IF;
3126 END tnt_shipment_services;
3127
3128 -- Carrier Services Shipping integration Project 16095594 End
3129 END WSH_CUSTOM_PUB;