1 PACKAGE BODY PER_ASSIGNMENTS_F3_PKG AS
2 /* $Header: peasg01t.pkb 120.50.12020000.8 2013/02/06 07:35:03 srannama 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 p_ins_new_sal_flag varchar2 -- fix for bug 9109727
252 ) is
253 l_re_entry_point number;
254 l_warning varchar2(80);
255 l_new_end_date date;
256 l_show_cand_prim_assgts varchar2(1);
257 begin
258 hr_utility.set_location('Entering: '|| 'PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 5);
259 --
260 -- NB RE_ENTRY_POINT_1 is no longer used but kept in so that
261 -- RE_ENTRY_POINT_2 will behave in the same way.
262 --
263 if p_re_entry_point = 1 then
264 goto RE_ENTRY_POINT_1;
265 elsif p_re_entry_point = 2 then
266 goto RE_ENTRY_POINT_2;
267 end if;
268 --
269 -- Validate the Payroll exists for the lifetime of the Assignment
270 -- and that no assignment actions are orphaned by a change in Payroll.
271 --
272 -- The payroll_change_validate procedure is already called above for
273 -- 'UPDATE_OVERRIDE' so we do not need to recall it here.
274 --
275 if p_upd_mode <> 'UPDATE_OVERRIDE' then
276 --
277 -- Do payroll_change_validate.
278 --
279 l_re_entry_point := 2;
280 l_new_end_date := p_new_end_date;
281 --
282 update_and_delete_bundle(
283 p_upd_mode,
284 p_val_st_date,
285 p_eff_st_date,
286 p_eff_end_date,
287 p_pd_os_id,
288 p_per_sys_st,
289 p_ass_id,
290 p_val_end_date,
291 p_upd_mode,
292 p_del_mode,
293 p_sess_date,
294 p_pay_id,
295 p_grd_id,
296 p_sp_ceil_st_id,
297 p_ceil_seq,
298 l_new_end_date,
299 l_warning,
300 l_re_entry_point,
301 'Y',
302 p_pay_basis_id, --fix for bug 4764140.
303 p_ins_new_sal_flag); -- fix for bug 9109727
304 --
305 -- Value of l_new_end_date will not be changed by
306 -- update_and_delete_bundle because we are only executing
307 -- entry point 2 of the code, not the bit which sets the
308 -- l_new_end_date value.
309 --
310 end if;
311 --
312 <<RE_ENTRY_POINT_1>>
313 --
314 hr_utility.set_location('PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 10);
315 if p_per_sys_st = 'END' then
316 process_end_status(
317 p_ass_id,
318 p_sess_date,
319 p_eot,
320 p_eff_end_date);
321 elsif p_per_sys_st = 'TERM_ASSIGN' then
322 process_term_status(
323 p_ass_id,
324 p_sess_date);
325 end if;
326 --
327 -- If the primary flag has changed then flag a PRIMARY_CHANGE
328 -- otherwise if an UPDATE_OVERRIDE that could potentially remove a
329 -- change in primary flag is to be performed then check whether update
330 -- of other primary flags is necessary.
331 --
332 p_copy_y_to_prim_ch := null;
333 --
334 hr_utility.set_location('PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 20);
335 if p_s_prim_flag = p_prim_flag then
336 if p_upd_mode = 'UPDATE_OVERRIDE' then
337 check_future_primary(
338 p_upd_mode,
339 p_val_st_date,
340 p_prim_flag,
341 p_eff_st_date,
342 p_s_prim_flag,
343 p_prim_change_flag,
344 p_new_prim_flag,
345 p_ass_id,
346 p_eff_end_date,
347 p_pd_os_id,
348 l_show_cand_prim_assgts,
349 p_prim_date_from,
350 p_new_prim_ass_id);
351 --
352 if l_show_cand_prim_assgts = 'Y' then
353 --
354 -- Need to do a show_lov on client so
355 -- return special warning to be interpreted
356 -- on client.
357 --
358 p_returned_warning := 'SHOW_LOV';
359 p_re_entry_point := 2;
360 return;
361 end if;
362 end if;
363 else
364 if p_prim_flag = 'Y' then
365 validate_primary_flag(
366 p_val_st_date,
367 p_pd_os_id,
368 p_ass_id);
369 p_copy_y_to_prim_ch := 'Y';
370 end if;
371 end if;
372 --
373 <<RE_ENTRY_POINT_2>>
374 --
375 -- Now check the changing of term_assign's with relation to it's
376 -- impact on element entries (G255).
377 --
378 hr_utility.set_location('PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 30);
379 test_for_cancel_term(
380 p_ass_id,
381 p_val_st_date,
382 p_val_end_date,
383 p_upd_mode,
384 p_per_sys_st,
385 p_s_per_sys_st,
386 p_cancel_atd,
387 p_cancel_lspd,
388 p_reterm_atd,
389 p_reterm_lspd);
390 --
391 -- OK, now ready to update so:
392 -- If the assignment number has been changed then update all the other
393 -- assignments with the same id as the current one to have the same new
394 -- assignment number.
395 --
396 if p_s_ass_number <> p_ass_number then
397 update per_all_assignments_f a
398 set a.assignment_number = P_ASS_NUMBER
399 where a.business_group_id + 0 = P_BG_ID
400 and a.rowid <> P_ROW_ID
401 and a.assignment_id = P_ASS_ID;
402 end if;
403 --
404 p_re_entry_point := 0;
405 --
406 hr_utility.set_location('Leaving: '|| 'PER_ASSIGNMENTS_F3_PKG.pre_update_bundle2' , 40);
407 end pre_update_bundle2;
408 -----------------------------------------------------------------------------
409 --
410 -- UPDATE_AND_DELETE_BUNDLE: procedure to bundle procedure calls which follow
411 -- each other at pre_update and pre_delete time (on PERWSEMA).
412 --
413 -- p_re_entry_point is used to re-enter code after warnings and prevent
414 -- code being re-run. A p_re_entry_point of 0 implies successful completion.
415 --
416 -- A null p_re_entry_point should be passed to run whole procedure.
417 -- p_only_one_entry_point is 'Y' if only the given entry point is to be
418 -- run through. eg If p_re_entry_point=2 and p_only_one_entry_point='Y' then
419 -- only the code relating to PAYROLL_CHANGE_VALIDATE will be run and then
420 -- the procedure will finish.
421 -- Normally the value of p_only_one_entry_point will be 'N' where the code
422 -- will run through until the end of the proc unless it hits a warning or
423 -- error.
424 --
425 procedure update_and_delete_bundle(
426 p_dt_mode varchar2,
427 p_val_st_date date,
428 p_eff_st_date date,
429 p_eff_end_date date,
430 p_pd_os_id number,
431 p_per_sys_st varchar2,
432 p_ass_id number,
433 p_val_end_date date,
434 p_dt_upd_mode varchar2,
435 p_dt_del_mode varchar2,
436 p_sess_date date,
437 p_pay_id number,
438 p_grd_id number,
439 p_sp_ceil_st_id number,
440 p_ceil_seq number,
441 p_new_end_date IN OUT NOCOPY date,
442 p_returned_warning IN OUT NOCOPY varchar2,
443 p_re_entry_point IN OUT NOCOPY number,
444 p_only_one_entry_point varchar2,
445 p_pay_basis_id number, --fix for bug 4764140
446 p_ins_new_sal_flag VARCHAR2 DEFAULT 'N' -- fix for 9109727
447 ) is
448 --
449 l_null number;
450 l_start_date date;
451 l_dt_mode varchar2(30);
452 l_val_start_date date;
453 l_val_end_date date;
454 l_element_entry_id1 number;
455 l_element_entry_id number;
456 l_entries_changed_warning varchar2(1) := 'N';
457 l_pay_basis_id number; --fix for bug 4764140
458 --
459 l_Del_proposal_Id Per_Pay_proposals.Pay_proposal_Id%TYPE;
460 l_Del_Proposal_Ovn per_pay_Proposals.Object_version_Number%TYPE;
461 l_del_proposal_change_dt per_pay_proposals.change_date%type;
462 l_del_warn boolean;
463 l_del_bg_id per_pay_Proposals.business_group_id%type;
464 l_inv_next_sal_date_warning boolean;
465 l_proposed_salary_warning boolean;
466 l_approved_warning boolean;
467 l_payroll_warning boolean;
468
469 --Bug# 5758747
470 l_leg_code varchar2(2);
471 --
472 -- Bug# 1321860
473 -- Description : Removed the cursor, which is not required
474 --
475
476
477 cursor sp_point is
478 select 1
479 from sys.dual where exists (
480 select null
481 from per_spinal_point_steps_f s,
482 per_spinal_point_placements_f p,
483 per_grade_spines_f g
484 where s.step_id = p.step_id
485 and g.grade_spine_id = s.grade_spine_id
486 and s.sequence > P_CEIL_SEQ
487 and p.assignment_id = P_ASS_ID
488 and g.grade_id = P_GRD_ID
489 and (
490
491 (p_eff_st_date BETWEEN p.effective_start_date
492 AND p.effective_end_date)
493 OR ( ( p_dt_mode='UPDATE_OVERRIDE' or p_dt_mode= 'UPDATE')
494 and p_eff_st_date <= p.effective_start_date )
495 )
496
497 ); -- modified the cursor for bug fix 7205433
498
499
500 -- fix for bug 4531033 starts here
501 cursor csr_get_salary is
502 select element_entry_id
503 from pay_element_entries_f
504 where assignment_id = p_ass_id
505 and creator_type = 'SP'
506 and l_val_start_date between
507 effective_start_date and effective_end_date;
508
509 cursor csr_chk_rec_exists is
510 select element_entry_id
511 from pay_element_entries_f
512 where assignment_id = p_ass_id
513 and creator_type = 'SP'
514 and (l_val_start_date - 1) between
515 effective_start_date and effective_end_date;
516 -- fix for bug 4531033 ends here
517 --
518 -- fix for bug 4764140 starts here.
519 cursor csr_pay_basis_id is
520 select pay_basis_id
521 from per_all_assignments
522 where assignment_id = p_ass_id
523 and effective_start_date <= p_eff_st_date
524 and effective_end_date >= p_eff_st_date;
525 --
526 --
527 Cursor Proposal_Dtls is
528 Select Pay_Proposal_Id, Object_Version_Number,business_group_id,change_date
529 From Per_Pay_Proposals
530 where assignment_id = p_ass_id
531 and change_date <= p_val_st_date
532 and nvl(date_to,to_date('31/12/4712','dd/mm/yyyy')) >= p_val_st_date;
533
534 -- fix for bug 4764140 ends here.
535 -- fix for the bug 4612843
536
537 l_step_enddate date;
538 l_asg_enddate date;
539
540 cursor get_step_enddate is
541 select s.effective_end_date
542 from per_spinal_point_steps_f s,
543 per_grade_spines_F g
544 where s.step_id= p_sp_ceil_st_id
545 and g.grade_id= P_GRD_ID
546 and g.grade_spine_id=s.grade_spine_id ;
547
548 cursor get_asg_enddate is
549 select max(effective_end_date ) from per_all_assignments_f
550 where assignment_id= P_ASS_ID
551 -- and grade_id=P_GRD_ID
552 and assignment_type='E';
553
554 -- end of bug 4612843
555
556 -- Bug# 5758747
557 CURSOR get_leg_code IS
558 SELECT legislation_code
559 FROM per_business_groups
560 WHERE business_group_id = (SELECT business_group_id
561 FROM per_all_assignments_f
562 WHERE assignment_id = p_ass_id
563 and effective_start_date <= p_eff_st_date
564 and effective_end_date >= p_eff_st_date);
565
566 /* Bug 9109727 */
567
568 CURSOR Element_Info(P_assignmnet_id number,P_pay_basis_id number, P_Effective_Date in DAte) IS
569 Select ele.element_entry_id
570 from per_pay_bases bas,
571 pay_element_entries_f ele,
572 pay_element_entry_values_f entval
573 where bas.pay_basis_id = P_pay_basis_id
574 and entval.input_value_id = bas.input_value_id
575 and p_effective_date
576 between entval.effective_start_date
577 and entval.effective_end_date
578 and ele.assignment_id = P_assignmnet_id
579 and p_effective_date between ele.effective_start_date
580 and ele.effective_end_date
581 and ele.element_entry_id = entval.element_entry_id;
582
583
584 l_ovn number;
585 l_Pay_Proposal_Id number;
586 l_proposed_salary number;
587 l_proposal_reason varchar2(100) := NULL; -- Added for bug 10145976
588
589 /* Bug 9109727 */
590
591 begin
592 p_returned_warning := null;
593 hr_utility.clear_warning;
594 --
595 hr_utility.set_location('per_assignments_f3_pkg.update_and_delete_bundle',1);
596 hr_utility.trace('RE_ENTRY_POINT is '||to_char(p_re_entry_point));
597
598 --Bug# 5758747
599 OPEN get_leg_code;
600 FETCH get_leg_code INTO l_leg_code;
601 CLOSE get_leg_code;
602
603 --
604 if p_re_entry_point = 1 then
605 goto RE_ENTRY_POINT_1;
606 elsif p_re_entry_point = 2 then
607 goto RE_ENTRY_POINT_2;
608 elsif p_re_entry_point = 3 then
609 goto RE_ENTRY_POINT_3;
610 end if;
611 --
612 -- CHECK_TERM_BY_POS:
613 --
614 -- Checks the validity of the end date according to future
615 -- terminations. Returns a new end date if appropriate.
616 -- If p_only_one_entry_point = 'Y' and p_re_entry_point is not between
617 -- 1 and 3 (or 0) then we will perform CHECK_TERM_BY_POS only. We set
618 -- p_re_entry_point to 999 as an arbritary value to do this.
619 --
620 if p_dt_mode = 'UPDATE_OVERRIDE' then
621 l_start_date := p_val_st_date;
622 else
623 l_start_date := p_eff_st_date;
624 end if;
625 --
626 p_new_end_date := null;
627 --
628 hr_assignment.check_term(
629 p_pd_os_id,
630 p_ass_id,
631 l_start_date,
632 p_eff_end_date,
633 p_per_sys_st,
634 p_dt_mode,
635 p_new_end_date);
636 --
637 --Bug# 5758747
638 IF l_leg_code = 'US' THEN
639 hr_assignment.check_for_cobra(
640 p_ass_id,
641 p_val_st_date,
642 p_val_end_date);
643 if hr_utility.check_warning then
644 p_returned_warning := 'HR_ASS_TERM_COBRA_EXISTS';
645 p_re_entry_point := 1;
646 return;
647 end if;
648 END IF;
649 --
650 if p_only_one_entry_point = 'Y' then
651 return;
652 end if;
653 --
654 <<RE_ENTRY_POINT_1>>
655 hr_utility.trace('RE_ENTRY_POINT1');
656 --
657 -- WARN_TERM_BY_POS:
658 --
659 -- Warn user if the operation will remove an assignment with
660 -- TERM_ASSIGN status.
661 --
662 if p_dt_upd_mode = 'UPDATE_OVERRIDE' then
663 l_start_date := p_val_st_date;
664 else
665 l_start_date := p_eff_st_date;
666 end if;
667 --
668 if p_dt_upd_mode is null then
669 l_dt_mode := p_dt_del_mode;
670 else
671 if p_per_sys_st = 'END' then
672 l_dt_mode := 'DELETE';
673 else
674 l_dt_mode := p_dt_del_mode;
675 end if;
676 end if;
677 --
678 hr_assignment.warn_del_term(
679 p_ass_id,
680 l_dt_mode,
681 l_start_date,
682 p_eff_end_date);
683 --
684 if hr_utility.check_warning then
685 p_returned_warning := 'HR_EMP_ASS_TERM_FOUND';
686 p_re_entry_point := 2;
687 return;
688 end if;
689 --
690 if p_only_one_entry_point = 'Y' then
691 return;
692 end if;
693 --
694 <<RE_ENTRY_POINT_2>>
695 hr_utility.trace('RE_ENTRY_POINT2');
696
697 --
698 -- Bug# 1321860
699 -- Description : Removed the future payroll action check condition.
700 --
701 --
702 -- PAYROLL_CHANGE_VALIDATE:
703 --
704 if p_dt_upd_mode is null then
705 if p_dt_del_mode is null then
706 goto CHECK_TERM_COBRA;
707 else
708 l_dt_mode := p_dt_del_mode;
709 end if;
710 else
711 if p_per_sys_st = 'END' then
712 l_dt_mode := 'DELETE';
713 else
714 l_dt_mode := p_dt_upd_mode;
715 end if;
716 end if;
717 --
718 if l_dt_mode = 'DELETE' then
719 l_val_start_date := p_sess_date;
720 else
721 l_val_start_date := p_val_st_date;
722 end if;
723 --
724 if p_new_end_date is null then
725 l_val_end_date := p_val_end_date;
726 else
727 l_val_end_date := p_new_end_date;
728 end if;
729 --
730 --fix for bug 4764140 starts here.
731 open csr_pay_basis_id;
732 fetch csr_pay_basis_id into l_pay_basis_id;
733 close csr_pay_basis_id;
734 if (nvl(p_pay_basis_id,hr_api.g_number) <> nvl(l_pay_basis_id,hr_api.g_number)) then
735 --fix for bug 4764140 ends here.
736 -- fix for bug 4531033 starts here
737 Open Proposal_Dtls;
738 Fetch proposal_Dtls into l_Del_Proposal_Id, l_Del_Proposal_Ovn, l_del_bg_id,l_del_proposal_change_dt;
739 Close Proposal_Dtls;
740
741 --
742 -- End date the proposal. This should end date the element entry as well.
743 --
744 If l_del_proposal_change_dt < l_val_start_date then
745 hr_utility.set_location('End date proposal',25);
746 hr_maintain_proposal_api.update_salary_proposal(
747 p_validate => false,
748 p_pay_proposal_id => l_Del_proposal_Id,
749 p_date_to => l_val_start_date - 1,
750 p_object_version_number => l_Del_Proposal_Ovn,
751 p_inv_next_sal_date_warning => l_inv_next_sal_date_warning,
752 p_proposed_salary_warning => l_proposed_salary_warning,
753 p_approved_warning => l_approved_warning,
754 p_payroll_warning => l_payroll_warning);
755
756 -- Bug 9109727 changes start.
757 /******** Commented out for bug fix 9128059. This code has been moved to ASSGT.POST-UPDATE trigger, form PERWSEMA.fmb **********
758 -- If asked by the user, create a new sal proposal with the new sal basis
759 IF nvl(p_ins_new_sal_flag, 'N') = 'Y' then
760
761 Open Element_Info(p_ass_id, p_pay_basis_id, l_val_start_date);
762 Fetch Element_Info Into L_Element_Entry_Id;
763 Close Element_Info;
764
765 -- Start changes for bug 10145976
766 if hr_api.not_exists_in_hr_lookups
767 (p_effective_date => l_val_start_date
768 ,p_lookup_type => 'PROPOSAL_REASON'
769 ,p_lookup_code => 'SALBASISCHG'
770 )
771 then
772 hr_utility.set_location('proposal_reason set to null', 26);
773 l_proposal_reason :=null;
774 else
775 hr_utility.set_location('proposal_reason set to SALBASISCHG', 27);
776 l_proposal_reason := 'SALBASISCHG';
777 end if;
778 -- End changes for bug 10145976
779
780 Hr_Maintain_Proposal_Api.INSERT_SALARY_PROPOSAL
781 (P_PAY_PROPOSAL_ID => l_Pay_Proposal_Id
782 ,P_ASSIGNMENT_ID => p_ass_id
783 ,P_BUSINESS_GROUP_ID => l_del_bg_id
784 ,P_CHANGE_DATE => l_val_start_date
785 ,P_PROPOSED_SALARY_N => per_saladmin_utility.get_proposed_salary(p_ass_id,l_val_start_date-1)
786 ,P_OBJECT_VERSION_NUMBER => l_ovn
787 ,P_ELEMENT_ENTRY_ID => L_Element_Entry_Id
788 ,P_MULTIPLE_COMPONENTS => 'N'
789 ,P_APPROVED => 'Y'
790 ,P_PROPOSAL_REASON => l_proposal_reason -- Modified for bug 10145976
791 ,P_INV_NEXT_SAL_DATE_WARNING => L_INV_NEXT_SAL_DATE_WARNING
792 ,P_PROPOSED_SALARY_WARNING => L_PROPOSED_SALARY_WARNING
793 ,P_APPROVED_WARNING => L_APPROVED_WARNING
794 ,P_PAYROLL_WARNING => L_PAYROLL_WARNING);
795 END IF ;
796 -- Bug 9109727 changes end
797 ********** Commented out for bug fix 9128059. This code has been moved to ASSGT.POST-UPDATE trigger, form PERWSEMA.fmb ***********/
798
799 Elsif l_del_proposal_change_dt = l_val_start_date THEN
800 -- Zap the proposal and the element entry.
801 hr_utility.set_location('Zap proposal',25);
802
803 Hr_Maintain_Proposal_Api.DELETE_SALARY_PROPOSAL
804 (P_PAY_PROPOSAL_ID => l_Del_proposal_Id
805 ,P_BUSINESS_GROUP_ID => l_del_bg_id
806 ,P_OBJECT_VERSION_NUMBER => l_Del_Proposal_Ovn
807 ,P_SALARY_WARNING => l_Del_Warn);
808
809 -- fix for the bug 9446176 starts here
810
811 IF(l_dt_mode = 'CORRECTION')
812 then
813
814 -- Start changes for bug 10161268
815 if hr_api.not_exists_in_hr_lookups
816 (p_effective_date => l_val_start_date
817 ,p_lookup_type => 'PROPOSAL_REASON'
818 ,p_lookup_code => 'SALBASISCHG'
819 )
820 then
821 hr_utility.set_location('proposal_reason set to null', 26);
822 l_proposal_reason :=null;
823 else
824 hr_utility.set_location('proposal_reason set to SALBASISCHG', 27);
825 l_proposal_reason := 'SALBASISCHG';
826 end if;
827 -- End changes for bug 10161268
828
829 Hr_Maintain_Proposal_Api.INSERT_SALARY_PROPOSAL
830 (P_PAY_PROPOSAL_ID => l_Pay_Proposal_Id
831 ,P_ASSIGNMENT_ID => p_ass_id
832 ,P_BUSINESS_GROUP_ID => l_del_bg_id
833 ,P_CHANGE_DATE => l_val_start_date
834 ,P_PROPOSED_SALARY_N => per_saladmin_utility.get_proposed_salary(p_ass_id,l_val_start_date-1)
835 ,P_OBJECT_VERSION_NUMBER => l_ovn
836 ,P_ELEMENT_ENTRY_ID => L_Element_Entry_Id
837 ,P_MULTIPLE_COMPONENTS => 'N'
838 ,P_APPROVED => 'Y'
839 ,P_PROPOSAL_REASON => l_proposal_reason -- Modified for bug 10161268
840 ,P_INV_NEXT_SAL_DATE_WARNING => L_INV_NEXT_SAL_DATE_WARNING
841 ,P_PROPOSED_SALARY_WARNING => L_PROPOSED_SALARY_WARNING
842 ,P_APPROVED_WARNING => L_APPROVED_WARNING
843 ,P_PAYROLL_WARNING => L_PAYROLL_WARNING);
844
845 END IF;
846
847 -- fix for the bug 9446176 ends here
848
849 Else
850 hr_utility.set_location('Should never come here',25);
851 null;
852 End if;
853
854 /***
855 open csr_get_salary;
856 fetch csr_get_salary into l_element_entry_id;
857 if csr_get_salary%found then
858 close csr_get_salary;
859
860
861
862 open csr_chk_rec_exists;
863 fetch csr_chk_rec_exists into l_element_entry_id1;
864
865 if csr_chk_rec_exists%found then
866 close csr_chk_rec_exists;
867
868 --
869 hr_entry_api.delete_element_entry
870 ('DELETE'
871 ,l_val_start_date - 1
872 ,l_element_entry_id1);
873 --
874
875 else
876
877 close csr_chk_rec_exists;
878 hr_entry_api.delete_element_entry
879 ('ZAP'
880 ,l_val_start_date
881 ,l_element_entry_id);
882
883 end if;
884
885 l_entries_changed_warning := 'S';
886 else
887 close csr_get_salary;
888 end if;
889
890 **/
891 -- fix for bug 4531033 ends here
892 end if;--fix for bug 4764140.
893
894 hrentmnt.check_payroll_changes_asg(
895 p_ass_id,
896 p_pay_id,
897 l_dt_mode,
898 l_val_start_date,
899 l_val_end_date);
900 --
901 -- added the IF clause for bug 2537091
902 if p_per_sys_st <> 'END' then
903 hrentmnt.check_opmu(
904 p_ass_id,
905 p_pay_id,
906 l_dt_mode,
907 l_val_start_date,
908 l_val_end_date);
909 end if;
910 --
911 if p_only_one_entry_point = 'Y' then
912 return;
913 end if;
914 --
915 <<CHECK_TERM_COBRA>>
916 hr_utility.trace('CHECK_TERM_COBRA');
917 --
918 -- CHECK_TERM_COBRA:
919 --
920 --Bug# 5758747
921 IF l_leg_code = 'US' THEN
922 hr_assignment.check_for_cobra(
923 p_ass_id,
924 p_val_st_date,
925 p_val_end_date);
926 --
927 if hr_utility.check_warning then
928 p_returned_warning := 'HR_ASS_TERM_COBRA_EXISTS';
929 p_re_entry_point := 3;
930 return;
931 end if;
932 END IF;
933 --
934 <<RE_ENTRY_POINT_3>>
935 hr_utility.trace('RE_ENTRY_POINT3');
936 --
937 -- CHECK_SPP_AND_CEIL:
938 --
939 -- Check to see if there are any placements for the assignment that
940 -- have a spinal point sequence greater than the ceiling spinal
941 -- point sequence. Note the check is limited to those placements
942 -- linked to a grade spine record whose grade id is the same as
943 -- ASS.GRADE_ID.
944 --
945 if p_grd_id is not null and p_sp_ceil_st_id is not null then
946 hr_utility.set_location ('RE_ENTRY_POINT3 ' ||p_dt_mode,10 );
947 hr_utility.set_location ('p_eff_st_date ' ||p_eff_st_date,10 );
948
949 open sp_point;
950 fetch sp_point into l_null;
951 if sp_point%found then
952 fnd_message.set_name('PAY',
953 'PER_7935_CEIL_PLACE_HIGH_EXIST');
954 close sp_point;
955 fnd_message.raise_error;
956 end if;
957 close sp_point;
958 end if;
959 --fix for bug 4612843
960 open get_step_enddate;
961 fetch get_step_enddate into l_step_enddate;
962 if (l_step_enddate < hr_api.g_eot) then
963 close get_step_enddate;
964 open get_asg_enddate;
965 fetch get_asg_enddate into l_asg_enddate;
966 if ( l_asg_enddate > l_step_enddate ) then
967 close get_asg_enddate;
968 fnd_message.set_name('PAY','PAY_7589_SYS_STEP_DT_OUTDATE');
969 fnd_message.raise_error;
970 else
971 close get_asg_enddate;
972 end if ;
973 else
974 close get_step_enddate;
975 end if;
976
977 -- end of fix for the bug 4612843
978 --
979 p_re_entry_point := 0;
980 --
981 hr_utility.set_location('per_assignments_f3_pkg.update_and_delete_bundle',2);
982 --
983 end update_and_delete_bundle;
984 -----------------------------------------------------------------------------
985 END PER_ASSIGNMENTS_F3_PKG;
[Home] [Help]