1 PACKAGE body hr_us_w4_ex AS
2 /* $Header: pyusw4ex.pkb 120.1 2006/05/15 11:12:24 alikhar noship $ */
3 /*
4 +=====================================================================+
5 | Copyright (c) 1997 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +=====================================================================+
9 Name : pyusw4ex.pkb
10 Description : This package declares functions and procedures which are
11 used to return values for the Tax Form Exception Report.
12
13 Change List
14 -----------
15
16 Ver Date Author Bug No Description of Change
17 -------+-----------+----------+--------+--------------------------
18 115.0 11-06-00 Asasthan Date Created
19 115.5 22-JUL-2003 ahanda 3061866 Changed cursor to get user_entity for US
20 115.6 08-JAN-2004 ardsouza 3349705 Used cursor instead of query to fetch
21 latest assignment action for improved
22 performance
23 115.7 09-JAN-2004 ardsouza 3349705 Added hint USE_NL to improve performance
24 115.8 12-JAN-2004 ardsouza 3349705 Suppressed index on ppa.effective_date.
25 Hint USE_NL not needed. Added "not null"
26 condition on paf.payroll_id.
27 115.9 12-MAY-2006 alikhar 5163321 Added code to handle no data found
28 returned by cursor c_get_latest_asg.
29 =============================================================================
30
31 */
32
33 /*
34 Name : bal_db_item
35 Purpose : Given the name of a balance DB item as would be seen in a
36 fast for mula
37 it returns the defined_balance_id of the balance it represents.
38 Arguments :
39 Notes : A defined balance_id is required by the PLSQL balance function.
40 */
41
42 function bal_db_item
43 (
44 p_db_item_name varchar2
45 ) return number is
46
47 /* Get the defined_balance_id for the specified balance DB item. */
48
49 cursor csr_defined_balance is
50 select to_number(UE.creator_id)
51 from ff_user_entities UE,
52 ff_database_items DI
53 where DI.user_name = p_db_item_name
54 and UE.user_entity_id = DI.user_entity_id
55 and Ue.creator_type = 'B'
56 and ue.legislation_code = 'US';
57
58 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
59
60 begin
61
62 open csr_defined_balance;
63 fetch csr_defined_balance into l_defined_balance_id;
64 if csr_defined_balance%notfound then
65 close csr_defined_balance;
66 raise hr_utility.hr_error;
67 else
68 close csr_defined_balance;
69 end if;
70
71 return (l_defined_balance_id);
72
73 end bal_db_item;
74
75
76 FUNCTION get_bal_info (w4_tax_unit_id in number,
77 w4_jurisdiction_code in varchar2,
78 w4_person_id in number,
79 w4_start_date in date,
80 w4_end_date in date)
81 RETURN NUMBER
82
83 IS
84 l_bal_aaid pay_assignment_actions.assignment_action_id%type;
85 l_balance pay_run_result_values.result_value%type :=null;
86 l_hours_ytd number := 0;
87 l_gross_ytd number := 0;
88 l_gross_per_week number :=0;
89 l_gross_per_hour number :=0;
90
91 -- Bug 3349705 - Cursor to fetch the latest assignment action id.
92 --
93 CURSOR c_get_latest_asg(p_person_id number ) IS
94 select paa.assignment_action_id
95 from pay_assignment_actions paa,
96 per_all_assignments_f paf,
97 pay_payroll_actions ppa,
98 pay_action_classifications pac
99 where paf.person_id = p_person_id
100 and paa.assignment_id = paf.assignment_id
101 and paa.tax_unit_id = w4_tax_unit_id
102 and paa.payroll_action_id = ppa.payroll_action_id
103 and ppa.action_type = pac.action_type
104 and pac.classification_name = 'SEQUENCED'
105 and ppa.business_group_id = paf.business_group_id
106 and paf.payroll_id is not null
107 and ppa.effective_date +0 between paf.effective_start_date
108 and paf.effective_end_date
109 and ppa.effective_date +0 between w4_start_date
110 and w4_end_date
111 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
112 and paa.source_action_id is null)
113 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
114 and paa.source_action_id is not null )
115 or (ppa.action_type = 'V' and ppa.run_type_id is null
116 and paa.run_type_id is not null
117 and paa.source_action_id is null))
118 order by paa.action_sequence desc;
119
120 Begin
121
122
123 Begin
124 open c_get_latest_asg(w4_person_id );
125 fetch c_get_latest_asg into l_bal_aaid;
126 if c_get_latest_asg%notfound then
127 l_balance := 0;
128 end if;
129 close c_get_latest_asg;
130
131 Exception
132 when no_data_found then
133 l_balance := 0;
134 End;
135
136 /* Bug 3349705 - Commented and replaced by cursor above for performance problems.
137
138 Begin
139 select paa1.assignment_action_id
140 into l_bal_aaid
141 from pay_assignment_actions paa1,
142 per_assignments_f paf2,
143 pay_payroll_actions ppa2,
144 pay_action_classifications pac2
145 where paf2.person_id = w4_person_id
146 and paa1.assignment_id = paf2.assignment_id
147 and paa1.tax_unit_id = w4_tax_unit_id
148 and paa1.payroll_action_id = ppa2.payroll_action_id
149 and ppa2.action_type = pac2.action_type
150 and pac2.classification_name = 'SEQUENCED'
151 and ppa2.effective_date between paf2.effective_start_date
152 and paf2.effective_end_date
153 and ppa2.effective_date between w4_start_date and
154 w4_end_date
155 and not exists (select ''
156 FROM pay_action_classifications pac,
157 pay_payroll_actions ppa,
158 pay_assignment_actions paa,
159 per_assignments_f paf1
160 WHERE paf1.person_id = w4_person_id
161 AND paa.assignment_id = paf1.assignment_id
162 AND paa.tax_unit_id = w4_tax_unit_id
163 AND ppa.payroll_action_id = paa.payroll_action_id
164 AND ppa.effective_date between w4_start_date and
165 w4_end_date
166 AND paa.action_sequence > paa1.action_sequence
167 AND pac.action_type = ppa.action_type
168 AND pac.classification_name = 'SEQUENCED')
169 and rownum < 2;
170 Exception
171 when no_data_found then
172 l_balance := 0;
173 End;
174
175 */
176
177 if l_balance is null then
178
179 pay_balance_pkg.set_context('TAX_UNIT_ID',w4_tax_unit_id);
180
181 if w4_jurisdiction_code <> '00-000-0000' then
182
183 pay_balance_pkg.set_context('JURISDICTION_CODE',w4_jurisdiction_code);
184 end if;
185
186 l_hours_ytd := nvl(pay_balance_pkg.get_value
187 (p_defined_balance_id => bal_db_item('REGULAR_HOURS_WORKED_PER_GRE_YTD'),
188 p_assignment_action_id => l_bal_aaid),0);
189
190 if l_hours_ytd = 0 then
191 l_gross_per_week := 0;
192 else
193 l_gross_ytd := nvl(pay_balance_pkg.get_value
194 (p_defined_balance_id => bal_db_item('GROSS_EARNINGS_PER_GRE_YTD'),
195 p_assignment_action_id => l_bal_aaid),0);
196
197 l_gross_per_hour := l_gross_ytd/l_hours_ytd;
198 l_gross_per_week := l_gross_per_hour * 40;
199 end if;
200
201 end if;
202
203 return(l_gross_per_week);
204 End ; /* get_bal_info */
205
206
207
208
209 FUNCTION get_tax_info (w4_tax_unit_id in number,
210 w4_jurisdiction_code in varchar2,
211 w4_person_id in number,
212 w4_allowance in varchar2,
213 w4_exempt in varchar2,
214 w4_state_code in varchar2,
215 w4_start_date in date,
216 w4_end_date in date)
217 RETURN NUMBER
218
219 IS
220 l_exception number(1) := 0;
221 l_fed_earnings number;
222 l_fed_allowance number;
223 l_state_allowance number;
224 l_state_earnings number;
225 l_gross_earnings_per_week number :=0;
226
227 BEGIN
228 if w4_jurisdiction_code = '00-000-0000' then
229
230 Begin
231
232 select nvl(to_number(fed_information1),0),nvl(to_number(fed_information2),0)
233 into l_fed_allowance , l_fed_earnings
234 from pay_us_federal_tax_info_f
235 where fed_information_category = 'ALLOWANCES LIMIT';
236
237 Exception
238 when no_data_found then
239 raise hr_utility.hr_error;
240 End;
241
242 if nvl(l_fed_allowance,0) > 0 then
243 if nvl(to_number(w4_allowance),0) > l_fed_allowance then
244 l_exception := 1;
245 end if;
246 end if;
247
248 if w4_exempt = 'Y' then
249 if nvl(l_fed_earnings,0) > 0 then
250 l_gross_earnings_per_week:= get_bal_info(w4_tax_unit_id,
251 w4_jurisdiction_code,
252 w4_person_id ,
253 w4_start_date ,
254 w4_end_date);
255 if l_gross_earnings_per_week > l_fed_earnings then
256 l_exception := 1;
257 end if;
258 end if;
259 end if;
260
261 else
262
263 Begin
264
265 select to_number(sta_information10),to_number(sta_information11)
266 into l_state_allowance , l_state_earnings
267 from pay_us_state_tax_info_f
268 where state_code = substr(w4_jurisdiction_code,1,2)
269 and effective_end_date = to_date('31/12/4712','DD/MM/YYYY')
270 and sta_information_category = 'State tax limit rate info';
271
272 Exception
273 when no_data_found then
274 /* the state is not interested in knowing exceptions
275 so smoothly get out */
276 l_exception := 0;
277 return(l_exception);
278 End;
279
280 if nvl(l_state_allowance,0) > 0 then
281 if w4_allowance > l_state_allowance then
282 l_exception := 1;
283 end if;
284 end if;
285
286 if w4_exempt = 'Y' then
287 if nvl(l_state_earnings,0) > 0 then
288 l_gross_earnings_per_week:= get_bal_info(w4_tax_unit_id,
289 w4_jurisdiction_code,
290 w4_person_id ,
291 w4_start_date ,
292 w4_end_date);
293 if l_gross_earnings_per_week > l_state_earnings then
294 l_exception := 1;
295 end if;
296 end if;
297 end if;
298 end if;
299
300 if substr(w4_jurisdiction_code,1,2) = '03' then
301 l_exception := 1;
302 end if;
303 return(l_exception);
304 END get_tax_info;
305
306 end hr_us_w4_ex;
[Home] [Help]