PACKAGE BODY: APPS.PAY_ES_COURT_ORDER_DEDUCTION

Source

1 PACKAGE BODY pay_es_court_order_deduction as
2 /* $Header: pyescodc.pkb 120.0.12010000.2 2008/08/06 07:11:18 ubhat ship $ */
3 --------------------------------------------------------------------------------
4 -- CALC_COURT_ORDER_DEDUCTION
5 --------------------------------------------------------------------------------
6 FUNCTION calc_court_order_deduction(p_business_gr_id NUMBER
7                                    ,p_effective_date DATE
8                                    ,p_minimum_wage   NUMBER
9                                    ,p_annual_salary  NUMBER
10                                    ,p_age            NUMBER) RETURN NUMBER
11 IS
12     CURSOR csr_get_table_range(c_salary         NUMBER
13                               ,c_wage           NUMBER
14                               ,c_effective_date DATE) IS
15     SELECT /*+ ORDERED*/ to_number(pur.row_low_range_or_name) Low_value
16            ,to_number(pur.row_high_range) High_value
17     FROM    pay_user_tables put
18            ,pay_user_rows_f pur
19     WHERE   put.user_table_name = 'ES_COURT_ORDER_DEDUCTION_SCALE'
20     AND     put.user_table_id = pur.user_table_id
21     AND     put.legislation_code = 'ES'
22     AND     pur.legislation_code = 'ES'
23     AND     (c_salary > to_number(pur.row_low_range_or_name) * c_wage
24             OR c_salary BETWEEN to_number(pur.row_low_range_or_name) * c_wage
25                             AND to_number(pur.row_high_range * c_wage))
26     AND     c_effective_date between pur.effective_start_date AND pur.effective_end_date
27     ORDER BY 1;
28     --
29     l_perc NUMBER;
30     l_co_deduction NUMBER;
31     l_salary NUMBER;
32     --
33 BEGIN
34     --
35     l_co_deduction := 0;
36     l_salary := p_annual_salary;
37     FOR l_rec in csr_get_table_range(p_annual_salary
38                                     ,p_minimum_wage
39                                     ,p_effective_date) LOOP
40         hr_utility.trace('High Val :'||l_rec.high_value||'  Low Val :'||l_rec.low_value);
41         BEGIN
42             l_perc := hruserdt.get_table_value(p_bus_group_id     =>  p_business_gr_id
43                                  ,p_table_name     => 'ES_COURT_ORDER_DEDUCTION_SCALE'
44                                  ,p_col_name       => 'DEDUCTION_SCALE'
45                                  ,p_row_value      => l_rec.low_value
46                                  ,p_effective_date => p_effective_date);
47         EXCEPTION
48 		    WHEN NO_DATA_FOUND THEN
49 		        l_perc := 0;
50     	END;
51         hr_utility.trace('High Val :'||l_rec.high_value||'  Low Val :'||l_rec.low_value||' perc :'||l_perc);
52         l_co_deduction := l_co_deduction + (LEAST(p_minimum_wage, l_salary)* l_perc/100);
53         l_salary := l_salary - p_minimum_wage;
54     END LOOP;
55 
56     RETURN l_co_deduction;
57 END calc_court_order_deduction;
58 --
59 END pay_es_court_order_deduction;