1 PACKAGE BODY FTE_DIST_INT_PKG AS
2 /* $Header: FTEDISIB.pls 120.2 2006/04/12 15:42:25 susurend noship $ */
3 -- -------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_DISTANCE_INT_PKG --
6 -- TYPE: PACKAGE BODY --
7 -- DESCRIPTION: Contains core procedures for accessing and retrieving distance--
8 -- and transit time information from FTE_LOCATION_MILEAGES table --
9 -- --
10 -- --
11 -- CHANGE CONTROL LOG --
12 -- --
13 -- DATE VERSION BY BUG DESCRIPTION --
14 -- ---------- ------- -------- ------- ---------------------------------- --
15 -- 2003/07/14 J ABLUNDEL Created. --
16 -- --
17 -- -------------------------------------------------------------------------- --
18
19 -- -------------------------------------------------------------------------- --
20 -- Global Package Variables --
21 -- ------------------------ --
22 -- --
23 -- -------------------------------------------------------------------------- --
24
25 --
26 -- Global flag constants for location and region flags
27 --
28 g_location_search_flag CONSTANT VARCHAR2(1) := 'L';
29 g_region_search_flag CONSTANT VARCHAR2(1) := 'R';
30
31 --
32 -- Global table for storing messages found during execution of the API
33 --
34 g_message_tab FTE_DIST_INT_PKG.fte_dist_output_message_tab;
35
36
37 --
38 -- For debug
39 --
40 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_DIST_INT_PKG';
41
42
43 -- -------------------------------------------------------------------------- --
44 -- --
45 -- PRIVATE PROCEDURE DEFINITIONS --
46 -- ----------------------------- --
47 -- Described in Procedure code below --
48 -- -------------------------------------------------------------------------- --
49 PROCEDURE DISTANCE_SEARCH(p_location_region_flag IN VARCHAR2,
50 p_messaging_yn IN VARCHAR2,
51 p_level IN VARCHAR2,
52 p_search_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_search_tab,
53 p_origin_reg_loc_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_num_table,
54 p_dest_reg_loc_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_num_table,
55 x_result_found OUT NOCOPY VARCHAR2,
56 x_result_table OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_output_tab,
57 x_return_message OUT NOCOPY VARCHAR2,
58 x_return_status OUT NOCOPY VARCHAR2);
59
60
61
62
63 -- -------------------------------------------------------------------------- --
64 -- --
65 -- NAME: GET_DISTANCE_TIME --
66 -- --
67 -- TYPE: PROCEDURE --
68 -- --
69 -- PARAMETERS (IN OUT): p_distance_input_tab IN OUT NOCOPY --
70 -- FTE_DIST_INT_PKG.fte_distance_input_tab --
71 -- p_location_region_flag IN VARCHAR2 --
72 -- p_messaging_yn IN VARCHAR2 --
73 -- p_api_version IN VARCHAR2 --
74 -- --
75 -- PARAMETERS (OUT): x_distance_output_tab OUT NOCOPY --
76 -- FTE_DIST_INT_PKG.fte_distance_output_tab --
77 -- x_distance_message_tab OUT NOCOPY --
78 -- FTE_DIST_INT_PKG.fte_distance_output_message_tab --
79 -- x_return_message OUT VARCHAR2, --
80 -- x_return_status OUT VARCHAR2 --
81 -- --
82 -- RETURN: n/a --
83 -- --
84 -- DESCRIPTION: This procedure initiates the search for distance and --
85 -- Transit times for given location or region origin/ --
86 -- destination id pairs. --
87 -- --
88 -- The input table, p_distance_input_tab, contains the --
89 -- combination of origin and destination ids for which --
90 -- the search is to be conducted. --
91 -- --
92 -- p_location_region_flag can have a value of 'L' (for --
93 -- Location) or 'R' (for Region). This flag dictates the --
94 -- id pairs in the input table as whether they are region--
95 -- (WSH_REGIONS.REGION_ID) or locations --
96 -- (WSH_LOCATIONS.LOCATION_ID). As the origin/destination--
97 -- pairs in FTE_LOCATION_MILEAGES are stored as region --
98 -- ids, if a table of locations is passed in then the --
99 -- associated regions will need to be found for those --
100 -- locations before the search/retrieval can be conducted--
101 -- --
102 -- p_messaging_yn indicates if messaging is to be enabled--
103 -- or not, in the case that a result is not found for a --
104 -- OD pair a message can be logged indicating this to the--
105 -- calling API, can also be used for other forms of --
106 -- messaging back to the calling API --
107 -- --
108 -- p_api_version is the version of the API? not used --
109 -- --
110 -- x_distance_output_tab is the output table of origin/ --
111 -- destination pairs and the found distance and transit --
112 -- times associated with those pairs. --
113 -- --
114 -- x_distance_message_tab is a table of messages that --
115 -- were logged during the search (only if p_messaging_yn --
116 -- = Y) --
117 -- --
118 -- x_return_message and x_return_status standard status --
119 -- and message return parameters --
120 -- --
121 -- CHANGE CONTROL LOG --
122 -- ------------------ --
123 -- --
124 -- DATE VERSION BY BUG DESCRIPTION --
125 -- ---------- ------- -------- ------- ---------------------------------- --
126 -- 2003/07/14 J ABLUNDEL Created --
127 -- --
128 -- -------------------------------------------------------------------------- --
129 PROCEDURE GET_DISTANCE_TIME(p_distance_input_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_input_tab,
130 p_location_region_flag IN VARCHAR2,
131 p_messaging_yn IN VARCHAR2,
132 p_api_version IN VARCHAR2,
133 p_command IN VARCHAR2,
134 x_distance_output_tab OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_output_tab,
135 x_distance_message_tab OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_output_message_tab,
136 x_return_message OUT NOCOPY VARCHAR2,
137 x_return_status OUT NOCOPY VARCHAR2) IS
138
139
140 --
141 -- Local Variable Definitions
142 --
143 l_distance_profile VARCHAR2(30); -- holds the FTE_DISTANCE_LVL profile option value
144 l_region_type NUMBER; -- holds the region type based on the profile value
145 l_ctr PLS_INTEGER; -- counter for populating the search table index
146 l_msg_ctr PLS_INTEGER; -- counter for populating message table index
147 l_result_found_flag VARCHAR2(1); -- Indicates if at least 1 OD pair found a distance
148 l_return_message VARCHAR2(2000); -- Return message from API (if error in API)
149 l_return_status VARCHAR2(1); -- Return Status from called API (values = S,E,W,U)
150 l_error_text VARCHAR2(2000); -- Holds the unexpected error text
151
152 --
153 -- Exception Handlers
154 --
155 FTE_DIST_NO_INPUT_DATA EXCEPTION;
156 FTE_DIST_INVALID_LOC_REG_FLAG EXCEPTION;
157 FTE_DIST_DISTANCE_SEARCH_ERR EXCEPTION;
158 FTE_DIST_INVALID_PROFILE EXCEPTION;
159 FTE_DIST_NULL_PROFILE EXCEPTION;
160 FTE_DIST_NO_REGS_FOR_ANY_LOCS EXCEPTION;
161 FTE_DIST_NO_REGS_IN_SEARCH EXCEPTION;
162 FTE_DIST_NULL_REGION_TYPE EXCEPTION;
163
164
165 --
166 -- Local Record and Table Definitions
167 --
168 l_origin_location_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
169 l_origin_region_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
170 l_loc_region_id_origin_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
171 l_destination_location_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
172 l_destination_region_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
173 l_loc_region_id_dest_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
174 l_region_idx_loc_orig_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
175 l_region_idx_loc_dest_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
176
177 l_search_tab FTE_DIST_INT_PKG.fte_dist_search_tab;
178
179 l_result_table FTE_DIST_INT_PKG.fte_dist_output_tab;
180
181 --
182 -- Message logging tables
183 --
184 msg_message_type_tab FTE_DIST_INT_PKG.fte_dist_tmp_flag_table;
185 msg_message_code_tab FTE_DIST_INT_PKG.fte_dist_tmp_code_table;
186 msg_message_text_tab FTE_DIST_INT_PKG.fte_dist_tmp_msg_table;
187 msg_location_region_flag_tab FTE_DIST_INT_PKG.fte_dist_tmp_flag_table;
188 msg_level_tab FTE_DIST_INT_PKG.fte_dist_tmp_code_table;
189 msg_table_origin_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
190 msg_table_destination_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
191 msg_input_origin_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
192 msg_input_destination_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
193
194
195
196 --
197 -- Cursor Definitions
198 --
199 -- ---------------------------------------------------------
200 -- Cursor to retrieve region ids from location ids
201 -- ---------------------------------------------------------
202 cursor c_get_regions_for_locs(cp_loc_id NUMBER,
203 cp_region_type NUMBER) IS
204 select wrl.location_id,
205 wrl.region_id
206 from wsh_region_locations wrl
207 where wrl.location_id = cp_loc_id
208 and wrl.region_type = cp_region_type;
209
210
211 --
212 -- Local Debug Variable Definitions
213 --
214 l_debug_on BOOLEAN;
215 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_DISTANCE_TIME';
216
217
218 BEGIN
219
220 --
221 -- set the debug flag
222 --
223 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
224 --
225 IF l_debug_on IS NULL THEN
226 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
227 END IF;
228 --
229 --
230 -- Debug Statements
231 --
232 IF l_debug_on THEN
233 WSH_DEBUG_SV.push(l_module_name);
234 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
235 WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
236 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_REGION_FLAG',p_location_region_flag);
237 WSH_DEBUG_SV.log(l_module_name,'P_MESSAGING_YN',p_messaging_yn);
238 WSH_DEBUG_SV.log(l_module_name,'P_API_VERSION',p_api_version);
239 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
240 WSH_DEBUG_SV.logmsg(l_module_name,'-------- p_distance_input_tab ------');
241
242 IF (p_distance_input_tab.COUNT > 0) THEN
243 FOR dbdit IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
244 WSH_DEBUG_SV.log(l_module_name,'origin_id', p_distance_input_tab(dbdit).origin_id);
245 WSH_DEBUG_SV.log(l_module_name,'destination_id',p_distance_input_tab(dbdit).destination_id);
246 END LOOP;
247 ELSE
248 WSH_DEBUG_SV.logmsg(l_module_name,'NO INPUT ATTRIBUTES IN p_distance_input_tab INPUT TABLE');
249 END IF;
250 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
251
252
253 END IF;
254
255 --
256 -- Set the return flags for the start of the procedure
257 --
258 x_return_message := null;
259 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
260
261 --
262 -- Clear the message tab
263 --
264 IF l_debug_on THEN
265 WSH_DEBUG_SV.logmsg(l_module_name,'resetting the global message table');
266 END IF;
267
268 g_message_tab.DELETE;
269
270
271 IF l_debug_on THEN
272 WSH_DEBUG_SV.logmsg(l_module_name,'resetting the local tables');
273 END IF;
274 --
275 -- Reset the local tables
276 --
277 l_origin_location_id_tab.DELETE;
278 l_origin_region_id_tab.DELETE;
279 l_loc_region_id_origin_tab.DELETE;
280 l_destination_location_id_tab.DELETE;
281 l_destination_region_id_tab.DELETE;
282 l_loc_region_id_dest_tab.DELETE;
283 l_region_idx_loc_orig_tab.DELETE;
284 l_region_idx_loc_dest_tab.DELETE;
285 l_search_tab.DELETE;
286 l_result_table.DELETE;
287
288
289 --
290 -- Reset the message tables
291 --
292 msg_message_type_tab.DELETE;
293 msg_message_code_tab.DELETE;
294 msg_message_text_tab.DELETE;
295 msg_location_region_flag_tab.DELETE;
296 msg_level_tab.DELETE;
297 msg_table_origin_id_tab.DELETE;
298 msg_table_destination_id_tab.DELETE;
299 msg_input_origin_id_tab.DELETE;
300 msg_input_destination_tab.DELETE;
301
302
303 --
304 -- Check that the input table has records, otherwise we cannot do a search
305 --
306 IF (p_distance_input_tab.COUNT < 1) THEN
307 --
308 -- No input data exists - return back an error
309 --
310 IF l_debug_on THEN
311 WSH_DEBUG_SV.log(l_module_name,' Input table has no data - p_distance_input_tab.COUNT',p_distance_input_tab.COUNT);
312 END IF;
313
314 RAISE FTE_DIST_NO_INPUT_DATA;
315
316 END IF;
317
318
319 --
320 -- Check that the location_region flag is correctly marked
321 -- otherwise we dont know what to search for
322 --
323 IF ((p_location_region_flag is null) OR
324 ((p_location_region_flag <> g_location_search_flag) AND
325 (p_location_region_flag <> g_region_search_flag))) THEN
326 --
327 -- Invalid location_region_flag, raise an error
328 --
329 IF l_debug_on THEN
330 WSH_DEBUG_SV.log(l_module_name,'location region input flag is invalid - p_location_region_flag',p_location_region_flag);
331 END IF;
332
333 RAISE FTE_DIST_INVALID_LOC_REG_FLAG;
334
335 END IF;
336
337
338 --
339 -- Get the profile option of the distance stuff to
340 -- see what region level we should be searching for
341 --
342 IF l_debug_on THEN
343 WSH_DEBUG_SV.logmsg(l_module_name,'getting the distance profile valie fnd_profile.get(FTE_DISTANCE_LVL)');
344 END IF;
345
346 fnd_profile.get('FTE_DISTANCE_LVL',l_distance_profile);
347
348 IF l_debug_on THEN
349 WSH_DEBUG_SV.log(l_module_name,'fnd_profile.get(FTE_DISTANCE_LVL)= ',l_distance_profile);
350 END IF;
351
352 IF (l_distance_profile is null) THEN
353 --
354 -- The profile option is null - raise an error
355 --
356 IF l_debug_on THEN
357 WSH_DEBUG_SV.logmsg(l_module_name,'distance profile is null raise FTE_DIST_NULL_PROFILE exception');
358 END IF;
359 RAISE FTE_DIST_NULL_PROFILE;
360
361 END IF;
362
363
364 --
365 -- Got the profile option value now get the corresponding region type
366 -- so that we can use it in our query to get the regions for the
367 -- locations - if the input is a table of location id OD pairs
368 --
369 -- Region Types to Profile Types
370 --
371 -- REGION TYPE PROFILE TYPE DESC
372 -- ------------ ------------ --------
373 -- 0 n/a COUNTRY
374 -- 1 n/a STATE
375 -- 2 CITYSTATE CITY
376 -- 3 ZIP ZIP/POSTAL
377 -- ???? (4) COUNTY COUNTY
378
379 IF (l_distance_profile = 'CITYSTATE') THEN
380 --
381 -- region type is city level
382 --
383 l_region_type := 2;
384
385 IF l_debug_on THEN
386 WSH_DEBUG_SV.log(l_module_name,'distance profile is CITYSTATE - region type = ',l_region_type);
387 END IF;
388 ELSIF (l_distance_profile = 'ZIP') THEN
389 --
390 -- region type is zip/postal level
391 --
392 l_region_type := 3;
393
394 IF l_debug_on THEN
395 WSH_DEBUG_SV.log(l_module_name,'distance profile is ZIP - region type = ',l_region_type);
396 END IF;
397 ELSIF (l_distance_profile = 'COUNTY') THEN
398 --
399 -- region type is county level
400 --
401 l_region_type := 4;
402 IF l_debug_on THEN
403 WSH_DEBUG_SV.log(l_module_name,'distance profile is COUNTY - region type = ',l_region_type);
404 END IF;
405 ELSE
406 --
407 -- The profile option has an invalid value - raise an error
408 --
409 IF l_debug_on THEN
410 WSH_DEBUG_SV.logmsg(l_module_name,'The profile option has an invalid value - raise an error RAISE FTE_DIST_INVALID_PROFILE');
411 END IF;
412
413 RAISE FTE_DIST_INVALID_PROFILE;
414
415 END IF;
416
417
418 IF (l_region_type is null) THEN
419 --
420 -- region type is null cannot have that Raise an error
421 --
422 IF l_debug_on THEN
423 WSH_DEBUG_SV.logmsg(l_module_name,'region type is null - raise an error FTE_DIST_NULL_REGION_TYPE');
424 END IF;
425 RAISE FTE_DIST_NULL_REGION_TYPE;
426
427 END IF;
428
429
430 --
431 -- Input data seems to be OK, now its time to rock and roll!!!
432 --
433 IF (p_location_region_flag = g_location_search_flag) THEN
434 --
435 -- The input is in location id form - convert the
436 -- locations to regions
437 --
438 IF l_debug_on THEN
439 WSH_DEBUG_SV.logmsg(l_module_name,'The input is in location id form going to get region mappings');
440 WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
441 END IF;
442
443 --
444 -- Search for origin region ids from the origin location id
445 --
446 -- reset the origin result tables
447 --
448 IF l_debug_on THEN
449 WSH_DEBUG_SV.logmsg(l_module_name,' Search for origin region ids from the origin location id - reset the origin result tables');
450 END IF;
451
452 l_origin_location_id_tab.DELETE;
453 l_origin_region_id_tab.DELETE;
454 l_loc_region_id_origin_tab.DELETE;
455
456 IF l_debug_on THEN
457 WSH_DEBUG_SV.logmsg(l_module_name,'LOOPING and opening cursor c_get_regions_for_locs(orig)');
458 END IF;
459
460
461 --
462 -- execute the query to get all the region ids for the passed in origin locations
463 --
464 FOR aaa IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
465 IF l_debug_on THEN
466 WSH_DEBUG_SV.logmsg(l_module_name,'Open c_get_regions_for_locs (orig) with ...');
467 WSH_DEBUG_SV.log(l_module_name,'aaa p_distance_input_tab(aaa).origin_id = ',p_distance_input_tab(aaa).origin_id);
468 WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
469 END IF;
470
471 OPEN c_get_regions_for_locs(p_distance_input_tab(aaa).origin_id,
472 l_region_type);
473 FETCH c_get_regions_for_locs BULK COLLECT INTO
474 l_origin_location_id_tab,
475 l_origin_region_id_tab;
476 CLOSE c_get_regions_for_locs;
477
478
479
480 IF (l_origin_location_id_tab.COUNT > 0) THEN
481 --
482 -- Origin regions and locations were returned by the query, now we create mapping
483 -- tables of locations to regions
484 --
485 IF l_debug_on THEN
486 WSH_DEBUG_SV.log(l_module_name,'l_origin_location_id_tab.COUNT = ',l_origin_location_id_tab.COUNT);
487 END IF;
488
489 FOR bbb in l_origin_location_id_tab.FIRST..l_origin_location_id_tab.LAST LOOP
490 IF l_debug_on THEN
491 WSH_DEBUG_SV.log(l_module_name,'l_origin_location_id_tab(bbb) = ',l_origin_location_id_tab(bbb));
492 END IF;
493
494 l_loc_region_id_origin_tab(l_origin_location_id_tab(bbb)) := l_origin_region_id_tab(bbb);
495 IF l_debug_on THEN
496 WSH_DEBUG_SV.log(l_module_name,'l_loc_region_id_origin_tab(l_origin_location_id_tab(bbb)) =',l_loc_region_id_origin_tab(l_origin_location_id_tab(bbb)));
497 END IF;
498 END LOOP;
499 END IF;
500
501 END LOOP;
502
503 --
504 -- Search for destination region ids from the destination location id
505 --
506 -- Reset the destination result tables
507 --
508 IF l_debug_on THEN
509 WSH_DEBUG_SV.logmsg(l_module_name,' Search for destination region ids from the destination location id - reset the destination result tables');
510 END IF;
511
512 l_destination_location_id_tab.DELETE;
513 l_destination_region_id_tab.DELETE;
514 l_loc_region_id_dest_tab.DELETE;
515
516 IF l_debug_on THEN
517 WSH_DEBUG_SV.logmsg(l_module_name,'LOOPING and opening cursor c_get_regions_for_locs (dest)');
518 END IF;
519
520
521 --
522 -- Run the query to get the locations and regions for the input destination
523 -- locations
524 --
525 FOR ccc IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
526
527 IF l_debug_on THEN
528 WSH_DEBUG_SV.logmsg(l_module_name,'Open c_get_regions_for_locs (dest) with ...');
529 WSH_DEBUG_SV.log(l_module_name,'aaa p_distance_input_tab(ccc).destination_id = ',p_distance_input_tab(ccc).destination_id);
530 WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
531 END IF;
532
533 OPEN c_get_regions_for_locs(p_distance_input_tab(ccc).destination_id,
534 l_region_type);
535 FETCH c_get_regions_for_locs BULK COLLECT INTO
536 l_destination_location_id_tab,
537 l_destination_region_id_tab;
538 CLOSE c_get_regions_for_locs;
539
540
541 IF (l_destination_location_id_tab.COUNT > 0) THEN
542 --
543 -- The query returned some records now create mapping tables of locations
544 -- to regions
545 --
546 IF l_debug_on THEN
547 WSH_DEBUG_SV.log(l_module_name,'l_destination_location_id_tab.COUNT = ',l_destination_location_id_tab.COUNT);
548 END IF;
549
550
551 FOR ddd in l_destination_location_id_tab.FIRST..l_destination_location_id_tab.LAST LOOP
552 IF l_debug_on THEN
553 WSH_DEBUG_SV.log(l_module_name,'l_destination_location_id_tab(ddd) = ',l_destination_location_id_tab(ddd));
554 END IF;
555
556 l_loc_region_id_dest_tab(l_destination_location_id_tab(ddd)) := l_destination_region_id_tab(ddd);
557
558 IF l_debug_on THEN
559 WSH_DEBUG_SV.log(l_module_name,'l_loc_region_id_dest_tab(l_destination_location_id_tab(ddd)) =',l_loc_region_id_dest_tab(l_destination_location_id_tab(ddd)));
560 END IF;
561 END LOOP;
562 END IF;
563 END LOOP;
564
565
566
567 --
568 -- Populate the search table with OD pairs and Check if
569 -- any input locations do not have regions, i.e. were not
570 -- found during the search
571 --
572 IF l_debug_on THEN
573 WSH_DEBUG_SV.logmsg(l_module_name,'Populate the search table with OD pairs and Check ifany input locations do not have regions');
574 END IF;
575
576 --
577 -- reset the search table index
578 --
579 l_ctr := 0;
580 FOR eee IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
581
582 IF l_debug_on THEN
583 WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(eee).origin_id = ',p_distance_input_tab(eee).origin_id);
584 WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(eee).destination_id = ',p_distance_input_tab(eee).destination_id);
585 END IF;
586
587
588 IF (l_loc_region_id_origin_tab.EXISTS(p_distance_input_tab(eee).origin_id)) THEN
589 IF l_debug_on THEN
590 WSH_DEBUG_SV.log(l_module_name,'A region for the location exists p_distance_input_tab(eee).origin_id = ',p_distance_input_tab(eee).origin_id);
591 END IF;
592
593 --
594 -- origin exists, check the destination
595 --
596 IF (l_loc_region_id_dest_tab.EXISTS(p_distance_input_tab(eee).destination_id)) THEN
597 IF l_debug_on THEN
598 WSH_DEBUG_SV.log(l_module_name,'A region for the destination location exists p_distance_input_tab(eee).destination_id = ',p_distance_input_tab(eee).destination_id);
599 END IF;
600
601 --
602 -- destination exists put it in the search tab
603 --
604 IF l_debug_on THEN
605 WSH_DEBUG_SV.logmsg(l_module_name,'Add the OD pair to the search table');
606 END IF;
607
608 --
609 -- increment the index counter
610 --
611 l_ctr := l_ctr + 1;
612 l_search_tab(l_ctr).origin_id := l_loc_region_id_origin_tab(p_distance_input_tab(eee).origin_id);
613 l_search_tab(l_ctr).destination_id := l_loc_region_id_dest_tab(p_distance_input_tab(eee).destination_id);
614
615 l_search_tab(l_ctr).origin_loc_id := p_distance_input_tab(eee).origin_id;
616 l_search_tab(l_ctr).dest_loc_id := p_distance_input_tab(eee).destination_id;
617
618 --
619 -- populate the region to location origin and destination tables
620 -- to use in the distance search procdeure to detemine the mapping
621 -- for locations that are not found in the search
622 --
623 l_region_idx_loc_orig_tab(l_search_tab(l_ctr).origin_id) := p_distance_input_tab(eee).origin_id;
624 l_region_idx_loc_dest_tab(l_search_tab(l_ctr).destination_id) := p_distance_input_tab(eee).destination_id;
625 ELSE
626 --
627 -- The destination location has no region
628 -- log a message do not include it in the search
629 --
630 IF l_debug_on THEN
631 WSH_DEBUG_SV.logmsg(l_module_name,'The destination location has no region log a message do not include it in the search');
632 END IF;
633
634
635 IF (p_messaging_yn = 'Y') THEN
636 IF l_debug_on THEN
637 WSH_DEBUG_SV.logmsg(l_module_name,'logging to the message table NO_REGION_MAP_D');
638 END IF;
639
640 l_msg_ctr := msg_message_text_tab.COUNT + 1;
641 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_REGN_MAP_DEST_LOC');
642 FND_MESSAGE.SET_TOKEN('LOCATION_ID',to_char(p_distance_input_tab(eee).destination_id));
643 msg_message_text_tab(l_msg_ctr) := FND_MESSAGE.GET;
644 msg_message_type_tab(l_msg_ctr) := WSH_UTIL_CORE.G_RET_STS_WARNING;
645 msg_message_code_tab(l_msg_ctr) := 'NO_REGION_MAP_D';
646 msg_location_region_flag_tab(l_msg_ctr) := p_location_region_flag;
647 msg_level_tab(l_msg_ctr) := l_distance_profile;
648 msg_table_origin_id_tab(l_msg_ctr) := null;
649 msg_table_destination_id_tab(l_msg_ctr) := null;
650 msg_input_origin_id_tab(l_msg_ctr) := p_distance_input_tab(eee).origin_id;
651 msg_input_destination_tab(l_msg_ctr) := p_distance_input_tab(eee).destination_id;
652 END IF;
653 END IF;
654 ELSE
655 --
656 -- The origin location has no region
657 -- log a message - do not include it in the search
658 --
659 IF l_debug_on THEN
660 WSH_DEBUG_SV.logmsg(l_module_name,'The origin location has no region log a message do not include it in the search');
661 END IF;
662
663
664 IF (p_messaging_yn = 'Y') THEN
665 IF l_debug_on THEN
666 WSH_DEBUG_SV.logmsg(l_module_name,'logging to the message table NO_REGION_MAP_O');
667 END IF;
668
669 l_msg_ctr := msg_message_text_tab.COUNT + 1;
670 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_REGN_MAP_ORIG_LOC');
671 FND_MESSAGE.SET_TOKEN('LOCATION_ID',to_char(p_distance_input_tab(eee).origin_id));
672 msg_message_text_tab(l_msg_ctr) := FND_MESSAGE.GET;
673 msg_message_type_tab(l_msg_ctr) := WSH_UTIL_CORE.G_RET_STS_WARNING;
674 msg_message_code_tab(l_msg_ctr) := 'NO_REGION_MAP_O';
675 msg_location_region_flag_tab(l_msg_ctr) := p_location_region_flag;
676 msg_level_tab(l_msg_ctr) := l_distance_profile;
677 msg_table_origin_id_tab(l_msg_ctr) := null;
678 msg_table_destination_id_tab(l_msg_ctr) := null;
679 msg_input_origin_id_tab(l_msg_ctr) := p_distance_input_tab(eee).origin_id;
680 msg_input_destination_tab(l_msg_ctr) := p_distance_input_tab(eee).destination_id;
681 END IF;
682 END IF;
683 END LOOP;
684
685
686 IF ((p_messaging_yn = 'Y') AND
687 (msg_message_text_tab.COUNT > 0)) THEN
688 --
689 -- All messages have been logged in the temp tables now add the messages to
690 -- the global tables
691 --
692 IF l_debug_on THEN
693 WSH_DEBUG_SV.logmsg(l_module_name,'Messaging is on and nessages exist Log the messages in the global message table');
694 END IF;
695
696 --
697 -- Log the messages in the global message table
698 --
699 --
700 -- Debug Statements
701 --
702 IF l_debug_on THEN
703 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES',WSH_DEBUG_SV.C_PROC_LEVEL);
704 END IF;
705 --
706 FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES(p_message_type_tab => msg_message_type_tab,
707 p_message_code_tab => msg_message_code_tab,
708 p_message_text_tab => msg_message_text_tab,
709 p_location_region_flag_tab => msg_location_region_flag_tab,
710 p_level_tab => msg_level_tab,
711 p_table_origin_id_tab => msg_table_origin_id_tab,
712 p_table_destination_id_tab => msg_table_destination_id_tab,
713 p_input_origin_id_tab => msg_input_origin_id_tab,
714 p_input_destination_tab => msg_input_destination_tab,
715 x_return_status => l_return_status,
716 x_return_message => l_return_message);
717 IF l_debug_on THEN
718 WSH_DEBUG_SV.logmsg(l_module_name,'Back from calling FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES');
719 END IF;
720 END IF;
721
722
723
724 --
725 -- So now we have a search table of origin and destination regions
726 -- and two tables of locations and regions for origin and destination
727 -- indexed by region id, the search by flag and the messaging flag,
728 -- now we can call the search procedure
729 --
730 -- l_search_tab
731 -- l_region_idx_loc_orig_tab
732 -- l_region_idx_loc_dest_tab
733 -- p_location_region_flag
734 -- p_messaging_yn
735
736
737 ELSIF (p_location_region_flag = g_region_search_flag) THEN
738 --
739 -- The input is in region id form which means that we can go ahead
740 -- and search without converting
741 --
742 IF l_debug_on THEN
743 WSH_DEBUG_SV.logmsg(l_module_name,'Input IDs are in region form, no need to convert just add to the search table');
744 END IF;
745
746
747 --
748 -- Put the origin and destination into the search table
749 --
750 -- Reset the search table index counter
751 --
752 l_ctr := 0;
753 FOR fff IN p_distance_input_tab.FIRST..p_distance_input_tab.LAST LOOP
754 IF l_debug_on THEN
755 WSH_DEBUG_SV.logmsg(l_module_name,'Adding to the search table');
756 WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(fff).origin_id = ',p_distance_input_tab(fff).origin_id);
757 WSH_DEBUG_SV.log(l_module_name,'p_distance_input_tab(fff).destination_id = ',p_distance_input_tab(fff).destination_id);
758 END IF;
759
760
761 l_ctr := l_ctr + 1;
762 l_search_tab(l_ctr).origin_id := p_distance_input_tab(fff).origin_id;
763 l_search_tab(l_ctr).destination_id := p_distance_input_tab(fff).destination_id;
764
765 l_search_tab(l_ctr).origin_loc_id := null;
766 l_search_tab(l_ctr).dest_loc_id := null;
767
768 END LOOP;
769
770 --
771 -- So now we have a search table of regions now we can call the search
772 -- procedure
773 --
774 -- l_search_tab
775 -- p_location_region_flag
776 -- p_messaging_yn
777
778 END IF;
779
780
781 IF l_debug_on THEN
782 WSH_DEBUG_SV.logmsg(l_module_name,'Check the search table is populated');
783 END IF;
784
785
786 IF (l_search_tab.COUNT > 0) THEN
787 --
788 -- We have O/D pairs to search with
789 -- Call the Distance search procedure
790 --
791 IF l_debug_on THEN
792 WSH_DEBUG_SV.log(l_module_name,'records exist in search table l_search_tab.COUNT = ',l_search_tab.COUNT);
793 END IF;
794
795
796 --
797 -- reset the result table and result found flag
798 --
799 l_result_table.DELETE;
800 l_result_found_flag := 'N';
801
802 --
803 -- Debug Statements
804 --
805 IF l_debug_on THEN
806 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_DIST_INT_PKG.DISTANCE_SEARCH',WSH_DEBUG_SV.C_PROC_LEVEL);
807 WSH_DEBUG_SV.logmsg(l_module_name,'---------- INPUT PARAMETERS -------------');
808 WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
809 WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
810 WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', l_search_tab.COUNT);
811 IF (l_search_tab.COUNT > 0) THEN
812 FOR dbst IN l_search_tab.FIRST..l_search_tab.LAST LOOP
813 WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_id', l_search_tab(dbst).origin_id);
814 WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).destination_id', l_search_tab(dbst).destination_id);
815 WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).origin_loc_id',l_search_tab(dbst).origin_loc_id);
816 WSH_DEBUG_SV.log(l_module_name,'l_search_tab(dbst).dest_loc_id',l_search_tab(dbst).dest_loc_id);
817 END LOOP;
818 END IF;
819 WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab count = ', l_region_idx_loc_orig_tab.COUNT);
820 IF (l_region_idx_loc_orig_tab.COUNT > 0) THEN
821 FOR dbrilo IN l_region_idx_loc_orig_tab.FIRST..l_region_idx_loc_orig_tab.LAST LOOP
822 IF (l_region_idx_loc_orig_tab.EXISTS(dbrilo)) THEN
823 WSH_DEBUG_SV.log(l_module_name,'(dbrilo)',dbrilo);
824 WSH_DEBUG_SV.log(l_module_name,'l_region_idx_loc_orig_tab(dbrilo)',l_region_idx_loc_orig_tab(dbrilo));
825 END IF;
826 END LOOP;
827 END IF;
828 WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab count = ', l_region_idx_loc_dest_tab.COUNT);
829 IF (l_region_idx_loc_dest_tab.COUNT > 0) THEN
830 FOR dbrild IN l_region_idx_loc_dest_tab.FIRST..l_region_idx_loc_dest_tab.LAST LOOP
831 IF (l_region_idx_loc_dest_tab.EXISTS(dbrild)) THEN
832 WSH_DEBUG_SV.log(l_module_name,'(dbrild)',dbrild);
833 WSH_DEBUG_SV.log(l_module_name,'l_region_idx_loc_dest_tab(dbrild)',l_region_idx_loc_dest_tab(dbrild));
834 END IF;
835 END LOOP;
836 END IF;
837 END IF;
838
839
840 FTE_DIST_INT_PKG.DISTANCE_SEARCH(p_location_region_flag => p_location_region_flag,
841 p_messaging_yn => p_messaging_yn,
842 p_level => l_distance_profile,
843 p_search_tab => l_search_tab,
844 p_origin_reg_loc_tab => l_region_idx_loc_orig_tab,
845 p_dest_reg_loc_tab => l_region_idx_loc_dest_tab,
846 x_result_found => l_result_found_flag,
847 x_result_table => l_result_table,
848 x_return_message => l_return_message,
849 x_return_status => l_return_status);
850
851 IF l_debug_on THEN
852 WSH_DEBUG_SV.logmsg(l_module_name,'------- back from FTE_DIST_INT_PKG.DISTANCE_SEARCH -----');
853 WSH_DEBUG_SV.logmsg(l_module_name,'---------- OUTPUT PARAMETERS -------------');
854 WSH_DEBUG_SV.log(l_module_name,'x_result_found',l_result_found_flag);
855 WSH_DEBUG_SV.log(l_module_name,'x_result_table count = ',l_result_table.COUNT);
856 WSH_DEBUG_SV.log(l_module_name,'x_return_message',l_return_message);
857 WSH_DEBUG_SV.log(l_module_name,'x_return_status',l_return_status);
858 END IF;
859
860
861 IF ((l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR) OR
862 (l_return_status = WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
863 --
864 -- A serious error occurred performing the search
865 --
866 IF l_debug_on THEN
867 WSH_DEBUG_SV.logmsg(l_module_name,'A serious error occurred performing the search - RAISE FTE_DIST_DISTANCE_SEARCH_ERR');
868 END IF;
869
870
871 RAISE FTE_DIST_DISTANCE_SEARCH_ERR;
872
873 END IF;
874
875 ELSE
876 --
877 -- The search table is empty which means that there are no regions to search for
878 -- at all
879 -- Raise an error and return
880 --
881 IF l_debug_on THEN
882 WSH_DEBUG_SV.logmsg(l_module_name,'The search table is empty which means that there are no regions to search for');
883
884 END IF;
885
886
887 IF (p_location_region_flag = g_location_search_flag) THEN
888 --
889 -- No regions were found for any location ids - raise an error
890 --
891 IF l_debug_on THEN
892 WSH_DEBUG_SV.logmsg(l_module_name,'No regions were found for any location ids - raise an error FTE_DIST_NO_REGS_FOR_ANY_LOCS');
893 END IF;
894
895 RAISE FTE_DIST_NO_REGS_FOR_ANY_LOCS;
896
897 ELSIF (p_location_region_flag = g_region_search_flag) THEN
898 --
899 -- something went horribly wrong, none of the regions populated
900 -- in the search table!
901 --
902 IF l_debug_on THEN
903 WSH_DEBUG_SV.logmsg(l_module_name,'something went horribly wrong, none of the regions populatedin the search table - RAISE FTE_DIST_NO_REGS_IN_SEARCH');
904 END IF;
905
906 RAISE FTE_DIST_NO_REGS_IN_SEARCH;
907
908 END IF;
909
910 END IF;
911
912
913 IF (l_result_found_flag = 'Y') THEN
914 --
915 -- a result has been found return to the calling program
916 --
917 IF l_debug_on THEN
918 WSH_DEBUG_SV.logmsg(l_module_name,'The search executed successfully and at least 1 result was found');
919 IF (l_result_table.COUNT > 0) THEN
920 FOR sss in l_result_table.FIRST..l_result_table.LAST LOOP
921 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).location_region_flag = ',l_result_table(sss).location_region_flag);
922 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_location_id = ',l_result_table(sss).origin_location_id);
923 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_location_id = ',l_result_table(sss).destination_location_id);
924 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).origin_region_id = ',l_result_table(sss).origin_region_id);
925 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).destination_region_id = ',l_result_table(sss).destination_region_id);
926 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).type = ',l_result_table(sss).type);
927 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance = ',l_result_table(sss).distance);
928 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).distance_uom = ',l_result_table(sss).distance_uom);
929 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time = ',l_result_table(sss).transit_time);
930 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).transit_time_uom = ',l_result_table(sss).transit_time_uom);
931 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).status = ',l_result_table(sss).status);
932 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).error_msg = ',l_result_table(sss).error_msg);
933 WSH_DEBUG_SV.log(l_module_name,'l_result_table(sss).msg_id = ',l_result_table(sss).msg_id);
934 END LOOP;
935 END IF;
936 END IF;
937
938
939 x_distance_output_tab := l_result_table;
940 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
941 x_return_message := null;
942 x_distance_message_tab := g_message_tab;
943 g_message_tab.DELETE;
944 --
945 -- Debug Statements
946 --
947 IF l_debug_on THEN
948 WSH_DEBUG_SV.pop(l_module_name);
949 END IF;
950 --
951 RETURN;
952 ELSE
953 --
954 -- If here, no errors, but no result found - return a warning indicator
955 --
956 IF l_debug_on THEN
957 WSH_DEBUG_SV.logmsg(l_module_name,'The search executed successfully but no records were found - return with a warning status');
958 END IF;
959
960 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
961 x_return_message := WSH_UTIL_CORE.G_RET_STS_WARNING;
962 x_distance_message_tab := g_message_tab;
963 g_message_tab.DELETE;
964
965 --
966 -- Debug Statements
967 --
968 IF l_debug_on THEN
969 WSH_DEBUG_SV.pop(l_module_name);
970 END IF;
971 --
972 RETURN;
973 END IF;
974
975
976 --
977 -- Debug Statements
978 --
979 IF l_debug_on THEN
980 WSH_DEBUG_SV.pop(l_module_name);
981 END IF;
982 --
983
984
985 EXCEPTION
986 WHEN FTE_DIST_NO_INPUT_DATA THEN
987 --5067249
988 --FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_INPUT_DATA');
989 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
990 x_return_message := 'FTE_DIST_NO_INPUT_DATA';
991 --WSH_UTIL_CORE.add_message(x_return_status);
992
993 --
994 -- Close any open cursors
995 --
996 IF (c_get_regions_for_locs%ISOPEN) THEN
997 CLOSE c_get_regions_for_locs;
998 END IF;
999
1000 --
1001 -- Debug Statements
1002 --
1003 IF l_debug_on THEN
1004 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_INPUT_DATA RAISED');
1005 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_INPUT_DATA exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1006 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_INPUT_DATA');
1007 END IF;
1008
1009 WHEN FTE_DIST_INVALID_LOC_REG_FLAG THEN
1010 --5067249
1011 --FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INVALID_LOC_REG_FLAG');
1012 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1013 x_return_message := 'FTE_DIST_INVALID_LOC_REG_FLAG';
1014 --WSH_UTIL_CORE.add_message(x_return_status);
1015
1016 --
1017 -- Close any open cursors
1018 --
1019 IF (c_get_regions_for_locs%ISOPEN) THEN
1020 CLOSE c_get_regions_for_locs;
1021 END IF;
1022
1023
1024 --
1025 -- Debug Statements
1026 --
1027 IF l_debug_on THEN
1028 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_ACS_PKG.START_ACS FTE_DIST_INVALID_LOC_REG_FLAG RAISED');
1029 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_LOC_REG_FLAG exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1030 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_LOC_REG_FLAG');
1031 END IF;
1032
1033 WHEN FTE_DIST_DISTANCE_SEARCH_ERR THEN
1034 x_return_status := l_return_status;
1035 x_return_message := l_return_message;
1036
1037 --
1038 -- Close any open cursors
1039 --
1040 IF (c_get_regions_for_locs%ISOPEN) THEN
1041 CLOSE c_get_regions_for_locs;
1042 END IF;
1043
1044
1045 --
1046 -- Debug Statements
1047 --
1048 IF l_debug_on THEN
1049 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.GET_DISTANCE_TIME ( FTE_DIST_DISTANCE_SEARCH_ERR ) IS '||L_RETURN_STATUS||': '||L_RETURN_MESSAGE );
1050 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_DISTANCE_SEARCH_ERR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1051 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_DISTANCE_SEARCH_ERR');
1052 END IF;
1053 --
1054 WSH_UTIL_CORE.default_handler('FTE_DIST_INT.GET_DISTANCE_TIME');
1055
1056 WHEN FTE_DIST_INVALID_PROFILE THEN
1057 --5067249
1058 --FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INVALID_PROFILE');
1059 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1060 x_return_message := 'FTE_DIST_INVALID_PROFILE';
1061 --WSH_UTIL_CORE.add_message(x_return_status);
1062
1063 --
1064 -- Close any open cursors
1065 --
1066 IF (c_get_regions_for_locs%ISOPEN) THEN
1067 CLOSE c_get_regions_for_locs;
1068 END IF;
1069
1070
1071 --
1072 -- Debug Statements
1073 --
1074 IF l_debug_on THEN
1075 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_INVALID_PROFILE RAISED');
1076 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_PROFILE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1077 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_PROFILE');
1078 END IF;
1079
1080 WHEN FTE_DIST_NULL_PROFILE THEN
1081 --5067249
1082 --FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NULL_PROFILE');
1083 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1084 x_return_message := 'FTE_DIST_NULL_PROFILE';
1085 --WSH_UTIL_CORE.add_message(x_return_status);
1086
1087 --
1088 -- Close any open cursors
1089 --
1090 IF (c_get_regions_for_locs%ISOPEN) THEN
1091 CLOSE c_get_regions_for_locs;
1092 END IF;
1093
1094
1095 --
1096 -- Debug Statements
1097 --
1098 IF l_debug_on THEN
1099 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NULL_PROFILE RAISED');
1100 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_PROFILE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1101 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_PROFILE');
1102 END IF;
1103
1104 WHEN FTE_DIST_NO_REGS_FOR_ANY_LOCS THEN
1105 --5067249
1106 --FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_REGS_FOR_ANY_LOCS');
1107 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1108 x_return_message := 'FTE_DIST_NO_REGS_FOR_ANY_LOCS';
1109 --WSH_UTIL_CORE.add_message(x_return_status);
1110
1111 --
1112 -- Close any open cursors
1113 --
1114 IF (c_get_regions_for_locs%ISOPEN) THEN
1115 CLOSE c_get_regions_for_locs;
1116 END IF;
1117
1118
1119 --
1120 -- Debug Statements
1121 --
1122 IF l_debug_on THEN
1123 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_REGS_FOR_ANY_LOCS RAISED');
1124 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_REGS_FOR_ANY_LOCS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1125 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_REGS_FOR_ANY_LOCS');
1126 END IF;
1127
1128 WHEN FTE_DIST_NO_REGS_IN_SEARCH THEN
1129 --5067249
1130 --FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_REGS_IN_SEARCH');
1131 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1132 x_return_message := 'FTE_DIST_NO_REGS_IN_SEARCH';
1133 --WSH_UTIL_CORE.add_message(x_return_status);
1134
1135 --
1136 -- Close any open cursors
1137 --
1138 IF (c_get_regions_for_locs%ISOPEN) THEN
1139 CLOSE c_get_regions_for_locs;
1140 END IF;
1141
1142
1143 --
1144 -- Debug Statements
1145 --
1146 IF l_debug_on THEN
1147 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NO_REGS_IN_SEARCH RAISED');
1148 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_REGS_IN_SEARCH exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1149 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_REGS_IN_SEARCH');
1150 END IF;
1151
1152 WHEN FTE_DIST_NULL_REGION_TYPE THEN
1153 --5067249
1154 --FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NULL_REGION_TYPE');
1155 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1156 x_return_message := 'FTE_DIST_NULL_REGION_TYPE';
1157 --WSH_UTIL_CORE.add_message(x_return_status);
1158
1159 --
1160 -- Close any open cursors
1161 --
1162 IF (c_get_regions_for_locs%ISOPEN) THEN
1163 CLOSE c_get_regions_for_locs;
1164 END IF;
1165
1166
1167 --
1168 -- Debug Statements
1169 --
1170 IF l_debug_on THEN
1171 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NULL_REGION_TYPE RAISED');
1172 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_REGION_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1173 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_REGION_TYPE');
1174 END IF;
1175
1176 WHEN OTHERS THEN
1177 l_error_text := SQLERRM;
1178
1179 --
1180 -- Close any open cursors
1181 --
1182 IF (c_get_regions_for_locs%ISOPEN) THEN
1183 CLOSE c_get_regions_for_locs;
1184 END IF;
1185
1186 --
1187 -- Debug Statements
1188 --
1189 IF l_debug_on THEN
1190 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.GET_DISTANCE_TIME IS ' ||L_ERROR_TEXT );
1191 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1192 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1193 END IF;
1194 --
1195 WSH_UTIL_CORE.default_handler('FTE_DIST_INT_PKG.GET_DISTANCE_TIME');
1196 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1197 x_return_message := l_error_text;
1198
1199 END GET_DISTANCE_TIME;
1200
1201
1202
1203
1204 -- -------------------------------------------------------------------------- --
1205 -- --
1206 -- NAME: DISTANCE_SEARCH --
1207 -- --
1208 -- TYPE: PROCEDURE --
1209 -- --
1210 -- PARAMETERS (IN OUT): p_search_tab IN OUT NOCOPY --
1211 -- FTE_DIST_INT.fte_dist_search_table --
1212 -- p_messaging_yn IN VARCHAR2 --
1213 -- --
1214 -- PARAMETERS (OUT): x_result_found OUT VARCHAR2 --
1215 -- x_result_table OUT NOCOPY --
1216 -- FTE_DIST_INT_PKG.fte_dist_output_tab --
1217 -- x_return_message OUT VARCHAR2, --
1218 -- x_return_status OUT VARCHAR2 --
1219 -- --
1220 -- PARAMETERS (IN OUT): none --
1221 -- --
1222 -- RETURN: none --
1223 -- --
1224 -- DESCRIPTION: This procedure performs the distance and transit time --
1225 -- search for each set of OD pairs passed in in the --
1226 -- search table. It returns any found results in the --
1227 -- result table output parameter. If no results are --
1228 -- found at all a warning is returned. If any OD pairs --
1229 -- are not found they will be added to the message table --
1230 -- as no recods found --
1231 -- --
1232 -- CHANGE CONTROL LOG --
1233 -- ------------------ --
1234 -- --
1235 -- DATE VERSION BY BUG DESCRIPTION --
1236 -- ---------- ------- -------- ------- ---------------------------------- --
1237 -- 2003/07/14 J ABLUNDEL Created --
1238 -- --
1239 -- -------------------------------------------------------------------------- --
1240 PROCEDURE DISTANCE_SEARCH(p_location_region_flag IN VARCHAR2,
1241 p_messaging_yn IN VARCHAR2,
1242 p_level IN VARCHAR2,
1243 p_search_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_search_tab,
1244 p_origin_reg_loc_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_num_table,
1245 p_dest_reg_loc_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_num_table,
1246 x_result_found OUT NOCOPY VARCHAR2,
1247 x_result_table OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_output_tab,
1248 x_return_message OUT NOCOPY VARCHAR2,
1249 x_return_status OUT NOCOPY VARCHAR2) IS
1250
1251
1252 --
1253 -- Local Variable definitions
1254 --
1255 l_msg_ctr PLS_INTEGER; -- counter for populating message table index
1256 l_ctr PLS_INTEGER; -- counter for populating result table index
1257 l_return_message VARCHAR2(2000); -- Return message from API (if error in API)
1258 l_return_status VARCHAR2(1); -- Return Status from called API (values = S,E,W,U)
1259 l_error_text VARCHAR2(2000); -- holds th unexpected error message text
1260 l_msg_no_rcds_fnd VARCHAR2(2000); -- holds the message text of no records found msg
1261
1262 --
1263 -- Local records/tables
1264 --
1265 l_rslt_origin_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
1266 l_rslt_destination_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
1267 l_rslt_identifier_type_tab FTE_DIST_INT_PKG.fte_dist_tmp_code_table;
1268 l_rslt_distance_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
1269 l_rslt_distance_uom_tab FTE_DIST_INT_PKG.fte_dist_tmp_uom_table;
1270 l_rslt_transit_time_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
1271 l_rslt_transit_time_uom_tab FTE_DIST_INT_PKG.fte_dist_tmp_uom_table;
1272
1273 l_result_table FTE_DIST_INT_PKG.fte_dist_output_tab;
1274
1275
1276 --
1277 -- Message logging tables
1278 --
1279 msg_message_type_tab FTE_DIST_INT_PKG.fte_dist_tmp_flag_table;
1280 msg_message_code_tab FTE_DIST_INT_PKG.fte_dist_tmp_code_table;
1281 msg_message_text_tab FTE_DIST_INT_PKG.fte_dist_tmp_msg_table;
1282 msg_location_region_flag_tab FTE_DIST_INT_PKG.fte_dist_tmp_flag_table;
1283 msg_level_tab FTE_DIST_INT_PKG.fte_dist_tmp_code_table;
1284 msg_table_origin_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
1285 msg_table_destination_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
1286 msg_input_origin_id_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
1287 msg_input_destination_tab FTE_DIST_INT_PKG.fte_dist_tmp_num_table;
1288
1289
1290
1291 --
1292 -- Cursor Definitions
1293 --
1294 -- -----------------------------------------------------------------
1295 -- Distance and transit time search query
1296 -- -----------------------------------------------------------------
1297 --
1298 cursor c_perform_distance_search(cp_origin_id NUMBER,
1299 cp_destination_id NUMBER,
1300 cp_identifier_type VARCHAR2) IS
1301 select flm.origin_id,
1302 flm.destination_id,
1303 flm.identifier_type,
1304 flm.distance,
1305 flm.distance_uom,
1306 flm.transit_time,
1307 flm.transit_time_uom
1308 from fte_location_mileages flm
1309 where flm.origin_id = cp_origin_id
1310 and flm.destination_id = cp_destination_id
1311 and flm.identifier_type = cp_identifier_type;
1312
1313
1314
1315 --
1316 -- Debug Local Variables
1317 --
1318 l_debug_on BOOLEAN;
1319 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DISTANCE_SEARCH';
1320
1321
1322 BEGIN
1323
1324 --
1325 -- Set the procedure debug stuff
1326 --
1327 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1328 --
1329 IF l_debug_on IS NULL THEN
1330 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1331 END IF;
1332
1333 --
1334 -- Debug Statements for input parameters
1335 --
1336 IF l_debug_on THEN
1337 WSH_DEBUG_SV.push(l_module_name);
1338 WSH_DEBUG_SV.logmsg(l_module_name,'------- DISTANCE SEARCH INPUT PARAMETERS ------');
1339 WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------------');
1340 WSH_DEBUG_SV.log(l_module_name,'p_location_region_flag',p_location_region_flag);
1341 WSH_DEBUG_SV.log(l_module_name,'p_messaging_yn',p_messaging_yn);
1342 WSH_DEBUG_SV.log(l_module_name,'p_search_tab count = ', p_search_tab.COUNT);
1343 IF (p_search_tab.COUNT > 0) THEN
1344 FOR dbst IN p_search_tab.FIRST..p_search_tab.LAST LOOP
1345 WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_id', p_search_tab(dbst).origin_id);
1346 WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).destination_id', p_search_tab(dbst).destination_id);
1347 WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).origin_loc_id',p_search_tab(dbst).origin_loc_id);
1348 WSH_DEBUG_SV.log(l_module_name,'p_search_tab(dbst).dest_loc_id',p_search_tab(dbst).dest_loc_id);
1349 END LOOP;
1350 END IF;
1351 WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab count = ', p_origin_reg_loc_tab.COUNT);
1352 IF (p_origin_reg_loc_tab.COUNT > 0) THEN
1353 FOR dbrilo IN p_origin_reg_loc_tab.FIRST..p_origin_reg_loc_tab.LAST LOOP
1354 IF (p_origin_reg_loc_tab.EXISTS(dbrilo)) THEN
1355 WSH_DEBUG_SV.log(l_module_name,'(dbrilo)',dbrilo);
1356 WSH_DEBUG_SV.log(l_module_name,'p_origin_reg_loc_tab(dbrilo)',p_origin_reg_loc_tab(dbrilo));
1357 END IF;
1358 END LOOP;
1359 END IF;
1360 WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab count = ', p_dest_reg_loc_tab.COUNT);
1361 IF (p_dest_reg_loc_tab.COUNT > 0) THEN
1362 FOR dbrild IN p_dest_reg_loc_tab.FIRST..p_dest_reg_loc_tab.LAST LOOP
1363 IF (p_dest_reg_loc_tab.EXISTS(dbrild)) THEN
1364 WSH_DEBUG_SV.log(l_module_name,'(dbrild)',dbrild);
1365 WSH_DEBUG_SV.log(l_module_name,'p_dest_reg_loc_tab(dbrild)',p_dest_reg_loc_tab(dbrild));
1366 END IF;
1367 END LOOP;
1368 END IF;
1369 END IF;
1370
1371
1372 --
1373 -- Set the return parameters for the start of the procedure
1374 --
1375 x_return_message := null;
1376 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1377
1378
1379 l_ctr := 0;
1380 --
1381 -- we are going to loop through each OD pair and try and find
1382 -- a matching distance record
1383 --
1384 --
1385 -- Clean out the local tables
1386 --
1387 l_rslt_origin_id_tab.DELETE;
1388 l_rslt_destination_id_tab.DELETE;
1389 l_rslt_identifier_type_tab.DELETE;
1390 l_rslt_distance_tab.DELETE;
1391 l_rslt_distance_uom_tab.DELETE;
1392 l_rslt_transit_time_tab.DELETE;
1393 l_rslt_transit_time_uom_tab.DELETE;
1394 l_result_table.DELETE;
1395
1396
1397 --
1398 -- Reset the message tables
1399 --
1400 msg_message_type_tab.DELETE;
1401 msg_message_code_tab.DELETE;
1402 msg_message_text_tab.DELETE;
1403 msg_location_region_flag_tab.DELETE;
1404 msg_level_tab.DELETE;
1405 msg_table_origin_id_tab.DELETE;
1406 msg_table_destination_id_tab.DELETE;
1407 msg_input_origin_id_tab.DELETE;
1408 msg_input_destination_tab.DELETE;
1409
1410
1411 IF (p_messaging_yn = 'Y') THEN
1412 --
1413 -- Set the no records found error message in case we need to log it
1414 --
1415 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_RECORDS_FOUND');
1416 l_msg_no_rcds_fnd := FND_MESSAGE.GET;
1417 END IF;
1418
1419
1420
1421 FOR ggg IN p_search_tab.FIRST..p_search_tab.LAST LOOP
1422 --
1423 -- Perform the search to find matching rules
1424 --
1425 IF l_debug_on THEN
1426 WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1427 WSH_DEBUG_SV.logmsg(l_module_name,'RUNNING cursor c_perform_distance_search:');
1428 WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).origin_id',p_search_tab(ggg).origin_id);
1429 WSH_DEBUG_SV.log(l_module_name,'p_search_tab(ggg).destination_id',p_search_tab(ggg).destination_id);
1430 WSH_DEBUG_SV.log(l_module_name,'p_level',p_level);
1431 WSH_DEBUG_SV.logmsg(l_module_name,'-----------------------------------------');
1432 END IF;
1433
1434 OPEN c_perform_distance_search(p_search_tab(ggg).origin_id,
1435 p_search_tab(ggg).destination_id,
1436 p_level);
1437 FETCH c_perform_distance_search BULK COLLECT INTO
1438 l_rslt_origin_id_tab,
1439 l_rslt_destination_id_tab,
1440 l_rslt_identifier_type_tab,
1441 l_rslt_distance_tab,
1442 l_rslt_distance_uom_tab,
1443 l_rslt_transit_time_tab,
1444 l_rslt_transit_time_uom_tab;
1445 CLOSE c_perform_distance_search;
1446
1447
1448
1449
1450 --
1451 -- Search completed for OD pair see if theres any
1452 -- results found and load up the output table with the data
1453 -- if so
1454 --
1455 IF l_debug_on THEN
1456 WSH_DEBUG_SV.log(l_module_name,'l_rslt_origin_id_tab.COUNT = ',l_rslt_origin_id_tab.COUNT);
1457 END IF;
1458
1459 IF (l_rslt_origin_id_tab.COUNT > 0) THEN
1460 --
1461 -- Results exist - set the result found flag
1462 --
1463 x_result_found := 'Y';
1464
1465 --
1466 -- Loop through the results and populate the result table
1467 --
1468 FOR hhh in l_rslt_origin_id_tab.FIRST..l_rslt_origin_id_tab.LAST LOOP
1469 --
1470 -- increment the index counter for the result table
1471 --
1472 l_ctr := l_ctr + 1;
1473
1474 l_result_table(l_ctr).location_region_flag := p_location_region_flag;
1475
1476 IF (p_location_region_flag = g_location_search_flag) THEN
1477 --
1478 -- The search was for locations - get the corresponding location for the
1479 -- region found
1480 --
1481 IF l_debug_on THEN
1482 WSH_DEBUG_SV.logmsg(l_module_name,'The search was for locations - get the corresponding location for the region found');
1483 END IF;
1484
1485 -- AXE
1486 -- l_result_table(l_ctr).origin_location_id := p_origin_reg_loc_tab(l_rslt_origin_id_tab(hhh));
1487 -- l_result_table(l_ctr).destination_location_id := p_dest_reg_loc_tab(l_rslt_destination_id_tab(hhh));
1488
1489 l_result_table(l_ctr).origin_location_id := p_search_tab(ggg).origin_loc_id;
1490 l_result_table(l_ctr).destination_location_id := p_search_tab(ggg).dest_loc_id;
1491
1492
1493 IF l_debug_on THEN
1494 WSH_DEBUG_SV.log(l_module_name,'l_result_table(l_ctr).origin_location_id = ',p_origin_reg_loc_tab(l_rslt_origin_id_tab(hhh)));
1495 WSH_DEBUG_SV.log(l_module_name,'l_result_table(l_ctr).destination_location_id = ',p_dest_reg_loc_tab(l_rslt_destination_id_tab(hhh)));
1496 END IF;
1497
1498 ELSIF (p_location_region_flag = g_region_search_flag) THEN
1499 --
1500 -- The search was for Regions - populate the result table
1501 --
1502 IF l_debug_on THEN
1503 WSH_DEBUG_SV.logmsg(l_module_name,'The search was for Regions - populate the result table');
1504 END IF;
1505
1506 l_result_table(l_ctr).origin_location_id := null;
1507 l_result_table(l_ctr).destination_location_id := null;
1508 END IF;
1509
1510 l_result_table(l_ctr).origin_region_id := l_rslt_origin_id_tab(hhh);
1511 l_result_table(l_ctr).destination_region_id := l_rslt_destination_id_tab(hhh);
1512 l_result_table(l_ctr).type := p_level;
1513 l_result_table(l_ctr).distance := l_rslt_distance_tab(hhh);
1514 l_result_table(l_ctr).distance_uom := l_rslt_distance_uom_tab(hhh);
1515 l_result_table(l_ctr).transit_time := l_rslt_transit_time_tab(hhh);
1516 l_result_table(l_ctr).transit_time_uom := l_rslt_transit_time_uom_tab(hhh);
1517 l_result_table(l_ctr).status := null;
1518 l_result_table(l_ctr).error_msg := null;
1519 l_result_table(l_ctr).msg_id := null;
1520 END LOOP;
1521 ELSE
1522 --
1523 -- Nothing was found - log a message
1524 --
1525 IF l_debug_on THEN
1526 WSH_DEBUG_SV.logmsg(l_module_name,'No result was found for the OD pair');
1527 END IF;
1528
1529 IF (p_messaging_yn = 'Y') THEN
1530 IF l_debug_on THEN
1531 WSH_DEBUG_SV.logmsg(l_module_name,'No result was found for the OD pair - log a NO_RECORDS_FOUND log message');
1532 END IF;
1533
1534 l_msg_ctr := msg_message_text_tab.COUNT + 1;
1535 msg_message_text_tab(l_msg_ctr) := l_msg_no_rcds_fnd;
1536 msg_message_type_tab(l_msg_ctr) := WSH_UTIL_CORE.G_RET_STS_WARNING;
1537 msg_message_code_tab(l_msg_ctr) := 'NO_RECORDS_FOUND';
1538 msg_location_region_flag_tab(l_msg_ctr) := p_location_region_flag;
1539 msg_level_tab(l_msg_ctr) := p_level;
1540 msg_table_origin_id_tab(l_msg_ctr) := null;
1541 msg_table_destination_id_tab(l_msg_ctr) := null;
1542 IF (p_location_region_flag = g_location_search_flag) THEN
1543 msg_input_origin_id_tab(l_msg_ctr) := p_origin_reg_loc_tab(p_search_tab(ggg).origin_id);
1544 msg_input_destination_tab(l_msg_ctr) := p_dest_reg_loc_tab(p_search_tab(ggg).destination_id);
1545 ELSIF (p_location_region_flag = g_region_search_flag) THEN
1546 msg_input_origin_id_tab(l_msg_ctr) := p_search_tab(ggg).origin_id;
1547 msg_input_destination_tab(l_msg_ctr) := p_search_tab(ggg).destination_id;
1548 END IF;
1549
1550 END IF;
1551
1552
1553
1554 --
1555 -- set the return status to a warning
1556 --
1557 x_return_message := null;
1558 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1559 --
1560 -- Debug Statements
1561 --
1562 -- IF l_debug_on THEN
1563 -- WSH_DEBUG_SV.pop(l_module_name);
1564 -- END IF;
1565 --
1566 END IF;
1567
1568 END LOOP; -- search loop
1569
1570
1571 IF l_debug_on THEN
1572 WSH_DEBUG_SV.logmsg(l_module_name,'Search is completed');
1573 END IF;
1574
1575
1576 --
1577 -- So we are here, this means that we either have a result or we do not!
1578 -- aahh, who cares.. just return back
1579 --
1580 IF (x_result_found = 'Y') THEN
1581 --
1582 -- We hava winner!!!!
1583 --
1584 IF l_debug_on THEN
1585 WSH_DEBUG_SV.logmsg(l_module_name,'We have a winner');
1586 END IF;
1587 x_result_table := l_result_table;
1588 x_return_message := null;
1589 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1590 --
1591 -- Debug Statements
1592 --
1593 IF l_debug_on THEN
1594 WSH_DEBUG_SV.pop(l_module_name);
1595 END IF;
1596 --
1597
1598 IF ((p_messaging_yn = 'Y') AND
1599 (msg_message_type_tab.COUNT > 0)) THEN
1600 --
1601 -- There are messages write them to the log
1602 --
1603 FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES(p_message_type_tab => msg_message_type_tab,
1604 p_message_code_tab => msg_message_code_tab,
1605 p_message_text_tab => msg_message_text_tab,
1606 p_location_region_flag_tab => msg_location_region_flag_tab,
1607 p_level_tab => msg_level_tab,
1608 p_table_origin_id_tab => msg_table_origin_id_tab,
1609 p_table_destination_id_tab => msg_table_destination_id_tab,
1610 p_input_origin_id_tab => msg_input_origin_id_tab,
1611 p_input_destination_tab => msg_input_destination_tab,
1612 x_return_status => l_return_status,
1613 x_return_message => l_return_message);
1614 END IF;
1615
1616 RETURN;
1617 ELSE
1618 IF l_debug_on THEN
1619 WSH_DEBUG_SV.logmsg(l_module_name,'no result just return a warning');
1620 END IF;
1621
1622 IF (p_messaging_yn = 'Y') THEN
1623 --
1624 -- no result just return a warning
1625 --
1626 l_msg_ctr := msg_message_text_tab.COUNT + 1;
1627 msg_message_text_tab(l_msg_ctr) := l_msg_no_rcds_fnd;
1628 msg_message_type_tab(l_msg_ctr) := WSH_UTIL_CORE.G_RET_STS_WARNING;
1629 msg_message_code_tab(l_msg_ctr) := 'NO_RECORDS_FOUND';
1630 msg_location_region_flag_tab(l_msg_ctr) := p_location_region_flag;
1631 msg_level_tab(l_msg_ctr) := p_level;
1632 msg_table_origin_id_tab(l_msg_ctr) := null;
1633 msg_table_destination_id_tab(l_msg_ctr) := null;
1634 msg_input_origin_id_tab(l_msg_ctr) := null;
1635 msg_input_destination_tab(l_msg_ctr) := null;
1636
1637
1638 FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES(p_message_type_tab => msg_message_type_tab,
1639 p_message_code_tab => msg_message_code_tab,
1640 p_message_text_tab => msg_message_text_tab,
1641 p_location_region_flag_tab => msg_location_region_flag_tab,
1642 p_level_tab => msg_level_tab,
1643 p_table_origin_id_tab => msg_table_origin_id_tab,
1644 p_table_destination_id_tab => msg_table_destination_id_tab,
1645 p_input_origin_id_tab => msg_input_origin_id_tab,
1646 p_input_destination_tab => msg_input_destination_tab,
1647 x_return_status => l_return_status,
1648 x_return_message => l_return_message);
1649
1650 END IF;
1651
1652 x_result_found := 'N';
1653 x_return_message := null;
1654 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1655 --
1656 -- Debug Statements
1657 --
1658 IF l_debug_on THEN
1659 WSH_DEBUG_SV.pop(l_module_name);
1660 END IF;
1661 --
1662 RETURN;
1663 END IF;
1664
1665
1666 --
1667 -- Debug Statements
1668 --
1669 IF l_debug_on THEN
1670 WSH_DEBUG_SV.pop(l_module_name);
1671 END IF;
1672 --
1673
1674
1675
1676 EXCEPTION
1677 WHEN OTHERS THEN
1678 l_error_text := SQLERRM;
1679
1680 --
1681 -- Close any open cursors
1682 --
1683 IF (c_perform_distance_search%ISOPEN) THEN
1684 CLOSE c_perform_distance_search;
1685 END IF;
1686
1687 --
1688 -- Debug Statements
1689 --
1690 IF l_debug_on THEN
1691 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.DISTANCE_SEARCH IS ' ||L_ERROR_TEXT );
1692 END IF;
1693 --
1694 WSH_UTIL_CORE.default_handler('FTE_DIST_INT_PKG.DISTANCE_SEARCH');
1695 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1696 x_return_message := ('FTE_DIST_INT_PKG.DISTANCE_SEARCH '||l_error_text);
1697 --
1698 -- Debug Statements
1699 --
1700 IF l_debug_on THEN
1701 WSH_DEBUG_SV.pop(l_module_name);
1702 END IF;
1703 --
1704 RETURN;
1705
1706
1707 --
1708 -- Debug Statements
1709 --
1710 IF l_debug_on THEN
1711 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1712 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1713 END IF;
1714 --
1715
1716 END DISTANCE_SEARCH;
1717
1718
1719
1720
1721
1722 -- -------------------------------------------------------------------------- --
1723 -- --
1724 -- NAME: LOG_DISTANCE_MESSAGES --
1725 -- --
1726 -- TYPE: PROCEDURE --
1727 -- --
1728 -- PARAMETERS (IN OUT): p_message_type_tab IN OUT NOCOPY --
1729 -- FTE_DIST_INT_PKG.fte_dist_tmp_flag_table --
1730 -- p_message_code_tab IN OUT NOCOPY --
1731 -- FTE_DIST_INT_PKG.fte_dist_tmp_code_table --
1732 -- p_message_text_tab IN OUT NOCOPY --
1733 -- FTE_DIST_INT_PKG.fte_dist_tmp_msg_table --
1734 -- p_location_region_flag_tab IN OUT NOCOPY --
1735 -- FTE_DIST_INT_PKG.fte_dist_tmp_flag_table --
1736 -- p_level_tab IN OUT NOCOPY --
1737 -- FTE_DIST_INT_PKG.fte_dist_tmp_code_table --
1738 -- p_table_origin_id_tab IN OUT NOCOPY --
1739 -- FTE_DIST_INT_PKG.fte_dist_tmp_num_table --
1740 -- p_table_destination_id_tab IN OUT NOCOPY --
1741 -- FTE_DIST_INT_PKG.fte_dist_tmp_num_table --
1742 -- p_input_origin_id_tab IN OUT NOCOPY --
1743 -- FTE_DIST_INT_PKG.fte_dist_tmp_num_table --
1744 -- p_input_destination_tab IN OUT NOCOPY --
1745 -- FTE_DIST_INT_PKG.fte_dist_tmp_num_table --
1746 -- --
1747 -- PARAMETERS (OUT): x_return_status OUT NOCOPY VARCHAR2 --
1748 -- x_return_message OUT NOCOPY VARCHAR2 --
1749 -- --
1750 -- RETURN: n/a --
1751 -- --
1752 -- DESCRIPTION: This procedure takes in tables of messages and rule/ --
1753 -- result information and adds them to the global --
1754 -- message table which is returned to the calling API --
1755 -- at the end of the distance search Engine execution. --
1756 -- --
1757 -- CHANGE CONTROL LOG --
1758 -- ------------------ --
1759 -- --
1760 -- DATE VERSION BY BUG DESCRIPTION --
1761 -- ---------- ------- -------- ------- ---------------------------------- --
1762 -- 2003/07/14 J ABLUNDEL Created --
1763 -- --
1764 -- -------------------------------------------------------------------------- --
1765 PROCEDURE LOG_DISTANCE_MESSAGES(p_message_type_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_flag_table,
1766 p_message_code_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_code_table,
1767 p_message_text_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_msg_table,
1768 p_location_region_flag_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_flag_table,
1769 p_level_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_code_table,
1770 p_table_origin_id_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_num_table,
1771 p_table_destination_id_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_num_table,
1772 p_input_origin_id_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_num_table,
1773 p_input_destination_tab IN OUT NOCOPY FTE_DIST_INT_PKG.fte_dist_tmp_num_table,
1774 x_return_status OUT NOCOPY VARCHAR2,
1775 x_return_message OUT NOCOPY VARCHAR2) IS
1776
1777
1778
1779
1780 l_error_text VARCHAR2(2000);
1781 l_cs_message VARCHAR2(2000);
1782 l_rec_count PLS_INTEGER;
1783
1784 --
1785 l_debug_on BOOLEAN;
1786 --
1787 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOG_DISTANCE_MESSAGES';
1788 --
1789 BEGIN
1790
1791 --
1792 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1793 --
1794 IF l_debug_on IS NULL
1795 THEN
1796 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1797 END IF;
1798 --
1799
1800 -- Bug 4996745
1801 IF l_debug_on THEN
1802 WSH_DEBUG_SV.push(l_module_name);
1803 END IF;
1804
1805 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1806 x_return_message := null;
1807
1808
1809 FOR abcd IN p_message_type_tab.FIRST..p_message_type_tab.LAST LOOP
1810
1811 l_rec_count := g_message_tab.count + 1;
1812
1813 g_message_tab(l_rec_count).sequence_number := l_rec_count;
1814 g_message_tab(l_rec_count).message_type := p_message_type_tab(abcd);
1815
1816 IF (p_message_code_tab.EXISTS(abcd)) THEN
1817 g_message_tab(l_rec_count).message_code := p_message_code_tab(abcd);
1818 ELSE
1819 g_message_tab(l_rec_count).message_code := null;
1820 END IF;
1821
1822 IF (p_message_text_tab.EXISTS(abcd)) THEN
1823 g_message_tab(l_rec_count).message_text := p_message_text_tab(abcd);
1824 ELSE
1825 g_message_tab(l_rec_count).message_text := null;
1826 END IF;
1827
1828 IF (p_location_region_flag_tab.EXISTS(abcd)) THEN
1829 g_message_tab(l_rec_count).location_region_flag := p_location_region_flag_tab(abcd);
1830 ELSE
1831 g_message_tab(l_rec_count).location_region_flag := null;
1832 END IF;
1833
1834 IF (p_level_tab.EXISTS(abcd)) THEN
1835 g_message_tab(l_rec_count).level := p_level_tab(abcd);
1836 ELSE
1837 g_message_tab(l_rec_count).level := null;
1838 END IF;
1839
1840 IF (p_table_origin_id_tab.EXISTS(abcd)) THEN
1841 g_message_tab(l_rec_count).table_origin_id := p_table_origin_id_tab(abcd);
1842 ELSE
1843 g_message_tab(l_rec_count).table_origin_id := null;
1844 END IF;
1845
1846 IF (p_table_destination_id_tab.EXISTS(abcd)) THEN
1847 g_message_tab(l_rec_count).table_destination_id := p_table_destination_id_tab(abcd);
1848 ELSE
1849 g_message_tab(l_rec_count).table_destination_id := null;
1850 END IF;
1851
1852 IF (p_input_origin_id_tab.EXISTS(abcd)) THEN
1853 g_message_tab(l_rec_count).input_origin_id := p_input_origin_id_tab(abcd);
1854 ELSE
1855 g_message_tab(l_rec_count).input_origin_id := null;
1856 END IF;
1857
1858 IF (p_input_destination_tab.EXISTS(abcd)) THEN
1859 g_message_tab(l_rec_count).input_destination_id := p_input_destination_tab(abcd);
1860 ELSE
1861 g_message_tab(l_rec_count).input_destination_id := null;
1862 END IF;
1863
1864 END LOOP;
1865
1866 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1867 x_return_message := null;
1868 --
1869 -- Debug Statements
1870 --
1871 IF l_debug_on THEN
1872 WSH_DEBUG_SV.pop(l_module_name);
1873 END IF;
1874 --
1875 RETURN;
1876
1877 EXCEPTION
1878 WHEN OTHERS THEN
1879 l_error_text := SQLERRM;
1880
1881 --
1882 -- Debug Statements
1883 --
1884 IF l_debug_on THEN
1885 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES IS ' ||L_ERROR_TEXT );
1886 END IF;
1887 --
1888 WSH_UTIL_CORE.default_handler('FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES');
1889 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1890 x_return_message := ('FTE_DIST_INT_PKG.LOG_DISTANCE_MESSAGES '||l_error_text);
1891 --
1892 -- Debug Statements
1893 --
1894 IF l_debug_on THEN
1895 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1896 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1897 END IF;
1898
1899 RETURN;
1900
1901 END LOG_DISTANCE_MESSAGES;
1902
1903
1904 END FTE_DIST_INT_PKG;
[Home] [Help]