[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
and updates the changes in the system.
**/
-- -------------------------------------------------------------------------------------------
-- The main update.
-- -------------------------------------------------------------------------------------------
l_last_name varchar2(35);
l_updated_flag boolean := false;
select 'Y' into l_exists from dual where regexp_like(trim(p_correlation_ids),'[,]*\s*'||f_correlation_id|| '$') OR
regexp_like(trim(p_correlation_ids),',\s*' || f_correlation_id || '\s*,') OR regexp_like(trim(p_correlation_ids),'^' || f_correlation_id || '\s*[,]*') ;
select 1 from hr_organization_information where organization_id = p_business_group
and org_information1 = l_employer_paye_ref
and org_information_context = 'Tax Details References';
function update_extra_information(
f_person_id number,
f_nino_verifed varchar2 default 'No',
f_date_of_issue date default null,
f_nvrep_status varchar2 default null,
f_nino_ver_type varchar2 default null,
f_correlation_id varchar default null
) return number is
l_extra_info_id number := null;
select PERSON_EXTRA_INFO_ID,object_version_number,pei_information1, pei_information5
from per_people_extra_info where PERSON_ID = f_person_id
and pei_information_category = 'RTI_NINO';
fnd_file.put_line(fnd_file.LOG,'Entering ' ||l_package_name || '.update_extra_information');
/*select PERSON_EXTRA_INFO_ID,object_version_number,pei_information1, pei_information5 into
l_extra_info_id, l_object_version_number, l_nino_verified_flag,l_correlation_id
from per_people_extra_info where PERSON_ID = f_person_id
and pei_information_category = 'RTI_NINO';
-- Update the extra info using update API only when the RTI Verified is not already verified
if l_nino_verified_flag is null OR l_nino_verified_flag = 'No' then
/* Process the employee if
1. Correlation id on the reply file matches the correlation id stamped on the employee.
2. Correlation id is null in reply file due to notification from EAS/FPS and no correlation id is stamped on employee.
*/
if nvl(l_correlation_id,' ') = nvl(f_correlation_id,' ') then
fnd_file.put_line(fnd_file.LOG,'Updating the person EIT record');
hr_person_extra_info_api.update_person_extra_info(
p_person_extra_info_id => l_extra_info_id,
p_pei_information_category => 'RTI_NINO',
p_pei_information1 => f_nino_verifed,
p_pei_information2 => fnd_date.date_to_canonical(f_date_of_issue),
p_pei_information3 => f_nvrep_status,
p_pei_information4 => f_nino_ver_type,
p_object_version_number => l_object_version_number
);
'> - 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.';
elsif f_correlation_id is null then -- insert a new record, this should happen for only Notification status where correlation id is empty.
-- insert a new record.
fnd_file.put_line(fnd_file.LOG,'Inserting the person EIT record');
'> - 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';
fnd_file.put_line(fnd_file.LOG,'Leaving ' ||l_package_name || '.update_extra_information');
end update_extra_information;
function update_emp_record(
p_person_id number,
p_start_date date ,
p_end_date date,
p_nino_to_use varchar2,
p_date_issued date ,
p_msg_ident varchar2,
p_nino_reply_type varchar2,
p_object_version_number number,
p_employee_number varchar2,
p_recent_start_date date,
p_correlation_id varchar2
) return boolean is
p_effective_start_date date;
l_datetrack_update_mode varchar2(25);
select count(*) from
per_all_people_f pap where
pap.person_id = p_person_id
and pap.effective_start_date >= p_recent_start_date
and pap.effective_start_date > p_date_issued;
select pap.employee_number,pap.object_version_number, pap.effective_start_date from
per_all_people_f pap where
pap.person_id = p_person_id
and pap.effective_start_date >= p_recent_start_date
and pap.effective_start_date > decode(c_ignore_date, 'Y', pap.effective_start_date-1,p_date_issued)
order by 3;
l_update_status number;
fnd_file.put_line(fnd_file.LOG,'Entering ' || l_package_name || '.update_emp_record');
l_update_status := update_extra_information(
f_person_id => p_person_id,
f_nvrep_status =>p_msg_ident,
f_nino_ver_type => p_nino_reply_type,
f_correlation_id => p_correlation_id);
if l_update_status <> -1 then
-- Make the NI Number Null for all the records.
for emp_rec in csr_future_records('Y')
loop
l_obj_version := emp_rec.object_version_number;
hr_person_api.update_person(
p_effective_date =>emp_rec.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id =>p_person_id
,p_national_identifier => null
,p_object_version_number => l_obj_version
,p_employee_number =>l_employee_number
,p_effective_start_date=>p_effective_start_date
,p_effective_end_date =>p_effective_end_date
,p_full_name =>p_full_name
,p_comment_id =>p_comment_id
,p_name_combination_warning =>p_name_combination_warning
,p_assign_payroll_warning =>p_assign_payroll_warning
,p_orig_hire_warning =>p_orig_hire_warning
);
l_update_status := update_extra_information(
f_person_id => p_person_id,
f_nino_verifed => 'Yes - HMRC',
f_date_of_issue => p_date_issued,
f_nvrep_status =>p_msg_ident,
f_nino_ver_type => p_nino_reply_type,
f_correlation_id => p_correlation_id);
if l_update_status <> -1 then
-- update the NI Number in correction mode
for emp_rec in csr_future_records('Y')
loop
l_obj_version := emp_rec.object_version_number;
hr_person_api.update_person (
p_effective_date =>emp_rec.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id =>p_person_id
,p_national_identifier => p_nino_to_use
,p_object_version_number => l_obj_version
,p_employee_number =>l_employee_number
,p_effective_start_date=>p_effective_start_date
,p_effective_end_date =>p_effective_end_date
,p_full_name =>p_full_name
,p_comment_id =>p_comment_id
,p_name_combination_warning =>p_name_combination_warning
,p_assign_payroll_warning =>p_assign_payroll_warning
,p_orig_hire_warning =>p_orig_hire_warning
);
l_update_status := update_extra_information(
f_person_id => p_person_id,
f_nino_verifed => 'Yes - HMRC',
f_date_of_issue => p_date_issued,
f_nvrep_status =>p_msg_ident,
f_nino_ver_type => p_nino_reply_type,
f_correlation_id => p_correlation_id);
l_update_status := update_extra_information(
f_person_id => p_person_id,
f_nino_verifed => 'Yes - HMRC',
f_date_of_issue => p_date_issued,
f_nvrep_status =>p_msg_ident,
f_nino_ver_type => p_nino_reply_type,
f_correlation_id => p_correlation_id);
fnd_file.put_line(fnd_file.LOG,'Update Status = ' || to_char(l_update_status));
if l_update_status <> -1 then
--update the Employee NI Number record in update_change_insert mode.
l_obj_version := p_object_version_number;
l_datetrack_update_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_update_mode := 'UPDATE';
fnd_file.put_line(fnd_file.LOG,'l_datetrack_update_mode' || l_datetrack_update_mode);
hr_person_api.update_person (
p_effective_date =>p_date_issued
,p_datetrack_update_mode => l_datetrack_update_mode
,p_person_id =>p_person_id
,p_national_identifier => p_nino_to_use
,p_object_version_number => l_obj_version
,p_employee_number => l_emp_number
,p_effective_start_date=>p_effective_start_date
,p_effective_end_date =>p_effective_end_date
,p_full_name =>p_full_name
,p_comment_id =>p_comment_id
,p_name_combination_warning =>p_name_combination_warning
,p_assign_payroll_warning =>p_assign_payroll_warning
,p_orig_hire_warning =>p_orig_hire_warning
);
hr_person_api.update_person (
p_effective_date =>emp_rec.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id =>p_person_id
,p_national_identifier => p_nino_to_use
,p_object_version_number => l_obj_version
,p_employee_number =>l_employee_number
,p_effective_start_date=>p_effective_start_date
,p_effective_end_date =>p_effective_end_date
,p_full_name =>p_full_name
,p_comment_id =>p_comment_id
,p_name_combination_warning =>p_name_combination_warning
,p_assign_payroll_warning =>p_assign_payroll_warning
,p_orig_hire_warning =>p_orig_hire_warning
);
fnd_file.put_line(fnd_file.LOG,'Updated future record for person_id = ' ||to_char(p_person_id));
l_update_status := update_extra_information(
f_person_id => p_person_id,
f_nvrep_status =>p_msg_ident,
f_nino_ver_type => p_nino_reply_type);
fnd_file.put_line(fnd_file.LOG,'Update Status before leaving= ' || to_char(l_update_status));
fnd_file.put_line(fnd_file.LOG,'Leaving ' || l_package_name || '.update_emp_record');
if l_update_status <> -1 then
return true;
end update_emp_record;
select pap.person_id,pap.start_date, pap.effective_end_date,
pap.employee_number, pap.object_version_number from
per_all_people_f pap where
pap.business_group_id = p_business_group_id
and nvl(pap.national_identifier,'1') = nvl(p_nino_provided,'1')
and trim(upper(pap.last_name)) = trim(upper(p_last_name))
and trim(upper(pap.first_name)) = trim(upper(p_first_name))
--and trim(upper(pap.employee_number)) = trim(upper(p_payroll_id))
and p_date_issued between pap.effective_start_date and pap.effective_end_date
and pap.current_employee_flag = 'Y' -- NINO Should not uplaoded for terminated employee
-- Payroll id if present should match with one of the payroll id of the assignments for this person
and ( p_payroll_id is null
OR
exists (
select 1 from per_all_assignments_f paaf ,
per_assignment_extra_info paei where
paaf.person_id = pap.person_id
and p_date_issued between paaf.effective_start_date and paaf.effective_end_date
and paaf.assignment_id = paei.assignment_id
and paei.AEI_INFORMATION_CATEGORY = 'GB_RTI_AGGREGATION'
and paei.AEI_INFORMATION3 = p_payroll_id
)
)
order by 2,3;
select max(date_start) into p_recent_start_date from per_periods_of_service where person_id = p_person_id;
report_output_success(success) := 'The following employees have been updated with NINO received: ';
-- Update the records of the employees found .
if (l_person_id is not null) then
fnd_file.put_line(fnd_file.LOG,'Employee found for NI Number ' || l_nino_provided);
l_updated_flag := update_emp_record(
p_person_id =>l_person_id,
p_start_date =>l_start_date,
p_end_date =>l_end_date,
p_nino_to_use =>l_nino_to_use,
p_date_issued =>to_date(l_date_issued,l_date_issued_format),
p_msg_ident =>l_msg_ident,
p_nino_reply_type =>l_nino_reply_type,
p_object_version_number =>l_obj_ver,
p_employee_number =>l_emp_number,
p_recent_start_date =>l_recent_start_date,
p_correlation_id => l_correlation_id
);
if l_updated_flag then
l_processed_success := l_processed_success + 1;
l_updated_flag := false;
fnd_file.put_line(FND_FILE.OUTPUT,'Warning: File requested for processing is a test file. Process will not update records.');
fnd_file.put_line(fnd_file.LOG,'No Of employees updated with NINO: ' || to_char(l_processed_success));