[Home] [Help]
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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;
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
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;
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:
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 ;
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;
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
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;
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
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,
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.
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);
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:
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;
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.
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;
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
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,
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.
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);
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;
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
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,
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.
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);
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
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.
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
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.
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
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
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
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
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
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,
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.
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);
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
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,
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.
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);
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
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.
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
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.
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
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
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
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
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
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,
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.
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);
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
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,
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.
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);
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
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.
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
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.
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
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
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
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
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;
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
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,
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.
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);
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;
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
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,
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.
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);
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
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.
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
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.
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
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
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
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
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
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