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