[Home] [Help]
PACKAGE BODY: APPS.BEN_CTY_BUS
Source
1 Package Body ben_cty_bus as
2 /* $Header: bectyrhi.pkb 120.2 2006/03/30 23:42:52 gsehgal noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cty_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_comptncy_rt_id number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |---------------------------------< chk_comptncy_rt_id >----------------------------|
18 -- ----------------------------------------------------------------------------
19 --
20 -- Description
21 -- This procedure is used to check that the primary key for the table
22 -- is created properly. It should be null on insert and
23 -- should not be able to be updated.
24 --
25 -- Pre Conditions
26 -- None.
27 --
28 -- In Parameters
29 -- comptncy_rt_id PK of record being inserted or updated.
30 -- effective_date Effective Date of session
31 -- object_version_number Object version number of record being
32 -- inserted or updated.
33 --
34 -- Post Success
35 -- Processing continues
36 --
37 -- Post Failure
38 -- Errors handled by the procedure
39 --
40 -- Access Status
41 -- Internal table handler use only.
42 --
43 Procedure chk_comptncy_rt_id(p_comptncy_rt_id in number,
44 p_effective_date in date,
45 p_object_version_number in number) is
46 --
47 l_proc varchar2(72) := g_package||'chk_comptncy_rt_id';
48 l_api_updating boolean;
49 --
50 Begin
51 --
52 hr_utility.set_location('Entering:'||l_proc, 5);
53 --
54 l_api_updating := ben_cty_shd.api_updating
55 (p_effective_date => p_effective_date,
56 p_comptncy_rt_id => p_comptncy_rt_id,
57 p_object_version_number => p_object_version_number);
58 --
59 if (l_api_updating
60 and nvl(p_comptncy_rt_id,hr_api.g_number)
61 <> ben_cty_shd.g_old_rec.comptncy_rt_id) then
62 --
63 -- raise error as PK has changed
64 --
65 ben_cty_shd.constraint_error('BEN_COMPTNCY_RT_F_PK');
66 --
67 elsif not l_api_updating then
68 --
69 -- check if PK is null
70 --
71 if p_comptncy_rt_id is not null then
72 --
73 -- raise error as PK is not null
74 --
75 ben_cty_shd.constraint_error('BEN_COMPTNCY_RT_F_PK');
76 --
77 end if;
78 --
79 end if;
80 --
81 hr_utility.set_location('Leaving:'||l_proc, 10);
82 --
83 End chk_comptncy_rt_id;
84 --
85 -- ----------------------------------------------------------------------------
86 -- |------< chk_competence_id >------|
87 -- ----------------------------------------------------------------------------
88 --
89 -- Description
90 -- This procedure checks that a referenced foreign key actually exists
91 -- in the referenced table.
92 -- Additionally this procedure will check that competence_id is unique
93 -- within the Eligibility profile.
94 --
95 -- Pre-Conditions
96 -- None.
97 --
98 -- In Parameters
99 -- p_comptncy_rt_id PK
100 -- p_competence_id ID of FK column
101 -- p_effective_date session date
102 -- p_object_version_number object version number
103 --
104 -- Post Success
105 -- Processing continues
106 --
107 -- Post Failure
108 -- Error raised.
109 --
110 -- Access Status
111 -- Internal table handler use only.
112 --
113 Procedure chk_competence_id (p_comptncy_rt_id in number,
114 p_competence_id in number,
115 p_vrbl_rt_prfl_id in number,
116 p_validation_start_date in date,
117 p_validation_end_date in date,
118 p_effective_date in date,
119 p_business_group_id in number,
120 p_object_version_number in number) is
121 --
122 l_proc varchar2(72) := g_package||'chk_competence_id';
123 l_api_updating boolean;
124 l_dummy varchar2(1);
125 l_exists varchar2(1);
126 --
127 cursor c1 is
128 select null
129 from per_competences a
130 where a.competence_id = p_competence_id
131 and nvl(a.business_group_id, p_business_group_id) = p_business_group_id
132 and p_effective_date between a.date_from and
133 nvl(a.date_to, p_effective_date);
134 --
135 cursor c3 is
136 select null
137 from ben_comptncy_rt_f
138 where competence_id = p_competence_id
139 and vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
140 -- changed for Bug 5078478 .. change the commented query to the new one
141 -- and vrbl_rt_prfl_id <> nvl(p_comptncy_rt_id,hr_api.g_number)
142 and comptncy_rt_id <> nvl(p_comptncy_rt_id,hr_api.g_number)
143 -- changed end for Bug 5078478
144 and business_group_id + 0 = p_business_group_id
145 and p_validation_start_date <= effective_end_date
146 and p_validation_end_date >= effective_start_date;
147 --
148 --
149 Begin
150 --
151 hr_utility.set_location('Entering:'||l_proc,5);
152 --
153 l_api_updating := ben_cty_shd.api_updating
154 (p_comptncy_rt_id => p_comptncy_rt_id,
155 p_effective_date => p_effective_date,
156 p_object_version_number => p_object_version_number);
157 --
158 if (l_api_updating
159 and nvl(p_competence_id,hr_api.g_number)
160 <> nvl(ben_cty_shd.g_old_rec.competence_id,hr_api.g_number)
161 or not l_api_updating) then
162 --
163 -- check if competence_id value exists in per_competences table
164 --
165 open c1;
166 --
167 fetch c1 into l_dummy;
168 if c1%notfound then
169 --
170 close c1;
171 --
172 -- raise error as FK does not relate to PK in per_jobs
173 -- table.
174 --
175 ben_cty_shd.constraint_error('BEN_COMPTNCY_RT_FK2');
176 --
177 end if;
178 --
179 close c1;
180 --
181 open c3;
182 fetch c3 into l_exists;
183 if c3%found then
184 close c3;
185 --
186 -- raise error as this job already exists for this profile
187 --
188 fnd_message.set_name('BEN', 'BEN_92992_DUPS_ROW');
189 fnd_message.set_token('VAR1','Competency criteria');
190 fnd_message.set_token('VAR2','Variable Rate Profile');
191 fnd_message.raise_error;
192 --
193 end if;
194 close c3;
195 --
196 --
197 end if;
198 --
199 hr_utility.set_location('Leaving:'||l_proc,10);
200 --
201 End chk_competence_id;
202
203 --
204 -- ----------------------------------------------------------------------------
205 -- |------< chk_rating_level_id >------|
206 -- ----------------------------------------------------------------------------
207 --
208 -- Description
209 -- This procedure checks that a referenced foreign key actually exists
210 -- in the referenced table.
211 -- Additionally this procedure will check that rating_level_id is unique
212 -- within the variable rate profile.
213 --
214 -- Pre-Conditions
215 -- None.
216 --
217 -- In Parameters
218 -- p_comptncy_rt_id PK
219 -- p_competence_id ID of FK column
220 -- p_rating_level_id ID of FK column
221 -- p_effective_date session date
222 -- p_object_version_number object version number
223 --
224 -- Post Success
225 -- Processing continues
226 --
227 -- Post Failure
228 -- Error raised.
229 --
230 -- Access Status
231 -- Internal table handler use only.
232 --
233 Procedure chk_rating_level_id (p_comptncy_rt_id in number,
234 p_competence_id in number,
235 p_rating_level_id in number,
236 p_vrbl_rt_prfl_id in number,
237 p_validation_start_date in date,
238 p_validation_end_date in date,
239 p_effective_date in date,
240 p_business_group_id in number,
241 p_object_version_number in number) is
242 --
243 l_proc varchar2(72) := g_package||'chk_rating_level_id';
244 l_api_updating boolean;
245 l_dummy varchar2(1);
246 l_exists varchar2(1);
247 --
248 cursor c1 is
249 select null
250 from per_rating_levels a,
251 per_competences b
252 where b.competence_id = p_competence_id
253 and (a.competence_id = p_competence_id or
254 a.rating_scale_id = b.rating_scale_id )
255 and a.rating_level_id = p_rating_level_id
256 and nvl(a.business_group_id , p_business_group_id) = p_business_group_id
257 and p_effective_date between b.date_from and nvl(b.date_to, p_effective_date)
258 and nvl(b.business_group_id , p_business_group_id) = p_business_group_id;
259 --
260 cursor c3 is
261 select null
262 from ben_comptncy_rt_f
263 where competence_id = p_competence_id
264 and rating_level_id = p_rating_level_id
265 and vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
266 -- changed for Bug 5078478 .. change the commented query to the new one
267 -- and vrbl_rt_prfl_id <> nvl(p_comptncy_rt_id,hr_api.g_number)
268 and comptncy_rt_id <> nvl(p_comptncy_rt_id,hr_api.g_number)
269 -- -- changed end for Bug 5078478
270 and business_group_id + 0 = p_business_group_id
271 and p_validation_start_date <= effective_end_date
272 and p_validation_end_date >= effective_start_date;
273 --
274 --
275 Begin
276 --
277 hr_utility.set_location('Entering:'||l_proc,5);
278 --
279 l_api_updating := ben_cty_shd.api_updating
280 (p_comptncy_rt_id => p_comptncy_rt_id,
281 p_effective_date => p_effective_date,
282 p_object_version_number => p_object_version_number);
283 --
284 if (l_api_updating
285 and nvl(p_competence_id,hr_api.g_number)
286 <> nvl(ben_cty_shd.g_old_rec.rating_level_id,hr_api.g_number)
287 or not l_api_updating) then
288 --
289 -- check if rating_level_id value exists in per_rating_levels table
290 --
291 open c1;
292 --
293 fetch c1 into l_dummy;
294 if c1%notfound then
295 --
296 close c1;
297 --
298 -- raise error as FK does not relate to PK in per_jobs
299 -- table.
300 --
301 ben_cty_shd.constraint_error('BEN_COMPTNCY_RT_FK2');
302 --
303 end if;
304 --
305 close c1;
306 --
307 open c3;
308 fetch c3 into l_exists;
309 if c3%found then
310 close c3;
311 --
312 -- raise error as this competnecy already exists for this profile
313 --
314 fnd_message.set_name('BEN', 'BEN_92992_DUPS_ROW');
315 fnd_message.set_token('VAR1','Competency criteria');
316 fnd_message.set_token('VAR2','Variable Rate Profile');
317 fnd_message.raise_error;
318 --
319 end if;
320 close c3;
321 --
322 --
323 end if;
324 --
325 hr_utility.set_location('Leaving:'||l_proc,10);
326 --
327 End chk_rating_level_id;
328
329
330 --
331 -- ----------------------------------------------------------------------------
332 -- |------< chk_excld_flag >------|
333 -- ----------------------------------------------------------------------------
334 --
335 -- Description
336 -- This procedure is used to check that the lookup value is valid.
337 --
338 -- Pre Conditions
339 -- None.
340 --
341 -- In Parameters
342 -- comptncy_rt_id PK of record being inserted or updated.
343 -- excld_flag Value of lookup code.
344 -- effective_date effective date
345 -- object_version_number Object version number of record being
346 -- inserted or updated.
347 --
348 -- Post Success
349 -- Processing continues
350 --
351 -- Post Failure
352 -- Error handled by procedure
353 --
354 -- Access Status
355 -- Internal table handler use only.
356 --
357 Procedure chk_excld_flag(p_comptncy_rt_id in number,
358 p_excld_flag in varchar2,
359 p_effective_date in date,
360 p_object_version_number in number) is
361 --
362 l_proc varchar2(72) := g_package||'chk_excld_flag';
363 l_api_updating boolean;
364 --
365 Begin
366 --
367 hr_utility.set_location('Entering:'||l_proc, 5);
368 --
369 l_api_updating := ben_cty_shd.api_updating
370 (p_comptncy_rt_id => p_comptncy_rt_id,
371 p_effective_date => p_effective_date,
372 p_object_version_number => p_object_version_number);
373 --
374 if (l_api_updating
375 and p_excld_flag
376 <> nvl(ben_cty_shd.g_old_rec.excld_flag,hr_api.g_varchar2)
377 or not l_api_updating) then
378 --
379 -- check if value of lookup falls within lookup type.
380 --
381 --
382 if hr_api.not_exists_in_hr_lookups
383 (p_lookup_type => 'YES_NO',
384 p_lookup_code => p_excld_flag,
385 p_effective_date => p_effective_date) then
386 --
387 -- raise error as does not exist as lookup
388 --
389 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
390 fnd_message.raise_error;
391 --
392 end if;
393 --
394 end if;
395 --
396 hr_utility.set_location('Leaving:'||l_proc,10);
397 --
398 end chk_excld_flag;
399 --
400 -- added for Bug 5078478 .. add this procedure to check the duplicate seq no
401 -- |--------------------< chk_duplicate_ordr_num >----------------------------|
402 -- ----------------------------------------------------------------------------
403 --
404 -- Description
405 --
406 -- Pre Conditions
407 -- None.
408 --
409 -- In Parameters
410 -- p_comptncy_rt_id
411 -- p_vrbl_rt_prfl_id
412 -- p_ordr_num
413 -- p_effective_date
414 -- p_business_group_id
415 --
416 -- Post Success
417 -- Processing continues
418 --
419 -- Post Failure
420 -- Errors handled by the procedure
421 --
422 -- Access Status
423 -- Internal table handler use only.
424 --
425 -- ----------------------------------------------------------------------------
426
427
428 procedure chk_duplicate_ordr_num
429 (p_vrbl_rt_prfl_id in number
430 ,p_comptncy_rt_id in number
431 ,p_ordr_num in number
432 ,p_validation_start_date in date
433 ,p_validation_end_date in date
434 ,p_business_group_id in number)
435 is
436 l_proc varchar2(72) := g_package||' chk_duplicate_ordr_num ';
437 l_dummy char(1);
438 cursor c1 is select null
439 from ben_comptncy_rt_f
440 where vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
441 -- changed against bug: 5113011
442 and comptncy_rt_id <> nvl(p_comptncy_rt_id,-1)
443 -- and p_competence_id <> nvl(p_p_competence_id ,-1)
444 --and p_effective_date between effective_start_date
445 -- and effective_end_date
446 and p_validation_start_date <= effective_end_date
447 and p_validation_end_date >= effective_start_date
448 and business_group_id + 0 = p_business_group_id
449 and ordr_num = p_ordr_num;
450 --
451 Begin
452 hr_utility.set_location('Entering:'||l_proc, 5);
453
454 --
455 open c1;
456 fetch c1 into l_dummy;
457 --
458 if c1%found then
459 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
460 fnd_message.raise_error;
461 end if;
462 close c1;
463 --
464 hr_utility.set_location('Leaving:'||l_proc, 15);
465 End chk_duplicate_ordr_num;
466
467 --
468 -- ---------------------------------------------------------------------------
469 -- |----------------------< set_security_group_id >--------------------------|
470 -- ---------------------------------------------------------------------------
471 --
472 Procedure set_security_group_id
473 (p_comptncy_rt_id in number
474 ,p_associated_column1 in varchar2 default null
475 ) is
476 --
477 -- Declare cursor
478 --
479 cursor csr_sec_grp is
480 select pbg.security_group_id
481 from per_business_groups pbg
482 , ben_comptncy_rt_f cty
483 where cty.comptncy_rt_id = p_comptncy_rt_id
484 and pbg.business_group_id = cty.business_group_id;
485 --
486 -- Declare local variables
487 --
488 l_security_group_id number;
489 l_proc varchar2(72) := g_package||'set_security_group_id';
490 --
491 begin
492 --
493 hr_utility.set_location('Entering:'|| l_proc, 10);
494 --
495 -- Ensure that all the mandatory parameter are not null
496 --
497 hr_api.mandatory_arg_error
498 (p_api_name => l_proc
499 ,p_argument => 'comptncy_rt_id'
500 ,p_argument_value => p_comptncy_rt_id
501 );
502 --
503 open csr_sec_grp;
504 fetch csr_sec_grp into l_security_group_id;
505 --
506 if csr_sec_grp%notfound then
507 --
508 close csr_sec_grp;
509 --
510 -- The primary key is invalid therefore we must error
511 --
512 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
513 hr_multi_message.add
514 (p_associated_column1
515 => nvl(p_associated_column1,'comptncy_rt_ID')
516 );
517 --
518 else
519 close csr_sec_grp;
520 --
521 -- Set the security_group_id in CLIENT_INFO
522 --
523 hr_api.set_security_group_id
524 (p_security_group_id => l_security_group_id
525 );
526 end if;
527 --
528 hr_utility.set_location(' Leaving:'|| l_proc, 20);
529 --
530 end set_security_group_id;
531 --
532 -- ---------------------------------------------------------------------------
533 -- |---------------------< return_legislation_code >-------------------------|
534 -- ---------------------------------------------------------------------------
535 --
536 Function return_legislation_code
537 (p_comptncy_rt_id in number
538 )
539 Return Varchar2 Is
540 --
541 -- Declare cursor
542 --
543 cursor csr_leg_code is
544 select pbg.legislation_code
545 from per_business_groups pbg
546 , ben_comptncy_rt_f cty
547 where cty.comptncy_rt_id = p_comptncy_rt_id
548 and pbg.business_group_id = cty.business_group_id;
549 --
550 -- Declare local variables
551 --
552 l_legislation_code varchar2(150);
553 l_proc varchar2(72) := g_package||'return_legislation_code';
554 --
555 Begin
556 --
557 hr_utility.set_location('Entering:'|| l_proc, 10);
558 --
559 -- Ensure that all the mandatory parameter are not null
560 --
561 hr_api.mandatory_arg_error
562 (p_api_name => l_proc
563 ,p_argument => 'comptncy_rt_id'
564 ,p_argument_value => p_comptncy_rt_id
565 );
566 --
567 if ( nvl(ben_cty_bus.g_comptncy_rt_id, hr_api.g_number)
568 = p_comptncy_rt_id) then
569 --
570 -- The legislation code has already been found with a previous
571 -- call to this function. Just return the value in the global
572 -- variable.
573 --
574 l_legislation_code := ben_cty_bus.g_legislation_code;
575 hr_utility.set_location(l_proc, 20);
576 else
577 --
578 -- The ID is different to the last call to this function
579 -- or this is the first call to this function.
580 --
581 open csr_leg_code;
582 fetch csr_leg_code into l_legislation_code;
583 --
584 if csr_leg_code%notfound then
585 --
586 -- The primary key is invalid therefore we must error
587 --
588 close csr_leg_code;
589 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
590 fnd_message.raise_error;
591 end if;
595 -- available for the next call to this function.
592 hr_utility.set_location(l_proc,30);
593 --
594 -- Set the global variables so the values are
596 --
597 close csr_leg_code;
598 ben_cty_bus.g_comptncy_rt_id := p_comptncy_rt_id;
599 ben_cty_bus.g_legislation_code := l_legislation_code;
600 end if;
601 hr_utility.set_location(' Leaving:'|| l_proc, 40);
602 return l_legislation_code;
603 end return_legislation_code;
604 --
605 -- ----------------------------------------------------------------------------
606 -- |------------------------------< chk_df >----------------------------------|
607 -- ----------------------------------------------------------------------------
608 --
609 -- Description:
610 -- Validates all the Descriptive Flexfield values.
611 --
612 -- Prerequisites:
613 -- All other columns have been validated. Must be called as the
614 -- last step from insert_validate and update_validate.
615 --
616 -- In Arguments:
617 -- p_rec
618 --
619 -- Post Success:
620 -- If the Descriptive Flexfield structure column and data values are
621 -- all valid this procedure will end normally and processing will
622 -- continue.
623 --
624 -- Post Failure:
625 -- If the Descriptive Flexfield structure column value or any of
626 -- the data values are invalid then an application error is raised as
627 -- a PL/SQL exception.
628 --
629 -- Access Status:
630 -- Internal Row Handler Use Only.
631 --
632 -- ----------------------------------------------------------------------------
633 procedure chk_df
634 (p_rec in ben_cty_shd.g_rec_type
635 ) is
636 --
637 l_proc varchar2(72) := g_package || 'chk_df';
638 --
639 begin
640 hr_utility.set_location('Entering:'||l_proc,10);
641 --
642 if ((p_rec.comptncy_rt_id is not null) and (
643 nvl(ben_cty_shd.g_old_rec.cty_attribute_category, hr_api.g_varchar2) <>
644 nvl(p_rec.cty_attribute_category, hr_api.g_varchar2) or
645 nvl(ben_cty_shd.g_old_rec.cty_attribute1, hr_api.g_varchar2) <>
646 nvl(p_rec.cty_attribute1, hr_api.g_varchar2) or
647 nvl(ben_cty_shd.g_old_rec.cty_attribute2, hr_api.g_varchar2) <>
648 nvl(p_rec.cty_attribute2, hr_api.g_varchar2) or
649 nvl(ben_cty_shd.g_old_rec.cty_attribute3, hr_api.g_varchar2) <>
650 nvl(p_rec.cty_attribute3, hr_api.g_varchar2) or
651 nvl(ben_cty_shd.g_old_rec.cty_attribute4, hr_api.g_varchar2) <>
652 nvl(p_rec.cty_attribute4, hr_api.g_varchar2) or
653 nvl(ben_cty_shd.g_old_rec.cty_attribute5, hr_api.g_varchar2) <>
654 nvl(p_rec.cty_attribute5, hr_api.g_varchar2) or
655 nvl(ben_cty_shd.g_old_rec.cty_attribute6, hr_api.g_varchar2) <>
656 nvl(p_rec.cty_attribute6, hr_api.g_varchar2) or
657 nvl(ben_cty_shd.g_old_rec.cty_attribute7, hr_api.g_varchar2) <>
658 nvl(p_rec.cty_attribute7, hr_api.g_varchar2) or
659 nvl(ben_cty_shd.g_old_rec.cty_attribute8, hr_api.g_varchar2) <>
660 nvl(p_rec.cty_attribute8, hr_api.g_varchar2) or
661 nvl(ben_cty_shd.g_old_rec.cty_attribute9, hr_api.g_varchar2) <>
662 nvl(p_rec.cty_attribute9, hr_api.g_varchar2) or
663 nvl(ben_cty_shd.g_old_rec.cty_attribute10, hr_api.g_varchar2) <>
664 nvl(p_rec.cty_attribute10, hr_api.g_varchar2) or
665 nvl(ben_cty_shd.g_old_rec.cty_attribute11, hr_api.g_varchar2) <>
666 nvl(p_rec.cty_attribute11, hr_api.g_varchar2) or
667 nvl(ben_cty_shd.g_old_rec.cty_attribute12, hr_api.g_varchar2) <>
668 nvl(p_rec.cty_attribute12, hr_api.g_varchar2) or
669 nvl(ben_cty_shd.g_old_rec.cty_attribute13, hr_api.g_varchar2) <>
670 nvl(p_rec.cty_attribute13, hr_api.g_varchar2) or
671 nvl(ben_cty_shd.g_old_rec.cty_attribute14, hr_api.g_varchar2) <>
672 nvl(p_rec.cty_attribute14, hr_api.g_varchar2) or
673 nvl(ben_cty_shd.g_old_rec.cty_attribute15, hr_api.g_varchar2) <>
674 nvl(p_rec.cty_attribute15, hr_api.g_varchar2) or
675 nvl(ben_cty_shd.g_old_rec.cty_attribute16, hr_api.g_varchar2) <>
676 nvl(p_rec.cty_attribute16, hr_api.g_varchar2) or
677 nvl(ben_cty_shd.g_old_rec.cty_attribute17, hr_api.g_varchar2) <>
678 nvl(p_rec.cty_attribute17, hr_api.g_varchar2) or
679 nvl(ben_cty_shd.g_old_rec.cty_attribute18, hr_api.g_varchar2) <>
680 nvl(p_rec.cty_attribute18, hr_api.g_varchar2) or
681 nvl(ben_cty_shd.g_old_rec.cty_attribute19, hr_api.g_varchar2) <>
682 nvl(p_rec.cty_attribute19, hr_api.g_varchar2) or
683 nvl(ben_cty_shd.g_old_rec.cty_attribute20, hr_api.g_varchar2) <>
684 nvl(p_rec.cty_attribute20, hr_api.g_varchar2) or
685 nvl(ben_cty_shd.g_old_rec.cty_attribute21, hr_api.g_varchar2) <>
686 nvl(p_rec.cty_attribute21, hr_api.g_varchar2) or
687 nvl(ben_cty_shd.g_old_rec.cty_attribute22, hr_api.g_varchar2) <>
688 nvl(p_rec.cty_attribute22, hr_api.g_varchar2) or
689 nvl(ben_cty_shd.g_old_rec.cty_attribute23, hr_api.g_varchar2) <>
690 nvl(p_rec.cty_attribute23, hr_api.g_varchar2) or
691 nvl(ben_cty_shd.g_old_rec.cty_attribute24, hr_api.g_varchar2) <>
692 nvl(p_rec.cty_attribute24, hr_api.g_varchar2) or
693 nvl(ben_cty_shd.g_old_rec.cty_attribute25, hr_api.g_varchar2) <>
694 nvl(p_rec.cty_attribute25, hr_api.g_varchar2) or
695 nvl(ben_cty_shd.g_old_rec.cty_attribute26, hr_api.g_varchar2) <>
696 nvl(p_rec.cty_attribute26, hr_api.g_varchar2) or
697 nvl(ben_cty_shd.g_old_rec.cty_attribute27, hr_api.g_varchar2) <>
698 nvl(p_rec.cty_attribute27, hr_api.g_varchar2) or
699 nvl(ben_cty_shd.g_old_rec.cty_attribute28, hr_api.g_varchar2) <>
703 nvl(ben_cty_shd.g_old_rec.cty_attribute30, hr_api.g_varchar2) <>
700 nvl(p_rec.cty_attribute28, hr_api.g_varchar2) or
701 nvl(ben_cty_shd.g_old_rec.cty_attribute29, hr_api.g_varchar2) <>
702 nvl(p_rec.cty_attribute29, hr_api.g_varchar2) or
704 nvl(p_rec.cty_attribute30, hr_api.g_varchar2) ))
705 or (p_rec.comptncy_rt_id is null) then
706 --
707 -- Only execute the validation if absolutely necessary:
708 -- a) During update, the structure column value or any
709 -- of the attribute values have actually changed.
710 -- b) During insert.
711 --
712 hr_dflex_utility.ins_or_upd_descflex_attribs
713 (p_appl_short_name => 'BEN'
714 ,p_descflex_name => 'BEN_comptncy_rt_F'
715 ,p_attribute_category => 'cty_ATTRIBUTE_CATEGORY'
716 ,p_attribute1_name => 'cty_ATTRIBUTE1'
717 ,p_attribute1_value => p_rec.cty_attribute1
718 ,p_attribute2_name => 'cty_ATTRIBUTE2'
719 ,p_attribute2_value => p_rec.cty_attribute2
720 ,p_attribute3_name => 'cty_ATTRIBUTE3'
721 ,p_attribute3_value => p_rec.cty_attribute3
722 ,p_attribute4_name => 'cty_ATTRIBUTE4'
723 ,p_attribute4_value => p_rec.cty_attribute4
724 ,p_attribute5_name => 'cty_ATTRIBUTE5'
725 ,p_attribute5_value => p_rec.cty_attribute5
726 ,p_attribute6_name => 'cty_ATTRIBUTE6'
727 ,p_attribute6_value => p_rec.cty_attribute6
728 ,p_attribute7_name => 'cty_ATTRIBUTE7'
729 ,p_attribute7_value => p_rec.cty_attribute7
730 ,p_attribute8_name => 'cty_ATTRIBUTE8'
731 ,p_attribute8_value => p_rec.cty_attribute8
732 ,p_attribute9_name => 'cty_ATTRIBUTE9'
733 ,p_attribute9_value => p_rec.cty_attribute9
734 ,p_attribute10_name => 'cty_ATTRIBUTE10'
735 ,p_attribute10_value => p_rec.cty_attribute10
736 ,p_attribute11_name => 'cty_ATTRIBUTE11'
737 ,p_attribute11_value => p_rec.cty_attribute11
738 ,p_attribute12_name => 'cty_ATTRIBUTE12'
739 ,p_attribute12_value => p_rec.cty_attribute12
740 ,p_attribute13_name => 'cty_ATTRIBUTE13'
741 ,p_attribute13_value => p_rec.cty_attribute13
742 ,p_attribute14_name => 'cty_ATTRIBUTE14'
743 ,p_attribute14_value => p_rec.cty_attribute14
744 ,p_attribute15_name => 'cty_ATTRIBUTE15'
745 ,p_attribute15_value => p_rec.cty_attribute15
746 ,p_attribute16_name => 'cty_ATTRIBUTE16'
747 ,p_attribute16_value => p_rec.cty_attribute16
748 ,p_attribute17_name => 'cty_ATTRIBUTE17'
749 ,p_attribute17_value => p_rec.cty_attribute17
750 ,p_attribute18_name => 'cty_ATTRIBUTE18'
751 ,p_attribute18_value => p_rec.cty_attribute18
752 ,p_attribute19_name => 'cty_ATTRIBUTE19'
753 ,p_attribute19_value => p_rec.cty_attribute19
754 ,p_attribute20_name => 'cty_ATTRIBUTE20'
755 ,p_attribute20_value => p_rec.cty_attribute20
756 ,p_attribute21_name => 'cty_ATTRIBUTE21'
757 ,p_attribute21_value => p_rec.cty_attribute21
758 ,p_attribute22_name => 'cty_ATTRIBUTE22'
759 ,p_attribute22_value => p_rec.cty_attribute22
760 ,p_attribute23_name => 'cty_ATTRIBUTE23'
761 ,p_attribute23_value => p_rec.cty_attribute23
762 ,p_attribute24_name => 'cty_ATTRIBUTE24'
763 ,p_attribute24_value => p_rec.cty_attribute24
764 ,p_attribute25_name => 'cty_ATTRIBUTE25'
765 ,p_attribute25_value => p_rec.cty_attribute25
766 ,p_attribute26_name => 'cty_ATTRIBUTE26'
767 ,p_attribute26_value => p_rec.cty_attribute26
768 ,p_attribute27_name => 'cty_ATTRIBUTE27'
769 ,p_attribute27_value => p_rec.cty_attribute27
770 ,p_attribute28_name => 'cty_ATTRIBUTE28'
771 ,p_attribute28_value => p_rec.cty_attribute28
772 ,p_attribute29_name => 'cty_ATTRIBUTE29'
773 ,p_attribute29_value => p_rec.cty_attribute29
774 ,p_attribute30_name => 'cty_ATTRIBUTE30'
775 ,p_attribute30_value => p_rec.cty_attribute30
776 );
777 end if;
778 --
779 hr_utility.set_location(' Leaving:'||l_proc,20);
780 end chk_df;
781 --
782 -- ----------------------------------------------------------------------------
783 -- |-----------------------< chk_non_updateable_args >------------------------|
784 -- ----------------------------------------------------------------------------
785 -- {Start Of Comments}
786 --
787 -- Description:
788 -- This procedure is used to ensure that non updateable attributes have
789 -- not been updated. If an attribute has been updated an error is generated.
790 --
791 -- Pre Conditions:
795 -- In Arguments:
792 -- g_old_rec has been populated with details of the values currently in
793 -- the database.
794 --
796 -- p_rec has been populated with the updated values the user would like the
797 -- record set to.
798 --
799 -- Post Success:
800 -- Processing continues if all the non updateable attributes have not
801 -- changed.
802 --
803 -- Post Failure:
804 -- An application error is raised if any of the non updatable attributes
805 -- have been altered.
806 --
807 -- {End Of Comments}
808 -- ----------------------------------------------------------------------------
809 Procedure chk_non_updateable_args
810 (p_effective_date in date
811 ,p_rec in ben_cty_shd.g_rec_type
812 ) IS
813 --
814 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
815 --
816 Begin
817 --
818 -- Only proceed with the validation if a row exists for the current
819 -- record in the HR Schema.
820 --
821 IF NOT ben_cty_shd.api_updating
822 (p_comptncy_rt_id => p_rec.comptncy_rt_id
823 ,p_effective_date => p_effective_date
824 ,p_object_version_number => p_rec.object_version_number
825 ) THEN
826 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
827 fnd_message.set_token('PROCEDURE ', l_proc);
828 fnd_message.set_token('STEP ', '5');
829 fnd_message.raise_error;
830 END IF;
831 --
832 -- EDIT_HERE: Add checks to ensure non-updateable args have
833 -- not been updated.
834 --
835 End chk_non_updateable_args;
836 --
837 -- ----------------------------------------------------------------------------
838 -- |--------------------------< dt_update_validate >--------------------------|
839 -- ----------------------------------------------------------------------------
840 -- {Start Of Comments}
841 --
842 -- Description:
843 -- This procedure is used for referential integrity of datetracked
844 -- parent entities when a datetrack update operation is taking place
845 -- and where there is no cascading of update defined for this entity.
846 --
847 -- Prerequisites:
848 -- This procedure is called from the update_validate.
849 --
850 -- In Parameters:
851 --
852 -- Post Success:
853 -- Processing continues.
854 --
855 -- Post Failure:
856 --
857 -- Developer Implementation Notes:
858 -- This procedure should not need maintenance unless the HR Schema model
859 -- changes.
860 --
861 -- Access Status:
862 -- Internal Row Handler Use Only.
863 --
864 -- {End Of Comments}
865 -- ----------------------------------------------------------------------------
866 Procedure dt_update_validate
867 (p_vrbl_rt_prfl_id in number default hr_api.g_number
868 ,p_datetrack_mode in varchar2
869 ,p_validation_start_date in date
870 ,p_validation_end_date in date
871 ) Is
872 --
873 l_proc varchar2(72) := g_package||'dt_update_validate';
874 --
875 Begin
876 --
877 -- Ensure that the p_datetrack_mode argument is not null
878 --
879 hr_api.mandatory_arg_error
880 (p_api_name => l_proc
881 ,p_argument => 'datetrack_mode'
882 ,p_argument_value => p_datetrack_mode
883 );
884 --
885 -- Mode will be valid, as this is checked at the start of the upd.
886 --
887 -- Ensure the arguments are not null
888 --
889 hr_api.mandatory_arg_error
890 (p_api_name => l_proc
891 ,p_argument => 'validation_start_date'
892 ,p_argument_value => p_validation_start_date
893 );
894 --
895 hr_api.mandatory_arg_error
896 (p_api_name => l_proc
897 ,p_argument => 'validation_end_date'
898 ,p_argument_value => p_validation_end_date
899 );
900 --
901 If ((nvl(p_vrbl_rt_prfl_id, hr_api.g_number) <> hr_api.g_number) and
902 NOT (dt_api.check_min_max_dates
903 (p_base_table_name => 'ben_vrbl_rt_prfl_f'
904 ,p_base_key_column => 'VRBL_RT_PRFL_ID'
905 ,p_base_key_value => p_vrbl_rt_prfl_id
906 ,p_from_date => p_validation_start_date
907 ,p_to_date => p_validation_end_date))) Then
908 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
909 fnd_message.set_token('TABLE_NAME','vrbl rt prfl');
910 hr_multi_message.add
911 (p_associated_column1 => ben_cty_shd.g_tab_nam || '.VRBL_RT_PRFL_ID');
912 End If;
913 --
914 Exception
915 When Others Then
916 --
917 -- An unhandled or unexpected error has occurred which
918 -- we must report
919 --
920 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
921 fnd_message.set_token('PROCEDURE', l_proc);
922 fnd_message.set_token('STEP','15');
923 fnd_message.raise_error;
924 End dt_update_validate;
925 --
926 -- ----------------------------------------------------------------------------
927 -- |--------------------------< dt_delete_validate >--------------------------|
928 -- ----------------------------------------------------------------------------
929 -- {Start Of Comments}
933 -- child entities when either a datetrack DELETE or ZAP is in operation
930 --
931 -- Description:
932 -- This procedure is used for referential integrity of datetracked
934 -- and where there is no cascading of delete defined for this entity.
935 -- For the datetrack mode of DELETE or ZAP we must ensure that no
936 -- datetracked child rows exist between the validation start and end
937 -- dates.
938 --
939 -- Prerequisites:
940 -- This procedure is called from the delete_validate.
941 --
942 -- In Parameters:
943 --
944 -- Post Success:
945 -- Processing continues.
946 --
947 -- Post Failure:
948 -- If a row exists by determining the returning Boolean value from the
949 -- generic dt_api.rows_exist function then we must supply an error via
950 -- the use of the local exception handler l_rows_exist.
951 --
952 -- Developer Implementation Notes:
953 -- This procedure should not need maintenance unless the HR Schema model
954 -- changes.
955 --
956 -- Access Status:
957 -- Internal Row Handler Use Only.
958 --
959 -- {End Of Comments}
960 -- ----------------------------------------------------------------------------
961 Procedure dt_delete_validate
962 (p_comptncy_rt_id in number
963 ,p_datetrack_mode in varchar2
964 ,p_validation_start_date in date
965 ,p_validation_end_date in date
966 ) Is
967 --
968 l_proc varchar2(72) := g_package||'dt_delete_validate';
969 --
970 Begin
971 --
972 -- Ensure that the p_datetrack_mode argument is not null
973 --
974 hr_api.mandatory_arg_error
975 (p_api_name => l_proc
976 ,p_argument => 'datetrack_mode'
977 ,p_argument_value => p_datetrack_mode
978 );
979 --
980 -- Only perform the validation if the datetrack mode is either
981 -- DELETE or ZAP
982 --
983 If (p_datetrack_mode = hr_api.g_delete or
984 p_datetrack_mode = hr_api.g_zap) then
985 --
986 --
987 -- Ensure the arguments are not null
988 --
989 hr_api.mandatory_arg_error
990 (p_api_name => l_proc
991 ,p_argument => 'validation_start_date'
992 ,p_argument_value => p_validation_start_date
993 );
994 --
995 hr_api.mandatory_arg_error
996 (p_api_name => l_proc
997 ,p_argument => 'validation_end_date'
998 ,p_argument_value => p_validation_end_date
999 );
1000 --
1001 hr_api.mandatory_arg_error
1002 (p_api_name => l_proc
1003 ,p_argument => 'comptncy_rt_id'
1004 ,p_argument_value => p_comptncy_rt_id
1005 );
1006 --
1007 --
1008 --
1009 End If;
1010 --
1011 Exception
1012 When Others Then
1013 --
1014 -- An unhandled or unexpected error has occurred which
1015 -- we must report
1016 --
1017 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1018 fnd_message.set_token('PROCEDURE', l_proc);
1019 fnd_message.set_token('STEP','15');
1020 fnd_message.raise_error;
1021 --
1022 End dt_delete_validate;
1023 --
1024 -- ----------------------------------------------------------------------------
1025 -- |---------------------------< insert_validate >----------------------------|
1026 -- ----------------------------------------------------------------------------
1027 Procedure insert_validate
1028 (p_rec in ben_cty_shd.g_rec_type
1029 ,p_effective_date in date
1030 ,p_datetrack_mode in varchar2
1031 ,p_validation_start_date in date
1032 ,p_validation_end_date in date
1033 ) is
1034 --
1035 l_proc varchar2(72) := g_package||'insert_validate';
1036 --
1037 Begin
1038 hr_utility.set_location('Entering:'||l_proc, 5);
1039 --
1040 -- Call all supporting business operations
1041 --
1042 hr_api.validate_bus_grp_id
1043 (p_business_group_id => p_rec.business_group_id
1044 ,p_associated_column1 => ben_cty_shd.g_tab_nam
1045 || '.BUSINESS_GROUP_ID');
1046 --
1047
1048 chk_comptncy_rt_id
1049 (p_comptncy_rt_id => p_rec.comptncy_rt_id,
1050 p_effective_date => p_effective_date,
1051 p_object_version_number => p_rec.object_version_number);
1052 --
1053 chk_competence_id
1054 (p_comptncy_rt_id => p_rec.comptncy_rt_id,
1055 p_competence_id => p_rec.competence_id,
1056 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1057 p_validation_start_date => p_validation_start_date,
1058 p_validation_end_date => p_validation_end_date,
1059 p_effective_date => p_effective_date,
1060 p_business_group_id => p_rec.business_group_id,
1061 p_object_version_number => p_rec.object_version_number);
1062 --
1063 chk_rating_level_id
1064 (p_comptncy_rt_id => p_rec.comptncy_rt_id,
1065 p_competence_id => p_rec.competence_id,
1066 p_rating_level_id => p_rec.rating_level_id,
1067 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1068 p_validation_start_date => p_validation_start_date,
1072 p_object_version_number => p_rec.object_version_number);
1069 p_validation_end_date => p_validation_end_date,
1070 p_effective_date => p_effective_date,
1071 p_business_group_id => p_rec.business_group_id,
1073 --
1074 chk_excld_flag
1075 (p_comptncy_rt_id => p_rec.comptncy_rt_id,
1076 p_excld_flag => p_rec.excld_flag,
1077 p_effective_date => p_effective_date,
1078 p_object_version_number => p_rec.object_version_number);
1079 --
1080 -- added for Bug 5078478 .. added the check for checking duplicate seq no.
1081 chk_duplicate_ordr_num
1082 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
1083 ,p_comptncy_rt_id => p_rec.comptncy_rt_id
1084 ,p_ordr_num => p_rec.ordr_num
1085 ,p_validation_start_date => p_validation_start_date
1086 ,p_validation_end_date => p_validation_end_date
1087 ,p_business_group_id => p_rec.business_group_id);
1088
1089 --
1090 -- After validating the set of important attributes,
1091 -- if Multiple Message detection is enabled and at least
1092 -- one error has been found then abort further validation.
1093 --
1094 hr_multi_message.end_validation_set;
1095 --
1096 -- Validate Dependent Attributes
1097 --
1098 --
1099 -- ben_cty_bus.chk_df(p_rec);
1100 --
1101 hr_utility.set_location(' Leaving:'||l_proc, 10);
1102 End insert_validate;
1103 --
1104 -- ----------------------------------------------------------------------------
1105 -- |---------------------------< update_validate >----------------------------|
1106 -- ----------------------------------------------------------------------------
1107 Procedure update_validate
1108 (p_rec in ben_cty_shd.g_rec_type
1109 ,p_effective_date in date
1110 ,p_datetrack_mode in varchar2
1111 ,p_validation_start_date in date
1112 ,p_validation_end_date in date
1113 ) is
1114 --
1115 l_proc varchar2(72) := g_package||'update_validate';
1116 --
1117 Begin
1118 hr_utility.set_location('Entering:'||l_proc, 5);
1119 --
1120 -- Call all supporting business operations
1121 --
1122 hr_api.validate_bus_grp_id
1123 (p_business_group_id => p_rec.business_group_id
1124 ,p_associated_column1 => ben_cty_shd.g_tab_nam
1125 || '.BUSINESS_GROUP_ID');
1126
1127 chk_comptncy_rt_id
1128 (p_comptncy_rt_id => p_rec.comptncy_rt_id,
1129 p_effective_date => p_effective_date,
1130 p_object_version_number => p_rec.object_version_number);
1131 --
1132 chk_competence_id
1133 (p_comptncy_rt_id => p_rec.comptncy_rt_id,
1134 p_competence_id => p_rec.competence_id,
1135 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1136 p_validation_start_date => p_validation_start_date,
1137 p_validation_end_date => p_validation_end_date,
1138 p_effective_date => p_effective_date,
1139 p_business_group_id => p_rec.business_group_id,
1140 p_object_version_number => p_rec.object_version_number);
1141 --
1142 chk_rating_level_id
1143 (p_comptncy_rt_id => p_rec.comptncy_rt_id,
1144 p_competence_id => p_rec.competence_id,
1145 p_rating_level_id => p_rec.rating_level_id,
1146 p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id,
1147 p_validation_start_date => p_validation_start_date,
1148 p_validation_end_date => p_validation_end_date,
1149 p_effective_date => p_effective_date,
1150 p_business_group_id => p_rec.business_group_id,
1151 p_object_version_number => p_rec.object_version_number);
1152 --
1153 chk_excld_flag
1154 (p_comptncy_rt_id => p_rec.comptncy_rt_id,
1155 p_excld_flag => p_rec.excld_flag,
1156 p_effective_date => p_effective_date,
1157 p_object_version_number => p_rec.object_version_number);
1158 --
1159 chk_duplicate_ordr_num
1160 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
1161 ,p_comptncy_rt_id => p_rec.comptncy_rt_id
1162 ,p_ordr_num => p_rec.ordr_num
1163 ,p_validation_start_date => p_validation_start_date
1164 ,p_validation_end_date => p_validation_end_date
1165 ,p_business_group_id => p_rec.business_group_id);
1166
1167 --
1168 -- After validating the set of important attributes,
1169 -- if Multiple Message detection is enabled and at least
1170 -- one error has been found then abort further validation.
1171 --
1172 hr_multi_message.end_validation_set;
1173 --
1174 -- Validate Dependent Attributes
1175 --
1176 -- Call the datetrack update integrity operation
1177 --
1178 dt_update_validate
1179 (p_vrbl_rt_prfl_id => p_rec.vrbl_rt_prfl_id
1180 ,p_datetrack_mode => p_datetrack_mode
1181 ,p_validation_start_date => p_validation_start_date
1182 ,p_validation_end_date => p_validation_end_date
1183 );
1184 --
1185 chk_non_updateable_args
1186 (p_effective_date => p_effective_date
1187 ,p_rec => p_rec
1188 );
1189 --
1190 --
1191 -- ben_cty_bus.chk_df(p_rec);
1192 --
1193 hr_utility.set_location(' Leaving:'||l_proc, 10);
1194 End update_validate;
1195 --
1196 -- ----------------------------------------------------------------------------
1197 -- |---------------------------< delete_validate >----------------------------|
1198 -- ----------------------------------------------------------------------------
1199 Procedure delete_validate
1200 (p_rec in ben_cty_shd.g_rec_type
1201 ,p_effective_date in date
1202 ,p_datetrack_mode in varchar2
1203 ,p_validation_start_date in date
1204 ,p_validation_end_date in date
1205 ) is
1206 --
1207 l_proc varchar2(72) := g_package||'delete_validate';
1208 --
1209 Begin
1210 hr_utility.set_location('Entering:'||l_proc, 5);
1211 --
1212 -- Call all supporting business operations
1213 --
1214 dt_delete_validate
1215 (p_datetrack_mode => p_datetrack_mode
1216 ,p_validation_start_date => p_validation_start_date
1217 ,p_validation_end_date => p_validation_end_date
1218 ,p_comptncy_rt_id => p_rec.comptncy_rt_id
1219 );
1220 --
1221 hr_utility.set_location(' Leaving:'||l_proc, 10);
1222 End delete_validate;
1223 --
1224 end ben_cty_bus;