[Home] [Help]
PACKAGE BODY: APPS.OTA_MANDATORY_ENROLL_UTIL
Source
1 package body ota_mandatory_enroll_util as
2 /* $Header: otmandatoryenr.pkb 120.4 2008/01/25 11:48:49 shwnayak noship $ */
3
4
5 g_package varchar2(33) := 'ota_mandatory_enroll_util.'; -- Global package name
6
7
8
9 PROCEDURE process_mandatory_event_assoc( ERRBUF OUT NOCOPY VARCHAR2,
10 RETCODE OUT NOCOPY VARCHAR2,
11 p_event_id IN NUMBER) IS
12
13
14 TYPE mandatory_event_assoc_rec IS RECORD(
15 enr_prereq_type dbms_sql.varchar2_table,
16 event_id dbms_sql.number_table,
17 person_id dbms_sql.number_table,
18 organization_id dbms_sql.number_table,
19 job_id dbms_sql.number_table,
20 position_id dbms_sql.varchar2_table,
21 org_structure_version_id dbms_sql.number_table,
22 user_group_id dbms_sql.number_table,
23 requestor_id dbms_sql.number_table
24 );
25
26 l_rec mandatory_event_assoc_rec;
27 l_date ota_events.course_end_date%type;
28 l_conc_request_id ota_mandatory_enr_requests.conc_program_request_id%type;
29 l_event_title ota_events_tl.title%type;
30 l_proc varchar2(72) := g_package||'process_mandatory_event_assoc';
31
32
33 BEGIN
34
35 l_conc_request_id := FND_GLOBAL.conc_request_id;
36 hr_utility.set_location(' Entering:'||l_proc, 5);
37
38 if p_event_id is NULL then
39 FND_FILE.PUT_LINE(FND_FILE.LOG,'Mandatory Enrollments are being processed for all classes');
40 ELSE
41 OPEN get_class_name(p_event_id);
42 FETCH get_class_name into l_event_title;
43 CLOSE get_class_name;
44 FND_FILE.PUT_LINE(FND_FILE.LOG,'Mandatory Enrollments are being processed for Class -' || l_event_title);
45 END IF;
46
47 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent Request Id : ' || FND_GLOBAL.conc_request_id);
48 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Start time is - '||TO_CHAR(SYSTIMESTAMP));
49 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
50
51 SELECT
52 oea.MANDATORY_ENROLLMENT_PREREQ enr_prereq_type,
53 oea.EVENT_ID event_id,
54 oea.PERSON_ID person_id,
55 oea.ORGANIZATION_ID organization_id,
56 oea.JOB_ID job_id,
57 oea.POSITION_ID position_id,
58 oea.ORG_STRUCTURE_VERSION_ID org_structure_version_id,
59 oea.USER_GROUP_ID user_group_id,
60 evt.OWNER_ID requestor_id
61 BULK COLLECT INTO
62 l_rec.enr_prereq_type,l_rec.event_id,l_rec.person_id,l_rec.organization_id,l_rec.job_id,l_rec.position_id,
63 l_rec.org_structure_version_id,l_rec.user_group_id,l_rec.requestor_id
64 FROM
65 ota_event_associations oea ,
66 ota_events evt
67 WHERE oea.event_id = evt.event_id
68 AND oea.MANDATORY_ENROLLMENT_FLAG = 'Y'
69 AND ota_timezone_util.convert_date(trunc(sysdate),to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code , evt.timezone)
70 BETWEEN decode(evt.enrolment_start_date, NULL, to_date('0001/01/01','YYYY/MM/DD'),
71 to_date( to_char(evt.enrolment_start_date, 'YYYY/MM/DD') || ' ' || '00:00', 'YYYY/MM/DD HH24:MI'))
72 AND decode(evt.enrolment_end_date, NULL, to_date('4712/12/31','YYYY/MM/DD'),
73 to_date( to_char(evt.enrolment_end_date, 'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI'))
74 AND evt.event_status IN ('P','N')
75 AND nvl(p_event_id,-1)= decode(p_event_id,NULL,-1,evt.EVENT_ID)
76 AND evt.business_group_id = OTA_GENERAL.get_business_group_id
77 ORDER BY evt.event_id;
78
79 FORALL i IN 1 .. l_rec.event_id.COUNT
80
81 INSERT INTO ota_mandatory_enr_requests(
82 MANDATORY_ENR_REQUEST_ID ,
83 REQUESTOR_ID,
84 EVENT_ID,
85 ENR_PREREQ_TYPE,
86 PERSON_ID,
87 ORGANIZATION_ID,
88 ORG_STRUCTURE_VERSION_ID,
89 JOB_ID,
90 POSITION_ID,
91 USERGROUP_ID,
92 CONC_PROGRAM_REQUEST_ID,
93 CREATION_DATE)
94 VALUES(OTA_MANDATORY_ENR_REQUESTS_S.NEXTVAL,
95 l_rec.requestor_id(i),
96 l_rec.event_id(i),
97 l_rec.enr_prereq_type(i),
98 l_rec.person_id(i),
99 l_rec.organization_id(i),
100 l_rec.org_structure_version_id(i),
101 l_rec.job_id(i),
102 l_rec.position_id(i),
103 l_rec.user_group_id(i),
104 l_conc_request_id,
105 sysdate);
106
107 COMMIT;
108
109
110 process_mandatory_enr_requests(l_conc_request_id);
111
112
113 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'End time is - '||TO_CHAR(SYSTIMESTAMP));
114 hr_utility.set_location(' Leaving:'||l_proc, 10);
115
116 END process_mandatory_event_assoc;
117
118
119 FUNCTION learner_can_enroll_in_class(p_event_id IN NUMBER
120 ,p_learner_id IN NUMBER)
121 RETURN varchar2 IS
122
123 CURSOR get_learner_class_enr_status IS
124 SELECT bst.type status_code,
125 btt.name Status,
126 bst.booking_status_type_id status_id,
127 decode(bst.type, 'C', 0,'R',1, 'W',2, 'P',3,'E',4, 'A',5) status_number,
128 nvl(tdb.is_mandatory_enrollment,'N') mandatory_enrollment_flag
129 FROM ota_delegate_bookings tdb,
130 ota_booking_status_types bst,
131 ota_booking_status_types_tl btt
132 WHERE tdb.delegate_person_id = p_learner_id
133 AND tdb.event_id = p_event_id
134 AND tdb.booking_status_type_id = bst.booking_status_type_id
135 AND bst.booking_status_type_id = btt.booking_status_type_id
136 AND btt.LANGUAGE = USERENV('LANG')
137 ORDER BY mandatory_enrollment_flag desc, status_number desc;
138
139
140 l_status varchar2(100) := NULL;
141 l_status_code varchar2(30);
142 l_status_id NUMBER := NULL;
143 l_status_number NUMBER;
144 l_mandatory_enrollment_flag varchar2(1) := NULL;
145 l_proc varchar2(72) := g_package||'learner_can_enroll_in_class';
146 BEGIN
147
148 hr_utility.set_location(' Entering:'||l_proc, 5);
149
150 OPEN get_learner_class_enr_status;
151 FETCH get_learner_class_enr_status INTO l_status_code, l_status, l_status_id, l_status_number, l_mandatory_enrollment_flag ;
152 CLOSE get_learner_class_enr_status;
153
154 IF((l_status_code = 'C' AND l_mandatory_enrollment_flag = 'N') OR (l_status_code IS NULL))THEN
155 hr_utility.set_location(' Leaving:'||l_proc, 10);
156 RETURN 'Y';
157 ELSE
158 hr_utility.set_location(' Leaving:'||l_proc, 15);
159 RETURN 'N';
160 END IF;
161
162
163
164 END learner_can_enroll_in_class;
165
166
167
168
169 FUNCTION learner_is_notSelected(p_person_id IN per_all_people_f.person_id%type
170 ,p_assignment_id per_all_assignments_f.assignment_id%type
171 ,p_event_id IN ota_events.event_id%type)
172 RETURN Boolean IS
173
174 CURSOR lrnr_already_selected IS
175 SELECT assignment_id
176 FROM ota_mandatory_enr_req_members
177 WHERE
178 person_id = p_person_id
179 AND event_id = p_event_id
180 AND create_enrollment = 'Y';
181
182 l_lrnr_assignment_id per_all_assignments_f.assignment_id%type;
183 l_person_name per_all_people_f.full_name%type;
184 l_proc varchar2(72) := g_package||'learner_is_notSelected';
185
186 BEGIN
187
188 hr_utility.set_location(' Entering:'||l_proc, 5);
189
190 OPEN lrnr_already_selected;
191 FETCH lrnr_already_selected INTO l_lrnr_assignment_id;
192
193 IF lrnr_already_selected%NOTFOUND THEN
194 CLOSE lrnr_already_selected;
195 RETURN TRUE;
196 ELSE
197 IF p_assignment_id = l_lrnr_assignment_id THEN
198 CLOSE lrnr_already_selected;
199 hr_utility.set_location(' Leaving:'||l_proc, 10);
200 RETURN false;
201 ELSE
202 --Log an error mentioning learner has duplicate assignments
203 OPEN csr_get_person_name(p_person_id);
204 FETCH csr_get_person_name INTO l_person_name;
205 CLOSE csr_get_person_name;
206
207 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - '|| l_person_name);
208 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner has duplicate assignments.Error creating enrollment INTO class -' || p_event_id);
209 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
210 CLOSE lrnr_already_selected;
211 hr_utility.set_location(' Leaving:'||l_proc, 15);
212 RETURN false;
213 END IF;
214
215 END IF;
216
217
218 END learner_is_notSelected;
219
220
221 FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
222 p_organization_id IN ota_event_associations.organization_id%type,
223 p_person_id IN per_people_f.person_id%type)
224 RETURN VARCHAR2 IS
225
226 CURSOR csr_lrnr_belongs_to_org IS
227 SELECT asg.assignment_id
228 FROM per_all_assignments_f asg,
229 (
230 SELECT p_organization_id AS organization_id
231 FROM dual
232 UNION ALL
233 SELECT x.sub_organization_id AS organization_id
234 FROM per_org_hrchy_summary x,
235 per_org_structure_versions v,
236 per_org_structure_versions currv
237 WHERE v.org_structure_version_id = p_org_structure_version_id AND
238 v.organization_structure_id = currv.organization_structure_id AND
239 (currv.date_to IS NULL OR
240 sysdate BETWEEN currv.date_from AND currv.date_to) AND
241 x.organization_structure_id = currv.organization_structure_id AND
242 x.org_structure_version_id = currv.org_structure_version_id AND
243 x.organization_id = p_organization_id AND
244 x.sub_org_relative_level > 0
245 ) orgs
246 WHERE asg.person_id = p_person_id AND
247 asg.organization_id = orgs.organization_id AND
248 asg.assignment_type in ('E','A','C');
249
250 l_assignment_id per_all_assignments_f.assignment_id%type;
251 l_proc varchar2(72) := g_package||'learner_belongs_to_child_org';
252 BEGIN
253
254 hr_utility.set_location(' Entering:'||l_proc, 5);
255
256 OPEN csr_lrnr_belongs_to_org;
257 FETCH csr_lrnr_belongs_to_org INTO l_assignment_id;
258 CLOSE csr_lrnr_belongs_to_org;
259
260 IF l_assignment_id IS NOT NULL THEN
261 hr_utility.set_location(' Leaving:'||l_proc, 10);
262 RETURN 'Y';
263 ELSE
264 hr_utility.set_location(' Leaving:'||l_proc, 15);
265 RETURN 'N';
266 END IF;
267
268 END learner_belongs_to_child_org;
269
270
271
272 PROCEDURE create_request_member_record(l_person_id IN ota_mandatory_enr_req_members.person_id%type,
273 req_mandatory_enr_request_id IN ota_mandatory_enr_req_members.mandatory_enr_request_id%type,
274 req_event_id IN ota_events.event_id%type,
275 req_enr_prereq_type IN varchar2,
276 lrnr_completed_crs_prereq ota_mandatory_enr_req_members.completed_course_prereq%type,
277 lrnr_completed_comp_prereq ota_mandatory_enr_req_members.completed_competence_prereq%type,
278 l_numberof_records_processed IN OUT NOCOPY NUMBER) IS
279
280 CURSOR csr_get_assignment_info(l_person_id NUMBER) IS
281 SELECT paf.organization_id,
282 paf.business_group_id,
283 paf.assignment_id
284 FROM
285 per_all_assignments_f paf,
286 per_person_types ppt,
287 per_all_people_f perp,
288 per_person_type_usages_f ptu
289 WHERE
290 paf.person_id = l_person_id
291 AND perp.person_id =paf.person_id
292 AND perp.person_id = ptu.person_id
293 AND ptu.person_type_id = ppt.person_type_id
294 AND ((paf.primary_flag = 'Y' AND ppt.system_person_type IN ('EMP','CWK'))
295 OR (paf.assignment_type = 'A' AND ppt.system_person_type ='APL'))
296 AND paf.assignment_type IN ('A','E','C')
297 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
298 AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
299 AND ((ppt.system_person_type = 'APL'
300 AND NOT EXISTS (SELECT person_id
301 FROM per_person_type_usages_f ptf,
302 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
303 AND ptf.person_type_id = ptp.person_type_id
304 AND ptp.system_person_type IN ('EMP', 'CWK')
305 AND ptf.person_id = paf.PERSON_ID)
306 )OR ppt.system_person_type IN ('EMP', 'CWK'));
307
308 l_assignment_info csr_get_assignment_info%rowtype;
309 l_create_enrollment varchar2(1) :=null;
310 l_completed_crs_prereq varchar2(1):= null;
311 l_completed_comp_prereq varchar2(1) :=null;
312 l_proc varchar2(72) := g_package||'create_request_member_record';
313
314 BEGIN
315
316 hr_utility.set_location(' Entering:'||l_proc, 5);
317 --As learners are selected based on primary or secondary assignment criteria,but enrollments must be created
318 -- based on primary assignment we need to retreive the primary assignment before validations.
319 OPEN csr_get_assignment_info(l_person_id);
320 FETCH csr_get_assignment_info INTO l_assignment_info;
321 CLOSE csr_get_assignment_info;
322
323 IF learner_is_notSelected(l_person_id,l_assignment_info.assignment_id,req_event_id) THEN
324 --perform the above check to avoid multiple entries for the same learner into same class AND duplicate assignments
325
326 IF req_enr_prereq_type = 'N' THEN --Prereq=None
327 l_create_enrollment := 'Y';
328
329 ELSIF req_enr_prereq_type = 'A' THEN --Prereq=Course
330 l_create_enrollment := lrnr_completed_crs_prereq;
331
332 ELSIF req_enr_prereq_type = 'C' THEN --Prereq=Competence
333 l_create_enrollment := lrnr_completed_comp_prereq;
334
335 ELSIF req_enr_prereq_type = 'E' THEN --Prereq=Course OR Competence
336 IF lrnr_completed_comp_prereq = 'Y' THEN
337 l_create_enrollment := 'Y';
338 ELSE
339 l_create_enrollment := lrnr_completed_crs_prereq;
340 END IF;
341
342 ELSE --Prereq = Course AND Competence
343 IF lrnr_completed_comp_prereq = 'Y' THEN
344 l_create_enrollment := lrnr_completed_crs_prereq;
345 ELSE
346 l_create_enrollment := 'N';
347 END IF;
348
349 END IF;
350 l_completed_crs_prereq := lrnr_completed_crs_prereq;
351 l_completed_comp_prereq := lrnr_completed_comp_prereq;
352
353 INSERT INTO ota_mandatory_enr_req_members(mandatory_enr_request_id,person_id,assignment_id,error_message,creation_date,completed_course_prereq,completed_competence_prereq,create_enrollment,event_id,organization_id,business_group_id)
354 VALUES(req_mandatory_enr_request_id,l_person_id,l_assignment_info.assignment_id,NULL,sysdate,l_completed_crs_prereq,l_completed_comp_prereq,
355 l_create_enrollment,req_event_id,l_assignment_info.organization_id,l_assignment_info.business_group_id);
356 l_numberof_records_processed := l_numberof_records_processed + 1;
357 END IF;--learner_is_notSelected
358
359 hr_utility.set_location(' Leaving:'||l_proc, 10);
360
361 end create_request_member_record;
362
363
364
365
366
367 PROCEDURE process_mandatory_enr_requests(p_conc_request_id IN NUMBER) IS
368
369
370 TYPE learners_in_usergroup IS REF CURSOR;
371 csr_get_lrnr_in_ug learners_in_usergroup;
372
373 TYPE learner_rec IS RECORD(
374 person_id per_all_people_f.person_id%type,
375 job_id per_jobs_tl.job_id%type,
376 position_id per_all_positions.position_id%type,
377 completed_crs_prereq varchar2(1),
378 completed_comp_prereq varchar2(1),
379 organization_id per_all_assignments_f.organization_id%type,
380 assignment_id per_all_assignments_f.assignment_id%type
381 );
382 lrnr_rec learner_rec;
383
384 CURSOR csr_get_learners(
385 p_person_id ota_event_associations.person_id%type,
386 p_organization_id ota_event_associations.organization_id%type,
387 p_job_id ota_event_associations.job_id%type,
388 p_position_id ota_event_associations.position_id%type,
389 p_org_structure_version_id ota_event_associations. org_structure_version_id%type,
390 p_event_id ota_event_associations.event_id%type,
391 p_enr_prereq_type ota_event_associations.mandatory_enrollment_prereq%type,
392 p_event_start_date date
393 ) IS
394 SELECT ppf.person_id
395 , pjt.job_id Job_Id
396 , pps.position_id
397 ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
398 ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
399 , paf.organization_id
400 , paf.assignment_id
401 FROM per_all_people_f ppf
402 ,per_all_assignments_f paf
403 ,per_jobs_tl pjt
404 ,per_all_positions pps
405 ,per_person_type_usages_f ptu
406 ,per_person_types pts
407 ,per_person_types_tl ptt
408 ,hr_all_organization_units_tl orgTl
409 ,per_business_groups pbg
410 WHERE ppf.person_id = paf.person_id
411 AND (pts.system_person_type IN ('EMP','CWK') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
412 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
413 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
414 AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
415 OR nvl(p_event_start_date, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
416 AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
417 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
418 OR nvl(p_event_start_date, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
419 AND paf.job_id = pjt.job_id(+)
420 AND pjt.language(+) = USERENV('LANG')
421 AND pps.position_id(+) = paf.position_id
422 AND pts.person_type_id = ptt.person_type_id
423 AND ptt.language = USERENV('LANG')
424 AND pts.person_type_id = ptu.person_type_id
425 AND ptu.person_id = ppf.person_id
426 AND paf.organization_id = orgtl.organization_id
427 AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
428 AND paf.assignment_type IN ('A','E','C')
429 AND orgtl.language = USERENV('LANG')
430 AND (fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID') IS NOT NULL OR pbg.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID'))
431 AND paf.business_group_id = pbg.business_group_id
432 AND
433 ((pts.system_person_type = 'APL'
434 AND NOT EXISTS (SELECT person_id
435 FROM per_person_type_usages_f ptf,
436 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
437 AND ptf.person_type_id = ptp.person_type_id
438 AND ptp.system_person_type IN ('EMP', 'CWK')
439 AND ptf.person_id = ppf.PERSON_ID)
440 )
441 OR pts.system_person_type IN ('EMP', 'CWK'))
442 AND learner_can_enroll_in_class(p_event_id,ppf.person_id) = 'Y'
443 AND
444 (
445 ( nvl(p_organization_id, -1) = decode(p_organization_id, NULL, -1, nvl(paf.organization_id,-1))) OR
446
447 ( p_org_structure_version_id IS NOT NULL AND learner_belongs_to_child_org(p_org_structure_version_id,p_organization_id,ppf.person_id)='Y')
448 )
449 AND nvl(p_job_id, -1) = decode(p_job_id, NULL, -1, nvl(paf.job_id, -1))
450 AND nvl(p_position_id,-1) = decode(p_position_id, NULL, -1, nvl(paf.position_id, -1))
451
452 AND nvl(p_person_id,-1) = decode(p_person_id,NULL,-1,paf.person_id);
453
454
455 l_numberof_records_processed NUMBER:= 0;
456 l_create_enrollment varchar2(1);
457 l_completed_crs_prereq varchar2(1);
458 l_completed_comp_prereq varchar2(1);
459 l_person_name per_all_people_f.full_name%type;
460 l_event_start_date date;
461
462
463 sql_stmnt varchar2(4000);
464 usergroup_whereclause varchar2(4000);
465 l_proc varchar2(72) := g_package||'process_mandatory_enr_requests';
466 BEGIN
467
468 hr_utility.set_location(' Entering:'||l_proc, 5);
469
470 FOR request IN get_all_mandatory_enr_requests(p_conc_request_id) LOOP
471 l_event_start_date := trunc(ota_learner_access_util.get_event_start_date(request.event_id,sysdate));
472 IF request.usergroup_id IS NULL THEN
473 FOR learner IN csr_get_learners (request.person_id, request.organization_id, request.job_id, request.position_id, request.org_structure_version_id, request.event_id, request.enr_prereq_type,l_event_start_date) LOOP
474 create_request_member_record(learner.person_id,request.mandatory_enr_request_id,request.event_id,request.enr_prereq_type,learner.completed_crs_prereq,learner.completed_comp_prereq,l_numberof_records_processed);
475 END LOOP;--END FOR learner IN csr_get_learners
476 ELSE
477 --resolve the members FOR the user group AND them to ota_mandatory_enr_request_members
478 usergroup_whereclause :=TO_CHAR(ota_learner_access_util.get_ug_whereclause(request.usergroup_id, -1));
479 --FND_FILE.PUT_LINE(FND_FILE.LOG,'usergroup_whereclause : '||usergroup_whereclause);
480 sql_stmnt :='SELECT * FROM(
481 SELECT
482 ppf.person_id person_id
483 , pjt.job_id job_id
484 , pps.position_id position_id
485 ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,''E'',:1 ) completed_crs_prereq
486 ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,:2) completed_comp_prereq
487 , paf.organization_id organization_id
488 , paf.assignment_id assignment_id
489 FROM per_all_people_f ppf
490 ,per_all_assignments_f paf
491 ,per_jobs_tl pjt
492 ,per_all_positions pps
493 ,per_person_type_usages_f ptu
494 ,per_person_types pts
495 ,per_person_types_tl ptt
496 ,hr_all_organization_units_tl orgTl
497 ,per_business_groups pbg
498 WHERE ppf.person_id = paf.person_id
499 AND (pts.system_person_type IN (''EMP'',''CWK'') OR (paf.assignment_type = ''A'' AND pts.system_person_type =''APL''))
500 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
501 AND ( (nvl(fnd_profile.value(''OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS''),''N'') = ''Y''
502 AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
503 OR nvl(:3, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
504 AND ( (nvl(fnd_profile.value(''OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS''),''N'') = ''Y''
505 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
506 OR nvl(:4, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
507 AND paf.job_id = pjt.job_id(+)
508 AND pjt.language(+) = USERENV(''LANG'')
509 AND pps.position_id(+) = paf.position_id
510 AND pts.person_type_id = ptt.person_type_id
511 AND ptt.language = USERENV(''LANG'')
512 AND pts.person_type_id = ptu.person_type_id
513 AND ptu.person_id = ppf.person_id
514 AND paf.organization_id = orgtl.organization_id
515 AND pts.system_person_type IN (''EMP'', ''CWK'', ''APL'')
516 AND paf.assignment_type IN (''A'',''E'',''C'')
517 AND orgtl.language = USERENV(''LANG'')
518 AND (fnd_profile.value(''OTA_HR_GLOBAL_BUSINESS_GROUP_ID'') IS NOT NULL OR pbg.business_group_id = fnd_profile.value(''PER_BUSINESS_GROUP_ID''))
519 AND paf.business_group_id = pbg.business_group_id
520 AND
521 ((pts.system_person_type = ''APL''
522 AND NOT EXISTS (SELECT person_id
523 FROM per_person_type_usages_f ptf,
524 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
525 AND ptf.person_type_id = ptp.person_type_id
526 AND ptp.system_person_type IN (''EMP'', ''CWK'')
527 AND ptf.person_id = ppf.PERSON_ID)
528 )
529 OR pts.system_person_type IN (''EMP'', ''CWK''))
530 AND OTA_MANDATORY_ENROLL_UTIL.learner_can_enroll_in_class(:5,ppf.person_id) = ''Y''
531 )QRSLT WHERE'|| usergroup_whereclause;
532
533 OPEN csr_get_lrnr_in_ug FOR sql_stmnt USING request.event_id,request.event_id,l_event_start_date,l_event_start_date,request.event_id;
534 LOOP
535 FETCH csr_get_lrnr_in_ug into lrnr_rec;
536 EXIT WHEN csr_get_lrnr_in_ug%NOTFOUND;
537 create_request_member_record(lrnr_rec.person_id,request.mandatory_enr_request_id,request.event_id,request.enr_prereq_type,lrnr_rec.completed_crs_prereq,lrnr_rec.completed_comp_prereq,l_numberof_records_processed);
538 END LOOP;
539 CLOSE csr_get_lrnr_in_ug;
540
541 END IF;--END IF request.usergroup_id IS NULL
542
543 IF l_numberof_records_processed > 1000 THEN
544 COMMIT;
545 l_numberof_records_processed :=0;
546 END IF;
547
548 END LOOP;
549
550 COMMIT;
551 --All the records to be processed are now present IN ota_mandatory_enr_req_members with create_enrollment = 'Y'.
552 --The records which cannot be created due to unfulfilled course/competence prereq have the respective flags set to 'N'
553 --and create_enrollment IS set to 'N'
554
555
556 create_enrollments(p_conc_request_id);
557
558
559
560 --Write the error messages to log
561 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
562 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unsuccessful Learners');
563 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
564 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name Class Reason');
565 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
566 FOR unsuccessful_learner IN unprocessed_enrollments LOOP
567 OPEN csr_get_person_name(unsuccessful_learner.person_id);
568 FETCH csr_get_person_name INTO l_person_name;
569 CLOSE csr_get_person_name;
570 IF unsuccessful_learner.create_enrollment = 'N' THEN
571 IF unsuccessful_learner.completed_course_prereq = 'N' THEN
572 --The learner has NOT completed the course perquisites FOR the event
573 /*FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
574 FND_FILE.PUT_LINE(FND_FILE.LOG,'The learner has NOT completed the course perquisites for the class -'||unsuccessful_learner.title);
575 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');*/
576 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||'Incomplete course prerequisites');
577 END IF;
578 IF unsuccessful_learner.completed_competence_prereq = 'N' THEN
579 --The learner has NOT completed the competence perquisites FOR the event
580 /*FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
581 FND_FILE.PUT_LINE(FND_FILE.LOG,'The learner has NOT completed the competence perquisites for the class -'||unsuccessful_learner.title);
582 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');*/
583 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||'Incomplete competence prerequisites');
584 END IF;
585 END IF;
586
587 IF unsuccessful_learner.error_message IS NOT NULL THEN
588 /* FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
589 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error message - '||unsuccessful_learner.error_message);
590 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');*/
591 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||unsuccessful_learner.error_message);
592 END IF;
593
594
595 END LOOP;
596
597 hr_utility.set_location(' Leaving:'||l_proc, 10);
598
599
600 END process_mandatory_enr_requests;
601
602
603 PROCEDURE create_enrollments(p_conc_reqId IN NUMBER) IS
604
605
606 CURSOR csr_get_request_members(l_conc_reqID NUMBER) IS
607 SELECT
608 reqmembers.MANDATORY_ENR_REQUEST_ID,
609 reqmembers.PERSON_ID,
610 reqmembers.ASSIGNMENT_ID,
611 reqmembers.EVENT_ID,
612 reqmembers.ERROR_MESSAGE,
613 reqmembers.ORGANIZATION_ID,
614 reqmembers.BUSINESS_GROUP_ID
615 FROM
616 ota_mandatory_enr_req_members reqmembers,
617 ota_mandatory_enr_requests requests
618 WHERE
619 requests.conc_program_request_id = l_conc_reqId
620 AND requests.mandatory_enr_request_id = reqmembers.mandatory_enr_request_id
621 AND reqmembers.create_enrollment = 'Y'
622 ORDER BY event_id;
623
624
625
626 CURSOR csr_get_cost_center_info(l_assignment_id NUMBER) IS
627 SELECT pcak.cost_allocation_keyflex_id
628 FROM per_all_assignments_f assg,
629 pay_cost_allocations_f pcaf,
630 pay_cost_allocation_keyflex pcak
631 WHERE assg.assignment_id = pcaf.assignment_id
632 AND assg.assignment_id = l_assignment_id
633 AND assg.Primary_flag = 'Y'
634 AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
635 AND pcak.enabled_flag = 'Y'
636 AND sysdate BETWEEN nvl(pcaf.effective_start_date,sysdate)
637 AND nvl(pcaf.effective_end_date,sysdate+1)
638 AND trunc(sysdate) BETWEEN nvl(assg.effective_start_date,trunc(sysdate))
639 AND nvl(assg.effective_end_date,trunc(sysdate+1));
640
641 l_error_message ota_mandatory_enr_req_members.error_message%type;
642 l_mandaotory_enr_request_id ota_mandatory_enr_req_members.mandatory_enr_request_id%type;
643 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%type;
644 l_booking_status ota_booking_status_types_tl.name%type;
645
646 l_request_rec get_all_mandatory_enr_requests%rowtype;
647 l_req_member_rec csr_get_request_members%rowtype;
648 l_booking_id ota_delegate_bookings.booking_id%type;
649
650
651 l_cost_center_info csr_get_cost_center_info%rowtype;
652
653 l_person_name per_all_people_f.full_name%type;
654 l_class_name ota_events_tl.title%type;
655 l_proc varchar2(72) := g_package||'create_enrollments';
656
657 BEGIN
658
659 hr_utility.set_location(' Entering:'||l_proc, 5);
660
661 OPEN get_all_mandatory_enr_requests(p_conc_reqId);
662 FETCH get_all_mandatory_enr_requests INTO l_request_rec;
663 IF get_all_mandatory_enr_requests%NOTFOUND THEN
664 -- Raise error that no request found
665 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-No requests found FOR concurrent program- '||p_conc_reqId);
666 CLOSE get_all_mandatory_enr_requests;
667 RETURN;
668 ELSE
669 CLOSE get_all_mandatory_enr_requests;
670 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
671 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successful Learners');
672 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
673 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name Class ');
674 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
675
676 FOR l_req_member_rec IN csr_get_request_members(p_conc_reqId) LOOP
677
678 l_booking_id := NULL;
679 l_error_message := NULL;
680
681 OPEN csr_get_person_name(l_req_member_rec.person_id);
682 FETCH csr_get_person_name INTO l_person_name;
683 CLOSE csr_get_person_name;
684 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
685
686
687 OPEN csr_get_cost_center_info(l_req_member_rec.assignment_id);
688 FETCH csr_get_cost_center_info INTO l_cost_center_info;
689 CLOSE csr_get_cost_center_info;
690
691 BEGIN
692
693 ota_bulk_enroll_util.Create_Enrollment_And_Finance(
694 p_event_id => l_req_member_rec.event_id
695 ,p_cost_centers => l_cost_center_info.cost_allocation_keyflex_id
696 ,p_assignment_id => l_req_member_rec.assignment_id
697 ,p_delegate_contact_id => NULL
698 ,p_business_group_id_from => l_req_member_rec.business_group_id
699 ,p_organization_id => l_req_member_rec.organization_id
700 ,p_person_id => l_req_member_rec.person_id
701 ,p_booking_id => l_booking_id
702 ,p_message_name => l_error_message
703 ,p_override_prerequisites => 'Y'
704 ,p_is_mandatory_enrollment => 'Y');
705 EXCEPTION
706 WHEN OTHERS THEN
707 l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating Enrollment ');
708 UPDATE ota_mandatory_enr_req_members
709 SET error_message = l_error_message
710 WHERE person_id = l_req_member_rec.person_id
711 AND event_id = l_req_member_rec.event_id
712 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;
713 END;
714
715 OPEN get_class_name(l_req_member_rec.event_id);
716 FETCH get_class_name into l_class_name;
717 CLOSE get_class_name;
718
719 IF l_booking_id IS NOT NULL THEN
720 FND_FILE.PUT_LINE(FND_FILE.LOG,l_person_name||' | '||l_class_name);
721 --FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
722 UPDATE ota_mandatory_enr_req_members
723 SET error_message = NULL
724 WHERE person_id = l_req_member_rec.person_id
725 AND assignment_id = l_req_member_rec.assignment_id
726 AND event_id = l_req_member_rec.event_id
727 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;
728 ELSE
729 l_error_message := nvl(substr(l_error_message,1,2000),'Booking_id IS NULL');
730 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Error when creating enrollment into class- '||l_class_name);
731 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
732 --FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
733
734 UPDATE ota_mandatory_enr_req_members
735 SET error_message = l_error_message
736 WHERE person_id = l_req_member_rec.person_id
737 AND event_id = l_req_member_rec.event_id
738 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;
739 END IF;
740
741 END LOOP;
742 -- Start workflow AND send a notification to the requestor
743 notify_class_owners(p_conc_reqId);
744 END IF;
745
746 hr_utility.set_location(' Leaving:'||l_proc, 10);
747
748 END create_enrollments;
749
750
751
752
753
754
755 PROCEDURE notify_mandatory_request(p_person_id in NUMBER,
756 p_conc_program_request_id in NUMBER,
757 p_object_type in VARCHAR2,
758 p_object_id in NUMBER,
759 p_error_learners in NUMBER,
760 p_success_learners in NUMBER)
761 IS
762 l_proc varchar2(72) := g_package||'notify_mandatory_request';
763 l_process wf_activities.name%type :='OTA_BLK_MANDATORY_ENR_NTF_PRC';
764 l_item_type wf_items.item_type%type := 'OTWF';
765 l_item_key wf_items.item_key%type;
766
767 l_user_name varchar2(80);
768 l_person_full_name per_all_people_f.FULL_NAME%TYPE;
769 l_role_name wf_roles.name%type;
770 l_role_display_name wf_roles.display_name%type;
771
772 l_process_display_name varchar2(240);
773
774 CURSOR csr_get_user_name(p_person_id IN VARCHAR2) IS
775 SELECT user_name FROM fnd_user WHERE employee_id=p_person_id
776 and trunc(sysdate) between trunc(start_date) and trunc(nvl(end_date, sysdate+1));
777
778
779 CURSOR csr_get_person_name(p_person_id IN number) IS
780 SELECT ppf.full_name FROM per_all_people_f ppf WHERE person_id = p_person_id;
781
782 BEGIN
783 hr_utility.set_location('Entering:'||l_proc, 5);
784
785 -- Get the next item key from the sequence
786 select hr_workflow_item_key_s.nextval into l_item_key from sys.dual;
787
788 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
789
790 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'CONC_REQUEST_ID',p_conc_program_request_id);
791 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'OBJECT_NAME',p_object_id);
792 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'OBJECT_TYPE' ,p_object_type);
793 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'ERROR_NUMBER',p_error_learners);
794 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'SUCCESS_NUMBER',p_success_learners);
795
796 IF p_person_id IS NOT NULL THEN
797 OPEN csr_get_person_name(p_person_id);
798 FETCH csr_get_person_name INTO l_person_full_name;
799 CLOSE csr_get_person_name;
800
801 OPEN csr_get_user_name(p_person_id);
802 FETCH csr_get_user_name INTO l_user_name;
803 CLOSE csr_get_user_name;
804
805 --fnd_file.put_line(FND_FILE.LOG,'Requestor Name ' ||l_person_full_name);
806
807 IF l_person_full_name IS NOT NULL then
808 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'EVENT_OWNER',l_user_name);
809 END IF;
810 END IF;
811
812 -- Get and set owner role
813
814 hr_utility.set_location('Before Getting Owner'||l_proc, 10);
815
816 WF_DIRECTORY.GetRoleName(p_orig_system =>'PER',
817 p_orig_system_id => p_person_id,
818 p_name =>l_role_name,
819 p_display_name =>l_role_display_name);
820
821
822 WF_ENGINE.SetItemOwner(itemtype => l_item_type,
823 itemkey =>l_item_key,
824 owner =>l_role_name);
825
826 hr_utility.set_location('After Setting Owner'||l_proc, 10);
827
828
829 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
830
831 hr_utility.set_location('leaving:'||l_proc, 20);
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836 END notify_mandatory_request;
837
838
839
840
841 PROCEDURE notify_class_owners(p_conc_reqId IN NUMBER)
842 IS
843
844 CURSOR csr_get_all_event_owners IS
845 SELECT distinct event_id,requestor_id
846 from ota_mandatory_enr_requests
847 where requestor_id IS NOT NULL
848 and conc_program_request_id = p_conc_reqId ;
849
850 CURSOR csr_get_error_learners(p_event_id IN number) IS
851 SELECT COUNT(distinct person_id)
852 FROM ota_mandatory_enr_req_members reqm
853 WHERE reqm.event_id = p_event_id
854 AND(reqm.create_enrollment = 'N' or reqm.error_message IS NOT NULL);
855
856
857 CURSOR csr_get_successful_learners(p_event_id IN number) IS
858 SELECT COUNT( distinct person_id)
859 FROM ota_mandatory_enr_req_members reqm
860 WHERE reqm.event_id = p_event_id
861 AND(reqm.create_enrollment = 'Y' and reqm.error_message IS NULL);
862
863 l_error_learners NUMBER := 0;
864 l_success_learners NUMBER := 0;
865
866 l_proc varchar2(72) := g_package||'notify_class_owners';
867
868 BEGIN
869 hr_utility.set_location('Entering:'||l_proc, 5);
870 for owner in csr_get_all_event_owners loop
871
872 OPEN csr_get_error_learners(owner.event_id);
873 FETCH csr_get_error_learners INTO l_error_learners;
874 CLOSE csr_get_error_learners;
875
876 OPEN csr_get_successful_learners(owner.event_id);
877 FETCH csr_get_successful_learners INTO l_success_learners;
878 CLOSE csr_get_successful_learners;
879
880 notify_mandatory_request(owner.requestor_id,p_conc_reqId,'CL',owner.event_id,l_error_learners,l_success_learners);
881
882 end loop;
883
884
885 END notify_class_owners;
886
887
888 END ota_mandatory_enroll_util;