[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_P6EDI_PKG
Source
1 PACKAGE BODY pay_gb_p6edi_pkg AS
2 /* $Header: pygbp6ei.pkb 120.4 2007/11/02 10:53:34 dchindar noship $ */
3 -- --------------- p6 upload process ---------------------
4 --
5 -- uploads p6 information from flat file into
6 -- pay_gb_tax_code_interface table
7 --
8 PROCEDURE upload_p6(errbuf OUT NOCOPY VARCHAR2,
9 retcode OUT NOCOPY NUMBER,
10 p_request_id in number default null,
11 p_filename IN VARCHAR2,
12 p_mode IN NUMBER,
13 p_effective_date IN varchar2,
14 p_business_group_id IN NUMBER,
15 -- p_authority IN varchar2 default null, /*change for soy 08-09*/
16 p_validate_only IN varchar2 ) IS
17 l_date_of_message VARCHAR2(10) := null;
18 l_form_type VARCHAR2(10) := null;
19 l_form_number VARCHAR2(1) := null;
20 l_district_number VARCHAR2(3) := null;
21 l_employer_reference VARCHAR2(10) := null;
22 l_ni_number VARCHAR2(9) := null;
23 l_works_number VARCHAR2(20) := null;
24 l_total_pay_prev_emp VARCHAR2(35) := null;
25 l_total_tax_prev_emp VARCHAR2(35) := null;
26 l_tax_code VARCHAR2(10) := null;
27 l_employee_name VARCHAR2(28) := null;
28 l_effective_date VARCHAR2(20) := null;
29 l_issue_date VARCHAR2(10) := null;
30 l_week1_month1_indicator VARCHAR2(1) := '';
31 l_record_no NUMBER := 0;
32 l_present_line VARCHAR2(250) := null;
33 l_processing BOOLEAN := true;
34 l_line_number NUMBER := 0;
35 l_start_of_employer BOOLEAN := false;
36 l_file_handle utl_file.file_type;
37 l_qualifier VARCHAR2(3);
38 l_filename VARCHAR2(100);
39 l_location VARCHAR2(2000);
40 e_fatal_error exception;
41 l_record_name varchar2(5);
42 l_previous_record varchar2(5);
43 l_previous_qualifier varchar2(3);
44 l_lines_inserted number := 0;
45 l_pyudet_id number;
46 l_request_id number;
47
48 begin
49 --
50 -- hr_utility.trace_on(null,'P6');
51 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',5);
52 --
53 l_filename := p_filename;
54 --
55 -- Get I/O Directory
56 --
57 fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
58 --
59 -- Get request id
60 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
61
62 hr_utility.trace('directory: <'|| l_location || '>');
63 if l_location is null then
64 -- error : I/O directory not defined
65 retcode := 2;
66 errbuf := 'Input directory not defined. Set PER_DATA_EXCHANGE_DIR profile (HR: Data Exchange directory).';
67 hr_utility.trace('Input directory not defined in PER_DATA_EXCHANGE_DIR profile.');
68 raise e_fatal_error;
69 end if;
70 --
71 -- Open flat file
72 --
73 l_file_handle := utl_file.fopen(l_Location,l_filename,'r');
74 --
75 utl_file.get_line(l_file_handle,l_present_line);
76 --
77 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',10);
78 hr_utility.trace('line: '|| l_present_line);
79 --
80 if l_present_line = null then
81 l_processing := false;
82 -- the deletion is moved to the pyudet.run_process
83 -- else
84 -- clean the interface table
85 -- delete from pay_gb_tax_code_interface;
86 -- commit;
87 end if;
88 --
89 while l_processing loop
90 l_start_of_employer := false;
91 l_record_name := rtrim(ltrim(substr(l_present_line,1,5)));
92 l_qualifier := get_qualifier(l_present_line);
93 hr_utility.trace('l_rec_name is <' || l_record_name || '>');
94 hr_utility.trace('l_quali is <' || l_qualifier || '>');
95
96 -- For Bug fix 4232473, resetting record count to Zero for each set of
97 -- Employer Reference records.
98 if l_record_name = '****0' then
99 l_record_no := 0;
100 end if;
101
102 if l_record_name = 'BGM1' then -- get form type
103 l_form_number := substr(l_present_line,7,1);
104 if l_form_number not in ('1','2','3') then
105 -- error, form type error.
106 retcode := 2;
107 errbuf := 'This process only accepts P6, P6B and P9.';
108 --
109 hr_utility.trace('Form Type is not one of these: P6, P6B or P9.');
110 raise e_fatal_error;
111 else
112 if l_form_number = '1' then
113 l_form_type := 'P6';
114 elsif l_form_number = '2' then
115 l_form_type := 'P6B';
116 elsif l_form_number = '3' then
117 l_form_type := 'P9';
118 end if;
119 end if;
120 elsif l_record_name = 'NAD1A' and l_qualifier = 'BG' then
121 -- employer's group starts here
122 l_start_of_employer := true;
123 elsif l_record_name = 'ATT1' then
124 if l_qualifier = '7' then
125 l_employer_reference := process_att(l_present_line, l_qualifier);
126 elsif l_qualifier = '17' then
127 l_district_number := process_att(l_present_line, l_qualifier);
128 end if;
129 elsif l_record_name = 'DTM1' and l_qualifier = '243' then
130 l_date_of_message := process_date(l_present_line);
131 elsif l_record_name = 'UNS1' then --section separator
132 l_record_no := 0;
133 elsif l_record_name = 'LIN1' then --new employee
134 l_record_no := l_record_no + 1;
135 elsif l_record_name = 'NAD2B' then
136 hr_utility.trace('in NAD2B: l_prev_rec is <' || l_previous_record || '>');
137 hr_utility.trace('in NAD2B: l_prev_qual is <' || l_previous_qualifier || '>');
138 if l_previous_record = 'NAD2A' and l_previous_qualifier = 'BV' then
139 l_employee_name := get_name(l_present_line);
140 -- l_employee_name := ltrim(rtrim(l_employee_name || ' ' || substr(l_present_line,43,1)));
141 hr_utility.trace('employee name is (' || l_employee_name || ')');
142 else
143 -- error, file format error.
144 retcode := 2;
145 errbuf := 'The Input File does not follow the required format.';
146 --
147 hr_utility.trace('Format Error at line ' || to_char(l_line_number) || '.');
148 hr_utility.trace('Employee name does not have a required NAD2A previous record.');
149 raise e_fatal_error;
150 end if;
151 elsif l_record_name = 'ATT2' then
152 if l_qualifier = '11' then
153 l_ni_number := process_att(l_present_line, l_qualifier);
154 elsif l_qualifier = '19' then
155 l_works_number := process_att(l_present_line, l_qualifier);
156 end if;
157 elsif l_record_name = 'MOA1' then
158 if l_previous_record = 'TAX1' then
159 if l_previous_qualifier = '77' then
160 l_total_pay_prev_emp := process_tax(l_present_line,1);
161 elsif l_previous_qualifier = '81' then
162 l_total_tax_prev_emp := process_tax(l_present_line,1);
163 end if;
164 else
165 -- error, file format error.
166 retcode := 2;
167 errbuf := 'The Input File does not follow the required format.';
168 --
169 hr_utility.trace('Format Error at line ' || to_char(l_line_number) || '.');
170 hr_utility.trace('Amount does not have a required TAX1 previous record.');
171 raise e_fatal_error;
172 end if;
173 elsif l_record_name = 'DTM2' then
174 if l_qualifier = '7' then
175 l_effective_date := process_date(l_present_line);
176 elsif l_qualifier = '182' then
177 l_issue_date := process_date(l_present_line);
178 end if;
179 elsif l_record_name = 'ALC1' then
180 l_tax_code := process_tax(l_present_line,2);
181 l_week1_month1_indicator := ''; -- next line may have w1m1_indicator
182 elsif l_record_name = 'ATT3' then
183 if ((l_previous_record = 'ALC1') or (l_previous_record = 'ATT3')) then
184 if l_qualifier = '1' then -- Scottish Variable Rate indicator found
185 l_tax_code := 'S' || l_tax_code;
186 elsif l_qualifier = '2' then
187 l_week1_month1_indicator := 'Y';
188 else
189 -- error, file format error.
190 retcode := 2;
191 errbuf := 'The Input File does not follow the required format.';
192 --
193 hr_utility.trace('Format Error at line ' || to_char(l_line_number) || '.');
194 hr_utility.trace('Week1/Month1 or SVR indicator does not have a required ALC1 previous record.');
195 raise e_fatal_error;
196 end if;
197 end if;
198 end if;
199 --
200 --
201 l_previous_record := l_record_name;
202 l_previous_qualifier := l_qualifier;
203 l_line_number := l_line_number + 1;
204 --
205 --
206 if mod(l_lines_inserted,20) = 0 then
207 commit;
208 end if;
209 --
210 -- new employee or employer found, so upload the record just processed
211 --
212 if (((l_record_name = 'LIN1' or
213 (l_start_of_employer = true)) and (l_record_no > 1)) or
214 (l_record_name = 'UNS2')) then
215
216 write_to_database(l_date_of_message,
217 l_form_type,
218 l_district_number,
219 l_employer_reference,
220 l_ni_number,
221 l_works_number,
222 l_total_pay_prev_emp,
223 l_total_tax_prev_emp,
224 l_tax_code,
225 l_week1_month1_indicator,
226 l_employee_name,
227 l_effective_date,
228 l_issue_date,
229 l_request_id);
230
231 l_lines_inserted := l_lines_inserted + 1;
232 l_ni_number := null;
233 l_works_number := null;
234 l_total_pay_prev_emp := null;
235 l_total_tax_prev_emp := null;
236 l_tax_code := null;
237 l_employee_name := null;
238 end if;
239 --
240 --
241 -- read next line
242 --
243 --
244 begin
245 utl_file.get_line(l_file_handle,l_present_line);
246 --
247 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',50);
248 hr_utility.trace('line: '|| l_present_line);
249 --
250 exception
251 when no_data_found then
252 l_processing := false;
253 EXIT;
254 end;
255 --
256 --
257 end loop;
258 --
259 --
260 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',60);
261 hr_utility.trace('PAY_GB_P6EDI.upload_p6 - committing...');
262 commit;
263 --
264 --
265 utl_file.fclose(l_file_handle);
266 --
267 -- Call PYUDET mode 4
268 --
269 l_pyudet_id := fnd_request.submit_request(application => 'PAY',
270 program => 'PYUDET',
271 argument1 => p_request_id,
272 argument2 => p_mode,
273 argument3 => p_effective_date,
274 argument4 => p_business_group_id,
275 argument5 => null,
276 argument6 => l_form_type, --p_authority,
277 argument7 => l_request_id,
278 argument8 => p_validate_only); --added soy 08-09*/
279 --
280 --
281 hr_utility.trace('The pyudet request ID is '||to_char(l_pyudet_id));
282 hr_utility.trace('Finished P6 UPLOAD PROCESS.');
283 --
284 --
285 retcode := 0;
286 errbuf := 'No errors - examine logfiles for detailed reports.';
287 --hr_utility.trace_off;
288 --
289 --
290 exception
291 when e_fatal_error then
292 UTL_FILE.FCLOSE(l_file_handle);
293 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',100);
294 rollback;
295 when UTL_FILE.INVALID_OPERATION then
296 UTL_FILE.FCLOSE(l_file_handle);
297 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',110);
298 retcode:=2;
299 errbuf := 'Reading P6 File - Invalid Operation (file not found).';
300 when UTL_FILE.INTERNAL_ERROR then
301 UTL_FILE.FCLOSE(l_file_handle);
302 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',120);
303 retcode:=2;
304 errbuf := 'Reading P6 File - Internal Error.';
305 when UTL_FILE.INVALID_MODE then
306 UTL_FILE.FCLOSE(l_file_handle);
307 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',130);
308 retcode:=2;
309 errbuf := 'Reading P6 File - Invalid Mode.';
310 when UTL_FILE.INVALID_PATH then
311 UTL_FILE.FCLOSE(l_file_handle);
312 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',140);
313 retcode:=2;
314 errbuf := 'Reading P6 File - Invalid Path.';
315 when UTL_FILE.INVALID_FILEHANDLE then
316 UTL_FILE.FCLOSE(l_file_handle);
317 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',150);
318 retcode:=2;
319 errbuf := 'Reading P6 File - Invalid File Handle.';
320 when UTL_FILE.READ_ERROR then
321 UTL_FILE.FCLOSE(l_file_handle);
322 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',160);
323 retcode:=2;
324 errbuf := 'Reading P6 File - Read Error.';
325 when NO_DATA_FOUND then
326 UTL_FILE.FCLOSE(l_file_handle);
327 hr_utility.set_location('PAY_GB_P6EDI.upload_p6',170);
328 retcode:=2;
329 errbuf := 'No Data Found.';
330 end;
331
332
333 FUNCTION get_qualifier(line VARCHAR2)
334 return VARCHAR2 is
335 qualifier VARCHAR2(3);
336 begin
337 --
338 qualifier := substr(line,7,3);
339 --
340 hr_utility.set_location('PAY_GB_P6EDI.get_qualifier',70);
341 hr_utility.trace('qualifier is: ' || qualifier);
342 --
343 return ltrim(rtrim(qualifier));
344 end;
345
346 FUNCTION get_name(line VARCHAR2)
347 return VARCHAR2 is
348 name VARCHAR2(26);
349 begin
350 --
351 name := substr(line,7,26);
352 --
353 hr_utility.set_location('PAY_GB_P6EDI.get_name',75);
354 hr_utility.trace('name is: <' || name || '>');
355 --
356 return ltrim(rtrim(name));
357 end;
358
359
360 FUNCTION process_att(line VARCHAR2, qualifier VARCHAR2)
361 return VARCHAR2 is
362 string_to_return VARCHAR2(20);
363 begin
364 --
365 if qualifier = '17' then -- returns district_number
366 string_to_return := substr(line,11,3);
367 elsif qualifier = '7' then -- returns employer reference
368 string_to_return := substr(line,11,10);
369 elsif qualifier = '11' then -- returns ni number
370 string_to_return := substr(line,11,9);
371 elsif qualifier = '19' then -- returns works number
372 string_to_return := substr(line,11,20);
373 end if;
374 --
375 hr_utility.set_location('PAY_GB_P6EDI.process_att',80);
376 hr_utility.trace('string is: ' || string_to_return);
377 --
378 return ltrim(rtrim(string_to_return));
379 end;
380
381
382 FUNCTION process_date(line VARCHAR2)
383 return VARCHAR2 is
384 l_date_type VARCHAR2(3) := null;
385 string_to_return VARCHAR2(10) := null;
386 begin
387 --
388 l_date_type := substr(line,47,3);
389 if l_date_type = '102' then
390 string_to_return := substr(line,11,4) || '/' ||
391 substr(line,15,2) || '/' ||
392 substr(line,17,2);
393 end if;
394 --
395 hr_utility.set_location('PAY_GB_P6EDI.process_date',85);
396 hr_utility.trace('string is: ' || string_to_return);
397 --
398 return ltrim(rtrim(string_to_return));
399 end;
400
401
402 FUNCTION process_tax(line VARCHAR2, process_type NUMBER)
403 return VARCHAR2 is
404 string_to_return VARCHAR2(35);
405 begin
406 --
407 if process_type = 1 then -- returns amount
411 end if;
408 string_to_return := substr(line,7,35);
409 elsif process_type = 2 then -- returns tax code
410 string_to_return := substr(line,11,7);
412 --
413 hr_utility.set_location('PAY_GB_P6EDI.process_tax',90);
414 hr_utility.trace('string is: ' || string_to_return);
415 --
416 return ltrim(rtrim(string_to_return));
417 end;
418
419 PROCEDURE write_to_database(date_of_message_p VARCHAR2,
420 form_type_p VARCHAR2,
421 district_number_p VARCHAR2,
422 employer_reference_p VARCHAR2,
423 ni_number_p VARCHAR2,
424 works_number_p VARCHAR2,
425 total_pay_prev_emp_p VARCHAR2,
426 total_tax_prev_emp_p VARCHAR2,
427 tax_code_p VARCHAR2,
428 week1_month1_indicator_p VARCHAR2,
429 employee_name_p VARCHAR2,
430 effective_date_p VARCHAR2,
431 issue_date_p VARCHAR2,
432 request_id_p number default null) IS
433 begin
434 hr_utility.set_location('PAY_GB_P6EDI.write_to_database',95);
435
436 hr_utility.trace('tushar employee name is '||employee_name_p);
437 INSERT INTO pay_gb_tax_code_interface(
438 date_of_message,
439 effective_date,
440 issue_date,
441 form_type,
442 district_number,
443 employer_reference,
444 national_insurance_number,
445 tot_pay_prev_emp,
446 tot_tax_prev_emp,
447 tax_code,
448 non_cumulative_flag,
449 employee_name,
450 works_number,
451 processed_flag,
452 request_id)
453 VALUES (
454 fnd_date.canonical_to_date(date_of_message_p),
455 fnd_date.canonical_to_date(effective_date_p),
456 fnd_date.canonical_to_date(issue_date_p),
457 form_type_p,
458 to_number(district_number_p),
459 employer_reference_p,
460 ni_number_p,
461 total_pay_prev_emp_p,
462 total_tax_prev_emp_p,
463 tax_code_p,
464 week1_month1_indicator_p,
465 employee_name_p,
466 works_number_p,
467 '',
468 request_id_p);
469 end;
470
471 end pay_gb_p6edi_pkg;
472