1 Package Body per_env_bus as
2 /* $Header: peenvrhi.pkb 120.1 2005/08/04 03:23:12 vegopala noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_env_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_cal_entry_value_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_cal_entry_value_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 from per_business_groups_perf pbg
30 , per_calendar_entries ent
31 , per_cal_entry_values env
32 where env.cal_entry_value_id = p_cal_entry_value_id
33 and env.calendar_entry_id = ent.calendar_entry_id
34 and pbg.business_group_id (+) = ent.business_group_id;
35
36 --
37 -- Declare local variables
38 --
39 l_security_group_id number;
40 l_proc varchar2(72) := g_package||'set_security_group_id';
41 --
42 begin
43 --
44 hr_utility.set_location('Entering:'|| l_proc, 10);
45 --
46 -- Ensure that all the mandatory parameter are not null
47 --
48 hr_api.mandatory_arg_error
49 (p_api_name => l_proc
50 ,p_argument => 'cal_entry_value_id'
51 ,p_argument_value => p_cal_entry_value_id
52 );
53 --
54 open csr_sec_grp;
55 fetch csr_sec_grp into l_security_group_id;
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,'CAL_ENTRY_VALUE_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 end if;
78 --
79 hr_utility.set_location(' Leaving:'|| l_proc, 20);
80 --
81 end set_security_group_id;
82 --
83 -- ---------------------------------------------------------------------------
84 -- |---------------------< return_legislation_code >-------------------------|
85 -- ---------------------------------------------------------------------------
86 --
87 Function return_legislation_code
88 (p_cal_entry_value_id in number
89 )
90 Return Varchar2 Is
91 --
92 -- Declare cursor
93 --
94 cursor csr_leg_code is
95 select pbg.legislation_code
96 from per_business_groups_perf pbg
97 , per_calendar_entries ent
98 , per_cal_entry_values env
99 where env.cal_entry_value_id = p_cal_entry_value_id
100 and env.calendar_entry_id = ent.calendar_entry_id
101 and pbg.business_group_id (+) = ent.business_group_id;
102 --
103 -- Declare local variables
104 --
105 l_legislation_code varchar2(150);
106 l_proc varchar2(72) := g_package||'return_legislation_code';
107 --
108 Begin
109 --
110 hr_utility.set_location('Entering:'|| l_proc, 10);
111 --
112 -- Ensure that all the mandatory parameter are not null
113 --
114 hr_api.mandatory_arg_error
115 (p_api_name => l_proc
116 ,p_argument => 'cal_entry_value_id'
117 ,p_argument_value => p_cal_entry_value_id
118 );
119 --
120 if ( nvl(per_env_bus.g_cal_entry_value_id, hr_api.g_number)
121 = p_cal_entry_value_id) then
122 --
123 -- The legislation code has already been found with a previous
124 -- call to this function. Just return the value in the global
125 -- variable.
126 --
127 l_legislation_code := per_env_bus.g_legislation_code;
128 hr_utility.set_location(l_proc, 20);
129 else
130 --
131 -- The ID is different to the last call to this function
132 -- or this is the first call to this function.
133 --
134 open csr_leg_code;
135 fetch csr_leg_code into l_legislation_code;
136 --
137 if csr_leg_code%notfound then
138 --
139 -- The primary key is invalid therefore we must error
140 --
141 close csr_leg_code;
145 hr_utility.set_location(l_proc,30);
142 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
143 fnd_message.raise_error;
144 end if;
146 --
147 -- Set the global variables so the values are
148 -- available for the next call to this function.
149 --
150 close csr_leg_code;
151 per_env_bus.g_cal_entry_value_id := p_cal_entry_value_id;
152 per_env_bus.g_legislation_code := l_legislation_code;
153 end if;
154 hr_utility.set_location(' Leaving:'|| l_proc, 40);
155 return l_legislation_code;
156 end return_legislation_code;
157 --
158 -- ----------------------------------------------------------------------------
159 -- |-----------------------< chk_non_updateable_args >------------------------|
160 -- ----------------------------------------------------------------------------
161 -- {Start Of Comments}
162 --
163 -- Description:
164 -- This procedure is used to ensure that non updateable attributes have
165 -- not been updated. If an attribute has been updated an error is generated.
166 --
167 -- Pre Conditions:
168 -- g_old_rec has been populated with details of the values currently in
169 -- the database.
170 --
171 -- In Arguments:
172 -- p_rec has been populated with the updated values the user would like the
173 -- record set to.
174 --
175 -- Post Success:
176 -- Processing continues if all the non updateable attributes have not
177 -- changed.
178 --
179 -- Post Failure:
180 -- An application error is raised if any of the non updatable attributes
181 -- have been altered.
182 --
183 -- {End Of Comments}
184 -- ----------------------------------------------------------------------------
185 Procedure chk_non_updateable_args
186 (p_effective_date in date
187 ,p_rec in per_env_shd.g_rec_type
188 ) IS
189 --
190 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
191 --
192 Begin
193 --
194 -- Only proceed with the validation if a row exists for the current
195 -- record in the HR Schema.
196 --
197 IF NOT per_env_shd.api_updating
198 (p_cal_entry_value_id => p_rec.cal_entry_value_id
199 ,p_object_version_number => p_rec.object_version_number
200 ) THEN
201 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
202 fnd_message.set_token('PROCEDURE ', l_proc);
203 fnd_message.set_token('STEP ', '5');
204 fnd_message.raise_error;
205 END IF;
206 --
207 -- check parent Entry Id is not updated
208 If nvl(p_rec.calendar_entry_id, hr_api.g_number) <>
209 nvl(per_env_shd.g_old_rec.calendar_entry_id ,hr_api.g_number)
210 then
211 hr_api.argument_changed_error
212 (p_api_name => l_proc
213 ,p_argument => 'CALENDAR_ENTRY_ID'
214 ,p_base_table => 'PER_CAL_ENTRY_VALUES'
215 );
216 end if;
217 --
218 End chk_non_updateable_args;
219 --
220 -- ----------------------------------------------------------------------------
221 -- |-----------------------< chk_calendar_entry_id >--------------------------|
222 -- ----------------------------------------------------------------------------
223 --
224 -- {Start Of Comments}
225 --
226 -- Description:
227 -- Validates calendar_entry_id is set and exists, called on insert only.
228 --
229 -- In Arguments:
230 --
231 -- Post Success: processing contines
232 --
233 -- Post Failure: error raised and processing stops
234 --
235 Procedure chk_calendar_entry_id (p_calendar_entry_id in NUMBER) IS
236 --
237 l_proc varchar2(72) := g_package||'chk_calendar_entry_id';
238 l_dummy varchar2(1);
239 --
240 --
241 CURSOR CSR_ENTRY IS
242 SELECT 'X'
243 FROM per_calendar_entries ent
244 WHERE ent.calendar_entry_id = p_calendar_entry_id;
245 --
246 Begin
247 --
248 hr_utility.set_location('Entering:'||l_proc, 5);
249 --
250 If p_calendar_entry_id IS NULL then
251 -- raise as serious error, no point doing other checks
252 fnd_message.set_name('PER', 'PER_289976_CAL_ENT_NULL');
253 fnd_message.raise_error;
254 Else
255 --
256 open CSR_ENTRY;
257 fetch CSR_ENTRY into l_dummy;
258 if CSR_ENTRY%notfound then
259 close CSR_ENTRY;
260 -- raise as serious error, no point doing other checks
261 fnd_message.set_name('PER', 'PER_289977_CAL_ENT_INV');
262 fnd_message.raise_error;
263 else
264 close CSR_ENTRY;
265 end if;
266 End if;
267 --
268 --
269 hr_utility.set_location('Leaving:'||l_proc, 50);
270 --
271 End chk_calendar_entry_id;
272 --
273 --
274 -- ----------------------------------------------------------------------------
275 -- |-----------------------< chk_hierarchy_node_id >--------------------------|
276 -- ----------------------------------------------------------------------------
277 --
278 -- {Start Of Comments}
279 --
280 -- Description:
281 -- Validates hierarchy_node_id, if set, exists in per_gen_hierarchy_nodes.
282 -- Also checks that the parent calendar entry is using the same generic hierarchy
283 -- to which the node belongs, and that the node id is unique in per_cal_entry_values
284 -- for the parent calendar_entry_id. Insert only.
285 --
286 -- In Arguments:
287 --
288 -- Post Success: processing contines
289 --
290 -- Post Failure:
291 --
292 Procedure chk_hierarchy_node_id (p_hierarchy_node_id in NUMBER
296 l_id number(15) := NULL;
293 ,p_calendar_entry_id in NUMBER) IS
294 --
295 l_proc varchar2(72) := g_package||'chk_hierarchy_node_id';
297 l_dummy varchar2(1) := NULL;
298
299 -- Get the gen hier id for the entry record,
300 -- which cannot have VS id set
301 CURSOR CSR_ENTRY IS
302 SELECT ent.hierarchy_id
303 FROM per_calendar_entries ent
304 WHERE ent.calendar_entry_id = p_calendar_entry_id
305 AND ent.value_set_id is NULL;
306
307 -- Check the node exists in the hierarchy version 1
308 -- as calendar hierarchies only have one version
309 CURSOR CSR_GEN_HIER (l_gen_hier NUMBER) IS
310 SELECT 'X'
311 FROM per_gen_hierarchy_nodes pgn
312 WHERE pgn.hierarchy_node_id = p_hierarchy_node_id
313 AND pgn.hierarchy_version_id = (select pgv.hierarchy_version_id
314 from per_gen_hierarchy_versions pgv
315 where pgv.hierarchy_id = l_gen_hier
316 and pgv.version_number = 1);
317
318 --
319 -- Check that the node is unique for the parent entry id
320 -- in per_cal_entry_values;
321 --
322 CURSOR CSR_ENT_VAL IS
323 SELECT 'X'
324 FROM per_cal_entry_values env
325 WHERE env.hierarchy_node_id = p_hierarchy_node_id
326 AND env.calendar_entry_id = p_calendar_entry_id;
327 --
328 Begin
329 --
330 hr_utility.set_location('Entering:'||l_proc, 5);
331 --
332 hr_api.mandatory_arg_error
333 (p_api_name => l_proc
334 ,p_argument => 'calendar_entry_id'
335 ,p_argument_value => p_calendar_entry_id
336 );
337 --
338 --
339 IF p_hierarchy_node_id IS NOT NULL THEN
340 --
341 open CSR_ENTRY;
342 fetch CSR_ENTRY into l_id;
343 if CSR_ENTRY%notfound then
344 close CSR_ENTRY;
345 fnd_message.set_name('PER', 'PER_289978_CAL_ENT_NODE_INV');
346 fnd_message.raise_error;
347 else
348 hr_utility.set_location(l_proc, 15);
349 close CSR_ENTRY;
350 if l_id IS NULL then
351 fnd_message.set_name('PER', 'PER_289978_CAL_ENT_NODE_INV');
352 fnd_message.raise_error;
353 else
354 hr_utility.set_location(l_proc, 25);
355 open CSR_GEN_HIER(l_id);
356 fetch CSR_GEN_HIER into l_dummy;
357 if CSR_GEN_HIER%notfound then
358 close CSR_GEN_HIER;
359 -- node does not exist in the parent entry's gen heirarchy MM
360 fnd_message.set_name('PER', 'PER_289979_CAL_ENT_NODE_INV');
361 fnd_message.raise_error;
362 else
363 close CSR_GEN_HIER;
364 end if;
365
366 hr_utility.set_location(l_proc, 35);
367 open CSR_ENT_VAL;
368 fetch CSR_ENT_VAL into l_dummy;
369 if CSR_ENT_VAL%found then
370 close CSR_ENT_VAL;
371 -- node already used by an entry value for this
372 -- calendar entry
373 fnd_message.set_name('PER', 'PER_289980_CAL_ENT_NODE_UNQ');
374 fnd_message.raise_error;
375 else
376 close CSR_ENT_VAL;
377 end if;
378 end if;
379 end if;
380 END IF;
381 --
382 --
383 hr_utility.set_location('Leaving:'||l_proc, 50);
384 --
385 End chk_hierarchy_node_id;
386 --
387 --
388 -- ----------------------------------------------------------------------------
389 -- |------------------------------< chk_value >-------------------------------|
390 -- ----------------------------------------------------------------------------
391 --
392 -- {Start Of Comments}
393 --
394 -- Description:
395 -- Validates value by checking the parent calendar entry record has a
396 -- value_set_id (stand-alone coverage rather than generic hierarchy coverage)
397 -- and that a matching value exists in that value set. Insert only.
398 --
399 -- In Arguments:
400 --
401 -- Post Success: processing contines
402 --
403 -- Post Failure:
404 --
405 Procedure chk_value (p_value in VARCHAR2
406 ,p_calendar_entry_id in NUMBER) IS
407 --
408 l_proc varchar2(72) := g_package||'chk_value';
409 l_id number(15) := NULL;
410
411 -- Get the VS id for the entry record,
412 -- which cannot have a gen hier id set
413 CURSOR CSR_ENTRY IS
414 SELECT ent.VALUE_SET_ID
415 FROM per_calendar_entries ent
416 WHERE ent.calendar_entry_id = p_calendar_entry_id
417 AND ent.hierarchy_id is NULL;
418 --
419 Begin
420 --
421 hr_utility.set_location('Entering:'||l_proc, 5);
422 --
423 hr_api.mandatory_arg_error
424 (p_api_name => l_proc
425 ,p_argument => 'calendar_entry_id'
426 ,p_argument_value => p_calendar_entry_id
427 );
428 --
429 --
430 IF p_value IS NOT NULL THEN
431 --
432 open CSR_ENTRY;
433 fetch CSR_ENTRY into l_id;
434 if CSR_ENTRY%notfound then
435 close CSR_ENTRY;
436 -- parent entry is not using valueset -- MM
437 fnd_message.set_name('PER', 'PER_289981_CAL_ENT_VS_INV');
438 fnd_message.raise_error;
439 else
440 close CSR_ENTRY;
441 -- do valudation of the VS value ....
442
443 end if;
444 END IF;
445 --
446 hr_utility.set_location('Leaving:'||l_proc, 50);
447 --
448 End chk_value;
449
450 --
454 --
451 -- ----------------------------------------------------------------------------
452 -- |-------------------------< chk_comb_fks_valid >--------------------------|
453 -- ----------------------------------------------------------------------------
455 -- {Start Of Comments}
456 --
457 -- Description:
458 -- Validates that the correct combination of hierarchy_node_id,
459 -- and value is supplied. Runs on insert only as these params
460 -- are not updateable.
461 --
462 -- In Arguments:
463 --
464 -- Post Success: processing contines
465 --
466 -- Post Failure: processing halts and error is raised.
467 --
468 Procedure chk_comb_fks_valid(p_hierarchy_node_id IN NUMBER
469 ,p_org_structure_element_id IN NUMBER
470 ,p_value IN VARCHAR2) IS
471 --
472 l_proc varchar2(72) := g_package||'chk_comb_fks_valid';
473 --
474 Begin
475 --
476 hr_utility.set_location('Entering:'||l_proc, 5);
477 --
478 IF (p_hierarchy_node_id is null and p_value is null
479 and p_org_structure_element_id is null)
480 OR (p_hierarchy_node_id is not null and p_value is not null)
481 OR (p_org_structure_element_id is not null and p_value is not null)
482 OR (p_org_structure_element_id is not null and p_hierarchy_node_id is not null) then
483 -- unrecoverable error as only one must be set
484 fnd_message.set_name('PER', 'PER_289982_CAL_ENV_FKS_NULL');
485 fnd_message.raise_error;
486 END IF;
487 --
488 --
489 hr_utility.set_location('Leaving:'||l_proc, 50);
490 --
491 End chk_comb_fks_valid;
492 --
493 --
494 --
495 -- ----------------------------------------------------------------------------
496 -- |-------------------------< chk_override_name_type >--------------------------|
497 -- ----------------------------------------------------------------------------
498 --
499 -- {Start Of Comments}
500 --
501 -- Description:
502 -- Validates the combination of usage_flag, override_name and override_type
503 -- as follows:
504 -- a) One or both of override name or override_type has been
505 -- set for a child override override value only. i.e. usage = 'O'.
506 -- b) Coverage name has not been set for non-override values i.e. usage <> 'O'
507 -- c) Coverage name, if set, is <> parent entry name.
508 -- d) Coverage type (lookup code) exists for the
509 -- lookup_type CALENDAR_ENTRY_TYPE.
510 -- e) Coverage type, if set, is <> parent entry type.
511 -- f) Coverage type should not be set for an exception i.e. usage = 'Y'
512 --
513 --
514 -- In Arguments:
515 --
516 -- Post Success: processing contines
517 --
518 -- Post Failure: processing halts and error is raised.
519 --
520 Procedure chk_override_name_type(p_calendar_entry_id IN NUMBER
521 ,p_cal_entry_value_id IN NUMBER
522 ,p_usage_flag IN VARCHAR2
523 ,p_override_name IN VARCHAR2
524 ,p_override_type IN VARCHAR2
525 ,p_effective_date IN DATE) IS
526 --
527 CURSOR csr_OVR IS
528 SELECT name, type from per_calendar_entries
529 WHERE calendar_entry_id = p_calendar_entry_id;
530 --
531 l_proc varchar2(80) := g_package||'chk_override_name_type';
532 l_type per_cal_entry_values.override_type%type;
533 l_name per_cal_entry_values.override_name%type;
534 --
535 Begin
536 --
537 hr_utility.set_location('Entering:'||l_proc, 5);
538 --
539 IF (p_cal_entry_value_id is NULL
540 or (p_usage_flag <> per_env_shd.g_old_rec.usage_flag)
541 or (nvl(p_override_name,hr_api.g_varchar2)
542 <> nvl(per_env_shd.g_old_rec.override_name,hr_api.g_varchar2))
543 or (nvl(p_override_type,hr_api.g_varchar2)
544 <> nvl(per_env_shd.g_old_rec.override_type,hr_api.g_varchar2)) )
545 THEN
546
547 IF p_usage_flag = 'O' and p_override_name is null and p_override_type is null then
548 -- override must have a name or value - convert to MM
549 fnd_message.set_name('PER', 'HR_289992_CAL_OVER_NULL');
550 fnd_message.raise_error;
551 End if;
552 --
553 IF p_usage_flag <> 'O' and p_override_name is not null then
554 -- non override's must not have an override name - convert to MM
555 fnd_message.set_name('PER', 'HR_289993_CAL_OVERN_SET');
556 fnd_message.raise_error;
557 End if;
558 --
559 hr_utility.set_location(l_proc, 10);
560 IF p_override_type is not null then
561 IF p_usage_flag = 'Y' then
562 -- exceptions must not have an override type - convert to MM
563 fnd_message.set_name('PER', 'HR_289994_CAL_OVERT_SET');
564 fnd_message.raise_error;
565 ELSE
566 -- check the value exists in lookups...
567 if hr_api.NOT_EXISTS_IN_HR_LOOKUPS
568 (p_effective_date => p_effective_date
569 ,p_lookup_type => 'CALENDAR_ENTRY_TYPE'
570 ,p_lookup_code => p_override_type)
571 then
572 fnd_message.set_name('PER','HR_289995_CAL_OVERT_INV');
573 fnd_message.raise_error;
574 --
575 end if;
576 END IF;
577 End if;
578
579 hr_utility.set_location(l_proc, 20);
580
581 IF p_override_type is not null or p_override_name is not null then
582 -- validate that the override values differ from the parent...
583 open CSR_OVR;
584 fetch CSR_OVR into l_name, l_type;
585 close CSR_OVR;
589 End If;
586 If upper(l_name) = upper(p_override_name) then
587 fnd_message.set_name('PER', 'HR_289996_CAL_OVERN_DIF');
588 fnd_message.raise_error;
590 If l_type = p_override_type then
591 fnd_message.set_name('PER', 'HR_289997_CAL_OVERT_DIF');
592 fnd_message.raise_error;
593 End If;
594 End if;
595 --
596 END IF;
597 --
598 hr_utility.set_location('Leaving:'||l_proc, 30);
599 --
600 End chk_override_name_type;
601 --
602 --
603 -- ----------------------------------------------------------------------------
604 -- |---------------------< chk_comb_usage_parentev_id >------------------------|
605 -- ----------------------------------------------------------------------------
606 --
607 -- {Start Of Comments}
608 --
609 -- Description:
610 -- Validates the combination of usage_flag and parent_entry_value_id values as:
611 -- a) usage_flag is mandatory.
612 -- b) usage_flag value is one of 'N' entry value,'Y' exception,'O' override.
613 -- c) parent_entry_value_id is mandatory if usage_flag <> 'Y', otherwise
614 -- parent_entry_value_id must be null.
615 -- d) if parent_entry_value_id is not null, checks that parent_entry_value_id
616 -- corresponds to a record in per_cal_entry_values for the current
617 -- calendar_entry_id, and that the parent record is an existing entry value.
618 -- i.e. usage = 'Y'.
619 --
620 -- In Arguments:
621 --
622 -- Post Success: processing contines
623 --
624 -- Post Failure: processing halts and error is raised.
625 --
626 Procedure chk_comb_usage_parentev_id (p_cal_entry_value_id IN NUMBER
627 ,p_calendar_entry_id IN NUMBER
628 ,p_parent_entry_value_id IN NUMBER
629 ,p_usage_flag IN VARCHAR2) IS
630
631 -- Check the parent exists in per_cal_entry_values
632 -- as calendar hierarchies only have one version
633 CURSOR CSR_EV IS
634 SELECT env.calendar_entry_id, env.parent_entry_value_id
635 FROM per_cal_entry_values env
636 WHERE env.cal_entry_value_id = p_parent_entry_value_id;
637
638 l_parent_entry_id NUMBER(15);
639 l_parent_entry_value_id NUMBER(15);
640 --
641 l_proc varchar2(80) := g_package||'chk_comb_usage_parentev_id';
642 --
643 Begin
644 --
645 hr_utility.set_location('Entering:'||l_proc, 5);
646 --
647 --
648 IF (p_cal_entry_value_id is NULL
649 or (nvl(p_parent_entry_value_id,hr_api.g_number)
650 <> nvl(per_env_shd.g_old_rec.parent_entry_value_id,hr_api.g_number))
651 or (nvl(p_usage_flag,hr_api.g_varchar2)
652 <> nvl(per_env_shd.g_old_rec.usage_flag,hr_api.g_varchar2)) )
653 THEN
654
655
656 If p_usage_flag is null or p_usage_flag not in ('Y','N','O') then
657 -- usage is invalid
658 fnd_message.set_name('PER', 'HR_289998_CAL_USAGE_INV');
659 fnd_message.raise_error;
660 End if;
661
662 -- continue with combines validation
663 If p_parent_entry_value_id is not null and p_usage_flag = 'N' THEN
664 -- current record is a gen hierarchy coverage parent or a valueset coverage
665 -- so parent_entry_value_id must be null.
666 fnd_message.set_name('PER', 'PER_289983_CAL_VS_NO_PARENT');
667 fnd_message.raise_error;
668 End if;
669
670 If p_parent_entry_value_id is null and p_usage_flag <> 'N' THEN
671 -- current record is not a gen hierarchy coverage parent or
672 -- a valueset coverage so parent_entry_value_id must NOT be null.
673 fnd_message.set_name('PER', 'HR_289999_CAL_PARENT_NULL');
674 fnd_message.raise_error;
675 End if;
676
677 hr_utility.set_location(l_proc, 10);
678
679 If p_parent_entry_value_id is not NULL then
680 -- if we get here, we can proceed with validating the parent coverage id
681
682 open CSR_EV;
683 fetch CSR_EV into l_parent_entry_id, l_parent_entry_value_id;
684 If CSR_EV%notfound then
685 close CSR_EV;
686 -- parent EV was not found
687 fnd_message.set_name('PER', 'PER_289984_CAL_PARENT_INV1');
688 fnd_message.raise_error;
689 Else
690 close CSR_EV;
691
692 hr_utility.set_location(l_proc, 20);
693 If p_calendar_entry_id <> l_parent_entry_id then
694 -- parent EV exists for a different calendar entry
695 fnd_message.set_name('PER', 'PER_289985_CAL_PARENT_INV2');
696 fnd_message.raise_error;
697 End if;
698
699 If l_parent_entry_value_id is not NULL then
700 -- parent EV is actually EVX or EVO so this grandchild is not allowed.
701 fnd_message.set_name('PER', 'PER_289156_CAL_PARENT_INV3');
702 fnd_message.raise_error;
703 End if;
704 End if;
705 End if;
706 --
707 End if;
708 --
709 hr_utility.set_location('Leaving:'||l_proc, 30);
710 --
711 End CHK_COMB_USAGE_PARENTEV_ID;
712
713 -- ----------------------------------------------------------------------------
714 -- |------------------------< chk_identifier_key >----------------------------|
715 -- ----------------------------------------------------------------------------
716 --
717 -- {Start Of Comments}
718 --
719 -- Description:
720 -- Validates the Identifier Key is UNIQUE.
724 --
721 -- Note: This should only be NOT NULL when called by SEED DB user.
722 --
723 -- In Arguments:
725 -- Post Success: processing contines
726 --
727 -- Post Failure: processing halts and error is raised.
728 --
729 Procedure chk_identifier_key (p_identifier_key in VARCHAR2) IS
730 --
731 CURSOR c_unique IS
732 SELECT 'Y'
733 FROM PER_CAL_ENTRY_VALUES ENV
734 WHERE ENV.IDENTIFIER_KEY = p_identifier_key;
735
736 l_proc varchar2(72) := g_package||'chk_identifier_key';
737 l_value varchar2(240):= null;
738 --
739 Begin
740 --
741 hr_utility.set_location('Entering:'||l_proc, 5);
742 --
743 IF p_identifier_key IS NOT NULL THEN
744 open c_unique;
745 fetch c_unique into l_value;
746 IF c_unique%found THEN
747 close c_unique;
748 -- Add to MM list as non-fatal
749 fnd_message.set_name('PER','PER_449074_CAL_IDK_EXISTS');
750 hr_multi_message.add
751 (p_associated_column1 => 'PER_CAL_ENTRY_VALUES.identifier_key');
752 ELSE
753 close c_unique;
754 END IF;
755 --
756 END IF;
757 --
758 hr_utility.set_location('Leaving:'||l_proc, 40);
759 --
760 End chk_identifier_key;
761
762 --
763 --
764 -- ----------------------------------------------------------------------------
765 -- |---------------------------< insert_validate >----------------------------|
766 -- ----------------------------------------------------------------------------
767 Procedure insert_validate
768 (p_effective_date in date
769 ,p_rec in per_env_shd.g_rec_type
770 ) is
771 --
772 l_proc varchar2(72) := g_package||'insert_validate';
773 --
774 Begin
775 hr_utility.set_location('Entering:'||l_proc, 5);
776 --
777 -- Call all supporting business operations
778 --
779 --
780
781 -- Validate calendar entry id on insert only
782 chk_calendar_entry_id(p_rec.calendar_entry_id);
783
784 -- Check combination of FKs is valid before
785 -- individiual combinations and values are checked
786 chk_comb_fks_valid(p_rec.hierarchy_node_id
787 ,p_rec.org_structure_element_id
788 ,p_rec.value);
789
790
791 -- Now validate the FK, value params
792 chk_hierarchy_node_id(p_rec.hierarchy_node_id
793 ,p_rec.calendar_entry_id);
794
795 chk_value (p_rec.value
796 ,p_rec.calendar_entry_id);
797
798 -- Finally validate usage_flag and parent entry value
799 chk_comb_usage_parentev_id(p_rec.cal_entry_value_id
800 ,p_rec.calendar_entry_id
801 ,p_rec.parent_entry_value_id
802 ,p_rec.usage_flag);
803
804 -- validate override_name and override_type
805 chk_override_name_type (p_rec.calendar_entry_id
806 ,p_rec.cal_entry_value_id
807 ,p_rec.usage_flag
808 ,p_rec.override_name
809 ,p_rec.override_type
810 ,p_effective_date);
811
812 -- chk_identifier_key(p_rec.identifier_key);
813 --
814 --
815 --
816 hr_utility.set_location(' Leaving:'||l_proc, 10);
817 End insert_validate;
818 --
819 -- ----------------------------------------------------------------------------
820 -- |---------------------------< update_validate >----------------------------|
821 -- ----------------------------------------------------------------------------
822 Procedure update_validate
823 (p_effective_date in date
824 ,p_rec in per_env_shd.g_rec_type
825 ) is
826 --
827 l_proc varchar2(72) := g_package||'update_validate';
828 --
829 Begin
830 hr_utility.set_location('Entering:'||l_proc, 5);
831 --
832 -- Call all supporting business operations
833 --
834 -- Validate Dependent Attributes
835 --
836 chk_non_updateable_args
837 (p_effective_date => p_effective_date
838 ,p_rec => p_rec
839 );
840
841
842 -- Finally validate usage_flag and parent entry value
843 chk_comb_usage_parentev_id(p_rec.cal_entry_value_id
844 ,p_rec.calendar_entry_id
845 ,p_rec.parent_entry_value_id
846 ,p_rec.usage_flag);
847
848 -- validate override_name and override_type
849 chk_override_name_type (p_rec.calendar_entry_id
850 ,p_rec.cal_entry_value_id
851 ,p_rec.usage_flag
852 ,p_rec.override_name
853 ,p_rec.override_type
854 ,p_effective_date);
855 --
856 --
857 hr_utility.set_location(' Leaving:'||l_proc, 10);
858 End update_validate;
859 --
860 -- ----------------------------------------------------------------------------
861 -- |---------------------------< delete_validate >----------------------------|
862 -- ----------------------------------------------------------------------------
863 Procedure delete_validate
864 (p_rec in per_env_shd.g_rec_type
865 ) is
866 --
867 l_proc varchar2(72) := g_package||'delete_validate';
868 --
869 Begin
870 hr_utility.set_location('Entering:'||l_proc, 5);
871 --
872 -- Call all supporting business operations
873 --
874 hr_utility.set_location(' Leaving:'||l_proc, 10);
875 End delete_validate;
876 --
877 end per_env_bus;
[Home] [Help]