1 PACKAGE BODY OTA_ADMIN_ACCESS_UTIL as
2 /* $Header: otadminacc.pkb 120.6 2011/05/03 09:39:30 smahanka noship $ */
3
7 --learning path=CLP/learning_path_id,learning certification=CER/certification_id
4 --This function is used to determine the primary category of any catalog object
5 --object type/object_id:
6 --course=H/activity_version_id,offering=O/offering_id,class=CL/event_id
8 --category forum=RFOR/forum_id,category chat=SCHT/chat_id
9
10 function get_primary_category_id(p_object_type in varchar,
11 p_object_id in number) return NUMBER is
12
13 Cursor get_id_for_course(p_activity_version_id ota_activity_versions.activity_version_id%type) is
14 Select category_usage_id
15 From ota_act_cat_inclusions
16 Where activity_version_id=p_activity_version_id
17 And primary_flag='Y';
18
19 Cursor get_id_for_cert is
20 Select category_usage_id
21 From ota_cert_cat_inclusions
22 Where certification_id=p_object_id
23 And primary_flag='Y';
24
25 Cursor get_id_for_lp is
26 Select category_usage_id
27 From ota_lp_cat_inclusions
28 Where learning_path_id=p_object_id
29 And primary_flag='Y';
30
31 Cursor get_id_for_forum is
32 Select object_id
33 From ota_frm_obj_inclusions
34 Where forum_id=p_object_id
35 And primary_flag='Y';
36
37 Cursor get_id_for_chat is
38 Select object_id
39 From ota_chat_obj_inclusions
40 Where chat_id=p_object_id
41 And primary_flag='Y';
42
43 Cursor get_course_id_for_class is
44 Select activity_version_id
45 From ota_events
46 where event_id=p_object_id;
47
48 Cursor get_course_id_for_offering is
49 Select activity_version_id
50 From ota_offerings
51 where offering_id=p_object_id;
52
53 l_act_ver_id number:=null;
54 l_category_id number:=null;
55 Begin
56
57 if p_object_type='H' then
58 OPEN get_id_for_course(p_object_id);
59 Fetch get_id_for_course into l_category_id;
60 Close get_id_for_course ;
61
62 elsif p_object_type='SCHT' then
63 OPEN get_id_for_chat;
64 Fetch get_id_for_chat into l_category_id;
65 Close get_id_for_chat;
66
67 elsif p_object_type='RFOR' then
68 OPEN get_id_for_forum;
69 Fetch get_id_for_forum into l_category_id;
70 Close get_id_for_forum;
71
72 elsif p_object_type='CER' then
73 OPEN get_id_for_cert;
74 Fetch get_id_for_cert into l_category_id;
75 Close get_id_for_cert;
76
77 elsif p_object_type='CLP' then
78 OPEN get_id_for_lp;
79 Fetch get_id_for_lp into l_category_id;
80 Close get_id_for_lp;
81
82 elsif p_object_type='O' then
83 OPEN get_course_id_for_offering;
84 Fetch get_course_id_for_offering into l_act_ver_id;
85 Close get_course_id_for_offering;
86 OPEN get_id_for_course(l_act_ver_id);
87 Fetch get_id_for_course into l_category_id;
88 Close get_id_for_course ;
89
90 elsif p_object_type='CL' then
91 OPEN get_course_id_for_class;
92 Fetch get_course_id_for_class into l_act_ver_id;
93 Close get_course_id_for_class;
94 OPEN get_id_for_course(l_act_ver_id);
95 Fetch get_id_for_course into l_category_id;
96 Close get_id_for_course ;
97
98 end if;
99
100 return l_category_id;
101
102 End get_primary_category_id;
103
104
105 --This function is used to determine the admin group of any catalog object
106 --The admin group is determined based on primary category of the object
107 --object type/object_id:
108 --course=H/activity_version_id,offering=O/offering_id,class=CL/event_id
109 --learning path=CLP/learning_path_id,learning certification=CER/certification_id
110 --category forum=RFOR/forum_id,category chat=SCHT/chat_id
111 function get_catalog_obj_admin_grp (p_object_type in varchar,
112 p_object_id in number) return Number is
113
114
115 l_category_id number;
116 l_admin_grp_id number;
117
118 Begin
119 --allow access by default??
120 if p_object_type is null or p_object_id is null then
121 return null;
122 end if;
123
124
125 if p_object_type = 'C' then
126 l_category_id := p_object_id;
127 else
128 l_category_id:=get_primary_category_id(p_object_type, p_object_id);
129 end if;
130
131 OPEN check_is_category_secured(l_category_id);
132 Fetch check_is_category_secured into l_admin_grp_id;
133 Close check_is_category_secured;
134
135 return l_admin_grp_id;
136
137 end get_catalog_obj_admin_grp;
138
139
140 --This function is used to determine the admin group of any catalog object
141 --The admin group is determined based on primary category of the object
142 --object type/object_id:
143 --folder=F/folder_id
144 --learning object=LO/learning_object_id
145
146 function get_content_obj_admin_grp(p_object_type in varchar,
147 p_object_id in number) return Number is
148
149 Cursor get_folder_adminGrp IS
150 Select user_group_id
151 from ota_lo_folders
152 where folder_id=p_object_id;
153
154 Cursor get_lo_adminGrp IS
155 select user_group_id
156 from
157 ota_lo_folders lof,
158 ota_learning_objects lo
159 where
160 lo.learning_object_id=p_object_id
161 and lo.folder_id=lof.folder_id;
162
163
164 l_admin_grp_id Number;
165
166 Begin
167 if p_object_type ='F' then
168 OPEN get_folder_adminGrp;
169 Fetch get_folder_adminGrp into l_admin_grp_id;
170 Close get_folder_adminGrp;
171 elsif p_object_type ='LO' then
172 OPEN get_lo_adminGrp;
173 Fetch get_lo_adminGrp into l_admin_grp_id;
174 Close get_lo_adminGrp;
175 end if;
176
177 return l_admin_grp_id;
178
179 end get_content_obj_admin_grp;
180
181
182
183
184 function get_admin_group_id(p_object_type in varchar,
185 p_object_id in number) return Number is
186
187
188 l_admin_grp_id number null;
189
190 Begin
191 --allow access by default??
192 if p_object_type is null or p_object_id is null then
193 return null;
194 end if;
195
196
197 if p_object_type = 'C' then
198 l_admin_grp_id := get_catalog_obj_admin_grp(p_object_type, p_object_id);
199 else
200 l_admin_grp_id := get_content_obj_admin_grp(p_object_type, p_object_id);
201 end if;
202
203 return l_admin_grp_id;
204
205 end get_admin_group_id;
206
207
208
209
210
211 --The function admin_can_access_object will be called from all VO's where objects are not
212 --used for a transaction i.e objects used only for view purpose
213 --Admin can access catalog objects only if one of the below is true
214 --1.the object is not secured
215 --2.if secured then,admin should belong to that admingroup
216
217 function admin_can_access_object(p_object_type in varchar,
218 p_object_id in number,
219 p_module_name IN VARCHAR2 default 'ADMIN') return varchar2 is
220 l_person_id number;
221 l_adminGrpId number;
222
223 begin
224
225 if p_module_name <> 'ADMIN' then
226 return 'Y';
227
228 else
229
230 l_person_id := fnd_global.employee_id();
231
232 if p_object_type IN ('F','LO') then
233 l_adminGrpId := get_content_obj_admin_grp(p_object_type,p_object_id);
234 else
235 l_adminGrpId :=get_catalog_obj_admin_grp(p_object_type,p_object_id);
236 end if;
237
238 if l_adminGrpId is null then
239 return 'Y';
240 else
241 return ota_learner_access_util.is_learner_in_user_group(l_person_id, l_adminGrpId, ota_general.get_business_group_id());
242 end if;
243
244 end if;
245
246 end admin_can_access_object;
247
248 --Function called to check whether a particular course can be added to lp
249 --check if the course is secured.If it is not secured,you can add it to any lp
250 --if lp is secured,the course should be secured with same admin group
251 Function lp_has_access_to_course(p_lp_id in NUMBER,
252 p_crs_id in NUMBER) return varchar2 is
253
254 l_crsAdminGrpId number:=null;
255 l_lpAdminGrpId number :=null;
256
257 begin
258
259
260 l_crsAdminGrpId := get_catalog_obj_admin_grp('H',p_crs_id);
261 l_lpAdminGrpId := get_catalog_obj_admin_grp('CLP',p_lp_id);
262
263 if l_crsAdminGrpId is null then
264 return 'Y';
265 end if;
266
267 if(l_crsAdminGrpId = l_lpAdminGrpId) then
268 return 'Y';
269 else
270 return 'N';
271 end if;
272
273 End lp_has_access_to_course;
274
275 --Function called to check whether a particular course can be added to lp
276 --check if the course is secured.If it is not secured,you can add it any cert
277 --if cert is secured,the course should be secured with same admin group
278 Function cert_has_access_to_course(p_cert_id in NUMBER,
279 p_crs_id in NUMBER) return varchar2 is
280
281 l_crsAdminGrpId number:=null;
282 l_certAdminGrpId number :=null;
283
284 begin
285
286
287 l_crsAdminGrpId := get_catalog_obj_admin_grp('H',p_crs_id);
288 l_certAdminGrpId := get_catalog_obj_admin_grp('CER',p_cert_id);
289
290 if l_crsAdminGrpId is null then
291 return 'Y';
292 end if;
293
294 if(l_crsAdminGrpId = l_certAdminGrpId) then
295 return 'Y';
296 else
297 return 'N';
298 end if;
299
300 End cert_has_access_to_course;
301
302
303
304 --Function called to check whether a particular learning object can be added to an offering
305 --When called from catalog tab :create offering page,p_course_id is passed
306
307 function offering_has_access_to_lo(p_course_id in NUMBER,p_lo_id in NUMBER)
308 return varchar2 is
309
310 l_offrAdminGrpId number;
311 l_loAdminGrpId number;
312 begin
313 --check if the content object is secured.If it is not secured,you can add it
314 --if offering is secured,the content object should be secured with same admin group
315
316 l_offrAdminGrpId := get_catalog_obj_admin_grp('H',p_course_id);
317 l_loAdminGrpId := get_content_obj_admin_grp('LO',p_lo_id);
318
319 if l_loAdminGrpId is null then
320 return 'Y';
321 end if;
322
323 if l_offrAdminGrpId = l_loAdminGrpId then
324 return 'Y';
325 else
326 return 'N';
327 end if;
328
329 end offering_has_access_to_lo;
330
331
332
333 --when called from content tab,quick offering page,use existing course:p_course_id id passed
334 --when called from content tab,quick offering page,create new course:p_cateory_id id passed
335
336 function lo_has_access_to_offering(p_course_id in NUMBER,p_lo_id in NUMBER,p_category_id in NUMBER default NULL)
337 return varchar2 is
338
339 l_offrAdminGrpId number;
340 l_loAdminGrpId number;
341 begin
342 --check if the content object is secured.If it is not secured,you can add it to any object accessible.
343 --if offering is secured,the content object should be secured with same admin group
344
345 if p_course_id is not null then
346 l_offrAdminGrpId := get_catalog_obj_admin_grp('H',p_course_id);
347 else
348 l_offrAdminGrpId := get_catalog_obj_admin_grp('C',p_category_id);
349 end if;
350
351 l_loAdminGrpId := get_content_obj_admin_grp('LO',p_lo_id);
352
353
354 if l_loAdminGrpId is null then
355 if p_course_id is not null then
356 return admin_can_access_object('H',p_course_id);
357 else
358 return admin_can_access_object('C',p_category_id);
359 end if;
360 end if;
361
362 if(l_offrAdminGrpId = l_loAdminGrpId) then
363 return 'Y';
364 end if;
365
366 return 'N';
367
368
369 end lo_has_access_to_offering;
370 function is_root_category(p_category_id in NUMBER)
371 return boolean is
372
373 Cursor get_root_id IS
374 Select parent_cat_usage_id
375 From ota_category_usages
376 Where category_usage_id=p_category_id;
377
378 l_root_id number := null;
379 begin
380 OPEN get_root_id;
381 Fetch get_root_id into l_root_id;
382 Close get_root_id;
383
384 if l_root_id is null then
385 return true;
386 else
387 return false;
388 end if;
389
390 end is_root_category;
391
392
393 --This function is used for displaying categories for copy functionality of catalog objects
394 --and move functionality of category
395
396 function category_has_access_to_object(p_object_type in varchar2,
397 p_object_id in NUMBER,
398 p_category_id in NUMBER) return varchar2 is
399 l_ctgAdminGrpId number;
400 l_objAdminGrpId number;
401
402 begin
403 --1.always display root
404 --2.if the catalog object to be copied(or category to be moved) is access controlled,
405 --then category should be access controlled with same admin group or not access controlled
406 --This is done as a non access controlled category may have child categories which are access
407 --controlled.Hence display them.The selection can be restricted based on function disable_select
408 --3.if catalog object is not access controlled,then category should also be non access controlled
409 if is_root_category(p_category_id) then
410 return 'Y';
411 else
412 l_ctgAdminGrpId := get_catalog_obj_admin_grp('C',p_category_id);
413 l_objAdminGrpId := get_catalog_obj_admin_grp(p_object_type,p_object_id);
414
415 if(l_objAdminGrpId is null ) then
416 if (l_ctgAdminGrpId is null) then
417 return 'Y';
418 else
419 return 'N';
420 end if;
421 else
422 if((l_ctgAdminGrpId is null) or (l_ctgAdminGrpId = l_objAdminGrpId))then
423 return 'Y';
424 else
425 return 'N';
426 end if;
427 end if;
428
429 end if;
430
431 end category_has_access_to_object;
432
436 p_object_id in NUMBER,
433 --This function governs whether a displayed category/course can be selected for the copy/move functionality
434
435 function disable_select(p_object_type in varchar2,
437 p_dest_object_type in varchar2,
438 p_dest_object_id in NUMBER,
439 p_action in varchar2 default 'Copy' ) return varchar2 is
440 l_destAdminGrpId number;
441 l_objAdminGrpId number;
442
443 begin
444 --1.always allow root selection
445 --2.if catalog object is not access controlled,then category/course should also be non access controlled
446 --This holds good for copy of catalog objects and move for category
447 --3.if the catalog object to be copied(or category to be moved) is access controlled,
448 --3.1.if action is copy then destination object should be secured with same admin group
449 --3.2.if action is move then destination object can either be non access controlled or access controlled with the same admin group
450 if ((p_dest_object_type='C') and is_root_category(p_dest_object_id)) then
451 return 'N';
452 else
453 l_destAdminGrpId := get_catalog_obj_admin_grp(p_dest_object_type,p_dest_object_id);
454 l_objAdminGrpId := get_catalog_obj_admin_grp(p_object_type,p_object_id);
455
456 if(p_action = 'Copy') then
457 if ((l_destAdminGrpId is null and l_objAdminGrpId is null ) or (l_destAdminGrpId = l_objAdminGrpId))then
458 return 'N';
459 else
460 return 'Y';
461 end if;
462 else
463 --move action
464 if(l_objAdminGrpId is null ) then
465 if (l_destAdminGrpId is null) then
466 return 'N';
467 else
468 return 'Y';
469 end if;
470 else
471 if((l_destAdminGrpId is null) or (l_destAdminGrpId = l_objAdminGrpId))then
472 return 'N';
473 else
474 return 'Y';
475 end if;
476 end if;
477
478 end if; --end move
479
480 end if;
481
482 end disable_select;
483
484
485
486
487 /*function category_has_access_to_object(p_object_type in varchar2,
488 p_object_id in NUMBER,
489 p_category_id in NUMBER) return varchar2 is
490 l_ctgAdminGrpId number;
491 l_objAdminGrpId number;
492
493 begin
494 --always display root
495 --if catalog object is not secured,then category should also be unsecured
496 --if the catalog object to be copied(or category to be moved) is secured,then category should be secured with same admin group
497 if is_root_category(p_category_id) then
498 return 'Y';
499 else
500 l_ctgAdminGrpId := get_catalog_obj_admin_grp('C',p_category_id);
501 l_objAdminGrpId := get_catalog_obj_admin_grp(p_object_type,p_object_id);
502
503 if ((l_ctgAdminGrpId is null and l_objAdminGrpId is null ) or (l_ctgAdminGrpId = l_objAdminGrpId))then
504 return 'Y';
505 else
506 return 'N';
507 end if;
508
509 end if;
510
511 end category_has_access_to_object;*/
512
513
514 function is_root_folder(p_folder_id in NUMBER)
515 return boolean is
516
517 Cursor get_root_id IS
518 Select parent_folder_id
519 From ota_lo_folders
520 Where folder_id=p_folder_id;
521
522 l_root_id number := null;
523 begin
524 OPEN get_root_id;
525 Fetch get_root_id into l_root_id;
526 Close get_root_id;
527
528 if l_root_id is null then
529 return true;
530 else
531 return false;
532 end if;
533
534 end is_root_folder;
535
536 --This function is used for displaying folders for copy functionality of content objects
537 function folder_has_access_to_object(p_object_type in varchar2,
538 p_object_id in NUMBER,
539 p_folder_id in NUMBER) return varchar2 is
540 l_fAdminGrpId number;
541 l_objAdminGrpId number;
542
543 begin
544 --1.always display root
545 --2.if the content object to be copied is access controlled,
546 --then folder should be access controlled with same admin group or not access controlled
547 --This is done as a non access controlled folder may have child folder which are access
548 --controlled.Hence display them.The selection can be restricted based on function disable_folder_select
549 --3.if content object is not access controlled,then folder should also be non access controlled
550 if is_root_folder(p_folder_id) then
551 return 'Y';
552 else
553 l_fAdminGrpId := get_content_obj_admin_grp('F',p_folder_id);
554 l_objAdminGrpId := get_content_obj_admin_grp(p_object_type,p_object_id);
555
556 --Modified for Bug 8975113
557
558 /*if ((l_fAdminGrpId is null and l_objAdminGrpId is null ) or (l_fAdminGrpId = l_objAdminGrpId))then
559 return 'Y';
560 else
561 return 'N';
562 end if;*/
563
564 if(l_objAdminGrpId is null ) then
565 if (l_fAdminGrpId is null) then
566 return 'Y';
567 else
568 return 'N';
569 end if;
570 else
571 if((l_fAdminGrpId is null) or (l_fAdminGrpId = l_objAdminGrpId))then
572 return 'Y';
573 else
574 return 'N';
575 end if;
576 end if;
577
578
579 end if;
580
581 end folder_has_access_to_object;
582
583
584 --Added for Bug 8975113 - user is not able to copy to an access controlled folder
585 --This function governs whether a displayed content object can be selected for the copy functionality
586 function disable_content_obj_select(p_object_type in varchar2,
587 p_object_id in NUMBER,
588 p_dest_obj_type in varchar2,
589 p_dest_obj_id in NUMBER
590 ) return varchar2 is
591 l_destAdminGrpId number;
592 l_objAdminGrpId number;
593
594 begin
595 --1.always allow root selection
596 --2.if content object is not access controlled,then folder should also be non access controlled
597 --3.if the content object to be copied is access controlled,
598 -- then destination folder object should be access controlled with same admin group
599
600 if ((p_dest_obj_type='F') and is_root_folder(p_dest_obj_id)) then
601 return 'N';
602 else
603
604 l_destAdminGrpId := get_content_obj_admin_grp(p_dest_obj_type,p_dest_obj_id);
605 l_objAdminGrpId := get_content_obj_admin_grp(p_object_type,p_object_id);
606
607 if ((l_destAdminGrpId is null and l_objAdminGrpId is null ) or (l_destAdminGrpId = l_objAdminGrpId))then
608 return 'N';
609 else
610 return 'Y';
611 end if;
612
613
614 end if;
615
616 end disable_content_obj_select;
617 --This function is used for displaying learning objects for copy functionality of content objects
618 function lo_has_access_to_object(p_object_type in varchar2,
619 p_object_id in NUMBER,
620 p_lo_id in NUMBER) return varchar2 is
621 l_loAdminGrpId number;
622 l_objAdminGrpId number;
623
624 begin
625 --always display root
626 --if content object is not secured,then folder should also be unsecured
627 --if the content object to be copied is secured,then folder should be secured with same admin group
628
629 l_loAdminGrpId := get_content_obj_admin_grp('LO',p_lo_id);
630 l_objAdminGrpId := get_content_obj_admin_grp(p_object_type,p_object_id);
631
632 if ((l_loAdminGrpId is null and l_objAdminGrpId is null ) or (l_loAdminGrpId = l_objAdminGrpId))then
633 return 'Y';
634 else
635 return 'N';
636 end if;
637
638
639
640 end lo_has_access_to_object;
641
642
643 --This function is used for displaying eval objects while admin tries to add course/class evaluations
644 function object_has_access_to_eval(p_object_type in varchar,p_object_id in NUMBER,p_test_id in NUMBER)
645 return varchar2 is
646
647 l_objAdminGrpId number;
648 l_loAdminGrpId number;
649 l_lo_id number;
650
651 begin
652 --check if the eval is secured.If it is not secured,you can add it
653 --if course is secured,the eval should be secured with same admin group
654
655
656 OPEN get_lo_id(p_test_id);
657 Fetch get_lo_id into l_lo_id;
658 Close get_lo_id;
659
660
661 l_objAdminGrpId := get_catalog_obj_admin_grp(p_object_type,p_object_id);
662 l_loAdminGrpId := get_content_obj_admin_grp('LO',l_lo_id);
663
664
665 if l_loAdminGrpId is null then
666 return 'Y';
667 end if;
668
669 if(l_objAdminGrpId = l_loAdminGrpId) then
670 return 'Y';
671 else
672 return 'N';
673 end if;
674
675
676 End object_has_access_to_eval;
677
678
679
680 function test_has_access_to_qbank(p_qbank_id in NUMBER,p_lo_id in number,p_folder_id in number)
681 return varchar2 is
682
683
684 l_qbAdminGrpId number;
685 l_loAdminGrpId number;
686
687
688 begin
689 --check if the question bank is secured.If it is not secured,you can add it
690 --if question bank is secured,the test should be secured with same admin group
691
692
693 l_qbAdminGrpId := get_content_obj_admin_grp('F',p_folder_id);
694 l_loAdminGrpId := get_content_obj_admin_grp('LO',p_lo_id);
695
696
697
698 if l_qbAdminGrpId is null then
699 return 'Y';
700 end if;
701
702 if(l_loAdminGrpId = l_qbAdminGrpId) then
703 return 'Y';
704 else
705 return 'N';
706
707 end if;
708
709 End test_has_access_to_qbank;
710
711
712
713
714 function test_has_access_to_qbank(p_qbank_id in NUMBER,p_test_id in NUMBER)
715 return varchar2 is
716
717 Cursor get_folder_id IS
718 Select folder_id
719 from ota_question_banks
720 where question_bank_id=p_qbank_id;
721
722
723
724 l_qbAdminGrpId number;
725 l_folder_id number;
726 l_loAdminGrpId number;
727 l_lo_id number;
728
729 begin
730 --check if the question bank is secured.If it is not secured,you can add it
731 --if question bank is secured,the test should be secured with same admin group
732
733
734 OPEN get_lo_id(p_test_id);
735 Fetch get_lo_id into l_lo_id;
736 Close get_lo_id;
737
738 OPEN get_folder_id;
739 Fetch get_folder_id into l_folder_id;
740 Close get_folder_id;
741
742 l_qbAdminGrpId := get_content_obj_admin_grp('F',l_folder_id);
743 l_loAdminGrpId := get_content_obj_admin_grp('LO',l_lo_id);
744
745
746
747 if l_qbAdminGrpId is null then
748 return 'Y';
749 end if;
750
751 if(l_loAdminGrpId = l_qbAdminGrpId) then
752 return 'Y';
753 else
754 return 'N';
755
756 end if;
757
758 End test_has_access_to_qbank;
759 --This function is used for displaying categories for any catalog object:side nav->Category
760 --After adding a category,admin can change the primary category,thereby moving the catalog object
761 --across category.Hence tighter restriction
762
763 function object_can_add_category(p_object_type in varchar,
764 p_object_id in NUMBER,
765 p_category_usage_id in NUMBER)
766 return varchar2 is
767
768
769
770 l_objAdminGrpId number;
771 l_ctgAdminGrpId number;
772
773
774 begin
775 --if object is not secured,then category should also be unsecured
776 --if the object is secured,then category should be secured with same admin group
777
778
779 l_objAdminGrpId := get_catalog_obj_admin_grp(p_object_type,p_object_id);
780
781 OPEN check_is_category_secured(p_category_usage_id);
782 Fetch check_is_category_secured into l_ctgAdminGrpId;
783 Close check_is_category_secured;
784
785
786
787 if ((l_ctgAdminGrpId is null and l_objAdminGrpId is null ) or (l_ctgAdminGrpId = l_objAdminGrpId))then
788 return 'Y';
789 else
790 return 'N';
791 end if;
792
793 End object_can_add_category;
794
795 --course/player prereqs
796 Function can_add_object_as_prereq(p_object_type in varchar,
797 p_obj_id in NUMBER,
798 p_prereq_obj_id in NUMBER) return varchar2 is
799
800 l_objAdminGrpId number:=null;
801 l_prereq_objAdminGrpId number :=null;
802
803 begin
804
805 if p_object_type = 'H' then
806 l_objAdminGrpId := get_catalog_obj_admin_grp('H',p_obj_id);
807 l_prereq_objAdminGrpId := get_catalog_obj_admin_grp('H',p_prereq_obj_id);
808 elsif p_object_type = 'LO' then
809 l_objAdminGrpId := get_content_obj_admin_grp('LO',p_obj_id);
810 l_prereq_objAdminGrpId := get_content_obj_admin_grp('LO',p_prereq_obj_id);
811 end if;
812
813 if ((l_prereq_objAdminGrpId is null ) or (l_objAdminGrpId = l_prereq_objAdminGrpId))then
814 return 'Y';
815 else
816 return 'N';
817 end if;
818
819 End can_add_object_as_prereq;
820
821 function get_lo_offering_count (p_learning_object_id in number) return varchar2
822 IS
823 l_offering_count number;
824
825 CURSOR c_get_offering_count IS
826 SELECT count(*)
827 FROM ota_offerings
828 WHERE learning_object_id = p_learning_object_id
829 and admin_can_access_object('O',offering_id)='Y';
830
831 BEGIN
832 open c_get_offering_count;
833 fetch c_get_offering_count into l_offering_count;
834 close c_get_offering_count;
835
836 return(l_offering_count);
837
838 end get_lo_offering_count ;
839
840
841 --for valuesets.Need to find whther it is a category or class chat and then
842 --perform the appropriate action
843 function admin_can_access_chat(p_chat_id in number) return varchar2
844 IS
845
846 cursor get_chat_details is
847 select object_type,object_id
848 from ota_chat_obj_inclusions
849 where chat_id=p_chat_id
850 and primary_flag='Y';
851
852 l_chat_type varchar2(4);
853 l_object_id number;
854
855 BEGIN
856 open get_chat_details;
857 fetch get_chat_details into l_chat_type,l_object_id;
858 close get_chat_details;
859
860 if l_chat_type = 'C' then
861 return(admin_can_access_object('C',l_object_id));
862 else
863 return(admin_can_access_object('CL',l_object_id));
864 end if;
865
866 end admin_can_access_chat;
867
868
869 --for valuesets.Need to find whther it is a category or class forum and then
870 --perform the appropriate action
871 function admin_can_access_forum(p_forum_id in number) return varchar2
872 IS
873
874 --bugBug 8916572 - tst1212:user is able to generate report for forums outside administrator group
875 /*cursor get_forum_details is
876 select object_type,object_id
877 from ota_chat_obj_inclusions
878 where chat_id=p_forum_id
879 and primary_flag='Y';*/
880
881 cursor get_forum_details is
882 select object_type,object_id
883 from ota_frm_obj_inclusions
884 where forum_id=p_forum_id
885 and primary_flag='Y';
886
887 l_forum_type varchar2(4);
888 l_object_id number;
889
890 BEGIN
891 open get_forum_details;
892 fetch get_forum_details into l_forum_type,l_object_id;
893 close get_forum_details;
894
895 if l_forum_type = 'C' then
896 return(admin_can_access_object('C',l_object_id));
897 else
898 return(admin_can_access_object('CL',l_object_id));
899 end if;
900
901 end admin_can_access_forum;
902
903
904
905 end ota_admin_access_util;
906
[Home] [Help]