1 package body ota_learner_access_util as
2 /* $Header: otlrnacc.pkb 120.30 2008/07/11 06:47:21 pekasi noship $ */
3
4 person_ug_map_rec_table ota_learner_access_util.person_ug_map_table;
5 t_ug_learner_list_table ota_learner_access_util.ug_learner_list_table;
6
7 cursor csr_evt_tree(
8 p_event_id ota_events.event_id%type) is
9 select o.offering_id, i.activity_version_id, i.category_usage_id,
10 c1.parent_cat_usage_id as parent_cat_usage_id_1,
11 c2.parent_cat_usage_id as parent_cat_usage_id_2,
12 c3.parent_cat_usage_id as parent_cat_usage_id_3,
13 c4.parent_cat_usage_id as parent_cat_usage_id_4
14 from ota_events e,
15 ota_offerings o,
16 ota_act_cat_inclusions i,
17 ota_category_usages c1,
18 ota_category_usages c2,
19 ota_category_usages c3,
20 ota_category_usages c4
21 where e.event_id = p_event_id and
22 e.parent_offering_id = o.offering_id and
23 o.activity_version_id = i.activity_version_id and
24 i.primary_flag = 'Y' and
25 i.category_usage_id = c1.category_usage_id and
26 c1.parent_cat_usage_id = c2.category_usage_id(+) and
27 c2.parent_cat_usage_id = c3.category_usage_id(+) and
28 c3.parent_cat_usage_id = c4.category_usage_id(+);
29
30
31 cursor csr_lp_tree(
32 p_learning_path_id ota_learning_paths.learning_path_id%type) is
33 select i.category_usage_id,
34 c1.parent_cat_usage_id as parent_cat_usage_id_1,
35 c2.parent_cat_usage_id as parent_cat_usage_id_2,
36 c3.parent_cat_usage_id as parent_cat_usage_id_3,
37 c4.parent_cat_usage_id as parent_cat_usage_id_4
38 from ota_lp_cat_inclusions i,
39 ota_category_usages c1,
40 ota_category_usages c2,
41 ota_category_usages c3,
42 ota_category_usages c4
43 where i.learning_path_id = p_learning_path_id and
44 i.primary_flag = 'Y' and
45 i.category_usage_id = c1.category_usage_id and
46 c1.parent_cat_usage_id = c2.category_usage_id(+) and
47 c2.parent_cat_usage_id = c3.category_usage_id(+) and
48 c3.parent_cat_usage_id = c4.category_usage_id(+);
49
50 cursor csr_forum_tree(
51 p_forum_id ota_forums_b.forum_id%type) is
52 select i.object_id,
53 c1.parent_cat_usage_id as parent_cat_usage_id_1,
54 c2.parent_cat_usage_id as parent_cat_usage_id_2,
55 c3.parent_cat_usage_id as parent_cat_usage_id_3,
56 c4.parent_cat_usage_id as parent_cat_usage_id_4
57 from ota_frm_obj_inclusions i,
58 ota_category_usages c1,
59 ota_category_usages c2,
60 ota_category_usages c3,
61 ota_category_usages c4
62 where i.forum_id = p_forum_id and
63 i.primary_flag = 'Y' and
64 i.object_id = c1.category_usage_id and
65 i.object_type = 'C' and
66 c1.parent_cat_usage_id = c2.category_usage_id(+) and
67 c2.parent_cat_usage_id = c3.category_usage_id(+) and
68 c3.parent_cat_usage_id = c4.category_usage_id(+);
69
70 cursor csr_chat_tree(
71 p_chat_id ota_chats_b.chat_id%type) is
72 select i.object_id,
73 c1.parent_cat_usage_id as parent_cat_usage_id_1,
74 c2.parent_cat_usage_id as parent_cat_usage_id_2,
75 c3.parent_cat_usage_id as parent_cat_usage_id_3,
76 c4.parent_cat_usage_id as parent_cat_usage_id_4
77 from ota_chat_obj_inclusions i,
78 ota_category_usages c1,
79 ota_category_usages c2,
80 ota_category_usages c3,
81 ota_category_usages c4
82 where i.chat_id = p_chat_id and
83 i.primary_flag = 'Y' and
84 i.object_id = c1.category_usage_id and
85 i.object_type = 'C' and
86 c1.parent_cat_usage_id = c2.category_usage_id(+) and
87 c2.parent_cat_usage_id = c3.category_usage_id(+) and
88 c3.parent_cat_usage_id = c4.category_usage_id(+);
89
90
91 cursor csr_cert_tree(
92 p_certification_id ota_certifications_b.certification_id%type) is
93 select i.category_usage_id,
94 c1.parent_cat_usage_id as parent_cat_usage_id_1,
95 c2.parent_cat_usage_id as parent_cat_usage_id_2,
96 c3.parent_cat_usage_id as parent_cat_usage_id_3,
97 c4.parent_cat_usage_id as parent_cat_usage_id_4
98 from ota_cert_cat_inclusions i,
99 ota_category_usages c1,
100 ota_category_usages c2,
101 ota_category_usages c3,
102 ota_category_usages c4
103 where i.certification_id = p_certification_id and
104 i.primary_flag = 'Y' and
105 i.category_usage_id = c1.category_usage_id and
106 c1.parent_cat_usage_id = c2.category_usage_id(+) and
107 c2.parent_cat_usage_id = c3.category_usage_id(+) and
108 c3.parent_cat_usage_id = c4.category_usage_id(+);
109
110
111 cursor csr_evt_assoc(
112 p_self_enroll_only varchar2,
113 p_event_id ota_events.event_id%type,
114 p_offering_id ota_offerings.offering_id%type,
115 p_activity_version_id ota_activity_versions.activity_version_id%type,
116 p_category_usage_id ota_category_usages.category_usage_id%type,
117 p_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type,
118 p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
119 p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
120 p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
121 select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
122 from ota_event_associations ea
123 where ea.party_id is null and
124 ea.customer_id is null and
125 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
126 (
127 ea.event_id = p_event_id or
128 ea.offering_id = p_offering_id or
129 ea.activity_version_id = p_activity_version_id or
130 ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
131
132
133 /*Bug 7046019- Modified cursor check_mandatory_evt_assoc to include learner groups
134 Cursor check_mandatory_evt_assoc(
135 p_event_id ota_events.event_id%type,
136 p_person_id ota_event_associations.person_id%type,
137 p_as_of in date
138 ) is
139 select ea.event_association_id
140 from
141 ota_event_associations ea,
142 per_all_assignments_f paf
143 where ea.event_id = p_event_id
144 and paf.person_id = p_person_id
145 AND nvl(ea.mandatory_enrollment_flag,'N') = 'Y'
146 AND paf.assignment_type in ('E','A','C')
147 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
148 AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
149 OR nvl(p_as_of, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
150 AND nvl(ea.job_id, -1) = decode(ea.job_id, null, -1, paf.job_id)
151 AND nvl(ea.position_id,-1) = decode(ea.position_id, null, -1,paf.position_id)
152 AND nvl(ea.person_id,-1) = decode(ea.person_id,null,-1,p_person_id)
153 AND
154 (
155 ( nvl(ea.organization_id, -1) = decode(ea.organization_id, null, -1, paf.organization_id)) OR
156 ( ea.org_structure_version_id IS NOT NULL AND learner_belongs_to_child_org(ea.org_structure_version_id,ea.organization_id,paf.person_id)='Y')
157 );*/
158
159 Cursor check_mandatory_evt_assoc(
160 p_event_id ota_events.event_id%type,
161 p_person_id ota_event_associations.person_id%type,
162 p_as_of in date
163 ) is
164 select ea.event_association_id
165 from
166 ota_event_associations ea,
167 per_all_assignments_f paf
168 where ea.event_id = p_event_id
169 and paf.person_id = p_person_id
170 AND nvl(ea.mandatory_enrollment_flag,'N') = 'Y'
171 AND paf.assignment_type in ('E','A','C')
172 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
173 AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
174 OR nvl(p_as_of, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
175 AND ((ea.user_group_id is null
176 AND nvl(ea.job_id, -1) = decode(ea.job_id, null, -1, paf.job_id)
177 AND nvl(ea.position_id,-1) = decode(ea.position_id, null, -1,paf.position_id)
178 AND nvl(ea.person_id,-1) = decode(ea.person_id,null,-1,p_person_id)
179 AND(( nvl(ea.organization_id, -1) = decode(ea.organization_id, null, -1, paf.organization_id)) OR
180 ( ea.org_structure_version_id IS NOT NULL AND learner_belongs_to_child_org(ea.org_structure_version_id,ea.organization_id,paf.person_id)='Y'))
181 )OR(ea.user_group_id is not null AND is_learner_in_user_group(p_person_id,ea.user_group_id, ota_general.get_business_group_id)= 'Y')
182 );
183
184
185 cursor csr_evt_assoc_ext(
186 p_self_enroll_only varchar2,
187 p_party_id hz_parties.party_id%type,
188 p_event_id ota_events.event_id%type,
189 p_offering_id ota_offerings.offering_id%type,
190 p_activity_version_id ota_activity_versions.activity_version_id%type,
191 p_category_usage_id ota_category_usages.category_usage_id%type,
192 p_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type,
193 p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
194 p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
195 p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
196 select 1
197 from ota_event_associations ea
198 where ea.party_id = p_party_id and
199 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
200 (
201 ea.event_id = p_event_id or
202 ea.offering_id = p_offering_id or
203 ea.activity_version_id = p_activity_version_id or
204 ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
205
206
207 cursor csr_category_assoc(
208 p_self_enroll_only varchar2,
209 p_category_usage_id ota_category_usages.category_usage_id%type) is
210 select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
211 from ota_event_associations ea,
212 (
213 select cu.category_usage_id
214 from ota_category_usages cu
215 where cu.type = 'C'
216 connect by cu.category_usage_id = prior cu.parent_cat_usage_id
217 start with cu.category_usage_id = p_category_usage_id
218 ) cat
219 where ea.party_id is null and
220 ea.customer_id is null and
221 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
222 ea.category_usage_id = cat.category_usage_id;
223
224
225 cursor csr_category_assoc_ext(
226 p_self_enroll_only varchar2,
227 p_party_id in ota_event_associations.party_id%type,
228 p_category_usage_id ota_category_usages.category_usage_id%type) is
229 select 1
230 from ota_event_associations ea,
231 (
232 select cu.category_usage_id
233 from ota_category_usages cu
234 where cu.type = 'C'
235 connect by cu.category_usage_id = prior cu.parent_cat_usage_id
236 start with cu.category_usage_id = p_category_usage_id
237 ) cat
238 where ea.party_id = p_party_id and
239 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
240 ea.category_usage_id = cat.category_usage_id;
241
242
243 cursor csr_lp_assoc(
244 p_self_enroll_only varchar2,
245 p_learning_path_id in ota_learning_paths.learning_path_id%type,
246 p_category_usage_id ota_category_usages.category_usage_id%type,
247 p_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type,
248 p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
249 p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
250 p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
251 select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
252 from ota_event_associations ea
253 where ea.customer_id is null and
254 ea.party_id is null and
255 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
256 (
257 ea.learning_path_id = p_learning_path_id or
258 ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
259
260
261 cursor csr_lp_assoc_ext(
262 p_self_enroll_only varchar2,
263 p_party_id hz_parties.party_id%type,
264 p_learning_path_id in ota_learning_paths.learning_path_id%type,
265 p_category_usage_id ota_category_usages.category_usage_id%type,
266 p_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type,
267 p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
268 p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
269 p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
270 select 1
271 from ota_event_associations ea
272 where ea.party_id = p_party_id and
273 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
274 (
275 ea.learning_path_id = p_learning_path_id or
276 ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
277
278
279 cursor csr_forum_assoc(
280 p_self_enroll_only varchar2,
281 p_forum_id in ota_forums_b.forum_id%type,
282 p_category_usage_id ota_category_usages.category_usage_id%type,
283 p_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type,
284 p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
285 p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
286 p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
287 select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
288 from ota_event_associations ea
289 where ea.customer_id is null and
290 ea.party_id is null and
291 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
292 (
293 ea.forum_id = p_forum_id or
294 ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
295
296
297 cursor csr_forum_assoc_ext(
298 p_self_enroll_only varchar2,
299 p_party_id hz_parties.party_id%type,
300 p_forum_id in ota_forums_b.forum_id%type,
301 p_category_usage_id ota_category_usages.category_usage_id%type,
302 p_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type,
303 p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
304 p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
305 p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
306 select 1
307 from ota_event_associations ea
308 where ea.party_id = p_party_id and
309 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
310 (
311 ea.forum_id = p_forum_id or
312 ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
313
314
315 cursor csr_chat_assoc(
316 p_self_enroll_only varchar2,
317 p_chat_id in ota_chats_b.chat_id%type,
318 p_category_usage_id ota_category_usages.category_usage_id%type,
319 p_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type,
320 p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
321 p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
322 p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
323 select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
324 from ota_event_associations ea
325 where ea.customer_id is null and
326 ea.party_id is null and
327 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
328 (
329 ea.chat_id = p_chat_id or
330 ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
331
332
333 cursor csr_chat_assoc_ext(
334 p_self_enroll_only varchar2,
335 p_party_id hz_parties.party_id%type,
336 p_chat_id in ota_chats_b.chat_id%type,
337 p_category_usage_id ota_category_usages.category_usage_id%type,
338 p_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type,
339 p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
340 p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
341 p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
342 select 1
343 from ota_event_associations ea
344 where ea.party_id = p_party_id and
345 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
346 (
347 ea.chat_id = p_chat_id or
348 ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
349
350
351 cursor csr_cert_assoc(
352 p_self_enroll_only varchar2,
353 p_certification_id in ota_certifications_b.certification_id%type,
354 p_category_usage_id ota_category_usages.category_usage_id%type,
355 p_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type,
356 p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
357 p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
358 p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
359 select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
360 from ota_event_associations ea
361 where ea.customer_id is null and
362 ea.party_id is null and
363 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
364 (
365 ea.certification_id = p_certification_id or
366 ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
367
368
369 cursor csr_cert_assoc_ext(
370 p_self_enroll_only varchar2,
371 p_party_id hz_parties.party_id%type,
372 p_certification_id in ota_certifications_b.certification_id%type,
373 p_category_usage_id ota_category_usages.category_usage_id%type,
374 p_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type,
375 p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
376 p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
377 p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
378 select 1
379 from ota_event_associations ea
380 where ea.party_id = p_party_id and
381 (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
382 (
383 ea.certification_id = p_certification_id or
384 ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
385
386 /*Bug6679100-As exemployees have 2 records in per_all_assignments_f
387 modified csr_asg_details and csr_asg_details_recursive to use system_person_type while
388 validating the active assignment record.
389 cursor csr_asg_details(
390 p_person_id per_people_f.person_id%type,
391 p_organization_id ota_event_associations.organization_id%type,
392 p_job_id ota_event_associations.job_id%type,
393 p_position_id ota_event_associations.position_id%type,
394 p_as_of date) is
395 select asg.assignment_id
396 from per_all_assignments_f asg
397 where asg.person_id = p_person_id
398 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
399 AND trunc(sysdate) BETWEEN asg.effective_start_date and asg.effective_end_date)
400 OR nvl(p_as_of, trunc(sysdate)) between asg.effective_start_date and asg.effective_end_date )
401 and
402 nvl(p_organization_id, -1) = decode(p_organization_id, null, -1, nvl(asg.organization_id,-1)) and
403 nvl(p_job_id, -1) = decode(p_job_id, null, -1, nvl(asg.job_id, -1)) and
404 nvl(p_position_id,-1) = decode(p_position_id, null, -1, nvl(asg.position_id, -1)) and
405 asg.assignment_type in ('E','A','C');
406
407
408 cursor csr_asg_details_recursive(
409 p_person_id per_people_f.person_id%type,
410 p_organization_id ota_event_associations.organization_id%type,
411 p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
412 p_job_id ota_event_associations.job_id%type,
413 p_position_id ota_event_associations.position_id%type,
414 p_as_of date) is
415 select asg.assignment_id
416 from per_all_assignments_f asg,
417 (
418 select p_organization_id as organization_id
419 from dual
420 union all
421 select x.sub_organization_id as organization_id
422 from per_org_hrchy_summary x,
423 per_org_structure_versions v,
424 per_org_structure_versions currv
425 where v.org_structure_version_id = p_org_structure_version_id and
426 v.organization_structure_id = currv.organization_structure_id and
427 (currv.date_to is null or
428 sysdate between currv.date_from and currv.date_to) and
429 x.organization_structure_id = currv.organization_structure_id and
430 x.org_structure_version_id = currv.org_structure_version_id and
431 x.organization_id = p_organization_id and
432 x.sub_org_relative_level > 0
433 ) orgs
434 where asg.person_id = p_person_id
435 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
436 AND trunc(sysdate) BETWEEN asg.effective_start_date and asg.effective_end_date)
437 OR nvl(p_as_of, trunc(sysdate)) between asg.effective_start_date and asg.effective_end_date )
438 AND nvl(p_as_of, trunc(sysdate)) between asg.effective_start_date and asg.effective_end_date and
439 asg.organization_id = orgs.organization_id and
440 nvl(p_job_id, -1) = decode(p_job_id, null, -1, nvl(asg.job_id, -1)) and
441 nvl(p_position_id,-1) = decode(p_position_id, null, -1, nvl(asg.position_id, -1)) and
442 asg.assignment_type in ('E','A','C');*/
443
444 cursor csr_asg_details(
445 p_person_id per_people_f.person_id%type,
446 p_organization_id ota_event_associations.organization_id%type,
447 p_job_id ota_event_associations.job_id%type,
448 p_position_id ota_event_associations.position_id%type,
449 p_as_of date) is
450 select asg.assignment_id
451 from per_all_assignments_f asg
452 ,per_person_types ppt
453 ,per_all_people_f perp
454 ,per_person_type_usages_f ptu
455 where asg.person_id = p_person_id
456 and asg.person_id = perp.person_id
457 and perp.person_id =ptu.person_id
458 and ptu.person_type_id=ppt.person_type_id
459 and ((asg.primary_flag = 'Y' and ppt.system_person_type in ('EMP','CWK','OTHER'))
460 OR (asg.assignment_type = 'A' and ppt.system_person_type ='APL'))
461 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
462 AND trunc(sysdate) BETWEEN asg.effective_start_date and asg.effective_end_date)
463 OR decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between asg.effective_start_date and asg.effective_end_date )
464 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
465 AND trunc(sysdate) BETWEEN perp.effective_start_date and perp.effective_end_date)
466 OR decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between perp.effective_start_date and perp.effective_end_date )
467 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
468 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
469 OR decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between ptu.effective_start_date and ptu.effective_end_date )
470 and
471 nvl(p_organization_id, -1) = decode(p_organization_id, null, -1, nvl(asg.organization_id,-1)) and
472 nvl(p_job_id, -1) = decode(p_job_id, null, -1, nvl(asg.job_id, -1)) and
473 nvl(p_position_id,-1) = decode(p_position_id, null, -1, nvl(asg.position_id, -1)) and
474 asg.assignment_type in ('E','A','C');
475
476
477
478
479 cursor csr_asg_details_recursive(
480 p_person_id per_people_f.person_id%type,
481 p_organization_id ota_event_associations.organization_id%type,
482 p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
483 p_job_id ota_event_associations.job_id%type,
484 p_position_id ota_event_associations.position_id%type,
485 p_as_of date) is
486 select asg.assignment_id
487 from per_all_assignments_f asg,
488 (
489 select p_organization_id as organization_id
490 from dual
491 union all
492 select x.sub_organization_id as organization_id
493 from per_org_hrchy_summary x,
494 per_org_structure_versions v,
495 per_org_structure_versions currv
496 where v.org_structure_version_id = p_org_structure_version_id and
497 v.organization_structure_id = currv.organization_structure_id and
498 (currv.date_to is null or
499 sysdate between currv.date_from and currv.date_to) and
500 x.organization_structure_id = currv.organization_structure_id and
501 x.org_structure_version_id = currv.org_structure_version_id and
502 x.organization_id = p_organization_id and
503 x.sub_org_relative_level > 0
504 ) orgs
505 ,per_person_types ppt
506 ,per_all_people_f perp
507 ,per_person_type_usages_f ptu
508 where asg.person_id = p_person_id
509 and asg.person_id = perp.person_id
510 and perp.person_id =ptu.person_id
511 and ptu.person_type_id=ppt.person_type_id
512 and ((asg.primary_flag = 'Y' and ppt.system_person_type in ('EMP','CWK','OTHER'))
513 OR (asg.assignment_type = 'A' and ppt.system_person_type ='APL'))
514 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
515 AND trunc(sysdate) BETWEEN asg.effective_start_date and asg.effective_end_date)
516 OR decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between asg.effective_start_date and asg.effective_end_date )
517 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
518 AND trunc(sysdate) BETWEEN perp.effective_start_date and perp.effective_end_date)
519 OR decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between perp.effective_start_date and perp.effective_end_date )
520 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
521 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
522 OR decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between ptu.effective_start_date and ptu.effective_end_date )
523 -- AND nvl(p_as_of, trunc(sysdate)) between asg.effective_start_date and asg.effective_end_date and
524 and asg.organization_id = orgs.organization_id and
525 nvl(p_job_id, -1) = decode(p_job_id, null, -1, nvl(asg.job_id, -1)) and
526 nvl(p_position_id,-1) = decode(p_position_id, null, -1, nvl(asg.position_id, -1)) and
527 asg.assignment_type in ('E','A','C');
528
529
530 CURSOR csr_classes_in_course(
531 p_activity_version_id in ota_activity_versions.activity_version_id%type) is
532 SELECT OEV.event_id,
533 OEV.public_event_flag,
534 OEV.maximum_internal_attendees,
535 OEV.course_start_date
536 FROM ota_activity_versions_vl OAV,
537 ota_events_vl OEV,
538 ota_offerings_vl OFR,
539 ota_category_usages OCU
540 WHERE OFR.activity_version_id = OAV.activity_version_id
541 AND OEV.parent_offering_id = OFR.offering_id
542 AND OEV.business_group_id = ota_general.get_business_group_id
543 AND OFR.delivery_mode_id = OCU.category_usage_id
544 AND OCU.type ='DM'
545 AND trunc(sysdate) BETWEEN nvl(trunc(OAV.start_date),trunc(sysdate)) AND nvl(trunc(OAV.end_date),trunc(sysdate+1))
546 AND ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code, OEV.timezone)
547 BETWEEN to_date(to_char(OEV.enrolment_start_date,'YYYY/MM/DD') || ' ' || '00:00' , 'YYYY/MM/DD HH24:MI')
548 AND to_date(to_char(nvl(OEV.enrolment_end_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI')
549 AND OEV.event_type IN ('SCHEDULED','SELFPACED')
550 AND OEV.book_independent_flag = 'N'
551 AND OEV.Event_status in('N','P','F')
552 AND OAV.activity_version_id = p_activity_version_id
553 ORDER BY OEV.public_event_flag DESC;
554
555 cursor csr_ug_hierarchy(
556 p_user_group_id in ota_user_groups_b.user_group_id%type,
557 p_business_group_id number) is
558 SELECT child_user_group_id
559 FROM ( SELECT a.user_group_id user_group_id,
560 a.child_user_group_id child_user_group_id,
561 level UG_Level,
562 b.user_group_operator user_group_operator
563 FROM ota_user_group_elements a, ota_user_groups_b b
564 WHERE a.user_group_id = b.user_group_id
565 and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate)) and trunc(nvl(b.end_date_active, sysdate+1))
566 and (b.business_group_id = p_business_group_id or p_business_group_id = -1)
567 START WITH a.user_group_id = p_user_group_id
568 CONNECT BY PRIOR a.child_user_group_id = a.user_group_id
569 ORDER by LEVEL desc
570 ) WHERE child_user_group_id is not null;
571
572 cursor csr_org_hierarchy(p_organization_id ota_user_group_elements.organization_id%type,
573 p_org_structure_version_id ota_user_group_elements.org_structure_version_id%type) is
574 select p_organization_id as organization_id
575 from dual
576 union all
577 select x.sub_organization_id as organization_id
578 from per_org_hrchy_summary x,
579 per_org_structure_versions v,
580 per_org_structure_versions currv
581 where v.org_structure_version_id = p_org_structure_version_id and
582 v.organization_structure_id = currv.organization_structure_id and
583 (currv.date_to is null or sysdate between currv.date_from and currv.date_to) and
584 x.organization_structure_id = currv.organization_structure_id and
585 x.org_structure_version_id = currv.org_structure_version_id and
586 x.organization_id = p_organization_id and
587 x.sub_org_relative_level > 0;
588
589 cursor csr_user_group_elements(
590 p_user_group_id in ota_user_groups_b.user_group_id%type,
591 p_business_group_id number) is
592 select uge.organization_id,
593 uge.org_structure_version_id,
594 uge.job_id,
595 uge.position_id,
596 uge.person_id,
597 uge.match_type,
598 uge.child_user_group_id,
599 ugb.user_group_operator
600 from ota_user_group_elements uge, ota_user_groups_b ugb
601 where uge.user_group_id = p_user_group_id
602 and uge.user_group_id = ugb.user_group_id
603 and trunc(sysdate) between trunc(nvl(ugb.start_date_active, sysdate)) and trunc(nvl(ugb.end_date_active, sysdate+1)) --Bug#7120108
604 and (uge.business_group_id = p_business_group_id or p_business_group_id = -1);
605
606 function is_customer_event(p_event_id in ota_events.event_id%type) return boolean is
607
608 cursor customer_assoc is
609 select 1
610 from ota_event_associations
611 where event_id = p_event_id and
612 customer_id is not null;
613
614 v_dummy number;
615 v_result boolean;
616 begin
617 open customer_assoc;
618 fetch customer_assoc into v_dummy;
619 v_result := customer_assoc%found;
620 close customer_assoc;
621 return v_result;
622 end is_customer_event;
623
624
625 function get_event_start_date(
626 p_event_id ota_events.event_id%type,
627 p_date date) return date is
628
629 l_event_start_date ota_events.course_start_date%type;
630 --l_date date := trunc(p_date);
631
632 l_start_date ota_events.course_start_date%type;
633 l_synchronous_flag ota_category_usages.synchronous_flag%type;
634 l_event_status ota_events.event_status%type;
635 l_event_end_date ota_events.course_end_date%type;
636 l_event_end_time ota_events.course_end_time%type;
637 l_timezone ota_events.timezone%type;
638 begin
639 -- Modified this function for bug#4393763 to return
640 -- course_end_Date for synchronous class
641 -- sysdate for asynchronous class and 'Planned' status class
642 select oev.course_start_date, oev.course_end_date,ocu.synchronous_flag, oev.event_status
643 ,oev.timezone , oev.course_end_time
644 into l_event_start_date,l_event_end_date,l_synchronous_flag, l_event_status
645 ,l_timezone , l_event_end_time
646 from ota_events oev, ota_offerings ofr, ota_category_usages ocu
647 Where oev.event_id = p_event_id and
648 oev.parent_offering_id = ofr.offering_id and
649 ofr.delivery_mode_id = ocu.category_usage_id and
650 event_type in ('SCHEDULED','SELFPACED') and
651 event_status in('P','N','F') and
652 -- l_date between nvl(enrolment_start_date, l_date) AND nvl(enrolment_end_date, l_date);
653 -- Modified for Bug#5107347
654 ota_timezone_util.convert_date(trunc(p_date),to_char(p_date,'HH24:MI'), ota_timezone_util.get_server_timezone_code , oev.timezone)
655 BETWEEN decode(oev.enrolment_start_date, NULL, to_date('0001/01/01','YYYY/MM/DD'),
656 to_date( to_char(oev.enrolment_start_date, 'YYYY/MM/DD') || ' ' || '00:00', 'YYYY/MM/DD HH24:MI'))
657 AND decode(oev.enrolment_end_date, NULL, to_date('4712/12/31','YYYY/MM/DD'),
658 to_date( to_char(oev.enrolment_end_date, 'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI'));
659 -- and l_date <= nvl(course_end_date, l_date); -- Bug 4767809
660
661 If upper(l_synchronous_flag) = 'N' then
662 -- l_start_date := sysdate;
663 l_start_date := ota_timezone_util.convert_date(trunc(sysdate)
664 , to_char(sysdate,'HH24:MI')
665 , ota_timezone_util.get_server_timezone_code
666 , l_timezone);
667 Else
668 If l_event_status = 'P' then
669 -- l_start_date := sysdate;
670 l_start_date := ota_timezone_util.convert_date(trunc(sysdate)
671 , to_char(sysdate,'HH24:MI')
672 , ota_timezone_util.get_server_timezone_code
673 , l_timezone);
674 Else
675 l_start_date := to_date(to_char(l_event_end_date,'YYYY/MM/DD')
676 || ' ' || nvl(l_event_end_time,'23:59'), 'YYYY/MM/DD HH24:MI') ;
677 End If;
678 End If;
679 /*
680 If upper(l_synchronous_flag) = 'N' then
681 l_start_date := sysdate;
682 Else
683 If l_event_status = 'P' then
684 l_start_date := sysdate;
685 Else
686 l_start_date := to_date(to_char(l_event_end_date,'YYYY/MM/DD')
687 || ' ' || nvl(l_event_end_time,'23:59'), 'YYYY/MM/DD HH24:MI') ;
688 End If;
689 End If;
690 */
691 -- return l_event_start_date;
692 return l_start_date;
693 end get_event_start_date;
694
695
696 function person_matches_assoc(
697 p_person_id in per_people_f.person_id%type,
698 p_as_of in date,
699 p_assoc_person_id in ota_event_associations.person_id%type,
700 p_assoc_organization_id in ota_event_associations.organization_id%type,
701 p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,
702 p_assoc_job_id in ota_event_associations.job_id%type,
703 p_assoc_position_id in ota_event_associations.position_id%type,
704 p_assoc_match_type in ota_event_associations.match_type%type) return boolean is
705 begin
706 if p_assoc_person_id is null then
707 if p_assoc_organization_id is not null and p_assoc_match_type = 'CHILD_ORGS' then
708 for an_assignment in csr_asg_details_recursive(p_person_id => p_person_id,
709 p_organization_id => p_assoc_organization_id,
710 p_org_structure_version_id => p_assoc_org_structure_vrsn_id,
711 p_job_id => p_assoc_job_id,
712 p_position_id => p_assoc_position_id,
713 p_as_of => p_as_of) loop
714 return true;
715 end loop;
716 else
717 for an_assignment in csr_asg_details(p_person_id => p_person_id,
718 p_organization_id => p_assoc_organization_id,
719 p_job_id => p_assoc_job_id,
720 p_position_id => p_assoc_position_id,
721 p_as_of => p_as_of) loop
722 return true;
723 end loop;
724 end if;
725
726 elsif p_assoc_person_id = p_person_id then
727 return true;
728 end if;
729
730 return false;
731 end person_matches_assoc;
732
733
734 function emp_matches_org(
735 p_person_id in per_people_f.person_id%type,
736 p_event_id in ota_events.event_id%type,
737 p_organization_id in ota_events.organization_id%type) return varchar2 is
738
739 cursor csr_assignments(
740 p_person_id per_people_f.person_id%type,
741 p_organization_id ota_event_associations.organization_id%type,
742 p_course_start_date otv_scheduled_events.course_start_date%type,
743 p_now date) is
744 select asg.assignment_id
745 from per_all_assignments_f asg
746 where asg.person_id = p_person_id and
747 nvl(p_course_start_date, trunc(p_now)) between asg.effective_start_date and asg.effective_end_date and
748 p_organization_id = asg.organization_id and
749 asg.assignment_type in ('E','A','C');
750 l_now date;
751 l_event_start_date ota_events.course_start_date%type;
752 begin
753 l_now := sysdate;
754 -- Bug 4584737: if no data found, event has expired or does not exist. Return 'N'.
755 begin
756 l_event_start_date := get_event_start_date(p_event_id, l_now);
757 exception
758 when NO_DATA_FOUND then
759 return 'N';
760 end;
761
762 for an_assignment in csr_assignments(p_person_id, p_organization_id, l_event_start_date, l_now) loop
763 return 'Y';
764 end loop;
765
766 return 'N';
767 end emp_matches_org;
768
769 FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
770 p_organization_id IN ota_event_associations.organization_id%type,
771 p_person_id IN per_people_f.person_id%type)
772 RETURN VARCHAR2 IS
773
774 CURSOR csr_lrnr_belongs_to_org IS
775 SELECT asg.assignment_id
776 FROM per_all_assignments_f asg,
777 (
778 SELECT p_organization_id AS organization_id
779 FROM dual
780 UNION ALL
781 SELECT x.sub_organization_id AS organization_id
782 FROM per_org_hrchy_summary x,
783 per_org_structure_versions v,
784 per_org_structure_versions currv
785 WHERE v.org_structure_version_id = p_org_structure_version_id AND
786 v.organization_structure_id = currv.organization_structure_id AND
787 (currv.date_to IS NULL OR
788 sysdate BETWEEN currv.date_from AND currv.date_to) AND
789 x.organization_structure_id = currv.organization_structure_id AND
790 x.org_structure_version_id = currv.org_structure_version_id AND
791 x.organization_id = p_organization_id AND
792 x.sub_org_relative_level > 0
793 ) orgs
794 WHERE asg.person_id = p_person_id AND
795 asg.organization_id = orgs.organization_id AND
796 asg.assignment_type in ('E','A','C');
797
798 l_assignment_id per_all_assignments_f.assignment_id%type;
799 BEGIN
800
801 OPEN csr_lrnr_belongs_to_org;
802 FETCH csr_lrnr_belongs_to_org INTO l_assignment_id;
803 CLOSE csr_lrnr_belongs_to_org;
804
805 IF l_assignment_id IS NOT NULL THEN
806 RETURN 'Y';
807 ELSE
808 RETURN 'N';
809 END IF;
810
811 END learner_belongs_to_child_org;
812
813 function emp_has_access(
814 p_person_id in per_people_f.person_id%type,
815 p_event_id in ota_events.event_id%type,
816 p_self_enroll_only in varchar2) return varchar2 is
817
818 l_event_start_date ota_events.course_start_date%type;
819 l_now date := sysdate;
820 l_offering_id ota_offerings.offering_id%type;
821 l_activity_version_id ota_activity_versions.activity_version_id%type;
822 l_category_usage_id ota_category_usages.category_usage_id%type;
823 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
824 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
825 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
826 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
827
828 l_is_mandatory_enr_assoc boolean:= false ;
829 l_event_association_id ota_event_associations.event_association_id%type;
830 begin
831 -- Bug 4584737: if no data found, event has expired or does not exist. Return 'N'.
832 begin
833 l_event_start_date := get_event_start_date(p_event_id, l_now);
834 exception
835 when NO_DATA_FOUND then
836 return 'N';
837 end;
838 --Perform the below actions only if mandatory event associations are not associated with the person for given event
839 open check_mandatory_evt_assoc(p_event_id,p_person_id,l_event_start_date);
840 fetch check_mandatory_evt_assoc into l_event_association_id;
841
842 if check_mandatory_evt_assoc%NOTFOUND then
843 l_is_mandatory_enr_assoc :=false;
844 else
845 l_is_mandatory_enr_assoc := true;
846 end if;
847
848 close check_mandatory_evt_assoc;
849
850 if not l_is_mandatory_enr_assoc then
851 -- Get the event's offering, course, and five levels of categories. If the category tree is deeper than that,
852 -- we will have to use CONNECT BY below.
853 open csr_evt_tree(p_event_id);
854 fetch csr_evt_tree into
855 l_offering_id, l_activity_version_id, l_category_usage_id,
856 l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
857 close csr_evt_tree;
858
859 for assoc in csr_evt_assoc(p_self_enroll_only, p_event_id, l_offering_id, l_activity_version_id, l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4) loop
860 if (assoc.user_group_id is not null) then
861 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
862 return 'Y';
863 end if;
864 else
865 if person_matches_assoc(p_person_id, l_event_start_date, assoc.person_id, assoc.organization_id, assoc.org_structure_version_id, assoc.job_id, assoc.position_id, assoc.match_type) = true then
866 return 'Y';
867 end if;
868 end if;
869 end loop;
870
871 -- if the 4th-level parent category is not null, then there may be more parent
872 -- categories above. Use a CONNECT BY cursor.
873 if l_parent_cat_usage_id_4 is not null then
874 for assoc in csr_category_assoc(p_self_enroll_only, l_parent_cat_usage_id_4) loop
875 if (assoc.user_group_id is not null) then
876 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
877 return 'Y';
878 end if;
879 else
880 if person_matches_assoc(p_person_id, l_event_start_date, assoc.person_id, assoc.organization_id, assoc.org_structure_version_id, assoc.job_id, assoc.position_id, assoc.match_type) = true then
881 return 'Y';
882 end if;
883 end if;
884 end loop;
885 end if;
886 end if;
887
888 return 'N';
889 end emp_has_access;
890
891
892 function ext_has_access(
893 p_party_id in hz_parties.party_id%type,
894 p_event_id in ota_events.event_id%type,
895 p_self_enroll_only in varchar2) return varchar2 is
896
897 l_offering_id ota_offerings.offering_id%type;
898 l_activity_version_id ota_activity_versions.activity_version_id%type;
899 l_category_usage_id ota_category_usages.category_usage_id%type;
900 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
901 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
902 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
903 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
904 begin
905 -- Get the event's offering, course, and five levels of categories. If the category tree is deeper than that,
906 -- we will have to use CONNECT BY below.
907 open csr_evt_tree(p_event_id);
908 fetch csr_evt_tree into
909 l_offering_id, l_activity_version_id, l_category_usage_id,
910 l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
911 close csr_evt_tree;
912
913 for assoc in csr_evt_assoc_ext(p_self_enroll_only, p_party_id, p_event_id, l_offering_id, l_activity_version_id, l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4) loop
914 return 'Y';
915 end loop;
916
917 -- if the 4th-level parent category is not null, then there may be more parent
918 -- categories above. Use a CONNECT BY cursor as a last resort - it's slow.
919 if l_parent_cat_usage_id_4 is not null then
920 for assoc in csr_category_assoc_ext(p_self_enroll_only, p_party_id, l_parent_cat_usage_id_4) loop
921 return 'Y';
922 end loop;
923 end if;
924
925 return 'N';
926 end ext_has_access;
927
928 FUNCTION emp_has_valid_dates(p_person_id per_all_people_f.person_id%TYPE
929 ,p_event_id ota_events.event_id%TYPE)
930 RETURN VARCHAR2 IS
931 l_event_start_date DATE;
932 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
933 BEGIN
934
935 BEGIN
936 l_event_start_date := get_event_start_date(p_event_id, sysdate);
937 -- Added exception block for bug#5614187
938 EXCEPTION
939 when NO_DATA_FOUND then
940 l_event_start_date := trunc(sysdate);
941 END;
942
943 OPEN csr_asg_details(p_person_id, NUll, NULL, NULL, l_event_start_date);
944 FETCH csr_asg_details INTO l_assignment_id;
945 IF csr_asg_details%FOUND THEN
946 CLOSE csr_asg_details;
947 RETURN 'Y';
948 ELSE
949 CLOSE csr_asg_details;
950 RETURN 'N';
951 END IF;
952
953 END emp_has_valid_dates;
954
955
956 function employee_can_enroll(
957 p_person_id in per_people_f.person_id%type,
958 p_event_id in ota_events.event_id%type,
959 p_public_event_flag ota_events.public_event_flag%type,
960 p_max_internal in ota_events.maximum_internal_attendees%type,
961 p_event_start_date in otv_scheduled_events.course_start_date%type,
962 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
963
964 --Bug 547819
965 Cursor c_event_data is
966 Select OCU.synchronous_flag, OEV.event_status, OEV.secure_event_flag,
967 OEV.organization_id
968 From ota_events OEV, ota_offerings OFR, ota_category_usages OCU
969 Where OEV.event_id = p_event_id
970 And OEV.parent_offering_id = OFR.offering_id
971 And OFR.delivery_mode_id = OCU.category_usage_id;
972
973 l_synchronous_flag ota_category_usages.synchronous_flag%type;
974 l_effective_start_date PER_ALL_PEOPLE_F.effective_start_date%type;
975 l_event_status ota_events.event_status%type;
976 l_secure_event_flag ota_events.secure_event_flag%type;
977 l_organization_id ota_events.organization_id%type;
978 l_start_date otv_scheduled_events.course_start_date%type;
979 l_is_secure_event boolean;
980 begin
981 --Bug 547819
982 Open c_event_data;
983 Fetch c_event_data into l_synchronous_flag, l_event_status, l_secure_event_flag, l_organization_id;
984 Close c_event_data;
985
986 -- Is this a secure event?
987 l_is_secure_event := (l_secure_event_flag is not null and upper(l_secure_event_flag) = 'Y');
988
989 -- Employees can enroll if MAXIMUM_INTERNAL_ATTENDEES is null or greater than
990 -- zero and one of the following is true:
991 -- The event is secure and the employee is in the organization, or
992 -- The event is NOT secure and...
993 -- * the event is public, or
994 -- * the event is a customer event, or
995 -- * the learner has been specifically given access via
996 -- event associations.
997
998 if (p_max_internal is null or p_max_internal > 0) and
999 (
1000 (l_is_secure_event and emp_matches_org(p_person_id, p_event_id, l_organization_id) = 'Y') or
1001 (
1002 not l_is_secure_event and
1003 (
1004 (p_public_event_flag = 'Y' AND emp_has_valid_dates(p_person_id, p_event_id) = 'Y') or
1005 is_customer_event(p_event_id) or
1006 emp_has_access(p_person_id, p_event_id, 'N') = 'Y'
1007 )
1008 )
1009 ) then
1010 return 'Y';
1011 else
1012 return 'N';
1013 end if;
1014 end employee_can_enroll;
1015
1016
1017 function employee_can_self_enroll(
1018 p_person_id in per_people_f.person_id%type,
1019 p_event_id in ota_events.event_id%type,
1020 p_public_event_flag ota_events.public_event_flag%type,
1021 p_max_internal in ota_events.maximum_internal_attendees%type,
1022 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1023 begin
1024 -- Employees may not enroll if MAXIMUM_INTERNAL_ATTENDEES is zero.
1025 -- Self-enrollment can only be specified via event associations. Therefore,
1026 -- public events or customer events can never be self-enrollable.
1027 if p_max_internal <= 0 or p_public_event_flag = 'Y' or is_customer_event(p_event_id) then
1028 return 'N';
1029 end if;
1030
1031 -- Check for self-enrollment event associations
1032 return emp_has_access(p_person_id, p_event_id, 'Y');
1033 end employee_can_self_enroll;
1034
1035
1036 function ext_learner_can_enroll(
1037 p_party_id in hz_parties.party_id%type,
1038 p_event_id in ota_events.event_id%type,
1039 p_public_event_flag ota_events.public_event_flag%type,
1040 p_max_internal in ota_events.maximum_internal_attendees%type,
1041 p_event_start_date in otv_scheduled_events.course_start_date%type,
1042 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1043 begin
1044 -- External learners can enroll if the event is public, or it is a non-customer event and
1045 -- the learner has been specifically given access via event associations.
1046
1047 if p_public_event_flag = 'Y' or
1048 (not is_customer_event(p_event_id) and
1049 ext_has_access(p_party_id, p_event_id, 'N') = 'Y') then
1050 return 'Y';
1051 elsif (p_public_event_flag = 'N' and --added as the external learners should be
1052 is_customer_event(p_event_id)) then --able to enroll into private events created
1053 return 'Y'; --for the customers.bug#6327056.
1054 else
1055 return 'N';
1056 end if;
1057 end ext_learner_can_enroll;
1058
1059
1060 function ext_learner_can_self_enroll(
1061 p_party_id in hz_parties.party_id%type,
1062 p_event_id in ota_events.event_id%type,
1063 p_public_event_flag ota_events.public_event_flag%type,
1064 p_max_internal in ota_events.maximum_internal_attendees%type,
1065 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1066 begin
1067 -- Self-enrollment can only be specified via event associations. Therefore,
1068 -- public events or customer events can never be self-enrollable.
1069 if p_public_event_flag = 'Y' or is_customer_event(p_event_id) then
1070 return 'N';
1071 end if;
1072
1073 -- Check for self-enrollment event associations
1074 return ext_has_access(p_party_id, p_event_id, 'Y');
1075 end ext_learner_can_self_enroll;
1076
1077
1078 function learner_can_enroll(
1079 p_person_id in per_people_f.person_id%type,
1080 p_party_id in hz_parties.party_id%type,
1081 p_event_id in ota_events.event_id%type,
1082 p_public_event_flag in ota_events.public_event_flag%type,
1083 p_max_internal in ota_events.maximum_internal_attendees%type,
1084 p_event_start_date in otv_scheduled_events.course_start_date%type,
1085 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1086 begin
1087 if p_person_id is not null then
1088 return employee_can_enroll(p_person_id, p_event_id, p_public_event_flag, p_max_internal, p_event_start_date, p_parent_offering_id);
1089 else
1090 return ext_learner_can_enroll(p_party_id, p_event_id, p_public_event_flag, p_max_internal, p_event_start_date, p_parent_offering_id);
1091 end if;
1092 end learner_can_enroll;
1093
1094
1095 function learner_can_enroll(
1096 p_person_id in per_people_f.person_id%type,
1097 p_party_id in hz_parties.party_id%type,
1098 p_event_id in ota_events.event_id%type,
1099 p_event_start_date in otv_scheduled_events.course_start_date%type,
1100 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1101
1102 v_public_event_flag ota_events.public_event_flag%type;
1103 v_max_internal ota_events.maximum_internal_attendees%type;
1104 begin
1105 select maximum_internal_attendees, public_event_flag
1106 into v_max_internal, v_public_event_flag
1107 from ota_events
1108 where event_id = p_event_id;
1109
1110 return learner_can_enroll(p_person_id, p_party_id, p_event_id, v_public_event_flag, v_max_internal, p_event_start_date, p_parent_offering_id);
1111 end learner_can_enroll;
1112
1113
1114 function learner_can_enroll(
1115 p_person_id in per_people_f.person_id%type,
1116 p_party_id in hz_parties.party_id%type,
1117 p_event_id in ota_events.event_id%type) return varchar2 is
1118
1119 v_event_start_date ota_events.course_start_date%type;
1120 v_parent_offering_id ota_events.parent_offering_id%type;
1121 v_public_event_flag ota_events.public_event_flag%type;
1122 v_max_internal ota_events.maximum_internal_attendees%type;
1123 begin
1124 select maximum_internal_attendees, public_event_flag, parent_offering_id, course_start_date
1125 into v_max_internal, v_public_event_flag, v_parent_offering_id, v_event_start_date
1126 from ota_events
1127 where event_id = p_event_id;
1128
1129 return learner_can_enroll(p_person_id, p_party_id, p_event_id, v_public_event_flag, v_max_internal, v_event_start_date, v_parent_offering_id);
1130 end learner_can_enroll;
1131
1132
1133 function learner_can_self_enroll(
1134 p_person_id in per_people_f.person_id%type,
1135 p_party_id in hz_parties.party_id%type,
1136 p_event_id in ota_events.event_id%type,
1137 p_public_event_flag in ota_events.public_event_flag%type,
1138 p_max_internal in ota_events.maximum_internal_attendees%type,
1139 p_event_start_date in otv_scheduled_events.course_start_date%type,
1140 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1141 begin
1142 if p_person_id is not null then
1143 return employee_can_self_enroll(p_person_id, p_event_id, p_public_event_flag, p_max_internal, p_parent_offering_id);
1144 else
1145 return ext_learner_can_self_enroll(p_party_id, p_event_id, p_public_event_flag, p_max_internal, p_parent_offering_id);
1146 end if;
1147 end learner_can_self_enroll;
1148
1149
1150 function learner_can_self_enroll(
1151 p_person_id in per_people_f.person_id%type,
1152 p_party_id in hz_parties.party_id%type,
1153 p_event_id in ota_events.event_id%type,
1154 p_event_start_date in otv_scheduled_events.course_start_date%type,
1155 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1156
1157 v_public_event_flag ota_events.public_event_flag%type;
1158 v_max_internal ota_events.maximum_internal_attendees%type;
1159 begin
1160 select maximum_internal_attendees, public_event_flag
1161 into v_max_internal, v_public_event_flag
1162 from ota_events
1163 where event_id = p_event_id;
1164
1165 return learner_can_self_enroll(p_person_id, p_party_id, p_event_id, v_public_event_flag, v_max_internal, p_event_start_date, p_parent_offering_id);
1166 end learner_can_self_enroll;
1167
1168 function learner_can_self_enroll(
1169 p_person_id in per_people_f.person_id%type,
1170 p_party_id in hz_parties.party_id%type,
1171 p_event_id in ota_events.event_id%type) return varchar2 is
1172
1173 v_event_start_date ota_events.course_start_date%type;
1174 v_parent_offering_id ota_events.parent_offering_id%type;
1175 v_public_event_flag ota_events.public_event_flag%type;
1176 v_max_internal ota_events.maximum_internal_attendees%type;
1177 begin
1178 select maximum_internal_attendees, public_event_flag, parent_offering_id, course_start_date
1179 into v_max_internal, v_public_event_flag, v_parent_offering_id, v_event_start_date
1180 from ota_events
1181 where event_id = p_event_id;
1182
1183 return learner_can_self_enroll(p_person_id, p_party_id, p_event_id, v_public_event_flag, v_max_internal, v_event_start_date, v_parent_offering_id);
1184 end learner_can_self_enroll;
1185
1186
1187 function chk_delegate_ok_for_event(
1188 p_delegate_id in per_people_f.person_id%type,
1189 p_event_id in ota_events.event_id%type,
1190 p_event_start_date in otv_scheduled_events.course_start_date%type,
1191 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1192 begin
1193 return learner_can_enroll(p_delegate_id, null, p_event_id, p_event_start_date, p_parent_offering_id);
1194 end chk_delegate_ok_for_event;
1195
1196
1197 function learner_can_see_category(
1198 p_person_id in per_people_f.person_id%type,
1199 p_party_id in hz_parties.party_id%type,
1200 p_category_usage_id in ota_category_usages.category_usage_id%type) return varchar2 is
1201
1202 begin
1203 /*
1204 * MAC: stubbed out for performance.
1205 */
1206 return 'Y';
1207 end learner_can_see_category;
1208
1209
1210 function learner_can_see_course(
1211 p_person_id in per_people_f.person_id%type,
1212 p_activity_version_id in ota_activity_versions.activity_version_id%type) return varchar2 is
1213 begin
1214 return 'Y';
1215 end learner_can_see_course;
1216
1217
1218 function learner_can_see_offering(
1219 p_person_id in per_people_f.person_id%type,
1220 p_offering_id in ota_offerings.offering_id%type) return varchar2 is
1221 begin
1222 return 'Y';
1223 end learner_can_see_offering;
1224
1225
1226 function emp_has_access_to_path(
1227 p_person_id in per_people_f.person_id%type,
1228 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1229 p_self_enroll_only in varchar2) return varchar2 is
1230
1231 l_now date := sysdate;
1232 l_category_usage_id ota_category_usages.category_usage_id%type;
1233 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1234 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1235 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1236 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1237 begin
1238 -- Get the learning path's five levels of categories. If the category tree is deeper than that,
1239 -- we will have to use CONNECT BY below.
1240 open csr_lp_tree(p_learning_path_id);
1241 fetch csr_lp_tree into
1242 l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1243 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1244 close csr_lp_tree;
1245
1246 for assoc in csr_lp_assoc(p_self_enroll_only, p_learning_path_id, l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4) loop
1247 if (assoc.user_group_id is not null) then
1248 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1249 return 'Y';
1250 end if;
1251 else
1252 if person_matches_assoc(p_person_id, l_now, assoc.person_id, assoc.organization_id, assoc.org_structure_version_id, assoc.job_id, assoc.position_id, assoc.match_type) = true then
1253 return 'Y';
1254 end if;
1255 end if;
1256 end loop;
1257
1258 -- if the 4th-level parent category is not null, then there may be more parent
1259 -- categories above. Use a CONNECT BY cursor.
1260 if l_parent_cat_usage_id_4 is not null then
1261 for assoc in csr_category_assoc(p_self_enroll_only, l_parent_cat_usage_id_4) loop
1262 if (assoc.user_group_id is not null) then
1263 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1264 return 'Y';
1265 end if;
1266 else
1267 if person_matches_assoc(p_person_id, l_now, assoc.person_id, assoc.organization_id, assoc.org_structure_version_id, assoc.job_id, assoc.position_id, assoc.match_type) = true then
1268 return 'Y';
1269 end if;
1270 end if;
1271 end loop;
1272 end if;
1273
1274 return 'N';
1275 end emp_has_access_to_path;
1276
1277
1278 function ext_has_access_to_path(
1279 p_party_id in hz_parties.party_id%type,
1280 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1281 p_self_enroll_only in varchar2) return varchar2 is
1282
1283 l_category_usage_id ota_category_usages.category_usage_id%type;
1284 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1285 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1286 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1287 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1288 begin
1289 -- Get the learning path's five levels of categories. If the category tree is deeper than that,
1290 -- we will have to use CONNECT BY below.
1291 open csr_lp_tree(p_learning_path_id);
1292 fetch csr_lp_tree into
1293 l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1294 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1295 close csr_lp_tree;
1296
1297 for assoc in csr_lp_assoc_ext(p_self_enroll_only, p_party_id, p_learning_path_id, l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4) loop
1298 return 'Y';
1299 end loop;
1300
1301 -- if the 4th-level parent category is not null, then there may be more parent
1302 -- categories above. Use a CONNECT BY cursor as a last resort - it's slow.
1303 if l_parent_cat_usage_id_4 is not null then
1304 for assoc in csr_category_assoc_ext(p_self_enroll_only, p_party_id, l_parent_cat_usage_id_4) loop
1305 return 'Y';
1306 end loop;
1307 end if;
1308
1309 return 'N';
1310 end ext_has_access_to_path;
1311
1312
1313 function emp_can_enroll_in_path(
1314 p_person_id in per_people_f.person_id%type,
1315 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1316 p_public_flag ota_learning_paths.public_flag%type,
1317 p_start_date_active in ota_learning_paths.start_date_active%type,
1318 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1319 begin
1320 if p_public_flag = 'Y' then
1321 return 'Y';
1322 else
1323 return emp_has_access_to_path(p_person_id, p_learning_path_id, 'N');
1324 end if;
1325 end emp_can_enroll_in_path;
1326
1327
1328 function emp_can_self_enroll_in_path(
1329 p_person_id in per_people_f.person_id%type,
1330 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1331 p_public_flag ota_learning_paths.public_flag%type,
1332 p_start_date_active in ota_learning_paths.start_date_active%type,
1333 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1334 begin
1335 -- A public learning path disregards its learner access records. Learner access records
1336 -- are the only way an event can be made self-enrollable. Therefore, the employee cannot
1337 -- self-enroll.
1338 if p_public_flag = 'Y' then
1339 return 'N';
1340 else
1341 return emp_has_access_to_path(p_person_id, p_learning_path_id, 'Y');
1342 end if;
1343 end emp_can_self_enroll_in_path;
1344
1345
1346 function ext_can_enroll_in_path(
1347 p_party_id in hz_parties.party_id%type,
1348 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1349 p_public_flag ota_learning_paths.public_flag%type,
1350 p_start_date_active in ota_learning_paths.start_date_active%type,
1351 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1352 begin
1353 if p_public_flag = 'Y' then
1354 return 'Y';
1355 else
1356 return ext_has_access_to_path(p_party_id, p_learning_path_id, 'N');
1357 end if;
1358 end ext_can_enroll_in_path;
1359
1360
1361 function ext_can_self_enroll_in_path(
1362 p_party_id in hz_parties.party_id%type,
1363 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1364 p_public_flag ota_learning_paths.public_flag%type,
1365 p_start_date_active in ota_learning_paths.start_date_active%type,
1366 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1367 begin
1368 -- A public learning path disregards its learner access records. Learner access records
1369 -- are the only way an event can be made self-enrollable. Therefore, the ext learner cannot
1370 -- self-enroll.
1371 if p_public_flag = 'Y' then
1372 return 'N';
1373 else
1374 return ext_has_access_to_path(p_party_id, p_learning_path_id, 'Y');
1375 end if;
1376 end ext_can_self_enroll_in_path;
1377
1378
1379 function learner_can_enroll_in_path(
1380 p_person_id in per_people_f.person_id%type,
1381 p_party_id in hz_parties.party_id%type,
1382 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1383 p_public_flag ota_learning_paths.public_flag%type,
1384 p_start_date_active in ota_learning_paths.start_date_active%type,
1385 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1386 begin
1387 if p_person_id is not null then
1388 return emp_can_enroll_in_path(p_person_id, p_learning_path_id, p_public_flag, p_start_date_active, p_category_usage_id);
1389 else
1390 return ext_can_enroll_in_path(p_party_id, p_learning_path_id, p_public_flag, p_start_date_active, p_category_usage_id);
1391 end if;
1392 end learner_can_enroll_in_path;
1393
1394
1395 function learner_can_enroll_in_path(
1396 p_user_id in fnd_user.user_id%type,
1397 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1398 p_public_flag ota_learning_paths.public_flag%type,
1399 p_start_date_active in ota_learning_paths.start_date_active%type,
1400 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1401
1402 v_employee_id fnd_user.employee_id%type;
1403 v_party_id hz_parties.party_id%type;
1404 begin
1405 select employee_id, person_party_id
1406 into v_employee_id, v_party_id
1407 from fnd_user
1408 where user_id = p_user_id;
1409
1410 return learner_can_enroll_in_path(v_employee_id, v_party_id, p_learning_path_id, p_public_flag, p_start_date_active, p_category_usage_id);
1411 end learner_can_enroll_in_path;
1412
1413
1414 function lrn_can_self_enroll_in_path(
1415 p_person_id in per_people_f.person_id%type,
1416 p_party_id in hz_parties.party_id%type,
1417 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1418 p_public_flag ota_learning_paths.public_flag%type,
1419 p_start_date_active in ota_learning_paths.start_date_active%type,
1420 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1421 begin
1422 if p_person_id is not null then
1423 return emp_can_self_enroll_in_path(p_person_id, p_learning_path_id, p_public_flag, p_start_date_active, p_category_usage_id);
1424 else
1425 return ext_can_self_enroll_in_path(p_party_id, p_learning_path_id, p_public_flag, p_start_date_active, p_category_usage_id);
1426 end if;
1427 end lrn_can_self_enroll_in_path;
1428
1429
1430 function lrn_can_self_enroll_in_path(
1431 p_user_id in fnd_user.user_id%type,
1432 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1433 p_public_flag ota_learning_paths.public_flag%type,
1434 p_start_date_active in ota_learning_paths.start_date_active%type,
1435 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1436
1437 v_employee_id fnd_user.employee_id%type;
1438 v_party_id hz_parties.party_id%type;
1439 begin
1440 select employee_id, person_party_id
1441 into v_employee_id, v_party_id
1442 from fnd_user
1443 where user_id = p_user_id;
1444
1445 return lrn_can_self_enroll_in_path(v_employee_id, v_party_id, p_learning_path_id, p_public_flag, p_start_date_active, p_category_usage_id);
1446 end lrn_can_self_enroll_in_path;
1447
1448 --functions for forum(open) enrollments
1449
1450 function emp_has_access_to_forum(
1451 p_person_id in per_people_f.person_id%type,
1452 p_forum_id in ota_forums_b.forum_id%type,
1453 p_self_enroll_only in varchar2) return varchar2 is
1454
1455 l_now date := sysdate;
1456 l_object_id ota_frm_obj_inclusions.object_id%type;
1457 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1458 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1459 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1460 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1461 begin
1462 -- Get the forum's five levels of categories. If the category tree is deeper than that,
1463 -- we will have to use CONNECT BY below.
1464 open csr_forum_tree(p_forum_id);
1465 fetch csr_forum_tree into
1466 l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1467 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1468 close csr_forum_tree;
1469
1470 for assoc in csr_forum_assoc(p_self_enroll_only, p_forum_id, l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4) loop
1471 if (assoc.user_group_id is not null) then
1472 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1473 return 'Y';
1474 end if;
1475 else
1476 if person_matches_assoc(p_person_id, l_now, assoc.person_id, assoc.organization_id, assoc.org_structure_version_id, assoc.job_id, assoc.position_id, assoc.match_type) = true then
1477 return 'Y';
1478 end if;
1479 end if;
1480 end loop;
1481
1482 -- if the 4th-level parent category is not null, then there may be more parent
1483 -- categories above. Use a CONNECT BY cursor.
1484 if l_parent_cat_usage_id_4 is not null then
1485 for assoc in csr_category_assoc(p_self_enroll_only, l_parent_cat_usage_id_4) loop
1486 if (assoc.user_group_id is not null) then
1487 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1488 return 'Y';
1489 end if;
1490 else
1491 if person_matches_assoc(p_person_id, l_now, assoc.person_id, assoc.organization_id, assoc.org_structure_version_id, assoc.job_id, assoc.position_id, assoc.match_type) = true then
1492 return 'Y';
1493 end if;
1494 end if;
1495 end loop;
1496 end if;
1497
1498 return 'N';
1499 end emp_has_access_to_forum;
1500
1501
1502 function ext_has_access_to_forum(
1503 p_party_id in hz_parties.party_id%type,
1504 p_forum_id in ota_forums_b.forum_id%type,
1505 p_self_enroll_only in varchar2) return varchar2 is
1506
1507 l_object_id ota_frm_obj_inclusions.object_id%type;
1508 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1509 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1510 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1511 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1512 begin
1513 -- Get the forums's five levels of categories. If the category tree is deeper than that,
1514 -- we will have to use CONNECT BY below.
1515 open csr_forum_tree(p_forum_id);
1516 fetch csr_forum_tree into
1517 l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1518 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1519 close csr_forum_tree;
1520
1521 for assoc in csr_forum_assoc_ext(p_self_enroll_only, p_party_id, p_forum_id, l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4) loop
1522 return 'Y';
1523 end loop;
1524
1525 -- if the 4th-level parent category is not null, then there may be more parent
1526 -- categories above. Use a CONNECT BY cursor as a last resort - it's slow.
1527 if l_parent_cat_usage_id_4 is not null then
1528 for assoc in csr_category_assoc_ext(p_self_enroll_only, p_party_id, l_parent_cat_usage_id_4) loop
1529 return 'Y';
1530 end loop;
1531 end if;
1532
1533 return 'N';
1534 end ext_has_access_to_forum;
1535
1536
1537 function emp_can_enroll_in_forum(
1538 p_person_id in per_people_f.person_id%type,
1539 p_forum_id in ota_forums_b.forum_id%type,
1540 p_public_flag ota_forums_b.public_flag%type,
1541 p_start_date_active in ota_forums_b.start_date_active%type,
1542 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1543 begin
1544 if p_public_flag = 'Y' then
1545 return 'Y';
1546 else
1547 return emp_has_access_to_forum(p_person_id, p_forum_id, 'N');
1548 end if;
1549 end emp_can_enroll_in_forum;
1550
1551
1552 function emp_can_self_enroll_in_forum(
1553 p_person_id in per_people_f.person_id%type,
1554 p_forum_id in ota_forums_b.forum_id%type,
1555 p_public_flag ota_forums_b.public_flag%type,
1556 p_start_date_active in ota_forums_b.start_date_active%type,
1557 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1558 begin
1559 -- A public forum disregards its learner access records. Learner access records
1560 -- are the only way an event can be made self-enrollable. Therefore, the employee cannot
1561 -- self-enroll.
1562 if p_public_flag = 'Y' then
1563 return 'N';
1564 else
1565 return emp_has_access_to_forum(p_person_id, p_forum_id, 'Y');
1566 end if;
1567 end emp_can_self_enroll_in_forum;
1568
1569
1570 function ext_can_enroll_in_forum(
1571 p_party_id in hz_parties.party_id%type,
1572 p_forum_id in ota_forums_b.forum_id%type,
1573 p_public_flag ota_forums_b.public_flag%type,
1574 p_start_date_active in ota_forums_b.start_date_active%type,
1575 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1576 begin
1577 if p_public_flag = 'Y' then
1578 return 'Y';
1579 else
1580 return ext_has_access_to_forum(p_party_id, p_forum_id, 'N');
1581 end if;
1582 end ext_can_enroll_in_forum;
1583
1584
1585 function ext_can_self_enroll_in_forum(
1586 p_party_id in hz_parties.party_id%type,
1587 p_forum_id in ota_forums_b.forum_id%type,
1588 p_public_flag ota_forums_b.public_flag%type,
1589 p_start_date_active in ota_forums_b.start_date_active%type,
1590 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1591 begin
1592 -- A public forum disregards its learner access records. Learner access records
1593 -- are the only way an event can be made self-enrollable. Therefore, the ext learner cannot
1594 -- self-enroll.
1595 if p_public_flag = 'Y' then
1596 return 'N';
1597 else
1598 return ext_has_access_to_forum(p_party_id, p_forum_id, 'Y');
1599 end if;
1600 end ext_can_self_enroll_in_forum;
1601
1602
1603 function learner_can_enroll_in_forum(
1604 p_person_id in per_people_f.person_id%type,
1605 p_party_id in hz_parties.party_id%type,
1606 p_forum_id in ota_forums_b.forum_id%type,
1607 p_public_flag ota_forums_b.public_flag%type,
1608 p_start_date_active in ota_forums_b.start_date_active%type,
1609 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1610 begin
1611 if p_person_id is not null then
1612 return emp_can_enroll_in_forum(p_person_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1613 else
1614 return ext_can_enroll_in_forum(p_party_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1615 end if;
1616 end learner_can_enroll_in_forum;
1617
1618
1619 function learner_can_enroll_in_forum(
1620 p_user_id in fnd_user.user_id%type,
1621 p_forum_id in ota_forums_b.forum_id%type,
1622 p_public_flag ota_forums_b.public_flag%type,
1623 p_start_date_active in ota_forums_b.start_date_active%type,
1624 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1625
1626 v_employee_id fnd_user.employee_id%type;
1627 v_party_id hz_parties.party_id%type;
1628 begin
1629 select employee_id, person_party_id
1630 into v_employee_id, v_party_id
1631 from fnd_user
1632 where user_id = p_user_id;
1633
1634 return learner_can_enroll_in_forum(v_employee_id, v_party_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1635 end learner_can_enroll_in_forum;
1636
1637
1638 function lrn_can_self_enroll_in_forum(
1639 p_person_id in per_people_f.person_id%type,
1640 p_party_id in hz_parties.party_id%type,
1641 p_forum_id in ota_forums_b.forum_id%type,
1642 p_public_flag ota_forums_b.public_flag%type,
1643 p_start_date_active in ota_forums_b.start_date_active%type,
1644 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1645 begin
1646 if p_person_id is not null then
1647 return emp_can_self_enroll_in_forum(p_person_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1648 else
1649 return ext_can_self_enroll_in_forum(p_party_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1650 end if;
1651 end lrn_can_self_enroll_in_forum;
1652
1653
1654 function lrn_can_self_enroll_in_forum(
1655 p_user_id in fnd_user.user_id%type,
1656 p_forum_id in ota_forums_b.forum_id%type,
1657 p_public_flag ota_forums_b.public_flag%type,
1658 p_start_date_active in ota_forums_b.start_date_active%type,
1659 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1660
1661 v_employee_id fnd_user.employee_id%type;
1662 v_party_id hz_parties.party_id%type;
1663 begin
1664 select employee_id, person_party_id
1665 into v_employee_id, v_party_id
1666 from fnd_user
1667 where user_id = p_user_id;
1668
1669 return lrn_can_self_enroll_in_forum(v_employee_id, v_party_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1670 end lrn_can_self_enroll_in_forum;
1671 --end of utilities for forums
1672
1673 --functions for chats(open) enrollments
1674
1675 function emp_has_access_to_chat(
1676 p_person_id in per_people_f.person_id%type,
1677 p_chat_id in ota_chats_b.chat_id%type,
1678 p_self_enroll_only in varchar2) return varchar2 is
1679
1680 l_now date := sysdate;
1681 l_object_id ota_chat_obj_inclusions.object_id%type;
1682 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1683 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1684 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1685 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1686 begin
1687 -- Get the chat's five levels of categories. If the category tree is deeper than that,
1688 -- we will have to use CONNECT BY below.
1689 open csr_chat_tree(p_chat_id);
1690 fetch csr_chat_tree into
1691 l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1692 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1693 close csr_chat_tree;
1694
1695 for assoc in csr_chat_assoc(p_self_enroll_only, p_chat_id, l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4) loop
1696 if (assoc.user_group_id is not null) then
1697 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1698 return 'Y';
1699 end if;
1700 else
1701 if person_matches_assoc(p_person_id, l_now, assoc.person_id, assoc.organization_id, assoc.org_structure_version_id, assoc.job_id, assoc.position_id, assoc.match_type) = true then
1702 return 'Y';
1703 end if;
1704 end if;
1705 end loop;
1706
1707 -- if the 4th-level parent category is not null, then there may be more parent
1708 -- categories above. Use a CONNECT BY cursor.
1709 if l_parent_cat_usage_id_4 is not null then
1710 for assoc in csr_category_assoc(p_self_enroll_only, l_parent_cat_usage_id_4) loop
1711 if (assoc.user_group_id is not null) then
1712 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1713 return 'Y';
1714 end if;
1715 else
1716 if person_matches_assoc(p_person_id, l_now, assoc.person_id, assoc.organization_id, assoc.org_structure_version_id, assoc.job_id, assoc.position_id, assoc.match_type) = true then
1717 return 'Y';
1718 end if;
1719 end if;
1720 end loop;
1721 end if;
1722
1723 return 'N';
1724 end emp_has_access_to_chat;
1725
1726
1727 function ext_has_access_to_chat(
1728 p_party_id in hz_parties.party_id%type,
1729 p_chat_id in ota_chats_b.chat_id%type,
1730 p_self_enroll_only in varchar2) return varchar2 is
1731
1732 l_object_id ota_chat_obj_inclusions.object_id%type;
1733 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1734 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1735 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1736 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1737 begin
1738 -- Get the chat's five levels of categories. If the category tree is deeper than that,
1739 -- we will have to use CONNECT BY below.
1740 open csr_chat_tree(p_chat_id);
1741 fetch csr_chat_tree into
1742 l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1743 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1744 close csr_chat_tree;
1745
1746 for assoc in csr_chat_assoc_ext(p_self_enroll_only, p_party_id, p_chat_id, l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4) loop
1747 return 'Y';
1748 end loop;
1749
1750 -- if the 4th-level parent category is not null, then there may be more parent
1751 -- categories above. Use a CONNECT BY cursor as a last resort - it's slow.
1752 if l_parent_cat_usage_id_4 is not null then
1753 for assoc in csr_category_assoc_ext(p_self_enroll_only, p_party_id, l_parent_cat_usage_id_4) loop
1754 return 'Y';
1755 end loop;
1756 end if;
1757
1758 return 'N';
1759 end ext_has_access_to_chat;
1760
1761
1762 function emp_can_enroll_in_chat(
1763 p_person_id in per_people_f.person_id%type,
1764 p_chat_id in ota_chats_b.chat_id%type,
1765 p_public_flag ota_chats_b.public_flag%type,
1766 p_start_date_active in ota_chats_b.start_date_active%type,
1767 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1768 begin
1769 if p_public_flag = 'Y' then
1770 return 'Y';
1771 else
1772 return emp_has_access_to_chat(p_person_id, p_chat_id, 'N');
1773 end if;
1774 end emp_can_enroll_in_chat;
1775
1776
1777 function emp_can_self_enroll_in_chat(
1778 p_person_id in per_people_f.person_id%type,
1779 p_chat_id in ota_chats_b.chat_id%type,
1780 p_public_flag ota_chats_b.public_flag%type,
1781 p_start_date_active in ota_chats_b.start_date_active%type,
1782 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1783 begin
1784 -- A public chat disregards its learner access records. Learner access records
1785 -- are the only way an event can be made self-enrollable. Therefore, the employee cannot
1786 -- self-enroll.
1787 if p_public_flag = 'Y' then
1788 return 'N';
1789 else
1790 return emp_has_access_to_chat(p_person_id, p_chat_id, 'Y');
1791 end if;
1792 end emp_can_self_enroll_in_chat;
1793
1794
1795 function ext_can_enroll_in_chat(
1796 p_party_id in hz_parties.party_id%type,
1797 p_chat_id in ota_chats_b.chat_id%type,
1798 p_public_flag ota_chats_b.public_flag%type,
1799 p_start_date_active in ota_chats_b.start_date_active%type,
1800 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1801 begin
1802 if p_public_flag = 'Y' then
1803 return 'Y';
1804 else
1805 return ext_has_access_to_chat(p_party_id, p_chat_id, 'N');
1806 end if;
1807 end ext_can_enroll_in_chat;
1808
1809
1810 function ext_can_self_enroll_in_chat(
1811 p_party_id in hz_parties.party_id%type,
1812 p_chat_id in ota_chats_b.chat_id%type,
1813 p_public_flag ota_chats_b.public_flag%type,
1814 p_start_date_active in ota_chats_b.start_date_active%type,
1815 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1816 begin
1817 -- A public chat disregards its learner access records. Learner access records
1818 -- are the only way an event can be made self-enrollable. Therefore, the ext learner cannot
1819 -- self-enroll.
1820 if p_public_flag = 'Y' then
1821 return 'N';
1822 else
1823 return ext_has_access_to_chat(p_party_id, p_chat_id, 'Y');
1824 end if;
1825 end ext_can_self_enroll_in_chat;
1826
1827
1828 function learner_can_enroll_in_chat(
1829 p_person_id in per_people_f.person_id%type,
1830 p_party_id in hz_parties.party_id%type,
1831 p_chat_id in ota_chats_b.chat_id%type,
1832 p_public_flag ota_chats_b.public_flag%type,
1833 p_start_date_active in ota_chats_b.start_date_active%type,
1834 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1835 begin
1836 if p_person_id is not null then
1837 return emp_can_enroll_in_chat(p_person_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1838 else
1839 return ext_can_enroll_in_chat(p_party_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1840 end if;
1841 end learner_can_enroll_in_chat;
1842
1843
1844 function learner_can_enroll_in_chat(
1845 p_user_id in fnd_user.user_id%type,
1846 p_chat_id in ota_chats_b.chat_id%type,
1847 p_public_flag ota_chats_b.public_flag%type,
1848 p_start_date_active in ota_chats_b.start_date_active%type,
1849 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1850
1851 v_employee_id fnd_user.employee_id%type;
1852 v_party_id hz_parties.party_id%type;
1853 begin
1854 select employee_id, person_party_id
1855 into v_employee_id, v_party_id
1856 from fnd_user
1857 where user_id = p_user_id;
1858
1859 return learner_can_enroll_in_chat(v_employee_id, v_party_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1860 end learner_can_enroll_in_chat;
1861
1862
1863 function lrn_can_self_enroll_in_chat(
1864 p_person_id in per_people_f.person_id%type,
1865 p_party_id in hz_parties.party_id%type,
1866 p_chat_id in ota_chats_b.chat_id%type,
1867 p_public_flag ota_chats_b.public_flag%type,
1868 p_start_date_active in ota_chats_b.start_date_active%type,
1869 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1870 begin
1871 if p_person_id is not null then
1872 return emp_can_self_enroll_in_chat(p_person_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1873 else
1874 return ext_can_self_enroll_in_chat(p_party_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1875 end if;
1876 end lrn_can_self_enroll_in_chat;
1877
1878
1879 function lrn_can_self_enroll_in_chat(
1880 p_user_id in fnd_user.user_id%type,
1881 p_chat_id in ota_chats_b.chat_id%type,
1882 p_public_flag ota_chats_b.public_flag%type,
1883 p_start_date_active in ota_chats_b.start_date_active%type,
1884 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1885
1886 v_employee_id fnd_user.employee_id%type;
1887 v_party_id hz_parties.party_id%type;
1888 begin
1889 select employee_id, person_party_id
1890 into v_employee_id, v_party_id
1891 from fnd_user
1892 where user_id = p_user_id;
1893
1894 return lrn_can_self_enroll_in_chat(v_employee_id, v_party_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1895 end lrn_can_self_enroll_in_chat;
1896 --end of utilities for chats
1897
1898
1899 function emp_has_access_to_cert(
1900 p_person_id in per_people_f.person_id%type,
1901 p_certification_id in ota_certifications_b.certification_id%type,
1902 p_self_enroll_only in varchar2) return varchar2 is
1903
1904 l_now date := sysdate;
1905 l_category_usage_id ota_category_usages.category_usage_id%type;
1906 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1907 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1908 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1909 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1910 begin
1911 -- Get the certification's five levels of categories. If the category tree is deeper than that,
1912 -- we will have to use CONNECT BY below.
1913 open csr_cert_tree(p_certification_id);
1914 fetch csr_cert_tree into
1915 l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1916 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1917 close csr_cert_tree;
1918
1919 for assoc in csr_cert_assoc(p_self_enroll_only, p_certification_id, l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4) loop
1920 if (assoc.user_group_id is not null) then
1921 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1922 return 'Y';
1923 end if;
1924 else
1925 if person_matches_assoc(p_person_id, l_now, assoc.person_id, assoc.organization_id, assoc.org_structure_version_id, assoc.job_id, assoc.position_id, assoc.match_type) = true then
1926 return 'Y';
1927 end if;
1928 end if;
1929 end loop;
1930
1931 -- if the 4th-level parent category is not null, then there may be more parent
1932 -- categories above. Use a CONNECT BY cursor.
1933 if l_parent_cat_usage_id_4 is not null then
1934 for assoc in csr_category_assoc(p_self_enroll_only, l_parent_cat_usage_id_4) loop
1935 if (assoc.user_group_id is not null) then
1936 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1937 return 'Y';
1938 end if;
1939 else
1940 if person_matches_assoc(p_person_id, l_now, assoc.person_id, assoc.organization_id, assoc.org_structure_version_id, assoc.job_id, assoc.position_id, assoc.match_type) = true then
1941 return 'Y';
1942 end if;
1943 end if;
1944 end loop;
1945 end if;
1946
1947 return 'N';
1948 end emp_has_access_to_cert;
1949
1950
1951 function ext_has_access_to_cert(
1952 p_party_id in hz_parties.party_id%type,
1953 p_certification_id in ota_certifications_b.certification_id%type,
1954 p_self_enroll_only in varchar2) return varchar2 is
1955
1956 l_category_usage_id ota_category_usages.category_usage_id%type;
1957 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1958 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1959 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1960 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1961 begin
1962 -- Get the certification's five levels of categories. If the category tree is deeper than that,
1963 -- we will have to use CONNECT BY below.
1964 open csr_cert_tree(p_certification_id);
1965 fetch csr_cert_tree into
1966 l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1967 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1968 close csr_cert_tree;
1969
1970 for assoc in csr_cert_assoc_ext(p_self_enroll_only, p_party_id, p_certification_id, l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4) loop
1971 return 'Y';
1972 end loop;
1973
1974 -- if the 4th-level parent category is not null, then there may be more parent
1975 -- categories above. Use a CONNECT BY cursor as a last resort - it's slow.
1976 if l_parent_cat_usage_id_4 is not null then
1977 for assoc in csr_category_assoc_ext(p_self_enroll_only, p_party_id, l_parent_cat_usage_id_4) loop
1978 return 'Y';
1979 end loop;
1980 end if;
1981
1982 return 'N';
1983 end ext_has_access_to_cert;
1984
1985
1986 function emp_can_enroll_in_cert(
1987 p_person_id in per_people_f.person_id%type,
1988 p_certification_id in ota_certifications_b.certification_id%type,
1989 p_public_flag ota_certifications_b.public_flag%type,
1990 p_start_date_active in ota_certifications_b.start_date_active%type,
1991 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1992 begin
1993 if p_public_flag = 'Y' then
1994 return 'Y';
1995 else
1996 return emp_has_access_to_cert(p_person_id, p_certification_id, 'N');
1997 end if;
1998 end emp_can_enroll_in_cert;
1999
2000
2001 function emp_can_self_enroll_in_cert(
2002 p_person_id in per_people_f.person_id%type,
2003 p_certification_id in ota_certifications_b.certification_id%type,
2004 p_public_flag ota_certifications_b.public_flag%type,
2005 p_start_date_active in ota_certifications_b.start_date_active%type,
2006 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2007 begin
2008 -- A public certification disregards its learner access records. Learner access records
2009 -- are the only way a certification can be made self-enrollable. Therefore, the employee cannot
2010 -- self-enroll.
2011 if p_public_flag = 'Y' then
2012 return 'N';
2013 else
2014 return emp_has_access_to_cert(p_person_id, p_certification_id, 'Y');
2015 end if;
2016 end emp_can_self_enroll_in_cert;
2017
2018
2019 function ext_can_enroll_in_cert(
2020 p_party_id in hz_parties.party_id%type,
2021 p_certification_id in ota_certifications_b.certification_id%type,
2022 p_public_flag ota_certifications_b.public_flag%type,
2023 p_start_date_active in ota_certifications_b.start_date_active%type,
2024 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2025 begin
2026 if p_public_flag = 'Y' then
2027 return 'Y';
2028 else
2029 return ext_has_access_to_cert(p_party_id, p_certification_id, 'N');
2030 end if;
2031 end ext_can_enroll_in_cert;
2032
2033
2034 function ext_can_self_enroll_in_cert(
2035 p_party_id in hz_parties.party_id%type,
2036 p_certification_id in ota_certifications_b.certification_id%type,
2037 p_public_flag ota_certifications_b.public_flag%type,
2038 p_start_date_active in ota_certifications_b.start_date_active%type,
2039 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2040 begin
2041 -- A public certification disregards its learner access records. Learner access records
2042 -- are the only way a certification can be made self-enrollable. Therefore, the ext learner cannot
2043 -- self-enroll.
2044 if p_public_flag = 'Y' then
2045 return 'N';
2046 else
2047 return ext_has_access_to_cert(p_party_id, p_certification_id, 'Y');
2048 end if;
2049 end ext_can_self_enroll_in_cert;
2050
2051
2052 function learner_can_enroll_in_cert(
2053 p_person_id in per_people_f.person_id%type,
2054 p_party_id in hz_parties.party_id%type,
2055 p_certification_id in ota_certifications_b.certification_id%type,
2056 p_public_flag ota_certifications_b.public_flag%type,
2057 p_start_date_active in ota_certifications_b.start_date_active%type,
2058 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2059 begin
2060 if p_person_id is not null then
2061 return emp_can_enroll_in_cert(p_person_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2062 else
2063 return ext_can_enroll_in_cert(p_party_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2064 end if;
2065 end learner_can_enroll_in_cert;
2066
2067
2068 function learner_can_enroll_in_cert(
2069 p_user_id in fnd_user.user_id%type,
2070 p_certification_id in ota_certifications_b.certification_id%type,
2071 p_public_flag ota_certifications_b.public_flag%type,
2072 p_start_date_active in ota_certifications_b.start_date_active%type,
2073 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2074
2075 v_employee_id fnd_user.employee_id%type;
2076 v_party_id hz_parties.party_id%type;
2077 begin
2078 select employee_id, person_party_id
2079 into v_employee_id, v_party_id
2080 from fnd_user
2081 where user_id = p_user_id;
2082
2083 return learner_can_enroll_in_cert(v_employee_id, v_party_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2084 end learner_can_enroll_in_cert;
2085
2086
2087 function lrn_can_self_enroll_in_cert(
2088 p_person_id in per_people_f.person_id%type,
2089 p_party_id in hz_parties.party_id%type,
2090 p_certification_id in ota_certifications_b.certification_id%type,
2091 p_public_flag ota_certifications_b.public_flag%type,
2092 p_start_date_active in ota_certifications_b.start_date_active%type,
2093 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2094 begin
2095 if p_person_id is not null then
2096 return emp_can_self_enroll_in_cert(p_person_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2097 else
2098 return ext_can_self_enroll_in_cert(p_party_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2099 end if;
2100 end lrn_can_self_enroll_in_cert;
2101
2102
2103 function lrn_can_self_enroll_in_cert(
2104 p_user_id in fnd_user.user_id%type,
2105 p_certification_id in ota_certifications_b.certification_id%type,
2106 p_public_flag ota_certifications_b.public_flag%type,
2107 p_start_date_active in ota_certifications_b.start_date_active%type,
2108 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2109
2110 v_employee_id fnd_user.employee_id%type;
2111 v_party_id hz_parties.party_id%type;
2112 begin
2113 select employee_id, person_party_id
2114 into v_employee_id, v_party_id
2115 from fnd_user
2116 where user_id = p_user_id;
2117
2118 return lrn_can_self_enroll_in_cert(v_employee_id, v_party_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2119 end lrn_can_self_enroll_in_cert;
2120
2121 function learner_can_enroll_in_path(
2122 p_learning_path_id in ota_learning_paths.learning_path_id%TYPE
2123 ,p_person_id in per_all_people_f.person_id%type
2124 ,p_party_id in hz_parties.party_id%type) return varchar2
2125 IS
2126 CURSOR csr_get_lp_details IS
2127 SELECT public_flag, start_date_active
2128 FROM ota_learning_paths
2129 WHERE learning_path_id = p_learning_path_id;
2130
2131 l_start_date_active ota_learning_paths.start_date_active%TYPE;
2132 l_public_flag ota_learning_paths.public_flag%TYPE;
2133 BEGIN
2134 OPEN csr_get_lp_details;
2135 FETCH csr_get_lp_details INTO l_public_flag, l_start_date_active ;
2136 IF csr_get_lp_details%NOTFOUND THEN
2137 CLOSE csr_get_lp_details;
2138 RETURN 'N';
2139 ELSE
2140 CLOSE csr_get_lp_details;
2141 RETURN learner_can_enroll_in_path(p_person_id => p_person_id
2142 , p_party_id => p_party_id
2143 , p_learning_path_id => p_learning_path_id
2144 , p_public_flag => l_public_flag
2145 , p_start_date_active => l_start_date_active);
2146 END IF;
2147
2148 END learner_can_enroll_in_path;
2149
2150 function learner_can_enroll_in_cert(
2151 p_certification_id in ota_certifications_b.certification_id%type
2152 ,p_person_id in per_all_people_f.person_id%type
2153 ,p_party_id in hz_parties.party_id%type) return varchar2
2154 IS
2155 CURSOR csr_get_cert_details IS
2156 SELECT public_flag, start_date_active
2157 FROM ota_certifications_b
2158 WHERE certification_id = p_certification_id;
2159
2160 l_start_date_active ota_certifications_b.start_date_active%TYPE;
2161 l_public_flag ota_certifications_b.public_flag%TYPE;
2162 BEGIN
2163 OPEN csr_get_cert_details;
2164 FETCH csr_get_cert_details INTO l_public_flag, l_start_date_active ;
2165 IF csr_get_cert_details%NOTFOUND THEN
2166 CLOSE csr_get_cert_details;
2167 RETURN 'N';
2168 ELSE
2169 CLOSE csr_get_cert_details;
2170 RETURN learner_can_enroll_in_cert(p_person_id => p_person_id
2171 , p_party_id => p_party_id
2172 , p_certification_id => p_certification_id
2173 , p_public_flag => l_public_flag
2174 , p_start_date_active => l_start_date_active);
2175 END IF;
2176 END learner_can_enroll_in_cert;
2177
2178 function learner_has_access_to_course(
2179 p_person_id in per_people_f.person_id%type,
2180 p_party_id in hz_parties.party_id%type,
2181 p_activity_version_id in ota_activity_versions.activity_version_id%type) return varchar2 is
2182
2183 begin
2184
2185 for assoc in csr_classes_in_course(p_activity_version_id) loop
2186 if learner_can_enroll(p_person_id, p_party_id, assoc.event_id, assoc.public_event_flag, assoc.maximum_internal_attendees, assoc.course_start_date) = 'Y' then
2187 return 'Y';
2188 end if;
2189 end loop;
2190
2191 return 'N';
2192 end learner_has_access_to_course;
2193
2194
2195 /*
2196 Always use this procedure to check learner access for a person as this
2197 taking care the whole hierarchy for a user group.
2198 If there is any change in this procedure make sure, the changes are
2199 done in the procedure get_ug_whereclause
2200 */
2201 function is_learner_in_user_group(
2202 p_person_id in per_people_f.person_id%type,
2203 p_user_group_id in ota_user_groups_b.user_group_id%type,
2204 p_business_group_id number) return varchar2 is
2205
2206 is_avail BOOLEAN default false;
2207 ug_index NUMBER := 1;
2208 begin
2209
2210 for ug_hierarchy in csr_ug_hierarchy(p_user_group_id, p_business_group_id) loop
2211
2212 is_avail := is_learner_matches_user_group(p_person_id, ug_hierarchy.child_user_group_id, p_business_group_id);
2213
2214 person_ug_map_rec_table(ug_index).person_id := p_person_id;
2215 person_ug_map_rec_table(ug_index).user_group_id := ug_hierarchy.child_user_group_id;
2216 person_ug_map_rec_table(ug_index).is_person_matches_ug := is_avail;
2217
2218 ug_index := ug_index+1;
2219
2220 end loop; --each user group in the hierarchy has been filled with true or false
2221
2222 --process for root
2223 is_avail := is_learner_matches_user_group(p_person_id, p_user_group_id, p_business_group_id);
2224
2225 person_ug_map_rec_table(ug_index).person_id := p_person_id;
2226 person_ug_map_rec_table(ug_index).user_group_id := p_user_group_id;
2227 person_ug_map_rec_table(ug_index).is_person_matches_ug := is_avail;
2228
2229 if(is_avail = true) then
2230 return 'Y';
2231 else
2232 return 'N';
2233 end if;
2234
2235 end is_learner_in_user_group;
2236
2237 function is_learner_matches_user_group(
2238 p_person_id in per_people_f.person_id%type,
2239 p_user_group_id in ota_user_groups_b.user_group_id%type,
2240 p_business_group_id number) return boolean is
2241
2242 l_user_group_operator ota_user_groups_b.user_group_operator%type;
2243 l_person_id per_people_f.person_id%type;
2244 l_user_group_id ota_user_groups_b.user_group_id%type;
2245 l_is_avail BOOLEAN default false;
2246
2247 begin
2248 for elements in csr_user_group_elements(p_user_group_id, p_business_group_id) loop
2249
2250 l_user_group_operator := elements.user_group_operator;
2251
2252 if elements.user_group_operator is not null and elements.user_group_operator = 'AND' then
2253 if elements.person_id is not null and elements.person_id <> p_person_id then
2254 return false;
2255 else
2256 if elements.child_user_group_id is not null then
2257
2258 for i in 1 .. person_ug_map_rec_table.count loop
2259 l_person_id := person_ug_map_rec_table(i).person_id;
2260 l_user_group_id := person_ug_map_rec_table(i).user_group_id;
2261 l_is_avail := person_ug_map_rec_table(i).is_person_matches_ug;
2262
2263 if( l_person_id is not null and l_person_id = p_person_id and
2264 l_user_group_id is not null and l_user_group_id = elements.child_user_group_id and
2265 l_is_avail is not null and l_is_avail = false) then
2266
2267 return false;
2268 end if;
2269 end loop;
2270 else
2271 if person_matches_assoc(p_person_id, sysdate, elements.person_id, elements.organization_id, elements.org_structure_version_id, elements.job_id, elements.position_id, elements.match_type) = false then
2272 return false;
2273 end if;
2274 end if;
2275 end if;
2276 else
2277 if elements.person_id is not null and elements.person_id = p_person_id then
2278 return true;
2279 else
2280 if elements.child_user_group_id is not null then
2281
2282 for i in 1 .. person_ug_map_rec_table.count loop
2283 l_person_id := person_ug_map_rec_table(i).person_id;
2284 l_user_group_id := person_ug_map_rec_table(i).user_group_id;
2285 l_is_avail := person_ug_map_rec_table(i).is_person_matches_ug;
2286
2287 if( l_person_id is not null and l_person_id = p_person_id and
2288 l_user_group_id is not null and l_user_group_id = elements.child_user_group_id and
2289 l_is_avail is not null and l_is_avail = true) then
2290
2291 return true;
2292 end if;
2293 end loop;
2294 else
2295 if person_matches_assoc(p_person_id, sysdate, elements.person_id, elements.organization_id, elements.org_structure_version_id, elements.job_id, elements.position_id, elements.match_type) = true then
2296 return true;
2297 end if;
2298 end if;
2299 end if;
2300 end if;
2301 end loop;
2302
2303 if l_user_group_operator is not null and l_user_group_operator = 'AND' then
2304 return true;
2305 else
2306 return false;
2307 end if;
2308
2309 end is_learner_matches_user_group;
2310
2311 /*
2312 Always use this procedure to get where clause for a user group as this
2313 taking care the whole hierarchy for a user group.
2314 If there is any change in this procedure make sure, the changes are
2315 done in the procedure is_learner_in_user_group
2316 */
2317 function get_ug_whereclause(
2318 p_user_group_id in ota_user_groups_b.user_group_id%type,
2319 p_business_group_id number) return clob is
2320
2321 whereclause clob;
2322 ug_index NUMBER := 1;
2323 begin
2324
2325 for ug_hierarchy in csr_ug_hierarchy(p_user_group_id, p_business_group_id) loop
2326
2327 whereclause := build_ug_whereclause(ug_hierarchy.child_user_group_id, p_business_group_id);
2328
2329 t_ug_learner_list_table(ug_index).user_group_id := ug_hierarchy.child_user_group_id;
2330 t_ug_learner_list_table(ug_index).ugwhereclause := whereclause;
2331
2332 ug_index := ug_index+1;
2333
2334 end loop; --each user group in the hierarchy has been filled with where clause
2335
2336 --process root
2337 whereclause := build_ug_whereclause(p_user_group_id, p_business_group_id);
2338 t_ug_learner_list_table(ug_index).user_group_id := p_user_group_id;
2339 t_ug_learner_list_table(ug_index).ugwhereclause := whereclause;
2340
2341 if (whereclause is null) then -- Bug#6835942
2342 whereclause := ' (1=2) ';
2343 end if;
2344
2345 return whereclause;
2346 end get_ug_whereclause;
2347
2348 function build_ug_whereclause(
2349 p_user_group_id in ota_user_groups_b.user_group_id%type,
2350 p_business_group_id number) return clob is
2351
2352 l_user_group_operator ota_user_groups_b.user_group_operator%type;
2353 l_user_group_id ota_user_groups_b.user_group_id%type;
2354 whereclause clob;
2355 childugwhereclause clob;
2356 ugOperatorCheckCount number := 0;
2357 orgHierarchyCount number := 0;
2358
2359 begin
2360 for elements in csr_user_group_elements(p_user_group_id, p_business_group_id) loop
2361
2362 l_user_group_operator := elements.user_group_operator;
2363
2364 if (ugOperatorCheckCount = 0) then
2365 whereclause := ' ( ';
2366 end if;
2367
2368 if (ugOperatorCheckCount > 0 and length(trim(whereclause)) > 1 and elements.child_user_group_id is null) then
2369 whereclause := whereclause || ' ' || l_user_group_operator || ' ';
2370 end if;
2371
2372 if elements.person_id is not null then --person
2373 whereclause := whereclause || ' person_id = ' || elements.person_id || ' ';
2374 else --child user group
2375 if elements.child_user_group_id is not null then --
2376 for i in 1 .. t_ug_learner_list_table.count loop
2377 l_user_group_id := t_ug_learner_list_table(i).user_group_id;
2378 childugwhereclause := t_ug_learner_list_table(i).ugwhereclause;
2379
2380 if( l_user_group_id is not null and l_user_group_id = elements.child_user_group_id and whereclause is not null ) then
2381 if(ugOperatorCheckCount > 0 and length(trim(whereclause)) > 1 and childugwhereclause is not null) then
2382 whereclause := whereclause || ' ' || l_user_group_operator || ' ';
2383 end if;
2384 whereclause := whereclause || ' ' || childugwhereclause || ' ';
2385 exit;
2386 end if;
2387 end loop;
2388 else --Assignment
2389 whereclause := whereclause || ' ( ' ;
2390
2391 if(elements.job_id is not null) then
2392 whereclause := whereclause || ' job_id = ' || elements.job_id;
2393 if(elements.position_id is not null or elements.organization_id is not null) then
2394 whereclause := whereclause || ' and ';
2395 end if;
2396 end if;
2397
2398 if(elements.position_id is not null) then
2399 whereclause := whereclause || ' position_id = ' || elements.position_id;
2400 if(elements.organization_id is not null) then
2401 whereclause := whereclause || ' and ';
2402 end if;
2403 end if;
2404
2405 if(elements.organization_id is not null) then
2406 if(elements.org_structure_version_id is null) then
2407 whereclause := whereclause || ' organization_id = ' || elements.organization_id;
2408 else
2409 orgHierarchyCount := 0;
2410 whereclause := whereclause || ' organization_id in ( ';
2411 for orghierarchy in csr_org_hierarchy(elements.organization_id, elements.org_structure_version_id) loop
2412 if(orgHierarchyCount > 0) then
2413 whereclause := whereclause || ' , ';
2414 end if;
2415 whereclause := whereclause || orghierarchy.organization_id;
2416 orgHierarchyCount := orgHierarchyCount+1;
2417 end loop;
2418 whereclause := whereclause || ' ) ';
2419 end if;
2420 end if;
2421
2422 whereclause := whereclause || ' ) ' ;
2423
2424 end if;
2425 end if;
2426 ugOperatorCheckCount := ugOperatorCheckCount+1;
2427 end loop;
2428
2429 if (ugOperatorCheckCount > 0) then
2430 if (whereclause is not null and length(trim(whereclause)) > 1) then -- Bug#6835942
2431 whereclause := whereclause || ' ) ';
2432 else
2433 whereclause := null;
2434 end if;
2435 end if;
2436
2437 return whereclause;
2438
2439 end build_ug_whereclause;
2440
2441 function is_full_access_learner_group(p_user_group_id in ota_user_groups_b.user_group_id%type,
2442 p_business_group_id number) return varchar2 is
2443
2444 total_elements_count NUMBER := -2;
2445 restricted_elements_count NUMBER := -1;
2446
2447 BEGIN
2448
2449 SELECT count(user_group_element_id) into total_elements_count
2450 FROM OTA_USER_GROUP_ELEMENTS
2451 WHERE user_group_id in ( SELECT child_user_group_id
2452 FROM ( SELECT a.child_user_group_id
2453 FROM ota_user_group_elements a,
2454 ota_user_groups_b b
2455 WHERE a.user_group_id = b.user_group_id
2456 and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate)) and
2457 trunc(nvl(b.end_date_active, sysdate+1))
2458 START WITH a.user_group_id = p_user_group_id
2459 CONNECT BY PRIOR a.child_user_group_id = a.user_group_id
2460 UNION ALL SELECT p_user_group_id FROM dual
2461 ) WHERE child_user_group_id is not null ) and
2462 (fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID') is not null
2463 or business_group_id = p_business_group_id);
2464
2465 SELECT count(a.user_group_element_id) into restricted_elements_count
2466 FROM OTA_USER_GROUP_ELEMENTS a,
2467 PER_PEOPLE_F e,
2468 PER_JOBS_VL job,
2469 HR_ORGANIZATION_UNITS org,
2470 PER_POSITIONS pos
2471 WHERE a.user_group_id in ( SELECT child_user_group_id
2472 FROM ( SELECT a.child_user_group_id
2473 FROM ota_user_group_elements a,
2474 ota_user_groups_b b
2475 WHERE a.user_group_id = b.user_group_id
2476 and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate)) and
2477 trunc(nvl(b.end_date_active, sysdate+1))
2478 START WITH a.user_group_id = p_user_group_id
2479 CONNECT BY PRIOR a.child_user_group_id = a.user_group_id
2480 UNION ALL SELECT p_user_group_id FROM dual
2481 ) WHERE child_user_group_id is not null ) and
2482 e.person_id(+) = a.person_id and
2483 (e.effective_start_date is null or e.effective_start_date <= trunc(sysdate)) and
2484 (e.effective_end_date is null or trunc(sysdate) <= e.effective_end_date) and
2485 job.job_id(+) = a.job_id and
2486 org.organization_id(+) = a.organization_id and
2487 pos.position_id(+) = a.position_id and
2488 (fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID') is not null or
2489 a.business_group_id = p_business_group_id) and
2490 -- decode (a.position_id, null, -1, pos.organization_id) = nvl(org.organization_id,-1) and --7157831
2491 decode (a.position_id, null, -1, pos.organization_id) = decode(a.position_id, null, -1, org.organization_id) and --7248298
2492 (e.person_id is not null or job.job_id is not null or pos.position_id is not null or
2493 org.organization_id is not null or a.child_user_group_id is not null);
2494
2495 if (total_elements_count = 0) then --7157831
2496 return 'N';
2497 else
2498 if( (total_elements_count - restricted_elements_count) = 0) then
2499 return 'Y';
2500 else
2501 return 'N';
2502 end if;
2503 end if;
2504 end is_full_access_learner_group;
2505
2506 end ota_learner_access_util;
[Home] [Help]