1 Package Body pay_ppr_bus as
2 /* $Header: pypprrhi.pkb 115.3 2004/02/25 21:33 adkumar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ppr_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_status_processing_rule_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_status_processing_rule_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , pay_status_processing_rules_f spr
32 where spr.status_processing_rule_id = p_status_processing_rule_id
33 and pbg.business_group_id (+) = spr.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'status_processing_rule_id'
50 ,p_argument_value => p_status_processing_rule_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'STATUS_PROCESSING_RULE_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_status_processing_rule_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , pay_status_processing_rules_f spr
102 where spr.status_processing_rule_id = p_status_processing_rule_id
103 and pbg.business_group_id (+) = spr.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'status_processing_rule_id'
119 ,p_argument_value => p_status_processing_rule_id
120 );
121 --
122 if ( nvl(pay_ppr_bus.g_status_processing_rule_id, hr_api.g_number)
123 = p_status_processing_rule_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := pay_ppr_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 pay_ppr_bus.g_status_processing_rule_id := p_status_processing_rule_id;
154 pay_ppr_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |-----------------------< chk_non_updateable_args >------------------------|
162 -- ----------------------------------------------------------------------------
163 -- {Start Of Comments}
164 --
165 -- Description:
166 -- This procedure is used to ensure that non updateable attributes have
167 -- not been updated. If an attribute has been updated an error is generated.
168 --
169 -- Pre Conditions:
170 -- g_old_rec has been populated with details of the values currently in
171 -- the database.
172 --
173 -- In Arguments:
174 -- p_rec has been populated with the updated values the user would like the
175 -- record set to.
176 --
177 -- Post Success:
178 -- Processing continues if all the non updateable attributes have not
179 -- changed.
180 --
181 -- Post Failure:
182 -- An application error is raised if any of the non updatable attributes
183 -- have been altered.
184 --
185 -- {End Of Comments}
186 -- ----------------------------------------------------------------------------
187 Procedure chk_non_updateable_args
188 (p_effective_date in date
189 ,p_rec in pay_ppr_shd.g_rec_type
190 ) IS
191 --
192 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
193 l_argument varchar2(80);
194 l_error exception;
195 --
196 Begin
197 hr_utility.set_location('Entering:'||l_proc, 5);
198 --
199 -- Only proceed with the validation if a row exists for the current
200 -- record in the HR Schema.
201 --
202 IF NOT pay_ppr_shd.api_updating
203 (p_status_processing_rule_id => p_rec.status_processing_rule_id
204 ,p_effective_date => p_effective_date
205 ,p_object_version_number => p_rec.object_version_number
206 ) THEN
207 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
208 fnd_message.set_token('PROCEDURE ', l_proc);
209 fnd_message.set_token('STEP ', '5');
210 fnd_message.raise_error;
211 END IF;
212 --
213 hr_utility.set_location('Entering:'||l_proc, 10);
214 --
215 -- Ensure that the following attributes are not updated.
216 --
217 If nvl(p_rec.business_group_id,hr_api.g_number) <>
218 nvl(pay_ppr_shd.g_old_rec.business_group_id,hr_api.g_number) then
219 --
220 l_argument := 'business_group_id';
221 raise l_error;
222 --
223 End if;
224 --
225 hr_utility.set_location('Entering:'||l_proc, 15);
226 --
227 If nvl(p_rec.legislation_code,hr_api.g_varchar2) <>
228 nvl(pay_ppr_shd.g_old_rec.legislation_code,hr_api.g_varchar2) then
229 --
230 l_argument := 'legislation_code';
231 raise l_error;
232 --
233 End if;
234 --
235 hr_utility.set_location('Entering:'||l_proc, 20);
236 --
237 If nvl(p_rec.status_processing_rule_id,hr_api.g_number) <>
238 nvl(pay_ppr_shd.g_old_rec.status_processing_rule_id,hr_api.g_number) then
239 --
240 l_argument := 'status_processing_rule_id';
241 raise l_error;
242 --
243 End if;
244 --
245 hr_utility.set_location('Entering:'||l_proc, 25);
246 --
247 If nvl(p_rec.assignment_status_type_id,hr_api.g_number) <>
248 nvl(pay_ppr_shd.g_old_rec.assignment_status_type_id,hr_api.g_number) then
249 --
250 l_argument := 'assignment_status_type_id';
251 raise l_error;
252 --
253 End if;
254 --
255 hr_utility.set_location('Entering:'||l_proc, 30);
256 --
257 If nvl(p_rec.element_type_id,hr_api.g_number) <>
258 nvl(pay_ppr_shd.g_old_rec.element_type_id,hr_api.g_number) then
259 --
260 l_argument := 'element_type_id';
261 raise l_error;
262 --
263 End if;
264 --
265 hr_utility.set_location('Entering:'||l_proc, 35);
266 --
267 If nvl(p_rec.legislation_subgroup,hr_api.g_varchar2) <>
268 nvl(pay_ppr_shd.g_old_rec.legislation_subgroup,hr_api.g_varchar2) then
269 --
270 l_argument := 'legislation_subgroup';
271 raise l_error;
272 --
273 End if;
274 --
275 hr_utility.set_location('Leaving :'||l_proc, 40);
276 EXCEPTION
277 WHEN l_error THEN
278 hr_api.argument_changed_error
279 (p_api_name => l_proc
280 ,p_argument => l_argument);
281 WHEN OTHERS THEN
282 RAISE;
283 End chk_non_updateable_args;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |--------------------------< dt_update_validate >--------------------------|
287 -- ----------------------------------------------------------------------------
288 -- {Start Of Comments}
289 --
290 -- Description:
291 -- This procedure is used for referential integrity of datetracked
292 -- parent entities when a datetrack update operation is taking place
293 -- and where there is no cascading of update defined for this entity.
294 --
295 -- Prerequisites:
296 -- This procedure is called from the update_validate.
297 --
298 -- In Parameters:
299 --
300 -- Post Success:
301 -- Processing continues.
302 --
303 -- Post Failure:
304 --
305 -- Developer Implementation Notes:
306 -- This procedure should not need maintenance unless the HR Schema model
307 -- changes.
308 --
309 -- Access Status:
310 -- Internal Row Handler Use Only.
311 --
312 -- {End Of Comments}
313 -- ----------------------------------------------------------------------------
314 Procedure dt_update_validate
315 (p_datetrack_mode in varchar2
316 ,p_validation_start_date in date
317 ,p_validation_end_date in date
318 ) Is
319 --
320 l_proc varchar2(72) := g_package||'dt_update_validate';
321 --
322 Begin
323 --
324 -- Ensure that the p_datetrack_mode argument is not null
325 --
326 hr_api.mandatory_arg_error
327 (p_api_name => l_proc
328 ,p_argument => 'datetrack_mode'
329 ,p_argument_value => p_datetrack_mode
330 );
331 --
332 -- Mode will be valid, as this is checked at the start of the upd.
333 --
334 -- Ensure the arguments are not null
335 --
336 hr_api.mandatory_arg_error
337 (p_api_name => l_proc
338 ,p_argument => 'validation_start_date'
339 ,p_argument_value => p_validation_start_date
340 );
341 --
342 hr_api.mandatory_arg_error
343 (p_api_name => l_proc
344 ,p_argument => 'validation_end_date'
345 ,p_argument_value => p_validation_end_date
346 );
347 --
348 --
349 --
350 Exception
351 When Others Then
352 --
353 -- An unhandled or unexpected error has occurred which
354 -- we must report
355 --
356 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
357 fnd_message.set_token('PROCEDURE', l_proc);
358 fnd_message.set_token('STEP','15');
359 fnd_message.raise_error;
360 End dt_update_validate;
361 --
362 -- ----------------------------------------------------------------------------
363 -- |--------------------------< dt_delete_validate >--------------------------|
364 -- ----------------------------------------------------------------------------
365 -- {Start Of Comments}
366 --
367 -- Description:
368 -- This procedure is used for referential integrity of datetracked
369 -- child entities when either a datetrack DELETE or ZAP is in operation
370 -- and where there is no cascading of delete defined for this entity.
371 -- For the datetrack mode of DELETE or ZAP we must ensure that no
372 -- datetracked child rows exist between the validation start and end
373 -- dates.
374 --
375 -- Prerequisites:
376 -- This procedure is called from the delete_validate.
377 --
378 -- In Parameters:
379 --
380 -- Post Success:
381 -- Processing continues.
382 --
383 -- Post Failure:
384 -- If a row exists by determining the returning Boolean value from the
385 -- generic dt_api.rows_exist function then we must supply an error via
386 -- the use of the local exception handler l_rows_exist.
387 --
388 -- Developer Implementation Notes:
389 -- This procedure should not need maintenance unless the HR Schema model
390 -- changes.
391 --
392 -- Access Status:
393 -- Internal Row Handler Use Only.
394 --
395 -- {End Of Comments}
396 -- ----------------------------------------------------------------------------
397 Procedure dt_delete_validate
398 (p_status_processing_rule_id in number
399 ,p_datetrack_mode in varchar2
400 ,p_validation_start_date in date
401 ,p_validation_end_date in date
402 ) Is
403 --
404 l_proc varchar2(72) := g_package||'dt_delete_validate';
405 --
406 Begin
407 --
408 -- Ensure that the p_datetrack_mode argument is not null
409 --
410 hr_api.mandatory_arg_error
411 (p_api_name => l_proc
412 ,p_argument => 'datetrack_mode'
413 ,p_argument_value => p_datetrack_mode
414 );
415 --
416 -- Only perform the validation if the datetrack mode is either
417 -- DELETE or ZAP
418 --
419 If (p_datetrack_mode = hr_api.g_delete or
420 p_datetrack_mode = hr_api.g_zap) then
421 --
422 --
423 -- Ensure the arguments are not null
424 --
425 hr_api.mandatory_arg_error
426 (p_api_name => l_proc
427 ,p_argument => 'validation_start_date'
428 ,p_argument_value => p_validation_start_date
429 );
430 --
431 hr_api.mandatory_arg_error
432 (p_api_name => l_proc
433 ,p_argument => 'validation_end_date'
434 ,p_argument_value => p_validation_end_date
435 );
436 --
437 hr_api.mandatory_arg_error
438 (p_api_name => l_proc
439 ,p_argument => 'status_processing_rule_id'
440 ,p_argument_value => p_status_processing_rule_id
441 );
442 --
443 --
444 --
445 End If;
446 --
447 Exception
448 When Others Then
449 --
450 -- An unhandled or unexpected error has occurred which
451 -- we must report
452 --
453 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
454 fnd_message.set_token('PROCEDURE', l_proc);
455 fnd_message.set_token('STEP','15');
456 fnd_message.raise_error;
457 --
458 End dt_delete_validate;
459
460 --
461 -- ----------------------------------------------------------------------------
462 -- |------------------------< chk_assignment_status_type_id >-----------------|
463 -- ----------------------------------------------------------------------------
464 --
465 -- Description:
466 -- This procedure is used to validate the assignment_status_type_id against
467 -- the parent table
468 --
469 -- ----------------------------------------------------------------------------
470 Procedure chk_assignment_status_type_id
471 (p_assignment_status_type_id in number
472 ,p_business_group_id in number
473 ,p_legislation_code in varchar2
474 ,p_element_type_id in number
475 ,p_formula_id in number
476 ) is
477 --
478 l_proc varchar2(72) := g_package||'chk_assignment_status_type_id';
479 l_exists varchar2(1);
480
481 Cursor c_chk_assign_status_type
482 is
483 SELECT '1'
484 FROM per_assignment_status_types astp
485 WHERE nvl(astp.assignment_status_type_id,-1) = nvl(p_assignment_status_type_id,-1)
486 and ((astp.legislation_code =
487 nvl(p_legislation_code,hr_api.return_legislation_code(p_business_group_id)))
488 or ( astp.legislation_code is null and
489 astp.business_group_id = p_business_group_id)
490 or ( astp.legislation_code is null and
491 astp.business_group_id is null));
492 --
493 Begin
494 hr_utility.set_location('Entering:'||l_proc, 5);
495 --
496 If p_assignment_status_type_id is not null then
497
498 Open c_chk_assign_status_type;
499 Fetch c_chk_assign_status_type into l_exists;
500 If c_chk_assign_status_type%notfound Then
501 --
502 Close c_chk_assign_status_type;
503 pay_ppr_shd.constraint_error('PAY_STATUS_PROCESSING_RULE_FK2');
504 fnd_message.raise_error;
505 --
506 End If;
507 Close c_chk_assign_status_type;
508 End If;
509 --
510 hr_utility.set_location('Leaving:'||l_proc, 10);
511 End;
512
513 --
514 -- ----------------------------------------------------------------------------
515 -- |------------------------< chk_formula_id >--------------------------|
516 -- ----------------------------------------------------------------------------
517 --
518 -- Description:
519 -- This procedure is used to validate the formula_id against the
520 -- parent table
521 -- check whether formula_type is of type 'ORACLE_PAYROLL' or
522 -- 'BALANCE ADJUSTMENT'
523 -- If FORMULA_ID is not null and the Formula Type associated with FORMULA_ID
524 -- is 'Balance Adjustment', then PROCESSING_RULE must be 'B', else 'P'.
525 -- ----------------------------------------------------------------------------
526 Procedure chk_formula_id
527 ( p_business_group_id in number
528 , p_legislation_code in varchar2
529 , p_status_processing_rule_id in number
530 , p_start_date in date
531 , p_end_date in date
532 , p_element_type_id in number
533 , p_assignment_status_type_id in number
534 , p_formula_id in varchar2
535 , p_processing_rule out nocopy varchar2
536 , p_formula_mismatch_warning out nocopy boolean
537 ) is
538 --
539 l_proc varchar2(72) := g_package||'chk_formula_id';
540
541 cursor c_assignment_status is
542 SELECT astp.user_status
543 FROM pay_ass_status_types_plus_std astp
544 WHERE nvl(astp.assignment_status_type_id,-1) = nvl(p_assignment_status_type_id,-1)
545 and ((astp.legislation_code =
546 nvl(p_legislation_code,hr_api.return_legislation_code(p_business_group_id)))
547 or ( astp.legislation_code is null and
548 astp.business_group_id = p_business_group_id)
549 or ( astp.legislation_code is null and
550 astp.business_group_id is null));
551
552 Cursor c_chk_formula_id
553 is
554 select distinct ft.formula_type_name,
555 Decode(ft.formula_type_name,'Balance Adjustment','B','P') processing_rule
556 from ff_formula_types ft, ff_formulas_f ff
557 where ff.formula_type_id = ft.formula_type_id
558 and ff.formula_id = p_formula_id
559 and ((ff.legislation_code =
560 nvl(p_legislation_code,hr_api.return_legislation_code(p_business_group_id)))
561 or ( ff.legislation_code is null and
562 ff.business_group_id = p_business_group_id)
563 or ( ff.legislation_code is null and
564 ff.business_group_id is null));
565
566 --
567 l_formula_type ff_formula_types.formula_type_name%type;
568 l_assignment_status per_assignment_status_types.user_status%type;
569 --
570 Begin
571 hr_utility.set_location('Entering:'||l_proc, 5);
572
573 --
574 -- if assignment status is of type 'Standard' or 'Balance Adjustment'
575 -- and formula is null then return 'P'
576 if (p_formula_id is null) then
577 -- By default 'P' according to Business Rule
578 p_processing_rule := 'P';
579 else
580 --
581 Open c_chk_formula_id;
582 Fetch c_chk_formula_id into l_formula_type, p_processing_rule;
583 If c_chk_formula_id%notfound Then
584 --
585 Close c_chk_formula_id;
586 fnd_message.set_name('PAY', 'HR_7462_PLK_INVLD_VALUE');
587 fnd_message.set_token('COLUMN_NAME', 'FORMULA_ID');
588 fnd_message.raise_error;
589 --
590 elsif NOT ((upper(l_formula_type) = 'ORACLE PAYROLL'
591 or
592 upper(l_formula_type)='BALANCE ADJUSTMENT')) then
593 --
594 Close c_chk_formula_id;
595 fnd_message.set_name('PAY','PAY_33196_SPR_INVALID_FOR_TYPE');
596 fnd_message.set_token('FORMULA','FORMULA_NAME');
597 fnd_message.raise_error;
598 --
599 elsif (p_assignment_status_type_id is not null
600 and NVL(UPPER(l_formula_type),'-1') <> 'ORACLE PAYROLL') then
601 open c_assignment_status;
602 fetch c_assignment_status into l_assignment_status;
603 close c_assignment_status;
604
605 Close c_chk_formula_id;
606
607 fnd_message.set_name('PAY', 'PAY_33197_SPR_INVALID_ASSIGN');
608 fnd_message.set_token('ASSIGNMENT_STATUS', l_assignment_status);
609 fnd_message.set_token('FORMULA_TYPE', 'Oracle Payroll');
610 fnd_message.raise_error;
611 end if;
612 Close c_chk_formula_id;
613
614
615
616 hr_utility.set_location('Entering:'||l_proc, 10);
617 --
618 --
619 -- check if formula is updated
620 -- formula can not be updated if result rule exist with in
621 -- date range specified for Status Processing Rule
622 --
623 if (p_formula_id <> nvl(pay_ppr_shd.g_old_rec.formula_id,p_formula_id)) and
624 (pay_status_rules_pkg.result_rules_exist(p_status_processing_rule_id,
625 p_start_date, p_end_date)) then
626 fnd_message.set_name('PAY','HR_7135_SPR_FORMULA_NO_UPDATE');
627 fnd_message.raise_error;
628 end if;
629 --
630 hr_utility.set_location('Entering:'||l_proc, 15);
631 --
632 -- check whether input values for the element do not match the
633 -- data type of any of the inputs of the selected formula
634 --
635
636 if (p_formula_id is not null
637 and pay_status_rules_pkg.no_input_values_match_formula(p_element_type_id,p_formula_id)) then
638 --
639 -- if input type do not match then set output variable to true
640 --
641 p_formula_mismatch_warning := True;
642 else
643 p_formula_mismatch_warning := false;
644 end if;
645 End If;
646 --
647 hr_utility.set_location('Leaving:'||l_proc, 20);
648 End;
649 --
650 -- ----------------------------------------------------------------------------
651 -- |------------------------< chk_element_type_id >---------------------------|
652 -- ----------------------------------------------------------------------------
653 --
654 -- Description:
655 -- This procedure is used to validate the element type id against the
656 -- parent table and to check whether the business group and legislation code
657 -- are consistent with those of the element type.
658 --
659 -- ----------------------------------------------------------------------------
660 Procedure chk_element_type_id
661 (p_effective_date in date
662 ,p_element_type_id in number
663 ,p_business_group_id in number
664 ,p_legislation_code in varchar2
665 ) is
666 --
667 l_proc varchar2(72) := g_package||'chk_element_type_id';
668 l_exists varchar2(1);
669
670 Cursor c_chk_element_type
671 is
672 select null
673 from pay_element_types_f element, pay_element_classifications classif,
674 pay_element_classifications_tl classif_tl, pay_element_types_f_tl element_tl
675 where classif_tl.classification_id = classif.classification_id
676 and classif_tl.language = userenv('LANG')
677 and element.Element_type_id = element_tl.Element_type_id
678 and element.Element_type_id = p_element_type_id
679 and element_tl.language = userenv('LANG')
680 and element.classification_id = classif.classification_id
681 and p_effective_date between element.effective_start_date
682 and element.effective_end_date
683 and nvl(element.business_group_id,nvl(p_business_group_id, 0)) = nvl(p_business_group_id,0)
684 and nvl(element.legislation_code, nvl(nvl(p_legislation_code,hr_api.return_legislation_code(p_business_group_id)), '~'))
685 = nvl(nvl(p_legislation_code,hr_api.return_legislation_code(p_business_group_id)),'~');
686
687 --
688 Begin
689 hr_utility.set_location('Entering:'||l_proc, 5);
690 --
691 -- Check mandatory element_type_id exists
692 --
693 --
694 hr_api.mandatory_arg_error
695 (p_api_name => l_proc
696 ,p_argument => 'element_type_id'
697 ,p_argument_value => p_element_type_id
698 );
699 --
700 hr_utility.set_location('Entering:'||l_proc, 10);
701 --
702 open c_chk_element_type;
703 fetch c_chk_element_type into l_exists;
704 if c_chk_element_type%notfound then
705 close c_chk_element_type;
706 fnd_message.set_name('PAY', 'HR_7462_PLK_INVLD_VALUE');
707 fnd_message.set_token('COLUMN_NAME', 'ELEMENT_TYPE_ID');
708 fnd_message.raise_error;
709 end if;
710 close c_chk_element_type;
711 --
712 hr_utility.set_location('Leaving:'||l_proc, 15);
713 End;
714 --
715 -- ----------------------------------------------------------------------------
716 -- |------------------------< chk_legislation_code >--------------------------|
717 -- ----------------------------------------------------------------------------
718 --
719 -- Description:
720 -- This procedure is used to validate the legislation code against the
721 -- parent table
722 --
723 -- ----------------------------------------------------------------------------
724 Procedure chk_legislation_code
725 (p_legislation_code in varchar2)
726 is
727 --
728 l_proc varchar2(72) := g_package||'chk_legislation_code';
729 l_exists varchar2(1);
730
731 Cursor c_chk_leg_code
732 is
733 select null
734 from fnd_territories
735 where territory_code = p_legislation_code;
736 --
737 Begin
738 hr_utility.set_location('Entering:'||l_proc, 5);
739 --
740 If p_legislation_code is not null then
741
742 Open c_chk_leg_code;
743 Fetch c_chk_leg_code into l_exists;
744 If c_chk_leg_code%notfound Then
745 --
746 Close c_chk_leg_code;
747 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
748 fnd_message.set_token('COLUMN','LEGISLATION_CODE');
749 fnd_message.set_token('TABLE','FND_TERRITORIES');
750 fnd_message.raise_error;
751 --
752 End If;
753 Close c_chk_leg_code;
754
755 End If;
756 --
757 hr_utility.set_location('Leaving:'||l_proc, 10);
758 End;
759
760 --
761 -- ----------------------------------------------------------------------------
762 -- |-------------------------< chk_unique_rules >-----------------------------|
763 -- ----------------------------------------------------------------------------
764 --
765 -- Description:
766 -- This procedure is used to check whether the status processing rule being
767 -- created is a duplicate rule.
768 -- null assignmnet_status_type_id denote 'Standard' or 'Balance Adjustment'
769 -- assignment status
770 -- ----------------------------------------------------------------------------
771 Procedure chk_unique_rules
772 (p_effective_date in date
773 ,p_assignment_status_type_id in number default null
774 ,p_processing_rule in varchar2
775 ,p_element_type_id in number
776 ,p_status_processing_rule_id in number default null
777 ) is
778 --
779 l_proc varchar2(72) := g_package||'chk_unique_rules';
780 l_exists varchar2(1);
781 --
782 cursor c_duplicate_rule
783 is
784 select '1'
785 from pay_status_processing_rules_f
786 where nvl(assignment_status_type_id,-1) = nvl(p_assignment_status_type_id,-1)
787 and element_type_id = p_element_type_id
788 and processing_rule = nvl(p_processing_rule,'P')
789 and p_effective_date between effective_start_date
790 and effective_end_date
791 and status_processing_rule_id <> nvl(p_status_processing_rule_id,-1);
792 --
793 begin
794 --
795 hr_utility.set_location('Entering:'||l_proc, 1);
796 --
797 open c_duplicate_rule;
798 fetch c_duplicate_rule into l_exists;
799 if c_duplicate_rule%found then
800 --
801 close c_duplicate_rule;
802 fnd_message.set_name('PAY', 'PAY_33195_SPR_NOT_UNIQUE');
803 fnd_message.raise_error;
804 --
805 End If;
806 --
807 close c_duplicate_rule;
808 hr_utility.set_location('Leaving:'||l_proc, 2);
809 End chk_unique_rules;
810 --
811 -- ----------------------------------------------------------------------------
812 -- |------------------------< set_effective_end_date >------------------------|
813 -- ----------------------------------------------------------------------------
814 --
815 -- Description:
816 -- This procedure is used to set the effective end date of the status
817 -- processing rule based on the end date of Element, formula or Future
818 -- Status Processing Rule.
819 -- ----------------------------------------------------------------------------
820 --
821 Procedure set_effective_end_date
822 (p_effective_date in date
823 ,p_status_processing_rule_id in number
824 ,p_element_type_id in number
825 ,p_formula_id in number
826 ,p_assignment_status_type_id in number
827 ,p_processing_rule in varchar2
828 ,p_business_group_id in number
829 ,p_legislation_code in varchar2
830 ,p_datetrack_mode in varchar2 default null
831 ,p_validation_start_date in date
832 ,p_validation_end_date in out nocopy date
833 ) is
834 --
835 l_proc varchar2(72) := g_package||'set_effective_end_date';
836 l_max_end_date_of_element date;
837
838 --
839 Begin
840 hr_utility.set_location('Entering:'||l_proc, 5);
841 --
842 -- find out max effective end date of the element
843 l_max_end_date_of_element :=
844 pay_element_types_pkg.element_end_date (p_element_type_id);
845
846 --
847 -- set effective_end_date of status processing_rule based
848 -- on end date of formula and element end date and any future
849 -- Status Processing rule
850 --
851 p_validation_end_date := pay_status_rules_pkg.status_rule_end_date(
852 p_status_processing_rule_id =>p_status_processing_rule_id,
853 p_element_type_id =>p_element_type_id,
854 p_formula_id =>p_formula_id,
855 p_assignment_status_type_id =>p_assignment_status_type_id,
856 p_processing_rule =>p_processing_rule,
857 p_session_date =>p_effective_date,
858 p_max_element_end_date =>l_max_end_date_of_element,
859 p_validation_start_date =>p_validation_start_date,
860 p_business_group_id =>p_business_group_id,
861 p_legislation_code =>p_legislation_code
862 );
863
864 --
865 hr_utility.set_location('Leaving:'||l_proc, 10);
866 End;
867 --
868 -- ----------------------------------------------------------------------------
869 -- |----------------------< chk_startup_action >------------------------------|
870 -- ----------------------------------------------------------------------------
871 --
872 -- Description:
873 -- This procedure will check that the current action is allowed according
874 -- to the current startup mode.
875 --
876 -- ----------------------------------------------------------------------------
877 PROCEDURE chk_startup_action
878 (p_insert IN boolean
879 ,p_business_group_id IN number
880 ,p_legislation_code IN varchar2
881 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
882 --
883 BEGIN
884 --
885 -- Call the supporting procedure to check startup mode
886 --
887 IF (p_insert) THEN
888 hr_startup_data_api_support.chk_startup_action
889 (p_generic_allowed => FALSE
890 ,p_startup_allowed => TRUE
891 ,p_user_allowed => TRUE
892 ,p_business_group_id => p_business_group_id
893 ,p_legislation_code => p_legislation_code
894 ,p_legislation_subgroup => p_legislation_subgroup
895 );
896 ELSE
897 hr_startup_data_api_support.chk_upd_del_startup_action
898 (p_generic_allowed => FALSE
899 ,p_startup_allowed => TRUE
900 ,p_user_allowed => TRUE
901 ,p_business_group_id => p_business_group_id
902 ,p_legislation_code => p_legislation_code
903 ,p_legislation_subgroup => p_legislation_subgroup
904 );
905 END IF;
906 --
907 END chk_startup_action;
908 --
909 -- ----------------------------------------------------------------------------
910 -- |---------------------------< insert_validate >----------------------------|
911 -- ----------------------------------------------------------------------------
912 Procedure insert_validate
913 (p_rec in pay_ppr_shd.g_rec_type
914 ,p_effective_date in date
915 ,p_datetrack_mode in varchar2
916 ,p_validation_start_date in date
917 ,p_validation_end_date in date
918 ,p_processing_rule out nocopy varchar2
919 ,p_formula_mismatch_warning out nocopy boolean
920 ) is
921 --
922 l_proc varchar2(72) := g_package||'insert_validate';
923 l_processing_rule pay_status_processing_rules_f.processing_rule%type;
924 l_formula_mismatch_warning boolean;
925 --
926 Begin
927 hr_utility.set_location('Entering:'||l_proc, 5);
928 --
929 -- Call all supporting business operations
930 --
931 --
932 hr_utility.set_location('Before chk_startup:'||l_proc, 6);
933
934 chk_startup_action(true
935 ,p_rec.business_group_id
936 ,p_rec.legislation_code
937 );
938 IF hr_startup_data_api_support.g_startup_mode
939 NOT IN ('GENERIC','STARTUP') THEN
940 --
941 -- Validate Important Attributes
942 --
943 hr_api.validate_bus_grp_id
944 (p_business_group_id => p_rec.business_group_id
945 ,p_associated_column1 => pay_ppr_shd.g_tab_nam
946 || '.BUSINESS_GROUP_ID');
947 --
948 -- after validating the set of important attributes,
949 -- if Multiple Message Detection is enabled and at least
950 -- one error has been found then abort further validation.
951 --
952 hr_multi_message.end_validation_set;
953 END IF;
954 --
955 --
956 -- Validate Dependent Attributes
957 -- This procedure is used to validate the business group id against the
958 -- parent table
959 --
960 -- ----------------------------------------------------------------------------
961 IF hr_startup_data_api_support.g_startup_mode
962 IN ('STARTUP') THEN
963
964 chk_legislation_code
965 (p_legislation_code => p_rec.legislation_code);
966 End if;
967 --
968 -- ----------------------------------------------------------------------------
969
970 chk_assignment_status_type_id
971 (p_assignment_status_type_id =>p_rec.assignment_status_type_id
972 ,p_business_group_id =>p_rec.business_group_id
973 ,p_legislation_code =>p_rec.legislation_code
974 ,p_element_type_id =>p_rec.element_type_id
975 ,p_formula_id =>p_rec.formula_id
976 );
977
978 --
979 -- ----------------------------------------------------------------------------
980 chk_formula_id
981 (p_business_group_id =>p_rec.business_group_id
982 , p_legislation_code =>p_rec.legislation_code
983 , p_status_processing_rule_id => p_rec.status_processing_rule_id
984 , p_start_date =>p_validation_start_date
985 , p_end_date =>p_validation_end_date
986 , p_element_type_id =>p_rec.element_type_id
987 , p_assignment_status_type_id =>p_rec.assignment_status_type_id
988 , p_formula_id =>p_rec.formula_id
989 ,p_processing_rule =>l_processing_rule
990 , p_formula_mismatch_warning => l_formula_mismatch_warning
991 );
992
993 p_processing_rule := l_processing_rule;
994 p_formula_mismatch_warning := l_formula_mismatch_warning;
995 --
996 -- ----------------------------------------------------------------------------
997 chk_element_type_id
998 (p_effective_date =>p_effective_date
999 ,p_element_type_id =>p_rec.element_type_id
1000 ,p_business_group_id =>p_rec.business_group_id
1001 ,p_legislation_code =>p_rec.legislation_code
1002 );
1003 --
1004 -- ----------------------------------------------------------------------------
1005 chk_unique_rules
1006 (p_effective_date => p_effective_date
1007 ,p_assignment_status_type_id => p_rec.assignment_status_type_id
1008 ,p_processing_rule => l_processing_rule
1009 ,p_element_type_id => p_rec.element_type_id
1010 ,p_status_processing_rule_id => p_rec.status_processing_rule_id
1011 );
1012 --
1013 hr_utility.set_location(' Leaving:'||l_proc, 10);
1014 End insert_validate;
1015 --
1016 -- ----------------------------------------------------------------------------
1017 -- |---------------------------< update_validate >----------------------------|
1018 -- ----------------------------------------------------------------------------
1019 Procedure update_validate
1020 (p_rec in pay_ppr_shd.g_rec_type
1021 ,p_effective_date in date
1022 ,p_datetrack_mode in varchar2
1023 ,p_validation_start_date in date
1024 ,p_validation_end_date in date
1025 ,p_processing_rule out nocopy varchar2
1026 ,p_formula_mismatch_warning out nocopy boolean
1027 ) is
1028 --
1029 l_proc varchar2(72) := g_package||'update_validate';
1030 l_formula_mismatch_warning boolean;
1031 l_processing_rule varchar2(1);
1032 --
1033 Begin
1034 hr_utility.set_location('Entering:'||l_proc, 5);
1035 --
1036 -- Call all supporting business operations
1037 --
1038 --
1039 chk_startup_action(false
1040 ,p_rec.business_group_id
1041 ,p_rec.legislation_code
1042 );
1043 IF hr_startup_data_api_support.g_startup_mode
1044 NOT IN ('GENERIC','STARTUP') THEN
1045 --
1046 -- Validate Important Attributes
1047 --
1048 hr_api.validate_bus_grp_id
1049 (p_business_group_id => p_rec.business_group_id
1050 ,p_associated_column1 => pay_ppr_shd.g_tab_nam
1051 || '.BUSINESS_GROUP_ID');
1052 --
1053 -- After validating the set of important attributes,
1054 -- if Multiple Message Detection is enabled and at least
1055 -- one error has been found then abort further validation.
1056 --
1057 hr_multi_message.end_validation_set;
1058 END IF;
1059 --
1060 --
1061 -- ----------------------------------------------------------------------------
1062 chk_formula_id
1063 (p_business_group_id => p_rec.business_group_id
1064 , p_legislation_code => p_rec.legislation_code
1065 , p_status_processing_rule_id => p_rec.status_processing_rule_id
1066 , p_start_date => p_validation_start_date
1067 , p_end_date => p_validation_end_date
1068 , p_element_type_id => p_rec.element_type_id
1069 , p_assignment_status_type_id => p_rec.assignment_status_type_id
1070 , p_formula_id => p_rec.formula_id
1071 , p_processing_rule => l_processing_rule
1072 , p_formula_mismatch_warning => l_formula_mismatch_warning
1073 );
1074 --
1075 p_processing_rule := l_processing_rule;
1076 p_formula_mismatch_warning := l_formula_mismatch_warning;
1077 --
1078
1079 -- Call the datetrack update integrity operation
1080 --
1081 dt_update_validate
1082 (p_datetrack_mode => p_datetrack_mode
1083 ,p_validation_start_date => p_validation_start_date
1084 ,p_validation_end_date => p_validation_end_date
1085 );
1086 --
1087
1088 chk_non_updateable_args
1089 (p_effective_date => p_effective_date
1090 ,p_rec => p_rec
1091 );
1092 --
1093 --
1094 hr_utility.set_location(' Leaving:'||l_proc, 10);
1095 End update_validate;
1096 --
1097 -- ----------------------------------------------------------------------------
1098 -- |---------------------------< delete_validate >----------------------------|
1099 -- ----------------------------------------------------------------------------
1100 Procedure delete_validate
1101 (p_rec in pay_ppr_shd.g_rec_type
1102 ,p_effective_date in date
1103 ,p_datetrack_mode in varchar2
1104 ,p_validation_start_date in date
1105 ,p_validation_end_date in date
1106 ) is
1107 --
1108 l_proc varchar2(72) := g_package||'delete_validate';
1109 --
1110 Begin
1111 hr_utility.set_location('Entering:'||l_proc, 5);
1112 --
1113 --
1114 chk_startup_action(false
1115 ,pay_ppr_shd.g_old_rec.business_group_id
1116 ,pay_ppr_shd.g_old_rec.legislation_code
1117 );
1118 IF hr_startup_data_api_support.g_startup_mode
1119 NOT IN ('GENERIC','STARTUP') THEN
1120 --
1121 -- Validate Important Attributes
1122 --
1123 --
1124 -- After validating the set of important attributes,
1125 -- if Multiple Message Detection is enabled and at least
1126 -- one error has been found then abort further validation.
1127 --
1128 hr_multi_message.end_validation_set;
1129 END IF;
1130 --
1131 -- Call all supporting business operations
1132 --
1133 dt_delete_validate
1134 (p_datetrack_mode => p_datetrack_mode
1135 ,p_validation_start_date => p_validation_start_date
1136 ,p_validation_end_date => p_validation_end_date
1137 ,p_status_processing_rule_id => p_rec.status_processing_rule_id
1138 );
1139 --
1140 hr_utility.set_location(' Leaving:'||l_proc, 10);
1141 End delete_validate;
1142 --
1143 end pay_ppr_bus;
[Home] [Help]