[Home] [Help]
PACKAGE BODY: APPS.PER_PPC_BUS
Source
1 Package Body per_ppc_bus as
2 /* $Header: peppcrhi.pkb 120.4 2006/08/16 14:04:23 abhshriv noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ppc_bus.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |----------------------< check_non_updateable_args >-----------------------|
13 -- ----------------------------------------------------------------------------
14 -- {Start Of Comments}
15 --
16 -- Description:
17 -- This procedure is used to ensure that non updatetable attributes have
18 -- not been updated. If an attribute has been updated an error is generated.
19 --
20 -- Pre Conditions:
21 -- None
22 --
23 -- In Arguments:
24 -- p_rec has been populated with the updated values the user would like the
25 --
26 -- Post Success:
27 -- Processing continues if all the non updateable attributes have not
28 -- changed.
29 --
30 -- Post Failure:
31 -- An application error is raised if any of the non updatable attributes
32 -- (business_group_id, pay_proposal_id or component_id) have been altered.
33 --
34 -- Access Status:
35 -- Internal Table Handler Use Only.
36 --
37 --
38 --
39 --
40 Procedure check_non_updateable_args(p_rec in per_ppc_shd.g_rec_type) is
41 --
42 l_proc varchar2(72) := g_package||'check_non_updateable_args';
43 l_error exception;
44 l_argument varchar2(30);
45 --
46 Begin
47 hr_utility.set_location('Entering:'||l_proc, 5);
48 --
49 -- Only proceed with validation if a row exists for
50 -- the current record in the HR Schema
51 --
52 if not per_ppc_shd.api_updating
53 (p_component_id => p_rec.component_id
54 ,p_object_version_number => p_rec.object_version_number
55 ) then
56 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
57 hr_utility.set_message_token('PROCEDURE', l_proc);
58 hr_utility.set_message_token('STEP', '5');
59 end if;
60 hr_utility.set_location(l_proc, 6);
61 --
62 if p_rec.business_group_id <> per_ppc_shd.g_old_rec.business_group_id
63 then
64 l_argument := 'business_group_id';
65 raise l_error;
66 end if;
67 hr_utility.set_location(l_proc, 7);
68 --
69 if p_rec.pay_proposal_id <> per_ppc_shd.g_old_rec.pay_proposal_id then
70 l_argument := 'pay_proposal_id';
71 raise l_error;
72 end if;
73 hr_utility.set_location(l_proc, 8);
74 --
75 if p_rec.component_id <> per_ppc_shd.g_old_rec.component_id then
76 l_argument := 'component_id';
77 raise l_error;
78 end if;
79 hr_utility.set_location(l_proc, 9);
80 --
81 exception
82 when l_error then
83 hr_api.argument_changed_error
84 (p_api_name => l_proc
85 ,p_argument => l_argument);
86 when others then
87 raise;
88 hr_utility.set_location(' Leaving:'||l_proc, 10);
89 end check_non_updateable_args;
90 --
91 --
92 -- ----------------------------------------------------------------
93 -- |------------------------< chk_access >------------------------|
94 -- ----------------------------------------------------------------
95 -- Description
96 --
97 -- This procedure checks whether the assignment id exists as of the
98 -- change_date of the proposal.
99 --
100 -- Pre_conditions:
101 -- A valid pay_proposal
102 --
103 -- In Arguments:
104 -- p_pay_proposal_id
105 --
106 -- Post Success:
107 -- Process continues if the assignment id is valid
108 --
109 -- Post Failure:
110 -- Processing stops after raising appropriate Error Message
111 --
112 -- Access Status
113 -- Internal Table Handler Use Only.
114 --
115 procedure chk_access
116 (p_pay_proposal_id in per_pay_proposals.pay_proposal_id%TYPE
117 ) is
118 --
119 -- Declare local variables
120 --
121 l_proc varchar2(72) := g_package||'chk_access';
122 l_exists varchar2(1);
123 --
124 -- Cursor to check access to the assignment record
125 --
126 cursor csr_asg_sec is
127 select null
128 from per_pay_proposals pyp,
129 per_assignments_f2 asg
130 where pyp.pay_proposal_id = p_pay_proposal_id
131 and pyp.assignment_id = asg.assignment_id
132 and pyp.change_date between asg.effective_start_date
133 and asg.effective_end_date;
134 begin
135 hr_utility.set_location('Entering:'|| l_proc, 10);
136 --
137 -- Always perform this validation on update and delete
138 -- even although the assignment_id value cannot be changed.
139 --
140 open csr_asg_sec;
141 fetch csr_asg_sec into l_exists;
142 if csr_asg_sec%notfound then
143 close csr_asg_sec;
144 fnd_message.set_name('PER', 'PER_SAL_ASG_NOT_EXIST');
145 fnd_message.raise_error;
146 end if;
147 close csr_asg_sec;
148 hr_utility.set_location(' Leaving:'|| l_proc, 30);
149 end chk_access;
150
151 --
152 --
153 -------------------------------------------------------------------------------
154 -------------------------------< chk_pay_proposal_id >-------------------------
155 -------------------------------------------------------------------------------
156 --
157 --
158 -- Description:
159 -- - Validates that the pay_proposal_id exists in per_pay_proposals
160 -- - Checks that the pay_proposal_id is not null
161 -- if p_validate is not 'WEAK' then it also
162 -- - Validates that the multiple_components flag in per_pay_proposals
163 -- is not set to 'N'
164 -- - Validates that the approved flag in per_pay_proposals table is not
165 -- set to 'Y'.
166 --
167 -- Pre_conditions:
168 -- A valid business_group_id
169 --
170 -- In Arguments:
171 -- p_component_id
172 -- p_pay_proposal_id
173 -- p_business_group_id
174 -- p_object_version_number
175 -- p_validation_strength
176 --
177 -- Post Success:
178 -- Process continues if :
179 -- - The pay_proposal_id exists and
180 -- - The multiple_components flag is not set to 'N' and
181 -- - The approved flag is not set to 'Y'
182 --
183 -- Post Failure:
184 -- An application error is raised and processing is terminated if any of
185 -- the following cases are found :
186 -- - The pay_proposal_id not found
187 -- - The multiple_components flag in per_pay_proposal is set to 'N'
188 -- - The approved flag is set to 'Y'
189 --
190 -- Access Status:
191 -- Internal Table Handler Use Only.
192 --
193 --
194 procedure chk_pay_proposal_id
195 (p_component_id
196 in per_pay_proposal_components.component_id%TYPE
197 ,p_pay_proposal_id
198 in per_pay_proposal_components.pay_proposal_id%TYPE
199 ,p_business_group_id
200 in per_pay_proposal_components.business_group_id%TYPE
201 ,p_object_version_number
202 in per_pay_proposal_components.object_version_number%TYPE
203 ,p_validation_strength in varchar2 default 'STRONG')is
204 --
205 l_proc varchar2(72) := g_package||'chk_pay_proposal_id';
206 l_exists varchar2(1);
207 l_api_updating boolean;
208 l_approved per_pay_proposals.approved%TYPE;
209 l_multiple_components per_pay_proposals.multiple_components%TYPE;
210 l_business_group_id per_pay_proposals.business_group_id%TYPE;
211 --
212 --
213 -- Cursor to check for valid pay_proposal_id and gets the value
214 -- of the approved and multiple_components flag
215 --
216 cursor csr_pay_proposal_details is
217 select pro.approved, pro.multiple_components,pro.business_group_id
218 from per_pay_proposals pro
219 where pro.pay_proposal_id = p_pay_proposal_id;
220 --
221 --
222 begin
223 hr_utility.set_location('Entering:'|| l_proc, 1);
224 --
225 -- Check mandatory parameters have been set
226 --
227 hr_api.mandatory_arg_error
228 (p_api_name => l_proc
229 ,p_argument => 'business_group_id'
230 ,p_argument_value => p_business_group_id
231 );
232 --
233 hr_api.mandatory_arg_error
234 (p_api_name => l_proc
235 ,p_argument => 'pay_proposal_id'
236 ,p_argument_value => p_pay_proposal_id
237 );
238 --
239 -- This following checks is done regardless of insert
240 -- or update mode due to master-detail relationship
241 -- between proposal and component .
242 --
243 hr_utility.set_location(l_proc, 2);
244 --
245 -- Check the pay_proposal_id and the value of the approved and
246 -- multiple_components flag.
247 --
248 open csr_pay_proposal_details;
249 fetch csr_pay_proposal_details into l_approved,
250 l_multiple_components,l_business_group_id;
251 if csr_pay_proposal_details%notfound then
252 close csr_pay_proposal_details;
253 hr_utility.set_location(l_proc, 3);
254 per_ppc_shd.constraint_error('PER_PAY_PROPOSAL_COMPONENT_FK1');
255 elsif
256 l_business_group_id <> p_business_group_id then
257 --
258 -- The component exists for a proposal in diferent Business Group.
259 --
260 close csr_pay_proposal_details;
261 hr_utility.set_location(l_proc, 4);
262 per_ppc_shd.constraint_error('PER_PAY_PROPOSAL_COMPONENT_FK2');
263 else
264 close csr_pay_proposal_details;
265 /*
266 if l_approved = 'Y' and p_validation_strength <>'WEAK' then
267 hr_utility.set_location(l_proc, 5);
268 hr_utility.set_message (801,'HR_51311_PPC_CANT_INS_OR_UPD');
269 hr_utility.raise_error;
270 --
271 els */ -- allow component update.
272 if l_multiple_components = 'N' and p_validation_strength <>'WEAK' then
273 hr_utility.set_location(l_proc, 6);
274 hr_utility.set_message (801,'HR_51312_PPC_COMP_NOT_ALLOWED');
275 hr_utility.raise_error;
276 end if;
277 --
278 end if;
279 --
280 hr_utility.set_location('Leaving: ' || l_proc, 7);
281 end chk_pay_proposal_id;
282 --
283 --
284 ----------------------------------------------------------------------
285 -- |---------------< chk_approved >-----------------------------------
286 ----------------------------------------------------------------------
287 --
288 -- Description:
289 -- - Validates that it cannot be set to 'Y' if the change amount
290 -- or change_percentage is null.
291 --
292 -- Pre-condition
293 -- A valid pay_proposal_id
294 -- The Change_amount and the Change_percentage have been validated
295 -- or derived as appropriate.
296 --
297 -- In arguments:
298 -- p_component_id
299 -- p_change_amount_n
300 -- p_change_percentage
301 -- p_component_reason
302 -- p_object_version_number
303 --
304 -- Post_success
305 -- Process continues if:
306 -- The change_amount_n and change_percentage are not null.
307 --
308 -- Post-Failure:
309 -- An application error is raised and processing is terminated
310 -- if any of the following cases are found :
311 -- - The change_amount_n or change_percentage are null.
312 --
313 -- Access Status
314 -- Internal Table Handler Use Only.
315 --
316 --
317 --
318 procedure chk_approved
319 (p_component_id
320 in per_pay_proposal_components.component_id%TYPE
321 ,p_approved
322 in per_pay_proposal_components.approved%TYPE
323 ,p_component_reason
324 in per_pay_proposal_components.component_reason%TYPE
325 ,p_change_amount_n
326 in per_pay_proposal_components.change_amount_n%TYPE
327 ,p_change_percentage
328 in per_pay_proposal_components.change_percentage%TYPE
329 ,p_object_version_number
330 in per_pay_proposal_components.object_version_number%TYPE
331 ) is
332 --
333 l_proc varchar2(72):= g_package||'chk_approved';
334 l_exists varchar2(1);
335 l_api_updating boolean;
336 --
337 --
338 begin
339 hr_utility.set_location('Entering:'|| l_proc, 1);
340 --
341 hr_api.mandatory_arg_error
342 (p_api_name => l_proc
343 ,p_argument => 'approved'
344 ,p_argument_value => p_approved
345 );
346 --
347 hr_api.mandatory_arg_error
348 (p_api_name => l_proc
349 ,p_argument => 'component_reason'
350 ,p_argument_value => p_component_reason
351 );
352 --
353 -- Only proceed with validation if :
354 -- a) The current g_old_rec is current and
355 -- b) The value for approved flag has changed
356 --
357 l_api_updating := per_ppc_shd.api_updating
358 (p_component_id => p_component_id
359 ,p_object_version_number => p_object_version_number);
360 --
361 if (l_api_updating AND
362 (per_ppc_shd.g_old_rec.approved <> p_approved)
363 OR not l_api_updating)
364 then
365 --
366 -- check that the value of the approved is either 'Y' or 'N'
367 --
368 if (p_approved <> 'Y' AND p_approved <> 'N') then
369 hr_utility.set_location(l_proc, 2);
370 per_ppc_shd.constraint_error ('PER_PPC_APPROVED_CHK');
371 end if;
372 --
373 -- Check that a component cannot be approved if the
374 -- change_amount_n or component_reason is null
375 -- CHANGED to allow change percentage to be null. This would be
376 -- the case if the previously approved salary was 0.00.
377 --
378 hr_utility.set_location(l_proc,3);
379 if (p_approved = 'Y') then
380 if (p_change_amount_n IS NULL)
381 then
382 hr_utility.set_location(l_proc, 4);
383 hr_utility.set_message(801,'HR_51269_PYP_CANT_APPR_SAL');
384 hr_utility.raise_error;
385 end if;
386 --
387 if (p_component_reason IS NULL) then
388 hr_utility.set_location(l_proc, 5);
389 hr_utility.set_message (801, 'HR_51318_PPC_CANT_APP_COMP');
390 hr_utility.raise_error;
391 end if;
392 end if;
393 --
394 end if;
395 --
396 hr_utility.set_location('Leaving: ' || l_proc, 4);
397 --
398 end chk_approved;
399 --
400 --
401 -- ---------------------------------------------------------------------
402 -- |-----------------------< chk_component_reason >---------------------|
403 -- ----------------------------------------------------------------------
404 --
405 --
406 -- Description:
407 -- Validates the value entered for component_reason exists on hr_lookups.
408 -- Validates that the component reason is not null
409 -- Validates that the component reason is unique among the component
410 -- reason for this salary proposal
411 -- Validates that the component reason cannot be updated if the approved
412 -- flag is 'Y'
413 --
414 --
415 -- Pre-conditions:
416 -- A valid pay_proposal_id
417 --
418 -- In Arguments:
419 -- p_component_id
420 -- p_pay_proposal_id
421 -- p_component_reason
422 -- p_approved
423 -- p_object_version_number
424 --
425 -- Post Success:
426 -- Processing continues if :
427 -- - The component_reason value is valid and is unique.
428 -- - The approved flag is not set to 'Y' if updating.
429 --
430 -- Post Failure:
431 -- An application error is raised and processing is terminated if any
432 -- - The component_reason value is invalid or duplicated.
433 --
434 -- Access Status
435 -- Internal Table Handler Use Only.
436 --
437 --
438 procedure chk_component_reason
439 (p_component_id in per_pay_proposal_components.component_id%TYPE
440 ,p_pay_proposal_id in per_pay_proposal_components.pay_proposal_id%TYPE
441 ,p_component_reason in per_pay_proposal_components.component_reason%TYPE
442 ,p_approved in per_pay_proposal_components.approved%TYPE
443 ,p_object_version_number
444 in per_pay_proposal_components.object_version_number%TYPE
445 )
446 is
447 --
448 l_proc varchar2(72):= g_package||'chk_component_reason';
449 l_exists varchar2(1);
450 l_api_updating boolean;
451 l_change_date date;
452 l_sal_pro_approved varchar2(10);
453 --
454 -- Cursor to check that the component reason is unique.
455 --
456 cursor csr_unique_comp_reason is
457 select null
458 from per_pay_proposal_components ppc
459 where ppc.pay_proposal_id = p_pay_proposal_id
460 and ppc.component_reason = p_component_reason;
461 --
462 --
463 -- cursor to get the change_date from the main proposal.
464 -- this date is used in the lookup (i.e. New standard).
465 --
466 cursor csr_get_date is
467 select change_date, approved
468 from per_pay_proposals
469 where pay_proposal_id = p_pay_proposal_id;
470 --
471 begin
472 hr_utility.set_location('Entering:'|| l_proc, 1);
473 open csr_get_date;
474 fetch csr_get_date into l_change_date, l_sal_pro_approved;
475 if csr_get_date%notfound then
476 close csr_get_date;
477 hr_utility.set_message(801,'HR_51310_PPC_INVAL_PRO_ID');
478 hr_utility.raise_error;
479 end if;
480 close csr_get_date;
481 --
482 -- Check mandatory parameters have been set
483 --
484 hr_api.mandatory_arg_error
485 (p_api_name => l_proc
486 ,p_argument => 'pay_proposal_id'
487 ,p_argument_value => p_pay_proposal_id
488 );
489 --
490 hr_api.mandatory_arg_error
491 (p_api_name => l_proc
492 ,p_argument => 'approved'
493 ,p_argument_value => p_approved
494 );
495 --
496 hr_api.mandatory_arg_error
497 (p_api_name => l_proc
498 ,p_argument => 'component_reason'
499 ,p_argument_value => p_component_reason
500 );
501 --
502 --
503 -- Only proceed with validation if :
504 -- a) The current g_old_rec is current and
505 -- b) The value for component_reason has changed
506 --
507 l_api_updating := per_ppc_shd.api_updating
508 (p_component_id => p_component_id
509 ,p_object_version_number => p_object_version_number);
510 --
511 -- if (l_api_updating) then
512 --
513 -- Check that the component reason cannot be updated if the
514 -- the component is already approved.
515 --
516 -- if (per_ppc_shd.g_old_rec.approved = 'Y' AND p_approved = 'Y' AND
517 -- per_ppc_shd.g_old_rec.component_reason <> p_component_reason) then
518 -- hr_utility.set_location(l_proc, 2);
519 -- hr_utility.set_message(801,'HR_51268_PYP_CANT_UPD_RECORD');
520 -- hr_utility.raise_error;
521 -- end if;
522 -- end if;
523 --
524 -- Check if sal proposal is approved
525 -- then component should be approved.
526 --
527 if ( l_sal_pro_approved = 'Y' AND p_approved <>'Y' ) then
528 hr_utility.set_location(l_proc, 2);
529 hr_utility.set_message(800,'PER_SAL_APRVD_COMP_NOT_APRVD');
530 hr_utility.raise_error;
531 end if;
532 --
533 if (l_api_updating AND
534 (per_ppc_shd.g_old_rec.component_reason <> p_component_reason)
535 OR not l_api_updating) then
536 --
537 -- Check that the component reason cannot be updated if the
538 -- the component is already approved.
539 --
540 -- if (per_ppc_shd.g_old_rec.approved = 'Y' AND p_approved = 'Y'
541 -- AND l_api_updating) then
542 -- hr_utility.set_location(l_proc, 2);
543 -- hr_utility.set_message(801,'HR_51268_PYP_CANT_UPD_RECORD');
544 -- hr_utility.raise_error;
545 -- end if;
546 --
547 -- check that the p_component_reason exists in hr_lookups.
548 --
549 if hr_api.not_exists_in_hr_lookups
550 (p_effective_date => l_change_date
551 ,p_lookup_type => 'PROPOSAL_REASON'
552 ,p_lookup_code => p_component_reason
553 ) then
554 -- Error: Invalid proposal_reason
555 hr_utility.set_location(l_proc, 10);
556 hr_utility.set_message(801,'HR_51265_INVAL_PRO_REASON');
557 hr_utility.raise_error;
558 end if;
559 --
560 -- Check whether the component reason is unique.
561 --
562 open csr_unique_comp_reason;
563 fetch csr_unique_comp_reason into l_exists;
564 if csr_unique_comp_reason%notfound then
565 hr_utility.set_location(l_proc, 5);
566 close csr_unique_comp_reason;
567 else
568 hr_utility.set_location(l_proc, 6);
569 close csr_unique_comp_reason;
570 per_ppc_shd.constraint_error ('PER_PAY_PROPOSAL_COMPONENT_UK2');
571 end if;
572 end if;
573 --
574 hr_utility.set_location('Leaving: ' || l_proc, 7);
575 --
576 end chk_component_reason;
577 --
578 --
579 --
580 -- ------------------------------------------------------------------
581 -- |-------------< chk_change_amount_percentage >--------------------|
582 -- -------------------------------------------------------------------
583 --
584 --
585 -- Description:
586 -- Derives the value of change_amount_n or change_percentage if the
587 -- change_percentage or change_amount_n is inserted respectively.
588 -- Checks that the rercord cannot be updated if the approved falg is set
589 -- to 'Y'.
590 -- If both of these values are provided, then the change_percentage
591 -- is recalculated from the change_amount_n.
592 -- Round the change_percentage by 3.
593 --
594 -- Pre-conditions:
595 -- A valid pa_proposal_id.
596 --
597 -- In Arguments:
598 -- p_component_id
599 -- p_pay_proposal_id
600 -- p_change_amount_n
601 -- p_change_percentage
602 -- p_approved
603 -- p_object_version_number
604 --
605 -- Post Success:
606 -- Processing continues if
607 -- - The approved flag is not set to 'Y' if the record is going to
608 -- be updated.
609 -- - The pay_proposal_id is valid and a salary has already being
610 -- approved for this assignment.
611 --
612 -- Post Failure:
613 -- - The approved flag is set to 'Y" while updating.
614 --
615 -- Access Status
616 -- Internal Table Handler Use Only.
617 --
618 --
619 procedure chk_change_amount_percentage
620 (p_component_id
621 in per_pay_proposal_components.component_id%TYPE
622 ,p_pay_proposal_id
623 in per_pay_proposal_components.pay_proposal_id%TYPE
624 ,p_change_amount_n
625 in out nocopy per_pay_proposal_components.change_amount_n%TYPE
626 ,p_change_percentage
627 in out nocopy per_pay_proposal_components.change_percentage%TYPE
628 ,p_approved
629 in per_pay_proposal_components.approved%TYPE
630 ,p_object_version_number
631 in per_pay_proposal_components.object_version_number%TYPE
632 )
633 is
634 --
635 l_exists varchar2(1);
636 l_api_updating boolean;
637 l_proposed_salary number;
638 l_change_amount_n
639 per_pay_proposal_components.change_amount_n%TYPE;
640 l_change_percentage
641 per_pay_proposal_components.change_percentage%TYPE;
642 l_date per_pay_proposals.change_date%TYPE;
643 l_assignment_id number;
644 l_business_group_id number;
645 l_change_date date;
646 l_prev_date date;
647 l_proc varchar2(72):=
648 g_package||'chk_change_amount_percentage';
649 --
650 --
651 -- Cursor to get the last approved salary proposal.
652 --
653 -- BEGIN MODIFICATION FOR BUG 4260464
654 --
655 -- Modify cursor to join directly to lower subquery. This allows the
656 -- appropriate index to be used instead of a full table scan.
657 --
658 -- CURSOR csr_last_proposed_salary is
659 -- select pro.proposed_salary_n
660 -- from per_pay_proposals pro
661 -- where pro.change_date=(select max(pro2.change_date)
662 -- from per_pay_proposals pro2
663 -- where pro2.assignment_id=pro.assignment_id
664 -- and pro2.approved='Y'
665 -- and pro2.pay_proposal_id<>p_pay_proposal_id)
666 -- and pro.assignment_id = (select pro3.assignment_id
667 -- from per_pay_proposals pro3
668 -- where pro3.pay_proposal_id=p_pay_proposal_id);
669 --
670 CURSOR csr_dates(p_pay_proposal_id number) is
671 select pro2.assignment_id, pro2.business_group_id,
672 pro2.change_date, pro2.change_date-1 prev_date
673 from per_pay_proposals pro2
674 where pro2.pay_proposal_id = p_pay_proposal_id;
675
676 CURSOR csr_proposal_info(p_assignment_id number, p_query_date date)
677 is
678 select pay_proposal_id, change_date, proposed_salary_n
679 ,nvl(ppb.pay_annualization_factor,
680 PER_SALADMIN_UTILITY.get_pay_annualization_factor
681 (ppp.assignment_id, change_date, ppb.pay_annualization_factor,
682 ppb.pay_basis)) annualization_factor,
683 pet.input_currency_code as currency_code, ppb.pay_basis
684 frequency
685 from per_pay_proposals ppp
686 ,per_all_assignments_f paa
687 ,per_pay_bases ppb
688 ,pay_input_values_f piv
689 ,pay_element_types_f pet
690 where
691 ppp.assignment_id = p_assignment_id
692 and p_query_date
693 between change_date and nvl(date_to, hr_general.end_of_time)
694 and paa.assignment_id = ppp.assignment_id
695 and ppp.change_date
696 between paa.effective_start_date and paa.effective_end_date
697 and paa.pay_basis_id = ppb.pay_basis_id
698 and ppb.input_value_id = piv.input_value_id
699 and ppp.change_date
700 between piv.effective_start_date and piv.effective_end_date
701 and piv.element_type_id = pet.element_type_id
702 and ppp.change_date
703 between pet.effective_start_date and pet.effective_end_date;
704
705 CURSOR csr_last_proposed_salary is
706 select p1.proposed_salary_n
707 from per_pay_proposals p1,
708 (select pro2.assignment_id, pro2.change_date-1 prev_date
709 from per_pay_proposals pro2
710 where pro2.pay_proposal_id = p_pay_proposal_id ) p2
711 where p1.date_to = prev_date
712 and p1.assignment_id = p2.assignment_id;
713
714 r_old csr_proposal_info%rowtype;
715 r_new csr_proposal_info%rowtype;
716 l_currency_rate number := 1;
717 l_annual_rate number := 1;
718 --
719 begin
720 hr_utility.set_location('Entering:'|| l_proc, 1);
721 --
722 -- Check for mandatory arguments
723 --
724 hr_api.mandatory_arg_error
725 (p_api_name => l_proc
726 ,p_argument => 'pay_proposal_id'
727 ,p_argument_value => p_pay_proposal_id
728 );
729 --
730 --
731 hr_api.mandatory_arg_error
732 (p_api_name => l_proc
733 ,p_argument => 'approved'
734 ,p_argument_value => p_approved
735 );
736 --
737 -- Only proceed with validation if :
738 -- a) The current g_old_rec is current and
739 -- b) The value for change_amount_n or change_percentage has changed
740 --
741 l_api_updating := per_ppc_shd.api_updating
742 (p_component_id => p_component_id
743 ,p_object_version_number => p_object_version_number);
744 --
745
746 if (l_api_updating) then
747 --
748 -- Check that the change_amount_n and change_percentage cannot be
749 -- updated if the the component is already approved.
750 --
751 if (per_ppc_shd.g_old_rec.approved = 'Y' AND p_approved = 'Y' AND
752 (per_ppc_shd.g_old_rec.change_amount_n <> p_change_amount_n OR
753 per_ppc_shd.g_old_rec.change_percentage <> p_change_percentage))
754 then
755 hr_utility.set_location(l_proc, 1);
756 -- allow Component update.
757 -- hr_utility.set_message(801,'HR_51268_PYP_CANT_UPD_RECORD');
758 -- hr_utility.raise_error;
759 end if;
760 --
761 -- Check to see which of the two attribue has been updated.
762 --
763 end if;
764 --
765 --
766 l_change_amount_n := p_change_amount_n;
767 l_change_percentage := p_change_percentage;
768 --
769 if (l_api_updating AND
770 (nvl(per_ppc_shd.g_old_rec.change_amount_n,hr_api.g_number)
771 <> nvl(p_change_amount_n, hr_api.g_number) OR
772 nvl(per_ppc_shd.g_old_rec.change_percentage,hr_api.g_number)
773 <>
774 nvl(p_change_percentage ,hr_api.g_number))
775 OR not l_api_updating) then
776 --
777 -- Don't do anything if both are null.
778 --
779 if ( p_change_amount_n IS NULL AND p_change_percentage IS NULL)
780 then
781 hr_utility.set_location(l_proc, 2);
782 else
783 open csr_dates(p_pay_proposal_id);
784 fetch csr_dates into l_assignment_id, l_business_group_id,
785 l_change_date, l_prev_date;
786 close csr_dates;
787 --
788 --
789 --
790 open csr_proposal_info(l_assignment_id, l_change_date);
791 fetch csr_proposal_info into r_new;
792 close csr_proposal_info;
793 --
794 open csr_proposal_info(l_assignment_id, l_prev_date);
795 fetch csr_proposal_info into r_old;
796 close csr_proposal_info;
797 --
798 --
799 --
800 if (r_old.currency_code <> r_new.currency_code)
801 then
802 l_currency_rate :=
803 hr_currency_pkg.get_rate_sql(r_old.currency_code,
804 r_new.currency_code ,
805 l_change_date,
806 hr_currency_pkg.get_rate_type(l_business_group_id,l_change_date,'P'));
807 end if;
808
809 if (r_old.annualization_factor<>r_new.annualization_factor
810 and r_new.annualization_factor<>0) then
811 l_annual_rate :=
812 r_old.annualization_factor/r_new.annualization_factor;
813 end if;
814
815 l_proposed_salary := r_old.proposed_salary_n * l_annual_rate *
816 l_currency_rate;
817 /*
818 --
819 -- get the last approved salary proposal
820 --
821 open csr_last_proposed_salary;
822 fetch csr_last_proposed_salary into l_proposed_salary;
823 close csr_last_proposed_salary;
824 */
825
826 if (l_proposed_salary is not null) then
827 --
828 -- recalculate the change_amount_n from change_percentage
829 --
830 if(p_change_amount_n IS NULL AND p_change_percentage IS NULL) then
831 l_change_amount_n := 0;
832 l_change_percentage := 0;
833 elsif (p_change_amount_n IS NULL and p_change_percentage IS NOT NULL) then
834 l_change_amount_n := p_change_percentage*l_proposed_salary/100 ;
835 l_change_percentage := p_change_percentage;
836 elsif (p_change_amount_n IS NOT NULL and p_change_percentage IS NULL AND l_proposed_salary <>0) then
837 l_change_percentage :=(p_change_amount_n*100)/l_proposed_salary;
838 l_change_amount_n := p_change_amount_n;
839 elsif (p_change_amount_n IS NOT NULL and p_change_percentage IS NOT NULL) then
840 l_change_percentage := p_change_percentage;
841 l_change_amount_n := p_change_amount_n;
842 end if;
843
844
845 --
846 end if;
847 end if; -- For check percentage and amount null check
848 --
849 end if;
850 --
851 -- set output parameters
852 --
853 p_change_amount_n := l_change_amount_n;
854 p_change_percentage := l_change_percentage;
855 hr_utility.set_location('Leaving: '|| l_proc, 10);
856 --
857 end chk_change_amount_percentage;
858 --
859 --
860 -- -------------------------------------------------------------------
861 --|------------------< chk_delete_component >-----------------------|-
862 ----------------------------------------------------------------------
863 --
864 --
865 -- Description
866 -- - Validates that a component of an approved proposal cannot
867 -- be deleted.
868 --
869 -- Pre-Condition
870 -- A valid pay_proposal_id
871 --
872 -- Post_success
873 -- The process continues
874 --
875 -- Post_Failure
876 -- An error message is raised if the approved flag in per_pay_proposal
877 -- is 'Y'
878 --
879 -- Access Status
880 -- Internal Table Handler Use Only.
881 --
882 --
883 procedure chk_delete_component
884 (p_component_id
885 in per_pay_proposal_components.component_id%TYPE
886 ) is
887 --
888 l_exists varchar2(1);
889 l_proc varchar2(72):= g_package || 'chk_delete_component';
890 l_pay_proposal_id per_pay_proposals.pay_proposal_id%TYPE;
891 --
892 -- Cursor to check the status of the per_pay-proposal apprved flag.
893 --
894 cursor csr_proposal_status is
895 select null
896 from per_pay_proposals pro
897 where pro.pay_proposal_id = l_pay_proposal_id
898 and pro.approved = 'Y';
899 --
900 -- Cursor to check that this component exists for this proposal
901 --
902 Cursor csr_comp_exists is
903 select pay_proposal_id
904 from per_pay_proposal_components comp
905 where comp.component_id = p_component_id;
906 --
907 --
908 begin
909 hr_utility.set_location('Entering:'|| l_proc, 1);
910 --
911 -- Check mandatory parameters have been set
912 --
913 hr_api.mandatory_arg_error
914 (p_api_name => l_proc
915 ,p_argument => 'component_id'
916 ,p_argument_value => p_component_id
917 );
918 --
919 -- Check that the component exists
920 --
921 open csr_comp_exists;
922 fetch csr_comp_exists into l_pay_proposal_id;
923 if csr_comp_exists%notfound then
924 close csr_comp_exists;
925 hr_utility.set_location(l_proc, 2);
926 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
927 hr_utility.raise_error;
928 end if;
929 close csr_comp_exists;
930 --
931 -- Check that the proposal is not already approved.
932 --
933 open csr_proposal_status;
934 fetch csr_proposal_status into l_exists;
935 if csr_proposal_status%notfound then
936 hr_utility.set_location(l_proc, 2);
937 close csr_proposal_status;
938 else
939 hr_utility.set_location(l_proc, 3);
940 close csr_proposal_status;
941 -- hr_utility.set_message(801,'HR_51315_PPC_CANT_DEL_RECORD');
942 -- hr_utility.raise_error;
943 end if;
944 --
945 hr_utility.set_location ('Leaving: ' || l_proc, 4);
946 --
947 end chk_delete_component;
948 --
949 -- -----------------------------------------------------------------------
950 -- |------------------------------< chk_df >-----------------------------|
951 -- -----------------------------------------------------------------------
952 --
953 -- Description:
954 -- Validates the all Descriptive Flexfield values.
955 --
956 -- Pre-conditions:
957 -- All other columns have been validated. Must be called as the
958 -- last step from insert_validate and update_validate.
959 --
960 -- In Arguments:
961 -- p_rec
962 --
963 -- Post Success:
964 -- If the Descriptive Flexfield structure column and data values are
965 -- all valid this procedure will end normally and processing will
966 -- continue.
967 --
968 -- Post Failure:
969 -- If the Descriptive Flexfield structure column value or any of
970 -- the data values are invalid then an application error is raised as
971 -- a PL/SQL exception.
972 --
973 -- Access Status:
974 -- Internal Row Handler Use Only.
975 --
976 procedure chk_df
977 (p_rec in per_ppc_shd.g_rec_type) is
978 --
979 l_proc varchar2(72) := g_package||'chk_df';
980 --
981 begin
982 hr_utility.set_location('Entering:'||l_proc, 10);
983 --
984 if ((p_rec.component_id is not null) and (
985 nvl(per_ppc_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
986 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
987 nvl(per_ppc_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
988 nvl(p_rec.attribute1, hr_api.g_varchar2) or
989 nvl(per_ppc_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
990 nvl(p_rec.attribute2, hr_api.g_varchar2) or
991 nvl(per_ppc_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
992 nvl(p_rec.attribute3, hr_api.g_varchar2) or
993 nvl(per_ppc_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
994 nvl(p_rec.attribute4, hr_api.g_varchar2) or
995 nvl(per_ppc_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
996 nvl(p_rec.attribute5, hr_api.g_varchar2) or
997 nvl(per_ppc_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
998 nvl(p_rec.attribute6, hr_api.g_varchar2) or
999 nvl(per_ppc_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
1000 nvl(p_rec.attribute7, hr_api.g_varchar2) or
1001 nvl(per_ppc_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
1002 nvl(p_rec.attribute8, hr_api.g_varchar2) or
1003 nvl(per_ppc_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
1004 nvl(p_rec.attribute9, hr_api.g_varchar2) or
1005 nvl(per_ppc_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
1006 nvl(p_rec.attribute10, hr_api.g_varchar2) or
1007 nvl(per_ppc_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
1008 nvl(p_rec.attribute11, hr_api.g_varchar2) or
1009 nvl(per_ppc_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
1010 nvl(p_rec.attribute12, hr_api.g_varchar2) or
1011 nvl(per_ppc_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
1012 nvl(p_rec.attribute13, hr_api.g_varchar2) or
1013 nvl(per_ppc_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
1014 nvl(p_rec.attribute14, hr_api.g_varchar2) or
1015 nvl(per_ppc_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
1016 nvl(p_rec.attribute15, hr_api.g_varchar2) or
1017 nvl(per_ppc_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
1018 nvl(p_rec.attribute16, hr_api.g_varchar2) or
1019 nvl(per_ppc_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
1020 nvl(p_rec.attribute17, hr_api.g_varchar2) or
1021 nvl(per_ppc_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
1022 nvl(p_rec.attribute18, hr_api.g_varchar2) or
1023 nvl(per_ppc_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
1024 nvl(p_rec.attribute19, hr_api.g_varchar2) or
1025 nvl(per_ppc_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
1026 nvl(p_rec.attribute20, hr_api.g_varchar2)))
1027 or
1028 (p_rec.component_id is null) then
1029 --
1030 -- Only execute the validation if absolutely necessary:
1031 -- a) During update, the structure column value or any
1032 -- of the attribute values have actually changed.
1033 -- b) During insert.
1034 --
1035 hr_dflex_utility.ins_or_upd_descflex_attribs
1036 (p_appl_short_name => 'PER'
1037 ,p_descflex_name => 'PER_PAY_PROPOSAL_COMPONENTS'
1038 ,p_attribute_category => p_rec.attribute_category
1039 ,p_attribute1_name => 'ATTRIBUTE1'
1040 ,p_attribute1_value => p_rec.attribute1
1041 ,p_attribute2_name => 'ATTRIBUTE2'
1042 ,p_attribute2_value => p_rec.attribute2
1043 ,p_attribute3_name => 'ATTRIBUTE3'
1044 ,p_attribute3_value => p_rec.attribute3
1045 ,p_attribute4_name => 'ATTRIBUTE4'
1046 ,p_attribute4_value => p_rec.attribute4
1047 ,p_attribute5_name => 'ATTRIBUTE5'
1048 ,p_attribute5_value => p_rec.attribute5
1049 ,p_attribute6_name => 'ATTRIBUTE6'
1050 ,p_attribute6_value => p_rec.attribute6
1051 ,p_attribute7_name => 'ATTRIBUTE7'
1052 ,p_attribute7_value => p_rec.attribute7
1053 ,p_attribute8_name => 'ATTRIBUTE8'
1054 ,p_attribute8_value => p_rec.attribute8
1055 ,p_attribute9_name => 'ATTRIBUTE9'
1056 ,p_attribute9_value => p_rec.attribute9
1057 ,p_attribute10_name => 'ATTRIBUTE10'
1058 ,p_attribute10_value => p_rec.attribute10
1059 ,p_attribute11_name => 'ATTRIBUTE11'
1060 ,p_attribute11_value => p_rec.attribute11
1061 ,p_attribute12_name => 'ATTRIBUTE12'
1062 ,p_attribute12_value => p_rec.attribute12
1063 ,p_attribute13_name => 'ATTRIBUTE13'
1064 ,p_attribute13_value => p_rec.attribute13
1065 ,p_attribute14_name => 'ATTRIBUTE14'
1066 ,p_attribute14_value => p_rec.attribute14
1067 ,p_attribute15_name => 'ATTRIBUTE15'
1068 ,p_attribute15_value => p_rec.attribute15
1069 ,p_attribute16_name => 'ATTRIBUTE16'
1070 ,p_attribute16_value => p_rec.attribute16
1071 ,p_attribute17_name => 'ATTRIBUTE17'
1072 ,p_attribute17_value => p_rec.attribute17
1073 ,p_attribute18_name => 'ATTRIBUTE18'
1074 ,p_attribute18_value => p_rec.attribute18
1075 ,p_attribute19_name => 'ATTRIBUTE19'
1076 ,p_attribute19_value => p_rec.attribute19
1077 ,p_attribute20_name => 'ATTRIBUTE20'
1078 ,p_attribute20_value => p_rec.attribute20
1079 );
1080 end if;
1081 --
1082 hr_utility.set_location(' Leaving:'||l_proc, 20);
1083 end chk_df;
1084 --
1085 -- ------------------------------------------------------------------
1086 -- |------------------< insert_validate >----------------------------|
1087 -- ------------------------------------------------------------------
1088 Procedure insert_validate(p_rec in out nocopy per_ppc_shd.g_rec_type
1089 ,p_validation_strength in varchar2 default 'STRONG') is
1090 --
1091 l_proc varchar2(72) := g_package||'insert_validate';
1092 --
1093 Begin
1094 hr_utility.set_location('Entering:'||l_proc, 5);
1095 --
1096 -- Call all supporting business operations. Mapping to the
1097 -- appropriate Business Rules in perpyp.bru is provided (where
1098 -- relevant)
1099 --
1100 --
1101 -- Validate business_group id
1102 --
1103 -- Business Rule Mapping
1104 -- =====================
1105 -- Rule CHK_BUSINESS_GROUP_ID a,c
1106 --
1107 hr_api.validate_bus_grp_id(p_rec.business_group_id);
1108 --
1109 hr_utility.set_location(l_proc, 10);
1110 --
1111 -- Validate pay_proposal_id
1112 --
1113 -- Business Rule Mapping
1114 -- =====================
1115 -- Rule CHK_PAY_PROPOSAL_ID /a,c,d,e
1116 --
1117 per_ppc_bus.chk_pay_proposal_id
1118 (p_component_id => p_rec.component_id
1119 ,p_pay_proposal_id => p_rec.pay_proposal_id
1120 ,p_business_group_id => p_rec.business_group_id
1121 ,p_object_version_number => p_rec.object_version_number
1122 ,p_validation_strength => p_validation_strength
1123 );
1124 --
1125 -- call to chk_access added for fixing bug#3839734
1126 per_ppc_bus.chk_access(p_pay_proposal_id => p_rec.pay_proposal_id);
1127 --
1128 hr_utility.set_location(l_proc, 15);
1129 --
1130 -- Business Rule Mapping
1131 -- =====================
1132 -- Rule CHK_COMPONENT_REASON /a,b,c
1133 --
1134 per_ppc_bus.chk_component_reason
1135 (p_component_id => p_rec.component_id
1136 ,p_pay_proposal_id => p_rec.pay_proposal_id
1137 ,p_component_reason => p_rec.component_reason
1138 ,p_approved => p_rec.approved
1139 ,p_object_version_number => p_rec.object_version_number
1140 );
1141 --
1142 hr_utility.set_location(l_proc, 20);
1143 --
1144 --
1145 -- Business Rule Mapping
1146 -- =====================
1147 -- Rule CHK_CHANGE_AMOUNT /a
1148 -- Rule CHK_CHANGE_PERCENTAGE /a
1149 -- Rule CHK_CHANGE_AMOUNT_PERCENTAGE /a,b
1150 --
1151 per_ppc_bus.chk_change_amount_percentage
1152 (p_component_id => p_rec.component_id
1153 ,p_pay_proposal_id => p_rec.pay_proposal_id
1154 ,p_change_amount_n => p_rec.change_amount_n
1155 ,p_change_percentage => p_rec.change_percentage
1156 ,p_approved => p_rec.approved
1157 ,p_object_version_number => p_rec.object_version_number
1158 );
1159 --
1160 hr_utility.set_location(l_proc, 25);
1161 --
1162 --
1163 -- Business Rule Mapping
1164 -- =====================
1165 -- Rule CHK_APPROVED /a,b,c
1166 --
1167 per_ppc_bus.chk_approved
1168 (p_component_id => p_rec.component_id
1169 ,p_approved => p_rec.approved
1170 ,p_component_reason => p_rec.component_reason
1171 ,p_change_amount_n => p_rec.change_amount_n
1172 ,p_change_percentage => p_rec.change_percentage
1173 ,p_object_version_number => p_rec.object_version_number
1174 );
1175 --
1176 hr_utility.set_location(l_proc, 30);
1177 --
1178 --
1179 -- Call descriptive flexfield validation routines
1180 --
1181 per_ppc_bus.chk_df(p_rec => p_rec);
1182 --
1183 hr_utility.set_location(' Leaving:'||l_proc, 35);
1184 End insert_validate;
1185 --
1186 -- ----------------------------------------------------------------------------
1187 -- |---------------------------< update_validate >----------------------------|
1188 -- ----------------------------------------------------------------------------
1189 Procedure update_validate(p_rec in out nocopy per_ppc_shd.g_rec_type
1190 ,p_validation_strength in varchar2 default 'STRONG') is
1191 --
1192 l_proc varchar2(72) := g_package||'update_validate';
1193 --
1194 Begin
1195 hr_utility.set_location('Entering:'||l_proc, 5);
1196 --
1197 -- Call all supporting business operations. Mapping to the
1198 -- appropriate Business Rules in perpyp.bru is provided (where
1199 -- relevant)
1200 --
1201 -- Validate business_group id
1202 --
1203 hr_api.validate_bus_grp_id(p_rec.business_group_id);
1204 --
1205 hr_utility.set_location(l_proc, 12);
1206 --
1207 -- Check those columns which cannot be updated
1208 -- have not changed
1209 --
1210 -- Business Rule Mapping
1211 -- =====================
1212 -- CHK_COMPONENT_ID /c
1213 -- CHK_PAY_PROPOSAL_ID /b
1214 -- CHK_BUSINESS_GROUP_ID /b
1215 --
1216 per_ppc_bus.check_non_updateable_args
1217 (p_rec =>p_rec);
1218 --
1219 hr_utility.set_location(l_proc, 10);
1220 --
1221 -- Validate pay_proposal_id
1222 --
1223 -- Business Rule Mapping
1224 -- =====================
1225 -- CHK_PAY_PROPOSAL_id /e
1226 --
1227 per_ppc_bus.chk_pay_proposal_id
1228 (p_component_id => p_rec.component_id
1229 ,p_pay_proposal_id => p_rec.pay_proposal_id
1230 ,p_business_group_id => p_rec.business_group_id
1231 ,p_object_version_number => p_rec.object_version_number
1232 ,p_validation_strength => p_validation_strength
1233 );
1234 --
1235 -- call to chk_access added for fixing bug#3839734
1236 per_ppc_bus.chk_access(p_pay_proposal_id => p_rec.pay_proposal_id);
1237 --
1238 --
1239 --
1240 hr_utility.set_location(l_proc, 15);
1241 --
1242 -- Business Rule Mapping
1243 -- =====================
1244 -- Rule CHK_COMPONENT_REASON /a,b,d
1245 --
1246 per_ppc_bus.chk_component_reason
1247 (p_component_id => p_rec.component_id
1248 ,p_pay_proposal_id => p_rec.pay_proposal_id
1249 ,p_component_reason => p_rec.component_reason
1250 ,p_approved => p_rec.approved
1251 ,p_object_version_number => p_rec.object_version_number
1252 );
1253 --
1254 hr_utility.set_location(l_proc, 20);
1255 --
1256 --
1257 -- Business Rule Mapping
1258 -- =====================
1259 -- Rule CHK_CHANGE_AMOUNT /a,b
1260 -- Rule CHK_CHANGE_PERCENTAGE /a
1261 -- Rule CHK_CHANGE_AMOUNT_PERCENTAGE /a,b
1262 --
1263 per_ppc_bus.chk_change_amount_percentage
1264 (p_component_id => p_rec.component_id
1265 ,p_pay_proposal_id => p_rec.pay_proposal_id
1266 ,p_change_amount_n => p_rec.change_amount_n
1267 ,p_change_percentage => p_rec.change_percentage
1268 ,p_approved => p_rec.approved
1269 ,p_object_version_number => p_rec.object_version_number
1270 );
1271 --
1272 hr_utility.set_location(l_proc, 25);
1273 --
1274 --
1275 -- Business Rule Mapping
1276 -- =====================
1277 -- Rule CHK_APPROVED /b,c
1278 --
1279 per_ppc_bus.chk_approved
1280 (p_component_id => p_rec.component_id
1281 ,p_approved => p_rec.approved
1282 ,p_component_reason => p_rec.component_reason
1283 ,p_change_amount_n => p_rec.change_amount_n
1284 ,p_change_percentage => p_rec.change_percentage
1285 ,p_object_version_number => p_rec.object_version_number
1286 );
1287 --
1288 hr_utility.set_location(l_proc, 30);
1289 --
1290 --
1291 -- Call descriptive flexfield validation routines
1292 --
1293 per_ppc_bus.chk_df(p_rec => p_rec);
1294 --
1295 hr_utility.set_location(' Leaving:'||l_proc, 10);
1296 End update_validate;
1297 --
1298 -- ----------------------------------------------------------------------------
1299 -- |---------------------------< delete_validate >----------------------------|
1300 -- ----------------------------------------------------------------------------
1301 Procedure delete_validate(p_rec in per_ppc_shd.g_rec_type,
1302 p_validation_strength in varchar2 default 'STRONG') is
1303 --
1304 l_proc varchar2(72) := g_package||'delete_validate';
1305 --
1306 Begin
1307 hr_utility.set_location('Entering:'||l_proc, 5);
1308 --
1309 -- Call all supporting business operations
1310 --
1311 -- Validate delete
1312 -- call to chk_access added for fixing bug#3839734
1313 per_ppc_bus.chk_access(p_pay_proposal_id => per_ppc_shd.g_old_rec.pay_proposal_id);
1314 --
1315 --
1316 -- Business Rule Mapping
1317 -- =====================
1318 -- Rule CHK_DELETE_COMPONENT /a
1319 --
1320 if (p_validation_strength='WEAK') THEN
1321 hr_utility.set_location(' WEAK:'||l_proc, 7);
1322 elsif (p_validation_strength='STRONG') THEN
1323 hr_utility.set_location(' STRONG:'||l_proc, 8);
1324 chk_delete_component
1325 (p_component_id => p_rec.component_id
1326 );
1327 else
1328 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1329 hr_utility.set_message_token('PROCEDURE', l_proc);
1330 hr_utility.set_message_token('STEP', '1');
1331 end if;
1332
1333 hr_utility.set_location(' Leaving:'||l_proc, 10);
1334 End delete_validate;
1335 --
1336 --
1337 -- ---------------------------------------------------------------------------
1338 -- |---------------------< return_legislation_code >-------------------------|
1339 -- ---------------------------------------------------------------------------
1340 --
1341 function return_legislation_code
1342 (p_component_id in number
1343 ) return varchar2 is
1344 --
1345 -- Cursor to find legislation code
1346 --
1347 cursor csr_leg_code is
1348 select pbg.legislation_code
1349 from per_business_groups pbg
1350 , per_pay_proposal_components ppc
1351 where ppc.component_id = component_id
1352 and pbg.business_group_id = ppc.business_group_id;
1353 --
1354 -- Declare local variables
1355 --
1356 l_legislation_code varchar2(150);
1357 l_proc varchar2(72) := 'return_legislation_code';
1358 begin
1359 hr_utility.set_location('Entering:'|| l_proc, 10);
1360 --
1361 -- Ensure that all the mandatory parameter are not null
1362 --
1363 hr_api.mandatory_arg_error(p_api_name => l_proc,
1364 p_argument => 'component_id',
1365 p_argument_value => p_component_id);
1366 --
1367 if nvl(g_component_id, hr_api.g_number) = p_component_id then
1368 --
1369 -- The legislation code has already been found with a previous
1370 -- call to this function. Just return the value in the global
1371 -- variable.
1372 --
1373 l_legislation_code := g_legislation_code;
1374 hr_utility.set_location(l_proc, 20);
1375 else
1376 --
1377 -- The ID is different to the last call to this function
1378 -- or this is the first call to this function.
1379 --
1380 open csr_leg_code;
1381 fetch csr_leg_code into l_legislation_code;
1382 if csr_leg_code%notfound then
1383 --
1384 -- The primary key is invalid therefore we must error
1385 --
1386 close csr_leg_code;
1387 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1388 hr_utility.raise_error;
1389 end if;
1390 hr_utility.set_location(l_proc, 30);
1391 --
1392 -- Set the global variables so the values are
1393 -- available for the next call to this function
1394 --
1395 close csr_leg_code;
1396 g_component_id := p_component_id;
1397 g_legislation_code := l_legislation_code;
1398 end if;
1399 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1400 --
1401 return l_legislation_code;
1402 end return_legislation_code;
1403 --
1404 end per_ppc_bus;