1 package body pay_nz_tax as
2 -- $Header: pynztax.pkb 120.0 2005/05/29 02:13:15 appldev noship $
3 --
4 -- Copyright (c) 1999 Oracle Corporation
5 -- All Rights Reserved
6 --
7 -- Procedures and functions used in NZ tax calculations
8 --
9 -- Change List
10 -- ===========
11 --
12 -- Date Author Reference Description
13 -- -----------+--------+---------+-------------
14 -- 10-Aug-2004 sshankar 3181581 Performance issue - Fixed code for cursors c_cs_code_override and c_cs_code
15 -- 24 Mar 2003 srrajago 2856694 Performance issue - Included joins with time period id and action status
16 -- for the cursors c_cs_code_override,c_cs_code and c_extra_emol_at_low_rate.
17 -- 20 Nov 2000 Kaverma 2665496 modified cursor c_other_asg_exists query
18 -- 14 Feb 2000 JTurner Ported to R11i
19 -- 27 JUL 1999 JTURNER N/A Added extra emol ind and child support
20 -- code fns
21 -- 26 JUL 1999 JTURNER N/A Added half month start and end functions
22 -- 22 JUL 1999 JTURNER N/A Created
23
24
25 -- other_asg_exists
26 --
27 -- function to check for existance of another current
28 -- payroll assignment for the employee
29
30
31 function other_asg_exists
32 (p_assignment_id in number) return varchar2 is
33
34 l_dummy varchar2(1) ;
35 l_return_flag varchar2(1) ;
36
37 /* Bug No : 2665496 */
38
39 cursor get_session_date is
40 select effective_date
41 from fnd_sessions
42 where session_id = userenv('SESSIONID');
43
44 cursor c_other_asg_exists (p2_assignment_id number,p_session_date date) is
45 select null
46 from per_all_assignments_f a1
47 , per_all_assignments_f a2
48 where a1.assignment_id = p2_assignment_id
49 and a2.person_id = a1.person_id
50 and a2.assignment_id <> a1.assignment_id
51 and a2.payroll_id is not null
52 and p_session_date between a2.effective_start_date
53 and a2.effective_end_date;
54
55 l_session_date date;
56
57 begin
58
59 hr_utility.set_location('hr_nz_tax.other_asg_exists', 10) ;
60
61 open get_session_date;
62 fetch get_session_date into l_session_date;
63 close get_session_date;
64
65 open c_other_asg_exists (p_assignment_id, l_session_date) ;
66
67 fetch c_other_asg_exists into l_dummy ;
68
69 if c_other_asg_exists%found
70 then
71 l_return_flag := 'Y' ;
72 else
73 l_return_flag := 'N' ;
74 end if ;
75
76 close c_other_asg_exists ;
77
78 hr_utility.set_location('hr_nz_tax.other_asg_exists', 20) ;
79
80 return l_return_flag ;
81
82 exception
83 when others then
84 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
85 hr_utility.set_message_token('PROCEDURE', 'hr_nz_tax.other_asg_exists');
86 hr_utility.set_message_token('STEP','body');
87 hr_utility.raise_error ;
88
89 end other_asg_exists ;
90
91
92 -- half_month_start
93 --
94 -- Month halves are 1 - 15 and 16 - last day of month for tax reporting
95 -- purposes. This function returns start date of half month that contains
96 -- effective date.
97
98
99 function half_month_start (p_effective_date in date) return date is
100
101 l_return_date date ;
102
103 begin
104
105 if p_effective_date < to_date('16/' || to_char(p_effective_date, 'mm/yyyy'), 'dd/mm/yyyy')
106 then
107 l_return_date := to_date('01/' || to_char(p_effective_date, 'mm/yyyy'), 'dd/mm/yyyy') ;
108 else
109 l_return_date := to_date('16/' || to_char(p_effective_date, 'mm/yyyy'), 'dd/mm/yyyy') ;
110 end if ;
111
112 return l_return_date ;
113
114 end half_month_start ;
115
116
117 -- half_month_end
118 --
119 -- Month halves are 1 - 15 and 16 - last day of month for tax reporting
120 -- purposes. This function returns end date of half month that contains
121 -- effective_date.
122
123
124 function half_month_end (p_effective_date in date) return date is
125
126 l_return_date date ;
127
128 begin
129
130 if p_effective_date < to_date('16/' || to_char(p_effective_date, 'mm/yyyy'), 'dd/mm/yyyy')
131 then
132 l_return_date := to_date('15/' || to_char(p_effective_date, 'mm/yyyy'), 'dd/mm/yyyy') ;
133 else
134 l_return_date := last_day(p_effective_date) ;
135 end if ;
136
137 return l_return_date ;
138
139 end half_month_end ;
140
141
142 -- extra_emol_at_low_tax_rate
143 --
144 -- Determines if any extra emoluments have been taxed at the
145 -- lower rate.
146
147
148 function extra_emol_at_low_tax_rate (p_assignment_id in number, p_effective_date in date) return varchar2 is
149
150 l_extra_emol_indicator varchar2(1) ;
151 l_start_date date ;
152 l_end_date date ;
153 l_dummy varchar2(1) ;
154
155 /* Bug No : 2856694 - Included joins with time period id and action status in the where clause of the
156 following cursor */
157
158 cursor c_extra_emol_at_low_rate (p_assignment_id number
159 ,p_start_date date
160 ,p_end_date date) is
161 select null
162 from pay_element_types_f et
163 , pay_input_values_f iv
164 , pay_run_results rr
165 , pay_run_result_values rrv
166 , pay_assignment_actions aa
167 , pay_payroll_actions pa
168 , per_time_periods tp
169 where et.element_name = 'PAYE Tax Deduction'
170 and iv.element_type_id = et.element_type_id
171 and iv.name = 'Extra Emol at Low Tax Rate'
172 and rr.element_type_id = et.element_type_id
173 and rr.status in ('P','PA')
174 and rrv.run_result_id = rr.run_result_id
175 and rrv.input_value_id = iv.input_value_id
176 and rrv.result_value = 'Y'
177 and aa.assignment_action_id = rr.assignment_action_id
178 and aa.assignment_id = p_assignment_id
179 and pa.payroll_action_id = aa.payroll_action_id
180 and tp.payroll_id = pa.payroll_id
181 and pa.date_earned between tp.start_date
182 and tp.end_date
183 and tp.regular_payment_date between p_start_date
184 and p_end_date
185 and tp.time_period_id = pa.time_period_id
186 and pa.action_status = 'C'
187 and aa.action_status = 'C';
188
189 begin
190
191 l_start_date := to_date('01/' || to_char(p_effective_date, 'mm/yyyy'), 'dd/mm/yyyy') ;
192 l_end_date := last_day(p_effective_date) ;
193
194 open c_extra_emol_at_low_rate (p_assignment_id
195 ,l_start_date
196 ,l_end_date) ;
197
198 fetch c_extra_emol_at_low_rate into l_dummy ;
199
200 if c_extra_emol_at_low_rate%found
201 then
202 l_extra_emol_indicator := 'Y' ;
203 else
204 l_extra_emol_indicator := 'N' ;
205 end if ;
206
207 close c_extra_emol_at_low_rate ;
208
209 return l_extra_emol_indicator ;
210
211 end extra_emol_at_low_tax_rate ;
212
213
214 -- child_support_code
215 --
216 -- Determines child support variation code.
217
218
219 function child_support_code (p_assignment_id in number, p_effective_date in date) return varchar2 is
220
221 l_child_support_code varchar2(1) ;
222 l_start_date date ;
223 l_end_date date ;
224
225 /* Bug No : 2856694 - Included joins with time_period_id and action_status in the where clause of the
226 following cursors c_cs_code_override and c_cs_code */
227
228 --
229 -- Bug 3181581
230 -- Changed cursor code as part of performance fix. Added per_assignments_f and used effective_date
231 -- instead of regular_payment_date
232 --
233 cursor c_cs_code_override (p_assignment_id number
234 ,p_start_date date
235 ,p_end_date date) is
236 select rrv.result_value
237 from pay_element_types_f et
238 , pay_input_values_f iv
239 , pay_run_results rr
240 , pay_run_result_values rrv
241 , pay_assignment_actions aa
242 , per_assignments_f asg
243 , pay_payroll_actions pa
244 , per_time_periods tp
245 where et.element_name = 'Child Support Information'
246 and iv.element_type_id = et.element_type_id
247 and iv.name = 'Child Support Code Override'
248 and rr.element_type_id = et.element_type_id
249 and rr.status in ('P','PA')
250 and rrv.run_result_id = rr.run_result_id
251 and rrv.input_value_id = iv.input_value_id
252 and rrv.result_value is not null
253 and aa.assignment_action_id = rr.assignment_action_id
254 and asg.assignment_id = p_assignment_id
255 and aa.assignment_id = asg.assignment_id
256 and pa.payroll_action_id = aa.payroll_action_id
257 and tp.payroll_id = pa.payroll_id
258 and pa.date_earned between tp.start_date
259 and tp.end_date
260 and pa.effective_date between p_start_date
261 and p_end_date
262 and tp.time_period_id = pa.time_period_id
263 and pa.action_status = 'C'
264 and aa.action_status = 'C'
265 order by
266 pa.action_sequence desc ;
267
268 --
269 -- Bug 3181581
270 -- Changed cursor code as part of performance fix. Added per_assignments_f and used effective_date
271 -- instead of regular_payment_date
272 --
273 cursor c_cs_code (p_assignment_id number
274 ,p_start_date date
275 ,p_end_date date) is
276 select rrv.result_value
277 from pay_element_types_f et
278 , pay_input_values_f iv
279 , pay_run_results rr
280 , pay_run_result_values rrv
281 , pay_assignment_actions aa
282 , per_assignments_f asg
283 , pay_payroll_actions pa
284 , per_time_periods tp
285 where et.element_name = 'Child Support Deduction'
286 and iv.element_type_id = et.element_type_id
287 and iv.name = 'Child Support Code'
288 and rr.element_type_id = et.element_type_id
289 and rr.status in ('P','PA')
290 and rrv.run_result_id = rr.run_result_id
291 and rrv.input_value_id = iv.input_value_id
292 and aa.assignment_action_id = rr.assignment_action_id
293 and asg.assignment_id = p_assignment_id
294 and aa.assignment_id = asg.assignment_id
295 and pa.payroll_action_id = aa.payroll_action_id
296 and tp.payroll_id = pa.payroll_id
297 and pa.date_earned between tp.start_date
298 and tp.end_date
299 and pa.effective_date between p_start_date
300 and p_end_date
301 and tp.time_period_id = pa.time_period_id
302 and pa.action_status = 'C'
303 and aa.action_status = 'C'
304 order by pa.action_sequence desc;
305
306 begin
307
308 l_start_date := to_date('01/' || to_char(p_effective_date, 'mm/yyyy'), 'dd/mm/yyyy') ;
309 l_end_date := last_day(p_effective_date) ;
310
311 l_child_support_code := null ;
312
313 open c_cs_code_override (p_assignment_id
314 ,l_start_date
315 ,l_end_date) ;
316
317 fetch c_cs_code_override into l_child_support_code ;
318
319 if c_cs_code_override%notfound
320 then
321
322 open c_cs_code (p_assignment_id
323 ,l_start_date
324 ,l_end_date) ;
325
326 fetch c_cs_code into l_child_support_code ;
327
328 if c_cs_code%notfound
329 then
330 l_child_support_code := null ;
331 end if ;
332
333 close c_cs_code ;
334
335 end if ;
336
337 close c_cs_code_override ;
338
339 return l_child_support_code ;
340
341 end child_support_code ;
342
343 end pay_nz_tax ;
[Home] [Help]