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