4 PROCEDURE update_ni_category_pension(
1 PACKAGE BODY pay_gb_nicat_upd AS
2 /* $Header: paygbnicatupd.pkb 120.4 2011/12/30 13:42:55 ssarap noship $ */
3
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY NUMBER,
7 p_paye_ref in varchar2,
8 p_bg_id IN NUMBER,
9 p_payroll_id in number,
10 p_f_category in varchar2,
11 p_g_category in varchar2,
12 p_s_category in varchar2,
13 p_mode in varchar2
14 )
15 IS
16 /*** Local variables ***/
17 l_ni_new_category varchar2(1) := ' ';
18 l_ni_new_pension varchar2(30) := ' ';
19 l_ni_new_pension_f varchar2(30) := ' ';
20 l_ni_new_pension_g varchar2(30) := ' ';
21 l_ni_new_pension_s varchar2(30) := ' ';
22 l_input_value_id_category number := -1;
23 l_input_value_id_pension number := -1;
24 l_effective_date date := to_date('06-04-2012','DD-MM-YYYY');
25 TYPE report_output_type IS TABLE OF varchar2(1000);
26 report_output report_output_type;
27 counter number := 1;
28 TYPE pen_basis_type IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(1);
29 pen_basis pen_basis_type;
30 -- Fetches the input value id of NI Category and Pension.
31 cursor c_get_input_value_ids
32 is
33
34 SELECT max (decode (pivf.name
35 ,'Category'
36 ,pivf.input_value_id
37 ,NULL)) input_category
38 ,max (decode (pivf.name
39 ,'Pension'
40 ,pivf.input_value_id
41 ,NULL)) input_pension
42 FROM pay_element_types_f petf
43 ,pay_input_values_f pivf
44 WHERE petf.element_name = 'NI'
45 AND petf.legislation_code = 'GB'
46 AND petf.element_type_id = pivf.element_type_id
47 AND pivf.name IN ('Category','Pension')
48 AND SYSDATE BETWEEN petf.effective_start_date
49 AND pivf.effective_end_date
50 AND SYSDATE BETWEEN pivf.effective_start_date
51 AND pivf.effective_end_date;
52
53
54 /*
55 Cursor retrieves all the element entries that are to be updated for a given
56 business group, paye reference and payroll.
57 Retrieve all the element entries which have the NI categories F,G,S
58 and Ni Pension basis 'M' after 06-Apr-2012.
59 Inner query retrieves all the NI elements with Categories F,G,S and
60 also all the NI elements which has pension basis 'A' which is an obsoleted pension
61 basis from 06-APR-2012. Apart from this it also fetches the NI categories A,B,J with
62 pension basis value as null , we get null value for pension basis because the pension basis for A,B,J
63 can be either A or N, but we dint retrieve rows with the screen entry value 'N'.
64 Outer query filters these null values./
65 */
66 CURSOR csr_element_entries_for_update(
67 c_bg_id NUMBER,
68 c_paye_ref VARCHAR2,
69 c_payroll_id NUMBER
70 )
71 IS
72
73 SELECT ele_entries.*
74 FROM
75 (
76 SELECT peevf.element_entry_id
77 ,peevf.effective_start_date
78 ,peevf.effective_end_date
79 ,max (decode (peevf.input_value_id
80 ,l_input_value_id_category
81 ,peevf.screen_entry_value
82 ,NULL)) pension_category
83 ,max (decode (peevf.input_value_id
84 ,l_input_value_id_pension
85 ,peevf.screen_entry_value
86 ,NULL)) pension_basis
87 ,paaf.assignment_id
91 ,org.org_information1
88 ,papf.sex
89 ,papf.full_name
90 ,paaf.assignment_number
92 ,ppf.payroll_id
93 FROM per_all_assignments_f paaf
94 ,per_all_people_f papf
95 ,pay_payrolls_f ppf
96 ,pay_element_entries_f peef
97 ,pay_element_entry_values_f peevf
98 ,hr_organization_information org
99 ,hr_soft_coding_keyflex flex
100 WHERE org.organization_id = paaf.business_group_id
101 AND paaf.business_group_id = nvl (c_bg_id
102 ,paaf.business_group_id)
103 AND paaf.business_group_id = papf.business_group_id
104 AND nvl (org.org_information10
105 ,'UK') = 'UK'
106 AND org.org_information_context = 'Tax Details References'
107 AND org.org_information1 = flex.segment1
108 AND org.org_information1 = nvl (c_paye_ref
109 ,org.org_information1)
110 AND ppf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
111 AND ppf.payroll_id = nvl (c_payroll_id
112 ,ppf.payroll_id)
113 AND paaf.payroll_id = ppf.payroll_id
114 AND paaf.assignment_id = peef.assignment_id
115 AND papf.person_id = paaf.person_id
116 AND peef.element_entry_id = peevf.element_entry_id
117 AND peevf.input_value_id IN (l_input_value_id_category,l_input_value_id_pension)
118 AND peevf.screen_entry_value IN ('F','G','S'
119 ,'A','B','J'
120 ,'M')
121 AND l_effective_date BETWEEN ppf.effective_start_date
122 AND ppf.effective_end_date
123 AND l_effective_date BETWEEN peef.effective_start_date
124 AND peef.effective_end_date
125 AND l_effective_date BETWEEN paaf.effective_start_date
126 AND paaf.effective_end_date
127 AND l_effective_date BETWEEN peevf.effective_start_date
128 AND peevf.effective_end_date
129 AND l_effective_date BETWEEN papf.effective_start_date
130 AND papf.effective_end_date
131 GROUP BY peevf.element_entry_id
132 ,peevf.effective_start_date
133 ,peevf.effective_end_date
134 ,paaf.assignment_id
135 ,papf.sex
136 ,papf.full_name
137 ,paaf.assignment_number
138 ,org.org_information1
139 ,ppf.payroll_id
140 ) ele_entries
141 WHERE ele_entries.pension_basis IS NOT NULL
142 ORDER BY ele_entries.full_name, ele_entries.payroll_id;
143
144 BEGIN
145 hr_utility.trace('Entering ' || p_package_name || '.update_ni_category_pension');
146 hr_utility.trace('Business Group ID = ' ||to_char(p_bg_id));
147 hr_utility.trace('PAYE Reference = ' ||p_paye_ref);
148 hr_utility.trace('Payroll ID = ' || to_char(p_payroll_id));
149 hr_utility.trace('p_f_category = ' || p_f_category);
150 hr_utility.trace('p_g_category = ' || p_g_category);
151 hr_utility.trace('p_s_category = ' || p_s_category);
152 hr_utility.trace('Validate_mode' ||p_mode);
153 -- Initialization
154 report_output := report_output_type();
155 pen_basis('A') := 'APP not contracted out';
156 pen_basis('C') := 'Contracted out';
157 pen_basis('M') := 'Contracted Out Money Purchase';
158 pen_basis('N') := 'Not contracted out';
159 pen_basis('X') := 'N/A for Category X';
160
161 -- get the input_valid_id for NI category and NI Pension basis.
162 open c_get_input_value_ids;
163 fetch c_get_input_value_ids into l_input_value_id_category,l_input_value_id_pension;
164 close c_get_input_value_ids;
165 hr_utility.trace('l_input_value_id_category = ' || to_char(l_input_value_id_category)
166 || ' l_input_value_id_pension = ' || to_char(l_input_value_id_pension));
167
168 hr_utility.trace('Assignment_id Element_entry_id Status');
169 hr_utility.trace('------------- ---------------- ------');
170
171 --Determine the new pension basis for each category.
172 /* For reference below are the lookups and theie meanings.
173 Lookup Code Meaning
174 ----------- -------
175 A APP not contracted out
176 C Contracted out
177 M Contracted Out Money Purchase
178 N Not contracted out
179 X N/A for Category X
180
181 */
182 if p_f_category = 'A' THEN
183 l_ni_new_pension_f := 'N';
184 else -- p_f_category would be 'D'
185 l_ni_new_pension_f := 'C';
186 end if;
187 if p_g_category = 'B' THEN
188 l_ni_new_pension_g := 'N';
189 else -- p_g_category would be 'E'
190 l_ni_new_pension_g := 'C';
191 end if;
192 if p_s_category = 'J' THEN
193 l_ni_new_pension_s := 'N';
194 else -- p_s_category would be 'L'
195 l_ni_new_pension_s := 'C';
196 end if;
197 report_output.EXTEND(2);
198 report_output(counter) := 'Business Group := ' || to_char(p_bg_id);
199 counter := counter +1;
200 report_output(counter)
201 :=
202 'Assignment Name Gender Old NI New NI Old Pension New Pension Eff date
203 Number Category Category Basis Basis
204 ------ ---- ------ -------- -------- ----------- ------------ --------';
205
209 if l_element_entry.pension_category = 'F' THEN
206 FOR l_element_entry in csr_element_entries_for_update(p_bg_id, p_paye_ref,p_payroll_id)
207 LOOP
208 -- determine the new category and new pension basis for the this element entry.
210 l_ni_new_category := p_f_category;
211 l_ni_new_pension := l_ni_new_pension_f;
212 elsif l_element_entry.pension_category = 'G' THEN
213 l_ni_new_category := p_g_category;
214 l_ni_new_pension := l_ni_new_pension_g;
215 elsif l_element_entry.pension_category = 'S' THEN
216 l_ni_new_category := p_s_category;
217 l_ni_new_pension := l_ni_new_pension_s;
218 else -- Pension category would be either A,B,J. Then we should just update pension basis.
219 l_ni_new_category := l_element_entry.pension_category;
220 l_ni_new_pension := 'N';
221 end if;
222
223 if p_mode = 'GB_VALIDATE_COMMIT' THEN
224 -- Call the update api in UPDATE_OVERRIDE mode for updating the element entries for future entries as well.
225 hr_entry_api.update_element_entry(p_dt_update_mode => 'UPDATE_OVERRIDE',
226 p_session_date => l_effective_date,
227 p_element_entry_id => l_element_entry.element_entry_id,
228 p_input_value_id1 => l_input_value_id_category,
229 p_input_value_id2 => l_input_value_id_pension,
230 p_entry_value1 => l_ni_new_category,
231 p_entry_value2 => pen_basis(l_ni_new_pension)
232 );
233 end if;
234 report_output.EXTEND(1);
235 counter := counter +1;
236 report_output(counter) := rpad(to_char(l_element_entry.assignment_number),15) || ' ' ||
237 rpad(to_char(l_element_entry.full_name),16) || ' ' ||
238 rpad(to_char(l_element_entry.sex),12) || ' ' ||
239 rpad(to_char(l_element_entry.pension_category),12) || ' ' ||
240 rpad(to_char(l_ni_new_category),12) || ' ' ||
241 rpad(to_char(pen_basis(l_element_entry.pension_basis)),31) || ' ' ||
242 rpad(to_char(pen_basis(l_ni_new_pension)),31) || ' ' ||
243 rpad(to_char(l_effective_date),12);
244
245 hr_utility.trace(l_element_entry.assignment_id ||' ' || l_element_entry.element_entry_id|| ' Updated Successfully');
246
247 END LOOP;
248 hr_utility.trace('Leaving ' || p_package_name || '.update_ni_catergory_and_pension');
249 if p_mode = 'GB_VALIDATE_COMMIT' THEN
250 COMMIT;
251 else
252 ROLLBACK;
253 end if;
254 --Display the report.
255 if counter >2 THEN
256 FOR i IN 1..report_output.count
257 LOOP
258 fnd_file.put_line(FND_FILE.OUTPUT,report_output(i));
259 END LOOP;
260 else
261 fnd_file.put_line(FND_FILE.OUTPUT,' ');
262 fnd_file.put_line(FND_FILE.OUTPUT,rpad('-',68,'-')||'No records updated'||rpad('-',69,'-'));
263 fnd_file.put_line(FND_FILE.OUTPUT,' ');
264 end if;
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 ROLLBACK;
269 fnd_file.put_line(FND_FILE.LOG,rpad('-',155,'-'));
270 fnd_file.put_line(FND_FILE.LOG,'Error Record :');
271 fnd_file.put_line(FND_FILE.LOG,report_output(report_output.LAST));
272 fnd_file.put_line(FND_FILE.LOG,rpad('-',155,'-'));
273 fnd_file.put_line(FND_FILE.LOG,SQLCODE||' - '||SQLERRM);
274 RAISE_APPLICATION_ERROR(-20001, SQLCODE||' - '||SQLERRM);
275 END update_ni_category_pension;
276
277 END pay_gb_nicat_upd;
278
[Home] [Help]