[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_RTI_NINO_REPLY_PKG
Source
1 package body PAY_GB_RTI_NINO_REPLY_PKG AS
2 /* $Header: pygbrtininoreply.pkb 120.23.12020000.2 2013/02/08 13:49:48 ssarap noship $ */
3 /* This package reads the response file for NI Number verification sent by HMRC
4 and updates the changes in the system.
5 **/
6 -- -------------------------------------------------------------------------------------------
7 -- The main update.
8 -- -------------------------------------------------------------------------------------------
9 l_last_name varchar2(35);
10 l_first_name varchar2(35);
11 l_nino_provided varchar2(20);
12 l_emp_number varchar2(35);
13 application_error exception;
14 TYPE report_output_type IS TABLE OF varchar2(1000) index by binary_integer;
15 report_output_already_process report_output_type;
16 l_processed_already number := 0;
17 report_output_warnings report_output_type;
18 warnings number:= 1;
19 l_processed_warnings number := 0;
20 l_nino_to_use varchar2(20);
21 procedure read_nino_reply( errbuf OUT NOCOPY VARCHAR2,
22 retcode OUT NOCOPY NUMBER,
23 p_business_group number,
24 p_filename varchar2,
25 p_validate_mode varchar2
26 ) IS
27
28 l_filename varchar2(200);
29 l_package_name varchar2(30) := 'PAY_GB_RTI_NINO_REPLY_PKG';
30 l_file_handle utl_file.file_type;
31 l_request_id varchar2(10);
32 l_location varchar2(2000) := null;
33 l_processing BOOLEAN := true;
34 l_present_line varchar2(250);
35 l_record_name varchar2(10);
36 l_qualifier varchar2(10);
37 l_unique_reference varchar2(20);
38 l_nino_reply_type varchar2(20);
39 l_form_type varchar2(20);
40 l_payroll_id varchar2(20);
41 e_fatal_error exception;
42 e_invalid_paye_ref exception;
43 e_multiple_emp_err exception;
44 l_employer_paye_ref varchar2(35);
45 l_employee_count number := 0;
46 l_employee_number per_all_people_f.employee_number%type;
47 l_date_issued_format varchar(20);
48 l_date_issued varchar2(20);
49 l_date_of_msg_format varchar2(20);
50 l_date_of_msg varchar2(20);
51 l_msg_ident varchar2(20);
52 l_person_id number;
53 l_start_date date;
54 l_end_date date;
55 l_obj_ver number;
56
57 l_recent_start_date date;
58 l_updated_flag boolean := false;
59 report_output_header report_output_type;
60 header number := 0;
61 report_output_success report_output_type;
62 success number := 1;
63
64 l_test_indicator varchar2(1);
65 l_hmrc_off_number varchar2(35);
66 l_correlation_id varchar2(35);
67 l_emp_count number;
68
69 l_processed_success number :=0;
70 l_loop_count NUMBER := 0;
71 /*
72 function check_correlation_ids(f_correlation_id varchar2)
73 return boolean is
74 l_exists varchar2(1) := null;
75 begin
76
77 select 'Y' into l_exists from dual where regexp_like(trim(p_correlation_ids),'[,]*\s*'||f_correlation_id|| '$') OR
78 regexp_like(trim(p_correlation_ids),',\s*' || f_correlation_id || '\s*,') OR regexp_like(trim(p_correlation_ids),'^' || f_correlation_id || '\s*[,]*') ;
79 fnd_file.put_line(fnd_file.LOG,'correlation id <' || f_correlation_id || '>' ||'Matches');
80 return true;
81 exception
82 when no_data_found then
83 fnd_file.put_line(fnd_file.LOG,'correlation id <' || f_correlation_id || '>' ||' doesnot match');
84 return false;
85 end check_correlation_ids;
86 */
87 function validate_paye_ref(l_employer_paye_ref varchar2 , p_business_group number) return
88 boolean is
89 cursor csr_paye_ref_exists is
90 select 1 from hr_organization_information where organization_id = p_business_group
91 and org_information1 = l_employer_paye_ref
92 and org_information_context = 'Tax Details References';
93 l_paye_exists number := null;
94 begin
95 fnd_file.put_line(fnd_file.LOG,'Validating Paye Reference');
96 open csr_paye_ref_exists;
97 fetch csr_paye_ref_exists into l_paye_exists;
98 close csr_paye_ref_exists;
99 if l_paye_exists = 1 then
100 return false;
101 end if;
102 return true;
103 end validate_paye_ref;
104
105 function update_extra_information(
106 f_person_id number,
107 f_nino_verifed varchar2 default 'No',
108 f_date_of_issue date default null,
109 f_nvrep_status varchar2 default null,
110 f_nino_ver_type varchar2 default null,
111 f_correlation_id varchar default null
112 ) return number is
113 l_extra_info_id number := null;
114 l_object_version_number number;
115 l_nino_verified_flag varchar2(15) := null;
116 l_correlation_id varchar2(35);
117 cursor fetch_person_extra_info is
118 select PERSON_EXTRA_INFO_ID,object_version_number,pei_information1, pei_information5
119 from per_people_extra_info where PERSON_ID = f_person_id
120 and pei_information_category = 'RTI_NINO';
121 begin
122 fnd_file.put_line(fnd_file.LOG,'Entering ' ||l_package_name || '.update_extra_information');
123
127 and pei_information_category = 'RTI_NINO';
124 /*select PERSON_EXTRA_INFO_ID,object_version_number,pei_information1, pei_information5 into
125 l_extra_info_id, l_object_version_number, l_nino_verified_flag,l_correlation_id
126 from per_people_extra_info where PERSON_ID = f_person_id
128 */
129
130 fnd_file.put_line(fnd_file.LOG, 'f_person_id: ' ||to_char(f_person_id) ||
131 fnd_global.local_chr(10) || 'f_nino_verifed ' || f_nino_verifed
132 ||fnd_global.local_chr(10) || to_char(f_date_of_issue)
133 ||fnd_global.local_chr(10) || f_nvrep_status
134 ||fnd_global.local_chr(10) || f_nino_ver_type
135 ||fnd_global.local_chr(10) || 'l_extra_info_id: ' || to_char(l_extra_info_id)
136 ||fnd_global.local_chr(10) || 'l_object_version_number: ' || to_char(l_object_version_number));
137 open fetch_person_extra_info;
138 fetch fetch_person_extra_info into l_extra_info_id, l_object_version_number, l_nino_verified_flag,l_correlation_id;
139 close fetch_person_extra_info;
140 -- Already record exists and we have to update it if all the validations satisfy.
141 if l_extra_info_id is not null then
142 -- Update the extra info using update API only when the RTI Verified is not already verified
143 if l_nino_verified_flag is null OR l_nino_verified_flag = 'No' then
144 /* Process the employee if
145 1. Correlation id on the reply file matches the correlation id stamped on the employee.
146 2. Correlation id is null in reply file due to notification from EAS/FPS and no correlation id is stamped on employee.
147 */
148 if nvl(l_correlation_id,' ') = nvl(f_correlation_id,' ') then
149 fnd_file.put_line(fnd_file.LOG,'Updating the person EIT record');
150 hr_person_extra_info_api.update_person_extra_info(
151 p_person_extra_info_id => l_extra_info_id,
152 p_pei_information_category => 'RTI_NINO',
153 p_pei_information1 => f_nino_verifed,
154 p_pei_information2 => fnd_date.date_to_canonical(f_date_of_issue),
155 p_pei_information3 => f_nvrep_status,
156 p_pei_information4 => f_nino_ver_type,
157 p_object_version_number => l_object_version_number
158 );
159 /* Correlation id is stamped on employee and no correlation id is present
160 in the reply file. Throw Warning.
161 */
162 elsif l_correlation_id is not null and f_correlation_id is null then
163 l_processed_warnings := l_processed_warnings + 1;
164 warnings := warnings + 1;
165 report_output_warnings(warnings) :=
166 'Correlation ID ' || l_correlation_id || ' found for matched employee <' || l_emp_number || ', '|| l_last_name || '> - NINO not uploaded';
167 return -1;
168 /* Correlation id is not stamped on employee and correlation id is present
169 in the reply file. Throw Warning.
170 */
171 elsif l_correlation_id is null and f_correlation_id is not null then
172 l_processed_warnings := l_processed_warnings + 1;
173 warnings := warnings + 1;
174 report_output_warnings(warnings) :=
175 'Correlation ID ' || f_correlation_id || ' not present for matched employee <' || l_emp_number || ', ' || l_last_name ||
176 '> - please check and update NI number manually or enter the correlation ID on employee record that was received from NVREQ acknowledgement and re-run process.';
177 return -1;
178 /* Correlation id is stamped on employee and correlation id is present
179 in the reply file but both mismatch.. Throw Warning.
180 */
181 elsif l_correlation_id is not null and f_correlation_id is not null then
182 l_processed_warnings := l_processed_warnings + 1;
183 warnings := warnings + 1;
184 report_output_warnings(warnings) :=
185 'Correlation ID mismatch for <,' || l_emp_number || ', ' || l_last_name || '> - NINO not uploaded';
186 return -1;
187 end if;
188 else
189 l_processed_already := l_processed_already + 1;
190 report_output_already_process(l_processed_already) := 'NINO is verified already for this person: employee no. <' || l_emp_number ||'> last name <' ||l_last_name || '> first name <'
191 || l_first_name ||'> NI Number <' || l_nino_provided ||'>' || ' New NI Number <' || l_nino_to_use || '>';
192 fnd_file.put_line(fnd_file.LOG,'NINO is verified already for this person: last name <' ||l_last_name || '> first name <' || l_first_name ||'> NI Number <' || l_nino_provided ||'>');
193 return -1;
194 end if;
195 elsif f_correlation_id is null then -- insert a new record, this should happen for only Notification status where correlation id is empty.
196 -- insert a new record.
197 fnd_file.put_line(fnd_file.LOG,'Inserting the person EIT record');
198 fnd_file.put_line(fnd_file.LOG,'f_nvrep_status:--> ' || f_nvrep_status || ' f_nino_ver_type:--> ['|| f_nino_ver_type||']');
199
200 hr_person_extra_info_api.create_person_extra_info(
201 p_person_id =>f_person_id,
202 p_information_type => 'RTI_NINO',
203 p_pei_information_category => 'RTI_NINO',
204 p_pei_information1 => f_nino_verifed,
205 p_pei_information2 => fnd_date.date_to_canonical(f_date_of_issue),
206 p_pei_information3 => f_nvrep_status,
207 p_pei_information4 => f_nino_ver_type,
208 p_object_version_number => l_object_version_number,
209 p_person_extra_info_id => l_extra_info_id
210 );
211 else
212 -- generate warning
213 l_processed_warnings := l_processed_warnings + 1;
214 warnings := warnings + 1;
215 report_output_warnings(warnings) :=
216 'Correlation ID ' || f_correlation_id || ' not present for matched employee <,' || l_emp_number || ', ' || l_last_name ||
217 '> - please check and update NI number manually or enter the correlation ID on employee record that was received from NVREQ acknowledgement and re-run process';
218 return -1;
219 end if;
220 fnd_file.put_line(fnd_file.LOG,'Leaving ' ||l_package_name || '.update_extra_information');
221 return 1;
222 exception
226 rollback;
223 when OTHERS then
224 fnd_file.put_line(fnd_file.LOG,DBMS_UTILITY.format_error_backtrace);
225 fnd_file.put_line(fnd_file.LOG,sqlcode ||' - ' || sqlerrm);
227 raise application_error;
228
229 end update_extra_information;
230
231 function update_emp_record(
232 p_person_id number,
233 p_start_date date ,
234 p_end_date date,
235 p_nino_to_use varchar2,
236 p_date_issued date ,
237 p_msg_ident varchar2,
238 p_nino_reply_type varchar2,
239 p_object_version_number number,
240 p_employee_number varchar2,
241 p_recent_start_date date,
242 p_correlation_id varchar2
243 ) return boolean is
244 p_effective_start_date date;
245 p_effective_end_date date;
246 p_full_name varchar2(103);
247 p_comment_id number;
248 p_name_combination_warning boolean;
249 p_assign_payroll_warning boolean;
250 p_orig_hire_warning boolean;
251 l_datetrack_update_mode varchar2(25);
252 l_future_rec_count number :=0;
253 cursor csr_future_records_count is
254 select count(*) from
255 per_all_people_f pap where
256 pap.person_id = p_person_id
257 and pap.effective_start_date >= p_recent_start_date
258 and pap.effective_start_date > p_date_issued;
259
260 -- To fetch the furture records of the employee
261 cursor csr_future_records(c_ignore_date varchar2) is
262 select pap.employee_number,pap.object_version_number, pap.effective_start_date from
263 per_all_people_f pap where
264 pap.person_id = p_person_id
265 and pap.effective_start_date >= p_recent_start_date
266 and pap.effective_start_date > decode(c_ignore_date, 'Y', pap.effective_start_date-1,p_date_issued)
267 order by 3;
268 l_update_status number;
269 l_obj_version number;
270 begin
271 fnd_file.put_line(fnd_file.LOG,'Entering ' || l_package_name || '.update_emp_record');
272 fnd_file.put_line(fnd_file.LOG,'Parameters ' || ' p_person_id: ' || to_char(p_person_id)
273 || fnd_global.local_chr(10) || 'p_start_date: ' || to_char(p_start_date)
274 || fnd_global.local_chr(10) || 'p_end_date: ' || to_char(p_end_date )
275 || fnd_global.local_chr(10) || 'p_nino_to_use: ' || p_nino_to_use
276 || fnd_global.local_chr(10) || 'p_date_issued: ' || to_date(p_date_issued)
277 || fnd_global.local_chr(10) || 'p_msg_ident: ' ||p_msg_ident
278 || fnd_global.local_chr(10) || 'p_nino_reply_type: ' || p_nino_reply_type
279 || fnd_global.local_chr(10) || 'p_object_version_number: ' || to_char(p_object_version_number)
280 || fnd_global.local_chr(10) || 'p_employee_number: ' || p_employee_number
281 || fnd_global.local_chr(10) || 'p_recent_start_date: ' ||to_char(p_recent_start_date)
282 );
283 /* Status Details.
284 1. This notification shows the correct NINO. Please use this NINO for any future submissions instead of the one you originally provided.
285 2. There was no NINO provided or the one given is incl_orrect. Please do not use the incorrect one for any future submissions
286 3. For future submissions please use the NINO provided in this notification
287 4. The NINO you have provided is correct. For future submissions please continue to use this NINO.
288 5. Service currently unavailable
289 */
290 --fnd_file.put_line(fnd_file.LOG,'NVREP Status --> ' || l_return_status);
291 if p_msg_ident = '2' then
292
293 -- update EIT
294 l_update_status := update_extra_information(
295 f_person_id => p_person_id,
296 f_nvrep_status =>p_msg_ident,
297 f_nino_ver_type => p_nino_reply_type,
298 f_correlation_id => p_correlation_id);
299
300 if l_update_status <> -1 then
301 -- Make the NI Number Null for all the records.
302 for emp_rec in csr_future_records('Y')
303 loop
304 l_obj_version := emp_rec.object_version_number;
305 l_employee_number := emp_rec.employee_number;
306 hr_person_api.update_person(
307 p_effective_date =>emp_rec.effective_start_date
308 ,p_datetrack_update_mode => 'CORRECTION'
309 ,p_person_id =>p_person_id
310 ,p_national_identifier => null
311 ,p_object_version_number => l_obj_version
312 ,p_employee_number =>l_employee_number
313 ,p_effective_start_date=>p_effective_start_date
314 ,p_effective_end_date =>p_effective_end_date
315 ,p_full_name =>p_full_name
316 ,p_comment_id =>p_comment_id
317 ,p_name_combination_warning =>p_name_combination_warning
318 ,p_assign_payroll_warning =>p_assign_payroll_warning
319 ,p_orig_hire_warning =>p_orig_hire_warning
320 );
321 end loop;
322 end if;
323
324 elsif p_msg_ident = '1' then
325 -- NINO is verified now. Update all the EIT's
326 l_update_status := update_extra_information(
327 f_person_id => p_person_id,
328 f_nino_verifed => 'Yes - HMRC',
329 f_date_of_issue => p_date_issued,
330 f_nvrep_status =>p_msg_ident,
331 f_nino_ver_type => p_nino_reply_type,
332 f_correlation_id => p_correlation_id);
333 if l_update_status <> -1 then
334 -- update the NI Number in correction mode
335 for emp_rec in csr_future_records('Y')
336 loop
337 l_obj_version := emp_rec.object_version_number;
338 l_employee_number := emp_rec.employee_number;
339 hr_person_api.update_person (
340 p_effective_date =>emp_rec.effective_start_date
341 ,p_datetrack_update_mode => 'CORRECTION'
342 ,p_person_id =>p_person_id
343 ,p_national_identifier => p_nino_to_use
344 ,p_object_version_number => l_obj_version
345 ,p_employee_number =>l_employee_number
346 ,p_effective_start_date=>p_effective_start_date
347 ,p_effective_end_date =>p_effective_end_date
348 ,p_full_name =>p_full_name
349 ,p_comment_id =>p_comment_id
350 ,p_name_combination_warning =>p_name_combination_warning
354 end loop;
351 ,p_assign_payroll_warning =>p_assign_payroll_warning
352 ,p_orig_hire_warning =>p_orig_hire_warning
353 );
355 end if;
356 elsif p_msg_ident = '4' then
357 -- no changes to Employee NI Number record.
358 -- Update all the EIT 's
359 l_update_status := update_extra_information(
360 f_person_id => p_person_id,
361 f_nino_verifed => 'Yes - HMRC',
362 f_date_of_issue => p_date_issued,
363 f_nvrep_status =>p_msg_ident,
364 f_nino_ver_type => p_nino_reply_type,
365 f_correlation_id => p_correlation_id);
366
367
368 elsif p_msg_ident ='3' then
369 -- NINO is verified Now. Update the EIT fields accordingly
370 l_update_status := update_extra_information(
371 f_person_id => p_person_id,
372 f_nino_verifed => 'Yes - HMRC',
373 f_date_of_issue => p_date_issued,
374 f_nvrep_status =>p_msg_ident,
375 f_nino_ver_type => p_nino_reply_type,
376 f_correlation_id => p_correlation_id);
377 fnd_file.put_line(fnd_file.LOG,'Update Status = ' || to_char(l_update_status));
378 if l_update_status <> -1 then
379 --update the Employee NI Number record in update_change_insert mode.
380 l_obj_version := p_object_version_number;
381 l_emp_number := p_employee_number;
382 open csr_future_records_count;
383 fetch csr_future_records_count into l_future_rec_count;
384 if l_future_rec_count > 0 then
385 l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
386 else
387 l_datetrack_update_mode := 'UPDATE';
388 end if;
389
390 close csr_future_records_count;
391
392 fnd_file.put_line(fnd_file.LOG,'l_datetrack_update_mode' || l_datetrack_update_mode);
393 hr_person_api.update_person (
394 p_effective_date =>p_date_issued
395 ,p_datetrack_update_mode => l_datetrack_update_mode
396 ,p_person_id =>p_person_id
397 ,p_national_identifier => p_nino_to_use
398 ,p_object_version_number => l_obj_version
399 ,p_employee_number => l_emp_number
400 ,p_effective_start_date=>p_effective_start_date
401 ,p_effective_end_date =>p_effective_end_date
402 ,p_full_name =>p_full_name
403 ,p_comment_id =>p_comment_id
404 ,p_name_combination_warning =>p_name_combination_warning
405 ,p_assign_payroll_warning =>p_assign_payroll_warning
406 ,p_orig_hire_warning =>p_orig_hire_warning
407 );
408 -- update the future records in correction mode
409 for emp_rec in csr_future_records('N')
410 loop
411 l_obj_version := emp_rec.object_version_number;
412 l_employee_number := emp_rec.employee_number;
413 hr_person_api.update_person (
414 p_effective_date =>emp_rec.effective_start_date
415 ,p_datetrack_update_mode => 'CORRECTION'
416 ,p_person_id =>p_person_id
417 ,p_national_identifier => p_nino_to_use
418 ,p_object_version_number => l_obj_version
419 ,p_employee_number =>l_employee_number
420 ,p_effective_start_date=>p_effective_start_date
421 ,p_effective_end_date =>p_effective_end_date
422 ,p_full_name =>p_full_name
423 ,p_comment_id =>p_comment_id
424 ,p_name_combination_warning =>p_name_combination_warning
425 ,p_assign_payroll_warning =>p_assign_payroll_warning
426 ,p_orig_hire_warning =>p_orig_hire_warning
427 );
428 fnd_file.put_line(fnd_file.LOG,'Updated future record for person_id = ' ||to_char(p_person_id));
429 fnd_file.put_line(fnd_file.LOG,' Effective start date = ' ||to_char(p_effective_start_date));
430 fnd_file.put_line(fnd_file.LOG,' Effective end date = ' ||to_char(p_effective_end_date));
431 fnd_file.put_line(fnd_file.LOG,' New NI Number = ' || p_nino_to_use);
432 end loop;
433
434 end if;
435 -- Update all the EIT's.
436 elsif p_msg_ident = '5' then
437 -- update only the EIT. Update only nvrep status alone
438 l_update_status := update_extra_information(
439 f_person_id => p_person_id,
440 f_nvrep_status =>p_msg_ident,
441 f_nino_ver_type => p_nino_reply_type);
442 end if;
443 fnd_file.put_line(fnd_file.LOG,'Update Status before leaving= ' || to_char(l_update_status));
444 fnd_file.put_line(fnd_file.LOG,'Leaving ' || l_package_name || '.update_emp_record');
445 if l_update_status <> -1 then
446 return true;
447 else
448 return false;
449 end if;
450 end update_emp_record;
451
452 procedure get_emp_details(
453 p_business_group_id number,
454 p_person_id OUT NOCOPY number,
455 p_start_date OUT NOCOPY date,
456 p_end_date OUT NOCOPY date,
457 p_nino_provided varchar2,
458 p_last_name varchar2,
459 p_first_name varchar2,
460 p_payroll_id varchar2,
461 p_date_issued date,
462 p_recent_start_date OUT NOCOPY date,
463 p_employee_number OUT NOCOPY varchar2,
464 p_object_version_number OUT NOCOPY number,
465 p_emp_count OUT NOCOPY number
466 ) is
467 cursor csr_emp_details is
468 select pap.person_id,pap.start_date, pap.effective_end_date,
469 pap.employee_number, pap.object_version_number from
470 per_all_people_f pap where
471 pap.business_group_id = p_business_group_id
472 and nvl(pap.national_identifier,'1') = nvl(p_nino_provided,'1')
473 and trim(upper(pap.last_name)) = trim(upper(p_last_name))
474 and trim(upper(pap.first_name)) = trim(upper(p_first_name))
475 --and trim(upper(pap.employee_number)) = trim(upper(p_payroll_id))
476 and p_date_issued between pap.effective_start_date and pap.effective_end_date
477 and pap.current_employee_flag = 'Y' -- NINO Should not uplaoded for terminated employee
478 -- Payroll id if present should match with one of the payroll id of the assignments for this person
479 and ( p_payroll_id is null
480 OR
481 exists (
482 select 1 from per_all_assignments_f paaf ,
486 and paaf.assignment_id = paei.assignment_id
483 per_assignment_extra_info paei where
484 paaf.person_id = pap.person_id
485 and p_date_issued between paaf.effective_start_date and paaf.effective_end_date
487 and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
488 and paei.AEI_INFORMATION3 = p_payroll_id
489 )
490 )
491 order by 2,3;
492
493 begin
494 fnd_file.put_line(fnd_file.LOG,'Entering: ' || l_package_name || '.get_emp_details');
495 p_emp_count := 0;
496 FOR CUR_REC in csr_emp_details
497 loop
498 p_emp_count := p_emp_count + 1;
499 if p_emp_count > 1 then
500 exit;
501 end if;
502 p_person_id := CUR_REC.person_id;
503 p_start_date := CUR_REC.start_date;
504 p_end_date := CUR_REC.effective_end_date;
505 p_employee_number := CUR_REC.employee_number;
506 p_object_version_number := CUR_REC.object_version_number;
507 end loop;
508 select max(date_start) into p_recent_start_date from per_periods_of_service where person_id = p_person_id;
509
510 fnd_file.put_line(fnd_file.LOG,'Leaving: ' || l_package_name || '.get_emp_details');
511 end get_emp_details;
512
513 begin
514 -- hr_utility.trace_on(null,'suma');
515 fnd_file.put_line(fnd_file.LOG,'PAY_GB_RTI_NINO_REPLY_PKG.read_nino_reply');
516 fnd_file.put_line(fnd_file.LOG,'Parameter values:');
517 fnd_file.put_line(fnd_file.LOG,'p_business_group: '|| to_char(p_business_group)
518 || fnd_global.local_chr(10) || 'p_validate_mode: ' || p_validate_mode
519 || fnd_global.local_chr(10) || 'p_filename: ' || p_filename);
520 --
521 l_filename := p_filename;
522 --
523 -- Get I/O Directory
524 --
525 fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
526 -- Get request id
527 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
528
529 fnd_file.put_line(fnd_file.LOG,'directory: <'|| l_location || '>');
530 if l_location is null then
531 -- error : I/O directory not defined
532 retcode := 2;
533 errbuf := 'Input directory not defined. Set PER_DATA_EXCHANGE_DIR profile (HR: Data Exchange directory).';
534 fnd_file.put_line(fnd_file.LOG,'Input directory not defined in PER_DATA_EXCHANGE_DIR profile.');
535 raise e_fatal_error;
536 end if;
537 --
538 -- Open flat file
539 l_file_handle := utl_file.fopen(l_location,l_filename,'r');
540 --l_file_handle := utl_file.fopen('/usr/tmp','Reply.mf','r');
541 --
542 utl_file.get_line(l_file_handle,l_present_line);
543
544 fnd_file.put_line(fnd_file.LOG,'PAY_GB_RTI_NINO_REPLY_PKG.read_nino_reply');
545
546 fnd_file.put_line(fnd_file.LOG,'line: '|| l_present_line);
547
548 report_output_success(success) := 'The following employees have been updated with NINO received: ';
549 success := success +1;
550 report_output_success(success) := '-------------------------------------------------------------';
551 success := success +1;
552 report_output_success(success) := 'Employee No. First Name Last Name NINO Provided NINO To Use Message Identifier Reply Type Issue Date ';
553 success := success +1;
554 report_output_success(success) := '------------ ------------ ----------- --------------- ------------- ------------------ ---------- -----------';
555 report_output_warnings(warnings) := 'Employees processed with warnings:';
556 warnings := warnings + 1;
557 report_output_warnings(warnings) := '----------------------------------';
558 if l_present_line = null then
559 l_processing := false;
560 end if;
561
562 while l_processing loop
563 /* Check to be sure that the process is reading the correct data.
564 Will exit if some corrupted data is found instead of scanning till the end of file.
565 **/
566 l_loop_count := l_loop_count + 1;
567 if l_loop_count > (l_employee_count*10 +15) then
568 exit;
569 end if;
570 -- Reading the header section for the first time
571 l_record_name := rtrim(ltrim(substr(l_present_line, 1,5)));
572 l_qualifier := trim(substr(l_present_line,7,3));
573 fnd_file.put_line(fnd_file.LOG,'l_present_line is <' || l_present_line || '>');
574 fnd_file.put_line(fnd_file.LOG,'l_rec_name is <' || l_record_name || '>');
575 fnd_file.put_line(fnd_file.LOG,'l_quali is <' || l_qualifier || '>');
576
577
578
579 -- if the record is header section, get the details of the header section
580 if l_record_name = '****0' then
581 l_unique_reference := substr(l_present_line,108,14);
582 l_test_indicator := substr(l_present_line,92,1);
583 if l_test_indicator = '1' then
584 header := header + 1;
585 report_output_header(header) := 'This is a test submission file.';
586 else
587 header := header + 1;
588 report_output_header(header) := 'This is a live submission file.';
589 end if;
590 elsif l_record_name = 'BGM1' then
591 if l_qualifier = '28' then
592 l_form_type := 'NVREP';
593 else
594 fnd_file.put_line(fnd_file.LOG,'Form Type is not NVREP');
595 retcode:=2;
596 errbuf := 'Form Type is not NVREP.';
597 raise e_fatal_error;
598 end if;
599 elsif l_record_name = 'NAD2B' then -- Employee first name and last name
600 l_last_name := rtrim(ltrim(substr(l_present_line,7,35)));
601 l_first_name := rtrim(ltrim(substr(l_present_line,43,35)));
602 elsif l_record_name = 'ATT1' and l_qualifier = '7' then -- PAYE Reference
603 l_employer_paye_ref := rtrim(ltrim(substr(l_present_line,11,35)));
604 header := header + 1;
605 report_output_header(header) := 'Employer PAYE Reference: ' || l_employer_paye_ref;
606 elsif l_record_name = 'ATT1' and l_qualifier = '17' then
607 l_hmrc_off_number := rtrim(ltrim(substr(l_present_line,11,35)));
611 l_employer_paye_ref := l_hmrc_off_number || '/' || l_employer_paye_ref;
608 header := header + 1;
609 report_output_header(header) := 'HMRC Office Number: ' || to_char(l_hmrc_off_number);
610 -- emp payeref is combination of tax district and tax ref
612 -- if the paye reference is not there within the business group then reject the file.
613 if validate_paye_ref(l_employer_paye_ref, p_business_group) then
614 raise e_invalid_paye_ref;
615 end if;
616 elsif l_record_name = 'DTM1' and l_qualifier = '243' then -- Date Of Message
617 l_date_of_msg := rtrim(ltrim(substr(l_present_line,11,35)));
618 l_date_of_msg_format := rtrim(ltrim(substr(l_present_line,47,3)));
619 if l_date_of_msg_format = '102' then
620 l_date_of_msg_format := 'YYYYMMDD';
621 end if;
622 header := header + 1;
623 report_output_header(header) := 'Date Of Message Received: ' || fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_date_of_msg));
624 elsif l_record_name = 'LIN1' then
625 l_employee_count := l_employee_count + 1;
626 l_employee_number := rtrim(ltrim(substr(l_present_line,7,6)));
627 elsif l_record_name = 'ATT2' and l_qualifier = '222' then -- NINO verification / Reply value
628 l_nino_reply_type := rtrim(ltrim(substr(l_present_line,11,35)));
629 elsif l_record_name = 'ATT2' and l_qualifier = '11' then -- NI Number provided
630 l_nino_provided := rtrim(ltrim(substr(l_present_line,11,35)));
631 elsif l_record_name = 'ATT2' and l_qualifier = '225' then -- NI Number provided
632 l_nino_to_use := rtrim(ltrim(substr(l_present_line,11,35)));
633 elsif l_record_name = 'ATT2' and l_qualifier = '19' then -- Payroll id
634 l_payroll_id := rtrim(ltrim(substr(l_present_line,11,35)));
635 elsif l_record_name = 'ATT2' and l_qualifier = '223' then -- Message identifier
636 l_msg_ident := rtrim(ltrim(substr(l_present_line,11,35)));
637 elsif l_record_name = 'ATT2' and l_qualifier = '35' then -- Correlation id
638 l_correlation_id := rtrim(ltrim(substr(l_present_line,11,35)));
639 elsif l_record_name = 'DTM2' and l_qualifier = '182' then -- Date of Issue
640 l_date_issued := trim(substr(l_present_line,11,35));
641 l_date_issued_format := rtrim(ltrim(substr(l_present_line,47,3)));
642 if l_date_issued_format = '102' then
643 l_date_issued_format := 'YYYYMMDD';
644 end if;
645 end if;
646 -- writing into the database
647 if ((l_record_name = 'LIN1' and (l_employee_count > 1)) or
648 (l_record_name = 'UNS2')) then
649 fnd_file.put_line(fnd_file.LOG,'p_business_group:' || to_number(p_business_group)
650 || fnd_global.local_chr(10) || 'l_employer_paye_ref: ' || l_employer_paye_ref
651 || fnd_global.local_chr(10) || 'l_employee_number: ' || l_employee_number
652 || fnd_global.local_chr(10) || 'l_last_name: ' || l_last_name
653 || fnd_global.local_chr(10) || 'l_first_name: ' || l_first_name
654 || fnd_global.local_chr(10) || 'l_nino_provided: ' || l_nino_provided
655 || fnd_global.local_chr(10) || 'l_payroll_id: ' || l_payroll_id
656 || fnd_global.local_chr(10) || 'l_correlation_id: ' || l_correlation_id
657 || fnd_global.local_chr(10) || 'l_nino_to_use: ' || l_nino_to_use
658 || fnd_global.local_chr(10) || 'l_date_issued: ' ||l_date_issued
659 || fnd_global.local_chr(10)|| 'l_msg_ident: ' || l_msg_ident
660 || fnd_global.local_chr(10) || 'l_nino_reply_type: ' ||l_nino_reply_type
661 || fnd_global.local_chr(10) || 'l_date_issued_format ' || l_date_issued_format);
662
663
664 -- fetch person_id, start date and end date based on NI Number , first name and last name
665 get_emp_details(
666 p_business_group_id => p_business_group,
667 p_person_id =>l_person_id,
668 p_start_date =>l_start_date,
669 p_end_date =>l_end_date,
670 p_nino_provided =>l_nino_provided,
671 p_last_name =>l_last_name,
672 p_first_name =>l_first_name,
673 p_payroll_id =>l_payroll_id,
674 p_date_issued =>to_date(l_date_issued, l_date_issued_format),
675 p_recent_start_date =>l_recent_start_date,
676 p_object_version_number =>l_obj_ver,
677 p_employee_number =>l_emp_number,
678 p_emp_count => l_emp_count
679 );
680 fnd_file.put_line(fnd_file.LOG,'person_id ' || to_char(l_person_id) );
681 fnd_file.put_line(fnd_file.LOG,'person_count' || to_char(l_emp_count) );
682 -- Update the records of the employees found .
683 if (l_person_id is not null) then
684 fnd_file.put_line(fnd_file.LOG,'Employee found for NI Number ' || l_nino_provided);
685 fnd_file.put_line(fnd_file.LOG,'person_id ' || to_char(l_person_id)
686 || fnd_global.local_chr(10) || 'start_date' || to_char(l_start_date)
687 || fnd_global.local_chr(10) || 'end_date' || to_char(l_end_date)
688 || fnd_global.local_chr(10) || 'recent_start_date ' || to_char(l_recent_start_date)
689 || fnd_global.local_chr(10) || 'employee_number ' || l_employee_number );
690 if l_emp_count = 1 then
691 l_updated_flag := update_emp_record(
692 p_person_id =>l_person_id,
693 p_start_date =>l_start_date,
694 p_end_date =>l_end_date,
695 p_nino_to_use =>l_nino_to_use,
696 p_date_issued =>to_date(l_date_issued,l_date_issued_format),
697 p_msg_ident =>l_msg_ident,
698 p_nino_reply_type =>l_nino_reply_type,
699 p_object_version_number =>l_obj_ver,
700 p_employee_number =>l_emp_number,
701 p_recent_start_date =>l_recent_start_date,
702 p_correlation_id => l_correlation_id
703 );
704 if l_updated_flag then
705 l_processed_success := l_processed_success + 1;
706 success := success+1;
707 report_output_success(success) := rpad(l_emp_number,15) || ' ' || rpad(l_first_name,20) || ' ' || rpad(l_last_name,18) || ' ' || rpad(nvl(l_nino_provided,' '),16) || ' '
711 else
708 || rpad(nvl(l_nino_to_use,' '),14) || ' ' || rpad(l_msg_ident,18)|| ' ' || rpad(l_nino_reply_type,10)|| ' ' || fnd_date.date_to_displaydate(fnd_date.canonical_to_date(l_date_issued));
709 l_updated_flag := false;
710 end if;
712 errbuf := 'Multiple records present for the matching criteria <Name: ' || l_last_name || ' ' || l_first_name || ', NINO:' || l_nino_provided ||
713 ', Employee Number:- ' || l_payroll_id || ' >. NINO not uploaded, check and enter manually.';
714 raise e_multiple_emp_err;
715 end if;
716 else
717 l_processed_warnings := l_processed_warnings + 1;
718 warnings := warnings + 1;
719 retcode := 1;
720 report_output_warnings(warnings) := 'Employee not Found/Terminated for - NI Number:- ' || l_nino_provided || ' - Name:- ' || l_last_name || ' ' || l_first_name
721 || ' Employee Number:- ' || l_payroll_id || ' NI Number to user:- ' || l_nino_to_use;
722 fnd_file.put_line(fnd_file.LOG, 'Employee Not found for NI Number ' || l_nino_provided || ' Name ' || l_last_name || ' ' || l_first_name
723 || ' Employee Number:- ' || l_payroll_id || ' NI Number to user:- ' || l_nino_to_use);
724 end if;
725
726 -- re initialising the local variables.
727 l_nino_provided := null;
728 l_nino_to_use := null;
729 l_date_issued := null;
730 l_msg_ident := null;
731 l_payroll_id := null;
732 l_nino_reply_type := null;
733 l_person_id := null;
734 l_start_date := null;
735 l_end_date := null;
736 l_last_name :=null;
737 l_first_name := null;
738 l_obj_ver := null;
739 l_emp_number :=null;
740 l_recent_start_date :=null;
741 l_correlation_id := null;
742 l_emp_count := null;
743 end if;
744
745 -- read next line
746 --
747 begin
748 utl_file.get_line(l_file_handle,l_present_line);
749 --
750 fnd_file.put_line(fnd_file.LOG,'PAY_GB_RTI_NINO_REPLY_PKG.read_nino_reply');
751 fnd_file.put_line(fnd_file.LOG,'line: '|| l_present_line);
752 --
753 exception
754 when no_data_found then
755 l_processing := false;
756 utl_file.fclose(l_file_handle);
757 EXIT;
758 when others then
759 utl_file.fclose(l_file_handle);
760 end; -- end inner block
761 end loop; -- end l_processing loop as we reached end of file.
762
763 if p_validate_mode = 'Validate Only' then
764 rollback;
765 fnd_file.put_line(FND_FILE.OUTPUT,'Following details are only validated.');
766 elsif p_validate_mode = 'Validate and Commit - Live' then
767 if l_test_indicator = '1' then
768 rollback;
769 retcode := 1;
770 fnd_file.put_line(FND_FILE.OUTPUT,'Warning: File requested for processing is a test file. Process will not update records.');
771 else
772 commit;
773 fnd_file.put_line(FND_FILE.OUTPUT,'Following details are validated and saved in the database.');
774 end if;
775 elsif p_validate_mode = 'Validate and Commit - Always' then
776 commit;
777 fnd_file.put_line(FND_FILE.OUTPUT,'Following details are validated and saved in the database.');
778 end if;
779 -- Printing the header
780 FOR i IN 1..report_output_header.count
781 LOOP
782 fnd_file.put_line(FND_FILE.OUTPUT,report_output_header(i));
783 fnd_file.put_line(fnd_file.LOG,report_output_header(i));
784 END LOOP;
785 fnd_file.put_line(FND_FILE.OUTPUT,' ');
786 fnd_file.put_line(FND_FILE.OUTPUT,' ');
787 fnd_file.put_line(fnd_file.LOG,rpad(' ',70, '-'));
788
789 -- printing the already processed employee details
790 FOR i IN 1..report_output_already_process.count
791 LOOP
792 fnd_file.put_line(FND_FILE.OUTPUT,report_output_already_process(i));
793 END LOOP;
794 fnd_file.put_line(FND_FILE.OUTPUT,' ');
795 fnd_file.put_line(FND_FILE.OUTPUT,'Total Number of employees with NINO previously verified: ' || to_char(l_processed_already) );
796 fnd_file.put_line(FND_FILE.OUTPUT,' ');
797 --Prinitng the Success part
798 FOR i IN 1..report_output_success.count
799 LOOP
800 fnd_file.put_line(FND_FILE.OUTPUT,report_output_success(i));
801 fnd_file.put_line(fnd_file.LOG,report_output_success(i));
802 END LOOP;
803 fnd_file.put_line(FND_FILE.OUTPUT, fnd_global.local_chr(10) || 'Total number of employees processed successfully:' || to_char(l_processed_success));
804 fnd_file.put_line(fnd_file.LOG,'No Of employees updated with NINO: ' || to_char(l_processed_success));
805 fnd_file.put_line(FND_FILE.OUTPUT,' ');
806 fnd_file.put_line(FND_FILE.OUTPUT,' ');
807 -- Printing the warnings part
808 FOR i IN 1..report_output_warnings.count
809 LOOP
810 fnd_file.put_line(FND_FILE.OUTPUT,report_output_warnings(i));
811 fnd_file.put_line(fnd_file.LOG,report_output_warnings(i));
812 END LOOP;
813 fnd_file.put_line(FND_FILE.OUTPUT, fnd_global.local_chr(10) || 'Total number of employees processed with Warnings:' || to_char(l_processed_warnings));
814 fnd_file.put_line(fnd_file.LOG,'No Of employees processed with warnings:' || to_char(warnings -1));
815
816 fnd_file.put_line(FND_FILE.OUTPUT,fnd_global.local_chr(10) || 'Total number of employees in the file: ' || to_char(l_employee_count));
817 fnd_file.put_line(fnd_file.LOG, 'Total No of employees in the file: ' || to_char(l_employee_count));
818
819 exception
820 when e_invalid_paye_ref then
821 fnd_file.put_line(fnd_file.LOG,'No match found on database for PAYE Ref <'|| l_employer_paye_ref ||'>');
822 retcode := 2;
823 errbuf := 'No match found in the database for PAYE Ref <'|| l_employer_paye_ref ||'>.';
824 utl_file.fclose(l_file_handle);
825 rollback;
826 when e_multiple_emp_err then
827 UTL_FILE.FCLOSE(l_file_handle);
828 fnd_file.put_line(fnd_file.LOG,'Multiple employees matched for < ' || l_last_name || ' ' || l_first_name || '> ');
829 retcode := 2;
830 rollback;
831 when e_fatal_error then
832 UTL_FILE.FCLOSE(l_file_handle);
833 fnd_file.put_line(fnd_file.LOG,'Fatal Error');
834 rollback;
835 when UTL_FILE.INVALID_OPERATION then
836 UTL_FILE.FCLOSE(l_file_handle);
837 fnd_file.put_line(fnd_file.LOG,'PAY_GB_RTI_NINO_REPLY_PKG.read_nino_reply');
838 retcode:=2;
839 errbuf := 'Reading NINO Reply File - Invalid Operation (file not found).';
840 when UTL_FILE.INTERNAL_ERROR then
841 UTL_FILE.FCLOSE(l_file_handle);
842 fnd_file.put_line(fnd_file.LOG,'PAY_GB_RTI_NINO_REPLY_PKG.read_nino_reply');
843 retcode:=2;
844 errbuf := 'Reading NINO Reply File - Internal Error.';
845 when UTL_FILE.INVALID_MODE then
846 UTL_FILE.FCLOSE(l_file_handle);
847 fnd_file.put_line(fnd_file.LOG,'PAY_GB_RTI_NINO_REPLY_PKG.read_nino_reply');
848 retcode:=2;
849 errbuf := 'Reading NINO Reply File - Invalid Mode.';
850 when UTL_FILE.INVALID_PATH then
851 UTL_FILE.FCLOSE(l_file_handle);
852 fnd_file.put_line(fnd_file.LOG,'PAY_GB_RTI_NINO_REPLY_PKG.read_nino_reply');
853 retcode:=2;
854 errbuf := 'Reading NINO Reply File - Invalid Path.';
855 when UTL_FILE.INVALID_FILEHANDLE then
856 UTL_FILE.FCLOSE(l_file_handle);
857 fnd_file.put_line(fnd_file.LOG,'PAY_GB_RTI_NINO_REPLY_PKG.read_nino_reply');
858 retcode:=2;
859 errbuf := 'Reading NINO Reply File - Invalid File Handle.';
860 when UTL_FILE.READ_ERROR then
861 UTL_FILE.FCLOSE(l_file_handle);
862 fnd_file.put_line(fnd_file.LOG,'PAY_GB_RTI_NINO_REPLY_PKG.read_nino_reply');
863 retcode:=2;
864 errbuf := 'Reading NINO Reply File - Read Error.';
865
866 when others then
867 retcode := 2;
868 fnd_file.put_line(fnd_file.LOG,'Exception : ' || SQLERRM || ' ' || sqlcode);
869 fnd_file.put_line(fnd_file.LOG,DBMS_UTILITY.format_error_backtrace);
870 retcode := 2;
871 utl_file.fclose(l_file_handle);
872 rollback;
873
874 end read_nino_reply;
875 end;