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