1 Package Body per_pyp_bus as
2 /* $Header: pepyprhi.pkb 120.24 2011/09/28 10:47:13 akaranam ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pyp_bus.'; -- Global package name
9 g_legislation_code varchar2(150) default null;
10 g_pay_proposal_id number default null;
11
12 --
13 -- ----------------------------------------------------------------------------
14 -- |----------------------< check_non_updateable_args >-----------------------|
15 -- ----------------------------------------------------------------------------
16 --
17 Procedure check_non_updateable_args(p_rec in per_pyp_shd.g_rec_type) is
18 --
19 l_proc varchar2(72) := g_package||'check_non_updateable_args';
20 l_error exception;
21 l_argument varchar2(30);
22 --
23 Begin
24 hr_utility.set_location('Entering:'||l_proc, 5);
25 --
26 -- Only proceed with validation if a row exists for
27 -- the current record in the HR Schema
28 --
29 if not per_pyp_shd.api_updating
30 (p_pay_proposal_id => p_rec.pay_proposal_id
31 ,p_object_version_number => p_rec.object_version_number
32 ) then
33 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
34 hr_utility.set_message_token('PROCEDURE', l_proc);
35 hr_utility.set_message_token('STEP', '5');
36 end if;
37 --
38 hr_utility.set_location(l_proc, 6);
39 --
40 if p_rec.business_group_id <> per_pyp_shd.g_old_rec.business_group_id then
41 l_argument := 'business_group_id';
42 raise l_error;
43 end if;
44 hr_utility.set_location(l_proc, 7);
45 --
46 if p_rec.assignment_id <> per_pyp_shd.g_old_rec.assignment_id then
47 l_argument := 'assignment_id';
48 raise l_error;
49 end if;
50 hr_utility.set_location(l_proc, 8);
51 --
52 if p_rec.pay_proposal_id <> per_pyp_shd.g_old_rec.pay_proposal_id then
53 l_argument := 'pay_proposal_id';
54 raise l_error;
55 end if;
56 hr_utility.set_location(l_proc, 9);
57 --
58 /* change_date can be updated provided that the proposal has not already
59 been approved.
60
61 if p_rec.change_date <> per_pyp_shd.g_old_rec.change_date then
62 l_argument := 'change_date';
63 raise l_error;
64 end if;
65 hr_utility.set_location(l_proc, 10);*/
66 --
67 if p_rec.last_change_date <> per_pyp_shd.g_old_rec.last_change_date then
68 l_argument := 'last_change_date';
69 raise l_error;
70 end if;
71 hr_utility.set_location(l_proc, 11);
72 exception
73 when l_error then
74 hr_api.argument_changed_error
75 (p_api_name => l_proc
76 ,p_argument => l_argument);
77 when others then
78 raise;
79 hr_utility.set_location(' Leaving:'||l_proc, 12);
80 end check_non_updateable_args;
81 --
82 /* procedure changed to incorporate changes for Bug#7386307 by schowdhu*/
83
84 procedure validate_date_to
85 (p_assignment_id in number,
86 p_pay_proposal_id in number,
87 p_change_date in date,
88 p_date_to in date,
89 p_approved in per_pay_proposals.approved%TYPE
90 )
91 IS
92 --
93 -- Cursor to find legislation code
94 --
95 cursor csr_next_proposed_date is
96 select min(change_date)
97 from per_pay_proposals
98 where assignment_id = p_assignment_id
99 and change_date > p_change_date
100 and approved = 'N'
101 and pay_proposal_id <> p_pay_proposal_id;
102
103 cursor csr_next_approved_date is
104 select min(change_date)
105 from per_pay_proposals
106 where assignment_id = p_assignment_id
107 and change_date > p_change_date
108 and approved = 'Y'
109 and pay_proposal_id <> p_pay_proposal_id;
110 --
111 -- Declare local variables
112 --
113 l_proc varchar2(72) := 'validate_date_to';
114 l_next_proposed_date date;
115 l_next_approved_date date;
116
117 begin
118 hr_utility.set_location('Entering:'|| l_proc, 10);
119 if p_date_to is not null then
120
121 OPEN csr_next_proposed_date;
122 fetch csr_next_proposed_date into l_next_proposed_date;
123 CLOSE csr_next_proposed_date;
124
125 OPEN csr_next_approved_date;
126 fetch csr_next_approved_date into l_next_approved_date;
127 CLOSE csr_next_approved_date;
128
129 if p_approved = 'N' and l_next_proposed_date is not null then
130 if p_date_to >= l_next_proposed_date then
131 hr_utility.set_message(800, 'PER_SAL_DATES_OVERLAP');
132 hr_utility.raise_error;
133 end if;
134 end if;
135 if p_approved = 'Y' and l_next_approved_date is not null then
136 if p_date_to >= l_next_approved_date then
137 hr_utility.set_message(800, 'PER_SAL_DATES_OVERLAP');
138 hr_utility.raise_error;
139
140 end if;
141
142
143 end if;
144
145
146
147 end if;
148 --
149 hr_utility.set_location('Leaving :'|| l_proc, 90);
150 end validate_date_to;
151 --
152 procedure gen_last_change_date
153 (p_rec in out nocopy per_pyp_shd.g_rec_type) is
154 --
155 l_proc varchar2(72) := g_package || 'gen_last_change_date';
156 l_last_change_date per_pay_proposals.last_change_date%TYPE;
157 --
158 -- define a cusor to determine wheather another proposal exists or not
159 --
160 Cursor csr_last_change_date is
161 select max(pro.change_date)
162 from per_pay_proposals pro
163 where pro.assignment_id = p_rec.assignment_id
164 and pro.change_date<p_rec.change_date;
165
166 --
167 Begin
168 hr_utility.set_location('Entering:' || l_proc, 1);
169 --
170 -- set the last_change_date
171 --
172 open csr_last_change_date;
173 fetch csr_last_change_date into l_last_change_date;
174 if csr_last_change_date%notfound then
175 hr_utility.set_location(l_proc, 2);
176 p_rec.last_change_date := null;
177 else
178 p_rec.last_change_date := l_last_change_date;
179 end if;
180 close csr_last_change_date;
181 --
182 hr_utility.set_location('Leaving: ' || l_proc, 3);
183 --
184 end gen_last_change_date;
185 --
186 --
187 -------------------------------------------------------------------------------
188 -------------------------------< chk_pay_basis_change_date >-------------------
189 -------------------------------------------------------------------------------
190 --
191 --
192 -- Description:
193 -- It checks if there is already at least one approved salary proposal exists
194 -- and there is no current salary element entry (this can only happen if the
195 -- pay basis has been changed and the salary element closed down), the new
196 -- salary change date must be one day after the end_date of the previous
197 -- salary entry i.e. the date of the pay_basis change.
198 -- It also raise an error informing the user that they must remove the future
199 -- salary bases changes first, if there are any salary bases changes after
200 -- the change_date.
201 -- If the current element has an end date that is not the end of time or the
202 -- last effective end date of the assignment or the end date of the period
203 -- of service then an error will be raised.
204 --
205 -- Pre_conditions:
206 --
207 -- In Arguments:
208 -- p_assignment_id
209 -- p_change_date
210 --
211 -- Post Success:
212 -- Process continues if :
213 -- The change date is valid.
214 --
215 -- Post Failure:
216 -- An application error is raised and processing is terminated if any of
217 -- the following cases are found :
218 -- - There exist future change in the pay_basis.
219 -- - There exists a change in the pay basis that is not on the change_date.
220 --
221 -- Access Status
222 -- Internal Table Handler Use Only.
223 --
224 --
225 procedure chk_pay_basis_change_date
226 (p_assignment_id in per_pay_proposals.assignment_id%TYPE
227 ,p_change_date in per_pay_proposals.change_date%TYPE
228 ) is
229 --
230 -- Cursor to check that there is at least one approved salary proposal
231 -- for this assignment
232 --
233 cursor csr_first_proposal is
234 select null
235 from per_pay_proposals
236 where assignment_id = p_assignment_id
237 and approved = 'Y';
238 --
239 -- cursor which checks whether there is an open salary element.
240 --
241 cursor csr_sp_element_open is
242 select null
243 from pay_element_entries_f
244 where assignment_id = p_assignment_id
245 and creator_type = 'SP'
246 and effective_end_date = hr_general.end_of_time;
247 --
248 -- Cursor to get the effective_end_date of the latest salary
249 -- element.
250 --
251 cursor csr_element_effective_end_date is
252 select max(peef.effective_end_date)
253 from pay_element_entries_f peef
254 , pay_element_links_f pel
255 , pay_input_values_f piv
256 , per_pay_bases ppb
257 , per_all_assignments_f asg
258 where asg.assignment_id = p_assignment_id
259 and asg.pay_basis_id=ppb.pay_basis_id
260 and ppb.input_value_id=piv.input_value_id
261 and asg.effective_start_date
262 between piv.effective_start_date and piv.effective_end_date
263 and piv.element_type_id=pel.element_type_id
264 /**
265 * Bug Fix : 3036147
266 * Description: To allow the user create new salary proposal
267 * when salary element got changed.
268 *and asg.effective_start_date
269 * between pel.effective_start_date and pel.effective_end_date
270 **/
271 and pel.element_link_id=peef.element_link_id
272 and peef.assignment_id=p_assignment_id
273 and asg.assignment_id=peef.assignment_id
274 and peef.creator_type = 'SP';
275 --
276 cursor csr_asg_effective_end_date is
277 select max(effective_end_date)
278 from per_all_assignments_f asg,
279 per_assignment_status_types ast
280 where asg.assignment_id = p_assignment_id
281 and asg.assignment_status_type_id=ast.assignment_status_type_id
282 and ast.per_system_status='ACTIVE_ASSIGN';
283
284 -- Modified the cursor below to retrieve the last_standard_process_date
285 -- and actual_termination_date along with the final_process_date
286 -- as part of the fix for Bug 4073821
287 --
288 cursor csr_pds_final_process_date is
289 select final_process_date,
290 last_standard_process_date,
291 actual_termination_date
292 from per_periods_of_service pds
293 , per_all_assignments_f asg
294 where asg.assignment_id=p_assignment_id
295 and p_change_date between asg.effective_start_date and asg.effective_end_date
296 and asg.person_id=pds.person_id
297 and p_change_date between pds.date_start
298 and NVL(pds.final_process_date,hr_general.end_of_time);
299
300 --
301 -- Cursor to check that whether there are any pay_basis changes after
302 -- the change_date
303 --
304 cursor csr_asg_pay_bases is
305 select null
306 from per_all_assignments_f asg1
307 where assignment_id = p_assignment_id
308 and exists (select null
309 from per_all_assignments_f asg2
310 where asg2.assignment_id = p_assignment_id
311 and asg1.pay_basis_id +0 <> asg2.pay_basis_id
312 and asg2.effective_start_date > p_change_date
313 and asg1.effective_end_date >= p_change_date);
314
315 --
316 -- Cursor to determine the termination rule for the salary element
317 -- associated with the p_assignment_id as of the p_change_date
318 -- Added as part of fix for Bug 4073821
319 --
320
321 CURSOR csr_ele_term_rule(p_assignment_id IN NUMBER
322 ,p_change_date IN DATE) IS
323 SELECT pet.post_termination_rule
324 FROM pay_element_types_f pet,
325 per_all_assignments_f asg,
326 per_pay_bases ppb,
327 pay_input_values_f iv
328 WHERE pet.element_type_id = iv.element_type_id
329 AND iv.input_value_id = ppb.input_value_id
330 AND ppb.pay_basis_id = asg.pay_basis_id
331 AND asg.assignment_id = p_assignment_id
332 AND p_change_date BETWEEN iv.effective_start_date AND iv.effective_end_date
333 AND p_change_date BETWEEN asg.effective_start_date AND asg.effective_end_date
334 AND p_change_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
335 --
336 --
337 l_exists varchar2(1);
338 l_effective_end_date date;
339 l_asg_effective_end_date per_all_assignments_f.effective_end_date%TYPE;
340 l_pds_final_process_date per_periods_of_service.final_process_date%TYPE;
341 --4073821
342 l_pds_last_std_proc_date per_periods_of_service.last_standard_process_date%TYPE;
343 l_pds_actual_term_date per_periods_of_service.actual_termination_date%TYPE;
344 l_ele_term_rule pay_element_types_f.post_termination_rule%TYPE;
345
346 l_proc varchar2(72) := g_package||'chk_pay_basis_change_date';
347 --
348 begin
349 --
350 hr_utility.set_location('Entering: ' || l_proc,1);
351 --
352 -- check that whether this is the first salary record or not
353 -- i.e. At least one approved salary exists.
354 --
355 open csr_first_proposal;
356 fetch csr_first_proposal into l_exists;
357 if csr_first_proposal%found then
358 close csr_first_proposal;
359 hr_utility.set_location(l_proc,5);
360 --
361 -- Now check whether there has been a pay_basis change in the assignment.
362 -- If there is no salary element going to the end of time this means
363 -- that the pay_basis has changed.
364 --
365 -- now get the effective_end_date of the last salary_element
366 --
367 open csr_element_effective_end_date;
368 fetch csr_element_effective_end_date into l_effective_end_date;
369 if csr_element_effective_end_date%notfound then
370 close csr_element_effective_end_date;
371 hr_utility.set_location(l_proc,10);
372 hr_utility.set_message(801,'HR_51716_PYP_ELEMNT_ID_INVL');
373 hr_utility.raise_error;
374 else
375 close csr_element_effective_end_date;
376 if l_effective_end_date = hr_general.end_of_time then
377 hr_utility.set_location(l_proc,15);
378 else
379 --
380 -- element has ended for some reason. Check
381 -- to see if the new proposal is the day after the element has ended
382 -- which is correct for a pay basis change
383 --
384 hr_utility.set_location(l_proc,20);
385 if (l_effective_end_date+1 <> p_change_date) then
386 --
387 -- new proposal is not 1 day after element end so check to see if
388 -- element ends on the day the assignment ends, so changes are OK.
389 --
390 hr_utility.set_location(l_proc,25);
391 open csr_asg_effective_end_date;
392 fetch csr_asg_effective_end_date into l_asg_effective_end_date;
393 close csr_asg_effective_end_date;
394 if (l_asg_effective_end_date <>l_effective_end_date) then
395
396 -- Added for fixing the issue reported in Bug 4073821
397
398 OPEN csr_ele_term_rule(p_assignment_id, p_change_date);
399 FETCH csr_ele_term_rule INTO l_ele_term_rule;
400 CLOSE csr_ele_term_rule;
401
402 open csr_pds_final_process_date;
403 fetch csr_pds_final_process_date into l_pds_final_process_date, l_pds_last_std_proc_date, l_pds_actual_term_date;
404 close csr_pds_final_process_date;
405
406 IF NVL(l_ele_term_rule,'F') = 'F' THEN -- Start of Termination Rule condition added for Bug 4073821
407 --
408 -- element does not end on the day that the assignment ends, but
409 -- could end on the final process date
410 --
411 hr_utility.set_location(l_proc,26);
412 --4073821
413 -- open csr_pds_final_process_date;
414 -- fetch csr_pds_final_process_date into l_pds_final_process_date;
415 -- close csr_pds_final_process_date;
416 if (l_pds_final_process_date <> l_effective_end_date) or
417 (l_pds_final_process_date is null) then
418 hr_utility.set_location(l_proc,27);
419 hr_utility.set_message(801,'HR_51717_PYP_CHG_DATE_INVL');
420 hr_utility.raise_error;
421 end if;
422 ELSIF NVL(l_ele_term_rule,'F') = 'L' THEN
423 --
424 -- Element does not end on the day that the assignment ends, but
425 -- could end on the last standard process date
426 --
427 hr_utility.set_location(l_proc,26);
428 if (l_pds_last_std_proc_date <> l_effective_end_date) or
429 (l_pds_last_std_proc_date is null) then
430 hr_utility.set_location(l_proc,27);
431 hr_utility.set_message(801,'HR_51717_PYP_CHG_DATE_INVL');
432 hr_utility.raise_error;
433 end if;
434 END IF; -- End of Termination Rule condition added for Bug 4073821
435 end if;
436 end if;
437 --
438 end if;
439 --
440 end if;
441 --
442 else
443 close csr_first_proposal;
444 hr_utility.set_location(l_proc,28);
445 end if;
446 --
447 -- Now do a further check to see whether there is future pay_basis changes
448 -- The following validation is removed by ggnanagu
449 -- As part of the new Salary UI Enhancement 5059480
450 --
451 /*
452 open csr_asg_pay_bases;
453 fetch csr_asg_pay_bases into l_exists;
454 if csr_asg_pay_bases%found then
455 --
456 -- raise an error if there future pay_basis change in the assignment.
457 --
458 close csr_asg_pay_bases;
459 hr_utility.set_location(l_proc,30);
460 hr_utility.set_message(801,'HR_51718_PYP_FUTU_PAY_BAS_CHG');
461 hr_utility.raise_error;
462 else
463 close csr_asg_pay_bases;
464 hr_utility.set_location(l_proc,31);
465 end if;
466 */
467
468 hr_utility.set_location('Leaving: ' ||l_proc,35);
469 --
470 end chk_pay_basis_change_date;
471 --
472 --
473 -- ----------------------------------------------------------------
474 -- |------------------------< chk_access >------------------------|
475 -- ----------------------------------------------------------------
476 -- Description
477 --
478 -- This procedure checks whether the assignment id exists as of the
479 -- change_date of the proposal.
480 --
481 -- Pre_conditions:
482 -- A valid change_date
483 --
484 -- In Arguments:
485 -- p_assignment_id
486 -- p_change_date
487 --
488 -- Post Success:
489 -- Process continues if the assignment id is valid
490 --
491 -- Post Failure:
492 -- Processing stops after raising appropriate Error Message
493 --
494 -- Access Status
495 -- Internal Table Handler Use Only.
496 --
497 procedure chk_access
498 (p_change_date in date
499 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
500 ) is
501 --
502 -- Declare local variables
503 --
504 l_proc varchar2(72) := g_package||'chk_access';
505 l_exists varchar2(1);
506 --bug#8566773 vkodedal 03-Jun-2009
507 l_asg_type varchar2(1);
508 ---
509 ---cursor to get the assignment_type
510 ---
511 cursor csr_asg_type is
512 select ASSIGNMENT_TYPE
513 from per_all_assignments_f
514 where assignment_id=p_assignment_id
515 and p_change_date between effective_start_date
516 and effective_end_date;
517 --
518 -- Cursor to check access to the assignment record
519 --
520 cursor csr_asg_sec is
521 select null
522 from per_assignments_f2 asg
523 where asg.assignment_id = p_assignment_id
524 and p_change_date between asg.effective_start_date
525 and asg.effective_end_date;
526 begin
527 hr_utility.set_location('Entering:'|| l_proc, 10);
528
529 --get the assignment_type
530 --bug#8566773 vkodedal 03-Jun-2009 relax the validation for offer assignment
531 open csr_asg_type;
532 fetch csr_asg_type into l_asg_type;
533 close csr_asg_type;
534
535 if l_asg_type <> 'O' then
536 --
537 -- Always perform this validation on update and delete
538 -- even although the assignment_id value cannot be changed.
539 --
540 open csr_asg_sec;
541 fetch csr_asg_sec into l_exists;
542 if csr_asg_sec%notfound then
543 close csr_asg_sec;
544 fnd_message.set_name('PER', 'PER_SAL_ASG_NOT_EXIST');
545 fnd_message.raise_error;
546 end if;
547 close csr_asg_sec;
548
549 end if;
550 hr_utility.set_location(' Leaving:'|| l_proc, 30);
551 end chk_access;
552 --
553 --
554 -------------------------------------------------------------------------------
555 -------------------------------< chk_assignment_id_change_date >---------------
556 -------------------------------------------------------------------------------
557 --
558 --
559 -- Description:
560 -- - Validates that assignment_id exists and is date effctive on
561 -- per_assignmnets_f.
562 -- - Validates that the business group of the assignment is the same as the
563 -- business group of the pay proposal.
564 -- - Validates that the assignments has a valid pay_basis associated with it.
565 -- - Validates that the assingment system_status is not TERM_ASSIGN as of
566 -- change_date.
567 -- - Validates that the payroll status associated to the assignment is not
568 -- closed as of change_date.
569 -- - Validates that the change_date is after the last change_date.
570 -- - Validates that the change_date is unique
571 -- Note that the check for assignment type (i.e. TERM_ASSIG) and
572 -- valid pay_basis as of change date is done in chk_assignment.
573 -- validates that there is no other unapproved proposals
574 -- validates that the change_date is not updated if the proposal was approved.
575 -- Note: The chk_assignment_id and chk_change_date is merged into this procedure
576 -- because of close interrelations between assignment_id and change_date.
577 --
578 -- Pre_conditions:
579 -- A valid business_group_id
580 --
581 --
582 -- In Arguments:
583 -- p_pay_proposal_id
584 -- p_assignment_id
585 -- p_business_group_id
586 -- p_change_date
587 -- p_payroll_warning
588 -- p_object_version_number
589 --
590 -- Post Success:
591 -- Process continues if :
592 -- All the in parameters are valid.
593 --
594 -- Post Failure:
595 -- An application error is raised and processing is terminated if any of
596 -- the following cases are found :
597 -- - The assignmnet_id does not exist or is not date effective
598 -- - The business group of the assignment is invalid
599 -- - The assigment has not a pay_bases associated with it.
600 -- - The assignment system status is TERM_ASSIGN
601 -- - The change_date with the same date is already exists for the assinment.
602 -- - The change_date is before another existing change_date for the assignment.
603 --
604 -- Access Status
605 -- Internal Table Handler Use Only.
606 --
607 --
608
609 procedure chk_assignment_id_change_date
610 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
611 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
612 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
613 ,p_change_date in per_pay_proposals.change_date%TYPE
614 ,p_payroll_warning out nocopy boolean
615 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
616 )
617 is
618 --
619 l_exists varchar2(1);
620 l_api_updating boolean;
621 l_proc varchar2(72) := g_package||'chk_assignment_id_change_date';
622 l_pay_basis_id per_all_assignments_f.pay_basis_id%TYPE;
623 l_payroll_status per_time_periods.status%TYPE;
624 l_assginment_id per_all_assignments_f.assignment_id%TYPE;
625 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
626 l_system_status per_assignment_status_types.per_system_status%TYPE;
627 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
628 l_change_date per_pay_proposals.change_date%TYPE;
629 l_payroll_id per_all_assignments_f.payroll_id%TYPE;
630 --
631 --
632 -- Cursor to check existence of pay proposal with the same change date for the
633 -- assignment.
634 -- Also to check the latest proposal change_date for the assignment.
635 --
636 cursor csr_dup_change_date is
637 select null
638 from per_pay_proposals
639 where assignment_id = p_assignment_id
640 and business_group_id + 0 = p_business_group_id
641 and change_date = p_change_date
642 and pay_proposal_id <> nvl(p_pay_proposal_id,-1);
643 --
644 cursor csr_last_change_date is
645 select max(change_date)
646 from per_pay_proposals
647 where assignment_id = p_assignment_id
648 and business_group_id + 0 = p_business_group_id
649 and pay_proposal_id<>nvl(p_pay_proposal_id,-1);
650 --
651 -- Define a cursor to check whether other proposals exist.
652 --
653 Cursor csr_other_proposals_exist is
654 select null
655 from per_pay_proposals
656 where assignment_id = p_assignment_id
657 and approved = 'N'
658 and pay_proposal_id<>nvl(p_pay_proposal_id,-1);
659 --
660 cursor csr_chk_assig_details is
661 select ast.per_system_status,
662 asg.business_group_id,
663 asg.assignment_type,
664 ptp.status,
665 asg.pay_basis_id,
666 asg.payroll_id
667 from per_all_assignments_f asg,
668 per_time_periods ptp,
669 per_assignment_status_types ast
670 where asg.assignment_id = p_assignment_id
671 and asg.assignment_status_type_id = ast.assignment_status_type_id
672 and p_change_date between asg.effective_start_date
673 and asg.effective_end_date
674 and asg.payroll_id=ptp.payroll_id(+)
675 and (p_change_date between ptp.start_date(+)
676 and ptp.end_date(+)); --bug 2694178, 2801228
677 --
678 --
679 begin
680 hr_utility.set_location('Entering:'|| l_proc, 1);
681 --
682 -- Check mandatory parameters have been set
683 --
684 hr_api.mandatory_arg_error
685 (p_api_name => l_proc
686 ,p_argument => 'assignment_id'
687 ,p_argument_value => p_assignment_id
688 );
689 --
690 hr_api.mandatory_arg_error
691 (p_api_name => l_proc
692 ,p_argument => 'business_group_id'
693 ,p_argument_value => p_business_group_id
694 );
695 --
696 hr_api.mandatory_arg_error
697 (p_api_name => l_proc
698 ,p_argument => 'change_date'
699 ,p_argument_value => p_change_date
700 );
701 --
702 -- Only proceed with validation if :
703 -- a) The current g_old_rec is current and
704 -- b) The value for assignment_id or change_date has changed
705 --
706 l_api_updating := per_pyp_shd.api_updating
707 (p_pay_proposal_id => p_pay_proposal_id
708 ,p_object_version_number => p_object_version_number);
709 --
710 -- only proceed if we are inserting or if we are updaing and change date
711 -- has changed
712 --
713 if (l_api_updating AND (nvl(per_pyp_shd.g_old_rec.change_date,hr_api.g_date) <>
714 nvl(p_change_date,hr_api.g_date) )or not l_api_updating) then
715 --
716 -- if we are updating but it was already approved then error.
717 --
718 if ((l_api_updating and per_pyp_shd.g_old_rec.approved='Y') and
719 (nvl(g_validate_ss_change_pay,'N') = 'N'))then
720 hr_utility.set_message(800,'HR_51349_PYP_CNT_UPD_CHG_DATE');
721 hr_utility.raise_error;
722 else
723 hr_utility.set_location(l_proc, 2);
724 --
725 -- Check the assignment details as of change_date
726 --
727 open csr_chk_assig_details;
728 fetch csr_chk_assig_details into l_system_status, l_business_group_id,
729 l_assignment_type, l_payroll_status, l_pay_basis_id,l_payroll_id;
730 if csr_chk_assig_details%notfound then
731 hr_utility.set_location(l_proc, 5);
732 -- The assignment_id is incorrect
733 close csr_chk_assig_details;
734 /**
735 * Bug Fix: 3144666
736 * Description: To change the error to warning
737 **/
738 p_payroll_warning := true;
739 /*Change date does not fall within any payroll period.
740 Message changed for Bug 3077957 */
741 -- hr_utility.set_message(800,'PER_289483_CHG_DT_NO_PAY_PRD');
742 -- hr_utility.raise_error;
743 --
744 else close csr_chk_assig_details;
745 --
746 -- Check that the business group id is the same.
747 --
748 if l_business_group_id <> p_business_group_id then
749 hr_utility.set_location(l_proc, 10);
750 -- The business_group_id is incorrect
751 hr_utility.set_message(801,'HR_51255_PYP_INVLID_BUS_GROUP');
752 hr_utility.raise_error;
753 --
754 -- Check that the system_status is not 'TERM_ASSIGN'
755 --
756 elsif l_system_status = 'TERM_ASSIGN' then
757 hr_utility.set_location(l_proc, 15);
758 hr_utility.set_message(801,'HR_7340_SAL_ASS_TERMINATED');
759 hr_utility.raise_error;
760 --
761 -- Check that the payroll_status is not closed
762 -- If the payroll is null then there is no need check payroll status
763 -- bug# 2801228
764 elsif (nvl(l_payroll_status,'C') <> 'O' and
765 l_payroll_id is not null) then
766 hr_utility.set_location(l_proc, 25);
767 /**
768 * Bug Fix: 3144666
769 * Description: To change the error to warning
770 **/
771 p_payroll_warning := true;
772 --hr_utility.set_message(800,'HR_SAL_PAYROLL_PERIOD_CLOSED');
773 --hr_utility.raise_error; -- Error will raised instead of warning.
774 -- bug# 2694178
775 --
776 -- Check that the assignment has a vaild pay_basis
777 --
778 elsif (l_pay_basis_id IS NULL) then
779 hr_utility.set_location(l_proc, 30);
780 hr_utility.set_message(801, 'HR_289855_SAL_ASS_NOT_SAL_ELIG');
781 hr_utility.raise_error;
782 elsif (l_api_updating = false) then
783 --
784 -- check that the p_change_date is greater than the last proposal
785 -- change_date.
786 --
787 -- The following code is commented out.
788 -- New Salary proposals can be added even if future proposals exist
789 -- Change made by ggnanagu
790 /*
791 open csr_last_change_date;
792 fetch csr_last_change_date into l_change_date;
793 if csr_last_change_date%notfound then
794 hr_utility.set_location(l_proc, 35);
795 --
796 elsif
797 l_change_date > p_change_date then
798 hr_utility.set_location(l_proc, 40);
799 close csr_last_change_date;
800 hr_utility.set_message(801,'HR_7293_SAL_FUTURE_APPS_EXIST');
801 hr_utility.raise_error;
802 --
803 end if;
804 close csr_last_change_date;
805 */
806 --
807 -- The following code is commented out.
808 -- There can be more than one Unapproved Proposal now
809 -- Change made by ggnanagu
810 /*
811 open csr_other_proposals_exist;
812 fetch csr_other_proposals_exist into l_exists;
813 if csr_other_proposals_exist%notfound then
814 hr_utility.set_location(l_proc, 45);
815 close csr_other_proposals_exist;
816 --
817 else
818 close csr_other_proposals_exist;
819 hr_utility.set_location(l_proc, 50);
820 hr_utility.set_message(801, 'HR_7294_SAL_ONLY_ONE_PROPOSAL');
821 hr_utility.raise_error;
822 end if;
823 */
824
825 --
826 -- Now check for change_date being unique.
827 --
828 open csr_dup_change_date;
829 fetch csr_dup_change_date into l_exists;
830 if csr_dup_change_date%notfound then
831 hr_utility.set_location(l_proc, 55);
832 close csr_dup_change_date;
833 else
834 hr_utility.set_location(l_proc, 60);
835 close csr_dup_change_date;
836 hr_utility.set_message(801,'HR_13000_SAL_DATE_NOT_UNIQUE');
837 hr_utility.raise_error;
838 end if;
839 --
840 -- This code was added at version 70.4 to fix bug 411671.
841 -- this checks that the change_date of the salary proposal is valid if
842 -- the pay_basis has chnaged. It checks that the chnage_date must be the
843 -- same date as that of pay_basis chnage in the assignmnet.
844 -- it also checks that the change_date cannot be before any pay_basis changes.
845 --
846 --
847 -- This is commented out by ggnanagu
848 -- The new salary proposal need not have the change_date equals to the
849 -- Salary Basis change date
850 /*
851
852 chk_pay_basis_change_date (p_assignment_id,p_change_date);
853 hr_utility.set_location(l_proc, 61);
854
855 */
856 end if;
857 --
858 end if;
859 end if;
860 end if;
861 hr_utility.set_location('Leaving: ' || l_proc, 65);
862 end chk_assignment_id_change_date;
863 --
864 --
865 --
866 ------------------------------------------------------------------------------
867 ----------------------- derive_next_sal_perf_date-----------------------------
868 ------------------------------------------------------------------------------
869 --
870 -- Description
871 --
872 -- This function sets the next salary or performance review date
873 --
874 --
875 --
876 -- Pre_conditions:
877 -- A valid change_date
878 --
879 --
880 -- In Arguments:
881 -- p_change_date
882 -- p_period
883 -- p_frequency_
884 --
885 -- Post Success:
886 -- A date is returned from the arguments.
887 --
888 --
889 -- Access Status
890 -- Internal Table Handler Use Only.
891 --
892 --
893 function derive_next_sal_perf_date
894 (p_change_date in per_pay_proposals.change_date%TYPE
895 ,p_period in per_all_assignments_f.sal_review_period%TYPE
896 ,p_frequency in per_all_assignments_f.sal_review_period_frequency%TYPE
897 )
898 Return Date is
899 --
900 l_proc varchar2(72) := g_package||'derive_next_sal_perf_date';
901 l_derived_date date;
902 l_num_months number(15) := 0;
903 l_num_days number(15) := 0;
904 --
905 begin
906 hr_utility.set_location('Entering:'|| l_proc, 1);
907 --
908 -- Check if the frequency is year
909 --
910 if (p_frequency = 'Y')then
911 hr_utility.set_location(l_proc, 2);
912 l_num_months := 12 * p_period;
913 elsif
914 (p_frequency = 'M') then
915 hr_utility.set_location(l_proc, 3);
916 l_num_months := p_period;
917 --
918 elsif (p_frequency = 'W' ) then
919 hr_utility.set_location(l_proc, 4);
920 l_num_days := 7 * p_period;
921 --
922 elsif
923 (p_frequency = 'D') then
924 hr_utility.set_location(l_proc, 5);
925 l_num_days := p_period;
926 --
927 else
928 hr_utility.set_location(l_proc, 6);
929 hr_utility.set_message(801,'HR_51258_PYP_INVAL_FREQ_PERIOD');
930 hr_utility.raise_error;
931 end if;
932 --
933 -- Now return the derived date
934 --
935 if (l_num_months <> 0) then
936 hr_utility.set_location(l_proc, 7);
937 --start of changes for bug # 12884599
938 l_derived_date :=least ( ADD_MONTHS (p_change_date, l_num_months),
939 TRUNC(ADD_MONTHS (p_change_date, l_num_months),'MONTH') +
940 (EXTRACT(DAY FROM TO_DATE(p_change_date)) - 1) );
941 --end of changes for bug # 12884599
942
943 --
944 elsif (l_num_days <> 0 ) then
945 hr_utility.set_location(l_proc, 8);
946 l_derived_date := p_change_date + l_num_days;
947 --
948 end if;
949 hr_utility.set_location('Leaving: ' ||l_proc, 9);
950 --
951 return l_derived_date;
952 --
953 end derive_next_sal_perf_date;
954 --
955 --
956 -- ---------------------------------------------------------------------------
957 -- |--------------------< chk_next_sal_review_date >-------------------------|
958 -- ---------------------------------------------------------------------------
959 --
960 --
961 --
962 -- Description:
963 -- - Validates that the next_sal_review_date is after the change_date.
964 -- - Set a warning flag if the assignment type is TERM_ASSIGN as of
965 -- - the next_sal_review_date.
966 --
967 --
968 -- Pre_conditions:
969 -- A valid change_date
970 -- A valid business_group_id
971 -- A valid assignment_id
972 --
973 -- In Arguments:
974 -- p_pay_proprosal_id
975 -- p_business_group_id
976 -- p_assignment_id
977 -- p_change_date
978 -- p_next_sal_review_date
979 -- p_object_version_number
980 -- p_inv_next_sal_date_warning
981 --
982 -- Post Success:
983 -- Process continues if :
984 -- The next_sal_review_date is null or
985 -- the next_sal_review_date is a date for which the assignment type is
986 -- not TERM_ASSIGN
987 --
988 -- Post Failure:
989 -- An application error is raised and processing is terminated if any of
990 -- the following cases are found :
991 -- - The assignment_id is null.
992 -- - The change_date is null.
993 -- - A warning flag is set if the next_sal_review_date is a date for which
994 -- the assignment type is TERM_ASSIGN.
995 --
996 -- Access Status
997 -- Internal Table Handler Use Only.
998 --
999 --
1000 procedure chk_next_sal_review_date
1001 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
1002 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
1003 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
1004 ,p_change_date in per_pay_proposals.change_date%TYPE
1005 ,p_next_sal_review_date in per_pay_proposals.next_sal_review_date%TYPE
1006 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
1007 ,p_inv_next_sal_date_warning out nocopy boolean
1008 )
1009 is
1010 --
1011 l_proc varchar2(72) := g_package||'chk_next_sal_review_date';
1012 l_exists varchar2(1);
1013 l_api_updating boolean;
1014 --
1015 -- Cursor to check the assignment status as next_sal_review_date.
1016 --
1017 cursor csr_valid_assg_status is
1018 select null
1019 from per_all_assignments_f assg,
1020 per_assignment_status_types ast
1021 where assg.assignment_id = p_assignment_id
1022 and assg.assignment_status_type_id = ast.assignment_status_type_id
1023 and assg.business_group_id + 0 = p_business_group_id
1024 and p_next_sal_review_date between assg.effective_start_date
1025 and nvl(assg.effective_end_date, hr_api.g_eot)
1026 and ast.per_system_status = 'TERM_ASSIGN';
1027 --
1028 begin
1029 hr_utility.set_location('Entering:'|| l_proc, 1);
1030 p_inv_next_sal_date_warning := false;
1031 --
1032 -- Check mandatory parameters have being set.
1033 --
1034 hr_api.mandatory_arg_error
1035 (p_api_name => l_proc
1036 ,p_argument => 'change_date'
1037 ,p_argument_value => p_change_date
1038 );
1039 --
1040 hr_api.mandatory_arg_error
1041 (p_api_name => l_proc
1042 ,p_argument => 'assignment_id'
1043 ,p_argument_value => p_assignment_id
1044 );
1045 --
1046 hr_api.mandatory_arg_error
1047 (p_api_name => l_proc
1048 ,p_argument => 'business_group_id'
1049 ,p_argument_value => p_business_group_id
1050 );
1051 --
1052 -- Only proceed with validation if :
1053 -- a) The current g_old_rec is current and
1054 -- b) The value for next_sal_review_date has changed
1055 --
1056 l_api_updating := per_pyp_shd.api_updating
1057 (p_pay_proposal_id => p_pay_proposal_id
1058 ,p_object_version_number => p_object_version_number);
1059 --
1060 if (l_api_updating AND (nvl(per_pyp_shd.g_old_rec.next_sal_review_date,hr_api.g_date) <>
1061 nvl(p_next_sal_review_date,hr_api.g_date) )or not l_api_updating) then
1062 --
1063 -- If the next_sal_review is not null do the following checks
1064 --
1065 if (p_next_sal_review_date IS NOT NULL) then
1066 hr_utility.set_location(l_proc, 2);
1067 --
1068 -- Check that the next_sal_review date is not before the change_date
1069 --
1070 -- Bug 740286
1071 -- if (p_change_date > p_next_sal_review_date) then
1072 --
1073 -- hr_utility.set_location(l_proc, 3);
1074 -- hr_utility.set_message(801, 'HR_13007_SAL_DATE_NEXT_DATE');
1075 -- hr_utility.raise_error;
1076 -- end if;
1077 --
1078 -- check the assignment_status as the next_sal_review_date.
1079 -- if the assignment status is TERM_ASSIGN then issue a warning
1080 -- message to inform the user about it.
1081 --
1082 open csr_valid_assg_status;
1083 fetch csr_valid_assg_status into l_exists;
1084 if csr_valid_assg_status%found then
1085 p_inv_next_sal_date_warning := true;
1086 end if;
1087 --
1088 close csr_valid_assg_status;
1089 --
1090 hr_utility.set_location('LEAVING ' ||l_proc, 4);
1091 end if;
1092 --
1093 end if;
1094 --
1095 hr_utility.set_location('Leaving: ' ||l_proc, 5);
1096 --
1097 end chk_next_sal_review_date;
1098 --
1099 --
1100 -- ---------------------------------------------------------------------------
1101 -- |------------------------< chk_chg_next_sal_review_date >-----------------|
1102 -- ---------------------------------------------------------------------------
1103 --
1104 --
1105 -- Description:
1106 -- - Derive the next_sal_review_date if the period and frequency information
1107 -- - is set for the salary at the assignment level.
1108 --
1109 --
1110 -- Pre_conditions:
1111 -- A valid change_date
1112 -- A valid business_group_id
1113 -- A valid assignment_id
1114 --
1115 -- In Arguments:
1116 -- p_pay_proprosal_id
1117 -- p_business_group_id
1118 -- p_assignment_id
1119 -- p_change_date
1120 -- p_next_sal_review_date
1121 -- p_object_version_number
1122 -- p_inv_next_sal_date_warning
1123 --
1124 -- Post Success:
1125 -- Process continues if :
1126 -- The next_sal_review_date is null or
1127 -- the next_sal_review_date is a date for which the assignment type is
1128 -- not TERM_ASSIGN
1129 --
1130 -- Post Failure:
1131 -- An application error is raised and processing is terminated if any of
1132 -- the following cases are found :
1133 -- - The assignment_id is null.
1134 -- - The change_date is null.
1135 -- - A warning flag is set if the next_sal_review_date is a date for which
1136 -- the assignment type is TERM_ASSIGN.
1137 --
1138 -- Access Status
1139 -- Internal Table Handler Use Only.
1140 --
1141 --
1142 procedure chk_chg_next_sal_review_date
1143 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
1144 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
1145 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
1146 ,p_change_date in per_pay_proposals.change_date%TYPE
1147 ,p_next_sal_review_date in out nocopy per_pay_proposals.next_sal_review_date%TYPE
1148 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
1149 ,p_inv_next_sal_date_warning out nocopy boolean
1150 )
1151 is
1152 --
1153 l_proc varchar2(72):= g_package||'chk_chg_next_sal_review_date';
1154 l_exists varchar2(1);
1155 l_api_updating boolean;
1156 l_sal_review_period number(15);
1157 l_sal_review_period_frequency varchar2(30);
1158 l_next_sal_review_date Date;
1159 --
1160 --
1161 -- Cursor to get the frequency for salary details at
1162 -- assignment level.
1163 --
1164 cursor csr_sal_review_details is
1165 select sal_review_period,
1166 sal_review_period_frequency
1167 from per_all_assignments_f
1168 where assignment_id = p_assignment_id
1169 and business_group_id + 0 = p_business_group_id
1170 and p_change_date between effective_start_date
1171 and nvl(effective_end_date, hr_api.g_eot);
1172 --
1173 --
1174 -- Cursor to check the assignment status as next_sal_review_date.
1175 --
1176 cursor csr_valid_assg_status is
1177 select null
1178 from per_all_assignments_f assg,
1179 per_assignment_status_types ast
1180 where assg.assignment_id = p_assignment_id
1181 and assg.assignment_status_type_id = ast.assignment_status_type_id
1182 and assg.business_group_id + 0 = p_business_group_id
1183 and p_next_sal_review_date between assg.effective_start_date
1184 and nvl(assg.effective_end_date, hr_api.g_eot)
1185 and ast.per_system_status = 'TERM_ASSIGN';
1186 --
1187 begin
1188 hr_utility.set_location('Entering:'|| l_proc, 1);
1189 p_inv_next_sal_date_warning := false;
1190 --
1191 -- Check mandatory parameters have being set.
1192 --
1193 hr_api.mandatory_arg_error
1194 (p_api_name => l_proc
1195 ,p_argument => 'change_date'
1196 ,p_argument_value => p_change_date
1197 );
1198 --
1199 hr_api.mandatory_arg_error
1200 (p_api_name => l_proc
1201 ,p_argument => 'assignment_id'
1202 ,p_argument_value => p_assignment_id
1203 );
1204 --
1205 hr_api.mandatory_arg_error
1206 (p_api_name => l_proc
1207 ,p_argument => 'business_group_id'
1208 ,p_argument_value => p_business_group_id
1209 );
1210 --
1211 --
1212 -- Only proceed with validation if :
1213 -- a) The current g_old_rec is current and
1214 -- b) The value for next_sal_review_date has changed
1215 --
1216 l_api_updating := per_pyp_shd.api_updating
1217 (p_pay_proposal_id => p_pay_proposal_id
1218 ,p_object_version_number => p_object_version_number);
1219 --
1220 if (l_api_updating AND (nvl(per_pyp_shd.g_old_rec.next_sal_review_date,hr_api.g_date) <>
1221 nvl(p_next_sal_review_date,hr_api.g_date)) OR not l_api_updating) then
1222 --
1223 if (p_next_sal_review_date IS NULL) then
1224 -- When the next_sal_review is null then we do the following:
1225 -- a) check the sal_review details at the assignment level
1226 -- If the details exist then calculate the next_sal_review
1227 -- date accordingly, otherwise do nothing.
1228 --
1229 open csr_sal_review_details;
1230 fetch csr_sal_review_details into l_sal_review_period,
1231 l_sal_review_period_frequency;
1232 if csr_sal_review_details%found then
1233 if (l_sal_review_period is not null) then
1234 hr_utility.set_location(l_proc, 6);
1235 p_next_sal_review_date :=
1236 derive_next_sal_perf_date
1237 (p_change_date => p_change_date
1238 ,p_period => l_sal_review_period
1239 ,p_frequency => l_sal_review_period_frequency
1240 );
1241 open csr_valid_assg_status;
1242 fetch csr_valid_assg_status into l_exists;
1243 --
1244 if csr_valid_assg_status%found then
1245 hr_utility.set_location(l_proc, 7);
1246 p_inv_next_sal_date_warning := true;
1247 end if;
1248 --
1249 close csr_valid_assg_status;
1250 --
1251 end if;
1252 end if;
1253 close csr_sal_review_details;
1254 hr_utility.set_location(l_proc, 10);
1255 --
1256 end if;
1257 --
1258 end if;
1259 --
1260 hr_utility.set_location('Leaving: ' ||l_proc, 11);
1261 end chk_chg_next_sal_review_date;
1262 --
1263 --
1264 -- ----------------------------------------------------------------------------
1265 -- |---------------------------< chk_multiple_components >---------------------|
1266 -- ----------------------------------------------------------------------------
1267 --
1268 -- Description:
1269 -- - Validates that the first salary proposal cannot have multiple_components.
1270 --
1271 -- Pre_conditions:
1272 -- A valid change_date
1273 -- A valid business_group_id
1274 -- A valid assignment_id
1275 --
1276 -- In Arguments:
1277 -- p_pay_proprosal_id
1278 -- p_assignment_id
1279 -- p_change_date
1280 -- p_multiple_components
1281 -- p_object_version_number
1282 --
1283 -- Post Success:
1284 -- Process continues if :
1285 -- The multiple_components is not set to a value other than 'Y' or 'N'.
1286 --
1287 -- Post Failure:
1288 -- An application error is raised and processing is terminated if any of
1289 -- the following cases are found :
1290 -- - The multiple_components is set to a value other than 'Y' or 'N'.
1291 -- - The multiple_components is set to 'Y' for the first salary proposal.
1292 --
1293 -- Access Status
1294 -- Internal Table Handler Use Only.
1295 --
1296 procedure chk_multiple_components
1297 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
1298 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
1299 ,p_change_date in per_pay_proposals.change_date%TYPE
1300 ,p_multiple_components in per_pay_proposals.multiple_components%TYPE
1301 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
1302 )
1303 is
1304 --
1305 l_proc varchar2(72):= g_package||'chk_multiple_components';
1306 l_exists varchar2(1);
1307 l_api_updating boolean;
1308 --
1309 -- Cursor to check for the first salary proposals.
1310 --
1311 Cursor csr_is_first_proposal is
1312 select null
1313 from per_pay_proposals pro,
1314 per_all_assignments_f ass
1315 where pro.assignment_id = p_assignment_id
1316 and ass.assignment_id = pro.assignment_id
1317 and p_change_date between ass.effective_start_date
1318 AND ass.effective_end_date;
1319 --
1320 begin
1321 hr_utility.set_location('Entering:'|| l_proc, 1);
1322 --
1323 -- Check mandatory parameters have being set.
1324 --
1325 hr_api.mandatory_arg_error
1326 (p_api_name => l_proc
1327 ,p_argument => 'change_date'
1328 ,p_argument_value => p_change_date
1329 );
1330 --
1331 hr_api.mandatory_arg_error
1332 (p_api_name => l_proc
1333 ,p_argument => 'assignment_id'
1334 ,p_argument_value => p_assignment_id
1335 );
1336 --
1337 hr_api.mandatory_arg_error
1338 (p_api_name => l_proc
1339 ,p_argument => 'multiple_components'
1340 ,p_argument_value => p_multiple_components
1341 );
1342 --
1343 -- Only proceed with validation if :
1344 -- a) The current g_old_rec is current and
1345 -- b) The value for multiple_components has changed
1346 --
1347 l_api_updating := per_pyp_shd.api_updating
1348 (p_pay_proposal_id => p_pay_proposal_id
1349 ,p_object_version_number => p_object_version_number);
1350 --
1351 if (l_api_updating AND(per_pyp_shd.g_old_rec.multiple_components <>
1352 p_multiple_components) OR not l_api_updating) then
1353 hr_utility.set_location(l_proc, 4);
1354 --
1355 -- check that the value of the multiple_components is either 'Y' or 'N'
1356 --
1357 if (p_multiple_components <> 'Y' AND p_multiple_components <> 'N') then
1358 hr_utility.set_location(l_proc, 5);
1359 hr_utility.set_message (801, 'HR_51261_PYP_INVAL_MULTI_COMP');
1360 hr_utility.raise_error;
1361 end if;
1362 --
1363 -- Check that the multiple_components is not set to 'Y' for the first
1364 -- proposal
1365 --
1366 -- Commented by ggnanagu
1367 -- Now its possible for the first proposal to have components
1368 /*
1369 open csr_is_first_proposal;
1370 fetch csr_is_first_proposal into l_exists;
1371 if csr_is_first_proposal%notfound then
1372 hr_utility.set_location(l_proc, 10);
1373 if (p_multiple_components = 'Y') then
1374 close csr_is_first_proposal;
1375 hr_utility.set_location(l_proc, 15);
1376 hr_utility.set_message (801, 'HR_51262_PYP_FIRST_SAL_COMP');
1377 hr_utility.raise_error;
1378 end if;
1379 --
1380 end if;
1381 --
1382 hr_utility.set_location(l_proc, 20);
1383 --
1384 close csr_is_first_proposal; */
1385
1386 end if;
1387 hr_utility.set_location('Leaving: '||l_proc, 25);
1388
1389 end chk_multiple_components;
1390 --
1391 --
1392 -- ----------------------------------------------------------------------------
1393 -- |-----------------------< chk_proposal_reason >----------------------------|
1394 -- ----------------------------------------------------------------------------
1395 --
1396 -- Description:
1397 -- Validates the value entered for proposal_reason exists on hr_lookups.
1398 --
1399 -- Pre-conditions:
1400 -- None
1401 --
1402 -- In Arguments:
1403 -- p_pay_proposal_id
1404 -- p_proposal_reason
1405 -- p_change_date
1406 -- p_object_version_number
1407 --
1408 -- Post Success:
1409 -- Processing continues if :
1410 -- - The proposal_reason value is valid
1411 --
1412 -- Post Failure:
1413 -- An application error is raised and processing is terminated if any
1414 -- - The proposal_reason value is invalid
1415 --
1416 -- Access Status:
1417 -- Internal Table Handler Use Only.
1418 --
1419 --
1420 procedure chk_proposal_reason
1421 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
1422 ,p_change_date in per_pay_proposals.change_date%TYPE
1423 ,p_proposal_reason in per_pay_proposals.proposal_reason%TYPE
1424 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
1425 )
1426 is
1427 --
1428 l_proc varchar2(72):= g_package||'chk_proposal_reason';
1429 l_api_updating boolean;
1430 --
1431
1432 begin
1433 hr_utility.set_location('Entering:'|| l_proc, 1);
1434 --
1435 -- Check mandatory parameters have being set.
1436 --
1437 hr_api.mandatory_arg_error
1438 (p_api_name => l_proc
1439 ,p_argument => 'change_date'
1440 ,p_argument_value => p_change_date
1441 );
1442 --
1443 -- Only proceed with validation if :
1444 -- a) The current g_old_rec is current and
1445 -- b) The value for proposal_reason has changed
1446 --
1447 l_api_updating := per_pyp_shd.api_updating
1448 (p_pay_proposal_id => p_pay_proposal_id
1449 ,p_object_version_number => p_object_version_number);
1450 --
1451 if (l_api_updating AND (nvl(per_pyp_shd.g_old_rec.proposal_reason,hr_api.g_varchar2) <>
1452 nvl(p_proposal_reason,hr_api.g_varchar2))
1453 OR not l_api_updating ) then
1454 hr_utility.set_location(l_proc, 6);
1455 --
1456 -- check that the p_proposal_reason exists in hr_lookups.
1457 --
1458 if (p_proposal_reason IS NOT NULL ) then
1459 if hr_api.not_exists_in_hr_lookups
1460 (p_effective_date => p_change_date
1461 ,p_lookup_type => 'PROPOSAL_REASON'
1462 ,p_lookup_code => p_proposal_reason
1463 ) then
1464 -- Error: Invalid proposal_reason
1465 hr_utility.set_location(l_proc, 10);
1466 hr_utility.set_message(801,'HR_51265_INVAL_PRO_REASON');
1467 hr_utility.raise_error;
1468 end if;
1469 --
1470 end if;
1471 end if;
1472 --
1473 hr_utility.set_location(' Leaving:'|| l_proc, 15);
1474 end chk_proposal_reason;
1475 --
1476 -- ----------------------------------------------------------------------------
1477 -- |---------------------< is_salary_in_range >--------------------------------|
1478 -- ----------------------------------------------------------------------------
1479 --
1480 --
1481 -- Description
1482 -- This is to validate that the given salary is within the grade range
1483 --
1484 -- Pre_condition
1485 -- None
1486 --
1487 -- In Arguments:
1488 -- p_assignment_id
1489 -- p_bussiness_group_id
1490 -- p_change_date
1491 -- p_proposed_salary_n
1492 -- p_proposed-salary_warning
1493 --
1494 -- Post Success
1495 -- The process continues if:
1496 -- The proposed salary is in the range determined by the grade rate
1497 --
1498 -- Post Failure:
1499 -- A warning message is issued if the salary in not in the range.
1500 --
1501 -- Access Status
1502 -- Internal Table Handler USe only
1503 --
1504 --
1505 procedure is_salary_in_range
1506 (p_assignment_id in per_pay_proposals.assignment_id%TYPE
1507 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
1508 ,p_change_date in per_pay_proposals.change_date%TYPE
1509 ,p_proposed_salary_n in per_pay_proposals.proposed_salary_n%TYPE
1510 ,p_proposed_salary_warning out nocopy boolean
1511 ) is
1512
1513 l_proc varchar2(70):= g_package || 'is_salary_in_range';
1514 l_organization_id per_all_assignments_f.organization_id%TYPE;
1515 l_pay_basis_id per_all_assignments_f.pay_basis_id%TYPE;
1516 l_position_id per_all_assignments_f.position_id%TYPE;
1517 l_grade_id per_all_assignments_f.grade_id%TYPE;
1518 l_normal_hours per_all_assignments_f.normal_hours%TYPE;
1519 l_frequency per_all_assignments_f.frequency%TYPE;
1520 l_prop_salary_link_warning boolean;
1521 l_prop_salary_ele_warning boolean;
1522 l_prop_salary_grade_warning boolean;
1523 --
1524 cursor csr_asg is
1525 select organization_id
1526 ,pay_basis_id
1527 ,position_id
1528 ,grade_id
1529 ,normal_hours
1530 ,frequency
1531 from per_all_assignments_f
1532 where assignment_id=p_assignment_id
1533 and p_change_date between effective_start_date and effective_end_date;
1534 --
1535 --
1536 begin
1537 hr_utility.set_location('Entering:'|| l_proc, 10);
1538 open csr_asg;
1539 fetch csr_asg into
1540 l_organization_id
1541 ,l_pay_basis_id
1542 ,l_position_id
1543 ,l_grade_id
1544 ,l_normal_hours
1545 ,l_frequency;
1546 close csr_asg;
1547 --
1548 hr_utility.set_location(l_proc, 20);
1549 is_salary_in_range_int
1550 (p_organization_id =>l_organization_id
1551 ,p_pay_basis_id =>l_pay_basis_id
1552 ,p_position_id =>l_position_id
1553 ,p_grade_id =>l_grade_id
1554 ,p_normal_hours =>l_normal_hours
1555 ,p_frequency =>l_frequency
1556 ,p_business_group_id =>p_business_group_id
1557 ,p_change_date =>p_change_date
1558 ,p_proposed_salary_n =>p_proposed_salary_n
1559 ,p_prop_salary_link_warning =>l_prop_salary_link_warning
1560 ,p_prop_salary_ele_warning =>l_prop_salary_ele_warning
1561 ,p_prop_salary_grade_warning =>l_prop_salary_grade_warning
1562 --added by vkodedal bug#8452388
1563 ,p_assignment_id =>p_assignment_id
1564 );
1565 --
1566 hr_utility.set_location(l_proc, 30);
1567 --
1568 p_proposed_salary_warning :=l_prop_salary_link_warning
1569 OR l_prop_salary_ele_warning
1570 OR l_prop_salary_grade_warning;
1571 hr_utility.set_location('Leaving:'|| l_proc, 10);
1572 end is_salary_in_range;
1573 --
1574 -- ----------------------------------------------------------------------------
1575 -- |---------------------< is_salary_in_range_int >---------------------------|
1576 -- ----------------------------------------------------------------------------
1577 --
1578 --
1579 -- Description
1580 -- This is to validate that the given salary is within the grade range
1581 --
1582 -- Pre_condition
1583 -- None
1584 --
1585 -- In Arguments:
1586 -- p_organization_id
1587 -- p_pay_basis_id
1588 -- p_posiiton_id
1589 -- p_grade_id
1590 -- p_normal_hours
1591 -- p_frequency
1592 -- p_business_group_id
1593 -- p_change_date
1594 -- p_proposed_salary_n
1595 --
1596 -- Out Arguments:
1597 -- p_prop_salary_link_warning
1598 -- p_prop_salary_ele_warning
1599 -- p_prop_salary_grade_warning
1600 --
1601 -- Post Success
1602 -- The process continues if:
1603 -- The proposed salary is in the range determined by the grade rate
1604 --
1605 -- Post Failure:
1606 -- A warning message is issued if the salary in not in the range.
1607 --
1608 -- Access Status
1609 -- Internal Table Handler Use only
1610 --
1611 --
1612 procedure is_salary_in_range_int
1613 (p_organization_id in per_all_assignments_f.organization_id%TYPE
1614 ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
1615 ,p_position_id in per_all_assignments_f.position_id%TYPE
1616 ,p_grade_id in per_all_assignments_f.grade_id%TYPE
1617 ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
1618 ,p_frequency in per_all_assignments_f.frequency%TYPE
1619 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
1620 ,p_change_date in per_pay_proposals.change_date%TYPE
1621 ,p_proposed_salary_n in per_pay_proposals.proposed_salary_n%TYPE
1622 ,p_prop_salary_link_warning out nocopy boolean
1623 ,p_prop_salary_ele_warning out nocopy boolean
1624 ,p_prop_salary_grade_warning out nocopy boolean
1625
1626 ) is
1627
1628 l_proc varchar2(70):= g_package || 'is_salary_in_range_int';
1629
1630 begin
1631 --
1632 hr_utility.set_location(l_proc, 20);
1633
1634 is_salary_in_range_int
1635 (p_organization_id =>p_organization_id
1636 ,p_pay_basis_id =>p_pay_basis_id
1637 ,p_position_id =>p_position_id
1638 ,p_grade_id =>p_grade_id
1639 ,p_normal_hours =>p_normal_hours
1640 ,p_frequency =>p_frequency
1641 ,p_business_group_id =>p_business_group_id
1642 ,p_change_date =>p_change_date
1643 ,p_proposed_salary_n =>p_proposed_salary_n
1644 ,p_prop_salary_link_warning =>p_prop_salary_link_warning
1645 ,p_prop_salary_ele_warning =>p_prop_salary_ele_warning
1646 ,p_prop_salary_grade_warning =>p_prop_salary_grade_warning
1647 --added by vkodedal bug#8452388
1648 ,p_assignment_id =>null
1649 );
1650 --
1651 hr_utility.set_location(l_proc, 30);
1652
1653 end is_salary_in_range_int;
1654 --
1655 -- ----------------------------------------------------------------------------
1656 -- |---------------------< is_salary_in_range_int >---------------------------|
1657 -- ----------------------------------------------------------------------------
1658 --
1659 --
1660 -- Description
1661 -- This is to validate that the given salary is within the grade range
1662 --
1663 -- Pre_condition
1664 -- None
1665 --
1666 -- In Arguments:
1667 -- p_organization_id
1668 -- p_pay_basis_id
1669 -- p_posiiton_id
1670 -- p_grade_id
1671 -- p_normal_hours
1672 -- p_frequency
1673 -- p_business_group_id
1674 -- p_change_date
1675 -- p_proposed_salary_n
1676 -- p_assignment_id
1677
1678 -- Out Arguments:
1679 -- p_prop_salary_link_warning
1680 -- p_prop_salary_ele_warning
1681 -- p_prop_salary_grade_warning
1682 --
1683 -- Post Success
1684 -- The process continues if:
1685 -- The proposed salary is in the range determined by the grade rate
1686 --
1687 -- Post Failure:
1688 -- A warning message is issued if the salary in not in the range.
1689 --
1690 -- Access Status
1691 -- Internal Table Handler Use only
1692 --
1693 --
1694 procedure is_salary_in_range_int
1695 (p_organization_id in per_all_assignments_f.organization_id%TYPE
1696 ,p_pay_basis_id in per_all_assignments_f.pay_basis_id%TYPE
1697 ,p_position_id in per_all_assignments_f.position_id%TYPE
1698 ,p_grade_id in per_all_assignments_f.grade_id%TYPE
1699 ,p_normal_hours in per_all_assignments_f.normal_hours%TYPE
1700 ,p_frequency in per_all_assignments_f.frequency%TYPE
1701 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
1702 ,p_change_date in per_pay_proposals.change_date%TYPE
1703 ,p_proposed_salary_n in per_pay_proposals.proposed_salary_n%TYPE
1704 ,p_prop_salary_link_warning out nocopy boolean
1705 ,p_prop_salary_ele_warning out nocopy boolean
1706 ,p_prop_salary_grade_warning out nocopy boolean
1707 --added by vkodedal bug#8452388
1708 ,p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1709 ) is
1710
1711 l_proc varchar2(70):= g_package || 'is_salary_in_range_int';
1712 l_working_hours per_all_assignments_f.normal_hours%TYPE;
1713 l_working_hours_frequency per_all_assignments_f.frequency%TYPE;
1714 l_normal_hours per_all_assignments_f.normal_hours%TYPE;
1715 l_normal_hours_frequency per_all_assignments_f.frequency%TYPE;
1716 l_org_working_hours NUMBER;
1717 l_org_working_hours_frequency per_organization_units.frequency%TYPE;
1718 l_bus_working_hours NUMBER;
1719 l_bus_working_hours_frequency per_business_groups.frequency%TYPE;
1720 l_pyp_working_hours hr_all_positions_f.working_hours%TYPE;
1721 l_pyp_working_hours_frequency hr_all_positions_f.frequency%TYPE;
1722 l_minimum NUMBER;
1723 l_maximum NUMBER;
1724 l_ele_w_or_e pay_input_values_f.warning_or_error%TYPE;
1725 l_ele_min_value pay_input_values_f.min_value%TYPE;
1726 l_ele_max_value pay_input_values_f.max_value%TYPE;
1727 l_link_w_or_e pay_link_input_values_f.warning_or_error%TYPE;
1728 l_link_min_value pay_link_input_values_f.min_value%TYPE;
1729 l_link_max_value pay_link_input_values_f.max_value%TYPE;
1730 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1731 l_pay_basis VARCHAR2(30);
1732 l_grade_basis VARCHAR2(30);
1733 l_annual_salary number;
1734 l_currency_code VARCHAR2(15);
1735 l_uom VARCHAR2(30);
1736 l_ann_minimum number;
1737 l_ann_maximum number;
1738 l_fte_factor number;
1739 l_rgeflg varchar2(1) := 'S';
1740 l_grade_annualization_factor NUMBER;
1741 l_pay_annualization_factor NUMBER;
1742 l_dummy VARCHAR2(200);
1743 l_fte_profile_value VARCHAR2(240) := fnd_profile.VALUE('BEN_CWB_FTE_FACTOR');
1744 --
1745 --
1746 -- define cursor to get ele/link min/max values
1747 --
1748 --Bug: 3026239
1749 --Change Description: Modified the cursor to use fnd_number.canonical_to_number instead of to_number
1750 --Changed by: kgowripe
1751 Cursor csr_get_ele_values is
1752 select iv.warning_or_error,
1753 fnd_number.canonical_to_number(iv.min_value),
1754 fnd_number.canonical_to_number(iv.max_value),
1755 liv.warning_or_error,
1756 fnd_number.canonical_to_number(liv.min_value),
1757 fnd_number.canonical_to_number(liv.max_value)
1758 from pay_link_input_values_f liv,
1759 pay_input_values_f iv,
1760 pay_element_links_f el,
1761 per_pay_bases ppb
1762 where
1763 p_pay_basis_id=ppb.pay_basis_id
1764 and ppb.input_value_id=iv.input_value_id and
1765 p_change_date BETWEEN
1766 iv.effective_start_date AND iv.effective_end_date
1767 and iv.element_type_id = el.element_type_id and
1768 p_change_date BETWEEN
1769 el.effective_start_date AND el.effective_end_date
1770 and liv.element_link_id = el.element_link_id and
1771 liv.input_value_id = iv.input_value_id and
1772 p_change_date BETWEEN
1773 liv.effective_start_date AND liv.effective_end_date;
1774 --
1775 -- Define a cursor to get the working hours and min/max for a grade
1776 --
1777 -- Changes 11-Oct-99 SCNair (per_all_positions to hr_all_positions) Date track pos req.
1778 --
1779 Cursor csr_get_min_max_values is
1780 select p_normal_hours,
1781 p_frequency,
1782 fnd_number.canonical_to_number(O2.ORG_INFORMATION3) working_hours,
1783 O2.ORG_INFORMATION4 frequency,
1784 fnd_number.canonical_to_number(b2.ORG_INFORMATION3) working_hours,
1785 b2.ORG_INFORMATION4 frequency,
1786 fnd_number.canonical_to_number(pgr.minimum),
1787 fnd_number.canonical_to_number(pgr.maximum)
1788 from
1789 hr_all_organization_units bus, HR_ORGANIZATION_INFORMATION b2 ,
1790 hr_all_organization_units org, HR_ORGANIZATION_INFORMATION O2 ,
1791 pay_grade_rules_f pgr,
1792 per_pay_bases ppb
1793 where
1794 org.organization_id = p_organization_id
1795 and org.ORGANIZATION_ID = O2.ORGANIZATION_ID (+)
1796 and O2.ORG_INFORMATION_CONTEXT (+) = 'Work Day Information'
1797 and
1798 pgr.grade_or_spinal_point_id = p_grade_id and
1799 pgr.rate_id = ppb.rate_id and
1800 p_change_date
1801 between pgr.effective_start_date and pgr.effective_end_date
1802 and
1803 ppb.pay_basis_id = p_pay_basis_id
1804 and bus.organization_id = p_business_group_id
1805 and bus.ORGANIZATION_ID = b2.ORGANIZATION_ID (+)
1806 and b2.ORG_INFORMATION_CONTEXT (+) = 'Work Day Information';
1807 --
1808 Cursor csr_get_pos_min_max_values is
1809 select pos.working_hours,
1810 pos.frequency
1811 from hr_all_positions_f pos
1812 where p_position_id = pos.position_id
1813 and p_change_date
1814 BETWEEN pos.effective_start_date AND pos.effective_end_date;
1815 --
1816
1817 CURSOR Currency IS
1818 SELECT PET.INPUT_CURRENCY_CODE
1819 , PPB.PAY_ANNUALIZATION_FACTOR
1820 , PPB.GRADE_ANNUALIZATION_FACTOR
1821 , PPB.PAY_BASIS
1822 , PPB.RATE_BASIS
1823 , PET.ELEMENT_TYPE_ID
1824 , PIV.UOM
1825 FROM PAY_ELEMENT_TYPES_F PET
1826 , PAY_INPUT_VALUES_F PIV
1827 , PER_PAY_BASES PPB
1828 --
1829 WHERE PPB.PAY_BASIS_ID=P_PAY_BASIS_ID
1830 --
1831 AND PPB.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
1832 AND p_change_date BETWEEN
1833 PIV.EFFECTIVE_START_DATE AND
1834 PIV.EFFECTIVE_END_DATE
1835 --
1836 AND PIV.ELEMENT_TYPE_ID=PET.ELEMENT_TYPE_ID
1837 AND p_change_date BETWEEN
1838 PET.EFFECTIVE_START_DATE AND
1839 PET.EFFECTIVE_END_DATE;
1840
1841 --
1842 --
1843 --
1844 begin
1845 hr_utility.set_location('Entering:'|| l_proc, 1);
1846 -- Get the pay_basis details for validating
1847 -- the proposed salary.
1848 open currency;
1849 fetch currency into
1850 l_currency_code
1851 ,l_pay_annualization_factor
1852 ,l_grade_annualization_factor
1853 ,l_pay_basis
1854 ,l_grade_basis
1855 ,l_element_type_id
1856 ,l_uom;
1857
1858 if currency%notfound is null then
1859 close currency;
1860 hr_utility.set_location(l_proc, 5);
1861 hr_utility.set_message(801, 'HR_289855_SAL_ASS_NOT_SAL_ELIG');
1862 hr_utility.raise_error;
1863 --
1864 elsif (l_element_type_id IS NULL) then
1865 --
1866 -- issue an error message if the l_element_type_id is null
1867 --
1868 close currency;
1869 hr_utility.set_location(l_proc, 6);
1870 hr_utility.set_message(801, 'HR_289855_SAL_ASS_NOT_SAL_ELIG');
1871 hr_utility.raise_error;
1872 else
1873 close currency;
1874 end if;
1875 --
1876 -- Now check the proporsed salary to be in appropriate range
1877 --
1878 hr_utility.set_location(l_proc, 7);
1879 open csr_get_min_max_values;
1880 fetch csr_get_min_max_values into
1881 l_normal_hours,
1882 l_normal_hours_frequency,
1883 l_org_working_hours,
1884 l_org_working_hours_frequency,
1885 l_bus_working_hours,
1886 l_bus_working_hours_frequency,
1887 l_minimum,l_maximum;
1888 hr_utility.set_location(l_proc, 10);
1889 if csr_get_min_max_values%notfound then
1890 hr_utility.set_location(l_proc, 11);
1891 close csr_get_min_max_values;
1892 else
1893 open csr_get_pos_min_max_values;
1894 fetch csr_get_pos_min_max_values into
1895 l_pyp_working_hours,
1896 l_pyp_working_hours_frequency;
1897 close csr_get_pos_min_max_values;
1898 --
1899 open csr_get_ele_values;
1900 fetch csr_get_ele_values into l_ele_w_or_e, l_ele_min_value,
1901 l_ele_max_value, l_link_w_or_e, l_link_min_value,
1902 l_link_max_value;
1903 if csr_get_ele_values%notfound then
1904 hr_utility.set_location(l_proc, 12);
1905 end if;
1906 close csr_get_ele_values;
1907 --
1908 hr_utility.set_location(l_proc, 15);
1909 --
1910 if l_pyp_working_hours is null then
1911 if l_org_working_hours is null then
1912 l_working_hours := l_bus_working_hours;
1913 l_working_hours_frequency := l_bus_working_hours_frequency;
1914 hr_utility.set_location(l_proc, 17);
1915 else
1916 l_working_hours := l_org_working_hours;
1917 l_working_hours_frequency := l_org_working_hours_frequency;
1918 hr_utility.set_location(l_proc, 18);
1919 end if;
1920 else
1921 l_working_hours := l_pyp_working_hours;
1922 l_working_hours_frequency := l_pyp_working_hours_frequency;
1923 hr_utility.set_location(l_proc, 19);
1924 end if;
1925 --
1926
1927 --
1928 -- check link min/max
1929 --
1930 if((p_proposed_salary_n < NVL(l_link_min_value,p_proposed_salary_n-1))
1931 or(p_proposed_salary_n > NVL(l_link_max_value,p_proposed_salary_n+1)))
1932 then
1933 hr_utility.set_location(l_proc, 20);
1934 p_prop_salary_link_warning := true;
1935 end if;
1936 --
1937 -- check ele min/max
1938 --
1939 if((p_proposed_salary_n < NVL(l_ele_min_value,p_proposed_salary_n-1))
1940 or (p_proposed_salary_n > NVL(l_ele_max_value,p_proposed_salary_n+1)))
1941 then
1942 hr_utility.set_location(l_proc, 30);
1943 p_prop_salary_ele_warning := true;
1944 end if;
1945 --
1946 -- Now check if the assignment has a grade then the proposed
1947 -- salary is within the range.
1948 --
1949
1950 if (l_minimum IS NOT NULL and l_maximum IS NOT NULL) then
1951 --
1952 -- checks grade rates and pro rates if necessary
1953 --
1954 hr_utility.set_location(l_proc, 40);
1955
1956 --8452388 vkodedal introduced profile option 03-Jun-2009
1957 --8587143 handle null assignment id
1958 if ( p_assignment_id is not null ) then
1959 l_fte_factor := PER_SALADMIN_UTILITY.get_fte_factor(p_assignment_id,p_CHANGE_DATE);
1960 else
1961 if (l_fte_profile_value = 'NHBGWH') then
1962 if (l_working_hours = 0) then
1963 -- if working hours are set to zero then ignore it.
1964 hr_utility.set_location(l_proc, 45);
1965 l_fte_factor:=1;
1966 elsif
1967 (l_working_hours IS NOT NULL)
1968 AND (l_normal_hours IS NOT NULL)
1969 AND NOT((l_pay_basis = 'HOURLY')
1970 AND (l_grade_basis = 'HOURLY'))
1971 AND (l_normal_hours_frequency=l_working_hours_frequency) then
1972 -- if both assignment hours and normal hours are defined then do a comparison
1973 --
1974 hr_utility.set_location(l_proc, 50);
1975 l_fte_factor := l_normal_hours/l_working_hours;
1976 end if;
1977 else
1978 l_fte_factor := 1;
1979 end if;
1980 end if;
1981
1982 l_annual_salary:=p_proposed_salary_n
1983 *nvl(l_pay_annualization_factor,1);
1984
1985 if l_pay_basis <> 'HOURLY' then
1986 --FTE ANNUAL SALARY WILL BE USED FOR COMPARISION WHEN PROFILE IS Y
1987 if NVL(fnd_profile.value('PER_ANNUAL_SALARY_ON_FTE'),'Y') = 'Y' then
1988 l_annual_salary := l_annual_salary / l_fte_factor;
1989 end if;
1990 end if;
1991
1992
1993 l_ann_minimum := l_minimum
1994 *nvl(l_grade_annualization_factor,1);
1995 l_ann_maximum := l_maximum
1996 *nvl(l_grade_annualization_factor,1);
1997
1998 if l_pay_basis = 'HOURLY' and l_grade_basis = 'HOURLY'
1999 then
2000 if( (p_proposed_salary_n < l_minimum) or
2001 (p_proposed_salary_n > l_maximum) ) then
2002 hr_utility.set_location(l_proc, 55);
2003 p_prop_salary_grade_warning := true;
2004 end if;
2005 else
2006 if( (l_annual_salary < l_ann_minimum) or
2007 (l_annual_salary > l_ann_maximum) ) then
2008 hr_utility.set_location(l_proc, 60);
2009 p_prop_salary_grade_warning := true;
2010 end if;
2011 end if;
2012 --
2013 end if;
2014 --
2015 end if;
2016 if csr_get_min_max_values%ISOPEN then
2017 close csr_get_min_max_values;
2018 end if;
2019 hr_utility.set_location('Leaving: ' ||l_proc, 65);
2020 end is_salary_in_range_int;
2021 --
2022 ----------------------------------------------------------------------------
2023 -- |--------------------------< chk_proposed_salary >-----------------------
2024 ----------------------------------------------------------------------------
2025 --
2026 -- Description:
2027 -- - Check that the assignment's salary basis has an associated grade rate.
2028 -- - If so, check if the assignment has a grade
2029 -- - If so, check if the assignment has a rate assoiated with it.
2030 -- - If so, check if the propoosed salary comes within the min and max
2031 -- - specified for the grade and grade rate.
2032 -- - If it doesn't, raise a warning to this effect.
2033 --
2034 -- - Validates that the proposed salary cannot be updated if the overall
2035 -- proposal is approved (i.e. approved ='Y').
2036 --
2037 -- Pre_conditions:
2038 -- A valid change_date
2039 -- A valid business_group_id
2040 -- A valid assignment_id
2041 --
2042 -- In Arguments:
2043 -- p_pay_proprosal_id
2044 -- p_business_group_id
2045 -- p_assignment_id
2046 -- p_change_date
2047 -- p_proposed_salary_n
2048 -- p_object_version_number
2049 -- p_proposed_salary_warning
2050 -- p_multiple_components
2051 -- Post Success:
2052 -- Process continues if :
2053 -- The the assignment's salary basis has no garde assoicated with it or
2054 -- the proposed salary is within the assignment's grade_rate.
2055 -- The proposed salary has a valid currency_code associated with it.
2056 --
2057 --
2058 -- Post Failure:
2059 -- An application error is raised and processing is terminated if any of
2060 -- the following cases are found :
2061 -- - The assignment_id is null.
2062 -- - The change_date is null.
2063 -- - A warning flag is set if the proposed salary is not within min
2064 -- and max of salary basis' grade rate.
2065 --
2066 -- Access Status
2067 -- Internal Table Handler Use Only.
2068 --
2069 procedure chk_proposed_salary
2070 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
2071 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
2072 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
2073 ,p_change_date in per_pay_proposals.change_date%TYPE
2074 ,p_proposed_salary_n in per_pay_proposals.proposed_salary_n%TYPE
2075 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
2076 ,p_proposed_salary_warning out nocopy boolean
2077 -- vkodedal 19-feb-2008
2078 ,p_multiple_components in per_pay_proposals.multiple_components%TYPE
2079 )
2080 is
2081
2082 --
2083 l_proc varchar2(72):= g_package||'chk_proposed_salary';
2084 l_api_updating boolean;
2085 l_proposed_salary_warning boolean;
2086
2087 --
2088 begin
2089 hr_utility.set_location('Entering:'|| l_proc, 1);
2090 --
2091 -- Check mandatory parameters have being set.
2092 --
2093 hr_api.mandatory_arg_error
2094 (p_api_name => l_proc
2095 ,p_argument => 'change_date'
2096 ,p_argument_value => p_change_date
2097 );
2098 --
2099 hr_api.mandatory_arg_error
2100 (p_api_name => l_proc
2101 ,p_argument => 'assignment_id'
2102 ,p_argument_value => p_assignment_id
2103 );
2104 --
2105 hr_api.mandatory_arg_error
2106 (p_api_name => l_proc
2107 ,p_argument => 'business_group_id'
2108 ,p_argument_value => p_business_group_id
2109 );
2110 -- vkodedal 19-feb-2008 p_proposed_salary_n can be null when there are multiple components
2111 if( p_multiple_components <> 'Y' ) then
2112 /*hr_api.mandatory_arg_error
2113 (p_api_name => l_proc
2114 ,p_argument => 'proposed_salary_n'
2115 ,p_argument_value => p_proposed_salary_n
2116 ); */
2117 --smadhuna Bug 7704348 08-jan-2009 User defined error message is to be thrown when proposed_salary_n is null
2118 IF p_proposed_salary_n IS NULL THEN
2119 hr_utility.set_location(l_proc, 2);
2120 hr_utility.set_message(800,'PER_33483_SALARY_NULL');
2121 hr_utility.raise_error;
2122 END IF;
2123 end if;
2124 --
2125 --
2126 -- Only proceed with validation if :
2127 -- a) The current g_old_rec is current and
2128 -- b) The value for proposed_salary_n has changed
2129 --
2130 l_api_updating := per_pyp_shd.api_updating
2131 (p_pay_proposal_id => p_pay_proposal_id
2132 ,p_object_version_number => p_object_version_number);
2133 --
2134 if (l_api_updating AND (nvl(per_pyp_shd.g_old_rec.proposed_salary_n,hr_api.g_number) <>
2135 nvl(p_proposed_salary_n,hr_api.g_number))OR not l_api_updating) then
2136 if p_proposed_salary_n is not null then
2137
2138 --
2139 -- The following check is commented out. As now we are allowing update of Approved Salary Proposals
2140 -- Change made by ggnanagu
2141 --
2142
2143 /* if (l_api_updating)AND(per_pyp_shd.g_old_rec.approved = 'Y' ) then
2144 hr_utility.set_location(l_proc||' proposed = '||to_char(p_proposed_salary_n)||' old = '||to_char(per_pyp_shd.g_old_rec.proposed_salary_n), 2);
2145 hr_utility.set_message(801,'HR_51268_PYP_CANT_UPD_RECORD');
2146 hr_utility.raise_error;
2147 end if; */
2148
2149 is_salary_in_range
2150 (p_assignment_id => p_assignment_id
2151 ,p_business_group_id => p_business_group_id
2152 ,p_change_date => p_change_date
2153 ,p_proposed_salary_n => p_proposed_salary_n
2154 ,p_proposed_salary_warning => l_proposed_salary_warning);
2155 p_proposed_salary_warning := l_proposed_salary_warning;
2156 end if;
2157 end if;
2158 hr_utility.set_location('Leaving: ' ||l_proc, 3);
2159 end chk_proposed_salary;
2160 --
2161 --
2162 ------------------------------------------------------------------------
2163 -- |-----------------< chk_approved >-----------------------------------
2164 ------------------------------------------------------------------------
2165 --
2166 -- Description:
2167 -- Validates that the approved can only have values of 'Y' and 'N'
2168 -- Validates that it is a mandatory column
2169 -- Checks the value of the approved flag is 'Y' for the first emp proposal
2170 -- automatically.
2171 -- Checks the value for an applicants proposal is 'N'
2172 -- Validates that the approved flag can not be set to 'Y' if the proposed
2173 -- salary is null.
2174 -- Validates that when the approved flag is set to 'Y' if some unapproved
2175 -- components then raising a warning message.
2176 -- Validates that the approved falg can not be set to 'N' if the proposal
2177 -- is not the latest proposals.
2178 --
2179 -- Pre_conditions:
2180 -- A valid change_date
2181 -- A valid business_group_id
2182 -- A valid assignment_id
2183 --
2184 -- In Arguments:
2185 -- p_pay_proprosal_id
2186 -- p_business_group_id
2187 -- p_assignment_id
2188 -- p_change_date
2189 -- p_proposed_salary_n
2190 -- p_object_version_number
2191 -- p_approved_warning
2192 --
2193 -- Post Success:
2194 -- Process continues if :
2195 -- The value of the approved is 'Y' or 'N'
2196 -- The proposed salary is not null when approved is set to 'Y'.
2197 --
2198 --
2199 --
2200 -- Post Failure:
2201 -- An application error is raised and processing is terminated if any of
2202 -- the following cases are found :
2203 -- - The assignment_id is null.
2204 -- - The change_date is null.
2205 -- - A warning flag is set if the approved flag is set to yes while
2206 -- - there are some outstanding unapproved components.
2207 --
2208 -- Access Status
2209 -- Internal Table Handler Use Only.
2210 --
2211 procedure chk_approved
2212 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
2213 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
2214 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
2215 ,p_change_date in per_pay_proposals.change_date%TYPE
2216 ,p_proposed_salary_n in per_pay_proposals.proposed_salary_n%TYPE
2217 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
2218 ,p_approved in per_pay_proposals.approved%TYPE
2219 ,p_approved_warning out nocopy boolean
2220 )
2221 is
2222 --
2223 l_proc varchar2(72):= g_package||'chk_approved';
2224 l_exists varchar2(1);
2225 l_api_updating boolean;
2226 l_assignment_type per_all_assignments_f.assignment_type%TYPE;
2227 l_autoApprove varchar2(1);
2228 --
2229 -- Cursor which checks for unapproved components
2230 --
2231 Cursor csr_unapproved_components is
2232 select null
2233 from per_pay_proposal_components
2234 where pay_proposal_id = p_pay_proposal_id
2235 and business_group_id + 0 = p_business_group_id
2236 and approved = 'N';
2237 --
2238 -- Cursor to get the latest proposals
2239 --
2240 Cursor csr_is_first_proposal is
2241 select null
2242 from per_pay_proposals
2243 where assignment_id = p_assignment_id
2244 and business_group_id + 0 = p_business_group_id
2245 and pay_proposal_id<>nvl(p_pay_proposal_id,-1);
2246 --
2247 cursor asg_type is
2248 select assignment_type
2249 from per_all_assignments_f
2250 where assignment_id=p_assignment_id
2251 and p_change_date between
2252 effective_start_date and effective_end_date;
2253
2254 --
2255 -- Define a cursor to check for approved proposals in the future
2256 --
2257 Cursor csr_future_approved_proposals is
2258 select null
2259 from per_pay_proposals
2260 where assignment_id = p_assignment_id
2261 and approved = 'Y'
2262 and change_date > p_change_date;
2263 --
2264 --
2265 begin
2266 hr_utility.set_location('Entering:'|| l_proc, 5);
2267 --
2268 -- Check mandatory parameters have being set.
2269 --
2270 --
2271 --
2272 hr_api.mandatory_arg_error
2273 (p_api_name => l_proc
2274 ,p_argument => 'change_date'
2275 ,p_argument_value => p_change_date
2276 );
2277 --
2278 hr_api.mandatory_arg_error
2279 (p_api_name => l_proc
2280 ,p_argument => 'assignment_id'
2281 ,p_argument_value => p_assignment_id
2282 );
2283 --
2284 hr_api.mandatory_arg_error
2285 (p_api_name => l_proc
2286 ,p_argument => 'business_group_id'
2287 ,p_argument_value => p_business_group_id
2288 );
2289 --
2290 --
2291 hr_api.mandatory_arg_error
2292 (p_api_name => l_proc
2293 ,p_argument => 'approved'
2294 ,p_argument_value => p_approved
2295 );
2296 --
2297 -- Only proceed with validation if :
2298 -- a) The current g_old_rec is current and
2299 -- b) The value for approved has changed
2300 --
2301 l_api_updating := per_pyp_shd.api_updating
2302 (p_pay_proposal_id => p_pay_proposal_id
2303 ,p_object_version_number => p_object_version_number);
2304 --
2305 -- always check whether it is updating or changed or not
2306 -- because the person type may have changed.
2307 --
2308 /* if (l_api_updating AND (per_pyp_shd.g_old_rec.approved <> p_approved)
2309 or not l_api_updating) then */
2310 --
2311 --
2312 -- check that the value of the approved is either 'Y' or 'N'
2313 --
2314 if (p_approved <> 'Y' AND p_approved <> 'N') then
2315 hr_utility.set_location(l_proc, 10);
2316 hr_utility.set_message (801, 'HR_51278_PYP_INVL_APPR_VAL');
2317 hr_utility.raise_error;
2318 end if;
2319 --
2320 --
2321 -- Check that the approved flag cannot be set to 'Y' if the
2322 -- proposed salary is null.
2323 --
2324 if
2325 (p_proposed_salary_n IS NULL AND p_approved = 'Y') then
2326 hr_utility.set_location(l_proc, 20);
2327 hr_utility.set_message(801,'HR_51269_PYP_CANT_APPR_SAL');
2328 hr_utility.raise_error;
2329 end if;
2330
2331 -- Validation Added by ggnanagu
2332 -- If there are approved proposals in the future then this proposal
2333 -- Cannot be in Proposed status
2334
2335 ---changed for Bug 7126872 in order to skip the error for cwb process
2336 if (p_approved = 'N' and NVL(BEN_CWB_POST_PROCESS.g_is_cwb_component_plan,'N') = 'N') THEN
2337 open csr_future_approved_proposals;
2338 fetch csr_future_approved_proposals into l_exists;
2339 if csr_future_approved_proposals%notfound then
2340 hr_utility.set_location(l_proc, 55);
2341 close csr_future_approved_proposals;
2342 else
2343 hr_utility.set_location(l_proc, 60);
2344 close csr_future_approved_proposals;
2345 hr_utility.set_message(801,'HR_FUTURE_APPROVED_PROPOSALS');
2346 hr_utility.raise_error;
2347 end if;
2348 end if;
2349 --
2350 -- Check that the approve flag is correct for the first proposal.
2351 --
2352 if ( p_proposed_salary_n IS NOT NULL) then
2353 open csr_is_first_proposal;
2354 fetch csr_is_first_proposal into l_exists;
2355 if csr_is_first_proposal%notfound then
2356 hr_utility.set_location(l_proc, 30);
2357 close csr_is_first_proposal;
2358 open asg_type;
2359 fetch asg_type into l_assignment_type;
2360 if (asg_type%notfound) then
2361 close asg_type;
2362 hr_utility.set_location(l_proc, 40);
2363 hr_utility.set_message(801,'HR_289855_SAL_ASS_NOT_SAL_ELIG');
2364 hr_utility.raise_error;
2365 else
2366 hr_utility.set_location(l_proc, 50);
2367 close asg_type;
2368 if (l_assignment_type='E' or l_assignment_type='C') then -- a workers 1st proposal must be approved
2369 hr_utility.set_location(l_proc, 55);
2370 if p_approved = 'N' then
2371 --vkodedal 05-Oct-2007 ER to satisfy satutory requirement
2372 --Retain auto approve first proposal functionality if profile is null or set to Yes
2373 l_autoApprove:=fnd_profile.value('HR_AUTO_APPROVE_FIRST_PROPOSAL');
2374 if(l_autoApprove is null or l_autoApprove ='Y') then
2375 hr_utility.set_location(l_proc, 60);
2376 hr_utility.set_message (800,'HR_52513_PYP_FIRST_EMP_NOT_APR');
2377 hr_utility.raise_error;
2378 end if;
2379 end if;
2380 else
2381 if p_approved = 'Y' then -- an applicants first proposal must be unapproved
2382 hr_utility.set_location(l_proc, 70);
2383 hr_utility.set_message (800,'HR_52514_PYP_FIRST_APPL_APR');
2384 hr_utility.raise_error;
2385 end if;
2386 end if;
2387 end if;
2388 else
2389 close csr_is_first_proposal;
2390 end if;
2391 hr_utility.set_location(l_proc, 80);
2392 end if;
2393 --
2394 -- Check that if the approved set to 'Y' and one or more
2395 -- unapproved components exists.
2396 --
2397 open csr_unapproved_components;
2398 fetch csr_unapproved_components into l_exists;
2399 hr_utility.set_location(l_proc,90);
2400 if csr_unapproved_components%found then
2401 hr_utility.set_location(l_proc,100);
2402 p_approved_warning := true;
2403 end if;
2404 close csr_unapproved_components;
2405 --
2406 -- Check that an approved proposal cannot be unapproved.
2407 --
2408 if (l_api_updating AND per_pyp_shd.g_old_rec.approved = 'Y' AND p_approved = 'N') then
2409 hr_utility.set_location(l_proc,110);
2410 hr_utility.set_message(801,'HR_51270_PYP_CANT_UNAPPRO_PRO');
2411 hr_utility.raise_error;
2412 end if;
2413 hr_utility.set_location(l_proc,120);
2414 -- end if;
2415 --
2416 hr_utility.set_location('Leaving: '||l_proc,130);
2417 --
2418 end chk_approved;
2419 --
2420 --
2421 --
2422 --------------------------------------------------------------------------------
2423 --|-----------------------< chk_del_pay_proposal >---------------------------|
2424 --------------------------------------------------------------------------------
2425 --
2426 --
2427 -- Description
2428 -- - Checks that only the last salary proposal can be deleted.
2429 -- - Checks if the proposal has some components then the process fails
2430 -- - If the salary falls below or above the grade min and max as a result
2431 -- - of the
2432 -- deleting an approved proposal, then a warning message is issued to
2433 -- this effect.
2434 --
2435 -- Pre-conditions:
2436 -- A valid pay_proposal_id
2437 --
2438 -- In Arguments:
2439 -- p_pay_proprosal_id
2440 -- p_object_version_number
2441 -- p_salary_warning
2442 --
2443 -- Post Success:
2444 -- Process continues if :
2445 -- The proposal is the last proposal.
2446 --
2447 -- Post Failure:
2448 -- An application error is raised and processing is terminated if any of
2449 -- the following cases are found :
2450 -- - The pay_proposal is null.
2451 -- - The salary proposal is not the latest one.
2452
2453 --
2454 --
2455 procedure chk_del_pay_proposal
2456 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
2457 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
2458 ,p_salary_warning out nocopy boolean
2459 ) is
2460 --
2461 l_proc varchar2(72):= g_package||'chk_del_pay_proposal';
2462 l_exists varchar2(1);
2463 l_proposed_salary per_pay_proposals.proposed_salary_n%TYPE;
2464 l_last_change_date per_pay_proposals.change_date%TYPE;
2465 l_assignment_id per_pay_proposals.assignment_id%TYPE;
2466 l_business_group_id per_pay_proposals.business_group_id%TYPE;
2467 l_change_date per_pay_proposals.change_date%TYPE;
2468 l_approved per_pay_proposals.approved%TYPE;
2469 l_multiple_components per_pay_proposals.multiple_components%TYPE;
2470 --
2471 --
2472 -- Define a cursor to get the proposals details
2473 --
2474 cursor csr_get_pro_detail is
2475 select assignment_id,business_group_id,change_date,
2476 multiple_components,approved
2477 from per_pay_proposals
2478 where pay_proposal_id = p_pay_proposal_id
2479 and object_version_number = p_object_version_number;
2480 --
2481 -- Define a cursor to check for unapproved componnets
2482 --
2483 cursor csr_unapproved_components is
2484 select null
2485 from per_pay_proposal_components
2486 where pay_proposal_id = p_pay_proposal_id
2487 and approved = 'N';
2488 --
2489 -- Define a cursor which gets the latest approved salary_proposal.
2490 --
2491 cursor csr_get_latest_salary is
2492 select proposed_salary_n
2493 from per_pay_proposals
2494 where assignment_id = l_assignment_id
2495 and change_date < l_change_date
2496 order by change_date desc;
2497 --
2498 Cursor csr_is_latest_proposal is
2499 select max(change_date)
2500 from per_pay_proposals
2501 where assignment_id = l_assignment_id;
2502 --
2503 -- Cursor to check that there are components for the proposal
2504 -- Note: If the proposal has some components, the delete process
2505 -- should fail.
2506 --
2507 cursor csr_component_exists is
2508 select null
2509 from per_pay_proposal_components
2510 where pay_proposal_id = p_pay_proposal_id;
2511
2512 --
2513 begin
2514 hr_utility.set_location('Entering:'|| l_proc, 1);
2515 -- get the proposal details first.
2516 --
2517 open csr_get_pro_detail;
2518 fetch csr_get_pro_detail into l_assignment_id,l_business_group_id,
2519 l_change_date,l_multiple_components, l_approved;
2520 if csr_get_pro_detail%notfound then
2521 close csr_get_pro_detail;
2522 hr_utility.set_location(l_proc, 2);
2523 per_pyp_shd.constraint_error('PER_PAY_PROPOSALS_PK');
2524 end if;
2525 close csr_get_pro_detail;
2526 --
2527 -- Check mandatory column from the above cursor are set
2528 --
2529 hr_api.mandatory_arg_error
2530 (p_api_name => l_proc
2531 ,p_argument => 'assignment_id'
2532 ,p_argument_value => l_assignment_id
2533 );
2534 --
2535 hr_api.mandatory_arg_error
2536 (p_api_name => l_proc
2537 ,p_argument => 'business_group_id'
2538 ,p_argument_value => l_business_group_id
2539 );
2540 --
2541 hr_api.mandatory_arg_error
2542 (p_api_name => l_proc
2543 ,p_argument => 'change_date'
2544 ,p_argument_value => l_change_date
2545 );
2546 --
2547 hr_api.mandatory_arg_error
2548 (p_api_name => l_proc
2549 ,p_argument => 'multiple_components'
2550 ,p_argument_value => l_multiple_components
2551 );
2552 --
2553 hr_api.mandatory_arg_error
2554 (p_api_name => l_proc
2555 ,p_argument => 'approved'
2556 ,p_argument_value => l_approved
2557 );
2558 --
2559 --
2560 -- check that the proposal has no components
2561 --
2562 if(l_multiple_components = 'Y') then
2563 open csr_component_exists;
2564 fetch csr_component_exists into l_exists;
2565 if csr_component_exists%found then
2566 close csr_component_exists;
2567 hr_utility.set_location (l_proc,2);
2568 hr_utility.set_message(801, 'HR_51326_PYP_CANT_DEL_MULT_PRO');
2569 hr_utility.raise_error;
2570 end if;
2571 close csr_component_exists;
2572 end if;
2573 --
2574 -- Check that, this is the latest salary proposal
2575 -- i.e. Only the latest salary proposal can be deleted.
2576 --
2577 open csr_is_latest_proposal;
2578 fetch csr_is_latest_proposal into l_last_change_date;
2579 if csr_is_latest_proposal%notfound then
2580 hr_utility.set_location(l_proc,5);
2581 close csr_is_latest_proposal;
2582 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
2583 hr_utility.raise_error;
2584 --
2585 --
2586 -- The following code is commented out by ggnanagu
2587 -- Now its possible to delete any salary proposal
2588 --
2589 /* elsif (l_change_date < l_last_change_date) then
2590 --
2591 -- raise an error. You can only delete the latest proposal
2592 --
2593 hr_utility.set_location(l_proc,10);
2594 close csr_is_latest_proposal;
2595 hr_utility.set_message(801, 'HR_7292_SAL_NOT_LATEST_SAL_REC');
2596 hr_utility.raise_error;*/
2597 --
2598 elsif l_approved = 'Y' then
2599 --
2600 -- Only do the salary range validation check, if we are
2601 -- deleting an approved proposal.
2602 --
2603 open csr_get_latest_salary;
2604 fetch csr_get_latest_salary into l_proposed_salary;
2605 if (csr_get_latest_salary%notfound) then
2606 --
2607 -- This means that there is no other proposals
2608 --
2609 hr_utility.set_location(l_proc, 20);
2610 close csr_get_latest_salary;
2611 --
2612 --
2613 else
2614 --
2615 -- Call the is_salary_in_range procedure for salary range checking.
2616 --
2617 is_salary_in_range
2618 (p_assignment_id => l_assignment_id
2619 ,p_business_group_id => l_business_group_id
2620 ,p_change_date => l_change_date
2621 ,p_proposed_salary_n => l_proposed_salary
2622 ,p_proposed_salary_warning => p_salary_warning);
2623 --
2624 hr_utility.set_location(l_proc, 40);
2625 --
2626 end if;
2627 --
2628 /**** This check should be done at BP level ***/
2629 /***elsif (p_multiple_components = 'Y') then
2630 --
2631 -- check that there is some unapproved components
2632 --
2633 open csr_unapproved_components;
2634 fetch csr_unapproved_components into l_exists;
2635 if csr_unapproved_components%notfound then
2636 hr_utility.set_location(l_proc, 45);
2637 --
2638 -- There is no unapproved components
2639 --
2640 p_components_warning := false;
2641 else
2642 hr_utility.set_location(l_proc, 50);
2643 p_components_warning := true;
2644 end if;
2645 close csr_unapproved_components;
2646 ***/
2647 end if;
2648 if csr_is_latest_proposal%ISOPEN then
2649 close csr_is_latest_proposal;
2650 end if;
2651 hr_utility.set_location('Leaving: ' ||l_proc, 55);
2652 end chk_del_pay_proposal;
2653
2654 -- -----------------------------------------------------------------------
2655 -- |---------------------< chk_date_overlapping >--------------------------|
2656 -- -----------------------------------------------------------------------
2657 --
2658 -- Description:
2659 -- Validates the change_date and date_to.
2660 --
2661 -- Pre-conditions:
2662 --
2663 -- In Arguments:
2664 -- p_change_date and p_date_to
2665 --
2666 -- Post Success:
2667 -- Process continues if :
2668 -- p_change_date <= p_date_to
2669 --
2670 -- Post Failure:
2671 -- An application error is raised and processing is terminated if any of
2672 -- the following cases are found :
2673 -- - p_change_date > p_date_to
2674 --
2675 --
2676 procedure chk_date_overlapping
2677 (p_change_date in per_pay_proposals.change_date%TYPE,
2678 p_date_to in per_pay_proposals.date_to%TYPE) is
2679 --
2680 l_proc varchar2(72):= g_package||'chk_date_overlapping';
2681 --
2682 begin
2683 hr_utility.set_location('Entering:'|| l_proc, 5);
2684 --
2685 --
2686 if(p_change_date > p_date_to ) then
2687 hr_utility.set_location(l_proc, 10);
2688 hr_utility.set_message(800,'PER_PROPOSAL_DATE_OVERLAP');
2689 hr_utility.raise_error;
2690 end if;
2691 hr_utility.set_location(' Leaving:'||l_proc, 20);
2692 end chk_date_overlapping;
2693 --
2694 --
2695 --
2696 -- -----------------------------------------------------------------------
2697 -- |---------------------< chk_forced_ranking >--------------------------|
2698 -- -----------------------------------------------------------------------
2699 --
2700 -- Description:
2701 -- Validates the forced ranking.
2702 --
2703 -- Pre-conditions:
2704 --
2705 -- In Arguments:
2706 -- p_forced_ranking
2707 --
2708 -- Post Success:
2709 -- Process continues if :
2710 -- p_forced_ranking is a positive integer
2711 --
2712 -- Post Failure:
2713 -- An application error is raised and processing is terminated if any of
2714 -- the following cases are found :
2715 -- - p_forced_ranking is less than or equal to 0.
2716 --
2717 --
2718 procedure chk_forced_ranking
2719 (p_forced_ranking in per_pay_proposals.forced_ranking%TYPE) is
2720 --
2721 l_proc varchar2(72):= g_package||'chk_forced_ranking';
2722 --
2723 begin
2724 hr_utility.set_location('Entering:'|| l_proc, 5);
2725 --
2726 --
2727 if(p_forced_ranking <= 0) then
2728 hr_utility.set_location(l_proc, 10);
2729 hr_utility.set_message(800,'HR_52400_PYP_INVALID_RANKING');
2730 hr_utility.raise_error;
2731 end if;
2732 hr_utility.set_location(' Leaving:'||l_proc, 20);
2733 end chk_forced_ranking;
2734 --
2735 --
2736 -- ----------------------------------------------------------------------------
2737 -- |----------------------< chk_performance_review_id >-----------------------|
2738 -- ----------------------------------------------------------------------------
2739 --
2740 -- Description:
2741 -- Validates that the value entered for performance_review_id is valid.
2742 --
2743 -- Pre-conditions:
2744 -- p_assignment_id is valid
2745 --
2746 -- In Arguments:
2747 -- p_pay_proposal_id
2748 -- p_assignment_id
2749 -- p_performance_review_id
2750 -- p_object_version_number
2751 --
2752 -- Post Success:
2753 -- Processing continues if :
2754 -- - The performance_review_id value is valid
2755 --
2756 -- Post Failure:
2757 -- An application error is raised and processing is terminated if any
2758 -- - The performance_review_id value is invalid
2759 --
2760 -- Access Status:
2761 -- Internal Table Handler Use Only.
2762 --
2763 procedure chk_performance_review_id
2764 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
2765 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
2766 ,p_performance_review_id in per_pay_proposals.performance_review_id%TYPE
2767 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
2768 )
2769 is
2770 --
2771 l_proc varchar2(72):= g_package||'chk_performance_review_id';
2772 l_exists varchar2(1);
2773 l_api_updating boolean;
2774 --
2775 --
2776 -- Cursor to check existence of performance_review_id in per_performance_reviews
2777 --
2778 --
2779 cursor csr_chk_performance_review_id is
2780 select null
2781 from per_performance_reviews prv
2782 , per_all_assignments_f asg
2783 where asg.assignment_id = p_assignment_id
2784 and asg.person_id=prv.person_id
2785 and prv.performance_review_id = p_performance_review_id;
2786 --
2787
2788 begin
2789 hr_utility.set_location('Entering:'|| l_proc, 5);
2790 --
2791 -- Check mandatory parameters have being set.
2792 --
2793 hr_api.mandatory_arg_error
2794 (p_api_name => l_proc
2795 ,p_argument => 'assignment_id'
2796 ,p_argument_value => p_assignment_id
2797 );
2798 --
2799 -- Only proceed with validation if :
2800 -- a) The current g_old_rec is current and
2801 -- b) The value for event_id has changed
2802 --
2803 l_api_updating := per_pyp_shd.api_updating
2804 (p_pay_proposal_id => p_pay_proposal_id
2805 ,p_object_version_number => p_object_version_number);
2806 --
2807 if (l_api_updating AND (nvl(per_pyp_shd.g_old_rec.performance_review_id,hr_api.g_number)
2808 <> nvl(p_performance_review_id,hr_api.g_number))
2809 or not l_api_updating) then
2810 hr_utility.set_location(l_proc, 10);
2811 --
2812 --
2813 if (p_performance_review_id IS NOT NULL) then
2814 --
2815 open csr_chk_performance_review_id;
2816 fetch csr_chk_performance_review_id into l_exists;
2817 if csr_chk_performance_review_id%notfound then
2818 hr_utility.set_location(l_proc, 15);
2819 close csr_chk_performance_review_id;
2820 per_pyp_shd.constraint_error('PER_PAY_PROPOSALS_FK4');
2821 end if;
2822 --
2823 close csr_chk_performance_review_id;
2824 end if;
2825 --
2826 end if;
2827 --
2828 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2829 end chk_performance_review_id;
2830 --
2831 -- -----------------------------------------------------------------------
2832 -- |------------------------------< chk_df >-----------------------------|
2833 -- -----------------------------------------------------------------------
2834 --
2835 -- Description:
2836 -- Validates the all Descriptive Flexfield values.
2837 --
2838 -- Pre-conditions:
2839 -- All other columns have been validated. Must be called as the
2840 -- last step from insert_validate and update_validate.
2841 --
2842 -- In Arguments:
2843 -- p_rec
2844 --
2845 -- Post Success:
2846 -- If the Descriptive Flexfield structure column and data values are
2847 -- all valid this procedure will end normally and processing will
2848 -- continue.
2849 --
2850 -- Post Failure:
2851 -- If the Descriptive Flexfield structure column value or any of
2852 -- the data values are invalid then an application error is raised as
2853 -- a PL/SQL exception.
2854 --
2855 -- Access Status:
2856 -- Internal Row Handler Use Only.
2857 --
2858 procedure chk_df
2859 (p_rec in per_pyp_shd.g_rec_type) is
2860 --
2861 l_proc varchar2(72) := g_package||'chk_df';
2862 --
2863 begin
2864 hr_utility.set_location('Entering:'||l_proc, 10);
2865 --
2866 if ((p_rec.pay_proposal_id is not null) and (
2867 nvl(per_pyp_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
2868 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
2869 nvl(per_pyp_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
2870 nvl(p_rec.attribute1, hr_api.g_varchar2) or
2871 nvl(per_pyp_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
2872 nvl(p_rec.attribute2, hr_api.g_varchar2) or
2873 nvl(per_pyp_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
2874 nvl(p_rec.attribute3, hr_api.g_varchar2) or
2875 nvl(per_pyp_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
2876 nvl(p_rec.attribute4, hr_api.g_varchar2) or
2877 nvl(per_pyp_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
2878 nvl(p_rec.attribute5, hr_api.g_varchar2) or
2879 nvl(per_pyp_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
2880 nvl(p_rec.attribute6, hr_api.g_varchar2) or
2881 nvl(per_pyp_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
2882 nvl(p_rec.attribute7, hr_api.g_varchar2) or
2883 nvl(per_pyp_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
2884 nvl(p_rec.attribute8, hr_api.g_varchar2) or
2885 nvl(per_pyp_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
2886 nvl(p_rec.attribute9, hr_api.g_varchar2) or
2887 nvl(per_pyp_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
2888 nvl(p_rec.attribute10, hr_api.g_varchar2) or
2889 nvl(per_pyp_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
2890 nvl(p_rec.attribute11, hr_api.g_varchar2) or
2891 nvl(per_pyp_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
2892 nvl(p_rec.attribute12, hr_api.g_varchar2) or
2893 nvl(per_pyp_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
2894 nvl(p_rec.attribute13, hr_api.g_varchar2) or
2895 nvl(per_pyp_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
2896 nvl(p_rec.attribute14, hr_api.g_varchar2) or
2897 nvl(per_pyp_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
2898 nvl(p_rec.attribute15, hr_api.g_varchar2) or
2899 nvl(per_pyp_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
2900 nvl(p_rec.attribute16, hr_api.g_varchar2) or
2901 nvl(per_pyp_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
2902 nvl(p_rec.attribute17, hr_api.g_varchar2) or
2903 nvl(per_pyp_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
2904 nvl(p_rec.attribute18, hr_api.g_varchar2) or
2905 nvl(per_pyp_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
2906 nvl(p_rec.attribute19, hr_api.g_varchar2) or
2907 nvl(per_pyp_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
2908 nvl(p_rec.attribute20, hr_api.g_varchar2)))
2909 or
2910 (p_rec.pay_proposal_id is null) then
2911 --
2912 -- Only execute the validation if absolutely necessary:
2913 -- a) During update, the structure column value or any
2914 -- of the attribute values have actually changed.
2915 -- b) During insert.
2916 --
2917 if nvl(fnd_profile.value('FLEXFIELDS:VALIDATE_ON_SERVER'),'N') = 'Y'
2918 then
2919 hr_dflex_utility.ins_or_upd_descflex_attribs
2920 (p_appl_short_name => 'PER'
2921 ,p_descflex_name => 'PER_PAY_PROPOSALS'
2922 ,p_attribute_category => p_rec.attribute_category
2923 ,p_attribute1_name => 'ATTRIBUTE1'
2924 ,p_attribute1_value => p_rec.attribute1
2925 ,p_attribute2_name => 'ATTRIBUTE2'
2926 ,p_attribute2_value => p_rec.attribute2
2927 ,p_attribute3_name => 'ATTRIBUTE3'
2928 ,p_attribute3_value => p_rec.attribute3
2929 ,p_attribute4_name => 'ATTRIBUTE4'
2930 ,p_attribute4_value => p_rec.attribute4
2931 ,p_attribute5_name => 'ATTRIBUTE5'
2932 ,p_attribute5_value => p_rec.attribute5
2933 ,p_attribute6_name => 'ATTRIBUTE6'
2934 ,p_attribute6_value => p_rec.attribute6
2935 ,p_attribute7_name => 'ATTRIBUTE7'
2936 ,p_attribute7_value => p_rec.attribute7
2937 ,p_attribute8_name => 'ATTRIBUTE8'
2938 ,p_attribute8_value => p_rec.attribute8
2939 ,p_attribute9_name => 'ATTRIBUTE9'
2940 ,p_attribute9_value => p_rec.attribute9
2941 ,p_attribute10_name => 'ATTRIBUTE10'
2942 ,p_attribute10_value => p_rec.attribute10
2943 ,p_attribute11_name => 'ATTRIBUTE11'
2944 ,p_attribute11_value => p_rec.attribute11
2945 ,p_attribute12_name => 'ATTRIBUTE12'
2946 ,p_attribute12_value => p_rec.attribute12
2947 ,p_attribute13_name => 'ATTRIBUTE13'
2948 ,p_attribute13_value => p_rec.attribute13
2949 ,p_attribute14_name => 'ATTRIBUTE14'
2950 ,p_attribute14_value => p_rec.attribute14
2951 ,p_attribute15_name => 'ATTRIBUTE15'
2952 ,p_attribute15_value => p_rec.attribute15
2953 ,p_attribute16_name => 'ATTRIBUTE16'
2954 ,p_attribute16_value => p_rec.attribute16
2955 ,p_attribute17_name => 'ATTRIBUTE17'
2956 ,p_attribute17_value => p_rec.attribute17
2957 ,p_attribute18_name => 'ATTRIBUTE18'
2958 ,p_attribute18_value => p_rec.attribute18
2959 ,p_attribute19_name => 'ATTRIBUTE19'
2960 ,p_attribute19_value => p_rec.attribute19
2961 ,p_attribute20_name => 'ATTRIBUTE20'
2962 ,p_attribute20_value => p_rec.attribute20
2963 );
2964 end if;
2965 end if;
2966 --
2967 hr_utility.set_location(' Leaving:'||l_proc, 20);
2968 end chk_df;
2969 --
2970 -- ----------------------------------------------------------------------------
2971 -- |---------------------------< insert_validate >----------------------------|
2972 -- ----------------------------------------------------------------------------
2973 --
2974 Procedure insert_validate
2975 (p_rec in out nocopy per_pyp_shd.g_rec_type
2976 ,p_inv_next_sal_date_warning out nocopy boolean
2977 ,p_proposed_salary_warning out nocopy boolean
2978 ,p_approved_warning out nocopy boolean
2979 ,p_payroll_warning out nocopy boolean
2980 ) is
2981 --
2982 l_proc varchar2(72) := g_package||'insert_validate';
2983 l_inv_next_sal_date_warning boolean := false;
2984 l_inv_next_perf_date_warning boolean := false;
2985 l_proposed_salary_warning boolean := false;
2986 l_approved_warning boolean := false;
2987 --
2988 Begin
2989 hr_utility.set_location('Entering:'||l_proc, 5);
2990 --
2991 -- Call all supporting business operations. Mapping to the
2992 -- appropriate Business Rules in perpyp.bru is provided (where
2993 -- relevant)
2994 --
2995 -- Validate business_group id
2996 --
2997 -- Business Rule Mapping
2998 -- =====================
2999 -- Rule CHK_BUSINESS_GROUP_ID a,c
3000 --
3001 hr_api.validate_bus_grp_id(p_rec.business_group_id);
3002 --
3003 hr_utility.set_location(l_proc, 10);
3004 --
3005 --
3006 -- Validate assignment id and change_date
3007 --
3008 -- Business Rule Mapping
3009 -- =====================
3010 -- Rule CHK_ASSIGNMENT_ID /a,c,d,e,f,g,h
3011 -- Rule CHK_CHANGE_DATE /a,b,c
3012 --
3013 -- call to chk_access added for fixing bug#3839734
3014 per_pyp_bus.chk_access(p_assignment_id => p_rec.assignment_id
3015 ,p_change_date => p_rec.change_date);
3016 --
3017 per_pyp_bus.chk_assignment_id_change_date
3018 (p_pay_proposal_id => p_rec.pay_proposal_id
3019 ,p_business_group_id => p_rec.business_group_id
3020 ,p_assignment_id => p_rec.assignment_id
3021 ,p_change_date => p_rec.change_date
3022 ,p_payroll_warning => p_payroll_warning
3023 ,p_object_version_number => p_rec.object_version_number
3024 );
3025 --
3026 hr_utility.set_location(l_proc, 15);
3027
3028 --
3029 --
3030 -- Validate that date_to is not earlier than the start_date
3031 --
3032 --
3033 per_pyp_bus.chk_date_overlapping
3034 (p_change_date => p_rec.change_date
3035 ,p_date_to => p_rec.date_to
3036 );
3037 --
3038 --
3039 --
3040 /*changed for Bug#7386307 as procedure signature is changed--schowdhu*/
3041 per_pyp_bus.validate_date_to
3042 (p_assignment_id => p_rec.assignment_id,
3043 p_pay_proposal_id => p_rec.pay_proposal_id,
3044 p_change_date => p_rec.change_date,
3045 p_date_to => p_rec.date_to,
3046 p_approved => p_rec.approved
3047 );
3048 --
3049 -- Validate proposal_reason
3050 --
3051 -- Business Rule Mapping
3052 -- =====================
3053 -- Rule CHK_PROPOSAL_REASON a
3054 --
3055 per_pyp_bus.chk_proposal_reason
3056 (p_pay_proposal_id => p_rec.pay_proposal_id
3057 ,p_proposal_reason => p_rec.proposal_reason
3058 ,p_change_date => p_rec.change_date
3059 ,p_object_version_number => p_rec.object_version_number
3060 );
3061 --
3062 hr_utility.set_location(l_proc, 20);
3063 --
3064 --
3065 -- Validate multiple_components
3066 --
3067 -- Business Rule Mapping
3068 -- =====================
3069 -- Rule CHK_MULTIPLE_COMPONENTS a,b,c
3070 --
3071 per_pyp_bus.chk_multiple_components
3072 (p_pay_proposal_id => p_rec.pay_proposal_id
3073 ,p_assignment_id => p_rec.assignment_id
3074 ,p_change_date => p_rec.change_date
3075 ,p_multiple_components => p_rec.multiple_components
3076 ,p_object_version_number => p_rec.object_version_number
3077 );
3078 --
3079 hr_utility.set_location(l_proc, 35);
3080 --
3081 --
3082 -- Validate next_sal_review_date and change_date
3083 --
3084 -- Business Rule Mapping
3085 -- =====================
3086 -- Rule CHK_CHG_NEXT_SAL_REVIEW_DATE a
3087 --
3088 per_pyp_bus.chk_chg_next_sal_review_date
3089 (p_pay_proposal_id => p_rec.pay_proposal_id
3090 ,p_business_group_id => p_rec.business_group_id
3091 ,p_assignment_id => p_rec.assignment_id
3092 ,p_change_date => p_rec.change_date
3093 ,p_next_sal_review_date => p_rec.next_sal_review_date
3094 ,p_object_version_number => p_rec.object_version_number
3095 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
3096 );
3097 --
3098 hr_utility.set_location(l_proc, 50);
3099 --
3100 -- Validate next_sal_review_date
3101 --
3102 -- Business Rule Mapping
3103 -- =====================
3104 -- Rule CHK_NEXT_SAL_REVIEW_DATE a
3105 --
3106 per_pyp_bus.chk_next_sal_review_date
3107 (p_pay_proposal_id => p_rec.pay_proposal_id
3108 ,p_business_group_id => p_rec.business_group_id
3109 ,p_assignment_id => p_rec.assignment_id
3110 ,p_change_date => p_rec.change_date
3111 ,p_next_sal_review_date => p_rec.next_sal_review_date
3112 ,p_object_version_number => p_rec.object_version_number
3113 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
3114 );
3115 --
3116 hr_utility.set_location(l_proc, 55);
3117 --
3118 --
3119 -- Validate proposed_salary
3120 --
3121 -- Business Rule Mapping
3122 -- =====================
3123 -- Rule CHK_PROPOSED_SALARY a,c
3124 --
3125 per_pyp_bus.chk_proposed_salary
3126 (p_pay_proposal_id => p_rec.pay_proposal_id
3127 ,p_business_group_id => p_rec.business_group_id
3128 ,p_assignment_id => p_rec.assignment_id
3129 ,p_change_date => p_rec.change_date
3130 ,p_proposed_salary_n => p_rec.proposed_salary_n
3131 ,p_object_version_number => p_rec.object_version_number
3132 ,p_proposed_salary_warning => p_proposed_salary_warning
3133 -- vkodedal 19-feb-2008
3134 ,p_multiple_components => p_rec.multiple_components
3135 );
3136 --
3137 hr_utility.set_location(l_proc, 70);
3138 --
3139 -- Validate approved
3140 --
3141 -- Business Rule Mapping
3142 -- =====================
3143 -- Rule CHK_APPROVED b,c,d,f
3144 --
3145 per_pyp_bus.chk_approved
3146 (p_pay_proposal_id => p_rec.pay_proposal_id
3147 ,p_business_group_id => p_rec.business_group_id
3148 ,p_assignment_id => p_rec.assignment_id
3149 ,p_change_date => p_rec.change_date
3150 ,p_approved => p_rec.approved
3151 ,p_proposed_salary_n => p_rec.proposed_salary_n
3152 ,p_object_version_number => p_rec.object_version_number
3153 ,p_approved_warning => p_approved_warning
3154 );
3155 hr_utility.set_location(l_proc, 75);
3156 --
3157 -- Validate performance_review_id
3158 --
3159 -- Business Rule Mapping
3160 -- =====================
3161 -- Rule CHK_PERFORMANCE_REVIEW_ID a,b
3162 --
3163 per_pyp_bus.chk_performance_review_id
3164 (p_pay_proposal_id => p_rec.pay_proposal_id
3165 ,p_assignment_id => p_rec.assignment_id
3166 ,p_performance_review_id => p_rec.performance_review_id
3167 ,p_object_version_number => p_rec.object_version_number
3168 );
3169 --
3170 hr_utility.set_location(l_proc, 80);
3171 --
3172 -- Validate forced_ranking
3173 --
3174 -- Business Rule Mapping
3175 -- =====================
3176 -- Rule CHK_FORCED_RANKING a
3177 --
3178 per_pyp_bus.chk_forced_ranking
3179 (p_forced_ranking => p_rec.forced_ranking);
3180 --
3181 --
3182 hr_utility.set_location(l_proc, 85);
3183 --
3184 --
3185 -- Call descriptive flexfield validation routines
3186 --
3187 chk_df(p_rec => p_rec);
3188 --
3189 hr_utility.set_location(' Leaving:'||l_proc, 90);
3190 End insert_validate;
3191 --
3192 -- ----------------------------------------------------------------------------
3193 -- |---------------------------< update_validate >----------------------------|
3194 -- ----------------------------------------------------------------------------
3195 Procedure update_validate
3196 (p_rec in out nocopy per_pyp_shd.g_rec_type
3197 ,p_inv_next_sal_date_warning out nocopy boolean
3198 ,p_proposed_salary_warning out nocopy boolean
3199 ,p_approved_warning out nocopy boolean
3200 ,p_payroll_warning out nocopy boolean
3201 ) is
3202
3203 --
3204 l_proc varchar2(72) := g_package||'update_validate';
3205 --
3206 Begin
3207 hr_utility.set_location('Entering:'||l_proc, 5);
3208 --
3209 -- Call all supporting business operations. Mapping to the
3210 -- appropriate Business Rules in perpyp.bru is provided (where
3211 -- relevant)
3212 --
3213 -- Validate business_group id
3214 --
3215 hr_api.validate_bus_grp_id(p_rec.business_group_id);
3216 --
3217 hr_utility.set_location(l_proc, 12);
3218 --
3219 -- Check those columns which cannot be updated
3220 -- have not changed
3221 --
3222 -- Business Rule Mapping
3223 -- =====================
3224 -- CHK_BUSINESS_GROUP_ID /b
3225 -- CHK_ASSIGNMENT_ID /b
3226 -- CHK_PAY_PROPOSAL_ID /c
3227 -- CHK_LAST_CHANGE_DATE /c
3228 --
3229 per_pyp_bus.check_non_updateable_args
3230 (p_rec =>p_rec);
3231 --
3232 hr_utility.set_location (l_proc,10);
3233 --
3234 -- Validate assignment id and change_date
3235 --
3236 -- Business Rule Mapping
3237 -- =====================
3238 -- Rule CHK_ASSIGNMENT_ID /a,c,d,e,f,g,h
3239 -- Rule CHK_CHANGE_DATE /a,b,c
3240 --
3241 -- call to chk_access added for fixing bug#3839734
3242 per_pyp_bus.chk_access(p_assignment_id => p_rec.assignment_id
3243 ,p_change_date => p_rec.change_date);
3244 --
3245 per_pyp_bus.chk_assignment_id_change_date
3246 (p_pay_proposal_id => p_rec.pay_proposal_id
3247 ,p_business_group_id => p_rec.business_group_id
3248 ,p_assignment_id => p_rec.assignment_id
3249 ,p_change_date => p_rec.change_date
3250 ,p_payroll_warning => p_payroll_warning
3251 ,p_object_version_number => p_rec.object_version_number
3252 );
3253 --
3254 hr_utility.set_location (l_proc,12);
3255 --
3256 --
3257 -- Validate that date_to is not earlier than the start_date
3258 --
3259 --
3260 per_pyp_bus.chk_date_overlapping
3261 (p_change_date => p_rec.change_date
3262 ,p_date_to => p_rec.date_to
3263 );
3264 --
3265 --
3266 /* changed for Bug#7386307 as procedure signature is changed--schowdhu */
3267
3268 per_pyp_bus.validate_date_to
3269 (p_assignment_id => p_rec.assignment_id,
3270 p_pay_proposal_id => p_rec.pay_proposal_id,
3271 p_change_date => p_rec.change_date,
3272 p_date_to => p_rec.date_to,
3273 p_approved => p_rec.approved
3274 );
3275 --
3276 --
3277 -- Validate proposal_reason
3278 --
3279 -- Business Rule Mapping
3280 -- =====================
3281 -- Rule CHK_PROPOSAL_REASON a
3282 --
3283 per_pyp_bus.chk_proposal_reason
3284 (p_pay_proposal_id => p_rec.pay_proposal_id
3285 ,p_proposal_reason => p_rec.proposal_reason
3286 ,p_change_date => p_rec.change_date
3287 ,p_object_version_number => p_rec.object_version_number
3288 );
3289 --
3290 hr_utility.set_location(l_proc, 15);
3291 --
3292 -- Validate multiple_components
3293 --
3294 -- Business Rule Mapping
3295 -- =====================
3296 -- Rule CHK_MULTIPLE_COMPONENTS a,c,d
3297 --
3298 per_pyp_bus.chk_multiple_components
3299 (p_pay_proposal_id => p_rec.pay_proposal_id
3300 ,p_assignment_id => p_rec.assignment_id
3301 ,p_change_date => p_rec.change_date
3302 ,p_multiple_components => p_rec.multiple_components
3303 ,p_object_version_number => p_rec.object_version_number
3304 );
3305 --
3306 hr_utility.set_location(l_proc, 20);
3307 --
3308 -- Validate next_sal_review_date and change_date
3309 --
3310 -- Business Rule Mapping
3311 -- =====================
3312 -- Rule CHK_CHG_NEXT_SAL_REVIEW_DATE a
3313 --
3314 per_pyp_bus.chk_chg_next_sal_review_date
3315 (p_pay_proposal_id => p_rec.pay_proposal_id
3316 ,p_business_group_id => p_rec.business_group_id
3317 ,p_assignment_id => p_rec.assignment_id
3318 ,p_change_date => p_rec.change_date
3319 ,p_next_sal_review_date => p_rec.next_sal_review_date
3320 ,p_object_version_number => p_rec.object_version_number
3321 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
3322 );
3323 --
3324 hr_utility.set_location(l_proc, 40);
3325 --
3326 -- Validate next_sal_review_date
3327 --
3328 -- Business Rule Mapping
3329 -- =====================
3330 -- Rule CHK_NEXT_SAL_REVIEW_DATE a
3331 --
3332 per_pyp_bus.chk_next_sal_review_date
3333 (p_pay_proposal_id => p_rec.pay_proposal_id
3334 ,p_business_group_id => p_rec.business_group_id
3335 ,p_assignment_id => p_rec.assignment_id
3336 ,p_change_date => p_rec.change_date
3337 ,p_next_sal_review_date => p_rec.next_sal_review_date
3338 ,p_object_version_number => p_rec.object_version_number
3339 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning
3340 );
3341 --
3342 hr_utility.set_location(l_proc, 45);
3343 --
3344 --
3345 -- Validate proposed_salary
3346 --
3347 -- Business Rule Mapping
3348 -- =====================
3349 -- Rule CHK_PROPOSED_SALARY a,c
3350 --
3351 per_pyp_bus.chk_proposed_salary
3352 (p_pay_proposal_id => p_rec.pay_proposal_id
3353 ,p_business_group_id => p_rec.business_group_id
3354 ,p_assignment_id => p_rec.assignment_id
3355 ,p_change_date => p_rec.change_date
3356 ,p_proposed_salary_n => p_rec.proposed_salary_n
3357 ,p_object_version_number => p_rec.object_version_number
3358 ,p_proposed_salary_warning => p_proposed_salary_warning
3359 -- vkodedal 19-feb-2008
3360 ,p_multiple_components => p_rec.multiple_components
3361 );
3362 --
3363 hr_utility.set_location(l_proc, 60);
3364 --
3365 -- Validate approved
3366 --
3367 -- Business Rule Mapping
3368 -- =====================
3369 -- Rule CHK_APPROVED b,c,e,f
3370 --
3371 per_pyp_bus.chk_approved
3372 (p_pay_proposal_id => p_rec.pay_proposal_id
3373 ,p_business_group_id => p_rec.business_group_id
3374 ,p_assignment_id => p_rec.assignment_id
3375 ,p_change_date => p_rec.change_date
3376 ,p_approved => p_rec.approved
3377 ,p_proposed_salary_n => p_rec.proposed_salary_n
3378 ,p_object_version_number => p_rec.object_version_number
3379 ,p_approved_warning => p_approved_warning
3380 );
3381 --
3382 hr_utility.set_location(l_proc, 65);
3383 --
3384 -- Validate performance_review_id
3385 --
3386 -- Business Rule Mapping
3387 -- =====================
3388 -- Rule CHK_PERFORMANCE_REVIEW_ID
3389 --
3390 per_pyp_bus.chk_performance_review_id
3391 (p_pay_proposal_id => p_rec.pay_proposal_id
3392 ,p_assignment_id => p_rec.assignment_id
3393 ,p_performance_review_id => p_rec.performance_review_id
3394 ,p_object_version_number => p_rec.object_version_number
3395 );
3396 --
3397 hr_utility.set_location(l_proc, 70);
3398 --
3399 -- Validate forced_ranking
3400 --
3401 -- Business Rule Mapping
3402 -- =====================
3403 -- Rule CHK_FORCED_RANKING
3404 --
3405 per_pyp_bus.chk_forced_ranking
3406 (p_forced_ranking => p_rec.forced_ranking);
3407 --
3408 hr_utility.set_location(l_proc, 75);
3409 --
3410 --
3411 -- Call descriptive flexfield validation routines
3412 --
3413 chk_df(p_rec => p_rec);
3414 --
3415 --
3416 hr_utility.set_location(' Leaving:'||l_proc, 85);
3417 End update_validate;
3418 --
3419 -- ----------------------------------------------------------------------------
3420 -- |---------------------------< delete_validate >----------------------------|
3421 -- ----------------------------------------------------------------------------
3422 Procedure delete_validate
3423 (p_rec in per_pyp_shd.g_rec_type
3424 ,p_salary_warning out nocopy boolean
3425 ) is
3426
3427 --
3428 l_proc varchar2(72) := g_package||'delete_validate';
3429 --
3430 Begin
3431 hr_utility.set_location('Entering:'||l_proc, 5);
3432 --
3433 -- Call all supporting business operations
3434 --
3435 -- Validate delete
3436 -- call to chk_access added for fixing bug#3839734
3437 per_pyp_bus.chk_access(p_assignment_id => per_pyp_shd.g_old_rec.assignment_id
3438 ,p_change_date => per_pyp_shd.g_old_rec.change_date);
3439 --
3440 --
3441 -- Business Rule Mapping
3442 -- =====================
3443 -- Rule CHK_del_pay_proposal b,c,d
3444 --
3445 chk_del_pay_proposal
3446 (p_pay_proposal_id => p_rec.pay_proposal_id
3447 ,p_object_version_number => p_rec.object_version_number
3448 ,p_salary_warning => p_salary_warning
3449 );
3450
3451 hr_utility.set_location(' Leaving:'||l_proc, 10);
3452 End delete_validate;
3453 --
3454 -- ---------------------------------------------------------------------------
3455 -- |---------------------< return_legislation_code >-------------------------|
3456 -- ---------------------------------------------------------------------------
3457 --
3458 function return_legislation_code
3459 (p_pay_proposal_id in number
3460 ) return varchar2 is
3461 --
3462 -- Cursor to find legislation code
3463 --
3464 cursor csr_leg_code is
3465 select pbg.legislation_code
3466 from per_business_groups pbg
3467 , per_pay_proposals pyp
3468 where pyp.pay_proposal_id = p_pay_proposal_id
3469 and pbg.business_group_id = pyp.business_group_id;
3470 --
3471 -- Declare local variables
3472 --
3473 l_legislation_code varchar2(150);
3474 l_proc varchar2(72) := 'return_legislation_code';
3475 begin
3476 hr_utility.set_location('Entering:'|| l_proc, 10);
3477 --
3478 -- Ensure that all the mandatory parameter are not null
3479 --
3480 hr_api.mandatory_arg_error(p_api_name => l_proc,
3481 p_argument => 'pay_proposal_id',
3482 p_argument_value => p_pay_proposal_id);
3483 --
3484 if nvl(g_pay_proposal_id, hr_api.g_number) = p_pay_proposal_id then
3485 --
3486 -- The legislation code has already been found with a previous
3487 -- call to this function. Just return the value in the global
3488 -- variable.
3489 --
3490 l_legislation_code := g_legislation_code;
3491 hr_utility.set_location(l_proc, 20);
3492 else
3493 --
3494 -- The ID is different to the last call to this function
3495 -- or this is the first call to this function.
3496 --
3497 open csr_leg_code;
3498 fetch csr_leg_code into l_legislation_code;
3499 if csr_leg_code%notfound then
3500 --
3501 -- The primary key is invalid therefore we must error
3502 --
3503 close csr_leg_code;
3504 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
3505 hr_utility.raise_error;
3506 end if;
3507 hr_utility.set_location(l_proc, 30);
3508 --
3509 -- Set the global variables so the values are
3510 -- available for the next call to this function
3511 --
3512 close csr_leg_code;
3513 g_pay_proposal_id := p_pay_proposal_id;
3514 g_legislation_code := l_legislation_code;
3515 end if;
3516 hr_utility.set_location(' Leaving:'|| l_proc, 40);
3517 --
3518 return l_legislation_code;
3519 end return_legislation_code;
3520 --
3521 end per_pyp_bus;
[Home] [Help]