1 PACKAGE BODY HR_CA_PERSON_TERM_LEG_HOOK AS
2 /* $Header: pycaterm.pkb 120.0.12010000.1 2008/11/12 09:23:24 sneelapa noship $ */
3
4 /* +======================================================================+
5 | Copyright (c) 2003 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9 Package Name : hr_ca_person_term_leg_hook
10 Package File Name : pycaterm.pkb
11
12 Description : This package will be called before Process Hook
13 hr_periods_of_service_bk1.update_pds_details_b for CA
14 legislation. It is used to correctly End Date the Tax
15 Records (Federal / Provincial) as per the
16 Final Process Date entered by the user.
17
18 Change List:
19 ------------
20 Name Date Version Bug Text
21 ------------- ----------- --------- ------- ------------------------------
22 sneelapa 24-APR-2008 115.0 Initial version.
23
24 */
25
26 procedure update_tax_rules(p_period_of_service_id in number,
27 p_final_process_date in date) is
28
29 /* Fetching Old Final Process Date and Person ID From Period of Service */
30
31 cursor c_get_person_oldfpd(p_period_of_service_id in number) is
32 select distinct pds.person_id,
33 pds.final_process_date
34 from per_periods_of_service pds
35 where pds.period_of_service_id = p_period_of_service_id ;
36
37 /* Getting all Terminated Assignments for the Person concerned */
38
39 cursor c_get_assignments(p_person_id in number) is
40 select distinct assignment_id
41 from per_assignments_f paf_o,
42 per_assignment_status_types past
43 where paf_o.person_id = p_person_id
44 and paf_o.assignment_status_type_id = past.assignment_status_type_id
45 and past.per_system_status = 'TERM_ASSIGN'
46 and ((past.business_group_id is null
47 and past.legislation_code is null)
48 OR (past.business_group_id is null
49 and past.legislation_code = 'CA')
50 OR (past.legislation_code is null
51 and exists
52 (select 'x'
53 from per_assignments_f paf_a
54 where paf_a.assignment_id = paf_o.assignment_id
55 and paf_a.business_group_id = past.business_group_id)
56 )
57 )
58 and paf_o.effective_end_date = (select max(paf_i.effective_end_date)
59 from per_assignments_f paf_i
60 where paf_i.assignment_id = paf_o.assignment_id) ;
61
62 -- Fetching Federal Tax Rules details the latest record that needs to be
63 -- end dated as of New FPD
64
65 cursor c_get_fedtax_rules(p_assignment_id in number,
66 p_final_process_date in date) is
67 select pueo.emp_fed_tax_inf_id,
68 pueo.effective_start_date,
69 pueo.effective_end_date
70 from pay_ca_emp_fed_tax_info_f pueo
71 where pueo.assignment_id = p_assignment_id
72 and pueo.effective_start_date = (select max(puei.effective_start_date)
73 from pay_ca_emp_fed_tax_info_f puei
74 where puei.assignment_id = pueo.assignment_id
75 and puei.effective_start_date <= p_final_process_date) ;
76
77
78 -- Fetching Provincial Tax Rules details for the latest record
79 -- that needs to be end dated as of New FPD
80
81 cursor c_get_provtax_rules(p_assignment_id in number,
82 p_final_process_date in date) is
83 select pueo.emp_province_tax_inf_id,
84 pueo.effective_start_date,
85 pueo.effective_end_date
86 from pay_ca_emp_prov_tax_info_f pueo
87 where pueo.assignment_id = p_assignment_id
88 and pueo.effective_start_date = (select max(puei.effective_start_date)
89 from pay_ca_emp_prov_tax_info_f puei
90 where puei.assignment_id = pueo.assignment_id
91 and puei.province_code = pueo.province_code
92 and puei.effective_start_date <= p_final_process_date) ;
93
94
95 -- To check whether Future Dated Payroll Actions existing
96
97 CURSOR c_compl_pay_act(p_new_final_process_date in date) IS
98 SELECT NULL
99 FROM pay_payroll_actions ppa
100 ,pay_assignment_actions paa
101 ,per_assignments_f asg
102 ,per_periods_of_service pds
103 WHERE pds.period_of_service_id = p_period_of_service_id
104 AND pds.person_id = asg.person_id
105 AND asg.period_of_service_id = pds.period_of_service_id
106 AND asg.assignment_id = paa.assignment_id
107 AND paa.payroll_action_id = ppa.payroll_action_id
108 AND ppa.action_type NOT IN ('X','BEE')
109 AND ppa.effective_date > p_new_final_process_date ;
110
111
112 l_person_id per_people_f.person_id%type ;
113 l_old_fpd per_periods_of_service.final_process_date%type ;
114 l_final_process_date per_periods_of_service.final_process_date%type ;
115 l_assignment_id per_assignments_f.assignment_id%type ;
116 l_emp_fed_tax_inf_id pay_ca_emp_fed_tax_info_f.emp_fed_tax_inf_id%type ;
117 l_emp_prov_tax_inf_id pay_ca_emp_prov_tax_info_f.emp_province_tax_inf_id%type ;
118 l_eff_st_dt date ;
119 l_eff_end_dt date ;
120 l_dummy varchar2(1) ;
121
122 begin
123
124 hr_utility.trace('Entering HR_CA_PERSON_TERM_LEG_HOOK.update_tax_rules');
125 hr_utility.trace('p_period_of_service_id := '|| p_period_of_service_id) ;
126 hr_utility.trace('p_final_process_date := '||to_char(p_final_process_date,'dd/mon/yyyy')) ;
127
128 -- If FPD is entered NULL we need to end date Tax records as of 31st Dec,4712
129 If p_final_process_date IS NULL OR p_final_process_date = hr_api.g_date Then
130 l_final_process_date := fnd_date.canonical_to_date('4712/12/31 00:00:00') ;
131 Else
132 l_final_process_date := p_final_process_date ;
133 End If ;
134 hr_utility.trace('l_final_process_date := '||to_char(l_final_process_date,'dd/mon/yyyy')) ;
135
136 open c_get_person_oldfpd(p_period_of_service_id) ;
137 loop
138 fetch c_get_person_oldfpd into l_person_id, l_old_fpd ;
139 exit when c_get_person_oldfpd%NOTFOUND ;
140
141 hr_utility.trace('l_person_id := '||l_person_id) ;
142 hr_utility.trace('l_old_fpd := '||l_old_fpd) ;
143
144 If l_old_fpd IS NOT NULL Then
145 -- If Old FPD is Null proper End-Dating of Tax records will happen
146 -- by HR_EX_EMPLOYEE_BK2.FINAL_PROCESS_EMP_B so we are not updating Tax Records here
147
148 OPEN c_compl_pay_act(l_final_process_date) ;
149 FETCH c_compl_pay_act INTO l_dummy;
150 IF c_compl_pay_act%FOUND THEN
151 CLOSE c_compl_pay_act;
152 hr_utility.set_message(800,'HR_449742_EMP_FPD_PAYACT');
153 hr_utility.raise_error;
154 ELSE -- No Future Dated Payroll Action exists after the new Final Process Date
155
156 open c_get_assignments(l_person_id) ;
157 loop
158 fetch c_get_assignments into l_assignment_id ;
159 hr_utility.trace('l_assignment_id := '||l_assignment_id) ;
160 exit when c_get_assignments%NOTFOUND ;
161
162 open c_get_fedtax_rules(l_assignment_id, l_final_process_date ) ;
163 loop
164 fetch c_get_fedtax_rules into l_emp_fed_tax_inf_id, l_eff_st_dt, l_eff_end_dt ;
165 exit when c_get_fedtax_rules%NOTFOUND ;
166 hr_utility.trace('l_emp_fed_tax_inf_id := '||l_emp_fed_tax_inf_id) ;
167
168 -- Updating Fed Tax Rules
169 update pay_ca_emp_fed_tax_info_f
170 set effective_end_date = l_final_process_date
171 where emp_fed_tax_inf_id = l_emp_fed_tax_inf_id
172 and effective_start_date = l_eff_st_dt
173 and effective_end_date = l_eff_end_dt ;
174 end loop ;
175
176 close c_get_fedtax_rules ;
177
178 open c_get_provtax_rules(l_assignment_id, l_final_process_date) ;
179 loop
180 fetch c_get_provtax_rules into l_emp_prov_tax_inf_id, l_eff_st_dt, l_eff_end_dt ;
181 exit when c_get_provtax_rules%NOTFOUND ;
182 hr_utility.trace('l_emp_prov_tax_inf_id := '||l_emp_prov_tax_inf_id) ;
183
184 -- Updating State Tax Rules
185 update pay_ca_emp_prov_tax_info_f
186 set effective_end_date = l_final_process_date
187 where emp_province_tax_inf_id = l_emp_prov_tax_inf_id
188 and effective_start_date = l_eff_st_dt
189 and effective_end_date = l_eff_end_dt ;
190 end loop ;
191 close c_get_provtax_rules ;
192
193 end loop ;
194 close c_get_assignments ;
195 END IF; -- Future Dated Payroll Action does not exist
196 CLOSE c_compl_pay_act ;
197
198 END IF ; -- Old FPD is not Null
199
200 end loop ;
201
202 close c_get_person_oldfpd ;
203
204 hr_utility.trace('Leaving HR_CA_PERSON_TERM_LEG_HOOK.update_tax_rules');
205 end update_tax_rules ;
206
207 end HR_CA_PERSON_TERM_LEG_HOOK ;
[Home] [Help]