[Home] [Help]
PACKAGE BODY: APPS.WSH_OTM_RIQ_XML
Source
1 PACKAGE BODY WSH_OTM_RIQ_XML as
2 /* $Header: WSHGLRXB.pls 120.5.12010000.3 2008/08/22 14:32:03 anvarshn ship $ */
3
4 --
5 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_OTM_RIQ_XML';
6 --
7 --XPATH expressions
8 --Status Code
9 G_XPATH_SM_STATUS_PREFIX CONSTANT VARCHAR2(200):='/RemoteQueryReply/OrderRoutingRuleReply';
10 G_XPATH_RIQ_STATUS_PREFIX CONSTANT VARCHAR2(200):='/RemoteQueryReply/RIQQueryReply';
11
12 G_XPATH_STATUS_CODE CONSTANT VARCHAR2(200):='/RemoteQueryStatus/RemoteQueryStatusCode/text()';
13 --Status Message
14 G_XPATH_STATUS_MESSAGE CONSTANT VARCHAR2(200):='/RemoteQueryStatus/RemoteQueryStatusMessage/text()';
15 --Log messages
16 G_XPATH_MESSAGES CONSTANT VARCHAR2(200):='/RemoteQueryStatus/TransactionReport/IntegrationLogMessage/IMessageText';
17 --Selects one option for the Choose Ship Method Action
18 G_XPATH_SM_OPTION CONSTANT VARCHAR2(200):='/RemoteQueryReply/RIQQueryReply/RIQResult';
19 --Gets the result for the Get Freight Cost action
20 G_XPATH_FREIGHT_COST_RESULT CONSTANT VARCHAR2(200):='/RemoteQueryReply/RIQQueryReply/RIQResult';
21 --Gets the result for the Get Ship Method action
22 G_XPATH_GET_SM_RESULT CONSTANT VARCHAR2(200):='/RemoteQueryReply/OrderRoutingRuleReply';
23
24 G_XPATH_SM_OPTION_PREFIX CONSTANT VARCHAR2(200):='/RIQResult';
25 G_XPATH_FREIGHT_COST_PREFIX CONSTANT VARCHAR2(200):='/RIQResult';
26 G_XPATH_GET_SM_PREFIX CONSTANT VARCHAR2(200):='/OrderRoutingRuleReply';
27
28
29 G_XPATH_CARRIER CONSTANT VARCHAR2(200):='/ServiceProviderGid/Gid/Xid/text()';
30 G_XPATH_MODE CONSTANT VARCHAR2(200):='/TransportModeGid/Gid/Xid/text()';
31 G_XPATH_SERVICE_LEVEL CONSTANT VARCHAR2(200):='/RateServiceGid/Gid/Xid/text()';
32 G_XPATH_FREIGHT_TERMS CONSTANT VARCHAR2(200):='/PaymentMethodCodeGid/Gid/Xid/text()';
33 G_XPATH_TRANSIT_TIME CONSTANT VARCHAR2(200):='/TransitTime/Duration/DurationValue/text()';
34 G_XPATH_TRANSIT_TIME_UOM CONSTANT VARCHAR2(200):='/TransitTime/Duration/DurationUOMGid/Gid/Xid/text()';
35 G_XPATH_COST_SUMMARY CONSTANT VARCHAR2(200):='/Cost/FinancialAmount/MonetaryAmount/text()';
36 G_XPATH_COST_SUMMARY_CURRENCY CONSTANT VARCHAR2(200):='/Cost/FinancialAmount/GlobalCurrencyCode/text()';
37 G_XPATH_COST_DETAILS CONSTANT VARCHAR2(200):='/CostDetails';
38 G_XPATH_COST_DETAIL_TYPE CONSTANT VARCHAR2(200):='/CostType/text()';
39
40 G_CARRIER_PREFIX CONSTANT VARCHAR2(4):='CAR-';
41 G_CARRIER_PREFIX_LENGTH CONSTANT NUMBER:=LENGTH(G_CARRIER_PREFIX);
42 G_ORG_LOCATION_PREFIX CONSTANT VARCHAR2(4):='ORG-';
43 G_CUST_LOCATION_PREFIX CONSTANT VARCHAR2(4):='CUS-';
44 G_LOCATION_SEPERATOR CONSTANT VARCHAR2(1):='-';
45 --TODO old NS map
46 G_GLOG_NS_MAP CONSTANT VARCHAR2(200):='xmlns="http://glog.com"';
47 G_OTM_NS_MAP CONSTANT VARCHAR2(200):='xmlns="http://xmlns.oracle.com/apps/otm"';
48
49 g_carrier_freight_codes WSH_NEW_DELIVERY_ACTIONS.TableVarchar30;
50 g_carrier_generic_flags WSH_NEW_DELIVERY_ACTIONS.TableVarchar3;
51
52 g_state_region_type CONSTANT NUMBER:=1;
53 g_price_cost_type_id NUMBER;
54 g_domain_name VARCHAR2(50);
55 g_servlet_uri VARCHAR2(4000) := NULL;
56 g_user_name VARCHAR2(101);
57 g_password VARCHAR2(128);
58 g_timezone_code VARCHAR2(50);
59 g_global_time_class VARCHAR2(30);
60 g_xml_namespace_map VARCHAR2(200);
61 /*Bug7329859*/
62 g_source_line_tab_temp FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_TAB;
63 /*Bug7329859*/
64
65 TYPE WSH_UOM_MAP_TAB IS TABLE OF VARCHAR2(30)
66 INDEX BY VARCHAR2(30);
67 g_EBS_to_OTM_UOM_map WSH_UOM_MAP_TAB;
68 g_OTM_to_EBS_UOM_map WSH_UOM_MAP_TAB;
69
70 TYPE WSH_SM_REC IS RECORD(
71 id NUMBER,
72 summary_rate NUMBER,
73 base_rate NUMBER,
74 charge_rate NUMBER,
75 carrier_id NUMBER,
76 mode_of_transport VARCHAR2(30),
77 service_level VARCHAR2(30),
78 freight_terms VARCHAR2(30),
79 transit_time NUMBER,
80 transit_time_UOM VARCHAR2(30),
81 ship_method_code VARCHAR2(30)
82 );
83 TYPE WSH_SM_TAB IS TABLE OF WSH_SM_REC
84 INDEX BY VARCHAR2(30);
85
86 --ECO 5516007 ,FP(5573379) to R12
87 -- Forward Declaration
88 PROCEDURE DERIVE_RIQ_DATES
89 (x_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_tab,
90 x_source_header_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_tab,
91 x_return_status OUT NOCOPY VARCHAR2);
92
93 --ECO 5516007, FP(5573379) to R12
94 --=========================================================================
95 -- Procedure: DERIVE_RIQ_DATES
96 -- Description: Derive the Ship Date and Arrival Date for the Order Lines
97 -- and Order Header, based on the Order Date Type.
98 --
99 -- Usage:
100 -- 1. When OM calls OTM to determine the rates(Rate Inquiry), this API is
101 -- called to ensure the appropriate dates are being passed to OTM.
102 -- (Calling Procedure: Call_otm_for_om)
103 --
104 -- Assumption: x_source_line_tab and x_source_header_tab have been populated
105 -- by the calling APIs.
106 -- The Input Header Tab does not necessarily correspond to a
107 -- single Order Header. Within a single order, we can have cases
108 -- like : a) 2 Order Lines with different Ship From
109 -- b) 2 Order Lines with different Ship To
110 -- c) 2 Order Lines with different Set of Scheduled Dates,
111 -- all the above are examples of cases where 1 order can
112 -- have multiple headers.
113 --
114 --=========================================================================
115 PROCEDURE DERIVE_RIQ_DATES
116 (x_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_tab,
117 x_source_header_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_tab,
118 x_return_status OUT NOCOPY VARCHAR2) AS
119
120 -- Cursor declaration Section
121
122 -- Cursor to derive the Order Header Date Type
123 CURSOR c_get_order_date_type_csr (p_header_id IN NUMBER) IS
124 SELECT NVL(order_date_type_code,'SHIP')
125 FROM oe_order_headers_all
126 WHERE header_id = p_header_id ;
127
128 -- Cursor to derive the Order Line Date Type
129 -- Ship Date should be greatest of Sysdate and Schedule Ship Date
130 -- Arrival Date should be greatest of Sysdate and Schedule Arrival Date
131 -- If Schedule Ship Date/Arrival Date are not specified, use request date
132 -- If Order Date Type is not 'ARRIVAL', then arrival date is treated as Null
133 -- Also select schedule_ship_date and schedule_arrival_date
134 --
135 CURSOR c_get_line_dates_csr (p_order_date_type IN VARCHAR2, p_line_id IN NUMBER ) IS
136 SELECT GREATEST(SYSDATE, NVL(schedule_ship_date, request_date)) ship_date,
137 DECODE(p_order_date_type,
138 'ARRIVAL',GREATEST(SYSDATE, NVL(schedule_arrival_date, request_date)),
139 NULL) arrival_date,
140 schedule_ship_date,
141 schedule_arrival_date
142 FROM oe_order_lines_all
143 WHERE line_id = p_line_id;
144
145 -- End of Cursor declaration Section
146
147 -- Variable declaration Section
148 l_line_index NUMBER;
149 l_header_index NUMBER;
150 l_order_date_type OE_ORDER_HEADERS_ALL.ORDER_DATE_TYPE_CODE%TYPE;
151 l_ship_date OE_ORDER_LINES_ALL.SCHEDULE_SHIP_DATE%TYPE;
152 l_arrival_date OE_ORDER_LINES_ALL.SCHEDULE_ARRIVAL_DATE%TYPE;
153 l_schedule_ship_date OE_ORDER_LINES_ALL.SCHEDULE_SHIP_DATE%TYPE;
154 l_schedule_arrival_date OE_ORDER_LINES_ALL.SCHEDULE_ARRIVAL_DATE%TYPE;
155
156 l_debug_on BOOLEAN;
157 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DERIVE_RIQ_DATES';
158
159 -- End of Variable declaration Section
160
161 BEGIN
162
163 -- Debug Logic
164 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
165 IF l_debug_on IS NULL THEN
166 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
167 END IF;
168
169 IF l_debug_on THEN
170 WSH_DEBUG_SV.push(l_module_name);
171 WSH_DEBUG_SV.log(l_module_name,'x_source_line_tab.COUNT',x_source_line_tab.COUNT);
172 WSH_DEBUG_SV.log(l_module_name,'x_source_header_tab.COUNT',x_source_header_tab.COUNT);
173 END IF;
174
175 -- Initialize the Procedure Specific Variables
176 l_header_index := 0;
177
178 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
179
180 IF (x_source_header_tab.COUNT > 0 AND x_source_line_tab.COUNT > 0) THEN--{
181 l_header_index := x_source_header_tab.FIRST;
182 LOOP--{
183
184 -- Initialize the variable for re-use across headers
185 l_order_date_type := 'N';
186 l_ship_date := NULL;
187 l_arrival_date := NULL;
188 l_schedule_ship_date := NULL;
189 l_schedule_arrival_date := NULL;
190 l_line_index := 0;
191
192 IF l_debug_on THEN
193 WSH_DEBUG_SV.logmsg(l_module_name,'========= New Header for Lines ======');
194 WSH_DEBUG_SV.log(l_module_name,'Initialized Order Date Type :',l_order_date_type);
195 WSH_DEBUG_SV.log(l_module_name,'Consolidation Id :',x_source_header_tab(l_header_index).consolidation_id);
196 END IF;
197
198 l_line_index := x_source_line_tab.FIRST;
199 LOOP--{
200
201 -- Find matching header and line, using consolidation_id
202 -- All the lines in a header correspond to same order_date_type,
203 -- so just determine date type for each header using 1st line
204 -- Still need to use the update the Ship/Arrival Dates for other
205 -- order lines(as of now Order Header is used to derive the dates
206 -- being passed to OTM)
207 --
208 IF x_source_line_tab(l_line_index).consolidation_id =
209 x_source_header_tab(l_header_index).consolidation_id THEN --{
210
211 IF l_debug_on THEN
212 WSH_DEBUG_SV.log(l_module_name,
213 'x_source_line_tab('||l_line_index||').source_header_id',
214 x_source_line_tab(l_line_index).source_header_id);
215 WSH_DEBUG_SV.log(l_module_name,
216 'Line Consolidation Id :',
217 x_source_line_tab(l_line_index).consolidation_id);
218 END IF;
219
220 IF l_order_date_type = 'N' THEN--{
221 -- Derive Order Header Information
222 OPEN c_get_order_date_type_csr
223 (p_header_id => x_source_line_tab(l_line_index).source_header_id);
224 FETCH c_get_order_date_type_csr INTO l_order_date_type;
225 CLOSE c_get_order_date_type_csr;
226 -- The variable is flagged as the variable l_order_date_type will be set to
227 -- SHIP or ARRIVAL, so as not to process for this set again
228
229 IF l_debug_on THEN
230 WSH_DEBUG_SV.log(l_module_name,'Actual order date type',l_order_date_type);
231 WSH_DEBUG_SV.log(l_module_name,
232 'x_source_line_tab('||l_line_index||').source_line_id',
233 x_source_line_tab(l_line_index).source_line_id);
234 WSH_DEBUG_SV.log(l_module_name,
235 'x_source_line_tab('||l_line_index||').ship_date - before changes',
236 x_source_line_tab(l_line_index).ship_date);
237 WSH_DEBUG_SV.log(l_module_name,
238 'x_source_line_tab('||l_line_index||').arrival_date - before changes',
239 x_source_line_tab(l_line_index).arrival_date);
240 END IF;
241
242 -- Derive Order Line Information
243 OPEN c_get_line_dates_csr
244 (p_order_date_type => l_order_date_type,
245 p_line_id => x_source_line_tab(l_line_index).source_line_id);
246 FETCH c_get_line_dates_csr
247 INTO l_ship_date, l_arrival_date,
248 l_schedule_ship_date, l_schedule_arrival_date;
249 CLOSE c_get_line_dates_csr;
250
251 IF l_debug_on THEN
252 WSH_DEBUG_SV.log(l_module_name,'Order Line Ship Date ',l_ship_date);
253 WSH_DEBUG_SV.log(l_module_name,'Order Line Arrival Date ',l_arrival_date);
254 WSH_DEBUG_SV.log(l_module_name,'Order Line Schedule Ship Date ',l_schedule_ship_date);
255 WSH_DEBUG_SV.log(l_module_name,'Order Line Schedule Arrival Date ',l_schedule_arrival_date);
256 END IF;
257
258 -- For Date Type of Arrival, when the line is not scheduled, pass the arrival date
259 -- and ship date should be NULL
260 IF l_order_date_type = 'ARRIVAL' AND l_schedule_arrival_date is NULL THEN
261 l_ship_date := null;
262 END IF;
263
264 -- If Ship_Date is same as Arrival_Date, mark Arrival_Date as Null
265 IF l_ship_date = l_arrival_date THEN
266 l_arrival_date := NULL;
267 END IF;
268 END IF;--} -- for 1st Order line, get the l_ship_date and l_arrival_date
269
270 -- Need to update all the Order lines, without recalculating dates above
271 -- use the same values
272 x_source_line_tab(l_line_index).ship_date := l_ship_date;
273 x_source_line_tab(l_line_index).arrival_date := l_arrival_date;
274
275 IF l_debug_on THEN
276 WSH_DEBUG_SV.log(l_module_name,
277 'x_source_line_tab('||l_line_index||').ship_date - after changes ',
278 x_source_line_tab(l_line_index).ship_date);
279 WSH_DEBUG_SV.log(l_module_name,
280 'x_source_line_tab('||l_line_index||').arrival_date - after changes ',
281 x_source_line_tab(l_line_index).arrival_date);
282 END IF;
283 END IF;--}
284
285 EXIT WHEN l_line_index >= x_source_line_tab.LAST;
286 l_line_index := x_source_line_tab.NEXT(l_line_index);
287 END LOOP; --} -- loop for lines
288
289 -- Setting the Ship and Arrival Date for the Header
290 -- Header is created based on the grouping criterias including
291 -- Scheduled Dates of Lines, Ship From/To and other criterias
292 -- As the inside loop is for Lines, ensure the call for header is
293 -- in the header loop
294 x_source_header_tab(l_header_index).ship_date := l_ship_date;
295 x_source_header_tab(l_header_index).arrival_date := l_arrival_date;
296
297 -- Print Header Information outside the Loop for Order Lines
298 IF l_debug_on THEN
299 WSH_DEBUG_SV.log(l_module_name,
300 'x_source_header_tab('||l_header_index||').ship_date after changes ',
301 x_source_header_tab(l_header_index).ship_date);
302 WSH_DEBUG_SV.log(l_module_name,
303 'x_source_header_tab('||l_header_index||').arrival_date after changes ',
304 x_source_header_tab(l_header_index).arrival_date);
305 WSH_DEBUG_SV.logmsg(l_module_name,'=====================================');
306 END IF;
307
308 EXIT WHEN l_header_index >= x_source_header_tab.LAST;
309 l_header_index := x_source_header_tab.NEXT(l_header_index);
310 END LOOP;--} -- loop for header
311
312 END IF;--} -- x_source_header_tab and x_source_line_tab are populated
313
314 IF l_debug_on THEN
315 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
316 WSH_DEBUG_SV.pop(l_module_name);
317 END IF;
318
319 EXCEPTION
320 WHEN OTHERS THEN
321
322 IF c_get_order_date_type_csr%ISOPEN THEN
323 CLOSE c_get_order_date_type_csr;
324 END IF;
325
326 IF c_get_line_dates_csr%ISOPEN THEN
327 CLOSE c_get_line_dates_csr;
328 END IF;
329
330 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
331 WSH_UTIL_CORE.DEFAULT_HANDLER('WSH_OTM_RIQ_XML.DERIVE_RIQ_DATES',l_module_name);
332
333 IF l_debug_on THEN
334 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
335 WSH_DEBUG_SV.logmsg(l_module_name,
336 'Unexpected error has occured in derive_riq_dates. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
337 WSH_DEBUG_SV.pop(l_module_name);
338 END IF;
339
340 END DERIVE_RIQ_DATES;
341 -- End of ECO 5516007, FP to R12
342 --=========================================================================
343
344
345 PROCEDURE print_CLOB(
346 p_clob IN CLOB,
347 x_return_status OUT NOCOPY VARCHAR2)
348 IS
349
350 i NUMBER;
351 l_amt NUMBER;
352 l_pos NUMBER;
353 l_buffer VARCHAR2(32767);
354 l_length NUMBER;
355 l_return_status VARCHAR2(1);
356 l_debug_on BOOLEAN;
357 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'print_CLOB';
358
359 BEGIN
360
361 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
362 IF l_debug_on IS NULL
363 THEN
364 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
365 END IF;
366
367 IF l_debug_on THEN
368 WSH_DEBUG_SV.push(l_module_name);
369 END IF;
370
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372
373
374
375
376
377 IF (l_debug_on AND p_clob IS NOT NULL)
378 THEN
379 l_amt:= 32000;
380 l_pos:= 1;
381 l_length:=DBMS_LOB.GETLENGTH(p_clob);
382
383 WSH_DEBUG_SV.log(l_module_name,'Length',l_length);
384
385 WHILE(l_length > 0)
386 LOOP
387
388 IF (l_length < l_amt)
389 THEN
390
391 l_amt:=l_length;
392
393 END IF;
394
395 dbms_lob.read (p_clob, l_amt, l_pos, l_buffer);
396
397 WSH_DEBUG_SV.log(l_module_name, l_buffer);
398
399 l_length:=l_length-l_amt;
400 l_pos := l_pos + l_amt;
401 END LOOP;
402
403
404 END IF;
405
406 IF l_debug_on THEN
407 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
408 WSH_DEBUG_SV.pop(l_module_name);
409 END IF;
410 --
411 EXCEPTION
412
413 WHEN FND_API.G_EXC_ERROR THEN
414 x_return_status := FND_API.G_RET_STS_ERROR;
415
416 IF l_debug_on THEN
417 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
418 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
419 END IF;
420
421 WHEN others THEN
422 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
423 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.print_CLOB',l_module_name);
424
425 IF l_debug_on THEN
426 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
427 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
428 END IF;
429 --
430
431 END print_CLOB;
432
433
434
435 PROCEDURE print_source_line_tab (
436 p_source_line_tab IN FTE_PROCESS_REQUESTS.fte_source_line_tab,
437 x_return_status OUT NOCOPY VARCHAR2)
438 IS
439
440 i NUMBER;
441 l_return_status VARCHAR2(1);
442 l_debug_on BOOLEAN;
443 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'print_source_line_tab';
444
445 BEGIN
446
447 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
448 IF l_debug_on IS NULL
449 THEN
450 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
451 END IF;
452
453 IF l_debug_on THEN
454 WSH_DEBUG_SV.push(l_module_name);
455 END IF;
456
457 x_return_status := FND_API.G_RET_STS_SUCCESS;
458
459
460
461
462 i := p_source_line_tab.FIRST;
463 IF ((i IS NOT NULL) AND (l_debug_on))
464 THEN
465 WSH_DEBUG_SV.log(l_module_name,'-----------BEGIN Source Line Tab -------------');
466 LOOP
467 WSH_DEBUG_SV.log(l_module_name,'i := '||i);
468
469 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).source_type ',p_source_line_tab(i).source_type);
470 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).source_header_id ',p_source_line_tab(i).source_header_id);
471 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).source_line_id ',p_source_line_tab(i).source_line_id);
472 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).ship_from_org_id ',p_source_line_tab(i).ship_from_org_id);
473 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).ship_from_location_id ',p_source_line_tab(i).ship_from_location_id);
474 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).ship_to_site_id ',p_source_line_tab(i).ship_to_site_id);
475 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).ship_to_location_id ',p_source_line_tab(i).ship_to_location_id);
476 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).customer_id ',p_source_line_tab(i).customer_id);
477 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).inventory_item_id ',p_source_line_tab(i).inventory_item_id);
478 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).source_quantity ',p_source_line_tab(i).source_quantity);
479 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).source_quantity_uom ',p_source_line_tab(i).source_quantity_uom);
480 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).ship_date ',p_source_line_tab(i).ship_date);
481 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).arrival_date ',p_source_line_tab(i).arrival_date);
482 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).delivery_lead_time ',p_source_line_tab(i).delivery_lead_time);
483 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).scheduled_flag ',p_source_line_tab(i).scheduled_flag);
484 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).order_set_type ',p_source_line_tab(i).order_set_type);
485 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).order_set_id ',p_source_line_tab(i).order_set_id);
486 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).intmed_ship_to_site_id ',p_source_line_tab(i).intmed_ship_to_site_id);
487 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).intmed_ship_to_loc_id ',p_source_line_tab(i).intmed_ship_to_loc_id);
488 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).carrier_id ',p_source_line_tab(i).carrier_id);
489 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).ship_method_flag ',p_source_line_tab(i).ship_method_flag);
490 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).ship_method_code ',p_source_line_tab(i).ship_method_code);
491 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).freight_carrier_code ',p_source_line_tab(i).freight_carrier_code);
492 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).service_level ',p_source_line_tab(i).service_level);
493 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).mode_of_transport ',p_source_line_tab(i).mode_of_transport);
494 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).freight_terms ',p_source_line_tab(i).freight_terms);
495 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).fob_code ',p_source_line_tab(i).fob_code);
496 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).weight ',p_source_line_tab(i).weight );
497 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).weight_uom_code ',p_source_line_tab(i).weight_uom_code);
498 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).volume ',p_source_line_tab(i).volume );
499 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).volume_uom_code ',p_source_line_tab(i).volume_uom_code);
500 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).freight_rating_flag ',p_source_line_tab(i).freight_rating_flag);
501 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).freight_rate ',p_source_line_tab(i).freight_rate);
502 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).freight_rate_currency ',p_source_line_tab(i).freight_rate_currency);
503 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).status ',p_source_line_tab(i).status );
504 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).message_data ',p_source_line_tab(i).message_data);
505 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).consolidation_id ',p_source_line_tab(i).consolidation_id);
506 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).override_ship_method ',p_source_line_tab(i).override_ship_method);
507 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).currency ',p_source_line_tab(i).currency);
508 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).currency_conversion_type ',p_source_line_tab(i).currency_conversion_type);
509
510
511 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).origin_country ',p_source_line_tab(i).origin_country);
512 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).origin_state ',p_source_line_tab(i).origin_state);
513 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).origin_city ',p_source_line_tab(i).origin_city);
514 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).origin_zip ',p_source_line_tab(i).origin_zip);
515
516 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).destination_country ',p_source_line_tab(i).destination_country);
517 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).destination_state ',p_source_line_tab(i).destination_state);
518 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).destination_city ',p_source_line_tab(i).destination_city);
519 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).destination_zip ',p_source_line_tab(i).destination_zip);
520
521 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).distance ',p_source_line_tab(i).distance);
522 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).distance_uom ',p_source_line_tab(i).distance_uom);
523 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).vehicle_item_id ',p_source_line_tab(i).vehicle_item_id);
524 WSH_DEBUG_SV.log(l_module_name,'l_source_line_tab(i).commodity_category_id ',p_source_line_tab(i).commodity_category_id);
525
526
527 WSH_DEBUG_SV.log(l_module_name,'------------------------');
528
529 EXIT WHEN (i >= p_source_line_tab.LAST);
530 i := p_source_line_tab.NEXT(i);
531 END LOOP;
532 END IF;
533
534 IF l_debug_on THEN
535 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
536 WSH_DEBUG_SV.pop(l_module_name);
537 END IF;
538 --
539 EXCEPTION
540
541 WHEN FND_API.G_EXC_ERROR THEN
542 x_return_status := FND_API.G_RET_STS_ERROR;
543
544 IF l_debug_on THEN
545 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
546 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
547 END IF;
548
549 WHEN others THEN
550 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
551 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.print_source_line_tab',l_module_name);
552
553 IF l_debug_on THEN
554 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
555 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
556 END IF;
557 --
558
559 END print_source_line_tab;
560
561 PROCEDURE print_source_header_tab (
562 p_source_header_tab IN FTE_PROCESS_REQUESTS.fte_source_header_tab,
563 x_return_status OUT NOCOPY VARCHAR2)
564 IS
565 i NUMBER;
566 l_return_status VARCHAR2(1);
567 l_debug_on BOOLEAN;
568 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'print_source_header_tab';
569
570 BEGIN
571
572 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
573 IF l_debug_on IS NULL
574 THEN
575 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
576 END IF;
577
578 IF l_debug_on THEN
579 WSH_DEBUG_SV.push(l_module_name);
580 END IF;
581
582 x_return_status := FND_API.G_RET_STS_SUCCESS;
583
584
585
586
587 i := p_source_header_tab.FIRST;
588 IF ((i IS NOT NULL) AND (l_debug_on)) THEN
589 WSH_DEBUG_SV.log(l_module_name,'-----------BEGIN Source Header Tab -------------');
590 LOOP
591 WSH_DEBUG_SV.log(l_module_name,'i ',i);
592
593 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).consolidation_id ',p_source_header_tab(i).consolidation_id);
594 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).ship_from_org_id ',p_source_header_tab(i).ship_from_org_id);
595 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).ship_from_location_id ',p_source_header_tab(i).ship_from_location_id);
596 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).ship_to_location_id ',p_source_header_tab(i).ship_to_location_id);
597 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).ship_to_site_id ',p_source_header_tab(i).ship_to_site_id);
598 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).customer_id ',p_source_header_tab(i).customer_id);
599 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).ship_date ',p_source_header_tab(i).ship_date);
600 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).arrival_date ',p_source_header_tab(i).arrival_date);
601 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).delivery_lead_time ',p_source_header_tab(i).delivery_lead_time);
602 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).scheduled_flag ',p_source_header_tab(i).scheduled_flag);
603 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).total_weight ',p_source_header_tab(i).total_weight);
604 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).weight_uom_code ',p_source_header_tab(i).weight_uom_code);
605 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).total_volume ',p_source_header_tab(i).total_volume);
606 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).volume_uom_code ',p_source_header_tab(i).volume_uom_code);
607 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).ship_method_code ',p_source_header_tab(i).ship_method_code);
608 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).carrier_id ',p_source_header_tab(i).carrier_id);
609 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).service_level ',p_source_header_tab(i).service_level);
610 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).mode_of_transport ',p_source_header_tab(i).mode_of_transport);
611 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).freight_terms ',p_source_header_tab(i).freight_terms);
612 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).status ',p_source_header_tab(i).status );
613 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).message_data ',p_source_header_tab(i).message_data);
614 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).enforce_lead_time ',p_source_header_tab(i).enforce_lead_time);
615 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).currency ',p_source_header_tab(i).currency);
616 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).currency_conversion_type ',p_source_header_tab(i).currency_conversion_type);
617
618 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).origin_country ',p_source_header_tab(i).origin_country);
619 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).origin_state ',p_source_header_tab(i).origin_state);
620 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).origin_city ',p_source_header_tab(i).origin_city);
621 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).origin_zip ',p_source_header_tab(i).origin_zip);
622
623
624 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).destination_country ',p_source_header_tab(i).destination_country);
625 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).destination_state ',p_source_header_tab(i).destination_state);
626 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).destination_city ',p_source_header_tab(i).destination_city);
627 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).destination_zip ',p_source_header_tab(i).destination_zip);
628
629 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).distance ',p_source_header_tab(i).distance);
630 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).distance_uom ',p_source_header_tab(i).distance_uom);
631 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).vehicle_item_id ',p_source_header_tab(i).vehicle_item_id);
632 WSH_DEBUG_SV.log(l_module_name,'l_source_header_tab(i).commodity_category_id ',p_source_header_tab(i).commodity_category_id);
633
634
635 WSH_DEBUG_SV.log(l_module_name,'------------------------');
636
637
638 EXIT WHEN (i >= p_source_header_tab.LAST);
639 i := p_source_header_tab.NEXT(i);
640 END LOOP;
641 END IF;
642
643
644 IF l_debug_on THEN
645 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
646 WSH_DEBUG_SV.pop(l_module_name);
647 END IF;
648 --
649 EXCEPTION
650
651 WHEN FND_API.G_EXC_ERROR THEN
652 x_return_status := FND_API.G_RET_STS_ERROR;
653
654 IF l_debug_on THEN
655 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
656 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
657 END IF;
658
659 WHEN others THEN
660 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
661 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.print_source_header_tab',l_module_name);
662
663 IF l_debug_on THEN
664 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
665 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
666 END IF;
667 --
668
669 END print_source_header_tab;
670
671
672 PROCEDURE print_rates_tab (
673 p_source_line_rates_tab IN FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
674 p_source_header_rates_tab IN FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
675 x_return_status OUT NOCOPY VARCHAR2)
676 IS
677 i NUMBER;
678 l_return_status VARCHAR2(1);
679 l_debug_on BOOLEAN;
680 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'print_rates_tab';
681
682 BEGIN
683
684 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
685 IF l_debug_on IS NULL
686 THEN
687 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
688 END IF;
689
690 IF l_debug_on THEN
691 WSH_DEBUG_SV.push(l_module_name);
692 END IF;
693
694 x_return_status := FND_API.G_RET_STS_SUCCESS;
695
696
697
698 i := p_source_line_rates_tab.FIRST;
699 IF (i IS NOT NULL) THEN
700 WSH_DEBUG_SV.log(l_module_name,'-----------BEGIN Source Line Rates Tab -------------');
701 LOOP
702 WSH_DEBUG_SV.log(l_module_name,'I',i);
703 WSH_DEBUG_SV.log(l_module_name,'source_line_id',p_source_line_rates_tab(i).source_line_id);
704 WSH_DEBUG_SV.log(l_module_name,'cost_type_id',p_source_line_rates_tab(i).cost_type_id);
705 WSH_DEBUG_SV.log(l_module_name,'line_type_code',p_source_line_rates_tab(i).line_type_code);
706 WSH_DEBUG_SV.log(l_module_name,'cost_type',p_source_line_rates_tab(i).cost_type);
707 WSH_DEBUG_SV.log(l_module_name,'cost_sub_type',p_source_line_rates_tab(i).cost_sub_type);
708 WSH_DEBUG_SV.log(l_module_name,'priced_quantity',p_source_line_rates_tab(i).priced_quantity);
709 WSH_DEBUG_SV.log(l_module_name,'priced_uom',p_source_line_rates_tab(i).priced_uom);
710 WSH_DEBUG_SV.log(l_module_name,'unit_price',p_source_line_rates_tab(i).unit_price);
711 WSH_DEBUG_SV.log(l_module_name,'base_price',p_source_line_rates_tab(i).base_price);
712 WSH_DEBUG_SV.log(l_module_name,'adjusted_unit_price',p_source_line_rates_tab(i).adjusted_unit_price);
713 WSH_DEBUG_SV.log(l_module_name,'adjusted_price',p_source_line_rates_tab(i).adjusted_price);
714 WSH_DEBUG_SV.log(l_module_name,'currency',p_source_line_rates_tab(i).currency);
715 WSH_DEBUG_SV.log(l_module_name,'consolidation_id',p_source_line_rates_tab(i).consolidation_id);
716 WSH_DEBUG_SV.log(l_module_name,'lane_id',p_source_line_rates_tab(i).lane_id);
717 WSH_DEBUG_SV.log(l_module_name,'carrier_id',p_source_line_rates_tab(i).carrier_id);
718 WSH_DEBUG_SV.log(l_module_name,'carrier_freight_code',p_source_line_rates_tab(i).carrier_freight_code);
719 WSH_DEBUG_SV.log(l_module_name,'service_level',p_source_line_rates_tab(i).service_level);
720 WSH_DEBUG_SV.log(l_module_name,'mode_of_transport',p_source_line_rates_tab(i).mode_of_transport);
721 WSH_DEBUG_SV.log(l_module_name,'ship_method_code',p_source_line_rates_tab(i).ship_method_code);
722 WSH_DEBUG_SV.log(l_module_name,'------------------------');
723
724 EXIT WHEN (i >= p_source_line_rates_tab.LAST);
725 i := p_source_line_rates_tab.NEXT(i);
726 END LOOP;
727 END IF;
728
729 WSH_DEBUG_SV.log(l_module_name,'-----------BEGIN Source header Rates Tab -------------');
730
731 i := p_source_header_rates_tab.FIRST;
732 IF (i IS NOT NULL) THEN
733 LOOP
734 WSH_DEBUG_SV.log(l_module_name,'I',i);
735 WSH_DEBUG_SV.log(l_module_name,'consolidation_id',p_source_header_rates_tab(i).consolidation_id);
736 WSH_DEBUG_SV.log(l_module_name,'lane_id',p_source_header_rates_tab(i).lane_id);
737 WSH_DEBUG_SV.log(l_module_name,'carrier_id',p_source_header_rates_tab(i).carrier_id);
738 WSH_DEBUG_SV.log(l_module_name,'carrier_freight_code',p_source_header_rates_tab(i).carrier_freight_code);
739 WSH_DEBUG_SV.log(l_module_name,'service_level',p_source_header_rates_tab(i).service_level);
740 WSH_DEBUG_SV.log(l_module_name,'mode_of_transport',p_source_header_rates_tab(i).mode_of_transport);
741 WSH_DEBUG_SV.log(l_module_name,'ship_method_code',p_source_header_rates_tab(i).ship_method_code);
742 WSH_DEBUG_SV.log(l_module_name,'cost_type_id',p_source_header_rates_tab(i).cost_type_id);
743 WSH_DEBUG_SV.log(l_module_name,'cost_type',p_source_header_rates_tab(i).cost_type);
744 WSH_DEBUG_SV.log(l_module_name,'price',p_source_header_rates_tab(i).price);
745 WSH_DEBUG_SV.log(l_module_name,'currency',p_source_header_rates_tab(i).currency);
746 WSH_DEBUG_SV.log(l_module_name,'transit_time',p_source_header_rates_tab(i).transit_time);
747 WSH_DEBUG_SV.log(l_module_name,'transit_time_uom',p_source_header_rates_tab(i).transit_time_uom);
748 WSH_DEBUG_SV.log(l_module_name,'first_line_index',p_source_header_rates_tab(i).first_line_index);
749 WSH_DEBUG_SV.log(l_module_name,'------------------------');
750
751 EXIT WHEN (i >= p_source_header_rates_tab.LAST);
752 i := p_source_header_rates_tab.NEXT(i);
753 END LOOP;
754 END IF;
755 WSH_DEBUG_SV.log(l_module_name,'---- END Source header Rates Tab ------- ');
756
757 IF l_debug_on THEN
758 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
759 WSH_DEBUG_SV.pop(l_module_name);
760 END IF;
761 --
762 EXCEPTION
763
764 WHEN FND_API.G_EXC_ERROR THEN
765 x_return_status := FND_API.G_RET_STS_ERROR;
766
767 IF l_debug_on THEN
768 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
769 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
770 END IF;
771
772 WHEN others THEN
773 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
774 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.print_rates_tab',l_module_name);
775
776 IF l_debug_on THEN
777 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
778 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
779 END IF;
780 --
781
782 END print_rates_tab;
783
784
785
786 PROCEDURE print_CS_Results(
787 p_result_consolidation_id_tab IN WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
788 p_result_carrier_id_tab IN WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
789 p_result_service_level_tab IN WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
790 p_result_mode_of_transport_tab IN WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
791 p_result_freight_term_tab IN WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
792 p_result_transit_time_min_tab IN WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
793 p_result_transit_time_max_tab IN WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
794 p_ship_method_code_tab IN WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
795 x_return_status OUT NOCOPY VARCHAR2)
796 IS
797 i NUMBER;
798 l_return_status VARCHAR2(1);
799 l_debug_on BOOLEAN;
800 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'print_CS_Results';
801
802 BEGIN
803
804 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
805 IF l_debug_on IS NULL
806 THEN
807 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
808 END IF;
809
810 IF l_debug_on THEN
811 WSH_DEBUG_SV.push(l_module_name);
812 END IF;
813
814 x_return_status := FND_API.G_RET_STS_SUCCESS;
815
816
817
818 i := p_result_consolidation_id_tab.FIRST;
819 IF (i IS NOT NULL) THEN
820 WSH_DEBUG_SV.log(l_module_name,'-----------BEGIN CS Results -------------');
821 LOOP
822 WSH_DEBUG_SV.log(l_module_name,'I',i);
823 WSH_DEBUG_SV.log(l_module_name,'consol_id',p_result_consolidation_id_tab(i));
824 IF (p_result_carrier_id_tab.EXISTS(i))
825 THEN
826 WSH_DEBUG_SV.log(l_module_name,'carrier_id',p_result_carrier_id_tab(i));
827 END IF;
828 IF (p_result_service_level_tab.EXISTS(i))
829 THEN
830 WSH_DEBUG_SV.log(l_module_name,'service_level',p_result_service_level_tab(i));
831 END IF;
832
833 IF (p_result_mode_of_transport_tab.EXISTS(i))
834 THEN
835 WSH_DEBUG_SV.log(l_module_name,'mode_of_transport',p_result_mode_of_transport_tab(i));
836 END IF;
837
838 IF (p_result_freight_term_tab.EXISTS(i))
839 THEN
840 WSH_DEBUG_SV.log(l_module_name,'freight_term',p_result_freight_term_tab(i));
841 END IF;
842
843 IF (p_result_transit_time_min_tab.EXISTS(i))
844 THEN
845 WSH_DEBUG_SV.log(l_module_name,'transit_time_min',p_result_transit_time_min_tab(i));
846 END IF;
847
848
849 IF (p_result_transit_time_max_tab.EXISTS(i))
850 THEN
851 WSH_DEBUG_SV.log(l_module_name,'transit_time_max',p_result_transit_time_max_tab(i));
852 END IF;
853
854 IF (p_ship_method_code_tab.EXISTS(i))
855 THEN
856 WSH_DEBUG_SV.log(l_module_name,'ship_method_code',p_ship_method_code_tab(i));
857 END IF;
858
859
860 WSH_DEBUG_SV.log(l_module_name,'------------------------');
861
862 EXIT WHEN (i >= p_result_consolidation_id_tab.LAST);
863 i := p_result_consolidation_id_tab.NEXT(i);
864 END LOOP;
865 END IF;
866
867 WSH_DEBUG_SV.log(l_module_name,'---- END CS Results ------- ');
868
869 IF l_debug_on THEN
870 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
871 WSH_DEBUG_SV.pop(l_module_name);
872 END IF;
873 --
874 EXCEPTION
875
876 WHEN FND_API.G_EXC_ERROR THEN
877 x_return_status := FND_API.G_RET_STS_ERROR;
878
879 IF l_debug_on THEN
880 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
881 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
882 END IF;
883
884 WHEN others THEN
885 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
886 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.print_rates_tab',l_module_name);
887
888 IF l_debug_on THEN
889 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
890 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
891 END IF;
892 --
893
894 END print_CS_Results;
895
896
897 FUNCTION Convert_Carrier_Ouput(
898 p_carrier IN VARCHAR2) RETURN NUMBER
899 IS
900
901 l_carrier_prefix VARCHAR2(4);
902 l_carrier VARCHAR2(30);
903 l_carrier_id NUMBER;
904 l_debug_on BOOLEAN;
905 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Convert_Carrier_Ouput';
906
907 BEGIN
908
909 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
910 IF l_debug_on IS NULL
911 THEN
912 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
913 END IF;
914
915 IF l_debug_on THEN
916 WSH_DEBUG_SV.push(l_module_name);
917 END IF;
918
919 l_carrier_id:=NULL;
920
921
922
923 IF ((p_carrier IS NOT NULL) AND (LENGTH(p_carrier) > 4)AND (LENGTH(p_carrier) < 34))
924 THEN
925 l_carrier_prefix:=SUBSTR(p_carrier,1,4);
926 IF(l_carrier_prefix = G_CARRIER_PREFIX)
927 THEN
928 l_carrier:=SUBSTR(p_carrier,5);
929 l_carrier_id:=TO_NUMBER(l_carrier);
930 END IF;
931 END IF;
932
933 IF l_debug_on THEN
934
935 WSH_DEBUG_SV.pop(l_module_name);
936 END IF;
937
938 RETURN l_carrier_id;
939
940 --
941 EXCEPTION
942
943 WHEN others THEN
944
945 IF l_debug_on THEN
946 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
947 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
948 END IF;
949 RETURN NULL;
950 --
951
952 END Convert_Carrier_Ouput;
953
954
955 FUNCTION Convert_To_Number(
956 p_string IN VARCHAR2) RETURN NUMBER
957 IS
958 l_number NUMBER;
959 l_debug_on BOOLEAN;
960 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Convert_To_Number';
961
962 BEGIN
963
964 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
965 IF l_debug_on IS NULL
966 THEN
967 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
968 END IF;
969
970 IF l_debug_on THEN
971 WSH_DEBUG_SV.push(l_module_name);
972 END IF;
973
974 l_number:=NULL;
975
976 l_number := TO_NUMBER(p_string);
977
978 IF l_debug_on THEN
979
980 WSH_DEBUG_SV.pop(l_module_name);
981 END IF;
982
983 RETURN l_number;
984
985 --
986 EXCEPTION
987
988 WHEN others THEN
989
990 IF l_debug_on THEN
991 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
992 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
993 END IF;
994 RETURN NULL;
995 --
996
997 END Convert_To_Number;
998
999 --WSH_UTIL_VALIDATE.Validate_Carrier accepts carrier name not id
1000
1001 PROCEDURE Validate_Carrier(
1002 p_carrier_id IN NUMBER,
1003 x_return_status OUT NOCOPY VARCHAR2)
1004 IS
1005 l_carrier_id NUMBER;
1006
1007 l_return_status VARCHAR2(1);
1008 l_debug_on BOOLEAN;
1009 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Carrier';
1010
1011 CURSOR c_check_carrier(c_carrier_id IN NUMBER)
1012 IS
1013 SELECT carrier_id
1014 FROM WSH_CARRIERS
1015 WHERE carrier_id= c_carrier_id;
1016
1017 BEGIN
1018
1019 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1020 IF l_debug_on IS NULL
1021 THEN
1022 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1023 END IF;
1024
1025 IF l_debug_on THEN
1026 WSH_DEBUG_SV.push(l_module_name);
1027 END IF;
1028
1029 x_return_status := FND_API.G_RET_STS_SUCCESS;
1030
1031 l_carrier_id:=NULL;
1032
1033 IF(p_carrier_id IS NOT NULL)
1034 THEN
1035 OPEN c_check_carrier(p_carrier_id);
1036 FETCH c_check_carrier INTO l_carrier_id;
1037 CLOSE c_check_carrier;
1038
1039 END IF;
1040
1041 IF (l_carrier_id IS NULL)
1042 THEN
1043 x_return_status := FND_API.G_RET_STS_ERROR;
1044 END IF;
1045
1046 IF l_debug_on THEN
1047 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1048 WSH_DEBUG_SV.pop(l_module_name);
1049 END IF;
1050 --
1051 EXCEPTION
1052
1053 WHEN FND_API.G_EXC_ERROR THEN
1054 x_return_status := FND_API.G_RET_STS_ERROR;
1055
1056 IF l_debug_on THEN
1057 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1058 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1059 END IF;
1060
1061 WHEN others THEN
1062 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1063 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Validate_Carrier',l_module_name);
1064
1065 IF l_debug_on THEN
1066 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1067 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1068 END IF;
1069 --
1070
1071 END Validate_Carrier;
1072
1073
1074 PROCEDURE Validate_Look_Up_NoCase(
1075 p_lookup_code IN VARCHAR2,
1076 p_lookup_type IN VARCHAR2,
1077 x_lookup_code OUT NOCOPY VARCHAR2,
1078 x_return_status OUT NOCOPY VARCHAR2)
1079 IS
1080 l_code VARCHAR2(30);
1081
1082 l_return_status VARCHAR2(1);
1083 l_debug_on BOOLEAN;
1084 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Look_Up_NoCase';
1085
1086 -- fixed in R12, select from fnd_lookup_values not from oe_lookups
1087 -- cause WSH_SERVICE_LEVELS, WSH_MODE_OF_TRANSPORT are not defined in oe_lookup
1088
1089 CURSOR c_check_lookup(c_lookup_code IN VARCHAR2)
1090 IS
1091 SELECT lookup_code
1092 FROM fnd_lookup_values
1093 WHERE lookup_type = p_lookup_type
1094 AND UPPER(lookup_code) = UPPER(c_lookup_code)
1095 AND nvl(start_date_active,SYSDATE) <= SYSDATE
1096 AND nvl(end_date_active,SYSDATE) >= SYSDATE
1097 AND enabled_flag = 'Y'
1098 AND view_application_id in (660,665);
1099
1100 BEGIN
1101
1102 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1103 IF l_debug_on IS NULL
1104 THEN
1105 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1106 END IF;
1107
1108 IF l_debug_on THEN
1109 WSH_DEBUG_SV.push(l_module_name);
1110 wsh_debug_sv.log(l_module_name, 'lookup_code', p_lookup_code);
1111 wsh_debug_sv.log(l_module_name, 'lookup_type', p_lookup_type);
1112 END IF;
1113
1114 x_return_status := FND_API.G_RET_STS_SUCCESS;
1115
1116 l_code:=NULL;
1117
1118 IF(p_lookup_code IS NOT NULL)
1119 THEN
1120 OPEN c_check_lookup(p_lookup_code);
1121 FETCH c_check_lookup INTO l_code;
1122 CLOSE c_check_lookup;
1123
1124 END IF;
1125
1126 x_lookup_code:=l_code;
1127
1128 IF (l_code IS NULL)
1129 THEN
1130 x_return_status := FND_API.G_RET_STS_ERROR;
1131 END IF;
1132
1133 IF l_debug_on THEN
1134 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1135 WSH_DEBUG_SV.pop(l_module_name);
1136 END IF;
1137 --
1138 EXCEPTION
1139
1140 WHEN FND_API.G_EXC_ERROR THEN
1141 x_return_status := FND_API.G_RET_STS_ERROR;
1142
1143 IF l_debug_on THEN
1144 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1145 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1146 END IF;
1147
1148 WHEN others THEN
1149 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1150 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Validate_Look_Up_NoCase',l_module_name);
1151
1152 IF l_debug_on THEN
1153 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1154 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1155 END IF;
1156 --
1157
1158 END Validate_Look_Up_NoCase;
1159
1160
1161
1162 PROCEDURE Get_OTM_To_EBS_UOM(
1163 p_uom IN VARCHAR2,
1164 p_uom_class IN VARCHAR2,
1165 x_uom OUT NOCOPY VARCHAR2,
1166 x_return_status OUT NOCOPY VARCHAR2)
1167 IS
1168
1169 CURSOR c_get_EBS_UOM(c_uom_class IN VARCHAR2,c_uom IN VARCHAR2)
1170 IS
1171 SELECT uom_code
1172 FROM MTL_UNITS_OF_MEASURE
1173 WHERE uom_class=c_uom_class
1174 AND nvl(disable_date, sysdate) >= SYSDATE
1175 AND attribute15=c_uom;
1176
1177 CURSOR c_check_uom(c_uom_class IN VARCHAR2, c_uom IN VARCHAR2)
1178 IS
1179 SELECT UOM_CODE
1180 FROM mtl_units_of_measure
1181 WHERE uom_code = c_uom
1182 AND nvl(disable_date, sysdate) >= SYSDATE
1183 AND uom_class = c_uom_class;
1184
1185
1186 l_uom VARCHAR2(30);
1187
1188 l_return_status VARCHAR2(1);
1189 l_debug_on BOOLEAN;
1190 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_OTM_To_EBS_UOM';
1191
1192
1193 BEGIN
1194
1195 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1196 IF l_debug_on IS NULL
1197 THEN
1198 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1199 END IF;
1200
1201 IF l_debug_on THEN
1202 WSH_DEBUG_SV.push(l_module_name);
1203 END IF;
1204
1205 x_return_status := FND_API.G_RET_STS_SUCCESS;
1206
1207
1208
1209 l_uom:=NULL;
1210 IF(p_uom IS NOT NULL)
1211 THEN
1212
1213 IF (g_OTM_to_EBS_UOM_map.EXISTS(p_uom))
1214 THEN
1215 l_uom:=g_OTM_to_EBS_UOM_map(p_uom);
1216
1217 ELSE
1218
1219
1220 OPEN c_check_uom(p_uom_class,p_uom);
1221 FETCH c_check_uom INTO l_uom;
1222 CLOSE c_check_uom;
1223
1224 IF(l_uom IS NULL)
1225 THEN
1226
1227 OPEN c_get_EBS_UOM(p_uom_class,p_uom);
1228 FETCH c_get_EBS_UOM INTO l_uom;
1229 CLOSE c_get_EBS_UOM;
1230 END IF;
1231
1232 g_OTM_to_EBS_UOM_map(p_uom):=l_uom;
1233
1234 END IF;
1235 END IF;
1236
1237 x_uom:=l_uom;
1238
1239 IF l_debug_on THEN
1240 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1241 WSH_DEBUG_SV.pop(l_module_name);
1242 END IF;
1243 --
1244 EXCEPTION
1245
1246 WHEN FND_API.G_EXC_ERROR THEN
1247 x_return_status := FND_API.G_RET_STS_ERROR;
1248
1249 IF l_debug_on THEN
1250 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1251 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1252 END IF;
1253
1254 WHEN others THEN
1255 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1256 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Get_OTM_To_EBS_UOM',l_module_name);
1257
1258 IF l_debug_on THEN
1259 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1260 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1261 END IF;
1262 --
1263
1264 END Get_OTM_To_EBS_UOM;
1265
1266
1267
1268 PROCEDURE Get_EBS_To_OTM_UOM(
1269 p_uom IN VARCHAR2,
1270 x_uom OUT NOCOPY VARCHAR2,
1271 x_return_status OUT NOCOPY VARCHAR2)
1272 IS
1273
1274 CURSOR c_get_OTM_UOM(c_uom IN VARCHAR2)
1275 IS
1276 SELECT attribute15
1277 FROM MTL_UNITS_OF_MEASURE
1278 WHERE uom_code=c_uom
1279 AND nvl(disable_date, sysdate) >= SYSDATE;
1280
1281 l_uom VARCHAR2(30);
1282
1283 l_return_status VARCHAR2(1);
1284 l_debug_on BOOLEAN;
1285 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_EBS_To_OTM_UOM';
1286
1287
1288 BEGIN
1289
1290 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1291 IF l_debug_on IS NULL
1292 THEN
1293 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1294 END IF;
1295
1296 IF l_debug_on THEN
1297 WSH_DEBUG_SV.push(l_module_name);
1298 END IF;
1299
1300 x_return_status := FND_API.G_RET_STS_SUCCESS;
1301
1302
1303
1304 l_uom:=NULL;
1305 IF(p_uom IS NOT NULL)
1306 THEN
1307
1308 IF (g_EBS_to_OTM_UOM_map.EXISTS(p_uom))
1309 THEN
1310 l_uom:=g_EBS_to_OTM_UOM_map(p_uom);
1311
1312 ELSE
1313
1314 OPEN c_get_OTM_UOM(p_uom);
1315 FETCH c_get_OTM_UOM INTO l_uom;
1316 CLOSE c_get_OTM_UOM;
1317
1318 IF (l_uom IS NULL)
1319 THEN
1320 l_uom:=p_uom;
1321 END IF;
1322 g_EBS_to_OTM_UOM_map(p_uom):=l_uom;
1323
1324 END IF;
1325 END IF;
1326
1327 x_uom:=l_uom;
1328
1329 IF l_debug_on THEN
1330 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1331 WSH_DEBUG_SV.pop(l_module_name);
1332 END IF;
1333 --
1334 EXCEPTION
1335
1336 WHEN FND_API.G_EXC_ERROR THEN
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338
1339 IF l_debug_on THEN
1340 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1341 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1342 END IF;
1343
1344 WHEN others THEN
1345 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1346 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Get_EBS_To_OTM_UOM',l_module_name);
1347
1348 IF l_debug_on THEN
1349 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1350 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1351 END IF;
1352 --
1353
1354 END Get_EBS_To_OTM_UOM;
1355
1356
1357 PROCEDURE Get_Carrier_Info(
1358 p_carrier_id IN NUMBER,
1359 x_generic OUT NOCOPY VARCHAR2,
1360 x_carrier_freight_code OUT NOCOPY VARCHAR2,
1361 x_return_status OUT NOCOPY VARCHAR2)
1362 IS
1363
1364 CURSOR c_get_carrier_info (c_carrier_id VARCHAR2) IS
1365 SELECT freight_code,generic_flag
1366 FROM wsh_carriers
1367 WHERE carrier_id = c_carrier_id;
1368
1369 l_carrier_freight_code VARCHAR2(30);
1370 l_generic_carrier VARCHAR2(1);
1371
1372 l_return_status VARCHAR2(1);
1373 l_debug_on BOOLEAN;
1374 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Carrier_Info';
1375
1376 BEGIN
1377
1378 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1379 IF l_debug_on IS NULL
1380 THEN
1381 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1382 END IF;
1383
1384 IF l_debug_on THEN
1385 WSH_DEBUG_SV.push(l_module_name);
1386 END IF;
1387
1388 x_return_status := FND_API.G_RET_STS_SUCCESS;
1389
1390 IF((g_carrier_freight_codes.EXISTS(p_carrier_id)) AND (g_carrier_generic_flags.EXISTS(p_carrier_id)))
1391 THEN
1392 x_generic:=g_carrier_generic_flags(p_carrier_id);
1393 x_carrier_freight_code:=g_carrier_freight_codes(p_carrier_id);
1394 ELSE
1395
1396 OPEN c_get_carrier_info(p_carrier_id);
1397 FETCH c_get_carrier_info INTO l_carrier_freight_code,l_generic_carrier;
1398 CLOSE c_get_carrier_info;
1399
1400 g_carrier_freight_codes(p_carrier_id):=l_carrier_freight_code;
1401 g_carrier_generic_flags(p_carrier_id):=l_generic_carrier;
1402
1403 x_generic:=l_generic_carrier;
1404 x_carrier_freight_code:=l_carrier_freight_code;
1405
1406 END IF;
1407
1408
1409
1410
1411
1412 IF l_debug_on THEN
1413 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1414 WSH_DEBUG_SV.pop(l_module_name);
1415 END IF;
1416 --
1417 EXCEPTION
1418
1419 WHEN FND_API.G_EXC_ERROR THEN
1420 x_return_status := FND_API.G_RET_STS_ERROR;
1421
1422 IF l_debug_on THEN
1423 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1424 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1425 END IF;
1426
1427 WHEN others THEN
1428 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1429 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Get_Carrier_Info',l_module_name);
1430
1431 IF l_debug_on THEN
1432 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1433 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1434 END IF;
1435 --
1436
1437 END Get_Carrier_Info;
1438
1439
1440 PROCEDURE Sort(
1441 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_TAB,
1442 x_return_status OUT NOCOPY VARCHAR2)
1443 IS
1444
1445 CURSOR c_sort(c_tab IN WSH_OTM_RIQ_SORT_TAB)
1446 IS
1447 SELECT position
1448 FROM TABLE (CAST (c_tab AS WSH_OTM_RIQ_SORT_TAB))
1449 ORDER BY numberValue;
1450
1451 i NUMBER;
1452 j NUMBER;
1453 l_count NUMBER;
1454
1455 l_sort_tab WSH_OTM_RIQ_SORT_TAB;
1456 l_sort_rec WSH_OTM_RIQ_SORT_REC;
1457 l_index_tab WSH_NEW_DELIVERY_ACTIONS.TableNumbers;
1458 l_out_tab FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_TAB;
1459
1460 l_return_status VARCHAR2(1);
1461 l_debug_on BOOLEAN;
1462 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Sort';
1463 --
1464 BEGIN
1465 --
1466 --
1467 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1468 --
1469 IF l_debug_on IS NULL
1470 THEN
1471 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1472 END IF;
1473 --
1474 IF l_debug_on THEN
1475 WSH_DEBUG_SV.push(l_module_name);
1476 END IF;
1477 --
1478 x_return_status := FND_API.G_RET_STS_SUCCESS;
1479
1480 l_count:=x_source_header_rates_tab.COUNT;
1481
1482 IF (l_count > 0)
1483 THEN
1484
1485 l_sort_rec:= NEW WSH_OTM_RIQ_SORT_REC();
1486 l_sort_tab:= NEW WSH_OTM_RIQ_SORT_TAB(l_sort_rec);
1487 j:=l_sort_tab.FIRST;
1488 l_sort_tab.EXTEND(l_count-1,j);
1489
1490 --l_sort_tab now has l_count unitialized records
1491 i:=x_source_header_rates_tab.FIRST;
1492 WHILE(i IS NOT NULL)
1493 LOOP
1494 l_sort_tab(j).position:=i;
1495 l_sort_tab(j).numberValue:=x_source_header_rates_tab(i).price;
1496
1497 j:=l_sort_tab.NEXT(j);
1498 i:=x_source_header_rates_tab.NEXT(i);
1499 END LOOP;
1500
1501 OPEN c_sort(l_sort_tab);
1502 FETCH c_sort BULK COLLECT INTO l_index_tab;
1503 CLOSE c_sort;
1504
1505 --Use sorted index to copy sorted results
1506 i:=l_index_tab.FIRST;
1507 WHILE (i IS NOT NULL)
1508 LOOP
1509 l_out_tab(i):=x_source_header_rates_tab(l_index_tab(i));
1510
1511 i:=l_index_tab.NEXT(i);
1512 END LOOP;
1513
1514 x_source_header_rates_tab.DELETE;
1515
1516 --Copy to output
1517 x_source_header_rates_tab:=l_out_tab;
1518
1519 l_out_tab.DELETE;
1520 l_index_tab.DELETE;
1521 l_sort_tab.DELETE;
1522
1523 END IF;
1524 --
1525 IF l_debug_on THEN
1526 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1527 WSH_DEBUG_SV.pop(l_module_name);
1528 END IF;
1529 --
1530 EXCEPTION
1531
1532 WHEN FND_API.G_EXC_ERROR THEN
1533 x_return_status := FND_API.G_RET_STS_ERROR;
1534
1535 IF l_debug_on THEN
1536 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1537 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1538 END IF;
1539
1540 WHEN others THEN
1541 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1542 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Sort',l_module_name);
1543 --
1544 IF l_debug_on THEN
1545 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1546 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1547 END IF;
1548 --
1549 END Sort;
1550
1551
1552 PROCEDURE Validate_Transit_Time(
1553 x_transit_time IN OUT NOCOPY NUMBER,
1554 x_transit_time_uom IN OUT NOCOPY VARCHAR2,
1555 x_return_status OUT NOCOPY VARCHAR2)
1556 IS
1557
1558
1559 l_transit_time NUMBER;
1560 l_transit_time_uom VARCHAR(3);
1561
1562 l_return_status VARCHAR2(1);
1563 l_debug_on BOOLEAN;
1564 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Validate_Transit_Time';
1565
1566 BEGIN
1567
1568 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1569 IF l_debug_on IS NULL
1570 THEN
1571 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1572 END IF;
1573
1574 IF l_debug_on THEN
1575 WSH_DEBUG_SV.push(l_module_name);
1576 END IF;
1577
1578 x_return_status := FND_API.G_RET_STS_SUCCESS;
1579
1580 l_transit_time:=NULL;
1581 l_transit_time_uom:=NULL;
1582
1583 IF ((x_transit_time IS NOT NULL) AND (x_transit_time_uom IS NOT NULL) AND (g_global_time_class IS NOT NULL))
1584 THEN
1585
1586
1587 Get_OTM_To_EBS_UOM(
1588 p_uom=>x_transit_time_uom,
1589 p_uom_class=>g_global_time_class,
1590 x_uom=>l_transit_time_uom,
1591 x_return_status=>l_return_status);
1592 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1593 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
1594 IF l_debug_on
1595 THEN
1596
1597 WSH_DEBUG_SV.log(l_module_name,'Get_OTM_To_EBS_UOM Failed');
1598 END IF;
1599 raise FND_API.G_EXC_ERROR;
1600 END IF;
1601
1602
1603 IF (l_transit_time_uom IS NOT NULL)
1604 THEN
1605 IF l_debug_on THEN
1606 WSH_DEBUG_SV.log(l_module_name,'time UOM Code valid',x_transit_time_uom);
1607 END IF;
1608
1609 l_transit_time:=x_transit_time;
1610 ELSE
1611 IF l_debug_on THEN
1612 WSH_DEBUG_SV.log(l_module_name,'Invalid time UOM Code',x_transit_time_uom);
1613 END IF;
1614 l_transit_time:=NULL;
1615 l_transit_time_uom:=NULL;
1616
1617 END IF;
1618
1619 END IF;
1620
1621 x_transit_time:=l_transit_time;
1622 x_transit_time_uom:=l_transit_time_uom;
1623
1624
1625
1626 IF l_debug_on THEN
1627 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1628 WSH_DEBUG_SV.pop(l_module_name);
1629 END IF;
1630 --
1631 EXCEPTION
1632
1633 WHEN FND_API.G_EXC_ERROR THEN
1634 x_return_status := FND_API.G_RET_STS_ERROR;
1635
1636 IF l_debug_on THEN
1637 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1638 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1639 END IF;
1640
1641 WHEN others THEN
1642 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1643 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Validate_Transit_Time',l_module_name);
1644
1645 IF l_debug_on THEN
1646 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1647 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1648 END IF;
1649 --
1650
1651 END Validate_Transit_Time;
1652
1653
1654 PROCEDURE Get_Ship_Method_Code(
1655 p_org_id IN NUMBER,
1656 p_carrier_id IN NUMBER,
1657 p_mode IN VARCHAR2,
1658 p_service_level IN VARCHAR2,
1659 x_ship_method_code OUT NOCOPY VARCHAR2,
1660 x_return_status OUT NOCOPY VARCHAR2)
1661
1662 IS
1663
1664 CURSOR c_get_ship_method_code (c_carrier_id VARCHAR2, c_mode_of_trans VARCHAR2, c_service_level VARCHAR2, c_org_id NUMBER) IS
1665 SELECT a.ship_method_code
1666 FROM wsh_carrier_services a, wsh_org_carrier_services b
1667 WHERE a.carrier_service_id = b.carrier_service_id
1668 AND b.organization_id = c_org_id
1669 AND b.enabled_flag = 'Y'
1670 AND a.enabled_flag = 'Y'
1671 AND a.mode_of_transport = c_mode_of_trans
1672 AND UPPER(a.service_level) = UPPER(c_service_level)
1673 AND a.carrier_id = c_carrier_id;
1674
1675 l_ship_method_code VARCHAR2(30);
1676 l_return_status VARCHAR2(1);
1677 l_debug_on BOOLEAN;
1678 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Ship_Method_Code';
1679
1680 BEGIN
1681
1682 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1683 IF l_debug_on IS NULL
1684 THEN
1685 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1686 END IF;
1687
1688 IF l_debug_on THEN
1689 WSH_DEBUG_SV.push(l_module_name);
1690 END IF;
1691
1692 x_return_status := FND_API.G_RET_STS_SUCCESS;
1693
1694 l_ship_method_code:=NULL;
1695 IF(p_carrier_id IS NOT NULL AND p_mode IS NOT NULL AND p_service_level IS NOT NULL)
1696 THEN
1697
1698 OPEN c_get_ship_method_code(p_carrier_id,p_mode,p_service_level,p_org_id);
1699 FETCH c_get_ship_method_code INTO l_ship_method_code;
1700 CLOSE c_get_ship_method_code;
1701 END IF;
1702 IF(l_ship_method_code IS NULL)
1703 THEN
1704 x_ship_method_code:=NULL;
1705 x_return_status := FND_API.G_RET_STS_ERROR;
1706 ELSE
1707 x_ship_method_code:=l_ship_method_code;
1708 END IF;
1709
1710
1711
1712 IF l_debug_on THEN
1713 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1714 WSH_DEBUG_SV.pop(l_module_name);
1715 END IF;
1716 --
1717 EXCEPTION
1718
1719 WHEN FND_API.G_EXC_ERROR THEN
1720 x_return_status := FND_API.G_RET_STS_ERROR;
1721
1722 IF l_debug_on THEN
1723 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1724 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
1725 END IF;
1726
1727 WHEN others THEN
1728 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1729 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Get_Ship_Method_Code',l_module_name);
1730
1731 IF l_debug_on THEN
1732 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1733 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1734 END IF;
1735 --
1736
1737 END Get_Ship_Method_Code;
1738
1739
1740 --For the source location pass in the location id and the org id
1741 --For the destination location pass in location id and the customer id
1742
1743 PROCEDURE Get_Location_Info(
1744 p_location_id IN NUMBER,
1745 p_org_id IN NUMBER,
1746 p_customer_id IN NUMBER,
1747 x_location_xid IN OUT NOCOPY VARCHAR2,
1748 x_location_domain IN OUT NOCOPY VARCHAR2,
1749 x_postal_code IN OUT NOCOPY VARCHAR2,
1750 x_city IN OUT NOCOPY VARCHAR2,
1751 x_province_code IN OUT NOCOPY VARCHAR2,
1752 x_country_code IN OUT NOCOPY VARCHAR2,
1753 x_country_domain IN OUT NOCOPY VARCHAR2,
1754 x_corporation_xid IN OUT NOCOPY VARCHAR2,
1755 x_corporation_domain IN OUT NOCOPY VARCHAR2,
1756 x_return_status OUT NOCOPY VARCHAR2) IS
1757
1758
1759 l_postal_code VARCHAR2(60);
1760 l_city VARCHAR2(100);
1761 l_state VARCHAR2(100);
1762 l_country_code VARCHAR2(3);
1763 l_state_code VARCHAR2(30);
1764
1765 CURSOR c_get_state_code(p_location_id IN NUMBER)
1766 IS
1767
1768 SELECT r.state_code
1769 FROM WSH_REGION_LOCATIONS rl ,WSH_REGIONS r
1770 WHERE rl.location_id=p_location_id
1771 AND rl.region_type=g_state_region_type
1772 AND rl.region_id = r.region_id;
1773 /*
1774 CURSOR c_get_loc_info(p_location_id IN NUMBER)
1775 IS
1776 SELECT wl.postal_code,wl.city,wl.state, ft.iso_territory_code
1777 FROM WSH_LOCATIONS wl, FND_TERRITORIES ft
1778 WHERE
1779 wl.wsh_location_id=p_location_id
1780 AND wl.country=ft.territory_code;
1781 */
1782 CURSOR c_get_cus_loc_info(p_location_id IN NUMBER)
1783 IS
1784 SELECT hz.postal_code, hz.city,hz.state,ft.iso_territory_code
1785 FROM HZ_LOCATIONS hz,FND_TERRITORIES ft
1786 WHERE
1787 hz.location_id=p_location_id
1788 AND hz.country=ft.territory_code;
1789
1790
1791 CURSOR c_get_org_loc_info(p_location_id IN NUMBER)
1792 IS
1793 SELECT hr.postal_code, hr.town_or_city,hr.region_2,ft.iso_territory_code
1794 FROM HR_LOCATIONS_ALL hr,FND_TERRITORIES ft
1795 WHERE
1796 hr.location_id=p_location_id
1797 AND hr.country=ft.territory_code;
1798
1799 l_return_status VARCHAR2(1);
1800 l_debug_on BOOLEAN;
1801 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Location_Info';
1802
1803 BEGIN
1804
1805 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1806 IF l_debug_on IS NULL
1807 THEN
1808 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1809 END IF;
1810
1811 IF l_debug_on THEN
1812 WSH_DEBUG_SV.push(l_module_name);
1813 WSH_DEBUG_SV.log(l_module_name, 'p_location_id', p_location_id);
1814 WSH_DEBUG_SV.log(l_module_name, 'p_org_id', p_org_id);
1815 WSH_DEBUG_SV.log(l_module_name, 'p_customer_id', p_customer_id);
1816 END IF;
1817
1818 x_return_status := FND_API.G_RET_STS_SUCCESS;
1819
1820
1821 IF((p_location_id IS NOT NULL) AND ((p_org_id IS NOT NULL) OR (p_customer_id IS NOT NULL)))
1822 THEN
1823
1824 IF (p_org_id IS NOT NULL)
1825 THEN
1826 OPEN c_get_org_loc_info(p_location_id);
1827 FETCH c_get_org_loc_info INTO l_postal_code,l_city,l_state,l_country_code;
1828 CLOSE c_get_org_loc_info;
1829
1830 ELSE
1831
1832 OPEN c_get_cus_loc_info(p_location_id);
1833 FETCH c_get_cus_loc_info INTO l_postal_code,l_city,l_state,l_country_code;
1834 CLOSE c_get_cus_loc_info;
1835
1836 END IF;
1837
1838 OPEN c_get_state_code(p_location_id);
1839 FETCH c_get_state_code INTO l_state_code;
1840 CLOSE c_get_state_code;
1841
1842 IF l_debug_on
1843 THEN
1844 WSH_DEBUG_SV.log(l_module_name,'l_postal_code',l_postal_code);
1845 WSH_DEBUG_SV.log(l_module_name,'l_city',l_city);
1846 WSH_DEBUG_SV.log(l_module_name,'l_state',l_state);
1847 WSH_DEBUG_SV.log(l_module_name,'l_country_code',l_country_code);
1848
1849 END IF;
1850
1851 IF NOT(l_postal_code IS NULL AND l_city IS NULL AND l_state IS NULL AND l_state_code IS NULL AND l_country_code IS NULL)
1852 THEN
1853
1854
1855 IF(p_org_id IS NOT NULL)
1856 THEN
1857 x_location_domain:=g_domain_name;
1858 x_location_xid:=G_ORG_LOCATION_PREFIX||p_org_id||G_LOCATION_SEPERATOR||p_location_id;
1859 --x_location_xid:='ORG-'||p_location_id;
1860 x_corporation_domain:=g_domain_name;
1861 x_corporation_xid:=G_ORG_LOCATION_PREFIX||p_org_id;
1862
1863 ELSIF(p_customer_id IS NOT NULL)
1864 THEN
1865 x_location_domain:=g_domain_name;
1866 x_location_xid:=G_CUST_LOCATION_PREFIX||p_customer_id||G_LOCATION_SEPERATOR||p_location_id;
1867 x_corporation_domain:=g_domain_name;
1868 x_corporation_xid:=G_CUST_LOCATION_PREFIX||p_customer_id;
1869 END IF;
1870
1871 x_postal_code:=l_postal_code;
1872 x_city:=l_city;
1873
1874 IF ((l_state_code IS NOT NULL) AND (LENGTH(l_state_code)= 2))
1875 THEN
1876 x_province_code:=l_state_code;
1877 ELSIF ((l_state IS NOT NULL) AND (LENGTH(l_state)= 2))
1878 THEN
1879 x_province_code:=l_state;
1880 ELSE
1881 x_province_code:=NULL;
1882 END IF;
1883
1884 IF(l_country_code IS NOT NULL)
1885 THEN
1886 --Country code domain is PUBLIC do not specify
1887 --x_country_domain:=g_domain_name;
1888 x_country_code:=l_country_code;
1889
1890 END IF;
1891
1892
1893
1894
1895 ELSE
1896 x_return_status := FND_API.G_RET_STS_ERROR;
1897 END IF;
1898 ELSE
1899 x_return_status := FND_API.G_RET_STS_ERROR;
1900 END IF;
1901
1902
1903 IF l_debug_on THEN
1904 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
1905 WSH_DEBUG_SV.pop(l_module_name);
1906 END IF;
1907 --
1908 EXCEPTION
1909 WHEN others THEN
1910 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1911 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Get_Location_Info',l_module_name);
1912 --
1913 IF l_debug_on THEN
1914 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1915 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1916 END IF;
1917 --
1918 END Get_Location_Info;
1919
1920
1921
1922 PROCEDURE Format_Line_Input_For_Xml(
1923 x_source_header_xml_rec IN OUT NOCOPY WSH_OTM_RIQ_HEADER_REC,
1924 p_source_header_rec IN FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_REC,
1925 p_source_line_tab IN FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_TAB,
1926 p_source_type IN VARCHAR2,
1927 p_action IN VARCHAR2,
1928 x_source_line_tab IN OUT NOCOPY WSH_OTM_RIQ_LINE_TAB,
1929 x_return_status OUT NOCOPY VARCHAR2) IS
1930
1931
1932 CURSOR c_item_dimensions(c_inv_item_id IN NUMBER,c_org_id IN NUMBER) IS
1933 SELECT unit_length,unit_width,unit_height,dimension_uom_code
1934 FROM mtl_system_items
1935 WHERE inventory_item_id = c_inv_item_id AND organization_id=c_org_id;
1936
1937 l_line_rec WSH_OTM_RIQ_LINE_REC;
1938 i NUMBER;
1939 j NUMBER;
1940 l_length NUMBER;
1941 l_width NUMBER;
1942 l_height NUMBER;
1943 l_dim_uom VARCHAR2(30);
1944 l_otm_dim_uom VARCHAR2(30);
1945
1946 l_line_tab WSH_UTIL_CORE.Id_Tab_Type;
1947 l_all_lines_tab WSH_UTIL_CORE.Id_Tab_Type;
1948 l_dummy NUMBER;
1949
1950 l_debug_on BOOLEAN;
1951 l_return_status VARCHAR2(1);
1952 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Format_Line_Input_For_Xml';
1953
1954 BEGIN
1955 --
1956 --
1957 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1958 --
1959 IF l_debug_on IS NULL
1960 THEN
1961 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1962 END IF;
1963 --
1964 IF l_debug_on THEN
1965 WSH_DEBUG_SV.push(l_module_name);
1966 END IF;
1967 --
1968 x_return_status := FND_API.G_RET_STS_SUCCESS;
1969
1970 l_line_rec:= NEW WSH_OTM_RIQ_LINE_REC();
1971 x_source_line_tab.DELETE;
1972
1973 i:=p_source_line_tab.FIRST;
1974 j:=i;
1975 WHILE(i IS NOT NULL)
1976 LOOP
1977 IF((p_source_line_tab(i).consolidation_id IS NOT NULL )
1978 AND (p_source_header_rec.consolidation_id IS NOT NULL)
1979 AND (p_source_line_tab(i).consolidation_id = p_source_header_rec.consolidation_id))
1980 THEN
1981 x_source_line_tab.EXTEND;
1982 x_source_line_tab(j):=NEW WSH_OTM_RIQ_LINE_REC();
1983
1984 --Dummy Value for LineNumber
1985 x_source_line_tab(j).LineNumber:=j;
1986
1987 --Weight
1988 x_source_line_tab(j).Weight:=p_source_line_tab(i).weight;
1989 IF (x_source_line_tab(j).Weight IS NULL)
1990 THEN
1991 x_source_line_tab(j).Weight:=0;
1992 END IF;
1993
1994 Get_EBS_To_OTM_UOM(
1995 p_uom=>p_source_line_tab(i).weight_uom_code,
1996 x_uom=>x_source_line_tab(j).WeightUOM,
1997 x_return_status=>l_return_status);
1998 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
1999 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2000 IF l_debug_on
2001 THEN
2002 WSH_DEBUG_SV.log(l_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
2003 END IF;
2004 raise FND_API.G_EXC_ERROR;
2005 END IF;
2006
2007
2008 --UOM Domain is public do not specify
2009 --x_source_line_tab(j).WeightUOMDomain:=g_domain_name;
2010
2011 --Volume
2012 x_source_line_tab(j).Volume:=p_source_line_tab(i).volume;
2013 IF (x_source_line_tab(j).Volume IS NULL)
2014 THEN
2015 x_source_line_tab(j).Volume:=0;
2016 END IF;
2017
2018
2019 Get_EBS_To_OTM_UOM(
2020 p_uom=>p_source_line_tab(i).volume_uom_code,
2021 x_uom=>x_source_line_tab(j).VolumeUOM,
2022 x_return_status=>l_return_status);
2023 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
2024 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2025 IF l_debug_on
2026 THEN
2027 WSH_DEBUG_SV.log(l_module_name,'Get_EBS_To_OTM_UOM Volume Failed');
2028 END IF;
2029 raise FND_API.G_EXC_ERROR;
2030 END IF;
2031
2032
2033 --UOM Domain is public do not specify
2034 --x_source_line_tab(j).VolumeUOMDomain:=g_domain_name;
2035
2036 --Item
2037
2038 IF ((p_source_line_tab(i).inventory_item_id IS NOT NULL)
2039 AND (p_source_line_tab(i).ship_from_org_id IS NOT NULL))
2040 THEN
2041 x_source_line_tab(j).ItemId:=p_source_line_tab(i).ship_from_org_id||'-'||
2042 p_source_line_tab(i).inventory_item_id;
2043 x_source_line_tab(j).ItemOrgId:=p_source_line_tab(i).ship_from_org_id;
2044
2045 END IF;
2046
2047
2048 IF (x_source_line_tab(j).ItemId IS NOT NULL)
2049 THEN
2050 x_source_line_tab(j).ItemDomain:=x_source_header_xml_rec.domain;
2051 END IF;
2052
2053 --Item Dimensions
2054
2055 IF((x_source_line_tab(j).ItemId IS NOT NULL) AND (x_source_line_tab(j).ItemOrgId IS NOT NULL))
2056 THEN
2057 l_length:=NULL;
2058 l_width:=NULL;
2059 l_height:=NULL;
2060 l_dim_uom:=NULL;
2061 l_otm_dim_uom:=NULL;
2062
2063 OPEN c_item_dimensions(p_source_line_tab(i).inventory_item_id,p_source_line_tab(i).ship_from_org_id);
2064 FETCH c_item_dimensions INTO l_length,l_width,l_height,l_dim_uom;
2065 CLOSE c_item_dimensions;
2066
2067
2068 Get_EBS_To_OTM_UOM(
2069 p_uom=>l_dim_uom,
2070 x_uom=>l_otm_dim_uom,
2071 x_return_status=>l_return_status);
2072 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
2073 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2074 IF l_debug_on
2075 THEN
2076 WSH_DEBUG_SV.log(l_module_name,'Get_EBS_To_OTM_UOM Dim Failed');
2077 END IF;
2078 raise FND_API.G_EXC_ERROR;
2079 END IF;
2080 IF l_debug_on
2081 THEN
2082 WSH_DEBUG_SV.log(l_module_name,'Length',l_length);
2083 WSH_DEBUG_SV.log(l_module_name,'Width',l_width);
2084 WSH_DEBUG_SV.log(l_module_name,'Height',l_height);
2085 WSH_DEBUG_SV.log(l_module_name,'DimUOM',l_dim_uom);
2086 WSH_DEBUG_SV.log(l_module_name,'OTMDimUOM',l_otm_dim_uom);
2087 END IF;
2088
2089 IF ((l_otm_dim_uom IS NOT NULL) AND ((l_length IS NOT NULL) AND (l_width IS NOT NULL) AND (l_height IS NOT NULL)))
2090 THEN
2091 x_source_line_tab(j).Length:=l_length;
2092 x_source_line_tab(j).Width:=l_width;
2093 x_source_line_tab(j).Height:=l_height;
2094
2095 x_source_line_tab(j).LengthUOM:=l_otm_dim_uom;
2096 x_source_line_tab(j).WidthUOM:=l_otm_dim_uom;
2097 x_source_line_tab(j).HeightUOM:=l_otm_dim_uom;
2098
2099 --UOM Domain is public do not specify
2100 --x_source_line_tab(j).LengthUOMDomain:=g_domain_name;
2101 --x_source_line_tab(j).WidthUOMDomain:=g_domain_name;
2102 --x_source_line_tab(j).HeightUOMDomain:=g_domain_name;
2103
2104
2105 END IF;
2106
2107 END IF;
2108
2109 -- Custom hook to override line level attributes
2110 l_line_tab(1) := p_source_line_tab(i).source_line_id;
2111 l_all_lines_tab(l_all_lines_tab.COUNT + 1) := l_line_tab(1);
2112 IF l_debug_on THEN
2113 WSH_DEBUG_SV.logmsg(l_module_name,'Line id : '||l_line_tab(1)
2114 ||' x_source_line_tab(j).Weight : '||x_source_line_tab(j).Weight
2115 ||' x_source_line_tab(j).Volume : '||x_source_line_tab(j).Volume
2116 ||' x_source_line_tab(j).Length : '||x_source_line_tab(j).Length
2117 ||' x_source_line_tab(j).Height : '||x_source_line_tab(j).Height
2118 ||' x_source_line_tab(j).Width : '||x_source_line_tab(j).Width);
2119 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_CUSTOM_PUB.Override_RIQ_XML_Attributes') ;
2120 END IF;
2121 WSH_CUSTOM_PUB.Override_RIQ_XML_Attributes (
2122 p_line_id_tab => l_line_tab,
2123 x_weight => x_source_line_tab(j).Weight,
2124 x_volume => x_source_line_tab(j).Volume,
2125 x_length => x_source_line_tab(j).Length,
2126 x_height => x_source_line_tab(j).Height,
2127 x_width => x_source_line_tab(j).Width,
2128 x_return_status =>l_return_status );
2129 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2130 IF l_debug_on THEN
2131 WSH_DEBUG_SV.log(l_module_name,'WSH_CUSTOM_PUB.Override_RIQ_XML_Attribute Failed');
2132 END IF;
2133 RAISE FND_API.G_EXC_ERROR;
2134 END IF;
2135 IF l_debug_on THEN
2136 WSH_DEBUG_SV.logmsg(l_module_name,' x_source_line_tab(j).Weight : '
2137 ||x_source_line_tab(j).Weight
2138 ||' x_source_line_tab(j).Volume : '||x_source_line_tab(j).Volume
2139 ||' x_source_line_tab(j).Length : '||x_source_line_tab(j).Length
2140 ||' x_source_line_tab(j).Height : '||x_source_line_tab(j).Height
2141 ||' x_source_line_tab(j).Width : '||x_source_line_tab(j).Width);
2142 END IF;
2143
2144 -- Checking if all dimensions and OTM dimension UOM are present
2145 IF l_otm_dim_uom IS NULL OR x_source_line_tab(j).Length IS NULL
2146 OR x_source_line_tab(j).Height IS NULL OR x_source_line_tab(j).Width IS NULL THEN
2147 x_source_line_tab(j).Length := NULL;
2148 x_source_line_tab(j).Height := NULL;
2149 x_source_line_tab(j).Width := NULL;
2150 x_source_line_tab(j).LengthUOM := NULL;
2151 x_source_line_tab(j).WidthUOM := NULL;
2152 x_source_line_tab(j).HeightUOM := NULL;
2153 END IF;
2154
2155 j:=j+1;
2156 END IF;
2157 i:=p_source_line_tab.NEXT(i);
2158 END LOOP;
2159
2160 -- Custom hook to override header level attributes
2161 l_dummy := NULL;
2162 IF l_debug_on THEN
2163 WSH_DEBUG_SV.logmsg(l_module_name,'No of Order Lines : '||l_all_lines_tab.COUNT
2164 ||' x_source_header_xml_rec.TotalWeight : '||x_source_header_xml_rec.TotalWeight
2165 ||' x_source_header_xml_rec.TotalVolume : '||x_source_header_xml_rec.TotalVolume);
2166 WSH_DEBUG_SV.logmsg(l_module_name,'Calling WSH_CUSTOM_PUB.Override_RIQ_XML_Attributes');
2167 END IF;
2168 WSH_CUSTOM_PUB.Override_RIQ_XML_Attributes (
2169 p_line_id_tab => l_all_lines_tab,
2170 x_weight => x_source_header_xml_rec.TotalWeight,
2171 x_volume => x_source_header_xml_rec.TotalVolume,
2172 x_length => l_dummy,
2173 x_height => l_dummy,
2174 x_width => l_dummy,
2175 x_return_status => l_return_status );
2176 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
2177 IF l_debug_on THEN
2178 WSH_DEBUG_SV.log(l_module_name,'WSH_CUSTOM_PUB.Override_RIQ_XML_Attribute Failed');
2179 END IF;
2180 RAISE FND_API.G_EXC_ERROR;
2181 END IF;
2182 IF l_debug_on THEN
2183 WSH_DEBUG_SV.logmsg(l_module_name, ' x_source_header_xml_rec.TotalWeight : '
2184 ||x_source_header_xml_rec.TotalWeight
2185 ||' x_source_header_xml_rec.TotalVolume : '||x_source_header_xml_rec.TotalVolume);
2186 END IF;
2187
2188 IF l_debug_on THEN
2189 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
2190 WSH_DEBUG_SV.pop(l_module_name);
2191 END IF;
2192 --
2193 EXCEPTION
2194
2195 WHEN FND_API.G_EXC_ERROR THEN
2196 x_return_status := FND_API.G_RET_STS_ERROR;
2197
2198 IF l_debug_on THEN
2199 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2200 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2201 END IF;
2202
2203 WHEN others THEN
2204 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2205 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Format_Line_Input_For_Xml',l_module_name);
2206
2207 IF l_debug_on THEN
2208 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2209 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2210 END IF;
2211 --
2212
2213 END Format_Line_Input_For_Xml;
2214
2215
2216 PROCEDURE Format_Header_Input_For_Xml(
2217 p_source_header_tab IN FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_TAB,
2218 p_source_type IN VARCHAR2,
2219 p_action IN VARCHAR2,
2220 x_source_header_tab IN OUT NOCOPY WSH_OTM_RIQ_HEADER_TAB,
2221 x_return_status OUT NOCOPY VARCHAR2) IS
2222
2223
2224 CURSOR c_SM_Components(c_shp_mthd_cd VARCHAR2)
2225 IS
2226 SELECT carrier_id, mode_of_transport,service_level
2227 FROM wsh_carrier_services
2228 WHERE ship_method_code = c_shp_mthd_cd;
2229
2230
2231 l_carrier_freight_code VARCHAR2(30);
2232 l_generic_carrier VARCHAR2(1);
2233 i NUMBER;
2234 l_carrier_id NUMBER;
2235 l_mode VARCHAR2(30);
2236 l_service_level VARCHAR2(30);
2237
2238 l_header_rec WSH_OTM_RIQ_HEADER_REC;
2239 /*Bug7329859*/
2240 j NUMBER;
2241 l_customer_id NUMBER;
2242 /*Bug7329859*/
2243 l_return_status VARCHAR2(1);
2244 l_debug_on BOOLEAN;
2245 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FORMAT_HEADER_INPUT_FOR_XML';
2246
2247 BEGIN
2248 --
2249 --
2250 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2251 --
2252 IF l_debug_on IS NULL
2253 THEN
2254 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2255 END IF;
2256 --
2257 IF l_debug_on THEN
2258 WSH_DEBUG_SV.push(l_module_name);
2259 END IF;
2260 --
2261 x_return_status := FND_API.G_RET_STS_SUCCESS;
2262
2263 x_source_header_tab.DELETE;
2264 x_source_header_tab.EXTEND(p_source_header_tab.COUNT);
2265 l_header_rec:=NEW WSH_OTM_RIQ_HEADER_REC();
2266
2267 --Initialize fields that will be common to all header records
2268
2269 --ShipUnitCount hardcoded to 1
2270 l_header_rec.ShipUnitCount:=1;
2271
2272 --Dummy value for ShipUnitGid
2273 l_header_rec.ShipUnitGid:='Q';
2274
2275
2276 IF (p_action = 'GET_RATE_CHOICE')
2277 THEN
2278 l_header_rec.RIQRequestType:='AllOptions';
2279 l_header_rec.Perspective:='B';
2280 l_header_rec.UseRIQRoute:='N';
2281
2282 ELSIF(p_action = 'R')
2283 THEN
2284 l_header_rec.RIQRequestType:='LowestCost';
2285 l_header_rec.Perspective:='B';
2286 l_header_rec.UseRIQRoute:='N';
2287
2288 ELSIF(p_action = 'C')
2289 THEN
2290 l_header_rec.GetFreightCost:='N';
2291
2292 ELSIF(p_action = 'B')
2293 THEN
2294 l_header_rec.GetFreightCost:='Y';
2295
2296 END IF;
2297
2298
2299
2300
2301 l_header_rec.domain:=g_domain_name;
2302 --Transmission Type
2303
2304 l_header_rec.TransmissionType:='QUERY';
2305
2306 IF ((g_user_name IS NULL) OR (g_password IS NULL))
2307 THEN
2308 IF l_debug_on
2309 THEN
2310 WSH_DEBUG_SV.log(l_module_name,'User/Pwd is null');
2311 WSH_DEBUG_SV.log(l_module_name,'user',g_user_name);
2312 WSH_DEBUG_SV.log(l_module_name,'pwd',g_password);
2313 END IF;
2314
2315 RAISE FND_API.G_EXC_ERROR;
2316
2317 END IF;
2318
2319 l_header_rec.UserName:=g_user_name;
2320 l_header_rec.Passwd:=g_password;
2321
2322
2323
2324 --Use the same index as header
2325 i:=p_source_header_tab.FIRST;
2326 WHILE(i IS NOT NULL)
2327 LOOP
2328 l_carrier_id:=p_source_header_tab(i).carrier_id;
2329 l_mode:=p_source_header_tab(i).mode_of_transport;
2330 l_service_level:=p_source_header_tab(i).service_level;
2331
2332
2333 IF (p_source_header_tab(i).ship_method_code is not null)
2334 AND (l_carrier_id is null OR l_mode is null OR l_service_level is null)
2335 THEN
2336 OPEN c_SM_Components(p_source_header_tab(i).ship_method_code);
2337 FETCH c_SM_Components INTO l_carrier_id,l_mode,l_service_level;
2338 CLOSE c_SM_Components;
2339
2340
2341
2342 END IF;
2343
2344
2345 x_source_header_tab(i):=l_header_rec;
2346
2347 --Source Location
2348
2349 Get_Location_Info(
2350 p_location_id=>p_source_header_tab(i).ship_from_location_id,
2351 p_org_id=>p_source_header_tab(i).ship_from_org_id,
2352 p_customer_id=>NULL,
2353 x_location_xid=>x_source_header_tab(i).SourceLocationId,
2354 x_location_domain=>x_source_header_tab(i).SourceLocationDomain,
2355 x_postal_code=>x_source_header_tab(i).SourcePostalCode,
2356 x_city=>x_source_header_tab(i).SourceCity,
2357 x_province_code=>x_source_header_tab(i).SourceProvinceCode,
2358 x_country_code=>x_source_header_tab(i).SourceCountryCode,
2359 x_country_domain=>x_source_header_tab(i).SourceCountryDomain,
2360 x_corporation_xid=>x_source_header_tab(i).SourceCorporationId,
2361 x_corporation_domain=>x_source_header_tab(i).SourceCorporationDomain,
2362 x_return_status=>l_return_status);
2363
2364 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
2365 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2366 IF l_debug_on
2367 THEN
2368
2369 WSH_DEBUG_SV.log(l_module_name,'Source Location Get Info Failed');
2370 END IF;
2371 raise FND_API.G_EXC_ERROR;
2372 END IF;
2373
2374
2375
2376 --Destination Location
2377 /*Bug7329859 If customer_id is NULL in header rec then getting it from line rec with same consolidation id */
2378 l_customer_id := p_source_header_tab(i).customer_id;
2379 IF l_customer_id IS NULL THEN
2380 j:=g_source_line_tab_temp.FIRST;
2381 WHILE(j IS NOT NULL)
2382 LOOP
2383 IF((g_source_line_tab_temp(j).consolidation_id IS NOT NULL )
2384 AND (p_source_header_tab(i).consolidation_id IS NOT NULL)
2385 AND (g_source_line_tab_temp(j).consolidation_id = p_source_header_tab(i).consolidation_id))
2386 THEN
2387 l_customer_id := g_source_line_tab_temp(j).customer_id;
2388 EXIT;
2389 END IF;
2390 j:=g_source_line_tab_temp.NEXT(j);
2391 END LOOP;
2392 END IF;
2393 /*Bug7329859*/
2394
2395 Get_Location_Info(
2396 p_location_id=>p_source_header_tab(i).ship_to_location_id,
2397 p_org_id=>NULL,
2398 p_customer_id=>l_customer_id, --Bug7329859
2399 x_location_xid=>x_source_header_tab(i).DestLocationId,
2400 x_location_domain=>x_source_header_tab(i).DestLocationDomain,
2401 x_postal_code=>x_source_header_tab(i).DestPostalCode,
2402 x_city=>x_source_header_tab(i).DestCity,
2403 x_province_code=>x_source_header_tab(i).DestProvinceCode,
2404 x_country_code=>x_source_header_tab(i).DestCountryCode,
2405 x_country_domain=>x_source_header_tab(i).DestCountryDomain,
2406 x_corporation_xid=>x_source_header_tab(i).DestCorporationId,
2407 x_corporation_domain=>x_source_header_tab(i).DestCorporationDomain,
2408 x_return_status=>l_return_status);
2409
2410 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
2411 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2412
2413 IF l_debug_on
2414 THEN
2415
2416 WSH_DEBUG_SV.log(l_module_name,'Dest Location Get Info Failed');
2417 END IF;
2418
2419 raise FND_API.G_EXC_ERROR;
2420 END IF;
2421
2422 --Dates
2423 IF (p_source_header_tab(i).ship_date IS NOT NULL)
2424 THEN
2425 x_source_header_tab(i).AvailableByDate:=TO_CHAR(p_source_header_tab(i).ship_date,'YYYYMMDDHH24MISS');
2426 IF (g_timezone_code IS NOT NULL)
2427 THEN
2428 x_source_header_tab(i).AvailableByTimezoneCode:=g_timezone_code;
2429 --TZ Domain is PUBLIC domain do not specify
2430 --x_source_header_tab(i).AvailableByTZDomain:=g_domain_name;
2431 END IF;
2432
2433 END IF;
2434
2435 IF (p_source_header_tab(i).arrival_date IS NOT NULL)
2436 THEN
2437 x_source_header_tab(i).DeliveryByDate:=TO_CHAR(p_source_header_tab(i).arrival_date,'YYYYMMDDHH24MISS');
2438 IF( g_timezone_code IS NOT NULL)
2439 THEN
2440
2441 x_source_header_tab(i).DeliveryByTimezoneCode:=g_timezone_code;
2442 --TZ Domain is PUBLIC domain do not specify
2443 --x_source_header_tab(i).DeliveryByTZDomain:=g_domain_name;
2444 END IF;
2445 END IF;
2446
2447
2448
2449 --Carrier
2450
2451 --Handle Generic carrier
2452 IF ((p_action = 'GET_RATE_CHOICE') OR (p_action = 'R'))
2453 THEN
2454 IF (l_carrier_id is not null)
2455 THEN
2456
2457 Get_Carrier_Info(
2458 p_carrier_id=>l_carrier_id,
2459 x_generic=>l_generic_carrier,
2460 x_carrier_freight_code=>l_carrier_freight_code,
2461 x_return_status=>l_return_status);
2462
2463 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
2464 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2465 IF l_debug_on
2466 THEN
2467 WSH_DEBUG_SV.log(l_module_name,'Dest Location Get Info Failed');
2468 END IF;
2469 raise FND_API.G_EXC_ERROR;
2470 END IF;
2471
2472
2473 IF (l_generic_carrier = 'Y')
2474 THEN
2475 l_carrier_id:=NULL;
2476 END IF;
2477
2478 END IF;
2479 END IF;
2480
2481 IF(l_carrier_id IS NOT NULL)
2482 THEN
2483 x_source_header_tab(i).ServiceProviderId:=G_CARRIER_PREFIX||l_carrier_id;
2484
2485 END IF;
2486
2487 IF (x_source_header_tab(i).ServiceProviderId IS NOT NULL)
2488 THEN
2489 x_source_header_tab(i).ServiceProviderDomain:=g_domain_name;
2490 END IF;
2491
2492 --Mode
2493 IF(l_mode IS NOT NULL)
2494 THEN
2495 x_source_header_tab(i).ModeOfTransportCode:=l_mode;
2496 x_source_header_tab(i).ModeOfTransportDomain:=g_domain_name;
2497
2498 END IF;
2499
2500 --Service Level
2501 IF (l_service_level IS NOT NULL)
2502 THEN
2503 x_source_header_tab(i).RateServiceDomain:=g_domain_name;
2504 x_source_header_tab(i).RateServiceCode:=l_service_level;
2505
2506 END IF;
2507
2508
2509 --Freight Terms
2510 IF(p_source_header_tab(i).freight_terms IS NOT NULL)
2511 THEN
2512
2513 x_source_header_tab(i).PaymentMethodDomain:=g_domain_name;
2514 x_source_header_tab(i).PaymentMethodCode:=p_source_header_tab(i).freight_terms;
2515
2516 END IF;
2517
2518
2519 --Total Weight
2520 x_source_header_tab(i).TotalWeight:=p_source_header_tab(i).total_weight;
2521 IF (x_source_header_tab(i).TotalWeight IS NULL)
2522 THEN
2523 x_source_header_tab(i).TotalWeight:=0;
2524 END IF;
2525
2526
2527 Get_EBS_To_OTM_UOM(
2528 p_uom=>p_source_header_tab(i).weight_uom_code,
2529 x_uom=>x_source_header_tab(i).TotalWeightUOM,
2530 x_return_status=>l_return_status);
2531 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
2532 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2533 IF l_debug_on
2534 THEN
2535 WSH_DEBUG_SV.log(l_module_name,'Get_EBS_To_OTM_UOM Weight Failed');
2536 END IF;
2537 raise FND_API.G_EXC_ERROR;
2538 END IF;
2539
2540
2541 --UOM Domain is public do not specify
2542 --x_source_header_tab(i).TotalWeightUOMDomain:=g_domain_name;
2543
2544 --Total Volume
2545 x_source_header_tab(i).TotalVolume:=p_source_header_tab(i).total_volume;
2546 IF (x_source_header_tab(i).TotalVolume IS NULL)
2547 THEN
2548 x_source_header_tab(i).TotalVolume:=0;
2549 END IF;
2550
2551
2552 Get_EBS_To_OTM_UOM(
2553 p_uom=>p_source_header_tab(i).volume_uom_code,
2554 x_uom=>x_source_header_tab(i).TotalVolumeUOM,
2555 x_return_status=>l_return_status);
2556 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
2557 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
2558 IF l_debug_on
2559 THEN
2560 WSH_DEBUG_SV.log(l_module_name,'Get_EBS_To_OTM_UOM Volume Failed');
2561 END IF;
2562 raise FND_API.G_EXC_ERROR;
2563 END IF;
2564
2565
2566 --UOM Domain is public do not specify
2567 --x_source_header_tab(i).TotalVolumeUOMDomain:=g_domain_name;
2568
2569
2570
2571 i:=p_source_header_tab.NEXT(i);
2572 END LOOP;
2573
2574 --
2575 IF l_debug_on THEN
2576 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
2577 WSH_DEBUG_SV.pop(l_module_name);
2578 END IF;
2579 --
2580 EXCEPTION
2581
2582 WHEN FND_API.G_EXC_ERROR THEN
2583 x_return_status := FND_API.G_RET_STS_ERROR;
2584
2585 IF l_debug_on THEN
2586 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2587 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
2588 END IF;
2589
2590 WHEN others THEN
2591 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2592 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.FORMAT_HEADER_INPUT_FOR_XML',l_module_name);
2593
2594 IF l_debug_on THEN
2595 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2596 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2597 END IF;
2598 --
2599
2600 END FORMAT_HEADER_INPUT_FOR_XML;
2601
2602
2603
2604
2605
2606
2607 PROCEDURE Create_RIQ_XML(
2608 p_source_line_tab IN WSH_OTM_RIQ_LINE_TAB,
2609 p_source_header_rec IN WSH_OTM_RIQ_HEADER_REC,
2610 p_source_type IN VARCHAR2,
2611 p_action IN VARCHAR2,
2612 x_xml_input IN OUT NOCOPY XMLTYPE,
2613 x_return_status OUT NOCOPY VARCHAR2) IS
2614
2615 l_return_status VARCHAR2(1);
2616 l_debug_on BOOLEAN;
2617 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Create_RIQ_XML';
2618
2619
2620 CURSOR c_Choose_SM_XML(c_header_rec IN WSH_OTM_RIQ_HEADER_REC,c_line_tab IN WSH_OTM_RIQ_LINE_TAB)
2621 IS
2622 SELECT XMLELEMENT("Transmission",
2623 XMLFOREST(
2624 XMLFOREST(
2625 c_header_rec.TransmissionType AS "TransmissionType",
2626 c_header_rec.UserName AS "UserName",
2627 c_header_rec.Passwd AS "Password"
2628 ) AS "TransmissionHeader",
2629
2630 XMLELEMENT("GLogXMLElement",
2631 XMLELEMENT("RemoteQuery",
2632 XMLELEMENT("RIQQuery",
2633 XMLConcat(
2634
2635 XMLELEMENT("RIQRequestType",
2636 c_header_rec.RIQRequestType
2637 ),
2638 XMLElement("SourceAddress",
2639 XMLForest(
2640 XMLFOREST(
2641 c_header_rec.SourceCity AS "City",
2642 c_header_rec.SourceProvinceCode AS "ProvinceCode",
2643 c_header_rec.SourcePostalCode AS "PostalCode",
2644 XMLFOREST(
2645 XMLFOREST(
2646 c_header_rec.SourceCountryDomain AS "DomainName",
2647 c_header_rec.SourceCountryCode AS "Xid"
2648 ) AS "Gid"
2649 ) AS "CountryCode3Gid",
2650 XMLELEMENT("Gid",
2651 XMLFOREST(
2652 c_header_rec.SourceLocationDomain AS "DomainName",
2653 c_header_rec.SourceLocationId AS "Xid"
2654 )
2655 ) AS "LocationGid"
2656 ) AS "MileageAddress",
2657 XMLFOREST(
2658 XMLFOREST(
2659 c_header_rec.SourceLocationDomain AS "DomainName",
2660 c_header_rec.SourceCorporationId AS "Xid"
2661 ) AS "Gid"
2662 ) AS "CorporationGid"
2663 )
2664 ),
2665 XMLElement("DestAddress",
2666 XMLForest(
2667
2668 XMLFOREST(
2669 c_header_rec.DestCity AS "City",
2670 c_header_rec.DestProvinceCode AS "ProvinceCode",
2671 c_header_rec.DestPostalCode AS "PostalCode",
2672 XMLFOREST(
2673 XMLFOREST(
2674 c_header_rec.DestCountryDomain AS "DomainName",
2675 c_header_rec.DestCountryCode AS "Xid"
2676 ) AS "Gid"
2677 ) AS "CountryCode3Gid",
2678
2679 XMLELEMENT("Gid",
2680 XMLFOREST(
2681 c_header_rec.DestLocationDomain AS "DomainName",
2682 c_header_rec.DestLocationId AS "Xid"
2683 )
2684
2685 ) AS "LocationGid"
2686 ) AS "MileageAddress",
2687 XMLFOREST(
2688 XMLFOREST(
2689 c_header_rec.DestLocationDomain AS "DomainName",
2690 c_header_rec.DestCorporationId AS "Xid"
2691 ) AS "Gid"
2692 ) AS "CorporationGid"
2693 )
2694 ),
2695 XMLFOREST(
2696 XMLFOREST(
2697 c_header_rec.AvailableByDate AS "AvailableDate",
2698 XMLFOREST(
2699 XMLFOREST(
2700 c_header_rec.AvailableByTZDomain AS "DomainName",
2701 c_header_rec.AvailableByTimeZoneCode AS "Xid"
2702 ) AS "Gid"
2703 ) AS "TimeZoneGid"
2704 ) AS "AvailableBy"
2705 ),
2706 XMLFOREST(
2707 XMLFOREST(
2708 c_header_rec.DeliveryByDate AS "DeliveryByDate",
2709
2710 XMLFOREST(
2711 XMLFOREST(
2712 c_header_rec.DeliveryByTZDomain AS "DomainName",
2713 c_header_rec.DeliveryByTimeZoneCode AS "Xid"
2714 ) AS "Gid"
2715 ) AS "TimeZoneGid"
2716 ) AS "DeliveryBy"
2717 ),
2718 XMLELEMENT("Perspective",
2719 c_header_rec.Perspective
2720 ),
2721 XMLELEMENT("UseRIQRoute",
2722 c_header_rec.UseRIQRoute
2723 ),
2724 XMLAgg(
2725 XMLELEMENT("ShipUnit",
2726 XMLFOREST(
2727
2728 XMLFOREST(
2729 XMLFOREST(
2730 c_header_rec.ShipUnitGid AS "Xid"
2731 ) AS "Gid"
2732
2733 ) AS "ShipUnitGid",
2734
2735 XMLFOREST(
2736 XMLFOREST(
2737 e.weight AS "WeightValue",
2738 XMLFOREST(
2739 XMLFOREST(
2740 e.weightUOMDomain AS "DomainName",
2741 e.weightUOM AS "Xid"
2742 ) AS "Gid"
2743 ) AS "WeightUOMGid"
2744
2745 ) AS "Weight",
2746 XMLFOREST(
2747 e.volume AS "VolumeValue",
2748 XMLFOREST(
2749 XMLFOREST(
2750 e.volumeUOMDomain AS "DomainName",
2751 e.volumeUOM AS "Xid"
2752 ) AS "Gid"
2753 ) AS "VolumeUOMGid"
2754 ) AS "Volume"
2755 ) AS "WeightVolume",
2756 XMLFOREST(
2757 XMLFOREST(
2758 e.Length AS "LengthValue",
2759 XMLFOREST(
2760 XMLFOREST(
2761 e.lengthUOMDomain AS "DomainName",
2762 e.lengthUOM AS "Xid"
2763 ) AS "Gid"
2764 ) AS "LengthUOMGid"
2765 ) AS "Length",
2766 XMLFOREST(
2767 e.width AS "WidthValue",
2768 XMLForest(
2769 XMLFOREST(
2770 e.widthUOMDomain AS "DomainName",
2771 e.widthUOM AS "Xid"
2772 ) AS "Gid"
2773
2774 ) AS "WidthUOMGid"
2775 ) AS "Width",
2776 XMLFOREST(
2777 e.height AS "HeightValue",
2778 XMLFOREST(
2779 XMLFOREST(
2780 e.heightUOMDomain AS "DomainName",
2781 e.heightUOM AS "Xid"
2782 ) AS "Gid"
2783 ) AS "HeightUOMGid"
2784 ) AS "Height"
2785 ) AS "LengthWidthHeight",
2786
2787 XMLFOREST(
2788 XMLFOREST(
2789 XMLFOREST(
2790 XMLFOREST(
2791 e.ItemDomain AS "DomainName",
2792 e.ItemId AS "Xid"
2793 ) AS "Gid"
2794 ) AS "PackagedItemGid"
2795 ) AS "PackagedItemRef",
2796 e.LineNumber AS "LineNumber"
2797 ) AS "ShipUnitContent",
2798 c_header_rec.ShipUnitCount AS "ShipUnitCount"
2799 )
2800 )
2801 )
2802 )
2803 )
2804 )
2805 ) AS "TransmissionBody"
2806 )
2807 )
2808 FROM TABLE(CAST(c_line_tab AS WSH_OTM_RIQ_LINE_TAB )) e;
2809
2810
2811
2812
2813 CURSOR c_Get_Freight_Rates_XML(c_header_rec IN WSH_OTM_RIQ_HEADER_REC,c_line_tab IN WSH_OTM_RIQ_LINE_TAB)
2814 IS
2815
2816 SELECT
2817 XMLELEMENT("Transmission",
2818 XMLFOREST(
2819 XMLFOREST(
2820 c_header_rec.TransmissionType AS "TransmissionType",
2821 c_header_rec.UserName AS "UserName",
2822 c_header_rec.Passwd AS "Password"
2823 ) AS "TransmissionHeader",
2824
2825 XMLELEMENT("GLogXMLElement",
2826 XMLELEMENT("RemoteQuery",
2827 XMLELEMENT("RIQQuery",
2828 XMLConcat(
2829
2830 XMLELEMENT("RIQRequestType",
2831 c_header_rec.RIQRequestType
2832 ),
2833 XMLElement("SourceAddress",
2834 XMLForest(
2835 XMLFOREST(
2836 c_header_rec.SourceCity AS "City",
2837 c_header_rec.SourceProvinceCode AS "ProvinceCode",
2838 c_header_rec.SourcePostalCode AS "PostalCode",
2839 XMLFOREST(
2840 XMLFOREST(
2841 c_header_rec.SourceCountryDomain AS "DomainName",
2842 c_header_rec.SourceCountryCode AS "Xid"
2843 ) AS "Gid"
2844 ) AS "CountryCode3Gid",
2845 XMLELEMENT("Gid",
2846 XMLFOREST(
2847 c_header_rec.SourceLocationDomain AS "DomainName",
2848 c_header_rec.SourceLocationId AS "Xid"
2849 )
2850 ) AS "LocationGid"
2851 ) AS "MileageAddress",
2852 XMLFOREST(
2853 XMLFOREST(
2854 c_header_rec.SourceLocationDomain AS "DomainName",
2855 c_header_rec.SourceCorporationId AS "Xid"
2856 ) AS "Gid"
2857 ) AS "CorporationGid"
2858 )
2859 ),
2860 XMLElement("DestAddress",
2861 XMLForest(
2862
2863 XMLFOREST(
2864 c_header_rec.DestCity AS "City",
2865 c_header_rec.DestProvinceCode AS "ProvinceCode",
2866 c_header_rec.DestPostalCode AS "PostalCode",
2867 XMLFOREST(
2868 XMLFOREST(
2869 c_header_rec.DestCountryDomain AS "DomainName",
2870 c_header_rec.DestCountryCode AS "Xid"
2871 ) AS "Gid"
2872 ) AS "CountryCode3Gid",
2873 XMLELEMENT("Gid",
2874 XMLFOREST(
2875 c_header_rec.DestLocationDomain AS "DomainName",
2876 c_header_rec.DestLocationId AS "Xid"
2877 )
2878
2879 ) AS "LocationGid"
2880 ) AS "MileageAddress",
2881 XMLFOREST(
2882 XMLFOREST(
2883 c_header_rec.DestLocationDomain AS "DomainName",
2884 c_header_rec.DestCorporationId AS "Xid"
2885 ) AS "Gid"
2886 ) AS "CorporationGid"
2887 )
2888 ),
2889
2890 XMLFOREST(
2891 XMLFOREST(
2892 XMLFOREST(
2893 c_header_rec.ModeOfTransportDomain AS "DomainName",
2894 c_header_rec.ModeOfTransportCode AS "Xid"
2895
2896 ) AS "Gid"
2897 ) AS "TransportModeGid"
2898 ),
2899 XMLFOREST(
2900 XMLFOREST(
2901 XMLFOREST(
2902 c_header_rec.ServiceProviderDomain AS "DomainName",
2903 c_header_rec.ServiceProviderId AS "Xid"
2904
2905 ) AS "Gid"
2906 ) AS "ServiceProviderGid"
2907 ),
2908 XMLFOREST(
2909 XMLFOREST(
2910 XMLFOREST(
2911 c_header_rec.RateServiceDomain AS "DomainName",
2912 c_header_rec.RateServiceCode AS "Xid"
2913
2914 ) AS "Gid"
2915 ) AS "RateServiceGid"
2916 ),
2917 XMLFOREST(
2918 XMLFOREST(
2919 c_header_rec.AvailableByDate AS "AvailableDate",
2920 XMLFOREST(
2921 XMLFOREST(
2922 c_header_rec.AvailableByTZDomain AS "DomainName",
2923 c_header_rec.AvailableByTimeZoneCode AS "Xid"
2924 ) AS "Gid"
2925 ) AS "TimeZoneGid"
2926 ) AS "AvailableBy"
2927 ),
2928 XMLFOREST(
2929 XMLFOREST(
2930 c_header_rec.DeliveryByDate AS "DeliveryByDate",
2931
2932 XMLFOREST(
2933 XMLFOREST(
2934 c_header_rec.DeliveryByTZDomain AS "DomainName",
2935 c_header_rec.DeliveryByTimeZoneCode AS "Xid"
2936 ) AS "Gid"
2937 ) AS "TimeZoneGid"
2938 ) AS "DeliveryBy"
2939 ),
2940 XMLELEMENT("Perspective",
2941 c_header_rec.Perspective
2942 ),
2943 XMLELEMENT("UseRIQRoute",
2944 c_header_rec.UseRIQRoute
2945 ),
2946
2947 XMLAgg(
2948 XMLELEMENT("ShipUnit",
2949 XMLFOREST(
2950
2951 XMLFOREST(
2952 XMLFOREST(
2953 c_header_rec.ShipUnitGid AS "Xid"
2954 ) AS "Gid"
2955
2956 ) AS "ShipUnitGid",
2957
2958 XMLFOREST(
2959 XMLFOREST(
2960 e.weight AS "WeightValue",
2961 XMLFOREST(
2962 XMLFOREST(
2963 e.weightUOMDomain AS "DomainName",
2964 e.weightUOM AS "Xid"
2965 ) AS "Gid"
2966 ) AS "WeightUOMGid"
2967
2968 ) AS "Weight",
2969 XMLFOREST(
2970 e.volume AS "VolumeValue",
2971 XMLFOREST(
2972 XMLFOREST(
2973 e.volumeUOMDomain AS "DomainName",
2974 e.volumeUOM AS "Xid"
2975 ) AS "Gid"
2976 ) AS "VolumeUOMGid"
2977 ) AS "Volume"
2978 ) AS "WeightVolume",
2979 XMLFOREST(
2980 XMLFOREST(
2981 e.Length AS "LengthValue",
2982 XMLFOREST(
2983 XMLFOREST(
2984 e.lengthUOMDomain AS "DomainName",
2985 e.lengthUOM AS "Xid"
2986 ) AS "Gid"
2987 ) AS "LengthUOMGid"
2988 ) AS "Length",
2989 XMLFOREST(
2990 e.width AS "WidthValue",
2991 XMLForest(
2992 XMLFOREST(
2993 e.widthUOMDomain AS "DomainName",
2994 e.widthUOM AS "Xid"
2995 ) AS "Gid"
2996
2997 ) AS "WidthUOMGid"
2998 ) AS "Width",
2999 XMLFOREST(
3000 e.height AS "HeightValue",
3001 XMLFOREST(
3002 XMLFOREST(
3003 e.heightUOMDomain AS "DomainName",
3004 e.heightUOM AS "Xid"
3005 ) AS "Gid"
3006 ) AS "HeightUOMGid"
3007 ) AS "Height"
3008 ) AS "LengthWidthHeight",
3009 XMLFOREST(
3010 XMLFOREST(
3011 XMLFOREST(
3012 XMLFOREST(
3013 e.ItemDomain AS "DomainName",
3014 e.ItemId AS "Xid"
3015 ) AS "Gid"
3016 ) AS "PackagedItemGid"
3017 ) AS "PackagedItemRef",
3018 e.LineNumber AS "LineNumber"
3019 ) AS "ShipUnitContent",
3020
3021 c_header_rec.ShipUnitCount AS "ShipUnitCount"
3022 )
3023 )
3024 )
3025 )
3026 )
3027 )
3028 ) AS "TransmissionBody"
3029 )
3030 )
3031 FROM TABLE(CAST(c_line_tab AS WSH_OTM_RIQ_LINE_TAB )) e;
3032
3033
3034
3035 CURSOR c_Get_SM_XML(c_header_rec IN WSH_OTM_RIQ_HEADER_REC,c_line_tab IN WSH_OTM_RIQ_LINE_TAB)
3036 IS
3037 SELECT
3038 XMLELEMENT("Transmission",
3039 XMLFOREST(
3040 XMLFOREST(
3041 c_header_rec.TransmissionType AS "TransmissionType",
3042 c_header_rec.UserName AS "UserName",
3043 c_header_rec.Passwd AS "Password"
3044 ) AS "TransmissionHeader",
3045
3046 XMLELEMENT("GLogXMLElement",
3047 XMLELEMENT("RemoteQuery",
3048 XMLELEMENT("OrderRoutingRuleQuery",
3049 XMLConcat(
3050
3051 XMLElement("SourceAddress",
3052 XMLForest(
3053 XMLFOREST(
3054 c_header_rec.SourceCity AS "City",
3055 c_header_rec.SourceProvinceCode AS "ProvinceCode",
3056 c_header_rec.SourcePostalCode AS "PostalCode",
3057 XMLFOREST(
3058 XMLFOREST(
3059 c_header_rec.SourceCountryDomain AS "DomainName",
3060 c_header_rec.SourceCountryCode AS "Xid"
3061 ) AS "Gid"
3062 ) AS "CountryCode3Gid",
3063
3064 XMLELEMENT("Gid",
3065 XMLFOREST(
3066 c_header_rec.SourceLocationDomain AS "DomainName",
3067 c_header_rec.SourceLocationId AS "Xid"
3068 )
3069 ) AS "LocationGid"
3070 ) AS "MileageAddress",
3071 XMLFOREST(
3072 XMLFOREST(
3073 c_header_rec.SourceLocationDomain AS "DomainName",
3074 c_header_rec.SourceCorporationId AS "Xid"
3075 ) AS "Gid"
3076 ) AS "CorporationGid"
3077 )
3078 ),
3079 XMLElement("DestAddress",
3080 XMLForest(
3081
3082 XMLFOREST(
3083 c_header_rec.DestCity AS "City",
3084 c_header_rec.DestProvinceCode AS "ProvinceCode",
3085 c_header_rec.DestPostalCode AS "PostalCode",
3086 XMLFOREST(
3087 XMLFOREST(
3088 c_header_rec.DestCountryDomain AS "DomainName",
3089 c_header_rec.DestCountryCode AS "Xid"
3090 ) AS "Gid"
3091 ) AS "CountryCode3Gid",
3092 XMLELEMENT("Gid",
3093 XMLFOREST(
3094 c_header_rec.DestLocationDomain AS "DomainName",
3095 c_header_rec.DestLocationId AS "Xid"
3096 )
3097
3098 ) AS "LocationGid"
3099 ) AS "MileageAddress",
3100 XMLFOREST(
3101 XMLFOREST(
3102 c_header_rec.DestLocationDomain AS "DomainName",
3103 c_header_rec.DestCorporationId AS "Xid"
3104 ) AS "Gid"
3105 ) AS "CorporationGid"
3106 )
3107 ),
3108
3109 XMLFOREST(
3110 c_header_rec.AvailableByDate AS "EstDepartureDate"
3111 ),
3112
3113 XMLFOREST(
3114 c_header_rec.DeliveryByDate AS "EstArrivalDate"
3115 ),
3116 XMLFOREST(
3117 XMLFOREST(
3118 c_header_rec.TotalWeight AS "WeightValue",
3119 XMLFOREST(
3120 XMLFOREST(
3121 c_header_rec.TotalWeightUOMDomain AS "DomainName",
3122 c_header_rec.TotalWeightUOM AS "Xid"
3123 ) AS "Gid"
3124 ) AS "WeightUOMGid"
3125
3126 ) AS "Weight"
3127 ),
3128 XMLFOREST(
3129 XMLFOREST(
3130 c_header_rec.TotalVolume AS "VolumeValue",
3131 XMLFOREST(
3132 XMLFOREST(
3133 c_header_rec.TotalVolumeUOMDomain AS "DomainName",
3134 c_header_rec.TotalVolumeUOM AS "Xid"
3135 ) AS "Gid"
3136 ) AS "VolumeUOMGid"
3137 ) AS "Volume"
3138 ),
3139 XMLFOREST(
3140 XMLFOREST(
3141 XMLFOREST(
3142 c_header_rec.PaymentMethodDomain AS "DomainName",
3143 c_header_rec.PaymentMethodCode AS "Xid"
3144 ) AS "Gid"
3145 ) AS "PaymentMethodCodeGid"
3146 )
3147 )
3148 )
3149 )
3150 ) AS "TransmissionBody"
3151 )
3152 )
3153 FROM TABLE(CAST(c_line_tab AS WSH_OTM_RIQ_LINE_TAB )) e;
3154
3155
3156
3157 BEGIN
3158
3159 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3160 IF l_debug_on IS NULL
3161 THEN
3162 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3163 END IF;
3164 IF l_debug_on THEN
3165 WSH_DEBUG_SV.push(l_module_name);
3166 END IF;
3167 x_return_status := FND_API.G_RET_STS_SUCCESS;
3168
3169 IF (p_action IS NOT NULL)
3170 THEN
3171 IF (p_action = 'GET_RATE_CHOICE')
3172 THEN
3173 OPEN c_Choose_SM_XML(p_source_header_rec,p_source_line_tab);
3174 FETCH c_Choose_SM_XML INTO x_xml_input;
3175 CLOSE c_Choose_SM_XML;
3176
3177 ELSIF ((p_action = 'C') OR (p_action = 'B'))
3178 THEN
3179
3180 OPEN c_Get_SM_XML(p_source_header_rec,p_source_line_tab);
3181 FETCH c_Get_SM_XML INTO x_xml_input;
3182 CLOSE c_Get_SM_XML;
3183
3184
3185 ELSIF (p_action ='R')
3186 THEN
3187
3188 OPEN c_Get_Freight_Rates_XML(p_source_header_rec,p_source_line_tab);
3189 FETCH c_Get_Freight_Rates_XML INTO x_xml_input;
3190 CLOSE c_Get_Freight_Rates_XML;
3191
3192 END IF;
3193
3194
3195 END IF;
3196
3197
3198
3199
3200 IF l_debug_on THEN
3201 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3202 WSH_DEBUG_SV.pop(l_module_name);
3203 END IF;
3204 --
3205 EXCEPTION
3206
3207 WHEN FND_API.G_EXC_ERROR THEN
3208 x_return_status := FND_API.G_RET_STS_ERROR;
3209
3210 IF l_debug_on THEN
3211 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3212 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3213 END IF;
3214
3215 WHEN others THEN
3216 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3217 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Create_RIQ_XML',l_module_name);
3218
3219 IF l_debug_on THEN
3220 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3221 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3222 END IF;
3223 --
3224
3225 END Create_RIQ_XML;
3226
3227 PROCEDURE Fetch_XML_Match(
3228 p_xml IN XMLTYPE,
3229 p_xpath IN VARCHAR2,
3230 x_xml_seq OUT NOCOPY XMLSequenceType,
3231 x_return_status OUT NOCOPY VARCHAR2)
3232 IS
3233 l_return_status VARCHAR2(1);
3234 l_debug_on BOOLEAN;
3235 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Fetch_XML_Match';
3236
3237
3238 CURSOR c_extract(C_XML_IN IN XMLTYPE,C_XPATH IN VARCHAR2)
3239 IS
3240 SELECT XMLSEQUENCE(EXTRACT(C_XML_IN,C_XPATH,g_xml_namespace_map)) XML
3241 FROM DUAL;
3242
3243 BEGIN
3244 --
3245 --
3246 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3247 --
3248 IF l_debug_on IS NULL
3249 THEN
3250 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3251 END IF;
3252 --
3253 IF l_debug_on THEN
3254 WSH_DEBUG_SV.push(l_module_name);
3255 END IF;
3256 --
3257 x_return_status := FND_API.G_RET_STS_SUCCESS;
3258
3259 OPEN c_extract(p_xml,p_xpath);
3260 FETCH c_extract INTO x_xml_seq;
3261 CLOSE c_extract;
3262
3263
3264
3265 --
3266 IF l_debug_on THEN
3267 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3268 WSH_DEBUG_SV.pop(l_module_name);
3269 END IF;
3270 --
3271 EXCEPTION
3272
3273 WHEN FND_API.G_EXC_ERROR THEN
3274 x_return_status := FND_API.G_RET_STS_ERROR;
3275
3276 IF l_debug_on THEN
3277 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3278 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3279 END IF;
3280
3281 WHEN others THEN
3282 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3283 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Fetch_XML_Match',l_module_name);
3284 --
3285 IF l_debug_on THEN
3286 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3287 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3288 END IF;
3289 --
3290 END Fetch_XML_Match;
3291
3292
3293
3294 PROCEDURE Check_Response_Status(
3295 p_xpath_prefix IN VARCHAR2,
3296 p_xml_output IN XMLTYPE,
3297 x_return_status OUT NOCOPY VARCHAR2)
3298 IS
3299
3300 i NUMBER;
3301 l_xml_seq XMLSequenceType;
3302 l_status_code VARCHAR2(30);
3303 l_status_message VARCHAR2(32000);
3304 l_log_message VARCHAR2(32000);
3305 l_tmp_xml XMLTYPE;
3306 l_return_status VARCHAR2(1);
3307 l_debug_on BOOLEAN;
3308 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Check_Response_Status';
3309
3310 l_prev_xml_namespace_map VARCHAR2(200) := NULL;
3311
3312 BEGIN
3313 --
3314 --
3315 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3316 --
3317 IF l_debug_on IS NULL
3318 THEN
3319 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3320 END IF;
3321 --
3322 IF l_debug_on THEN
3323 WSH_DEBUG_SV.push(l_module_name);
3324 END IF;
3325 --
3326 x_return_status := FND_API.G_RET_STS_SUCCESS;
3327
3328 IF l_debug_on
3329 THEN
3330 WSH_DEBUG_SV.log(l_module_name,'prefix:',p_xpath_prefix);
3331 WSH_DEBUG_SV.log(l_module_name,'code:',G_XPATH_STATUS_CODE);
3332 END IF;
3333
3334
3335 l_status_code:=NULL;
3336 l_tmp_xml:=p_xml_output.extract(p_xpath_prefix||G_XPATH_STATUS_CODE,g_xml_namespace_map);
3337
3338 IF (l_tmp_xml IS NULL)
3339 THEN
3340 --Try again incase message is using other NS(TODO)
3341 l_prev_xml_namespace_map := g_xml_namespace_map;
3342 g_xml_namespace_map:=G_OTM_NS_MAP;
3343 l_tmp_xml:=p_xml_output.extract(p_xpath_prefix||G_XPATH_STATUS_CODE,g_xml_namespace_map);
3344
3345 END IF;
3346
3347 IF (l_tmp_xml IS NULL)
3348 THEN
3349 --Try again incase message did not contain name space
3350 g_xml_namespace_map:=NULL;
3351 l_tmp_xml:=p_xml_output.extract(p_xpath_prefix||G_XPATH_STATUS_CODE,g_xml_namespace_map);
3352
3353 END IF;
3354
3355 IF (l_tmp_xml IS NOT NULL)
3356 THEN
3357 l_status_code:=l_tmp_xml.getStringVal();
3358 IF l_debug_on
3359 THEN
3360 WSH_DEBUG_SV.log(l_module_name,'OTM Status code',l_status_code);
3361 END IF;
3362 ELSE
3363 -- Now g_xml_namespace_map is NULL, so revert it back to the original
3364 g_xml_namespace_map := l_prev_xml_namespace_map;
3365 END IF;
3366
3367 IF ((l_status_code IS NULL) OR (l_status_code <> 'SUCCESS'))
3368 THEN
3369
3370 x_return_status:=FND_API.G_RET_STS_ERROR;
3371
3372 IF l_debug_on
3373 THEN
3374 --Log status code
3375 WSH_DEBUG_SV.log(l_module_name,'OTM Status code',l_status_code,WSH_DEBUG_SV.C_EXCEP_LEVEL);
3376
3377 l_status_message:=NULL;
3378 l_tmp_xml:=p_xml_output.extract(p_xpath_prefix||G_XPATH_STATUS_MESSAGE,g_xml_namespace_map);
3379 IF (l_tmp_xml IS NULL) THEN
3380 --Try again incase message is using other NS(TODO)
3381 g_xml_namespace_map:=G_OTM_NS_MAP;
3382 l_tmp_xml:=p_xml_output.extract(p_xpath_prefix||G_XPATH_STATUS_MESSAGE,g_xml_namespace_map);
3383 END IF;
3384
3385 IF (l_tmp_xml IS NULL) THEN
3386 --Try again incase message did not contain name space
3387 g_xml_namespace_map:=NULL;
3388 l_tmp_xml:=p_xml_output.extract(p_xpath_prefix||G_XPATH_STATUS_MESSAGE,g_xml_namespace_map);
3389 END IF;
3390 IF (l_tmp_xml IS NOT NULL)
3391 THEN
3392 l_status_message:=l_tmp_xml.getStringVal();
3393 --Log status message
3394 WSH_DEBUG_SV.log(l_module_name,'OTM Status message',l_status_message,WSH_DEBUG_SV.C_EXCEP_LEVEL);
3395 ELSE
3396 -- Now g_xml_namespace_map is NULL, so revert it back to the original
3397 g_xml_namespace_map := l_prev_xml_namespace_map;
3398 END IF;
3399
3400 Fetch_XML_Match(
3401 p_xml=>p_xml_output,
3402 p_xpath=>p_xpath_prefix||G_XPATH_MESSAGES,
3403 x_xml_seq=>l_xml_seq,
3404 x_return_status=>l_return_status);
3405
3406 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
3407 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
3408 IF l_debug_on
3409 THEN
3410
3411 WSH_DEBUG_SV.log(l_module_name,'Fetch_XML_Match Failed');
3412 END IF;
3413
3414 raise FND_API.G_EXC_ERROR;
3415 END IF;
3416
3417 i:=l_xml_seq.FIRST;
3418 WHILE (i IS NOT NULL)
3419 LOOP
3420 l_log_message:=l_xml_seq(i).getStringVal();
3421 --Log status message
3422 WSH_DEBUG_SV.log(l_module_name,'OTM message',l_log_message,WSH_DEBUG_SV.C_EXCEP_LEVEL);
3423
3424 i:=l_xml_seq.NEXT(i);
3425
3426 END LOOP;
3427 END IF;
3428
3429 END IF;
3430
3431
3432 --
3433 IF l_debug_on THEN
3434 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3435 WSH_DEBUG_SV.pop(l_module_name);
3436 END IF;
3437 --
3438 EXCEPTION
3439
3440 WHEN FND_API.G_EXC_ERROR THEN
3441 x_return_status := FND_API.G_RET_STS_ERROR;
3442
3443 IF l_debug_on THEN
3444 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3445 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3446 END IF;
3447
3448 WHEN others THEN
3449 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3450 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Check_Response_Status',l_module_name);
3451 --
3452 IF l_debug_on THEN
3453 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3454 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3455 END IF;
3456 --
3457 END Check_Response_Status;
3458
3459 PROCEDURE Extract_Single_Cost(
3460 p_xml IN XMLTYPE,
3461 p_xpath_prefix IN VARCHAR2,
3462 p_currency IN VARCHAR2,
3463 x_cost OUT NOCOPY NUMBER,
3464 x_return_status OUT NOCOPY VARCHAR2) IS
3465
3466 l_negative VARCHAR2(1);
3467 l_tmp_xml XMLTYPE;
3468 l_return_status VARCHAR2(1);
3469 l_debug_on BOOLEAN;
3470 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Extract_Single_Cost';
3471
3472 l_cost_summary VARCHAR2(200);
3473 l_currency VARCHAR2(30);
3474 l_rate NUMBER;
3475
3476 l_currency_conversion_type VARCHAR2(30) := NULL;
3477
3478 -- Bug 5886042
3479 -- currency conversion_type in the error message should be
3480 -- user_conversion_type
3481
3482 l_user_conv_type VARCHAR2(30) := NULL;
3483
3484 CURSOR c_get_user_conv_type(p_curr_conv_type varchar2) IS
3485 SELECT user_conversion_type
3486 FROM gl_daily_conversion_types
3487 WHERE conversion_type = p_curr_conv_type;
3488 --
3489
3490 BEGIN
3491 --
3492 --
3493 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3494 --
3495 IF l_debug_on IS NULL
3496 THEN
3497 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3498 END IF;
3499 --
3500 IF l_debug_on THEN
3501 WSH_DEBUG_SV.push(l_module_name);
3502 END IF;
3503 --
3504
3505 x_cost := NULL;
3506 x_return_status := FND_API.G_RET_STS_SUCCESS;
3507
3508 l_cost_summary:=NULL;
3509 l_currency:=NULL;
3510
3511 l_tmp_xml:=p_xml.extract(p_xpath_prefix||G_XPATH_COST_SUMMARY,g_xml_namespace_map);
3512 IF (l_tmp_xml IS NOT NULL)
3513 THEN
3514 l_cost_summary:=l_tmp_xml.getStringVal();
3515 END IF;
3516
3517 l_tmp_xml:=p_xml.extract(p_xpath_prefix||G_XPATH_COST_SUMMARY_CURRENCY,g_xml_namespace_map);
3518 IF (l_tmp_xml IS NOT NULL)
3519 THEN
3520 l_currency:=l_tmp_xml.getStringVal();
3521 END IF;
3522
3523 l_rate:=Convert_To_Number(l_cost_summary);
3524
3525 IF l_debug_on THEN
3526 WSH_DEBUG_SV.log(l_module_name,'l_currency',l_currency);
3527 WSH_DEBUG_SV.log(l_module_name,'p_currency',p_currency);
3528 WSH_DEBUG_SV.log(l_module_name,'l_cost_summary',l_cost_summary);
3529 WSH_DEBUG_SV.log(l_module_name,'l_rate',l_rate);
3530 END IF;
3531
3532 IF((l_currency IS NOT NULL) AND (p_currency IS NOT NULL) AND
3533 (l_rate IS NOT NULL))
3534 THEN
3535
3536 IF (l_currency <> p_currency )
3537 THEN
3538 --Block to catch any exceptions thrown
3539 BEGIN
3540 --FOr discount rates may be negative flip it to ensure that
3541 -- it doesnt interfere with any negative value returned from the
3542 --currency conversion API that indicates a failure
3543 l_negative:='N';
3544 IF(l_rate < 0)
3545 THEN
3546 l_negative:='Y';
3547 l_rate:=l_rate *-1;
3548 IF l_debug_on THEN
3549 WSH_DEBUG_SV.log(l_module_name,'negative:l_rate',l_rate);
3550 END IF;
3551 END IF;
3552 wsh_util_core.get_currency_conversion_type(
3553 x_curr_conv_type => l_currency_conversion_type,
3554 x_return_status => l_return_status);
3555
3556 IF l_debug_on THEN
3557 WSH_DEBUG_SV.log(l_module_name,'x_curr_conv_type',l_currency_conversion_type);
3558 WSH_DEBUG_SV.log(l_module_name,'x_return_status',l_return_status);
3559 END IF;
3560
3561 if (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
3562 l_rate:=GL_CURRENCY_API.convert_amount(
3563 x_from_currency => l_currency,
3564 x_to_currency => p_currency,
3565 x_conversion_date => SYSDATE,
3566 x_conversion_type => l_currency_conversion_type,
3567 x_amount => l_rate
3568 );
3569 else
3570 raise FND_API.G_EXC_ERROR;
3571 end if;
3572
3573 EXCEPTION
3574 WHEN OTHERS THEN
3575 IF l_debug_on THEN
3576 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected exception occurred while getting the conversion type or converting the amount');
3577 END IF;
3578 raise FND_API.G_EXC_ERROR;
3579 END;
3580
3581 IF (l_negative='Y' AND l_rate IS NOT NULL AND l_rate > 0)
3582 THEN
3583 l_rate:=l_rate *-1;
3584 END IF;
3585
3586 -- l_negative <> 'Y' is added not to error out discount amount
3587 IF((l_rate IS NULL) OR (l_rate < 0 AND l_negative <> 'Y'))
3588 THEN
3589 raise FND_API.G_EXC_ERROR;
3590 END IF;
3591
3592 END IF;
3593
3594 END IF;
3595
3596 x_cost:=l_rate;
3597
3598 --
3599 IF l_debug_on THEN
3600 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3601 WSH_DEBUG_SV.log(l_module_name,'x_cost',x_cost);
3602 WSH_DEBUG_SV.pop(l_module_name);
3603 END IF;
3604 --
3605 EXCEPTION
3606
3607 WHEN FND_API.G_EXC_ERROR THEN
3608 x_return_status := FND_API.G_RET_STS_ERROR;
3609
3610 -- l_return_status is set from wsh_util_core.get_currency_conversion_type
3611 -- if it's success, gl_currency_api.convert_amount failed
3612 -- otherwise, error message is already set in the api, so no need to set it
3613 if (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) then
3614
3615 -- Bug 5886042
3616 -- currency conversion_type in the error message should be
3617 -- user_conversion_type
3618 BEGIN
3619 OPEN c_get_user_conv_type(l_currency_conversion_type);
3620 FETCH c_get_user_conv_type INTO l_user_conv_type;
3621 CLOSE c_get_user_conv_type;
3622 EXCEPTION
3623 WHEN OTHERS THEN
3624 l_user_conv_type := l_currency_conversion_type;
3625 IF c_get_user_conv_type%ISOPEN THEN
3626 CLOSE c_get_user_conv_type;
3627 END IF;
3628
3629 IF l_debug_on THEN
3630 WSH_DEBUG_SV.logmsg(l_module_name, 'Error occurred while getting the user currency conversion type');
3631 WSH_DEBUG_SV.log(l_module_name, 'l_currency_conversion_type', l_currency_conversion_type);
3632 END IF;
3633 END;
3634
3635 IF c_get_user_conv_type%ISOPEN THEN
3636 CLOSE c_get_user_conv_type;
3637 END IF;
3638 --
3639
3640 fnd_message.set_name('WSH', 'WSH_CURR_CONV_ERROR');
3641 fnd_message.set_token('FROM_CURR', l_currency);
3642 fnd_message.set_token('TO_CURR', p_currency);
3643 fnd_message.set_token('CONV_TYPE', l_user_conv_type);
3644 wsh_util_core.add_message(x_return_status);
3645 end if;
3646
3647 IF l_debug_on THEN
3648 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3649 WSH_DEBUG_SV.log(l_module_name,'l_currency',l_currency);
3650 WSH_DEBUG_SV.log(l_module_name,'p_currency',p_currency);
3651 WSH_DEBUG_SV.log(l_module_name,'l_cost_summary',l_cost_summary);
3652 WSH_DEBUG_SV.log(l_module_name,'l_rate',l_rate);
3653 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3654 END IF;
3655
3656 WHEN others THEN
3657 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3658 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Extract_Single_Cost',l_module_name);
3659 --
3660 IF l_debug_on THEN
3661 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3662 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3663 END IF;
3664 --
3665 END Extract_Single_Cost;
3666
3667
3668
3669 PROCEDURE Extract_Rate(
3670 p_xml IN XMLTYPE,
3671 p_xpath_prefix IN VARCHAR2,
3672 p_currency IN VARCHAR2,
3673 x_summary_rate OUT NOCOPY NUMBER,
3674 x_base_rate OUT NOCOPY NUMBER,
3675 x_charge_rate OUT NOCOPY NUMBER,
3676 x_return_status OUT NOCOPY VARCHAR2)
3677 IS
3678
3679 i NUMBER;
3680 l_xml_seq XMLSequenceType;
3681 l_tmp_xml XMLTYPE;
3682 l_return_status VARCHAR2(1);
3683 l_debug_on BOOLEAN;
3684 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Extract_Rate';
3685
3686 l_cost_type VARCHAR2(30);
3687 l_rate_detail NUMBER;
3688 l_summary_rate NUMBER;
3689 l_base_rate NUMBER;
3690 l_charge NUMBER;
3691
3692
3693 BEGIN
3694 --
3695 --
3696 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3697 --
3698 IF l_debug_on IS NULL
3699 THEN
3700 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3701 END IF;
3702 --
3703 IF l_debug_on THEN
3704 WSH_DEBUG_SV.push(l_module_name);
3705 END IF;
3706 --
3707 x_return_status := FND_API.G_RET_STS_SUCCESS;
3708
3709
3710 Extract_Single_Cost(
3711 p_xml=>p_xml,
3712 p_xpath_prefix=>p_xpath_prefix,
3713 p_currency=>p_currency,
3714 x_cost=>l_summary_rate,
3715 x_return_status=>l_return_status);
3716
3717 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
3718 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
3719 IF l_debug_on
3720 THEN
3721
3722 WSH_DEBUG_SV.log(l_module_name,'Extract_Single_Cost Failed');
3723 END IF;
3724
3725 raise FND_API.G_EXC_ERROR;
3726 END IF;
3727
3728 IF l_debug_on THEN
3729 WSH_DEBUG_SV.log(l_module_name,'After Extract_Single_Cost, Summary Rate:'||l_summary_rate);
3730 END IF;
3731
3732
3733 IF(l_summary_rate IS NULL) THEN
3734 IF l_debug_on
3735 THEN
3736
3737 WSH_DEBUG_SV.log(l_module_name,'x_summary_rate null');
3738 END IF;
3739 raise FND_API.G_EXC_ERROR;
3740 END IF;
3741
3742
3743
3744 Fetch_XML_Match(
3745 p_xml=>p_xml,
3746 p_xpath=>p_xpath_prefix||G_XPATH_COST_DETAILS,
3747 x_xml_seq=>l_xml_seq,
3748 x_return_status=>l_return_status);
3749
3750 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
3751 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
3752 IF l_debug_on
3753 THEN
3754
3755 WSH_DEBUG_SV.log(l_module_name,'Fetch_XML_Match Failed');
3756 END IF;
3757
3758 raise FND_API.G_EXC_ERROR;
3759 END IF;
3760
3761 --Initialize base and charge to 0
3762 l_base_rate:=0;
3763 l_charge:=0;
3764
3765 i:=l_xml_seq.FIRST;
3766 WHILE (i IS NOT NULL)
3767 LOOP
3768
3769 l_cost_type:=NULL;
3770
3771 IF l_debug_on
3772 THEN
3773
3774 WSH_DEBUG_SV.log(l_module_name,l_xml_seq(i).getStringVal());
3775 WSH_DEBUG_SV.log(l_module_name,G_XPATH_COST_DETAILS||G_XPATH_COST_DETAIL_TYPE);
3776 END IF;
3777
3778
3779 l_tmp_xml:=l_xml_seq(i).extract(G_XPATH_COST_DETAILS||G_XPATH_COST_DETAIL_TYPE,g_xml_namespace_map);
3780 IF (l_tmp_xml IS NOT NULL)
3781 THEN
3782 l_cost_type:=l_tmp_xml.getStringVal();
3783 IF l_debug_on
3784 THEN
3785 WSH_DEBUG_SV.log(l_module_name,'l_cost_type',l_cost_type);
3786 END IF;
3787 END IF;
3788
3789 l_rate_detail:=NULL;
3790 Extract_Single_Cost(
3791 p_xml=>l_xml_seq(i),
3792 p_xpath_prefix=>G_XPATH_COST_DETAILS,
3793 p_currency=>p_currency,
3794 x_cost=>l_rate_detail,
3795 x_return_status=>l_return_status);
3796
3797 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
3798 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
3799 IF l_debug_on
3800 THEN
3801
3802 WSH_DEBUG_SV.log(l_module_name,'Extract_Single_Cost Failed');
3803 END IF;
3804
3805 raise FND_API.G_EXC_ERROR;
3806 END IF;
3807
3808 IF l_debug_on
3809 THEN
3810 WSH_DEBUG_SV.log(l_module_name,'l_rate_detail',l_rate_detail);
3811 END IF;
3812
3813
3814 IF((l_rate_detail IS NOT NULL) AND (l_cost_type IS NOT NULL))
3815 THEN
3816 IF (l_cost_type = 'B')
3817 THEN
3818 l_base_rate:=l_base_rate+l_rate_detail;
3819 ELSIF (l_cost_type = 'D')
3820 THEN
3821 --Include discount into the base rate
3822 --OTM returns discount as -ve
3823 l_base_rate:=l_base_rate+l_rate_detail;
3824
3825 ELSE
3826 --A charge is present
3827 l_charge:=l_charge+l_rate_detail;
3828
3829 END IF;
3830
3831 END IF;
3832
3833 i:=l_xml_seq.NEXT(i);
3834
3835 END LOOP;
3836
3837 IF l_debug_on THEN
3838 WSH_DEBUG_SV.log(l_module_name,'l_summary_rate',l_summary_rate);
3839 WSH_DEBUG_SV.log(l_module_name,'l_base_rate',l_base_rate);
3840 WSH_DEBUG_SV.log(l_module_name,'l_charge',l_charge);
3841 END IF;
3842
3843
3844 --l_charge:=l_summary_rate-l_base_rate;
3845 IF((l_charge < 0) OR (l_summary_rate < 0) OR (l_base_rate < 0))
3846 THEN
3847 IF l_debug_on
3848 THEN
3849 WSH_DEBUG_SV.log(l_module_name,'Negative rates');
3850 WSH_DEBUG_SV.log(l_module_name,'l_summary_rate',l_summary_rate);
3851 WSH_DEBUG_SV.log(l_module_name,'l_base_rate',l_base_rate);
3852 WSH_DEBUG_SV.log(l_module_name,'l_charge',l_charge);
3853 END IF;
3854
3855 raise FND_API.G_EXC_ERROR;
3856
3857
3858 END IF;
3859
3860 x_summary_rate:=l_summary_rate;
3861 x_base_rate:=l_base_rate;
3862 x_charge_rate:=l_charge;
3863
3864 IF l_debug_on THEN
3865 WSH_DEBUG_SV.log(l_module_name,'x_summary_rate',x_summary_rate);
3866 WSH_DEBUG_SV.log(l_module_name,'x_base_rate',x_base_rate);
3867 WSH_DEBUG_SV.log(l_module_name,'x_charge_rate',x_charge_rate);
3868 END IF;
3869
3870
3871
3872 --
3873 IF l_debug_on THEN
3874 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
3875 WSH_DEBUG_SV.pop(l_module_name);
3876 END IF;
3877 --
3878 EXCEPTION
3879
3880 WHEN FND_API.G_EXC_ERROR THEN
3881 x_return_status := FND_API.G_RET_STS_ERROR;
3882
3883 IF l_debug_on THEN
3884 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
3885 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
3886 END IF;
3887
3888 WHEN others THEN
3889 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3890 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Extract_Rate',l_module_name);
3891 --
3892 IF l_debug_on THEN
3893 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3894 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3895 END IF;
3896 --
3897 END Extract_Rate;
3898
3899
3900 PROCEDURE Extract_Ship_Method(
3901 p_xml IN XMLTYPE,
3902 p_xpath_prefix IN VARCHAR2,
3903 p_action IN VARCHAR2,
3904 x_carrier_id OUT NOCOPY NUMBER,
3905 x_mode OUT NOCOPY VARCHAR2,
3906 x_service_level OUT NOCOPY VARCHAR2,
3907 x_freight_terms_code OUT NOCOPY VARCHAR2,
3908 x_transit_time OUT NOCOPY NUMBER,
3909 x_transit_time_UOM OUT NOCOPY VARCHAR2,
3910 x_return_status OUT NOCOPY VARCHAR2)
3911 IS
3912
3913 l_tmp_xml XMLTYPE;
3914 l_carrier_prefix VARCHAR2(30);
3915
3916 l_carrier VARCHAR2(50);
3917 l_carrier_id NUMBER;
3918 l_mode VARCHAR2(50);
3919 l_service_level VARCHAR2(50);
3920 l_freight_terms_code VARCHAR2(30);
3921 l_transit_time_string VARCHAR2(30);
3922 l_transit_time NUMBER;
3923 l_transit_time_uom VARCHAR2(30);
3924
3925 l_return_status VARCHAR2(1);
3926 l_debug_on BOOLEAN;
3927 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Extract_Ship_Method';
3928
3929
3930
3931 BEGIN
3932 --
3933 --
3934 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3935 --
3936 IF l_debug_on IS NULL
3937 THEN
3938 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3939 END IF;
3940 --
3941 IF l_debug_on THEN
3942 WSH_DEBUG_SV.push(l_module_name);
3943 END IF;
3944 --
3945 x_return_status := FND_API.G_RET_STS_SUCCESS;
3946
3947 IF l_debug_on
3948 THEN
3949
3950 WSH_DEBUG_SV.log(l_module_name,'l_xml',p_xml.getStringVal());
3951 WSH_DEBUG_SV.log(l_module_name,'XML Done');
3952 WSH_DEBUG_SV.log(l_module_name,'xp:',p_xpath_prefix||G_XPATH_CARRIER);
3953 END IF;
3954
3955 l_tmp_xml:=p_xml.extract(p_xpath_prefix||G_XPATH_CARRIER,g_xml_namespace_map);
3956 IF (l_tmp_xml IS NOT NULL)
3957 THEN
3958 l_carrier_id:=Convert_Carrier_Ouput(l_tmp_xml.getStringVal());
3959
3960 END IF;
3961
3962 l_tmp_xml:=p_xml.extract(p_xpath_prefix||G_XPATH_MODE,g_xml_namespace_map);
3963 IF (l_tmp_xml IS NOT NULL)
3964 THEN
3965 l_mode:=l_tmp_xml.getStringVal();
3966
3967 END IF;
3968
3969 l_tmp_xml:=p_xml.extract(p_xpath_prefix||G_XPATH_SERVICE_LEVEL,g_xml_namespace_map);
3970 IF (l_tmp_xml IS NOT NULL)
3971 THEN
3972 l_service_level:=l_tmp_xml.getStringVal();
3973
3974 END IF;
3975
3976 l_tmp_xml:=p_xml.extract(p_xpath_prefix||G_XPATH_FREIGHT_TERMS,g_xml_namespace_map);
3977 IF (l_tmp_xml IS NOT NULL)
3978 THEN
3979 l_freight_terms_code:=l_tmp_xml.getStringVal();
3980
3981 END IF;
3982
3983 l_tmp_xml:=p_xml.extract(p_xpath_prefix||G_XPATH_TRANSIT_TIME,g_xml_namespace_map);
3984
3985 IF (l_tmp_xml IS NOT NULL)
3986 THEN
3987 l_transit_time_string:=l_tmp_xml.getStringVal();
3988 IF l_debug_on
3989 THEN
3990 WSH_DEBUG_SV.log(l_module_name,'l_transit_time_string',l_transit_time_string);
3991 END IF;
3992
3993 IF (l_transit_time_string IS NOT NULL)
3994 THEN
3995 l_transit_time:=Convert_To_Number(l_transit_time_string);
3996 END IF;
3997
3998 IF l_debug_on
3999 THEN
4000 WSH_DEBUG_SV.log(l_module_name,'l_transit_time',l_transit_time);
4001 END IF;
4002
4003
4004 END IF;
4005
4006 l_tmp_xml:=p_xml.extract(p_xpath_prefix||G_XPATH_TRANSIT_TIME_UOM,g_xml_namespace_map);
4007 IF (l_tmp_xml IS NOT NULL)
4008 THEN
4009 l_transit_time_uom:=l_tmp_xml.getStringVal();
4010 IF l_debug_on
4011 THEN
4012 WSH_DEBUG_SV.log(l_module_name,'l_transit_time_uom',l_transit_time_uom);
4013 END IF;
4014
4015
4016 END IF;
4017
4018
4019 IF ((l_mode IS NOT NULL) AND (LENGTH(l_mode) > 30))
4020 THEN
4021 IF l_debug_on
4022 THEN
4023 WSH_DEBUG_SV.log(l_module_name,'Mode length > 30 truncating');
4024 WSH_DEBUG_SV.log(l_module_name,'l_mode',l_mode);
4025 END IF;
4026
4027 l_mode:=SUBSTR(l_mode,1,30);
4028
4029 END IF;
4030
4031 IF ((l_service_level IS NOT NULL) AND (LENGTH(l_service_level) > 30))
4032 THEN
4033 IF l_debug_on
4034 THEN
4035 WSH_DEBUG_SV.log(l_module_name,'Service Level length > 30 truncating');
4036 WSH_DEBUG_SV.log(l_module_name,'l_service_level',l_service_level);
4037 END IF;
4038
4039 l_service_level:=SUBSTR(l_service_level,1,30);
4040
4041 END IF;
4042
4043
4044
4045 x_carrier_id:=l_carrier_id;
4046 x_mode:=l_mode;
4047 x_service_level:=l_service_level;
4048 x_freight_terms_code:=l_freight_terms_code;
4049
4050 IF((l_transit_time IS NOT NULL) AND (l_transit_time_uom IS NOT NULL) AND (LENGTH(l_transit_time_uom)<=3))
4051 THEN
4052 x_transit_time:=l_transit_time;
4053 x_transit_time_UOM:=l_transit_time_uom;
4054
4055 END IF;
4056
4057
4058 IF l_debug_on
4059 THEN
4060
4061 WSH_DEBUG_SV.log(l_module_name,'l_carrier_id',x_carrier_id);
4062 WSH_DEBUG_SV.log(l_module_name,'l_mode',x_mode);
4063 WSH_DEBUG_SV.log(l_module_name,'l_service_level',x_service_level);
4064 WSH_DEBUG_SV.log(l_module_name,'l_freight_terms_code',x_freight_terms_code);
4065 WSH_DEBUG_SV.log(l_module_name,'x_transit_time',x_transit_time);
4066 WSH_DEBUG_SV.log(l_module_name,'x_transit_time_UOM',x_transit_time_uom);
4067
4068 END IF;
4069
4070 --
4071 IF l_debug_on THEN
4072 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
4073 WSH_DEBUG_SV.pop(l_module_name);
4074 END IF;
4075 --
4076 EXCEPTION
4077
4078 WHEN FND_API.G_EXC_ERROR THEN
4079 x_return_status := FND_API.G_RET_STS_ERROR;
4080
4081 IF l_debug_on THEN
4082 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4083 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4084 END IF;
4085
4086 WHEN others THEN
4087 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4088 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Extract_Ship_Method',l_module_name);
4089 --
4090 IF l_debug_on THEN
4091 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4092 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4093 END IF;
4094 --
4095 END Extract_Ship_Method;
4096
4097
4098 PROCEDURE Populate_Get_SM_Result(
4099 p_source_header_rec IN FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_REC,
4100 p_carrier_id IN NUMBER,
4101 p_mode IN VARCHAR2,
4102 p_service_level IN VARCHAR2,
4103 p_freight_terms_code IN VARCHAR2,
4104 p_transit_time IN NUMBER,
4105 p_transit_time_UOM IN VARCHAR2,
4106 x_result_consolidation_id_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
4107 x_result_carrier_id_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
4108 x_result_service_level_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
4109 x_result_mode_of_transport_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
4110 x_result_freight_term_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
4111 x_result_transit_time_min_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
4112 x_result_transit_time_max_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
4113 x_ship_method_code_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
4114 x_return_status OUT NOCOPY VARCHAR2)
4115
4116 IS
4117 l_freight_terms_code VARCHAR2(30);
4118 i NUMBER;
4119 l_ship_method_code VARCHAR2(30);
4120 l_return_status VARCHAR2(1);
4121 l_debug_on BOOLEAN;
4122 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Populate_Get_SM_Result';
4123
4124 BEGIN
4125 --
4126 --
4127 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4128 --
4129 IF l_debug_on IS NULL
4130 THEN
4131 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4132 END IF;
4133 --
4134 IF l_debug_on THEN
4135 WSH_DEBUG_SV.push(l_module_name);
4136 END IF;
4137 --
4138 x_return_status := FND_API.G_RET_STS_SUCCESS;
4139
4140
4141 i:=x_result_consolidation_id_tab.LAST;
4142 IF (i IS NULL)
4143 THEN
4144 i:=1;
4145 ELSE
4146 i:=i+1;
4147 END IF;
4148
4149 x_result_consolidation_id_tab(i):=p_source_header_rec.consolidation_id;
4150
4151 Validate_Carrier(
4152 p_carrier_id=>p_carrier_id,
4153 x_return_status=>l_return_status);
4154 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4155 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4156 IF l_debug_on
4157 THEN
4158
4159 WSH_DEBUG_SV.log(l_module_name,'Validate_Carrier Failed');
4160 END IF;
4161 x_result_carrier_id_tab(i):=NULL;
4162
4163 ELSE
4164 x_result_carrier_id_tab(i):=p_carrier_id;
4165 END IF;
4166
4167
4168
4169
4170
4171 Validate_Look_Up_NoCase(
4172 p_lookup_type=>'WSH_SERVICE_LEVELS',
4173 p_lookup_code=>p_service_level,
4174 x_lookup_code=>x_result_service_level_tab(i),
4175 x_return_status=>l_return_status);
4176 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4177 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4178 IF l_debug_on
4179 THEN
4180
4181 WSH_DEBUG_SV.log(l_module_name,'Validate WSH_SERVICE_LEVELS Failed');
4182 END IF;
4183
4184 END IF;
4185
4186
4187 --p_freight_terms_code is an INOUT parameter to Validate_Freight_terms
4188 l_freight_terms_code:=p_freight_terms_code;
4189
4190 Validate_Look_Up_NoCase(
4191 p_lookup_type=>'FREIGHT_TERMS',
4192 p_lookup_code=>l_freight_terms_code,
4193 x_lookup_code=>x_result_freight_term_tab(i),
4194 x_return_status=>l_return_status);
4195 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4196 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4197 IF l_debug_on
4198 THEN
4199
4200 WSH_DEBUG_SV.log(l_module_name,'Validate FREIGHT_TERMS Failed');
4201 END IF;
4202 END IF;
4203
4204
4205 Validate_Look_Up_NoCase(
4206 p_lookup_type=>'WSH_MODE_OF_TRANSPORT',
4207 p_lookup_code=>p_mode,
4208 x_lookup_code=>x_result_mode_of_transport_tab(i),
4209 x_return_status=>l_return_status);
4210 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4211 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4212 IF l_debug_on
4213 THEN
4214
4215 WSH_DEBUG_SV.log(l_module_name,'Validate WSH_MODE_OF_TRANSPORT Failed');
4216 END IF;
4217
4218 END IF;
4219
4220
4221 x_result_transit_time_min_tab(i):=NULL;
4222
4223 x_result_transit_time_max_tab(i):=NULL;
4224
4225 Get_Ship_Method_Code(
4226 p_org_id=>p_source_header_rec.ship_from_org_id,
4227 p_carrier_id=>p_carrier_id,
4228 p_mode=>p_mode,
4229 p_service_level=>p_service_level,
4230 x_ship_method_code=>l_ship_method_code,
4231 x_return_status=>l_return_status);
4232
4233 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4234 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4235 IF l_debug_on
4236 THEN
4237
4238 WSH_DEBUG_SV.log(l_module_name,'Get_Ship_Method_Code Failed');
4239 END IF;
4240 x_ship_method_code_tab(i):=NULL;
4241
4242 ELSE
4243 x_ship_method_code_tab(i):=l_ship_method_code;
4244 END IF;
4245
4246
4247
4248 --
4249 IF l_debug_on THEN
4250 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
4251 WSH_DEBUG_SV.pop(l_module_name);
4252 END IF;
4253 --
4254 EXCEPTION
4255
4256 WHEN FND_API.G_EXC_ERROR THEN
4257 x_return_status := FND_API.G_RET_STS_ERROR;
4258
4259 IF l_debug_on THEN
4260 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4261 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4262 END IF;
4263
4264 WHEN others THEN
4265 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4266 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Populate_Get_SM_Result',l_module_name);
4267 --
4268 IF l_debug_on THEN
4269 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4270 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4271 END IF;
4272 --
4273 END Populate_Get_SM_Result;
4274
4275
4276 PROCEDURE Allocate_rates(
4277 p_summary_rate IN NUMBER,
4278 p_base_price IN NUMBER,
4279 p_charge IN NUMBER,
4280 p_source_header_rates_rec IN FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_REC,
4281 p_source_header_rec IN FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_REC,
4282 p_source_line_tab IN FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_TAB,
4283 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_RATES_TAB,
4284 x_return_status OUT NOCOPY VARCHAR2)
4285 IS
4286 i NUMBER;
4287 j NUMBER;
4288 k NUMBER;
4289 l_number_of_lines NUMBER;
4290
4291 l_fraction NUMBER;
4292 l_base_price NUMBER;
4293 l_charge NUMBER;
4294 l_line_rate_rec FTE_PROCESS_REQUESTS.fte_source_line_rates_rec;
4295
4296 l_return_status VARCHAR2(1);
4297 l_debug_on BOOLEAN;
4298 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Allocate_rates';
4299
4300 BEGIN
4301 --
4302 --
4303 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4304 --
4305 IF l_debug_on IS NULL
4306 THEN
4307 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4308 END IF;
4309 --
4310 IF l_debug_on THEN
4311 WSH_DEBUG_SV.push(l_module_name);
4312 END IF;
4313 --
4314 x_return_status := FND_API.G_RET_STS_SUCCESS;
4315
4316
4317
4318
4319 --FTE_SOURCE_LINE_CONSOLIDATION ensures that total weight in the header rec and weight in the line rec are in the same UOM
4320
4321 --Index of the start of line rates;
4322 j:=p_source_header_rates_rec.first_line_index;
4323
4324 i:=p_source_line_tab.FIRST;
4325 WHILE(i IS NOT NULL)
4326 LOOP
4327
4328
4329
4330 IF((p_source_line_tab(i).consolidation_id IS NOT NULL)
4331 AND (p_source_header_rec.consolidation_id IS NOT NULL)
4332 AND (p_source_header_rec.consolidation_id = p_source_line_tab(i).consolidation_id))
4333 THEN
4334
4335
4336 l_fraction:=0;
4337 IF ((p_source_header_rec.total_weight IS NOT NULL)
4338 AND (p_source_line_tab(i).weight IS NOT NULL))
4339 THEN
4340
4341
4342 IF (p_source_header_rec.total_weight > 0)
4343 THEN
4344 l_fraction:=p_source_line_tab(i).weight/p_source_header_rec.total_weight;
4345 END IF;
4346
4347 l_base_price:=p_base_price*l_fraction;
4348 l_charge:=p_charge*l_fraction;
4349
4350 IF l_debug_on THEN
4351
4352 WSH_DEBUG_SV.log(l_module_name,'j',j);
4353 END IF;
4354
4355 --Init new line rate rec
4356 x_source_line_rates_tab(j):=l_line_rate_rec;
4357
4358
4359 x_source_line_rates_tab(j).source_line_id:=p_source_line_tab(i).source_line_id;
4360
4361 x_source_line_rates_tab(j).cost_type_id := g_price_cost_type_id;
4362
4363 x_source_line_rates_tab(j).line_type_code := 'PRICE';
4364
4365 x_source_line_rates_tab(j).cost_type := 'FTEPRICE';
4366 x_source_line_rates_tab(j).cost_sub_type := 'PRICE';
4367
4368
4369
4370 x_source_line_rates_tab(j).priced_quantity:= p_source_line_tab(i).source_quantity;
4371 x_source_line_rates_tab(j).priced_uom := p_source_line_tab(i).source_quantity_uom;
4372
4373
4374
4375 IF ((x_source_line_rates_tab(j).priced_quantity IS NULL) OR (x_source_line_rates_tab(j).priced_quantity = 0))
4376 THEN
4377 x_source_line_rates_tab(j).adjusted_unit_price := l_base_price; -- adjusted unit price
4378 x_source_line_rates_tab(j).adjusted_price := l_base_price ; -- adjusted unit price (including discount)
4379
4380 ELSE
4381 x_source_line_rates_tab(j).adjusted_unit_price := (l_base_price)/(x_source_line_rates_tab(j).priced_quantity) ; -- adjusted unit price
4382 x_source_line_rates_tab(j).adjusted_price := l_base_price ; -- adjusted unit price (including discount)
4383
4384 END IF;
4385 x_source_line_rates_tab(j).currency := p_source_header_rates_rec.currency;
4386
4387 x_source_line_rates_tab(j).unit_price := x_source_line_rates_tab(j).adjusted_unit_price;
4388 x_source_line_rates_tab(j).base_price := x_source_line_rates_tab(j).adjusted_price;
4389
4390 x_source_line_rates_tab(j).consolidation_id := p_source_header_rec.consolidation_id;
4391 x_source_line_rates_tab(j).lane_id := p_source_header_rates_rec.lane_id;
4392 x_source_line_rates_tab(j).carrier_id := p_source_header_rates_rec.carrier_id;
4393 x_source_line_rates_tab(j).carrier_freight_code := p_source_header_rates_rec.carrier_freight_code;
4394 x_source_line_rates_tab(j).service_level := p_source_header_rates_rec.service_level;
4395 x_source_line_rates_tab(j).mode_of_transport := p_source_header_rates_rec.mode_of_transport;
4396 x_source_line_rates_tab(j).ship_method_code := p_source_header_rates_rec.ship_method_code;
4397
4398
4399 j:=j+1;
4400
4401
4402 IF l_debug_on THEN
4403
4404 WSH_DEBUG_SV.log(l_module_name,'p_charge',p_charge);
4405 END IF;
4406
4407
4408 --Insert a charge rec only if charge > 0
4409 IF ((p_charge IS NOT NULL) AND (p_charge > 0))
4410 THEN
4411
4412 IF l_debug_on THEN
4413
4414 WSH_DEBUG_SV.log(l_module_name,'Creating Charge line');
4415 END IF;
4416
4417 --Init new line rate rec
4418 x_source_line_rates_tab(j):=l_line_rate_rec;
4419
4420
4421 x_source_line_rates_tab(j).source_line_id := p_source_line_tab(i).source_line_id;
4422 x_source_line_rates_tab(j).cost_type_id := NULL; -- fix this
4423 x_source_line_rates_tab(j).line_type_code := 'CHARGE';
4424 x_source_line_rates_tab(j).cost_type := 'FTECHARGE';
4425 x_source_line_rates_tab(j).cost_sub_type := NULL;
4426 x_source_line_rates_tab(j).priced_quantity := p_source_line_tab(i).source_quantity;
4427 x_source_line_rates_tab(j).priced_uom := p_source_line_tab(i).source_quantity_uom;
4428
4429 IF ((x_source_line_rates_tab(j).priced_quantity IS NULL) OR (x_source_line_rates_tab(j).priced_quantity = 0))
4430 THEN
4431 x_source_line_rates_tab(j).adjusted_unit_price := l_charge; -- adjusted unit price
4432 x_source_line_rates_tab(j).adjusted_price := l_charge ; -- adjusted unit price (including discount)
4433
4434 ELSE
4435 x_source_line_rates_tab(j).adjusted_unit_price := (l_charge)/(x_source_line_rates_tab(j).priced_quantity) ; -- adjusted unit price
4436 x_source_line_rates_tab(j).adjusted_price := l_charge; -- adjusted unit price (including discount)
4437
4438 END IF;
4439
4440
4441 x_source_line_rates_tab(j).unit_price := x_source_line_rates_tab(j).adjusted_unit_price;
4442 x_source_line_rates_tab(j).base_price := x_source_line_rates_tab(j).adjusted_price;
4443
4444 x_source_line_rates_tab(j).currency := p_source_header_rates_rec.currency;
4445
4446 x_source_line_rates_tab(j).consolidation_id := p_source_header_rec.consolidation_id;
4447 x_source_line_rates_tab(j).lane_id := p_source_header_rates_rec.lane_id;
4448 x_source_line_rates_tab(j).carrier_id := p_source_header_rates_rec.carrier_id;
4449 x_source_line_rates_tab(j).carrier_freight_code := p_source_header_rates_rec.carrier_freight_code;
4450 x_source_line_rates_tab(j).service_level := p_source_header_rates_rec.service_level;
4451 x_source_line_rates_tab(j).mode_of_transport := p_source_header_rates_rec.mode_of_transport;
4452 x_source_line_rates_tab(j).ship_method_code := p_source_header_rates_rec.ship_method_code;
4453
4454
4455
4456 j:=j+1;
4457 END IF;
4458
4459 END IF;
4460
4461 END IF;
4462 i:=p_source_line_tab.NEXT(i);
4463 END LOOP;
4464
4465 --If total_weight is 0 distribute charges equally to all lines
4466 IF ((p_source_header_rec.total_weight IS NULL) OR (p_source_header_rec.total_weight =0))
4467 THEN
4468
4469 IF l_debug_on THEN
4470 WSH_DEBUG_SV.log(l_module_name,'total weight is or null');
4471 END IF;
4472
4473 l_number_of_lines:=(j-p_source_header_rates_rec.first_line_index)/2;
4474 l_fraction:=1/l_number_of_lines;
4475 l_base_price:=p_base_price*l_fraction;
4476 l_charge:=p_charge*l_fraction;
4477
4478 j:=p_source_header_rates_rec.first_line_index;
4479 WHILE(j IS NOT NULL)
4480 LOOP
4481 IF (x_source_line_rates_tab(j).cost_sub_type='PRICE')
4482 THEN
4483 x_source_line_rates_tab(j).unit_price:=l_base_price;
4484 x_source_line_rates_tab(j).base_price:=l_base_price;
4485 x_source_line_rates_tab(j).adjusted_unit_price:=l_base_price;
4486 x_source_line_rates_tab(j).adjusted_price:=l_base_price;
4487
4488 ELSE
4489
4490 x_source_line_rates_tab(j).unit_price:=l_charge;
4491 x_source_line_rates_tab(j).base_price:=l_charge;
4492 x_source_line_rates_tab(j).adjusted_unit_price:=l_charge;
4493 x_source_line_rates_tab(j).adjusted_price:=l_charge;
4494
4495
4496 END IF;
4497
4498 j:=x_source_line_rates_tab.NEXT(j);
4499 END LOOP;
4500
4501 END IF;
4502
4503
4504 --
4505 IF l_debug_on THEN
4506 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
4507 WSH_DEBUG_SV.pop(l_module_name);
4508 END IF;
4509 --
4510 EXCEPTION
4511
4512 WHEN FND_API.G_EXC_ERROR THEN
4513 x_return_status := FND_API.G_RET_STS_ERROR;
4514
4515 IF l_debug_on THEN
4516 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4517 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4518 END IF;
4519
4520 WHEN others THEN
4521 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4522 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Allocate_rates',l_module_name);
4523 --
4524 IF l_debug_on THEN
4525 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4526 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4527 END IF;
4528 --
4529 END Allocate_rates;
4530
4531
4532
4533 PROCEDURE Populate_Get_FC_Result(
4534 p_summary_rate IN NUMBER,
4535 p_base_rate IN NUMBER,
4536 p_charge_rate IN NUMBER,
4537 p_transit_time IN NUMBER,
4538 p_transit_time_UOM IN VARCHAR2,
4539 p_source_header_rec IN FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_REC,
4540 p_source_line_tab IN FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_TAB,
4541 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_RATES_TAB,
4542 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_TAB,
4543 x_return_status OUT NOCOPY VARCHAR2)
4544 IS
4545
4546 i NUMBER;
4547 j NUMBER;
4548 k NUMBER;
4549 l_generic_carrier VARCHAR2(1);
4550 l_carrier_freight_code VARCHAR2(30);
4551
4552 l_source_header_rates_rec FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_REC;
4553
4554 l_return_status VARCHAR2(1);
4555 l_debug_on BOOLEAN;
4556 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Populate_Get_FC_Result';
4557
4558 BEGIN
4559 --
4560 --
4561 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4562 --
4563 IF l_debug_on IS NULL
4564 THEN
4565 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4566 END IF;
4567 --
4568 IF l_debug_on THEN
4569 WSH_DEBUG_SV.push(l_module_name);
4570 END IF;
4571 --
4572 x_return_status := FND_API.G_RET_STS_SUCCESS;
4573
4574 --i is the source header rates tab index
4575 i:=x_source_header_rates_tab.LAST;
4576 IF (i IS NULL)
4577 THEN
4578 i:=1;
4579 ELSE
4580 i:=i+1;
4581 END IF;
4582
4583 --j is the source line rates tab index
4584 j:=x_source_line_rates_tab.LAST;
4585 IF (j IS NULL)
4586 THEN
4587 j:=1;
4588 ELSE
4589 j:=j+1;
4590 END IF;
4591
4592 --Init source header rates rec
4593 x_source_header_rates_tab(i):=l_source_header_rates_rec;
4594
4595 x_source_header_rates_tab(i).consolidation_id := p_source_header_rec.consolidation_id;
4596 --No real Lane id, fake lane id
4597 x_source_header_rates_tab(i).lane_id := i;
4598 x_source_header_rates_tab(i).carrier_id := p_source_header_rec.carrier_id;
4599
4600 --Get carrier freight code
4601
4602 IF (x_source_header_rates_tab(i).carrier_id IS NOT NULL)
4603 THEN
4604
4605 Get_Carrier_Info(
4606 p_carrier_id=>x_source_header_rates_tab(i).carrier_id,
4607 x_generic=>l_generic_carrier,
4608 x_carrier_freight_code=>l_carrier_freight_code,
4609 x_return_status=>l_return_status);
4610
4611 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4612 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4613 IF l_debug_on
4614 THEN
4615 WSH_DEBUG_SV.log(l_module_name,'Dest Location Get Info Failed');
4616 END IF;
4617 raise FND_API.G_EXC_ERROR;
4618 END IF;
4619 x_source_header_rates_tab(i).carrier_freight_code := l_carrier_freight_code;
4620 END IF;
4621
4622
4623
4624 x_source_header_rates_tab(i).service_level := p_source_header_rec.service_level;
4625 x_source_header_rates_tab(i).mode_of_transport := p_source_header_rec.mode_of_transport;
4626 x_source_header_rates_tab(i).ship_method_code := p_source_header_rec.ship_method_code;
4627 x_source_header_rates_tab(i).cost_type_id := null;
4628 x_source_header_rates_tab(i).cost_type := 'SUMMARY';
4629 x_source_header_rates_tab(i).price := p_summary_rate;
4630 x_source_header_rates_tab(i).currency := p_source_header_rec.currency;
4631 x_source_header_rates_tab(i).transit_time := p_transit_time;
4632 x_source_header_rates_tab(i).transit_time_uom := p_transit_time_UOM;
4633 x_source_header_rates_tab(i).first_line_index := j;
4634
4635
4636 Allocate_rates(
4637 p_summary_rate=>p_summary_rate,
4638 p_base_price=>p_base_rate,
4639 p_charge=>p_charge_rate,
4640 p_source_header_rates_rec=>x_source_header_rates_tab(i),
4641 p_source_header_rec=>p_source_header_rec,
4642 p_source_line_tab=>p_source_line_tab,
4643 x_source_line_rates_tab=>x_source_line_rates_tab,
4644 x_return_status=>l_return_status);
4645
4646 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4647 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4648 IF l_debug_on
4649 THEN
4650
4651 WSH_DEBUG_SV.log(l_module_name,'Allocate_rates Failed');
4652 END IF;
4653
4654 raise FND_API.G_EXC_ERROR;
4655 END IF;
4656
4657
4658 --
4659 IF l_debug_on THEN
4660 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
4661 WSH_DEBUG_SV.pop(l_module_name);
4662 END IF;
4663 --
4664 EXCEPTION
4665
4666 WHEN FND_API.G_EXC_ERROR THEN
4667 x_return_status := FND_API.G_RET_STS_ERROR;
4668
4669 IF l_debug_on THEN
4670 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4671 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4672 END IF;
4673
4674 WHEN others THEN
4675 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4676 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Populate_Get_FC_Result',l_module_name);
4677 --
4678 IF l_debug_on THEN
4679 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4680 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4681 END IF;
4682 --
4683 END Populate_Get_FC_Result;
4684
4685
4686
4687 PROCEDURE Populate_Choose_SM_Result(
4688 p_carrier_id IN NUMBER,
4689 p_mode IN VARCHAR2,
4690 p_service_level IN VARCHAR2,
4691 p_freight_terms_code IN VARCHAR2,
4692 p_transit_time IN NUMBER,
4693 p_transit_time_UOM IN VARCHAR2,
4694 p_summary_rate IN NUMBER,
4695 p_base_rate IN NUMBER,
4696 p_charge_rate IN NUMBER,
4697 p_ship_method_code IN VARCHAR2,
4698 p_source_header_rec IN FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_REC,
4699 p_source_line_tab IN FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_TAB,
4700 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_RATES_TAB,
4701 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_TAB,
4702 x_return_status OUT NOCOPY VARCHAR2)
4703 IS
4704 i NUMBER;
4705 j NUMBER;
4706 k NUMBER;
4707 l_source_header_rates_rec FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_REC;
4708 l_generic_carrier VARCHAR2(1);
4709 l_carrier_freight_code VARCHAR2(30);
4710
4711 l_return_status VARCHAR2(1);
4712 l_debug_on BOOLEAN;
4713 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Populate_Choose_SM_Result';
4714
4715 BEGIN
4716 --
4717 --
4718 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4719 --
4720 IF l_debug_on IS NULL
4721 THEN
4722 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4723 END IF;
4724 --
4725 IF l_debug_on THEN
4726 WSH_DEBUG_SV.push(l_module_name);
4727 END IF;
4728 --
4729 x_return_status := FND_API.G_RET_STS_SUCCESS;
4730
4731
4732 --i is the source header rates tab index
4733 i:=x_source_header_rates_tab.LAST;
4734 IF (i IS NULL)
4735 THEN
4736 i:=1;
4737 ELSE
4738 i:=i+1;
4739 END IF;
4740
4741 --j is the source line rates tab index
4742 j:=x_source_line_rates_tab.LAST;
4743 IF (j IS NULL)
4744 THEN
4745 j:=1;
4746 ELSE
4747 j:=j+1;
4748 END IF;
4749
4750
4751
4752
4753 --If no SM Code then do not populate results for this carrier/mode/service level
4754
4755 IF (p_ship_method_code IS NOT NULL)
4756 THEN
4757
4758 --Init source header rates rec
4759 x_source_header_rates_tab(i):=l_source_header_rates_rec;
4760
4761 x_source_header_rates_tab(i).consolidation_id := p_source_header_rec.consolidation_id;
4762 --No real Lane id, fake lane id
4763 x_source_header_rates_tab(i).lane_id := i;
4764 x_source_header_rates_tab(i).carrier_id := p_carrier_id;
4765
4766 --Get carrier freight code
4767
4768 IF (x_source_header_rates_tab(i).carrier_id IS NOT NULL)
4769 THEN
4770 l_carrier_freight_code:=NULL;
4771
4772 Get_Carrier_Info(
4773 p_carrier_id=>x_source_header_rates_tab(i).carrier_id,
4774 x_generic=>l_generic_carrier,
4775 x_carrier_freight_code=>l_carrier_freight_code,
4776 x_return_status=>l_return_status);
4777
4778 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4779 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4780 IF l_debug_on
4781 THEN
4782 WSH_DEBUG_SV.log(l_module_name,'Get_Carrier_Info Failed');
4783 END IF;
4784 raise FND_API.G_EXC_ERROR;
4785 END IF;
4786 x_source_header_rates_tab(i).carrier_freight_code := l_carrier_freight_code;
4787 END IF;
4788
4789
4790 x_source_header_rates_tab(i).ship_method_code:=p_ship_method_code;
4791
4792 x_source_header_rates_tab(i).service_level := p_service_level;
4793 x_source_header_rates_tab(i).mode_of_transport := p_mode;
4794
4795
4796
4797
4798 x_source_header_rates_tab(i).cost_type_id := null;
4799 x_source_header_rates_tab(i).cost_type := 'SUMMARY';
4800 x_source_header_rates_tab(i).price := p_summary_rate;
4801 x_source_header_rates_tab(i).currency := p_source_header_rec.currency;
4802 x_source_header_rates_tab(i).transit_time := p_transit_time;
4803 x_source_header_rates_tab(i).transit_time_uom := p_transit_time_UOM;
4804 x_source_header_rates_tab(i).first_line_index := j;
4805
4806
4807 Allocate_rates(
4808 p_summary_rate=>p_summary_rate,
4809 p_base_price=>p_base_rate,
4810 p_charge=>p_charge_rate,
4811 p_source_header_rates_rec=>x_source_header_rates_tab(i),
4812 p_source_header_rec=>p_source_header_rec,
4813 p_source_line_tab=>p_source_line_tab,
4814 x_source_line_rates_tab=>x_source_line_rates_tab,
4815 x_return_status=>l_return_status);
4816
4817 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4818 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4819 IF l_debug_on
4820 THEN
4821
4822 WSH_DEBUG_SV.log(l_module_name,'Allocate_rates Failed');
4823 END IF;
4824
4825 raise FND_API.G_EXC_ERROR;
4826 END IF;
4827 ELSE
4828 IF l_debug_on
4829 THEN
4830 WSH_DEBUG_SV.log(l_module_name,'Ignoring this Choose Ship Method result');
4831 WSH_DEBUG_SV.log(l_module_name,'p_carrier_id',p_carrier_id);
4832 WSH_DEBUG_SV.log(l_module_name,'p_mode',p_mode);
4833 WSH_DEBUG_SV.log(l_module_name,'p_service_level',p_service_level);
4834
4835 END IF;
4836
4837
4838 END IF;
4839
4840 --
4841 IF l_debug_on THEN
4842 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
4843 WSH_DEBUG_SV.pop(l_module_name);
4844 END IF;
4845 --
4846 EXCEPTION
4847
4848 WHEN FND_API.G_EXC_ERROR THEN
4849 x_return_status := FND_API.G_RET_STS_ERROR;
4850
4851 IF l_debug_on THEN
4852 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4853 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
4854 END IF;
4855
4856 WHEN others THEN
4857 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
4858 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Populate_Choose_SM_Result',l_module_name);
4859 --
4860 IF l_debug_on THEN
4861 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4862 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4863 END IF;
4864 --
4865 END Populate_Choose_SM_Result;
4866
4867
4868
4869 PROCEDURE Handle_Get_SM_Response(
4870 p_xml_output IN XMLTYPE,
4871 p_source_type IN VARCHAR2,
4872 p_action IN VARCHAR2,
4873 x_source_header_rec IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_REC,
4874 x_result_consolidation_id_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
4875 x_result_carrier_id_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
4876 x_result_service_level_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
4877 x_result_mode_of_transport_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
4878 x_result_freight_term_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
4879 x_result_transit_time_min_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
4880 x_result_transit_time_max_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
4881 x_ship_method_code_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
4882 x_return_status OUT NOCOPY VARCHAR2)
4883 IS
4884
4885 i NUMBER;
4886 l_xml_seq XMLSequenceType;
4887 l_carrier_id NUMBER;
4888 l_mode VARCHAR2(30);
4889 l_service_level VARCHAR2(30);
4890 l_freight_terms VARCHAR2(30);
4891 l_transit_time NUMBER;
4892 l_transit_time_UOM VARCHAR2(30);
4893 l_tmp_xml XMLTYPE;
4894
4895 l_return_status VARCHAR2(1);
4896 l_debug_on BOOLEAN;
4897 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Handle_Get_SM_Response';
4898
4899
4900
4901 BEGIN
4902 --
4903 --
4904 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4905 --
4906 IF l_debug_on IS NULL
4907 THEN
4908 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4909 END IF;
4910 --
4911 IF l_debug_on THEN
4912 WSH_DEBUG_SV.push(l_module_name);
4913 END IF;
4914 --
4915 x_return_status := FND_API.G_RET_STS_SUCCESS;
4916
4917 IF l_debug_on
4918 THEN
4919 WSH_DEBUG_SV.log(l_module_name,'p_xml_output',p_xml_output.getStringVal());
4920 WSH_DEBUG_SV.log(l_module_name,'xpath:',G_XPATH_GET_SM_RESULT);
4921 END IF;
4922
4923 Fetch_XML_Match(
4924 p_xml=>p_xml_output,
4925 p_xpath=>G_XPATH_GET_SM_RESULT,
4926 x_xml_seq=>l_xml_seq,
4927 x_return_status=>l_return_status);
4928 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4929 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4930 IF l_debug_on
4931 THEN
4932
4933 WSH_DEBUG_SV.log(l_module_name,'Fetch_XML_Match Failed');
4934 END IF;
4935
4936 raise FND_API.G_EXC_ERROR;
4937 END IF;
4938
4939 i:=l_xml_seq.FIRST;
4940
4941 IF (i IS NOT NULL)
4942 THEN
4943
4944 IF l_debug_on
4945 THEN
4946 WSH_DEBUG_SV.log(l_module_name,'l_xml_seq(i)',l_xml_seq(i).getStringVal());
4947 WSH_DEBUG_SV.log(l_module_name,'xpath prefix:',G_XPATH_GET_SM_PREFIX);
4948 END IF;
4949
4950
4951 Extract_Ship_Method(
4952 p_xml=>l_xml_seq(i),
4953 p_xpath_prefix=>G_XPATH_GET_SM_PREFIX,
4954 p_action=>p_action,
4955 x_carrier_id=>l_carrier_id,
4956 x_mode=>l_mode,
4957 x_service_level=>l_service_level,
4958 x_freight_terms_code=>l_freight_terms,
4959 x_transit_time=>l_transit_time,
4960 x_transit_time_UOM=>l_transit_time_uom,
4961 x_return_status=>l_return_status);
4962
4963 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
4964 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
4965 IF l_debug_on
4966 THEN
4967
4968 WSH_DEBUG_SV.log(l_module_name,'Extract_Ship_Method Failed');
4969 END IF;
4970
4971 raise FND_API.G_EXC_ERROR;
4972 END IF;
4973
4974
4975
4976
4977
4978 IF l_debug_on
4979 THEN
4980 WSH_DEBUG_SV.log(l_module_name,'l_carrier_id',l_carrier_id);
4981 WSH_DEBUG_SV.log(l_module_name,'l_mode',l_mode);
4982 WSH_DEBUG_SV.log(l_module_name,'l_service_level',l_service_level);
4983 WSH_DEBUG_SV.log(l_module_name,'l_freight_terms',l_freight_terms);
4984 WSH_DEBUG_SV.log(l_module_name,'l_transit_time',l_transit_time);
4985 WSH_DEBUG_SV.log(l_module_name,'l_transit_time_uom',l_transit_time_uom);
4986
4987 END IF;
4988
4989
4990 Populate_Get_SM_Result(
4991 p_source_header_rec=>x_source_header_rec,
4992 p_carrier_id=>l_carrier_id,
4993 p_mode=>l_mode,
4994 p_service_level=>l_service_level,
4995 p_freight_terms_code=>l_freight_terms,
4996 p_transit_time=>l_transit_time,
4997 p_transit_time_UOM=>l_transit_time_uom,
4998 x_result_consolidation_id_tab=>x_result_consolidation_id_tab,
4999 x_result_carrier_id_tab=>x_result_carrier_id_tab,
5000 x_result_service_level_tab=>x_result_service_level_tab,
5001 x_result_mode_of_transport_tab=>x_result_mode_of_transport_tab,
5002 x_result_freight_term_tab=>x_result_freight_term_tab,
5003 x_result_transit_time_min_tab=>x_result_transit_time_min_tab,
5004 x_result_transit_time_max_tab=>x_result_transit_time_max_tab,
5005 x_ship_method_code_tab=>x_ship_method_code_tab,
5006 x_return_status=>l_return_status);
5007
5008 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5009 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5010 IF l_debug_on
5011 THEN
5012
5013 WSH_DEBUG_SV.log(l_module_name,'Populate_Get_SM_Result Failed');
5014 END IF;
5015
5016 raise FND_API.G_EXC_ERROR;
5017 END IF;
5018
5019 ELSE
5020 IF l_debug_on THEN
5021 WSH_DEBUG_SV.log(l_module_name,'Path does not exist',G_XPATH_GET_SM_RESULT);
5022
5023 END IF;
5024 raise FND_API.G_EXC_ERROR;
5025
5026
5027 END IF;
5028
5029 --
5030 IF l_debug_on THEN
5031 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
5032 WSH_DEBUG_SV.pop(l_module_name);
5033 END IF;
5034 --
5035 EXCEPTION
5036
5037 WHEN FND_API.G_EXC_ERROR THEN
5038 x_return_status := FND_API.G_RET_STS_ERROR;
5039
5040 IF l_debug_on THEN
5041 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5042 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
5043 END IF;
5044
5045 WHEN others THEN
5046 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5047 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Handle_Get_SM_Response',l_module_name);
5048 --
5049 IF l_debug_on THEN
5050 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5051 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5052 END IF;
5053 --
5054 END Handle_Get_SM_Response;
5055
5056
5057 PROCEDURE Handle_Get_FC_Response(
5058 p_xml_output IN XMLTYPE,
5059 p_source_type IN VARCHAR2,
5060 p_action IN VARCHAR2,
5061 p_source_header_rec IN FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_REC,
5062 p_source_line_tab IN FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_TAB,
5063 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_RATES_TAB,
5064 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_TAB,
5065 x_return_status OUT NOCOPY VARCHAR2)
5066 IS
5067
5068 l_summary_rate NUMBER;
5069 l_base_rate NUMBER;
5070 l_charge_rate NUMBER;
5071 l_tmp_xml XMLTYPE;
5072
5073 l_return_status VARCHAR2(1);
5074 l_debug_on BOOLEAN;
5075 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Handle_Get_FC_Response';
5076
5077 CURSOR c_get_currency(p_source_header_id number) IS
5078 SELECT transactional_curr_code
5079 FROM oe_order_headers_all
5080 WHERE header_id = p_source_header_id;
5081
5082 l_currency_code OE_ORDER_HEADERS_ALL.TRANSACTIONAL_CURR_CODE%TYPE := NULL;
5083
5084 -- exceptions related to getting currency_code from order table
5085 ORDER_NO_HEADER_ID_ERROR EXCEPTION;
5086 ORDER_NO_CURR_ERROR EXCEPTION;
5087
5088 BEGIN
5089 --
5090 --
5091 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5092 --
5093 IF l_debug_on IS NULL
5094 THEN
5095 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5096 END IF;
5097 --
5098 IF l_debug_on THEN
5099 WSH_DEBUG_SV.push(l_module_name);
5100 WSH_DEBUG_SV.log(l_module_name,'p_source_line_tab.count',p_source_line_tab.count);
5101 WSH_DEBUG_SV.log(l_module_name,'p_source_line_tab(p_source_line_tab.FIRST).source_header_id',
5102 p_source_line_tab(p_source_line_tab.FIRST).source_header_id);
5103 WSH_DEBUG_SV.log(l_module_name,'p_source_header_rec.currency',p_source_header_rec.currency);
5104 END IF;
5105 --
5106 x_return_status := FND_API.G_RET_STS_SUCCESS;
5107
5108 IF p_source_header_rec.currency IS NOT NULL THEN
5109 l_currency_code := p_source_header_rec.currency;
5110 ELSE
5111 IF (p_source_line_tab(p_source_line_tab.FIRST).source_header_id IS NULL) THEN
5112 raise ORDER_NO_HEADER_ID_ERROR;
5113 END IF;
5114 IF l_debug_on THEN
5115 WSH_DEBUG_SV.log(l_module_name, 'fetching c_get_currency');
5116 END IF;
5117
5118 OPEN c_get_currency(p_source_line_tab(p_source_line_tab.FIRST).source_header_id);
5119 FETCH c_get_currency INTO l_currency_code;
5120 CLOSE c_get_currency;
5121
5122 IF l_debug_on THEN
5123 WSH_DEBUG_SV.log(l_module_name, 'after fetch:', l_currency_code);
5124 END IF;
5125
5126 IF (l_currency_code IS NULL) THEN
5127 raise ORDER_NO_CURR_ERROR;
5128 END IF;
5129
5130 END IF;
5131
5132 l_tmp_xml:=p_xml_output.extract(G_XPATH_FREIGHT_COST_RESULT,g_xml_namespace_map);
5133 IF (l_tmp_xml IS NOT NULL)
5134 THEN
5135
5136 Extract_Rate(
5137 p_xml=>l_tmp_xml,
5138 p_xpath_prefix=>G_XPATH_FREIGHT_COST_PREFIX,
5139 p_currency=> l_currency_code,
5140 x_summary_rate=>l_summary_rate,
5141 x_base_rate=>l_base_rate,
5142 x_charge_rate=>l_charge_rate,
5143 x_return_status=>l_return_status);
5144 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5145 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5146 IF l_debug_on
5147 THEN
5148
5149 WSH_DEBUG_SV.log(l_module_name,'Extract_Ship_Method Failed');
5150 END IF;
5151
5152 raise FND_API.G_EXC_ERROR;
5153 END IF;
5154
5155 IF l_debug_on
5156 THEN
5157 WSH_DEBUG_SV.log(l_module_name,'l_summary_rate',l_summary_rate);
5158 WSH_DEBUG_SV.log(l_module_name,'l_base_rate',l_base_rate);
5159 WSH_DEBUG_SV.log(l_module_name,'l_charge_rate',l_charge_rate);
5160 END IF;
5161
5162
5163
5164 Populate_Get_FC_Result(
5165 p_summary_rate=>l_summary_rate,
5166 p_base_rate=>l_base_rate,
5167 p_charge_rate=>l_charge_rate,
5168 p_transit_time=>NULL,
5169 p_transit_time_UOM=>NULL,
5170 p_source_header_rec=>p_source_header_rec,
5171 p_source_line_tab=>p_source_line_tab,
5172 x_source_line_rates_tab=>x_source_line_rates_tab,
5173 x_source_header_rates_tab=>x_source_header_rates_tab,
5174 x_return_status=>l_return_status);
5175
5176 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5177 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5178 IF l_debug_on
5179 THEN
5180
5181 WSH_DEBUG_SV.log(l_module_name,'Populate_Get_FC_Result Failed');
5182 END IF;
5183
5184 raise FND_API.G_EXC_ERROR;
5185 END IF;
5186 ELSE
5187
5188 IF l_debug_on THEN
5189 WSH_DEBUG_SV.log(l_module_name,'Path does not exist',G_XPATH_FREIGHT_COST_RESULT);
5190
5191 END IF;
5192 raise FND_API.G_EXC_ERROR;
5193 END IF;
5194 --
5195 IF l_debug_on THEN
5196 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
5197 WSH_DEBUG_SV.pop(l_module_name);
5198 END IF;
5199 --
5200 EXCEPTION
5201 WHEN ORDER_NO_HEADER_ID_ERROR THEN
5202 x_return_status := FND_API.G_RET_STS_ERROR;
5203
5204 IF l_debug_on THEN
5205 WSH_DEBUG_SV.logmsg(l_module_name,'Order Header Id is null',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5206 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:ORDER_NO_HEADER_ID_ERROR');
5207 END IF;
5208
5209 WHEN ORDER_NO_CURR_ERROR THEN
5210 x_return_status := FND_API.G_RET_STS_ERROR;
5211
5212 IF l_debug_on THEN
5213 WSH_DEBUG_SV.logmsg(l_module_name,'Transactional Curr Code is not defined for the order.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5214 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:ORDER_NO_CURR_ERROR');
5215 END IF;
5216
5217 WHEN FND_API.G_EXC_ERROR THEN
5218 x_return_status := FND_API.G_RET_STS_ERROR;
5219
5220 IF l_debug_on THEN
5221 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5222 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
5223 END IF;
5224
5225 WHEN others THEN
5226 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5227 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Handle_Get_FC_Response',l_module_name);
5228 -- in case the exception occured while working on
5229 -- cursor c_get_currency
5230 IF c_get_currency%ISOPEN THEN
5231 CLOSE c_get_currency;
5232 END IF;
5233 --
5234 IF l_debug_on THEN
5235 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5236 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5237 END IF;
5238 --
5239 END Handle_Get_FC_Response;
5240
5241 PROCEDURE Handle_Choose_SM_Response(
5242 p_xml_output IN XMLTYPE,
5243 p_source_type IN VARCHAR2,
5244 p_action IN VARCHAR2,
5245 p_source_header_rec IN FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_REC,
5246 p_source_line_tab IN FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_TAB,
5247 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_RATES_TAB,
5248 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_TAB,
5249 x_return_status OUT NOCOPY VARCHAR2)
5250 IS
5251
5252 l_SM_failure VARCHAR2(1);
5253 l_summary_rate NUMBER;
5254 l_base_rate NUMBER;
5255 l_charge_rate NUMBER;
5256
5257 l_carrier_id NUMBER;
5258 l_mode VARCHAR2(30);
5259 l_service_level VARCHAR2(30);
5260 l_freight_terms VARCHAR2(30);
5261 l_transit_time NUMBER;
5262 l_transit_time_UOM VARCHAR2(30);
5263
5264 i NUMBER;
5265 l_xml_seq XMLSequenceType;
5266 l_sm_tab WSH_SM_TAB;
5267 l_sm_rec_empty WSH_SM_REC;
5268 l_sm_rec WSH_SM_REC;
5269 l_sm_rec_hash WSH_SM_REC;
5270 l_sm_rec_i WSH_SM_REC;
5271 l_sm_i VARCHAR2(30);
5272 l_ship_method_code VARCHAR2(30);
5273
5274 l_return_status VARCHAR2(1);
5275 l_debug_on BOOLEAN;
5276 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Handle_Choose_SM_Response';
5277
5278
5279
5280 BEGIN
5281 --
5282 --
5283 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5284 --
5285 IF l_debug_on IS NULL
5286 THEN
5287 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5288 END IF;
5289 --
5290 IF l_debug_on THEN
5291 WSH_DEBUG_SV.push(l_module_name);
5292 END IF;
5293 --
5294 x_return_status := FND_API.G_RET_STS_SUCCESS;
5295
5296
5297 Fetch_XML_Match(
5298 p_xml=>p_xml_output,
5299 p_xpath=>G_XPATH_SM_OPTION,
5300 x_xml_seq=>l_xml_seq,
5301 x_return_status=>l_return_status);
5302
5303 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5304 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5305 IF l_debug_on
5306 THEN
5307
5308 WSH_DEBUG_SV.log(l_module_name,'Fetch_XML_Match Failed');
5309 END IF;
5310
5311 raise FND_API.G_EXC_ERROR;
5312 END IF;
5313
5314
5315 i:=l_xml_seq.FIRST;
5316 WHILE (i IS NOT NULL)
5317 LOOP
5318 l_SM_failure:='N';
5319 l_sm_rec:=l_sm_rec_empty;
5320
5321 Extract_Ship_Method(
5322 p_xml=>l_xml_seq(i),
5323 p_xpath_prefix=>G_XPATH_SM_OPTION_PREFIX,
5324 p_action=>p_action,
5325 x_carrier_id=>l_sm_rec.carrier_id,
5326 x_mode=>l_sm_rec.mode_of_transport,
5327 x_service_level=>l_sm_rec.service_level,
5328 x_freight_terms_code=>l_sm_rec.freight_terms,
5329 x_transit_time=>l_sm_rec.transit_time,
5330 x_transit_time_UOM=>l_sm_rec.transit_time_uom,
5331 x_return_status=>l_return_status);
5332
5333 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5334 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5335 IF l_debug_on
5336 THEN
5337 WSH_DEBUG_SV.log(l_module_name,'Extract_Ship_Method Failed');
5338 END IF;
5339 l_SM_failure:='Y';
5340
5341 --raise FND_API.G_EXC_ERROR;
5342
5343
5344 END IF;
5345
5346 IF ((l_sm_rec.carrier_id IS NULL) OR (l_sm_rec.mode_of_transport IS NULL) OR (l_sm_rec.service_level IS NULL))
5347 THEN
5348 IF l_debug_on
5349 THEN
5350 WSH_DEBUG_SV.log(l_module_name,'Missing a Ship Method component');
5351 END IF;
5352 l_SM_failure:='Y';
5353 END IF;
5354
5355
5356
5357 IF l_debug_on
5358 THEN
5359 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.carrier_id',l_sm_rec.carrier_id);
5360 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.mode_of_transport',l_sm_rec.mode_of_transport);
5361 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.service_level',l_sm_rec.service_level);
5362 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.freight_terms',l_sm_rec.freight_terms);
5363 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.transit_time',l_sm_rec.transit_time);
5364 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.transit_time_uom',l_sm_rec.transit_time_uom);
5365
5366 END IF;
5367
5368 IF(l_SM_failure='N')
5369 THEN
5370 Get_Ship_Method_Code(
5371 p_org_id=>p_source_header_rec.ship_from_org_id,
5372 p_carrier_id=>l_sm_rec.carrier_id,
5373 p_mode=>l_sm_rec.mode_of_transport,
5374 p_service_level=>l_sm_rec.service_level,
5375 x_ship_method_code=>l_sm_rec.ship_method_code,
5376 x_return_status=>l_return_status);
5377
5378 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5379 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5380
5381 IF l_debug_on
5382 THEN
5383 WSH_DEBUG_SV.log(l_module_name,'Get_Ship_Method_Code Failed');
5384
5385 END IF;
5386 l_SM_failure:='Y';
5387 END IF;
5388
5389 IF l_debug_on
5390 THEN
5391 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.ship_method_code',l_sm_rec.ship_method_code);
5392 END IF;
5393
5394 END IF;
5395
5396 IF(l_SM_failure='N')
5397 THEN
5398 Extract_Rate(
5399 p_xml=>l_xml_seq(i),
5400 p_xpath_prefix=>G_XPATH_SM_OPTION_PREFIX,
5401 p_currency=>p_source_header_rec.currency,
5402 x_summary_rate=>l_sm_rec.summary_rate,
5403 x_base_rate=>l_sm_rec.base_rate,
5404 x_charge_rate=>l_sm_rec.charge_rate,
5405 x_return_status=>l_return_status);
5406 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5407 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5408 IF l_debug_on
5409 THEN
5410 WSH_DEBUG_SV.log(l_module_name,'Extract_Rate Failed');
5411 END IF;
5412
5413 raise FND_API.G_EXC_ERROR;
5414 END IF;
5415
5416
5417 IF l_debug_on
5418 THEN
5419 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.summary_rate',l_sm_rec.summary_rate);
5420 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.base_rate',l_sm_rec.base_rate);
5421 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.charge_rate',l_sm_rec.charge_rate);
5422 END IF;
5423
5424
5425 Validate_Transit_Time(
5426 x_transit_time=>l_sm_rec.transit_time,
5427 x_transit_time_uom=>l_sm_rec.transit_time_uom,
5428 x_return_status=>l_return_status);
5429 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5430 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5431 IF l_debug_on
5432 THEN
5433 WSH_DEBUG_SV.log(l_module_name,'Validate_Transit_Time Failed');
5434
5435 END IF;
5436 END IF;
5437
5438 IF l_debug_on
5439 THEN
5440 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.transit_time',l_sm_rec.transit_time);
5441 WSH_DEBUG_SV.log(l_module_name,'l_sm_rec.transit_time_uom',l_sm_rec.transit_time_uom);
5442 END IF;
5443
5444
5445
5446
5447 IF(l_sm_rec.ship_method_code IS NOT NULL)
5448 THEN
5449 IF(l_SM_tab.EXISTS(l_sm_rec.ship_method_code))
5450 THEN
5451
5452 l_sm_rec_hash:=l_SM_tab(l_sm_rec.ship_method_code);
5453
5454 IF (l_sm_rec.summary_rate < l_sm_rec_hash.summary_rate)
5455 THEN
5456
5457 l_SM_tab(l_sm_rec.ship_method_code):=l_sm_rec;
5458
5459 END IF;
5460 ELSE
5461
5462
5463 l_SM_tab(l_sm_rec.ship_method_code):=l_sm_rec;
5464
5465 END IF;
5466
5467 END IF;
5468
5469 END IF;
5470 i:=l_xml_seq.NEXT(i);
5471
5472 END LOOP;
5473
5474
5475 l_sm_i:=l_SM_tab.FIRST;
5476 WHILE(l_sm_i IS NOT NULL)
5477 LOOP
5478 l_sm_rec_i:=l_SM_tab(l_sm_i);
5479
5480 Populate_Choose_SM_Result(
5481 p_carrier_id=>l_sm_rec_i.carrier_id,
5482 p_mode=>l_sm_rec_i.mode_of_transport,
5483 p_service_level=>l_sm_rec_i.service_level,
5484 p_freight_terms_code=>l_sm_rec_i.freight_terms,
5485 p_transit_time=>l_sm_rec_i.transit_time,
5486 p_transit_time_UOM=>l_sm_rec_i.transit_time_uom,
5487 p_summary_rate=>l_sm_rec_i.summary_rate,
5488 p_base_rate=>l_sm_rec_i.base_rate,
5489 p_charge_rate=>l_sm_rec_i.charge_rate,
5490 p_ship_method_code=>l_sm_rec_i.ship_method_code,
5491 p_source_header_rec=>p_source_header_rec,
5492 p_source_line_tab=>p_source_line_tab,
5493 x_source_line_rates_tab=>x_source_line_rates_tab,
5494 x_source_header_rates_tab=>x_source_header_rates_tab,
5495 x_return_status=>l_return_status);
5496
5497 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5498 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5499 IF l_debug_on
5500 THEN
5501 WSH_DEBUG_SV.log(l_module_name,'Populate_Choose_SM_Result Failed');
5502 END IF;
5503
5504 raise FND_API.G_EXC_ERROR;
5505 END IF;
5506
5507 l_sm_i:=l_SM_tab.NEXT(l_sm_i);
5508
5509 END LOOP;
5510
5511 --Sort header by rates
5512
5513 Sort(
5514 x_source_header_rates_tab=>x_source_header_rates_tab,
5515 x_return_status=>l_return_status);
5516
5517 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5518 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5519 IF l_debug_on
5520 THEN
5521
5522 WSH_DEBUG_SV.log(l_module_name,'Sort Failed');
5523 END IF;
5524
5525 raise FND_API.G_EXC_ERROR;
5526 END IF;
5527
5528 IF (x_source_header_rates_tab.COUNT = 0)
5529 THEN
5530 IF l_debug_on
5531 THEN
5532
5533 WSH_DEBUG_SV.log(l_module_name,'No Valid results for Choos Ship Method');
5534 END IF;
5535
5536
5537 raise FND_API.G_EXC_ERROR;
5538
5539 END IF;
5540
5541 --
5542 IF l_debug_on THEN
5543 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
5544 WSH_DEBUG_SV.pop(l_module_name);
5545 END IF;
5546 --
5547 EXCEPTION
5548
5549 WHEN FND_API.G_EXC_ERROR THEN
5550 x_return_status := FND_API.G_RET_STS_ERROR;
5551
5552 IF l_debug_on THEN
5553 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5554 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
5555 END IF;
5556
5557 WHEN others THEN
5558 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5559 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Handle_Choose_SM_Response',l_module_name);
5560 --
5561 IF l_debug_on THEN
5562 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5563 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5564 END IF;
5565 --
5566 END Handle_Choose_SM_Response;
5567
5568
5569 PROCEDURE Parse_RIQ_Output_XML(
5570 p_xml_output IN XMLTYPE,
5571 p_source_type IN VARCHAR2,
5572 p_action IN VARCHAR2,
5573 x_source_header_rec IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_REC,
5574 x_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_TAB,
5575 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_RATES_TAB,
5576 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_TAB,
5577 x_result_consolidation_id_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
5578 x_result_carrier_id_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
5579 x_result_service_level_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
5580 x_result_mode_of_transport_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
5581 x_result_freight_term_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
5582 x_result_transit_time_min_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
5583 x_result_transit_time_max_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
5584 x_ship_method_code_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
5585 x_return_status OUT NOCOPY VARCHAR2)
5586 IS
5587
5588 l_xpath_status_prefix VARCHAR2(200);
5589 l_return_status VARCHAR2(1);
5590 l_debug_on BOOLEAN;
5591 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Parse_RIQ_Output_XML';
5592
5593
5594
5595 BEGIN
5596 --
5597 --
5598 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5599 --
5600 IF l_debug_on IS NULL
5601 THEN
5602 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5603 END IF;
5604 --
5605 IF l_debug_on THEN
5606 WSH_DEBUG_SV.push(l_module_name);
5607 END IF;
5608 --
5609 x_return_status := FND_API.G_RET_STS_SUCCESS;
5610
5611 IF(p_xml_output IS NOT NULL)
5612 THEN
5613 --g_xml_namespace_map:=p_xml_output.getNameSpace();
5614 IF l_debug_on
5615 THEN
5616
5617 WSH_DEBUG_SV.log(l_module_name,'Namespace',g_xml_namespace_map);
5618 END IF;
5619
5620
5621 END IF;
5622
5623 IF((p_action IS NOT NULL) AND (p_action='C'))
5624 THEN
5625 l_xpath_status_prefix:=G_XPATH_SM_STATUS_PREFIX;
5626 ELSE
5627 l_xpath_status_prefix:=G_XPATH_RIQ_STATUS_PREFIX;
5628 END IF;
5629
5630
5631 Check_Response_Status(
5632 p_xpath_prefix=>l_xpath_status_prefix,
5633 p_xml_output=>p_xml_output,
5634 x_return_status=>l_return_status);
5635 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5636 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5637 IF l_debug_on
5638 THEN
5639
5640 WSH_DEBUG_SV.log(l_module_name,'Check_Response_Status Failed');
5641 END IF;
5642
5643 raise FND_API.G_EXC_ERROR;
5644 END IF;
5645
5646
5647
5648 IF (p_action = 'GET_RATE_CHOICE')
5649 THEN
5650 Handle_Choose_SM_Response(
5651 p_xml_output=>p_xml_output,
5652 p_source_type=>p_source_type,
5653 p_action=>p_action,
5654 p_source_header_rec=>x_source_header_rec,
5655 p_source_line_tab=>x_source_line_tab,
5656 x_source_line_rates_tab=>x_source_line_rates_tab,
5657 x_source_header_rates_tab=>x_source_header_rates_tab,
5658 x_return_status=>l_return_status);
5659
5660 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5661 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5662 IF l_debug_on
5663 THEN
5664
5665 WSH_DEBUG_SV.log(l_module_name,'Handle_Choose_SM_Response Failed');
5666 END IF;
5667
5668 raise FND_API.G_EXC_ERROR;
5669 END IF;
5670
5671
5672 ELSIF(p_action='R')
5673 THEN
5674 Handle_Get_FC_Response(
5675 p_xml_output=>p_xml_output,
5676 p_source_type=>p_source_type,
5677 p_action=>p_action,
5678 p_source_header_rec=>x_source_header_rec,
5679 p_source_line_tab=>x_source_line_tab,
5680 x_source_line_rates_tab=>x_source_line_rates_tab,
5681 x_source_header_rates_tab=>x_source_header_rates_tab,
5682 x_return_status=>l_return_status);
5683
5684 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5685 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5686 IF l_debug_on
5687 THEN
5688
5689 WSH_DEBUG_SV.log(l_module_name,'Handle_Get_FC_Response Failed');
5690 END IF;
5691
5692 raise FND_API.G_EXC_ERROR;
5693 END IF;
5694
5695
5696 ELSIF (p_action= 'C')
5697 THEN
5698
5699 Handle_Get_SM_Response(
5700 p_xml_output=>p_xml_output,
5701 p_source_type=>p_source_type,
5702 p_action=>p_action,
5703 x_source_header_rec=>x_source_header_rec,
5704 x_result_consolidation_id_tab=>x_result_consolidation_id_tab,
5705 x_result_carrier_id_tab=>x_result_carrier_id_tab,
5706 x_result_service_level_tab=>x_result_service_level_tab,
5707 x_result_mode_of_transport_tab=>x_result_mode_of_transport_tab,
5708 x_result_freight_term_tab=>x_result_freight_term_tab,
5709 x_result_transit_time_min_tab=>x_result_transit_time_min_tab,
5710 x_result_transit_time_max_tab=>x_result_transit_time_max_tab,
5711 x_ship_method_code_tab=>x_ship_method_code_tab,
5712 x_return_status=>l_return_status);
5713
5714 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
5715 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
5716 IF l_debug_on
5717 THEN
5718
5719 WSH_DEBUG_SV.log(l_module_name,'Handle_Get_FC_Response Failed');
5720 END IF;
5721
5722 raise FND_API.G_EXC_ERROR;
5723 END IF;
5724
5725
5726 END IF;
5727
5728
5729
5730 --
5731 IF l_debug_on THEN
5732 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
5733 WSH_DEBUG_SV.pop(l_module_name);
5734 END IF;
5735 --
5736 EXCEPTION
5737
5738 WHEN FND_API.G_EXC_ERROR THEN
5739 x_return_status := FND_API.G_RET_STS_ERROR;
5740
5741 IF l_debug_on THEN
5742 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5743 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
5744 END IF;
5745
5746 WHEN others THEN
5747 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5748 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Parse_RIQ_Output_XML',l_module_name);
5749 --
5750 IF l_debug_on THEN
5751 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5752 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5753 END IF;
5754 --
5755 END Parse_RIQ_Output_XML;
5756
5757 PROCEDURE Init_Globals(
5758 x_return_status OUT NOCOPY VARCHAR2)
5759 IS
5760
5761 CURSOR c_get_global_time_class
5762 IS
5763 SELECT gu_time_class
5764 FROM WSH_GLOBAL_PARAMETERS;
5765
5766 CURSOR c_get_price_cost_type_id
5767 IS
5768 SELECT freight_cost_type_id
5769 FROM WSH_FREIGHT_COST_TYPES
5770 WHERE name='PRICE' AND freight_cost_type_code='FTEPRICE';
5771 l_return_status VARCHAR2(1);
5772
5773 --
5774 l_debug_on BOOLEAN;
5775
5776 --
5777 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Init_Globals';
5778 --
5779 BEGIN
5780 --
5781 --
5782 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5783 --
5784 IF l_debug_on IS NULL
5785 THEN
5786 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5787 END IF;
5788 --
5789 IF l_debug_on THEN
5790 WSH_DEBUG_SV.push(l_module_name);
5791 END IF;
5792 --
5793 x_return_status := FND_API.G_RET_STS_SUCCESS;
5794
5795 --Clean up cache
5796 g_carrier_freight_codes.DELETE;
5797 g_carrier_generic_flags.DELETE;
5798
5799 g_price_cost_type_id:=NULL;
5800
5801 OPEN c_get_price_cost_type_id;
5802 FETCH c_get_price_cost_type_id INTO g_price_cost_type_id;
5803 CLOSE c_get_price_cost_type_id;
5804
5805 g_domain_name:=FND_PROFILE.Value('WSH_OTM_DOMAIN_NAME');
5806
5807 --Servlet URI, raise the same message as in WSHGLHUB.pls
5808 g_servlet_uri := FND_PROFILE.VALUE('WSH_OTM_SERVLET_URI');
5809
5810 --User/Pwd, raise the same message as in WSHGLHUB.pls
5811 g_user_name:=FND_PROFILE.Value('WSH_OTM_USER_ID');
5812 g_password:=FND_PROFILE.Value('WSH_OTM_PASSWORD');
5813
5814 IF ((g_servlet_uri IS NULL) OR (g_user_name IS NULL) OR (g_password IS NULL)) THEN--{
5815
5816 IF l_debug_on THEN
5817 WSH_DEBUG_SV.log(l_module_name,'OTM Servlet URI ',g_servlet_uri);
5818 WSH_DEBUG_SV.log(l_module_name,'user ',g_user_name);
5819 WSH_DEBUG_SV.log(l_module_name,'password ',g_password);
5820 END IF;
5821
5822 IF g_servlet_uri IS NULL THEN
5823 FND_MESSAGE.SET_NAME('WSH','WSH_PROFILE_NOT_SET_ERR');
5824 FND_MESSAGE.SET_TOKEN('PRF_NAME',fnd_message.get_string('WSH','WSH_OTM_SERVLET_URI'));
5825 FND_MSG_PUB.ADD;
5826 END IF;
5827
5828 IF g_user_name IS NULL THEN
5829 FND_MESSAGE.SET_NAME('WSH','WSH_PROFILE_NOT_SET_ERR');
5830 FND_MESSAGE.SET_TOKEN('PRF_NAME',fnd_message.get_string('WSH','WSH_OTM_USER_ID'));
5831 FND_MSG_PUB.ADD;
5832 END IF;
5833
5834 IF g_password IS NULL THEN
5835 FND_MESSAGE.SET_NAME('WSH','WSH_PROFILE_NOT_SET_ERR');
5836 FND_MESSAGE.SET_TOKEN('PRF_NAME',fnd_message.get_string('WSH','WSH_OTM_PASSWORD'));
5837 FND_MSG_PUB.ADD;
5838 END IF;
5839
5840 RAISE FND_API.G_EXC_ERROR;
5841 END IF;--}
5842
5843 --Time Zone
5844 g_timezone_code:=FND_TIMEZONES.get_server_timezone_code;
5845
5846 OPEN c_get_global_time_class;
5847 FETCH c_get_global_time_class INTO g_global_time_class;
5848 CLOSE c_get_global_time_class;
5849
5850 g_xml_namespace_map:=G_GLOG_NS_MAP;
5851
5852
5853 g_EBS_to_OTM_UOM_map.DELETE;
5854 g_OTM_to_EBS_UOM_map.DELETE;
5855
5856 --
5857 IF l_debug_on THEN
5858 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
5859 WSH_DEBUG_SV.pop(l_module_name);
5860 END IF;
5861 --
5862 EXCEPTION
5863
5864 WHEN FND_API.G_EXC_ERROR THEN
5865 x_return_status := FND_API.G_RET_STS_ERROR;
5866
5867 IF l_debug_on THEN
5868 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5869 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
5870 END IF;
5871
5872 WHEN others THEN
5873 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5874 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Init_Globals',l_module_name);
5875 --
5876 IF l_debug_on THEN
5877 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5878 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5879 END IF;
5880 --
5881 END Init_Globals;
5882
5883
5884 PROCEDURE Clear_Globals(
5885 x_return_status OUT NOCOPY VARCHAR2)
5886 IS
5887 l_return_status VARCHAR2(1);
5888
5889 --
5890 l_debug_on BOOLEAN;
5891
5892 --
5893 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Clear_Globals';
5894 --
5895 BEGIN
5896 --
5897 --
5898 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5899 --
5900 IF l_debug_on IS NULL
5901 THEN
5902 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5903 END IF;
5904 --
5905 IF l_debug_on THEN
5906 WSH_DEBUG_SV.push(l_module_name);
5907 END IF;
5908 --
5909 x_return_status := FND_API.G_RET_STS_SUCCESS;
5910
5911 --Clean up cache
5912 g_carrier_freight_codes.DELETE;
5913 g_carrier_generic_flags.DELETE;
5914
5915
5916 g_EBS_to_OTM_UOM_map.DELETE;
5917 g_OTM_to_EBS_UOM_map.DELETE;
5918
5919 --
5920 IF l_debug_on THEN
5921 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
5922 WSH_DEBUG_SV.pop(l_module_name);
5923 END IF;
5924 --
5925 EXCEPTION
5926
5927 WHEN FND_API.G_EXC_ERROR THEN
5928 x_return_status := FND_API.G_RET_STS_ERROR;
5929
5930 IF l_debug_on THEN
5931 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5932 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
5933 END IF;
5934
5935 WHEN others THEN
5936 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5937 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Clear_Globals',l_module_name);
5938 --
5939 IF l_debug_on THEN
5940 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
5941 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
5942 END IF;
5943 --
5944 END Clear_Globals;
5945
5946
5947 PROCEDURE Process_One_Header(
5948 x_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_TAB,
5949 x_source_header_rec IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_REC,
5950 x_source_header_rec_xml IN OUT NOCOPY WSH_OTM_RIQ_HEADER_REC,
5951 p_source_type IN VARCHAR2,
5952 p_action IN VARCHAR2,
5953 x_source_line_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_RATES_TAB,
5954 x_source_header_rates_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_TAB,
5955 x_result_consolidation_id_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
5956 x_result_carrier_id_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
5957 x_result_service_level_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
5958 x_result_mode_of_transport_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
5959 x_result_freight_term_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
5960 x_result_transit_time_min_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
5961 x_result_transit_time_max_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
5962 x_ship_method_code_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
5963 x_return_status OUT NOCOPY VARCHAR2)
5964 IS
5965 j NUMBER; -- Bug 6810844
5966 i NUMBER;
5967 l_source_header_tab_xml WSH_OTM_RIQ_HEADER_TAB;
5968 l_source_line_tab_xml WSH_OTM_RIQ_LINE_TAB;
5969 l_xmlCLOB CLOB;
5970 l_xml_input XMLTYPE;
5971 l_xml_output XMLTYPE;
5972 l_total_header_count NUMBER;
5973 l_failed_header_count NUMBER;
5974
5975 l_return_status VARCHAR2(1);
5976 l_msg_count NUMBER := 0;
5977 l_msg_data VARCHAR2(2000);
5978
5979 --
5980 l_debug_on BOOLEAN;
5981
5982 --
5983 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Process_One_Header';
5984 --
5985 BEGIN
5986 --
5987 --
5988 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5989 --
5990 IF l_debug_on IS NULL
5991 THEN
5992 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5993 END IF;
5994 --
5995 IF l_debug_on THEN
5996 WSH_DEBUG_SV.push(l_module_name);
5997 END IF;
5998 --
5999 x_return_status := FND_API.G_RET_STS_SUCCESS;
6000
6001
6002 l_source_line_tab_xml:= NEW WSH_OTM_RIQ_LINE_TAB();
6003 --Get a new line table for each header
6004 l_source_line_tab_xml.DELETE;
6005
6006 Format_Line_Input_For_Xml(
6007 x_source_header_xml_rec=>x_source_header_rec_xml,
6008 p_source_header_rec=>x_source_header_rec,
6009 p_source_line_tab=>x_source_line_tab,
6010 p_source_type=>p_source_type,
6011 p_action=>p_action,
6012 x_source_line_tab=>l_source_line_tab_xml,
6013 x_return_status=>l_return_status);
6014
6015 -- Bug 6175042: Updating x_source_header_rec and x_source_line_tab weights and volumes.
6016 x_source_header_rec.total_weight := x_source_header_rec_xml.totalweight;
6017 x_source_header_rec.total_volume := x_source_header_rec_xml.totalvolume;
6018
6019 -- Bug 6810844: assigning the weight and volume of lines for the corresponding consolidation id.
6020 i:=x_source_line_tab.FIRST;
6021 j:=i;
6022 WHILE(i IS NOT NULL)
6023 LOOP
6024 IF ((x_source_line_tab(i).consolidation_id IS NOT NULL )
6025 AND (x_source_header_rec.consolidation_id IS NOT NULL)
6026 AND (x_source_line_tab(i).consolidation_id = x_source_header_rec.consolidation_id)) THEN
6027
6028 x_source_line_tab(i).weight := l_source_line_tab_xml(j).weight;
6029 x_source_line_tab(i).volume := l_source_line_tab_xml(j).volume;
6030 j:= j+1;
6031
6032 END IF;
6033 i:=x_source_line_tab.NEXT(i);
6034 END LOOP;
6035
6036 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6037 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6038 IF l_debug_on
6039 THEN
6040
6041 WSH_DEBUG_SV.log(l_module_name,'Format_Line_Input_For_Xml Failed');
6042 END IF;
6043 raise FND_API.G_EXC_ERROR;
6044
6045 END IF;
6046
6047
6048 --Create XML Input for 1 header and the lines associated with it
6049 Create_RIQ_XML(
6050 p_source_line_tab=>l_source_line_tab_xml,
6051 p_source_header_rec=>x_source_header_rec_xml,
6052 p_source_type=>p_source_type,
6053 p_action=>p_action,
6054 x_xml_input=>l_xml_input,
6055 x_return_status=>l_return_status);
6056
6057 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6058 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6059 IF l_debug_on
6060 THEN
6061
6062 WSH_DEBUG_SV.log(l_module_name,'Create_RIQ_XML Failed');
6063 END IF;
6064
6065 raise FND_API.G_EXC_ERROR;
6066
6067 END IF;
6068
6069
6070 IF l_debug_on
6071 THEN
6072
6073 print_CLOB(p_CLOB=>l_xml_input.getClobVal(),
6074 x_return_status=>l_return_status);
6075
6076 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6077 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6078 IF l_debug_on
6079 THEN
6080 WSH_DEBUG_SV.log(l_module_name,'print_CLOB Failed');
6081 END IF;
6082
6083 raise FND_API.G_EXC_ERROR;
6084 END IF;
6085 END IF;
6086
6087
6088
6089 --Invoke the OTM Web Service get the response
6090
6091 IF l_debug_on
6092 THEN
6093
6094 WSH_DEBUG_SV.log(l_module_name,'Calling POST_REQUEST_TO_OTM');
6095 END IF;
6096
6097
6098 WSH_OTM_HTTP_UTL.POST_REQUEST_TO_OTM (
6099 p_request=>l_xml_input,
6100 x_response=>l_xmlCLOB,
6101 x_return_status=>l_return_status);
6102
6103 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6104 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6105 IF l_debug_on
6106 THEN
6107
6108 WSH_DEBUG_SV.log(l_module_name,'POST_REQUEST_TO_OTM Failed');
6109 END IF;
6110 raise FND_API.G_EXC_ERROR;
6111
6112 END IF;
6113
6114 IF ((l_xmlCLOB IS NULL) OR (DBMS_LOB.GETLENGTH(l_xmlCLOB) <= 0))
6115 THEN
6116
6117 IF l_debug_on
6118 THEN
6119
6120 WSH_DEBUG_SV.log(l_module_name,'Returned CLOB is invalid');
6121 END IF;
6122 raise FND_API.G_EXC_ERROR;
6123
6124 END IF;
6125
6126
6127 IF l_debug_on
6128 THEN
6129
6130 WSH_DEBUG_SV.log(l_module_name,'After Calling POST_REQUEST_TO_OTM');
6131 END IF;
6132
6133
6134 IF l_debug_on
6135 THEN
6136
6137 print_CLOB(p_CLOB=>l_xmlCLOB,
6138 x_return_status=>l_return_status);
6139
6140 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6141 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6142 IF l_debug_on
6143 THEN
6144 WSH_DEBUG_SV.log(l_module_name,'print_CLOB Failed');
6145 END IF;
6146
6147 raise FND_API.G_EXC_ERROR;
6148 END IF;
6149 END IF;
6150
6151 IF l_debug_on
6152 THEN
6153
6154 WSH_DEBUG_SV.log(l_module_name,'Before creating XMLType');
6155 END IF;
6156
6157
6158 l_xml_output:=NEW XMLTYPE(l_xmlCLOB);
6159
6160 IF l_debug_on
6161 THEN
6162
6163 WSH_DEBUG_SV.log(l_module_name,'After creating XMLType');
6164 END IF;
6165
6166
6167 Parse_RIQ_Output_XML(
6168 p_xml_output=>l_xml_output,
6169 p_source_type=>p_source_type,
6170 p_action=>p_action,
6171 x_source_header_rec=>x_source_header_rec,
6172 x_source_line_tab=>x_source_line_tab,
6173 x_source_line_rates_tab=>x_source_line_rates_tab,
6174 x_source_header_rates_tab=>x_source_header_rates_tab,
6175 x_result_consolidation_id_tab=>x_result_consolidation_id_tab,
6176 x_result_carrier_id_tab=>x_result_carrier_id_tab,
6177 x_result_service_level_tab=>x_result_service_level_tab,
6178 x_result_mode_of_transport_tab=>x_result_mode_of_transport_tab,
6179 x_result_freight_term_tab=>x_result_freight_term_tab,
6180 x_result_transit_time_min_tab=>x_result_transit_time_min_tab,
6181 x_result_transit_time_max_tab=>x_result_transit_time_max_tab,
6182 x_ship_method_code_tab=>x_ship_method_code_tab,
6183 x_return_status=>l_return_status);
6184
6185 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6186 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6187 IF l_debug_on
6188 THEN
6189 WSH_DEBUG_SV.log(l_module_name,'Parse_RIQ_Output_XML Failed');
6190 END IF;
6191
6192 raise FND_API.G_EXC_ERROR;
6193
6194 END IF;
6195
6196
6197
6198
6199 --Perform any freeing up of xmltype/CLOB resources
6200
6201
6202 --
6203 IF l_debug_on THEN
6204 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
6205 WSH_DEBUG_SV.pop(l_module_name);
6206 END IF;
6207 --
6208 EXCEPTION
6209
6210 WHEN FND_API.G_EXC_ERROR THEN
6211 x_return_status := FND_API.G_RET_STS_ERROR;
6212
6213 IF l_debug_on THEN
6214 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6215 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
6216 END IF;
6217
6218 WHEN others THEN
6219 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6220 wsh_util_core.default_handler('WSH_OTM_RIQ_XML.Process_One_Header',l_module_name);
6221 --
6222 IF l_debug_on THEN
6223 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6224 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6225 END IF;
6226 --
6227 END Process_One_Header;
6228
6229
6230 PROCEDURE CALL_OTM_FOR_OM(
6231 x_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_TAB,
6232 x_source_header_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_TAB,
6233 p_source_type IN VARCHAR2,
6234 p_action IN VARCHAR2,
6235 x_source_line_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_LINE_RATES_TAB,
6236 x_source_header_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.FTE_SOURCE_HEADER_RATES_TAB,
6237 x_result_consolidation_id_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
6238 x_result_carrier_id_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
6239 x_result_service_level_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
6240 x_result_mode_of_transport_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
6241 x_result_freight_term_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
6242 x_result_transit_time_min_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
6243 x_result_transit_time_max_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableNumbers,
6244 x_ship_method_code_tab IN OUT NOCOPY WSH_NEW_DELIVERY_ACTIONS.TableVarchar30,
6245 x_return_status OUT NOCOPY VARCHAR2,
6246 x_msg_count OUT NOCOPY NUMBER,
6247 x_msg_data OUT NOCOPY VARCHAR2)
6248 IS
6249
6250 i NUMBER;
6251 l_source_header_tab_xml WSH_OTM_RIQ_HEADER_TAB;
6252
6253 l_total_header_count NUMBER;
6254 l_failed_header_count NUMBER;
6255
6256 l_return_status VARCHAR2(1);
6257 l_msg_count NUMBER := 0;
6258 l_msg_data VARCHAR2(2000);
6259
6260 --OTM R12 Org-Specific
6261 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
6262 l_gc3_is_installed VARCHAR2(1);
6263
6264 --
6265 l_debug_on BOOLEAN;
6266
6267 --
6268 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CALL_OTM_FOR_OM';
6269 --
6270 BEGIN
6271 --
6272 --
6273 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
6274 --
6275 IF l_debug_on IS NULL
6276 THEN
6277 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
6278 END IF;
6279 --
6280 IF l_debug_on THEN
6281 WSH_DEBUG_SV.push(l_module_name);
6282 END IF;
6283 --
6284 x_return_status := FND_API.G_RET_STS_SUCCESS;
6285
6286 FND_MSG_PUB.initialize;
6287
6288 --ECO 5516007 changes start, FP(5573379) to R12
6289 -- Ensure the Ship Date and Arrival Dates are populated appropriately
6290 IF l_debug_on THEN
6291 WSH_DEBUG_SV.log(l_module_name,'Calling derive_riq_dates');
6292 END IF;
6293
6294 derive_riq_dates
6295 (x_source_line_tab => x_source_line_tab,
6296 x_source_header_tab => x_source_header_tab,
6297 x_return_status => l_return_status);
6298
6299 IF l_debug_on THEN
6300 WSH_DEBUG_SV.log(l_module_name,'Return Status after call to DERIVE_RIQ_DATES ', x_return_status );
6301 END IF;
6302
6303 -- Handle Return Status for Error or Unexpected error.
6304 IF l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR,
6305 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
6306 -- standard error raising, as used in this API
6307 RAISE FND_API.G_EXC_ERROR;
6308 END IF;
6309 --ECO 5516007 changes end, FP to R12
6310 /*Bug7329859 storing x_source_line_tab in a global table to use in API Format_Header_Input_For_Xml*/
6311 g_source_line_tab_temp.DELETE;
6312 g_source_line_tab_temp := x_source_line_tab;
6313
6314 IF (l_debug_on)
6315 THEN
6316 print_source_line_tab (
6317 p_source_line_tab=>x_source_line_tab,
6318 x_return_status=>l_return_status);
6319 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6320 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6321 IF l_debug_on
6322 THEN
6323 WSH_DEBUG_SV.log(l_module_name,'print_source_line_tab Failed');
6324 END IF;
6325 raise FND_API.G_EXC_ERROR;
6326 END IF;
6327
6328 print_source_header_tab (
6329 p_source_header_tab=>x_source_header_tab,
6330 x_return_status=>l_return_status);
6331 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6332 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6333 IF l_debug_on
6334 THEN
6335 WSH_DEBUG_SV.log(l_module_name,'print_source_header_tab Failed');
6336 END IF;
6337 raise FND_API.G_EXC_ERROR;
6338 END IF;
6339
6340 END IF;
6341
6342 Init_Globals(
6343 x_return_status=>l_return_status);
6344 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6345 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6346 IF l_debug_on
6347 THEN
6348 WSH_DEBUG_SV.log(l_module_name,'Init_Globals Failed');
6349 END IF;
6350 raise FND_API.G_EXC_ERROR;
6351 END IF;
6352
6353
6354 l_source_header_tab_xml:= NEW WSH_OTM_RIQ_HEADER_TAB();
6355
6356
6357 Format_Header_Input_For_Xml(
6358 p_source_header_tab=>x_source_header_tab,
6359 p_source_type=>p_source_type,
6360 p_action=>p_action,
6361 x_source_header_tab=>l_source_header_tab_xml,
6362 x_return_status=>l_return_status);
6363
6364 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6365 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6366 IF l_debug_on
6367 THEN
6368
6369 WSH_DEBUG_SV.log(l_module_name,'Format_Header_Input_For_Xml Failed');
6370 END IF;
6371
6372 raise FND_API.G_EXC_ERROR;
6373 END IF;
6374
6375 l_failed_header_count:=0;
6376 l_total_header_count:=l_source_header_tab_xml.COUNT;
6377 i:=l_source_header_tab_xml.FIRST;
6378 WHILE(i IS NOT NULL)
6379 LOOP
6380 --OTM R12 Org-Specific start . Added the check l_param_info.otm_enabled = 'Y and
6381 --the corresponding ELSE part.
6382 IF (nvl(l_param_info.organization_id,FND_API.G_MISS_NUM) <>
6383 x_source_header_tab(i).ship_from_org_id) THEN --{
6384 WSH_SHIPPING_PARAMS_PVT.Get(
6385 p_organization_id => x_source_header_tab(i).ship_from_org_id,
6386 x_param_info => l_param_info,
6387 x_return_status => l_return_status);
6388
6389 IF l_debug_on THEN
6390 WSH_DEBUG_SV.log(l_module_name,'After call to WSH_SHIPPING_PA'||
6391 'RAMS_PVT.Get l_return_status ',l_return_status);
6392 WSH_DEBUG_SV.log(l_module_name,'Ship param not defined for org',
6393 x_source_header_tab(i).ship_from_org_id);
6394 END IF;
6395 IF (l_return_status in (WSH_UTIL_CORE.G_RET_STS_ERROR,
6396 WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
6397 RAISE FND_API.G_EXC_ERROR;
6398 END IF;
6399 END IF; --}
6400 --OTM R12 End
6401
6402 IF l_param_info.otm_enabled = 'Y' THEN --{ --OTM R12 Org-Specific. Added the check
6403 Process_One_Header(
6404 x_source_line_tab=>x_source_line_tab,
6405 x_source_header_rec=>x_source_header_tab(i),
6406 x_source_header_rec_xml=>l_source_header_tab_xml(i),
6407 p_source_type=>p_source_type,
6408 p_action=>p_action,
6409 x_source_line_rates_tab=>x_source_line_rates_tab,
6410 x_source_header_rates_tab=>x_source_header_rates_tab,
6411 x_result_consolidation_id_tab=>x_result_consolidation_id_tab,
6412 x_result_carrier_id_tab=>x_result_carrier_id_tab,
6413 x_result_service_level_tab=>x_result_service_level_tab,
6414 x_result_mode_of_transport_tab=>x_result_mode_of_transport_tab,
6415 x_result_freight_term_tab=>x_result_freight_term_tab,
6416 x_result_transit_time_min_tab=>x_result_transit_time_min_tab,
6417 x_result_transit_time_max_tab=>x_result_transit_time_max_tab,
6418 x_ship_method_code_tab=>x_ship_method_code_tab,
6419 x_return_status=>l_return_status);
6420
6421 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6422 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6423 IF l_debug_on
6424 THEN
6425 WSH_DEBUG_SV.log(l_module_name,'Process_One_Header Failed');
6426 END IF;
6427 l_failed_header_count:=l_failed_header_count+1;
6428 --raise FND_API.G_EXC_ERROR;
6429 END IF;
6430 ELSE --OTM R12 Org-Specific. Added the else part.
6431 IF l_debug_on THEN
6432 WSH_DEBUG_SV.log(l_module_name,'OTM not enabled for organzation', x_source_header_tab(i).ship_from_org_id);
6433 END IF;
6434 l_failed_header_count:=l_failed_header_count+1;
6435 END IF; --}
6436 i:=l_source_header_tab_xml.NEXT(i);
6437 END LOOP;
6438
6439
6440 --Perform any post processing on the output
6441
6442 Clear_Globals(
6443 x_return_status=>l_return_status);
6444 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6445 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6446 IF l_debug_on
6447 THEN
6448 WSH_DEBUG_SV.log(l_module_name,'Clear_Globals Failed');
6449 END IF;
6450 raise FND_API.G_EXC_ERROR;
6451 END IF;
6452
6453 IF (l_debug_on)
6454 THEN
6455
6456
6457 print_source_line_tab (
6458 p_source_line_tab=>x_source_line_tab,
6459 x_return_status=>l_return_status);
6460 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6461 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6462 IF l_debug_on
6463 THEN
6464 WSH_DEBUG_SV.log(l_module_name,'print_source_line_tab Failed');
6465 END IF;
6466 raise FND_API.G_EXC_ERROR;
6467 END IF;
6468
6469 print_source_header_tab (
6470 p_source_header_tab=>x_source_header_tab,
6471 x_return_status=>l_return_status);
6472 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6473 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6474 IF l_debug_on
6475 THEN
6476 WSH_DEBUG_SV.log(l_module_name,'print_source_header_tab Failed');
6477 END IF;
6478 raise FND_API.G_EXC_ERROR;
6479 END IF;
6480
6481
6482 print_rates_tab (
6483 p_source_line_rates_tab=>x_source_line_rates_tab,
6484 p_source_header_rates_tab=>x_source_header_rates_tab,
6485 x_return_status=>l_return_status);
6486 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6487 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6488 IF l_debug_on
6489 THEN
6490 WSH_DEBUG_SV.log(l_module_name,'print_source_header_tab Failed');
6491 END IF;
6492 raise FND_API.G_EXC_ERROR;
6493 END IF;
6494
6495 print_CS_Results(
6496 p_result_consolidation_id_tab=>x_result_consolidation_id_tab,
6497 p_result_carrier_id_tab=>x_result_carrier_id_tab,
6498 p_result_service_level_tab=>x_result_service_level_tab,
6499 p_result_mode_of_transport_tab=>x_result_mode_of_transport_tab,
6500 p_result_freight_term_tab=>x_result_freight_term_tab,
6501 p_result_transit_time_min_tab=>x_result_transit_time_min_tab,
6502 p_result_transit_time_max_tab=>x_result_transit_time_max_tab,
6503 p_ship_method_code_tab=>x_ship_method_code_tab,
6504 x_return_status=>l_return_status);
6505 IF((l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) AND
6506 (l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING)) THEN
6507 IF l_debug_on
6508 THEN
6509 WSH_DEBUG_SV.log(l_module_name,'print_CS_Results Failed');
6510 END IF;
6511 raise FND_API.G_EXC_ERROR;
6512 END IF;
6513
6514
6515
6516 END IF;
6517
6518
6519 IF (l_failed_header_count = l_total_header_count)
6520 THEN
6521 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6522 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
6523 IF l_debug_on
6524 THEN
6525 FND_MESSAGE.SET_TOKEN('LOGFILE',WSH_DEBUG_SV.g_Dir||'/'||WSH_DEBUG_SV.g_File);
6526 ELSE
6527 FND_MESSAGE.SET_TOKEN('LOGFILE','');
6528 END IF;
6529
6530 FND_MSG_PUB.ADD;
6531 IF (p_action IS NOT NULL AND p_action='C')
6532 THEN
6533 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_CS_RESULT');
6534 ELSE
6535 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_FL');
6536 END IF;
6537
6538 FND_MSG_PUB.ADD;
6539 ELSIF (l_failed_header_count = 0)
6540 THEN
6541 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
6542
6543 ELSE -- partial failure
6544 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6545 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
6546 IF l_debug_on
6547 THEN
6548 FND_MESSAGE.SET_TOKEN('LOGFILE',WSH_DEBUG_SV.g_Dir||'/'||WSH_DEBUG_SV.g_File);
6549 ELSE
6550 FND_MESSAGE.SET_TOKEN('LOGFILE','');
6551 END IF;
6552
6553 FND_MSG_PUB.ADD;
6554 IF (p_action IS NOT NULL AND p_action='C')
6555 THEN
6556 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_CS_RESULT_W');
6557 ELSE
6558 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_SUC_W');
6559 END IF;
6560
6561 FND_MSG_PUB.ADD;
6562
6563 IF l_debug_on
6564 THEN
6565 WSH_DEBUG_SV.log(l_module_name, l_failed_header_count||' shipments out of '||l_total_header_count||' shipments failed.');
6566 END IF;
6567 END IF;
6568
6569
6570
6571
6572 FND_MSG_PUB.Count_And_Get
6573 (
6574 p_count => x_msg_count,
6575 p_data => x_msg_data,
6576 p_encoded => FND_API.G_FALSE
6577 );
6578 IF l_debug_on
6579 THEN
6580 WSH_DEBUG_SV.log(l_module_name,'x_msg_count',x_msg_count);
6581 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',x_msg_data);
6582 END IF;
6583
6584
6585 --
6586 IF l_debug_on THEN
6587 WSH_DEBUG_SV.log(l_module_name,'x_return_status',x_return_status);
6588 WSH_DEBUG_SV.pop(l_module_name);
6589 END IF;
6590 --
6591 EXCEPTION
6592
6593 WHEN FND_API.G_EXC_ERROR THEN
6594 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
6595 IF l_debug_on
6596 THEN
6597 FND_MESSAGE.SET_TOKEN('LOGFILE',WSH_DEBUG_SV.g_Dir||'/'||WSH_DEBUG_SV.g_File);
6598 ELSE
6599 FND_MESSAGE.SET_TOKEN('LOGFILE','');
6600 END IF;
6601 FND_MSG_PUB.ADD;
6602 IF (p_action IS NOT NULL AND p_action='C')
6603 THEN
6604 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_CS_RESULT');
6605 ELSE
6606 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_FL');
6607 END IF;
6608 FND_MSG_PUB.ADD;
6609 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6610 FND_MSG_PUB.Count_And_Get
6611 (
6612 p_count => x_msg_count,
6613 p_data => x_msg_data,
6614 p_encoded => FND_API.G_FALSE
6615 );
6616
6617 IF l_debug_on THEN
6618 WSH_DEBUG_SV.logmsg(l_module_name,'FND_API.G_EXC_ERROR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6619 WSH_DEBUG_SV.log(l_module_name,'x_msg_count',x_msg_count);
6620 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',x_msg_data);
6621 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_ERROR');
6622 END IF;
6623
6624 WHEN OTHERS THEN
6625 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
6626 IF l_debug_on
6627 THEN
6628 FND_MESSAGE.SET_TOKEN('LOGFILE',WSH_DEBUG_SV.g_Dir||'/'||WSH_DEBUG_SV.g_File);
6629 ELSE
6630 FND_MESSAGE.SET_TOKEN('LOGFILE','');
6631 END IF;
6632 FND_MSG_PUB.ADD;
6633 IF (p_action IS NOT NULL AND p_action='C')
6634 THEN
6635 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_CS_RESULT');
6636 ELSE
6637 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_RATE_MUL_OMDEL_FL');
6638 END IF;
6639
6640 FND_MSG_PUB.ADD;
6641 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6642 FND_MSG_PUB.Count_And_Get
6643 (
6644 p_count => x_msg_count,
6645 p_data => x_msg_data,
6646 p_encoded => FND_API.G_FALSE
6647 );
6648 --wsh_util_core.default_handler('WSH_OTM_RIQ_XML.CALL_OTM_FOR_OM',l_module_name);
6649 --
6650 IF l_debug_on THEN
6651 WSH_DEBUG_SV.log(l_module_name,'x_msg_count',x_msg_count);
6652 WSH_DEBUG_SV.log(l_module_name,'x_msg_data',x_msg_data);
6653
6654 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6655 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6656 END IF;
6657 --
6658 END CALL_OTM_FOR_OM;
6659
6660
6661 END WSH_OTM_RIQ_XML;