[Home] [Help]
PACKAGE BODY: APPS.PAY_RETRO_STATUS_INTERNAL
Source
1 Package Body pay_retro_status_internal as
2 /* $Header: pyrtsbsi.pkb 120.8.12010000.1 2008/11/19 08:56:18 nerao ship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' pay_retro_status_internal.';
7
8 g_update varchar2(6) := 'UPDATE';
9 g_delete varchar2(6) := 'DELETE';
10
11 --
12 -- Global Definitions
13 --
14 subtype t_retro_asg_rec is pay_retro_assignments%rowtype;
15 subtype t_retro_ent_rec is pay_retro_entries%rowtype;
16
17 --
18 -- ----------------------------------------------------------------------------
19 -- |-----------------------< get_unprocessed_retro_asg >----------------------|
20 -- ----------------------------------------------------------------------------
21 --
22 function get_unprocessed_retro_asg
23 (p_assignment_id in number
24 ) return number
25 is
26 l_proc varchar2(72) := g_package||'get_unprocessed_retro_asg';
27 l_retro_assignment_id number;
28 cursor csr_unproc_retro_asg
29 is
30 select pra.retro_assignment_id
31 from pay_retro_assignments pra
32 where pra.assignment_id = p_assignment_id
33 and pra.retro_assignment_action_id is null
34 and pra.superseding_retro_asg_id is null
35 and approval_status in ('P','A','D');
36 begin
37 l_retro_assignment_id := null;
38 for l_rec in csr_unproc_retro_asg loop
39 if l_retro_assignment_id is not null then
40 --
41 -- Multiple unprocessed retro assignments found.
42 -- This should not happen.
43 --
44 pay_core_utils.assert_condition
45 (l_proc||':too_many_rows', false);
46 end if;
47 l_retro_assignment_id := l_rec.retro_assignment_id;
48 end loop;
49
50 return l_retro_assignment_id;
51
52 end get_unprocessed_retro_asg;
53 --
54 -- ----------------------------------------------------------------------------
55 -- |---------------------------< get_element_name >---------------------------|
56 -- ----------------------------------------------------------------------------
57 --
58 function get_element_name
59 (p_element_entry_id in number
60 ) return varchar2
61 is
62 cursor csr_entry
63 is
64 select
65 pettl.element_name
66 from
67 pay_element_entries_f pee
68 ,pay_element_links_f pel
69 ,pay_element_types_f_tl pettl
70 where
71 pee.element_entry_id = p_element_entry_id
72 and pel.element_link_id = pee.element_link_id
73 and pee.effective_start_date between pel.effective_start_date
74 and pel.effective_end_date
75 and pettl.element_type_id = pel.element_type_id
76 and pettl.language = userenv('lang')
77 ;
78 --
79 l_rec csr_entry%rowtype;
80 begin
81 open csr_entry;
82 fetch csr_entry into l_rec;
83 close csr_entry;
84 --
85 return l_rec.element_name;
86
87 end get_element_name;
88 --
89 -- ----------------------------------------------------------------------------
90 -- |--------------------------< get_component_name >--------------------------|
91 -- ----------------------------------------------------------------------------
92 --
93 function get_component_name
94 (p_retro_component_id in number
95 ) return varchar2
96 is
97 cursor csr_retro_component is
98 select component_name
99 from pay_retro_components
100 where retro_component_id = p_retro_component_id
101 ;
102 --
103 l_rec csr_retro_component%rowtype;
104 begin
105 open csr_retro_component;
106 fetch csr_retro_component into l_rec;
107 close csr_retro_component;
108 --
109 return l_rec.component_name;
110
111 end get_component_name;
112 --
113 -- ----------------------------------------------------------------------------
114 -- |----------------------< get_retro_asg_creator_type >----------------------|
115 -- ----------------------------------------------------------------------------
116 -- {Start Of Comments}
117 --
118 -- Description:
119 -- Returns by which owner type this retro assignment was initially created.
120 --
121 -- As currently no owner type column is available on the retro assignments,
122 -- created by = -1 is checked similarly to the OAF UI.
123 --
124 -- 1) If all retro entries are system types, this is system created.
125 -- 2) If all retro entries are user types or no entry exists, this is
126 -- user created.
127 -- 3) If it cannot be determined by 1 and 2, assume it as system creatd
128 -- when created-by is -1, otherwise take it as user created.
129 --
130 -- {End Of Comments}
131 -- ----------------------------------------------------------------------------
132 function get_retro_asg_creator_type
133 (p_retro_assignment_id in number
134 ,p_created_by in number
135 ) return varchar2
136 is
137 l_proc varchar2(72) := g_package||'get_retro_asg_creator_type';
138 l_owner_type varchar2(1);
139 --
140 -- Cursor to check what retro entry owner types exist under the
141 -- initial retro assignment.
142 --
143 -- Value Owner Type(s)
144 -- Null no retro entry
145 -- 1 System
146 -- 2 User
147 -- 3 System, User
148 -- 4 Merged
149 -- 5 System, Merged
150 -- 6 User, Merged
151 -- 7 System, User, Merged
152 --
153 cursor csr_retro_ent_type
154 is
155 select sum(distinct decode(owner_type,'S',1, 'U',2, 'M',4, 1)) owntype_sum
156 from pay_retro_entries
157 where retro_assignment_id = p_retro_assignment_id
158 ;
159 --
160 l_ownertype_sum number;
161 --
162 begin
163 hr_utility.set_location('Entering:'|| l_proc, 10);
164 --
165 -- Check the owner type of all the retro entries.
166 --
167 open csr_retro_ent_type;
168 fetch csr_retro_ent_type into l_ownertype_sum;
169 close csr_retro_ent_type;
170 --
171 -- Determine the owner type.
172 --
173 if (l_ownertype_sum = 1) then
174 hr_utility.set_location(l_proc, 20);
175 l_owner_type := g_system;
176 elsif (l_ownertype_sum is null) or (l_ownertype_sum = 2) then
177 hr_utility.set_location(l_proc, 30);
178 l_owner_type := g_user;
179 else
180 --
181 -- Failed to judge it by the entry owner type.
182 -- Temporarily determine it by the created_by value.
183 --
184 if p_created_by = -1 then
185 hr_utility.set_location(l_proc, 40);
186 l_owner_type := g_system;
187 else
188 hr_utility.set_location(l_proc, 50);
189 l_owner_type := g_user;
190 end if;
191 end if;
192 --
193 return l_owner_type;
194
195 hr_utility.set_location(' Leaving:'||l_proc, 90);
196 end get_retro_asg_creator_type;
197 --
198 -- ----------------------------------------------------------------------------
199 -- |----------------------< get_retro_ent_creator_type >----------------------|
200 -- ----------------------------------------------------------------------------
201 -- {Start Of Comments}
202 --
203 -- Description:
204 -- Returns by which owner type this retro entry was initially created.
205 --
206 -- As currently no owner type column is available on the retro assignments,
207 -- created by = -1 is checked similarly to the OAF UI.
208 --
209 -- 1) If the current owner type is system type, this is system created.
210 -- 2) If the current owner type is user type, this is user created.
211 -- 3) If it cannot be determined by 1 and 2, assume it as system creatd
212 -- when created-by is -1, otherwise take it as user created.
213 --
214 -- {End Of Comments}
215 -- ----------------------------------------------------------------------------
216 function get_retro_ent_creator_type
217 (p_old_owner_type in varchar2
218 ,p_old_created_by in number
219 ) return varchar2
220 is
221 l_proc varchar2(72) := g_package||'get_retro_ent_creator_type';
222 l_owner_type varchar2(1);
223 begin
224 hr_utility.set_location('Entering:'|| l_proc, 10);
225
226 if (nvl(p_old_owner_type, g_system) = g_system) then
227 l_owner_type := g_system;
228 elsif p_old_owner_type = g_user then
229 l_owner_type := g_user;
230 elsif p_old_created_by = -1 then
231 l_owner_type := g_system;
232 else
233 l_owner_type := g_user;
234 end if;
235
236 return l_owner_type;
237
238 hr_utility.set_location(' Leaving:'||l_proc, 90);
239 end get_retro_ent_creator_type;
240 --
241 -- ----------------------------------------------------------------------------
242 -- |--------------------------< chk_assignment_id >---------------------------|
243 -- ----------------------------------------------------------------------------
244 -- {Start Of Comments}
245 --
246 -- Description:
247 -- Validates that the employee assignment exists on the reprocess date.
248 --
249 -- Prerequisites:
250 -- None.
251 --
252 -- In Parameters:
253 -- p_assignment_id
254 -- p_reprocess_date
255 --
256 -- Out Parameters:
257 -- p_business_group_id
258 -- p_payroll_id
259 --
260 -- Post Success:
261 -- Processing continues.
262 --
263 -- Post Failure:
264 -- An application error is raised.
265 --
266 -- {End Of Comments}
267 -- ----------------------------------------------------------------------------
268 procedure chk_assignment_id
269 (p_assignment_id in number
270 ,p_reprocess_date in date
271 ,p_business_group_id out nocopy number
272 ,p_payroll_id out nocopy number
273 )
274 is
275 --
276 l_business_group_id number;
277 l_payroll_id number;
278 --
279 cursor csr_asg
280 is
281 select
282 paf.business_group_id
283 ,paf.payroll_id
284 from
285 per_all_assignments_f paf
286 ,per_periods_of_service prd
287 where
288 paf.assignment_id = p_assignment_id
289 and paf.payroll_id is not null
290 and p_reprocess_date between paf.effective_start_date
291 and paf.effective_end_date
292 and prd.period_of_service_id = paf.period_of_service_id
293 ;
294
295 l_proc varchar2(72) := g_package||'chk_assignment_id';
296 begin
297 hr_utility.set_location('Entering:'|| l_proc, 10);
298 open csr_asg;
299 fetch csr_asg into l_business_group_id, l_payroll_id;
300 if csr_asg%notfound then
301 close csr_asg;
302 --
303 -- A valid assignment does not exist on the reprocess date.
304 --
305 fnd_message.set_name('PAY','PAY_34300_RTS_ASG_NOT_EXISTS');
306 fnd_message.set_token('EFFECTIVE_DATE', to_char(p_reprocess_date));
307 fnd_message.raise_error;
308 --
309 end if;
310 close csr_asg;
311 --
312 -- Set out variable.
313 --
314 p_business_group_id := l_business_group_id;
315 p_payroll_id := l_payroll_id;
316
317 hr_utility.set_location(' Leaving:'||l_proc, 40);
318 end chk_assignment_id;
319 --
320 -- ----------------------------------------------------------------------------
321 -- |-------------------------< chk_approval_status >--------------------------|
322 -- ----------------------------------------------------------------------------
323 -- {Start Of Comments}
324 --
325 -- Description:
326 -- This procedure ensures that a valid approval status is specified.
327 -- The available status are dependent on the RETRO_STATUS_USER_UPD
328 -- legislation rule as follows.
329 --
330 -- Leg Rule: RETRO_STATUS_USER_UPD = Y, R
331 --
332 -- Approval Status Insert Update
333 -- ------------------------------------+-------|-------|
334 -- A Confirmed - Awaiting Processing | X | X |
335 -- D Deferred | X | X |
336 -- P Included - Awaiting Processing | | |
337 -- ------------------------------------+-------|-------|
338 --
339 -- Leg Rule: RETRO_STATUS_USER_UPD = N
340 --
341 -- Approval Status Insert Update
342 -- ------------------------------------+-------|-------|
343 -- A Confirmed - Awaiting Processing | | |
344 -- D Deferred | | |
345 -- P Included - Awaiting Processing | X | X |
346 -- ------------------------------------+-------|-------|
347 --
348 --
349 -- Prerequisites:
350 -- None.
351 --
352 -- In Parameters:
353 -- p_business_group_id
354 -- p_approval_status
355 -- p_owner_type
356 --
357 -- Post Success:
358 -- Processing continues.
359 --
360 -- Post Failure:
361 -- An application error is raised.
362 --
363 -- {End Of Comments}
364 -- ----------------------------------------------------------------------------
365 procedure chk_approval_status
366 (p_business_group_id in number
367 ,p_approval_status in varchar2
368 ,p_owner_type in varchar2
369 )
370 is
371 l_leg_code per_business_groups.legislation_code%type;
372 l_rule_mode pay_legislation_rules.rule_mode%type;
373 l_rule_found boolean;
374 l_inv_app_status boolean:= false;
375 l_proc varchar2(72) := g_package||'chk_approval_status';
376 begin
377 hr_utility.set_location('Entering:'|| l_proc, 10);
378
379 if p_owner_type = g_user then
380
381 l_leg_code := hr_api.return_legislation_code(p_business_group_id);
382
383 pay_core_utils.get_legislation_rule
384 (p_legrul_name => 'RETRO_STATUS_USER_UPD'
385 ,p_legislation => l_leg_code
386 ,p_legrul_value => l_rule_mode
387 ,p_found => l_rule_found
388 );
389
390 if not l_rule_found then
391 l_rule_mode := 'N';
392 end if;
393
394 hr_utility.set_location(l_proc, 20);
395
396 if (l_rule_mode = 'Y' and p_approval_status in ('A','D'))
397 or (l_rule_mode = 'R' and p_approval_status in ('A','D'))
398 or (l_rule_mode = 'N' and p_approval_status = 'P') then
399 --
400 -- The approval status is valid.
401 --
402 null;
403 else
404 hr_utility.trace('Legislation Code: '|| l_leg_code);
405 hr_utility.trace('Leg Rule: RETRO_STATUS_USER_UPD = '|| l_rule_mode);
406 hr_utility.trace('Approval Status = '|| p_approval_status);
407 --
408 l_inv_app_status := true;
409 end if;
410 elsif p_owner_type = g_system then
411 hr_utility.set_location(l_proc, 30);
412
413 if hr_api.not_exists_in_hr_lookups
414 (trunc(sysdate), 'ADVANCE_RETRO_STATUS', p_approval_status) then
415
416 l_inv_app_status := true;
417 end if;
418 end if;
419
420 if l_inv_app_status then
421 fnd_message.set_name('PAY','PAY_34301_RTS_INV_APPRVL_STA');
422 fnd_message.raise_error;
423 end if;
424 hr_utility.set_location(' Leaving:'||l_proc, 40);
425
426 end chk_approval_status;
427 --
428 -- ----------------------------------------------------------------------------
429 -- |----------------------< chk_asg_rep_date_updatable >----------------------|
430 -- ----------------------------------------------------------------------------
431 -- {Start Of Comments}
432 --
433 -- Description:
434 -- Validates that the reprocess date is updatable.
435 --
436 -- Prerequisites:
437 -- None.
438 --
439 -- In Parameters:
440 -- p_retro_assignment_id
441 -- p_created_by
442 -- p_reprocess_date
443 --
444 -- Post Success:
445 -- Processing continues.
446 --
447 -- Post Failure:
448 -- An application error is raised.
449 --
450 -- {End Of Comments}
451 -- ----------------------------------------------------------------------------
452 procedure chk_asg_rep_date_updatable
453 (p_retro_assignment_id in number
454 ,p_created_by in number
455 ,p_owner_type in varchar2
456 )
457 is
458 l_proc varchar2(72) := g_package||'chk_asg_rep_date_updatable';
459 l_owner_type varchar2(10);
460 --
461 begin
462 hr_utility.set_location('Entering:'|| l_proc, 10);
463 --
464 if p_owner_type = g_user then
465
466 l_owner_type := get_retro_asg_creator_type
467 (p_retro_assignment_id => p_retro_assignment_id
468 ,p_created_by => p_created_by
469 );
470
471 if l_owner_type = g_system then
472 --
473 -- You cannot update the reprocess date of the retro assignment
474 -- that was created by the system.
475 --
476 fnd_message.set_name('PAY','PAY_34302_RTS_INV_REP_DATE');
477 fnd_message.raise_error;
478 --
479 end if;
480 end if;
481
482 hr_utility.set_location(' Leaving:'||l_proc, 40);
483
484 end chk_asg_rep_date_updatable;
485 --
486 -- ----------------------------------------------------------------------------
487 -- |---------------------< chk_retro_asg_reprocess_date >---------------------|
488 -- ----------------------------------------------------------------------------
489 -- {Start Of Comments}
490 --
491 -- Description:
492 -- Validates that the reprocess date specified for the retro assignment
493 -- is earlier than the reprocess date of any child retro entries.
494 --
495 -- Prerequisites:
496 -- None.
497 --
498 -- In Parameters:
499 -- p_retro_assignment_id
500 -- p_reprocess_date
501 --
502 -- Post Success:
503 -- Processing continues.
504 --
505 -- Post Failure:
506 -- An application error is raised.
507 --
508 -- {End Of Comments}
509 -- ----------------------------------------------------------------------------
510 procedure chk_retro_asg_reprocess_date
511 (p_retro_assignment_id in number
512 ,p_reprocess_date in date
513 )
514 is
515 l_proc varchar2(72) := g_package||'chk_retro_asg_reprocess_date';
516 l_dummy number;
517 --
518 cursor csr_chk_rep_date
519 is
520 select 1
521 from pay_retro_entries
522 where
523 retro_assignment_id = p_retro_assignment_id
524 and reprocess_date < p_reprocess_date;
525
526 begin
527 hr_utility.set_location('Entering:'|| l_proc, 10);
528 open csr_chk_rep_date;
529 fetch csr_chk_rep_date into l_dummy;
530 if csr_chk_rep_date%found then
531 close csr_chk_rep_date;
532 --
533 fnd_message.set_name('PAY','PAY_34288_RET_ASG_DAT_ERR');
534 fnd_message.raise_error;
535 --
536 end if;
537 close csr_chk_rep_date;
538
539 hr_utility.set_location(' Leaving:'||l_proc, 40);
540
541 end chk_retro_asg_reprocess_date;
542 --
543 -- ----------------------------------------------------------------------------
544 -- |-----------------------< chk_retro_asg_updatable >------------------------|
545 -- ----------------------------------------------------------------------------
546 -- {Start Of Comments}
547 --
548 -- Description:
549 -- Validates that the specified retro assignment is user updatable.
550 -- The retro assignment is updatable/deletable when the following conditions
551 -- are all satisfied.
552 -- 1) The retro assignment is not processed by retropay.
553 -- 2) The retro assignment is not superseded by another retro assignment.
554 -- 3) The approval status was A (confirmed) and the status is not being
555 -- changed.
556 -- 4) No system retro entry exists when deleting.
557 --
558 -- Prerequisites:
559 -- None.
560 --
561 -- In Parameters:
562 -- p_retro_assignment_id
563 -- p_retro_assignment_action_id
564 -- p_superseding_retro_asg_id
565 -- p_old_approval_status
566 -- p_new_approval_status
567 -- p_owner_type
568 -- p_dml_mode
569 --
570 -- Post Success:
571 -- Processing continues.
572 --
573 -- Post Failure:
574 -- An application error is raised.
575 --
576 -- {End Of Comments}
577 -- ----------------------------------------------------------------------------
578 procedure chk_retro_asg_updatable
579 (p_retro_assignment_id in number
580 ,p_retro_assignment_action_id in number
581 ,p_superseding_retro_asg_id in number
582 ,p_old_approval_status in varchar2
583 ,p_new_approval_status in varchar2
584 ,p_owner_type in varchar2
585 ,p_dml_mode in varchar2
586 )
587 is
588 l_proc varchar2(72) := g_package||'chk_retro_asg_updatable';
589 l_dummy number;
590 --
591 cursor csr_sys_ent_exists
592 is
593 select 1 from pay_retro_entries
594 where retro_assignment_id = p_retro_assignment_id
595 and nvl(owner_type, g_system) <> g_user;
596 --
597 begin
598 hr_utility.set_location('Entering:'|| l_proc, 10);
599
600 if p_owner_type = g_user then
601
602 hr_utility.set_location(l_proc, 20);
603
604 if (p_retro_assignment_action_id is not null) or
605 (p_superseding_retro_asg_id is not null) or
606 (p_old_approval_status not in ('A','D','P')) then
607 --
608 -- The retro assignment has been processed or is superseded.
609 --
610 fnd_message.set_name('PAY','PAY_34303_RTS_RT_ASG_UNAVAIL');
611 fnd_message.raise_error;
612 --
613 elsif p_old_approval_status = 'A' and
614 p_new_approval_status is null then
615 --
616 -- The retro assignment has been confirmed. The retro assignment
617 -- and retro entries cannot be changed unless the status is changed
618 -- or reconfirmed.
619 --
620 fnd_message.set_name('PAY','PAY_34313_RTS_ASG_CONFIRMED');
621 fnd_message.raise_error;
622 --
623 elsif p_dml_mode = g_delete then
624 --
625 -- Check to see if any system generated retro entry exists.
626 --
627 open csr_sys_ent_exists;
628 fetch csr_sys_ent_exists into l_dummy;
629 if csr_sys_ent_exists%found then
630 close csr_sys_ent_exists;
631 --
632 -- system generated entry found.
633 --
634 fnd_message.set_name('PAY','PAY_34289_RET_ASG_DEL_ERR');
635 fnd_message.raise_error;
636 --
637 end if;
638 close csr_sys_ent_exists;
639 end if;
640 end if;
641
642 hr_utility.set_location(' Leaving:'||l_proc, 40);
643
644 end chk_retro_asg_updatable;
645 --
646 -- ----------------------------------------------------------------------------
647 -- |-----------------------< chk_ent_reprocess_date >-------------------------|
648 -- ----------------------------------------------------------------------------
649 -- {Start Of Comments}
650 --
651 -- Description:
652 -- Validates that the specified retro entry reprocess date is valid.
653 --
654 -- Prerequisites:
655 -- None.
656 --
657 -- In Parameters:
658 -- p_retro_assignment_id
659 -- p_element_entry_id
660 -- p_reprocess_date
661 -- p_old_reprocess_date
662 -- p_system_reprocess_date
663 -- p_asg_reprocess_date
664 -- p_asg_owner_type
665 -- p_created_by
666 -- p_old_owner_type
667 -- p_owner_type
668 --
669 -- Post Success:
670 -- Processing continues.
671 --
672 -- Post Failure:
673 -- An application error is raised.
674 --
675 -- {End Of Comments}
676 -- ----------------------------------------------------------------------------
677 procedure chk_ent_reprocess_date
678 (p_retro_assignment_id in number
679 ,p_element_entry_id in number
680 ,p_reprocess_date in date
681 ,p_old_reprocess_date in date
682 ,p_system_reprocess_date in date
683 ,p_asg_reprocess_date in date
684 ,p_asg_owner_type in varchar2
685 ,p_created_by in number
686 ,p_old_owner_type in varchar2
687 ,p_owner_type in varchar2
688 )
689 is
690 l_proc varchar2(72) := g_package||'chk_ent_reprocess_date';
691 l_element_name pay_element_types_f_tl.element_name%type;
692 l_owner_type varchar2(10);
693 begin
694 hr_utility.set_location('Entering:'|| l_proc, 10);
695
696 if p_owner_type = g_user then
697
698 if p_reprocess_date <> p_old_reprocess_date then
699 hr_utility.set_location(l_proc, 20);
700 --
701 -- Check the existing owner type.
702 --
703 l_owner_type := get_retro_ent_creator_type
704 (p_old_owner_type => p_old_owner_type
705 ,p_old_created_by => p_created_by
706 );
707 --
708 if (p_asg_owner_type = g_system) and (l_owner_type = g_system) then
709 --
710 -- If the system has initially generated the retro assignment
711 -- and this entry, the reprocess date cannot be updated.
712 --
713 l_element_name := get_element_name(p_element_entry_id);
714 fnd_message.set_name('PAY','PAY_34304_RTS_RT_ENT_UNAVAIL');
715 fnd_message.set_token('ELEMENT_NAME', l_element_name);
716 fnd_message.raise_error;
717 --
718 elsif nvl(p_system_reprocess_date, p_reprocess_date) < p_reprocess_date then
719 --
720 -- The reprocess date cannot be later than the system reprocess date.
721 --
722 l_element_name := get_element_name(p_element_entry_id);
723 fnd_message.set_name('PAY','PAY_34285_RET_REC_DATE_ETRY');
724 fnd_message.set_token('ELEMENT_NAME', l_element_name);
725 fnd_message.raise_error;
726 --
727 end if;
728 end if;
729 hr_utility.set_location(l_proc, 30);
730 --
731 if (p_reprocess_date <> p_old_reprocess_date) or
732 (p_old_reprocess_date is null) then
733 --
734 hr_utility.set_location(l_proc, 40);
735 if p_reprocess_date < p_asg_reprocess_date then
736 --
737 -- The reprocess date cannot be earlier than the asg reprocess date.
738 --
739 l_element_name := get_element_name(p_element_entry_id);
740 fnd_message.set_name('PAY','PAY_33182_RET_RECALC_DATE_ERR');
741 fnd_message.set_token('ELEMENT_NAME', l_element_name);
742 fnd_message.raise_error;
743 --
744 end if;
745 end if;
746 end if;
747 hr_utility.set_location(' Leaving:'||l_proc, 40);
748 end chk_ent_reprocess_date;
749 --
750 -- ----------------------------------------------------------------------------
751 -- |-----------------------< chk_retro_entry_deletable >----------------------|
752 -- ----------------------------------------------------------------------------
753 -- {Start Of Comments}
754 --
755 -- Description:
756 -- Validates that the specified retro entry is user deletable.
757 --
758 -- Prerequisites:
759 -- None.
760 --
761 -- In Parameters:
762 -- p_element_entry_id
763 -- p_old_owner_type
764 -- p_owner_type
765 --
766 -- Post Success:
767 -- Processing continues.
768 --
769 -- Post Failure:
770 -- An application error is raised.
771 --
772 -- {End Of Comments}
773 -- ----------------------------------------------------------------------------
774 procedure chk_retro_entry_deletable
775 (p_element_entry_id in number
776 ,p_old_owner_type in varchar2
777 ,p_owner_type in varchar2
778 )
779 is
780 l_proc varchar2(72) := g_package||'chk_retro_entry_deletable';
781 l_owner_type varchar2(10);
782 l_element_name pay_element_types_f_tl.element_name%type;
783 begin
784 hr_utility.set_location('Entering:'|| l_proc, 10);
785
786 if p_owner_type = g_user then
787
788 if nvl(p_old_owner_type, g_system) <> g_user then
789 --
790 -- User cannot delete system generated retro entry.
791 --
792 l_element_name := get_element_name(p_element_entry_id);
793
794 fnd_message.set_name('PAY','PAY_34314_RTS_ENT_NO_DEL');
795 fnd_message.set_token('ELEMENT_NAME', l_element_name);
796 fnd_message.raise_error;
797 end if;
798 end if;
799
800 hr_utility.set_location(' Leaving:'||l_proc, 40);
801 end chk_retro_entry_deletable;
802 --
803 -- ----------------------------------------------------------------------------
804 -- |-------------------------< chk_element_entry_id >-------------------------|
805 -- ----------------------------------------------------------------------------
806 -- {Start Of Comments}
807 --
808 -- Description:
809 -- This procedure ensures that the element entry exists.
810 --
811 -- Prerequisites:
812 -- None.
813 --
814 -- In Parameters:
815 -- p_element_entry_id
816 -- p_assignment_id
817 --
818 -- Out Parameters:
819 -- p_element_type_id
820 --
821 -- Post Success:
822 -- Processing continues.
823 --
824 -- Post Failure:
825 -- An application error is raised.
826 --
827 -- {End Of Comments}
828 -- ----------------------------------------------------------------------------
829 procedure chk_element_entry_id
830 (p_element_entry_id in number
831 ,p_assignment_id in number
832 ,p_element_type_id out nocopy number
833 )
834 is
835 l_proc varchar2(72) := g_package||'chk_element_entry_id';
836 --
837 cursor csr_entry
838 is
839 select
840 pel.element_type_id
841 from
842 pay_element_entries_f pee
843 ,pay_element_links_f pel
844 ,pay_element_types_f_tl pettl
845 where
846 pee.element_entry_id = p_element_entry_id
847 and pee.assignment_id = nvl(p_assignment_id, pee.assignment_id)
848 and pee.creator_type in ('A', 'F', 'H', 'Q', 'SP', 'UT', 'M', 'S')
849 and pel.element_link_id = pee.element_link_id
850 and pee.effective_start_date between pel.effective_start_date
851 and pel.effective_end_date
852 and pettl.element_type_id = pel.element_type_id
853 and pettl.language = userenv('lang')
854 ;
855 --
856 l_rec csr_entry%rowtype;
857 begin
858 hr_utility.set_location('Entering:'|| l_proc, 10);
859 open csr_entry;
860 fetch csr_entry into l_rec;
861 if csr_entry%notfound then
862 close csr_entry;
863 --
864 fnd_message.set_name('PAY','PAY_34305_RTS_INV_ENT_ID');
865 fnd_message.set_token('ELEMENT_ENTRY_ID', to_char(p_element_entry_id));
866 fnd_message.raise_error;
867 --
868 end if;
869 close csr_entry;
870
871 --
872 -- Set out variables.
873 --
874 p_element_type_id := l_rec.element_type_id;
875
876 hr_utility.set_location(' Leaving:'||l_proc, 40);
877
878 end chk_element_entry_id;
879 --
880 -- -------------------------------------------------------------------------
881 -- |-------------------< get_default_retro_component_id >------------------|
882 -- -------------------------------------------------------------------------
883 -- {Start Of Comments}
884 --
885 -- Description:
886 -- This function returns the default retro component ID for the element
887 -- type.
888 --
889 -- Prerequisites:
890 -- None.
891 --
892 -- In Parameters:
893 -- p_element_entry_id
894 -- p_reprocess_date
895 -- p_element_type_id
896 -- p_assignment_id
897 --
898 -- Returns:
899 -- retro_component_id
900 --
901 -- Post Success:
902 -- Processing continues if default component is found.
903 --
904 -- Post Failure:
905 -- An application error is raised and processing is terminated if the
906 -- default retro component id is not found.
907 --
908 -- {End Of Comments}
909 -- ----------------------------------------------------------------------------
910 function get_default_retro_component_id
911 (p_element_entry_id in number
912 ,p_reprocess_date in date
913 ,p_element_type_id in number
914 ,p_assignment_id in number
915 ) return number
916 is
917 --
918 -- Declare local variables
919 --
920 l_proc varchar2(72) := g_package||'get_default_retro_component_id';
921
922 l_retro_component_id number;
923 l_element_name pay_element_types_f_tl.element_name%type;
924 --
925 begin
926 hr_utility.set_location('Entering:'|| l_proc, 10);
927 --
928 -- Call another package to obtain the default component.
929 -- Note that p_ef_date is not currently used in the other package,
930 -- hence passing it just as a dummy date.
931 --
932 l_retro_component_id
933 := pay_retro_utils_pkg.get_retro_component_id
934 (p_element_entry_id => p_element_entry_id
935 ,p_ef_date => p_reprocess_date
936 ,p_element_type_id => p_element_type_id
937 ,p_asg_id => p_assignment_id
938 );
939
940 --
941 -- Note: if no default is found in get_retro_component_id,
942 -- it returns -1.
943 --
944 if nvl(l_retro_component_id, -1) = -1 then
945 --
946 -- No default component is defined for this element type.
947 --
948 l_element_name := get_element_name(p_element_entry_id);
949
950 fnd_message.set_name('PAY','PAY_34306_RTS_NO_DEF_CMP_AVL');
951 fnd_message.set_token('ELEMENT_NAME', l_element_name);
952 fnd_message.raise_error;
953 end if;
954
955 --
956 -- Set out variable.
957 --
958 return l_retro_component_id;
959
960 hr_utility.set_location(' Leaving:'|| l_proc, 50);
961 end get_default_retro_component_id;
962 --
963 -- -------------------------------------------------------------------------
964 -- |----------------------< chk_retro_component_id >-----------------------|
965 -- -------------------------------------------------------------------------
966 -- {Start Of Comments}
967 --
968 -- Description:
969 -- Validates that the retro component id exists in pay_retro_components.
970 --
971 -- Prerequisites:
972 -- None.
973 --
974 -- In Parameters:
975 -- p_retro_component_id
976 -- p_business_group_id
977 --
978 -- Post Success:
979 -- Processing continues if the retro component id is valid.
980 --
981 --
982 -- Post Failure:
983 -- An application error is raised and processing is terminated if the
984 -- retro component id is invalid.
985 --
986 -- {End Of Comments}
987 -- ----------------------------------------------------------------------------
988 procedure chk_retro_component_id
989 (p_retro_component_id in number
990 ,p_business_group_id in number
991 )
992 is
993 --
994 -- Declare local variables
995 --
996 l_proc varchar2(72) := g_package||'chk_retro_component_id';
997 l_exists number;
998 l_legislation_code pay_retro_component_usages.legislation_code%type;
999
1000 --
1001 -- Cursor to check that a retro component exists.
1002 --
1003 cursor csr_retro_component is
1004 select 1
1005 from pay_retro_components
1006 where retro_component_id = p_retro_component_id
1007 and nvl(legislation_code, l_legislation_code) = l_legislation_code
1008 ;
1009 --
1010 begin
1011 hr_utility.set_location('Entering:'|| l_proc, 10);
1012 --
1013 -- Check mandatory parameters have been set
1014 --
1015 hr_api.mandatory_arg_error
1016 (p_api_name => l_proc
1017 ,p_argument => 'retro_component_id'
1018 ,p_argument_value => p_retro_component_id
1019 );
1020 --
1021 -- Set the legislation code
1022 --
1023 l_legislation_code
1024 := hr_api.return_legislation_code(p_business_group_id);
1025 --
1026 -- Check if the retro component exists.
1027 --
1028 hr_utility.set_location(l_proc, 20);
1029 open csr_retro_component;
1030 fetch csr_retro_component into l_exists;
1031 if csr_retro_component%notfound then
1032 close csr_retro_component;
1033
1034 fnd_message.set_name('PAY','PAY_33167_RCU_INV_RETRO_COMP');
1035 fnd_message.raise_error;
1036
1037 end if;
1038 close csr_retro_component;
1039
1040 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1041 end chk_retro_component_id;
1042 --
1043 -- ----------------------------------------------------------------------------
1044 -- |-------------------------< chk_retro_comp_usage >-------------------------|
1045 -- ----------------------------------------------------------------------------
1046 -- {Start Of Comments}
1047 --
1048 -- Description:
1049 -- This procedure ensures that the element component usage is defined
1050 -- for the corresponding element type with the retro component.
1051 --
1052 -- Prerequisites:
1053 -- chk_entry_exists and chk_retro_component_id have already been passed.
1054 --
1055 -- In Parameters:
1056 -- p_element_type_id
1057 -- p_retro_component_id
1058 --
1059 -- Post Success:
1060 -- Processing continues.
1061 --
1062 -- Post Failure:
1063 -- An application error is raised.
1064 --
1065 -- {End Of Comments}
1066 -- ----------------------------------------------------------------------------
1067 procedure chk_retro_comp_usage
1068 (p_element_type_id in number
1069 ,p_retro_component_id in number
1070 )
1071 is
1072 l_dummy number;
1073 l_element_name pay_element_types_f_tl.element_name%type;
1074 l_component_name pay_retro_components.component_name%type;
1075 --
1076 cursor csr_rcu
1077 is
1078 select 1
1079 from
1080 pay_retro_component_usages rcu
1081 where
1082 rcu.creator_id = p_element_type_id
1083 and rcu.creator_type = 'ET'
1084 and rcu.retro_component_id = p_retro_component_id
1085 ;
1086 --
1087 cursor csr_ele
1088 is
1089 select
1090 pettl.element_name
1091 from
1092 pay_element_types_f_tl pettl
1093 where
1094 pettl.element_type_id = p_element_type_id
1095 and pettl.language = userenv('lang')
1096 ;
1097
1098 l_proc varchar2(72) := g_package||'chk_retro_comp_usage';
1099 begin
1100 hr_utility.set_location('Entering:'|| l_proc, 10);
1101 open csr_rcu;
1102 fetch csr_rcu into l_dummy;
1103 if csr_rcu%notfound then
1104 --
1105 -- The retro component is not defined for this element type.
1106 --
1107 open csr_ele;
1108 fetch csr_ele into l_element_name;
1109 close csr_ele;
1110 l_component_name := get_component_name(p_retro_component_id);
1111
1112 fnd_message.set_name('PAY','PAY_34307_RTS_INV_RTR_CMP_USG');
1113 fnd_message.set_token('ELEMENT_NAME', l_element_name);
1114 fnd_message.set_token('COMPONENT_NAME', l_component_name);
1115 fnd_message.raise_error;
1116 --
1117 end if;
1118 hr_utility.set_location(' Leaving:'||l_proc, 40);
1119
1120 end chk_retro_comp_usage;
1121 --
1122 -- ----------------------------------------------------------------------------
1123 -- |----------------------------< lock_retro_asg >----------------------------|
1124 -- ----------------------------------------------------------------------------
1125 procedure lock_retro_asg
1126 (p_retro_assignment_id in number
1127 ,p_old_rec out nocopy t_retro_asg_rec
1128 )
1129 is
1130 l_proc varchar2(72) := g_package||'lock_retro_asg';
1131 l_old_rec t_retro_asg_rec;
1132 --
1133 cursor csr_lock_retro_asg
1134 is
1135 select *
1136 from pay_retro_assignments
1137 where retro_assignment_id = p_retro_assignment_id
1138 for update nowait;
1139 begin
1140 hr_utility.set_location('Entering:'|| l_proc, 10);
1141 --
1142 open csr_lock_retro_asg;
1143 fetch csr_lock_retro_asg into l_old_rec;
1144 if csr_lock_retro_asg%notfound then
1145 close csr_lock_retro_asg;
1146 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
1147 fnd_message.raise_error;
1148 end if;
1149 close csr_lock_retro_asg;
1150 --
1151 hr_utility.set_location(l_proc, 20);
1152
1153 p_old_rec := l_old_rec;
1154
1155 hr_utility.set_location(' Leaving:'||l_proc, 40);
1156 Exception
1157 When HR_Api.Object_Locked then
1158 --
1159 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1160 fnd_message.set_token('TABLE_NAME', 'pay_retro_assignments');
1161 fnd_message.raise_error;
1162 end lock_retro_asg;
1163 --
1164 -- ----------------------------------------------------------------------------
1165 -- |----------------------------< decode_default >----------------------------|
1166 -- ----------------------------------------------------------------------------
1167 -- {Start Of Comments}
1168 --
1169 -- Description:
1170 -- This function is used to override the passed value with a specific value
1171 -- when the passed value was the same as the default value.
1172 --
1173 -- {End Of Comments}
1174 -- ----------------------------------------------------------------------------
1175 function decode_default
1176 (p_passed_value in varchar2
1177 ,p_default_value in varchar2
1178 ,p_override_value in varchar2
1179 ) return varchar2
1180 is
1181 begin
1182 if p_passed_value = p_default_value then
1183 return p_override_value;
1184 elsif (p_passed_value is null) and (p_default_value is null) then
1185 return p_override_value;
1186 else
1187 return p_passed_value;
1188 end if;
1189 end decode_default;
1190 --
1191 -- Number variable version.
1192 --
1193 function decode_default
1194 (p_passed_value in number
1195 ,p_default_value in number
1196 ,p_override_value in number
1197 ) return number
1198 is
1199 begin
1200 if p_passed_value = p_default_value then
1201 return p_override_value;
1202 elsif (p_passed_value is null) and (p_default_value is null) then
1203 return p_override_value;
1204 else
1205 return p_passed_value;
1206 end if;
1207 end decode_default;
1208 --
1209 -- Date variable version.
1210 --
1211 function decode_default
1212 (p_passed_value in date
1213 ,p_default_value in date
1214 ,p_override_value in date
1215 ) return date
1216 is
1217 begin
1218 if p_passed_value = p_default_value then
1219 return p_override_value;
1220 elsif (p_passed_value is null) and (p_default_value is null) then
1221 return p_override_value;
1222 else
1223 return p_passed_value;
1224 end if;
1225 end decode_default;
1226 --
1227 -- ----------------------------------------------------------------------------
1228 -- |------------------------< create_super_retro_asg >------------------------|
1229 -- ----------------------------------------------------------------------------
1230 --
1231 procedure create_super_retro_asg
1232 (p_assignment_id in number
1233 ,p_reprocess_date in date
1234 ,p_start_date in date default null
1235 ,p_approval_status in varchar2 default null
1236 ,p_owner_type in varchar2 default g_user
1237 ,p_retro_assignment_id out nocopy number
1238 )
1239 is
1240 l_retro_assignment_id number;
1241 l_old_retro_asg_id number;
1242 l_old_ra_rec t_retro_asg_rec;
1243 l_new_ra_rec t_retro_asg_rec;
1244 l_business_group_id number;
1245 l_payroll_id number;
1246 l_reprocess_date date;
1247 l_old_approval_status pay_retro_assignments.approval_status%type;
1248 l_approval_status pay_retro_assignments.approval_status%type;
1249 l_new_approval_status pay_retro_assignments.approval_status%type;
1250 l_new_reprocess_date date;
1251 l_new_start_date date;
1252 --
1253 cursor csr_app_status(p_retro_asg_id in number)
1254 is
1255 select approval_status
1256 from pay_retro_assignments
1257 where retro_assignment_id = p_retro_asg_id;
1258
1259 l_proc varchar2(72) := g_package||'create_super_retro_asg';
1260 begin
1261 hr_utility.set_location('Entering:'|| l_proc, 10);
1262 --
1263 -- Issue a savepoint
1264 --
1265 savepoint rts_create_super_retro_asg;
1266
1267 hr_api.mandatory_arg_error
1268 (p_api_name => l_proc
1269 ,p_argument => 'ASSIGNMENT_ID'
1270 ,p_argument_value => p_assignment_id
1271 );
1272
1273 --
1274 -- Owner type must be either U or S.
1275 --
1276 pay_core_utils.assert_condition
1277 (l_proc||':owner_type', p_owner_type in (g_user, g_system));
1278
1279 hr_utility.set_location(l_proc, 15);
1280 --
1281 -- Check to see if the previous version of retro assignment exists.
1282 --
1283 l_old_retro_asg_id := get_unprocessed_retro_asg(p_assignment_id);
1284
1285 if l_old_retro_asg_id is null then
1286 --
1287 -- Reprocess Date is mandatory
1288 --
1289 hr_api.mandatory_arg_error
1290 (p_api_name => l_proc
1291 ,p_argument => 'ASG REPROCESS_DATE'
1292 ,p_argument_value => p_reprocess_date
1293 );
1294 else
1295 --
1296 -- Lock and obtain the old record info.
1297 --
1298 lock_retro_asg
1299 (p_retro_assignment_id => l_old_retro_asg_id
1300 ,p_old_rec => l_old_ra_rec
1301 );
1302 --
1303 -- Check to see if this retro assignment is updatable.
1304 --
1305 chk_retro_asg_updatable
1306 (p_retro_assignment_id => l_old_retro_asg_id
1307 ,p_retro_assignment_action_id => l_old_ra_rec.retro_assignment_action_id
1308 ,p_superseding_retro_asg_id => l_old_ra_rec.superseding_retro_asg_id
1309 ,p_old_approval_status => l_old_ra_rec.approval_status
1310 ,p_new_approval_status => p_approval_status
1311 ,p_owner_type => p_owner_type
1312 ,p_dml_mode => g_update
1313 );
1314
1315 --
1316 -- Check to see if reprocess date is updatable.
1317 --
1318 if p_reprocess_date <> l_old_ra_rec.reprocess_date then
1319 chk_asg_rep_date_updatable
1320 (p_retro_assignment_id => l_old_retro_asg_id
1321 ,p_created_by => l_old_ra_rec.created_by
1322 ,p_owner_type => p_owner_type
1323 );
1324 end if;
1325 end if;
1326 --
1327 -- Set the reprocess date.
1328 --
1329 l_reprocess_date := nvl(p_reprocess_date, l_old_ra_rec.reprocess_date);
1330
1331 --
1332 -- Insert validation.
1333 --
1334 hr_utility.set_location(l_proc, 20);
1335 chk_assignment_id
1336 (p_assignment_id => p_assignment_id
1337 ,p_reprocess_date => l_reprocess_date
1338 ,p_business_group_id => l_business_group_id
1339 ,p_payroll_id => l_payroll_id
1340 );
1341
1342 if p_approval_status is not null then
1343
1344 chk_approval_status
1345 (p_business_group_id => l_business_group_id
1346 ,p_approval_status => p_approval_status
1347 ,p_owner_type => p_owner_type
1348 );
1349
1350 end if;
1351
1352 hr_utility.set_location(l_proc, 30);
1353 --
1354 -- Create superseding retro assignment.
1355 --
1356 pay_retro_utils_pkg.create_super_retro_asg
1357 (p_asg_id => p_assignment_id
1358 ,p_payroll_id => l_payroll_id
1359 ,p_reprocess_date => l_reprocess_date
1360 ,p_retro_asg_id => l_retro_assignment_id
1361 );
1362
1363 hr_utility.set_location(l_proc, 40);
1364 --
1365 -- If this is the user type, we might need to change automatically
1366 -- adjusted values.
1367 --
1368 if (p_owner_type = g_user) then
1369 --
1370 -- Relock the new record.
1371 --
1372 lock_retro_asg
1373 (p_retro_assignment_id => l_retro_assignment_id
1374 ,p_old_rec => l_new_ra_rec
1375 );
1376
1377 hr_utility.set_location(l_proc, 45);
1378 --
1379 -- Check the reprocess date.
1380 --
1381 if (p_reprocess_date <> l_new_ra_rec.reprocess_date) then
1382 hr_utility.set_location(l_proc, 50);
1383 --
1384 -- Check to see if this reprocess date is valid.
1385 --
1386 chk_retro_asg_reprocess_date
1387 (p_retro_assignment_id => l_retro_assignment_id
1388 ,p_reprocess_date => p_reprocess_date
1389 );
1390 --
1391 -- Set the new reprocess date.
1392 --
1393 l_new_reprocess_date := p_reprocess_date;
1394
1395 end if;
1396 --
1397 -- Check the start date.
1398 --
1399 if (p_start_date is null)
1400 or (p_start_date <> l_new_ra_rec.start_date) then
1401 hr_utility.set_location(l_proc, 55);
1402 --
1403 -- Set the new start date.
1404 --
1405 l_new_start_date := decode_default(p_start_date
1406 ,null
1407 ,p_reprocess_date);
1408 end if;
1409
1410 hr_utility.set_location(l_proc, 60);
1411 --
1412 -- As the above procedure doesn't accept approval status at the moment,
1413 -- we might need override the default one if necessary.
1414 --
1415 hr_utility.set_location(l_proc, 70);
1416 --
1417 l_old_approval_status := l_old_ra_rec.approval_status;
1418 l_approval_status := l_new_ra_rec.approval_status;
1419
1420 hr_utility.trace('Old status: '||l_old_approval_status);
1421 hr_utility.trace('Default status: '||l_approval_status);
1422 hr_utility.trace('Specified status: '||p_approval_status);
1423 --
1424 -- Identify the expected new approval status.
1425 --
1426 if (p_approval_status <> l_approval_status) then
1427 --
1428 -- Approval status is specified.
1429 --
1430 l_new_approval_status := p_approval_status;
1431 elsif (p_approval_status is null) and
1432 (l_approval_status <> l_old_approval_status) then
1433 --
1434 -- The approval status should be inherited.
1435 --
1436 l_new_approval_status := l_old_approval_status;
1437 end if;
1438 --
1439 -- Override the values if any outstanding change exists.
1440 --
1441 if (l_new_reprocess_date is not null)
1442 or (l_new_start_date is not null)
1443 or (l_new_approval_status is not null) then
1444 hr_utility.set_location(l_proc, 80);
1445 --
1446 update pay_retro_assignments
1447 set
1448 reprocess_date = nvl(l_new_reprocess_date, reprocess_date)
1449 ,start_date = nvl(l_new_start_date, start_date)
1450 ,approval_status = nvl(l_new_approval_status, approval_status)
1451 where retro_assignment_id = l_retro_assignment_id;
1452 --
1453 end if;
1454 end if;
1455
1456 hr_utility.set_location(l_proc, 90);
1457 --
1458 -- Set out variable.
1459 --
1460 p_retro_assignment_id := l_retro_assignment_id;
1461
1462 hr_utility.set_location(' Leaving:'||l_proc, 100);
1463 exception
1464 when others then
1465 --
1466 -- A validation or unexpected error has occured
1467 --
1468 rollback to rts_create_super_retro_asg;
1469 hr_utility.set_location(' Leaving:'||l_proc, 120);
1470 raise;
1471 end create_super_retro_asg;
1472 --
1473 -- ----------------------------------------------------------------------------
1474 -- |---------------------------< update_retro_asg >---------------------------|
1475 -- ----------------------------------------------------------------------------
1476 procedure update_retro_asg
1477 (p_retro_assignment_id in number
1478 ,p_reprocess_date in date default hr_api.g_date
1479 ,p_start_date in date default hr_api.g_date
1480 ,p_approval_status in varchar2 default hr_api.g_varchar2
1481 ,p_owner_type in varchar2 default g_user
1482 )
1483 is
1484 l_proc varchar2(72) := g_package||'update_retro_asg';
1485 l_old_rec t_retro_asg_rec;
1486 l_new_rec t_retro_asg_rec;
1487 l_business_group_id number;
1488 l_payroll_id number;
1489 begin
1490 hr_utility.set_location('Entering:'|| l_proc, 10);
1491 --
1492 -- Issue a savepoint
1493 --
1494 savepoint rts_update_retro_asg;
1495 --
1496 hr_api.mandatory_arg_error
1497 (p_api_name => l_proc
1498 ,p_argument => 'RETRO_ASSIGNMENT_ID'
1499 ,p_argument_value => p_retro_assignment_id
1500 );
1501
1502 --
1503 -- Owner type must be either U or S.
1504 --
1505 pay_core_utils.assert_condition
1506 (l_proc||':owner_type', p_owner_type in (g_user, g_system));
1507
1508 hr_utility.set_location(l_proc, 20);
1509 --
1510 -- Lock the existing retro assignment.
1511 --
1512 lock_retro_asg
1513 (p_retro_assignment_id => p_retro_assignment_id
1514 ,p_old_rec => l_old_rec
1515 );
1516
1517 hr_utility.set_location(l_proc, 30);
1518 --
1519 -- Copy the changed values.
1520 --
1521 l_new_rec := l_old_rec;
1522 --
1523 l_new_rec.reprocess_date := decode_default(p_reprocess_date
1524 ,hr_api.g_date
1525 ,l_old_rec.reprocess_date);
1526 l_new_rec.start_date := decode_default(p_start_date
1527 ,hr_api.g_date
1528 ,l_old_rec.start_date);
1529 l_new_rec.approval_status := decode_default(p_approval_status
1530 ,hr_api.g_varchar2
1531 ,l_old_rec.approval_status);
1532 hr_utility.set_location(l_proc, 40);
1533 --
1534 -- Update validation.
1535 --
1536 chk_retro_asg_updatable
1537 (p_retro_assignment_id => p_retro_assignment_id
1538 ,p_retro_assignment_action_id => l_old_rec.retro_assignment_action_id
1539 ,p_superseding_retro_asg_id => l_old_rec.superseding_retro_asg_id
1540 ,p_old_approval_status => l_old_rec.approval_status
1541 --
1542 -- setting null if approval status is defaulted.
1543 --
1544 ,p_new_approval_status => decode_default
1545 (p_approval_status
1546 ,hr_api.g_varchar2
1547 ,null)
1548 ,p_owner_type => p_owner_type
1549 ,p_dml_mode => g_update
1550 );
1551
1552 chk_assignment_id
1553 (p_assignment_id => l_new_rec.assignment_id
1554 ,p_reprocess_date => l_new_rec.reprocess_date
1555 ,p_business_group_id => l_business_group_id
1556 ,p_payroll_id => l_payroll_id
1557 );
1558
1559 if l_old_rec.approval_status <> l_new_rec.approval_status then
1560 hr_utility.set_location(l_proc, 45);
1561 chk_approval_status
1562 (p_business_group_id => l_business_group_id
1563 ,p_approval_status => l_new_rec.approval_status
1564 ,p_owner_type => p_owner_type
1565 );
1566 end if;
1567
1568 if l_old_rec.reprocess_date <> l_new_rec.reprocess_date then
1569 hr_utility.set_location(l_proc, 50);
1570 chk_asg_rep_date_updatable
1571 (p_retro_assignment_id => p_retro_assignment_id
1572 ,p_created_by => l_old_rec.created_by
1573 ,p_owner_type => p_owner_type
1574 );
1575
1576 chk_retro_asg_reprocess_date
1577 (p_retro_assignment_id => p_retro_assignment_id
1578 ,p_reprocess_date => l_new_rec.reprocess_date
1579 );
1580 --
1581 -- If the start date was not specified, change the start date
1582 -- as well.
1583 --
1584 l_new_rec.start_date := decode_default(p_start_date
1585 ,hr_api.g_date
1586 ,l_new_rec.reprocess_date);
1587 end if;
1588
1589 hr_utility.set_location(l_proc, 60);
1590 --
1591 -- Now update the retro assignment.
1592 --
1593 update pay_retro_assignments
1594 set reprocess_date = l_new_rec.reprocess_date
1595 ,start_date = l_new_rec.start_date
1596 ,approval_status = l_new_rec.approval_status
1597 where retro_assignment_id = p_retro_assignment_id;
1598
1599 hr_utility.set_location(' Leaving:'||l_proc, 80);
1600 exception
1601 when others then
1602 --
1603 -- A validation or unexpected error has occured
1604 --
1605 rollback to rts_update_retro_asg;
1606 hr_utility.set_location(' Leaving:'||l_proc, 90);
1607 raise;
1608 end update_retro_asg;
1609 --
1610 -- ----------------------------------------------------------------------------
1611 -- |------------------------< adjust_retro_asg_date >-------------------------|
1612 -- ----------------------------------------------------------------------------
1613 -- {Start Of Comments}
1614 --
1615 -- Description:
1616 -- This procedure adjusts the reprocess date and the start date of the retro
1617 -- assignment with the ealiest date of the child retro entries.
1618 --
1619 -- Prerequisites:
1620 -- None.
1621 --
1622 -- In Parameters:
1623 -- Name Reqd Type Description
1624 -- p_retro_assignment_id Yes Number Retro Assignment ID.
1625 --
1626 -- Post Success:
1627 -- The procedure will set the following out parameters:
1628 -- Name Type Description
1629 --
1630 -- Post Failure:
1631 -- The procedure will not update a retro assignment and raises an error.
1632 --
1633 -- Access Status:
1634 -- Internal Development Use Only.
1635 --
1636 -- {End Of Comments}
1637 -- ----------------------------------------------------------------------------
1638 procedure adjust_retro_asg_date
1639 (p_retro_assignment_id in number
1640 )
1641 is
1642 l_proc varchar2(72) := g_package||'adjust_retro_asg_date';
1643 l_reprocess_date date;
1644 l_start_date date;
1645 l_old_rec t_retro_asg_rec;
1646 begin
1647 hr_utility.set_location('Entering:'|| l_proc, 10);
1648
1649 select
1650 min(reprocess_date)
1651 ,min(effective_date)
1652 into
1653 l_reprocess_date
1654 ,l_start_date
1655 from pay_retro_entries
1656 where
1657 retro_assignment_id = p_retro_assignment_id
1658 ;
1659
1660 hr_utility.set_location(l_proc, 20);
1661 --
1662 -- Continue only when retro entry exists.
1663 --
1664 if l_reprocess_date is not null then
1665 hr_utility.set_location(l_proc, 30);
1666 --
1667 -- Lock the retro assignment.
1668 --
1669 lock_retro_asg
1670 (p_retro_assignment_id => p_retro_assignment_id
1671 ,p_old_rec => l_old_rec
1672 );
1673
1674 if (l_reprocess_date < l_old_rec.reprocess_date)
1675 or (l_start_date < l_old_rec.start_date) then
1676 --
1677 hr_utility.set_location(l_proc, 40);
1678 --
1679 update pay_retro_assignments
1680 set reprocess_date = least(l_reprocess_date, l_old_rec.reprocess_date)
1681 ,start_date = least(l_start_date, l_old_rec.start_date)
1682 where retro_assignment_id = p_retro_assignment_id;
1683 end if;
1684 end if;
1685
1686 hr_utility.set_location(' Leaving:'||l_proc, 80);
1687 end adjust_retro_asg_date;
1688 --
1689 -- ----------------------------------------------------------------------------
1690 -- |---------------------------< delete_retro_asg >---------------------------|
1691 -- ----------------------------------------------------------------------------
1692 procedure delete_retro_asg
1693 (p_retro_assignment_id in number
1694 ,p_owner_type in varchar2 default g_user
1695 ,p_replaced_retro_asg_id out nocopy number
1696 )
1697 is
1698 l_proc varchar2(72) := g_package||'delete_retro_asg';
1699 l_old_rec t_retro_asg_rec;
1700 l_replaced_retro_asg_id number;
1701 --
1702 cursor csr_superseding_retro_asg
1703 (p_retro_asg_id number
1704 ,p_asg_id number)
1705 is
1706 select retro_assignment_id
1707 from pay_retro_assignments
1708 where superseding_retro_asg_id = p_retro_asg_id
1709 ;
1710 --
1711 begin
1712 hr_utility.set_location('Entering:'|| l_proc, 10);
1713 --
1714 -- Issue a savepoint
1715 --
1716 savepoint rts_delete_retro_asg;
1717 --
1718 hr_api.mandatory_arg_error
1719 (p_api_name => l_proc
1720 ,p_argument => 'RETRO_ASSIGNMENT_ID'
1721 ,p_argument_value => p_retro_assignment_id
1722 );
1723
1724 --
1725 -- Owner type must be either U or S.
1726 --
1727 pay_core_utils.assert_condition
1728 (l_proc||':owner_type', p_owner_type in (g_user, g_system));
1729
1730 hr_utility.set_location(l_proc, 20);
1731 --
1732 -- Lock the existing retro assignment.
1733 --
1734 lock_retro_asg
1735 (p_retro_assignment_id => p_retro_assignment_id
1736 ,p_old_rec => l_old_rec
1737 );
1738
1739 hr_utility.set_location(l_proc, 30);
1740 --
1741 -- Delete validation.
1742 --
1743 chk_retro_asg_updatable
1744 (p_retro_assignment_id => p_retro_assignment_id
1745 ,p_retro_assignment_action_id => l_old_rec.retro_assignment_action_id
1746 ,p_superseding_retro_asg_id => l_old_rec.superseding_retro_asg_id
1747 ,p_old_approval_status => l_old_rec.approval_status
1748 ,p_new_approval_status => null
1749 ,p_owner_type => p_owner_type
1750 ,p_dml_mode => g_delete
1751 );
1752
1753 hr_utility.set_location(l_proc, 50);
1754 --
1755 -- Delete child retro entries.
1756 --
1757 delete from pay_retro_entries pre
1758 where retro_assignment_id = p_retro_assignment_id;
1759
1760 --
1761 -- Reverse the child retro assignment superseded by this retro assignment.
1762 --
1763 for l_rec in csr_superseding_retro_asg(p_retro_assignment_id
1764 ,l_old_rec.assignment_id) loop
1765 --
1766 l_replaced_retro_asg_id := l_rec.retro_assignment_id;
1767 --
1768 update pay_retro_assignments
1769 set superseding_retro_asg_id = null
1770 where retro_assignment_id = l_replaced_retro_asg_id;
1771
1772 end loop;
1773
1774 hr_utility.set_location(l_proc, 60);
1775 --
1776 -- Delete this retro assignment.
1777 --
1778 delete from pay_retro_assignments
1779 where retro_assignment_id = p_retro_assignment_id;
1780
1781 --
1782 -- Set out variable.
1783 --
1784 p_replaced_retro_asg_id := l_replaced_retro_asg_id;
1785
1786 hr_utility.set_location(' Leaving:'||l_proc, 80);
1787 exception
1788 when others then
1789 --
1790 -- A validation or unexpected error has occured
1791 --
1792 rollback to rts_delete_retro_asg;
1793 hr_utility.set_location(' Leaving:'||l_proc, 90);
1794 raise;
1795 end delete_retro_asg;
1796 --
1797 -- ----------------------------------------------------------------------------
1798 -- |-----------------------< delete_retro_asg_cascade >-----------------------|
1799 -- ----------------------------------------------------------------------------
1800 procedure delete_retro_asg_cascade
1801 (p_retro_assignment_id in number
1802 ,p_owner_type in varchar2 default g_user
1803 )
1804 is
1805 l_proc varchar2(72) := g_package||'delete_retro_asg_cascade';
1806 l_replaced_retro_asg_id number;
1807 l_retro_assignment_id number;
1808 --
1809 begin
1810 hr_utility.set_location('Entering:'|| l_proc, 10);
1811 --
1812 -- Issue a savepoint
1813 --
1814 savepoint rts_delete_retro_asg_cascade;
1815 --
1816 hr_api.mandatory_arg_error
1817 (p_api_name => l_proc
1818 ,p_argument => 'RETRO_ASSIGNMENT_ID'
1819 ,p_argument_value => p_retro_assignment_id
1820 );
1821 --
1822 l_retro_assignment_id := p_retro_assignment_id;
1823 --
1824 while (l_retro_assignment_id is not null) loop
1825
1826 delete_retro_asg
1827 (p_retro_assignment_id => l_retro_assignment_id
1828 ,p_owner_type => p_owner_type
1829 ,p_replaced_retro_asg_id => l_replaced_retro_asg_id
1830 );
1831 l_retro_assignment_id := l_replaced_retro_asg_id;
1832 end loop;
1833
1834 hr_utility.set_location(' Leaving:'||l_proc, 80);
1835 exception
1836 when others then
1837 --
1838 -- A validation or unexpected error has occured
1839 --
1840 rollback to rts_delete_retro_asg_cascade;
1841 hr_utility.set_location(' Leaving:'||l_proc, 90);
1842 raise;
1843 end delete_retro_asg_cascade;
1844 --
1845 -- ----------------------------------------------------------------------------
1846 -- |---------------------------< lock_retro_entry >---------------------------|
1847 -- ----------------------------------------------------------------------------
1848 procedure lock_retro_entry
1849 (p_retro_assignment_id in number
1850 ,p_element_entry_id in number
1851 ,p_old_rec out nocopy t_retro_ent_rec
1852 )
1853 is
1854 l_proc varchar2(72) := g_package||'lock_retro_entry';
1855 l_old_rec t_retro_ent_rec;
1856 --
1857 cursor csr_lock_retro_ent
1858 is
1859 select *
1860 from pay_retro_entries
1861 where retro_assignment_id = p_retro_assignment_id
1862 and element_entry_id = p_element_entry_id
1863 for update nowait;
1864 begin
1865 hr_utility.set_location('Entering:'|| l_proc, 10);
1866 --
1867 -- NOTE: do not check no data found.
1868 --
1869 open csr_lock_retro_ent;
1870 fetch csr_lock_retro_ent into l_old_rec;
1871 close csr_lock_retro_ent;
1872 --
1873 hr_utility.set_location(l_proc, 20);
1874
1875 p_old_rec := l_old_rec;
1876
1877 hr_utility.set_location(' Leaving:'||l_proc, 40);
1878 Exception
1879 When HR_Api.Object_Locked then
1880 --
1881 fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1882 fnd_message.set_token('TABLE_NAME', 'pay_retro_entries');
1883 fnd_message.raise_error;
1884 end lock_retro_entry;
1885 --
1886 -- ----------------------------------------------------------------------------
1887 -- |-------------------------< maintain_retro_entry >-------------------------|
1888 -- ----------------------------------------------------------------------------
1889 procedure maintain_retro_entry
1890 (p_retro_assignment_id in number
1891 ,p_element_entry_id in number
1892 ,p_reprocess_date in date
1893 ,p_effective_date in date default null
1894 ,p_retro_component_id in number default null
1895 ,p_owner_type in varchar2 default g_user
1896 ,p_system_reprocess_date in date default hr_api.g_eot
1897 ,p_entry_param_name in varchar2 default null
1898 )
1899 is
1900 l_proc varchar2(72) := g_package||'maintain_retro_entry';
1901 l_retro_asg_rec t_retro_asg_rec;
1902 l_old_rec t_retro_ent_rec;
1903 l_new_rec t_retro_ent_rec;
1904 l_assignment_id number;
1905 l_asg_owner_type varchar2(10);
1906 l_element_type_id number;
1907 l_element_name pay_element_types_f_tl.element_name%type;
1908 l_business_group_id number;
1909 l_legislation_code per_business_groups.legislation_code%type;
1910 l_component_name pay_retro_components.component_name%type;
1911 l_payroll_id number;
1912 l_retro_component_id number;
1913 l_reprocess_date date;
1914 l_system_reprocess_date date;
1915 --
1916 begin
1917 hr_utility.set_location('Entering:'|| l_proc, 10);
1918 --
1919 -- Issue a savepoint
1920 --
1921 savepoint rts_maintain_retro_entry;
1922
1923 hr_api.mandatory_arg_error
1924 (p_api_name => l_proc
1925 ,p_argument => 'RETRO_ASSIGNMENT_ID'
1926 ,p_argument_value => p_retro_assignment_id
1927 );
1928
1929 hr_api.mandatory_arg_error
1930 (p_api_name => l_proc
1931 ,p_argument => 'ELEMENT_ENTRY_ID'
1932 ,p_argument_value => p_element_entry_id
1933 );
1934
1935 --
1936 -- Owner type must be either U or S.
1937 --
1938 pay_core_utils.assert_condition
1939 (l_proc||':owner_type', p_owner_type in (g_user, g_system));
1940
1941 hr_utility.set_location(l_proc, 20);
1942 --
1943 -- Lock the retro assignment.
1944 --
1945 lock_retro_asg
1946 (p_retro_assignment_id => p_retro_assignment_id
1947 ,p_old_rec => l_retro_asg_rec
1948 );
1949
1950 -- Copy the assignment ID.
1951 l_assignment_id := l_retro_asg_rec.assignment_id;
1952
1953 --
1954 -- Try to find and lock the existing retro entry.
1955 --
1956 lock_retro_entry
1957 (p_retro_assignment_id => p_retro_assignment_id
1958 ,p_element_entry_id => p_element_entry_id
1959 ,p_old_rec => l_old_rec
1960 );
1961
1962 hr_utility.set_location(l_proc, 50);
1963 --
1964 -- Insert and update validations
1965 --
1966 if l_old_rec.retro_assignment_id is null then
1967 hr_api.mandatory_arg_error
1968 (p_api_name => l_proc
1969 ,p_argument => nvl(p_entry_param_name,'ENTRY')
1970 ||' REPROCESS_DATE'
1971 ,p_argument_value => p_reprocess_date
1972 );
1973 end if;
1974
1975 chk_element_entry_id
1976 (p_element_entry_id => p_element_entry_id
1977 ,p_assignment_id => l_assignment_id
1978 ,p_element_type_id => l_element_type_id
1979 );
1980 --
1981 chk_assignment_id
1982 (p_assignment_id => l_assignment_id
1983 ,p_reprocess_date => nvl(p_reprocess_date, l_old_rec.reprocess_date)
1984 ,p_business_group_id => l_business_group_id
1985 ,p_payroll_id => l_payroll_id
1986 );
1987 --
1988 hr_utility.set_location(l_proc, 80);
1989 if p_owner_type = g_user then
1990 l_asg_owner_type := get_retro_asg_creator_type
1991 (p_retro_assignment_id => p_retro_assignment_id
1992 ,p_created_by => l_retro_asg_rec.created_by
1993 );
1994 --
1995 chk_ent_reprocess_date
1996 (p_retro_assignment_id => p_retro_assignment_id
1997 ,p_element_entry_id => p_element_entry_id
1998 ,p_reprocess_date => p_reprocess_date
1999 ,p_old_reprocess_date => l_old_rec.reprocess_date
2000 ,p_system_reprocess_date => l_old_rec.system_reprocess_date
2001 ,p_asg_reprocess_date => l_retro_asg_rec.reprocess_date
2002 ,p_asg_owner_type => l_asg_owner_type
2003 ,p_created_by => l_old_rec.created_by
2004 ,p_old_owner_type => l_old_rec.owner_type
2005 ,p_owner_type => p_owner_type
2006 );
2007 end if;
2008 hr_utility.set_location(l_proc, 100);
2009 l_reprocess_date := nvl(p_reprocess_date, l_old_rec.reprocess_date);
2010
2011 l_retro_component_id := p_retro_component_id;
2012 l_legislation_code := hr_api.return_legislation_code(l_business_group_id);
2013 --
2014 if p_retro_component_id is null then
2015 hr_utility.set_location(l_proc, 110);
2016 --
2017 -- We need derive the default value for retro component.
2018 --
2019 if (l_old_rec.retro_assignment_id is null) then
2020 --
2021 hr_utility.set_location(l_proc, 120);
2022 l_retro_component_id :=
2023 get_default_retro_component_id
2024 (p_element_entry_id => p_element_entry_id
2025 ,p_reprocess_date => l_reprocess_date
2026 ,p_element_type_id => l_element_type_id
2027 ,p_assignment_id => l_assignment_id
2028 );
2029 else
2030 hr_utility.set_location(l_proc, 130);
2031 --
2032 -- Inherit the previous value.
2033 --
2034 l_retro_component_id := l_old_rec.retro_component_id;
2035
2036 end if;
2037 end if;
2038 --
2039 hr_utility.set_location(l_proc, 140);
2040 --
2041 if (l_old_rec.retro_assignment_id is null)
2042 or (l_old_rec.retro_component_id <> l_retro_component_id) then
2043 --
2044 hr_utility.set_location(l_proc, 150);
2045 chk_retro_component_id
2046 (p_retro_component_id => l_retro_component_id
2047 ,p_business_group_id => l_business_group_id
2048 );
2049 --
2050 chk_retro_comp_usage
2051 (p_element_type_id => l_element_type_id
2052 ,p_retro_component_id => l_retro_component_id
2053 );
2054 end if;
2055
2056 hr_utility.set_location(l_proc, 160);
2057 --
2058 -- If system reprocess date is set to null, override the value with
2059 -- the previous value, for null is available for user type entries.
2060 -- Note that if the value is EOT, it will be handled in
2061 -- pay_retro_pkg.maintain_retro_entry.
2062 --
2063 l_system_reprocess_date
2064 := nvl(p_system_reprocess_date, l_old_rec.system_reprocess_date);
2065
2066 pay_retro_pkg.maintain_retro_entry
2067 (p_retro_assignment_id => p_retro_assignment_id
2068 ,p_element_entry_id => p_element_entry_id
2069 ,p_element_type_id => l_element_type_id
2070 ,p_reprocess_date => l_reprocess_date
2071 ,p_eff_date => nvl(p_effective_date, l_reprocess_date)
2072 ,p_retro_component_id => l_retro_component_id
2073 ,p_owner_type => p_owner_type
2074 ,p_system_reprocess_date => l_system_reprocess_date
2075 );
2076
2077 --
2078 -- Ensure that the reprocess date is maintained appropriately.
2079 --
2080 if p_owner_type = g_user then
2081 --
2082 if l_old_rec.reprocess_date <> p_reprocess_date then
2083 hr_utility.set_location(l_proc, 180);
2084 lock_retro_entry
2085 (p_retro_assignment_id => p_retro_assignment_id
2086 ,p_element_entry_id => p_element_entry_id
2087 ,p_old_rec => l_new_rec
2088 );
2089 --
2090 if p_reprocess_date <> l_new_rec.reprocess_date then
2091 --
2092 hr_utility.set_location(l_proc, 190);
2093 --
2094 update pay_retro_entries
2095 set reprocess_date = p_reprocess_date
2096 ,effective_date = p_reprocess_date
2097 where
2098 retro_assignment_id = p_retro_assignment_id
2099 and element_entry_id = p_element_entry_id;
2100 --
2101 end if;
2102 end if;
2103 else
2104 hr_utility.set_location(l_proc, 200);
2105 adjust_retro_asg_date
2106 (p_retro_assignment_id => p_retro_assignment_id
2107 );
2108 end if;
2109 --
2110 hr_utility.set_location(' Leaving:'||l_proc, 250);
2111 exception
2112 when others then
2113 --
2114 -- A validation or unexpected error has occured
2115 --
2116 rollback to rts_maintain_retro_entry;
2117 hr_utility.set_location(' Leaving:'||l_proc, 260);
2118 raise;
2119 end maintain_retro_entry;
2120 --
2121 -- ----------------------------------------------------------------------------
2122 -- |--------------------------< delete_retro_entry >--------------------------|
2123 -- ----------------------------------------------------------------------------
2124 procedure delete_retro_entry
2125 (p_retro_assignment_id in number
2126 ,p_element_entry_id in number
2127 ,p_owner_type in varchar2 default g_user
2128 )
2129 is
2130 l_proc varchar2(72) := g_package||'delete_retro_entry';
2131 l_old_rec t_retro_ent_rec;
2132 l_new_rec t_retro_ent_rec;
2133 begin
2134 hr_utility.set_location('Entering:'|| l_proc, 10);
2135 --
2136 -- Issue a savepoint
2137 --
2138 savepoint rts_delete_retro_entry;
2139
2140 hr_api.mandatory_arg_error
2141 (p_api_name => l_proc
2142 ,p_argument => 'RETRO_ASSIGNMENT_ID'
2143 ,p_argument_value => p_retro_assignment_id
2144 );
2145
2146 hr_api.mandatory_arg_error
2147 (p_api_name => l_proc
2148 ,p_argument => 'ELEMENT_ENTRY_ID'
2149 ,p_argument_value => p_element_entry_id
2150 );
2151
2152 --
2153 -- Owner type must be either U or S.
2154 --
2155 pay_core_utils.assert_condition
2156 (l_proc||':owner_type', p_owner_type in (g_user, g_system));
2157
2158 hr_utility.set_location(l_proc, 20);
2159 --
2160 -- Lock the existing retro entry.
2161 --
2162 lock_retro_entry
2163 (p_retro_assignment_id => p_retro_assignment_id
2164 ,p_element_entry_id => p_element_entry_id
2165 ,p_old_rec => l_old_rec
2166 );
2167
2168 if l_old_rec.retro_assignment_id is null then
2169 hr_utility.set_location(l_proc, 30);
2170 --
2171 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
2172 fnd_message.raise_error;
2173 end if;
2174
2175 hr_utility.set_location(l_proc, 50);
2176 --
2177 -- Delete validation.
2178 --
2179 chk_retro_entry_deletable
2180 (p_element_entry_id => p_element_entry_id
2181 ,p_old_owner_type => l_old_rec.owner_type
2182 ,p_owner_type => p_owner_type
2183 );
2184
2185 hr_utility.set_location(l_proc, 60);
2186
2187 delete from pay_retro_entries
2188 where retro_assignment_id = p_retro_assignment_id
2189 and element_entry_id = p_element_entry_id
2190 ;
2191
2192 hr_utility.set_location(' Leaving:'||l_proc, 80);
2193 exception
2194 when others then
2195 --
2196 -- A validation or unexpected error has occured
2197 --
2198 rollback to rts_delete_retro_entry;
2199 hr_utility.set_location(' Leaving:'||l_proc, 90);
2200 raise;
2201 end delete_retro_entry;
2202 --
2203 -- ----------------------------------------------------------------------------
2204 -- |------------------------< update_reprocess_date >-------------------------|
2205 -- ----------------------------------------------------------------------------
2206 --
2207 procedure update_reprocess_date(
2208 p_assignment_id in number
2209 ,p_reprocess_date in date
2210 ,p_owner_type in varchar2 default g_user
2211 ,p_retro_asg_id out nocopy number) is
2212
2213 l_retro_assignment_id number;
2214 l_old_retro_asg_id number;
2215 l_old_ra_rec t_retro_asg_rec;
2216 l_business_group_id number;
2217 l_payroll_id number;
2218 l_reprocess_date date;
2219 l_old_approval_status pay_retro_assignments.approval_status%type;
2220 l_new_approval_status pay_retro_assignments.approval_status%type;
2221 l_new_reprocess_date date;
2222
2223 l_proc varchar2(72) := g_package||'update_reprocess_date';
2224 begin
2225
2226 hr_utility.set_location('Entering:'|| l_proc, 10);
2227 --
2228 -- Issue a savepoint
2229 --
2230 savepoint rts_update_reprocess_date;
2231
2232 hr_api.mandatory_arg_error
2233 (p_api_name => l_proc
2234 ,p_argument => 'ASSIGNMENT_ID'
2235 ,p_argument_value => p_assignment_id
2236 );
2237
2238 --
2239 -- Owner type must be either U or S.
2240 --
2241 pay_core_utils.assert_condition
2242 (l_proc||':owner_type', p_owner_type in (g_user, g_system));
2243
2244 hr_utility.set_location('Getting the retro assignment '||l_proc, 15);
2245 --
2246 -- Check to see if the previous version of retro assignment exists.
2247 --
2248 l_old_retro_asg_id := get_unprocessed_retro_asg(p_assignment_id);
2249
2250 if l_old_retro_asg_id is null then
2251 --
2252 --
2253 -- No unprocessed retro assignment is found.
2254 --
2255 hr_utility.set_location('No retro assignment found for this assignment : '||l_proc, 15);
2256 fnd_message.set_name('PAY','PAY_34312_RTS_NO_RTA_FOUND');
2257 fnd_message.raise_error;
2258 --
2259 else
2260 --
2261 hr_utility.set_location('Got retro assignment id and now locking : '||l_proc, 15);
2262 --
2263 -- Lock and obtain the old record info.
2264 --
2265 lock_retro_asg
2266 (p_retro_assignment_id => l_old_retro_asg_id
2267 ,p_old_rec => l_old_ra_rec
2268 );
2269 --
2270 -- Check to see if this retro assignment is updatable.
2271 hr_utility.set_location('Calling chk_retro_asg_updatable : '||l_proc,20);
2272 chk_retro_asg_updatable
2273 (p_retro_assignment_id => l_old_retro_asg_id
2274 ,p_retro_assignment_action_id => l_old_ra_rec.retro_assignment_action_id
2275 ,p_superseding_retro_asg_id => l_old_ra_rec.superseding_retro_asg_id
2276 ,p_old_approval_status => l_old_ra_rec.approval_status
2277 ,p_new_approval_status => l_old_ra_rec.approval_status
2278 ,p_owner_type => p_owner_type
2279 ,p_dml_mode => g_update
2280 );
2281
2282 --
2283 --Checking whether reprocess date is later than the existing reprocess date.
2284 chk_retro_asg_reprocess_date
2285 (p_retro_assignment_id => l_old_retro_asg_id
2286 ,p_reprocess_date => p_reprocess_date);
2287 --
2288 -- Insert validation.
2289 --
2290 hr_utility.set_location('Validates that the employee assignment exists on the reprocess date : '||l_proc, 25);
2291 chk_assignment_id
2292 (p_assignment_id => p_assignment_id
2293 ,p_reprocess_date => p_reprocess_date
2294 ,p_business_group_id => l_business_group_id
2295 ,p_payroll_id => l_payroll_id
2296 );
2297
2298
2299 hr_utility.set_location('Creating superseding retro assignment : '||l_proc, 30);
2300 --
2301 -- Create superseding retro assignment.
2302 --
2303 pay_retro_utils_pkg.create_super_retro_asg
2304 (p_asg_id => p_assignment_id
2305 ,p_payroll_id => l_payroll_id
2306 ,p_reprocess_date => p_reprocess_date
2307 ,p_retro_asg_id => l_retro_assignment_id
2308 );
2309 p_retro_asg_id := l_retro_assignment_id;
2310 hr_utility.set_location('Leaving .... '||l_proc, 40);
2311 --
2312 end if;
2313
2314 exception
2315 when others then
2316 --
2317 -- A validation or unexpected error has occured
2318 --
2319 rollback to rts_update_reprocess_date;
2320 hr_utility.set_location(' Leaving:'||l_proc, 90);
2321 raise;
2322
2323 end;
2324 end pay_retro_status_internal;