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