APPS.PAY_GB_PRE_RTI_NINO SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 3

  /* This package updates the NINO Verified field to 'Yes - Pre RTI'.
  **/
  -- -------------------------------------------------------------------------------------------
  -- The main update.
  -- -------------------------------------------------------------------------------------------
l_package_name varchar2(30) := 'pay_gb_pre_rti_nino';

Line: 13

function update_extra_information(
f_person_id number,
f_nino_verifed varchar2 default 'No',
f_date_of_issue date default null,
f_emp_num varchar2,
f_paye_ref varchar2,
f_payroll varchar2,
f_nino varchar2
) return number is
l_extra_info_id number := null;

Line: 26

select PERSON_EXTRA_INFO_ID,object_version_number,pei_information1 from per_people_extra_info where PERSON_ID = f_person_id
and pei_information_category = 'RTI_NINO';

Line: 29

fnd_file.put_line(fnd_file.LOG,'Entering ' ||l_package_name || '.update_extra_information');

Line: 43

			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_object_version_number => l_object_version_number
			);

Line: 56

	-- insert a new record
	hr_person_extra_info_api.create_person_extra_info(
	p_person_id =>f_person_id,
	p_information_type => 'RTI_NINO',
	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_object_version_number => l_object_version_number,
	p_person_extra_info_id => l_extra_info_id
	);

Line: 67

fnd_file.put_line(fnd_file.LOG,'Leaving ' ||l_package_name || '.update_extra_information');

Line: 76

end update_extra_information;

Line: 78

PROCEDURE update_rti_nino(
  errbuf OUT NOCOPY VARCHAR2,
  retcode OUT NOCOPY NUMBER,
  p_bg_id IN NUMBER,
	p_paye_ref in varchar2,
  p_effective_date date,
	p_payroll_id in number
  )
IS
p_package_name varchar2(25) := 'pay_gb_pre_rti_nino';

Line: 95

	Cursor retrieves all the employees that are to be updated for a given
	business group, paye reference and payroll.
*/
   CURSOR csr_get_employees
  IS
        SELECT  papf.person_id
               ,paaf.assignment_id
               ,papf.national_identifier
               ,papf.full_name
               ,org.org_information1 as paye_reference
               ,ppf.payroll_id
			   ,papf.employee_number
			   ,ppf.payroll_name
        FROM    per_all_assignments_f paaf
               ,per_all_people_f papf
               ,pay_payrolls_f ppf
               ,hr_organization_information org
               ,hr_soft_coding_keyflex flex
        WHERE   org.organization_id = paaf.business_group_id
        AND     papf.business_group_id = p_bg_id
        AND     paaf.business_group_id = papf.business_group_id
        AND     nvl (org.org_information10
                    ,'UK') = 'UK'
        AND     org.org_information_context = 'Tax Details References'
        AND     org.org_information1 = flex.segment1
        AND     org.org_information1 = nvl (p_paye_ref
                                           ,org.org_information1)
        AND     ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
        AND     ppf.payroll_id = nvl (p_payroll_id
                                     ,ppf.payroll_id)
        AND     paaf.payroll_id = ppf.payroll_id

        AND     papf.person_id = paaf.person_id

        AND     p_effective_date  BETWEEN ppf.effective_start_date
                                        AND     ppf.effective_end_date
			  AND     papf.CURRENT_EMPLOYEE_FLAG = 'Y'
        AND     p_effective_date BETWEEN paaf.effective_start_date
                                        AND paaf.effective_end_date
        AND     paaf.PRIMARY_FLAG = 'Y'

        AND     p_effective_date BETWEEN papf.effective_start_date
                                        AND     papf.effective_end_date;

Line: 140

	  fnd_file.put_line(fnd_file.LOG,'Entering ' || p_package_name || '.update_rti_nino');

Line: 150

				if update_extra_information(l_emp_rec.person_id,'Yes - Pre RTI',p_effective_date ,l_emp_rec.employee_number,l_emp_rec.paye_reference,l_emp_rec.payroll_name,l_emp_rec.national_identifier) <> -1 then
					fnd_file.put_line(fnd_file.LOG,'NI Number Updated for Employee id ' || l_emp_rec.employee_number || ' Paye reference <' || l_emp_rec.paye_reference ||'>');

Line: 173

	fnd_file.put_line(fnd_file.output,'No Of employees updated with NINO Flag: ' || to_char(success));

Line: 175

	fnd_file.put_line(fnd_file.output, 'NINO verified flag is not updated for the following employees as no NI Number exists for them: ');

Line: 184

 fnd_file.put_line(fnd_file.output,'No Of employees not updated: ' || to_char(warnings));

Line: 190

		fnd_file.put_line(fnd_file.output, 'The following employees have not been updated as NINO previously verified: ');

Line: 198

		fnd_file.put_line(fnd_file.output,'No Of employees not updated: ' || to_char(already_verified));

Line: 206

 fnd_file.put_line(fnd_file.LOG,'Leaving ' || p_package_name || '.update_rti_nino');

Line: 219

END update_rti_nino;