1 PACKAGE BODY pay_us_update_tax_rec_pkg AS
2 /* $Header: pyusutxr.pkb 120.0 2005/05/29 10:07:53 appldev noship $ */
3
4 PROCEDURE terminate_emp_tax_records ( p_assignment_id NUMBER
5 ,p_process_date DATE
6 ,p_actual_termination_date DATE default NULL)
7 IS
8
9 l_new_end_date DATE;
10 l_ef_date DATE;
11 l_default_date DATE;
12 l_dt_mode VARCHAR2(30);
13 l_proc VARCHAR2(100) := 'pay_us_update_tax_rec_pkg.terminate_emp_tax_records';
14
15 CURSOR csr_tax_defaulting(p_asg_id NUMBER) is
16 select min(ftr.effective_start_date)
17 from pay_us_emp_fed_tax_rules_f ftr
18 where ftr.assignment_id = p_asg_id;
19
20
21 CURSOR csr_tax_entries(p_asg_id NUMBER,p_ef_date DATE) is
22 select pee.element_entry_id
23 from pay_element_entries_f pee,
24 pay_element_links_f pel,
25 pay_element_types_f pet
26 where pee.element_link_id = pel.element_link_id
27 and pel.element_type_id = pet.element_type_id
28 and p_ef_date between pet.effective_start_date and pet.effective_end_date
29 and p_ef_date between pel.effective_start_date and pel.effective_end_date
30 and p_ef_date between pee.effective_start_date and pee.effective_end_date
31 and (pet.element_name like '%VERTEX%'
32 or upper(pet.element_name) = 'WORKERS COMPENSATION')
33 and pee.assignment_id = p_asg_id;
34
35 BEGIN
36
37 hr_utility.trace('Entering: ' || l_proc);
38 open csr_tax_defaulting(p_assignment_id);
39 fetch csr_tax_defaulting into l_default_date;
40
41 if csr_tax_defaulting%NOTFOUND then
42 -- no defaulting, then nothing to do!
43 close csr_tax_defaulting;
44 return;
45 end if;
46
47 close csr_tax_defaulting;
48
49 if (p_process_date is null) then
50 if (p_actual_termination_date is null) then
51 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
52 hr_utility.set_message_token('PROCEDURE','PROCESS_US_TAX_RULES');
53 hr_utility.set_message_token('STEP',1);
54 hr_utility.raise_error;
55
56 else
57 l_new_end_date := hr_api.g_eot;
58 l_ef_date := p_actual_termination_date;
59 end if;
60 else
61 l_new_end_date := p_process_date;
62 l_ef_date := p_process_date;
63
64 end if;
65
66 if hr_general.chk_maintain_tax_records = 'Y' then
67
68 hr_utility.set_location(l_proc,6);
69 UPDATE pay_us_emp_fed_tax_rules_f peft
70 SET peft.effective_end_date = l_new_end_date
71 WHERE peft.assignment_id = p_assignment_id
72 AND l_ef_date
73 BETWEEN peft.effective_start_date AND peft.effective_end_date;
74 --
75 DELETE pay_us_emp_fed_tax_rules_f peft
76 WHERE peft.assignment_id = p_assignment_id
77 AND peft.effective_start_date > l_ef_date;
78 --
79 hr_utility.set_location(l_proc,7);
80 UPDATE pay_us_emp_state_tax_rules_f pest
81 SET pest.effective_end_date = l_new_end_date
82 WHERE pest.assignment_id = p_assignment_id
83 AND l_ef_date
84 BETWEEN pest.effective_start_date AND pest.effective_end_date;
85 --
86 DELETE pay_us_emp_state_tax_rules_f pest
87 WHERE pest.assignment_id = p_assignment_id
88 AND pest.effective_start_date > l_ef_date;
89 --
90 hr_utility.set_location(l_proc,8);
91 UPDATE pay_us_emp_county_tax_rules_f pect
92 SET pect.effective_end_date = l_new_end_date
93 WHERE pect.assignment_id = p_assignment_id
94 AND l_ef_date
95 BETWEEN pect.effective_start_date AND pect.effective_end_date;
96 --
97 DELETE pay_us_emp_county_tax_rules_f pect
98 WHERE pect.assignment_id = p_assignment_id
99 AND pect.effective_start_date > l_ef_date;
100 --
101 hr_utility.set_location(l_proc,9);
102 UPDATE pay_us_emp_city_tax_rules_f pecit
103 SET pecit.effective_end_date = l_new_end_date
104 WHERE pecit.assignment_id = p_assignment_id
105 AND l_ef_date
106 BETWEEN pecit.effective_start_date AND pecit.effective_end_date;
107 --
108 DELETE pay_us_emp_city_tax_rules_f pecit
109 WHERE pecit.assignment_id = p_assignment_id
110 AND pecit.effective_start_date > l_ef_date;
111 --
112 end if; -- maintaining tax records
113
114 if hr_utility.chk_product_install(p_product =>'Oracle Payroll',
115 p_legislation => 'US')
116 and p_process_date is null then
117
118 -- We need to clean out the Vertex Element Entries.
119 -- We don't want to end date them, just erase any future changes.
120 -- We don't do anything if final_process date is set, since
121 -- the element ending code will handle that case.
122
123 -- if defaulting has happened in the future, we want to zap the ee's
124 -- else we want to delete any changes after the ATD
125
126 if l_default_date > l_ef_date then
127 l_ef_date := l_default_date;
128 l_dt_mode := hr_api.g_zap;
129 else
130 l_dt_mode := hr_api.g_future_change;
131 end if;
132
133 hr_utility.set_location(l_proc,10);
134 for c_ele_rec in csr_tax_entries(p_assignment_id,l_ef_date)
135 loop
136
137 hr_entry_api.delete_element_entry(
138 p_dt_delete_mode => l_dt_mode,
139 p_session_date => l_ef_date,
140 p_element_entry_id => c_ele_rec.element_entry_id
141 );
142
143
144 end loop;
145
146 end if; -- payroll installed
147
148 hr_utility.trace(' Leaving: ' || l_proc);
149
150
151 EXCEPTION
152 when NO_DATA_FOUND then
153 NULL;
154
155 END terminate_emp_tax_records;
156
157
158 PROCEDURE reverse_term_emp_tax_records ( p_assignment_id NUMBER
159 ,p_process_date DATE)
160 IS
161 BEGIN
162 --
163 hr_utility.trace('Entered reverse_term_emp_tax_records for assign ' ||
164 p_assignment_id );
165 --
166 hr_utility.set_location
167 ('pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records',5);
168 UPDATE pay_us_emp_fed_tax_rules_f peft
169 SET peft.effective_end_date =
170 to_date('31/12/4712','DD/MM/YYYY')
171 WHERE peft.assignment_id = p_assignment_id
172 AND peft.effective_end_date = p_process_date;
173 --
174 hr_utility.set_location
175 ('pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records',6);
176 UPDATE pay_us_emp_state_tax_rules_f pest
177 SET pest.effective_end_date =
178 to_date('31/12/4712','DD/MM/YYYY')
179 WHERE pest.assignment_id = p_assignment_id
180 AND pest.effective_end_date = p_process_date;
181 --
182 hr_utility.set_location
183 ('pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records',7);
184 UPDATE pay_us_emp_county_tax_rules_f pect
185 SET pect.effective_end_date =
186 to_date('31/12/4712','DD/MM/YYYY')
187 WHERE pect.assignment_id = p_assignment_id
188 AND pect.effective_end_date = p_process_date;
189 --
190 hr_utility.set_location
191 ('pay_us_update_tax_rec_pkg.reverse_term_emp_tax_records',8);
192 UPDATE pay_us_emp_city_tax_rules_f pecit
193 SET pecit.effective_end_date =
194 to_date('31/12/4712','DD/MM/YYYY')
195 WHERE pecit.assignment_id = p_assignment_id
196 AND pecit.effective_end_date = p_process_date;
197 --
198 EXCEPTION
199 when NO_DATA_FOUND then
200 NULL;
201
202 END reverse_term_emp_tax_records;
203
204 end pay_us_update_tax_rec_pkg;
[Home] [Help]