[Home] [Help]
PACKAGE: APPS.OTA_LEARNER_ACCESS_UTIL
Source
1 package ota_learner_access_util as
2 /* $Header: otlrnacc.pkh 120.7 2008/05/30 08:01:00 pekasi noship $ */
3
4
5 --|--------------------------------------------------------------------------|
6 --|--< CHK_DELEGATE_OK_FOR_EVENT>-------------------------------------------|
7 --|--------------------------------------------------------------------------|
8 TYPE person_ug_map IS RECORD (
9 person_id NUMBER
10 ,user_group_id NUMBER
11 ,is_person_matches_ug BOOLEAN
12 );
13
14 TYPE person_ug_map_table IS TABLE OF person_ug_map INDEX BY BINARY_INTEGER;
15
16 TYPE ug_learner_list IS RECORD (
17 user_group_id NUMBER
18 ,ugwhereclause CLOB
19 );
20
21 TYPE ug_learner_list_table IS TABLE OF ug_learner_list INDEX BY BINARY_INTEGER;
22
23 function learner_can_enroll(
24 p_person_id in per_people_f.person_id%type,
25 p_party_id in hz_parties.party_id%type,
26 p_event_id in ota_events.event_id%type,
27 p_public_event_flag in ota_events.public_event_flag%type,
28 p_max_internal in ota_events.maximum_internal_attendees%type,
29 p_event_start_date in otv_scheduled_events.course_start_date%type,
30 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2;
31
32
33 function learner_can_enroll(
34 p_person_id in per_people_f.person_id%type,
35 p_party_id in hz_parties.party_id%type,
36 p_event_id in ota_events.event_id%type,
37 p_event_start_date in otv_scheduled_events.course_start_date%type,
38 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2;
39
40 function learner_can_enroll(
41 p_person_id in per_people_f.person_id%type,
42 p_party_id in hz_parties.party_id%type,
43 p_event_id in ota_events.event_id%type) return varchar2;
44
45 function learner_can_self_enroll(
46 p_person_id in per_people_f.person_id%type,
47 p_party_id in hz_parties.party_id%type,
48 p_event_id in ota_events.event_id%type,
49 p_public_event_flag in ota_events.public_event_flag%type,
50 p_max_internal in ota_events.maximum_internal_attendees%type,
51 p_event_start_date in otv_scheduled_events.course_start_date%type,
52 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2;
53
54
55 function learner_can_self_enroll(
56 p_person_id in per_people_f.person_id%type,
57 p_party_id in hz_parties.party_id%type,
58 p_event_id in ota_events.event_id%type,
59 p_event_start_date in otv_scheduled_events.course_start_date%type,
60 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2;
61
62 function learner_can_self_enroll(
63 p_person_id in per_people_f.person_id%type,
64 p_party_id in hz_parties.party_id%type,
65 p_event_id in ota_events.event_id%type) return varchar2;
66
67 function employee_can_enroll(
68 p_person_id in per_people_f.person_id%type,
69 p_event_id in ota_events.event_id%type,
70 p_public_event_flag ota_events.public_event_flag%type,
71 p_max_internal in ota_events.maximum_internal_attendees%type,
72 p_event_start_date in otv_scheduled_events.course_start_date%type,
73 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2;
74
75
76 function ext_learner_can_enroll(
77 p_party_id in hz_parties.party_id%type,
78 p_event_id in ota_events.event_id%type,
79 p_public_event_flag ota_events.public_event_flag%type,
80 p_max_internal in ota_events.maximum_internal_attendees%type,
81 p_event_start_date in otv_scheduled_events.course_start_date%type,
82 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2;
83
84
85 function chk_delegate_ok_for_event(
86 p_delegate_id in per_people_f.person_id%type,
87 p_event_id in ota_events.event_id%type,
88 p_event_start_date in otv_scheduled_events.course_start_date%type,
89 p_parent_offering_id in ota_events.parent_offering_id%type default null) return varchar2;
90
91
92 function learner_can_see_category(
93 p_person_id in per_people_f.person_id%type,
94 p_party_id in hz_parties.party_id%type,
95 p_category_usage_id in ota_category_usages.category_usage_id%type) return varchar2;
96
97
98 function learner_can_see_course(
99 p_person_id in per_people_f.person_id%type,
100 p_activity_version_id in ota_activity_versions.activity_version_id%type) return varchar2;
101
102
103 function learner_can_see_offering(
104 p_person_id in per_people_f.person_id%type,
105 p_offering_id in ota_offerings.offering_id%type) return varchar2;
106
107
108 function learner_can_enroll_in_path(
109 p_person_id in per_people_f.person_id%type,
110 p_party_id in hz_parties.party_id%type,
111 p_learning_path_id in ota_learning_paths.learning_path_id%type,
112 p_public_flag ota_learning_paths.public_flag%type,
113 p_start_date_active in ota_learning_paths.start_date_active%type,
114 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
115
116
117 function learner_can_enroll_in_path(
118 p_user_id in fnd_user.user_id%type,
119 p_learning_path_id in ota_learning_paths.learning_path_id%type,
120 p_public_flag ota_learning_paths.public_flag%type,
121 p_start_date_active in ota_learning_paths.start_date_active%type,
122 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
123
124
125 function emp_can_enroll_in_path(
126 p_person_id in per_people_f.person_id%type,
127 p_learning_path_id in ota_learning_paths.learning_path_id%type,
128 p_public_flag ota_learning_paths.public_flag%type,
129 p_start_date_active in ota_learning_paths.start_date_active%type,
130 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
131
132
133 function ext_can_enroll_in_path(
134 p_party_id in hz_parties.party_id%type,
135 p_learning_path_id in ota_learning_paths.learning_path_id%type,
136 p_public_flag ota_learning_paths.public_flag%type,
137 p_start_date_active in ota_learning_paths.start_date_active%type,
138 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
139
140
141 function lrn_can_self_enroll_in_path(
142 p_person_id in per_people_f.person_id%type,
143 p_party_id in hz_parties.party_id%type,
144 p_learning_path_id in ota_learning_paths.learning_path_id%type,
145 p_public_flag ota_learning_paths.public_flag%type,
146 p_start_date_active in ota_learning_paths.start_date_active%type,
147 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
148
149
150 function emp_can_self_enroll_in_path(
151 p_person_id in per_people_f.person_id%type,
152 p_learning_path_id in ota_learning_paths.learning_path_id%type,
153 p_public_flag ota_learning_paths.public_flag%type,
154 p_start_date_active in ota_learning_paths.start_date_active%type,
155 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
156
157
158 function ext_can_self_enroll_in_path(
159 p_party_id in hz_parties.party_id%type,
160 p_learning_path_id in ota_learning_paths.learning_path_id%type,
161 p_public_flag ota_learning_paths.public_flag%type,
162 p_start_date_active in ota_learning_paths.start_date_active%type,
163 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
164
165 function learner_can_enroll_in_forum(
166 p_person_id in per_people_f.person_id%type,
167 p_party_id in hz_parties.party_id%type,
168 p_forum_id in ota_forums_b.forum_id%type,
169 p_public_flag ota_forums_b.public_flag%type,
170 p_start_date_active in ota_forums_b.start_date_active%type,
171 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
172
173
174 function learner_can_enroll_in_forum(
175 p_user_id in fnd_user.user_id%type,
176 p_forum_id in ota_forums_b.forum_id%type,
177 p_public_flag ota_forums_b.public_flag%type,
178 p_start_date_active in ota_forums_b.start_date_active%type,
179 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
180
181
182 function emp_can_enroll_in_forum(
183 p_person_id in per_people_f.person_id%type,
184 p_forum_id in ota_forums_b.forum_id%type,
185 p_public_flag ota_forums_b.public_flag%type,
186 p_start_date_active in ota_forums_b.start_date_active%type,
187 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
188
189
190 function ext_can_enroll_in_forum(
191 p_party_id in hz_parties.party_id%type,
192 p_forum_id in ota_forums_b.forum_id%type,
193 p_public_flag ota_forums_b.public_flag%type,
194 p_start_date_active in ota_forums_b.start_date_active%type,
195 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
196
197
198 function lrn_can_self_enroll_in_forum(
199 p_person_id in per_people_f.person_id%type,
200 p_party_id in hz_parties.party_id%type,
201 p_forum_id in ota_forums_b.forum_id%type,
202 p_public_flag ota_forums_b.public_flag%type,
203 p_start_date_active in ota_forums_b.start_date_active%type,
204 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
205
206
207 function emp_can_self_enroll_in_forum(
208 p_person_id in per_people_f.person_id%type,
209 p_forum_id in ota_forums_b.forum_id%type,
210 p_public_flag ota_forums_b.public_flag%type,
211 p_start_date_active in ota_forums_b.start_date_active%type,
212 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
213
214
215 function ext_can_self_enroll_in_forum(
216 p_party_id in hz_parties.party_id%type,
217 p_forum_id in ota_forums_b.forum_id%type,
218 p_public_flag ota_forums_b.public_flag%type,
219 p_start_date_active in ota_forums_b.start_date_active%type,
220 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
221
222 function learner_can_enroll_in_chat(
223 p_person_id in per_people_f.person_id%type,
224 p_party_id in hz_parties.party_id%type,
225 p_chat_id in ota_chats_b.chat_id%type,
226 p_public_flag ota_chats_b.public_flag%type,
227 p_start_date_active in ota_chats_b.start_date_active%type,
228 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
229
230
231 function learner_can_enroll_in_chat(
232 p_user_id in fnd_user.user_id%type,
233 p_chat_id in ota_chats_b.chat_id%type,
234 p_public_flag ota_chats_b.public_flag%type,
235 p_start_date_active in ota_chats_b.start_date_active%type,
236 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
237
238
239 function emp_can_enroll_in_chat(
240 p_person_id in per_people_f.person_id%type,
241 p_chat_id in ota_chats_b.chat_id%type,
242 p_public_flag ota_chats_b.public_flag%type,
243 p_start_date_active in ota_chats_b.start_date_active%type,
244 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
245
246
247 function ext_can_enroll_in_chat(
248 p_party_id in hz_parties.party_id%type,
249 p_chat_id in ota_chats_b.chat_id%type,
250 p_public_flag ota_chats_b.public_flag%type,
251 p_start_date_active in ota_chats_b.start_date_active%type,
252 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
253
254
255 function lrn_can_self_enroll_in_chat(
256 p_person_id in per_people_f.person_id%type,
257 p_party_id in hz_parties.party_id%type,
258 p_chat_id in ota_chats_b.chat_id%type,
259 p_public_flag ota_chats_b.public_flag%type,
260 p_start_date_active in ota_chats_b.start_date_active%type,
261 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
262
263
264 function emp_can_self_enroll_in_chat(
265 p_person_id in per_people_f.person_id%type,
266 p_chat_id in ota_chats_b.chat_id%type,
267 p_public_flag ota_chats_b.public_flag%type,
268 p_start_date_active in ota_chats_b.start_date_active%type,
269 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
270
271
272 function ext_can_self_enroll_in_chat(
273 p_party_id in hz_parties.party_id%type,
274 p_chat_id in ota_chats_b.chat_id%type,
275 p_public_flag ota_chats_b.public_flag%type,
276 p_start_date_active in ota_chats_b.start_date_active%type,
277 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
278
279 function learner_can_enroll_in_cert(
280 p_person_id in per_people_f.person_id%type,
281 p_party_id in hz_parties.party_id%type,
282 p_certification_id in ota_certifications_b.certification_id%type,
283 p_public_flag ota_certifications_b.public_flag%type,
284 p_start_date_active in ota_certifications_b.start_date_active%type,
285 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
286
287
288 function learner_can_enroll_in_cert(
289 p_user_id in fnd_user.user_id%type,
290 p_certification_id in ota_certifications_b.certification_id%type,
291 p_public_flag ota_certifications_b.public_flag%type,
292 p_start_date_active in ota_certifications_b.start_date_active%type,
293 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
294
295
296 function emp_can_enroll_in_cert(
297 p_person_id in per_people_f.person_id%type,
298 p_certification_id in ota_certifications_b.certification_id%type,
299 p_public_flag ota_certifications_b.public_flag%type,
300 p_start_date_active in ota_certifications_b.start_date_active%type,
301 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
302
303
304 function ext_can_enroll_in_cert(
305 p_party_id in hz_parties.party_id%type,
306 p_certification_id in ota_certifications_b.certification_id%type,
307 p_public_flag ota_certifications_b.public_flag%type,
308 p_start_date_active in ota_certifications_b.start_date_active%type,
309 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
310
311
312 function lrn_can_self_enroll_in_cert(
313 p_person_id in per_people_f.person_id%type,
314 p_party_id in hz_parties.party_id%type,
315 p_certification_id in ota_certifications_b.certification_id%type,
316 p_public_flag ota_certifications_b.public_flag%type,
317 p_start_date_active in ota_certifications_b.start_date_active%type,
318 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
319
320
321 function emp_can_self_enroll_in_cert(
322 p_person_id in per_people_f.person_id%type,
323 p_certification_id in ota_certifications_b.certification_id%type,
324 p_public_flag ota_certifications_b.public_flag%type,
325 p_start_date_active in ota_certifications_b.start_date_active%type,
326 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
327
328
329 function ext_can_self_enroll_in_cert(
330 p_party_id in hz_parties.party_id%type,
331 p_certification_id in ota_certifications_b.certification_id%type,
332 p_public_flag ota_certifications_b.public_flag%type,
333 p_start_date_active in ota_certifications_b.start_date_active%type,
334 p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2;
335
336 function learner_can_enroll_in_path(
337 p_learning_path_id in ota_learning_paths.learning_path_id%TYPE
338 ,p_person_id in per_all_people_f.person_id%type
339 ,p_party_id in hz_parties.party_id%type) return varchar2;
340
341 function learner_can_enroll_in_cert(
342 p_certification_id in ota_certifications_b.certification_id%TYPE
343 ,p_person_id in per_all_people_f.person_id%type
344 ,p_party_id in hz_parties.party_id%type) return varchar2;
345
346 FUNCTION emp_has_valid_dates(p_person_id per_all_people_f.person_id%TYPE
347 ,p_event_id ota_events.event_id%TYPE) RETURN VARCHAR2;
348 function get_event_start_date(
349 p_event_id ota_events.event_id%type,
350 p_date date) return date;
351
352 FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
353 p_organization_id IN ota_event_associations.organization_id%type,
354 p_person_id IN per_people_f.person_id%type)
355 RETURN VARCHAR2;
356 function learner_has_access_to_course(
357 p_person_id in per_people_f.person_id%type,
358 p_party_id in hz_parties.party_id%type,
359 p_activity_version_id in ota_activity_versions.activity_version_id%type) return varchar2;
360
361 function is_learner_in_user_group(
362 p_person_id in per_people_f.person_id%type,
363 p_user_group_id in ota_user_groups_b.user_group_id%type,
364 p_business_group_id number) return varchar2;
365
366 function is_learner_matches_user_group(
367 p_person_id in per_people_f.person_id%type,
368 p_user_group_id in ota_user_groups_b.user_group_id%type,
369 p_business_group_id number) return boolean;
370
371 function build_ug_whereclause(
372 p_user_group_id in ota_user_groups_b.user_group_id%type,
373 p_business_group_id number) return clob;
374
375 function get_ug_whereclause(
376 p_user_group_id in ota_user_groups_b.user_group_id%type,
377 p_business_group_id number) return clob;
378
379 function is_full_access_learner_group(p_user_group_id in ota_user_groups_b.user_group_id%type,
380 p_business_group_id number) return varchar2;
381
382 end ota_learner_access_util;