[Home] [Help]
PACKAGE BODY: APPS.PQH_RLS_BUS
Source
1 Package Body pqh_rls_bus as
2 /* $Header: pqrlsrhi.pkb 120.0.12020000.2 2013/04/12 18:48:44 pathota ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_rls_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 -- |----------------------< check_sshr_edit_roles >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 procedure check_sshr_edit_roles (
21 p_role_type_cd varchar2,
22 p_business_group_id number,
23 p_enable_flag varchar2
24 )
25 is
26 dummy varchar2(1) := 'N';
27 cursor c1 is
28 select 'Y'
29 from pqh_roles
30 where role_type_cd = decode(p_role_type_cd,'PQH_EXCL','PQH_INCL','PQH_EXCL')
31 and enable_flag = 'Y'
32 and business_group_id = p_business_group_id;
33 --
34 cursor c2 is
35 select 'Y'
36 from pqh_roles
37 where role_type_cd = decode(p_role_type_cd,'PQH_EXCL','PQH_INCL','PQH_EXCL')
38 and enable_flag = 'Y'
39 and business_group_id is null;
40 --
41 begin
42 -- message('Fired'||:PQH_ROLES.role_type_cd||' BG: '||:PQH_ROLES.business_group_id);pause;
43
44 if p_role_type_cd in ('PQH_EXCL','PQH_INCL') and
45 p_enable_flag = 'Y' then
46 if NVL(p_business_group_id,-1) = -1 then
47 open c2;
48 fetch c2 into dummy;
49 close c2;
50 else
51 open c1;
52 fetch c1 into dummy;
53 close c1;
54 end if;
55 -- message('Dummy ' ||dummy);pause;
56 if dummy = 'Y' then -- other record is found
57 -- throw message
58
59 hr_utility.set_message(8302,'PQH_SSHR_BOTH_EDIT_PRFL_ERR');
60 hr_utility.raise_error;
61
62 end if;
63 end if;
64 end;
65 --
66 -- ---------------------------------------------------------------------------
67 -- |----------------------< set_security_group_id >--------------------------|
68 -- ---------------------------------------------------------------------------
69 --
70 Procedure set_security_group_id
71 (p_role_id in number
72 ) is
73 --
74 -- Declare cursor
75 --
76 cursor csr_sec_grp is
77 select pbg.security_group_id
78 from per_business_groups pbg
79 ,pqh_roles rls
80 where
81 rls.role_id = p_role_id
82 and pbg.business_group_id = rls.business_group_id;
83 --
84 -- Declare local variables
85 --
86 l_security_group_id number;
87 l_proc varchar2(72) := g_package||'set_security_group_id';
88 --
89 begin
90 --
91 hr_utility.set_location('Entering:'|| l_proc,10);
92 --
93 -- Ensure that all the mandatory parameter are not null
94 --
95 hr_api.mandatory_arg_error
96 (p_api_name => l_proc
97 ,p_argument => 'role_id'
98 ,p_argument_value => p_role_id
99 );
100 --
101 open csr_sec_grp;
102 fetch csr_sec_grp into l_security_group_id;
103 --
104 if csr_sec_grp%notfound then
105 --
106 close csr_sec_grp;
107 --
108 -- The primary key is invalid therefore we must error
109 --
110 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
111 fnd_message.raise_error;
112 --
113 end if;
114 close csr_sec_grp;
115 --
116 -- Set the security_group_id in CLIENT_INFO
117 --
118 hr_api.set_security_group_id
119 (p_security_group_id => l_security_group_id
120 );
121 --
122 hr_utility.set_location(' Leaving:'|| l_proc,20);
123 --
124 end set_security_group_id;
125 --
126 -- ---------------------------------------------------------------------------
127 -- |---------------------< return_legislation_code >-------------------------|
128 -- ---------------------------------------------------------------------------
129 --
130 Function return_legislation_code
131 (p_role_id in number
132 )
133 Return Varchar2 Is
134 --
135 -- Declare cursor
136 --
137 cursor csr_leg_code is
138 select pbg.legislation_code
139 from per_business_groups pbg
140 ,pqh_roles rls
141 where
142 rls.role_id = p_role_id
143 and pbg.business_group_id = rls.business_group_id;
144 --
145 -- Declare local variables
146 --
147 l_legislation_code varchar2(150);
148 l_proc varchar2(72) := g_package||'return_legislation_code';
149 --
150 Begin
151 --
152 hr_utility.set_location('Entering:'|| l_proc,10);
153 --
154 -- Ensure that all the mandatory parameter are not null
155 --
156 hr_api.mandatory_arg_error
157 (p_api_name => l_proc
158 ,p_argument => 'role_id'
159 ,p_argument_value => p_role_id
160 );
161 --
162 if ( nvl(pqh_rls_bus.g_role_id,hr_api.g_number)
163 = p_role_id) then
164 --
165 -- The legislation code has already been found with a previous
166 -- call to this function. Just return the value in the global
167 -- variable.
168 --
169 l_legislation_code := pqh_rls_bus.g_legislation_code;
170 hr_utility.set_location(l_proc,20);
171 else
172 --
173 -- The ID is different to the last call to this function
174 -- or this is the first call to this function.
175 --
176 open csr_leg_code;
177 fetch csr_leg_code into l_legislation_code;
178 --
179 if csr_leg_code%notfound then
180 --
181 -- The primary key is invalid therefore we must error
182 --
183 close csr_leg_code;
184 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
185 fnd_message.raise_error;
186 end if;
187 hr_utility.set_location(l_proc,0);
188 --
189 -- Set the global variables so the values are
190 -- available for the next call to this function.
191 --
192 close csr_leg_code;
193 pqh_rls_bus.g_role_id := p_role_id;
194 pqh_rls_bus.g_legislation_code := l_legislation_code;
195 end if;
196 hr_utility.set_location(' Leaving:'|| l_proc,40);
197 return l_legislation_code;
198 end return_legislation_code;
199 --
200 -- ----------------------------------------------------------------------------
201 -- |-----------------------< chk_non_updateable_args >------------------------|
202 -- ----------------------------------------------------------------------------
203 -- {Start Of Comments}
204 --
205 -- Description:
206 -- This procedure is used to ensure that non updateable attributes have
207 -- not been updated. If an attribute has been updated an error is generated.
208 --
209 -- Pre Conditions:
210 -- g_old_rec has been populated with details of the values currently in
211 -- the database.
212 --
213 -- In Arguments:
214 -- has been populated with the updated values the user would like the
215 -- record set to.
216 --
217 -- Post Success:
218 -- Processing continues if all the non updateable attributes have not
219 -- changed.
220 --
221 -- Post Failure:
222 -- An application error is raised if any of the non updatable attributes
223 -- have been altered.
224 --
225 -- {End Of Comments}
226 -- ----------------------------------------------------------------------------
227 Procedure chk_non_updateable_args
228 (p_effective_date in date,
229 p_rec in pqh_rls_shd.g_rec_type
230 ) IS
231 --
232 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
233 l_error EXCEPTION;
234 l_argument varchar2(30);
235 --
236 Begin
237 --
238 -- Only proceed with the validation if a row exists for the current
239 -- record in the HR Schema.
240 --
241 IF NOT pqh_rls_shd.api_updating
242 (p_role_id => p_rec.role_id
243 ,p_object_version_number => p_rec.object_version_number
244 ) THEN
245 fnd_message.set_name('PER','HR_6153_ALL_PROCEDURE_FAIL');
246 fnd_message.set_token('PROCEDURE ',l_proc);
247 fnd_message.set_token('STEP ','5');
248 fnd_message.raise_error;
249 END IF;
250 --
251 -- EDIT_HERE: Add checks to ensure non-updateable args have
252 -- not been updated.
253 --
254 EXCEPTION
255 WHEN l_error THEN
256 hr_api.argument_changed_error
257 (p_api_name => l_proc
258 ,p_argument => l_argument);
259 WHEN OTHERS THEN
260 RAISE;
261 End chk_non_updateable_args;
262
263
264
265 --
266 -- ----------------------------------------------------------------------------
267 -- |------< chk_role_id >------|
268 -- ----------------------------------------------------------------------------
269 --
270 -- Description
271 -- This procedure is used to check that the primary key for the table
272 -- is created properly. It should be null on insert and
273 -- should not be able to be updated.
274 --
275 -- Pre Conditions
276 -- None.
277 --
278 -- In Parameters
279 -- role_id PK of record being inserted or updated.
280 -- object_version_number Object version number of record being
281 -- inserted or updated.
282 --
283 -- Post Success
284 -- Processing continues
285 --
286 -- Post Failure
287 -- Errors handled by the procedure
288 --
289 -- Access Status
290 -- Internal table handler use only.
291 --
292 Procedure chk_role_id(p_role_id in number,
293 p_object_version_number in number) is
294 --
295 l_proc varchar2(72) := g_package||'chk_role_id';
296 l_api_updating boolean;
297 --
298 Begin
299 --
300 hr_utility.set_location('Entering:'||l_proc,5);
301 --
302 l_api_updating := pqh_rls_shd.api_updating
303 (p_role_id => p_role_id,
304 p_object_version_number => p_object_version_number);
305 --
306 if (l_api_updating
307 and nvl(p_role_id,hr_api.g_number)
308 <> pqh_rls_shd.g_old_rec.role_id) then
309 --
310 -- raise error as PK has changed
311 --
312 pqh_rls_shd.constraint_error('PQH_ROLES_PK');
313 --
314 elsif not l_api_updating then
315 --
316 -- check if PK is null
317 --
318 if p_role_id is not null then
319 --
320 -- raise error as PK is not null
321 --
322 pqh_rls_shd.constraint_error('PQH_ROLES_PK');
323 --
324 end if;
325 --
326 end if;
327 --
328 hr_utility.set_location('Leaving:'||l_proc,10);
329 --
330 End chk_role_id;
331 --
332 -- ----------------------------------------------------------------------------
333 -- |------< chk_enable_flag >------|
334 -- ----------------------------------------------------------------------------
335 --
336 -- Description
337 -- This procedure is used to check that the lookup value is valid.
338 --
339 -- Pre Conditions
340 -- None.
341 --
342 -- In Parameters
343 -- role_id PK of record being inserted or updated.
344 -- enable_flag Value of lookup code.
345 -- effective_date effective date
346 -- object_version_number Object version number of record being
347 -- inserted or updated.
348 --
349 -- Post Success
350 -- Processing continues
351 --
352 -- Post Failure
353 -- Error handled by procedure
354 --
355 -- Access Status
356 -- Internal table handler use only.
357 --
358 Procedure chk_enable_flag(p_role_id in number,
359 p_enable_flag in varchar2,
360 p_effective_date in date,
361 p_object_version_number in number) is
362 --
363 l_proc varchar2(72) := g_package||'chk_enable_flag';
364 l_api_updating boolean;
365 --
366
367 CURSOR csr_role_positions IS
368 SELECT count(*)
369 FROM pqh_position_roles_v
370 WHERE role_id = p_role_id;
371
372 CURSOR csr_role_name IS
373 SELECT role_name
374 FROM pqh_roles
375 WHERE role_id = p_role_id;
376
377 l_posn_count NUMBER(15) := 0;
378 l_role_name pqh_roles.role_name%TYPE;
379 Begin
380 --
381 hr_utility.set_location('Entering:'||l_proc,5);
382 --
383 l_api_updating := pqh_rls_shd.api_updating
384 (p_role_id => p_role_id,
385 p_object_version_number => p_object_version_number);
386 --
387 if (l_api_updating
388 and p_enable_flag
389 <> nvl(pqh_rls_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
390 or not l_api_updating)
391 and p_enable_flag is not null then
392 --
393 -- check if value of lookup falls within lookup type.
394 --
395 if hr_api.not_exists_in_hr_lookups
396 (p_lookup_type => 'YES_NO',
397 p_lookup_code => p_enable_flag,
398 p_effective_date => p_effective_date) then
399 --
400 -- raise error as does not exist as lookup
401 --
402 hr_utility.set_message(8302,'PQH_INVALID_ENABLE_FLAG');
403 hr_utility.raise_error;
404 --
405 end if;
406 /*
407 --
408 -- check if the role is disabled and if there are any positions attached to the role
409 -- display an error message
410 --
411 if NVL(p_enable_flag,N') = 'N' then
412
413 OPEN csr_role_positions;
414 FETCH csr_role_positions INTO l_posn_count;
415 CLOSE csr_role_positions;
416
417 if NVL(l_posn_count,0) > 0 then
418 --
419 -- get the role name for token
420 --
421 OPEN csr_role_name;
422 FETCH csr_role_name INTO l_role_name;
423 CLOSE csr_role_name;
424
425 --
426 -- raise error as posn attached to the role
427 --
428 hr_utility.set_message(8302,'PQH_ROLE_ENABLE_FLAG');
429 hr_utility.set_message_token('ROLENAME',p_role_name);
430 hr_utility.raise_error;
431 --
432 end if; -- for posn > 0
433
434 end if; -- role is disabled
435
436 */
437
438 end if;
439 --
440 hr_utility.set_location('Leaving:'||l_proc,0);
441 --
442 end chk_enable_flag;
443 --
444 -- ----------------------------------------------------------------------------
445 -- |------< chk_role_type_cd >------|
446 -- ----------------------------------------------------------------------------
447 --
448 -- Description
449 -- This procedure is used to check that the lookup value is valid.
450 --
451 -- Pre Conditions
452 -- None.
453 --
454 -- In Parameters
455 -- role_id PK of record being inserted or updated.
456 -- role_type_cd Value of lookup code.
457 -- effective_date effective date
458 -- object_version_number Object version number of record being
459 -- inserted or updated.
460 --
461 -- Post Success
462 -- Processing continues
463 --
464 -- Post Failure
465 -- Error handled by procedure
466 --
467 -- Access Status
468 -- Internal table handler use only.
469 --
470 Procedure chk_role_type_cd(p_role_id in number,
471 p_role_type_cd in varchar2,
472 p_effective_date in date,
473 p_object_version_number in number) is
474 --
475 l_proc varchar2(72) := g_package||'chk_role_type_cd';
476 l_api_updating boolean;
477 --
478 Begin
479 --
480 hr_utility.set_location('Entering:'||l_proc,5);
481 --
482 l_api_updating := pqh_rls_shd.api_updating
483 (p_role_id => p_role_id,
484 p_object_version_number => p_object_version_number);
485 --
486 if (l_api_updating
487 and p_role_type_cd
488 <> nvl(pqh_rls_shd.g_old_rec.role_type_cd,hr_api.g_varchar2)
489 or not l_api_updating)
490 and p_role_type_cd is not null then
491 --
492 -- check if value of lookup falls within lookup type.
493 --
494 if hr_api.not_exists_in_hr_lookups
495 (p_lookup_type => 'PQH_ROLE_TYPE',
496 p_lookup_code => p_role_type_cd,
497 p_effective_date => p_effective_date) then
498 --
499 -- raise error as does not exist as lookup
500 --
501 hr_utility.set_message(801,'PQH_INVALID_ROLE_TYPE');
502 hr_utility.raise_error;
503 --
504 end if;
505 --
506 end if;
507 --
508 hr_utility.set_location('Leaving:'||l_proc,0);
509 --
510 end chk_role_type_cd;
511 --
512 -- ----------------------------------------------------------------------------
513 -- |------< chk_role_assignment >------|
514 -- ----------------------------------------------------------------------------
515 --
516 -- Description
517 -- This procedure is used to check that the lookup value is valid.
518 --
519 -- Pre Conditions
520 -- None.
521 --
522 -- In Parameters
523 -- role_id PK of record being inserted or updated.
524 -- role_type_cd Value of lookup code.
525 -- effective_date effective date
526 -- object_version_number Object version number of record being
527 -- inserted or updated.
528 --
529 -- Post Success
530 -- Processing continues
531 --
532 -- Post Failure
533 -- Error handled by procedure
534 --
535 -- Access Status
536 -- Internal table handler use only.
537 --
538 Procedure chk_role_assignment(p_role_id in number,
539 p_effective_date in date,
540 p_object_version_number in number) is
541 --
542 l_proc varchar2(72) := g_package||'chk_role_type_cd';
543 l_role_assigned boolean := FALSE;
544 l_role_assign_count number(25) :=0;
545 --
546 Begin
547 --
548 hr_utility.set_location('Entering:'||l_proc,5);
549 --
550 begin
551 select count(*) into l_role_assign_count
552 from per_people_extra_info pei
553 where pei.information_type='PQH_ROLE_USERS'
554 and pei.pei_information3=p_role_id;
555
556 if l_role_assign_count >0 then
557 --
558 -- raise error as does not exist as lookup
559 --
560 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
561 hr_utility.raise_error;
562 end if;
563 --
564 end;
565 --
566 hr_utility.set_location('Leaving:'||l_proc,0);
567 --
568 end chk_role_assignment;
569 --
570 -- ----------------------------------------------------------------------------
571 -- ----------------------------------------------------------------------------
572 --
573 Procedure chk_role_name (p_role_id in number,
574 p_role_name in varchar2) is
575 --
576 l_proc varchar2(72) := g_package||'chk_role_name';
577 --
578 l_dummy varchar2(1);
579
580 cursor csr_role_name is
581 select 'X'
582 from pqh_roles
583 where role_name = p_role_name
584 and role_id <> nvl(p_role_id,0);
585
586 Begin
587 --
588 hr_utility.set_location('Entering:'||l_proc,5);
589 --
590 open csr_role_name;
591 fetch csr_role_name into l_dummy;
592 close csr_role_name;
593
594 if nvl(l_dummy ,'Y') = 'X' then
595 --
596 hr_utility.set_message(8302,'PQH_DUPLICATE_ROLE_NAME');
597 hr_utility.raise_error;
598 --
599 end if;
600
601 --
602 hr_utility.set_location('Leaving:'||l_proc,0);
603 --
604 end chk_role_name;
605 --
606 -- ----------------------------------------------------------------------------
607 -- ----------------------------------------------------------------------------
608 --
609 Procedure chk_role_delete (p_role_id in number ) is
610 --
611 l_proc varchar2(72) := g_package||'chk_role_delete';
612
613 --
614 l_cnt_templates number(9);
615 l_cnt_positions number(9);
616 l_cnt_users number(9);
617 l_cnt_routing_lists number(9);
618 --
619 cursor csr_cnt_templates is
620 select count(*)
621 from pqh_role_templates
622 where role_id = NVL(p_role_id,0)
623 and nvl(enable_flag,'N') = 'Y';
624 --
625 cursor csr_cnt_positions is
626 select count(*)
627 from pqh_position_roles_v
628 where role_id = NVL(p_role_id,0);
629 --
630 cursor csr_cnt_users is
631 select count(*)
632 from pqh_role_users_v
633 where role_id = NVL(p_role_id,0);
634 --
635 cursor csr_cnt_routing_lists is
636 select count(*)
637 from pqh_routing_list_members
638 where role_id = NVL(p_role_id,0);
639 --
640 Begin
641 --
642 hr_utility.set_location('Entering:'||l_proc, 5);
643 --
644 open csr_cnt_templates;
645 fetch csr_cnt_templates into l_cnt_templates;
646 close csr_cnt_templates;
647 --
648 open csr_cnt_positions;
649 fetch csr_cnt_positions into l_cnt_positions;
650 close csr_cnt_positions;
651 --
652 open csr_cnt_users;
653 fetch csr_cnt_users into l_cnt_users;
654 close csr_cnt_users;
655 --
656 open csr_cnt_routing_lists;
657 fetch csr_cnt_routing_lists into l_cnt_routing_lists;
658 close csr_cnt_routing_lists;
659 --
660 if nvl(l_cnt_templates ,0) <> 0 then
661 --
662 hr_utility.set_message(8302,'PQH_DELETE_ROLE');
663 hr_utility.set_message_token('ENTITY','Templates');
664 hr_utility.raise_error;
665 --
666 end if;
667 --
668 if nvl(l_cnt_positions ,0) <> 0 then
669 --
670 hr_utility.set_message(8302,'PQH_DELETE_ROLE');
671 hr_utility.set_message_token('ENTITY','Positions');
672 hr_utility.raise_error;
673 --
674 end if;
675 --
676 if nvl(l_cnt_users ,0) <> 0 then
677 --
678 hr_utility.set_message(8302,'PQH_DELETE_ROLE');
679 hr_utility.set_message_token('ENTITY','Users');
680 hr_utility.raise_error;
681 --
682 end if;
683 --
684 if nvl(l_cnt_routing_lists ,0) <> 0 then
685 --
686 hr_utility.set_message(8302,'PQH_DELETE_ROLE');
687 hr_utility.set_message_token('ENTITY','Routing Lists');
688 hr_utility.raise_error;
689 --
690 end if;
691 --
692
693 hr_utility.set_location('Leaving:'||l_proc,10);
694 --
695 end chk_role_delete;
696 --
697 -- ---------------------------------------------------------------------------
698 -- |----------------------< chk_for_pending_txns >---------------------------|
699 -- ---------------------------------------------------------------------------
700 --
701 --
702 Procedure chk_for_pending_txns(p_role_id in number) is
703 --
704 l_proc varchar2(72) := g_package||'chk_for_pending_txns';
705 l_api_updating boolean;
706 l_bus_grp_name varchar2(240);
707 --
708 cursor c_txn_cats(p_role_id number) is
709 select distinct ptc.transaction_category_id, ptc.name transaction_category,
710 ptc.business_group_id
711 from pqh_routing_list_members rlm, pqh_routing_categories rct,
712 pqh_transaction_categories ptc
713 where rlm.routing_list_id = rct.routing_list_id
714 and rlm.role_id = p_role_id
715 and rct.transaction_category_id=ptc.transaction_category_id;
716 --
717 Begin
718 --
719 hr_utility.set_location('Entering:'||l_proc, 5);
720 --
721 for r_txn_cat in c_txn_cats(p_role_id)
722 loop
723 --
724 if nvl(pqh_tct_bus.chk_active_transaction_exists(r_txn_cat.transaction_category_id),'N')
725 = 'Y' then
726 --
727 hr_utility.set_message(8302,'PQH_CANT_DEL_RLS_PNDG_TXN');
728 hr_utility.set_message_token('TRANSACTION_CATEGORY', r_txn_cat.transaction_category);
729 if (r_txn_cat.business_group_id is not null) then
730 l_bus_grp_name := hr_general.DECODE_ORGANIZATION(r_txn_cat.business_group_id);
731 else
732 l_bus_grp_name := hr_general.decode_lookup('PQH_TCT_SCOPE', 'GLOBAL');
733 end if;
734 --
735 hr_utility.set_message_token('BUSINESS_GROUP', l_bus_grp_name);
736 --
737 hr_utility.raise_error;
738 end if;
739 --
740 end loop;
741 --
742 hr_utility.set_location('Leaving:'||l_proc,10);
743 --
744 end chk_for_pending_txns;
745 --
746 -- ---------------------------------------------------------------------------
747 -- |----------------------< chk_user_pending_txns >---------------------------|
748 -- ---------------------------------------------------------------------------
749 --
750 --
751 Procedure chk_user_pending_txns(p_role_id in number,
752 p_user_id in number) is
753 --
754 l_proc varchar2(72) := g_package||'chk_user_pending_txns';
755 l_api_updating boolean;
756 --
757 cursor c_txn_cats(p_role_id number, p_user_id number) is
758 select distinct transaction_category_id
759 from pqh_routing_list_members rlm, pqh_routing_categories rct
760 where (rlm.routing_list_id = rct.routing_list_id
761 and rlm.user_id = p_user_id
762 and rlm.role_id = p_role_id)
763 or (rct.override_user_id = p_user_id and rct.override_role_id = p_role_id) ;
764 --
765 Begin
766 --
767 hr_utility.set_location('Entering:'||l_proc, 5);
768 --
769 for r_txn_cat in c_txn_cats(p_role_id, p_user_id)
770 loop
771 --
772 if nvl(pqh_tct_bus.chk_active_transaction_exists(r_txn_cat.transaction_category_id),'N')
773 = 'Y' then
774 hr_utility.set_message(8302,'PQH_CANT_DEL_USR_PNDG_TXN');
775 hr_utility.raise_error;
776 end if;
777 --
778 end loop;
779 --
780 hr_utility.set_location('Leaving:'||l_proc,10);
781 --
782 end chk_user_pending_txns;
783 --
784
785 -- mvankada
786 -- -----------------------------------------------------------------------
787 -- |------------------------------< chk_ddf >-----------------------------|
788 -- -----------------------------------------------------------------------
789 --
790 -- Description:
791 -- Validates the all Developer Descriptive Flexfield values.
792 --
793 -- Pre-conditions:
794 -- All other columns have been validated. Must be called as the
795 -- last step from insert_validate and update_validate.
796 --
797 -- In Arguments:
798 --
799 --
800 -- Post Success:
801 -- If the Developer Descriptive Flexfield structure column and data values
802 -- are all valid this procedure will end normally and processing will
803 -- continue.
804 --
805 -- Post Failure:
806 -- If the Developer Descriptive Flexfield structure column value or any of
807 -- the data values are invalid then an application error is raised as
808 -- a PL/SQL exception.
809 --
810 -- Access Status:
811 -- Internal Row Handler Use Only.
812 --
813 procedure chk_ddf
814 ( p_rec in pqh_rls_shd.g_rec_type) is
815 --
816 l_proc varchar2(72) := g_package||'chk_ddf';
817 --
818 begin
819 hr_utility.set_location('Entering:'||l_proc,10);
820 --
821
822 if ((p_rec.role_id is not null) and (
823 nvl(pqh_rls_shd.g_old_rec.information_category,hr_api.g_varchar2) <>
824 nvl(p_rec.information_category,hr_api.g_varchar2) or
825 nvl(pqh_rls_shd.g_old_rec.information1,hr_api.g_varchar2) <>
826 nvl(p_rec.information1,hr_api.g_varchar2) or
827 nvl(pqh_rls_shd.g_old_rec.information2,hr_api.g_varchar2) <>
828 nvl(p_rec.information2,hr_api.g_varchar2) or
829 nvl(pqh_rls_shd.g_old_rec.information3,hr_api.g_varchar2) <>
830 nvl(p_rec.information3,hr_api.g_varchar2) or
831 nvl(pqh_rls_shd.g_old_rec.information4,hr_api.g_varchar2) <>
832 nvl(p_rec.information4,hr_api.g_varchar2) or
833 nvl(pqh_rls_shd.g_old_rec.information5,hr_api.g_varchar2) <>
834 nvl(p_rec.information5,hr_api.g_varchar2) or
835 nvl(pqh_rls_shd.g_old_rec.information6,hr_api.g_varchar2) <>
836 nvl(p_rec.information6,hr_api.g_varchar2) or
837 nvl(pqh_rls_shd.g_old_rec.information7,hr_api.g_varchar2) <>
838 nvl(p_rec.information7,hr_api.g_varchar2) or
839 nvl(pqh_rls_shd.g_old_rec.information8,hr_api.g_varchar2) <>
840 nvl(p_rec.information8,hr_api.g_varchar2) or
841 nvl(pqh_rls_shd.g_old_rec.information9,hr_api.g_varchar2) <>
842 nvl(p_rec.information9,hr_api.g_varchar2) or
843 nvl(pqh_rls_shd.g_old_rec.information10,hr_api.g_varchar2) <>
844 nvl(p_rec.information10,hr_api.g_varchar2) or
845 nvl(pqh_rls_shd.g_old_rec.information11,hr_api.g_varchar2) <>
846 nvl(p_rec.information11,hr_api.g_varchar2) or
847 nvl(pqh_rls_shd.g_old_rec.information12,hr_api.g_varchar2) <>
848 nvl(p_rec.information12,hr_api.g_varchar2) or
849 nvl(pqh_rls_shd.g_old_rec.information13,hr_api.g_varchar2) <>
850 nvl(p_rec.information13,hr_api.g_varchar2) or
851 nvl(pqh_rls_shd.g_old_rec.information14,hr_api.g_varchar2) <>
852 nvl(p_rec.information14,hr_api.g_varchar2) or
853 nvl(pqh_rls_shd.g_old_rec.information15,hr_api.g_varchar2) <>
854 nvl(p_rec.information15,hr_api.g_varchar2) or
855 nvl(pqh_rls_shd.g_old_rec.information16,hr_api.g_varchar2) <>
856 nvl(p_rec.information16,hr_api.g_varchar2) or
857 nvl(pqh_rls_shd.g_old_rec.information17,hr_api.g_varchar2) <>
858 nvl(p_rec.information17,hr_api.g_varchar2) or
859 nvl(pqh_rls_shd.g_old_rec.information18,hr_api.g_varchar2) <>
860 nvl(p_rec.information18,hr_api.g_varchar2) or
861 nvl(pqh_rls_shd.g_old_rec.information19,hr_api.g_varchar2) <>
862 nvl(p_rec.information19,hr_api.g_varchar2) or
863 nvl(pqh_rls_shd.g_old_rec.information20,hr_api.g_varchar2) <>
864 nvl(p_rec.information20,hr_api.g_varchar2) or
865 nvl(pqh_rls_shd.g_old_rec.information21,hr_api.g_varchar2) <>
866 nvl(p_rec.information21,hr_api.g_varchar2) or
867 nvl(pqh_rls_shd.g_old_rec.information22,hr_api.g_varchar2) <>
868 nvl(p_rec.information22,hr_api.g_varchar2) or
869 nvl(pqh_rls_shd.g_old_rec.information23,hr_api.g_varchar2) <>
870 nvl(p_rec.information23,hr_api.g_varchar2) or
871 nvl(pqh_rls_shd.g_old_rec.information24,hr_api.g_varchar2) <>
872 nvl(p_rec.information24,hr_api.g_varchar2) or
873 nvl(pqh_rls_shd.g_old_rec.information25,hr_api.g_varchar2) <>
874 nvl(p_rec.information25,hr_api.g_varchar2) or
875 nvl(pqh_rls_shd.g_old_rec.information26,hr_api.g_varchar2) <>
876 nvl(p_rec.information26,hr_api.g_varchar2) or
877 nvl(pqh_rls_shd.g_old_rec.information27,hr_api.g_varchar2) <>
878 nvl(p_rec.information27,hr_api.g_varchar2) or
879 nvl(pqh_rls_shd.g_old_rec.information28,hr_api.g_varchar2) <>
880 nvl(p_rec.information28,hr_api.g_varchar2) or
881 nvl(pqh_rls_shd.g_old_rec.information29,hr_api.g_varchar2) <>
882 nvl(p_rec.information29,hr_api.g_varchar2) or
883 nvl(pqh_rls_shd.g_old_rec.information30,hr_api.g_varchar2) <>
884 nvl(p_rec.information30,hr_api.g_varchar2)))
885 or
886 (p_rec.role_id is null) then
887 --
888 -- Only execute the validation if absolutely necessary:
889 -- a) During update,the structure column value or any
890 -- of the attribute values have actually changed.
891 -- b) During insert.
892 --
893 hr_dflex_utility.ins_or_upd_descflex_attribs
894 (p_appl_short_name => 'PQH'
895 ,p_descflex_name => 'Roles Developer DF'
896 ,p_attribute_category => p_rec.information_category
897 ,p_attribute1_name => 'INFORMATION1'
898 ,p_attribute1_value => p_rec.information1
899 ,p_attribute2_name => 'INFORMATION2'
900 ,p_attribute2_value => p_rec.information2
901 ,p_attribute3_name => 'INFORMATION3'
902 ,p_attribute3_value => p_rec.information3
903 ,p_attribute4_name => 'INFORMATION4'
904 ,p_attribute4_value => p_rec.information4
905 ,p_attribute5_name => 'INFORMATION5'
906 ,p_attribute5_value => p_rec.information5
907 ,p_attribute6_name => 'INFORMATION6'
908 ,p_attribute6_value => p_rec.information6
909 ,p_attribute7_name => 'INFORMATION7'
910 ,p_attribute7_value => p_rec.information7
911 ,p_attribute8_name => 'INFORMATION8'
912 ,p_attribute8_value => p_rec.information8
913 ,p_attribute9_name => 'INFORMATION9'
914 ,p_attribute9_value => p_rec.information9
915 ,p_attribute10_name => 'INFORMATION10'
916 ,p_attribute10_value => p_rec.information10
917 ,p_attribute11_name => 'INFORMATION11'
918 ,p_attribute11_value => p_rec.information11
919 ,p_attribute12_name => 'INFORMATION12'
920 ,p_attribute12_value => p_rec.information12
921 ,p_attribute13_name => 'INFORMATION13'
922 ,p_attribute13_value => p_rec.information13
923 ,p_attribute14_name => 'INFORMATION14'
924 ,p_attribute14_value => p_rec.information14
925 ,p_attribute15_name => 'INFORMATION15'
926 ,p_attribute15_value => p_rec.information15
927 ,p_attribute16_name => 'INFORMATION16'
928 ,p_attribute16_value => p_rec.information16
929 ,p_attribute17_name => 'INFORMATION17'
930 ,p_attribute17_value => p_rec.information17
931 ,p_attribute18_name => 'INFORMATION18'
932 ,p_attribute18_value => p_rec.information18
933 ,p_attribute19_name => 'INFORMATION19'
934 ,p_attribute19_value => p_rec.information19
935 ,p_attribute20_name => 'INFORMATION20'
936 ,p_attribute20_value => p_rec.information20
937 ,p_attribute21_name => 'INFORMATION21'
938 ,p_attribute21_value => p_rec.information21
939 ,p_attribute22_name => 'INFORMATION22'
940 ,p_attribute22_value => p_rec.information22
941 ,p_attribute23_name => 'INFORMATION23'
942 ,p_attribute23_value => p_rec.information23
943 ,p_attribute24_name => 'INFORMATION24'
944 ,p_attribute24_value => p_rec.information24
945 ,p_attribute25_name => 'INFORMATION25'
946 ,p_attribute25_value => p_rec.information25
947 ,p_attribute26_name => 'INFORMATION26'
948 ,p_attribute26_value => p_rec.information26
949 ,p_attribute27_name => 'INFORMATION27'
950 ,p_attribute27_value => p_rec.information27
951 ,p_attribute28_name => 'INFORMATION28'
952 ,p_attribute28_value => p_rec.information28
953 ,p_attribute29_name => 'INFORMATION29'
954 ,p_attribute29_value => p_rec.information29
955 ,p_attribute30_name => 'INFORMATION30'
956 ,p_attribute30_value => p_rec.information30
957 );
958 end if;
959
960 hr_utility.set_location(' Leaving:'||l_proc,20);
961
962 end chk_ddf;
963
964 -- ----------------------------------------------------------------------------
965 -- |---------------------------< insert_validate >----------------------------|
966 -- ----------------------------------------------------------------------------
967 Procedure insert_validate
968 (p_effective_date in date
969 ,p_rec in pqh_rls_shd.g_rec_type
970 ) is
971 --
972 l_proc varchar2(72) := g_package||'insert_validate';
973 --
974 Begin
975 hr_utility.set_location('Entering:'||l_proc,5);
976 --
977 -- Call all supporting business operations
978 --
979 chk_role_id
980 (p_role_id => p_rec.role_id,
981 p_object_version_number => p_rec.object_version_number);
982 --
983 chk_enable_flag
984 (p_role_id => p_rec.role_id,
985 p_enable_flag => p_rec.enable_flag,
986 p_effective_date => p_effective_date,
987 p_object_version_number =>p_rec.object_version_number);
988 --
989 chk_role_type_cd
990 (p_role_id => p_rec.role_id,
991 p_role_type_cd => p_rec.role_type_cd,
992 p_effective_date => p_effective_date,
993 p_object_version_number => p_rec.object_version_number);
994 --
995 chk_role_name
996 (p_role_id => p_rec.role_id,
997 p_role_name => p_rec.role_name);
998 --
999 --
1000 if p_rec.business_group_id is not null then -- ** For Global Roles **
1001 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1002 end if;
1003 --
1004 --
1005 -- mvankada
1006 -- Developer Descriptive Flex Check
1007 -- ================================
1008 --
1009 pqh_rls_bus.chk_ddf(p_rec => p_rec);
1010 --
1011 check_sshr_edit_roles (
1012 p_role_type_cd => p_rec.role_type_cd,
1013 p_business_group_id => p_rec.business_group_id,
1014 p_enable_flag => p_rec.enable_flag );
1015 --
1016 hr_utility.set_location(' Leaving:'||l_proc,10);
1017
1018 End insert_validate;
1019 --
1020 -- ----------------------------------------------------------------------------
1021 -- |---------------------------< update_validate >----------------------------|
1022 -- ----------------------------------------------------------------------------
1023 Procedure update_validate
1024 (p_effective_date in date
1025 ,p_rec in pqh_rls_shd.g_rec_type
1026 ) is
1027 --
1028 l_proc varchar2(72) := g_package||'update_validate';
1029 --
1030 Begin
1031 hr_utility.set_location('Entering:'||l_proc,5);
1032 --
1033 -- Call all supporting business operations
1034 --
1035 chk_role_id
1036 (p_role_id => p_rec.role_id,
1037 p_object_version_number => p_rec.object_version_number);
1038 --
1039 chk_enable_flag
1040 (p_role_id => p_rec.role_id,
1041 p_enable_flag =>p_rec .enable_flag,
1042 p_effective_date => p_effective_date,
1043 p_object_version_number => p_rec.object_version_number);
1044 --
1045 chk_role_type_cd
1046 (p_role_id => p_rec.role_id,
1047 p_role_type_cd => P_rec.role_type_cd,
1048 p_effective_date => p_effective_date,
1049 p_object_version_number => p_rec.object_version_number);
1050 --
1051 chk_role_name
1052 (p_role_id => p_rec.role_id,
1053 p_role_name => p_rec.role_name);
1054 --
1055 --
1056 if p_rec.business_group_id is not null then -- ** For Global Roles **
1057 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1058 end if;
1059 --
1060 chk_non_updateable_args
1061 (p_effective_date => p_effective_date
1062 ,p_rec => p_rec
1063 );
1064 --
1065 --
1066
1067 -- mvankada
1068
1069 -- Developer Descriptive Flex Check
1070 -- ================================
1071 --
1072 pqh_rls_bus.chk_ddf(p_rec => p_rec);
1073 --
1074 check_sshr_edit_roles (
1075 p_role_type_cd => p_rec.role_type_cd,
1076 p_business_group_id => p_rec.business_group_id,
1077 p_enable_flag => p_rec.enable_flag );
1078 --
1079 hr_utility.set_location(' Leaving:'||l_proc,10);
1080 End update_validate;
1081 --
1082 -- ----------------------------------------------------------------------------
1083 -- |---------------------------< delete_validate >----------------------------|
1084 -- ----------------------------------------------------------------------------
1085 Procedure delete_validate
1086 (p_rec in pqh_rls_shd.g_rec_type
1087 ,p_effective_date in date
1088 ) is
1089 --
1090 l_proc varchar2(72) := g_package||'delete_validate';
1091 --
1092 Begin
1093 hr_utility.set_location('Entering:'||l_proc,5);
1094 --
1095 -- Call all supporting business operations
1096 --
1097 /* Commented to allow role deletion and the other data
1098 chk_role_delete
1099 (p_role_id => p_rec.role_id );
1100 --
1101 chk_role_assignment
1102 (p_role_id =>p_rec.role_id,
1103 p_effective_date => p_effective_date,
1104 p_object_version_number => p_rec.object_version_number);
1105 */
1106 --
1107 hr_utility.set_location(' Leaving:'||l_proc,10);
1108 End delete_validate;
1109 --
1110 end pqh_rls_bus;