[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_SOE_PKG
Source
1 PACKAGE BODY pay_jp_soe_pkg AS
2 /* $Header: pyjpsoe.pkb 120.3 2006/12/11 07:29:13 ttagawa noship $ */
3 --
4 -- Constants
5 --
6 c_nonres CONSTANT VARCHAR2(80) := 'Non Resident';
7 --
8 -- Global variables.
9 --
10 TYPE id_t IS RECORD(
11 SAL_ITAX_CATEGORY_IV NUMBER,
12 SAL_NR_ITAX_IV NUMBER,
13 BON_ITAX_CATEGORY_IV NUMBER,
14 BON_NR_ITAX_IV NUMBER,
15 SP_BON_ITAX_CATEGORY_IV NUMBER,
16 SP_BON_NR_ITAX_IV NUMBER,
17 YEA_ITAX_CATEGORY_IV NUMBER,
18 YEA_CATEGORY_IV NUMBER);
19 /*
20 --
21 -- Only Resident balances except for allowance balance.
22 --
23 SAL_ALLOWANCE_BAL NUMBER,
24 SAL_SAL_TAXABLE_BAL NUMBER,
25 SAL_MAT_TAXABLE_BAL NUMBER,
26 BON_ALLOWANCE_BAL NUMBER,
27 BON_SAL_TAXABLE_BAL NUMBER,
28 BON_MAT_TAXABLE_BAL NUMBER,
29 SP_BON_ALLOWANCE_BAL NUMBER,
30 SP_BON_SAL_TAXABLE_BAL NUMBER,
31 SP_BON_MAT_TAXABLE_BAL NUMBER,
32 SI_PREM_BAL NUMBER,
33 ITAX_BAL NUMBER,
34 YEA_ITAX_BAL NUMBER);
35 */
36 g_id id_t;
37 g_defined_balance_lst pay_balance_pkg.t_balance_value_tab;
38 -------------------------------------------------------------------------------
39 FUNCTION messages_exist_flag(
40 p_source_id IN NUMBER,
41 p_source_type IN VARCHAR2) RETURN VARCHAR2
42 -------------------------------------------------------------------------------
43 -- Returns 'Y' or 'N' which indicates message lines exist or not in
44 -- PAY_MESSAGE_LINES table. In most environment, you would specify
45 -- p_source_type = 'A' when source_id means assignment_action_id.
46 -------------------------------------------------------------------------------
47 IS
48 l_messages_exist VARCHAR2(1);
49 CURSOR csr_messages_exist IS
50 select 'Y'
51 from dual
52 where exists(
53 select NULL
54 from pay_message_lines pml
55 where pml.source_id = p_source_id
56 and pml.source_type = p_source_type);
57 BEGIN
58 open csr_messages_exist;
59 fetch csr_messages_exist into l_messages_exist;
60 if csr_messages_exist%NOTFOUND then
61 l_messages_exist := 'N';
62 end if;
63 close csr_messages_exist;
64 --
65 -- Return value.
66 --
67 return l_messages_exist;
68 END messages_exist_flag;
69 -------------------------------------------------------------------------------
70 FUNCTION retro_entries_processed_flag(p_creator_id IN NUMBER) RETURN VARCHAR2
71 -------------------------------------------------------------------------------
72 -- Returns 'Y' or 'N' which indicates retro entries are processed or not by
73 -- subsequent assignment actions.
74 -- p_creator_id is assignment action with action_type = 'G'.
75 -- If one of entries created by retro assignment action is unprocessed,
76 -- this function returns 'N'.
77 -------------------------------------------------------------------------------
78 IS
79 l_entries_processed VARCHAR2(1);
80
81 -- Added by Shashi
82 -- This is used to check the applicability of Advanced Retropay at Business Group Level
83 l_use_advanced_retropay HR_ORGANIZATION_INFORMATION.ORG_INFORMATION4%TYPE;
84
85 CURSOR csr_retro_entries_processed IS
86 select 'N'
87 from dual
88 where exists(
89 select 1
90 from pay_run_results prr,
91 pay_element_entries_f pee,
92 pay_assignment_actions paa
93 where pee.creator_id = p_creator_id
94 and paa.assignment_id = pee.assignment_id
95 and paa.assignment_action_id = p_creator_id
96 and pee.creator_type = 'R'
97 and prr.source_id(+) = pee.element_entry_id
98 --
99 -- Necessary to specify source_type because source_type of
100 -- reversal assignment action means source run_result_id.
101 --
102 and prr.source_type(+) = 'E'
103 and nvl(prr.status,'U') = 'U'
104 and rownum =1);
105
106 -- Below cursor is added to check the status of entry processing for Advanced Retropay.
107 -- Added by Shashi on 4th April, 2005
108 ----------START-------------
109
110 CURSOR csr_adv_ret_entries_processed IS
111 select 'N'
112 from dual
113 where exists(
114 select 1
115 from pay_run_results prr,
116 pay_element_entries_f pee,
117 pay_assignment_actions paa
118 where pee.creator_id = p_creator_id
119 and paa.assignment_id = pee.assignment_id
120 and paa.assignment_action_id = p_creator_id
121 and (pee.creator_type = 'RR' or pee.creator_type = 'EE')
122 and prr.source_id(+) = pee.element_entry_id
123 --
124 -- Necessary to specify source_type because source_type of
125 -- reversal assignment action means source run_result_id.
126 --
127 and prr.source_type(+) = 'E'
128 and nvl(prr.status,'U') = 'U'
129 and rownum =1);
130
131 ------------END-------------
132
133 BEGIN
134
135 -- Below query is used to get the usage of advanced retropay at business group level.
136 -- Added by Shashi on 4th April, 2005
137 ----------START-------------
138 BEGIN
139
140 SELECT NVL(org.org_information4, 'N')
141 INTO l_use_advanced_retropay
142 FROM pay_assignment_actions paa,
143 hr_organization_information org,
144 per_all_assignments asg
145 WHERE paa.assignment_action_id = p_creator_id
146 AND paa.assignment_id = asg.assignment_id
147 AND org.organization_id = asg.business_group_id
148 AND org.org_information_context LIKE 'JP_BUSINESS_GROUP_INFO';
149
150 EXCEPTION
151
152 WHEN NO_DATA_FOUND THEN
153
154 l_use_advanced_retropay := 'N';
155 WHEN OTHERS THEN
156 hr_utility.set_location('Error in retro_entries_processed_flag',99);
157 raise;
158
159 END;
160 ------------END-------------
161
162 if l_use_advanced_retropay = 'N' then
163
164 open csr_retro_entries_processed;
165 fetch csr_retro_entries_processed into l_entries_processed;
166 if csr_retro_entries_processed%NOTFOUND then
167 l_entries_processed := 'Y';
168 end if;
169 close csr_retro_entries_processed;
170
171 else
172
173 open csr_adv_ret_entries_processed;
174 fetch csr_adv_ret_entries_processed into l_entries_processed;
175 if csr_adv_ret_entries_processed%NOTFOUND then
176 l_entries_processed := 'Y';
177 end if;
178 close csr_adv_ret_entries_processed;
179
180 end if;
181
182 --
183 -- Return value.
184 --
185 return l_entries_processed;
186 END retro_entries_processed_flag;
187 -------------------------------------------------------------------------------
188 FUNCTION entry_processed_flag(
189 p_element_entry_id IN NUMBER,
190 p_effective_start_date IN DATE,
191 p_effective_end_date IN DATE) RETURN VARCHAR2
192 -------------------------------------------------------------------------------
193 -- Returns 'Y' or 'N' which indicates specified entry is processed or not
194 -- in the period with PAY_PAYROLL_ACTIONS.effective_date between
195 -- p_effective_start_date and p_effective_end_date.
196 -- If the entry is processed once without reversal, then returns 'Y'.
197 -- Even if the entry is deleted, this function returns correct value.
198 -------------------------------------------------------------------------------
199 IS
200 l_entry_processed VARCHAR2(1);
201 l_result_status PAY_RUN_RESULTS.STATUS%TYPE;
202 CURSOR csr_result_status IS
203 select prr.status
204 from pay_payroll_actions ppa,
205 pay_assignment_actions paa,
206 pay_run_results prr
207 where prr.source_id = p_element_entry_id
208 --
209 -- Necessary to specify source_type because source_type of
210 -- reversal assignment action means source run_result_id.
211 --
212 and prr.source_type = 'E'
213 and paa.assignment_action_id = prr.assignment_action_id
214 and ppa.payroll_action_id = paa.payroll_action_id
215 and ppa.effective_date
216 between p_effective_start_date and p_effective_end_date
217 and not exists(
218 select NULL
219 from pay_run_results prr2
220 where prr2.source_id = prr.run_result_id
221 and prr2.source_type = 'R')
222 order by decode(prr.status,'U',1,2);
223 BEGIN
224 open csr_result_status;
225 fetch csr_result_status into l_result_status;
226 if csr_result_status%NOTFOUND then
227 l_entry_processed := 'N';
228 else
229 if l_result_status = 'U' then
230 l_entry_processed := 'N';
231 else
232 l_entry_processed := 'Y';
233 end if;
234 end if;
235 close csr_result_status;
236 --
237 -- Return value.
238 --
239 return l_entry_processed;
240 END entry_processed_flag;
241 -------------------------------------------------------------------------------
242 Function lock_action(
243 p_locked_action_id IN NUMBER,
244 p_locking_action_type IN VARCHAR2) return lock_action_t
245 -------------------------------------------------------------------------------
246 -- p_locking_action_type allows the following values.
247 -- 'P','C','T','V','M'
248 -- In case of 'M', p_lock_action_id = locked pre_payment_id.
249 -------------------------------------------------------------------------------
250 Is
251 Cursor csr_lock_action is
252 select paa.assignment_action_id,
253 paa.action_status,
254 paa.object_version_number,
255 ppa.payroll_action_id,
256 ppa.action_type,
257 ppa.effective_date
258 from pay_payroll_actions ppa,
259 pay_assignment_actions paa,
260 pay_action_interlocks pai
261 where pai.locked_action_id = p_locked_action_id
262 and paa.assignment_action_id = pai.locking_action_id
263 and ppa.payroll_action_id = paa.payroll_action_id
264 and ppa.action_type = p_locking_action_type;
265 Cursor csr_prepay_lock_action is
266 select paa.assignment_action_id,
267 paa.action_status,
268 paa.object_version_number,
269 ppa.payroll_action_id,
270 ppa.action_type,
271 ppa.effective_date
272 from pay_payroll_actions ppa,
273 pay_assignment_actions paa,
274 pay_action_interlocks pai
275 where pai.locked_action_id = p_locked_action_id
276 and paa.assignment_action_id = pai.locking_action_id
277 and ppa.payroll_action_id = paa.payroll_action_id
278 and ppa.action_type in ('P','U');
279 Cursor csr_payment_lock_action is
280 select /*+ ORDERED
281 INDEX(PAA PAY_ASSIGNMENT_ACTIONS_FK2)
282 INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
283 paa.assignment_action_id,
284 paa.action_status,
285 paa.object_version_number,
286 ppa.payroll_action_id,
287 ppa.action_type,
288 ppa.effective_date
289 from pay_assignment_actions paa,
290 pay_payroll_actions ppa
291 where paa.pre_payment_id = p_locked_action_id
292 and ppa.payroll_action_id = paa.payroll_action_id
293 and not exists(
294 select /*+ ORDERED
295 INDEX(PAI PAY_ACTION_INTERLOCKS_FK2)
296 INDEX(PAA2 PAY_ASSIGNMENT_ACTIONS_PK)
297 INDEX(PPA2 PAY_PAYROLL_ACTIONS_PK) */
298 NULL
299 from pay_action_interlocks pai,
300 pay_assignment_actions paa2,
301 pay_payroll_actions ppa2
302 where pai.locked_action_id = paa.assignment_action_id
303 and paa2.assignment_action_id = pai.locking_action_id
304 and ppa2.payroll_action_id = paa2.payroll_action_id
305 and ppa2.action_type = 'D');
306 l_lock_action lock_action_t;
307 Begin
308 if p_locking_action_type = 'P' then
309 open csr_prepay_lock_action;
310 fetch csr_prepay_lock_action into l_lock_action;
311 if csr_prepay_lock_action%NOTFOUND then
312 l_lock_action := NULL;
313 end if;
314 close csr_prepay_lock_action;
315 elsif p_locking_action_type = 'M' then
316 open csr_payment_lock_action;
317 fetch csr_payment_lock_action into l_lock_action;
318 if csr_payment_lock_action%NOTFOUND then
319 l_lock_action := NULL;
320 end if;
321 close csr_payment_lock_action;
322 else
323 open csr_lock_action;
324 fetch csr_lock_action into l_lock_action;
325 if csr_lock_action%NOTFOUND then
326 l_lock_action := NULL;
327 end if;
328 close csr_lock_action;
329 end if;
330
331 return l_lock_action;
332 End lock_action;
333 -------------------------------------------------------------------------------
334 Function lock_status(
335 p_locked_action_id IN NUMBER,
336 p_locking_action_type IN VARCHAR2) return lock_status_t
337 -------------------------------------------------------------------------------
338 -- p_locking_action_type allows the following values.
339 -- 'M','T'
340 -- This function never returns NULL.
341 -------------------------------------------------------------------------------
342 Is
343 Cursor csr_lock_status is
344 /* This select statement returns only 1 row. */
345 select decode(count(*),0,'U','C'),
346 /* When not locked, that means the following statement returns no rows,
347 max(decode(paa.action_status,'C',NULL,'E',2,1)) returns NULL. */
348 decode(max(decode(paa.action_status,'C',NULL,'E',2,1)),NULL,'C',1,'I','E')
349 from pay_payroll_actions ppa,
350 pay_assignment_actions paa,
351 pay_action_interlocks pai
352 where pai.locked_action_id = p_locked_action_id
353 and paa.assignment_action_id = pai.locking_action_id
354 and ppa.payroll_action_id = paa.payroll_action_id
355 and ppa.action_type = p_locking_action_type;
356 Cursor csr_prepay_lock_status is
357 /* This select statement returns only 1 row. */
358 select decode(count(decode(ppa2.payroll_action_id,NULL,paa.assignment_action_id,NULL)),count(distinct ppp.pre_payment_id),'C',0,'U','I'),
359 decode(max(decode(decode(ppa2.payroll_action_id,NULL,paa.action_status,NULL),'C',NULL,NULL,NULL,'E',2,1)),NULL,'C',1,'I','E')
360 from pay_payroll_actions ppa2,
361 pay_assignment_actions paa2,
362 pay_action_interlocks pai,
363 pay_assignment_actions paa,
364 pay_pre_payments ppp
365 where ppp.assignment_action_id = p_locked_action_id
366 and paa.pre_payment_id(+) = ppp.pre_payment_id
367 /* "H"(Cheque) action can be locked by "D"(Void) only once. */
368 and pai.locked_action_id(+) = paa.assignment_action_id
369 and paa2.assignment_action_id(+) = pai.locking_action_id
370 and ppa2.payroll_action_id(+) = paa2.payroll_action_id
371 and ppa2.action_type(+) = 'D';
372 l_lock_status lock_status_t;
373 Begin
374 if p_locked_action_id is NULL then
375 l_lock_status.lock_status := 'U';
376 l_lock_status.action_status := 'C';
377 else
378 if p_locking_action_type = 'M' then
379 open csr_prepay_lock_status;
380 fetch csr_prepay_lock_status into l_lock_status;
381 close csr_prepay_lock_status;
382 else
383 open csr_lock_status;
384 fetch csr_lock_status into l_lock_status;
385 close csr_lock_status;
386 end if;
387 end if;
388
389 return l_lock_status;
390 End lock_status;
391 -------------------------------------------------------------------------------
392 Function get_lock_action_val(
393 p_locked_action_id IN NUMBER,
394 p_locking_action_type IN VARCHAR2,
395 p_attribute IN VARCHAR2) return VARCHAR2
396 -------------------------------------------------------------------------------
397 Is
398 l_lock_action lock_action_t;
399 l_return_val varchar2(30);
400 Begin
401 l_lock_action := lock_action(
402 p_locked_action_id,
403 p_locking_action_type);
404
405 if p_attribute = 'action_status' then
406 l_return_val := l_lock_action.action_status;
407 elsif p_attribute = 'action_type' then
408 l_return_val := l_lock_action.action_type;
409 else
410 l_return_val := NULL;
411 end if;
412
413 return l_return_val;
414 end get_lock_action_val;
415 -------------------------------------------------------------------------------
416 Function get_lock_action_num(
417 p_locked_action_id IN NUMBER,
418 p_locking_action_type IN VARCHAR2,
419 p_attribute IN VARCHAR2) return NUMBER
420 -------------------------------------------------------------------------------
421 Is
422 l_lock_action lock_action_t;
423 l_return_num number;
424 Begin
425 l_lock_action := lock_action(
426 p_locked_action_id,
427 p_locking_action_type);
428
429 if p_attribute = 'assignment_action_id' then
430 l_return_num := l_lock_action.assignment_action_id;
431 elsif p_attribute = 'object_version_number' then
432 l_return_num := l_lock_action.object_version_number;
433 elsif p_attribute = 'payroll_action_id' then
434 l_return_num := l_lock_action.payroll_action_id;
435 else
436 l_return_num := NULL;
437 end if;
438
439 return l_return_num;
440 end get_lock_action_num;
441 -------------------------------------------------------------------------------
442 Function get_lock_status_val(
443 p_locked_action_id IN NUMBER,
444 p_locking_action_type IN VARCHAR2,
445 p_attribute IN VARCHAR2) return VARCHAR2
446 -------------------------------------------------------------------------------
447 Is
448 l_lock_action lock_status_t;
449 l_return_val varchar2(30);
450 Begin
451 l_lock_action := lock_status(
452 p_locked_action_id,
453 p_locking_action_type);
454
455 if p_attribute = 'lock_status' then
456 l_return_val := l_lock_action.lock_status;
457 elsif p_attribute = 'action_status' then
458 l_return_val := l_lock_action.action_status;
459 else
460 l_return_val := NULL;
461 end if;
462
463 return l_return_val;
464 end get_lock_status_val;
465 -------------------------------------------------------------------------------
466 FUNCTION get_effective_date(
467 p_effective_date IN DATE,
468 p_assignment_id IN NUMBER) RETURN DATE
469 -------------------------------------------------------------------------------
470 -- When a assignment exists in the year but does not exist on session_date,
471 -- nearest date ESD or EED will be returned to effective_date.
472 -------------------------------------------------------------------------------
473 IS
474 CURSOR csr_get_effective_date IS
475 select nvl( nvl( min(decode(greatest(least(p_effective_date,paa.effective_end_date), paa.effective_start_date),p_effective_date,p_effective_date)),
476 max(decode(greatest(paa.effective_end_date,p_effective_date),p_effective_date,paa.effective_end_date))),
477 min(decode(least(p_effective_date, paa.effective_start_date),p_effective_date, paa.effective_start_date)) ) EFFECTIVE_DATE
478 from per_all_assignments_f paa
479 where to_number(to_char(p_effective_date, 'YYYY'))
480 between to_number(to_char(paa.effective_start_date, 'YYYY'))
481 and to_number(to_char(paa.effective_end_date, 'YYYY'))
482 and paa.assignment_id = p_assignment_id;
483 l_effective_date DATE;
484 BEGIN
485 OPEN csr_get_effective_date;
486 FETCH csr_get_effective_date INTO l_effective_date;
487 CLOSE csr_get_effective_date;
488
489 RETURN l_effective_date;
490 END get_effective_date;
491 -------------------------------------------------------------------------------
492 PROCEDURE lock_row(
493 p_assignment_action_id IN NUMBER,
494 p_object_version_number IN NUMBER)
495 -------------------------------------------------------------------------------
496 -- This procedure locks pay_assignment_actions table for "ROLLBACK" or
497 -- "MARK FOR RETRY".
498 -------------------------------------------------------------------------------
499 IS
500 l_object_version_number NUMBER;
501 CURSOR csr_obj IS
502 select paa.object_version_number
503 from pay_assignment_actions paa
504 where paa.assignment_action_id=p_assignment_action_id
505 for update;
506 BEGIN
507 open csr_obj;
508 fetch csr_obj into l_object_version_number;
509 --
510 -- If record not found, issue error "Record is deleted".
511 --
512 if csr_obj%NOTFOUND then
513 close csr_obj;
514 fnd_message.set_name('FND','FORM_RECORD_DELETED');
515 fnd_message.raise_error;
516 end if;
517 close csr_obj;
518 --
519 -- If object_version_number is different, issue error "Record is changed".
520 --
521 if l_object_version_number <> p_object_version_number then
522 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
523 fnd_message.raise_error;
524 end if;
525 END lock_row;
526 -------------------------------------------------------------------------------
527 PROCEDURE rollback(
528 p_validate IN BOOLEAN DEFAULT FALSE,
529 p_rollback_mode IN VARCHAR2,
530 p_assignment_action_id IN NUMBER,
531 p_payroll_action_id IN NUMBER,
532 p_action_type IN VARCHAR2)
533 -------------------------------------------------------------------------------
534 -- Issue "ROLLBACK" or "MARK FOR RETRY" for specified assignment action.
535 -------------------------------------------------------------------------------
536 IS
537 l_dml_mode VARCHAR2(30);
538 l_count number;
539 BEGIN
540 if p_validate then
541 l_dml_mode := 'NONE';
542 else
543 l_dml_mode := 'NO_COMMIT';
544 end if;
545 --
546 -- Issue "Rollback" process without commiting.
547 -- 4615270
548 -- Check whether only single assact exists or multiple assacts exist
549 -- in current payroll_action_id.
550 --
551 select count(*)
552 into l_count
553 from pay_assignment_actions
554 where payroll_action_id = p_payroll_action_id
555 and rownum <= 2;
556 --
557 -- Rollback/Mark for Retry at assignment action level
558 -- when multiple assacts exist in current payroll_action_id.
559 --
560 if l_count > 1 then
561 py_rollback_pkg.rollback_ass_action(
562 p_assignment_action_id => p_assignment_action_id,
563 p_rollback_mode => p_rollback_mode,
564 p_leave_base_table_row => FALSE,
565 p_all_or_nothing => TRUE,
566 p_dml_mode => l_dml_mode,
567 p_multi_thread => FALSE);
568 --
569 -- Rollback/Mark for Retry at payroll action level
570 -- when single assact exists in current payroll_action_id.
571 --
572 else
573 py_rollback_pkg.rollback_payroll_action(
574 p_payroll_action_id => p_payroll_action_id,
575 p_rollback_mode => p_rollback_mode,
576 p_leave_base_table_row => FALSE,
577 p_all_or_nothing => TRUE,
578 p_dml_mode => l_dml_mode,
579 p_multi_thread => FALSE);
580 end if;
581 END rollback;
582 -------------------------------------------------------------------------------
583 PROCEDURE reverse_assact(
584 p_assignment_action_id IN NUMBER)
585 -------------------------------------------------------------------------------
586 -- Pay attention "Reversal" in this procedure do reverse assignment action
587 -- with the same payroll_id, consolidation_set_id and effective_date.
588 -------------------------------------------------------------------------------
589 IS
590 CURSOR csr_assact IS
591 select pay_payroll_actions_s.nextval PAYROLL_ACTION_ID,
592 ppa.business_group_id,
593 ppa.effective_date,
594 ppa.date_earned,
595 ppa.payroll_id,
596 ppa.consolidation_set_id,
597 ppa.time_period_id
598 from pay_payroll_actions ppa,
599 pay_assignment_actions paa
600 where paa.assignment_action_id = p_assignment_action_id
601 and ppa.payroll_action_id = paa.payroll_action_id;
602 l_rec csr_assact%ROWTYPE;
603 BEGIN
604 open csr_assact;
605 fetch csr_assact into l_rec;
606 if csr_assact%NOTFOUND then
607 close csr_assact;
608 end if;
609 close csr_assact;
610 --
611 -- Insert "Reversal" payroll action.
612 --
613 insert into pay_payroll_actions(
614 PAYROLL_ACTION_ID,
615 ACTION_TYPE,
616 BUSINESS_GROUP_ID,
617 EFFECTIVE_DATE,
618 DATE_EARNED,
619 PAYROLL_ID,
620 CONSOLIDATION_SET_ID,
621 TIME_PERIOD_ID,
622 ACTION_POPULATION_STATUS,
623 ACTION_STATUS,
624 OBJECT_VERSION_NUMBER)
625 values( l_rec.payroll_action_id,
626 'V',
627 l_rec.business_group_id,
628 l_rec.effective_date,
629 l_rec.date_earned,
630 l_rec.payroll_id,
631 l_rec.consolidation_set_id,
632 l_rec.time_period_id,
633 'U',
634 'U',
635 1);
636 --
637 -- Main "Reversal" assignment action routine.
638 --
639 hrassact.reversal(l_rec.payroll_action_id, p_assignment_action_id);
640 END reverse_assact;
641 -------------------------------------------------------------------------------
642 -- This procedure collects balance values etc. without termination payment.
643 PROCEDURE run_attributes(
644 p_assignment_action_id IN NUMBER,
645 p_itax_category OUT NOCOPY VARCHAR2,
646 p_d_itax_category OUT NOCOPY VARCHAR2,
647 p_yea_category OUT NOCOPY VARCHAR2,
648 p_d_yea_category OUT NOCOPY VARCHAR2,
649 p_allowance_ytd OUT NOCOPY NUMBER,
650 p_taxable_ytd OUT NOCOPY NUMBER,
651 p_si_prem_ytd OUT NOCOPY NUMBER,
652 p_itax_ytd OUT NOCOPY NUMBER)
653 -------------------------------------------------------------------------------
654 IS
655 l_salary_category varchar2(30);
656 BEGIN
657 --
658 -- Get Income Tax Category and YEA Category.
659 --
660 pay_jp_custom_pkg.get_itax_category(
661 P_ASSIGNMENT_ACTION_ID => p_assignment_action_id,
662 P_SALARY_CATEGORY => l_salary_category,
663 P_ITAX_CATEGORY => p_itax_category,
664 P_ITAX_YEA_CATEGORY => p_yea_category);
665 --
666 -- Setup output variables.
667 --
668 if p_itax_category = 'NON_RES' then
669 p_d_itax_category := c_nonres;
670 else
671 p_d_itax_category := hr_general.decode_lookup('JP_ITAX_TYPE', p_itax_category);
672 end if;
673 --
674 p_d_yea_category := hr_general.decode_lookup('JP_YEA_PROCESS_STATUS', p_yea_category);
675 --
676 -- Reset balance values (this is optional operation).
677 --
678 for i in 1..g_defined_balance_lst.count loop
679 g_defined_balance_lst(i).balance_value := 0;
680 end loop;
681 --
682 -- Get balance values using "bulk" get_value.
683 --
684 pay_balance_pkg.get_value(
685 P_ASSIGNMENT_ACTION_ID => p_assignment_action_id,
686 P_DEFINED_BALANCE_LST => g_defined_balance_lst);
687 --
688 p_allowance_ytd := g_defined_balance_lst(1).balance_value;
689 p_taxable_ytd := g_defined_balance_lst(2).balance_value
690 + g_defined_balance_lst(3).balance_value;
691 p_si_prem_ytd := g_defined_balance_lst(4).balance_value;
692 p_itax_ytd := g_defined_balance_lst(5).balance_value
693 + g_defined_balance_lst(6).balance_value;
694 END run_attributes;
695 -------------------------------------------------------------------------------
696 --
697 BEGIN
698 --
699 -- Package initialize routine.
700 --
701 -- Used to determine Income Tax Category.
702 --
703 g_id.SAL_ITAX_CATEGORY_IV := hr_jp_id_pkg.input_value_id(
704 'SAL_ITX',
705 'ITX_TYPE',NULL,'JP');
706 g_id.SAL_NR_ITAX_IV := hr_jp_id_pkg.input_value_id(
707 'SAL_ITX_NRES',
708 'Pay Value',NULL,'JP');
709 g_id.BON_ITAX_CATEGORY_IV := hr_jp_id_pkg.input_value_id(
710 'BON_ITX',
711 'ITX_TYPE',NULL,'JP');
712 g_id.BON_NR_ITAX_IV := hr_jp_id_pkg.input_value_id(
713 'BON_ITX_NRES',
714 'Pay Value',NULL,'JP');
715 g_id.SP_BON_ITAX_CATEGORY_IV := hr_jp_id_pkg.input_value_id(
716 'SPB_ITX',
717 'ITX_TYPE',NULL,'JP');
718 g_id.SP_BON_NR_ITAX_IV := hr_jp_id_pkg.input_value_id(
719 'SPB_ITX_NRES',
720 'Pay Value',NULL,'JP');
721 g_id.YEA_ITAX_CATEGORY_IV := hr_jp_id_pkg.input_value_id(
722 'YEA_AMT_AFTER_EMP_INCOME_DCT_RSLT',
723 'ITX_TYPE',NULL,'JP');
724 g_id.YEA_CATEGORY_IV := hr_jp_id_pkg.input_value_id(
725 'YEA_AMT_AFTER_EMP_INCOME_DCT_RSLT',
726 'INCLUDE_FLAG',NULL,'JP');
727 --
728 -- Currently there's no exact way to distinguish Resident or Nonresident
729 -- (when Itax element is not assigned to a assignment). So need to fetch
730 -- not only Resident balances but also Non-resident balances.
731 -- Pay attention Termination Payment is not included.
732 --
733 -- Only Resident balances except for allowance balance.
734 --
735 g_defined_balance_lst(1).defined_balance_id := hr_jp_id_pkg.defined_balance_id('B_YEA_ERN', '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01', null, 'JP');
736 g_defined_balance_lst(2).defined_balance_id := hr_jp_id_pkg.defined_balance_id('B_YEA_TXBL_ERN_MONEY', '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01', null, 'JP');
737 g_defined_balance_lst(3).defined_balance_id := hr_jp_id_pkg.defined_balance_id('B_YEA_TXBL_ERN_KIND', '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01', null, 'JP');
738 g_defined_balance_lst(4).defined_balance_id := hr_jp_id_pkg.defined_balance_id('B_YEA_SAL_DCT_SI_PREM','_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01', null, 'JP');
739 g_defined_balance_lst(5).defined_balance_id := hr_jp_id_pkg.defined_balance_id('B_YEA_WITHHOLD_ITX', '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01', null, 'JP');
740 g_defined_balance_lst(6).defined_balance_id := hr_jp_id_pkg.defined_balance_id('B_YEA_TAX_PAY', '_ASG_YTD EFFECTIVE_DATE 01-01 RESET 01', null, 'JP');
741 END pay_jp_soe_pkg;