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