1 package body pay_nz_ems_tax_rate as
2 /* $Header: pynzemsrt.pkb 120.1.12020000.2 2012/07/05 02:14:33 amnaraya ship $
3 **
4 ** Copyright (c) 1999 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** The package has a function which checks if the value of Tax Rate input
8 ** being used is NULL or not.
9 **
10 ** Change List
11 ** ===========
12 **
13 ** Date Author Reference Description
14 ** -----------+--------+---------+-------------
15 ** 07-NOV-2009 dduvvuri 7480679 Created
16 ** 10-NOV-2009 dduvvuri 7480679 Function inputs and body changed
17 ** 03-FEB-2012 dduvvuri 13627558 Created new function get_balance_value
18 */
19
20 FUNCTION get_tax_rate
21 (
22 p_given_date IN DATE
23 ,p_run_result_id IN NUMBER
24 )
25 RETURN VARCHAR2
26 IS
27
28 l_input_value_id pay_input_values_f.input_value_id%TYPE;
29
30 l_result number := NULL;
31
32 CURSOR get_rate_input_value
33 (c_effective_date date)
34 IS
35 SELECT pivf.input_value_id
36 FROM pay_input_values_f pivf
37 WHERE pivf.element_type_id = (select distinct element_type_id
38 from pay_element_types_f
39 where legislation_code = 'NZ'
40 and element_name = 'PAYE Information'
41 )
42 AND upper(pivf.name) = 'TAX RATE'
43 AND c_effective_date between pivf.effective_start_date and pivf.effective_end_date;
44
45 CURSOR get_rate_result_value
46 (c_run_result_id pay_run_result_values.run_result_id%TYPE
47 ,c_input_value_id pay_run_result_values.input_value_id%TYPE)
48 IS
49 SELECT prrv.result_value
50 FROM pay_run_result_values prrv
51 WHERE prrv.run_result_id = c_run_result_id
52 AND prrv.input_value_id = c_input_value_id;
53
54 BEGIN
55
56 OPEN get_rate_input_value(p_given_date);
57 FETCH get_rate_input_value INTO l_input_value_id;
58 CLOSE get_rate_input_value;
59
60 IF l_input_value_id IS NOT NULL
61 THEN
62 OPEN get_rate_result_value(p_run_result_id,l_input_value_id);
63 FETCH get_rate_result_value INTO l_result;
64 CLOSE get_rate_result_value;
65 END IF;
66
67
68 IF l_result IS NULL THEN
69 RETURN 'N';
70 ELSE
71 RETURN 'Y';
72 END IF;
73
74 END get_tax_rate;
75
76 FUNCTION get_balance_value(ASSIGNMENT_ID IN NUMBER
77 ,P_BAL_NAME IN VARCHAR2
78 ,P_DATE IN DATE)
79 RETURN NUMBER
80 IS
81
82 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
83 l_bal_value NUMBER := 0;
84
85 CURSOR csr_get_id
86 IS
87 select defined_balance_id
88 from pay_defined_balances
89 where balance_type_id = ( select balance_type_id
90 from pay_balance_types
91 where legislation_code = 'NZ'
92 and balance_name = P_BAL_NAME
93 )
94 and balance_dimension_id = ( select balance_dimension_id
95 from pay_balance_dimensions
96 where legislation_code = 'NZ'
97 and dimension_name = '_ASG_TD'
98 );
99
100 BEGIN
101
102 OPEN csr_get_id;
103 FETCH csr_get_id INTO l_def_bal_id;
104 IF csr_get_id%NOTFOUND THEN
105 l_bal_value := 0;
106 CLOSE csr_get_id;
107 RETURN l_bal_value;
108 END IF;
109 CLOSE csr_get_id;
110
111 l_bal_value := PAY_BALANCE_PKG.get_value(l_def_bal_id,ASSIGNMENT_ID,P_DATE);
112
113 RETURN l_bal_value;
114
115 END get_balance_value;
116
117
118 end pay_nz_ems_tax_rate ;
[Home] [Help]