[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_ALECTA_UPLOAD
Source
1 PACKAGE BODY PAY_SE_ALECTA_UPLOAD AS
2 /* $Header: pyseaaru.pkb 120.0.12010000.1 2010/02/08 09:40:54 vijranga noship $ */
3
4
5 g_package CONSTANT VARCHAR2 (33) := ' pay_se_alecta_upload';
6 e_record_not_found EXCEPTION;
7
8 c_warning CONSTANT NUMBER := 1;
9 c_error CONSTANT NUMBER := 2;
10 c_end_of_time CONSTANT DATE := to_date('12/31/4712','MM/DD/YYYY');
11
12 PROCEDURE upload (
13 errbuf OUT NOCOPY VARCHAR2,
14 retcode OUT NOCOPY NUMBER,
15 p_file_name IN VARCHAR2,
16 p_effective_date IN VARCHAR2,
17 p_business_group_id IN per_business_groups.business_group_id%TYPE
18 )
19 IS
20 c_read_file CONSTANT VARCHAR2 (1) := 'r';
21 c_max_linesize CONSTANT NUMBER := 4000;
22 c_data_exchange_dir CONSTANT VARCHAR2(30) := 'PER_DATA_EXCHANGE_DIR';
23
24 l_proc CONSTANT VARCHAR2(72) := g_package||'.upload' ;
25 l_legislation_code per_business_groups.legislation_code%TYPE;
26 l_bg_name per_business_groups.name%TYPE;
27
28 l_file_type UTL_FILE.file_type;
29 l_filename VARCHAR2 (240);
30 l_location VARCHAR2 (4000);
31 l_line_read VARCHAR2 (4000) := NULL;
32 l_line_no NUMBER := 0;
33 l_record_found NUMBER := 0;
34
35 e_fatal_error EXCEPTION;
36
37
38 CURSOR csr_leg (v_bg_id per_business_groups.business_group_id%TYPE)
39 IS
40 SELECT legislation_code, name
41 FROM per_business_groups
42 WHERE business_group_id = v_bg_id;
43
44
45 BEGIN
46
47 INSERT INTO fnd_sessions(SESSION_ID , EFFECTIVE_DATE )
48 VALUES(userenv('SESSIONID'),fnd_date.canonical_to_date(p_effective_date)) ;
49
50 hr_utility.set_location('p_file_name '||p_file_name,1);
51 hr_utility.set_location('p_business_group_id '||p_business_group_id,1 );
52
53 hr_utility.set_location ( 'Entering:' || l_proc, 10);
54 -- fnd_file.put_line(fnd_file.log,'$$ Entering'||l_proc);
55
56 OPEN csr_leg (p_business_group_id);
57 FETCH csr_leg INTO l_legislation_code, l_bg_name;
58 CLOSE csr_leg;
59
60 hr_utility.set_location ( 'Legislation = ' || l_legislation_code, 20);
61 -- fnd_file.put_line(fnd_file.log,'$$ l_legislation_code'||l_legislation_code);
62
63 l_filename := p_file_name;
64 fnd_profile.get (c_data_exchange_dir, l_location);
65
66 hr_utility.set_location ( 'Directory = ' || l_location, 30);
67 -- fnd_file.put_line(fnd_file.log,'$$ l_location'||l_location);
68
69 IF l_location IS NULL
70 THEN
71 hr_utility.set_location ( 'Raising I/O error = ' || l_location, 35);
72 RAISE e_fatal_error;
73 END IF;
74
75 l_file_type :=
76 UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
77
78 <<read_lines_in_file>>
79 LOOP
80 BEGIN
81 UTL_FILE.get_line (l_file_type, l_line_read);
82 l_line_no := l_line_no + 1;
83 EXCEPTION
84 WHEN VALUE_ERROR
85 THEN
86 IF UTL_FILE.is_open (l_file_type)
87 THEN
88 UTL_FILE.fclose (l_file_type);
89 END IF;
90
91 hr_utility.set_location (l_proc, 50);
92 retcode := c_error;
93 errbuf := 'Input line (line nr = '|| l_line_no + 1 || ') too large for buffer (=' || c_max_linesize || ').';
94 EXIT;
95 WHEN NO_DATA_FOUND
96 THEN
97 EXIT;
98 END;
99 hr_utility.set_location ( ' line read: ' || l_line_read,110);
100
101 BEGIN
102
103 compare_record (
104 p_line => l_line_read
105 ,p_record_found => l_record_found
106 );
107
108 hr_utility.set_location ( ' l_record_found = ' || l_record_found, 130);
109 -- fnd_file.put_line(fnd_file.log,'$$ l_record_found'||l_record_found);
110
111 if l_record_found = 0 then
112 hr_utility.set_location ( ' Record not found', 110);
113 RAISE e_record_not_found;
114 end if;
115
116 EXCEPTION
117
118 WHEN e_record_not_found
119 THEN
120 retcode := c_warning;
121 hr_utility.set_message (801, 'HR_377018_DK_INVALID_RECORD');
122 hr_utility.set_message_token (801, 'LINE', l_line_read);
123 hr_utility.set_location (l_proc, 260);
124 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
125 END;
126 END LOOP read_lines_in_file;
127
128 UTL_FILE.fclose (l_file_type);
129 hr_utility.set_location ( 'Leaving:'|| l_proc, 260);
130
131 EXCEPTION
132 WHEN e_fatal_error
133 THEN
134 IF UTL_FILE.is_open (l_file_type)
135 THEN
136 UTL_FILE.fclose (l_file_type);
137 END IF;
138
139 hr_utility.set_location (l_proc, 270);
140 retcode := c_error;
141 hr_utility.set_message (801, 'HR_SE_DATA_EXCHANGE_DIR_MIS');
142 errbuf := hr_utility.get_message;
143
144 WHEN UTL_FILE.invalid_operation
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, 280);
152 retcode := c_error;
153 errbuf := 'Reading File ('||l_location ||' -> ' || l_filename || ') - Invalid Operation.';
154
155 WHEN UTL_FILE.internal_error
156 THEN
157 IF UTL_FILE.is_open (l_file_type)
158 THEN
159 UTL_FILE.fclose (l_file_type);
160 END IF;
161
162 hr_utility.set_location (l_proc, 290);
163 retcode := c_error;
164 errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Internal Error.';
165
166 WHEN UTL_FILE.invalid_mode
167 THEN
168 IF UTL_FILE.is_open (l_file_type)
169 THEN
170 UTL_FILE.fclose (l_file_type);
171 END IF;
172
173 hr_utility.set_location (l_proc, 300);
174 retcode := c_error;
175 errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Invalid Mode.';
176
177 WHEN UTL_FILE.invalid_path
178 THEN
179 IF UTL_FILE.is_open (l_file_type)
180 THEN
181 UTL_FILE.fclose (l_file_type);
182 END IF;
183
184 retcode := c_error;
185 errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Invalid Path or Filename.';
186 hr_utility.set_location (l_proc, 310);
187
188 WHEN UTL_FILE.invalid_filehandle
189 THEN
190 IF UTL_FILE.is_open (l_file_type)
191 THEN
192 UTL_FILE.fclose (l_file_type);
193 END IF;
194
195 hr_utility.set_location (l_proc, 320);
196 retcode := c_error;
197 errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Invalid File Type.';
198 WHEN UTL_FILE.read_error
199
200 THEN
201 IF UTL_FILE.is_open (l_file_type)
202 THEN
203 UTL_FILE.fclose (l_file_type);
204 END IF;
205
206 hr_utility.set_location (l_proc, 330);
207 retcode := c_error;
208 errbuf := 'Reading File (' || l_location || ' -> ' || l_filename || ') - Read Error.';
209 END upload;
210
211
212 PROCEDURE compare_record
213 (p_line IN VARCHAR2
214 ,p_record_found OUT NOCOPY NUMBER
215 )
216 IS
217
218 l_proc CONSTANT VARCHAR2 (72) := g_package|| '.compare_record';
219 l_org_number VARCHAR2(60);
220 l_cost_centre VARCHAR2(60);
221 l_agreed_product_id VARCHAR2(60);
222 l_agreed_product VARCHAR2(60);
223 l_person_number VARCHAR2(60);
224 l_last_name VARCHAR2(60);
225 l_first_name VARCHAR2(60);
226 l_salary VARCHAR2(60);
227 l_sal_after_withdraw VARCHAR2(60);
228 l_time_of_event VARCHAR2(60);
229 l_alt_itp VARCHAR2(60);
230 l_sick_leave VARCHAR2(60);
231 l_parental_leave VARCHAR2(60);
232 l_leave_of_absence VARCHAR2(60);
233 l_record_found number := 0;
234
235 BEGIN
236
237 hr_utility.set_location ( 'Entering:'|| l_proc, 70);
238 --fnd_file.put_line(fnd_file.log,''||p_line);
239
240 l_org_number := get_token(p_line,1);
241 l_cost_centre := get_token(p_line,2);
242 l_agreed_product_id := get_token(p_line,3);
243 l_person_number := get_token(p_line,4);
244 l_last_name := get_token(p_line,5);
245 l_first_name := get_token(p_line,6);
246 l_salary := get_token(p_line,7);
247 l_time_of_event := get_token(p_line,8);
248 l_alt_itp := get_token(p_line,9);
249 l_sick_leave := get_token(p_line,10);
250 l_parental_leave := get_token(p_line,11);
251 l_leave_of_absence := get_token(p_line,12);
252
253 l_org_number := replace(l_org_number,'-',''); -- Bug#9345105 fix
254 l_person_number := replace(l_person_number,'-',''); -- Bug#9345105 fix
255
256 /* fnd_file.put_line(fnd_file.log,'$$ l_org_number'||l_org_number);
257 fnd_file.put_line(fnd_file.log,'$$ l_cost_centre'||l_cost_centre);
258 fnd_file.put_line(fnd_file.log,'$$ l_agreed_product_id'||l_agreed_product_id);
259 fnd_file.put_line(fnd_file.log,'$$ l_person_number'||l_person_number);
260 fnd_file.put_line(fnd_file.log,'$$ l_last_name'||l_last_name);
261 fnd_file.put_line(fnd_file.log,'$$ l_first_name'||l_first_name);
262 fnd_file.put_line(fnd_file.log,'$$ l_salary'||l_salary);
263 fnd_file.put_line(fnd_file.log,'$$ l_time_of_event'||l_time_of_event);
264 fnd_file.put_line(fnd_file.log,'$$ l_alt_itp'||l_alt_itp);
265 fnd_file.put_line(fnd_file.log,'$$ l_sick_leave'||l_sick_leave);
266 fnd_file.put_line(fnd_file.log,'$$ l_parental_leave'||l_parental_leave);
267 fnd_file.put_line(fnd_file.log,'$$ l_leave_of_absence'||l_leave_of_absence);
268 */
269
270 /*
271 ----------- Four cases -----------
272 where org no, cost centre, person no and time for event are mandatory
273 */
274
275 IF (l_org_number IS NOT NULL AND l_cost_centre IS NOT NULL
276 AND l_person_number IS NOT NULL AND l_time_of_event IS NOT NULL)
277 THEN
278 /*
279 ------------- Case 1 New Entry ----------------
280 where org no, cost centre, agreed product, person no, time for event, last name,
281 first name, salary details are available
282 */
283 IF (l_first_name IS NOT NULL AND l_last_name IS NOT NULL) THEN
284 fnd_file.put_line(fnd_file.log,'## Case 1 New entry');
285 SELECT COUNT ('1') INTO l_record_found
286 FROM pay_action_information
287 WHERE action_information_category = 'EMEA REPORT INFORMATION'
288 AND action_information1 = 'PYSEALEA'
289 AND l_org_number = action_information5
290 AND l_cost_centre = action_information6
291 AND l_agreed_product_id = action_information7
292 AND l_person_number = action_information8
293 AND l_last_name = action_information10
294 AND l_first_name = action_information11
295 AND l_salary IN (action_information14, action_information13)
296 AND fnd_date.date_to_canonical(to_date(l_time_of_event, 'YYYY/MM/DD')) = action_information9;
297
298 /*
299 ------------ Case 2 Moving with in comapnay -------
300 where org no, cost centre, agreed plan, person no, time for event, salary deatails
301 are available
302 */
303 ELSIF (l_agreed_product_id IS NOT NULL) THEN
304 fnd_file.put_line(fnd_file.log,'## Case 2 Moving with in comapnay');
305 SELECT COUNT ('1') INTO l_record_found
306 FROM pay_action_information
307 WHERE action_information_category = 'EMEA REPORT INFORMATION'
308 AND action_information1 = 'PYSEALEA'
309 AND l_org_number = action_information5
310 AND l_cost_centre = action_information6
311 AND l_agreed_product_id = action_information7
312 AND l_person_number = action_information8
313 AND l_salary IN (action_information12, action_information11)
314 AND fnd_date.date_to_canonical(to_date(l_time_of_event, 'YYYY/MM/DD')) = action_information9;
315
316 /*
317 ----------- Case 3 Salary Change --------------
318 where org no, cost centre, person no, time for event, salary details are available
319 */
320 ELSIF (l_salary IS NOT NULL ) THEN
321 fnd_file.put_line(fnd_file.log,'## Case 3 Salary change');
322 SELECT COUNT ('1') INTO l_record_found
323 FROM pay_action_information
324 WHERE action_information_category = 'EMEA REPORT INFORMATION'
325 AND action_information1 = 'PYSEALEA'
326 AND l_org_number = action_information5
327 AND l_cost_centre = action_information6
328 AND l_person_number = action_information7
329 AND l_salary IN (action_information11, action_information10)
330 AND fnd_date.date_to_canonical(to_date(l_time_of_event, 'YYYY/MM/DD')) = action_information8;
331
332 /*
333 ------------- Case 4 withdrawl ------------
334 where org no, cost centre, person no, time for event details are available
335 */
336 ELSE
337 fnd_file.put_line(fnd_file.log,'## Case 4 Withdrawl');
338 SELECT COUNT ('1') INTO l_record_found
339 FROM pay_action_information
340 WHERE action_information_category = 'EMEA REPORT INFORMATION'
341 AND action_information1 = 'PYSEALEA'
342 AND l_org_number = action_information5
343 AND l_cost_centre = action_information6
344 AND l_person_number = action_information7
345 AND fnd_date.date_to_canonical(to_date(l_time_of_event, 'YYYY/MM/DD')) = action_information8 ;
346 END IF;
347 END IF;
348
349 p_record_found := l_record_found;
350 --fnd_file.put_line(fnd_file.log,'## l_record_found'||l_record_found);
351 hr_utility.set_location ( 'Leaving:'|| l_proc, 120);
352
353 END compare_record;
354
355 function get_token(
356 the_string varchar2,
357 the_index number,
358 delim varchar2 := ';'
359 )
360 return varchar2
361 is
362 start_pos number;
363 end_pos number;
364 begin
365 if the_index = 1 then
366 start_pos := 1;
367 else
368 start_pos := instr(the_string, delim, 1, the_index - 1);
369 if start_pos = 0 then
370 return null;
371 else
372 start_pos := start_pos + length(delim);
373 end if;
374 end if;
375
376 end_pos := instr(the_string, delim, start_pos, 1);
377
378 if end_pos = 0 then
379 return substr(the_string, start_pos);
380 else
381 return substr(the_string, start_pos, end_pos - start_pos);
382 end if;
383
384 end get_token;
385
386 END PAY_SE_ALECTA_UPLOAD;