1 PACKAGE BODY PERWSEPY_PKG AS
2 /* $Header: pepyppkg.pkb 120.5 2008/03/17 09:24:28 schowdhu noship $ */
3 --------------------------------------------------------------------------
4
5 --------------------------------------------------------------------------
6 g_package varchar2(33) := ' PERWSEPY_PKG.'; -- Global package name
7 ---------------------------------------------------------------------------
8
9
10 /* Procedure to check the format of amounts */
11 PROCEDURE CHECK_LENGTH(p_amount IN OUT NOCOPY NUMBER
12 ,p_uom IN VARCHAR2
13 ,p_currcode IN VARCHAR2) IS
14
15 L_PRECISION NUMBER;
16 L_EXT_PRECISION NUMBER;
17 L_MIN_ACCT_UNIT NUMBER;
18
19 l_proc VARCHAR2(100):='PERWSEPY.PKG.CHECK_FORMAT';
20 BEGIN
21 hr_utility.set_location('Entering:'||l_proc, 5);
22 if(p_uom='M') then
23 hr_utility.set_location(l_proc, 10);
24 fnd_currency.get_info(currency_code => p_currcode
25 ,precision => L_PRECISION
26 ,EXT_PRECISION => L_EXT_PRECISION
27 ,MIN_ACCT_UNIT => L_MIN_ACCT_UNIT);
28 hr_utility.set_location(l_proc, 20);
29 p_amount:=round(p_amount,l_precision);
30 else
31 hr_utility.set_location(l_proc, 30);
32 p_amount:=round(p_amount,5);
33 end if;
34 END CHECK_LENGTH;
35 --------------------------------------------------------------------------
36 /* Procedure for calculating the change in percents and amounts and
37 new totals depending on which inputs it gets. Also calculates the
38 multi components flag. */
39
40 PROCEDURE CALCULATE_PERCENTS_P (p_change_amount IN OUT NOCOPY NUMBER,
41 p_change_percent IN OUT NOCOPY NUMBER,
42 p_old_amount IN NUMBER,
43 p_new_amount IN OUT NOCOPY NUMBER,
44 p_multi_components IN OUT NOCOPY VARCHAR2,
45 p_components VARCHAR2) IS
46
47
48 BEGIN
49 --
50 -- if all amounts are null then set the multi components flag to null
51 --
52 IF (p_change_percent IS NULL)
53 AND (p_change_amount IS NULL)
54 AND (p_new_amount IS NULL) THEN
55 p_multi_components:=null;
56 ELSE
57 -- /* Moved to here from below for bug 4424532*/
58 -- if the components are null or open then set them to the desired value
59 --
60 IF (nvl(p_multi_components,'O') = 'O') THEN
61 p_multi_components:=p_components;
62 END IF;
63
64 --
65 -- if change amount and new amount are null then calculate them based on
66 -- the change percent and old amount
67 --
68 IF (p_change_amount IS NULL) and (p_new_amount IS NULL) THEN
69 p_change_percent:=round(p_change_percent,3); -- 5554418
70 p_change_amount:=nvl(p_old_amount,0)*p_change_percent/100;
71 p_new_amount:=nvl(p_old_amount,0)+p_change_amount;
72 --
73 -- if the change percent and new amount are null then calculate them based on
74 -- the change amount and old amount
75 --
76 ELSIF (p_change_percent IS NULL) AND
77 (p_new_amount IS NULL) THEN
78 p_new_amount:=nvl(p_old_amount,0)+p_change_amount;
79 p_change_percent:=round(100*p_change_amount/nvl(p_old_amount,0),3); -- 5554418
80 --
81 -- if the change amount and change percent are null then calculate them based on
82 -- the old amount and the new amount
83 --
84 ELSIF (p_change_amount IS NULL) AND
85 (p_change_percent IS NULL) THEN
86 p_change_amount:=p_new_amount-nvl(p_old_amount,0);
87 p_change_percent:=round(100*p_change_amount/nvl(p_old_amount,0),3); -- 5554418
88 --
89 -- in any other circumstances we have inputed wrong values
90 --
91 ELSE
92 null; -- ADD SOME ERROR
93 END IF;
94
95 END IF;
96
97 EXCEPTION
98 WHEN ZERO_DIVIDE THEN NULL;
99 END CALCULATE_PERCENTS_P;
100 ------------------------------------------------------------------------------
101 /* component_amount_p will calculate the new percentage change of a
102 component for a given new amount. The new total will be calculated
103 elsewhere to stop a loop from WHEN-VALIDTAE-ITEM triggers from
104 change amount and change percent.
105 Multiple components is not set by this, but by MEANING. */
106
107
108 PROCEDURE COMPONENT_AMOUNT_P (p_change_amount IN OUT NOCOPY NUMBER
109 ,p_old_amount IN NUMBER
110 ,p_uom IN VARCHAR2
111 ,p_currcode IN VARCHAR2
112 ,p_change_percent OUT NOCOPY NUMBER) IS
113
114
115 l_percent NUMBER;
116 l_new NUMBER;
117 l_change NUMBER;
118 l_dummy VARCHAR2(255);
119
120 BEGIN
121
122 l_percent := NULL;
123 l_new := NULL;
124 l_change:=p_change_amount;
125
126 CALCULATE_PERCENTS_P(l_change,
127 l_percent,
128 p_old_amount,
129 l_new,
130 l_dummy,
131 'Y');
132
133 CHECK_LENGTH(l_change
134 ,p_uom
135 ,p_currcode);
136
137 p_change_amount:=l_change;
138 p_change_percent:=l_percent;
139
140 END COMPONENT_AMOUNT_P;
141
142 ----------------------------------------------------------
143 /* component_percent_p will calculate the new change amount of a
144 component for a given percentage change if the form status is CHANGED.
145 The new total will be calculated elsewhere to stop a loop from
146 WHEN-VALIDTAE-ITEM triggers from change amount and change percent
147 Multiple components is not set by this, but by MEANING */
148
149 PROCEDURE COMPONENT_PERCENT_P (p_change_percent IN OUT NOCOPY number
150 ,p_old_amount IN NUMBER
151 ,p_change_amount OUT NOCOPY number
152 ,p_status IN VARCHAR2
153 ,p_uom IN VARCHAR2
154 ,p_currcode IN VARCHAR2) IS
155
156 l_change NUMBER;
157 l_new NUMBER;
158 l_percent NUMBER;
159 l_dummy VARCHAR2(255);
160
161 BEGIN
162
163 IF (p_status<>'NEW') THEN
164 l_new := NULL;
165 l_change:=NULL;
166 l_percent:=p_change_percent;
167
168 CALCULATE_PERCENTS_P ( l_change,
169 l_percent,
170 p_old_amount,
171 l_new,
172 l_dummy,
173 'Y');
174 CHECK_LENGTH(l_change
175 ,p_uom
176 ,p_currcode);
177
178 /* l_percent:=null;
179 l_new:=null;
180 CALCULATE_PERCENTS_P ( l_change,
181 l_percent,
182 p_old_amount,
183 l_new,
184 l_dummy,
185 'Y');*/
186 p_change_amount:=l_change;
187 p_change_percent:=l_percent;
188 END IF;
189
190 END COMPONENT_PERCENT_P;
191 ----------------------------------------------------------
192 /* verifies that the new salary lies withing the range allowed by the
193 grade if it exists. The formatted output of the proposed salary
194 is ignored. Should be taken care of by the forms formatting.
195 calculates the change amount and change percent given a new amount */
196
197 PROCEDURE PROPOSED_SALARY_P (p_pay_proposal_id IN NUMBER
198 ,p_business_group_id IN NUMBER
199 ,p_assignment_id IN NUMBER
200 ,p_change_date IN DATE
201 ,p_proposed_salary IN OUT NOCOPY NUMBER
202 ,p_object_version_number IN NUMBER
203 ,p_old_amount IN NUMBER
204 ,p_uom IN VARCHAR2
205 ,p_currcode IN VARCHAR2
206 ,p_components IN OUT NOCOPY VARCHAR2
207 ,p_change_amount OUT NOCOPY NUMBER
208 ,p_change_percent OUT NOCOPY NUMBER) IS
209
210
211 l_change NUMBER;
212 l_percent NUMBER;
213 l_new NUMBER;
214 l_proposed_salary NUMBER;
215 l_comps VARCHAR2(1);
216 l_proposed_salary_warning BOOLEAN;
217 p_multiple_components VARCHAR2(1);
218
219 BEGIN
220
221 l_change:=null;
222 l_percent:=null;
223 l_comps:=p_components;
224 l_proposed_salary:=p_proposed_salary;
225
226 per_pyp_bus.chk_proposed_salary
227 (p_pay_proposal_id => p_pay_proposal_id
228 ,p_business_group_id => p_business_group_id
229 ,p_assignment_id => p_assignment_id
230 ,p_change_date => p_change_date
231 ,p_proposed_salary_n => l_proposed_salary
232 ,p_object_version_number => p_object_version_number
233 ,p_proposed_salary_warning => l_proposed_salary_warning
234 -- schowdhu 17-mar-2008 p_proposed_salary_n changed to include
235 -- p_multiple_components
236 ,p_multiple_components => p_multiple_components);
237
238
239 CALCULATE_PERCENTS_P (l_change,
240 l_percent,
241 p_old_amount,
242 l_proposed_salary,
243 l_comps,
244 'N');
245 CHECK_LENGTH(l_change
246 ,p_uom
247 ,p_currcode);
248
249 p_change_amount:=l_change;
250 p_change_percent:=l_percent;
251 p_components:=l_comps;
252 p_proposed_salary:=l_proposed_salary;
253
254 END PROPOSED_SALARY_P;
255 ----------------------------------------------------------
256 /* calculates change percent and new amount from a given change amount */
257
258 PROCEDURE CHANGE_AMOUNT_P (p_change_amount IN OUT NOCOPY number
259 ,p_old_amount IN NUMBER
260 ,p_components IN OUT NOCOPY VARCHAR2
261 ,p_uom IN VARCHAR2
262 ,p_currcode IN VARCHAR2
263 ,p_new_amount OUT NOCOPY number
264 ,p_change_percent OUT NOCOPY number) IS
265
266 l_change NUMBER;
267 l_percent NUMBER;
268 l_new NUMBER;
269 l_comps VARCHAR2(1);
270 BEGIN
271
272 l_change:=p_change_amount;
273 l_percent:=null;
274 l_new:=null;
275 l_comps:=p_components;
276 CALCULATE_PERCENTS_P (l_change,
277 l_percent,
278 p_old_amount,
279 l_new,
280 l_comps,
281 'N');
282 CHECK_LENGTH(l_change
283 ,p_uom
284 ,p_currcode);
285 p_change_amount:=l_change;
286
287 CHECK_LENGTH(l_new
288 ,p_uom
289 ,p_currcode);
290 p_new_amount:=l_new;
291
292 p_change_percent:=l_percent;
293 p_components:=l_comps;
294
295 END CHANGE_AMOUNT_P;
296 ----------------------------------------------------------
297 /* calculates the change amount and new total from a given change percent */
298
299 PROCEDURE CHANGE_PERCENT_P (p_change_percent IN OUT NOCOPY number
300 ,p_old_amount IN NUMBER
301 ,p_components IN OUT NOCOPY VARCHAR2
302 ,p_uom IN VARCHAR2
303 ,p_currcode IN VARCHAR2
304 ,p_new_amount OUT NOCOPY number
305 ,p_change_amount OUT NOCOPY number) IS
306
307 l_change NUMBER;
308 l_percent NUMBER;
309 l_new NUMBER;
310 l_comps VARCHAR2(1);
311
312 BEGIN
313
314 if (p_old_amount is not null) THEN
315 l_change:=null;
316 l_percent:=p_change_percent;
317 l_new:=null;
318 l_comps:=p_components;
319
320 CALCULATE_PERCENTS_P (l_change,
321 l_percent,
322 p_old_amount,
323 l_new,
324 l_comps,
325 'N');
326
327 CHECK_LENGTH(l_change
328 ,p_uom
329 ,p_currcode);
330 p_change_amount:=l_change;
331
332 CHECK_LENGTH(l_new
333 ,p_uom
334 ,p_currcode);
335 p_new_amount:=l_new;
336 /* l_percent:=null;
337 l_change:=null;
338
339 CALCULATE_PERCENTS_P (l_change,
340 l_percent,
341 p_old_amount,
342 l_new,
343 l_comps,
344 'N');*/
345 p_change_percent:=l_percent;
346 p_components:=l_comps;
347 end if;
348 END CHANGE_PERCENT_P;
349 ---------------------------------------------------------------------
350 --------------------------------------------------------------------------------------
351 /* Following procedure has been copied from per_pyp_bus.
352 Some of the restrictions has been commented in per_pyp_bus as enhancement in FPKRUP.
353 This need to be restriced for the old Salary Form.
354 Change made by abhshriv
355 */
356
357 procedure chk_assignment_id_change_date
358 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
359 ,p_business_group_id in per_pay_proposals.business_group_id%TYPE
360 ,p_assignment_id in per_pay_proposals.assignment_id%TYPE
361 ,p_change_date in per_pay_proposals.change_date%TYPE
362 ,p_payroll_warning out nocopy boolean
363 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
364 )
365 is
366 --
367 l_exists varchar2(1);
368 l_proc varchar2(72) := g_package||'chk_assignment_id_change_date';
369 l_change_date per_pay_proposals.change_date%TYPE;
370
371 --
372 -- Cursor to check the latest proposal change_date for the assignment.
373 --
374 cursor csr_last_change_date is
375 select max(change_date)
376 from per_pay_proposals
377 where assignment_id = p_assignment_id
378 and business_group_id + 0 = p_business_group_id
379 and pay_proposal_id<>nvl(p_pay_proposal_id,-1);
380 --
381 -- Cursor to check whether other proposals exist.
382 --
383 Cursor csr_other_proposals_exist is
384 select null
385 from per_pay_proposals
386 where assignment_id = p_assignment_id
387 and approved = 'N'
388 and pay_proposal_id<>nvl(p_pay_proposal_id,-1);
389 --
390 begin
391 hr_utility.set_location('Entering:'|| l_proc, 1);
392
393 -- The following is commented out in PER_PPC_BUS api as enhancement in FPKRUP.
394 -- New Salary proposals can be added even if future proposals exist.
395 -- This need to be restriced for the old Salary Form.
396 -- Change made by abhshriv
397
398 open csr_last_change_date;
399 fetch csr_last_change_date into l_change_date;
400 if csr_last_change_date%notfound then
401 hr_utility.set_location(l_proc, 35);
402 --
403 elsif
404 l_change_date > p_change_date then
405 hr_utility.set_location(l_proc, 40);
406 close csr_last_change_date;
407 hr_utility.set_message(801,'HR_7293_SAL_FUTURE_APPS_EXIST');
408 hr_utility.raise_error;
409 --
410 end if;
411 close csr_last_change_date;
412
413 -- The following is commented out in PER_PPC_BUS api as relaxation in FPKRUP.
414 -- There can be more than one Unapproved Proposal now
415 -- This need to be restriced for the old Salary Form.
416 -- Change made by abhshriv
417
418 open csr_other_proposals_exist;
419 fetch csr_other_proposals_exist into l_exists;
420 if csr_other_proposals_exist%notfound then
421 hr_utility.set_location(l_proc, 45);
422 close csr_other_proposals_exist;
423 --
424 else
425 close csr_other_proposals_exist;
426 hr_utility.set_location(l_proc, 50);
427 hr_utility.set_message(801, 'HR_7294_SAL_ONLY_ONE_PROPOSAL');
428 hr_utility.raise_error;
429 end if;
430
431 -- The following is commented out in PER_PPC_BUS api as relaxation in FPKRUP.
432 -- The new salary proposal need not have the change_date equals to the
433 -- Salary Basis change date.
434 -- This need to be restriced for the old Salary Form.
435 -- Change made by abhshriv.
436
437 chk_pay_basis_change_date (p_assignment_id,p_change_date);
438 hr_utility.set_location(l_proc, 61);
439
440 hr_utility.set_location('Leaving: ' || l_proc, 65);
441 end chk_assignment_id_change_date;
442 -------------------------------------------------------------------------------
443 -------------------------------------------------------------------------------
444 /* Following procedure has been copied from per_pyp_bus.
445 Some of the restrictions has been commented in per_pyp_bus as enhancement in FPKRUP.
446 This need to be restriced for the old Salary Form.
447 Change made by abhshriv
448 */
449
450 procedure chk_pay_basis_change_date
451 (p_assignment_id in per_pay_proposals.assignment_id%TYPE
452 ,p_change_date in per_pay_proposals.change_date%TYPE
453 ) is
454 --
455 -- Cursor to check that whether there are any pay_basis changes after
456 -- the change_date
457 --
458 cursor csr_asg_pay_bases is
459 select null
460 from per_all_assignments_f asg1
461 where assignment_id = p_assignment_id
462 and exists (select null
463 from per_all_assignments_f asg2
464 where asg2.assignment_id = p_assignment_id
465 and asg1.pay_basis_id +0 <> asg2.pay_basis_id
466 and asg2.effective_start_date > p_change_date
467 and asg1.effective_end_date >= p_change_date);
468 --
469 --
470 l_exists varchar2(1);
471 l_proc varchar2(72) := g_package||'chk_pay_basis_change_date';
472 --
473 begin
474 --
475 hr_utility.set_location('Entering: ' || l_proc,1);
476 --
477 --
478
479 -- The validation for future pay basis changes has been commented in
480 -- PER_PYP_BUS.chk_pay_basis_change_date().
481 -- To enable it from the form that part of the code is copied here.
482 -- Rest of the validation is done in PER_PYP_BUS.
483 -- Change made by abhshriv
484
485 PER_PYP_BUS.chk_pay_basis_change_date(p_assignment_id,p_change_date);
486
487 open csr_asg_pay_bases;
488 fetch csr_asg_pay_bases into l_exists;
489 if csr_asg_pay_bases%found then
490 --
491 -- raise an error if there future pay_basis change in the assignment.
492 --
493 close csr_asg_pay_bases;
494 hr_utility.set_location(l_proc,30);
495 hr_utility.set_message(801,'HR_51718_PYP_FUTU_PAY_BAS_CHG');
496 hr_utility.raise_error;
497 else
498 close csr_asg_pay_bases;
499 hr_utility.set_location(l_proc,31);
500 end if;
501
502
503 hr_utility.set_location('Leaving: ' ||l_proc,35);
504 --
505 end chk_pay_basis_change_date;
506 --------------------------------------------------------------------------------------
507 -------------------------------------------------------------------------------
508 /* Following procedure has been copied from per_pyp_bus.
509 Some of the restrictions has been commented in per_pyp_bus as enhancement in FPKRUP.
510 This need to be restriced for the old Salary Form.
511 Change made by abhshriv
512 */
513 procedure chk_del_pay_proposal
514 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
515 ,p_object_version_number in per_pay_proposals.object_version_number%TYPE
516 ,p_salary_warning out nocopy boolean
517 ) is
518 --
519 l_proc varchar2(72):= g_package||'chk_del_pay_proposal';
520 l_exists varchar2(1);
521 l_proposed_salary per_pay_proposals.proposed_salary_n%TYPE;
522 l_last_change_date per_pay_proposals.change_date%TYPE;
523 l_assignment_id per_pay_proposals.assignment_id%TYPE;
524 l_business_group_id per_pay_proposals.business_group_id%TYPE;
525 l_change_date per_pay_proposals.change_date%TYPE;
526 l_approved per_pay_proposals.approved%TYPE;
527 l_multiple_components per_pay_proposals.multiple_components%TYPE;
528 --
529 --
530 -- Define a cursor to get the proposals change date
531 --
532 cursor csr_get_pro_detail is
533 select assignment_id,business_group_id,change_date,
534 multiple_components,approved
535 from per_pay_proposals
536 where pay_proposal_id = p_pay_proposal_id
537 and object_version_number = p_object_version_number;
538 --
539 -- Define a cursor which gets the latest approved salary_proposal.
540 --
541 Cursor csr_is_latest_proposal is
542 select max(change_date)
543 from per_pay_proposals
544 where assignment_id = l_assignment_id;
545 --
546 begin
547 hr_utility.set_location('Entering:'|| l_proc, 1);
548 --
549 -- get the proposal details first.
550 --
551 open csr_get_pro_detail;
552 fetch csr_get_pro_detail into l_assignment_id,l_business_group_id,
553 l_change_date,l_multiple_components, l_approved;
554 if csr_get_pro_detail%notfound then
555 close csr_get_pro_detail;
556 hr_utility.set_location(l_proc, 2);
557 per_pyp_shd.constraint_error('PER_PAY_PROPOSALS_PK');
558 end if;
559 close csr_get_pro_detail;
560
561 -- Check that, this is the latest salary proposal
562 -- i.e. Only the latest salary proposal can be deleted.
563 --
564 open csr_is_latest_proposal;
565 fetch csr_is_latest_proposal into l_last_change_date;
566 if csr_is_latest_proposal%notfound then
567 hr_utility.set_location(l_proc,5);
568 close csr_is_latest_proposal;
569 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
570 hr_utility.raise_error;
571 elsif (l_change_date < l_last_change_date) then
572 --
573 -- raise an error. You can only delete the latest proposal
574 --
575 hr_utility.set_location(l_proc,10);
576 close csr_is_latest_proposal;
577 hr_utility.set_message(801, 'HR_7292_SAL_NOT_LATEST_SAL_REC');
578 hr_utility.raise_error;
579 end if;
580 close csr_is_latest_proposal;
581
582 hr_utility.set_location('Leaving: ' ||l_proc, 55);
583
584 end chk_del_pay_proposal;
585 -------------------------------------------------------------------------------
586 /* Following procedure has been copied from per_ppc_bus.
587 Some of the restrictions has been commented in per_ppc_bus as enhancement in FPKRUP.
588 This need to be restricted for the old Salary Form.
589 Change made by abhshriv
590 */
591
592 procedure chk_delete_component
593 (p_component_id
594 in per_pay_proposal_components.component_id%TYPE
595 ) is
596 --
597 l_exists varchar2(1);
598 l_proc varchar2(72):= g_package || 'chk_delete_component';
599 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
600 --
601 -- Cursor to check the status of the per_pay-proposal apprved flag.
602 --
603 cursor csr_proposal_status is
604 select null
605 from per_pay_proposals pro
606 where pro.pay_proposal_id = l_pay_proposal_id
607 and pro.approved = 'Y';
608 --
609 -- Cursor to check that this component exists for this proposal
610 --
611 Cursor csr_comp_exists is
612 select pay_proposal_id
613 from per_pay_proposal_components comp
614 where comp.component_id = p_component_id;
615 --
616 --
617 begin
618 hr_utility.set_location('Entering:'|| l_proc, 1);
619 --
620 -- Check that the component exists and get the proposal
621 --
622 open csr_comp_exists;
623 fetch csr_comp_exists into l_pay_proposal_id;
624 if csr_comp_exists%notfound then
625 close csr_comp_exists;
626 hr_utility.set_location(l_proc, 2);
627 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
628 hr_utility.raise_error;
629 end if;
630 close csr_comp_exists;
631 --
632 -- Check that the proposal is not already approved.
633 --
634 open csr_proposal_status;
635 fetch csr_proposal_status into l_exists;
636 if csr_proposal_status%notfound then
637 hr_utility.set_location(l_proc, 2);
638 close csr_proposal_status;
639 else
640 hr_utility.set_location(l_proc, 3);
641 close csr_proposal_status;
642 hr_utility.set_message(801,'HR_51315_PPC_CANT_DEL_RECORD');
643 hr_utility.raise_error;
644 end if;
645 --
646 hr_utility.set_location ('Leaving: ' || l_proc, 4);
647 --
648 end chk_delete_component;
649
650 -------------------------------------------------------------------------------
651
652
653
654 /* validates the new review date and populates the grade
655 and next review date */
656
657 PROCEDURE CHANGE_DATE_P (p_pay_proposal_id IN NUMBER
658 ,p_business_group_id IN NUMBER
659 ,p_assignment_id IN NUMBER
660 ,p_change_date IN DATE
661 ,p_next_sal_review_date IN OUT NOCOPY DATE
662 ,p_object_version_number IN NUMBER
663 ,p_payroll_warning OUT NOCOPY BOOLEAN
664 ,p_inv_next_sal_date_warning OUT NOCOPY BOOLEAN) IS
665
666 l_next_sal_review_date DATE default NULL;
667
668 BEGIN
669
670 /*
671 The following procedure has been copied from per_pyp_bus to PERWSEPY_PKG.
672 The per_pyp_bus has relaxations for retro pay delivered in FPKRUP.
673 This need to be restriced for the old Salary Form.
674 Rest of the validations are done from per_pyp_bus.
675 Change made by abhshriv
676 */
677 chk_assignment_id_change_date
678 (p_pay_proposal_id => p_pay_proposal_id
679 ,p_business_group_id => p_business_group_id
680 ,p_assignment_id => p_assignment_id
681 ,p_change_date => p_change_date
682 ,p_payroll_warning => p_payroll_warning
683 ,p_object_version_number => p_object_version_number);
684
685
686
687 per_pyp_bus.chk_assignment_id_change_date
688 (p_pay_proposal_id => p_pay_proposal_id
689 ,p_business_group_id => p_business_group_id
690 ,p_assignment_id => p_assignment_id
691 ,p_change_date => p_change_date
692 ,p_payroll_warning => p_payroll_warning
693 ,p_object_version_number => p_object_version_number);
694
695 per_pyp_bus.chk_chg_next_sal_review_date
696 (p_pay_proposal_id => p_pay_proposal_id
697 ,p_business_group_id => p_business_group_id
698 ,p_assignment_id => p_assignment_id
699 ,p_change_date => p_change_date
700 ,p_next_sal_review_date => l_next_sal_review_date
701 ,p_object_version_number => p_object_version_number
702 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning);
703
704 p_next_sal_review_date:=l_next_sal_review_date;
705
706 END CHANGE_DATE_P;
707 ---------------------------------------------------------------------
708 /* validates the next performance review date. If none then derives it is appropriate */
709
710 PROCEDURE NEXT_SAL_REVIEW_DATE_P(p_pay_proposal_id NUMBER
711 ,p_business_group_id NUMBER
712 ,p_assignment_id NUMBER
713 ,p_change_date DATE
714 ,p_next_sal_review_date IN OUT NOCOPY DATE
715 ,p_object_version_number NUMBER
716 ,p_inv_next_sal_date_warning OUT NOCOPY BOOLEAN) IS
717
718 l_inv_next_sal_date_warning BOOLEAN;
719 BEGIN
720
721
722 per_pyp_bus.chk_chg_next_sal_review_date
723 (p_pay_proposal_id => p_pay_proposal_id
724 ,p_business_group_id => p_business_group_id
725 ,p_assignment_id => p_assignment_id
726 ,p_change_date => p_change_date
727 ,p_next_sal_review_date => p_next_sal_review_date
728 ,p_object_version_number => p_object_version_number
729 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning);
730
731 per_pyp_bus.chk_next_sal_review_date
732 (p_pay_proposal_id => p_pay_proposal_id
733 ,p_business_group_id => p_business_group_id
734 ,p_assignment_id => p_assignment_id
735 ,p_change_date => p_change_date
736 ,p_next_sal_review_date => p_next_sal_review_date
737 ,p_object_version_number => p_object_version_number
738 ,p_inv_next_sal_date_warning => p_inv_next_sal_date_warning);
739
740 END NEXT_SAL_REVIEW_DATE_P;
741 ---------------------------------------------------------
742 PROCEDURE APPROVED_P(p_pay_proposal_id IN NUMBER
743 ,p_business_group_id IN NUMBER
744 ,p_assignment_id IN NUMBER
745 ,p_change_date IN DATE
746 ,p_proposed_salary IN NUMBER
747 ,p_object_version_number IN NUMBER
748 ,p_approved IN VARCHAR2) IS
749
750 l_approved_warning BOOLEAN;
751
752 BEGIN
753
754 per_pyp_bus.chk_approved
755 (p_pay_proposal_id => p_pay_proposal_id
756 ,p_business_group_id => p_business_group_id
757 ,p_assignment_id => p_assignment_id
758 ,p_change_date => p_change_date
759 ,p_proposed_salary_n => p_proposed_salary
760 ,p_object_version_number => p_object_version_number
761 ,p_approved => p_approved
762 ,p_approved_warning => l_approved_warning);
763
764 /* don't do this approval because it only applies to commited items
765 APPROVED_WARNING_P(l_approved_warning,p_accepted);*/
766
767 END APPROVED_P;
768 ---------------------------------------------------------
769 PROCEDURE COMPONENT_APPROVED_P(p_component_id IN NUMBER
770 ,p_approved IN VARCHAR2
771 ,p_component_reason IN VARCHAR2
772 ,p_change_amount IN NUMBER
773 ,p_change_percentage IN NUMBER
774 ,p_object_version_number IN NUMBER) IS
775 BEGIN
776
777 per_ppc_bus.chk_approved
778 (p_component_id => p_component_id
779 ,p_approved => p_approved
780 ,p_component_reason => p_component_reason
781 ,p_change_amount_n => p_change_amount
782 ,p_change_percentage => p_change_percentage
783 ,p_object_version_number => p_object_version_number);
784
785 END COMPONENT_APPROVED_P;
786 --------------------------------------------------------------------
787 PROCEDURE check_for_unaproved(p_assignment_id NUMBER
788 ,l_error OUT NOCOPY BOOLEAN) IS
789
790 cursor unaproved IS
791 select null
792 from per_pay_proposals_v2
793 where assignment_id=p_assignment_id
794 and approved='N';
795
796 l_dummy NUMBER;
797
798 BEGIN
799
800 open unaproved;
801 fetch unaproved into l_dummy;
802 if unaproved%FOUND THEN
803 l_error:=TRUE;
804 else
805 l_error:=FALSE;
806 end if;
807 close unaproved;
808 END check_for_unaproved;
809 -----------------------------------------------------------------------------------------
810 PROCEDURE CHECK_START_END_ASS_DATES(p_date IN DATE
811 ,p_assignment_id IN NUMBER
812 ,p_start_ass_date_err OUT NOCOPY BOOLEAN
813 ,p_end_ass_date_err OUT NOCOPY BOOLEAN) IS
814
815 cursor start_ass_date is
816 select 1
817 from per_all_assignments_f paf
818 where paf.assignment_id=p_assignment_id
819 and p_date < (select min(paf2.effective_start_date)
820 from per_all_assignments_f paf2
821 where paf2.assignment_id=p_assignment_id);
822
823 cursor end_ass_date is
824 select 1
825 from per_all_assignments_f paf
826 where paf.assignment_id=p_assignment_id
827 and p_date > (select max(paf2.effective_end_date)
828 from per_all_assignments_f paf2
829 where paf2.assignment_id=p_assignment_id);
830 l_dummy NUMBER;
831
832 begin
833
834 open start_ass_date;
835 fetch start_ass_date into l_dummy;
836 if (start_ass_date%FOUND) THEN
837 close start_ass_date;
838 p_start_ass_date_err:=TRUE;
839 else
840 close start_ass_date;
841 end if;
842
843 open end_ass_date;
844 fetch end_ass_date into l_dummy;
845 if (end_ass_date%FOUND) THEN
846 close end_ass_date;
847 p_end_ass_date_err:=TRUE;
848 else
849 close end_ass_date;
850 end if;
851
852 END CHECK_START_END_ASS_DATES;
853 ---------------------------------------------------------
854
855 END PERWSEPY_PKG;
[Home] [Help]