[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
535: OEV.course_start_date
536: FROM ota_activity_versions_vl OAV,
537: ota_events_vl OEV,
538: ota_offerings_vl OFR,
539: ota_category_usages OCU
540: WHERE OFR.activity_version_id = OAV.activity_version_id
541: AND OEV.parent_offering_id = OFR.offering_id
542: AND OEV.business_group_id = ota_general.get_business_group_id
543: AND OFR.delivery_mode_id = OCU.category_usage_id
629: l_event_start_date ota_events.course_start_date%type;
630: --l_date date := trunc(p_date);
631:
632: l_start_date ota_events.course_start_date%type;
633: l_synchronous_flag ota_category_usages.synchronous_flag%type;
634: l_event_status ota_events.event_status%type;
635: l_event_end_date ota_events.course_end_date%type;
636: l_event_end_time ota_events.course_end_time%type;
637: l_timezone ota_events.timezone%type;
642: select oev.course_start_date, oev.course_end_date,ocu.synchronous_flag, oev.event_status
643: ,oev.timezone , oev.course_end_time
644: into l_event_start_date,l_event_end_date,l_synchronous_flag, l_event_status
645: ,l_timezone , l_event_end_time
646: from ota_events oev, ota_offerings ofr, ota_category_usages ocu
647: Where oev.event_id = p_event_id and
648: oev.parent_offering_id = ofr.offering_id and
649: ofr.delivery_mode_id = ocu.category_usage_id and
650: event_type in ('SCHEDULED','SELFPACED') and
818: l_event_start_date ota_events.course_start_date%type;
819: l_now date := sysdate;
820: l_offering_id ota_offerings.offering_id%type;
821: l_activity_version_id ota_activity_versions.activity_version_id%type;
822: l_category_usage_id ota_category_usages.category_usage_id%type;
823: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
824: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
825: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
826: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
819: l_now date := sysdate;
820: l_offering_id ota_offerings.offering_id%type;
821: l_activity_version_id ota_activity_versions.activity_version_id%type;
822: l_category_usage_id ota_category_usages.category_usage_id%type;
823: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
824: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
825: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
826: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
827:
820: l_offering_id ota_offerings.offering_id%type;
821: l_activity_version_id ota_activity_versions.activity_version_id%type;
822: l_category_usage_id ota_category_usages.category_usage_id%type;
823: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
824: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
825: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
826: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
827:
828: l_is_mandatory_enr_assoc boolean:= false ;
821: l_activity_version_id ota_activity_versions.activity_version_id%type;
822: l_category_usage_id ota_category_usages.category_usage_id%type;
823: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
824: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
825: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
826: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
827:
828: l_is_mandatory_enr_assoc boolean:= false ;
829: l_event_association_id ota_event_associations.event_association_id%type;
822: l_category_usage_id ota_category_usages.category_usage_id%type;
823: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
824: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
825: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
826: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
827:
828: l_is_mandatory_enr_assoc boolean:= false ;
829: l_event_association_id ota_event_associations.event_association_id%type;
830: begin
895: p_self_enroll_only in varchar2) return varchar2 is
896:
897: l_offering_id ota_offerings.offering_id%type;
898: l_activity_version_id ota_activity_versions.activity_version_id%type;
899: l_category_usage_id ota_category_usages.category_usage_id%type;
900: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
901: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
902: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
903: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
896:
897: l_offering_id ota_offerings.offering_id%type;
898: l_activity_version_id ota_activity_versions.activity_version_id%type;
899: l_category_usage_id ota_category_usages.category_usage_id%type;
900: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
901: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
902: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
903: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
904: begin
897: l_offering_id ota_offerings.offering_id%type;
898: l_activity_version_id ota_activity_versions.activity_version_id%type;
899: l_category_usage_id ota_category_usages.category_usage_id%type;
900: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
901: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
902: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
903: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
904: begin
905: -- Get the event's offering, course, and five levels of categories. If the category tree is deeper than that,
898: l_activity_version_id ota_activity_versions.activity_version_id%type;
899: l_category_usage_id ota_category_usages.category_usage_id%type;
900: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
901: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
902: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
903: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
904: begin
905: -- Get the event's offering, course, and five levels of categories. If the category tree is deeper than that,
906: -- we will have to use CONNECT BY below.
899: l_category_usage_id ota_category_usages.category_usage_id%type;
900: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
901: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
902: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
903: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
904: begin
905: -- Get the event's offering, course, and five levels of categories. If the category tree is deeper than that,
906: -- we will have to use CONNECT BY below.
907: open csr_evt_tree(p_event_id);
964: --Bug 547819
965: Cursor c_event_data is
966: Select OCU.synchronous_flag, OEV.event_status, OEV.secure_event_flag,
967: OEV.organization_id
968: From ota_events OEV, ota_offerings OFR, ota_category_usages OCU
969: Where OEV.event_id = p_event_id
970: And OEV.parent_offering_id = OFR.offering_id
971: And OFR.delivery_mode_id = OCU.category_usage_id;
972:
969: Where OEV.event_id = p_event_id
970: And OEV.parent_offering_id = OFR.offering_id
971: And OFR.delivery_mode_id = OCU.category_usage_id;
972:
973: l_synchronous_flag ota_category_usages.synchronous_flag%type;
974: l_effective_start_date PER_ALL_PEOPLE_F.effective_start_date%type;
975: l_event_status ota_events.event_status%type;
976: l_secure_event_flag ota_events.secure_event_flag%type;
977: l_organization_id ota_events.organization_id%type;
1196:
1197: function learner_can_see_category(
1198: p_person_id in per_people_f.person_id%type,
1199: p_party_id in hz_parties.party_id%type,
1200: p_category_usage_id in ota_category_usages.category_usage_id%type) return varchar2 is
1201:
1202: begin
1203: /*
1204: * MAC: stubbed out for performance.
1228: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1229: p_self_enroll_only in varchar2) return varchar2 is
1230:
1231: l_now date := sysdate;
1232: l_category_usage_id ota_category_usages.category_usage_id%type;
1233: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1234: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1235: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1236: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1229: p_self_enroll_only in varchar2) return varchar2 is
1230:
1231: l_now date := sysdate;
1232: l_category_usage_id ota_category_usages.category_usage_id%type;
1233: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1234: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1235: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1236: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1237: begin
1230:
1231: l_now date := sysdate;
1232: l_category_usage_id ota_category_usages.category_usage_id%type;
1233: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1234: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1235: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1236: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1237: begin
1238: -- Get the learning path's five levels of categories. If the category tree is deeper than that,
1231: l_now date := sysdate;
1232: l_category_usage_id ota_category_usages.category_usage_id%type;
1233: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1234: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1235: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1236: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1237: begin
1238: -- Get the learning path's five levels of categories. If the category tree is deeper than that,
1239: -- we will have to use CONNECT BY below.
1232: l_category_usage_id ota_category_usages.category_usage_id%type;
1233: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1234: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1235: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1236: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1237: begin
1238: -- Get the learning path's five levels of categories. If the category tree is deeper than that,
1239: -- we will have to use CONNECT BY below.
1240: open csr_lp_tree(p_learning_path_id);
1279: p_party_id in hz_parties.party_id%type,
1280: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1281: p_self_enroll_only in varchar2) return varchar2 is
1282:
1283: l_category_usage_id ota_category_usages.category_usage_id%type;
1284: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1285: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1286: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1287: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1280: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1281: p_self_enroll_only in varchar2) return varchar2 is
1282:
1283: l_category_usage_id ota_category_usages.category_usage_id%type;
1284: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1285: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1286: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1287: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1288: begin
1281: p_self_enroll_only in varchar2) return varchar2 is
1282:
1283: l_category_usage_id ota_category_usages.category_usage_id%type;
1284: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1285: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1286: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1287: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1288: begin
1289: -- Get the learning path's five levels of categories. If the category tree is deeper than that,
1282:
1283: l_category_usage_id ota_category_usages.category_usage_id%type;
1284: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1285: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1286: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1287: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1288: begin
1289: -- Get the learning path's five levels of categories. If the category tree is deeper than that,
1290: -- we will have to use CONNECT BY below.
1283: l_category_usage_id ota_category_usages.category_usage_id%type;
1284: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1285: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1286: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1287: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1288: begin
1289: -- Get the learning path's five levels of categories. If the category tree is deeper than that,
1290: -- we will have to use CONNECT BY below.
1291: open csr_lp_tree(p_learning_path_id);
1314: p_person_id in per_people_f.person_id%type,
1315: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1316: p_public_flag ota_learning_paths.public_flag%type,
1317: p_start_date_active in ota_learning_paths.start_date_active%type,
1318: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1319: begin
1320: if p_public_flag = 'Y' then
1321: return 'Y';
1322: else
1329: p_person_id in per_people_f.person_id%type,
1330: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1331: p_public_flag ota_learning_paths.public_flag%type,
1332: p_start_date_active in ota_learning_paths.start_date_active%type,
1333: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1334: begin
1335: -- A public learning path disregards its learner access records. Learner access records
1336: -- are the only way an event can be made self-enrollable. Therefore, the employee cannot
1337: -- self-enroll.
1347: p_party_id in hz_parties.party_id%type,
1348: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1349: p_public_flag ota_learning_paths.public_flag%type,
1350: p_start_date_active in ota_learning_paths.start_date_active%type,
1351: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1352: begin
1353: if p_public_flag = 'Y' then
1354: return 'Y';
1355: else
1362: p_party_id in hz_parties.party_id%type,
1363: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1364: p_public_flag ota_learning_paths.public_flag%type,
1365: p_start_date_active in ota_learning_paths.start_date_active%type,
1366: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1367: begin
1368: -- A public learning path disregards its learner access records. Learner access records
1369: -- are the only way an event can be made self-enrollable. Therefore, the ext learner cannot
1370: -- self-enroll.
1381: p_party_id in hz_parties.party_id%type,
1382: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1383: p_public_flag ota_learning_paths.public_flag%type,
1384: p_start_date_active in ota_learning_paths.start_date_active%type,
1385: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1386: begin
1387: if p_person_id is not null then
1388: return emp_can_enroll_in_path(p_person_id, p_learning_path_id, p_public_flag, p_start_date_active, p_category_usage_id);
1389: else
1396: p_user_id in fnd_user.user_id%type,
1397: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1398: p_public_flag ota_learning_paths.public_flag%type,
1399: p_start_date_active in ota_learning_paths.start_date_active%type,
1400: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1401:
1402: v_employee_id fnd_user.employee_id%type;
1403: v_party_id hz_parties.party_id%type;
1404: begin
1416: p_party_id in hz_parties.party_id%type,
1417: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1418: p_public_flag ota_learning_paths.public_flag%type,
1419: p_start_date_active in ota_learning_paths.start_date_active%type,
1420: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1421: begin
1422: if p_person_id is not null then
1423: return emp_can_self_enroll_in_path(p_person_id, p_learning_path_id, p_public_flag, p_start_date_active, p_category_usage_id);
1424: else
1431: p_user_id in fnd_user.user_id%type,
1432: p_learning_path_id in ota_learning_paths.learning_path_id%type,
1433: p_public_flag ota_learning_paths.public_flag%type,
1434: p_start_date_active in ota_learning_paths.start_date_active%type,
1435: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1436:
1437: v_employee_id fnd_user.employee_id%type;
1438: v_party_id hz_parties.party_id%type;
1439: begin
1453: p_self_enroll_only in varchar2) return varchar2 is
1454:
1455: l_now date := sysdate;
1456: l_object_id ota_frm_obj_inclusions.object_id%type;
1457: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1458: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1459: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1460: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1461: begin
1454:
1455: l_now date := sysdate;
1456: l_object_id ota_frm_obj_inclusions.object_id%type;
1457: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1458: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1459: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1460: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1461: begin
1462: -- Get the forum's five levels of categories. If the category tree is deeper than that,
1455: l_now date := sysdate;
1456: l_object_id ota_frm_obj_inclusions.object_id%type;
1457: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1458: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1459: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1460: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1461: begin
1462: -- Get the forum's five levels of categories. If the category tree is deeper than that,
1463: -- we will have to use CONNECT BY below.
1456: l_object_id ota_frm_obj_inclusions.object_id%type;
1457: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1458: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1459: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1460: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1461: begin
1462: -- Get the forum's five levels of categories. If the category tree is deeper than that,
1463: -- we will have to use CONNECT BY below.
1464: open csr_forum_tree(p_forum_id);
1504: p_forum_id in ota_forums_b.forum_id%type,
1505: p_self_enroll_only in varchar2) return varchar2 is
1506:
1507: l_object_id ota_frm_obj_inclusions.object_id%type;
1508: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1509: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1510: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1511: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1512: begin
1505: p_self_enroll_only in varchar2) return varchar2 is
1506:
1507: l_object_id ota_frm_obj_inclusions.object_id%type;
1508: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1509: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1510: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1511: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1512: begin
1513: -- Get the forums's five levels of categories. If the category tree is deeper than that,
1506:
1507: l_object_id ota_frm_obj_inclusions.object_id%type;
1508: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1509: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1510: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1511: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1512: begin
1513: -- Get the forums's five levels of categories. If the category tree is deeper than that,
1514: -- we will have to use CONNECT BY below.
1507: l_object_id ota_frm_obj_inclusions.object_id%type;
1508: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1509: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1510: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1511: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1512: begin
1513: -- Get the forums's five levels of categories. If the category tree is deeper than that,
1514: -- we will have to use CONNECT BY below.
1515: open csr_forum_tree(p_forum_id);
1538: p_person_id in per_people_f.person_id%type,
1539: p_forum_id in ota_forums_b.forum_id%type,
1540: p_public_flag ota_forums_b.public_flag%type,
1541: p_start_date_active in ota_forums_b.start_date_active%type,
1542: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1543: begin
1544: if p_public_flag = 'Y' then
1545: return 'Y';
1546: else
1553: p_person_id in per_people_f.person_id%type,
1554: p_forum_id in ota_forums_b.forum_id%type,
1555: p_public_flag ota_forums_b.public_flag%type,
1556: p_start_date_active in ota_forums_b.start_date_active%type,
1557: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1558: begin
1559: -- A public forum disregards its learner access records. Learner access records
1560: -- are the only way an event can be made self-enrollable. Therefore, the employee cannot
1561: -- self-enroll.
1571: p_party_id in hz_parties.party_id%type,
1572: p_forum_id in ota_forums_b.forum_id%type,
1573: p_public_flag ota_forums_b.public_flag%type,
1574: p_start_date_active in ota_forums_b.start_date_active%type,
1575: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1576: begin
1577: if p_public_flag = 'Y' then
1578: return 'Y';
1579: else
1586: p_party_id in hz_parties.party_id%type,
1587: p_forum_id in ota_forums_b.forum_id%type,
1588: p_public_flag ota_forums_b.public_flag%type,
1589: p_start_date_active in ota_forums_b.start_date_active%type,
1590: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1591: begin
1592: -- A public forum disregards its learner access records. Learner access records
1593: -- are the only way an event can be made self-enrollable. Therefore, the ext learner cannot
1594: -- self-enroll.
1605: p_party_id in hz_parties.party_id%type,
1606: p_forum_id in ota_forums_b.forum_id%type,
1607: p_public_flag ota_forums_b.public_flag%type,
1608: p_start_date_active in ota_forums_b.start_date_active%type,
1609: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1610: begin
1611: if p_person_id is not null then
1612: return emp_can_enroll_in_forum(p_person_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1613: else
1620: p_user_id in fnd_user.user_id%type,
1621: p_forum_id in ota_forums_b.forum_id%type,
1622: p_public_flag ota_forums_b.public_flag%type,
1623: p_start_date_active in ota_forums_b.start_date_active%type,
1624: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1625:
1626: v_employee_id fnd_user.employee_id%type;
1627: v_party_id hz_parties.party_id%type;
1628: begin
1640: p_party_id in hz_parties.party_id%type,
1641: p_forum_id in ota_forums_b.forum_id%type,
1642: p_public_flag ota_forums_b.public_flag%type,
1643: p_start_date_active in ota_forums_b.start_date_active%type,
1644: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1645: begin
1646: if p_person_id is not null then
1647: return emp_can_self_enroll_in_forum(p_person_id, p_forum_id, p_public_flag, p_start_date_active, p_category_usage_id);
1648: else
1655: p_user_id in fnd_user.user_id%type,
1656: p_forum_id in ota_forums_b.forum_id%type,
1657: p_public_flag ota_forums_b.public_flag%type,
1658: p_start_date_active in ota_forums_b.start_date_active%type,
1659: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1660:
1661: v_employee_id fnd_user.employee_id%type;
1662: v_party_id hz_parties.party_id%type;
1663: begin
1678: p_self_enroll_only in varchar2) return varchar2 is
1679:
1680: l_now date := sysdate;
1681: l_object_id ota_chat_obj_inclusions.object_id%type;
1682: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1683: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1684: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1685: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1686: begin
1679:
1680: l_now date := sysdate;
1681: l_object_id ota_chat_obj_inclusions.object_id%type;
1682: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1683: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1684: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1685: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1686: begin
1687: -- Get the chat's five levels of categories. If the category tree is deeper than that,
1680: l_now date := sysdate;
1681: l_object_id ota_chat_obj_inclusions.object_id%type;
1682: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1683: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1684: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1685: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1686: begin
1687: -- Get the chat's five levels of categories. If the category tree is deeper than that,
1688: -- we will have to use CONNECT BY below.
1681: l_object_id ota_chat_obj_inclusions.object_id%type;
1682: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1683: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1684: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1685: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1686: begin
1687: -- Get the chat's five levels of categories. If the category tree is deeper than that,
1688: -- we will have to use CONNECT BY below.
1689: open csr_chat_tree(p_chat_id);
1729: p_chat_id in ota_chats_b.chat_id%type,
1730: p_self_enroll_only in varchar2) return varchar2 is
1731:
1732: l_object_id ota_chat_obj_inclusions.object_id%type;
1733: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1734: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1735: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1736: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1737: begin
1730: p_self_enroll_only in varchar2) return varchar2 is
1731:
1732: l_object_id ota_chat_obj_inclusions.object_id%type;
1733: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1734: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1735: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1736: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1737: begin
1738: -- Get the chat's five levels of categories. If the category tree is deeper than that,
1731:
1732: l_object_id ota_chat_obj_inclusions.object_id%type;
1733: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1734: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1735: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1736: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1737: begin
1738: -- Get the chat's five levels of categories. If the category tree is deeper than that,
1739: -- we will have to use CONNECT BY below.
1732: l_object_id ota_chat_obj_inclusions.object_id%type;
1733: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1734: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1735: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1736: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1737: begin
1738: -- Get the chat's five levels of categories. If the category tree is deeper than that,
1739: -- we will have to use CONNECT BY below.
1740: open csr_chat_tree(p_chat_id);
1763: p_person_id in per_people_f.person_id%type,
1764: p_chat_id in ota_chats_b.chat_id%type,
1765: p_public_flag ota_chats_b.public_flag%type,
1766: p_start_date_active in ota_chats_b.start_date_active%type,
1767: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1768: begin
1769: if p_public_flag = 'Y' then
1770: return 'Y';
1771: else
1778: p_person_id in per_people_f.person_id%type,
1779: p_chat_id in ota_chats_b.chat_id%type,
1780: p_public_flag ota_chats_b.public_flag%type,
1781: p_start_date_active in ota_chats_b.start_date_active%type,
1782: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1783: begin
1784: -- A public chat disregards its learner access records. Learner access records
1785: -- are the only way an event can be made self-enrollable. Therefore, the employee cannot
1786: -- self-enroll.
1796: p_party_id in hz_parties.party_id%type,
1797: p_chat_id in ota_chats_b.chat_id%type,
1798: p_public_flag ota_chats_b.public_flag%type,
1799: p_start_date_active in ota_chats_b.start_date_active%type,
1800: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1801: begin
1802: if p_public_flag = 'Y' then
1803: return 'Y';
1804: else
1811: p_party_id in hz_parties.party_id%type,
1812: p_chat_id in ota_chats_b.chat_id%type,
1813: p_public_flag ota_chats_b.public_flag%type,
1814: p_start_date_active in ota_chats_b.start_date_active%type,
1815: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1816: begin
1817: -- A public chat disregards its learner access records. Learner access records
1818: -- are the only way an event can be made self-enrollable. Therefore, the ext learner cannot
1819: -- self-enroll.
1830: p_party_id in hz_parties.party_id%type,
1831: p_chat_id in ota_chats_b.chat_id%type,
1832: p_public_flag ota_chats_b.public_flag%type,
1833: p_start_date_active in ota_chats_b.start_date_active%type,
1834: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1835: begin
1836: if p_person_id is not null then
1837: return emp_can_enroll_in_chat(p_person_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1838: else
1845: p_user_id in fnd_user.user_id%type,
1846: p_chat_id in ota_chats_b.chat_id%type,
1847: p_public_flag ota_chats_b.public_flag%type,
1848: p_start_date_active in ota_chats_b.start_date_active%type,
1849: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1850:
1851: v_employee_id fnd_user.employee_id%type;
1852: v_party_id hz_parties.party_id%type;
1853: begin
1865: p_party_id in hz_parties.party_id%type,
1866: p_chat_id in ota_chats_b.chat_id%type,
1867: p_public_flag ota_chats_b.public_flag%type,
1868: p_start_date_active in ota_chats_b.start_date_active%type,
1869: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1870: begin
1871: if p_person_id is not null then
1872: return emp_can_self_enroll_in_chat(p_person_id, p_chat_id, p_public_flag, p_start_date_active, p_category_usage_id);
1873: else
1880: p_user_id in fnd_user.user_id%type,
1881: p_chat_id in ota_chats_b.chat_id%type,
1882: p_public_flag ota_chats_b.public_flag%type,
1883: p_start_date_active in ota_chats_b.start_date_active%type,
1884: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1885:
1886: v_employee_id fnd_user.employee_id%type;
1887: v_party_id hz_parties.party_id%type;
1888: begin
1901: p_certification_id in ota_certifications_b.certification_id%type,
1902: p_self_enroll_only in varchar2) return varchar2 is
1903:
1904: l_now date := sysdate;
1905: l_category_usage_id ota_category_usages.category_usage_id%type;
1906: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1907: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1908: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1909: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1902: p_self_enroll_only in varchar2) return varchar2 is
1903:
1904: l_now date := sysdate;
1905: l_category_usage_id ota_category_usages.category_usage_id%type;
1906: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1907: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1908: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1909: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1910: begin
1903:
1904: l_now date := sysdate;
1905: l_category_usage_id ota_category_usages.category_usage_id%type;
1906: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1907: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1908: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1909: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1910: begin
1911: -- Get the certification's five levels of categories. If the category tree is deeper than that,
1904: l_now date := sysdate;
1905: l_category_usage_id ota_category_usages.category_usage_id%type;
1906: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1907: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1908: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1909: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1910: begin
1911: -- Get the certification's five levels of categories. If the category tree is deeper than that,
1912: -- we will have to use CONNECT BY below.
1905: l_category_usage_id ota_category_usages.category_usage_id%type;
1906: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1907: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1908: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1909: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1910: begin
1911: -- Get the certification's five levels of categories. If the category tree is deeper than that,
1912: -- we will have to use CONNECT BY below.
1913: open csr_cert_tree(p_certification_id);
1952: p_party_id in hz_parties.party_id%type,
1953: p_certification_id in ota_certifications_b.certification_id%type,
1954: p_self_enroll_only in varchar2) return varchar2 is
1955:
1956: l_category_usage_id ota_category_usages.category_usage_id%type;
1957: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1958: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1959: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1960: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1953: p_certification_id in ota_certifications_b.certification_id%type,
1954: p_self_enroll_only in varchar2) return varchar2 is
1955:
1956: l_category_usage_id ota_category_usages.category_usage_id%type;
1957: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1958: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1959: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1960: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1961: begin
1954: p_self_enroll_only in varchar2) return varchar2 is
1955:
1956: l_category_usage_id ota_category_usages.category_usage_id%type;
1957: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1958: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1959: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1960: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1961: begin
1962: -- Get the certification's five levels of categories. If the category tree is deeper than that,
1955:
1956: l_category_usage_id ota_category_usages.category_usage_id%type;
1957: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1958: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1959: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1960: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1961: begin
1962: -- Get the certification's five levels of categories. If the category tree is deeper than that,
1963: -- we will have to use CONNECT BY below.
1956: l_category_usage_id ota_category_usages.category_usage_id%type;
1957: l_parent_cat_usage_id_1 ota_category_usages.category_usage_id%type;
1958: l_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type;
1959: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
1960: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
1961: begin
1962: -- Get the certification's five levels of categories. If the category tree is deeper than that,
1963: -- we will have to use CONNECT BY below.
1964: open csr_cert_tree(p_certification_id);
1987: p_person_id in per_people_f.person_id%type,
1988: p_certification_id in ota_certifications_b.certification_id%type,
1989: p_public_flag ota_certifications_b.public_flag%type,
1990: p_start_date_active in ota_certifications_b.start_date_active%type,
1991: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
1992: begin
1993: if p_public_flag = 'Y' then
1994: return 'Y';
1995: else
2002: p_person_id in per_people_f.person_id%type,
2003: p_certification_id in ota_certifications_b.certification_id%type,
2004: p_public_flag ota_certifications_b.public_flag%type,
2005: p_start_date_active in ota_certifications_b.start_date_active%type,
2006: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2007: begin
2008: -- A public certification disregards its learner access records. Learner access records
2009: -- are the only way a certification can be made self-enrollable. Therefore, the employee cannot
2010: -- self-enroll.
2020: p_party_id in hz_parties.party_id%type,
2021: p_certification_id in ota_certifications_b.certification_id%type,
2022: p_public_flag ota_certifications_b.public_flag%type,
2023: p_start_date_active in ota_certifications_b.start_date_active%type,
2024: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2025: begin
2026: if p_public_flag = 'Y' then
2027: return 'Y';
2028: else
2035: p_party_id in hz_parties.party_id%type,
2036: p_certification_id in ota_certifications_b.certification_id%type,
2037: p_public_flag ota_certifications_b.public_flag%type,
2038: p_start_date_active in ota_certifications_b.start_date_active%type,
2039: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2040: begin
2041: -- A public certification disregards its learner access records. Learner access records
2042: -- are the only way a certification can be made self-enrollable. Therefore, the ext learner cannot
2043: -- self-enroll.
2054: p_party_id in hz_parties.party_id%type,
2055: p_certification_id in ota_certifications_b.certification_id%type,
2056: p_public_flag ota_certifications_b.public_flag%type,
2057: p_start_date_active in ota_certifications_b.start_date_active%type,
2058: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2059: begin
2060: if p_person_id is not null then
2061: return emp_can_enroll_in_cert(p_person_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2062: else
2069: p_user_id in fnd_user.user_id%type,
2070: p_certification_id in ota_certifications_b.certification_id%type,
2071: p_public_flag ota_certifications_b.public_flag%type,
2072: p_start_date_active in ota_certifications_b.start_date_active%type,
2073: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2074:
2075: v_employee_id fnd_user.employee_id%type;
2076: v_party_id hz_parties.party_id%type;
2077: begin
2089: p_party_id in hz_parties.party_id%type,
2090: p_certification_id in ota_certifications_b.certification_id%type,
2091: p_public_flag ota_certifications_b.public_flag%type,
2092: p_start_date_active in ota_certifications_b.start_date_active%type,
2093: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2094: begin
2095: if p_person_id is not null then
2096: return emp_can_self_enroll_in_cert(p_person_id, p_certification_id, p_public_flag, p_start_date_active, p_category_usage_id);
2097: else
2104: p_user_id in fnd_user.user_id%type,
2105: p_certification_id in ota_certifications_b.certification_id%type,
2106: p_public_flag ota_certifications_b.public_flag%type,
2107: p_start_date_active in ota_certifications_b.start_date_active%type,
2108: p_category_usage_id in ota_category_usages.category_usage_id%type default null) return varchar2 is
2109:
2110: v_employee_id fnd_user.employee_id%type;
2111: v_party_id hz_parties.party_id%type;
2112: begin