DBA Data[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;