[Home] [Help]
PACKAGE BODY: APPS.OTA_MANDATORY_ENROLL_UTIL
Source
1 package body ota_mandatory_enroll_util as
2 /* $Header: otmandatoryenr.pkb 120.6.12020000.21 2013/06/24 13:01:14 jaysridh ship $ */
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 PROCEDURE process_mand_event_assoc_multi( ERRBUF OUT NOCOPY VARCHAR2,
119 RETCODE OUT NOCOPY VARCHAR2,
120 p_action_id in number default null,
121 p_chunk_size in number default 1000,
122 p_thread_count in number default 3,
123 p_event_id IN NUMBER) IS
124
125
126 TYPE mandatory_event_assoc_rec IS RECORD(
127 enr_prereq_type dbms_sql.varchar2_table,
128 event_id dbms_sql.number_table,
129 person_id dbms_sql.number_table,
130 organization_id dbms_sql.number_table,
131 job_id dbms_sql.number_table,
132 position_id dbms_sql.varchar2_table,
133 org_structure_version_id dbms_sql.number_table,
134 user_group_id dbms_sql.number_table,
135 requestor_id dbms_sql.number_table
136 );
137
138 cursor csr_check_mand_multi_running(c_conc_req_id fnd_concurrent_requests.REQUEST_ID%type) is
139 select 1 from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
140 where fcp.CONCURRENT_PROGRAM_NAME = 'OTA_MAND_ENR_MULTI'
141 and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID
142 and fcr.STATUS_CODE = 'R' and nvl(PARENT_REQUEST_ID,-1) = -1
143 and fcr.REQUEST_ID <> c_conc_req_id;
144
145 l_rec mandatory_event_assoc_rec;
146 l_date ota_events.course_end_date%type;
147 l_conc_request_id OTA_MAND_MULTI_ENR_REQUESTS.conc_program_request_id%type;
148 l_event_title ota_events_tl.title%type;
149 l_request_id number;
150 l_person_name per_all_people_f.full_name%type;
151 l_mand_multi_conc_exist number;
152 l_chunk_size number := nvl(p_chunk_size,1000);
153 l_threads number := nvl(p_thread_count,3);
154 OTA_INCOMP_MAND_MULTI_EXIST exception;
155 l_proc varchar2(72) := g_package||'process_mand_event_assoc_multi';
156
157
158 BEGIN
159
160 l_conc_request_id := FND_GLOBAL.conc_request_id;
161 hr_utility.set_location(' Entering:'||l_proc, 5);
162 if p_chunk_size is NULL then
163 l_chunk_size := 1000;
164 end if;
165 if p_thread_count is NULL then
166 l_threads := 3;
167 end if;
168
169 if p_action_id is null then
170
171 open csr_check_mand_multi_running(l_conc_request_id);
172 fetch csr_check_mand_multi_running into l_mand_multi_conc_exist;
173
174 if csr_check_mand_multi_running%FOUND then
175 close csr_check_mand_multi_running;
176 raise OTA_INCOMP_MAND_MULTI_EXIST;
177 end if;
178
179 close csr_check_mand_multi_running;
180
181 if p_event_id is NULL then
182 FND_FILE.PUT_LINE(FND_FILE.LOG,'Mandatory Enrollments are being processed for all classes');
183 ELSE
184 OPEN get_class_name(p_event_id);
185 FETCH get_class_name into l_event_title;
186 CLOSE get_class_name;
187 FND_FILE.PUT_LINE(FND_FILE.LOG,'Mandatory Enrollments are being processed for Class -' || l_event_title);
188 END IF;
189
190 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent Request Id : ' || FND_GLOBAL.conc_request_id);
191 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Start time is - '||TO_CHAR(SYSTIMESTAMP));
192 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
193
194 SELECT
195 oea.MANDATORY_ENROLLMENT_PREREQ enr_prereq_type,
196 oea.EVENT_ID event_id,
197 oea.PERSON_ID person_id,
198 oea.ORGANIZATION_ID organization_id,
199 oea.JOB_ID job_id,
200 oea.POSITION_ID position_id,
201 oea.ORG_STRUCTURE_VERSION_ID org_structure_version_id,
202 oea.USER_GROUP_ID user_group_id,
203 evt.OWNER_ID requestor_id
204 BULK COLLECT INTO
205 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,
206 l_rec.org_structure_version_id,l_rec.user_group_id,l_rec.requestor_id
207 FROM
208 ota_event_associations oea ,
209 ota_events evt
210 WHERE oea.event_id = evt.event_id
211 AND oea.MANDATORY_ENROLLMENT_FLAG = 'Y'
212 AND ota_timezone_util.convert_date(trunc(sysdate),to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code , evt.timezone)
213 BETWEEN decode(evt.enrolment_start_date, NULL, to_date('0001/01/01','YYYY/MM/DD'),
214 to_date( to_char(evt.enrolment_start_date, 'YYYY/MM/DD') || ' ' || '00:00', 'YYYY/MM/DD HH24:MI'))
215 AND decode(evt.enrolment_end_date, NULL, to_date('4712/12/31','YYYY/MM/DD'),
216 to_date( to_char(evt.enrolment_end_date, 'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI'))
217 AND evt.event_status IN ('P','N')
218 AND nvl(p_event_id,-1)= decode(p_event_id,NULL,-1,evt.EVENT_ID)
219 AND evt.business_group_id = OTA_GENERAL.get_business_group_id
220 ORDER BY evt.event_id;
221
222 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_rec.event_id.COUNT:'||l_rec.event_id.COUNT);
223
224 FORALL i IN 1 .. l_rec.event_id.COUNT
225
226 INSERT INTO OTA_MAND_MULTI_ENR_REQUESTS(
227 MANDATORY_ENR_REQUEST_ID ,
228 REQUESTOR_ID,
229 EVENT_ID,
230 ENR_PREREQ_TYPE,
231 PERSON_ID,
232 ORGANIZATION_ID,
233 ORG_STRUCTURE_VERSION_ID,
234 JOB_ID,
235 POSITION_ID,
236 USERGROUP_ID,
237 CONC_PROGRAM_REQUEST_ID)
238 VALUES(OTA_MAND_MULTI_ENR_REQUESTS_S.NEXTVAL,
239 l_rec.requestor_id(i),
240 l_rec.event_id(i),
241 l_rec.enr_prereq_type(i),
242 l_rec.person_id(i),
243 l_rec.organization_id(i),
244 l_rec.org_structure_version_id(i),
245 l_rec.job_id(i),
246 l_rec.position_id(i),
247 l_rec.user_group_id(i),
248 l_conc_request_id);
249
250 COMMIT;
251
252
253 process_mand_enr_reqs_multi(l_conc_request_id,
254 l_chunk_size,
255 l_threads,
256 p_event_id);
257 else
258 l_conc_request_id := p_action_id;
259 create_enrollments_multi(l_conc_request_id);
260 end if;
261
262 ota_mandatory_enroll_util.check_all_slaves_finished
263 (p_rpt_flag => TRUE);
264
265 -- Start workflow AND send a notification to the requestor
266 if p_action_id is null then
267
268 --Write the error messages to log
269 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
270 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unsuccessful Learners');
271 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
272 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name Class Reason');
273 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
274 FOR unsuccessful_learner IN unprocessed_multi_enrollments(l_conc_request_id) LOOP
275 OPEN csr_get_person_name(unsuccessful_learner.person_id);
276 FETCH csr_get_person_name INTO l_person_name;
277 CLOSE csr_get_person_name;
278 IF unsuccessful_learner.create_enrollment = 'N' THEN
279 IF unsuccessful_learner.completed_course_prereq = 'N' THEN
280 --The learner has NOT completed the course perquisites FOR the event
281 /*FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
282 FND_FILE.PUT_LINE(FND_FILE.LOG,'The learner has NOT completed the course perquisites for the class -'||unsuccessful_learner.title);
283 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');*/
284 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||'Incomplete course prerequisites');
285 END IF;
286 IF unsuccessful_learner.completed_competence_prereq = 'N' THEN
287 --The learner has NOT completed the competence perquisites FOR the event
288 /*FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
289 FND_FILE.PUT_LINE(FND_FILE.LOG,'The learner has NOT completed the competence perquisites for the class -'||unsuccessful_learner.title);
290 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');*/
291 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||'Incomplete competence prerequisites');
292 END IF;
293 END IF;
294
295 IF unsuccessful_learner.error_message IS NOT NULL THEN
296 /* FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
297 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error message - '||unsuccessful_learner.error_message);
298 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');*/
299 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||unsuccessful_learner.error_message);
300 END IF;
301
302
303 END LOOP;
304
305 notify_multi_class_owners(l_conc_request_id);
306 l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'OTA'
307 ,program => 'OTA_PURGE_MAND_ENROLL_REC');
308 end if;
309 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'End time is - '||TO_CHAR(SYSTIMESTAMP));
310 hr_utility.set_location(' Leaving:'||l_proc, 10);
311 Exception
312 when OTA_INCOMP_MAND_MULTI_EXIST then
313 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: Another Process Mandatory Enrollment Multi threaded concurrent program is in progress');
314 END process_mand_event_assoc_multi;
315
316 FUNCTION learner_can_enroll_in_class(p_event_id IN NUMBER
317 ,p_learner_id IN NUMBER)
318 RETURN varchar2 IS
319
320 CURSOR check_enr_exists IS
321 SELECT booking_id
322 FROM ota_delegate_bookings
323 WHERE delegate_person_id = p_learner_id
324 AND event_id = p_event_id;
325 CURSOR get_learner_class_enr_status IS
326 SELECT bst.type status_code,
327 bst.booking_status_type_id status_id,
328 decode(bst.type, 'C', 0,'R',1, 'W',2, 'P',3,'E',4, 'A',5) status_number,
329 nvl(tdb.is_mandatory_enrollment,'N') mandatory_enrollment_flag
330 FROM ota_delegate_bookings tdb,
331 ota_booking_status_types bst
332 WHERE tdb.delegate_person_id = p_learner_id
333 AND tdb.event_id = p_event_id
334 AND tdb.booking_status_type_id = bst.booking_status_type_id
335 ORDER BY mandatory_enrollment_flag desc, status_number desc;
336
337
338 l_status_code varchar2(30);
339 l_status_id NUMBER := NULL;
340 l_status_number NUMBER;
341 l_mandatory_enrollment_flag varchar2(1) := NULL;
342 l_proc varchar2(72) := g_package||'learner_can_enroll_in_class';
343 l_booking_id ota_delegate_bookings.booking_id%type;
344 BEGIN
345
346 hr_utility.set_location(' Entering:'||l_proc, 5);
347
348 OPEN check_enr_exists;
349 FETCH check_enr_exists INTO l_booking_id;
350 IF check_enr_exists%NOTFOUND THEN---if enrollment doesnot exist
351 CLOSE check_enr_exists;
352 RETURN 'Y';
353
354 ELSE
355 --Since ota_delegate_bookings can have more than one row('n' cancelled rows and one enrolled row), we
356 --need to execute below cursor
357 CLOSE check_enr_exists;
358 OPEN get_learner_class_enr_status;
359 FETCH get_learner_class_enr_status INTO l_status_code, l_status_id, l_status_number, l_mandatory_enrollment_flag ;
360 CLOSE get_learner_class_enr_status;
361
362 IF((l_status_code = 'C' AND l_mandatory_enrollment_flag = 'N') OR (l_status_code IS NULL))THEN
363 hr_utility.set_location(' Leaving:'||l_proc, 10);
364 RETURN 'Y';
365 ELSE
366 hr_utility.set_location(' Leaving:'||l_proc, 15);
367 RETURN 'N';
368 END IF;
369
370 END IF;
371
372 END learner_can_enroll_in_class;
373
374
375
376
377 FUNCTION learner_is_notSelected_inClass(p_person_id IN per_all_people_f.person_id%type
378 ,p_assignment_id per_all_assignments_f.assignment_id%type
379 ,p_event_id IN ota_events.event_id%type)
380 RETURN Boolean IS
381
382 CURSOR lrnr_already_selected IS
383 SELECT assignment_id
384 FROM ota_mandatory_enr_req_members
385 WHERE
386 person_id = p_person_id
387 AND event_id = p_event_id
388 AND create_enrollment = 'Y';
389
390 l_lrnr_assignment_id per_all_assignments_f.assignment_id%type;
391 l_person_name per_all_people_f.full_name%type;
392 l_proc varchar2(72) := g_package||'learner_is_notSelected_inClass';
393
394 BEGIN
395
396 hr_utility.set_location(' Entering:'||l_proc, 5);
397
398 OPEN lrnr_already_selected;
399 FETCH lrnr_already_selected INTO l_lrnr_assignment_id;
400
401 IF lrnr_already_selected%NOTFOUND THEN
402 CLOSE lrnr_already_selected;
403 RETURN TRUE;
404 ELSE
405 IF p_assignment_id = l_lrnr_assignment_id THEN
406 CLOSE lrnr_already_selected;
407 hr_utility.set_location(' Leaving:'||l_proc, 10);
408 RETURN false;
409 ELSE
410 --Log an error mentioning learner has duplicate assignments
411 OPEN csr_get_person_name(p_person_id);
412 FETCH csr_get_person_name INTO l_person_name;
413 CLOSE csr_get_person_name;
414
415 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - '|| l_person_name);
416 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner has duplicate assignments.Error creating enrollment INTO class -' || p_event_id);
417 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
418 CLOSE lrnr_already_selected;
419 hr_utility.set_location(' Leaving:'||l_proc, 15);
420 RETURN false;
421 END IF;
422
423 END IF;
424
425
426 END learner_is_notSelected_inClass;
427
428 FUNCTION lrn_is_notSelected_inClass_mul(p_person_id IN per_all_people_f.person_id%type
429 ,p_assignment_id per_all_assignments_f.assignment_id%type
430 ,p_event_id IN ota_events.event_id%type)
431 RETURN Boolean IS
432
433 CURSOR lrnr_already_selected IS
434 SELECT assignment_id
435 FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS
436 WHERE
437 person_id = p_person_id
438 AND event_id = p_event_id
439 AND create_enrollment = 'Y';
440
441 l_lrnr_assignment_id per_all_assignments_f.assignment_id%type;
442 l_person_name per_all_people_f.full_name%type;
443 l_proc varchar2(72) := g_package||'lrn_is_notSelected_inClass_mul';
444
445 BEGIN
446
447 hr_utility.set_location(' Entering:'||l_proc, 5);
448
449 OPEN lrnr_already_selected;
450 FETCH lrnr_already_selected INTO l_lrnr_assignment_id;
451
452 IF lrnr_already_selected%NOTFOUND THEN
453 CLOSE lrnr_already_selected;
454 RETURN TRUE;
455 ELSE
456 IF p_assignment_id = l_lrnr_assignment_id THEN
457 CLOSE lrnr_already_selected;
458 hr_utility.set_location(' Leaving:'||l_proc, 10);
459 RETURN false;
460 ELSE
461 --Log an error mentioning learner has duplicate assignments
462 OPEN csr_get_person_name(p_person_id);
463 FETCH csr_get_person_name INTO l_person_name;
464 CLOSE csr_get_person_name;
465
466 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - '|| l_person_name);
467 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner has duplicate assignments.Error creating enrollment INTO class -' || p_event_id);
468 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
469 CLOSE lrnr_already_selected;
470 hr_utility.set_location(' Leaving:'||l_proc, 15);
471 RETURN false;
472 END IF;
473
474 END IF;
475
476
477 END lrn_is_notSelected_inClass_mul;
478
479
480 FUNCTION learner_is_notSelected_inCert(p_person_id IN per_all_people_f.person_id%type
481 ,p_assignment_id per_all_assignments_f.assignment_id%type
482 ,p_certification_id IN ota_certifications_b.certification_id%type default NULL)
483 RETURN Boolean IS
484
485 CURSOR lrnr_already_selected IS
486 SELECT assignment_id
487 FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS
488 WHERE
489 person_id = p_person_id
490 AND certification_id = p_certification_id
491 AND create_enrollment = 'Y';
492
493
494 l_lrnr_assignment_id per_all_assignments_f.assignment_id%type;
495 l_person_name per_all_people_f.full_name%type;
496 l_proc varchar2(72) := g_package||'learner_is_notSelected_inCert';
497
498 BEGIN
499
500 hr_utility.set_location(' Entering:'||l_proc, 5);
501
502 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered learner_is_notSelected_inCert');
503
504 OPEN lrnr_already_selected;
505 FETCH lrnr_already_selected INTO l_lrnr_assignment_id;
506
507 IF lrnr_already_selected%NOTFOUND THEN
508 CLOSE lrnr_already_selected;
509 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Return true');
510 RETURN TRUE;
511 ELSE
512 IF p_assignment_id = l_lrnr_assignment_id THEN
513 CLOSE lrnr_already_selected;
514 hr_utility.set_location(' Leaving:'||l_proc, 10);
515 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Return false');
516 RETURN false;
517 ELSE
518 --Log an error mentioning learner has duplicate assignments
519 OPEN csr_get_person_name(p_person_id);
520 FETCH csr_get_person_name INTO l_person_name;
521 CLOSE csr_get_person_name;
522
523 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - '|| l_person_name);
524 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner has duplicate assignments.Error creating enrollment INTO cert -' || p_certification_id);
525 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
526 CLOSE lrnr_already_selected;
527 hr_utility.set_location(' Leaving:'||l_proc, 15);
528 FND_FILE.PUT_LINE(FND_FILE.LOG,'Return false as lrnr has multiple assignments');
529 RETURN false;
530 END IF;
531
532 END IF;
533
534
535 END learner_is_notSelected_inCert;
536
537
538
539 FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
540 p_organization_id IN ota_event_associations.organization_id%type,
541 p_person_id IN per_people_f.person_id%type)
542 RETURN VARCHAR2 IS
543
544 /* CURSOR csr_lrnr_belongs_to_org IS
545 SELECT asg.assignment_id
546 FROM per_all_assignments_f asg,
547 (
548 SELECT p_organization_id AS organization_id
549 FROM dual
550 UNION ALL
551 SELECT x.sub_organization_id AS organization_id
552 FROM per_org_hrchy_summary x,
553 per_org_structure_versions v,
554 per_org_structure_versions currv
555 WHERE v.org_structure_version_id = p_org_structure_version_id AND
556 v.organization_structure_id = currv.organization_structure_id AND
557 (currv.date_to IS NULL OR
558 sysdate BETWEEN currv.date_from AND currv.date_to) AND
559 x.organization_structure_id = currv.organization_structure_id AND
560 x.org_structure_version_id = currv.org_structure_version_id AND
561 x.organization_id = p_organization_id AND
562 x.sub_org_relative_level > 0
563 ) orgs
564 WHERE asg.person_id = p_person_id AND
565 asg.organization_id = orgs.organization_id AND
566 asg.assignment_type in ('E','A','C');*/
567
568
569 CURSOR csr_lrnr_belongs_to_org IS
570 SELECT asg.assignment_id
571 FROM per_all_assignments_f asg,
572 (
573 SELECT o.organization_id_child as organization_id
574 FROM (SELECT o.organization_id_child
575 FROM per_org_structure_elements o
576 CONNECT BY o.organization_id_parent = PRIOR o.organization_id_child
577 AND o.org_structure_version_id = PRIOR o.org_structure_version_id
578 START WITH o.organization_id_parent = p_organization_id
579 AND o.org_structure_version_id = p_org_structure_version_id
580 UNION
581 SELECT p_organization_id organization_id_child
582 FROM DUAL) o,
583 hr_organization_units org
584 WHERE o.organization_id_child = org.organization_id)orgs
585 WHERE asg.person_id = p_person_id AND
586 asg.organization_id = orgs.organization_id AND
587 asg.assignment_type in ('E','A','C');
588
589
590 l_assignment_id per_all_assignments_f.assignment_id%type;
591 l_proc varchar2(72) := g_package||'learner_belongs_to_child_org';
592 BEGIN
593
594 hr_utility.set_location(' Entering:'||l_proc, 5);
595
596 OPEN csr_lrnr_belongs_to_org;
597 FETCH csr_lrnr_belongs_to_org INTO l_assignment_id;
598 CLOSE csr_lrnr_belongs_to_org;
599
600 IF l_assignment_id IS NOT NULL THEN
601 hr_utility.set_location(' Leaving:'||l_proc, 10);
602 RETURN 'Y';
603 ELSE
604 hr_utility.set_location(' Leaving:'||l_proc, 15);
605 RETURN 'N';
606 END IF;
607
608 END learner_belongs_to_child_org;
609
610
611
612 PROCEDURE create_request_member_record(l_person_id IN ota_mandatory_enr_req_members.person_id%type,
613 req_mandatory_enr_request_id IN ota_mandatory_enr_req_members.mandatory_enr_request_id%type,
614 req_event_id IN ota_events.event_id%type,
615 req_enr_prereq_type IN varchar2,
616 lrnr_completed_crs_prereq ota_mandatory_enr_req_members.completed_course_prereq%type,
617 lrnr_completed_comp_prereq ota_mandatory_enr_req_members.completed_competence_prereq%type,
618 l_numberof_records_processed IN OUT NOCOPY NUMBER) IS
619
620 CURSOR csr_get_assignment_info(l_person_id NUMBER) IS
621 SELECT paf.organization_id,
622 paf.business_group_id,
623 paf.assignment_id
624 FROM
625 per_all_assignments_f paf,
626 per_person_types ppt,
627 per_all_people_f perp,
628 per_person_type_usages_f ptu
629 WHERE
630 paf.person_id = l_person_id
631 AND perp.person_id =paf.person_id
632 AND perp.person_id = ptu.person_id
633 AND ptu.person_type_id = ppt.person_type_id
634 AND ((paf.primary_flag = 'Y' AND ppt.system_person_type IN ('EMP','CWK'))
635 OR (paf.assignment_type = 'A' AND ppt.system_person_type ='APL'))
636 AND paf.assignment_type IN ('A','E','C')
637 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
638 AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
639 AND ((ppt.system_person_type = 'APL'
640 AND NOT EXISTS (SELECT person_id
641 FROM per_person_type_usages_f ptf,
642 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
643 AND ptf.person_type_id = ptp.person_type_id
644 AND ptp.system_person_type IN ('EMP', 'CWK')
645 AND ptf.person_id = paf.PERSON_ID)
646 )OR ppt.system_person_type IN ('EMP', 'CWK'));
647
648 l_assignment_info csr_get_assignment_info%rowtype;
649 l_create_enrollment varchar2(1) :=null;
650 l_completed_crs_prereq varchar2(1):= null;
651 l_completed_comp_prereq varchar2(1) :=null;
652 l_proc varchar2(72) := g_package||'create_request_member_record';
653
654 BEGIN
655
656 hr_utility.set_location(' Entering:'||l_proc, 5);
657 --As learners are selected based on primary or secondary assignment criteria,but enrollments must be created
658 -- based on primary assignment we need to retreive the primary assignment before validations.
659 OPEN csr_get_assignment_info(l_person_id);
660 FETCH csr_get_assignment_info INTO l_assignment_info;
661 CLOSE csr_get_assignment_info;
662
663 IF learner_is_notSelected_inClass(l_person_id,l_assignment_info.assignment_id,req_event_id) THEN
664 --perform the above check to avoid multiple entries for the same learner into same class AND duplicate assignments
665
666 IF req_enr_prereq_type = 'N' THEN --Prereq=None
667 l_create_enrollment := 'Y';
668
669 ELSIF req_enr_prereq_type = 'A' THEN --Prereq=Course
670 l_create_enrollment := lrnr_completed_crs_prereq;
671
672 ELSIF req_enr_prereq_type = 'C' THEN --Prereq=Competence
673 l_create_enrollment := lrnr_completed_comp_prereq;
674
675 ELSIF req_enr_prereq_type = 'E' THEN --Prereq=Course OR Competence
676 IF lrnr_completed_comp_prereq = 'Y' THEN
677 l_create_enrollment := 'Y';
678 ELSE
679 l_create_enrollment := lrnr_completed_crs_prereq;
680 END IF;
681
682 ELSE --Prereq = Course AND Competence
683 IF lrnr_completed_comp_prereq = 'Y' THEN
684 l_create_enrollment := lrnr_completed_crs_prereq;
685 ELSE
686 l_create_enrollment := 'N';
687 END IF;
688
689 END IF;
690 l_completed_crs_prereq := lrnr_completed_crs_prereq;
691 l_completed_comp_prereq := lrnr_completed_comp_prereq;
692
693 INSERT INTO ota_mandatory_enr_req_members(mandatory_enr_request_id,person_id,assignment_id,error_message,creation_date,
694 completed_course_prereq,completed_competence_prereq,create_enrollment,event_id,organization_id,business_group_id)
695 VALUES(req_mandatory_enr_request_id,l_person_id,l_assignment_info.assignment_id,NULL,sysdate,l_completed_crs_prereq,
696 l_completed_comp_prereq, l_create_enrollment,req_event_id,l_assignment_info.organization_id,l_assignment_info.business_group_id);
697 l_numberof_records_processed := l_numberof_records_processed + 1;
698 END IF;--learner_is_notSelected_inClass
699
700 hr_utility.set_location(' Leaving:'||l_proc, 10);
701
702 end create_request_member_record;
703
704 PROCEDURE create_request_member_record(l_person_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.person_id%type,
705 req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,
706 req_event_id IN ota_events.event_id%type,
707 req_enr_prereq_type IN varchar2,
708 lrnr_completed_crs_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_course_prereq%type,
709 lrnr_completed_comp_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_competence_prereq%type,
710 l_numberof_records_processed IN OUT NOCOPY NUMBER,
711 p_person_action_id OUT NOCOPY OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type) IS
712
713 CURSOR csr_get_assignment_info(l_person_id NUMBER) IS
714 SELECT paf.organization_id,
715 paf.business_group_id,
716 paf.assignment_id
717 FROM
718 per_all_assignments_f paf,
719 per_person_types ppt,
720 per_all_people_f perp,
721 per_person_type_usages_f ptu
722 WHERE
723 paf.person_id = l_person_id
724 AND perp.person_id =paf.person_id
725 AND perp.person_id = ptu.person_id
726 AND ptu.person_type_id = ppt.person_type_id
727 AND ((paf.primary_flag = 'Y' AND ppt.system_person_type IN ('EMP','CWK'))
728 OR (paf.assignment_type = 'A' AND ppt.system_person_type ='APL'))
729 AND paf.assignment_type IN ('A','E','C')
730 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
731 AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
732 AND ((ppt.system_person_type = 'APL'
733 AND NOT EXISTS (SELECT person_id
734 FROM per_person_type_usages_f ptf,
735 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
736 AND ptf.person_type_id = ptp.person_type_id
737 AND ptp.system_person_type IN ('EMP', 'CWK')
738 AND ptf.person_id = paf.PERSON_ID)
739 )OR ppt.system_person_type IN ('EMP', 'CWK'));
740
741 l_assignment_info csr_get_assignment_info%rowtype;
742 l_create_enrollment varchar2(1) :=null;
743 l_completed_crs_prereq varchar2(1):= null;
744 l_completed_comp_prereq varchar2(1) :=null;
745 l_proc varchar2(72) := g_package||'create_request_member_record';
746
747 l_person_action_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type := NULL;
748
749 Cursor C_Sel1 is select OTA_PERSON_ACTIONS_S.nextval from sys.dual;
750
751 BEGIN
752
753 hr_utility.set_location(' Entering:'||l_proc, 5);
754 --As learners are selected based on primary or secondary assignment criteria,but enrollments must be created
755 -- based on primary assignment we need to retreive the primary assignment before validations.
756 OPEN csr_get_assignment_info(l_person_id);
757 FETCH csr_get_assignment_info INTO l_assignment_info;
758 CLOSE csr_get_assignment_info;
759
760 IF lrn_is_notSelected_inClass_mul(l_person_id,l_assignment_info.assignment_id,req_event_id) THEN
761 --perform the above check to avoid multiple entries for the same learner into same class AND duplicate assignments
762
763 IF req_enr_prereq_type = 'N' THEN --Prereq=None
764 l_create_enrollment := 'Y';
765
766 ELSIF req_enr_prereq_type = 'A' THEN --Prereq=Course
767 l_create_enrollment := lrnr_completed_crs_prereq;
768
769 ELSIF req_enr_prereq_type = 'C' THEN --Prereq=Competence
770 l_create_enrollment := lrnr_completed_comp_prereq;
771
772 ELSIF req_enr_prereq_type = 'E' THEN --Prereq=Course OR Competence
773 IF lrnr_completed_comp_prereq = 'Y' THEN
774 l_create_enrollment := 'Y';
775 ELSE
776 l_create_enrollment := lrnr_completed_crs_prereq;
777 END IF;
778
779 ELSE --Prereq = Course AND Competence
780 IF lrnr_completed_comp_prereq = 'Y' THEN
781 l_create_enrollment := lrnr_completed_crs_prereq;
782 ELSE
783 l_create_enrollment := 'N';
784 END IF;
785
786 END IF;
787 l_completed_crs_prereq := lrnr_completed_crs_prereq;
788 l_completed_comp_prereq := lrnr_completed_comp_prereq;
789
790 Open C_Sel1;
791 Fetch C_Sel1 Into l_person_action_id;
792 Close C_Sel1;
793
794 INSERT INTO OTA_MAND_MULTI_ENR_REQ_MEMBERS(mandatory_enr_request_id,person_id,assignment_id,error_message,
795 completed_course_prereq,completed_competence_prereq,create_enrollment,event_id,organization_id,mbr_bg_id,
796 person_action_id,action_status_cd)
797 VALUES(req_mandatory_enr_request_id,l_person_id,l_assignment_info.assignment_id,NULL,l_completed_crs_prereq,
798 l_completed_comp_prereq, l_create_enrollment,req_event_id,l_assignment_info.organization_id,l_assignment_info.business_group_id,
799 l_person_action_id,'U');
800 l_numberof_records_processed := l_numberof_records_processed + 1;
801 END IF;--lrn_is_notSelected_inClass_mul
802 p_person_action_id := l_person_action_id;
803 hr_utility.set_location(' Leaving:'||l_proc, 10);
804
805 end create_request_member_record;
806
807
808
809 PROCEDURE process_mandatory_enr_requests(p_conc_request_id IN NUMBER) IS
810
811
812 TYPE learners_in_usergroup IS REF CURSOR;
813 csr_get_lrnr_in_ug learners_in_usergroup;
814
815 TYPE learner_rec IS RECORD(
816 person_id per_all_people_f.person_id%type,
817 job_id per_jobs_tl.job_id%type,
818 position_id per_all_positions.position_id%type,
819 completed_crs_prereq varchar2(1),
820 completed_comp_prereq varchar2(1),
821 organization_id per_all_assignments_f.organization_id%type,
822 assignment_id per_all_assignments_f.assignment_id%type
823 );
824 lrnr_rec learner_rec;
825
826 CURSOR csr_get_learner(
827 p_person_id ota_event_associations.person_id%type,
828 p_event_id ota_event_associations.event_id%type,
829 p_enr_prereq_type ota_event_associations.mandatory_enrollment_prereq%type,
830 p_event_start_date date,
831 p_future_enddated_prof_val varchar2,
832 p_per_bg_grp_prof_val number,
833 p_ota_global_bg_prof_val number
834 ) IS
835 SELECT ppf.person_id
836 ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
837 ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
838 , paf.organization_id
839 , paf.assignment_id
840 FROM per_all_people_f ppf
841 ,per_all_assignments_f paf
842 ,per_person_type_usages_f ptu
843 ,per_person_types pts
844 ,per_business_groups pbg
845 WHERE
846 paf.person_id = p_person_id
847 AND ppf.person_id = paf.person_id
848 AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag= 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
849 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
850 AND ((p_future_enddated_prof_val = 'Y'
851 AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
852 OR nvl(p_event_start_date, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
853 AND((p_future_enddated_prof_val = 'Y'
854 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
855 OR nvl(p_event_start_date, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
856 AND pts.person_type_id = ptu.person_type_id
857 AND ptu.person_id = ppf.person_id
858 -- AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
859 AND paf.assignment_type IN ('A','E','C')
860 AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
861 AND paf.business_group_id = pbg.business_group_id
862 AND
863 ((pts.system_person_type = 'APL'
864 AND NOT EXISTS (SELECT person_id
865 FROM per_person_type_usages_f ptf,
866 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
867 AND ptf.person_type_id = ptp.person_type_id
868 AND ptp.system_person_type IN ('EMP', 'CWK')
869 AND ptf.person_id = ppf.PERSON_ID)
870 )
871 OR pts.system_person_type IN ('EMP', 'CWK'));
872
873
874
875
876
877
878 CURSOR csr_get_learners(
879 p_organization_id ota_event_associations.organization_id%type,
880 p_job_id ota_event_associations.job_id%type,
881 p_position_id ota_event_associations.position_id%type,
882 p_org_structure_version_id ota_event_associations. org_structure_version_id%type,
883 p_event_id ota_event_associations.event_id%type,
884 p_enr_prereq_type ota_event_associations.mandatory_enrollment_prereq%type,
885 p_event_start_date date,
886 p_future_enddated_prof_val varchar2,
887 p_per_bg_grp_prof_val number,
888 p_ota_global_bg_prof_val number
889 ) IS
890 SELECT ppf.person_id
891 , pjt.job_id Job_Id
892 , pps.position_id
893 ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
894 ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
895 , paf.organization_id
896 , paf.assignment_id
897 FROM per_all_people_f ppf
898 ,per_all_assignments_f paf
899 ,per_jobs_tl pjt
900 ,per_all_positions pps
901 ,per_person_type_usages_f ptu
902 ,per_person_types pts
903 ,hr_all_organization_units_tl orgTl
904 ,per_business_groups pbg
905 WHERE ppf.person_id = paf.person_id
906 AND (pts.system_person_type IN ('EMP','CWK') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
907 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
908 AND((p_future_enddated_prof_val = 'Y'
909 AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
910 OR nvl(p_event_start_date, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
911 AND((p_future_enddated_prof_val = 'Y'
912 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
913 OR nvl(p_event_start_date, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
914 AND paf.job_id = pjt.job_id(+)
915 AND pjt.language(+) = USERENV('LANG')
916 AND pps.position_id(+) = paf.position_id
917 AND pts.person_type_id = ptu.person_type_id
918 AND ptu.person_id = ppf.person_id
919 AND paf.organization_id = orgtl.organization_id
920 AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
921 AND paf.assignment_type IN ('A','E','C')
922 AND orgtl.language = USERENV('LANG')
923 AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
924 AND paf.business_group_id = pbg.business_group_id
925 AND
926 ((pts.system_person_type = 'APL'
927 AND NOT EXISTS (SELECT person_id
928 FROM per_person_type_usages_f ptf,
929 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
930 AND ptf.person_type_id = ptp.person_type_id
931 AND ptp.system_person_type IN ('EMP', 'CWK')
932 AND ptf.person_id = ppf.PERSON_ID)
933 )
934 OR pts.system_person_type IN ('EMP', 'CWK'))
935 AND
936 (
937 ( nvl(p_organization_id, -1) = decode(p_organization_id, NULL, -1, nvl(paf.organization_id,-1))) OR
938
939 ( 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')
940 )
941 AND nvl(p_job_id, -1) = decode(p_job_id, NULL, -1, nvl(paf.job_id, -1))
942 AND nvl(p_position_id,-1) = decode(p_position_id, NULL, -1, nvl(paf.position_id, -1));
943
944
945 l_numberof_records_processed NUMBER:= 0;
946 l_create_enrollment varchar2(1);
947 l_completed_crs_prereq varchar2(1);
948 l_completed_comp_prereq varchar2(1);
949 l_person_name per_all_people_f.full_name%type;
950 l_event_start_date date;
951
952 l_allow_future_end_prof_val varchar2(1);
953 l_per_bg_grp_prof_val number;
954 l_ota_global_bg_prof_val number;
955
956 sql_stmnt clob;
957 usergroup_whereclause clob;
958 v_split_sql_stmt DBMS_SQL.VARCHAR2S;
959 v_upperbound NUMBER;
960 v_cur INTEGER;
961 v_person_id number;
962 v_job_id number;
963 v_pos_id number;
964 v_is_crs_prereq_comp varchar2(1);
965 v_is_comp_prereq_comp varchar2(1);
966 v_org_id number;
967 v_assign_id number;
968 ignore INTEGER;
969 l_proc varchar2(72) := g_package||'process_mandatory_enr_requests';
970 BEGIN
971
972 hr_utility.set_location(' Entering:'||l_proc, 5);
973
974 l_allow_future_end_prof_val := fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS');
975 l_per_bg_grp_prof_val := fnd_profile.value('PER_BUSINESS_GROUP_ID');
976 l_ota_global_bg_prof_val := fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
977
978 IF l_allow_future_end_prof_val is null then
979 l_allow_future_end_prof_val := 'N';
980 END IF;
981
982 FND_FILE.PUT_LINE(FND_FILE.LOG,'Value of profile OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS : '||l_allow_future_end_prof_val);
983 FND_FILE.PUT_LINE(FND_FILE.LOG,'Value of profile PER_BUSINESS_GROUP_ID : '||l_per_bg_grp_prof_val);
984 FND_FILE.PUT_LINE(FND_FILE.LOG,'Value of profile OTA_HR_GLOBAL_BUSINESS_GROUP_ID : '||l_ota_global_bg_prof_val);
985
986
987 FOR request IN get_all_mandatory_enr_requests(p_conc_request_id) LOOP
988 l_event_start_date := trunc(ota_learner_access_util.get_event_start_date(request.event_id,sysdate));
989 IF request.usergroup_id IS NULL THEN
990 If request.person_id IS NOT NULL THEN
991 FOR learner IN csr_get_learner (request.person_id,request.event_id, request.enr_prereq_type,l_event_start_date,l_allow_future_end_prof_val,l_per_bg_grp_prof_val,l_ota_global_bg_prof_val) LOOP
992 IF learner_can_enroll_in_class(request.event_id,learner.person_id) = 'Y' THEN
993 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);
994 END IF;
995
996 END LOOP;--END FOR learner IN csr_get_learner
997 ELSE
998 FOR asg_learner IN csr_get_learners (request.organization_id, request.job_id, request.position_id, request.org_structure_version_id,
999 request.event_id, request.enr_prereq_type,l_event_start_date,l_allow_future_end_prof_val,l_per_bg_grp_prof_val,l_ota_global_bg_prof_val) LOOP
1000 IF learner_can_enroll_in_class(request.event_id,asg_learner.person_id) = 'Y' THEN
1001 create_request_member_record(asg_learner.person_id,request.mandatory_enr_request_id,request.event_id,request.enr_prereq_type,asg_learner.completed_crs_prereq,asg_learner.completed_comp_prereq,l_numberof_records_processed);
1002 END IF;
1003
1004 END LOOP;--END FOR learner IN csr_get_learners
1005 END IF;
1006 ELSE
1007 --resolve the members FOR the user group AND them to ota_mandatory_enr_request_members
1008 --usergroup_whereclause :=TO_CHAR(ota_learner_access_util.get_ug_whereclause(request.usergroup_id, -1));
1009 usergroup_whereclause :=ota_learner_access_util.get_ug_whereclause(request.usergroup_id, -1);
1010 --FND_FILE.PUT_LINE(FND_FILE.LOG,'usergroup_whereclause : '||usergroup_whereclause);
1011 sql_stmnt :='SELECT * FROM(
1012 SELECT
1013 ppf.person_id person_id
1014 , pjt.job_id job_id
1015 , pps.position_id position_id
1016 ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,''E'',:1 ) completed_crs_prereq
1017 ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,:2) completed_comp_prereq
1018 , paf.organization_id organization_id
1019 , paf.assignment_id assignment_id
1020 FROM per_all_people_f ppf
1021 ,per_all_assignments_f paf
1022 ,per_jobs_tl pjt
1023 ,per_all_positions pps
1024 ,per_person_type_usages_f ptu
1025 ,per_person_types pts
1026 ,per_person_types_tl ptt
1027 ,hr_all_organization_units_tl orgTl
1028 ,per_business_groups pbg
1029 WHERE ppf.person_id = paf.person_id
1030 AND (pts.system_person_type IN (''EMP'',''CWK'') OR (paf.assignment_type = ''A'' AND pts.system_person_type =''APL''))
1031 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1032 AND ( (nvl(fnd_profile.value(''OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS''),''N'') = ''Y''
1033 AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
1034 OR nvl(:3, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
1035 AND ( (nvl(fnd_profile.value(''OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS''),''N'') = ''Y''
1036 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
1037 OR nvl(:4, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
1038 AND paf.job_id = pjt.job_id(+)
1039 AND pjt.language(+) = USERENV(''LANG'')
1040 AND pps.position_id(+) = paf.position_id
1041 AND pts.person_type_id = ptt.person_type_id
1042 AND ptt.language = USERENV(''LANG'')
1043 AND pts.person_type_id = ptu.person_type_id
1044 AND ptu.person_id = ppf.person_id
1045 AND paf.organization_id = orgtl.organization_id
1046 AND pts.system_person_type IN (''EMP'', ''CWK'', ''APL'')
1047 AND paf.assignment_type IN (''A'',''E'',''C'')
1048 AND orgtl.language = USERENV(''LANG'')
1049 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''))
1050 AND paf.business_group_id = pbg.business_group_id
1051 AND
1052 ((pts.system_person_type = ''APL''
1053 AND NOT EXISTS (SELECT person_id
1054 FROM per_person_type_usages_f ptf,
1055 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
1056 AND ptf.person_type_id = ptp.person_type_id
1057 AND ptp.system_person_type IN (''EMP'', ''CWK'')
1058 AND ptf.person_id = ppf.PERSON_ID)
1059 )
1060 OR pts.system_person_type IN (''EMP'', ''CWK''))
1061 AND OTA_MANDATORY_ENROLL_UTIL.learner_can_enroll_in_class(:5,ppf.person_id) = ''Y''
1062 )QRSLT WHERE'|| usergroup_whereclause;
1063
1064 v_upperbound := CEIL(DBMS_LOB.GETLENGTH(sql_stmnt)/256);
1065 FOR i IN 1..v_upperbound
1066 LOOP
1067 v_split_sql_stmt(i) := DBMS_LOB.SUBSTR(sql_stmnt
1068 ,256 -- amount
1069 ,((i-1)*256)+1 -- offset
1070 );
1071 END LOOP;
1072
1073 v_cur := DBMS_SQL.OPEN_CURSOR;
1074 DBMS_SQL.PARSE(v_cur, v_split_sql_stmt, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
1075 DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_person_id);
1076 DBMS_SQL.DEFINE_COLUMN(v_cur, 2, v_job_id);
1077 DBMS_SQL.DEFINE_COLUMN(v_cur, 3, v_pos_id);
1078 DBMS_SQL.DEFINE_COLUMN(v_cur, 4, v_is_crs_prereq_comp,1);
1079 DBMS_SQL.DEFINE_COLUMN(v_cur, 5, v_is_comp_prereq_comp,1);
1080 DBMS_SQL.DEFINE_COLUMN(v_cur, 6, v_org_id);
1081 DBMS_SQL.DEFINE_COLUMN(v_cur, 7, v_assign_id);
1082
1083 DBMS_SQL.BIND_VARIABLE(v_cur, ':1', request.event_id);
1084 DBMS_SQL.BIND_VARIABLE(v_cur, ':2', request.event_id);
1085 DBMS_SQL.BIND_VARIABLE(v_cur, ':3', l_event_start_date);
1086 DBMS_SQL.BIND_VARIABLE(v_cur, ':4', l_event_start_date);
1087 DBMS_SQL.BIND_VARIABLE(v_cur, ':5', request.event_id);
1088
1089 ignore := DBMS_SQL.EXECUTE(v_cur);
1090
1091 LOOP
1092 IF DBMS_SQL.FETCH_ROWS(v_cur)>0 THEN
1093 -- get column values of the row
1094 DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_person_id);
1095 DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_job_id);
1096 DBMS_SQL.COLUMN_VALUE(v_cur, 3, v_pos_id);
1097 DBMS_SQL.COLUMN_VALUE(v_cur, 4, v_is_crs_prereq_comp);
1098 DBMS_SQL.COLUMN_VALUE(v_cur, 5, v_is_comp_prereq_comp);
1099 DBMS_SQL.COLUMN_VALUE(v_cur, 6, v_org_id);
1100 DBMS_SQL.COLUMN_VALUE(v_cur, 7, v_assign_id);
1101
1102 create_request_member_record(v_person_id,
1103 request.mandatory_enr_request_id,
1104 request.event_id,
1105 request.enr_prereq_type,
1106 v_is_crs_prereq_comp,
1107 v_is_comp_prereq_comp,
1108 l_numberof_records_processed);
1109
1110 ELSE
1111
1112 -- No more rows:
1113 EXIT;
1114 END IF;
1115 END LOOP;
1116
1117 DBMS_SQL.CLOSE_CURSOR(v_cur);
1118
1119 /*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;
1120 LOOP
1121 FETCH csr_get_lrnr_in_ug into lrnr_rec;
1122 EXIT WHEN csr_get_lrnr_in_ug%NOTFOUND;
1123 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);
1124 END LOOP;
1125 CLOSE csr_get_lrnr_in_ug;*/
1126
1127 END IF;--END IF request.usergroup_id IS NULL
1128
1129 IF l_numberof_records_processed > 1000 THEN
1130 COMMIT;
1131 l_numberof_records_processed :=0;
1132 END IF;
1133
1134 END LOOP;
1135
1136 COMMIT;
1137 --All the records to be processed are now present IN ota_mandatory_enr_req_members with create_enrollment = 'Y'.
1138 --The records which cannot be created due to unfulfilled course/competence prereq have the respective flags set to 'N'
1139 --and create_enrollment IS set to 'N'
1140
1141
1142 create_enrollments(p_conc_request_id);
1143
1144
1145
1146 --Write the error messages to log
1147 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
1148 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unsuccessful Learners');
1149 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
1150 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name Class Reason');
1151 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
1152 FOR unsuccessful_learner IN unprocessed_enrollments(p_conc_request_id) LOOP
1153 OPEN csr_get_person_name(unsuccessful_learner.person_id);
1154 FETCH csr_get_person_name INTO l_person_name;
1155 CLOSE csr_get_person_name;
1156 IF unsuccessful_learner.create_enrollment = 'N' THEN
1157 IF unsuccessful_learner.completed_course_prereq = 'N' THEN
1158 --The learner has NOT completed the course perquisites FOR the event
1159 /*FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1160 FND_FILE.PUT_LINE(FND_FILE.LOG,'The learner has NOT completed the course perquisites for the class -'||unsuccessful_learner.title);
1161 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');*/
1162 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||'Incomplete course prerequisites');
1163 END IF;
1164 IF unsuccessful_learner.completed_competence_prereq = 'N' THEN
1165 --The learner has NOT completed the competence perquisites FOR the event
1166 /*FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1167 FND_FILE.PUT_LINE(FND_FILE.LOG,'The learner has NOT completed the competence perquisites for the class -'||unsuccessful_learner.title);
1168 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');*/
1169 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||'Incomplete competence prerequisites');
1170 END IF;
1171 END IF;
1172
1173 IF unsuccessful_learner.error_message IS NOT NULL THEN
1174 /* FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1175 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error message - '||unsuccessful_learner.error_message);
1176 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');*/
1177 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||unsuccessful_learner.error_message);
1178 END IF;
1179
1180
1181 END LOOP;
1182
1183 hr_utility.set_location(' Leaving:'||l_proc, 10);
1184
1185
1186 END process_mandatory_enr_requests;
1187
1188
1189 PROCEDURE process_mand_enr_reqs_multi(p_conc_request_id IN NUMBER,
1190 p_chunk_size in number,
1191 p_thread_count in number,
1192 p_event_id IN NUMBER) IS
1193
1194
1195 TYPE learners_in_usergroup IS REF CURSOR;
1196 csr_get_lrnr_in_ug learners_in_usergroup;
1197
1198 TYPE learner_rec IS RECORD(
1199 person_id per_all_people_f.person_id%type,
1200 job_id per_jobs_tl.job_id%type,
1201 position_id per_all_positions.position_id%type,
1202 completed_crs_prereq varchar2(1),
1203 completed_comp_prereq varchar2(1),
1204 organization_id per_all_assignments_f.organization_id%type,
1205 assignment_id per_all_assignments_f.assignment_id%type
1206 );
1207 lrnr_rec learner_rec;
1208
1209 CURSOR csr_get_learner(
1210 p_person_id ota_event_associations.person_id%type,
1211 p_event_id ota_event_associations.event_id%type,
1212 p_enr_prereq_type ota_event_associations.mandatory_enrollment_prereq%type,
1213 p_event_start_date date,
1214 p_future_enddated_prof_val varchar2,
1215 p_per_bg_grp_prof_val number,
1216 p_ota_global_bg_prof_val number
1217 ) IS
1218 SELECT ppf.person_id
1219 ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
1220 ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
1221 , paf.organization_id
1222 , paf.assignment_id
1223 FROM per_all_people_f ppf
1224 ,per_all_assignments_f paf
1225 ,per_person_type_usages_f ptu
1226 ,per_person_types pts
1227 ,per_business_groups pbg
1228 WHERE
1229 paf.person_id = p_person_id
1230 AND ppf.person_id = paf.person_id
1231 AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag= 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
1232 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1233 AND ((p_future_enddated_prof_val = 'Y'
1234 AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
1235 OR nvl(p_event_start_date, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
1236 AND((p_future_enddated_prof_val = 'Y'
1237 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
1238 OR nvl(p_event_start_date, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
1239 AND pts.person_type_id = ptu.person_type_id
1240 AND ptu.person_id = ppf.person_id
1241 -- AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
1242 AND paf.assignment_type IN ('A','E','C')
1243 AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
1244 AND paf.business_group_id = pbg.business_group_id
1245 AND
1246 ((pts.system_person_type = 'APL'
1247 AND NOT EXISTS (SELECT person_id
1248 FROM per_person_type_usages_f ptf,
1249 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
1250 AND ptf.person_type_id = ptp.person_type_id
1251 AND ptp.system_person_type IN ('EMP', 'CWK')
1252 AND ptf.person_id = ppf.PERSON_ID)
1253 )
1254 OR pts.system_person_type IN ('EMP', 'CWK'));
1255
1256
1257
1258
1259
1260
1261 CURSOR csr_get_learners(
1262 p_organization_id ota_event_associations.organization_id%type,
1263 p_job_id ota_event_associations.job_id%type,
1264 p_position_id ota_event_associations.position_id%type,
1265 p_org_structure_version_id ota_event_associations. org_structure_version_id%type,
1266 p_event_id ota_event_associations.event_id%type,
1267 p_enr_prereq_type ota_event_associations.mandatory_enrollment_prereq%type,
1268 p_event_start_date date,
1269 p_future_enddated_prof_val varchar2,
1270 p_per_bg_grp_prof_val number,
1271 p_ota_global_bg_prof_val number
1272 ) IS
1273 SELECT ppf.person_id
1274 , pjt.job_id Job_Id
1275 , pps.position_id
1276 ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
1277 ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
1278 , paf.organization_id
1279 , paf.assignment_id
1280 FROM per_all_people_f ppf
1281 ,per_all_assignments_f paf
1282 ,per_jobs_tl pjt
1283 ,per_all_positions pps
1284 ,per_person_type_usages_f ptu
1285 ,per_person_types pts
1286 ,hr_all_organization_units_tl orgTl
1287 ,per_business_groups pbg
1288 WHERE ppf.person_id = paf.person_id
1289 AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag= 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
1290 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1291 AND((p_future_enddated_prof_val = 'Y'
1292 AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
1293 OR nvl(p_event_start_date, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
1294 AND((p_future_enddated_prof_val = 'Y'
1295 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
1296 OR nvl(p_event_start_date, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
1297 AND paf.job_id = pjt.job_id(+)
1298 AND pjt.language(+) = USERENV('LANG')
1299 AND pps.position_id(+) = paf.position_id
1300 AND pts.person_type_id = ptu.person_type_id
1301 AND ptu.person_id = ppf.person_id
1302 AND paf.organization_id = orgtl.organization_id
1303 AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
1304 AND paf.assignment_type IN ('A','E','C')
1305 AND orgtl.language = USERENV('LANG')
1306 AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
1307 AND paf.business_group_id = pbg.business_group_id
1308 AND
1309 ((pts.system_person_type = 'APL'
1310 AND NOT EXISTS (SELECT person_id
1311 FROM per_person_type_usages_f ptf,
1312 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
1313 AND ptf.person_type_id = ptp.person_type_id
1314 AND ptp.system_person_type IN ('EMP', 'CWK')
1315 AND ptf.person_id = ppf.PERSON_ID)
1316 )
1317 OR pts.system_person_type IN ('EMP', 'CWK'))
1318 AND
1319 (
1320 ( nvl(p_organization_id, -1) = decode(p_organization_id, NULL, -1, nvl(paf.organization_id,-1))) OR
1321
1322 ( 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')
1323 )
1324 AND nvl(p_job_id, -1) = decode(p_job_id, NULL, -1, nvl(paf.job_id, -1))
1325 AND nvl(p_position_id,-1) = decode(p_position_id, NULL, -1, nvl(paf.position_id, -1));
1326
1327
1328 l_numberof_records_processed NUMBER:= 0;
1329 l_create_enrollment varchar2(1);
1330 l_completed_crs_prereq varchar2(1);
1331 l_completed_comp_prereq varchar2(1);
1332 l_person_name per_all_people_f.full_name%type;
1333 l_event_start_date date;
1334
1335 l_allow_future_end_prof_val varchar2(1);
1336 l_per_bg_grp_prof_val number;
1337 l_ota_global_bg_prof_val number;
1338
1339 sql_stmnt clob;
1340 usergroup_whereclause clob;
1341 v_split_sql_stmt DBMS_SQL.VARCHAR2S;
1342 v_upperbound NUMBER;
1343 v_cur INTEGER;
1344 v_person_id number;
1345 v_job_id number;
1346 v_pos_id number;
1347 v_is_crs_prereq_comp varchar2(1);
1348 v_is_comp_prereq_comp varchar2(1);
1349 v_org_id number;
1350 v_assign_id number;
1351 ignore INTEGER;
1352 l_proc varchar2(72) := g_package||'process_mand_enr_reqs_multi';
1353
1354 l_chunk_size number := p_chunk_size;
1355 l_thread_count number := p_thread_count;
1356 l_threads number := l_thread_count;
1357 l_start_person_action_id number(15);
1358 l_end_person_action_id number(15);
1359 l_person_action_id number(15);
1360 l_num_range number := 0;
1361 l_range_id number(15);
1362 l_object_version_number Number(15);
1363 l_request_id number;
1364 OTA_NO_PERSON_TO_PROCESS exception;
1365 BEGIN
1366
1367 hr_utility.set_location(' Entering:'||l_proc, 5);
1368
1369 l_allow_future_end_prof_val := fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS');
1370 l_per_bg_grp_prof_val := fnd_profile.value('PER_BUSINESS_GROUP_ID');
1371 l_ota_global_bg_prof_val := fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
1372
1373 IF l_allow_future_end_prof_val is null then
1374 l_allow_future_end_prof_val := 'N';
1375 END IF;
1376
1377 FND_FILE.PUT_LINE(FND_FILE.LOG,'Value of profile OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS : '||l_allow_future_end_prof_val);
1378 FND_FILE.PUT_LINE(FND_FILE.LOG,'Value of profile PER_BUSINESS_GROUP_ID : '||l_per_bg_grp_prof_val);
1379 FND_FILE.PUT_LINE(FND_FILE.LOG,'Value of profile OTA_HR_GLOBAL_BUSINESS_GROUP_ID : '||l_ota_global_bg_prof_val);
1380
1381
1382 FOR request IN get_all_mand_multi_enr_reqs(p_conc_request_id) LOOP
1383 l_event_start_date := trunc(ota_learner_access_util.get_event_start_date(request.event_id,sysdate));
1384 IF request.usergroup_id IS NULL THEN
1385 If request.person_id IS NOT NULL THEN
1386 FOR learner IN csr_get_learner (request.person_id,request.event_id, request.enr_prereq_type,l_event_start_date,l_allow_future_end_prof_val,l_per_bg_grp_prof_val,l_ota_global_bg_prof_val) LOOP
1387 IF learner_can_enroll_in_class(request.event_id,learner.person_id) = 'Y' THEN
1388 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,l_person_action_id);
1389 END IF;
1390
1391 if l_person_action_id is not null then
1392
1393 l_end_person_action_id := l_person_action_id;
1394 --
1395 If l_numberof_records_processed = 1 then
1396 --
1397 l_start_person_action_id := l_person_action_id;
1398 --
1399 End if;
1400
1401 End if;
1402
1403 IF l_numberof_records_processed = l_chunk_size THEN
1404 --COMMIT;
1405 ota_batch_action_api.create_batch_ranges
1406 (p_validate => false
1407 ,p_range_id => l_range_id
1408 ,p_batch_source_cd => 'ME'
1409 ,p_batch_action_id => p_conc_request_id
1410 ,p_range_status_cd => 'U'
1411 ,p_starting_person_action_id => l_start_person_action_id
1412 ,p_ending_person_action_id => l_end_person_action_id
1413 ,p_object_version_number => l_object_version_number);
1414
1415 l_start_person_action_id := 0;
1416 l_end_person_action_id := 0;
1417 l_numberof_records_processed :=0;
1418 l_num_range := l_num_range + 1;
1419 END IF;
1420
1421 END LOOP;--END FOR learner IN csr_get_learner
1422 ELSE
1423 FOR asg_learner IN csr_get_learners (request.organization_id, request.job_id, request.position_id, request.org_structure_version_id,
1424 request.event_id, request.enr_prereq_type,l_event_start_date,l_allow_future_end_prof_val,l_per_bg_grp_prof_val,l_ota_global_bg_prof_val) LOOP
1425 IF learner_can_enroll_in_class(request.event_id,asg_learner.person_id) = 'Y' THEN
1426 create_request_member_record(asg_learner.person_id,request.mandatory_enr_request_id,request.event_id,request.enr_prereq_type,asg_learner.completed_crs_prereq,
1427 asg_learner.completed_comp_prereq,l_numberof_records_processed,l_person_action_id);
1428 END IF;
1429
1430 if l_person_action_id is not null then
1431
1432 l_end_person_action_id := l_person_action_id;
1433 --
1434 If l_numberof_records_processed = 1 then
1435 --
1436 l_start_person_action_id := l_person_action_id;
1437 --
1438 End if;
1439
1440 End if;
1441
1442 IF l_numberof_records_processed = l_chunk_size THEN
1443 --COMMIT;
1444 ota_batch_action_api.create_batch_ranges
1445 (p_validate => false
1446 ,p_range_id => l_range_id
1447 ,p_batch_source_cd => 'ME'
1448 ,p_batch_action_id => p_conc_request_id
1449 ,p_range_status_cd => 'U'
1450 ,p_starting_person_action_id => l_start_person_action_id
1451 ,p_ending_person_action_id => l_end_person_action_id
1452 ,p_object_version_number => l_object_version_number);
1453
1454 l_start_person_action_id := 0;
1455 l_end_person_action_id := 0;
1456 l_numberof_records_processed :=0;
1457 l_num_range := l_num_range + 1;
1458 END IF;
1459 END LOOP;--END FOR learner IN csr_get_learners
1460 END IF;
1461 ELSE
1462 --resolve the members FOR the user group AND them to ota_mandatory_enr_request_members
1463 --usergroup_whereclause :=TO_CHAR(ota_learner_access_util.get_ug_whereclause(request.usergroup_id, -1));
1464 usergroup_whereclause :=ota_learner_access_util.get_ug_whereclause(request.usergroup_id, -1);
1465 --FND_FILE.PUT_LINE(FND_FILE.LOG,'usergroup_whereclause : '||usergroup_whereclause);
1466 sql_stmnt :='SELECT * FROM(
1467 SELECT
1468 ppf.person_id person_id
1469 , pjt.job_id job_id
1470 , pps.position_id position_id
1471 ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,''E'',:1 ) completed_crs_prereq
1472 ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,:2) completed_comp_prereq
1473 , paf.organization_id organization_id
1474 , paf.assignment_id assignment_id
1475 FROM per_all_people_f ppf
1476 ,per_all_assignments_f paf
1477 ,per_jobs_tl pjt
1478 ,per_all_positions pps
1479 ,per_person_type_usages_f ptu
1480 ,per_person_types pts
1481 ,per_person_types_tl ptt
1482 ,hr_all_organization_units_tl orgTl
1483 ,per_business_groups pbg
1484 WHERE ppf.person_id = paf.person_id
1485 AND ((pts.system_person_type IN (''EMP'',''CWK'') AND paf.primary_flag = ''Y'') OR (paf.assignment_type = ''A'' AND pts.system_person_type =''APL''))
1486 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
1487 AND ( (nvl(fnd_profile.value(''OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS''),''N'') = ''Y''
1488 AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
1489 OR nvl(:3, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
1490 AND ( (nvl(fnd_profile.value(''OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS''),''N'') = ''Y''
1491 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
1492 OR nvl(:4, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
1493 AND paf.job_id = pjt.job_id(+)
1494 AND pjt.language(+) = USERENV(''LANG'')
1495 AND pps.position_id(+) = paf.position_id
1496 AND pts.person_type_id = ptt.person_type_id
1497 AND ptt.language = USERENV(''LANG'')
1498 AND pts.person_type_id = ptu.person_type_id
1499 AND ptu.person_id = ppf.person_id
1500 AND paf.organization_id = orgtl.organization_id
1501 AND pts.system_person_type IN (''EMP'', ''CWK'', ''APL'')
1502 AND paf.assignment_type IN (''A'',''E'',''C'')
1503 AND orgtl.language = USERENV(''LANG'')
1504 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''))
1505 AND paf.business_group_id = pbg.business_group_id
1506 AND
1507 ((pts.system_person_type = ''APL''
1508 AND NOT EXISTS (SELECT person_id
1509 FROM per_person_type_usages_f ptf,
1510 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
1511 AND ptf.person_type_id = ptp.person_type_id
1512 AND ptp.system_person_type IN (''EMP'', ''CWK'')
1513 AND ptf.person_id = ppf.PERSON_ID)
1514 )
1515 OR pts.system_person_type IN (''EMP'', ''CWK''))
1516 AND OTA_MANDATORY_ENROLL_UTIL.learner_can_enroll_in_class(:5,ppf.person_id) = ''Y''
1517 )QRSLT WHERE'|| usergroup_whereclause;
1518
1519 v_upperbound := CEIL(DBMS_LOB.GETLENGTH(sql_stmnt)/256);
1520 FOR i IN 1..v_upperbound
1521 LOOP
1522 v_split_sql_stmt(i) := DBMS_LOB.SUBSTR(sql_stmnt
1523 ,256 -- amount
1524 ,((i-1)*256)+1 -- offset
1525 );
1526 END LOOP;
1527
1528 v_cur := DBMS_SQL.OPEN_CURSOR;
1529 DBMS_SQL.PARSE(v_cur, v_split_sql_stmt, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
1530 DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_person_id);
1531 DBMS_SQL.DEFINE_COLUMN(v_cur, 2, v_job_id);
1532 DBMS_SQL.DEFINE_COLUMN(v_cur, 3, v_pos_id);
1533 DBMS_SQL.DEFINE_COLUMN(v_cur, 4, v_is_crs_prereq_comp,1);
1534 DBMS_SQL.DEFINE_COLUMN(v_cur, 5, v_is_comp_prereq_comp,1);
1535 DBMS_SQL.DEFINE_COLUMN(v_cur, 6, v_org_id);
1536 DBMS_SQL.DEFINE_COLUMN(v_cur, 7, v_assign_id);
1537
1538 DBMS_SQL.BIND_VARIABLE(v_cur, ':1', request.event_id);
1539 DBMS_SQL.BIND_VARIABLE(v_cur, ':2', request.event_id);
1540 DBMS_SQL.BIND_VARIABLE(v_cur, ':3', l_event_start_date);
1541 DBMS_SQL.BIND_VARIABLE(v_cur, ':4', l_event_start_date);
1542 DBMS_SQL.BIND_VARIABLE(v_cur, ':5', request.event_id);
1543
1544 ignore := DBMS_SQL.EXECUTE(v_cur);
1545
1546 LOOP
1547 IF DBMS_SQL.FETCH_ROWS(v_cur)>0 THEN
1548 -- get column values of the row
1549 DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_person_id);
1550 DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_job_id);
1551 DBMS_SQL.COLUMN_VALUE(v_cur, 3, v_pos_id);
1552 DBMS_SQL.COLUMN_VALUE(v_cur, 4, v_is_crs_prereq_comp);
1553 DBMS_SQL.COLUMN_VALUE(v_cur, 5, v_is_comp_prereq_comp);
1554 DBMS_SQL.COLUMN_VALUE(v_cur, 6, v_org_id);
1555 DBMS_SQL.COLUMN_VALUE(v_cur, 7, v_assign_id);
1556
1557 create_request_member_record(v_person_id,
1558 request.mandatory_enr_request_id,
1559 request.event_id,
1560 request.enr_prereq_type,
1561 v_is_crs_prereq_comp,
1562 v_is_comp_prereq_comp,
1563 l_numberof_records_processed,
1564 l_person_action_id);
1565
1566 if l_person_action_id is not null then
1567
1568 l_end_person_action_id := l_person_action_id;
1569 --
1570 If l_numberof_records_processed = 1 then
1571 --
1572 l_start_person_action_id := l_person_action_id;
1573 --
1574 End if;
1575
1576 End if;
1577
1578 IF l_numberof_records_processed = l_chunk_size THEN
1579 --COMMIT;
1580 ota_batch_action_api.create_batch_ranges
1581 (p_validate => false
1582 ,p_range_id => l_range_id
1583 ,p_batch_source_cd => 'ME'
1584 ,p_batch_action_id => p_conc_request_id
1585 ,p_range_status_cd => 'U'
1586 ,p_starting_person_action_id => l_start_person_action_id
1587 ,p_ending_person_action_id => l_end_person_action_id
1588 ,p_object_version_number => l_object_version_number);
1589
1590 l_start_person_action_id := 0;
1591 l_end_person_action_id := 0;
1592 l_numberof_records_processed :=0;
1593 l_num_range := l_num_range + 1;
1594 END IF;
1595
1596 ELSE
1597
1598 -- No more rows:
1599 EXIT;
1600 END IF;
1601 END LOOP;
1602
1603 DBMS_SQL.CLOSE_CURSOR(v_cur);
1604
1605 /*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;
1606 LOOP
1607 FETCH csr_get_lrnr_in_ug into lrnr_rec;
1608 EXIT WHEN csr_get_lrnr_in_ug%NOTFOUND;
1609 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,
1610 lrnr_rec.completed_comp_prereq,l_numberof_records_processed,l_person_action_id);
1611
1612 if l_person_action_id is not null then
1613
1614 l_end_person_action_id := l_person_action_id;
1615 --
1616 If l_numberof_records_processed = 1 then
1617 --
1618 l_start_person_action_id := l_person_action_id;
1619 --
1620 End if;
1621
1622 End if;
1623
1624 IF l_numberof_records_processed = l_chunk_size THEN
1625 --COMMIT;
1626 ota_batch_action_api.create_batch_ranges
1627 (p_validate => false
1628 ,p_range_id => l_range_id
1629 ,p_batch_source_cd => 'ME'
1630 ,p_batch_action_id => p_conc_request_id
1631 ,p_range_status_cd => 'U'
1632 ,p_starting_person_action_id => l_start_person_action_id
1633 ,p_ending_person_action_id => l_end_person_action_id
1634 ,p_object_version_number => l_object_version_number);
1635
1636 l_start_person_action_id := 0;
1637 l_end_person_action_id := 0;
1638 l_numberof_records_processed :=0;
1639 l_num_range := l_num_range + 1;
1640 END IF;
1641 END LOOP;
1642 CLOSE csr_get_lrnr_in_ug;*/
1643
1644 END IF;--END IF request.usergroup_id IS NULL
1645
1646 END LOOP;
1647
1648 If l_numberof_records_processed <> 0 then
1649 --
1650 --
1651 ota_batch_action_api.create_batch_ranges
1652 (p_validate => false
1653 ,p_range_id => l_range_id
1654 ,p_batch_source_cd => 'ME'
1655 ,p_batch_action_id => p_conc_request_id
1656 ,p_range_status_cd => 'U'
1657 ,p_starting_person_action_id => l_start_person_action_id
1658 ,p_ending_person_action_id => l_end_person_action_id
1659 ,p_object_version_number => l_object_version_number);
1660
1661 l_num_range := l_num_range + 1;
1662 --
1663 End if;
1664
1665 COMMIT;
1666
1667 if l_num_range > 1 --2
1668 then
1669
1670 l_threads := least(l_threads, l_num_range);
1671 --
1672 for l_count in 1..(l_threads - 1)
1673 loop
1674 --
1675 --l_request_id := fnd_request.submit_request();
1676
1677 l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'OTA'
1678 ,program => 'OTA_MAND_ENR_MULTI'
1679 ,argument1 => p_conc_request_id
1680 ,argument2 => l_chunk_size
1681 ,argument3 => l_thread_count
1682 ,argument4 => p_event_id);
1683
1684
1685 --
1686 -- Store the request id of the concurrent request
1687 --
1688 ota_mandatory_enroll_util.g_num_processes := ota_mandatory_enroll_util.g_num_processes + 1;
1689 ota_mandatory_enroll_util.g_processes_tbl(ota_mandatory_enroll_util.g_num_processes) := l_request_id;
1690
1691 --
1692 end loop;
1693 --
1694 commit;
1695 --
1696 elsif l_num_range = 0
1697 then
1698 -- Means no person is selected or no ug to process
1699 --
1700 --
1701 raise OTA_NO_PERSON_TO_PROCESS;
1702
1703 --
1704 end if; --2
1705 --All the records to be processed are now present IN OTA_MAND_MULTI_ENR_REQ_MEMBERS with create_enrollment = 'Y'.
1706 --The records which cannot be created due to unfulfilled course/competence prereq have the respective flags set to 'N'
1707 --and create_enrollment IS set to 'N'
1708
1709
1710 create_enrollments_multi(p_conc_request_id);
1711
1712 hr_utility.set_location(' Leaving:'||l_proc, 10);
1713
1714 Exception
1715 when OTA_NO_PERSON_TO_PROCESS then
1716 FND_FILE.PUT_LINE(FND_FILE.LOG,'No Person record to process');
1717 END process_mand_enr_reqs_multi;
1718
1719 PROCEDURE create_enrollments(p_conc_reqId IN NUMBER) IS
1720
1721
1722 CURSOR csr_get_request_members(l_conc_reqID NUMBER) IS
1723 SELECT
1724 reqmembers.MANDATORY_ENR_REQUEST_ID,
1725 reqmembers.PERSON_ID,
1726 reqmembers.ASSIGNMENT_ID,
1727 reqmembers.EVENT_ID,
1728 reqmembers.ERROR_MESSAGE,
1729 reqmembers.ORGANIZATION_ID,
1730 reqmembers.BUSINESS_GROUP_ID
1731 FROM
1732 ota_mandatory_enr_req_members reqmembers,
1733 ota_mandatory_enr_requests requests
1734 WHERE
1735 requests.conc_program_request_id = l_conc_reqId
1736 AND requests.mandatory_enr_request_id = reqmembers.mandatory_enr_request_id
1737 AND reqmembers.create_enrollment = 'Y'
1738 ORDER BY event_id;
1739
1740
1741
1742 CURSOR csr_get_cost_center_info(l_assignment_id NUMBER) IS
1743 SELECT pcak.cost_allocation_keyflex_id
1744 FROM per_all_assignments_f assg,
1745 pay_cost_allocations_f pcaf,
1746 pay_cost_allocation_keyflex pcak
1747 WHERE assg.assignment_id = pcaf.assignment_id
1748 AND assg.assignment_id = l_assignment_id
1749 AND assg.Primary_flag = 'Y'
1750 AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
1751 AND pcak.enabled_flag = 'Y'
1752 AND sysdate BETWEEN nvl(pcaf.effective_start_date,sysdate)
1753 AND nvl(pcaf.effective_end_date,sysdate+1)
1754 AND trunc(sysdate) BETWEEN nvl(assg.effective_start_date,trunc(sysdate))
1755 AND nvl(assg.effective_end_date,trunc(sysdate+1));
1756
1757 l_error_message ota_mandatory_enr_req_members.error_message%type;
1758 l_mandaotory_enr_request_id ota_mandatory_enr_req_members.mandatory_enr_request_id%type;
1759 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%type;
1760 l_booking_status ota_booking_status_types_tl.name%type;
1761
1762 l_request_rec get_all_mandatory_enr_requests%rowtype;
1763 l_req_member_rec csr_get_request_members%rowtype;
1764 l_booking_id ota_delegate_bookings.booking_id%type;
1765
1766
1767 l_cost_center_info csr_get_cost_center_info%rowtype;
1768
1769 l_person_name per_all_people_f.full_name%type;
1770 l_class_name ota_events_tl.title%type;
1771 l_proc varchar2(72) := g_package||'create_enrollments';
1772
1773 BEGIN
1774
1775 hr_utility.set_location(' Entering:'||l_proc, 5);
1776
1777 OPEN get_all_mandatory_enr_requests(p_conc_reqId);
1778 FETCH get_all_mandatory_enr_requests INTO l_request_rec;
1779 IF get_all_mandatory_enr_requests%NOTFOUND THEN
1780 -- Raise error that no request found
1781 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR-No requests found FOR concurrent program- '||p_conc_reqId);
1782 CLOSE get_all_mandatory_enr_requests;
1783 RETURN;
1784 ELSE
1785 CLOSE get_all_mandatory_enr_requests;
1786 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
1787 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successful Learners');
1788 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
1789 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name Class ');
1790 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
1791
1792 FOR l_req_member_rec IN csr_get_request_members(p_conc_reqId) LOOP
1793
1794 l_booking_id := NULL;
1795 l_error_message := NULL;
1796
1797 OPEN csr_get_person_name(l_req_member_rec.person_id);
1798 FETCH csr_get_person_name INTO l_person_name;
1799 CLOSE csr_get_person_name;
1800 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1801
1802
1803 OPEN csr_get_cost_center_info(l_req_member_rec.assignment_id);
1804 FETCH csr_get_cost_center_info INTO l_cost_center_info;
1805 CLOSE csr_get_cost_center_info;
1806
1807 BEGIN
1808
1809 ota_bulk_enroll_util.Create_Enrollment_And_Finance(
1810 p_event_id => l_req_member_rec.event_id
1811 ,p_cost_centers => l_cost_center_info.cost_allocation_keyflex_id
1812 ,p_assignment_id => l_req_member_rec.assignment_id
1813 ,p_delegate_contact_id => NULL
1814 ,p_business_group_id_from => l_req_member_rec.business_group_id
1815 ,p_organization_id => l_req_member_rec.organization_id
1816 ,p_person_id => l_req_member_rec.person_id
1817 ,p_booking_id => l_booking_id
1818 ,p_message_name => l_error_message
1819 ,p_override_prerequisites => 'Y'
1820 ,p_is_mandatory_enrollment => 'Y');
1821 EXCEPTION
1822 WHEN OTHERS THEN
1823 l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating Enrollment ');
1824 UPDATE ota_mandatory_enr_req_members
1825 SET error_message = l_error_message
1826 WHERE person_id = l_req_member_rec.person_id
1827 AND event_id = l_req_member_rec.event_id
1828 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;
1829 END;
1830
1831 OPEN get_class_name(l_req_member_rec.event_id);
1832 FETCH get_class_name into l_class_name;
1833 CLOSE get_class_name;
1834
1835 IF l_booking_id IS NOT NULL THEN
1836 FND_FILE.PUT_LINE(FND_FILE.LOG,l_person_name||' | '||l_class_name);
1837 --FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
1838 UPDATE ota_mandatory_enr_req_members
1839 SET error_message = NULL
1840 WHERE person_id = l_req_member_rec.person_id
1841 AND assignment_id = l_req_member_rec.assignment_id
1842 AND event_id = l_req_member_rec.event_id
1843 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;
1844 ELSE
1845 l_error_message := nvl(substr(l_error_message,1,2000),'Booking_id IS NULL');
1846 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Error when creating enrollment into class- '||l_class_name);
1847 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
1848 --FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
1849
1850 UPDATE ota_mandatory_enr_req_members
1851 SET error_message = l_error_message
1852 WHERE person_id = l_req_member_rec.person_id
1853 AND event_id = l_req_member_rec.event_id
1854 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;
1855 END IF;
1856
1857 END LOOP;
1858 -- Start workflow AND send a notification to the requestor
1859 notify_class_owners(p_conc_reqId);
1860 END IF;
1861
1862 hr_utility.set_location(' Leaving:'||l_proc, 10);
1863
1864 END create_enrollments;
1865
1866
1867 PROCEDURE create_enrollments_multi(p_conc_reqId IN NUMBER) IS
1868
1869 l_start_person_action_id number DEFAULT 0;
1870 l_end_person_action_id number DEFAULT 0;
1871 l_range_id OTA_BATCH_RANGES.range_id%TYPE;
1872
1873 CURSOR csr_get_request_members(l_conc_reqID NUMBER) IS
1874 SELECT
1875 reqmembers.MANDATORY_ENR_REQUEST_ID,
1876 reqmembers.PERSON_ID,
1877 reqmembers.ASSIGNMENT_ID,
1878 reqmembers.EVENT_ID,
1879 reqmembers.ERROR_MESSAGE,
1880 reqmembers.ORGANIZATION_ID,
1881 reqmembers.MBR_BG_ID,
1882 reqmembers.CERTIFICATION_ID,
1883 reqmembers.CREATE_ENROLLMENT
1884 FROM
1885 OTA_MAND_MULTI_ENR_REQ_MEMBERS reqmembers
1886 WHERE
1887 reqmembers.create_enrollment = 'Y'
1888 AND reqmembers.person_action_id BETWEEN l_start_person_action_id AND l_end_person_action_id
1889 ORDER BY certification_id;
1890
1891 CURSOR csr_get_cost_center_info(l_assignment_id NUMBER) IS
1892 SELECT pcak.cost_allocation_keyflex_id
1893 FROM per_all_assignments_f assg,
1894 pay_cost_allocations_f pcaf,
1895 pay_cost_allocation_keyflex pcak
1896 WHERE assg.assignment_id = pcaf.assignment_id
1897 AND assg.assignment_id = l_assignment_id
1898 AND assg.Primary_flag = 'Y'
1899 AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
1900 AND pcak.enabled_flag = 'Y'
1901 AND sysdate BETWEEN nvl(pcaf.effective_start_date,sysdate)
1902 AND nvl(pcaf.effective_end_date,sysdate+1)
1903 AND trunc(sysdate) BETWEEN nvl(assg.effective_start_date,trunc(sysdate))
1904 AND nvl(assg.effective_end_date,trunc(sysdate+1));
1905
1906 CURSOR c_range_thread IS
1907 SELECT ran.range_id
1908 ,ran.starting_person_action_id
1909 ,ran.ending_person_action_id
1910 FROM OTA_BATCH_RANGES ran
1911 WHERE ran.range_status_cd = 'U'
1912 AND ran.batch_source_cd = 'ME'
1913 AND ran.BATCH_ACTION_ID = p_conc_reqId
1914 AND rownum < 2
1915 FOR UPDATE OF ran.range_status_cd;
1916
1917 l_error_message OTA_MAND_MULTI_ENR_REQ_MEMBERS.error_message%type;
1918 l_mandaotory_enr_request_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type;
1919 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%type;
1920 l_booking_status ota_booking_status_types_tl.name%type;
1921
1922 l_request_rec get_all_mandatory_enr_requests%rowtype;
1923 l_req_member_rec csr_get_request_members%rowtype;
1924 l_booking_id ota_delegate_bookings.booking_id%type;
1925
1926
1927 l_cost_center_info csr_get_cost_center_info%rowtype;
1928
1929 l_person_name per_all_people_f.full_name%type;
1930 l_class_name ota_events_tl.title%type;
1931 l_proc varchar2(72) := g_package||'create_enrollments_multi';
1932
1933 BEGIN
1934
1935 hr_utility.set_location(' Entering:'||l_proc, 5);
1936
1937 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
1938 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successful Learners');
1939 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
1940 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name Class ');
1941 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
1942
1943 LOOP
1944 OPEN c_range_thread;
1945
1946 FETCH c_range_thread
1947 INTO l_range_id
1948 ,l_start_person_action_id
1949 ,l_end_person_action_id;
1950
1951 IF c_range_thread%NOTFOUND THEN
1952
1953 CLOSE c_range_thread;
1954
1955 EXIT;
1956 END IF;
1957
1958 CLOSE c_range_thread;
1959
1960 UPDATE OTA_BATCH_RANGES ran
1961 SET ran.range_status_cd = 'P'
1962 WHERE ran.range_id = l_range_id;
1963
1964 COMMIT;
1965
1966 FOR l_req_member_rec IN csr_get_request_members(p_conc_reqId) LOOP
1967
1968 l_booking_id := NULL;
1969 l_error_message := NULL;
1970
1971 OPEN csr_get_person_name(l_req_member_rec.person_id);
1972 FETCH csr_get_person_name INTO l_person_name;
1973 CLOSE csr_get_person_name;
1974 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1975
1976
1977 OPEN csr_get_cost_center_info(l_req_member_rec.assignment_id);
1978 FETCH csr_get_cost_center_info INTO l_cost_center_info;
1979 CLOSE csr_get_cost_center_info;
1980
1981 BEGIN
1982
1983 ota_bulk_enroll_util.Create_Enrollment_And_Finance(
1984 p_event_id => l_req_member_rec.event_id
1985 ,p_cost_centers => l_cost_center_info.cost_allocation_keyflex_id
1986 ,p_assignment_id => l_req_member_rec.assignment_id
1987 ,p_delegate_contact_id => NULL
1988 ,p_business_group_id_from => l_req_member_rec.mbr_bg_id
1989 ,p_organization_id => l_req_member_rec.organization_id
1990 ,p_person_id => l_req_member_rec.person_id
1991 ,p_booking_id => l_booking_id
1992 ,p_message_name => l_error_message
1993 ,p_override_prerequisites => 'Y'
1994 ,p_is_mandatory_enrollment => 'Y');
1995 EXCEPTION
1996 WHEN OTHERS THEN
1997 l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating Enrollment ');
1998 UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
1999 SET error_message = l_error_message
2000 WHERE person_id = l_req_member_rec.person_id
2001 AND event_id = l_req_member_rec.event_id
2002 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;
2003 END;
2004
2005 OPEN get_class_name(l_req_member_rec.event_id);
2006 FETCH get_class_name into l_class_name;
2007 CLOSE get_class_name;
2008
2009 IF l_booking_id IS NOT NULL THEN
2010 FND_FILE.PUT_LINE(FND_FILE.LOG,l_person_name||' | '||l_class_name);
2011 --FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
2012
2013 UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
2014 SET action_status_cd = 'P',error_message = NULL
2015 WHERE person_id = l_req_member_rec.person_id
2016 AND assignment_id = l_req_member_rec.assignment_id
2017 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id
2018 AND action_status_cd not in ('P','E');
2019
2020 ELSE
2021 l_error_message := nvl(substr(l_error_message,1,2000),'Booking_id IS NULL');
2022 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Error when creating enrollment into class- '||l_class_name);
2023 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
2024 --FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
2025
2026 UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
2027 SET error_message = l_error_message, action_status_cd = 'E'
2028 WHERE person_id = l_req_member_rec.person_id
2029 AND event_id = l_req_member_rec.event_id
2030 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;
2031 END IF;
2032
2033 END LOOP;
2034
2035 commit;
2036 hr_utility.set_location(' Leaving:'||l_proc, 10);
2037 END LOOP;
2038
2039 END create_enrollments_multi;
2040
2041
2042
2043 PROCEDURE notify_mandatory_request(p_person_id in NUMBER,
2044 p_conc_program_request_id in NUMBER,
2045 p_object_type in VARCHAR2,
2046 p_object_id in NUMBER,
2047 p_error_learners in NUMBER,
2048 p_success_learners in NUMBER,
2049 p_process in wf_activities.name%type)
2050 IS
2051 l_proc varchar2(72) := g_package||'notify_mandatory_request';
2052 -- l_process wf_activities.name%type :='OTA_BLK_MANDATORY_ENR_NTF_PRC';
2053 l_item_type wf_items.item_type%type := 'OTWF';
2054 l_item_key wf_items.item_key%type;
2055
2056 l_user_name varchar2(80);
2057 l_person_full_name per_all_people_f.FULL_NAME%TYPE;
2058 l_role_name wf_roles.name%type;
2059 l_role_display_name wf_roles.display_name%type;
2060
2061 l_process_display_name varchar2(240);
2062
2063 CURSOR csr_get_user_name(p_person_id IN VARCHAR2) IS
2064 SELECT user_name FROM fnd_user WHERE employee_id=p_person_id
2065 and trunc(sysdate) between trunc(start_date) and trunc(nvl(end_date, sysdate+1));
2066
2067
2068 CURSOR csr_get_person_name(p_person_id IN number) IS
2069 SELECT ppf.full_name FROM per_all_people_f ppf WHERE person_id = p_person_id;
2070
2071 BEGIN
2072 hr_utility.set_location('Entering:'||l_proc, 5);
2073
2074 -- Get the next item key from the sequence
2075 select hr_workflow_item_key_s.nextval into l_item_key from sys.dual;
2076
2077 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, p_process);
2078
2079 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'CONC_REQUEST_ID',p_conc_program_request_id);
2080 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'OBJECT_NAME',p_object_id);
2081 WF_ENGINE.setitemattrtext(l_item_type, l_item_key, 'OBJECT_TYPE' ,p_object_type);
2082 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'ERROR_NUMBER',p_error_learners);
2083 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'SUCCESS_NUMBER',p_success_learners);
2084
2085 IF p_person_id IS NOT NULL THEN
2086 OPEN csr_get_person_name(p_person_id);
2087 FETCH csr_get_person_name INTO l_person_full_name;
2088 CLOSE csr_get_person_name;
2089
2090 OPEN csr_get_user_name(p_person_id);
2091 FETCH csr_get_user_name INTO l_user_name;
2092 CLOSE csr_get_user_name;
2093
2094 --fnd_file.put_line(FND_FILE.LOG,'Requestor Name ' ||l_person_full_name);
2095
2096 IF l_person_full_name IS NOT NULL then
2097 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'EVENT_OWNER',l_user_name);
2098 END IF;
2099 END IF;
2100
2101 -- Get and set owner role
2102
2103 hr_utility.set_location('Before Getting Owner'||l_proc, 10);
2104
2105 WF_DIRECTORY.GetRoleName(p_orig_system =>'PER',
2106 p_orig_system_id => p_person_id,
2107 p_name =>l_role_name,
2108 p_display_name =>l_role_display_name);
2109
2110
2111 WF_ENGINE.SetItemOwner(itemtype => l_item_type,
2112 itemkey =>l_item_key,
2113 owner =>l_role_name);
2114
2115 hr_utility.set_location('After Setting Owner'||l_proc, 10);
2116
2117
2118 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
2119
2120 hr_utility.set_location('leaving:'||l_proc, 20);
2121
2122 EXCEPTION
2123 WHEN OTHERS THEN
2124 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2125 END notify_mandatory_request;
2126
2127
2128
2129
2130 PROCEDURE notify_class_owners(p_conc_reqId IN NUMBER)
2131 IS
2132
2133 CURSOR csr_get_all_event_owners IS
2134 SELECT distinct event_id,requestor_id
2135 from ota_mandatory_enr_requests
2136 where requestor_id IS NOT NULL
2137 and conc_program_request_id = p_conc_reqId ;
2138
2139 CURSOR csr_get_error_learners(p_event_id IN number) IS
2140 SELECT COUNT(distinct person_id)
2141 FROM ota_mandatory_enr_req_members reqm
2142 WHERE reqm.event_id = p_event_id
2143 AND(reqm.create_enrollment = 'N' or reqm.error_message IS NOT NULL);
2144
2145
2146 CURSOR csr_get_successful_learners(p_event_id IN number) IS
2147 SELECT COUNT( distinct person_id)
2148 FROM ota_mandatory_enr_req_members reqm
2149 WHERE reqm.event_id = p_event_id
2150 AND(reqm.create_enrollment = 'Y' and reqm.error_message IS NULL);
2151
2152 l_error_learners NUMBER := 0;
2153 l_success_learners NUMBER := 0;
2154 l_process wf_activities.name%type :='OTA_BLK_MANDATORY_ENR_NTF_PRC';
2155 l_proc varchar2(72) := g_package||'notify_class_owners';
2156
2157 BEGIN
2158 hr_utility.set_location('Entering:'||l_proc, 5);
2159 for owner in csr_get_all_event_owners loop
2160
2161 OPEN csr_get_error_learners(owner.event_id);
2162 FETCH csr_get_error_learners INTO l_error_learners;
2163 CLOSE csr_get_error_learners;
2164
2165 OPEN csr_get_successful_learners(owner.event_id);
2166 FETCH csr_get_successful_learners INTO l_success_learners;
2167 CLOSE csr_get_successful_learners;
2168
2169 notify_mandatory_request(owner.requestor_id,p_conc_reqId,'CL',owner.event_id,l_error_learners,l_success_learners,l_process);
2170
2171 end loop;
2172
2173
2174 END notify_class_owners;
2175
2176 PROCEDURE notify_multi_class_owners(p_conc_reqId IN NUMBER)
2177 IS
2178
2179 CURSOR csr_get_all_event_owners IS
2180 SELECT distinct event_id,requestor_id
2181 from OTA_MAND_MULTI_ENR_REQUESTS
2182 where requestor_id IS NOT NULL
2183 and conc_program_request_id = p_conc_reqId ;
2184
2185 CURSOR csr_get_error_learners(p_event_id IN number) IS
2186 SELECT COUNT(distinct person_id)
2187 FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS reqm
2188 WHERE reqm.event_id = p_event_id
2189 AND(reqm.create_enrollment = 'N' or reqm.error_message IS NOT NULL);
2190
2191
2192 CURSOR csr_get_successful_learners(p_event_id IN number) IS
2193 SELECT COUNT( distinct person_id)
2194 FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS reqm
2195 WHERE reqm.event_id = p_event_id
2196 AND(reqm.create_enrollment = 'Y' and reqm.error_message IS NULL);
2197
2198 l_error_learners NUMBER := 0;
2199 l_success_learners NUMBER := 0;
2200 l_process wf_activities.name%type :='OTA_BLK_MANDATORY_ENR_NTF_PRC';
2201 l_proc varchar2(72) := g_package||'notify_multi_class_owners';
2202
2203 BEGIN
2204 hr_utility.set_location('Entering:'||l_proc, 5);
2205 for owner in csr_get_all_event_owners loop
2206
2207 OPEN csr_get_error_learners(owner.event_id);
2208 FETCH csr_get_error_learners INTO l_error_learners;
2209 CLOSE csr_get_error_learners;
2210
2211 OPEN csr_get_successful_learners(owner.event_id);
2212 FETCH csr_get_successful_learners INTO l_success_learners;
2213 CLOSE csr_get_successful_learners;
2214
2215 notify_mandatory_request(owner.requestor_id,p_conc_reqId,'CL',owner.event_id,l_error_learners,l_success_learners,l_process);
2216
2217 end loop;
2218
2219
2220 END notify_multi_class_owners;
2221
2222 PROCEDURE process_automatic_cert_subscr( ERRBUF OUT NOCOPY VARCHAR2,
2223 RETCODE OUT NOCOPY VARCHAR2,
2224 p_action_id in number default null,
2225 p_chunk_size in number default 1000,
2226 p_thread_count in number default 3,
2227 p_cert_id IN NUMBER) IS
2228
2229
2230 TYPE auto_cert_subscr_rec IS RECORD(
2231 enr_prereq_type dbms_sql.varchar2_table,
2232 event_id dbms_sql.number_table,
2233 person_id dbms_sql.number_table,
2234 organization_id dbms_sql.number_table,
2235 job_id dbms_sql.number_table,
2236 position_id dbms_sql.varchar2_table,
2237 org_structure_version_id dbms_sql.number_table,
2238 user_group_id dbms_sql.number_table,
2239 requestor_id dbms_sql.number_table,
2240 certification_id dbms_sql.number_table
2241 );
2242
2243 l_rec auto_cert_subscr_rec;
2244 l_date ota_events.course_end_date%type;
2245 l_conc_request_id OTA_MAND_MULTI_ENR_REQUESTS.conc_program_request_id%type;
2246 l_cert_name ota_certifications_tl.name%type;
2247 l_chunk_size number := nvl(p_chunk_size,1000);
2248 l_threads number := nvl(p_thread_count,3);
2249 l_request_id number;
2250 l_person_name per_all_people_f.full_name%type;
2251 l_proc varchar2(72) := g_package||'process_automatic_cert_subscr';
2252
2253
2254 BEGIN
2255
2256 l_conc_request_id := FND_GLOBAL.conc_request_id;
2257 hr_utility.set_location(' Entering:'||l_proc, 5);
2258 if p_chunk_size is NULL then
2259 l_chunk_size := 1000;
2260 end if;
2261 if p_thread_count is NULL then
2262 l_threads := 3;
2263 end if;
2264
2265 if p_action_id is null then
2266
2267 if p_cert_id is NULL then
2268 FND_FILE.PUT_LINE(FND_FILE.LOG,'Automatic subscriptions are being processed for all certifications');
2269 ELSE
2270 OPEN get_certification_name(p_cert_id);
2271 FETCH get_certification_name into l_cert_name;
2272 CLOSE get_certification_name;
2273 FND_FILE.PUT_LINE(FND_FILE.LOG,'Automatic subscriptions is being processed for Certification -' || l_cert_name);
2274 END IF;
2275
2276 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent Request Id : ' || FND_GLOBAL.conc_request_id);
2277 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Start time is - '||TO_CHAR(SYSTIMESTAMP));
2278 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
2279
2280 SELECT
2281 'N' prereq_type,
2282 NULL event_id,
2283 oea.PERSON_ID person_id,
2284 oea.ORGANIZATION_ID organization_id,
2285 oea.JOB_ID job_id,
2286 oea.POSITION_ID position_id,
2287 oea.ORG_STRUCTURE_VERSION_ID org_structure_version_id,
2288 oea.USER_GROUP_ID user_group_id,
2289 NULL requestor_id,
2290 oea.CERTIFICATION_ID cert_id
2291 BULK COLLECT INTO
2292 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,
2293 l_rec.org_structure_version_id,l_rec.user_group_id,l_rec.requestor_id,l_rec.certification_id
2294 FROM
2295 ota_event_associations oea ,
2296 ota_certifications_b cert
2297 WHERE oea.certification_id = cert.certification_id
2298 AND oea.MANDATORY_ENROLLMENT_FLAG = 'Y'
2299 AND trunc(sysdate) BETWEEN trunc(cert.start_date_active) and nvl(cert.end_date_active, trunc(sysdate))
2300 AND nvl(p_cert_id,-1)= decode(p_cert_id,NULL,-1,cert.certification_id)
2301 AND ((cert.renewable_flag = 'N' and trunc(sysdate) <= nvl( cert.INITIAL_COMPLETION_DATE ,trunc(sysdate)))
2302 or cert.renewable_flag = 'Y')
2303 AND cert.business_group_id = OTA_GENERAL.get_business_group_id
2304 AND exists (select 1 from ota_certification_members crm where cert.certification_id = crm.certification_id)
2305 ORDER BY cert.certification_id;
2306
2307
2308
2309 FOR i IN 1 .. l_rec.certification_id.COUNT LOOP
2310 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting into ota_mandatory_enr_requests');
2311
2312 INSERT INTO OTA_MAND_MULTI_ENR_REQUESTS(
2313 MANDATORY_ENR_REQUEST_ID ,
2314 REQUESTOR_ID,
2315 EVENT_ID,
2316 ENR_PREREQ_TYPE,
2317 PERSON_ID,
2318 ORGANIZATION_ID,
2319 ORG_STRUCTURE_VERSION_ID,
2320 JOB_ID,
2321 POSITION_ID,
2322 USERGROUP_ID,
2323 CONC_PROGRAM_REQUEST_ID,
2324 CERTIFICATION_ID)
2325 VALUES(OTA_MAND_MULTI_ENR_REQUESTS_S.NEXTVAL,
2326 l_rec.requestor_id(i),
2327 NULL,
2328 'N',
2329 l_rec.person_id(i),
2330 l_rec.organization_id(i),
2331 l_rec.org_structure_version_id(i),
2332 l_rec.job_id(i),
2333 l_rec.position_id(i),
2334 l_rec.user_group_id(i),
2335 l_conc_request_id,
2336 l_rec.certification_id(i));
2337
2338 END LOOP;
2339
2340 COMMIT;
2341
2342 process_auto_cert_subscr_req(l_conc_request_id,p_cert_id,l_chunk_size,l_threads);
2343 else
2344 l_conc_request_id := p_action_id;
2345 create_cert_subscriptions(l_conc_request_id);
2346 end if;
2347
2348 ota_mandatory_enroll_util.check_all_slaves_finished
2349 (p_rpt_flag => TRUE);
2350
2351 if p_action_id is null then
2352
2353 --Write the error messages to log
2354 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
2355 FND_FILE.PUT_LINE(FND_FILE.LOG,'Unsuccessful Learners');
2356 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
2357 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name Class Reason');
2358 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
2359 FOR unsuccessful_learner IN unprocessed_subscriptions(l_conc_request_id) LOOP
2360 OPEN csr_get_person_name(unsuccessful_learner.person_id);
2361 FETCH csr_get_person_name INTO l_person_name;
2362 CLOSE csr_get_person_name;
2363 IF unsuccessful_learner.create_enrollment = 'N' THEN
2364 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||'Unable to create certification subscription');
2365 END IF;
2366
2367 IF unsuccessful_learner.error_message IS NOT NULL THEN
2368 /* FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
2369 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error message - '||unsuccessful_learner.error_message);
2370 FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');*/
2371 FND_FILE.PUT_LINE(FND_FILE.LOG, l_person_name||' | '||unsuccessful_learner.title||' | '||unsuccessful_learner.error_message);
2372 END IF;
2373
2374
2375
2376 END LOOP;
2377
2378 l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'OTA'
2379 ,program => 'OTA_PURGE_MAND_ENROLL_REC');
2380 end if;
2381
2382 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'End time is - '||TO_CHAR(SYSTIMESTAMP));
2383 hr_utility.set_location(' Leaving:'||l_proc, 10);
2384
2385 END process_automatic_cert_subscr;
2386
2387
2388
2389 PROCEDURE process_auto_cert_subscr_req(p_conc_request_id IN NUMBER,
2390 p_cert_id IN NUMBER,
2391 p_chunk_size in number,
2392 p_thread_count in number) IS
2393
2394
2395 TYPE learners_in_usergroup IS REF CURSOR;
2396 csr_get_lrnr_in_ug learners_in_usergroup;
2397
2398 TYPE learner_rec IS RECORD(
2399 person_id per_all_people_f.person_id%type,
2400 job_id per_jobs_tl.job_id%type,
2401 position_id per_all_positions.position_id%type,
2402 organization_id per_all_assignments_f.organization_id%type,
2403 assignment_id per_all_assignments_f.assignment_id%type
2404 );
2405 lrnr_rec learner_rec;
2406
2407 CURSOR csr_get_learner(
2408 p_person_id ota_event_associations.person_id%type,
2409 p_per_bg_grp_prof_val number,
2410 p_ota_global_bg_prof_val number
2411 ) IS
2412 SELECT ppf.person_id
2413 , paf.organization_id
2414 , paf.assignment_id
2415 FROM per_all_people_f ppf
2416 ,per_all_assignments_f paf
2417 ,per_person_type_usages_f ptu
2418 ,per_person_types pts
2419 ,per_business_groups pbg
2420 WHERE
2421 paf.person_id = p_person_id
2422 AND ppf.person_id = paf.person_id
2423 AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag= 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
2424 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2425 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
2426 AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
2427 AND pts.person_type_id = ptu.person_type_id
2428 AND ptu.person_id = ppf.person_id
2429 AND paf.assignment_type IN ('A','E','C')
2430 AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
2431 AND paf.business_group_id = pbg.business_group_id
2432 AND
2433 ((pts.system_person_type = 'APL'
2434 AND NOT EXISTS (SELECT person_id
2435 FROM per_person_type_usages_f ptf,
2436 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
2437 AND ptf.person_type_id = ptp.person_type_id
2438 AND ptp.system_person_type IN ('EMP', 'CWK')
2439 AND ptf.person_id = ppf.PERSON_ID)
2440 )
2441 OR pts.system_person_type IN ('EMP', 'CWK'));
2442
2443
2444
2445 CURSOR csr_get_learners(
2446 p_organization_id ota_event_associations.organization_id%type,
2447 p_job_id ota_event_associations.job_id%type,
2448 p_position_id ota_event_associations.position_id%type,
2449 p_org_structure_version_id ota_event_associations. org_structure_version_id%type,
2450 p_per_bg_grp_prof_val number,
2451 p_ota_global_bg_prof_val number
2452 ) IS
2453 SELECT ppf.person_id
2454 , pjt.job_id Job_Id
2455 , pps.position_id
2456 , paf.organization_id
2457 , paf.assignment_id
2458 FROM per_all_people_f ppf
2459 ,per_all_assignments_f paf
2460 ,per_jobs_tl pjt
2461 ,per_all_positions pps
2462 ,per_person_type_usages_f ptu
2463 ,per_person_types pts
2464 ,hr_all_organization_units_tl orgTl
2465 ,per_business_groups pbg
2466 WHERE ppf.person_id = paf.person_id
2467 AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag = 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
2468 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2469 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
2470 AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
2471 AND paf.job_id = pjt.job_id(+)
2472 AND pjt.language(+) = USERENV('LANG')
2473 AND pps.position_id(+) = paf.position_id
2474 AND pts.person_type_id = ptu.person_type_id
2475 AND ptu.person_id = ppf.person_id
2476 AND paf.organization_id = orgtl.organization_id
2477 AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
2478 AND paf.assignment_type IN ('A','E','C')
2479 AND orgtl.language = USERENV('LANG')
2480 AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
2481 AND paf.business_group_id = pbg.business_group_id
2482 AND
2483 ((pts.system_person_type = 'APL'
2484 AND NOT EXISTS (SELECT person_id
2485 FROM per_person_type_usages_f ptf,
2486 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
2487 AND ptf.person_type_id = ptp.person_type_id
2488 AND ptp.system_person_type IN ('EMP', 'CWK')
2489 AND ptf.person_id = ppf.PERSON_ID)
2490 )
2491 OR pts.system_person_type IN ('EMP', 'CWK'))
2492 AND
2493 (
2494 ( nvl(p_organization_id, -1) = decode(p_organization_id, NULL, -1, nvl(paf.organization_id,-1))) OR
2495
2496 ( 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')
2497 )
2498 AND nvl(p_job_id, -1) = decode(p_job_id, NULL, -1, nvl(paf.job_id, -1))
2499 AND nvl(p_position_id,-1) = decode(p_position_id, NULL, -1, nvl(paf.position_id, -1));
2500
2501
2502
2503 l_numberof_records_processed NUMBER:= 0;
2504 l_person_name per_all_people_f.full_name%type;
2505
2506
2507 l_per_bg_grp_prof_val number;
2508 l_ota_global_bg_prof_val number;
2509 l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%type:=0;
2510 l_cert_status_code NUMBER :=0;
2511
2512 sql_stmnt clob;
2513 usergroup_whereclause clob;
2514 v_split_sql_stmt DBMS_SQL.VARCHAR2S;
2515 v_upperbound NUMBER;
2516 v_cur INTEGER;
2517 v_person_id number;
2518 v_job_id number;
2519 v_pos_id number;
2520 v_org_id number;
2521 v_assign_id number;
2522 ignore INTEGER;
2523 l_proc varchar2(72) := g_package||'process_auto_cert_subscr_req';
2524
2525 l_chunk_size number := p_chunk_size;
2526 l_thread_count number := p_thread_count;
2527 l_threads number := l_thread_count;
2528 l_start_person_action_id number(15);
2529 l_end_person_action_id number(15);
2530 l_person_action_id number(15);
2531 l_num_range number := 0;
2532 l_range_id number(15);
2533 l_object_version_number Number(15);
2534 l_request_id number;
2535 OTA_NO_PERSON_TO_PROCESS exception;
2536 BEGIN
2537
2538 hr_utility.set_location(' Entering:'||l_proc, 5);
2539
2540
2541 l_per_bg_grp_prof_val := fnd_profile.value('PER_BUSINESS_GROUP_ID');
2542 l_ota_global_bg_prof_val := fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID');
2543
2544 FND_FILE.PUT_LINE(FND_FILE.LOG,'Value of profile PER_BUSINESS_GROUP_ID : '||l_per_bg_grp_prof_val);
2545 FND_FILE.PUT_LINE(FND_FILE.LOG,'Value of profile OTA_HR_GLOBAL_BUSINESS_GROUP_ID : '||l_ota_global_bg_prof_val);
2546
2547 FND_FILE.PUT_LINE(FND_FILE.LOG,'');
2548 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successful Learners');
2549 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
2550 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name Certification ');
2551 FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------------------------------------------------');
2552
2553 FOR request IN get_all_mand_multi_enr_reqs(p_conc_request_id) LOOP
2554 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Looping through OTA_MAND_MULTI_ENR_REQUESTS');
2555 l_cert_status_code :=0;
2556 l_cert_enrollment_id :=0;
2557
2558 IF request.usergroup_id IS NULL THEN
2559 If request.person_id IS NOT NULL THEN
2560 FOR learner IN csr_get_learner (request.person_id,l_per_bg_grp_prof_val,l_ota_global_bg_prof_val) LOOP
2561 learner_can_enroll_in_cert(request.certification_id,learner.person_id,l_cert_status_code, l_cert_enrollment_id);
2562 IF l_cert_status_code = l_subc_doesnot_exist THEN
2563 create_cert_req_member_record(l_person_id => learner.person_id,
2564 req_mandatory_enr_request_id => request.mandatory_enr_request_id,
2565 req_cert_id => request.certification_id,
2566 l_numberof_records_processed => l_numberof_records_processed,
2567 p_person_action_id => l_person_action_id);
2568 ELSIF l_cert_status_code = l_cancelled_subc_exist THEN
2569 create_cert_req_member_record(learner.person_id,request.mandatory_enr_request_id,request.certification_id,l_numberof_records_processed, 'U',l_person_action_id);
2570 ELSIF l_cert_status_code = l_upd_subc_exist THEN
2571 -- update the subscription, set is_automatic_subscription to Y
2572 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Update existing subscription');
2573 update ota_cert_enrollments set is_automatic_subscription = 'Y'
2574 where cert_enrollment_id = l_cert_enrollment_id;
2575
2576 OPEN csr_get_person_name(learner.person_id);
2577 FETCH csr_get_person_name INTO l_person_name;
2578 CLOSE csr_get_person_name;
2579 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
2580 --l_numberof_records_processed := l_numberof_records_processed + 1;
2581 END IF;
2582
2583 if l_person_action_id is not null then
2584
2585 l_end_person_action_id := l_person_action_id;
2586 --
2587 If l_numberof_records_processed = 1 then
2588 --
2589 l_start_person_action_id := l_person_action_id;
2590 --
2591 End if;
2592
2593 End if;
2594
2595 IF l_numberof_records_processed = l_chunk_size THEN
2596 --COMMIT;
2597 ota_batch_action_api.create_batch_ranges
2598 (p_validate => false
2599 ,p_range_id => l_range_id
2600 ,p_batch_source_cd => 'AS'
2601 ,p_batch_action_id => p_conc_request_id
2602 ,p_range_status_cd => 'U'
2603 ,p_starting_person_action_id => l_start_person_action_id
2604 ,p_ending_person_action_id => l_end_person_action_id
2605 ,p_object_version_number => l_object_version_number);
2606
2607 l_start_person_action_id := 0;
2608 l_end_person_action_id := 0;
2609 l_numberof_records_processed :=0;
2610 l_num_range := l_num_range + 1;
2611 END IF;
2612
2613 END LOOP;--END FOR learner IN csr_get_learner
2614 ELSE
2615 FOR asg_learner IN csr_get_learners (request.organization_id, request.job_id, request.position_id, request.org_structure_version_id,
2616 l_per_bg_grp_prof_val,l_ota_global_bg_prof_val) LOOP
2617 learner_can_enroll_in_cert(request.certification_id,asg_learner.person_id,l_cert_status_code, l_cert_enrollment_id);
2618 IF l_cert_status_code = l_subc_doesnot_exist THEN
2619 create_cert_req_member_record(l_person_id => asg_learner.person_id,
2620 req_mandatory_enr_request_id => request.mandatory_enr_request_id,
2621 req_cert_id => request.certification_id,
2622 l_numberof_records_processed => l_numberof_records_processed,
2623 p_person_action_id => l_person_action_id);
2624 ELSIF l_cert_status_code = l_cancelled_subc_exist THEN
2625 create_cert_req_member_record(asg_learner.person_id,request.mandatory_enr_request_id,request.certification_id,l_numberof_records_processed, 'U',l_person_action_id);
2626 ELSIF l_cert_status_code = l_upd_subc_exist THEN
2627 -- update the subscription, set is_automatic_subscription to Y
2628 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Update existing subscription');
2629 update ota_cert_enrollments set is_automatic_subscription = 'Y'
2630 where cert_enrollment_id = l_cert_enrollment_id;
2631 OPEN csr_get_person_name(asg_learner.person_id);
2632 FETCH csr_get_person_name INTO l_person_name;
2633 CLOSE csr_get_person_name;
2634 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
2635 --l_numberof_records_processed := l_numberof_records_processed + 1;
2636 END IF;
2637
2638 if l_person_action_id is not null then
2639
2640 l_end_person_action_id := l_person_action_id;
2641 --
2642 If l_numberof_records_processed = 1 then
2643 --
2644 l_start_person_action_id := l_person_action_id;
2645 --
2646 End if;
2647
2648 End if;
2649
2650 IF l_numberof_records_processed = l_chunk_size THEN
2651 --COMMIT;
2652 ota_batch_action_api.create_batch_ranges
2653 (p_validate => false
2654 ,p_range_id => l_range_id
2655 ,p_batch_source_cd => 'AS'
2656 ,p_batch_action_id => p_conc_request_id
2657 ,p_range_status_cd => 'U'
2658 ,p_starting_person_action_id => l_start_person_action_id
2659 ,p_ending_person_action_id => l_end_person_action_id
2660 ,p_object_version_number => l_object_version_number);
2661
2662 l_start_person_action_id := 0;
2663 l_end_person_action_id := 0;
2664 l_numberof_records_processed :=0;
2665 l_num_range := l_num_range + 1;
2666 END IF;
2667
2668 END LOOP;--END FOR learner IN csr_get_learners
2669 END IF;
2670 ELSE
2671 --resolve the members FOR the user group AND them to ota_mandatory_enr_request_members
2672 --usergroup_whereclause :=TO_CHAR(ota_learner_access_util.get_ug_whereclause(request.usergroup_id, -1));
2673 usergroup_whereclause :=ota_learner_access_util.get_ug_whereclause(request.usergroup_id, -1);
2674 --FND_FILE.PUT_LINE(FND_FILE.LOG,'usergroup_whereclause : '||usergroup_whereclause);
2675 sql_stmnt :='SELECT * FROM(
2676 SELECT
2677 ppf.person_id person_id
2678 , pjt.job_id job_id
2679 , pps.position_id position_id
2680 , paf.organization_id organization_id
2681 , paf.assignment_id assignment_id
2682 FROM per_all_people_f ppf
2683 ,per_all_assignments_f paf
2684 ,per_jobs_tl pjt
2685 ,per_all_positions pps
2686 ,per_person_type_usages_f ptu
2687 ,per_person_types pts
2688 ,per_person_types_tl ptt
2689 ,hr_all_organization_units_tl orgTl
2690 ,per_business_groups pbg
2691 WHERE ppf.person_id = paf.person_id
2692 AND ((pts.system_person_type IN (''EMP'',''CWK'') AND paf.primary_flag = ''Y'') OR (paf.assignment_type = ''A'' AND pts.system_person_type =''APL''))
2693 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
2694 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
2695 AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date
2696 AND paf.job_id = pjt.job_id(+)
2697 AND pjt.language(+) = USERENV(''LANG'')
2698 AND pps.position_id(+) = paf.position_id
2699 AND pts.person_type_id = ptt.person_type_id
2700 AND ptt.language = USERENV(''LANG'')
2701 AND pts.person_type_id = ptu.person_type_id
2702 AND ptu.person_id = ppf.person_id
2703 AND paf.organization_id = orgtl.organization_id
2704 AND pts.system_person_type IN (''EMP'', ''CWK'', ''APL'')
2705 AND paf.assignment_type IN (''A'',''E'',''C'')
2706 AND orgtl.language = USERENV(''LANG'')
2707 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''))
2708 AND paf.business_group_id = pbg.business_group_id
2709 AND
2710 ((pts.system_person_type = ''APL''
2711 AND NOT EXISTS (SELECT person_id
2712 FROM per_person_type_usages_f ptf,
2713 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
2714 AND ptf.person_type_id = ptp.person_type_id
2715 AND ptp.system_person_type IN (''EMP'', ''CWK'')
2716 AND ptf.person_id = ppf.PERSON_ID)
2717 )
2718 OR pts.system_person_type IN (''EMP'', ''CWK''))
2719 )QRSLT WHERE'|| usergroup_whereclause;
2720
2721 v_upperbound := CEIL(DBMS_LOB.GETLENGTH(sql_stmnt)/256);
2722 FOR i IN 1..v_upperbound
2723 LOOP
2724 v_split_sql_stmt(i) := DBMS_LOB.SUBSTR(sql_stmnt
2725 ,256 -- amount
2726 ,((i-1)*256)+1 -- offset
2727 );
2728 END LOOP;
2729
2730 v_cur := DBMS_SQL.OPEN_CURSOR;
2731 DBMS_SQL.PARSE(v_cur, v_split_sql_stmt, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
2732 DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_person_id);
2733 DBMS_SQL.DEFINE_COLUMN(v_cur, 2, v_job_id);
2734 DBMS_SQL.DEFINE_COLUMN(v_cur, 3, v_pos_id);
2735 DBMS_SQL.DEFINE_COLUMN(v_cur, 4, v_org_id);
2736 DBMS_SQL.DEFINE_COLUMN(v_cur, 5, v_assign_id);
2737
2738 ignore := DBMS_SQL.EXECUTE(v_cur);
2739
2740 LOOP
2741 IF DBMS_SQL.FETCH_ROWS(v_cur)>0 THEN
2742 -- get column values of the row
2743 DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_person_id);
2744 DBMS_SQL.COLUMN_VALUE(v_cur, 2, v_job_id);
2745 DBMS_SQL.COLUMN_VALUE(v_cur, 3, v_pos_id);
2746 DBMS_SQL.COLUMN_VALUE(v_cur, 4, v_org_id);
2747 DBMS_SQL.COLUMN_VALUE(v_cur, 5, v_assign_id);
2748
2749 learner_can_enroll_in_cert(request.certification_id,v_person_id,l_cert_status_code, l_cert_enrollment_id);
2750
2751 IF l_cert_status_code = l_subc_doesnot_exist THEN
2752 create_cert_req_member_record(l_person_id => v_person_id,
2753 req_mandatory_enr_request_id => request.mandatory_enr_request_id,
2754 req_cert_id => request.certification_id,
2755 l_numberof_records_processed => l_numberof_records_processed,
2756 p_person_action_id => l_person_action_id);
2757 ELSIF l_cert_status_code = l_cancelled_subc_exist THEN
2758 create_cert_req_member_record(v_person_id,request.mandatory_enr_request_id,request.certification_id,l_numberof_records_processed, 'U',l_person_action_id);
2759 ELSIF l_cert_status_code = l_upd_subc_exist THEN
2760 -- update the subscription, set is_automatic_subscription to Y
2761 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Update existing subscription');
2762 update ota_cert_enrollments set is_automatic_subscription = 'Y'
2763 where cert_enrollment_id = l_cert_enrollment_id;
2764
2765 OPEN csr_get_person_name(v_person_id);
2766 FETCH csr_get_person_name INTO l_person_name;
2767 CLOSE csr_get_person_name;
2768 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
2769 --l_numberof_records_processed := l_numberof_records_processed + 1;
2770 END IF;
2771
2772 if l_person_action_id is not null then
2773
2774 l_end_person_action_id := l_person_action_id;
2775 --
2776 If l_numberof_records_processed = 1 then
2777 --
2778 l_start_person_action_id := l_person_action_id;
2779 --
2780 End if;
2781
2782 End if;
2783
2784 IF l_numberof_records_processed = l_chunk_size THEN
2785 --COMMIT;
2786 ota_batch_action_api.create_batch_ranges
2787 (p_validate => false
2788 ,p_range_id => l_range_id
2789 ,p_batch_source_cd => 'AS'
2790 ,p_batch_action_id => p_conc_request_id
2791 ,p_range_status_cd => 'U'
2792 ,p_starting_person_action_id => l_start_person_action_id
2793 ,p_ending_person_action_id => l_end_person_action_id
2794 ,p_object_version_number => l_object_version_number);
2795
2796 l_start_person_action_id := 0;
2797 l_end_person_action_id := 0;
2798 l_numberof_records_processed :=0;
2799 l_num_range := l_num_range + 1;
2800 END IF;
2801
2802 ELSE
2803
2804 -- No more rows:
2805 EXIT;
2806 END IF;
2807 END LOOP;
2808
2809 DBMS_SQL.CLOSE_CURSOR(v_cur);
2810
2811 /*OPEN csr_get_lrnr_in_ug FOR sql_stmnt;
2812 LOOP
2813 FETCH csr_get_lrnr_in_ug into lrnr_rec;
2814 EXIT WHEN csr_get_lrnr_in_ug%NOTFOUND;
2815 learner_can_enroll_in_cert(request.certification_id,lrnr_rec.person_id,l_cert_status_code, l_cert_enrollment_id);
2816 IF l_cert_status_code = l_subc_doesnot_exist THEN
2817 create_cert_req_member_record(l_person_id => lrnr_rec.person_id,
2818 req_mandatory_enr_request_id => request.mandatory_enr_request_id,
2819 req_cert_id => request.certification_id,
2820 l_numberof_records_processed => l_numberof_records_processed,
2821 p_person_action_id => l_person_action_id);
2822 ELSIF l_cert_status_code = l_cancelled_subc_exist THEN
2823 create_cert_req_member_record(lrnr_rec.person_id,request.mandatory_enr_request_id,request.certification_id,l_numberof_records_processed, 'U',l_person_action_id);
2824 ELSIF l_cert_status_code = l_upd_subc_exist THEN
2825 -- update the subscription, set is_automatic_subscription to Y
2826 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Update existing subscription');
2827 update ota_cert_enrollments set is_automatic_subscription = 'Y'
2828 where cert_enrollment_id = l_cert_enrollment_id;
2829
2830 OPEN csr_get_person_name(lrnr_rec.person_id);
2831 FETCH csr_get_person_name INTO l_person_name;
2832 CLOSE csr_get_person_name;
2833 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
2834 --l_numberof_records_processed := l_numberof_records_processed + 1;
2835 END IF;
2836
2837 if l_person_action_id is not null then
2838
2839 l_end_person_action_id := l_person_action_id;
2840 --
2841 If l_numberof_records_processed = 1 then
2842 --
2843 l_start_person_action_id := l_person_action_id;
2844 --
2845 End if;
2846
2847 End if;
2848
2849 IF l_numberof_records_processed = l_chunk_size THEN
2850 --COMMIT;
2851 ota_batch_action_api.create_batch_ranges
2852 (p_validate => false
2853 ,p_range_id => l_range_id
2854 ,p_batch_source_cd => 'AS'
2855 ,p_batch_action_id => p_conc_request_id
2856 ,p_range_status_cd => 'U'
2857 ,p_starting_person_action_id => l_start_person_action_id
2858 ,p_ending_person_action_id => l_end_person_action_id
2859 ,p_object_version_number => l_object_version_number);
2860
2861 l_start_person_action_id := 0;
2862 l_end_person_action_id := 0;
2863 l_numberof_records_processed :=0;
2864 l_num_range := l_num_range + 1;
2865 END IF;
2866
2867 END LOOP;
2868 CLOSE csr_get_lrnr_in_ug;*/
2869
2870 END IF;--END IF request.usergroup_id IS NULL
2871
2872 END LOOP;
2873
2874 If l_numberof_records_processed <> 0 then
2875 --
2876 --
2877 ota_batch_action_api.create_batch_ranges
2878 (p_validate => false
2879 ,p_range_id => l_range_id
2880 ,p_batch_source_cd => 'AS'
2881 ,p_batch_action_id => p_conc_request_id
2882 ,p_range_status_cd => 'U'
2883 ,p_starting_person_action_id => l_start_person_action_id
2884 ,p_ending_person_action_id => l_end_person_action_id
2885 ,p_object_version_number => l_object_version_number);
2886
2887 l_num_range := l_num_range + 1;
2888 --
2889 End if;
2890
2891 COMMIT;
2892
2893 if l_num_range > 1 --2
2894 then
2895
2896 l_threads := least(l_threads, l_num_range);
2897 --
2898 for l_count in 1..(l_threads - 1)
2899 loop
2900 --
2901 --l_request_id := fnd_request.submit_request();
2902
2903 l_request_id := FND_REQUEST.SUBMIT_REQUEST(application => 'OTA'
2904 ,program => 'OTAUTOCERTSUBSC'
2905 ,argument1 => p_conc_request_id
2906 ,argument2 => l_chunk_size
2907 ,argument3 => l_thread_count
2908 ,argument4 => p_cert_id);
2909
2910
2911 --
2912 -- Store the request id of the concurrent request
2913 --
2914 ota_mandatory_enroll_util.g_num_processes := ota_mandatory_enroll_util.g_num_processes + 1;
2915 ota_mandatory_enroll_util.g_processes_tbl(ota_mandatory_enroll_util.g_num_processes) := l_request_id;
2916
2917 --
2918 end loop;
2919 --
2920 commit;
2921 --
2922 elsif l_num_range = 0
2923 then
2924 -- Means no person is selected or no ug to process
2925 --
2926 --
2927 FND_FILE.PUT_LINE(FND_FILE.LOG,l_proc || ' No person or no learner group to process');
2928 raise OTA_NO_PERSON_TO_PROCESS;
2929
2930 --
2931 end if; --2
2932 --All the records to be processed are now present IN OTA_MAND_MULTI_ENR_REQ_MEMBERS with create_enrollment = 'Y'.
2933 --The records which cannot be created due to unfulfilled course/competence prereq have the respective flags set to 'N'
2934 --and create_enrollment IS set to 'N'
2935
2936 create_cert_subscriptions(p_conc_request_id);
2937
2938 hr_utility.set_location(' Leaving:'||l_proc, 10);
2939
2940 Exception
2941 when OTA_NO_PERSON_TO_PROCESS then
2942 FND_FILE.PUT_LINE(FND_FILE.LOG,'No Person record to process');
2943 END process_auto_cert_subscr_req;
2944
2945
2946 PROCEDURE learner_can_enroll_in_cert(p_certification_id IN NUMBER
2947 ,p_learner_id IN NUMBER
2948 ,l_return_status OUT NOCOPY NUMBER
2949 ,l_cert_enr_id OUT NOCOPY NUMBER) IS
2950
2951
2952 CURSOR check_subscr_exists IS
2953 SELECT cert_enrollment_id,
2954 certification_status_code,
2955 nvl(is_automatic_subscription, 'N')
2956 FROM ota_cert_enrollments
2957 WHERE person_id = p_learner_id
2958 AND certification_id = p_certification_id;
2959 -- AND certification_status_code <> 'CANCELLED';
2960
2961
2962
2963 l_status_code varchar2(30);
2964 l_status_id NUMBER := NULL;
2965 l_status_number NUMBER;
2966 l_mandatory_enrollment_flag varchar2(1) := NULL;
2967 l_proc varchar2(72) := g_package||'learner_can_enroll_in_cert';
2968 l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%type;
2969 l_certification_status_code ota_cert_enrollments.certification_status_code%type;
2970 l_is_automatic_subscription ota_cert_enrollments.is_automatic_subscription%type;
2971 BEGIN
2972
2973 hr_utility.set_location(' Entering:'||l_proc, 5);
2974 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered learner can enrol in cert ');
2975 l_cert_enr_id := 0;
2976
2977 OPEN check_subscr_exists;
2978 FETCH check_subscr_exists INTO l_cert_enrollment_id, l_certification_status_code, l_is_automatic_subscription;
2979 IF check_subscr_exists%NOTFOUND THEN---if subscription does not exist
2980 CLOSE check_subscr_exists;
2981 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Subscription does not exist Returning Y ');
2982 l_return_status:= l_subc_doesnot_exist;
2983 ELSE
2984 CLOSE check_subscr_exists;
2985 --FND_FILE.PUT_LINE(FND_FILE.LOG,'l_certification_status_code ' || l_certification_status_code);
2986 --FND_FILE.PUT_LINE(FND_FILE.LOG,'l_is_automatic_subscription' ||l_is_automatic_subscription);
2987 IF ((l_certification_status_code = 'AWAITING_APPROVAL') OR
2988 (l_certification_status_code = 'CANCELLED' AND l_is_automatic_subscription = 'Y')) THEN
2989 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Subscription exists with status AWAITING_APPROVAL or Automatic subscription exists with Cancelled status. Returning N ');
2990 l_return_status:= l_non_upd_subc_exist;
2991 ELSE
2992 -- Both cert subscription in cancel and rejected status without the automatic certification flag set will be
2993 --re-subscribed, in case of automatic certification subscription
2994 IF (l_certification_status_code = 'CANCELLED' OR l_certification_status_code = 'REJECTED') AND l_is_automatic_subscription = 'N' THEN
2995 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Non auto subscription exists with status CANCELLED. Resubscribe ');
2996 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Update cancelled existing subscription');
2997 l_return_status := l_cancelled_subc_exist;
2998 l_cert_enr_id := l_cert_enrollment_id;
2999 ELSE
3000 IF l_is_automatic_subscription <> 'Y' THEN
3001 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Subscription exists. Update. Returning Y ');
3002 l_return_status := l_upd_subc_exist;
3003 l_cert_enr_id:= l_cert_enrollment_id;
3004 END IF;
3005 END IF;
3006
3007 END IF;
3008 END IF;
3009
3010
3011
3012 END learner_can_enroll_in_cert;
3013
3014
3015 PROCEDURE create_cert_req_member_record(l_person_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.person_id%type,
3016 req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,
3017 req_cert_id IN ota_cert_enrollments.certification_id%type,
3018 l_numberof_records_processed IN OUT NOCOPY NUMBER,
3019 l_create_enrollment IN varchar2 default 'Y',
3020 p_person_action_id OUT NOCOPY OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type) IS
3021
3022 CURSOR csr_get_assignment_info(l_person_id NUMBER) IS
3023 SELECT paf.organization_id,
3024 paf.business_group_id,
3025 paf.assignment_id
3026 FROM
3027 per_all_assignments_f paf,
3028 per_person_types ppt,
3029 per_all_people_f perp,
3030 per_person_type_usages_f ptu
3031 WHERE
3032 paf.person_id = l_person_id
3033 AND perp.person_id =paf.person_id
3034 AND perp.person_id = ptu.person_id
3035 AND ptu.person_type_id = ppt.person_type_id
3036 AND ((paf.primary_flag = 'Y' AND ppt.system_person_type IN ('EMP','CWK'))
3037 OR (paf.assignment_type = 'A' AND ppt.system_person_type ='APL'))
3038 AND paf.assignment_type IN ('A','E','C')
3039 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
3040 AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
3041 AND ((ppt.system_person_type = 'APL'
3042 AND NOT EXISTS (SELECT person_id
3043 FROM per_person_type_usages_f ptf,
3044 per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
3045 AND ptf.person_type_id = ptp.person_type_id
3046 AND ptp.system_person_type IN ('EMP', 'CWK')
3047 AND ptf.person_id = paf.PERSON_ID)
3048 )OR ppt.system_person_type IN ('EMP', 'CWK'));
3049
3050 l_assignment_info csr_get_assignment_info%rowtype;
3051 l_completed_crs_prereq varchar2(1):= null;
3052 l_completed_comp_prereq varchar2(1) :=null;
3053 l_proc varchar2(72) := g_package||'create_cert_req_member_record';
3054
3055 l_person_action_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type := NULL;
3056
3057 Cursor C_Sel1 is select OTA_PERSON_ACTIONS_S.nextval from sys.dual;
3058
3059 BEGIN
3060
3061 hr_utility.set_location(' Entering:'||l_proc, 5);
3062
3063 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside create_cert_req_member_record for person_id - ' || l_person_id);
3064 --As learners are selected based on primary or secondary assignment criteria,but enrollments must be created
3065 -- based on primary assignment we need to retreive the primary assignment before validations.
3066 OPEN csr_get_assignment_info(l_person_id);
3067 FETCH csr_get_assignment_info INTO l_assignment_info;
3068 CLOSE csr_get_assignment_info;
3069
3070 IF learner_is_notSelected_inCert(l_person_id,l_assignment_info.assignment_id,req_cert_id) THEN
3071 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting into req members');
3072 Open C_Sel1;
3073 Fetch C_Sel1 Into l_person_action_id;
3074 Close C_Sel1;
3075 INSERT INTO OTA_MAND_MULTI_ENR_REQ_MEMBERS(mandatory_enr_request_id,person_id,assignment_id,error_message,
3076 completed_course_prereq,completed_competence_prereq,create_enrollment,event_id,organization_id,mbr_bg_id,certification_id,
3077 person_action_id,action_status_cd)
3078 VALUES(req_mandatory_enr_request_id,l_person_id,l_assignment_info.assignment_id,NULL,l_completed_crs_prereq,l_completed_comp_prereq,
3079 l_create_enrollment,NULL,l_assignment_info.organization_id,ota_general.get_business_group_id,req_cert_id,
3080 l_person_action_id,'U');
3081 l_numberof_records_processed := l_numberof_records_processed + 1;
3082 END IF;--learner_is_notSelected_inCert
3083 p_person_action_id := l_person_action_id;
3084
3085 hr_utility.set_location(' Leaving:'||l_proc, 10);
3086
3087 end create_cert_req_member_record;
3088
3089
3090 PROCEDURE create_cert_subscriptions(p_conc_reqId IN NUMBER) IS
3091
3092 l_start_person_action_id number DEFAULT 0;
3093 l_end_person_action_id number DEFAULT 0;
3094 l_range_id OTA_BATCH_RANGES.range_id%TYPE;
3095
3096 CURSOR csr_get_request_members(l_conc_reqID NUMBER) IS
3097 SELECT
3098 reqmembers.MANDATORY_ENR_REQUEST_ID,
3099 reqmembers.PERSON_ID,
3100 reqmembers.ASSIGNMENT_ID,
3101 reqmembers.EVENT_ID,
3102 reqmembers.ERROR_MESSAGE,
3103 reqmembers.ORGANIZATION_ID,
3104 reqmembers.MBR_BG_ID,
3105 reqmembers.CERTIFICATION_ID,
3106 reqmembers.CREATE_ENROLLMENT
3107 FROM
3108 OTA_MAND_MULTI_ENR_REQ_MEMBERS reqmembers--,
3109 --OTA_MAND_MULTI_ENR_REQUESTS requests
3110 WHERE
3111 -- requests.conc_program_request_id = l_conc_reqId
3112 -- AND requests.mandatory_enr_request_id = reqmembers.mandatory_enr_request_id
3113 -- AND
3114 reqmembers.create_enrollment in ('Y', 'U')
3115 AND reqmembers.person_action_id BETWEEN l_start_person_action_id AND l_end_person_action_id
3116 ORDER BY certification_id;
3117
3118 CURSOR c_range_thread IS
3119 SELECT ran.range_id
3120 ,ran.starting_person_action_id
3121 ,ran.ending_person_action_id
3122 FROM OTA_BATCH_RANGES ran
3123 WHERE ran.range_status_cd = 'U'
3124 AND ran.batch_source_cd = 'AS'
3125 AND ran.BATCH_ACTION_ID = p_conc_reqId
3126 AND rownum < 2
3127 FOR UPDATE OF ran.range_status_cd;
3128
3129 l_error_message OTA_MAND_MULTI_ENR_REQ_MEMBERS.error_message%type;
3130 l_mandaotory_enr_request_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type;
3131 l_request_rec get_all_mandatory_enr_requests%rowtype;
3132 l_req_member_rec csr_get_request_members%rowtype;
3133 l_person_name per_all_people_f.full_name%type;
3134 l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%TYPE;
3135 l_certification_status_code ota_cert_enrollments.CERTIFICATION_STATUS_CODE%TYPE;
3136 l_certification_name ota_certifications_tl.name%type;
3137
3138 l_proc varchar2(72) := g_package||'create_cert_subscriptions';
3139
3140 BEGIN
3141
3142 hr_utility.set_location(' Entering:'||l_proc, 5);
3143 LOOP
3144 OPEN c_range_thread;
3145
3146 FETCH c_range_thread
3147 INTO l_range_id
3148 ,l_start_person_action_id
3149 ,l_end_person_action_id;
3150
3151 IF c_range_thread%NOTFOUND THEN
3152
3153 CLOSE c_range_thread;
3154
3155 EXIT;
3156 END IF;
3157
3158 CLOSE c_range_thread;
3159
3160 UPDATE OTA_BATCH_RANGES ran
3161 SET ran.range_status_cd = 'P'
3162 WHERE ran.range_id = l_range_id;
3163
3164 COMMIT;
3165
3166 FOR l_req_member_rec IN csr_get_request_members(p_conc_reqId) LOOP
3167
3168 l_cert_enrollment_id := null;
3169 l_certification_status_code := null;
3170 l_error_message := null;
3171
3172 OPEN csr_get_person_name(l_req_member_rec.person_id);
3173 FETCH csr_get_person_name INTO l_person_name;
3174 CLOSE csr_get_person_name;
3175
3176
3177
3178 BEGIN
3179
3180 OTA_CERT_ENROLLMENT_API.subscribe_to_certification(
3181 p_certification_id => l_req_member_rec.certification_id
3182 ,p_person_id => l_req_member_rec.person_id
3183 ,p_business_group_id => l_req_member_rec.mbr_bg_id
3184 ,p_approval_flag => 'N'
3185 ,p_is_history_flag => 'N'
3186 ,p_cert_enrollment_id => l_cert_enrollment_id
3187 ,p_certification_status_code => l_certification_status_code
3188 ,p_is_automatic_subscription => 'Y');
3189
3190 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
3191
3192 EXCEPTION
3193 WHEN OTHERS THEN
3194 l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating certification subscription ');
3195 UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
3196 SET error_message = l_error_message
3197 WHERE person_id = l_req_member_rec.person_id
3198 AND certification_id = l_req_member_rec.certification_id
3199 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;
3200 END;
3201
3202 OPEN get_certification_name(l_req_member_rec.certification_id);
3203 FETCH get_certification_name into l_certification_name;
3204 CLOSE get_certification_name;
3205
3206 IF l_cert_enrollment_id IS NOT NULL THEN
3207 FND_FILE.PUT_LINE(FND_FILE.LOG,l_person_name||' | '||l_certification_name||' | '||l_cert_enrollment_id);
3208 IF l_req_member_rec.create_enrollment = 'U' THEN
3209 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'l_req_member_rec.create_enrollment = U');
3210 update ota_cert_enrollments set is_automatic_subscription = 'Y'
3211 where cert_enrollment_id = l_cert_enrollment_id;
3212 END IF;
3213
3214 UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
3215 SET action_status_cd = 'P'
3216 WHERE person_id = l_req_member_rec.person_id
3217 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id
3218 AND action_status_cd not in ('P','E');
3219
3220
3221 ELSE
3222 l_error_message := nvl(substr(l_error_message,1,2000),'Subscription IS NULL');
3223 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Error when creating enrollment into class- '||l_class_name);
3224 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
3225 --FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
3226
3227 UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
3228 SET error_message = l_error_message, action_status_cd = 'E'
3229 WHERE person_id = l_req_member_rec.person_id
3230 AND certification_id = l_req_member_rec.certification_id
3231 AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;
3232 END IF;
3233
3234 END LOOP;
3235
3236 commit;
3237 hr_utility.set_location(' Leaving:'||l_proc, 10);
3238 END LOOP;
3239 END create_cert_subscriptions;
3240
3241
3242 -- ============================================================================
3243 -- << Rpt_error >>
3244 -- ============================================================================
3245 --
3246 procedure rpt_error (p_proc in varchar2
3247 ,p_last_actn in varchar2
3248 ,p_rpt_flag in boolean default FALSE
3249 ) is
3250 L_proc varchar2(80) := g_package||'.rpt_error';
3251 Begin
3252 If (p_rpt_flag ) then
3253 fnd_file.put_line(fnd_file.log,'<<<Fail in '||p_proc||' while '|| p_last_actn||'>>>');
3254 End if;
3255 hr_utility.set_location('> Fail in ' || p_proc, 999 );
3256 hr_utility.set_location('>> While ' || p_last_actn, 999);
3257 End rpt_error;
3258
3259 --
3260 -- ============================================================================
3261 -- <<Check_all_slaves_finished>>
3262 -- ============================================================================
3263 --
3264 Procedure check_all_slaves_finished(p_rpt_flag Boolean default FALSE) is
3265 --
3266 l_proc varchar2(80) := g_package||'.check_all_slaves_finished';
3267 l_no_slaves boolean := true;
3268 l_dummy varchar2(1);
3269 l_actn varchar2(80);
3270 --
3271 Cursor c_slaves(p_request_id number) is
3272 Select null
3273 From fnd_concurrent_requests fnd
3274 Where fnd.phase_code <> 'C'
3275 And fnd.request_id = p_request_id;
3276 Begin
3277 hr_utility.set_location ('Entering '||l_proc,5);
3278 If g_num_processes <> 0 then
3279 l_actn := 'Checking Slaves.....';
3280 While l_no_slaves loop
3281 l_no_slaves := false;
3282 For l_count in 1..g_num_processes loop
3283 open c_slaves(g_processes_tbl(l_count));
3284 fetch c_slaves into l_dummy;
3285 If c_slaves%found then
3286 l_no_slaves := true;
3287 close c_slaves;
3288 exit;
3289 End if;
3290 Close c_slaves;
3291 End loop;
3292 If (l_no_slaves) then
3293 dbms_lock.sleep(5);
3294 End if;
3295 End loop;
3296 End if;
3297 hr_utility.set_location ('Leaving '||l_proc,5);
3298 Exception
3299 when others then
3300 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error:'||substr(SQLERRM,1,2000));
3301 rpt_error(p_proc =>l_proc,p_last_actn=>l_actn,p_rpt_flag=>p_rpt_flag);
3302 raise;
3303 End check_all_slaves_finished;
3304
3305 Procedure purge_mand_enroll_data(ERRBUF OUT NOCOPY VARCHAR2,
3306 RETCODE OUT NOCOPY VARCHAR2) is
3307
3308 TYPE mand_rec IS RECORD (conc_req_id OTA_MAND_MULTI_ENR_REQUESTS.CONC_PROGRAM_REQUEST_ID%TYPE
3309 ,mandatory_enr_req_id OTA_MAND_MULTI_ENR_REQUESTS.MANDATORY_ENR_REQUEST_ID%TYPE);
3310
3311 TYPE t_mand_rec IS TABLE OF mand_rec INDEX BY binary_integer;
3312 mand_enr_req_rec t_mand_rec;
3313 l_proc varchar2(80) := g_package||'.purge_mand_enroll_data';
3314 begin
3315 hr_utility.set_location ('Entering '||l_proc,5);
3316 dbms_lock.sleep(5);
3317 select omr.CONC_PROGRAM_REQUEST_ID, omr.MANDATORY_ENR_REQUEST_ID
3318 bulk collect into mand_enr_req_rec
3319 from OTA_MAND_MULTI_ENR_REQUESTS omr, fnd_concurrent_requests fcr
3320 where fcr.REQUEST_ID (+) = omr.CONC_PROGRAM_REQUEST_ID
3321 and nvl(fcr.STATUS_CODE,'C') in ('C','E');
3322
3323 if mand_enr_req_rec.count <= 0 then
3324 return;
3325 end if;
3326
3327 FOR l_conc_req IN mand_enr_req_rec.first .. mand_enr_req_rec.last LOOP
3328
3329 delete from OTA_MAND_MULTI_ENR_REQ_MEMBERS
3330 where MANDATORY_ENR_REQUEST_ID = mand_enr_req_rec(l_conc_req).mandatory_enr_req_id;
3331
3332 if sql%rowcount > 0 then
3333 commit;
3334 end if;
3335
3336 delete from ota_batch_ranges
3337 where BATCH_ACTION_ID = mand_enr_req_rec(l_conc_req).conc_req_id
3338 and BATCH_SOURCE_CD <> 'EP';
3339
3340 if sql%rowcount > 0 then
3341 commit;
3342 end if;
3343
3344 delete from ota_mand_multi_enr_requests
3345 where mandatory_enr_request_id = mand_enr_req_rec(l_conc_req).mandatory_enr_req_id;
3346
3347 end loop;
3348
3349 if sql%rowcount > 0 then
3350 commit;
3351 end if;
3352 hr_utility.set_location ('Leaving '||l_proc,5);
3353 end purge_mand_enroll_data;
3354
3355 END ota_mandatory_enroll_util;