1 PACKAGE BODY HR_US_PERSON_TERM_LEG_HOOK AS
2 /* $Header: pyusterm.pkb 120.1 2008/01/18 13:06:51 jdevasah noship $ */
3 /* +======================================================================+
4 | Copyright (c) 2003 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 Package Name : hr_us_person_term_leg_hook
9 Package File Name : pyusterm.pkb
10
11 Description : This package will be called from Before Process Hook
12 hr_periods_of_service_bk1.update_pds_details_b for US
13 legislation. It is used to correctly End Date the Tax
14 Records (Federal / State / County / City) as per the
15 Final Process Date entered by the user.
16
17 Change List:
18 ------------
19 Name Date Version Bug Text
20 ------------- ----------- ------- ------- ------------------------------
21 sudedas 13-NOV-2006 115.0 5460532 Created.
22 sudedas 02-MAR-2007 115.1 5460532 Corrected Issues experienced
23 by customers.
24 sudedas 08-MAR-2007 115.3 5898172 To take care of SSHR Issue
25 when FPD defaulted to 01Jan(-4712)
26 */
27
28 procedure update_tax_rules(p_period_of_service_id in number,
29 p_final_process_date in date) is
30
31 /* Fetching Old Final Process Date and Person ID From Period of Service */
32
33 cursor c_get_person_oldfpd(p_period_of_service_id in number) is
34 select distinct pds.person_id,
35 pds.final_process_date
36 from per_periods_of_service pds
37 where pds.period_of_service_id = p_period_of_service_id ;
38
39 /* Getting all Terminated Assignments for the Person concerned */
40
41 cursor c_get_assignments(p_person_id in number) is
42 select distinct assignment_id
43 from per_assignments_f paf_o,
44 per_assignment_status_types past
45 where paf_o.person_id = p_person_id
46 and paf_o.assignment_status_type_id = past.assignment_status_type_id
47 and past.per_system_status = 'TERM_ASSIGN'
48 and ((past.business_group_id is null
49 and past.legislation_code is null)
50 OR (past.business_group_id is null
51 and past.legislation_code = 'US')
52 OR (past.legislation_code is null
53 and exists
54 (select 'x'
55 from per_assignments_f paf_a
56 where paf_a.assignment_id = paf_o.assignment_id
57 and paf_a.business_group_id = past.business_group_id)
58 )
59 )
60 and paf_o.effective_end_date = (select max(paf_i.effective_end_date)
61 from per_assignments_f paf_i
62 where paf_i.assignment_id = paf_o.assignment_id) ;
63
64 -- Fetching Federal Tax Rules details the latest record that needs to be
65 -- end dated as of New FPD
66
67 cursor c_get_fedtax_rules(p_assignment_id in number,
68 p_final_process_date in date) is
69 select pueo.emp_fed_tax_rule_id,
70 pueo.effective_start_date,
71 pueo.effective_end_date
72 from pay_us_emp_fed_tax_rules_f pueo
73 where pueo.assignment_id = p_assignment_id
74 and pueo.effective_start_date = (select max(puei.effective_start_date)
75 from pay_us_emp_fed_tax_rules_f puei
76 where puei.assignment_id = pueo.assignment_id
77 and puei.effective_start_date <= p_final_process_date) ;
78
79 -- Fetching State Tax Rules details for the latest record for each state
80 -- that needs to be end dated as of New FPD
81
82
83 cursor c_get_statetax_rules(p_assignment_id in number,
84 p_final_process_date in date) is
85 select pueo.emp_state_tax_rule_id,
86 pueo.effective_start_date,
87 pueo.effective_end_date
88 from pay_us_emp_state_tax_rules_f pueo
89 where pueo.assignment_id = p_assignment_id
90 and pueo.effective_start_date = (select max(puei.effective_start_date)
91 from pay_us_emp_state_tax_rules_f puei
92 where puei.assignment_id = pueo.assignment_id
93 and puei.state_code = pueo.state_code
94 and puei.effective_start_date <= p_final_process_date) ;
95
96 -- Fetching County Tax Rules details for the latest record for each
97 -- State/County combination that needs to be end dated as of New FPD
98
99 cursor c_get_countytax_rules(p_assignment_id in number,
100 p_final_process_date in date) is
101 select pueo.emp_county_tax_rule_id,
102 pueo.effective_start_date,
103 pueo.effective_end_date
104 from pay_us_emp_county_tax_rules_f pueo
105 where pueo.assignment_id = p_assignment_id
106 and pueo.effective_start_date = (select max(puei.effective_start_date)
107 from pay_us_emp_county_tax_rules_f puei
108 where puei.assignment_id = pueo.assignment_id
109 and puei.state_code = pueo.state_code
110 and puei.county_code = pueo.county_code
111 and puei.effective_start_date <= p_final_process_date) ;
112
113 -- Fetching City Tax Rules details for the latest record for each
114 -- State/County/City combination that needs to be end dated as of New FPD
115
116 cursor c_get_citytax_rules(p_assignment_id in number,
117 p_final_process_date in date) is
118 select pueo.emp_city_tax_rule_id,
119 pueo.effective_start_date,
120 pueo.effective_end_date
121 from pay_us_emp_city_tax_rules_f pueo
122 where pueo.assignment_id = p_assignment_id
123 and pueo.effective_start_date = (select max(puei.effective_start_date)
124 from pay_us_emp_city_tax_rules_f puei
125 where puei.assignment_id = pueo.assignment_id
126 and puei.state_code = pueo.state_code
127 and puei.county_code = pueo.county_code
128 and puei.city_code = pueo.city_code
129 and puei.effective_start_date <= p_final_process_date) ;
130
131 -- To check whether Future Dated Payroll Actions existing
132
133 CURSOR c_compl_pay_act(p_new_final_process_date in date) IS
134 SELECT NULL
135 FROM pay_payroll_actions ppa
136 ,pay_assignment_actions paa
137 ,per_assignments_f asg
138 ,per_periods_of_service pds
139 WHERE pds.period_of_service_id = p_period_of_service_id
140 AND pds.person_id = asg.person_id
141 AND asg.period_of_service_id = pds.period_of_service_id
142 AND asg.assignment_id = paa.assignment_id
143 AND paa.payroll_action_id = ppa.payroll_action_id
144 AND ppa.action_type NOT IN ('X','BEE')
145 AND ppa.effective_date > p_new_final_process_date ;
146
147
148 l_person_id per_people_f.person_id%type ;
149 l_old_fpd per_periods_of_service.final_process_date%type ;
150 l_final_process_date per_periods_of_service.final_process_date%type ;
151 l_assignment_id per_assignments_f.assignment_id%type ;
152 l_emp_fed_tax_rule_id pay_us_emp_fed_tax_rules_f.emp_fed_tax_rule_id%type ;
153 l_emp_st_tax_rule_id pay_us_emp_state_tax_rules_f.emp_state_tax_rule_id%type ;
154 l_emp_county_tax_rule_id pay_us_emp_county_tax_rules_f.emp_county_tax_rule_id%type ;
155 l_emp_city_tax_rule_id pay_us_emp_city_tax_rules_f.emp_city_tax_rule_id%type ;
156 l_eff_st_dt date ;
157 l_eff_end_dt date ;
158 l_dummy varchar2(1) ;
159
160 begin
161
162 hr_utility.trace('Entering HR_US_PERSON_TERM_LEG_HOOK.update_tax_rules');
163 hr_utility.trace('p_period_of_service_id := '|| p_period_of_service_id) ;
164 hr_utility.trace('p_final_process_date := '||to_char(p_final_process_date,'dd/mon/yyyy')) ;
165
166 -- If FPD is entered NULL we need to end date Tax records as of 31st Dec,4712
167 If p_final_process_date IS NULL OR p_final_process_date = hr_api.g_date Then
168 l_final_process_date := fnd_date.canonical_to_date('4712/12/31 00:00:00') ;
169 Else
170 l_final_process_date := p_final_process_date ;
171 End If ;
172 hr_utility.trace('l_final_process_date := '||to_char(l_final_process_date,'dd/mon/yyyy')) ;
173
174 open c_get_person_oldfpd(p_period_of_service_id) ;
175 loop
176 fetch c_get_person_oldfpd into l_person_id, l_old_fpd ;
177 exit when c_get_person_oldfpd%NOTFOUND ;
178
179 hr_utility.trace('l_person_id := '||l_person_id) ;
180 hr_utility.trace('l_old_fpd := '||l_old_fpd) ;
181
182 If l_old_fpd IS NOT NULL Then
183 -- If Old FPD is Null proper End-Dating of Tax records will happen
184 -- by HR_EX_EMPLOYEE_BK2.FINAL_PROCESS_EMP_B so we are not updating Tax Records here
185
186 OPEN c_compl_pay_act(l_final_process_date) ;
187 FETCH c_compl_pay_act INTO l_dummy;
188 IF c_compl_pay_act%FOUND THEN
189 CLOSE c_compl_pay_act;
190 hr_utility.set_message(800,'HR_449742_EMP_FPD_PAYACT');
191 hr_utility.raise_error;
192 ELSE -- No Future Dated Payroll Action exists after the new Final Process Date
193
194 open c_get_assignments(l_person_id) ;
195 loop
196 fetch c_get_assignments into l_assignment_id ;
197 hr_utility.trace('l_assignment_id := '||l_assignment_id) ;
198 exit when c_get_assignments%NOTFOUND ;
199
200 open c_get_fedtax_rules(l_assignment_id, l_final_process_date ) ;
201 loop
202 fetch c_get_fedtax_rules into l_emp_fed_tax_rule_id, l_eff_st_dt, l_eff_end_dt ;
203 exit when c_get_fedtax_rules%NOTFOUND ;
204 hr_utility.trace('l_emp_fed_tax_rule_id := '||l_emp_fed_tax_rule_id) ;
205
206 -- Updating Fed Tax Rules
207 update pay_us_emp_fed_tax_rules_f
208 set effective_end_date = l_final_process_date
209 where emp_fed_tax_rule_id = l_emp_fed_tax_rule_id
210 and effective_start_date = l_eff_st_dt
211 and effective_end_date = l_eff_end_dt ;
212 end loop ;
213 close c_get_fedtax_rules ;
214
215 open c_get_statetax_rules(l_assignment_id, l_final_process_date) ;
216 loop
217 fetch c_get_statetax_rules into l_emp_st_tax_rule_id, l_eff_st_dt, l_eff_end_dt ;
218 exit when c_get_statetax_rules%NOTFOUND ;
219 hr_utility.trace('l_emp_st_tax_rule_id := '||l_emp_st_tax_rule_id) ;
220
221 -- Updating State Tax Rules
222 update pay_us_emp_state_tax_rules_f
223 set effective_end_date = l_final_process_date
224 where emp_state_tax_rule_id = l_emp_st_tax_rule_id
225 and effective_start_date = l_eff_st_dt
226 and effective_end_date = l_eff_end_dt ;
227 end loop ;
228 close c_get_statetax_rules ;
229
230 open c_get_countytax_rules(l_assignment_id, l_final_process_date) ;
231 loop
232 fetch c_get_countytax_rules into l_emp_county_tax_rule_id, l_eff_st_dt, l_eff_end_dt ;
233 exit when c_get_countytax_rules%NOTFOUND ;
234 hr_utility.trace('l_emp_county_tax_rule_id := '||l_emp_county_tax_rule_id) ;
235
236 -- Updating County Tax Rules
237 update pay_us_emp_county_tax_rules_f
238 set effective_end_date = l_final_process_date
239 where emp_county_tax_rule_id = l_emp_county_tax_rule_id
240 and effective_start_date = l_eff_st_dt
241 and effective_end_date = l_eff_end_dt ;
242
243 end loop ;
244 close c_get_countytax_rules ;
245
246 open c_get_citytax_rules(l_assignment_id, l_final_process_date) ;
247 loop
248 fetch c_get_citytax_rules into l_emp_city_tax_rule_id, l_eff_st_dt, l_eff_end_dt ;
249 exit when c_get_citytax_rules%NOTFOUND ;
250 hr_utility.trace('l_emp_city_tax_rule_id := '||l_emp_city_tax_rule_id) ;
251
252 -- Updating City Tax Rules
253 update pay_us_emp_city_tax_rules_f
254 set effective_end_date = l_final_process_date
255 where emp_city_tax_rule_id = l_emp_city_tax_rule_id
256 and effective_start_date = l_eff_st_dt
257 and effective_end_date = l_eff_end_dt ;
258 end loop ;
259 close c_get_citytax_rules ;
260 end loop ;
261 close c_get_assignments ;
262 END IF; -- Future Dated Payroll Action does not exist
263 CLOSE c_compl_pay_act ;
264 END IF ; -- Old FPD is not Null
265 end loop ;
266 close c_get_person_oldfpd ;
267 hr_utility.trace('Leaving HR_US_PERSON_TERM_LEG_HOOK.update_tax_rules');
268 end update_tax_rules ;
269
270 end HR_US_PERSON_TERM_LEG_HOOK ;
[Home] [Help]