[Home] [Help]
PACKAGE BODY: APPS.FTE_BULKLOAD_PKG
Source
1 PACKAGE BODY FTE_BULKLOAD_PKG AS
2 /* $Header: FTEBKLDB.pls 120.6 2005/08/19 00:12:46 pkaliyam noship $ */
3
4 -- -----------------------------------------------------------------------------
5 -- --
6 -- NAME: FTE_BULKLOAD_PKG --
7 -- TYPE: PACKAGE BODY --
8 -- DESCRIPTION: Contains procedures for storing bulkload request data in --
9 -- table FTE_BULKLOAD_DATA --
10 -- --
11 -- PROCEDURES and FUNCTIONS: --
12 -- FUNCTION GET_PROCESS_ID --
13 -- SUBMIT_DATA --
14 -- GET_UPLOAD_DIR --
15 -- ADD_ROW --
16 -- PROCEDURES LOAD_FILE --
17 -- PROCESS_DATA --
18 -- READ_BLOCK_FROM_TABLE --
19 -- READ_BLOCK_FROM_DIR --
20 -- PROCESS_BLOCK --
21 -- UPDATE_RID --
22 -- UPLOAD_FILE --
23 -- --
24 ----------------------------------------------------------------------------- --
25
26 G_PKG_NAME VARCHAR2(50) := 'FTE_BULKLOAD_PKG';
27 g_carriage_return VARCHAR2(1) := Fnd_Global.Local_Chr(13);
28 g_linefeed VARCHAR2(1) := Fnd_Global.Local_Chr(10);
29 g_tab VARCHAR2(1) := Fnd_Global.Local_Chr(9);
30
31 ------------------------------------------------------------------
32 -- Procedure: GET_PROCESS_ID
33 -- Purpose: get the process id for qp interface tables
34 -- Return: process_id for qp_interface tables, also known as load_id
35 -------------------------------------------------------------------
36 FUNCTION GET_PROCESS_ID RETURN NUMBER IS
37 l_id NUMBER;
38 BEGIN
39 SELECT qp_process_id_s.nextval
40 INTO l_id
41 FROM dual;
42 RETURN l_id;
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 RETURN -1;
46 WHEN OTHERS THEN
47 RETURN -2;
48 END GET_PROCESS_ID;
49
50 ----------------------------------------------------------------------------------------------
51 -- Procedure: SUBMIT_DATA
52 -- Purpose: Create a new row in the database table 'FTE_BULKLOAD_DATA' with the
53 -- specified parameters when user uses the local file option. This represents a new
54 -- bulkloading request to the concurrent manager.
55 --
56 -- IN parameters:
57 -- 1. p_FileName: name of the file to submit
58 -- 2. p_LoadId: id of the load
59 -- 3. p_FileType: type of the file
60 -- 4. p_LoadType: type of load
61 -- 5. p_RequestId: id to return to user.
62 --
63 -- RETURN: 0 ==> Operation succeeded.
64 -- 1 ==> File Not Found
65 -- 2 ==> Invalid File - No Template (For DTT Upload- Pack J [ABLUNDEL][2003/06/13])
66 -- 3 ==> Invalid File Length (For DTT Upload- Pack J [ABLUNDEL][2003/06/13])
67 -- Propagate all other errors to the caller.
68 -----------------------------------------------------------------------------------------------
69 FUNCTION SUBMIT_DATA ( p_FileName IN VARCHAR2,
70 p_LoadId IN NUMBER,
71 p_FileType IN VARCHAR2,
72 p_LoadType IN VARCHAR2,
73 p_RequestId IN NUMBER ) RETURN NUMBER IS
74
75 v_FileContents BLOB;
76 v_FileLocator BFILE;
77 v_sql_stmt VARCHAR2(200);
78 v_LastUpdateDate DATE;
79 v_FileExists NUMBER;
80 v_DirName VARCHAR2(500);
81
82 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.SUBMIT_DATA';
83
84 cursor c_check_dtt_file(ci_file_name VARCHAR2) IS
85 select fmdf.template_id
86 from fte_mile_download_files fmdf
87 where fmdf.file_name = ci_file_name;
88
89 l_dtt_file_check NUMBER;
90 l_dtt_file_name VARCHAR2(50);
91
92 BEGIN
93
94 FTE_UTIL_PKG.ENTER_Debug(l_module_name);
95
96 IF (g_debug_on) THEN
97 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'File Name ', p_FileName);
98 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Load ID ', p_LoadId);
99 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'File Type ', p_FileType);
100 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Load Type ', p_LoadType);
101 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Request ID', p_RequestId);
102 END IF;
103
104 IF (p_LoadType NOT IN ('LTL_ASSOC', 'DTT_DWNLD')) THEN
105 IF p_FileName IS NULL THEN
106 IF (g_debug_on) THEN
107 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'No filename supplied', WSH_DEBUG_SV.c_proc_level);
108 END IF;
109 FTE_UTIL_PKG.Exit_Debug(l_module_name);
110 RETURN 1;
111 END IF;
112 END IF;
113
114 v_DirName := get_upload_dir;
115 BEGIN
116 v_sql_stmt := 'CREATE OR REPLACE DIRECTORY UPLOAD_DIR AS ''' || v_DirName || '''';
117 EXECUTE IMMEDIATE v_sql_stmt;
118
119 IF (p_LoadType NOT IN ('LTL_ASSOC', 'DTT_DWNLD')) THEN
120 -- initialize the BFILE locator for reading
121 v_FileLocator := BFILENAME('UPLOAD_DIR', p_FileName);
122 v_FileExists := DBMS_LOB.FILEEXISTS(v_FileLocator);
123 IF v_FileExists <> 1 THEN
124 IF (g_debug_on) THEN
125 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'File Not Found', WSH_DEBUG_SV.c_proc_level);
126 END IF;
127 FTE_UTIL_PKG.Exit_Debug(l_module_name);
128 RETURN 1;
129 END IF;
130 END IF;
131
132
133 --+
134 -- [ABLUNDEL][PACK J][DEV][2003/06/13]
135 -- Added check if the file upload is a DTT, we need to check if the
136 -- file is valid or not
137 --+
138 IF (p_LoadType = 'DTT_UPLOAD') THEN
139 --+
140 -- parse the file name to get the first 8 characters, the
141 -- DTT file should be in the format of 'DLF'+5 numbers+'.'+file_extension
142 -- e.g. 'DLF00001.OUT' - after parsing gives us 'DLF00001'
143 --+
144 l_dtt_file_name := substr(p_FileName,1,(instr(p_FileName,'.')-1));
145 --+
146 -- DTT Upload file should be 8 chars in length
147 --+
148
149 IF (length(l_dtt_file_name) <> 8) THEN
150 IF (g_debug_on) THEN
151 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'DTT filename supplied is invalid - invalid length',
152 WSH_DEBUG_SV.c_proc_level);
153 END IF;
154 FTE_UTIL_PKG.Exit_Debug(l_module_name);
155 RETURN 3;
156 END IF;
157
158 l_dtt_file_check := null;
159
160 OPEN c_check_dtt_file(l_dtt_file_name);
161 FETCH c_check_dtt_file INTO l_dtt_file_check;
162 CLOSE c_check_dtt_file;
163
164 IF (l_dtt_file_check is null) THEN
165 --+
166 -- Upload File is invalid
167 --+
168 IF (g_debug_on) THEN
169 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'DTT filename supplied is invalid - not associated with a template',
170 WSH_DEBUG_SV.c_proc_level);
171 END IF;
172 FTE_UTIL_PKG.Exit_Debug(l_module_name);
173 RETURN 2;
174 END IF;
175 END IF;
176 --+
177 -- End of Pack J addition for DTT file upload
178 --+
179
180 v_LastUpdateDate := sysdate;
181 INSERT INTO FTE_BULKLOAD_DATA (LOAD_ID,
182 LOAD_TYPE,
183 FILE_TYPE,
184 FILE_NAME,
185 FILE_SIZE,
186 REQUEST_ID,
187 CONTENT,
188 CREATED_BY,
189 CREATION_DATE,
190 LAST_UPDATED_BY,
191 LAST_UPDATE_DATE,
192 LAST_UPDATE_LOGIN)
193
194 VALUES (p_LoadId, p_LoadType, p_FileType, p_FileName,
195 0, p_RequestId, EMPTY_BLOB(), FND_GLOBAL.USER_ID,
196 v_LastUpdateDate, FND_GLOBAL.USER_ID, v_LastUpdateDate, FND_GLOBAL.USER_ID);
197
198 FTE_UTIL_PKG.Exit_Debug(l_module_name);
199 RETURN 0;
200 END;
201 EXCEPTION
202 WHEN OTHERS THEN
203 IF (c_check_dtt_file%ISOPEN) THEN
204 CLOSE c_check_dtt_file;
205 END IF;
206
207 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
208 p_msg => sqlerrm,
209 p_category => 'O');
210 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
211 RETURN 4;
212 END SUBMIT_DATA;
213
214 -----------------------------------------------------------------------------
215 -- FUNCTION GET_UPLOAD_DIR
216 -- Purpose: get the upload dir from the global variable
217 -- return the directory
218 -----------------------------------------------------------------------------
219 FUNCTION GET_UPLOAD_DIR RETURN VARCHAR2 IS
220 l_db_name VARCHAR2(30);
221
222 BEGIN
223 fnd_profile.get('FTE_BULKLOAD_DIR',g_upload_dirname);
224 return g_upload_dirname;
225 EXCEPTION
226 WHEN OTHERS THEN
227 RAISE;
228 END GET_UPLOAD_DIR;
229
230 ----------------------------------------------------------------------------------
231 -- Function: ADD_ROW
232 --
233 -- Purpose: Adding a row to a table of STRINGARRAYs from values in p_tokens.
234 --
235 -- IN parameters:
236 -- 1. p_tokens: array of values parsed from the file to be stored
237 -- 2. p_table: a pl/sql table of STRINGARRAY
238 -- 3. p_col: a boolean, true if this line is a column header line, false else
239 --
240 -- Returns a number: -1 for no error, 1 for too few items in p_tokens, 2 for too many
241 ----------------------------------------------------------------------------------
242 FUNCTION ADD_ROW(p_tokens IN STRINGARRAY,
243 p_block_header IN OUT NOCOPY block_header_tbl,
244 p_block_data IN OUT NOCOPY block_data_tbl,
245 p_col IN BOOLEAN) RETURN NUMBER IS
246
247 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.ADD_ROW';
248 l_count NUMBER;
249
250 BEGIN
251 FTE_UTIL_PKG.ENTER_Debug(l_module_name);
252
253 IF (g_debug_on) THEN
254 FOR i in 1..p_tokens.COUNT LOOP
255 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'p_tokens i', p_tokens(i));
256 END LOOP;
257 IF (p_col) THEN
258 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'p_col', 'TRUE');
259 ELSE
260 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'p_col', 'FALSE');
261 END IF;
262 END IF;
263
264 IF (p_block_data IS NULL) THEN
265 l_count := 1;
266 ELSE
267 l_count := p_block_data.COUNT + 1;
268 END IF;
269
270 IF (p_col) THEN
271 FOR i in 1..p_tokens.COUNT LOOP
272 -- IF (TRIM(' ' FROM replace(replace(p_tokens(i), ' ', '_'), '*', '')) IS NOT NULL) THEN
273 IF (TRIM(' ' FROM replace(p_tokens(i), '*', '')) IS NOT NULL) THEN
274 -- g_block_header_index(i) := replace(replace(TRIM(p_tokens(i)), ' ', '_'), '*', '');
275 -- p_block_header(replace(replace(TRIM(p_tokens(i)), ' ', '_'), '*', '')) := i;
276 g_block_header_index(i) := replace(TRIM(p_tokens(i)), '*', '');
277 p_block_header(replace(TRIM(p_tokens(i)), '*', '')) := i;
278 END IF;
279 END LOOP;
280 ELSE
281 FOR i in 1..g_block_header_index.COUNT LOOP -- only copy till the size of the header column
282 IF (p_tokens.COUNT >= i) THEN -- if the items are less than the column counts, then copy null to rest
283 p_block_data(l_count)(g_block_header_index(i)) := TRIM(' ' FROM p_tokens(i));
284 ELSE
285 p_block_data(l_count)(g_block_header_index(i)) := NULL;
286 END IF;
287 END LOOP;
288 END IF;
289
290 IF (g_debug_on) THEN
291 FOR i in 1..g_block_header_index.COUNT LOOP
292 IF (p_block_data.COUNT > 0) THEN
293 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, g_block_header_index(i), p_block_data(l_count)(g_block_header_index(i)));
294 ELSE
295 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, g_block_header_index(i));
296 END IF;
297 END LOOP;
298 END IF;
299
300 FTE_UTIL_PKG.Exit_Debug(l_module_name);
301 RETURN -1;
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
306 p_msg => sqlerrm,
307 p_category => 'O');
308 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
309 RETURN 1;
310 END ADD_ROW;
311
312 -----------------------------------------------------------------------------
313 -- PROCEDURE LOAD_FILE
314 --
315 -- Purpose: This is the starting point of the bulkloading process. Submits a
316 -- request to a concurrent program, that starts the rate chart loading
317 -- process.
318 --
319 -- IN Parameters
320 -- 1. p_load_id: The load id of the job
321 -- 2. p_src_filename: file name to be loaded
322 -- 3. p_currency: currency for LTL load
323 -- 4. p_uom_code: uom for LTL load
324 -- 5. p_orig_country: origin country for LTL load
325 -- 6. p_dest_country: destination country for LTL load
326 -- 7. p_service_code: service level code for LTL load
327 -- 8. p_action_code: LTL load action
328 -- 9. p_tariff_name: LTL load tariff name
329 -- 10. p_resp_id:
330 -- 11. p_resp_appl_id:
331 -- 12. p_user_id:
332 -- 13. p_user_debug: debug option
333
334 -- Out Parameters
335 -- 1. x_request_id: The request id of the bulkload process
336 -- 2. x_error_msg:
337 -----------------------------------------------------------------------------
338 PROCEDURE LOAD_FILE ( p_load_id IN NUMBER,
339 p_src_filename IN VARCHAR2,
340 p_currency IN VARCHAR2,
341 p_uom_code IN VARCHAR2,
342 p_origin_country IN VARCHAR2,
343 p_dest_country IN VARCHAR2,
344 p_service_code IN VARCHAR2,
345 p_action_code IN VARCHAR2,
346 p_tariff_name IN VARCHAR2,
347 p_resp_id IN NUMBER,
348 p_resp_appl_id IN NUMBER,
349 p_user_id IN NUMBER,
350 p_user_debug IN NUMBER,
351 x_request_id OUT NOCOPY NUMBER,
352 x_error_msg OUT NOCOPY VARCHAR2) IS
353
354 l_program VARCHAR2(256);
355 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.LOAD_FILE';
356 x_status NUMBER := -1;
357
358 BEGIN
359
360 FTE_UTIL_PKG.INIT_DEBUG(p_user_debug);
361 FTE_UTIL_PKG.ENTER_Debug(l_module_name);
362
363 IF (g_debug_on) THEN
364 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'File Name ', p_src_filename);
365 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Load ID ', p_load_id);
366 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Currency ', p_currency);
367 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'UOM code ', p_uom_code);
368 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Origin Country', p_origin_country);
369 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Dest Country ', p_dest_country);
370 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Service code ', p_service_code);
371 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Action code ', p_action_code);
372 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Tariff Name ', p_tariff_name);
373 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'User Debug ', p_user_debug);
374 END IF;
375
376 fnd_global.apps_initialize(user_id => p_user_id,
377 resp_id => p_resp_id,
378 resp_appl_id => p_resp_appl_id);
379
380 --+
381 -- The non-null tariff name parameter indicates
382 -- the load is of type LTL.
383 --+
384 IF (p_tariff_name IS NULL) THEN
385 l_program := 'FTE_BULKLOADER';
386 ELSE
387 l_program := 'FTE_LTL_BULK_LOADER';
388 END IF;
389
390 x_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'FTE',
391 program => l_program,
392 description => null,
393 start_time => null,
394 sub_request => false,
395 argument1 => p_load_id,
396 argument2 => p_src_filename,
397 argument3 => p_currency,
398 argument4 => p_uom_code,
399 argument5 => p_origin_country,
400 argument6 => p_dest_country,
401 argument7 => p_service_code,
402 argument8 => p_action_code,
403 argument9 => p_tariff_name,
404 argument10 => p_user_debug);
405
406
407 x_error_msg := fnd_message.get;
408 COMMIT;
409
410 IF (g_debug_on) THEN
411 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'x_error_msg ', x_error_msg);
412 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'x_request_id', x_request_id);
413 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'p_user_id ', p_user_id);
414 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'p_resp_id ',p_resp_id);
415 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'p_resp_appl_id', p_resp_appl_id);
416 END IF;
417
418 FTE_UTIL_PKG.Exit_Debug(l_module_name);
419 EXCEPTION
420 WHEN OTHERS THEN
421 x_error_msg := sqlerrm;
422 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
423 p_msg => x_error_msg,
424 p_category => 'O');
425 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
426 END LOAD_FILE;
427
428 ------------------------------------------------------------------------------------
429 -- Procedure: PROCESS_BLOCK
430 --
431 -- Purpose: Call the approrite PROCESS_DATA procedure from different packages
432 --
433 -- IN parameters:
434 -- 1. p_block_type: the type of block
435 -- 2. p_table: table
436 -- 3. p_line_number: line number for the first line of the block
437 --
438 -- OUT parameters:
439 -- 1. x_status: status of procedure, -1 is no error
440 -- 2. x_error_msg: error message if any
441 --
442 -- According to different types of blocks, the number of columns vary and uses different table
443 ------------------------------------------------------------------------------------
444 PROCEDURE PROCESS_BLOCK(p_block_type IN VARCHAR2,
445 p_block_header IN block_header_tbl,
446 p_block_data IN block_data_tbl,
447 p_line_number IN NUMBER,
448 x_status OUT NOCOPY NUMBER,
449 x_error_msg OUT NOCOPY VARCHAR2) IS
450
451 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_BLOCK';
452 BEGIN
453 FTE_UTIL_PKG.ENTER_DEBUG(l_module_name);
454
455 x_status := -1;
456
457 IF (p_block_type IN ('RATE_CHART', 'RATE_LINE', 'RATE_BREAK','RATING_ATTRIBUTE',
458 'CHARGES_DISCOUNTS', 'CHARGES_DISCOUNTS_LINE', 'ADJUSTED_RATE_CHART')) THEN
459
460 FTE_RATE_CHART_LOADER.PROCESS_DATA(p_type => p_block_type,
461 p_block_header => p_block_header,
462 p_block_data => p_block_data,
463 p_line_number => p_line_number,
464 x_status => x_status,
465 x_error_msg => x_error_msg);
466 ELSIF (p_block_type IN ('REGION', 'ZONE')) THEN
467 FTE_REGION_ZONE_LOADER.PROCESS_DATA(p_type => p_block_type,
468 p_block_header => p_block_header,
469 p_block_data => p_block_data,
470 p_line_number => p_line_number,
471 x_status => x_status,
472 x_error_msg => x_error_msg);
473 ElSIF (p_block_type IN ('SCHEDULE', 'SERVICE', 'SERVICE_RATING_SETUP')) THEN
474 FTE_LANE_LOADER.PROCESS_DATA(p_type => p_block_type,
475 p_block_header => p_block_header,
476 p_block_data => p_block_data,
477 p_line_number => p_line_number,
478 x_status => x_status,
479 x_error_msg => x_error_msg);
480 ELSIF (p_block_type IN ('RATING_ZONE_CHART', 'RATING_SETUP', 'ORIGIN', 'DESTINATION')) THEN
481 FTE_PARCEL_LOADER.PROCESS_DATA(p_type => p_block_type,
482 p_block_header => p_block_header,
483 p_block_data => p_block_data,
484 p_line_number => p_line_number,
485 x_status => x_status,
486 x_error_msg => x_error_msg);
487 ELSIF (UPPER(p_block_type) IN ('TL_SERVICES', 'TL_SURCHARGES', 'FACILITY_CHARGES', 'TL_BASE_RATES')) THEN
488 FTE_TL_LOADER.PROCESS_DATA(p_type => p_block_type,
489 p_block_header => p_block_header,
490 p_block_data => p_block_data,
491 p_line_number => p_line_number,
492 x_status => x_status,
493 x_error_msg => x_error_msg);
494 ELSE
495 x_status := 2;
496 x_error_msg := FTE_UTIL_PKG.GET_MSG(P_Name => 'FTE_TYPE_UNKNOWN',
497 P_Tokens => STRINGARRAY('TYPE'),
498 P_values => STRINGARRAY(p_block_type));
499 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
500 p_msg => x_error_msg,
501 p_category => 'D',
502 p_line_number => p_line_number);
503 END IF;
504
505 FTE_UTIL_PKG.Exit_Debug(l_module_name);
506
507 EXCEPTION
508 WHEN OTHERS THEN
509 x_status := 2;
510 x_error_msg := sqlerrm;
511 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
512 p_msg => x_error_msg,
513 p_category => 'O',
514 p_line_number => p_line_number-1);
515 FTE_UTIL_PKG.EXIT_DEBUG(l_module_name);
516
517 END PROCESS_BLOCK;
518
519 ---------------------------------------------------------------------------------------------------
520 -- Procedure: READ_BLOCK_FROM_DIR
521 --
522 -- Purpose: Reading a file from the server directory, storing each block in the file in a
523 -- pl/sql table and call the specific loader packages.
524 --
525 -- IN parameters:
526 -- 1. p_file_name: file name to be read
527 -- 2. p_load_id: loading id
528 --
529 -- OUT parameters:
530 -- 1. x_status: status of the process, -1 is no errors
531 -- 2. x_error_msg: error message when status <> -1
532 ---------------------------------------------------------------------------------------------------
533 PROCEDURE READ_BLOCK_FROM_DIR (p_file_name IN VARCHAR2,
534 p_load_id IN NUMBER,
535 x_status OUT NOCOPY NUMBER,
536 x_error_msg OUT NOCOPY VARCHAR2) IS
537
538 l_tokens STRINGARRAY;
539 l_debug_on BOOLEAN;
540 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.READ_BLOCK_FROM_DIR';
541 x_chart_file UTL_FILE.file_type;
542 l_src_file_dir VARCHAR2(500);
543 l_line VARCHAR2(1000);
544 l_block_type VARCHAR2(100) := NULL;
545 l_section_line BOOLEAN := TRUE;
546 l_body_block BOOLEAN := FALSE;
547 l_column_line BOOLEAN := FALSE;
548 l_last_line_null BOOLEAN := FALSE;
549 l_processed_lines NUMBER := 0;
550 l_block_header block_header_tbl;
551 l_block_data block_data_tbl;
552
553 BEGIN
554 FTE_UTIL_PKG.ENTER_Debug(l_module_name);
555 x_status := -1;
556
557 IF (g_debug_on) THEN
558 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'File Name', p_file_name);
559 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Load ID', p_load_id);
560 END IF;
561
562 l_src_file_dir := Fte_Bulkload_Pkg.Get_Upload_Dir;
563 x_chart_file := utl_file.fopen(l_src_file_dir, p_file_name, 'R');
564
565 LOOP
566 utl_file.get_line(x_chart_file, l_line);
567 l_line := replace(l_line, g_carriage_return, ''); --dos2unix conversion
568 l_line := TRIM(' ' FROM l_line);
569
570 IF l_section_line THEN
571
572 IF ((replace(replace(l_line, g_tab, ''), ' ', '')) IS NOT NULL) THEN
573 IF ((l_line IS NOT NULL) AND (l_line NOT IN ('%HEADER%'))) THEN
574 l_last_line_null := FALSE;
575 l_block_type := Trim(' ' FROM upper(replace(l_line, g_tab, '')));
576 l_section_line := FALSE;
577 l_body_block := TRUE;
578 l_column_line := TRUE;
579 END IF;
580 l_processed_lines := l_processed_lines +1;
581 END IF;
582
583 ELSIF l_body_block THEN
584
585 -- build the pl/sql table with the block
586 IF ((replace(replace(l_line, g_tab, ''), ' ', '')) IS NOT NULL) THEN
587
588 l_last_line_null := FALSE;
589
590 l_tokens := FTE_UTIL_PKG.TOKENIZE_STRING(l_line, g_tab);
591
592 x_status := ADD_ROW(l_tokens, l_block_header, l_block_data, l_column_line);
593
594 IF (x_status = 1) THEN
595 -- too few values in p_tokens
596 FTE_UTIL_PKG.Exit_Debug(l_module_name);
597 RETURN;
598 ELSIF (x_status = 2) THEN
599 -- too many values in p_tokens
600 FTE_UTIL_PKG.Exit_Debug(l_module_name);
601 RETURN;
602 END IF;
603
604 IF (l_column_line) THEN -- if column line is this line, next line wont' be a column line
605 l_column_line := FALSE;
606 END IF;
607 ELSE
608
609 IF (l_column_line) THEN
610 --throw error about no information
611 x_error_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BLOCK_NO_LINE',
612 p_tokens => STRINGARRAY('BLOCK'),
613 p_values => STRINGARRAY(l_block_type));
614 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
615 p_msg => x_error_msg,
616 p_category => 'A');
617
618 FTE_UTIL_PKG.Exit_Debug(l_module_name);
619 x_status := 1;
620 RETURN;
621 END IF;
622
623 l_last_line_null := TRUE;
624 l_section_line := TRUE;
625 l_body_block := FALSE;
626 --call packages to process block
627
628 IF (g_debug_on) THEN
629 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'l_block_type', l_block_type);
630 END IF;
631
632 PROCESS_BLOCK(p_block_type => l_block_type,
633 p_block_header => l_block_header,
634 p_block_data => l_block_data,
635 p_line_number => l_processed_lines-l_block_data.COUNT-1,
636 x_status => x_status,
637 x_error_msg => x_error_msg);
638
639 IF (x_status <> -1) THEN
640 --use line number to report the error
641 FTE_UTIL_PKG.Exit_Debug(l_module_name);
642 RETURN;
643 END IF;
644
645 l_block_header.DELETE;
646 l_block_data.DELETE;
647 g_block_header_index.DELETE;
648
649 END IF;
650 l_processed_lines := l_processed_lines +1;
651
652 END IF;
653 END LOOP;
654
655 IF (g_debug_on) THEN
656 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Finished Reading File.');
657 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of lines processed', l_processed_lines);
658 END IF;
659
660 FTE_UTIL_PKG.Exit_Debug(l_module_name);
661 EXCEPTION
662 WHEN NO_DATA_FOUND THEN
663 utl_file.fclose(x_chart_file);
664
665 IF (NOT l_last_line_null) THEN
666 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'l_block_type', l_block_type);
667 PROCESS_BLOCK(p_block_type => l_block_type,
668 p_block_header => l_block_header,
669 p_block_data => l_block_data,
670 p_line_number => l_processed_lines - l_block_data.COUNT-1,
671 x_status => x_status,
672 x_error_msg => x_error_msg);
673
674 IF (x_status <> -1) THEN
675 --use line number to report the error
676 FTE_UTIL_PKG.Exit_Debug(l_module_name);
677 RETURN;
678 END IF;
679
680 l_block_header.DELETE;
681 l_block_data.DELETE;
682 g_block_header_index.DELETE;
683
684 END IF;
685 l_processed_lines := l_processed_lines +1;
686
687 IF (g_debug_on) THEN
688 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Finished Reading File.');
689 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of lines processed', l_processed_lines);
690 END IF;
691
692 FTE_UTIL_PKG.Exit_Debug(l_module_name);
693 WHEN UTL_FILE.INVALID_PATH THEN
694 -- x_error_msg := 'File ' || l_src_file_dir || '/' || p_file_name || ' Not Accessible' ||
695 -- fnd_global.newline || 'Please make sure that the directory is accessible to UTL_FILE.';
696
697 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
698 p_msg => 'FTE_FILE_NOT_ACCESSIBLE',
699 p_category => 'E');
700
701 x_status := 2;
702 FTE_UTIL_PKG.Exit_Debug(l_module_name);
703 WHEN UTL_FILE.INVALID_OPERATION THEN
704 -- x_error_msg := 'ERROR: The file does not exist, or file or directory access was denied by the operating system.'
705 -- || fnd_global.newline || 'Please verify file and directory access privileges on the file system.';
706
707 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
708 p_msg => 'FTE_FILE_NOT_EXIST',
709 p_category => 'E');
710
711 x_status := 2;
712 FTE_UTIL_PKG.Exit_Debug(l_module_name);
713 WHEN OTHERS THEN
714 x_error_msg := 'Unexpected error while reading file: [Row ' || l_processed_lines || '].'
715 || fnd_global.newline || l_line || fnd_global.newline || sqlerrm;
716
717 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, x_error_msg);
718
719 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
720 p_msg => sqlerrm,
721 p_category => 'O',
722 p_line_number => l_processed_lines);
723 x_status := 2;
724 FTE_UTIL_PKG.Exit_Debug(l_module_name);
725 END READ_BLOCK_FROM_DIR;
726
727 ---------------------------------------------------------------------------------------------------
728 -- Procedure: READ_BLOCK_FROM_TABLE
729 --
730 -- Purpose: Reading a file from the database temp table, storing each block in the file in a
731 -- pl/sql table and call the specific loader packages.
732 --
733 -- IN parameters:
734 -- 1. p_file_name: file name to be read
735 -- 2. p_load_id: loading id
736 --
737 -- OUT parameters:
738 -- 1. x_status: status of the process, -1 is no errors
739 -- 2. x_error_msg: error message when status <> -1
740 ---------------------------------------------------------------------------------------------------
741 PROCEDURE READ_BLOCK_FROM_TABLE (p_file_name IN VARCHAR2,
742 p_load_id IN NUMBER,
743 x_status OUT NOCOPY NUMBER,
744 x_error_msg OUT NOCOPY VARCHAR2) IS
745
746 l_size NUMBER;
747 l_language VARCHAR2(40);
748 l_content BLOB;
749 l_amount BINARY_INTEGER := 12000;
750 l_position INTEGER := 1;
751 data_buffer VARCHAR2(32000);
752 temp_buffer VARCHAR2(32000);
753 l_lines STRINGARRAY;
754 l_tokens STRINGARRAY;
755 l_debug_on BOOLEAN;
756 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.READ_BLOCK_FROM_TABLE';
757 l_block_type VARCHAR2(100);
758 l_section_line BOOLEAN;
759 l_body_block BOOLEAN;
760 l_column_line BOOLEAN;
761 l_processed_lines NUMBER := 0;
762 l_block_header block_header_tbl;
763 l_block_data block_data_tbl;
764 l_last_block_processed BOOLEAN;
765
766 BEGIN
767 FTE_UTIL_PKG.ENTER_Debug(l_module_name);
768 x_status := -1;
769
770 IF (g_debug_on) THEN
771 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'File Name', p_file_name);
772 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Load ID', p_load_id);
773 END IF;
774
775 SELECT content, file_size INTO l_content, l_size
776 FROM fte_bulkload_data
777 WHERE file_name = p_file_name
778 AND load_id = p_load_id;
779
780 data_buffer := NULL;
781 l_section_line := TRUE;
782 l_column_line := FALSE;
783 l_body_block := FALSE;
784 l_block_type := NULL;
785 l_last_block_processed := FALSE;
786
787 WHILE l_size > 0 LOOP
788 --read a big chunk at a time:
789 dbms_lob.read (l_content, l_amount, l_position, temp_buffer);
790 data_buffer := data_buffer || utl_raw.cast_to_varchar2(temp_buffer);
791 data_buffer := replace(data_buffer, g_carriage_return, ''); --dos2unix conversion
792
793 --Now tokenize by linefeed
794 l_lines := FTE_UTIL_PKG.TOKENIZE_STRING(data_buffer, g_linefeed);
795
796 FOR k IN 1..l_lines.COUNT-1 LOOP
797
798 IF l_section_line THEN
799
800 IF ((replace(replace(l_lines(k), g_tab, ''), ' ', '')) IS NOT NULL) THEN
801 IF (Trim(' ' FROM l_lines(k)) IS NOT NULL) AND (l_lines(k) NOT IN ('%HEADER%')) THEN
802 l_block_type := Trim(' ' FROM upper(replace(l_lines(k), g_tab, '')));
803 l_section_line := FALSE;
804 l_body_block := TRUE;
805 l_column_line := TRUE;
806 l_last_block_processed := FALSE;
807 END IF;
808 l_processed_lines := l_processed_lines +1;
809 END IF;
810
811 ELSIF l_body_block THEN
812
813 -- build the pl/sql table with the block
814 IF ((replace(replace(l_lines(k), g_tab, ''), ' ', '')) IS NOT NULL) THEN
815
816 l_tokens := FTE_UTIL_PKG.TOKENIZE_STRING(l_lines(k), g_tab);
817
818 x_status := ADD_ROW(l_tokens, l_block_header, l_block_data, l_column_line);
819
820 IF (x_status = 1) THEN
821 -- too few values in p_tokens
822 x_error_msg := FTE_UTIL_PKG.GET_MSG('FTE_TOO_FEW_COLUMNS');
823 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
824 p_msg => x_error_msg,
825 p_category => 'D',
826 p_line_number => l_processed_lines+1);
827 FTE_UTIL_PKG.Exit_Debug(l_module_name);
828 RETURN;
829 ELSIF (x_status = 2) THEN
830 -- too many values in p_tokens
831 x_error_msg := FTE_UTIL_PKG.GET_MSG('FTE_TOO_MANY_COLUMNS');
832 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
833 p_msg => x_error_msg,
834 p_category => 'D',
835 p_line_number => l_processed_lines+1);
836 FTE_UTIL_PKG.Exit_Debug(l_module_name);
837 RETURN;
838 END IF;
839
840 IF (l_column_line) THEN -- if column line is this line, next line wont' be a column line
841 l_column_line := FALSE;
842 END IF;
843 ELSE
844
845 l_section_line := TRUE;
846 l_body_block := FALSE;
847 l_last_block_processed := TRUE;
848 --call packages to process block
849
850 IF (g_debug_on) THEN
851 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'l_block_type', l_block_type);
852 END IF;
853 PROCESS_BLOCK(p_block_type => l_block_type,
854 p_block_header => l_block_header,
855 p_block_data => l_block_data,
856 p_line_number => l_processed_lines - l_block_data.COUNT-1,
857 x_status => x_status,
858 x_error_msg => x_error_msg);
859
860 IF (x_status <> -1) THEN
861 FTE_UTIL_PKG.Exit_Debug(l_module_name);
862 RETURN;
863 END IF;
864
865 l_block_header.DELETE;
866 l_block_data.DELETE;
867 g_block_header_index.DELETE;
868
869 END IF;
870 l_processed_lines := l_processed_lines +1;
871
872 END IF;
873
874 END LOOP; -- end for l_lines loop
875
876 l_position := l_position + l_amount;
877 l_size := l_size - l_amount;
878
879 --Append the last remaining to the next chunk because it might not be complete
880 data_buffer := l_lines(l_lines.COUNT);
881 END LOOP;
882
883 --process last line
884 IF (NOT l_last_block_processed) THEN
885
886 IF ((replace(data_buffer, ' ', '')) IS NOT NULL) THEN
887
888 l_tokens := FTE_UTIL_PKG.TOKENIZE_STRING(data_buffer, g_tab);
889
890 x_status := ADD_ROW(l_tokens, l_block_header, l_block_data, l_column_line);
891
892 IF (x_status = 1) THEN
893 -- too few values in p_tokens
894 FTE_UTIL_PKG.Exit_Debug(l_module_name);
895 RETURN;
896 ELSIF (x_status = 2) THEN
897 -- too many values in p_tokens
898 FTE_UTIL_PKG.Exit_Debug(l_module_name);
899 RETURN;
900 END IF;
901
902 IF (l_column_line) THEN -- if column line is this line, next line wont' be a column line
903 l_column_line := FALSE;
904 END IF;
905 END IF;
906
907 l_section_line := TRUE;
908 l_body_block := FALSE;
909 --call packages to process block
910 PROCESS_BLOCK(p_block_type => l_block_type,
911 p_block_header => l_block_header,
912 p_block_data => l_block_data,
913 p_line_number => l_processed_lines - l_block_data.COUNT-1,
914 x_status => x_status,
915 x_error_msg => x_error_msg);
916
917 IF (x_status <> -1) THEN
918 FTE_UTIL_PKG.Exit_Debug(l_module_name);
919 RETURN;
920 END IF;
921
922 l_block_header.DELETE;
923 l_block_data.DELETE;
924 g_block_header_index.DELETE;
925
926 l_processed_lines := l_processed_lines +1;
927
928 END IF;
929
930 IF (x_status <> -1) THEN
931 FTE_UTIL_PKG.Exit_Debug(l_module_name);
932 RETURN;
933 END IF;
934
935 IF (g_debug_on) THEN
936 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Finished Reading File.');
937 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Number of lines processed', l_processed_lines);
938 END IF;
939 FTE_UTIL_PKG.Exit_Debug(l_module_name);
940 EXCEPTION
941 WHEN OTHERS THEN
942 x_error_msg := 'Unexpected error while reading file: [Row ' || l_processed_lines || '].'
943 || fnd_global.newline || sqlerrm;
944
945 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, x_error_msg);
946
947 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
948 p_msg => sqlerrm,
949 p_category => 'O',
950 p_line_number => l_processed_lines);
951 x_status := 2;
952 FTE_UTIL_PKG.Exit_Debug(l_module_name);
953 END READ_BLOCK_FROM_TABLE;
954
955 PROCEDURE PRINT_END_OF_REPORT IS
956
957 l_msg VARCHAR2(2000);
958 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_DATA';
959
960 BEGIN
961
962 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
963 p_msg => ' ',
964 p_category => NULL);
965
966 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_ROLLBACK'); -- Rolling back the above entities creation due to the error mentioned.
967
968 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
969 p_msg => ' ',
970 p_category => NULL);
971
972 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
973 p_msg => l_msg,
974 p_category => NULL);
975
976 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
977 p_msg => '+---------------------------------------------------------------------------+',
978 p_category => NULL);
979
980 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
981 p_msg => ' ',
982 p_category => NULL);
983
984 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_END_REPORT'); -- *** End of BulkLoader Report ***
985
986 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
987 p_msg => l_msg,
988 p_category => NULL);
989
990 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
991 p_msg => ' ',
992 p_category => NULL);
993
994 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
995 p_msg => '+---------------------------------------------------------------------------+',
996 p_category => NULL);
997 END PRINT_END_OF_REPORT;
998 -----------------------------------------------------------------------------------
999 -- Procedure: PROCESS_DATA
1000 --
1001 -- Purpose: Read the input file into blocks and call approriate loader packages
1002 --
1003 -- IN parameters:
1004 -- 1. p_load_id: loading id
1005 -- 2. p_src_filename: filename
1006 -- 3. p_currency: currency for LTL loader
1007 -- 4. p_uom_code: uom for LTL load
1008 -- 5. p_orig_country: origin country for LTL load
1009 -- 6. p_dest_country: destination country for LTL load
1010 -- 7. p_service_code: service level code for LTL load
1011 -- 8. p_action_code: LTL load action
1012 -- 9. p_tariff_name: LTL load tariff name
1013 -- 10. p_user_debug: debug option
1014 -- OUT parameters:
1015 -- 1. x_status: -1 for no error
1016 -- 2. x_error_msg: error message if status <> -1
1017 -----------------------------------------------------------------------------------
1018 PROCEDURE PROCESS_DATA (ERRBUF OUT NOCOPY VARCHAR2,
1019 RETCODE OUT NOCOPY VARCHAR2,
1020 p_load_id IN NUMBER,
1021 p_src_filename IN VARCHAR2,
1022 p_currency IN VARCHAR2,
1023 p_uom_code IN VARCHAR2,
1024 p_origin_country IN VARCHAR2,
1025 p_dest_country IN VARCHAR2,
1026 p_service_code IN VARCHAR2,
1027 p_action_code IN VARCHAR2,
1028 p_tariff_name IN VARCHAR2,
1029 p_user_debug IN NUMBER) IS
1030
1031 l_blob BLOB;
1032 l_load_type VARCHAR2(20);
1033 l_file_name VARCHAR2(40);
1034 l_dir_name VARCHAR2(100);
1035 l_source VARCHAR2(30);
1036 l_type VARCHAR2(30);
1037 x_status NUMBER;
1038 l_request_id NUMBER;
1039 x_error_msg VARCHAR2(1000);
1040 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.PROCESS_DATA';
1041 l_msg VARCHAR2(2000);
1042
1043 BEGIN
1044
1045 --+
1046 -- Start the DEBUGGER
1047 --+
1048 FTE_UTIL_PKG.INIT_DEBUG(p_user_debug);
1049
1050 FTE_UTIL_PKG.ENTER_Debug(l_module_name);
1051 RETCODE := 0;
1052
1053 g_load_id := p_load_id;
1054
1055 IF (g_debug_on) THEN
1056 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'File Name', p_src_filename);
1057 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Load ID', p_load_id);
1058 END IF;
1059
1060 UPLOAD_FILE(p_load_id, l_type, l_blob, l_file_name, l_dir_name, x_status);
1061
1062 FND_PROFILE.GET('FTE_BULKLOAD_SOURCE_TYPE', l_source);
1063
1064 IF (g_debug_on) THEN
1065 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Load type', l_type);
1066 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Reading from ', l_source);
1067 END IF;
1068
1069 --+
1070 -- Write the header for BulkLoader output file
1071 --+
1072
1073 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1074 p_msg => '+---------------------------------------------------------------------------+',
1075 p_category => NULL);
1076 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1077 p_msg => ' ',
1078 p_category => NULL);
1079
1080 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_START_REPORT'); -- *** Start of BulkLoader Report ***
1081
1082 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1083 p_msg => l_msg,
1084 p_category => NULL);
1085
1086 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1087 p_msg => ' ',
1088 p_category => NULL);
1089
1090 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1091 p_msg => '+---------------------------------------------------------------------------+',
1092 p_category => NULL);
1093
1094 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1095 p_msg => ' ',
1096 p_category => NULL);
1097
1098 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_PROCESSING', -- File Processed :
1099 p_tokens => STRINGARRAY('NAME'),
1100 p_values => STRINGARRAY(p_src_filename));
1101
1102 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1103 p_msg => l_msg,
1104 p_category => NULL);
1105
1106 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_TYPE', -- Type of Load :
1107 p_tokens => STRINGARRAY('TYPE'),
1108 p_values => STRINGARRAY(l_type));
1109
1110 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1111 p_msg => l_msg,
1112 p_category => NULL);
1113
1114 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_LOADID', -- Load ID :
1115 p_tokens => STRINGARRAY('ID'),
1116 p_values => STRINGARRAY(p_load_id));
1117
1118 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1119 p_msg => l_msg,
1120 p_category => NULL);
1121
1122 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_DEBUG', -- Debug Enabled :
1123 p_tokens => STRINGARRAY('DEBUG'),
1124 p_values => STRINGARRAY(p_user_debug));
1125
1126 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1127 p_msg => l_msg,
1128 p_category => NULL);
1129
1130 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1131 p_msg => ' ',
1132 p_category => NULL);
1133
1134 IF (UPPER(l_source) = 'SERVER') THEN
1135 READ_BLOCK_FROM_DIR(p_file_name => p_src_filename,
1136 p_load_id => p_load_id,
1137 x_status => x_status,
1138 x_error_msg => x_error_msg);
1139 ELSE
1140 READ_BLOCK_FROM_TABLE(p_file_name => p_src_filename,
1141 p_load_id => p_load_id,
1142 x_status => x_status,
1143 x_error_msg => x_error_msg);
1144 END IF;
1145
1146 IF (x_status <> -1) THEN
1147 ROLLBACK;
1148 RETCODE := 2;
1149 ERRBUF := x_error_msg;
1150
1151 PRINT_END_OF_REPORT;
1152
1153 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1154 RETURN;
1155 END IF;
1156
1157 IF (l_type = 'LANE') THEN
1158 FTE_LANE_LOADER.SUBMIT_LANE(x_status => x_status,
1159 x_error_msg => x_error_msg);
1160 ELSIF (l_type = 'PRICELIST' OR l_type = 'MODLIST' OR l_type = 'LTL_MODLIST') THEN
1161 FTE_RATE_CHART_LOADER.SUBMIT_QP_PROCESS(x_status => x_status,
1162 x_error_msg => x_error_msg);
1163 ELSIF (l_type = 'PRICE_ZONE_CHART') THEN
1164 FTE_PARCEL_LOADER.PROCESS_ZONES_AND_LANES(x_status => x_status,
1165 x_error_msg => x_error_msg);
1166 END IF;
1167
1168 IF (x_status <> -1) THEN
1169 ROLLBACK;
1170
1171 PRINT_END_OF_REPORT;
1172
1173 RETCODE := 2;
1174 ERRBUF := x_error_msg;
1175 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1176 RETURN;
1177 END IF;
1178
1179 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1180 p_msg => ' ',
1181 p_category => NULL);
1182
1183 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_COMMIT'); -- Committing the above entities creation. --
1184
1185 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1186 p_msg => l_msg,
1187 p_category => NULL);
1188
1189 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1190 p_msg => ' ',
1191 p_category => NULL);
1192
1193 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1194 p_msg => '+---------------------------------------------------------------------------+',
1195 p_category => NULL);
1196
1197 l_msg := FTE_UTIL_PKG.GET_MSG(p_name => 'FTE_BULKLOAD_END_REPORT'); -- *** End of BulkLoader Report ***
1198
1199 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1200 p_msg => l_msg,
1201 p_category => NULL);
1202
1203 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1204 p_msg => '+---------------------------------------------------------------------------+',
1205 p_category => NULL);
1206
1207 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1208
1209 EXCEPTION
1210 WHEN OTHERS THEN
1211 ROLLBACK;
1212 RETCODE := 2;
1213 ERRBUF := sqlerrm;
1214 x_status := 2;
1215 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1216 p_msg => sqlerrm,
1217 p_category => 'O');
1218 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1219 END PROCESS_DATA;
1220
1221
1222 --------------------------------------------------------------------------------
1223 -- Procedure: UPDATE_RID
1224 --
1225 -- Purpose: Update the row specified by the parameters with the request ID of the.
1226 -- concurrent request. The row should already exist in the database.
1227 --
1228 -- IN parameters:
1229 -- 1. p_FileName: file name
1230 -- 2. p_LoadId: loading id
1231 -- 3. p_FileType: type of file
1232 -- 4. p_LoadType: loading type
1233 -- 5. p_RequestId: the Id that need to be updated.
1234 --------------------------------------------------------------------------------
1235 PROCEDURE UPDATE_RID( p_FileName IN VARCHAR2,
1236 p_LoadId IN NUMBER,
1237 p_FileType IN VARCHAR2,
1238 p_LoadType IN VARCHAR2,
1239 p_RequestId IN NUMBER ) IS
1240
1241 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.UPDATE_RID';
1242
1243 BEGIN
1244 --SETUP DEBUGGING
1245 FTE_UTIL_PKG.ENTER_Debug(l_module_name);
1246
1247 IF (g_debug_on) THEN
1248 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Updating FTE_BUILLOAD_DATA', WSH_DEBUG_SV.c_proc_level);
1249 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'File Name', p_filename);
1250 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Load ID', p_loadid);
1251 END IF;
1252
1253 IF (p_LoadType <> 'DTT_DWNLD') THEN
1254 UPDATE FTE_BULKLOAD_DATA
1255 SET request_id = p_RequestId,
1256 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1257 LAST_UPDATE_DATE = sysdate,
1258 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
1259 WHERE load_id = p_LoadId AND
1260 load_type = p_LoadType AND
1261 file_type = p_FileType AND
1262 file_name = p_FileName;
1263
1264 ELSIF (p_LoadType = 'DTT_DWNLD') THEN
1265 UPDATE FTE_BULKLOAD_DATA
1266 SET request_id = p_RequestId,
1267 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1268 LAST_UPDATE_DATE = sysdate,
1269 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
1270 WHERE load_id = p_LoadId AND
1271 load_type = p_LoadType;
1272 END IF;
1273
1274 commit;
1275 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1276 EXCEPTION
1277 WHEN OTHERS THEN
1278 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1279 p_msg => sqlerrm,
1280 p_category => 'O');
1281
1282 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1283 END UPDATE_RID;
1284
1285 -------------------------------------------------------------------------------------------
1286 -- Procedure: UPLOAD_FILE
1287 --
1288 -- Purpose: Retrieve information on a load from the database with load id as the parameter
1289 --
1290 -- IN parameters:
1291 -- 1. p_LoadId: loading id
1292 --
1293 -- OUT parameters:
1294 -- 1. p_LoadType: type of load
1295 -- 2. p_FileContents: file
1296 -- 3. p_FileName: name of file
1297 -- 4. p_DirName: name of the file directory
1298 -- 5. p_ExitStatus: status
1299 --------------------------------------------------------------------------------------------
1300 PROCEDURE UPLOAD_FILE (p_LoadId IN NUMBER,
1301 p_LoadType OUT NOCOPY VARCHAR2,
1302 p_FileContents OUT NOCOPY BLOB,
1303 p_FileName OUT NOCOPY VARCHAR2,
1304 p_DirName OUT NOCOPY VARCHAR2,
1305 p_ExitStatus OUT NOCOPY NUMBER) IS
1306
1307 v_FileLocator BFILE;
1308 v_FileSize NUMBER;
1309 v_sql_stmt VARCHAR2(200);
1310 v_LastUpdateDate DATE;
1311 l_source_type VARCHAR2(40);
1312
1313 l_debug_on BOOLEAN;
1314 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.UPLOAD_FILE';
1315
1316 BEGIN
1317
1318 FTE_UTIL_PKG.ENTER_Debug(l_module_name);
1319
1320 IF (g_debug_on) THEN
1321 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Load ID', p_LoadId);
1322 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'File Name', p_FileName);
1323 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Load type', p_LoadType);
1324 FTE_UTIL_PKG.WRITE_LOGFILE(l_module_name, 'Directory name', p_DirName);
1325 END IF;
1326
1327 BEGIN
1328 p_DirName := get_upload_dir;
1329 v_sql_stmt := 'CREATE OR REPLACE DIRECTORY UPLOAD_DIR AS ''' || p_DirName || '''';
1330 EXECUTE IMMEDIATE v_sql_stmt;
1331
1332 SELECT file_name, load_type
1333 INTO p_FileName, p_LoadType
1334 FROM fte_bulkload_data
1335 WHERE load_id = p_LoadId;
1336
1337 --For LTL pricelists, we don't upload the file into the database, but rather
1338 --we read the contents of the file directly using UTL_FILE.
1339 IF (p_loadType <> 'LTL_PRICELIST') THEN
1340 fnd_profile.get('FTE_BULKLOAD_SOURCE_TYPE',l_source_type);
1341
1342 IF (upper(l_source_type) = 'SERVER') THEN
1343 -- initialize the BFILE locator for reading
1344 v_FileLocator := BFILENAME('UPLOAD_DIR', p_FileName);
1345 DBMS_LOB.FILEOPEN(v_FileLocator, DBMS_LOB.FILE_READONLY);
1346 v_FileSize := DBMS_LOB.GETLENGTH(v_FileLocator);
1347
1348 -- select the column into which we are going to load
1349 -- the file. There should be only one column returned by this
1350 -- query. The Load ID should be unique.
1351 SELECT content
1352 INTO p_FileContents
1353 FROM fte_bulkload_data
1354 WHERE load_id = p_LoadId
1355 FOR UPDATE;
1356
1357 -- load the entire file into the charactor LOB.
1358 DBMS_LOB.LOADFROMFILE(p_FileContents, v_FileLocator, v_FileSize);
1359 DBMS_LOB.FILECLOSE(v_FileLocator);
1360
1361 v_LastUpdateDate := sysdate;
1362
1363 UPDATE FTE_BULKLOAD_DATA
1364 SET last_update_date = v_LastUpdateDate,
1365 file_size = v_FileSize,
1366 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1367 LAST_UPDATE_LOGIN = FND_GLOBAL.USER_ID
1368 WHERE load_id = p_LoadId;
1369 ELSE
1370 SELECT content
1371 INTO p_FileContents
1372 FROM fte_bulkload_data
1373 WHERE load_id = p_LoadId;
1374 END IF;
1375 END IF;
1376 p_ExitStatus := 0;
1377 EXCEPTION
1378 WHEN NO_DATA_FOUND THEN
1379 p_ExitStatus := 1;
1380 WHEN OTHERS THEN
1381 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1382 p_msg => sqlerrm,
1383 p_category => 'O');
1384 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1385 END;
1386
1387 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1388
1389 EXCEPTION
1390 WHEN OTHERS THEN
1391 FTE_UTIL_PKG.WRITE_OUTFILE(p_module_name => l_module_name,
1392 p_msg => sqlerrm,
1393 p_category => 'O');
1394 FTE_UTIL_PKG.Exit_Debug(l_module_name);
1395 END UPLOAD_FILE;
1396
1397 END FTE_BULKLOAD_PKG;