[Home] [Help]
PACKAGE BODY: APPS.OTA_BULK_ENROLL_UTIL
Source
1 package body ota_bulk_enroll_util as
2 /* $Header: otblkenr.pkb 120.24.12020000.4 2012/06/29 12:02:09 rpahune ship $ */
3
4 g_package varchar2(33) := 'ota_bulk_enroll_util.'; -- Global package name
5
6 Function get_enrollment_status( p_object_type IN VARCHAR2
7 ,p_object_id IN NUMBER
8 ,p_learner_id IN NUMBER
9 ,p_return_mode IN NUMBER
10 ) RETURN VARCHAR2
11 IS
12 CURSOR get_lp_enr_status IS
13 SELECT lpe.path_status_code status_code,
14 lkp.meaning Status_meaning,
15 decode(lpe.path_status_code,'CANCELLED' , 0, 'ACTIVE',1, 'COMPLETED', 2) path_status_number
16 FROM ota_lp_enrollments lpe,
17 hr_lookups lkp
18 WHERE lpe.learning_path_id = p_object_id
19 AND lpe.person_id = p_learner_id
20 AND lkp.lookup_code = lpe.path_status_code
21 AND lkp.lookup_type = 'OTA_LEARNING_PATH_STATUS'
22 order by path_status_number desc;
23
24 CURSOR get_class_enr_status IS
25 SELECT bst.type status_code,
26 btt.name Status,
27 bst.booking_status_type_id status_id,
28 decode(bst.type, 'C', 0,'R',1, 'W',2, 'P',3, 'E',4, 'A',5) status_number
29 FROM ota_delegate_bookings tdb,
30 ota_booking_status_types bst,
31 ota_booking_status_types_tl btt
32 WHERE tdb.delegate_person_id = p_learner_id
33 AND tdb.event_id = p_object_id
34 AND tdb.booking_status_type_id = bst.booking_status_type_id
35 AND bst.booking_status_type_id = btt.booking_status_type_id
36 -- Added for bug#5572552
37 AND btt.LANGUAGE = USERENV('LANG')
38 order by status_number desc;
39
40 CURSOR get_cert_enr_status IS
41 SELECT cre.certification_status_code status_code,
42 lkp.meaning Status_meaning,
43 decode(cre.certification_status_code, 'REJECTED', 0, 'EXPIRED', 1, 'AWAITING_APPROVAL',2,
44 'CERTIFIED' , 3, 'CONCLUDED', 4,'ENROLLED',5) cert_status_number
45 FROM ota_cert_enrollments cre,
46 hr_lookups lkp
47 WHERE cre.certification_id = p_object_id
48 AND cre.person_id = p_learner_id
49 AND lkp.lookup_code =cre.certification_status_code
50 AND lkp.lookup_type = 'OTA_CERT_ENROLL_STATUS'
51 order by cert_status_number desc;
52
53 l_status varchar2(100) := null;
54 l_status_code varchar2(30);
55 l_status_id NUMBER := NULL;
56 l_status_number NUMBER;
57 BEGIN
58 IF p_object_type = 'LP' THEN
59 OPEN get_lp_enr_status;
60 FETCH get_lp_enr_status INTO l_status_code, l_status, l_status_number;
61 CLOSE get_lp_enr_status;
62 ELSIF p_object_type in ('CL','LPCL') THEN
63 OPEN get_class_enr_status;
64 FETCH get_class_enr_status INTO l_status_code, l_status, l_status_id, l_status_number;
65 CLOSE get_class_enr_status;
66 ELSIF p_object_type = 'CRT' THEN
67 OPEN get_cert_enr_status;
68 FETCH get_cert_enr_status INTO l_status_code, l_status, l_status_number;
69 CLOSE get_cert_enr_status;
70 END IF;
71 IF p_return_mode = 1 THEN
72 return l_status;
73 ELSIF p_return_mode = 2 THEN
74 return l_status_code;
75 ELSIF p_return_mode = 3 THEN
76 return l_status_id;
77 END IF;
78 end get_enrollment_status;
79
80 FUNCTION get_enr_status_from_request(
81 p_object_type IN VARCHAR2
82 ,p_enrollment_status IN VARCHAR2) RETURN VARCHAR2
83 IS
84 CURSOR get_class_enrollment_status IS
85 SELECT btt.name
86 FROM ota_booking_status_types_tl btt
87 WHERE btt.language = USERENV('LANG')
88 AND btt.booking_status_type_id = p_enrollment_status;
89
90 CURSOR get_subscription_status IS
91 SELECT lkp.meaning
92 FROM hr_lookups lkp
93 WHERE lkp.lookup_code = p_enrollment_status
94 AND lkp.lookup_type = decode(p_object_type,'LP','OTA_LEARNING_PATH_STATUS','CRT','OTA_CERT_ENROLL_STATUS');
95
96 l_status VARCHAR2(100) := null;
97 BEGIN
98 IF p_enrollment_status IS NULL THEN
99 RETURN NULL;
100 ELSIF p_object_type in ('CL','LPCL') THEN
101 OPEN get_class_enrollment_status;
102 FETCH get_class_enrollment_status INTO l_status;
103 CLOSE get_class_enrollment_status;
104 ELSE
105 OPEN get_subscription_status;
106 FETCH get_subscription_status INTO l_status;
107 CLOSE get_subscription_status;
108 END IF;
109
110 RETURN l_status;
111
112 END get_enr_status_from_request;
113
114
115 PROCEDURE assign_enrollment_status(p_class_id IN NUMBER
116 , p_booking_status_id OUT NOCOPY NUMBER
117 , p_status_message OUT NOCOPY VARCHAR2)
118 IS
119 CURSOR get_enrolled_places IS
120 SELECT sum(nvl(tdb.number_of_places,1)) enrolled_places
121 FROM ota_delegate_bookings tdb, ota_booking_status_types bst
122 WHERE tdb.event_id = p_class_id
123 and tdb.internal_booking_flag = 'Y'
124 and tdb.booking_status_type_id = bst.booking_status_type_id
125 and bst.type IN ('P', 'A','E');
126
127 l_enrolled_places number;
128 l_max_enroll number;
129 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
130 l_max_internal number;
131 l_event_status ota_events.event_status%TYPE;
132 l_maximum_internal_allowed number;
133
134 BEGIN
135 OPEN get_enrolled_places;
136 FETCH get_enrolled_places INTO l_enrolled_places;
137 CLOSE get_enrolled_places;
138
139 select MAXIMUM_INTERNAL_ATTENDEES, MAXIMUM_ATTENDEES, event_status
140 INTO l_max_internal, l_max_enroll, l_event_status
141 FROM ota_events
142 WHERE event_id = p_class_id;
143
144 l_maximum_internal_allowed := nvl(l_max_internal,l_max_enroll) - nvl(l_enrolled_places,0);
145
146 IF l_event_status = 'F' THEN
147 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_For_Web
148 (p_web_booking_status_type => 'WAITLISTED'
149 ,p_business_group_id => 81);
150
151 p_booking_status_id := l_booking_status_row.booking_status_type_id;
152 IF p_booking_status_id IS NOT NULL THEN
153 p_status_message := 'FULL_WAITILISTED';
154 ELSE
155 p_status_message := 'NOSTATUS';
156 END IF;
157
158 ELSIF l_event_status in ('P') THEN
159 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_For_Web
160 (p_web_booking_status_type => 'REQUESTED'
161 ,p_business_group_id => 81);
162
163 p_booking_status_id := l_booking_status_row.booking_status_type_id;
164 IF p_booking_status_id IS NOT NULL THEN
165 p_status_message := 'PLANNED_REQUESTED';
166 ELSE
167 p_status_message := 'NOSTATUS';
168 END IF;
169
170 ELSIF l_event_status = 'N' THEN
171 IF l_maximum_internal_allowed > 0 THEN
172 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_For_Web
173 (p_web_booking_status_type => 'PLACED'
174 ,p_business_group_id => 81);
175 p_booking_status_id := l_booking_status_row.booking_status_type_id;
176 IF p_booking_status_id IS NOT NULL THEN
177 p_status_message := 'NORMAL_PLACED';
178 ELSE
179 p_status_message := 'NOSTATUS';
180 END IF;
181
182 ELSE
183 l_booking_status_row := OTA_LEARNER_ENROLL_SS.Get_Booking_Status_For_Web
184 (p_web_booking_status_type => 'WAITLISTED'
185 ,p_business_group_id => 81);
186 p_booking_status_id := l_booking_status_row.booking_status_type_id;
187 IF p_booking_status_id IS NOT NULL THEN
188 p_status_message := 'NORMAL_WAITLISTED';
189 ELSE
190 p_status_message := 'NOSTATUS';
191 END IF;
192 END IF;
193 END IF;
194
195 END assign_enrollment_status;
196
197 FUNCTION get_total_selected_learners(p_bulk_enr_request_id IN NUMBER)
198 RETURN NUMBER
199 IS
200 CURSOR csr_get_number_of_learners IS
201 SELECT count(bulk_enr_request_id)
202 FROM ota_bulk_enr_req_members
203 WHERE bulk_enr_request_id = p_bulk_enr_request_id;
204
205 l_total_enr_requested NUMBER := 0;
206 BEGIN
207 OPEN csr_get_number_of_learners;
208 FETCH csr_get_number_of_learners INTO l_total_enr_requested;
209 CLOSE csr_get_number_of_learners;
210
211 RETURN l_total_enr_requested;
212 END get_total_selected_learners;
213
214 PROCEDURE get_enr_request_prereq_info(
215 p_bulk_enr_request_id IN NUMBER
216 ,p_unfulfil_course_prereqs OUT NOCOPY NUMBER
217 ,p_unfulfil_comp_prereqs OUT NOCOPY NUMBER)
218 IS
219 CURSOR get_unfulfil_crs_prereq IS
220 SELECT count(person_id)
221 FROM ota_bulk_enr_req_members brm,
222 ota_bulk_enr_requests ber,
223 ota_events evt
224 WHERE ber.bulk_enr_request_id = brm.bulk_enr_request_id
225 AND ber.bulk_enr_request_id = p_bulk_enr_request_id
226 AND evt.event_id = ber.object_id
227 AND ber.object_type = 'CL'
228 AND ota_cpr_utility.is_mand_crs_prereqs_completed(brm.person_id
229 , NULL
230 , brm.person_id
231 , 'E'
232 , evt.activity_version_id) = 'N';
233
234 CURSOR get_unfulfil_comp_prereqs IS
235 SELECT count(person_id)
236 FROM ota_bulk_enr_req_members brm,
237 ota_bulk_enr_requests ber,
238 ota_events evt
239 WHERE ber.bulk_enr_request_id = brm.bulk_enr_request_id
240 AND ber.bulk_enr_request_id = p_bulk_enr_request_id
241 AND evt.event_id = ber.object_id
242 AND ber.object_type = 'CL'
243 AND ota_cpr_utility.is_mand_comp_prereqs_completed(brm.person_id, evt.activity_version_id) = 'N';
244
245 BEGIN
246 p_unfulfil_course_prereqs := 0;
247 p_unfulfil_comp_prereqs := 0;
248
249 OPEN get_unfulfil_crs_prereq;
250 FETCH get_unfulfil_crs_prereq INTO p_unfulfil_course_prereqs;
251 CLOSE get_unfulfil_crs_prereq;
252
253 OPEN get_unfulfil_comp_prereqs;
254 FETCH get_unfulfil_comp_prereqs INTO p_unfulfil_comp_prereqs;
255 CLOSE get_unfulfil_comp_prereqs;
256
257 END get_enr_request_prereq_info;
258
259
260 PROCEDURE get_enr_request_info(
261 p_bulk_enr_request_id IN NUMBER
262 ,p_selected_learners OUT NOCOPY NUMBER
263 ,p_unfulfil_course_prereqs OUT NOCOPY NUMBER
264 ,p_unfulfil_comp_prereqs OUT NOCOPY NUMBER)
265 IS
266 l_request_rec csr_get_request_info%ROWTYPE;
267
268 BEGIN
269 OPEN csr_get_request_info(p_bulk_enr_request_id);
270 FETCH csr_get_request_info INTO l_request_rec;
271 IF csr_get_request_info%NOTFOUND THEN
272 CLOSE csr_get_request_info;
273 --ERRROR - this cond should not occur
274 p_unfulfil_course_prereqs := 0;
275 p_unfulfil_comp_prereqs := 0;
276 p_selected_learners := 0;
277 RETURN;
278 ELSE
279 CLOSE csr_get_request_info;
280 p_selected_learners := get_total_selected_learners(p_bulk_enr_request_id);
281 IF l_request_rec.object_type NOT IN ('CL','LPCL') THEN
282 p_unfulfil_course_prereqs := 0;
283 p_unfulfil_comp_prereqs := 0;
284 ELSE
285 get_enr_request_prereq_info(
286 p_bulk_enr_request_id
287 ,p_unfulfil_course_prereqs
288 ,p_unfulfil_comp_prereqs);
289 END IF;
290 END IF;
291 END get_enr_request_info;
292
293 PROCEDURE get_enr_req_completion_status(
294 p_bulk_enr_request_id IN NUMBER
295 ,p_selected_learners OUT NOCOPY NUMBER
296 ,p_success_enrollments OUT NOCOPY NUMBER
297 ,p_errored_enrollments OUT NOCOPY NUMBER
298 ,p_unfulfil_course_prereqs OUT NOCOPY NUMBER
299 ,p_unfulfil_comp_prereqs OUT NOCOPY NUMBER)
300 IS
301
302 CURSOR get_success_enrollments IS
303 SELECT count(person_id)
304 FROM ota_bulk_enr_req_members
305 WHERE bulk_enr_request_id = p_bulk_enr_request_id
306 AND enrollment_status IS NOT NULL;
307
308 CURSOR get_errored_enrollments IS
309 SELECT count(person_id)
310 FROM ota_bulk_enr_req_members
311 WHERE bulk_enr_request_id = p_bulk_enr_request_id
312 AND error_message IS NOT NULL;
313 BEGIN
314 get_enr_request_info(
315 p_bulk_enr_request_id => p_bulk_enr_request_id
316 ,p_selected_learners => p_selected_learners
317 ,p_unfulfil_course_prereqs => p_unfulfil_course_prereqs
318 ,p_unfulfil_comp_prereqs => p_unfulfil_comp_prereqs);
319
320 OPEN get_success_enrollments;
321 FETCH get_success_enrollments INTO p_success_enrollments;
322 CLOSE get_success_enrollments;
323
324 OPEN get_errored_enrollments;
325 FETCH get_errored_enrollments INTO p_errored_enrollments;
326 CLOSE get_errored_enrollments;
327
328 END get_enr_req_completion_status;
329
330
331 Function get_enrolled_learners(
332 p_object_type IN VARCHAR2
333 ,p_object_id IN VARCHAR2)
334 RETURN NUMBER IS
335
336 CURSOR cse_get_class_enrollments IS
337 SELECT sum(nvl(tdb.number_of_places,0))
338 FROM ota_delegate_bookings tdb
339 , ota_booking_status_types bst
340 WHERE tdb.booking_status_type_id = bst.booking_status_type_id
341 AND tdb.event_id = p_object_id
342 --TBD: needs to be confirmed if Requested(R) and Waitlisted(W) should also be included here
343 AND bst.type NOT IN ('C');
344
345 CURSOR csr_get_lp_enrollments IS
346 SELECT count(lp_enrollment_id)
347 FROM ota_lp_enrollments
348 WHERE learning_path_id = p_object_id
349 AND path_status_code <> 'CANCELLED';
350
351 CURSOR csr_get_cert_enrollments IS
352 SELECT count(cert_enrollment_id)
353 FROM ota_cert_enrollments crt
354 WHERE certification_id = p_object_id
355 --TBD: Confirm if this is correct
356 AND CERTIFICATION_STATUS_CODE <> 'CANCELLED';
357
358 l_enrolled_learners NUMBER := 0;
359
360 BEGIN
361 IF p_object_type in ('CL','LPCL') THEN
362 OPEN cse_get_class_enrollments;
363 FETCH cse_get_class_enrollments INTO l_enrolled_learners;
364 CLOSE cse_get_class_enrollments;
365
366 ELSIF p_object_type = 'LP' THEN
367 OPEN csr_get_lp_enrollments;
368 FETCH csr_get_lp_enrollments INTO l_enrolled_learners;
369 CLOSE csr_get_lp_enrollments;
370
371 ELSIF p_object_type = 'CRT' THEN
372 OPEN csr_get_cert_enrollments;
373 FETCH csr_get_cert_enrollments INTO l_enrolled_learners;
374 CLOSE csr_get_cert_enrollments;
375 END IF;
376
377 RETURN l_enrolled_learners;
378 END get_enrolled_learners;
379
380 PROCEDURE Create_Enrollment_And_Finance( p_event_id IN VARCHAR2
381 ,p_extra_information IN VARCHAR2
382 ,p_cost_centers IN VARCHAR2
383 ,p_assignment_id IN PER_ALL_ASSIGNMENTS_F.assignment_id%TYPE
384 ,p_business_group_id_from IN PER_ALL_ASSIGNMENTS_F.business_group_id%TYPE
385 ,p_organization_id IN PER_ALL_ASSIGNMENTS_F.organization_id%TYPE
386 ,p_person_id IN PER_ALL_PEOPLE_F.person_id%type
387 ,p_delegate_contact_id IN NUMBER
388 ,p_booking_id OUT NOCOPY OTA_DELEGATE_BOOKINGS.Booking_id%type
389 ,p_message_name OUT NOCOPY varchar2
390 ,p_tdb_information_category IN VARCHAR2
391 ,p_tdb_information1 IN VARCHAR2
392 ,p_tdb_information2 IN VARCHAR2
393 ,p_tdb_information3 IN VARCHAR2
394 ,p_tdb_information4 IN VARCHAR2
395 ,p_tdb_information5 IN VARCHAR2
396 ,p_tdb_information6 IN VARCHAR2
397 ,p_tdb_information7 IN VARCHAR2
398 ,p_tdb_information8 IN VARCHAR2
399 ,p_tdb_information9 IN VARCHAR2
400 ,p_tdb_information10 IN VARCHAR2
401 ,p_tdb_information11 IN VARCHAR2
402 ,p_tdb_information12 IN VARCHAR2
403 ,p_tdb_information13 IN VARCHAR2
404 ,p_tdb_information14 IN VARCHAR2
405 ,p_tdb_information15 IN VARCHAR2
406 ,p_tdb_information16 IN VARCHAR2
407 ,p_tdb_information17 IN VARCHAR2
408 ,p_tdb_information18 IN VARCHAR2
409 ,p_tdb_information19 IN VARCHAR2
410 ,p_tdb_information20 IN VARCHAR2
411 ,p_booking_justification_id IN VARCHAR2
412 ,p_override_prerequisites IN VARCHAR2
413 ,p_override_learner_access IN VARCHAR2
414 ,p_is_mandatory_enrollment IN VARCHAR2 default 'N')
415 IS
416
417 CURSOR bg_to (pp_event_id ota_events.event_id%TYPE) IS
418 SELECT hao.business_group_id,
419 evt.organization_id,
420 evt.currency_code,
421 evt.course_start_date,
422 evt.course_end_date,
423 evt.Title,
424 evt.owner_id,
425 off.activity_version_id,
426 evt.offering_id
427 FROM OTA_EVENTS_VL evt,
428 OTA_OFFERINGS off,
429 HR_ALL_ORGANIZATION_UNITS hao
430 WHERE evt.event_id = pp_event_id
431 AND off.offering_id = evt.parent_offering_id
432 AND evt.organization_id = hao.organization_id (+);
433
434
435 Cursor Get_Event_status is
436 Select event_status, maximum_internal_attendees, maximum_attendees
437 from OTA_EVENTS
438 WHERE EVENT_ID = TO_NUMBER(p_event_id);
439
440 CURSOR get_existing_internal IS
441 SELECT sum(nvl(dbt.number_of_places, 0))
442 FROM OTA_DELEGATE_BOOKINGS dbt,
443 OTA_BOOKING_STATUS_TYPES bst
444 WHERE dbt.event_id = TO_NUMBER(p_event_id)
445 AND dbt.internal_booking_flag = 'Y'
446 AND dbt.booking_status_type_id = bst.booking_status_type_id
447 AND bst.type in ('P','A','E');
448
449 CURSOR get_existing_bookings IS
450 SELECT sum(number_of_places)
451 FROM OTA_DELEGATE_BOOKINGS dbt,
452 OTA_BOOKING_STATUS_TYPES bst
453 WHERE dbt.event_id = TO_NUMBER(p_event_id)
454 AND dbt.booking_status_type_id = bst.booking_status_type_id
455 AND bst.type in ('P','A','E');
456
457
458 CURSOR c_get_price_basis is
459 SELECT nvl(price_basis,NULL)
460 FROM ota_events
461 where event_id = p_event_id;
462
463 CURSOR csr_user(p_owner_id in number) IS
464 SELECT
465 USER_NAME
466 FROM
467 FND_USER
468 WHERE
469 Employee_id = p_owner_id ;
470
471 CURSOR csr_activity(p_activity_version_id number )
472 IS
473 SELECT version_name
474 FROM OTA_ACTIVITY_VERSIONS_TL
475 WHERE activity_version_id = p_activity_version_id
476 AND language=userenv('LANG');
477
478 CURSOR csr_get_priority IS
479 SELECT bjs.priority_level
480 FROM ota_bkng_justifications_b BJS
481 WHERE bjs.booking_justification_id = p_booking_justification_id;
482
483
484 --Added FOR bug#5579345
485 /*CURSOR csr_get_assignment_details IS
486 SELECT ppf.work_telephone,
487 paf.organization_id,
488 ppf.email_address
489 FROM
490 per_all_people_f ppf,
491 per_all_assignments_f paf
492 WHERE
493 ppf.person_id = paf.person_id
494 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
495 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
496 AND ppf.person_id = p_person_id
497 AND paf.assignment_id = p_assignment_id;*/
498
499 --Bug6723416 :Modified the cursor to fetch phone number from per_phones
500
501 /*CURSOR csr_get_assignment_details IS
502 SELECT PPH.PHONE_NUMBER work_telephone,
503 paf.organization_id,
504 ppf.email_address
505 FROM
506 per_all_people_f ppf,
507 per_all_assignments_f paf ,
508 per_phones pph
509 WHERE
510 ppf.person_id = paf.person_id
511 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
512 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
513 AND ppf.person_id = p_person_id
514 AND paf.assignment_id = p_assignment_id
515 AND pph.PARENT_ID(+) = ppf.PERSON_ID
516 AND pph.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
517 AND pph.PHONE_TYPE(+) = 'W1'
518 --AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM, SYSDATE) AND NVL(PPH.DATE_TO, SYSDATE);For bug6770085
519 AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM(+), SYSDATE) AND NVL(PPH.DATE_TO(+), SYSDATE);*/
520
521 CURSOR csr_get_assignment_details IS
522 SELECT PPH.PHONE_NUMBER work_telephone,
523 paf.organization_id,
524 ppf.email_address,
525 pfax.PHONE_NUMBER fax_number
526 FROM
527 per_all_people_f ppf,
528 per_all_assignments_f paf ,
529 per_phones pph,
530 per_phones pfax
531 WHERE
532 ppf.person_id = paf.person_id
533 AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
534 AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
535 AND ppf.person_id = p_person_id
536 AND paf.assignment_id = p_assignment_id
537 AND pph.PARENT_ID(+) = ppf.PERSON_ID
538 AND pph.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
539 AND pph.PHONE_TYPE(+) = 'W1'
540 --AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM, SYSDATE) AND NVL(PPH.DATE_TO, SYSDATE);For bug6770085
541 AND trunc(sysdate) BETWEEN NVL(PPH.DATE_FROM(+), SYSDATE) AND NVL(PPH.DATE_TO(+), SYSDATE)
542 AND pfax.PARENT_ID(+) = ppf.PERSON_ID
543 AND pfax.PARENT_TABLE(+) = 'PER_ALL_PEOPLE_F'
544 AND pfax.PHONE_TYPE(+) = 'WF'
545 AND trunc(sysdate) BETWEEN NVL(pfax.DATE_FROM(+), SYSDATE) AND NVL(pfax.DATE_TO(+), SYSDATE);
546
547
548 l_price_basis OTA_EVENTS.price_basis%TYPE;
549
550 l_person_details csr_get_assignment_details%ROWTYPE;
551 --
552 l_booking_status_row OTA_BOOKING_STATUS_TYPES%ROWTYPE;
553 l_booking_id OTA_DELEGATE_BOOKINGS.booking_id%type := null;
554 l_object_version_number BINARY_INTEGER;
555 l_tfl_ovn BINARY_INTEGER;
556 l_finance_line_id OTA_FINANCE_LINES.finance_line_id%type:= null;
557 l_booking_type VARCHAR2(4000);
558 l_error_crypt VARCHAR2(4000);
559 --
560 l_mode VARCHAR2(200);
561 l_delegate_id PER_PEOPLE_F.person_id%TYPE;
562 l_restricted_assignment_id PER_ASSIGNMENTS_F.assignment_id%type;
563 l_cancel_boolean BOOLEAN;
564
565 -- -------------------
566 -- Finance API Vars
567 -- -------------------
568 l_auto_create_finance VARCHAR2(40);
569 fapi_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
570 fapi_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
571 fapi_result VARCHAR2(40);
572 fapi_from VARCHAR2(5);
573 fapi_to VARCHAR2(5);
574
575 result_finance_header_id OTA_FINANCE_LINES.finance_header_id%TYPE;
576 result_create_finance_line VARCHAR2(5) := 'Y';
577 result_object_version_number OTA_FINANCE_LINES.object_version_number%TYPE;
578
579 l_logged_in_user NUMBER;
580 l_user NUMBER;
581 l_automatic_transfer_gl VARCHAR2(40);
582 l_notification_text VARCHAR2(1000);
583 l_cost_allocation_keyflex_id VARCHAR2(1000);
584
585 l_event_status VARCHAR2(30);
586
587 l_maximum_internal_attendees NUMBER;
588 l_existing_internal NUMBER;
589 l_maximum_internal_allowed NUMBER;
590
591 l_called_from VARCHAR2(80);
592 l_business_group_id_to hr_all_organization_units.organization_id%type;
593 l_sponsor_organization_id hr_all_organization_units.organization_id%type;
594 l_event_currency_code ota_events.currency_code%type;
595 l_event_title ota_events.title%type;
596 l_course_start_date ota_events.course_start_date%type;
597 l_course_end_date ota_events.course_end_date%type;
598 l_owner_id ota_events.owner_id%type;
599 l_activity_version_id ota_activity_versions.activity_version_id%type;
600 l_version_name ota_activity_versions.version_name%type;
601 l_owner_username fnd_user.user_name%type;
602 l_offering_id ota_events.offering_id%type;
603 l_booking_status_used VARCHAR2(20);
604
605 l_existing_bookings NUMBER;
606 l_maximum_external_allowed NUMBER;
607 l_maximum_attendees NUMBER;
608 l_internal_booking_flag OTA_DELEGATE_BOOKINGS.internal_booking_flag%TYPE;
609 l_work_telephone OTA_DELEGATE_BOOKINGS.delegate_contact_phone%TYPE := NULL;
610 l_work_fax OTA_DELEGATE_BOOKINGS.delegate_contact_fax%TYPE := NULL;
611 l_organization_id OTA_DELEGATE_BOOKINGS.organization_id%TYPE := NULL;
612 l_assignment_id OTA_DELEGATE_BOOKINGS.delegate_assignment_id%TYPE := NULL;
613 l_email_address OTA_DELEGATE_BOOKINGS.delegate_contact_email%TYPE := NULL;
614 l_person_address_type VARCHAR2(1);
615 l_ext_lrnr_details ota_learner_enroll_ss.csr_ext_lrnr_Details%ROWTYPE;
616 l_customer_id HZ_CUST_ACCOUNT_ROLES.cust_account_id%type := NULL;
617 l_corespondent VARCHAR2(1) := NULL;
618 l_source_of_booking VARCHAR2(30) := NULL; --Bug 5580960 : Incleased the SIZE.
619 l_enrollment_type VARCHAR2(1) := 'S';
620 l_priority_level VARCHAR2(30) := null;
621
622 -- Added for DFF defaulting bug#5478206
623 l_attribute_category VARCHAR2(30) := p_tdb_information_category;
624 l_attribute1 VARCHAR2(150) := p_tdb_information1 ;
625 l_attribute2 VARCHAR2(150) := p_tdb_information2 ;
626 l_attribute3 VARCHAR2(150) := p_tdb_information3 ;
627 l_attribute4 VARCHAR2(150) := p_tdb_information4 ;
628 l_attribute5 VARCHAR2(150) := p_tdb_information5 ;
629 l_attribute6 VARCHAR2(150) := p_tdb_information6 ;
630 l_attribute7 VARCHAR2(150) := p_tdb_information7 ;
631 l_attribute8 VARCHAR2(150) := p_tdb_information8 ;
632 l_attribute9 VARCHAR2(150) := p_tdb_information9 ;
633 l_attribute10 VARCHAR2(150) := p_tdb_information10 ;
634 l_attribute11 VARCHAR2(150) := p_tdb_information11 ;
635 l_attribute12 VARCHAR2(150) := p_tdb_information12 ;
636 l_attribute13 VARCHAR2(150) := p_tdb_information13 ;
637 l_attribute14 VARCHAR2(150) := p_tdb_information14 ;
638 l_attribute15 VARCHAR2(150) := p_tdb_information15 ;
639 l_attribute16 VARCHAR2(150) := p_tdb_information16 ;
640 l_attribute17 VARCHAR2(150) := p_tdb_information17 ;
641 l_attribute18 VARCHAR2(150) := p_tdb_information18 ;
642 l_attribute19 VARCHAR2(150) := p_tdb_information19 ;
643 l_attribute20 VARCHAR2(150) := p_tdb_information20 ;
644 -- end of code added for bug#5478206
645 BEGIN
646
647 HR_UTIL_MISC_WEB.VALIDATE_SESSION(p_person_id => l_logged_in_user);
648
649 -- ----------------------------------------------------------------------
650 -- RETRIEVE THE DATA REQUIRED
651 -- ----------------------------------------------------------------------
652 ota_utility.Get_Default_Value_Dff( appl_short_name => 'OTA'
653 ,flex_field_name => 'OTA_DELEGATE_BOOKINGS'
654 ,p_attribute_category => l_attribute_category
655 ,p_attribute1 => l_attribute1
656 ,p_attribute2 => l_attribute2
657 ,p_attribute3 => l_attribute3
658 ,p_attribute4 => l_attribute4
659 ,p_attribute5 => l_attribute5
660 ,p_attribute6 => l_attribute6
661 ,p_attribute7 => l_attribute7
662 ,p_attribute8 => l_attribute8
663 ,p_attribute9 => l_attribute9
664 ,p_attribute10 => l_attribute10
665 ,p_attribute11 => l_attribute11
666 ,p_attribute12 => l_attribute12
667 ,p_attribute13 => l_attribute13
668 ,p_attribute14 => l_attribute14
669 ,p_attribute15 => l_attribute15
670 ,p_attribute16 => l_attribute16
671 ,p_attribute17 => l_attribute17
672 ,p_attribute18 => l_attribute18
673 ,p_attribute19 => l_attribute19
674 ,p_attribute20 => l_attribute20);
675
676
677 BEGIN
678
679 IF p_booking_justification_id IS NOT NULL THEN
680 OPEN csr_get_priority;
681 FETCH csr_get_priority INTO l_priority_level;
682 CLOSE csr_get_priority;
683 END IF;
684
685 IF p_person_id IS NOT NULL THEN
686 l_delegate_id := p_person_id;
687 l_person_address_type := 'I';
688 l_corespondent := 'S';
689 -- l_source_of_booking := 'E'; Bug 5580960: removed hardcoding. Now Source of Booking will be decided by profile value OTA_DEFAULT_ENROLLMENT_SOURCE
690 l_source_of_booking := fnd_profile.value('OTA_DEFAULT_ENROLLMENT_SOURCE');
691
692 l_restricted_assignment_id := p_assignment_id;
693
694 --Modified for bug#5579345
695 -- l_person_details := ota_learner_enroll_ss.Get_Person_To_Enroll_Details(p_person_id => l_delegate_id);
696 OPEN csr_get_assignment_details;
697 FETCH csr_get_assignment_details INTO l_person_details;
698 IF csr_get_assignment_details%NOTFOUND THEN
699 CLOSE csr_get_assignment_details;
700 fnd_message.set_name ('OTA','OTA_NO_DELEGATE_INFORMATION');
701 --
702 -- Raise the error for the main procedure exception handler
703 -- to handle
704 p_message_name := SUBSTR(SQLERRM, 1,300);
705 RETURN;
706 ELSE
707 l_internal_booking_flag := 'Y';
708 l_work_telephone := l_person_details.work_telephone;
709 l_organization_id := l_person_details.organization_id;
710 l_assignment_id := p_assignment_id;
711 l_email_address := l_person_details.email_address;
712 l_work_fax := l_person_details.fax_number;
713 CLOSE csr_get_assignment_details;
714 END IF;
715
716 ELSE
717 l_internal_booking_flag := 'N';
718 l_person_address_type := null;
719 l_ext_lrnr_details := ota_learner_enroll_ss.Get_ext_lrnr_Details(p_delegate_contact_id);
720 l_customer_id := l_ext_lrnr_details.customer_id;
721 END IF;
722
723 -- -----------------------------------------------
724 -- Open BG Cursor to get the Business Group TO
725 -- -----------------------------------------------
726 OPEN bg_to(p_event_id);
727 FETCH bg_to INTO l_business_group_id_to,
728 l_sponsor_organization_id,
729 l_event_currency_code,
730 l_course_start_date,
731 l_course_end_date,
732 l_event_title,
733 l_owner_id,
734 l_activity_version_id,
735 l_offering_id;
736 CLOSE bg_to;
737
738
739 For act in csr_activity(l_activity_version_id)
740 Loop
741 l_version_name := act.version_name;
742 End Loop;
743
744 if l_owner_id is not null then
745 For owner in csr_user(l_owner_id)
746 Loop
747 l_owner_username := owner.user_name;
748 End Loop;
749 end if;
750
751
752 -- The enrollment doesn't need mangerial approval so check the mode
753 -- to find out whether they can only be waitlisted and then get the
754 -- default booking status for either waitlisted or placed.
755
756 OPEN get_event_status;
757 FETCH get_event_status into l_event_status, l_maximum_internal_attendees,l_maximum_attendees;
758 CLOSE get_event_status;
759
760 IF p_person_id IS NOT NULL THEN
761 OPEN get_existing_internal;
762 FETCH get_existing_internal into l_existing_internal;
763 CLOSE get_existing_internal;
764
765 l_maximum_internal_allowed := nvl(l_maximum_internal_attendees,0) - nvl(l_existing_internal,0);
766 ELSE
767 OPEN get_existing_bookings;
768 FETCH get_existing_bookings into l_existing_bookings;
769 CLOSE get_existing_bookings;
770
771 l_maximum_external_allowed := nvl(l_maximum_attendees,0) - nvl(l_existing_bookings,0);
772 END IF;
773
774 --Create enrollments in Waitlisted status for planned class
775 IF l_event_status in ('F','P') THEN
776
777 l_booking_status_row := ota_learner_enroll_ss.Get_Booking_Status_for_web
778 (p_web_booking_status_type => 'WAITLISTED'
779 ,p_business_group_id => ota_general.get_business_group_id);
780
781 l_booking_status_used := 'WAITLISTED';
782
783 /*ELSIF l_event_status in ('P') THEN
784
785 l_booking_status_row := ota_learner_enroll_ss.Get_Booking_Status_for_web
786 (p_web_booking_status_type => 'REQUESTED'
787 ,p_business_group_id => ota_general.get_business_group_id);
788
789 l_booking_status_used := 'REQUESTED';*/
790
791 ELSIF l_event_status = 'N' THEN
792
793 IF l_maximum_internal_attendees is null then
794 l_booking_status_row := ota_learner_enroll_ss.Get_Booking_Status_for_web
795 (p_web_booking_status_type => 'PLACED'
796 ,p_business_group_id => ota_general.get_business_group_id);
797
798 l_booking_status_used := 'PLACED';
799
800 ELSE
801
802 IF l_maximum_internal_allowed > 0 OR l_maximum_external_allowed > 0 THEN
803 l_booking_status_row := ota_learner_enroll_ss.Get_Booking_Status_for_web
804 (p_web_booking_status_type => 'PLACED'
805 ,p_business_group_id => ota_general.get_business_group_id);
806
807 l_booking_status_used := 'PLACED';
808
809 ELSIF l_maximum_internal_allowed <= 0 OR l_maximum_external_allowed <= 0 THEN
810 l_booking_status_row := ota_learner_enroll_ss.Get_Booking_Status_for_web
811 (p_web_booking_status_type => 'WAITLISTED'
812 ,p_business_group_id => ota_general.get_business_group_id);
813
814 l_booking_status_used := 'WAITLISTED';
815
816 END IF;
817 END IF;
818 IF l_booking_status_row.booking_Status_type_id is null then
819 fnd_message.set_name ('OTA','OTA_13667_WEB_STATUS_NOT_SEEDE');
820 RAISE ota_learner_enroll_ss.g_mesg_on_stack_exception ;
821 END IF ;
822 END IF;
823
824 EXCEPTION
825 WHEN ota_learner_enroll_ss.g_mesg_on_stack_exception THEN
826 --
827 -- Store the technical message which will have been seeded
828 -- if this exception has been raised. This will be used to provide
829 -- the code.
830 --
831 hr_message.provide_error;
832 --
833 -- Now distinguish which error was raised.
834 --
835 IF (hr_message.last_message_name = 'OTA_13667_WEB_STATUS_NOT_SEEDE') THEN
836 --
837 -- Seed the user friendly message
838 --
839 fnd_message.set_name ('OTA','OTA_WEB_INCORRECT_CONF');
840 --
841 -- Raise the error for the main procedure exception handler
842 -- to handle
843 --
844 p_message_name := hr_message.last_message_name;
845 p_message_name := SUBSTR(SQLERRM, 1,300);
846 --
847 ELSIF (hr_message.last_message_name = 'HR_51396_WEB_PERSON_NOT_FND') THEN
848 --
849 -- Seed the user friendly message
850 --
851 fnd_message.set_name ('OTA','OTA_NO_DELEGATE_INFORMATION');
852 --
853 -- Raise the error for the main procedure exception handler
854 -- to handle
855 p_message_name := 'OTA_NO_DELEGATE_INFORMATION';
856 p_message_name := SUBSTR(SQLERRM, 1,300);
857 --
858 --
859 ELSE
860 -- Raise the error for the main procedure exception handler
861 -- to handle
862 p_message_name := hr_message.get_message_text;
863
864 --
865 END IF;
866 --
867 WHEN OTHERS THEN
868 --
869 -- Can't store a technical message, as we don't know what it is
870 -- and a message may not have been put on the stack
871 --
872 hr_message.provide_error;
873 --
874 -- Seed the user friendly message
875 --
876 fnd_message.set_name ('OTA','OTA_WEB_ERR_GETTING_INFO');
877 --
878 --
879 -- Raise the error for the main procedure exception handler
880 -- to handle
881 --
882 p_message_name := SUBSTR(SQLERRM, 1,300);
883
884 END ;
885 --
886 -- ----------------------------------------------------------------------
887 -- Save
888 -- ----------------------------------------------------------------------
889 -- If there are no errors, save to the database
890 -- (there shouldn't be as the main exception handler will be used
891 --
892 IF p_message_name is null then
893
894
895 BEGIN
896 --
897 -- Check to see if delegate has a booking status of CANCELLED for
898 -- this event, if cancelled l_cancel_boolean is set to true
899 -- FIX for bug 900679
900 --
901 l_cancel_boolean := ota_learner_enroll_ss.Chk_Event_Cancelled_for_Person(
902 p_event_id => p_event_id
903 ,p_delegate_person_id => l_delegate_id
904 ,p_delegate_contact_id => p_delegate_contact_id
905 ,p_booking_id => l_booking_id);
906
907 l_auto_create_finance := FND_PROFILE.value('OTA_AUTO_CREATE_FINANCE');
908 l_automatic_transfer_gl := FND_PROFILE.value('OTA_SSHR_AUTO_GL_TRANSFER');
909 l_user := FND_PROFILE.value('USER_ID');
910
911 IF (l_cancel_boolean) THEN
912 --
913 -- Delegate has a Cancelled status for this event, hence
914 -- we must update the existing record by changing Cancelled
915 -- to Requested status
916 --
917
918 l_object_version_number := OTA_LEARNER_ENROLL_SS.Get_Booking_OVN (p_booking_id => l_booking_id);
919
920 /* Call Cancel procedure to cancel the Finance if person Re-enroll */
921 ota_learner_enroll_ss.cancel_finance(l_booking_id);
922
923
924 -- ----------------------------------------------------------------
925 -- Delegate has no record for this event, hence create a record
926 -- with requested status
927 -- ----------------------------------------------------------------
928 -- Check if the Profile AutoCreate Finance is ON or OFF
929 -- ----------------------------------------------------------------
930 END IF;
931 open c_get_price_basis;
932 fetch c_get_price_basis into l_price_basis;
933 close c_get_price_basis;
934
935
936 IF l_delegate_id IS NOT NULL
937 AND l_auto_create_finance = 'Y'
938 and l_price_basis <> 'N'
939 and l_event_currency_code is not null THEN
940
941 l_cost_allocation_keyflex_id := TO_NUMBER(p_cost_centers);
942 result_finance_header_id := fapi_finance_header_id;
943 result_object_version_number := l_object_version_number;
944
945 ota_crt_finance_segment.Create_Segment(
946 p_assignment_id => p_assignment_id,
947 p_business_group_id_from => p_business_group_id_from,
948 p_business_group_id_to => l_business_group_id_to,
949 p_organization_id => p_organization_id,
950 p_sponsor_organization_id => l_sponsor_organization_id,
951 p_event_id => p_event_id,
952 p_person_id => l_delegate_id,
953 p_currency_code => l_event_currency_code,
954 p_cost_allocation_keyflex_id=> l_cost_allocation_keyflex_id,
955 p_user_id => l_user,
956 p_finance_header_id => fapi_finance_header_id,
957 p_object_version_number => fapi_object_version_number,
958 p_result => fapi_result,
959 p_from_result => fapi_from,
960 p_to_result => fapi_to );
961
962 IF fapi_result = 'S' THEN
963 result_object_version_number := fapi_object_version_number;
964 result_finance_header_id := fapi_finance_header_id;
965
966 ELSIF fapi_result = 'E' THEN
967 result_object_version_number := l_object_version_number;
968 result_finance_header_id := NULL;
969 result_create_finance_line := NULL;
970 END IF;
971
972 ota_tdb_api_ins2.Create_Enrollment(
973 p_booking_id => l_booking_id
974 ,p_booking_status_type_id => l_booking_status_row.booking_status_type_id
975 ,p_delegate_person_id => l_delegate_id
976 ,p_delegate_contact_id => null
977 ,p_contact_id => null
978 ,p_business_group_id => ota_general.get_business_group_id
979 ,p_event_id => p_event_id
980 -- ,p_date_booking_placed => trunc(sysdate)
981 ,p_date_booking_placed => sysdate
982 ,p_corespondent => l_corespondent
983 ,p_internal_booking_flag => l_internal_booking_flag
984 ,p_person_address_type => l_person_address_type
985 ,p_number_of_places => 1
986 ,p_object_version_number => result_object_version_number
987 ,p_administrator => l_user --Bug 13907998
988 ,p_delegate_contact_phone => l_work_telephone
989 ,p_delegate_contact_fax => l_work_fax
990 ,p_source_of_booking => l_source_of_booking
991 ,p_special_booking_instructions => p_extra_information
992 ,p_successful_attendance_flag => 'N'
993 ,p_finance_header_id => result_finance_header_id
994 ,p_create_finance_line => result_create_finance_line
995 ,p_finance_line_id => l_finance_line_id
996 ,p_enrollment_type => l_enrollment_type
997 ,p_validate => FALSE
998 ,p_currency_code => l_event_currency_code
999 ,p_organization_id => l_organization_id
1000 ,p_delegate_assignment_id => l_assignment_id
1001 ,p_delegate_contact_email => l_email_address
1002 -- Modified for bug#5478206
1003 ,p_tdb_information_category => l_attribute_category
1004 ,p_tdb_information1 => l_attribute1
1005 ,p_tdb_information2 => l_attribute2
1006 ,p_tdb_information3 => l_attribute3
1007 ,p_tdb_information4 => l_attribute4
1008 ,p_tdb_information5 => l_attribute5
1009 ,p_tdb_information6 => l_attribute6
1010 ,p_tdb_information7 => l_attribute7
1011 ,p_tdb_information8 => l_attribute8
1012 ,p_tdb_information9 => l_attribute9
1013 ,p_tdb_information10 => l_attribute10
1014 ,p_tdb_information11 => l_attribute11
1015 ,p_tdb_information12 => l_attribute12
1016 ,p_tdb_information13 => l_attribute13
1017 ,p_tdb_information14 => l_attribute14
1018 ,p_tdb_information15 => l_attribute15
1019 ,p_tdb_information16 => l_attribute16
1020 ,p_tdb_information17 => l_attribute17
1021 ,p_tdb_information18 => l_attribute18
1022 ,p_tdb_information19 => l_attribute19
1023 ,p_tdb_information20 => l_attribute20
1024 ,p_booking_justification_id => p_booking_justification_id
1025 ,p_booking_priority => l_priority_level
1026 ,p_override_prerequisites => p_override_prerequisites
1027 ,p_override_learner_access => 'Y'
1028 ,p_is_mandatory_enrollment => p_is_mandatory_enrollment
1029 );
1030
1031
1032 IF l_automatic_transfer_gl = 'Y' AND l_finance_line_id IS NOT NULL AND l_offering_id is null THEN
1033
1034 UPDATE ota_finance_lines SET transfer_status = 'AT'
1035 WHERE finance_line_id = l_finance_line_id;
1036
1037
1038
1039 END IF;
1040
1041 ELSE
1042
1043 ota_tdb_api_ins2.Create_Enrollment(p_booking_id => l_booking_id
1044 ,p_booking_status_type_id => l_booking_status_row.booking_status_type_id
1045 ,p_delegate_person_id => l_delegate_id
1046 ,p_delegate_contact_id => p_delegate_contact_id
1047 ,p_customer_id => l_customer_id
1048 ,p_contact_id => null
1049 ,p_business_group_id => ota_general.get_business_group_id
1050 ,p_event_id => p_event_id
1051 -- ,p_date_booking_placed => trunc(sysdate)
1052 ,p_date_booking_placed => sysdate
1053 ,p_corespondent => l_corespondent
1054 ,p_internal_booking_flag => l_internal_booking_flag
1055 ,p_person_address_type => l_person_address_type
1056 ,p_number_of_places => 1
1057 ,p_object_version_number => l_object_version_number
1058 ,p_administrator => l_user --Bug 13907998
1059 ,p_delegate_contact_phone => l_work_telephone
1060 ,p_delegate_contact_fax => l_work_fax
1061 ,p_source_of_booking => l_source_of_booking
1062 ,p_special_booking_instructions => p_extra_information
1063 ,p_successful_attendance_flag => 'N'
1064 ,p_finance_line_id => l_finance_line_id
1065 ,p_enrollment_type => l_enrollment_type
1066 ,p_validate => FALSE
1067 ,p_organization_id => l_organization_id
1068 ,p_delegate_assignment_id => l_assignment_id
1069 ,p_delegate_contact_email => l_email_address
1070 -- Modified for bug#5478206
1071 ,p_tdb_information_category => l_attribute_category
1072 ,p_tdb_information1 => l_attribute1
1073 ,p_tdb_information2 => l_attribute2
1074 ,p_tdb_information3 => l_attribute3
1075 ,p_tdb_information4 => l_attribute4
1076 ,p_tdb_information5 => l_attribute5
1077 ,p_tdb_information6 => l_attribute6
1078 ,p_tdb_information7 => l_attribute7
1079 ,p_tdb_information8 => l_attribute8
1080 ,p_tdb_information9 => l_attribute9
1081 ,p_tdb_information10 => l_attribute10
1082 ,p_tdb_information11 => l_attribute11
1083 ,p_tdb_information12 => l_attribute12
1084 ,p_tdb_information13 => l_attribute13
1085 ,p_tdb_information14 => l_attribute14
1086 ,p_tdb_information15 => l_attribute15
1087 ,p_tdb_information16 => l_attribute16
1088 ,p_tdb_information17 => l_attribute17
1089 ,p_tdb_information18 => l_attribute18
1090 ,p_tdb_information19 => l_attribute19
1091 ,p_tdb_information20 => l_attribute20
1092 ,p_booking_justification_id => p_booking_justification_id
1093 ,p_booking_priority => l_priority_level
1094 ,p_override_prerequisites => p_override_prerequisites
1095 ,p_override_learner_access => 'Y'
1096 ,p_is_mandatory_enrollment => p_is_mandatory_enrollment
1097 );
1098
1099
1100 END IF;
1101 p_booking_id := l_booking_id;
1102
1103 IF l_booking_id is not null then
1104
1105 IF l_booking_status_used = 'PLACED' then
1106 p_message_name := 'OTA_443526_CONFIRMED_PLACED';
1107 ELSIF l_booking_status_used = 'WAITLISTED' then
1108 p_message_name := 'OTA_443527_CONFIRMED_WAITLIST';
1109 ELSIF l_booking_status_used = 'REQUESTED' then
1110 p_message_name := 'OTA_443528_CONFIRMED_REQUESTED';
1111 END IF;
1112 END IF;
1113
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 -- Both the Confirm Procedure and the API return APP-20002 or -20001
1117 -- so provide error can be used, as if the confirm procedure errors
1118 -- a different tool bar will be used.
1119 -- If the API has errored, the WF won't have been activated
1120 -- whereas if the confirm procedure errored, then it probably will have
1121 -- been.
1122 -- p_mode will be changed to indicate an error and,if it's a WF error
1123 -- the mode will also indicate this.
1124 -- Then the "Confirmation" page will be called from the main handler.
1125 --
1126 -- It is OK to use hr_message.provide_error as an application
1127 -- error will have been raised which will have put an error onto
1128 -- the stack
1129 --
1130 p_message_name := fnd_message.get;
1131 --
1132 END; -- End of if p_message is not null
1133
1134 END IF;
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 p_message_name := SUBSTR(SQLERRM, 1,300);
1138 END Create_Enrollment_And_Finance;
1139
1140
1141 FUNCTION get_object_name(p_object_type IN VARCHAR2, p_object_id IN NUMBER)
1142 RETURN VARCHAR2 IS
1143 CURSOR csr_get_lp_name IS
1144 SELECT name
1145 FROM ota_learning_paths_tl
1146 WHERE learning_path_id = p_object_id
1147 AND language = USERENV('LANG');
1148
1149 CURSOR csr_get_cert_name IS
1150 SELECT name
1151 FROM ota_certifications_tl
1152 WHERE certification_id = p_object_id
1153 AND language = USERENV('LANG');
1154
1155 CURSOR csr_get_class_name IS
1156 SELECT title
1157 FROM ota_events_tl
1158 WHERE event_id = p_object_id
1159 AND language = USERENV('LANG');
1160
1161 l_class_name OTA_EVENTS_TL.TITLE%TYPE := NULL;
1162 l_lp_name OTA_LEARNING_PATHS_TL.NAME%TYPE := NULL;
1163 l_cert_name OTA_CERTIFICATIONS_TL.NAME%TYPE := NULL;
1164 BEGIN
1165 IF p_object_type in ('CL','LPCL','CLU') THEN ----modified for 12426494
1166 OPEN csr_get_class_name;
1167 FETCH csr_get_class_name INTO l_class_name;
1168 CLOSE csr_get_class_name;
1169 RETURN l_class_name;
1170
1171 ELSIF p_object_type = 'LP' THEN
1172 OPEN csr_get_lp_name;
1173 FETCH csr_get_lp_name INTO l_lp_name;
1174 CLOSE csr_get_lp_name;
1175 RETURN l_lp_name;
1176
1177 ELSIF p_object_type = 'CRT' THEN
1178 OPEN csr_get_cert_name;
1179 FETCH csr_get_cert_name INTO l_cert_name;
1180 CLOSE csr_get_cert_name;
1181 RETURN l_cert_name;
1182 END IF;
1183 RETURN NULL;
1184 END get_object_name;
1185
1186 PROCEDURE delete_bulk_enr_request
1187 (itemtype IN WF_ITEMS.ITEM_TYPE%TYPE
1188 ,itemkey IN WF_ITEMS.ITEM_KEY%TYPE
1189 ,actid IN NUMBER
1190 ,funcmode IN VARCHAR2
1191 ,resultout OUT nocopy VARCHAR2 ) AS
1192
1193 l_blk_enr_request_id OTA_BULK_ENR_REQUESTS.BULK_ENR_REQUEST_ID%TYPE;
1194 BEGIN
1195 IF (funcmode='RUN') THEN
1196 l_blk_enr_request_id := WF_ENGINE.getitemattrtext(itemtype => itemtype,
1197 itemkey => itemkey,
1198 aname =>'BLK_ENR_REQUEST_ID',
1199 ignore_notfound => true);
1200
1201 DELETE FROM OTA_BULK_ENR_REQ_MEMBERS
1202 WHERE BULK_ENR_REQUEST_ID = l_blk_enr_request_id;
1203
1204 DELETE FROM OTA_BULK_ENR_REQUESTS
1205 WHERE BULK_ENR_REQUEST_ID = l_blk_enr_request_id;
1206
1207 COMMIT;
1208
1209 resultout := 'COMPLETE';
1210 ELSE IF (funcmode='CANCEL') THEN
1211 resultout := 'COMPLETE';
1212 END IF;
1213 END IF;
1214 END delete_bulk_enr_request;
1215
1216
1217 --modified for 12426494
1218 PROCEDURE notify_requestor(p_enr_request_id IN NUMBER,
1219 p_process_name IN VARCHAR2 default 'OTA_BLK_ENR_NTF_PRC')
1220 IS
1221 l_proc varchar2(72) := g_package||'create_wf_process';
1222 l_process wf_activities.name%type :=p_process_name;
1223 l_item_type wf_items.item_type%type := 'OTWF';
1224 l_item_key wf_items.item_key%type;
1225
1226 l_user_name varchar2(80);
1227 l_person_id per_all_people_f.person_id%type;
1228
1229 l_process_display_name varchar2(240);
1230 l_request_rec csr_get_request_info%ROWTYPE;
1231
1232
1233 Cursor get_display_name is
1234 SELECT wrpv.display_name displayName
1235 FROM wf_runnable_processes_v wrpv
1236 WHERE wrpv.item_type = l_item_type
1237 AND wrpv.process_name = l_process;
1238
1239
1240 CURSOR csr_get_user_name(p_person_id IN VARCHAR2) IS
1241 SELECT user_name
1242 FROM fnd_user
1243 WHERE employee_id=p_person_id;
1244
1245
1246 CURSOR csr_get_person_name(p_person_id IN number) IS
1247 SELECT ppf.full_name
1248 FROM per_all_people_f ppf
1249 WHERE person_id = p_person_id;
1250
1251 CURSOR csr_get_error_learners IS
1252 SELECT COUNT(person_id)
1253 FROM ota_bulk_enr_req_members
1254 WHERE bulk_enr_request_id = p_enr_request_id
1255 and error_message IS NOT NULL;
1256
1257 CURSOR csr_get_selected_learners IS
1258 SELECT COUNT(person_id)
1259 FROM ota_bulk_enr_req_members
1260 WHERE bulk_enr_request_id = p_enr_request_id;
1261
1262 CURSOR csr_get_successful_learners IS
1263 SELECT COUNT(person_id)
1264 FROM ota_bulk_enr_req_members
1265 WHERE bulk_enr_request_id = p_enr_request_id
1266 and enrollment_status IS NOT NULL;
1267
1268 l_object_name VARCHAR2(240);
1269 l_person_full_name per_all_people_f.FULL_NAME%TYPE;
1270 l_error_learners NUMBER := 0;
1271 l_success_learners NUMBER := 0;
1272 l_selected_learners NUMBER := 0;
1273
1274 l_role_name wf_roles.name%type;
1275 l_role_display_name wf_roles.display_name%type;
1276
1277 BEGIN
1278 hr_utility.set_location('Entering:'||l_proc, 5);
1279
1280
1281 OPEN get_display_name;
1282 FETCH get_display_name INTO l_process_display_name;
1283 CLOSE get_display_name;
1284
1285 OPEN csr_get_request_info(p_enr_request_id);
1286 FETCH csr_get_request_info INTO l_request_rec;
1287 CLOSE csr_get_request_info;
1288
1289 l_object_name := OTA_BULK_ENROLL_UTIL.get_object_name(l_request_rec.object_type, l_request_rec.object_id);
1290
1291 -- Get the next item key from the sequence
1292 select hr_workflow_item_key_s.nextval
1293 into l_item_key
1294 from sys.dual;
1295
1296 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1297
1298 l_person_id := l_request_rec.requestor_id;
1299
1300 wf_engine.additemattr
1301 (itemtype => l_item_type
1302 ,itemkey => l_item_key
1303 ,aname => 'BLK_ENR_REQUEST_ID');
1304
1305 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'BLK_ENR_REQUEST_ID',p_enr_request_id);
1306 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'CONC_REQUEST_ID',l_request_rec.conc_program_request_id);
1307 --Enh 5606090: Language support for Bulk enrollment.
1308 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'OBJECT_NAME',l_request_rec.object_id);
1309 WF_ENGINE.setitemattrtext(
1310 l_item_type
1311 ,l_item_key
1312 ,'OBJECT_TYPE'
1313 ,l_request_rec.object_type);
1314 OPEN csr_get_error_learners;
1315 FETCH csr_get_error_learners INTO l_error_learners;
1316 CLOSE csr_get_error_learners;
1317
1318 OPEN csr_get_selected_learners;
1319 FETCH csr_get_selected_learners INTO l_selected_learners;
1320 CLOSE csr_get_selected_learners;
1321
1322 OPEN csr_get_successful_learners;
1323 FETCH csr_get_successful_learners INTO l_success_learners;
1324 CLOSE csr_get_successful_learners;
1325
1326 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'TOTAL_NUMBER',l_selected_learners);
1327 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'ERROR_NUMBER',l_error_learners);
1328 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'SUCCESS_NUMBER',l_success_learners);
1329
1330 IF l_person_id IS NOT NULL THEN
1331 OPEN csr_get_person_name(l_person_id);
1332 FETCH csr_get_person_name INTO l_person_full_name;
1333 CLOSE csr_get_person_name;
1334
1335 SELECT user_name INTO l_user_name
1336 FROM fnd_user
1337 WHERE employee_id=l_person_id
1338 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')) --Bug 5676892
1339 AND ROWNUM =1 ;
1340 if l_request_rec.object_type <> 'LPCL' then
1341 fnd_file.put_line(FND_FILE.LOG,'Requestor Name ' ||l_person_full_name);
1342 end if;
1343 IF l_person_full_name IS NOT NULL then
1344 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'EVENT_OWNER',l_user_name);
1345 END IF;
1346 END IF;
1347
1348 -- Get and set owner role
1349
1350 hr_utility.set_location('Before Getting Owner'||l_proc, 10);
1351
1352 WF_DIRECTORY.GetRoleName(p_orig_system =>'PER',
1353 p_orig_system_id => l_person_id,
1354 p_name =>l_role_name,
1355 p_display_name =>l_role_display_name);
1356
1357
1358 WF_ENGINE.SetItemOwner(itemtype => l_item_type,
1359 itemkey =>l_item_key,
1360 owner =>l_role_name);
1361
1362 hr_utility.set_location('After Setting Owner'||l_proc, 10);
1363
1364
1365 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
1366
1367 hr_utility.set_location('leaving:'||l_proc, 20);
1368
1369 EXCEPTION
1370 WHEN OTHERS THEN
1371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1372 END notify_requestor;
1373
1374 PROCEDURE mass_subscribe_to_lp(
1375 p_enr_request_id IN NUMBER
1376 ,p_from_conc_program IN boolean default false)
1377 IS
1378
1379 l_request_rec csr_get_request_info%ROWTYPE;
1380 l_req_member_rec csr_get_request_members%ROWTYPE;
1381 l_error_message ota_bulk_enr_req_members.error_message%TYPE;
1382 l_person_name per_all_people_f.full_name%TYPE;
1383 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
1384 l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
1385 l_return_status varchar2(30);
1386
1387
1388 BEGIN
1389 OPEN csr_get_request_info(p_enr_request_id);
1390 FETCH csr_get_request_info INTO l_request_rec;
1391 IF csr_get_request_info%NOTFOUND THEN
1392 CLOSE csr_get_request_info;
1393 -- Raise error that no request found
1394 ELSE
1395 CLOSE csr_get_request_info;
1396 FOR l_req_member_rec IN csr_get_request_members(p_enr_request_id) LOOP
1397
1398 IF l_req_member_rec.enrollment_status IS NULL THEN
1399 l_lp_enrollment_id := null;
1400 l_path_status_code := null;
1401 l_error_message := null;
1402
1403 begin
1404 OPEN csr_get_person_name(l_req_member_rec.person_id);
1405 FETCH csr_get_person_name INTO l_person_name;
1406 CLOSE csr_get_person_name;
1407 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1408
1409 -- call subscribe for each learning path
1410 ota_lp_enrollment_api.subscribe_to_learning_path(
1411 p_learning_path_id => l_request_rec.object_id
1412 ,p_person_id => l_req_member_rec.person_id
1413 ,p_enrollment_source_code => 'ADMIN'
1414 ,p_business_group_id => l_request_rec.business_group_id
1415 ,p_creator_person_id => l_request_rec.requestor_id
1416 ,p_lp_enrollment_id => l_lp_enrollment_id
1417 ,p_path_status_code => l_path_status_code);
1418 EXCEPTION
1419 when others then
1420
1421 l_error_message := fnd_message.get;
1422 fnd_message.clear;
1423 l_error_message := nvl(l_error_message,'Error When creating Learning Path subscription ');
1424 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
1425 UPDATE ota_bulk_enr_req_members
1426 SET error_message = l_error_message, enrollment_status = NULL
1427 WHERE person_id = l_req_member_rec.person_id
1428 AND bulk_enr_request_id = p_enr_request_id;
1429 END;
1430
1431 IF l_lp_enrollment_id IS NOT NULL THEN
1432 FND_FILE.PUT_LINE(FND_FILE.LOG,'Subscription Status - '
1433 || ota_utility.get_lookup_meaning('OTA_LEARNING_PATH_STATUS',l_path_status_code, 810));
1434 -- update lpe status to bulk_enr_req_members table
1435 UPDATE ota_bulk_enr_req_members
1436 SET enrollment_status = l_path_status_code, error_message = NULL
1437 WHERE person_id = l_req_member_rec.person_id
1438 AND bulk_enr_request_id = p_enr_request_id;
1439 END IF;
1440 END IF;
1441 END LOOP;
1442 IF p_from_conc_program THEN
1443 -- Start workflow and send a notification to the requestor
1444 notify_requestor(p_enr_request_id => p_enr_request_id);
1445 END IF;
1446
1447 END IF;
1448
1449 END mass_subscribe_to_lp;
1450
1451 PROCEDURE mass_subscribe_to_cert(
1452 p_enr_request_id IN NUMBER
1453 ,p_from_conc_program IN boolean default false)
1454 IS
1455
1456 l_request_rec csr_get_request_info%ROWTYPE;
1457 l_req_member_rec csr_get_request_members%ROWTYPE;
1458 l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%TYPE;
1459 l_error_message ota_bulk_enr_req_members.error_message%TYPE;
1460 l_person_name per_all_people_f.full_name%TYPE;
1461 l_certification_status_code ota_cert_enrollments.CERTIFICATION_STATUS_CODE%TYPE;
1462
1463
1464 BEGIN
1465 OPEN csr_get_request_info(p_enr_request_id);
1466 FETCH csr_get_request_info INTO l_request_rec;
1467 IF csr_get_request_info%NOTFOUND THEN
1468 -- Raise error that no request found
1469 CLOSE csr_get_request_info;
1470 ELSE
1471 CLOSE csr_get_request_info;
1472 FOR l_req_member_rec IN csr_get_request_members(p_enr_request_id) LOOP
1473 IF l_req_member_rec.enrollment_status IS NULL THEN
1474 l_cert_enrollment_id := null;
1475 l_certification_status_code := null;
1476 l_error_message := null;
1477
1478 begin
1479 OPEN csr_get_person_name(l_req_member_rec.person_id);
1480 FETCH csr_get_person_name INTO l_person_name;
1481 CLOSE csr_get_person_name;
1482 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1483
1484 -- call subscribe for each learning path
1485 OTA_CERT_ENROLLMENT_API.subscribe_to_certification(
1486 p_certification_id => l_request_rec.object_id
1487 ,p_person_id => l_req_member_rec.person_id
1488 ,p_business_group_id => l_request_rec.business_group_id
1489 ,p_approval_flag => 'N'
1490 ,p_is_history_flag => 'N'
1491 ,p_cert_enrollment_id => l_cert_enrollment_id
1492 ,p_certification_status_code => l_certification_status_code);
1493
1494 EXCEPTION
1495 when others then
1496 l_error_message := fnd_message.get;
1497 fnd_message.clear;
1498 l_error_message := nvl(l_error_message,'Error When creating Certification subscription ');
1499 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
1500 UPDATE ota_bulk_enr_req_members
1501 SET error_message = l_error_message, enrollment_status = NULL
1502 WHERE person_id = l_req_member_rec.person_id
1503 AND bulk_enr_request_id = p_enr_request_id;
1504 END;
1505
1506 IF l_cert_enrollment_id IS NOT NULL THEN
1507 FND_FILE.PUT_LINE(FND_FILE.LOG,'Subscription Status - '
1508 || ota_utility.get_lookup_meaning('OTA_CERT_ENROLL_STATUS',l_certification_status_code, 810));
1509 -- update lpe status to bulk_enr_req_members table
1510 UPDATE ota_bulk_enr_req_members
1511 SET enrollment_status = l_certification_status_code, error_message = NULL
1512 WHERE person_id = l_req_member_rec.person_id
1513 AND bulk_enr_request_id = p_enr_request_id;
1514 END IF;
1515 END IF;
1516 END LOOP;
1517 IF p_from_conc_program THEN
1518 -- Start workflow and send a notification to the requestor
1519 notify_requestor(p_enr_request_id => p_enr_request_id);
1520 END IF;
1521
1522 END IF;
1523
1524 END mass_subscribe_to_cert;
1525
1526 /*PROCEDURE mass_subscribe_to_class(
1527 p_enr_request_id IN NUMBER
1528 ,p_from_conc_program IN boolean default false)
1529 IS
1530
1531 CURSOR csr_get_booking_status_id(l_booking_id NUMBER) IS
1532 SELECT btt.booking_status_type_id , btt.name booking_status
1533 FROM ota_delegate_bookings tdb, ota_booking_status_types_tl btt
1534 WHERE booking_id = l_booking_id
1535 and tdb.booking_status_type_id = btt.booking_status_type_id
1536 and btt.language = USERENV('LANG');
1537
1538 CURSOR csr_get_assignment_info(l_assignment_id NUMBER) IS
1539 SELECT paf.organization_id
1540 FROM per_all_assignments_f paf
1541 WHERE paf.assignment_id = l_assignment_id
1542 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date -- Bug#8357553
1543 and paf.assignment_type in ('E', 'A', 'C');
1544
1545 CURSOR csr_get_cost_center_info(l_assignment_id NUMBER) IS
1546 SELECT pcak.cost_allocation_keyflex_id
1547 FROM per_all_assignments_f assg,
1548 pay_cost_allocations_f pcaf,
1549 pay_cost_allocation_keyflex pcak
1550 WHERE assg.assignment_id = pcaf.assignment_id
1551 AND assg.assignment_id = l_assignment_id
1552 AND assg.Primary_flag = 'Y'
1553 AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
1554 AND pcak.enabled_flag = 'Y'
1555 AND sysdate between nvl(pcaf.effective_start_date,sysdate)
1556 and nvl(pcaf.effective_end_date,sysdate+1)
1557 AND trunc(sysdate) between nvl(assg.effective_start_date,trunc(sysdate))
1558 and nvl(assg.effective_end_date,trunc(sysdate+1));
1559
1560 l_error_message ota_bulk_enr_req_members.error_message%TYPE;
1561 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%TYPE;
1562 l_booking_status ota_booking_status_types_tl.name%TYPE;
1563
1564 l_request_rec csr_get_request_info%ROWTYPE;
1565 l_req_member_rec csr_get_request_members%ROWTYPE;
1566 l_booking_id ota_delegate_bookings.booking_id%TYPE;
1567
1568 l_assignment_info csr_get_assignment_info%ROWTYPE;
1569 l_cost_center_info csr_get_cost_center_info%ROWTYPE;
1570
1571 l_person_name per_all_people_f.full_name%TYPE;
1572
1573 BEGIN
1574 OPEN csr_get_request_info(p_enr_request_id);
1575 FETCH csr_get_request_info INTO l_request_rec;
1576 IF csr_get_request_info%NOTFOUND THEN
1577 -- Raise error that no request found
1578 CLOSE csr_get_request_info;
1579 ELSE
1580 CLOSE csr_get_request_info;
1581 FOR l_req_member_rec IN csr_get_request_members(p_enr_request_id) LOOP
1582 IF l_req_member_rec.enrollment_status IS NULL THEN
1583 l_booking_id := null;
1584 l_error_message := null;
1585
1586 OPEN csr_get_person_name(l_req_member_rec.person_id);
1587 FETCH csr_get_person_name INTO l_person_name;
1588 CLOSE csr_get_person_name;
1589 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1590
1591 OPEN csr_get_assignment_info(l_req_member_rec.assignment_id);
1592 FETCH csr_get_assignment_info INTO l_assignment_info;
1593 CLOSE csr_get_assignment_info;
1594
1595 OPEN csr_get_cost_center_info(l_req_member_rec.assignment_id);
1596 FETCH csr_get_cost_center_info INTO l_cost_center_info;
1597 CLOSE csr_get_cost_center_info;
1598
1599 BEGIN
1600 -- Call Process save enrollment
1601 Create_Enrollment_And_Finance(
1602 p_event_id => l_request_rec.object_id
1603 ,p_cost_centers => l_cost_center_info.cost_allocation_keyflex_id
1604 ,p_assignment_id => l_req_member_rec.assignment_id
1605 ,p_delegate_contact_id => null
1606 ,p_business_group_id_from => l_request_rec.business_group_id
1607 ,p_organization_id => l_assignment_info.organization_id
1608 ,p_person_id => l_req_member_rec.person_id
1609 ,p_booking_id => l_booking_id
1610 ,p_message_name => l_error_message
1611 ,p_override_prerequisites => 'Y');
1612 EXCEPTION
1613 WHEN OTHERS THEN
1614 l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating Enrollment ');
1615 UPDATE ota_bulk_enr_req_members
1616 SET error_message = l_error_message, enrollment_status = NULL
1617 WHERE person_id = l_req_member_rec.person_id
1618 AND bulk_enr_request_id = p_enr_request_id;
1619 END;
1620
1621 IF l_booking_id IS NOT NULL THEN
1622 -- update booking status type id to bulk_enr_req_members table
1623 OPEN csr_get_booking_status_id(l_booking_id);
1624 FETCH csr_get_booking_status_id INTO l_booking_status_type_id, l_booking_status;
1625 CLOSE csr_get_booking_status_id;
1626
1627 FND_FILE.PUT_LINE(FND_FILE.LOG,'Enrollment Status - ' || l_booking_status);
1628
1629 UPDATE ota_bulk_enr_req_members
1630 SET enrollment_status = l_booking_status_type_id, error_message = NULL
1631 WHERE person_id = l_req_member_rec.person_id
1632 AND assignment_id = l_req_member_rec.assignment_id
1633 AND bulk_enr_request_id = p_enr_request_id;
1634 ELSE
1635 l_error_message := nvl(substr(l_error_message,1,2000),'Error When creating Enrollment ');
1636 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
1637 UPDATE ota_bulk_enr_req_members
1638 SET error_message = l_error_message, enrollment_status = NULL
1639 WHERE person_id = l_req_member_rec.person_id
1640 AND bulk_enr_request_id = p_enr_request_id;
1641 END IF;
1642 END IF;
1643 END LOOP;
1644 IF p_from_conc_program THEN
1645 -- Start workflow and send a notification to the requestor
1646 notify_requestor(p_enr_request_id => p_enr_request_id);
1647 END IF;
1648 END IF;
1649 END mass_subscribe_to_class;
1650 */
1651
1652 /*
1653 Modified mass_subscribe_to class for enhanced lp functionality
1654 We can enroll into classes once lrnr is successfully enrolled into corresponding lp
1655 */
1656 PROCEDURE mass_subscribe_to_class(
1657 p_enr_request_id IN NUMBER
1658 ,p_from_conc_program IN boolean default false)
1659 IS
1660
1661 CURSOR csr_get_booking_status_id(l_booking_id NUMBER) IS
1662 SELECT btt.booking_status_type_id , btt.name booking_status
1663 FROM ota_delegate_bookings tdb, ota_booking_status_types_tl btt
1664 WHERE booking_id = l_booking_id
1665 and tdb.booking_status_type_id = btt.booking_status_type_id
1666 and btt.language = USERENV('LANG');
1667
1668 CURSOR csr_get_assignment_info(l_assignment_id NUMBER) IS
1669 SELECT paf.organization_id
1670 FROM per_all_assignments_f paf
1671 WHERE paf.assignment_id = l_assignment_id
1672 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date -- Bug#12327352
1673 and paf.assignment_type in ('E', 'A', 'C');
1674
1675 CURSOR csr_get_cost_center_info(l_assignment_id NUMBER) IS
1676 SELECT pcak.cost_allocation_keyflex_id
1677 FROM per_all_assignments_f assg,
1678 pay_cost_allocations_f pcaf,
1679 pay_cost_allocation_keyflex pcak
1680 WHERE assg.assignment_id = pcaf.assignment_id
1681 AND assg.assignment_id = l_assignment_id
1682 AND assg.Primary_flag = 'Y'
1683 AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
1684 AND pcak.enabled_flag = 'Y'
1685 AND sysdate between nvl(pcaf.effective_start_date,sysdate)
1686 and nvl(pcaf.effective_end_date,sysdate+1)
1687 AND trunc(sysdate) between nvl(assg.effective_start_date,trunc(sysdate))
1688 and nvl(assg.effective_end_date,trunc(sysdate+1));
1689
1690
1691 CURSOR csr_check_lp_enr_exists(l_person_id in NUMBER,l_lp_id in NUMBER) IS
1692 SELECT
1693 lpe.lp_enrollment_id
1694 from
1695 ota_lp_enrollments lpe
1696 where lpe.learning_path_id= l_lp_id
1697 AND lpe.person_id = l_person_id
1698 AND lpe.path_status_code <> 'CANCELLED';
1699
1700 CURSOR csr_lp_created_now(l_person_id in NUMBER,l_lp_id in NUMBER,l_conc_req_id in NUMBER) IS
1701 SELECT
1702 berm.bulk_enr_request_id
1703 from
1704 ota_bulk_enr_requests ber,
1705 ota_bulk_enr_req_members berm
1706 where berm.bulk_enr_request_id = ber.bulk_enr_request_id
1707 AND ber.conc_program_request_id = l_conc_req_id
1708 AND ber.object_id = l_lp_id
1709 AND berm.person_id = l_person_id
1710 AND berm.error_message is NULL;
1711
1712
1713 CURSOR csr_get_lp_member_to_upd(l_person_id in NUMBER,l_lp_id in NUMBER,l_event_id in NUMBER) IS
1714 SELECT
1715 lme.lp_member_enrollment_id
1716 from
1717 ota_lp_enrollments lpe,
1718 ota_lp_member_enrollments lme,
1719 OTA_LEARNING_PATH_MEMBERS lpm,
1720 ota_events evt
1721 where lpe.learning_path_id= l_lp_id
1722 AND lpe.person_id = l_person_id
1723 AND lpe.path_status_code <> 'CANCELLED'
1724 AND lme.lp_enrollment_id= lpe.lp_enrollment_id
1725 AND evt.event_id= l_event_id
1726 and evt.activity_version_id=lpm.activity_version_id
1727 AND lme.learning_path_member_id=lpm.learning_path_member_id
1728 AND lpe.learning_path_id= lpm.learning_path_id;
1729
1730 l_error_message ota_bulk_enr_req_members.error_message%TYPE;
1731 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%TYPE;
1732 l_booking_status ota_booking_status_types_tl.name%TYPE;
1733
1734 l_request_rec csr_get_request_info%ROWTYPE;
1735 l_req_member_rec csr_get_request_members%ROWTYPE;
1736 l_booking_id ota_delegate_bookings.booking_id%TYPE;
1737
1738 l_assignment_info csr_get_assignment_info%ROWTYPE;
1739 l_cost_center_info csr_get_cost_center_info%ROWTYPE;
1740
1741 l_person_name per_all_people_f.full_name%TYPE;
1742
1743 l_subscribed_to_lp boolean;
1744 lp_enr_exists_rec csr_check_lp_enr_exists%ROWTYPE;
1745 l_subscribed_now boolean;
1746 lp_created_in_this_concreq_rec csr_lp_created_now%ROWTYPE;
1747 lp_mem_enr_to_upd_rec csr_get_lp_member_to_upd%ROWTYPE;
1748
1749 l_parent_object_type ota_bulk_enr_requests.object_type%TYPE;
1750
1751 l_existing_booking_id ota_delegate_bookings.booking_id%TYPE;
1752 l_lp_name varchar2(80);
1753 l_class_name varchar2(80);
1754 l_override_prerequisites varchar2(1) := 'Y';
1755
1756
1757 BEGIN
1758 OPEN csr_get_request_info(p_enr_request_id);
1759 FETCH csr_get_request_info INTO l_request_rec;
1760 IF csr_get_request_info%NOTFOUND THEN
1761 -- Raise error that no request found
1762 CLOSE csr_get_request_info;
1763 ELSE
1764 CLOSE csr_get_request_info;
1765 FOR l_req_member_rec IN csr_get_request_members(p_enr_request_id) LOOP
1766 l_subscribed_to_lp :=false;
1767 l_subscribed_now := false;
1768 IF l_req_member_rec.enrollment_status IS NULL THEN
1769 l_booking_id := null;
1770 l_error_message := null;
1771
1772 OPEN csr_get_person_name(l_req_member_rec.person_id);
1773 FETCH csr_get_person_name INTO l_person_name;
1774 CLOSE csr_get_person_name;
1775 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1776
1777 OPEN csr_get_assignment_info(l_req_member_rec.assignment_id);
1778 FETCH csr_get_assignment_info INTO l_assignment_info;
1779 CLOSE csr_get_assignment_info;
1780
1781 OPEN csr_get_cost_center_info(l_req_member_rec.assignment_id);
1782 FETCH csr_get_cost_center_info INTO l_cost_center_info;
1783 CLOSE csr_get_cost_center_info;
1784
1785 if(l_request_rec.object_type = 'LPCL') then
1786 OPEN csr_check_lp_enr_exists(l_req_member_rec.person_id,l_request_rec.parent_object_id);
1787 FETCH csr_check_lp_enr_exists into lp_enr_exists_rec;
1788 if csr_check_lp_enr_exists%FOUND THEN
1789 --update error message in req members
1790 l_subscribed_to_lp:=true;
1791 end if;
1792
1793 close csr_check_lp_enr_exists ;
1794
1795 OPEN csr_lp_created_now(l_req_member_rec.person_id,l_request_rec.parent_object_id,l_request_rec.conc_program_request_id);
1796 FETCH csr_lp_created_now into lp_created_in_this_concreq_rec;
1797 if csr_lp_created_now%FOUND THEN
1798 l_subscribed_now:=true;
1799 end if;
1800
1801 close csr_lp_created_now ;
1802 end if;
1803
1804
1805 if ((l_request_rec.object_type = 'CL') OR (l_request_rec.object_type = 'LPCL' AND l_subscribed_to_lp AND l_subscribed_now)) then
1806 BEGIN
1807
1808 if l_request_rec.object_type = 'LPCL' then
1809 l_parent_object_type:= 'LP';
1810 l_override_prerequisites := 'N';
1811 else
1812 l_parent_object_type := NULL;
1813 l_override_prerequisites := 'Y';
1814 end if;
1815
1816 l_existing_booking_id := ota_tdb_bus.booking_id_for (NULL,
1817 l_assignment_info.organization_id,
1818 l_request_rec.object_id,
1819 l_req_member_rec.person_id);
1820
1821 if l_request_rec.object_type = 'LPCL' and l_existing_booking_id is NOT NULL then
1822 --check if a booking already exists,if so we need to show a different message
1823
1824 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1825 l_class_name := OTA_BULK_ENROLL_UTIL.get_object_name('CL', l_request_rec.object_id);
1826 l_lp_name := OTA_BULK_ENROLL_UTIL.get_object_name('LP', l_request_rec.parent_object_id);
1827
1828 fnd_message.set_name ('OTA','OTA_467145_LP_CLASS_ENROLLED');
1829 fnd_message.set_token('LEARNER_NAME',l_person_name);
1830 fnd_message.set_token('CLASS_NAME',l_class_name);
1831 fnd_message.set_token('LP_NAME',l_lp_name);
1832 l_error_message := fnd_message.get();
1833
1834
1835 -- l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating Enrollment ');
1836 UPDATE ota_bulk_enr_req_members
1837 SET error_message = l_error_message, enrollment_status = NULL
1838 WHERE person_id = l_req_member_rec.person_id
1839 AND bulk_enr_request_id = p_enr_request_id;
1840
1841 FOR lp_mem_enr_to_upd_rec IN csr_get_lp_member_to_upd(l_req_member_rec.person_id, l_request_rec.parent_object_id,l_request_rec.object_id) loop
1842
1843 UPDATE ota_lp_member_enrollments
1844 SET event_id= l_request_rec.object_id
1845 where lp_member_enrollment_id = lp_mem_enr_to_upd_rec.lp_member_enrollment_id;
1846
1847 end loop;
1848
1849
1850 else
1851 -- Call Process save enrollment
1852 --Need to modify this process to update event_id in lp_member_enrollments for an lp_member_enrollment based on personid,activity_version_id and lp_id
1853 --Write another program which perumal will call for lp and class is called
1854 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1855 Create_Enrollment_And_Finance(
1856 p_event_id => l_request_rec.object_id
1857 ,p_cost_centers => l_cost_center_info.cost_allocation_keyflex_id
1858 ,p_assignment_id => l_req_member_rec.assignment_id
1859 ,p_delegate_contact_id => null
1860 ,p_business_group_id_from => l_request_rec.business_group_id
1861 ,p_organization_id => l_assignment_info.organization_id
1862 ,p_person_id => l_req_member_rec.person_id
1863 ,p_booking_id => l_booking_id
1864 ,p_message_name => l_error_message
1865 ,p_override_prerequisites => l_override_prerequisites);
1866 -- ,p_parent_object_type => l_parent_object_type
1867 -- ,p_parent_object_id => l_request_rec.parent_object_id);
1868
1869 end if;--l_request_rec.object_type = 'LPCL' and l_existing_booking_id is NOT NULL then
1870 EXCEPTION
1871 WHEN OTHERS THEN
1872 l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating Enrollment ');
1873 UPDATE ota_bulk_enr_req_members
1874 SET error_message = l_error_message, enrollment_status = NULL
1875 WHERE person_id = l_req_member_rec.person_id
1876 AND bulk_enr_request_id = p_enr_request_id;
1877 END;
1878
1879 IF l_booking_id IS NOT NULL THEN
1880 -- update booking status type id to bulk_enr_req_members table
1881 OPEN csr_get_booking_status_id(l_booking_id);
1882 FETCH csr_get_booking_status_id INTO l_booking_status_type_id, l_booking_status;
1883 CLOSE csr_get_booking_status_id;
1884
1885 FND_FILE.PUT_LINE(FND_FILE.LOG,'Enrollment Status - ' || l_booking_status);
1886
1887 UPDATE ota_bulk_enr_req_members
1888 SET enrollment_status = l_booking_status_type_id, error_message = NULL
1889 WHERE person_id = l_req_member_rec.person_id
1890 AND assignment_id = l_req_member_rec.assignment_id
1891 AND bulk_enr_request_id = p_enr_request_id;
1892
1893
1894 FOR lp_mem_enr_to_upd_rec IN csr_get_lp_member_to_upd(l_req_member_rec.person_id, l_request_rec.parent_object_id,l_request_rec.object_id) loop
1895 UPDATE ota_lp_member_enrollments
1896 SET event_id= l_request_rec.object_id
1897 where lp_member_enrollment_id = lp_mem_enr_to_upd_rec.lp_member_enrollment_id;
1898
1899 end loop;
1900
1901 ELSE
1902 l_error_message := nvl(substr(l_error_message,1,2000),'Error When creating Enrollment ');
1903 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
1904 UPDATE ota_bulk_enr_req_members
1905 SET error_message = l_error_message, enrollment_status = NULL
1906 WHERE person_id = l_req_member_rec.person_id
1907 AND bulk_enr_request_id = p_enr_request_id;
1908 END IF;
1909
1910 END IF;--l_request_rec.object_type = 'CL') OR (l_request_rec.object_type = 'LPCL' AND l_subscribed_to_lp
1911
1912 END IF;--l_req_member_rec.enrollment_status
1913
1914 END LOOP;
1915
1916 IF p_from_conc_program THEN
1917 -- Start workflow and send a notification to the requestor
1918 notify_requestor(p_enr_request_id => p_enr_request_id);
1919 END IF;
1920 END IF;
1921 END mass_subscribe_to_class;
1922
1923 /*
1924 Modified submit_bulk_enrollmentsfor enhanced lp functionality
1925 Request tables will be populated with 1 record for LP(object_type=LP) and 'n' records for classes(object_type=LPCL)
1926 */
1927
1928 PROCEDURE submit_bulk_enrollments(
1929 p_enr_request_id IN NUMBER
1930 ,p_enr_request_id_end IN NUMBER DEFAULT NULL
1931 ,p_conc_request_id OUT NOCOPY NUMBER
1932 ,p_object_type OUT NOCOPY VARCHAR2
1933 ,p_object_name OUT NOCOPY VARCHAR2)
1934 IS
1935 l_threshold number;
1936 l_learners_selected NUMBER := 0;
1937 l_request_rec csr_get_request_info%ROWTYPE;
1938
1939 BEGIN
1940
1941 OPEN csr_get_request_info(p_enr_request_id);
1942 FETCH csr_get_request_info INTO l_request_rec;
1943 CLOSE csr_get_request_info;
1944
1945 --This is bulk enroll to class/lp/cert
1946 if(p_enr_request_id_end IS NULL and p_enr_request_id IS NOT NULL) then
1947
1948 l_threshold := FND_PROFILE.VALUE('OTA_MAX_ENR_PRC_ONLINE');
1949
1950 IF l_threshold IS NULL THEN l_threshold := 0; END IF;
1951 SELECT count(person_id)
1952 INTO l_learners_selected
1953 FROM ota_bulk_enr_req_members
1954 WHERE bulk_enr_request_id = p_enr_request_id;
1955
1956 IF l_threshold >= l_learners_selected THEN
1957 -- No concurrent processing required
1958 p_conc_request_id := -1;
1959
1960 IF l_request_rec.object_type = 'CL' THEN
1961 mass_subscribe_to_class(
1962 p_enr_request_id => p_enr_request_id
1963 ,p_from_conc_program => false);
1964 ELSIF l_request_rec.object_type = 'LP' THEN
1965 mass_subscribe_to_lp(
1966 p_enr_request_id => p_enr_request_id
1967 ,p_from_conc_program => false);
1968 ELSIF l_request_rec.object_type = 'CRT' THEN
1969 mass_subscribe_to_cert(
1970 p_enr_request_id => p_enr_request_id
1971 ,p_from_conc_program => false);
1972 END IF;
1973
1974 ELSE
1975 -- Concurrent processing needs to be done
1976 p_conc_request_id := FND_REQUEST.SUBMIT_REQUEST(
1977 application => 'OTA'
1978 , program => 'OTBLKENR'
1979 , argument1 => p_enr_request_id
1980 , argument2 => NULL);
1981 IF p_conc_request_id = 0 THEN
1982 -- Raise error submitting concurrent program
1983 null;
1984 END IF;
1985 END IF;
1986 UPDATE ota_bulk_enr_requests ber
1987 SET CONC_PROGRAM_REQUEST_ID = p_conc_request_id
1988 WHERE bulk_enr_request_id = p_enr_request_id;
1989
1990 p_object_type := ota_utility.get_lookup_meaning(
1991 'OTA_OBJECT_TYPE'
1992 ,l_request_rec.object_type
1993 ,810);
1994 p_object_name := get_object_name(l_request_rec.object_type, l_request_rec.object_id );
1995
1996 elsif (p_enr_request_id_end IS NOT NULL) then
1997 --This is mass subscribe to lp and class
1998 --launch a single conc program which iterates through request ids and perform
1999 --mass subscribe to lp and class
2000
2001 p_conc_request_id := FND_REQUEST.SUBMIT_REQUEST(
2002 application => 'OTA'
2003 , program => 'OTBLKENR'
2004 , argument1 => p_enr_request_id
2005 , argument2 => p_enr_request_id_end);
2006
2007 UPDATE ota_bulk_enr_requests ber
2008 SET CONC_PROGRAM_REQUEST_ID = p_conc_request_id
2009 WHERE bulk_enr_request_id between p_enr_request_id and p_enr_request_id_end;
2010
2011 p_object_type := ota_utility.get_lookup_meaning(
2012 'OTA_OBJECT_TYPE'
2013 ,l_request_rec.object_type
2014 ,810);
2015 p_object_name := get_object_name(l_request_rec.object_type, l_request_rec.object_id );
2016
2017
2018
2019
2020
2021 end if;
2022
2023 END submit_bulk_enrollments;
2024
2025
2026 /*
2027 Modified submit_bulk_enrollmentsfor enhanced lp functionality
2028 Request tables will be populated with 1 record for LP(object_type=LP) and 'n' records for classes(object_type=LPCL)
2029 */
2030
2031
2032 Procedure process_bulk_enrollments
2033 (ERRBUF OUT NOCOPY VARCHAR2,
2034 RETCODE OUT NOCOPY VARCHAR2
2035 ,p_enr_request_id IN NUMBER
2036 ,p_enr_request_id_end IN NUMBER DEFAULT NULL) as
2037
2038 l_completed boolean;
2039 l_enr_request_id number;
2040
2041 failure exception;
2042 l_proc varchar2(72) := g_package||' bulk_enroll';
2043
2044 l_object_name varchar2(80);
2045
2046 l_request_rec csr_get_request_info%ROWTYPE;
2047 a_request_rec csr_get_all_requests%ROWTYPE;
2048
2049 current_request_id NUMBER;
2050
2051
2052
2053
2054 BEGIN
2055
2056 --This is bulk enroll to class/lp/cert
2057 if(p_enr_request_id_end IS NULL and p_enr_request_id IS NOT NULL) then
2058
2059 OPEN csr_get_request_info(p_enr_request_id);
2060 FETCH csr_get_request_info INTO l_request_rec;
2061 l_object_name := OTA_BULK_ENROLL_UTIL.get_object_name(l_request_rec.object_type, l_request_rec.object_id);
2062
2063 IF csr_get_request_info%NOTFOUND THEN
2064 CLOSE csr_get_request_info;
2065 --fnd_concurrent.set_completion_status('ERROR');
2066 ELSIF l_request_rec.object_type = 'LP' THEN
2067 CLOSE csr_get_request_info;
2068
2069 FND_FILE.PUT_LINE(FND_FILE.LOG,'Executing Bulk Enrollment for the Learning Path - ' || l_object_name);
2070
2071 mass_subscribe_to_lp(
2072 p_enr_request_id => p_enr_request_id
2073 ,p_from_conc_program => true);
2074
2075 ELSIF l_request_rec.object_type = 'CL' THEN
2076 CLOSE csr_get_request_info;
2077
2078 FND_FILE.PUT_LINE(FND_FILE.LOG,'Executing Bulk Enrollment for the Class - ' || l_object_name);
2079 mass_subscribe_to_class(
2080 p_enr_request_id => p_enr_request_id
2081 ,p_from_conc_program => true);
2082
2083 ELSIF l_request_rec.object_type = 'CRT' THEN
2084 CLOSE csr_get_request_info;
2085
2086 FND_FILE.PUT_LINE(FND_FILE.LOG,'Executing Bulk Enrollment for the Certification - ' || l_object_name);
2087 mass_subscribe_to_cert(
2088 p_enr_request_id => p_enr_request_id
2089 ,p_from_conc_program => true);
2090 ELSE
2091 CLOSE csr_get_request_info;
2092 --fnd_concurrent.set_completion_status('ERROR');
2093 -- Raise error for unknown object type
2094 END IF;
2095
2096 commit;
2097
2098 /*moved for lp enh changes
2099 EXCEPTION
2100 when others then
2101 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
2102 ||','||SUBSTR(SQLERRM, 1, 500));*/
2103
2104
2105 elsif (p_enr_request_id_end IS NOT NULL) then
2106 --This is mass subscribe to lp and class
2107 --iterate through the request and call mass subscribe to lp and mass_subscribe_to_class(n times)
2108
2109 FOR a_request_rec in csr_get_all_requests(p_enr_request_id ,p_enr_request_id_end) loop
2110 current_request_id := a_request_rec.bulk_enr_request_id;
2111 OPEN csr_get_request_info(current_request_id);
2112 FETCH csr_get_request_info INTO l_request_rec;
2113
2114
2115 IF csr_get_request_info%NOTFOUND THEN
2116 CLOSE csr_get_request_info;
2117 --fnd_concurrent.set_completion_status('ERROR');
2118 ELSIF l_request_rec.object_type = 'LP' THEN
2119 CLOSE csr_get_request_info;
2120 l_object_name := OTA_BULK_ENROLL_UTIL.get_object_name(l_request_rec.object_type, l_request_rec.object_id);
2121 FND_FILE.PUT_LINE(FND_FILE.LOG,'Enrollments for the Learning Path - ' || l_object_name);
2122
2123 mass_subscribe_to_lp(
2124 p_enr_request_id => current_request_id
2125 ,p_from_conc_program => true);
2126
2127 COMMIT;
2128
2129 ELSIF l_request_rec.object_type = 'LPCL' THEN
2130 CLOSE csr_get_request_info;
2131 l_object_name := OTA_BULK_ENROLL_UTIL.get_object_name(l_request_rec.object_type, l_request_rec.object_id);
2132 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
2133 FND_FILE.PUT_LINE(FND_FILE.LOG,'Enrollments for the Class - ' || l_object_name);
2134 mass_subscribe_to_class(
2135 p_enr_request_id => current_request_id
2136 ,p_from_conc_program => true);
2137
2138
2139 COMMIT;
2140
2141 ELSE
2142 CLOSE csr_get_request_info;
2143 --fnd_concurrent.set_completion_status('ERROR');
2144 -- Raise error for unknown object type
2145 END IF;
2146
2147 end loop;
2148
2149
2150
2151
2152
2153 end if;
2154
2155 EXCEPTION
2156 when others then
2157 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
2158 ||','||SUBSTR(SQLERRM, 1, 500));
2159
2160 END process_bulk_enrollments;
2161
2162
2163 --Added for 12426494
2164 PROCEDURE mass_update_class_enrollments(
2165 p_enr_request_id IN NUMBER,
2166 p_book_stat_type_id IN NUMBER,
2167 p_status_change_comments IN VARCHAR2,
2168 p_successful_attendance IN VARCHAR2,
2169 p_failure_reason IN VARCHAR2,
2170 p_attendance_result IN VARCHAR2,
2171 p_event_id IN NUMBER,
2172 p_enrollment_justification_id IN NUMBER,
2173 p_enrollment_priority IN VARCHAR2,
2174 p_auth_person_id IN NUMBER,
2175 p_debug IN VARCHAR2 default 'N') IS
2176
2177
2178 CURSOR csr_get_booking_status_id(l_booking_id NUMBER) IS
2179 SELECT btt.booking_status_type_id , btt.name booking_status
2180 FROM ota_delegate_bookings tdb, ota_booking_status_types_tl btt
2181 WHERE booking_id = l_booking_id
2182 and tdb.booking_status_type_id = btt.booking_status_type_id
2183 and btt.language = USERENV('LANG');
2184
2185 CURSOR booking_csr (p_booking_id ota_delegate_bookings.booking_id%TYPE)
2186 IS
2187 SELECT b.object_version_number
2188 FROM ota_delegate_bookings b
2189 WHERE b.booking_id = p_booking_id;
2190
2191 CURSOR finance_csr (p_booking_id ota_finance_lines.booking_id%TYPE)
2192 IS
2193 SELECT fln.finance_line_id finance_line_id,
2194 fln.object_version_number object_version_number
2195 FROM ota_finance_lines fln
2196 WHERE fln.booking_id = p_booking_id;
2197
2198 l_error_message ota_bulk_enr_req_members.error_message%TYPE;
2199 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%TYPE;
2200 l_booking_status ota_booking_status_types_tl.name%TYPE;
2201 l_object_version_number ota_delegate_bookings.object_version_number%TYPE;
2202 l_request_rec csr_get_request_info%ROWTYPE;
2203 l_req_member_rec csr_get_request_members%ROWTYPE;
2204 l_booking_id ota_delegate_bookings.booking_id%TYPE;
2205 l_person_name per_all_people_f.full_name%TYPE;
2206
2207
2208 l_class_name varchar2(80);
2209
2210 v_finance_line_id number;
2211 v_object_version_number number;
2212 v_tfl_object_version_number number;
2213
2214 BEGIN
2215 OPEN csr_get_request_info(p_enr_request_id);
2216 FETCH csr_get_request_info INTO l_request_rec;
2217 IF csr_get_request_info%NOTFOUND THEN
2218 -- Raise error that no request found
2219 CLOSE csr_get_request_info;
2220 ELSE
2221 CLOSE csr_get_request_info;
2222 FOR l_req_member_rec IN csr_get_request_members(p_enr_request_id) LOOP
2223
2224 IF l_req_member_rec.booking_id IS NOT NULL THEN
2225 l_booking_id := l_req_member_rec.booking_id;
2226 l_error_message := null;
2227
2228 OPEN booking_csr(l_booking_id);
2229 FETCH booking_csr INTO l_object_version_number;
2230 CLOSE booking_csr;
2231 v_object_version_number := l_object_version_number;
2232
2233 OPEN finance_csr(l_booking_id);
2234 FETCH finance_csr INTO v_finance_line_id, v_tfl_object_version_number;
2235 CLOSE finance_csr;
2236
2237 OPEN csr_get_person_name(l_req_member_rec.person_id);
2238 FETCH csr_get_person_name INTO l_person_name;
2239 CLOSE csr_get_person_name;
2240 if(p_debug = 'Y') then
2241 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
2242 end if;
2243
2244
2245 if (l_request_rec.object_type = 'CLU') then
2246 BEGIN
2247
2248
2249 -- Call Process save enrollment
2250 --Need to modify this process to update event_id in lp_member_enrollments for an lp_member_enrollment based on personid,activity_version_id and lp_id
2251 --Write another program which perumal will call for lp and class is called
2252 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
2253 ota_tdb_api_upd2.update_enrollment(
2254 p_booking_id => l_booking_id,
2255 p_event_id => p_event_id,
2256 p_failure_reason => p_failure_reason,
2257 p_status_change_comments => p_status_change_comments,
2258 p_booking_status_type_id => p_book_stat_type_id,
2259 p_attendance_result => p_attendance_result,
2260 p_successful_attendance_flag => p_successful_attendance,
2261 p_booking_justification_id => p_enrollment_justification_id,
2262 p_booking_priority => p_enrollment_priority,
2263 p_authorizer_person_id => p_auth_person_id,
2264 p_object_version_number => l_object_version_number,
2265 p_tfl_object_version_number => v_tfl_object_version_number,
2266 p_finance_line_id => v_finance_line_id);
2267
2268
2269 EXCEPTION
2270 WHEN OTHERS THEN
2271 l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating Enrollment ');
2272 UPDATE ota_bulk_enr_req_members
2273 SET error_message = l_error_message
2274 WHERE person_id = l_req_member_rec.person_id
2275 AND bulk_enr_request_id = p_enr_request_id;
2276 END;
2277
2278 IF l_object_version_number<>v_object_version_number THEN
2279 -- update booking status type id to bulk_enr_req_members table
2280 OPEN csr_get_booking_status_id(l_booking_id);
2281 FETCH csr_get_booking_status_id INTO l_booking_status_type_id, l_booking_status;
2282 CLOSE csr_get_booking_status_id;
2283 if(p_debug = 'Y') then
2284 FND_FILE.PUT_LINE(FND_FILE.LOG,'Enrollment Status - ' || l_booking_status);
2285 end if;
2286
2287 UPDATE ota_bulk_enr_req_members
2288 SET enrollment_status = l_booking_status_type_id, error_message = NULL
2289 WHERE person_id = l_req_member_rec.person_id
2290 AND assignment_id = l_req_member_rec.assignment_id
2291 AND bulk_enr_request_id = p_enr_request_id;
2292
2293 ELSE
2294 --l_error_message := nvl(substr(l_error_message,1,2000),'Error When creating Enrollment ');
2295 --FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
2296 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
2297 l_error_message := fnd_message.get;
2298 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
2299 UPDATE ota_bulk_enr_req_members
2300 SET error_message = l_error_message
2301 WHERE person_id = l_req_member_rec.person_id
2302 AND bulk_enr_request_id = p_enr_request_id;
2303 END IF;
2304
2305 END IF;--l_request_rec.object_type = 'CLU'
2306
2307 END IF;--l_req_member_rec.booking_id
2308
2309 END LOOP;
2310
2311 -- Start workflow and send a notification to the requestor
2312 notify_requestor(p_enr_request_id => p_enr_request_id,
2313 p_process_name => 'OTA_BLK_ENR_UPD_NTF_PRC');
2314
2315 END IF;
2316 END mass_update_class_enrollments;
2317
2318 --Added for 12426494
2319 PROCEDURE submit_bulk_enrollment_updates(
2320 p_enr_request_id IN NUMBER
2321 ,p_book_stat_type_id IN NUMBER
2322 ,p_status_change_comments IN varchar2
2323 ,p_successful_attendance IN varchar2
2324 ,p_failure_reason IN varchar2
2325 ,p_attendance_result IN varchar2
2326 ,p_event_id IN number
2327 ,p_enrollment_justification_id number
2328 ,p_enrollment_priority varchar2
2329 ,p_auth_person_id number
2330 ,p_conc_request_id OUT NOCOPY NUMBER)
2331 IS
2332
2333 l_request_rec csr_get_request_info%ROWTYPE;
2334
2335 BEGIN
2336
2337 OPEN csr_get_request_info(p_enr_request_id);
2338 FETCH csr_get_request_info INTO l_request_rec;
2339 CLOSE csr_get_request_info;
2340
2341 --This is bulk enroll to class/lp/cert
2342 if(p_enr_request_id IS NOT NULL) then
2343
2344
2345 -- Concurrent processing needs to be done
2346 p_conc_request_id := FND_REQUEST.SUBMIT_REQUEST(
2347 application => 'OTA'
2348 , program => 'OTBLKENRUPD'
2349 , argument1 => p_enr_request_id
2350 , argument2 => p_book_stat_type_id
2351 , argument3 => p_status_change_comments
2352 , argument4 => p_successful_attendance
2353 , argument5 => p_failure_reason
2354 , argument6 => p_attendance_result
2355 , argument7 => p_event_id
2356 , argument8 => p_enrollment_justification_id
2357 , argument9 => p_enrollment_priority
2358 , argument10 => p_auth_person_id);
2359 IF p_conc_request_id = 0 THEN
2360 -- Raise error submitting concurrent program
2361 null;
2362 END IF;
2363 UPDATE ota_bulk_enr_requests ber
2364 SET CONC_PROGRAM_REQUEST_ID = p_conc_request_id
2365 WHERE bulk_enr_request_id = p_enr_request_id;
2366
2367
2368 end if;
2369
2370 END submit_bulk_enrollment_updates;
2371
2372
2373
2374 --Added for 12426494
2375 Procedure process_bulk_enr_upd
2376 (ERRBUF OUT NOCOPY VARCHAR2,
2377 RETCODE OUT NOCOPY VARCHAR2
2378 ,p_enr_request_id IN NUMBER
2379 ,p_book_stat_type_id IN NUMBER
2380 ,p_status_change_comments IN VARCHAR2
2381 ,p_successful_attendance IN VARCHAR2
2382 ,p_failure_reason IN VARCHAR2
2383 ,p_attendance_result IN VARCHAR2
2384 ,p_event_id IN NUMBER
2385 ,p_enrollment_justification_id IN NUMBER
2386 ,p_enrollment_priority IN VARCHAR2
2387 ,p_auth_person_id IN NUMBER
2388 ,p_debug IN VARCHAR2 default 'N') as
2389
2390
2391
2392 l_proc varchar2(72) := g_package||' bulk_enroll';
2393
2394 l_request_rec csr_get_request_info%ROWTYPE;
2395
2396 current_request_id NUMBER;
2397
2398 l_book_stat_type_id number := p_book_stat_type_id;
2399 l_satus_change_comments varchar2(240) := p_status_change_comments;
2400 l_successful_attendance varchar2(240) := p_successful_attendance;
2401 l_failure_reason varchar2(240) := p_failure_reason;
2402 l_attendance_result varchar2(240) := p_attendance_result;
2403 l_event_id number := p_event_id;
2404 l_enrollment_justification_id number := p_enrollment_justification_id;
2405 l_enrollment_priority varchar2(240) := p_enrollment_priority;
2406 l_auth_person_id number := p_auth_person_id;
2407
2408 cursor csr_con_req_info(l_request_id number) is
2409 select argument2, argument3, argument4, argument5,
2410 argument6, argument7, argument8, argument9, argument10
2411 from fnd_concurrent_requests
2412 where request_id = l_request_id;
2413
2414 BEGIN
2415
2416 if(p_enr_request_id IS NOT NULL) then
2417
2418 OPEN csr_get_request_info(p_enr_request_id);
2419 FETCH csr_get_request_info INTO l_request_rec;
2420 IF csr_get_request_info%NOTFOUND THEN
2421 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - Request not found with specified request id');
2422 CLOSE csr_get_request_info;
2423 --fnd_concurrent.set_completion_status('ERROR');
2424 ELSE
2425 CLOSE csr_get_request_info;
2426 if(l_request_rec.object_type='CLU') then
2427 if(p_book_stat_type_id is null) then
2428 open csr_con_req_info(l_request_rec.conc_program_request_id);
2429 fetch csr_con_req_info into l_book_stat_type_id,l_satus_change_comments,l_successful_attendance,l_failure_reason,l_attendance_result,l_event_id,l_enrollment_justification_id,l_enrollment_priority,l_auth_person_id;
2430 close csr_con_req_info;
2431 end if;
2432 mass_update_class_enrollments(p_enr_request_id => p_enr_request_id,
2433 p_book_stat_type_id => l_book_stat_type_id,
2434 p_status_change_comments => l_satus_change_comments,
2435 p_successful_attendance => l_successful_attendance,
2436 p_failure_reason => l_failure_reason,
2437 p_attendance_result => l_attendance_result,
2438 p_event_id => l_event_id,
2439 p_enrollment_justification_id => l_enrollment_justification_id,
2440 p_enrollment_priority => l_enrollment_priority,
2441 p_auth_person_id => l_auth_person_id,
2442 p_debug => p_debug);
2443 else
2444 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - This request cannot be run with this concurrent program');
2445 end if;
2446 --fnd_concurrent.set_completion_status('ERROR');
2447 -- Raise error for unknown object type
2448 END IF;
2449
2450 commit;
2451
2452
2453 end if;
2454
2455 EXCEPTION
2456 when others then
2457 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
2458 ||','||SUBSTR(SQLERRM, 1, 500));
2459
2460 END process_bulk_enr_upd;
2461 end ota_bulk_enroll_util;