1 package body hr_gbnidir as
2 /* $Header: pygbnicd.pkb 120.7.12020000.3 2012/08/28 11:29:39 achandwa ship $ */
3 --------------------------------------------------------------------------------
4 --
5 g_defined_director_set boolean := FALSE;
6 g_defined_ptd_set boolean := FALSE;
7 g_assignment_id number;
8 g_effective_date date;
9 g_catpen varchar2(80);
10 g_assignment_action_id number;
11 g_statutory_period_start_date date;
12 g_ni_element_type_id number;
13 g_cat_input_id number;
14 g_pen_input_id number;
15 g_comp_min_ees number;
16 g_comp_min_ers number;
17 g_ni_a_employee number;
18 g_ni_b_employee number;
19 g_ni_c_employee number;
20 g_ni_d_employee number;
21 g_ni_e_employee number;
22 g_ni_f_employee number;
23 g_ni_g_employee number;
24 g_ni_j_employee number;
25 g_ni_l_employee number;
26 g_ni_l_employee_not number;
27 g_ni_s_employee number;
28 g_ni_s_employee_not number;
29 g_ni_a_employer number;
30 g_ni_b_employer number;
31 g_ni_c_employer number;
32 g_ni_d_employer number;
33 g_ni_e_employer number;
34 g_ni_f_employer number;
35 g_ni_g_employer number;
36 g_ni_j_employer number;
37 g_ni_l_employer number;
38 g_ni_s_employer number;
39 g_ni_a_able number;
40 g_ni_b_able number;
41 g_ni_c_able number;
42 g_ni_co_able number;
43 g_ni_d_able number;
44 g_ni_e_able number;
45 g_ni_f_able number;
46 g_ni_g_able number;
47 g_ni_j_able number;
48 g_ni_l_able number;
49 g_ni_s_able number;
50 g_ni_c_co_able number;
51 g_ni_d_co_able number;
52 g_ni_e_co_able number;
53 g_ni_f_co_able number;
54 g_ni_g_co_able number;
55 g_ni_s_co_able number;
56 g_ni_c_co number;
57 g_ni_d_co number;
58 g_ni_e_co number;
59 g_ni_f_co number;
60 g_ni_g_co number;
61 g_ni_s_co number;
62 g_ni_able_id number;
63 g_ni_a_able_lel number;
64 g_ni_a_able_uap number; --EOY 08/09
65 g_ni_a_able_uel number;
66 g_ni_a_able_et number;
67 g_ni_a_able_auel number;
68 g_ni_a_ee_auel number;
69 g_ni_b_able_lel number;
70 g_ni_b_able_uap number; --EOY 08/09
71 g_ni_b_able_uel number;
72 g_ni_b_able_et number;
73 g_ni_b_able_auel number;
74 g_ni_b_ee_auel number;
75 g_ni_c_able_lel number;
76 g_ni_c_able_uap number; --EOY 08/09
77 g_ni_c_able_uel number;
78 g_ni_c_able_et number;
79 g_ni_c_able_auel number;
80 g_ni_c_ee_auel number;
81 g_ni_d_able_lel number;
82 g_ni_d_able_uap number; --EOY 08/09
83 g_ni_d_able_uel number;
84 g_ni_d_able_et number;
85 g_ni_d_able_auel number;
86 g_ni_d_ee_auel number;
87 g_ni_e_able_lel number;
88 g_ni_e_able_uap number; --EOY 08/09
89 g_ni_e_able_uel number;
90 g_ni_e_able_et number;
91 g_ni_e_able_auel number;
92 g_ni_e_ee_auel number;
93 g_ni_f_able_lel number;
94 g_ni_f_able_uap number; --EOY 08/09
95 g_ni_f_able_uel number;
96 g_ni_f_able_et number;
97 g_ni_f_able_auel number;
98 g_ni_f_ee_auel number;
99 g_ni_g_able_lel number;
100 g_ni_g_able_uap number; --EOY 08/09
101 g_ni_g_able_uel number;
102 g_ni_g_able_et number;
103 g_ni_g_able_auel number;
104 g_ni_g_ee_auel number;
105 g_ni_j_able_lel number;
106 g_ni_j_able_uap number; --EOY 08/09
107 g_ni_j_able_uel number;
108 g_ni_j_able_et number;
109 g_ni_j_able_auel number;
110 g_ni_j_ee_auel number;
111 g_ni_l_able_lel number;
112 g_ni_l_able_uap number; --EOY 08/9
113 g_ni_l_able_uel number;
114 g_ni_l_able_et number;
115 g_ni_l_able_auel number;
116 g_ni_l_ee_auel number;
117 g_ni_s_able_lel number;
118 g_ni_s_able_uap number; --EOY 08/09
119 g_ni_s_able_uel number;
120 g_ni_s_able_et number;
121 g_ni_s_able_auel number;
122 g_ni_s_ee_auel number;
123 g_st_ni_a_able number;
124 g_st_ni_ap_able number;
125 g_st_ni_b_able number;
126 g_st_ni_bp_able number;
127 g_st_ni_c_able number;
128 g_st_ni_co_able number;
129 g_st_ni_d_able number;
130 g_st_ni_e_able number;
131 g_st_ni_f_able number;
132 g_st_ni_g_able number;
133 g_st_ni_j_able number;
134 g_st_ni_jp_able number;
135 g_st_ni_l_able number;
136 g_st_ni_s_able number;
137 g_ni_c_ers_rebate number;
138 g_ni_d_ers_rebate number;
139 g_ni_d_ees_rebate number;
140 g_ni_e_ers_rebate number;
141 g_ni_f_ers_rebate number;
142 g_ni_f_ees_rebate number;
143 g_ni_g_ers_rebate number;
144 g_ni_s_ers_rebate number;
145 g_comp_min_ees_defbal number;
146 g_comp_min_ers_defbal number;
147 g_ni_a_employee_defbal number;
148 g_ni_b_employee_defbal number;
149 g_ni_c_employee_defbal number;
150 g_ni_d_employee_defbal number;
151 g_ni_e_employee_defbal number;
152 g_ni_f_employee_defbal number;
153 g_ni_g_employee_defbal number;
154 g_ni_j_employee_defbal number;
155 g_ni_l_employee_defbal number;
156 g_ni_l_employee_not_defbal number;
157 g_ni_s_employee_defbal number;
158 g_ni_s_employee_not_defbal number;
159 g_ni_a_employer_defbal number;
160 g_ni_b_employer_defbal number;
161 g_ni_c_employer_defbal number;
162 g_ni_d_employer_defbal number;
163 g_ni_e_employer_defbal number;
164 g_ni_f_employer_defbal number;
165 g_ni_g_employer_defbal number;
166 g_ni_j_employer_defbal number;
167 g_ni_l_employer_defbal number;
168 g_ni_s_employer_defbal number;
169 g_ni_a_able_defbal number;
170 g_ni_b_able_defbal number;
171 g_ni_c_able_defbal number;
172 g_ni_d_able_defbal number;
173 g_ni_e_able_defbal number;
174 g_ni_f_able_defbal number;
175 g_ni_g_able_defbal number;
176 g_ni_j_able_defbal number;
177 g_ni_l_able_defbal number;
178 g_ni_s_able_defbal number;
179 g_ni_c_co_able_defbal number;
180 g_ni_d_co_able_defbal number;
181 g_ni_e_co_able_defbal number;
182 g_ni_f_co_able_defbal number;
183 g_ni_g_co_able_defbal number;
184 g_ni_s_co_able_defbal number;
185 g_ni_a_able_lel_defbal number;
186 g_ni_a_able_uap_defbal number; --EOY 08/09
187 g_ni_a_able_uel_defbal number;
188 g_ni_a_able_et_defbal number;
189 g_ni_a_able_auel_defbal number;
190 g_ni_a_ee_auel_defbal number;
191 g_ni_b_able_lel_defbal number;
192 g_ni_b_able_uap_defbal number; --EOY 08/09
193 g_ni_b_able_uel_defbal number;
194 g_ni_b_able_et_defbal number;
195 g_ni_b_able_auel_defbal number;
196 g_ni_b_ee_auel_defbal number;
197 g_ni_c_able_lel_defbal number;
198 g_ni_c_able_uap_defbal number; --EOY 08/09
199 g_ni_c_able_uel_defbal number;
200 g_ni_c_able_et_defbal number;
201 g_ni_c_able_auel_defbal number;
202 g_ni_c_ee_auel_defbal number;
203 g_ni_d_able_lel_defbal number;
204 g_ni_d_able_uap_defbal number; --EOY 08/09
205 g_ni_d_able_uel_defbal number;
206 g_ni_d_able_et_defbal number;
207 g_ni_d_able_auel_defbal number;
208 g_ni_d_ee_auel_defbal number;
209 g_ni_e_able_lel_defbal number;
210 g_ni_e_able_uap_defbal number; --EOY 08/09
211 g_ni_e_able_uel_defbal number;
212 g_ni_e_able_et_defbal number;
213 g_ni_e_able_auel_defbal number;
214 g_ni_e_ee_auel_defbal number;
215 g_ni_f_able_lel_defbal number;
216 g_ni_f_able_uap_defbal number; --EOY 08/09
217 g_ni_f_able_uel_defbal number;
218 g_ni_f_able_et_defbal number;
219 g_ni_f_able_auel_defbal number;
220 g_ni_f_ee_auel_defbal number;
221 g_ni_g_able_lel_defbal number;
222 g_ni_g_able_uap_defbal number; --EOY 08/09
223 g_ni_g_able_uel_defbal number;
224 g_ni_g_able_et_defbal number;
225 g_ni_g_able_auel_defbal number;
226 g_ni_g_ee_auel_defbal number;
227 g_ni_j_able_lel_defbal number;
228 g_ni_j_able_uap_defbal number; --EOY 08/09
229 g_ni_j_able_uel_defbal number;
230 g_ni_j_able_et_defbal number;
231 g_ni_j_able_auel_defbal number;
232 g_ni_j_ee_auel_defbal number;
233 g_ni_l_able_lel_defbal number;
234 g_ni_l_able_uap_defbal number; --EOY 08/09
235 g_ni_l_able_uel_defbal number;
236 g_ni_l_able_et_defbal number;
237 g_ni_l_able_auel_defbal number;
238 g_ni_l_ee_auel_defbal number;
239 g_ni_s_able_lel_defbal number;
240 g_ni_s_able_uap_defbal number; --EOY 08/09
241 g_ni_s_able_uel_defbal number;
242 g_ni_s_able_et_defbal number;
243 g_ni_s_able_auel_defbal number;
244 g_ni_s_ee_auel_defbal number;
245 g_ni_c_ers_rebate_defbal number;
246 g_ni_d_ers_rebate_defbal number;
247 g_ni_d_ees_rebate_defbal number;
248 g_ni_e_ers_rebate_defbal number;
249 g_ni_f_ers_rebate_defbal number;
250 g_ni_f_ees_rebate_defbal number;
251 g_ni_g_ers_rebate_defbal number;
252 g_ni_s_ers_rebate_defbal number;
253 --------------------------------------------------------------------------------
254 -- GET_PLSQL_GLOBAL
255 -- retrieve a PLSQL global from the session
256 function GET_PLSQL_GLOBAL
257 ( P_global_name in varchar2 )
258 return number is
259 --
260 l_value number;
261 --
262 Begin
263 --
264 l_value := 0;
265
266 --
267 if p_global_name = 'a_employee' then l_value := g_ni_a_employee;
268 elsif p_global_name = 'b_employee' then l_value := g_ni_b_employee;
269 elsif p_global_name = 'c_employee' then l_value := g_ni_c_employee;
270 elsif p_global_name = 'd_employee' then l_value := g_ni_d_employee;
271 elsif p_global_name = 'e_employee' then l_value := g_ni_e_employee;
272 elsif p_global_name = 'f_employee' then l_value := g_ni_f_employee;
273 elsif p_global_name = 'g_employee' then l_value := g_ni_g_employee;
274 elsif p_global_name = 'j_employee' then l_value := g_ni_j_employee;
275 elsif p_global_name = 'l_employee' then l_value := g_ni_l_employee;
276 elsif p_global_name = 'l_employee_not' then l_value := g_ni_l_employee_not;
277 elsif p_global_name = 's_employee' then l_value := g_ni_s_employee;
278 elsif p_global_name = 's_employee_not' then l_value := g_ni_s_employee_not;
279 elsif p_global_name = 'a_employer' then l_value := g_ni_a_employer;
280 elsif p_global_name = 'b_employer' then l_value := g_ni_b_employer;
281 elsif p_global_name = 'c_employer' then l_value := g_ni_c_employer;
282 elsif p_global_name = 'd_employer' then l_value := g_ni_d_employer;
283 elsif p_global_name = 'e_employer' then l_value := g_ni_e_employer;
284 elsif p_global_name = 'f_employer' then l_value := g_ni_f_employer;
285 elsif p_global_name = 'g_employer' then l_value := g_ni_g_employer;
286 elsif p_global_name = 'j_employer' then l_value := g_ni_j_employer;
287 elsif p_global_name = 'l_employer' then l_value := g_ni_l_employer;
288 elsif p_global_name = 's_employer' then l_value := g_ni_s_employer;
289 elsif p_global_name = 'a_able' then l_value := g_ni_a_able;
290 elsif p_global_name = 'b_able' then l_value := g_ni_b_able;
291 elsif p_global_name = 'c_able' then l_value := g_ni_c_able;
292 elsif p_global_name = 'co_able' then l_value := g_ni_co_able;
293 elsif p_global_name = 'd_able' then l_value := g_ni_d_able;
294 elsif p_global_name = 'e_able' then l_value := g_ni_e_able;
295 elsif p_global_name = 'f_able' then l_value := g_ni_f_able;
296 elsif p_global_name = 'g_able' then l_value := g_ni_g_able;
297 elsif p_global_name = 'j_able' then l_value := g_ni_j_able;
298 elsif p_global_name = 'l_able' then l_value := g_ni_l_able;
299 elsif p_global_name = 's_able' then l_value := g_ni_s_able;
300 elsif p_global_name = 'c_co_able' then l_value := g_ni_c_co_able;
301 elsif p_global_name = 'd_co_able' then l_value := g_ni_d_co_able;
302 elsif p_global_name = 'e_co_able' then l_value := g_ni_e_co_able;
303 elsif p_global_name = 'f_co_able' then l_value := g_ni_f_co_able;
304 elsif p_global_name = 'g_co_able' then l_value := g_ni_g_co_able;
305 elsif p_global_name = 's_co_able' then l_value := g_ni_s_co_able;
306 elsif p_global_name = 'c_ers_rebate' then l_value := g_ni_c_ers_rebate;
307 elsif p_global_name = 'd_ers_rebate' then l_value := g_ni_d_ers_rebate;
308 elsif p_global_name = 'e_ers_rebate' then l_value := g_ni_e_ers_rebate;
309 elsif p_global_name = 'f_ers_rebate' then l_value := g_ni_f_ers_rebate;
310 elsif p_global_name = 'g_ers_rebate' then l_value := g_ni_g_ers_rebate;
311 elsif p_global_name = 's_ers_rebate' then l_value := g_ni_s_ers_rebate;
312 elsif p_global_name = 'd_ees_rebate' then l_value := g_ni_d_ees_rebate;
313 elsif p_global_name = 'f_ees_rebate' then l_value := g_ni_f_ees_rebate;
314 elsif p_global_name = 'a_able_lel' then l_value := g_ni_a_able_lel;
315 elsif p_global_name = 'a_able_uap' then l_value := g_ni_a_able_uap; --EOY 08/09
316 elsif p_global_name = 'a_able_uel' then l_value := g_ni_a_able_uel;
317 elsif p_global_name = 'a_able_et' then l_value := g_ni_a_able_et;
318 elsif p_global_name = 'a_able_auel' then l_value := g_ni_a_able_auel;
319 elsif p_global_name = 'a_ee_auel' then l_value := g_ni_a_ee_auel;
320 elsif p_global_name = 'b_able_lel' then l_value := g_ni_b_able_lel;
321 elsif p_global_name = 'b_able_uap' then l_value := g_ni_b_able_uap; --EOY 08/09
322 elsif p_global_name = 'b_able_uel' then l_value := g_ni_b_able_uel;
323 elsif p_global_name = 'b_able_et' then l_value := g_ni_b_able_et;
324 elsif p_global_name = 'b_able_auel' then l_value := g_ni_b_able_auel;
325 elsif p_global_name = 'b_ee_auel' then l_value := g_ni_b_ee_auel;
326 elsif p_global_name = 'c_able_lel' then l_value := g_ni_c_able_lel;
327 elsif p_global_name = 'c_able_uap' then l_value := g_ni_c_able_uap; --EOY 08/09
328 elsif p_global_name = 'c_able_uel' then l_value := g_ni_c_able_uel;
329 elsif p_global_name = 'c_able_et' then l_value := g_ni_c_able_et;
330 elsif p_global_name = 'c_able_auel' then l_value := g_ni_c_able_auel;
331 elsif p_global_name = 'c_ee_auel' then l_value := g_ni_c_ee_auel;
332 elsif p_global_name = 'd_able_lel' then l_value := g_ni_d_able_lel;
333 elsif p_global_name = 'd_able_uap' then l_value := g_ni_d_able_uap; --EOY 08/09
334 elsif p_global_name = 'd_able_uel' then l_value := g_ni_d_able_uel;
335 elsif p_global_name = 'd_able_et' then l_value := g_ni_d_able_et;
336 elsif p_global_name = 'd_able_auel' then l_value := g_ni_d_able_auel;
337 elsif p_global_name = 'd_ee_auel' then l_value := g_ni_d_ee_auel;
338 elsif p_global_name = 'e_able_lel' then l_value := g_ni_e_able_lel;
339 elsif p_global_name = 'e_able_uap' then l_value := g_ni_e_able_uap; --EOY 08/09
340 elsif p_global_name = 'e_able_uel' then l_value := g_ni_e_able_uel;
341 elsif p_global_name = 'e_able_et' then l_value := g_ni_e_able_et;
342 elsif p_global_name = 'e_able_auel' then l_value := g_ni_e_able_auel;
343 elsif p_global_name = 'e_ee_auel' then l_value := g_ni_e_ee_auel;
344 elsif p_global_name = 'f_able_lel' then l_value := g_ni_f_able_lel;
345 elsif p_global_name = 'f_able_uap' then l_value := g_ni_f_able_uap; --EOY 08/09
346 elsif p_global_name = 'f_able_uel' then l_value := g_ni_f_able_uel;
347 elsif p_global_name = 'f_able_et' then l_value := g_ni_f_able_et;
348 elsif p_global_name = 'f_able_auel' then l_value := g_ni_f_able_auel;
349 elsif p_global_name = 'f_ee_auel' then l_value := g_ni_f_ee_auel;
350 elsif p_global_name = 'g_able_lel' then l_value := g_ni_g_able_lel;
351 elsif p_global_name = 'g_able_uap' then l_value := g_ni_g_able_uap; --EOY 08/09
352 elsif p_global_name = 'g_able_uel' then l_value := g_ni_g_able_uel;
353 elsif p_global_name = 'g_able_et' then l_value := g_ni_g_able_et;
354 elsif p_global_name = 'g_able_auel' then l_value := g_ni_g_able_auel;
355 elsif p_global_name = 'g_ee_auel' then l_value := g_ni_g_ee_auel;
356 elsif p_global_name = 's_able_lel' then l_value := g_ni_s_able_lel;
357 elsif p_global_name = 's_able_uap' then l_value := g_ni_s_able_uap; --EOY 08/09
358 elsif p_global_name = 's_able_uel' then l_value := g_ni_s_able_uel;
359 elsif p_global_name = 's_able_et' then l_value := g_ni_s_able_et;
360 elsif p_global_name = 's_able_auel' then l_value := g_ni_s_able_auel;
361 elsif p_global_name = 's_ee_auel' then l_value := g_ni_s_ee_auel;
362 elsif p_global_name = 'st_a_able' then l_value := g_st_ni_a_able;
363 elsif p_global_name = 'st_ap_able' then l_value := g_st_ni_ap_able;
364 elsif p_global_name = 'st_b_able' then l_value := g_st_ni_b_able;
365 elsif p_global_name = 'st_bp_able' then l_value := g_st_ni_bp_able;
366 elsif p_global_name = 'st_c_able' then l_value := g_st_ni_c_able;
367 elsif p_global_name = 'st_co_able' then l_value := g_st_ni_co_able;
368 elsif p_global_name = 'st_d_able' then l_value := g_st_ni_d_able;
369 elsif p_global_name = 'st_e_able' then l_value := g_st_ni_e_able;
370 elsif p_global_name = 'st_f_able' then l_value := g_st_ni_f_able;
371 elsif p_global_name = 'st_g_able' then l_value := g_st_ni_g_able;
372 elsif p_global_name = 'st_j_able' then l_value := g_st_ni_j_able;
373 elsif p_global_name = 'st_jp_able' then l_value := g_st_ni_jp_able;
374 elsif p_global_name = 'st_l_able' then l_value := g_st_ni_l_able;
375 elsif p_global_name = 'st_s_able' then l_value := g_st_ni_s_able;
376 elsif p_global_name = 'comp_min_ees' then l_value := g_comp_min_ees;
377 elsif p_global_name = 'comp_min_ers' then l_value := g_comp_min_ers;
378 --9509806 Begin - Missed values are added
379 elsif p_global_name = 'j_able_lel' then l_value := g_ni_j_able_lel;
380 elsif p_global_name = 'j_able_uap' then l_value := g_ni_j_able_uap; --EOY 08/09
381 elsif p_global_name = 'j_able_uel' then l_value := g_ni_j_able_uel;
382 elsif p_global_name = 'j_able_et' then l_value := g_ni_j_able_et;
383 elsif p_global_name = 'j_able_auel' then l_value := g_ni_j_able_auel;
384 elsif p_global_name = 'j_ee_auel' then l_value := g_ni_j_ee_auel;
385 elsif p_global_name = 'l_able_lel' then l_value := g_ni_l_able_lel;
386 elsif p_global_name = 'l_able_uap' then l_value := g_ni_l_able_uap; --EOY 08/09
387 elsif p_global_name = 'l_able_uel' then l_value := g_ni_l_able_uel;
388 elsif p_global_name = 'l_able_et' then l_value := g_ni_l_able_et;
389 elsif p_global_name = 'l_able_auel' then l_value := g_ni_l_able_auel;
390 elsif p_global_name = 'l_ee_auel' then l_value := g_ni_l_ee_auel;
391 --9509806 End
392 end if;
393 --
394 RETURN l_value ;
395 --
396 end GET_PLSQL_GLOBAL;
397 --------------------------------------------------------------------------------
398 -- Procedure: set_defined_balances
399 -- Description: This procedure sets all the defined balances that are
400 -- needed for NI calculation, and stores them in global
401 -- variables so should only be called once per session. This
402 -- sets a global flag to denote that they have been set.
403 --------------------------------------------------------------------------------
404
405 procedure set_defined_balances (p_database_item_suffix in varchar2) is
406 --
407 begin
408 select
409 max(decode(BTYPE.balance_name,'NI A Employee',DEFBAL.Defined_balance_id))
410 ,max(decode(BTYPE.balance_name,'NI B Employee',DEFBAL.Defined_balance_id))
411 ,max(decode(BTYPE.balance_name,'NI C Employee',DEFBAL.Defined_balance_id))
412 ,max(decode(BTYPE.balance_name,'NI D Employee',DEFBAL.Defined_balance_id))
413 ,max(decode(BTYPE.balance_name,'NI E Employee',DEFBAL.Defined_balance_id))
414 ,max(decode(BTYPE.balance_name,'NI F Employee',DEFBAL.Defined_balance_id))
415 ,max(decode(BTYPE.balance_name,'NI G Employee',DEFBAL.Defined_balance_id))
416 ,max(decode(BTYPE.balance_name,'NI J Employee',DEFBAL.Defined_balance_id))
417 ,max(decode(BTYPE.balance_name,'NI L Employee',DEFBAL.Defined_balance_id))
418 ,max(decode(BTYPE.balance_name,'NI L Employee Notional',
419 DEFBAL.Defined_balance_id))
420 ,max(decode(BTYPE.balance_name,'NI S Employee',DEFBAL.Defined_balance_id))
421 ,max(decode(BTYPE.balance_name,'NI S Employee Notional',
422 DEFBAL.Defined_balance_id))
423 ,max(decode(BTYPE.balance_name,'NI A Employer',DEFBAL.Defined_balance_id))
424 ,max(decode(BTYPE.balance_name,'NI B Employer',DEFBAL.Defined_balance_id))
425 ,max(decode(BTYPE.balance_name,'NI C Employer',DEFBAL.Defined_balance_id))
426 ,max(decode(BTYPE.balance_name,'NI D Employer',DEFBAL.Defined_balance_id))
427 ,max(decode(BTYPE.balance_name,'NI E Employer',DEFBAL.Defined_balance_id))
428 ,max(decode(BTYPE.balance_name,'NI F Employer',DEFBAL.Defined_balance_id))
429 ,max(decode(BTYPE.balance_name,'NI G Employer',DEFBAL.Defined_balance_id))
430 ,max(decode(BTYPE.balance_name,'NI J Employer',DEFBAL.Defined_balance_id))
431 ,max(decode(BTYPE.balance_name,'NI L Employer',DEFBAL.Defined_balance_id))
432 ,max(decode(BTYPE.balance_name,'NI S Employer',DEFBAL.Defined_balance_id))
433 ,max(decode(BTYPE.balance_name,'NI A Able',DEFBAL.Defined_balance_id))
434 ,max(decode(BTYPE.balance_name,'NI A Able LEL',DEFBAL.Defined_balance_id))
435 ,max(decode(BTYPE.balance_name,'NI A Able UAP',DEFBAL.Defined_balance_id)) --EOY 08/09
436 ,max(decode(BTYPE.balance_name,'NI A Able UEL',DEFBAL.Defined_balance_id))
437 ,max(decode(BTYPE.balance_name,'NI A Able ET',DEFBAL.Defined_balance_id))
438 ,max(decode(BTYPE.balance_name,'NI A Able AUEL',DEFBAL.Defined_balance_id))
439 ,max(decode(BTYPE.balance_name,'NI A EE AUEL',DEFBAL.Defined_balance_id))
440 ,max(decode(BTYPE.balance_name,'NI B Able',DEFBAL.Defined_balance_id))
441 ,max(decode(BTYPE.balance_name,'NI B Able LEL',DEFBAL.Defined_balance_id))
442 ,max(decode(BTYPE.balance_name,'NI B Able UAP',DEFBAL.Defined_balance_id)) --EOY 08/09
443 ,max(decode(BTYPE.balance_name,'NI B Able UEL',DEFBAL.Defined_balance_id))
444 ,max(decode(BTYPE.balance_name,'NI B Able ET',DEFBAL.Defined_balance_id))
445 ,max(decode(BTYPE.balance_name,'NI B Able AUEL',DEFBAL.Defined_balance_id))
446 ,max(decode(BTYPE.balance_name,'NI B EE AUEL',DEFBAL.Defined_balance_id))
447 ,max(decode(BTYPE.balance_name,'NI C Able',DEFBAL.Defined_balance_id))
448 ,max(decode(BTYPE.balance_name,'NI C Able LEL',DEFBAL.Defined_balance_id))
449 ,max(decode(BTYPE.balance_name,'NI C Able UAP',DEFBAL.Defined_balance_id)) --EOY 08/09
450 ,max(decode(BTYPE.balance_name,'NI C Able UEL',DEFBAL.Defined_balance_id))
451 ,max(decode(BTYPE.balance_name,'NI C Able ET',DEFBAL.Defined_balance_id))
452 ,max(decode(BTYPE.balance_name,'NI C Able AUEL',DEFBAL.Defined_balance_id))
453 ,max(decode(BTYPE.balance_name,'NI C EE AUEL',DEFBAL.Defined_balance_id))
454 ,max(decode(BTYPE.balance_name,'NI D Able',DEFBAL.Defined_balance_id))
455 ,max(decode(BTYPE.balance_name,'NI D Able LEL',DEFBAL.Defined_balance_id))
456 ,max(decode(BTYPE.balance_name,'NI D Able UAP',DEFBAL.Defined_balance_id)) --EOY 08/09
457 ,max(decode(BTYPE.balance_name,'NI D Able UEL',DEFBAL.Defined_balance_id))
458 ,max(decode(BTYPE.balance_name,'NI D Able ET',DEFBAL.Defined_balance_id))
459 ,max(decode(BTYPE.balance_name,'NI D Able AUEL',DEFBAL.Defined_balance_id))
460 ,max(decode(BTYPE.balance_name,'NI D EE AUEL',DEFBAL.Defined_balance_id))
461 ,max(decode(BTYPE.balance_name,'NI E Able',DEFBAL.Defined_balance_id))
462 ,max(decode(BTYPE.balance_name,'NI E Able LEL',DEFBAL.Defined_balance_id))
463 ,max(decode(BTYPE.balance_name,'NI E Able UAP',DEFBAL.Defined_balance_id)) --EOY 08/09
464 ,max(decode(BTYPE.balance_name,'NI E Able UEL',DEFBAL.Defined_balance_id))
465 ,max(decode(BTYPE.balance_name,'NI E Able ET',DEFBAL.Defined_balance_id))
466 ,max(decode(BTYPE.balance_name,'NI E Able AUEL',DEFBAL.Defined_balance_id))
467 ,max(decode(BTYPE.balance_name,'NI E EE AUEL',DEFBAL.Defined_balance_id))
468 ,max(decode(BTYPE.balance_name,'NI F Able',DEFBAL.Defined_balance_id))
469 ,max(decode(BTYPE.balance_name,'NI F Able LEL',DEFBAL.Defined_balance_id))
470 ,max(decode(BTYPE.balance_name,'NI F Able UAP',DEFBAL.Defined_balance_id)) --EOY 08/09
471 ,max(decode(BTYPE.balance_name,'NI F Able UEL',DEFBAL.Defined_balance_id))
472 ,max(decode(BTYPE.balance_name,'NI F Able ET',DEFBAL.Defined_balance_id))
473 ,max(decode(BTYPE.balance_name,'NI F Able AUEL',DEFBAL.Defined_balance_id))
474 ,max(decode(BTYPE.balance_name,'NI F EE AUEL',DEFBAL.Defined_balance_id))
475 ,max(decode(BTYPE.balance_name,'NI G Able',DEFBAL.Defined_balance_id))
476 ,max(decode(BTYPE.balance_name,'NI G Able LEL',DEFBAL.Defined_balance_id))
477 ,max(decode(BTYPE.balance_name,'NI G Able UAP',DEFBAL.Defined_balance_id)) --EOY 08/09
478 ,max(decode(BTYPE.balance_name,'NI G Able UEL',DEFBAL.Defined_balance_id))
479 ,max(decode(BTYPE.balance_name,'NI G Able ET',DEFBAL.Defined_balance_id))
480 ,max(decode(BTYPE.balance_name,'NI G Able AUEL',DEFBAL.Defined_balance_id))
481 ,max(decode(BTYPE.balance_name,'NI G EE AUEL',DEFBAL.Defined_balance_id))
482 ,max(decode(BTYPE.balance_name,'NI J Able',DEFBAL.Defined_balance_id))
483 ,max(decode(BTYPE.balance_name,'NI J Able LEL',DEFBAL.Defined_balance_id))
484 ,max(decode(BTYPE.balance_name,'NI J Able UAP',DEFBAL.Defined_balance_id)) --EOY 08/09
485 ,max(decode(BTYPE.balance_name,'NI J Able UEL',DEFBAL.Defined_balance_id))
486 ,max(decode(BTYPE.balance_name,'NI J Able ET',DEFBAL.Defined_balance_id))
487 ,max(decode(BTYPE.balance_name,'NI J Able AUEL',DEFBAL.Defined_balance_id))
488 ,max(decode(BTYPE.balance_name,'NI J EE AUEL',DEFBAL.Defined_balance_id))
489 ,max(decode(BTYPE.balance_name,'NI L Able',DEFBAL.Defined_balance_id))
490 ,max(decode(BTYPE.balance_name,'NI L Able LEL',DEFBAL.Defined_balance_id))
491 ,max(decode(BTYPE.balance_name,'NI L Able UAP',DEFBAL.Defined_balance_id)) --EOY 08/09
492 ,max(decode(BTYPE.balance_name,'NI L Able UEL',DEFBAL.Defined_balance_id))
493 ,max(decode(BTYPE.balance_name,'NI L Able ET',DEFBAL.Defined_balance_id))
494 ,max(decode(BTYPE.balance_name,'NI L Able AUEL',DEFBAL.Defined_balance_id))
495 ,max(decode(BTYPE.balance_name,'NI L EE AUEL',DEFBAL.Defined_balance_id))
496 ,max(decode(BTYPE.balance_name,'NI S Able',DEFBAL.Defined_balance_id))
497 ,max(decode(BTYPE.balance_name,'NI S Able LEL',DEFBAL.Defined_balance_id))
498 ,max(decode(BTYPE.balance_name,'NI S Able UAP',DEFBAL.Defined_balance_id)) --EOY 08/09
499 ,max(decode(BTYPE.balance_name,'NI S Able UEL',DEFBAL.Defined_balance_id))
500 ,max(decode(BTYPE.balance_name,'NI S Able ET',DEFBAL.Defined_balance_id))
501 ,max(decode(BTYPE.balance_name,'NI S Able AUEL',DEFBAL.Defined_balance_id))
502 ,max(decode(BTYPE.balance_name,'NI S EE AUEL',DEFBAL.Defined_balance_id))
503 ,max(decode(BTYPE.balance_name,'NI C CO Able',DEFBAL.Defined_balance_id))
504 ,max(decode(BTYPE.balance_name,'NI D CO Able',DEFBAL.Defined_balance_id))
505 ,max(decode(BTYPE.balance_name,'NI E CO Able',DEFBAL.Defined_balance_id))
506 ,max(decode(BTYPE.balance_name,'NI F CO Able',DEFBAL.Defined_balance_id))
507 ,max(decode(BTYPE.balance_name,'NI G CO Able',DEFBAL.Defined_balance_id))
508 ,max(decode(BTYPE.balance_name,'NI S CO Able',DEFBAL.Defined_balance_id))
509 ,max(decode(BTYPE.balance_name,'NI C Ers Rebate',DEFBAL.Defined_balance_id))
510 ,max(decode(BTYPE.balance_name,'NI D Ers Rebate',DEFBAL.Defined_balance_id))
511 ,max(decode(BTYPE.balance_name,'NI E Ers Rebate',DEFBAL.Defined_balance_id))
512 ,max(decode(BTYPE.balance_name,'NI F Ers Rebate',DEFBAL.Defined_balance_id))
513 ,max(decode(BTYPE.balance_name,'NI G Ers Rebate',DEFBAL.Defined_balance_id))
514 ,max(decode(BTYPE.balance_name,'NI S Ers Rebate',DEFBAL.Defined_balance_id))
515 ,max(decode(BTYPE.balance_name,'NI D Ees Rebate',DEFBAL.Defined_balance_id))
516 ,max(decode(BTYPE.balance_name,'NI F Ees Rebate',DEFBAL.Defined_balance_id))
517 ,max(decode(BTYPE.balance_name,'Employee COMP Min Payment',DEFBAL.Defined_balance_id))
518 ,max(decode(BTYPE.balance_name,'Employer COMP Min Payment',DEFBAL.Defined_balance_id))
519 into
520 g_ni_a_employee_defbal ,g_ni_b_employee_defbal ,g_ni_c_employee_defbal ,g_ni_d_employee_defbal ,g_ni_e_employee_defbal
521 ,g_ni_f_employee_defbal ,g_ni_g_employee_defbal ,g_ni_j_employee_defbal, g_ni_l_employee_defbal, g_ni_l_employee_not_defbal
522 ,g_ni_s_employee_defbal ,g_ni_s_employee_not_defbal
523 ,g_ni_a_employer_defbal ,g_ni_b_employer_defbal ,g_ni_c_employer_defbal ,g_ni_d_employer_defbal ,g_ni_e_employer_defbal
524 ,g_ni_f_employer_defbal ,g_ni_g_employer_defbal, g_ni_j_employer_defbal, g_ni_l_employer_defbal ,g_ni_s_employer_defbal
525 ,g_ni_a_able_defbal ,g_ni_a_able_lel_defbal, g_ni_a_able_uap_defbal, g_ni_a_able_uel_defbal, g_ni_a_able_et_defbal, g_ni_a_able_auel_defbal,
526 g_ni_a_ee_auel_defbal
527 ,g_ni_b_able_defbal ,g_ni_b_able_lel_defbal, g_ni_b_able_uap_defbal, g_ni_b_able_uel_defbal, g_ni_b_able_et_defbal, g_ni_b_able_auel_defbal,
528 g_ni_b_ee_auel_defbal
529 ,g_ni_c_able_defbal ,g_ni_c_able_lel_defbal, g_ni_c_able_uap_defbal, g_ni_c_able_uel_defbal, g_ni_c_able_et_defbal, g_ni_c_able_auel_defbal,
530 g_ni_c_ee_auel_defbal
531 ,g_ni_d_able_defbal ,g_ni_d_able_lel_defbal, g_ni_d_able_uap_defbal, g_ni_d_able_uel_defbal, g_ni_d_able_et_defbal, g_ni_d_able_auel_defbal,
532 g_ni_d_ee_auel_defbal
533 ,g_ni_e_able_defbal ,g_ni_e_able_lel_defbal, g_ni_e_able_uap_defbal, g_ni_e_able_uel_defbal, g_ni_e_able_et_defbal, g_ni_e_able_auel_defbal,
534 g_ni_e_ee_auel_defbal
535 ,g_ni_f_able_defbal ,g_ni_f_able_lel_defbal, g_ni_f_able_uap_defbal, g_ni_f_able_uel_defbal, g_ni_f_able_et_defbal, g_ni_f_able_auel_defbal,
536 g_ni_f_ee_auel_defbal
537 ,g_ni_g_able_defbal ,g_ni_g_able_lel_defbal, g_ni_g_able_uap_defbal, g_ni_g_able_uel_defbal, g_ni_g_able_et_defbal, g_ni_g_able_auel_defbal,
538 g_ni_g_ee_auel_defbal
539 ,g_ni_j_able_defbal ,g_ni_j_able_lel_defbal, g_ni_j_able_uap_defbal, g_ni_j_able_uel_defbal, g_ni_j_able_et_defbal, g_ni_j_able_auel_defbal,
540 g_ni_j_ee_auel_defbal
541 ,g_ni_l_able_defbal ,g_ni_l_able_lel_defbal, g_ni_l_able_uap_defbal, g_ni_l_able_uel_defbal, g_ni_l_able_et_defbal, g_ni_l_able_auel_defbal,
542 g_ni_l_ee_auel_defbal
543 ,g_ni_s_able_defbal ,g_ni_s_able_lel_defbal, g_ni_s_able_uap_defbal, g_ni_s_able_uel_defbal, g_ni_s_able_et_defbal, g_ni_s_able_auel_defbal,
544 g_ni_s_ee_auel_defbal
545 ,g_ni_c_co_able_defbal ,g_ni_d_co_able_defbal ,g_ni_e_co_able_defbal
546 ,g_ni_f_co_able_defbal ,g_ni_g_co_able_defbal ,g_ni_s_co_able_defbal
547 ,g_ni_c_ers_rebate_defbal ,g_ni_d_ers_rebate_defbal ,g_ni_e_ers_rebate_defbal
548 ,g_ni_f_ers_rebate_defbal ,g_ni_g_ers_rebate_defbal ,g_ni_s_ers_rebate_defbal
549 ,g_ni_d_ees_rebate_defbal ,g_ni_f_ees_rebate_defbal , g_comp_min_ees_defbal,
550 g_comp_min_ers_defbal
551 from
552 pay_balance_types BTYPE,
553 pay_defined_balances DEFBAL,
554 pay_balance_dimensions DIM
555 where DEFBAL.balance_dimension_id = DIM.balance_dimension_id
556 and DIM.database_item_suffix = p_database_item_suffix
557 and DIM.legislation_code = 'GB'
558 and DEFBAL.legislation_code = 'GB'
559 and defbal.balance_type_id = btype.balance_type_id
560 and BTYPE.legislation_code = 'GB';
561 --
562 if p_database_item_suffix = '_PER_TD_DIR_YTD' then
563 g_defined_director_set := TRUE;
564 g_defined_ptd_set := FALSE;
565 elsif p_database_item_suffix = '_PER_NI_PTD' then
566 g_defined_ptd_set := TRUE;
567 g_defined_director_set := FALSE;
568 end if;
569 --
570 end set_defined_balances;
571 --------------------------------------------------------------------------------
572 -- Procedure: set_balance_table
573 -- Description: Set up the balance list table with all the defined balance ids
574 -- that have been stored in the global variables.
575 -- This is so that the list is in the correct layout for Core BUE call.
576 ------------------------------------------------------------------------------
577 --
578 procedure set_balance_table(p_balance_list in out nocopy pay_balance_pkg.t_balance_value_tab)
579 --
580 is
581 begin
582 p_balance_list.delete;
583 --
584 p_balance_list(1).defined_balance_id := g_comp_min_ees_defbal;
585 p_balance_list(2).defined_balance_id := g_comp_min_ers_defbal;
586 p_balance_list(3).defined_balance_id := g_ni_a_employee_defbal;
587 p_balance_list(4).defined_balance_id := g_ni_b_employee_defbal;
588 p_balance_list(5).defined_balance_id := g_ni_c_employee_defbal;
589 p_balance_list(6).defined_balance_id := g_ni_d_employee_defbal;
590 p_balance_list(7).defined_balance_id := g_ni_e_employee_defbal;
591 p_balance_list(8).defined_balance_id := g_ni_f_employee_defbal;
592 p_balance_list(9).defined_balance_id := g_ni_g_employee_defbal;
593 p_balance_list(10).defined_balance_id := g_ni_j_employee_defbal;
594 p_balance_list(11).defined_balance_id := g_ni_l_employee_defbal;
595 p_balance_list(12).defined_balance_id := g_ni_l_employee_not_defbal;
596 p_balance_list(13).defined_balance_id := g_ni_s_employee_defbal;
597 p_balance_list(14).defined_balance_id := g_ni_s_employee_not_defbal;
598 p_balance_list(15).defined_balance_id := g_ni_a_employer_defbal;
599 p_balance_list(16).defined_balance_id := g_ni_b_employer_defbal;
600 p_balance_list(17).defined_balance_id := g_ni_c_employer_defbal;
601 p_balance_list(18).defined_balance_id := g_ni_d_employer_defbal;
602 p_balance_list(19).defined_balance_id := g_ni_e_employer_defbal;
603 p_balance_list(20).defined_balance_id := g_ni_f_employer_defbal;
604 p_balance_list(21).defined_balance_id := g_ni_g_employer_defbal;
605 p_balance_list(22).defined_balance_id := g_ni_j_employer_defbal;
606 p_balance_list(23).defined_balance_id := g_ni_l_employer_defbal;
607 p_balance_list(24).defined_balance_id := g_ni_s_employer_defbal;
608 p_balance_list(25).defined_balance_id := g_ni_a_able_defbal;
609 p_balance_list(26).defined_balance_id := g_ni_b_able_defbal;
610 p_balance_list(27).defined_balance_id := g_ni_c_able_defbal;
611 p_balance_list(28).defined_balance_id := g_ni_d_able_defbal;
612 p_balance_list(29).defined_balance_id := g_ni_e_able_defbal;
613 p_balance_list(30).defined_balance_id := g_ni_f_able_defbal;
614 p_balance_list(31).defined_balance_id := g_ni_g_able_defbal;
615 p_balance_list(32).defined_balance_id := g_ni_j_able_defbal;
616 p_balance_list(33).defined_balance_id := g_ni_l_able_defbal;
617 p_balance_list(34).defined_balance_id := g_ni_s_able_defbal;
618 p_balance_list(35).defined_balance_id := g_ni_c_co_able_defbal;
619 p_balance_list(36).defined_balance_id := g_ni_d_co_able_defbal;
620 p_balance_list(37).defined_balance_id := g_ni_e_co_able_defbal;
621 p_balance_list(38).defined_balance_id := g_ni_f_co_able_defbal;
622 p_balance_list(39).defined_balance_id := g_ni_g_co_able_defbal;
623 p_balance_list(40).defined_balance_id := g_ni_s_co_able_defbal;
624 p_balance_list(41).defined_balance_id := g_ni_a_able_lel_defbal;
625 p_balance_list(42).defined_balance_id := g_ni_a_able_uel_defbal;
626 p_balance_list(43).defined_balance_id := g_ni_a_able_et_defbal;
627 p_balance_list(44).defined_balance_id := g_ni_a_able_auel_defbal;
628 p_balance_list(45).defined_balance_id := g_ni_a_ee_auel_defbal;
629 p_balance_list(46).defined_balance_id := g_ni_b_able_lel_defbal;
630 p_balance_list(47).defined_balance_id := g_ni_b_able_uel_defbal;
631 p_balance_list(48).defined_balance_id := g_ni_b_able_et_defbal;
632 p_balance_list(49).defined_balance_id := g_ni_b_able_auel_defbal;
633 p_balance_list(50).defined_balance_id := g_ni_b_ee_auel_defbal;
634 p_balance_list(51).defined_balance_id := g_ni_c_able_lel_defbal;
635 p_balance_list(52).defined_balance_id := g_ni_c_able_uel_defbal;
636 p_balance_list(53).defined_balance_id := g_ni_c_able_et_defbal;
637 p_balance_list(54).defined_balance_id := g_ni_c_able_auel_defbal;
638 p_balance_list(55).defined_balance_id := g_ni_c_ee_auel_defbal;
639 p_balance_list(56).defined_balance_id := g_ni_d_able_lel_defbal;
640 p_balance_list(57).defined_balance_id := g_ni_d_able_uel_defbal;
641 p_balance_list(58).defined_balance_id := g_ni_d_able_et_defbal;
642 p_balance_list(59).defined_balance_id := g_ni_d_able_auel_defbal;
643 p_balance_list(60).defined_balance_id := g_ni_d_ee_auel_defbal;
644 p_balance_list(61).defined_balance_id := g_ni_e_able_lel_defbal;
645 p_balance_list(62).defined_balance_id := g_ni_e_able_uel_defbal;
646 p_balance_list(63).defined_balance_id := g_ni_e_able_et_defbal;
647 p_balance_list(64).defined_balance_id := g_ni_e_able_auel_defbal;
648 p_balance_list(65).defined_balance_id := g_ni_e_ee_auel_defbal;
649 p_balance_list(66).defined_balance_id := g_ni_f_able_lel_defbal;
650 p_balance_list(67).defined_balance_id := g_ni_f_able_uel_defbal;
651 p_balance_list(68).defined_balance_id := g_ni_f_able_et_defbal;
652 p_balance_list(69).defined_balance_id := g_ni_f_able_auel_defbal;
653 p_balance_list(70).defined_balance_id := g_ni_f_ee_auel_defbal;
654 p_balance_list(71).defined_balance_id := g_ni_g_able_lel_defbal;
655 p_balance_list(72).defined_balance_id := g_ni_g_able_uel_defbal;
656 p_balance_list(73).defined_balance_id := g_ni_g_able_et_defbal;
657 p_balance_list(74).defined_balance_id := g_ni_g_able_auel_defbal;
658 p_balance_list(75).defined_balance_id := g_ni_g_ee_auel_defbal;
659 p_balance_list(76).defined_balance_id := g_ni_j_able_lel_defbal;
660 p_balance_list(77).defined_balance_id := g_ni_j_able_uel_defbal;
661 p_balance_list(78).defined_balance_id := g_ni_j_able_et_defbal;
662 p_balance_list(79).defined_balance_id := g_ni_j_able_auel_defbal;
663 p_balance_list(80).defined_balance_id := g_ni_j_ee_auel_defbal;
664 p_balance_list(81).defined_balance_id := g_ni_l_able_lel_defbal;
665 p_balance_list(82).defined_balance_id := g_ni_l_able_uel_defbal;
666 p_balance_list(83).defined_balance_id := g_ni_l_able_et_defbal;
667 p_balance_list(84).defined_balance_id := g_ni_l_able_auel_defbal;
668 p_balance_list(85).defined_balance_id := g_ni_l_ee_auel_defbal;
669 p_balance_list(86).defined_balance_id := g_ni_s_able_lel_defbal;
670 p_balance_list(87).defined_balance_id := g_ni_s_able_uel_defbal;
671 p_balance_list(88).defined_balance_id := g_ni_s_able_et_defbal;
672 p_balance_list(89).defined_balance_id := g_ni_s_able_auel_defbal;
673 p_balance_list(90).defined_balance_id := g_ni_s_ee_auel_defbal;
674 p_balance_list(91).defined_balance_id := g_ni_c_ers_rebate_defbal;
675 p_balance_list(92).defined_balance_id := g_ni_d_ers_rebate_defbal;
676 p_balance_list(93).defined_balance_id := g_ni_d_ees_rebate_defbal;
677 p_balance_list(94).defined_balance_id := g_ni_e_ers_rebate_defbal;
678 p_balance_list(95).defined_balance_id := g_ni_f_ers_rebate_defbal;
679 p_balance_list(96).defined_balance_id := g_ni_f_ees_rebate_defbal;
680 p_balance_list(97).defined_balance_id := g_ni_g_ers_rebate_defbal;
681 p_balance_list(98).defined_balance_id := g_ni_s_ers_rebate_defbal;
682 --EOY 08/09 Begin
683 p_balance_list(99).defined_balance_id := g_ni_a_able_uap_defbal;
684 p_balance_list(100).defined_balance_id := g_ni_b_able_uap_defbal;
685 p_balance_list(101).defined_balance_id := g_ni_c_able_uap_defbal;
686 p_balance_list(102).defined_balance_id := g_ni_d_able_uap_defbal;
687 p_balance_list(103).defined_balance_id := g_ni_e_able_uap_defbal;
688 p_balance_list(104).defined_balance_id := g_ni_f_able_uap_defbal;
689 p_balance_list(105).defined_balance_id := g_ni_g_able_uap_defbal;
690 p_balance_list(106).defined_balance_id := g_ni_j_able_uap_defbal;
691 p_balance_list(107).defined_balance_id := g_ni_l_able_uap_defbal;
692 p_balance_list(108).defined_balance_id := g_ni_s_able_uap_defbal;
693 --EOY 08/09 End
694
695 end set_balance_table;
696 ------------------------------------------------------------------------------
697 -- Procedure: set_balance_values
698 -- Description: set the global balance values from the balance table
699 -- after the table has been returned by the batch-mode call to core BUE.
700 ------------------------------------------------------------------------------
701 procedure set_balance_values(p_balance_list in pay_balance_pkg.t_balance_value_tab)
702 is
703 begin
704 g_comp_min_ees := p_balance_list(1).balance_value;
705 g_comp_min_ers := p_balance_list(2).balance_value;
706 g_ni_a_employee := p_balance_list(3).balance_value;
707 g_ni_b_employee := p_balance_list(4).balance_value;
708 g_ni_c_employee := p_balance_list(5).balance_value;
709 g_ni_d_employee := p_balance_list(6).balance_value;
710 g_ni_e_employee := p_balance_list(7).balance_value;
711 g_ni_f_employee := p_balance_list(8).balance_value;
712 g_ni_g_employee := p_balance_list(9).balance_value;
713 g_ni_j_employee := p_balance_list(10).balance_value;
714 g_ni_l_employee := p_balance_list(11).balance_value;
715 g_ni_l_employee_not := p_balance_list(12).balance_value;
716 g_ni_s_employee := p_balance_list(13).balance_value;
717 g_ni_s_employee_not := p_balance_list(14).balance_value;
718 g_ni_a_employer := p_balance_list(15).balance_value;
719 g_ni_b_employer := p_balance_list(16).balance_value;
720 g_ni_c_employer := p_balance_list(17).balance_value;
721 g_ni_d_employer := p_balance_list(18).balance_value;
722 g_ni_e_employer := p_balance_list(19).balance_value;
723 g_ni_f_employer := p_balance_list(20).balance_value;
724 g_ni_g_employer := p_balance_list(21).balance_value;
725 g_ni_j_employer := p_balance_list(22).balance_value;
726 g_ni_l_employer := p_balance_list(23).balance_value;
727 g_ni_s_employer := p_balance_list(24).balance_value;
728 g_ni_a_able := p_balance_list(25).balance_value;
729 g_ni_b_able := p_balance_list(26).balance_value;
730 g_ni_c_able := p_balance_list(27).balance_value;
731 g_ni_d_able := p_balance_list(28).balance_value;
732 g_ni_e_able := p_balance_list(29).balance_value;
733 g_ni_f_able := p_balance_list(30).balance_value;
734 g_ni_g_able := p_balance_list(31).balance_value;
735 g_ni_j_able := p_balance_list(32).balance_value;
736 g_ni_l_able := p_balance_list(33).balance_value;
737 g_ni_s_able := p_balance_list(34).balance_value;
738 g_ni_c_co_able := p_balance_list(35).balance_value;
739 g_ni_d_co_able := p_balance_list(36).balance_value;
740 g_ni_e_co_able := p_balance_list(37).balance_value;
741 g_ni_f_co_able := p_balance_list(38).balance_value;
742 g_ni_g_co_able := p_balance_list(39).balance_value;
743 g_ni_s_co_able := p_balance_list(40).balance_value;
744 g_ni_a_able_lel := p_balance_list(41).balance_value;
745 g_ni_a_able_uel := p_balance_list(42).balance_value;
746 g_ni_a_able_et := p_balance_list(43).balance_value;
747 g_ni_a_able_auel := p_balance_list(44).balance_value;
748 g_ni_a_ee_auel := p_balance_list(45).balance_value;
749 g_ni_b_able_lel := p_balance_list(46).balance_value;
750 g_ni_b_able_uel := p_balance_list(47).balance_value;
751 g_ni_b_able_et := p_balance_list(48).balance_value;
752 g_ni_b_able_auel := p_balance_list(49).balance_value;
753 g_ni_b_ee_auel := p_balance_list(50).balance_value;
754 g_ni_c_able_lel := p_balance_list(51).balance_value;
755 g_ni_c_able_uel := p_balance_list(52).balance_value;
756 g_ni_c_able_et := p_balance_list(53).balance_value;
757 g_ni_c_able_auel := p_balance_list(54).balance_value;
758 g_ni_c_ee_auel := p_balance_list(55).balance_value;
759 g_ni_d_able_lel := p_balance_list(56).balance_value;
760 g_ni_d_able_uel := p_balance_list(57).balance_value;
761 g_ni_d_able_et := p_balance_list(58).balance_value;
762 g_ni_d_able_auel := p_balance_list(59).balance_value;
763 g_ni_d_ee_auel := p_balance_list(60).balance_value;
764 g_ni_e_able_lel := p_balance_list(61).balance_value;
765 g_ni_e_able_uel := p_balance_list(62).balance_value;
766 g_ni_e_able_et := p_balance_list(63).balance_value;
767 g_ni_e_able_auel := p_balance_list(64).balance_value;
768 g_ni_e_ee_auel := p_balance_list(65).balance_value;
769 g_ni_f_able_lel := p_balance_list(66).balance_value;
770 g_ni_f_able_uel := p_balance_list(67).balance_value;
771 g_ni_f_able_et := p_balance_list(68).balance_value;
772 g_ni_f_able_auel := p_balance_list(69).balance_value;
773 g_ni_f_ee_auel := p_balance_list(70).balance_value;
774 g_ni_g_able_lel := p_balance_list(71).balance_value;
775 g_ni_g_able_uel := p_balance_list(72).balance_value;
776 g_ni_g_able_et := p_balance_list(73).balance_value;
777 g_ni_g_able_auel := p_balance_list(74).balance_value;
778 g_ni_g_ee_auel := p_balance_list(75).balance_value;
779 g_ni_j_able_lel := p_balance_list(76).balance_value;
780 g_ni_j_able_uel := p_balance_list(77).balance_value;
781 g_ni_j_able_et := p_balance_list(78).balance_value;
782 g_ni_j_able_auel := p_balance_list(79).balance_value;
783 g_ni_j_ee_auel := p_balance_list(80).balance_value;
784 g_ni_l_able_lel := p_balance_list(81).balance_value;
785 g_ni_l_able_uel := p_balance_list(82).balance_value;
786 g_ni_l_able_et := p_balance_list(83).balance_value;
787 g_ni_l_able_auel := p_balance_list(84).balance_value;
788 g_ni_l_ee_auel := p_balance_list(85).balance_value;
789 g_ni_s_able_lel := p_balance_list(86).balance_value;
790 g_ni_s_able_uel := p_balance_list(87).balance_value;
791 g_ni_s_able_et := p_balance_list(88).balance_value;
792 g_ni_s_able_auel := p_balance_list(89).balance_value;
793 g_ni_s_ee_auel := p_balance_list(90).balance_value;
794 g_ni_c_ers_rebate := p_balance_list(91).balance_value;
795 g_ni_d_ers_rebate := p_balance_list(92).balance_value;
796 g_ni_d_ees_rebate := p_balance_list(93).balance_value;
797 g_ni_e_ers_rebate := p_balance_list(94).balance_value;
798 g_ni_f_ers_rebate := p_balance_list(95).balance_value;
799 g_ni_f_ees_rebate := p_balance_list(96).balance_value;
800 g_ni_g_ers_rebate := p_balance_list(97).balance_value;
801 g_ni_s_ers_rebate := p_balance_list(98).balance_value;
802 --EOY 08/09 Begin
803 g_ni_a_able_uap := p_balance_list(99).balance_value;
804 g_ni_b_able_uap := p_balance_list(100).balance_value;
805 g_ni_c_able_uap := p_balance_list(101).balance_value;
806 g_ni_d_able_uap := p_balance_list(102).balance_value;
807 g_ni_e_able_uap := p_balance_list(103).balance_value;
808 g_ni_f_able_uap := p_balance_list(104).balance_value;
809 g_ni_g_able_uap := p_balance_list(105).balance_value;
810 g_ni_j_able_uap := p_balance_list(106).balance_value;
811 g_ni_l_able_uap := p_balance_list(107).balance_value;
812 g_ni_s_able_uap := p_balance_list(108).balance_value;
813 --EOY 08/09 End
814 --
815 end set_balance_values;
816
817 ------------------------------------------------------------------------------
818 -- NI_ABLE_DIR_YTD --
819 -- find the NIable Pay balance for a particular category for director
820 -- This function is now obsolete so blanked out, and zero returned. This
821 -- is not called by any UK formula, package, report or form. The values
822 -- here can be obtained using ni_balances_per_dir_td_ytd.
823 --
824 --------------------------------------------------------------------------------
825
826 function ni_able_dir_ytd
827 (
828 p_assignment_action_id IN number ,
829 p_category IN varchar2 ,
830 p_pension IN varchar2
831 )
832 return number is
833 begin
834 --
835 RETURN 0;
836 --
837 end ni_able_dir_ytd;
838 --
839 --------------------------------------------------------------------------------
840 -- NI_BALANCES_PER_DIR_TD_YTD --
841 -- get all of the NI balances for an assignment in one select
842 --
843 --------------------------------------------------------------------------------
844 --
845
846 function NI_BALANCES_PER_DIR_TD_YTD
847 (
848 p_assignment_action_id IN number,
849 p_global_name IN varchar2
850 )
851 return number is
852 --
853 -- N.B. When called from FastFormula, p_assignment_action_id
854 -- provided via context-set variables.
855 --
856 l_stat_period_start date;
857 l_start_director date;
858 l_bact_effective_date date;
859 l_assignment_id number;
860 l_balance_value number;
861 l_balance_list pay_balance_pkg.t_balance_value_tab;
862 --
863 Begin
864 --
865 -- Set up the defined balances for all NI balances _PER_TD_DIR_YTD
866 if g_defined_director_set = FALSE then
867 hr_utility.trace('Calling set_defined_balances');
868 set_defined_balances('_PER_TD_DIR_YTD');
869 end if;
870 hr_utility.trace('example defbal:'||to_char(g_comp_min_ers_defbal));
871 --
872 if g_ni_able_id is null then
873 select balance_type_id
874 into g_ni_able_id
875 from pay_balance_types
876 where balance_name = 'NIable Pay';
877 --
878 select element_type_id
879 into g_ni_element_type_id
880 from pay_element_types_f
881 where element_name = 'NI'
882 and sysdate between effective_start_date
883 and effective_end_date;
884 --
885 select input_value_id
886 into g_cat_input_id
887 from pay_input_values_f
888 where name = 'Category'
889 and element_type_id = g_ni_element_type_id
890 and sysdate between effective_start_date
891 and effective_end_date;
892 --
893 select input_value_id
894 into g_pen_input_id
895 from pay_input_values_f
896 where name = 'Pension'
897 and element_type_id = g_ni_element_type_id
898 and sysdate between effective_start_date
899 and effective_end_date;
900 end if;
901 --
902 -- find the start of the financial year and the start of the directorship
903 select BACT.effective_date, BAL_ASSACT.assignment_id
904 into l_bact_effective_date, l_assignment_id
905 from pay_payroll_actions BACT,
906 pay_assignment_actions BAL_ASSACT
907 where BAL_ASSACT.assignment_action_id = p_assignment_action_id
908 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id;
909
910 l_stat_period_start := hr_gbbal.span_start(l_bact_effective_date, 1, '06-04-');
911 l_start_director := hr_gbbal.start_director(l_assignment_id,
912 l_stat_period_start,
913 l_bact_effective_date);
914 --
915 -- if the assignment_action_id has changed from the last call or is null,
916 -- calculate the balances via route-code before calling the global function.
917 -- Added JN and LC for 2003 Legislation.
918 --
919 -- NI ABLE CURSOR, USE THIS RATHER THAN CORE BUE DUE TO DIFFERING ROUTE ETC.
920 --
921 IF g_assignment_action_id <> p_assignment_action_id
922 OR g_assignment_action_id is null then
923 select /*+ ORDERED INDEX(BAL_ASSACT PAY_ASSIGNMENT_ACTIONS_PK,
924 BACT PAY_PAYROLL_ACTIONS_PK,
925 BPTP PER_TIME_PERIODS_PK,
926 START_ASS PER_ASSIGNMENTS_F_PK,
927 ASS PER_ASSIGNMENTS_F_N12,
928 ASSACT PAY_ASSIGNMENT_ACTIONS_N51,
929 PACT PAY_PAYROLL_ACTIONS_PK,
930 PPTP PER_TIME_PERIODS_PK ,
931 RR PAY_RUN_RESULTS_N50,
932 TARGET PAY_RUN_RESULT_VALUES_PK,
933 FEED PAY_BALANCE_FEEDS_F_UK2 )
934 USE_NL(BAL_ASSACT,BACT,BPTP,START_ASS,ASS,ASSACT,PACT,PPTP,RR,TARGET,FEED) +*/
935 nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
936 'AN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
937 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
938 'AA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
939 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
940 'BN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
941 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
942 'BA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
943 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
944 'CN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
945 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
946 'CC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
947 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
948 'DC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
949 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
950 'EC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
951 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
952 'FM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
953 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
954 'GM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
955 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
956 'JN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
957 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
958 'JA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
959 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
960 'LC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
961 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
962 'SM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
963 into g_st_ni_a_able , g_st_ni_ap_able , g_st_ni_b_able ,
964 g_st_ni_bp_able, g_st_ni_c_able , g_st_ni_co_able ,
965 g_st_ni_d_able , g_st_ni_e_able , g_st_ni_f_able ,
966 g_st_ni_g_able , g_st_ni_j_able, g_st_ni_jp_able,
967 g_st_ni_l_able, g_st_ni_s_able
968 from
969 pay_assignment_actions BAL_ASSACT
970 ,pay_payroll_actions BACT
971 ,per_time_periods BPTP
972 ,per_all_assignments_f START_ASS
973 ,per_all_assignments_f ASS
974 ,pay_assignment_actions ASSACT
975 ,pay_payroll_actions PACT
976 ,per_time_periods PPTP
977 ,pay_run_results RR
978 ,pay_run_result_values TARGET
979 ,pay_balance_feeds_f FEED
980 where BAL_ASSACT.assignment_action_id = p_assignment_action_id
981 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
982 and FEED.balance_type_id = g_ni_able_id + decode(RR.run_result_id, null, 0, 0)
983 and nvl(TARGET.result_value,'0') <> '0'
984 and FEED.input_value_id = TARGET.input_value_id
985 and TARGET.run_result_id = RR.run_result_id
986 and RR.assignment_action_id = ASSACT.assignment_action_id
987 and ASSACT.payroll_action_id = PACT.payroll_action_id
988 and PACT.effective_date between
989 FEED.effective_start_date and FEED.effective_end_date
990 and RR.status in ('P','PA')
991 and BPTP.time_period_id = BACT.time_period_id
992 and PPTP.time_period_id = PACT.time_period_id
993 and START_ASS.assignment_id = BAL_ASSACT.assignment_id
994 and ASS.person_id = START_ASS.person_id /* person level not pos */
995 and ASSACT.assignment_id = ASS.assignment_id
996 and BACT.effective_date between
997 START_ASS.effective_start_date and START_ASS.effective_end_date
998 and PACT.effective_date between
999 ASS.effective_start_date and ASS.effective_end_date
1000 and PACT.effective_date >=
1001 /* find the latest td payroll transfer date - compare each of the */
1002 /* assignment rows with its predecessor looking for the payroll */
1003 /* that had a different tax district at that date */
1004 ( select /*+ ORDERED INDEX (NASS PER_ASSIGNMENTS_F_PK,
1005 PASS PER_ASSIGNMENTS_F_PK,
1006 ROLL PAY_PAYROLLS_F_PK,
1007 FLEX HR_SOFT_CODING_KEYFLEX_PK,
1008 PROLL PAY_PAYROLLS_F_PK,
1009 PFLEX HR_SOFT_CODING_KEYFLEX_PK)
1010 USE_NL(NASS,PASS,ROLL,FLEX,PROLL,PFLEX) +*/
1011 nvl(max(NASS.effective_start_date), to_date('01-01-0001','DD-MM-YYYY'))
1012 from per_all_assignments_f NASS
1013 ,per_all_assignments_f PASS
1014 ,pay_all_payrolls_f ROLL
1015 ,hr_soft_coding_keyflex FLEX
1016 ,pay_all_payrolls_f PROLL
1017 ,hr_soft_coding_keyflex PFLEX
1018 where NASS.assignment_id = ASS.assignment_id
1019 and ROLL.payroll_id = NASS.payroll_id
1020 and NASS.effective_start_date between
1021 ROLL.effective_start_date and ROLL.effective_end_date
1022 and ROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
1023 and NASS.assignment_id = PASS.assignment_id
1024 and PASS.effective_end_date = (NASS.effective_start_date - 1)
1025 and NASS.effective_start_date <= BACT.effective_date
1026 and PROLL.payroll_id = PASS.payroll_id
1027 and NASS.effective_start_date between
1028 PROLL.effective_start_date and PROLL.effective_end_date
1029 and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
1030 and NASS.payroll_id <> PASS.payroll_id
1031 and FLEX.segment1 <> PFLEX.segment1
1032 )
1033 and exists ( select null from
1034 /* check that the current assignment tax districts match */
1035 pay_all_payrolls_f BROLL
1036 ,hr_soft_coding_keyflex BFLEX
1037 ,pay_all_payrolls_f PROLL
1038 ,hr_soft_coding_keyflex PFLEX
1039 where BACT.payroll_id = BROLL.payroll_id
1040 and PACT.payroll_id = PROLL.payroll_id
1041 and BFLEX.soft_coding_keyflex_id = BROLL.soft_coding_keyflex_id
1042 and PFLEX.soft_coding_keyflex_id = PROLL.soft_coding_keyflex_id
1043 and BACT.effective_date between
1044 BROLL.effective_start_date and BROLL.effective_end_date
1045 and BACT.effective_date between
1046 PROLL.effective_start_date and PROLL.effective_end_date
1047 and BFLEX.segment1 = PFLEX.segment1
1048 )
1049 and PPTP.regular_payment_date >= l_stat_period_start
1050 and PACT.effective_date >= l_start_director
1051 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence;
1052 --
1053 -- MAIN NI SELECTION, USE CORE BUE
1054 -- Set the l_balance_list table (once per assignment action) for this
1055 -- assignment action, then call the core BUE in batch mode.
1056 -- Then set the global balance values so that they remain the same for the
1057 -- current assignment. This uses the in out param l_balance_list.
1058 --
1059 set_balance_table(l_balance_list);
1060 --
1061 -- Call the Core BUE in BATCH MODE with the above set of defined balances,
1062 -- the resulting values will be stored in the globals as before.
1063 -- Exception handle this in case of NO DATA FOUND, which can be invoked
1064 -- by this call only if the Defined balance id is not found. These are
1065 -- all seeded.
1066 --
1067 BEGIN
1068 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
1069 p_defined_balance_lst => l_balance_list);
1070 EXCEPTION WHEN NO_DATA_FOUND THEN
1071 hr_utility.trace('No data found on call to pay_balance_pkg');
1072 null;
1073 END;
1074 --
1075 -- Set the Global variables to the values just retrieved from the bulk call to
1076 -- Core BUE.
1077 set_balance_values(l_balance_list);
1078 --
1079 -- Set the global assignment action for the next call
1080 --
1081 g_assignment_action_id := p_assignment_action_id;
1082 --
1083 END IF;
1084 --
1085 -- Calculate the value from the global-retrieval function
1086 -- passing in the global name. This function is called
1087 -- in all cases, as at this point all globals are set.
1088 --
1089 l_balance_value := get_plsql_global(p_global_name);
1090 --
1091 RETURN l_balance_value;
1092 --
1093 end NI_BALANCES_PER_DIR_TD_YTD;
1094 --
1095 --------------------------------------------------------------------------------
1096 --
1097 -- DIRECTOR_WEEKS --
1098 -- fin how many weeks this assignment has been a director
1099 -- 1) since start of directorship
1100 -- 2) since tax district transfer
1101 -- 3) since the start of payroll year
1102 --
1103 --------------------------------------------------------------------------------
1104 --
1105
1106 function director_weeks
1107 (
1108 p_assignment_id IN number
1109 )
1110 return number is
1111 --
1112 -- N.B. When called from FastFormula, p_assignment_id
1113 -- provided via context-set variables.
1114 l_start_of_director_date date;
1115 l_weeks number;
1116 l_tax_year_start date;
1117 l_effective_date date;
1118 --
1119 Begin
1120 --
1121 select effective_date
1122 into l_effective_date
1123 from fnd_sessions
1124 where session_id = userenv('sessionid');
1125 --
1126 -- find the previous 6-Apr
1127 select to_date('06-04-' || to_char( to_number(
1128 to_char( l_effective_date,'YYYY'))
1129 + decode(sign( l_effective_date - to_date('06-04-'
1130 || to_char(l_effective_date,'YYYY'),'DD-MM-YYYY')),
1131 -1,-1,0)),'DD-MM-YYYY') finyear
1132 into l_tax_year_start
1133 from dual;
1134 --
1135 /* has this person been a director this financial year */
1136
1137 select nvl(min(p.effective_start_date)
1138 ,to_date('31-12-4712','dd-mm-yyyy'))
1139 into l_start_of_director_date
1140 from per_people_f p,
1141 per_assignments_f ASS
1142 where p.per_information2 = 'Y'
1143 and ASS.assignment_id = p_assignment_id
1144 and l_effective_date between
1145 ASS.effective_start_date and ASS.effective_end_date
1146 and ASS.person_id = P.person_id
1147 and P.effective_start_date <= l_effective_date
1148 and p.effective_end_date >= l_tax_year_start ;
1149 --
1150 -- calculate number of weeks of directorship
1151 select 52 - greatest(0,least(52,trunc(( l_start_of_director_date
1152 - l_tax_year_start)/7)))
1153 into l_weeks
1154 from dual;
1155 --
1156 --
1157 RETURN l_weeks;
1158 --
1159 end director_weeks;
1160 --
1161 -------------------------------------------------------------------------------
1162 -- VALIDATE_USER_VALUE
1163 -- check that a value is in the user table
1164 function validate_user_value
1165 ( p_user_table IN varchar2,
1166 p_user_column IN varchar2,
1167 p_user_value IN varchar2
1168 )
1169 return number is
1170 --
1171 l_valid number ;
1172 Begin
1173 --
1174 --
1175 -- initialize flag that indicates a valid value entered
1176 -- l_valid := 0;
1177 select nvl(max(1),0) into l_valid
1178 from pay_user_column_instances CINST
1179 , pay_user_columns C
1180 , pay_user_rows R
1181 , pay_user_tables TAB
1182 where TAB.user_table_name = p_user_table
1183 and C.user_column_name = p_user_column
1184 and fnd_number.canonical_to_number(CINST.value)
1185 = fnd_number.canonical_to_number(p_user_value)
1186 and C.user_table_id = TAB.user_table_id
1187 and CINST.user_column_id = C.user_column_id
1188 and R.user_table_id = TAB.user_table_id
1189 and TAB.user_key_units = 'N'
1190 and CINST.user_row_id = R.user_row_id;
1191 --
1192 RETURN l_valid;
1193 --
1194 end validate_user_value;
1195 --
1196 -------------------------------------------------------------------------------
1197 -- USER_VALUE_BY_LABEL
1198 -- find a value from a user table by keying on the label column
1199 --
1200 function user_value_by_label
1201 ( p_user_table IN varchar2,
1202 p_user_column IN varchar2,
1203 p_label IN varchar2
1204 )
1205 return number is
1206 --
1207 l_value number;
1208 Begin
1209 l_value := 0;
1210 begin
1211 select fnd_number.canonical_to_number(CINST.value) into l_value from
1212 pay_user_column_instances CINST
1213 , pay_user_column_instances LABEL
1214 , pay_user_columns CLABEL
1215 , pay_user_columns C
1216 , pay_user_rows R
1217 , pay_user_tables TAB
1218 where TAB.user_table_name = p_user_table
1219 and C.user_column_name = p_user_column
1220 and C.user_table_id = TAB.user_table_id
1221 and CINST.user_column_id = C.user_column_id
1222 and R.user_table_id = TAB.user_table_id
1223 and TAB.user_key_units = 'N'
1224 and CINST.user_row_id = R.user_row_id
1225 and LABEL.value = p_label
1226 and CLABEL.user_column_name = 'LABEL'
1227 and CLABEL.user_column_id = LABEL.user_column_id
1228 and CLABEL.user_table_id = TAB.user_table_id
1229 and LABEL.user_row_id = R.user_row_id;
1230 --
1231 exception when NO_DATA_FOUND then
1232 l_value := null;
1233 end;
1234
1235 RETURN l_value ;
1236 --
1237 end user_value_by_label;
1238 --
1239 -------------------------------------------------------------------------------
1240 -- USER_RANGE_BY_LABEL
1241 -- find the high or low of the row identified by the LABEL
1242 function user_range_by_label
1243 ( p_user_table IN varchar2,
1244 p_high_or_low IN varchar2,
1245 p_label IN varchar2)
1246 return number is
1247 --
1248 l_value number;
1249 Begin
1250 l_value := 0;
1251 --
1252 begin
1253 select decode(substr(p_high_or_low,1,1),
1254 'H',fnd_number.canonical_to_number(R.ROW_HIGH_RANGE),
1255 fnd_number.canonical_to_number(R.row_low_range_or_name))
1256 into l_value from
1257 pay_user_column_instances LABEL
1258 , pay_user_columns CLABEL
1259 , pay_user_rows R
1260 , pay_user_tables TAB
1261 where TAB.user_table_name = p_user_table
1262 and R.user_table_id = TAB.user_table_id
1263 and TAB.user_key_units = 'N'
1264 and LABEL.value = p_label
1265 and CLABEL.user_column_name = 'LABEL'
1266 and CLABEL.user_column_id = LABEL.user_column_id
1267 and CLABEL.user_table_id = TAB.user_table_id
1268 and LABEL.user_row_id = R.user_row_id;
1269 --
1270 exception when NO_DATA_FOUND then
1271 l_value := null;
1272 end;
1273 --
1274 RETURN l_value ;
1275 --
1276 end user_range_by_label;
1277 --
1278 --
1279 -------------------------------------------------------------------------------
1280 -- NI_CO_RATE_FROM_CI_RATE
1281 -- given the contracted in rate find the contracted out rate
1282 function ni_co_rate_from_ci_rate
1283 ( p_ci_rate IN number)
1284 return number is
1285 --
1286 l_value number;
1287 Begin
1288 l_value := 0;
1289 --
1290 select min(fnd_number.canonical_to_number(CINST.value))
1291 into l_value from
1292 pay_user_column_instances CINST
1293 , pay_user_column_instances LABEL
1294 , pay_user_columns CLABEL
1295 , pay_user_columns C
1296 , pay_user_rows R
1297 , pay_user_tables TAB
1298 where upper(TAB.user_table_name) = 'NI_ERS_WEEKLY'
1299 and C.user_column_name = 'C_ERS_RATE_CO'
1300 and C.user_table_id = TAB.user_table_id
1301 and CINST.user_column_id = C.user_column_id
1302 and R.user_table_id = TAB.user_table_id
1303 and TAB.user_key_units = 'N'
1304 and CINST.user_row_id = R.user_row_id
1305 and fnd_number.canonical_to_number(LABEL.value)
1306 = fnd_number.canonical_to_number(p_ci_rate)
1307 and CLABEL.user_column_name = 'C_ERS_RATE_CI'
1308 and CLABEL.user_column_id = LABEL.user_column_id
1309 and CLABEL.user_table_id = TAB.user_table_id
1310 and LABEL.user_row_id = R.user_row_id;
1311 --
1312 RETURN l_value ;
1313 --
1314 end ni_co_rate_from_ci_rate;
1315 -------------------------------------------------------------------------------
1316 -- NI_CM_RATE_FROM_CI_RATE
1317 -- given the contracted in rate find the contracted out comp rate
1318 function ni_cm_rate_from_ci_rate
1319 ( p_ci_rate IN number)
1320 return number is
1321 --
1322 l_value number;
1323 Begin
1324 l_value := 0;
1325 --
1326 select min(fnd_number.canonical_to_number(CINST.value))
1327 into l_value from
1328 pay_user_column_instances CINST
1329 , pay_user_column_instances LABEL
1330 , pay_user_columns CLABEL
1331 , pay_user_columns C
1332 , pay_user_rows R
1333 , pay_user_tables TAB
1334 where upper(TAB.user_table_name) = 'NI_ERS_WEEKLY'
1335 and C.user_column_name = 'C_ERS_RATE_CM'
1336 and C.user_table_id = TAB.user_table_id
1337 and CINST.user_column_id = C.user_column_id
1338 and R.user_table_id = TAB.user_table_id
1339 and TAB.user_key_units = 'N'
1340 and CINST.user_row_id = R.user_row_id
1341 and fnd_number.canonical_to_number(LABEL.value) = fnd_number.canonical_to_number(p_ci_rate)
1342 and CLABEL.user_column_name = 'C_ERS_RATE_CI'
1343 and CLABEL.user_column_id = LABEL.user_column_id
1344 and CLABEL.user_table_id = TAB.user_table_id
1345 and LABEL.user_row_id = R.user_row_id;
1346 --
1347 RETURN l_value ;
1348 --
1349 end ni_cm_rate_from_ci_rate;
1350 --
1351 -------------------------------------------------------------------------------
1352 -- STATUTORY_PERIOD_START_DATE ELEMENT
1353 -- find the start of the statutory period for a assignment action
1354 -- 1) check the period type on the element entry
1355 -- 2) if not fornd get the period type of the payroll
1356 -- 3) find the statutory period start date
1357 function STATUTORY_PERIOD_START_DATE
1358 ( p_assignment_action_id IN number )
1359 return date is
1360 --
1361 l_date date ;
1362 l_tax_year_start date;
1363 f_year number(4);
1364 f_start_dd_mon varchar2(7) := '06-04-';
1365 l_freq number;
1366 l_effective_date date;
1367 l_assignment_id number;
1368 l_element_type_id number;
1369 l_period_type_id number;
1370 l_payroll_period_type varchar2(30);
1371 l_period_type varchar2(30);
1372
1373 -- Start bug#7670451
1374
1375 cursor csr_get_asg_period_type ( c_period_type_id number
1376 ,c_assignment_id number
1377 ,c_element_type_id number
1378 ,c_effective_date date)
1379 IS
1380 select ENT_PT.screen_entry_value
1381 from pay_element_entry_values_f ENT_PT
1382 ,pay_element_entries_f ENT
1383 ,pay_element_links_f EL
1384 where ENT_PT.input_value_id + 0 = c_period_type_id
1385 and ENT_PT.element_entry_id = ENT.element_entry_id
1386 and ENT.assignment_id = c_assignment_id
1387 and EL.element_type_id = c_element_type_id
1388 and EL.element_link_id = ENT.element_link_id
1389 and c_effective_date between
1390 EL.effective_start_date and EL.effective_end_date
1391 and c_effective_date between
1392 ENT_PT.effective_start_date and ENT_PT.effective_end_date
1393 and c_effective_date between
1394 ENT.effective_start_date and ENT.effective_end_date;
1395 -- End bug#7670451
1396
1397 Begin
1398 if g_assignment_action_id = p_assignment_action_id then
1399 l_date := g_statutory_period_start_date;
1400 else
1401 -- find the assignment, effective_date and payroll period type
1402 select act.assignment_id,
1403 ptp.regular_payment_date,
1404 ptp.period_type
1405 into l_assignment_id,
1406 l_effective_date,
1407 l_payroll_period_type
1408 from pay_assignment_actions act,
1409 pay_payroll_actions pact,
1410 per_time_periods ptp
1411 where act.assignment_action_id = p_assignment_action_id
1412 and pact.payroll_action_id = act.payroll_action_id
1413 and ptp.time_period_id = pact.time_period_id;
1414 --
1415 -- Get the id's of the NI startup data
1416 select element_type_id
1417 into l_element_type_id
1418 from pay_element_types_f
1419 where element_name = 'NI'
1420 and l_effective_date between
1421 effective_start_date and effective_end_date;
1422 --
1423 select input_value_id
1424 into l_period_type_id
1425 from pay_input_values_f
1426 where name = 'Priority Period Type'
1427 and element_type_id = l_element_type_id
1428 and l_effective_date between
1429 effective_start_date and effective_end_date;
1430 --
1431 --
1432 -- find the assignments period type
1433 /* select ENT_PT.screen_entry_value
1434 into l_period_type
1435 from pay_element_entry_values_f ENT_PT
1436 ,pay_element_entries_f ENT
1437 ,pay_element_links_f EL
1438 where ENT_PT.input_value_id + 0 = l_period_type_id
1439 and ENT_PT.element_entry_id = ENT.element_entry_id
1440 and ENT.assignment_id = l_assignment_id
1441 and EL.element_type_id = l_element_type_id
1442 and EL.element_link_id = ENT.element_link_id
1443 and l_effective_date between
1444 EL.effective_start_date and EL.effective_end_date
1445 and l_effective_date between
1446 ENT_PT.effective_start_date and ENT_PT.effective_end_date
1447 and l_effective_date between
1448 ENT.effective_start_date and ENT.effective_end_date;*/
1449 -- Start bug#7670451
1450 -- Modified select query to cursor to avoid No data found error.
1451 open csr_get_asg_period_type ( l_period_type_id
1452 ,l_assignment_id
1453 ,l_element_type_id
1454 ,l_effective_date );
1455
1456 fetch csr_get_asg_period_type into l_period_type;
1457 if csr_get_asg_period_type%notfound then
1458 l_period_type := l_payroll_period_type;
1459 end if;
1460 close csr_get_asg_period_type;
1461
1462 -- End bug#7670451
1463 --
1464 select ptpt.NUMBER_PER_FISCAL_YEAR
1465 into l_freq
1466 from
1467 per_time_period_types ptpt
1468 where ptpt.period_type = nvl(l_period_type,l_payroll_period_type);
1469 --
1470 --
1471 -- Find the statutory start date
1472 --
1473 l_date := hr_gbbal.span_start(p_input_date => l_effective_date,
1474 p_frequency => l_freq,
1475 p_start_dd_mm => '06-04');
1476 end if;
1477 --
1478 RETURN l_date ;
1479 --
1480 --
1481 end STATUTORY_PERIOD_START_DATE;
1482 --
1483 -------------------------------------------------------------------------------
1484 -- STATUTORY_PERIOD_NUMBER
1485 -- given a date find the statutory period
1486 function STATUTORY_PERIOD_NUMBER
1487 ( p_date in date ,
1488 p_period_type in varchar2 )
1489 return number is
1490 --
1491 l_tax_year_start date;
1492 f_year number(4);
1493 l_period number(2);
1494 f_start_dd_mon varchar2(7) := '06-04-';
1495
1496 Begin
1497 --
1498 f_year := to_number(to_char(p_date,'YYYY'));
1499 --
1500 if p_date >= to_date(f_start_dd_mon||to_char(f_year),'DD-MM-YYYY')
1501 then
1502 l_tax_year_start := to_date(f_start_dd_mon||to_char(f_year),'DD-MM-YYYY');
1503 else
1504 l_tax_year_start := to_date(f_start_dd_mon||to_char(f_year -1),'DD-MM-YYYY');
1505 end if;
1506 --
1507 l_period := 0;
1508 --
1509 if p_period_type = 'W' then
1510 l_period := trunc((p_date - l_tax_year_start)/7) + 1;
1511 end if;
1512 if p_period_type = 'CM' then
1513 l_period := trunc(months_between(p_date,l_tax_year_start))+ 1;
1514 end if;
1515 --
1516 RETURN l_period ;
1517 --
1518 end STATUTORY_PERIOD_NUMBER;
1519 --
1520 --------------------------------------------------------------------------------
1521 -- NI_ABLE_PER_PTD --
1522 -- find the NIable Pay balance for a particular category
1523 -- This function is now obsolete so blanked out, and zero returned. This
1524 -- is not called by any UK formula, package, report or form. The values
1525 -- here can be obtained using ni_balances_per_ni_ptd.
1526 --
1527 --------------------------------------------------------------------------------
1528 --
1529
1530 function ni_able_per_ptd
1531 (
1532 p_assignment_action_id IN number ,
1533 p_category IN varchar2 ,
1534 p_pension IN varchar2
1535 )
1536 return number is
1537 --
1538 begin
1539 --
1540 RETURN 0;
1541 --
1542 end ni_able_per_ptd;
1543 --
1544 --------------------------------------------------------------------------------
1545 --
1546 -- Multiple Assignments --
1547 -- How many assignments has this person got
1548 --
1549 --------------------------------------------------------------------------------
1550 --
1551
1552 function count_assignments
1553 (
1554 p_assignment_id IN number
1555 )
1556 return number is
1557 --
1558 -- N.B. When called from FastFormula, p_assignment_id
1559 -- provided via context-set variables.
1560 l_effective_date date;
1561 l_count number;
1562 l_person_id number;
1563 --
1564 Begin
1565 --
1566 select person_id
1567 into l_person_id
1568 from per_assignments
1569 where assignment_id = p_assignment_id;
1570 --
1571 select count(1) into l_count
1572 from per_assignments a
1573 where a.person_id = l_person_id
1574 and a.payroll_id is not null;
1575 --
1576 --
1577 RETURN l_count;
1578 --
1579 end count_assignments;
1580 --
1581 --------------------------------------------------------------------------------
1582 -- COUNT_ASSIGNMENTS_ON_PAYROLL
1583 -- count the number of live payroll assignments on a date
1584 function COUNT_ASSIGNMENTS_ON_PAYROLL
1585 ( p_date in date ,
1586 p_payroll_id in number )
1587 return number is
1588 --
1589 l_count number;
1590
1591 Begin
1592 --
1593 l_count := 0;
1594
1595 select count(1) into l_count
1596 from per_assignments_f a, per_assignment_status_types st
1597 where st.assignment_status_type_id = a.assignment_status_type_id
1598 and p_date between a.effective_start_date and a.effective_end_date
1599 and a.payroll_id = p_payroll_id
1600 and st.pay_system_status = 'P';
1601 --
1602 --
1603 RETURN l_count ;
1604 --
1605 end COUNT_ASSIGNMENTS_ON_PAYROLL;
1606 --
1607 -------------------------------------------------------------------------------
1608 -- PERIOD_TYPE_CHECK
1609 -- check that all the assignments are on the same period type
1610 function PERIOD_TYPE_CHECK
1611 ( p_assignment_id number )
1612 return number is
1613 --
1614 l_count number := 0;
1615 l_person_id number;
1616 l_tax_reference varchar2(20);
1617 l_effective_date date;
1618
1619 Begin
1620 --
1621 select effective_date
1622 into l_effective_date
1623 from fnd_sessions
1624 where session_id = userenv('sessionid');
1625
1626 --
1627 begin
1628 select FLEX.segment1,a.person_id
1629 into l_tax_reference, l_person_id
1630 from pay_payrolls_f p,
1631 per_assignments_f a,
1632 hr_soft_coding_keyflex FLEX
1633 where a.assignment_id = p_assignment_id
1634 and a.payroll_id = p.payroll_id
1635 and l_effective_date
1636 between a.effective_start_date and a.effective_end_date
1637 and l_effective_date
1638 between p.effective_start_date and p.effective_end_date
1639 and FLEX.soft_coding_keyflex_id = p.soft_coding_keyflex_id;
1640 exception when NO_DATA_FOUND then
1641 l_count := 0; return l_count;
1642 end;
1643 --
1644 -- check how many period types you have for this person excluding those
1645 -- reported and calculated under a different tax reference
1646 begin
1647 select count(distinct ptpt.NUMBER_PER_FISCAL_YEAR) into l_count
1648 from pay_payrolls_f p,
1649 per_assignments_f a,
1650 hr_soft_coding_keyflex FLEX,
1651 per_time_period_types ptpt
1652 where a.person_id = l_person_id
1653 and a.assignment_type = 'E'
1654 and a.payroll_id = p.payroll_id
1655 and nvl(hr_gbnidir.element_entry_value(
1656 a.assignment_id, l_effective_date,
1657 'NI','Priority Period Type') ,p.period_type)
1658 = ptpt.period_type
1659 and l_effective_date
1660 between a.effective_start_date and a.effective_end_date
1661 and l_effective_date
1662 between p.effective_start_date and p.effective_end_date
1663 and FLEX.soft_coding_keyflex_id = p.soft_coding_keyflex_id
1664 and FLEX.segment1 = l_tax_reference;
1665 --
1666 exception when NO_DATA_FOUND then
1667 l_count := 0;
1668 end;
1669
1670 Return l_count;
1671 --
1672 --
1673 end PERIOD_TYPE_CHECK;
1674 --
1675 -------------------------------------------------------------------------------
1676 -- PAYE_STAT_PERIOD_START_DATE
1677 -- find the longest period that this person is on
1678 function PAYE_STAT_PERIOD_START_DATE
1679 ( p_assignment_action_id number )
1680 return date is
1681 --
1682 l_assignment_id number;
1683 l_person_id number;
1684 l_tax_reference varchar2(20);
1685 l_effective_date date;
1686 l_date date ;
1687 l_tax_year_start date;
1688 f_year number(4);
1689 f_start_dd_mon varchar2(7) := '06-04-';
1690 l_freq number;
1691
1692 Begin
1693 -- find the effective date and assignment for this action
1694 select assignment_id,effective_date
1695 into l_assignment_id,l_effective_date
1696 from pay_assignment_actions BASSACT,
1697 pay_payroll_actions BACT
1698 where BASSACT.assignment_action_id = p_assignment_action_id
1699 and BACT.payroll_action_id = BASSACT.payroll_action_id;
1700 --
1701 begin
1702 -- 5907448
1703 -- find the tax reference and person for this assignment
1704 select
1705 /*+
1706 ordered
1707 use_nl(a p flex)
1708 index(a per_assignments_f_pk)
1709 index(p pay_payrolls_f_pk)
1710 index(flex hr_soft_coding_keyflex_pk)
1711 */
1712 FLEX.segment1,a.person_id into l_tax_reference, l_person_id
1713 from per_all_assignments_f a,
1714 pay_all_payrolls_f p,
1715 hr_soft_coding_keyflex FLEX
1716 where a.assignment_id = l_assignment_id
1717 and a.payroll_id = p.payroll_id
1718 and l_effective_date between a.effective_start_date and a.effective_end_date
1719 and l_effective_date between p.effective_start_date and p.effective_end_date
1720 and FLEX.soft_coding_keyflex_id(+) = p.soft_coding_keyflex_id;
1721 end;
1722 --
1723 -- find the longest period you have for this person excluding those
1724 -- reported and calculated under a different tax reference, unless
1725 -- there is a priority period type, in which case use this frequency.
1726 --
1727 begin
1728 -- 5907448
1729 select
1730 /*+
1731 ordered
1732 use_nl(a p flex ptpt)
1733 index(a per_assignments_f_n12)
1734 index(p pay_payrolls_f_pk)
1735 index(flex hr_soft_coding_keyflex_pk)
1736 index(ptpt per_time_period_types_pk)
1737 */
1738 min(ptpt.NUMBER_PER_FISCAL_YEAR) into l_freq
1739 from per_all_assignments_f a,
1740 pay_all_payrolls_f p,
1741 hr_soft_coding_keyflex FLEX,
1742 per_time_period_types ptpt
1743 where a.person_id = l_person_id
1744 and a.payroll_id = p.payroll_id
1745 /*Bug 13829747-- Added a check to pick only assignments which are of employee type*/
1746 and a.assignment_type = 'E'
1747 and nvl(hr_gbnidir.element_entry_value(a.assignment_id,
1748 l_effective_date,'NI','Priority Period Type'),
1749 p.period_type) = ptpt.period_type
1750 and l_effective_date between a.effective_start_date and a.effective_end_date
1751 and l_effective_date between p.effective_start_date and p.effective_end_date
1752 and FLEX.soft_coding_keyflex_id = p.soft_coding_keyflex_id
1753 and FLEX.segment1 = l_tax_reference;
1754 --
1755 exception when NO_DATA_FOUND then
1756 l_freq := 52;
1757 end;
1758
1759 Begin
1760 --
1761 f_year := to_number(to_char(l_effective_date,'YYYY'));
1762 --
1763 if l_effective_date >= to_date(f_start_dd_mon||to_char(f_year),'DD-MM-YYYY')
1764 then
1765 l_tax_year_start := to_date(f_start_dd_mon||to_char(f_year),'DD-MM-YYYY');
1766 else
1767 l_tax_year_start := to_date(f_start_dd_mon||to_char(f_year -1),'DD-MM-YYYY');
1768 end if;
1769 --
1770 l_date := l_effective_date;
1771 -- if its weekly, fortnightly or lunar work out the offset in days
1772 if l_freq in (52,26,13) then
1773 if l_freq = 52 then
1774 l_date := l_effective_date - mod(l_effective_date - l_tax_year_start,7);
1775 end if;
1776 if l_freq = 26 then
1777 l_date := l_effective_date - mod(l_effective_date - l_tax_year_start,14);
1778 end if;
1779 if l_freq = 13 then
1780 l_date := l_effective_date - mod(l_effective_date - l_tax_year_start,28);
1781 end if;
1782 else
1783 -- for monthly based go back to the previous 6th Apr
1784 l_date := to_date('06-'||to_char(l_effective_date-5,'mm-yyyy'),'dd-mm-yyyy');
1785 --
1786 -- for quaters go back to the start of the quarter
1787 if l_freq = 4 then
1788 l_date := add_months(l_date,-
1789 mod(months_between(l_date, l_tax_year_start),3));
1790 end if;
1791 -- for half year go back to the start of the half year
1792 if l_freq = 2 then
1793 l_date := add_months(l_date,-
1794 mod(months_between(l_date, l_tax_year_start),6));
1795 end if;
1796 --
1797 --
1798 -- for annual period go back to the start of year
1799 if l_freq = 1 then
1800 l_date := l_tax_year_start;
1801 end if;
1802 end if;
1803
1804 Return l_date;
1805 --
1806 end;
1807 --
1808 end PAYE_STAT_PERIOD_START_DATE;
1809 -------------------------------------------------------------------------------
1810 -- ELEMENT_ENTRY_VALUE
1811 function ELEMENT_ENTRY_VALUE
1812 ( p_assignment_id number,
1813 p_effective_date date,
1814 p_element_name varchar2,
1815 p_input_name varchar2)
1816 return varchar2 is
1817 --
1818 l_element_type_id number;
1819 l_input_id number;
1820 l_value varchar2(80);
1821
1822 Begin
1823 --
1824 -- Get the id's of the setup data
1825 --
1826 SELECT type_tl.element_type_id
1827 INTO l_element_type_id
1828 FROM pay_element_types_f_tl type_tl
1829 WHERE EXISTS
1830 (SELECT 1
1831 FROM pay_element_types_f type
1832 WHERE type.element_type_id = type_tl.element_type_id
1833 AND p_effective_date BETWEEN
1834 type.effective_start_date AND type.effective_end_date)
1835 AND type_tl.language = USERENV ('LANG')
1836 AND type_tl.element_name = p_element_name;
1837 --
1838 select iv.input_value_id
1839 into l_input_id
1840 from pay_input_values_f_tl IV_TL,
1841 pay_input_values_f IV
1842 where iv_tl.input_value_id = iv.input_value_id
1843 and userenv('LANG') = iv_tl.language
1844 and iv_tl.name = p_input_name
1845 and iv.element_type_id = l_element_type_id
1846 and p_effective_date between
1847 iv.effective_start_date and iv.effective_end_date;
1848 --
1849 --
1850 -- find the assignments period type
1851 select max(ENT_PT.screen_entry_value)
1852 into l_value
1853 from pay_element_entry_values_f ENT_PT
1854 ,pay_element_entries_f ENT
1855 ,pay_element_links_f EL
1856 where ENT_PT.input_value_id + 0 = l_input_id
1857 and ENT_PT.element_entry_id = ENT.element_entry_id
1858 and ENT.assignment_id = p_assignment_id
1859 and EL.element_type_id = l_element_type_id
1860 and EL.element_link_id = ENT.element_link_id
1861 and p_effective_date between
1862 EL.effective_start_date and EL.effective_end_date
1863 and p_effective_date between
1864 ENT_PT.effective_start_date and ENT_PT.effective_end_date
1865 and p_effective_date between
1866 ENT.effective_start_date and ENT.effective_end_date;
1867 return l_value;
1868 end ELEMENT_ENTRY_VALUE;
1869 -------------------------------------------------------------------------------
1870 -- NI_ELEMENT_ENTRY_VALUE
1871 function NI_ELEMENT_ENTRY_VALUE
1872 ( p_assignment_id number,
1873 p_effective_date date )
1874 return varchar2 is
1875 --
1876 l_element_type_id number;
1877 l_cat_input_id number;
1878 l_pen_input_id number;
1879 l_category varchar2(80);
1880 l_pension varchar2(80);
1881 l_value varchar2(80);
1882
1883 Begin
1884 --
1885 -- check if the assignments category has already been fetched otherwise fetch
1886 if ( p_assignment_id <> nvl(g_assignment_id,-1) ) or
1887 ( p_effective_date <> nvl(g_effective_date,to_date('31-12-4712','dd-mm-yyyy')))
1888 then
1889 --
1890 -- Get the id's of the setup data
1891 if g_ni_element_type_id is null then
1892 select element_type_id
1893 into l_element_type_id
1894 from pay_element_types_f
1895 where element_name = 'NI'
1896 and p_effective_date between
1897 effective_start_date and effective_end_date;
1898 --
1899 select input_value_id
1900 into l_cat_input_id
1901 from pay_input_values_f
1902 where name = 'Category'
1903 and element_type_id = l_element_type_id
1904 and p_effective_date between
1905 effective_start_date and effective_end_date;
1906 --
1907 select input_value_id
1908 into l_pen_input_id
1909 from pay_input_values_f
1910 where name = 'Pension'
1911 and element_type_id = l_element_type_id
1912 and p_effective_date between
1913 effective_start_date and effective_end_date;
1914 else
1915 l_element_type_id := g_ni_element_type_id;
1916 l_cat_input_id := g_cat_input_id;
1917 l_pen_input_id := g_pen_input_id;
1918 end if;
1919 --
1920 -- find the assignments category and pension
1921 -- Match Element Entry Value start and end dates with
1922 -- Element Entry start and end dates, as always the same.
1923 -- (Performance fix).
1924 select max(decode(ENT_PT.input_value_id,l_cat_input_id,ENT_PT.screen_entry_value,null)),
1925 max(decode(ENT_PT.input_value_id,l_pen_input_id,ENT_PT.screen_entry_value,null))
1926 into l_category, l_pension
1927 from pay_element_entry_values_f ENT_PT
1928 ,pay_element_entries_f ENT
1929 ,pay_element_links_f EL
1930 where ENT_PT.element_entry_id = ENT.element_entry_id
1931 and ENT.assignment_id = p_assignment_id
1932 and EL.element_type_id = l_element_type_id
1933 and EL.element_link_id = ENT.element_link_id
1934 and p_effective_date between
1935 EL.effective_start_date and EL.effective_end_date
1936 and p_effective_date between
1937 ENT.effective_start_date and ENT.effective_end_date
1938 and ENT.effective_start_date = ENT_PT.effective_start_date
1939 and ENT.effective_end_date = ENT_PT.effective_end_date;
1940 --
1941 if l_category is null then l_category := ' ';
1942 end if;
1943 if l_pension is null then l_pension := ' ';
1944 end if;
1945 -- filter out invalid category / pension combinations
1946 g_catpen := l_category||l_pension;
1947 g_assignment_id := p_assignment_id;
1948 g_effective_date := p_effective_date;
1949 end if;
1950 return g_catpen;
1951
1952 end NI_ELEMENT_ENTRY_VALUE;
1953 --------------------------------------------------------------------------------
1954 -- NI_BALANCES_PER_NI_PTD --
1955 -- get all of the NI balances for an assignment in one select
1956 --
1957 --------------------------------------------------------------------------------
1958 --
1959
1960 function NI_BALANCES_PER_NI_PTD
1961 (
1962 p_assignment_action_id IN number,
1963 p_global_name IN varchar2
1964 )
1965 return number is
1966 --
1967 -- N.B. When called from FastFormula, p_assignment_action_id
1968 -- provided via context-set variables.
1969 --
1970 l_effective_date date;
1971 l_balance_type_id number;
1972 l_element_type_id number;
1973 l_category_input_id number;
1974 l_pension_input_id number;
1975 l_niable number;
1976 l_stat_period_start date;
1977 l_balance_value number;
1978 l_balance_list pay_balance_pkg.t_balance_value_tab;
1979 --
1980 Begin
1981 --
1982 -- Set up the defined balances for all NI balances _PER_NI_PTD
1983 if g_defined_ptd_set = FALSE then
1984 hr_utility.trace('Calling set_defined_balances');
1985 set_defined_balances('_PER_NI_PTD');
1986 end if;
1987 hr_utility.trace('example defbal:'||to_char(g_comp_min_ers_defbal));
1988 --
1989 if g_ni_able_id is null then
1990 select balance_type_id
1991 into g_ni_able_id
1992 from pay_balance_types
1993 where balance_name = 'NIable Pay';
1994 --
1995 select element_type_id
1996 into g_ni_element_type_id
1997 from pay_element_types_f
1998 where element_name = 'NI'
1999 and sysdate between effective_start_date
2000 and effective_end_date;
2001 --
2002 select input_value_id
2003 into g_cat_input_id
2004 from pay_input_values_f
2005 where name = 'Category'
2006 and element_type_id = g_ni_element_type_id
2007 and sysdate between effective_start_date
2008 and effective_end_date;
2009 --
2010 select input_value_id
2011 into g_pen_input_id
2012 from pay_input_values_f
2013 where name = 'Pension'
2014 and element_type_id = g_ni_element_type_id
2015 and sysdate between effective_start_date
2016 and effective_end_date;
2017 end if;
2018 --
2019 IF g_assignment_action_id = p_assignment_action_id then
2020 l_stat_period_start := g_statutory_period_start_date;
2021 ELSE
2022 -- The assignment_action_id has changed from the last call or is null,
2023 -- calculate the balances via route-code before calling the global function.
2024 --
2025 l_stat_period_start := hr_gbnidir.STATUTORY_PERIOD_START_DATE(p_assignment_action_id);
2026 g_statutory_period_start_date := l_stat_period_start;
2027 g_assignment_action_id := p_assignment_action_id;
2028 hr_utility.trace('Assignment Action: '||to_char(g_assignment_action_id));
2029 --
2030 /* NI ABLE CURSOR WHICH REMAINS DUE TO USING VARIANT ROUTE AND STYLE */
2031 /* OF SELECTION FROM BALANCE TABLES. */
2032 select /*+ ORDERED INDEX(BAL_ASSACT PAY_ASSIGNMENT_ACTIONS_PK,
2033 BACT PAY_PAYROLL_ACTIONS_PK,
2034 BPTP PER_TIME_PERIODS_PK,
2035 START_ASS PER_ASSIGNMENTS_F_PK,
2036 ASS PER_ASSIGNMENTS_N4,
2037 ASSACT PAY_ASSIGNMENT_ACTIONS_N51,
2038 PACT PAY_PAYROLL_ACTIONS_PK,
2039 PPTP PER_TIME_PERIODS_PK ,
2040 RR PAY_RUN_RESULTS_N50,
2041 TARGET PAY_RUN_RESULT_VALUES_PK,
2042 FEED PAY_BALANCE_FEEDS_F_UK2)
2043 USE_NL(BAL_ASSACT,BACT,BPTP,START_ASS,ASS,ASSACT,PACT,PPTP,RR,TARGET,FEED) +*/
2044 nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2045 'AN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2046 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2047 'AA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2048 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2049 'BN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2050 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2051 'BA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2052 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2053 'CN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2054 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2055 'CC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2056 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2057 'DC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2058 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2059 'EC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2060 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2061 'FM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2062 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2063 'GM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2064 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2065 'JN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2066 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2067 'JA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2068 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2069 'LC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2070 ,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
2071 'SM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
2072 into g_st_ni_a_able , g_st_ni_ap_able, g_st_ni_b_able ,
2073 g_st_ni_bp_able, g_st_ni_c_able , g_st_ni_co_able ,
2074 g_st_ni_d_able , g_st_ni_e_able , g_st_ni_f_able ,
2075 g_st_ni_g_able , g_st_ni_j_able, g_st_ni_jp_able,
2076 g_st_ni_l_able, g_st_ni_s_able
2077 from
2078 pay_assignment_actions BAL_ASSACT
2079 ,pay_payroll_actions BACT
2080 ,per_time_periods BPTP
2081 ,per_all_assignments_f START_ASS
2082 ,per_all_assignments_f ASS
2083 ,pay_assignment_actions ASSACT
2084 ,pay_payroll_actions PACT
2085 ,per_time_periods PPTP
2086 ,pay_run_results RR
2087 ,pay_run_result_values TARGET
2088 ,pay_balance_feeds_f FEED
2089 where BAL_ASSACT.assignment_action_id = p_assignment_action_id
2090 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
2091 and FEED.balance_type_id = g_ni_able_id + decode(RR.run_result_id, null, 0, 0)
2092 and nvl(TARGET.result_value,'0') <> '0'
2093 and FEED.input_value_id = TARGET.input_value_id
2094 and TARGET.run_result_id = RR.run_result_id
2095 and RR.assignment_action_id = ASSACT.assignment_action_id
2096 and ASSACT.payroll_action_id = PACT.payroll_action_id
2097 and PACT.effective_date between
2098 FEED.effective_start_date and FEED.effective_end_date
2099 and RR.status in ('P','PA')
2100 and BPTP.time_period_id = BACT.time_period_id
2101 and PPTP.time_period_id = PACT.time_period_id
2102 and START_ASS.assignment_id = BAL_ASSACT.assignment_id
2103 and ASS.period_of_service_id = START_ASS.period_of_service_id
2104 and ASSACT.assignment_id = ASS.assignment_id
2105 and BACT.effective_date between
2106 START_ASS.effective_start_date and START_ASS.effective_end_date
2107 and PACT.effective_date between
2108 ASS.effective_start_date and ASS.effective_end_date
2109 and PACT.effective_date >=
2110 /* find the latest td payroll transfer date - compare each of the */
2111 /* assignment rows with its predecessor looking for the payroll */
2112 /* that had a different tax district at that date */
2113 ( select /*+ ORDERED INDEX (NASS PER_ASSIGNMENTS_F_PK,
2114 PASS PER_ASSIGNMENTS_F_PK,
2115 ROLL PAY_PAYROLLS_F_PK,
2116 FLEX HR_SOFT_CODING_KEYFLEX_PK,
2117 PROLL PAY_PAYROLLS_F_PK,
2118 PFLEX HR_SOFT_CODING_KEYFLEX_PK)
2119 USE_NL(NASS,PASS,ROLL,FLEX,PROLL,PFLEX) +*/
2120 nvl(max(NASS.effective_start_date), to_date('01-01-0001','DD-MM-YYYY'))
2121 from per_all_assignments_f NASS
2122 ,per_all_assignments_f PASS
2123 ,pay_all_payrolls_f ROLL
2124 ,hr_soft_coding_keyflex FLEX
2125 ,pay_all_payrolls_f PROLL
2126 ,hr_soft_coding_keyflex PFLEX
2127 where NASS.assignment_id = ASS.assignment_id
2128 and ROLL.payroll_id = NASS.payroll_id
2129 and NASS.effective_start_date between
2130 ROLL.effective_start_date and ROLL.effective_end_date
2131 and ROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
2132 and NASS.assignment_id = PASS.assignment_id
2133 and PASS.effective_end_date = (NASS.effective_start_date - 1)
2134 and NASS.effective_start_date <= BACT.effective_date
2135 and PROLL.payroll_id = PASS.payroll_id
2136 and NASS.effective_start_date between
2137 PROLL.effective_start_date and PROLL.effective_end_date
2138 and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
2139 and NASS.payroll_id <> PASS.payroll_id
2140 and FLEX.segment1 <> PFLEX.segment1
2141 )
2142 and exists ( select null from
2143 /* check that the current assignment tax districts match */
2144 pay_all_payrolls_f BROLL
2145 ,hr_soft_coding_keyflex BFLEX
2146 ,pay_all_payrolls_f PROLL
2147 ,hr_soft_coding_keyflex PFLEX
2148 where BACT.payroll_id = BROLL.payroll_id
2149 and PACT.payroll_id = PROLL.payroll_id
2150 and BFLEX.soft_coding_keyflex_id = BROLL.soft_coding_keyflex_id
2151 and PFLEX.soft_coding_keyflex_id = PROLL.soft_coding_keyflex_id
2152 and BACT.effective_date between
2153 BROLL.effective_start_date and BROLL.effective_end_date
2154 and BACT.effective_date between
2155 PROLL.effective_start_date and PROLL.effective_end_date
2156 and BFLEX.segment1 = PFLEX.segment1
2157 )
2158 and PPTP.regular_payment_date >= l_stat_period_start
2159 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence;
2160 --
2161 -- MAIN NI SELECTION, USE CORE BUE
2162 -- Set the l_balance_list table (once per assignment action) for this
2163 -- assignment action, then call the core BUE in batch mode.
2164 -- Then set the global balance values so that they remain the same for the
2165 -- current assignment. This uses the in out param l_balance_list, which
2166 -- stores the defined balances for all NI PER_NI_PTD balances.
2167 --
2168 set_balance_table(l_balance_list);
2169 --
2170 -- Call the Core BUE in BATCH MODE with the above set of defined balances,
2171 -- the resulting values will be stored in the globals as before.
2172 -- Exception handle this in case of NO DATA FOUND, which can be invoked
2173 -- by this call only if the Defined balance id is not found. These are
2174 -- all seeded so the exception should not happen but placed as a precaution.
2175 --
2176 BEGIN
2177 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id,
2178 p_defined_balance_lst => l_balance_list);
2179 EXCEPTION WHEN NO_DATA_FOUND THEN
2180 hr_utility.trace('No data found on call to pay_balance_pkg');
2181 null;
2182 END;
2183 --
2184 -- Set the Global variables to the values just retrieved from the bulk
2185 -- call to Core BUE.
2186 set_balance_values(l_balance_list);
2187 --
2188 END IF;
2189 --
2190 -- Calculate the value from the global-retrieval function
2191 -- passing in the global name. This function is called
2192 -- in all cases, as at this point all globals are set.
2193 --
2194 l_balance_value := get_plsql_global(p_global_name);
2195 --
2196 --
2197 RETURN l_balance_value;
2198 --
2199 end NI_BALANCES_PER_NI_PTD;
2200 --
2201 ----------------------------------------------------------------------------
2202 -- Function STATUTORY_PERIOD_DATE_MODE.
2203 -- Description: This function takes in an assignment ID
2204 -- and effective date from a Date Mode query
2205 -- on a Person level Period to Date balance.
2206 -- The steps are: 1. Find the start of the statutory period for
2207 -- the assignment as at the effective date.
2208 -- 2. Check the period type on the element entry
2209 -- 3. If not found get the period type of the payroll
2210 -- 4. Return the statutory period end date.
2211 --
2212 ----------------------------------------------------------------------------
2213 --
2214 function statutory_period_date_mode (p_assignment_id IN NUMBER,
2215 p_effective_date IN DATE) RETURN DATE IS
2216 --
2217 l_period_regular_payment_date date;
2218 l_time_period_type varchar2(30);
2219 l_time_period_start_date date;
2220 l_time_period_end_date date;
2221 l_element_type_id number;
2222 l_input_value_id number;
2223 l_asg_period_type varchar2(30);
2224 l_frequency number;
2225 l_f_year number;
2226 l_f_start_dd_mon varchar2(7) := '06-04-';
2227 l_tax_year_start date;
2228 l_date date;
2229 --
2230 cursor get_time_period_info (c_assignment_id number,
2231 c_effective_date date) is
2232 select ptp.regular_payment_date,
2233 ptp.period_type,
2234 ptp.start_date,
2235 ptp.end_date
2236 from per_assignments_f paf,
2237 per_time_periods ptp
2238 where paf.assignment_id = c_assignment_id
2239 and ptp.payroll_id = paf.payroll_id
2240 and c_effective_date between
2241 ptp.start_date and ptp.end_date
2242 and c_effective_date between
2243 paf.effective_start_date and paf.effective_end_date;
2244 --
2245 cursor ni_element_type (c_effective_date date) is
2246 select pet.element_type_id,
2247 piv.input_value_id
2248 from pay_input_values_f piv,
2249 pay_element_types_f pet
2250 where pet.element_name = 'NI'
2251 and c_effective_date between
2252 pet.effective_start_date and pet.effective_end_date
2253 and piv.element_type_id = pet.element_type_id
2254 and piv.name = 'Priority Period Type'
2255 and c_effective_date between
2256 piv.effective_start_date and piv.effective_end_date;
2257
2258 cursor get_asg_period_type (c_period_type_id number,
2259 c_assignment_id number,
2260 c_element_type_id number,
2261 c_effective_date date) is
2262 select ENT_PT.screen_entry_value
2263 from pay_element_entry_values_f ENT_PT
2264 ,pay_element_entries_f ENT
2265 ,pay_element_links_f EL
2266 where ENT_PT.input_value_id + 0 = c_period_type_id
2267 and ENT_PT.element_entry_id = ENT.element_entry_id
2268 and ENT.assignment_id = c_assignment_id
2269 and EL.element_type_id = c_element_type_id
2270 and EL.element_link_id = ENT.element_link_id
2271 and c_effective_date between
2272 EL.effective_start_date and EL.effective_end_date
2273 and c_effective_date between
2274 ENT_PT.effective_start_date and ENT_PT.effective_end_date
2275 and c_effective_date between
2276 ENT.effective_start_date and ENT.effective_end_date;
2277 --
2278 cursor get_frequency (c_asg_period_type varchar2,
2279 c_payroll_period_type varchar2) is
2280 select ptpt.number_per_fiscal_year
2281 from per_time_period_types ptpt
2282 where ptpt.period_type = nvl(c_asg_period_type, c_payroll_period_type);
2283 --
2284 begin
2285 --
2286 -- fetch the time period info for the assignment
2287 -- as at the effective date
2288 --
2289 open get_time_period_info(p_assignment_id,
2290 p_effective_date);
2291 fetch get_time_period_info into l_period_regular_payment_date,
2292 l_time_period_type,
2293 l_time_period_start_date,
2294 l_time_period_end_date;
2295 close get_time_period_info;
2296 --
2297 -- Get the element type ID of NI element as at the
2298 -- regular payment date of the time period above.
2299 --
2300 open ni_element_type(l_period_regular_payment_date);
2301 fetch ni_element_type into l_element_type_id, l_input_value_id;
2302 close ni_element_type;
2303 --
2304 -- Get the assignment period type from the date effective
2305 -- element tables as at the obtained regular payment date of the
2306 -- time period.
2307 --
2308 open get_asg_period_type(l_input_value_id,
2309 p_assignment_id,
2310 l_element_type_id,
2311 l_period_regular_payment_date);
2312 fetch get_asg_period_type into l_asg_period_type;
2313 close get_asg_period_type;
2314 --
2315 -- Get the frequency from the number per fiscal year of the time
2316 -- period, using an nvl of the assignment period type and (payroll)
2317 -- time period type.
2318 --
2319 open get_frequency(l_asg_period_type, l_time_period_type);
2320 fetch get_frequency into l_frequency;
2321 close get_frequency;
2322 --
2323 --
2324 -- Find the statutory end date
2325 --
2326 l_date := hr_gbbal.span_end(p_input_date => l_period_regular_payment_date,
2327 p_frequency => l_frequency,
2328 p_start_dd_mm => '06-04-');
2329 --
2330 --
2331 -- Note: This function is used for date mode expiry checking
2332 -- of person level PTD balances, so the end of the statutory
2333 -- time period is returned so that it can be checked against the
2334 -- effective date of the query.
2335 --
2336 RETURN l_date;
2337 --
2338 end statutory_period_date_mode;
2339 --
2340 ----------------------------------------------------------------------------
2341 -- Function NIABLE_BANDS.
2342 -- Description: Code externalized from NI_PERSON and NI_DIRECTOR formulas
2343 -- to save source text space in those.
2344 -- It takes in the NIABLE thresholds, Niable for the Person and
2345 -- NI Able for the Person within this category.
2346 -- Outputs the NIABLE within each band
2347 -- As the EET is obsolete from 6th April 2001, the formula
2348 -- will pass in a zero for the EET value from the Global,
2349 -- and return a zero for the out param. This does not affect
2350 -- the running of the function and will be removed in
2351 -- April 2002.
2352 -- Sep 02 - Removed all EET calculations.
2353 --
2354 ----------------------------------------------------------------------------
2355 --
2356 function niable_bands (L_NIABLE IN NUMBER,
2357 L_NI_CAT_ABLE IN NUMBER,
2358 L_TOT_NIABLE IN NUMBER,
2359 L_LEL IN NUMBER,
2360 L_EET IN NUMBER,
2361 L_ET IN NUMBER,
2362 L_UEL IN NUMBER,
2363 NI_ABLE_LEL IN OUT NOCOPY NUMBER,
2364 NI_ABLE_EET IN OUT NOCOPY NUMBER,
2365 NI_ABLE_ET IN OUT NOCOPY NUMBER,
2366 NI_ABLE_UEL IN OUT NOCOPY NUMBER,
2367 NI_UPPER IN OUT NOCOPY NUMBER,
2368 NI_LOWER IN OUT NOCOPY NUMBER
2369 ) RETURN NUMBER IS
2370
2371 -- L_NIABLE: earnings subject to NI for the current category and higher priority categories
2372 -- L_NI_CAT_ABLE: earnings subject to NI for the current category
2373 -- L_TOT_NIABLE: total earnings subject to NI
2374 -- L_LEL: lower earnings threshold for the period (period in this case may be Annual or pro-rated
2375 -- annual for directors. Priority Processing Period for multiple assignments.
2376 -- L_ET: employers earnings threshold for the period.
2377 -- L_UEL: upper earnings threshold
2378 NI_ABLE NUMBER;
2379 begin
2380 hr_utility.set_location('hr_gbnidir.niable_bands', 10);
2381 NI_ABLE := 0; -- niable up to the UEL for current category
2382 NI_ABLE_LEL := 0; -- niable up to the LEL for current category if niable is >LEL
2383 NI_ABLE_EET := 0; -- Now obsolete, set to zero.
2384 NI_ABLE_ET := 0; -- niable between the LEL and the ET for category
2385 NI_ABLE_UEL := 0; -- niable bwteen the ET and UEL for category
2386 NI_UPPER := 0; -- earnings above the UEL for category, ie Above UEL (AUEL).
2387 NI_LOWER := 0; -- earnings below the LEL for category
2388 if l_ni_cat_able > 0 then
2389 hr_utility.set_location('hr_gbnidir.niable_bands', 20);
2390 NI_LOWER := greatest(0,(least(l_lel,l_niable)
2391 - (l_niable - l_ni_cat_able)));
2392 -- if Employees total earnings subject to NI is over the Lower Earnings limit
2393 -- work out how much of those LEL earnings are for Niable for the category
2394 -- being calculated. Each Category is calculated in priority order so the
2395 -- highest priority category will attract the LEL earnings. Subsequent
2396 -- categories start their calculating at higher thresholds as NIABLE already
2397 -- calculated is taken into account.
2398 if l_tot_niable > l_lel then
2399 hr_utility.set_location('hr_gbnidir.niable_bands', 20);
2400 ni_able_lel := ni_lower;
2401 --
2402 ni_able_et := greatest(0,((least(l_et,l_niable)) -
2403 (greatest(l_lel,(l_niable - l_ni_cat_able)))));
2404 --
2405 ni_able_uel := greatest(0,((least(l_uel,l_niable)) -
2406 (greatest(l_et,(l_niable - l_ni_cat_able)))));
2407 ni_upper := greatest(0,(l_niable - (greatest(l_uel,
2408 (l_niable - l_ni_cat_able)))));
2409 ni_able := ni_able_lel + ni_able_et + ni_able_uel;
2410 hr_utility.trace('NIABLE_BANDS ni_able_lel='||to_char(ni_able_lel)||
2411 ' ni_able_et='||to_char(ni_able_et)||
2412 ' ni_able_uel='||to_char(ni_able_uel)||
2413 ' ni_upper='||to_char(ni_upper)||
2414 ' ni_lower='||to_char(ni_lower)||
2415 ' ni_able='||to_char(ni_able));
2416 end if;
2417 end if;
2418
2419 return NI_ABLE;
2420 --
2421 end niable_bands;
2422
2423 --
2424 ----------------------------------------------------------------------------
2425 -- Function NIABLE_BANDS.
2426 -- Description: Over Loaded function Added for UAP EOY 08/09
2427 -- Bug 7312374
2428 ----------------------------------------------------------------------------
2429 function niable_bands (L_NIABLE IN NUMBER,
2430 L_NI_CAT_ABLE IN NUMBER,
2431 L_TOT_NIABLE IN NUMBER,
2432 L_LEL IN NUMBER,
2433 L_EET IN NUMBER,
2434 L_ET IN NUMBER,
2435 L_UAP IN NUMBER, -- EOY 08/09
2436 L_UEL IN NUMBER,
2437 NI_ABLE_LEL IN OUT NOCOPY NUMBER,
2438 NI_ABLE_EET IN OUT NOCOPY NUMBER,
2439 NI_ABLE_ET IN OUT NOCOPY NUMBER,
2440 NI_ABLE_UAP IN OUT NOCOPY NUMBER, -- EOY 08/09
2441 NI_ABLE_UEL IN OUT NOCOPY NUMBER,
2442 NI_UPPER IN OUT NOCOPY NUMBER,
2443 NI_LOWER IN OUT NOCOPY NUMBER
2444 ) RETURN NUMBER IS
2445
2446 -- L_NIABLE: earnings subject to NI for the current category and higher priority categories
2447 -- L_NI_CAT_ABLE: earnings subject to NI for the current category
2448 -- L_TOT_NIABLE: total earnings subject to NI
2449 -- L_LEL: lower earnings threshold for the period (period in this case may be Annual or pro-rated
2450 -- annual for directors. Priority Processing Period for multiple assignments.
2451 -- L_ET: employers earnings threshold for the period.
2452 -- L_UEL: upper earnings threshold
2453 -- L_UAP: upper accrual point
2454
2455 NI_ABLE NUMBER;
2456 begin
2457 hr_utility.set_location('hr_gbnidir.niable_bands', 10);
2458 NI_ABLE := 0; -- niable up to the UEL for current category
2459 NI_ABLE_LEL := 0; -- niable up to the LEL for current category if niable is >LEL
2460 NI_ABLE_EET := 0; -- Now obsolete, set to zero.
2461 NI_ABLE_ET := 0; -- niable between the LEL and the ET for category
2462 NI_ABLE_UAP := 0; -- niable between the ET and the UAP for category
2463 NI_ABLE_UEL := 0; -- niable between the UAP and UEL for category
2464 NI_UPPER := 0; -- earnings above the UEL for category, ie Above UEL (AUEL).
2465 NI_LOWER := 0; -- earnings below the LEL for category
2466 if l_ni_cat_able > 0 then
2467 hr_utility.set_location('hr_gbnidir.niable_bands', 20);
2468 NI_LOWER := greatest(0,(least(l_lel,l_niable)
2469 - (l_niable - l_ni_cat_able)));
2470 -- if Employees total earnings subject to NI is over the Lower Earnings limit
2471 -- work out how much of those LEL earnings are for Niable for the category
2472 -- being calculated. Each Category is calculated in priority order so the
2473 -- highest priority category will attract the LEL earnings. Subsequent
2474 -- categories start their calculating at higher thresholds as NIABLE already
2475 -- calculated is taken into account.
2476 if l_tot_niable > l_lel then
2477 hr_utility.set_location('hr_gbnidir.niable_bands', 20);
2478 ni_able_lel := ni_lower;
2479 --
2480 ni_able_et := greatest(0,((least(l_et,l_niable)) -
2481 (greatest(l_lel,(l_niable - l_ni_cat_able)))));
2482 --
2483 --EOY 08/09 Begin
2484 ni_able_uap := greatest(0,((least(l_uap,l_niable)) -
2485 (greatest(l_et,(l_niable - l_ni_cat_able)))));
2486
2487 ni_able_uel := greatest(0,((least(l_uel,l_niable)) -
2488 (greatest(l_uap,(l_niable - l_ni_cat_able)))));
2489 --EOY 08/09 End
2490 ni_upper := greatest(0,(l_niable - (greatest(l_uel,
2491 (l_niable - l_ni_cat_able)))));
2492 --EOY 08/09 Begin
2493 --ni_able := ni_able_lel + ni_able_et + ni_able_uel;
2494 ni_able := ni_able_lel + ni_able_et + ni_able_uap + ni_able_uel;
2495 --EOY 08/09 End
2496 hr_utility.trace('NIABLE_BANDS ni_able_lel='||to_char(ni_able_lel)||
2497 ' ni_able_et='||to_char(ni_able_et)||
2498 ' ni_able_uap='||to_char(ni_able_uap)||
2499 ' ni_able_uel='||to_char(ni_able_uel)||
2500 ' ni_upper='||to_char(ni_upper)||
2501 ' ni_lower='||to_char(ni_lower)||
2502 ' ni_able='||to_char(ni_able));
2503 end if;
2504 end if;
2505
2506 return NI_ABLE;
2507 --
2508 end niable_bands;
2509
2510 ----------------------------------------------------------------------------
2511 -- Function NIABLE_BANDS_ST_PT.
2512 -- Description: Modified function of niable_bands added for ST/PT EOY 10/11
2513 -- PT - Primary Threshold or Employee Threshold
2514 -- ST - Secondary Threshold or Employer Threshold
2515 ----------------------------------------------------------------------------
2516 function niable_bands_st_pt (L_NIABLE IN NUMBER,
2517 L_NI_CAT_ABLE IN NUMBER,
2518 L_TOT_NIABLE IN NUMBER,
2519 L_LEL IN NUMBER,
2520 L_ET IN NUMBER,
2521 L_ERT IN NUMBER,
2522 L_UAP IN NUMBER,
2523 L_UEL IN NUMBER,
2524 NI_ABLE_LEL IN OUT NOCOPY NUMBER,
2525 NI_ABLE_ET IN OUT NOCOPY NUMBER,
2526 NI_ABLE_ERT IN OUT NOCOPY NUMBER,
2527 NI_ABLE_UAP IN OUT NOCOPY NUMBER,
2528 NI_ABLE_ER_UAP IN OUT NOCOPY NUMBER,
2529 NI_ABLE_UEL IN OUT NOCOPY NUMBER,
2530 NI_UPPER IN OUT NOCOPY NUMBER,
2531 NI_LOWER IN OUT NOCOPY NUMBER
2532 ) RETURN NUMBER is
2533
2534 -- L_NIABLE: earnings subject to NI for the current category and higher priority categories
2535 -- L_NI_CAT_ABLE: earnings subject to NI for the current category
2536 -- L_TOT_NIABLE: total earnings subject to NI
2537 -- L_LEL: lower earnings threshold for the period (period in this case may be Annual or pro-rated
2538 -- annual for directors. Priority Processing Period for multiple assignments.
2539 -- L_ET: employers earnings threshold for the period. 2010/11. This is replaced by st and pt
2540 -- L_ST: employers secondary earnings threshold for the period.
2541 -- L_PT: employers primary earnings threshold for the period.
2542 -- L_UEL: upper earnings threshold
2543 -- L_UAP: upper accrual point
2544
2545 NI_ABLE NUMBER;
2546 begin
2547 hr_utility.set_location('hr_gbnidir.niable_bands', 10);
2548 NI_ABLE := 0; -- niable up to the UEL for current category
2549 NI_ABLE_LEL := 0; -- niable up to the LEL for current category if niable is >LEL
2550 NI_ABLE_ET := 0; -- niable between the LEL and the ET(PT) for category
2551 NI_ABLE_ERT := 0; -- niable between the ERT and the LEL for category
2552 NI_ABLE_UAP := 0; -- niable between the ET(PT) and the UAP for category
2553 NI_ABLE_ER_UAP := 0; -- niable between the ERT(ST) and the UAP for category
2554 NI_ABLE_UEL := 0; -- niable between the UAP and UEL for category
2555 NI_UPPER := 0; -- earnings above the UEL for category, ie Above UEL (AUEL).
2556 NI_LOWER := 0; -- earnings below the LEL for category
2557 if l_ni_cat_able > 0 then
2558 hr_utility.set_location('hr_gbnidir.niable_bands', 20);
2559 NI_LOWER := greatest(0,(least(l_lel,l_niable)
2560 - (l_niable - l_ni_cat_able)));
2561 -- if Employees total earnings subject to NI is over the Lower Earnings limit
2562 -- work out how much of those LEL earnings are for Niable for the category
2563 -- being calculated. Each Category is calculated in priority order so the
2564 -- highest priority category will attract the LEL earnings. Subsequent
2565 -- categories start their calculating at higher thresholds as NIABLE already
2566 -- calculated is taken into account.
2567 if l_tot_niable > l_lel then
2568 hr_utility.set_location('hr_gbnidir.niable_bands', 20);
2569 ni_able_lel := ni_lower;
2570 --
2571 ni_able_et := greatest(0,((least(l_et,l_niable)) -
2572 (greatest(l_lel,(l_niable - l_ni_cat_able)))));
2573 ni_able_ert := greatest(0,((least(l_ert,l_niable)) -
2574 (greatest(l_lel,(l_niable - l_ni_cat_able)))));
2575 --
2576 ni_able_uap := greatest(0,((least(l_uap,l_niable)) -
2577 (greatest(l_et,(l_niable - l_ni_cat_able)))));
2578
2579 ni_able_er_uap := greatest(0,((least(l_uap,l_niable)) -
2580 (greatest(l_ert,(l_niable - l_ni_cat_able)))));
2581
2582 ni_able_uel := greatest(0,((least(l_uel,l_niable)) -
2583 (greatest(l_uap,(l_niable - l_ni_cat_able)))));
2584 ni_upper := greatest(0,(l_niable - (greatest(l_uel,
2585 (l_niable - l_ni_cat_able)))));
2586
2587
2588 ni_able := ni_able_lel + ni_able_et + ni_able_uap + ni_able_uel;
2589
2590 hr_utility.trace('NIABLE_BANDS ni_able_lel='||to_char(ni_able_lel)||
2591 ' ni_able_et='||to_char(ni_able_et)||
2592 ' ni_able_ert='||to_char(ni_able_ert)||
2593 ' ni_able_uap='||to_char(ni_able_uap)||
2594 ' ni_able_er_uap='||to_char(ni_able_er_uap)||
2595 ' ni_able_uel='||to_char(ni_able_uel)||
2596 ' ni_upper='||to_char(ni_upper)||
2597 ' ni_lower='||to_char(ni_lower)||
2598 ' ni_able='||to_char(ni_able));
2599 end if;
2600 end if;
2601
2602 return NI_ABLE;
2603 --
2604 end niable_bands_st_pt;
2605
2606 -- 13803907 start
2607 function iom_niable_bands_st_pt (L_NIABLE IN NUMBER,
2608 L_NI_CAT_ABLE IN NUMBER,
2609 L_TOT_NIABLE IN NUMBER,
2610 L_LEL IN NUMBER,
2611 L_ET IN NUMBER,
2612 L_ERT IN NUMBER,
2613 L_UEL IN NUMBER,
2614 NI_ABLE_LEL IN OUT NOCOPY NUMBER,
2615 NI_ABLE_ET IN OUT NOCOPY NUMBER,
2616 NI_ABLE_ERT IN OUT NOCOPY NUMBER,
2617 NI_ABLE_ER_UEL IN OUT NOCOPY NUMBER,
2618 NI_ABLE_UEL IN OUT NOCOPY NUMBER,
2619 NI_UPPER IN OUT NOCOPY NUMBER,
2620 NI_LOWER IN OUT NOCOPY NUMBER
2621 ) RETURN NUMBER is
2622
2623 -- L_NIABLE: earnings subject to NI for the current category and higher priority categories
2624 -- L_NI_CAT_ABLE: earnings subject to NI for the current category
2625 -- L_TOT_NIABLE: total earnings subject to NI
2626 -- L_LEL: lower earnings threshold for the period (period in this case may be Annual or pro-rated
2627 -- annual for directors. Priority Processing Period for multiple assignments.
2628 -- L_ET: employers earnings threshold for the period.
2629 -- L_ST: employers secondary earnings threshold for the period.
2630 -- L_PT: employers primary earnings threshold for the period.
2631 -- L_UEL: upper earnings threshold
2632 NI_ABLE NUMBER;
2633 begin
2634 hr_utility.set_location('hr_gbnidir.niable_bands', 10);
2635 NI_ABLE := 0; -- niable up to the UEL for current category
2636 NI_ABLE_LEL := 0; -- niable up to the LEL for current category if niable is >LEL
2637 NI_ABLE_ET := 0; -- niable between the LEL and the ET for category
2638 NI_ABLE_ERT := 0; -- niable between the ERT and the LEL for category
2639 NI_ABLE_ER_UEL := 0; -- niable between the ERT(ST) and the UEL for category
2640 NI_ABLE_UEL := 0; -- niable bwteen the ET and UEL for category
2641 NI_UPPER := 0; -- earnings above the UEL for category, ie Above UEL (AUEL).
2642 NI_LOWER := 0; -- earnings below the LEL for category
2643 if l_ni_cat_able > 0 then
2644 hr_utility.set_location('hr_gbnidir.niable_bands', 20);
2645 NI_LOWER := greatest(0,(least(l_lel,l_niable)
2646 - (l_niable - l_ni_cat_able)));
2647 -- if Employees total earnings subject to NI is over the Lower Earnings limit
2648 -- work out how much of those LEL earnings are for Niable for the category
2649 -- being calculated. Each Category is calculated in priority order so the
2650 -- highest priority category will attract the LEL earnings. Subsequent
2651 -- categories start their calculating at higher thresholds as NIABLE already
2652 -- calculated is taken into account.
2653 if l_tot_niable > l_lel then
2654 hr_utility.set_location('hr_gbnidir.niable_bands', 20);
2655 ni_able_lel := ni_lower;
2656 --
2657 ni_able_et := greatest(0,((least(l_et,l_niable)) -
2658 (greatest(l_lel,(l_niable - l_ni_cat_able)))));
2659
2660 ni_able_ert := greatest(0,((least(l_ert,l_niable)) -
2661 (greatest(l_lel,(l_niable - l_ni_cat_able)))));
2662 --
2663 ni_able_er_uel := greatest(0,((least(l_uel,l_niable)) -
2664 (greatest(l_ert,(l_niable - l_ni_cat_able))))); /* Modified */
2665
2666 ni_able_uel := greatest(0,((least(l_uel,l_niable)) -
2667 (greatest(l_et,(l_niable - l_ni_cat_able)))));
2668 ni_upper := greatest(0,(l_niable - (greatest(l_uel,
2669 (l_niable - l_ni_cat_able)))));
2670 ni_able := ni_able_lel + ni_able_et + ni_able_uel;
2671 hr_utility.trace('NIABLE_BANDS ni_able_lel='||to_char(ni_able_lel)||
2672 ' ni_able_et='||to_char(ni_able_et)||
2673 ' ni_able_ert='||to_char(ni_able_ert)||
2674 ' ni_able_uel='||to_char(ni_able_uel)||
2675 ' ni_upper='||to_char(ni_upper)||
2676 ' ni_lower='||to_char(ni_lower)||
2677 ' ni_able='||to_char(ni_able));
2678 end if;
2679 end if;
2680
2681 return NI_ABLE;
2682 --
2683 end iom_niable_bands_st_pt;
2684 -- 13803907 end
2685
2686
2687 end hr_gbnidir;
[Home] [Help]