eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PER_NO_POSTALCODE_UPLOAD

Source


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