[Home] [Help]
PACKAGE BODY: APPS.PAY_PL_POST_TERMINATION_PKG
Source
1 PACKAGE BODY PAY_PL_POST_TERMINATION_PKG as
2 /* $Header: pyplterm.pkb 120.0 2006/03/01 22:24:40 mseshadr noship $ */
3
4 PROCEDURE Actual_Term_sii_tax_records(p_debug boolean default false
5 ,p_period_of_service_id number
6 ,p_actual_termination_date Date
7 ,p_business_group_id NUMBER
8 ) is
9
10
11 cursor csr_term_normal_assignments is
12 select min(paaf.effective_start_date),
13 paaf.assignment_id,
14 paaf.person_id
15 from per_all_assignments_f paaf,
16 per_assignment_status_types past,
17 hr_soft_coding_keyflex kflex
18 where period_of_service_id=p_period_of_service_id
19 and past.ASSIGNMENT_STATUS_TYPE_ID=paaf.ASSIGNMENT_STATUS_TYPE_ID
20 and PER_SYSTEM_STATUS='TERM_ASSIGN'
21 and kflex.soft_coding_keyflex_id=paaf.soft_coding_keyflex_id
22 and kflex.segment3='NORMAL'
23 group by paaf.assignment_id,paaf.person_id;
24
25
26 cursor csr_paye_details(r_per_or_asg_id number,
27 r_contract_category varchar2,
28 r_effective_date date ) is
29 select effective_start_date,
30 paye_details_id,
31 tax_reduction,
32 tax_calc_with_spouse_child,
33 income_reduction,
34 income_reduction_amount,
35 rate_of_tax,
36 object_version_number
37 from pay_pl_paye_details_f
38 where contract_category=r_contract_category
39 and per_or_asg_id =r_per_or_asg_id
40 and ( r_effective_date is null or
41 r_effective_date between effective_start_date and effective_end_date
42 )
43 and rownum=1;
44 --r_effective_date to be passed as null to fetch terminated record
45
46
47 cursor csr_sii_details(r_per_or_asg_id number,
48 r_contract_category varchar2,
49 r_effective_date date ) is
50 select effective_start_date,sii_details_id,
51 emp_social_security_info,object_version_number,
52 old_age_contribution,pension_contribution,
53 sickness_contribution,work_injury_contribution,
54 labor_contribution,health_contribution,
55 unemployment_contribution,old_age_cont_end_reason,
56 pension_cont_end_reason,sickness_cont_end_reason,
57 work_injury_cont_end_reason,labor_fund_cont_end_reason,
58 health_cont_end_reason,unemployment_cont_end_reason
59 from pay_pl_sii_details_f
60 where per_or_asg_id=r_per_or_asg_id
61 and contract_category=r_contract_category
62 and ( r_effective_date is null or
63 r_effective_date between effective_start_date and effective_end_date
64 )
65 and rownum=1;
66 --r_effective_date to be passed as null to fetch terminated record
67 --terminated records cannot be updated,so it wont fetch more than one row
68 --anyways
69
70
71 l_csr_sii_details csr_sii_details%rowtype;
72 l_csr_paye_details csr_paye_details%rowtype;
73
74 l_temp_ovn number;
75 l_temp_details_id number;
76 l_temp_effective_date_warning boolean;
77 l_temp_effective_start_date pay_pl_paye_details_f.effective_start_date%type;
78 l_temp_effective_end_date pay_pl_paye_details_f.effective_end_date%type;
79
80 l_proc varchar2(55);
81 BEGIN
82 --
83 -- we need to create sii/tax records on Employee termination also
84 -- bug:4644778
85 -- 1)Get list of all terminated Normal Assignments for this period_of_service_id
86 -- 2)Check if there exists Terminated SII and Terminated Tax Records.(no date join)
87 -- Since these Terminated Tax records can be only corrected,there should be
88 -- only record for every sii_details_id or paye_details_id
89 -- a)If exists,check if the actual_termination_date+1 is after the effective_start_date of record
90 -- if yes,no record needs to be created
91 -- if no,(actual_termination_date is before the record's effective_start_date)
92 -- copy the record,delete the record,create a new record with same details
93 -- as of actual_termination_date+1
94 -- b)if not exists
95 -- check if there exists Normal record as on Actual_termination_date
96 -- if yes,copy the record,create terminated sii/tax record
97 -- if no,null
98 --
99
100 if p_debug then
101 l_proc:='PAY_PL_POST_TERMINATION_PKG.Actual_Term_sii_tax_records';
102 hr_utility.set_location('Entering: '||l_proc, 10);
103 end if;
104
105
106 for i in csr_term_normal_assignments loop
107
108
109 --Check for Paye details
110 --Check for Sii details
111 if p_debug then
112 hr_utility.trace('Term Normal Assignment Found '||i.assignment_id);
113 end if;
114 open csr_paye_details(r_per_or_asg_id => i.assignment_id,
115 r_contract_category => 'TERM_NORMAL' ,
116 r_effective_date => null );
117
118 fetch csr_paye_details into l_csr_paye_details;
119 if csr_paye_details%found then
120 close csr_paye_details;
121 if ((p_actual_termination_date+1)< l_csr_paye_details.effective_start_date) then
122 -- 18th
123 -- Assgn terminated.paye record created
124 --then 15th
125 --Employee terminated here
126 --copy the record in loc var,zap the record,create rec from 15th
127 pay_pl_paye_api.delete_pl_paye_details
128 ( p_validate =>false
129 ,p_effective_date =>l_csr_paye_details.effective_start_date
130 ,p_datetrack_delete_mode =>'ZAP'
131 ,p_paye_details_id =>l_csr_paye_details.paye_details_id
132 ,p_object_version_number =>l_csr_paye_details.object_version_number
133 ,p_effective_start_date =>l_temp_effective_start_date
134 ,p_effective_end_date =>l_temp_effective_end_date
135 );
136 pay_pl_paye_api.create_pl_paye_details
137 ( p_validate =>false
138 ,p_effective_date =>(p_actual_termination_date+1)
139 ,p_contract_category => 'TERM_NORMAL'
140 ,p_per_or_asg_id => i.assignment_id
141 ,p_business_group_id => p_business_group_id
142 ,p_tax_reduction => l_csr_paye_details.tax_reduction
143 ,p_tax_calc_with_spouse_child => l_csr_paye_details.tax_calc_with_spouse_child
144 ,p_income_reduction => l_csr_paye_details.income_reduction
145 ,p_income_reduction_amount => l_csr_paye_details.income_reduction_amount
146 ,p_rate_of_tax => l_csr_paye_details.rate_of_tax
147 ,p_paye_details_id => l_temp_details_id --out var
148 ,p_object_version_number => l_temp_ovn --out var
149 ,p_effective_start_date => l_temp_effective_start_date --out var
150 ,p_effective_end_date => l_temp_effective_end_date --out var
151 ,p_effective_date_warning => l_temp_effective_date_warning --out var
152 );
153 --else
154 --null;
155
156
157 end if;
158
159 else --csr_paye_details%found terminated record
160 hr_utility.set_location(l_proc,25);
161 close csr_paye_details;
162
163 open csr_paye_details(r_per_or_asg_id => i.person_id ,
164 r_contract_category => 'NORMAL',
165 r_effective_date => p_actual_termination_date+1);
166 fetch csr_paye_details into l_csr_paye_details;
167 if csr_paye_details%FOUND then
168 close csr_paye_details;
169 pay_pl_paye_api.create_pl_paye_details
170 (p_validate => false
171 ,p_effective_date => p_actual_termination_date+1
172 ,p_contract_category => 'TERM_NORMAL'
173 ,p_per_or_asg_id => i.assignment_id
174 ,p_business_group_id => p_business_group_id
175 ,p_tax_reduction => l_csr_paye_details.tax_reduction
176 ,p_tax_calc_with_spouse_child => l_csr_paye_details.tax_calc_with_spouse_child
177 ,p_income_reduction => l_csr_paye_details.income_reduction
178 ,p_income_reduction_amount => l_csr_paye_details.income_reduction_amount
179 ,p_rate_of_tax => l_csr_paye_details.rate_of_tax
180 ,p_paye_details_id => l_temp_details_id --out var
181 ,p_object_version_number => l_temp_ovn --out var
182 ,p_effective_start_date => l_temp_effective_start_date --out var
183 ,p_effective_end_date => l_temp_effective_end_date --out var
184 ,p_effective_date_warning => l_temp_effective_date_warning --out var
185 );
186 else
187 close csr_paye_details;
188 end if;--csr_paye_details found non-terminated record
189
190 end if;--csr_paye_details%found terminated record
191 /*******************Paye record done******************/
192
193
194 if p_debug then
195 hr_utility.set_location(l_proc,30);
196 end if;
197 /***************Sii record********************/
198 open csr_sii_details(r_per_or_asg_id => i.assignment_id,
199 r_contract_category => 'TERM_NORMAL' ,
200 r_effective_date => null );
201
202 fetch csr_sii_details into l_csr_sii_details;
203 if csr_sii_details%found then
204 hr_utility.set_location(l_proc,40);
205 close csr_sii_details;
206 if ((p_actual_termination_date+1)< l_csr_sii_details.effective_start_date) then
207 -- 18th
208 -- Assgn terminated.Sii record created
209 --then 15th
210 --Employee terminated here
211 --copy the record in loc var,zap the record,create rec from 15th
212 pay_pl_sii_api.delete_pl_sii_details
213 ( p_validate =>false
214 ,p_effective_date =>l_csr_sii_details.effective_start_date
215 ,p_datetrack_delete_mode =>'ZAP'
216 ,p_sii_details_id =>l_csr_sii_details.sii_details_id
217 ,p_object_version_number =>l_csr_sii_details.object_version_number
218 ,p_effective_start_date =>l_temp_effective_start_date
219 ,p_effective_end_date =>l_temp_effective_end_date
220 );
221 pay_pl_sii_api.create_pl_sii_details
222 ( p_validate => false
223 ,p_effective_date => (p_actual_termination_date+1)
224 ,p_contract_category => 'TERM_NORMAL'
225 ,p_per_or_asg_id => i.assignment_id
226 ,p_business_group_id => p_business_group_id
227 ,p_emp_social_security_info => l_csr_sii_details.emp_social_security_info
228 ,p_old_age_contribution => l_csr_sii_details.old_age_contribution
229 ,p_pension_contribution => l_csr_sii_details.pension_contribution
230 ,p_sickness_contribution => l_csr_sii_details.sickness_contribution
231 ,p_work_injury_contribution => l_csr_sii_details.work_injury_contribution
232 ,p_labor_contribution => l_csr_sii_details.labor_contribution
233 ,p_health_contribution => l_csr_sii_details.health_contribution
234 ,p_unemployment_contribution => l_csr_sii_details.unemployment_contribution
235 ,p_old_age_cont_end_reason => l_csr_sii_details.old_age_cont_end_reason
236 ,p_pension_cont_end_reason => l_csr_sii_details.pension_cont_end_reason
237 ,p_sickness_cont_end_reason => l_csr_sii_details.sickness_cont_end_reason
238 ,p_work_injury_cont_end_reason => l_csr_sii_details.work_injury_cont_end_reason
239 ,p_labor_fund_cont_end_reason => l_csr_sii_details.labor_fund_cont_end_reason
240 ,p_health_cont_end_reason => l_csr_sii_details.health_cont_end_reason
241 ,p_unemployment_cont_end_reason => l_csr_sii_details.unemployment_cont_end_reason
242 ,p_sii_details_id => l_temp_details_id --out var
243 ,p_object_version_number => l_temp_ovn --out var
244 ,p_effective_start_date => l_temp_effective_start_date --out var
245 ,p_effective_end_date => l_temp_effective_end_date --out var
246 ,p_effective_date_warning => l_temp_effective_date_warning --out var
247 );
248 --else
249 --null;
250
251
252 end if;
253
254 else --csr_sii_details%found 1 terminated record
255 hr_utility.set_location(l_proc,45);
256 close csr_sii_details;--terminated one is closed
257
258 open csr_sii_details(r_per_or_asg_id => i.person_id ,
259 r_contract_category => 'NORMAL',
260 r_effective_date => p_actual_termination_date+1);
261 fetch csr_sii_details into l_csr_sii_details;
262 if csr_sii_details%FOUND then
263 close csr_sii_details;
264
265 pay_pl_sii_api.create_pl_sii_details
266 ( p_validate => false
267 ,p_effective_date => (p_actual_termination_date+1)
268 ,p_contract_category => 'TERM_NORMAL'
269 ,p_per_or_asg_id => i.assignment_id
270 ,p_business_group_id => p_business_group_id
271 ,p_emp_social_security_info => l_csr_sii_details.emp_social_security_info
272 ,p_old_age_contribution => l_csr_sii_details.old_age_contribution
273 ,p_pension_contribution => l_csr_sii_details.pension_contribution
274 ,p_sickness_contribution => l_csr_sii_details.sickness_contribution
275 ,p_work_injury_contribution => l_csr_sii_details.work_injury_contribution
276 ,p_labor_contribution => l_csr_sii_details.labor_contribution
277 ,p_health_contribution => l_csr_sii_details.health_contribution
278 ,p_unemployment_contribution => l_csr_sii_details.unemployment_contribution
279 ,p_old_age_cont_end_reason => l_csr_sii_details.old_age_cont_end_reason
280 ,p_pension_cont_end_reason => l_csr_sii_details.pension_cont_end_reason
281 ,p_sickness_cont_end_reason => l_csr_sii_details.sickness_cont_end_reason
282 ,p_work_injury_cont_end_reason => l_csr_sii_details.work_injury_cont_end_reason
283 ,p_labor_fund_cont_end_reason => l_csr_sii_details.labor_fund_cont_end_reason
284 ,p_health_cont_end_reason => l_csr_sii_details.health_cont_end_reason
285 ,p_unemployment_cont_end_reason => l_csr_sii_details.unemployment_cont_end_reason
286 ,p_sii_details_id => l_temp_details_id --out var
287 ,p_object_version_number => l_temp_ovn --out var
288 ,p_effective_start_date => l_temp_effective_start_date --out var
289 ,p_effective_end_date => l_temp_effective_end_date --out var
290 ,p_effective_date_warning => l_temp_effective_date_warning --out var
291 );
292 else
293 close csr_sii_details;--non terminated one closed
297 if p_debug then
294 end if;--csr_sii_details found-- non-terminated record
295
296 end if;--csr_sii_details%found terminated record
298 hr_utility.set_location(l_proc,60);
299 end if;
300 end loop;
301
302 if p_debug then
303 hr_utility.set_location('Leaving: '||l_proc, 100);
304 end if;
305
306 End Actual_term_sii_tax_records;--End of procedure
307
308
309 end PAY_PL_POST_TERMINATION_PKG;
310