1 PACKAGE BODY PER_ASSIGNMENTS_F3_PKG AS
2 /* $Header: peasg01t.pkb 120.29.12010000.3 2008/09/29 05:42:27 ubhat ship $ */
3 -----------------------------------------------------------------------------
4 --
5 g_package varchar2(24) := 'PER_ASSIGNMENTS_F3_PKG.';
6 g_debug boolean; -- debug flag
7 --
8 procedure process_end_status(
9 p_ass_id number,
10 p_sess_date date,
11 p_eff_end_date date,
12 p_eot date) is
13 --
14 -- If the status is changed to 'END' then if there are any future
15 -- changes then disallow the update.
16 ---
17 begin
18 hr_utility.set_location('Entering: '|| 'PER_ASSIGNMENTS_F3_PKG.process_end_status', 5);
19 if p_eff_end_date < p_eot then
20 fnd_message.set_name('PAY', 'HR_6071_APP_ASS_INVALID_END');
21 fnd_message.raise_error;
22 end if;
23 --
24 -- Now check delete referential integrity as it will cause date
25 -- effective delete.
26 --
27 hr_assignment.del_ref_int_check(p_ass_id, 'END', p_sess_date);
28 --
29 hr_utility.set_location('Leaving: '|| 'PER_ASSIGNMENTS_F3_PKG.process_end_status', 5);
30 end process_end_status;
31 ---------------------------------------------------------------------------
32 procedure process_term_status(
33 p_ass_id number,
34 p_sess_date date) is
35 l_dummy number;
36 --
37 -- If the status is changed to 'TERM' then if there are any future
38 -- other than TERM_ASSIGNs changes then disallow the update.
39 --
40 cursor term_assign is
41 select 1
42 from per_assignments_f a
43 where a.assignment_id = P_ASS_ID
44 and a.effective_start_date > P_SESS_DATE
45 and exists (select null
46 from per_assignment_status_types s
47 where s.assignment_status_type_id =
48 a.assignment_status_type_id
49 and s.per_system_status <> 'TERM_ASSIGN');
50 begin
51 hr_utility.set_location('Entering: '|| 'PER_ASSIGNMENTS_F3_PKG.process_term_status' , 5);
52 open term_assign;
53 fetch term_assign into l_dummy;
54 if term_assign%found then
55 fnd_message.set_name('PAY', 'HR_6387_EMP_ASS_INVALID_TERM');
56 close term_assign;
57 fnd_message.raise_error;
58 end if;
59 close term_assign;
60 end process_term_status;
61 -----------------------------------------------------------------------------
62 procedure validate_primary_flag(
63 p_val_st_date date,
64 p_pd_os_id number,
65 p_ass_id number) is
66 --
67 -- A Primary Assignment must run until NVL(FINAL_PROCESS_DATE,END_OF_TIME)
68 -- for the Period of Service. If it is terminated the termination must be
69 -- as a result of termination of the Employees Period of Service i.e. the
70 -- first termination date is on the Actual Termination Date.
71 --
72 -- HR_ASSIGNMENT.CHECK_ASS_FOR_PRIMARY performs these checks on an
73 -- assignment that has had its PRIMARY FLAG set to 'Yes'
74 --
75 begin
76 hr_utility.set_location('Entering: '|| 'PER_ASSIGNMENTS_F3_PKG.validate_primary_flag.' , 5);
77 hr_assignment.check_ass_for_primary(
78 p_pd_os_id,
79 p_ass_id,
80 p_val_st_date);
81 end validate_primary_flag;
82 ----------------------------------------------------------------------------
83 procedure test_for_cancel_term(
84 p_ass_id number,
85 p_val_start_date date,
86 p_val_end_date date,
87 p_mode varchar2,
88 p_per_sys_st varchar2,
89 p_s_per_sys_st varchar2,
90 p_cancel_atd IN OUT NOCOPY date,
91 p_cancel_lspd IN OUT NOCOPY date,
92 p_reterm_atd IN OUT NOCOPY date,
93 p_reterm_lspd IN OUT NOCOPY date) is
94 --
95 -- Run the check to see whether this update operation will result
96 -- in a TERM_STATUS being removed or superceded by an earlier one.
97 -- This also checks to see whether the operation will cause a new
98 -- "leading TERM_ASSIGN" to be implicitly created (i.e. a row in the
99 -- future becomes the "leading TERM_ASSIGN").
100 --
101 begin
102 hr_utility.set_location('Entering: '|| 'PER_ASSIGNMENTS_F3_PKG.test_for_cancel_term' , 5);
103 hr_assignment.test_for_cancel_reterm(
104 p_ass_id,
105 p_val_start_date,
106 p_val_end_date,
107 p_mode,
108 p_per_sys_st,
109 p_s_per_sys_st,
110 p_cancel_atd,
111 p_cancel_lspd,
112 p_reterm_atd,
113 p_reterm_lspd);
114 --
115 end test_for_cancel_term;
116 -----------------------------------------------------------------------------
117 procedure check_future_primary(
118 p_dt_mode varchar2,
119 p_val_start_date date,
120 p_prim_flag varchar2,
121 p_eff_start_date date,
122 p_s_prim_flag varchar2,
123 p_prim_change_flag IN OUT NOCOPY varchar2,
124 p_new_prim_flag IN OUT NOCOPY varchar2,
125 p_ass_id number,
126 p_eff_end_date date,
127 p_pd_os_id number,
128 p_show_cand_prim_assgts IN OUT NOCOPY varchar2,
129 p_prim_date_from IN OUT NOCOPY date,
130 p_new_prim_ass_id IN OUT NOCOPY varchar2) is
131 l_prim_flag varchar2(1);
132 l_start_date date;
133 --
134 -- When an operation such as UPDATE_OVERRIDE,DELETE_NEXT_CHANGE,
135 -- FUTURE_CHANGE or ZAP is performed it may remove future changes to
136 -- the primary assignment flag.
137 --
138 -- In order to cater for this a check is done to see whether there will
139 -- be a change of primary flag at any point in time. N.B. This may be from
140 -- primary to non-primary or vice versa.
141 --
142 -- HR_ASSIGNMENT.CHECK_FUTURE_PRIMARY returns an indicator PRIMARY_CHANGE_FLAG
143 -- to show whether a change will be made. C_NEW_PRIMARY_FLAG is the value
144 -- the current record will have after the operation and
145 -- C_PRIMARY_DATE_FROM is the date on which changes to other assignment
146 -- records must be catered for in order to maintain the rule that there
147 -- may be one and only one primary assignment at any point in time during
148 -- the lifetime of a Period of Service.
149 --
150 -- If the PRIMARY_CHANGE_FLAG is 'N' or if the C_NEW_PRIMARY_FLAG is 'Y'
151 -- then a new primary assignment record is not required. Otherwise another
152 -- assignmnet must be chosen to become the new primary assignment with
153 -- effect from C_PRIMARY_DATE_FROM.
154 --
155 -- HR_ASSIGNMENT.GET_NEW_PRIMARY_ASSIGNMENT determines whether there is a
156 -- single candidate assignment in which case the ID is returned in the
157 -- variable C_NEW_PRIMARY_ASS_ID. Otherwise an indicator is returned that
158 -- results in a QuickPick List of Candidate Assignments being displayed.
159 --
160 -- P_SHOW_CAND_PRIM_ASSGTS is returned as Y if a list of values needs to be
161 -- shown on the client to select from a list of primary candidate
162 -- assignments.
163 --
164 begin
165 hr_utility.set_location('per_assignments_f3_pkg.check_future_primary',1);
166 p_show_cand_prim_assgts := null;
167 if p_dt_mode = 'UPDATE_OVERRIDE' then
168 l_start_date := p_val_start_date;
169 l_prim_flag := p_prim_flag;
170 else
171 l_start_date := p_eff_start_date;
172 l_prim_flag := p_s_prim_flag;
173 end if;
174 --
175 -- Note last 3 parameters return values from server-side procedure.
176 -- The last is OUT only so no need to get p_prim_date_from, we will
177 -- only write to the item.
178 --
179 hr_utility.set_location('per_assignments_f3_pkg.check_future_primary',2);
180 hr_assignment.check_future_primary(
181 p_ass_id,
182 l_start_date,
183 p_eff_end_date,
184 p_dt_mode,
185 l_prim_flag,
186 p_prim_change_flag,
187 p_new_prim_flag,
188 p_prim_date_from);
189 --
190 hr_utility.trace('Primary Effective From '||
191 to_char(p_prim_date_from,'DD-MON-YYYY'));
192 --
193 if p_prim_change_flag = 'N' or p_new_prim_flag = 'Y' then
194 return;
195 end if;
196 --
197 -- As above, last parameter is OUT only - it's actually a varchar2
198 -- returning a number value!
199 --
200 hr_utility.set_location('per_assignments_f3_pkg.check_future_primary',3);
201 hr_assignment.get_new_primary_assignment(
202 p_ass_id,
203 p_pd_os_id,
204 l_start_date,
205 p_new_prim_ass_id);
206 --
207 hr_utility.trace('New Primary is '||p_new_prim_ass_id);
208 --
209 if hr_utility.check_warning then
210 p_show_cand_prim_assgts := 'Y';
211 end if;
212 --
213 end check_future_primary;
214 -----------------------------------------------------------------------------
215 procedure pre_update_bundle2(
216 p_upd_mode varchar2,
217 p_del_mode varchar2,
218 p_sess_date date,
219 p_per_sys_st varchar2,
220 p_val_st_date date,
221 p_new_end_date date,
222 p_val_end_date date,
223 p_ass_id number,
224 p_pay_id number,
225 p_eot date,
226 p_eff_end_date date,
227 p_prim_flag varchar2,
228 p_new_prim_flag IN OUT NOCOPY varchar2,
229 p_pd_os_id number,
230 p_s_per_sys_st varchar2,
231 p_ass_number varchar2,
232 p_s_ass_number varchar2,
233 p_row_id varchar2,
234 p_s_prim_flag varchar2,
235 p_bg_id number,
236 p_eff_st_date date,
237 p_grd_id number,
238 p_sp_ceil_st_id number,
239 p_ceil_seq number,
240 p_re_entry_point IN OUT NOCOPY number,
241 p_returned_warning IN OUT NOCOPY varchar2,
242 p_prim_change_flag IN OUT NOCOPY varchar2,
243 p_prim_date_from IN OUT NOCOPY date,
244 p_new_prim_ass_id IN OUT NOCOPY varchar2,
245 p_cancel_atd IN OUT NOCOPY date,
246 p_cancel_lspd IN OUT NOCOPY date,
247 p_reterm_atd IN OUT NOCOPY date,
248 p_reterm_lspd IN OUT NOCOPY date,
249 p_copy_y_to_prim_ch IN OUT NOCOPY varchar2,
250 p_pay_basis_id number --fix for bug 4764140
251 ) is
252 l_re_entry_point number;
253 l_warning varchar2(80);
254 l_new_end_date date;
255 l_show_cand_prim_assgts varchar2(1);
256 begin
257 hr_utility.set_location('Entering: '|| 'PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 5);
258 --
259 -- NB RE_ENTRY_POINT_1 is no longer used but kept in so that
260 -- RE_ENTRY_POINT_2 will behave in the same way.
261 --
262 if p_re_entry_point = 1 then
263 goto RE_ENTRY_POINT_1;
264 elsif p_re_entry_point = 2 then
265 goto RE_ENTRY_POINT_2;
266 end if;
267 --
268 -- Validate the Payroll exists for the lifetime of the Assignment
269 -- and that no assignment actions are orphaned by a change in Payroll.
270 --
271 -- The payroll_change_validate procedure is already called above for
272 -- 'UPDATE_OVERRIDE' so we do not need to recall it here.
273 --
274 if p_upd_mode <> 'UPDATE_OVERRIDE' then
275 --
276 -- Do payroll_change_validate.
277 --
278 l_re_entry_point := 2;
279 l_new_end_date := p_new_end_date;
280 --
281 update_and_delete_bundle(
282 p_upd_mode,
283 p_val_st_date,
284 p_eff_st_date,
285 p_eff_end_date,
286 p_pd_os_id,
287 p_per_sys_st,
288 p_ass_id,
289 p_val_end_date,
290 p_upd_mode,
291 p_del_mode,
292 p_sess_date,
293 p_pay_id,
294 p_grd_id,
295 p_sp_ceil_st_id,
296 p_ceil_seq,
297 l_new_end_date,
298 l_warning,
299 l_re_entry_point,
300 'Y',
301 p_pay_basis_id );--fix for bug 4764140.
302 --
303 -- Value of l_new_end_date will not be changed by
304 -- update_and_delete_bundle because we are only executing
305 -- entry point 2 of the code, not the bit which sets the
306 -- l_new_end_date value.
307 --
308 end if;
309 --
310 <<RE_ENTRY_POINT_1>>
311 --
312 hr_utility.set_location('PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 10);
313 if p_per_sys_st = 'END' then
314 process_end_status(
315 p_ass_id,
316 p_sess_date,
317 p_eot,
318 p_eff_end_date);
319 elsif p_per_sys_st = 'TERM_ASSIGN' then
320 process_term_status(
321 p_ass_id,
322 p_sess_date);
323 end if;
324 --
325 -- If the primary flag has changed then flag a PRIMARY_CHANGE
326 -- otherwise if an UPDATE_OVERRIDE that could potentially remove a
327 -- change in primary flag is to be performed then check whether update
328 -- of other primary flags is necessary.
329 --
330 p_copy_y_to_prim_ch := null;
331 --
332 hr_utility.set_location('PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 20);
333 if p_s_prim_flag = p_prim_flag then
334 if p_upd_mode = 'UPDATE_OVERRIDE' then
335 check_future_primary(
336 p_upd_mode,
337 p_val_st_date,
338 p_prim_flag,
339 p_eff_st_date,
340 p_s_prim_flag,
341 p_prim_change_flag,
342 p_new_prim_flag,
343 p_ass_id,
344 p_eff_end_date,
345 p_pd_os_id,
346 l_show_cand_prim_assgts,
347 p_prim_date_from,
348 p_new_prim_ass_id);
349 --
350 if l_show_cand_prim_assgts = 'Y' then
351 --
352 -- Need to do a show_lov on client so
353 -- return special warning to be interpreted
354 -- on client.
355 --
356 p_returned_warning := 'SHOW_LOV';
357 p_re_entry_point := 2;
358 return;
359 end if;
360 end if;
361 else
362 if p_prim_flag = 'Y' then
363 validate_primary_flag(
364 p_val_st_date,
365 p_pd_os_id,
366 p_ass_id);
367 p_copy_y_to_prim_ch := 'Y';
368 end if;
369 end if;
370 --
371 <<RE_ENTRY_POINT_2>>
372 --
373 -- Now check the changing of term_assign's with relation to it's
374 -- impact on element entries (G255).
375 --
376 hr_utility.set_location('PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 30);
377 test_for_cancel_term(
378 p_ass_id,
379 p_val_st_date,
380 p_val_end_date,
381 p_upd_mode,
382 p_per_sys_st,
383 p_s_per_sys_st,
384 p_cancel_atd,
385 p_cancel_lspd,
386 p_reterm_atd,
387 p_reterm_lspd);
388 --
389 -- OK, now ready to update so:
390 -- If the assignment number has been changed then update all the other
391 -- assignments with the same id as the current one to have the same new
392 -- assignment number.
393 --
394 if p_s_ass_number <> p_ass_number then
395 update per_all_assignments_f a
396 set a.assignment_number = P_ASS_NUMBER
397 where a.business_group_id + 0 = P_BG_ID
398 and a.rowid <> P_ROW_ID
399 and a.assignment_id = P_ASS_ID;
400 end if;
401 --
402 p_re_entry_point := 0;
403 --
404 hr_utility.set_location('Leaving: '|| 'PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 40);
405 end pre_update_bundle2;
406 -----------------------------------------------------------------------------
407 --
408 -- UPDATE_AND_DELETE_BUNDLE: procedure to bundle procedure calls which follow
409 -- each other at pre_update and pre_delete time (on PERWSEMA).
410 --
411 -- p_re_entry_point is used to re-enter code after warnings and prevent
412 -- code being re-run. A p_re_entry_point of 0 implies successful completion.
413 --
414 -- A null p_re_entry_point should be passed to run whole procedure.
415 -- p_only_one_entry_point is 'Y' if only the given entry point is to be
416 -- run through. eg If p_re_entry_point=2 and p_only_one_entry_point='Y' then
417 -- only the code relating to PAYROLL_CHANGE_VALIDATE will be run and then
418 -- the procedure will finish.
419 -- Normally the value of p_only_one_entry_point will be 'N' where the code
420 -- will run through until the end of the proc unless it hits a warning or
421 -- error.
422 --
423 procedure update_and_delete_bundle(
424 p_dt_mode varchar2,
425 p_val_st_date date,
426 p_eff_st_date date,
427 p_eff_end_date date,
428 p_pd_os_id number,
429 p_per_sys_st varchar2,
430 p_ass_id number,
431 p_val_end_date date,
432 p_dt_upd_mode varchar2,
433 p_dt_del_mode varchar2,
434 p_sess_date date,
435 p_pay_id number,
436 p_grd_id number,
437 p_sp_ceil_st_id number,
438 p_ceil_seq number,
439 p_new_end_date IN OUT NOCOPY date,
440 p_returned_warning IN OUT NOCOPY varchar2,
441 p_re_entry_point IN OUT NOCOPY number,
442 p_only_one_entry_point varchar2,
443 p_pay_basis_id number --fix for bug 4764140
444 ) is
445 --
446 l_null number;
447 l_start_date date;
448 l_dt_mode varchar2(30);
449 l_val_start_date date;
450 l_val_end_date date;
451 l_element_entry_id1 number;
452 l_element_entry_id number;
453 l_entries_changed_warning varchar2(1) := 'N';
454 l_pay_basis_id number; --fix for bug 4764140
455 --
456 l_Del_proposal_Id Per_Pay_proposals.Pay_proposal_Id%TYPE;
457 l_Del_Proposal_Ovn per_pay_Proposals.Object_version_Number%TYPE;
458 l_del_proposal_change_dt per_pay_proposals.change_date%type;
459 l_del_warn boolean;
460 l_del_bg_id per_pay_Proposals.business_group_id%type;
461 l_inv_next_sal_date_warning boolean;
462 l_proposed_salary_warning boolean;
463 l_approved_warning boolean;
464 l_payroll_warning boolean;
465
466 --Bug# 5758747
467 l_leg_code varchar2(2);
468 --
469 -- Bug# 1321860
470 -- Description : Removed the cursor, which is not required
471 --
472
473
474 cursor sp_point is
475 select 1
476 from sys.dual where exists (
477 select null
478 from per_spinal_point_steps_f s,
479 per_spinal_point_placements_f p,
480 per_grade_spines_f g
481 where s.step_id = p.step_id
482 and g.grade_spine_id = s.grade_spine_id
483 and s.sequence > P_CEIL_SEQ
484 and p.assignment_id = P_ASS_ID
485 and g.grade_id = P_GRD_ID
486 and (
487
488 (p_eff_st_date BETWEEN p.effective_start_date
489 AND p.effective_end_date)
490 OR ( ( p_dt_mode='UPDATE_OVERRIDE' or p_dt_mode= 'UPDATE')
491 and p_eff_st_date <= p.effective_start_date )
492 )
493
494 ); -- modified the cursor for bug fix 7205433
495
496
497 -- fix for bug 4531033 starts here
498 cursor csr_get_salary is
499 select element_entry_id
500 from pay_element_entries_f
501 where assignment_id = p_ass_id
502 and creator_type = 'SP'
503 and l_val_start_date between
504 effective_start_date and effective_end_date;
505
506 cursor csr_chk_rec_exists is
507 select element_entry_id
508 from pay_element_entries_f
509 where assignment_id = p_ass_id
510 and creator_type = 'SP'
511 and (l_val_start_date - 1) between
512 effective_start_date and effective_end_date;
513 -- fix for bug 4531033 ends here
514 --
515 -- fix for bug 4764140 starts here.
516 cursor csr_pay_basis_id is
517 select pay_basis_id
518 from per_all_assignments
519 where assignment_id = p_ass_id
520 and effective_start_date <= p_eff_st_date
521 and effective_end_date >= p_eff_st_date;
522 --
523 --
524 Cursor Proposal_Dtls is
525 Select Pay_Proposal_Id, Object_Version_Number,business_group_id,change_date
526 From Per_Pay_Proposals
527 where assignment_id = p_ass_id
528 and change_date <= p_val_st_date
529 and nvl(date_to,to_date('31/12/4712','dd/mm/yyyy')) >= p_val_st_date;
530
531 -- fix for bug 4764140 ends here.
532 -- fix for the bug 4612843
533
534 l_step_enddate date;
535 l_asg_enddate date;
536
537 cursor get_step_enddate is
538 select s.effective_end_date
539 from per_spinal_point_steps_f s,
540 per_grade_spines_F g
541 where s.step_id= p_sp_ceil_st_id
542 and g.grade_id= P_GRD_ID
543 and g.grade_spine_id=s.grade_spine_id ;
544
545 cursor get_asg_enddate is
546 select max(effective_end_date ) from per_all_assignments_f
547 where assignment_id= P_ASS_ID
548 -- and grade_id=P_GRD_ID
549 and assignment_type='E';
550
551 -- end of bug 4612843
552
553 -- Bug# 5758747
554 CURSOR get_leg_code IS
555 SELECT legislation_code
556 FROM per_business_groups
557 WHERE business_group_id = (SELECT business_group_id
558 FROM per_all_assignments_f
559 WHERE assignment_id = p_ass_id
560 and effective_start_date <= p_eff_st_date
561 and effective_end_date >= p_eff_st_date);
562
563 begin
564 p_returned_warning := null;
565 hr_utility.clear_warning;
566 --
567 hr_utility.set_location('per_assignments_f3_pkg.update_and_delete_bundle',1);
568 hr_utility.trace('RE_ENTRY_POINT is '||to_char(p_re_entry_point));
569
570 --Bug# 5758747
571 OPEN get_leg_code;
572 FETCH get_leg_code INTO l_leg_code;
573 CLOSE get_leg_code;
574
575 --
576 if p_re_entry_point = 1 then
577 goto RE_ENTRY_POINT_1;
578 elsif p_re_entry_point = 2 then
579 goto RE_ENTRY_POINT_2;
580 elsif p_re_entry_point = 3 then
581 goto RE_ENTRY_POINT_3;
582 end if;
583 --
584 -- CHECK_TERM_BY_POS:
585 --
586 -- Checks the validity of the end date according to future
587 -- terminations. Returns a new end date if appropriate.
588 -- If p_only_one_entry_point = 'Y' and p_re_entry_point is not between
589 -- 1 and 3 (or 0) then we will perform CHECK_TERM_BY_POS only. We set
590 -- p_re_entry_point to 999 as an arbritary value to do this.
591 --
592 if p_dt_mode = 'UPDATE_OVERRIDE' then
593 l_start_date := p_val_st_date;
594 else
595 l_start_date := p_eff_st_date;
596 end if;
597 --
598 p_new_end_date := null;
599 --
600 hr_assignment.check_term(
601 p_pd_os_id,
602 p_ass_id,
603 l_start_date,
604 p_eff_end_date,
605 p_per_sys_st,
606 p_dt_mode,
607 p_new_end_date);
608 --
609 --Bug# 5758747
610 IF l_leg_code = 'US' THEN
611 hr_assignment.check_for_cobra(
612 p_ass_id,
613 p_val_st_date,
614 p_val_end_date);
615 if hr_utility.check_warning then
616 p_returned_warning := 'HR_ASS_TERM_COBRA_EXISTS';
617 p_re_entry_point := 1;
618 return;
619 end if;
620 END IF;
621 --
622 if p_only_one_entry_point = 'Y' then
623 return;
624 end if;
625 --
626 <<RE_ENTRY_POINT_1>>
627 hr_utility.trace('RE_ENTRY_POINT1');
628 --
629 -- WARN_TERM_BY_POS:
630 --
631 -- Warn user if the operation will remove an assignment with
632 -- TERM_ASSIGN status.
633 --
634 if p_dt_upd_mode = 'UPDATE_OVERRIDE' then
635 l_start_date := p_val_st_date;
636 else
637 l_start_date := p_eff_st_date;
638 end if;
639 --
640 if p_dt_upd_mode is null then
641 l_dt_mode := p_dt_del_mode;
642 else
643 if p_per_sys_st = 'END' then
644 l_dt_mode := 'DELETE';
645 else
646 l_dt_mode := p_dt_del_mode;
647 end if;
648 end if;
649 --
650 hr_assignment.warn_del_term(
651 p_ass_id,
652 l_dt_mode,
653 l_start_date,
654 p_eff_end_date);
655 --
656 if hr_utility.check_warning then
657 p_returned_warning := 'HR_EMP_ASS_TERM_FOUND';
658 p_re_entry_point := 2;
659 return;
660 end if;
661 --
662 if p_only_one_entry_point = 'Y' then
663 return;
664 end if;
665 --
666 <<RE_ENTRY_POINT_2>>
667 hr_utility.trace('RE_ENTRY_POINT2');
668
669 --
670 -- Bug# 1321860
671 -- Description : Removed the future payroll action check condition.
672 --
673 --
674 -- PAYROLL_CHANGE_VALIDATE:
675 --
676 if p_dt_upd_mode is null then
677 if p_dt_del_mode is null then
678 goto CHECK_TERM_COBRA;
679 else
680 l_dt_mode := p_dt_del_mode;
681 end if;
682 else
683 if p_per_sys_st = 'END' then
684 l_dt_mode := 'DELETE';
685 else
686 l_dt_mode := p_dt_upd_mode;
687 end if;
688 end if;
689 --
690 if l_dt_mode = 'DELETE' then
691 l_val_start_date := p_sess_date;
692 else
693 l_val_start_date := p_val_st_date;
694 end if;
695 --
696 if p_new_end_date is null then
697 l_val_end_date := p_val_end_date;
698 else
699 l_val_end_date := p_new_end_date;
700 end if;
701 --
702 --fix for bug 4764140 starts here.
703 open csr_pay_basis_id;
704 fetch csr_pay_basis_id into l_pay_basis_id;
705 close csr_pay_basis_id;
706 if (nvl(p_pay_basis_id,hr_api.g_number) <> nvl(l_pay_basis_id,hr_api.g_number)) then
707 --fix for bug 4764140 ends here.
708 -- fix for bug 4531033 starts here
709 Open Proposal_Dtls;
710 Fetch proposal_Dtls into l_Del_Proposal_Id, l_Del_Proposal_Ovn, l_del_bg_id,l_del_proposal_change_dt;
711 Close Proposal_Dtls;
712
713 --
714 -- End date the proposal. This should end date the element entry as well.
715 --
716 If l_del_proposal_change_dt < l_val_start_date then
717 hr_utility.set_location('End date proposal',25);
718 hr_maintain_proposal_api.update_salary_proposal(
719 p_validate => false,
720 p_pay_proposal_id => l_Del_proposal_Id,
721 p_date_to => l_val_start_date - 1,
722 p_object_version_number => l_Del_Proposal_Ovn,
723 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
724 p_proposed_salary_warning => l_proposed_salary_warning,
725 p_approved_warning => l_approved_warning,
726 p_payroll_warning => l_payroll_warning);
727
728 -- Zap the proposal and the element entry.
729
730 Elsif l_del_proposal_change_dt = l_val_start_date then
731 hr_utility.set_location('Zap proposal',25);
732
733 Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
734 (P_PAY_PROPOSAL_ID => l_Del_proposal_Id
735 ,P_BUSINESS_GROUP_ID => l_del_bg_id
736 ,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
737 ,P_SALARY_WARNING => l_Del_Warn);
738 Else
739 hr_utility.set_location('Should never come here',25);
740 null;
741 End if;
742
743 /***
744 open csr_get_salary;
745 fetch csr_get_salary into l_element_entry_id;
746 if csr_get_salary%found then
747 close csr_get_salary;
748
749
750
751 open csr_chk_rec_exists;
752 fetch csr_chk_rec_exists into l_element_entry_id1;
753
754 if csr_chk_rec_exists%found then
755 close csr_chk_rec_exists;
756
757 --
758 hr_entry_api.delete_element_entry
759 ('DELETE'
760 ,l_val_start_date - 1
761 ,l_element_entry_id1);
762 --
763
764 else
765
766 close csr_chk_rec_exists;
767 hr_entry_api.delete_element_entry
768 ('ZAP'
769 ,l_val_start_date
770 ,l_element_entry_id);
771
772 end if;
773
774 l_entries_changed_warning := 'S';
775 else
776 close csr_get_salary;
777 end if;
778
779 **/
780 -- fix for bug 4531033 ends here
781 end if;--fix for bug 4764140.
782
783 hrentmnt.check_payroll_changes_asg(
784 p_ass_id,
785 p_pay_id,
786 l_dt_mode,
787 l_val_start_date,
788 l_val_end_date);
789 --
790 -- added the IF clause for bug 2537091
791 if p_per_sys_st <> 'END' then
792 hrentmnt.check_opmu(
793 p_ass_id,
794 p_pay_id,
795 l_dt_mode,
796 l_val_start_date,
797 l_val_end_date);
798 end if;
799 --
800 if p_only_one_entry_point = 'Y' then
801 return;
802 end if;
803 --
804 <<CHECK_TERM_COBRA>>
805 hr_utility.trace('CHECK_TERM_COBRA');
806 --
807 -- CHECK_TERM_COBRA:
808 --
809 --Bug# 5758747
810 IF l_leg_code = 'US' THEN
811 hr_assignment.check_for_cobra(
812 p_ass_id,
813 p_val_st_date,
814 p_val_end_date);
815 --
816 if hr_utility.check_warning then
817 p_returned_warning := 'HR_ASS_TERM_COBRA_EXISTS';
818 p_re_entry_point := 3;
819 return;
820 end if;
821 END IF;
822 --
823 <<RE_ENTRY_POINT_3>>
824 hr_utility.trace('RE_ENTRY_POINT3');
825 --
826 -- CHECK_SPP_AND_CEIL:
827 --
828 -- Check to see if there are any placements for the assignment that
829 -- have a spinal point sequence greater than the ceiling spinal
830 -- point sequence. Note the check is limited to those placements
831 -- linked to a grade spine record whose grade id is the same as
832 -- ASS.GRADE_ID.
833 --
834 if p_grd_id is not null and p_sp_ceil_st_id is not null then
835 hr_utility.set_location ('RE_ENTRY_POINT3 ' ||p_dt_mode,10 );
836 hr_utility.set_location ('p_eff_st_date ' ||p_eff_st_date,10 );
837
838 open sp_point;
839 fetch sp_point into l_null;
840 if sp_point%found then
841 fnd_message.set_name('PAY',
842 'PER_7935_CEIL_PLACE_HIGH_EXIST');
843 close sp_point;
844 fnd_message.raise_error;
845 end if;
846 close sp_point;
847 end if;
848 --fix for bug 4612843
849 open get_step_enddate;
850 fetch get_step_enddate into l_step_enddate;
851 if (l_step_enddate < hr_api.g_eot) then
852 close get_step_enddate;
853 open get_asg_enddate;
854 fetch get_asg_enddate into l_asg_enddate;
855 if ( l_asg_enddate > l_step_enddate ) then
856 close get_asg_enddate;
857 fnd_message.set_name('PAY','PAY_7589_SYS_STEP_DT_OUTDATE');
858 fnd_message.raise_error;
859 else
860 close get_asg_enddate;
861 end if ;
862 else
863 close get_step_enddate;
864 end if;
865
866 -- end of fix for the bug 4612843
867 --
868 p_re_entry_point := 0;
869 --
870 hr_utility.set_location('per_assignments_f3_pkg.update_and_delete_bundle',2);
871 --
872 end update_and_delete_bundle;
873 -----------------------------------------------------------------------------
874 END PER_ASSIGNMENTS_F3_PKG;