[Home] [Help]
PACKAGE BODY: APPS.PER_SE_POSTALCODE_UPLOAD
Source
1 PACKAGE BODY PER_SE_POSTALCODE_UPLOAD AS
2 /* $Header: pesepost.pkb 120.1 2008/12/30 10:55:31 rsengupt ship $ */
3
4
5 -- Global package name
6 g_package CONSTANT VARCHAR2 (33) := 'POST_SE_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) :='SE_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_377221_SE_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_377222_SE_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,95,5); -- Changes for Postal Code Format Chance 2008/2009
282 or is_number(substr(p_line,1,6))=true
279 l_code:= substr(p_line,1,5);
280 if l_code is null or is_number(l_code)=false
281 --or is_number(substr(p_line,95,6))=true -- Changes for Postal Code Format Chance 2008/2009
283 then
284 RAISE e_invalid_record;
285 else
286
287 --g_meaning:=rtrim(substr(p_line,100,20)); -- Changes for Postal Code Format Chance 2008/2009
288 g_meaning:=rtrim(substr(p_line,6,15));
289
290 --fnd_file.put_line(fnd_file.log,'g_meaning:'||g_meaning);
291
292 /* if Trace switched ON this wud give us the value read */
293 hr_utility.set_location ( ' code '||l_code , 20);
294 hr_utility.set_location ( ' desc '||l_description , 30);
295 end if;
296
297 END IF;
298
299 BEGIN
300 /* Get 'Y' if the lookupcode already available */
301 /* get the description also to checkup whether the Description has been changed */
302 /* if so update the description only */
303
304 SELECT 'Y',flv.meaning
305 INTO l_update_flag,l_original_meaning
306 FROM fnd_lookup_values flv
307 WHERE flv.lookup_type=g_lookup_type
308 AND flv.lookup_code=l_code and
309 security_group_id = g_security_group_id and
310 language=userenv('LANG') and
311 view_application_id = g_view_application_id;
312 EXCEPTION
313 WHEN NO_DATA_FOUND
314 THEN
315 null;
316 END;
317 --end;
318 IF l_update_flag='Y'
319 THEN
320 IF l_original_meaning <>l_code||' '||g_meaning
321 THEN
322 UPDATE_ROW(l_code,g_meaning,l_Description);
323
324 END IF;
325 ELSE
326 INSERT_ROW(l_code,g_meaning,l_Description);
327
328 END IF;
329
330 end READ_RECORD;
331
332 PROCEDURE INSERT_ROW(
333 p_lookup_code in fnd_lookup_values.lookup_code%TYPE
334 ,p_meaning in fnd_lookup_values.meaning%TYPE
335 ,p_description IN fnd_lookup_values.description%type
336 )
337 is
338
339 --G_LOOKUP_TYPE CONSTANT VARCHAR2(50) :='SE_POSTAL_CODE';
340
341 l_rowid VARCHAR2(30);
342
343 begin
344
345
346 FND_LOOKUP_VALUES_PKG.INSERT_ROW(
347 X_ROWID => l_rowid,
348 X_LOOKUP_TYPE => g_lookup_type,
349 X_SECURITY_GROUP_ID => g_security_group_id,
350 X_VIEW_APPLICATION_ID => 3,
351 X_LOOKUP_CODE => p_lookup_code,
352 X_TAG => NULL,
353 X_ATTRIBUTE_CATEGORY => null,
354 X_ATTRIBUTE1 => null,
355 X_ATTRIBUTE2 => null,
356 X_ATTRIBUTE3 => null,
357 X_ATTRIBUTE4 => null,
358 X_ENABLED_FLAG => 'Y',
359 X_START_DATE_ACTIVE => null,
360 X_END_DATE_ACTIVE => null,
361 X_TERRITORY_CODE => 'SE',
362 X_ATTRIBUTE5 => null,
363 X_ATTRIBUTE6 => null,
364 X_ATTRIBUTE7 => null,
365 X_ATTRIBUTE8 => null,
366 X_ATTRIBUTE9 => null,
367 X_ATTRIBUTE10 => null,
368 X_ATTRIBUTE11 => null,
369 X_ATTRIBUTE12 => null,
370 X_ATTRIBUTE13 => null,
371 X_ATTRIBUTE14 => null,
372 X_ATTRIBUTE15 => null,
373 X_MEANING => p_lookup_code||' '||p_meaning,
374 X_DESCRIPTION => p_description,
375 X_CREATION_DATE => SYSDATE,
376 X_CREATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
377 X_LAST_UPDATE_DATE => SYSDATE,
378 X_LAST_UPDATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
379 X_LAST_UPDATE_LOGIN => TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
380
381 );
382
383 end INSERT_ROW;
384
385
386
387 PROCEDURE UPDATE_ROW(p_lookup_code in fnd_lookup_values.lookup_code%TYPE,
388 p_meaning in fnd_lookup_values.meaning%TYPE,
389 p_description IN fnd_lookup_values.description%type)
390 is
391 --G_LOOKUP_TYPE CONSTANT VARCHAR2(50) :='SE_POSTAL_CODE';
392
393 begin
394
395 FND_LOOKUP_VALUES_PKG.UPDATE_ROW(
396 X_LOOKUP_TYPE => g_lookup_type,
397 X_SECURITY_GROUP_ID => g_security_group_id,
398 X_VIEW_APPLICATION_ID => 3,
399 X_LOOKUP_CODE => p_lookup_code,
400 X_TAG => NULL,
401 X_ATTRIBUTE_CATEGORY => NULL,
402 X_ATTRIBUTE1 => NULL,
403 X_ATTRIBUTE2 => NULL,
404 X_ATTRIBUTE3 => NULL,
405 X_ATTRIBUTE4 => NULL,
406 X_ENABLED_FLAG => 'Y',
407 X_START_DATE_ACTIVE => null,
408 X_END_DATE_ACTIVE => NULL,
409 X_TERRITORY_CODE => 'SE',
410 X_ATTRIBUTE5 => NULL,
411 X_ATTRIBUTE6 => NULL,
412 X_ATTRIBUTE7 => NULL,
413 X_ATTRIBUTE8 => NULL,
414 X_ATTRIBUTE9 => NULL,
415 X_ATTRIBUTE10 => NULL,
416 X_ATTRIBUTE11 => NULL,
417 X_ATTRIBUTE12 => NULL,
418 X_ATTRIBUTE13 => NULL,
419 X_ATTRIBUTE14 => NULL,
420 X_ATTRIBUTE15 => NULL,
421 X_MEANING => p_lookup_code||' '||p_meaning,
422 X_DESCRIPTION => null,
423 X_LAST_UPDATE_DATE => SYSDATE,
424 X_LAST_UPDATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
425 X_LAST_UPDATE_LOGIN => TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')));
426
427 end UPDATE_ROW;
428
429 /*
430 Function is_number
431 This function checks whether the postal_code read from the file is
432 numeric or not
433 If the postal code is not numeric, the record is skipped and the a
434 warning is thrown by the concurrent manager
435 */
436
437
438 FUNCTION is_number ( p_value IN varchar2 ) return boolean is
439 l_number number(20);
440 begin
441 l_number := to_number(p_value);
442 return true;
443 exception
444 when others then
445 return false;
446 end is_number;
447
448
449 end PER_SE_POSTALCODE_UPLOAD ;