[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PAY_JP_PRE_TAX_PKG
Source
1 PACKAGE BODY PAY_JP_PRE_TAX_PKG AS
2 /* $Header: pyjppret.pkb 120.5.12020000.8 2012/08/17 12:47:39 mdubasi ship $ */
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_assignment_id => p_assignment_id,
136 p_locking_assignment_action_id => p_locking_assignment_action_id,
137 p_locked_assignment_action_id => p_locked_assignment_action_id,
138 p_action_status => l_action_status,
139 p_message => l_message);
140
141 p_errbuf := substrb(l_message,1,255);
142 if l_action_status = 'C' then
143 p_retcode := '0';
144 elsif l_action_status = 'I' then
145 p_retcode := '1';
146 elsif l_action_status = 'E' then
147 p_retcode := '2';
148 end if;
149
150 if l_action_status = 'C' then
151
152 get_assignment_details(
153 p_errbuf => p_errbuf
154 ,p_retcode => p_retcode
155 ,p_assignment_id => p_assignment_id
156 ,p_effective_date => p_effective_date
157 ,p_person_id => l_person_id
158 ,p_period_of_service_id => l_period_of_service_id
159 ,p_date_start => l_date_start
160 ,p_leaving_reason => l_leaving_reason
161 ,p_actual_termination_date => l_actual_termination_date
162 ,p_employment_category => l_employment_category
163 );
164
165 if (l_person_id is not null) then
166
167 pay_action_information_api.create_action_information
168 (
169 p_action_information_id => l_action_info_id1
170 ,p_action_context_id => p_locking_assignment_action_id
171 ,p_action_context_type => 'AAP'
172 ,p_object_version_number => l_ovn
173 ,p_effective_date => p_effective_date
174 ,p_assignment_id => p_assignment_id
175 ,p_action_information_category => 'JP_PRE_TAX_1'
176 ,p_action_information1 => p_locked_assignment_action_id
177 ,p_action_information2 => fnd_number.number_to_canonical(p_value.taxable_sal_amt)
178 ,p_action_information3 => fnd_number.number_to_canonical(p_value.taxable_mat_amt)
179 ,p_action_information4 => fnd_number.number_to_canonical(l_person_id)
180 ,p_action_information5 => p_value.hi_org_id
181 ,p_action_information6 => fnd_number.number_to_canonical(p_value.hi_prem_ee)
182 ,p_action_information7 => fnd_number.number_to_canonical(p_value.hi_prem_er)
183 ,p_action_information8 => p_value.wp_org_id
184 ,p_action_information9 => fnd_number.number_to_canonical(p_value.wp_prem_ee)
185 ,p_action_information10 => fnd_number.number_to_canonical(p_value.wp_prem_er)
186 ,p_action_information11 => p_value.wpf_org_id
187 ,p_action_information12 => fnd_number.number_to_canonical(p_value.wpf_prem_ee)
188 ,p_action_information13 => p_value.salary_category
189 ,p_action_information14 => fnd_number.number_to_canonical(p_value.mutual_aid)
190 ,p_action_information15 => fnd_number.number_to_canonical(l_period_of_service_id)
191 ,p_action_information16 => fnd_date.date_to_canonical(l_date_start)
192 ,p_action_information17 => l_leaving_reason
193 ,p_action_information18 => fnd_date.date_to_canonical(l_actual_termination_date)
194 ,p_action_information19 => p_value.ui_org_id
195 ,p_action_information20 => fnd_number.number_to_canonical(p_value.ui_prem_ee)
196 ,p_action_information21 => p_value.itax_org_id
197 ,p_action_information22 => p_value.itax_category
198 ,p_action_information23 => p_value.itax_yea_category
199 ,p_action_information24 => fnd_number.number_to_canonical(p_value.itax)
200 ,p_action_information25 => fnd_number.number_to_canonical(p_value.itax_adjustment)
201 ,p_action_information29 => fnd_number.number_to_canonical(p_value.disaster_tax_reduction)
202 ,p_action_information30 => l_employment_category
203 );
204
205 pay_action_information_api.create_action_information
206 (
207 p_action_information_id => l_action_info_id2
208 ,p_action_context_id => p_locking_assignment_action_id
209 ,p_action_context_type => 'AAP'
210 ,p_object_version_number => l_ovn
211 ,p_effective_date => p_effective_date
212 ,p_assignment_id => p_assignment_id
213 ,p_action_information_category => 'JP_PRE_TAX_2'
214 ,p_action_information1 => p_locked_assignment_action_id
215 ,p_action_information3 => p_value.ltax_district_code
216 ,p_action_information5 => fnd_number.number_to_canonical(p_value.ltax)
217 ,p_action_information6 => fnd_number.number_to_canonical(p_value.ltax_lumpsum)
218 ,p_action_information7 => fnd_number.number_to_canonical(p_value.sp_ltax)
219 ,p_action_information8 => fnd_number.number_to_canonical(p_value.sp_ltax_income)
220 ,p_action_information9 => fnd_number.number_to_canonical(p_value.sp_ltax_shi)
221 ,p_action_information10 => fnd_number.number_to_canonical(p_value.sp_ltax_to)
222 ,p_action_information11 => fnd_number.number_to_canonical(p_value.ci_prem_ee)
223 ,p_action_information12 => fnd_number.number_to_canonical(p_value.ci_prem_er)
224 ,p_action_information14 => p_value.ui_category
225 ,p_action_information15 => p_value.sp_ltax_district_code
226 ,p_action_information16 => fnd_number.number_to_canonical(p_value.ui_sal_amt)
227 ,p_action_information17 => p_value.wai_org_id
228 ,p_action_information18 => p_value.wai_category
229 ,p_action_information19 => fnd_number.number_to_canonical(p_value.wai_sal_amt)
230 ,p_action_information20 => fnd_number.number_to_canonical(p_value.wpf_prem_er)
231 );
232
233 end if;
234
235 end if;
236
237 hr_utility.set_location('pay_jp_pre_tax_pkg.insert_row',20);
238
239 EXCEPTION
240
241 WHEN OTHERS THEN
242
243 hr_utility.set_location('pay_jp_pre_tax_pkg.insert_row',20);
244
245 p_errbuf := substrb(sqlerrm,1,255);
246 p_retcode := '2';
247
248 END insert_row;
249
250 BEGIN
251 hr_utility.set_location('pay_jp_pre_tax_pkg.run_assact',10);
252
253 open csr_assact;
254 fetch csr_assact into l_business_group_id,
255 l_assact_action_status,
256 l_date_earned,
257 l_effective_date,
258 l_assignment_id;
259 if csr_assact%NOTFOUND then
260 close csr_assact;
261 p_errbuf := error_message('ASSACT_NOT_FOUND');
262 p_retcode := '1';
263 return;
264 end if;
265 close csr_assact;
266
267 pay_jp_custom_pkg.fetch_values(
268 P_BUSINESS_GROUP_ID => l_business_group_id,
269 P_ASSIGNMENT_ACTION_ID => p_locked_assignment_action_id,
270 P_ASSIGNMENT_ID => l_assignment_id,
271 P_DATE_EARNED => l_date_earned,
272 P_VALUE => l_value);
273
274 insert_row(
275 p_errbuf => p_errbuf
276 ,p_retcode => p_retcode
277 ,p_locked_assignment_action_id => p_locked_assignment_action_id
278 ,p_locking_assignment_action_id => p_locking_assignment_action_id
279 ,p_assignment_id => l_assignment_id
280 ,p_effective_date => l_effective_date
281 ,p_value => l_value
282 );
283
284 hr_utility.set_location('pay_jp_pre_tax_pkg.run_assact',20);
285
286 EXCEPTION
287 when OTHERS then
288 p_errbuf := substrb(sqlerrm,1,255);
289 p_retcode := '2';
290 END RUN_ASSACT;
291
292 -----------------------------------------------------------------------
293 PROCEDURE REFRESH(
294 -----------------------------------------------------------------------
295 errbuf OUT NOCOPY VARCHAR2,
296 retcode OUT NOCOPY VARCHAR2)
297 IS
298 BEGIN
299 -- Delete rollbacked assignment_action_id.
300 delete pay_action_information pai
301 where (action_information_category='JP_PRE_TAX_1'
302 or action_information_category='JP_PRE_TAX_2')
303 and action_context_type='AAP'
304 and not exists(
305 select NULL
306 from pay_assignment_actions paa
307 where paa.assignment_action_id=pai.action_information1);
308
309 commit;
310
311 retcode := '0';
312 EXCEPTION
313 when OTHERS then
314 errbuf := substrb(sqlerrm,1,255);
315 retcode := '2';
316 END REFRESH;
317 --
318 /****************************************************************************
319 Name : chk_all_exclusions
320 Description : This fucntion checks if the assignment set passed has only
321 exclusions.
322 *****************************************************************************/
323 FUNCTION chk_all_exclusions(
324 p_assignment_set_id IN NUMBER) RETURN NUMBER
325 IS
326 l_dummy NUMBER;
327 BEGIN
328 SELECT 0
329 INTO l_dummy
330 FROM HR_ASSIGNMENT_SET_AMENDMENTS
331 WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
332 AND INCLUDE_OR_EXCLUDE = 'I'
333 AND ROWNUM = 1;
334 RETURN l_dummy;
335 EXCEPTION
336 WHEN NO_DATA_FOUND THEN
337 l_dummy := 1;
338 RETURN l_dummy;
339 END chk_all_exclusions;
340
341 /****************************************************************************
342 Name : get_amendment_flag
343 Description : This fucntion return the include_or_exclude flag for an
344 assignment id.
345 *****************************************************************************/
346 FUNCTION get_amendment_flag
347 (
348 p_assignment_id IN NUMBER,
349 p_assignment_set_id IN NUMBER
350 )
351 RETURN VARCHAR2 IS
352 l_inc_or_exc HR_ASSIGNMENT_SET_AMENDMENTS.INCLUDE_OR_EXCLUDE%TYPE;
353 --
354 BEGIN
355 SELECT INCLUDE_OR_EXCLUDE
356 INTO l_inc_or_exc
357 FROM HR_ASSIGNMENT_SET_AMENDMENTS
358 WHERE ASSIGNMENT_ID = p_assignment_id
359 AND ASSIGNMENT_SET_ID = p_assignment_set_id;
360 --
361 RETURN l_inc_or_exc;
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 RETURN 'ZZ';
365 END get_amendment_flag;
366
367 /****************************************************************************
368 Name : chk_ass_set
369 Description : This fucntion checks if for the passed assignment_id an
370 assignment action is to be created or not. It checks for
371 assignment set by criteria also taking into account if any
372 amendment is defined for that assignment id.
373 *****************************************************************************/
374 FUNCTION chk_ass_set(
375 p_assignment_id IN NUMBER,
376 p_assignment_set_id IN NUMBER,
377 p_formula_id IN NUMBER,
378 p_effective_date IN DATE,
379 p_dummy IN NUMBER) RETURN BOOLEAN
380 IS
381 l_result BOOLEAN;
382 l_amendment_flag HR_ASSIGNMENT_SET_AMENDMENTS.INCLUDE_OR_EXCLUDE%TYPE;
383 --
384 BEGIN
385 IF (p_dummy = 1)THEN
386 l_amendment_flag := get_amendment_flag(p_assignment_id,p_assignment_set_id);
387 IF (l_amendment_flag = 'ZZ') THEN
388 l_result := hr_jp_ast_utility_pkg.formula_validate(p_formula_id,p_assignment_id,p_effective_date,true);
389 ELSIF l_amendment_flag = 'E' THEN
390 l_result := false;
391 ELSIF l_amendment_flag = 'I' THEN
392 l_result := true;
393 END IF;
394 ELSE
395 l_result := hr_jp_ast_utility_pkg.formula_validate(p_formula_id,p_assignment_id,p_effective_date,true);
396 END IF;
397 --
398 RETURN l_result;
399 --
400 EXCEPTION
401 WHEN OTHERS THEN
402 hr_utility.set_location('chk_ass_set'||substr(sqlerrm,1,200),99);
403 RETURN FALSE;
404 END chk_ass_set;
405 --
406
407 /****************************************************************************
408 Name : chk_ass_set_mixed
409 Description : This fucntion checks if the assignment set passed is based
410 on both criteria and amendment or not.
411 *****************************************************************************/
412 FUNCTION chk_ass_set_mixed(
413 p_assignment_set_id IN NUMBER) RETURN NUMBER
414 IS
415 l_dummy NUMBER;
416 --
417 BEGIN
418 SELECT 1
419 INTO l_dummy
420 FROM HR_ASSIGNMENT_SET_AMENDMENTS
421 WHERE ASSIGNMENT_SET_ID = p_assignment_set_id
422 AND ROWNUM = 1
423 AND EXISTS ( select null from hr_assignment_sets
424 where ASSIGNMENT_SET_ID = p_assignment_set_id
425 AND FORMULA_ID IS NULL
426 );
427 RETURN l_dummy;
428 EXCEPTION
429 WHEN NO_DATA_FOUND THEN
430 l_dummy := 0;
431 RETURN l_dummy;
432 END chk_ass_set_mixed;
433 --
434 -----------------------------------------------------------------------
435 PROCEDURE ROLLBACK_ASSACT(
436 -----------------------------------------------------------------------
437 p_errbuf OUT NOCOPY VARCHAR2,
438 p_retcode OUT NOCOPY VARCHAR2,
439 p_business_group_id IN pay_payroll_actions.business_group_id%TYPE,
440 p_payroll_id IN pay_all_payrolls_f.payroll_id%TYPE,
441 p_from_date IN DATE,
442 p_to_date IN DATE) IS
443 --
444
445 CURSOR csr_assact IS
446 SELECT DISTINCT paa1.assignment_action_id, ppa1.payroll_action_id
447 FROM pay_payroll_actions ppa, pay_assignment_actions paa,
448 pay_payroll_actions ppa1, pay_assignment_actions paa1,
449 pay_action_information pai
450 WHERE ppa.business_group_id = p_business_group_id
451 AND ppa.payroll_id = p_payroll_id
452 AND ppa.date_earned BETWEEN p_from_date AND p_to_date
453 AND ppa.payroll_action_id = paa.payroll_action_id
454 AND pai.action_information_category = 'JP_PRE_TAX_1'
455 AND pai.action_context_type = 'AAP'
456 AND pai.action_information1 = paa.assignment_action_id
457 AND pai.action_context_id = paa1.assignment_action_id
458 AND paa1.payroll_action_id = ppa1.payroll_action_id
459 AND ppa1.business_group_id = p_business_group_id
460 AND ppa1.action_type = 'X';
461
462 TYPE t_assact_rec IS RECORD(
463 payroll_action_id pay_payroll_actions.payroll_action_id%TYPE,
464 assignment_action_id pay_assignment_actions.assignment_action_id%TYPE);
465
466 TYPE t_assact_tab IS TABLE OF t_assact_rec INDEX BY BINARY_INTEGER;
467
468 l_assact_tab t_assact_tab;
469
470 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
471 l_index NUMBER := 1;
472 l_count NUMBER;
473 l_count1 NUMBER; -- Bug 13772840
474
475 BEGIN
476
477 l_assact_tab.DELETE;
478
479 for l_assact_rec in csr_assact
480 loop
481
482 l_assact_tab(l_index).payroll_action_id := l_assact_rec.payroll_action_id;
483 l_assact_tab(l_index).assignment_action_id := l_assact_rec.assignment_action_id;
484 l_index := l_index+1;
485
486 end loop;
487
488 if (l_index<>1) then
489
490 for l_count in 1..l_assact_tab.COUNT
491 loop
492
493 py_rollback_pkg.rollback_ass_action(
494 p_assignment_action_id=>l_assact_tab(l_count).assignment_action_id);
495 -- Bug 13772840
496 -- end loop;
497
498 -- l_count := l_assact_tab.COUNT;
499
500 l_payroll_action_id := l_assact_tab(l_count).payroll_action_id;
501
502 SELECT COUNT(1)
503 INTO l_count1 -- Bug 13772840
504 FROM pay_assignment_actions
505 WHERE payroll_action_id = l_payroll_action_id;
506
507 IF (l_count1=0) THEN -- Bug 13772840
508 py_rollback_pkg.rollback_payroll_action(
509 p_payroll_action_id=>l_payroll_action_id);
510 END IF;
511
512 end loop; -- Bug 13772840
513
514 commit;
515
516 end if;
517
518 -- Bug 13772840
519 -- Delete from pay_action_information when insertion happens via "Run" button in PAYJPPTX form.
520
521 DELETE FROM pay_action_information pai
522 WHERE (pai.action_information_category='JP_PRE_TAX_1'
523 OR pai.action_information_category='JP_PRE_TAX_2')
524 AND pai.action_context_type='AAP'
525 AND exists ( select null
526 from pay_assignment_actions paa,pay_payroll_actions ppa
527 where paa.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
528 and ppa.business_group_id = p_business_group_id
529 and ppa.date_earned BETWEEN p_from_date AND p_to_date --Bug 14094779
530 and paa.ASSIGNMENT_ACTION_ID = pai.ACTION_INFORMATION1
531 and ppa.payroll_id = p_payroll_id );
532
533 commit;
534
535 p_retcode := '0';
536
537 EXCEPTION
538 when OTHERS then
539 p_errbuf := substrb(sqlerrm,1,255);
540 p_retcode := '2';
541
542 END ROLLBACK_ASSACT;
543 --
544
545 -------------------------------------------------------------------------
546 PROCEDURE ROLLBACK_ASSACT_ASGSET(
547 -----------------------------------------------------------------------
548 p_errbuf OUT NOCOPY VARCHAR2,
549 p_retcode OUT NOCOPY VARCHAR2,
550 p_business_group_id IN pay_payroll_actions.business_group_id%TYPE,
551 p_payroll_id IN pay_payroll_actions.business_group_id%TYPE,
552 p_assignment_set_id IN hr_assignment_sets.assignment_set_id%TYPE,
553 p_from_date IN varchar2,
554 p_to_date IN varchar2) IS
555 --
556 l_from_date date;
557 l_to_date date;
558
559 CURSOR csr_assact(p_assignment_id NUMBER)
560 IS
561 SELECT DISTINCT paa1.assignment_action_id, ppa1.payroll_action_id
562 FROM pay_payroll_actions ppa, pay_assignment_actions paa,
563 pay_payroll_actions ppa1, pay_assignment_actions paa1,
564 pay_action_information pai
565 WHERE ppa.business_group_id = p_business_group_id
566 AND ppa.payroll_id = p_payroll_id
567 AND ppa.date_earned BETWEEN l_from_date AND l_to_date
568 AND ppa.payroll_action_id = paa.payroll_action_id
569 AND pai.action_information_category = 'JP_PRE_TAX_1'
570 AND pai.action_context_type = 'AAP'
571 AND pai.action_information1 = paa.assignment_action_id
572 AND pai.action_context_id = paa1.assignment_action_id
573 AND paa1.payroll_action_id = ppa1.payroll_action_id
574 AND ppa1.business_group_id = p_business_group_id
575 AND ppa1.action_type = 'X'
576 AND pai.assignment_id = p_assignment_id;
577
578 CURSOR csr_asgset_assact IS
579 SELECT DISTINCT ASSIGNMENT_ID
580 FROM per_all_assignments_f
581 WHERE l_from_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
582 OR l_to_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
583 AND PAYROLL_ID = p_payroll_id
584 AND BUSINESS_GROUP_ID = p_business_group_id;
585
586 TYPE t_temp_rec IS RECORD(
587 assignment_id per_all_assignments_f.assignment_id%TYPE);
588
589 TYPE t_temp_table IS TABLE OF t_temp_rec
590 INDEX BY BINARY_INTEGER;
591
592 l_temp_table t_temp_table;
593
594 TYPE t_assact_rec IS RECORD(
595 payroll_action_id pay_payroll_actions.payroll_action_id%TYPE,
596 assignment_action_id pay_assignment_actions.assignment_action_id%TYPE);
597
598 TYPE t_assact_tab IS TABLE OF t_assact_rec INDEX BY BINARY_INTEGER;
599
600 l_assact_tab t_assact_tab;
601
602 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
603 l_index NUMBER := 1;
604 l_index1 NUMBER;
605 l_count NUMBER;
606 l_count1 NUMBER; -- Bug 13772840
607
608 l_errbuf VARCHAR2(255);
609 l_retcode VARCHAR2(1);
610
611 l_ass_set_id NUMBER;
612 l_result1 VARCHAR2(30);
613 l_result2 BOOLEAN;
614 l_formula_id NUMBER;
615 g_all_exclusions_flag NUMBER;
616 g_dummy NUMBER;
617
618
619 BEGIN
620
621 l_from_date := FND_DATE.CANONICAL_TO_DATE(p_from_date);
622 l_to_date := FND_DATE.CANONICAL_TO_DATE(p_to_date);
623 l_ass_set_id := p_assignment_set_id;
624
625 IF p_assignment_set_id IS NULL THEN
626
627 ROLLBACK_ASSACT(l_errbuf,l_retcode,p_business_group_id,p_payroll_id,l_from_date,l_to_date);
628
629 p_errbuf := l_errbuf;
630 p_retcode := l_retcode;
631
632 ELSE
633
634 g_dummy := chk_ass_set_mixed(l_ass_set_id);
635 g_all_exclusions_flag := chk_all_exclusions(l_ass_set_id);
636
637 -- assignment set is passed as parameter
638 SELECT formula_id INTO l_formula_id
639 FROM hr_assignment_sets
640 WHERE assignment_set_id = l_ass_set_id;
641
642 l_assact_tab.DELETE;
643
644 for i in csr_asgset_assact
645 loop
646
647 IF l_formula_id IS NULL THEN
648 -- assignment set by ammmendment passed
649 IF (g_all_exclusions_flag = 0) THEN
650 -- assignment set by ammmendment passed is not all exclusions.
651 l_result1 := get_amendment_flag(i.assignment_id, l_ass_set_id);
652 IF (l_result1 = 'I') THEN
653
654 l_temp_table(l_index).assignment_id := i.assignment_id ;
655 l_index := l_index+1;
656
657 END IF;
658 --
659 ELSE
660 -- assignment set by ammmendment passed is all exclusions.
661 l_result1 := get_amendment_flag(i.assignment_id, l_ass_set_id);
662 IF (l_result1 <> 'E') THEN
663
664 l_temp_table(l_index).assignment_id := i.assignment_id ;
665 l_index := l_index+1;
666
667 END IF;
668 --
669 END IF;
670 --
671 ELSE
672 -- assignment set by criteria passed
673 l_result2 := chk_ass_set(i.assignment_id, l_ass_set_id, l_formula_id,l_from_date, g_dummy);
674 IF (l_result2 = TRUE) THEN
675
676 l_temp_table(l_index).assignment_id := i.assignment_id ;
677 l_index := l_index+1;
678
679 END IF;
680 --
681 END IF;
682
683 end loop;
684
685 l_index1 := l_index;
686 l_index := 1;
687
688 if (l_index1<>1) then
689 for i IN 1 .. l_temp_table.COUNT
690 loop
691 for l_assact_rec in csr_assact(l_temp_table(i).assignment_id)
692 loop
693 l_assact_tab(l_index).payroll_action_id := l_assact_rec.payroll_action_id;
694 l_assact_tab(l_index).assignment_action_id := l_assact_rec.assignment_action_id;
695 l_index := l_index+1;
696 end loop;
697 end loop;
698 end if;
699
700 if (l_index<>1) then
701
702 for l_count in 1..l_assact_tab.COUNT
703 loop
704
705 py_rollback_pkg.rollback_ass_action(
706 p_assignment_action_id=>l_assact_tab(l_count).assignment_action_id);
707
708 l_payroll_action_id := l_assact_tab(l_count).payroll_action_id;
709
710 SELECT COUNT(1)
711 INTO l_count1
712 FROM pay_assignment_actions
713 WHERE payroll_action_id = l_payroll_action_id;
714
715 IF (l_count1=0) THEN
716 py_rollback_pkg.rollback_payroll_action(
717 p_payroll_action_id=>l_payroll_action_id);
718 END IF;
719
720 end loop;
721
722 end if;
723
724 for i IN 1 .. l_temp_table.COUNT
725 loop
726 -- Bug 13772840
727 -- Delete from pay_action_information when insertion happens via "Run" button in PAYJPPTX form.
728
729 DELETE FROM pay_action_information pai
730 WHERE (pai.action_information_category='JP_PRE_TAX_1'
731 OR pai.action_information_category='JP_PRE_TAX_2')
732 AND pai.action_context_type='AAP'
733 AND exists ( select null
734 from pay_assignment_actions paa,pay_payroll_actions ppa
735 where paa.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
736 and ppa.business_group_id = p_business_group_id
737 and ppa.date_earned BETWEEN l_from_date AND l_to_date --Bug 14094779
738 and paa.ASSIGNMENT_ACTION_ID = pai.ACTION_INFORMATION1
739 and ppa.payroll_id = p_payroll_id )
740 AND pai.assignment_id = l_temp_table(i).assignment_id;
741
742 end loop;
743
744 commit;
745
746 p_retcode := '0';
747
748 END IF;
749
750 EXCEPTION
751 when OTHERS then
752 p_errbuf := substrb(sqlerrm,1,255);
753 p_retcode := '2';
754
755 END ROLLBACK_ASSACT_ASGSET;
756 --
757 -----------------------------------------------------------------------
758 PROCEDURE RUN_SINGLE_ASSACT(
759 -----------------------------------------------------------------------
760 p_errbuf OUT NOCOPY VARCHAR2,
761 p_retcode OUT NOCOPY VARCHAR2,
762 p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE) IS
763
764 l_count NUMBER;
765
766 l_errbuf VARCHAR2(255);
767 l_retcode CHAR(1) := '0';
768
769 BEGIN
770
771 SELECT COUNT(1)
772 INTO l_count
773 FROM pay_action_information
774 WHERE action_information_category = 'JP_PRE_TAX_1'
775 AND action_context_type = 'AAP'
776 AND action_information1 = p_assignment_action_id;
777
778 if (l_count=0) then
779
780 run_assact(
781 p_errbuf => l_errbuf,
782 p_retcode => l_retcode,
783 p_locked_assignment_action_id => p_assignment_action_id,
784 p_locking_assignment_action_id => p_assignment_action_id);
785
786 if (NVL(l_retcode,'0')='0') then
787 commit;
788 end if;
789
790 end if;
791
792 p_errbuf := l_errbuf;
793 p_retcode := l_retcode;
794
795 EXCEPTION
796 when OTHERS then
797 p_errbuf := substrb(sqlerrm,1,255);
798 p_retcode := '2';
799
800 END RUN_SINGLE_ASSACT;
801 --
802 END PAY_JP_PRE_TAX_PKG;