1 PACKAGE BODY JTF_UM_WF_DELEGATION_PVT as
2 /* $Header: JTFVDELB.pls 120.5 2006/01/16 09:42:41 vimohan ship $ */
3 /**
4 * Procedure : has_admin_enrollment
5 * Type : Private
6 * Pre_reqs : None
7 * Description : Will determine if an admin has all the roles
8 * for an enrollment or not
9 * granting delegation role
10 * Parameters :
11 * input parameters
12 * @param p_subscription_id
13 * description: The subscription_id of an enrollment
14 * required : Y
15 * validation : Must be a valid subscription_id
16 * @param p_user_id:
17 * description: The user_id of a logged in user
18 * required : Y
19 * validation : Must be a valid user_id
20 * output parameters
21 * x_has_enrollment
22 * 0 - if admin does not have all the roles
23 * 1 - if admin has all the roles
24 *
25 */
26
27 MODULE_NAME CONSTANT VARCHAR2(50) := 'JTF.UM.PLSQL.JTF_UM_WF_DELEGATION_PVT';
28 l_is_debug_parameter_on boolean := JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME);
29
30 procedure has_admin_enrollment (
31 p_subscription_id in number,
32 p_user_id in number,
33 x_has_enrollment out NOCOPY number
34 ) IS
35
36 l_procedure_name CONSTANT varchar2(30) := 'has_admin_enrollment';
37 CURSOR FIND_SUB_ROLES IS SELECT PRINCIPAL_NAME FROM JTF_UM_SUBSCRIPTION_ROLE
38 WHERE SUBSCRIPTION_ID = p_subscription_id
39 AND NVL(EFFECTIVE_END_DATE , SYSDATE+1) > SYSDATE;
40
41 l_principal_name JTF_AUTH_PRINCIPALS_B.PRINCIPAL_NAME%TYPE;
42
43 BEGIN
44
45 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
46 p_message => l_procedure_name
47 );
48
49 if (JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME) ) then
50 /* Bug #3468334 */
51 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
52 p_message => 'p_subscription_id:' || p_subscription_id || '+' || 'p_user_id:' || p_user_id
53 );
54 end if;
55
56
57 x_has_enrollment := 1; -- true if there are no roles associated to an enrollment
58
59 OPEN FIND_SUB_ROLES;
60 LOOP
61 FETCH FIND_SUB_ROLES INTO l_principal_name;
62 EXIT WHEN FIND_SUB_ROLES%NOTFOUND;
63
64 x_has_enrollment := 0; -- set it to false, before checking, as we found at least one role
65
66 if JTF_UM_UTIL_PVT.check_role(
67 p_user_id => p_user_id,
68 p_principal_name => l_principal_name
69 ) then
70
71 x_has_enrollment := 1;
72
73 end if;
74
75 IF x_has_enrollment = 0 THEN
76 EXIT; -- no need to check further, if we can find at least one role
77 -- which an admin does not have
78 END IF;
79
80 END LOOP;
81 CLOSE FIND_SUB_ROLES;
82
83 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
84 p_message => l_procedure_name
85 );
86
87 END has_admin_enrollment;
88
89 /**
90 * Procedure : can_delegate
91 * Type : Private
92 * Pre_reqs : None
93 * Description : Will determine if an approver can delegate
94 * Parameters :
95 * input parameters
96 * @param p_subscription_id
97 * description: The subscription_id of an enrollment
98 * required : Y
99 * validation : Must be a valid subscription_id
100 * p_user_id:
101 * description: The user_id of an approver
102 * required : Y
103 * validation : Must be a valid user_id
104 * output parameters
105 * x_result: The value indicating whether an approver can delegate or not.
106 * This API will return true, if no delegation role has been
107 * defined for an enrollment
108 *
109 * Note:
110 *
111 * This API will call has_delagation_role() and has_admin_enrollment
112 * Please note that this API should NOT be used for the enrollments
113 * that have either "IMPLICIT" or "EXPLICIT" delegation mode.
114 */
115 procedure can_delegate(
116 p_subscription_id in number,
117 p_user_id in number,
118 x_result out NOCOPY boolean
119 ) is
120
121 l_procedure_name CONSTANT varchar2(30) := 'can_delegate';
122
123 l_has_enrollment number;
124 x_return_result boolean := false;
125
126 begin
127
128 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
129 p_message => l_procedure_name
130 );
131
132 if ( JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME) ) then
133 /* Bug #3468334 changed -- */
134 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
135 p_message => 'p_subscription_id:' || p_subscription_id || '+' || 'p_user_id:' || p_user_id
136 );
137 end if;
138
139 x_result := false;
140
141 -- Check if a user has a delegation role or not
142 has_delegation_role (
143 p_subscription_id => p_subscription_id,
144 p_user_id => p_user_id,
145 x_result => x_return_result
146 );
147
148 -- If a user does have a delegation role then, check
149 -- if a user has all the roles for an enrollment
150
151 IF x_return_result THEN
152 has_admin_enrollment (
153 p_subscription_id => p_subscription_id,
154 p_user_id => p_user_id,
155 x_has_enrollment => l_has_enrollment
156 );
157 END IF;
158
159 -- set the out parameter based on the result
160
161 IF l_has_enrollment = 1 THEN
162
163 x_result := true;
164
165 END IF;
166
167 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
168 p_message => l_procedure_name
169 );
170
171 end can_delegate;
172
173 /**
174 * Procedure : can_enrollment_delegate
175 * Type : Private
176 * Pre_reqs : None
177 * Description : Will determine if an enrollment can delegate
178 * Parameters :
179 * input parameters
180 * @param p_subscription_id
181 * description: The subscription_id of an enrollment
182 * required : Y
183 * validation : Must be a valid subscription_id
184 * p_usertype_id:
185 * description: The usertype_id of the user
186 * required : Y
187 * validation : Must be a valid usertype_id
188 * output parameters
189 * x_result: The value indicating whether an enrollment can delegate or not.
190 * This API will return true, if no delegation role has been
191 * defined for an enrollment
192 *
193 * Note:
194 */
195 procedure can_enrollment_delegate (p_subscription_id in number,
196 p_usertype_id in number,
197 x_result out NOCOPY boolean) is
198
199 l_procedure_name CONSTANT varchar2 (23) := 'can_enrollment_delegate';
200 l_sub_flag jtf_um_usertype_subscrip.subscription_flag%type;
201
202 cursor get_subscription_flag is
203 select subscription_flag
204 from jtf_um_usertype_subscrip
205 where usertype_id = p_usertype_id
206 and subscription_id = p_subscription_id
207 and effective_start_date <= sysdate
208 and nvl (effective_end_date, sysdate + 1) > sysdate;
209
210 begin
211
212 JTF_DEBUG_PUB.LOG_ENTERING_METHOD (p_module => MODULE_NAME,
213 p_message => l_procedure_name);
214
215 if ( JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME) ) then
216 /* Bug #3468334 changed -- */
217 JTF_DEBUG_PUB.LOG_PARAMETERS (p_module => MODULE_NAME,
218 p_message => 'p_subscription_id:' || p_subscription_id || '+' || 'p_usertype_id:' || p_usertype_id);
219 end if;
220
221 x_result := false;
222
223 open get_subscription_flag;
224 fetch get_subscription_flag into l_sub_flag;
225 close get_subscription_flag;
226
227 if (l_sub_flag = 'DELEGATION') or (l_sub_flag = 'DELEGATION_SELFSERVICE') then
228 x_result := true;
229 END IF;
230
231 JTF_DEBUG_PUB.LOG_EXITING_METHOD (p_module => MODULE_NAME,
232 p_message => l_procedure_name);
233
234 end can_enrollment_delegate;
235
236 /**
237 * Procedure : can_delegate_int
238 * Type : Private
239 * Pre_reqs : None
240 * Description : Will determine if an approver can delegate
241 * Parameters :
242 * input parameters
243 * @param p_subscription_id
244 * description: The subscription_id of an enrollment
245 * required : Y
246 * validation : Must be a valid subscription_id
247 * p_user_id:
248 * description: The user_id of an approver
249 * required : Y
250 * validation : Must be a valid user_id
251 * output parameters
252 * x_result: The boolean value in number, 0 or 1 indicating whether
253 * an approver can delegate or not. This API has been created
254 * as JDBC does not support boolean!!!!
255 */
256 procedure can_delegate_int(
257 p_subscription_id in number,
258 p_user_id in number,
259 x_result out NOCOPY number
260 ) IS
261
262 l_result boolean := false;
263 BEGIN
264 can_delegate(
265 p_subscription_id => p_subscription_id,
266 p_user_id => p_user_id,
267 x_result => l_result
268 );
269
270 IF l_result THEN
271 x_result := 1;
272 ELSE
273 x_result := 0;
274 END IF;
275
276 END can_delegate_int;
277
278 /**
279 * Procedure : has_delegation_role
280 * Type : Private
281 * Pre_reqs : None
282 * Description : Will determine if an approver has a delegation role
283 * Parameters :
284 * input parameters
285 * @param p_subscription_id
286 * description: The subscription_id of an enrollment
287 * required : Y
288 * validation : Must be a valid subscription_id
289 * p_user_id:
290 * description: The user_id of an approver
291 * required : Y
292 * validation : Must be a valid user_id
293 * output parameters
294 * x_result: The value indicating whether an approver has a delegation role or not
295 *
296 * Note:
297 * This API will return true, if there is no delegataion role defined for
298 * an enrollment.
299 */
300
301 procedure has_delegation_role (
302 p_subscription_id in number,
303 p_user_id in number,
304 x_result out NOCOPY boolean
305 ) IS
306 l_procedure_name CONSTANT varchar2(30) := 'has_delegation_role';
307 l_delegation_role_id number;
308 BEGIN
309
310 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
311 p_message => l_procedure_name
312 );
313
314 if( JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME) ) then
315 /* Bug #3468334 changed -- */
316 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
317 p_message => 'p_subscription_id:' || p_subscription_id || '+' || 'p_user_id:' || p_user_id
318 );
319 end if;
320
321 IF NOT JTF_UM_UTIL_PVT.VALIDATE_USER_ID(p_user_id) THEN
322
323 JTF_DEBUG_PUB.LOG_EXCEPTION( p_module => MODULE_NAME,
324 p_message => JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('user_id')
325 );
326
327 RAISE_APPLICATION_ERROR(-20000, JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('user_id'));
328
329 END IF;
330
331 IF NOT JTF_UM_UTIL_PVT.VALIDATE_SUBSCRIPTION_ID(p_subscription_id) THEN
332
333 JTF_DEBUG_PUB.LOG_EXCEPTION( p_module => MODULE_NAME,
334 p_message => JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('subscription_id')
335 );
336
337 RAISE_APPLICATION_ERROR(-20000, JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('subscription_id'));
338
339
340 END IF;
341
342 JTF_UM_SUBSCRIPTIONS_PKG.get_delegation_role(
343 p_subscription_id => p_subscription_id,
344 x_delegation_role => l_delegation_role_id
345 );
346
347 IF l_delegation_role_id IS NULL THEN
348 x_result := TRUE;
349 ELSE
350
351 x_result := JTF_UM_UTIL_PVT.check_role(
352 p_user_id => p_user_id,
353 p_auth_principal_id => l_delegation_role_id
354 );
355 END IF;
356
357 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
358 p_message => l_procedure_name
359 );
360
361 END has_delegation_role;
362
363
364 /**
365 * Procedure : get_checkbox_status
366 * Type : Private
367 * Pre_reqs : None
368 * Description : Will determine the status of the checkbox for
369 * granting delegation role
370 * Parameters :
371 * input parameters
372 * @param p_subscription_id
373 * description: The subscription_id of an enrollment
374 * required : Y
375 * validation : Must be a valid subscription_id
376 * @param p_grant_delegation_flag
377 * description: The value of grant_delegation_flag
378 * required : Y
379 * validation : Must be true or false
380 * @param p_user_id:
381 * description: The user_id of a logged in user
382 * required : Y
383 * validation : Must be a valid user_id
384 * output parameters
385 * x_result: Following int values
386 * 1 - CHECKED_UPDATE
387 * 2 - NOT_CHECKED_UPDATE
388 * 3 - CHECKED_NO_UPDATE
389 * 4 - NOT_CHECKED_NO_UPDATE
390 * Note : This is a package private procedure
391 *
392 */
393
394 procedure get_checkbox_status (
395 p_subscription_id in number,
396 p_grant_delegation_flag in boolean,
397 p_user_id in number,
398 p_usertype_id in number,
399 p_ignore_del_flag in boolean := false,
400 p_enrl_owner_user_id in number := FND_API.G_MISS_NUM,
401 x_result out NOCOPY number
402 ) IS
403
404 l_procedure_name CONSTANT varchar2(30) := 'get_checkbox_status';
405 l_delegation_role_id JTF_UM_SUBSCRIPTIONS_B.AUTH_DELEGATION_ROLE_ID%TYPE;
406 l_has_user_enrollment number;
407 query_user_has_roles number :=0;
408 l_activation_mode JTF_UM_USERTYPE_SUBSCRIP.SUBSCRIPTION_FLAG%TYPE;
409 l_user_has_role boolean := false;
410
411 -- This query will find out the active user type to subscription mapping
412 -- If no active mapping exists, then it will pick up the latest record
413 -- which has been end dated
414
415 CURSOR FIND_ACTIVATION_MODE IS SELECT SUBSCRIPTION_FLAG FROM JTF_UM_USERTYPE_SUBSCRIP
416 WHERE SUBSCRIPTION_ID = p_subscription_id
417 AND USERTYPE_ID = p_usertype_id
418 ORDER BY EFFECTIVE_END_DATE DESC;
419
420 BEGIN
421
422 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
423 p_message => l_procedure_name
424 );
425
426
427 if ( JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME) ) then
428 /* Bug #3468334 changed */
429 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
430 p_message => 'p_subscription_id:' || p_subscription_id || '+' || 'p_grant_delegation_flag:' || JTF_DBSTRING_UTILS.getBooleanString(p_grant_delegation_flag) || '+' || 'p_user_id:' || p_user_id
431 );
432 end if;
433
434 OPEN FIND_ACTIVATION_MODE;
435 FETCH FIND_ACTIVATION_MODE INTO l_activation_mode;
436 CLOSE FIND_ACTIVATION_MODE;
437
438 IF l_activation_mode IS NULL THEN
439 JTF_DEBUG_PUB.LOG_EXCEPTION( p_module => MODULE_NAME,
440 p_message => JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('JTA_UM_UT_ENROLL_NO_ASGN')
441 );
442
443 RAISE_APPLICATION_ERROR(-20000, JTF_DEBUG_PUB.GET_INVALID_PARAM_MSG('JTA_UM_UT_ENROLL_NO_ASGN'));
444 END IF;
445
446
447 -- Check if an enrollment is IMPLICIT or EXPLICIT
448 -- In this case, the status of the checkbox will be grayed and unchecked
449
450 IF l_activation_mode = 'IMPLICIT' OR l_activation_mode = 'EXPLICIT' THEN
451
452 x_result := NOT_CHECKED_NO_UPDATE;
453
454 ELSE -- The enrollment is either DELEGATION or DELEGATION_SELFSERVICE.
455
456
457 -- Check if a user has all the roles for this enrollment
458
459 has_admin_enrollment (
460 p_subscription_id => p_subscription_id,
461 p_user_id => p_user_id,
462 x_has_enrollment => l_has_user_enrollment
463 );
464
465 --check if queried user has all the roles
466
467 if p_enrl_owner_user_id <> FND_API.G_MISS_NUM then
468
469 has_admin_enrollment (
470 p_subscription_id => p_subscription_id,
471 p_user_id => p_enrl_owner_user_id,
472 x_has_enrollment => query_user_has_roles
473 );
474
475 end if;
476
477
478
479 -- Check if an enrollment has a delegation role
480
481 JTF_UM_SUBSCRIPTIONS_PKG.get_delegation_role(
482 p_subscription_id => p_subscription_id,
483 x_delegation_role => l_delegation_role_id
484 );
485
486
487
488 IF l_has_user_enrollment = 1 THEN -- User does have all the roles
489
490 IF l_delegation_role_id IS NULL THEN
491
492 -- The enrollment does not have a delegation role
493
494 --check if queried user has all the roles
495 IF query_user_has_roles = 1 THEN
496
497 x_result := CHECKED_NO_UPDATE;
498
499 ELSE
500
501 x_result := NOT_CHECKED_NO_UPDATE;
502
503 END IF;
504
505
506
507 ELSE
508
509 -- The enrollment does have a delegation role
510
511 -- Check if queried user has this delegation role
512
513 IF p_ignore_del_flag THEN
514
515 l_user_has_role := false;
516
517
518 IF JTF_UM_UTIL_PVT.check_role(
519 p_user_id => p_enrl_owner_user_id,
520 p_auth_principal_id => l_delegation_role_id
521 ) THEN
522
523
524
525 l_user_has_role := true;
526
527
528 END IF;
529
530 END IF;
531
532 -- Check if a current logged in user has this delegation role
533
534 IF JTF_UM_UTIL_PVT.check_role(
535 p_user_id => p_user_id,
536 p_auth_principal_id => l_delegation_role_id
537 ) THEN
538
539 -- The user does have the delegation role
540
541 IF p_ignore_del_flag THEN
542
543 IF l_user_has_role THEN
544
545 --check if queried user has all the roles
546 IF query_user_has_roles = 1 THEN
547
548 x_result := CHECKED_UPDATE;
549
550 ELSE
551
552 x_result := NOT_CHECKED_NO_UPDATE;
553
554 END IF;
555
556 ELSE
557
558 --check if queried user has all the roles
559 IF query_user_has_roles = 1 THEN
560
561 x_result := NOT_CHECKED_UPDATE;
562
563 ELSE
564
565 x_result := NOT_CHECKED_NO_UPDATE;
566
567 END IF;
568
569 END IF;
570
571 ELSE
572
573 IF p_grant_delegation_flag THEN
574
575 -- The grant delegation flag is set
576
577 x_result := CHECKED_UPDATE;
578
579 ELSE
580
581 -- The grant delegation flag is not set
582
583 x_result := NOT_CHECKED_UPDATE;
584
585 END IF;
586
587 END IF;
588
589 ELSE
590 -- A user does not have the delegation role
591
592
593 IF p_ignore_del_flag THEN
594
595 IF l_user_has_role THEN
596
597 --check if queried user has all the roles
598 IF query_user_has_roles = 1 THEN
599
600 x_result := CHECKED_NO_UPDATE;
601
602 ELSE
603
604 x_result := NOT_CHECKED_NO_UPDATE;
605
606 END IF;
607
608
609 ELSE
610
611 x_result := NOT_CHECKED_NO_UPDATE;
612
613 END IF;
614
615 ELSE
616
617 IF p_grant_delegation_flag THEN
618
619 -- The grant delegation flag is set
620
621 x_result := CHECKED_NO_UPDATE;
622
623 ELSE
624
625 -- The grant delegation flag is not set
626
627 x_result := NOT_CHECKED_NO_UPDATE;
628
629 END IF;
630
631 END IF;
632
633 END IF;
634
635 END IF;
636
637 ELSE -- User does not have all the roles
638
639
640 IF l_delegation_role_id IS NULL THEN
641
642 -- The enrollment does not have a delegation role
643
644 --check if queried user has all the roles
645 IF query_user_has_roles = 1 THEN
646
647 x_result := CHECKED_NO_UPDATE;
648
649 ELSE
650
651 x_result := NOT_CHECKED_NO_UPDATE;
652
653 END IF;
654
655
656
657 ELSE
658
659 IF p_ignore_del_flag THEN
660
661 l_user_has_role := false;
662
663 IF JTF_UM_UTIL_PVT.check_role(
664 p_user_id => p_enrl_owner_user_id,
665 p_auth_principal_id => l_delegation_role_id
666 ) THEN
667
668 l_user_has_role := true;
669
670
671 END IF;
672
673 END IF;
674
675 -- The enrollment does have a delegation role
676
677 IF p_ignore_del_flag THEN
678
679 IF l_user_has_role THEN
680
681 --check if queried user has all the roles
682 IF query_user_has_roles = 1 THEN
683
684 x_result := CHECKED_NO_UPDATE;
685
686 ELSE
687
688 x_result := NOT_CHECKED_NO_UPDATE;
689
690 END IF;
691
692
693 ELSE
694
695 x_result := NOT_CHECKED_NO_UPDATE;
696
697 END IF;
698
699 ELSE
700
701 IF p_grant_delegation_flag THEN
702
703 -- The grant delegation flag is set
704
705 x_result := CHECKED_NO_UPDATE;
706
707 ELSE
708
709 -- The grant delegation flag is not set
710
711 x_result := NOT_CHECKED_NO_UPDATE;
712
713 END IF;
714
715 END IF;
716
717 END IF;
718
719 END IF;
720
721 END IF;
722
723 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
724 p_message => l_procedure_name
725 );
726
727 END get_checkbox_status;
728
729
730 /**
731 * Procedure : get_checkbox_status
732 * Type : Private
733 * Pre_reqs : None
734 * Description : Will determine the status of the checkbox for granting delegation role
735 * Parameters :
736 * input parameters
737 * @param p_reg_id
738 * description: The usertype_reg_id or subscription_reg_id
739 * required : Y
740 * validation : Must be a usertype_reg_id or subscription_reg_id
741 * p_user_id:
742 * description: The user_id of a logged in user
743 * required : Y
744 * validation : Must be a valid user_id
745 * output parameters
746 * x_result: Following int values
747 * 0 - NO_CHECKBOX
748 * 1 - CHECKED_UPDATE
749 * 2 - NOT_CHECKED_UPDATE
750 * 3 - CHECKED_NO_UPDATE
751 * 4 - NOT_CHECKED_NO_UPDATE
752 *
753 *
754 */
755
756 procedure get_checkbox_status (
757 p_reg_id in number,
758 p_user_id in number,
759 x_result out NOCOPY number
760 ) IS
761
762 BEGIN
763
764 get_checkbox_status (
765 p_reg_id => p_reg_id,
766 p_user_id => p_user_id,
767 p_ignore_del_flag => false,
768 x_result => x_result
769 );
770
771 END get_checkbox_status;
772 /**
773 * Procedure : get_checkbox_status
774 * Type : Private
775 * Pre_reqs : None
776 * Description : Will determine the status of the checkbox for granting delegation role
777 * Parameters :
778 * input parameters
779 * @param p_reg_id
780 * description: The usertype_reg_id or subscription_reg_id
781 * required : Y
782 * validation : Must be a usertype_reg_id or subscription_reg_id
783 * p_user_id:
784 * description: The user_id of a logged in user
785 * required : Y
786 * validation : Must be a valid user_id
787 * p_ignore_del_flag:
788 * description: If set to yes, it will ignore the value of the grant delegation flag
789 * output parameters
790 * x_result: Following int values
791 * 0 - NO_CHECKBOX
792 * 1 - CHECKED_UPDATE
793 * 2 - NOT_CHECKED_UPDATE
794 * 3 - CHECKED_NO_UPDATE
795 * 4 - NOT_CHECKED_NO_UPDATE
796 *
797 *
798 */
799
800 procedure get_checkbox_status (
801 p_reg_id in number,
802 p_user_id in number,
803 p_ignore_del_flag in boolean,
804 p_enrl_owner_user_id in number := FND_API.G_MISS_NUM,
805 x_result out NOCOPY number
806 ) IS
807
808 l_procedure_name CONSTANT varchar2(30) := 'get_checkbox_status';
809
810 CURSOR FIND_DELEGATION_FLAG IS SELECT GRANT_DELEGATION_FLAG,SUBSCRIPTION_ID
811 FROM JTF_UM_SUBSCRIPTION_REG
812 WHERE SUBSCRIPTION_REG_ID = p_reg_id;
813
814 CURSOR FIND_USERTYPE_ID IS SELECT UTREG.USERTYPE_ID
815 FROM JTF_UM_USERTYPE_REG UTREG, JTF_UM_SUBSCRIPTION_REG SUBREG
816 WHERE SUBREG.USER_ID = UTREG.USER_ID
817 AND SUBREG.SUBSCRIPTION_REG_ID = p_reg_id
818 and nvl(UTREG.effective_end_date,sysdate+1) > sysdate;
819
820 l_grant_delegation_flag JTF_UM_SUBSCRIPTION_REG.GRANT_DELEGATION_FLAG%TYPE;
821 l_subscription_id JTF_UM_SUBSCRIPTION_REG.SUBSCRIPTION_ID%TYPE;
822 l_delegation_role_id JTF_UM_SUBSCRIPTIONS_B.AUTH_DELEGATION_ROLE_ID%TYPE;
823 l_flag_boolean boolean := false;
824 l_usertype_id number;
825
826 BEGIN
827
828 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
829 p_message => l_procedure_name
830 );
831
832 if ( JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME) ) then
833 /* Bug #3468334 changed */
834 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
835 p_message => 'p_reg_id:' || p_reg_id || '+' || 'p_user_id:' || p_user_id
836 );
837 end if;
838 OPEN FIND_DELEGATION_FLAG;
839 FETCH FIND_DELEGATION_FLAG INTO l_grant_delegation_flag,l_subscription_id;
840
841 IF FIND_DELEGATION_FLAG%NOTFOUND THEN
842
843 x_result := NO_CHECKBOX;
844
845 ELSE
846
847 IF l_grant_delegation_flag = 'Y' THEN
848
849 l_flag_boolean := true;
850
851 END IF;
852
853 OPEN FIND_USERTYPE_ID;
854 FETCH FIND_USERTYPE_ID INTO l_usertype_id;
855 CLOSE FIND_USERTYPE_ID;
856
857 get_checkbox_status (
858 p_subscription_id => l_subscription_id,
859 p_grant_delegation_flag => l_flag_boolean,
860 p_user_id => p_user_id,
861 p_usertype_id => l_usertype_id,
862 p_ignore_del_flag => p_ignore_del_flag,
863 p_enrl_owner_user_id => p_enrl_owner_user_id,
864 x_result => x_result
865 );
866
867 END IF;
868
869 CLOSE FIND_DELEGATION_FLAG;
870
871 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
872 p_message => l_procedure_name
873 );
874
875
876 END get_checkbox_status;
877
878
879 /**
880 * Procedure : get_checkbox_status_reg
881 * Type : Private
882 * Pre_reqs : None
883 * Description : Will determine the status of the checkbox for
884 * granting delegation role
885 * Parameters :
886 * input parameters
887 * @param p_subscription_id
888 * description: The subscription_id of an enrollment
889 * required : Y
890 * validation : Must be a valid subscription_id
891 * @param p_user_id:
892 * description: The user_id of a logged in user
893 * required : Y
894 * validation : Must be a valid user_id
895 * output parameters
896 * x_result: Following int values
897 * 1 - CHECKED_UPDATE
898 * 2 - NOT_CHECKED_UPDATE
899 * 3 - CHECKED_NO_UPDATE
900 * 4 - NOT_CHECKED_NO_UPDATE
901 *
902 *
903 */
904
905 procedure get_checkbox_status_reg (
906 p_subscription_id in number,
907 p_user_id in number,
908 p_usertype_id in number,
909 x_result out NOCOPY number
910 ) IS
911
912 l_delegation_role_id JTF_UM_SUBSCRIPTIONS_B.AUTH_DELEGATION_ROLE_ID%TYPE;
913 l_procedure_name CONSTANT varchar2(30) := 'get_checkbox_status_reg';
914
915 BEGIN
916
917 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
918 p_message => l_procedure_name
919 );
920
921
922
923 get_checkbox_status (
924 p_subscription_id => p_subscription_id,
925 p_grant_delegation_flag => false,
926 p_user_id => p_user_id,
927 p_usertype_id => p_usertype_id,
928 x_result => x_result
929 );
930
931 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
932 p_message => l_procedure_name
933 );
934
935 END get_checkbox_status_reg;
936
937 /**
938 * Procedure : get_enrollment_avail
939 * Type : Private
940 * Pre_reqs : None
941 * Description : Will determine if an admin has this enrollment
942 * or not and it will also determine the
943 * checkbox status
944 * granting delegation role
945 * Parameters :
946 * input parameters
947 * @param p_subscription_id
948 * description: The subscription_id of an enrollment
949 * required : Y
950 * validation : Must be a valid subscription_id
951 * @param p_user_id:
952 * description: The user_id of a logged in user
953 * required : Y
954 * validation : Must be a valid user_id
955 * output parameters
956 * x_checkbox_code: Following int values
957 * 1 - CHECKED_UPDATE
958 * 2 - NOT_CHECKED_UPDATE
959 * 3 - CHECKED_NO_UPDATE
960 * 4 - NOT_CHECKED_NO_UPDATE
961 * x_can_assign
962 * 0 - if admin cannot assign
963 * 1 - if admin can assign
964 *
965 */
966
967 procedure get_enrollment_avail (
968 p_subscription_id in number,
969 p_user_id in number,
970 p_usertype_id in number,
971 x_checkbox_code out NOCOPY number,
972 x_can_assign out NOCOPY number
973 ) IS
974
975 l_procedure_name CONSTANT varchar2(30) := 'get_enrollment_avail';
976
977 BEGIN
978
979
980 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
981 p_message => l_procedure_name
982 );
983
984
985 x_checkbox_code := 0;
986
987 can_delegate_int(
988 p_subscription_id => p_subscription_id,
989 p_user_id => p_user_id,
990 x_result => x_can_assign
991 );
992
993 get_checkbox_status_reg (
994 p_subscription_id => p_subscription_id,
995 p_user_id => p_user_id,
996 p_usertype_id => p_usertype_id,
997 x_result => x_checkbox_code
998 );
999
1000 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1001 p_message => l_procedure_name
1002 );
1003
1004
1005 END get_enrollment_avail;
1006
1007
1008 /**
1009 * Function : is_approval_required
1010 * Type : Private
1011 * Pre_reqs : None
1012 * Description : Will determine if an approval is required
1013 * for an enrollment or not
1014 * Parameters :
1015 * input parameters
1016 * @param p_subscription_id
1017 * description: The subscription_id of an enrollment
1018 * required : Y
1019 * validation : Must be a valid subscription_id
1020 * @param p_approval_id:
1021 * description: The p_approval_id of an enrollment
1022 * required : Y
1023 * validation : Must be a valid approval_id
1024 * @param p_activation_mode:
1025 * description: The p_activation_mode of an enrollment
1026 * required : Y
1027 * validation : Must be a valid activation mode
1028 * @param p_is_admin:
1029 * description: To determine, if a user is an admin or not
1030 * required : Y
1031 * validation : Must be 0 or 1
1032 * @return
1033 * 0 - if approval is not required
1034 * 1 - if approval is required
1035 *
1036 */
1037
1038 function is_approval_required(
1039 p_subscription_id in number,
1040 p_approval_id in number,
1041 p_activation_mode in varchar2,
1042 p_is_admin in number,
1043 p_can_assign in number
1044 ) return number is
1045
1046 l_procedure_name CONSTANT varchar2(30) := 'is_approval_required';
1047 l_approval_required boolean;
1048
1049 BEGIN
1050
1051 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
1052 p_message => l_procedure_name
1053 );
1054
1055 if ( JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME) ) then
1056 /* Bug #3468334 changed -- */
1057 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
1058 p_message => 'p_subscription_id:'|| p_subscription_id || '+' || 'p_approval_id:' || p_approval_id
1059 || '+' || 'p_activation_mode:' || p_activation_mode || '+' || 'p_is_admin:' || p_is_admin || '+' || 'p_can_assign:' || p_can_assign
1060 );
1061 end if;
1062
1063 IF p_approval_id IS NULL THEN
1064
1065 -- No approval is required, if approval id is null
1066
1067 l_approval_required := false;
1068
1069 ELSE
1070
1071 IF p_is_admin = 0 THEN
1072
1073 -- If it is not an admin then apprpoval is required
1074
1075 l_approval_required := true;
1076
1077 ELSE
1078
1079 IF p_activation_mode = 'IMPLICIT' OR p_activation_mode = 'EXPLICIT' THEN
1080
1081 -- If activation mode is implicit or explicit then approval
1082 -- is required. These are old activation modes
1083
1084 l_approval_required := true;
1085
1086 ELSIF p_can_assign = 1 THEN
1087
1088 -- No approval is required, if an admin can assign
1089
1090 l_approval_required := false;
1091
1092 ELSE
1093
1094 -- Approval is required, if an admin cannot assign
1095
1096 l_approval_required := true;
1097
1098 END IF;
1099
1100 END IF;
1101
1102 END IF;
1103
1104 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
1105 p_message => l_procedure_name
1106 );
1107
1108 IF l_approval_required THEN
1109 return 1;
1110 ELSE
1111 return 0;
1112 END IF;
1113
1114 END is_approval_required;
1115
1116
1117 end JTF_UM_WF_DELEGATION_PVT;
[Home] [Help]