[Home] [Help]
PACKAGE BODY: APPS.PER_FI_POSTALCODE_UPLOAD
Source
1 PACKAGE BODY PER_FI_POSTALCODE_UPLOAD AS
2 /* $Header: pefipost.pkb 120.0 2005/05/31 08:44:38 appldev noship $ */
3
4
5 -- Global package name
6 g_package CONSTANT VARCHAR2 (33) := 'POST_FI_UPLOAD';
7 g_proc CONSTANT VARCHAR2 (33) :='error';
8 g_bg_id per_business_groups.business_group_id%TYPE;
9 g_view_application_id FND_LOOKUP_TYPES.view_application_id%type;
10 g_security_group_id PER_BUSINESS_GROUPS.business_group_id%type;
11 --l_original_desc fnd_lookup_values.description%type;
12
13 -- File Handling variables
14 l_file_type UTL_FILE.file_type;
15 l_location VARCHAR2 (4000);
16 l_line_read VARCHAR2 (4000) ;
17 l_batch_seq NUMBER :=0;
18
19 -- Variables to Read from File
20 g_meaning VARCHAR2(80);
21
22 -- Exceptions
23 e_fatal_error EXCEPTION;
24 e_invalid_record EXCEPTION;
25 e_empty_line EXCEPTION;
26
27 -- Global constants
28 c_warning CONSTANT NUMBER := 1;
29 c_error CONSTANT NUMBER := 2;
30
31
32 --Variable for localization_lookup_type
33
34 g_lookup_type CONSTANT VARCHAR2(50) :='FI_POSTAL_CODE';
35
36 -- Constants
37 c_read_file CONSTANT VARCHAR2 (1) := 'r';
38 c_max_linesize CONSTANT NUMBER := 400;
39 c_commit_point CONSTANT NUMBER := 10;
40 c_data_exchange_dir CONSTANT VARCHAR2 (30) := 'PER_DATA_EXCHANGE_DIR';
41
42
43
44 /*
45 PROCEDURE NAME : READ_FILE
46 PARAMATERS : p_filename -- Name of the file to be read.
47
48 PURPOSE : To Open the file Specified from the particular Dir
49 Pass it to READ_RECORD Procedure
50
51 ERRORS HANDLED : Raise ERROR if No directory specified
52 Raise Error for all invalid file level operations
53 Like
54 invalid operation
55 internal error
56 invalid mode
57 invalid path
58 invalid filehandle
59 read error
60 */
61
62 PROCEDURE READ_FILE(
63 errbuf OUT NOCOPY VARCHAR2
64 ,retcode OUT NOCOPY NUMBER
65 ,p_filename IN VARCHAR2
66 ,p_business_group_id IN per_business_groups.business_group_id%TYPE
67 )
68 is
69 l_filename VARCHAR2 (240);
70
71 BEGIN
72 g_bg_id := p_business_group_id;
73 /* Get the filename from parameter to local variable */
74 l_filename := p_filename;
75 if substr(l_filename,length(l_filename)-3) <>'.txt'
76 then RAISE UTL_FILE.invalid_filehandle;
77 END IF;
78
79 /* Get the DIR set in the profile to local variable */
80 fnd_profile.get (c_data_exchange_dir, l_location);
81
82 IF l_location IS NULL
83 THEN
84 -- error : I/O directory not defined
85 RAISE e_fatal_error;
86 END IF;
87 hr_utility.set_location(l_location,4);
88 -- Open flat file
89 l_file_type := UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
90
91 LOOP
92 BEGIN
93
94 UTL_FILE.get_line (l_file_type, l_line_read);
95 hr_utility.set_location ( ' line ' || l_line_read, 10);
96 l_batch_seq := l_batch_seq + 1;
97 READ_RECORD(l_line_read);
98
99 IF MOD (l_batch_seq, c_commit_point) = 0
100 THEN
101 COMMIT;
102 END IF;
103 EXCEPTION
104 WHEN NO_DATA_FOUND
105 THEN
106 EXIT;
107
108 WHEN e_invalid_record THEN
109 -- Set retcode to 1, indicating a WARNING to the ConcMgr
110 retcode := c_warning;
111
112 -- Set the application error
113
114 hr_utility.set_message (800, 'HR_376627_FI_INVALID_RECORD');
115 hr_utility.set_message_token (800, 'LINE_NO', l_batch_seq);
116 hr_utility.set_message_token (800, 'LINE', l_line_read);
117
118 -- Write the message to log file, do not raise an application error but continue
119 -- (with next line)
120
121 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
122
123
124 WHEN e_empty_line THEN
125 -- Set retcode to 1, indicating a WARNING to the ConcMgr
126 retcode := c_warning;
127
128 -- Set the application error
129
130 hr_utility.set_message (800, 'HR_376626_FI_EMPTY_LINE');
131 hr_utility.set_message_token (800, 'LINE_NO', l_batch_seq);
132
133
134 -- Write the message to log file, do not raise an application error but continue
135 -- (with next line)
136
137 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
138
139
140 END;
141
142 END LOOP ;
143 -- Commit the outstanding records
144 COMMIT;
145
146 EXCEPTION
147 WHEN e_fatal_error
148 -- No directory specified
149 THEN
150 -- Close the file in case of error
151 IF UTL_FILE.is_open (l_file_type)
152 THEN
153 UTL_FILE.fclose (l_file_type);
154 END IF;
155
156 hr_utility.set_location (g_proc, 270);
157
158 -- Set retcode to 2, indicating an ERROR to the ConcMgr
159 retcode := c_error;
160
161 -- Set the application error
162 hr_utility.set_message (800, 'HR_78040_DATA_EXCHANGE_DIR_MIS');
163
164 -- Return the message to the ConcMgr (This msg will appear in the log file)
165 errbuf := hr_utility.get_message;
166
167 WHEN UTL_FILE.invalid_operation
168 -- File could not be opened as requested, because of operating system permissions
169 -- Also raised when attempting a write operation on a file opened for read.
170
171 THEN
172 IF UTL_FILE.is_open (l_file_type)
173 THEN
174 UTL_FILE.fclose (l_file_type);
175 END IF;
176
177 hr_utility.set_location (g_proc, 280);
178 retcode := c_error;
179 errbuf := 'Reading File ('||l_location ||' -> ' || l_filename || ') - Invalid Operation.';
180 WHEN UTL_FILE.internal_error
181 -- Unspecified internal error
182 THEN
183 IF UTL_FILE.is_open (l_file_type)
184 THEN
185 UTL_FILE.fclose (l_file_type);
186 END IF;
187
188 hr_utility.set_location (g_proc, 290);
189 retcode := c_error;
190 errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Internal Error.';
191
192 WHEN UTL_FILE.invalid_mode
193 -- Invalid string specified for file mode
194 THEN
195 IF UTL_FILE.is_open (l_file_type)
196 THEN
197 UTL_FILE.fclose (l_file_type);
198 END IF;
199
200 hr_utility.set_location (g_proc, 300);
201 retcode := c_error;
202 errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Invalid Mode.';
203
204 WHEN UTL_FILE.invalid_path
205 -- Directory or filename is invalid or not accessible
206 THEN
207 IF UTL_FILE.is_open (l_file_type)
208 THEN
209 UTL_FILE.fclose (l_file_type);
210 END IF;
211
212 retcode := c_error;
213 errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Invalid Path or Filename.';
214 hr_utility.set_location (g_proc, 310);
215
216 WHEN UTL_FILE.invalid_filehandle
217 -- File type does not specify an open file
218 THEN
219 IF UTL_FILE.is_open (l_file_type)
220 THEN
221 UTL_FILE.fclose (l_file_type);
222 END IF;
223
224 hr_utility.set_location (g_proc, 320);
225 retcode := c_error;
226 errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Invalid File Type.';
227 WHEN UTL_FILE.read_error
228
229 -- Operating system error occurred during a read operation
230 THEN
231 IF UTL_FILE.is_open (l_file_type)
232 THEN
233 UTL_FILE.fclose (l_file_type);
234 END IF;
235
236 hr_utility.set_location (g_proc, 330);
237 retcode := c_error;
238 errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Read Error.';
239
240
241 end READ_FILE;
242
243
244 /*
245 PROCEDURE NAME : READ_RECORD
246 PARAMATERS : p_line -- a line read from file.
247
248 PURPOSE : To Update or Create the Lookup_code
249 ERRORS HANDLED : Raise warning for EMPTY LINE
250 Raise an Error for the Empty Lookup Code
251 */
252
253 PROCEDURE READ_RECORD
254 ( p_line IN varchar2
255 )
256 is
257 l_update_flag varchar2(50);
258 l_original_meaning fnd_lookup_values.meaning%type;
259 l_Description fnd_lookup_values.description%type;
260 l_code fnd_lookup_values.lookup_code%type;
261
262
263 BEGIN
264 l_update_flag:='N';
265 g_view_application_id := 3;
266 begin
267 select SECURITY_GROUP_ID into g_security_group_id from per_business_groups where business_group_id = g_bg_id;
268 exception
269 when others then
270 g_security_group_id := 0;
271 end;
272 IF p_line is NULL
273 THEN
274 /* If the line is empty Raise an Warning saying the line is empty */
275 RAISE e_empty_line;
276 ELSE
277 /* get the Lookup_code 12 from the line */
278 l_code:= substr(p_line,14,5);
279 if l_code is null or is_number(l_code)=false
280 or is_number(substr(p_line,14,6))=true
281 then
282 RAISE e_invalid_record;
283 else
284
285 g_meaning:=rtrim(substr(p_line,19,30))||' '||'/'||' '||rtrim(substr(p_line,49,30));
286
287 /* if Trace switched ON this wud give us the value read */
288 hr_utility.set_location ( ' code '||l_code , 20);
289 hr_utility.set_location ( ' desc '||l_description , 30);
290 end if;
291
292 END IF;
293
294 BEGIN
295 /* Get 'Y' if the lookupcode already available */
296 /* get the description also to checkup whether the Description has been changed */
297 /* if so update the description only */
298
299 SELECT 'Y',flv.meaning
300 INTO l_update_flag,l_original_meaning
301 FROM fnd_lookup_values flv
302 WHERE flv.lookup_type=g_lookup_type
303 AND flv.lookup_code=l_code and
304 security_group_id = g_security_group_id and
305 view_application_id = g_view_application_id;
306 EXCEPTION
307 WHEN NO_DATA_FOUND
308 THEN
309 null;
310 END;
311 --end;
312 IF l_update_flag='Y'
313 THEN
314 IF l_original_meaning <>l_code||' '||g_meaning
315 THEN
316 UPDATE_ROW(l_code,g_meaning,l_Description);
317
318 END IF;
319 ELSE
320 INSERT_ROW(l_code,g_meaning,l_Description);
321
322 END IF;
323
324 end READ_RECORD;
325
326 PROCEDURE INSERT_ROW(
327 p_lookup_code in fnd_lookup_values.lookup_code%TYPE
328 ,p_meaning in fnd_lookup_values.meaning%TYPE
329 ,p_description IN fnd_lookup_values.description%type
330 )
331 is
332
333 --G_LOOKUP_TYPE CONSTANT VARCHAR2(50) :='SE_POSTAL_CODE';
334
335 l_rowid VARCHAR2(30);
336
337 begin
338
339
340 FND_LOOKUP_VALUES_PKG.INSERT_ROW(
341 X_ROWID => l_rowid,
342 X_LOOKUP_TYPE => g_lookup_type,
343 X_SECURITY_GROUP_ID => g_security_group_id,
344 X_VIEW_APPLICATION_ID => 3,
345 X_LOOKUP_CODE => p_lookup_code,
346 X_TAG => NULL,
347 X_ATTRIBUTE_CATEGORY => null,
348 X_ATTRIBUTE1 => null,
349 X_ATTRIBUTE2 => null,
350 X_ATTRIBUTE3 => null,
351 X_ATTRIBUTE4 => null,
352 X_ENABLED_FLAG => 'Y',
353 X_START_DATE_ACTIVE => SYSDATE,
354 X_END_DATE_ACTIVE => null,
355 X_TERRITORY_CODE => 'FI',
356 X_ATTRIBUTE5 => null,
357 X_ATTRIBUTE6 => null,
358 X_ATTRIBUTE7 => null,
359 X_ATTRIBUTE8 => null,
360 X_ATTRIBUTE9 => null,
361 X_ATTRIBUTE10 => null,
362 X_ATTRIBUTE11 => null,
363 X_ATTRIBUTE12 => null,
364 X_ATTRIBUTE13 => null,
365 X_ATTRIBUTE14 => null,
366 X_ATTRIBUTE15 => null,
367 X_MEANING => p_lookup_code||' '||p_meaning,
368 X_DESCRIPTION => p_description,
369 X_CREATION_DATE => SYSDATE,
370 X_CREATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
371 X_LAST_UPDATE_DATE => SYSDATE,
372 X_LAST_UPDATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
373 X_LAST_UPDATE_LOGIN => TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
374
375 );
376
377 end INSERT_ROW;
378
379
380
381 PROCEDURE UPDATE_ROW(p_lookup_code in fnd_lookup_values.lookup_code%TYPE,
382 p_meaning in fnd_lookup_values.meaning%TYPE,
383 p_description IN fnd_lookup_values.description%type)
384 is
385 --G_LOOKUP_TYPE CONSTANT VARCHAR2(50) :='FI_POSTAL_CODE';
386
387 begin
388
389 FND_LOOKUP_VALUES_PKG.UPDATE_ROW(
390 X_LOOKUP_TYPE => g_lookup_type,
391 X_SECURITY_GROUP_ID => g_security_group_id,
392 X_VIEW_APPLICATION_ID => 3,
393 X_LOOKUP_CODE => p_lookup_code,
394 X_TAG => NULL,
395 X_ATTRIBUTE_CATEGORY => NULL,
396 X_ATTRIBUTE1 => NULL,
397 X_ATTRIBUTE2 => NULL,
398 X_ATTRIBUTE3 => NULL,
399 X_ATTRIBUTE4 => NULL,
400 X_ENABLED_FLAG => 'Y',
401 X_START_DATE_ACTIVE => SYSDATE,
402 X_END_DATE_ACTIVE => NULL,
403 X_TERRITORY_CODE => 'FI',
404 X_ATTRIBUTE5 => NULL,
405 X_ATTRIBUTE6 => NULL,
406 X_ATTRIBUTE7 => NULL,
407 X_ATTRIBUTE8 => NULL,
408 X_ATTRIBUTE9 => NULL,
409 X_ATTRIBUTE10 => NULL,
410 X_ATTRIBUTE11 => NULL,
411 X_ATTRIBUTE12 => NULL,
412 X_ATTRIBUTE13 => NULL,
413 X_ATTRIBUTE14 => NULL,
414 X_ATTRIBUTE15 => NULL,
415 X_MEANING => p_lookup_code||' '||p_meaning,
416 X_DESCRIPTION => null,
417 X_LAST_UPDATE_DATE => SYSDATE,
418 X_LAST_UPDATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
419 X_LAST_UPDATE_LOGIN => TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')));
420
421 end UPDATE_ROW;
422
423 /*
424 Function is_number
425 This function checks whether the postal_code read from the file is
426 numeric or not
427 If the postal code is not numeric, the record is skipped and the a
428 warning is thrown by the concurrent manager
429 */
430
431
432 FUNCTION is_number ( p_value IN varchar2 ) return boolean is
433 l_number number(20);
434 begin
435 l_number := to_number(p_value);
436 return true;
437 exception
438 when others then
439 return false;
440 end is_number;
441
442
443 end PER_FI_POSTALCODE_UPLOAD ;