1 PACKAGE BODY FTE_BULK_DTT_PKG AS
2 /* $Header: FTEDISUB.pls 115.7 2004/01/27 00:41:21 ablundel noship $ */
3 -- -------------------------------------------------------------------------- --
4 -- --
5 -- NAME: FTE_BULK_DTT_PKG --
6 -- --
7 -- TYPE: PACKAGE BODY --
8 -- --
9 -- DESCRIPTION: Read a file containing DTT data from the directory specified --
10 -- in FTE_BULKLOAD_PKG.GET_DIRNAME and create location/mileage --
11 -- records in FTE_LOCATION_MILEAGES table --
12 -- --
13 -- PROCEDURE BULK_LOAD_DTT called from BulkLoadDataCO.java to --
14 -- start the loading process. submits a request to a concurrent --
15 -- program to execute the loading process --
16 -- --
17 -- PROCEDURE LOAD_DTT_FILE called by the concurrent program to --
18 -- execute the loading of the Distance and Transit Tine (DTT) --
19 -- file --
20 -- --
21 -- --
22 -- CHANGE CONTROL LOG --
23 -- --
24 -- DATE VERSION BY BUG DESCRIPTION --
25 -- ---------- ------- -------- ------- ---------------------------------- --
26 -- 2003/07/17 J ABLUNDEL Created. --
27 -- --
28 -- 2003/12/08 J ABLUNDEL 3301222 PROCEDURE: READ_DTT_FILE --
29 -- replaced the MERGE INTO statement --
30 -- with some code that is 8i compatibl--
31 -- as the MERGE statement can only be --
32 -- used with 8i or higher --
33 -- --
34 -- 2003/12/17 J ABLUNDEL 3325486 PROCEDURE: READ_DTT_FILE --
35 -- Added a REPLACE in the substring --
36 -- to remove tabs from the mileage and--
37 -- time return values. NB The TAB --
38 -- equates to 1 space --
39 -- --
40 -- 2004/01/22 J ABLUNDEL 3381771 PROCEDURE: READ_DTT_FILE --
41 -- Added a check to se if the input --
42 -- line is blank or contains only --
43 -- space or tab chars --
44 -- --
45 -- -------------------------------------------------------------------------- --
46
47 -- -------------------------------------------------------------------------- --
48 -- Global Package Variables --
49 -- ------------------------ --
50 -- --
51 -- -------------------------------------------------------------------------- --
52 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_BULK_DTT_PKG';
53 g_user_id CONSTANT NUMBER := FND_GLOBAL.USER_ID;
54
55 g_bulk_insert_limit CONSTANT NUMBER := 250;
56 g_total_numcharts NUMBER;
57 g_chart_count_temp NUMBER := 0;
58
59 g_valid_date DATE;
60 g_valid_date_string VARCHAR2(20);
61 g_scac VARCHAR2(10);
62
63 g_ret_dist_col_name CONSTANT VARCHAR2(30) := 'RETURNDIST';
64 g_ret_time_col_name CONSTANT VARCHAR2(30) := 'RETURNTIME';
65
66
67 -- AXE
68 g_time_hour CONSTANT VARCHAR2(4) := 'Hour';
69 g_time_minute CONSTANT VARCHAR2(6) := 'Minute';
70 g_distance_mile CONSTANT VARCHAR2(4) := 'Mile';
71 g_distance_kilometer CONSTANT VARCHAR2(9) := 'Kilometer';
72
73
74 -- -------------------------------------------------------------------------- --
75 -- --
76 -- NAME: PROCEDURE BULK_LOAD_DTT --
77 -- --
78 -- TYPE: PROCEDURE --
79 -- --
80 -- PARAMETERS (IN OUT): p_load_id IN NUMBER The load id of the job --
81 -- p_src_filename IN VARCHAR2 --
82 -- p_resp_id IN NUMBER --
83 -- p_resp_appl_id IN NUMBER --
84 -- p_user_id IN NUMBER --
85 -- p_user_debug IN NUMBER --
86 -- --
87 -- PARAMETERS (OUT): x_request_id: The request id of the bulkload process --
88 -- x_error_msg_text: --
89 -- --
90 -- RETURN: n/a --
91 -- --
92 -- DESCRIPTION: Purpose This is the starting point of the bulkloading --
93 -- process. Submits a request to a concurrent program, --
94 -- that starts the location/mileage loading process --
95 -- Called from $fte/java/catalog/BulkLoadDataCO.java for --
96 -- Uploading DTT file --
97 -- --
98 -- --
99 -- CHANGE CONTROL LOG --
100 -- ------------------ --
101 -- --
102 -- DATE VERSION BY BUG DESCRIPTION --
103 -- ---------- ------- -------- ------- ---------------------------------- --
104 -- 2003/07/17 J ABLUNDEL Created --
105 -- --
106 -- -------------------------------------------------------------------------- --
107 PROCEDURE BULK_LOAD_DTT(p_load_id IN NUMBER,
108 p_src_filename IN VARCHAR2,
109 p_resp_id IN NUMBER,
110 p_resp_appl_id IN NUMBER,
111 p_user_id IN NUMBER,
112 p_user_debug IN NUMBER,
113 x_request_id OUT NOCOPY NUMBER,
114 x_error_msg_text OUT NOCOPY VARCHAR2) IS
115
116
117
118 x_src_filedir VARCHAR2(100);
119 l_debug_on BOOLEAN;
120 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' ||'BULK_LOAD_DTT';
121 l_error_text VARCHAR2(2000);
122
123
124 BEGIN
125
126
127 g_user_debug := p_user_debug;
128 --
129 -- SETUP DEBUGGING
130 --
131 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
132 IF l_debug_on IS NULL THEN
133 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
134 END IF;
135
136 IF (g_user_debug = 1) THEN
137 l_debug_on := TRUE;
138 END IF;
139
140
141 IF l_debug_on THEN
142 WSH_DEBUG_SV.push(l_module_name);
143 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
144 WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
145 WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
146 WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
147 WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
148 WSH_DEBUG_SV.log(l_module_name,'p_resp_id',p_resp_id);
149 WSH_DEBUG_SV.log(l_module_name,'p_resp_appl_id',p_resp_appl_id);
150 WSH_DEBUG_SV.log(l_module_name,'p_user_id',p_user_id);
151 WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
152 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
153 END IF;
154
155
156 IF l_debug_on THEN
157 WSH_DEBUG_SV.logmsg(l_module_name,'Calling fnd_global.apps_initialize with p_user_id,p_resp_id,p_resp_appl_id');
158 END IF;
159
160 fnd_global.apps_initialize(user_id => p_user_id,
161 resp_id => p_resp_id,
162 resp_appl_id => p_resp_appl_id);
163
164
165
166 IF l_debug_on THEN
167 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FTE_BULKLOAD_PKG.GET_UPLOAD_DIR to get the file directory');
168 END IF;
169
170
171 x_src_filedir := FTE_BULKLOAD_PKG.GET_UPLOAD_DIR;
172
173
174 IF l_debug_on THEN
175 WSH_DEBUG_SV.log(l_module_name,'x_src_filedir = ',x_src_filedir);
176 END IF;
177
178
179 IF l_debug_on THEN
180 WSH_DEBUG_SV.logmsg(l_module_name,'Calling FND_REQUEST.SUBMIT_REQUEST to submoit the request to the concurrent program');
181 END IF;
182
183
184 x_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'FTE',
185 program => 'FTE_BULK_DTT_LOADER',
186 description => null,
187 start_time => null,
188 sub_request => false,
189 argument1 => p_load_id,
190 argument2 => p_src_filename,
191 argument3 => x_src_filedir,
192 argument4 => p_user_debug);
193
194 IF l_debug_on THEN
195 WSH_DEBUG_SV.log(l_module_name,'x_request_id = ',x_request_id);
196 END IF;
197
198
199 x_error_msg_text := fnd_message.get;
200
201 commit;
202
203 IF l_debug_on THEN
204 WSH_DEBUG_SV.log(l_module_name, 'x_error_msg_text', x_error_msg_text);
205 WSH_DEBUG_SV.log(l_module_name, 'x_src_filedir', x_src_filedir);
206 WSH_DEBUG_SV.log(l_module_name, 'x_request_id', x_request_id);
207 WSH_DEBUG_SV.log(l_module_name, 'p_user_id', p_user_id);
208 WSH_DEBUG_SV.log(l_module_name, 'p_resp_id', p_resp_id);
209 WSH_DEBUG_SV.log(l_module_name, 'p_resp_appl_id', p_resp_appl_id);
210 WSH_DEBUG_SV.pop(l_module_name);
211 END IF;
212
213 RETURN;
214
215 EXCEPTION
216 WHEN OTHERS THEN
217 l_error_text := sqlerrm;
218 Fnd_File.Put_Line(Fnd_File.Log, 'Unexpected Error in Procedure BULK_LOAD_DTT' || sqlerrm);
219
220 --
221 -- Debug Statements
222 --
223 IF l_debug_on THEN
224 WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.BULK_LOAD_DTT IS ' ||l_error_text);
225 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||l_error_text,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
226 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
227 END IF;
228
229 RETURN;
230
231
232 END BULK_LOAD_DTT;
233
234
235
236
237 -- -------------------------------------------------------------------------- --
238 -- --
239 -- NAME: PROCEDURE LOAD_DTT_FILE --
240 -- --
241 -- TYPE: PROCEDURE --
242 -- --
243 -- PARAMETERS (IN OUT): p_load_id IN NUMBER The load id of the job --
244 -- p_src_filename IN VARCHAR2 --
245 -- p_src_filedir IN VARCHAR2 --
246 -- p_user_debug IN NUMBER --
247 -- --
248 -- 1. p_load_id: The load id of the bulkload job. --
249 -- 2. p_src_filename: The filename of the file containing--
250 -- the DTT data. --
251 -- 3. p_src_filedir: The directory containing the DTT --
252 -- data file. There should be no --
253 -- trailing '/', and this directory --
254 -- should be readable by UTL_FILE --
255 -- 4. p_user_debug: turns the debugger on --
256 -- --
257 -- PARAMETERS (OUT): p_errbuf: A buffer of error messages --
258 -- p_retcode: The return code. A return code of '2' --
259 -- specifies ERROR --
260 -- --
261 -- RETURN: n/a --
262 -- --
263 -- DESCRIPTION: Runs the entire DTT Bulkloading process --
264 -- Called from the FTE_BULK_DTT_LOADER concurrent program--
265 -- --
266 -- --
267 -- CHANGE CONTROL LOG --
268 -- ------------------ --
269 -- --
270 -- DATE VERSION BY BUG DESCRIPTION --
271 -- ---------- ------- -------- ------- ---------------------------------- --
272 -- 2003/07/17 J ABLUNDEL Created --
273 -- --
274 -- -------------------------------------------------------------------------- --
275 PROCEDURE LOAD_DTT_FILE(p_errbuf OUT NOCOPY VARCHAR2,
276 p_retcode OUT NOCOPY VARCHAR2,
277 p_load_id IN NUMBER,
278 p_src_filename IN VARCHAR2,
279 p_src_filedir IN VARCHAR2,
280 p_user_debug IN NUMBER) IS
281
282
283
284 g_first_time BOOLEAN;
285 x_status NUMBER;
286
287 l_debug_on BOOLEAN;
288 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' ||'LOAD_DTT_FILE';
289 l_error_text VARCHAR2(2000);
290
291
292 BEGIN
293
294 FND_FILE.PUT_LINE(FND_FILE.log, 'LOAD_DTT_FILE');
295
296 x_status := -1;
297 g_user_debug := p_user_debug;
298 g_first_time := FIRST_TIME;
299
300 --
301 -- SETUP DEBUGGING
302 --
303 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
304 IF l_debug_on IS NULL THEN
305 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
306 END IF;
307
308 IF (g_user_debug = 1) THEN
309 l_debug_on := TRUE;
310 END IF;
311
312
313 --
314 -- Debug Statements
315 --
316 IF l_debug_on THEN
317 WSH_DEBUG_SV.push(l_module_name);
318 WSH_DEBUG_SV.logmsg(l_module_name,'-------LOAD_DTT_FILE-------');
319 WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
320 WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
321 WSH_DEBUG_SV.log(l_module_name,'p_src_filename',p_src_filename);
322 WSH_DEBUG_SV.log(l_module_name,'p_src_filedir',p_src_filedir);
323 WSH_DEBUG_SV.log(l_module_name,'p_user_debug',p_user_debug);
324 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
325 END IF;
326
327
328 --
329 -- Debug Statements
330 --
331 IF l_debug_on THEN
332 WSH_DEBUG_SV.logmsg(l_module_name,'Check If this is the first time by calling function FIRST_TIME');
333 END IF;
334
335 IF (FIRST_TIME) THEN
336
337 --
338 -- Debug Statements
339 --
340 IF l_debug_on THEN
341 WSH_DEBUG_SV.logmsg(l_module_name,'This is the first time calling FTE_BULK_DTT_PKG.READ_DTT_FILE');
342 WSH_DEBUG_SV.logmsg(l_module_name,'with parameters: p_src_filedir,p_src_filename,p_load_id');
343 END IF;
344
345 FND_FILE.PUT_LINE(FND_FILE.log, 'Calling READ_DTT_FILE');
346
347 FTE_BULK_DTT_PKG.READ_DTT_FILE(p_src_filedir,
348 p_src_filename,
349 p_load_id,
350 p_errbuf,
351 x_status);
352
353 --
354 -- Debug Statements
355 --
356 IF l_debug_on THEN
357 WSH_DEBUG_SV.logmsg(l_module_name,'Back from FTE_BULK_DTT_PKG.READ_DTT_FILE');
358 WSH_DEBUG_SV.log(l_module_name,'x_status',x_status);
359 END IF;
360
361 END IF;
362
363 IF (x_status = -1) THEN
364 --
365 -- Debug Statements
366 --
367 IF l_debug_on THEN
368 WSH_DEBUG_SV.logmsg(l_module_name,'The file Reading and upload was successful');
369 END IF;
370
371 --
372 -- Concurrent Manager expects 0 for success.
373 --
374 p_retcode := 0;
375 p_errbuf := 'COMPLETED DTT LOADING SUCCESSFULLY';
376 ELSE
377 --
378 -- Debug Statements
379 --
380 IF l_debug_on THEN
381 WSH_DEBUG_SV.logmsg(l_module_name,'Errors occurred during the upload');
382 END IF;
383
384 p_retcode := 2;
385 p_errbuf := 'COMPLETED WITH ERRORS. ' || p_errbuf || '. Please Check Logs for more details.';
386 END IF;
387
388
389 --
390 -- Debug Statements
391 --
392 IF l_debug_on THEN
393 WSH_DEBUG_SV.pop(l_module_name);
394 END IF;
395 --
396 RETURN;
397
398
399 EXCEPTION
400 WHEN OTHERS THEN
401 l_error_text := SQLERRM;
402 FND_FILE.PUT_LINE(FND_FILE.LOG, '*****ERROR**** '||l_error_text);
403 p_retcode := 2;
404 p_errbuf := p_errbuf || sqlerrm;
405
406 --
407 -- Debug Statements
408 --
409 IF l_debug_on THEN
410 WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.LOAD_DTT_FILE IS ' ||l_error_text);
411 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||l_error_text,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
412 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
413 END IF;
414
415 RETURN;
416
417
418 END LOAD_DTT_FILE;
419
420
421
422 -- -------------------------------------------------------------------------- --
423 -- --
424 -- NAME: READ_DTT_FILE --
425 -- --
426 -- TYPE: PROCEDURE --
427 -- --
428 -- PARAMETERS (IN OUT): p_source_file_directory IN VARCHAR2 --
429 -- p_source_file_name IN VARCHAR2 --
430 -- p_load_id IN VARCHAR2 --
431 -- --
432 -- PARAMETERS (OUT): x_return_message OUT NOCOPY VARCHAR2 --
433 -- x_return_status OUT NOCOPY NUMBER --
434 -- --
435 -- RETURN: n/a --
436 -- --
437 -- DESCRIPTION: Reads the DTT data file. Gets the relevant origin and --
438 -- destination ids from fte_mile_download_lines and --
439 -- stores the information in a global temp table: --
440 -- FTE_DISTANCE_LOADER_TMP and then inserts or updates --
441 -- the values into the FTE_LOCATION_MILEAGES table --
442 -- --
443 -- CHANGE CONTROL LOG --
444 -- ------------------ --
445 -- --
446 -- DATE VERSION BY BUG DESCRIPTION --
447 -- ---------- ------- -------- ------- ---------------------------------- --
448 -- 2003/07/17 J ABLUNDEL Created --
449 -- --
450 -- 2003/12/08 J ABLUNDEL 3301222 replaced the MERGE INTO statement --
451 -- with some code that is 8i compatibl--
452 -- as the MERGE statement can only be --
453 -- used with 8i or higher --
454 -- --
455 -- 2003/12/17 J ABLUNDEL 3325486 Added a REPLACE in the substring --
456 -- to remove tabs from the mileage and--
457 -- time return values. NB The TAB --
458 -- equates to 1 space --
459 -- --
460 -- 2004/01/22 J ABLUNDEL 3381771 Added a check to se if the input --
461 -- line is blank or contains only --
462 -- space or tab chars --
463 -- --
464 -- -------------------------------------------------------------------------- --
465 PROCEDURE READ_DTT_FILE(p_source_file_directory IN VARCHAR2,
466 p_source_file_name IN VARCHAR2,
467 p_load_id IN VARCHAR2,
468 x_return_message OUT NOCOPY VARCHAR2,
469 x_return_status OUT NOCOPY NUMBER) IS
470
471
472
473 l_upload_date DATE;
474 l_dtt_file UTL_FILE.file_type;
475 l_line VARCHAR2(2000);
476 l_load_ctr PLS_INTEGER;
477 l_cur_date DATE;
478 l_line_ctr PLS_INTEGER;
479 l_dtt_file_name VARCHAR2(50);
480 l_download_file_id NUMBER;
481 l_template_id NUMBER;
482 l_download_date DATE;
483 l_download_count NUMBER;
484 l_ret_dist_col_id NUMBER;
485 l_ret_dist_start_pos NUMBER;
486 l_ret_dist_length NUMBER;
487 l_ret_dist_enabled_flag VARCHAR2(1);
488 l_ret_dist_return_format VARCHAR2(10);
489 l_ret_dist_db_uom VARCHAR2(3);
490 l_ret_time_col_id NUMBER;
491 l_ret_time_start_pos NUMBER;
492 l_ret_time_length NUMBER;
493 l_ret_time_enabled_flag VARCHAR2(1);
494 l_ret_time_return_format VARCHAR2(10);
495 l_ret_time_db_uom VARCHAR2(3);
496 l_identifier_type VARCHAR2(30);
497 l_conv_flag VARCHAR2(10);
498 l_ret_time_val VARCHAR2(50);
499 l_ret_dist_val VARCHAR2(50);
500 l_ret_time NUMBER;
501 l_ret_dist NUMBER;
502 l_ret_mins NUMBER;
503 l_ret_hrs NUMBER;
504 l_tmp_count PLS_INTEGER;
505 l_number_of_table_lines NUMBER;
506
507
508
509 l_origin_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
510 l_destination_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
511 l_location_origin_id FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
512 l_location_destination_id FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
513 l_location_identifier_type FTE_BULK_DTT_PKG.fte_distu_tmp_code_table;
514 l_location_distance FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
515 l_location_distance_uom FTE_BULK_DTT_PKG.fte_distu_tmp_uom_table;
516 l_location_transit_time FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
517 l_location_transit_time_uom FTE_BULK_DTT_PKG.fte_distu_tmp_uom_table;
518 l_location_creation_date FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
519 l_location_created_by FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
520 l_location_last_update_date FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
521 l_location_last_updated_by FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
522 l_location_last_update_login FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
523 l_location_program_app_id FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
524 l_location_program_id FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
525 l_location_program_update_date FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
526 l_location_request_id FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
527 l_deleted_download_ids FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
528
529
530
531 cursor c_check_download_lines(cp_download_file_id NUMBER) IS
532 select count(fmdl.download_file_id)
533 from fte_mile_download_lines fmdl
534 where fmdl.download_file_id = cp_download_file_id;
535
536
537 cursor c_get_file_info(cp_file_name VARCHAR2) IS
538 select fmdf.download_file_id,
539 fmdf.template_id,
540 fmdf.download_date,
541 fmdf.upload_date,
542 fmdf.identifier_type
543 from fte_mile_download_files fmdf
544 where fmdf.file_name = cp_file_name;
545
546
547 cursor c_get_download_lines(cp_download_file_id NUMBER) IS
548 select fmdl.origin_id,
549 fmdl.destination_id
550 from fte_mile_download_lines fmdl
551 where fmdl.download_file_id = cp_download_file_id;
552
553
554 cursor c_get_ret_col_info(cp_column_type VARCHAR2,
555 cp_template_id NUMBER) IS
556 select fmtc.column_id,
557 fmtc.start_position,
558 fmtc.length
559 from fte_mile_template_columns fmtc
560 where fmtc.column_type = cp_column_type
561 and fmtc.template_id = cp_template_id;
562
563
564 cursor c_get_ret_enabled(cp_column_id NUMBER) IS
565 select fmca.enabled_flag,
566 fmca.return_format,
567 fmca.db_uom
568 from fte_mile_column_attributes fmca
569 where fmca.column_id = cp_column_id;
570
571
572
573 FTE_DIST_NULL_FILE_NAME EXCEPTION;
574 FTE_DIST_INV_FILENAME_LGTH EXCEPTION;
575 FTE_DIST_NO_FILE_DOWNLOAD EXCEPTION;
576 FTE_DIST_NO_FILE_TEMPLATE EXCEPTION;
577 FTE_DIST_NO_FILE_DOWNLOAD_DATE EXCEPTION;
578 FTE_DIST_FILE_UPLOAD_DONE_PREV EXCEPTION;
579 FTE_DIST_NO_DOWNLOAD_LINES EXCEPTION;
580 FTE_DIST_RET_DIST_COL_NO_DATA EXCEPTION;
581 FTE_DIST_RET_TIME_COL_NO_DATA EXCEPTION;
582 FTE_DIST_NO_RET_COL_ENABLED EXCEPTION;
583 FTE_DIST_LESS_FILE_LINES EXCEPTION;
584 FTE_DIST_MANY_FILE_LINES EXCEPTION;
585
586
587
588
589 l_debug_on BOOLEAN;
590 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' ||'READ_DTT_FILE';
591 l_error_text VARCHAR2(2000);
592
593
594
595 --
596 -- [ABLUNDEL][12/08/2003][BUG# 3301222]
597 --
598 -- The following variable declarations were added to support the bug fix for the
599 -- 8i - 9i compatibility problem with the MERGE statement
600 --
601 --
602 cursor c_get_merge_data is
603 select tt.origin_id,
604 tt.destination_id,
605 fdlt.origin_id,
606 fdlt.destination_id,
607 fdlt.identifier_type,
608 fdlt.distance,
609 fdlt.distance_uom,
610 fdlt.transit_time,
611 fdlt.transit_time_uom,
612 fdlt.creation_date,
613 fdlt.created_by,
614 fdlt.last_update_date,
615 fdlt.last_updated_by,
616 fdlt.last_update_login,
617 fdlt.program_application_id,
618 fdlt.program_id,
619 fdlt.program_update_date,
620 fdlt.request_id
621 from (SELECT origin_id,
622 destination_id,
623 identifier_type,
624 distance,
625 distance_uom,
626 transit_time,
627 transit_time_uom,
628 creation_date,
629 created_by,
630 last_update_date,
631 last_updated_by,
632 last_update_login,
633 program_application_id,
634 program_id,
635 program_update_date,
636 request_id FROM FTE_DISTANCE_LOADER_TMP) fdlt,
637 FTE_LOCATION_MILEAGES tt
638 where fdlt.origin_id = tt.origin_id(+)
639 and fdlt.destination_id = tt.destination_id(+);
640
641
642 l_u_origin_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
643 l_u_destination_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
644 l_u_identifier_type_tab FTE_BULK_DTT_PKG.fte_distu_tmp_code_table;
645 l_u_distance_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
646 l_u_distance_uom_tab FTE_BULK_DTT_PKG.fte_distu_tmp_uom_table;
647 l_u_transit_time_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
648 l_u_transit_time_uom_tab FTE_BULK_DTT_PKG.fte_distu_tmp_uom_table;
649 l_u_last_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
650 l_u_last_updated_by_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
651 l_u_last_update_login_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
652 l_u_program_app_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
653 l_u_program_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
654 l_u_program_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
655 l_u_request_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
656
657 l_i_origin_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
658 l_i_destination_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
659 l_i_identifier_type_tab FTE_BULK_DTT_PKG.fte_distu_tmp_code_table;
660 l_i_distance_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
661 l_i_distance_uom_tab FTE_BULK_DTT_PKG.fte_distu_tmp_uom_table;
662 l_i_transit_time_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
663 l_i_transit_time_uom_tab FTE_BULK_DTT_PKG.fte_distu_tmp_uom_table;
664 l_i_creation_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
665 l_i_created_by_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
666 l_i_last_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
667 l_i_last_updated_by_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
668 l_i_last_update_login_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
669 l_i_program_app_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
670 l_i_program_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
671 l_i_program_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
672 l_i_request_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
673
674 l_old_origin_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
675 l_old_destination_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
676 l_new_origin_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
677 l_new_destination_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
678 l_new_identifier_type_tab FTE_BULK_DTT_PKG.fte_distu_tmp_code_table;
679 l_new_distance_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
680 l_new_distance_uom_tab FTE_BULK_DTT_PKG.fte_distu_tmp_uom_table;
681 l_new_transit_time_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
682 l_new_transit_time_uom_tab FTE_BULK_DTT_PKG.fte_distu_tmp_uom_table;
683 l_new_creation_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
684 l_new_created_by_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
685 l_new_last_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
686 l_new_last_updated_by_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
687 l_new_last_update_login_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
688 l_new_program_app_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
689 l_new_program_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
690 l_new_program_update_date_tab FTE_BULK_DTT_PKG.fte_distu_tmp_date_table;
691 l_new_request_id_tab FTE_BULK_DTT_PKG.fte_distu_tmp_num_table;
692
693
694 l_insert_ctr PLS_INTEGER;
695 l_update_ctr PLS_INTEGER;
696 l_current_rows PLS_INTEGER;
697 l_remaining_rows PLS_INTEGER;
698 l_previous_rows PLS_INTEGER;
699 l_bulk_collect_size PLS_INTEGER := 500;
700
701
702
703 FTE_DIST_ORIG_DEST_LOAD_ERR EXCEPTION;
704 --
705 --
706 -- [ABLUNDEL][12/08/2003][BUG# 3301222] End of new variable declaration additions
707 --
708
709 --
710 -- [ABLUNDEL][12/08/2003][BUG# ]AXE
711 -- New cursors/variables to get the correct Time and Distance Uom
712 --
713 cursor c_get_time_uom(x_unit_of_measure VARCHAR2,
714 x_language VARCHAR2) IS
715 select muomv.uom_code
716 from mtl_units_of_measure_vl muomv,
717 wsh_global_parameters wgp
718 where wgp.gu_time_class = muomv.uom_class
719 and muomv.unit_of_measure = x_unit_of_measure
720 and muomv.language = x_language;
721
722 cursor c_get_distance_uom(y_unit_of_measure VARCHAR2,
723 y_language VARCHAR2) IS
724 select muomv.uom_code
725 from mtl_units_of_measure_vl muomv,
726 wsh_global_parameters wgp
727 where wgp.GU_DISTANCE_CLASS = muomv.uom_class
728 and muomv.UNIT_OF_MEASURE = y_unit_of_measure
729 and muomv.language = y_language;
730
731
732 l_time_uom_code VARCHAR2(50);
733 l_time_unit_of_measure VARCHAR2(50);
734 l_distance_uom_code VARCHAR2(50);
735 l_distance_unit_of_measure VARCHAR2(50);
736 l_language VARCHAR2(50);
737
738 FTE_DIST_INV_TEMP_TIME_UOM EXCEPTION;
739 FTE_DIST_NO_TIME_UOM EXCEPTION;
740 FTE_DIST_INV_TEMP_DIST_UOM EXCEPTION;
741 FTE_DIST_NO_DISTANCE_UOM EXCEPTION;
742 --
743 --
744 --
745
746
747
748
749 l_line_length NUMBER;
750 l_colon_check PLS_INTEGER;
751
752 FTE_DIST_INV_LINE_LENGTH EXCEPTION;
753 FTE_DIST_INV_TIME_FORMAT EXCEPTION;
754
755
756
757 BEGIN
758
759 --
760 -- SETUP DEBUGGING
761 --
762 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
763 IF l_debug_on IS NULL THEN
764 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
765 END IF;
766
767 IF (g_user_debug = 1) THEN
768 l_debug_on := TRUE;
769 END IF;
770
771
772 IF l_debug_on THEN
773 WSH_DEBUG_SV.push(l_module_name);
774 WSH_DEBUG_SV.logmsg(l_module_name,'---------------READ_DTT_FILE------------');
775 WSH_DEBUG_SV.logmsg(l_module_name,'-------- INPUT PARAMETERS ------');
776 WSH_DEBUG_SV.log(l_module_name,'Procedure = ',l_module_name);
777 WSH_DEBUG_SV.log(l_module_name,'p_source_file_directory',p_source_file_directory);
778 WSH_DEBUG_SV.log(l_module_name,'p_source_file_name',p_source_file_name);
779 WSH_DEBUG_SV.log(l_module_name,'p_load_id',p_load_id);
780 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
781 END IF;
782
783
784 FND_FILE.PUT_LINE(FND_FILE.log, 'READ_DTT_FILE');
785
786 --
787 -- Set the date variable
788 --
789 l_cur_date := sysdate;
790
791 --
792 -- Set the language
793 --
794 l_language := userenv('LANG');
795
796 --
797 -- Check the input file name is not null
798 --
799 IF (p_source_file_name is null) THEN
800 --
801 -- Input file name is null raise an error
802 --
803 IF l_debug_on THEN
804 WSH_DEBUG_SV.logmsg(l_module_name,'Input file name is null raise FTE_DIST_NULL_FILE_NAME exception');
805 END IF;
806
807 RAISE FTE_DIST_NULL_FILE_NAME;
808 --
809 -- Debug Statements
810 --
811 IF l_debug_on THEN
812 WSH_DEBUG_SV.pop(l_module_name);
813 END IF;
814 --
815 RETURN;
816 END IF;
817
818
819
820 --
821 -- Take off the file extension
822 --
823 IF l_debug_on THEN
824 WSH_DEBUG_SV.logmsg(l_module_name,'Take off the file extension');
825 END IF;
826
827 FND_FILE.PUT_LINE(FND_FILE.log, 'Removing File Extension from file');
828
829 l_dtt_file_name := substr(p_source_file_name,1,(instr(p_source_file_name,'.')-1));
830
831 --
832 -- DTT Upload file should be 8 chars in length
833 --
834 IF (length(l_dtt_file_name) <> 8) THEN
835
836 IF l_debug_on THEN
837 WSH_DEBUG_SV.logmsg(l_module_name,'Input file name is not 8 chars in length raise FTE_DIST_INV_FILENAME_LGTH exception');
838 END IF;
839
840 RAISE FTE_DIST_INV_FILENAME_LGTH;
841
842 --
843 -- Debug Statements
844 --
845 IF l_debug_on THEN
846 WSH_DEBUG_SV.pop(l_module_name);
847 END IF;
848 --
849 RETURN;
850 END IF;
851
852
853 FND_FILE.PUT_LINE(FND_FILE.log, 'Retrieving the download file id');
854 --
855 IF l_debug_on THEN
856 WSH_DEBUG_SV.log(l_module_name,'Get the download file id open cursor c_get_file_info l_dtt_file_name = ',l_dtt_file_name);
857 END IF;
858 --
859 -- Get the download file id
860 --
861 OPEN c_get_file_info(l_dtt_file_name);
862 FETCH c_get_file_info INTO l_download_file_id,
863 l_template_id,
864 l_download_date,
865 l_upload_date,
866 l_identifier_type;
867 CLOSE c_get_file_info;
868
869
870 IF (l_download_file_id is null) THEN
871 --
872 -- No download took place for this file
873 --
874 IF l_debug_on THEN
875 WSH_DEBUG_SV.logmsg(l_module_name,'Download file id is null, RAISE FTE_DIST_NO_FILE_DOWNLOAD exception');
876 END IF;
877 --
878 RAISE FTE_DIST_NO_FILE_DOWNLOAD;
879 --
880 IF l_debug_on THEN
881 WSH_DEBUG_SV.pop(l_module_name);
882 END IF;
883 --
884 RETURN;
885 END IF;
886
887 IF (l_template_id is null) THEN
888 --
889 -- There is no template associated with the file
890 --
891 --
892 IF l_debug_on THEN
893 WSH_DEBUG_SV.logmsg(l_module_name,'There is no template associated with the file, RAISE FTE_DIST_NO_FILE_TEMPLATE exception');
894 END IF;
895 --
896 RAISE FTE_DIST_NO_FILE_TEMPLATE;
897 --
898 IF l_debug_on THEN
899 WSH_DEBUG_SV.pop(l_module_name);
900 END IF;
901 --
902 RETURN;
903 END IF;
904
905
906 IF (l_download_date is null) THEN
907 --
908 -- The download must not have completed correctly
909 -- for this file
910 --
911 IF l_debug_on THEN
912 WSH_DEBUG_SV.logmsg(l_module_name,'l_download_date is null, The download must not have completed correctly for this file, RAISE FTE_DIST_NO_FILE_DOWNLOAD_DATE exception');
913 END IF;
914 --
915 RAISE FTE_DIST_NO_FILE_DOWNLOAD_DATE;
916 --
917 IF l_debug_on THEN
918 WSH_DEBUG_SV.pop(l_module_name);
919 END IF;
920 --
921 RETURN;
922 END IF;
923
924 IF (l_upload_date is not null) THEN
925 --
926 -- This file has already been uploaded successfull
927 -- doing it again makes no sense raise an error
928 --
929 IF l_debug_on THEN
930 WSH_DEBUG_SV.logmsg(l_module_name,'l_upload_date is not null, This file has already been uploaded successfully doing it again makes no sense raise an error RAISE FTE_DIST_FILE_UPLOAD_DONE_PREV exception');
931 END IF;
932 --
933 RAISE FTE_DIST_FILE_UPLOAD_DONE_PREV;
934 --
935 IF l_debug_on THEN
936 WSH_DEBUG_SV.pop(l_module_name);
937 END IF;
938 --
939 RETURN;
940 END IF;
941
942
943 IF l_debug_on THEN
944 WSH_DEBUG_SV.log(l_module_name,'For return distance column OPEN c_get_ret_col_info,g_ret_dist_col_name = ',g_ret_dist_col_name);
945 WSH_DEBUG_SV.log(l_module_name,'l_template_id = ',l_template_id);
946 END IF;
947 --
948 -- For return distance column
949 --
950 OPEN c_get_ret_col_info(g_ret_dist_col_name,
951 l_template_id);
952 FETCH c_get_ret_col_info INTO l_ret_dist_col_id,
953 l_ret_dist_start_pos,
954 l_ret_dist_length;
955 CLOSE c_get_ret_col_info;
956
957
958 IF l_debug_on THEN
959 WSH_DEBUG_SV.log(l_module_name,'See if it is enabled OPEN c_get_ret_enabled, l_ret_dist_col_id = ',l_ret_dist_col_id);
960 END IF;
961 --
962 -- See if it is enabled
963 --
964 OPEN c_get_ret_enabled(l_ret_dist_col_id);
965 FETCH c_get_ret_enabled INTO l_ret_dist_enabled_flag,
966 l_ret_dist_return_format,
967 l_ret_dist_db_uom;
968 CLOSE c_get_ret_enabled;
969
970
971
972 IF (l_ret_dist_enabled_flag = 'Y') THEN
973
974
975 IF ((l_ret_dist_start_pos is null) OR
976 (l_ret_dist_length is null)) THEN
977 --
978 -- The column is enabled but there is no start or length or both
979 -- we will not be able to parse the file
980 --
981 --
982 IF l_debug_on THEN
983 WSH_DEBUG_SV.logmsg(l_module_name,'l_ret_dist_enabled_flag = Y, The column is enabled but there is no start or length or both we will not be able to parse the file RAISE FTE_DIST_RET_DIST_COL_NO_DATA exception');
984 END IF;
985 --
986 RAISE FTE_DIST_RET_DIST_COL_NO_DATA;
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 -- AXE
996 --
997 -- [AAB][02/23/2004][BUG# ]AXE
998 --
999 -- Distance is enabled get the acutal Distance UoM to store in the database
1000 --
1001 IF (l_ret_dist_db_uom = 'MI') THEN
1002 l_distance_unit_of_measure := g_distance_mile;
1003 ELSIF (l_ret_dist_db_uom = 'KM') THEN
1004 l_distance_unit_of_measure := g_distance_kilometer;
1005 ELSE
1006 --
1007 -- Error, must be mile or kilometer
1008 --
1009 RAISE FTE_DIST_INV_TEMP_DIST_UOM;
1010 --
1011 IF l_debug_on THEN
1012 WSH_DEBUG_SV.pop(l_module_name);
1013 END IF;
1014 --
1015 RETURN;
1016 END IF;
1017
1018 --
1019 -- Get the correct code from the mtl table
1020 --
1021 OPEN c_get_distance_uom(l_distance_unit_of_measure,
1022 l_language);
1023 FETCH c_get_distance_uom INTO l_distance_uom_code;
1024 CLOSE c_get_distance_uom;
1025
1026 IF (l_distance_uom_code is null) THEN
1027 --
1028 -- There is no Distance UoM code from mtl table
1029 --
1030 RAISE FTE_DIST_NO_DISTANCE_UOM;
1031 --
1032 IF l_debug_on THEN
1033 WSH_DEBUG_SV.pop(l_module_name);
1034 END IF;
1035 --
1036 RETURN;
1037 END IF;
1038 --
1039 --
1040 --
1041 END IF;
1042
1043
1044 IF l_debug_on THEN
1045 WSH_DEBUG_SV.log(l_module_name,'Get the return time column g_ret_time_col_name = ',g_ret_time_col_name);
1046 WSH_DEBUG_SV.log(l_module_name,'l_template_id = ',l_template_id);
1047 END IF;
1048 --
1049 -- Get the return time column
1050 --
1051 OPEN c_get_ret_col_info(g_ret_time_col_name,
1052 l_template_id);
1053 FETCH c_get_ret_col_info INTO l_ret_time_col_id,
1054 l_ret_time_start_pos,
1055 l_ret_time_length;
1056 CLOSE c_get_ret_col_info;
1057
1058
1059 IF l_debug_on THEN
1060 WSH_DEBUG_SV.log(l_module_name,' See if it is enabled OPEN c_get_ret_enabled, l_ret_time_col_id = ',l_ret_time_col_id);
1061 END IF;
1062 --
1063 -- See if it is enabled
1064 --
1065 OPEN c_get_ret_enabled(l_ret_time_col_id);
1066 FETCH c_get_ret_enabled INTO l_ret_time_enabled_flag,
1067 l_ret_time_return_format,
1068 l_ret_time_db_uom;
1069 CLOSE c_get_ret_enabled;
1070
1071
1072 IF (l_ret_time_enabled_flag = 'Y') THEN
1073 IF ((l_ret_time_start_pos is null) OR
1074 (l_ret_time_length is null)) THEN
1075 --
1076 -- The column is enabled but there is no start or length or both
1077 -- we will not be able to parse the file
1078 --
1079 --
1080 IF l_debug_on THEN
1081 WSH_DEBUG_SV.logmsg(l_module_name,'l_ret_time_enabled_flag = Y, The column is enabled but there is no start or length or both we will not be able to parse the file RAISE FTE_DIST_RET_TIME_COL_NO_DATA exception');
1082 END IF;
1083 --
1084 RAISE FTE_DIST_RET_TIME_COL_NO_DATA;
1085 --
1086 IF l_debug_on THEN
1087 WSH_DEBUG_SV.pop(l_module_name);
1088 END IF;
1089 --
1090 RETURN;
1091 END IF;
1092
1093 --
1094 -- [AAB][02/23/2004][BUG# ]AXE
1095 --
1096 -- Time is enabled get the acutal Time UoM to store in the database
1097 --
1098 IF (l_ret_time_db_uom = 'HR') THEN
1099 l_time_unit_of_measure := g_time_hour;
1100 ELSIF (l_ret_time_db_uom = 'MIN') THEN
1101 l_time_unit_of_measure := g_time_minute;
1102 ELSE
1103 --
1104 -- Error, must be minute or hour
1105 --
1106 RAISE FTE_DIST_INV_TEMP_TIME_UOM;
1107 RETURN;
1108 END IF;
1109
1110 --
1111 -- Get the correct code from the mtl table
1112 --
1113 OPEN c_get_time_uom(l_time_unit_of_measure,
1114 l_language);
1115 FETCH c_get_time_uom INTO l_time_uom_code;
1116 CLOSE c_get_time_uom;
1117
1118 IF (l_time_uom_code is null) THEN
1119 --
1120 -- There is no Time UoM code from mtl table
1121 --
1122 RAISE FTE_DIST_NO_TIME_UOM;
1123 RETURN;
1124 END IF;
1125
1126 --
1127
1128
1129 END IF;
1130
1131
1132 IF ((l_ret_dist_enabled_flag <> 'Y') AND
1133 (l_ret_time_enabled_flag <> 'Y')) THEN
1134 --
1135 -- Neither column is enabled? Therefore we cannot
1136 -- parse the data
1137 --
1138 --
1139 IF l_debug_on THEN
1140 WSH_DEBUG_SV.logmsg(l_module_name,'l_ret_time_enabled_flag and l_ret_dist_enabled_flag <> Y, Neither column is enabled Therefore we cannot parse the data, RAISE FTE_DIST_NO_RET_COL_ENABLED exception');
1141 END IF;
1142 --
1143 RAISE FTE_DIST_NO_RET_COL_ENABLED;
1144 --
1145 IF l_debug_on THEN
1146 WSH_DEBUG_SV.pop(l_module_name);
1147 END IF;
1148 --
1149 RETURN;
1150 END IF;
1151
1152
1153 --
1154 -- So we are here at least one column is enabled
1155 --
1156 -- For the return time we may have to convert from the Return Format" to
1157 -- the DB uom
1158 -- The DB UoM is either HR - Hours or MIN - Minutes
1159 -- The return format is MIN Minutes, or HR Hours or HR:MIN Hours colon Minutes
1160 --
1161 -- UoM HR MIN
1162 -- Format
1163 -- MIN /60 --
1164 -- HR -- *60
1165 -- HR:MIN parse MIN parse HR
1166 -- /60 *60
1167 -- add to HR add to MIN
1168 --
1169
1170
1171
1172 IF ((l_ret_time_return_format = 'MIN') AND
1173 (l_ret_time_db_uom = 'HR')) THEN
1174 l_conv_flag := 'MINHR';
1175 ELSIF ((l_ret_time_return_format = 'HR') AND
1176 (l_ret_time_db_uom = 'MIN')) THEN
1177 l_conv_flag := 'HRMIN';
1178 ELSIF ((l_ret_time_return_format = 'HR:MIN') AND
1179 (l_ret_time_db_uom = 'MIN')) THEN
1180 l_conv_flag := 'HMMIN';
1181 ELSIF ((l_ret_time_return_format = 'HR:MIN') AND
1182 (l_ret_time_db_uom = 'HR')) THEN
1183 l_conv_flag := 'HMHR';
1184 ELSE
1185 l_conv_flag := null;
1186 END IF;
1187
1188
1189
1190
1191 IF l_debug_on THEN
1192 WSH_DEBUG_SV.log(l_module_name,'l_conv_flag',l_conv_flag);
1193 END IF;
1194
1195
1196 IF l_debug_on THEN
1197 WSH_DEBUG_SV.log(l_module_name,'Get the download lines OPEN c_get_download_lines, l_download_file_id = ',l_download_file_id);
1198 END IF;
1199
1200 FND_FILE.PUT_LINE(FND_FILE.log, 'Retrieving the download lines '||l_download_file_id);
1201 --
1202 -- Get the download lines
1203 --
1204 OPEN c_get_download_lines(l_download_file_id);
1205 FETCH c_get_download_lines BULK COLLECT INTO
1206 l_origin_id_tab,
1207 l_destination_id_tab;
1208 CLOSE c_get_download_lines;
1209
1210
1211 IF ((l_origin_id_tab.COUNT = 0) OR
1212 (l_destination_id_tab.COUNT = 0)) THEN
1213 --
1214 -- There are no download lines in the table?
1215 --
1216 IF l_debug_on THEN
1217 WSH_DEBUG_SV.logmsg(l_module_name,'There are no download lines in the table RAISE FTE_DIST_NO_DOWNLOAD_LINES exception');
1218 END IF;
1219 --
1220 RAISE FTE_DIST_NO_DOWNLOAD_LINES;
1221 --
1222 IF l_debug_on THEN
1223 WSH_DEBUG_SV.pop(l_module_name);
1224 END IF;
1225 --
1226 RETURN;
1227 END IF;
1228
1229
1230
1231 --
1232 -- Get the count of lines from the table
1233 -- we can only insert records into the table is the number of lines
1234 -- in the file matches with the number of lines in the table
1235 --
1236
1237 l_number_of_table_lines := l_origin_id_tab.COUNT;
1238
1239 IF l_debug_on THEN
1240 WSH_DEBUG_SV.log(l_module_name,'l_number_of_table_lines = ',l_number_of_table_lines);
1241 END IF;
1242
1243 FND_FILE.PUT_LINE(FND_FILE.log, 'Number of lines in table = '||to_char(l_number_of_table_lines));
1244
1245
1246
1247 --
1248 -- Lets read the file
1249 --
1250 FND_FILE.PUT_LINE(FND_FILE.log, 'READING FILE ' || p_source_file_directory || '/' || p_source_file_name);
1251
1252
1253 --
1254 -- The file is read in a separate plsql block as when it is finished
1255 -- we get a no data found exception - which in this case is good
1256 --
1257 BEGIN
1258
1259 IF l_debug_on THEN
1260 WSH_DEBUG_SV.logmsg(l_module_name,'Open the file Set the full file name for utl_file');
1261 END IF;
1262 --
1263 -- Set the full file name for utl_file
1264 --
1265 l_dtt_file := utl_file.fopen(p_source_file_directory, p_source_file_name, 'R');
1266
1267
1268 --
1269 -- Reset the line and loader counters
1270 --
1271 l_line_ctr := 0;
1272 l_load_ctr := 0;
1273
1274
1275 IF l_debug_on THEN
1276 WSH_DEBUG_SV.logmsg(l_module_name,'Start a loop to read the file contents one line at a time');
1277 END IF;
1278 --
1279 -- Start a loop to read the file
1280 --
1281 LOOP
1282
1283
1284 IF l_debug_on THEN
1285 WSH_DEBUG_SV.logmsg(l_module_name,'Get the next line out of the file');
1286 END IF;
1287 --
1288 -- Get the next line out of the file
1289 --
1290 utl_file.get_line(l_dtt_file, l_line);
1291
1292
1293 --
1294 -- [AAB][01/22/04][BUG# 3381771]
1295 -- Check that the line contains something
1296 --
1297 --
1298 IF l_debug_on THEN
1299 WSH_DEBUG_SV.logmsg(l_module_name,'check the file length');
1300 END IF;
1301 l_line_length := 0;
1302 l_line_length := NVL(LENGTH(l_line),0);
1303
1304 IF (l_line_length <= 0) THEN
1305 RAISE FTE_DIST_INV_LINE_LENGTH;
1306 --
1307 IF l_debug_on THEN
1308 WSH_DEBUG_SV.pop(l_module_name);
1309 END IF;
1310 --
1311 RETURN;
1312 ELSE
1313 --
1314 -- Line length is greater than 0, lets get rid of all spaces and see if
1315 -- there is any length left
1316 --
1317 l_line_length := NVL(LENGTH(RTRIM(LTRIM(REPLACE(substr(l_line,1, l_line_length),FND_GLOBAL.local_chr(9),' '),' '),' ')),0);
1318 IF (l_line_length <= 0) THEN
1319 RAISE FTE_DIST_INV_LINE_LENGTH;
1320 --
1321 IF l_debug_on THEN
1322 WSH_DEBUG_SV.pop(l_module_name);
1323 END IF;
1324 --
1325 RETURN;
1326 END IF;
1327 END IF;
1328
1329 --
1330
1331 --
1332 -- Increment the line counter
1333 --
1334 l_line_ctr := l_line_ctr + 1;
1335
1336 FND_FILE.PUT_LINE(FND_FILE.log, 'File Line #'||to_char(l_line_ctr)||': '||l_line);
1337
1338 IF l_debug_on THEN
1339 WSH_DEBUG_SV.logmsg(l_module_name,'File Line #'||to_char(l_line_ctr)||': '||l_line);
1340 END IF;
1341
1342 --
1343 -- Get the distance if applicable
1344 --
1345 IF (l_ret_dist_enabled_flag = 'Y') THEN
1346 --
1347 -- get rid of trailing and leading spaces
1348 --
1349 --
1350 -- [AAB][12/16/03][BUG# 3325486]
1351 -- Added Replace to the extraction to replace all occurances of TAB in the string with a space,
1352 -- The spaces are then trimmed off the left and right end
1353 --
1354 l_ret_dist_val := RTRIM(LTRIM(REPLACE(substr(l_line,l_ret_dist_start_pos, l_ret_dist_length),FND_GLOBAL.local_chr(9),' '),' '),' ');
1355
1356 IF l_debug_on THEN
1357 WSH_DEBUG_SV.logmsg(l_module_name,'Converting distance value to a number');
1358 END IF;
1359
1360 FND_FILE.PUT_LINE(FND_FILE.log, 'Converting distance value to a number');
1361 --
1362 -- Convert the value to a number
1363 --
1364 IF (l_ret_dist_val is not null) THEN
1365 l_ret_dist := to_number(l_ret_dist_val);
1366 ELSE
1367 l_ret_dist := 0;
1368 END IF;
1369 ELSE
1370 l_ret_dist := null;
1371 END IF;
1372
1373
1374 --
1375 -- Get the return time if applicable
1376 --
1377 IF (l_ret_time_enabled_flag = 'Y') THEN
1378 --
1379 -- get rid of trailing and leading spaces
1380 --
1381
1382 --
1383 -- [AAB][12/16/03][BUG# 3325486]
1384 -- Added Replace to the extraction to replace all occurances of TAB in the string with a space,
1385 -- The spaces are then trimmed off the left and right end
1386 --
1387 l_ret_time_val := RTRIM(LTRIM(REPLACE(substr(l_line,l_ret_time_start_pos, l_ret_time_length),FND_GLOBAL.local_chr(9),' '),' '),' ');
1388
1389
1390 l_colon_check := 0;
1391
1392 IF (l_ret_time_val is not null) THEN
1393
1394 IF (l_conv_flag is not null) THEN
1395 IF (l_conv_flag = 'MINHR') THEN
1396 l_ret_time := (to_number(l_ret_time_val) / 60);
1397 ELSIF (l_conv_flag = 'HRMIN') THEN
1398 l_ret_time := (to_number(l_ret_time_val) * 60);
1399 ELSIF (l_conv_flag = 'HMMIN') THEN
1400
1401 -- AXE
1402 -- [AAB][01/23/2004][BUG# ]
1403 -- Check that the return value has a colon in it otherwise we could get
1404 -- a weird value for the time
1405 --
1406 l_colon_check := instr(l_ret_time_val,':');
1407
1408 IF l_debug_on THEN
1409 WSH_DEBUG_SV.log(l_module_name,'check HMMIN return time for a colon, l_colon_check = ',l_colon_check);
1410 END IF;
1411
1412 IF (l_colon_check <= 0) THEN
1413 --
1414 -- Error no colon in the return time string
1415 --
1416 RAISE FTE_DIST_INV_TIME_FORMAT;
1417 --
1418 IF l_debug_on THEN
1419 WSH_DEBUG_SV.pop(l_module_name);
1420 END IF;
1421 --
1422 RETURN;
1423 END IF;
1424
1425
1426 --
1427 IF l_debug_on THEN
1428 WSH_DEBUG_SV.log(l_module_name,'converting HR:MIN to Minutes');
1429 END IF;
1430
1431 l_ret_mins := to_number(substr(l_ret_time_val, (instr(l_ret_time_val,':')+1),LENGTH(l_ret_time_val)));
1432 l_ret_hrs := to_number(substr(l_ret_time_val,1,(instr(l_ret_time_val,':')-1)));
1433 l_ret_time := ((l_ret_hrs * 60) + l_ret_mins);
1434
1435 IF l_debug_on THEN
1436 WSH_DEBUG_SV.log(l_module_name,'END OF converting HR:MIN to Hours');
1437 END IF;
1438 ELSIF (l_conv_flag = 'HMHR') THEN
1439
1440 -- AXE
1441 -- [AAB][01/23/2004][BUG# ]
1442 -- Check that the return value has a colon in it otherwise we could get
1443 -- a weird value for the time
1444 --
1445 l_colon_check := instr(l_ret_time_val,':');
1446
1447 IF l_debug_on THEN
1448 WSH_DEBUG_SV.log(l_module_name,'check HMHR return time for a colon, l_colon_check = ',l_colon_check);
1449 END IF;
1450
1451 IF (l_colon_check <= 0) THEN
1452 --
1453 -- Error no colon in the return time string
1454 --
1455 RAISE FTE_DIST_INV_TIME_FORMAT;
1456 --
1457 IF l_debug_on THEN
1458 WSH_DEBUG_SV.pop(l_module_name);
1459 END IF;
1460 --
1461 RETURN;
1462 END IF;
1463
1464 IF l_debug_on THEN
1465 WSH_DEBUG_SV.log(l_module_name,'converting HR:MIN to Hours');
1466 END IF;
1467
1468 l_ret_mins := to_number(substr(l_ret_time_val, (instr(l_ret_time_val,':')+1),LENGTH(l_ret_time_val)));
1469 l_ret_hrs := to_number(substr(l_ret_time_val,1,(instr(l_ret_time_val,':')-1)));
1470 l_ret_time := ((l_ret_mins /60) + l_ret_hrs);
1471
1472 IF l_debug_on THEN
1473 WSH_DEBUG_SV.log(l_module_name,'END OF converting HR:MIN to Hours');
1474 END IF;
1475 END IF;
1476 ELSE
1477 l_ret_time := to_number(l_ret_time_val);
1478 END IF;
1479 ELSE
1480 l_ret_time := 0;
1481 END IF;
1482 ELSE
1483 l_ret_time := null;
1484 END IF;
1485
1486
1487 --
1488 -- Now we have to get the orign and destination regions from
1489 -- the download line table
1490 --
1491 -- Increment the load counter
1492 --
1493 l_load_ctr := l_load_ctr + 1;
1494
1495 IF l_debug_on THEN
1496 WSH_DEBUG_SV.log(l_module_name,'l_load_ctr',l_load_ctr);
1497 END IF;
1498
1499 l_location_origin_id(l_load_ctr) := l_origin_id_tab(l_line_ctr);
1500 l_location_destination_id(l_load_ctr) := l_destination_id_tab(l_line_ctr);
1501 l_location_identifier_type(l_load_ctr) := l_identifier_type;
1502 l_location_distance(l_load_ctr) := l_ret_dist;
1503 l_location_distance_uom(l_load_ctr) := l_distance_uom_code; -- AXE l_ret_dist_db_uom;
1504 l_location_transit_time(l_load_ctr) := l_ret_time;
1505 l_location_transit_time_uom(l_load_ctr) := l_time_uom_code; -- AXE l_ret_time_db_uom;
1506 l_location_creation_date(l_load_ctr) := l_cur_date;
1507 l_location_created_by(l_load_ctr) := g_user_id;
1508 l_location_last_update_date(l_load_ctr) := l_cur_date;
1509 l_location_last_updated_by(l_load_ctr) := g_user_id;
1510 l_location_last_update_login(l_load_ctr) := null;
1511 l_location_program_app_id(l_load_ctr) := null;
1512 l_location_program_id(l_load_ctr) := null;
1513 l_location_program_update_date(l_load_ctr) := null;
1514 l_location_request_id(l_load_ctr) := p_load_id;
1515
1516 IF (l_load_ctr = 250) THEN
1517 IF l_debug_on THEN
1518 WSH_DEBUG_SV.logmsg(l_module_name,'we are going to insert 250 at a time into the global temp table insert into fte_distance_loader_tmp');
1519 END IF;
1520 --
1521 FND_FILE.PUT_LINE(FND_FILE.log, 'Inserting 250 records into temp table');
1522 --
1523 -- we are going to insert 250 at a time into the global
1524 -- temp table
1525 --
1526 FORALL i in l_location_origin_id.FIRST..l_location_origin_id.LAST
1527 insert into fte_distance_loader_tmp(ORIGIN_ID,
1528 DESTINATION_ID,
1529 IDENTIFIER_TYPE,
1530 DISTANCE,
1531 DISTANCE_UOM,
1532 TRANSIT_TIME,
1533 TRANSIT_TIME_UOM,
1534 CREATION_DATE,
1535 CREATED_BY,
1536 LAST_UPDATE_DATE,
1537 LAST_UPDATED_BY,
1538 LAST_UPDATE_LOGIN,
1539 PROGRAM_APPLICATION_ID,
1540 PROGRAM_ID,
1541 PROGRAM_UPDATE_DATE,
1542 REQUEST_ID)
1543 values(l_location_origin_id(i),
1544 l_location_destination_id(i),
1545 l_location_identifier_type(i),
1546 l_location_distance(i),
1547 l_location_distance_uom(i),
1548 l_location_transit_time(i),
1549 l_location_transit_time_uom(i),
1550 l_location_creation_date(i),
1551 l_location_created_by(i),
1552 l_location_last_update_date(i),
1553 l_location_last_updated_by(i),
1554 l_location_last_update_login(i),
1555 l_location_program_app_id(i),
1556 l_location_program_id(i),
1557 l_location_program_update_date(i),
1558 l_location_request_id(i));
1559
1560
1561 --
1562 -- Clear the tables for the next pass
1563 --
1564 IF l_debug_on THEN
1565 WSH_DEBUG_SV.logmsg(l_module_name,'Clearing the temp tables for the next pass');
1566 END IF;
1567
1568 l_location_origin_id.DELETE;
1569 l_location_destination_id.DELETE;
1570 l_location_identifier_type.DELETE;
1571 l_location_distance.DELETE;
1572 l_location_distance_uom.DELETE;
1573 l_location_transit_time.DELETE;
1574 l_location_transit_time_uom.DELETE;
1575 l_location_creation_date.DELETE;
1576 l_location_created_by.DELETE;
1577 l_location_last_update_date.DELETE;
1578 l_location_last_updated_by.DELETE;
1579 l_location_last_update_login.DELETE;
1580 l_location_program_app_id.DELETE;
1581 l_location_program_id.DELETE;
1582 l_location_program_update_date.DELETE;
1583 l_location_request_id.DELETE;
1584
1585 l_load_ctr := 0;
1586 END IF;
1587
1588 --
1589 -- End of reading the line
1590 --
1591 END LOOP;
1592
1593
1594 IF l_debug_on THEN
1595 WSH_DEBUG_SV.logmsg(l_module_name,'Close the file');
1596 END IF;
1597 --
1598 -- End of reading the file - close it out
1599 --
1600 utl_file.fclose(l_dtt_file);
1601
1602
1603 EXCEPTION
1604 WHEN FTE_DIST_INV_TIME_FORMAT THEN
1605 --
1606 -- Close the file
1607 --
1608 IF (utl_file.is_open(l_dtt_file)) THEN
1609 utl_file.fclose(l_dtt_file);
1610 END IF;
1611 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INV_TIME_FORMAT');
1612 x_return_status := 2;
1613 x_return_message := FND_MESSAGE.GET;
1614 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - INV TIME FORMAT. '||x_return_message);
1615
1616 --
1617 -- Close the cursors
1618 --
1619 IF (c_check_download_lines%ISOPEN) THEN
1620 CLOSE c_check_download_lines;
1621 END IF;
1622
1623 IF (c_get_file_info%ISOPEN) THEN
1624 CLOSE c_get_file_info;
1625 END IF;
1626
1627 IF (c_get_download_lines%ISOPEN) THEN
1628 CLOSE c_get_download_lines;
1629 END IF;
1630
1631 IF (c_get_ret_col_info%ISOPEN) THEN
1632 CLOSE c_get_ret_col_info;
1633 END IF;
1634
1635 IF (c_get_ret_enabled%ISOPEN) THEN
1636 CLOSE c_get_ret_enabled;
1637 END IF;
1638
1639 IF (c_get_merge_data%ISOPEN) THEN
1640 CLOSE c_get_merge_data;
1641 END IF;
1642
1643 IF (c_get_time_uom%ISOPEN) THEN
1644 CLOSE c_get_time_uom;
1645 END IF;
1646
1647 IF (c_get_distance_uom%ISOPEN) THEN
1648 CLOSE c_get_distance_uom;
1649 END IF;
1650
1651 --
1652 -- Debug Statements
1653 --
1654 --
1655 IF l_debug_on THEN
1656 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TIME_FORMAT FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
1657 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TIME_FORMAT');
1658 WSH_DEBUG_SV.pop(l_module_name);
1659 END IF;
1660 --
1661 RETURN;
1662
1663
1664 WHEN FTE_DIST_INV_LINE_LENGTH THEN
1665 --
1666 -- Close the file
1667 --
1668 IF (utl_file.is_open(l_dtt_file)) THEN
1669 utl_file.fclose(l_dtt_file);
1670 END IF;
1671 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INV_LINE_LENGTH');
1672 x_return_status := 2;
1673 x_return_message := FND_MESSAGE.GET;
1674 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - INV LINE LENGTH. '||x_return_message);
1675
1676 --
1677 -- Close the cursors
1678 --
1679 IF (c_check_download_lines%ISOPEN) THEN
1680 CLOSE c_check_download_lines;
1681 END IF;
1682
1683 IF (c_get_file_info%ISOPEN) THEN
1684 CLOSE c_get_file_info;
1685 END IF;
1686
1687 IF (c_get_download_lines%ISOPEN) THEN
1688 CLOSE c_get_download_lines;
1689 END IF;
1690
1691 IF (c_get_ret_col_info%ISOPEN) THEN
1692 CLOSE c_get_ret_col_info;
1693 END IF;
1694
1695 IF (c_get_ret_enabled%ISOPEN) THEN
1696 CLOSE c_get_ret_enabled;
1697 END IF;
1698
1699 IF (c_get_merge_data%ISOPEN) THEN
1700 CLOSE c_get_merge_data;
1701 END IF;
1702
1703 IF (c_get_time_uom%ISOPEN) THEN
1704 CLOSE c_get_time_uom;
1705 END IF;
1706
1707 IF (c_get_distance_uom%ISOPEN) THEN
1708 CLOSE c_get_distance_uom;
1709 END IF;
1710
1711 --
1712 -- Debug Statements
1713 --
1714 --
1715 IF l_debug_on THEN
1716 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_LINE_LENGTH FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
1717 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_LINE_LENGTH');
1718 WSH_DEBUG_SV.pop(l_module_name);
1719 END IF;
1720 --
1721 RETURN;
1722
1723
1724 WHEN NO_DATA_FOUND THEN
1725 --
1726 -- Occurs when the entire file has been read, this is OK
1727 -- Close the file
1728 --
1729 IF (utl_file.is_open(l_dtt_file)) THEN
1730 utl_file.fclose(l_dtt_file);
1731 END IF;
1732 IF l_debug_on THEN
1733 WSH_DEBUG_SV.logmsg(l_module_name,'Finished reading file');
1734 END IF;
1735
1736 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Finished Reading File.');
1737 x_return_status := -1;
1738
1739
1740 WHEN UTL_FILE.INVALID_PATH THEN
1741 --
1742 -- Close the file
1743 --
1744 IF (utl_file.is_open(l_dtt_file)) THEN
1745 utl_file.fclose(l_dtt_file);
1746 END IF;
1747 x_return_message := 'FILE ' || p_source_file_directory || '/' || p_source_file_name || ' NOT ACCESSIBLE';
1748 x_return_message := x_return_message||' Also please make sure that the directory is accessible to UTL_FILE.';
1749 fnd_file.put_line(FND_FILE.log, x_return_message);
1750 x_return_status := 2;
1751 --
1752 --
1753 -- Close the cursors
1754 --
1755 IF (c_check_download_lines%ISOPEN) THEN
1756 CLOSE c_check_download_lines;
1757 END IF;
1758
1759 IF (c_get_file_info%ISOPEN) THEN
1760 CLOSE c_get_file_info;
1761 END IF;
1762
1763 IF (c_get_download_lines%ISOPEN) THEN
1764 CLOSE c_get_download_lines;
1765 END IF;
1766
1767 IF (c_get_ret_col_info%ISOPEN) THEN
1768 CLOSE c_get_ret_col_info;
1769 END IF;
1770
1771 IF (c_get_ret_enabled%ISOPEN) THEN
1772 CLOSE c_get_ret_enabled;
1773 END IF;
1774
1775 IF (c_get_merge_data%ISOPEN) THEN
1776 CLOSE c_get_merge_data;
1777 END IF;
1778
1779 IF (c_get_time_uom%ISOPEN) THEN
1780 CLOSE c_get_time_uom;
1781 END IF;
1782
1783 IF (c_get_distance_uom%ISOPEN) THEN
1784 CLOSE c_get_distance_uom;
1785 END IF;
1786
1787 IF l_debug_on THEN
1788 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR UTL_FILE.INVALID_PATH FTE_BULK_DTT_PKG.READ_DTT_FILE ' ||x_return_message);
1789 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:UTL_FILE.INVALID_PATH');
1790 WSH_DEBUG_SV.pop(l_module_name);
1791 END IF;
1792 --
1793 RETURN;
1794
1795
1796 WHEN OTHERS THEN
1797 --
1798 -- Close the file
1799 --
1800 IF (utl_file.is_open(l_dtt_file)) THEN
1801 utl_file.fclose(l_dtt_file);
1802 END IF;
1803
1804 FND_FILE.PUT_LINE(FND_FILE.log, 'UNEXPECTED ERROR IN PROCEDURE READ_DTT_FILE.'||sqlerrm);
1805 FND_FILE.PUT_LINE(FND_FILE.log, 'OFFENDING_LINE: ' || l_line);
1806 FND_FILE.PUT_LINE(FND_FILE.Log, sqlerrm);
1807 x_return_status := 2;
1808 x_return_message := ('UNEXPECTED ERROR IN PROCEDURE FTE_BULK_DTT_PKG.READ_DTT_FILE: '||sqlerrm);
1809
1810 --
1811 -- Close the cursors
1812 --
1813 IF (c_check_download_lines%ISOPEN) THEN
1814 CLOSE c_check_download_lines;
1815 END IF;
1816
1817 IF (c_get_file_info%ISOPEN) THEN
1818 CLOSE c_get_file_info;
1819 END IF;
1820
1821 IF (c_get_download_lines%ISOPEN) THEN
1822 CLOSE c_get_download_lines;
1823 END IF;
1824
1825 IF (c_get_ret_col_info%ISOPEN) THEN
1826 CLOSE c_get_ret_col_info;
1827 END IF;
1828
1829 IF (c_get_ret_enabled%ISOPEN) THEN
1830 CLOSE c_get_ret_enabled;
1831 END IF;
1832
1833 IF (c_get_merge_data%ISOPEN) THEN
1834 CLOSE c_get_merge_data;
1835 END IF;
1836
1837 IF (c_get_time_uom%ISOPEN) THEN
1838 CLOSE c_get_time_uom;
1839 END IF;
1840
1841 IF (c_get_distance_uom%ISOPEN) THEN
1842 CLOSE c_get_distance_uom;
1843 END IF;
1844
1845 --
1846 -- Debug Statements
1847 --
1848 IF l_debug_on THEN
1849 WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.READ_DTT_FILE IS ' ||sqlerrm);
1850 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1851 WSH_DEBUG_SV.logmsg(l_module_name,'OFFENDING_LINE: ' ||l_line);
1852 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1853 END IF;
1854
1855 RAISE;
1856
1857
1858 END;
1859
1860
1861
1862 --
1863 -- insert the last few lines that did not sum upto 250
1864 -- into the Global temp table
1865 --
1866 IF (l_location_origin_id.COUNT > 0) THEN
1867
1868 IF l_debug_on THEN
1869 WSH_DEBUG_SV.logmsg(l_module_name,'insert the last few lines that did not sum upto 250 into the Global temp table');
1870 END IF;
1871
1872 FND_FILE.PUT_LINE(FND_FILE.log, 'Inserting remainder of records into temp table');
1873
1874 FORALL i in l_location_origin_id.FIRST..l_location_origin_id.LAST
1875 insert into fte_distance_loader_tmp(ORIGIN_ID,
1876 DESTINATION_ID,
1877 IDENTIFIER_TYPE,
1878 DISTANCE,
1879 DISTANCE_UOM,
1880 TRANSIT_TIME,
1881 TRANSIT_TIME_UOM,
1882 CREATION_DATE,
1883 CREATED_BY,
1884 LAST_UPDATE_DATE,
1885 LAST_UPDATED_BY,
1886 LAST_UPDATE_LOGIN,
1887 PROGRAM_APPLICATION_ID,
1888 PROGRAM_ID,
1889 PROGRAM_UPDATE_DATE,
1890 REQUEST_ID)
1891 values(l_location_origin_id(i),
1892 l_location_destination_id(i),
1893 l_location_identifier_type(i),
1894 l_location_distance(i),
1895 l_location_distance_uom(i),
1896 l_location_transit_time(i),
1897 l_location_transit_time_uom(i),
1898 l_location_creation_date(i),
1899 l_location_created_by(i),
1900 l_location_last_update_date(i),
1901 l_location_last_updated_by(i),
1902 l_location_last_update_login(i),
1903 l_location_program_app_id(i),
1904 l_location_program_id(i),
1905 l_location_program_update_date(i),
1906 l_location_request_id(i));
1907
1908 --
1909 -- Clean the local tables
1910 --
1911 IF l_debug_on THEN
1912 WSH_DEBUG_SV.logmsg(l_module_name,'Clean the temp tables');
1913 END IF;
1914 --
1915 l_location_origin_id.DELETE;
1916 l_location_destination_id.DELETE;
1917 l_location_identifier_type.DELETE;
1918 l_location_distance.DELETE;
1919 l_location_distance_uom.DELETE;
1920 l_location_transit_time.DELETE;
1921 l_location_transit_time_uom.DELETE;
1922 l_location_creation_date.DELETE;
1923 l_location_created_by.DELETE;
1924 l_location_last_update_date.DELETE;
1925 l_location_last_updated_by.DELETE;
1926 l_location_last_update_login.DELETE;
1927 l_location_program_app_id.DELETE;
1928 l_location_program_id.DELETE;
1929 l_location_program_update_date.DELETE;
1930 l_location_request_id.DELETE;
1931
1932 l_load_ctr := 0;
1933 END IF;
1934
1935 --
1936 -- Only insert all records if the number of lines in the file mathes the number of lines in the table
1937 --
1938 IF (l_number_of_table_lines = l_line_ctr) THEN
1939
1940 FND_FILE.PUT_LINE(FND_FILE.log, 'Number of lines in the table matched the number of lines in the file, inserting and/or updating into the location mileages table');
1941
1942 IF l_debug_on THEN
1943 WSH_DEBUG_SV.logmsg(l_module_name,'Number of lines in the table matched the number of lines in the file, inserting and/or updating into the location mileages table');
1944 END IF;
1945 --
1946
1947
1948 -- ----------------------------------------------------------------------------
1949 -- [ABLUNDEL][12/08/2003][BUG# 3301222]
1950 --
1951 -- The following merge SQL statement was removed as it is not compatible
1952 -- with 8i installations only 9i ort higher. This was failing when applied
1953 -- to an 8i instance. It was replaced by the code following the merge statement
1954 -- to allow for 8i compatibility
1955 --
1956 --
1957 --
1958 -- --
1959 -- -- All the records are in the temp table now we have to insert/update them into the
1960 -- -- fte_location_mileages table using the new funky MErGe stuff.....
1961 -- --
1962 -- MERGE INTO fte_location_mileages flm
1963 -- USING (SELECT origin_id,
1964 -- destination_id,
1965 -- identifier_type,
1966 -- distance,
1967 -- distance_uom,
1968 -- transit_time,
1969 -- transit_time_uom,
1970 -- creation_date,
1971 -- created_by,
1972 -- last_update_date,
1973 -- last_updated_by,
1974 -- last_update_login,
1975 -- program_application_id,
1976 -- program_id,
1977 -- program_update_date,
1978 -- request_id FROM FTE_DISTANCE_LOADER_TMP) fdlt
1979 -- ON (flm.origin_id = fdlt.origin_id AND
1980 -- flm.destination_id = fdlt.destination_id)
1981 -- WHEN MATCHED THEN UPDATE SET flm.identifier_type = fdlt.identifier_type,
1982 -- flm.distance = fdlt.distance,
1983 -- flm.distance_uom = fdlt.distance_uom,
1984 -- flm.transit_time = fdlt.transit_time,
1985 -- flm.transit_time_uom = fdlt.transit_time_uom,
1986 -- flm.last_update_date = fdlt.last_update_date,
1987 -- flm.last_updated_by = fdlt.last_updated_by,
1988 -- flm.last_update_login = fdlt.last_update_login,
1989 -- flm.program_application_id = fdlt.program_application_id,
1990 -- flm.program_id = fdlt.program_id,
1991 -- flm.program_update_date = fdlt.program_update_date,
1992 -- flm.request_id = fdlt.request_id
1993 -- WHEN NOT MATCHED THEN INSERT (flm.origin_id,
1994 -- flm.destination_id,
1995 -- flm.identifier_type,
1996 -- flm.distance,
1997 -- flm.distance_uom,
1998 -- flm.transit_time,
1999 -- flm.transit_time_uom,
2000 -- flm.creation_date,
2001 -- flm.created_by,
2002 -- flm.last_update_date,
2003 -- flm.last_updated_by,
2004 -- flm.last_update_login,
2005 -- flm.program_application_id,
2006 -- flm.program_id,
2007 -- flm.program_update_date,
2008 -- flm.request_id)
2009 -- VALUES (fdlt.origin_id,
2010 -- fdlt.destination_id,
2011 -- fdlt.identifier_type,
2012 -- fdlt.distance,
2013 -- fdlt.distance_uom,
2014 -- fdlt.transit_time,
2015 -- fdlt.transit_time_uom,
2016 -- fdlt.creation_date,
2017 -- fdlt.created_by,
2018 -- fdlt.last_update_date,
2019 -- fdlt.last_updated_by,
2020 -- fdlt.last_update_login,
2021 -- fdlt.program_application_id,
2022 -- fdlt.program_id,
2023 -- fdlt.program_update_date,
2024 -- fdlt.request_id);
2025 --
2026 --
2027 --
2028 --
2029 -- [ABLUNDEL][12/08/2003][BUG# 3301222]
2030 --
2031 -- New code to replace the MERGE statement above for 8i compatibility
2032 --
2033 --
2034 FND_FILE.PUT_LINE(FND_FILE.log,'Clean up the query collections');
2035 --
2036 IF l_debug_on THEN
2037 WSH_DEBUG_SV.logmsg(l_module_name,'Clean up the query collections');
2038 END IF;
2039
2040 --
2041 -- Clean up the query collections
2042 --
2043 l_old_origin_id_tab.DELETE;
2044 l_old_destination_id_tab.DELETE;
2045 l_new_origin_id_tab.DELETE;
2046 l_new_destination_id_tab.DELETE;
2047 l_new_identifier_type_tab.DELETE;
2048 l_new_distance_tab.DELETE;
2049 l_new_distance_uom_tab.DELETE;
2050 l_new_transit_time_tab.DELETE;
2051 l_new_transit_time_uom_tab.DELETE;
2052 l_new_creation_date_tab.DELETE;
2053 l_new_created_by_tab.DELETE;
2054 l_new_last_update_date_tab.DELETE;
2055 l_new_last_updated_by_tab.DELETE;
2056 l_new_last_update_login_tab.DELETE;
2057 l_new_program_app_id_tab.DELETE;
2058 l_new_program_id_tab.DELETE;
2059 l_new_program_update_date_tab.DELETE;
2060 l_new_request_id_tab.DELETE;
2061
2062 --
2063 -- Set the previous rows counter to zero
2064 --
2065 l_previous_rows := 0;
2066
2067 FND_FILE.PUT_LINE(FND_FILE.log,'Open the cursor, c_get_merge_data, to get the records to update and/or insert');
2068 --
2069 IF l_debug_on THEN
2070 WSH_DEBUG_SV.logmsg(l_module_name,'Open the cursor, c_get_merge_data, to get the records to update and/or insert');
2071 END IF;
2072
2073 --
2074 -- Open the cursor to get the records to update and/or insert
2075 --
2076 OPEN c_get_merge_data;
2077 LOOP
2078 FETCH c_get_merge_data BULK COLLECT INTO
2079 l_old_origin_id_tab,
2080 l_old_destination_id_tab,
2081 l_new_origin_id_tab,
2082 l_new_destination_id_tab,
2083 l_new_identifier_type_tab,
2084 l_new_distance_tab,
2085 l_new_distance_uom_tab,
2086 l_new_transit_time_tab,
2087 l_new_transit_time_uom_tab,
2088 l_new_creation_date_tab,
2089 l_new_created_by_tab,
2090 l_new_last_update_date_tab,
2091 l_new_last_updated_by_tab,
2092 l_new_last_update_login_tab,
2093 l_new_program_app_id_tab,
2094 l_new_program_id_tab,
2095 l_new_program_update_date_tab,
2096 l_new_request_id_tab
2097 LIMIT l_bulk_collect_size;
2098
2099
2100 --
2101 -- Set the current rows and remaining rows counters to be
2102 -- able to see if there are any records left in the cursor
2103 --
2104 l_current_rows := c_get_merge_data%rowcount ;
2105 l_remaining_rows := l_current_rows - l_previous_rows;
2106
2107
2108
2109 IF (l_remaining_rows <= 0) then
2110
2111 FND_FILE.PUT_LINE(FND_FILE.log,'There are no rows left from the cursor - exit the loop');
2112 --
2113 IF l_debug_on THEN
2114 WSH_DEBUG_SV.logmsg(l_module_name,'There are no rows left from the cursor - exit the loop');
2115 END IF;
2116 --
2117 -- There are no rows left from the cursor - exit the loop
2118 --
2119 EXIT;
2120 END IF;
2121
2122 --
2123 -- Set the previous rows counter to equal the current rows counter
2124 --
2125 l_previous_rows := l_current_rows ;
2126
2127
2128
2129 IF (l_new_origin_id_tab.COUNT > 0) THEN
2130 --
2131 -- We have records to insert or update
2132 -- Clean out the Update anbd insert plsql collections
2133 --
2134
2135 FND_FILE.PUT_LINE(FND_FILE.log,'cleaning the temp update tables');
2136 --
2137 IF l_debug_on THEN
2138 WSH_DEBUG_SV.logmsg(l_module_name,'cleaning the temp update tables');
2139 END IF;
2140
2141 l_u_origin_id_tab.DELETE;
2142 l_u_destination_id_tab.DELETE;
2143 l_u_identifier_type_tab.DELETE;
2144 l_u_distance_tab.DELETE;
2145 l_u_distance_uom_tab.DELETE;
2146 l_u_transit_time_tab.DELETE;
2147 l_u_transit_time_uom_tab.DELETE;
2148 l_u_last_update_date_tab.DELETE;
2149 l_u_last_updated_by_tab.DELETE;
2150 l_u_last_update_login_tab.DELETE;
2151 l_u_program_app_id_tab.DELETE;
2152 l_u_program_id_tab.DELETE;
2153 l_u_program_update_date_tab.DELETE;
2154 l_u_request_id_tab.DELETE;
2155
2156
2157 FND_FILE.PUT_LINE(FND_FILE.log,'cleaning the temp insert tables');
2158 --
2159 IF l_debug_on THEN
2160 WSH_DEBUG_SV.logmsg(l_module_name,'cleaning the temp insert tables');
2161 END IF;
2162
2163 l_i_origin_id_tab.DELETE;
2164 l_i_destination_id_tab.DELETE;
2165 l_i_identifier_type_tab.DELETE;
2166 l_i_distance_tab.DELETE;
2167 l_i_distance_uom_tab.DELETE;
2168 l_i_transit_time_tab.DELETE;
2169 l_i_transit_time_uom_tab.DELETE;
2170 l_i_creation_date_tab.DELETE;
2171 l_i_created_by_tab.DELETE;
2172 l_i_last_update_date_tab.DELETE;
2173 l_i_last_updated_by_tab.DELETE;
2174 l_i_last_update_login_tab.DELETE;
2175 l_i_program_app_id_tab.DELETE;
2176 l_i_program_id_tab.DELETE;
2177 l_i_program_update_date_tab.DELETE;
2178 l_i_request_id_tab.DELETE;
2179
2180
2181 --
2182 -- Reset the insert and update counters
2183 --
2184 l_insert_ctr := 0;
2185 l_update_ctr := 0;
2186
2187
2188
2189 FOR kk in l_new_origin_id_tab.FIRST..l_new_origin_id_tab.LAST LOOP -- LOOP_02
2190 --
2191 -- Loop through the query return and see which records need
2192 -- to be updated or inserted
2193 --
2194 FND_FILE.PUT_LINE(FND_FILE.log, 'checking for insert and update records');
2195 --
2196 IF l_debug_on THEN
2197 WSH_DEBUG_SV.logmsg(l_module_name,'checking for insert and update records');
2198 END IF;
2199
2200 IF ((l_old_origin_id_tab(kk) is null) AND
2201 (l_old_destination_id_tab(kk) is null)) THEN
2202
2203 FND_FILE.PUT_LINE(FND_FILE.log, 'getting insert records');
2204 --
2205 IF l_debug_on THEN
2206 WSH_DEBUG_SV.logmsg(l_module_name,'getting insert records');
2207 END IF;
2208 --
2209 -- Must be for an insert
2210 --
2211
2212 -- Increment the insert counter
2213 l_insert_ctr := l_insert_ctr + 1;
2214
2215 --
2216 -- Populate the insert collections with the queried data
2217 --
2218 l_i_origin_id_tab(l_insert_ctr) := l_new_origin_id_tab(kk);
2219 l_i_destination_id_tab(l_insert_ctr) := l_new_destination_id_tab(kk);
2220 l_i_identifier_type_tab(l_insert_ctr) := l_new_identifier_type_tab(kk);
2221 l_i_distance_tab(l_insert_ctr) := l_new_distance_tab(kk);
2222 l_i_distance_uom_tab(l_insert_ctr) := l_new_distance_uom_tab(kk);
2223 l_i_transit_time_tab(l_insert_ctr) := l_new_transit_time_tab(kk);
2224 l_i_transit_time_uom_tab(l_insert_ctr) := l_new_transit_time_uom_tab(kk);
2225 l_i_creation_date_tab(l_insert_ctr) := l_new_creation_date_tab(kk);
2226 l_i_created_by_tab(l_insert_ctr) := l_new_created_by_tab(kk);
2227 l_i_last_update_date_tab(l_insert_ctr) := l_new_last_update_date_tab(kk);
2228 l_i_last_updated_by_tab(l_insert_ctr) := l_new_last_updated_by_tab(kk);
2229 l_i_last_update_login_tab(l_insert_ctr) := l_new_last_update_login_tab(kk);
2230 l_i_program_app_id_tab(l_insert_ctr) := l_new_program_app_id_tab(kk);
2231 l_i_program_id_tab(l_insert_ctr) := l_new_program_id_tab(kk);
2232 l_i_program_update_date_tab(l_insert_ctr) := l_new_program_update_date_tab(kk);
2233 l_i_request_id_tab(l_insert_ctr) := l_new_request_id_tab(kk);
2234
2235 ELSIF ((l_old_origin_id_tab(kk) is not null) AND
2236 (l_old_destination_id_tab(kk) is not null)) THEN
2237
2238 FND_FILE.PUT_LINE(FND_FILE.log, 'getting update records');
2239 --
2240 IF l_debug_on THEN
2241 WSH_DEBUG_SV.logmsg(l_module_name,'getting update records');
2242 END IF;
2243
2244 --
2245 -- must be an update
2246 --
2247
2248 -- increment the update couter
2249 l_update_ctr := l_update_ctr + 1;
2250
2251 --
2252 -- Populate the update collections with the queried data
2253 --
2254 l_u_origin_id_tab(l_update_ctr) := l_old_origin_id_tab(kk);
2255 l_u_destination_id_tab(l_update_ctr) := l_old_destination_id_tab(kk);
2256 l_u_identifier_type_tab(l_update_ctr) := l_new_identifier_type_tab(kk);
2257 l_u_distance_tab(l_update_ctr) := l_new_distance_tab(kk);
2258 l_u_distance_uom_tab(l_update_ctr) := l_new_distance_uom_tab(kk);
2259 l_u_transit_time_tab(l_update_ctr) := l_new_transit_time_tab(kk);
2260 l_u_transit_time_uom_tab(l_update_ctr) := l_new_transit_time_uom_tab(kk);
2261 l_u_last_update_date_tab(l_update_ctr) := l_new_last_update_date_tab(kk);
2262 l_u_last_updated_by_tab(l_update_ctr) := l_new_last_updated_by_tab(kk);
2263 l_u_last_update_login_tab(l_update_ctr) := l_new_last_update_login_tab(kk);
2264 l_u_program_app_id_tab(l_update_ctr) := l_new_program_app_id_tab(kk);
2265 l_u_program_id_tab(l_update_ctr) := l_new_program_id_tab(kk);
2266 l_u_program_update_date_tab(l_update_ctr) := l_new_program_update_date_tab(kk);
2267 l_u_request_id_tab(l_update_ctr) := l_new_request_id_tab(kk);
2268 ELSE
2269 --
2270 -- This is an error, there should either be both or neither
2271 --
2272 RAISE FTE_DIST_ORIG_DEST_LOAD_ERR;
2273 --
2274 IF l_debug_on THEN
2275 WSH_DEBUG_SV.pop(l_module_name);
2276 END IF;
2277 --
2278 RETURN;
2279 END IF;
2280
2281 END LOOP; -- END OF LOOP_02 (checking for insert and update records
2282
2283
2284
2285 --
2286 -- Do the update if any
2287 --
2288 IF (l_u_origin_id_tab.COUNT > 0) THEN
2289
2290 FND_FILE.PUT_LINE(FND_FILE.log, 'Bulk update of mileage records');
2291 --
2292 IF l_debug_on THEN
2293 WSH_DEBUG_SV.logmsg(l_module_name,'Bulk update of mileage records');
2294 END IF;
2295
2296 --
2297 -- Update records exist, doing a bulk update
2298 --
2299 FORALL m in l_u_origin_id_tab.FIRST..l_u_origin_id_tab.LAST
2300 UPDATE FTE_LOCATION_MILEAGES
2301 SET identifier_type = l_u_identifier_type_tab(m),
2302 distance = l_u_distance_tab(m),
2303 distance_uom = l_u_distance_uom_tab(m),
2304 transit_time = l_u_transit_time_tab(m),
2305 transit_time_uom = l_u_transit_time_uom_tab(m),
2306 last_update_date = l_u_last_update_date_tab(m),
2307 last_updated_by = l_u_last_updated_by_tab(m),
2308 last_update_login = l_u_last_update_login_tab(m),
2309 program_application_id = l_u_program_app_id_tab(m),
2310 program_id = l_u_program_id_tab(m),
2311 program_update_date = l_u_program_update_date_tab(m),
2312 request_id = l_u_request_id_tab(m)
2313 WHERE origin_id = l_u_origin_id_tab(m)
2314 AND destination_id = l_u_destination_id_tab(m);
2315 END IF;
2316
2317
2318 --
2319 -- now do the insert
2320 --
2321 IF (l_i_origin_id_tab.COUNT > 0) THEN
2322
2323 FND_FILE.PUT_LINE(FND_FILE.log, 'Bulk insert of mileage records');
2324 --
2325 IF l_debug_on THEN
2326 WSH_DEBUG_SV.logmsg(l_module_name,'Bulk insert of mileage records');
2327 END IF;
2328
2329 --
2330 -- Insert records exist, doing a bulk insert
2331 --
2332 FORALL i in l_i_origin_id_tab.FIRST..l_i_origin_id_tab.LAST
2333 insert into FTE_LOCATION_MILEAGES(origin_id,
2334 destination_id,
2335 identifier_type,
2336 distance,
2337 distance_uom,
2338 transit_time,
2339 transit_time_uom,
2340 creation_date,
2341 created_by,
2342 last_update_date,
2343 last_updated_by,
2344 last_update_login,
2345 program_application_id,
2346 program_id,
2347 program_update_date,
2348 request_id)
2349 values(l_i_origin_id_tab(i),
2350 l_i_destination_id_tab(i),
2351 l_i_identifier_type_tab(i),
2352 l_i_distance_tab(i),
2353 l_i_distance_uom_tab(i),
2354 l_i_transit_time_tab(i),
2355 l_i_transit_time_uom_tab(i),
2356 l_i_creation_date_tab(i),
2357 l_i_created_by_tab(i),
2358 l_i_last_update_date_tab(i),
2359 l_i_last_updated_by_tab(i),
2360 l_i_last_update_login_tab(i),
2361 l_i_program_app_id_tab(i),
2362 l_i_program_id_tab(i),
2363 l_i_program_update_date_tab(i),
2364 l_i_request_id_tab(i));
2365
2366 END IF;
2367 END IF;
2368
2369 EXIT WHEN c_get_merge_data%NOTFOUND OR c_get_merge_data%NOTFOUND IS NULL;
2370 END LOOP; -- End of LOOP_01 (Cursor query loop
2371
2372 --
2373 -- Close the cursor if open
2374 --
2375 IF (c_get_merge_data%ISOPEN) THEN
2376 CLOSE c_get_merge_data;
2377 END IF;
2378 --
2379 --
2380 -- [ABLUNDEL][12/08/2003][BUG# 3301222] End of Code Changes for bug
2381 -- ---------------------------------------------------------------------
2382
2383
2384 FND_FILE.PUT_LINE(FND_FILE.log, 'Updating the Download files table with upload information');
2385 --
2386 IF l_debug_on THEN
2387 WSH_DEBUG_SV.logmsg(l_module_name,'Updating the Download files table with upload information');
2388 END IF;
2389 --
2390 --
2391 -- Update FTE_MILE_DOWNLOAD_FILES with the upload date
2392 --
2393 update fte_mile_download_files
2394 set upload_date = l_cur_date,
2395 upload_id = p_load_id
2396 where download_file_id = l_download_file_id;
2397
2398 --
2399 -- save all the changes
2400 --
2401 commit;
2402
2403
2404 FND_FILE.PUT_LINE(FND_FILE.log, 'Deleting data from the Download lines table');
2405 --
2406 IF l_debug_on THEN
2407 WSH_DEBUG_SV.logmsg(l_module_name,'Deleting data from the Download lines table');
2408 END IF;
2409 --
2410 --
2411 -- Delete the lines
2412 --
2413 delete fte_mile_download_lines
2414 where download_file_id = l_download_file_id
2415 returning download_file_id BULK COLLECT INTO l_deleted_download_ids;
2416
2417 commit;
2418
2419
2420 FND_FILE.PUT_LINE(FND_FILE.log, 'Returning with success');
2421
2422 --
2423 IF l_debug_on THEN
2424 WSH_DEBUG_SV.logmsg(l_module_name,'Returning with success');
2425 END IF;
2426 --
2427 x_return_status := -1;
2428 x_return_message := null;
2429
2430 --
2431 IF l_debug_on THEN
2432 WSH_DEBUG_SV.pop(l_module_name);
2433 END IF;
2434 --
2435 RETURN;
2436
2437 ELSIF (l_number_of_table_lines > l_line_ctr) THEN
2438 --
2439 -- The file has less lines than when it was created, i.e. lines from the
2440 -- file have been deleted, thus we cannot load
2441 --
2442 -- issue a commit to clear the temp table??
2443 --
2444 IF l_debug_on THEN
2445 WSH_DEBUG_SV.logmsg(l_module_name,'The file has less lines than when it was created, i.e. lines from the file have been deleted, thus we cannot load, RAISE FTE_DIST_LESS_FILE_LINES exception');
2446 END IF;
2447 --
2448 RAISE FTE_DIST_LESS_FILE_LINES;
2449 --
2450 IF l_debug_on THEN
2451 WSH_DEBUG_SV.pop(l_module_name);
2452 END IF;
2453 --
2454 RETURN;
2455
2456 ELSIF (l_number_of_table_lines < l_line_ctr) THEN
2457 --
2458 -- There are too many lines in the file i.e. lines have been added
2459 -- we cannot load this file.
2460 --
2461 IF l_debug_on THEN
2462 WSH_DEBUG_SV.logmsg(l_module_name,'There are too many lines in the file i.e. lines have been added we cannot load this file RAISE FTE_DIST_MANY_FILE_LINES exception');
2463 END IF;
2464 --
2465 RAISE FTE_DIST_MANY_FILE_LINES;
2466 --
2467 IF l_debug_on THEN
2468 WSH_DEBUG_SV.pop(l_module_name);
2469 END IF;
2470 --
2471 RETURN;
2472
2473 END IF;
2474
2475 -- AXE
2476 EXCEPTION
2477 WHEN FTE_DIST_INV_TEMP_TIME_UOM THEN
2478 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INV_TEMP_TIME_UOM');
2479 x_return_status := 2;
2480 x_return_message := FND_MESSAGE.GET;
2481 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - INV TEMP TIME UOM. '||x_return_message);
2482 --
2483 -- Close the file
2484 --
2485 IF (utl_file.is_open(l_dtt_file)) THEN
2486 utl_file.fclose(l_dtt_file);
2487 END IF;
2488
2489 --
2490 -- Close the cursors
2491 --
2492 IF (c_check_download_lines%ISOPEN) THEN
2493 CLOSE c_check_download_lines;
2494 END IF;
2495
2496 IF (c_get_file_info%ISOPEN) THEN
2497 CLOSE c_get_file_info;
2498 END IF;
2499
2500 IF (c_get_download_lines%ISOPEN) THEN
2501 CLOSE c_get_download_lines;
2502 END IF;
2503
2504 IF (c_get_ret_col_info%ISOPEN) THEN
2505 CLOSE c_get_ret_col_info;
2506 END IF;
2507
2508 IF (c_get_ret_enabled%ISOPEN) THEN
2509 CLOSE c_get_ret_enabled;
2510 END IF;
2511
2512 IF (c_get_merge_data%ISOPEN) THEN
2513 CLOSE c_get_merge_data;
2514 END IF;
2515
2516 IF (c_get_time_uom%ISOPEN) THEN
2517 CLOSE c_get_time_uom;
2518 END IF;
2519
2520 IF (c_get_distance_uom%ISOPEN) THEN
2521 CLOSE c_get_distance_uom;
2522 END IF;
2523
2524 --
2525 -- Debug Statements
2526 --
2527 --
2528 IF l_debug_on THEN
2529 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TEMP_TIME_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2530 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TEMP_TIME_UOM');
2531 WSH_DEBUG_SV.pop(l_module_name);
2532 END IF;
2533 --
2534 RETURN;
2535
2536
2537 WHEN FTE_DIST_NO_TIME_UOM THEN
2538 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_TIME_UOM');
2539 x_return_status := 2;
2540 x_return_message := FND_MESSAGE.GET;
2541 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - NO TIME UOM. '||x_return_message);
2542 --
2543 -- Close the file
2544 --
2545 IF (utl_file.is_open(l_dtt_file)) THEN
2546 utl_file.fclose(l_dtt_file);
2547 END IF;
2548
2549 --
2550 -- Close the cursors
2551 --
2552 IF (c_check_download_lines%ISOPEN) THEN
2553 CLOSE c_check_download_lines;
2554 END IF;
2555
2556 IF (c_get_file_info%ISOPEN) THEN
2557 CLOSE c_get_file_info;
2558 END IF;
2559
2560 IF (c_get_download_lines%ISOPEN) THEN
2561 CLOSE c_get_download_lines;
2562 END IF;
2563
2564 IF (c_get_ret_col_info%ISOPEN) THEN
2565 CLOSE c_get_ret_col_info;
2566 END IF;
2567
2568 IF (c_get_ret_enabled%ISOPEN) THEN
2569 CLOSE c_get_ret_enabled;
2570 END IF;
2571
2572 IF (c_get_merge_data%ISOPEN) THEN
2573 CLOSE c_get_merge_data;
2574 END IF;
2575
2576 IF (c_get_time_uom%ISOPEN) THEN
2577 CLOSE c_get_time_uom;
2578 END IF;
2579
2580 IF (c_get_distance_uom%ISOPEN) THEN
2581 CLOSE c_get_distance_uom;
2582 END IF;
2583
2584 --
2585 -- Debug Statements
2586 --
2587 --
2588 IF l_debug_on THEN
2589 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_TIME_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2590 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_TIME_UOM');
2591 WSH_DEBUG_SV.pop(l_module_name);
2592 END IF;
2593 --
2594 RETURN;
2595
2596
2597 WHEN FTE_DIST_INV_TEMP_DIST_UOM THEN
2598 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INV_TEMP_DIST_UOM');
2599 x_return_status := 2;
2600 x_return_message := FND_MESSAGE.GET;
2601 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - INV TEMP DIST UOM. '||x_return_message);
2602 --
2603 -- Close the file
2604 --
2605 IF (utl_file.is_open(l_dtt_file)) THEN
2606 utl_file.fclose(l_dtt_file);
2607 END IF;
2608
2609 --
2610 -- Close the cursors
2611 --
2612 IF (c_check_download_lines%ISOPEN) THEN
2613 CLOSE c_check_download_lines;
2614 END IF;
2615
2616 IF (c_get_file_info%ISOPEN) THEN
2617 CLOSE c_get_file_info;
2618 END IF;
2619
2620 IF (c_get_download_lines%ISOPEN) THEN
2621 CLOSE c_get_download_lines;
2622 END IF;
2623
2624 IF (c_get_ret_col_info%ISOPEN) THEN
2625 CLOSE c_get_ret_col_info;
2626 END IF;
2627
2628 IF (c_get_ret_enabled%ISOPEN) THEN
2629 CLOSE c_get_ret_enabled;
2630 END IF;
2631
2632 IF (c_get_merge_data%ISOPEN) THEN
2633 CLOSE c_get_merge_data;
2634 END IF;
2635
2636 IF (c_get_time_uom%ISOPEN) THEN
2637 CLOSE c_get_time_uom;
2638 END IF;
2639
2640 IF (c_get_distance_uom%ISOPEN) THEN
2641 CLOSE c_get_distance_uom;
2642 END IF;
2643
2644 --
2645 -- Debug Statements
2646 --
2647 --
2648 IF l_debug_on THEN
2649 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_TEMP_DIST_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2650 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_TEMP_DIST_UOM');
2651 WSH_DEBUG_SV.pop(l_module_name);
2652 END IF;
2653 --
2654 RETURN;
2655
2656
2657
2658 WHEN FTE_DIST_NO_DISTANCE_UOM THEN
2659 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_DISTANCE_UOM');
2660 x_return_status := 2;
2661 x_return_message := FND_MESSAGE.GET;
2662 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - NO DISTANCE UOM. '||x_return_message);
2663 --
2664 -- Close the file
2665 --
2666 IF (utl_file.is_open(l_dtt_file)) THEN
2667 utl_file.fclose(l_dtt_file);
2668 END IF;
2669
2670 --
2671 -- Close the cursors
2672 --
2673 IF (c_check_download_lines%ISOPEN) THEN
2674 CLOSE c_check_download_lines;
2675 END IF;
2676
2677 IF (c_get_file_info%ISOPEN) THEN
2678 CLOSE c_get_file_info;
2679 END IF;
2680
2681 IF (c_get_download_lines%ISOPEN) THEN
2682 CLOSE c_get_download_lines;
2683 END IF;
2684
2685 IF (c_get_ret_col_info%ISOPEN) THEN
2686 CLOSE c_get_ret_col_info;
2687 END IF;
2688
2689 IF (c_get_ret_enabled%ISOPEN) THEN
2690 CLOSE c_get_ret_enabled;
2691 END IF;
2692
2693 IF (c_get_merge_data%ISOPEN) THEN
2694 CLOSE c_get_merge_data;
2695 END IF;
2696
2697 IF (c_get_time_uom%ISOPEN) THEN
2698 CLOSE c_get_time_uom;
2699 END IF;
2700
2701 IF (c_get_distance_uom%ISOPEN) THEN
2702 CLOSE c_get_distance_uom;
2703 END IF;
2704
2705 --
2706 -- Debug Statements
2707 --
2708 --
2709 IF l_debug_on THEN
2710 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_DISTANCE_UOM FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2711 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_DISTANCE_UOM');
2712 WSH_DEBUG_SV.pop(l_module_name);
2713 END IF;
2714 --
2715 RETURN;
2716
2717
2718
2719 WHEN FTE_DIST_ORIG_DEST_LOAD_ERR THEN
2720 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_ORIG_DEST_LOAD_ERR');
2721 x_return_status := 2;
2722 x_return_message := FND_MESSAGE.GET;
2723 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - ORIG DEST LOAD ERR. '||x_return_message);
2724 --
2725 -- Close the file
2726 --
2727 IF (utl_file.is_open(l_dtt_file)) THEN
2728 utl_file.fclose(l_dtt_file);
2729 END IF;
2730
2731 --
2732 -- Close the cursors
2733 --
2734 IF (c_check_download_lines%ISOPEN) THEN
2735 CLOSE c_check_download_lines;
2736 END IF;
2737
2738 IF (c_get_file_info%ISOPEN) THEN
2739 CLOSE c_get_file_info;
2740 END IF;
2741
2742 IF (c_get_download_lines%ISOPEN) THEN
2743 CLOSE c_get_download_lines;
2744 END IF;
2745
2746 IF (c_get_ret_col_info%ISOPEN) THEN
2747 CLOSE c_get_ret_col_info;
2748 END IF;
2749
2750 IF (c_get_ret_enabled%ISOPEN) THEN
2751 CLOSE c_get_ret_enabled;
2752 END IF;
2753
2754 IF (c_get_merge_data%ISOPEN) THEN
2755 CLOSE c_get_merge_data;
2756 END IF;
2757
2758 IF (c_get_time_uom%ISOPEN) THEN
2759 CLOSE c_get_time_uom;
2760 END IF;
2761
2762 IF (c_get_distance_uom%ISOPEN) THEN
2763 CLOSE c_get_distance_uom;
2764 END IF;
2765
2766 --
2767 -- Debug Statements
2768 --
2769 --
2770 IF l_debug_on THEN
2771 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_ORIG_DEST_LOAD_ERR FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2772 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_ORIG_DEST_LOAD_ERR');
2773 WSH_DEBUG_SV.pop(l_module_name);
2774 END IF;
2775 --
2776 RETURN;
2777
2778
2779
2780 WHEN FTE_DIST_LESS_FILE_LINES THEN
2781 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_LESS_FILE_LINES');
2782 x_return_status := 2;
2783 x_return_message := FND_MESSAGE.GET;
2784 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - LESS FILE LINES. '||x_return_message);
2785 --
2786 -- Close the file
2787 --
2788 IF (utl_file.is_open(l_dtt_file)) THEN
2789 utl_file.fclose(l_dtt_file);
2790 END IF;
2791
2792 --
2793 -- Close the cursors
2794 --
2795 IF (c_check_download_lines%ISOPEN) THEN
2796 CLOSE c_check_download_lines;
2797 END IF;
2798
2799 IF (c_get_file_info%ISOPEN) THEN
2800 CLOSE c_get_file_info;
2801 END IF;
2802
2803 IF (c_get_download_lines%ISOPEN) THEN
2804 CLOSE c_get_download_lines;
2805 END IF;
2806
2807 IF (c_get_ret_col_info%ISOPEN) THEN
2808 CLOSE c_get_ret_col_info;
2809 END IF;
2810
2811 IF (c_get_ret_enabled%ISOPEN) THEN
2812 CLOSE c_get_ret_enabled;
2813 END IF;
2814
2815 IF (c_get_merge_data%ISOPEN) THEN
2816 CLOSE c_get_merge_data;
2817 END IF;
2818
2819 IF (c_get_time_uom%ISOPEN) THEN
2820 CLOSE c_get_time_uom;
2821 END IF;
2822
2823 IF (c_get_distance_uom%ISOPEN) THEN
2824 CLOSE c_get_distance_uom;
2825 END IF;
2826
2827 --
2828 -- Debug Statements
2829 --
2830 --
2831 IF l_debug_on THEN
2832 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_LESS_FILE_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2833 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_LESS_FILE_LINES');
2834 WSH_DEBUG_SV.pop(l_module_name);
2835 END IF;
2836 --
2837 RETURN;
2838
2839
2840 WHEN FTE_DIST_MANY_FILE_LINES THEN
2841 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_MANY_FILE_LINES');
2842 x_return_status := 2;
2843 x_return_message := FND_MESSAGE.GET;
2844 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - TOO MANY FILE LINES. '||x_return_message);
2845 --
2846 -- Close the file
2847 --
2848 IF (utl_file.is_open(l_dtt_file)) THEN
2849 utl_file.fclose(l_dtt_file);
2850 END IF;
2851
2852 --
2853 -- Close the cursors
2854 --
2855 IF (c_check_download_lines%ISOPEN) THEN
2856 CLOSE c_check_download_lines;
2857 END IF;
2858
2859 IF (c_get_file_info%ISOPEN) THEN
2860 CLOSE c_get_file_info;
2861 END IF;
2862
2863 IF (c_get_download_lines%ISOPEN) THEN
2864 CLOSE c_get_download_lines;
2865 END IF;
2866
2867 IF (c_get_ret_col_info%ISOPEN) THEN
2868 CLOSE c_get_ret_col_info;
2869 END IF;
2870
2871 IF (c_get_ret_enabled%ISOPEN) THEN
2872 CLOSE c_get_ret_enabled;
2873 END IF;
2874
2875 IF (c_get_merge_data%ISOPEN) THEN
2876 CLOSE c_get_merge_data;
2877 END IF;
2878
2879 IF (c_get_time_uom%ISOPEN) THEN
2880 CLOSE c_get_time_uom;
2881 END IF;
2882
2883 IF (c_get_distance_uom%ISOPEN) THEN
2884 CLOSE c_get_distance_uom;
2885 END IF;
2886
2887 --
2888 -- Debug Statements
2889 --
2890 --
2891 IF l_debug_on THEN
2892 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_MANY_FILE_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2893 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_MANY_FILE_LINES');
2894 WSH_DEBUG_SV.pop(l_module_name);
2895 END IF;
2896 --
2897 RETURN;
2898
2899
2900
2901 WHEN FTE_DIST_NULL_FILE_NAME THEN
2902 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NULL_FILE_NAME');
2903 x_return_status := 2;
2904 x_return_message := FND_MESSAGE.GET;
2905 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - NULL FILE NAME. '||x_return_message);
2906 --
2907 -- Close the file
2908 --
2909 IF (utl_file.is_open(l_dtt_file)) THEN
2910 utl_file.fclose(l_dtt_file);
2911 END IF;
2912
2913 --
2914 -- Close the cursors
2915 --
2916 IF (c_check_download_lines%ISOPEN) THEN
2917 CLOSE c_check_download_lines;
2918 END IF;
2919
2920 IF (c_get_file_info%ISOPEN) THEN
2921 CLOSE c_get_file_info;
2922 END IF;
2923
2924 IF (c_get_download_lines%ISOPEN) THEN
2925 CLOSE c_get_download_lines;
2926 END IF;
2927
2928 IF (c_get_ret_col_info%ISOPEN) THEN
2929 CLOSE c_get_ret_col_info;
2930 END IF;
2931
2932 IF (c_get_ret_enabled%ISOPEN) THEN
2933 CLOSE c_get_ret_enabled;
2934 END IF;
2935
2936 IF (c_get_merge_data%ISOPEN) THEN
2937 CLOSE c_get_merge_data;
2938 END IF;
2939
2940 IF (c_get_time_uom%ISOPEN) THEN
2941 CLOSE c_get_time_uom;
2942 END IF;
2943
2944 IF (c_get_distance_uom%ISOPEN) THEN
2945 CLOSE c_get_distance_uom;
2946 END IF;
2947
2948 --
2949 -- Debug Statements
2950 --
2951 --
2952 IF l_debug_on THEN
2953 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NULL_FILE_NAME FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
2954 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NULL_FILE_NAME');
2955 WSH_DEBUG_SV.pop(l_module_name);
2956 END IF;
2957 --
2958 RETURN;
2959
2960
2961
2962 WHEN FTE_DIST_INV_FILENAME_LGTH THEN
2963 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_INVALID_FILE_LENGTH');
2964 x_return_status := 2;
2965 x_return_message := FND_MESSAGE.GET;
2966 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - INVALID FILE LENGTH. '||x_return_message);
2967 --
2968 -- Close the file
2969 --
2970 IF (utl_file.is_open(l_dtt_file)) THEN
2971 utl_file.fclose(l_dtt_file);
2972 END IF;
2973
2974 --
2975 -- Close the cursors
2976 --
2977 IF (c_check_download_lines%ISOPEN) THEN
2978 CLOSE c_check_download_lines;
2979 END IF;
2980
2981 IF (c_get_file_info%ISOPEN) THEN
2982 CLOSE c_get_file_info;
2983 END IF;
2984
2985 IF (c_get_download_lines%ISOPEN) THEN
2986 CLOSE c_get_download_lines;
2987 END IF;
2988
2989 IF (c_get_ret_col_info%ISOPEN) THEN
2990 CLOSE c_get_ret_col_info;
2991 END IF;
2992
2993 IF (c_get_ret_enabled%ISOPEN) THEN
2994 CLOSE c_get_ret_enabled;
2995 END IF;
2996
2997 IF (c_get_merge_data%ISOPEN) THEN
2998 CLOSE c_get_merge_data;
2999 END IF;
3000
3001 IF (c_get_time_uom%ISOPEN) THEN
3002 CLOSE c_get_time_uom;
3003 END IF;
3004
3005 IF (c_get_distance_uom%ISOPEN) THEN
3006 CLOSE c_get_distance_uom;
3007 END IF;
3008
3009 --
3010 -- Debug Statements
3011 --
3012 --
3013 IF l_debug_on THEN
3014 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_INV_FILENAME_LGTH FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3015 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_INV_FILENAME_LGTH');
3016 WSH_DEBUG_SV.pop(l_module_name);
3017 END IF;
3018 --
3019 RETURN;
3020
3021 WHEN FTE_DIST_NO_FILE_DOWNLOAD THEN
3022 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_FILE_DOWNLOAD');
3023 x_return_status := 2;
3024 x_return_message := FND_MESSAGE.GET;
3025 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - NO FILE DOWNLOAD. '||x_return_message);
3026 --
3027 -- Close the file
3028 --
3029 IF (utl_file.is_open(l_dtt_file)) THEN
3030 utl_file.fclose(l_dtt_file);
3031 END IF;
3032
3033 --
3034 -- Close the cursors
3035 --
3036 IF (c_check_download_lines%ISOPEN) THEN
3037 CLOSE c_check_download_lines;
3038 END IF;
3039
3040 IF (c_get_file_info%ISOPEN) THEN
3041 CLOSE c_get_file_info;
3042 END IF;
3043
3044 IF (c_get_download_lines%ISOPEN) THEN
3045 CLOSE c_get_download_lines;
3046 END IF;
3047
3048 IF (c_get_ret_col_info%ISOPEN) THEN
3049 CLOSE c_get_ret_col_info;
3050 END IF;
3051
3052 IF (c_get_ret_enabled%ISOPEN) THEN
3053 CLOSE c_get_ret_enabled;
3054 END IF;
3055
3056 IF (c_get_merge_data%ISOPEN) THEN
3057 CLOSE c_get_merge_data;
3058 END IF;
3059
3060 IF (c_get_time_uom%ISOPEN) THEN
3061 CLOSE c_get_time_uom;
3062 END IF;
3063
3064 IF (c_get_distance_uom%ISOPEN) THEN
3065 CLOSE c_get_distance_uom;
3066 END IF;
3067
3068 --
3069 -- Debug Statements
3070 --
3071 --
3072 IF l_debug_on THEN
3073 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_DOWNLOAD FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3074 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_DOWNLOAD');
3075 WSH_DEBUG_SV.pop(l_module_name);
3076 END IF;
3077 --
3078 RETURN;
3079
3080 WHEN FTE_DIST_NO_FILE_TEMPLATE THEN
3081 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_FILE_TEMPLATE');
3082 x_return_status := 2;
3083 x_return_message := FND_MESSAGE.GET;
3084 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - NO FILE TEMPLATE. '||x_return_message);
3085 --
3086 -- Close the file
3087 --
3088 IF (utl_file.is_open(l_dtt_file)) THEN
3089 utl_file.fclose(l_dtt_file);
3090 END IF;
3091 --
3092 -- Close the cursors
3093 --
3094 IF (c_check_download_lines%ISOPEN) THEN
3095 CLOSE c_check_download_lines;
3096 END IF;
3097
3098 IF (c_get_file_info%ISOPEN) THEN
3099 CLOSE c_get_file_info;
3100 END IF;
3101
3102 IF (c_get_download_lines%ISOPEN) THEN
3103 CLOSE c_get_download_lines;
3104 END IF;
3105
3106 IF (c_get_ret_col_info%ISOPEN) THEN
3107 CLOSE c_get_ret_col_info;
3108 END IF;
3109
3110 IF (c_get_ret_enabled%ISOPEN) THEN
3111 CLOSE c_get_ret_enabled;
3112 END IF;
3113
3114 IF (c_get_merge_data%ISOPEN) THEN
3115 CLOSE c_get_merge_data;
3116 END IF;
3117
3118 IF (c_get_time_uom%ISOPEN) THEN
3119 CLOSE c_get_time_uom;
3120 END IF;
3121
3122 IF (c_get_distance_uom%ISOPEN) THEN
3123 CLOSE c_get_distance_uom;
3124 END IF;
3125
3126 --
3127 -- Debug Statements
3128 --
3129 IF l_debug_on THEN
3130 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_TEMPLATE FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3131 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_TEMPLATE');
3132 WSH_DEBUG_SV.pop(l_module_name);
3133 END IF;
3134 --
3135 RETURN;
3136
3137
3138
3139 WHEN FTE_DIST_NO_FILE_DOWNLOAD_DATE THEN
3140 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_FILE_DOWNLOAD_DATE');
3141 x_return_status := 2;
3142 x_return_message := FND_MESSAGE.GET;
3143 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - NO FILE DOWNLOAD_DATE. '||x_return_message);
3144 --
3145 -- Close the file
3146 --
3147 IF (utl_file.is_open(l_dtt_file)) THEN
3148 utl_file.fclose(l_dtt_file);
3149 END IF;
3150 --
3151 -- Close the cursors
3152 --
3153 IF (c_check_download_lines%ISOPEN) THEN
3154 CLOSE c_check_download_lines;
3155 END IF;
3156
3157 IF (c_get_file_info%ISOPEN) THEN
3158 CLOSE c_get_file_info;
3159 END IF;
3160
3161 IF (c_get_download_lines%ISOPEN) THEN
3162 CLOSE c_get_download_lines;
3163 END IF;
3164
3165 IF (c_get_ret_col_info%ISOPEN) THEN
3166 CLOSE c_get_ret_col_info;
3167 END IF;
3168
3169 IF (c_get_ret_enabled%ISOPEN) THEN
3170 CLOSE c_get_ret_enabled;
3171 END IF;
3172
3173 IF (c_get_merge_data%ISOPEN) THEN
3174 CLOSE c_get_merge_data;
3175 END IF;
3176
3177 IF (c_get_time_uom%ISOPEN) THEN
3178 CLOSE c_get_time_uom;
3179 END IF;
3180
3181 IF (c_get_distance_uom%ISOPEN) THEN
3182 CLOSE c_get_distance_uom;
3183 END IF;
3184
3185 --
3186 -- Debug Statements
3187 --
3188 IF l_debug_on THEN
3189 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_FILE_DOWNLOAD_DATE FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3190 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_FILE_DOWNLOAD_DATE');
3191 WSH_DEBUG_SV.pop(l_module_name);
3192 END IF;
3193 --
3194 RETURN;
3195
3196
3197 WHEN FTE_DIST_FILE_UPLOAD_DONE_PREV THEN
3198 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_FILE_UPLOAD_DONE_PREV');
3199 x_return_status := 2;
3200 x_return_message := FND_MESSAGE.GET;
3201 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - PREVIOUS FILE UPLOAD EXISTS. '||x_return_message);
3202 --
3203 -- Close the file
3204 --
3205 IF (utl_file.is_open(l_dtt_file)) THEN
3206 utl_file.fclose(l_dtt_file);
3207 END IF;
3208 --
3209 -- Close the cursors
3210 --
3211 IF (c_check_download_lines%ISOPEN) THEN
3212 CLOSE c_check_download_lines;
3213 END IF;
3214
3215 IF (c_get_file_info%ISOPEN) THEN
3216 CLOSE c_get_file_info;
3217 END IF;
3218
3219 IF (c_get_download_lines%ISOPEN) THEN
3220 CLOSE c_get_download_lines;
3221 END IF;
3222
3223 IF (c_get_ret_col_info%ISOPEN) THEN
3224 CLOSE c_get_ret_col_info;
3225 END IF;
3226
3227 IF (c_get_ret_enabled%ISOPEN) THEN
3228 CLOSE c_get_ret_enabled;
3229 END IF;
3230
3231 IF (c_get_merge_data%ISOPEN) THEN
3232 CLOSE c_get_merge_data;
3233 END IF;
3234
3235 IF (c_get_time_uom%ISOPEN) THEN
3236 CLOSE c_get_time_uom;
3237 END IF;
3238
3239 IF (c_get_distance_uom%ISOPEN) THEN
3240 CLOSE c_get_distance_uom;
3241 END IF;
3242
3243 --
3244 -- Debug Statements
3245 --
3246 IF l_debug_on THEN
3247 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_FILE_UPLOAD_DONE_PREV FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3248 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_FILE_UPLOAD_DONE_PREV');
3249 WSH_DEBUG_SV.pop(l_module_name);
3250 END IF;
3251 --
3252 RETURN;
3253
3254
3255 WHEN FTE_DIST_NO_DOWNLOAD_LINES THEN
3256 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_DOWNLOAD_LINES');
3257 x_return_status := 2;
3258 x_return_message := FND_MESSAGE.GET;
3259 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - NO DOWNLOAD LINES. '||x_return_message);
3260 --
3261 -- Close the file
3262 --
3263 IF (utl_file.is_open(l_dtt_file)) THEN
3264 utl_file.fclose(l_dtt_file);
3265 END IF;
3266 --
3267 -- Close the cursors
3268 --
3269 IF (c_check_download_lines%ISOPEN) THEN
3270 CLOSE c_check_download_lines;
3271 END IF;
3272
3273 IF (c_get_file_info%ISOPEN) THEN
3274 CLOSE c_get_file_info;
3275 END IF;
3276
3277 IF (c_get_download_lines%ISOPEN) THEN
3278 CLOSE c_get_download_lines;
3279 END IF;
3280
3281 IF (c_get_ret_col_info%ISOPEN) THEN
3282 CLOSE c_get_ret_col_info;
3283 END IF;
3284
3285 IF (c_get_ret_enabled%ISOPEN) THEN
3286 CLOSE c_get_ret_enabled;
3287 END IF;
3288
3289 IF (c_get_merge_data%ISOPEN) THEN
3290 CLOSE c_get_merge_data;
3291 END IF;
3292
3293 IF (c_get_time_uom%ISOPEN) THEN
3294 CLOSE c_get_time_uom;
3295 END IF;
3296
3297 IF (c_get_distance_uom%ISOPEN) THEN
3298 CLOSE c_get_distance_uom;
3299 END IF;
3300
3301 --
3302 -- Debug Statements
3303 --
3304 IF l_debug_on THEN
3305 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_DOWNLOAD_LINES FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3306 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_DOWNLOAD_LINES');
3307 WSH_DEBUG_SV.pop(l_module_name);
3308 END IF;
3309 --
3310 RETURN;
3311
3312
3313 WHEN FTE_DIST_RET_DIST_COL_NO_DATA THEN
3314 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_RET_DIST_COL_NO_DATA');
3315 x_return_status := 2;
3316 x_return_message := FND_MESSAGE.GET;
3317 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - NO RETURN DISTANCE COLUMN DATA. '||x_return_message);
3318 --
3319 -- Close the file
3320 --
3321 IF (utl_file.is_open(l_dtt_file)) THEN
3322 utl_file.fclose(l_dtt_file);
3323 END IF;
3324 --
3325 -- Close the cursors
3326 --
3327 IF (c_check_download_lines%ISOPEN) THEN
3328 CLOSE c_check_download_lines;
3329 END IF;
3330
3331 IF (c_get_file_info%ISOPEN) THEN
3332 CLOSE c_get_file_info;
3333 END IF;
3334
3335 IF (c_get_download_lines%ISOPEN) THEN
3336 CLOSE c_get_download_lines;
3337 END IF;
3338
3339 IF (c_get_ret_col_info%ISOPEN) THEN
3340 CLOSE c_get_ret_col_info;
3341 END IF;
3342
3343 IF (c_get_ret_enabled%ISOPEN) THEN
3344 CLOSE c_get_ret_enabled;
3345 END IF;
3346
3347 IF (c_get_merge_data%ISOPEN) THEN
3348 CLOSE c_get_merge_data;
3349 END IF;
3350
3351 IF (c_get_time_uom%ISOPEN) THEN
3352 CLOSE c_get_time_uom;
3353 END IF;
3354
3355 IF (c_get_distance_uom%ISOPEN) THEN
3356 CLOSE c_get_distance_uom;
3357 END IF;
3358
3359 --
3360 -- Debug Statements
3361 --
3362 IF l_debug_on THEN
3363 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_RET_DIST_COL_NO_DATA FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3364 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_RET_DIST_COL_NO_DATA');
3365 WSH_DEBUG_SV.pop(l_module_name);
3366 END IF;
3367 --
3368 RETURN;
3369
3370 WHEN FTE_DIST_RET_TIME_COL_NO_DATA THEN
3371 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_RET_TIME_COL_NO_DATA');
3372 x_return_status := 2;
3373 x_return_message := FND_MESSAGE.GET;
3374 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - NO RETURN TIME COLUMN DATA. '||x_return_message);
3375 --
3376 -- Close the file
3377 --
3378 IF (utl_file.is_open(l_dtt_file)) THEN
3379 utl_file.fclose(l_dtt_file);
3380 END IF;
3381 --
3382 -- Close the cursors
3383 --
3384 IF (c_check_download_lines%ISOPEN) THEN
3385 CLOSE c_check_download_lines;
3386 END IF;
3387
3388 IF (c_get_file_info%ISOPEN) THEN
3389 CLOSE c_get_file_info;
3390 END IF;
3391
3392 IF (c_get_download_lines%ISOPEN) THEN
3393 CLOSE c_get_download_lines;
3394 END IF;
3395
3396 IF (c_get_ret_col_info%ISOPEN) THEN
3397 CLOSE c_get_ret_col_info;
3398 END IF;
3399
3400 IF (c_get_ret_enabled%ISOPEN) THEN
3401 CLOSE c_get_ret_enabled;
3402 END IF;
3403
3404 IF (c_get_merge_data%ISOPEN) THEN
3405 CLOSE c_get_merge_data;
3406 END IF;
3407
3408 IF (c_get_time_uom%ISOPEN) THEN
3409 CLOSE c_get_time_uom;
3410 END IF;
3411
3412 IF (c_get_distance_uom%ISOPEN) THEN
3413 CLOSE c_get_distance_uom;
3414 END IF;
3415
3416 --
3417 -- Debug Statements
3418 --
3419 IF l_debug_on THEN
3420 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_RET_TIME_COL_NO_DATA FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3421 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_RET_TIME_COL_NO_DATA');
3422 WSH_DEBUG_SV.pop(l_module_name);
3423 END IF;
3424 --
3425 RETURN;
3426
3427
3428 WHEN FTE_DIST_NO_RET_COL_ENABLED THEN
3429 FND_MESSAGE.SET_NAME('FTE','FTE_DIST_NO_RET_COL_ENABLED');
3430 x_return_status := 2;
3431 x_return_message := FND_MESSAGE.GET;
3432 FND_FILE.PUT_LINE(FND_FILE.log, 'ERROR IN PROCEDURE READ_DTT_FILE - NO RETURN COLUMNS ENABLED. '||x_return_message);
3433 --
3434 -- Close the file
3435 --
3436 IF (utl_file.is_open(l_dtt_file)) THEN
3437 utl_file.fclose(l_dtt_file);
3438 END IF;
3439 --
3440 -- Close the cursors
3441 --
3442 IF (c_check_download_lines%ISOPEN) THEN
3443 CLOSE c_check_download_lines;
3444 END IF;
3445
3446 IF (c_get_file_info%ISOPEN) THEN
3447 CLOSE c_get_file_info;
3448 END IF;
3449
3450 IF (c_get_download_lines%ISOPEN) THEN
3451 CLOSE c_get_download_lines;
3452 END IF;
3453
3454 IF (c_get_ret_col_info%ISOPEN) THEN
3455 CLOSE c_get_ret_col_info;
3456 END IF;
3457 IF (c_get_ret_enabled%ISOPEN) THEN
3458 CLOSE c_get_ret_enabled;
3459 END IF;
3460
3461 IF (c_get_merge_data%ISOPEN) THEN
3462 CLOSE c_get_merge_data;
3463 END IF;
3464
3465 IF (c_get_time_uom%ISOPEN) THEN
3466 CLOSE c_get_time_uom;
3467 END IF;
3468
3469 IF (c_get_distance_uom%ISOPEN) THEN
3470 CLOSE c_get_distance_uom;
3471 END IF;
3472
3473 --
3474 -- Debug Statements
3475 --
3476 IF l_debug_on THEN
3477 WSH_DEBUG_SV.logmsg(l_module_name,'ERROR FTE_DIST_NO_RET_COL_ENABLED FTE_BULK_DTT_PKG.READ_DTT_FILE: '||x_return_message);
3478 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION: FTE_DIST_NO_RET_COL_ENABLED');
3479 WSH_DEBUG_SV.pop(l_module_name);
3480 END IF;
3481 --
3482 RETURN;
3483
3484
3485 WHEN OTHERS THEN
3486 x_return_status := 2;
3487 x_return_message := sqlerrm;
3488 FND_FILE.PUT_LINE(FND_FILE.log, 'UNEXPECTED ERROR IN PROCEDURE READ_DTT_FILE. '|| sqlerrm);
3489
3490 --
3491 -- Debug Statements
3492 --
3493 IF l_debug_on THEN
3494 WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.READ_DTT_FILE IS ' ||sqlerrm);
3495 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3496 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3497 END IF;
3498
3499
3500 RETURN;
3501
3502 END READ_DTT_FILE;
3503
3504
3505
3506
3507 -- -------------------------------------------------------------------------- --
3508 -- --
3509 -- NAME: FIRST_TIME --
3510 -- --
3511 -- TYPE: FUNCTION --
3512 -- --
3513 -- PARAMETERS (IN OUT): none --
3514 -- --
3515 -- PARAMETERS (OUT): none --
3516 -- --
3517 -- RETURN: TRUE, FALSE (boolean) --
3518 -- --
3519 -- DESCRIPTION: Return TRUE if this is the first call of the procedure--
3520 -- LOAD_DTT_FILE by the concurrent manager. FALSE --
3521 -- otherwise. This is necessary because the procedure --
3522 -- LOAD_DTT_FILE is called twice by the concurrent --
3523 -- manager controlling the bulkloading process. The first--
3524 -- time to start the process, and the second time after --
3525 -- the sub-processes have finished executing. --
3526 -- --
3527 -- CHANGE CONTROL LOG --
3528 -- ------------------ --
3529 -- --
3530 -- DATE VERSION BY BUG DESCRIPTION --
3531 -- ---------- ------- -------- ------- ---------------------------------- --
3532 -- 2003/07/17 J ABLUNDEL Created --
3533 -- --
3534 -- -------------------------------------------------------------------------- --
3535 FUNCTION FIRST_TIME RETURN BOOLEAN IS
3536
3537 req_data VARCHAR2(100);
3538
3539 l_debug_on BOOLEAN;
3540 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' ||'FIRST_TIME';
3541 l_error_text VARCHAR2(2000);
3542
3543
3544 BEGIN
3545
3546 --
3547 -- SETUP DEBUGGING
3548 --
3549 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3550 IF l_debug_on IS NULL THEN
3551 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3552 END IF;
3553
3554 IF (g_user_debug = 1) THEN
3555 l_debug_on := TRUE;
3556 END IF;
3557
3558
3559 IF l_debug_on THEN
3560 WSH_DEBUG_SV.push(l_module_name);
3561 WSH_DEBUG_SV.logmsg(l_module_name,'---------------FIRST_TIME------------');
3562 END IF;
3563
3564
3565 FND_FILE.PUT_LINE(FND_FILE.log, 'FIRST_TIME');
3566
3567 req_data := FND_CONC_GLOBAL.request_data;
3568
3569 IF (req_data IS NULL) THEN
3570 FND_FILE.PUT_LINE(FND_FILE.log, 'FIRST_TIME returning TRUE');
3571 --
3572 IF l_debug_on THEN
3573 WSH_DEBUG_SV.pop(l_module_name);
3574 END IF;
3575 --
3576 RETURN TRUE;
3577 ELSE
3578 FND_FILE.PUT_LINE(FND_FILE.log, 'FIRST_TIME returning FALSE');
3579 --
3580 IF l_debug_on THEN
3581 WSH_DEBUG_SV.pop(l_module_name);
3582 END IF;
3583 --
3584 RETURN FALSE;
3585 END IF;
3586
3587
3588 EXCEPTION
3589 WHEN OTHERS THEN
3590 Fnd_File.Put_Line(Fnd_File.Log, 'Unexpected Error in Procedure FIRST_TIME' || sqlerrm);
3591
3592 --
3593 -- Debug Statements
3594 --
3595 IF l_debug_on THEN
3596 WSH_DEBUG_SV.logmsg(l_module_name,'THE UNEXPECTED ERROR FROM FTE_BULK_DTT_PKG.FIRST_TIME IS ' ||sqlerrm);
3597 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||sqlerrm,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3598 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3599 END IF;
3600
3601 RETURN FALSE;
3602
3603 END FIRST_TIME;
3604
3605
3606
3607 END FTE_BULK_DTT_PKG;
[Home] [Help]