1 PACKAGE BODY pay_us_tax_udfs_pkg AS
2 /* $Header: pyustudf.pkb 115.3 2004/06/24 10:58:21 ppanda noship $ */
3
4
5 FUNCTION get_altrnt_flat_rate_calc_meth(p_assignment_action_id in number,
6 p_state_code in varchar2)
7 RETURN VARCHAR2 IS
8
9 ln_calc_method varchar2(30) := 'NO_METHOD';
10
11 /* get_altrnt_flat_rate_calc_meth will return the first
12 calculation method found for the given state
13 */
14
15 BEGIN /* get_alternate_flat_rate_calc_method */
16
17 hr_utility.trace('get_alternate_flat_rate_calc_method');
18
19
20 if etei_data_val.COUNT = 0 then
21 ln_calc_method := 'NO_METHOD';
22 else
23 for i in etei_data_val.FIRST .. etei_data_val.LAST loop
24
25 if ( p_state_code = etei_data_val(i).state_code and
26 p_assignment_action_id = etei_data_val(1).asg_act_id
27 ) then
28 ln_calc_method := etei_data_val(i).calc_method;
29 exit;
30 end if;
31 end loop;
32
33 end if;
34
35 hr_utility.trace('Value of Calc Method is : ' || ln_calc_method);
36
37 return ln_calc_method;
38
39 END get_altrnt_flat_rate_calc_meth;
40
41
42 FUNCTION store_data( p_assignment_action_id in number,
43 p_element_type_id in number,
44 p_state_code in varchar2,
45 p_calc_method in varchar2)
46 RETURN NUMBER IS
47
48 ln_return number;
49 ln_cnt number;
50 ln_found number;
51 ln_element_type_id pay_element_types_f.element_type_id%TYPE;
52 lv_level fnd_lookup_values.meaning%TYPE;
53 lv_state_code pay_us_states.state_code%TYPE;
54 lv_calc_method fnd_lookup_values.lookup_code%TYPE;
55
56
57 begin
58
59 hr_utility.trace('In store_data ');
60 hr_utility.trace('asg_act_id = '||p_assignment_action_id);
61 hr_utility.trace('Element Type Id(1) = '||p_element_type_id);
62 hr_utility.trace('lv_state_code = '||p_state_code);
63 hr_utility.trace('lv_calc_method = '||p_calc_method);
64
65 ln_found := 0;
66 ln_cnt := etei_data_val.COUNT +1;
67 hr_utility.trace('ln_cnt = '||ln_cnt);
68
69 if etei_data_val.COUNT > 0 then
70
71 if (p_assignment_action_id <> etei_data_val(1).asg_act_id)then
72 hr_utility.trace('Flushing table and Inserting ');
73 etei_data_val.DELETE;
74 etei_data_val(1).asg_act_id := p_assignment_action_id;
75 etei_data_val(1).ele_type_id := p_element_type_id;
76 etei_data_val(1).state_code := p_state_code;
77 etei_data_val(1).calc_method := p_calc_method;
78 else
79 for i in etei_data_val.FIRST .. etei_data_val.LAST loop
80
81 if (p_element_type_id = etei_data_val(i).ele_type_id) then
82 if p_state_code <> etei_data_val(i).state_code then
83 ln_found := 0;
84 else
85 ln_found := 1;
86 end if;
87 else
88 ln_found := 0;
89 end if;
90
91 if ln_found = 1 then
92 exit;
93 end if;
94
95 end loop;
96
97 if ln_found = 0 then
98 etei_data_val(ln_cnt).asg_act_id := p_assignment_action_id;
99 etei_data_val(ln_cnt).ele_type_id := p_element_type_id;
100 etei_data_val(ln_cnt).state_code := p_state_code;
101 etei_data_val(ln_cnt).calc_method := p_calc_method;
102 end if;
103 end if;
104 else
105
106 hr_utility.trace('First time Inserting ');
107 etei_data_val(1).asg_act_id := p_assignment_action_id;
108 etei_data_val(1).ele_type_id := p_element_type_id;
109 etei_data_val(1).state_code := p_state_code;
110 etei_data_val(1).calc_method := p_calc_method;
111
112 end if;
113
114 return etei_data_val.COUNT;
115 end store_data;
116
117 FUNCTION set_altrnt_flat_rate_calc_meth(
118 p_assignment_action_id in number,
119 p_element_type_id in number,
120 p_date_earned in date,
121 p_state_code in varchar2 default 'NOT_APPLICABLE',
122 p_calc_method in varchar2 default 'NOT_APPLICABLE')
123 RETURN NUMBER IS
124
125 ln_return number;
126
127 cursor c_get_element_extra_info is
128 select pet.element_type_id,
129 petei.EEI_INFORMATION2,
130 petei.EEI_INFORMATION4,
131 petei.EEI_INFORMATION5
132 from pay_element_types_f pet,
133 pay_element_type_extra_info petei
134 where pet.element_type_id = p_element_type_id
135 and p_date_earned between pet.effective_start_date
136 and pet.effective_end_date
137 and petei.INFORMATION_TYPE = 'PAY_US_TAX_CALCULATION_METHOD'
138 and petei.element_type_id = pet.element_type_id;
139
140
141 ln_element_type_id pay_element_types_f.element_type_id%TYPE;
142 lv_level fnd_lookup_values.meaning%TYPE;
143 lv_state_code pay_us_states.state_code%TYPE;
144 lv_calc_method fnd_lookup_values.lookup_code%TYPE;
145
146 /* set_altrnt_flat_rate_calc_meth will set the following
147 information : Assignment Action Id , Element Type Id,
148 State Code and Calculation Method.
149 We will insert records in a plsql table for an assignment action id
150 one or more element type id and for each element type id
151 one or more state code and calculation method.
152 */
153 ln_extra_info_count number := 0;
154
155 BEGIN /* set_altrnt_flat_rate_calc_meth */
156
157 hr_utility.trace('In set_altrnt_flat_rate_calc_meth');
158
159 if (p_state_code = 'NOT_APPLICABLE' AND
160 p_calc_method = 'NOT_APPLICABLE') then
161
162 for c1_rec in c_get_element_extra_info loop
163 ln_extra_info_count := ln_extra_info_count + 1;
164 if c_get_element_extra_info%notfound then
165 --
166 -- Purge the pl/sql table if alternate supplemetal method defined for an element
167 --
168 if (ln_extra_info_count = 1 and
169 etei_data_val.COUNT > 0)
170 then
171 etei_data_val.DELETE;
172 end if;
173 --End of purge
174 hr_utility.trace('State Code and Claculation Method Not Found for an Element');
175 exit;
176 end if;
177
178 ln_element_type_id := c1_rec.element_type_id;
179 lv_level := c1_rec.EEI_INFORMATION2;
180 lv_state_code := c1_rec.EEI_INFORMATION4;
181 lv_calc_method := c1_rec.EEI_INFORMATION5;
182
183 if lv_level = 'Federal' then
184 lv_state_code := '00';
185 end if;
186
187 ln_return := store_data(p_assignment_action_id,
188 p_element_type_id,
189 lv_state_code,
190 lv_calc_method);
191 end loop;
192 else
193 lv_state_code := p_state_code;
194 lv_calc_method := p_calc_method;
195
196 ln_return := store_data(p_assignment_action_id,
197 p_element_type_id,
198 lv_state_code,
199 lv_calc_method);
200 end if;
201 return ln_return;
202 exception when others then
203 return 0;
204
205 END set_altrnt_flat_rate_calc_meth; /* set_altrnt_flat_rate_calc_meth */
206
207 END pay_us_tax_udfs_pkg;
208
[Home] [Help]