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