[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_PRE_TAX_PKG
Source
1 PACKAGE BODY PAY_JP_PRE_TAX_PKG AS
2 /* $Header: pyjppret.pkb 120.5 2006/09/14 13:39:17 sgottipa noship $ */
3
4 -----------------------------------------------------------------------
5 FUNCTION ERROR_MESSAGE(p_error_name VARCHAR2) RETURN VARCHAR2
6 -----------------------------------------------------------------------
7 IS
8 l_message VARCHAR2(255);
9 BEGIN
10 if p_error_name = 'INVALID_MODE' then
11 fnd_message.set_name('PAY','PAY_JP_INVALID_MODE');
12 l_message := fnd_message.get;
13 elsif p_error_name = 'ASSACT_NOT_FOUND' then
14 fnd_message.set_name('PAY','PAY_JP_ASSACT_NOT_EXISTS');
15 l_message := fnd_message.get;
16 elsif p_error_name = 'ASSACT_STATUS_UP' then
17 fnd_message.set_name('PAY','PAY_JP_ASSACT_PROCESSING');
18 l_message := fnd_message.get;
19 elsif p_error_name = 'ASSACT_STATUS_UPC' then
20 fnd_message.set_name('PAY','PAY_JP_ASSACT_PROC_COMPLETED');
21 l_message := fnd_message.get;
22 else
23 l_message := NULL;
24 end if;
25
26 return l_message;
27 END ERROR_MESSAGE;
28
29 -----------------------------------------------------------------------
30 PROCEDURE RUN_ASSACT(
31 -----------------------------------------------------------------------
32 p_errbuf OUT NOCOPY VARCHAR2,
33 p_retcode OUT NOCOPY VARCHAR2,
34 p_locked_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
35 p_locking_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE )
36 IS
37 l_effective_date pay_payroll_actions.effective_date%TYPE;
38 l_value pay_jp_custom_pkg.value_rec;
39 l_business_group_id pay_payroll_actions.business_group_id%TYPE;
40 l_assact_action_status pay_assignment_actions.action_status%TYPE;
41 l_date_earned pay_payroll_actions.date_earned%TYPE;
42 l_assignment_id pay_assignment_actions.assignment_id%TYPE;
43
44 ----------------------------------------
45 -- Cursor
46 ----------------------------------------
47 CURSOR csr_assact IS
48 select ppa.business_group_id,
49 paa.action_status ASSACT_ACTION_STATUS,
50 ppa.date_earned,
51 ppa.effective_date,
52 paa.assignment_id
53 from pay_payroll_actions ppa,
54 pay_assignment_actions paa
55 where paa.assignment_action_id=p_locked_assignment_action_id
56 and paa.action_status='C'
57 and ppa.payroll_action_id=paa.payroll_action_id
58 and ppa.action_type in ('R','Q','B','I')
59 for update of paa.assignment_action_id;
60 --
61 PROCEDURE get_assignment_details(
62 p_errbuf OUT NOCOPY VARCHAR2,
63 p_retcode OUT NOCOPY VARCHAR2,
64 p_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
65 p_effective_date IN pay_payroll_actions.effective_date%TYPE,
66 p_person_id OUT NOCOPY per_all_assignments_f.person_id%TYPE,
67 p_period_of_service_id OUT NOCOPY per_all_assignments_f.period_of_service_id%TYPE,
68 p_date_start OUT NOCOPY per_periods_of_service.date_start%TYPE,
69 p_leaving_reason OUT NOCOPY per_periods_of_service.leaving_reason%TYPE,
70 p_actual_termination_date OUT NOCOPY per_periods_of_service.actual_termination_date%TYPE,
71 p_employment_category OUT NOCOPY per_all_assignments_f.employment_category%TYPE)
72 IS
73
74 BEGIN
75
76 hr_utility.set_location('pay_jp_pre_tax_pkg.get_assignment_details',10);
77
78 select asg.person_id,
79 asg.period_of_service_id,
80 pds.date_start,
81 pds.leaving_reason,
82 pds.actual_termination_date,
83 asg.employment_category
84 into p_person_id,
85 p_period_of_service_id,
86 p_date_start,
87 p_leaving_reason,
88 p_actual_termination_date,
89 p_employment_category
90 from per_all_assignments_f asg,
91 per_periods_of_service pds
92 where asg.assignment_id = p_assignment_id
93 and p_effective_date between asg.effective_start_date and asg.effective_end_date
94 and pds.period_of_service_id = asg.period_of_service_id;
95
96 EXCEPTION
97
98 WHEN OTHERS THEN
99
100 hr_utility.set_location('pay_jp_pre_tax_pkg.get_assignment_details',20);
101
102 p_errbuf := substrb(sqlerrm,1,255);
103 p_retcode := '2';
104
105 END get_assignment_details;
106 --
107 PROCEDURE insert_row(
108 p_errbuf OUT NOCOPY VARCHAR2,
109 p_retcode OUT NOCOPY VARCHAR2,
110 p_locked_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
111 p_locking_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
112 p_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
113 p_effective_date IN pay_payroll_actions.effective_date%TYPE,
114 p_value IN pay_jp_custom_pkg.value_rec)
115 IS
116 l_action_status VARCHAR2(1);
117 l_message VARCHAR2(255);
118 l_person_id per_all_assignments_f.person_id%TYPE;
119 l_period_of_service_id per_all_assignments_f.period_of_service_id%TYPE;
120 l_date_start per_periods_of_service.date_start%TYPE;
121 l_leaving_reason per_periods_of_service.leaving_reason%TYPE;
122 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
123 l_employment_category per_all_assignments_f.employment_category%TYPE;
124
125 l_action_info_id1 pay_action_information.action_information_id%TYPE;
126 l_action_info_id2 pay_action_information.action_information_id%TYPE;
127 l_ovn pay_action_information.object_version_number%TYPE;
128
129 BEGIN
130
131 hr_utility.set_location('pay_jp_pre_tax_pkg.insert_row',10);
132
133 pay_jp_custom_pkg.validate_record(
134 p_value => p_value,
135 p_action_status => l_action_status,
136 p_message => l_message);
137
138 p_errbuf := substrb(l_message,1,255);
139 if l_action_status = 'C' then
140 p_retcode := '0';
141 elsif l_action_status = 'I' then
142 p_retcode := '1';
143 elsif l_action_status = 'E' then
144 p_retcode := '2';
145 end if;
146
147 if l_action_status = 'C' then
148
149 get_assignment_details(
150 p_errbuf => p_errbuf
151 ,p_retcode => p_retcode
152 ,p_assignment_id => p_assignment_id
153 ,p_effective_date => p_effective_date
154 ,p_person_id => l_person_id
155 ,p_period_of_service_id => l_period_of_service_id
156 ,p_date_start => l_date_start
157 ,p_leaving_reason => l_leaving_reason
158 ,p_actual_termination_date => l_actual_termination_date
159 ,p_employment_category => l_employment_category
160 );
161
162 if (l_person_id is not null) then
163
164 pay_action_information_api.create_action_information
165 (
166 p_action_information_id => l_action_info_id1
167 ,p_action_context_id => p_locking_assignment_action_id
168 ,p_action_context_type => 'AAP'
169 ,p_object_version_number => l_ovn
170 ,p_effective_date => p_effective_date
171 ,p_assignment_id => p_assignment_id
172 ,p_action_information_category => 'JP_PRE_TAX_1'
173 ,p_action_information1 => p_locked_assignment_action_id
174 ,p_action_information2 => fnd_number.number_to_canonical(p_value.taxable_sal_amt)
175 ,p_action_information3 => fnd_number.number_to_canonical(p_value.taxable_mat_amt)
176 ,p_action_information4 => fnd_number.number_to_canonical(l_person_id)
177 ,p_action_information5 => p_value.hi_org_id
178 ,p_action_information6 => fnd_number.number_to_canonical(p_value.hi_prem_ee)
179 ,p_action_information7 => fnd_number.number_to_canonical(p_value.hi_prem_er)
180 ,p_action_information8 => p_value.wp_org_id
181 ,p_action_information9 => fnd_number.number_to_canonical(p_value.wp_prem_ee)
182 ,p_action_information10 => fnd_number.number_to_canonical(p_value.wp_prem_er)
183 ,p_action_information11 => p_value.wpf_org_id
184 ,p_action_information12 => fnd_number.number_to_canonical(p_value.wpf_prem_ee)
185 ,p_action_information13 => p_value.salary_category
186 ,p_action_information14 => fnd_number.number_to_canonical(p_value.mutual_aid)
187 ,p_action_information15 => fnd_number.number_to_canonical(l_period_of_service_id)
188 ,p_action_information16 => fnd_date.date_to_canonical(l_date_start)
189 ,p_action_information17 => l_leaving_reason
190 ,p_action_information18 => fnd_date.date_to_canonical(l_actual_termination_date)
191 ,p_action_information19 => p_value.ui_org_id
192 ,p_action_information20 => fnd_number.number_to_canonical(p_value.ui_prem_ee)
193 ,p_action_information21 => p_value.itax_org_id
194 ,p_action_information22 => p_value.itax_category
195 ,p_action_information23 => p_value.itax_yea_category
196 ,p_action_information24 => fnd_number.number_to_canonical(p_value.itax)
197 ,p_action_information25 => fnd_number.number_to_canonical(p_value.itax_adjustment)
198 ,p_action_information29 => fnd_number.number_to_canonical(p_value.disaster_tax_reduction)
199 ,p_action_information30 => l_employment_category
200 );
201
202 pay_action_information_api.create_action_information
203 (
204 p_action_information_id => l_action_info_id2
205 ,p_action_context_id => p_locking_assignment_action_id
206 ,p_action_context_type => 'AAP'
207 ,p_object_version_number => l_ovn
208 ,p_effective_date => p_effective_date
209 ,p_assignment_id => p_assignment_id
210 ,p_action_information_category => 'JP_PRE_TAX_2'
211 ,p_action_information1 => p_locked_assignment_action_id
212 ,p_action_information3 => p_value.ltax_district_code
213 ,p_action_information5 => fnd_number.number_to_canonical(p_value.ltax)
214 ,p_action_information6 => fnd_number.number_to_canonical(p_value.ltax_lumpsum)
215 ,p_action_information7 => fnd_number.number_to_canonical(p_value.sp_ltax)
216 ,p_action_information8 => fnd_number.number_to_canonical(p_value.sp_ltax_income)
217 ,p_action_information9 => fnd_number.number_to_canonical(p_value.sp_ltax_shi)
218 ,p_action_information10 => fnd_number.number_to_canonical(p_value.sp_ltax_to)
219 ,p_action_information11 => fnd_number.number_to_canonical(p_value.ci_prem_ee)
220 ,p_action_information12 => fnd_number.number_to_canonical(p_value.ci_prem_er)
221 ,p_action_information14 => p_value.ui_category
222 ,p_action_information15 => p_value.sp_ltax_district_code
223 ,p_action_information16 => fnd_number.number_to_canonical(p_value.ui_sal_amt)
224 ,p_action_information17 => p_value.wai_org_id
225 ,p_action_information18 => p_value.wai_category
226 ,p_action_information19 => fnd_number.number_to_canonical(p_value.wai_sal_amt)
227 ,p_action_information20 => fnd_number.number_to_canonical(p_value.wpf_prem_er)
228 );
229
230 end if;
231
232 end if;
233
234 hr_utility.set_location('pay_jp_pre_tax_pkg.insert_row',20);
235
236 EXCEPTION
237
238 WHEN OTHERS THEN
239
240 hr_utility.set_location('pay_jp_pre_tax_pkg.insert_row',20);
241
242 p_errbuf := substrb(sqlerrm,1,255);
243 p_retcode := '2';
244
245 END insert_row;
246
247 BEGIN
248 hr_utility.set_location('pay_jp_pre_tax_pkg.run_assact',10);
249
250 open csr_assact;
251 fetch csr_assact into l_business_group_id,
252 l_assact_action_status,
253 l_date_earned,
254 l_effective_date,
255 l_assignment_id;
256 if csr_assact%NOTFOUND then
257 close csr_assact;
258 p_errbuf := error_message('ASSACT_NOT_FOUND');
259 p_retcode := '1';
260 return;
261 end if;
262 close csr_assact;
263
264 pay_jp_custom_pkg.fetch_values(
265 P_BUSINESS_GROUP_ID => l_business_group_id,
266 P_ASSIGNMENT_ACTION_ID => p_locked_assignment_action_id,
267 P_ASSIGNMENT_ID => l_assignment_id,
268 P_DATE_EARNED => l_date_earned,
269 P_VALUE => l_value);
270
271 insert_row(
272 p_errbuf => p_errbuf
273 ,p_retcode => p_retcode
274 ,p_locked_assignment_action_id => p_locked_assignment_action_id
275 ,p_locking_assignment_action_id => p_locking_assignment_action_id
276 ,p_assignment_id => l_assignment_id
277 ,p_effective_date => l_effective_date
278 ,p_value => l_value
279 );
280
281 hr_utility.set_location('pay_jp_pre_tax_pkg.run_assact',20);
282
283 EXCEPTION
284 when OTHERS then
285 p_errbuf := substrb(sqlerrm,1,255);
286 p_retcode := '2';
287 END RUN_ASSACT;
288
289 -----------------------------------------------------------------------
290 PROCEDURE REFRESH(
291 -----------------------------------------------------------------------
292 errbuf OUT NOCOPY VARCHAR2,
293 retcode OUT NOCOPY VARCHAR2)
294 IS
295 BEGIN
296 -- Delete rollbacked assignment_action_id.
297 delete pay_action_information pai
298 where (action_information_category='JP_PRE_TAX_1'
299 or action_information_category='JP_PRE_TAX_2')
300 and action_context_type='AAP'
301 and not exists(
302 select NULL
303 from pay_assignment_actions paa
304 where paa.assignment_action_id=pai.action_information1);
305
306 commit;
307
308 retcode := '0';
309 EXCEPTION
310 when OTHERS then
311 errbuf := substrb(sqlerrm,1,255);
312 retcode := '2';
313 END REFRESH;
314 --
315 -----------------------------------------------------------------------
316 PROCEDURE ROLLBACK_ASSACT(
317 -----------------------------------------------------------------------
318 p_errbuf OUT NOCOPY VARCHAR2,
319 p_retcode OUT NOCOPY VARCHAR2,
320 p_business_group_id IN pay_payroll_actions.business_group_id%TYPE,
321 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE,
322 p_from_date IN DATE,
323 p_to_date IN DATE) IS
324 --
325
326 CURSOR csr_assact IS
327 SELECT DISTINCT paa1.assignment_action_id, ppa1.payroll_action_id
328 FROM pay_payroll_actions ppa, pay_assignment_actions paa,
329 pay_payroll_actions ppa1, pay_assignment_actions paa1,
330 pay_action_information pai
331 WHERE ppa.business_group_id = p_business_group_id
332 AND ppa.payroll_id = p_payroll_id
333 AND ppa.date_earned BETWEEN p_from_date AND p_to_date
334 AND ppa.payroll_action_id = paa.payroll_action_id
335 AND pai.action_information_category = 'JP_PRE_TAX_1'
336 AND pai.action_context_type = 'AAP'
337 AND pai.action_information1 = paa.assignment_action_id
338 AND pai.action_context_id = paa1.assignment_action_id
339 AND paa1.payroll_action_id = ppa1.payroll_action_id
340 AND ppa1.business_group_id = p_business_group_id
341 AND ppa1.action_type = 'X';
342
343 TYPE t_assact_rec IS RECORD(
344 payroll_action_id pay_payroll_actions.payroll_action_id%TYPE,
345 assignment_action_id pay_assignment_actions.assignment_action_id%TYPE);
346
347 TYPE t_assact_tab IS TABLE OF t_assact_rec INDEX BY BINARY_INTEGER;
348
349 l_assact_tab t_assact_tab;
350
351 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
352 l_index NUMBER := 1;
353 l_count NUMBER;
354
355 BEGIN
356
357 l_assact_tab.DELETE;
358
359 for l_assact_rec in csr_assact
360 loop
361
362 l_assact_tab(l_index).payroll_action_id := l_assact_rec.payroll_action_id;
363 l_assact_tab(l_index).assignment_action_id := l_assact_rec.assignment_action_id;
364 l_index := l_index+1;
365
366 end loop;
367
368 if (l_index<>1) then
369
370 for l_count in 1..l_assact_tab.COUNT
371 loop
372
373 py_rollback_pkg.rollback_ass_action(
374 p_assignment_action_id=>l_assact_tab(l_count).assignment_action_id);
375
376 end loop;
377
378 l_count := l_assact_tab.COUNT;
379
380 l_payroll_action_id := l_assact_tab(l_count).payroll_action_id;
381
382 SELECT COUNT(1)
383 INTO l_count
384 FROM pay_assignment_actions
385 WHERE payroll_action_id = l_payroll_action_id;
386
387 IF (l_count=0) THEN
388 py_rollback_pkg.rollback_payroll_action(
389 p_payroll_action_id=>l_payroll_action_id);
390 END IF;
391
392 commit;
393
394 end if;
395
396 p_retcode := '0';
397
398 EXCEPTION
399 when OTHERS then
400 p_errbuf := substrb(sqlerrm,1,255);
401 p_retcode := '2';
402
403 END ROLLBACK_ASSACT;
404 --
405 -----------------------------------------------------------------------
406 PROCEDURE RUN_SINGLE_ASSACT(
407 -----------------------------------------------------------------------
408 p_errbuf OUT NOCOPY VARCHAR2,
409 p_retcode OUT NOCOPY VARCHAR2,
410 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE) IS
411
412 l_count NUMBER;
413
414 l_errbuf VARCHAR2(255);
415 l_retcode CHAR(1) := '0';
416
417 BEGIN
418
419 SELECT COUNT(1)
420 INTO l_count
421 FROM pay_action_information
422 WHERE action_information_category = 'JP_PRE_TAX_1'
423 AND action_context_type = 'AAP'
424 AND action_information1 = p_assignment_action_id;
425
426 if (l_count=0) then
427
428 run_assact(
429 p_errbuf => l_errbuf,
430 p_retcode => l_retcode,
431 p_locked_assignment_action_id => p_assignment_action_id,
432 p_locking_assignment_action_id => p_assignment_action_id);
433
434 if (NVL(l_retcode,'0')='0') then
435 commit;
436 end if;
437
438 end if;
439
440 p_errbuf := l_errbuf;
441 p_retcode := l_retcode;
442
443 EXCEPTION
444 when OTHERS then
445 p_errbuf := substrb(sqlerrm,1,255);
446 p_retcode := '2';
447
448 END RUN_SINGLE_ASSACT;
449 --
450 END PAY_JP_PRE_TAX_PKG;