[Home] [Help]
PACKAGE: APPS.OTA_MANDATORY_ENROLL_UTIL
Source
1 package ota_mandatory_enroll_util AUTHID CURRENT_USER as
2 /* $Header: otmandatoryenr.pkh 120.3.12020000.7 2013/02/07 18:44:25 jaysridh ship $ */
3
4 l_subc_doesnot_exist NUMBER := -1;
5 l_non_upd_subc_exist NUMBER := -2;
6 l_cancelled_subc_exist NUMBER := -3;
7 l_upd_subc_exist NUMBER:= -4;
8
9 Type g_processes_table is table of number index by binary_integer;
10
11 g_num_processes number := 0;
12 g_processes_tbl g_processes_table;
13
14 CURSOR csr_get_person_name(p_person_id NUMBER) IS
15 SELECT full_name
16 FROM per_all_people_f
17 WHERE trunc(sysdate) between effective_start_date and effective_end_date
18 AND person_id = p_person_id;
19
20 CURSOR get_class_name(p_event_id IN NUMBER) IS
21 SELECT title
22 FROM OTA_EVENTS_TL
23 WHERE event_id = p_event_id
24 AND language=userenv('LANG');
25
26 CURSOR get_all_mandatory_enr_requests(p_conc_request_id IN NUMBER) IS
27 SELECT
28 MANDATORY_ENR_REQUEST_ID,
29 REQUESTOR_ID,
30 EVENT_ID,
31 ENR_PREREQ_TYPE,
32 PERSON_ID,
33 ORGANIZATION_ID,
34 ORG_STRUCTURE_VERSION_ID,
35 JOB_ID,
36 POSITION_ID,
37 USERGROUP_ID,
38 CERTIFICATION_ID
39 FROM ota_mandatory_enr_requests
40 WHERE conc_program_request_id = p_conc_request_id;
41
42 CURSOR get_all_mand_multi_enr_reqs(p_conc_request_id IN NUMBER) IS
43 SELECT
44 MANDATORY_ENR_REQUEST_ID,
45 REQUESTOR_ID,
46 EVENT_ID,
47 ENR_PREREQ_TYPE,
48 PERSON_ID,
49 ORGANIZATION_ID,
50 ORG_STRUCTURE_VERSION_ID,
51 JOB_ID,
52 POSITION_ID,
53 USERGROUP_ID,
54 CERTIFICATION_ID
55 FROM OTA_MAND_MULTI_ENR_REQUESTS
56 WHERE conc_program_request_id = p_conc_request_id;
57
58 CURSOR unprocessed_enrollments(l_conc_reqID NUMBER) IS
59 SELECT distinct
60 evt_tl.event_id event_id,
61 evt_tl.title title,
62 reqm.person_id person_id,
63 reqm.completed_course_prereq completed_course_prereq,
64 reqm.completed_competence_prereq completed_competence_prereq,
65 reqm.create_enrollment create_enrollment,
66 reqm.error_message error_message
67 FROM
68 ota_mandatory_enr_req_members reqm,
69 ota_mandatory_enr_requests requests,
70 ota_events_tl evt_tl
71 WHERE
72 evt_tl.event_id = reqm.event_id
73 AND(reqm.create_enrollment = 'N' or reqm.error_message IS NOT NULL)
74 AND evt_tl.language=userenv('LANG')
75 AND requests.conc_program_request_id = l_conc_reqId
76 AND requests.mandatory_enr_request_id = reqm.mandatory_enr_request_id;
77
78 CURSOR unprocessed_multi_enrollments(l_conc_reqID NUMBER) IS
79 SELECT distinct
80 evt_tl.event_id event_id,
81 evt_tl.title title,
82 reqm.person_id person_id,
83 reqm.completed_course_prereq completed_course_prereq,
84 reqm.completed_competence_prereq completed_competence_prereq,
85 reqm.create_enrollment create_enrollment,
86 reqm.error_message error_message
87 FROM
88 OTA_MAND_MULTI_ENR_REQ_MEMBERS reqm,
89 OTA_MAND_MULTI_ENR_REQUESTS requests,
90 ota_events_tl evt_tl
91 WHERE
92 evt_tl.event_id = reqm.event_id
93 AND(reqm.create_enrollment = 'N' or reqm.error_message IS NOT NULL)
94 AND evt_tl.language=userenv('LANG')
95 AND requests.conc_program_request_id = l_conc_reqId
96 AND requests.mandatory_enr_request_id = reqm.mandatory_enr_request_id;
97
98 CURSOR unprocessed_subscriptions(l_conc_reqID NUMBER) IS
99 SELECT distinct
100 cert_tl.certification_id certification_id,
101 cert_tl.name title,
102 reqm.person_id person_id,
103 reqm.create_enrollment create_enrollment,
104 reqm.error_message error_message
105 FROM
106 OTA_MAND_MULTI_ENR_REQ_MEMBERS reqm,
107 OTA_MAND_MULTI_ENR_REQUESTS requests,
108 ota_certifications_tl cert_tl
109 WHERE
110 cert_tl.certification_id = reqm.certification_id
111 AND(reqm.create_enrollment = 'N' or reqm.error_message IS NOT NULL)
112 AND cert_tl.language=userenv('LANG')
113 AND requests.conc_program_request_id = l_conc_reqId
114 AND requests.mandatory_enr_request_id = reqm.mandatory_enr_request_id;
115
116 FUNCTION learner_can_enroll_in_class(p_event_id IN NUMBER
117 ,p_learner_id IN NUMBER
118 )RETURN VARCHAR2;
119
120
121 FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
122 p_organization_id IN ota_event_associations.organization_id%type,
123 p_person_id IN per_people_f.person_id%type)
124 RETURN VARCHAR2;
125
126 FUNCTION learner_is_notSelected_inClass(p_person_id IN per_all_people_f.person_id%type
127 ,p_assignment_id per_all_assignments_f.assignment_id%type
128 ,p_event_id IN ota_events.event_id%type)
129 RETURN Boolean;
130
131 FUNCTION lrn_is_notSelected_inClass_mul(p_person_id IN per_all_people_f.person_id%type
132 ,p_assignment_id per_all_assignments_f.assignment_id%type
133 ,p_event_id IN ota_events.event_id%type)
134 RETURN Boolean;
135
136 PROCEDURE process_mandatory_event_assoc(ERRBUF OUT NOCOPY VARCHAR2
137 ,RETCODE OUT NOCOPY VARCHAR2
138 ,p_event_id in NUMBER);
139
140 PROCEDURE process_mand_event_assoc_multi(ERRBUF OUT NOCOPY VARCHAR2
141 ,RETCODE OUT NOCOPY VARCHAR2,
142 p_action_id in number default null,
143 p_chunk_size in number default 1000,
144 p_thread_count in number default 3,
145 p_event_id in NUMBER);
146
147 PROCEDURE process_mandatory_enr_requests(p_conc_request_id IN NUMBER);
148
149 PROCEDURE process_mand_enr_reqs_multi(p_conc_request_id IN NUMBER,
150 p_chunk_size in number,
151 p_thread_count in number,
152 p_event_id IN NUMBER);
153
154 PROCEDURE create_enrollments(p_conc_reqId IN NUMBER);
155
156 PROCEDURE create_enrollments_multi(p_conc_reqId IN NUMBER);
157
158 PROCEDURE notify_mandatory_request(p_person_id in NUMBER,
159 p_conc_program_request_id in NUMBER,
160 p_object_type in VARCHAR2,
161 p_object_id in NUMBER,
162 p_error_learners in NUMBER,
163 p_success_learners in NUMBER,
164 p_process in wf_activities.name%type);
165
166
167 PROCEDURE notify_class_owners(p_conc_reqId IN NUMBER);
168
169 PROCEDURE notify_multi_class_owners(p_conc_reqId IN NUMBER);
170
171 PROCEDURE process_automatic_cert_subscr( ERRBUF OUT NOCOPY VARCHAR2,
172 RETCODE OUT NOCOPY VARCHAR2,
173 p_action_id in number default null,
174 p_chunk_size in number default 1000,
175 p_thread_count in number default 3,
176 p_cert_id IN NUMBER);
177
178 CURSOR get_certification_name(p_cert_id IN NUMBER) IS
179 SELECT name
180 FROM OTA_CERTIFICATIONS_TL
181 WHERE certification_id = p_cert_id
182 AND source_lang=userenv('LANG');
183
184
185 PROCEDURE process_auto_cert_subscr_req(p_conc_request_id IN NUMBER,
186 p_cert_id IN NUMBER,
187 p_chunk_size in number,
188 p_thread_count in number);
189
190 PROCEDURE learner_can_enroll_in_cert(p_certification_id IN NUMBER
191 ,p_learner_id IN NUMBER
192 ,l_return_status OUT NOCOPY NUMBER
193 ,l_cert_enr_id OUT NOCOPY NUMBER);
194
195 PROCEDURE create_cert_req_member_record(l_person_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.person_id%type,
196 req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,
197 req_cert_id IN ota_cert_enrollments.certification_id%type,
198 l_numberof_records_processed IN OUT NOCOPY NUMBER,
199 l_create_enrollment IN varchar2 default 'Y',
200 p_person_action_id OUT NOCOPY OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type);
201
202 FUNCTION learner_is_notSelected_inCert(p_person_id IN per_all_people_f.person_id%type
203 ,p_assignment_id per_all_assignments_f.assignment_id%type
204 ,p_certification_id IN ota_certifications_b.certification_id%type default NULL)
205 RETURN Boolean;
206
207 PROCEDURE create_cert_subscriptions(p_conc_reqId IN NUMBER);
208
209 Procedure check_all_slaves_finished(p_rpt_flag Boolean default FALSE);
210
211 Procedure purge_mand_enroll_data(ERRBUF OUT NOCOPY VARCHAR2,
212 RETCODE OUT NOCOPY VARCHAR2);
213
214 END ota_mandatory_enroll_util;