37: l_legislation_code per_business_groups.legislation_code%TYPE;
38: l_bg_name per_business_groups.name%TYPE;
39:
40: -- File Handling variables
41: l_file_type UTL_FILE.file_type;
42: l_filename VARCHAR2 (240);
43: l_location VARCHAR2 (4000);
44: l_line_read VARCHAR2 (4000);
45: -- Batch Variables
191: RAISE e_fatal_error;
192: END IF;
193: -- Open flat file
194: -- Bug fixes 4216001 and 4216029
195: l_file_type := UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
196: l_end_rec_flag := 'N';
197: <
198: LOOP
199: BEGIN
196: l_end_rec_flag := 'N';
197: <
198: LOOP
199: BEGIN
200: UTL_FILE.get_line (l_file_type, l_line_read);
201: l_batch_seq := l_batch_seq + 1;
202: EXCEPTION
203: WHEN VALUE_ERROR THEN
204: IF UTL_FILE.is_open (l_file_type) THEN
200: UTL_FILE.get_line (l_file_type, l_line_read);
201: l_batch_seq := l_batch_seq + 1;
202: EXCEPTION
203: WHEN VALUE_ERROR THEN
204: IF UTL_FILE.is_open (l_file_type) THEN
205: UTL_FILE.fclose (l_file_type);
206: END IF;
207: hr_utility.set_location (l_proc, 300);
208: retcode := c_error;
201: l_batch_seq := l_batch_seq + 1;
202: EXCEPTION
203: WHEN VALUE_ERROR THEN
204: IF UTL_FILE.is_open (l_file_type) THEN
205: UTL_FILE.fclose (l_file_type);
206: END IF;
207: hr_utility.set_location (l_proc, 300);
208: retcode := c_error;
209: -- The error will mean batch_seq doesn't get upped so add 1 when
256: if to_number(substr(l_line_read,112,5)) <> l_batch_seq then
257: RAISE e_file_format_err;
258: end if;
259: begin
260: UTL_FILE.get_line (l_file_type, l_line_read);
261: exception
262: when NO_DATA_FOUND then
263: NULL;
264:
278:
279: --COMMIT;
280:
281:
282: UTL_FILE.fclose (l_file_type);
283:
284: if l_end_rec_flag = 'N' then
285: fnd_file.put_line (fnd_file.LOG,'end record error');
286: raise e_file_format_err;
314: END IF;
315: -- Open flat file
316:
317: l_file_type :=
318: UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
319: -- Create the Batch header
320:
321: l_batch_id := hr_pump_utils.create_batch_header
322: (p_batch_name => p_batch_name,
331: l_batch_seq := 0;
332: <
333: LOOP
334: BEGIN
335: UTL_FILE.get_line (l_file_type, l_line_read);
336: l_batch_seq := l_batch_seq + 1;
337: EXCEPTION
338: WHEN VALUE_ERROR THEN
339: IF UTL_FILE.is_open (l_file_type) THEN
335: UTL_FILE.get_line (l_file_type, l_line_read);
336: l_batch_seq := l_batch_seq + 1;
337: EXCEPTION
338: WHEN VALUE_ERROR THEN
339: IF UTL_FILE.is_open (l_file_type) THEN
340: UTL_FILE.fclose (l_file_type);
341: END IF;
342: hr_utility.set_location (l_proc, 300);
343: retcode := c_error;
336: l_batch_seq := l_batch_seq + 1;
337: EXCEPTION
338: WHEN VALUE_ERROR THEN
339: IF UTL_FILE.is_open (l_file_type) THEN
340: UTL_FILE.fclose (l_file_type);
341: END IF;
342: hr_utility.set_location (l_proc, 300);
343: retcode := c_error;
344: -- The error will mean batch_seq doesn't get upped so add 1 when
835: END LOOP read_lines_in_file;
836: -- Commit the outstanding records
837:
838: COMMIT;
839: UTL_FILE.fclose (l_file_type);
840: hr_utility.set_location ( 'Leaving:'|| l_proc, 200);
841: -- Most of these exceptions are not translated as they should not happen normally
842: -- If they do happen, something is seriously wrong and SysAdmin interference will be necessary.
843:
845: WHEN e_fatal_error
846: -- No directory specified
847: THEN
848: -- Close the file in case of error
849: IF UTL_FILE.is_open (l_file_type)
850: THEN
851: UTL_FILE.fclose (l_file_type);
852: END IF;
853: hr_utility.set_location (l_proc, 210);
847: THEN
848: -- Close the file in case of error
849: IF UTL_FILE.is_open (l_file_type)
850: THEN
851: UTL_FILE.fclose (l_file_type);
852: END IF;
853: hr_utility.set_location (l_proc, 210);
854: -- Set retcode to 2, indicating an ERROR to the ConcMgr
855: retcode := c_error;
857: hr_utility.set_message (801, 'PAY_377020_DK_DATA_EXC_DIR_MIS');
858: -- Return the message to the ConcMgr (This msg will appear in the log file)
859: errbuf := hr_utility.get_message;
860:
861: WHEN UTL_FILE.invalid_operation
862: -- File could not be opened as requested, perhaps because of operating system permissions
863: -- Also raised when attempting a write operation on a file opened for read, or a read operation
864: -- on a file opened for write.
865: THEN
862: -- File could not be opened as requested, perhaps because of operating system permissions
863: -- Also raised when attempting a write operation on a file opened for read, or a read operation
864: -- on a file opened for write.
865: THEN
866: IF UTL_FILE.is_open (l_file_type)
867: THEN
868: UTL_FILE.fclose (l_file_type);
869: END IF;
870: hr_utility.set_location (l_proc, 220);
864: -- on a file opened for write.
865: THEN
866: IF UTL_FILE.is_open (l_file_type)
867: THEN
868: UTL_FILE.fclose (l_file_type);
869: END IF;
870: hr_utility.set_location (l_proc, 220);
871: retcode := c_error;
872: errbuf := 'Reading File ('||l_location ||' -> '
872: errbuf := 'Reading File ('||l_location ||' -> '
873: || l_filename
874: || ') - Invalid Operation.';
875:
876: WHEN UTL_FILE.internal_error
877: -- Unspecified internal error
878: THEN
879: IF UTL_FILE.is_open (l_file_type)
880: THEN
875:
876: WHEN UTL_FILE.internal_error
877: -- Unspecified internal error
878: THEN
879: IF UTL_FILE.is_open (l_file_type)
880: THEN
881: UTL_FILE.fclose (l_file_type);
882: END IF;
883: hr_utility.set_location (l_proc, 230);
877: -- Unspecified internal error
878: THEN
879: IF UTL_FILE.is_open (l_file_type)
880: THEN
881: UTL_FILE.fclose (l_file_type);
882: END IF;
883: hr_utility.set_location (l_proc, 230);
884: retcode := c_error;
885: errbuf := 'Reading File ('
887: || ' -> '
888: || l_filename
889: || ') - Internal Error.';
890:
891: WHEN UTL_FILE.invalid_mode
892: -- Invalid string specified for file mode
893: THEN
894: IF UTL_FILE.is_open (l_file_type)
895: THEN
890:
891: WHEN UTL_FILE.invalid_mode
892: -- Invalid string specified for file mode
893: THEN
894: IF UTL_FILE.is_open (l_file_type)
895: THEN
896: UTL_FILE.fclose (l_file_type);
897: END IF;
898: hr_utility.set_location (l_proc, 240);
892: -- Invalid string specified for file mode
893: THEN
894: IF UTL_FILE.is_open (l_file_type)
895: THEN
896: UTL_FILE.fclose (l_file_type);
897: END IF;
898: hr_utility.set_location (l_proc, 240);
899: retcode := c_error;
900: errbuf := 'Reading File ('
902: || ' -> '
903: || l_filename
904: || ') - Invalid Mode.';
905:
906: WHEN UTL_FILE.invalid_path
907: -- Directory or filename is invalid or not accessible
908: THEN
909: IF UTL_FILE.is_open (l_file_type)
910: THEN
905:
906: WHEN UTL_FILE.invalid_path
907: -- Directory or filename is invalid or not accessible
908: THEN
909: IF UTL_FILE.is_open (l_file_type)
910: THEN
911: UTL_FILE.fclose (l_file_type);
912: END IF;
913: retcode := c_error;
907: -- Directory or filename is invalid or not accessible
908: THEN
909: IF UTL_FILE.is_open (l_file_type)
910: THEN
911: UTL_FILE.fclose (l_file_type);
912: END IF;
913: retcode := c_error;
914: errbuf := 'Reading File ('
915: || l_location
917: || l_filename
918: || ') - Invalid Path or Filename.';
919: hr_utility.set_location (l_proc, 250);
920:
921: WHEN UTL_FILE.invalid_filehandle
922: -- File type does not specify an open file
923: THEN
924: IF UTL_FILE.is_open (l_file_type)
925: THEN
920:
921: WHEN UTL_FILE.invalid_filehandle
922: -- File type does not specify an open file
923: THEN
924: IF UTL_FILE.is_open (l_file_type)
925: THEN
926: UTL_FILE.fclose (l_file_type);
927: END IF;
928: hr_utility.set_location (l_proc, 260);
922: -- File type does not specify an open file
923: THEN
924: IF UTL_FILE.is_open (l_file_type)
925: THEN
926: UTL_FILE.fclose (l_file_type);
927: END IF;
928: hr_utility.set_location (l_proc, 260);
929: retcode := c_error;
930: errbuf := 'Reading File ('
932: || ' -> '
933: || l_filename
934: || ') - Invalid File Type.';
935:
936: WHEN UTL_FILE.read_error
937: -- Operating system error occurred during a read operation
938: THEN
939: IF UTL_FILE.is_open (l_file_type)
940: THEN
935:
936: WHEN UTL_FILE.read_error
937: -- Operating system error occurred during a read operation
938: THEN
939: IF UTL_FILE.is_open (l_file_type)
940: THEN
941: UTL_FILE.fclose (l_file_type);
942: END IF;
943: hr_utility.set_location (l_proc, 270);
937: -- Operating system error occurred during a read operation
938: THEN
939: IF UTL_FILE.is_open (l_file_type)
940: THEN
941: UTL_FILE.fclose (l_file_type);
942: END IF;
943: hr_utility.set_location (l_proc, 270);
944: retcode := c_error;
945: errbuf := 'Reading File ('