1 Package Body per_rol_bus as
2 /* $Header: perolrhi.pkb 120.0 2005/05/31 18:34:51 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_rol_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_role_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_role_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select null
28 from hr_organization_information hoi
29 , per_roles rol
30 , per_people_f per
31 where rol.role_id = p_role_id
32 and per.person_id = rol.person_id
33 and hoi.organization_id = per.business_group_id
34 and hoi.org_information_context||'' = 'Business Group Information';
35 --
36 -- Declare local variables
37 --
38 l_security_group_id number;
39 l_proc varchar2(72) := g_package||'set_security_group_id';
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'role_id'
50 ,p_argument_value => p_role_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id;
55 --
56 if csr_sec_grp%notfound then
57 --
58 close csr_sec_grp;
59 --
60 -- The primary key is invalid therefore we must error
61 --
62 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
63 fnd_message.raise_error;
64 --
65 end if;
66 close csr_sec_grp;
67 --
68 -- Set the security_group_id in CLIENT_INFO
69 --
70 hr_api.set_security_group_id
71 (p_security_group_id => l_security_group_id
72 );
73 --
74 hr_utility.set_location(' Leaving:'|| l_proc, 20);
75 --
76 end set_security_group_id;
77 --
78 -- ---------------------------------------------------------------------------
79 -- |---------------------< return_legislation_code >-------------------------|
80 -- ---------------------------------------------------------------------------
81 --
82 Function return_legislation_code
83 (p_role_id in number
84 )
85 Return Varchar2 Is
86 --
87 -- Declare cursor
88 --
89 cursor csr_leg_code is
90 select pbg.legislation_code
91 from per_business_groups pbg
92 , per_roles rol
93 , per_people_f per
94 where rol.role_id = p_role_id
95 and rol.person_id = per.person_id
96 and per.business_group_id = pbg.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 => 'role_id'
112 ,p_argument_value => p_role_id
113 );
114 --
115 if ( nvl(per_rol_bus.g_role_id, hr_api.g_number)
116 = p_role_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_rol_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_rol_bus.g_role_id := p_role_id;
147 per_rol_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_person_id >----------------------------|
155 -- ---------------------------------------------------------------------------
156 --
157 -- Desciption :
158 --
159 -- Validate that on insert PERSON_ID is not null and that
160 -- it exists in per_all_people_f on the effective_date.
161 --
162 --
163 -- Pre-conditions : None
164 --
165 -- In Arguments :
166 -- p_role_id
167 -- p_person_id
168 -- p_effective_date
169 --
170 -- Post Success :
171 -- Processing continues if person_id is not null and exists in
172 -- per_all_people_f on the effective_date.
173 --
174 -- Post Failure :
175 -- An application error will be raised and processing is
176 -- terminated.
177 --
178 -- Access Status :
179 -- Internal Table Handler Use only.
180 --
181 -- {End of Comments}
182 --
183 -- ---------------------------------------------------------------------------
184 procedure chk_person_id
185 (p_role_id in per_roles.person_id%TYPE
186 ,p_person_id in per_roles.person_id%TYPE
187 ,p_effective_date in date
188 ) is
189 --
190 l_proc varchar2(72) := g_package||'chk_person_id';
191 l_dummy number;
192 --
193 cursor csr_person_id is
194 select null
195 from per_people_f per
196 where per.person_id = p_person_id
197 and p_effective_date between per.effective_start_date
198 and per.effective_end_date;
199 --
200 begin
201 hr_utility.set_location('Entering:'||l_proc, 1);
202 --
203 -- Check mandatory person_id is set
204 --
205 if p_person_id is null then
206 hr_utility.set_message(800, 'HR_52891_INC_PERSON_ID_NULL');
207 hr_utility.raise_error;
208 end if;
209 --
210 hr_utility.set_location(l_proc, 5);
211 -- --
212 if (p_role_id is null) then
213 hr_utility.set_location(l_proc, 10);
214 --
215 -- Check that the person_id is in the per_people_f view on the effective_date
216 --
217 open csr_person_id;
218 fetch csr_person_id into l_dummy;
219 if csr_person_id%notfound then
220 close csr_person_id;
221 hr_utility.set_message(800, 'HR_52896_INC_FK_NOT_FOUND');
222 hr_utility.raise_error;
223 end if;
224 close csr_person_id;
225 end if;
226 --
227 hr_utility.set_location(' Leaving:'||l_proc, 15);
228 --
229 end chk_person_id;
230
231 -- ----------------------------------------------------------------------------
232 -- |-----------------------------< chk_dates >--------------------------------|
233 -- ----------------------------------------------------------------------------
234 procedure chk_dates
235 (p_start_date in date
236 ,p_end_date in date
237 ) is
238 --
239 l_proc varchar2(72) := g_package||'chk_dates';
240 --
241 begin
242 --
243 hr_utility.set_location('Entering: '||l_proc,5);
244 --
245 if p_end_date is NOT NULL then
246 if p_start_date > p_end_date then
247 hr_utility.set_message(800,'PER_52675_END_START_DATE');
248 hr_utility.raise_error;
249 end if;
250 end if;
251 --
252 hr_utility.set_location('Leaving: '||l_proc,10);
253 --
254 end chk_dates;
255
256 -- ----------------------------------------------------------------------------
257 -- |-----------------------------< chk_emp_rights>----------------------------|
258 -- ----------------------------------------------------------------------------
259 --
260 -- Description:
261 -- Validates that the end_of_rights_date is null when emp_rights_flag is
262 -- set to 'N' and that the end_of_rights_date is the same or later than
263 -- the end date.
264 --
265 -- Pre-Requisites:
266 -- None.
267 --
268 -- In Parameters:
269 -- p_emp_rights_flag
270 -- p_end_of_rights_date
271 -- p_end_date
272 -- p_role_id
273 --
274 -- Post Success:
275 -- Processing continues if end_of_rights_date is null or end_of_rights_date
276 -- is not null and emp_rights_flag = 'Y'. Also continues if end_date is
277 -- the same or earlier than the end of rights date.
278 --
279 -- Post Failure:
280 -- An application error is raised and processing is terminated if
281 -- end_of_rights_date is not null and emp_rights_flag = 'N'.
282 -- Also terminates if end_of_rights_date is earlier than the end date.
283 --
284 -- Access Status:
285 -- Internal Development use only.
286 --
287 -- -------------------------------------------------------------------
288 procedure chk_emp_rights(p_emp_rights_flag varchar2
289 ,p_end_of_rights_date date
290 ,p_end_date date) is
291 --
292 l_proc varchar2(72) := g_package||'chk_emp_rights';
293 --
294 begin
295 --
296 hr_utility.set_location('Entering: '||l_proc,5);
297 --
298 if p_end_of_rights_date is NOT NULL then
299 if p_emp_rights_flag = 'N' then
300 hr_utility.set_message(800,'PER_52676_EMP_RIGHTS_NO');
301 hr_utility.raise_error;
302 elsif p_end_date is NOT NULL then
303 if p_end_of_rights_date < p_end_date then
304 hr_utility.set_message(800,'PER_52677_EMP_RIGHTS_DATE');
305 hr_utility.raise_error;
306 end if;
307 end if;
308 end if;
309 --
310 hr_utility.set_location('Leaving: '||l_proc,10);
311 --
312 end chk_emp_rights;
313
314 -- ----------------------------------------------------------------------------
315 -- |-----------------------------< chk_job_group>-----------------------------|
316 -- ----------------------------------------------------------------------------
317 --
318 -- Description:
319 -- Validates that the job group exists in the person's business group.
320 -- Also validates that the role exists in the job group and that the job
321 -- group is not the default HR job group.
322 --
323 -- Pre-Requisites:
324 -- None.
325 --
326 -- In Parameters:
327 -- p_job_id
328 -- p_job_group_id
329 -- p_person_id
330 --
331 -- Post Success:
332 -- Processing continues if the role entered exists in the job group
333 -- and the job group is not the default HR job group. Also, that the
334 -- job group has the same business group as the person.
335 --
336 -- Post Failure:
337 -- An application error is raised and processing is terminated if
338 -- the role does not exist in the job group or the job group is the
339 -- default HR job group or the job group's business group is not the
340 -- same as the person's.
341 --
342 -- Access Status:
343 -- Internal Development use only.
344 --
345 -- -------------------------------------------------------------------
346 procedure chk_job_group(p_job_id number
347 ,p_job_group_id number
348 ,p_person_id number
349 ) is
350 --
351 l_proc varchar2(72) := g_package||'chk_job_group';
352 l_job_group varchar2(30);
353 l_job_in_jgr varchar2(30);
354 l_person_bgid number;
355 l_jgr_bgid number;
356 --
357 cursor csr_bg is
358 select p.business_group_id
359 ,j.business_group_id
360 from per_all_people_f p
361 ,per_job_groups j
362 where p.person_id = p_person_id
363 and j.job_group_id = p_job_group_id;
364 --
365 cursor csr_valid_job_group is
366 select 'X'
367 from per_job_groups
368 where job_group_id = p_job_group_id
369 and internal_name <> 'HR_'||business_group_id;
370 --
371 cursor csr_job_in_jgr is
372 select 'X'
373 from per_jobs
374 where job_group_id = p_job_group_id
375 and job_id = p_job_id;
376 --
377 begin
378 --
379 hr_utility.set_location('Entering: '||l_proc,5);
380 --
381 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' then
382 open csr_bg;
383 fetch csr_bg into l_person_bgid, l_jgr_bgid;
384 if l_jgr_bgid <> l_person_bgid then
385 hr_utility.set_message(800,'PER_52678_INV_BG');
386 hr_utility.raise_error;
387 end if;
388 close csr_bg;
389 end if;
390 --
391 open csr_valid_job_group;
392 fetch csr_valid_job_group into l_job_group;
393 if csr_valid_job_group%notfound then
394 hr_utility.set_message(800,'PER_52679_INV_JOB_GROUP');
395 hr_utility.raise_error;
396 else
397 open csr_job_in_jgr;
398 fetch csr_job_in_jgr into l_job_in_jgr;
399 if csr_job_in_jgr%notfound then
400 hr_utility.set_message(800,'PER_52680_ROLE_NOT_JGR');
401 hr_utility.raise_error;
402 end if;
403 close csr_job_in_jgr;
404 end if;
405 close csr_valid_job_group;
406 --
407 hr_utility.set_location('Leaving: '||l_proc,10);
408 --
409 end chk_job_group;
410 --
411 -- ----------------------------------------------------------------------------
412 -- |-----------------------------< chk_rep_body>------------------------------|
413 -- ----------------------------------------------------------------------------
414 --
415 -- Description:
416 -- Validates that the representative body is linked to the job group.
417 --
418 -- Pre-Requisites:
419 -- None.
420 --
421 -- In Parameters:
422 -- p_organization_id
423 -- p_job_group_id
424 --
425 -- Post Success:
426 -- Processing continues if the rep body is linked to the job group.
427 --
428 -- Post Failure:
429 -- An application error is raised and processing is terminated if
430 -- the rep body is not linked to the job group.
431 --
432 -- Access Status:
433 -- Internal Development use only.
434 --
435 -- -------------------------------------------------------------------
436 procedure chk_rep_body(p_organization_id number
437 ,p_job_group_id number) is
438 --
439 l_proc varchar2(72) := g_package||'chk_rep_body';
440 l_rep_jgr varchar2(30);
441 --
442 cursor csr_rep_jgr is
443 select 'X'
444 from hr_organization_information hoi
445 where org_information1 = to_char(p_job_group_id)
446 and organization_id = p_organization_id;
447 --
448 begin
449 --
450 hr_utility.set_location('Entering: '||l_proc,5);
451 --
452 if p_organization_id is NOT NULL then
453 open csr_rep_jgr;
454 fetch csr_rep_jgr into l_rep_jgr;
455 if csr_rep_jgr%notfound then
456 hr_utility.set_message(800,'PER_52681_INV_REP_BODY');
457 hr_utility.raise_error;
458 end if;
459 close csr_rep_jgr;
460 end if;
461 --
462 hr_utility.set_location('Entering: '||l_proc,10);
463 --
464 end chk_rep_body;
465 -- ----------------------------------------------------------------------------
466 -- |-----------------------< chk_flags >--------------------------------------|
467 -- ----------------------------------------------------------------------------
468 --
469 -- Description:
470 -- Validates that the primary_contact_flag and the emp_rights_flag
471 -- only have values of 'Y','N' or null.
472 --
473 -- Pre-Requisites:
474 -- None.
475 --
476 -- In Parameters:
477 -- p_primary_contact_flag
478 -- p_emp_rights_flag
479 --
480 -- Post Success:
481 -- Processing continues if primary contact flag and emp_rights_flag
482 -- have values of 'Y', 'N' or null.
483 --
484 -- Post Failure:
485 -- An application error is raised and processing is terminated.
486 --
487 -- Access Status:
488 -- Internal Development use only.
489 --
490 -- -------------------------------------------------------------------
491 procedure chk_flags(p_primary_contact_flag varchar2
492 ,p_emp_rights_flag varchar2) is
493 --
494 l_proc varchar2(72) := g_package||'chk_flags';
495 --
496 begin
497 --
498 hr_utility.set_location('Entering: '||l_proc,5);
499 --
500 if p_primary_contact_flag is NOT NULL then
501 if p_primary_contact_flag not in ('Y','N') then
502 hr_utility.set_message(800,'PER_52682_PRIM_CON_FLAG');
503 hr_utility.raise_error;
504 end if;
505 end if;
506 if p_emp_rights_flag is NOT NULL then
507 if p_emp_rights_flag not in('Y','N') then
508 hr_utility.set_message(800,'PER_52683_EMP_RIGHTS_FLAG');
509 hr_utility.raise_error;
510 end if;
511 end if;
512 --
513 hr_utility.set_location('Entering: '||l_proc,10);
514 --
515 end chk_flags;
516 -- ----------------------------------------------------------------------------
517 -- ----------------------------------------------------------------------------
518 -- |-----------------------------< chk_ddf >----------------------------------|
519 -- ----------------------------------------------------------------------------
520 --
521 -- Description:
522 -- Validates all the Developer Descriptive Flexfield values.
523 --
524 -- Prerequisites:
525 -- All other columns have been validated. Must be called as the
526 -- last step from insert_validate and update_validate.
527 --
528 -- In Arguments:
529 -- p_rec
530 --
531 -- Post Success:
532 -- If the Developer Descriptive Flexfield structure column and data values
533 -- are all valid this procedure will end normally and processing will
534 -- continue.
535 --
536 -- Post Failure:
537 -- If the Developer Descriptive Flexfield structure column value or any of
538 -- the data values are invalid then an application error is raised as
539 -- a PL/SQL exception.
540 --
541 -- Access Status:
542 -- Internal Row Handler Use Only.
543 --
544 -- ----------------------------------------------------------------------------
545 procedure chk_ddf
546 (p_rec in per_rol_shd.g_rec_type
547 ) is
548 --
549 l_proc varchar2(72) := g_package || 'chk_ddf';
550 --
551 begin
552 hr_utility.set_location('Entering:'||l_proc,10);
553 --
554 if ((p_rec.role_id is not null) and (
555 nvl(per_rol_shd.g_old_rec.role_information_category, hr_api.g_varchar2) <>
556 nvl(p_rec.role_information_category, hr_api.g_varchar2) or
557 nvl(per_rol_shd.g_old_rec.role_information1, hr_api.g_varchar2) <>
558 nvl(p_rec.role_information1, hr_api.g_varchar2) or
559 nvl(per_rol_shd.g_old_rec.role_information2, hr_api.g_varchar2) <>
560 nvl(p_rec.role_information2, hr_api.g_varchar2) or
561 nvl(per_rol_shd.g_old_rec.role_information3, hr_api.g_varchar2) <>
562 nvl(p_rec.role_information3, hr_api.g_varchar2) or
563 nvl(per_rol_shd.g_old_rec.role_information4, hr_api.g_varchar2) <>
564 nvl(p_rec.role_information4, hr_api.g_varchar2) or
565 nvl(per_rol_shd.g_old_rec.role_information5, hr_api.g_varchar2) <>
566 nvl(p_rec.role_information5, hr_api.g_varchar2) or
567 nvl(per_rol_shd.g_old_rec.role_information6, hr_api.g_varchar2) <>
568 nvl(p_rec.role_information6, hr_api.g_varchar2) or
569 nvl(per_rol_shd.g_old_rec.role_information7, hr_api.g_varchar2) <>
570 nvl(p_rec.role_information7, hr_api.g_varchar2) or
571 nvl(per_rol_shd.g_old_rec.role_information8, hr_api.g_varchar2) <>
572 nvl(p_rec.role_information8, hr_api.g_varchar2) or
573 nvl(per_rol_shd.g_old_rec.role_information9, hr_api.g_varchar2) <>
574 nvl(p_rec.role_information9, hr_api.g_varchar2) or
575 nvl(per_rol_shd.g_old_rec.role_information10, hr_api.g_varchar2) <>
576 nvl(p_rec.role_information10, hr_api.g_varchar2) or
577 nvl(per_rol_shd.g_old_rec.role_information11, hr_api.g_varchar2) <>
578 nvl(p_rec.role_information11, hr_api.g_varchar2) or
579 nvl(per_rol_shd.g_old_rec.role_information12, hr_api.g_varchar2) <>
580 nvl(p_rec.role_information12, hr_api.g_varchar2) or
581 nvl(per_rol_shd.g_old_rec.role_information13, hr_api.g_varchar2) <>
582 nvl(p_rec.role_information13, hr_api.g_varchar2) or
583 nvl(per_rol_shd.g_old_rec.role_information14, hr_api.g_varchar2) <>
584 nvl(p_rec.role_information14, hr_api.g_varchar2) or
585 nvl(per_rol_shd.g_old_rec.role_information15, hr_api.g_varchar2) <>
586 nvl(p_rec.role_information15, hr_api.g_varchar2) or
587 nvl(per_rol_shd.g_old_rec.role_information16, hr_api.g_varchar2) <>
588 nvl(p_rec.role_information16, hr_api.g_varchar2) or
589 nvl(per_rol_shd.g_old_rec.role_information17, hr_api.g_varchar2) <>
590 nvl(p_rec.role_information17, hr_api.g_varchar2) or
591 nvl(per_rol_shd.g_old_rec.role_information18, hr_api.g_varchar2) <>
592 nvl(p_rec.role_information18, hr_api.g_varchar2) or
593 nvl(per_rol_shd.g_old_rec.role_information19, hr_api.g_varchar2) <>
594 nvl(p_rec.role_information19, hr_api.g_varchar2) or
595 nvl(per_rol_shd.g_old_rec.role_information20, hr_api.g_varchar2) <>
596 nvl(p_rec.role_information20, hr_api.g_varchar2) ))
597 or (p_rec.role_id is null) then
598 --
599 -- Only execute the validation if absolutely necessary:
600 -- a) During update, the structure column value or any
601 -- of the attribute values have actually changed.
602 -- b) During insert.
603 --
604 hr_dflex_utility.ins_or_upd_descflex_attribs
605 (p_appl_short_name => 'PER'
606 ,p_descflex_name => 'Roles Developer DF'
607 ,p_attribute_category => p_rec.ROLE_INFORMATION_CATEGORY
608 ,p_attribute1_name => 'ROLE_INFORMATION1'
609 ,p_attribute1_value => p_rec.role_information1
610 ,p_attribute2_name => 'ROLE_INFORMATION2'
611 ,p_attribute2_value => p_rec.role_information2
612 ,p_attribute3_name => 'ROLE_INFORMATION3'
613 ,p_attribute3_value => p_rec.role_information3
614 ,p_attribute4_name => 'ROLE_INFORMATION4'
615 ,p_attribute4_value => p_rec.role_information4
616 ,p_attribute5_name => 'ROLE_INFORMATION5'
617 ,p_attribute5_value => p_rec.role_information5
618 ,p_attribute6_name => 'ROLE_INFORMATION6'
619 ,p_attribute6_value => p_rec.role_information6
620 ,p_attribute7_name => 'ROLE_INFORMATION7'
621 ,p_attribute7_value => p_rec.role_information7
622 ,p_attribute8_name => 'ROLE_INFORMATION8'
623 ,p_attribute8_value => p_rec.role_information8
624 ,p_attribute9_name => 'ROLE_INFORMATION9'
625 ,p_attribute9_value => p_rec.role_information9
626 ,p_attribute10_name => 'ROLE_INFORMATION10'
627 ,p_attribute10_value => p_rec.role_information10
628 ,p_attribute11_name => 'ROLE_INFORMATION11'
629 ,p_attribute11_value => p_rec.role_information11
630 ,p_attribute12_name => 'ROLE_INFORMATION12'
631 ,p_attribute12_value => p_rec.role_information12
632 ,p_attribute13_name => 'ROLE_INFORMATION13'
633 ,p_attribute13_value => p_rec.role_information13
634 ,p_attribute14_name => 'ROLE_INFORMATION14'
635 ,p_attribute14_value => p_rec.role_information14
636 ,p_attribute15_name => 'ROLE_INFORMATION15'
637 ,p_attribute15_value => p_rec.role_information15
638 ,p_attribute16_name => 'ROLE_INFORMATION16'
639 ,p_attribute16_value => p_rec.role_information16
640 ,p_attribute17_name => 'ROLE_INFORMATION17'
641 ,p_attribute17_value => p_rec.role_information17
642 ,p_attribute18_name => 'ROLE_INFORMATION18'
643 ,p_attribute18_value => p_rec.role_information18
644 ,p_attribute19_name => 'ROLE_INFORMATION19'
645 ,p_attribute19_value => p_rec.role_information19
646 ,p_attribute20_name => 'ROLE_INFORMATION20'
647 ,p_attribute20_value => p_rec.role_information20
648 );
649 end if;
650 --
651 hr_utility.set_location(' Leaving:'||l_proc,20);
652 end chk_ddf;
653 --
654 -- ----------------------------------------------------------------------------
655 -- |------------------------------< chk_df >----------------------------------|
656 -- ----------------------------------------------------------------------------
657 --
658 -- Description:
659 -- Validates all the Descriptive Flexfield values.
660 --
661 -- Prerequisites:
662 -- All other columns have been validated. Must be called as the
663 -- last step from insert_validate and update_validate.
664 --
665 -- In Arguments:
666 -- p_rec
667 --
668 -- Post Success:
669 -- If the Descriptive Flexfield structure column and data values are
670 -- all valid this procedure will end normally and processing will
671 -- continue.
672 --
673 -- Post Failure:
674 -- If the Descriptive Flexfield structure column value or any of
675 -- the data values are invalid then an application error is raised as
676 -- a PL/SQL exception.
677 --
678 -- Access Status:
679 -- Internal Row Handler Use Only.
680 --
681 -- ----------------------------------------------------------------------------
682 procedure chk_df
683 (p_rec in per_rol_shd.g_rec_type
684 ) is
685 --
686 l_proc varchar2(72) := g_package || 'chk_df';
687 --
688 begin
689 hr_utility.set_location('Entering:'||l_proc,10);
690 --
691 if ((p_rec.role_id is not null) and (
692 nvl(per_rol_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
693 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
694 nvl(per_rol_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
695 nvl(p_rec.attribute1, hr_api.g_varchar2) or
696 nvl(per_rol_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
697 nvl(p_rec.attribute2, hr_api.g_varchar2) or
698 nvl(per_rol_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
699 nvl(p_rec.attribute3, hr_api.g_varchar2) or
700 nvl(per_rol_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
701 nvl(p_rec.attribute4, hr_api.g_varchar2) or
702 nvl(per_rol_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
703 nvl(p_rec.attribute5, hr_api.g_varchar2) or
704 nvl(per_rol_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
705 nvl(p_rec.attribute6, hr_api.g_varchar2) or
706 nvl(per_rol_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
707 nvl(p_rec.attribute7, hr_api.g_varchar2) or
708 nvl(per_rol_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
709 nvl(p_rec.attribute8, hr_api.g_varchar2) or
710 nvl(per_rol_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
711 nvl(p_rec.attribute9, hr_api.g_varchar2) or
712 nvl(per_rol_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
713 nvl(p_rec.attribute10, hr_api.g_varchar2) or
714 nvl(per_rol_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
715 nvl(p_rec.attribute11, hr_api.g_varchar2) or
716 nvl(per_rol_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
717 nvl(p_rec.attribute12, hr_api.g_varchar2) or
718 nvl(per_rol_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
719 nvl(p_rec.attribute13, hr_api.g_varchar2) or
720 nvl(per_rol_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
721 nvl(p_rec.attribute14, hr_api.g_varchar2) or
722 nvl(per_rol_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
723 nvl(p_rec.attribute15, hr_api.g_varchar2) or
724 nvl(per_rol_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
725 nvl(p_rec.attribute16, hr_api.g_varchar2) or
726 nvl(per_rol_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
727 nvl(p_rec.attribute17, hr_api.g_varchar2) or
728 nvl(per_rol_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
729 nvl(p_rec.attribute18, hr_api.g_varchar2) or
730 nvl(per_rol_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
731 nvl(p_rec.attribute19, hr_api.g_varchar2) or
732 nvl(per_rol_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
733 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
734 or (p_rec.role_id is null) then
735 --
736 -- Only execute the validation if absolutely necessary:
737 -- a) During update, the structure column value or any
738 -- of the attribute values have actually changed.
739 -- b) During insert.
740 --
741 hr_dflex_utility.ins_or_upd_descflex_attribs
742 (p_appl_short_name => 'PER'
743 ,p_descflex_name => 'PER_ROLES'
744 ,p_attribute_category => p_rec.ATTRIBUTE_CATEGORY
745 ,p_attribute1_name => 'ATTRIBUTE1'
746 ,p_attribute1_value => p_rec.attribute1
747 ,p_attribute2_name => 'ATTRIBUTE2'
748 ,p_attribute2_value => p_rec.attribute2
749 ,p_attribute3_name => 'ATTRIBUTE3'
750 ,p_attribute3_value => p_rec.attribute3
751 ,p_attribute4_name => 'ATTRIBUTE4'
752 ,p_attribute4_value => p_rec.attribute4
753 ,p_attribute5_name => 'ATTRIBUTE5'
754 ,p_attribute5_value => p_rec.attribute5
755 ,p_attribute6_name => 'ATTRIBUTE6'
756 ,p_attribute6_value => p_rec.attribute6
757 ,p_attribute7_name => 'ATTRIBUTE7'
758 ,p_attribute7_value => p_rec.attribute7
759 ,p_attribute8_name => 'ATTRIBUTE8'
760 ,p_attribute8_value => p_rec.attribute8
761 ,p_attribute9_name => 'ATTRIBUTE9'
762 ,p_attribute9_value => p_rec.attribute9
763 ,p_attribute10_name => 'ATTRIBUTE10'
764 ,p_attribute10_value => p_rec.attribute10
765 ,p_attribute11_name => 'ATTRIBUTE11'
766 ,p_attribute11_value => p_rec.attribute11
767 ,p_attribute12_name => 'ATTRIBUTE12'
768 ,p_attribute12_value => p_rec.attribute12
769 ,p_attribute13_name => 'ATTRIBUTE13'
770 ,p_attribute13_value => p_rec.attribute13
771 ,p_attribute14_name => 'ATTRIBUTE14'
772 ,p_attribute14_value => p_rec.attribute14
773 ,p_attribute15_name => 'ATTRIBUTE15'
774 ,p_attribute15_value => p_rec.attribute15
775 ,p_attribute16_name => 'ATTRIBUTE16'
776 ,p_attribute16_value => p_rec.attribute16
777 ,p_attribute17_name => 'ATTRIBUTE17'
778 ,p_attribute17_value => p_rec.attribute17
779 ,p_attribute18_name => 'ATTRIBUTE18'
780 ,p_attribute18_value => p_rec.attribute18
781 ,p_attribute19_name => 'ATTRIBUTE19'
782 ,p_attribute19_value => p_rec.attribute19
783 ,p_attribute20_name => 'ATTRIBUTE20'
784 ,p_attribute20_value => p_rec.attribute20
785 );
786 end if;
787 --
788 hr_utility.set_location(' Leaving:'||l_proc,20);
789 end chk_df;
790 --
791 -- ----------------------------------------------------------------------------
792 -- |-----------------------< chk_non_updateable_args >------------------------|
793 -- ----------------------------------------------------------------------------
794 -- {Start Of Comments}
795 --
796 -- Description:
797 -- This procedure is used to ensure that non updateable attributes have
798 -- not been updated. If an attribute has been updated an error is generated.
799 --
800 -- Pre Conditions:
801 -- g_old_rec has been populated with details of the values currently in
802 -- the database.
803 --
804 -- In Arguments:
805 -- p_rec has been populated with the updated values the user would like the
806 -- record set to.
807 --
808 -- Post Success:
809 -- Processing continues if all the non updateable attributes have not
810 -- changed.
811 --
812 -- Post Failure:
813 -- An application error is raised if any of the non updatable attributes
814 -- have been altered.
815 --
816 -- {End Of Comments}
817 -- ----------------------------------------------------------------------------
818 Procedure chk_non_updateable_args
819 (p_effective_date in date
820 ,p_rec in per_rol_shd.g_rec_type
821 ) IS
822 --
823 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
824 l_error EXCEPTION;
825 l_argument varchar2(30);
826 --
827 Begin
828 --
829 -- Only proceed with the validation if a row exists for the current
830 -- record in the HR Schema.
831 --
832 IF NOT per_rol_shd.api_updating
833 (p_role_id => p_rec.role_id
834 ,p_object_version_number => p_rec.object_version_number
835 ) THEN
836 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
837 fnd_message.set_token('PROCEDURE ', l_proc);
838 fnd_message.set_token('STEP ', '5');
839 fnd_message.raise_error;
840 END IF;
841 --
842 IF nvl(p_rec.person_id, hr_api.g_number) <>
843 nvl(per_rol_shd.g_old_rec.person_id, hr_api.g_number) then
844 l_argument := 'person_id';
845 raise l_error;
846 END IF;
847 --
848 EXCEPTION
849 WHEN l_error THEN
850 hr_api.argument_changed_error
851 (p_api_name => l_proc
852 ,p_argument => l_argument);
853 WHEN OTHERS THEN
854 RAISE;
855 End chk_non_updateable_args;
856 --
857 -- ----------------------------------------------------------------------------
858 -- |---------------------------< insert_validate >----------------------------|
859 -- ----------------------------------------------------------------------------
860 Procedure insert_validate
861 (p_effective_date in date
862 ,p_rec in per_rol_shd.g_rec_type
863 ) is
864 --
865 l_proc varchar2(72) := g_package||'insert_validate';
866 --
867 Begin
868 hr_utility.set_location('Entering:'||l_proc, 5);
869 --
870 -- Call all supporting business operations
871 --
872 -- Call parent person table's set security group id function.
873 --
874 per_per_bus.set_security_group_id(p_person_id => to_number(p_rec.person_id));
875 --
876 per_rol_bus.chk_job_group(p_job_id => p_rec.job_id
877 ,p_job_group_id => p_rec.job_group_id
878 ,p_person_id => p_rec.person_id);
879 --
880 per_rol_bus.chk_rep_body(p_job_group_id => p_rec.job_group_id
881 ,p_organization_id => p_rec.organization_id);
882 --
883 per_rol_bus.chk_dates(p_start_date => p_rec.start_date
884 ,p_end_date => p_rec.end_date);
885 --
886 per_rol_bus.chk_person_id
887 (p_role_id => p_rec.role_id
888 ,p_person_id => p_rec.person_id
889 ,p_effective_date => p_effective_date);
890 --
891 per_rol_bus.chk_emp_rights
892 (p_emp_rights_flag => p_rec.emp_rights_flag
893 ,p_end_of_rights_date => p_rec.end_of_rights_date
894 ,p_end_date => p_rec.end_date);
895 --
896 per_rol_bus.chk_ddf(p_rec);
897 --
898 per_rol_bus.chk_df(p_rec);
899 --
900 hr_utility.set_location(' Leaving:'||l_proc, 10);
901 End insert_validate;
902 --
903 -- ----------------------------------------------------------------------------
904 -- |---------------------------< update_validate >----------------------------|
905 -- ----------------------------------------------------------------------------
906 Procedure update_validate
907 (p_effective_date in date
908 ,p_rec in per_rol_shd.g_rec_type
909 ) is
910 --
911 l_proc varchar2(72) := g_package||'update_validate';
912 --
913 Begin
914 hr_utility.set_location('Entering:'||l_proc, 5);
915 --
916 -- Call all supporting business operations
917 --
918 per_rol_bus.chk_job_group(p_job_id => p_rec.job_id
919 ,p_job_group_id => p_rec.job_group_id
920 ,p_person_id => p_rec.person_id);
921 --
922 per_rol_bus.chk_rep_body(p_job_group_id => p_rec.job_group_id
923 ,p_organization_id => p_rec.organization_id);
924 --
925 per_rol_bus.chk_dates(p_start_date => p_rec.start_date
926 ,p_end_date => p_rec.end_date);
927 --
928 per_rol_bus.chk_person_id
929 (p_role_id => p_rec.role_id
930 ,p_person_id => p_rec.person_id
931 ,p_effective_date => p_effective_date);
932 --
933 per_rol_bus.chk_emp_rights
934 (p_emp_rights_flag => p_rec.emp_rights_flag
935 ,p_end_of_rights_date => p_rec.end_of_rights_date
936 ,p_end_date => p_rec.end_date);
937 --
938 -- Call parent person table's set security group id function.
939 --
940 per_per_bus.set_security_group_id(p_person_id => to_number(p_rec.person_id));
941 --
942 chk_non_updateable_args
943 (p_effective_date => p_effective_date
944 ,p_rec => p_rec
945 );
946 --
947 per_rol_bus.chk_ddf(p_rec);
948 --
949 per_rol_bus.chk_df(p_rec);
950 --
951 hr_utility.set_location(' Leaving:'||l_proc, 10);
952 End update_validate;
953 --
954 -- ----------------------------------------------------------------------------
955 -- |---------------------------< delete_validate >----------------------------|
956 -- ----------------------------------------------------------------------------
957 Procedure delete_validate
958 (p_rec in per_rol_shd.g_rec_type
959 ) is
960 --
961 l_proc varchar2(72) := g_package||'delete_validate';
962 --
963 Begin
964 hr_utility.set_location('Entering:'||l_proc, 5);
965 --
966 -- Call all supporting business operations
967 --
968 hr_utility.set_location(' Leaving:'||l_proc, 10);
969 End delete_validate;
970 --
971 -- Start of fix 2497485
972 -- ----------------------------------------------------------------------------
973 -- |---------------------------< chk_dup_roles >-------------------------------|
974 -- ----------------------------------------------------------------------------
975 function chk_dup_roles
976 (p_person_id in per_roles.person_id%Type
977 ,p_job_group_id in per_roles.job_group_id%Type
978 ,p_job_id in per_roles.job_id%Type
979 ) return boolean is
980 --
981 --
982 cursor csr_dup_roles is
983 select 'X'
984 from per_roles
985 where person_id = p_person_id
986 and job_group_id = p_job_group_id
987 and job_id = p_job_id;
988 --
989 l_exist boolean := false;
990 l_dummy varchar2(1);
991 --
992 --
993 begin
994 --
995 open csr_dup_roles;
996 fetch csr_dup_roles into l_dummy;
997 if csr_dup_roles%found then
998 l_exist := true;
999 end if;
1000 --
1001 close csr_dup_roles;
1002 --
1003 return l_exist;
1004 --
1005 --
1006 end chk_dup_roles;
1007 -- End of 2497485
1008 --
1009 end per_rol_bus;
[Home] [Help]