1 PACKAGE BODY FTE_DIST_DWNLD_PKG AS
2 /* $Header: FTEDISDB.pls 115.11 2004/03/18 20:20:07 ablundel noship $ */
3 -- -------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_DIST_DWNLD_PKG --
6 -- TYPE: PACKAGE BODY --
7 -- DESCRIPTION: Contains core procedures for creating an OD download file --
8 -- --
9 -- --
10 -- CHANGE CONTROL LOG --
11 -- --
12 -- DATE VERSION BY BUG DESCRIPTION --
13 -- ---------- ------- -------- ------- ---------------------------------- --
14 -- 2003/07/17 J ABLUNDEL Created. --
15 -- --
16 -- 2003/12/17 J ABLUNDEL 3325486 PROCEDURE: CREATE_DWNLD_FILE --
17 -- Commented out the code that creates--
18 -- the spaces in the line for return --
19 -- distance and return time. Now the --
20 -- download file only contains the --
21 -- origin and destination columns --
22 -- --
23 -- 2003/12/19 J ABLUNDEL 3330390 GLOBAL PACKAGE VARIABLES --
24 -- Changed the value for --
25 -- g_default_file_ext to be lowercase --
26 -- 'in' instead of 'IN' as the linux --
27 -- version of batchpro only works --
28 -- with a lowercase file extension --
29 -- --
30 -- 2004/03/05 J ABLUNDEL 3487060 PROCEDURE: CREATE_DWNLD_FILE --
31 -- Need to check that region values --
32 -- exist for translated values for --
33 -- the return from c_get_region_values--
34 -- from WSH_REGIONS_V (changed to the --
35 -- view, VL) from the TL table --
36 -- --
37 -- -------------------------------------------------------------------------- --
38
39 -- -------------------------------------------------------------------------- --
40 -- Global Package Variables --
41 -- ------------------------ --
42 -- --
43 -- -------------------------------------------------------------------------- --
44 g_file_prefix CONSTANT VARCHAR2(3) := 'DLF';
45
46 -- [ABLUNDEL][12/19/2003][BUG# 3330390]
47 -- Change default file extension to be lowercase - in
48 --
49 -- g_default_file_ext CONSTANT VARCHAR2(2) := 'IN';
50 --
51 g_default_file_ext CONSTANT VARCHAR2(2) := 'in';
52
53
54 g_filename_length CONSTANT NUMBER := 8;
55 g_file_ext_length CONSTANT NUMBER := 3;
56 g_y_flag CONSTANT VARCHAR2(1) := 'Y';
57 g_n_flag CONSTANT VARCHAR2(1) := 'N';
58 g_ret_dist_col_name CONSTANT VARCHAR2(30) := 'RETURNDIST';
59 g_ret_time_col_name CONSTANT VARCHAR2(30) := 'RETURNTIME';
60 g_origin_col_name CONSTANT VARCHAR2(30) := 'ORIGIN';
61 g_dest_col_name CONSTANT VARCHAR2(30) := 'DESTINATION';
62 g_postal_code_name CONSTANT VARCHAR2(30) := 'POSTAL_CODE';
63 g_city_code_name CONSTANT VARCHAR2(30) := 'CITY';
64 g_state_code_name CONSTANT VARCHAR2(30) := 'STATE';
65 g_county_code_name CONSTANT VARCHAR2(30) := 'COUNTY';
66 g_country_code_name CONSTANT VARCHAR2(30) := 'COUNTRY';
67
68
69 --
70 -- For debug
71 --
72 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_DIST_INT_PKG';
73
74
75 -- -------------------------------------------------------------------------- --
76 -- --
77 -- PRIVATE PROCEDURE DEFINITIONS --
78 -- ----------------------------- --
79 -- Described in Procedure code below --
80 -- -------------------------------------------------------------------------- --
81 -- -------------------------------------------------------------------------- --
82 -- --
83 -- NAME: PROCEDURE BULK_DOWNLOAD_DTT --
84 -- --
85 -- TYPE: PROCEDURE --
86 -- --
87 -- PARAMETERS (IN OUT): p_load_id IN NUMBER The load id of the job --
88 -- p_src_filename IN VARCHAR2 --
89 -- p_resp_id IN NUMBER --
90 -- p_resp_appl_id IN NUMBER --
91 -- p_user_id IN NUMBER --
92 -- p_user_debug IN NUMBER --
93 -- --
94 -- PARAMETERS (OUT): x_request_id: The request id of the bulkload process --
95 -- x_error_msg_text: --
96 -- --
97 -- RETURN: n/a --
98 -- --
99 -- DESCRIPTION: Purpose This is the starting point of the bulkloading --
100 -- process. Submits a request to a concurrent program, --
101 -- that starts the location/mileage loading process --
102 -- Called from $fte/java/mileage/FteMileDwnldCO.java for --
103 -- Downloading DTT file --
104 -- --
105 -- --
106 -- CHANGE CONTROL LOG --
107 -- ------------------ --
108 -- --
109 -- DATE VERSION BY BUG DESCRIPTION --
110 -- ---------- ------- -------- ------- ---------------------------------- --
111 -- 2003/07/17 J ABLUNDEL Created --
112 -- --
113 -- -------------------------------------------------------------------------- --
114 PROCEDURE BULK_DOWNLOAD_DTT(p_load_id IN NUMBER,
115 p_template_id IN NUMBER,
116 p_origin_facility_id IN VARCHAR2,
117 p_origin_region_id IN VARCHAR2,
118 p_origin_all_fac_flag IN VARCHAR2,
119 p_all_fac_no_data_flag IN VARCHAR2,
120 p_dest_facility_id IN VARCHAR2,
121 p_dest_region_id IN VARCHAR2,
122 p_dest_all_fac_flag IN VARCHAR2,
123 p_file_extension IN VARCHAR2,
124 p_src_filename IN VARCHAR2,
125 p_resp_id IN NUMBER,
126 p_resp_appl_id IN NUMBER,
127 p_user_id IN NUMBER,
128 p_user_debug IN NUMBER,
129 x_filename OUT NOCOPY VARCHAR2,
130 x_request_id OUT NOCOPY NUMBER,
131 x_error_msg_text OUT NOCOPY VARCHAR2) IS
132
133
134 x_src_filedir VARCHAR2(100);
135 l_debug_on BOOLEAN;
136 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' ||'BULK_DOWNLOAD_DTT';
137 l_file_extension VARCHAR2(3);
138 l_filename VARCHAR2(50);
139 l_return_status VARCHAR2(1);
140 l_return_message VARCHAR2(2000);
141 l_user_debug VARCHAR2(1);
142
143 l_origin_facility_id NUMBER;
144 l_origin_region_id NUMBER;
145 l_dest_facility_id NUMBER;
146 l_dest_region_id NUMBER;
147
148
149 FTE_DIST_NO_FILENAME EXCEPTION;
150 FTE_DIST_ERR_CREATE_FILENAME EXCEPTION;
151
152 BEGIN
153
154 l_user_debug := 'N';
155
156 IF ((p_user_debug = 0) OR (p_user_debug is null)) THEN
157 l_user_debug := 'N';
158 END IF;
159
160 g_user_debug := p_user_debug;
161 --
162 -- SETUP DEBUGGING
163 --
164 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
165 IF l_debug_on IS NULL THEN
166 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
167 END IF;
168
169 IF ((g_user_debug is not null) AND (g_user_debug = 1)) THEN
170 l_debug_on := TRUE;
171 l_user_debug := 'Y';
172 END IF;
173
174
175 --
176 -- Debug Statements
177 --
178 IF l_debug_on THEN
179 WSH_DEBUG_SV.push(l_module_name);
180 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
181 WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
182 WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_template_id);
183 WSH_DEBUG_SV.log(l_module_name,'p_template_id',p_origin_facility_id);
184 WSH_DEBUG_SV.log(l_module_name,'p_origin_facility_id',p_origin_region_id);
185 WSH_DEBUG_SV.log(l_module_name,'p_origin_region_id',p_origin_all_fac_flag);
186 WSH_DEBUG_SV.log(l_module_name,'p_all_fac_no_data_flag',p_all_fac_no_data_flag);
187 WSH_DEBUG_SV.log(l_module_name,'p_dest_facility_id',p_dest_facility_id);
188 WSH_DEBUG_SV.log(l_module_name,'p_dest_region_id',p_dest_region_id);
189 WSH_DEBUG_SV.log(l_module_name,'p_dest_all_fac_flag',p_dest_all_fac_flag);
190 WSH_DEBUG_SV.log(l_module_name,'p_file_extension',p_file_extension);
191 WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
192 WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
193 WSH_DEBUG_SV.log(l_module_name,'p_resp_appl_id',p_resp_appl_id);
194 WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
195 WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
196 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
197 END IF;
198
199
200 --
201 -- The facility and region ids passed in are in tet format as java
202 -- seems to have a problem parsing to an integer if the string is big like 1000002008644
203 -- so we do it here
204 --
205 IF (p_origin_facility_id is not null) THEN
206 l_origin_facility_id := to_number(p_origin_facility_id);
207 ELSE
208 l_origin_facility_id := null;
209 END IF;
210
211 IF (p_origin_region_id is not null) THEN
212 l_origin_region_id := to_number(p_origin_region_id);
213 ELSE
214 l_origin_region_id := null;
215 END IF;
216
217 IF (p_dest_facility_id is not null) THEN
218 l_dest_facility_id := to_number(p_dest_facility_id);
219 ELSE
220 l_dest_facility_id := null;
221 END IF;
222
223 IF (p_dest_region_id is not null) THEN
224 l_dest_region_id := to_number(p_dest_region_id);
225 ELSE
226 l_dest_region_id := null;
227 END IF;
228
229
230 IF l_debug_on THEN
231 WSH_DEBUG_SV.logmsg(l_module_name,'Calling fnd_global.apps_initialize p_user_id, p_resp_id, p_resp_appl_id');
232 END IF;
233
234 fnd_global.apps_initialize(user_id => p_user_id,
235 resp_id => p_resp_id,
236 resp_appl_id => p_resp_appl_id);
237
238
239 IF l_debug_on THEN
240 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FTE_BULKLOAD_PKG.GET_UPLOAD_DIR');
241 END IF;
242
243 x_src_filedir := FTE_BULKLOAD_PKG.GET_UPLOAD_DIR;
244
245
246 IF l_debug_on THEN
247 WSH_DEBUG_SV.log(l_module_name,'x_src_filedir', x_src_filedir);
248 END IF;
249
250
251
252 --
253 -- Create the filename
254 --
255 l_file_extension := p_file_extension;
256
257 IF l_debug_on THEN
258 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILENAME');
259 END IF;
260
261 FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILENAME(p_user_debug_flag => l_user_debug,
262 x_file_extension => l_file_extension,
263 x_file_name => l_filename,
264 x_return_message => l_return_message,
265 x_return_status => l_return_status);
266
267
268 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
269 IF l_debug_on THEN
270 WSH_DEBUG_SV.logmsg(l_module_name,'Error from FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILENAME , l_return_status = '||l_return_status);
271 WSH_DEBUG_SV.logmsg(l_module_name,'RAISE FTE_DIST_ERR_CREATE_FILENAME');
272 END IF;
273
274 RAISE FTE_DIST_ERR_CREATE_FILENAME;
275 --
276 -- Debug Statements
277 --
278 IF l_debug_on THEN
279 WSH_DEBUG_SV.pop(l_module_name);
280 END IF;
281 --
282 RETURN;
283 END IF;
284
285 IF (l_filename is null) THEN
286 IF l_debug_on THEN
287 WSH_DEBUG_SV.logmsg(l_module_name,'l_filename is null - RAISE FTE_DIST_NO_FILENAME');
288 END IF;
289 --
290 RAISE FTE_DIST_NO_FILENAME;
291 --
292 -- Debug Statements
293 --
294 IF l_debug_on THEN
295 WSH_DEBUG_SV.pop(l_module_name);
296 END IF;
297 --
298 RETURN;
299 END IF;
300
301 x_filename := l_filename;
302
303 IF l_debug_on THEN
304 WSH_DEBUG_SV.log(l_module_name,'x_filename = ',x_filename);
305 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FND_REQUEST.SUBMIT_REQUEST for FTE FTE_BULK_DTT_DOWNLOAD');
306 END IF;
307
308
309
310 x_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'FTE',
311 program => 'FTE_BULK_DTT_DOWNLOAD',
312 description => null,
313 start_time => null,
314 sub_request => false,
315 argument1 => p_load_id,
316 argument2 => x_filename,
317 argument3 => x_src_filedir,
318 argument4 => p_user_debug,
319 argument5 => p_template_id,
320 argument6 => l_origin_facility_id,
321 argument7 => l_origin_region_id,
322 argument8 => p_origin_all_fac_flag,
323 argument9 => p_all_fac_no_data_flag,
324 argument10 => l_dest_facility_id,
325 argument11 => l_dest_region_id,
326 argument12 => p_dest_all_fac_flag,
327 argument13 => l_file_extension);
328
329
330 x_error_msg_text := fnd_message.get;
331
332 IF l_debug_on THEN
333 WSH_DEBUG_SV.log(l_module_name,'x_request_id',x_request_id);
334 WSH_DEBUG_SV.log(l_module_name,'x_error_msg_text',x_error_msg_text);
335 END IF;
336
337
338 commit;
339
340 --
341 -- Debug Statements
342 --
343 IF l_debug_on THEN
344 WSH_DEBUG_SV.pop(l_module_name);
345 END IF;
346 --
347
348 RETURN;
349
350
351
352 EXCEPTION
353 WHEN FTE_DIST_ERR_CREATE_FILENAME THEN
354 x_request_id := 0;
355 x_error_msg_text := l_return_message;
356 Fnd_File.Put_Line(Fnd_File.Log, x_error_msg_text);
357
358 --
359 -- Debug Statements
360 --
361 IF l_debug_on THEN
362 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_ERR_CREATE_FILENAME FTE_DIST_DWNLD_PKG.BULK_DOWNLOAD_DTT: '||x_error_msg_text);
363 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_ERR_CREATE_FILENAME');
364 WSH_DEBUG_SV.pop(l_module_name);
365 END IF;
366 --
367 RETURN;
368
369 WHEN FTE_DIST_NO_FILENAME THEN
370 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_FILENAME');
371 x_error_msg_text := FND_MESSAGE.GET;
372 Fnd_File.Put_Line(Fnd_File.Log, x_error_msg_text);
373 x_request_id := 0;
374 --
375 -- Debug Statements
376 --
377 IF l_debug_on THEN
378 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILENAME FTE_DIST_DWNLD_PKG.BULK_DOWNLOAD_DTT: '||x_error_msg_text);
379 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILENAME');
380 WSH_DEBUG_SV.pop(l_module_name);
381 END IF;
382 --
383 RETURN;
384
385 WHEN OTHERS THEN
386 x_error_msg_text := sqlerrm;
387 Fnd_File.Put_Line(Fnd_File.Log, 'Unexpected Error in Procedure BULK_DOWNLOAD_DTT' || sqlerrm);
388 x_request_id := 0;
389 --
390 -- Debug Statements
391 --
392 IF l_debug_on THEN
393 WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_DIST_DWNLD_PKG.BULK_DOWNLOAD_DTT IS: '||x_error_msg_text);
394 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||x_error_msg_text,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
395 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
396 END IF;
397 --
398 RETURN;
399
400
401 END BULK_DOWNLOAD_DTT;
402
403
404
405 -- -------------------------------------------------------------------------- --
406 -- --
407 -- NAME: PROCEDURE DOWNLOAD_DTT_FILE --
408 -- --
409 -- TYPE: PROCEDURE --
410 -- --
411 -- PARAMETERS (IN OUT): p_load_id IN NUMBER The load id of the job --
412 -- p_src_filename IN VARCHAR2 --
413 -- p_src_filedir IN VARCHAR2 --
414 -- p_user_debug IN NUMBER --
415 -- --
416 -- 1. p_load_id: The load id of the bulkload job. --
417 -- 2. p_src_filename: The filename of the file containing--
418 -- the DTT data. --
419 -- 3. p_src_filedir: The directory containing the DTT --
420 -- data file. There should be no --
421 -- trailing '/', and this directory --
422 -- should be readable by UTL_FILE --
423 -- 4. p_user_debug: turns the debugger on --
424 -- --
425 -- PARAMETERS (OUT): p_errbuf: A buffer of error messages --
426 -- p_retcode: The return code. A return code of '2' --
427 -- specifies ERROR --
428 -- --
429 -- RETURN: n/a --
430 -- --
431 -- DESCRIPTION: Runs the entire DTT downloading process --
432 -- Called from the FTE_BULK_DTT_DOWNLOAD concurrent --
433 -- program --
434 -- --
435 -- --
436 -- CHANGE CONTROL LOG --
437 -- --
438 -- DATE VERSION BY BUG DESCRIPTION --
439 -- ---------- ------- -------- ------- ---------------------------------- --
440 -- 2003/07/17 J ABLUNDEL Created --
441 -- --
442 -- -------------------------------------------------------------------------- --
443 PROCEDURE DOWNLOAD_DTT_FILE(p_errbuf OUT NOCOPY VARCHAR2,
444 p_retcode OUT NOCOPY VARCHAR2,
445 p_load_id IN NUMBER,
446 p_src_filename IN VARCHAR2,
447 p_src_filedir IN VARCHAR2,
448 p_user_debug IN NUMBER,
449 p_template_id IN NUMBER,
450 p_origin_facility_id IN NUMBER,
451 p_origin_region_id IN NUMBER,
452 p_origin_all_fac_flag IN VARCHAR2,
453 p_all_fac_no_data_flag IN VARCHAR2,
454 p_dest_facility_id IN NUMBER,
455 p_dest_region_id IN NUMBER,
456 p_dest_all_fac_flag IN VARCHAR2,
457 p_file_extension IN VARCHAR2) IS
458
459
460
461 g_first_time BOOLEAN;
462 l_return_status VARCHAR2(1);
463 l_return_message VARCHAR2(2000);
464 l_filename VARCHAR2(50);
465 l_dtt_file_name VARCHAR2(50);
466
467
468 l_debug_on BOOLEAN;
469 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' ||'DOWNLOAD_DTT_FILE';
470
471
472 BEGIN
473
474 g_user_debug := p_user_debug;
475
476 --
477 -- SETUP DEBUGGING
478 --
479 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
480 IF l_debug_on IS NULL THEN
481 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
482 END IF;
483
484 IF (g_user_debug = 1) THEN
485 l_debug_on := TRUE;
486 END IF;
487
488 --
489 -- Debug Statements
490 --
491 IF l_debug_on THEN
492 WSH_DEBUG_SV.push(l_module_name);
493 WSH_DEBUG_SV.logmsg(l_module_name,'-------DOWNLOAD_DTT_FILE-------');
494 WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
495 WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
496 WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
497 WSH_DEBUG_SV.log(l_module_name,'p_src_filedir',p_src_filedir);
498 WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
499 WSH_DEBUG_SV.log(l_module_name,'p_template_id',p_template_id);
500 WSH_DEBUG_SV.log(l_module_name,'p_origin_facility_id',p_origin_facility_id);
501 WSH_DEBUG_SV.log(l_module_name,'p_origin_region_id',p_origin_region_id);
502 WSH_DEBUG_SV.log(l_module_name,'p_origin_all_fac_flag',p_origin_all_fac_flag);
503 WSH_DEBUG_SV.log(l_module_name,'p_all_fac_no_data_flag',p_all_fac_no_data_flag);
504 WSH_DEBUG_SV.log(l_module_name,'p_dest_facility_id',p_dest_facility_id);
505 WSH_DEBUG_SV.log(l_module_name,'p_dest_region_id',p_dest_region_id);
506 WSH_DEBUG_SV.log(l_module_name,'p_dest_all_fac_flag',p_dest_all_fac_flag);
507 WSH_DEBUG_SV.log(l_module_name,'p_file_extension',p_file_extension);
508 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
509 END IF;
510
511
512 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
513 g_first_time := FIRST_TIME;
514 l_filename := p_src_filename;
515
516
517
518
519 IF (FIRST_TIME) THEN
520
521 IF (l_filename is not null) THEN
522
523 IF l_debug_on THEN
524 WSH_DEBUG_SV.logmsg(l_module_name,'l_filename is not null - stripping the extension');
525 END IF;
526
527 l_dtt_file_name := substr(l_filename,1,(instr(l_filename,'.')-1));
528
529 IF l_debug_on THEN
530 WSH_DEBUG_SV.log(l_module_name,'l_dtt_file_name',l_dtt_file_name);
531 END IF;
532
533 END IF;
534
535 IF l_debug_on THEN
536 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA');
537 END IF;
538
539 FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA(p_template_id => p_template_id,
540 p_origin_facility_id => p_origin_facility_id,
541 p_origin_region_id => p_origin_region_id,
542 p_origin_all_fac_flag => p_origin_all_fac_flag,
543 p_all_fac_no_data_flag => p_all_fac_no_data_flag,
544 p_dest_facility_id => p_dest_facility_id,
545 p_dest_region_id => p_dest_region_id,
546 p_dest_all_fac_flag => p_dest_all_fac_flag,
547 p_file_extension => p_file_extension,
548 p_user_debug_flag => null,
549 x_filename => l_dtt_file_name,
550 x_return_message => l_return_message,
551 x_return_status => l_return_status);
552
553
554
555 END IF;
556
557 IF (l_return_status = WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
558 --
559 -- Concurrent Manager expects 0 for success.
560 --
561 IF l_debug_on THEN
562 WSH_DEBUG_SV.logmsg(l_module_name,'COMPLETED DTT DOWNLOAD SUCCESSFULLY');
563 END IF;
564 p_retcode := 0;
565 p_errbuf := 'COMPLETED DTT DOWNLOAD SUCCESSFULLY';
566 ELSE
567 FND_FILE.PUT_LINE(FND_FILE.LOG,l_return_message);
568 IF l_debug_on THEN
569 WSH_DEBUG_SV.logmsg(l_module_name,'DTT DOWNLOAD Completed with errors');
570 WSH_DEBUG_SV.log(l_module_name,'l_return_message',l_return_message);
571 END IF;
572 --
573 p_retcode := 2;
574 p_errbuf := 'COMPLETED WITH ERRORS. ' || p_errbuf ||': '||l_return_message||'. Please Check Logs for more details.';
575 END IF;
576
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 FND_FILE.PUT_LINE(FND_FILE.LOG, '*****ERROR****' || SQLERRM);
581 p_retcode := 2;
582 p_errbuf := p_errbuf || sqlerrm;
583
584 --
585 -- Debug Statements
586 --
587 IF l_debug_on THEN
588 WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_DIST_DWNLD_PKG.DOWNLOAD_DTT_FILE IS: '||p_errbuf);
589 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||p_errbuf,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
590 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
591 END IF;
592 --
593
594 END DOWNLOAD_DTT_FILE;
595
596
597
598
599
600 -- -------------------------------------------------------------------------- --
601 -- --
602 -- NAME: DOWNLOAD_OD_DATA --
603 -- --
604 -- TYPE: PROCEDURE --
605 -- --
606 -- PARAMETERS (IN OUT): p_template_id IN NUMBER --
607 -- p_origin_facility_id IN NUMBER --
608 -- p_origin_region_id IN NUMBER --
609 -- p_origin_all_fac_flag IN VARCHAR2 --
610 -- p_all_fac_no_data_flag IN VARCHAR2 --
611 -- p_dest_facility_id IN NUMBER --
612 -- p_dest_region_id IN NUMBER --
613 -- p_dest_all_fac_flag IN VARCHAR2 --
614 -- --
615 -- PARAMETERS (OUT): x_return_message OUT NOCOPY VARCHAR2 --
616 -- x_return_status OUT NOCOPY VARCHAR2 --
617 -- --
618 -- RETURN: n/a --
619 -- --
620 -- DESCRIPTION: This procedure initiates the creation of an OD --
621 -- pair download file based on the input criteria --
622 -- --
623 -- --
624 -- CHANGE CONTROL LOG --
625 -- ------------------ --
626 -- --
627 -- DATE VERSION BY BUG DESCRIPTION --
628 -- ---------- ------- -------- ------- ---------------------------------- --
629 -- 2003/07/17 J ABLUNDEL Created --
630 -- --
631 -- -------------------------------------------------------------------------- --
632 PROCEDURE DOWNLOAD_OD_DATA(p_template_id IN NUMBER,
633 p_origin_facility_id IN NUMBER,
634 p_origin_region_id IN NUMBER,
635 p_origin_all_fac_flag IN VARCHAR2,
636 p_all_fac_no_data_flag IN VARCHAR2,
637 p_dest_facility_id IN NUMBER,
638 p_dest_region_id IN NUMBER,
639 p_dest_all_fac_flag IN VARCHAR2,
640 p_file_extension IN VARCHAR2,
641 p_user_debug_flag IN VARCHAR2,
642 x_filename IN OUT NOCOPY VARCHAR2,
643 x_return_message OUT NOCOPY VARCHAR2,
644 x_return_status OUT NOCOPY VARCHAR2) IS
645
646
647 --
648 -- Local Variable Definitions
649 --
650 l_distance_profile VARCHAR2(30); -- holds the FTE_DISTANCE_LVL profile option value
651 l_return_message VARCHAR2(2000); -- Return message from API (if error in API)
652 l_return_status VARCHAR2(1); -- Return Status from called API (values = S,E,W,U)
653 l_error_text VARCHAR2(2000); -- Holds the unexpected error text
654 l_filename VARCHAR2(240); -- holds the name of the download file
655 l_file_extension VARCHAR2(10); -- holds the file extension of the download file
656 l_ctr PLS_INTEGER; -- Used to check the input parameters
657 l_region_type NUMBER; -- holds the type of the region to download
658 l_origin_id NUMBER; -- holds the region or location origin id
659 l_origin_route PLS_INTEGER; -- holds the origin query route to take based on input
660 l_destination_route PLS_INTEGER; -- holds the destination query route to take based on input
661 l_destination_id NUMBER; -- holds the region or location destination id
662 l_user_debug_flag VARCHAR2(1); -- holds the debug flag from the java calling method
663
664
665 --
666 -- Exception Handlers
667 --
668 FTE_DIST_INVALID_PROFILE EXCEPTION;
669 FTE_DIST_NULL_PROFILE EXCEPTION;
670 FTE_DIST_NULL_TEMPLATE_ID EXCEPTION;
671 FTE_DIST_NULL_ORIGIN_INPUT EXCEPTION;
672 FTE_DIST_NULL_DEST_INPUT EXCEPTION;
673 FTE_DIST_MANY_ORIGIN_INPUT EXCEPTION;
674 FTE_DIST_MANY_DEST_INPUT EXCEPTION;
675 FTE_DIST_NULL_REGION_TYPE EXCEPTION;
676 FTE_DIST_NO_FILENAME EXCEPTION;
677 FTE_DIST_INVALID_FILE_LENGTH EXCEPTION;
678 FTE_DIST_INVALID_FILE_EXT EXCEPTION;
679 FTE_DIST_ERR_CREATE_FILENAME EXCEPTION;
680 FTE_DIST_DWNLD_FAILED EXCEPTION;
681
682
683
684
685 --
686 -- Local Debug Variable Definitions
687 --
688 l_debug_on BOOLEAN;
689 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DOWNLOAD_OD_DATA';
690
691
692
693 BEGIN
694
695 --
696 -- set the debug flag
697 --
698 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
699 IF (p_user_debug_flag <> 'Y') THEN
700 l_user_debug_flag := null;
701 ELSE
702 l_user_debug_flag := p_user_debug_flag;
703 END IF;
704
705 --
706 IF l_debug_on IS NULL THEN
707 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
708 END IF;
709
710 IF (l_user_debug_flag = 'Y') THEN
711 l_debug_on := TRUE;
712 END IF;
713 --
714 --
715 -- Debug Statements
716 --
717 IF l_debug_on THEN
718 WSH_DEBUG_SV.push(l_module_name);
719 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
720 WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
721 WSH_DEBUG_SV.log(l_module_name,'p_template_id',p_template_id);
722 WSH_DEBUG_SV.log(l_module_name,'p_origin_facility_id',p_origin_facility_id);
723 WSH_DEBUG_SV.log(l_module_name,'p_origin_region_id',p_origin_region_id);
724 WSH_DEBUG_SV.log(l_module_name,'p_origin_all_fac_flag',p_origin_all_fac_flag);
725 WSH_DEBUG_SV.log(l_module_name,'p_all_fac_no_data_flag',p_all_fac_no_data_flag);
726 WSH_DEBUG_SV.log(l_module_name,'p_dest_facility_id',p_dest_facility_id);
727 WSH_DEBUG_SV.log(l_module_name,'p_dest_region_id',p_dest_region_id);
728 WSH_DEBUG_SV.log(l_module_name,'p_dest_all_fac_flag',p_dest_all_fac_flag);
729 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
730 END IF;
731
732 --
733 -- Set the return flags for the start of the procedure
734 --
735 x_return_message := null;
736 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
737
738
739 --
740 -- Set the filename and extension to the local variables
741 --
742 l_filename := x_filename;
743 l_file_extension := p_file_extension;
744
745
746 IF l_debug_on THEN
747 WSH_DEBUG_SV.log(l_module_name,'l_filename = ',l_filename);
748 WSH_DEBUG_SV.log(l_module_name,'l_file_extension = ',l_file_extension);
749 END IF;
750
751 --
752 -- Check that the input parameters are OK
753 --
754 IF ((p_template_id is null) OR
755 (p_template_id = 0)) THEN
756 --
757 -- Template Id is null, cannot make a file without a template
758 -- Raise an error
759 --
760 IF l_debug_on THEN
761 WSH_DEBUG_SV.logmsg(l_module_name,'p_template_id is null raise FTE_DIST_NULL_TEMPLATE_ID exception');
762 END IF;
763 RAISE FTE_DIST_NULL_TEMPLATE_ID;
764 --
765 -- Debug Statements
766 --
767 IF l_debug_on THEN
768 WSH_DEBUG_SV.pop(l_module_name);
769 END IF;
770 --
771 RETURN;
772 END IF;
773
774
775
776 IF ((p_all_fac_no_data_flag is null) OR
777 (p_all_fac_no_data_flag <> 'Y')) THEN
778 IF l_debug_on THEN
779 WSH_DEBUG_SV.log(l_module_name,'p_all_fac_no_data_flag <> Y checking inputs');
780 END IF;
781 --
782 -- Check that there is no more than one origin input value
783 -- otherwise we could get very confused
784 --
785 l_ctr := 0;
786 IF (p_origin_facility_id > 0 ) THEN
787 l_ctr := l_ctr + 1;
788 END IF;
789
790 IF (p_origin_region_id > 0) THEN
791 l_ctr := l_ctr + 1;
792 END IF;
793
794 IF ((p_origin_all_fac_flag is not null) AND
795 (p_origin_all_fac_flag <> 'N')) THEN
796 l_ctr := l_ctr + 1;
797 END IF;
798
799 IF l_debug_on THEN
800 WSH_DEBUG_SV.logmsg(l_module_name,'Origin params l_ctr = ',l_ctr);
801 END IF;
802
803 IF (l_ctr = 0) THEN
804 --
805 -- No origin input provided - raise an error
806 --
807 IF l_debug_on THEN
808 WSH_DEBUG_SV.logmsg(l_module_name,'all origin input parameters are null, raise FTE_DIST_NULL_ORIGIN_INPUT exception');
809 END IF;
810
811 RAISE FTE_DIST_NULL_ORIGIN_INPUT;
812 --
813 -- Debug Statements
814 --
815 IF l_debug_on THEN
816 WSH_DEBUG_SV.pop(l_module_name);
817 END IF;
818 --
819 RETURN;
820
821 ELSIF (l_ctr > 1) THEN
822 --
823 -- Too many origin inputs provided - raise an error
824 --
825 IF l_debug_on THEN
826 WSH_DEBUG_SV.logmsg(l_module_name,'too many origin input parameters, raise FTE_DIST_MANY_ORIGIN_INPUT exception');
827 END IF;
828
829 RAISE FTE_DIST_MANY_ORIGIN_INPUT;
830 --
831 -- Debug Statements
832 --
833 IF l_debug_on THEN
834 WSH_DEBUG_SV.pop(l_module_name);
835 END IF;
836 --
837 RETURN;
838 END IF;
839
840
841 --
842 -- reset the counter to check the destination input parameters
843 --
844 l_ctr := 0;
845
846 --
847 -- Check that there is no more than one destination input value
848 -- otherwise we could get very confused
849 --
850 IF (p_dest_facility_id > 0) THEN
851 l_ctr := l_ctr + 1;
852 END IF;
853
854 IF (p_dest_region_id > 0) THEN
855 l_ctr := l_ctr + 1;
856 END IF;
857
858 IF ((p_dest_all_fac_flag is not null) AND
859 (p_dest_all_fac_flag <> 'N')) THEN
860 l_ctr := l_ctr + 1;
861 END IF;
862
863 IF l_debug_on THEN
864 WSH_DEBUG_SV.logmsg(l_module_name,'Destination params l_ctr = ',l_ctr);
865 END IF;
866
867 IF (l_ctr = 0) THEN
868 --
869 -- No destination input provided - raise an error
870 --
871 IF l_debug_on THEN
872 WSH_DEBUG_SV.logmsg(l_module_name,'all destination input parameters are null, raise FTE_DIST_NULL_DEST_INPUT exception');
873 END IF;
874
875
876 RAISE FTE_DIST_NULL_DEST_INPUT;
877 --
878 -- Debug Statements
879 --
880 IF l_debug_on THEN
881 WSH_DEBUG_SV.pop(l_module_name);
882 END IF;
883 --
884 RETURN;
885
886 ELSIF (l_ctr > 1) THEN
887 --
888 -- Too many destination inputs provided - raise an error
889 --
890 IF l_debug_on THEN
891 WSH_DEBUG_SV.logmsg(l_module_name,'too many destination input parameters , raise FTE_DIST_MANY_DEST_INPUT exception');
892 END IF;
893
894
895 RAISE FTE_DIST_MANY_DEST_INPUT;
896 --
897 -- Debug Statements
898 --
899 IF l_debug_on THEN
900 WSH_DEBUG_SV.pop(l_module_name);
901 END IF;
902 --
903 RETURN;
904 END IF;
905 -- ELSE
906 --
907 -- all facilities without distance has been selected
908 --
909 END IF;
910
911
912 --
913 -- Check the filename and file extension parameters
914 --
915 IF (l_filename is null) THEN
916 --
917 -- The filename is null we will create one. If the extension is null
918 -- then we will make one up
919 --
920
921 IF (l_file_extension is not null) THEN
922 IF (length(l_file_extension) > g_file_ext_length) THEN
923 RAISE FTE_DIST_INVALID_FILE_EXT;
924 --
925 -- Debug Statements
926 --
927 IF l_debug_on THEN
928 WSH_DEBUG_SV.pop(l_module_name);
929 END IF;
930 --
931 RETURN;
932 END IF;
933 END IF;
934
935
936 IF l_debug_on THEN
937 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILENAME',WSH_DEBUG_SV.C_PROC_LEVEL);
938 WSH_DEBUG_SV.log(l_module_name,'p_user_debug_flag =',l_user_debug_flag);
939 WSH_DEBUG_SV.log(l_module_name,'l_file_extension= ',l_file_extension);
940 WSH_DEBUG_SV.log(l_module_name,'l_filename = ',l_filename);
941 WSH_DEBUG_SV.log(l_module_name,'l_return_message = ',l_return_message);
942 WSH_DEBUG_SV.log(l_module_name,'l_return_status = ',l_return_status);
943 END IF;
944 --
945
946 FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILENAME(p_user_debug_flag => l_user_debug_flag,
947 x_file_extension => l_file_extension,
948 x_file_name => l_filename,
949 x_return_message => l_return_message,
950 x_return_status => l_return_status);
951
952
953
954 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
955 IF l_debug_on THEN
956 WSH_DEBUG_SV.log(l_module_name,'FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILENAME failed, return status = ',l_return_status);
957 WSH_DEBUG_SV.log(l_module_name,'l_return_message = ',l_return_message);
958 WSH_DEBUG_SV.logmsg(l_module_name,'RAISE FTE_DIST_ERR_CREATE_FILENAME');
959 END IF;
960
961 RAISE FTE_DIST_ERR_CREATE_FILENAME;
962 --
963 -- Debug Statements
964 --
965 IF l_debug_on THEN
966 WSH_DEBUG_SV.pop(l_module_name);
967 END IF;
968 --
969 RETURN;
970 END IF;
971
972 IF (l_filename is null) THEN
973 RAISE FTE_DIST_NO_FILENAME;
974 RETURN;
975 END IF;
976 ELSE
977 --
978 -- A filename has been passed in to the API
979 --
980 IF (length(l_filename) <> g_filename_length) THEN
981 --
982 -- The filename is incorrect
983 --
984 RAISE FTE_DIST_INVALID_FILE_LENGTH;
985 --
986 -- Debug Statements
987 --
988 IF l_debug_on THEN
989 WSH_DEBUG_SV.pop(l_module_name);
990 END IF;
991 --
992 RETURN;
993 END IF;
994
995 IF (l_file_extension is null) THEN
996 l_file_extension := g_default_file_ext;
997 END IF;
998 l_filename := l_filename||'.'||l_file_extension;
999 END IF;
1000
1001 x_filename := l_filename;
1002
1003
1004 IF l_debug_on THEN
1005 WSH_DEBUG_SV.log(l_module_name,'x_filename = ',x_filename);
1006 END IF;
1007
1008 --
1009 -- End of checking input parameters for correctness.
1010 --
1011
1012
1013
1014 IF l_debug_on THEN
1015 WSH_DEBUG_SV.logmsg(l_module_name,'Get the profile option value of the distance stuff to see what region level we should be searching for');
1016 END IF;
1017 --
1018 -- Get the profile option value
1019 --
1020 --
1021 -- Get the profile option of the distance stuff to
1022 -- see what region level we should be searching for
1023 --
1024 IF l_debug_on THEN
1025 WSH_DEBUG_SV.logmsg(l_module_name,'getting the distance profile valie fnd_profile.get(FTE_DISTANCE_LVL)');
1026 END IF;
1027
1028 fnd_profile.get('FTE_DISTANCE_LVL',l_distance_profile);
1029
1030 IF l_debug_on THEN
1031 WSH_DEBUG_SV.log(l_module_name,'fnd_profile.get(FTE_DISTANCE_LVL)= ',l_distance_profile);
1032 END IF;
1033
1034 IF (l_distance_profile is null) THEN
1035 --
1036 -- The profile option is null - raise an error
1037 --
1038 IF l_debug_on THEN
1039 WSH_DEBUG_SV.logmsg(l_module_name,'distance profile is null raise FTE_DIST_NULL_PROFILE exception');
1040 END IF;
1041 RAISE FTE_DIST_NULL_PROFILE;
1042 --
1043 -- Debug Statements
1044 --
1045 IF l_debug_on THEN
1046 WSH_DEBUG_SV.pop(l_module_name);
1047 END IF;
1048 --
1049 RETURN;
1050 END IF;
1051
1052
1053 IF ((l_distance_profile <> 'CITYSTATE') AND
1054 (l_distance_profile <> 'ZIP') AND
1055 (l_distance_profile <> 'COUNTY')) THEN
1056 --
1057 -- The profile option has an invalid value - raise an error
1058 --
1059 IF l_debug_on THEN
1060 WSH_DEBUG_SV.logmsg(l_module_name,'The profile option has an invalid value - raise an error RAISE FTE_DIST_INVALID_PROFILE');
1061 END IF;
1062
1063 RAISE FTE_DIST_INVALID_PROFILE;
1064 --
1065 -- Debug Statements
1066 --
1067 IF l_debug_on THEN
1068 WSH_DEBUG_SV.pop(l_module_name);
1069 END IF;
1070 --
1071 RETURN;
1072 END IF;
1073
1074
1075 IF (l_distance_profile = 'CITYSTATE') THEN
1076 --
1077 -- region type is city level
1078 --
1079 l_region_type := 2;
1080
1081 IF l_debug_on THEN
1082 WSH_DEBUG_SV.log(l_module_name,'distance profile is CITYSTATE - region type = ',l_region_type);
1083 END IF;
1084 ELSIF (l_distance_profile = 'ZIP') THEN
1085 --
1086 -- region type is zip/postal level
1087 --
1088 l_region_type := 3;
1089
1090 IF l_debug_on THEN
1091 WSH_DEBUG_SV.log(l_module_name,'distance profile is ZIP - region type = ',l_region_type);
1092 END IF;
1093 ELSIF (l_distance_profile = 'COUNTY') THEN
1094 --
1095 -- region type is county level
1096 --
1097 l_region_type := 4;
1098 IF l_debug_on THEN
1099 WSH_DEBUG_SV.log(l_module_name,'distance profile is COUNTY - region type = ',l_region_type);
1100 END IF;
1101 ELSE
1102 --
1103 -- The profile option has an invalid value - raise an error
1104 --
1105 IF l_debug_on THEN
1106 WSH_DEBUG_SV.logmsg(l_module_name,'The profile option has an invalid value - raise an error RAISE FTE_DIST_INVALID_PROFILE');
1107 END IF;
1108
1109 RAISE FTE_DIST_INVALID_PROFILE;
1110 --
1111 -- Debug Statements
1112 --
1113 IF l_debug_on THEN
1114 WSH_DEBUG_SV.pop(l_module_name);
1115 END IF;
1116 --
1117 RETURN;
1118 END IF;
1119
1120
1121 IF (l_region_type is null) THEN
1122 --
1123 -- region type is null cannot have that Raise an error
1124 --
1125 IF l_debug_on THEN
1126 WSH_DEBUG_SV.logmsg(l_module_name,'region type is null - raise an error FTE_DIST_NULL_REGION_TYPE');
1127 END IF;
1128 RAISE FTE_DIST_NULL_REGION_TYPE;
1129 --
1130 -- Debug Statements
1131 --
1132 IF l_debug_on THEN
1133 WSH_DEBUG_SV.pop(l_module_name);
1134 END IF;
1135 --
1136 RETURN;
1137 END IF;
1138
1139
1140 --
1141 -- There are 3 scenarios for the origin and 3 for the destination
1142 -- and 1 for both (facilities w/o distance and t time data) determine
1143 -- which routes to go for each set
1144 -- route_1: facility_id
1145 -- route_2: region_id
1146 -- route_3: all eligible facilities
1147 -- route_4: all facilities without distance and transit time data
1148 --
1149 IF (p_origin_facility_id > 0) THEN
1150 l_origin_route := 1;
1151 l_origin_id := p_origin_facility_id;
1152 END IF;
1153
1154 IF (p_origin_region_id > 0) THEN
1155 l_origin_route := 2;
1156 l_origin_id := p_origin_region_id;
1157 END IF;
1158
1159 IF ((p_origin_all_fac_flag is not null) AND
1160 (p_origin_all_fac_flag <> 'N')) THEN
1161 l_origin_route := 3;
1162 END IF;
1163
1164 IF (p_dest_facility_id > 0) THEN
1165 l_destination_route := 1;
1166 l_destination_id := p_dest_facility_id;
1167 END IF;
1168
1169 IF (p_dest_region_id > 0) THEN
1170 l_destination_route := 2;
1171 l_destination_id := p_dest_region_id;
1172 END IF;
1173
1174 IF ((p_dest_all_fac_flag is not null) AND
1175 (p_dest_all_fac_flag <> 'N')) THEN
1176 l_destination_route := 3;
1177 END IF;
1178
1179 IF (p_all_fac_no_data_flag = 'Y') THEN
1180 --
1181 -- this applies to both
1182 --
1183 l_origin_route := 4;
1184 l_destination_route := 4;
1185 END IF;
1186
1187
1188
1189 IF l_debug_on THEN
1190 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILE',WSH_DEBUG_SV.C_PROC_LEVEL);
1191 WSH_DEBUG_SV.logmsg(l_module_name,'------------ INPUT PARAMETERS ------------');
1192 WSH_DEBUG_SV.log(l_module_name,'l_origin_route = ',l_origin_route);
1193 WSH_DEBUG_SV.log(l_module_name,'l_destination_route = ',l_destination_route);
1194 WSH_DEBUG_SV.log(l_module_name,'l_origin_id = ',l_origin_id);
1195 WSH_DEBUG_SV.log(l_module_name,'l_destination_id = ',l_destination_id);
1196 WSH_DEBUG_SV.log(l_module_name,'p_template_id = ',p_template_id);
1197 WSH_DEBUG_SV.log(l_module_name,'l_filename = ',l_filename);
1198 WSH_DEBUG_SV.log(l_module_name,'l_file_extension = ',l_file_extension);
1199 WSH_DEBUG_SV.log(l_module_name,'l_region_type = ',l_region_type);
1200 WSH_DEBUG_SV.log(l_module_name,'l_user_debug_flag = ',l_user_debug_flag);
1201 WSH_DEBUG_SV.log(l_module_name,'l_return_message = ',l_return_message);
1202 WSH_DEBUG_SV.log(l_module_name,'l_return_status = ',l_return_status);
1203 END IF;
1204 --
1205 --
1206 -- Create the download file
1207 --
1208 FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILE(p_origin_route => l_origin_route,
1209 p_destination_route => l_destination_route,
1210 p_origin_id => l_origin_id,
1211 p_destination_id => l_destination_id,
1212 p_template_id => p_template_id,
1213 p_file_name => l_filename,
1214 p_file_extension => l_file_extension,
1215 p_region_type => l_region_type,
1216 p_distance_profile => l_distance_profile,
1217 p_user_debug_flag => l_user_debug_flag,
1218 x_return_message => l_return_message,
1219 x_return_status => l_return_status);
1220
1221
1222 IF (l_return_message <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1223 --
1224 -- The Download File suffered an error!
1225 --
1226 RAISE FTE_DIST_DWNLD_FAILED;
1227
1228 IF l_debug_on THEN
1229 WSH_DEBUG_SV.pop(l_module_name);
1230 END IF;
1231 --
1232 RETURN;
1233
1234 END IF;
1235
1236 --
1237 -- Debug Statements
1238 --
1239 IF l_debug_on THEN
1240 WSH_DEBUG_SV.pop(l_module_name);
1241 END IF;
1242 --
1243
1244 --
1245 -- Everything was OK
1246 --
1247 x_return_message := null;
1248 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1249
1250 --
1251 -- commit the changes
1252 --
1253 commit;
1254
1255
1256 --
1257 -- Lets go home
1258 --
1259 RETURN;
1260
1261
1262 EXCEPTION
1263 WHEN FTE_DIST_INVALID_PROFILE THEN
1264 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INVALID_PROFILE');
1265 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1266 x_return_message := FND_MESSAGE.GET;
1267 WSH_UTIL_CORE.add_message(x_return_status);
1268
1269 --
1270 -- Close any open cursors
1271 --
1272
1273 --
1274 -- Debug Statements
1275 --
1276 IF l_debug_on THEN
1277 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_INVALID_PROFILE RAISED');
1278 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_PROFILE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1279 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_PROFILE');
1280 END IF;
1281 --
1282 -- Debug Statements
1283 --
1284 IF l_debug_on THEN
1285 WSH_DEBUG_SV.pop(l_module_name);
1286 END IF;
1287 --
1288 RETURN;
1289
1290
1291 WHEN FTE_DIST_NULL_PROFILE THEN
1292 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NULL_PROFILE');
1293 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1294 x_return_message := FND_MESSAGE.GET;
1295 WSH_UTIL_CORE.add_message(x_return_status);
1296
1297 --
1298 -- Close any open cursors
1299 --
1300
1301 --
1302 -- Debug Statements
1303 --
1304 IF l_debug_on THEN
1305 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.GET_DISTANCE_TIME FTE_DIST_NULL_PROFILE RAISED');
1306 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_PROFILE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1307 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_PROFILE');
1308 END IF;
1309 --
1310 -- Debug Statements
1311 --
1312 IF l_debug_on THEN
1313 WSH_DEBUG_SV.pop(l_module_name);
1314 END IF;
1315 --
1316 RETURN;
1317
1318
1319 WHEN FTE_DIST_NULL_TEMPLATE_ID THEN
1320 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NULL_TEMPLATE_ID');
1321 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1322 x_return_message := FND_MESSAGE.GET;
1323 WSH_UTIL_CORE.add_message(x_return_status);
1324
1325 --
1326 -- Close any open cursors
1327 --
1328
1329 --
1330 -- Debug Statements
1331 --
1332 IF l_debug_on THEN
1333 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_NULL_TEMPLATE_ID RAISED');
1334 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_TEMPLATE_ID exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1335 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_TEMPLATE_ID');
1336 END IF;
1337 --
1338 -- Debug Statements
1339 --
1340 IF l_debug_on THEN
1341 WSH_DEBUG_SV.pop(l_module_name);
1342 END IF;
1343 --
1344 RETURN;
1345
1346
1347 WHEN FTE_DIST_NULL_ORIGIN_INPUT THEN
1348 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NULL_ORIGIN_INPUT');
1349 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1350 x_return_message := FND_MESSAGE.GET;
1351 WSH_UTIL_CORE.add_message(x_return_status);
1352
1353 --
1354 -- Close any open cursors
1355 --
1356
1357 --
1358 -- Debug Statements
1359 --
1360 IF l_debug_on THEN
1361 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_NULL_ORIGIN_INPUT RAISED');
1362 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_ORIGIN_INPUT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1363 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_ORIGIN_INPUT');
1364 END IF;
1365 --
1366 -- Debug Statements
1367 --
1368 IF l_debug_on THEN
1369 WSH_DEBUG_SV.pop(l_module_name);
1370 END IF;
1371 --
1372 RETURN;
1373
1374 WHEN FTE_DIST_NULL_DEST_INPUT THEN
1375 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NULL_DEST_INPUT');
1376 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1377 x_return_message := FND_MESSAGE.GET;
1378 WSH_UTIL_CORE.add_message(x_return_status);
1379
1380 --
1381 -- Close any open cursors
1382 --
1383
1384 --
1385 -- Debug Statements
1386 --
1387 IF l_debug_on THEN
1388 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_NULL_DEST_INPUT RAISED');
1389 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_DEST_INPUT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1390 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_DEST_INPUT');
1391 END IF;
1392 --
1393 -- Debug Statements
1394 --
1395 IF l_debug_on THEN
1396 WSH_DEBUG_SV.pop(l_module_name);
1397 END IF;
1398 --
1399 RETURN;
1400
1401 WHEN FTE_DIST_MANY_ORIGIN_INPUT THEN
1402 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_MANY_ORIGIN_INPUT');
1403 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1404 x_return_message := FND_MESSAGE.GET;
1405 WSH_UTIL_CORE.add_message(x_return_status);
1406
1407 --
1408 -- Close any open cursors
1409 --
1410
1411 --
1412 -- Debug Statements
1413 --
1414 IF l_debug_on THEN
1415 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_MANY_ORIGIN_INPUT RAISED');
1416 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_MANY_ORIGIN_INPUT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1417 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_MANY_ORIGIN_INPUT');
1418 END IF;
1419 --
1420 -- Debug Statements
1421 --
1422 IF l_debug_on THEN
1423 WSH_DEBUG_SV.pop(l_module_name);
1424 END IF;
1425 --
1426 RETURN;
1427
1428 WHEN FTE_DIST_MANY_DEST_INPUT THEN
1429 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_MANY_DEST_INPUT');
1430 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1431 x_return_message := FND_MESSAGE.GET;
1432 WSH_UTIL_CORE.add_message(x_return_status);
1433
1434 --
1435 -- Close any open cursors
1436 --
1437
1438 --
1439 -- Debug Statements
1440 --
1441 IF l_debug_on THEN
1442 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_MANY_DEST_INPUT RAISED');
1443 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_MANY_DEST_INPUT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1444 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_MANY_DEST_INPUT');
1445 END IF;
1446 --
1447 -- Debug Statements
1448 --
1449 IF l_debug_on THEN
1450 WSH_DEBUG_SV.pop(l_module_name);
1451 END IF;
1452 --
1453 RETURN;
1454
1455
1456 WHEN FTE_DIST_NULL_REGION_TYPE THEN
1457 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NULL_REGION_TYPE');
1458 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1459 x_return_message := FND_MESSAGE.GET;
1460 WSH_UTIL_CORE.add_message(x_return_status);
1461
1462 --
1463 -- Close any open cursors
1464 --
1465
1466 --
1467 -- Debug Statements
1468 --
1469 IF l_debug_on THEN
1470 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_NULL_REGION_TYPE RAISED');
1471 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NULL_REGION_TYPE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1472 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NULL_REGION_TYPE');
1473 END IF;
1474 --
1475 -- Debug Statements
1476 --
1477 IF l_debug_on THEN
1478 WSH_DEBUG_SV.pop(l_module_name);
1479 END IF;
1480 --
1481 RETURN;
1482
1483
1484 WHEN FTE_DIST_NO_FILENAME THEN
1485 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_FILENAME');
1486 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1487 x_return_message := FND_MESSAGE.GET;
1488 WSH_UTIL_CORE.add_message(x_return_status);
1489
1490 --
1491 -- Close any open cursors
1492 --
1493
1494 --
1495 -- Debug Statements
1496 --
1497 IF l_debug_on THEN
1498 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_NO_FILENAME RAISED');
1499 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_FILENAME exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1500 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_FILENAME');
1501 END IF;
1502 --
1503 -- Debug Statements
1504 --
1505 IF l_debug_on THEN
1506 WSH_DEBUG_SV.pop(l_module_name);
1507 END IF;
1508 --
1509 RETURN;
1510
1511
1512 WHEN FTE_DIST_INVALID_FILE_LENGTH THEN
1513 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INVALID_FILE_LENGTH');
1514 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1515 x_return_message := FND_MESSAGE.GET;
1516 WSH_UTIL_CORE.add_message(x_return_status);
1517
1518 --
1519 -- Close any open cursors
1520 --
1521
1522 --
1523 -- Debug Statements
1524 --
1525 IF l_debug_on THEN
1526 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_INVALID_FILE_LENGTH RAISED');
1527 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_FILE_LENGTH exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1528 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_FILE_LENGTH');
1529 END IF;
1530 --
1531 -- Debug Statements
1532 --
1533 IF l_debug_on THEN
1534 WSH_DEBUG_SV.pop(l_module_name);
1535 END IF;
1536 --
1537 RETURN;
1538
1539
1540 WHEN FTE_DIST_INVALID_FILE_EXT THEN
1541 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INVALID_FILE_EXT');
1542 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1543 x_return_message := FND_MESSAGE.GET;
1544 WSH_UTIL_CORE.add_message(x_return_status);
1545
1546 --
1547 -- Close any open cursors
1548 --
1549
1550 --
1551 -- Debug Statements
1552 --
1553 IF l_debug_on THEN
1554 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_INVALID_FILE_EXT RAISED');
1555 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_FILE_EXT exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1556 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_FILE_EXT');
1557 END IF;
1558 --
1559 -- Debug Statements
1560 --
1561 IF l_debug_on THEN
1562 WSH_DEBUG_SV.pop(l_module_name);
1563 END IF;
1564 --
1565 RETURN;
1566
1567
1568 WHEN FTE_DIST_ERR_CREATE_FILENAME THEN
1569 x_return_status := l_return_status;
1570 x_return_message := l_return_message;
1571 --
1572 -- Close any open cursors
1573 --
1574
1575 --
1576 -- Debug Statements
1577 --
1578 IF l_debug_on THEN
1579 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_ERR_CREATE_FILENAME RAISED');
1580 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_ERR_CREATE_FILENAME exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1581 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_ERR_CREATE_FILENAME');
1582 END IF;
1583 --
1584 -- Debug Statements
1585 --
1586 IF l_debug_on THEN
1587 WSH_DEBUG_SV.pop(l_module_name);
1588 END IF;
1589 --
1590 RETURN;
1591
1592 WHEN FTE_DIST_DWNLD_FAILED THEN
1593 x_return_status := l_return_status;
1594 x_return_message := l_return_message;
1595 --
1596 -- Close any open cursors
1597 --
1598
1599 --
1600 -- Debug Statements
1601 --
1602 IF l_debug_on THEN
1603 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA FTE_DIST_DWNLD_FAILED RAISED');
1604 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_DWNLD_FAILED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1605 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_DWNLD_FAILED');
1606 END IF;
1607 --
1608 -- Debug Statements
1609 --
1610 IF l_debug_on THEN
1611 WSH_DEBUG_SV.pop(l_module_name);
1612 END IF;
1613 --
1614 RETURN;
1615
1616 WHEN OTHERS THEN
1617 l_error_text := SQLERRM;
1618
1619 --
1620 -- Close any open cursors
1621 --
1622
1623 --
1624 -- Debug Statements
1625 --
1626 IF l_debug_on THEN
1627 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA IS ' ||L_ERROR_TEXT );
1628 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1629 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1630 END IF;
1631 --
1632 WSH_UTIL_CORE.default_handler('FTE_DIST_DWNLD_PKG.DOWNLOAD_OD_DATA');
1633 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1634 x_return_message := l_error_text;
1635 --
1636 -- Debug Statements
1637 --
1638 IF l_debug_on THEN
1639 WSH_DEBUG_SV.pop(l_module_name);
1640 END IF;
1641 --
1642 RETURN;
1643
1644 END DOWNLOAD_OD_DATA;
1645
1646
1647
1648
1649 -- -------------------------------------------------------------------------- --
1650 -- --
1651 -- NAME: CREATE_DWNLD_FILENAME --
1652 -- --
1653 -- TYPE: FUNCTION --
1654 -- --
1655
1656 PROCEDURE CREATE_DWNLD_FILENAME(p_user_debug_flag IN VARCHAR2,
1657 x_file_extension IN OUT NOCOPY VARCHAR2,
1658 x_file_name OUT NOCOPY VARCHAR2,
1659 x_return_message OUT NOCOPY VARCHAR2,
1660 x_return_status OUT NOCOPY VARCHAR2) IS
1661
1662
1663 --
1664 -- Gets the file id used to create the file name
1665 --
1666 cursor c_get_file_id IS
1667 select fte_mile_dlf_file_s.nextval
1668 from dual;
1669
1670
1671
1672 l_file_id NUMBER;
1673 l_file_name VARCHAR2(30);
1674 l_file_ext VARCHAR2(3);
1675 l_error_text VARCHAR2(2000);
1676
1677
1678
1679 FTE_DIST_NO_FILE_ID EXCEPTION;
1680
1681 --
1682 -- Local Debug Variable Definitions
1683 --
1684 l_debug_on BOOLEAN;
1685 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DWNLD_FILENAME';
1686
1687 BEGIN
1688
1689
1690 --
1691 -- set the debug flag
1692 --
1693 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1694 --
1695 IF l_debug_on IS NULL THEN
1696 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1697 END IF;
1698 IF (p_user_debug_flag = 'Y') THEN
1699 l_debug_on := TRUE;
1700 END IF;
1701 --
1702 --
1703 -- Debug Statements
1704 --
1705 IF l_debug_on THEN
1706 WSH_DEBUG_SV.push(l_module_name);
1707 WSH_DEBUG_SV.logmsg(l_module_name,'-------CREATE_DWNLD_FILENAME-------');
1708 WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
1709 WSH_DEBUG_SV.log(l_module_name,'x_file_extension',x_file_extension);
1710 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
1711 END IF;
1712
1713 --
1714 -- Set the return flags for the start of the procedure
1715 --
1716 x_return_message := null;
1717 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1718
1719
1720 IF l_debug_on THEN
1721 WSH_DEBUG_SV.logmsg(l_module_name,'Opening c_get_file_id cursor');
1722 END IF;
1723
1724 OPEN c_get_file_id;
1725 FETCH c_get_file_id INTO l_file_id;
1726 CLOSE c_get_file_id;
1727
1728
1729 IF (l_file_id is null) THEN
1730 IF l_debug_on THEN
1731 WSH_DEBUG_SV.logmsg(l_module_name,'l_file_id from cursor is null - raise FTE_DIST_NO_FILE_ID');
1732 END IF;
1733
1734 RAISE FTE_DIST_NO_FILE_ID;
1735 --
1736 -- Debug Statements
1737 --
1738 IF l_debug_on THEN
1739 WSH_DEBUG_SV.pop(l_module_name);
1740 END IF;
1741 --
1742
1743 RETURN;
1744 END IF;
1745
1746 l_file_name := g_file_prefix||lpad(to_char(l_file_id),5,'0');
1747 l_file_ext := nvl(x_file_extension,g_default_file_ext);
1748
1749 IF l_debug_on THEN
1750 WSH_DEBUG_SV.log(l_module_name,'l_file_name = ',l_file_name);
1751 WSH_DEBUG_SV.log(l_module_name,'l_file_ext = ',l_file_ext);
1752 END IF;
1753
1754 x_file_name := l_file_name||'.'||l_file_ext;
1755 x_file_extension := l_file_ext;
1756
1757 IF l_debug_on THEN
1758 WSH_DEBUG_SV.log(l_module_name,'x_file_name = ',x_file_name);
1759 WSH_DEBUG_SV.log(l_module_name,'x_file_extension = ',x_file_extension);
1760 END IF;
1761
1762 x_return_message := null;
1763 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1764
1765 --
1766 -- Debug Statements
1767 --
1768 IF l_debug_on THEN
1769 WSH_DEBUG_SV.pop(l_module_name);
1770 END IF;
1771 --
1772 RETURN;
1773
1774
1775 EXCEPTION
1776 WHEN FTE_DIST_NO_FILE_ID THEN
1777
1778 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_FILE_ID');
1779 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1780 x_return_message := FND_MESSAGE.GET;
1781 WSH_UTIL_CORE.add_message(x_return_status);
1782
1783 --
1784 -- Close any open cursors
1785 --
1786 IF (c_get_file_id%ISOPEN) THEN
1787 CLOSE c_get_file_id;
1788 END IF;
1789 --
1790 -- Debug Statements
1791 --
1792 IF l_debug_on THEN
1793 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILENAME FTE_DIST_NO_FILE_ID RAISED');
1794 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_FILE_ID exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
1795 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_FILE_ID');
1796 END IF;
1797 --
1798 -- Debug Statements
1799 --
1800 IF l_debug_on THEN
1801 WSH_DEBUG_SV.pop(l_module_name);
1802 END IF;
1803 --
1804 RETURN;
1805
1806
1807 WHEN OTHERS THEN
1808 l_error_text := SQLERRM;
1809
1810 --
1811 -- Close any open cursors
1812 --
1813 IF (c_get_file_id%ISOPEN) THEN
1814 CLOSE c_get_file_id;
1815 END IF;
1816 --
1817 -- Debug Statements
1818 --
1819 IF l_debug_on THEN
1820 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILENAME IS ' ||L_ERROR_TEXT );
1821 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1822 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1823 END IF;
1824 --
1825 WSH_UTIL_CORE.default_handler('FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILENAME');
1826 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1827 x_return_message := x_return_message||' - '||l_error_text;
1828 --
1829 -- Debug Statements
1830 --
1831 IF l_debug_on THEN
1832 WSH_DEBUG_SV.pop(l_module_name);
1833 END IF;
1834 --
1835 RETURN;
1836
1837
1838
1839 END CREATE_DWNLD_FILENAME;
1840
1841
1842
1843
1844 -- -------------------------------------------------------------------------- --
1845 -- --
1846 -- NAME: CREATE_DWNLD_FILE --
1847 -- --
1848 -- TYPE: PROCEDURE --
1849 -- --
1850 -- PARAMETERS (IN OUT): --
1851 -- --
1852 -- PARAMETERS (OUT): --
1853 -- x_return_message OUT NOCOPY VARCHAR2 --
1854 -- x_return_status OUT NOCOPY VARCHAR2 --
1855 -- --
1856 -- RETURN: none --
1857 -- --
1858 -- DESCRIPTION: This procedure performs the distance and transit time --
1859 -- --
1860 -- CHANGE CONTROL LOG --
1861 -- ------------------ --
1862 -- --
1863 -- DATE VERSION BY BUG DESCRIPTION --
1864 -- ---------- ------- -------- ------- ---------------------------------- --
1865 -- 2003/07/17 J ABLUNDEL Created --
1866 -- --
1867 -- 2003/12/17 J ABLUNDEL 3325486 Commented out the code that creates--
1868 -- the spaces in the line for return --
1869 -- distance and return time. Now the --
1870 -- download file only contains the --
1871 -- origin and destination columns --
1872 -- --
1873 -- 2004/03/05 J ABLUNDEL 3487060 Need to check that region values --
1874 -- exist for translated values for --
1875 -- the return from c_get_region_values--
1876 -- from WSH_REGIONS_V (changed to the --
1877 -- view, VL) from the TL table --
1878 -- --
1879 -- -------------------------------------------------------------------------- --
1880 PROCEDURE CREATE_DWNLD_FILE(p_origin_route IN PLS_INTEGER,
1881 p_destination_route IN PLS_INTEGER,
1882 p_origin_id IN NUMBER,
1883 p_destination_id IN NUMBER,
1884 p_template_id IN NUMBER,
1885 p_file_name IN VARCHAR2,
1886 p_file_extension IN VARCHAR2,
1887 p_region_type IN NUMBER,
1888 p_distance_profile IN VARCHAR2,
1889 p_user_debug_flag IN VARCHAR2,
1890 x_return_message OUT NOCOPY VARCHAR2,
1891 x_return_status OUT NOCOPY VARCHAR2) IS
1892
1893 l_ctr PLS_INTEGER;
1894 l_cd PLS_INTEGER;
1895 l_error_text VARCHAR2(2000);
1896 l_download_dir VARCHAR2(100);
1897 l_origin_col_id NUMBER;
1898 l_ret_time_col_id NUMBER;
1899 l_ret_dist_col_id NUMBER;
1900 l_use_length VARCHAR2(1);
1901 l_ret_dist_length VARCHAR2(1);
1902 l_ret_time_length VARCHAR2(1);
1903 l_ret_dist_yn VARCHAR2(1);
1904 l_ret_time_yn VARCHAR2(1);
1905 l_origin_seq PLS_INTEGER;
1906 l_dest_seq PLS_INTEGER;
1907 l_ret_dist_seq PLS_INTEGER;
1908 l_ret_time_seq PLS_INTEGER;
1909 l_col1_type VARCHAR2(30);
1910 l_col2_type VARCHAR2(30);
1911 l_idx1 PLS_INTEGER;
1912 l_idx2 PLS_INTEGER;
1913 l_col1_start_pos NUMBER;
1914 l_col1_length NUMBER;
1915 l_col_length NUMBER;
1916 l_col1_delim VARCHAR2(10);
1917 l_col2_start_pos NUMBER;
1918 l_col2_length NUMBER;
1919 l_col2_delim VARCHAR2(10);
1920 l_idx PLS_INTEGER;
1921 l_found PLS_INTEGER;
1922 l_distance_count NUMBER;
1923 l_origin_route NUMBER;
1924 l_destination_route NUMBER;
1925 l_language VARCHAR2(4);
1926 l_od_idx PLS_INTEGER;
1927 l_code_idx PLS_INTEGER;
1928 l_str_length NUMBER;
1929 l_dest_attr_string VARCHAR2(2000);
1930 l_origin_attr_string VARCHAR2(2000);
1931 l_target_file utl_file.file_type;
1932 l_origin_reg_id NUMBER;
1933 l_dest_reg_id NUMBER;
1934 l_download_file_id NUMBER;
1935 l_line_ctr PLS_INTEGER;
1936 l_rmve_ctr PLS_INTEGER;
1937 l_file_string VARCHAR2(4000);
1938 l_ret_dist_string VARCHAR2(4000);
1939 l_ret_time_string VARCHAR2(4000);
1940 l_dest_attr_value VARCHAR2(4000);
1941 l_origin_attr_value VARCHAR2(4000);
1942 l_dest_attr_found VARCHAR2(1);
1943 l_orig_attr_found VARCHAR2(1);
1944 l_match_flag VARCHAR2(1);
1945 l_check_region_type NUMBER;
1946 l_orig_reg_ctr PLS_INTEGER;
1947 l_parent_loop PLS_INTEGER;
1948 l_reg_ctr PLS_INTEGER;
1949 l_preg_ctr PLS_INTEGER;
1950 l_cont_ctr PLS_INTEGER;
1951
1952 l_orig_denorm_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1953 l_dest_denorm_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1954 l_od_pair_tab FTE_DIST_DWNLD_PKG.fte_distd_od_pair_tab;
1955
1956 l_col_tab FTE_DIST_DWNLD_PKG.fte_distd_col_tab;
1957 l_tmplt_col_tab FTE_DIST_DWNLD_PKG.fte_distd_tmplt_col_tab;
1958 l_attr_tab FTE_DIST_DWNLD_PKG.fte_distd_attr_tab;
1959
1960 l_column_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1961 l_column_type_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_code_table;
1962 l_column_start_position_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1963 l_column_length_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1964 l_column_delimiter_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_flag_table;
1965 l_column_sequence_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1966
1967 l_odattr_column_attr_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1968 l_odattr_code_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_code_table;
1969 l_odattr_attr_delimiter_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_flag_table;
1970 l_odattr_space_padding_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1971 l_odattr_sequence_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1972 l_odattr_length_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1973
1974 l_retdis_column_attr_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1975 l_retdis_code_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_code_table;
1976 l_retdis_attr_delimiter_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_flag_table;
1977 l_retdis_space_padding_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1978 l_retdis_sequence_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1979 l_retdis_length_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1980
1981 l_rettim_column_attr_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1982 l_rettim_code_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_code_table;
1983 l_rettim_attr_delimiter_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_flag_table;
1984 l_rettim_space_padding_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1985 l_rettim_sequence_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1986 l_rettim_length_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1987
1988 l_origin_location_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1989 l_origin_region_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1990 l_elig_locs_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1991 l_destination_location_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1992 l_destination_region_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1993
1994 l_reg_region_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
1995 l_reg_postal_code_from_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_code_table;
1996 l_reg_city_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_char60_table;
1997 l_reg_state_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_char60_table;
1998 l_reg_county_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_char60_table;
1999 l_reg_country_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_char80_table;
2000
2001 l_reg_table FTE_DIST_DWNLD_PKG.fte_distd_region_tab;
2002 l_reg_code_region_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2003 l_reg_code_state_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_char10_table;
2004 l_reg_code_country_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_char10_table;
2005
2006 l_reg_code_table FTE_DIST_DWNLD_PKG.fte_distd_reg_code_tab;
2007
2008 l_remove_idx_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2009 l_distance_tab_origin_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2010 l_distance_tab_distance_id_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2011 l_orig_location_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2012 l_orig_region_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2013 l_dest_location_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2014 l_dest_region_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2015
2016 l_match_locations_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2017 l_sub_regions_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2018 l_sub_region_type_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2019 l_match_region_id FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2020 l_parent_sub_regions_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2021 l_parent_regions_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2022 l_sub_par_cont_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2023 l_tmp_orig_reg FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2024 l_tmp_orig_loc FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2025 l_tmp_dest_reg FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2026 l_tmp_dest_loc FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2027
2028 l_od_check_tab FTE_DIST_DWNLD_PKG.fte_distd_tmp_num_table;
2029
2030 cursor c_get_distance_tab_pairs IS
2031 select flm.origin_id,
2032 flm.destination_id
2033 from fte_location_mileages flm;
2034
2035
2036 cursor c_get_region_codes(cp_region_id NUMBER) IS
2037 select wr.region_id,
2038 wr.state_code,
2039 wr.country_code
2040 from wsh_regions wr
2041 where region_id = cp_region_id;
2042
2043
2044 -- [BUG:3487060]
2045 -- Replace the base table query with a query on the view and remove county
2046 --
2047 -- cursor c_get_region_values(cp_region_id NUMBER,
2048 -- cp_language VARCHAR2) IS
2049 -- select wrtl.region_id,
2050 -- wrtl.postal_code_from,
2051 -- wrtl.city,
2052 -- wrtl.state,
2053 -- wrtl.county,
2054 -- wrtl.country
2055 -- from wsh_regions_tl wrtl
2056 -- where wrtl.region_id = cp_region_id
2057 -- and wrtl.language = cp_language;
2058 --
2059
2060
2061 cursor c_get_region_values(cp_region_id NUMBER) IS
2062 select wrtl.region_id,
2063 wrtl.postal_code_from,
2064 wrtl.city,
2065 wrtl.state,
2066 null,
2067 wrtl.country
2068 from wsh_regions_v wrtl
2069 where wrtl.region_id = cp_region_id;
2070
2071
2072
2073
2074
2075 cursor c_check_distance_table IS
2076 select count(origin_id)
2077 from fte_location_mileages;
2078
2079
2080 cursor c_get_all_elig_fac(cp_enabled_flag_y VARCHAR2) IS
2081 select flp.location_id
2082 from fte_location_parameters flp
2083 where flp.include_mileage_flag = cp_enabled_flag_y;
2084
2085
2086 cursor c_get_region_for_facility(cp_location_id NUMBER,
2087 cp_region_type NUMBER) IS
2088 select wrl.location_id,
2089 wrl.region_id
2090 from wsh_region_locations wrl
2091 where wrl.location_id = cp_location_id
2092 and wrl.region_type = cp_region_type;
2093
2094
2095
2096 cursor c_get_template_columns(cp_template_id NUMBER) IS
2097 select fmtc.column_id,
2098 fmtc.column_type,
2099 fmtc.start_position,
2100 fmtc.length,
2101 fmtc.column_delimiter,
2102 fmtc.column_sequence
2103 from fte_mile_template_columns fmtc
2104 where fmtc.template_id = cp_template_id
2105 order by fmtc.column_sequence;
2106
2107
2108 cursor c_get_col_attrs(cp_column_id NUMBER,
2109 cp_enabled_flag VARCHAR2) IS
2110 select fmca.column_attribute_id,
2111 fmca.code,
2112 fmca.attribute_delimiter,
2113 fmca.space_padding,
2114 fmca.sequence_id,
2115 fmca.length
2116 from fte_mile_column_attributes fmca
2117 where fmca.column_id = cp_column_id
2118 and fmca.enabled_flag = cp_enabled_flag
2119 order by fmca.sequence_id;
2120
2121
2122 cursor c_get_col_attr(cp_column_id NUMBER,
2123 cp_enabled_flag VARCHAR2,
2124 cp_seq_id NUMBER) IS
2125 select fmca.column_attribute_id,
2126 fmca.code,
2127 fmca.attribute_delimiter,
2128 fmca.space_padding,
2129 fmca.sequence_id,
2130 fmca.length
2131 from fte_mile_column_attributes fmca
2132 where fmca.column_id = cp_column_id
2133 and fmca.enabled_flag = cp_enabled_flag
2134 and fmca.sequence_id = cp_seq_id;
2135
2136
2137 cursor c_check_region_type(cp_region_id NUMBER) IS
2138 select region_type
2139 from wsh_regions
2140 where region_id = cp_region_id;
2141
2142 cursor c_get_sub_regions(cp_region_id NUMBER) IS
2143 select region_id,
2144 region_type
2145 from wsh_regions
2146 where parent_region_id = cp_region_id;
2147
2148 cursor c_check_matching_locs(cp_region_id NUMBER) IS
2149 select location_id
2150 from wsh_region_locations
2151 where region_id = cp_region_id;
2152
2153 cursor c_check_mile_flag(cp_location_id NUMBER) IS
2154 select include_mileage_flag
2155 from fte_location_parameters
2156 where location_id = cp_location_id;
2157
2158
2159
2160 FTE_DIST_NO_COLS_FOR_TEMPLATE EXCEPTION;
2161 FTE_DIST_NO_RET_COLS EXCEPTION;
2162 FTE_DIST_NO_OD_COLS EXCEPTION;
2163 FTE_DIST_NO_RET_ATTRS EXCEPTION;
2164 FTE_DIST_NO_OD_ATTRS EXCEPTION;
2165 FTE_DIST_INVALID_START_POS EXCEPTION;
2166 FTE_DIST_INVALID_COL_LENGTHS EXCEPTION;
2167 FTE_DIST_NO_LOC_REG_MAP EXCEPTION;
2168 FTE_DIST_NO_LOC_SPEC_R1 EXCEPTION;
2169 FTE_DIST_NO_REGION_SPEC_R2 EXCEPTION;
2170 FTE_DIST_NO_ELIG_FACILI_R3 EXCEPTION;
2171 FTE_DIST_NO_DWNLD_DIR EXCEPTION;
2172 FTE_DIST_COL_ZERO_START EXCEPTION;
2173 FTE_DIST_NO_RET_LENGTH EXCEPTION;
2174 FTE_DIST_NO_RET_ATTR EXCEPTION;
2175 FTE_DIST_RET_DIST_INV_LENGTH EXCEPTION;
2176 FTE_DIST_RET_DIST_INV_START EXCEPTION;
2177 FTE_DIST_RET_TIME_INV_LENGTH EXCEPTION;
2178 FTE_DIST_RET_TIME_INV_START EXCEPTION;
2179 FTE_DIST_NO_OD_PAIRS EXCEPTION;
2180 FTE_DIST_NO_MATCH_REGIONS_FND EXCEPTION;
2181 FTE_DIST_INV_REGION_LOW EXCEPTION;
2182 FTE_DIST_NO_ORIG_REG_VALS EXCEPTION;
2183 FTE_DIST_NO_DEST_REG_VALS EXCEPTION;
2184
2185
2186
2187 --
2188 -- Local Debug Variable Definitions
2189 --
2190 l_debug_on BOOLEAN;
2191 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CREATE_DWNLD_FILE';
2192
2193 l_spacer VARCHAR2(2000);
2194
2195 BEGIN
2196
2197 --
2198 -- set the debug flag
2199 --
2200 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2201 --
2202 IF l_debug_on IS NULL THEN
2203 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2204 END IF;
2205 IF (p_user_debug_flag = 'Y') THEN
2206 l_debug_on := TRUE;
2207 END IF;
2208 --
2209 --
2210 -- Debug Statements
2211 --
2212 IF l_debug_on THEN
2213 WSH_DEBUG_SV.push(l_module_name);
2214 WSH_DEBUG_SV.logmsg(l_module_name,'-------CREATE_DWNLD_FILE-------');
2215 WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
2216 WSH_DEBUG_SV.log(l_module_name,'p_origin_route',p_origin_route);
2217 WSH_DEBUG_SV.log(l_module_name,'p_destination_route',p_destination_route);
2218 WSH_DEBUG_SV.log(l_module_name,'p_origin_id',p_origin_id);
2219 WSH_DEBUG_SV.log(l_module_name,'p_destination_id',p_destination_id);
2220 WSH_DEBUG_SV.log(l_module_name,'p_template_id',p_template_id);
2221 WSH_DEBUG_SV.log(l_module_name,'p_file_name',p_file_name);
2222 WSH_DEBUG_SV.log(l_module_name,'p_file_extension',p_file_extension);
2223 WSH_DEBUG_SV.log(l_module_name,'p_region_type',p_region_type);
2224 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
2225 END IF;
2226
2227 --
2228 -- Set the return flags for the start of the procedure
2229 --
2230 x_return_message := null;
2231 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2232
2233
2234 --
2235 -- reset the tables
2236 --
2237 l_column_id_tab.DELETE;
2238 l_column_type_tab.DELETE;
2239 l_column_start_position_tab.DELETE;
2240 l_column_length_tab.DELETE;
2241 l_column_delimiter_tab.DELETE;
2242 l_column_sequence_id_tab.DELETE;
2243
2244 l_odattr_column_attr_id_tab.DELETE;
2245 l_odattr_code_tab.DELETE;
2246 l_odattr_attr_delimiter_tab.DELETE;
2247 l_odattr_space_padding_tab.DELETE;
2248 l_odattr_sequence_id_tab.DELETE;
2249 l_odattr_length_tab.DELETE;
2250
2251 l_retdis_column_attr_id_tab.DELETE;
2252 l_retdis_code_tab.DELETE;
2253 l_retdis_attr_delimiter_tab.DELETE;
2254 l_retdis_space_padding_tab.DELETE;
2255 l_retdis_sequence_id_tab.DELETE;
2256 l_retdis_length_tab.DELETE;
2257
2258 l_rettim_column_attr_id_tab.DELETE;
2259 l_rettim_code_tab.DELETE;
2260 l_rettim_attr_delimiter_tab.DELETE;
2261 l_rettim_space_padding_tab.DELETE;
2262 l_rettim_sequence_id_tab.DELETE;
2263 l_rettim_length_tab.DELETE;
2264
2265
2266 --
2267 -- Set the language for getting info from wsh_regions_v -- [BUG:3487060] not used anymore but keep for debug messages
2268 --
2269 l_language := nvl(userenv('LANG'),'US');
2270
2271 IF l_debug_on THEN
2272 WSH_DEBUG_SV.log(l_module_name,'l_language := ',l_language);
2273 END IF;
2274 --
2275 -- Get the download_directory
2276 --
2277 IF l_debug_on THEN
2278 WSH_DEBUG_SV.logmsg(l_module_name,'gettting the bulkload directory from FTE_BULKLOAD_DIR profile ');
2279 END IF;
2280 fnd_profile.get('FTE_BULKLOAD_DIR',l_download_dir);
2281
2282 IF l_debug_on THEN
2283 WSH_DEBUG_SV.log(l_module_name,'FTE_BULKLOAD_DIR,l_download_dir = ',l_download_dir);
2284 END IF;
2285
2286 IF (l_download_dir is null) THEN
2287 RAISE FTE_DIST_NO_DWNLD_DIR;
2288 --
2289 -- Debug Statements
2290 --
2291 IF l_debug_on THEN
2292 WSH_DEBUG_SV.pop(l_module_name);
2293 END IF;
2294 --
2295 RETURN;
2296 END IF;
2297
2298
2299 --
2300 -- Get the template column and attribute data
2301 --
2302 IF l_debug_on THEN
2303 WSH_DEBUG_SV.log(l_module_name,'OPENING CURSOR c_get_template_columns with template_id',p_template_id);
2304 END IF;
2305
2306
2307 OPEN c_get_template_columns(p_template_id);
2308 FETCH c_get_template_columns BULK COLLECT INTO
2309 l_column_id_tab,
2310 l_column_type_tab,
2311 l_column_start_position_tab,
2312 l_column_length_tab,
2313 l_column_delimiter_tab,
2314 l_column_sequence_id_tab;
2315 CLOSE c_get_template_columns;
2316
2317 IF (l_column_id_tab.COUNT = 0) THEN
2318 --
2319 -- No columns for the template were found
2320 -- Raise an error
2321 --
2322 IF l_debug_on THEN
2323 WSH_DEBUG_SV.logmsg(l_module_name,'No columns for the template were found raise FTE_DIST_NO_COLS_FOR_TEMPLATE');
2324 END IF;
2325 RAISE FTE_DIST_NO_COLS_FOR_TEMPLATE;
2326 --
2327 -- Debug Statements
2328 --
2329 IF l_debug_on THEN
2330 WSH_DEBUG_SV.pop(l_module_name);
2331 END IF;
2332 --
2333 RETURN;
2334 END IF;
2335
2336
2337 IF l_debug_on THEN
2338 WSH_DEBUG_SV.logmsg(l_module_name,'Retrieved column tables ---------------------');
2339 WSH_DEBUG_SV.log(l_module_name,'l_column_id_tab.COUNT = ',l_column_id_tab.COUNT);
2340 FOR ddd IN l_column_delimiter_tab.FIRST..l_column_delimiter_tab.LAST LOOP
2341 WSH_DEBUG_SV.log(l_module_name,'l_column_id_tab(ddd) = ',l_column_id_tab(ddd));
2342 END LOOP;
2343 END IF;
2344
2345
2346
2347 --
2348 -- Now get the attribute information for the columns
2349 --
2350 FOR aaa IN l_column_type_tab.FIRST..l_column_type_tab.LAST LOOP
2351 IF (l_column_type_tab(aaa) = g_origin_col_name) THEN
2352 --
2353 -- OD attributes are only stored with the origin id
2354 --
2355 l_origin_col_id := l_column_id_tab(aaa);
2356 IF (l_origin_col_id is null) THEN
2357 --
2358 -- No OD column exists - raise an error
2359 --
2360 RAISE FTE_DIST_NO_OD_COLS;
2361 --
2362 -- Debug Statements
2363 --
2364 IF l_debug_on THEN
2365 WSH_DEBUG_SV.pop(l_module_name);
2366 END IF;
2367 --
2368 RETURN;
2369 END IF;
2370 END IF;
2371
2372 IF (l_column_type_tab(aaa) = g_ret_dist_col_name) THEN
2373 --
2374 -- Return Distance col id
2375 --
2376 l_ret_dist_col_id := l_column_id_tab(aaa);
2377
2378 IF (l_column_length_tab(aaa) is null) THEN
2379 l_ret_dist_length := 'N';
2380 ELSE
2381 l_ret_dist_length := 'Y';
2382 END IF;
2383 END IF;
2384
2385 IF (l_column_type_tab(aaa) = g_ret_time_col_name) THEN
2386 --
2387 -- Return Time col id
2388 --
2389 l_ret_time_col_id := l_column_id_tab(aaa);
2390 IF (l_column_length_tab(aaa) is null) THEN
2391 l_ret_time_length := 'N';
2392 ELSE
2393 l_ret_time_length := 'Y';
2394 END IF;
2395 END IF;
2396 END LOOP;
2397
2398
2399 IF ((l_ret_dist_col_id is null) AND
2400 (l_ret_time_col_id is null)) THEN
2401 --
2402 -- There must be at least one return column
2403 -- but there isnt - rasie an error
2404 --
2405 RAISE FTE_DIST_NO_RET_COLS;
2406 --
2407 -- Debug Statements
2408 --
2409 IF l_debug_on THEN
2410 WSH_DEBUG_SV.pop(l_module_name);
2411 END IF;
2412 --
2413 RETURN;
2414 END IF;
2415
2416 IF ((l_ret_time_length = 'N') AND
2417 (l_ret_dist_length = 'N')) THEN
2418 --
2419 -- There are no lengths specified for the return columns
2420 -- Raise an error
2421 --
2422 RAISE FTE_DIST_NO_RET_LENGTH;
2423 --
2424 -- Debug Statements
2425 --
2426 IF l_debug_on THEN
2427 WSH_DEBUG_SV.pop(l_module_name);
2428 END IF;
2429 --
2430 RETURN;
2431 END IF;
2432
2433 IF l_debug_on THEN
2434 WSH_DEBUG_SV.logmsg(l_module_name,'OPENING CURSOR c_get_col_attrs');
2435 WSH_DEBUG_SV.log(l_module_name,'PARAMETER l_origin_col_id= ',l_origin_col_id);
2436 WSH_DEBUG_SV.log(l_module_name,'PARAMETER g_y_flag = ',g_y_flag);
2437 END IF;
2438 --
2439 -- Get the OD attributes
2440 --
2441 OPEN c_get_col_attrs(l_origin_col_id,
2442 g_y_flag);
2443 FETCH c_get_col_attrs BULK COLLECT INTO
2444 l_odattr_column_attr_id_tab,
2445 l_odattr_code_tab,
2446 l_odattr_attr_delimiter_tab,
2447 l_odattr_space_padding_tab,
2448 l_odattr_sequence_id_tab,
2449 l_odattr_length_tab;
2450 CLOSE c_get_col_attrs;
2451
2452
2453 --
2454 -- Get the return distance attribute
2455 --
2456 OPEN c_get_col_attrs(l_ret_dist_col_id,
2457 g_y_flag);
2458 FETCH c_get_col_attrs BULK COLLECT INTO
2459 l_retdis_column_attr_id_tab,
2460 l_retdis_code_tab,
2461 l_retdis_attr_delimiter_tab,
2462 l_retdis_space_padding_tab,
2463 l_retdis_sequence_id_tab,
2464 l_retdis_length_tab;
2465 CLOSE c_get_col_attrs;
2466
2467 --
2468 -- Get the return time attribute
2469 --
2470 OPEN c_get_col_attrs(l_ret_time_col_id,
2471 g_y_flag);
2472 FETCH c_get_col_attrs BULK COLLECT INTO
2473 l_rettim_column_attr_id_tab,
2474 l_rettim_code_tab,
2475 l_rettim_attr_delimiter_tab,
2476 l_rettim_space_padding_tab,
2477 l_rettim_sequence_id_tab,
2478 l_rettim_length_tab;
2479 CLOSE c_get_col_attrs;
2480
2481
2482 IF ((l_rettim_column_attr_id_tab.COUNT = 0) AND
2483 (l_retdis_column_attr_id_tab.COUNT = 0)) THEN
2484 l_ret_dist_yn := 'N';
2485 l_ret_time_yn := 'N';
2486 --
2487 -- Neither of the return attributes have been enabled
2488 -- at least 1 must be
2489 --
2490 RAISE FTE_DIST_NO_RET_ATTR;
2491 --
2492 -- Debug Statements
2493 --
2494 IF l_debug_on THEN
2495 WSH_DEBUG_SV.pop(l_module_name);
2496 END IF;
2497 --
2498 RETURN;
2499 ELSIF ((l_rettim_column_attr_id_tab.COUNT > 0) AND
2500 (l_retdis_column_attr_id_tab.COUNT = 0)) THEN
2501 l_ret_time_yn := 'Y';
2502 l_ret_dist_yn := 'N';
2503 ELSIF ((l_rettim_column_attr_id_tab.COUNT = 0) AND
2504 (l_retdis_column_attr_id_tab.COUNT > 0)) THEN
2505 l_ret_time_yn := 'N';
2506 l_ret_dist_yn := 'Y';
2507 ELSIF ((l_rettim_column_attr_id_tab.COUNT > 0) AND
2508 (l_retdis_column_attr_id_tab.COUNT > 0)) THEN
2509 l_ret_time_yn := 'Y';
2510 l_ret_dist_yn := 'Y';
2511 END IF;
2512
2513
2514
2515 IF l_debug_on THEN
2516 WSH_DEBUG_SV.logmsg(l_module_name,'Setting all sequences for columns - if their sequence is null');
2517 END IF;
2518 --
2519 -- make sure all the columns have sequences
2520 --
2521 FOR colseq IN l_column_id_tab.FIRST..l_column_id_tab.LAST LOOP
2522 IF (l_column_type_tab(colseq) = g_origin_col_name) THEN
2523 IF (l_column_sequence_id_tab(colseq) is null) THEN
2524 l_column_sequence_id_tab(colseq) := 1;
2525 END IF;
2526 ELSIF (l_column_type_tab(colseq) = g_dest_col_name) THEN
2527 IF (l_column_sequence_id_tab(colseq) is null) THEN
2528 l_column_sequence_id_tab(colseq) := 2;
2529 END IF;
2530 ELSIF (l_column_type_tab(colseq) = g_ret_dist_col_name) THEN
2531 IF (l_column_sequence_id_tab(colseq) is null) THEN
2532 l_column_sequence_id_tab(colseq) := 3;
2533 END IF;
2534 ELSIF (l_column_type_tab(colseq) = g_ret_time_col_name) THEN
2535 IF (l_column_sequence_id_tab(colseq) is null) THEN
2536 l_column_sequence_id_tab(colseq) := 4;
2537 END IF;
2538 END IF;
2539 END LOOP;
2540
2541
2542 IF l_debug_on THEN
2543 WSH_DEBUG_SV.logmsg(l_module_name,'Put the columns in a new table in the correct sequence, 1 to 4');
2544 END IF;
2545 --
2546 -- Put the columns in a new table in the correct sequence, 1 to 4
2547 --
2548 l_found := 0;
2549 FOR jjj in 1..4 LOOP
2550 FOR fff IN l_column_id_tab.FIRST..l_column_id_tab.LAST LOOP
2551 IF (l_column_sequence_id_tab(fff) = jjj) THEN
2552
2553 IF ((l_column_type_tab(fff) = g_ret_dist_col_name) AND
2554 (l_ret_dist_yn = 'Y')) THEN
2555
2556 l_found := l_found + 1;
2557 l_col_tab(l_found).seq := l_found;
2558 l_col_tab(l_found).code := l_column_type_tab(fff);
2559 IF ((l_column_length_tab(fff) is not null) AND
2560 (l_column_length_tab(fff) > 0)) THEN
2561 l_col_tab(l_found).length := l_column_length_tab(fff);
2562 ELSE
2563 --
2564 -- Return column is enabled but there is no length
2565 -- raise an error
2566 --
2567 RAISE FTE_DIST_RET_DIST_INV_LENGTH;
2568 RETURN;
2569 END IF;
2570 l_col_tab(l_found).delim := l_column_delimiter_tab(fff);
2571 IF ((l_column_start_position_tab(fff) is not null) AND
2572 (l_column_start_position_tab(fff) > 0)) THEN
2573 l_col_tab(l_found).start_pos := l_column_start_position_tab(fff);
2574 ELSE
2575 --
2576 -- Return column is enabled but there is no start pos
2577 --
2578 RAISE FTE_DIST_RET_DIST_INV_START;
2579 RETURN;
2580 END IF;
2581 l_col_tab(l_found).id := l_column_id_tab(fff);
2582 l_ret_dist_seq := l_col_tab(l_found).seq;
2583 END IF;
2584
2585 IF ((l_column_type_tab(fff) =g_ret_time_col_name) AND
2586 (l_ret_time_yn = 'Y')) THEN
2587
2588 l_found := l_found + 1;
2589 l_col_tab(l_found).seq := l_found;
2590 l_col_tab(l_found).code := l_column_type_tab(fff);
2591 IF ((l_column_length_tab(fff) is not null) AND
2592 (l_column_length_tab(fff) > 0)) THEN
2593 l_col_tab(l_found).length := l_column_length_tab(fff);
2594 ELSE
2595 --
2596 -- Return column is enabled but there is no length
2597 -- raise an error
2598 --
2599 RAISE FTE_DIST_RET_TIME_INV_LENGTH;
2600 RETURN;
2601 END IF;
2602
2603 l_col_tab(l_found).delim := l_column_delimiter_tab(fff);
2604
2605 IF ((l_column_start_position_tab(fff) is not null) AND
2606 (l_column_start_position_tab(fff) > 0)) THEN
2607 l_col_tab(l_found).start_pos := l_column_start_position_tab(fff);
2608 ELSE
2609 --
2610 -- Return column is enabled but there is no start pos
2611 --
2612 RAISE FTE_DIST_RET_TIME_INV_START;
2613 RETURN;
2614 END IF;
2615 l_col_tab(l_found).id := l_column_id_tab(fff);
2616 l_ret_time_seq := l_col_tab(l_found).seq;
2617 END IF;
2618
2619 IF (l_column_type_tab(fff) = g_origin_col_name) THEN
2620
2621 l_found := l_found + 1;
2622 l_col_tab(l_found).seq := l_found;
2623 l_col_tab(l_found).code := l_column_type_tab(fff);
2624 l_col_tab(l_found).length := l_column_length_tab(fff);
2625 l_col_tab(l_found).delim := l_column_delimiter_tab(fff);
2626 l_col_tab(l_found).start_pos := l_column_start_position_tab(fff);
2627 l_col_tab(l_found).id := l_column_id_tab(fff);
2628 l_origin_seq := l_col_tab(l_found).seq;
2629 END IF;
2630
2631 IF (l_column_type_tab(fff) = g_dest_col_name) THEN
2632 l_found := l_found + 1;
2633 l_col_tab(l_found).seq := l_found;
2634 l_col_tab(l_found).code := l_column_type_tab(fff);
2635 l_col_tab(l_found).length := l_column_length_tab(fff);
2636 l_col_tab(l_found).delim := l_column_delimiter_tab(fff);
2637 l_col_tab(l_found).start_pos := l_column_start_position_tab(fff);
2638 l_col_tab(l_found).id := l_column_id_tab(fff);
2639 l_dest_seq := l_col_tab(l_found).seq;
2640 END IF;
2641
2642 END IF;
2643 END LOOP;
2644 END LOOP;
2645
2646
2647 IF l_debug_on THEN
2648 WSH_DEBUG_SV.logmsg(l_module_name,'Setting all sequences for attributes - if their sequence is null');
2649 END IF;
2650 --
2651 -- make sure all the attributes have sequences
2652 --
2653 FOR attrs IN l_odattr_column_attr_id_tab.FIRST..l_odattr_column_attr_id_tab.LAST LOOP
2654 IF (l_odattr_code_tab(attrs) = g_postal_code_name) THEN
2655 IF (l_odattr_sequence_id_tab(attrs) is null) THEN
2656 l_odattr_sequence_id_tab(attrs) := 1;
2657 END IF;
2658 ELSIF (l_odattr_code_tab(attrs) = g_city_code_name) THEN
2659 IF (l_odattr_sequence_id_tab(attrs) is null) THEN
2660 l_odattr_sequence_id_tab(attrs) := 2;
2661 END IF;
2662 ELSIF (l_odattr_code_tab(attrs) = g_state_code_name) THEN
2663 IF (l_odattr_sequence_id_tab(attrs) is null) THEN
2664 l_odattr_sequence_id_tab(attrs) := 3;
2665 END IF;
2666 ELSIF (l_odattr_code_tab(attrs) = g_county_code_name) THEN
2667 IF (l_odattr_sequence_id_tab(attrs) is null) THEN
2668 l_odattr_sequence_id_tab(attrs) := 4;
2669 END IF;
2670 ELSIF (l_odattr_code_tab(attrs) = g_country_code_name) THEN
2671 IF (l_odattr_sequence_id_tab(attrs) is null) THEN
2672 l_odattr_sequence_id_tab(attrs) := 5;
2673 END IF;
2674 END IF;
2675 END LOOP;
2676
2677
2678 IF l_debug_on THEN
2679 WSH_DEBUG_SV.logmsg(l_module_name,'Put the attributes in a new table in the correct sequence, 1 to 5');
2680 END IF;
2681 --
2682 -- Put the attibutes in a new table in the correct sequence, 1 to 5
2683 --
2684 l_found := 0;
2685 FOR jjj in 1..5 LOOP
2686 FOR fff IN l_odattr_column_attr_id_tab.FIRST..l_odattr_column_attr_id_tab.LAST LOOP
2687 IF (l_odattr_sequence_id_tab(fff) = jjj) THEN
2688 l_found := l_found + 1;
2689 l_attr_tab(l_found).seq := l_found;
2690 l_attr_tab(l_found).code := l_odattr_code_tab(fff);
2691 l_attr_tab(l_found).length := l_odattr_length_tab(fff);
2692 l_col_length := l_col_length + nvl(l_odattr_length_tab(fff),0);
2693 l_attr_tab(l_found).delim := l_odattr_attr_delimiter_tab(fff);
2694 IF (l_found < l_odattr_column_attr_id_tab.COUNT) THEN
2695 IF (l_odattr_attr_delimiter_tab(fff) is not null) THEN
2696 l_col_length := l_col_length + 1;
2697 END IF;
2698 ELSE
2699 l_attr_tab(l_found).delim := null;
2700 END IF;
2701 END IF;
2702 END LOOP;
2703 END LOOP;
2704
2705
2706 --
2707 -- Now we have to determine the start positions and the lengths
2708 -- for the download we only care about the ORIGIN and DESTIANTION columns
2709 -- and the OD attributes
2710 --
2711 -- a column has a start pos and a length and a delimiter
2712 /* ******************************************************** */
2713 FOR ccc IN l_col_tab.FIRST..l_col_tab.LAST LOOP
2714 IF (l_col_tab(ccc).code = g_origin_col_name) THEN
2715 l_origin_seq := l_col_tab(ccc).seq;
2716 ELSIF (l_col_tab(ccc).code = g_dest_col_name) THEN
2717 l_dest_seq := l_col_tab(ccc).seq;
2718 ELSIF (l_col_tab(ccc).code = g_ret_dist_col_name) THEN
2719 l_ret_dist_seq := l_col_tab(ccc).seq;
2720 ELSIF (l_col_tab(ccc).code = g_ret_time_col_name) THEN
2721 l_ret_time_seq := l_col_tab(ccc).seq;
2722 END IF;
2723
2724 IF (l_col_tab(ccc).seq = 1) THEN
2725 --
2726 IF (l_col_tab(ccc).start_pos is null) THEN
2727 -- set start pos to 1
2728 l_col1_start_pos := 1;
2729 l_col_tab(ccc).start_pos := 1;
2730 END IF;
2731
2732 IF ((l_col_tab(ccc).code = g_origin_col_name) OR
2733 (l_col_tab(ccc).code = g_dest_col_name)) THEN
2734 IF (l_col_tab(ccc).length is not null) THEN
2735 l_col1_length := l_col_tab(ccc).length;
2736 l_use_length := 'C';
2737 ELSE
2738 --
2739 -- use the attribute length
2740 --
2741 l_col1_length := l_col_length;
2742 l_col_tab(ccc).length := l_col_length;
2743 l_use_length := 'A';
2744 END IF;
2745 END IF;
2746
2747
2748 IF (l_col_tab(ccc).delim is not null) THEN
2749 l_col1_delim := l_col_tab(ccc).delim;
2750 ELSE
2751 l_col1_delim := null;
2752 l_col_tab(ccc).delim := null;
2753 END IF;
2754 ELSIF (l_col_tab(ccc).seq = 2) THEN
2755 IF (l_col_tab(ccc).start_pos is null) THEN
2756 l_col_tab(ccc).start_pos := l_col_tab(1).start_pos + l_col_tab(1).length;
2757 IF (l_col_tab(1).delim is not null) THEN
2758 l_col_tab(ccc).start_pos := l_col_tab(ccc).start_pos + 1;
2759 END IF;
2760 END IF;
2761
2762 IF ((l_col_tab(ccc).code = g_origin_col_name) OR
2763 (l_col_tab(ccc).code = g_dest_col_name)) THEN
2764 IF (l_use_length is null) THEN
2765 IF (l_col_tab(ccc).length is not null) THEN
2766 l_use_length := 'C';
2767 ELSE
2768 l_col_tab(ccc).length := l_col_length;
2769 l_use_length := 'A';
2770 END IF;
2771 ELSIF (l_use_length = 'A') THEN
2772 l_col_tab(ccc).length := l_col_length;
2773 ELSIF (l_use_length = 'C') THEN
2774 IF (l_col_tab(ccc).length is null) THEN
2775 IF (l_col_tab(ccc).code = g_origin_col_name) THEN
2776 l_col_tab(ccc).length := l_col_tab(l_dest_seq).length;
2777 ELSIF (l_col_tab(ccc).code = g_dest_col_name) THEN
2778 l_col_tab(ccc).length := l_col_tab(l_origin_seq).length;
2779 END IF;
2780 END IF;
2781 END IF;
2782 END IF;
2783
2784 ELSIF (l_col_tab(ccc).seq = 3) THEN
2785 IF (l_col_tab(ccc).start_pos is null) THEN
2786 l_col_tab(ccc).start_pos := l_col_tab(2).start_pos + l_col_tab(2).length;
2787 IF (l_col_tab(2).delim is not null) THEN
2788 l_col_tab(ccc).start_pos := l_col_tab(ccc).start_pos + 1;
2789 END IF;
2790 END IF;
2791
2792 IF ((l_col_tab(ccc).code = g_origin_col_name) OR
2793 (l_col_tab(ccc).code = g_dest_col_name)) THEN
2794 IF (l_use_length is null) THEN
2795 IF (l_col_tab(ccc).length is not null) THEN
2796 l_use_length := 'C';
2797 ELSE
2798 l_col_tab(ccc).length := l_col_length;
2799 l_use_length := 'A';
2800 END IF;
2801 ELSIF (l_use_length = 'A') THEN
2802 l_col_tab(ccc).length := l_col_length;
2803 ELSIF (l_use_length = 'C') THEN
2804 IF (l_col_tab(ccc).length is null) THEN
2805 IF (l_col_tab(ccc).code = g_origin_col_name) THEN
2806 l_col_tab(ccc).length := l_col_tab(l_dest_seq).length;
2807 ELSIF (l_col_tab(ccc).code = g_dest_col_name) THEN
2808 l_col_tab(ccc).length := l_col_tab(l_origin_seq).length;
2809 END IF;
2810 END IF;
2811 END IF;
2812 END IF;
2813
2814 ELSIF (l_col_tab(ccc).seq = 4) THEN
2815 IF (l_col_tab(ccc).start_pos is null) THEN
2816 l_col_tab(ccc).start_pos := l_col_tab(3).start_pos + l_col_tab(3).length;
2817 IF (l_col_tab(3).delim is not null) THEN
2818 l_col_tab(ccc).start_pos := l_col_tab(ccc).start_pos + 1;
2819 END IF;
2820 END IF;
2821
2822 IF ((l_col_tab(ccc).code = g_origin_col_name) OR
2823 (l_col_tab(ccc).code = g_dest_col_name)) THEN
2824 IF (l_use_length is null) THEN
2825 IF (l_col_tab(ccc).length is not null) THEN
2826 l_use_length := 'C';
2827 ELSE
2828 l_col_tab(ccc).length := l_col_length;
2829 l_use_length := 'A';
2830 END IF;
2831 ELSIF (l_use_length = 'A') THEN
2832 l_col_tab(ccc).length := l_col_length;
2833 ELSIF (l_use_length = 'C') THEN
2834 IF (l_col_tab(ccc).length is null) THEN
2835 IF (l_col_tab(ccc).code = g_origin_col_name) THEN
2836 l_col_tab(ccc).length := l_col_tab(l_dest_seq).length;
2837 ELSIF (l_col_tab(ccc).code = g_dest_col_name) THEN
2838 l_col_tab(ccc).length := l_col_tab(l_origin_seq).length;
2839 END IF;
2840 END IF;
2841 END IF;
2842 END IF;
2843 END IF;
2844 END LOOP;
2845 /* ******************************************************* */
2846 IF l_debug_on THEN
2847 WSH_DEBUG_SV.logmsg(l_module_name,'---------- L_COL_TAB --------------');
2848 FOR zzz in l_col_tab.FIRST..l_col_tab.LAST LOOP
2849 WSH_DEBUG_SV.log(l_module_name,'idx = ',zzz);
2850 WSH_DEBUG_SV.log(l_module_name,'l_col_tab(zzz).seq = ',l_col_tab(zzz).seq);
2851 WSH_DEBUG_SV.log(l_module_name,'l_col_tab(zzz).code = ',l_col_tab(zzz).code);
2852 WSH_DEBUG_SV.log(l_module_name,'l_col_tab(zzz).start_pos = ',l_col_tab(zzz).start_pos);
2853 WSH_DEBUG_SV.log(l_module_name,'l_col_tab(zzz).length = ',l_col_tab(zzz).length);
2854 WSH_DEBUG_SV.log(l_module_name,'l_col_tab(zzz).delim = ',l_col_tab(zzz).delim);
2855 WSH_DEBUG_SV.log(l_module_name,'l_col_tab(zzz).id = ',l_col_tab(zzz).id);
2856 END LOOP;
2857 END IF;
2858
2859
2860 /*
2861
2862
2863
2864 IF (l_origin_seq < l_dest_seq) THEN
2865 -- origin col is first
2866 l_col1_type := 'ORIGIN';
2867 l_col2_type := 'DESTINATION';
2868 l_idx1 := l_origin_seq;
2869 l_idx2 := l_dest_seq;
2870 ELSE
2871 l_col1_type := 'DESTINATION';
2872 l_col2_type := 'ORIGIN';
2873 l_idx1 := l_dest_seq;
2874 l_idx2 := l_origin_seq;
2875 END IF;
2876
2877 --
2878 -- Do Column 1
2879 --
2880 IF (l_col_tab(l_idx1).start_pos is not null) THEN
2881 l_col1_start_pos := l_col_tab(l_idx1).start_pos;
2882 ELSE
2883 -- set start pos to 1
2884 l_col1_start_pos := 1;
2885 END IF;
2886
2887 IF (l_col_tab(l_idx1).length is not null) THEN
2888 l_col1_length := l_col_tab(l_idx1).length;
2889 l_use_length := 'C';
2890 ELSE
2891 --
2892 -- use the attribute length
2893 --
2894 l_col1_length := l_col_length;
2895 l_use_length := 'A';
2896 END IF;
2897
2898 IF (l_col_tab(l_idx1).delim is not null) THEN
2899 l_col1_delim := l_col_tab(l_idx1).delim;
2900 ELSE
2901 l_col1_delim := null;
2902 END IF;
2903
2904 --
2905 -- now do col2
2906 --
2907 IF (l_col_tab(l_idx2).start_pos is not null) THEN
2908 l_col2_start_pos := l_col_tab(l_idx2).start_pos;
2909 ELSE
2910 -- set start pos to the start1 pos + start 1 length
2911 l_col2_start_pos := l_col1_start_pos + l_col1_length;
2912 IF (l_col1_delim is not null) THEN
2913 l_col2_start_pos := l_col2_start_pos + 1;
2914 END IF;
2915 END IF;
2916
2917 IF (l_col_tab(l_idx2).length is not null) THEN
2918 l_col2_length := l_col_tab(l_idx2).length;
2919 ELSE
2920 --
2921 -- use the col1 length
2922 --
2923 l_col2_length := l_col1_length;
2924 END IF;
2925
2926 IF (l_col_tab(l_idx2).delim is not null) THEN
2927 l_col2_delim := l_col_tab(l_idx2).delim;
2928 ELSE
2929 l_col2_delim := null;
2930 END IF;
2931
2932 --
2933 -- Now we should have all the information about the columns and
2934 -- the atttrributes we need to validate the positions and lengths
2935 --
2936 IF (l_col1_start_pos >= l_col2_start_pos) THEN
2937 IF l_debug_on THEN
2938 WSH_DEBUG_SV.logmsg(l_module_name,'l_col1_start_pos >= l_col2_start_pos');
2939 WSH_DEBUG_SV.log(l_module_name,'l_col1_start_pos = ',l_col1_start_pos);
2940 WSH_DEBUG_SV.log(l_module_name,'l_col2_start_pos = ',l_col2_start_pos);
2941 WSH_DEBUG_SV.logmsg(l_module_name,'RAISE FTE_DIST_INVALID_START_POS');
2942 END IF;
2943
2944 RAISE FTE_DIST_INVALID_START_POS;
2945 --
2946 -- Debug Statements
2947 --
2948 IF l_debug_on THEN
2949 WSH_DEBUG_SV.pop(l_module_name);
2950 END IF;
2951 --
2952 RETURN;
2953 END IF;
2954
2955 IF ((l_col1_start_pos <= 0) OR
2956 (l_col2_start_pos <= 0)) THEN
2957 IF l_debug_on THEN
2958 WSH_DEBUG_SV.logmsg(l_module_name,'l_col1_start_pos <= 0');
2959 WSH_DEBUG_SV.log(l_module_name,'l_col1_start_pos = ',l_col1_start_pos);
2960 WSH_DEBUG_SV.logmsg(l_module_name,'l_col2_start_pos <= 0');
2961 WSH_DEBUG_SV.log(l_module_name,'l_col2_start_pos = ',l_col2_start_pos);
2962 WSH_DEBUG_SV.logmsg(l_module_name,'RAISE FTE_DIST_COL_ZERO_START');
2963 END IF;
2964 RAISE FTE_DIST_COL_ZERO_START;
2965 --
2966 -- Debug Statements
2967 --
2968 IF l_debug_on THEN
2969 WSH_DEBUG_SV.pop(l_module_name);
2970 END IF;
2971 --
2972 RETURN;
2973 END IF;
2974
2975
2976 IF ((l_col1_length <= 0) OR
2977 (l_col2_length <= 0)) THEN
2978 IF l_debug_on THEN
2979 WSH_DEBUG_SV.logmsg(l_module_name,'l_col1_length <= 0 and so is col2_length');
2980 WSH_DEBUG_SV.log(l_module_name,'l_col1_length = ',l_col1_length);
2981 WSH_DEBUG_SV.log(l_module_name,'l_col2_length = ',l_col2_length);
2982 WSH_DEBUG_SV.logmsg(l_module_name,'RAISE FTE_DIST_INVALID_COL_LENGTHS');
2983 END IF;
2984 RAISE FTE_DIST_INVALID_COL_LENGTHS;
2985 --
2986 -- Debug Statements
2987 --
2988 IF l_debug_on THEN
2989 WSH_DEBUG_SV.pop(l_module_name);
2990 END IF;
2991 --
2992 RETURN;
2993 END IF;
2994 */
2995 /*
2996
2997 --
2998 -- Now we should have all the information to build the template
2999 -- Store the information in a table
3000 --
3001 l_idx := 0;
3002
3003 l_idx := l_idx + 1;
3004 l_tmplt_col_tab(l_idx).seq := l_idx;
3005 l_tmplt_col_tab(l_idx).type := l_col1_type;
3006 l_tmplt_col_tab(l_idx).start_pos := l_col1_start_pos;
3007 l_tmplt_col_tab(l_idx).length := l_col1_length;
3008 l_tmplt_col_tab(l_idx).delim := l_col1_delim;
3009 l_idx := l_idx + 1;
3010 l_tmplt_col_tab(l_idx).seq := l_idx;
3011 l_tmplt_col_tab(l_idx).type := l_col2_type;
3012 l_tmplt_col_tab(l_idx).start_pos := l_col2_start_pos;
3013 l_tmplt_col_tab(l_idx).length := l_col2_length;
3014 l_tmplt_col_tab(l_idx).delim := l_col2_delim;
3015
3016 */
3017
3018
3019 IF l_debug_on THEN
3020 /*
3021 WSH_DEBUG_SV.logmsg(l_module_name,'---------- COLUMNS --------------');
3022 FOR zzz in l_tmplt_col_tab.FIRST..l_tmplt_col_tab.LAST LOOP
3023 WSH_DEBUG_SV.log(l_module_name,'idx = ',zzz);
3024 WSH_DEBUG_SV.log(l_module_name,'l_tmplt_col_tab(zzz).seq = ',l_tmplt_col_tab(zzz).seq);
3025 WSH_DEBUG_SV.log(l_module_name,'l_tmplt_col_tab(zzz).type = ',l_tmplt_col_tab(zzz).type);
3026 WSH_DEBUG_SV.log(l_module_name,'l_tmplt_col_tab(zzz).start_pos = ',l_tmplt_col_tab(zzz).start_pos);
3027 WSH_DEBUG_SV.log(l_module_name,'l_tmplt_col_tab(zzz).length = ',l_tmplt_col_tab(zzz).length);
3028 WSH_DEBUG_SV.log(l_module_name,'l_tmplt_col_tab(zzz).delim = ',l_tmplt_col_tab(zzz).delim);
3029 END LOOP;
3030 */
3031 WSH_DEBUG_SV.logmsg(l_module_name,'------------ --------------');
3032 WSH_DEBUG_SV.logmsg(l_module_name,'------------ ATTRIBUTES ------------');
3033 FOR vvv IN l_attr_tab.FIRST..l_attr_tab.LAST LOOP
3034 WSH_DEBUG_SV.log(l_module_name,'l_attr_tab(vvv).seq := ',l_attr_tab(vvv).seq);
3035 WSH_DEBUG_SV.log(l_module_name,'l_attr_tab(vvv).code = ',l_attr_tab(vvv).code);
3036 WSH_DEBUG_SV.log(l_module_name,'l_attr_tab(vvv).length = ',l_attr_tab(vvv).length);
3037 WSH_DEBUG_SV.log(l_module_name,'l_attr_tab(vvv).delim = ',l_attr_tab(vvv).delim);
3038 END LOOP;
3039 WSH_DEBUG_SV.logmsg(l_module_name,'------------ --------------');
3040 WSH_DEBUG_SV.log(l_module_name,'l_use_length := '||l_use_length);
3041 END IF;
3042
3043
3044 --
3045 -- Now we have a table of column and attribute data
3046 -- lets get the data and populate the file
3047 --
3048 l_origin_route := p_origin_route;
3049 l_destination_route := p_destination_route;
3050
3051 IF ((l_origin_route = 4) OR
3052 (l_destination_route = 4)) THEN
3053 IF l_debug_on THEN
3054 WSH_DEBUG_SV.logmsg(l_module_name,'origin route and destination route is 4 - big one');
3055 END IF;
3056 --
3057 -- The call is for all eligible facilities without distance or transit time information
3058 -- This means all OD pairs that do not have a record in the distance table
3059 -- in order to do this we have to get all OD pairs of origin and destination
3060 -- do route 3 for both origin and destination
3061 -- format them into an OD pairing table
3062 -- get all OD pairs from the distance table
3063 -- compare each table to each other
3064 --
3065 --
3066 -- If the distance table is completely empty we can do a straight route 3 for both
3067 -- origin and destination
3068 --
3069 IF l_debug_on THEN
3070 WSH_DEBUG_SV.logmsg(l_module_name,'OPENING CURSOR c_check_distance_table');
3071 END IF;
3072
3073 OPEN c_check_distance_table;
3074 FETCH c_check_distance_table INTO l_distance_count;
3075 CLOSE c_check_distance_table;
3076
3077 IF (l_distance_count = 0) THEN
3078 --
3079 -- the table is empty - go route 3
3080 --
3081 IF l_debug_on THEN
3082 WSH_DEBUG_SV.logmsg(l_module_name,'No distance records exist - go route 3');
3083 END IF;
3084 l_origin_route := 3;
3085 l_destination_route := 3;
3086 END IF;
3087 END IF;
3088
3089
3090 IF (l_origin_route = 1) THEN
3091
3092 IF l_debug_on THEN
3093 WSH_DEBUG_SV.logmsg(l_module_name,'Origin Route = 1');
3094 END IF;
3095 IF (p_origin_id is null) THEN
3096 IF l_debug_on THEN
3097 WSH_DEBUG_SV.logmsg(l_module_name,'Origin id is null - RAISE FTE_DIST_NO_LOC_SPEC_R1');
3098 END IF;
3099
3100 RAISE FTE_DIST_NO_LOC_SPEC_R1;
3101 --
3102 -- Debug Statements
3103 --
3104 IF l_debug_on THEN
3105 WSH_DEBUG_SV.pop(l_module_name);
3106 END IF;
3107 --
3108 RETURN;
3109 END IF;
3110
3111 --
3112 -- The origin is a facility p_origin_id is a location
3113 -- get the region with the region type
3114 --
3115 IF l_debug_on THEN
3116 WSH_DEBUG_SV.logmsg(l_module_name,'Opening cursor c_get_region_for_facility');
3117 WSH_DEBUG_SV.log(l_module_name,'PARAMETER p_origin_id = ',p_origin_id);
3118 WSH_DEBUG_SV.log(l_module_name,'PARAMETER p_region_type = ',p_region_type);
3119 END IF;
3120
3121 OPEN c_get_region_for_facility(p_origin_id,
3122 p_region_type);
3123 FETCH c_get_region_for_facility BULK COLLECT INTO
3124 l_origin_location_id,
3125 l_origin_region_id;
3126 CLOSE c_get_region_for_facility;
3127
3128 IF l_debug_on THEN
3129 WSH_DEBUG_SV.logmsg(l_module_name,'l_origin_location_id.COUNT = ',l_origin_location_id.COUNT);
3130 IF (l_origin_location_id.COUNT > 0) THEN
3131 FOR ggg IN l_origin_location_id.FIRST..l_origin_location_id.LAST LOOP
3132 WSH_DEBUG_SV.log(l_module_name,'l_origin_location_id(ggg) = ',l_origin_location_id(ggg));
3133 WSH_DEBUG_SV.log(l_module_name,'l_origin_region_id(ggg) = ',l_origin_region_id(ggg));
3134 END LOOP;
3135 END IF;
3136 END IF;
3137
3138
3139 IF (l_origin_location_id.COUNT = 0) THEN
3140 IF l_debug_on THEN
3141 WSH_DEBUG_SV.logmsg(l_module_name,'l_origin_location_id.COUNT = 0 RAISE FTE_DIST_NO_LOC_REG_MAP');
3142 END IF;
3143
3144 RAISE FTE_DIST_NO_LOC_REG_MAP;
3145 --
3146 -- Debug Statements
3147 --
3148 IF l_debug_on THEN
3149 WSH_DEBUG_SV.pop(l_module_name);
3150 END IF;
3151 --
3152 RETURN;
3153 END IF;
3154
3155 ELSIF (l_origin_route = 2) THEN
3156 IF l_debug_on THEN
3157 WSH_DEBUG_SV.logmsg(l_module_name,'l_origin_route = 2');
3158 END IF;
3159 --
3160 -- The origin is a region p_origin_id is a region
3161 -- get the region
3162 --
3163 IF (p_origin_id is null) THEN
3164 IF l_debug_on THEN
3165 WSH_DEBUG_SV.logmsg(l_module_name,'p_origin_id is null - RAISE FTE_DIST_NO_REGION_SPEC_R2');
3166 END IF;
3167
3168 RAISE FTE_DIST_NO_REGION_SPEC_R2;
3169 --
3170 -- Debug Statements
3171 --
3172 IF l_debug_on THEN
3173 WSH_DEBUG_SV.pop(l_module_name);
3174 END IF;
3175 --
3176 RETURN;
3177 END IF;
3178
3179 -- -------------------------------------------------------------------
3180 -- New Code if the region is a parent of the profile level get the
3181 -- children of the parent and go down until the regions of the profile
3182 -- level are found
3183 -- -------------------------------------------------------------------
3184
3185 l_reg_ctr := 0;
3186 l_check_region_type := null;
3187 --
3188 -- Check that the region matches with the distance level
3189 --
3190 IF l_debug_on THEN
3191 WSH_DEBUG_SV.log(l_module_name,'p_origin_id = ',p_origin_id);
3192 WSH_DEBUG_SV.log(l_module_name,'p_region_type = ',p_region_type);
3193 END IF;
3194
3195
3196 OPEN c_check_region_type(p_origin_id);
3197 FETCH c_check_region_type INTO l_check_region_type;
3198 CLOSE c_check_region_type;
3199
3200 IF l_debug_on THEN
3201 WSH_DEBUG_SV.logmsg(l_module_name,'after c_check_region_type');
3202 WSH_DEBUG_SV.log(l_module_name,'l_check_region_type = ',l_check_region_type);
3203 END IF;
3204
3205
3206 IF (l_check_region_type is not null) THEN
3207 IF (l_check_region_type = p_region_type) THEN
3208
3209 l_origin_location_id(1) := null;
3210 l_origin_region_id(1) := p_origin_id;
3211
3212 ELSIF (l_check_region_type < p_region_type) THEN
3213 --
3214 -- The region passed in is a parent of the profile level
3215 -- get all sub levels that match the profile and check that
3216 -- there is at least one eligible facility
3217 --
3218 l_parent_regions_tab(1) := p_origin_id;
3219 l_parent_loop := 0;
3220
3221 LOOP
3222
3223 IF l_debug_on THEN
3224 WSH_DEBUG_SV.logmsg(l_module_name,'IN FIRST LOOP');
3225 END IF;
3226
3227 l_sub_regions_tab.DELETE;
3228 l_sub_region_type_tab.DELETE;
3229
3230 IF (l_parent_regions_tab.COUNT > 0) THEN
3231 l_parent_loop := l_parent_loop + 1;
3232
3233 FOR kkk IN l_parent_regions_tab.FIRST..l_parent_regions_tab.LAST LOOP
3234
3235 IF (l_parent_regions_tab.EXISTS(kkk)) THEN
3236
3237 OPEN c_get_sub_regions(l_parent_regions_tab(kkk));
3238 FETCH c_get_sub_regions BULK COLLECT INTO
3239 l_sub_regions_tab,
3240 l_sub_region_type_tab;
3241 CLOSE c_get_sub_regions;
3242
3243 -- l_reg_ctr := 0;
3244 l_preg_ctr := 0;
3245
3246 IF (l_sub_regions_tab.COUNT > 0) THEN
3247
3248 FOR pppp IN l_sub_regions_tab.FIRST..l_sub_regions_tab.LAST LOOP
3249 IF (l_sub_regions_tab.EXISTS(pppp)) THEN
3250
3251 IF (l_sub_region_type_tab(pppp) = p_region_type) THEN
3252 --
3253 -- region types match put them in a table
3254 --
3255 l_reg_ctr := l_reg_ctr + 1;
3256 l_origin_location_id(l_sub_regions_tab(pppp)) := null;
3257 l_match_region_id(l_sub_regions_tab(pppp)) := l_sub_regions_tab(pppp);
3258
3259 ELSIF (l_sub_region_type_tab(pppp) < p_region_type) THEN
3260 --
3261 -- These are still parents
3262 --
3263 l_preg_ctr := l_preg_ctr + 1;
3264 l_parent_sub_regions_tab(l_preg_ctr) := l_sub_regions_tab(pppp);
3265
3266 END IF;
3267 END IF;
3268
3269 END LOOP;
3270
3271 END IF;
3272
3273 l_cont_ctr := 0;
3274
3275 IF (l_parent_sub_regions_tab.COUNT > 0) THEN
3276 FOR nnn IN l_parent_sub_regions_tab.FIRST..l_parent_sub_regions_tab.LAST LOOP
3277 IF (l_parent_sub_regions_tab.EXISTS(nnn)) THEN
3278 l_cont_ctr := l_sub_par_cont_tab.COUNT + 1;
3279 l_sub_par_cont_tab(l_cont_ctr) := l_parent_sub_regions_tab(nnn);
3280 END IF;
3281 END LOOP;
3282 l_parent_sub_regions_tab.DELETE;
3283 -- ELSE
3284 --
3285 -- All matching sub regions for parent have been found
3286 --
3287 END IF;
3288 END IF;
3289 END LOOP;
3290
3291 --
3292 -- Loop for parent (original region) has completed
3293 -- we may have some more parents
3294 --
3295 IF (l_sub_par_cont_tab.COUNT = 0) THEN
3296 EXIT;
3297 ELSE
3298 l_parent_regions_tab := l_sub_par_cont_tab;
3299 l_sub_par_cont_tab.DELETE;
3300 END IF;
3301
3302 END IF;
3303
3304 END LOOP;
3305
3306 --
3307 -- now we have a complete table of sub regions that match the profile level
3308 --
3309 -- For each region we have to check that there are eligible facilities for
3310 -- those regions
3311 --
3312 l_orig_reg_ctr := 0;
3313
3314 FOR uuuu IN l_match_region_id.FIRST..l_match_region_id.LAST LOOP
3315 l_match_flag := 'N';
3316 l_match_locations_tab.DELETE;
3317
3318 IF (l_match_region_id.EXISTS(uuuu)) THEN
3319
3320 IF l_debug_on THEN
3321 WSH_DEBUG_SV.log(l_module_name,'l_match_region_id(uuuu) = ',l_match_region_id(uuuu));
3322 END IF;
3323
3324
3325 OPEN c_check_matching_locs(l_match_region_id(uuuu));
3326 FETCH c_check_matching_locs BULK COLLECT INTO
3327 l_match_locations_tab;
3328 CLOSE c_check_matching_locs;
3329
3330
3331 IF (l_match_locations_tab.COUNT > 0) THEN
3332
3333 FOR vvvv IN l_match_locations_tab.FIRST..l_match_locations_tab.LAST LOOP
3334 IF (l_match_locations_tab.EXISTS(vvvv)) THEN
3335
3336 OPEN c_check_mile_flag(l_match_locations_tab(vvvv));
3337 FETCH c_check_mile_flag INTO
3338 l_match_flag;
3339 CLOSE c_check_mile_flag;
3340
3341 IF ((l_match_flag is not null) AND
3342 (l_match_flag = 'Y')) THEN
3343 -- This region has an eligible location
3344
3345 l_orig_reg_ctr := l_orig_reg_ctr + 1;
3346 l_origin_region_id(l_match_region_id(uuuu)) := l_match_region_id(uuuu);
3347 l_origin_location_id(l_match_region_id(uuuu)) := null;
3348
3349 END IF;
3350 END IF;
3351 END LOOP;
3352 -- ELSE
3353 -- region has no facilities/locations
3354 -- null;
3355 END IF;
3356 END IF;
3357 END LOOP;
3358
3359 ELSIF (l_check_region_type > p_region_type) THEN
3360 RAISE FTE_DIST_INV_REGION_LOW;
3361 RETURN;
3362 END IF;
3363 END IF;
3364
3365
3366 IF (l_origin_region_id.COUNT <= 0) THEN
3367
3368 RAISE FTE_DIST_NO_MATCH_REGIONS_FND;
3369 RETURN;
3370 END IF;
3371
3372
3373
3374 IF (l_origin_region_id.COUNT > 0) THEN
3375 l_ctr := 0;
3376 FOR ggg IN l_origin_region_id.FIRST..l_origin_region_id.LAST LOOP
3377 IF (l_origin_region_id.EXISTS(ggg)) THEN
3378 l_ctr := l_ctr + 1;
3379 l_tmp_orig_reg(l_ctr) := l_origin_region_id(ggg);
3380 l_tmp_orig_loc(l_ctr) := null;
3381 END IF;
3382 END LOOP;
3383 l_origin_region_id.DELETE;
3384 l_origin_location_id.DELETE;
3385 l_origin_region_id := l_tmp_orig_reg;
3386 l_origin_location_id := l_tmp_orig_loc;
3387 l_tmp_orig_reg.DELETE;
3388 l_tmp_orig_loc.DELETE;
3389 END IF;
3390
3391 -- ---------------------------------------------------------------------
3392 -- End of new code
3393 -- ---------------------------------------------------------------------
3394
3395
3396
3397 IF l_debug_on THEN
3398 WSH_DEBUG_SV.log(l_module_name,'l_origin_region_id.COUNT = ',l_origin_region_id.COUNT);
3399 IF (l_origin_region_id.COUNT > 0) THEN
3400 FOR ggg IN l_origin_region_id.FIRST..l_origin_region_id.LAST LOOP
3401 WSH_DEBUG_SV.log(l_module_name,'l_origin_location_id(ggg) = ',l_origin_location_id(ggg));
3402 WSH_DEBUG_SV.log(l_module_name,'l_origin_region_id(ggg) = ',l_origin_region_id(ggg));
3403 END LOOP;
3404 END IF;
3405 END IF;
3406
3407 ELSIF ((l_origin_route = 3) OR
3408 (l_origin_route = 4)) THEN
3409 --
3410 -- The call is for all eligible facilities for origin
3411 --
3412 IF l_debug_on THEN
3413 WSH_DEBUG_SV.logmsg(l_module_name,'l_origin_route = 3');
3414 WSH_DEBUG_SV.logmsg(l_module_name,'Opening cursor c_get_all_elig_fac');
3415 WSH_DEBUG_SV.log(l_module_name,'PARAMETER g_y_flag = ',g_y_flag);
3416 END IF;
3417
3418 OPEN c_get_all_elig_fac(g_y_flag);
3419 FETCH c_get_all_elig_fac BULK COLLECT INTO
3420 l_elig_locs_id_tab;
3421 CLOSE c_get_all_elig_fac;
3422
3423 IF (l_elig_locs_id_tab.COUNT = 0) THEN
3424 --
3425 -- There are no eligible facilities
3426 --
3427 IF l_debug_on THEN
3428 WSH_DEBUG_SV.logmsg(l_module_name,'There are no eligible facilities RAISE FTE_DIST_NO_ELIG_FACILI_R3');
3429 END IF;
3430
3431 RAISE FTE_DIST_NO_ELIG_FACILI_R3;
3432 --
3433 -- Debug Statements
3434 --
3435 IF l_debug_on THEN
3436 WSH_DEBUG_SV.pop(l_module_name);
3437 END IF;
3438 --
3439 RETURN;
3440 END IF;
3441
3442
3443
3444 FOR ppp IN l_elig_locs_id_tab.FIRST..l_elig_locs_id_tab.LAST LOOP
3445 --
3446 -- get the regions for the locations
3447 --
3448 IF l_debug_on THEN
3449 WSH_DEBUG_SV.logmsg(l_module_name,'There are eligible facilities - get the regions for the locations - Opening cursor c_get_region_for_facility');
3450 WSH_DEBUG_SV.log(l_module_name,'PARAMETERS l_elig_locs_id_tab(ppp) = ',l_elig_locs_id_tab(ppp));
3451 WSH_DEBUG_SV.log(l_module_name,'PARAMETERS p_region_type = ',p_region_type);
3452 END IF;
3453
3454
3455 OPEN c_get_region_for_facility(l_elig_locs_id_tab(ppp),
3456 p_region_type);
3457 FETCH c_get_region_for_facility BULK COLLECT INTO
3458 l_orig_location_id,
3459 l_orig_region_id;
3460 CLOSE c_get_region_for_facility;
3461
3462 IF (l_orig_location_id.COUNT > 0) THEN
3463 FOR ioi IN l_orig_location_id.FIRST..l_orig_location_id.LAST LOOP
3464 l_cd := l_origin_location_id.COUNT + 1;
3465 l_origin_location_id(l_cd) := l_orig_location_id(ioi);
3466 l_origin_region_id(l_cd) := l_orig_region_id(ioi);
3467 END LOOP;
3468 END IF;
3469 END LOOP;
3470
3471 IF (l_origin_location_id.COUNT = 0) THEN
3472 IF l_debug_on THEN
3473 WSH_DEBUG_SV.logmsg(l_module_name,'l_origin_location_id.COUNT = 0 - RAISE FTE_DIST_NO_LOC_REG_MAP');
3474 END IF;
3475
3476 RAISE FTE_DIST_NO_LOC_REG_MAP;
3477 --
3478 -- Debug Statements
3479 --
3480 IF l_debug_on THEN
3481 WSH_DEBUG_SV.pop(l_module_name);
3482 END IF;
3483 --
3484 RETURN;
3485 END IF;
3486
3487 IF l_debug_on THEN
3488 WSH_DEBUG_SV.log(l_module_name,'l_origin_location_id.COUNT = ',l_origin_location_id.COUNT);
3489 IF (l_origin_location_id.COUNT > 0) THEN
3490 FOR ppp IN l_origin_location_id.FIRST..l_origin_location_id.LAST LOOP
3491 WSH_DEBUG_SV.log(l_module_name,'l_origin_location_id(ppp) = ',l_origin_location_id(ppp));
3492 WSH_DEBUG_SV.log(l_module_name,'l_origin_region_id(ppp) = ',l_origin_region_id(ppp));
3493 END LOOP;
3494 END IF;
3495 END IF;
3496 END IF;
3497
3498
3499 IF l_debug_on THEN
3500 WSH_DEBUG_SV.logmsg(l_module_name,'Get the destination regions');
3501 END IF;
3502 --
3503 -- Get the destination regions
3504 --
3505 IF (l_destination_route = 1) THEN
3506
3507 IF l_debug_on THEN
3508 WSH_DEBUG_SV.logmsg(l_module_name,'l_destination_route = 1');
3509 END IF;
3510
3511 IF (p_destination_id is null) THEN
3512 IF l_debug_on THEN
3513 WSH_DEBUG_SV.logmsg(l_module_name,'p_destination_id is null - RAISE FTE_DIST_NO_LOC_SPEC_R1');
3514 END IF;
3515
3516 RAISE FTE_DIST_NO_LOC_SPEC_R1;
3517 --
3518 -- Debug Statements
3519 --
3520 IF l_debug_on THEN
3521 WSH_DEBUG_SV.pop(l_module_name);
3522 END IF;
3523 --
3524 RETURN;
3525 END IF;
3526
3527
3528 IF l_debug_on THEN
3529 WSH_DEBUG_SV.logmsg(l_module_name,'The destination is a facility p_destination_id is a location get the region with the region type');
3530 WSH_DEBUG_SV.logmsg(l_module_name,'OPENING cursor c_get_region_for_facility');
3531 WSH_DEBUG_SV.log(l_module_name,'PARAMETERS p_destination_id = ',p_destination_id);
3532 WSH_DEBUG_SV.log(l_module_name,'PARAMETERS p_region_type = ',p_region_type);
3533 END IF;
3534
3535 --
3536 -- The destination is a facility p_destination_id is a location
3537 -- get the region with the region type
3538 --
3539 OPEN c_get_region_for_facility(p_destination_id,
3540 p_region_type);
3541 FETCH c_get_region_for_facility BULK COLLECT INTO
3542 l_destination_location_id,
3543 l_destination_region_id;
3544 CLOSE c_get_region_for_facility;
3545
3546 IF l_debug_on THEN
3547 WSH_DEBUG_SV.log(l_module_name,'l_destination_location_id.COUNT =',l_destination_location_id.COUNT);
3548 IF (l_destination_location_id.COUNT > 0) THEN
3549 FOR gggd IN l_destination_location_id.FIRST..l_destination_location_id.LAST LOOP
3550 WSH_DEBUG_SV.log(l_module_name,'l_destination_location_id(gggd) = ',l_destination_location_id(gggd));
3551 WSH_DEBUG_SV.log(l_module_name,'l_destination_region_id(gggd) = ',l_destination_region_id(gggd));
3552 END LOOP;
3553 END IF;
3554 END IF;
3555
3556
3557 IF (l_destination_location_id.COUNT = 0) THEN
3558 IF l_debug_on THEN
3559 WSH_DEBUG_SV.logmsg(l_module_name,'l_destination_location_id.COUNT = 0 - RAISE FTE_DIST_NO_LOC_REG_MAP');
3560 END IF;
3561
3562 RAISE FTE_DIST_NO_LOC_REG_MAP;
3563 --
3564 -- Debug Statements
3565 --
3566 IF l_debug_on THEN
3567 WSH_DEBUG_SV.pop(l_module_name);
3568 END IF;
3569 --
3570 RETURN;
3571 END IF;
3572
3573 ELSIF (l_destination_route = 2) THEN
3574 IF l_debug_on THEN
3575 WSH_DEBUG_SV.logmsg(l_module_name,'l_destination_route = 2');
3576 END IF;
3577
3578 --
3579 -- The destination is a region p_destination_id is a region
3580 -- get the region
3581 --
3582 IF (p_destination_id is null) THEN
3583 IF l_debug_on THEN
3584 WSH_DEBUG_SV.logmsg(l_module_name,'p_destination_id is null - RAISE FTE_DIST_NO_REGION_SPEC_R2');
3585 END IF;
3586
3587 RAISE FTE_DIST_NO_REGION_SPEC_R2;
3588 --
3589 -- Debug Statements
3590 --
3591 IF l_debug_on THEN
3592 WSH_DEBUG_SV.pop(l_module_name);
3593 END IF;
3594 --
3595 RETURN;
3596 END IF;
3597
3598 -- -------------------------------------------------------------------
3599 -- New Code if the region is a parent of the profile level get the
3600 -- children of the parent and go down until the regions of the profile
3601 -- level are found (Destination)
3602 -- -------------------------------------------------------------------
3603 l_reg_ctr := 0;
3604 l_check_region_type := null;
3605 l_parent_regions_tab.DELETE;
3606 l_match_region_id.DELETE;
3607 l_sub_par_cont_tab.DELETE;
3608 l_match_locations_tab.DELETE;
3609 l_destination_region_id.DELETE;
3610 l_destination_location_id.DELETE;
3611 l_tmp_dest_reg.DELETE;
3612 l_tmp_dest_loc.DELETE;
3613
3614 --
3615 -- Check that the region matches with the distance level
3616 --
3617 IF l_debug_on THEN
3618 WSH_DEBUG_SV.log(l_module_name,'p_destination_id = ',p_destination_id);
3619 WSH_DEBUG_SV.log(l_module_name,'p_region_type = ',p_region_type);
3620 END IF;
3621
3622
3623 OPEN c_check_region_type(p_destination_id);
3624 FETCH c_check_region_type INTO l_check_region_type;
3625 CLOSE c_check_region_type;
3626
3627 IF l_debug_on THEN
3628 WSH_DEBUG_SV.logmsg(l_module_name,'after c_check_region_type (destination)');
3629 WSH_DEBUG_SV.log(l_module_name,'l_check_region_type = ',l_check_region_type);
3630 END IF;
3631
3632
3633 IF (l_check_region_type is not null) THEN
3634 IF (l_check_region_type = p_region_type) THEN
3635
3636 l_destination_location_id(1) := null;
3637 l_destination_region_id(1) := p_destination_id;
3638
3639 ELSIF (l_check_region_type < p_region_type) THEN
3640 --
3641 -- The region passed in is a parent of the profile level
3642 -- get all sub levels that match the profile and check that
3643 -- there is at least one eligible facility
3644 --
3645 l_parent_regions_tab(1) := p_destination_id;
3646 l_parent_loop := 0;
3647
3648 LOOP
3649
3650 IF l_debug_on THEN
3651 WSH_DEBUG_SV.logmsg(l_module_name,'IN FIRST LOOP');
3652 END IF;
3653
3654 l_sub_regions_tab.DELETE;
3655 l_sub_region_type_tab.DELETE;
3656
3657 IF (l_parent_regions_tab.COUNT > 0) THEN
3658 l_parent_loop := l_parent_loop + 1;
3659
3660 FOR kkk IN l_parent_regions_tab.FIRST..l_parent_regions_tab.LAST LOOP
3661
3662 IF (l_parent_regions_tab.EXISTS(kkk)) THEN
3663
3664 OPEN c_get_sub_regions(l_parent_regions_tab(kkk));
3665 FETCH c_get_sub_regions BULK COLLECT INTO
3666 l_sub_regions_tab,
3667 l_sub_region_type_tab;
3668 CLOSE c_get_sub_regions;
3669
3670 -- l_reg_ctr := 0;
3671 l_preg_ctr := 0;
3672
3673 IF (l_sub_regions_tab.COUNT > 0) THEN
3674
3675 FOR pppp IN l_sub_regions_tab.FIRST..l_sub_regions_tab.LAST LOOP
3676 IF (l_sub_regions_tab.EXISTS(pppp)) THEN
3677
3678 IF (l_sub_region_type_tab(pppp) = p_region_type) THEN
3679 --
3680 -- region types match put them in a table
3681 --
3682 l_reg_ctr := l_reg_ctr + 1;
3683 l_destination_location_id(l_sub_regions_tab(pppp)) := null;
3684 l_match_region_id(l_sub_regions_tab(pppp)) := l_sub_regions_tab(pppp);
3685 ELSIF (l_sub_region_type_tab(pppp) < p_region_type) THEN
3686 --
3687 -- These are still parents
3688 --
3689 l_preg_ctr := l_preg_ctr + 1;
3690 l_parent_sub_regions_tab(l_preg_ctr) := l_sub_regions_tab(pppp);
3691
3692 END IF;
3693 END IF;
3694
3695 END LOOP;
3696
3697 END IF;
3698
3699 l_cont_ctr := 0;
3700
3701 IF (l_parent_sub_regions_tab.COUNT > 0) THEN
3702 FOR nnn IN l_parent_sub_regions_tab.FIRST..l_parent_sub_regions_tab.LAST LOOP
3703 IF (l_parent_sub_regions_tab.EXISTS(nnn)) THEN
3704 l_cont_ctr := l_sub_par_cont_tab.COUNT + 1;
3705 l_sub_par_cont_tab(l_cont_ctr) := l_parent_sub_regions_tab(nnn);
3706 END IF;
3707 END LOOP;
3708 l_parent_sub_regions_tab.DELETE;
3709 -- ELSE
3710 --
3711 -- All matching sub regions for parent have been found
3712 --
3713 END IF;
3714 END IF;
3715 END LOOP;
3716
3717 --
3718 -- Loop for parent (original region) has completed
3719 -- we may have some more parents
3720 --
3721 IF (l_sub_par_cont_tab.COUNT = 0) THEN
3722 EXIT;
3723 ELSE
3724 l_parent_regions_tab := l_sub_par_cont_tab;
3725 l_sub_par_cont_tab.DELETE;
3726 END IF;
3727
3728 END IF;
3729
3730 END LOOP;
3731
3732 --
3733 -- now we have a complete table of sub regions that match the profile level
3734 --
3735 -- For each region we have to check that there are eligible facilities for
3736 -- those regions
3737 --
3738
3739 FOR uuuu IN l_match_region_id.FIRST..l_match_region_id.LAST LOOP
3740 l_match_flag := 'N';
3741 l_match_locations_tab.DELETE;
3742
3743 IF (l_match_region_id.EXISTS(uuuu)) THEN
3744
3745 IF l_debug_on THEN
3746 WSH_DEBUG_SV.log(l_module_name,'l_match_region_id(uuuu) = ',l_match_region_id(uuuu));
3747 END IF;
3748
3749
3750 OPEN c_check_matching_locs(l_match_region_id(uuuu));
3751 FETCH c_check_matching_locs BULK COLLECT INTO
3752 l_match_locations_tab;
3753 CLOSE c_check_matching_locs;
3754
3755 IF (l_match_locations_tab.COUNT > 0) THEN
3756
3757 FOR vvvv IN l_match_locations_tab.FIRST..l_match_locations_tab.LAST LOOP
3758 IF (l_match_locations_tab.EXISTS(vvvv)) THEN
3759
3760 OPEN c_check_mile_flag(l_match_locations_tab(vvvv));
3761 FETCH c_check_mile_flag INTO
3762 l_match_flag;
3763 CLOSE c_check_mile_flag;
3764
3765 IF ((l_match_flag is not null) AND
3766 (l_match_flag = 'Y')) THEN
3767 -- This region has an eligible location
3768
3769 l_destination_region_id(l_match_region_id(uuuu)) := l_match_region_id(uuuu);
3770 l_destination_location_id(l_match_region_id(uuuu)) := null;
3771
3772 END IF;
3773 END IF;
3774 END LOOP;
3775 -- ELSE
3776 -- region has no facilities/locations
3777 -- null;
3778 END IF;
3779 END IF;
3780 END LOOP;
3781
3782 ELSIF (l_check_region_type > p_region_type) THEN
3783 RAISE FTE_DIST_INV_REGION_LOW;
3784 RETURN;
3785 END IF;
3786 END IF;
3787
3788 IF (l_destination_region_id.COUNT <= 0) THEN
3789
3790 RAISE FTE_DIST_NO_MATCH_REGIONS_FND;
3791 RETURN;
3792 END IF;
3793
3794
3795
3796 IF (l_destination_region_id.COUNT > 0) THEN
3797 l_ctr := 0;
3798 FOR ggg IN l_destination_region_id.FIRST..l_destination_region_id.LAST LOOP
3799 IF (l_destination_region_id.EXISTS(ggg)) THEN
3800 l_ctr := l_ctr + 1;
3801 l_tmp_dest_reg(l_ctr) := l_destination_region_id(ggg);
3802 l_tmp_dest_loc(l_ctr) := null;
3803 END IF;
3804 END LOOP;
3805 l_destination_region_id.DELETE;
3806 l_destination_location_id.DELETE;
3807 l_destination_region_id := l_tmp_dest_reg;
3808 l_destination_location_id := l_tmp_dest_loc;
3809 l_tmp_dest_reg.DELETE;
3810 l_tmp_dest_loc.DELETE;
3811 END IF;
3812
3813
3814
3815
3816 -- l_destination_location_id(1) := null;
3817 -- l_destination_region_id(1) := p_destination_id;
3818 -- ---------------------------------------------------------------------
3819 -- End of new code (Destination)
3820 -- ---------------------------------------------------------------------
3821
3822
3823 IF l_debug_on THEN
3824 WSH_DEBUG_SV.log(l_module_name,'l_destination_region_id.COUNT = ',l_destination_region_id.COUNT);
3825 IF (l_destination_region_id.COUNT > 0) THEN
3826 FOR gggdd IN l_destination_region_id.FIRST..l_destination_region_id.LAST LOOP
3827 WSH_DEBUG_SV.log(l_module_name,'l_destination_location_id(gggdd) = ',l_destination_location_id(gggdd));
3828 WSH_DEBUG_SV.log(l_module_name,'l_destination_region_id(gggdd) = ',l_destination_region_id(gggdd));
3829 END LOOP;
3830 END IF;
3831 END IF;
3832
3833 ELSIF ((l_destination_route = 3) OR
3834 (l_destination_route = 4)) THEN
3835 --
3836 -- The call is for all eligible facilities for destination
3837 --
3838 IF l_debug_on THEN
3839 WSH_DEBUG_SV.logmsg(l_module_name,'l_destination_route = 3 - The call is for all eligible facilities for destination');
3840 WSH_DEBUG_SV.logmsg(l_module_name,'Opening cursor c_get_all_elig_fac');
3841 WSH_DEBUG_SV.log(l_module_name,'PARAMETERS g_y_flag = ',g_y_flag);
3842 END IF;
3843
3844 OPEN c_get_all_elig_fac(g_y_flag);
3845 FETCH c_get_all_elig_fac BULK COLLECT INTO
3846 l_elig_locs_id_tab;
3847 CLOSE c_get_all_elig_fac;
3848
3849 IF (l_elig_locs_id_tab.COUNT = 0) THEN
3850 --
3851 -- There are no eligible facilities
3852 --
3853 IF l_debug_on THEN
3854 WSH_DEBUG_SV.logmsg(l_module_name,'There are no eligible facilities - RAISE FTE_DIST_NO_ELIG_FACILI_R3');
3855 END IF;
3856
3857 RAISE FTE_DIST_NO_ELIG_FACILI_R3;
3858 --
3859 -- Debug Statements
3860 --
3861 IF l_debug_on THEN
3862 WSH_DEBUG_SV.pop(l_module_name);
3863 END IF;
3864 --
3865 RETURN;
3866 END IF;
3867
3868 FOR qqq IN l_elig_locs_id_tab.FIRST..l_elig_locs_id_tab.LAST LOOP
3869 --
3870 -- get the regions for the locations
3871 --
3872 IF l_debug_on THEN
3873 WSH_DEBUG_SV.logmsg(l_module_name,'Opening cursor c_get_region_for_facility');
3874 WSH_DEBUG_SV.log(l_module_name,'PARAMETERS l_elig_locs_id_tab(qqq) = ',l_elig_locs_id_tab(qqq));
3875 WSH_DEBUG_SV.log(l_module_name,'PARAMETERS p_region_type = ',p_region_type);
3876 END IF;
3877
3878 OPEN c_get_region_for_facility(l_elig_locs_id_tab(qqq),
3879 p_region_type);
3880 FETCH c_get_region_for_facility BULK COLLECT INTO
3881 l_dest_location_id,
3882 l_dest_region_id;
3883 CLOSE c_get_region_for_facility;
3884
3885 IF (l_dest_location_id.COUNT > 0) THEN
3886 FOR ioi IN l_dest_location_id.FIRST..l_dest_location_id.LAST LOOP
3887 l_cd := l_destination_location_id.COUNT + 1;
3888 l_destination_location_id(l_cd) := l_dest_location_id(ioi);
3889 l_destination_region_id(l_cd) := l_dest_region_id(ioi);
3890 END LOOP;
3891 END IF;
3892
3893 END LOOP;
3894
3895 IF (l_destination_location_id.COUNT = 0) THEN
3896 IF l_debug_on THEN
3897 WSH_DEBUG_SV.logmsg(l_module_name,'l_destination_location_id.COUNT = 0 - RAISE FTE_DIST_NO_LOC_REG_MAP');
3898 END IF;
3899
3900 RAISE FTE_DIST_NO_LOC_REG_MAP;
3901 --
3902 -- Debug Statements
3903 --
3904 IF l_debug_on THEN
3905 WSH_DEBUG_SV.pop(l_module_name);
3906 END IF;
3907 --
3908 RETURN;
3909 END IF;
3910
3911 IF l_debug_on THEN
3912 WSH_DEBUG_SV.log(l_module_name,'l_destination_location_id.COUNT =',l_destination_location_id.COUNT);
3913 IF (l_destination_location_id.COUNT > 0) THEN
3914 FOR pppddd IN l_destination_location_id.FIRST..l_destination_location_id.LAST LOOP
3915 WSH_DEBUG_SV.log(l_module_name,'l_destination_location_id(pppddd) = ',l_destination_location_id(pppddd));
3916 WSH_DEBUG_SV.log(l_module_name,'l_destination_region_id(pppddd) = ',l_destination_region_id(pppddd));
3917 END LOOP;
3918 END IF;
3919 END IF;
3920 END IF;
3921
3922
3923 IF l_debug_on THEN
3924 WSH_DEBUG_SV.logmsg(l_module_name,'Now we have tables of origin and destination regions we need to get the attributes of each region');
3925 WSH_DEBUG_SV.logmsg(l_module_name,'Opening cursor c_get_region_values');
3926 END IF;
3927
3928 --
3929 -- Now we have tables of origin and destination regions
3930 -- we need to get the attributes of each region
3931 --
3932 FOR kkkk IN l_origin_region_id.FIRST..l_origin_region_id.LAST LOOP
3933
3934 IF l_debug_on THEN
3935 WSH_DEBUG_SV.log(l_module_name,'PARAMETERS l_origin_region_id(kkkk) = ',l_origin_region_id(kkkk));
3936 -- [BUG:3487060] WSH_DEBUG_SV.log(l_module_name,'PARAMETERS l_language = ',l_language);
3937 END IF;
3938
3939 -- [BUG:3487060] Remove Language from the query parameters
3940
3941 OPEN c_get_region_values(l_origin_region_id(kkkk)); -- l_language);
3942 FETCH c_get_region_values BULK COLLECT INTO
3943 l_reg_region_id_tab,
3944 l_reg_postal_code_from_tab,
3945 l_reg_city_tab,
3946 l_reg_state_tab,
3947 l_reg_county_tab,
3948 l_reg_country_tab;
3949 CLOSE c_get_region_values;
3950
3951
3952 --
3953 -- [ABLUNDEL][03/05/2004][BUG:3487060]
3954 -- Need to check that region values exist for the translated values
3955 -- in WSH_REGIONS_V - if not we log a bug
3956 --
3957 IF (l_reg_region_id_tab.COUNT = 0) THEN
3958 --
3959 -- There are no region values from WSH_REGIONS_V for the region id and the language
3960 --
3961 IF l_debug_on THEN
3962 WSH_DEBUG_SV.logmsg(l_module_name,'c_get_region_values - There are no region values from WSH_REGIONS_V for the region id and the language');
3963 WSH_DEBUG_SV.log(l_module_name,'l_origin region_id(kkkk)',l_origin_region_id(kkkk));
3964 WSH_DEBUG_SV.log(l_module_name,'l_language',l_language);
3965 END IF;
3966
3967 RAISE FTE_DIST_NO_ORIG_REG_VALS;
3968 --
3969 -- Debug Statements
3970 --
3971 IF l_debug_on THEN
3972 WSH_DEBUG_SV.pop(l_module_name);
3973 END IF;
3974 --
3975 RETURN;
3976 END IF;
3977
3978
3979
3980 --
3981 -- We also need to get the state and country codes - the mileage engine
3982 -- uses these
3983 --
3984 l_reg_code_region_id_tab.DELETE;
3985 l_reg_code_state_tab.DELETE;
3986 l_reg_code_country_tab.DELETE;
3987
3988 IF l_debug_on THEN
3989 WSH_DEBUG_SV.logmsg(l_module_name,'Opening cursor c_get_region_codes');
3990 WSH_DEBUG_SV.log(l_module_name,'PARAMETERS l_origin_region_id(kkkk) = ',l_origin_region_id(kkkk));
3991 END IF;
3992
3993 OPEN c_get_region_codes(l_origin_region_id(kkkk));
3994 FETCH c_get_region_codes BULK COLLECT INTO
3995 l_reg_code_region_id_tab,
3996 l_reg_code_state_tab,
3997 l_reg_code_country_tab;
3998 CLOSE c_get_region_codes;
3999 -- END LOOP;
4000
4001
4002 IF (l_reg_code_region_id_tab.COUNT > 0) THEN
4003 IF l_debug_on THEN
4004 WSH_DEBUG_SV.logmsg(l_module_name,'Index the codes');
4005 END IF;
4006 --
4007 -- Index the codes
4008 --
4009 FOR sss IN l_reg_code_region_id_tab.FIRST..l_reg_code_region_id_tab.LAST LOOP
4010 l_code_idx := l_reg_code_region_id_tab(sss);
4011 l_reg_code_table(l_code_idx).region_id := l_reg_code_region_id_tab(sss);
4012 l_reg_code_table(l_code_idx).state_code := l_reg_code_state_tab(sss);
4013 l_reg_code_table(l_code_idx).country_code := l_reg_code_country_tab(sss);
4014 END LOOP;
4015 END IF;
4016
4017
4018
4019
4020
4021 IF l_debug_on THEN
4022 WSH_DEBUG_SV.logmsg(l_module_name,'Put them in an indexed table');
4023 END IF;
4024 --
4025 -- Put them in an indexed table
4026 --
4027 FOR www IN l_reg_region_id_tab.FIRST..l_reg_region_id_tab.LAST LOOP
4028 l_idx := l_reg_region_id_tab(www);
4029 l_reg_table(l_idx).region_id := l_reg_region_id_tab(www);
4030 l_reg_table(l_idx).postal_code := l_reg_postal_code_from_tab(www);
4031 l_reg_table(l_idx).city := l_reg_city_tab(www);
4032 l_reg_table(l_idx).state := nvl(l_reg_code_table(l_reg_region_id_tab(www)).state_code,l_reg_state_tab(www));
4033 l_reg_table(l_idx).county := l_reg_county_tab(www);
4034 l_reg_table(l_idx).country := nvl(l_reg_code_table(l_reg_region_id_tab(www)).country_code,l_reg_country_tab(www));
4035 END LOOP;
4036
4037 END LOOP;
4038
4039
4040
4041
4042 IF l_debug_on THEN
4043 WSH_DEBUG_SV.logmsg(l_module_name,'Now do the same for the destination regions');
4044 WSH_DEBUG_SV.logmsg(l_module_name,'Opening cursor c_get_region_values');
4045 END IF;
4046 --
4047 -- Now do the same for the destination regions
4048 --
4049 FOR llll IN l_destination_region_id.FIRST..l_destination_region_id.LAST LOOP
4050
4051 IF l_debug_on THEN
4052 WSH_DEBUG_SV.log(l_module_name,'PARAMETER l_destination_region_id(llll) = ',l_destination_region_id(llll));
4053 WSH_DEBUG_SV.log(l_module_name,'l_language = ',l_language);
4054 END IF;
4055
4056 OPEN c_get_region_values(l_destination_region_id(llll)); -- l_language);
4057 FETCH c_get_region_values BULK COLLECT INTO
4058 l_reg_region_id_tab,
4059 l_reg_postal_code_from_tab,
4060 l_reg_city_tab,
4061 l_reg_state_tab,
4062 l_reg_county_tab,
4063 l_reg_country_tab;
4064 CLOSE c_get_region_values;
4065
4066 --
4067 -- [ABLUNDEL][03/05/2004][BUG:3487060]
4068 -- Need to check that region values exist for the translated values
4069 -- in WSH_REGIONS_V - if not we log a bug
4070 --
4071 IF (l_reg_region_id_tab.COUNT = 0) THEN
4072 --
4073 -- There are no region values from WSH_REGIONS_V for the region id and the language
4074 --
4075 IF l_debug_on THEN
4076 WSH_DEBUG_SV.logmsg(l_module_name,'c_get_region_values - There are no region values from WSH_REGIONS_V for the region id and the language');
4077 WSH_DEBUG_SV.log(l_module_name,'l_destination_region_id(llll)',l_destination_region_id(llll));
4078 WSH_DEBUG_SV.log(l_module_name,'l_language',l_language);
4079 END IF;
4080
4081 RAISE FTE_DIST_NO_DEST_REG_VALS;
4082 --
4083 -- Debug Statements
4084 --
4085 IF l_debug_on THEN
4086 WSH_DEBUG_SV.pop(l_module_name);
4087 END IF;
4088 --
4089 RETURN;
4090 END IF;
4091
4092 --
4093 -- We also need to get the state and country codes - the mileage engine
4094 -- uses these
4095 --
4096 l_reg_code_region_id_tab.DELETE;
4097 l_reg_code_state_tab.DELETE;
4098 l_reg_code_country_tab.DELETE;
4099
4100 IF l_debug_on THEN
4101 WSH_DEBUG_SV.logmsg(l_module_name,'Opening cursor c_get_region_codes');
4102 WSH_DEBUG_SV.log(l_module_name,'PARAMETER l_destination_region_id(llll) = ',l_destination_region_id(llll));
4103 END IF;
4104
4105 OPEN c_get_region_codes(l_destination_region_id(llll));
4106 FETCH c_get_region_codes BULK COLLECT INTO
4107 l_reg_code_region_id_tab,
4108 l_reg_code_state_tab,
4109 l_reg_code_country_tab;
4110 CLOSE c_get_region_codes;
4111 -- END LOOP;
4112
4113
4114 IF l_debug_on THEN
4115 WSH_DEBUG_SV.logmsg(l_module_name,'Index the codes');
4116 END IF;
4117 --
4118 -- Index the codes
4119 --
4120 FOR rrr IN l_reg_code_region_id_tab.FIRST..l_reg_code_region_id_tab.LAST LOOP
4121 l_code_idx := l_reg_code_region_id_tab(rrr);
4122 l_reg_code_table(l_code_idx).region_id := l_reg_code_region_id_tab(rrr);
4123 l_reg_code_table(l_code_idx).state_code := l_reg_code_state_tab(rrr);
4124 l_reg_code_table(l_code_idx).country_code := l_reg_code_country_tab(rrr);
4125 END LOOP;
4126
4127 IF l_debug_on THEN
4128 WSH_DEBUG_SV.logmsg(l_module_name,'Put them in an indexed table');
4129 END IF;
4130 --
4131 -- Put them in an indexed table
4132 --
4133 FOR xxx IN l_reg_region_id_tab.FIRST..l_reg_region_id_tab.LAST LOOP
4134 l_idx := l_reg_region_id_tab(xxx);
4135 l_reg_table(l_idx).region_id := l_reg_region_id_tab(xxx);
4136 l_reg_table(l_idx).postal_code := l_reg_postal_code_from_tab(xxx);
4137 l_reg_table(l_idx).city := l_reg_city_tab(xxx);
4138 l_reg_table(l_idx).state := nvl(l_reg_code_table(l_reg_region_id_tab(xxx)).state_code,l_reg_state_tab(xxx));
4139 l_reg_table(l_idx).county := l_reg_county_tab(xxx);
4140 l_reg_table(l_idx).country := nvl(l_reg_code_table(l_reg_region_id_tab(xxx)).country_code,l_reg_country_tab(xxx));
4141 END LOOP;
4142 END LOOP;
4143
4144
4145 --
4146 -- Now we have a complete table of regions and their values
4147 --
4148 IF l_debug_on THEN
4149 WSH_DEBUG_SV.logmsg(l_module_name,'Now we have a complete table of regions and their values');
4150 WSH_DEBUG_SV.log(l_module_name,'l_reg_table.COUNT = ',l_reg_table.COUNT);
4151 IF (l_reg_table.COUNT > 0) THEN
4152 FOR bbb IN l_reg_table.FIRST..l_reg_table.LAST LOOP
4153 IF (l_reg_table.EXISTS(bbb)) THEN
4154 WSH_DEBUG_SV.log(l_module_name,'(bbb) = '||bbb);
4155 WSH_DEBUG_SV.log(l_module_name,'l_reg_table(bbb).region_id = '||l_reg_table(bbb).region_id);
4156 WSH_DEBUG_SV.log(l_module_name,'l_reg_table(bbb).postal_code = '||l_reg_table(bbb).postal_code);
4157 WSH_DEBUG_SV.log(l_module_name,'l_reg_table(bbb).city = '||l_reg_table(bbb).city);
4158 WSH_DEBUG_SV.log(l_module_name,'l_reg_table(bbb).state = '||l_reg_table(bbb).state);
4159 WSH_DEBUG_SV.log(l_module_name,'l_reg_table(bbb).county = '||l_reg_table(bbb).county);
4160 WSH_DEBUG_SV.log(l_module_name,'l_reg_table(bbb).country = '||l_reg_table(bbb).country);
4161 END IF;
4162 END LOOP;
4163 END IF;
4164 END IF;
4165
4166
4167
4168 IF l_debug_on THEN
4169 WSH_DEBUG_SV.logmsg(l_module_name,'Now we need to format the Origin and destination pairs into a OD table');
4170 END IF;
4171
4172 -- the l_origin_region_id and l_destination_region_id has all regions for all the locations
4173 -- as many locations can point to the same region this means that we have to denormalize
4174 -- the list
4175 FOR mmm IN l_origin_region_id.FIRST..l_origin_region_id.LAST LOOP
4176 l_orig_denorm_id(l_origin_region_id(mmm)) := l_origin_region_id(mmm);
4177 END LOOP;
4178
4179 l_origin_region_id.DELETE;
4180 l_ctr := 0;
4181 FOR nnno IN l_orig_denorm_id.FIRST..l_orig_denorm_id.LAST LOOP
4182 IF (l_orig_denorm_id.EXISTS(nnno)) THEN
4183 l_ctr := l_ctr + 1;
4184 l_origin_region_id(l_ctr) := l_orig_denorm_id(nnno);
4185 END IF;
4186 END LOOP;
4187
4188
4189 FOR nnn IN l_destination_region_id.FIRST..l_destination_region_id.LAST LOOP
4190 l_dest_denorm_id(l_destination_region_id(nnn)) := l_destination_region_id(nnn);
4191 END LOOP;
4192
4193 l_destination_region_id.DELETE;
4194 l_ctr := 0;
4195 FOR nnnd IN l_dest_denorm_id.FIRST..l_dest_denorm_id.LAST LOOP
4196 IF (l_dest_denorm_id.EXISTS(nnnd)) THEN
4197 l_ctr := l_ctr + 1;
4198 l_destination_region_id(l_ctr) := l_dest_denorm_id(nnnd);
4199 END IF;
4200 END LOOP;
4201
4202
4203 --
4204 -- Now we need to format the Origin and destination pairs into a OD table
4205 --
4206 l_od_check_tab.DELETE;
4207
4208 l_od_idx := 0;
4209 FOR mmm IN l_origin_region_id.FIRST..l_origin_region_id.LAST LOOP
4210
4211 IF ((l_destination_route <> 3) OR (l_origin_route = 3)) THEN
4212 --
4213 -- add a check, we only want one record of each, eg with the same orig and dest we dont want
4214 -- A - A and
4215 -- A - A
4216 -- we only want
4217 -- A - A
4218
4219 IF (l_od_check_tab.EXISTS(l_origin_region_id(mmm)||l_origin_region_id(mmm)) = FALSE) THEN
4220 l_od_idx := l_od_idx + 1;
4221 -- put it in the table
4222 l_od_pair_tab(l_od_idx).origin_id := l_origin_region_id(mmm);
4223 l_od_pair_tab(l_od_idx).destination_id := l_origin_region_id(mmm);
4224
4225 --
4226 -- add it to the check table
4227 --
4228 l_od_check_tab(l_origin_region_id(mmm)||l_origin_region_id(mmm)) := l_origin_region_id(mmm)||l_origin_region_id(mmm);
4229 END IF;
4230 END IF;
4231
4232 --
4233 -- Loop through the destinations for the origin
4234 --
4235 FOR nnn IN l_destination_region_id.FIRST..l_destination_region_id.LAST LOOP
4236 IF (l_origin_region_id(mmm) <> l_destination_region_id(nnn)) THEN
4237 IF (l_od_check_tab.EXISTS(l_origin_region_id(mmm)||l_destination_region_id(nnn)) = FALSE) THEN
4238 l_od_idx := l_od_idx + 1;
4239 -- put it in the table
4240
4241 l_od_pair_tab(l_od_idx).origin_id := l_origin_region_id(mmm);
4242 l_od_pair_tab(l_od_idx).destination_id := l_destination_region_id(nnn);
4243 --
4244 -- add it to the check table
4245 --
4246 l_od_check_tab(l_origin_region_id(mmm)||l_destination_region_id(nnn)) := l_origin_region_id(mmm)||l_destination_region_id(nnn);
4247 END IF;
4248 END IF;
4249 END LOOP;
4250 END LOOP;
4251
4252 IF ((l_origin_route <> 4) AND
4253 (l_destination_route <> 4)) THEN
4254 --
4255 -- Now loop the other way to get the dest - origins
4256 --
4257 FOR ooo IN l_destination_region_id.FIRST..l_destination_region_id.LAST LOOP
4258
4259 IF (l_origin_route <> 3) THEN
4260 IF (l_od_check_tab.EXISTS(l_destination_region_id(ooo)||l_destination_region_id(ooo)) = FALSE) THEN
4261 l_od_idx := l_od_idx + 1;
4262 l_od_pair_tab(l_od_idx).origin_id := l_destination_region_id(ooo);
4263 l_od_pair_tab(l_od_idx).destination_id := l_destination_region_id(ooo);
4264 --
4265 -- add it to the check table
4266 --
4267 l_od_check_tab(l_destination_region_id(ooo)||l_destination_region_id(ooo)) := l_destination_region_id(ooo)||l_destination_region_id(ooo);
4268 END IF;
4269 END IF;
4270
4271 IF (((l_origin_route <> 3) AND
4272 (l_destination_route <> 3)) OR
4273 ((l_origin_route < 3) AND
4274 (l_destination_route = 3)) OR
4275 ((l_origin_route = 3) AND
4276 (l_destination_route < 3))) THEN
4277
4278 --
4279 -- Loop through the destinations for the origin
4280 --
4281 FOR yyy IN l_origin_region_id.FIRST..l_origin_region_id.LAST LOOP
4282 IF (l_destination_region_id(ooo) <> l_origin_region_id(yyy)) THEN
4283 IF (l_od_check_tab.EXISTS(l_destination_region_id(ooo)||l_origin_region_id(yyy)) = FALSE) THEN
4284 l_od_idx := l_od_idx + 1;
4285 l_od_pair_tab(l_od_idx).origin_id := l_destination_region_id(ooo);
4286 l_od_pair_tab(l_od_idx).destination_id := l_origin_region_id(yyy);
4287 --
4288 -- add it to the check table
4289 --
4290 l_od_check_tab(l_destination_region_id(ooo)||l_origin_region_id(yyy)) := l_destination_region_id(ooo)||l_origin_region_id(yyy);
4291 END IF;
4292 END IF;
4293 END LOOP;
4294 END IF;
4295 END LOOP;
4296 END IF;
4297
4298 --
4299 -- now we have a complete OD pair region ID table
4300 --
4301 IF l_debug_on THEN
4302 WSH_DEBUG_SV.logmsg(l_module_name,'now we have a complete OD pair region ID table');
4303 WSH_DEBUG_SV.log(l_module_name,'l_od_pair_tab.COUNT = ',l_od_pair_tab.COUNT);
4304 IF (l_od_pair_tab.COUNT > 0) THEN
4305 FOR uuu IN l_od_pair_tab.FIRST..l_od_pair_tab.LAST LOOP
4306 WSH_DEBUG_SV.log(l_module_name,'l_od_pair_tab(uuu).origin_id = ',l_od_pair_tab(uuu).origin_id);
4307 WSH_DEBUG_SV.log(l_module_name,'l_od_pair_tab(uuu).destination_id = ',l_od_pair_tab(uuu).destination_id);
4308 END LOOP;
4309 END IF;
4310 END IF;
4311
4312
4313
4314 IF ((l_origin_route = 4) OR
4315 (l_destination_route = 4)) THEN
4316
4317 l_rmve_ctr := 0;
4318 l_remove_idx_tab.DELETE;
4319 l_distance_tab_origin_id_tab.DELETE;
4320 l_distance_tab_distance_id_tab.DELETE;
4321 --
4322 -- We have to compare the OD table with the distance table
4323 --
4324 IF (l_od_pair_tab.COUNT > 0) THEN
4325
4326 OPEN c_get_distance_tab_pairs;
4327 FETCH c_get_distance_tab_pairs BULK COLLECT INTO
4328 l_distance_tab_origin_id_tab,
4329 l_distance_tab_distance_id_tab;
4330 CLOSE c_get_distance_tab_pairs;
4331
4332 IF (l_distance_tab_origin_id_tab.COUNT > 0) THEN
4333
4334 FOR eee IN l_od_pair_tab.FIRST..l_od_pair_tab.LAST LOOP
4335 IF (l_od_pair_tab.EXISTS(eee)) THEN
4336 FOR qqq IN l_distance_tab_origin_id_tab.FIRST..l_distance_tab_origin_id_tab.LAST LOOP
4337 IF ((l_od_pair_tab(eee).origin_id = l_distance_tab_origin_id_tab(qqq)) AND
4338 (l_od_pair_tab(eee).destination_id = l_distance_tab_distance_id_tab(qqq))) THEN
4339 l_rmve_ctr := l_rmve_ctr + 1;
4340 --
4341 -- The OD pair exists in the table we need to remove it from the table
4342 --
4343 l_remove_idx_tab(l_rmve_ctr) := eee;
4344 EXIT;
4345 END IF;
4346 END LOOP;
4347 END IF;
4348 END LOOP;
4349 END IF;
4350 END IF;
4351
4352 IF (l_remove_idx_tab.COUNT > 0) THEN
4353 --
4354 -- Records exist to be removed from the l_od_pair_tab table
4355 --
4356 FOR jjk IN l_remove_idx_tab.FIRST..l_remove_idx_tab.LAST LOOP
4357 l_od_pair_tab.DELETE(l_remove_idx_tab(jjk));
4358 END LOOP;
4359 END IF;
4360 END IF;
4361
4362
4363 IF l_debug_on THEN
4364 WSH_DEBUG_SV.logmsg(l_module_name,'populate the FTE_MILE_DOWNLOAD_FILES table NB: download date is null as it has not completed yet');
4365 END IF;
4366
4367 --
4368 -- populate the FTE_MILE_DOWNLOAD_FILES table
4369 -- NB: download date is null as it has not completed yet.
4370 --
4371 insert into fte_mile_download_files(DOWNLOAD_FILE_ID,
4372 FILE_NAME,
4373 DOWNLOAD_FILE_EXTENSION,
4374 TEMPLATE_ID,
4375 DOWNLOAD_DATE,
4376 UPLOAD_ID,
4377 UPLOAD_DATE,
4378 IDENTIFIER_TYPE,
4379 CREATION_DATE,
4380 CREATED_BY,
4381 LAST_UPDATE_DATE,
4382 LAST_UPDATED_BY,
4383 LAST_UPDATE_LOGIN,
4384 PROGRAM_APPLICATION_ID,
4385 PROGRAM_ID,
4386 PROGRAM_UPDATE_DATE,
4387 REQUEST_ID)
4388 values(FTE_MILE_DOWNLOAD_FILES_S.NEXTVAL,
4389 substr(p_file_name,1,8),
4390 p_file_extension,
4391 p_template_id,
4392 null,
4393 null,
4394 null,
4395 p_distance_profile,
4396 sysdate,
4397 fnd_global.user_id,
4398 sysdate,
4399 fnd_global.user_id,
4400 fnd_global.login_id,
4401 null,
4402 null,
4403 null,
4404 null)
4405 RETURNING DOWNLOAD_FILE_ID INTO l_download_file_id;
4406
4407
4408 IF l_debug_on THEN
4409 WSH_DEBUG_SV.log(l_module_name,'l_download_file_id = ',l_download_file_id);
4410 WSH_DEBUG_SV.logmsg(l_module_name,'Format the values to the file lines');
4411 END IF;
4412
4413
4414 IF l_debug_on THEN
4415 WSH_DEBUG_SV.log(l_module_name,'l_od_pair_tab.COUNT = ',l_od_pair_tab.COUNT);
4416 END IF;
4417
4418 IF (l_od_pair_tab.COUNT = 0) THEN
4419 --
4420 -- There are no OD pairs to upload
4421 -- we need to return as success but with a message
4422 --
4423 IF l_debug_on THEN
4424 WSH_DEBUG_SV.logmsg(l_module_name,'l_od_pair_tab.COUNT = 0 - RAISE FTE_DIST_NO_OD_PAIRS');
4425 END IF;
4426
4427 RAISE FTE_DIST_NO_OD_PAIRS;
4428 --
4429 -- Debug Statements
4430 --
4431 IF l_debug_on THEN
4432 WSH_DEBUG_SV.pop(l_module_name);
4433 END IF;
4434 --
4435 RETURN;
4436
4437 END IF;
4438
4439
4440
4441 --
4442 -- Format the values to the file lines
4443 --
4444 FOR axe IN l_od_pair_tab.FIRST..l_od_pair_tab.LAST LOOP
4445 IF (l_od_pair_tab.EXISTS(axe)) THEN
4446 l_origin_reg_id := l_od_pair_tab(axe).origin_id;
4447 l_dest_reg_id := l_od_pair_tab(axe).destination_id;
4448
4449
4450 l_origin_attr_string := null;
4451 l_dest_attr_string := null;
4452
4453 FOR vvv in l_attr_tab.FIRST..l_attr_tab.LAST LOOP
4454
4455 l_orig_attr_found := 'N';
4456 l_dest_attr_found := 'N';
4457
4458 IF (l_attr_tab(vvv).code = g_postal_code_name) THEN
4459 IF (l_reg_table(l_origin_reg_id).postal_code is not null) THEN
4460 l_origin_attr_string := l_origin_attr_string||l_reg_table(l_origin_reg_id).postal_code;
4461 l_origin_attr_value := l_reg_table(l_origin_reg_id).postal_code;
4462 l_orig_attr_found := 'Y';
4463 END IF;
4464 IF (l_reg_table(l_dest_reg_id).postal_code is not null) THEN
4465 l_dest_attr_string := l_dest_attr_string||l_reg_table(l_dest_reg_id).postal_code;
4466 l_dest_attr_value := l_reg_table(l_dest_reg_id).postal_code;
4467 l_dest_attr_found := 'Y';
4468 END IF;
4469
4470 ELSIF (l_attr_tab(vvv).code = g_city_code_name) THEN
4471 IF (l_reg_table(l_origin_reg_id).city is not null) THEN
4472 l_origin_attr_string := l_origin_attr_string||l_reg_table(l_origin_reg_id).city;
4473 l_origin_attr_value := l_reg_table(l_origin_reg_id).city;
4474 l_orig_attr_found := 'Y';
4475 END IF;
4476 IF (l_reg_table(l_dest_reg_id).city is not null) THEN
4477 l_dest_attr_string := l_dest_attr_string||l_reg_table(l_dest_reg_id).city;
4478 l_dest_attr_value := l_reg_table(l_dest_reg_id).city;
4479 l_dest_attr_found := 'Y';
4480 END IF;
4481 ELSIF (l_attr_tab(vvv).code = g_state_code_name) THEN
4482 IF (l_reg_table(l_origin_reg_id).state is not null) THEN
4483 l_origin_attr_string := l_origin_attr_string||l_reg_table(l_origin_reg_id).state;
4484 l_origin_attr_value := l_reg_table(l_origin_reg_id).state;
4485 l_orig_attr_found := 'Y';
4486 END IF;
4487 IF (l_reg_table(l_dest_reg_id).state is not null) THEN
4488 l_dest_attr_string := l_dest_attr_string||l_reg_table(l_dest_reg_id).state;
4489 l_dest_attr_value := l_reg_table(l_dest_reg_id).state;
4490 l_dest_attr_found := 'Y';
4491 END IF;
4492 ELSIF (l_attr_tab(vvv).code = g_county_code_name) THEN
4493 IF (l_reg_table(l_origin_reg_id).county is not null) THEN
4494 l_origin_attr_string := l_origin_attr_string||l_reg_table(l_origin_reg_id).county;
4495 l_origin_attr_value := l_reg_table(l_origin_reg_id).county;
4496 l_orig_attr_found := 'Y';
4497 END IF;
4498 IF (l_reg_table(l_dest_reg_id).county is not null) THEN
4499 l_dest_attr_string := l_dest_attr_string||l_reg_table(l_dest_reg_id).county;
4500 l_dest_attr_value := l_reg_table(l_dest_reg_id).county;
4501 l_dest_attr_found := 'Y';
4502 END IF;
4503 ELSIF (l_attr_tab(vvv).code = g_country_code_name) THEN
4504 IF (l_reg_table(l_origin_reg_id).country is not null) THEN
4505 l_origin_attr_string := l_origin_attr_string||l_reg_table(l_origin_reg_id).country;
4506 l_origin_attr_value := l_reg_table(l_origin_reg_id).country;
4507 l_orig_attr_found := 'Y';
4508 END IF;
4509 IF (l_reg_table(l_dest_reg_id).country is not null) THEN
4510 l_dest_attr_string := l_dest_attr_string||l_reg_table(l_dest_reg_id).country;
4511 l_dest_attr_value := l_reg_table(l_dest_reg_id).country;
4512 l_dest_attr_found := 'Y';
4513 END IF;
4514 END IF;
4515
4516 IF (l_use_length = 'A') THEN
4517 IF (l_orig_attr_found = 'Y') THEN
4518 FOR fff IN 1..(l_attr_tab(vvv).length - (LENGTH(l_origin_attr_value))) LOOP
4519 l_spacer := l_spacer||' ';
4520 END LOOP;
4521 l_origin_attr_string := l_origin_attr_string||l_spacer;
4522 l_spacer := null;
4523 l_origin_attr_value := null;
4524 ELSE
4525 FOR fff IN 1..l_attr_tab(vvv).length LOOP
4526 l_spacer := l_spacer||' ';
4527 END LOOP;
4528 l_origin_attr_string := l_origin_attr_string||l_spacer;
4529 l_spacer := null;
4530 l_origin_attr_value := null;
4531 END IF;
4532
4533 IF (l_dest_attr_found = 'Y') THEN
4534 FOR fff IN 1..(l_attr_tab(vvv).length - (LENGTH(l_dest_attr_value))) LOOP
4535 l_spacer := l_spacer||' ';
4536 END LOOP;
4537 l_dest_attr_string := l_dest_attr_string||l_spacer;
4538 l_spacer := null;
4539 l_dest_attr_value := null;
4540 ELSE
4541 FOR fff IN 1..l_attr_tab(vvv).length LOOP
4542 l_spacer := l_spacer||' ';
4543 END LOOP;
4544 l_dest_attr_string := l_dest_attr_string||l_spacer;
4545 l_spacer := null;
4546 l_origin_attr_value := null;
4547 END IF;
4548 END IF;
4549
4550 IF (l_attr_tab(vvv).delim is not null) THEN
4551 IF (l_orig_attr_found = 'Y') THEN
4552 l_origin_attr_string := l_origin_attr_string||l_attr_tab(vvv).delim;
4553 ELSE
4554 IF (l_use_length = 'A') THEN
4555 l_origin_attr_string := l_origin_attr_string||' ';
4556 END IF;
4557 END IF;
4558 IF (l_dest_attr_found = 'Y') THEN
4559 l_dest_attr_string := l_dest_attr_string||l_attr_tab(vvv).delim;
4560 ELSE
4561 IF (l_use_length = 'A') THEN
4562 l_dest_attr_string := l_dest_attr_string||' ';
4563 END IF;
4564 END IF;
4565 END IF;
4566
4567 END LOOP;
4568 -- ************************************************* --
4569
4570
4571 FOR zzz in l_col_tab.FIRST..l_col_tab.LAST LOOP
4572
4573 IF (l_col_tab(zzz).code = g_origin_col_name) THEN
4574 -- origin column
4575 --
4576 IF (l_use_length = 'C') THEN
4577 l_str_length := LENGTH(l_origin_attr_string);
4578 IF (l_str_length < l_col_tab(zzz).length) THEN
4579 l_origin_attr_string := RPAD(l_origin_attr_string,l_col_tab(zzz).length,' ');
4580 ELSIF (l_str_length > l_col_tab(zzz).length) THEN
4581 l_origin_attr_string := substr(l_origin_attr_string,1,l_col_tab(zzz).length);
4582 END IF;
4583 END IF;
4584 IF (l_col_tab(zzz).seq = 1) THEN
4585 -- This is the first column, check the start pos
4586 --
4587 IF (l_col_tab(zzz).start_pos > 1) THEN
4588 FOR fff IN 1..(l_col_tab(zzz).start_pos - 1) LOOP
4589 l_spacer := l_spacer||' ';
4590 END LOOP;
4591 l_origin_attr_string := l_spacer||l_origin_attr_string;
4592 l_spacer := null;
4593 END IF;
4594 END IF;
4595 IF (l_col_tab(zzz).delim is not null) THEN
4596 l_origin_attr_string := l_origin_attr_string||l_col_tab(zzz).delim;
4597 END IF;
4598
4599 ELSIF (l_col_tab(zzz).code = g_dest_col_name) THEN
4600 -- destination column
4601 --
4602 IF (l_use_length = 'C') THEN
4603 l_str_length := LENGTH(l_dest_attr_string);
4604 IF (l_str_length < l_col_tab(zzz).length) THEN
4605 l_dest_attr_string := RPAD(l_dest_attr_string,l_col_tab(zzz).length,' ');
4606 ELSIF (l_str_length > l_col_tab(zzz).length) THEN
4607 l_dest_attr_string := substr(l_dest_attr_string,1,l_col_tab(zzz).length);
4608 END IF;
4609 END IF;
4610 IF (l_col_tab(zzz).seq = 1) THEN
4611 -- This is the first column, check the start pos
4612 --
4613 IF (l_col_tab(zzz).start_pos > 1) THEN
4614 FOR fff IN 1..(l_col_tab(zzz).start_pos - 1) LOOP
4615 l_spacer := l_spacer||' ';
4616 END LOOP;
4617 l_dest_attr_string := l_spacer||l_dest_attr_string;
4618 l_spacer := null;
4619 END IF;
4620 END IF;
4621 IF (l_col_tab(zzz).delim is not null) THEN
4622 l_dest_attr_string := l_dest_attr_string||l_col_tab(zzz).delim;
4623 END IF;
4624 ELSIF (l_col_tab(zzz).code = g_ret_dist_col_name) THEN
4625 -- return distance
4626 --
4627 FOR bb IN 1..l_col_tab(zzz).length LOOP
4628 l_ret_dist_string := l_ret_dist_string||' ';
4629 END LOOP;
4630
4631 IF (l_col_tab(zzz).seq = 1) THEN
4632 -- This is the first column, check the start pos
4633 --
4634 IF (l_col_tab(zzz).start_pos > 1) THEN
4635 FOR fff IN 1..(l_col_tab(zzz).start_pos - 1) LOOP
4636 l_spacer := l_spacer||' ';
4637 END LOOP;
4638 l_ret_dist_string := l_spacer||l_ret_dist_string;
4639 l_spacer := null;
4640
4641 END IF;
4642 END IF;
4643 -- IF (l_col_tab(zzz).delim is not null) THEN
4644 -- l_ret_dist_string := l_ret_dist_string||l_col_tab(zzz).delim;
4645 -- END IF;
4646 ELSIF (l_col_tab(zzz).code = g_ret_time_col_name) THEN
4647 -- return time
4648 --
4649 FOR bb IN 1..l_col_tab(zzz).length LOOP
4650 l_ret_time_string := l_ret_time_string||' ';
4651 END LOOP;
4652
4653 IF (l_col_tab(zzz).seq = 1) THEN
4654 -- This is the first column, check the start pos
4655 --
4656 IF (l_col_tab(zzz).start_pos > 1) THEN
4657 FOR fff IN 1..(l_col_tab(zzz).start_pos - 1) LOOP
4658 l_spacer := l_spacer||' ';
4659 END LOOP;
4660 l_ret_time_string := l_spacer||l_ret_time_string;
4661 l_spacer := null;
4662 END IF;
4663 END IF;
4664 -- IF (l_col_tab(zzz).delim is not null) THEN
4665 -- l_ret_dist_string := l_ret_dist_string||l_col_tab(zzz).delim;
4666 -- END IF;
4667
4668 END IF;
4669
4670
4671 IF (l_col_tab(zzz).seq = 1) THEN
4672
4673 IF (l_col_tab(zzz).code = g_origin_col_name) THEN
4674 l_file_string := l_origin_attr_string;
4675 ELSIF (l_col_tab(zzz).code = g_dest_col_name) THEN
4676 l_file_string := l_dest_attr_string;
4677 ELSIF (l_col_tab(zzz).code = g_ret_dist_col_name) THEN
4678 l_file_string := l_ret_dist_string;
4679 ELSIF (l_col_tab(zzz).code = g_ret_time_col_name) THEN
4680 l_file_string := l_ret_time_string;
4681 END IF;
4682 ELSIF (l_col_tab(zzz).seq > 1) THEN
4683 IF (l_col_tab(zzz).code = g_origin_col_name) THEN
4684 IF (l_col_tab(zzz).start_pos = LENGTH(l_file_string) + 1) THEN
4685 l_file_string := l_file_string||l_origin_attr_string;
4686 ELSIF (l_col_tab(zzz).start_pos < LENGTH(l_file_string) + 1) THEN
4687 l_col_tab(zzz).start_pos := LENGTH(l_file_string) + 1;
4688 l_file_string := l_file_string||l_origin_attr_string;
4689 ELSIF (l_col_tab(zzz).start_pos > LENGTH(l_file_string) + 1) THEN
4690 FOR fff IN 1..((l_col_tab(zzz).start_pos - LENGTH(l_file_string)) - 1) LOOP
4691 l_spacer := l_spacer||' ';
4692 END LOOP;
4693 l_file_string := l_file_string||l_spacer||l_origin_attr_string;
4694 l_spacer := null;
4695
4696 ELSIF (l_col_tab(zzz).start_pos is null) THEN
4697 -- must be using attribute lengths
4698 l_file_string := l_file_string||l_origin_attr_string;
4699
4700 END IF;
4701 ELSIF (l_col_tab(zzz).code = g_dest_col_name) THEN
4702 IF (l_col_tab(zzz).start_pos = LENGTH(l_file_string) + 1) THEN
4703 l_file_string := l_file_string||l_dest_attr_string;
4704 ELSIF (l_col_tab(zzz).start_pos < LENGTH(l_file_string) + 1) THEN
4705 l_col_tab(zzz).start_pos := LENGTH(l_file_string) + 1;
4706 l_file_string := l_file_string||l_dest_attr_string;
4707 ELSIF (l_col_tab(zzz).start_pos > LENGTH(l_file_string) + 1) THEN
4708 FOR fff IN 1..((l_col_tab(zzz).start_pos - LENGTH(l_file_string)) - 1) LOOP
4709 l_spacer := l_spacer||' ';
4710 END LOOP;
4711 l_file_string := l_file_string||l_spacer||l_dest_attr_string;
4712 l_spacer := null;
4713
4714 ELSIF (l_col_tab(zzz).start_pos is null) THEN
4715 -- must be using attribute lengths
4716 l_file_string := l_file_string||l_dest_attr_string;
4717
4718 END IF;
4719
4720 --
4721 -- [2003/12/17][ABLUNDEL][BUG: 3325486]
4722 -- Commented out the code that creates the spaces in the line for
4723 -- return distance and return time. Now the download file only
4724 -- contains the origin and destination columns
4725 --
4726
4727 ELSIF (l_col_tab(zzz).code = g_ret_dist_col_name) THEN
4728 IF (l_col_tab(zzz).start_pos = LENGTH(l_file_string) + 1) THEN
4729 -- [BUG: 3325486] l_file_string := l_file_string||l_ret_dist_string;
4730 null; -- [BUG: 3325486]
4731 ELSIF (l_col_tab(zzz).start_pos < LENGTH(l_file_string) + 1) THEN
4732 l_col_tab(zzz).start_pos := LENGTH(l_file_string) + 1;
4733 -- [BUG: 3325486] l_file_string := l_file_string||l_ret_dist_string;
4734 ELSIF (l_col_tab(zzz).start_pos > LENGTH(l_file_string) + 1) THEN
4735 FOR fff IN 1..((l_col_tab(zzz).start_pos - LENGTH(l_file_string)) - 1) LOOP
4736 l_spacer := l_spacer||' ';
4737 END LOOP;
4738 -- [BUG: 3325486] l_file_string := l_file_string||l_spacer||l_ret_dist_string;
4739 l_spacer := null;
4740
4741 ELSIF (l_col_tab(zzz).start_pos is null) THEN
4742 -- must be using attribute lengths
4743 -- [BUG: 3325486] l_file_string := l_file_string||l_ret_dist_string;
4744 null; -- [BUG: 3325486]
4745 END IF;
4746 ELSIF (l_col_tab(zzz).code = g_ret_time_col_name) THEN
4747 IF (l_col_tab(zzz).start_pos = LENGTH(l_file_string) + 1) THEN
4748 -- [BUG: 3325486] l_file_string := l_file_string||l_ret_time_string;
4749 null; -- [BUG: 3325486]
4750 ELSIF (l_col_tab(zzz).start_pos < LENGTH(l_file_string) + 1) THEN
4751 l_col_tab(zzz).start_pos := LENGTH(l_file_string) + 1;
4752 -- [BUG: 3325486] l_file_string := l_file_string||l_ret_time_string;
4753 ELSIF (l_col_tab(zzz).start_pos > LENGTH(l_file_string) + 1) THEN
4754 FOR fff IN 1..((l_col_tab(zzz).start_pos - LENGTH(l_file_string)) - 1) LOOP
4755 l_spacer := l_spacer||' ';
4756 END LOOP;
4757 -- [BUG: 3325486] l_file_string := l_file_string||l_spacer||l_ret_time_string;
4758 l_spacer := null;
4759
4760 ELSIF (l_col_tab(zzz).start_pos is null) THEN
4761 -- must be using attribute lengths
4762 -- [BUG: 3325486] l_file_string := l_file_string||l_ret_time_string;
4763 null; -- [BUG: 3325486]
4764 END IF;
4765 END IF;
4766 END IF;
4767
4768 END LOOP;
4769
4770 l_od_pair_tab(axe).origin_line := l_origin_attr_string;
4771 l_od_pair_tab(axe).destination_line := l_dest_attr_string;
4772 l_od_pair_tab(axe).file_line := l_file_string;
4773 l_ret_dist_string := null;
4774 l_ret_time_string := null;
4775
4776 END IF;
4777
4778 END LOOP;
4779
4780
4781 IF l_debug_on THEN
4782 FOR axe1 IN l_od_pair_tab.FIRST..l_od_pair_tab.LAST LOOP
4783 IF (l_od_pair_tab.EXISTS(axe1)) THEN
4784 WSH_DEBUG_SV.log(l_module_name,'l_od_pair_tab(axe1).origin_id = ',l_od_pair_tab(axe1).origin_id);
4785 WSH_DEBUG_SV.log(l_module_name,'l_od_pair_tab(axe1).destination_id = ',l_od_pair_tab(axe1).destination_id);
4786 WSH_DEBUG_SV.log(l_module_name,'l_od_pair_tab(axe1).origin_line = ',l_od_pair_tab(axe1).origin_line);
4787 WSH_DEBUG_SV.log(l_module_name,'l_od_pair_tab(axe1).destination_line = ',l_od_pair_tab(axe1).destination_line);
4788 WSH_DEBUG_SV.log(l_module_name,'l_od_pair_tab(axe1).file_line = ',l_od_pair_tab(axe1).file_line);
4789 END IF;
4790 END LOOP;
4791 END IF;
4792
4793
4794
4795 IF l_debug_on THEN
4796 WSH_DEBUG_SV.logmsg(l_module_name,'opening the target file with parameters l_target_file := utl_file.fopen(l_download_dir, p_file_name,W,8192');
4797 END IF;
4798
4799 l_target_file := utl_file.fopen(l_download_dir, p_file_name, 'W',8192);
4800
4801
4802 --
4803 -- Now we create the file data
4804 --
4805 l_line_ctr := 0;
4806 FOR axe1 IN l_od_pair_tab.FIRST..l_od_pair_tab.LAST LOOP
4807 IF (l_od_pair_tab.EXISTS(axe1)) THEN
4808
4809 utl_file.put_line(l_target_file,l_od_pair_tab(axe1).file_line);
4810 utl_file.fflush(l_target_file);
4811
4812 l_line_ctr := l_line_ctr + 1;
4813 IF l_debug_on THEN
4814 WSH_DEBUG_SV.log(l_module_name,'insert into fte_mile_download_lines - line number = ',l_line_ctr);
4815 END IF;
4816 insert into fte_mile_download_lines(DOWNLOAD_FILE_ID,
4817 LINE_NUMBER,
4818 ORIGIN_ID,
4819 DESTINATION_ID,
4820 CREATION_DATE,
4821 CREATED_BY,
4822 LAST_UPDATE_DATE,
4823 LAST_UPDATED_BY,
4824 LAST_UPDATE_LOGIN,
4825 PROGRAM_APPLICATION_ID,
4826 PROGRAM_ID,
4827 PROGRAM_UPDATE_DATE,
4828 REQUEST_ID)
4829 values(l_download_file_id,
4830 l_line_ctr,
4831 l_od_pair_tab(axe1).origin_id,
4832 l_od_pair_tab(axe1).destination_id,
4833 sysdate,
4834 fnd_global.user_id,
4835 sysdate,
4836 fnd_global.user_id,
4837 fnd_global.login_id,
4838 null,
4839 null,
4840 null,
4841 null);
4842
4843
4844 END IF;
4845 END LOOP;
4846
4847
4848 IF l_debug_on THEN
4849 WSH_DEBUG_SV.logmsg(l_module_name,' closing the file ');
4850 END IF;
4851
4852 utl_file.fclose(l_target_file);
4853
4854
4855 --
4856 -- If we are here then all has gone well
4857 -- update the fte_mile_download_files table with the download date
4858 --
4859 IF l_debug_on THEN
4860 WSH_DEBUG_SV.logmsg(l_module_name,'If we are here then all has gone well update the fte_mile_download_files table with the download date');
4861 END IF;
4862
4863 update fte_mile_download_files
4864 set download_date = sysdate
4865 where download_file_id = l_download_file_id;
4866
4867
4868 IF l_debug_on THEN
4869 WSH_DEBUG_SV.logmsg(l_module_name,'set the return status to success and return back to the calling procedure');
4870 END IF;
4871
4872 x_return_message := null;
4873 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4874
4875 --
4876 -- Debug Statements
4877 --
4878 IF l_debug_on THEN
4879 WSH_DEBUG_SV.pop(l_module_name);
4880 END IF;
4881 --
4882 RETURN;
4883
4884
4885 EXCEPTION
4886 WHEN FTE_DIST_NO_ORIG_REG_VALS THEN
4887 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_ORIG_REG_VALS');
4888 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4889 x_return_message := FND_MESSAGE.GET;
4890 WSH_UTIL_CORE.add_message(x_return_status);
4891 --
4892 -- Close the file
4893 --
4894 IF (utl_file.is_open(l_target_file)) THEN
4895 utl_file.fclose(l_target_file);
4896 END IF;
4897
4898 --
4899 -- Close cursors
4900 --
4901 IF (c_get_distance_tab_pairs%ISOPEN) THEN
4902 CLOSE c_get_distance_tab_pairs;
4903 END IF;
4904 IF (c_get_region_codes%ISOPEN) THEN
4905 CLOSE c_get_region_codes;
4906 END IF;
4907 IF (c_get_region_values%ISOPEN) THEN
4908 CLOSE c_get_region_values;
4909 END IF;
4910 IF (c_check_distance_table%ISOPEN) THEN
4911 CLOSE c_check_distance_table;
4912 END IF;
4913 IF (c_get_all_elig_fac%ISOPEN) THEN
4914 CLOSE c_get_all_elig_fac;
4915 END IF;
4916 IF (c_get_region_for_facility%ISOPEN) THEN
4917 CLOSE c_get_region_for_facility;
4918 END IF;
4919 IF (c_get_template_columns%ISOPEN) THEN
4920 CLOSE c_get_template_columns;
4921 END IF;
4922 IF (c_get_col_attrs%ISOPEN) THEN
4923 CLOSE c_get_col_attrs;
4924 END IF;
4925 IF (c_get_col_attr%ISOPEN) THEN
4926 CLOSE c_get_col_attr;
4927 END IF;
4928
4929 --
4930 -- Debug Statements
4931 --
4932 IF l_debug_on THEN
4933 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_INT_PKG.FTE_DIST_NO_ORIG_REG_VALS RAISED');
4934 WSH_DEBUG_SV.log(l_module_name,'FTE_DIST_NO_ORIG_REG_VALS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4935 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_ORIG_REG_VALS');
4936 WSH_DEBUG_SV.pop(l_module_name);
4937 END IF;
4938 --
4939 RETURN;
4940
4941
4942 WHEN FTE_DIST_NO_DEST_REG_VALS THEN
4943 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_DEST_REG_VALS');
4944 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
4945 x_return_message := FND_MESSAGE.GET;
4946 WSH_UTIL_CORE.add_message(x_return_status);
4947 --
4948 -- Close the file
4949 --
4950 IF (utl_file.is_open(l_target_file)) THEN
4951 utl_file.fclose(l_target_file);
4952 END IF;
4953
4954 --
4955 -- Close cursors
4956 --
4957 IF (c_get_distance_tab_pairs%ISOPEN) THEN
4958 CLOSE c_get_distance_tab_pairs;
4959 END IF;
4960 IF (c_get_region_codes%ISOPEN) THEN
4961 CLOSE c_get_region_codes;
4962 END IF;
4963 IF (c_get_region_values%ISOPEN) THEN
4964 CLOSE c_get_region_values;
4965 END IF;
4966 IF (c_check_distance_table%ISOPEN) THEN
4967 CLOSE c_check_distance_table;
4968 END IF;
4969 IF (c_get_all_elig_fac%ISOPEN) THEN
4970 CLOSE c_get_all_elig_fac;
4971 END IF;
4972 IF (c_get_region_for_facility%ISOPEN) THEN
4973 CLOSE c_get_region_for_facility;
4974 END IF;
4975 IF (c_get_template_columns%ISOPEN) THEN
4976 CLOSE c_get_template_columns;
4977 END IF;
4978 IF (c_get_col_attrs%ISOPEN) THEN
4979 CLOSE c_get_col_attrs;
4980 END IF;
4981 IF (c_get_col_attr%ISOPEN) THEN
4982 CLOSE c_get_col_attr;
4983 END IF;
4984
4985 --
4986 -- Debug Statements
4987 --
4988 IF l_debug_on THEN
4989 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_INT_PKG.FTE_DIST_NO_DEST_REG_VALS RAISED');
4990 WSH_DEBUG_SV.log(l_module_name,'FTE_DIST_NO_DEST_REG_VALS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
4991 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_DEST_REG_VALS');
4992 WSH_DEBUG_SV.pop(l_module_name);
4993 END IF;
4994 --
4995 RETURN;
4996
4997
4998
4999 WHEN FTE_DIST_NO_COLS_FOR_TEMPLATE THEN
5000 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_COLS_FOR_TEMPLATE');
5001 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5002 x_return_message := FND_MESSAGE.GET;
5003 WSH_UTIL_CORE.add_message(x_return_status);
5004 --
5005 -- Close the file
5006 --
5007 IF (utl_file.is_open(l_target_file)) THEN
5008 utl_file.fclose(l_target_file);
5009 END IF;
5010
5011 --
5012 -- Close cursors
5013 --
5014 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5015 CLOSE c_get_distance_tab_pairs;
5016 END IF;
5017 IF (c_get_region_codes%ISOPEN) THEN
5018 CLOSE c_get_region_codes;
5019 END IF;
5020 IF (c_get_region_values%ISOPEN) THEN
5021 CLOSE c_get_region_values;
5022 END IF;
5023 IF (c_check_distance_table%ISOPEN) THEN
5024 CLOSE c_check_distance_table;
5025 END IF;
5026 IF (c_get_all_elig_fac%ISOPEN) THEN
5027 CLOSE c_get_all_elig_fac;
5028 END IF;
5029 IF (c_get_region_for_facility%ISOPEN) THEN
5030 CLOSE c_get_region_for_facility;
5031 END IF;
5032 IF (c_get_template_columns%ISOPEN) THEN
5033 CLOSE c_get_template_columns;
5034 END IF;
5035 IF (c_get_col_attrs%ISOPEN) THEN
5036 CLOSE c_get_col_attrs;
5037 END IF;
5038 IF (c_get_col_attr%ISOPEN) THEN
5039 CLOSE c_get_col_attr;
5040 END IF;
5041
5042
5043 --
5044 -- Debug Statements
5045 --
5046 IF l_debug_on THEN
5047 WSH_DEBUG_SV.logmsg(l_module_name, 'EXCEPTION FTE_DIST_INT_PKG.FTE_DIST_NO_COLS_FOR_TEMPLATE RAISED');
5048 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_COLS_FOR_TEMPLATE exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5049 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_COLS_FOR_TEMPLATE');
5050 WSH_DEBUG_SV.pop(l_module_name);
5051 END IF;
5052 --
5053 RETURN;
5054
5055
5056
5057
5058 WHEN FTE_DIST_NO_OD_COLS THEN
5059 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_OD_COLS');
5060 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5061 x_return_message := FND_MESSAGE.GET;
5062 WSH_UTIL_CORE.add_message(x_return_status);
5063
5064 --
5065 -- close file
5066 --
5067 IF (utl_file.is_open(l_target_file)) THEN
5068 utl_file.fclose(l_target_file);
5069 END IF;
5070
5071 --
5072 -- Close cursors
5073 --
5074 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5075 CLOSE c_get_distance_tab_pairs;
5076 END IF;
5077 IF (c_get_region_codes%ISOPEN) THEN
5078 CLOSE c_get_region_codes;
5079 END IF;
5080 IF (c_get_region_values%ISOPEN) THEN
5081 CLOSE c_get_region_values;
5082 END IF;
5083 IF (c_check_distance_table%ISOPEN) THEN
5084 CLOSE c_check_distance_table;
5085 END IF;
5086 IF (c_get_all_elig_fac%ISOPEN) THEN
5087 CLOSE c_get_all_elig_fac;
5088 END IF;
5089 IF (c_get_region_for_facility%ISOPEN) THEN
5090 CLOSE c_get_region_for_facility;
5091 END IF;
5092 IF (c_get_template_columns%ISOPEN) THEN
5093 CLOSE c_get_template_columns;
5094 END IF;
5095 IF (c_get_col_attrs%ISOPEN) THEN
5096 CLOSE c_get_col_attrs;
5097 END IF;
5098 IF (c_get_col_attr%ISOPEN) THEN
5099 CLOSE c_get_col_attr;
5100 END IF;
5101
5102 --
5103 -- Debug Statements
5104 --
5105 IF l_debug_on THEN
5106 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_INT_PKG.FTE_DIST_NO_OD_COLS RAISED');
5107 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_OD_COLS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5108 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_OD_COLS');
5109 WSH_DEBUG_SV.pop(l_module_name);
5110 END IF;
5111 --
5112 RETURN;
5113
5114
5115 WHEN FTE_DIST_NO_RET_COLS THEN
5116 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_RET_COLS');
5117 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5118 x_return_message := FND_MESSAGE.GET;
5119 WSH_UTIL_CORE.add_message(x_return_status);
5120 --
5121 -- Close the file
5122 --
5123 IF (utl_file.is_open(l_target_file)) THEN
5124 utl_file.fclose(l_target_file);
5125 END IF;
5126
5127 --
5128 -- Close cursors
5129 --
5130 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5131 CLOSE c_get_distance_tab_pairs;
5132 END IF;
5133 IF (c_get_region_codes%ISOPEN) THEN
5134 CLOSE c_get_region_codes;
5135 END IF;
5136 IF (c_get_region_values%ISOPEN) THEN
5137 CLOSE c_get_region_values;
5138 END IF;
5139 IF (c_check_distance_table%ISOPEN) THEN
5140 CLOSE c_check_distance_table;
5141 END IF;
5142 IF (c_get_all_elig_fac%ISOPEN) THEN
5143 CLOSE c_get_all_elig_fac;
5144 END IF;
5145 IF (c_get_region_for_facility%ISOPEN) THEN
5146 CLOSE c_get_region_for_facility;
5147 END IF;
5148 IF (c_get_template_columns%ISOPEN) THEN
5149 CLOSE c_get_template_columns;
5150 END IF;
5151 IF (c_get_col_attrs%ISOPEN) THEN
5152 CLOSE c_get_col_attrs;
5153 END IF;
5154 IF (c_get_col_attr%ISOPEN) THEN
5155 CLOSE c_get_col_attr;
5156 END IF;
5157
5158 --
5159 -- Debug Statements
5160 --
5161 IF l_debug_on THEN
5162 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_INT_PKG.FTE_DIST_NO_RET_COLS RAISED');
5163 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_RET_COLS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5164 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_RET_COLS');
5165 WSH_DEBUG_SV.pop(l_module_name);
5166 END IF;
5167 --
5168 RETURN;
5169
5170
5171 WHEN FTE_DIST_NO_RET_ATTRS THEN
5172 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_RET_ATTRS');
5173 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5174 x_return_message := FND_MESSAGE.GET;
5175 WSH_UTIL_CORE.add_message(x_return_status);
5176 --
5177 -- Close the file
5178 --
5179 IF (utl_file.is_open(l_target_file)) THEN
5180 utl_file.fclose(l_target_file);
5181 END IF;
5182
5183
5184 --
5185 -- Close cursors
5186 --
5187 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5188 CLOSE c_get_distance_tab_pairs;
5189 END IF;
5190 IF (c_get_region_codes%ISOPEN) THEN
5191 CLOSE c_get_region_codes;
5192 END IF;
5193 IF (c_get_region_values%ISOPEN) THEN
5194 CLOSE c_get_region_values;
5195 END IF;
5196 IF (c_check_distance_table%ISOPEN) THEN
5197 CLOSE c_check_distance_table;
5198 END IF;
5199 IF (c_get_all_elig_fac%ISOPEN) THEN
5200 CLOSE c_get_all_elig_fac;
5201 END IF;
5202 IF (c_get_region_for_facility%ISOPEN) THEN
5203 CLOSE c_get_region_for_facility;
5204 END IF;
5205 IF (c_get_template_columns%ISOPEN) THEN
5206 CLOSE c_get_template_columns;
5207 END IF;
5208 IF (c_get_col_attrs%ISOPEN) THEN
5209 CLOSE c_get_col_attrs;
5210 END IF;
5211 IF (c_get_col_attr%ISOPEN) THEN
5212 CLOSE c_get_col_attr;
5213 END IF;
5214
5215
5216 --
5217 -- Debug Statements
5218 --
5219 IF l_debug_on THEN
5220 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_INT_PKG.FTE_DIST_NO_RET_ATTRS RAISED');
5221 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_RET_ATTRS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5222 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_RET_ATTRS');
5223 WSH_DEBUG_SV.pop(l_module_name);
5224 END IF;
5225 --
5226 RETURN;
5227
5228
5229 WHEN FTE_DIST_NO_OD_ATTRS THEN
5230 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_OD_ATTRS');
5231 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5232 x_return_message := FND_MESSAGE.GET;
5233 WSH_UTIL_CORE.add_message(x_return_status);
5234 --
5235 -- Close the file
5236 --
5237 IF (utl_file.is_open(l_target_file)) THEN
5238 utl_file.fclose(l_target_file);
5239 END IF;
5240
5241 --
5242 -- Close cursors
5243 --
5244 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5245 CLOSE c_get_distance_tab_pairs;
5246 END IF;
5247 IF (c_get_region_codes%ISOPEN) THEN
5248 CLOSE c_get_region_codes;
5249 END IF;
5250 IF (c_get_region_values%ISOPEN) THEN
5251 CLOSE c_get_region_values;
5252 END IF;
5253 IF (c_check_distance_table%ISOPEN) THEN
5254 CLOSE c_check_distance_table;
5255 END IF;
5256 IF (c_get_all_elig_fac%ISOPEN) THEN
5257 CLOSE c_get_all_elig_fac;
5258 END IF;
5259 IF (c_get_region_for_facility%ISOPEN) THEN
5260 CLOSE c_get_region_for_facility;
5261 END IF;
5262 IF (c_get_template_columns%ISOPEN) THEN
5263 CLOSE c_get_template_columns;
5264 END IF;
5265 IF (c_get_col_attrs%ISOPEN) THEN
5266 CLOSE c_get_col_attrs;
5267 END IF;
5268 IF (c_get_col_attr%ISOPEN) THEN
5269 CLOSE c_get_col_attr;
5270 END IF;
5271
5272 --
5273 -- Debug Statements
5274 --
5275 IF l_debug_on THEN
5276 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_INT_PKG.FTE_DIST_NO_OD_ATTRS RAISED');
5277 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_OD_ATTRS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5278 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_OD_ATTRS');
5279 WSH_DEBUG_SV.pop(l_module_name);
5280 END IF;
5281 --
5282 RETURN;
5283
5284
5285 WHEN FTE_DIST_INVALID_START_POS THEN
5286 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INVALID_START_POS');
5287 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5288 x_return_message := FND_MESSAGE.GET;
5289 WSH_UTIL_CORE.add_message(x_return_status);
5290 --
5291 -- Close the file
5292 --
5293 IF (utl_file.is_open(l_target_file)) THEN
5294 utl_file.fclose(l_target_file);
5295 END IF;
5296
5297 --
5298 -- Close cursors
5299 --
5300 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5301 CLOSE c_get_distance_tab_pairs;
5302 END IF;
5303 IF (c_get_region_codes%ISOPEN) THEN
5304 CLOSE c_get_region_codes;
5305 END IF;
5306 IF (c_get_region_values%ISOPEN) THEN
5307 CLOSE c_get_region_values;
5308 END IF;
5309 IF (c_check_distance_table%ISOPEN) THEN
5310 CLOSE c_check_distance_table;
5311 END IF;
5312 IF (c_get_all_elig_fac%ISOPEN) THEN
5313 CLOSE c_get_all_elig_fac;
5314 END IF;
5315 IF (c_get_region_for_facility%ISOPEN) THEN
5316 CLOSE c_get_region_for_facility;
5317 END IF;
5318 IF (c_get_template_columns%ISOPEN) THEN
5319 CLOSE c_get_template_columns;
5320 END IF;
5321 IF (c_get_col_attrs%ISOPEN) THEN
5322 CLOSE c_get_col_attrs;
5323 END IF;
5324 IF (c_get_col_attr%ISOPEN) THEN
5325 CLOSE c_get_col_attr;
5326 END IF;
5327
5328
5329 --
5330 -- Debug Statements
5331 --
5332 IF l_debug_on THEN
5333 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_INT_PKG.FTE_DIST_INVALID_START_POS');
5334 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_START_POS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5335 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_START_POS');
5336 WSH_DEBUG_SV.pop(l_module_name);
5337 END IF;
5338 --
5339 RETURN;
5340
5341
5342 WHEN FTE_DIST_INVALID_COL_LENGTHS THEN
5343 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INVALID_COL_LENGTHS');
5344 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5345 x_return_message := FND_MESSAGE.GET;
5346 WSH_UTIL_CORE.add_message(x_return_status);
5347 --
5348 -- Close the file
5349 --
5350 IF (utl_file.is_open(l_target_file)) THEN
5351 utl_file.fclose(l_target_file);
5352 END IF;
5353
5354
5355 --
5356 -- Close cursors
5357 --
5358 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5359 CLOSE c_get_distance_tab_pairs;
5360 END IF;
5361 IF (c_get_region_codes%ISOPEN) THEN
5362 CLOSE c_get_region_codes;
5363 END IF;
5364 IF (c_get_region_values%ISOPEN) THEN
5365 CLOSE c_get_region_values;
5366 END IF;
5367 IF (c_check_distance_table%ISOPEN) THEN
5368 CLOSE c_check_distance_table;
5369 END IF;
5370 IF (c_get_all_elig_fac%ISOPEN) THEN
5371 CLOSE c_get_all_elig_fac;
5372 END IF;
5373 IF (c_get_region_for_facility%ISOPEN) THEN
5374 CLOSE c_get_region_for_facility;
5375 END IF;
5376 IF (c_get_template_columns%ISOPEN) THEN
5377 CLOSE c_get_template_columns;
5378 END IF;
5379 IF (c_get_col_attrs%ISOPEN) THEN
5380 CLOSE c_get_col_attrs;
5381 END IF;
5382 IF (c_get_col_attr%ISOPEN) THEN
5383 CLOSE c_get_col_attr;
5384 END IF;
5385
5386 IF l_debug_on THEN
5387 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_INT_PKG.FTE_DIST_INVALID_COL_LENGTHS');
5388 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INVALID_COL_LENGTHS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5389 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INVALID_COL_LENGTHS');
5390 WSH_DEBUG_SV.pop(l_module_name);
5391 END IF;
5392 --
5393 RETURN;
5394
5395
5396 WHEN FTE_DIST_NO_LOC_REG_MAP THEN
5397 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_LOC_REG_MAP');
5398 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5399 x_return_message := FND_MESSAGE.GET;
5400 WSH_UTIL_CORE.add_message(x_return_status);
5401 --
5402 -- Close the file
5403 --
5404 IF (utl_file.is_open(l_target_file)) THEN
5405 utl_file.fclose(l_target_file);
5406 END IF;
5407 --
5408 -- Close cursors
5409 --
5410 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5411 CLOSE c_get_distance_tab_pairs;
5412 END IF;
5413 IF (c_get_region_codes%ISOPEN) THEN
5414 CLOSE c_get_region_codes;
5415 END IF;
5416 IF (c_get_region_values%ISOPEN) THEN
5417 CLOSE c_get_region_values;
5418 END IF;
5419 IF (c_check_distance_table%ISOPEN) THEN
5420 CLOSE c_check_distance_table;
5421 END IF;
5422 IF (c_get_all_elig_fac%ISOPEN) THEN
5423 CLOSE c_get_all_elig_fac;
5424 END IF;
5425 IF (c_get_region_for_facility%ISOPEN) THEN
5426 CLOSE c_get_region_for_facility;
5427 END IF;
5428 IF (c_get_template_columns%ISOPEN) THEN
5429 CLOSE c_get_template_columns;
5430 END IF;
5431 IF (c_get_col_attrs%ISOPEN) THEN
5432 CLOSE c_get_col_attrs;
5433 END IF;
5434 IF (c_get_col_attr%ISOPEN) THEN
5435 CLOSE c_get_col_attr;
5436 END IF;
5437
5438 --
5439 -- Debug Statements
5440 --
5441 IF l_debug_on THEN
5442 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_NO_LOC_REG_MAP');
5443 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_LOC_REG_MAP exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5444 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_LOC_REG_MAP');
5445 WSH_DEBUG_SV.pop(l_module_name);
5446 END IF;
5447 --
5448 --
5449 RETURN;
5450
5451
5452 WHEN FTE_DIST_NO_LOC_SPEC_R1 THEN
5453 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_LOC_SPEC_R1');
5454 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5455 x_return_message := FND_MESSAGE.GET;
5456 WSH_UTIL_CORE.add_message(x_return_status);
5457 --
5458 -- Close the file
5459 --
5460 IF (utl_file.is_open(l_target_file)) THEN
5461 utl_file.fclose(l_target_file);
5462 END IF;
5463
5464 --
5465 -- Close cursors
5466 --
5467 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5468 CLOSE c_get_distance_tab_pairs;
5469 END IF;
5470 IF (c_get_region_codes%ISOPEN) THEN
5471 CLOSE c_get_region_codes;
5472 END IF;
5473 IF (c_get_region_values%ISOPEN) THEN
5474 CLOSE c_get_region_values;
5475 END IF;
5476 IF (c_check_distance_table%ISOPEN) THEN
5477 CLOSE c_check_distance_table;
5478 END IF;
5479 IF (c_get_all_elig_fac%ISOPEN) THEN
5480 CLOSE c_get_all_elig_fac;
5481 END IF;
5482 IF (c_get_region_for_facility%ISOPEN) THEN
5483 CLOSE c_get_region_for_facility;
5484 END IF;
5485 IF (c_get_template_columns%ISOPEN) THEN
5486 CLOSE c_get_template_columns;
5487 END IF;
5488 IF (c_get_col_attrs%ISOPEN) THEN
5489 CLOSE c_get_col_attrs;
5490 END IF;
5491 IF (c_get_col_attr%ISOPEN) THEN
5492 CLOSE c_get_col_attr;
5493 END IF;
5494
5495 --
5496 -- Debug Statements
5497 --
5498 IF l_debug_on THEN
5499 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_NO_LOC_SPEC_R1');
5500 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_LOC_SPEC_R1 exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5501 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_LOC_SPEC_R1');
5502 WSH_DEBUG_SV.pop(l_module_name);
5503 END IF;
5504 --
5505 RETURN;
5506
5507 WHEN FTE_DIST_NO_REGION_SPEC_R2 THEN
5508 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_REGION_SPEC_R2');
5509 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5510 x_return_message := FND_MESSAGE.GET;
5511 WSH_UTIL_CORE.add_message(x_return_status);
5512 --
5513 -- Close the file
5514 --
5515 IF (utl_file.is_open(l_target_file)) THEN
5516 utl_file.fclose(l_target_file);
5517 END IF;
5518
5519 --
5520 -- Close cursors
5521 --
5522 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5523 CLOSE c_get_distance_tab_pairs;
5524 END IF;
5525 IF (c_get_region_codes%ISOPEN) THEN
5526 CLOSE c_get_region_codes;
5527 END IF;
5528 IF (c_get_region_values%ISOPEN) THEN
5529 CLOSE c_get_region_values;
5530 END IF;
5531 IF (c_check_distance_table%ISOPEN) THEN
5532 CLOSE c_check_distance_table;
5533 END IF;
5534 IF (c_get_all_elig_fac%ISOPEN) THEN
5535 CLOSE c_get_all_elig_fac;
5536 END IF;
5537 IF (c_get_region_for_facility%ISOPEN) THEN
5538 CLOSE c_get_region_for_facility;
5539 END IF;
5540 IF (c_get_template_columns%ISOPEN) THEN
5541 CLOSE c_get_template_columns;
5542 END IF;
5543 IF (c_get_col_attrs%ISOPEN) THEN
5544 CLOSE c_get_col_attrs;
5545 END IF;
5546 IF (c_get_col_attr%ISOPEN) THEN
5547 CLOSE c_get_col_attr;
5548 END IF;
5549
5550 --
5551 -- Debug Statements
5552 --
5553 IF l_debug_on THEN
5554 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_NO_REGION_SPEC_R2');
5555 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_REGION_SPEC_R2 exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5556 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_REGION_SPEC_R2');
5557 WSH_DEBUG_SV.pop(l_module_name);
5558 END IF;
5559 --
5560 RETURN;
5561
5562 WHEN FTE_DIST_NO_ELIG_FACILI_R3 THEN
5563 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_ELIG_FACILI_R3');
5564 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5565 x_return_message := FND_MESSAGE.GET;
5566 WSH_UTIL_CORE.add_message(x_return_status);
5567 --
5568 -- Close the file
5569 --
5570 IF (utl_file.is_open(l_target_file)) THEN
5571 utl_file.fclose(l_target_file);
5572 END IF;
5573
5574
5575 --
5576 -- Close cursors
5577 --
5578 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5579 CLOSE c_get_distance_tab_pairs;
5580 END IF;
5581 IF (c_get_region_codes%ISOPEN) THEN
5582 CLOSE c_get_region_codes;
5583 END IF;
5584 IF (c_get_region_values%ISOPEN) THEN
5585 CLOSE c_get_region_values;
5586 END IF;
5587 IF (c_check_distance_table%ISOPEN) THEN
5588 CLOSE c_check_distance_table;
5589 END IF;
5590 IF (c_get_all_elig_fac%ISOPEN) THEN
5591 CLOSE c_get_all_elig_fac;
5592 END IF;
5593 IF (c_get_region_for_facility%ISOPEN) THEN
5594 CLOSE c_get_region_for_facility;
5595 END IF;
5596 IF (c_get_template_columns%ISOPEN) THEN
5597 CLOSE c_get_template_columns;
5598 END IF;
5599 IF (c_get_col_attrs%ISOPEN) THEN
5600 CLOSE c_get_col_attrs;
5601 END IF;
5602 IF (c_get_col_attr%ISOPEN) THEN
5603 CLOSE c_get_col_attr;
5604 END IF;
5605
5606 --
5607 -- Debug Statements
5608 --
5609 IF l_debug_on THEN
5610 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_NO_ELIG_FACILI_R3');
5611 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_ELIG_FACILI_R3 exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5612 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_ELIG_FACILI_R3');
5613 WSH_DEBUG_SV.pop(l_module_name);
5614 END IF;
5615 --
5616 RETURN;
5617
5618 WHEN FTE_DIST_NO_DWNLD_DIR THEN
5619 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_DWNLD_DIR');
5620 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5621 x_return_message := FND_MESSAGE.GET;
5622 WSH_UTIL_CORE.add_message(x_return_status);
5623 --
5624 -- Close the file
5625 --
5626 IF (utl_file.is_open(l_target_file)) THEN
5627 utl_file.fclose(l_target_file);
5628 END IF;
5629
5630 --
5631 -- Close cursors
5632 --
5633 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5634 CLOSE c_get_distance_tab_pairs;
5635 END IF;
5636 IF (c_get_region_codes%ISOPEN) THEN
5637 CLOSE c_get_region_codes;
5638 END IF;
5639 IF (c_get_region_values%ISOPEN) THEN
5640 CLOSE c_get_region_values;
5641 END IF;
5642 IF (c_check_distance_table%ISOPEN) THEN
5643 CLOSE c_check_distance_table;
5644 END IF;
5645 IF (c_get_all_elig_fac%ISOPEN) THEN
5646 CLOSE c_get_all_elig_fac;
5647 END IF;
5648 IF (c_get_region_for_facility%ISOPEN) THEN
5649 CLOSE c_get_region_for_facility;
5650 END IF;
5651 IF (c_get_template_columns%ISOPEN) THEN
5652 CLOSE c_get_template_columns;
5653 END IF;
5654 IF (c_get_col_attrs%ISOPEN) THEN
5655 CLOSE c_get_col_attrs;
5656 END IF;
5657 IF (c_get_col_attr%ISOPEN) THEN
5658 CLOSE c_get_col_attr;
5659 END IF;
5660
5661 --
5662 -- Debug Statements
5663 --
5664 IF l_debug_on THEN
5665 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_NO_DWNLD_DIR RAISED');
5666 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_DWNLD_DIR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5667 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_DWNLD_DIR');
5668 WSH_DEBUG_SV.pop(l_module_name);
5669 END IF;
5670 --
5671 RETURN;
5672
5673 WHEN FTE_DIST_COL_ZERO_START THEN
5674 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_COL_ZERO_START');
5675 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5676 x_return_message := FND_MESSAGE.GET;
5677 WSH_UTIL_CORE.add_message(x_return_status);
5678 --
5679 -- Close the file
5680 --
5681 IF (utl_file.is_open(l_target_file)) THEN
5682 utl_file.fclose(l_target_file);
5683 END IF;
5684
5685 --
5686 -- Close cursors
5687 --
5688 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5689 CLOSE c_get_distance_tab_pairs;
5690 END IF;
5691 IF (c_get_region_codes%ISOPEN) THEN
5692 CLOSE c_get_region_codes;
5693 END IF;
5694 IF (c_get_region_values%ISOPEN) THEN
5695 CLOSE c_get_region_values;
5696 END IF;
5697 IF (c_check_distance_table%ISOPEN) THEN
5698 CLOSE c_check_distance_table;
5699 END IF;
5700 IF (c_get_all_elig_fac%ISOPEN) THEN
5701 CLOSE c_get_all_elig_fac;
5702 END IF;
5703 IF (c_get_region_for_facility%ISOPEN) THEN
5704 CLOSE c_get_region_for_facility;
5705 END IF;
5706 IF (c_get_template_columns%ISOPEN) THEN
5707 CLOSE c_get_template_columns;
5708 END IF;
5709 IF (c_get_col_attrs%ISOPEN) THEN
5710 CLOSE c_get_col_attrs;
5711 END IF;
5712 IF (c_get_col_attr%ISOPEN) THEN
5713 CLOSE c_get_col_attr;
5714 END IF;
5715
5716 --
5717 -- Debug Statements
5718 --
5719 IF l_debug_on THEN
5720 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_COL_ZERO_START RAISED');
5721 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_COL_ZERO_START exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5722 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_COL_ZERO_START');
5723 WSH_DEBUG_SV.pop(l_module_name);
5724 END IF;
5725 --
5726 RETURN;
5727
5728
5729 WHEN utl_file.invalid_path THEN
5730 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INV_FILE_PATH');
5731 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5732 x_return_message := FND_MESSAGE.GET;
5733 WSH_UTIL_CORE.add_message(x_return_status);
5734 --
5735 -- Close the file
5736 --
5737 IF (utl_file.is_open(l_target_file)) THEN
5738 utl_file.fclose(l_target_file);
5739 END IF;
5740
5741 --
5742 -- Close cursors
5743 --
5744 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5745 CLOSE c_get_distance_tab_pairs;
5746 END IF;
5747 IF (c_get_region_codes%ISOPEN) THEN
5748 CLOSE c_get_region_codes;
5749 END IF;
5750 IF (c_get_region_values%ISOPEN) THEN
5751 CLOSE c_get_region_values;
5752 END IF;
5753 IF (c_check_distance_table%ISOPEN) THEN
5754 CLOSE c_check_distance_table;
5755 END IF;
5756 IF (c_get_all_elig_fac%ISOPEN) THEN
5757 CLOSE c_get_all_elig_fac;
5758 END IF;
5759 IF (c_get_region_for_facility%ISOPEN) THEN
5760 CLOSE c_get_region_for_facility;
5761 END IF;
5762 IF (c_get_template_columns%ISOPEN) THEN
5763 CLOSE c_get_template_columns;
5764 END IF;
5765 IF (c_get_col_attrs%ISOPEN) THEN
5766 CLOSE c_get_col_attrs;
5767 END IF;
5768 IF (c_get_col_attr%ISOPEN) THEN
5769 CLOSE c_get_col_attr;
5770 END IF;
5771
5772 --
5773 -- Debug Statements
5774 --
5775 IF l_debug_on THEN
5776 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.utl_file.invalid_path RAISED');
5777 WSH_DEBUG_SV.logmsg(l_module_name,'utl_file.invalid_path exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5778 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:utl_file.invalid_path');
5779 WSH_DEBUG_SV.pop(l_module_name);
5780 END IF;
5781 --
5782 RETURN;
5783
5784 WHEN utl_file.invalid_mode THEN
5785 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INV_FILE_MODE');
5786 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5787 x_return_message := FND_MESSAGE.GET;
5788 WSH_UTIL_CORE.add_message(x_return_status);
5789 --
5790 -- Close the file
5791 --
5792 IF (utl_file.is_open(l_target_file)) THEN
5793 utl_file.fclose(l_target_file);
5794 END IF;
5795
5796 --
5797 -- Close cursors
5798 --
5799 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5800 CLOSE c_get_distance_tab_pairs;
5801 END IF;
5802 IF (c_get_region_codes%ISOPEN) THEN
5803 CLOSE c_get_region_codes;
5804 END IF;
5805 IF (c_get_region_values%ISOPEN) THEN
5806 CLOSE c_get_region_values;
5807 END IF;
5808 IF (c_check_distance_table%ISOPEN) THEN
5809 CLOSE c_check_distance_table;
5810 END IF;
5811 IF (c_get_all_elig_fac%ISOPEN) THEN
5812 CLOSE c_get_all_elig_fac;
5813 END IF;
5814 IF (c_get_region_for_facility%ISOPEN) THEN
5815 CLOSE c_get_region_for_facility;
5816 END IF;
5817 IF (c_get_template_columns%ISOPEN) THEN
5818 CLOSE c_get_template_columns;
5819 END IF;
5820 IF (c_get_col_attrs%ISOPEN) THEN
5821 CLOSE c_get_col_attrs;
5822 END IF;
5823 IF (c_get_col_attr%ISOPEN) THEN
5824 CLOSE c_get_col_attr;
5825 END IF;
5826
5827 --
5828 -- Debug Statements
5829 --
5830 IF l_debug_on THEN
5831 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.utl_file.invalid_mode RAISED');
5832 WSH_DEBUG_SV.logmsg(l_module_name,'utl_file.invalid_mode exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5833 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:utl_file.invalid_mode');
5834 WSH_DEBUG_SV.pop(l_module_name);
5835 END IF;
5836 --
5837 RETURN;
5838
5839 WHEN utl_file.invalid_operation THEN
5840 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INV_FILE_OPERATION');
5841 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5842 x_return_message := FND_MESSAGE.GET;
5843 WSH_UTIL_CORE.add_message(x_return_status);
5844 --
5845 -- Close the file
5846 --
5847 IF (utl_file.is_open(l_target_file)) THEN
5848 utl_file.fclose(l_target_file);
5849 END IF;
5850
5851
5852 --
5853 -- Close cursors
5854 --
5855 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5856 CLOSE c_get_distance_tab_pairs;
5857 END IF;
5858 IF (c_get_region_codes%ISOPEN) THEN
5859 CLOSE c_get_region_codes;
5860 END IF;
5861 IF (c_get_region_values%ISOPEN) THEN
5862 CLOSE c_get_region_values;
5863 END IF;
5864 IF (c_check_distance_table%ISOPEN) THEN
5865 CLOSE c_check_distance_table;
5866 END IF;
5867 IF (c_get_all_elig_fac%ISOPEN) THEN
5868 CLOSE c_get_all_elig_fac;
5869 END IF;
5870 IF (c_get_region_for_facility%ISOPEN) THEN
5871 CLOSE c_get_region_for_facility;
5872 END IF;
5873 IF (c_get_template_columns%ISOPEN) THEN
5874 CLOSE c_get_template_columns;
5875 END IF;
5876 IF (c_get_col_attrs%ISOPEN) THEN
5877 CLOSE c_get_col_attrs;
5878 END IF;
5879 IF (c_get_col_attr%ISOPEN) THEN
5880 CLOSE c_get_col_attr;
5881 END IF;
5882
5883 --
5884 -- Debug Statements
5885 --
5886 IF l_debug_on THEN
5887 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.utl_file.invalid_operation RAISED');
5888 WSH_DEBUG_SV.logmsg(l_module_name,'utl_file.invalid_operation exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5889 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:utl_file.invalid_operation');
5890 WSH_DEBUG_SV.pop(l_module_name);
5891 END IF;
5892 --
5893 RETURN;
5894
5895 WHEN FTE_DIST_NO_RET_LENGTH THEN
5896 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_RET_LENGTH');
5897 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5898 x_return_message := FND_MESSAGE.GET;
5899 WSH_UTIL_CORE.add_message(x_return_status);
5900 --
5901 -- Close the file
5902 --
5903 IF (utl_file.is_open(l_target_file)) THEN
5904 utl_file.fclose(l_target_file);
5905 END IF;
5906
5907 --
5908 -- Close cursors
5909 --
5910 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5911 CLOSE c_get_distance_tab_pairs;
5912 END IF;
5913 IF (c_get_region_codes%ISOPEN) THEN
5914 CLOSE c_get_region_codes;
5915 END IF;
5916 IF (c_get_region_values%ISOPEN) THEN
5917 CLOSE c_get_region_values;
5918 END IF;
5919 IF (c_check_distance_table%ISOPEN) THEN
5920 CLOSE c_check_distance_table;
5921 END IF;
5922 IF (c_get_all_elig_fac%ISOPEN) THEN
5923 CLOSE c_get_all_elig_fac;
5924 END IF;
5925 IF (c_get_region_for_facility%ISOPEN) THEN
5926 CLOSE c_get_region_for_facility;
5927 END IF;
5928 IF (c_get_template_columns%ISOPEN) THEN
5929 CLOSE c_get_template_columns;
5930 END IF;
5931 IF (c_get_col_attrs%ISOPEN) THEN
5932 CLOSE c_get_col_attrs;
5933 END IF;
5934 IF (c_get_col_attr%ISOPEN) THEN
5935 CLOSE c_get_col_attr;
5936 END IF;
5937
5938 --
5939 -- Debug Statements
5940 --
5941 IF l_debug_on THEN
5942 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_NO_RET_LENGTH RAISED');
5943 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_RET_LENGTH exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
5944 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_RET_LENGTH');
5945 WSH_DEBUG_SV.pop(l_module_name);
5946 END IF;
5947 --
5948 RETURN;
5949
5950
5951 WHEN FTE_DIST_NO_RET_ATTR THEN
5952 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_RET_ATTR');
5953 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
5954 x_return_message := FND_MESSAGE.GET;
5955 WSH_UTIL_CORE.add_message(x_return_status);
5956 --
5957 -- Close the file
5958 --
5959 IF (utl_file.is_open(l_target_file)) THEN
5960 utl_file.fclose(l_target_file);
5961 END IF;
5962
5963 --
5964 -- Close cursors
5965 --
5966 IF (c_get_distance_tab_pairs%ISOPEN) THEN
5967 CLOSE c_get_distance_tab_pairs;
5968 END IF;
5969 IF (c_get_region_codes%ISOPEN) THEN
5970 CLOSE c_get_region_codes;
5971 END IF;
5972 IF (c_get_region_values%ISOPEN) THEN
5973 CLOSE c_get_region_values;
5974 END IF;
5975 IF (c_check_distance_table%ISOPEN) THEN
5976 CLOSE c_check_distance_table;
5977 END IF;
5978 IF (c_get_all_elig_fac%ISOPEN) THEN
5979 CLOSE c_get_all_elig_fac;
5980 END IF;
5981 IF (c_get_region_for_facility%ISOPEN) THEN
5982 CLOSE c_get_region_for_facility;
5983 END IF;
5984 IF (c_get_template_columns%ISOPEN) THEN
5985 CLOSE c_get_template_columns;
5986 END IF;
5987 IF (c_get_col_attrs%ISOPEN) THEN
5988 CLOSE c_get_col_attrs;
5989 END IF;
5990 IF (c_get_col_attr%ISOPEN) THEN
5991 CLOSE c_get_col_attr;
5992 END IF;
5993
5994 --
5995 -- Debug Statements
5996 --
5997 IF l_debug_on THEN
5998 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_NO_RET_ATTR RAISED');
5999 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_RET_ATTR exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6000 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_RET_ATTR');
6001 WSH_DEBUG_SV.pop(l_module_name);
6002 END IF;
6003 --
6004 RETURN;
6005
6006
6007 WHEN FTE_DIST_RET_DIST_INV_LENGTH THEN
6008 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_RET_DIST_INV_LENGTH');
6009 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6010 x_return_message := FND_MESSAGE.GET;
6011 WSH_UTIL_CORE.add_message(x_return_status);
6012 --
6013 -- Close the file
6014 --
6015 IF (utl_file.is_open(l_target_file)) THEN
6016 utl_file.fclose(l_target_file);
6017 END IF;
6018 --
6019 -- Close cursors
6020 --
6021 IF (c_get_distance_tab_pairs%ISOPEN) THEN
6022 CLOSE c_get_distance_tab_pairs;
6023 END IF;
6024 IF (c_get_region_codes%ISOPEN) THEN
6025 CLOSE c_get_region_codes;
6026 END IF;
6027 IF (c_get_region_values%ISOPEN) THEN
6028 CLOSE c_get_region_values;
6029 END IF;
6030 IF (c_check_distance_table%ISOPEN) THEN
6031 CLOSE c_check_distance_table;
6032 END IF;
6033 IF (c_get_all_elig_fac%ISOPEN) THEN
6034 CLOSE c_get_all_elig_fac;
6035 END IF;
6036 IF (c_get_region_for_facility%ISOPEN) THEN
6037 CLOSE c_get_region_for_facility;
6038 END IF;
6039 IF (c_get_template_columns%ISOPEN) THEN
6040 CLOSE c_get_template_columns;
6041 END IF;
6042 IF (c_get_col_attrs%ISOPEN) THEN
6043 CLOSE c_get_col_attrs;
6044 END IF;
6045 IF (c_get_col_attr%ISOPEN) THEN
6046 CLOSE c_get_col_attr;
6047 END IF;
6048
6049 --
6050 -- Debug Statements
6051 --
6052 IF l_debug_on THEN
6053 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_RET_DIST_INV_LENGTH RAISED');
6054 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_RET_DIST_INV_LENGTH exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6055 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_RET_DIST_INV_LENGTH');
6056 WSH_DEBUG_SV.pop(l_module_name);
6057 END IF;
6058 --
6059 RETURN;
6060
6061
6062
6063 WHEN FTE_DIST_RET_DIST_INV_START THEN
6064 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_RET_DIST_INV_START');
6065 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6066 x_return_message := FND_MESSAGE.GET;
6067 WSH_UTIL_CORE.add_message(x_return_status);
6068 --
6069 -- Close the file
6070 --
6071 IF (utl_file.is_open(l_target_file)) THEN
6072 utl_file.fclose(l_target_file);
6073 END IF;
6074 --
6075 -- Close cursors
6076 --
6077 IF (c_get_distance_tab_pairs%ISOPEN) THEN
6078 CLOSE c_get_distance_tab_pairs;
6079 END IF;
6080 IF (c_get_region_codes%ISOPEN) THEN
6081 CLOSE c_get_region_codes;
6082 END IF;
6083 IF (c_get_region_values%ISOPEN) THEN
6084 CLOSE c_get_region_values;
6085 END IF;
6086 IF (c_check_distance_table%ISOPEN) THEN
6087 CLOSE c_check_distance_table;
6088 END IF;
6089 IF (c_get_all_elig_fac%ISOPEN) THEN
6090 CLOSE c_get_all_elig_fac;
6091 END IF;
6092 IF (c_get_region_for_facility%ISOPEN) THEN
6093 CLOSE c_get_region_for_facility;
6094 END IF;
6095 IF (c_get_template_columns%ISOPEN) THEN
6096 CLOSE c_get_template_columns;
6097 END IF;
6098 IF (c_get_col_attrs%ISOPEN) THEN
6099 CLOSE c_get_col_attrs;
6100 END IF;
6101 IF (c_get_col_attr%ISOPEN) THEN
6102 CLOSE c_get_col_attr;
6103 END IF;
6104
6105 --
6106 -- Debug Statements
6107 --
6108 IF l_debug_on THEN
6109 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_RET_DIST_INV_START RAISED');
6110 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_RET_DIST_INV_START exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6111 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_RET_DIST_INV_START');
6112 WSH_DEBUG_SV.pop(l_module_name);
6113 END IF;
6114 --
6115 RETURN;
6116
6117
6118 WHEN FTE_DIST_RET_TIME_INV_LENGTH THEN
6119 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_RET_TIME_INV_LENGTH');
6120 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6121 x_return_message := FND_MESSAGE.GET;
6122 WSH_UTIL_CORE.add_message(x_return_status);
6123 --
6124 -- Close the file
6125 --
6126 IF (utl_file.is_open(l_target_file)) THEN
6127 utl_file.fclose(l_target_file);
6128 END IF;
6129 --
6130 -- Close cursors
6131 --
6132 IF (c_get_distance_tab_pairs%ISOPEN) THEN
6133 CLOSE c_get_distance_tab_pairs;
6134 END IF;
6135 IF (c_get_region_codes%ISOPEN) THEN
6136 CLOSE c_get_region_codes;
6137 END IF;
6138 IF (c_get_region_values%ISOPEN) THEN
6139 CLOSE c_get_region_values;
6140 END IF;
6141 IF (c_check_distance_table%ISOPEN) THEN
6142 CLOSE c_check_distance_table;
6143 END IF;
6144 IF (c_get_all_elig_fac%ISOPEN) THEN
6145 CLOSE c_get_all_elig_fac;
6146 END IF;
6147 IF (c_get_region_for_facility%ISOPEN) THEN
6148 CLOSE c_get_region_for_facility;
6149 END IF;
6150 IF (c_get_template_columns%ISOPEN) THEN
6151 CLOSE c_get_template_columns;
6152 END IF;
6153 IF (c_get_col_attrs%ISOPEN) THEN
6154 CLOSE c_get_col_attrs;
6155 END IF;
6156 IF (c_get_col_attr%ISOPEN) THEN
6157 CLOSE c_get_col_attr;
6158 END IF;
6159
6160 --
6161 -- Debug Statements
6162 --
6163 IF l_debug_on THEN
6164 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_RET_TIME_INV_LENGTH RAISED');
6165 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_RET_TIME_INV_LENGTH exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6166 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_RET_TIME_INV_LENGTH');
6167 WSH_DEBUG_SV.pop(l_module_name);
6168 END IF;
6169 --
6170 RETURN;
6171
6172
6173
6174
6175 WHEN FTE_DIST_RET_TIME_INV_START THEN
6176 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_RET_TIME_INV_START');
6177 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6178 x_return_message := FND_MESSAGE.GET;
6179 WSH_UTIL_CORE.add_message(x_return_status);
6180 --
6181 -- Close the file
6182 --
6183 IF (utl_file.is_open(l_target_file)) THEN
6184 utl_file.fclose(l_target_file);
6185 END IF;
6186 --
6187 -- Close cursors
6188 --
6189 IF (c_get_distance_tab_pairs%ISOPEN) THEN
6190 CLOSE c_get_distance_tab_pairs;
6191 END IF;
6192 IF (c_get_region_codes%ISOPEN) THEN
6193 CLOSE c_get_region_codes;
6194 END IF;
6195 IF (c_get_region_values%ISOPEN) THEN
6196 CLOSE c_get_region_values;
6197 END IF;
6198 IF (c_check_distance_table%ISOPEN) THEN
6199 CLOSE c_check_distance_table;
6200 END IF;
6201 IF (c_get_all_elig_fac%ISOPEN) THEN
6202 CLOSE c_get_all_elig_fac;
6203 END IF;
6204 IF (c_get_region_for_facility%ISOPEN) THEN
6205 CLOSE c_get_region_for_facility;
6206 END IF;
6207 IF (c_get_template_columns%ISOPEN) THEN
6208 CLOSE c_get_template_columns;
6209 END IF;
6210 IF (c_get_col_attrs%ISOPEN) THEN
6211 CLOSE c_get_col_attrs;
6212 END IF;
6213 IF (c_get_col_attr%ISOPEN) THEN
6214 CLOSE c_get_col_attr;
6215 END IF;
6216
6217 --
6218 -- Debug Statements
6219 --
6220 IF l_debug_on THEN
6221 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_RET_TIME_INV_START RAISED');
6222 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_RET_TIME_INV_START exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6223 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_RET_TIME_INV_START');
6224 WSH_DEBUG_SV.pop(l_module_name);
6225 END IF;
6226 --
6227 RETURN;
6228
6229
6230 WHEN FTE_DIST_NO_OD_PAIRS THEN
6231 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_OD_PAIRS');
6232 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6233 x_return_message := FND_MESSAGE.GET;
6234 WSH_UTIL_CORE.add_message(x_return_status);
6235 --
6236 -- Close the file
6237 --
6238 IF (utl_file.is_open(l_target_file)) THEN
6239 utl_file.fclose(l_target_file);
6240 END IF;
6241 --
6242 -- Close cursors
6243 --
6244 IF (c_get_distance_tab_pairs%ISOPEN) THEN
6245 CLOSE c_get_distance_tab_pairs;
6246 END IF;
6247 IF (c_get_region_codes%ISOPEN) THEN
6248 CLOSE c_get_region_codes;
6249 END IF;
6250 IF (c_get_region_values%ISOPEN) THEN
6251 CLOSE c_get_region_values;
6252 END IF;
6253 IF (c_check_distance_table%ISOPEN) THEN
6254 CLOSE c_check_distance_table;
6255 END IF;
6256 IF (c_get_all_elig_fac%ISOPEN) THEN
6257 CLOSE c_get_all_elig_fac;
6258 END IF;
6259 IF (c_get_region_for_facility%ISOPEN) THEN
6260 CLOSE c_get_region_for_facility;
6261 END IF;
6262 IF (c_get_template_columns%ISOPEN) THEN
6263 CLOSE c_get_template_columns;
6264 END IF;
6265 IF (c_get_col_attrs%ISOPEN) THEN
6266 CLOSE c_get_col_attrs;
6267 END IF;
6268 IF (c_get_col_attr%ISOPEN) THEN
6269 CLOSE c_get_col_attr;
6270 END IF;
6271
6272 --
6273 -- Debug Statements
6274 --
6275 IF l_debug_on THEN
6276 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_NO_OD_PAIRS RAISED');
6277 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_OD_PAIRS exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6278 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_OD_PAIRS');
6279 WSH_DEBUG_SV.pop(l_module_name);
6280 END IF;
6281 --
6282 RETURN;
6283
6284 WHEN FTE_DIST_NO_MATCH_REGIONS_FND THEN
6285 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_MATCH_REGIONS_FND');
6286 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6287 x_return_message := FND_MESSAGE.GET;
6288 WSH_UTIL_CORE.add_message(x_return_status);
6289 --
6290 -- Close the file
6291 --
6292 IF (utl_file.is_open(l_target_file)) THEN
6293 utl_file.fclose(l_target_file);
6294 END IF;
6295 --
6296 -- Close cursors
6297 --
6298 IF (c_get_distance_tab_pairs%ISOPEN) THEN
6299 CLOSE c_get_distance_tab_pairs;
6300 END IF;
6301 IF (c_get_region_codes%ISOPEN) THEN
6302 CLOSE c_get_region_codes;
6303 END IF;
6304 IF (c_get_region_values%ISOPEN) THEN
6305 CLOSE c_get_region_values;
6306 END IF;
6307 IF (c_check_distance_table%ISOPEN) THEN
6308 CLOSE c_check_distance_table;
6309 END IF;
6310 IF (c_get_all_elig_fac%ISOPEN) THEN
6311 CLOSE c_get_all_elig_fac;
6312 END IF;
6313 IF (c_get_region_for_facility%ISOPEN) THEN
6314 CLOSE c_get_region_for_facility;
6315 END IF;
6316 IF (c_get_template_columns%ISOPEN) THEN
6317 CLOSE c_get_template_columns;
6318 END IF;
6319 IF (c_get_col_attrs%ISOPEN) THEN
6320 CLOSE c_get_col_attrs;
6321 END IF;
6322 IF (c_get_col_attr%ISOPEN) THEN
6323 CLOSE c_get_col_attr;
6324 END IF;
6325
6326 --
6327 -- Debug Statements
6328 --
6329 IF l_debug_on THEN
6330 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_NO_MATCH_REGIONS_FND RAISED');
6331 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_NO_MATCH_REGIONS_FND exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6332 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_NO_MATCH_REGIONS_FND');
6333 WSH_DEBUG_SV.pop(l_module_name);
6334 END IF;
6335 --
6336 RETURN;
6337
6338 WHEN FTE_DIST_INV_REGION_LOW THEN
6339 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INV_REGION_LOW');
6340 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
6341 x_return_message := FND_MESSAGE.GET;
6342 WSH_UTIL_CORE.add_message(x_return_status);
6343 --
6344 -- Close the file
6345 --
6346 IF (utl_file.is_open(l_target_file)) THEN
6347 utl_file.fclose(l_target_file);
6348 END IF;
6349 --
6350 -- Close cursors
6351 --
6352 IF (c_get_distance_tab_pairs%ISOPEN) THEN
6353 CLOSE c_get_distance_tab_pairs;
6354 END IF;
6355 IF (c_get_region_codes%ISOPEN) THEN
6356 CLOSE c_get_region_codes;
6357 END IF;
6358 IF (c_get_region_values%ISOPEN) THEN
6359 CLOSE c_get_region_values;
6360 END IF;
6361 IF (c_check_distance_table%ISOPEN) THEN
6362 CLOSE c_check_distance_table;
6363 END IF;
6364 IF (c_get_all_elig_fac%ISOPEN) THEN
6365 CLOSE c_get_all_elig_fac;
6366 END IF;
6367 IF (c_get_region_for_facility%ISOPEN) THEN
6368 CLOSE c_get_region_for_facility;
6369 END IF;
6370 IF (c_get_template_columns%ISOPEN) THEN
6371 CLOSE c_get_template_columns;
6372 END IF;
6373 IF (c_get_col_attrs%ISOPEN) THEN
6374 CLOSE c_get_col_attrs;
6375 END IF;
6376 IF (c_get_col_attr%ISOPEN) THEN
6377 CLOSE c_get_col_attr;
6378 END IF;
6379 --
6380 -- Debug Statements
6381 --
6382 IF l_debug_on THEN
6383 WSH_DEBUG_SV.logmsg(l_module_name,'EXCEPTION FTE_DIST_DWNLD_PKG.FTE_DIST_INV_REGION_LOW RAISED');
6384 WSH_DEBUG_SV.logmsg(l_module_name,'FTE_DIST_INV_REGION_LOW exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
6385 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FTE_DIST_INV_REGION_LOW');
6386 WSH_DEBUG_SV.pop(l_module_name);
6387 END IF;
6388 --
6389 RETURN;
6390
6391
6392
6393 WHEN OTHERS THEN
6394 l_error_text := SQLERRM;
6395
6396 --
6397 -- Close the file
6398 --
6399 IF (utl_file.is_open(l_target_file)) THEN
6400 utl_file.fclose(l_target_file);
6401 END IF;
6402
6403
6404 --
6405 -- Close cursors
6406 --
6407 IF (c_get_distance_tab_pairs%ISOPEN) THEN
6408 CLOSE c_get_distance_tab_pairs;
6409 END IF;
6410 IF (c_get_region_codes%ISOPEN) THEN
6411 CLOSE c_get_region_codes;
6412 END IF;
6413 IF (c_get_region_values%ISOPEN) THEN
6414 CLOSE c_get_region_values;
6415 END IF;
6416 IF (c_check_distance_table%ISOPEN) THEN
6417 CLOSE c_check_distance_table;
6418 END IF;
6419 IF (c_get_all_elig_fac%ISOPEN) THEN
6420 CLOSE c_get_all_elig_fac;
6421 END IF;
6422 IF (c_get_region_for_facility%ISOPEN) THEN
6423 CLOSE c_get_region_for_facility;
6424 END IF;
6425 IF (c_get_template_columns%ISOPEN) THEN
6426 CLOSE c_get_template_columns;
6427 END IF;
6428 IF (c_get_col_attrs%ISOPEN) THEN
6429 CLOSE c_get_col_attrs;
6430 END IF;
6431 IF (c_get_col_attr%ISOPEN) THEN
6432 CLOSE c_get_col_attr;
6433 END IF;
6434
6435 --
6436 -- Debug Statements
6437 --
6438 IF l_debug_on THEN
6439 WSH_DEBUG_SV.logmsg(l_module_name, 'THE UNEXPECTED ERROR FROM FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILE IS ' ||l_error_text);
6440 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
6441 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
6442 END IF;
6443 --
6444 WSH_UTIL_CORE.default_handler('FTE_DIST_DWNLD_PKG.CREATE_DWNLD_FILE');
6445 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
6446 x_return_message := l_error_text;
6447
6448 RETURN;
6449
6450 END CREATE_DWNLD_FILE;
6451
6452 FUNCTION FIRST_TIME RETURN BOOLEAN IS
6453
6454 req_data VARCHAR2(100) := NULL;
6455
6456 BEGIN
6457
6458 req_data := FND_CONC_GLOBAL.request_data;
6459
6460 IF (req_data IS NULL) THEN
6461 RETURN TRUE;
6462 ELSE
6463 RETURN FALSE;
6464 END IF;
6465
6466
6467 EXCEPTION
6468 WHEN OTHERS THEN
6469 Fnd_File.Put_Line(Fnd_File.Log, 'Unexpected Error in Procedure FIRST_TIME' || sqlerrm);
6470
6471
6472 END FIRST_TIME;
6473
6474 END FTE_DIST_DWNLD_PKG;
[Home] [Help]