[Home] [Help]
PACKAGE BODY: APPS.JTF_UM_SUBSCRIPTION_INFO
Source
1 PACKAGE BODY JTF_UM_SUBSCRIPTION_INFO as
2 /*$Header: JTFVSBIB.pls 120.1 2005/07/02 02:14:32 appldev ship $*/
3
4 MODULE_NAME CONSTANT VARCHAR2(50) := 'JTF.UM.PLSQL.JTF_UM_SUBSCRIPTION_INFO';
5 l_is_debug_parameter_on boolean := JTF_DEBUG_PUB.IS_LOG_PARAMETERS_ON(MODULE_NAME);
6
7 function is_user_enrolled(p_subscription_id number,
8 p_user_id number) return NUMBER IS
9
10
11 CURSOR USER_ENROLLMENT IS SELECT SUBSCRIPTION_REG_ID
12 FROM JTF_UM_SUBSCRIPTION_REG
13 WHERE SUBSCRIPTION_ID = p_subscription_id
14 AND USER_ID = p_user_id
15 AND NVL(EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
16 AND STATUS_CODE IN ('APPROVED', 'PENDING');
17
18 l_return_value NUMBER;
19 l_dummy NUMBER;
20
21 BEGIN
22
23 l_return_value := 1;
24
25 OPEN USER_ENROLLMENT;
26 FETCH USER_ENROLLMENT INTO l_dummy;
27
28 IF USER_ENROLLMENT%NOTFOUND THEN
29
30 l_return_value := 0;
31
32 END IF;
33
34 CLOSE USER_ENROLLMENT;
35
36 return l_return_value;
37
38 END is_user_enrolled;
39
40
41
42 /**
43 * Procedure : GET_USERTYPE_SUB_INFO
44 * Type : Private
45 * Pre_reqs : None
46 * Description : Returns the enrollment information for a user type
47 * Parameters :
48 * input parameters
49 * @param p_usertype_id
50 * description: The user type id
51 * required : Y
52 * validation : Must be a valid user type id
53 * @param p_user_id
54 * description: The user id of a logged in user
55 * required : Y
56 * validation : Must be a valid user id
57 * @param p_is_admin
58 * description: To know, if logged in user is an admin
59 * required : Y
60 * validation : Must be 0 or 1
61 * output parameters
62 * x_result: SUBSCRIPTION_INFO_TABLE
63 */
64 procedure GET_USERTYPE_SUB_INFO(
65 p_usertype_id in number,
66 p_user_id in number,
67 p_is_admin in number,
68 x_result out NOCOPY SUBSCRIPTION_INFO_TABLE
69 ) IS
70
71 l_procedure_name CONSTANT varchar2(30) := 'GET_USERTYPE_SUB_INFO';
72
73 CURSOR FIND_SUB_INFO IS SELECT A.SUBSCRIPTION_FLAG, A.SUBSCRIPTION_ID, A.SUBSCRIPTION_DISPLAY_ORDER, B.SUBSCRIPTION_KEY, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.AUTH_DELEGATION_ROLE_ID, B.APPROVAL_ID
74 FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B
75 WHERE A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
76 AND A.USERTYPE_ID = p_usertype_id
77 AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
78 AND B.ENABLED_FLAG = 'Y'
79 AND A.EFFECTIVE_START_DATE < SYSDATE
80 ORDER BY A.SUBSCRIPTION_DISPLAY_ORDER, B.SUBSCRIPTION_NAME;
81
82 i NUMBER := 1;
83 show_enrollment boolean;
84 l_checkbox_code NUMBER;
85 l_can_assign NUMBER;
86
87 BEGIN
88
89 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
90 p_message => l_procedure_name
91 );
92
93 if l_is_debug_parameter_on then
94 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
95 p_message => 'p_usertype_id:' || p_usertype_id || '+' || 'p_user_id:' || p_user_id || '+' || 'p_is_admin:' || p_is_admin
96 );
97 end if;
98
99 FOR j in FIND_SUB_INFO LOOP
100
101 show_enrollment := false;
102 l_can_assign := 0;
103
104 JTF_UM_WF_DELEGATION_PVT.get_enrollment_avail(
105 p_subscription_id => j.SUBSCRIPTION_ID,
106 p_user_id => p_user_id,
107 p_usertype_id => p_usertype_id,
108 x_checkbox_code => l_checkbox_code,
109 x_can_assign => l_can_assign
110 );
111
112 IF j.SUBSCRIPTION_FLAG <> 'DELEGATION' THEN
113
114 -- Show all the enrollments except Delegation Only
115
116 show_enrollment := true;
117
118 ELSIF p_is_admin = 1 AND l_can_assign = 1 THEN
119
120 -- Don't show for self service user
121 -- show if admin can assign this to user
122
123 show_enrollment := true;
124
125 END IF;
126
127 IF show_enrollment THEN
128
129 x_result(i).NAME := j.SUBSCRIPTION_NAME;
130 x_result(i).KEY := j.SUBSCRIPTION_KEY;
131 x_result(i).DESCRIPTION := j.DESCRIPTION;
132 x_result(i).DISPLAY_ORDER := j.SUBSCRIPTION_DISPLAY_ORDER;
133 x_result(i).ACTIVATION_MODE := j.SUBSCRIPTION_FLAG;
134 x_result(i).DELEGATION_ROLE := j.AUTH_DELEGATION_ROLE_ID;
135 x_result(i).CHECKBOX_STATUS := l_checkbox_code;
136 x_result(i).SUBSCRIPTION_ID := j.SUBSCRIPTION_ID;
137
138 x_result(i).APPROVAL_REQUIRED := JTF_UM_WF_DELEGATION_PVT.is_approval_required(
139 p_subscription_id => j.SUBSCRIPTION_ID,
140 p_approval_id => j.APPROVAL_ID,
141 p_activation_mode => j.SUBSCRIPTION_FLAG,
142 p_is_admin => p_is_admin,
143 p_can_assign => l_can_assign
144 );
145
146 i := i + 1;
147
148 END IF;
149
150 END LOOP;
151
152
153 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
154 p_message => l_procedure_name
155 );
156
157 END GET_USERTYPE_SUB_INFO;
158
159
160 procedure GET_USER_ASSIGNED_SUB(
161 p_user_id in number,
162 p_usertype_id in number,
163 p_administrator in number,
164 p_logged_in_user_id in number,
165 p_sub_status in varchar2,
166 x_result out NOCOPY SUBSCRIPTION_INFO_TABLE
167 ) IS
168
169 l_procedure_name CONSTANT varchar2(30) := 'GET_USER_ASSIGNED_SUB';
170
171 CURSOR FIND_SUB_INFO IS
172
173 SELECT SUBSCRIPTION_STATUS, SUBSCRIPTION_FLAG, SUBSCRIPTION_ID, SUBSCRIPTION_DISPLAY_ORDER,
174 SUBSCRIPTION_KEY, SUBSCRIPTION_NAME, DESCRIPTION, AUTH_DELEGATION_ROLE_ID, REG_ID FROM
175 (
176 SELECT DECODE(SUBREG.STATUS_CODE,'APPROVED','CURRENT','PENDING','PENDING') SUBSCRIPTION_STATUS, A.SUBSCRIPTION_FLAG, A.SUBSCRIPTION_ID, A.SUBSCRIPTION_DISPLAY_ORDER,
177 B.SUBSCRIPTION_KEY, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.AUTH_DELEGATION_ROLE_ID, SUBREG.SUBSCRIPTION_REG_ID REG_ID
178 FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG
179 WHERE SUBREG.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
180 AND A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
181 AND A.USERTYPE_ID = p_usertype_id
182 AND SUBREG.USER_ID = p_user_id
183 AND SUBREG.STATUS_CODE = p_sub_status
184 AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
185 AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
186 AND SUBREG.EFFECTIVE_START_DATE < SYSDATE
187
188 UNION ALL
189
190 SELECT DECODE(SUBREG.STATUS_CODE,'APPROVED','CURRENT','PENDING','PENDING') SUBSCRIPTION_STATUS, A.SUBSCRIPTION_FLAG, A.SUBSCRIPTION_ID, A.SUBSCRIPTION_DISPLAY_ORDER,
191 B.SUBSCRIPTION_KEY, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.AUTH_DELEGATION_ROLE_ID, SUBREG.SUBSCRIPTION_REG_ID REG_ID
192
193 FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG
194 WHERE SUBREG.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
195 AND A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
196 AND A.USERTYPE_ID = p_usertype_id
197 AND SUBREG.USER_ID = p_user_id
198 AND SUBREG.STATUS_CODE = p_sub_status
199 AND A.EFFECTIVE_END_DATE IS NOT NULL
200 AND A.EFFECTIVE_END_DATE < SYSDATE
201 AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
202 AND SUBREG.EFFECTIVE_START_DATE < SYSDATE
203 AND NOT EXISTS (SELECT 'X'
204 FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG SUBREG
205 WHERE SUBREG.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
206 AND A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
207 AND A.USERTYPE_ID = p_usertype_id
208 AND SUBREG.USER_ID = p_user_id
209 AND SUBREG.STATUS_CODE = p_sub_status
210 AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
211 AND NVL(SUBREG.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
212 AND SUBREG.EFFECTIVE_START_DATE < SYSDATE)
213 AND A.EFFECTIVE_END_DATE IN(
214 SELECT MAX(EFFECTIVE_END_DATE) FROM JTF_UM_USERTYPE_SUBSCRIP USUB
215 WHERE USUB.USERTYPE_ID = p_usertype_id
216 AND USUB.SUBSCRIPTION_ID = A.SUBSCRIPTION_ID
217 )
218 ) ALL_ENROLLMENTS ORDER BY SUBSCRIPTION_NAME;
219
220 i NUMBER := 1;
221 l_checkbox_code NUMBER;
222 show_enrollment boolean;
223 l_ignore_del_flag boolean := false;
224
225 BEGIN
226
227 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
228 p_message => l_procedure_name
229 );
230
231 if l_is_debug_parameter_on then
232 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
233 p_message => 'p_user_id:' || p_user_id || '+' || 'p_usertype_id:' || p_usertype_id || '+' ||
234 'p_administrator:' || p_administrator || '+' || 'p_logged_in_user_id:' || p_logged_in_user_id || '+' || 'p_sub_status:' || p_sub_status
235 );
236 end if;
237
238
239 FOR j in FIND_SUB_INFO LOOP
240
241 show_enrollment := false;
242
243 IF p_administrator = 1 THEN
244
245 show_enrollment := true;
246
247 ELSIF j.SUBSCRIPTION_FLAG <> 'IMPLICIT' THEN
248
249 show_enrollment := true;
250
251 END IF;
252
253 IF show_enrollment THEN
254
255 IF j.SUBSCRIPTION_STATUS = 'CURRENT' THEN
256
257 l_ignore_del_flag := true;
258
259 END IF;
260
261 JTF_UM_WF_DELEGATION_PVT.get_checkbox_status(
262 p_reg_id => j.REG_ID,
263 p_user_id => p_logged_in_user_id,
264 p_ignore_del_flag => l_ignore_del_flag,
265 p_enrl_owner_user_id => p_user_id,
266 x_result => l_checkbox_code
267 );
268
269 x_result(i).NAME := j.SUBSCRIPTION_NAME;
270 x_result(i).KEY := j.SUBSCRIPTION_KEY;
271 x_result(i).DESCRIPTION := j.DESCRIPTION;
272 x_result(i).DISPLAY_ORDER := j.SUBSCRIPTION_DISPLAY_ORDER;
273 x_result(i).ACTIVATION_MODE := j.SUBSCRIPTION_FLAG;
274 x_result(i).DELEGATION_ROLE := j.AUTH_DELEGATION_ROLE_ID;
275 x_result(i).CHECKBOX_STATUS := l_checkbox_code;
276 x_result(i).SUBSCRIPTION_ID := j.SUBSCRIPTION_ID;
277 x_result(i).SUBSCRIPTION_STATUS := j.SUBSCRIPTION_STATUS;
278 x_result(i).SUBSCRIPTION_REG_ID := j.REG_ID;
279
280 i := i + 1;
281
282 END IF;
283
284 END LOOP;
285
286
287 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
288 p_message => l_procedure_name
289 );
290
291 END GET_USER_ASSIGNED_SUB;
292
293 procedure GET_USER_AVAIL_SUB(
294 p_user_id in number,
295 p_usertype_id in number,
296 p_is_admin in number,
297 p_administrator in number,
298 p_logged_in_user_id in number,
299 x_result out NOCOPY SUBSCRIPTION_INFO_TABLE
300 ) IS
301
302 l_procedure_name CONSTANT varchar2(30) := 'GET_USER_AVAIL_SUB';
303
304 CURSOR FIND_SUB_INFO IS
305 SELECT 'AVAILABLE' SUBSCRIPTION_STATUS, A.SUBSCRIPTION_FLAG, A.SUBSCRIPTION_ID, A.SUBSCRIPTION_DISPLAY_ORDER, B.SUBSCRIPTION_KEY, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.AUTH_DELEGATION_ROLE_ID, B.APPROVAL_ID, to_number(NULL) REG_ID
306 FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B
307 WHERE A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
308 AND A.USERTYPE_ID = p_usertype_id
309 AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
310 AND B.ENABLED_FLAG = 'Y'
311 AND A.EFFECTIVE_START_DATE < SYSDATE
312 AND NOT EXISTS (SELECT SUBSCRIPTION_REG_ID FROM JTF_UM_SUBSCRIPTION_REG REG WHERE
313 USER_ID = p_user_id
314 AND REG.SUBSCRIPTION_ID = A.SUBSCRIPTION_ID
315 AND REG.STATUS_CODE IN ('APPROVED', 'PENDING')
316 AND NVL(REG.EFFECTIVE_END_DATE, SYSDATE +1 ) > Sysdate
317 )
318 ORDER BY A.SUBSCRIPTION_DISPLAY_ORDER, B.SUBSCRIPTION_NAME;
319
320
321 i NUMBER := 1;
322 show_enrollment boolean;
323 l_checkbox_code NUMBER;
324 l_can_assign NUMBER;
325 l_approval_required NUMBER;
326
327 BEGIN
328
329 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
330 p_message => l_procedure_name
331 );
332
333 if l_is_debug_parameter_on then
334 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
335 p_message => 'p_user_id:' || p_user_id || '+' || 'p_is_admin:' || p_is_admin || '+' || 'p_administrator:'
336 || p_administrator || '+' || 'p_logged_in_user_id:' || p_logged_in_user_id
337 );
338 end if;
339 FOR j in FIND_SUB_INFO LOOP
340
341 show_enrollment := false;
342
343
344 JTF_UM_WF_DELEGATION_PVT.get_enrollment_avail(
345 p_subscription_id => j.SUBSCRIPTION_ID,
346 p_user_id => p_logged_in_user_id,
347 p_usertype_id => p_usertype_id,
348 x_checkbox_code => l_checkbox_code,
349 x_can_assign => l_can_assign
350 );
351
352 IF j.SUBSCRIPTION_FLAG <> 'DELEGATION' THEN
353
354 -- Show all the enrollments except Delegation Only
355
356 -- Implicit enrollments are to be shown only to the administrator
357
358 IF j.SUBSCRIPTION_FLAG = 'IMPLICIT' THEN
359
360 IF p_administrator = 1 THEN
361
362 show_enrollment := true;
363
364 END IF;
365
366 ELSE
367
368 show_enrollment := true;
369
370 END IF;
371
372 ELSIF p_is_admin = 1 AND l_can_assign = 1 THEN
373
374 -- Don't show for self service user
375 -- show if admin can assign this to user
376
377 show_enrollment := true;
378
379 END IF;
380
381 l_approval_required := JTF_UM_WF_DELEGATION_PVT.is_approval_required(
382 p_subscription_id => j.SUBSCRIPTION_ID,
383 p_approval_id => j.APPROVAL_ID,
384 p_activation_mode => j.SUBSCRIPTION_FLAG,
385 p_is_admin => p_is_admin,
386 p_can_assign => l_can_assign
387 );
388
389
390 IF show_enrollment THEN
391
392 x_result(i).NAME := j.SUBSCRIPTION_NAME;
393 x_result(i).KEY := j.SUBSCRIPTION_KEY;
394 x_result(i).DESCRIPTION := j.DESCRIPTION;
395 x_result(i).DISPLAY_ORDER := j.SUBSCRIPTION_DISPLAY_ORDER;
396 x_result(i).ACTIVATION_MODE := j.SUBSCRIPTION_FLAG;
397 x_result(i).DELEGATION_ROLE := j.AUTH_DELEGATION_ROLE_ID;
398 x_result(i).CHECKBOX_STATUS := l_checkbox_code;
399 x_result(i).SUBSCRIPTION_ID := j.SUBSCRIPTION_ID;
400 x_result(i).APPROVAL_REQUIRED := l_approval_required;
401 x_result(i).SUBSCRIPTION_STATUS := j.SUBSCRIPTION_STATUS;
402
403 i := i + 1;
404
405 END IF;
406
407 END LOOP;
408
409
410 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
411 p_message => l_procedure_name
412 );
413
414 END GET_USER_AVAIL_SUB;
415
416
417 /**
418 * Procedure : GET_USER_SUB_INFO
419 * Type : Private
420 * Pre_reqs : None
421 * Description : Returns the enrollment information for a user type
422 * Parameters :
423 * input parameters
424 * @param p_user_id
425 * description: The user id for which enrollments are queried
426 * required : Y
427 * validation : Must be a valid user id
428 * @param p_is_admin
429 * description: To know, if logged in user is an admin
430 * required : Y
431 * validation : Must be 0 or 1
432 * @param p_logged_in_user_id
433 * description: The user id of logged in user
434 * required : Y
435 * validation : Must be a valid user id
436 * @param p_sub_status
437 * description: The status of the enrollment assignment
438 * required : Y
439 * validation : Must be 'AVAILABLE', 'APPROVED' or 'PENDING'
440 * output parameters
441 * x_result: SUBSCRIPTION_INFO_TABLE
442 */
443
444 procedure GET_USER_SUB_INFO(
445 p_user_id in number,
446 p_is_admin in number,
447 p_logged_in_user_id in number,
448 p_administrator in number,
449 p_sub_status in varchar2,
450 x_result out NOCOPY SUBSCRIPTION_INFO_TABLE
451 ) IS
452
453 l_procedure_name CONSTANT varchar2(30) := 'GET_USER_SUB_INFO';
454
455
456 BEGIN
457
458 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
459 p_message => l_procedure_name
460 );
461
462 if l_is_debug_parameter_on then
463 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
464 p_message => 'p_user_id:' || p_user_id || '+' || 'p_is_admin:' || p_is_admin || '+' || 'p_sub_status:' || p_sub_status
465 );
466 end if;
467
468 IF p_sub_status = 'AVAILABLE' THEN
469
470 GET_USER_AVAIL_SUB(
471 p_user_id => p_user_id,
472 p_usertype_id => JTF_UM_UTIL_PVT.GET_USERTYPE_ID(p_user_id),
473 p_is_admin => p_is_admin,
474 p_administrator => p_administrator,
475 p_logged_in_user_id => p_logged_in_user_id,
476 x_result => x_result
477 );
478
479 ELSE
480
481 GET_USER_ASSIGNED_SUB(
482 p_user_id => p_user_id,
483 p_usertype_id => JTF_UM_UTIL_PVT.GET_USERTYPE_ID(p_user_id),
484 p_administrator => p_administrator,
485 p_logged_in_user_id => p_logged_in_user_id,
486 p_sub_status => p_sub_status,
487 x_result => x_result
488 );
489
490 END IF;
491
492 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
493 p_message => l_procedure_name
494 );
495
496 END GET_USER_SUB_INFO;
497
498 /**
499 * Procedure : GET_USER_SUB_INFO
500 * Type : Private
501 * Pre_reqs : None
502 * Description : Returns the enrollment information for a user type
503 * Parameters :
504 * input parameters
505 * @param p_user_id
506 * description: The user id
507 * required : Y
508 * validation : Must be a valid user id
509 * @param p_usertype_id
510 * description: The user type id
511 * required : Y
512 * validation : Must be a valid user type id
513 * @param p_sub_list
514 * description: The list of enrollments
515 * required : Y
516 * validation : Must be a valid list
517 * output parameters
518 * x_result: SUBSCRIPTION_INFO_TABLE
519 */
520 procedure GET_CONF_SUB_INFO(
521 p_user_id in number,
522 p_usertype_id in number,
523 p_is_admin in number,
524 p_admin_id in number,
525 p_administrator in number,
526 p_sub_list in SUBSCRIPTION_LIST,
527 x_result out NOCOPY SUBSCRIPTION_INFO_TABLE
528 ) IS
529
530 l_procedure_name CONSTANT varchar2(30) := 'GET_CONF_SUB_INFO';
531 l_subscription_id NUMBER;
532
533 CURSOR FIND_SUB_INFO IS
534 SELECT A.SUBSCRIPTION_FLAG, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.APPROVAL_ID, C.SUBSCRIPTION_REG_ID
535 FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_REG C
536 WHERE A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
537 AND A.USERTYPE_ID = p_usertype_id
538 AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
539 AND B.ENABLED_FLAG = 'Y'
540 AND A.EFFECTIVE_START_DATE < SYSDATE
541 AND C.USER_ID = p_user_id
542 AND C.SUBSCRIPTION_ID = l_subscription_id
543 AND C.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
544 AND NVL(C.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE;
545
546 i NUMBER := 1;
547 l_checkbox_code NUMBER;
548 l_can_assign NUMBER;
549 l_approval_required NUMBER;
550 sub_index NUMBER;
551 show_enrollment boolean;
552
553 BEGIN
554
555
556 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
557 p_message => l_procedure_name
558 );
559
560 if l_is_debug_parameter_on then
561 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
562 p_message => 'p_user_id:' || p_user_id || '+' || 'p_usertype_id:' || p_usertype_id
563 || '+' || 'p_is_admin:' || p_is_admin
564 );
565 end if;
566
567 -- Start reading the subscription list
568
569 sub_index := p_sub_list.first;
570
571 -- Parse through each subscription
572
573 WHILE sub_index <= p_sub_list.last LOOP
574
575 -- set the value so that cursor can correcty be opened
576
577 l_subscription_id := p_sub_list(sub_index).SUBSCRIPTION_ID;
578
579 -- Execute the cursor and populate the result table
580 FOR j in FIND_SUB_INFO LOOP
581
582 -- Do not show Automatic enrollments to users other than administartor
583
584 show_enrollment := false;
585
586 IF p_administrator = 1 THEN
587
588 show_enrollment := true;
589
590 ELSIF j.SUBSCRIPTION_FLAG <> 'IMPLICIT' THEN
591
592 show_enrollment := true;
593
594 END IF;
595
596 IF show_enrollment THEN
597
598 JTF_UM_WF_DELEGATION_PVT.get_enrollment_avail(
599 p_subscription_id => l_subscription_id,
600 p_user_id => p_admin_id,
601 p_usertype_id => p_usertype_id,
602 x_checkbox_code => l_checkbox_code,
603 x_can_assign => l_can_assign
604 );
605
606 l_approval_required := JTF_UM_WF_DELEGATION_PVT.is_approval_required(
607 p_subscription_id => l_subscription_id,
608 p_approval_id => j.APPROVAL_ID,
609 p_activation_mode => j.SUBSCRIPTION_FLAG,
610 p_is_admin => p_is_admin,
611 p_can_assign => l_can_assign
612 );
613
614
615 x_result(i).NAME := j.SUBSCRIPTION_NAME;
616 x_result(i).DESCRIPTION := j.DESCRIPTION;
617 x_result(i).APPROVAL_REQUIRED := l_approval_required;
618 x_result(i).SUBSCRIPTION_REG_ID := j.SUBSCRIPTION_REG_ID;
619
620 END IF;
621
622 END LOOP;
623
624 i := i + 1;
625 sub_index := p_sub_list.next(sub_index);
626
627 END LOOP;
628
629 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
630 p_message => l_procedure_name
631 );
632
633 END GET_CONF_SUB_INFO;
634
635
636 /**
637 * Procedure : GET_USERTYPE_SUB
638 * Type : Private
639 * Pre_reqs : None
640 * Description : Returns the enrollment information for a user type
641 * Parameters :
642 * input parameters
643 * @param p_usetype_id
644 * description: The user type id
645 * required : Y
646 * validation : Must be a valid user type id
647 * @param p_user_id
648 * description: The user id
649 * required : Y
650 * validation : Must be a valid user id
651 * @param p_sub_list
652 * description: The list of enrollments
653 * required : Y
654 * validation : Must be a valid list
655 * @param p_is_admin
656 * description: To know, if logged in user is an admin
657 * required : Y
658 * validation : Must be 0 or 1
659 * output parameters
660 * x_result: SUBSCRIPTION_INFO_TABLE
661 */
662 procedure GET_USERTYPE_SUB(
663 p_usertype_id in number,
664 p_user_id in number,
665 p_is_admin in number,
666 p_admin_id in number,
667 p_sub_list in SUBSCRIPTION_LIST,
668 x_result out NOCOPY SUBSCRIPTION_INFO_TABLE
669 ) IS
670
671 l_procedure_name CONSTANT varchar2(30) := 'GET_USERTYPE_SUB';
672 l_subscription_id NUMBER;
673
674 CURSOR FIND_SUB_INFO IS
675 SELECT A.SUBSCRIPTION_FLAG, B.SUBSCRIPTION_NAME, B.DESCRIPTION, B.APPROVAL_ID, TMPL.TEMPLATE_HANDLER, TMPL.PAGE_NAME
676 FROM JTF_UM_USERTYPE_SUBSCRIP A, JTF_UM_SUBSCRIPTIONS_VL B, JTF_UM_SUBSCRIPTION_TMPL SUBTMPL, JTF_UM_TEMPLATES_B TMPL
677 WHERE A.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID
678 AND A.USERTYPE_ID = p_usertype_id
679 AND NVL(A.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
680 AND NVL(SUBTMPL.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
681 AND NVL(TMPL.EFFECTIVE_END_DATE, SYSDATE+1) > SYSDATE
682 AND B.ENABLED_FLAG = 'Y'
683 AND A.EFFECTIVE_START_DATE < SYSDATE
684 AND B.SUBSCRIPTION_ID = SUBTMPL.SUBSCRIPTION_ID
685 AND SUBTMPL.TEMPLATE_ID = TMPL.TEMPLATE_ID
686 AND B.SUBSCRIPTION_ID = l_subscription_id
687 AND TMPL.TEMPLATE_TYPE_CODE = 'ENROLLMENT_TEMPLATE';
688
689 i NUMBER := 1;
690 l_checkbox_code NUMBER;
691 l_can_assign NUMBER;
692 l_approval_required NUMBER;
693 sub_index NUMBER;
694 l_is_user_enrolled NUMBER;
695
696 BEGIN
697
698
699 JTF_DEBUG_PUB.LOG_ENTERING_METHOD( p_module => MODULE_NAME,
700 p_message => l_procedure_name
701 );
702
703 if l_is_debug_parameter_on then
704 JTF_DEBUG_PUB.LOG_PARAMETERS( p_module => MODULE_NAME,
705 p_message => 'p_usertype_id:' || p_usertype_id || '+' || 'p_admin_id:' || p_admin_id
706 || '+' || 'p_is_admin:' || p_is_admin
707 );
708 end if;
709
710 -- Start reading the subscription list
711
712 sub_index := p_sub_list.first;
713
714 -- Parse through each subscription
715
716 WHILE sub_index <= p_sub_list.last LOOP
717
718 -- set the value so that cursor can correcty be opened
719
720 l_subscription_id := p_sub_list(sub_index).SUBSCRIPTION_ID;
721
722 -- Execute the cursor and populate the result table
723 FOR j in FIND_SUB_INFO LOOP
724
725 -- Do not show Automatic enrollments to users other than administartor
726
727
728 JTF_UM_WF_DELEGATION_PVT.get_enrollment_avail(
729 p_subscription_id => l_subscription_id,
730 p_user_id => p_admin_id,
731 p_usertype_id => p_usertype_id,
732 x_checkbox_code => l_checkbox_code,
733 x_can_assign => l_can_assign
734 );
735
736 l_approval_required := JTF_UM_WF_DELEGATION_PVT.is_approval_required(
737 p_subscription_id => l_subscription_id,
738 p_approval_id => j.APPROVAL_ID,
739 p_activation_mode => j.SUBSCRIPTION_FLAG,
740 p_is_admin => p_is_admin,
741 p_can_assign => l_can_assign
742 );
743
744 l_is_user_enrolled := is_user_enrolled(
745 p_subscription_id => l_subscription_id,
746 p_user_id => p_user_id
747 );
748
749
750 x_result(i).NAME := j.SUBSCRIPTION_NAME;
751 x_result(i).DESCRIPTION := j.DESCRIPTION;
752 x_result(i).APPROVAL_REQUIRED := l_approval_required;
753 x_result(i).APPROVAL_ID := j.APPROVAL_ID;
754 x_result(i).TEMPLATE_HANDLER := j.TEMPLATE_HANDLER;
755 x_result(i).PAGE_NAME := j.PAGE_NAME;
756 x_result(i).IS_USER_ENROLLED := l_is_user_enrolled;
757 x_result(i).ACTIVATION_MODE := j.SUBSCRIPTION_FLAG;
758 x_result(i).SUBSCRIPTION_ID := l_subscription_id;
759
760
761 END LOOP;
762
763 i := i + 1;
764 sub_index := p_sub_list.next(sub_index);
765
766 END LOOP;
767
768 JTF_DEBUG_PUB.LOG_EXITING_METHOD( p_module => MODULE_NAME,
769 p_message => l_procedure_name
770 );
771
772 END GET_USERTYPE_SUB;
773
774 end JTF_UM_SUBSCRIPTION_INFO;