1 Package Body per_jgr_bus as
2 /* $Header: pejgrrhi.pkb 115.9 2004/09/09 08:11:45 smparame noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_jgr_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_job_group_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_job_group_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_job_groups jgr
30 where jgr.job_group_id = p_job_group_id
31 and pbg.business_group_id = jgr.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 => 'job_group_id'
47 ,p_argument_value => p_job_group_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 -- |--------------------<chk_unique_jgr_name>--------------------------------|
77 -- ----------------------------------------------------------------------------
78 --
79 -- Description:
80 --
81 -- Validate that both the internal_name and the displayed_name of the
82 -- Job Group are unique. If HR: Cross Business Group profile is set to 'N'
83 -- they must be unique within the business group. If the HR: Cross Business
84 -- Group profile is set to 'Y' then the names must be unique across
85 -- all business groups.
86 --
87 -- Pre-requisites:
88 -- None
89 --
90 -- In parameters:
91 -- p_displayed_name
92 -- p_internal_name
93 -- p_business_group_id
94 --
95 -- Post Success:
96 -- Where business_group_id is not null, processing continues if
97 -- the displayed_name and the internal_name are unique within
98 -- that business group.
99 -- Where business_group_id is null, processing continues if the
100 -- the displayed_name and the internal_name are unique across all
101 -- business groups.
102 --
103 -- Post Failure:
104 -- An application error is raised and processing is terminated if the
105 -- internal_name and the displayed_name are not unique either in that
106 -- business group, where business_group_id is not null, or across all
107 -- business groups where business group id is null.
108 --
109 -- Access Status:
110 -- Internal table handler use only.
111 --
112 procedure chk_unique_jgr_name(p_displayed_name in varchar2
113 ,p_internal_name in varchar2
114 ,p_business_group_id in number
115 ,p_job_group_id in number
116 ) is
117 --
118 l_int_name varchar2(80);
119 l_disp_name varchar2(80);
120 l_proc varchar2(72) := g_package||'chk_unique_jgr_name';
121 l_job_Group_id number;
122 --
123 cursor csr_int_name_exists is
124 select internal_name
125 from per_job_groups
126 where business_group_id = p_business_group_id
127 and internal_name = p_internal_name
128 and job_group_id <> nvl(p_job_group_id,-999);
129 --
130 cursor csr_disp_name_exists is
131 select displayed_name
132 from per_job_groups
133 where business_group_id = p_business_group_id
134 and displayed_name = p_displayed_name
135 and job_group_id <> nvl(p_job_group_id,-999);
136 --
137 cursor csr_int_name_no_bg is
138 select internal_name
139 from per_job_groups
140 where internal_name = p_internal_name
141 and job_group_id <> nvl(p_job_group_id,-999);
142 --
143 cursor csr_disp_name_no_bg is
144 select displayed_name
145 from per_job_groups
146 where displayed_name = p_displayed_name
147 and job_group_id <> nvl(p_job_group_id,-999);
148 --
149 begin
150 --
151 l_job_Group_id := p_job_Group_id;
152 hr_utility.set_location('job_group_id '||l_job_Group_id,1);
153 hr_utility.set_location('Entering :'||l_proc,5);
154 --
155 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' then
156 open csr_disp_name_exists;
157 fetch csr_disp_name_exists into l_disp_name;
158 if csr_disp_name_exists%found then
159 close csr_disp_name_exists;
160 hr_utility.set_message(800,'PER_52661_DIS_NAME_EXIST_BG');
161 hr_utility.raise_error;
162 else open csr_int_name_exists;
163 fetch csr_int_name_exists into l_int_name;
164 if csr_int_name_exists%found then
165 close csr_int_name_exists;
166 hr_utility.set_message(800,'PER_52662_INT_NAME_EXIST_BG');
167 hr_utility.raise_error;
168 end if;
169 close csr_int_name_exists;
170 end if;
171 close csr_disp_name_exists;
172 else
173 open csr_disp_name_no_bg;
174 fetch csr_disp_name_no_bg into l_disp_name;
175 if csr_disp_name_no_bg%found then
176 close csr_disp_name_no_bg;
177 hr_utility.set_message(800,'PER_52663_DISP_NAME_EXISTS');
178 hr_utility.raise_error;
179 else open csr_int_name_no_bg;
180 fetch csr_int_name_no_bg into l_int_name;
181 if csr_int_name_no_bg%found then
182 close csr_int_name_no_bg;
183 hr_utility.set_message(800,'PER_52664_INT_NAME_EXISTS');
184 hr_utility.raise_error;
185 end if;
186 close csr_disp_name_no_bg;
187 end if;
188 close csr_int_name_no_bg;
189 end if;
190 --
191 hr_utility.set_location('Leaving :'||l_proc,10);
192 end chk_unique_jgr_name;
193
194 -- ---------------------------------------------------------------------------
195 -- |--------------------<chk_master_flag>------------------------------------|
196 -- ----------------------------------------------------------------------------
197 --
198 -- Description:
199 --
200 -- Validate that if this Job Group has a not null business_group_id
201 -- and HR: Cross Business Group profile is set to 'N' then
202 -- only one Job Group in that business group can have the master flag
203 -- set to 'Y'. Also, if business_group_id is null or HR:Cross Business Group
204 -- profile is set to 'Y' then validate that only one Job Group in all
205 -- business groups has master flag set to 'Y'.
206 --
207 -- Pre-requisites:
208 -- None
209 --
210 -- In parameters:
211 -- p_job_group_id
212 -- p_business_group_Id
213 -- p_master_flag
214 --
215 -- Post Success:
216 -- Where business_group_id is not null, processing continues if only one
217 -- job group has master flag set to 'Y' in that business group.
218 -- Where business_group_id is null, processing continues if only one
219 -- job group has master flag set to 'Y' across all business groups.
220 --
221 -- Post Failure:
222 -- An application error is raised and processing is terminated if more
223 -- than one job group has master flag set to 'Y' either in that business
224 -- group, where business group id is not null, or across all business groups
225 -- where business group id is null.
226 --
227 -- Access Status:
228 -- Internal table handler use only.
229 --
230 procedure chk_master_flag(p_job_group_id in number
231 ,p_business_group_id in number
232 ,p_master_flag in varchar2
233 ) is
234 --
235 cursor csr_master_flag_bg is
236 select master_flag
237 from per_job_groups
238 where p_business_group_id = business_group_id
239 and job_group_id <> p_job_group_id -- bug fix 3879133
240 and master_flag = 'Y';
241 --
242 cursor csr_master_flag_no_bg is
243 select master_flag
244 from per_job_groups
245 where master_flag = 'Y'
246 and job_group_id <> p_job_group_id; -- bug fix 3879133.
247 --
248 l_flag_bg varchar2(1);
249 l_flag_no_bg varchar2(1);
250 l_proc varchar2(72) := g_package||'chk_master_flag';
251 --
252 begin
253 --
254 hr_utility.set_location('Entering: '||l_proc,5);
255 --
256 if p_master_flag = 'Y'
257 --and
258 --per_jgr_shd.g_old_rec.master_flag <> p_master_flag then
259 then
260 --
261 if fnd_profile.value('HR_CROSS_BUSINESS_GROUP') = 'N' then
262 open csr_master_flag_bg;
263 fetch csr_master_flag_bg into l_flag_bg;
264 if csr_master_flag_bg%found then
265 close csr_master_flag_bg;
266 hr_utility.set_message(800,'PER_52665_MASTER_FLAG_BG');
267 hr_utility.raise_error;
268 end if;
269 close csr_master_flag_bg;
270 else
271 open csr_master_flag_no_bg;
272 fetch csr_master_flag_no_bg into l_flag_no_bg;
273 if csr_master_flag_no_bg%found then
274 close csr_master_flag_no_bg;
275 hr_utility.set_message(800,'PER_52666_MASTER_FLAG_NBG');
276 hr_utility.raise_error;
277 end if;
278 close csr_master_flag_no_bg;
279 end if;
280 --
281 hr_utility.set_location('Leaving: '||l_proc,10);
282 --
283 end if;
284 --
285 end chk_master_flag;
286 --
287 -- --------------------------------------------------------------------------
288 -- |----------------------<chk_object_version_number>------------------------|
289 -- --------------------------------------------------------------------------
290 --
291 --
292 -- Desciption :
293 --
294 -- Checks that the OVN passed is not null on delete.
295 --
296 -- Pre-conditions :
297 -- None.
298 --
299 -- In Arguments :
300 -- p_object_version_number
301 --
302 -- Post Success :
303 -- Processing continues
304 --
305 -- Post Failure :
306 -- An application error will be raised and processing is
307 -- terminated
308 --
309 -- Access Status :
310 -- Internal Table Handler Use only.
311 --
312 -- {End of Comments}
313 --
314 -- ---------------------------------------------------------------------------
315 procedure chk_object_version_number
316 (
317 p_object_version_number in per_job_groups.object_version_number%TYPE
318 ) is
319 --
320 l_proc varchar2(72) := g_package||'chk_object_version_number';
321 --
322 begin
323 --
324 hr_utility.set_location('Entering:'||l_proc, 1);
325 --
326 -- Check mandatory parameters have been set
327 --
328 hr_api.mandatory_arg_error
329 (p_api_name => l_proc
330 ,p_argument => 'object_version_number'
331 ,p_argument_value => p_object_version_number
332 );
333 --
334 hr_utility.set_location(' Leaving:'||l_proc, 3);
335 --
336 end chk_object_version_number;
337 --
338 -- ---------------------------------------------------------------------------
339 -- |---------------------< chk_delete_jgr>-----------------------------------|
340 -- ---------------------------------------------------------------------------
341 --
342 -- Description:
343 -- Validates that the default 'HR' job group is not deleted and that
344 -- user defined job groups can only be deleted if no jobs are linked
345 -- to that job group.
346 --
347 -- Pre-requisites:
348 -- None
349 --
350 -- In parameters:
351 -- p_job_group_id
352 --
353 -- Post Success:
354 -- Where the job group is not the default 'HR' job group and no
355 -- jobs exists which are linked to this job group, then processing
356 -- continues and the job group is deleted.
357 --
358 -- Post Failure:
359 -- An application error is raised and processing is terminated if
360 -- the job group is the default 'HR' job group or the job group
361 -- has jobs linked to it.
362 --
363 -- Access Status:
364 -- Internal table handler use only.
365 --
366 procedure chk_delete_jgr(p_job_group_id in number
367 ) is
368 --
369 cursor csr_jobs_exist is
370 select 'X'
371 from per_jobs
372 where job_group_id = p_job_group_id;
373 --
374 cursor csr_hr_jobgroup is
375 select 'Y'
376 from per_job_groups
377 where job_group_id = p_job_group_id
378 and internal_name = 'HR_'||to_char(business_group_id);
379 --
380 cursor csr_rep_body is
381 select 'X'
382 from hr_organization_information
383 where org_information1 in
384 (select to_char(job_group_id) from per_job_groups
385 where job_group_id = p_job_group_id);
386 --
387 l_jobs_exist varchar2(30);
388 l_hr_jobgroup varchar2(30);
389 l_rep_body varchar2(30);
390 l_proc varchar2(72) := g_package||'chk_object_version_number';
391 --
392 begin
393 hr_utility.set_location('Entering: '||l_proc,5);
394 open csr_hr_jobgroup;
395 fetch csr_hr_jobgroup into l_hr_jobgroup;
396 if csr_hr_jobgroup%found then
397 hr_utility.set_message(800,'PER_52667_DEL_HR_JGR');
398 hr_utility.raise_error;
399 end if;
400 close csr_hr_jobgroup;
401 open csr_jobs_exist;
402 fetch csr_jobs_exist into l_jobs_exist;
403 if csr_jobs_exist%found then
404 hr_utility.set_message(800,'PER_52668_JGR_JOB_EXIST');
405 hr_utility.raise_error;
406 end if;
407 close csr_jobs_exist;
408 open csr_rep_body;
409 fetch csr_rep_body into l_rep_body;
410 if csr_rep_body%found then
411 hr_utility.set_message(800,'PER_52685_JGR_REP_DEL');
412 hr_utility.raise_error;
413 end if;
414 close csr_rep_body;
415 --
416 hr_utility.set_location('Leaving: '||l_proc,10);
417 --
418 end chk_delete_jgr;
419 --
420 -- ---------------------------------------------------------------------------
421 -- |---------------------<chk_job_structure>---------------------------------|
422 -- ---------------------------------------------------------------------------
423 --
424 -- Description:
425 --
426 -- Validates that the job structure is valid in the fnd_id_flex_structures_vl table.
427 --
428 -- Pre-requisites:
429 -- None
430 --
431 -- In parameters:
432 -- p_id_flex_num
433 --
434 -- Post Success:
435 -- Where the job structure exists in fnd_id_flex_structures_vl processing
436 -- continues.
437 --
438 -- Post Failure:
439 -- An application error is raised and processing is terminated if id_flex_num
440 -- is not valid.
441 --
442 -- Access Status:
443 -- Internal table handler use only.
444 --
445 procedure chk_job_structure(p_id_flex_num in number) is
446 --
447 l_proc varchar2(72) := g_package||'chk_job_structure';
448 l_job_structure varchar2(30);
449 --
450 cursor csr_job_structure is
451 select 'X'
452 from fnd_id_flex_structures_vl fnd, hr_organization_information hoi
453 where fnd.id_flex_code = 'JOB'
454 and hoi.org_information_context = 'Business Group Information'
455 and fnd.id_flex_num = p_id_flex_num;
456 --
457 begin
458 --
459 hr_utility.set_location('Entering: '||l_proc,5);
460 --
461 if p_id_flex_num is not null then
462 open csr_job_structure;
463 fetch csr_job_structure into l_job_structure;
464 if csr_job_structure%notfound then
465 close csr_job_structure;
466 hr_utility.set_message(800,'PER_52671_JOB_STRUC_INV');
467 hr_utility.raise_error;
468 end if;
469 close csr_job_structure;
470 --
471 end if;
472 --
473 hr_utility.set_location('Leaving: '||l_proc,10);
474 --
475 end chk_job_structure;
476 --
477 -- ---------------------------------------------------------------------------
478 -- |---------------------< return_legislation_code >-------------------------|
479 -- ---------------------------------------------------------------------------
480 --
481 Function return_legislation_code
482 (p_job_group_id in number
483 )
484 Return Varchar2 Is
485 --
486 -- Declare cursor
487 --
488 cursor csr_leg_code is
489 select pbg.legislation_code
490 from per_business_groups pbg
491 , per_job_groups jgr
492 where jgr.job_group_id = p_job_group_id
493 and pbg.business_group_id = jgr.business_group_id;
494 --
495 -- Declare local variables
496 --
497 l_legislation_code varchar2(150);
498 l_proc varchar2(72) := g_package||'return_legislation_code';
499 --
500 Begin
501 --
502 hr_utility.set_location('Entering:'|| l_proc, 10);
503 --
504 -- Ensure that all the mandatory parameter are not null
505 --
506 hr_api.mandatory_arg_error
507 (p_api_name => l_proc
508 ,p_argument => 'job_group_id'
509 ,p_argument_value => p_job_group_id
510 );
511 --
512 if ( nvl(per_jgr_bus.g_job_group_id, hr_api.g_number)
513 = p_job_group_id) then
514 --
515 -- The legislation code has already been found with a previous
516 -- call to this function. Just return the value in the global
517 -- variable.
518 --
519 l_legislation_code := per_jgr_bus.g_legislation_code;
520 hr_utility.set_location(l_proc, 20);
521 else
522 --
523 -- The ID is different to the last call to this function
524 -- or this is the first call to this function.
525 --
526 open csr_leg_code;
527 fetch csr_leg_code into l_legislation_code;
528 --
529 if csr_leg_code%notfound then
530 --
531 -- The primary key is invalid therefore we must error
532 --
533 close csr_leg_code;
534 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
535 fnd_message.raise_error;
536 end if;
537 hr_utility.set_location(l_proc,30);
538 --
539 -- Set the global variables so the values are
540 -- available for the next call to this function.
541 --
542 close csr_leg_code;
543 per_jgr_bus.g_job_group_id := p_job_group_id;
544 per_jgr_bus.g_legislation_code := l_legislation_code;
545 end if;
546 hr_utility.set_location(' Leaving:'|| l_proc, 40);
547 return l_legislation_code;
548 end return_legislation_code;
549 --
550 -- ----------------------------------------------------------------------------
551 -- |-----------------------< chk_non_updateable_args >------------------------|
552 -- ----------------------------------------------------------------------------
553 -- {Start Of Comments}
554 --
555 -- Description:
556 -- This procedure is used to ensure that non updateable attributes have
557 -- not been updated. If an attribute has been updated an error is generated.
558 --
559 -- Pre Conditions:
560 -- g_old_rec has been populated with details of the values currently in
561 -- the database.
562 --
563 -- In Arguments:
564 -- p_rec has been populated with the updated values the user would like the
565 -- record set to.
566 --
567 -- Post Success:
568 -- Processing continues if all the non updateable attributes have not
569 -- changed.
570 --
571 -- Post Failure:
572 -- An application error is raised if any of the non updatable attributes
573 -- have been altered.
574 --
575 -- {End Of Comments}
576 -- ----------------------------------------------------------------------------
577 Procedure chk_non_updateable_args
578 (p_rec in per_jgr_shd.g_rec_type
579 ) IS
580 --
581 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
582 l_error EXCEPTION;
583 l_argument varchar2(30);
584 --
585 Begin
586 --
587 -- Only proceed with the validation if a row exists for the current
588 -- record in the HR Schema.
589 --
590 IF NOT per_jgr_shd.api_updating
591 (p_job_group_id => p_rec.job_group_id
592 ,p_object_version_number => p_rec.object_version_number
593 ) THEN
594 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
595 fnd_message.set_token('PROCEDURE ', l_proc);
596 fnd_message.set_token('STEP ', '5');
597 fnd_message.raise_error;
598 END IF;
599 --
600 -- EDIT_HERE: Add checks to ensure non-updateable args have
601 -- not been updated.
602 --
603 hr_utility.set_location(l_proc, 30);
604 --
605 EXCEPTION
606 WHEN l_error THEN
607 hr_api.argument_changed_error
608 (p_api_name => l_proc
609 ,p_argument => l_argument);
610 WHEN OTHERS THEN
611 RAISE;
612 End chk_non_updateable_args;
613 --
614 -- ----------------------------------------------------------------------------
615 -- |---------------------------< insert_validate >----------------------------|
616 -- ----------------------------------------------------------------------------
617 Procedure insert_validate
618 (p_effective_date in date
619 ,p_rec in per_jgr_shd.g_rec_type
620 ) is
621 --
622 l_proc varchar2(72) := g_package||'insert_validate';
623 --
624 Begin
625 hr_utility.set_location('Entering:'||l_proc, 5);
626 --
627 -- Call all supporting business operations
628 --
629 if p_rec.business_group_id is not null then
630 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
631 end if;
632 --
633 per_jgr_bus.chk_unique_jgr_name(p_displayed_name => p_rec.displayed_name
634 ,p_internal_name => p_rec.internal_name
635 ,p_business_group_id => p_rec.business_group_id
636 ,p_job_group_id => p_rec.job_group_id);
637 --
638 per_jgr_bus.chk_master_flag(p_job_group_id => p_rec.job_group_id
639 ,p_business_group_id => p_rec.business_group_id
640 ,p_master_flag => p_rec.master_flag);
641 --
642 per_jgr_bus.chk_job_structure(p_id_flex_num => p_rec.id_flex_num);
643 --
644 hr_utility.set_location(' Leaving:'||l_proc, 10);
645 End insert_validate;
646 --
647 -- ----------------------------------------------------------------------------
648 -- |---------------------------< update_validate >----------------------------|
649 -- ----------------------------------------------------------------------------
650 Procedure update_validate
651 (p_effective_date in date
652 ,p_rec in per_jgr_shd.g_rec_type
653 ) is
654 --
655 l_proc varchar2(72) := g_package||'update_validate';
656 --
657 Begin
658 hr_utility.set_location('Entering:'||l_proc, 5);
659 --
660 -- Call all supporting business operations
661 --
662 if p_rec.business_group_id is not null then
663 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
664 end if;
665 --
666 per_jgr_bus.chk_unique_jgr_name
667 (p_displayed_name => p_rec.displayed_name
668 ,p_internal_name => p_rec.internal_name
669 ,p_business_group_id => p_rec.business_group_id
670 ,p_job_group_id => p_rec.job_group_id);
671 --
672 per_jgr_bus.chk_master_flag
673 (p_job_group_id => p_rec.job_group_id
674 ,p_business_group_id => p_rec.business_group_id
675 ,p_master_flag => p_rec.master_flag);
676 --
677 per_jgr_bus.chk_job_structure
678 (p_id_flex_num => p_rec.id_flex_num);
679 --
680 chk_non_updateable_args
681 (p_rec => p_rec
682 );
683 --
684 --
685 hr_utility.set_location(' Leaving:'||l_proc, 10);
686 End update_validate;
687 --
688 -- ----------------------------------------------------------------------------
689 -- |---------------------------< delete_validate >----------------------------|
690 -- ----------------------------------------------------------------------------
691 Procedure delete_validate
692 (p_rec in per_jgr_shd.g_rec_type
693 ) is
694 --
695 l_proc varchar2(72) := g_package||'delete_validate';
696 l_int varchar2(30);
697 --
698 Begin
699 hr_utility.set_location('Entering:'||l_proc, 5);
700 --
701 -- Call all supporting business operations
702 --
703 l_int := p_rec.internal_name;
704 hr_utility.set_location('internal_name '||l_int,7);
705 --
706 chk_delete_jgr
707 (p_job_group_id => p_rec.job_group_id);
708 --
709 -- Validate Object Version Number
710 --
711 chk_object_version_number
712 (p_object_version_number => p_rec.object_version_number);
713 --
714 hr_utility.set_location(' Leaving:'||l_proc, 10);
715 End delete_validate;
716 --
717 end per_jgr_bus;
[Home] [Help]