[Home] [Help]
PACKAGE BODY: APPS.PAY_GB_PRE_RTI_NINO
Source
1 PACKAGE BODY pay_gb_pre_rti_nino AS
2 /* $Header: pygbprertinino.pkb 120.7.12020000.1 2013/02/22 12:58:47 ssarap noship $ */
3 /* This package updates the NINO Verified field to 'Yes - Pre RTI'.
4 **/
5 -- -------------------------------------------------------------------------------------------
6 -- The main update.
7 -- -------------------------------------------------------------------------------------------
8 l_package_name varchar2(30) := 'pay_gb_pre_rti_nino';
9 already_verified number := 0;
10 application_error exception;
11 TYPE report_output_type IS TABLE OF varchar2(1000) INDEX BY BINARY_INTEGER;
12 report_output_already report_output_type;
13 function update_extra_information(
14 f_person_id number,
15 f_nino_verifed varchar2 default 'No',
16 f_date_of_issue date default null,
17 f_emp_num varchar2,
18 f_paye_ref varchar2,
19 f_payroll varchar2,
20 f_nino varchar2
21 ) return number is
22 l_extra_info_id number := null;
23 l_object_version_number number;
24 l_nino_verified_flag varchar2(15) := null;
25 cursor get_emp_extra_info is
26 select PERSON_EXTRA_INFO_ID,object_version_number,pei_information1 from per_people_extra_info where PERSON_ID = f_person_id
27 and pei_information_category = 'RTI_NINO';
28 begin
29 fnd_file.put_line(fnd_file.LOG,'Entering ' ||l_package_name || '.update_extra_information');
30 open get_emp_extra_info;
31 fetch get_emp_extra_info into l_extra_info_id, l_object_version_number, l_nino_verified_flag;
32 close get_emp_extra_info;
33 fnd_file.put_line(fnd_file.LOG,'f_person_id: ' ||to_char(f_person_id) ||
34 fnd_global.local_chr(10) || 'f_nino_verifed varchar2' || f_nino_verifed
35 ||fnd_global.local_chr(10) || to_char(f_date_of_issue)
36 ||fnd_global.local_chr(10) || 'l_extra_info_id: ' || to_char(l_extra_info_id)
37 ||fnd_global.local_chr(10) || 'l_object_version_number: ' || to_char(l_object_version_number));
38
39 -- Update the extra info record using update API only when the RTI_NINO record exists
40 if l_extra_info_id is not null then
41 -- Check if the NINO is verified already
42 if l_nino_verified_flag is null OR l_nino_verified_flag = 'No' then
43 hr_person_extra_info_api.update_person_extra_info(
44 p_person_extra_info_id => l_extra_info_id,
45 p_pei_information_category => 'RTI_NINO',
46 p_pei_information1 => f_nino_verifed,
47 p_pei_information2 => fnd_date.date_to_canonical(f_date_of_issue),
48 p_object_version_number => l_object_version_number
49 );
50 else
51 already_verified := already_verified + 1;
52 report_output_already(already_verified) := rpad(f_emp_num,20,' ') || ' ' || rpad(f_paye_ref,20,' ') || ' ' || rpad(f_payroll,20,' ') || ' ' || rpad(f_nino,20,' ');
53 return -1;
54 end if;
55 else
56 -- insert a new record
57 hr_person_extra_info_api.create_person_extra_info(
58 p_person_id =>f_person_id,
59 p_information_type => 'RTI_NINO',
60 p_pei_information_category => 'RTI_NINO',
61 p_pei_information1 => f_nino_verifed,
62 p_pei_information2 => fnd_date.date_to_canonical(f_date_of_issue),
63 p_object_version_number => l_object_version_number,
64 p_person_extra_info_id => l_extra_info_id
65 );
66 end if;
67 fnd_file.put_line(fnd_file.LOG,'Leaving ' ||l_package_name || '.update_extra_information');
68 return 1;
69 exception
70 when OTHERS then
71 fnd_file.put_line(fnd_file.LOG,DBMS_UTILITY.format_error_backtrace);
72 fnd_file.put_line(fnd_file.LOG,sqlcode ||' - ' || sqlerrm);
73 rollback;
74 raise application_error;
75
76 end update_extra_information;
77
78 PROCEDURE update_rti_nino(
79 errbuf OUT NOCOPY VARCHAR2,
80 retcode OUT NOCOPY NUMBER,
81 p_bg_id IN NUMBER,
82 p_paye_ref in varchar2,
83 p_effective_date date,
84 p_payroll_id in number
85 )
86 IS
87 p_package_name varchar2(25) := 'pay_gb_pre_rti_nino';
88 /*** Local variables ***/
89 success number := 0;
90 warnings number := 0;
91 report_output_warnings report_output_type;
92 report_output_success report_output_type;
93
94 /*
95 Cursor retrieves all the employees that are to be updated for a given
96 business group, paye reference and payroll.
97 */
98 CURSOR csr_get_employees
99 IS
100 SELECT papf.person_id
101 ,paaf.assignment_id
102 ,papf.national_identifier
103 ,papf.full_name
104 ,org.org_information1 as paye_reference
105 ,ppf.payroll_id
106 ,papf.employee_number
107 ,ppf.payroll_name
108 FROM per_all_assignments_f paaf
109 ,per_all_people_f papf
110 ,pay_payrolls_f ppf
111 ,hr_organization_information org
112 ,hr_soft_coding_keyflex flex
113 WHERE org.organization_id = paaf.business_group_id
114 AND papf.business_group_id = p_bg_id
115 AND paaf.business_group_id = papf.business_group_id
116 AND nvl (org.org_information10
117 ,'UK') = 'UK'
118 AND org.org_information_context = 'Tax Details References'
119 AND org.org_information1 = flex.segment1
120 AND org.org_information1 = nvl (p_paye_ref
121 ,org.org_information1)
122 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
123 AND ppf.payroll_id = nvl (p_payroll_id
124 ,ppf.payroll_id)
125 AND paaf.payroll_id = ppf.payroll_id
126
127 AND papf.person_id = paaf.person_id
128
129 AND p_effective_date BETWEEN ppf.effective_start_date
130 AND ppf.effective_end_date
131 AND papf.CURRENT_EMPLOYEE_FLAG = 'Y'
132 AND p_effective_date BETWEEN paaf.effective_start_date
133 AND paaf.effective_end_date
134 AND paaf.PRIMARY_FLAG = 'Y'
135
136 AND p_effective_date BETWEEN papf.effective_start_date
137 AND papf.effective_end_date;
138 BEGIN
139 --hr_utility.trace_on(null,'suma');
140 fnd_file.put_line(fnd_file.LOG,'Entering ' || p_package_name || '.update_rti_nino');
141 fnd_file.put_line(fnd_file.LOG,'Business Group ID = ' ||to_char(p_bg_id));
142 fnd_file.put_line(fnd_file.LOG,'PAYE Reference = ' ||p_paye_ref);
143 fnd_file.put_line(fnd_file.LOG,'Payroll ID = ' || to_char(p_payroll_id));
144 fnd_file.put_line(fnd_file.LOG,'p_effective_date = ' || p_effective_date);
145
146 FOR l_emp_rec in csr_get_employees
147 LOOP
148
149 if l_emp_rec.national_identifier is not null then
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
151 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 ||'>');
152 success := success + 1;
153 report_output_success(success) := rpad(l_emp_rec.employee_number,20,' ') || ' ' || rpad(l_emp_rec.paye_reference,20,' ') || ' ' || rpad(l_emp_rec.payroll_name,20,' ') || ' ' || rpad(l_emp_rec.national_identifier,20,' ');
154 end if;
155 else
156 fnd_file.put_line(fnd_file.LOG,'NI Number is not present for Employee id ' || l_emp_rec.employee_number || ' Paye reference <' || l_emp_rec.paye_reference ||'>');
157 warnings := warnings + 1;
158 report_output_warnings(warnings) := rpad(l_emp_rec.employee_number,20,' ') || ' ' || rpad(l_emp_rec.paye_reference,20,' ') || ' ' || rpad(l_emp_rec.payroll_name,20,' ') ;
159 end if;
160
161 END LOOP;
162 if success + warnings + already_verified > 0 then
163 --Display the report.
164 fnd_file.put_line(fnd_file.output, 'NINO verified flag is changed to ''Yes - Pre RTI'' for following employees: ');
165 fnd_file.put_line(fnd_file.output,' ');
166 fnd_file.put_line(fnd_file.output, rpad('Employee Number',20,' ') || ' ' || rpad('Paye Reference',20,' ') || ' ' || rpad('Payroll Name',20,' ') || ' ' || rpad('National Identifier',20,' '));
167 FOR i IN 1..report_output_success.count
168 LOOP
169 fnd_file.put_line(FND_FILE.OUTPUT,report_output_success(i));
170 END LOOP;
171
172 fnd_file.put_line(fnd_file.output,' ');
173 fnd_file.put_line(fnd_file.output,'No Of employees updated with NINO Flag: ' || to_char(success));
174 fnd_file.put_line(fnd_file.output,' ');
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: ');
176 fnd_file.put_line(fnd_file.output,' ');
177 fnd_file.put_line(fnd_file.output, rpad('Employee Number',20,' ') || ' ' || rpad('Paye Reference',20,' ') || ' ' || rpad('Payroll Name',20,' '));
178 FOR i IN 1..report_output_warnings.count
179 LOOP
180 fnd_file.put_line(FND_FILE.OUTPUT,report_output_warnings(i));
181 END LOOP;
182
183 fnd_file.put_line(fnd_file.output,' ');
184 fnd_file.put_line(fnd_file.output,'No Of employees not updated: ' || to_char(warnings));
185 --hr_utility.trace_off;
186
187
188 if already_verified > 0 then
189 fnd_file.put_line(fnd_file.output,' ');
190 fnd_file.put_line(fnd_file.output, 'The following employees have not been updated as NINO previously verified: ');
191 fnd_file.put_line(fnd_file.output, rpad('Employee Number',20,' ') || ' ' || rpad('Paye Reference',20,' ') || ' ' || rpad('Payroll Name',20,' ') || ' ' || rpad('National Identifier',20,' '));
192 FOR i IN 1..report_output_already.count
193 LOOP
194 fnd_file.put_line(FND_FILE.OUTPUT,report_output_already(i));
195 END LOOP;
196
197 fnd_file.put_line(fnd_file.output,' ');
198 fnd_file.put_line(fnd_file.output,'No Of employees not updated: ' || to_char(already_verified));
199 end if;
200 fnd_file.put_line(fnd_file.output,' ');
201 fnd_file.put_line(fnd_file.output,'Total Number Of Employees: ' || to_char(success + warnings + already_verified));
202 else
203 fnd_file.put_line(fnd_file.output,lpad(rpad('No Records Processed',50,'-'),100,'-'));
204 end if;
205
206 fnd_file.put_line(fnd_file.LOG,'Leaving ' || p_package_name || '.update_rti_nino');
207 commit;
208 EXCEPTION
209 WHEN OTHERS THEN
210 ROLLBACK;
211 fnd_file.put_line(FND_FILE.LOG,rpad('-',155,'-'));
212 fnd_file.put_line(FND_FILE.LOG,'Error Record :');
213 -- fnd_file.put_line(FND_FILE.LOG,report_output(report_output.LAST));
214 fnd_file.put_line(FND_FILE.LOG,rpad('-',155,'-'));
215 fnd_file.put_line(FND_FILE.LOG,SQLCODE||' - '||SQLERRM);
216 fnd_file.put_line(FND_FILE.LOG,' ');
217 fnd_file.put_line(FND_FILE.LOG,DBMS_UTILITY.format_error_backtrace);
218 RAISE_APPLICATION_ERROR(-20001, SQLCODE||' - '||SQLERRM);
219 END update_rti_nino;
220 END pay_gb_pre_rti_nino;