[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_EMP_CONT_2007
Source
1 package body PAY_NO_EMP_CONT_2007 as
2 /* $Header: pynoempcont2007.pkb 120.1.12010000.4 2008/09/29 11:49:40 rsengupt ship $ */
3
4
5 ----------------------------------Function definitions----------------------------------------------
6
7 -- Function to calculate the Employer Contributions
8
9 FUNCTION GET_EMPLOYER_DEDUCTION
10 (p_tax_unit_id IN NUMBER
11 ,p_local_unit_id IN NUMBER
12 ,p_jurisdiction_code IN VARCHAR2
13 ,p_payroll_id IN NUMBER
14 ,p_payroll_action_id IN NUMBER
15 ,p_date_earned IN DATE
16 ,p_asg_act_id IN NUMBER
17 ,p_bus_group_id IN NUMBER
18 ,p_under_age_high_rate IN NUMBER
19 ,p_over_age_high_rate IN NUMBER
20 ,p_run_base OUT NOCOPY NUMBER
21 ,p_run_contribution OUT NOCOPY NUMBER
22 ,p_curr_exemption_limit_used OUT NOCOPY NUMBER) RETURN NUMBER IS
23
24
25 -- Local Variables
26
27 l_le_status VARCHAR2(40) ;
28 l_lu_status VARCHAR2(40) ;
29 l_lu_rep_sep VARCHAR2(1) ;
30 l_lu_tax_mun VARCHAR2(200) ;
31
32 l_val NUMBER;
33 l_temp NUMBER;
34 l_check NUMBER;
35 l_check2 NUMBER;
36
37 l_le_exists BOOLEAN;
38 l_le_lu_exists BOOLEAN;
39
40 l_main_index NUMBER;
41 start_index_main NUMBER;
42 end_index_main NUMBER;
43 start_index_calc NUMBER;
44 end_index_calc NUMBER;
45
46 start_index_mu NUMBER;
47 end_index_mu NUMBER;
48 start_index_lu NUMBER;
49 end_index_lu NUMBER;
50
51 l_asg_ers_base NUMBER;
52 l_asg_ers_base2 NUMBER;
53 l_asg_ers_base_diff NUMBER;
54
55 l_lu_ers_base NUMBER;
56 l_lu_ers NUMBER;
57
58 l_le_ers_base NUMBER;
59 l_le_ers NUMBER;
60
61 l_zone VARCHAR2(10);
62 l_zone_temp VARCHAR2(20);
63 l_cell NUMBER;
64
65 l_le_lu_index NUMBER;
66 l_le_index NUMBER;
67
68 l_def_bal_id_1 NUMBER;
69 l_def_bal_id_2 NUMBER;
70 l_def_bal_id_3 NUMBER;
71 l_def_bal_id_4 NUMBER;
72
73
74 l_bal_val_ytd NUMBER;
75 l_bal_val_bimonth NUMBER;
76
77 l_le_run NUMBER;
78
79 l_exemption_limit_used NUMBER;
80 l_exemption_limit_used_yet NUMBER;
81 l_exemption_limit NUMBER;
82
83 -- 2007 Legislative changes for 'Economic Aid' to Employer
84 l_economic_aid NUMBER;
85
86 l_LU_over_limit VARCHAR2(1);
87 l_LE_over_limit VARCHAR2(1);
88
89 l_table_name VARCHAR2(240);
90 l_col_name VARCHAR2(240);
91 l_zone_value VARCHAR2(240);
92
93 l_high_rate NUMBER;
94 l_normal_rate NUMBER;
95 l_diff_rate NUMBER;
96
97 l_saving NUMBER;
98 l_total_saving NUMBER;
99 l_amount_over_limit NUMBER;
100 l_base_over_limit NUMBER;
101
102 l_total_bimonth_base NUMBER;
103 l_old_bimonth_base NUMBER;
104 l_new_bimonth_base NUMBER;
105
106 l_old_run_base NUMBER;
107 l_new_run_base NUMBER;
108
109 l_old_bmth_cont_todate NUMBER;
110 l_new_bmth_cont_todate NUMBER;
111
112 l_curr_zone VARCHAR2(10);
113 l_bimonth_end_date DATE;
114
115
116 -- Local PL/SQL Tables
117
118 g_tab_calc PAY_NO_EMP_CONT_2007.g_tab_calc_tabtype;
119 g_lu_tab PAY_NO_EMP_CONT_2007.g_lu_tabtype;
120 g_mu_tab PAY_NO_EMP_CONT_2007.g_mu_tabtype;
121
122 -- Cursor definitions
123
124 -- csr to get the current assignment_action_ids with the same LE
125 cursor csr_curr_le_asg_act_id
126 (p_tax_unit_id pay_assignment_actions.TAX_UNIT_ID%TYPE
127 ,p_payroll_action_id pay_payroll_actions.PAYROLL_ACTION_ID%type
128 ,p_date_earned DATE ) is
129 select pact.ASSIGNMENT_ACTION_ID
130 from pay_assignment_actions pact
131 ,pay_run_types_f prt
132 where pact.PAYROLL_ACTION_ID = p_payroll_action_id
133 and pact.TAX_UNIT_ID = p_tax_unit_id
134 and prt.LEGISLATION_CODE = 'NO'
135 and prt.RUN_TYPE_NAME = 'Employer Contributions'
136 and nvl(pact.RUN_TYPE_ID,-99) <> prt.RUN_TYPE_ID
137 and p_date_earned between prt.EFFECTIVE_START_DATE and prt.EFFECTIVE_END_DATE ;
138
139
140 ------------- Remove this
141 -- testing the number of rows returned by the above cursor
142 -- csr to get the current assignment_action_ids with the same LE
143 cursor csr_test_aag_act_id
144 (p_tax_unit_id pay_assignment_actions.TAX_UNIT_ID%TYPE
145 ,p_payroll_action_id pay_payroll_actions.PAYROLL_ACTION_ID%type
146 ,p_date_earned DATE ) is
147 select count(*)
148 from pay_assignment_actions pact
149 ,pay_run_types_f prt
150 where pact.PAYROLL_ACTION_ID = p_payroll_action_id
151 and pact.TAX_UNIT_ID = p_tax_unit_id
152 and prt.LEGISLATION_CODE = 'NO'
153 and prt.RUN_TYPE_NAME = 'Employer Contributions'
154 and nvl(pact.RUN_TYPE_ID,-99) <> prt.RUN_TYPE_ID
155 and p_date_earned between prt.EFFECTIVE_START_DATE and prt.EFFECTIVE_END_DATE ;
156
157 --------------
158
159 cursor csr_get_bimonth_end_date (p_date_earned DATE) is
160 select last_day(Add_months(p_date_earned,MOD(TO_NUMBER(TO_CHAR(p_date_earned,'MM')),2)))
161 from dual;
162
163 ------------------- end cursor definitions
164
165 --------------- Begin -------------------------------------
166
167 BEGIN
168
169 -- hr_utility.trace_on(null,'NOEC');
170 hr_utility.trace('2007 EMP_CONT ::: Enterd procedure GET_EMPLOYER_DEDUCTION ----------------------------------');
171 hr_utility.trace('2007 EMP_CONT ::: -------------------------------------------------');
172 hr_utility.trace('2007 EMP_CONT ::: p_tax_unit_id = '|| p_tax_unit_id );
173 hr_utility.trace('2007 EMP_CONT ::: p_local_unit_id = '|| p_local_unit_id );
174 hr_utility.trace('2007 EMP_CONT ::: p_jurisdiction_code = '|| p_jurisdiction_code );
175 hr_utility.trace('2007 EMP_CONT ::: p_payroll_id = '|| p_payroll_id );
176 hr_utility.trace('2007 EMP_CONT ::: p_payroll_action_id = '|| p_payroll_action_id );
177 hr_utility.trace('2007 EMP_CONT ::: p_asg_act_id = '|| p_asg_act_id );
178 hr_utility.trace('2007 EMP_CONT ::: p_bus_group_id = '|| p_bus_group_id );
179 hr_utility.trace('2007 EMP_CONT ::: p_date_earned = '|| p_date_earned );
180 hr_utility.trace('2007 EMP_CONT ::: p_under_age_high_rate = '|| p_under_age_high_rate );
181 hr_utility.trace('2007 EMP_CONT ::: p_over_age_high_rate = '|| p_over_age_high_rate );
182 hr_utility.trace('2007 EMP_CONT ::: -------------------------------------------------');
183
184
185 -- cursor retrieves bimonth end date for the p_date_earned
186 OPEN csr_get_bimonth_end_date (p_date_earned);
187 FETCH csr_get_bimonth_end_date INTO l_bimonth_end_date;
188 CLOSE csr_get_bimonth_end_date;
189
190 -- Get the current assignment's Zone
191
192 -- zone will now be fetched from user tables, have to change the below call
193 -- l_curr_zone := to_number(substr(get_lookup_meaning('NO_TAX_MUNICIPALITY',p_jurisdiction_code),1,1));
194 l_curr_zone := hruserdt.get_table_value (p_bus_group_id, 'NO_TAX_MUNICIPALITY' , 'ZONE', p_jurisdiction_code, p_date_earned ) ;
195
196 hr_utility.trace('2007 EMP_CONT ::: l_curr_zone = '|| l_curr_zone );
197
198 -- Get the Status , Report Separately and Tax Municipality for the Local Unit of the current assignment
199 OPEN PAY_NO_EMP_CONT_2007.get_lu_details(p_local_unit_id);
200 FETCH PAY_NO_EMP_CONT_2007.get_lu_details INTO l_lu_status , l_lu_rep_sep , l_lu_tax_mun ;
201 CLOSE PAY_NO_EMP_CONT_2007.get_lu_details;
202
203 hr_utility.trace('2007 EMP_CONT ::: l_lu_status = '|| l_lu_status );
204 hr_utility.trace('2007 EMP_CONT ::: l_lu_rep_sep = '|| l_lu_rep_sep );
205 hr_utility.trace('2007 EMP_CONT ::: l_lu_tax_mun = '|| l_lu_tax_mun );
206 hr_utility.trace('2007 EMP_CONT ::: goin to test for local unit report separately..........');
207
208 -- check if Local Unit is Report separately = yes
209 IF trim(l_lu_rep_sep) = 'Y'
210
211 THEN ---------------------------------------------------------------------------------------------------------------------------------
212 -- since LU is report separately, check if LE LU combination exists
213
214 hr_utility.trace('2007 EMP_CONT ::: Local unit is Rep Sep');
215
216 start_index_main := NVL (PAY_NO_EMP_CONT_2007.g_tab_main.FIRST, 0) ;
217 end_index_main := NVL (PAY_NO_EMP_CONT_2007.g_tab_main.LAST, 0) ;
218 l_le_lu_exists := FALSE;
219
220 -- loop through existing records for LE LU to check if the current LE LU exists
221
222 WHILE (PAY_NO_EMP_CONT_2007.g_tab_main.EXISTS(start_index_main)) and (start_index_main <= end_index_main) LOOP
223 IF (PAY_NO_EMP_CONT_2007.g_tab_main(start_index_main).legal_employer_id = p_tax_unit_id) AND
224 (PAY_NO_EMP_CONT_2007.g_tab_main(start_index_main).local_unit_id = p_local_unit_id) AND
225 (PAY_NO_EMP_CONT_2007.g_tab_main(start_index_main).zone = l_curr_zone)
226
227 THEN
228 l_le_lu_exists := TRUE;
229 l_le_lu_index := start_index_main;
230
231 hr_utility.trace('2007 EMP_CONT ::: LE LU combination found');
232 hr_utility.trace('2007 EMP_CONT ::: l_le_lu_index = '|| l_le_lu_index );
233 EXIT;
234 END IF;
235 start_index_main := start_index_main + 1;
236 END LOOP; -- end while loop
237
238
239 -- if the LE LU exists, return the values
240 IF l_le_lu_exists
241
242 THEN -------------------------------------------------------------------------------------------------------------------------
243 -- since the combination alreday exists, calculation has been already done, just return values
244 p_run_base := PAY_NO_EMP_CONT_2007.g_tab_main(l_le_lu_index).run_base;
245 p_run_contribution := PAY_NO_EMP_CONT_2007.g_tab_main(l_le_lu_index).run_contribution;
246 p_curr_exemption_limit_used := 0; -- coz this value must have been returned before
247
248 hr_utility.trace('2007 EMP_CONT ::: LU found, since results exist, just returning them');
249 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_le_lu_index = '|| l_le_lu_index );
250 hr_utility.trace('2007 EMP_CONT ::: LU ***** p_run_base = '|| p_run_base );
251 hr_utility.trace('2007 EMP_CONT ::: LU ***** p_run_contribution = '|| p_run_contribution );
252 hr_utility.trace('2007 EMP_CONT ::: LU ***** p_curr_exemption_limit_used = '|| p_curr_exemption_limit_used );
253
254 hr_utility.trace('2007 EMP_CONT ::: LU ***** Leaving procedure-----------------------------------');
255
256 RETURN 1; -- here return le_run and the other values thru OUT parameters
257
258 ELSE --------------------------------------------------------------------------------------------------------------------------
259 -- combination does not exist, calculation has to be done and values returned
260 hr_utility.trace('2007 EMP_CONT ::: LU does not exist, calculate for this LU');
261
262 ------------------- initializing the g_tab_calc table for LE LU combination
263 hr_utility.trace('2007 EMP_CONT ::: LU ***** initializing g_tab_calc ');
264 -- loop for each zone
265
266 g_tab_calc(1).zone := '1' ;
267 g_tab_calc(2).zone := '1a' ;
268 g_tab_calc(3).zone := '2' ;
269 g_tab_calc(4).zone := '3' ;
270 g_tab_calc(5).zone := '4' ;
271 g_tab_calc(6).zone := '4a' ;
272 g_tab_calc(7).zone := '5' ;
273
274
275 FOR i IN 1..7 LOOP
276
277 g_tab_calc(i).under_limit := 'Y';
278 g_tab_calc(i).status := l_lu_status;
279
280 -- initializing all balance values to zero
281 g_tab_calc(i).bimonth_base := 0 ;
282 g_tab_calc(i).run_base := 0 ;
283 g_tab_calc(i).bimonth_contribution := 0 ;
284 g_tab_calc(i).bimonth_contribution_todate := 0 ;
285 g_tab_calc(i).run_contribution := 0 ;
286
287 END LOOP; -- end i loop
288
289 hr_utility.trace('2007 EMP_CONT ::: -- calling display_table_calc function ');
290 l_check := display_table_calc(g_tab_calc);
291 hr_utility.trace('2007 EMP_CONT ::: -- returned from display_table_calc function ');
292
293 hr_utility.trace('2007 EMP_CONT ::: LU ***** finished initializing g_tab_calc ');
294 -------------------- finished initializing the g_tab_lu table
295
296 ------------------ get exemption limit used for LU
297 hr_utility.trace('2007 EMP_CONT ::: LU ***** get exemption limit ');
298
299 -- set the context values for balance
300 pay_balance_pkg.set_context('LOCAL_UNIT_ID',p_local_unit_id);
301
302 -- get defined balance ids
306 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_def_bal_id_1 = '|| l_def_bal_id_1 );
303 l_def_bal_id_1 := get_defined_balance_id('Employer Contribution Exemption Limit Used','_TU_LU_YTD') ;
304 l_def_bal_id_2 := get_defined_balance_id('Employer Contribution Exemption Limit Used','_TU_LU_BIMONTH') ;
305
307 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_def_bal_id_2 = '|| l_def_bal_id_2 );
308
309 -- get the balance value
310 l_bal_val_ytd := pay_balance_pkg.get_value(l_def_bal_id_1,p_asg_act_id,p_tax_unit_id,p_jurisdiction_code,NULL,NULL,NULL,l_bimonth_end_date);
311 l_bal_val_bimonth := pay_balance_pkg.get_value(l_def_bal_id_2,p_asg_act_id,p_tax_unit_id,p_jurisdiction_code,NULL,NULL,NULL,l_bimonth_end_date);
312
313 l_exemption_limit_used_yet := l_bal_val_ytd - l_bal_val_bimonth ;
314
315 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_bal_val_ytd = '|| l_bal_val_ytd );
316 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_bal_val_bimonth = '|| l_bal_val_bimonth );
317 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_exemption_limit_used_yet = '|| l_exemption_limit_used_yet );
318
319 OPEN PAY_NO_EMP_CONT_2007.csr_get_exemption_limit(p_local_unit_id ,p_date_earned );
320
321 -- 2007 Legislative changes for 'Economic Aid' to Employer
322 -- FETCH PAY_NO_EMP_CONT_2007.csr_get_exemption_limit INTO l_exemption_limit ;
323 FETCH PAY_NO_EMP_CONT_2007.csr_get_exemption_limit INTO l_exemption_limit , l_economic_aid ;
324
325 CLOSE PAY_NO_EMP_CONT_2007.csr_get_exemption_limit;
326
327 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_exemption_limit = ' || l_exemption_limit );
328 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_economic_aid = ' || l_economic_aid );
329
330 -- 2007 Legislative changes for 'Economic Aid' to Employer
331 l_exemption_limit := l_exemption_limit - l_economic_aid ;
332
333 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_exemption_limit after reducing eco aid = ' || l_exemption_limit );
334
335
336 IF l_exemption_limit_used_yet >= l_exemption_limit
337 THEN
338 l_LU_over_limit := 'Y';
339 ELSE
340 l_LU_over_limit := 'N';
341 END IF ; -- end if exemption limit check
342
343 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_exemption_limit = '||l_exemption_limit );
344 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_LU_over_limit = '|| l_LU_over_limit );
345 hr_utility.trace('2007 EMP_CONT ::: LU ***** got exemtion limt , leaving' );
346
347 ------------------- got exemption limit used for LU
348
349 ----------------- populating the tables g_lu_tab (LU) and g_mu_tab (MU)
350
351 -- from 2007 , the tax municipality will be picked from the local unit
352
353 hr_utility.trace('2007 EMP_CONT ::: LU ***** populating g_lu_tab and g_mu_tab , entering ' );
354
355 l_temp := populate_tables
356 (p_tax_unit_id
357 ,p_payroll_id
358 ,p_date_earned
359 ,g_lu_tab
360 ,g_mu_tab );
361
362
363 hr_utility.trace('2007 EMP_CONT ::: LU ***** populated g_lu_tab and g_mu_tab , leaving ' );
364 ----------------- Fetch the run base using assignment level balances (at LU level)
365 hr_utility.trace('2007 EMP_CONT ::: LU ***** Fetch the run base using assignment level balances , enetring ' );
366
367 -- get defined balance ids
368 l_def_bal_id_1 := get_defined_balance_id('Employer Contribution Base','_ASG_TU_MU_LU_BIMONTH') ;
369 l_def_bal_id_2 := get_defined_balance_id('Employer Contribution Base 2','_ASG_TU_MU_LU_BIMONTH') ;
370
371 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_def_bal_id_1 = '|| l_def_bal_id_1 );
372 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_def_bal_id_2 = '|| l_def_bal_id_2 );
373
374 ----- test only , Remove it
375 OPEN csr_test_aag_act_id (p_tax_unit_id , p_payroll_action_id, p_date_earned);
376 FETCH csr_test_aag_act_id INTO l_check2;
377 CLOSE csr_test_aag_act_id;
378 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_check2 , no of rows returned by asg act id cursor = '||l_check2 );
379 -- remove till here
380
381 hr_utility.trace('2007 EMP_CONT ::: LU ***** level 0 leave' );
382
383 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
384 pay_balance_pkg.set_context('LOCAL_UNIT_ID',p_local_unit_id);
385
386 -----
387
388 -- loop to get all assignment_action_id in the current payroll_action_id
389 FOR csr1_rec IN csr_curr_le_asg_act_id (p_tax_unit_id , p_payroll_action_id, p_date_earned) LOOP
390
391 hr_utility.trace('2007 EMP_CONT ::: LU ***** level 1 , loop csr_curr_le_asg_act_id ' );
392
393 start_index_mu := NVL (g_mu_tab.FIRST, 0) ;
394 end_index_mu := NVL (g_mu_tab.LAST, 0) ;
395
396 WHILE (g_mu_tab.EXISTS(start_index_mu)) and (start_index_mu <= end_index_mu) LOOP
397
398 hr_utility.trace('2007 EMP_CONT ::: LU ***** level 2, g_mu_tab ' );
399 hr_utility.trace('2007 EMP_CONT ::: LU ***** ASS_ACT_ID = '|| csr1_rec.ASSIGNMENT_ACTION_ID || ' MU = '|| g_mu_tab(start_index_mu)|| ' LU = '|| p_local_unit_id );
400
401 pay_balance_pkg.set_context('JURISDICTION_CODE',g_mu_tab(start_index_mu));
402
403 -- get the balance value
404 l_asg_ers_base := pay_balance_pkg.get_value(l_def_bal_id_1,csr1_rec.ASSIGNMENT_ACTION_ID);
405 l_asg_ers_base2 := pay_balance_pkg.get_value(l_def_bal_id_2,csr1_rec.ASSIGNMENT_ACTION_ID);
409 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_asg_ers_base = '|| l_asg_ers_base );
406 l_asg_ers_base_diff := l_asg_ers_base - l_asg_ers_base2;
407
408
410 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_asg_ers_base2 = '|| l_asg_ers_base2 );
411 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_asg_ers_base_diff = '|| l_asg_ers_base_diff );
412
413 hr_utility.trace('2007 EMP_CONT ::: LU ***** ============== just checking================= ');
414 hr_utility.trace('2007 EMP_CONT ::: LU ***** g_mu_tab(start_index_mu) = '|| g_mu_tab(start_index_mu) );
415
416 l_zone_temp := to_char(g_mu_tab(start_index_mu));
417
418 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_zone_temp = '|| l_zone_temp );
419
420 -- l_zone := to_number(substr(get_lookup_meaning('NO_TAX_MUNICIPALITY',l_zone_temp),1,1));
421 l_zone := hruserdt.get_table_value (p_bus_group_id, 'NO_TAX_MUNICIPALITY' , 'ZONE', l_zone_temp, p_date_earned ) ;
422
423
424 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_zone = '|| l_zone );
425
426 l_cell := lookup_cell(g_tab_calc, l_zone);
427 g_tab_calc(l_cell).run_base := g_tab_calc(l_cell).run_base + l_asg_ers_base_diff ;
428
429 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_cell = '|| l_cell );
430
431 start_index_mu := start_index_mu + 1;
432
433 END LOOP; -- end while loop
434
435 END LOOP; -- end loop csr1_rec
436
437
438 hr_utility.trace('2007 EMP_CONT ::: LU ***** level 0 back' );
439
440 hr_utility.trace('2007 EMP_CONT ::: -- calling display_table_calc function ');
441 l_check := display_table_calc(g_tab_calc);
442 hr_utility.trace('2007 EMP_CONT ::: -- returned from display_table_calc function ');
443
444 hr_utility.trace('2007 EMP_CONT ::: LU ***** Fetched the run base using assignment level balances , leaving ' );
445 ---------------------- Fetched the run base using assignment level balances (at LU level)
446
447 ----------------- Fetch the bimonth base using group level balances (at LU level)
448 hr_utility.trace('2007 EMP_CONT ::: LU ***** Fetch the bimonth base using group level balances , entering ' );
449
450 -- get defined balance ids
451 l_def_bal_id_1 := get_defined_balance_id('Employer Contribution Base','_TU_MU_LU_BIMONTH') ;
452 l_def_bal_id_2 := get_defined_balance_id('Employer Contribution','_TU_MU_LU_BIMONTH') ;
453
454 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_def_bal_id_1 = '|| l_def_bal_id_1 );
455 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_def_bal_id_2 = '|| l_def_bal_id_2 );
456
457 -- loop to get all MU in g_mu_tab
458
459 start_index_mu := NVL (g_mu_tab.FIRST, 0) ;
460 end_index_mu := NVL (g_mu_tab.LAST, 0) ;
461
462 hr_utility.trace('2007 EMP_CONT ::: LU ***** level 0 leave ' );
463
464 pay_balance_pkg.set_context('LOCAL_UNIT_ID',p_local_unit_id);
465
466
467 WHILE (g_mu_tab.EXISTS(start_index_mu)) and (start_index_mu <= end_index_mu) LOOP
468
469 hr_utility.trace('2007 EMP_CONT ::: LU ***** level 1 ,g_mu_tab ' );
470
471 pay_balance_pkg.set_context('JURISDICTION_CODE',g_mu_tab(start_index_mu));
472
473 hr_utility.trace('2007 EMP_CONT ::: LU ***** MU = '|| g_mu_tab(start_index_mu)|| ' LU = '|| p_local_unit_id );
474
475 -- get the balance value
476 l_lu_ers_base := pay_balance_pkg.get_value(l_def_bal_id_1,p_asg_act_id,p_tax_unit_id,g_mu_tab(start_index_mu),NULL,NULL,NULL,l_bimonth_end_date);
477 l_lu_ers := pay_balance_pkg.get_value(l_def_bal_id_2,p_asg_act_id,p_tax_unit_id,g_mu_tab(start_index_mu),NULL,NULL,NULL,l_bimonth_end_date);
478
479 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_lu_ers_base = '|| l_lu_ers_base);
480 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_lu_ers = '||l_lu_ers );
481
482 hr_utility.trace('2007 EMP_CONT ::: LU ***** ============== just checking again ================= ');
483
484 hr_utility.trace('2007 EMP_CONT ::: LU ***** g_mu_tab(start_index_mu) = '|| g_mu_tab(start_index_mu) );
485
486 l_zone_temp := to_char(g_mu_tab(start_index_mu));
487
488 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_zone_temp = '|| l_zone_temp );
489
490 -- l_zone := to_number(substr(get_lookup_meaning('NO_TAX_MUNICIPALITY',l_zone_temp),1,1));
491 l_zone := hruserdt.get_table_value (p_bus_group_id, 'NO_TAX_MUNICIPALITY' , 'ZONE', l_zone_temp, p_date_earned ) ;
492
493 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_zone = '|| l_zone );
494
495 l_cell := lookup_cell(g_tab_calc, l_zone);
496 g_tab_calc(l_cell).bimonth_base := g_tab_calc(l_cell).bimonth_base + l_lu_ers_base ;
497 g_tab_calc(l_cell).bimonth_contribution_todate := g_tab_calc(l_cell).bimonth_contribution_todate + l_lu_ers ;
498
499 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_cell = '|| l_cell );
500
501 start_index_mu := start_index_mu + 1;
502 END LOOP; -- end while loop
503
504 hr_utility.trace('2007 EMP_CONT ::: LU ***** level 0 back' );
505
506 hr_utility.trace('2007 EMP_CONT ::: -- calling display_table_calc function ');
507 l_check := display_table_calc(g_tab_calc);
508 hr_utility.trace('2007 EMP_CONT ::: -- returned from display_table_calc function ');
509
510 hr_utility.trace('2007 EMP_CONT ::: LU ***** Fetched the bimonth base using group level balances , leaving ' );
514 ------------------------------ apply differential rate and check exemption limit at LU Level
511 ---------------------- Fetched the bimonth base using group level balances (at LU level)
512
513
515
516 hr_utility.trace('2007 EMP_CONT ::: LU ***** apply differential rate and check exemption limit , entering ' );
517 --l_lu_status status for LU
518 --l_le_status status for LE
519 l_total_saving := 0;
520
521 -- changing this calc
522
523 -- check if exemption limit already used up
524 IF l_LU_over_limit = 'Y'
525
526 THEN
527 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_LU_over_limit = Y ' );
528
529 -- as limit is over, put under_limit as N for all rows
530 FOR i IN 1..7 LOOP
531 g_tab_calc(i).under_limit := 'N';
532 END LOOP; -- end i loop
533
534 hr_utility.trace('2007 EMP_CONT ::: LU ***** as limit is over, put under_limit as N for all rows ' );
535
536 ELSE
537 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_LU_over_limit = N ' );
538
539 -- limit is not over
540 -- perform exemption limit check only if status = AA,CC,GG
541 IF ( l_lu_status = 'AA' OR l_lu_status = 'GG' OR l_lu_status = 'CC' )
542
543 THEN
544 hr_utility.trace('2007 EMP_CONT ::: LU ***** status is = '|| l_lu_status );
545
546 -------- performing exemption limit check
547 l_table_name := 'NO_NIS_ZONE_RATES';
548
549 -- loop for each zone,under_62 commbination
550 FOR i IN 2..7 LOOP
551
552 -- l_col_name := 'Under Age Percentage';
553 -- BUG Fix : 5999230
554 -- Renaming user column of user table NO_NIS_ZONE_RATES from 'Under Age Percentage' to 'NIS Rates'
555
556 l_col_name := 'NIS Rates';
557 l_high_rate := p_under_age_high_rate;
558
559 hr_utility.trace('2007 EMP_CONT ::: LU ***** exemption limit check ----------------------------' );
560
561 l_zone_value := g_tab_calc(i).zone;
562 -- l_normal_rate := TO_NUMBER(hruserdt.get_table_value (p_bus_group_id, l_table_name, l_col_name, l_zone_value, p_date_earned ));
563 l_normal_rate := fnd_number.canonical_to_number(hruserdt.get_table_value (p_bus_group_id, l_table_name, l_col_name, l_zone_value, p_date_earned ));
564 l_diff_rate := l_high_rate - l_normal_rate ;
565
566
567 l_saving := ( g_tab_calc(i).bimonth_base * l_diff_rate ) / 100 ;
568 l_total_saving := l_total_saving + l_saving ;
569
570 hr_utility.trace('2007 EMP_CONT ::: LU ***** i = '||i||' l_zone_value = '||l_zone_value
571 ||' l_normal_rate = '||l_normal_rate||' l_high_rate = '||l_high_rate
572 ||' l_diff_rate = '||l_diff_rate||' l_saving = '||l_saving||' l_total_saving = '||l_total_saving );
573
574 -- check if exemption limit exceeded
575 IF ((l_exemption_limit_used_yet + l_total_saving) >= l_exemption_limit)
576 THEN
577 hr_utility.trace('2007 EMP_CONT ::: LU******** exemption limit exceeded in table');
578 hr_utility.trace('2007 EMP_CONT ::: LU ***** bimonth_base = '|| g_tab_calc(i).bimonth_base );
579 hr_utility.trace('2007 EMP_CONT ::: LU ***** run_base = '||g_tab_calc(i).run_base );
580 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_zone_value = '|| l_zone_value );
581 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_normal_rate = '|| l_normal_rate );
582 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_high_rate = '|| l_high_rate );
583 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_diff_rate = '|| l_diff_rate );
584 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_saving = '|| l_saving );
585 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_total_saving= '|| l_total_saving );
586 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_exemption_limit_used_yet = '|| l_exemption_limit_used_yet );
587
588 -- get the exceeding amount
589 l_amount_over_limit := ((l_exemption_limit_used_yet + l_total_saving) - l_exemption_limit);
590 l_base_over_limit := (l_amount_over_limit / l_diff_rate) * 100 ;
591
592 -------
593 l_total_bimonth_base := g_tab_calc(i).bimonth_base ;
594 l_old_bimonth_base := l_total_bimonth_base - l_base_over_limit ;
595 l_new_bimonth_base := l_base_over_limit ;
596
597 l_old_run_base := (l_old_bimonth_base / l_total_bimonth_base ) * g_tab_calc(i).run_base ;
598 l_new_run_base := (l_new_bimonth_base / l_total_bimonth_base ) * g_tab_calc(i).run_base ;
599
600 l_old_bmth_cont_todate := ( l_old_bimonth_base / l_total_bimonth_base ) * g_tab_calc(i).bimonth_contribution_todate ;
601 l_new_bmth_cont_todate := ( l_new_bimonth_base / l_total_bimonth_base ) * g_tab_calc(i).bimonth_contribution_todate ;
602
603 g_tab_calc(i).bimonth_base := l_old_bimonth_base ;
604 g_tab_calc(i).run_base := l_old_run_base ;
605 g_tab_calc(i).bimonth_contribution_todate := l_old_bmth_cont_todate ;
606
607 -------
608
609 -- to set the actual total saving coz here the total saving might cross the exemption limit provided
610 -- set the new l_total_saving
611
615 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_base_over_limit = '|| l_base_over_limit );
612 l_total_saving := l_exemption_limit - l_exemption_limit_used_yet ;
613
614 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_amount_over_limit = '|| l_amount_over_limit );
616 hr_utility.trace('2007 EMP_CONT ::: LU ***** NEW bimonth base for current row = '|| g_tab_calc(i).bimonth_base );
617 hr_utility.trace('2007 EMP_CONT ::: LU ***** NEW l_total_saving = '|| l_total_saving );
618
619 -- insert a new row for exceeded limit
620
621 g_tab_calc(8).zone := g_tab_calc(i).zone;
622 g_tab_calc(8).under_limit := 'N';
623 g_tab_calc(8).status := g_tab_calc(i).status;
624 g_tab_calc(8).bimonth_base := l_new_bimonth_base ;
625 g_tab_calc(8).run_base := l_new_run_base ;
626 g_tab_calc(8).bimonth_contribution := g_tab_calc(i).bimonth_contribution ;
627 g_tab_calc(8).bimonth_contribution_todate := l_new_bmth_cont_todate ;
628 g_tab_calc(8).run_contribution := g_tab_calc(i).run_contribution ;
629
630 -- finished inserting new row
631
632 -- set remaining rows under_limit = N
633 FOR j IN i+1..7 LOOP
634 g_tab_calc(j).under_limit := 'N';
635 END LOOP; --end j loop
636
637 l_LU_over_limit := 'Y'; -- indicating exemption limit on LU level is over
638
639 --hr_utility.trace('2007 EMP_CONT ::: LU ***** l_LU_over_limit Y' );
640 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_LU_over_limit = '||l_LU_over_limit );
641
642 EXIT; -- exit loop i since no more check is required
643 END IF; -- end check if exemption limit exceeded
644
645 -- for AA and GG, only exemption limit check required for zone 1a, exit after that
646 IF ( l_lu_status = 'AA' OR l_lu_status = 'GG')
647 THEN EXIT ;
648 END IF;
649
650 END LOOP; -- end i loop
651
652 -------- performed exemption limit check
653
654 END IF; -- end if status = AA,CC,GG
655
656 END IF; -- end if l_LU_over_limit = Y
657
658 -- from the total saving, removing exemption limit reported in the bimonth period
659 -- exemption limit used currently at LU level
660 l_exemption_limit_used := l_total_saving - l_bal_val_bimonth ;
661
662 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_total_saving = '||l_total_saving );
663 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_bal_val_bimonth = '||l_bal_val_bimonth );
664 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_exemption_limit_used = '||l_exemption_limit_used );
665
666 hr_utility.trace('2007 EMP_CONT ::: -- calling display_table_calc function ');
667 l_check := display_table_calc(g_tab_calc);
668 hr_utility.trace('2007 EMP_CONT ::: -- returned from display_table_calc function ');
669
670 hr_utility.trace('2007 EMP_CONT ::: LU ***** applied differential rate and check exemption limit , leaving ' );
671 ------------------------------ applied differential rate and check exemption limit at LU Level
672
673 -- ec_main_calculation function call for LU
674 hr_utility.trace('2007 EMP_CONT ::: LU ***** ec_main_calculation function call for LU , entering ' );
675
676 l_main_index := ec_main_calculation ( g_tab_calc
677 ,PAY_NO_EMP_CONT_2007.g_tab_main
678 ,p_tax_unit_id
679 ,p_local_unit_id
680 ,l_exemption_limit_used
681 ,l_lu_status
682 ,p_bus_group_id
683 ,p_date_earned
684 ,p_under_age_high_rate
685 ,p_over_age_high_rate
686 ,l_curr_zone ) ;
687
688 hr_utility.trace('2007 EMP_CONT ::: -- calling display_table_calc function ');
689 l_check := display_table_calc(g_tab_calc);
690 hr_utility.trace('2007 EMP_CONT ::: -- returned from display_table_calc function ');
691
692 hr_utility.trace('2007 EMP_CONT ::: LU ***** ec_main_calculation function call for LU , leaving ' );
693
694 -- done all caclculation and entered values in the main table for next time usage
695
696 -- returning values at LU level
697 p_run_base := PAY_NO_EMP_CONT_2007.g_tab_main(l_main_index).run_base;
698 p_run_contribution := PAY_NO_EMP_CONT_2007.g_tab_main(l_main_index).run_contribution;
699 p_curr_exemption_limit_used := PAY_NO_EMP_CONT_2007.g_tab_main(l_main_index).exemption_limit_used;
700
701 hr_utility.trace('2007 EMP_CONT ::: LU ***** l_main_index = '|| l_main_index );
702 hr_utility.trace('2007 EMP_CONT ::: LU ***** p_run_base = '|| p_run_base );
703 hr_utility.trace('2007 EMP_CONT ::: LU ***** p_run_contribution = '|| p_run_contribution );
704 hr_utility.trace('2007 EMP_CONT ::: LU ***** p_curr_exemption_limit_used = '|| p_curr_exemption_limit_used );
705
706 hr_utility.trace('2007 EMP_CONT ::: LU ***** Leaving procedure-----------------------------------');
707
708 RETURN 1; -- here return 1 , other values thru OUT parameters
709
710 END IF; --end if l_le_lu_exists
711
712
713 ELSE ---------------------------------------------------------------------------------------------------------------------------------
714 -- since LU is NOT report separately, check if LE -9999 combination exists
715
719 start_index_main := NVL (PAY_NO_EMP_CONT_2007.g_tab_main.FIRST, 0) ;
716 hr_utility.trace('2007 EMP_CONT ::: LU is not report separately');
717 hr_utility.trace('2007 EMP_CONT ::: checking if LE exists');
718
720 end_index_main := NVL (PAY_NO_EMP_CONT_2007.g_tab_main.LAST, 0) ;
721 l_le_exists := FALSE;
722
723 -- loop through existing records for LE and -9999 to check if the current LE exists
724 WHILE (PAY_NO_EMP_CONT_2007.g_tab_main.EXISTS(start_index_main)) and (start_index_main <= end_index_main) LOOP
725
726 IF (PAY_NO_EMP_CONT_2007.g_tab_main(start_index_main).legal_employer_id = p_tax_unit_id) AND
727 (PAY_NO_EMP_CONT_2007.g_tab_main(start_index_main).local_unit_id = -9999) AND
728 (PAY_NO_EMP_CONT_2007.g_tab_main(start_index_main).zone = l_curr_zone)
729
730 THEN
731 l_le_exists := TRUE;
732 l_le_index := start_index_main;
733
734 hr_utility.trace('2007 EMP_CONT ::: LE exists');
735 hr_utility.trace('2007 EMP_CONT ::: l_le_index = '||l_le_index);
736
737 EXIT;
738 END IF;
739 start_index_main := start_index_main + 1;
740 END LOOP; -- end while loop
741
742
743 -- if the LE and -9999 exists, return the values
744 IF l_le_exists
745
746 THEN -------------------------------------------------------------------------------------------------------------------------
747 p_run_base := PAY_NO_EMP_CONT_2007.g_tab_main(l_le_index).run_base;
748 p_run_contribution := PAY_NO_EMP_CONT_2007.g_tab_main(l_le_index).run_contribution;
749 p_curr_exemption_limit_used := 0; -- coz this value must have been returned before
750
751 hr_utility.trace('2007 EMP_CONT ::: LE #### LE already exists, so just returning vales' );
752 hr_utility.trace('2007 EMP_CONT ::: LE #### l_le_index = '|| l_le_index );
753 hr_utility.trace('2007 EMP_CONT ::: LE #### p_run_base = '|| p_run_base );
754 hr_utility.trace('2007 EMP_CONT ::: LE #### p_run_contribution = '|| p_run_contribution );
755 hr_utility.trace('2007 EMP_CONT ::: LE #### p_curr_exemption_limit_used = '|| p_curr_exemption_limit_used );
756
757 hr_utility.trace('2007 EMP_CONT ::: LE #### Leaving procedure-----------------------------------');
758
759
760 RETURN 1; -- here return le_run and the other values thru OUT parameters
761
762 ELSE --------------------------------------------------------------------------------------------------------------------------
763 -- combination does not exist, calculation has to be done and values returned
764
765 ---------- initializing the g_tab_calc table for LE and -9999 combination
766 hr_utility.trace('2007 EMP_CONT ::: LE #### initializing the g_tab_calc , entering');
767
768 OPEN PAY_NO_EMP_CONT_2007.get_le_status(p_tax_unit_id);
769 FETCH PAY_NO_EMP_CONT_2007.get_le_status INTO l_le_status;
770 CLOSE PAY_NO_EMP_CONT_2007.get_le_status;
771
772 hr_utility.trace('2007 EMP_CONT ::: LE #### l_le_status = '|| l_le_status );
773
774 -- loop for each zone
775
776 g_tab_calc(1).zone := '1' ;
777 g_tab_calc(2).zone := '1a' ;
778 g_tab_calc(3).zone := '2' ;
779 g_tab_calc(4).zone := '3' ;
780 g_tab_calc(5).zone := '4' ;
781 g_tab_calc(6).zone := '4a' ;
782 g_tab_calc(7).zone := '5' ;
783
784
785 FOR i IN 1..7 LOOP
786
787 g_tab_calc(i).under_limit := 'Y';
788 g_tab_calc(i).status := l_le_status;
789
790 -- initializing all balance values to zero
791 g_tab_calc(i).bimonth_base := 0 ;
792 g_tab_calc(i).run_base := 0 ;
793 g_tab_calc(i).bimonth_contribution := 0 ;
794 g_tab_calc(i).bimonth_contribution_todate := 0 ;
795 g_tab_calc(i).run_contribution := 0 ;
796
797 END LOOP; -- end i loop
798
799 hr_utility.trace('2007 EMP_CONT ::: -- calling display_table_calc function ');
800 l_check := display_table_calc(g_tab_calc);
801 hr_utility.trace('2007 EMP_CONT ::: -- returned from display_table_calc function ');
802
803 hr_utility.trace('2007 EMP_CONT ::: LE #### finished initializing the g_tab_calc , leaving ');
804 ---------------- finished initializing the g_tab_lu table
805
806 ---------------- get exemption limit used for LE
807 hr_utility.trace('2007 EMP_CONT ::: LE #### get exemption limit used for LE , entering');
808
809 -- set the context values for balance
810
811 pay_balance_pkg.set_context('LOCAL_UNIT_ID',p_local_unit_id);
812
813 -- get defined balance ids
814 l_def_bal_id_1 := get_defined_balance_id('Employer Contribution Exemption Limit Used','_TU_LU_YTD') ;
815 l_def_bal_id_2 := get_defined_balance_id('Employer Contribution Exemption Limit Used','_TU_LU_BIMONTH') ;
816
817 hr_utility.trace('2007 EMP_CONT ::: LE #### l_def_bal_id_1 = '|| l_def_bal_id_1 );
818 hr_utility.trace('2007 EMP_CONT ::: LE #### l_def_bal_id_2 = '|| l_def_bal_id_2 );
819
820 -- get the balance value
821 l_bal_val_ytd := pay_balance_pkg.get_value(l_def_bal_id_1,p_asg_act_id,p_tax_unit_id,p_jurisdiction_code,NULL,NULL,NULL,l_bimonth_end_date);
822 l_bal_val_bimonth := pay_balance_pkg.get_value(l_def_bal_id_2,p_asg_act_id,p_tax_unit_id,p_jurisdiction_code,NULL,NULL,NULL,l_bimonth_end_date);
823
824 --l_bal_val_ytd := 0 ;
825 --l_bal_val_bimonth := 0 ;
826
827 l_exemption_limit_used_yet := l_bal_val_ytd - l_bal_val_bimonth ;
828
832
829 hr_utility.trace('2007 EMP_CONT ::: LE #### l_bal_val_ytd = '|| l_bal_val_ytd );
830 hr_utility.trace('2007 EMP_CONT ::: LE #### l_bal_val_bimonth = '|| l_bal_val_bimonth );
831 hr_utility.trace('2007 EMP_CONT ::: LE #### l_exemption_limit_used_yet = '|| l_exemption_limit_used_yet );
833 OPEN PAY_NO_EMP_CONT_2007.csr_get_exemption_limit(p_tax_unit_id ,p_date_earned );
834
835 -- 2007 Legislative changes for 'Economic Aid' to Employer
836 -- FETCH PAY_NO_EMP_CONT_2007.csr_get_exemption_limit INTO l_exemption_limit ;
837 FETCH PAY_NO_EMP_CONT_2007.csr_get_exemption_limit INTO l_exemption_limit , l_economic_aid ;
838
839 CLOSE PAY_NO_EMP_CONT_2007.csr_get_exemption_limit;
840
841 hr_utility.trace('2007 EMP_CONT ::: LE #### l_exemption_limit = ' || l_exemption_limit );
842 hr_utility.trace('2007 EMP_CONT ::: LE #### l_economic_aid = ' || l_economic_aid );
843
844 -- 2007 Legislative changes for 'Economic Aid' to Employer
845 l_exemption_limit := l_exemption_limit - l_economic_aid ;
846
847 hr_utility.trace('2007 EMP_CONT ::: LE #### l_exemption_limit after reducing eco aid = ' || l_exemption_limit );
848
849 IF l_exemption_limit_used_yet >= l_exemption_limit
850 THEN
851 l_LE_over_limit := 'Y';
852 ELSE
853 l_LE_over_limit := 'N';
854 END IF ; -- end if exemption limit check
855
856 hr_utility.trace('2007 EMP_CONT ::: LE #### l_exemption_limit = '|| l_exemption_limit );
857 hr_utility.trace('2007 EMP_CONT ::: LE #### l_LE_over_limit = '|| l_LE_over_limit );
858 hr_utility.trace('2007 EMP_CONT ::: LE #### got exemption limit used for LE , leaving ');
859 ------------------- got exemption limit used for LE
860
861 ------------------ populating the tables g_lu_tab (LU) and g_mu_tab (MU)
862 hr_utility.trace('2007 EMP_CONT ::: LE #### populating the tables g_lu_tab and g_mu_tab , entering');
863
864 -- from 2007 , the tax municipality will be picked from the local unit
865
866 l_temp := populate_tables
867 (p_tax_unit_id
868 ,p_payroll_id
869 ,p_date_earned
870 ,g_lu_tab
871 ,g_mu_tab );
872
873 hr_utility.trace('2007 EMP_CONT ::: LE #### populating the tables g_lu_tab and g_mu_tab, leaving ');
874
875 -------------------- Fetch the run base using assignment level balances (at LE level)
876 hr_utility.trace('2007 EMP_CONT ::: LE #### Fetch the run base using assignment level balances , entering');
877
878 -- get defined balance ids
879 l_def_bal_id_1 := get_defined_balance_id('Employer Contribution Base','_ASG_TU_MU_LU_BIMONTH') ;
880 l_def_bal_id_2 := get_defined_balance_id('Employer Contribution Base 2','_ASG_TU_MU_LU_BIMONTH') ;
881
882 hr_utility.trace('2007 EMP_CONT ::: LE #### l_def_bal_id_1 = '|| l_def_bal_id_1 );
883 hr_utility.trace('2007 EMP_CONT ::: LE #### l_def_bal_id_2 = '|| l_def_bal_id_2 );
884
885 ----- test only , Remove it
886 OPEN csr_test_aag_act_id (p_tax_unit_id , p_payroll_action_id, p_date_earned);
887 FETCH csr_test_aag_act_id INTO l_check2;
888 CLOSE csr_test_aag_act_id;
889 hr_utility.trace('2007 EMP_CONT ::: LE #### l_check2 , no of rows returned by asg act id cursor = '||l_check2 );
890 -- remove till here
891
892 hr_utility.trace('2007 EMP_CONT ::: LE #### level 0 leave' );
893
894 -- change 2b -1
895 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
896 ----
897
898 -- loop to get all assignment_action_id in the current payroll_action_id
899 FOR csr1_rec IN csr_curr_le_asg_act_id (p_tax_unit_id , p_payroll_action_id, p_date_earned) LOOP
900
901 hr_utility.trace('2007 EMP_CONT ::: LE #### level 1 , loop csr_curr_le_asg_act_id ' );
902
903 start_index_lu := NVL (g_lu_tab.FIRST, 0) ;
904 end_index_lu := NVL (g_lu_tab.LAST, 0) ;
905
906 WHILE (g_lu_tab.EXISTS(start_index_lu)) and (start_index_lu <= end_index_lu) LOOP
907
908 hr_utility.trace('2007 EMP_CONT ::: LE #### level 2, g_lu_tab ' );
909
910 start_index_mu := NVL (g_mu_tab.FIRST, 0) ;
911 end_index_mu := NVL (g_mu_tab.LAST, 0) ;
912
913 -- Change 2b - 2
914 pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_lu_tab(start_index_lu));
915 -----
916
917 WHILE (g_mu_tab.EXISTS(start_index_mu)) and (start_index_mu <= end_index_mu) LOOP
918
919 hr_utility.trace('2007 EMP_CONT ::: LE #### level 3, g_mu_tab ' );
920 hr_utility.trace('2007 EMP_CONT ::: LE #### ASS_ACT_ID = '|| csr1_rec.ASSIGNMENT_ACTION_ID || ' MU = '|| g_mu_tab(start_index_mu)|| ' LU = '|| g_lu_tab(start_index_lu) );
921
922 --setting the context values
923 pay_balance_pkg.set_context('JURISDICTION_CODE',g_mu_tab(start_index_mu));
924
925 -- get the balance value
926 l_asg_ers_base := pay_balance_pkg.get_value(l_def_bal_id_1,csr1_rec.ASSIGNMENT_ACTION_ID);
927 l_asg_ers_base2 := pay_balance_pkg.get_value(l_def_bal_id_2,csr1_rec.ASSIGNMENT_ACTION_ID);
928 l_asg_ers_base_diff := l_asg_ers_base - l_asg_ers_base2;
929
930 hr_utility.trace('2007 EMP_CONT ::: LE #### l_asg_ers_base = '|| l_asg_ers_base );
931 hr_utility.trace('2007 EMP_CONT ::: LE #### l_asg_ers_base2 = '|| l_asg_ers_base2 );
932 hr_utility.trace('2007 EMP_CONT ::: LE #### l_asg_ers_base_diff = '|| l_asg_ers_base_diff );
933
934 hr_utility.trace('2007 EMP_CONT ::: LE #### ============== just checking================= ');
935
939
936 hr_utility.trace('2007 EMP_CONT ::: LE #### g_mu_tab(start_index_mu) = '|| g_mu_tab(start_index_mu) );
937
938 l_zone_temp := to_char(g_mu_tab(start_index_mu));
940 hr_utility.trace('2007 EMP_CONT ::: LE #### l_zone_temp = '|| l_zone_temp );
941
942 -- l_zone := to_number(substr(get_lookup_meaning('NO_TAX_MUNICIPALITY',l_zone_temp),1,1));
943 l_zone := hruserdt.get_table_value (p_bus_group_id, 'NO_TAX_MUNICIPALITY' , 'ZONE', l_zone_temp, p_date_earned ) ;
944
945 hr_utility.trace('2007 EMP_CONT ::: LE #### l_zone = '|| l_zone );
946
947 l_cell := lookup_cell(g_tab_calc,l_zone);
948 g_tab_calc(l_cell).run_base := g_tab_calc(l_cell).run_base + l_asg_ers_base_diff ;
949
950 hr_utility.trace('2007 EMP_CONT ::: LE #### l_cell = '|| l_cell );
951
952 start_index_mu := start_index_mu + 1;
953 END LOOP; -- end while loop g_mu_tab
954
955 start_index_lu := start_index_lu + 1;
956 END LOOP; -- end while loop g_lu_tab
957
958 END LOOP; -- end loop csr1_rec
959
960 hr_utility.trace('2007 EMP_CONT ::: LE #### level 0 back' );
961
962 hr_utility.trace('2007 EMP_CONT ::: -- calling display_table_calc function ');
963 l_check := display_table_calc(g_tab_calc);
964 hr_utility.trace('2007 EMP_CONT ::: -- returned from display_table_calc function ');
965
966 hr_utility.trace('2007 EMP_CONT ::: LE #### Fetched the run base using assignment level balances , leaving');
967 -------------------- Fetched the run base using assignment level balances (at LE level)
968
969 -------------------- Fetch the bimonth base using group level balances (at LE level)
970 hr_utility.trace('2007 EMP_CONT ::: LE #### Fetch the bimonth base using group level balances , entering');
971
972 -- get defined balance ids
973 l_def_bal_id_1 := get_defined_balance_id('Employer Contribution Base','_TU_MU_LU_BIMONTH') ;
974 l_def_bal_id_2 := get_defined_balance_id('Employer Contribution','_TU_MU_LU_BIMONTH') ;
975
976 hr_utility.trace('2007 EMP_CONT ::: LE #### l_def_bal_id_1 = '|| l_def_bal_id_1 );
977 hr_utility.trace('2007 EMP_CONT ::: LE #### l_def_bal_id_2 = '|| l_def_bal_id_2 );
978
979 -- loop to get all LU in g_lu_tab
980
981 start_index_lu := NVL (g_lu_tab.FIRST, 0) ;
982 end_index_lu := NVL (g_lu_tab.LAST, 0) ;
983
984 hr_utility.trace('2007 EMP_CONT ::: LE #### level 0 leave ' );
985
986 -- change 3b -1
987 --pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
988 ----
989
990 WHILE (g_lu_tab.EXISTS(start_index_lu)) and (start_index_lu <= end_index_lu) LOOP
991
992 hr_utility.trace('2007 EMP_CONT ::: LE #### level 1 ,g_lu_tab ' );
993
994 -- loop to get all MU in g_mu_tab
995 start_index_mu := NVL (g_mu_tab.FIRST, 0) ;
996 end_index_mu := NVL (g_mu_tab.LAST, 0) ;
997
998 -- change 3b -2
999 pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_lu_tab(start_index_lu));
1000 ----
1001
1002 WHILE (g_mu_tab.EXISTS(start_index_mu)) and (start_index_mu <= end_index_mu) LOOP
1003
1004 hr_utility.trace('2007 EMP_CONT ::: LE #### level 2 ,g_mu_tab ' );
1005
1006 hr_utility.trace('2007 EMP_CONT ::: LE #### MU = '|| g_mu_tab(start_index_mu)|| ' LU = '|| g_lu_tab(start_index_lu) );
1007
1008 --setting the context values
1009 --pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
1010 pay_balance_pkg.set_context('JURISDICTION_CODE',g_mu_tab(start_index_mu));
1011 --pay_balance_pkg.set_context('LOCAL_UNIT_ID',g_lu_tab(start_index_lu));
1012
1013 -- get the balance value
1014 l_le_ers_base := pay_balance_pkg.get_value(l_def_bal_id_1,p_asg_act_id,p_tax_unit_id,g_mu_tab(start_index_mu),NULL,NULL,NULL,l_bimonth_end_date);
1015 l_le_ers := pay_balance_pkg.get_value(l_def_bal_id_2,p_asg_act_id,p_tax_unit_id,g_mu_tab(start_index_mu),NULL,NULL,NULL,l_bimonth_end_date);
1016
1017 hr_utility.trace('2007 EMP_CONT ::: LE #### l_le_ers_base = '|| l_le_ers_base);
1018 hr_utility.trace('2007 EMP_CONT ::: LE #### l_le_ers = '||l_le_ers );
1019
1020 hr_utility.trace('2007 EMP_CONT ::: LE #### ============== just checking again ================= ');
1021
1022 hr_utility.trace('2007 EMP_CONT ::: LE #### g_mu_tab(start_index_mu) = '|| g_mu_tab(start_index_mu) );
1023
1024 l_zone_temp := to_char(g_mu_tab(start_index_mu));
1025
1026 hr_utility.trace('2007 EMP_CONT ::: LE #### l_zone_temp = '|| l_zone_temp );
1027
1028 -- l_zone := to_number(substr(get_lookup_meaning('NO_TAX_MUNICIPALITY',l_zone_temp),1,1));
1029 l_zone := hruserdt.get_table_value (p_bus_group_id, 'NO_TAX_MUNICIPALITY' , 'ZONE', l_zone_temp, p_date_earned ) ;
1030
1031 hr_utility.trace('2007 EMP_CONT ::: LE #### l_zone = '|| l_zone );
1032
1033 l_cell := lookup_cell(g_tab_calc,l_zone);
1034 g_tab_calc(l_cell).bimonth_base := g_tab_calc(l_cell).bimonth_base + l_le_ers_base ;
1035 g_tab_calc(l_cell).bimonth_contribution_todate := g_tab_calc(l_cell).bimonth_contribution_todate + l_le_ers ;
1036
1037 hr_utility.trace('2007 EMP_CONT ::: LE #### l_cell = '|| l_cell );
1038
1039 start_index_mu := start_index_mu + 1;
1040 END LOOP; -- end while loop g_mu_tab
1041
1042 start_index_lu := start_index_lu + 1;
1043 END LOOP; -- end while loop g_lu_tab
1044
1045 hr_utility.trace('2007 EMP_CONT ::: LE #### level 0 back' );
1046
1050
1047 hr_utility.trace('2007 EMP_CONT ::: -- calling display_table_calc function ');
1048 l_check := display_table_calc(g_tab_calc);
1049 hr_utility.trace('2007 EMP_CONT ::: -- returned from display_table_calc function ');
1051 hr_utility.trace('2007 EMP_CONT ::: LE #### Fetch the bimonth base using group level balances , leaving ');
1052 -------------------- Fetched the bimonth base using group level balances (at LE level)
1053
1054 ------------------------------ apply differential rate and check exemption limit at LE Level
1055 hr_utility.trace('2007 EMP_CONT ::: LE #### apply differential rate and check exemption limit , entering ');
1056
1057 --l_lu_status --status for LU
1058 --l_le_status --status for LE
1059 l_total_saving := 0;
1060
1061 -- check if exemption limit already used up
1062 IF l_LE_over_limit = 'Y'
1063
1064 THEN
1065 hr_utility.trace('2007 EMP_CONT ::: LE #### l_LE_over_limit = Y ' );
1066
1067 -- as limit is over, put under_limit as N for all rows
1068 FOR i IN 1..7 LOOP
1069 g_tab_calc(i).under_limit := 'N';
1070 END LOOP; -- end i loop
1071
1072 hr_utility.trace('2007 EMP_CONT ::: LE #### as limit is over, put under_limit as N for all rows ');
1073
1074 ELSE
1075 hr_utility.trace('2007 EMP_CONT ::: LE #### l_LE_over_limit = N ' );
1076
1077 -- limit is not over
1078 -- perform exemption limit check only if status = AA,CC,GG
1079 IF ( l_le_status = 'AA' OR l_le_status = 'GG' OR l_le_status = 'CC' )
1080
1081 THEN
1082 hr_utility.trace('2007 EMP_CONT ::: LE #### status is = '|| l_le_status );
1083
1084 -------- performing exemption limit check
1085 l_table_name := 'NO_NIS_ZONE_RATES';
1086
1087 -- loop for each zone
1088 FOR i IN 2..7 LOOP
1089
1090 -- l_col_name := 'Under Age Percentage';
1091 -- BUG Fix : 5999230
1092 -- Renaming user column of user table NO_NIS_ZONE_RATES from 'Under Age Percentage' to 'NIS Rates'
1093
1094 l_col_name := 'NIS Rates';
1095 l_high_rate := p_under_age_high_rate;
1096
1097 hr_utility.trace('2007 EMP_CONT ::: LE #### exemption limit check ----------------------' );
1098
1099 l_zone_value := to_char(g_tab_calc(i).zone);
1100 -- l_normal_rate := TO_NUMBER(hruserdt.get_table_value (p_bus_group_id, l_table_name, l_col_name, l_zone_value, p_date_earned ));
1101 l_normal_rate := fnd_number.canonical_to_number(hruserdt.get_table_value (p_bus_group_id, l_table_name, l_col_name, l_zone_value, p_date_earned ));
1102 l_diff_rate := l_high_rate - l_normal_rate ;
1103
1104 l_saving := ( g_tab_calc(i).bimonth_base * l_diff_rate ) / 100 ;
1105 l_total_saving := l_total_saving + l_saving ;
1106
1107 hr_utility.trace('2007 EMP_CONT ::: LE #### i = '||i||' l_zone_value = '||l_zone_value
1108 ||' l_normal_rate = '||l_normal_rate||' l_high_rate = '||l_high_rate
1109 ||' l_diff_rate = '||l_diff_rate||' l_saving = '||l_saving||' l_total_saving = '||l_total_saving );
1110
1111 -- check if exemption limit exceeded
1112 IF ((l_exemption_limit_used_yet + l_total_saving) >= l_exemption_limit)
1113 THEN
1114 hr_utility.trace('2007 EMP_CONT ::: LE #### exemption limit exceeded in table');
1115 hr_utility.trace('2007 EMP_CONT ::: LE #### bimonth_base = '|| g_tab_calc(i).bimonth_base );
1116 hr_utility.trace('2007 EMP_CONT ::: LE #### run_base = '||g_tab_calc(i).run_base );
1117 hr_utility.trace('2007 EMP_CONT ::: LE #### l_zone_value = '|| l_zone_value );
1118 hr_utility.trace('2007 EMP_CONT ::: LE #### l_normal_rate = '|| l_normal_rate );
1119 hr_utility.trace('2007 EMP_CONT ::: LE #### l_high_rate = '|| l_high_rate );
1120 hr_utility.trace('2007 EMP_CONT ::: LE #### l_diff_rate = '|| l_diff_rate );
1121 hr_utility.trace('2007 EMP_CONT ::: LE #### l_saving = '|| l_saving );
1122 hr_utility.trace('2007 EMP_CONT ::: LE #### l_total_saving= '|| l_total_saving );
1123 hr_utility.trace('2007 EMP_CONT ::: LE #### l_exemption_limit_used_yet = '|| l_exemption_limit_used_yet );
1124
1125 -- get the exceeding amount
1126 l_amount_over_limit := ((l_exemption_limit_used_yet + l_total_saving) - l_exemption_limit);
1127 l_base_over_limit := ( l_amount_over_limit / l_diff_rate ) * 100 ;
1128 --g_tab_calc(i).bimonth_base := g_tab_calc(i).bimonth_base - l_base_over_limit;
1129
1130 -----------
1131 l_total_bimonth_base := g_tab_calc(i).bimonth_base ;
1132 l_old_bimonth_base := l_total_bimonth_base - l_base_over_limit ;
1133 l_new_bimonth_base := l_base_over_limit ;
1134
1135 l_old_run_base := (l_old_bimonth_base / l_total_bimonth_base ) * g_tab_calc(i).run_base ;
1136 l_new_run_base := (l_new_bimonth_base / l_total_bimonth_base ) * g_tab_calc(i).run_base ;
1137
1138 l_old_bmth_cont_todate := ( l_old_bimonth_base / l_total_bimonth_base ) * g_tab_calc(i).bimonth_contribution_todate ;
1139 l_new_bmth_cont_todate := ( l_new_bimonth_base / l_total_bimonth_base ) * g_tab_calc(i).bimonth_contribution_todate ;
1140
1141
1142 g_tab_calc(i).bimonth_base := l_old_bimonth_base ;
1143 g_tab_calc(i).run_base := l_old_run_base ;
1144 g_tab_calc(i).bimonth_contribution_todate := l_old_bmth_cont_todate ;
1145
1146 ------------
1150
1147
1148 -- to set the actual total saving coz here the total saving might cross the exemption limit provided
1149 -- set the new l_total_saving
1151 l_total_saving := l_exemption_limit - l_exemption_limit_used_yet ;
1152
1153 hr_utility.trace('2007 EMP_CONT ::: LE #### l_amount_over_limit = '|| l_amount_over_limit );
1154 hr_utility.trace('2007 EMP_CONT ::: LE #### l_base_over_limit = '|| l_base_over_limit );
1155 hr_utility.trace('2007 EMP_CONT ::: LE #### NEW bimonth base for current row = '|| g_tab_calc(i).bimonth_base );
1156 hr_utility.trace('2007 EMP_CONT ::: LE #### NEW l_total_saving = '|| l_total_saving );
1157
1158 -- insert a new row for exceeded limit
1159
1160 g_tab_calc(8).zone := g_tab_calc(i).zone;
1161 g_tab_calc(8).under_limit := 'N';
1162 g_tab_calc(8).status := g_tab_calc(i).status;
1163 g_tab_calc(8).bimonth_base := l_new_bimonth_base ;
1164 g_tab_calc(8).run_base := l_new_run_base ;
1165 g_tab_calc(8).bimonth_contribution := g_tab_calc(i).bimonth_contribution ;
1166 g_tab_calc(8).bimonth_contribution_todate := l_new_bmth_cont_todate ;
1167 g_tab_calc(8).run_contribution := g_tab_calc(i).run_contribution ;
1168
1169 -- finished inserting new row
1170
1171 -- set remaining rows under_limit = N
1172 FOR j IN i+1..7 LOOP
1173 g_tab_calc(j).under_limit := 'N';
1174 END LOOP; --end j loop
1175
1176 l_LE_over_limit := 'Y'; -- indicating exemption limit on LE level is over
1177
1178 --hr_utility.trace('2007 EMP_CONT ::: LE #### l_LE_over_limit Y' );
1179 hr_utility.trace('2007 EMP_CONT ::: LE #### l_LE_over_limit = '||l_LE_over_limit );
1180
1181 EXIT; -- exit loop i since no more check is required
1182 END IF; -- end check if exemption limit exceeded
1183
1184 -- for AA and GG, only exemption limit check required for zone 1a, exit after that
1185 IF ( l_le_status = 'AA' OR l_le_status = 'GG')
1186 THEN EXIT ;
1187 END IF;
1188
1189 END LOOP; -- end i loop
1190
1191 -------- performed exemption limit check
1192
1193 END IF; -- end if status = AA,CC,GG
1194
1195 END IF; -- end if l_LE_over_limit = Y
1196
1197 l_exemption_limit_used := l_total_saving - l_bal_val_bimonth ;
1198
1199 hr_utility.trace('2007 EMP_CONT ::: LE #### l_total_saving = '||l_total_saving );
1200 hr_utility.trace('2007 EMP_CONT ::: LE #### l_bal_val_bimonth = '||l_bal_val_bimonth );
1201 hr_utility.trace('2007 EMP_CONT ::: LE #### l_exemption_limit_used = '||l_exemption_limit_used );
1202
1203 hr_utility.trace('2007 EMP_CONT ::: -- calling display_table_calc function ');
1204 l_check := display_table_calc(g_tab_calc);
1205 hr_utility.trace('2007 EMP_CONT ::: -- returned from display_table_calc function ');
1206
1207 hr_utility.trace('2007 EMP_CONT ::: LE #### apply differential rate and check exemption limit , leaving ');
1208 ------------------------------ applied differential rate and check exemption limit at LE Level
1209
1210 -- ec_main_calculation function call for LE
1211 hr_utility.trace('2007 EMP_CONT ::: LE #### ec_main_calculation function call for LE , entering ' );
1212
1213 l_main_index := ec_main_calculation ( g_tab_calc
1214 ,PAY_NO_EMP_CONT_2007.g_tab_main
1215 ,p_tax_unit_id
1216 ,-9999
1217 ,l_exemption_limit_used
1218 ,l_le_status
1219 ,p_bus_group_id
1220 ,p_date_earned
1221 ,p_under_age_high_rate
1222 ,p_over_age_high_rate
1223 ,l_curr_zone ) ;
1224
1225
1226
1227 hr_utility.trace('2007 EMP_CONT ::: -- calling display_table_calc function ');
1228 l_check := display_table_calc(g_tab_calc);
1229 hr_utility.trace('2007 EMP_CONT ::: -- returned from display_table_calc function ');
1230
1231 hr_utility.trace('2007 EMP_CONT ::: LE #### ec_main_calculation function call for LE , leaving ' );
1232
1233 -- done all caclculation and entered values in the main table for next time usage
1234
1235 -- returning values at LE level
1236 p_run_base := PAY_NO_EMP_CONT_2007.g_tab_main(l_main_index).run_base;
1237 p_run_contribution := PAY_NO_EMP_CONT_2007.g_tab_main(l_main_index).run_contribution;
1238 p_curr_exemption_limit_used := PAY_NO_EMP_CONT_2007.g_tab_main(l_main_index).exemption_limit_used;
1239
1240 hr_utility.trace('2007 EMP_CONT ::: LE #### l_main_index = '|| l_main_index );
1241 hr_utility.trace('2007 EMP_CONT ::: LE #### p_run_base = '|| p_run_base );
1242 hr_utility.trace('2007 EMP_CONT ::: LE #### p_run_contribution = '|| p_run_contribution );
1243 hr_utility.trace('2007 EMP_CONT ::: LE #### p_curr_exemption_limit_used = '|| p_curr_exemption_limit_used );
1244
1245 hr_utility.trace('2007 EMP_CONT ::: LE #### Leaving procedure-----------------------------------');
1246
1247 RETURN 1; -- here return 1 and the other values thru OUT parameters
1248
1249
1250 END IF; -- end if l_le_exists
1251
1252
1253 END IF; -- end if l_lu_rep_sep = 'Y'
1254
1255 hr_utility.trace('2007 EMP_CONT ::: exiting main function');
1256 --hr_utility.trace_off();
1257
1261 END GET_EMPLOYER_DEDUCTION;
1258 -----------------------------------Exception -----------------------------------------------------------
1259 --EXCEPTION
1260
1262
1263 -----------------------------------------------------------------------------------------------------------------------------------
1264
1265 -- Function to get defined balance id
1266
1267 FUNCTION get_defined_balance_id
1268 (p_balance_name IN VARCHAR2
1269 ,p_dbi_suffix IN VARCHAR2 ) RETURN NUMBER IS
1270
1271 l_defined_balance_id NUMBER;
1272
1273 BEGIN
1274
1275 SELECT pdb.defined_balance_id
1276 INTO l_defined_balance_id
1277 FROM pay_defined_balances pdb
1278 ,pay_balance_types pbt
1279 ,pay_balance_dimensions pbd
1280 WHERE pbd.database_item_suffix = p_dbi_suffix
1281 AND pbd.legislation_code = 'NO'
1282 AND pbt.balance_name = p_balance_name
1283 AND pbt.legislation_code = 'NO'
1284 AND pdb.balance_type_id = pbt.balance_type_id
1285 AND pdb.balance_dimension_id = pbd.balance_dimension_id
1286 AND pdb.legislation_code = 'NO';
1287
1288 l_defined_balance_id := NVL(l_defined_balance_id,0);
1289
1290 RETURN l_defined_balance_id ;
1291 END get_defined_balance_id ;
1292
1293 -----------------------------------------------------------------------------------------------------------------------------------
1294
1295 -- Function to populate LU (g_lu_tab) and MU (g_mu_tab) tables
1296
1297 FUNCTION populate_tables
1298 (p_tax_unit_id IN NUMBER
1299 ,p_payroll_id IN NUMBER
1300 ,p_date_earned IN DATE
1301 ,g_lu_tab IN OUT NOCOPY PAY_NO_EMP_CONT_2007.g_lu_tabtype
1302 ,g_mu_tab IN OUT NOCOPY PAY_NO_EMP_CONT_2007.g_mu_tabtype ) RETURN NUMBER IS
1303
1304
1305 /* cursor to get the element_type_id of element 'Tax Deduction Base' */
1306
1307 /*
1308 CURSOR csr_element_type (p_date_earned DATE ) IS
1309 SELECT ELEMENT_TYPE_ID
1310 FROM pay_element_types_f pet
1311 WHERE pet.element_name = 'Tax Deduction Base'
1312 AND pet.LEGISLATION_CODE = 'NO'
1313 AND p_date_earned BETWEEN pet.EFFECTIVE_START_DATE AND pet.EFFECTIVE_END_DATE ;
1314 */
1315
1316 -- changing element from Tax Deduction Base to Tax
1317
1318 CURSOR csr_element_type (p_date_earned DATE ) IS
1319 SELECT ELEMENT_TYPE_ID
1320 FROM pay_element_types_f pet
1321 WHERE pet.element_name = 'Tax'
1322 AND pet.LEGISLATION_CODE = 'NO'
1323 AND p_date_earned BETWEEN pet.EFFECTIVE_START_DATE AND pet.EFFECTIVE_END_DATE ;
1324
1325
1326 start_index_mu NUMBER;
1327 end_index_mu NUMBER;
1328 l_mu_exists BOOLEAN;
1329
1330 k NUMBER;
1331 l_lu_status VARCHAR2(40) ;
1332 l_lu_rep_sep VARCHAR2(1) ;
1333 l_lu_tax_mun VARCHAR2(200) ;
1334
1335 start_index_lu NUMBER;
1336 end_index_lu NUMBER;
1337 l_lu_exists BOOLEAN;
1338
1339 l_tax_ele_type_id NUMBER;
1340
1341 BEGIN
1342
1343 OPEN csr_element_type (p_date_earned) ;
1344 FETCH csr_element_type INTO l_tax_ele_type_id ;
1345 CLOSE csr_element_type ;
1346
1347
1348
1349 ------------- testing the 3 cursor loops
1350
1351 -- loop to get all payroll_action_id in the bimonth period for the current payroll
1352 FOR csr1_rec IN PAY_NO_EMP_CONT_2007.csr_payroll_action_id (p_date_earned) LOOP
1353
1354 -- loop to get assignment_id and assignment_action_id for all payroll_action_id obtained above for the current legal employer
1355 FOR csr2_rec IN PAY_NO_EMP_CONT_2007.csr_assignment_id (p_tax_unit_id , csr1_rec.PAYROLL_ACTION_ID) LOOP
1356
1357 -- loop to get local unit and tax municipality for all assignment actions obtained above
1358 FOR csr3_rec IN PAY_NO_EMP_CONT_2007.csr_lu_mu (csr2_rec.ASSIGNMENT_ID , csr2_rec.ASSIGNMENT_ACTION_ID , p_date_earned , l_tax_ele_type_id ) LOOP
1359
1360 hr_utility.trace('2007 EMP_CONT ::: --------------------------------------------------------');
1361
1362 hr_utility.trace('2007 EMP_CONT ::: PAY_ACT_ID = '||csr1_rec.PAYROLL_ACTION_ID||' ASS_ID = '||csr2_rec.ASSIGNMENT_ID||' ASS_ACT_ID = '||csr2_rec.ASSIGNMENT_ACTION_ID
1363 ||' Local Unit = '||csr3_rec.local_unit_id||' Tax Mul = '||csr3_rec.tax_mun_id);
1364
1365 ----------------------------- check for MU -------------------------------------------
1366 start_index_mu := NVL (g_mu_tab.FIRST, 0) ;
1367 end_index_mu := NVL (g_mu_tab.LAST, 0) ;
1368 l_mu_exists := FALSE;
1369
1370 -- loop through existing records for local unit and tax municipality to check if the current combination exists
1371 WHILE (g_mu_tab.EXISTS(start_index_mu)) and (start_index_mu <= end_index_mu) LOOP
1372 IF (g_mu_tab(start_index_mu) = csr3_rec.tax_mun_id)
1373 THEN
1374 l_mu_exists := TRUE;
1375 EXIT;
1376 END IF;
1377 start_index_mu := start_index_mu + 1;
1378 END LOOP; -- end while loop
1379
1380 -- if the current combination doe not exists , add the combination to the pl/sql table
1381 IF NOT l_mu_exists
1382 THEN
1383 k := NVL (g_mu_tab.LAST, 0) + 1 ;
1384 g_mu_tab(k) := csr3_rec.tax_mun_id ;
1385 END IF;
1386 -------------------------------------------------------------------------------------------------
1387
1388 ----------------------------- check for unique LU -------------------------------------------
1389
1393 CLOSE PAY_NO_EMP_CONT_2007.get_lu_details;
1390 -- Get the Status and Report Separately for this particular Local Unit
1391 OPEN PAY_NO_EMP_CONT_2007.get_lu_details(csr3_rec.local_unit_id);
1392 FETCH PAY_NO_EMP_CONT_2007.get_lu_details INTO l_lu_status , l_lu_rep_sep , l_lu_tax_mun ;
1394
1395 IF trim(l_lu_rep_sep) = 'N'
1396 THEN
1397
1398 start_index_lu := NVL (g_lu_tab.FIRST, 0) ;
1399 end_index_lu := NVL (g_lu_tab.LAST, 0) ;
1400 l_lu_exists := FALSE;
1401
1402 -- loop through existing records for local unit to check if the current Local Unit exists
1403 WHILE (g_lu_tab.EXISTS(start_index_lu)) and (start_index_lu <= end_index_lu) LOOP
1404 IF (g_lu_tab(start_index_lu) = csr3_rec.local_unit_id)
1405 THEN
1406 l_lu_exists := TRUE;
1407 EXIT;
1408 END IF;
1409 start_index_lu := start_index_lu + 1;
1410 END LOOP; -- end while loop
1411
1412
1413 -- if the current Local Unit doe not exists , add the Local Unit to the pl/sql table
1414 IF NOT l_lu_exists
1415 THEN
1416 k := NVL (g_lu_tab.LAST, 0) + 1 ;
1417 g_lu_tab(k) := csr3_rec.local_unit_id ;
1418 END IF; -- end IF for inserting Local Unit in l_unique_lu_tab table
1419
1420 END IF; -- end IF for checking is report separatly is Y
1421 -------------------------------------------------------------------------------------------------
1422
1423
1424 END LOOP; -- end loop csr3_rec
1425
1426 END LOOP; -- end loop csr2_rec
1427
1428 END LOOP; -- end loop csr1_rec
1429
1430 ------------- end testing the 3 cursor loops
1431
1432
1433 ---- sub test 1
1434 start_index_mu := NVL (g_mu_tab.FIRST, 0) ;
1435 end_index_mu := NVL (g_mu_tab.LAST, 0) ;
1436
1437 WHILE (g_mu_tab.EXISTS(start_index_mu)) and (start_index_mu <= end_index_mu) LOOP
1438 hr_utility.trace('2007 EMP_CONT ::: *****************************************************************************');
1439 hr_utility.trace('2007 EMP_CONT ::: Tax Mul = '|| g_mu_tab(start_index_mu));
1440
1441 start_index_mu := start_index_mu + 1;
1442 END LOOP; -- end while loop
1443 hr_utility.trace('2007 EMP_CONT ::: *****************************************************************************');
1444
1445 -- end sub test 1
1446 -------------------------------------------------------------------------------------------------
1447
1448
1449
1450 ---- sub test 2
1451 start_index_lu := NVL (g_lu_tab.FIRST, 0) ;
1452 end_index_lu := NVL (g_lu_tab.LAST, 0) ;
1453
1454
1455 WHILE (g_lu_tab.EXISTS(start_index_lu)) and (start_index_lu <= end_index_lu) LOOP
1456 hr_utility.trace('2007 EMP_CONT ::: \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\');
1457 hr_utility.trace('2007 EMP_CONT ::: Local Unit = '|| g_lu_tab(start_index_lu));
1458
1459 start_index_lu := start_index_lu + 1;
1460 END LOOP; -- end while loop
1461 hr_utility.trace('2007 EMP_CONT ::: \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\');
1462
1463 -- end sub test 2
1464
1465 ------------------------------
1466 RETURN 1;
1467
1468 END populate_tables ;
1469
1470
1471 -----------------------------------------------------------------------------------------------------------------------------------
1472
1473 -- function to get the lookup meaning
1474
1475 FUNCTION get_lookup_meaning (p_lookup_type IN varchar2,p_lookup_code IN varchar2) RETURN VARCHAR2 IS
1476
1477 CURSOR csr_lookup IS
1478 select meaning
1479 from hr_lookups
1480 where lookup_type = p_lookup_type
1481 and lookup_code = p_lookup_code
1482 and enabled_flag = 'Y';
1483
1484 l_meaning hr_lookups.meaning%type;
1485
1486 BEGIN
1487
1488 OPEN csr_lookup;
1489 FETCH csr_lookup INTO l_Meaning;
1490 CLOSE csr_lookup;
1491
1492 RETURN l_meaning;
1493 END get_lookup_meaning;
1494
1495 -----------------------------------------------------------------------------------------------------------------------------------
1496
1497 -- Function to look up the corresponding cell number in he table g_tab_calc
1498
1499 FUNCTION lookup_cell
1500 (g_tab_calc IN PAY_NO_EMP_CONT_2007.g_tab_calc_tabtype
1501 ,l_zone IN VARCHAR2 ) RETURN NUMBER IS
1502
1503 l_cell NUMBER;
1504
1505 BEGIN
1506 l_cell := 0;
1507
1508 FOR i IN 1..7 LOOP
1509
1510 IF (g_tab_calc(i).zone = l_zone ) THEN l_cell := i; EXIT; END IF;
1511
1512 END LOOP; -- end i loop
1513
1514 RETURN l_cell ;
1515
1516 END lookup_cell ;
1517
1518 -----------------------------------------------------------------------------------------------------------------------------------
1519 -- NEW function for main calculation
1520
1521 FUNCTION ec_main_calculation
1522
1523 (g_tab_calc IN OUT NOCOPY PAY_NO_EMP_CONT_2007.g_tab_calc_tabtype
1524 ,g_tab_main IN OUT NOCOPY PAY_NO_EMP_CONT_2007.g_tab_main_tabtype
1525 ,p_tax_unit_id IN NUMBER
1526 ,p_local_unit_id IN NUMBER
1527 ,p_exemption_limit_used IN NUMBER
1528 ,p_org_status IN VARCHAR2
1529 ,p_bus_group_id IN NUMBER
1530 ,p_date_earned IN DATE
1534 ) RETURN NUMBER IS
1531 ,p_under_age_high_rate IN NUMBER
1532 ,p_over_age_high_rate IN NUMBER
1533 ,l_curr_zone IN VARCHAR2
1535
1536
1537 l_main_index NUMBER;
1538 start_main_index NUMBER;
1539 end_main_index NUMBER;
1540 l_curr_index NUMBER;
1541
1542 start_index_calc NUMBER;
1543 end_index_calc NUMBER;
1544
1545 l_rate NUMBER;
1546 i NUMBER;
1547 x NUMBER;
1548 l_run_base NUMBER;
1549 l_unallocated_cont NUMBER;
1550 l_extra_run_cont NUMBER;
1551
1552 BEGIN
1553
1554 -- get the next location for the main table record
1555 start_main_index := NVL (g_tab_main.LAST, 0) + 1 ;
1556 end_main_index := start_main_index + 6 ;
1557 x := 1;
1558
1559 hr_utility.trace('2007 EMP_CONT ::: Initializing Main Table --------');
1560
1561 -- initialize 7 rows of the main table g_tab_main
1562 ------------------------------------------------------------------------------------------------------
1563
1564 g_tab_main(start_main_index + 0).zone := '1' ;
1565 g_tab_main(start_main_index + 1).zone := '1a' ;
1566 g_tab_main(start_main_index + 2).zone := '2' ;
1567 g_tab_main(start_main_index + 3).zone := '3' ;
1568 g_tab_main(start_main_index + 4).zone := '4' ;
1569 g_tab_main(start_main_index + 5).zone := '4a' ;
1570 g_tab_main(start_main_index + 6).zone := '5' ;
1571
1572 FOR j IN start_main_index..end_main_index LOOP
1573
1574 -- populate the fixed values
1575 g_tab_main(j).legal_employer_id := p_tax_unit_id ;
1576 g_tab_main(j).local_unit_id := p_local_unit_id ;
1577 g_tab_main(j).exemption_limit_used := p_exemption_limit_used ;
1578
1579 -- initializing other values
1580 g_tab_main(j).run_base := 0 ;
1581 g_tab_main(j).run_contribution := 0 ;
1582
1583
1584 x := x+1;
1585
1586 -- finding the l_main_index
1587 IF (g_tab_main(j).zone = l_curr_zone) THEN l_main_index := j; END IF; -- end if finding the l_main_index
1588
1589
1590 END LOOP; -- end loop for initializing g_tab_main
1591
1592 hr_utility.trace('2007 EMP_CONT ::: Finished Initializing Main Table --------');
1593 ------------------------------------------------------------------------------------------------------
1594
1595 l_run_base := 0;
1596 l_unallocated_cont := 0;
1597
1598 start_index_calc := NVL (g_tab_calc.FIRST, 0) ;
1599 end_index_calc := NVL (g_tab_calc.LAST, 0) ;
1600
1601 hr_utility.trace('2007 EMP_CONT ::: Main Calc Entered--------' );
1602 hr_utility.trace('2007 EMP_CONT ::: Main Calc i -- p_org_status - zone -- under_limit -- l_rate = -- bimonth_base -- bimonth_cont -- bimonth_todate -- run_cont ' );
1603
1604 FOR i IN start_index_calc..end_index_calc LOOP
1605
1606 l_rate := PAY_NO_EMP_CONT_2007.get_ec_rate (g_tab_calc(i).zone
1607 ,g_tab_calc(i).under_limit
1608 ,p_org_status
1609 ,p_bus_group_id
1610 ,p_date_earned
1611 ,p_under_age_high_rate
1612 ,p_over_age_high_rate );
1613
1614 -- calculating values for g_tab_calc
1615 g_tab_calc(i).bimonth_contribution := ( g_tab_calc(i).bimonth_base * l_rate ) / 100 ;
1616 g_tab_calc(i).run_contribution := g_tab_calc(i).bimonth_contribution - g_tab_calc(i).bimonth_contribution_todate ;
1617
1618 -- Rounding Downwards the run contribution to the nearest NOK
1619 --g_tab_calc(i).run_contribution := trunc(g_tab_calc(i).run_contribution) ;
1620
1621 -- collecting the run_base
1622 l_run_base := l_run_base + g_tab_calc(i).run_base ;
1623
1624 -- collecting the run_contributions where run_base is zero (for re-allocating the run_contributions)
1625 IF (g_tab_calc(i).run_base = 0) and (g_tab_calc(i).run_contribution <> 0)
1626 THEN
1627 l_unallocated_cont := l_unallocated_cont + g_tab_calc(i).run_contribution ;
1628 END IF;
1629
1630 hr_utility.trace('2007 EMP_CONT ::: Main Calc '||i
1631 ||' '||p_org_status
1632 ||' '||g_tab_calc(i).zone
1633 ||' '||g_tab_calc(i).under_limit
1634 ||' '||l_rate
1635 ||' '||g_tab_calc(i).bimonth_base
1636 ||' '||g_tab_calc(i).bimonth_contribution
1637 ||' '||g_tab_calc(i).bimonth_contribution_todate
1638 ||' '||g_tab_calc(i).run_contribution );
1639
1640 END LOOP;
1641
1642 hr_utility.trace('2007 EMP_CONT ::: Main Calc --- exited from Main calc loop' );
1643 hr_utility.trace('2007 EMP_CONT ::: Main Calc l_run_base = '|| l_run_base );
1644 hr_utility.trace('2007 EMP_CONT ::: Main Calc l_unallocated_cont = '|| l_unallocated_cont );
1645
1646 -- re-allocating the run_contributions that can be allocated
1647 -- and also summing the values to get the final values
1648
1649 hr_utility.trace('2007 EMP_CONT ::: Main Calc --- entered loop for re-allocation and summing up values' );
1650
1651 FOR i IN start_index_calc..end_index_calc LOOP
1652
1653 -- to handle the error of division by zero
1654
1655 IF ( l_run_base <> 0 )
1656 THEN
1657
1658 -- re-allocate unallocated contributions to rows where there is some run base
1659 l_extra_run_cont := l_unallocated_cont * (g_tab_calc(i).run_base / l_run_base) ;
1660
1661 -- add the extra run_cont to existing run_cont
1662 g_tab_calc(i).run_contribution := g_tab_calc(i).run_contribution + l_extra_run_cont;
1663
1664 ELSE
1665 l_extra_run_cont := 0 ;
1666
1667 END IF;
1668
1669 hr_utility.trace('2007 EMP_CONT ::: Main Calc '||i
1670 ||' '||g_tab_calc(i).run_base
1671 ||' '||l_run_base
1675
1672 ||' '||l_unallocated_cont
1673 ||' '||l_extra_run_cont
1674 ||' '||g_tab_calc(i).run_contribution ) ;
1676 -- getting the value of l_curr_index of the g_tab_main where the present values should go
1677
1678 -- l_curr_index := main_lookup_cell(g_tab_main , start_main_index , g_tab_calc(i).under_62 , g_tab_calc(i).zone );
1679 l_curr_index := main_lookup_cell(g_tab_main , start_main_index , g_tab_calc(i).zone );
1680
1681 -- inserting values in g_tab_main
1682 g_tab_main(l_curr_index).run_base := g_tab_main(l_curr_index).run_base + g_tab_calc(i).run_base ;
1683 g_tab_main(l_curr_index).run_contribution := g_tab_main(l_curr_index).run_contribution + g_tab_calc(i).run_contribution ;
1684
1685 END LOOP;
1686
1687 hr_utility.trace('2007 EMP_CONT ::: Main Calc --- leaving loop for re-allocation and summing up values' );
1688
1689 hr_utility.trace('2007 EMP_CONT ::: Main Calc Leaving--------' );
1690
1691 RETURN l_main_index ;
1692
1693 END ec_main_calculation ;
1694
1695
1696 -----------------------------------------------------------------------------------------------------------------------------------
1697
1698 -- function to get the ec rate
1699
1700 FUNCTION get_ec_rate
1701
1702 (p_zone IN VARCHAR2
1703 ,p_under_limit IN VARCHAR2
1704 ,p_org_status IN VARCHAR2
1705 ,p_bus_group_id IN NUMBER
1706 ,p_date_earned IN DATE
1707 ,p_under_age_high_rate IN NUMBER
1708 ,p_over_age_high_rate IN NUMBER ) RETURN NUMBER IS
1709
1710 l_ec_rate NUMBER;
1711 l_table_name VARCHAR2(240);
1712 l_col_name VARCHAR2(240);
1713 l_zone_value VARCHAR2(240);
1714
1715 BEGIN
1716
1717 -- providing existing values
1718 l_table_name := 'NO_NIS_ZONE_RATES';
1719
1720 l_zone_value := to_char(p_zone);
1721
1722 l_ec_rate := 0 ;
1723
1724
1725 IF (p_org_status = 'EE') -------------------------------------
1726 THEN
1727 -- no contribution for status EE
1728 l_ec_rate := 0;
1729
1730 ELSIF ( (p_org_status = 'AA') OR (p_org_status = 'GG') ) -------------------------------------
1731 THEN
1732 -- Normal rates apply for all except zone '1a'
1733 -- For zone '1a', high rate applies after exemption limit is exhausted
1734
1735 IF ((l_zone_value = '1a') AND (p_under_limit = 'N'))
1736
1737 THEN l_ec_rate := p_under_age_high_rate ;
1738
1739 -- ELSE l_ec_rate := TO_NUMBER(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'Under Age Percentage', l_zone_value, p_date_earned ));
1740 -- ELSE l_ec_rate := fnd_number.canonical_to_number(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'Under Age Percentage', l_zone_value, p_date_earned ));
1741 -- BUG Fix : 5999230
1742 -- Renaming user column of user table NO_NIS_ZONE_RATES from 'Under Age Percentage' to 'NIS Rates'
1743
1744 ELSE l_ec_rate := fnd_number.canonical_to_number(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'NIS Rates', l_zone_value, p_date_earned ));
1745
1746 END IF;
1747
1748
1752 -- For zone '1a', the normal rate of zone '1' is used
1749 ELSIF (p_org_status = 'BB') -------------------------------------
1750 THEN
1751 -- Normal rates apply for all
1753
1754 IF (l_zone_value = '1a') THEN l_zone_value := '1' ; END IF;
1755
1756 -- l_ec_rate := TO_NUMBER(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'Under Age Percentage', l_zone_value, p_date_earned ));
1757 -- l_ec_rate := fnd_number.canonical_to_number(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'Under Age Percentage', l_zone_value, p_date_earned ));
1758 -- BUG Fix : 5999230
1759 -- Renaming user column of user table NO_NIS_ZONE_RATES from 'Under Age Percentage' to 'NIS Rates'
1760
1761 l_ec_rate := fnd_number.canonical_to_number(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'NIS Rates', l_zone_value, p_date_earned ));
1762
1763
1764 ELSIF (p_org_status = 'DD') -------------------------------------
1765 THEN
1766 -- Normal rates apply for all
1767 -- For zone '4a', the normal rate of zone '4' is used
1768
1769 IF (l_zone_value = '4a') THEN l_zone_value := '4' ; END IF;
1770
1771 -- l_ec_rate := TO_NUMBER(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'Under Age Percentage', l_zone_value, p_date_earned ));
1772 -- l_ec_rate := fnd_number.canonical_to_number(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'Under Age Percentage', l_zone_value, p_date_earned ));
1773 -- BUG Fix : 5999230
1774 -- Renaming user column of user table NO_NIS_ZONE_RATES from 'Under Age Percentage' to 'NIS Rates'
1775
1776 l_ec_rate := fnd_number.canonical_to_number(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'NIS Rates', l_zone_value, p_date_earned ));
1777
1778
1779 ELSIF (p_org_status = 'CC') -------------------------------------
1780 THEN
1781 -- Normal rates apply for all except zone '1a'
1782 -- For zone '1a', high rate applies after exemption limit is exhausted
1783
1784 IF (p_under_limit = 'N')
1785
1786 THEN l_ec_rate := p_under_age_high_rate ;
1787
1788 -- ELSE l_ec_rate := TO_NUMBER(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'Under Age Percentage', l_zone_value, p_date_earned ));
1789 -- ELSE l_ec_rate := fnd_number.canonical_to_number(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'Under Age Percentage', l_zone_value, p_date_earned ));
1790 -- BUG Fix : 5999230
1791 -- Renaming user column of user table NO_NIS_ZONE_RATES from 'Under Age Percentage' to 'NIS Rates'
1792
1793 ELSE l_ec_rate := fnd_number.canonical_to_number(hruserdt.get_table_value (p_bus_group_id, l_table_name, 'NIS Rates', l_zone_value, p_date_earned ));
1794
1795 END IF;
1796
1797 END IF; -- end status check -------------------------------------
1798
1799
1800 RETURN l_ec_rate ;
1801
1802 END get_ec_rate ;
1803
1804 -----------------------------------------------------------------------------------------------------------------------------------
1805
1806 -- function to display table values of g_tab_calc
1807
1808 FUNCTION display_table_calc
1809 (g_tab_calc IN PAY_NO_EMP_CONT_2007.g_tab_calc_tabtype ) RETURN NUMBER IS
1810
1811 start_index_calc NUMBER;
1812 end_index_calc NUMBER;
1813
1814 BEGIN
1815
1816 start_index_calc := NVL (g_tab_calc.FIRST, 0) ;
1817 end_index_calc := NVL (g_tab_calc.LAST, 0) ;
1818
1819 hr_utility.trace('2007 EMP_CONT ::: ---------- Displaying g_tab_calc table data ----------------------');
1820 hr_utility.trace('2007 EMP_CONT ::: zone -- under_limit -- status -- bimonth_base -- run_base -- bimonth_cont -- bimonth_cont_todate -- run_cont ');
1821
1822 FOR i IN start_index_calc..end_index_calc LOOP
1823
1824 hr_utility.trace('2007 EMP_CONT ::: '
1825 ||' '||g_tab_calc(i).zone
1826 ||' '||g_tab_calc(i).under_limit
1827 ||' '||g_tab_calc(i).status
1828 ||' '||g_tab_calc(i).bimonth_base
1829 ||' '||g_tab_calc(i).run_base
1830 ||' '||g_tab_calc(i).bimonth_contribution
1831 ||' '||g_tab_calc(i).bimonth_contribution_todate
1832 ||' '||g_tab_calc(i).run_contribution );
1833
1834 END LOOP;
1835 hr_utility.trace('2007 EMP_CONT ::: ---------- Displayed g_tab_calc table data , exiting function ----------------------');
1836
1837 RETURN 1 ;
1838
1839 END display_table_calc ;
1840
1841 -----------------------------------------------------------------------------------------------------------------------------------
1842 -- changing the function to fetch the avg ni base rate for months and end of year averaging
1843 -- function to get the average NI Base Rate Value
1844
1845 FUNCTION avg_ni_base_rate (p_date_earned IN DATE , p_bus_grp_id NUMBER ) RETURN NUMBER IS
1846
1847 l_base_rate_value NUMBER;
1848 l_cum_base_rate_value NUMBER;
1849 l_avg_base_rate_value NUMBER;
1850
1851 l_no_of_mths NUMBER;
1852 l_start_of_year DATE;
1853 l_end_of_year DATE;
1854
1855 l_eff_start_date DATE;
1856 l_eff_end_date DATE;
1857
1858 -- defining cursor to get all NI Base Rate values
1859 -- NEW
1860
1861 /*
1862
1863 cursor csr_get_ni_base_rates (p_date_earned date ,p_bus_grp_id NUMBER ) IS
1864 select pucf.EFFECTIVE_START_DATE
1865 ,pucf.EFFECTIVE_END_DATE
1866 ,pucf.VALUE
1867 from pay_user_tables put
1868 ,pay_user_rows_f pur
1869 ,pay_user_columns puc
1870 ,pay_user_column_instances_f pucf
1871 where put.USER_TABLE_NAME = 'NO_GLOBAL_CONSTANTS'
1872 and pur.ROW_LOW_RANGE_OR_NAME = 'NATIONAL_INSURANCE_BASE_RATE'
1873 and puc.USER_COLUMN_NAME = 'Value'
1874 and put.legislation_code = 'NO'
1875 and pur.legislation_code = 'NO'
1876 and puc.legislation_code = 'NO'
1880 and pucf.user_row_id = pur.user_row_id
1877 and ( pucf.business_group_id = p_bus_grp_id OR pucf.business_group_id is NULL )
1878 and put.user_table_id = pur.user_table_id
1879 and put.user_table_id = puc.user_table_id
1881 and pucf.user_column_id = puc.user_column_id
1882 and p_date_earned between pur.effective_start_date and pur.effective_end_date
1883 and pucf.effective_start_date < (trunc(add_months(p_date_earned,12),'Y')-1)
1884 and pucf.effective_end_date > trunc(p_date_earned , 'Y' ) ;
1885
1886 */
1887
1888 -- Bug Fix 5566622 : Value of G (National Insurance Base Rate) to be taken
1889 -- from Global (NO_NATIONAL_INSURANCE_BASE_RATE) and not user table (NATIONAL_INSURANCE_BASE_RATE).
1890 -- modifying cursor csr_get_ni_base_rates
1891
1892 /*
1893 cursor csr_get_ni_base_rates (p_date_earned date ) IS
1894 select EFFECTIVE_START_DATE
1895 , EFFECTIVE_END_DATE
1896 , to_number(global_value) VALUE
1897 from ff_globals_f
1898 where legislation_code = 'NO'
1899 and global_name = 'NO_NATIONAL_INSURANCE_BASE_RATE'
1900 and BUSINESS_GROUP_ID IS NULL
1901 and effective_start_date <= (trunc(add_months(p_date_earned,12),'Y')-1)
1902 and effective_end_date >= trunc(p_date_earned , 'Y' ) ;
1903 */
1904
1905 cursor csr_get_ni_base_rates (p_date_earned date ) IS
1906 select EFFECTIVE_START_DATE
1907 , EFFECTIVE_END_DATE
1908 , fnd_number.canonical_to_number(global_value) VALUE
1909 from ff_globals_f
1910 where legislation_code = 'NO'
1911 and global_name = 'NO_NATIONAL_INSURANCE_BASE_RATE'
1912 and BUSINESS_GROUP_ID IS NULL
1913 and effective_start_date <= (trunc(add_months(p_date_earned,12),'Y')-1)
1914 and effective_end_date >= trunc(p_date_earned , 'Y' ) ;
1915
1916
1917 BEGIN
1918
1919 l_end_of_year := (trunc(add_months(p_date_earned,12),'Y')-1) ;
1920 l_start_of_year := trunc(p_date_earned, 'Y');
1921
1922 l_cum_base_rate_value := 0 ;
1923
1924 hr_utility.trace('2007 EMP_CONT ::: p_date_earned = '||p_date_earned);
1925 hr_utility.trace('2007 EMP_CONT ::: l_start_of_year = '||l_start_of_year);
1926 hr_utility.trace('2007 EMP_CONT ::: l_end_of_year = '||l_end_of_year);
1927 hr_utility.trace('2007 EMP_CONT ::: ========================================= ');
1928
1929 -- Bug Fix 5566622 : Value of G (National Insurance Base Rate) to be taken from Global and not user table.
1930 -- modifying call for cursor csr_get_ni_base_rates
1931
1932 -- FOR csr_rec IN csr_get_ni_base_rates (p_date_earned , p_bus_grp_id) LOOP
1933 FOR csr_rec IN csr_get_ni_base_rates (p_date_earned ) LOOP
1934
1935 l_base_rate_value := csr_rec.VALUE ;
1936 l_eff_start_date := csr_rec.EFFECTIVE_START_DATE ;
1937 l_eff_end_date := csr_rec.EFFECTIVE_END_DATE ;
1938
1939 -- check if effective start date is before the starting of the year
1940
1941 IF (l_eff_start_date < l_start_of_year)
1942 THEN
1943 hr_utility.trace('2007 EMP_CONT ::: OLD l_eff_start_date = '||l_eff_start_date);
1944 l_eff_start_date := l_start_of_year;
1945 hr_utility.trace('2007 EMP_CONT ::: NEW l_eff_start_date = '||l_eff_start_date);
1946 END IF;
1947
1948 -- check if effective end date is after the end of the period
1949
1950 IF (l_eff_end_date > l_end_of_year)
1951 THEN
1952 hr_utility.trace('2007 EMP_CONT ::: OLD l_eff_end_date = '||l_eff_end_date);
1953 l_eff_end_date := l_end_of_year;
1954 hr_utility.trace('2007 EMP_CONT ::: NEW l_eff_end_date = '||l_eff_end_date);
1955 END IF;
1956
1957 -- calculating the number of months the value is valid for
1958
1959 l_no_of_mths := months_between(l_eff_end_date , (l_eff_start_date -1) );
1960 l_cum_base_rate_value := l_cum_base_rate_value + (l_base_rate_value * l_no_of_mths) ;
1961
1962
1963 hr_utility.trace('2007 EMP_CONT ::: -------------- ');
1964 hr_utility.trace('2007 EMP_CONT ::: l_base_rate_value = '||l_base_rate_value);
1965 hr_utility.trace('2007 EMP_CONT ::: l_eff_start_date = '||l_eff_start_date);
1966 hr_utility.trace('2007 EMP_CONT ::: l_eff_end_date = '||l_eff_end_date);
1967 hr_utility.trace('2007 EMP_CONT ::: l_no_of_mths = '||l_no_of_mths);
1968 hr_utility.trace('2007 EMP_CONT ::: l_base_rate_value * l_no_of_mths = '||l_base_rate_value * l_no_of_mths);
1969 hr_utility.trace('2007 EMP_CONT ::: l_cum_base_rate_value = '||l_cum_base_rate_value);
1970 hr_utility.trace('2007 EMP_CONT ::: -------------- ');
1971
1972 END LOOP; -- end cursor loop
1973
1974 l_avg_base_rate_value := l_cum_base_rate_value / 12 ;
1975
1976 hr_utility.trace('2007 EMP_CONT ::: =================================================== ');
1977 hr_utility.trace('2007 EMP_CONT ::: l_cum_base_rate_value = '||l_cum_base_rate_value);
1978 hr_utility.trace('2007 EMP_CONT ::: l_avg_base_rate_value = '||l_avg_base_rate_value);
1979 hr_utility.trace('2007 EMP_CONT ::: ---------------- OVER ---------------- ');
1980
1981
1982 RETURN l_avg_base_rate_value ;
1983
1984 EXCEPTION
1985 WHEN OTHERS THEN
1986 l_avg_base_rate_value := 0;
1987 RETURN l_avg_base_rate_value ;
1988
1989
1990 END avg_ni_base_rate ;
1991
1992
1993 ---------------------------------------------------------------------------------------------------------------
1994
1995 -- Function to look up the corresponding cell number in he table g_tab_main
1996
1997 FUNCTION main_lookup_cell
1998 (g_tab_main IN PAY_NO_EMP_CONT_2007.g_tab_main_tabtype
1999 ,start_main_index IN NUMBER
2000 ,l_zone IN VARCHAR2 ) RETURN NUMBER IS
2001
2002 l_cell NUMBER;
2003
2004 BEGIN
2005 l_cell := 0;
2006
2007 FOR i IN start_main_index..(start_main_index + 6) LOOP
2008
2009 IF (g_tab_main(i).zone = l_zone ) THEN l_cell := i; EXIT; END IF;
2010
2011 END LOOP; -- end i loop
2012
2013 RETURN l_cell ;
2014
2018
2015 END main_lookup_cell ;
2016
2017 ---------------------------------------------------------------------------------------------------------------
2019 -- Function to check if any exemption limit error exists
2020
2021 FUNCTION chk_exemption_limit_err
2022 (p_date_earned IN DATE
2023 ,p_bus_grp_id IN NUMBER
2024 ,p_payroll_action_id IN NUMBER )
2025
2026 RETURN NUMBER IS
2027
2028 l_status VARCHAR2(240);
2029 l_rep_sep VARCHAR2(240);
2030 l_exempt_limit NUMBER;
2031 l_org_name VARCHAR2(240);
2032 l_lu_tax_mun VARCHAR2(200) ;
2033
2034 -- 2007 Legislative changes for 'Economic Aid' to Employer
2035 l_economic_aid NUMBER;
2036
2037 BEGIN
2038
2039 hr_utility.trace('2007 EXEM_LIM ::: Entered the procedure for exemption limit ');
2040 --p_err_text := '';
2041
2042 IF ( g_error_flag = TRUE )
2043
2044 THEN
2045 hr_utility.trace('2007 EXEM_LIM ::: g_error_flag = TRUE , returning 1' );
2046 RETURN 1 ; -- error has occured already , return 1 to formula funnction and from there just RETURN
2047
2048 ELSIF ( g_error_check = TRUE )
2049
2050 THEN
2051 hr_utility.trace('2007 EXEM_LIM ::: g_error_flag = FALE but g_error_check = TRUE , returning 0' );
2052 RETURN 0 ; -- checking has been done but no error is there , return 0 to formula function , continue processing
2053
2054 ELSE -- the error condition has not been checked yet
2055
2056 hr_utility.trace('2007 EXEM_LIM ::: both globals are false , nocheck has been performed ');
2057 -- since we are checking the condition now , set the check global to TRUE
2058 g_error_check := TRUE ;
2059 hr_utility.trace('2007 EXEM_LIM ::: made g_error_check := TRUE ');
2060
2061 -- loop thru the cursor csr_get_lu_le and get all LE and LU
2062
2063 FOR csr_rec in PAY_NO_EMP_CONT_2007.csr_get_lu_le (p_payroll_action_id ,p_date_earned ) LOOP
2064
2065 hr_utility.trace('2007 EXEM_LIM ::: ------------------Inside For loop------------- ' );
2066 hr_utility.trace('2007 EXEM_LIM ::: Local unit id = '||csr_rec.loc_unit ||' Legal Employer ID = '||csr_rec.leg_emp );
2067 -- get the status and report separately for the LU
2068
2069 OPEN PAY_NO_EMP_CONT_2007.get_lu_details (csr_rec.loc_unit);
2070 FETCH PAY_NO_EMP_CONT_2007.get_lu_details INTO l_status , l_rep_sep , l_lu_tax_mun ;
2071 CLOSE PAY_NO_EMP_CONT_2007.get_lu_details ;
2072
2073 hr_utility.trace('2007 EXEM_LIM ::: l_status '|| l_status );
2074 hr_utility.trace('2007 EXEM_LIM ::: l_rep_sep '||l_rep_sep );
2075
2079 THEN
2076 -- if report separately = yes and status in AA,CC,GG
2077 IF ( trim(l_rep_sep) = 'Y' ) AND ( l_status IN ('AA','CC','GG') )
2078
2083
2080 hr_utility.trace('2007 EXEM_LIM ::: local unit is rep sep and status in AA,CC,GG....fetching exemption limit ');
2081 -- then open exemption limit cursor
2082 OPEN PAY_NO_EMP_CONT_2007.csr_get_exemption_limit (csr_rec.loc_unit, p_date_earned) ;
2084 -- 2007 Legislative changes for 'Economic Aid' to Employer
2085 -- FETCH PAY_NO_EMP_CONT_2007.csr_get_exemption_limit INTO l_exempt_limit ;
2086 FETCH PAY_NO_EMP_CONT_2007.csr_get_exemption_limit INTO l_exempt_limit , l_economic_aid ;
2087
2088 -- if any error is there
2089 -- checking if no data returned for Exemption Limit
2090 IF (PAY_NO_EMP_CONT_2007.csr_get_exemption_limit%NOTFOUND OR l_exempt_limit IS NULL)
2091 THEN
2092 hr_utility.trace('2007 EXEM_LIM ::: Problem in getting Exemption limit ' );
2093 -- Exemption Limit for this Local Unit Not Specified for this Period or not specified at all
2094
2095 -- get global error flag to TRUE
2096 g_error_flag := TRUE ;
2097 hr_utility.trace('2007 EXEM_LIM ::: set g_error_flag = TRUE ');
2098
2099 -- get the name of the local unit
2100 OPEN PAY_NO_EMP_CONT_2007.csr_org_name(csr_rec.loc_unit ,p_bus_grp_id );
2101 FETCH PAY_NO_EMP_CONT_2007.csr_org_name INTO l_org_name ;
2102 CLOSE PAY_NO_EMP_CONT_2007.csr_org_name;
2103
2104 hr_utility.trace('2007 EXEM_LIM ::: name of local unit = '|| l_org_name );
2105
2106 -- Set the message and message token
2107 hr_utility.set_message (801, 'PAY_376856_NO_LU_NO_EXEM_LIMIT');
2108 hr_utility.set_message_token (801, 'ORG_NAME', l_org_name);
2109
2110 hr_utility.trace('2007 EXEM_LIM ::: set the message = '||hr_utility.get_message );
2111
2112 -- Put the meassage in the log file
2113 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
2114
2115 hr_utility.trace('2007 EXEM_LIM ::: put the msg in the log file ');
2116
2117 -- p_err_text := p_err_text || hr_utility.get_message ;
2118
2119 END IF; -- end exemption limit got check
2120
2121 CLOSE PAY_NO_EMP_CONT_2007.csr_get_exemption_limit;
2122
2123 ELSIF ( trim(l_rep_sep) = 'N' ) -- else if LU is not rep sep , check at the legal employer level
2124
2125 THEN
2126 hr_utility.trace('2007 EXEM_LIM ::: local unit is NOT rep sep....fetching LE Status ');
2127 -- get the status of the LE
2128 OPEN PAY_NO_EMP_CONT_2007.get_le_status (csr_rec.leg_emp) ;
2129 FETCH PAY_NO_EMP_CONT_2007.get_le_status INTO l_status ;
2130 CLOSE PAY_NO_EMP_CONT_2007.get_le_status ;
2131
2132 hr_utility.trace('2007 EXEM_LIM ::: LE l_status '|| l_status );
2133
2134 -- if the status of LE in AA,CC,GG
2135 IF ( l_status IN ('AA','CC','GG') )
2136 THEN
2137 hr_utility.trace('2007 EXEM_LIM ::: Legal Emp status in AA,CC,GG ....fetching exemption limit ');
2138 -- then open exemption limit cursor
2139 OPEN PAY_NO_EMP_CONT_2007.csr_get_exemption_limit (csr_rec.leg_emp, p_date_earned) ;
2140
2141 -- 2007 Legislative changes for 'Economic Aid' to Employer
2142 -- FETCH PAY_NO_EMP_CONT_2007.csr_get_exemption_limit INTO l_exempt_limit ;
2143 FETCH PAY_NO_EMP_CONT_2007.csr_get_exemption_limit INTO l_exempt_limit , l_economic_aid ;
2144
2145 -- if any error is there
2146 -- checking if no data returned for Exemption Limit
2147 IF (PAY_NO_EMP_CONT_2007.csr_get_exemption_limit%NOTFOUND OR l_exempt_limit IS NULL)
2148 THEN
2149 hr_utility.trace('2007 EXEM_LIM ::: Problem in getting Exemption limit ' );
2150 -- Exemption Limit for this Legal Emplyer Not Specified for this Period or not specified at all
2151
2152 -- get global error flag to TRUE
2153 g_error_flag := TRUE ;
2154 hr_utility.trace('2007 EXEM_LIM ::: set g_error_flag = TRUE ');
2155
2156 -- get the name of the Legal Employer
2157 OPEN PAY_NO_EMP_CONT_2007.csr_org_name(csr_rec.leg_emp ,p_bus_grp_id );
2158 FETCH PAY_NO_EMP_CONT_2007.csr_org_name INTO l_org_name ;
2159 CLOSE PAY_NO_EMP_CONT_2007.csr_org_name;
2160
2161 hr_utility.trace('2007 EXEM_LIM ::: name of legal employer = '|| l_org_name );
2162
2163 -- Set the message and message token
2164 hr_utility.set_message (801, 'PAY_376857_NO_LE_NO_EXEM_LIMIT');
2165 hr_utility.set_message_token (801, 'ORG_NAME', l_org_name);
2166 hr_utility.trace('2007 EXEM_LIM ::: set the message = '||hr_utility.get_message );
2167
2168 -- Put the meassage in the log file
2169 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
2170 hr_utility.trace('2007 EXEM_LIM ::: put the msg in the log file ');
2171
2172 -- p_err_text := p_err_text || hr_utility.get_message ;
2173
2174 END IF; -- end exemption limit got check
2175
2176 CLOSE PAY_NO_EMP_CONT_2007.csr_get_exemption_limit;
2177
2178 END IF; -- end if the status of LE in AA,CC,GG
2179
2180 END IF ; -- end if report separately = yes and status in AA,CC,GG
2181
2182 END LOOP; -- end loop thru the cursor csr_get_lu_le and get all LE and LU
2183
2184 hr_utility.trace('2007 EXEM_LIM ::: **************** end of loop ************* ');
2185
2186 hr_utility.trace('2007 EXEM_LIM ::: final error check ');
2187
2188 -- finally check if any error did occur
2189 IF ( g_error_flag = TRUE )
2190 THEN
2191 hr_utility.trace('2007 EXEM_LIM ::: g_error_flag = TRUE , returning 1 ');
2192 RETURN 1 ; -- error did occur
2193 ELSE
2194 hr_utility.trace('2007 EXEM_LIM ::: g_error_flag = FALSE , returning 1 ');
2195 RETURN 0 ; -- error didnot occur
2196
2197 END IF; -- end final check for error
2198
2199 END IF ; -- end of error check
2200
2201
2202 -- end of function
2203 END chk_exemption_limit_err ;
2204
2205 --------------------------------------------------------------------------------------------------------------------------------
2206
2207 -- function to get the employer contribution rate
2208 FUNCTION get_emp_contr_rate
2209
2210 (p_bus_group_id IN NUMBER,
2211 p_tax_unit_id IN NUMBER,
2212 p_local_unit_id IN NUMBER,
2213 p_jurisdiction_code IN VARCHAR2,
2214 p_date_earned IN DATE,
2215 p_asg_act_id IN NUMBER,
2216 p_under_age_high_rate IN NUMBER,
2217 p_over_age_high_rate IN NUMBER,
2218 p_under_limit IN VARCHAR2) RETURN NUMBER IS
2219
2220 l_ec_rate NUMBER;
2221 l_org_status VARCHAR2(40);
2222 l_le_status VARCHAR2(40);
2223 l_lu_status VARCHAR2(40);
2224 l_lu_rep_sep VARCHAR2(1);
2225 l_lu_tax_mun VARCHAR2(200) ;
2226 -- l_curr_zone NUMBER;
2227 l_curr_zone VARCHAR2(10); -- current zone can return Character Values
2228
2229
2230 BEGIN
2231
2232 OPEN PAY_NO_EMP_CONT_2007.get_lu_details(p_local_unit_id);
2233 FETCH PAY_NO_EMP_CONT_2007.get_lu_details
2234 INTO l_lu_status , l_lu_rep_sep , l_lu_tax_mun ;
2235 CLOSE PAY_NO_EMP_CONT_2007.get_lu_details;
2236
2237
2238 IF(l_lu_rep_sep = 'Y') THEN
2239 l_org_status := l_lu_status; -- if report sepeartely is yes then assigning the lu status
2240
2241
2242 ELSE
2243
2244
2245 OPEN PAY_NO_EMP_CONT_2007.get_le_status(p_tax_unit_id);
2246 FETCH PAY_NO_EMP_CONT_2007.get_le_status
2247 INTO l_le_status;
2248 CLOSE PAY_NO_EMP_CONT_2007.get_le_status;
2249
2250 l_org_status := l_le_status; -- assigning the le status
2251 END IF;
2252
2253 -- l_curr_zone := to_number(SUBSTR(PAY_NO_EMP_CONT_2007.get_lookup_meaning('NO_TAX_MUNICIPALITY', p_jurisdiction_code), 1, 1));
2254 l_curr_zone := hruserdt.get_table_value (p_bus_group_id, 'NO_TAX_MUNICIPALITY' , 'ZONE', p_jurisdiction_code, p_date_earned ) ;
2255
2256
2257
2258 l_ec_rate := PAY_NO_EMP_CONT_2007.get_ec_rate( l_curr_zone, p_under_limit, l_org_status, p_bus_group_id, p_date_earned, p_under_age_high_rate, p_over_age_high_rate);
2259
2260
2261
2262 RETURN l_ec_rate;
2263 END get_emp_contr_rate;
2264
2265 --------------------------------------------------------------------------------------------------------------------------
2266
2267
2268 ---------------------------- end of package ---------------------------------------------------------------------------------------
2269
2270 END PAY_NO_EMP_CONT_2007;