1 Package Body per_evt_bus as
2 /* $Header: peevtrhi.pkb 120.2 2008/04/30 11:32:10 uuddavol ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_evt_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_event_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_event_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , per_events evt
30 where evt.event_id = p_event_id
31 and pbg.business_group_id = evt.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'event_id'
47 ,p_argument_value => p_event_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_event_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , per_events evt
90 where evt.event_id = p_event_id
91 and pbg.business_group_id = evt.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'event_id'
107 ,p_argument_value => p_event_id
108 );
109 --
110 if ( nvl(per_evt_bus.g_event_id, hr_api.g_number)
111 = p_event_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := per_evt_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 per_evt_bus.g_event_id := p_event_id;
142 per_evt_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |------------------------------< chk_df >----------------------------------|
150 -- ----------------------------------------------------------------------------
151 --
152 -- Description:
153 -- Validates all the Descriptive Flexfield values.
154 --
155 -- Prerequisites:
156 -- All other columns have been validated. Must be called as the
157 -- last step from insert_validate and update_validate.
158 --
159 -- In Arguments:
160 -- p_rec
161 --
162 -- Post Success:
163 -- If the Descriptive Flexfield structure column and data values are
164 -- all valid this procedure will end normally and processing will
165 -- continue.
166 --
167 -- Post Failure:
168 -- If the Descriptive Flexfield structure column value or any of
169 -- the data values are invalid then an application error is raised as
170 -- a PL/SQL exception.
171 --
172 -- Access Status:
173 -- Internal Row Handler Use Only.
174 --
175 -- ----------------------------------------------------------------------------
176 procedure chk_df
177 (p_rec in per_evt_shd.g_rec_type
178 ) is
179 --
180 l_proc varchar2(72) := g_package || 'chk_df';
181 --
182 begin
183 hr_utility.set_location('Entering:'||l_proc,10);
184 --
185 if ((p_rec.event_id is not null) and (
186 nvl(per_evt_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
187 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
188 nvl(per_evt_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
189 nvl(p_rec.attribute1, hr_api.g_varchar2) or
190 nvl(per_evt_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
191 nvl(p_rec.attribute2, hr_api.g_varchar2) or
192 nvl(per_evt_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
193 nvl(p_rec.attribute3, hr_api.g_varchar2) or
194 nvl(per_evt_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
195 nvl(p_rec.attribute4, hr_api.g_varchar2) or
196 nvl(per_evt_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
197 nvl(p_rec.attribute5, hr_api.g_varchar2) or
198 nvl(per_evt_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute6, hr_api.g_varchar2) or
200 nvl(per_evt_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
201 nvl(p_rec.attribute7, hr_api.g_varchar2) or
202 nvl(per_evt_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute8, hr_api.g_varchar2) or
204 nvl(per_evt_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute9, hr_api.g_varchar2) or
206 nvl(per_evt_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute10, hr_api.g_varchar2) or
208 nvl(per_evt_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute11, hr_api.g_varchar2) or
210 nvl(per_evt_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute12, hr_api.g_varchar2) or
212 nvl(per_evt_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute13, hr_api.g_varchar2) or
214 nvl(per_evt_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute14, hr_api.g_varchar2) or
216 nvl(per_evt_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute15, hr_api.g_varchar2) or
218 nvl(per_evt_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute16, hr_api.g_varchar2) or
220 nvl(per_evt_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute17, hr_api.g_varchar2) or
222 nvl(per_evt_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute18, hr_api.g_varchar2) or
224 nvl(per_evt_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute19, hr_api.g_varchar2) or
226 nvl(per_evt_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
228 or (p_rec.event_id is null) then
229 --
230 -- Only execute the validation if absolutely necessary:
231 -- a) During update, the structure column value or any
232 -- of the attribute values have actually changed.
233 -- b) During insert.
234 --
235 hr_dflex_utility.ins_or_upd_descflex_attribs
236 (p_appl_short_name => 'PER'
237 ,p_descflex_name => 'PER_EVENTS'
238 ,p_attribute_category => p_rec.attribute_category
239 ,p_attribute1_name => 'ATTRIBUTE1'
240 ,p_attribute1_value => p_rec.attribute1
241 ,p_attribute2_name => 'ATTRIBUTE2'
242 ,p_attribute2_value => p_rec.attribute2
243 ,p_attribute3_name => 'ATTRIBUTE3'
244 ,p_attribute3_value => p_rec.attribute3
245 ,p_attribute4_name => 'ATTRIBUTE4'
246 ,p_attribute4_value => p_rec.attribute4
247 ,p_attribute5_name => 'ATTRIBUTE5'
248 ,p_attribute5_value => p_rec.attribute5
249 ,p_attribute6_name => 'ATTRIBUTE6'
250 ,p_attribute6_value => p_rec.attribute6
251 ,p_attribute7_name => 'ATTRIBUTE7'
252 ,p_attribute7_value => p_rec.attribute7
253 ,p_attribute8_name => 'ATTRIBUTE8'
254 ,p_attribute8_value => p_rec.attribute8
255 ,p_attribute9_name => 'ATTRIBUTE9'
256 ,p_attribute9_value => p_rec.attribute9
257 ,p_attribute10_name => 'ATTRIBUTE10'
258 ,p_attribute10_value => p_rec.attribute10
259 ,p_attribute11_name => 'ATTRIBUTE11'
260 ,p_attribute11_value => p_rec.attribute11
261 ,p_attribute12_name => 'ATTRIBUTE12'
262 ,p_attribute12_value => p_rec.attribute12
263 ,p_attribute13_name => 'ATTRIBUTE13'
264 ,p_attribute13_value => p_rec.attribute13
265 ,p_attribute14_name => 'ATTRIBUTE14'
266 ,p_attribute14_value => p_rec.attribute14
267 ,p_attribute15_name => 'ATTRIBUTE15'
268 ,p_attribute15_value => p_rec.attribute15
269 ,p_attribute16_name => 'ATTRIBUTE16'
270 ,p_attribute16_value => p_rec.attribute16
271 ,p_attribute17_name => 'ATTRIBUTE17'
272 ,p_attribute17_value => p_rec.attribute17
273 ,p_attribute18_name => 'ATTRIBUTE18'
274 ,p_attribute18_value => p_rec.attribute18
275 ,p_attribute19_name => 'ATTRIBUTE19'
276 ,p_attribute19_value => p_rec.attribute19
277 ,p_attribute20_name => 'ATTRIBUTE20'
278 ,p_attribute20_value => p_rec.attribute20
279 );
280 end if;
281 --
282 hr_utility.set_location(' Leaving:'||l_proc,20);
283 end chk_df;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |-----------------------< chk_non_updateable_args >------------------------|
287 -- ----------------------------------------------------------------------------
288 -- {Start Of Comments}
289 --
290 -- Description:
291 -- This procedure is used to ensure that non updateable attributes have
292 -- not been updated. If an attribute has been updated an error is generated.
293 --
294 -- Pre Conditions:
295 -- g_old_rec has been populated with details of the values currently in
296 -- the database.
297 --
298 -- In Arguments:
299 -- p_rec has been populated with the updated values the user would like the
300 -- record set to.
301 --
302 -- Post Success:
303 -- Processing continues if all the non updateable attributes have not
304 -- changed.
305 --
306 -- Post Failure:
307 -- An application error is raised if any of the non updatable attributes
308 -- have been altered.
309 --
310 -- {End Of Comments}
311 -- ----------------------------------------------------------------------------
312 Procedure chk_non_updateable_args
313 (p_rec in per_evt_shd.g_rec_type
314 ) IS
315 --
316 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
317 l_error EXCEPTION;
318 l_argument varchar2(30);
319 --
320 Begin
321 --
322 -- Only proceed with the validation if a row exists for the current
323 -- record in the HR Schema.
324 --
325 IF NOT per_evt_shd.api_updating
326 (p_event_id => p_rec.event_id
327 ,p_object_version_number => p_rec.object_version_number
328 ) THEN
329 fnd_message.set_name('PER', 'HR_289323_EVENT_NOT_FOUND');
330 fnd_message.set_token('PROCEDURE ', l_proc);
331 fnd_message.set_token('STEP ', '5');
332 fnd_message.raise_error;
333 END IF;
334 --
335 if p_rec.business_group_id <> per_evt_shd.g_old_rec.business_group_id then
336 l_argument := 'business_group_id';
337 raise l_error;
338 end if;
339 --
340 EXCEPTION
341 WHEN l_error THEN
342 hr_api.argument_changed_error
343 (p_api_name => l_proc
344 ,p_argument => l_argument);
345 WHEN OTHERS THEN
346 RAISE;
347 End chk_non_updateable_args;
348
349 --
350 -- ----------------------------------------------------------------------------
351 -- |---------------------------< chk_event_type >-----------------------------|
352 -- ----------------------------------------------------------------------------
353 --
354 -- Description :
355 -- Perform check to make sure that :
356 -- - Typem parameter has been passed in.
357 --
358 -- In Parameters
359 -- p_type
360 --
361 -- Post Success
362 -- Processing continues.
363 --
364 -- Post Failure
365 -- An application error is raised and processing is terminated if any of
366 -- the following cases are found :
367 -- - type is not set
368 --
369 -- Access Status
370 -- Internal Development Use Only
371 --
372
373 Procedure chk_event_type
374 (p_type in per_events.type%TYPE
375 ) is
376 --
377 l_proc varchar2(72):=g_package||'chk_type';
378 --
379 begin
380 hr_utility.set_location('Entering:'|| l_proc, 20);
381 --
382 -- Check mandatory parameters have been set
383 --
384 hr_api.mandatory_arg_error
385 (
386 p_api_name => l_proc,
387 p_argument => 'type',
388 p_argument_value => p_type
389 );
390
394
391 hr_utility.set_location('Leaving:'|| l_proc, 20);
392 --
393 end chk_event_type;
395 --
396 -- ----------------------------------------------------------------------------
397 -- |---------------------------< chk_event_times >----------------------------|
398 -- ----------------------------------------------------------------------------
399 --
400 -- Description :
401 -- Perform check to make sure that :
402 -- - Validates that the start date of the event is entered
403 -- - Validates that end time and date is later or equal to start time and date
404 --
405 -- In Parameters
406 -- p_date_start
407 -- p_time_start
408 -- p_date_end
409 -- p_time_end
410 --
411 -- Post Success
412 -- Processing continues.
413 --
414 -- Post Failure
415 -- An application error is raised and processing is terminated if any of
416 -- the following cases are found :
417 -- - date_start is not set
418 -- - date_end is not later than date_start
419 --
420 -- Access Status
421 -- Internal Development Use Only
422 --
423
424 Procedure chk_event_times
425 (p_date_start in per_events.date_start%TYPE
426 ,p_date_end in per_events.date_end%TYPE
427 ,p_time_start in per_events.time_start%TYPE
428 ,p_time_end in per_events.time_end%TYPE
429 ) is
430 --
431 l_proc varchar2(72):=g_package||'chk_event_times';
432 l_time_start date;
433 l_time_end date;
434 --
435 begin
436 hr_utility.set_location('Entering:'|| l_proc, 1);
437 --
438 -- Check mandatory parameters have been set
439 --
440 hr_api.mandatory_arg_error
441 (
442 p_api_name => l_proc,
443 p_argument => 'date_start',
444 p_argument_value => p_date_start
445 );
446
447 if (p_time_start is not null) then
448 begin
449 l_time_start := to_date(p_time_start, 'HH24:MI');
450 Exception
451 WHEN OTHERS THEN
452 -- raise an error with message "The Start time is not valid"
453 hr_utility.set_message(800, 'HR_289319_START_TIME_INVALID');
454 hr_utility.raise_error;
455 end;
456 end if;
457
458 if (p_time_end IS NOT NULL) then
459 begin
460 l_time_end := to_date(p_time_end, 'HH24:MI');
461 Exception
462 WHEN OTHERS THEN
463 -- raise an error with message "The End date is not valid"
464 hr_utility.set_message(800, 'HR_289320_END_TIME_INVALID');
465 hr_utility.raise_error;
466 end;
467 end if;
468
469 if (p_date_end is not null) then
470
471 if ((p_date_end < p_date_start)
472 or ( (p_date_end = p_date_start)
473 and (nvl(l_time_end, hr_api.g_eot) < nvl(l_time_start, hr_api.g_sot)))
474 ) then
475 -- raise an error with message "The Start date must be before the End date."
476 hr_utility.set_message(800, 'HR_289321_START_DATE_AFTER_END');
477 hr_utility.raise_error;
478 end if;
479 end if;
480
481 hr_utility.set_location('Leaving:'|| l_proc, 10);
482 --
483 end chk_event_times;
484
485 --
486 -------------------------------------------------------------------------------
487 --------------------------------<chk_emp_or_apl>-------------------------------
488 -------------------------------------------------------------------------------
489 --
490 -- Description:
491 -- - Validates that a valid Employee/Applicant flag is set
492 --
493 -- - Validates that it is exists as lookup code for that type
494 --
495 -- In Arguments:
496 -- p_emp_or_apl
497 --
498 -- Post Success:
499 -- Process continues if :
500 -- The in parameter is valid.
501 --
502 -- Post Failure:
503 -- An application error is raised and processing is terminated if any of
504 -- the following cases are found :
505 -- - employee/applicant flag is not set or is invalid
506 --
507 -- Access Status
508 -- Internal Table Handler Use Only.
509 --
510 procedure chk_emp_or_apl
511 (p_emp_or_apl in per_events.emp_or_apl%TYPE
512 ,p_date_start in date
513 )
514 is
515 --
516 l_proc varchar2(72) := g_package||'chk_emp_apl';
517 --
518 begin
519 hr_utility.set_location('Entering:'|| l_proc, 10);
520 --
521 -- Whe updating only proceed with validation if :
522 -- a) The g_old_rec is current and
523 -- b) The value for employee/applicant flag has changed
524 --
525 if (per_evt_shd.g_old_rec.event_id is null
526 or nvl(per_evt_shd.g_old_rec.emp_or_apl, hr_api.g_varchar2)
527 <> nvl(p_emp_or_apl, hr_api.g_varchar2)
528 ) then
529 --
530 hr_utility.set_location(l_proc, 20);
531 --
532 -- If employee/applicant is not null then
533 -- check if the value exists in hr_lookups
534 -- where the lookup_type = 'EMP_APL'
535 --
536 if p_emp_or_apl is not null then
537 if hr_api.not_exists_in_hr_lookups
538 (p_effective_date => p_date_start
539 ,p_lookup_type => 'EMP_APL'
540 ,p_lookup_code => p_emp_or_apl
544 hr_utility.raise_error;
541 ) then
542 -- error invalid employee/applicant flag
543 hr_utility.set_message(800, 'HR_289310_EMP_APL_NOT_FOUND');
545 end if;
546 hr_utility.set_location(l_proc, 30);
547 end if;
548 end if;
549 hr_utility.set_location('Leaving: '|| l_proc, 40);
550 end chk_emp_or_apl;
551
552 --
553 --------------------------------------------------------------------------------
554 --------------------------<chk_event_or_interview>------------------------------
555 --------------------------------------------------------------------------------
556 --
557 -- Description:
558 -- - Validates that a valid event/interview flag is set
559 --
560 -- - Validates that it is exists as lookup code for that type
561 --
562 -- In Arguments:
563 -- p_event_or_interview
564 --
565 -- Post Success:
566 -- Process continues if :
567 -- All the in parameters are valid.
568 --
569 -- Post Failure:
570 -- An application error is raised and processing is terminated if any of
571 -- the following cases are found :
572 -- - event/interview flag is invalid
573 --
574 -- Access Status
575 -- Internal Table Handler Use Only.
576 --
577 --
578 procedure chk_event_or_interview
579 (p_event_or_interview in per_events.event_or_interview%TYPE
580 ,p_date_start in date
581 )
582 is
583 --
584 l_proc varchar2(72) := g_package||'chk_event_or_interview';
585
586 begin
587 hr_utility.set_location('Entering:'|| l_proc, 10);
588 --
589 -- Only proceed with validation if :
590 -- a) The current g_old_rec is current and
591 -- b) The value for event_or_interview has changed
592 --
593 if (per_evt_shd.g_old_rec.event_id is null
594 or nvl(per_evt_shd.g_old_rec.event_or_interview, hr_api.g_varchar2)
595 <> nvl(p_event_or_interview, hr_api.g_varchar2) ) then
596 --
597 hr_utility.set_location(l_proc, 20);
598 --
599 -- If event_or_interview is not null then
600 -- check if the value exists in hr_lookups
601 -- where the lookup_type = 'EVENT_INTERVIEW'
602 --
603 --
604 if p_event_or_interview is not null then
605 if hr_api.not_exists_in_hr_lookups
606 (p_effective_date => p_date_start
607 ,p_lookup_type => 'EVENT_INTERVIEW'
608 ,p_lookup_code => p_event_or_interview
609 ) then
610 -- error invalid event/interview
611 hr_utility.set_message(800, 'HR_289311_EVT_INT_NOT_FOUND');
612 hr_utility.raise_error;
613 end if;
614 hr_utility.set_location(l_proc, 30);
615 end if;
616 end if;
617 hr_utility.set_location('Leaving: '|| l_proc, 40);
618 end chk_event_or_interview;
619
620 --
621 -- ---------------------------------------------------------------------------
622 -- |-------------------< chk_internal_contact_person_id >-------------------|
623 -- ---------------------------------------------------------------------------
624 --
625 -- Description:
626 -- - Validates that a person id exists in table per_people_f.
627 -- - Validates that the business group of the event matches
628 -- the business group of the person.
629 --
630 -- Pre-conditions:
631 -- None.
632 --
633 -- In Arguments:
634 -- p_object_version_number
635 -- p_person_id
636 -- p_business_group_id
637 -- p_date_start
638 --
639 -- Post Success:
640 -- If a row does exist in per_people_f for the given person id then
641 -- processing continues.
642 --
643 -- Post Failure:
644 -- If a row does not exist in per_people_f for the given person id then
645 -- an application error will be raised and processing is terminated.
646 --
647 -- Access Status:
648 -- Internal Table Handler Use Only.
649 --
650 -- {End Of Comments}
651 -- ----------------------------------------------------------------------------
652 procedure chk_internal_contact_person_id
653 (p_object_version_number in per_events.object_version_number%TYPE
654 ,p_internal_contact_person_id in per_events.internal_contact_person_id%TYPE
655 ,p_business_group_id in per_events.business_group_id%TYPE
656 ,p_date_start in per_events.date_start%TYPE
657 )
658 is
659 --
660 l_proc varchar2(72) := g_package||'chk_internal_contact_person_id';
661 --
662 l_business_group_id per_all_people_f.business_group_id%type;
663 --
664 cursor csr_valid_pers is
665 select ppf.business_group_id
666 from per_all_people_f ppf
667 where ppf.person_id = p_internal_contact_person_id
668 and p_date_start between
669 ppf.effective_start_date and ppf.effective_end_date
670 and (ppf.current_employee_flag = 'Y'
671 or (ppf.current_npw_flag = 'Y' and
672 nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'), 'N') = 'Y'));
673 --
674 begin
675 hr_utility.set_location('Entering:'|| l_proc, 10);
676 --
677 hr_utility.set_location('event_id : ' || per_evt_shd.g_old_rec.event_id, 11);
681 if ((per_evt_shd.g_old_rec.event_id is null and p_internal_contact_person_id is not null)
678 hr_utility.set_location('old person id : ' || per_evt_shd.g_old_rec.internal_contact_person_id, 12);
679 hr_utility.set_location('new person id : ' || p_internal_contact_person_id, 13);
680
682 or (per_evt_shd.g_old_rec.event_id is not null
683 and nvl(per_evt_shd.g_old_rec.internal_contact_person_id, hr_api.g_number)
684 <> nvl(p_internal_contact_person_id, hr_api.g_number))) then
685 --
686 hr_utility.set_location(l_proc, 20);
687 --
688 -- Check that the Internal Contact Person ID is linked to a
689 -- valid person on PER_PEOPLE_F
690 --
691 open csr_valid_pers;
692 fetch csr_valid_pers into l_business_group_id;
693 if csr_valid_pers%notfound then
694 --
695 close csr_valid_pers;
696 hr_utility.set_message(800, 'HR_51011_PER_NOT_EXIST_DATE');
697 hr_utility.raise_error;
698 --
699 end if;
700 close csr_valid_pers;
701 hr_utility.set_location(l_proc, 30);
702 --
703 -- Check that the business group of the person is the same as the
704 -- business group of the event, or that cross business group profile
705 -- option is enabled.
706 --
707
708 if nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'N' then
709 if (p_business_group_id <> l_business_group_id) then
710 --
711 hr_utility.set_message(800, 'HR_289312_PERSON_NOT_IN_BG');
712 hr_utility.raise_error;
713 --
714 end if;
715 end if;
716 --
717 end if;
718 --
719 hr_utility.set_location(' Leaving:'|| l_proc, 40);
720 end chk_internal_contact_person_id;
721
722 --
723 -- ---------------------------------------------------------------------------
724 -- |-----------------------< chk_organization_run_by_id >-----------------------|
725 -- ---------------------------------------------------------------------------
726 --
727 -- Description:
728 -- - Validates that an organization id exists in table HR_ORGANIZATION_UNITS.
729 -- - Validates that the business group of the organization matches
730 -- the business group of the event.
731 --
732 -- Pre-conditions:
733 -- None.
734 --
735 -- In Arguments:
736 -- p_organization_run_by_id
737 -- p_business_group_id
738 --
739 -- Post Success:
740 -- If a row does exist in HR_ORGANIZATION_UNITS for the given organization id then
741 -- processing continues.
742 --
743 -- Post Failure:
744 -- If a row does not exist in HR_ORGANIZATION_UNITS for the given organization id then
745 -- an application error will be raised and processing is terminated.
746 --
747 -- Access Status:
748 -- Internal Table Handler Use Only.
749 --
750 -- {End Of Comments}
751 -- ----------------------------------------------------------------------------
752 procedure chk_organization_run_by_id
753 (p_object_version_number in per_events.object_version_number%TYPE
754 ,p_organization_run_by_id in per_events.organization_run_by_id%TYPE
755 ,p_business_group_id in per_events.business_group_id%TYPE
756 ,p_date_start in per_events.date_start%TYPE
757 )
758 is
759 --
760 l_proc varchar2(72) := g_package||'chk_organization_run_by_id';
761 --
762 l_business_group_id hr_all_organization_units.business_group_id%type;
763 --
764 cursor csr_valid_org is
765 select business_group_id
766 from hr_all_organization_units hou
767 where hou.organization_id = p_organization_run_by_id
768 and p_date_start between date_from and nvl(date_to, hr_api.g_eot);
769 --
770 begin
771 hr_utility.set_location('Entering:'|| l_proc, 10);
772 --
773 if ((per_evt_shd.g_old_rec.event_id is null and p_organization_run_by_id is not null)
774 or (per_evt_shd.g_old_rec.event_id is not null
775 and nvl(per_evt_shd.g_old_rec.organization_run_by_id, hr_api.g_number)
776 <> nvl(p_organization_run_by_id, hr_api.g_number))) then
777 --
778 hr_utility.set_location(l_proc, 20);
779 --
780 -- Check that the Organization Run By is linked to a
781 -- valid organization on hr_organiztion_units
782 --
783 open csr_valid_org;
784 fetch csr_valid_org into l_business_group_id;
785 if csr_valid_org%notfound then
786 --
787 close csr_valid_org;
788 hr_utility.set_message(800, 'HR_289313_NO_SUCH_ORGANIZATION');
789 hr_utility.raise_error;
790 --
791 end if;
792 close csr_valid_org;
793 hr_utility.set_location(l_proc, 30);
794 --
795 -- Check that the business group of the organization is the same as the
796 -- business group of the event or the organization is global.
797 --
798 if nvl(fnd_profile.value('HR_CROSS_BUSINESS_GROUP'), 'N') = 'N' then
799 if (p_business_group_id <> nvl(l_business_group_id, p_business_group_id)) then
800 --
801 hr_utility.set_message(800, 'HR_289314_ORG_NOT_IN_THIS_BG');
802 hr_utility.raise_error;
803 --
804 end if;
805 end if;
806 --
807 end if;
808 --
812 --
809 hr_utility.set_location(' Leaving:'|| l_proc, 40);
810 end chk_organization_run_by_id;
811
813 -- ----------------------------------------------------------------------------
814 -- |---------------------------< chk_assignment_id >----------------------------|
815 -- ----------------------------------------------------------------------------
816 -- {Start Of Comments}
817 --
818 -- Description:
819 -- Verify that the value in assignment_ID is in the per_assignments table.
820 --
821 -- In Parameters:
822 -- p_assignment_id
823 --
824 -- Post Success:
825 -- Processing continues.
826 --
827 -- Post Failure:
828 -- An application error will be raised and processing is terminated.
829 --
830 -- Access Status:
831 -- Internal Table Handler Use Only.
832 --
833 -- {End Of Comments}
834 -- ----------------------------------------------------------------------------
835
836 -- ----------------------------------------------------------------------------
837 Procedure chk_assignment_id
838 (
839 p_assignment_id in per_events.assignment_id%type
840 ,p_date_start in per_events.date_start%type
841 ,p_business_group_id in per_events.business_group_id%TYPE
842 ) is
843 --
844 l_proc varchar2(72) := g_package||'chk_assignment_id';
845 l_business_group_id per_all_assignments_f.business_group_id%type;
846 l_assignment_id per_events.assignment_id%type;
847 --
848 cursor c_valid_asg(v_assignment_id number) is
849 select business_group_id
850 from per_all_assignments_f asg
851 where asg.assignment_id = v_assignment_id
852 and p_date_start between effective_start_date and effective_end_date;
853 --
854 Begin
855 hr_utility.set_location('Entering:'||l_proc, 10);
856 --
857 -- Only proceed with validation if :
858 -- a) The g_old_rec is current and
859 -- b) The value for assignment has changed
860 --
861 if (p_assignment_id is not null) then
862 if (p_assignment_id <> hr_api.g_number) then
863 l_assignment_id := p_assignment_id;
864 else
865 l_assignment_id := per_evt_shd.g_old_rec.assignment_id;
866 end if;
867 --
868 -- Check that the assignment_id is in the per_assignments table.
869 --
870 open c_valid_asg(l_assignment_id);
871 fetch c_valid_asg into l_business_group_id;
872 if c_valid_asg%notfound then
873 close c_valid_asg;
874 hr_utility.set_message(800, 'HR_289315_NO_SUCH_ASSIGNMENT');
875 hr_utility.raise_error;
876 end if;
877 close c_valid_asg;
878 hr_utility.set_location(l_proc, 20);
879 --
880 end if;
881 --
882 if (p_business_group_id <> nvl(l_business_group_id, p_business_group_id)) then
883 --
884 hr_utility.set_message(800, 'HR_289316_ASG_NOT_IN_THIS_BG');
885 hr_utility.raise_error;
886 --
887 end if;
888
889 hr_utility.set_location(' Leaving:'||l_proc, 30);
890 --
891 End chk_assignment_id;
892
893 --
894 -- ---------------------------------------------------------------------------
895 -- |---------------------------< chk_location_id >---------------------------|
896 -- ---------------------------------------------------------------------------
897 --
898 -- Description:
899 -- - Validates that a location id exists in table hr_locations.
900 -- - Validates that the business group of the event matches
901 -- the business group of the location if the location is not global.
902 --
903 -- Pre-conditions:
904 -- None.
905 --
906 -- In Arguments:
907 -- p_location_id
908 -- p_business_group_id
909 --
910 -- Post Success:
911 -- If a row does exist in hr_locations for the given location id then
912 -- processing continues.
913 --
914 -- Post Failure:
915 -- If a row does not exist in hr_locations for the given location id then
916 -- an application error will be raised and processing is terminated.
917 --
918 -- Access Status:
919 -- Internal Table Handler Use Only.
920 --
921 -- {End Of Comments}
922 -- ----------------------------------------------------------------------------
923 procedure chk_location_id
924 (p_object_version_number in per_events.object_version_number%TYPE
925 ,p_location_id in per_events.location_id%TYPE
926 ,p_business_group_id in per_events.business_group_id%TYPE
927 )
928 is
929 --
930 l_proc varchar2(72) := g_package||'chk_location_id';
931 --
932 l_business_group_id hr_locations_all.business_group_id%TYPE;
933 --
934 cursor csr_valid_locn is
935 select business_group_id
936 from hr_locations_all hln
937 where hln.location_id = p_location_id;
938 --
939 begin
940 hr_utility.set_location('Entering:'|| l_proc, 10);
941 --
942 if p_location_id is not null and p_location_id <> hr_api.g_number then
943 hr_utility.set_location(l_proc, 20);
944 --
945 -- Check that the Location ID is linked to a
946 -- valid location on HR_LOCATIONS
947 --
948 open csr_valid_locn;
949 fetch csr_valid_locn into l_business_group_id;
950 if csr_valid_locn%notfound then
951 --
955 --
952 close csr_valid_locn;
953 hr_utility.set_message(800, 'HR_289317_NO_SUCH_LOCATION');
954 hr_utility.raise_error;
956 end if;
957 close csr_valid_locn;
958 hr_utility.set_location(l_proc, 30);
959 --
960 -- Check that the business group of the location is the same as the
961 -- business group of the event, or is global.
962 --
963 if (p_business_group_id <> nvl(l_business_group_id, p_business_group_id)) then
964 --
965 hr_utility.set_message(800, 'HR_289318_LOC_NOT_IN_THIS_BG');
966 hr_utility.raise_error;
967 --
968 end if;
969 --
970 end if;
971 --
972 hr_utility.set_location(' Leaving:'|| l_proc, 40);
973 end chk_location_id;
974
975 -- ----------------------------------------------------------------------------
976 -- |--------------------------< chk_party_id >--------------------------------|
977 -- ----------------------------------------------------------------------------
978 --
979 --
980 -- Description:
981 -- - Validates that the person_id and the party_id are matched in
982 -- per_all_people_f
983 -- and if person_id is not null and party_id is null, derive party_id
984 -- from per_all_people_f from person_id
985 --
986 -- Pre_conditions:
987 -- A valid business_group_id
988 --
989 -- In Arguments:
990 -- A Pl/Sql record structre.
991 -- effective_date
992
993 --
994 -- Post Success:
995 -- Process continues if :
996 --
997 -- Post Failure:
998 -- An application error is raised and processing is terminated if any of
999
1000 -- Access Status:
1001 -- Internal Table Handler Use Only.
1002 --
1003 --
1004 Procedure chk_party_id(
1005 p_rec in out nocopy per_evt_shd.g_rec_type
1006 )is
1007 --
1008 l_proc varchar2(72) := g_package||'chk_party_id';
1009 l_party_id per_events.party_id%TYPE;
1010 l_party_id2 per_events.party_id%TYPE;
1011 --
1012 --
1013 -- cursor to get party_id
1014 --
1015 cursor csr_get_party_id is
1016 select max(per.party_id)
1017 from per_all_people_f per
1018 where per.person_id =
1019 (select asg.person_id from per_all_assignments_f asg
1020 where asg.assignment_id = p_rec.assignment_id
1021 and p_rec.date_start
1022 between asg.effective_start_date
1023 and asg.effective_end_date);
1024 --
1025 cursor csr_valid_party_id is
1026 select party_id
1027 from hz_parties hzp
1028 where hzp.party_id = p_rec.party_id;
1029 --
1030 begin
1031 hr_utility.set_location('Entering:'|| l_proc, 1);
1032 --
1033 --
1034 if p_rec.party_id is not null then
1035 hr_utility.set_location(l_proc,10);
1036 open csr_valid_party_id;
1037 fetch csr_valid_party_id into l_party_id2;
1038 if csr_valid_party_id%notfound then
1039 close csr_valid_party_id;
1040 hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
1041 hr_utility.set_location(l_proc,20);
1042 hr_utility.raise_error;
1043 end if;
1044 close csr_valid_party_id;
1045 elsif p_rec.assignment_id is not null then
1046 open csr_get_party_id;
1047 fetch csr_get_party_id into l_party_id;
1048 close csr_get_party_id;
1049 --
1050 -- derive party_id from per_all_people_f using assignment_id
1051 --
1052 hr_utility.set_location(l_proc,30);
1053 p_rec.party_id := l_party_id;
1054 end if;
1055 --
1056 hr_utility.set_location(' Leaving:'||l_proc,100);
1057 End chk_party_id;
1058
1059 --
1060 -- ----------------------------------------------------------------------------
1061 -- |---------------------------< insert_validate >----------------------------|
1062 -- ----------------------------------------------------------------------------
1063 Procedure insert_validate
1064 (p_rec in out nocopy per_evt_shd.g_rec_type
1065 ) is
1066 --
1067 l_proc varchar2(72) := g_package||'insert_validate';
1068 --
1069 Begin
1070 hr_utility.set_location('Entering:'||l_proc, 10);
1071
1072 -- HR/TCA merge
1073 -- if party_id is specified, business_group_id isn't required parameter
1074 --
1075 if p_rec.party_id is null and p_rec.business_group_id is not null then
1076 --
1077 -- Call all supporting business operations
1078 --
1079 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1080 end if;
1081
1082 --
1083 -- Validate CHK_EVENT_TYPE
1084 --
1085 per_evt_bus.chk_event_type
1086 (p_type => p_rec.type
1087 );
1088
1089 --
1090 -- Validate CHK_EVENT_TIMES
1091 --
1092 per_evt_bus.chk_event_times
1093 (p_date_start => p_rec.date_start
1094 ,p_date_end => p_rec.date_end
1095 ,p_time_start => p_rec.time_start
1096 ,p_time_end => p_rec.time_end
1097 );
1098
1099 --
1100 -- Validate CHK_EMP_OR_APL
1101 --
1102 per_evt_bus.chk_emp_or_apl
1103 (p_emp_or_apl => p_rec.emp_or_apl
1104 ,p_date_start => p_rec.date_start
1105 );
1106
1107 --
1108 -- Validate CHK_EVENT_OR_INTERVIEW
1109 --
1113 );
1110 per_evt_bus.chk_event_or_interview
1111 (p_event_or_interview => p_rec.event_or_interview
1112 ,p_date_start => p_rec.date_start
1114
1115 --
1116 -- Validate CHK_INTERNAL_CONTACT_PERSON_ID
1117 --
1118 per_evt_bus.chk_internal_contact_person_id
1119 (p_object_version_number => p_rec.object_version_number
1120 ,p_internal_contact_person_id => p_rec.internal_contact_person_id
1121 ,p_business_group_id => p_rec.business_group_id
1122 ,p_date_start => p_rec.date_start
1123 );
1124
1125 --
1126 -- Validate CHK_PARTY_ID
1127 --
1128 per_evt_bus.chk_party_id
1129 (p_rec
1130 );
1131 --
1132 -- Validate CHK_ORGANIZATION_RUN_BY_ID
1133 --
1134 per_evt_bus.chk_organization_run_by_id
1135 (p_object_version_number => p_rec.object_version_number
1136 ,p_organization_run_by_id => p_rec.organization_run_by_id
1137 ,p_business_group_id => p_rec.business_group_id
1138 ,p_date_start => p_rec.date_start
1139 );
1140
1141 --
1142 -- Validate CHK_ASSIGNMENT_ID
1143 --
1144 per_evt_bus.chk_assignment_id
1145 (p_assignment_id => p_rec.assignment_id
1146 ,p_date_start => p_rec.date_start
1147 ,p_business_group_id => p_rec.business_group_id
1148 );
1149
1150 --
1151 -- Validate CHK_LOCATION
1152 --
1153 per_evt_bus.chk_location_id
1154 (p_object_version_number => p_rec.object_version_number
1155 ,p_location_id => p_rec.location_id
1156 ,p_business_group_id => p_rec.business_group_id
1157 );
1158
1159 --
1160 per_evt_bus.chk_df(p_rec);
1161 --
1162 hr_utility.set_location(' Leaving:'||l_proc, 20);
1163 End insert_validate;
1164 --
1165 -- ----------------------------------------------------------------------------
1166 -- |---------------------------< update_validate >----------------------------|
1167 -- ----------------------------------------------------------------------------
1168 Procedure update_validate
1169 (p_rec in per_evt_shd.g_rec_type
1170 ) is
1171 --
1172 l_proc varchar2(72) := g_package||'update_validate';
1173 --
1174 Begin
1175 hr_utility.set_location('Entering:'||l_proc, 10);
1176 --
1177 -- HR/TCA merge
1178 -- if party_id is specified, business_group_id isn't required parameter
1179 if p_rec.business_group_id is not null then
1180 --
1181 -- Call all supporting business operations
1182 --
1183 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1184 end if;
1185 --
1186 chk_non_updateable_args
1187 (p_rec
1188 );
1189 --
1190 -- Validate CHK_EVENT_TIMES
1191 --
1192 per_evt_bus.chk_event_times
1193 (p_date_start => p_rec.date_start
1194 ,p_date_end => p_rec.date_end
1195 ,p_time_start => p_rec.time_start
1196 ,p_time_end => p_rec.time_end
1197 );
1198
1199 --
1200 -- Validate CHK_EMP_OR_APL
1201 --
1202 per_evt_bus.chk_emp_or_apl
1203 (p_emp_or_apl => p_rec.emp_or_apl
1204 ,p_date_start => p_rec.date_start
1205 );
1206
1207 --
1208 -- Validate CHK_EVENT_OR_INTERVIEW
1209 --
1210 per_evt_bus.chk_event_or_interview
1211 (p_event_or_interview => p_rec.event_or_interview
1212 ,p_date_start => p_rec.date_start
1213 );
1214
1215 --
1216 -- Validate CHK_INTERNAL_CONTACT_PERSON_ID
1217 --
1218 per_evt_bus.chk_internal_contact_person_id
1219 (p_object_version_number => p_rec.object_version_number
1220 ,p_internal_contact_person_id => p_rec.internal_contact_person_id
1221 ,p_date_start => p_rec.date_start
1222 ,p_business_group_id => p_rec.business_group_id
1223 );
1224
1225 --
1226 -- Validate CHK_ORGANIZATION_RUN_BY_ID
1227 --
1228 per_evt_bus.chk_organization_run_by_id
1229 (p_object_version_number => p_rec.object_version_number
1230 ,p_organization_run_by_id => p_rec.organization_run_by_id
1231 ,p_business_group_id => p_rec.business_group_id
1232 ,p_date_start => p_rec.date_start
1233 );
1234
1235 --
1236 -- Validate CHK_ASSIGNMENT_ID
1237 --
1238 per_evt_bus.chk_assignment_id
1239 (p_assignment_id => p_rec.assignment_id
1240 ,p_date_start => p_rec.date_start
1241 ,p_business_group_id => p_rec.business_group_id
1242 );
1243
1244 --
1245 -- Validate CHK_LOCATION
1246 --
1247 per_evt_bus.chk_location_id
1248 (p_object_version_number => p_rec.object_version_number
1249 ,p_location_id => p_rec.location_id
1250 ,p_business_group_id => p_rec.business_group_id
1251 );
1252
1253 --
1254 per_evt_bus.chk_df(p_rec);
1255 --
1256 hr_utility.set_location(' Leaving:'||l_proc, 20);
1257 End update_validate;
1258 --
1259 -- ----------------------------------------------------------------------------
1260 -- |---------------------------< delete_validate >----------------------------|
1261 -- ----------------------------------------------------------------------------
1262 Procedure delete_validate
1263 (p_rec in per_evt_shd.g_rec_type
1264 ) is
1265 --
1266 l_proc varchar2(72) := g_package||'delete_validate';
1267 --
1268 Begin
1269 hr_utility.set_location('Entering:'||l_proc, 10);
1270 --
1271 -- Call all supporting business operations
1272 --
1273 hr_utility.set_location(' Leaving:'||l_proc, 20);
1274 End delete_validate;
1275 --
1276 end per_evt_bus;
[Home] [Help]