1 package body ota_learner_access_util as
2 /* $Header: otlrnacc.pkb 120.40.12020000.2 2013/02/13 13:38:40 atadepal ship $ */
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_ug_hierarchy(
531 p_user_group_id in ota_user_groups_b.user_group_id%type,
532 p_business_group_id number,
533 p_ignore_ug_date_check varchar2 default 'N') is
534 SELECT child_user_group_id
535 FROM ( SELECT a.user_group_id user_group_id,
536 a.child_user_group_id child_user_group_id,
537 level UG_Level,
538 b.user_group_operator user_group_operator
539 FROM ota_user_group_elements a, ota_user_groups_b b
540 WHERE a.user_group_id = b.user_group_id
541 and (p_ignore_ug_date_check = 'Y' or
542 trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate)) and trunc(nvl(b.end_date_active, sysdate+1)))
543 and (b.business_group_id = p_business_group_id or p_business_group_id = -1)
544 START WITH a.user_group_id = p_user_group_id
545 CONNECT BY PRIOR a.child_user_group_id = a.user_group_id
546 ORDER by LEVEL desc
547 ) WHERE child_user_group_id is not null;
548
549 cursor csr_org_hierarchy(p_organization_id ota_user_group_elements.organization_id%type,
550 p_org_structure_version_id ota_user_group_elements.org_structure_version_id%type) is
551 select p_organization_id as organization_id
552 from dual
553 union all
554 select x.sub_organization_id as organization_id
555 from per_org_hrchy_summary x,
556 per_org_structure_versions v,
557 per_org_structure_versions currv
558 where v.org_structure_version_id = p_org_structure_version_id and
559 v.organization_structure_id = currv.organization_structure_id and
560 (currv.date_to is null or sysdate between currv.date_from and currv.date_to) and
561 x.organization_structure_id = currv.organization_structure_id and
562 x.org_structure_version_id = currv.org_structure_version_id and
563 x.organization_id = p_organization_id and
564 x.sub_org_relative_level > 0;
565
566 cursor csr_user_group_elements(
567 p_user_group_id in ota_user_groups_b.user_group_id%type,
568 p_business_group_id number,
569 p_ignore_ug_date_check varchar2 default 'N') is
570 select uge.organization_id,
571 uge.org_structure_version_id,
572 uge.job_id,
573 uge.position_id,
574 uge.person_id,
575 uge.match_type,
576 uge.child_user_group_id,
577 ugb.user_group_operator,
578 uge.elig_prfl_id,
579 uge.user_group_id
580 from ota_user_group_elements uge, ota_user_groups_b ugb
581 where uge.user_group_id = p_user_group_id
582 and uge.user_group_id = ugb.user_group_id
583 and (p_ignore_ug_date_check = 'Y' or
584 trunc(sysdate) between trunc(nvl(ugb.start_date_active, sysdate)) and trunc(nvl(ugb.end_date_active, sysdate+1))) --Bug#7120108
585 and (uge.business_group_id = p_business_group_id or p_business_group_id = -1)
586 and (uge.person_id is null OR uge.elig_prfl_id is null);
587
588 function is_customer_event(p_event_id in ota_events.event_id%type) return boolean is
589
590 cursor customer_assoc is
591 select 1
592 from ota_event_associations
593 where event_id = p_event_id and
594 customer_id is not null;
595
596 v_dummy number;
597 v_result boolean;
598 begin
599 open customer_assoc;
600 fetch customer_assoc into v_dummy;
601 v_result := customer_assoc%found;
602 close customer_assoc;
603 return v_result;
604 end is_customer_event;
605
606
607 function get_event_start_date(
608 p_event_id ota_events.event_id%type,
609 p_date date) return date is
610
611 l_event_start_date ota_events.course_start_date%type;
612 --l_date date := trunc(p_date);
613
614 l_start_date ota_events.course_start_date%type;
615 l_synchronous_flag ota_category_usages.synchronous_flag%type;
616 l_event_status ota_events.event_status%type;
617 l_event_end_date ota_events.course_end_date%type;
618 l_event_end_time ota_events.course_end_time%type;
619 l_timezone ota_events.timezone%type;
620 begin
621 -- Modified this function for bug#4393763 to return
622 -- course_end_Date for synchronous class
623 -- sysdate for asynchronous class and 'Planned' status class
624 select oev.course_start_date, oev.course_end_date,ocu.synchronous_flag, oev.event_status
625 ,oev.timezone , oev.course_end_time
626 into l_event_start_date,l_event_end_date,l_synchronous_flag, l_event_status
627 ,l_timezone , l_event_end_time
628 from ota_events oev, ota_offerings ofr, ota_category_usages ocu
629 Where oev.event_id = p_event_id and
630 oev.parent_offering_id = ofr.offering_id and
631 ofr.delivery_mode_id = ocu.category_usage_id and
632 event_type in ('SCHEDULED','SELFPACED') and
633 event_status in('P','N','F') and
634 -- l_date between nvl(enrolment_start_date, l_date) AND nvl(enrolment_end_date, l_date);
635 -- Modified for Bug#5107347
636 ota_timezone_util.convert_date(trunc(p_date),to_char(p_date,'HH24:MI'), ota_timezone_util.get_server_timezone_code , oev.timezone)
637 BETWEEN decode(oev.enrolment_start_date, NULL, to_date('0001/01/01','YYYY/MM/DD'),
638 to_date( to_char(oev.enrolment_start_date, 'YYYY/MM/DD') || ' ' || '00:00', 'YYYY/MM/DD HH24:MI'))
639 AND decode(oev.enrolment_end_date, NULL, to_date('4712/12/31','YYYY/MM/DD'),
640 to_date( to_char(oev.enrolment_end_date, 'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI'));
641 -- and l_date <= nvl(course_end_date, l_date); -- Bug 4767809
642
643 If upper(l_synchronous_flag) = 'N' then
644 -- l_start_date := sysdate;
645 l_start_date := ota_timezone_util.convert_date(trunc(sysdate)
646 , to_char(sysdate,'HH24:MI')
647 , ota_timezone_util.get_server_timezone_code
648 , l_timezone);
649 Else
650 If l_event_status = 'P' then
651 -- l_start_date := sysdate;
652 l_start_date := ota_timezone_util.convert_date(trunc(sysdate)
653 , to_char(sysdate,'HH24:MI')
654 , ota_timezone_util.get_server_timezone_code
655 , l_timezone);
656 Else
657 l_start_date := to_date(to_char(l_event_end_date,'YYYY/MM/DD')
658 || ' ' || nvl(l_event_end_time,'23:59'), 'YYYY/MM/DD HH24:MI') ;
659 End If;
660 End If;
661 /*
662 If upper(l_synchronous_flag) = 'N' then
663 l_start_date := sysdate;
664 Else
665 If l_event_status = 'P' then
666 l_start_date := sysdate;
667 Else
668 l_start_date := to_date(to_char(l_event_end_date,'YYYY/MM/DD')
669 || ' ' || nvl(l_event_end_time,'23:59'), 'YYYY/MM/DD HH24:MI') ;
670 End If;
671 End If;
672 */
673 -- return l_event_start_date;
674 return l_start_date;
675 end get_event_start_date;
676
677
678 function person_matches_assoc(
679 p_person_id in per_people_f.person_id%type,
680 p_as_of in date,
681 p_assoc_person_id in ota_event_associations.person_id%type,
682 p_assoc_organization_id in ota_event_associations.organization_id%type,
683 p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,
684 p_assoc_job_id in ota_event_associations.job_id%type,
685 p_assoc_position_id in ota_event_associations.position_id%type,
686 p_assoc_match_type in ota_event_associations.match_type%type) return boolean is
687 begin
688 if p_assoc_person_id is null then
689 if p_assoc_organization_id is not null and p_assoc_match_type = 'CHILD_ORGS' then
690 for an_assignment in csr_asg_details_recursive(p_person_id => p_person_id,
691 p_organization_id => p_assoc_organization_id,
692 p_org_structure_version_id => p_assoc_org_structure_vrsn_id,
693 p_job_id => p_assoc_job_id,
694 p_position_id => p_assoc_position_id,
695 p_as_of => p_as_of) loop
696 return true;
697 end loop;
698 else
699 for an_assignment in csr_asg_details(p_person_id => p_person_id,
700 p_organization_id => p_assoc_organization_id,
701 p_job_id => p_assoc_job_id,
702 p_position_id => p_assoc_position_id,
703 p_as_of => p_as_of) loop
704 return true;
705 end loop;
706 end if;
707
708 elsif p_assoc_person_id = p_person_id then
709 return true;
710 end if;
711
712 return false;
713 end person_matches_assoc;
714
715
716 function emp_matches_org(
717 p_person_id in per_people_f.person_id%type,
718 p_event_id in ota_events.event_id%type,
719 p_organization_id in ota_events.organization_id%type) return varchar2 is
720
721 cursor csr_assignments(
722 p_person_id per_people_f.person_id%type,
723 p_organization_id ota_event_associations.organization_id%type,
724 p_course_start_date otv_scheduled_events.course_start_date%type,
725 p_now date) is
726 select asg.assignment_id
727 from per_all_assignments_f asg
728 where asg.person_id = p_person_id and
729 nvl(p_course_start_date, trunc(p_now)) between asg.effective_start_date and asg.effective_end_date and
730 p_organization_id = asg.organization_id and
731 asg.assignment_type in ('E','A','C');
732 l_now date;
733 l_event_start_date ota_events.course_start_date%type;
734 begin
735 l_now := sysdate;
736 -- Bug 4584737: if no data found, event has expired or does not exist. Return 'N'.
737 begin
738 l_event_start_date := get_event_start_date(p_event_id, l_now);
739 exception
740 when NO_DATA_FOUND then
741 return 'N';
742 end;
743
744 for an_assignment in csr_assignments(p_person_id, p_organization_id, l_event_start_date, l_now) loop
745 return 'Y';
746 end loop;
747
748 return 'N';
749 end emp_matches_org;
750
751 FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
752 p_organization_id IN ota_event_associations.organization_id%type,
753 p_person_id IN per_people_f.person_id%type)
754 RETURN VARCHAR2 IS
755
756 CURSOR csr_lrnr_belongs_to_org IS
757 SELECT asg.assignment_id
758 FROM per_all_assignments_f asg,
759 (
760 SELECT p_organization_id AS organization_id
761 FROM dual
762 UNION ALL
763 SELECT x.sub_organization_id AS organization_id
764 FROM per_org_hrchy_summary x,
765 per_org_structure_versions v,
766 per_org_structure_versions currv
767 WHERE v.org_structure_version_id = p_org_structure_version_id AND
768 v.organization_structure_id = currv.organization_structure_id AND
769 (currv.date_to IS NULL OR
770 sysdate BETWEEN currv.date_from AND currv.date_to) AND
771 x.organization_structure_id = currv.organization_structure_id AND
772 x.org_structure_version_id = currv.org_structure_version_id AND
773 x.organization_id = p_organization_id AND
774 x.sub_org_relative_level > 0
775 ) orgs
776 WHERE asg.person_id = p_person_id AND
777 asg.organization_id = orgs.organization_id AND
778 asg.assignment_type in ('E','A','C');
779
780 l_assignment_id per_all_assignments_f.assignment_id%type;
781 BEGIN
782
783 OPEN csr_lrnr_belongs_to_org;
784 FETCH csr_lrnr_belongs_to_org INTO l_assignment_id;
785 CLOSE csr_lrnr_belongs_to_org;
786
787 IF l_assignment_id IS NOT NULL THEN
788 RETURN 'Y';
789 ELSE
790 RETURN 'N';
791 END IF;
792
793 END learner_belongs_to_child_org;
794
795
796 --Checks whether user enrolled for the class or not
797 function is_user_enrolled(
798 p_user_id fnd_user.user_id%type,
799 p_event_id ota_events.event_id%type
800 ) return boolean is
801
802 cursor person_bookings(
803 p_event_id ota_events.event_id%type,
804 p_person_id per_all_people.person_id%type) is
805 select 1
806 from ota_delegate_bookings book,
807 ota_booking_status_types stype
808 where book.delegate_person_id = p_person_id and
809 book.event_id = p_event_id and
810 book.booking_status_type_id = stype.booking_status_type_id and
811 stype.type in ('P', 'A' ,'E');
812 begin
813
814 for a_booking in person_bookings(p_event_id, p_user_id) loop
815 return true;
816 end loop;
817
818 return false;
819 end is_user_enrolled;
820
821
822 function emp_has_access(
823 p_person_id in per_people_f.person_id%type,
824 p_event_id in ota_events.event_id%type,
825 p_self_enroll_only in varchar2) return varchar2 is
826
827 l_event_start_date ota_events.course_start_date%type;
828 l_now date := sysdate;
829 l_offering_id ota_offerings.offering_id%type;
830 l_activity_version_id ota_activity_versions.activity_version_id%type;
831 l_category_usage_id ota_category_usages.category_usage_id%type;
832 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
833 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
834 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
835 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
836
837 l_is_mandatory_enr_assoc boolean:= false ;
838 l_event_association_id ota_event_associations.event_association_id%type;
839 begin
840 -- Bug 4584737: if no data found, event has expired or does not exist. Return 'N'.
841 begin
842 l_event_start_date := get_event_start_date(p_event_id, l_now);
843 exception
844 when NO_DATA_FOUND then
845 return 'N';
846 end;
847 --Perform the below actions only if mandatory event associations are not associated with the person for given event
848 open check_mandatory_evt_assoc(p_event_id,p_person_id,l_event_start_date);
849 fetch check_mandatory_evt_assoc into l_event_association_id;
850
851 if check_mandatory_evt_assoc%NOTFOUND then
852 l_is_mandatory_enr_assoc :=false;
853 else
854 l_is_mandatory_enr_assoc := true;
855 end if;
856
857 close check_mandatory_evt_assoc;
858
859 if not l_is_mandatory_enr_assoc then
860 -- Get the event's offering, course, and five levels of categories. If the category tree is deeper than that,
861 -- we will have to use CONNECT BY below.
862 open csr_evt_tree(p_event_id);
863 fetch csr_evt_tree into
864 l_offering_id, l_activity_version_id, l_category_usage_id,
865 l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
866 close csr_evt_tree;
867
868 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
869 if (assoc.user_group_id is not null) then
870 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
871 return 'Y';
872 end if;
873 else
874 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
875 return 'Y';
876 end if;
877 end if;
878 end loop;
879
880 -- if the 4th-level parent category is not null, then there may be more parent
881 -- categories above. Use a CONNECT BY cursor.
882 if l_parent_cat_usage_id_4 is not null then
883 for assoc in csr_category_assoc(p_self_enroll_only, l_parent_cat_usage_id_4) loop
884 if (assoc.user_group_id is not null) then
885 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
886 return 'Y';
887 end if;
888 else
889 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
890 return 'Y';
891 end if;
892 end if;
893 end loop;
894 end if;
895 else -- added condition for 16191536
896 if( is_user_enrolled(p_person_id,p_event_id)) then
897 return 'Y';
898 end if ;
899 end if;
900
901 return 'N';
902 end emp_has_access;
903
904
905 function ext_has_access(
906 p_party_id in hz_parties.party_id%type,
907 p_event_id in ota_events.event_id%type,
908 p_self_enroll_only in varchar2) return varchar2 is
909
910 l_offering_id ota_offerings.offering_id%type;
911 l_activity_version_id ota_activity_versions.activity_version_id%type;
912 l_category_usage_id ota_category_usages.category_usage_id%type;
913 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
914 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
915 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
916 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
917 begin
918 -- Get the event's offering, course, and five levels of categories. If the category tree is deeper than that,
919 -- we will have to use CONNECT BY below.
920 open csr_evt_tree(p_event_id);
921 fetch csr_evt_tree into
922 l_offering_id, l_activity_version_id, l_category_usage_id,
923 l_parent_cat_usage_id_1, l_parent_cat_usage_id_2, l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
924 close csr_evt_tree;
925
926 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
927 return 'Y';
928 end loop;
929
930 -- if the 4th-level parent category is not null, then there may be more parent
931 -- categories above. Use a CONNECT BY cursor as a last resort - it's slow.
932 if l_parent_cat_usage_id_4 is not null then
933 for assoc in csr_category_assoc_ext(p_self_enroll_only, p_party_id, l_parent_cat_usage_id_4) loop
934 return 'Y';
935 end loop;
936 end if;
937
938 return 'N';
939 end ext_has_access;
940
941 FUNCTION emp_has_valid_dates(p_person_id per_all_people_f.person_id%TYPE
942 ,p_event_id ota_events.event_id%TYPE)
943 RETURN VARCHAR2 IS
944 l_event_start_date DATE;
945 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
946 BEGIN
947
948 BEGIN
949 l_event_start_date := get_event_start_date(p_event_id, sysdate);
950 -- Added exception block for bug#5614187
951 EXCEPTION
952 when NO_DATA_FOUND then
953 l_event_start_date := trunc(sysdate);
954 END;
955
956 OPEN csr_asg_details(p_person_id, NUll, NULL, NULL, l_event_start_date);
957 FETCH csr_asg_details INTO l_assignment_id;
958 IF csr_asg_details%FOUND THEN
959 CLOSE csr_asg_details;
960 RETURN 'Y';
961 ELSE
962 CLOSE csr_asg_details;
963 RETURN 'N';
964 END IF;
965
966 END emp_has_valid_dates;
967
968
969 function employee_can_enroll(
970 p_person_id in per_people_f.person_id%type,
971 p_event_id in ota_events.event_id%type,
972 p_public_event_flag ota_events.public_event_flag%type,
973 p_max_internal in ota_events.maximum_internal_attendees%type,
974 p_event_start_date in otv_scheduled_events.course_start_date%type,
975 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
976
977 --Bug 547819
978 Cursor c_event_data is
979 Select OCU.synchronous_flag, OEV.event_status, OEV.secure_event_flag,
980 OEV.organization_id
981 From ota_events OEV, ota_offerings OFR, ota_category_usages OCU
982 Where OEV.event_id = p_event_id
983 And OEV.parent_offering_id = OFR.offering_id
984 And OFR.delivery_mode_id = OCU.category_usage_id;
985
986 l_synchronous_flag ota_category_usages.synchronous_flag%type;
987 l_effective_start_date PER_ALL_PEOPLE_F.effective_start_date%type;
988 l_event_status ota_events.event_status%type;
989 l_secure_event_flag ota_events.secure_event_flag%type;
990 l_organization_id ota_events.organization_id%type;
991 l_start_date otv_scheduled_events.course_start_date%type;
992 l_is_secure_event boolean;
993 begin
994 --Bug 547819
995 Open c_event_data;
996 Fetch c_event_data into l_synchronous_flag, l_event_status, l_secure_event_flag, l_organization_id;
997 Close c_event_data;
998
999 -- Is this a secure event?
1000 l_is_secure_event := (l_secure_event_flag is not null and upper(l_secure_event_flag) = 'Y');
1001
1002 -- Employees can enroll if MAXIMUM_INTERNAL_ATTENDEES is null or greater than
1003 -- zero and one of the following is true:
1004 -- The event is secure and the employee is in the organization, or
1005 -- The event is NOT secure and...
1006 -- * the event is public, or
1007 -- * the event is a customer event, or
1008 -- * the learner has been specifically given access via
1009 -- event associations.
1010
1011 if (p_max_internal is null or p_max_internal > 0) and
1012 (
1013 (l_is_secure_event and emp_matches_org(p_person_id, p_event_id, l_organization_id) = 'Y') or
1014 (
1015 not l_is_secure_event and
1016 (
1017 (p_public_event_flag = 'Y' AND emp_has_valid_dates(p_person_id, p_event_id) = 'Y') or
1018 is_customer_event(p_event_id) or
1019 emp_has_access(p_person_id, p_event_id, 'N') = 'Y'
1020 )
1021 )
1022 ) then
1023 return 'Y';
1024 else
1025 return 'N';
1026 end if;
1027 end employee_can_enroll;
1028
1029
1030 function employee_can_self_enroll(
1031 p_person_id in per_people_f.person_id%type,
1032 p_event_id in ota_events.event_id%type,
1033 p_public_event_flag ota_events.public_event_flag%type,
1034 p_max_internal in ota_events.maximum_internal_attendees%type,
1035 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1036 begin
1037 -- Employees may not enroll if MAXIMUM_INTERNAL_ATTENDEES is zero.
1038 -- Self-enrollment can only be specified via event associations. Therefore,
1039 -- public events or customer events can never be self-enrollable.
1040 if p_max_internal <= 0 or p_public_event_flag = 'Y' or is_customer_event(p_event_id) then
1041 return 'N';
1042 end if;
1043
1044 -- Check for self-enrollment event associations
1045 return emp_has_access(p_person_id, p_event_id, 'Y');
1046 end employee_can_self_enroll;
1047
1048
1049 function ext_learner_can_enroll(
1050 p_party_id in hz_parties.party_id%type,
1051 p_event_id in ota_events.event_id%type,
1052 p_public_event_flag ota_events.public_event_flag%type,
1053 p_max_internal in ota_events.maximum_internal_attendees%type,
1054 p_event_start_date in otv_scheduled_events.course_start_date%type,
1055 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1056 begin
1057 -- External learners can enroll if the event is public, or it is a non-customer event and
1058 -- the learner has been specifically given access via event associations.
1059
1060 if p_public_event_flag = 'Y' or
1061 (not is_customer_event(p_event_id) and
1062 ext_has_access(p_party_id, p_event_id, 'N') = 'Y') then
1063 return 'Y';
1064 elsif (p_public_event_flag = 'N' and --added as the external learners should be
1065 is_customer_event(p_event_id)) then --able to enroll into private events created
1066 return 'Y'; --for the customers.bug#6327056.
1067 else
1068 return 'N';
1069 end if;
1070 end ext_learner_can_enroll;
1071
1072
1073 function ext_learner_can_self_enroll(
1074 p_party_id in hz_parties.party_id%type,
1075 p_event_id in ota_events.event_id%type,
1076 p_public_event_flag ota_events.public_event_flag%type,
1077 p_max_internal in ota_events.maximum_internal_attendees%type,
1078 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1079 begin
1080 -- Self-enrollment can only be specified via event associations. Therefore,
1081 -- public events or customer events can never be self-enrollable.
1082 if p_public_event_flag = 'Y' or is_customer_event(p_event_id) then
1083 return 'N';
1084 end if;
1085
1086 -- Check for self-enrollment event associations
1087 return ext_has_access(p_party_id, p_event_id, 'Y');
1088 end ext_learner_can_self_enroll;
1089
1090
1091 function learner_can_enroll(
1092 p_person_id in per_people_f.person_id%type,
1093 p_party_id in hz_parties.party_id%type,
1094 p_event_id in ota_events.event_id%type,
1095 p_public_event_flag in ota_events.public_event_flag%type,
1096 p_max_internal in ota_events.maximum_internal_attendees%type,
1097 p_event_start_date in otv_scheduled_events.course_start_date%type,
1098 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1099 begin
1100 if p_person_id is not null then
1101 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);
1102 else
1103 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);
1104 end if;
1105 end learner_can_enroll;
1106
1107
1108 function learner_can_enroll(
1109 p_person_id in per_people_f.person_id%type,
1110 p_party_id in hz_parties.party_id%type,
1111 p_event_id in ota_events.event_id%type,
1112 p_event_start_date in otv_scheduled_events.course_start_date%type,
1113 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1114
1115 v_public_event_flag ota_events.public_event_flag%type;
1116 v_max_internal ota_events.maximum_internal_attendees%type;
1117 begin
1118 select maximum_internal_attendees, public_event_flag
1119 into v_max_internal, v_public_event_flag
1120 from ota_events
1121 where event_id = p_event_id;
1122
1123 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);
1124 end learner_can_enroll;
1125
1126
1127 function learner_can_enroll(
1128 p_person_id in per_people_f.person_id%type,
1129 p_party_id in hz_parties.party_id%type,
1130 p_event_id in ota_events.event_id%type) return varchar2 is
1131
1132 v_event_start_date ota_events.course_start_date%type;
1133 v_parent_offering_id ota_events.parent_offering_id%type;
1134 v_public_event_flag ota_events.public_event_flag%type;
1135 v_max_internal ota_events.maximum_internal_attendees%type;
1136 begin
1137 select maximum_internal_attendees, public_event_flag, parent_offering_id, course_start_date
1138 into v_max_internal, v_public_event_flag, v_parent_offering_id, v_event_start_date
1139 from ota_events
1140 where event_id = p_event_id;
1141
1142 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);
1143 end learner_can_enroll;
1144
1145
1146 function learner_can_self_enroll(
1147 p_person_id in per_people_f.person_id%type,
1148 p_party_id in hz_parties.party_id%type,
1149 p_event_id in ota_events.event_id%type,
1150 p_public_event_flag in ota_events.public_event_flag%type,
1151 p_max_internal in ota_events.maximum_internal_attendees%type,
1152 p_event_start_date in otv_scheduled_events.course_start_date%type,
1153 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1154 begin
1155 if p_person_id is not null then
1156 return employee_can_self_enroll(p_person_id, p_event_id, p_public_event_flag, p_max_internal, p_parent_offering_id);
1157 else
1158 return ext_learner_can_self_enroll(p_party_id, p_event_id, p_public_event_flag, p_max_internal, p_parent_offering_id);
1159 end if;
1160 end learner_can_self_enroll;
1161
1162
1163 function learner_can_self_enroll(
1164 p_person_id in per_people_f.person_id%type,
1165 p_party_id in hz_parties.party_id%type,
1166 p_event_id in ota_events.event_id%type,
1167 p_event_start_date in otv_scheduled_events.course_start_date%type,
1168 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1169
1170 v_public_event_flag ota_events.public_event_flag%type;
1171 v_max_internal ota_events.maximum_internal_attendees%type;
1172 begin
1173 select maximum_internal_attendees, public_event_flag
1174 into v_max_internal, v_public_event_flag
1175 from ota_events
1176 where event_id = p_event_id;
1177
1178 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);
1179 end learner_can_self_enroll;
1180
1181 function learner_can_self_enroll(
1182 p_person_id in per_people_f.person_id%type,
1183 p_party_id in hz_parties.party_id%type,
1184 p_event_id in ota_events.event_id%type) return varchar2 is
1185
1186 v_event_start_date ota_events.course_start_date%type;
1187 v_parent_offering_id ota_events.parent_offering_id%type;
1188 v_public_event_flag ota_events.public_event_flag%type;
1189 v_max_internal ota_events.maximum_internal_attendees%type;
1190 begin
1191 select maximum_internal_attendees, public_event_flag, parent_offering_id, course_start_date
1192 into v_max_internal, v_public_event_flag, v_parent_offering_id, v_event_start_date
1193 from ota_events
1194 where event_id = p_event_id;
1195
1196 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);
1197 end learner_can_self_enroll;
1198
1199
1200 function chk_delegate_ok_for_event(
1201 p_delegate_id in per_people_f.person_id%type,
1202 p_event_id in ota_events.event_id%type,
1203 p_event_start_date in otv_scheduled_events.course_start_date%type,
1204 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2 is
1205 begin
1206 return learner_can_enroll(p_delegate_id, null, p_event_id, p_event_start_date, p_parent_offering_id);
1207 end chk_delegate_ok_for_event;
1208
1209
1210 function learner_can_see_category(
1211 p_person_id in per_people_f.person_id%type,
1212 p_party_id in hz_parties.party_id%type,
1213 p_category_usage_id in ota_category_usages.category_usage_id%type) return varchar2 is
1214
1215 begin
1216 /*
1217 * MAC: stubbed out for performance.
1218 */
1219 return 'Y';
1220 end learner_can_see_category;
1221
1222
1223 function learner_can_see_course(
1224 p_person_id in per_people_f.person_id%type,
1225 p_activity_version_id in ota_activity_versions.activity_version_id%type) return varchar2 is
1226 begin
1227 return 'Y';
1228 end learner_can_see_course;
1229
1230
1231 function learner_can_see_offering(
1232 p_person_id in per_people_f.person_id%type,
1233 p_offering_id in ota_offerings.offering_id%type) return varchar2 is
1234 begin
1235 return 'Y';
1236 end learner_can_see_offering;
1237
1238
1239 function emp_has_access_to_path(
1240 p_person_id in per_people_f.person_id%type,
1241 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1242 p_self_enroll_only in varchar2) return varchar2 is
1243
1244 l_now date := sysdate;
1245 l_category_usage_id ota_category_usages.category_usage_id%type;
1246 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1247 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1248 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1249 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1250 begin
1251 -- Get the learning path's five levels of categories. If the category tree is deeper than that,
1252 -- we will have to use CONNECT BY below.
1253 open csr_lp_tree(p_learning_path_id);
1254 fetch csr_lp_tree into
1255 l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1256 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1257 close csr_lp_tree;
1258
1259 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
1260 if (assoc.user_group_id is not null) then
1261 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1262 return 'Y';
1263 end if;
1264 else
1265 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
1266 return 'Y';
1267 end if;
1268 end if;
1269 end loop;
1270
1271 -- if the 4th-level parent category is not null, then there may be more parent
1272 -- categories above. Use a CONNECT BY cursor.
1273 if l_parent_cat_usage_id_4 is not null then
1274 for assoc in csr_category_assoc(p_self_enroll_only, l_parent_cat_usage_id_4) loop
1275 if (assoc.user_group_id is not null) then
1276 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1277 return 'Y';
1278 end if;
1279 else
1280 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
1281 return 'Y';
1282 end if;
1283 end if;
1284 end loop;
1285 end if;
1286
1287 return 'N';
1288 end emp_has_access_to_path;
1289
1290
1291 function ext_has_access_to_path(
1292 p_party_id in hz_parties.party_id%type,
1293 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1294 p_self_enroll_only in varchar2) return varchar2 is
1295
1296 l_category_usage_id ota_category_usages.category_usage_id%type;
1297 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1298 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1299 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1300 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1301 begin
1302 -- Get the learning path's five levels of categories. If the category tree is deeper than that,
1303 -- we will have to use CONNECT BY below.
1304 open csr_lp_tree(p_learning_path_id);
1305 fetch csr_lp_tree into
1306 l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1307 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1308 close csr_lp_tree;
1309
1310 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
1311 return 'Y';
1312 end loop;
1313
1314 -- if the 4th-level parent category is not null, then there may be more parent
1315 -- categories above. Use a CONNECT BY cursor as a last resort - it's slow.
1316 if l_parent_cat_usage_id_4 is not null then
1317 for assoc in csr_category_assoc_ext(p_self_enroll_only, p_party_id, l_parent_cat_usage_id_4) loop
1318 return 'Y';
1319 end loop;
1320 end if;
1321
1322 return 'N';
1323 end ext_has_access_to_path;
1324
1325
1326 function emp_can_enroll_in_path(
1327 p_person_id in per_people_f.person_id%type,
1328 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1329 p_public_flag ota_learning_paths.public_flag%type,
1330 p_start_date_active in ota_learning_paths.start_date_active%type,
1331 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1332 begin
1333 if p_public_flag = 'Y' then
1334 return 'Y';
1335 else
1336 return emp_has_access_to_path(p_person_id, p_learning_path_id, 'N');
1337 end if;
1338 end emp_can_enroll_in_path;
1339
1340
1341 function emp_can_self_enroll_in_path(
1342 p_person_id in per_people_f.person_id%type,
1343 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1344 p_public_flag ota_learning_paths.public_flag%type,
1345 p_start_date_active in ota_learning_paths.start_date_active%type,
1346 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1347 begin
1348 -- A public learning path disregards its learner access records. Learner access records
1349 -- are the only way an event can be made self-enrollable. Therefore, the employee cannot
1350 -- self-enroll.
1351 if p_public_flag = 'Y' then
1352 return 'N';
1353 else
1354 return emp_has_access_to_path(p_person_id, p_learning_path_id, 'Y');
1355 end if;
1356 end emp_can_self_enroll_in_path;
1357
1358
1359 function ext_can_enroll_in_path(
1360 p_party_id in hz_parties.party_id%type,
1361 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1362 p_public_flag ota_learning_paths.public_flag%type,
1363 p_start_date_active in ota_learning_paths.start_date_active%type,
1364 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1365 begin
1366 if p_public_flag = 'Y' then
1367 return 'Y';
1368 else
1369 return ext_has_access_to_path(p_party_id, p_learning_path_id, 'N');
1370 end if;
1371 end ext_can_enroll_in_path;
1372
1373
1374 function ext_can_self_enroll_in_path(
1375 p_party_id in hz_parties.party_id%type,
1376 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1377 p_public_flag ota_learning_paths.public_flag%type,
1378 p_start_date_active in ota_learning_paths.start_date_active%type,
1379 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1380 begin
1381 -- A public learning path disregards its learner access records. Learner access records
1382 -- are the only way an event can be made self-enrollable. Therefore, the ext learner cannot
1383 -- self-enroll.
1384 if p_public_flag = 'Y' then
1385 return 'N';
1386 else
1387 return ext_has_access_to_path(p_party_id, p_learning_path_id, 'Y');
1388 end if;
1389 end ext_can_self_enroll_in_path;
1390
1391
1392 function learner_can_enroll_in_path(
1393 p_person_id in per_people_f.person_id%type,
1394 p_party_id in hz_parties.party_id%type,
1395 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1396 p_public_flag ota_learning_paths.public_flag%type,
1397 p_start_date_active in ota_learning_paths.start_date_active%type,
1398 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1399 begin
1400 if p_person_id is not null then
1401 return emp_can_enroll_in_path(p_person_id, p_learning_path_id, p_public_flag, p_start_date_active, p_category_usage_id);
1402 else
1403 return ext_can_enroll_in_path(p_party_id, p_learning_path_id, p_public_flag, p_start_date_active, p_category_usage_id);
1404 end if;
1405 end learner_can_enroll_in_path;
1406
1407
1408 function learner_can_enroll_in_path(
1409 p_user_id in fnd_user.user_id%type,
1410 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1411 p_public_flag ota_learning_paths.public_flag%type,
1412 p_start_date_active in ota_learning_paths.start_date_active%type,
1413 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1414
1415 v_employee_id fnd_user.employee_id%type;
1416 v_party_id hz_parties.party_id%type;
1417 begin
1418 select employee_id, person_party_id
1419 into v_employee_id, v_party_id
1420 from fnd_user
1421 where user_id = p_user_id;
1422
1423 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);
1424 end learner_can_enroll_in_path;
1425
1426
1427 function lrn_can_self_enroll_in_path(
1428 p_person_id in per_people_f.person_id%type,
1429 p_party_id in hz_parties.party_id%type,
1430 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1431 p_public_flag ota_learning_paths.public_flag%type,
1432 p_start_date_active in ota_learning_paths.start_date_active%type,
1433 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1434 begin
1435 if p_person_id is not null then
1436 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);
1437 else
1438 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);
1439 end if;
1440 end lrn_can_self_enroll_in_path;
1441
1442
1443 function lrn_can_self_enroll_in_path(
1444 p_user_id in fnd_user.user_id%type,
1445 p_learning_path_id in ota_learning_paths.learning_path_id%type,
1446 p_public_flag ota_learning_paths.public_flag%type,
1447 p_start_date_active in ota_learning_paths.start_date_active%type,
1448 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1449
1450 v_employee_id fnd_user.employee_id%type;
1451 v_party_id hz_parties.party_id%type;
1452 begin
1453 select employee_id, person_party_id
1454 into v_employee_id, v_party_id
1455 from fnd_user
1456 where user_id = p_user_id;
1457
1458 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);
1459 end lrn_can_self_enroll_in_path;
1460
1461 --functions for forum(open) enrollments
1462
1463 function emp_has_access_to_forum(
1464 p_person_id in per_people_f.person_id%type,
1465 p_forum_id in ota_forums_b.forum_id%type,
1466 p_self_enroll_only in varchar2) return varchar2 is
1467
1468 l_now date := sysdate;
1469 l_object_id ota_frm_obj_inclusions.object_id%type;
1470 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1471 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1472 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1473 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1474 begin
1475 -- Get the forum's five levels of categories. If the category tree is deeper than that,
1476 -- we will have to use CONNECT BY below.
1477 open csr_forum_tree(p_forum_id);
1478 fetch csr_forum_tree into
1479 l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1480 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1481 close csr_forum_tree;
1482
1483 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
1484 if (assoc.user_group_id is not null) then
1485 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1486 return 'Y';
1487 end if;
1488 else
1489 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
1490 return 'Y';
1491 end if;
1492 end if;
1493 end loop;
1494
1495 -- if the 4th-level parent category is not null, then there may be more parent
1496 -- categories above. Use a CONNECT BY cursor.
1497 if l_parent_cat_usage_id_4 is not null then
1498 for assoc in csr_category_assoc(p_self_enroll_only, l_parent_cat_usage_id_4) loop
1499 if (assoc.user_group_id is not null) then
1500 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1501 return 'Y';
1502 end if;
1503 else
1504 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
1505 return 'Y';
1506 end if;
1507 end if;
1508 end loop;
1509 end if;
1510
1511 return 'N';
1512 end emp_has_access_to_forum;
1513
1514
1515 function ext_has_access_to_forum(
1516 p_party_id in hz_parties.party_id%type,
1517 p_forum_id in ota_forums_b.forum_id%type,
1518 p_self_enroll_only in varchar2) return varchar2 is
1519
1520 l_object_id ota_frm_obj_inclusions.object_id%type;
1521 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1522 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1523 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1524 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1525 begin
1526 -- Get the forums's five levels of categories. If the category tree is deeper than that,
1527 -- we will have to use CONNECT BY below.
1528 open csr_forum_tree(p_forum_id);
1529 fetch csr_forum_tree into
1530 l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1531 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1532 close csr_forum_tree;
1533
1534 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
1535 return 'Y';
1536 end loop;
1537
1538 -- if the 4th-level parent category is not null, then there may be more parent
1539 -- categories above. Use a CONNECT BY cursor as a last resort - it's slow.
1540 if l_parent_cat_usage_id_4 is not null then
1541 for assoc in csr_category_assoc_ext(p_self_enroll_only, p_party_id, l_parent_cat_usage_id_4) loop
1542 return 'Y';
1543 end loop;
1544 end if;
1545
1546 return 'N';
1547 end ext_has_access_to_forum;
1548
1549
1550 function emp_can_enroll_in_forum(
1551 p_person_id in per_people_f.person_id%type,
1552 p_forum_id in ota_forums_b.forum_id%type,
1553 p_public_flag ota_forums_b.public_flag%type,
1554 p_start_date_active in ota_forums_b.start_date_active%type,
1555 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1556 begin
1557 if p_public_flag = 'Y' then
1558 return 'Y';
1559 else
1560 return emp_has_access_to_forum(p_person_id, p_forum_id, 'N');
1561 end if;
1562 end emp_can_enroll_in_forum;
1563
1564
1565 function emp_can_self_enroll_in_forum(
1566 p_person_id in per_people_f.person_id%type,
1567 p_forum_id in ota_forums_b.forum_id%type,
1568 p_public_flag ota_forums_b.public_flag%type,
1569 p_start_date_active in ota_forums_b.start_date_active%type,
1570 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1571 begin
1572 -- A public forum disregards its learner access records. Learner access records
1573 -- are the only way an event can be made self-enrollable. Therefore, the employee cannot
1574 -- self-enroll.
1575 if p_public_flag = 'Y' then
1576 return 'N';
1577 else
1578 return emp_has_access_to_forum(p_person_id, p_forum_id, 'Y');
1579 end if;
1580 end emp_can_self_enroll_in_forum;
1581
1582
1583 function ext_can_enroll_in_forum(
1584 p_party_id in hz_parties.party_id%type,
1585 p_forum_id in ota_forums_b.forum_id%type,
1586 p_public_flag ota_forums_b.public_flag%type,
1587 p_start_date_active in ota_forums_b.start_date_active%type,
1588 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1589 begin
1590 if p_public_flag = 'Y' then
1591 return 'Y';
1592 else
1593 return ext_has_access_to_forum(p_party_id, p_forum_id, 'N');
1594 end if;
1595 end ext_can_enroll_in_forum;
1596
1597
1598 function ext_can_self_enroll_in_forum(
1599 p_party_id in hz_parties.party_id%type,
1600 p_forum_id in ota_forums_b.forum_id%type,
1601 p_public_flag ota_forums_b.public_flag%type,
1602 p_start_date_active in ota_forums_b.start_date_active%type,
1603 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1604 begin
1605 -- A public forum disregards its learner access records. Learner access records
1606 -- are the only way an event can be made self-enrollable. Therefore, the ext learner cannot
1607 -- self-enroll.
1608 if p_public_flag = 'Y' then
1609 return 'N';
1610 else
1611 return ext_has_access_to_forum(p_party_id, p_forum_id, 'Y');
1612 end if;
1613 end ext_can_self_enroll_in_forum;
1614
1615
1616 function learner_can_enroll_in_forum(
1617 p_person_id in per_people_f.person_id%type,
1618 p_party_id in hz_parties.party_id%type,
1619 p_forum_id in ota_forums_b.forum_id%type,
1620 p_public_flag ota_forums_b.public_flag%type,
1621 p_start_date_active in ota_forums_b.start_date_active%type,
1622 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1623 begin
1624 if p_person_id is not null then
1625 return emp_can_enroll_in_forum(p_person_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1626 else
1627 return ext_can_enroll_in_forum(p_party_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1628 end if;
1629 end learner_can_enroll_in_forum;
1630
1631
1632 function learner_can_enroll_in_forum(
1633 p_user_id in fnd_user.user_id%type,
1634 p_forum_id in ota_forums_b.forum_id%type,
1635 p_public_flag ota_forums_b.public_flag%type,
1636 p_start_date_active in ota_forums_b.start_date_active%type,
1637 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1638
1639 v_employee_id fnd_user.employee_id%type;
1640 v_party_id hz_parties.party_id%type;
1641 begin
1642 select employee_id, person_party_id
1643 into v_employee_id, v_party_id
1644 from fnd_user
1645 where user_id = p_user_id;
1646
1647 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);
1648 end learner_can_enroll_in_forum;
1649
1650
1651 function lrn_can_self_enroll_in_forum(
1652 p_person_id in per_people_f.person_id%type,
1653 p_party_id in hz_parties.party_id%type,
1654 p_forum_id in ota_forums_b.forum_id%type,
1655 p_public_flag ota_forums_b.public_flag%type,
1656 p_start_date_active in ota_forums_b.start_date_active%type,
1657 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1658 begin
1659 if p_person_id is not null then
1660 return emp_can_self_enroll_in_forum(p_person_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1661 else
1662 return ext_can_self_enroll_in_forum(p_party_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1663 end if;
1664 end lrn_can_self_enroll_in_forum;
1665
1666
1667 function lrn_can_self_enroll_in_forum(
1668 p_user_id in fnd_user.user_id%type,
1669 p_forum_id in ota_forums_b.forum_id%type,
1670 p_public_flag ota_forums_b.public_flag%type,
1671 p_start_date_active in ota_forums_b.start_date_active%type,
1672 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1673
1674 v_employee_id fnd_user.employee_id%type;
1675 v_party_id hz_parties.party_id%type;
1676 begin
1677 select employee_id, person_party_id
1678 into v_employee_id, v_party_id
1679 from fnd_user
1680 where user_id = p_user_id;
1681
1682 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);
1683 end lrn_can_self_enroll_in_forum;
1684 --end of utilities for forums
1685
1686 --functions for chats(open) enrollments
1687
1688 function emp_has_access_to_chat(
1689 p_person_id in per_people_f.person_id%type,
1690 p_chat_id in ota_chats_b.chat_id%type,
1691 p_self_enroll_only in varchar2) return varchar2 is
1692
1693 l_now date := sysdate;
1694 l_object_id ota_chat_obj_inclusions.object_id%type;
1695 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1696 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1697 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1698 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1699 begin
1700 -- Get the chat's five levels of categories. If the category tree is deeper than that,
1701 -- we will have to use CONNECT BY below.
1702 open csr_chat_tree(p_chat_id);
1703 fetch csr_chat_tree into
1704 l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1705 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1706 close csr_chat_tree;
1707
1708 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
1709 if (assoc.user_group_id is not null) then
1710 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1711 return 'Y';
1712 end if;
1713 else
1714 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
1715 return 'Y';
1716 end if;
1717 end if;
1718 end loop;
1719
1720 -- if the 4th-level parent category is not null, then there may be more parent
1721 -- categories above. Use a CONNECT BY cursor.
1722 if l_parent_cat_usage_id_4 is not null then
1723 for assoc in csr_category_assoc(p_self_enroll_only, l_parent_cat_usage_id_4) loop
1724 if (assoc.user_group_id is not null) then
1725 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1726 return 'Y';
1727 end if;
1728 else
1729 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
1730 return 'Y';
1731 end if;
1732 end if;
1733 end loop;
1734 end if;
1735
1736 return 'N';
1737 end emp_has_access_to_chat;
1738
1739
1740 function ext_has_access_to_chat(
1741 p_party_id in hz_parties.party_id%type,
1742 p_chat_id in ota_chats_b.chat_id%type,
1743 p_self_enroll_only in varchar2) return varchar2 is
1744
1745 l_object_id ota_chat_obj_inclusions.object_id%type;
1746 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1747 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1748 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1749 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1750 begin
1751 -- Get the chat's five levels of categories. If the category tree is deeper than that,
1752 -- we will have to use CONNECT BY below.
1753 open csr_chat_tree(p_chat_id);
1754 fetch csr_chat_tree into
1755 l_object_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1756 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1757 close csr_chat_tree;
1758
1759 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
1760 return 'Y';
1761 end loop;
1762
1763 -- if the 4th-level parent category is not null, then there may be more parent
1764 -- categories above. Use a CONNECT BY cursor as a last resort - it's slow.
1765 if l_parent_cat_usage_id_4 is not null then
1766 for assoc in csr_category_assoc_ext(p_self_enroll_only, p_party_id, l_parent_cat_usage_id_4) loop
1767 return 'Y';
1768 end loop;
1769 end if;
1770
1771 return 'N';
1772 end ext_has_access_to_chat;
1773
1774
1775 function emp_can_enroll_in_chat(
1776 p_person_id in per_people_f.person_id%type,
1777 p_chat_id in ota_chats_b.chat_id%type,
1778 p_public_flag ota_chats_b.public_flag%type,
1779 p_start_date_active in ota_chats_b.start_date_active%type,
1780 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1781 begin
1782 if p_public_flag = 'Y' then
1783 return 'Y';
1784 else
1785 return emp_has_access_to_chat(p_person_id, p_chat_id, 'N');
1786 end if;
1787 end emp_can_enroll_in_chat;
1788
1789
1790 function emp_can_self_enroll_in_chat(
1791 p_person_id in per_people_f.person_id%type,
1792 p_chat_id in ota_chats_b.chat_id%type,
1793 p_public_flag ota_chats_b.public_flag%type,
1794 p_start_date_active in ota_chats_b.start_date_active%type,
1795 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1796 begin
1797 -- A public chat disregards its learner access records. Learner access records
1798 -- are the only way an event can be made self-enrollable. Therefore, the employee cannot
1799 -- self-enroll.
1800 if p_public_flag = 'Y' then
1801 return 'N';
1802 else
1803 return emp_has_access_to_chat(p_person_id, p_chat_id, 'Y');
1804 end if;
1805 end emp_can_self_enroll_in_chat;
1806
1807
1808 function ext_can_enroll_in_chat(
1809 p_party_id in hz_parties.party_id%type,
1810 p_chat_id in ota_chats_b.chat_id%type,
1811 p_public_flag ota_chats_b.public_flag%type,
1812 p_start_date_active in ota_chats_b.start_date_active%type,
1813 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1814 begin
1815 if p_public_flag = 'Y' then
1816 return 'Y';
1817 else
1818 return ext_has_access_to_chat(p_party_id, p_chat_id, 'N');
1819 end if;
1820 end ext_can_enroll_in_chat;
1821
1822
1823 function ext_can_self_enroll_in_chat(
1824 p_party_id in hz_parties.party_id%type,
1825 p_chat_id in ota_chats_b.chat_id%type,
1826 p_public_flag ota_chats_b.public_flag%type,
1827 p_start_date_active in ota_chats_b.start_date_active%type,
1828 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1829 begin
1830 -- A public chat disregards its learner access records. Learner access records
1831 -- are the only way an event can be made self-enrollable. Therefore, the ext learner cannot
1832 -- self-enroll.
1833 if p_public_flag = 'Y' then
1834 return 'N';
1835 else
1836 return ext_has_access_to_chat(p_party_id, p_chat_id, 'Y');
1837 end if;
1838 end ext_can_self_enroll_in_chat;
1839
1840
1841 function learner_can_enroll_in_chat(
1842 p_person_id in per_people_f.person_id%type,
1843 p_party_id in hz_parties.party_id%type,
1844 p_chat_id in ota_chats_b.chat_id%type,
1845 p_public_flag ota_chats_b.public_flag%type,
1846 p_start_date_active in ota_chats_b.start_date_active%type,
1847 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1848 begin
1849 if p_person_id is not null then
1850 return emp_can_enroll_in_chat(p_person_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1851 else
1852 return ext_can_enroll_in_chat(p_party_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1853 end if;
1854 end learner_can_enroll_in_chat;
1855
1856
1857 function learner_can_enroll_in_chat(
1858 p_user_id in fnd_user.user_id%type,
1859 p_chat_id in ota_chats_b.chat_id%type,
1860 p_public_flag ota_chats_b.public_flag%type,
1861 p_start_date_active in ota_chats_b.start_date_active%type,
1862 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1863
1864 v_employee_id fnd_user.employee_id%type;
1865 v_party_id hz_parties.party_id%type;
1866 begin
1867 select employee_id, person_party_id
1868 into v_employee_id, v_party_id
1869 from fnd_user
1870 where user_id = p_user_id;
1871
1872 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);
1873 end learner_can_enroll_in_chat;
1874
1875
1876 function lrn_can_self_enroll_in_chat(
1877 p_person_id in per_people_f.person_id%type,
1878 p_party_id in hz_parties.party_id%type,
1879 p_chat_id in ota_chats_b.chat_id%type,
1880 p_public_flag ota_chats_b.public_flag%type,
1881 p_start_date_active in ota_chats_b.start_date_active%type,
1882 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1883 begin
1884 if p_person_id is not null then
1885 return emp_can_self_enroll_in_chat(p_person_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1886 else
1887 return ext_can_self_enroll_in_chat(p_party_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1888 end if;
1889 end lrn_can_self_enroll_in_chat;
1890
1891
1892 function lrn_can_self_enroll_in_chat(
1893 p_user_id in fnd_user.user_id%type,
1894 p_chat_id in ota_chats_b.chat_id%type,
1895 p_public_flag ota_chats_b.public_flag%type,
1896 p_start_date_active in ota_chats_b.start_date_active%type,
1897 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1898
1899 v_employee_id fnd_user.employee_id%type;
1900 v_party_id hz_parties.party_id%type;
1901 begin
1902 select employee_id, person_party_id
1903 into v_employee_id, v_party_id
1904 from fnd_user
1905 where user_id = p_user_id;
1906
1907 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);
1908 end lrn_can_self_enroll_in_chat;
1909 --end of utilities for chats
1910
1911
1912 function emp_has_access_to_cert(
1913 p_person_id in per_people_f.person_id%type,
1914 p_certification_id in ota_certifications_b.certification_id%type,
1915 p_self_enroll_only in varchar2) return varchar2 is
1916
1917 l_now date := sysdate;
1918 l_category_usage_id ota_category_usages.category_usage_id%type;
1919 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1920 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1921 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1922 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1923 begin
1924 -- Get the certification's five levels of categories. If the category tree is deeper than that,
1925 -- we will have to use CONNECT BY below.
1926 open csr_cert_tree(p_certification_id);
1927 fetch csr_cert_tree into
1928 l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1929 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1930 close csr_cert_tree;
1931
1932 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
1933 if (assoc.user_group_id is not null) then
1934 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1935 return 'Y';
1936 end if;
1937 else
1938 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
1939 return 'Y';
1940 end if;
1941 end if;
1942 end loop;
1943
1944 -- if the 4th-level parent category is not null, then there may be more parent
1945 -- categories above. Use a CONNECT BY cursor.
1946 if l_parent_cat_usage_id_4 is not null then
1947 for assoc in csr_category_assoc(p_self_enroll_only, l_parent_cat_usage_id_4) loop
1948 if (assoc.user_group_id is not null) then
1949 if(is_learner_in_user_group(p_person_id,assoc.user_group_id,ota_general.get_business_group_id) = 'Y' ) then
1950 return 'Y';
1951 end if;
1952 else
1953 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
1954 return 'Y';
1955 end if;
1956 end if;
1957 end loop;
1958 end if;
1959
1960 return 'N';
1961 end emp_has_access_to_cert;
1962
1963
1964 function ext_has_access_to_cert(
1965 p_party_id in hz_parties.party_id%type,
1966 p_certification_id in ota_certifications_b.certification_id%type,
1967 p_self_enroll_only in varchar2) return varchar2 is
1968
1969 l_category_usage_id ota_category_usages.category_usage_id%type;
1970 l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1971 l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1972 l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1973 l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1974 begin
1975 -- Get the certification's five levels of categories. If the category tree is deeper than that,
1976 -- we will have to use CONNECT BY below.
1977 open csr_cert_tree(p_certification_id);
1978 fetch csr_cert_tree into
1979 l_category_usage_id, l_parent_cat_usage_id_1, l_parent_cat_usage_id_2,
1980 l_parent_cat_usage_id_3, l_parent_cat_usage_id_4;
1981 close csr_cert_tree;
1982
1983 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
1984 return 'Y';
1985 end loop;
1986
1987 -- if the 4th-level parent category is not null, then there may be more parent
1988 -- categories above. Use a CONNECT BY cursor as a last resort - it's slow.
1989 if l_parent_cat_usage_id_4 is not null then
1990 for assoc in csr_category_assoc_ext(p_self_enroll_only, p_party_id, l_parent_cat_usage_id_4) loop
1991 return 'Y';
1992 end loop;
1993 end if;
1994
1995 return 'N';
1996 end ext_has_access_to_cert;
1997
1998
1999 function emp_can_enroll_in_cert(
2000 p_person_id in per_people_f.person_id%type,
2001 p_certification_id in ota_certifications_b.certification_id%type,
2002 p_public_flag ota_certifications_b.public_flag%type,
2003 p_start_date_active in ota_certifications_b.start_date_active%type,
2004 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2005 begin
2006 if p_public_flag = 'Y' then
2007 return 'Y';
2008 else
2009 return emp_has_access_to_cert(p_person_id, p_certification_id, 'N');
2010 end if;
2011 end emp_can_enroll_in_cert;
2012
2013
2014 function emp_can_self_enroll_in_cert(
2015 p_person_id in per_people_f.person_id%type,
2016 p_certification_id in ota_certifications_b.certification_id%type,
2017 p_public_flag ota_certifications_b.public_flag%type,
2018 p_start_date_active in ota_certifications_b.start_date_active%type,
2019 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2020 begin
2021 -- A public certification disregards its learner access records. Learner access records
2022 -- are the only way a certification can be made self-enrollable. Therefore, the employee cannot
2023 -- self-enroll.
2024 if p_public_flag = 'Y' then
2025 return 'N';
2026 else
2027 return emp_has_access_to_cert(p_person_id, p_certification_id, 'Y');
2028 end if;
2029 end emp_can_self_enroll_in_cert;
2030
2031
2032 function ext_can_enroll_in_cert(
2033 p_party_id in hz_parties.party_id%type,
2034 p_certification_id in ota_certifications_b.certification_id%type,
2035 p_public_flag ota_certifications_b.public_flag%type,
2036 p_start_date_active in ota_certifications_b.start_date_active%type,
2037 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2038 begin
2039 if p_public_flag = 'Y' then
2040 return 'Y';
2041 else
2042 return ext_has_access_to_cert(p_party_id, p_certification_id, 'N');
2043 end if;
2044 end ext_can_enroll_in_cert;
2045
2046
2047 function ext_can_self_enroll_in_cert(
2048 p_party_id in hz_parties.party_id%type,
2049 p_certification_id in ota_certifications_b.certification_id%type,
2050 p_public_flag ota_certifications_b.public_flag%type,
2051 p_start_date_active in ota_certifications_b.start_date_active%type,
2052 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2053 begin
2054 -- A public certification disregards its learner access records. Learner access records
2055 -- are the only way a certification can be made self-enrollable. Therefore, the ext learner cannot
2056 -- self-enroll.
2057 if p_public_flag = 'Y' then
2058 return 'N';
2059 else
2060 return ext_has_access_to_cert(p_party_id, p_certification_id, 'Y');
2061 end if;
2062 end ext_can_self_enroll_in_cert;
2063
2064
2065 function learner_can_enroll_in_cert(
2066 p_person_id in per_people_f.person_id%type,
2067 p_party_id in hz_parties.party_id%type,
2068 p_certification_id in ota_certifications_b.certification_id%type,
2069 p_public_flag ota_certifications_b.public_flag%type,
2070 p_start_date_active in ota_certifications_b.start_date_active%type,
2071 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2072 begin
2073 if p_person_id is not null then
2074 return emp_can_enroll_in_cert(p_person_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2075 else
2076 return ext_can_enroll_in_cert(p_party_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2077 end if;
2078 end learner_can_enroll_in_cert;
2079
2080
2081 function learner_can_enroll_in_cert(
2082 p_user_id in fnd_user.user_id%type,
2083 p_certification_id in ota_certifications_b.certification_id%type,
2084 p_public_flag ota_certifications_b.public_flag%type,
2085 p_start_date_active in ota_certifications_b.start_date_active%type,
2086 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2087
2088 v_employee_id fnd_user.employee_id%type;
2089 v_party_id hz_parties.party_id%type;
2090 begin
2091 select employee_id, person_party_id
2092 into v_employee_id, v_party_id
2093 from fnd_user
2094 where user_id = p_user_id;
2095
2096 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);
2097 end learner_can_enroll_in_cert;
2098
2099
2100 function lrn_can_self_enroll_in_cert(
2101 p_person_id in per_people_f.person_id%type,
2102 p_party_id in hz_parties.party_id%type,
2103 p_certification_id in ota_certifications_b.certification_id%type,
2104 p_public_flag ota_certifications_b.public_flag%type,
2105 p_start_date_active in ota_certifications_b.start_date_active%type,
2106 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2107 begin
2108 if p_person_id is not null then
2109 return emp_can_self_enroll_in_cert(p_person_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2110 else
2111 return ext_can_self_enroll_in_cert(p_party_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2112 end if;
2113 end lrn_can_self_enroll_in_cert;
2114
2115
2116 function lrn_can_self_enroll_in_cert(
2117 p_user_id in fnd_user.user_id%type,
2118 p_certification_id in ota_certifications_b.certification_id%type,
2119 p_public_flag ota_certifications_b.public_flag%type,
2120 p_start_date_active in ota_certifications_b.start_date_active%type,
2121 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2122
2123 v_employee_id fnd_user.employee_id%type;
2124 v_party_id hz_parties.party_id%type;
2125 begin
2126 select employee_id, person_party_id
2127 into v_employee_id, v_party_id
2128 from fnd_user
2129 where user_id = p_user_id;
2130
2131 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);
2132 end lrn_can_self_enroll_in_cert;
2133
2134 function learner_can_enroll_in_path(
2135 p_learning_path_id in ota_learning_paths.learning_path_id%TYPE
2136 ,p_person_id in per_all_people_f.person_id%type
2137 ,p_party_id in hz_parties.party_id%type) return varchar2
2138 IS
2139 CURSOR csr_get_lp_details IS
2140 SELECT public_flag, start_date_active
2141 FROM ota_learning_paths
2142 WHERE learning_path_id = p_learning_path_id;
2143
2144 l_start_date_active ota_learning_paths.start_date_active%TYPE;
2145 l_public_flag ota_learning_paths.public_flag%TYPE;
2146 BEGIN
2147 OPEN csr_get_lp_details;
2148 FETCH csr_get_lp_details INTO l_public_flag, l_start_date_active ;
2149 IF csr_get_lp_details%NOTFOUND THEN
2150 CLOSE csr_get_lp_details;
2151 RETURN 'N';
2152 ELSE
2153 CLOSE csr_get_lp_details;
2154 RETURN learner_can_enroll_in_path(p_person_id => p_person_id
2155 , p_party_id => p_party_id
2156 , p_learning_path_id => p_learning_path_id
2157 , p_public_flag => l_public_flag
2158 , p_start_date_active => l_start_date_active);
2159 END IF;
2160
2161 END learner_can_enroll_in_path;
2162
2163 function learner_can_enroll_in_cert(
2164 p_certification_id in ota_certifications_b.certification_id%type
2165 ,p_person_id in per_all_people_f.person_id%type
2166 ,p_party_id in hz_parties.party_id%type) return varchar2
2167 IS
2168 CURSOR csr_get_cert_details IS
2169 SELECT public_flag, start_date_active
2170 FROM ota_certifications_b
2171 WHERE certification_id = p_certification_id;
2172
2173 l_start_date_active ota_certifications_b.start_date_active%TYPE;
2174 l_public_flag ota_certifications_b.public_flag%TYPE;
2175 BEGIN
2176 OPEN csr_get_cert_details;
2177 FETCH csr_get_cert_details INTO l_public_flag, l_start_date_active ;
2178 IF csr_get_cert_details%NOTFOUND THEN
2179 CLOSE csr_get_cert_details;
2180 RETURN 'N';
2181 ELSE
2182 CLOSE csr_get_cert_details;
2183 RETURN learner_can_enroll_in_cert(p_person_id => p_person_id
2184 , p_party_id => p_party_id
2185 , p_certification_id => p_certification_id
2186 , p_public_flag => l_public_flag
2187 , p_start_date_active => l_start_date_active);
2188 END IF;
2189 END learner_can_enroll_in_cert;
2190
2191 function learner_has_access_to_course(
2192 p_person_id in per_people_f.person_id%type,
2193 p_party_id in hz_parties.party_id%type,
2194 p_activity_version_id in ota_activity_versions.activity_version_id%type,
2195 showLPOnlyClasses in varchar2 default 'N') return varchar2 is
2196
2197 CURSOR csr_classes_in_course(
2198 p_activity_version_id in ota_activity_versions.activity_version_id%type,
2199 p_business_group_id ota_events.business_group_id%type,
2200 p_server_timezone_code fnd_timezones_b.timezone_code%type) is
2201 SELECT OEV.event_id,
2202 OEV.public_event_flag,
2203 OEV.maximum_internal_attendees,
2204 OEV.course_start_date
2205 FROM ota_category_usages OCU,
2206 ota_events OEV,
2207 ota_offerings OFR,
2208 ota_activity_versions OAV
2209 WHERE OAV.activity_version_id = p_activity_version_id
2210 AND OFR.activity_version_id = OAV.activity_version_id
2211 AND OEV.parent_offering_id = OFR.offering_id
2212 AND OEV.business_group_id = p_business_group_id
2213 AND OEV.event_type IN ('SCHEDULED','SELFPACED')
2214 AND OEV.book_independent_flag = 'N'
2215 AND OEV.Event_status in('N','P','F')
2216 AND OFR.delivery_mode_id = OCU.category_usage_id
2217 AND OCU.type ='DM'
2218 AND trunc(sysdate) BETWEEN nvl(OAV.start_date, trunc(sysdate)) AND nvl(OAV.end_date, trunc(sysdate+1))
2219 AND ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), p_server_timezone_code, OEV.timezone)
2220 BETWEEN to_date(to_char(nvl(OEV.enrolment_start_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '00:00' , 'YYYY/MM/DD HH24:MI')
2221 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')
2222 ORDER BY OEV.public_event_flag DESC;
2223
2224 CURSOR csr_normal_classes_in_course(
2225 p_activity_version_id in ota_activity_versions.activity_version_id%type,
2226 p_business_group_id ota_events.business_group_id%type,
2227 p_server_timezone_code fnd_timezones_b.timezone_code%type) is
2228 SELECT OEV.event_id,
2229 OEV.public_event_flag,
2230 OEV.maximum_internal_attendees,
2231 OEV.course_start_date
2232 FROM ota_category_usages OCU,
2233 ota_events OEV,
2234 ota_offerings OFR,
2235 ota_activity_versions OAV
2236 WHERE OAV.activity_version_id = p_activity_version_id
2237 AND OFR.activity_version_id = OAV.activity_version_id
2238 AND OEV.parent_offering_id = OFR.offering_id
2239 AND OEV.business_group_id = p_business_group_id
2240 AND OEV.event_type IN ('SCHEDULED','SELFPACED')
2241 AND OEV.book_independent_flag = 'N'
2242 AND OEV.Event_status in('N','P','F')
2243 AND OFR.delivery_mode_id = OCU.category_usage_id
2244 AND OCU.type ='DM'
2245 AND trunc(sysdate) BETWEEN nvl(OAV.start_date, trunc(sysdate)) AND nvl(OAV.end_date, trunc(sysdate+1))
2246 AND ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), p_server_timezone_code, OEV.timezone)
2247 BETWEEN to_date(to_char(nvl(OEV.enrolment_start_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '00:00' , 'YYYY/MM/DD HH24:MI')
2248 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')
2249 AND nvl(OEV.event_availability, 'ALL') = 'ALL'
2250 ORDER BY OEV.public_event_flag DESC;
2251
2252 l_business_group_id ota_events.business_group_id%type;
2253 l_server_timezone_code fnd_timezones_b.timezone_code%type;
2254 begin
2255
2256 l_business_group_id := ota_general.get_business_group_id;
2257 l_server_timezone_code := ota_timezone_util.get_server_timezone_code;
2258
2259 if(showLPOnlyClasses = 'Y') then
2260 for assoc in csr_classes_in_course(p_activity_version_id, l_business_group_id, l_server_timezone_code) loop
2261 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
2262 return 'Y';
2263 end if;
2264 end loop;
2265 else
2266 for assoc in csr_normal_classes_in_course(p_activity_version_id, l_business_group_id, l_server_timezone_code) loop
2267 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
2268 return 'Y';
2269 end if;
2270 end loop;
2271 end if;
2272
2273 return 'N';
2274 end learner_has_access_to_course;
2275
2276 /*
2277 Always use this procedure to check learner access for a person as this
2278 taking care the whole hierarchy for a user group.
2279 If there is any change in this procedure make sure, the changes are
2280 done in the procedure get_ug_whereclause
2281 */
2282 function is_learner_in_user_group(
2283 p_person_id in per_people_f.person_id%type,
2284 p_user_group_id in ota_user_groups_b.user_group_id%type,
2285 p_business_group_id number,
2286 p_ignore_ug_date_check varchar2 default 'N') return varchar2 is
2287
2288 is_avail BOOLEAN default false;
2289 ug_index NUMBER := 1;
2290 begin
2291
2292 for ug_hierarchy in csr_ug_hierarchy(p_user_group_id, p_business_group_id, p_ignore_ug_date_check) loop
2293
2294 is_avail := is_learner_matches_user_group(p_person_id, ug_hierarchy.child_user_group_id, p_business_group_id, p_ignore_ug_date_check);
2295
2296 person_ug_map_rec_table(ug_index).person_id := p_person_id;
2297 person_ug_map_rec_table(ug_index).user_group_id := ug_hierarchy.child_user_group_id;
2298 person_ug_map_rec_table(ug_index).is_person_matches_ug := is_avail;
2299
2300 ug_index := ug_index+1;
2301
2302 end loop; --each user group in the hierarchy has been filled with true or false
2303
2304 --process for root
2305 is_avail := is_learner_matches_user_group(p_person_id, p_user_group_id, p_business_group_id, p_ignore_ug_date_check);
2306
2307 person_ug_map_rec_table(ug_index).person_id := p_person_id;
2308 person_ug_map_rec_table(ug_index).user_group_id := p_user_group_id;
2309 person_ug_map_rec_table(ug_index).is_person_matches_ug := is_avail;
2310
2311 if(is_avail = true) then
2312 hr_utility.set_location( 'Returning Y ',130);
2313 return 'Y';
2314 else
2315 hr_utility.set_location( 'Returning N ',130);
2316 return 'N';
2317 end if;
2318
2319 end is_learner_in_user_group;
2320
2321 function is_learner_matches_user_group(
2322 p_person_id in per_people_f.person_id%type,
2323 p_user_group_id in ota_user_groups_b.user_group_id%type,
2324 p_business_group_id number,
2325 p_ignore_ug_date_check varchar2 default 'N') return boolean is
2326
2327 l_user_group_operator ota_user_groups_b.user_group_operator%type;
2328 l_person_id per_people_f.person_id%type;
2329 l_user_group_id ota_user_groups_b.user_group_id%type;
2330 l_is_avail BOOLEAN default false;
2331
2332 begin
2333 for elements in csr_user_group_elements(p_user_group_id, p_business_group_id, p_ignore_ug_date_check) loop
2334
2335 l_user_group_operator := elements.user_group_operator;
2336 hr_utility.set_location( 'Entered ota_learner_access_util.is_learner_matches_user_group ',130);
2337 hr_utility.set_location( 'p_person_id :'||p_person_id,130);
2338 hr_utility.set_location( 'p_user_group_id :'||p_user_group_id,130);
2339 hr_utility.set_location( 'l_user_group_operator :'||l_user_group_operator,130);
2340 hr_utility.set_location( 'elements.person_id :'|| elements.person_id,130);
2341 hr_utility.set_location( 'elements.elig_prfl_id :'||elements.elig_prfl_id,130);
2342
2343 if elements.user_group_operator is not null and elements.user_group_operator = 'AND' then
2344 if elements.person_id is not null and elements.person_id <> p_person_id then
2345 hr_utility.set_location( 'elements.person_id is not null and elements.person_id <> p_person_id',130);
2346 return false;
2347 elsif elements.elig_prfl_id is not null then
2348 hr_utility.set_location( 'elements.elig_prfl_id is not null',130);
2349 if ota_elig_profile_util.check_processing_status(p_user_group_id) <> 'A' then --by default return false for AND
2350 hr_utility.set_location( 'processing_status <> A returns false',130);
2351 return false;
2352 else
2353 --if record doesnot exist in resolved data return false
2354 if ota_elig_profile_util.is_learner_in_resolved_ug_data(p_user_group_id,p_person_id, 'A')= 'N' then
2355 hr_utility.set_location( 'learner not in resolved data return true',130);
2356 return false;
2357 end if;
2358 end if;
2359
2360 elsif elements.child_user_group_id is not null then
2361 hr_utility.set_location( 'elements.child_user_group_id is not null',130);
2362
2363 for i in 1 .. person_ug_map_rec_table.count loop
2364 l_person_id := person_ug_map_rec_table(i).person_id;
2365 l_user_group_id := person_ug_map_rec_table(i).user_group_id;
2366 l_is_avail := person_ug_map_rec_table(i).is_person_matches_ug;
2367
2368 hr_utility.set_location( 'l_person_id :'||l_person_id,130);
2369 hr_utility.set_location( 'l_user_group_id :'||l_user_group_id,130);
2370 if( l_person_id is not null and l_person_id = p_person_id and
2371 l_user_group_id is not null and l_user_group_id = elements.child_user_group_id and
2372 l_is_avail is not null and l_is_avail = false) then
2373 hr_utility.set_location( 'if satisfied so false',130);
2374 return false;
2375 end if;
2376 end loop;
2377 else
2378 hr_utility.set_location( 'assignment match',130);
2379 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
2380 return false;
2381 end if;
2382 end if;
2383
2384 else
2385 if elements.person_id is not null and elements.person_id = p_person_id then
2386 return true;
2387 elsif elements.elig_prfl_id is not null then
2388 if ota_elig_profile_util.check_processing_status(p_user_group_id) <> 'A' then --by default return true for OR
2389 return true;
2390 else
2391 --if record exists in resolved data return true
2392 if ota_elig_profile_util.is_learner_in_resolved_ug_data(p_user_group_id,p_person_id, 'A')= 'Y' then
2393 return true;
2394 end if;
2395 end if;
2396
2397 elsif elements.child_user_group_id is not null then
2398
2399 for i in 1 .. person_ug_map_rec_table.count loop
2400 l_person_id := person_ug_map_rec_table(i).person_id;
2401 l_user_group_id := person_ug_map_rec_table(i).user_group_id;
2402 l_is_avail := person_ug_map_rec_table(i).is_person_matches_ug;
2403
2404 if( l_person_id is not null and l_person_id = p_person_id and
2405 l_user_group_id is not null and l_user_group_id = elements.child_user_group_id and
2406 l_is_avail is not null and l_is_avail = true) then
2407
2408 return true;
2409 end if;
2410 end loop;
2411 else
2412 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
2413 return true;
2414 end if;
2415 end if;
2416 end if;
2417 end loop;
2418
2419 hr_utility.set_location( 'Done with everything',130);
2420 if l_user_group_operator is not null and l_user_group_operator = 'AND' then
2421 hr_utility.set_location( 'Returning true from is_learner_matches_user_group ',130);
2422 return true;
2423 else
2424 return false;
2425 end if;
2426
2427 end is_learner_matches_user_group;
2428
2429 /*
2430 Always use this procedure to get where clause for a user group as this
2431 taking care the whole hierarchy for a user group.
2432 If there is any change in this procedure make sure, the changes are
2433 done in the procedure is_learner_in_user_group
2434 */
2435 function get_ug_whereclause(
2436 p_user_group_id in ota_user_groups_b.user_group_id%type,
2437 p_business_group_id number) return clob is
2438
2439 whereclause clob;
2440 ug_index NUMBER := 1;
2441 begin
2442
2443 for ug_hierarchy in csr_ug_hierarchy(p_user_group_id, p_business_group_id) loop
2444
2445 whereclause := build_ug_whereclause(ug_hierarchy.child_user_group_id, p_business_group_id);
2446
2447 t_ug_learner_list_table(ug_index).user_group_id := ug_hierarchy.child_user_group_id;
2448 t_ug_learner_list_table(ug_index).ugwhereclause := whereclause;
2449
2450 ug_index := ug_index+1;
2451
2452 end loop; --each user group in the hierarchy has been filled with where clause
2453
2454 --process root
2455 whereclause := build_ug_whereclause(p_user_group_id, p_business_group_id);
2456 t_ug_learner_list_table(ug_index).user_group_id := p_user_group_id;
2457 t_ug_learner_list_table(ug_index).ugwhereclause := whereclause;
2458
2459 if (whereclause is null) then -- Bug#6835942
2460 whereclause := ' (1=2) ';
2461 end if;
2462
2463 return whereclause;
2464 end get_ug_whereclause;
2465
2466 function build_ug_whereclause(
2467 p_user_group_id in ota_user_groups_b.user_group_id%type,
2468 p_business_group_id number) return clob is
2469
2470 l_user_group_operator ota_user_groups_b.user_group_operator%type;
2471 l_user_group_id ota_user_groups_b.user_group_id%type;
2472 whereclause clob;
2473 childugwhereclause clob;
2474 ugOperatorCheckCount number := 0;
2475 orgHierarchyCount number := 0;
2476 l_status varchar2(1);
2477
2478 begin
2479 for elements in csr_user_group_elements(p_user_group_id, p_business_group_id) loop
2480
2481 l_user_group_operator := elements.user_group_operator;
2482
2483 if (ugOperatorCheckCount = 0) then
2484 whereclause := ' ( ';
2485 end if;
2486
2487 if (ugOperatorCheckCount > 0 and length(trim(whereclause)) > 1 and elements.child_user_group_id is null) then
2488 whereclause := whereclause || ' ' || l_user_group_operator || ' ';
2489 end if;
2490
2491 if elements.person_id is not null then --person
2492 -- whereclause := whereclause || ' person_id = ' || elements.person_id || ' '; bug 11849651
2493 whereclause := whereclause || ' person_id = ' || TO_CHAR(elements.person_id) || ' ';
2494 else --child user group
2495 if elements.child_user_group_id is not null then --
2496 for i in 1 .. t_ug_learner_list_table.count loop
2497 l_user_group_id := t_ug_learner_list_table(i).user_group_id;
2498 childugwhereclause := t_ug_learner_list_table(i).ugwhereclause;
2499
2500 if( l_user_group_id is not null and l_user_group_id = elements.child_user_group_id and whereclause is not null ) then
2501 if(ugOperatorCheckCount > 0 and length(trim(whereclause)) > 1 and childugwhereclause is not null) then
2502 whereclause := whereclause || ' ' || l_user_group_operator || ' ';
2503 end if;
2504 whereclause := whereclause || ' ' || childugwhereclause || ' ';
2505 exit;
2506 end if;
2507 end loop;
2508 elsif elements.elig_prfl_id is not null then --Eligibility profile
2509 l_status := ota_elig_profile_util.check_processing_status(p_user_group_id);
2510 if l_status = 'A' then --user group is not under processing of a conc program
2511 whereclause := whereclause || ' person_id in (select person_id from ota_user_group_elements where ';
2512 whereclause := whereclause || 'user_group_id = '||TO_CHAR(elements.user_group_id);
2513 whereclause := whereclause || ' and elig_prfl_id is not null';
2514 whereclause := whereclause || ' and person_id is not null and nvl( processing_status,''A'') = ''A'' )';
2515
2516 else
2517 whereclause := whereclause || ' (1=2) ';--Under processing OR error state
2518 end if;
2519 else --Assignment
2520 whereclause := whereclause || ' ( ' ;
2521
2522 if(elements.job_id is not null) then
2523 -- whereclause := whereclause || ' job_id = ' || elements.job_id;
2524 whereclause := whereclause || ' job_id = ' ||TO_CHAR(elements.job_id);
2525 if(elements.position_id is not null or elements.organization_id is not null) then
2526 whereclause := whereclause || ' and ';
2527 end if;
2528 end if;
2529
2530 if(elements.position_id is not null) then
2531 -- whereclause := whereclause || ' position_id = ' || elements.position_id;
2532 whereclause := whereclause || ' position_id = ' || TO_CHAR(elements.position_id);
2533 if(elements.organization_id is not null) then
2534 whereclause := whereclause || ' and ';
2535 end if;
2536 end if;
2537
2538 if(elements.organization_id is not null) then
2539 if(elements.org_structure_version_id is null) then
2540 -- whereclause := whereclause || ' organization_id = ' || elements.organization_id;
2541 whereclause := whereclause || ' organization_id = ' || TO_CHAR(elements.organization_id);
2542 else
2543 orgHierarchyCount := 0;
2544 whereclause := whereclause || ' organization_id in ( ';
2545 for orghierarchy in csr_org_hierarchy(elements.organization_id, elements.org_structure_version_id) loop
2546 if(orgHierarchyCount > 0) then
2547 whereclause := whereclause || ' , ';
2548 end if;
2549 whereclause := whereclause || TO_CHAR(orghierarchy.organization_id);
2550 orgHierarchyCount := orgHierarchyCount+1;
2551 end loop;
2552 whereclause := whereclause || ' ) ';
2553 end if;
2554 end if;
2555
2556 whereclause := whereclause || ' ) ' ;
2557
2558 end if;
2559 end if;
2560 ugOperatorCheckCount := ugOperatorCheckCount+1;
2561 end loop;
2562
2563 if (ugOperatorCheckCount > 0) then
2564 if (whereclause is not null and length(trim(whereclause)) > 1) then -- Bug#6835942
2565 whereclause := whereclause || ' ) ';
2566 else
2567 whereclause := null;
2568 end if;
2569 end if;
2570
2571 return whereclause;
2572
2573 end build_ug_whereclause;
2574
2575 function is_full_access_learner_group(p_user_group_id in ota_user_groups_b.user_group_id%type,
2576 p_business_group_id number) return varchar2 is
2577
2578 total_elements_count NUMBER := -2;
2579 restricted_elements_count NUMBER := -1;
2580
2581 BEGIN
2582
2583 SELECT count(user_group_element_id) into total_elements_count
2584 FROM OTA_USER_GROUP_ELEMENTS
2585 WHERE user_group_id in ( SELECT child_user_group_id
2586 FROM ( SELECT a.child_user_group_id
2587 FROM ota_user_group_elements a,
2588 ota_user_groups_b b
2589 WHERE a.user_group_id = b.user_group_id
2590 and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate)) and
2591 trunc(nvl(b.end_date_active, sysdate+1))
2592 START WITH a.user_group_id = p_user_group_id
2593 CONNECT BY PRIOR a.child_user_group_id = a.user_group_id
2594 UNION ALL SELECT p_user_group_id FROM dual
2595 ) WHERE child_user_group_id is not null ) and
2596 (fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID') is not null
2597 or business_group_id = p_business_group_id)
2598 and( person_id is null or elig_prfl_id is null);
2599
2600 SELECT count(a.user_group_element_id) into restricted_elements_count
2601 FROM OTA_USER_GROUP_ELEMENTS a,
2602 PER_PEOPLE_F e,
2603 PER_JOBS_VL job,
2604 HR_ORGANIZATION_UNITS org,
2605 PER_POSITIONS pos,
2606 BEN_ELIGY_PRFL_F elig_profile
2607 WHERE a.user_group_id in ( SELECT child_user_group_id
2608 FROM ( SELECT a.child_user_group_id
2609 FROM ota_user_group_elements a,
2610 ota_user_groups_b b
2611 WHERE a.user_group_id = b.user_group_id
2612 and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate)) and
2613 trunc(nvl(b.end_date_active, sysdate+1))
2614 START WITH a.user_group_id = p_user_group_id
2615 CONNECT BY PRIOR a.child_user_group_id = a.user_group_id
2616 UNION ALL SELECT p_user_group_id FROM dual
2617 ) WHERE child_user_group_id is not null ) and
2618 e.person_id(+) = a.person_id and
2619 (e.effective_start_date is null or e.effective_start_date <= trunc(sysdate)) and
2620 (e.effective_end_date is null or trunc(sysdate) <= e.effective_end_date) and
2621 job.job_id(+) = a.job_id and
2622 org.organization_id(+) = a.organization_id and
2623 pos.position_id(+) = a.position_id and
2624 (fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID') is not null or
2625 a.business_group_id = p_business_group_id) and
2626 -- decode (a.position_id, null, -1, pos.organization_id) = nvl(org.organization_id,-1) and --7157831
2627 decode (a.position_id, null, -1, pos.organization_id) = decode(a.position_id, null, -1, org.organization_id) and --7248298
2628 (e.person_id is not null or job.job_id is not null or pos.position_id is not null or
2629 org.organization_id is not null or a.child_user_group_id is not null or elig_profile.eligy_prfl_id is not null)
2630 and elig_profile.eligy_prfl_id(+) = a.elig_prfl_id
2631 and (elig_profile.effective_start_date is null or trunc(elig_profile.effective_start_date) <= trunc(sysdate)) and
2632 (elig_profile.effective_end_date is null or trunc(sysdate) <= trunc(elig_profile.effective_end_date))
2633 and (a.person_id is null or a.elig_prfl_id is null);
2634
2635 if (total_elements_count = 0) then --7157831
2636 return 'N';
2637 else
2638 if( (total_elements_count - restricted_elements_count) = 0) then
2639 return 'Y';
2640 else
2641 return 'N';
2642 end if;
2643 end if;
2644 end is_full_access_learner_group;
2645
2646 end ota_learner_access_util;
[Home] [Help]