[Home] [Help]
PACKAGE BODY: APPS.FTE_ACS_PKG
Source
1 PACKAGE BODY FTE_ACS_PKG AS
2 /* $Header: FTEACSMB.pls 120.6 2005/09/28 05:01:57 alksharm ship $ */
3 -- -------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_ACS_PKG --
6 -- TYPE: PACKAGE BODY --
7 -- DESCRIPTION: Contains core procedures for carrier selection module --
8 -- --
9 -- --
10 -- CHANGE CONTROL LOG --
11 -- --
12 -- DATE VERSION BY BUG DESCRIPTION --
13 -- ---------- ------- -------- ------- ---------------------------------- --
14 -- -------------------------------------------------------------------------- --
15
16 -- -------------------------------------------------------------------------- --
17 -- Global Package Variables --
18 -- ------------------------ --
19 -- --
20 -- -------------------------------------------------------------------------- --
21
22 g_message_tab FTE_ACS_PKG.fte_cs_output_message_tab;
23
24 -- -------------------------------------------------------------------------- --
25 -- R12 Enhancement
26 -- Local data structues
27 -- -------------------------------------------------------------------------- --
28 TYPE FTE_CS_ENTITY_RULE_REC IS RECORD (entity_id_tab WSH_UTIL_CORE.ID_TAB_TYPE,
29 rule_id_tab WSH_UTIL_CORE.ID_TAB_TYPE);
30
31 TYPE FTE_CS_TEMP_ENTITY_REC IS RECORD( delivery_id NUMBER,
32 trip_id NUMBER,
33 rule_id NUMBER,
34 organization_id NUMBER,
35 initial_pickup_loc_id NUMBER,
36 ultimate_dropoff_loc_id NUMBER,
37 initial_pickup_date DATE,
38 ultimate_dropoff_date DATE);
39
40 TYPE FTE_CS_TEMP_ENTITY_TAB IS TABLE OF FTE_CS_TEMP_ENTITY_REC INDEX BY BINARY_INTEGER;
41
42 -- -------------------------------------------------------------------------- --
43 -- --
44 -- PRIVATE PROCEDURE DEFINITIONS --
45 -- ----------------------------- --
46 -- --
47 -- -------------------------------------------------------------------------- --
48 --
49 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_ACS_PKG';
50
51 --
52 -- -------------------------------------------------------------------------- --
53 -- --
54 -- NAME: LOG_CS_MESSAGES --
55 -- --
56 -- TYPE: PROCEDURE --
57 -- --
58 -- PARAMETERS (IN): p_query_id IN NUMBER --
59 -- --
60 -- PARAMETERS (OUT): x_return_status OUT NOCOPY VARCHAR2 --
61 -- x_return_message OUT NOCOPY VARCHAR2 --
62 -- --
63 -- PARAMETERS (IN OUT): p_message_type_tab IN OUT NOCOPY --
64 -- FTE_ACS_PKG.fte_flag_tab_type --
65 -- p_message_code_tab IN OUT NOCOPY --
66 -- FTE_ACS_PKG.fte_car_sel_tmp_code_table --
67 -- p_message_text_tab IN OUT NOCOPY --
68 -- FTE_ACS_PKG.fte_car_sel_msg_table --
69 -- p_level_tab IN OUT NOCOPY --
70 -- FTE_ACS_PKG.fte_car_sel_tmp_num_table --
71 -- p_group_id_tab IN OUT NOCOPY --
72 -- FTE_ACS_PKG.fte_car_sel_tmp_num_table --
73 -- p_rule_id_tab IN OUT NOCOPY --
74 -- FTE_ACS_PKG.fte_car_sel_tmp_num_table --
75 -- p_result_id_tab IN OUT NOCOPY --
76 -- FTE_ACS_PKG.fte_car_sel_tmp_num_table --
77 -- --
78 -- RETURN: n/a --
79 -- --
80 -- DESCRIPTION: This procedure takes in tables of messages and rule/ --
81 -- result information and adds them to the global --
82 -- message table which is returned to the calling API --
83 -- at the end of the Carrier Selection Engine execution. --
84 -- --
85 -- CHANGE CONTROL LOG --
86 -- ------------------ --
87 -- --
88 -- DATE VERSION BY BUG DESCRIPTION --
89 -- ---------- ------- -------- ------- ---------------------------------- --
90 -- 2002/12/15 I ABLUNDEL ------- Created --
91 -- --
92 -- -------------------------------------------------------------------------- --
93 PROCEDURE LOG_CS_MESSAGES(p_message_type_tab IN OUT NOCOPY FTE_ACS_PKG.fte_flag_tab_type,
94 p_message_code_tab IN OUT NOCOPY FTE_ACS_PKG.fte_car_sel_tmp_code_table,
95 p_message_text_tab IN OUT NOCOPY FTE_ACS_PKG.fte_car_sel_msg_table,
96 p_level_tab IN OUT NOCOPY FTE_ACS_PKG.fte_car_sel_tmp_num_table,
97 p_query_id IN NUMBER,
98 p_group_id_tab IN OUT NOCOPY FTE_ACS_PKG.fte_car_sel_tmp_num_table,
99 p_rule_id_tab IN OUT NOCOPY FTE_ACS_PKG.fte_car_sel_tmp_num_table,
100 p_result_id_tab IN OUT NOCOPY FTE_ACS_PKG.fte_car_sel_tmp_num_table,
101 x_return_status OUT NOCOPY VARCHAR2,
102 x_return_message OUT NOCOPY VARCHAR2) IS
103
104
105 l_error_text VARCHAR2(2000);
106 l_cs_message VARCHAR2(2000);
107 l_rec_count PLS_INTEGER;
108
109 --
110 l_debug_on BOOLEAN;
111 --
112 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_CS_MESSAGES';
113 --
114 BEGIN
115
116 --
117 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
118 --
119 IF l_debug_on IS NULL
120 THEN
121 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
122 END IF;
123 --
124 --
125 -- Debug Statements
126 --
127 IF l_debug_on THEN
128 WSH_DEBUG_SV.push(l_module_name);
129 --
130 WSH_DEBUG_SV.log(l_module_name,'P_QUERY_ID',P_QUERY_ID);
131 END IF;
132 --
133 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
134 x_return_message := null;
135
136
137 FOR abcd IN p_message_text_tab.FIRST..p_message_text_tab.LAST LOOP
138
139 l_rec_count := g_message_tab.count + 1;
140
141 g_message_tab(l_rec_count).sequence_number := l_rec_count;
142 g_message_tab(l_rec_count).message_type := p_message_type_tab(abcd);
143
144 IF (p_message_code_tab.EXISTS(abcd)) THEN
145 g_message_tab(l_rec_count).message_code := p_message_code_tab(abcd);
146 ELSE
147 g_message_tab(l_rec_count).message_code := null;
148 END IF;
149
150 g_message_tab(l_rec_count).message_text := p_message_text_tab(abcd);
151
152
153 IF (p_level_tab.EXISTS(abcd)) THEN
154 g_message_tab(l_rec_count).level := p_level_tab(abcd);
155 ELSE
156 g_message_tab(l_rec_count).level := null;
157 END IF;
158
159 IF (p_query_id is not null) THEN
160 g_message_tab(l_rec_count).query_id := p_query_id;
161 ELSE
162 g_message_tab(l_rec_count).query_id := null;
163 END IF;
164
165 IF (p_group_id_tab.EXISTS(abcd)) THEN
166 g_message_tab(l_rec_count).group_id := p_group_id_tab(abcd);
167 ELSE
168 g_message_tab(l_rec_count).group_id := null;
169 END IF;
170
171 IF (p_rule_id_tab.EXISTS(abcd)) THEN
172 g_message_tab(l_rec_count).rule_id := p_rule_id_tab(abcd);
173 ELSE
174 g_message_tab(l_rec_count).rule_id := null;
175 END IF;
176
177 IF (p_result_id_tab.EXISTS(abcd)) THEN
178 g_message_tab(l_rec_count).result_id := p_result_id_tab(abcd);
179 ELSE
180 g_message_tab(l_rec_count).result_id := null;
181 END IF;
182
183 END LOOP;
184
185 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
186 x_return_message := null;
187 --
188 -- Debug Statements
189 --
190 IF l_debug_on THEN
191 WSH_DEBUG_SV.pop(l_module_name);
192 END IF;
193 --
194 RETURN;
195
196 --
197 -- Debug Statements
198 --
199 IF l_debug_on THEN
200 WSH_DEBUG_SV.pop(l_module_name);
201 END IF;
202 --
203 EXCEPTION
204 WHEN OTHERS THEN
205 l_error_text := SQLERRM;
206 --
207 -- Debug Statements
208 --
209 IF l_debug_on THEN
210 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_ACS_PKG.LOG_CS_MESSAGES IS ' ||L_ERROR_TEXT );
211 END IF;
212 --
213 WSH_UTIL_CORE.default_handler('FTE_ACS_PKG.LOG_CS_MESSAGES');
214 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
215 x_return_message := ('FTE_ACS_PKG.LOG_CS_MESSAGES '||l_error_text);
216 --
217 -- Debug Statements
218 --
219 IF l_debug_on THEN
220 WSH_DEBUG_SV.pop(l_module_name);
221 END IF;
222 --
223 RETURN;
224
225 --
226 -- Debug Statements
227 --
228 IF l_debug_on THEN
229 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
230 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
231 END IF;
232 --
233 END LOG_CS_MESSAGES;
234
235 --***************************************************************************--
236 --========================================================================
237 -- PROCEDURE : LOG_EXCEPTIONS Private
238 --
239 -- PARAMETERS: p_caller IN Calling API
240 -- p_entity IN Entity to be used - Trip / Dlvy
241 -- p_use_gtt IN Flag indicates whether GTT should be used.
242 -- p_single_rec IN Single Record
243 -- x_cs_output_message_tab OUT Output Message Tab
244 -- x_return_status OUT Return Status
245 --
246 -- COMMENT : This API logs exceptions for entites for which routing rules could not be found.
247 --***************************************************************************--
248
249 PROCEDURE LOG_EXCEPTIONS( p_caller IN VARCHAR2,
250 p_entity IN VARCHAR2,
251 p_use_gtt IN BOOLEAN,
252 p_single_rec IN FTE_ACS_PKG.fte_cs_entity_rec_type DEFAULT NULL,
253 x_cs_output_message_tab OUT NOCOPY FTE_ACS_PKG.fte_cs_output_message_tab,
254 x_return_status OUT NOCOPY VARCHAR2)
255 IS
256
257 CURSOR c_get_failed_entities IS
258 SELECT delivery_id,
259 trip_id,
260 rule_id,
261 organization_id,
262 initial_pickup_loc_id,
263 ultimate_dropoff_loc_id,
264 initial_pickup_date,
265 ultimate_dropoff_date
266 FROM FTE_SEL_SEARCH_ENTITIES_TMP
267 WHERE rule_id IS NULL;
268
269
270 l_failed_entity_tab FTE_CS_TEMP_ENTITY_TAB;
271 l_fnd_message_name VARCHAR2(60);
272 l_exception_name VARCHAR2(60);
273 l_exception_message VARCHAR2(5000);
274
275 l_exception_id NUMBER;
276 itr NUMBER;
277 l_return_status VARCHAR2(1);
278 l_msg_count NUMBER;
279 l_msg_data VARCHAR2(2000);
280
281 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
282 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'log_exceptions';
283
284 BEGIN
285
286 IF l_debug_on THEN
287 WSH_DEBUG_SV.PUSH(l_module_name);
288 WSH_DEBUG_SV.log(l_module_name,'p_caller',p_caller);
289 WSH_DEBUG_SV.log(l_module_name,'p_entity',p_entity);
290 WSH_DEBUG_SV.log(l_module_name,'p_use_gtt',p_use_gtt);
291 END IF;
292
293 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
294
295 IF (p_use_gtt) THEN
296
297 OPEN c_get_failed_entities;
298 FETCH c_get_failed_entities BULK COLLECT INTO l_failed_entity_tab;
299 CLOSE c_get_failed_entities;
300
301 IF l_debug_on THEN
302 WSH_DEBUG_SV.logmsg(l_module_name,'Number of Failed Entites'||l_failed_entity_tab.COUNT);
303 END IF;
304
305 IF (l_failed_entity_tab.COUNT > 0) THEN
306
307 IF (p_entity = 'DLVY') THEN
308
309 IF p_caller = 'WSH_DLMG' THEN
310 l_fnd_message_name := 'WSH_SELECT_CARRIER_FAIL';
311 l_exception_name := 'WSH_SELECT_CARRIER_FAIL';
312 ELSIF p_caller in ('WSH_AUTO_CREATE_DEL','WSH_PICK_RELEASE','WSH_AUTO_CREATE_DEL_TRIP') THEN
313 l_fnd_message_name := 'WSH_CARRIER_CREATE_DEL';
314 l_exception_name := 'WSH_CARRIER_CREATE_DEL';
315 END IF;
316
317 -- Start Logging exceptions.
318 itr := l_failed_entity_tab.FIRST;
319 LOOP
320 l_exception_id := NULL;
321 FND_MESSAGE.SET_NAME('WSH',l_fnd_message_name);
322 FND_MESSAGE.SET_TOKEN('DELIVERY_ID' ,l_failed_entity_tab(itr).delivery_id);
323 l_exception_message := FND_MESSAGE.Get;
324
325 wsh_xc_util.log_exception(p_api_version => 1.0,
326 x_return_status => l_return_status,
327 x_msg_count => l_msg_count,
328 x_msg_data => l_msg_data,
329 x_exception_id => l_exception_id,
330 p_exception_location_id => l_failed_entity_tab(itr).initial_pickup_loc_id,
331 p_logged_at_location_id => l_failed_entity_tab(itr).initial_pickup_loc_id,
332 p_logging_entity => 'SHIPPER',
333 p_logging_entity_id => FND_GLOBAL.USER_ID,
334 p_exception_name => l_exception_name ,
335 p_message => substrb(l_exception_message,1,2000),
336 p_delivery_id => l_failed_entity_tab(itr).delivery_id);
337
338 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
339 raise FND_API.G_EXC_UNEXPECTED_ERROR;
340 END IF;
341
342 EXIT WHEN itr = l_failed_entity_tab.LAST;
343 itr := l_failed_entity_tab.NEXT(itr);
344 END LOOP;
345
346
347 ELSIF (p_entity = 'TRIP') THEN
348
349 l_fnd_message_name := 'WSH_ROUTE_TRIP_FAIL';
350 l_exception_name := 'WSH_ROUTE_TRIP_FAIL';
351 itr := l_failed_entity_tab.FIRST;
352
353 LOOP
354 l_exception_id := NULL;
355 FND_MESSAGE.SET_NAME('WSH',l_fnd_message_name);
356 FND_MESSAGE.SET_TOKEN('TRIP_ID' ,l_failed_entity_tab(itr).trip_id);
357 l_exception_message := FND_MESSAGE.Get;
358
359 wsh_xc_util.log_exception(p_api_version => 1.0,
360 x_return_status => l_return_status,
361 x_msg_count => l_msg_count,
362 x_msg_data => l_msg_data,
363 x_exception_id => l_exception_id,
364 p_exception_location_id => l_failed_entity_tab(itr).initial_pickup_loc_id,
365 p_logged_at_location_id => l_failed_entity_tab(itr).initial_pickup_loc_id,
366 p_logging_entity => 'SHIPPER',
367 p_logging_entity_id => FND_GLOBAL.USER_ID,
368 p_exception_name => l_exception_name ,
369 p_message => substrb(l_exception_message,1,2000),
370 p_trip_id => l_failed_entity_tab(itr).trip_id);
371
372 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR THEN
373 raise FND_API.G_EXC_UNEXPECTED_ERROR;
374 END IF;
375
376 EXIT WHEN itr = l_failed_entity_tab.LAST;
377 itr := l_failed_entity_tab.NEXT(itr);
378 END LOOP;
379 END IF;
380 END IF;
381 ELSE
382
383 IF l_debug_on THEN
384 WSH_DEBUG_SV.log(l_module_name,'p_single_rec.rule_id ',p_single_rec.rule_id);
385 END IF;
386
387 -- Only if the rule id is NULL, we need to log exception.
388 IF (p_single_rec.rule_id IS NULL) THEN
389 -- Result could not be found
390 IF (p_entity = 'DLVY') THEN
391
392 IF p_caller = 'WSH_DLMG' THEN
393 FND_MESSAGE.SET_NAME('WSH', 'WSH_SELECT_CARRIER_FAIL');
394 l_exception_name := 'WSH_SELECT_CARRIER_FAIL';
395 ELSIF p_caller in ('WSH_AUTO_CREATE_DEL','WSH_PICK_RELEASE','WSH_AUTO_CREATE_DEL_TRIP') THEN
396 FND_MESSAGE.SET_NAME('WSH', 'WSH_CARRIER_CREATE_DEL');
397 l_exception_name := 'WSH_CARRIER_CREATE_DEL';
398 END IF;
399
400 FND_MESSAGE.SET_TOKEN('DELIVERY_ID' ,p_single_rec.delivery_id);
401 l_exception_message := FND_MESSAGE.Get;
402 l_exception_id := NULL;
403
404 wsh_xc_util.log_exception(
405 p_api_version => 1.0,
406 x_return_status => l_return_status,
407 x_msg_count => l_msg_count,
408 x_msg_data => l_msg_data,
409 x_exception_id => l_exception_id,
410 p_exception_location_id => p_single_rec.initial_pickup_loc_id,
411 p_logged_at_location_id => p_single_rec.initial_pickup_loc_id,
412 p_logging_entity => 'SHIPPER',
413 p_logging_entity_id => FND_GLOBAL.USER_ID,
414 p_exception_name => l_exception_name ,
415 p_message => substrb(l_exception_message,1,2000),
416 p_delivery_id => p_single_rec.delivery_id);
417
418
419 ELSIF (p_entity = 'TRIP') THEN
420 --
421 -- For Trip we need to log the exception
422 --
423 FND_MESSAGE.SET_NAME('WSH','WSH_ROUTE_TRIP_FAIL');
424 FND_MESSAGE.SET_TOKEN('TRIP_NAME',p_single_rec.trip_name);
425 -- Seed this exception
426 l_exception_name := 'WSH_ROUTE_TRIP_FAIL';
427 l_exception_message := FND_MESSAGE.GET;
428 l_exception_id := NULL;
429
430 wsh_xc_util.log_exception(
431 p_api_version => 1.0,
432 x_return_status => l_return_status,
433 x_msg_count => l_msg_count,
434 x_msg_data => l_msg_data,
435 x_exception_id => l_exception_id,
436 p_exception_location_id => p_single_rec.initial_pickup_loc_id,
437 p_logged_at_location_id => p_single_rec.initial_pickup_loc_id,
438 p_logging_entity => 'SHIPPER',
439 p_logging_entity_id => FND_GLOBAL.USER_ID,
440 p_exception_name => l_exception_name ,
441 p_message => substrb(l_exception_message,1,2000),
442 p_trip_id => p_single_rec.trip_id);
443
444 END IF;
445
446 END IF; --IF (p_single_rec.rule_id IS NULL) THEN
447 END IF;
448
449 IF l_debug_on THEN
450 WSH_DEBUG_SV.logmsg(l_module_name,'Logged the Exceptions');
451 WSH_DEBUG_SV.POP (l_module_name);
452 END IF;
453
454 EXCEPTION
455 WHEN OTHERS THEN
456
457 IF c_get_failed_entities%ISOPEN THEN
458 CLOSE c_get_failed_entities;
459 END IF;
460
461 WSH_UTIL_CORE.default_handler('FTE_ACS_PKG.log_exceptions');
462 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
463
464 IF l_debug_on THEN
465 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
466 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
467 END IF;
468 --
469 END LOG_EXCEPTIONS;
470
471 --***************************************************************************--
472 --========================================================================
473 -- PROCEDURE : get_format_results Private
474 --
475 -- PARAMETERS: p_caller IN Calling API
476 -- p_use_gtt IN Flag indicates whether GTT should be used.
477 -- p_single_rec IN Single Record
478 -- x_cs_output_message_tab OUT Output Message Tab
479 -- x_return_status OUT Return Status
480 --
481 -- COMMENT : The API returns the results assoociated with a particular rule.
482 -- In case of multi records GTT is used. For single record p_single_rec is used.
483 --***************************************************************************--
484 PROCEDURE GET_FORMAT_RESULTS( p_caller IN VARCHAR2,
485 p_use_gtt IN BOOLEAN,
486 p_single_rec IN FTE_ACS_PKG.FTE_CS_ENTITY_REC_TYPE,
487 x_cs_output_tab OUT NOCOPY FTE_ACS_PKG.FTE_CS_RESULT_TAB_TYPE,
488 x_return_status OUT NOCOPY VARCHAR2)
489 IS
490
491 CURSOR c_get_rules_for_entity IS
492 SELECT delivery_id,
493 trip_id,
494 rule_id,
495 organization_id,
496 initial_pickup_loc_id,
497 ultimate_dropoff_loc_id,
498 initial_pickup_date,
499 ultimate_dropoff_date
500 FROM FTE_SEL_SEARCH_ENTITIES_TMP
501 WHERE rule_id IS NOT NULL;
502
503 CURSOR c_get_transit_time_for_rule(p_rule_id IN NUMBER) IS
504 SELECT attribute_value_from_number,
505 attribute_value_to_number
506 FROM FTE_SEL_RULE_RESTRICTIONS
507 WHERE rule_id = p_rule_id
508 AND attribute_name = 'TRANSIT_TIME';
509
510 CURSOR c_get_rule_name(p_rule_id IN NUMBER) IS
511 SELECT name
512 FROM FTE_SEL_RULES
513 WHERE rule_id = p_rule_id;
514
515 l_entity_tab fte_cs_temp_entity_tab;
516 l_result_tab fte_acs_cache_pkg.fte_cs_result_attr_tab;
517 itr NUMBER;
518 l_itr NUMBER;
519 l_cnt NUMBER;
520 l_return_status VARCHAR2(1);
521 l_return_message VARCHAR2(2000);
522
523 l_rule_name VARCHAR2(30);
524 l_prev_leg_destination_id NUMBER;
525
526 l_min_transit_time NUMBER;
527 l_max_transit_time NUMBER;
528
529 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
530 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_FORMAT_RESULTS';
531
532 BEGIN
533
534 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
535
536 IF l_debug_on THEN
537 wsh_debug_sv.push (l_module_name);
538 END IF;
539
540 IF (p_use_gtt) THEN
541
542 --Depending on the entity we need to fetch the records.
543 OPEN c_get_rules_for_entity;
544 FETCH c_get_rules_for_entity BULK COLLECT INTO l_entity_tab;
545 CLOSE c_get_rules_for_entity;
546
547 ELSE
548 l_entity_tab(1).delivery_id := p_single_rec.delivery_id;
549 l_entity_tab(1).trip_id := p_single_rec.trip_id;
550 l_entity_tab(1).rule_id := p_single_rec.rule_id;
551 l_entity_tab(1).organization_id := p_single_rec.organization_id;
552 l_entity_tab(1).initial_pickup_loc_id := p_single_rec.initial_pickup_loc_id;
553 l_entity_tab(1).ultimate_dropoff_loc_id := p_single_rec.ultimate_dropoff_loc_id;
554 l_entity_tab(1).initial_pickup_date := p_single_rec.initial_pickup_date;
555 l_entity_tab(1).ultimate_dropoff_date := p_single_rec.ultimate_dropoff_date;
556
557 END IF;
558
559 --
560 -- At this stage we have the association to a common tab.
561 -- l_entity_tab has the records present in it.
562 --
563 itr := l_entity_tab.FIRST;
564
565 IF (itr IS NOT NULL) THEN
566 LOOP
567
568 OPEN c_get_rule_name(l_entity_tab(itr).rule_id);
569 FETCH c_get_rule_name INTO l_rule_name;
570 CLOSE c_get_rule_name;
571
572 IF (p_caller = 'ORDER_MGMT') THEN
573 OPEN c_get_transit_time_for_rule(l_entity_tab(itr).rule_id);
574 FETCH c_get_transit_time_for_rule INTO l_min_transit_time,l_max_transit_time;
575 IF (c_get_transit_time_for_rule%NOTFOUND) THEN
576 l_min_transit_time := NULL;
577 l_max_transit_time := NULL;
578 END IF;
579 CLOSE c_get_transit_time_for_rule;
580 END IF;
581
582 --
583 -- get_result_for_rule will return results in sorted fashion.
584 -- Multileg result -> A B C
585 -- Ranked results will be in order 1,2,3
586 -- (This procedure wont return any other thing)
587 -- Reason for doing this : Only this information can be cached.
588 -- Other information will not be cached.
589 --
590
591 FTE_ACS_CACHE_PKG.get_results_for_rule(p_rule_id => l_entity_tab(itr).rule_id,
592 x_result_tab => l_result_tab,
593 x_return_status => l_return_status);
594
595 IF l_debug_on THEN
596 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
597 END IF;
598
599 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
600 raise FND_API.G_EXC_UNEXPECTED_ERROR;
601 END IF;
602
603 --
604 -- Generate the result tab using it.
605 --
606 l_itr := l_result_tab.FIRST;
607
608 --
609 -- Copy the result in the output format.
610 --
611 IF (l_itr IS NOT NULL) THEN
612 LOOP
613 -- Initally count will be zero.
614 l_cnt := x_cs_output_tab.COUNT;
615 x_cs_output_tab(l_cnt).rule_id := l_entity_tab(itr).rule_id;
616 x_cs_output_tab(l_cnt).rule_name := l_rule_name;
617 x_cs_output_tab(l_cnt).delivery_id := l_entity_tab(itr).delivery_id;
618 -- AG add organization_id to output
619 x_cs_output_tab(l_cnt).organization_id := l_entity_tab(itr).organization_id;
620 x_cs_output_tab(l_cnt).trip_id := l_entity_tab(itr).trip_id;
621 --
622 -- Procedure get_result_for_rule returns the following
623 -- It returns us the ship methods also. Need to check if there are any issues.
624 --
625 x_cs_output_tab(l_cnt).result_type := l_result_tab(l_itr).result_type;
626 x_cs_output_tab(l_cnt).rank := l_result_tab(l_itr).rank;
627 x_cs_output_tab(l_cnt).leg_destination := l_result_tab(l_itr).leg_destination;
628 x_cs_output_tab(l_cnt).leg_sequence := l_result_tab(l_itr).leg_sequence;
629 -- x_cs_output_tab(l_cnt).itinerary_id := l_result_tab(l_itr).itinerary_id;
630 x_cs_output_tab(l_cnt).carrier_id := l_result_tab(l_itr).carrier_id;
631 x_cs_output_tab(l_cnt).mode_of_transport := l_result_tab(l_itr).mode_of_transport;
632 x_cs_output_tab(l_cnt).service_level := l_result_tab(l_itr).service_level;
633
634 --
635 -- Other Get_Result_for_rule will not return the ship method.
636 -- Get_Result_for_rule will have caching.
637 --
638
639 FTE_ACS_RULE_UTIL_PKG.GET_SHIP_METHOD_CODE( p_carrier_id => l_result_tab(l_itr).carrier_id,
640 p_service_level => l_result_tab(l_itr).service_level,
641 p_mode_of_transport => l_result_tab(l_itr).mode_of_transport,
642 p_org_id => l_entity_tab(itr).organization_id,
643 x_ship_method_code => x_cs_output_tab(l_cnt).ship_method_code ,
644 x_return_status => l_return_status,
645 x_return_message => l_return_message);
646
647 IF l_debug_on THEN
648 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
649 END IF;
650
651 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR) THEN
652 raise FND_API.G_EXC_UNEXPECTED_ERROR;
653 END IF;
654
655 x_cs_output_tab(l_cnt).freight_terms_code := l_result_tab(l_itr).freight_terms_code;
656 x_cs_output_tab(l_cnt).consignee_carrier_ac_no := l_result_tab(l_itr).consignee_carrier_ac_no;
657 -- x_cs_output_tab(l_cnt).track_only_flag := l_result_tab(l_itr).track_only_flag;
658 x_cs_output_tab(l_cnt).result_level := l_result_tab(l_itr).result_level;
659
660 --If Caller is Order Management, then pass the transit time
661 IF (p_caller = 'ORDER_MGMT') THEN
662 x_cs_output_tab(l_cnt).min_transit_time := l_min_transit_time;
663 x_cs_output_tab(l_cnt).max_transit_time := l_max_transit_time;
664 END IF;
665
666 --
667 -- Modify the dates
668 --
669
670 IF (l_result_tab(l_itr).result_type ='RANK') THEN
671
672 --
673 --Result Format is : Delivery ID - Rank 1 - Carrier 1 - pick up date - drop off date
674 --
675 x_cs_output_tab(l_cnt).initial_pickup_location_id := l_entity_tab(itr).initial_pickup_loc_id;
676 x_cs_output_tab(l_cnt).ultimate_dropoff_location_id := l_entity_tab(itr).ultimate_dropoff_loc_id;
677 x_cs_output_tab(l_cnt).pickup_date := l_entity_tab(itr).initial_pickup_date;
678 x_cs_output_tab(l_cnt).dropoff_date := l_entity_tab(itr).ultimate_dropoff_date;
679
680 ELSIF (l_result_tab(l_itr).result_type = 'MULTILEG') THEN
681
682 --
683 -- ask whether multileg trips should give error here or at some other point.
684 -- result Format is : Delivery ID - Pickup A - Dropoff B - pickup date - NULL
685 -- Delivery ID - Pickup B - Dropoff C - NULL - drop off date
686 -- Finish general coding first - Later think of exceptional cases.
687
688 --IF (l_result_tab(l_itr).leg_sequence = 1) THEN
689 IF l_itr = l_result_tab.FIRST THEN
690 x_cs_output_tab(l_cnt).pickup_date := l_entity_tab(itr).initial_pickup_date;
691 x_cs_output_tab(l_cnt).initial_pickup_location_id := l_entity_tab(itr).initial_pickup_loc_id;
692 ELSE
693 x_cs_output_tab(l_cnt).initial_pickup_location_id := l_prev_leg_destination_id;
694 END IF;
695
696 x_cs_output_tab(l_cnt).ultimate_dropoff_location_id := l_result_tab(l_itr).leg_destination;
697 l_prev_leg_destination_id := l_result_tab(l_itr).leg_destination;
698
699 IF (l_itr = l_result_tab.LAST) THEN
700 -- Last stop we need to populate the ulitmate drop off location also.
701 x_cs_output_tab(l_cnt).dropoff_date := l_entity_tab(itr).ultimate_dropoff_date;
702 x_cs_output_tab(l_cnt).ultimate_dropoff_location_id := l_entity_tab(itr).ultimate_dropoff_loc_id;
703 END IF;
704 END IF;
705
706 EXIT WHEN l_itr = l_result_tab.LAST;
707 l_itr := l_result_tab.NEXT(l_itr);
708
709 END LOOP;
710 END IF;
711
712 EXIT WHEN itr = l_entity_tab.LAST;
713 itr := l_entity_tab.NEXT(itr);
714 END LOOP;
715 END IF;
716
717 IF l_debug_on THEN
718
719 itr := x_cs_output_tab.FIRST;
720 IF (itr IS NOT NULL) THEN
721 LOOP
722 WSH_DEBUG_SV.logmsg(l_module_name,'****NEW RECORD ******');
723 WSH_DEBUG_SV.log(l_module_name,'Rule_id ',x_cs_output_tab(itr).rule_id);
724 WSH_DEBUG_SV.log(l_module_name,'Rule_name ',x_cs_output_tab(itr).rule_name);
725 WSH_DEBUG_SV.log(l_module_name,'Delivery_id ',x_cs_output_tab(itr).delivery_id);
726 WSH_DEBUG_SV.log(l_module_name,'Initial_pickup_location_id ',x_cs_output_tab(itr).initial_pickup_location_id);
727 WSH_DEBUG_SV.log(l_module_name,'Ultimate_dropoff_location_id ',x_cs_output_tab(itr).ultimate_dropoff_location_id);
728 WSH_DEBUG_SV.log(l_module_name,'Trip_id ',x_cs_output_tab(itr).trip_id);
729 WSH_DEBUG_SV.log(l_module_name,'Result_type ',x_cs_output_tab(itr).result_type);
730 WSH_DEBUG_SV.log(l_module_name,'Rank ',x_cs_output_tab(itr).rank);
731 WSH_DEBUG_SV.log(l_module_name,'Leg_destination ',x_cs_output_tab(itr).leg_destination);
732 WSH_DEBUG_SV.log(l_module_name,'Leg_sequence ',x_cs_output_tab(itr).leg_sequence);
733 WSH_DEBUG_SV.log(l_module_name,'Carrier_id ',x_cs_output_tab(itr).carrier_id);
734 WSH_DEBUG_SV.log(l_module_name,'Mode_of_transport ',x_cs_output_tab(itr).mode_of_transport);
735 WSH_DEBUG_SV.log(l_module_name,'Service_level ',x_cs_output_tab(itr).service_level);
736 WSH_DEBUG_SV.log(l_module_name,'Ship_method_code ',x_cs_output_tab(itr).ship_method_code);
737 WSH_DEBUG_SV.log(l_module_name,'Freight_terms_code ',x_cs_output_tab(itr).freight_terms_code);
738 WSH_DEBUG_SV.log(l_module_name,'Consignee_carrier_ac_no ',x_cs_output_tab(itr).consignee_carrier_ac_no);
739 WSH_DEBUG_SV.log(l_module_name,'Result_level ',x_cs_output_tab(itr).result_level);
740 WSH_DEBUG_SV.log(l_module_name,'Pickup_date ',x_cs_output_tab(itr).pickup_date);
741 WSH_DEBUG_SV.log(l_module_name,'Dropoff_date ',x_cs_output_tab(itr).dropoff_date);
742 WSH_DEBUG_SV.log(l_module_name,'Min_transit_time ',x_cs_output_tab(itr).min_transit_time);
743 WSH_DEBUG_SV.log(l_module_name,'Max_transit_time ',x_cs_output_tab(itr).max_transit_time);
744 WSH_DEBUG_SV.log(l_module_name,'Append_flag ',x_cs_output_tab(itr).append_flag);
745 --WSH_DEBUG_SV.log(l_module_name,'Routing_rule_id ',x_cs_output_tab(itr).routing_rule_id);
746
747 EXIT WHEN itr = x_cs_output_tab.LAST;
748 itr := x_cs_output_tab.NEXT(itr);
749 END LOOP;
750 END IF;
751 wsh_debug_sv.pop(l_module_name);
752 END IF;
753 EXCEPTION
754 WHEN OTHERS THEN
755 IF (c_get_rules_for_entity%ISOPEN) THEN
756 CLOSE c_get_rules_for_entity;
757 END IF;
758
759 IF (c_get_transit_time_for_rule%ISOPEN) THEN
760 CLOSE c_get_transit_time_for_rule;
761 END IF;
762
763 IF (c_get_rule_name%ISOPEN) THEN
764 CLOSE c_get_rule_name;
765 END IF;
766
767 WSH_UTIL_CORE.default_handler('FTE_ACS_PKG.get_routing_rules');
768 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
769 --
770 IF l_debug_on THEN
771 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
772 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
773 END IF;
774 END GET_FORMAT_RESULTS;
775
776
777 --***************************************************************************--
778 --========================================================================
779 -- PROCEDURE : get_routing_results PUBLIC
780 --
781 -- PARAMETERS: p_start_search_level_flag IN Default Start Search level flag
782 -- p_format_cs_tab IN Input table of record
783 -- p_entity IN Entity for which routing rules are being found out
784 -- p_messaging_yn IN Flag indicating whether messaging has to be done or not
785 -- p_caller IN Caller of routing rules engine
786 -- x_cs_output_tab OUT Result Table
787 -- x_cs_output_message_tab OUT Output message table
788 -- x_return_message OUT Return message
789 -- x_return_status OUT Return Status
790 --
791 -- COMMENT : Returns the routing results associated with a given rule
792 --***************************************************************************--
793 PROCEDURE GET_ROUTING_RESULTS( --p_start_search_level_flag IN VARCHAR2,
794 p_format_cs_tab IN OUT NOCOPY FTE_ACS_PKG.FTE_CS_ENTITY_TAB_TYPE,
795 p_entity IN VARCHAR2,
796 p_messaging_yn IN VARCHAR2,
797 p_caller IN VARCHAR2,
798 x_cs_output_tab OUT NOCOPY FTE_ACS_PKG.FTE_CS_RESULT_TAB_TYPE,
799 x_cs_output_message_tab OUT NOCOPY FTE_ACS_PKG.FTE_CS_OUTPUT_MESSAGE_TAB,
800 x_return_message OUT NOCOPY VARCHAR2,
801 x_return_status OUT NOCOPY VARCHAR2)
802 IS
803
804 l_entities_left NUMBER;
805 l_single_entity BOOLEAN;
806 l_use_gtt BOOLEAN;
807
808 l_entity_rec FTE_ACS_PKG.fte_cs_entity_rec_type;
809 l_search_level_tab WSH_UTIL_CORE.column_tab_type;
810
811 l_output_tab FTE_ACS_CACHE_PKG.fte_cs_entity_attr_tab;
812 l_entity_info FTE_ACS_CACHE_PKG.fte_cs_entity_attr_rec;
813
814 l_entity_rule_rec fte_cs_entity_rule_rec;
815 l_entity_cnt NUMBER;
816 l_return_status VARCHAR2(1);
817 l_first NUMBER;
818 l_last NUMBER;
819 itr NUMBER;
820
821 l_rule_id NUMBER;
822 l_cnt NUMBER;
823
824 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
825 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ROUTING_RESULTS';
826 BEGIN
827
828 IF l_debug_on THEN
829 wsh_debug_sv.push (l_module_name);
830 END IF;
831
832 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
833
834 --
835 -- Format the data.
836 -- a) Determine Freight Term code levels
837 -- b) Get the Transit Time.
838 --
839
840 FTE_ACS_RULE_UTIL_PKG.FORMAT_ENTITY_INFO( p_input_cs_tab => p_format_cs_tab,
841 p_entity => p_entity,
842 x_return_status => l_return_status);
843
844 IF l_debug_on THEN
845 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
846 END IF;
847
848 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
849 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
850 raise FND_API.G_EXC_UNEXPECTED_ERROR;
851 END IF;
852 END IF;
853
854 l_entities_left := p_format_cs_tab.COUNT;
855
856 IF (p_format_cs_tab.COUNT= 1) THEN
857
858 l_entity_rec := p_format_cs_tab(p_format_cs_tab.FIRST);
859 l_single_entity := TRUE;
860 l_use_gtt := FALSE;
861
862 l_cnt := 1;
863 IF (p_entity='TRIP') THEN
864 -- 'I' means Organization id of the first stop
865 -- AG trip's origin org
866 IF l_entity_rec.triporigin_internalorg_id IS NOT NULL THEN
867 l_search_level_tab(l_cnt) := 'I';
868 l_cnt := l_cnt+1;
869 END IF;
870 END IF;
871 IF l_entity_rec.customer_site_id IS NOT NULL THEN
872 l_search_level_tab(l_cnt) := 'S'; -- Customer Site
873 l_cnt := l_cnt+1;
874 END IF;
875 IF l_entity_rec.customer_id IS NOT NULL THEN
876 l_search_level_tab(l_cnt) := 'C'; -- Customer
877 l_cnt := l_cnt+1;
878 END IF;
879 IF l_entity_rec.organization_id IS NOT NULL THEN
880 l_search_level_tab(l_cnt) := 'O'; -- Organization
881 l_cnt := l_cnt+1;
882 END IF;
883 l_search_level_tab(l_cnt) := 'E'; -- Enterprise
884 IF l_debug_on THEN
885 WSH_DEBUG_SV.logmsg(l_module_name,'***NEW RECORD ********');
886 WSH_DEBUG_SV.log(l_module_name,'delivery_id ',l_entity_rec.delivery_id);
887 WSH_DEBUG_SV.log(l_module_name,'trip_id ',l_entity_rec.trip_id);
888 WSH_DEBUG_SV.log(l_module_name,'delivery_name ',l_entity_rec.delivery_name);
889 WSH_DEBUG_SV.log(l_module_name,'trip_name ',l_entity_rec.trip_name);
890 WSH_DEBUG_SV.log(l_module_name,'organization_id ',l_entity_rec.organization_id);
891 WSH_DEBUG_SV.log(l_module_name,'triporigin_internalorg_id ',l_entity_rec.triporigin_internalorg_id);
892 WSH_DEBUG_SV.log(l_module_name,'customer_id ',l_entity_rec.customer_id);
893 WSH_DEBUG_SV.log(l_module_name,'customer_site_id ',l_entity_rec.customer_site_id);
894 WSH_DEBUG_SV.log(l_module_name,'gross_weight ', l_entity_rec.gross_weight);
895 WSH_DEBUG_SV.log(l_module_name,'weight_uom_code ',l_entity_rec.weight_uom_code);
896 WSH_DEBUG_SV.log(l_module_name,'volume ', l_entity_rec.volume);
897 WSH_DEBUG_SV.log(l_module_name,'volume_uom_code ', l_entity_rec.volume_uom_code);
898 WSH_DEBUG_SV.log(l_module_name,'initial_pickup_loc_id ', l_entity_rec.initial_pickup_loc_id);
899 WSH_DEBUG_SV.log(l_module_name,'ultimate_dropoff_loc_id ', l_entity_rec.ultimate_dropoff_loc_id);
900 WSH_DEBUG_SV.log(l_module_name,'initial_pickup_date ', nvl(l_entity_rec.initial_pickup_date,SYSDATE));
901 WSH_DEBUG_SV.log(l_module_name,'ultimate_dropoff_date ', l_entity_rec.ultimate_dropoff_date);
902 WSH_DEBUG_SV.log(l_module_name,'freight_terms_code ',l_entity_rec.freight_terms_code);
903 WSH_DEBUG_SV.log(l_module_name,'fob_code ',l_entity_rec.fob_code);
904 WSH_DEBUG_SV.log(l_module_name,'start_search_level ',l_entity_rec.start_search_level);
905 WSH_DEBUG_SV.log(l_module_name,'transit_time ',l_entity_rec.transit_time);
906 END IF;
907 ELSE
908
909 /*
910 l_cnt := 1;
911 IF (p_entity='TRIP') THEN
912 -- 'I' means Organization id of the first stop
913 l_search_level_tab(l_cnt) := 'I';
914 l_cnt := l_cnt+1;
915 END IF;
916 l_search_level_tab(l_cnt) := 'S'; -- Customer Site
917 l_cnt := l_cnt+1;
918 l_search_level_tab(l_cnt) := 'C'; -- Customer
919 l_cnt := l_cnt+1;
920 l_search_level_tab(l_cnt) := 'O'; -- Organization
921 l_cnt := l_cnt+1;
922 l_search_level_tab(l_cnt) := 'E'; -- Enterprise
923 */
924
925 FTE_ACS_RULE_UTIL_PKG.INSERT_INTO_GTT( p_input_data => p_format_cs_tab,
926 x_return_status => l_return_status);
927
928 IF l_debug_on THEN
929 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
930 END IF;
931
932 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
933 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
934 raise FND_API.G_EXC_UNEXPECTED_ERROR;
935 END IF;
936 END IF;
937
938 l_single_entity := FALSE;
939 l_use_gtt := TRUE;
940
941 -- AG moved here from above
942 l_cnt := 1;
943 IF (p_entity='TRIP') THEN
944 -- 'I' means Organization id of the first stop
945 l_search_level_tab(l_cnt) := 'I';
946 l_cnt := l_cnt+1;
947 END IF;
948 l_search_level_tab(l_cnt) := 'S'; -- Customer Site
949 l_cnt := l_cnt+1;
950 l_search_level_tab(l_cnt) := 'C'; -- Customer
951 l_cnt := l_cnt+1;
952 l_search_level_tab(l_cnt) := 'O'; -- Organization
953 l_cnt := l_cnt+1;
954 l_search_level_tab(l_cnt) := 'E'; -- Enterprise
955
956 END IF;
957
958 --
959 -- Data has been inserted into the GTT or it is there in the record.
960 -- For every level - Query the database or use the record.
961 --
962
963 --
964 -- For a trip sequence followed is-Organization of first stop,Customer Site,Customer,Organization of last stop,Enterprise
965 -- For a delivery sequence is - Customer Site, Customer , Organization and Enterprise
966 --
967
968 /*l_cnt := 1;
969 IF (p_entity='TRIP') THEN
970 -- 'I' means Organization id of the first stop
971 l_search_level_tab(l_cnt) := 'I';
972 l_cnt := l_cnt+1;
973 END IF;
974 l_search_level_tab(l_cnt) := 'S'; -- Customer Site
975 l_cnt := l_cnt+1;
976 l_search_level_tab(l_cnt) := 'C'; -- Customer
977 l_cnt := l_cnt+1;
978 l_search_level_tab(l_cnt) := 'O'; -- Organization
979 l_cnt := l_cnt+1;
980 l_search_level_tab(l_cnt) := 'E'; -- Enterprise */
981
982 --
983 -- Loop condition is when all entities have been exhausted or all levels have been.
984 -- a) Whenever a result is found - decrement it.
985 -- l_entities_left-1, Exit when l_entities_left is 0
986 -- b) All levels exhausted.
987
988 FOR i IN l_search_level_tab.FIRST ..l_search_level_tab.LAST
989 LOOP
990
991 --
992 -- If we have 1 record then p_single_rec will be populated
993 -- Query Global Temporary table only if p_use_gtt IS TRUE ;
994 --
995
996 FTE_ACS_RULE_UTIL_PKG.GET_CANDIDATE_RECORDS( p_search_level => l_search_level_tab(i),
997 p_query_gtt => l_use_gtt,
998 p_single_rec => l_entity_rec,
999 x_output_tab => l_output_tab,
1000 x_return_status => l_return_status);
1001
1002 IF l_debug_on THEN
1003 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
1004 END IF;
1005
1006 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1007 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1008 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1009 END IF;
1010 END IF;
1011
1012 itr := l_output_tab.FIRST;
1013
1014 IF (itr IS NOT NULL) THEN
1015
1016 l_entity_rule_rec.entity_id_tab.DELETE;
1017 l_entity_rule_rec.rule_id_tab.DELETE;
1018
1019 l_entity_cnt := 0;
1020 -- l_first is index used while doing bulk update
1021 l_first := l_entity_cnt;
1022
1023 LOOP
1024
1025 l_entity_info := l_output_tab(itr);
1026
1027 FTE_ACS_CACHE_PKG.GET_MATCHING_RULE( p_info => l_entity_info,
1028 x_rule_id => l_rule_id,
1029 x_return_status => l_return_status);
1030
1031 IF l_debug_on THEN
1032 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
1033 WSH_DEBUG_SV.log(l_module_name,'l_rule_id ',l_rule_id);
1034 END IF;
1035
1036 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1037 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1038 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1039 END IF;
1040 END IF;
1041
1042 IF (l_rule_id <> FTE_ACS_CACHE_PKG.g_rule_not_found) THEN
1043
1044 l_entities_left := l_entities_left - 1;
1045
1046 IF NOT(l_single_entity) THEN
1047 --Input will be either delivery or trip.
1048 IF (p_entity IN ('DLVY','PSEUDO_DLVY')) THEN
1049 l_entity_rule_rec.entity_id_tab(l_entity_cnt) := l_output_tab(itr).delivery_id;
1050 ELSIF (p_entity = 'TRIP') THEN
1051 l_entity_rule_rec.entity_id_tab(l_entity_cnt) := l_output_tab(itr).trip_id;
1052 END IF;
1053 l_entity_rule_rec.rule_id_tab(l_entity_cnt) := l_rule_id;
1054 l_entity_cnt := l_entity_cnt + 1;
1055 ELSE
1056 l_entity_rec.rule_id := l_rule_id;
1057 END IF;
1058
1059 END IF;
1060
1061 EXIT WHEN itr = l_output_tab.LAST;
1062 itr:= l_output_tab.NEXT(itr);
1063
1064 END LOOP;
1065 -- We have incremented l_entity_cnt extra by 1
1066 l_last := l_entity_cnt-1;
1067 END IF; -- IF (itr IS NOT NULL) THEN --}
1068
1069 --
1070 -- Update the GTT with the rule id
1071 --
1072 IF NOT(l_single_entity) THEN
1073
1074 IF (p_entity = 'TRIP') THEN
1075 FORALL j in l_first .. l_last
1076 UPDATE FTE_SEL_SEARCH_ENTITIES_TMP
1077 SET rule_id = l_entity_rule_rec.rule_id_tab(j)
1078 WHERE trip_id = l_entity_rule_rec.entity_id_tab(j);
1079
1080 ELSIF (p_entity IN ('DLVY','PSEUDO_DLVY')) THEN
1081 FORALL j in l_first .. l_last
1082 UPDATE FTE_SEL_SEARCH_ENTITIES_TMP
1083 SET rule_id = l_entity_rule_rec.rule_id_tab(j)
1084 WHERE delivery_id = l_entity_rule_rec.entity_id_tab(j);
1085 END IF;
1086 END IF;
1087
1088 IF (l_entities_left = 0 ) THEN
1089 EXIT ;
1090 END IF;
1091
1092 END LOOP;--FOR i IN l_search_level_tab.FIRST ..l_search_level_tab.LAST
1093 --
1094 -- Search over : Get Formatted Results for the entites.
1095 -- If data not there then log exceptions.
1096 --
1097
1098 IF l_debug_on THEN
1099 WSH_DEBUG_SV.log(l_module_name,'l_entities_left ',l_entities_left);
1100 END IF;
1101
1102 --
1103 -- In case of single records need to call get_format_resutls only when entity is
1104 -- associated with a result.
1105 -- In case of multirecords call is made to check if resutls are present there for
1106 -- any of the records.
1107
1108 IF ((l_single_entity AND (l_entities_left=0))
1109 OR NOT l_single_entity)
1110 THEN
1111 GET_FORMAT_RESULTS( p_caller => p_caller,
1112 p_use_gtt => l_use_gtt,
1113 p_single_rec => l_entity_rec,
1114 x_cs_output_tab => x_cs_output_tab,
1115 x_return_status => l_return_status);
1116
1117 IF l_debug_on THEN
1118 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
1119 END IF;
1120
1121 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1122 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1123 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1124 END IF;
1125 END IF;
1126 END IF;
1127
1128 IF (l_entities_left <> 0 ) THEN
1129
1130 --
1131 -- Exceptions are not logged when the action is Select Carrier from Shipping
1132 -- Transactions form or Apply Routing Rules from FTE. Only a warning message is shown.
1133 --
1134 IF (p_caller NOT IN('WSH_FSTRX','FTE_MLS_WRAPPER','ORDER_MGMT')) THEN
1135
1136 LOG_EXCEPTIONS( p_caller => p_caller,
1137 p_entity => p_entity,
1138 p_use_gtt => l_use_gtt,
1139 p_single_rec => l_entity_rec,
1140 x_cs_output_message_tab => x_cs_output_message_tab,
1141 x_return_status => l_return_status);
1142
1143 IF l_debug_on THEN
1144 WSH_DEBUG_SV.log(l_module_name,'l_return Status ',l_return_status);
1145 END IF;
1146
1147 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1148 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_WARNING THEN
1149 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1150 END IF;
1151 END IF;
1152
1153 ELSE
1154 --
1155 -- Put Message on the stack and return
1156 -- We can have multirecords in this case
1157 --
1158
1159 FND_MESSAGE.SET_NAME('WSH','WSH_FTE_CS_NO_CARRIER_SELECTED');
1160 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1161 WSH_UTIL_CORE.add_message(x_return_status);
1162
1163 END IF;
1164
1165 END IF;
1166
1167 IF l_debug_on THEN
1168 wsh_debug_sv.pop (l_module_name);
1169 END IF;
1170
1171 EXCEPTION
1172
1173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1174 --
1175 WSH_UTIL_CORE.default_handler('FTE_ACS_PKG.get_routing_rules');
1176 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1177 --
1178 IF l_debug_on THEN
1179 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1180 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1181 END IF;
1182
1183 WHEN OTHERS THEN
1184 --
1185 WSH_UTIL_CORE.default_handler('FTE_ACS_PKG.get_routing_rules');
1186 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1187 --
1188 IF l_debug_on THEN
1189 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1190 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1191 END IF;
1192
1193 END GET_ROUTING_RESULTS;
1194
1195 END FTE_ACS_PKG;