[Home] [Help]
PACKAGE BODY: APPS.PAY_CN_DEDUCTIONS
Source
1 PACKAGE BODY PAY_CN_DEDUCTIONS AS
2 /* $Header: pycndedn.pkb 120.28.12020000.4 2013/04/08 17:22:18 dduvvuri ship $ */
3
4 g_package_name CONSTANT VARCHAR2(18):= 'pay_cn_deductions.';
5 g_procedure_name VARCHAR2(100);
6 g_debug BOOLEAN;
7
8 g_start_date CONSTANT DATE := TRUNC(TO_DATE('01-01-0001','DD-MM-YYYY'));
9 g_end_date CONSTANT DATE := TRUNC(TO_DATE('31-12-4712','DD-MM-YYYY'));
10
11 --------------------------------------------------------------------------
12 -- --
13 -- Name : GET_PHF_HIGH_LIMIT_EXEMPT --
14 -- Type : FUNCTION --
15 -- Access : Public --
16 -- Description : Function to return the PHF High Limit Exemption flag--
17 -- from the GRE/Legal Entity Level --
18 -- Parameters : --
19 -- IN : p_employer_id VARCHAR2 --
20 -- --
21 -- OUT : --
22 -- --
23 -- RETURN : VARCHAR2 --
24 -- --
25 -- Change History : --
26 --------------------------------------------------------------------------
27 -- Rev# Date Userid Description --
28 --------------------------------------------------------------------------
29 -- 1.0 10-Oct-06 abhjain Created this function --
30 -- 1.1 19-Mar-08 dduvvuri 6828199 - this function will not be used further
31 -- as the context 'PER_PHF_STAT_INFO_CN' is removed now
32 --------------------------------------------------------------------------
33 FUNCTION get_phf_high_limit_exempt (p_employer_id IN VARCHAR2
34 ,p_contribution_area IN VARCHAR2
35 )
36 RETURN VARCHAR2
37
38 IS
39 g_procedure_name VARCHAR2(100);
40 --
41 -- PHF High Limit Exemption check cursor
42 --
43 CURSOR c_phf_high_limit_exempt
44 IS
45 SELECT org_information2 -- Y/N
46 FROM hr_organization_information hoi
47 WHERE hoi.organization_id = p_employer_id
48 AND hoi.org_information_context = 'PER_PHF_STAT_INFO_CN'
49 AND hoi.org_information1 = p_contribution_area;
50
51 l_exempt_flag VARCHAR2(10);
52
53 BEGIN
54
55 g_procedure_name := g_package_name||'get_phf_high_limit_exempt';
56 hr_cn_api.set_location(g_debug,' Entering: '||g_procedure_name, 10);
57
58 -- Check if user has eneter values for Employee contribution rates
59 OPEN c_phf_high_limit_exempt;
60 FETCH c_phf_high_limit_exempt INTO l_exempt_flag;
61 CLOSE c_phf_high_limit_exempt;
62
63 hr_cn_api.set_location(g_debug,'l_exempt_flag: '|| l_exempt_flag, 15);
64
65 IF g_debug THEN
66 hr_utility.trace(' =======================================================');
67 hr_utility.trace(' . Exempt Flag : '||l_exempt_flag);
68 hr_utility.trace(' =======================================================');
69 END IF;
70
71 RETURN NVL(l_exempt_flag, 'N');
72
73 END get_phf_high_limit_exempt;
74
75
76 --------------------------------------------------------------------------
77 -- --
78 -- Name : GET_SPECIAL_TAX_METHOD --
79 -- Type : FUNCTION --
80 -- Access : Public --
81 -- Description : Function to return the tax method for Special --
82 -- payment types based on the tax area for China --
83 -- --
84 -- Parameters : --
85 -- IN : p_assignment_id NUMBER --
86 -- p_date_earned DATE --
87 -- p_tax_area VARCHAR2 --
88 -- p_special_payment_type VARCHAR2 --
89 -- OUT : N/A --
90 -- --
91 -- Change History : --
92 --------------------------------------------------------------------------
93 -- Rev# Date Userid Description --
94 --------------------------------------------------------------------------
95 -- 1.0 18-FEB-03 vinaraya Created this function --
96 -- 1.1 19-MAR-03 vinaraya Changed the indentation --
97 -- 1.2 20-MAR-03 vinaraya Changed the tables PER_ALL_ASSIGNMENTS_F --
98 -- to PER_ALL_ASSIGNMENTS and HR_SOFT_CODIN --
99 -- G_KEYFLEX_KFV to HR_SOFT_CODING_KEYFLEX --
100 -- in csr_tax for performance reasons. --
101 -- 1.3 20-MAR-03 vinaraya Changed the p_special_payment_type_id --
102 -- to l_special_payment_type_id in the --
103 -- csr_tax cursor definition. --
104 -- 1.4 06-JUN-03 saikrish Added parameter p_message --
105 -- 1.5 19-Jun-03 statkar Removed parameter p_message --
106 --------------------------------------------------------------------------
107 FUNCTION get_special_tax_method ( p_assignment_id IN NUMBER
108 , p_date_earned IN DATE
109 , p_tax_area IN VARCHAR2
110 , p_special_payment_type IN VARCHAR2
111 )
112 RETURN VARCHAR2
113 IS
114 l_tax_method hr_organization_information.org_information3%type;
115 l_special_payment_type_id pay_element_classifications.classification_id%type;
116
117 CURSOR csr_sp_pay_id ( p_special_payment_type IN VARCHAR2 ) IS
118 SELECT pri.classification_id
119 FROM pay_element_classifications pri,
120 pay_element_classifications sec
121 WHERE sec.legislation_code = 'CN'
122 AND sec.classification_name = 'Special Payments'
123 AND pri.parent_classification_id = sec.classification_id
124 AND pri.classification_name = p_special_payment_type;
125
126 CURSOR csr_tax( p_assignment_id NUMBER
127 , p_date_earned DATE
128 , p_tax_area VARCHAR2
129 , p_special_payment_type_id NUMBER
130 ) IS
131 SELECT hoi.org_information3
132 FROM hr_organization_information hoi
133 ,per_all_assignments paf
134 ,hr_soft_coding_keyflex hsc
135 ,hr_all_organization_units hou
136 WHERE paf.assignment_id = p_assignment_id
137 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
138 AND hsc.segment1 = hou.organization_id
139 AND hou.business_group_id = hoi.organization_id
140 AND hoi.org_information_context = 'PER_SPECIAL_TAX_METHODS_CN'
141 AND hoi.org_information1 = p_tax_area
142 AND hoi.org_information2 = p_special_payment_type_id
143 AND p_date_earned BETWEEN to_date(substr(hoi.org_information4,1,10),'YYYY/MM/DD')
144 AND to_date(NVL(substr(hoi.org_information5,1,10),'4712/12/31'),'YYYY/MM/DD');
145
146 BEGIN
147 OPEN csr_sp_pay_id(p_special_payment_type);
148 FETCH csr_sp_pay_id
149 INTO l_special_payment_type_id;
150 IF csr_sp_pay_id %NOTFOUND THEN
151 l_tax_method:='UNKNOWN';
152 CLOSE csr_sp_pay_id;
153 RETURN l_tax_method;
154 END IF;
155 CLOSE csr_sp_pay_id;
156
157 OPEN csr_tax( p_assignment_id
158 , p_date_earned
159 , p_tax_area
160 , l_special_payment_type_id
161 );
162 FETCH csr_tax
163 INTO l_tax_method;
164 IF csr_tax%NOTFOUND THEN
165 l_tax_method:='UNKNOWN';
166 END IF;
167 CLOSE csr_tax;
168 RETURN l_tax_method;
169
170 EXCEPTION
171 WHEN OTHERS THEN
172 IF csr_tax%ISOPEN THEN
173 CLOSE csr_tax;
174 END IF;
175 IF csr_sp_pay_id%ISOPEN THEN
176 CLOSE csr_sp_pay_id;
177 END IF;
178 l_tax_method:='UNKNOWN';
179 RETURN l_tax_method;
180
181
182 END get_special_tax_method;
183
184 --------------------------------------------------------------------------
185 -- --
186 -- Name : GET_ACCRUAL_PLAN --
187 -- Type : FUNCTION --
188 -- Access : Private --
189 -- Description : Function to return the accrual plan id for the --
190 -- specified accrual category. --
191 -- --
192 -- Parameters : --
193 -- IN : p_assignment_id NUMBER --
194 -- p_effective_date DATE --
195 -- p_plan_category VARCHAR2 --
196 -- OUT : p_message VARCHAR2 --
197 -- plan_id NUMBER --
198 -- --
199 -- Change History : --
200 --------------------------------------------------------------------------
201 -- Rev# Date Userid Description --
202 --------------------------------------------------------------------------
203 -- 1.0 01-APR-03 statkar Created this function --
204 -- 1.1 06-JUN-03 saikrish Added p_message --
205 --------------------------------------------------------------------------
206 FUNCTION get_accrual_plan ( p_assignment_id IN NUMBER
207 ,p_effective_date IN DATE
208 ,p_plan_category IN VARCHAR2
209 ,p_message OUT NOCOPY VARCHAR2
210 )
211 RETURN NUMBER
212 IS
213
214 l_accrual_plan_id NUMBER := NULL;
215 l_dummy NUMBER ;
216
217 CURSOR csr_get_accrual_plan_id(p_assignment_id NUMBER
218 ,p_effective_date DATE
219 ,p_plan_category VARCHAR2) IS
220 SELECT pap.accrual_plan_id
221 FROM pay_accrual_plans pap,
222 pay_element_entries_f pee,
223 pay_element_links_f pel,
224 pay_element_types_f pet
225 WHERE pee.assignment_id = p_assignment_id
226 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
227 AND pel.element_link_id = pee.element_link_id
228 AND pel.element_type_id = pet.element_type_id
232 BEGIN
229 AND pap.accrual_plan_element_type_id = pet.element_type_id
230 AND pap.accrual_category = p_plan_category ;
231
233
234 OPEN csr_get_accrual_plan_id(p_assignment_id, p_effective_date, p_plan_category) ;
235 FETCH csr_get_accrual_plan_id INTO l_accrual_plan_id;
236
237 IF csr_get_accrual_plan_id%NOTFOUND
238 THEN
239 --Changes for bug 2995758 start here
240 p_message := 'ERROR';
241 CLOSE csr_get_accrual_plan_id;
242 ELSE
243 p_message := 'SUCCESS';
244 CLOSE csr_get_accrual_plan_id;
245 --End of changes for bug 2995758
246 END IF ;
247
248
249 RETURN l_accrual_plan_id;
250
251 END get_accrual_plan;
252
253 --------------------------------------------------------------------------
254 -- --
255 -- Name : GET_TERM_NET_ACCRUAL --
256 -- Type : FUNCTION --
257 -- Access : Public --
258 -- Description : Function to return the accrued leave for the given --
259 -- accrual plan --
260 -- --
261 -- Parameters : --
262 -- IN : p_assignment_id NUMBER --
266 -- p_calculation_date DATE --
263 -- p_payroll_id NUMBER --
264 -- p_business_group_id NUMBER --
265 -- p_plan_id NUMBER --
267 -- OUT : p_message VARCHAR2 --
268 -- accrued_leave NUMBER --
269 -- --
270 -- Change History : --
271 --------------------------------------------------------------------------
272 -- Rev# Date Userid Description --
273 --------------------------------------------------------------------------
274 -- 1.0 01-APR-03 statkar Created this function --
275 -- 1.1 06-JUN-03 saikrish Added p_message --
276 --------------------------------------------------------------------------
277 FUNCTION get_term_net_accrual ( p_assignment_id IN NUMBER
278 ,p_payroll_id IN NUMBER
279 ,p_business_group_id IN NUMBER
280 ,p_calculation_date IN DATE
281 ,p_plan_category IN VARCHAR2
282 ,p_message OUT NOCOPY VARCHAR2
283 )
284 RETURN NUMBER
285 IS
286 l_plan_id NUMBER;
287 l_accrued_leave NUMBER := NULL;
288 l_start_date DATE := NULL;
289 l_end_date DATE := NULL;
290 l_accrual_end_date DATE := NULL;
291 l_accrual NUMBER := NULL;
292
293 BEGIN
294
295 l_plan_id := get_accrual_plan ( p_assignment_id => p_assignment_id
296 ,p_effective_date => p_calculation_date
297 ,p_plan_category => p_plan_category
298 ,p_message => p_message --2995758 Changes
299 );
300
301 per_accrual_calc_functions.get_net_accrual(
302 p_assignment_id => p_assignment_id
303 ,p_plan_id => l_plan_id
304 ,p_payroll_id => p_payroll_id
305 ,p_business_group_id => p_business_group_id
306 ,p_calculation_date => p_calculation_date
307 ,p_start_date => l_start_date
308 ,p_end_date => l_end_date
309 ,p_accrual_end_date => l_accrual_end_date
310 ,p_accrual => l_accrual
311 ,p_net_entitlement => l_accrued_leave) ;
312
313 RETURN l_accrued_leave;
314
315 END get_term_net_accrual;
316 --------------------------------------------------------------------------
317 -- --
321 -- Description : Function to validate the contribute method details --
318 -- Name : VALIDATE_CONT_BASE_METHOD --
319 -- Type : FUNCTION --
320 -- Access : Public --
322 -- --
323 -- Parameters : --
324 -- IN : p_cont_base_method VARCHAR2 --
325 -- p_lowest_avg_salary NUMBER --
326 -- p_average_salary NUMBER --
327 -- p_fixed_amount NUMBER --
328 -- RETURN : BOOLEAN --
329 -- --
330 -- Change History : --
331 --------------------------------------------------------------------------
332 -- Rev# Date Userid Description --
333 --------------------------------------------------------------------------
334 -- 1.0 23-JUN-03 statkar Bug 3017511 Created this function --
335 -- 1.1 03-Jul-03 saikrish Added set location and trace calls, --
336 -- p_ee_or_er,p_return_segment parameters --
337 -- 1.2 04-Jul-03 saikrish Removed the p_ee_or_er parameter --
338 -- 1.3 14-Mar-08 dduvvuri Bug 6828199 - Added new cont base method of --
339 -- 'PROV AVE 60'
340 --------------------------------------------------------------------------
341 FUNCTION validate_cont_base_method
342 ( p_cont_base_method IN VARCHAR2
343 ,p_fixed_amount IN NUMBER
344 ,p_lowest_avg_salary IN NUMBER
345 ,p_average_salary IN NUMBER
346 ,p_return_segment OUT NOCOPY VARCHAR2
347 )
348 RETURN BOOLEAN
349 IS
350 l_message VARCHAR2(250);
351 BEGIN
352 g_procedure_name := g_package_name||'validate_cont_base_method';
353 hr_cn_api.set_location(g_debug,' Entering : '|| g_procedure_name, 10);
354
355 IF p_cont_base_method in ( 'CITY AVE 60' , 'PROV AVE 60' ) THEN /* modified for 6828199 */
356
357 IF p_average_salary IS NULL THEN
358 p_return_segment := 'Average Salary'; --Bug#:3034481
359 hr_cn_api.set_location(g_debug,' Return Segment : '||p_return_segment,15);
360 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 20);
361 RETURN FALSE;
362 END IF;
363
364 ELSIF p_cont_base_method = 'CITY LOW LAST YR' THEN
365
366 IF p_lowest_avg_salary IS NULL THEN
367 p_return_segment := 'Lowest Average Salary'; --Bug#:3034481
368 hr_cn_api.set_location(g_debug,' Return Segment : '||p_return_segment,15);
369 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 20);
370 RETURN FALSE;
371 END IF;
372
373 ELSIF p_cont_base_method = 'FIXED' THEN
374
375 IF p_fixed_amount IS NULL THEN
376 --Bug#:3034481
377 p_return_segment := 'Fixed Amount';
378 hr_cn_api.set_location(g_debug,' Return Segment : '||p_return_segment,15);
379 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 20);
380 RETURN FALSE;
381 END IF;
382
383 END IF;
384
385 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 30);
386 RETURN TRUE;
387
388 EXCEPTION
392 hr_utility.trace(l_message);
389 WHEN OTHERS THEN
390 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
391 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 40);
393 RETURN FALSE;
394
395 END validate_cont_base_method;
396
397 --------------------------------------------------------------------------
398 -- --
399 -- Name : VALIDATE_SWITCH_WITH_CONT_BASE --
400 -- Type : FUNCTION --
401 -- Access : Public --
402 -- Description : Function to validate Periodicity against --
403 -- the contribute base calc methods. --
404 -- --
405 -- Parameters : --
406 -- IN : p_cont_base_method VARCHAR2 --
407 -- p_switch_periodicity VARCHAR2 --
408 -- RETURN : BOOLEAN --
409 -- --
410 -- Change History : --
411 --------------------------------------------------------------------------
412 -- Rev# Date Userid Description --
413 --------------------------------------------------------------------------
414 -- 1.0 09-JUL-03 sshankar Bug 3038642 Created this function. --
415 -- 1.1 02-SEP-03 vinaraya Bug 3123334 Included the validation for --
416 -- EE/ER contribution Method being 'N/A' --
417 -- 1.2 03-SEP-03 vinaraya Reverted the changes and moved the check --
418 -- before the function call. --
419 -- --
420 --------------------------------------------------------------------------
421 FUNCTION validate_switch_with_cont_base
422 ( p_cont_base_method IN VARCHAR2
423 ,p_switch_periodicity IN VARCHAR2
424 )
425 RETURN BOOLEAN
426 IS
427 l_message VARCHAR2(250);
428 BEGIN
429
430 g_procedure_name := g_package_name||'validate_switch_with_cont_base';
431 hr_cn_api.set_location(g_debug,' Entering : '|| g_procedure_name, 10);
432
433
434 IF p_switch_periodicity = 'YEARLY' THEN
435 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 20);
436
437 IF ( p_cont_base_method = 'AVE MTH' or p_cont_base_method = 'EMP PRIOR SWITCH' ) THEN
438 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 30);
439 RETURN TRUE;
440 ELSE
441 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 40);
442 RETURN FALSE;
443 END IF;
444
445 ELSIF p_switch_periodicity = 'MONTHLY' THEN
446 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 50);
447 IF ( p_cont_base_method = 'AVE MTH' or p_cont_base_method = 'EMP PRIOR SWITCH') THEN
448 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 60);
449 RETURN FALSE;
450 ELSE
451 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 70);
452 RETURN TRUE;
453 END IF;
454
455 END IF;
456
457 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 80);
458 RETURN TRUE;
459
460 EXCEPTION
461 WHEN OTHERS THEN
462 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
463 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 90);
464 hr_utility.trace(l_message);
465 RETURN FALSE;
466
467
468 END VALIDATE_SWITCH_WITH_CONT_BASE;
469
470 --------------------------------------------------------------------------
471 -- --
472 -- Name : VALIDATE_LOW_LIMIT_METHOD --
473 -- Type : FUNCTION --
474 -- Access : Public --
475 -- Description : Function to validate the low limit method details --
476 -- --
477 -- Parameters : --
478 -- IN : p_low_limit_method VARCHAR2 --
479 -- p_lowest_avg_salary NUMBER --
480 -- p_average_salary NUMBER --
481 -- p_fixed_amount NUMBER --
482 -- RETURN : BOOLEAN --
486 -- Rev# Date Userid Description --
483 -- --
484 -- Change History : --
485 --------------------------------------------------------------------------
487 --------------------------------------------------------------------------
488 -- 1.0 23-JUN-03 statkar Bug 3017511 Created this function --
489 -- 1.1 03-Jul-03 saikrish Added set location and trace calls, --
490 -- p_return_segment --
491 -- 1.2 14-Mar-08 dduvvuri Bug 6828199 - Added new low limit method of --
492 -- 'PROV AVE PREV YEAR'
493 --------------------------------------------------------------------------
494 FUNCTION validate_low_limit_method(
495 p_low_limit_method IN VARCHAR2
496 ,p_fixed_amount IN NUMBER
497 ,p_lowest_avg_salary IN NUMBER
498 ,p_average_salary IN NUMBER
499 ,p_return_segment OUT NOCOPY VARCHAR2
500 )
501 RETURN BOOLEAN
502 IS
503 l_message VARCHAR2(250);
504 BEGIN
505
506 g_procedure_name := g_package_name||'validate_low_limit_method';
507 hr_cn_api.set_location(g_debug,' Entering : '|| g_procedure_name, 10);
508
509 IF p_low_limit_method = 'CITY LOW AVE' THEN
510
511 IF p_lowest_avg_salary IS NULL THEN
512 p_return_segment := 'Lowest Average Salary'; --Bug#:3034481
513 hr_cn_api.set_location(g_debug,' Return Segment : '||p_return_segment,15);
514 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 20);
518 ELSIF p_low_limit_method in ( 'MTH AVE PREV YEAR','PROV AVE PREV YEAR') THEN -- bug 6828199
515 RETURN FALSE;
516 END IF;
517
519
520 IF p_average_salary IS NULL THEN
521 p_return_segment := 'Average Salary'; --Bug#:3034481
522 hr_cn_api.set_location(g_debug,' Return Segment : '||p_return_segment,15);
523 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 20);
524 RETURN FALSE;
525 END IF;
526
527 ELSIF p_low_limit_method = 'FIXED' THEN
528
529 IF p_fixed_amount IS NULL THEN
530 p_return_segment := 'Low Limit Amount'; --Bug#:3034481
531 hr_cn_api.set_location(g_debug,' Return Segment : '||p_return_segment,15);
532 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 20);
533 RETURN FALSE;
534 END IF;
535
536 END IF;
537
538 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 30);
539 RETURN TRUE;
540
541 EXCEPTION
542 WHEN OTHERS THEN
543 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
544 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 40);
545 hr_utility.trace(l_message);
546 RETURN FALSE;
547
548 END validate_low_limit_method;
549
550 --------------------------------------------------------------------------
551 -- --
552 -- Name : VALIDATE_HIGH_LIMIT_METHOD --
553 -- Type : FUNCTION --
554 -- Access : Public --
555 -- Description : Function to validate the low limit method details --
556 -- --
557 -- Parameters : --
561 -- RETURN : BOOLEAN --
558 -- IN : p_high_limit_method VARCHAR2 --
559 -- p_average_salary NUMBER --
560 -- p_fixed_amount NUMBER --
562 -- --
563 -- Change History : --
564 --------------------------------------------------------------------------
565 -- Rev# Date Userid Description --
566 --------------------------------------------------------------------------
567 -- 1.0 23-JUN-03 statkar Bug 3017511 Created this function --
568 -- 1.1 03-Jul-03 saikrish Added set location and trace calls --
569 -- ,p_return_segment --
570 -- 1.2 14-Mar-08 dduvvuri Bug 6828199 - Added 2 new high limit methods
571 -- 'PROV AVE PREV YEAR' , 'CTY AVE PREV YEAR'
572 --------------------------------------------------------------------------
573 FUNCTION validate_high_limit_method(
574 p_high_limit_method IN VARCHAR2
575 ,p_fixed_amount IN NUMBER
576 ,p_average_salary IN NUMBER
577 ,p_return_segment OUT NOCOPY VARCHAR2
578 )
579 RETURN BOOLEAN
580 IS
581 l_message VARCHAR2(250);
582 BEGIN
583
584 g_procedure_name := g_package_name||'validate_high_limit_method';
585 hr_cn_api.set_location(g_debug,' Entering : '|| g_procedure_name, 10);
586
587 IF p_high_limit_method in ( 'MTH AVE PREV YEAR' , 'PROV AVE PREV YEAR' ,'CTY AVE PREV YEAR') THEN -- Bug 6828199
588
589 IF p_average_salary IS NULL THEN
590 p_return_segment := 'Average Salary'; --Bug#:3034481
591 hr_cn_api.set_location(g_debug,' Return Segment : '||p_return_segment,15);
592 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 20);
593 RETURN FALSE;
594 END IF;
595
596 ELSIF p_high_limit_method = 'FIXED' THEN
597
598 IF p_fixed_amount IS NULL THEN
599 p_return_segment := 'High Limit Amount'; --Bug#:3034481
600 hr_cn_api.set_location(g_debug,' Return Segment : '||p_return_segment,15);
601 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 20);
602 RETURN FALSE;
603 END IF;
604
605 END IF;
606
607 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 30);
608 RETURN TRUE;
609
610 EXCEPTION
611 WHEN OTHERS THEN
612 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
613 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 40);
614 hr_utility.trace(l_message);
615 RETURN FALSE;
616
617 END validate_high_limit_method;
618
619 --------------------------------------------------------------------------
620 -- --
621 -- Name : GET_ROUNDED_VALUE --
622 -- Type : FUNCTION --
623 -- Access : Private --
624 -- Description : Function to return the rounded value as per method --
625 -- --
626 -- Parameters : --
627 -- IN : p_value NUMBER --
628 -- p_rounding_method VARCHAR2 --
629 -- RETURN : NUMBER --
630 -- Change History : --
631 --------------------------------------------------------------------------
632 -- Rev# Date Userid Description --
633 --------------------------------------------------------------------------
634 -- 1.0 19-Apr-06 statkar Created this function --
635 --------------------------------------------------------------------------
636 FUNCTION get_rounded_value
637 (p_value IN NUMBER
638 ,p_rounding_method IN VARCHAR2
639 )
640 RETURN NUMBER
641 IS
642 l_amount NUMBER ;
643 l_message VARCHAR2(255);
644 BEGIN
645 g_procedure_name := g_package_name||'get_rounded_value';
646 hr_cn_api.set_location(g_debug,' Entering : '|| g_procedure_name, 10);
647 IF g_debug THEN
649 hr_utility.trace(' P_Value : '||TO_CHAR(p_value));
650 hr_utility.trace(' P_Rounding_Method : '||p_rounding_method);
651 END IF;
652
653 /* Possible rounding methods are:
654 ROUND CENT Round to nearest Cent (0.01)
655 ROUNDTO JIAO Round to nearest Jiao (0.1)
656 ROUND Round to nearest Yuan (1)
657 ROUND JIAO Round up to nearest Jiao (0.1)
658 ROUNDUP YUAN Round up to nearest Yuan (1)
659 */
660
661 IF p_rounding_method = 'ROUND CENT' THEN
662 l_amount := round(p_value,2);
663 ELSIF p_rounding_method = 'ROUNDTO JIAO' THEN
664 l_amount := round(p_value,1);
665 ELSIF p_rounding_method = 'ROUND' THEN
666 l_amount := round(p_value,0) ;
667 ELSIF p_rounding_method = 'ROUND JIAO' THEN
668 l_amount := fffunc.round_up(p_value,1) ;
669 ELSIF p_rounding_method = 'ROUNDUP YUAN' THEN
670 l_amount := fffunc.round_up(p_value,0) ;
671 ELSE
672 l_amount := round(p_value,2);
673 END IF;
674
675 IF g_debug THEN
676 hr_utility.trace(' P_Rounded_Value : '||TO_CHAR(l_amount));
677 hr_utility.trace('=======================================================');
678 END IF ;
679 hr_cn_api.set_location(g_debug,' Leaving : '|| g_procedure_name, 20);
680 RETURN l_amount;
681
682 EXCEPTION
683 WHEN OTHERS THEN
684 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
685 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 40);
686 hr_utility.trace(l_message);
687 RETURN 0;
688 END get_rounded_value;
689
690 --------------------------------------------------------------------------
691 -- --
692 -- Name : GET_CONT_BASE_METHODS --
693 -- Type : FUNCTION --
694 -- Access : Public --
695 -- Description : Function to return the contribute method details --
696 -- --
697 -- Parameters : --
698 -- IN : p_business_group_id NUMBER --
699 -- p_contribution_area VARCHAR2 --
700 -- p_phf_si_type VARCHAR2 --
701 -- p_hukou_type VARCHAR2 --
702 -- p_effective_date DATE --
703 -- OUT : p_ee_cont_base_method VARCHAR2 --
704 -- p_er_cont_base_method VARCHAR2 --
705 -- p_low_limit_method VARCHAR2 --
706 -- p_low_limit_amount NUMBER --
707 -- p_high_limit_method VARCHAR2 --
708 -- p_high_limit_amount NUMBER --
709 -- p_switch_periodicity VARCHAR2 --
710 -- p_switch_month VARCHAR2 --
711 -- p_rounding_method VARCHAR2 --
712 -- p_lowest_avg_salary NUMBER --
713 -- p_average_salary NUMBER --
714 -- p_ee_fixed_amount NUMBER --
715 -- p_er_fixed_amount NUMBER --
716 -- RETURN : VARCHAR2 --
717 -- --
718 -- Change History : --
719 --------------------------------------------------------------------------
720 -- Rev# Date Userid Description --
721 --------------------------------------------------------------------------
722 -- 1.0 19-MAY-03 statkar Created this function --
723 -- 2.0 20-Jun-03 statkar Bug 3017511 -Coding errors removed changes --
724 -- Added validation logic for interdependence --
725 -- 3.0 03-Jul-03 saikrish Added set location and trace calls --
726 -- ,message calls --
727 -- 4.0 03-SEP-03 vinaraya Included additional checks before the --
728 -- validate method function calls. --
729 -- 5.0 15-Mar-05 snekkala Removed the validation for Cont. Basis --
730 -- 6.0 14-Mar-08 dduvvuri Bug 6828199 - Modified signature to fetch
731 -- EE/ER Tax Threshhold amount
732 --------------------------------------------------------------------------
733 FUNCTION get_cont_base_methods
734 (p_business_group_id IN NUMBER
735 ,p_contribution_area IN VARCHAR2
736 ,p_phf_si_type IN VARCHAR2
737 ,p_hukou_type IN VARCHAR2
738 ,p_effective_date IN DATE
739 --
740 ,p_ee_cont_base_method OUT NOCOPY VARCHAR2
741 ,p_er_cont_base_method OUT NOCOPY VARCHAR2
742 ,p_low_limit_method OUT NOCOPY VARCHAR2
743 ,p_low_limit_amount OUT NOCOPY NUMBER
744 ,p_high_limit_method OUT NOCOPY VARCHAR2
745 ,p_high_limit_amount OUT NOCOPY NUMBER
746 ,p_switch_periodicity OUT NOCOPY VARCHAR2
747 ,p_switch_month OUT NOCOPY VARCHAR2
748 ,p_rounding_method OUT NOCOPY VARCHAR2
753 ,p_tax_thrhld_amount OUT NOCOPY NUMBER /* added for bug 6828199 */
648 hr_utility.trace('=======================================================');
749 ,p_lowest_avg_salary OUT NOCOPY NUMBER
750 ,p_average_salary OUT NOCOPY NUMBER
751 ,p_ee_fixed_amount OUT NOCOPY NUMBER
752 ,p_er_fixed_amount OUT NOCOPY NUMBER
754 )
755 RETURN VARCHAR2
756 IS
757
758 l_message VARCHAR2(255);
759
760 --
761 -- Org Information cursor
762 --
763 --
764 -- Bug 3017511 changes. Date format changed to HH24:MI:SS from HH:MI:SS
765 -- in the following two cursors.
766 --
767
768 CURSOR c_cont1 IS
769 --
770 SELECT org_information4 -- EE Contribution Base
771 ,org_information5 -- ER Contribution Base
775 ,fnd_number.canonical_to_number(org_information9) -- High Limit Amount
772 ,org_information6 -- Low Limit Method
773 ,fnd_number.canonical_to_number(org_information7) -- Low Limit Amount
774 ,org_information8 -- High Limit Method
776 ,org_information10 -- Switch Period Periodicity
777 ,org_information11 -- Switch Period Month
778 ,org_information12 -- Rounding Method
779 ,fnd_number.canonical_to_number(org_information13) -- Lowest Average Salary
780 ,fnd_number.canonical_to_number(org_information14) -- Average Salary
781 ,fnd_number.canonical_to_number(org_information17) -- EE Fixed Amount
782 ,fnd_number.canonical_to_number(org_information18) -- ER Fixed Amount
783 ,fnd_number.canonical_to_number(org_information19) -- EE/ER Tax Threshold amount for bug 6828199
784 FROM hr_organization_information
785 WHERE org_information_context = 'PER_CONT_AREA_CONT_BASE_CN'
786 AND organization_id = p_business_group_id
787 AND p_effective_date BETWEEN to_date(org_information15,'YYYY/MM/DD HH24:MI:SS')
788 AND to_date(nvl(org_information16,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
789 AND org_information1 = p_contribution_area
790 AND org_information2 = p_phf_si_type
791 AND org_information3 = p_hukou_type;
792
793 CURSOR c_cont2 IS
794 --
795 SELECT org_information4 -- EE Contribution Base
796 ,org_information5 -- ER Contribution Base
797 ,org_information6 -- Low Limit Method
798 ,fnd_number.canonical_to_number(org_information7) -- Low Limit Amount
799 ,org_information8 -- High Limit Method
800 ,fnd_number.canonical_to_number(org_information9) -- High Limit Amount
801 ,org_information10 -- Switch Period Periodicity
802 ,org_information11 -- Switch Period Month
803 ,org_information12 -- Rounding Method
804 ,fnd_number.canonical_to_number(org_information13) -- Lowest Average Salary
805 ,fnd_number.canonical_to_number(org_information14) -- Average Salary
806 --
807 -- Bug 3017511 changes. Added these two missed columns
808 --
809 ,fnd_number.canonical_to_number(org_information17) -- EE Fixed Amount
810 ,fnd_number.canonical_to_number(org_information18) -- ER Fixed Amount
811 ,fnd_number.canonical_to_number(org_information19) -- EE/ER Tax Threshold amount for bug 6828199
812 FROM hr_organization_information
813 WHERE org_information_context = 'PER_CONT_AREA_CONT_BASE_CN'
814 AND organization_id = p_business_group_id
815 AND p_effective_date BETWEEN to_date(org_information15,'YYYY/MM/DD HH24:MI:SS')
816 AND to_date(nvl(org_information16,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
817 AND org_information1 = p_contribution_area
818 AND org_information2 = p_phf_si_type
819 AND org_information3 IS NULL;
820
821 l_found_flag VARCHAR2(1) ;
822 l_valid BOOLEAN := FALSE;
823 l_indep_seg fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
824 l_dep_seg fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
825 l_lookup_meaning hr_lookups.meaning%TYPE;
826 --
827 -- Start Bug 3038642
828 --
829 l_lookup_meaning_dep hr_lookups.meaning%TYPE;
830 --
831 -- End Bug 3038642
832 --
833
834 p_return_segment fnd_descr_flex_col_usage_vl.end_user_column_name%TYPE;
835 p_ee_or_er VARCHAR2(2);
836
837 BEGIN
838
839 g_procedure_name := g_package_name||'get_cont_base_methods';
840 hr_cn_api.set_location(g_debug,' Entering : '|| g_procedure_name, 10);
841
842 l_found_flag := 'Y';
843 --
844 -- Check for the given hukou_type
845 --
846
847 OPEN c_cont1 ;
848 FETCH c_cont1 INTO
849 p_ee_cont_base_method
850 ,p_er_cont_base_method
851 ,p_low_limit_method
852 ,p_low_limit_amount
853 ,p_high_limit_method
854 ,p_high_limit_amount
855 ,p_switch_periodicity
856 ,p_switch_month
857 ,p_rounding_method
858 ,p_lowest_avg_salary
859 ,p_average_salary
860 ,p_ee_fixed_amount
861 ,p_er_fixed_amount
862 ,p_tax_thrhld_amount; -- added for bug 6828199
863
864 IF c_cont1%NOTFOUND THEN
865 --
866 OPEN c_cont2 ;
867 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 20);
868 --
869 -- Bug 3017511 changes. FETCH c_cont1 changed to FETCH c_cont2
870 --
871 FETCH c_cont2 INTO
872 p_ee_cont_base_method
873 ,p_er_cont_base_method
874 ,p_low_limit_method
875 ,p_low_limit_amount
876 ,p_high_limit_method
877 ,p_high_limit_amount
878 ,p_switch_periodicity
879 ,p_switch_month
880 ,p_rounding_method
881 ,p_lowest_avg_salary
882 ,p_average_salary
883 ,p_ee_fixed_amount
884 ,p_er_fixed_amount
885 ,p_tax_thrhld_amount; -- added for bug 6828199
886
887 IF c_cont2%NOTFOUND THEN
888 --
889 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 30);
890 l_found_flag := 'N';
891 --
892 END IF;
893 CLOSE c_cont2;
894 --
895 END IF;
896 CLOSE c_cont1;
897
898 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 40);
899
900 IF g_debug THEN
901 hr_utility.trace(' =======================================================');
902 hr_utility.trace(' . EE Cont Base Method : '||p_ee_cont_base_method);
903 hr_utility.trace(' . ER Cont Base Method : '||p_er_cont_base_method);
904 hr_utility.trace(' . Low Limit Method : '||p_low_limit_method);
905 hr_utility.trace(' . Low Limit Amount : '||p_low_limit_amount);
906 hr_utility.trace(' . High Limit Method : '||p_high_limit_method);
907 hr_utility.trace(' . High Limit Amount : '||p_high_limit_amount);
908 hr_utility.trace(' . Switch Periodicity : '||p_switch_periodicity);
909 hr_utility.trace(' . Switch Period Month : '||p_switch_month);
910 hr_utility.trace(' . Rounding Method : '||p_rounding_method);
911 hr_utility.trace(' . Lowest Avg Salary : '||p_lowest_avg_salary);
912 hr_utility.trace(' . Average Salary : '||p_average_salary);
913 hr_utility.trace(' . EE Fixed Amount : '||p_ee_fixed_amount);
914 hr_utility.trace(' . ER Fixed Amount : '||p_er_fixed_amount);
915 hr_utility.trace(' . Tax Threshold Amount : '||p_tax_thrhld_amount);
916 hr_utility.trace(' =======================================================');
917 END IF;
918
919 IF l_found_flag = 'N' THEN
920 --Bug 3034481
921 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 50);
922 l_lookup_meaning := hr_general.decode_lookup(p_lookup_type => 'CN_PHF_SI_CODE'
923 ,p_lookup_code => p_phf_si_type
924 );
925 l_message := hr_cn_api.get_pay_message('HR_374609_CONT_BASE_MISSING','PHFSI:'||l_lookup_meaning);
926 --Bug 3034481
927
928 -- Bug 3017511 added the following return clause
929 RETURN l_message;
930
931 ELSE
932 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 60);
933 l_message := 'SUCCESS';
934 END IF;
935
936 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 140);
937
938 RETURN l_message;
939
940
941 EXCEPTION
942 WHEN OTHERS THEN
943 --
944 -- Bug 3017511 changes. Added the two IF..END IF statements for checking open cursors.
945 --
946 IF c_cont1%ISOPEN THEN
947 CLOSE c_cont1;
948 END IF;
949
950 IF c_cont2%ISOPEN THEN
951 CLOSE c_cont2;
952 END IF;
953
954 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
955 hr_utility.trace(l_message);
956 RETURN l_message;
957
958 END get_cont_base_methods;
959
960 --------------------------------------------------------------------------
961 -- --
962 -- Name : GET_PHF_SI_RATES --
963 -- Type : FUNCTION --
964 -- Access : Public --
965 -- Description : Function to return the PHF/SI Rates from Org Level --
966 -- --
967 -- Parameters : --
968 -- IN : p_assignment_id NUMBER --
969 -- p_contribution_area VARCHAR2 --
970 -- p_hukou_type VARCHAR2 --
971 -- p_legal_employer_id NUMBER --
972 -- p_phf_si_type VARCHAR2 --
973 -- p_effective_date VARCHAR2 --
974 -- p_organization_id NUMBER --
975 -- OUT : p_ee_rate NUMBER --
976 -- p_er_rate NUMBER --
977 -- p_ee_percent_or_fixed VARCHAR2 --
978 -- p_er_percent_or_fixed VARCHAR2 --
979 -- p_ee_rounding_method VARCHAR2 --
980 -- p_ee_rounding_method VARCHAR2 --
981 -- RETURN : VARCHAR2 --
982 -- --
983 -- Change History : --
984 --------------------------------------------------------------------------
985 -- Rev# Date Userid Description --
986 --------------------------------------------------------------------------
987 -- 1.0 12-MAY-03 statkar Created this function --
988 -- 1.1 20-Jun-03 statkar Bug 3017511 Changes --
989 -- 1.2 03-Jul-03 saikrish Added set location and trace calls --
990 -- 1.3 04-Jul-03 sshankar Bug 3048748, Validate Hukou type against --
991 -- Organization. --
992 -- 1.4 05-Jul-04 sshankar Bug 3593118. Added Code to support --
993 -- Enterprise Annuity processing. --
994 -- 1.5 15-Jul-04 sshankar Modified code as per review comments in Bug--
995 -- 3593118. And Changed cursor --
996 -- csr_months_of_service and renamed it to --
997 -- csr_years_of_service. --
998 -- 1.6 15-Mar-05 snekkala Removed the validation for Hukuo Type and --
999 -- Organization --
1000 -- 1.7 05-Apr-05 rpalli Bug 4161962. Added Code to support --
1001 -- Enterprise Annuity Rounding. --
1002 -- 1.8 04-Mar-05 snekkala Bug 4303559. Added EE and ER Rounding --
1003 -- methods support. --
1004 -- 1.9 14-Mar-08 dduvvuri Bug 6828199 - Modified the signature to access
1005 -- EE and ER Tax threshold rates
1006 -- 2.0 12-May-08 dduvvuri Bug 6943573 - Added cursor c_org4
1007 -- 2.1 27-May-08 dduvvuri Bug 7120765 - Added fnd_number.canonical_to_number
1008 -- to org_information13 and org_information14 in all
1009 -- the 4 cursors.
1010 -- 2.2 17-Aug-2011 prasrang Added code to handle the increase in --
1011 -- precision for EE Rate and ER Rate. --
1012 --------------------------------------------------------------------------
1013 FUNCTION get_phf_si_rates (p_assignment_id IN NUMBER
1014 ,p_business_group_id IN NUMBER
1015 ,p_contribution_area IN VARCHAR2
1016 ,p_phf_si_type IN VARCHAR2
1017 ,p_employer_id IN VARCHAR2
1018 ,p_hukou_type IN VARCHAR2
1019 ,p_effective_date IN DATE
1020 --
1021 ,p_ee_rate_type OUT NOCOPY VARCHAR2
1022 ,p_er_rate_type OUT NOCOPY VARCHAR2
1023 ,p_ee_rate OUT NOCOPY NUMBER
1024 ,p_er_rate OUT NOCOPY NUMBER
1025 ,p_ee_thrhld_rate OUT NOCOPY NUMBER /* for bug 6828199 */
1026 ,p_er_thrhld_rate OUT NOCOPY NUMBER /* for bug 6828199 */
1027 ,p_ee_rounding_method OUT NOCOPY VARCHAR2
1028 ,p_er_rounding_method OUT NOCOPY VARCHAR2
1029 )
1030 RETURN VARCHAR2
1031
1032 IS
1033 g_procedure_name VARCHAR2(50);
1034 --
1035 -- Org Information cursor
1036 --
1037 --
1038 -- Bug 3017511 changes. Date format changed to HH24:MI:SS from HH:MI:SS
1039 -- in the following three cursors.
1040 --
1041
1042 CURSOR c_org1
1043 IS
1044 SELECT fnd_number.canonical_to_number(org_information4) -- EE Rate
1045 ,org_information5 -- EE Percent or Fixed
1046 ,fnd_number.canonical_to_number(org_information6) -- EE Rate
1047 ,org_information7 -- EE Percent or Fixed
1048 ,org_information8 -- EE Rounding Method
1049 ,org_information12 -- ER Rounding Method
1050 ,fnd_number.canonical_to_number(org_information13) -- EE Tax Threshold rate for bug 6828199
1051 ,fnd_number.canonical_to_number(org_information14) -- ER Tax thershold rate for bug 6828199
1052 FROM hr_organization_information
1053 WHERE org_information_context = 'PER_CONT_AREA_PHF_SI_RATES_CN'
1054 AND organization_id = p_business_group_id
1055 AND p_effective_date BETWEEN to_date(org_information10,'YYYY/MM/DD HH24:MI:SS')
1056 AND to_date(nvl(org_information11,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
1057 AND org_information1 = p_contribution_area
1058 AND org_information3 = p_phf_si_type
1059 AND org_information2 = p_employer_id
1060 AND org_information9 = p_hukou_type;
1061
1062 CURSOR c_org2
1063 IS
1064 SELECT fnd_number.canonical_to_number(org_information4) -- EE Rate
1065 ,org_information5 -- EE Percent or Fixed
1066 ,fnd_number.canonical_to_number(org_information6) -- EE Rate
1067 ,org_information7 -- EE Percent or Fixed
1068 ,org_information8 -- EE Rounding Method
1069 ,org_information12 -- ER Rounding Method
1070 ,fnd_number.canonical_to_number(org_information13) -- EE Tax Threshold rate for bug 6828199
1071 ,fnd_number.canonical_to_number(org_information14) -- ER Tax thershold rate for bug 6828199
1072 FROM hr_organization_information
1073 WHERE org_information_context = 'PER_CONT_AREA_PHF_SI_RATES_CN'
1074 AND organization_id = p_business_group_id
1078 AND org_information3 = p_phf_si_type
1075 AND p_effective_date BETWEEN to_date(org_information10,'YYYY/MM/DD HH24:MI:SS')
1076 AND to_date(nvl(org_information11,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
1077 AND org_information1 = p_contribution_area
1079 AND org_information2 = p_employer_id
1080 AND org_information9 IS NULL;
1081
1082 CURSOR c_org3
1083 IS
1084 SELECT fnd_number.canonical_to_number(org_information4) -- EE Rate
1085 ,org_information5 -- EE Percent or Fixed
1086 ,fnd_number.canonical_to_number(org_information6) -- EE Rate
1087 ,org_information7 -- EE Percent or Fixed
1088 ,org_information8 -- EE Rounding Method
1089 ,org_information12 -- ER Rounding Method
1090 ,fnd_number.canonical_to_number(org_information13) -- EE Tax Threshold rate for bug 6828199
1091 ,fnd_number.canonical_to_number(org_information14) -- ER Tax thershold rate for bug 6828199
1092 FROM hr_organization_information
1093 WHERE org_information_context = 'PER_CONT_AREA_PHF_SI_RATES_CN'
1094 AND organization_id = p_business_group_id
1095 AND p_effective_date BETWEEN to_date(org_information10,'YYYY/MM/DD HH24:MI:SS')
1096 AND to_date(nvl(org_information11,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
1097 AND org_information1 = p_contribution_area
1098 AND org_information3 = p_phf_si_type
1099 AND org_information2 IS NULL
1100 AND org_information9 IS NULL;
1101
1102 CURSOR c_org4
1103 IS
1104 SELECT fnd_number.canonical_to_number(org_information4) -- EE Rate
1105 ,org_information5 -- EE Percent or Fixed
1106 ,fnd_number.canonical_to_number(org_information6) -- EE Rate
1107 ,org_information7 -- EE Percent or Fixed
1108 ,org_information8 -- EE Rounding Method
1109 ,org_information12 -- ER Rounding Method
1110 ,fnd_number.canonical_to_number(org_information13) -- EE Tax Threshold rate for bug 6828199
1111 ,fnd_number.canonical_to_number(org_information14) -- ER Tax thershold rate for bug 6828199
1112 FROM hr_organization_information
1113 WHERE org_information_context = 'PER_CONT_AREA_PHF_SI_RATES_CN'
1114 AND organization_id = p_business_group_id
1115 AND p_effective_date BETWEEN to_date(org_information10,'YYYY/MM/DD HH24:MI:SS')
1116 AND to_date(nvl(org_information11,'4712/12/31 00:00:00'),'YYYY/MM/DD HH24:MI:SS')
1117 AND org_information1 = p_contribution_area
1118 AND org_information3 = p_phf_si_type
1119 AND org_information9 = p_hukou_type
1120 AND org_information2 IS NULL ;
1121
1122 l_indep_seg fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
1123 l_dep_seg fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
1124 l_lookup_meaning hr_lookups.meaning%TYPE;
1125
1126 --
1127 -- End 3048748
1128 --
1129
1130 l_message VARCHAR2(255);
1131
1132 --
1133 -- Bug 3593118
1134 -- Enterprise Annuity Starts
1135 --
1136 l_yrs_of_srvc NUMBER;
1137 l_er_rate NUMBER;
1138 l_ee_rate NUMBER;
1139 l_count_ut NUMBER;
1140 l_ut_message VARCHAR2(3000);
1141 --
1142 -- Cursor to fine number of years of service by the meployee.
1143 --
1144 -- Changed the cursor as per review comments in the Bug and renamed the
1145 -- cursor from csr_months_of_service to csr_years_of_service
1146 --
1147 CURSOR csr_years_of_service(c_effective_date DATE, c_assignment_id NUMBER)
1148 IS
1149 SELECT fnd_number.canonical_to_number(round(months_between(c_effective_date,min(effective_start_date))/12,2)) mths_of_service
1150 FROM per_all_assignments_f
1151 WHERE assignment_id = c_assignment_id; --
1152 -- Cursor to find number of column instances of user table
1153 --
1154 CURSOR csr_count_inst(c_table_name VARCHAR2
1155 ,c_col_name VARCHAR2
1156 ,c_effective_date DATE)
1157 IS
1158 SELECT count(inst.user_column_instance_id)
1159 FROM pay_user_column_instances_f inst
1160 ,pay_user_columns user_col
1161 ,pay_user_tables user_tab
1162 WHERE user_tab.user_table_name = c_table_name
1163 AND user_tab.legislation_code = 'CN'
1164 AND user_tab.user_table_id = user_col.user_table_id
1165 AND user_col.user_column_name = c_col_name
1166 AND user_col.legislation_code = 'CN'
1167 AND user_col.user_column_id = inst.user_column_id
1168 AND c_effective_date BETWEEN inst.effective_start_date
1169 AND inst.effective_end_date;
1170
1171
1172 -- Cursor to find the rounding method for the Enterprise Annuity
1173 -- Bug 4161962
1174 CURSOR c_annuity_round
1175 IS
1176 SELECT org_information1 -- EE Rounding Method
1177 , org_information2 -- ER Rounding Method
1178 FROM hr_organization_information
1179 WHERE org_information_context = 'PER_ORG_ANNUITY_ROUND_CN'
1180 AND organization_id = p_business_group_id;
1181
1182 --
1183 -- End Enterprise Annuity
1184 --
1185
1186
1187 BEGIN
1188
1189 g_procedure_name := g_package_name||'get_phf_si_rates';
1190 hr_cn_api.set_location(g_debug,' Entering: '||g_procedure_name, 10);
1191
1192 l_message :='SUCCESS';
1193 --
1194 -- Bug 3593118
1195 -- Enterprise Annuity Starts
1196 --
1197 IF p_phf_si_type = 'ENTANN' THEN
1198 hr_cn_api.set_location(g_debug,' Enerprise Annuity ', 11);
1199 --
1200 -- Find Number of Years of Service for the assignment
1201 --
1202 IF p_assignment_id IS NULL THEN
1203 -- If assignmentd_id is null then assume no of years of srvc as 0.
1204 l_yrs_of_srvc := 0;
1205 ELSE
1206 OPEN csr_years_of_service(p_effective_date, p_assignment_id);
1207 FETCH csr_years_of_service INTO l_yrs_of_srvc;
1208 CLOSE csr_years_of_service;
1209 END IF;
1210
1211 hr_cn_api.set_location(g_debug,' Years of Service '|| l_yrs_of_srvc, 12);
1212
1213 -- Check if user has eneter values for Employer contribution rates
1214 OPEN csr_count_inst('China Enterprise Annuity Contribution Rate','Employer Rate', p_effective_date);
1215 FETCH csr_count_inst INTO l_count_ut;
1216 CLOSE csr_count_inst;
1217
1218 hr_cn_api.set_location(g_debug,' Employer rate specified in UT, Num of records '|| l_count_ut, 13);
1219
1220 IF l_count_ut > 0 THEN
1221 -- Fetch Employer Contribution Rate
1222 l_er_rate := fnd_number.canonical_to_number(hr_cn_api.get_user_table_value
1223 (p_business_group_id => p_business_group_id
1224 ,p_table_name => 'China Enterprise Annuity Contribution Rate'
1225 ,p_column_name => 'Employer Rate'
1226 ,p_row_name => 'Employer Rate'
1227 ,p_row_value => fnd_number.number_to_canonical(l_yrs_of_srvc)
1228 ,p_effective_date => p_effective_date
1229 ,p_message => l_ut_message));
1230
1231 hr_cn_api.set_location(g_debug,' Employer rate '|| l_er_rate, 14);
1232
1233 IF l_ut_message <> 'SUCCESS' THEN
1234 -- If user has not specified matching range values, then
1235 -- go on to fetch from PHF/SI rates set up at organization level.
1236 hr_cn_api.set_location(g_debug,' Returned message Not Success', 14);
1237 l_er_rate := NULL;
1238 END IF;
1239 END IF;
1240
1241 -- Check if user has eneter values for Employee contribution rates
1242 OPEN csr_count_inst('China Enterprise Annuity Contribution Rate', 'Employee Rate', p_effective_date);
1243 FETCH csr_count_inst INTO l_count_ut;
1244 CLOSE csr_count_inst;
1245
1246 hr_cn_api.set_location(g_debug,' Employee rate specified in UT, Num of records '|| l_count_ut, 15);
1247
1248 IF l_count_ut > 0 THEN
1249 -- Fetch Employee Contribution Rate
1250 l_ee_rate := fnd_number.canonical_to_number(hr_cn_api.get_user_table_value
1251 (p_business_group_id => p_business_group_id
1252 ,p_table_name => 'China Enterprise Annuity Contribution Rate'
1253 ,p_column_name => 'Employee Rate'
1254 ,p_row_name => 'Employee Rate'
1255 ,p_row_value => fnd_number.number_to_canonical(l_yrs_of_srvc)
1256 ,p_effective_date => p_effective_date
1257 ,p_message => l_ut_message));
1258
1259 hr_cn_api.set_location(g_debug,' Employee rate '|| l_ee_rate, 16);
1260
1261 IF l_ut_message <> 'SUCCESS' THEN
1262 -- If user has not specified matching range values, then
1263 -- go on to fetch from PHF/SI rates set up at organization level.
1264 hr_cn_api.set_location(g_debug,' Returned message Not Success', 16);
1265 l_ee_rate := NULL;
1266 END IF;
1267
1268 END IF;
1269
1270 IF l_er_rate IS NOT NULL AND l_ee_rate IS NOT NULL THEN
1271 hr_cn_api.set_location(g_debug,' Both Employee and Employer rate Specified at UT', 17);
1272 p_er_rate := l_er_rate;
1273 p_ee_rate := l_ee_rate;
1274 p_er_rate_type := 'PERCENTAGE';
1275 p_ee_rate_type := 'PERCENTAGE';
1276
1277 -- Bug 4161962
1278 OPEN c_annuity_round;
1279 FETCH c_annuity_round INTO
1280 p_ee_rounding_method
1281 ,p_er_rounding_method;
1282 IF c_annuity_round%NOTFOUND THEN
1283 p_ee_rounding_method:=NULL;
1284 p_er_rounding_method:=NULL;
1285 END IF;
1286 CLOSE c_annuity_round;
1287
1288
1289 IF g_debug THEN
1290 hr_utility.trace(' =======================================================');
1291 hr_utility.trace(' . EE Rate : '||p_ee_rate);
1292 hr_utility.trace(' . EE Rate Type : '||p_ee_rate_type);
1293 hr_utility.trace(' . EE Rounding Method : '||p_ee_rounding_method);
1294 hr_utility.trace(' . ER Rate : '||p_er_rate);
1295 hr_utility.trace(' . ER Rate Type : '||p_er_rate_type);
1296 hr_utility.trace(' . ER Rounding Method : '||p_er_rounding_method);
1297 hr_utility.trace(' =======================================================');
1298 END IF;
1299
1300 RETURN l_message;
1301 END IF;
1302
1303 END IF;
1304 --
1305 -- End Enterprise Annuity
1306 --
1307
1308
1309
1310 OPEN c_org1;
1311 FETCH c_org1 INTO
1312 p_ee_rate
1313 ,p_ee_rate_type
1314 ,p_er_rate
1315 ,p_er_rate_type
1316 ,p_ee_rounding_method
1317 ,p_er_rounding_method
1318 ,p_ee_thrhld_rate -- for bug 6828199
1319 ,p_er_thrhld_rate; -- for bug 6828199
1320
1321
1322
1323 IF c_org1%NOTFOUND THEN
1324 --
1325 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 20);
1326 OPEN c_org2;
1327 FETCH c_org2 INTO
1328 p_ee_rate
1329 ,p_ee_rate_type
1330 ,p_er_rate
1331 ,p_er_rate_type
1332 ,p_ee_rounding_method
1333 ,p_er_rounding_method
1334 ,p_ee_thrhld_rate -- for bug 6828199
1335 ,p_er_thrhld_rate; -- for bug 6828199
1336
1337
1338
1339 IF c_org2%NOTFOUND THEN
1340
1341 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 30);
1342 OPEN c_org3;
1343 FETCH c_org3 INTO
1344 p_ee_rate
1345 ,p_ee_rate_type
1346 ,p_er_rate
1347 ,p_er_rate_type
1348 ,p_ee_rounding_method
1349 ,p_er_rounding_method
1350 ,p_ee_thrhld_rate -- for bug 6828199
1351 ,p_er_thrhld_rate; -- for bug 6828199
1352
1353
1354 IF c_org3%NOTFOUND THEN
1355
1356 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 40);
1357 OPEN c_org4;
1358 FETCH c_org4 INTO
1359 p_ee_rate
1360 ,p_ee_rate_type
1361 ,p_er_rate
1362 ,p_er_rate_type
1363 ,p_ee_rounding_method
1364 ,p_er_rounding_method
1365 ,p_ee_thrhld_rate -- for bug 6828199
1366 ,p_er_thrhld_rate; -- for bug 6828199
1367
1368
1369 IF c_org4%NOTFOUND THEN
1370 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 50);
1371 l_message := hr_cn_api.get_pay_message('HR_374608_PHF_SI_RATES_MISSING','PHFSI:'||p_phf_si_type);
1372 IF g_debug THEN
1373 hr_utility.trace(l_message);
1374 END IF;
1375 END IF;
1376 CLOSE c_org4;
1377 END IF;
1378
1379 -- CLOSE c_org4; -- Commented as part of bug 3886042
1380 --
1381 -- End 3048748
1382 --
1383
1384 -- END IF; -- Commented as part of bug 3886042
1385
1386 CLOSE c_org3;
1387
1388 END IF;
1389
1390 CLOSE c_org2;
1391
1392 END IF;
1393 CLOSE c_org1;
1394
1395 --
1396 -- Bug 3593118
1397 -- Enterprise Annuity Starts
1398 --
1399 IF l_er_rate IS NOT NULL THEN
1400 p_er_rate := l_er_rate;
1401 p_er_rate_type := 'PERCENTAGE';
1402 END IF;
1403 IF l_ee_rate IS NOT NULL THEN
1404 p_ee_rate := l_ee_rate;
1405 p_ee_rate_type := 'PERCENTAGE';
1406 END IF;
1407 --
1408 -- End Enterprise Annuity
1409 --
1410
1411 /* Changes for bug 10073610 */
1412
1413 IF p_ee_rate_type='FIXED' THEN
1414 IF nvl(p_ee_rate,0) <> 0 THEN
1415 p_ee_rate := round(p_ee_rate,2);
1416 END IF;
1417 END IF;
1418
1419 IF p_er_rate_type='FIXED' THEN
1420 IF nvl(p_er_rate,0) <> 0 THEN
1421 p_er_rate := round(p_er_rate,2);
1422 END IF;
1423 END IF;
1424
1425 IF g_debug THEN
1426 hr_utility.trace(' =======================================================');
1427 hr_utility.trace(' . EE Rate : '||p_ee_rate);
1428 hr_utility.trace(' . EE Rate Type : '||p_ee_rate_type);
1429 hr_utility.trace(' . EE Rounding Method : '||p_ee_rounding_method);
1430 hr_utility.trace(' . ER Rate : '||p_er_rate);
1431 hr_utility.trace(' . ER Rate Type : '||p_er_rate_type);
1432 hr_utility.trace(' . ER Rounding Method : '||p_er_rounding_method);
1433 hr_utility.trace(' . EE Tax Threshold Rate : '||p_ee_thrhld_rate);
1434 hr_utility.trace(' . ER Tax Threshold Rate : '||p_er_thrhld_rate);
1435 hr_utility.trace(' =======================================================');
1436 END IF;
1437
1438 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 70);
1439 RETURN l_message;
1440
1441 EXCEPTION
1442 WHEN OTHERS THEN
1443 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
1444
1445 IF c_org4%ISOPEN THEN
1446 CLOSE c_org4;
1447 END IF;
1448
1449 --
1450 -- Bug 3886042 Changes End
1451 --
1452 IF c_org3%ISOPEN THEN
1453 CLOSE c_org3;
1454 END IF;
1455 IF c_org2%ISOPEN THEN
1456 CLOSE c_org2;
1457 END IF;
1458 IF c_org1%ISOPEN THEN
1459 CLOSE c_org1;
1460 END IF;
1461 RETURN l_message;
1462
1463 END get_phf_si_rates;
1464
1465 --------------------------------------------------------------------------
1466 -- --
1467 -- Name : GET_CONT_BASE_AMOUNTS --
1468 -- Type : FUNCTION --
1469 -- Access : Private --
1470 -- Description : Function to return the contribute base amounts --
1471 -- --
1472 -- Parameters : --
1473 -- IN : p_cont_base_method VARCHAR2 --
1474 -- p_phf_si_earnings_asg_ptd NUMBER --
1475 -- p_phf_si_earnings_asg_pmth NUMBER --
1476 -- p_phf_si_earnings_asg_avg NUMBER --
1477 -- p_average_salary NUMBER --
1478 -- p_lowest_average_salary NUMBER --
1479 -- p_fixed_amount NUMBER --
1480 -- OUT : p_cont_base_amount NUMBER --
1481 -- RETURN : VARCHAR2 --
1482 -- --
1483 -- Change History : --
1484 --------------------------------------------------------------------------
1485 -- Rev# Date Userid Description --
1486 --------------------------------------------------------------------------
1487 -- 1.0 12-MAY-03 statkar Created this function --
1488 -- 1.1 26-Jun-03 statkar 3017511-Changed SELECT to IF..THEN..ELSE --
1489 -- 1.2 03-Jul-03 saikrish Added set location and trace calls --
1490 -- 1.3 07-Jul-03 statkar Bug 3038490. Changed spelling --
1491 -- 1.4 14-Mar-08 dduvvuri Bug 6828199 - Added new cont base method of
1492 -- 'PROV AVE 60'
1493 --------------------------------------------------------------------------
1494 FUNCTION get_cont_base_amount
1495 (p_cont_base_method IN VARCHAR2
1496 ,p_phf_si_earnings_asg_ptd IN NUMBER
1497 ,p_phf_si_earnings_asg_pmth IN NUMBER
1498 ,p_phf_si_earnings_asg_avg IN NUMBER
1499 ,p_average_salary IN NUMBER
1500 ,p_lowest_average_salary IN NUMBER
1501 ,p_fixed_amount IN NUMBER
1502 ,p_cont_base_amount OUT NOCOPY NUMBER)
1503 RETURN VARCHAR2
1504 IS
1505 l_cont_base_amount NUMBER;
1506 l_message VARCHAR2(255);
1507
1508 BEGIN
1509 g_procedure_name := g_package_name||'get_cont_base_amount';
1510 hr_cn_api.set_location(g_debug,' Entering: '||g_procedure_name, 10);
1511
1512 l_message := '';
1513
1514 IF p_cont_base_method = 'AVE MTH' THEN
1515 --
1516 -- Bug 3017511 Changed the SELECT DECODE.. to IF..THEN..ELSE conditions
1517 --
1518 IF p_phf_si_earnings_asg_avg = 0 THEN
1519 IF p_phf_si_earnings_asg_pmth = 0 THEN
1520 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 20);
1521 l_cont_base_amount := p_phf_si_earnings_asg_ptd;
1522 ELSE
1523 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 30);
1524 l_cont_base_amount := p_phf_si_earnings_asg_pmth;
1525 END IF;
1526 ELSE
1527 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 40);
1528 l_cont_base_amount := p_phf_si_earnings_asg_avg;
1529 END IF;
1530
1531 ELSIF p_cont_base_method = 'CURRENT' THEN
1532 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 50);
1533 l_cont_base_amount:= p_phf_si_earnings_asg_ptd;
1534 --
1535 -- Bug 3038490. Changed the mis-spelt EMP PRIOR SWITCH
1536 --
1537 ELSIF p_cont_base_method in ('PREVIOUS','EMP PRIOR SWITCH') THEN
1538 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 60);
1539 --
1540 -- Bug 3017511 Changed the SELECT DECODE.. to IF..THEN..ELSE conditions
1541 --
1542 IF p_phf_si_earnings_asg_pmth = 0 THEN
1543 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 70);
1544 l_cont_base_amount := p_phf_si_earnings_asg_ptd;
1545 ELSE
1546 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 80);
1547 l_cont_base_amount := p_phf_si_earnings_asg_pmth;
1548 END IF;
1549
1550 ELSIF p_cont_base_method in ( 'CITY AVE 60', 'PROV AVE 60' ) THEN /* modified for 6828199 */
1551 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 90);
1552 l_cont_base_amount:= 0.6*p_average_salary;
1553
1554 ELSIF p_cont_base_method = 'CITY LOW LAST YR' THEN
1555 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 100);
1556
1557 l_cont_base_amount:= p_lowest_average_salary;
1558
1559 ELSIF p_cont_base_method = 'FIXED' THEN
1560 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 110);
1561
1562 l_cont_base_amount:= p_fixed_amount;
1563
1564 ELSIF p_cont_base_method = 'N/A' THEN
1565 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 120);
1566
1567 l_cont_base_amount:= 0;
1568
1569 END IF;
1570
1571 p_cont_base_amount := l_cont_base_amount;
1572
1573 IF g_debug THEN
1574 hr_utility.trace(' ==============================================');
1575 hr_utility.trace(' . Cont Base Method : '||p_cont_base_method);
1576 hr_utility.trace(' . PHF_SI_EARNINGS (PTD) : '||p_phf_si_earnings_asg_ptd);
1577 hr_utility.trace(' . PHF_SI_EARNINGS (PMTH): '||p_phf_si_earnings_asg_pmth);
1578 hr_utility.trace(' . PHF_SI_EARNINGS (AVG) : '||p_phf_si_earnings_asg_avg);
1579 hr_utility.trace(' . Average Salary : '||p_average_salary);
1580 hr_utility.trace(' . Lowest Avg Salary : '||p_lowest_average_salary);
1581 hr_utility.trace(' . Fixed Amount : '||p_fixed_amount);
1582 hr_utility.trace(' . Cont Base Amount : '||p_cont_base_amount);
1583 hr_utility.trace(' ==============================================');
1584 END IF;
1585
1586 l_message:='SUCCESS';
1587 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 130);
1588 RETURN l_message;
1589
1590 EXCEPTION
1591 WHEN OTHERS THEN
1592 p_cont_base_amount := hr_api.g_number;
1593 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
1594 hr_utility.trace(l_message);
1595 RETURN l_message;
1596 END get_cont_base_amount;
1597
1598 --------------------------------------------------------------------------
1599 -- --
1600 -- Name : GET_RECALCULATE_FLAG --
1601 -- Type : Function --
1602 -- Access : Private --
1603 -- Description : Procedure to set the recalculation flag --
1604 -- --
1605 -- Parameters : --
1606 -- IN : p_switch_periodicity VARCHAR2 --
1607 -- p_switch_month VARCHAR2 --
1608 -- p_calculation_date DATE --
1609 -- p_process_date DATE --
1610 -- OUT : N/A
1611 -- RETURN : VARCHAR2 --
1612 -- --
1613 -- Change History : --
1614 --------------------------------------------------------------------------
1615 -- Rev# Date Userid Description --
1616 --------------------------------------------------------------------------
1617 -- 1.0 12-MAY-03 statkar Created this function --
1618 -- 1.1 20-Jun-03 statkar Changes for Bug 3017511 --
1619 -- 1.2 03-Jul-03 saikrish Added set location and trace calls --
1620 -- 1.3 04-Jul-03 statkar Bug 3039614 Changed the logic --
1621 -- 1.4 15-SEP-03 statkar Bug 3127638. Changed the logic to handle --
1622 -- p_switch_month in 'MM' format instead of --
1623 -- 'MON' format --
1624 -- 1.5 06-APR-04 snekkala Added if condition for recalculate flag --
1625 -- 1.6 06-Apr-04 statkar Bug 3555258 Modified trace calls --
1626 -- 1.7 02-Apr-09 dduvvuri 8328944 Modified Logic for YEARLY periodicity
1627 -- and removed unnecessary code --
1628 -- 1.8 02-Nov-09 dduvvuri 8838185 - Removed unnecessary code for MONTHLY --
1629 -- periodicity and return 'Y' always
1630 --------------------------------------------------------------------------
1631 FUNCTION get_recalculate_flag (p_switch_periodicity IN VARCHAR2
1632 ,p_switch_month IN VARCHAR2
1633 ,p_calculation_date IN DATE
1634 ,p_process_date IN DATE )
1635 RETURN VARCHAR2
1636 IS
1637 l_temp_date DATE;
1638 l_return_value VARCHAR2(1);
1639 l_message VARCHAR2(255);
1640 BEGIN
1641
1642 g_procedure_name := g_package_name||'get_recalculate_flag';
1643 hr_cn_api.set_location(g_debug,' Entering: '||g_procedure_name, 10);
1644 l_return_value := 'N';
1645 --
1646 -- Bug 3039614: Calculation Date can either be g_start_date or
1647 -- the last recalculation date as updated by update_element_entry
1648 -- We do not need to check if its either of the two since for
1649 -- g_start_date, adding a year or a month is going to be less than
1650 -- process date for all practical purposes.
1651 --
1652
1653 IF p_switch_periodicity = 'YEARLY' THEN
1654 --
1655 hr_cn_api.set_location(g_debug, g_procedure_name, 30);
1656 --
1657 -- Bug 3127638
1658 -- Changed to incorporate the new lookup of CN_CALENDAR_MONTH being 'MM' in place of 'MON'
1659 /* 8328944 - Used Process Date instead of calculation date */
1660 l_temp_date := TO_DATE('01-'||p_switch_month||'-'||TO_CHAR(p_process_date,'YYYY'),'DD-MM-YYYY');
1661
1662 IF TO_CHAR(l_temp_date,'MM') = TO_CHAR(p_process_date,'MM') THEN
1663 l_return_value := 'Y';
1664 ELSE
1665 l_return_value := 'N';
1666 END IF;
1667
1668 --
1669 ELSIF p_switch_periodicity = 'MONTHLY' THEN
1670 hr_cn_api.set_location (g_debug, g_procedure_name, 40);
1671 /* 8838185 - Remove unnecessary code and always return Y always */
1672 l_return_value := 'Y';
1673 END IF;
1674
1675 IF g_debug THEN
1676 hr_utility.trace(' ==============================================');
1677 hr_utility.trace(' . Switch Periodicity : '||p_switch_periodicity);
1678 hr_utility.trace(' . Switch Month : '||p_switch_month);
1679 hr_utility.trace(' . Calculation Date : '||TO_CHAR(p_calculation_date,'DD-MM-YYYY'));
1680 hr_utility.trace(' . Process Date : '||TO_CHAR(p_process_date, 'DD-MM-YYYY'));
1681 hr_utility.trace(' . Temporary Date : '||TO_CHAR(l_temp_date,'DD-MM-YYYY'));
1682 hr_utility.trace(' . Recalculate Flag : '||l_return_value);
1683 hr_utility.trace(' ==============================================');
1684 END IF;
1685 --
1686 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 50);
1687 RETURN l_return_value;
1688 --
1689 EXCEPTION
1690 WHEN OTHERS THEN
1691 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
1692 hr_utility.trace (l_message);
1693 RETURN 'E';
1694 END get_recalculate_flag;
1695
1696
1697 --------------------------------------------------------------------------
1698 -- --
1699 -- Name : GET_IN_LIMIT --
1700 -- Type : PROCEDURE --
1701 -- Access : Private --
1702 -- Description : Procedure to set the cont bases in limit --
1703 -- --
1704 -- Parameters : --
1705 -- IN : p_high_limit_method VARCHAR2 --
1706 -- : p_low_limit_method VARCHAR2 --
1707 -- p_high_fixed_amount NUMBER --
1708 -- p_low_fixed_amount NUMBER --
1709 -- p_rounding_method VARCHAR2 --
1710 -- p_average_salary NUMBER --
1711 -- p_lowest_avg_salary NUMBER --
1712 -- p_amount NUMBER --
1713 -- OUT : p_message VARCHAR2 --
1714 -- RETURN : N/A --
1715 -- --
1716 -- Change History : --
1717 --------------------------------------------------------------------------
1718 -- Rev# Date Userid Description --
1719 --------------------------------------------------------------------------
1720 -- 1.0 12-MAR-04 statkar Created this function --
1721 -- 1.1 14-Mar-08 dduvvuri 6828199 - Modifications due to changes in
1722 -- cont base low limit , cont base high limit ,
1723 -- ee/er cont base . Removed parameters p_phf_si_type
1724 -- and p_phf_high_lim_exemp as they are not required
1725 --------------------------------------------------------------------------
1726 PROCEDURE get_in_limit
1727 (p_high_limit_method IN VARCHAR2
1728 ,p_low_limit_method IN VARCHAR2
1729 ,p_high_fixed_amount IN NUMBER
1730 ,p_low_fixed_amount IN NUMBER
1731 ,p_rounding_method IN VARCHAR2
1732 ,p_average_salary IN NUMBER
1733 ,p_lowest_avg_salary IN NUMBER
1734 ,p_amount IN OUT NOCOPY NUMBER
1735 ,p_message OUT NOCOPY VARCHAR2
1736 )
1737 IS
1738
1739 l_low_limit_amount NUMBER;
1740 l_high_limit_amount NUMBER;
1741 l_amount NUMBER;
1742
1743 BEGIN
1744
1745 g_procedure_name := g_package_name||'get_in_limit';
1746 p_message := 'SUCCESS';
1747
1748 hr_cn_api.set_location(g_debug,' Entering : '||g_procedure_name, 10);
1749
1750 --
1751 -- Step 1: Fix the low limit amount
1752 --
1753 -- Valid Low Limit Methods are
1754 --
1755 -- MTH AVE PREV YEAR 60% of city's monthly average salary from last year
1756 -- PROV AVE PREV YEAR 60% of provincial monthly average salary from last year
1757 -- FIXED Fixed Amount
1758 -- CITY LOW AVE The city's lowest MONTHLY average salary for last year
1759 -- N/A Not Applicable
1760 --
1761
1762 IF p_low_limit_method in ( 'MTH AVE PREV YEAR','PROV AVE PREV YEAR' ) THEN -- bug 6828199
1763 l_low_limit_amount := 0.6* p_average_salary;
1764
1765 ELSIF p_low_limit_method = 'FIXED' THEN
1766 l_low_limit_amount := p_low_fixed_amount ;
1767
1768 ELSIF p_low_limit_method = 'CITY LOW AVE' THEN
1769 l_low_limit_amount := p_lowest_avg_salary;
1770
1771 ELSIF p_low_limit_method = 'N/A' THEN
1772 l_low_limit_amount := p_amount;
1773
1774 END IF;
1775
1776
1777 --
1778 -- Step 2: Fix the high limit amount
1779 --
1780 -- Valid High Limit Methods are
1781 --
1782 -- MTH AVE PREV YEAR Three times the city's monthly average salary from last year
1783 -- PROV AVE PREV YEAR Three times the provincial monthly average salary from last year
1784 -- CTY AVE PREV YEAR Five times the city's monthly average salary from last year
1785 -- FIXED Fixed Amount
1786 -- N/A Not Applicable
1787 --
1788
1789 IF p_high_limit_method in ( 'MTH AVE PREV YEAR' , 'PROV AVE PREV YEAR' ) THEN -- bug 6828199
1790 l_high_limit_amount := 3 * p_average_salary;
1791
1792 ELSIF p_high_limit_method = 'FIXED' THEN
1793 l_high_limit_amount := p_high_fixed_amount ;
1794
1795 ELSIF p_high_limit_method = 'CTY AVE PREV YEAR' THEN -- added for bug 6828199
1796 l_high_limit_amount := 5 * p_average_salary ;
1797
1798 ELSIF p_high_limit_method = 'N/A' THEN
1799 l_high_limit_amount := p_amount;
1800 END IF;
1801
1802 -------------------------------------------------------------------------
1803 --
1804 -- Step 3: Fix the amounts in the limit
1805 --
1806
1807 IF p_amount BETWEEN l_low_limit_amount and l_high_limit_amount THEN
1808
1809 l_amount := p_amount;
1810
1811 ELSIF p_amount > l_high_limit_amount THEN
1812
1813 l_amount := l_high_limit_amount;
1814
1815 ELSIF p_amount < l_low_limit_amount THEN
1816
1817 l_amount := l_low_limit_amount;
1818
1819 END IF;
1820
1821
1822 p_amount := get_rounded_value(l_amount,p_rounding_method);
1823
1824 IF g_debug THEN
1825 hr_utility.trace(' ==============================================');
1826 hr_utility.trace(' . Low Limit Method : '||p_low_limit_method);
1827 hr_utility.trace(' . Low Limit Amount : '||l_low_limit_amount);
1828 hr_utility.trace(' . High Limit Method : '||p_high_limit_method);
1829 hr_utility.trace(' . High Limit Amount : '||l_high_limit_amount);
1830 hr_utility.trace(' . Amount In Limit is : '||l_amount);
1831 hr_utility.trace(' . Rounding Method : '||p_rounding_method);
1832 hr_utility.trace(' . Rounded Amount : '||p_amount);
1833 hr_utility.trace(' ==============================================');
1834 END IF;
1835 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 20);
1836
1837 EXCEPTION
1838 WHEN OTHERS THEN
1839 p_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
1840 hr_utility.trace (p_message);
1841
1842 END get_in_limit;
1843
1844 --------------------------------------------------------------------------
1845 -- --
1846 -- Name : UPDATE_ELEMENT_ENTRY --
1847 -- Type : Function --
1848 -- Access : Public --
1849 -- Description : Procedure to update element entry with Calc Date --
1850 -- --
1851 -- Parameters : --
1852 -- IN : p_business_group_id NUMBER --
1853 -- p_element_entry_id NUMBER --
1854 -- p_pay_proc_period_end_date DATE --
1855 -- RETURN : VARCHAR2 --
1856 -- --
1857 -- Change History : --
1858 --------------------------------------------------------------------------
1859 -- Rev# Date Userid Description --
1860 --------------------------------------------------------------------------
1861 -- 1.0 04-Jul-03 statkar Created this function --
1862 -- 1.1 07-Jul-03 statkar Added the CORRECTION mode to handle Re-try --
1863 -- 1.2 14-Jul-03 statkar Bug 3050951. Added check for termination --
1864 -- 1.3 16-Jul-03 statkar Bug 3053186. Added check for end-date --
1865 -- 1.4 21-Jul-03 statkar Bug 3057542. Added check for future rows --
1866 -- 1.5 31-Jul-03 statkar Bug 3057542 Modified exception handling --
1867 -- 1.6 15-SEP-03 statkar Bug 3127638 Modified p_pay_proc_period_end_date
1868 -- 1.7 25-FEB-04 snekkala Bug 3456162 Modified the cursor c_asg_status--
1869 --------------------------------------------------------------------------
1870 FUNCTION update_element_entry
1871 (p_business_group_id IN NUMBER
1872 ,p_element_entry_id IN NUMBER
1873 ,p_calculation_date IN DATE
1874 )
1875 RETURN VARCHAR2
1876 IS
1877 --
1878 CURSOR c_ovn (p_calculation_date IN DATE) IS
1879 SELECT object_version_number
1880 FROM pay_element_entries_f
1881 WHERE element_entry_id = p_element_entry_id
1882 AND p_calculation_date BETWEEN effective_start_date
1883 AND effective_end_date;
1884 --
1885 l_ovn pay_element_entries_f.object_version_number%TYPE;
1886 --
1887 l_effective_start_date DATE;
1888 l_effective_end_date DATE;
1889 l_warning BOOLEAN;
1890 --
1891 CURSOR c_iv IS
1892 SELECT piv.input_value_id
1893 FROM pay_element_entries_f pee,
1894 pay_element_links_f pel,
1895 pay_input_values_f piv
1896 WHERE pee.element_entry_id = p_element_entry_id
1897 AND pee.element_link_id = pel.element_link_id
1898 AND pel.element_type_id = piv.element_type_id
1899 AND piv.name = 'Calculation Date'
1900 AND p_calculation_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1901 AND p_calculation_date BETWEEN pel.effective_start_date AND pel.effective_end_date
1902 AND p_calculation_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
1903 --
1904 l_iv_id pay_input_values_f.input_value_id%TYPE;
1905 --
1906 -- Bug 3057542 changes for checking future row
1907 --
1908 CURSOR c_exists (p_input_value_id IN NUMBER) IS
1909 SELECT effective_start_date
1910 FROM pay_element_entry_values_f
1911 WHERE element_entry_id = p_element_entry_id
1912 AND input_value_id = p_input_value_id
1913 AND effective_start_date >= p_calculation_date + 1
1914 ORDER by effective_start_date;
1915 --
1916 l_eff_start_date pay_element_entry_values_f.effective_start_date%TYPE;
1917
1918 l_dummy VARCHAR2(10);
1919 l_upd_mode VARCHAR2(30);
1920 --
1921 -- Bug 3050951: Added the c_asg_status cursor
1922 --
1923
1924 --
1925 --Bug 3456162: Changed the cursor to parameterised cursor
1926 --
1927
1928 CURSOR c_asg_status (l_effective_date IN DATE) IS
1929 SELECT past.per_system_status
1930 FROM pay_element_entries_f pee,
1931 per_assignments_f paf,
1932 per_assignment_status_types past
1933 WHERE pee.element_entry_id = p_element_entry_id
1934 AND pee.assignment_id = paf.assignment_id
1935 AND paf.assignment_status_type_id = past.assignment_status_type_id
1936 AND l_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date --Bug 3456162
1937 AND l_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date; --Bug 3456162
1938 --
1939 --Bug 3456162: Changes end.
1940 --
1941 --
1942 l_asg_status per_assignment_status_types.per_system_status%TYPE;
1943 --
1944 -- Bug 3050951: Changes end.
1945 --
1946
1947 --
1948 -- Bug 3053186: Added the c_ee_end cursor
1949 --
1950 CURSOR c_ee_end IS
1951 SELECT 'exists'
1952 FROM pay_element_entries_f
1953 WHERE element_entry_id = p_element_entry_id
1954 AND LAST_DAY(effective_end_date) = p_calculation_date;
1955 --
1956 l_message VARCHAR2(1000);
1957 l_days NUMBER;
1958 --
1959 BEGIN
1960 g_procedure_name := g_package_name||'update_element_entry';
1961 l_message := 'SUCCESS';
1962
1963 hr_cn_api.set_location(g_debug,' Entering: '||g_procedure_name, 10);
1964 --
1965 -- Bug 3050951: Added the following check for active assignments
1966 --
1967
1968 --
1969 --Bug 3456162 changes the open cursor
1970 --
1971 OPEN c_asg_status (p_calculation_date);
1972 FETCH c_asg_status
1973 INTO l_asg_status;
1974 CLOSE c_asg_status;
1975 --
1976 --Bug 3456162 Changes end
1977 --
1978 --
1979 IF l_asg_status = 'TERM_ASSIGN' THEN
1980 --
1981 hr_cn_api.set_location (g_debug,'Employee is terminated. Not calling pay_element_entry_api.',15);
1982 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name,15);
1983 RETURN l_message;
1984 --
1985 END IF;
1986 --
1987 -- Bug 3050951 Changes end
1988 --
1989 OPEN c_iv;
1990 FETCH c_iv
1991 INTO l_iv_id;
1992
1993 --
1994 -- Bug 3053186 Changes Start for checking the effective_end_date
1995 --
1996 IF c_iv%NOTFOUND THEN
1997 --
1998 OPEN c_ee_end;
1999 FETCH c_ee_end
2000 INTO l_dummy;
2001 --
2002 IF c_ee_end%FOUND THEN
2003 --
2004 CLOSE c_ee_end;
2005 hr_cn_api.set_location(g_debug,' Element Entry is end-dated this month. Not calling pay_element_entry_api.',18);
2006 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name,18);
2007 RETURN l_message;
2008 --
2009 ELSE
2010 --
2011 CLOSE c_ee_end;
2012 hr_cn_api.set_location(g_debug,' Element Entry is active this month. Can call pay_element_entry_api.',19);
2013 hr_cn_api.set_location(g_debug,' ' || g_procedure_name,19);
2014 --
2015 END IF;
2016 --
2017 END IF;
2018 --
2019 -- Bug 3053186 Changes end
2020 --
2021 CLOSE c_iv;
2022 --
2023 hr_cn_api.set_location(g_debug,' ' || g_procedure_name,20);
2024 hr_cn_api.set_location(g_debug,' Input Value ID : '||to_char(l_iv_id),25);
2025 --
2026 OPEN c_exists (l_iv_id);
2027 FETCH c_exists
2028 INTO l_eff_start_date;
2029 --
2030 IF c_exists%NOTFOUND THEN
2031 --
2032 hr_cn_api.set_location (g_debug, g_procedure_name, 32);
2033 --
2034 -- Bug 3456162 added the code
2035 --
2036 OPEN c_asg_status (p_calculation_date + 1);
2037 FETCH c_asg_status
2038 INTO l_asg_status;
2039
2040 IF c_asg_status%NOTFOUND THEN
2041 --
2042 hr_cn_api.set_location(g_debug,' Employee is terminated on last day. Not calling pay_element_entry_api.',33);
2043 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name,33);
2044 RETURN l_message;
2045 --
2046 END IF;
2047 CLOSE c_asg_status;
2048 --
2049 --Bug 3456162 changes end
2050 --
2051 OPEN c_ovn (p_calculation_date);
2052 FETCH c_ovn
2053 INTO l_ovn;
2054 CLOSE c_ovn;
2055 --
2056 l_upd_mode := 'UPDATE';
2057 --
2058 ELSE
2059 --
2060 -- Bug 3057542 changes
2061 --
2062 hr_cn_api.set_location (g_debug, g_procedure_name, 34);
2063
2064 IF (l_eff_start_date = p_calculation_date + 1) THEN
2065
2066 OPEN c_ovn (p_calculation_date+1);
2067 FETCH c_ovn
2068 INTO l_ovn;
2069 CLOSE c_ovn;
2070 --
2071 l_upd_mode := 'CORRECTION';
2072 --
2073 ELSE
2074
2075 hr_cn_api.set_location (g_debug, g_procedure_name, 36);
2076
2077 OPEN c_ovn (p_calculation_date);
2078 FETCH c_ovn
2079 INTO l_ovn;
2080 CLOSE c_ovn;
2081
2082 --
2083 l_upd_mode := 'UPDATE_CHANGE_INSERT';
2084 --
2085 END IF;
2086 --
2087 -- Bug 3057542 changes end
2088 --
2089 END IF;
2090 CLOSE c_exists;
2091 --
2092 hr_cn_api.set_location(g_debug,' ' || g_procedure_name,40);
2093 IF g_debug THEN
2094 hr_utility.trace(' ==============================================');
2095 hr_utility.trace ('. Calling pay_element_entry_api.');
2096 hr_utility.trace ('. Element Entry : '||TO_CHAR(p_element_entry_id));
2097 hr_utility.trace ('. Effective Date : '||TO_CHAR(p_calculation_date+1,'DD-MM-YYYY'));
2098 hr_utility.trace ('. Input Value Id : '||TO_CHAR(l_iv_id));
2099 hr_utility.trace ('. Entry Value : '||TO_CHAR(p_calculation_date,'DD-MM-YYYY'));
2100 hr_utility.trace ('. OVN : '||to_char(l_ovn));
2101 hr_utility.trace ('. Date-track Mode : '||l_upd_mode);
2102 hr_utility.trace(' ==============================================');
2103 END IF;
2104
2105 BEGIN
2106 pay_element_entry_api.update_element_entry
2107 (p_datetrack_update_mode => l_upd_mode
2108 ,p_effective_date => p_calculation_date + 1
2109 ,p_business_group_id => p_business_group_id
2110 ,p_element_entry_id => p_element_entry_id
2111 ,p_object_version_number => l_ovn
2112 ,p_input_value_id1 => l_iv_id
2113 ,p_entry_value1 => fnd_date.date_to_chardate(p_calculation_date) -- Bug 3127638
2114 ,p_effective_start_date => l_effective_start_date
2115 ,p_effective_end_date => l_effective_end_date
2116 ,p_update_warning => l_warning
2117 );
2118
2119 EXCEPTION
2120 WHEN OTHERS THEN
2121 --
2122 hr_cn_api.set_location(g_debug,' . Error in pay_element_entry_api.',5);
2123 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name,5);
2124 l_message := SUBSTRB(SQLERRM,1,240);
2125 RETURN l_message;
2126 --
2127 END;
2128 --
2129 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name,50);
2130 RETURN l_message;
2131 --
2132 EXCEPTION
2133 WHEN OTHERS THEN
2134 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
2135 RETURN l_message;
2136
2137 END update_element_entry;
2138
2139
2140 --------------------------------------------------------------------------
2141 -- --
2142 -- Name : GET_CONT_AMOUNT --
2143 -- Type : Function --
2144 -- Access : Private --
2145 -- Description : Function to calculate the contribution amounts --
2146 -- --
2147 -- Parameters : --
2148 -- IN : p_high_limit NUMBER --
2149 -- p_low_limit NUMBER --
2150 -- p_amount NUMBER --
2151 -- p_rounding_method VARCHAR2 --
2152 -- OUT : N/A --
2153 -- RETURN : NUMBER --
2154 -- --
2155 -- Change History : --
2156 --------------------------------------------------------------------------
2157 -- Rev# Date Userid Description --
2158 --------------------------------------------------------------------------
2159 -- 1.0 12-MAR-04 statkar Created this function --
2160 -- 1.1 05-APR-05 rpalli Bug 4161962. Added Code to round amounts --
2161 -- to 2 decimal places for null rounding --
2162 -- method passed. --
2163 --------------------------------------------------------------------------
2164 FUNCTION get_cont_amount
2165 (p_cont_base IN NUMBER
2166 ,p_rate_type IN VARCHAR2
2167 ,p_rate_amount IN NUMBER
2168 ,p_rounding_method IN VARCHAR2
2169 ,p_message OUT NOCOPY VARCHAR2)
2170 RETURN NUMBER
2171 IS
2172 l_amount NUMBER;
2173 BEGIN
2174 g_procedure_name := g_package_name||'get_cont_amount';
2175 hr_cn_api.set_location(g_debug,' Entering : '||g_procedure_name, 10);
2176
2177 p_message := 'SUCCESS';
2178 IF g_debug THEN
2179 hr_utility.trace(' ==============================================');
2180 hr_utility.trace(' . Rate Type : '||p_rate_type);
2181 hr_utility.trace(' . Rate : '||p_rate_amount);
2182 hr_utility.trace(' . Contribution Base : '||p_cont_base);
2183 END IF;
2184 IF p_rate_type = 'FIXED' THEN
2185 l_amount := p_rate_amount;
2186
2187 ELSIF p_rate_type = 'PERCENTAGE' THEN
2188 l_amount := p_cont_base * p_rate_amount/100;
2189
2190 END IF;
2191
2192 IF g_debug THEN
2193 hr_utility.trace(' . Contribution Amount : '||l_amount);
2194 hr_utility.trace(' . Rounding Method : '||p_rounding_method);
2195 END IF;
2196
2197 l_amount := get_rounded_value(l_amount, p_rounding_method);
2198
2199 IF g_debug THEN
2200 hr_utility.trace(' . Rounded Amount : '||l_amount);
2201 hr_utility.trace(' ==============================================');
2202 END IF;
2203
2204 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 40);
2205 RETURN l_amount;
2206
2207 EXCEPTION
2208 WHEN OTHERS THEN
2209 p_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
2210 RETURN hr_api.g_number;
2211
2212 END get_cont_amount;
2213
2214 --------------------------------------------------------------------------
2215 -- --
2216 -- Name : GET_PHF_SI_DEFERRED_AMOUNTS --
2217 -- Type : Function --
2218 -- Access : Public --
2219 -- Description : Procedure to calculate the deffered phf/si amounts --
2220 -- --
2221 -- Parameters : --
2222 -- IN : p_pay_proc_period_end_date DATE --
2223 -- p_actual_probation_end_date DATE --
2224 -- p_const_probation_end_date DATE --
2225 -- p_defer_deductions VARCHAR2 --
2226 -- p_deduct_in_probation_expiry VARCHAR2 --
2227 -- p_taxable_earnings_asg_er_ptd NUMBER --
2228 -- IN/OUT : p_ee_phf_si_amount NUMBER --
2229 -- p_er_phf_si_amount NUMBER --
2230 -- p_undeducted_ee_phf_ltd NUMBER --
2231 -- p_undeducted_er_phf_ltd NUMBER --
2232 -- RETURN : VARCHAR2 --
2233 -- --
2234 -- Change History : --
2235 --------------------------------------------------------------------------
2236 -- Rev# Date Userid Description --
2237 --------------------------------------------------------------------------
2238 -- 1.0 12-MAY-03 statkar Created this function --
2239 -- 1.1 20-Jun-03 statkar Bug 3017511 changes to logic --
2240 -- 1.2 03-Jul-03 saikrish Added set location and trace calls --
2241 -- 1.3 09-Jul-03 saikrish Changed code w.r.t to bug 3042788 --
2242 -- 1.4 10-Jul-03 saikrish Remove off TRUNC(TO_DATE()) for the --
2243 -- probation end date parameter. --
2244 --------------------------------------------------------------------------
2245 FUNCTION get_phf_si_deferred_amounts
2246 (p_pay_proc_period_end_date IN DATE
2247 ,p_actual_probation_end_date IN DATE
2248 ,p_const_probation_end_date IN DATE
2249 ,p_defer_deductions IN VARCHAR2
2250 ,p_deduct_in_probation_expiry IN VARCHAR2
2251 ,p_taxable_earnings_asg_er_ptd IN NUMBER
2252 --
2253 ,p_ee_phf_si_amount IN OUT NOCOPY NUMBER
2254 ,p_er_phf_si_amount IN OUT NOCOPY NUMBER
2255 ,p_undeducted_ee_phf_ltd IN OUT NOCOPY NUMBER
2256 ,p_undeducted_er_phf_ltd IN OUT NOCOPY NUMBER
2257 )
2258 RETURN VARCHAR2
2259
2260 IS
2261 l_defer_date DATE;
2262 l_default_date DATE;
2263 l_probation_end_date DATE;
2264 l_message VARCHAR2(80);
2265 l_probation_flag VARCHAR2(1);
2266 l_temp NUMBER;
2267 BEGIN
2268 g_procedure_name := g_package_name||'get_phf_si_deferred_amounts';
2269 hr_cn_api.set_location(g_debug,' Entering: '||g_procedure_name, 10);
2270
2271 l_message := 'SUCCESS';
2272 l_default_date := TO_DATE ('01-01-0001','DD-MM-YYYY');
2273
2274 IF g_debug THEN
2275 hr_utility.trace ('==============================================');
2276 hr_utility.trace (' Actual Probn End Date : '||p_actual_probation_end_date);
2277 hr_utility.trace (' Cont. Probn End Date : '||p_const_probation_end_date);
2278 hr_utility.trace (' Defer Deductions : '||p_defer_deductions);
2279 hr_utility.trace (' Deduct in Probation Expiry : '||p_deduct_in_probation_expiry);
2280 hr_utility.trace (' Taxable Earnings : '||p_taxable_earnings_asg_er_ptd);
2281
2282 hr_utility.trace (' Old EE PHF/SI amount : '||p_ee_phf_si_amount);
2283 hr_utility.trace (' Old ER PHF/SI amount : '||p_er_phf_si_amount);
2284 hr_utility.trace (' Old Undeducted EE PHF LTD : '||p_undeducted_ee_phf_ltd);
2285 hr_utility.trace (' Old Undeducted ER PHF LTD : '||p_undeducted_er_phf_ltd);
2286 hr_utility.trace (' p_pay_proc_period_end_date : '||TO_CHAR(p_pay_proc_period_end_date,'DD-MM-YYYY'));
2287 hr_utility.trace ('==============================================');
2288 END IF;
2289
2290
2291 --3042788 Code is modified to consider the defaulting of Actual,Const Probation end dates
2292 -- in the PHF/SI fast formulae.
2293 IF p_actual_probation_end_date = pay_cn_deductions.g_start_date THEN
2294 l_probation_end_date := p_const_probation_end_date;
2295 ELSE
2296 l_probation_end_date := p_actual_probation_end_date;
2297 END IF;
2298
2299 hr_cn_api.set_location (g_debug,'Probation End Date : '||l_probation_end_date,15);
2300
2301 IF l_probation_end_date = pay_cn_deductions.g_start_date THEN
2302 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 20);
2303 l_probation_flag := 'N';
2304 END IF;
2305
2306 hr_cn_api.set_location (g_debug,'Probation Flag After being Set : '||l_probation_flag,25);
2307 --3042788
2308 --
2309 -- Bug 3017511 changes. Logic Changed since deduct in probation expiry was not
2310 -- being considered at all
2311 --
2312 IF p_defer_deductions = 'N' OR
2313 ( p_defer_deductions ='Y'
2314 AND
2315 (
2316 ( p_deduct_in_probation_expiry= 'Y' AND l_probation_end_date <= p_pay_proc_period_end_date ) OR
2317 ( p_deduct_in_probation_expiry= 'N' AND add_months(l_probation_end_date,1) <= p_pay_proc_period_end_date ) OR
2318 l_probation_flag = 'N'
2319 )
2320 )
2321 THEN
2322
2323 IF p_undeducted_ee_phf_ltd >0 THEN
2324 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 30);
2325 p_ee_phf_si_amount := p_ee_phf_si_amount + p_undeducted_ee_phf_ltd;
2326 p_undeducted_ee_phf_ltd := 0 - p_undeducted_ee_phf_ltd;
2327
2328 END IF;
2329
2330 IF p_undeducted_er_phf_ltd >0 THEN
2331 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 40);
2332 p_er_phf_si_amount := p_er_phf_si_amount + p_undeducted_er_phf_ltd;
2333 p_undeducted_er_phf_ltd := 0 - p_undeducted_er_phf_ltd;
2334 END IF;
2335
2336 ELSE
2337 --
2338 -- Bug 3017511 changes. Logic Changed since in case of deferment, undeducted phf
2339 -- was getting incorrectly fed.
2340 --
2341 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 50);
2342 p_undeducted_ee_phf_ltd := p_ee_phf_si_amount;
2343 p_ee_phf_si_amount := 0;
2344
2345 p_undeducted_er_phf_ltd := p_er_phf_si_amount;
2346 p_er_phf_si_amount := 0;
2347
2348 END IF;
2349
2350 IF g_debug THEN
2351 hr_utility.trace(' ==============================================');
2352 hr_utility.trace(' New EE PHF/SI amount : '||p_ee_phf_si_amount);
2353 hr_utility.trace(' New ER PHF/SI amount : '||p_er_phf_si_amount);
2354 hr_utility.trace(' New Undeducted EE PHF LTD : '||p_undeducted_ee_phf_ltd);
2355 hr_utility.trace(' New Undeducted ER PHF LTD : '||p_undeducted_er_phf_ltd);
2356 hr_utility.trace(' ==============================================');
2357 END IF;
2358
2359 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 60);
2360 RETURN l_message;
2361
2362 EXCEPTION
2363 WHEN OTHERS THEN
2364 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
2365 RETURN l_message;
2366 END get_phf_si_deferred_amounts;
2367
2368 ----------------------------------------------------------------------------
2369 -- --
2370 -- Name : GET_DEF_BAL_ID --
2371 -- Type : Function --
2372 -- Access : Private --
2373 -- Description : Function to get the defined balance for balance --
2374 -- --
2375 -- Parameters : --
2376 -- IN : p_phf_si_type TYPE --
2377 -- p_assignment_action_id TYPE --
2378 -- RETURN : NUMBER --
2379 -- --
2380 -- Change History : --
2381 ----------------------------------------------------------------------------
2382 -- Rev# Date Userid Description --
2383 ----------------------------------------------------------------------------
2384 -- 1.0 16-MAR-04 snekkala Created this function --
2385 --1.1 13-Mar-13 mdubasi Handled no-data found --
2386 ----------------------------------------------------------------------------
2387 FUNCTION get_def_bal_id
2388 (p_balance_name IN pay_balance_types.balance_name%TYPE
2389 ,p_dimension_name IN pay_balance_dimensions.dimension_name%TYPE)
2390 RETURN NUMBER
2391 IS
2392
2393 CURSOR csr_def_bal_id
2394 IS
2395 SELECT pdb.defined_balance_id
2396 FROM pay_defined_balances pdb
2397 ,pay_balance_types pbt
2398 ,pay_balance_dimensions pbd
2399 WHERE pbt.balance_name = p_balance_name
2400 AND pbd.dimension_name = p_dimension_name
2401 AND pdb.balance_type_id = pbt.balance_type_id
2402 AND pbt.legislation_code = 'CN'
2403 AND pbd.legislation_code = 'CN'
2404 AND pdb.legislation_code = 'CN'
2405 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
2406
2407 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
2408 l_message VARCHAR2(80);
2409
2410 BEGIN
2411 l_message := 'SUCCESS';
2412
2413 OPEN csr_def_bal_id;
2414 FETCH csr_def_bal_id
2415 INTO l_def_bal_id;
2416 IF csr_def_bal_id %NOTFOUND THEN
2417 l_def_bal_id := 0 ;
2418 END IF;
2419 CLOSE csr_def_bal_id;
2420
2421 IF g_debug THEN
2422 hr_utility.trace ('. '||RPAD(TRIM(p_balance_name||p_dimension_name),35,' ')||' : '||l_def_bal_id);
2423 END IF;
2424
2425 RETURN l_def_bal_id;
2426
2427 EXCEPTION
2428 WHEN OTHERS THEN
2429 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
2430 hr_utility.trace(l_message);
2431
2432 END get_def_bal_id ;
2433
2434 ----------------------------------------------------------------------------
2435 -- --
2436 -- Name : GET_PHF_SI_BALANCES --
2437 -- Type : Function --
2438 -- Access : Private --
2439 -- Description : Function to get the balances of elements --
2440 -- --
2441 -- Parameters : --
2442 -- IN : p_phf_si_type VARCHAR2 --
2443 -- p_assignment_action_id NUMBER --
2444 -- p_tax_unit_id NUMBER --
2445 -- p_date_earned DATE --
2446 -- OUT : p_balance_value_tab T_BALANCE_VALUE_TAB --
2447 -- --
2448 -- Change History : --
2449 ----------------------------------------------------------------------------
2450 -- Rev# Date Userid Description --
2451 ----------------------------------------------------------------------------
2452 -- 1.0 16-MAR-04 snekkala Created this function --
2453 -- 1.1 05-JUL-04 sshankar Added Enterprise Annuity to cursor --
2454 -- c_element_name --
2455 ----------------------------------------------------------------------------
2456 PROCEDURE get_phf_si_balances
2457 (p_phf_si_type IN VARCHAR2
2458 ,p_assignment_action_id IN NUMBER
2459 ,p_tax_unit_id IN NUMBER
2460 ,p_date_earned IN DATE
2461 ,p_balance_value_tab OUT NOCOPY pay_balance_pkg.t_balance_value_tab
2462 )
2463 IS
2464
2465 CURSOR c_element_name is
2466 SELECT decode(p_phf_si_type
2467 ,'PHF','PHF'
2468 ,'MEDICAL','Medical'
2469 ,'PENSION','Pension'
2470 ,'SUPPMED','Supp Medical'
2471 ,'MATERNITY','Maternity'
2472 ,'UNEMPLOYMENT','Unemployment'
2473 ,'INJURY','Injury'
2474 ,'ENTANN','Enterprise Annuity'
2475 )
2476 ,decode(p_phf_si_type
2477 ,'PHF','PHF'
2478 ,'MEDICAL','Medical'
2479 ,'PENSION','Pension'
2480 ,'SUPPMED','Supplementary Medical'
2481 ,'MATERNITY','Maternity Insurance'
2482 ,'UNEMPLOYMENT','Unemploy Insurance'
2483 ,'INJURY','Injury Insurance'
2484 ,'ENTANN','Enterprise Annuity'
2485 )
2486 FROM dual;
2487
2488 l_element_name VARCHAR2(50);
2489 l_insurance_type VARCHAR2(50);
2490 l_message VARCHAR2(80);
2491 BEGIN
2492 g_procedure_name := g_package_name||'get_phf_si_balances';
2493
2494 hr_cn_api.set_location(g_debug,' Entering : '||g_procedure_name, 10);
2495
2496 l_message := 'SUCCESS';
2497
2498 --
2499 -- Fetch the element specific names
2500 --
2501 OPEN c_element_name;
2502 FETCH c_element_name INTO l_element_name,l_insurance_type;
2503 CLOSE c_element_name;
2504
2505 IF g_debug THEN
2506 hr_utility.trace ('===================== Defined Balances =========================');
2507 END IF;
2508
2509 p_balance_value_tab(1).defined_balance_id := get_def_bal_id('PHF SI Earnings','_ASG_PTD');
2510 p_balance_value_tab(2).defined_balance_id := get_def_bal_id('PHF SI Earnings','_ASG_PYEAR');
2511 p_balance_value_tab(3).defined_balance_id := get_def_bal_id('PHF SI Earnings','_ASG_PMTH');
2512 p_balance_value_tab(4).defined_balance_id := get_def_bal_id('Taxable Earnings','_ASG_ER_PTD');
2513
2514 p_balance_value_tab(5).defined_balance_id := get_def_bal_id(l_element_name||' EE Contribution Base','_ASG_LTD');
2515 p_balance_value_tab(6).defined_balance_id := get_def_bal_id(l_element_name||' ER Contribution Base','_ASG_LTD');
2516 p_balance_value_tab(7).defined_balance_id := get_def_bal_id(l_insurance_type||' Employee Deductions','_ASG_ER_PTD');
2517 p_balance_value_tab(8).defined_balance_id := get_def_bal_id(l_insurance_type||' Employer Deductions','_ASG_ER_PTD');
2518 p_balance_value_tab(9).defined_balance_id := get_def_bal_id('Undeducted EE '||l_element_name,'_ASG_LTD');
2519 p_balance_value_tab(10).defined_balance_id := get_def_bal_id('Undeducted ER '||l_element_name,'_ASG_LTD');
2520 p_balance_value_tab(11).defined_balance_id := get_def_bal_id('Undeducted EE '||l_element_name,'_ASG_ER_PTD');
2521 p_balance_value_tab(12).defined_balance_id := get_def_bal_id('Undeducted ER '||l_element_name,'_ASG_ER_PTD');
2522
2523 IF g_debug THEN
2524 hr_utility.trace ('===================== Defined Balances =========================');
2525 END IF;
2526
2527 IF g_debug THEN
2528 hr_utility.trace ('===================== Balance Values =========================');
2529 END IF;
2530
2531 FOR cnt in p_balance_value_tab.first .. p_balance_value_tab.last
2532 LOOP
2533 p_balance_value_tab(cnt).balance_value :=
2534 pay_balance_pkg.get_value
2535 (p_defined_balance_id => p_balance_value_tab(cnt).defined_balance_id
2536 ,p_assignment_action_id => p_assignment_action_id
2537 ,p_tax_unit_id => p_tax_unit_id
2538 ,p_jurisdiction_code => NULL
2539 ,p_source_id => NULL
2540 ,p_source_text => NULL
2541 ,p_tax_group => NULL
2542 ,p_date_earned => NULL
2543 ,p_get_rr_route => NULL
2544 ,p_get_rb_route => 'TRUE');
2545
2546 hr_utility.trace ('. Defined balance ID '||p_balance_value_tab(cnt).defined_balance_id||' has balance value '||p_balance_value_tab(cnt).balance_value);
2547 END LOOP;
2548
2549 IF g_debug THEN
2550 hr_utility.trace ('===================== Balance Values =========================');
2551 END IF;
2552
2553 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 20);
2554
2555 EXCEPTION
2556 WHEN OTHERS THEN
2557 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
2558 hr_utility.trace(l_message);
2559
2560 END get_phf_si_balances;
2561
2562
2563 ----------------------------------------------------------------------------
2564 -- --
2565 -- Name : CALCULATE_CONT_JAN_2006 --
2566 -- Type : Function --
2567 -- Access : Public --
2568 -- Description : Function to process the PHF/SI elements --
2569 -- --
2570 -- Parameters : --
2571 -- IN : --
2572 -- ,p_date_earned DATE --
2573 -- p_phf_si_type VARCHAR2 --
2574 -- ,p_ee_rate NUMBER --
2575 -- ,p_er_rate NUMBER --
2576 -- ,p_ee_rate_type VARCHAR2 --
2577 -- ,p_er_rate_type VARCHAR2 --
2578 -- ,p_ee_cont_base_method VARCHAR2 --
2579 -- ,p_er_cont_base_method VARCHAR2 --
2580 -- ,p_ee_fixed_amount NUMBER --
2581 -- ,p_er_fixed_amount NUMBER --
2582 -- ,p_phf_si_earngs_asg_ptd NUMBER --
2583 -- ,p_phf_si_earngs_asg_pmth NUMBER --
2584 -- ,p_phf_si_earngs_asg_pyear NUMBER --
2585 -- ,p_average_salary NUMBER --
2586 -- ,p_lowest_avg_salary NUMBER --
2587 -- ,p_ee_rate_roundg_method VARCHAR2 --
2588 -- ,p_er_rate_roundg_method VARCHAR2 --
2589 -- ,p_phf_high_lim_exemp VARCHAR2 --
2590 -- ,p_ee_hi_cont_type VARCHAR2 --
2591 -- ,p_er_hi_cont_type VARCHAR2 --
2592 -- ,p_ee_hi_cont_amt NUMBER --
2593 -- ,p_er_hi_cont_amt NUMBER --
2594 -- ,p_ee_hi_cont_base_meth VARCHAR2 --
2595 -- ,p_er_hi_cont_base_meth VARCHAR2 --
2596 -- ,p_ee_hi_cont_base_amount NUMBER --
2597 -- ,p_er_hi_cont_base_amount NUMBER --
2598 -- IN OUT :
2599 -- ,p_ee_phf_si_amount NUMBER --
2600 -- ,p_er_phf_si_amount NUMBER --
2601 -- ,p_ee_cont_base_amount NUMBER --
2602 -- ,p_er_cont_base_amount NUMBER --
2603 -- ,p_ee_taxable_cont NUMBER --
2604 -- ,p_er_taxable_cont NUMBER --
2605 -----------------------------------------------------------------------------
2606
2607 FUNCTION calculate_cont_jan_2006(
2608 p_date_earned IN DATE
2609 ,p_phf_si_type IN VARCHAR2
2610 ,p_ee_rate IN NUMBER
2611 ,p_er_rate IN NUMBER
2612 ,p_ee_rate_type IN VARCHAR2
2613 ,p_er_rate_type IN VARCHAR2
2614 ,p_ee_cont_base_method IN VARCHAR2
2615 ,p_er_cont_base_method IN VARCHAR2
2616 ,p_ee_fixed_amount IN NUMBER
2617 ,p_er_fixed_amount IN NUMBER
2618 ,p_phf_si_earnings_asg_ptd IN NUMBER
2619 ,p_phf_si_earnings_asg_pmth IN NUMBER
2620 ,p_phf_si_earnings_asg_pyear IN NUMBER
2621 ,p_average_salary IN NUMBER
2622 ,p_lowest_avg_salary IN NUMBER
2623 ,p_ee_rate_rounding_method IN VARCHAR2
2624 ,p_er_rate_rounding_method IN VARCHAR2
2625 ,p_ee_hi_cont_type IN VARCHAR2
2626 ,p_er_hi_cont_type IN VARCHAR2
2627 ,p_ee_hi_cont_amt IN NUMBER
2628 ,p_er_hi_cont_amt IN NUMBER
2629 ,p_ee_hi_cont_base_meth IN VARCHAR2
2630 ,p_er_hi_cont_base_meth IN VARCHAR2
2631 ,p_ee_hi_cont_base_amount IN NUMBER
2632 ,p_er_hi_cont_base_amount IN NUMBER
2633 ,p_ee_phf_si_amount IN OUT NOCOPY NUMBER
2634 ,p_er_phf_si_amount IN OUT NOCOPY NUMBER
2635 ,p_ee_taxable_cont IN OUT NOCOPY NUMBER
2636 ,p_er_taxable_cont IN OUT NOCOPY NUMBER
2637 ,p_ee_cont_base_amount IN OUT NOCOPY NUMBER
2638 ,p_er_cont_base_amount IN OUT NOCOPY NUMBER
2639 ,p_ee_tax_thrhld_rate IN NUMBER -- added for bug 6828199
2640 ,p_er_tax_thrhld_rate IN NUMBER -- added for bug 6828199
2641 ,p_tax_thrhld_amount IN NUMBER -- added for bug 6828199
2642 )
2643 RETURN VARCHAR2
2644 IS
2645 CURSOR c_get_global_value(p_global_name IN VARCHAR2)
2646 IS
2647 SELECT fnd_number.canonical_to_number(global_value)
2648 FROM ff_globals_f
2649 WHERE legislation_code= 'CN'
2650 AND global_name = p_global_name
2651 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
2652
2653
2654
2655 l_phf_si_earnings_asg_ptd NUMBER;
2656 l_phf_si_earnings_asg_pyear NUMBER;
2657 l_phf_si_earnings_asg_pmth NUMBER;
2658
2659 -------coming from old logic-----
2660 l_ee_rate NUMBER;
2661 l_er_rate NUMBER;
2662 l_ee_rate_type VARCHAR2(200);
2663 l_er_rate_type VARCHAR2(200);
2664 l_ee_cont_base_method VARCHAR2(200);
2665 l_er_cont_base_method VARCHAR2(200);
2666 l_ee_cont_base_amount NUMBER ;
2667 l_er_cont_base_amount NUMBER ;
2668 l_ee_fixed_amount NUMBER;
2669 l_er_fixed_amount NUMBER;
2670 l_average_salary NUMBER;
2671 l_lowest_avg_salary NUMBER;
2672 l_ee_rate_rounding_method VARCHAR2(200);
2673 l_er_rate_rounding_method VARCHAR2(200);
2674
2675 l_ee_phf_si_amount NUMBER :=0;
2676 l_er_phf_si_amount NUMBER :=0;
2677
2678 l_message VARCHAR2(1000) :='SUCCESS';
2679 initialize_on_error EXCEPTION;
2680
2681
2682 l_ee_hi_cont_type VARCHAR2(200);
2683 l_er_hi_cont_type VARCHAR2(200);
2684 l_ee_hi_cont_amt NUMBER := 0;
2685 l_er_hi_cont_amt NUMBER := 0;
2686 l_ee_hi_cont_base_meth VARCHAR2(200);
2687 l_er_hi_cont_base_meth VARCHAR2(200);
2688 l_ee_hi_cont_base_amount NUMBER := 0;
2689 l_er_hi_cont_base_amount NUMBER := 0;
2690
2691
2692 l_out_ee_hi_cont_base_amount NUMBER := 0;
2693 l_out_er_hi_cont_base_amount NUMBER := 0;
2694 l_ee_stat_fixed_amount NUMBER :=0;
2695 l_er_stat_fixed_amount NUMBER :=0;
2696 l_ee_phf_si_amount_higher NUMBER := 0;
2697 l_er_phf_si_amount_higher NUMBER := 0;
2698 l_ee_taxable_cont NUMBER := 0;
2699 l_er_taxable_cont NUMBER := 0;
2700
2701 l_ee_stat_percentage NUMBER :=0;
2702 l_er_stat_percentage NUMBER :=0;
2703 l_ee_tax_thrhld_rate NUMBER :=0; -- added for bug 6828199
2704 l_er_tax_thrhld_rate NUMBER :=0; -- added for bug 6828199
2705 l_tax_thrhld_amount NUMBER :=0; -- added for bug 6828199
2706
2707
2708
2709 BEGIN
2710
2711
2712 hr_cn_api.set_location(g_debug,' Entering PHF and SI Enhancements started from 1-Jan-2006 : ', 230);
2713
2714
2715 IF g_debug THEN
2716 hr_utility.trace(' =======================================================');
2717 hr_utility.trace('p_phf_si_type '||p_phf_si_type);
2718 hr_utility.trace('p_ee_rate '|| p_ee_rate);
2719 hr_utility.trace('p_er_rate '|| p_er_rate);
2720 hr_utility.trace('p_ee_rate_type '|| p_ee_rate_type);
2721 hr_utility.trace('p_er_rate_type '|| p_er_rate_type);
2722 hr_utility.trace('p_ee_cont_base_method '|| p_ee_cont_base_method);
2723 hr_utility.trace('p_er_cont_base_method '|| p_er_cont_base_method);
2724 hr_utility.trace('p_ee_cont_base_amount '|| p_ee_cont_base_amount);
2725 hr_utility.trace('p_er_cont_base_amount '|| p_er_cont_base_amount);
2726 hr_utility.trace('p_ee_fixed_amount '|| p_ee_fixed_amount);
2727 hr_utility.trace('p_er_fixed_amount '|| p_er_fixed_amount);
2728 hr_utility.trace('p_average_salary '|| p_average_salary);
2729 hr_utility.trace('p_lowest_avg_salary '|| p_lowest_avg_salary);
2730 hr_utility.trace('p_ee_rate_rounding_method '|| p_ee_rate_rounding_method);
2731 hr_utility.trace('p_er_rate_rounding_method '|| p_er_rate_rounding_method);
2732
2733 hr_utility.trace('p_ee_phf_si_amount '|| p_ee_phf_si_amount);
2734 hr_utility.trace('p_er_phf_si_amount '|| p_er_phf_si_amount);
2735
2736 hr_utility.trace('p_phf_si_earnings_asg_ptd '|| p_phf_si_earnings_asg_ptd);
2737 hr_utility.trace('p_phf_si_earnings_asg_pyear '|| p_phf_si_earnings_asg_pyear);
2738 hr_utility.trace('p_phf_si_earnings_asg_pmth '|| p_phf_si_earnings_asg_pmth);
2739
2740 hr_utility.trace('p_ee_hi_cont_type '||p_ee_hi_cont_type );
2741 hr_utility.trace('p_er_hi_cont_type '||p_er_hi_cont_type );
2742 hr_utility.trace('p_ee_hi_cont_amt '||p_ee_hi_cont_amt );
2743 hr_utility.trace('p_er_hi_cont_amt '||p_er_hi_cont_amt );
2744 hr_utility.trace('p_ee_hi_cont_base_meth '||p_ee_hi_cont_base_meth );
2745 hr_utility.trace('p_er_hi_cont_base_meth '||p_er_hi_cont_base_meth );
2746 hr_utility.trace('p_ee_hi_cont_base_amount '||p_ee_hi_cont_base_amount);
2747 hr_utility.trace('p_er_hi_cont_base_amount '||p_er_hi_cont_base_amount);
2748 hr_utility.trace('p_ee_taxable_cont '||p_ee_taxable_cont);
2749 hr_utility.trace('p_er_taxable_cont '||p_er_taxable_cont);
2750 hr_utility.trace('p_date_earned '||p_date_earned);
2751 hr_utility.trace('p_ee_tax_thrhld_rate '||p_ee_tax_thrhld_rate);
2752 hr_utility.trace('p_er_tax_thrhld_rate '||p_er_tax_thrhld_rate);
2753 hr_utility.trace('p_tax_thrhld_amount '||p_tax_thrhld_amount);
2754 hr_utility.trace(' =======================================================');
2755 END IF;
2756 ---------------------------------------------------------------------------------------------
2757 /* Start Bug 5563042 (PHF and SI Enhancements), check for employee level data */
2758 ---------------------------------------------------------------------------------------------
2759
2760 l_ee_rate := p_ee_rate;
2761 l_er_rate := p_er_rate;
2762 l_ee_rate_type := p_ee_rate_type;
2763 l_er_rate_type := p_er_rate_type;
2764 l_ee_cont_base_method := p_ee_cont_base_method;
2765 l_er_cont_base_method := p_er_cont_base_method;
2766 l_ee_cont_base_amount := p_ee_cont_base_amount;
2767 l_er_cont_base_amount := p_er_cont_base_amount;
2768 l_ee_fixed_amount := p_ee_fixed_amount;
2769 l_er_fixed_amount := p_er_fixed_amount;
2770 l_average_salary := p_average_salary;
2771 l_lowest_avg_salary := p_lowest_avg_salary;
2772 l_ee_rate_rounding_method := p_ee_rate_rounding_method;
2773 l_er_rate_rounding_method := p_er_rate_rounding_method;
2774
2775 l_ee_phf_si_amount := p_ee_phf_si_amount;
2776 l_er_phf_si_amount := p_er_phf_si_amount;
2777 l_phf_si_earnings_asg_ptd := p_phf_si_earnings_asg_ptd;
2778 l_phf_si_earnings_asg_pyear := p_phf_si_earnings_asg_pyear;
2779 l_phf_si_earnings_asg_pmth := p_phf_si_earnings_asg_pmth;
2780 l_ee_hi_cont_type := p_ee_hi_cont_type;
2781 l_er_hi_cont_type := p_er_hi_cont_type;
2782 l_ee_hi_cont_amt := p_ee_hi_cont_amt;
2783 l_er_hi_cont_amt := p_er_hi_cont_amt;
2784 l_ee_hi_cont_base_meth := p_ee_hi_cont_base_meth;
2785 l_er_hi_cont_base_meth := p_er_hi_cont_base_meth;
2786 l_ee_hi_cont_base_amount := p_ee_hi_cont_base_amount;
2787 l_er_hi_cont_base_amount := p_er_hi_cont_base_amount;
2788 l_ee_taxable_cont := p_ee_taxable_cont;
2789 l_er_taxable_cont := p_er_taxable_cont;
2790 l_ee_tax_thrhld_rate := p_ee_tax_thrhld_rate;
2791 l_er_tax_thrhld_rate := p_er_tax_thrhld_rate;
2792 l_tax_thrhld_amount := p_tax_thrhld_amount;
2793
2794
2795 IF g_debug THEN
2796 hr_utility.trace(' =======================================================');
2797 hr_utility.trace(' . l_ee_rate : '||l_ee_rate);
2798 hr_utility.trace(' . l_er_rate : '||l_er_rate);
2799 hr_utility.trace(' . l_ee_rate_type : '||l_ee_rate_type);
2800 hr_utility.trace(' . l_er_rate_type : '||l_er_rate_type);
2801 hr_utility.trace(' . l_ee_cont_base_method : '||l_ee_cont_base_method);
2802 hr_utility.trace(' . l_er_cont_base_method : '||l_er_cont_base_method);
2803 hr_utility.trace(' . l_ee_cont_base_amount : '||l_ee_cont_base_amount);
2804 hr_utility.trace(' . l_er_cont_base_amount : '||l_er_cont_base_amount);
2805 hr_utility.trace(' . l_ee_fixed_amount : '||l_ee_fixed_amount);
2806 hr_utility.trace(' . l_er_fixed_amount : '||l_er_fixed_amount);
2807 hr_utility.trace(' . l_average_salary : '||l_average_salary);
2808 hr_utility.trace(' . l_lowest_avg_salary : '||l_lowest_avg_salary);
2809 hr_utility.trace(' . l_ee_rate_rounding_method : '||l_ee_rate_rounding_method);
2810 hr_utility.trace(' . l_er_rate_rounding_method : '||l_er_rate_rounding_method);
2811 hr_utility.trace(' . l_ee_phf_si_amount : '||l_ee_phf_si_amount);
2812 hr_utility.trace(' . l_er_phf_si_amount : '||l_er_phf_si_amount);
2813 hr_utility.trace(' . l_phf_si_earnings_asg_ptd : '||l_phf_si_earnings_asg_ptd);
2814 hr_utility.trace(' . l_phf_si_earnings_asg_pyear : '||l_phf_si_earnings_asg_pyear);
2815 hr_utility.trace(' . l_phf_si_earnings_asg_pmth : '||l_phf_si_earnings_asg_pmth);
2816 hr_utility.trace(' . l_ee_hi_cont_type : '||l_ee_hi_cont_type );
2817 hr_utility.trace(' . l_er_hi_cont_type : '||l_er_hi_cont_type );
2818 hr_utility.trace(' . l_ee_hi_cont_amt : '||l_ee_hi_cont_amt );
2819 hr_utility.trace(' . l_er_hi_cont_amt : '||l_er_hi_cont_amt );
2820 hr_utility.trace(' . l_ee_hi_cont_base_meth : '||l_ee_hi_cont_base_meth );
2821 hr_utility.trace(' . l_er_hi_cont_base_meth : '||l_er_hi_cont_base_meth );
2822 hr_utility.trace(' . l_ee_hi_cont_base_amount : '||l_ee_hi_cont_base_amount);
2823 hr_utility.trace(' . l_er_hi_cont_base_amount : '||l_er_hi_cont_base_amount);
2824 hr_utility.trace(' . l_ee_taxable_cont : '||l_ee_taxable_cont);
2825 hr_utility.trace(' . l_er_taxable_cont : '||l_er_taxable_cont);
2826 hr_utility.trace(' . l_ee_tax_thrhld_rate : '||l_ee_tax_thrhld_rate);
2827 hr_utility.trace(' . l_er_tax_thrhld_rate : '||l_er_tax_thrhld_rate);
2828 hr_utility.trace(' . l_tax_thrhld_amount : '||l_tax_thrhld_amount);
2829 hr_utility.trace(' =======================================================');
2830 END IF;
2831
2832 IF (p_ee_hi_cont_amt = -1) THEN
2833 l_ee_hi_cont_amt := l_ee_rate;
2834 l_ee_hi_cont_type := l_ee_rate_type;
2835 END IF;
2836
2837 IF (p_er_hi_cont_amt = -1) THEN
2838 l_er_hi_cont_amt := l_er_rate;
2839 l_er_hi_cont_type := l_er_rate_type;
2840 END IF;
2841
2842 IF (p_ee_hi_cont_base_meth = 'XX') THEN
2843 l_ee_hi_cont_base_meth := l_ee_cont_base_method;
2844 l_ee_hi_cont_base_amount := nvl(l_ee_fixed_amount,l_ee_cont_base_amount); /* Changed for bug 8838185 */
2845 l_out_ee_hi_cont_base_amount := l_ee_cont_base_amount;
2846 END IF;
2847
2848 IF (p_er_hi_cont_base_meth = 'XX') THEN
2849 l_er_hi_cont_base_meth := l_er_cont_base_method;
2850 l_er_hi_cont_base_amount := nvl(l_er_fixed_amount,l_er_cont_base_amount); /* Changed for bug 8838185 */
2851 l_out_er_hi_cont_base_amount := l_er_cont_base_amount;
2852 END IF;
2853
2854 IF g_debug THEN
2855 hr_utility.trace(' =======================================================');
2856 hr_utility.trace(' . l_ee_hi_cont_type : '||l_ee_hi_cont_type );
2857 hr_utility.trace(' . l_er_hi_cont_type : '||l_er_hi_cont_type );
2858 hr_utility.trace(' . l_ee_hi_cont_amt : '||l_ee_hi_cont_amt );
2859 hr_utility.trace(' . l_er_hi_cont_amt : '||l_er_hi_cont_amt );
2860 hr_utility.trace(' . l_ee_hi_cont_base_meth : '||l_ee_hi_cont_base_meth );
2861 hr_utility.trace(' . l_er_hi_cont_base_meth : '||l_er_hi_cont_base_meth );
2862 hr_utility.trace(' . l_ee_hi_cont_base_amount: '||l_ee_hi_cont_base_amount);
2863 hr_utility.trace(' . l_er_hi_cont_base_amount: '||l_er_hi_cont_base_amount);
2864 hr_utility.trace(' . l_out_ee_hi_cont_base_amount: '||l_out_ee_hi_cont_base_amount);
2865 hr_utility.trace(' . l_out_er_hi_cont_base_amount: '||l_out_er_hi_cont_base_amount);
2866 hr_utility.trace(' =======================================================');
2867 END IF;
2868
2869 IF (l_out_ee_hi_cont_base_amount = 0) THEN
2870 l_message :=
2871 get_cont_base_amount
2872 (p_cont_base_method => l_ee_hi_cont_base_meth
2873 ,p_phf_si_earnings_asg_ptd => l_phf_si_earnings_asg_ptd
2874 ,p_phf_si_earnings_asg_pmth => l_phf_si_earnings_asg_pmth
2875 ,p_phf_si_earnings_asg_avg => l_phf_si_earnings_asg_pyear
2876 ,p_average_salary => l_average_salary
2877 ,p_lowest_average_salary => l_lowest_avg_salary
2878 ,p_fixed_amount => l_ee_hi_cont_base_amount
2879 ,p_cont_base_amount => l_out_ee_hi_cont_base_amount);
2880 /* Changes for bug 8838185 start */
2881 IF l_out_ee_hi_cont_base_amount = -1 THEN
2882 l_out_ee_hi_cont_base_amount := 0;
2883 END IF;
2884 /* Changes for bug 8838185 end */
2885 IF l_out_ee_hi_cont_base_amount = hr_api.g_number AND l_message <> 'SUCCESS' THEN
2886 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 240);
2887 RAISE initialize_on_error;
2888 END IF;
2889
2890 END IF;
2891
2892 IF (l_out_er_hi_cont_base_amount = 0) THEN
2893 l_message :=
2894 get_cont_base_amount
2895 (p_cont_base_method => l_er_hi_cont_base_meth
2896 ,p_phf_si_earnings_asg_ptd => l_phf_si_earnings_asg_ptd
2897 ,p_phf_si_earnings_asg_pmth => l_phf_si_earnings_asg_pmth
2898 ,p_phf_si_earnings_asg_avg => l_phf_si_earnings_asg_pyear
2899 ,p_average_salary => l_average_salary
2900 ,p_lowest_average_salary => l_lowest_avg_salary
2901 ,p_fixed_amount => l_er_hi_cont_base_amount
2902 ,p_cont_base_amount => l_out_er_hi_cont_base_amount);
2903 /* Changes for bug 8838185 start */
2904 IF l_out_er_hi_cont_base_amount = -1 THEN
2905 l_out_er_hi_cont_base_amount := 0;
2906 END IF;
2907 /* Changes for bug 8838185 end */
2908 IF l_out_er_hi_cont_base_amount = hr_api.g_number AND l_message <> 'SUCCESS' THEN
2909 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 250);
2910 RAISE initialize_on_error;
2911 END IF;
2912 END IF;
2913
2914
2915 l_ee_phf_si_amount_higher :=
2916 get_cont_amount
2917 (p_cont_base => l_out_ee_hi_cont_base_amount
2918 ,p_rate_type => l_ee_hi_cont_type
2919 ,p_rate_amount => l_ee_hi_cont_amt
2920 ,p_rounding_method => l_ee_rate_rounding_method
2921 ,p_message => l_message);
2922
2923 hr_cn_api.set_location(g_debug,' l_ee_phf_si_amount_higher : '||l_ee_phf_si_amount_higher, 260);
2924
2925 IF l_message <> 'SUCCESS' THEN
2926 l_ee_phf_si_amount_higher := 0;
2927 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 270);
2928 RETURN l_message;
2929 END IF;
2930
2931 l_er_phf_si_amount_higher :=
2932 get_cont_amount
2933 (p_cont_base => l_out_er_hi_cont_base_amount
2934 ,p_rate_type => l_er_hi_cont_type
2935 ,p_rate_amount => l_er_hi_cont_amt
2936 ,p_rounding_method => l_er_rate_rounding_method
2937 ,p_message => l_message);
2938
2939 hr_cn_api.set_location(g_debug,' l_er_phf_si_amount_higher : '||l_er_phf_si_amount_higher, 280);
2940
2941 IF l_message <> 'SUCCESS' THEN
2942 l_er_phf_si_amount_higher := 0;
2943 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 290);
2944 RETURN l_message;
2945 END IF;
2946
2947
2948 IF g_debug THEN
2949 hr_utility.trace(' =======================================================');
2950 hr_utility.trace(' . p_phf_si_type : '||p_phf_si_type );
2951 hr_utility.trace(' . l_ee_hi_cont_type : '||l_ee_hi_cont_type );
2952 hr_utility.trace(' . l_er_hi_cont_type : '||l_er_hi_cont_type );
2953 hr_utility.trace(' . l_ee_hi_cont_amt : '||l_ee_hi_cont_amt );
2954 hr_utility.trace(' . l_er_hi_cont_amt : '||l_er_hi_cont_amt );
2955 hr_utility.trace(' . l_ee_rate_type : '||l_ee_rate_type );
2956 hr_utility.trace(' . l_er_rate_type : '||l_er_rate_type );
2957 hr_utility.trace(' . l_ee_phf_si_amount_higher: '||l_ee_phf_si_amount_higher);
2958 hr_utility.trace(' . l_er_phf_si_amount_higher: '||l_er_phf_si_amount_higher);
2959 hr_utility.trace(' =======================================================');
2960 END IF;
2961
2962
2963 /* 6828199 - all the 4 phf si types can enter this piece of code now
2964 - Globals EE_PHF_HIGH_LIMIT and ER_PHF_HIGH_LIMIT are not required anymore
2965 */
2966
2967 /* Changes for bug 6828199 starts */
2968 /* Changes for bug 8838185 start */
2969 IF l_ee_tax_thrhld_rate IS NOT NULL THEN
2970 l_ee_stat_percentage := l_ee_tax_thrhld_rate;
2971 ELSE
2972
2973 IF l_ee_hi_cont_type in ('PERCENTAGE','FIXED') THEN
2974 IF l_ee_hi_cont_amt = -1 THEN
2975 l_ee_stat_percentage := l_ee_rate;
2976 ELSE
2977 l_ee_stat_percentage := l_ee_hi_cont_amt;
2978 END IF;
2979 END IF;
2980 END IF;
2981
2982 IF l_er_tax_thrhld_rate IS NOT NULL THEN
2983 l_er_stat_percentage := l_er_tax_thrhld_rate;
2984 ELSE
2985
2986 IF l_er_hi_cont_type in ('PERCENTAGE','FIXED') THEN
2987 IF l_er_hi_cont_amt = -1 THEN
2988 l_er_stat_percentage := l_er_rate;
2989 ELSE
2990 l_er_stat_percentage := l_er_hi_cont_amt;
2991 END IF;
2992 END IF;
2993
2994 END IF;
2995 /* Changes for bug 8838185 end */
2996 /* 7283402 - Use higher cont base amount l_out_ee_hi_cont_base_amount*/
2997 IF l_tax_thrhld_amount IS NOT NULL AND l_out_ee_hi_cont_base_amount > l_tax_thrhld_amount THEN
2998 l_ee_cont_base_amount := l_tax_thrhld_amount ;
2999 END IF;
3000 /* 7283402 - Use higher cont base amount l_out_er_hi_cont_base_amount*/
3001 IF l_tax_thrhld_amount IS NOT NULL AND l_out_er_hi_cont_base_amount > l_tax_thrhld_amount THEN
3002 l_er_cont_base_amount := l_tax_thrhld_amount ;
3003 END IF;
3004 /* Changes for bug 8838185 start */
3005 IF l_tax_thrhld_amount IS NULL THEN
3006 l_ee_cont_base_amount := l_out_ee_hi_cont_base_amount;
3007 l_er_cont_base_amount := l_out_er_hi_cont_base_amount;
3008 END IF;
3009 /* Changes for bug 8838185 end */
3010
3011 /* Changes for bug 6828199 end */
3012
3013 /* 6828199 - Logic for PHF/SI Taxation is driven by Tax Threshold Rates and Threshold base */
3014
3015 IF( l_ee_tax_thrhld_rate IS NOT NULL OR
3016 l_er_tax_thrhld_rate IS NOT NULL OR
3017 l_tax_thrhld_amount IS NOT NULL) THEN
3018 IF (l_ee_hi_cont_type = 'PERCENTAGE') THEN
3019 IF(l_ee_hi_cont_amt > l_ee_stat_percentage) THEN
3020 l_ee_phf_si_amount :=
3021 get_cont_amount
3022 (p_cont_base => l_ee_cont_base_amount
3023 ,p_rate_type => l_ee_rate_type
3024 ,p_rate_amount => l_ee_stat_percentage
3025 ,p_rounding_method => l_ee_rate_rounding_method
3026 ,p_message => l_message);
3027
3028 IF l_message <> 'SUCCESS' THEN
3029 l_ee_phf_si_amount := 0;
3030 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 305);
3031 RETURN l_message;
3032 END IF;
3033
3034 ELSE
3035 l_ee_phf_si_amount :=
3036 get_cont_amount
3037 (p_cont_base => l_ee_cont_base_amount
3038 ,p_rate_type => 'PERCENTAGE'
3039 ,p_rate_amount => l_ee_hi_cont_amt
3040 ,p_rounding_method => l_ee_rate_rounding_method
3041 ,p_message => l_message);
3042
3043 IF l_message <> 'SUCCESS' THEN
3044 l_ee_phf_si_amount := 0;
3045 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 310);
3046 RETURN l_message;
3047 END IF;
3048 END IF;
3049 hr_cn_api.set_location(g_debug,' l_ee_phf_si_amount : '||l_ee_phf_si_amount, 320);
3050 ELSE
3051
3052 IF l_ee_tax_thrhld_rate IS NULL THEN
3053
3054 l_ee_stat_fixed_amount := l_ee_stat_percentage ;
3055
3056 ELSE
3057
3058 l_ee_stat_fixed_amount :=
3059 get_cont_amount
3060 (p_cont_base => l_ee_cont_base_amount
3061 ,p_rate_type => 'PERCENTAGE'
3062 ,p_rate_amount => l_ee_stat_percentage
3063 ,p_rounding_method => l_ee_rate_rounding_method
3064 ,p_message => l_message);
3065
3066 END IF;
3067
3068 IF l_message <> 'SUCCESS' THEN
3069 l_ee_phf_si_amount := 0;
3070 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 330);
3071 RETURN l_message;
3072 END IF;
3073
3074 hr_cn_api.set_location(g_debug,' l_ee_stat_fixed_amount : '||l_ee_stat_fixed_amount, 340);
3075 hr_cn_api.set_location(g_debug,' l_ee_phf_si_amount_higher : '||l_ee_phf_si_amount_higher, 350);
3076
3077 IF (l_ee_phf_si_amount_higher >l_ee_stat_fixed_amount) THEN
3078 l_ee_phf_si_amount := l_ee_stat_fixed_amount;
3079 ELSE
3080 l_ee_phf_si_amount := l_ee_phf_si_amount_higher;
3081 END IF;
3082 hr_cn_api.set_location(g_debug,' l_ee_phf_si_amount : '||l_ee_phf_si_amount, 360);
3083 END IF;
3084
3085 IF (l_er_hi_cont_type = 'PERCENTAGE') THEN
3086 IF(l_er_hi_cont_amt > l_er_stat_percentage) THEN
3087 l_er_phf_si_amount :=
3088 get_cont_amount
3089 (p_cont_base => l_er_cont_base_amount
3090 ,p_rate_type => l_er_rate_type
3091 ,p_rate_amount => l_er_stat_percentage
3092 ,p_rounding_method => l_er_rate_rounding_method
3093 ,p_message => l_message);
3094
3095 IF l_message <> 'SUCCESS' THEN
3096 l_er_phf_si_amount := 0;
3097 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 370);
3098 RETURN l_message;
3099 END IF;
3100 ELSE
3101 l_er_phf_si_amount :=
3102 get_cont_amount
3103 (p_cont_base => l_er_cont_base_amount
3104 ,p_rate_type => 'PERCENTAGE'
3105 ,p_rate_amount => l_er_hi_cont_amt
3106 ,p_rounding_method => l_er_rate_rounding_method
3107 ,p_message => l_message);
3108
3109 IF l_message <> 'SUCCESS' THEN
3110 l_er_phf_si_amount := 0;
3111 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 380);
3112 RETURN l_message;
3113 END IF;
3114 END IF;
3115 hr_cn_api.set_location(g_debug,' l_er_phf_si_amount : '||l_er_phf_si_amount, 390);
3116 ELSE
3117
3118 IF l_er_tax_thrhld_rate IS NULL THEN
3119
3120 l_er_stat_fixed_amount := l_er_stat_percentage ;
3121
3122 ELSE
3123
3124 l_er_stat_fixed_amount :=
3125 get_cont_amount
3126 (p_cont_base => l_er_cont_base_amount
3127 ,p_rate_type => 'PERCENTAGE'
3128 ,p_rate_amount => l_er_stat_percentage
3129 ,p_rounding_method => l_er_rate_rounding_method
3130 ,p_message => l_message);
3131
3132 END IF;
3133
3134 IF l_message <> 'SUCCESS' THEN
3135 l_er_phf_si_amount := 0;
3136 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 400);
3137 RETURN l_message;
3138 END IF;
3139
3140 hr_cn_api.set_location(g_debug,' l_er_stat_fixed_amount : '||l_er_stat_fixed_amount, 410);
3141 hr_cn_api.set_location(g_debug,' l_er_phf_si_amount_higher : '||l_er_phf_si_amount_higher, 420);
3142
3143 IF (l_er_phf_si_amount_higher >l_er_stat_fixed_amount) THEN
3144 l_er_phf_si_amount := l_er_stat_fixed_amount;
3145 ELSE
3146 l_er_phf_si_amount := l_er_phf_si_amount_higher;
3147 END IF;
3148 hr_cn_api.set_location(g_debug,' l_er_phf_si_amount : '||l_er_phf_si_amount, 430);
3149 END IF;
3150
3151 l_ee_taxable_cont := GREATEST(l_ee_phf_si_amount_higher - l_ee_phf_si_amount, 0);
3152 l_er_taxable_cont := GREATEST(l_er_phf_si_amount_higher - l_er_phf_si_amount, 0);
3153 ELSE
3154 l_ee_taxable_cont :=0;
3155 l_er_taxable_cont :=0;
3156
3157 END IF;
3158
3159
3160 hr_cn_api.set_location(g_debug,' l_ee_taxable_cont : '|| l_ee_taxable_cont, 440);
3161 hr_cn_api.set_location(g_debug,' l_er_taxable_cont : '|| l_er_taxable_cont, 450);
3162
3163 p_ee_taxable_cont := l_ee_taxable_cont;
3164 p_er_taxable_cont := l_er_taxable_cont;
3165 p_ee_phf_si_amount := l_ee_phf_si_amount_higher;
3166 p_er_phf_si_amount := l_er_phf_si_amount_higher;
3167 p_ee_cont_base_amount := l_out_ee_hi_cont_base_amount;
3168 p_er_cont_base_amount := l_out_er_hi_cont_base_amount;
3169
3170
3171
3172
3173 IF g_debug THEN
3174 hr_utility.trace(' =======================================================');
3175 hr_utility.trace('p_ee_phf_si_amount '||p_ee_phf_si_amount );
3176 hr_utility.trace('p_er_phf_si_amount '||p_er_phf_si_amount );
3177 hr_utility.trace('p_er_cont_base_amount '||p_er_cont_base_amount );
3178 hr_utility.trace('p_ee_cont_base_amount '||p_ee_cont_base_amount );
3179 hr_utility.trace('p_ee_taxable_cont '||p_ee_taxable_cont );
3180 hr_utility.trace('p_er_taxable_cont '||p_er_taxable_cont );
3181 hr_utility.trace(' =======================================================');
3182 END IF;
3183
3184 hr_cn_api.set_location(g_debug,' Leaving PHF and SI Enhancements started from 1-Jan-2006: ', 460);
3185
3186 ---------------------------------------------------------------------------------------------
3187 /* End Bug 5563042 (PHF and SI Enhancements) check for employee level data */
3188 ---------------------------------------------------------------------------------------------
3189 RETURN 'SUCCESS';
3190
3191 EXCEPTION
3192 WHEN OTHERS THEN
3193 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
3194 RETURN l_message;
3195
3196 END calculate_cont_jan_2006;
3197
3198
3199 ----------------------------------------------------------------------------
3200 -- --
3201 -- Name : CALCULATE_CONTRIBUTION --
3202 -- Type : Function --
3203 -- Access : Public --
3204 -- Description : Function to process the PHF/SI elements --
3205 -- --
3206 -- Parameters : --
3207 -- IN : --
3208 -- p_business_group_id NUMBER --
3209 -- p_element_entry_id NUMBER --
3210 -- p_assignment_action_id NUMBER --
3211 -- p_assignment_id NUMBER --
3212 -- p_date_earned DATE --
3213 -- p_contribution_area VARCHAR2 --
3214 -- p_phf_si_type VARCHAR2 --
3215 -- p_hukuo_type VARCHAR2 --
3216 -- p_employer_id VARCHAR2 --
3217 -- p_pay_proc_period_end_date DATE --
3218 -- Bug 4522945:Extra input parameters added --
3219 -- p_phf_si_earnings_asg_ptd NUMBER --
3220 -- p_phf_si_earnings_asg_pyear NUMBER --
3221 -- p_phf_si_earnings_asg_pmth NUMBER --
3222 -- p_taxable_earnings_asg_er_ptd NUMBER --
3223 -- p_ee_cont_base_asg_ltd NUMBER --
3224 -- p_er_cont_base_asg_ltd NUMBER --
3225 -- p_ee_deductions_asg_er_ptd NUMBER --
3226 -- p_er_deductions_asg_er_ptd NUMBER --
3227 -- p_undeducted_ee_asg_ltd NUMBER --
3228 -- p_undeducted_er_asg_ltd NUMBER --
3229 -- p_undeducted_ee_asg_er_ptd NUMBER --
3230 -- p_undeducted_er_asg_er_ptd NUMBER --
3231 -- Bug 4522945 Changes End --
3232 -- IN/OUT : --
3233 -- p_calculation_date DATE --
3234 -- p_ee_cont_base_amount NUMBER --
3235 -- p_er_cont_base_amount NUMBER --
3236 -- OUT : --
3237 -- p_ee_phf_si_amount NUMBER --
3238 -- p_er_phf_si_amount NUMBER --
3239 -- p_undeducted_ee_phf_si_amount NUMBER --
3240 -- p_undeducted_er_phf_si_amount NUMBER --
3241 -- p_new_ee_cont_base_amount NUMBER --
3242 -- p_new_er_cont_base_amount NUMBER --
3243 -- RETURN : VARCHAR2 --
3244 -- --
3245 -- Change History : --
3246 ----------------------------------------------------------------------------
3247 -- Rev# Date Userid Description --
3248 ----------------------------------------------------------------------------
3249 -- 1.0 04-MAR-04 snekkala Created this function --
3250 -- 1.1 05-Jul-04 sshankar Modified call to get_phf_si_rates to support--
3251 -- Enterprise Annuity processing --
3252 -- 1.2 09-Aug-05 rpalli Modified proc to take phfsi balances --
3253 -- and dbi CN_PAYROLL_RUN_MONTHS_PREV_YEAR as --
3254 -- input parameters, to replace --
3255 -- get_phf_si_balances proc. --
3256 -- 1.3 16-Aug-05 rpalli Removed parameter p_run_months_prev_year --
3257 -- as input to proc(as per review comments). --
3258 -- 1.4 24-Apr-06 rpalli Bug#5171083 - commented out the code for --
3259 -- updating element entry --
3260 -- 1.5 14-Mar-08 dduvvuri Bug 6828199 - Added variables l_tax_thrhld_amount,
3261 -- l_ee_thrhld_rate and l_er_thrhld_rate and used them
3262 -- in certain function calls. Also commented out the
3263 -- function call get_phf_si_high_limit_exempt
3264 -- 1.6 30-sep-2009 dduvvuri Changes done for bug 8838185
3265 -- 1.7 02-Nov-2009 dduvvuri 8838185 - Removed redundancy coding for fetching New Contribution Base Amounts
3266 -- 1.8 17-Aug-2011 prasrang Added code to handle the increase in --
3267 -- precision for EE Rate and ER Rate. --
3268 ----------------------------------------------------------------------------
3269 FUNCTION calculate_contribution(
3270 p_business_group_id IN NUMBER
3271 ,p_element_entry_id IN NUMBER
3272 ,p_assignment_action_id IN NUMBER
3273 ,p_assignment_id IN NUMBER
3274 ,p_date_earned IN DATE
3275 ,p_contribution_area IN VARCHAR2
3276 ,p_phf_si_type IN VARCHAR2
3277 ,p_hukou_type IN VARCHAR2
3278 ,p_employer_id IN VARCHAR2
3279 ,p_pay_proc_period_end_date IN DATE
3280 --
3281 ,p_phf_si_earnings_asg_ptd IN NUMBER
3282 ,p_phf_si_earnings_asg_pyear IN NUMBER
3283 ,p_phf_si_earnings_asg_pmth IN NUMBER
3284 ,p_taxable_earnings_asg_er_ptd IN NUMBER
3285 ,p_ee_cont_base_asg_ltd IN NUMBER
3286 ,p_er_cont_base_asg_ltd IN NUMBER
3287 ,p_ee_deductions_asg_er_ptd IN NUMBER
3288 ,p_er_deductions_asg_er_ptd IN NUMBER
3289 ,p_undeducted_ee_asg_ltd IN NUMBER
3290 ,p_undeducted_er_asg_ltd IN NUMBER
3291 ,p_undeducted_ee_asg_er_ptd IN NUMBER
3292 ,p_undeducted_er_asg_er_ptd IN NUMBER
3293 --
3294 ,p_calculation_date IN OUT NOCOPY DATE
3295 ,p_ee_cont_base_amount IN OUT NOCOPY NUMBER
3296 ,p_er_cont_base_amount IN OUT NOCOPY NUMBER
3297 --
3298 ,p_ee_phf_si_amount OUT NOCOPY NUMBER
3299 ,p_er_phf_si_amount OUT NOCOPY NUMBER
3300 ,p_new_ee_cont_base_amount OUT NOCOPY NUMBER
3301 ,p_new_er_cont_base_amount OUT NOCOPY NUMBER
3302 ,p_undeducted_ee_phf_si_amount OUT NOCOPY NUMBER
3303 ,p_undeducted_er_phf_si_amount OUT NOCOPY NUMBER
3304 ,p_ee_hi_cont_type IN VARCHAR2
3305 ,p_er_hi_cont_type IN VARCHAR2
3306 ,p_ee_hi_cont_amt IN NUMBER
3307 ,p_er_hi_cont_amt IN NUMBER
3308 ,p_ee_hi_cont_base_meth IN VARCHAR2
3309 ,p_er_hi_cont_base_meth IN VARCHAR2
3310 ,p_ee_hi_cont_base_amount IN NUMBER
3311 ,p_er_hi_cont_base_amount IN NUMBER
3312 ,p_ee_taxable_cont OUT NOCOPY NUMBER
3313 ,p_er_taxable_cont OUT NOCOPY NUMBER
3314 ,p_lt_ee_taxable_cont_ptd IN NUMBER
3315 ,p_lt_er_taxable_cont_ptd IN NUMBER
3316 )
3317 RETURN VARCHAR2
3318 IS
3319
3320 CURSOR c_get_global_value(p_global_name IN VARCHAR2)
3321 IS
3322 SELECT fnd_number.canonical_to_number(global_value)
3323 FROM ff_globals_f
3324 WHERE legislation_code = 'CN'
3325 AND global_name = p_global_name
3326 AND p_date_earned BETWEEN effective_start_date AND effective_end_date;
3327
3328
3329 l_ee_stat_percentage NUMBER;
3330 l_er_stat_percentage NUMBER;
3331
3332 g_procedure_name VARCHAR2(50);
3333
3334 l_ee_cont_base_method hr_organization_information.org_information1%type;
3335 l_er_cont_base_method hr_organization_information.org_information1%type;
3336 l_low_limit_method hr_organization_information.org_information1%type;
3337 l_high_limit_method hr_organization_information.org_information1%type;
3338 l_low_limit_amount NUMBER;
3339 l_high_limit_amount NUMBER;
3340 l_tax_thrhld_amount NUMBER; -- for bug 6828199
3341 l_switch_periodicity hr_organization_information.org_information1%type;
3342 l_switch_month hr_organization_information.org_information1%type;
3343 l_base_rounding_method hr_organization_information.org_information1%type;
3344 l_lowest_avg_salary NUMBER;
3345 l_average_salary NUMBER;
3346 l_ee_fixed_amount NUMBER;
3347 l_er_fixed_amount NUMBER;
3348 l_ee_rate_type hr_organization_information.org_information1%type;
3349 l_er_rate_type hr_organization_information.org_information1%type;
3350 l_ee_rate NUMBER;
3351 l_er_rate NUMBER;
3352 l_ee_thrhld_rate NUMBER; -- for bug 6828199
3353 l_er_thrhld_rate NUMBER; -- for bug 6828199
3354 l_ee_rate_rounding_method hr_organization_information.org_information1%type;
3355 l_er_rate_rounding_method hr_organization_information.org_information1%type;
3356 l_defer_deductions hr_organization_information.org_information1%type;
3357 l_deduct_in_probation_expiry hr_organization_information.org_information1%type;
3358
3359 l_message VARCHAR2(1000);
3360 l_recalculate_flag VARCHAR2(1);
3361
3362 l_phf_si_earnings_asg_ptd NUMBER;
3363 l_phf_si_earnings_asg_pyear NUMBER;
3364 l_phf_si_earnings_asg_pmth NUMBER;
3365 l_taxable_earnings_asg_er_ptd NUMBER;
3366 l_ee_cont_base_asg_ltd NUMBER;
3367 l_er_cont_base_asg_ltd NUMBER;
3368 l_ee_deductions_asg_er_ptd NUMBER;
3369 l_er_deductions_asg_er_ptd NUMBER;
3370 l_undeducted_ee_asg_ltd NUMBER;
3371 l_undeducted_er_asg_ltd NUMBER;
3372 l_undeducted_ee_asg_er_ptd NUMBER;
3373 l_undeducted_er_asg_er_ptd NUMBER;
3374
3375 l_ee_cont_base_amount NUMBER :=0;
3376 l_er_cont_base_amount NUMBER :=0;
3377 l_new_ee_cont_base_amount NUMBER :=0;
3378 l_new_er_cont_base_amount NUMBER :=0;
3379 l_ee_phf_si_amount NUMBER :=0;
3380 l_er_phf_si_amount NUMBER :=0;
3381
3382 l_actual_probation_end_date DATE;
3383 l_const_probation_end_date DATE;
3384 l_calculation_date DATE;
3385
3386 initialize_on_error EXCEPTION;
3387
3388
3389 l_ee_hi_cont_base_amount NUMBER := 0;
3390 l_er_hi_cont_base_amount NUMBER := 0;
3391 l_ee_phf_si_amount_higher NUMBER := 0;
3392 l_er_phf_si_amount_higher NUMBER := 0;
3393 l_ee_taxable_cont NUMBER := 0;
3394 l_er_taxable_cont NUMBER := 0;
3395 l_ee_hi_cont_type VARCHAR2(100);
3396 l_er_hi_cont_type VARCHAR2(100);
3397 l_ee_hi_cont_amt NUMBER := 0;
3398 l_er_hi_cont_amt NUMBER := 0;
3399 l_ee_hi_cont_base_meth VARCHAR2(100);
3400 l_er_hi_cont_base_meth VARCHAR2(100);
3401 l_out_ee_hi_cont_base_amount NUMBER := 0;
3402 l_out_er_hi_cont_base_amount NUMBER := 0;
3403 l_phf_high_lim_exemp VARCHAR2(100);
3404 l_ee_stat_fixed_amount NUMBER :=0;
3405 l_er_stat_fixed_amount NUMBER :=0;
3406 l_ee_stat_limit NUMBER :=0;
3407 l_er_stat_limit NUMBER :=0;
3408
3409
3410 BEGIN
3411
3412 g_debug := hr_utility.debug_enabled;
3413 g_procedure_name := g_package_name || 'calculate_contribution';
3414
3415 hr_cn_api.set_location(g_debug, ' Entering: '||g_procedure_name, 10);
3416
3417 l_ee_cont_base_amount := p_ee_cont_base_amount;
3418 l_er_cont_base_amount := p_er_cont_base_amount;
3419 l_calculation_date := p_calculation_date;
3420
3421 /* Changes for bug 8838185 start */
3422 l_ee_hi_cont_type := p_ee_hi_cont_type;
3423 l_er_hi_cont_type := p_er_hi_cont_type;
3424 l_ee_hi_cont_amt := p_ee_hi_cont_amt;
3425 l_er_hi_cont_amt := p_er_hi_cont_amt;
3426 l_ee_hi_cont_base_meth := p_ee_hi_cont_base_meth;
3427 l_er_hi_cont_base_meth := p_er_hi_cont_base_meth;
3428 l_ee_hi_cont_base_amount := p_ee_hi_cont_base_amount;
3429 l_er_hi_cont_base_amount := p_er_hi_cont_base_amount;
3430 p_er_taxable_cont :=0;
3431 p_ee_taxable_cont :=0;
3432
3433 IF ( l_ee_hi_cont_base_meth <> 'XX' OR
3434 l_er_hi_cont_base_meth <> 'XX') THEN
3435 IF l_ee_hi_cont_base_amount = -1 THEN
3436 l_ee_hi_cont_base_amount := 0;
3437 END IF;
3438 IF l_er_hi_cont_base_amount = -1 THEN
3439 l_er_hi_cont_base_amount := 0;
3440 END IF;
3441 IF nvl(l_ee_hi_cont_base_amount,0) <> 0 THEN
3442 l_ee_cont_base_amount := l_ee_hi_cont_base_amount;
3443 END IF;
3444 IF nvl(l_er_hi_cont_base_amount,0) <> 0 THEN
3445 l_er_cont_base_amount := l_er_hi_cont_base_amount;
3446 END IF;
3447 END IF;
3448 /* Changes for bug 8838185 end */
3449
3450 l_message := 'SUCCESS';
3451 --
3452 -- Step I: Get Contribution Base Rates
3453 --
3454
3455 --
3456 -- Bug 3593118
3457 -- Enterprise Annuity
3458 -- Added new parameter p_assignment_id in call to get_phf_si_rates
3459 --
3460 l_message := get_phf_si_rates
3461 (p_assignment_id => p_assignment_id
3462 ,p_business_group_id => p_business_group_id
3463 ,p_contribution_area => p_contribution_area
3464 ,p_phf_si_type => p_phf_si_type
3465 ,p_employer_id => p_employer_id
3466 ,p_hukou_type => p_hukou_type
3467 ,p_effective_date => p_pay_proc_period_end_date
3468 --
3469 ,p_ee_rate_type => l_ee_rate_type
3470 ,p_er_rate_type => l_er_rate_type
3471 ,p_ee_rate => l_ee_rate
3472 ,p_er_rate => l_er_rate
3473 ,p_ee_thrhld_rate => l_ee_thrhld_rate -- added for bug 6828199
3474 ,p_er_thrhld_rate => l_er_thrhld_rate -- added for bug 6828199
3475 ,p_ee_rounding_method => l_ee_rate_rounding_method
3476 ,p_er_rounding_method => l_er_rate_rounding_method
3477 );
3478
3479 hr_utility.trace(' Calculate_contribution : Message=>' || l_message);
3480 IF l_message <> 'SUCCESS'
3481 THEN
3482 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 25);
3483 RAISE initialize_on_error;
3484 END IF;
3485
3486 --
3487 -- Step II: Get Contribution Base Methods
3488 --
3489 l_message := get_cont_base_methods
3490 (p_business_group_id => p_business_group_id
3491 ,p_contribution_area => p_contribution_area
3492 ,p_phf_si_type => p_phf_si_type
3493 ,p_hukou_type => p_hukou_type
3494 ,p_effective_date => p_pay_proc_period_end_date
3495 --
3496 ,p_ee_cont_base_method => l_ee_cont_base_method
3497 ,p_er_cont_base_method => l_er_cont_base_method
3498 ,p_low_limit_method => l_low_limit_method
3499 ,p_low_limit_amount => l_low_limit_amount
3500 ,p_high_limit_method => l_high_limit_method
3501 ,p_high_limit_amount => l_high_limit_amount
3502 ,p_switch_periodicity => l_switch_periodicity
3503 ,p_switch_month => l_switch_month
3504 ,p_rounding_method => l_base_rounding_method
3505 ,p_lowest_avg_salary => l_lowest_avg_salary
3506 ,p_average_salary => l_average_salary
3507 ,p_ee_fixed_amount => l_ee_fixed_amount
3508 ,p_er_fixed_amount => l_er_fixed_amount
3509 ,p_tax_thrhld_amount => l_tax_thrhld_amount -- added for bug 6828199
3510 );
3511
3512 hr_utility.trace(' get_cont_base_method : Message=>' || l_message);
3513
3514 IF l_message <> 'SUCCESS'
3515 THEN
3516 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 45);
3517 RAISE initialize_on_error;
3518 END IF;
3519
3520 --
3521 -- Step III: Calculation of Contribution Base Amounts starts here.
3522 --
3523 --
3524 -- Step 1 : Get the various balance values
3525 --
3526 l_phf_si_earnings_asg_ptd := p_phf_si_earnings_asg_ptd;
3527 l_phf_si_earnings_asg_pyear := p_phf_si_earnings_asg_pyear;
3528 l_phf_si_earnings_asg_pmth := p_phf_si_earnings_asg_pmth;
3529 l_taxable_earnings_asg_er_ptd := p_taxable_earnings_asg_er_ptd;
3530 l_ee_cont_base_asg_ltd := p_ee_cont_base_asg_ltd;
3531 l_er_cont_base_asg_ltd := p_er_cont_base_asg_ltd;
3532 l_ee_deductions_asg_er_ptd := p_ee_deductions_asg_er_ptd;
3533 l_er_deductions_asg_er_ptd := p_er_deductions_asg_er_ptd;
3534 l_undeducted_ee_asg_ltd := p_undeducted_ee_asg_ltd;
3535 l_undeducted_er_asg_ltd := p_undeducted_er_asg_ltd;
3536 l_undeducted_ee_asg_er_ptd := p_undeducted_ee_asg_er_ptd;
3537 l_undeducted_er_asg_er_ptd := p_undeducted_er_asg_er_ptd;
3538 --
3539 -- Step 2 : Check if Recalculation is necessary
3540 --
3541
3542 l_recalculate_flag:= get_recalculate_flag
3543 (p_switch_periodicity => l_switch_periodicity
3544 ,p_switch_month => l_switch_month
3545 ,p_calculation_date => l_calculation_date
3546 ,p_process_date => p_pay_proc_period_end_date);
3547
3548 hr_utility.trace(' l_recalculate_flag : Message=>' || l_recalculate_flag);
3549
3550 IF l_recalculate_flag='N' THEN
3551 --
3552 -- Step 3: Assign the contribution base amounts with corresponding
3553 -- ltd balances if ltd balance is not 0
3554 -- ptd balances if ltd balance is 0
3555 --
3556 /* Changes for bug 8838185 start */
3557 IF l_ee_cont_base_asg_ltd = 0 THEN
3558 if nvl(l_ee_cont_base_amount,0) = 0 THEN
3559 l_ee_cont_base_amount := l_phf_si_earnings_asg_ptd ;
3560 end if;
3561 ELSE
3562 if nvl(l_ee_cont_base_amount,0) = 0 THEN
3563 l_ee_cont_base_amount := l_ee_cont_base_asg_ltd ;
3564 end if;
3565 END IF;
3566
3567 get_in_limit
3568 (p_high_limit_method => l_high_limit_method
3569 ,p_low_limit_method => l_low_limit_method
3570 ,p_high_fixed_amount => l_high_limit_amount
3571 ,p_low_fixed_amount => l_low_limit_amount
3572 ,p_rounding_method => l_base_rounding_method
3573 ,p_average_salary => l_average_salary
3574 ,p_lowest_avg_salary => l_lowest_avg_salary
3575 ,p_amount => l_ee_cont_base_amount
3576 ,p_message => l_message
3577 );
3578
3579 IF l_message <> 'SUCCESS' THEN
3580 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 115);
3581 RAISE initialize_on_error;
3582 END IF;
3583
3584 IF l_er_cont_base_asg_ltd = 0 THEN
3585 if nvl(l_er_cont_base_amount,0) = 0 THEN
3586 l_er_cont_base_amount := l_phf_si_earnings_asg_ptd ;
3587 end if;
3588 ELSE
3589 if nvl(l_er_cont_base_amount,0) = 0 THEN
3590 l_er_cont_base_amount := l_er_cont_base_asg_ltd ;
3591 end if;
3592 END IF;
3593 /* Changes for bug 8838185 end */
3594 get_in_limit
3595 (p_high_limit_method => l_high_limit_method
3596 ,p_low_limit_method => l_low_limit_method
3597 ,p_high_fixed_amount => l_high_limit_amount
3598 ,p_low_fixed_amount => l_low_limit_amount
3599 ,p_rounding_method => l_base_rounding_method
3600 ,p_average_salary => l_average_salary
3601 ,p_lowest_avg_salary => l_lowest_avg_salary
3602 ,p_amount => l_er_cont_base_amount
3603 ,p_message => l_message
3604 );
3605
3606 IF l_message <> 'SUCCESS' THEN
3607 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 155);
3608 RAISE initialize_on_error;
3609 END IF;
3610
3611 l_calculation_date := g_end_date;
3612
3613 ELSE
3614 --
3615 -- Step 4 : Fetch the Contribution Base Amounts
3616 --
3617
3618 IF NVL(l_ee_cont_base_amount,0) = 0 THEN
3619
3620 l_message :=
3621 get_cont_base_amount
3622 (p_cont_base_method => l_ee_cont_base_method
3623 ,p_phf_si_earnings_asg_ptd => l_phf_si_earnings_asg_ptd
3624 ,p_phf_si_earnings_asg_pmth => l_phf_si_earnings_asg_pmth
3625 ,p_phf_si_earnings_asg_avg => l_phf_si_earnings_asg_pyear
3626 ,p_average_salary => l_average_salary
3627 ,p_lowest_average_salary => l_lowest_avg_salary
3628 ,p_fixed_amount => l_ee_fixed_amount
3629 ,p_cont_base_amount => l_ee_cont_base_amount);
3630
3631 IF l_ee_cont_base_amount = hr_api.g_number AND l_message <> 'SUCCESS' THEN
3632 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 105);
3633 RAISE initialize_on_error;
3634 END IF;
3635
3636 END IF;
3637
3638 /* commented out the call to function as it is not required now */
3639 -- l_phf_high_lim_exemp := get_phf_high_limit_exempt(p_employer_id,p_contribution_area);
3640
3641 get_in_limit
3642 (p_high_limit_method => l_high_limit_method
3643 ,p_low_limit_method => l_low_limit_method
3644 ,p_high_fixed_amount => l_high_limit_amount
3645 ,p_low_fixed_amount => l_low_limit_amount
3646 ,p_rounding_method => l_base_rounding_method
3647 ,p_average_salary => l_average_salary
3648 ,p_lowest_avg_salary => l_lowest_avg_salary
3649 ,p_amount => l_ee_cont_base_amount
3650 ,p_message => l_message
3651 );
3652
3653 IF l_message <> 'SUCCESS' THEN
3654 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 115);
3655 RAISE initialize_on_error;
3656 END IF;
3657
3658 IF NVL(l_er_cont_base_amount,0) = 0 THEN
3659
3660 l_message :=
3661 get_cont_base_amount
3662 (p_cont_base_method => l_er_cont_base_method
3663 ,p_phf_si_earnings_asg_ptd => l_phf_si_earnings_asg_ptd
3664 ,p_phf_si_earnings_asg_pmth => l_phf_si_earnings_asg_pmth
3665 ,p_phf_si_earnings_asg_avg => l_phf_si_earnings_asg_pyear
3666 ,p_average_salary => l_average_salary
3667 ,p_lowest_average_salary => l_lowest_avg_salary
3668 ,p_fixed_amount => l_er_fixed_amount
3669 ,p_cont_base_amount => l_er_cont_base_amount);
3670
3671 IF l_er_cont_base_amount = hr_api.g_number AND l_message <> 'SUCCESS' THEN
3672 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 145);
3673 RAISE initialize_on_error;
3674 END IF;
3675
3676 END IF;
3677
3678 get_in_limit
3679 (p_high_limit_method => l_high_limit_method
3680 ,p_low_limit_method => l_low_limit_method
3681 ,p_high_fixed_amount => l_high_limit_amount
3682 ,p_low_fixed_amount => l_low_limit_amount
3683 ,p_rounding_method => l_base_rounding_method
3684 ,p_average_salary => l_average_salary
3685 ,p_lowest_avg_salary => l_lowest_avg_salary
3686 ,p_amount => l_er_cont_base_amount
3687 ,p_message => l_message
3688 );
3689
3690 IF l_message <> 'SUCCESS' THEN
3691 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 155);
3692 RAISE initialize_on_error;
3693 END IF;
3694
3695 --
3696 -- Step 5 : As the contribution bases have been recalculated, call update element entry
3697 --
3698
3699 l_calculation_date := p_pay_proc_period_end_date;
3700
3701 /* l_message:= update_element_entry
3702 (p_business_group_id => p_business_group_id
3703 ,p_element_entry_id => p_element_entry_id
3704 ,p_calculation_date => l_calculation_date
3705 );
3706
3707 IF l_message <> 'SUCCESS'
3708 THEN
3709 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 90);
3710 RAISE initialize_on_error;
3711 END IF; */
3712
3713 END IF;
3714
3715 --
3716 -- Calculation of Contribution Base Amounts ends here.
3717 --
3718 --
3719 -- Calculation of Contribution Amounts starts here.
3720
3721 /* Bug 6828199 - commented out code as it is not required .
3722 Removed the restriction of stat limits for phf cont rates
3723 Globals EE_PHF_HIGH_LIMIT and ER_PHF_HIGH_LIMIT are not required anymore
3724 phf high limit exemption functionality removed from system */
3725
3726 /* IF (p_phf_si_type = 'PHF' AND l_phf_high_lim_exemp = 'N') AND
3727 (p_date_earned >= TO_DATE('01-01-2006','DD-MM-YYYY')) THEN
3728
3729 OPEN c_get_global_value ('EE_PHF_HIGH_LIMIT');
3730 FETCH c_get_global_value INTO l_ee_stat_limit;
3731 CLOSE c_get_global_value;
3732
3733 OPEN c_get_global_value ('ER_PHF_HIGH_LIMIT');
3734 FETCH c_get_global_value INTO l_er_stat_limit;
3735 CLOSE c_get_global_value;
3736
3737 IF (l_ee_rate_type = 'PERCENTAGE') THEN
3738 l_ee_rate := LEAST(l_ee_rate, l_ee_stat_limit);
3739 END IF;
3740
3741 IF (l_er_rate_type = 'PERCENTAGE') THEN
3742 l_er_rate := LEAST(l_er_rate, l_er_stat_limit);
3743 END IF;
3744
3745 END IF;
3746
3747 */
3748 hr_utility.trace(' l_ee_rate =>' || l_ee_rate);
3749 hr_utility.trace(' l_er_rate =>' || l_er_rate);
3750
3751 l_ee_phf_si_amount :=
3752 get_cont_amount
3753 (p_cont_base => l_ee_cont_base_amount
3754 ,p_rate_type => l_ee_rate_type
3755 ,p_rate_amount => l_ee_rate
3756 ,p_rounding_method => l_ee_rate_rounding_method
3757 ,p_message => l_message);
3758
3759
3760
3761 IF l_message <> 'SUCCESS' THEN
3762 l_ee_phf_si_amount := 0;
3763 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 210);
3764 RETURN l_message;
3765 END IF;
3766
3767 IF g_debug THEN
3768 hr_utility.trace(' =======================================================');
3769 hr_utility.trace(' . l_er_cont_base_amount : '||l_er_cont_base_amount );
3770 hr_utility.trace(' . l_er_rate_type : '||l_er_rate_type );
3771 hr_utility.trace(' . l_er_rate : '||l_er_rate );
3772 hr_utility.trace(' . l_er_rate_rounding_method : '||l_er_rate_rounding_method );
3773 hr_utility.trace(' =======================================================');
3774 END IF;
3775
3776 l_er_phf_si_amount :=
3777 get_cont_amount
3778 (p_cont_base => l_er_cont_base_amount
3779 ,p_rate_type => l_er_rate_type
3780 ,p_rate_amount => l_er_rate
3781 ,p_rounding_method => l_er_rate_rounding_method
3782 ,p_message => l_message);
3783
3784 IF l_message <> 'SUCCESS' THEN
3785 l_er_phf_si_amount := 0;
3786 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 220);
3787 RETURN l_message;
3788 END IF;
3789
3790
3791
3792 IF g_debug THEN
3793 hr_utility.trace(' =======================================================');
3794 hr_utility.trace(' . l_ee_rate : '||l_ee_rate);
3795 hr_utility.trace(' . l_er_rate : '||l_er_rate);
3796 hr_utility.trace(' . l_ee_rate_type : '||l_ee_rate_type);
3797 hr_utility.trace(' . l_er_rate_type : '||l_er_rate_type);
3798 hr_utility.trace(' . l_ee_cont_base_method : '||l_ee_cont_base_method);
3799 hr_utility.trace(' . l_er_cont_base_method : '||l_er_cont_base_method);
3800 hr_utility.trace(' . l_ee_cont_base_amount : '||l_ee_cont_base_amount);
3801 hr_utility.trace(' . l_er_cont_base_amount : '||l_er_cont_base_amount);
3802 hr_utility.trace(' . l_ee_fixed_amount : '||l_ee_fixed_amount);
3803 hr_utility.trace(' . l_er_fixed_amount : '||l_er_fixed_amount);
3804 hr_utility.trace(' . l_average_salary : '||l_average_salary);
3805 hr_utility.trace(' . l_lowest_avg_salary : '||l_lowest_avg_salary);
3806 hr_utility.trace(' . l_ee_rate_rounding_method : '||l_ee_rate_rounding_method);
3807 hr_utility.trace(' . l_er_rate_rounding_method : '||l_er_rate_rounding_method);
3808 hr_utility.trace(' . l_phf_high_lim_exemp : '||l_phf_high_lim_exemp);
3809 hr_utility.trace(' . l_ee_phf_si_amount : '||l_ee_phf_si_amount);
3810 hr_utility.trace(' . l_er_phf_si_amount : '||l_er_phf_si_amount);
3811
3812 hr_utility.trace(' . l_phf_si_earnings_asg_ptd : '||l_phf_si_earnings_asg_ptd);
3813 hr_utility.trace(' . l_phf_si_earnings_asg_pyear : '||l_phf_si_earnings_asg_pyear);
3814 hr_utility.trace(' . l_phf_si_earnings_asg_pmth : '||l_phf_si_earnings_asg_pmth);
3815
3816 hr_utility.trace(' . l_ee_hi_cont_type : '||l_ee_hi_cont_type );
3817 hr_utility.trace(' . l_er_hi_cont_type : '||l_er_hi_cont_type );
3818 hr_utility.trace(' . l_ee_hi_cont_amt : '||l_ee_hi_cont_amt );
3819 hr_utility.trace(' . l_er_hi_cont_amt : '||l_er_hi_cont_amt );
3820 hr_utility.trace(' . l_ee_hi_cont_base_meth : '||l_ee_hi_cont_base_meth );
3821 hr_utility.trace(' . l_er_hi_cont_base_meth : '||l_er_hi_cont_base_meth );
3822 hr_utility.trace(' . l_ee_hi_cont_base_amount : '||l_ee_hi_cont_base_amount);
3823 hr_utility.trace(' . l_er_hi_cont_base_amount : '||l_er_hi_cont_base_amount);
3824 hr_utility.trace(' . l_ee_taxable_cont : '||l_ee_taxable_cont);
3825 hr_utility.trace(' . l_er_taxable_cont : '||l_er_taxable_cont);
3826 hr_utility.trace(' . l_ee_thrhld_rate : '||l_ee_thrhld_rate);
3827 hr_utility.trace(' . l_er_thrhld_rate : '||l_er_thrhld_rate);
3828 hr_utility.trace(' . l_tax_thrhld_amount : '||l_tax_thrhld_amount);
3829 hr_utility.trace(' =======================================================');
3830 END IF;
3831
3832
3833
3834 ---------------------------------------------------------------------------------------------
3835 /* Start Bug 5563042 (PHF and SI Enhancements), started from 1-Jan-2006 */
3836 ---------------------------------------------------------------------------------------------
3837 IF (p_date_earned >= TO_DATE('01-01-2006','DD-MM-YYYY')) THEN
3838 IF (p_phf_si_type IN ('PHF','PENSION','MEDICAL','UNEMPLOYMENT')) THEN
3839 l_ee_hi_cont_type := p_ee_hi_cont_type;
3840 l_er_hi_cont_type := p_er_hi_cont_type;
3841 l_ee_hi_cont_amt := p_ee_hi_cont_amt;
3842 l_er_hi_cont_amt := p_er_hi_cont_amt;
3843 l_ee_hi_cont_base_meth := p_ee_hi_cont_base_meth;
3844 l_er_hi_cont_base_meth := p_er_hi_cont_base_meth;
3845 l_ee_hi_cont_base_amount := p_ee_hi_cont_base_amount;
3846 l_er_hi_cont_base_amount := p_er_hi_cont_base_amount;
3847
3848 /* Changes for bug 10073610 */
3849
3850 IF l_ee_hi_cont_type='FIXED' THEN
3851 IF nvl(l_ee_hi_cont_amt,0) <> 0 THEN
3852 l_ee_hi_cont_amt := round(l_ee_hi_cont_amt,2);
3853 END IF;
3854 END IF;
3855
3856 IF l_er_hi_cont_type='FIXED' THEN
3857 IF nvl(l_er_hi_cont_amt,0) <> 0 THEN
3858 l_er_hi_cont_amt := round(l_er_hi_cont_amt,2);
3859 END IF;
3860 END IF;
3861
3862 /* removed the parameter p_phf_high_lim_exemp as it is not required now */
3863 l_message:=
3864 calculate_cont_jan_2006(
3865 p_date_earned => p_date_earned
3866 ,p_phf_si_type => p_phf_si_type
3867 ,p_ee_rate => l_ee_rate
3868 ,p_er_rate => l_er_rate
3869 ,p_ee_rate_type => l_ee_rate_type
3870 ,p_er_rate_type => l_er_rate_type
3871 ,p_ee_cont_base_method => l_ee_cont_base_method
3872 ,p_er_cont_base_method => l_er_cont_base_method
3873 ,p_ee_fixed_amount => l_ee_fixed_amount
3874 ,p_er_fixed_amount => l_er_fixed_amount
3875 ,p_phf_si_earnings_asg_ptd => l_phf_si_earnings_asg_ptd
3876 ,p_phf_si_earnings_asg_pmth => l_phf_si_earnings_asg_pmth
3877 ,p_phf_si_earnings_asg_pyear => l_phf_si_earnings_asg_pyear
3878 ,p_average_salary => l_average_salary
3879 ,p_lowest_avg_salary => l_lowest_avg_salary
3880 ,p_ee_rate_rounding_method => l_ee_rate_rounding_method
3881 ,p_er_rate_rounding_method => l_er_rate_rounding_method
3882 ,p_ee_hi_cont_type => l_ee_hi_cont_type
3883 ,p_er_hi_cont_type => l_er_hi_cont_type
3884 ,p_ee_hi_cont_amt => l_ee_hi_cont_amt
3885 ,p_er_hi_cont_amt => l_er_hi_cont_amt
3886 ,p_ee_hi_cont_base_meth => l_ee_hi_cont_base_meth
3887 ,p_er_hi_cont_base_meth => l_er_hi_cont_base_meth
3888 ,p_ee_hi_cont_base_amount => l_ee_hi_cont_base_amount
3889 ,p_er_hi_cont_base_amount => l_er_hi_cont_base_amount
3890 ,p_ee_phf_si_amount => l_ee_phf_si_amount
3891 ,p_er_phf_si_amount => l_er_phf_si_amount
3892 ,p_ee_taxable_cont => l_ee_taxable_cont
3893 ,p_er_taxable_cont => l_er_taxable_cont
3894 ,p_ee_cont_base_amount => l_ee_cont_base_amount
3895 ,p_er_cont_base_amount => l_er_cont_base_amount
3896 ,p_ee_tax_thrhld_rate => l_ee_thrhld_rate -- added for bug 6828199
3897 ,p_er_tax_thrhld_rate => l_er_thrhld_rate -- added for bug 6828199
3898 ,p_tax_thrhld_amount => l_tax_thrhld_amount -- added for bug 6828199
3899
3900 );
3901
3902 IF l_message <> 'SUCCESS' THEN
3903 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 220);
3904 RETURN l_message;
3905 END IF;
3906 END IF;
3907 END IF;
3908
3909 /* Changes for bug 8838185 start */
3910 l_new_ee_cont_base_amount := l_ee_cont_base_amount - l_ee_cont_base_asg_ltd;
3911 l_new_er_cont_base_amount := l_er_cont_base_amount - l_er_cont_base_asg_ltd;
3912 /* Changes for bug 8838185 end */
3913
3914 ---------------------------------------------------------------------------------------------
3915 /* End Bug 5563042 (PHF and SI Enhancements) */
3916 ---------------------------------------------------------------------------------------------
3917
3918 --
3919 -- Verification of Deferred Amounts starts here
3920 --
3921
3922 --
3923 -- Step 1: Get the various DBIs
3924 --
3925 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
3926 pay_balance_pkg.set_context('ASSIGNMENT_ID',p_assignment_id);
3927
3928 l_actual_probation_end_date:= fnd_date.canonical_to_date(
3929 pay_balance_pkg.run_db_item('CTR_CN_ACTUAL_PROBATION_END_DATE',p_business_group_id,'CN'));
3930
3931 l_const_probation_end_date:= fnd_date.canonical_to_date(
3932 pay_balance_pkg.run_db_item('CTR_CN_CONST_PROBATION_END_DATE',p_business_group_id,'CN'));
3933
3934 l_defer_deductions := pay_balance_pkg.run_db_item('PER_EMPLOYER_INFO_CN_ORG_DEFER_DEDUCTIONS',p_business_group_id,'CN');
3935
3936 l_deduct_in_probation_expiry := pay_balance_pkg.run_db_item('PER_EMPLOYER_INFO_CN_ORG_DEDUCT_IN_PROBATION_EXPIRY',p_business_group_id,'CN');
3937
3938 l_message := get_phf_si_deferred_amounts
3939 (p_pay_proc_period_end_date => p_pay_proc_period_end_date
3940 ,p_actual_probation_end_date => NVL(l_actual_probation_end_date,g_start_date)
3941 ,p_const_probation_end_date => NVL(l_const_probation_end_date,g_start_date)
3942 ,p_defer_deductions => NVL(l_defer_deductions,'N')
3943 ,p_deduct_in_probation_expiry => NVL(l_deduct_in_probation_expiry,'Y')
3944 ,p_taxable_earnings_asg_er_ptd => l_taxable_earnings_asg_er_ptd
3945 --
3946 ,p_ee_phf_si_amount => l_ee_phf_si_amount
3947 ,p_er_phf_si_amount => l_er_phf_si_amount
3948 ,p_undeducted_ee_phf_ltd => l_undeducted_ee_asg_ltd
3949 ,p_undeducted_er_phf_ltd => l_undeducted_er_asg_ltd
3950 );
3951
3952 IF l_message <> 'SUCCESS' THEN
3953 l_ee_phf_si_amount := 0;
3954 hr_cn_api.set_location(g_debug,' Leaving : '||g_procedure_name, 285);
3955 RETURN l_message;
3956 END IF;
3957
3958 --
3959 -- Verification of Deferred Amounts ends here
3960 --
3961
3962 --
3963 -- In case there is a previous run, subtract the PHF/SI amounts if any
3964 --
3965 IF g_debug THEN
3966 hr_utility.trace(' ====================================================================');
3967 hr_utility.trace(' EE PHF SI Amount (RUN) : '|| l_ee_phf_si_amount);
3968 hr_utility.trace(' ER PHF SI Amount (RUN) : '|| l_er_phf_si_amount);
3969 hr_utility.trace(' EE PHF SI Amount (PTD) : '|| l_ee_deductions_asg_er_ptd);
3970 hr_utility.trace(' ER PHF SI Amount (PTD) : '|| l_er_deductions_asg_er_ptd);
3971 hr_utility.trace(' Undeducted EE PHF SI Amount (PTD) : '|| l_undeducted_ee_asg_er_ptd);
3972 hr_utility.trace(' Undeducted ER PHF SI Amount (PTD) : '|| l_undeducted_er_asg_er_ptd);
3973 END IF;
3974
3975 l_ee_phf_si_amount:= l_ee_phf_si_amount - l_ee_deductions_asg_er_ptd - l_undeducted_ee_asg_er_ptd;
3976 l_er_phf_si_amount:= l_er_phf_si_amount - l_er_deductions_asg_er_ptd - l_undeducted_er_asg_er_ptd;
3977 l_ee_taxable_cont := l_ee_taxable_cont - p_lt_ee_taxable_cont_ptd;
3978 l_er_taxable_cont := l_er_taxable_cont - p_lt_er_taxable_cont_ptd;
3979
3980 IF g_debug THEN
3981 hr_utility.trace(' EE PHF SI Amount (Final) : '|| l_ee_phf_si_amount);
3982 hr_utility.trace(' ER PHF SI Amount (Final) : '|| l_er_phf_si_amount);
3983 hr_utility.trace(' ====================================================================');
3984 END IF;
3985 --
3986
3987 -- Finally set all the output variables
3988 --
3989 p_ee_phf_si_amount := l_ee_phf_si_amount;
3990 p_er_phf_si_amount := l_er_phf_si_amount;
3991 p_undeducted_ee_phf_si_amount := l_undeducted_ee_asg_ltd;
3992 p_undeducted_er_phf_si_amount := l_undeducted_er_asg_ltd;
3993 p_new_ee_cont_base_amount := l_new_ee_cont_base_amount;
3994 p_new_er_cont_base_amount := l_new_er_cont_base_amount;
3995 p_ee_cont_base_amount := l_ee_cont_base_amount;
3996 p_er_cont_base_amount := l_er_cont_base_amount;
3997 p_calculation_date := l_calculation_date;
3998 p_ee_taxable_cont := l_ee_taxable_cont;
3999 p_er_taxable_cont := l_er_taxable_cont;
4000
4001 IF g_debug THEN
4002 hr_utility.trace(' ====================================================================');
4003 hr_utility.trace(' Calculation Date : '|| p_calculation_date);
4004 hr_utility.trace(' Employee PHF SI Amount : '|| p_ee_phf_si_amount);
4005 hr_utility.trace(' Employer PHF SI Amount : '|| p_er_phf_si_amount);
4006 hr_utility.trace(' Undeducted Employee PHF SI Amount : '|| p_undeducted_ee_phf_si_amount);
4007 hr_utility.trace(' Undeducted Employer PHF SI Amount : '|| p_undeducted_er_phf_si_amount);
4008 hr_utility.trace(' Employee new cont base Amount : '|| p_new_ee_cont_base_amount);
4009 hr_utility.trace(' Employer new cont base Amount : '|| p_new_er_cont_base_amount);
4010 hr_utility.trace(' Employee cont base Amount : '|| p_ee_cont_base_amount);
4011 hr_utility.trace(' Employer cont base Amount : '|| p_er_cont_base_amount);
4012 hr_utility.trace(' =====================================================================');
4013 END IF;
4014
4015 hr_cn_api.set_location(g_debug,' Leaving ' || g_procedure_name,300);
4016 RETURN l_message;
4017
4018
4019 EXCEPTION
4020 WHEN initialize_on_error THEN
4021 p_calculation_date := g_end_date;
4022 p_ee_phf_si_amount := NULL;
4023 p_er_phf_si_amount := NULL;
4024 p_new_ee_cont_base_amount := NULL;
4025 p_new_er_cont_base_amount := NULL;
4026 p_undeducted_ee_phf_si_amount := NULL;
4027 p_undeducted_er_phf_si_amount := NULL;
4028 RETURN l_message;
4029 WHEN OTHERS THEN
4030 p_calculation_date := g_end_date;
4031 p_ee_phf_si_amount := NULL;
4032 p_er_phf_si_amount := NULL;
4033 p_new_ee_cont_base_amount := NULL;
4034 p_new_er_cont_base_amount := NULL;
4035 p_undeducted_ee_phf_si_amount := NULL;
4036 p_undeducted_er_phf_si_amount := NULL;
4037 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
4038 RETURN l_message;
4039 END calculate_contribution;
4040
4041 --------------------------------------------------------------------------
4042 -- --
4043 -- Name : CHECK_CONT_BASE_SETUP --
4044 -- Type : Procedure --
4045 -- Access : Public --
4046 -- Description : Procedure to Check the Contribution Base Setup --
4047 -- --
4048 -- Parameters : --
4049 -- IN : p_organization_id NUMBER --
4050 -- p_contribution_area VARCHAR2 --
4051 -- p_phf_si_type VARCHAR2 --
4052 -- p_hukou_type VARCHAR2 --
4053 -- p_ee_cont_base_method VARCHAR2 --
4054 -- p_er_cont_base_method VARCHAR2 --
4055 -- p_low_limit_method VARCHAR2 --
4056 -- p_low_limit_amount NUMBER --
4057 -- p_high_limit_method VARCHAR2 --
4058 -- p_high_limit_amount NUMBER --
4059 -- p_switch_periodicity VARCHAR2 --
4060 -- p_switch_month VARCHAR2 --
4061 -- p_rounding_method VARCHAR2 --
4062 -- p_lowest_avg_salary NUMBER --
4063 -- p_average_salary NUMBER --
4064 -- p_ee_fixed_amount NUMBER --
4065 -- p_er_fixed_amount NUMBER --
4066 -- p_effective_start_date DATE --
4067 -- p_effective_end_date DATE --
4068 -- IN/ OUT : --
4069 -- OUT : p_message_name NOCOPY VARCHAR2 --
4070 -- p_token_name NOCOPY hr_cn_api.char_tab_type --
4071 -- p_token_value NOCOPY hr_cn_api.char_tab_type --
4072 -- --
4073 -- Change History : --
4074 --------------------------------------------------------------------------
4075 -- Rev# Date Userid Description --
4076 --------------------------------------------------------------------------
4077 -- 1.0 15-MAR-05 snekkala Created this Procedure --
4078 -- 1.1 28-Apr-05 snekkala Modified validation for er cont base --
4079 -- method from ee to er --
4080 --------------------------------------------------------------------------
4081 PROCEDURE check_cont_base_setup
4082 (p_organization_id IN NUMBER
4083 ,p_contribution_area IN VARCHAR2
4084 ,p_phf_si_type IN VARCHAR2
4085 ,p_hukou_type IN VARCHAR2
4086 ,p_ee_cont_base_method IN VARCHAR2
4087 ,p_er_cont_base_method IN VARCHAR2
4088 ,p_low_limit_method IN VARCHAR2
4089 ,p_low_limit_amount IN NUMBER
4090 ,p_high_limit_method IN VARCHAR2
4091 ,p_high_limit_amount IN NUMBER
4092 ,p_switch_periodicity IN VARCHAR2
4093 ,p_switch_month IN VARCHAR2
4094 ,p_rounding_method IN VARCHAR2
4095 ,p_lowest_avg_salary IN NUMBER
4096 ,p_average_salary IN NUMBER
4097 ,p_ee_fixed_amount IN NUMBER
4098 ,p_er_fixed_amount IN NUMBER
4099 ,p_effective_start_date IN DATE
4100 ,p_effective_end_date IN DATE
4101 ,p_message_name OUT NOCOPY VARCHAR2
4102 ,p_token_name OUT NOCOPY hr_cn_api.char_tab_type
4103 ,p_token_value OUT NOCOPY hr_cn_api.char_tab_type
4104 )
4105 AS
4106
4107 l_flag BOOLEAN;
4108 l_return_segment VARCHAR2(30);
4109 l_indep_seg fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
4110 l_dep_seg fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
4111 l_lookup_meaning hr_lookups.meaning%TYPE;
4112 l_lookup_meaning_dep hr_lookups.meaning%TYPE;
4113
4114
4115 BEGIN
4116
4117 p_message_name := 'HR_374615_INCOMPLETE_CONT_BASE';
4118 p_token_name(1) := 'INDEP_SEG';
4119 p_token_name(2) := 'INDEP_VALUE';
4120 p_token_name(3) := 'DEP_SEG';
4121 p_token_name(4) := 'DEP_VALUE';
4122
4123 l_flag := validate_switch_with_cont_base
4124 (p_cont_base_method => p_ee_cont_base_method
4125 ,p_switch_periodicity => p_switch_periodicity
4126 );
4127
4128 IF NOT l_flag THEN
4129
4130 hr_utility.trace('Invalid Contribution Base Setup');
4131
4132 l_indep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'Switch Period Periodicity'
4133 ,p_dff => 'Org Developer DF'
4134 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4135 );
4136
4137 l_dep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'EE Cont Base Method'
4138 ,p_dff => 'Org Developer DF'
4139 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4140 );
4141
4142 l_lookup_meaning := hr_general.decode_lookup(p_lookup_type => 'CN_SWITCH_PERIODICITY'
4143 ,p_lookup_code => p_switch_periodicity
4144 );
4145
4146 l_lookup_meaning_dep := hr_general.decode_lookup(p_lookup_type => 'CN_CONT_BASE_CALC_METHOD'
4147 ,p_lookup_code => p_ee_cont_base_method
4148 );
4149
4150 p_token_value(1) := l_indep_seg;
4151 p_token_value(2) := l_lookup_meaning;
4152 p_token_value(3) := l_dep_seg;
4153 p_token_value(4) := l_lookup_meaning_dep;
4154
4155 RETURN;
4156
4157 END IF;
4158
4159 l_flag := validate_cont_base_method(p_cont_base_method => p_ee_cont_base_method
4160 ,p_fixed_amount => p_ee_fixed_amount
4161 ,p_lowest_avg_salary => p_lowest_avg_salary
4162 ,p_average_salary => p_average_salary
4163 ,p_return_segment => l_return_segment
4164 );
4165
4166 IF NOT l_flag THEN
4167
4168 l_indep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'EE Cont Base Method'
4169 ,p_dff => 'Org Developer DF'
4170 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4171 );
4172
4173 IF l_return_segment = 'Fixed Amount' THEN
4174 l_return_segment := 'EE Fixed Amount';
4175 END IF;
4176
4177 l_dep_seg := hr_cn_api.get_dff_tl_value(p_column_name => l_return_segment
4178 ,p_dff => 'Org Developer DF'
4179 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4180 );
4181
4182 l_lookup_meaning := hr_general.decode_lookup(p_lookup_type => 'CN_CONT_BASE_CALC_METHOD'
4183 ,p_lookup_code => p_ee_cont_base_method
4184 );
4185
4186 l_lookup_meaning_dep := 'NULL';
4187
4188 p_token_value(1) := l_indep_seg;
4189 p_token_value(2) := l_lookup_meaning;
4190 p_token_value(3) := l_dep_seg;
4191 p_token_value(4) := l_lookup_meaning_dep;
4192
4193 RETURN;
4194
4195 END IF;
4196
4197 l_flag := validate_switch_with_cont_base
4198 (p_cont_base_method => p_er_cont_base_method
4199 ,p_switch_periodicity => p_switch_periodicity
4200 );
4201
4202 IF NOT l_flag THEN
4203
4204 l_indep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'Switch Period Periodicity'
4205 ,p_dff => 'Org Developer DF'
4206 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4207 );
4208
4209 l_dep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'ER Cont Base Method'
4210 ,p_dff => 'Org Developer DF'
4211 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4212 );
4213
4214 l_lookup_meaning := hr_general.decode_lookup(p_lookup_type => 'CN_SWITCH_PERIODICITY'
4215 ,p_lookup_code => p_switch_periodicity
4216 );
4217
4218 l_lookup_meaning_dep := hr_general.decode_lookup(p_lookup_type => 'CN_CONT_BASE_CALC_METHOD'
4219 ,p_lookup_code => p_er_cont_base_method
4220 );
4221
4222 p_token_value(1) := l_indep_seg;
4223 p_token_value(2) := l_lookup_meaning;
4224 p_token_value(3) := l_dep_seg;
4225 p_token_value(4) := l_lookup_meaning_dep;
4226
4227 RETURN;
4228
4229 END IF;
4230
4231 l_flag := validate_cont_base_method(p_cont_base_method => p_er_cont_base_method
4232 ,p_fixed_amount => p_er_fixed_amount
4233 ,p_lowest_avg_salary => p_lowest_avg_salary
4234 ,p_average_salary => p_average_salary
4235 ,p_return_segment => l_return_segment
4236 );
4237
4238 IF NOT l_flag THEN
4239
4240 l_indep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'ER Cont Base Method'
4241 ,p_dff => 'Org Developer DF'
4242 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4243 );
4244
4245 IF l_return_segment = 'Fixed Amount' THEN
4246 l_return_segment := 'EE Fixed Amount';
4247 END IF;
4248
4249 l_dep_seg := hr_cn_api.get_dff_tl_value(p_column_name => l_return_segment
4250 ,p_dff => 'Org Developer DF'
4251 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4252 );
4253
4254 l_lookup_meaning := hr_general.decode_lookup(p_lookup_type => 'CN_CONT_BASE_CALC_METHOD'
4255 ,p_lookup_code => p_er_cont_base_method
4256 );
4257
4258 l_lookup_meaning_dep := 'NULL';
4259
4260 p_token_value(1) := l_indep_seg;
4261 p_token_value(2) := l_lookup_meaning;
4262 p_token_value(3) := l_dep_seg;
4263 p_token_value(4) := l_lookup_meaning_dep;
4264
4265 RETURN;
4266
4267 END IF;
4268
4269 IF p_low_limit_method <> 'N/A' THEN
4270
4271
4272 l_flag := validate_low_limit_method(p_low_limit_method => p_low_limit_method
4273 ,p_fixed_amount => p_low_limit_amount
4274 ,p_lowest_avg_salary => p_lowest_avg_salary
4275 ,p_average_salary => p_average_salary
4276 ,p_return_segment => l_return_segment
4277 );
4278
4279 IF NOT l_flag THEN
4280
4281
4282 l_indep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'Low Limit Method'
4283 ,p_dff => 'Org Developer DF'
4284 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4285 );
4286
4287 l_dep_seg := hr_cn_api.get_dff_tl_value(p_column_name => l_return_segment
4288 ,p_dff => 'Org Developer DF'
4289 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4290 );
4291
4292 l_lookup_meaning := hr_general.decode_lookup(p_lookup_type => 'CN_CONT_BASE_LOW_LIMIT'
4293 ,p_lookup_code => p_low_limit_method
4294 );
4295
4296 l_lookup_meaning_dep := 'NULL';
4297
4298 p_token_value(1) := l_indep_seg;
4299 p_token_value(2) := l_lookup_meaning;
4300 p_token_value(3) := l_dep_seg;
4301 p_token_value(4) := l_lookup_meaning_dep;
4302
4303 RETURN;
4304
4305 END IF;
4306
4307 END IF;
4308
4309 IF p_high_limit_method <> 'N/A' THEN
4310
4311
4312 l_flag := validate_high_limit_method(p_high_limit_method => p_high_limit_method
4313 ,p_fixed_amount => p_high_limit_amount
4314 ,p_average_salary => p_average_salary
4315 ,p_return_segment => l_return_segment
4316 );
4317
4318 IF NOT l_flag THEN
4319
4320 l_indep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'High Limit Method'
4321 ,p_dff => 'Org Developer DF'
4322 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4323 );
4324
4325 l_dep_seg := hr_cn_api.get_dff_tl_value(p_column_name => l_return_segment
4326 ,p_dff => 'Org Developer DF'
4327 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4328 );
4329
4330 l_lookup_meaning := hr_general.decode_lookup(p_lookup_type => 'CN_CONT_BASE_HIGH_LIMIT'
4331 ,p_lookup_code => p_high_limit_method
4332 );
4333
4334 l_lookup_meaning_dep := 'NULL';
4335
4336 p_token_value(1) := l_indep_seg;
4337 p_token_value(2) := l_lookup_meaning;
4338 p_token_value(3) := l_dep_seg;
4339 p_token_value(4) := l_lookup_meaning_dep;
4340
4341 RETURN;
4342
4343 END IF;
4344
4345 END IF;
4346
4347 IF p_switch_periodicity = 'YEARLY' AND p_switch_month IS NULL THEN
4348
4349 l_indep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'Switch Period Periodicity'
4350 ,p_dff => 'Org Developer DF'
4351 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4352 );
4353
4354 l_dep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'Switch Period Month'
4355 ,p_dff => 'Org Developer DF'
4356 ,p_dff_context_code => 'PER_CONT_AREA_CONT_BASE_CN'
4357 );
4358
4359 l_lookup_meaning := hr_general.decode_lookup(p_lookup_type => 'CN_SWITCH_PERIODICITY'
4360 ,p_lookup_code => p_switch_periodicity
4361 );
4362
4363 l_lookup_meaning_dep := 'NULL';
4364
4365 p_token_value(1) := l_indep_seg;
4366 p_token_value(2) := l_lookup_meaning;
4367 p_token_value(3) := l_dep_seg;
4368 p_token_value(4) := l_lookup_meaning_dep;
4369
4370 RETURN;
4371
4372 END IF;
4373
4374 p_message_name := 'SUCCESS';
4375
4376 END check_cont_base_setup;
4377
4378 --------------------------------------------------------------------------
4379 -- --
4380 -- Name : CHECK_PHF_SI_RATES_SETUP --
4381 -- Type : Procedure --
4382 -- Access : Public --
4383 -- Description : Procedure to Check the Contribution Base Setup --
4384 -- --
4385 -- Parameters : --
4386 -- IN : p_organization_id NUMBER --
4387 -- p_contribution_area VARCHAR2 --
4388 -- p_phf_si_type VARCHAR2 --
4389 -- p_hukou_type VARCHAR2 --
4390 -- p_effective_start_date DATE --
4391 -- p_effective_end_date DATE --
4392 -- IN/ OUT : --
4393 -- OUT : p_message_name NOCOPY VARCHAR2 --
4394 -- p_token_name NOCOPY hr_cn_api.char_tab_type --
4395 -- p_token_value NOCOPY hr_cn_api.char_tab_type --
4396 -- --
4397 -- Change History : --
4398 --------------------------------------------------------------------------
4399 -- Rev# Date Userid Description --
4400 --------------------------------------------------------------------------
4401 -- 1.0 15-MAR-05 snekkala Created this Procedure --
4402 -- 1.1 12-May-08 dduvvuri Bug 6943573 - commented the code as no checking is required now
4403 --------------------------------------------------------------------------
4404 PROCEDURE check_phf_si_rates_setup
4405 (p_organization_id IN NUMBER
4406 ,p_contribution_area IN VARCHAR2
4407 ,p_organization IN VARCHAR2
4408 ,p_phf_si_type IN VARCHAR2
4409 ,p_hukou_type IN VARCHAR2
4410 ,p_effective_start_date IN DATE
4411 ,p_effective_end_date IN DATE
4412 ,p_message_name OUT NOCOPY VARCHAR2
4413 ,p_token_name OUT NOCOPY hr_cn_api.char_tab_type
4414 ,p_token_value OUT NOCOPY hr_cn_api.char_tab_type
4415 )
4416 AS
4417
4418 l_indep_seg fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
4419 l_dep_seg fnd_descr_flex_col_usage_vl.form_left_prompt%TYPE;
4420 l_lookup_meaning hr_lookups.meaning%TYPE;
4421 l_lookup_meaning_dep hr_lookups.meaning%TYPE;
4422
4423 BEGIN
4424
4425 p_message_name := 'SUCCESS';
4426
4427 -- Code commented for bug 6943573
4428 /*
4429 IF p_hukou_type IS NOT NULL AND p_organization IS NULL THEN
4430 l_indep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'Hukou Type'
4431 ,p_dff => 'Org Developer DF'
4432 ,p_dff_context_code => 'PER_CONT_AREA_PHF_SI_RATES_CN'
4433 );
4434
4435 l_dep_seg := hr_cn_api.get_dff_tl_value(p_column_name => 'Organization'
4436 ,p_dff => 'Org Developer DF'
4437 ,p_dff_context_code => 'PER_CONT_AREA_PHF_SI_RATES_CN'
4438 );
4439
4440 l_lookup_meaning := hr_general.decode_lookup(p_lookup_type => 'CN_HUKOU_TYPE'
4441 ,p_lookup_code => p_hukou_type
4442 );
4443
4444 l_lookup_meaning_dep := 'NULL';
4445
4446 p_message_name := 'HR_374616_INVALID_PHF_SI_INFO';
4447 p_token_name(1) := 'INDEP_SEG';
4448 p_token_name(2) := 'INDEP_VALUE';
4449 p_token_name(3) := 'DEP_SEG';
4450 p_token_name(4) := 'DEP_VALUE';
4451 p_token_value(1) := l_indep_seg;
4452 p_token_value(2) := l_lookup_meaning;
4453 p_token_value(3) := l_dep_seg;
4454 p_token_value(4) := l_lookup_meaning_dep;
4455
4456 END IF;
4457 */
4458
4459 END check_phf_si_rates_setup;
4460
4461 --------------------------------------------------------------------------
4462 -- --
4463 -- Name : GET_PHF_SI_EARNINGS --
4464 -- Type : Function --
4465 -- Access : Public --
4466 -- Description : Function to get PYEAR and PMTH values for PHF/SI Earnings --
4467 -- Called from all 8 PHF/SI formulas --
4468 -- Change History : --
4469 --------------------------------------------------------------------------
4470 -- Rev# Date Userid Description --
4471 --------------------------------------------------------------------------
4472 -- 1.0 31-MAR-09 dduvvuri 8328944 - Created this Function --
4473 -- 1.1 13-Mar-13 mdubasi 16392593 Added balance 'PHFSI Constituents
4474 -- for Retro Elements' --
4475 --------------------------------------------------------------------------
4476
4477 FUNCTION get_phf_si_earnings
4478 ( p_business_group_id IN NUMBER
4479 ,p_assignment_id IN NUMBER
4480 ,p_date_earned IN DATE
4481 ,p_pay_proc_period_end_date IN DATE
4482 ,p_employer_id IN VARCHAR2
4483 ,p_phf_si_earnings_pyear IN OUT NOCOPY NUMBER
4484 ,p_phf_si_earnings_pmth IN OUT NOCOPY NUMBER
4485 ,p_contribution_area IN VARCHAR2
4486 ,p_phf_si_type IN VARCHAR2
4487 ,p_hukou_type IN VARCHAR2
4488 )
4489 RETURN VARCHAR2
4490 IS
4491 l_message VARCHAR2(255);
4492 l_dimension VARCHAR2(10);
4493
4494 l_ee_cont_base_method hr_organization_information.org_information1%type;
4495 l_er_cont_base_method hr_organization_information.org_information1%type;
4496 l_low_limit_method hr_organization_information.org_information1%type;
4497 l_low_limit_amount NUMBER ;
4498 l_high_limit_method hr_organization_information.org_information1%type;
4499 l_high_limit_amount NUMBER ;
4500 l_switch_periodicity hr_organization_information.org_information1%type;
4501 l_switch_month hr_organization_information.org_information1%type;
4502 l_rounding_method hr_organization_information.org_information1%type;
4503 l_lowest_avg_salary NUMBER ;
4504 l_average_salary NUMBER ;
4505 l_ee_fixed_amount NUMBER ;
4506 l_er_fixed_amount NUMBER ;
4507 l_tax_thrhld_amount NUMBER ;
4508 l_temp_date DATE;
4509 l_temp_year NUMBER;
4510 l_asg_yr_action_id NUMBER := NULL;
4511 l_pay_runs_prev_yr NUMBER;
4512 l_asg_mth_action_id NUMBER := NULL;
4513 l_retro_phfsi_bal_id number:= NULL;
4514 l_phfsi_retro_earnings_pyear number :=NULL;
4515 l_phfsi_earnings_pyear number := NULL;
4516 initialize_on_error EXCEPTION;
4517
4518 /* cursor to fetch the latest asg action id in the target year from Jan to Dec */
4519 CURSOR get_prev_yr_asg_act_id(c_assignment_id IN NUMBER,c_date IN DATE) IS
4520 select /*+ORDERED*/ to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
4521 from pay_assignment_actions paa
4522 , pay_payroll_actions ppa
4523 where paa.assignment_id = c_assignment_id
4524 and ppa.payroll_action_id = paa.payroll_action_id
4525 and ppa.effective_date <= trunc(c_date,'Y') - 1
4526 and ppa.effective_date >= trunc(add_months(c_date,-12),'Y')
4527 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
4528 and paa.action_status='C'
4529 and ppa.action_status='C'
4530 and paa.tax_unit_id = p_employer_id;
4531
4532 /* cursor to fetch the latest asg action id in the target month */
4533 CURSOR get_prev_mth_asg_act_id(c_assignment_id IN NUMBER,c_date IN DATE) IS
4534 select /*+ORDERED*/ to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
4535 from pay_assignment_actions paa
4536 , pay_payroll_actions ppa
4537 where paa.assignment_id = c_assignment_id
4538 and ppa.payroll_action_id = paa.payroll_action_id
4539 and ppa.effective_date <= c_date - 1
4540 and ppa.effective_date >= add_months(c_date,-1)
4541 and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
4542 and paa.action_status='C'
4543 and ppa.action_status='C'
4544 and paa.tax_unit_id = p_employer_id;
4545
4546 BEGIN
4547 l_message := 'SUCCESS';
4548 l_dimension := 'PYEAR';
4549 /* useful for fetching the switch periodicity and the switch month. other fetched details
4550 can be ignored here as they will not be used elsewhere in scope of the function*/
4551 l_message := get_cont_base_methods
4552 (p_business_group_id => p_business_group_id
4553 ,p_contribution_area => p_contribution_area
4554 ,p_phf_si_type => p_phf_si_type
4555 ,p_hukou_type => p_hukou_type
4556 ,p_effective_date => p_pay_proc_period_end_date
4557 --
4558 ,p_ee_cont_base_method => l_ee_cont_base_method
4559 ,p_er_cont_base_method => l_er_cont_base_method
4560 ,p_low_limit_method => l_low_limit_method
4561 ,p_low_limit_amount => l_low_limit_amount
4562 ,p_high_limit_method => l_high_limit_method
4563 ,p_high_limit_amount => l_high_limit_amount
4564 ,p_switch_periodicity => l_switch_periodicity
4565 ,p_switch_month => l_switch_month
4566 ,p_rounding_method => l_rounding_method
4567 ,p_lowest_avg_salary => l_lowest_avg_salary
4568 ,p_average_salary => l_average_salary
4569 ,p_ee_fixed_amount => l_ee_fixed_amount
4570 ,p_er_fixed_amount => l_er_fixed_amount
4571 ,p_tax_thrhld_amount => l_tax_thrhld_amount
4572 );
4573
4574 hr_utility.trace(' get_cont_base_method : Message=>' || l_message);
4575
4576 IF l_message <> 'SUCCESS'
4577 THEN
4578 hr_cn_api.set_location(g_debug,' ' || g_procedure_name, 10);
4579 RAISE initialize_on_error;
4580 END IF;
4581
4582 IF l_switch_periodicity = 'YEARLY' THEN
4583 l_temp_date := TO_DATE('01-'||l_switch_month ||'-'||TO_CHAR(p_pay_proc_period_end_date,'YYYY'),'DD-MM-YYYY');
4584 IF p_pay_proc_period_end_date = last_day(l_temp_date) OR p_pay_proc_period_end_date > l_temp_date THEN
4585 null;
4586 /* Target year is previous year of year in the l_temp_date */
4587 ELSE
4588 l_temp_date := add_months(l_temp_date,-12);
4589 /* Target year is 2 years previous of year in the l_temp_date */
4590 END IF;
4591 ELSE
4592 /* Contribution Base recalculated in every month */
4593 l_temp_date := TO_DATE('01-'||TO_CHAR(p_pay_proc_period_end_date,'MM')||'-'||TO_CHAR(p_pay_proc_period_end_date,'YYYY'),'DD-MM-YYYY');
4594 END IF;
4595
4596
4597 OPEN get_prev_yr_asg_act_id(p_assignment_id,l_temp_date);
4598 FETCH get_prev_yr_asg_act_id INTO l_asg_yr_action_id;
4599 CLOSE get_prev_yr_asg_act_id;
4600
4601 OPEN get_prev_mth_asg_act_id(p_assignment_id,l_temp_date);
4602 FETCH get_prev_mth_asg_act_id INTO l_asg_mth_action_id;
4603 CLOSE get_prev_mth_asg_act_id;
4604
4605 hr_utility.trace('Asg Year Action ID' || l_asg_yr_action_id);
4606 hr_utility.trace('Asg Mth Action ID' || l_asg_mth_action_id);
4607
4608 IF l_asg_yr_action_id IS NULL THEN
4609 /* No latest assignment action exists for the target span */
4610 p_phf_si_earnings_pyear := 0;
4611 ELSE
4612 l_retro_phfsi_bal_id := get_def_bal_id('PHFSI Constituents for Retro Elements','_ASG_YTD');
4613 IF l_retro_phfsi_bal_id <> 0 THEN
4614 l_phfsi_retro_earnings_pyear := pay_balance_pkg.get_value
4615 (p_defined_balance_id => get_def_bal_id('PHFSI Constituents for Retro Elements','_ASG_YTD')
4616 ,p_assignment_action_id => l_asg_yr_action_id
4617 ,p_tax_unit_id => p_employer_id
4618 ,p_jurisdiction_code => NULL
4619 ,p_source_id => NULL
4620 ,p_source_text => NULL
4621 ,p_tax_group => NULL
4622 ,p_date_earned => NULL
4623 ,p_get_rr_route => NULL
4624 ,p_get_rb_route => 'TRUE');
4625 ELSE
4626 l_phfsi_retro_earnings_pyear := 0;
4627 END IF;
4628 hr_utility.trace('PHFSI Constituents for Retro Elements' || l_phfsi_retro_earnings_pyear);
4629
4630 /* call pay_balance_pkg and fetch the value of the PHF_SI_EARNINGS YTD dimension */
4631 l_phfsi_earnings_pyear := pay_balance_pkg.get_value
4632 (p_defined_balance_id => get_def_bal_id('PHF SI Earnings','_ASG_YTD')
4633 ,p_assignment_action_id => l_asg_yr_action_id
4634 ,p_tax_unit_id => p_employer_id
4635 ,p_jurisdiction_code => NULL
4636 ,p_source_id => NULL
4637 ,p_source_text => NULL
4638 ,p_tax_group => NULL
4639 ,p_date_earned => NULL
4640 ,p_get_rr_route => NULL
4641 ,p_get_rb_route => 'TRUE');
4642
4643 hr_utility.trace('PHF/SI Earnings Prev Yr' || l_phfsi_earnings_pyear);
4644 p_phf_si_earnings_pyear := nvl(l_phfsi_earnings_pyear,0) + nvl(l_phfsi_retro_earnings_pyear,0);
4645 hr_utility.trace('Total PHF/SI Earnings Prev Yr' || p_phf_si_earnings_pyear);
4646
4647 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(l_temp_date));
4648 pay_balance_pkg.set_context('ASSIGNMENT_ID',p_assignment_id);
4649 l_pay_runs_prev_yr := fnd_number.canonical_to_number(
4650 pay_balance_pkg.run_db_item('CN_PAYROLL_RUN_MONTHS_PREV_YEAR',p_business_group_id,'CN'));
4651 hr_utility.trace('Payroll runs Prev Yr' || l_pay_runs_prev_yr);
4652 p_phf_si_earnings_pyear := p_phf_si_earnings_pyear / l_pay_runs_prev_yr ;
4653 END IF;
4654
4655 IF l_asg_mth_action_id IS NULL THEN
4656 /* No latest assignment action exists for the target month */
4657 p_phf_si_earnings_pmth := 0;
4658 ELSE
4659 /* call pay_balance_pkg and fetch the value of the PHF_SI_EARNINGS MTD dimension */
4660 p_phf_si_earnings_pmth := pay_balance_pkg.get_value
4661 (p_defined_balance_id => get_def_bal_id('PHF SI Earnings','_ASG_MTD')
4662 ,p_assignment_action_id => l_asg_mth_action_id
4663 ,p_tax_unit_id => p_employer_id
4664 ,p_jurisdiction_code => NULL
4665 ,p_source_id => NULL
4666 ,p_source_text => NULL
4667 ,p_tax_group => NULL
4668 ,p_date_earned => NULL
4669 ,p_get_rr_route => NULL
4670 ,p_get_rb_route => 'TRUE');
4671 hr_utility.trace('PHF/SI Earnings Prev Mth' || p_phf_si_earnings_pmth);
4672 END IF;
4673
4674
4675 return l_message;
4676
4677 EXCEPTION
4678 WHEN initialize_on_error THEN
4679 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
4680 WHEN OTHERS THEN
4681 l_message := hr_cn_api.get_pay_message('HR_374610_ORACLE_GENERIC_ERROR', 'FUNCTION:'||g_procedure_name, 'SQLERRMC:'||sqlerrm);
4682 END get_phf_si_earnings;
4683
4684 --------------------------------------------------------------------------
4685 -- --
4686 -- Name : GET_YOS_SEV_PAY_TAX_RULE --
4687 -- Type : Function --
4688 -- Access : Public --
4689 -- Description : Function to get Severance Pay Taxation rule from BG level --
4690 -- Change History : --
4691 --------------------------------------------------------------------------
4692 -- Rev# Date Userid Description --
4693 --------------------------------------------------------------------------
4694 -- 1.0 24-aug-09 dduvvuri 8799060 - Created this Function --
4695 -- 1.1 26-aug-09 dduvvuri 8799060 - Modified the cursor --
4696 --------------------------------------------------------------------------
4697 FUNCTION get_yos_sev_pay_tax_rule (
4698 p_date_earned IN DATE
4699 , p_tax_area IN VARCHAR2
4700 )
4701 RETURN VARCHAR2
4702 IS
4703 l_use_yos_option varchar2(2);
4704
4705 CURSOR get_rule( p_date_earned DATE
4706 , p_tax_area VARCHAR2
4707 ) IS
4708 SELECT hoi.org_information2
4709 FROM hr_organization_information hoi
4710 WHERE hoi.org_information_context = 'PER_SEVERANCE_PAY_TAX_RULE_CN'
4711 AND hoi.org_information1 = p_tax_area
4712 AND hoi.organization_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4713 AND p_date_earned BETWEEN to_date(substr(hoi.org_information3,1,10),'YYYY/MM/DD')
4714 AND to_date(NVL(substr(hoi.org_information4,1,10),'4712/12/31'),'YYYY/MM/DD');
4715
4716 BEGIN
4717
4718 l_use_yos_option := null;
4719
4720 hr_utility.trace('YOS Option' || l_use_yos_option );
4721
4722 OPEN get_rule( p_date_earned
4723 , p_tax_area
4724 );
4725 FETCH get_rule INTO l_use_yos_option;
4726 CLOSE get_rule;
4727
4728 IF l_use_yos_option is null then
4729 l_use_yos_option := 'N';
4730 END IF;
4731
4732 hr_utility.trace('YOS Option' || l_use_yos_option );
4733
4734 RETURN l_use_yos_option;
4735
4736 EXCEPTION
4737 WHEN OTHERS THEN
4738 null;
4739 END get_yos_sev_pay_tax_rule;
4740
4741
4742 --------------------------------------------------------------------------
4743 -- --
4744 -- Name : GET_ENTERPRISE_ANNUITY_TAX --
4745 -- Type : Function --
4746 -- Access : Public --
4747 -- Description : Function to check if Tax needs to be calculated on --
4748 -- Enterprise Annuity Employer contribution --
4749 -- Change History : --
4750 --------------------------------------------------------------------------
4751 -- Rev# Date Userid Description --
4752 --------------------------------------------------------------------------
4753 -- 1.0 09-dec-12 prasrang 13372444 - Created this Function --
4754 --------------------------------------------------------------------------
4755
4756 FUNCTION get_enterprise_annuity_tax (
4757 p_date_paid IN DATE
4758 , p_tax_area IN VARCHAR2
4759 , p_le IN VARCHAR2
4760 )
4761 RETURN VARCHAR2
4762 IS
4763 l_tax_area varchar2(150);
4764 l_calculate_tax varchar2(150);
4765 l_start_date varchar2(150);
4766 l_end_date varchar2(150);
4767
4768 CURSOR get_rule( p_date_paid DATE
4769 , p_tax_area VARCHAR2
4770 , p_le VARCHAR2
4771 ) IS
4772 SELECT hoi.org_information1,
4773 hoi.org_information2,
4774 hoi.org_information3,
4775 hoi.org_information4
4776 FROM hr_organization_information hoi
4777 WHERE hoi.org_information_context = 'PER_ENN_ANN_TAX_CN'
4778 AND hoi.org_information1 = p_tax_area
4779 AND hoi.organization_id = p_le;
4780
4781 CURSOR get_rule_bg( p_date_paid DATE
4782 , p_tax_area VARCHAR2
4783 ) IS
4784 SELECT hoi.org_information2
4785 FROM hr_organization_information hoi
4786 WHERE hoi.org_information_context = 'PER_ENN_ANN_TAX_CN'
4787 AND hoi.org_information1 = p_tax_area
4788 AND hoi.organization_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4789 AND p_date_paid BETWEEN fnd_date.canonical_to_date(hoi.org_information3)
4790 AND nvl(fnd_date.canonical_to_date(hoi.org_information4), to_date( '31/12/4712','DD/MM/YYYY'));
4791
4792 BEGIN
4793
4794 l_tax_area := null;
4795 l_calculate_tax := null;
4796 l_start_date := null;
4797 l_end_date := null;
4798
4799 OPEN get_rule( p_date_paid
4800 , p_tax_area
4801 , p_le
4802 );
4803 FETCH get_rule INTO l_tax_area,l_calculate_tax,l_start_date,l_end_date;
4804 CLOSE get_rule;
4805
4806 IF l_tax_area IS NULL THEN
4807 OPEN get_rule_bg( p_date_paid
4808 , p_tax_area
4809 );
4810 FETCH get_rule_bg INTO l_calculate_tax;
4811 CLOSE get_rule_bg;
4812 ELSE
4813 IF p_date_paid NOT BETWEEN fnd_date.canonical_to_date(l_start_date)
4814 AND nvl(fnd_date.canonical_to_date(l_end_date), to_date( '31/12/4712','DD/MM/YYYY'))
4815 THEN
4816 l_calculate_tax := 'N';
4817 END IF;
4818 END IF;
4819
4820 IF l_calculate_tax is null then
4821 l_calculate_tax := 'N';
4822 END IF;
4823 hr_utility.trace('Calculate Tax on Enterprise Annuity: ' || l_calculate_tax );
4824 RETURN l_calculate_tax;
4825
4826 EXCEPTION
4827 WHEN OTHERS THEN
4828 null;
4829
4830 END get_enterprise_annuity_tax;
4831
4832 END pay_cn_deductions;