1 Package Body per_ent_bus as
2 /* $Header: peentrhi.pkb 120.2 2005/06/16 08:27:40 vegopala noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_ent_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_calendar_entry_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_calendar_entry_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 pbg
30 , per_calendar_entries ent
31 where ent.calendar_entry_id = p_calendar_entry_id
32 and pbg.business_group_id = ent.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error
46 (p_api_name => l_proc
47 ,p_argument => 'calendar_entry_id'
48 ,p_argument_value => p_calendar_entry_id
49 );
50 --
51 open csr_sec_grp;
52 fetch csr_sec_grp into l_security_group_id;
53 --
54 if csr_sec_grp%notfound then
55 --
56 close csr_sec_grp;
57 --
58 -- The primary key is invalid therefore we must error
59 --
60 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
61 hr_multi_message.add
62 (p_associated_column1
63 => nvl(p_associated_column1,'CALENDAR_ENTRY_ID')
64 );
65 --
66 else
67 close csr_sec_grp;
68 --
69 -- Set the security_group_id in CLIENT_INFO
70 --
71 hr_api.set_security_group_id
72 (p_security_group_id => l_security_group_id
73 );
74 end if;
75 --
76 hr_utility.set_location(' Leaving:'|| l_proc, 20);
77 --
78 end set_security_group_id;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |---------------------< return_legislation_code >-------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 Function return_legislation_code
85 (p_calendar_entry_id in number
86 )
87 Return Varchar2 Is
88 --
89 -- Declare cursor
90 --
91 cursor csr_leg_code is
92 select pbg.legislation_code
93 from per_business_groups_perf pbg
94 , per_calendar_entries ent
95 where ent.calendar_entry_id = p_calendar_entry_id
96 and pbg.business_group_id (+) = ent.business_group_id;
97 --
98 -- Declare local variables
99 --
100 l_legislation_code varchar2(150);
101 l_proc varchar2(72) := g_package||'return_legislation_code';
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'|| l_proc, 10);
106 --
107 -- Ensure that all the mandatory parameter are not null
108 --
109 hr_api.mandatory_arg_error
110 (p_api_name => l_proc
111 ,p_argument => 'calendar_entry_id'
112 ,p_argument_value => p_calendar_entry_id
113 );
114 --
115 if ( nvl(per_ent_bus.g_calendar_entry_id, hr_api.g_number)
116 = p_calendar_entry_id) then
117 --
118 -- The legislation code has already been found with a previous
119 -- call to this function. Just return the value in the global
120 -- variable.
121 --
122 l_legislation_code := per_ent_bus.g_legislation_code;
123 hr_utility.set_location(l_proc, 20);
124 else
125 --
126 -- The ID is different to the last call to this function
127 -- or this is the first call to this function.
128 --
129 open csr_leg_code;
130 fetch csr_leg_code into l_legislation_code;
131 --
132 if csr_leg_code%notfound then
133 --
134 -- The primary key is invalid therefore we must error
135 --
136 close csr_leg_code;
137 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
138 fnd_message.raise_error;
139 end if;
140 hr_utility.set_location(l_proc,30);
141 --
142 -- Set the global variables so the values are
143 -- available for the next call to this function.
144 --
145 close csr_leg_code;
146 per_ent_bus.g_calendar_entry_id := p_calendar_entry_id;
147 per_ent_bus.g_legislation_code := l_legislation_code;
148 end if;
149 hr_utility.set_location(' Leaving:'|| l_proc, 40);
150 return l_legislation_code;
151 end return_legislation_code;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |-----------------------< chk_non_updateable_args >------------------------|
155 -- ----------------------------------------------------------------------------
156 -- {Start Of Comments}
157 --
158 -- Description:
159 -- This procedure is used to ensure that non updateable attributes have
160 -- not been updated. If an attribute has been updated an error is generated.
161 --
162 -- Pre Conditions:
163 -- g_old_rec has been populated with details of the values currently in
164 -- the database.
165 --
166 -- In Arguments:
167 -- p_rec has been populated with the updated values the user would like the
168 -- record set to.
169 --
170 -- Post Success:
171 -- Processing continues if all the non updateable attributes have not
172 -- changed.
173 --
174 -- Post Failure:
175 -- An application error is raised if any of the non updatable attributes
176 -- have been altered.
177 --
178 -- {End Of Comments}
179 -- ----------------------------------------------------------------------------
180 Procedure chk_non_updateable_args
181 (p_effective_date in date
182 ,p_rec in per_ent_shd.g_rec_type
183 ) IS
184 --
185 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
186 --
187 Begin
188 --
189 -- Only proceed with the validation if a row exists for the current
190 -- record in the HR Schema.
191 --
192 IF NOT per_ent_shd.api_updating
193 (p_calendar_entry_id => p_rec.calendar_entry_id
194 ,p_object_version_number => p_rec.object_version_number
195 ) THEN
196 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
197 fnd_message.set_token('PROCEDURE ', l_proc);
198 fnd_message.set_token('STEP ', '5');
199 fnd_message.raise_error;
200 END IF;
201 --
202
203 -- check legislation code is not updated
204 If nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
205 nvl(per_ent_shd.g_old_rec.legislation_code ,hr_api.g_varchar2)
206 then
207 hr_api.argument_changed_error
208 (p_api_name => l_proc
209 ,p_argument => 'LEGISLATION_CODE'
210 ,p_base_table => 'PER_CALENDAR_ENTRIES'
211 );
212 end if;
213
214 -- check identifier key is not updated
215 If nvl(p_rec.identifier_key, hr_api.g_varchar2) <>
216 nvl(per_ent_shd.g_old_rec.identifier_key ,hr_api.g_varchar2)
217 then
218 hr_api.argument_changed_error
219 (p_api_name => l_proc
220 ,p_argument => 'IDENTIFIER_KEY'
221 ,p_base_table => 'PER_CALENDAR_ENTRIES'
222 );
223 end if;
224
225
226
227 End chk_non_updateable_args;
228
229 --
230 -- ----------------------------------------------------------------------------
231 -- |-----------------------------< chk_name >---------------------------------|
232 -- ----------------------------------------------------------------------------
233 --
234 -- {Start Of Comments}
235 --
236 -- Description:
237 -- Validates the name is not null.
238 --
239 -- In Arguments:
240 --
241 -- Post Success: processing contines
242 --
243 -- Post Failure: processing halts and error is raised.
244 --
245 Procedure chk_name (p_name in VARCHAR2) IS
246 --
247 l_proc varchar2(72) := g_package||'chk_name';
248 --
249 Begin
250 --
251 hr_utility.set_location('Entering:'||l_proc, 5);
252 --
253 IF p_name IS NULL THEN
254 -- Add to MM list as non-fatal
255 fnd_message.set_name('PER','PER_289958_CAL_NAME_NULL');
256 hr_multi_message.add
257 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.NAME');
258 --
259 END IF;
260 --
261 hr_utility.set_location('Leaving:'||l_proc, 40);
262 --
263 End chk_name;
264 --
265 --
266 -- ----------------------------------------------------------------------------
267 -- |-----------------------------< chk_type >---------------------------------|
268 -- ----------------------------------------------------------------------------
269 --
270 -- {Start Of Comments}
271 --
272 -- Description:
273 -- Validates the entry type exists in HR_STANDARD_LOOKUPS.
274 --
275 -- In Arguments:
276 --
277 -- Post Success: processing contines
278 --
279 -- Post Failure: processing halts and error is raised.
280 --
281 Procedure chk_type (p_type in VARCHAR2
282 ,p_calendar_entry_id in NUMBER
283 ,p_effective_date in DATE) IS
284 --
285 l_proc varchar2(72) := g_package||'chk_type';
286 --
287 Begin
288 --
289 hr_utility.set_location('Entering:'||l_proc, 5);
290 --
291 --
292 -- Only proceed with validation if :
293 -- a) Inserting or
294 -- b) The value for type has changed
295 --
296 IF p_type IS NULL THEN
297 -- Add to MM list
298 fnd_message.set_name('PER','PER_289959_CAL_TYPE_NULL');
299 hr_multi_message.add
300 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.TYPE');
301 --
302 ELSIF ((p_calendar_entry_id is null) or
303 ((p_calendar_entry_id is not null) and
304 (per_ent_shd.g_old_rec.type <> p_type))) then
305 --
306 if hr_api.NOT_EXISTS_IN_HR_LOOKUPS
307 (p_effective_date => p_effective_date
308 ,p_lookup_type => 'CALENDAR_ENTRY_TYPE'
309 ,p_lookup_code => p_type
310 )
311 then
312 -- Add to MM list
313 fnd_message.set_name('PER','PER_289960_CAL_TYPE_INV');
314 hr_multi_message.add
315 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.TYPE');
316 --
317 end if;
318 END IF;
319 --
320 hr_utility.set_location('Leaving:'||l_proc, 40);
321 --
322 End chk_type;
323
324
325 -- ---------------------------------------------------------------------------
326 -- |------------------------< chk_start_date_end_date >-----------------------|
327 -- ---------------------------------------------------------------------------
328 --
329 -- Description :
330 --
331 -- Validate that START DATE and END DATE are not null. Also check that
332 -- START DATE <= END DATE.
333 -- If start_date = end_date then check that start time <= end time.
334 -- Also prevent start min without start hour, and end min without end hour.
335 --
336 -- Pre-conditions :
337 --
338 --
339 -- In Arguments :
340 -- p_incident_date
341 --
342 -- Post Success :
343 -- Processing continues
344 --
345 -- Post Failure :
346 -- An application error will be raised and processing is
347 -- terminated
348 --
349 -- Access Status :
350 -- Internal Table Handler Use only.
351 --
352 -- {End of Comments}
353 --
354 -- ---------------------------------------------------------------------------
355 procedure chk_start_date_end_date
356 (p_start_date IN DATE
357 ,p_end_date IN DATE
358 ,p_start_hour IN VARCHAR2
359 ,p_start_min IN VARCHAR2
360 ,p_end_hour IN VARCHAR2
361 ,p_end_min IN VARCHAR2
362 ,p_hierarchy_id IN NUMBER
363 ,p_org_structure_version_id IN NUMBER) IS
364 --
365 l_proc varchar2(72) := g_package||'chk_start_date_end_date';
366 l_hier_date_from date;
367 l_hier_date_to date;
368
369 --Declare the cursors
370
371 cursor csr_org_hier(p_org_structure_version_id NUMBER) is
372 select date_from,date_to
373 from PER_ORG_STRUCTURE_VERSIONS
374 where org_structure_version_id = p_org_structure_version_id;
375
376 cursor csr_geo_hier(p_hierarchy_id NUMBER) is
377 select date_from, date_to
378 from PER_GEN_HIERARCHY_VERSIONS
379 where hierarchy_id = p_hierarchy_id;
380 --
381 begin
382 hr_utility.set_location('Entering:'||l_proc, 1);
383 --
384 -- Check mandatory incident_date is set
385 --
386 if p_start_date is null then
387 -- Add to MM list
388 fnd_message.set_name('PER','PER_289961_ENT_SDATE_NULL');
389 hr_multi_message.add
390 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.START_DATE');
391 end if;
392
393 if p_end_date is NULL then
394 -- Add to MM list
395 fnd_message.set_name('PER','PER_289962_ENT_EDATE_NULL');
396 hr_multi_message.add
397 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.END_DATE');
398 end if;
399
400 if ((p_end_date is NOT NULL and p_start_date is NOT NULL) and
401 (p_start_date > p_end_date)) then
402 -- Add to MM list
403 fnd_message.set_name('PER','PER_289963_ENT_SDATE_INV');
404 hr_multi_message.add
405 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.START_DATE');
406 end if;
407 --
408
409 -- only validate time if date is OK....
410
411 if (p_start_min is NOT NULL and p_start_hour is NULL) or
412 (p_end_min is NOT NULL and p_end_hour is NULL) then
413 -- Add to MM list as invalid combination passed
414 fnd_message.set_name('PER','PER_289187_CAL_ENT_TIME_INV');
415 hr_multi_message.add
416 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.START_HOUR'
417 ,p_associated_column2 => 'PER_CALENDAR_ENTRIES.START_MIN'
418 ,p_associated_column3 => 'PER_CALENDAR_ENTRIES.END_HOUR'
419 ,p_associated_column4 => 'PER_CALENDAR_ENTRIES.END_MIN');
420
421 else
422 if (p_start_min is NOT NULL and p_end_min is NOT NULL) or
423 (p_start_hour is NOT NULL and p_end_hour is NOT NULL) then
424
425 -- do times comparison...
429
426 if ( nvl(to_number(p_start_hour),0) = nvl(to_number(p_end_hour),23)
427 and nvl(to_number(p_start_min),0) > nvl(to_number(p_end_min),55) )
428 or ( nvl(to_number(p_start_hour),0) > nvl(to_number(p_end_hour),23) ) then
430 fnd_message.set_name('PER','PER_289188_CAL_ENT_TIME_INV2');
431 hr_multi_message.add
432 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.START_HOUR'
433 ,p_associated_column2 => 'PER_CALENDAR_ENTRIES.START_MIN'
434 ,p_associated_column3 => 'PER_CALENDAR_ENTRIES.END_HOUR'
435 ,p_associated_column4 => 'PER_CALENDAR_ENTRIES.END_MIN');
436 end if;
437
438 end if;
439 end if;
440
441 if p_org_structure_version_id is not null then
442 open csr_org_hier(p_org_structure_version_id);
443 fetch csr_org_hier into l_hier_date_from,l_hier_date_to;
444 end if;
445
446 if p_hierarchy_id is not null then
447 open csr_geo_hier(p_hierarchy_id);
448 fetch csr_geo_hier into l_hier_date_from,l_hier_date_to;
449 end if;
450
451 if l_hier_date_from is not null then
452 if l_hier_date_to is not null then
453 if p_end_date < l_hier_date_from or p_start_date > l_hier_date_to then
454 fnd_message.set_name('PER','CAC_SR_HIER_ENTRY_DATE_ERROR');
455 fnd_message.raise_error;
456 end if;
457 else
458 if p_end_date< l_hier_date_from then
459 fnd_message.set_name('PER','CAC_SR_HIER_ENTRY_DATE_ERROR');
460 fnd_message.raise_error;
461 end if;
462 end if;
463 end if;
464
465
466
467 hr_utility.set_location(' Leaving:'||l_proc, 5);
468 --
469 end chk_start_date_end_date;
470 --
471 --
472 -- ---------------------------------------------------------------------------
473 -- |----------------------< chk_no_entry_values_exist >----------------------|
474 -- ---------------------------------------------------------------------------
475 --
476 -- Description :
477 --
478 -- Validate that the entry can be deleted as no child
479 -- entry values exist.
480 --
481 -- Pre-conditions :
482 --
483 --
484 -- In Arguments :
485 --
486 -- Post Success :
487 -- Processing continues
488 --
489 -- Post Failure :
490 -- An application error will be raised and processing is
491 -- terminated
492 --
493 -- Access Status :
494 -- Internal Table Handler Use only.
495 --
496 -- {End of Comments}
497 --
498 -- ---------------------------------------------------------------------------
499 procedure chk_no_entry_values_exist(p_calendar_entry_id in number) IS
500 --
501 CURSOR csr_ev IS
502 Select 'x'
503 From per_cal_entry_values env
504 Where env.calendar_entry_id = p_calendar_entry_id;
505 --
506 l_proc varchar2(72) := g_package||'chk_no_entry_values_exist';
507 l_dummy varchar2(1);
508 --
509 Begin
510 hr_utility.set_location('Entering:'||l_proc, 1);
511 --
512 if p_calendar_entry_id is not null then
513 open csr_ev;
514 fetch csr_ev into l_dummy;
515 if csr_ev%found then
516 close csr_ev;
517 fnd_message.set_name('PER', 'PER_289964_CAL_NO_DELETE');
518 fnd_message.raise_error;
519 else
520 close csr_ev;
521 end if;
522 end if;
523 --
524 hr_utility.set_location(' Leaving:'||l_proc, 5);
525 --
526 End chk_no_entry_values_exist;
527
528 --
529 --
530 -- ----------------------------------------------------------------------------
531 -- |---------------------< chk_valueset_or_hierarchy >------------------------|
532 -- ----------------------------------------------------------------------------
533 --
534 -- {Start Of Comments}
535 --
536 -- Description:
537 -- Validates the valueset id, or generic hierarchy id, or
538 -- org structure and structure version id supplied.
539 --
540 -- In Arguments:
541 --
542 -- Post Success: processing contines
543 --
544 -- Post Failure: processing halts and error is raised.
545 --
546 Procedure chk_valueset_or_hierarchy (p_calendar_entry_id IN NUMBER
547 ,p_value_set_id IN NUMBER
548 ,p_hierarchy_id IN NUMBER
549 ,p_business_group_id IN NUMBER
550 ,p_organization_structure_id IN NUMBER
551 ,p_org_structure_version_id IN NUMBER) IS
552
553 -- Check if hierarchy exists in generic hierarchy table:
554 -- if entry is global then hierarchy must also be global
555 -- if entry not global then hierarchy must be global or in the current business group
556 CURSOR CSR_HIER IS
557 SELECT 'Y'
558 FROM per_gen_hierarchy gen
559 WHERE gen.hierarchy_id = p_hierarchy_id
560 AND gen.TYPE like 'PER_CAL%'
561 AND ( (p_business_group_id is not null and
562 (gen.business_group_id = p_business_group_id or gen.business_group_id is null))
563 OR
564 (p_business_group_id is null and gen.business_group_id is null));
565
566 -- Check Org structure (version) exists:
567 -- if entry is global then Org Structure must also be global
568 -- if entry not global then Org Structure must be global or in the current business group
569 CURSOR CSR_ORG_HIER IS
570 SELECT 'Y'
571 FROM per_org_structure_versions osv
575 (osv.business_group_id = p_business_group_id or osv.business_group_id is null))
572 WHERE OSV.ORGANIZATION_STRUCTURE_ID = p_organization_structure_id
573 AND OSV.ORG_STRUCTURE_VERSION_ID = p_org_structure_version_id
574 AND ( (p_business_group_id is not null and
576 OR
577 (p_business_group_id is null and osv.business_group_id is null) );
578
579 -- Check if VS exists
580 CURSOR CSR_VS IS
581 SELECT 'Y'
582 FROM fnd_flex_value_sets vs
583 WHERE vs.FLEX_VALUE_SET_ID = p_value_set_id
584 AND vs.VALIDATION_TYPE = 'F';
585
586 -- Check if child EVs exist and return coverage type for them.
587 CURSOR CSR_EV IS
588 SELECT decode (ent.HIERARCHY_ID, NULL,
589 decode (ent.VALUE_SET_ID, NULL,'O','V'),
590 'H')
591
592 FROM per_calendar_entries ent
593 WHERE ent.calendar_entry_id = p_calendar_entry_id
594 AND exists (select 'Y' from per_cal_entry_values env
595 where env.calendar_entry_id = p_calendar_entry_id);
596 --
597 l_chk_evs BOOLEAN := FALSE;
598 l_dummy VARCHAR2(1) := NULL;
599 l_param1 VARCHAR2(30);
600 --
601 l_proc varchar2(72) := g_package||'chk_valueset_or_hierarchy';
602 --
603 Begin
604 --
605 hr_utility.set_location('Entering:'||l_proc, 5);
606 --
607
608 IF (p_value_set_id is not null and p_hierarchy_id is not null)
609 OR (p_value_set_id is not null and (p_organization_structure_id is not null or p_org_structure_version_id is not null))
610 OR (p_hierarchy_id is not null and (p_organization_structure_id is not null or p_org_structure_version_id is not null))
611 THEN
612 -- raise error as only one coverage source may be chosen
613 fnd_message.set_name('PER','PER_289966_CAL_ENT_TWO_SET');
614 hr_multi_message.add
615 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.HIERARCHY_ID'
616 ,p_associated_column2 => 'PER_CALENDAR_ENTRIES.VALUE_SET_ID');
617 ELSE
618 -- only validate a specific id if > 1 are not set
619
620 --
621 -- validate the VS id exists and is a SQL VS
622 --
623 IF ( (p_calendar_entry_id is null and p_value_set_id is not null)
624 OR (p_calendar_entry_id is not null
625 AND ( (p_value_set_id is not null and per_ent_shd.g_old_rec.value_set_id is null)
626 OR (p_value_set_id is null and per_ent_shd.g_old_rec.value_set_id is not null)
627 OR (p_value_set_id is not null and p_value_set_id <> per_ent_shd.g_old_rec.value_set_id))) ) THEN
628
629 hr_utility.set_location(l_proc, 10);
630
631 if p_value_set_id is not NULL then
632 open csr_VS;
633 fetch csr_VS into l_dummy;
634 if csr_VS%notfound then
635 close csr_VS;
636 -- raise error on MM list as VS is invalid
637 -- i.e. id either was not found or is not F validation type
638 fnd_message.set_name('PER','PER_289967_CAL_ENT_VS_INV');
639 fnd_message.set_token('VSET',p_value_set_id);
640 hr_multi_message.add
641 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.VALUE_SET_ID');
642 else
643 close csr_VS;
644 end if;
645 end if;
646
647 if p_calendar_entry_id is not null then
648 l_chk_evs := TRUE;
649 end if;
650
651 END IF;
652
653 -- Validate Gen hierarchy
654 IF ( (p_calendar_entry_id is null and p_hierarchy_id is not null)
655 OR (p_calendar_entry_id is not null
656 AND ( (p_hierarchy_id is not null and per_ent_shd.g_old_rec.hierarchy_id is null)
657 OR (p_hierarchy_id is null and per_ent_shd.g_old_rec.hierarchy_id is not null)
658 OR (p_hierarchy_id is not null and p_hierarchy_id <> per_ent_shd.g_old_rec.hierarchy_id))) ) THEN
659
660 hr_utility.set_location(l_proc, 20);
661
662 if p_hierarchy_id is not null then
663 open csr_HIER;
664 fetch csr_HIER into l_dummy;
665 if csr_HIER%notfound then
666 close csr_HIER;
667 -- raise error as gen hierarchy is invalid
668 fnd_message.set_name('PER', 'PER_289968_CAL_ENT_HIER_INV');
669 hr_multi_message.add
670 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.HIERARCHY_ID');
671 else
672 close csr_HIER;
673 end if;
674 end if;
675
676 if p_calendar_entry_id is not null then
677 l_chk_evs := TRUE;
678 end if;
679
680 END IF;
681
682
683 -- validate BOTH org structure and version are supplied, if either are
684 IF (p_organization_structure_id is not null and p_org_structure_version_id is null)
685 OR (p_organization_structure_id is null and p_org_structure_version_id is not null) THEN
686 fnd_message.set_name('PER', 'HR_289971_CAL_OS_PARAMS');
687 hr_multi_message.add
688 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.ORGANIZATION_STRUCTURE_ID');
689 hr_multi_message.add
690 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.ORG_STRUCTURE_VERSION_ID');
691
692 ELSIF (p_organization_structure_id is not null and p_org_structure_version_id is not null) THEN
693 -- Validate Org Hier and Version
697 AND ( (per_ent_shd.g_old_rec.org_structure_version_id is not null
694
695 IF ( (p_calendar_entry_id is null)
696 OR (p_calendar_entry_id is not null
698 and p_org_structure_version_id <> per_ent_shd.g_old_rec.org_structure_version_id)
699 OR (per_ent_shd.g_old_rec.organization_structure_id is not null
700 and p_organization_structure_id <> per_ent_shd.g_old_rec.organization_structure_id)) ) ) THEN
701
702 hr_utility.set_location(l_proc, 30);
703
704 open csr_ORG_HIER;
705 fetch csr_ORG_HIER into l_dummy;
706 if csr_ORG_HIER%notfound then
707 close csr_ORG_HIER;
708 -- raise error as org Hierarchy is invalid
709 fnd_message.set_name('PER', 'PER_289968_CAL_ENT_HIER_INV');
710 hr_multi_message.add
711 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.ORG_STRUCTURE_VERSION_ID');
712 else
713 close csr_ORG_HIER;
714 end if;
715
716 if p_calendar_entry_id is not null then
717 l_chk_evs := TRUE;
718 end if;
719
720 END IF;
721
722 ELSIF (p_organization_structure_id is null and per_ent_shd.g_old_rec.organization_structure_id is not null)
723 OR (p_org_structure_version_id is null and per_ent_shd.g_old_rec.org_structure_version_id is not null) THEN
724 -- updating to null
725 l_chk_evs := TRUE;
726
727 END IF;
728
729 IF l_chk_evs THEN
730 -- validate that no child EV's exist
731 -- for this entry, otherwise prevent update
732
733 hr_utility.set_location(l_proc, 30);
734 open csr_EV;
735 fetch csr_EV into l_dummy; -- coverage type 'V' or 'H' or 'O'
736 if csr_EV%found then
737 close csr_EV;
738 -- raise error on MM list passing set id column name as
739
740 -- Set source coverage based on param
741 IF (per_ent_shd.g_old_rec.hierarchy_id is not null) THEN
742 l_param1 := 'Generic Hierarchy';
743 ELSIF (per_ent_shd.g_old_rec.value_set_id is not NULL) THEN
744 l_param1 := 'Valueset';
745 ELSIF (per_ent_shd.g_old_rec.org_structure_version_id is not NULL) THEN
746 l_param1 := 'Organization Hierarchy';
747 END IF;
748
749 -- Raise the relevant error
750 if l_dummy is not null then
751 -- fnd_message.set_name('PER', 'HR_289969_CAL_HEVS1_EXIST');
752 -- fnd_message.set_name('PER', 'HR_289972_CAL_VEVS1_EXIST');
753 fnd_message.set_name('PER', 'HR_289970_CAL_HEVS2_EXIST');
754 -- fnd_message.set_token('PARAM1',l_param1 );
755 if l_dummy = 'H' then -- Gen Hier evs exist
756 -- updating to diff hierarchy
757 hr_multi_message.add
758 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.HIERARCHY_ID');
759 elsif l_dummy = 'V' then -- VS evs exist
760 -- updating from VS to hierarchy
761 hr_multi_message.add
762 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.VALUE_SET_ID');
763 elsif l_dummy = 'O' then -- Org Hier evs exist
764 hr_multi_message.add
765 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.ORG_STRUCTURE_VERSION_ID');
766 end if;
767
768 end if;
769 else
770 close csr_EV;
771 end if;
772 END IF;
773
774 --
775 END IF;
776 --
777 hr_utility.set_location('Leaving:'||l_proc, 40);
778 --
779 End chk_valueset_or_hierarchy;
780
781 --
782 -- ----------------------------------------------------------------------------
783 -- |----------------------< chk_legislation_code >----------------------------|
784 -- ----------------------------------------------------------------------------
785 --
786 -- {Start Of Comments}
787 --
788 -- Description:
789 -- Validates the Legislation Code exists in fnd_territories or is NULL.
790 -- Note: This should only be NOT NULL when called by SEED DB user.
791 --
792 -- In Arguments:
793 --
794 -- Post Success: processing contines
795 --
796 -- Post Failure: processing halts and error is raised.
797 --
798 Procedure chk_legislation_code (p_legislation_code in VARCHAR2) IS
799 --
800 l_proc varchar2(72) := g_package||'chk_legislation_code';
801 l_value varchar2(240) := 'DUMMY';
802 --
803 Begin
804 --
805 hr_utility.set_location('Entering:'||l_proc, 5);
806 --
807 IF p_legislation_code IS NOT NULL THEN
808 l_value := hr_general.DECODE_TERRITORY(P_TERRITORY_CODE => p_legislation_code);
809
810 IF l_value IS NULL then
811 -- Add to MM list as non-fatal
812 fnd_message.set_name('PER','PER_449075_CAL_LEG_CODE');
813 hr_multi_message.add
814 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.LEGISLATION_CODE');
815 END IF;
816 --
817 END IF;
818 --
819 hr_utility.set_location('Leaving:'||l_proc, 40);
820 --
821 End chk_legislation_code;
822 --
823 -- ----------------------------------------------------------------------------
824 -- |------------------------< chk_identifier_key >----------------------------|
825 -- ----------------------------------------------------------------------------
829 -- Description:
826 --
827 -- {Start Of Comments}
828 --
830 -- Validates the Identifier Key is UNIQUE.
831 -- Note: This should only be NOT NULL when called by SEED DB user.
832 --
833 -- In Arguments:
834 --
835 -- Post Success: processing contines
836 --
837 -- Post Failure: processing halts and error is raised.
838 --
839 Procedure chk_identifier_key (p_identifier_key in VARCHAR2) IS
840 --
841 CURSOR c_unique IS
842 SELECT 'Y'
843 FROM PER_CALENDAR_ENTRIES ENT
844 WHERE ENT.IDENTIFIER_KEY = p_identifier_key;
845
846 l_proc varchar2(72) := g_package||'chk_identifier_key';
847 l_value varchar2(240):= null;
848 --
849 Begin
850 --
851 hr_utility.set_location('Entering:'||l_proc, 5);
852 --
853 IF p_identifier_key IS NOT NULL THEN
854 open c_unique;
855 fetch c_unique into l_value;
856 IF c_unique%found THEN
857 close c_unique;
858 -- Add to MM list as non-fatal
859 fnd_message.set_name('PER','PER_449074_CAL_IDK_EXISTS');
860 hr_multi_message.add
861 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.identifier_key');
862 ELSE
863 close c_unique;
864 END IF;
865 --
866 END IF;
867 --
868 hr_utility.set_location('Leaving:'||l_proc, 40);
869 --
870 End chk_identifier_key;
871
872 --
873 -- ----------------------------------------------------------------------------
874 -- |---------------------------< chk_id_leg_comb >----------------------------|
875 -- ----------------------------------------------------------------------------
876 --
877 -- {Start Of Comments}
878 --
879 -- Description:
880 -- Validates that both legislation_code and identidfier key are supplied
881 -- if either is supplied.
882 --
883 -- In Arguments:
884 --
885 -- Post Success: processing contines
886 --
887 -- Post Failure: processing halts and error is raised.
888 --
889 Procedure chk_id_leg_comb (p_legislation_code in VARCHAR2
890 ,p_identifier_key in VARCHAR2) IS
891 --
892 l_proc varchar2(72) := g_package||'chk_id_leg_comb';
893 l_value varchar2(240) := 'DUMMY';
894 --
895 Begin
896 --
897 hr_utility.set_location('Entering:'||l_proc, 5);
898 --
899 IF (p_legislation_code IS NOT NULL AND p_identifier_key IS NULL)
900 OR (p_legislation_code IS NULL AND p_identifier_key IS NOT NULL) THEN
901
902 -- Add to MM list as non-fatal
903 fnd_message.set_name('PER','PER_449076_CAL_LEG_ID_COMB');
904 hr_multi_message.add
905 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.LEGISLATION_CODE'
906 ,p_associated_column2 => 'PER_CALENDAR_ENTRIES.IDENTIFIER_KEY');
907 END IF;
908 --
909 hr_utility.set_location('Leaving:'||l_proc, 40);
910 --
911 End chk_id_leg_comb;
912
913 --
914 -- ----------------------------------------------------------------------------
915 -- |---------------------------< insert_validate >----------------------------|
916 -- ----------------------------------------------------------------------------
917 Procedure insert_validate
918 (p_effective_date in date
919 ,p_rec in per_ent_shd.g_rec_type
920 ) is
921 --
922 l_proc varchar2(72) := g_package||'insert_validate';
923 --
924 Begin
925 hr_utility.set_location('Entering:'||l_proc, 5);
926 --
927 -- Call all supporting business operations
928 --
929 If p_rec.business_group_id is not null then
930 hr_api.validate_bus_grp_id
931 (p_business_group_id => p_rec.business_group_id
932 ,p_associated_column1 => per_ent_shd.g_tab_nam
933 || '.BUSINESS_GROUP_ID');
934 End If;
935
936 -- validate entry name
937 chk_name (p_name => p_rec.name);
938
939 -- validate entry type
940 chk_type (p_type => p_rec.type
941 ,p_calendar_entry_id => p_rec.calendar_entry_id
942 ,p_effective_date => p_effective_date);
943
944
945 -- validate start date and end date
946 chk_start_date_end_date (p_start_date => p_rec.start_date
947 ,p_end_date => p_rec.end_date
948 ,p_start_min => p_rec.start_min
949 ,p_start_hour => p_rec.start_hour
950 ,p_end_min => p_rec.end_min
951 ,p_end_hour => p_rec.end_hour
952 ,p_hierarchy_id => p_rec.hierarchy_id
953 ,p_org_structure_version_id => p_rec.org_structure_version_id);
954
955
956 -- on insert only we check that the global entry is allowed
957 -- by reading the HR_CROSS_BUSINESS_GROUP profile.
958 If p_rec.business_group_id is null then
959 If nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') <> 'Y' then
960 fnd_message.set_name('PER', 'PER_289186_CAL_ENT_GLB_INV');
961 hr_multi_message.add
962 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.BUSINESS_GROUP_ID');
963 End if;
964 End if;
965
966 -- validate VS / Hierarchy id
967 chk_valueset_or_hierarchy (p_calendar_entry_id => p_rec.calendar_entry_id
968 ,p_value_set_id => p_rec.value_set_id
969 ,p_hierarchy_id => p_rec.hierarchy_id
970 ,p_business_group_id => p_rec.business_group_id
974 -- validate Seed data params
971 ,p_organization_structure_id => p_rec.organization_structure_id
972 ,p_org_structure_version_id => p_rec.org_structure_version_id);
973
975 chk_id_leg_comb(p_legislation_code => p_rec.legislation_code,
976 p_identifier_key => p_rec.identifier_key);
977 chk_legislation_code(p_legislation_code => p_rec.legislation_code);
978 chk_identifier_key(p_identifier_key => p_rec.identifier_key);
979
980
981 --
982 -- After validating the set of important attributes,
983 -- if Multiple Message detection is enabled and at least
984 -- one error has been found then abort further validation.
985 --
986 hr_multi_message.end_validation_set;
987 --
988 -- Validate Dependent Attributes
989 --
990 --
991 hr_utility.set_location(' Leaving:'||l_proc, 10);
992 End insert_validate;
993 --
994 -- ----------------------------------------------------------------------------
995 -- |---------------------------< update_validate >----------------------------|
996 -- ----------------------------------------------------------------------------
997 Procedure update_validate
998 (p_effective_date in date
999 ,p_rec in per_ent_shd.g_rec_type
1000 ) is
1001 --
1002 l_proc varchar2(72) := g_package||'update_validate';
1003 --
1004 Begin
1005 hr_utility.set_location('Entering:'||l_proc, 5);
1006 --
1007 -- Call all supporting business operations
1008 --
1009 If p_rec.business_group_id is not null then
1010 hr_api.validate_bus_grp_id
1011 (p_business_group_id => p_rec.business_group_id
1012 ,p_associated_column1 => per_ent_shd.g_tab_nam
1013 || '.BUSINESS_GROUP_ID');
1014 End if;
1015 --
1016 -- After validating the set of important attributes,
1017 -- if Multiple Message detection is enabled and at least
1018 -- one error has been found then abort further validation.
1019 --
1020 hr_multi_message.end_validation_set;
1021 --
1022 -- Validate Dependent Attributes
1023 --
1024 chk_non_updateable_args
1025 (p_effective_date => p_effective_date
1026 ,p_rec => p_rec
1027 );
1028
1029 -- validate entry name
1030 chk_name (p_name => p_rec.name);
1031
1032 -- validate entry type
1033 chk_type (p_type => p_rec.type
1034 ,p_calendar_entry_id => p_rec.calendar_entry_id
1035 ,p_effective_date => p_effective_date);
1036
1037 -- validate start date and end date
1038 chk_start_date_end_date (p_start_date => p_rec.start_date
1039 ,p_end_date => p_rec.end_date
1040 ,p_start_min => p_rec.start_min
1041 ,p_start_hour => p_rec.start_hour
1042 ,p_end_min => p_rec.end_min
1043 ,p_end_hour => p_rec.end_hour
1044 ,p_hierarchy_id => p_rec.hierarchy_id
1045 ,p_org_structure_version_id => p_rec.org_structure_version_id);
1046
1047 If p_rec.business_group_id is null then
1048 If nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'),'N') <> 'Y' then
1049 fnd_message.set_name('PER', 'PER_289186_CAL_ENT_GLB_INV');
1050 hr_multi_message.add
1051 (p_associated_column1 => 'PER_CALENDAR_ENTRIES.BUSINESS_GROUP_ID');
1052 End if;
1053 End if;
1054
1055 -- validate VS / Hierarchy id
1056 chk_valueset_or_hierarchy (p_calendar_entry_id => p_rec.calendar_entry_id
1057 ,p_value_set_id => p_rec.value_set_id
1058 ,p_hierarchy_id => p_rec.hierarchy_id
1059 ,p_business_group_id => p_rec.business_group_id
1060 ,p_organization_structure_id => p_rec.organization_structure_id
1061 ,p_org_structure_version_id => p_rec.org_structure_version_id);
1062
1063 --
1064 --
1065 hr_utility.set_location(' Leaving:'||l_proc, 10);
1066 End update_validate;
1067 --
1068 -- ----------------------------------------------------------------------------
1069 -- |---------------------------< delete_validate >----------------------------|
1070 -- ----------------------------------------------------------------------------
1071 Procedure delete_validate
1072 (p_rec in per_ent_shd.g_rec_type
1073 ) is
1074 --
1075 l_proc varchar2(72) := g_package||'delete_validate';
1076 --
1077 Begin
1078 hr_utility.set_location('Entering:'||l_proc, 5);
1079 --
1080 -- Call all supporting business operations
1081 --
1082 -- NOTE: API deletes any child entry values before this call is made.
1083 -- But now validate that there are no calendar entry values before we
1084 -- delete the entry itself.
1085 chk_no_entry_values_exist(p_calendar_entry_id => p_rec.calendar_entry_id);
1086 --
1087 --
1088 hr_utility.set_location(' Leaving:'||l_proc, 10);
1089 End delete_validate;
1090 --
1091 end per_ent_bus;
[Home] [Help]