[Home] [Help]
PACKAGE BODY: APPS.FTE_PROCESS_REQUESTS
Source
1 PACKAGE BODY FTE_PROCESS_REQUESTS AS
2 /* $Header: FTEPRREB.pls 120.4 2007/01/03 19:46:57 parkhj noship $ */
3
4 Type TableVarchar2000 IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
5
6 --
7 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_PROCESS_REQUESTS';
8 --
9
10 g_object_id_tab WSH_NEW_DELIVERY_ACTIONS.TableNumbers;
11
12 -- -------------------------------------------------------------------------- --
13 -- --
14 -- NAME: GET_CUSTOMER_SITE --
15 -- TYPE: PROCEDURE --
16 -- --
17 -- PARAMETERS (IN): p_consolidation_id IN NUMBER --
18 -- p_source_line_tab IN --
19 -- FTE_PROCESS_REQUESTS.fte_source_line_tab --
20 -- x_customer_site_id OUT NOCOPY --
21 -- NUMBER --
22 -- x_return_status OUT NOCOPY VARCHAR2 --
23 -- --
24 -- DESCRIPTION: This procedure returns customer site id, for the --
25 -- given pseudo delivery(p_consolidation_id). --
26 -- -------------------------------------------------------------------------- --
27
28 PROCEDURE get_customer_site( p_consolidation_id IN NUMBER,
29 p_source_line_tab IN FTE_PROCESS_REQUESTS.fte_source_line_tab,
30 x_customer_site_id OUT NOCOPY NUMBER,
31 x_return_status OUT NOCOPY VARCHAR2)
32 IS
33
34 itr NUMBER;
35 cust_site_id NUMBER;
36 no_ship_to_site EXCEPTION;
37 mult_ship_to_site EXCEPTION;
38
39 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
40 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CUSTOMER_SITE';
41
42 BEGIN
43 --
44 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
45 --
46 IF l_debug_on THEN
47 wsh_debug_sv.push (l_module_name);
48 END IF;
49 --
50 cust_site_id := -1;
51
52 --
53 -- Get ship_to_site_id for consolidation id. Loop over p_souce_line_tab.
54 -- If multiple sites are found then throw error message.
55 --
56 itr := p_source_line_tab.FIRST;
57
58 IF (itr IS NOT NULL) THEN
59 LOOP
60 IF (p_source_line_tab(itr).consolidation_id = p_consolidation_id AND
61 p_source_line_tab(itr).ship_to_site_id IS NOT NULL)
62 THEN
63 IF (cust_site_id = -1) THEN
64 cust_site_id := p_source_line_tab(itr).ship_to_site_id;
65 ELSIF (cust_site_id <> p_source_line_tab(itr).ship_to_site_id) THEN
66 RAISE MULT_SHIP_TO_SITE;
67 END IF;
68 END IF;
69 EXIT WHEN itr = p_source_line_tab.LAST;
70 itr := p_source_line_tab.NEXT(itr);
71 END LOOP;
72 END IF;
73
74 IF (cust_site_id = -1) THEN
75 RAISE NO_SHIP_TO_SITE;
76 ELSE
77 x_customer_site_id := cust_site_id;
78 END IF;
79 --
80 IF l_debug_on THEN
81 wsh_debug_sv.pop(l_module_name);
82 END IF;
83 --
84
85 EXCEPTION
86 WHEN NO_SHIP_TO_SITE THEN
87 -- Delivery is not assoociated with a site.
88 FND_MESSAGE.SET_NAME('WSH','WSH_PDLVY_NO_SITE');
89 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
90 WSH_UTIL_CORE.add_message(x_return_status);
91 IF l_debug_on THEN
92 WSH_DEBUG_SV.pop(l_module_name);
93 END IF;
94 WHEN MULT_SHIP_TO_SITE THEN
95 -- Delivery is associated with multiple sites
96 FND_MESSAGE.SET_NAME('WSH','WSH_PDLVY_MULT_SITE');
97 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
98 WSH_UTIL_CORE.add_message(x_return_status);
99 IF l_debug_on THEN
100 WSH_DEBUG_SV.pop(l_module_name);
101 END IF;
102
103 WHEN OTHERS THEN
104 WSH_UTIL_CORE.default_handler('FTE_PROCESS_REQUEST.get_customer_site');
105 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
106 IF l_debug_on THEN
107 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
108 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
109 END IF;
110 END get_customer_site;
111
112 -- -------------------------------------------------------------------------- --
113 -- --
114 -- NAME: GET_SHIP_TO_CUSTOMER --
115 -- TYPE: PROCEDURE --
116 -- --
117 -- PARAMETERS (IN): p_dropoff_loc_id IN NUMBER --
118 -- p_ship_to_site IN NUMBER --
119 -- x_customer_id OUT NOCOPY NUMBER --
120 -- x_return_status OUT NOCOPY VARCHAR2 --
121 -- --
122 -- DESCRIPTION: This procedure returns ship to customer using dropoff --
123 -- location and ship to site. --
124 -- If dropoff location returns multiple customers then --
125 -- use ship to site to determine the customer. --
126 -- -------------------------------------------------------------------------- --
127
128 PROCEDURE get_ship_to_customer(p_dropoff_loc_id IN NUMBER,
129 p_ship_to_site IN NUMBER,
130 x_customer_id OUT NOCOPY NUMBER,
131 x_return_status OUT NOCOPY VARCHAR2)
132 IS
133
134
135 CURSOR c_get_shipto_cust_from_loc (p_location_id IN NUMBER) IS
136 SELECT hcas.cust_account_id
137 FROM wsh_locations wl,
138 hz_party_sites hps,
139 hz_cust_acct_sites_all hcas
140 WHERE wl.wsh_location_id = p_location_id
141 AND wl.location_source_code = 'HZ'
142 AND wl.source_location_id = hps.location_id
143 AND hps.party_site_id = hcas.party_site_id;
144
145 CURSOR c_get_shipto_cust_from_site (p_site_id IN NUMBER) IS
146 SELECT distinct hcas.cust_account_id
147 FROM hz_cust_site_uses_all hcsu,
148 hz_cust_acct_sites_all hcas
149 WHERE hcsu.cust_acct_site_id = hcas.cust_acct_site_id
150 AND hcsu.site_use_id = p_site_id;
151
152 l_cust_tab WSH_UTIL_CORE.ID_TAB_TYPE;
153 no_cust_for_loc EXCEPTION;
154 mult_cust_for_loc EXCEPTION;
155
156 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
157 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_SHIP_TO_CUSTOMER';
158
159 BEGIN
160 --
161 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
162 --
163 IF l_debug_on THEN
164 wsh_debug_sv.push (l_module_name);
165 END IF;
166 --
167 OPEN c_get_shipto_cust_from_loc(p_dropoff_loc_id);
168 FETCH c_get_shipto_cust_from_loc BULK COLLECT INTO l_cust_tab;
169 CLOSE c_get_shipto_cust_from_loc;
170
171 IF (l_cust_tab.COUNT=1) THEN
172 x_customer_id := l_cust_tab(l_cust_tab.FIRST);
173 ELSIF (l_cust_tab.COUNT = 0) THEN
174 RAISE NO_CUST_FOR_LOC;
175 ELSE
176 OPEN c_get_shipto_cust_from_site(p_ship_to_site);
177 FETCH c_get_shipto_cust_from_site BULK COLLECT INTO l_cust_tab;
178 CLOSE c_get_shipto_cust_from_site;
179
180 IF (l_cust_tab.COUNT=1) THEN
181 x_customer_id := l_cust_tab(l_cust_tab.FIRST);
182 ELSIF (l_cust_tab.COUNT=0) THEN
183 RAISE NO_CUST_FOR_LOC;
184 ELSE
185 RAISE MULT_CUST_FOR_LOC;
186 END IF;
187 END IF;
188 --
189 IF l_debug_on THEN
190 WSH_DEBUG_SV.pop(l_module_name);
191 END IF;
192 --
193 EXCEPTION
194 WHEN NO_CUST_FOR_LOC THEN
195 -- Location not associated with a customer.
196 FND_MESSAGE.SET_NAME('WSH','WSH_CUST_NO_LOC');
197 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
198 WSH_UTIL_CORE.add_message(x_return_status);
199 IF l_debug_on THEN
200 WSH_DEBUG_SV.pop(l_module_name);
201 END IF;
202
203 WHEN MULT_CUST_FOR_LOC THEN
204 -- Location associated with a multiple customers.
205 FND_MESSAGE.SET_NAME('WSH','WSH_CUST_MULT_LOC');
206 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
207 WSH_UTIL_CORE.add_message(x_return_status);
208 IF l_debug_on THEN
209 WSH_DEBUG_SV.pop(l_module_name);
210 END IF;
211
212 WHEN OTHERS THEN
213 WSH_UTIL_CORE.default_handler('FTE_PROCESS_REQUEST.get_ship_to_customer');
214 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
215
216 IF (c_get_shipto_cust_from_loc%ISOPEN) THEN
217 CLOSE c_get_shipto_cust_from_loc;
218 END IF;
219
220 IF (c_get_shipto_cust_from_site%ISOPEN) THEN
221 CLOSE c_get_shipto_cust_from_site;
222 END IF;
223
224 IF l_debug_on THEN
225 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
226 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
227 END IF;
228 END get_ship_to_customer;
229
230 -- -------------------------------------------------------------------------- --
231 -- --
232 -- NAME: FORMAT_CS_CALL --
233 -- TYPE: PROCEDURE --
234 -- --
235 -- PARAMETERS (IN): p_source_header_tab IN OUT NOCOPY --
236 -- FTE_PROCESS_REQUESTS.fte_source_header_tab --
237 -- p_source_line_tab IN OUT NOCOPY --
238 -- FTE_PROCESS_REQUESTS.fte_source_line_tab --
239 -- x_result_tab OUT NOCOPY --
240 -- FTE_ACS_PKG.fte_cs_result_rec_type --
241 -- x_return_message OUT VARCHAR2 --
242 -- x_return_status OUT VARCHAR2 --
243 -- --
244 -- PARAMETERS (IN OUT): none --
245 -- RETURN: none --
246 -- DESCRIPTION: This procedure takes input of table of consolidated --
247 -- line data that is formatted into a call to the --
248 -- Carrier Selection Search Engine. After the return from--
249 -- the search engine, if any results where found a call --
250 -- to get the ship method is made. Tables of result data --
251 -- and ship method codes are returned. --
252 -- --
253 -- CHANGE CONTROL LOG --
254 -- ------------------ --
255 -- --
256 -- DATE VERSION BY BUG DESCRIPTION --
257 -- ---------- ------- -------- ------- ---------------------------------- --
258 -- --
259 -- --
260 -- -------------------------------------------------------------------------- --
261 PROCEDURE FORMAT_CS_CALL( p_source_header_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_tab,
262 p_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_tab,
263 x_result_tab OUT NOCOPY FTE_ACS_PKG.fte_cs_result_tab_type,
264 x_return_message OUT NOCOPY VARCHAR2,
265 x_return_status OUT NOCOPY VARCHAR2) IS
266
267 -- New Header and Result Tab
268 l_cs_input_tab FTE_ACS_PKG.fte_cs_entity_tab_type;
269 l_cs_output_tab FTE_ACS_PKG.fte_cs_result_tab_type;
270 l_cs_output_message_tab FTE_ACS_PKG.fte_cs_output_message_tab;
271
272 l_customer_site_id NUMBER;
273 l_customer_id NUMBER;
274
275 l_return_status VARCHAR2(1); -- return status flag
276 l_return_message VARCHAR2(2000); -- return error message variable
277 l_param_rec WSH_SHIPPING_PARAMS_PVT.PARAMETER_VALUE_REC_TYP;
278
279 -- These variables are used as counters for table creation
280 l PLS_INTEGER := 0; -- variable used as a counter
281 k PLS_INTEGER := 0; -- Variable used as a counter
282
283 -- Variables used for error handling
284 l_error_code NUMBER; -- Oracle SQL Error Number
285 l_error_text VARCHAR2(2000); -- Oracle SQL Error Text
286 l_message_tab TableVarchar2000;
287
288 --
289 l_debug_on BOOLEAN;
290 --
291 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'FORMAT_CS_CALL';
292 --
293
294 BEGIN
295 --
296 -- Initialize the return flags
297 -- Debug Statements
298 --
299 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
300 --
301 IF l_debug_on IS NULL
302 THEN
303 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
304 END IF;
305 --
306 --
307 -- Debug Statements
308 --
309 IF l_debug_on THEN
310 WSH_DEBUG_SV.push(l_module_name);
311 END IF;
312 --
313 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
314 x_return_message := null;
315
316 l := 0;
317
318 -- for the consolidations passed in, get the information out so that we
319 -- can call the Carrier Selection Search Engine
320
321 FOR j IN p_source_header_tab.FIRST..p_source_header_tab.LAST LOOP
322
323 --Prepare the input record.
324
325 l_cs_input_tab(l).delivery_id := p_source_header_tab(j).consolidation_id;
326 l_cs_input_tab(l).delivery_name := NULL;
327 l_cs_input_tab(l).organization_id := p_source_header_tab(j).ship_from_org_id;
328 l_cs_input_tab(l).gross_weight := p_source_header_tab(j).total_weight;
329 l_cs_input_tab(l).weight_uom_code := p_source_header_tab(j).weight_uom_code;
330 l_cs_input_tab(l).volume := p_source_header_tab(j).total_volume;
331 l_cs_input_tab(l).volume_uom_code := p_source_header_tab(j).volume_uom_code;
332 l_cs_input_tab(l).initial_pickup_loc_id := p_source_header_tab(j).ship_from_location_id;
333 l_cs_input_tab(l).ultimate_dropoff_loc_id := p_source_header_tab(j).ship_to_location_id;
334 --
335 -- Determine the ship to site.
336 --
337 get_customer_site( p_consolidation_id => p_source_header_tab(j).consolidation_id,
338 p_source_line_tab => p_source_line_tab,
339 x_customer_site_id => l_customer_site_id,
340 x_return_status => l_return_status);
341
342 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
343 GOTO next_pass;
344 END IF;
345
346 l_cs_input_tab(l).customer_site_id := l_customer_site_id;
347
348 --
349 --Determine whether to use ship to customer or sold to customer.
350 --
351 l_param_rec.organization_id := p_source_header_tab(j).ship_from_org_id;
352 l_param_rec.param_name(1) := 'EVAL_RULE_BASED_ON_SHIPTO_CUST';
353
354 WSH_SHIPPING_PARAMS_PVT.Get(x_param_value_info => l_param_rec ,
355 x_return_status => l_return_status);
356
357 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
358 GOTO next_pass;
359 END IF;
360
361 --Evaluate Rules based on ship to customer
362 IF (l_param_rec.PARAM_VALUE_CHR.EXISTS(1) AND l_param_rec.PARAM_VALUE_CHR(1) = 'Y') THEN
363
364 get_ship_to_customer( p_dropoff_loc_id => p_source_header_tab(j).ship_to_location_id,
365 p_ship_to_site => l_customer_site_id,
366 x_customer_id => l_customer_id,
367 x_return_status => l_return_status);
368
369 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
370 GOTO next_pass;
371 END IF;
372 l_cs_input_tab(l).customer_id := l_customer_id;
373 ELSE
374 l_cs_input_tab(l).customer_id := p_source_header_tab(j).customer_id;
375 END IF;
376
377 l_cs_input_tab(l).freight_terms_code := p_source_header_tab(j).freight_terms;
378 l_cs_input_tab(l).initial_pickup_date := p_source_header_tab(j).ship_date;
379 l_cs_input_tab(l).ultimate_dropoff_date := p_source_header_tab(j).arrival_date;
380 l_cs_input_tab(l).fob_code := p_source_header_tab(j).fob_code;
381 l_cs_input_tab(l).transit_time := p_source_header_tab(j).delivery_lead_time;
382
383 l := l+1;
384
385 <<next_pass>>
386 null;
387 END LOOP;
388
389 --
390 -- Debug Statements
391 --
392 IF l_debug_on THEN
393 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_ACS_PKG.START_ACS',WSH_DEBUG_SV.C_PROC_LEVEL);
394 END IF;
395 --
396
397 FTE_ACS_PKG.GET_ROUTING_RESULTS( p_format_cs_tab => l_cs_input_tab,
398 p_entity => 'PSEUDO_DLVY',
399 p_messaging_yn => 'Y',
400 p_caller => 'ORDER_MGMT',
401 x_cs_output_tab => l_cs_output_tab,
402 x_cs_output_message_tab => l_cs_output_message_tab,
403 x_return_message => l_return_message,
404 x_return_status => l_return_status);
405
406 --
407 -- The engine returns the formatted results
408 --
409 IF ((l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS OR l_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING) AND
410 (l_cs_output_tab.COUNT > 0)) THEN
411 --
412 -- Return the results - We have rule id
413 -- Ship method is returned internally from the engine.
414 --
415 x_result_tab := l_cs_output_tab;
416 ELSE
417 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
418 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_CS_RESULT');
419 wsh_util_core.add_message(x_return_status);
420 x_return_message := FND_MESSAGE.GET;
421 END IF;
422
423 --
424 -- Return to the calling API
425 --
426 --
427 -- Debug Statements
428 --
429 IF l_debug_on THEN
430 WSH_DEBUG_SV.pop(l_module_name);
431 END IF;
432 --
433
434 EXCEPTION
435 WHEN OTHERS THEN
436 l_error_code := SQLCODE;
437 l_error_text := SQLERRM;
438 --
439 -- Debug Statements
440 --
441 IF l_debug_on THEN
442 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_PROCESS_REQUESTS.FORMAT_CS_CALL IS ' ||L_ERROR_TEXT );
443 END IF;
444 --
445
446 WSH_UTIL_CORE.default_handler('FTE_PROCESS_REQUESTS.FORMAT_CS_CALL');
447 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
448 x_return_message := l_error_text;
449 --
450 -- Debug Statements
451 --
452 IF l_debug_on THEN
453 WSH_DEBUG_SV.pop(l_module_name);
454 END IF;
455 --
456
457 --
458 -- Debug Statements
459 --
460 IF l_debug_on THEN
461 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
462 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
463 END IF;
464 --
465 END FORMAT_CS_CALL;
466
467
468
469 -- -------------------------------------------------------------------------- --
470 -- --
471 -- NAME: PROCESS_LINES --
472 -- TYPE: PROCEDURE --
473 -- --
474 -- PARAMETERS (IN): --
475 -- p_source_line_tab IN OUT NOCOPY --
476 -- FTE_PROCESS_REQUESTS.fte_source_line_tab --
477 -- p_source_header_tab IN OUT NOCOPY --
478 -- FTE_PROCESS_REQUESTS.fte_source_header_tab --
479 -- p_source_type IN --
480 -- VARCHAR2 --
481 -- p_action IN --
482 -- VARCHAR2 --
483 -- x_source_line_rates_tab OUT NOCOPY --
484 -- FTE_PROCESS_REQUESTS.fte_source_line_rates_tab --
485 -- x_source_header_rates_tab OUT NOCOPY --
486 -- FTE_PROCESS_REQUESTS.fte_source_header_rates_tab--
487 -- x_return_status OUT NOCOPY --
488 -- VARCHAR2 --
489 -- x_msg_count OUT NOCOPY --
490 -- NUMBER --
491 -- x_msg_data OUT NOCOPY --
492 -- VARCHAR2 --
493 -- --
494 -- DESCRIPTION: --
495 -- --
496 -- main procedure that will take in a table of source line inputs --
497 -- and consolidate them based on grouping rules and for each consolidation --
498 -- call cs engine wrapper, lane search, rating, as required by p_action --
499 -- --
500 -- CHANGE LOG --
501 -- ---------- --
502 -- [2003/02/05][PACK I][ABLUNDEL][BUG: 2769793] --
503 -- Freight terms on the order line was getting nulled out as --
504 -- the engine was returning null. Only change the freight term --
505 -- if one exists --
506 -- --
507 -- 2003/06/01 Pack J, xizhang added action GET_GROUP and GET_RATE_CHOICE --
508 -- added OUT parameter x_source_header_rates_tab --
509 -- --
510 -- -------------------------------------------------------------------------- --
511 PROCEDURE Process_Lines(p_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_tab,
512 p_source_header_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_tab,
513 p_source_type IN VARCHAR2,
514 p_action IN VARCHAR2,
515 x_source_line_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
516 x_source_header_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_rates_tab,
517 x_return_status OUT NOCOPY VARCHAR2,
518 x_msg_count OUT NOCOPY NUMBER,
519 x_msg_data OUT NOCOPY VARCHAR2) IS
520
521 l_orig_delivery_time NUMBER;
522 l_transit_time NUMBER;
523 l_transit_ship_method VARCHAR2(30);
524 l_session_id NUMBER;
525 l_transit_status VARCHAR2(1);
526
527 l_cs_transit_min NUMBER;
528 l_cs_transit_max NUMBER;
529
530 l_con_id PLS_INTEGER;
531 l_no_results VARCHAR2(1) := 'Y';
532
533 l_source_line_rates_tab FTE_PROCESS_REQUESTS.fte_source_line_rates_tab;
534 l_source_header_rates_tab FTE_PROCESS_REQUESTS.fte_source_header_rates_tab;
535 l_request_id NUMBER;
536
537 l_status VARCHAR2(1);
538 l_msg_count NUMBER := 0;
539 l_msg_data VARCHAR2(200);
540
541 l_ship_method_meaning VARCHAR2(80);
542 l_org_name VARCHAR2(80);
543 l_carrier_name VARCHAR2(360);
544 l_freight_code VARCHAR2(230);
545 l_rule_name VARCHAR2(80);
546
547 --R12 Glog Integration
548 l_otm_installed VARCHAR2(1);
549 l_result_consolidation_id_tab WSH_NEW_DELIVERY_ACTIONS.TableNumbers;
550 l_result_carrier_id_tab WSH_NEW_DELIVERY_ACTIONS.TableNumbers;
551 l_result_service_level_tab WSH_NEW_DELIVERY_ACTIONS.TableVarchar30;
552 l_result_mode_of_transport_tab WSH_NEW_DELIVERY_ACTIONS.TableVarchar30;
553 l_result_freight_term_tab WSH_NEW_DELIVERY_ACTIONS.TableVarchar30;
554 l_result_transit_time_min_tab WSH_NEW_DELIVERY_ACTIONS.TableNumbers;
555 l_result_transit_time_max_tab WSH_NEW_DELIVERY_ACTIONS.TableNumbers;
556 l_ship_method_code_tab WSH_NEW_DELIVERY_ACTIONS.TableVarchar30;
557
558 --R12 - Routing Rules Engine
559 l_result_tab FTE_ACS_PKG.FTE_CS_RESULT_TAB_TYPE;
560 l_return_message VARCHAR2(2000);
561
562 CURSOR get_session_id IS
563 SELECT mrp_atp_schedule_temp_s.nextVal
564 FROM dual;
565
566 CURSOR get_ship_method_meaning(p_ship_method_code VARCHAR2) IS
567 SELECT ship_method_meaning
568 FROM wsh_carrier_services
569 WHERE ship_method_code = p_ship_method_code;
570
571 CURSOR get_ship_method_meaning2(p_carrier_id NUMBER, p_mode VARCHAR2, p_service VARCHAR2) IS
572 SELECT ship_method_meaning
573 FROM wsh_carrier_services
574 WHERE carrier_id = p_carrier_id AND
575 mode_of_transport = p_mode AND
576 service_level = p_service;
577
578 CURSOR get_generic_sm(p_mode VARCHAR2, p_service VARCHAR2, p_org_id NUMBER) IS
579 SELECT wcs.ship_method_code
580 FROM wsh_org_carrier_services wocs,
581 wsh_carrier_services wcs,
582 wsh_carriers wc
583 WHERE wcs.service_level= p_service AND
584 wcs.mode_of_transport= p_mode AND
585 wcs.enabled_flag = 'Y' AND
586 wcs.carrier_service_id = wocs.carrier_service_id AND
587 wocs.organization_id = p_org_id AND
588 wocs.enabled_flag = 'Y' AND
589 wcs.carrier_id = wc.carrier_id AND
590 nvl(wc.generic_flag, 'N') = 'Y';
591
592 CURSOR get_generic_sm_on_mod(p_mode VARCHAR2, p_org_id NUMBER) IS
593 SELECT wcs.ship_method_code
594 FROM wsh_org_carrier_services wocs,
595 wsh_carrier_services wcs,
596 wsh_carriers wc
597 WHERE
598 wcs.mode_of_transport = p_mode AND
599 wcs.enabled_flag = 'Y' AND
600 wcs.carrier_service_id = wocs.carrier_service_id AND
601 wocs.organization_id = p_org_id AND
602 wocs.enabled_flag = 'Y' AND
603 wcs.carrier_id = wc.carrier_id AND
604 nvl(wc.generic_flag, 'N') = 'Y';
605
606 CURSOR get_generic_sm_on_ser( p_service VARCHAR2, p_org_id NUMBER) IS
607 SELECT wcs.ship_method_code
608 FROM wsh_org_carrier_services wocs,
609 wsh_carrier_services wcs,
610 wsh_carriers wc
611 WHERE wcs.service_level = p_service AND
612 wcs.enabled_flag = 'Y' AND
613 wcs.carrier_service_id = wocs.carrier_service_id AND
614 wocs.organization_id = p_org_id AND
615 wocs.enabled_flag = 'Y' AND
616 wcs.carrier_id = wc.carrier_id AND
617 nvl(wc.generic_flag, 'N') = 'Y';
618
619 --
620 l_debug_on BOOLEAN;
621 --
622 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_LINES';
623 --
624 BEGIN
625
626 --
627 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
628 --
629 IF l_debug_on IS NULL
630 THEN
631 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
632 END IF;
633 --
634 --
635 -- Debug Statements
636 --
637 IF l_debug_on THEN
638 WSH_DEBUG_SV.push(l_module_name);
639 --
640 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_TYPE',P_SOURCE_TYPE);
641 WSH_DEBUG_SV.log(l_module_name,'P_ACTION',P_ACTION);
642 END IF;
643 --
644 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
645
646 /* R12 Glog Integration */
647 -- determine if OTM is installed
648 l_otm_installed:=NULL;
649 l_otm_installed:=WSH_UTIL_CORE.GC3_Is_Installed;
650 IF ((l_otm_installed IS NOT NULL) AND (l_otm_installed='Y'))
651 THEN
652 l_otm_installed:='Y';
653 ELSE
654 l_otm_installed:='N';
655 END IF;
656
657 IF((p_action IS NOT NULL) AND (p_action = 'GET_ESTIMATE_RATE'))
658 THEN
659 l_otm_installed:='N';
660 END IF;
661
662 IF (p_source_line_tab.COUNT < 1) THEN
663 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
664 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_SOURCE_LINES');
665 x_msg_count := l_msg_count + 1;
666 x_msg_data := FND_MESSAGE.GET;
667 --
668 -- Debug Statements
669 --
670 IF l_debug_on THEN
671 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR returned ' || x_msg_data);
672 WSH_DEBUG_SV.pop(l_module_name);
673 END IF;
674 --
675 RETURN;
676 END IF;
677
678 IF (p_action = 'GET_RATE_CHOICE'
679 OR p_action = 'GET_ESTIMATE_RATE') -- FTE J FTE rate estimate
680 THEN
681
682 -- this action requires group being passed in
683 IF (p_source_header_tab.COUNT < 1) THEN
684
685 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
686 FND_MESSAGE.SET_NAME('FTE', 'FTE_PRC_RATE_CHOICE_ERR1');
687 x_msg_count := l_msg_count + 1;
688 x_msg_data := FND_MESSAGE.GET;
689 --
690 -- Debug Statements
691 --
692 IF l_debug_on THEN
693 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR returned ' || x_msg_data);
694 WSH_DEBUG_SV.pop(l_module_name);
695 END IF;
696 --
697 RETURN;
698
699 ELSE -- p_source_header_tab.COUNT = 1
700
701 IF (l_otm_installed = 'N') THEN
702
703 IF l_debug_on THEN
704 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_FREIGHT_RATING_PUB.GET_FREIGHT_COSTS',WSH_DEBUG_SV.C_PROC_LEVEL);
705 END IF;
706 FTE_FREIGHT_RATING_PUB.Get_Freight_Costs(p_api_version => 1,
707 p_source_line_tab => p_source_line_tab,
708 p_source_header_tab => p_source_header_tab,
709 p_source_type => p_source_type,
710 p_action => p_action,
711 x_source_line_rates_tab => l_source_line_rates_tab,
712 x_source_header_rates_tab => l_source_header_rates_tab,
713 x_request_id => l_request_id,
714 x_return_status => l_status,
715 x_msg_count => l_msg_count,
716 x_msg_data => l_msg_data);
717 ELSE
718 IF l_debug_on THEN
719 WSH_DEBUG_SV.logmsg(l_module_name,'currency for choose ship method is '||p_source_header_tab(p_source_header_tab.FIRST).currency);
720 END IF;
721 WSH_OTM_RIQ_XML.CALL_OTM_FOR_OM(
722 x_source_line_tab => p_source_line_tab,
723 x_source_header_tab => p_source_header_tab,
724 p_source_type => p_source_type,
725 p_action => p_action,
726 x_source_line_rates_tab => l_source_line_rates_tab,
727 x_source_header_rates_tab => l_source_header_rates_tab,
728 x_result_consolidation_id_tab => l_result_consolidation_id_tab,
729 x_result_carrier_id_tab => l_result_carrier_id_tab,
730 x_result_service_level_tab => l_result_service_level_tab,
731 x_result_mode_of_transport_tab => l_result_mode_of_transport_tab,
732 x_result_freight_term_tab => l_result_freight_term_tab,
733 x_result_transit_time_min_tab => l_result_transit_time_min_tab,
734 x_result_transit_time_max_tab => l_result_transit_time_max_tab,
735 x_ship_method_code_tab => l_ship_method_code_tab,
736 x_return_status => l_status,
737 x_msg_count => l_msg_count,
738 x_msg_data => l_msg_data);
739 END IF;
740
741 IF (l_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
742 x_source_line_rates_tab := l_source_line_rates_tab;
743 x_source_header_rates_tab := l_source_header_rates_tab;
744 END IF;
745
746 x_return_status := l_status;
747 x_msg_count := l_msg_count;
748 x_msg_data := l_msg_data;
749
750 END IF; -- p_source_header_tab.COUNT = 1
751
752 ELSE -- p_action <> 'GET_RATE_CHOICE'
753
754 -- consolidate the lines and sum up weight and volume if not already consolidated
755 IF (p_source_header_tab.COUNT = 0) THEN
756 --
757 -- Debug Statements
758 --
759 IF l_debug_on THEN
760 WSH_DEBUG_SV.logmsg(l_module_name,'p_source_header_tab.COUNT = 0');
761 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_SOURCE_LINE_CONSOLIDATION.CONSOLIDATE_LINES',WSH_DEBUG_SV.C_PROC_LEVEL);
762 END IF;
763 --
764 FTE_SOURCE_LINE_CONSOLIDATION.Consolidate_Lines(p_source_line_tab,
765 p_source_header_tab,
766 p_action,
767 l_status,
768 l_msg_count,
769 l_msg_data);
770
771 --
772 -- Debug Statements
773 --
774 IF l_debug_on THEN
775 WSH_DEBUG_SV.logmsg(l_module_name,'-------- Consolidations -----------');
776 IF (p_source_header_tab.COUNT > 0) THEN
777 FOR zz IN p_source_header_tab.FIRST..p_source_header_tab.LAST LOOP
778 WSH_DEBUG_SV.logmsg(l_module_name,'*********************************');
779 WSH_DEBUG_SV.log(l_module_name, 'consolidation_id', p_source_header_tab(zz).consolidation_id);
780 WSH_DEBUG_SV.log(l_module_name, 'total_weight', p_source_header_tab(zz).total_weight);
781 WSH_DEBUG_SV.log(l_module_name, 'weight_uom_code', p_source_header_tab(zz).weight_uom_code);
782 WSH_DEBUG_SV.log(l_module_name, 'total_volume', p_source_header_tab(zz).total_volume);
783 WSH_DEBUG_SV.log(l_module_name, 'volume_uom_code', p_source_header_tab(zz).volume_uom_code);
784 END LOOP;
785 ELSE
786 WSH_DEBUG_SV.logmsg(l_module_name, 'FTE_SOURCE_LINE_CONSOLIDATION.Consolidate_Lines resulted in no consolidations');
787 END IF;
788 WSH_DEBUG_SV.logmsg(l_module_name,'-------- End Consolidations -----------');
789 END IF;
790 END IF;
791
792 IF (p_source_header_tab.COUNT = 0 OR
793 l_status is null OR
794 l_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
795
796 IF (l_status is not null) THEN
797 x_return_status := l_status;
798 ELSE
799 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
800 END IF;
801 --
802 -- Debug Statements
803 --
804 IF l_debug_on THEN
805 WSH_DEBUG_SV.pop(l_module_name);
806 END IF;
807 --
808 RETURN;
809 END IF;
810
811 --
812 -- Do carrier selection for each consolidation if consolidated successfully
813 -- and action is C or B
814 IF (p_source_header_tab.COUNT > 0 AND l_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS
815 AND (p_action = 'C' OR p_action = 'B')) THEN
816
817 IF (l_otm_installed = 'N') THEN
818 FORMAT_CS_CALL( p_source_header_tab => p_source_header_tab,
819 p_source_line_tab => p_source_line_tab,
820 x_result_tab => l_result_tab,
821 x_return_message => l_return_message,
822 x_return_status => l_status);
823 -- l_return_message is not used below, so assign it to l_msg_data
824 l_msg_data := substrb(l_return_message, 1, 200);
825
826 ELSE
827
828 WSH_OTM_RIQ_XML.CALL_OTM_FOR_OM(
829 x_source_line_tab => p_source_line_tab,
830 x_source_header_tab => p_source_header_tab,
831 p_source_type => p_source_type,
832 p_action => 'C',
833 x_source_line_rates_tab => l_source_line_rates_tab,
834 x_source_header_rates_tab => l_source_header_rates_tab,
835 x_result_consolidation_id_tab => l_result_consolidation_id_tab,
836 x_result_carrier_id_tab => l_result_carrier_id_tab,
837 x_result_service_level_tab => l_result_service_level_tab,
838 x_result_mode_of_transport_tab => l_result_mode_of_transport_tab,
839 x_result_freight_term_tab => l_result_freight_term_tab,
840 x_result_transit_time_min_tab => l_result_transit_time_min_tab,
841 x_result_transit_time_max_tab => l_result_transit_time_max_tab,
842 x_ship_method_code_tab => l_ship_method_code_tab,
843 x_return_status => l_status,
844 x_msg_count => l_msg_count,
845 x_msg_data => l_msg_data);
846
847 IF l_debug_on THEN
848 WSH_DEBUG_SV.log(l_module_name,'x_return_status from call_otm', l_status);
849 WSH_DEBUG_SV.log(l_module_name,'x_msg_count from call_otm', l_msg_count);
850 WSH_DEBUG_SV.log(l_module_name,'x_msg_data from call_otm', l_msg_data);
851 END IF;
852
853 /* R12 Glog Integration
854 format_cs_call's signature is changed in R12 to return
855 l_result_tab instead of 8 individual result tables,
856 and the subsequent flow is based on the new return type
857 so need to convert
858
859 1) output parameters l_result_consolidation_id_tab,
860 l_result_carrier_id_tab,
861 l_result_service_level_tab,
862 l_result_mode_of_transport_tab,
863 l_result_freight_term_tab,
864 l_result_transit_time_min_tab,
865 l_result_transit_time_max_tab,
866 l_ship_method_code_tab
867 to l_result_tab
868
869 2) l_msg_data is changed to l_return_message,
870 but l_msg_data rather than l_return_message is still used
871 below, so won't change it
872 */
873
874 IF (l_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
875 IF l_debug_on THEN
876 WSH_DEBUG_SV.log(l_module_name,'l_result_tab.count',l_result_tab.count);
877 WSH_DEBUG_SV.log(l_module_name,'l_result_consolidation_id_tab.count',l_result_consolidation_id_tab.count);
878 END IF;
879
880 FOR i IN l_result_consolidation_id_tab.FIRST..l_result_consolidation_id_tab.LAST LOOP
881 l_result_tab(i).delivery_id := l_result_consolidation_id_tab(i);
882 l_result_tab(i).carrier_id := l_result_carrier_id_tab(i);
883 l_result_tab(i).service_level := l_result_service_level_tab(i);
884 l_result_tab(i).mode_of_transport := l_result_mode_of_transport_tab(i);
885 l_result_tab(i).freight_terms_code := l_result_freight_term_tab(i);
886 l_result_tab(i).min_transit_time := l_result_transit_time_min_tab(i);
887 l_result_tab(i).max_transit_time := l_result_transit_time_max_tab(i);
888 l_result_tab(i).ship_method_code := l_ship_method_code_tab(i);
889 l_result_tab(i).result_type := 'RANK'; -- need to be populated
890 END LOOP;
891
892 IF l_debug_on THEN
893 WSH_DEBUG_SV.log(l_module_name,'l_result_tab.count',l_result_tab.count);
894 FOR i IN l_result_tab.FIRST..l_result_tab.LAST LOOP
895 WSH_DEBUG_SV.log(l_module_name,'l_result_tab.delivery_id',l_result_tab(i).delivery_id);
896 END LOOP;
897 END IF;
898
899 END IF;
900
901 END IF; -- if (l_otm_installed)
902
903 IF (l_status is not null AND l_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
904
905 IF l_debug_on THEN
906 WSH_DEBUG_SV.logmsg(l_module_name, 'Populating CS results into consolidation table');
907 END IF;
908
909 -- populate the CS results into the consolidation table
910 FOR i IN p_source_header_tab.FIRST..p_source_header_tab.LAST LOOP
911
912 -- FOR j IN l_result_consolidation_id_tab.FIRST..l_result_consolidation_id_tab.LAST LOOP
913 FOR j IN l_result_tab.FIRST..l_result_tab.LAST LOOP
914
915 -- IF (p_source_header_tab(i).consolidation_id = l_result_consolidation_id_tab(j)) THEN
916 -- Consolidation id is being passed as delivery id
917 IF (p_source_header_tab(i).consolidation_id = l_result_tab(j).delivery_id) THEN
918
919 --
920 -- If Result type is 'RANK' then copy.
921 -- else throw message.
922 -- EXIT;
923
924 IF (l_result_tab(j).result_type<>'RANK') THEN
925
926 l_no_results := 'N';
927 FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_MULTILEG');
928 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
929 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
930 -- p_source_header_tab(i).message_data := FND_MESSAGE.GET;
931 FND_MSG_PUB.ADD;
932
933 IF l_debug_on THEN
934 WSH_DEBUG_SV.logmsg(l_module_name,'Multileg results returned');
935 END IF;
936
937 --
938 -- Here we are looping over the output results table. The results are sorted by
939 -- sequence.The moment we find the first record.We copy the values and EXIT.
940 --
941
942 ELSIF (l_result_tab(j).result_type='RANK') THEN
943
944 l_no_results := 'N';
945 p_source_header_tab(i).ship_method_code := l_result_tab(j).ship_method_code;
946
947 p_source_header_tab(i).carrier_id := l_result_tab(j).carrier_id;
948 p_source_header_tab(i).service_level := l_result_tab(j).service_level;
949 p_source_header_tab(i).mode_of_transport := l_result_tab(j).mode_of_transport;
950
951 --
952 -- [2003/02/05][PACK I][ABLUNDEL][BUG: 2769793]
953 -- Freight terms on the order line was getting nulled out as
954 -- the engine was returning null. Only change the freight term
955 -- if one exists
956 --
957 -- IF (l_result_freight_term_tab(j) is not null) THEN
958 -- p_source_header_tab(i).freight_terms := l_result_freight_term_tab(j);
959 -- END IF;
960
961 IF (l_result_tab(j).freight_terms_code IS NOT NULL) THEN
962 p_source_header_tab(i).freight_terms := l_result_tab(j).freight_terms_code;
963 END IF;
964
965 IF l_debug_on THEN
966 WSH_DEBUG_SV.logmsg(l_module_name, '----------------------------------------');
967 WSH_DEBUG_SV.logmsg(l_module_name, 'Added ship method, carrier, service, mode, freight terms for consolidation_id = ' || p_source_header_tab(i).consolidation_id);
968 END IF;
969
970 l_orig_delivery_time := p_source_header_tab(i).delivery_lead_time; -- save for later
971 -- l_cs_transit_min := l_result_transit_time_min_tab(j);
972 -- l_cs_transit_max := l_result_transit_time_max_tab(j);
973
974 l_cs_transit_min := l_result_tab(j).min_transit_time;
975 l_cs_transit_max := l_result_tab(j).max_transit_time;
976
977 -- bug 2768725
978 -- get rule name only if it exists
979 -- IF (l_rule_name_tab.EXISTS(j)) THEN
980 -- l_rule_name := l_rule_name_tab(j);
981 -- END IF;
982
983 l_rule_name := l_result_tab(j).rule_name;
984
985 -- IF (l_result_transit_time_max_tab(j) is not null) THEN
986 -- p_source_header_tab(i).delivery_lead_time := l_result_transit_time_max_tab(j);
987 -- END IF;
988
989 IF (l_result_tab(j).max_transit_time IS NOT NULL) THEN
990 p_source_header_tab(i).delivery_lead_time := l_result_tab(j).max_transit_time;
991 END IF;
992
993 -- IF l_debug_on THEN
994 -- WSH_DEBUG_SV.logmsg(l_module_name, 'Obtained rule name and lead times for consolidation_id = ' || p_source_header_tab(i).consolidation_id);
995 -- END IF;
996 --
997
998 IF (p_source_header_tab(i).carrier_id is null) THEN
999
1000 IF l_debug_on THEN
1001 WSH_DEBUG_SV.logmsg(l_module_name, 'carrier_id is null');
1002 END IF;
1003
1004 -- check to see if generic carrier
1005 -- (as generic carriers can have just service level or mode)
1006 IF (p_source_header_tab(i).service_level is not null AND
1007 p_source_header_tab(i).mode_of_transport is not null) THEN
1008
1009 OPEN get_generic_sm(p_source_header_tab(i).mode_of_transport,p_source_header_tab(i).service_level,
1010 p_source_header_tab(i).ship_from_org_id);
1011
1012 FETCH get_generic_sm INTO p_source_header_tab(i).ship_method_code;
1013 CLOSE get_generic_sm;
1014 ELSIF (p_source_header_tab(i).service_level is null AND
1015 p_source_header_tab(i).mode_of_transport is not null) THEN
1016
1017 OPEN get_generic_sm_on_mod(p_source_header_tab(i).mode_of_transport,
1018 p_source_header_tab(i).ship_from_org_id);
1019
1020 FETCH get_generic_sm_on_mod INTO p_source_header_tab(i).ship_method_code;
1021 CLOSE get_generic_sm_on_mod;
1022 ELSIF (p_source_header_tab(i).service_level is not null AND
1023 p_source_header_tab(i).mode_of_transport is null) THEN
1024
1025 OPEN get_generic_sm_on_ser(p_source_header_tab(i).service_level,
1026 p_source_header_tab(i).ship_from_org_id);
1027
1028 FETCH get_generic_sm_on_ser INTO p_source_header_tab(i).ship_method_code;
1029 CLOSE get_generic_sm_on_ser;
1030 ELSE
1031 -- no carrier selection results at all
1032 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1033 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1034 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_CS_RESULT');
1035 p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1036 IF l_debug_on THEN
1037 WSH_DEBUG_SV.log(l_module_name, 'message_data', p_source_header_tab(i).message_data);
1038 END IF;
1039 END IF; -- no carrier servoce
1040 IF l_debug_on THEN
1041 WSH_DEBUG_SV.log(l_module_name, 'Gneric Ship Method Derived', p_source_header_tab(i).ship_method_code);
1042 END IF;
1043
1044 -- error: need to set up generic carrier
1045 IF (p_source_header_tab(i).ship_method_code is null) THEN
1046
1047 l_org_name := WSH_UTIL_CORE.Get_Org_Name(p_source_header_tab(i).ship_from_org_id);
1048
1049 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1050 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1051 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_GENERIC_CARRIER');
1052 FND_MESSAGE.SET_TOKEN('ORG_NAME', l_org_name);
1053 -- p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1054 FND_MSG_PUB.ADD;
1055
1056 IF l_debug_on THEN
1057 WSH_DEBUG_SV.log(l_module_name, 'message_data', p_source_header_tab(i).message_data);
1058 END IF;
1059 END IF;
1060
1061
1062
1063 -- result from CS, but no valid ship method assigned to org
1064 ELSIF (p_source_header_tab(i).carrier_id is not null AND
1065 p_source_header_tab(i).service_level is not null AND
1066 p_source_header_tab(i).mode_of_transport is not null AND
1067 p_source_header_tab(i).ship_method_code is null) THEN
1068
1069 IF l_debug_on THEN
1070 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR result from CS, but no valid ship method assigned to org');
1071 END IF;
1072
1073 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1074 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1075
1076 OPEN get_ship_method_meaning2( p_source_header_tab(i).carrier_id,
1077 p_source_header_tab(i).mode_of_transport,
1078 p_source_header_tab(i).service_level);
1079 FETCH get_ship_method_meaning2 INTO l_ship_method_meaning;
1080 CLOSE get_ship_method_meaning2;
1081
1082 l_org_name := WSH_UTIL_CORE.Get_Org_Name(p_source_header_tab(i).ship_from_org_id);
1083 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_SHIP_METHOD');
1084 FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_ship_method_meaning);
1085 FND_MESSAGE.SET_TOKEN('RULE_NAME', l_rule_name);
1086 FND_MESSAGE.SET_TOKEN('ORG_NAME', l_org_name);
1087 -- p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1088 FND_MSG_PUB.ADD;
1089
1090 IF l_debug_on THEN
1091 WSH_DEBUG_SV.log(l_module_name, 'message_data', p_source_header_tab(i).message_data);
1092 END IF;
1093
1094 -- have carrier, but not others, incomplete ship method setup
1095 ELSIF (p_source_header_tab(i).ship_method_code is null) THEN
1096
1097 IF l_debug_on THEN
1098 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR incomplete ship method setup');
1099 END IF;
1100
1101 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1102 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1103 WSH_CARRIERS_GRP.Get_Carrier_Name(p_source_header_tab(i).carrier_id, l_carrier_name, l_freight_code);
1104 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_INCOMPLETE_SHIP_METHOD');
1105 FND_MESSAGE.SET_TOKEN('RULE_NAME', l_rule_name);
1106 FND_MESSAGE.SET_TOKEN('CARRIER_NAME', l_carrier_name);
1107 -- p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1108 FND_MSG_PUB.ADD;
1109
1110 IF l_debug_on THEN
1111 WSH_DEBUG_SV.log(l_module_name, 'message_data', p_source_header_tab(i).message_data);
1112 END IF;
1113 END IF; -- end checking for errors
1114
1115 -- if have ship_method must mean have everything, continue
1116 IF (p_source_header_tab(i).ship_method_code is not null) THEN
1117
1118 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1119 -- get ship method, and transit time from transit times table
1120 OPEN get_session_id;
1121 FETCH get_session_id INTO l_session_id;
1122 CLOSE get_session_id;
1123
1124 l_transit_ship_method := p_source_header_tab(i).ship_method_code;
1125
1126 --
1127 -- Debug Statements
1128 --
1129 IF l_debug_on THEN
1130 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit MSC_ATP_PROC.ATP_SHIPPING_LEAD_TIME',WSH_DEBUG_SV.C_PROC_LEVEL);
1131 WSH_DEBUG_SV.log(l_module_name,'session_id', l_session_id);
1132 WSH_DEBUG_SV.log(l_module_name,'ship_from_location_id', p_source_header_tab(i).ship_from_location_id);
1133 WSH_DEBUG_SV.log(l_module_name,'ship_to_site_id', p_source_header_tab(i).ship_to_site_id);
1134 WSH_DEBUG_SV.log(l_module_name,'original_ship_method', l_transit_ship_method);
1135 END IF;
1136 --
1137 MSC_ATP_PROC.ATP_Shipping_Lead_Time(p_source_header_tab(i).ship_from_location_id,
1138 p_source_header_tab(i).ship_to_site_id,
1139 l_session_id,
1140 l_transit_ship_method,
1141 l_transit_time,
1142 l_transit_status);
1143
1144 IF l_debug_on THEN
1145 WSH_DEBUG_SV.logmsg(l_module_name, 'Results from MSC_ATP_PROC.ATP_SHIPPING_LEAD_TIME:');
1146 WSH_DEBUG_SV.log(l_module_name, 'ship_method', l_transit_ship_method);
1147 WSH_DEBUG_SV.log(l_module_name, 'transit_time', l_transit_time);
1148 WSH_DEBUG_SV.log(l_module_name, 'status', l_transit_status);
1149 END IF;
1150
1151 -- make sure ship method returned from transit times table matches
1152 -- that returned from CS, else error
1153 IF (l_transit_status = FND_API.G_RET_STS_ERROR OR
1154 l_transit_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1155
1156 IF l_debug_on THEN
1157 WSH_DEBUG_SV.logmsg(l_module_name, 'MSC_ATP_PROC.ATP_SHIPPING_LEAD_TIM returned ' || l_transit_status);
1158 END IF;
1159 p_source_header_tab(i).status := l_transit_status;
1160 -- continue, end of this iteration of loop
1161 -- ship method returned by ATP is different from CS ship method
1162 ELSIF (l_transit_status = FND_API.G_RET_STS_SUCCESS AND
1163 l_transit_ship_method is not null AND
1164 l_transit_ship_method <> p_source_header_tab(i).ship_method_code) THEN
1165
1166 IF l_debug_on THEN
1167 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR ship method returned by ATP is different from CS ship method');
1168 END IF;
1169
1170 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1171 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1172 OPEN get_ship_method_meaning(p_source_header_tab(i).ship_method_code);
1173 FETCH get_ship_method_meaning INTO l_ship_method_meaning;
1174 CLOSE get_ship_method_meaning;
1175
1176 IF (l_cs_transit_min is null) THEN
1177 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NOMATCH_SHIP_METHOD');
1178 FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_ship_method_meaning);
1179 -- p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1180 FND_MSG_PUB.ADD;
1181 ELSE
1182 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NOMATCH_SHIP_METHOD_2');
1183 FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_ship_method_meaning);
1184 FND_MESSAGE.SET_TOKEN('FTE_MIN_TRANSIT_TIME', l_cs_transit_min);
1185 FND_MESSAGE.SET_TOKEN('FTE_MAX_TRANSIT_TIME', l_cs_transit_max);
1186 -- p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1187 FND_MSG_PUB.ADD;
1188 END IF;
1189
1190 IF l_debug_on THEN
1191 WSH_DEBUG_SV.log(l_module_name, 'message_data', p_source_header_tab(i).message_data);
1192 END IF;
1193 -- continue, end of this iteration of loop
1194
1195 -- make sure transit time (from TT) is between min and max
1196 -- transit time of FTE (from CS)
1197 ELSIF ( p_source_header_tab(i).scheduled_flag = 'Y' AND
1198 l_transit_time is not null AND
1199 NOT (l_transit_time between nvl(l_cs_transit_min, 0)
1200 and nvl(l_cs_transit_max, 999))) THEN
1201 -- since ATP transit time falls outside FTE range
1202 -- set return lead time to be FTE's min
1203 p_source_header_tab(i).delivery_lead_time := l_cs_transit_min;
1204
1205 IF l_debug_on THEN
1206 WSH_DEBUG_SV.logmsg(l_module_name, 'WARNING ATP transit time falls outside FTE range. Setting return lead time to be FTE min');
1207 END IF;
1208
1209 -- set warning message
1210 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1211
1212 OPEN get_ship_method_meaning(p_source_header_tab(i).ship_method_code);
1213 FETCH get_ship_method_meaning INTO l_ship_method_meaning;
1214 CLOSE get_ship_method_meaning;
1215
1216 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_CS_TT_DISCREPANCY');
1217 FND_MESSAGE.SET_TOKEN('ATP_TRANSIT_TIME', l_transit_time);
1218 FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_ship_method_meaning);
1219 FND_MESSAGE.SET_TOKEN('FTE_MIN_TRANSIT_TIME', l_cs_transit_min);
1220 FND_MESSAGE.SET_TOKEN('FTE_MAX_TRANSIT_TIME', l_cs_transit_max);
1221 FND_MESSAGE.SET_TOKEN('RULE_NAME', l_rule_name);
1222 -- p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1223 FND_MSG_PUB.ADD;
1224
1225 IF l_debug_on THEN
1226 WSH_DEBUG_SV.log(l_module_name, 'message_data', p_source_header_tab(i).message_data);
1227 END IF;
1228 -- continue, end of this iteration of loop
1229 END IF;
1230
1231 -- populate returning delivery lead time with that of TT's if TT
1232 -- is not null
1233 IF (l_transit_time is not null AND
1234 (l_transit_time between nvl(l_cs_transit_min, 0)
1235 and nvl(l_cs_transit_max, 999))) THEN
1236 p_source_header_tab(i).delivery_lead_time := l_transit_time;
1237 END IF;
1238
1239 -- see if transit time from TT or CS is greater than
1240 -- existing on OM line, if so, error; always check
1241 -- only perform check if om's delivery lead time is not null or greater than 0
1242 IF (p_source_header_tab(i).scheduled_flag = 'Y' AND
1243 l_orig_delivery_time is not null AND l_orig_delivery_time > 0 AND
1244 p_source_header_tab(i).delivery_lead_time is not null AND
1245 p_source_header_tab(i).delivery_lead_time > l_orig_delivery_time) THEN
1246
1247 IF l_debug_on THEN
1248 WSH_DEBUG_SV.logmsg(l_module_name, 'ERROR OM lead time not null and greater than 0 (scheduled) and transit time to be returned by FTE process_lines is greater than exisiting on OM');
1249 END IF;
1250
1251 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1252 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1253 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_CS_TRANSIT_LARGER');
1254 OPEN get_ship_method_meaning(p_source_header_tab(i).ship_method_code);
1255 FETCH get_ship_method_meaning INTO l_ship_method_meaning;
1256 CLOSE get_ship_method_meaning;
1257 FND_MESSAGE.SET_TOKEN('CS_TRANSIT_TIME', p_source_header_tab(i).delivery_lead_time);
1258 FND_MESSAGE.SET_TOKEN('SHIP_METHOD', l_ship_method_meaning);
1259 FND_MESSAGE.SET_TOKEN('RULE_NAME', l_rule_name);
1260 FND_MESSAGE.SET_TOKEN('OM_TRANSIT_TIME', l_orig_delivery_time);
1261 -- p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1262 FND_MSG_PUB.ADD;
1263
1264 IF l_debug_on THEN
1265 WSH_DEBUG_SV.log(l_module_name, 'message_data', p_source_header_tab(i).message_data);
1266 END IF;
1267 END IF; -- end mandatory OM line delivery lead time check against CS/TT
1268
1269 END IF; -- end have ship method
1270
1271 END IF; --end checking if we have rank only.
1272 -- We have found a matching group. Exit out of inner loop.
1273 EXIT;
1274 END IF; -- end finding a matching consolidation id
1275 END LOOP; -- end looping thru all result consolidations
1276
1277 IF (l_no_results = 'Y') THEN -- if no results for this consolidation, error
1278 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1279 p_source_header_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1280 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_CS_RESULT');
1281 p_source_header_tab(i).message_data := FND_MESSAGE.GET;
1282 END IF;
1283 END LOOP;
1284
1285 -- populate the CS results from consolidation table to line output table
1286 FOR i IN p_source_line_tab.FIRST..p_source_line_tab.LAST LOOP
1287
1288 l_con_id := p_source_line_tab(i).consolidation_id;
1289 p_source_line_tab(i).ship_method_code := p_source_header_tab(l_con_id).ship_method_code;
1290 p_source_line_tab(i).carrier_id := p_source_header_tab(l_con_id).carrier_id;
1291 p_source_line_tab(i).service_level := p_source_header_tab(l_con_id).service_level;
1292 p_source_line_tab(i).mode_of_transport := p_source_header_tab(l_con_id).mode_of_transport;
1293 p_source_line_tab(i).freight_terms := p_source_header_tab(l_con_id).freight_terms;
1294 p_source_line_tab(i).delivery_lead_time := p_source_header_tab(l_con_id).delivery_lead_time;
1295 p_source_line_tab(i).status := p_source_header_tab(l_con_id).status;
1296 p_source_line_tab(i).message_data := p_source_header_tab(l_con_id).message_data;
1297
1298 IF l_debug_on THEN
1299 WSH_DEBUG_SV.logmsg(l_module_name, '-------- SOURCE LINE OUTPUT--------');
1300 WSH_DEBUG_SV.log(l_module_name, 'source_line_id', p_source_line_tab(i).source_line_id);
1301 WSH_DEBUG_SV.log(l_module_name, 'source_header_id', p_source_line_tab(i).source_header_id);
1302 WSH_DEBUG_SV.log(l_module_name, 'consolidation_id', p_source_line_tab(i).consolidation_id);
1303 WSH_DEBUG_SV.log(l_module_name, 'ship_from_org_id', p_source_line_tab(i).ship_from_org_id);
1304 WSH_DEBUG_SV.log(l_module_name, 'ship_from_location_id', p_source_line_tab(i).ship_from_location_id);
1305 WSH_DEBUG_SV.log(l_module_name, 'ship_to_site_id', p_source_line_tab(i).ship_to_site_id);
1306 WSH_DEBUG_SV.log(l_module_name, 'ship_to_location_id', p_source_line_tab(i).ship_to_location_id);
1307 WSH_DEBUG_SV.log(l_module_name, 'customer_id', p_source_line_tab(i).customer_id);
1308 WSH_DEBUG_SV.log(l_module_name, 'inventory_item_id', p_source_line_tab(i).inventory_item_id);
1309 WSH_DEBUG_SV.log(l_module_name, 'source_quantity', p_source_line_tab(i).source_quantity);
1310 WSH_DEBUG_SV.log(l_module_name, 'source_quantity_uom', p_source_line_tab(i).source_quantity_uom);
1311 WSH_DEBUG_SV.log(l_module_name, 'ship_date', p_source_line_tab(i).ship_date);
1312 WSH_DEBUG_SV.log(l_module_name, 'arrival_date', p_source_line_tab(i).arrival_date);
1313 WSH_DEBUG_SV.log(l_module_name, 'scheduled_flag', p_source_line_tab(i).scheduled_flag);
1314 WSH_DEBUG_SV.log(l_module_name, 'intmed_ship_to_site_id', p_source_line_tab(i).intmed_ship_to_site_id);
1315 WSH_DEBUG_SV.log(l_module_name, 'intmed_ship_to_loc_id', p_source_line_tab(i).intmed_ship_to_loc_id);
1316 WSH_DEBUG_SV.log(l_module_name, 'fob_code', p_source_line_tab(i).fob_code);
1317 WSH_DEBUG_SV.log(l_module_name, 'weight', p_source_line_tab(i).weight);
1318 WSH_DEBUG_SV.log(l_module_name, 'weight_uom_code', p_source_line_tab(i).weight_uom_code);
1319 WSH_DEBUG_SV.log(l_module_name, 'volume', p_source_line_tab(i).volume);
1320 WSH_DEBUG_SV.log(l_module_name, 'volume_uom_code', p_source_line_tab(i).volume_uom_code);
1321 WSH_DEBUG_SV.log(l_module_name, 'ship_method_code', p_source_line_tab(i).ship_method_code);
1322 WSH_DEBUG_SV.log(l_module_name, 'carrier_id', p_source_line_tab(i).carrier_id);
1323 WSH_DEBUG_SV.log(l_module_name, 'service_level', p_source_line_tab(i).service_level);
1324 WSH_DEBUG_SV.log(l_module_name, 'mode_of_transport', p_source_line_tab(i).mode_of_transport);
1325 WSH_DEBUG_SV.log(l_module_name, 'freight_terms', p_source_line_tab(i).freight_terms);
1326 WSH_DEBUG_SV.log(l_module_name, 'delivery_lead_time', p_source_line_tab(i).delivery_lead_time);
1327 WSH_DEBUG_SV.log(l_module_name, 'status', p_source_line_tab(i).status);
1328 WSH_DEBUG_SV.log(l_module_name, 'message_data', p_source_line_tab(i).message_data);
1329 END IF;
1330 END LOOP;
1331
1332 -- if it failed with any of the above validation
1333 IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1334
1335 FND_MESSAGE.SET_NAME('FTE','FTE_PRC_LOG_FILE_NAME');
1336 IF l_debug_on THEN
1337 FND_MESSAGE.SET_TOKEN('LOGFILE',WSH_DEBUG_SV.g_Dir||'/'||WSH_DEBUG_SV.g_File);
1338 ELSE
1339 FND_MESSAGE.SET_TOKEN('LOGFILE','');
1340 END IF;
1341 FND_MSG_PUB.ADD;
1342
1343 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_CS_RESULT');
1344 FND_MSG_PUB.ADD;
1345
1346 IF l_debug_on THEN
1347 WSH_DEBUG_SV.logmsg(l_module_name,'return_status is changed from '||l_status||' to '||x_return_status||' after getting ship method.',WSH_DEBUG_SV.C_PROC_LEVEL);
1348 WSH_DEBUG_SV.pop(l_module_name);
1349 END IF;
1350
1351 RETURN;
1352 END IF;
1353
1354 -- format cs call had warnings (no results at all), put error in each line
1355 ELSIF (l_status is not null AND l_status = WSH_UTIL_CORE.G_RET_STS_WARNING) THEN
1356 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS; -- overall status is success
1357 x_msg_data := l_msg_data;
1358
1359 FOR i IN p_source_line_tab.FIRST..p_source_line_tab.LAST LOOP
1360 p_source_line_tab(i).status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1361 FND_MESSAGE.SET_NAME('FTE', 'FTE_SEL_NO_CS_RESULT');
1362 p_source_line_tab(i).message_data := FND_MESSAGE.GET;
1363 END LOOP;
1364
1365 --
1366 -- Debug Statements
1367 --
1368 IF l_debug_on THEN
1369 WSH_DEBUG_SV.pop(l_module_name);
1370 END IF;
1371 --
1372 RETURN;
1373
1374 -- format cs call had errors or unexpected errors
1375 ELSE
1376 IF l_debug_on THEN
1377 WSH_DEBUG_SV.log(l_module_name, 'error_return_status', l_status);
1378 WSH_DEBUG_SV.log(l_module_name, 'error_return_status', l_msg_data);
1379 END IF;
1380 x_return_status := l_status;
1381 x_msg_data := l_msg_data;
1382 IF (x_return_status is null) THEN
1383 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1384 END IF;
1385
1386 FOR i IN p_source_line_tab.FIRST..p_source_line_tab.LAST LOOP
1387 IF l_debug_on THEN
1388 WSH_DEBUG_SV.log(l_module_name, 'i is', to_char(i));
1389 END IF;
1390 p_source_line_tab(i).status := x_return_status;
1391 p_source_line_tab(i).message_data := x_msg_data;
1392 END LOOP;
1393
1394 --
1395 -- Debug Statements
1396 --
1397 IF l_debug_on THEN
1398 WSH_DEBUG_SV.pop(l_module_name);
1399 END IF;
1400 --
1401 RETURN;
1402
1403 END IF;
1404
1405 END IF;
1406
1407
1408 -- if action is R or B, do rating call
1409 IF (p_source_header_tab.COUNT > 0 AND l_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS
1410 AND (p_action = 'R' OR p_action = 'B')) THEN
1411
1412 IF (l_otm_installed ='N') THEN
1413 IF l_debug_on THEN
1414 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_FREIGHT_RATING_PUB.GET_FREIGHT_COSTS',WSH_DEBUG_SV.C_PROC_LEVEL);
1415 END IF;
1416 FTE_FREIGHT_RATING_PUB.Get_Freight_Costs(
1417 p_api_version => 1,
1418 p_source_line_tab => p_source_line_tab,
1419 p_source_header_tab => p_source_header_tab,
1420 p_source_type => p_source_type,
1421 p_action => p_action,
1422 x_source_line_rates_tab => l_source_line_rates_tab,
1423 x_source_header_rates_tab => l_source_header_rates_tab,
1424 x_request_id => l_request_id,
1425 x_return_status => l_status,
1426 x_msg_count => l_msg_count,
1427 x_msg_data => l_msg_data);
1428 ELSE
1429 IF l_debug_on THEN
1430 WSH_DEBUG_SV.logmsg(l_module_name,'currency for get rates is '||p_source_header_tab(p_source_header_tab.FIRST).currency);
1431 END IF;
1432 WSH_OTM_RIQ_XML.CALL_OTM_FOR_OM(
1433 x_source_line_tab => p_source_line_tab,
1434 x_source_header_tab => p_source_header_tab,
1435 p_source_type => p_source_type,
1436 p_action => 'R',
1437 x_source_line_rates_tab => l_source_line_rates_tab,
1438 x_source_header_rates_tab => l_source_header_rates_tab,
1439 x_result_consolidation_id_tab => l_result_consolidation_id_tab,
1440 x_result_carrier_id_tab => l_result_carrier_id_tab,
1441 x_result_service_level_tab => l_result_service_level_tab,
1442 x_result_mode_of_transport_tab => l_result_mode_of_transport_tab,
1443 x_result_freight_term_tab => l_result_freight_term_tab,
1444 x_result_transit_time_min_tab => l_result_transit_time_min_tab,
1445 x_result_transit_time_max_tab => l_result_transit_time_max_tab,
1446 x_ship_method_code_tab => l_ship_method_code_tab,
1447 x_return_status => l_status,
1448 x_msg_count => l_msg_count,
1449 x_msg_data => l_msg_data);
1450 END IF;
1451
1452 IF (l_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1453 x_source_line_rates_tab := l_source_line_rates_tab;
1454 x_source_header_rates_tab := l_source_header_rates_tab;
1455 END IF;
1456
1457 x_return_status := l_status;
1458 x_msg_count := l_msg_count;
1459 x_msg_data := l_msg_data;
1460
1461 END IF;
1462
1463 END IF; -- p_action <> 'GET_RATE_CHOICE'
1464 --
1465 -- Debug Statements
1466 --
1467 IF l_debug_on THEN
1468 WSH_DEBUG_SV.pop(l_module_name);
1469 END IF;
1470 --
1471 EXCEPTION
1472
1473 WHEN OTHERS THEN
1474 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1475 x_msg_data := SQLERRM;
1476 --
1477 -- Debug Statements
1478 --
1479 IF l_debug_on THEN
1480 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1481 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1482 END IF;
1483 --
1484 RAISE;
1485
1486 --
1487 -- Debug Statements
1488 --
1489 IF l_debug_on THEN
1490 WSH_DEBUG_SV.pop(l_module_name);
1491 END IF;
1492 --
1493 END Process_Lines;
1494
1495
1496 -- FOR BACKWARD (I) COMPATIBILITY ONLY
1497 -- THE FOLLOWING BODY IS STUBBED OUT IN THIS BRANCH
1498
1499 PROCEDURE Process_Lines(p_source_line_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_tab,
1500 p_source_header_tab IN OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_header_tab,
1501 p_source_type IN VARCHAR2,
1502 p_action IN VARCHAR2,
1503 p_rating_parameters_tab IN FTE_PROCESS_REQUESTS.fte_rating_parameters_tab DEFAULT FTE_MISS_RATING_PARAMETERS_TAB,
1504 p_source_line_rates_tab OUT NOCOPY FTE_PROCESS_REQUESTS.fte_source_line_rates_tab,
1505 x_return_status OUT NOCOPY VARCHAR2,
1506 x_msg_count OUT NOCOPY NUMBER,
1507 x_msg_data OUT NOCOPY VARCHAR2)
1508 IS
1509 BEGIN
1510 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1511 END Process_Lines;
1512
1513
1514 END FTE_PROCESS_REQUESTS;