[Home] [Help]
PACKAGE BODY: APPS.OTA_BULK_ENROLL_UTIL
Source
1 package body ota_bulk_enroll_util as
2 /* $Header: otblkenr.pkb 120.12.12010000.4 2008/08/22 06:28:57 pvelugul 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 = 'CL' 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 = 'CL' 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 <> 'CL' 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 = 'CL' 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 IF l_event_status in ('F') THEN
775
776 l_booking_status_row := ota_learner_enroll_ss.Get_Booking_Status_for_web
777 (p_web_booking_status_type => 'WAITLISTED'
778 ,p_business_group_id => ota_general.get_business_group_id);
779
780 l_booking_status_used := 'WAITLISTED';
781
782 ELSIF l_event_status in ('P') THEN
783
784 l_booking_status_row := ota_learner_enroll_ss.Get_Booking_Status_for_web
785 (p_web_booking_status_type => 'REQUESTED'
786 ,p_business_group_id => ota_general.get_business_group_id);
787
788 l_booking_status_used := 'REQUESTED';
789
790 ELSIF l_event_status = 'N' THEN
791
792 IF l_maximum_internal_attendees is null then
793 l_booking_status_row := ota_learner_enroll_ss.Get_Booking_Status_for_web
794 (p_web_booking_status_type => 'PLACED'
795 ,p_business_group_id => ota_general.get_business_group_id);
796
797 l_booking_status_used := 'PLACED';
798
799 ELSE
800
801 IF l_maximum_internal_allowed > 0 OR l_maximum_external_allowed > 0 THEN
802 l_booking_status_row := ota_learner_enroll_ss.Get_Booking_Status_for_web
803 (p_web_booking_status_type => 'PLACED'
804 ,p_business_group_id => ota_general.get_business_group_id);
805
806 l_booking_status_used := 'PLACED';
807
808 ELSIF l_maximum_internal_allowed <= 0 OR l_maximum_external_allowed <= 0 THEN
809 l_booking_status_row := ota_learner_enroll_ss.Get_Booking_Status_for_web
810 (p_web_booking_status_type => 'WAITLISTED'
811 ,p_business_group_id => ota_general.get_business_group_id);
812
813 l_booking_status_used := 'WAITLISTED';
814
815 END IF;
816 END IF;
817 IF l_booking_status_row.booking_Status_type_id is null then
818 fnd_message.set_name ('OTA','OTA_13667_WEB_STATUS_NOT_SEEDE');
819 RAISE ota_learner_enroll_ss.g_mesg_on_stack_exception ;
820 END IF ;
821 END IF;
822
823 EXCEPTION
824 WHEN ota_learner_enroll_ss.g_mesg_on_stack_exception THEN
825 --
826 -- Store the technical message which will have been seeded
827 -- if this exception has been raised. This will be used to provide
828 -- the code.
829 --
830 hr_message.provide_error;
831 --
832 -- Now distinguish which error was raised.
833 --
834 IF (hr_message.last_message_name = 'OTA_13667_WEB_STATUS_NOT_SEEDE') THEN
835 --
836 -- Seed the user friendly message
837 --
838 fnd_message.set_name ('OTA','OTA_WEB_INCORRECT_CONF');
839 --
840 -- Raise the error for the main procedure exception handler
841 -- to handle
842 --
843 p_message_name := hr_message.last_message_name;
844 p_message_name := SUBSTR(SQLERRM, 1,300);
845 --
846 ELSIF (hr_message.last_message_name = 'HR_51396_WEB_PERSON_NOT_FND') THEN
847 --
848 -- Seed the user friendly message
849 --
850 fnd_message.set_name ('OTA','OTA_NO_DELEGATE_INFORMATION');
851 --
852 -- Raise the error for the main procedure exception handler
853 -- to handle
854 p_message_name := 'OTA_NO_DELEGATE_INFORMATION';
855 p_message_name := SUBSTR(SQLERRM, 1,300);
856 --
857 --
858 ELSE
859 -- Raise the error for the main procedure exception handler
860 -- to handle
861 p_message_name := hr_message.get_message_text;
862
863 --
864 END IF;
865 --
866 WHEN OTHERS THEN
867 --
868 -- Can't store a technical message, as we don't know what it is
869 -- and a message may not have been put on the stack
870 --
871 hr_message.provide_error;
872 --
873 -- Seed the user friendly message
874 --
875 fnd_message.set_name ('OTA','OTA_WEB_ERR_GETTING_INFO');
876 --
877 --
878 -- Raise the error for the main procedure exception handler
879 -- to handle
880 --
881 p_message_name := SUBSTR(SQLERRM, 1,300);
882
883 END ;
884 --
885 -- ----------------------------------------------------------------------
886 -- Save
887 -- ----------------------------------------------------------------------
888 -- If there are no errors, save to the database
889 -- (there shouldn't be as the main exception handler will be used
890 --
891 IF p_message_name is null then
892
893
894 BEGIN
895 --
896 -- Check to see if delegate has a booking status of CANCELLED for
897 -- this event, if cancelled l_cancel_boolean is set to true
898 -- FIX for bug 900679
899 --
900 l_cancel_boolean := ota_learner_enroll_ss.Chk_Event_Cancelled_for_Person(
901 p_event_id => p_event_id
902 ,p_delegate_person_id => l_delegate_id
903 ,p_delegate_contact_id => p_delegate_contact_id
904 ,p_booking_id => l_booking_id);
905
906 l_auto_create_finance := FND_PROFILE.value('OTA_AUTO_CREATE_FINANCE');
907 l_automatic_transfer_gl := FND_PROFILE.value('OTA_SSHR_AUTO_GL_TRANSFER');
908 l_user := FND_PROFILE.value('USER_ID');
909
910 IF (l_cancel_boolean) THEN
911 --
912 -- Delegate has a Cancelled status for this event, hence
913 -- we must update the existing record by changing Cancelled
914 -- to Requested status
915 --
916
917 l_object_version_number := OTA_LEARNER_ENROLL_SS.Get_Booking_OVN (p_booking_id => l_booking_id);
918
919 /* Call Cancel procedure to cancel the Finance if person Re-enroll */
920 ota_learner_enroll_ss.cancel_finance(l_booking_id);
921
922
923 -- ----------------------------------------------------------------
924 -- Delegate has no record for this event, hence create a record
925 -- with requested status
926 -- ----------------------------------------------------------------
927 -- Check if the Profile AutoCreate Finance is ON or OFF
928 -- ----------------------------------------------------------------
929 END IF;
930 open c_get_price_basis;
931 fetch c_get_price_basis into l_price_basis;
932 close c_get_price_basis;
933
934
935 IF l_delegate_id IS NOT NULL
936 AND l_auto_create_finance = 'Y'
937 and l_price_basis <> 'N'
938 and l_event_currency_code is not null THEN
939
940 l_cost_allocation_keyflex_id := TO_NUMBER(p_cost_centers);
941 result_finance_header_id := fapi_finance_header_id;
942 result_object_version_number := l_object_version_number;
943
944 ota_crt_finance_segment.Create_Segment(
945 p_assignment_id => p_assignment_id,
946 p_business_group_id_from => p_business_group_id_from,
947 p_business_group_id_to => l_business_group_id_to,
948 p_organization_id => p_organization_id,
949 p_sponsor_organization_id => l_sponsor_organization_id,
950 p_event_id => p_event_id,
951 p_person_id => l_delegate_id,
952 p_currency_code => l_event_currency_code,
953 p_cost_allocation_keyflex_id=> l_cost_allocation_keyflex_id,
954 p_user_id => l_user,
955 p_finance_header_id => fapi_finance_header_id,
956 p_object_version_number => fapi_object_version_number,
957 p_result => fapi_result,
958 p_from_result => fapi_from,
959 p_to_result => fapi_to );
960
961 IF fapi_result = 'S' THEN
962 result_object_version_number := fapi_object_version_number;
963 result_finance_header_id := fapi_finance_header_id;
964
965 ELSIF fapi_result = 'E' THEN
966 result_object_version_number := l_object_version_number;
967 result_finance_header_id := NULL;
968 result_create_finance_line := NULL;
969 END IF;
970
971 ota_tdb_api_ins2.Create_Enrollment(
972 p_booking_id => l_booking_id
973 ,p_booking_status_type_id => l_booking_status_row.booking_status_type_id
974 ,p_delegate_person_id => l_delegate_id
975 ,p_delegate_contact_id => null
976 ,p_contact_id => null
977 ,p_business_group_id => ota_general.get_business_group_id
978 ,p_event_id => p_event_id
979 -- ,p_date_booking_placed => trunc(sysdate)
980 ,p_date_booking_placed => sysdate
981 ,p_corespondent => l_corespondent
982 ,p_internal_booking_flag => l_internal_booking_flag
983 ,p_person_address_type => l_person_address_type
984 ,p_number_of_places => 1
985 ,p_object_version_number => result_object_version_number
986 ,p_delegate_contact_phone => l_work_telephone
987 ,p_delegate_contact_fax => l_work_fax
988 ,p_source_of_booking => l_source_of_booking
989 ,p_special_booking_instructions => p_extra_information
990 ,p_successful_attendance_flag => 'N'
991 ,p_finance_header_id => result_finance_header_id
992 ,p_create_finance_line => result_create_finance_line
993 ,p_finance_line_id => l_finance_line_id
994 ,p_enrollment_type => l_enrollment_type
995 ,p_validate => FALSE
996 ,p_currency_code => l_event_currency_code
997 ,p_organization_id => l_organization_id
998 ,p_delegate_assignment_id => l_assignment_id
999 ,p_delegate_contact_email => l_email_address
1000 -- Modified for bug#5478206
1001 ,p_tdb_information_category => l_attribute_category
1002 ,p_tdb_information1 => l_attribute1
1003 ,p_tdb_information2 => l_attribute2
1004 ,p_tdb_information3 => l_attribute3
1005 ,p_tdb_information4 => l_attribute4
1006 ,p_tdb_information5 => l_attribute5
1007 ,p_tdb_information6 => l_attribute6
1008 ,p_tdb_information7 => l_attribute7
1009 ,p_tdb_information8 => l_attribute8
1010 ,p_tdb_information9 => l_attribute9
1011 ,p_tdb_information10 => l_attribute10
1012 ,p_tdb_information11 => l_attribute11
1013 ,p_tdb_information12 => l_attribute12
1014 ,p_tdb_information13 => l_attribute13
1015 ,p_tdb_information14 => l_attribute14
1016 ,p_tdb_information15 => l_attribute15
1017 ,p_tdb_information16 => l_attribute16
1018 ,p_tdb_information17 => l_attribute17
1019 ,p_tdb_information18 => l_attribute18
1020 ,p_tdb_information19 => l_attribute19
1021 ,p_tdb_information20 => l_attribute20
1022 ,p_booking_justification_id => p_booking_justification_id
1023 ,p_booking_priority => l_priority_level
1024 ,p_override_prerequisites => 'Y'
1025 ,p_override_learner_access => 'Y'
1026 ,p_is_mandatory_enrollment => p_is_mandatory_enrollment
1027 );
1028
1029
1030 IF l_automatic_transfer_gl = 'Y' AND l_finance_line_id IS NOT NULL AND l_offering_id is null THEN
1031
1032 UPDATE ota_finance_lines SET transfer_status = 'AT'
1033 WHERE finance_line_id = l_finance_line_id;
1034
1035
1036
1037 END IF;
1038
1039 ELSE
1040
1041 ota_tdb_api_ins2.Create_Enrollment(p_booking_id => l_booking_id
1042 ,p_booking_status_type_id => l_booking_status_row.booking_status_type_id
1043 ,p_delegate_person_id => l_delegate_id
1044 ,p_delegate_contact_id => p_delegate_contact_id
1045 ,p_customer_id => l_customer_id
1046 ,p_contact_id => null
1047 ,p_business_group_id => ota_general.get_business_group_id
1048 ,p_event_id => p_event_id
1049 -- ,p_date_booking_placed => trunc(sysdate)
1050 ,p_date_booking_placed => sysdate
1051 ,p_corespondent => l_corespondent
1052 ,p_internal_booking_flag => l_internal_booking_flag
1053 ,p_person_address_type => l_person_address_type
1054 ,p_number_of_places => 1
1055 ,p_object_version_number => l_object_version_number
1056 ,p_delegate_contact_phone => l_work_telephone
1057 ,p_delegate_contact_fax => l_work_fax
1058 ,p_source_of_booking => l_source_of_booking
1059 ,p_special_booking_instructions => p_extra_information
1060 ,p_successful_attendance_flag => 'N'
1061 ,p_finance_line_id => l_finance_line_id
1062 ,p_enrollment_type => l_enrollment_type
1063 ,p_validate => FALSE
1064 ,p_organization_id => l_organization_id
1065 ,p_delegate_assignment_id => l_assignment_id
1066 ,p_delegate_contact_email => l_email_address
1067 -- Modified for bug#5478206
1068 ,p_tdb_information_category => l_attribute_category
1069 ,p_tdb_information1 => l_attribute1
1070 ,p_tdb_information2 => l_attribute2
1071 ,p_tdb_information3 => l_attribute3
1072 ,p_tdb_information4 => l_attribute4
1073 ,p_tdb_information5 => l_attribute5
1074 ,p_tdb_information6 => l_attribute6
1075 ,p_tdb_information7 => l_attribute7
1076 ,p_tdb_information8 => l_attribute8
1077 ,p_tdb_information9 => l_attribute9
1078 ,p_tdb_information10 => l_attribute10
1079 ,p_tdb_information11 => l_attribute11
1080 ,p_tdb_information12 => l_attribute12
1081 ,p_tdb_information13 => l_attribute13
1082 ,p_tdb_information14 => l_attribute14
1083 ,p_tdb_information15 => l_attribute15
1084 ,p_tdb_information16 => l_attribute16
1085 ,p_tdb_information17 => l_attribute17
1086 ,p_tdb_information18 => l_attribute18
1087 ,p_tdb_information19 => l_attribute19
1088 ,p_tdb_information20 => l_attribute20
1089 ,p_booking_justification_id => p_booking_justification_id
1090 ,p_booking_priority => l_priority_level
1091 ,p_override_prerequisites => 'Y'
1092 ,p_override_learner_access => 'Y'
1093 ,p_is_mandatory_enrollment => p_is_mandatory_enrollment
1094 );
1095
1096
1097 END IF;
1098 p_booking_id := l_booking_id;
1099
1100 IF l_booking_id is not null then
1101
1102 IF l_booking_status_used = 'PLACED' then
1103 p_message_name := 'OTA_443526_CONFIRMED_PLACED';
1104 ELSIF l_booking_status_used = 'WAITLISTED' then
1105 p_message_name := 'OTA_443527_CONFIRMED_WAITLIST';
1106 ELSIF l_booking_status_used = 'REQUESTED' then
1107 p_message_name := 'OTA_443528_CONFIRMED_REQUESTED';
1108 END IF;
1109 END IF;
1110
1111 EXCEPTION
1112 WHEN OTHERS THEN
1113 -- Both the Confirm Procedure and the API return APP-20002 or -20001
1114 -- so provide error can be used, as if the confirm procedure errors
1115 -- a different tool bar will be used.
1116 -- If the API has errored, the WF won't have been activated
1117 -- whereas if the confirm procedure errored, then it probably will have
1118 -- been.
1119 -- p_mode will be changed to indicate an error and,if it's a WF error
1120 -- the mode will also indicate this.
1121 -- Then the "Confirmation" page will be called from the main handler.
1122 --
1123 -- It is OK to use hr_message.provide_error as an application
1124 -- error will have been raised which will have put an error onto
1125 -- the stack
1126 --
1127 p_message_name := fnd_message.get;
1128 --
1129 END; -- End of if p_message is not null
1130
1131 END IF;
1132 EXCEPTION
1133 WHEN OTHERS THEN
1134 p_message_name := SUBSTR(SQLERRM, 1,300);
1135 END Create_Enrollment_And_Finance;
1136
1137
1138 FUNCTION get_object_name(p_object_type IN VARCHAR2, p_object_id IN NUMBER)
1139 RETURN VARCHAR2 IS
1140 CURSOR csr_get_lp_name IS
1141 SELECT name
1142 FROM ota_learning_paths_tl
1143 WHERE learning_path_id = p_object_id
1144 AND language = USERENV('LANG');
1145
1146 CURSOR csr_get_cert_name IS
1147 SELECT name
1148 FROM ota_certifications_tl
1149 WHERE certification_id = p_object_id
1150 AND language = USERENV('LANG');
1151
1152 CURSOR csr_get_class_name IS
1153 SELECT title
1154 FROM ota_events_tl
1155 WHERE event_id = p_object_id
1156 AND language = USERENV('LANG');
1157
1158 l_class_name OTA_EVENTS_TL.TITLE%TYPE := NULL;
1159 l_lp_name OTA_LEARNING_PATHS_TL.NAME%TYPE := NULL;
1160 l_cert_name OTA_CERTIFICATIONS_TL.NAME%TYPE := NULL;
1161 BEGIN
1162 IF p_object_type = 'CL' THEN
1163 OPEN csr_get_class_name;
1164 FETCH csr_get_class_name INTO l_class_name;
1165 CLOSE csr_get_class_name;
1166 RETURN l_class_name;
1167
1168 ELSIF p_object_type = 'LP' THEN
1169 OPEN csr_get_lp_name;
1170 FETCH csr_get_lp_name INTO l_lp_name;
1171 CLOSE csr_get_lp_name;
1172 RETURN l_lp_name;
1173
1174 ELSIF p_object_type = 'CRT' THEN
1175 OPEN csr_get_cert_name;
1176 FETCH csr_get_cert_name INTO l_cert_name;
1177 CLOSE csr_get_cert_name;
1178 RETURN l_cert_name;
1179 END IF;
1180 RETURN NULL;
1181 END get_object_name;
1182
1183 PROCEDURE delete_bulk_enr_request
1184 (itemtype IN WF_ITEMS.ITEM_TYPE%TYPE
1185 ,itemkey IN WF_ITEMS.ITEM_KEY%TYPE
1186 ,actid IN NUMBER
1187 ,funcmode IN VARCHAR2
1188 ,resultout OUT nocopy VARCHAR2 ) AS
1189
1190 l_blk_enr_request_id OTA_BULK_ENR_REQUESTS.BULK_ENR_REQUEST_ID%TYPE;
1191 BEGIN
1192 IF (funcmode='RUN') THEN
1193 l_blk_enr_request_id := WF_ENGINE.getitemattrtext(itemtype => itemtype,
1194 itemkey => itemkey,
1195 aname =>'BLK_ENR_REQUEST_ID',
1196 ignore_notfound => true);
1197
1198 DELETE FROM OTA_BULK_ENR_REQ_MEMBERS
1199 WHERE BULK_ENR_REQUEST_ID = l_blk_enr_request_id;
1200
1201 DELETE FROM OTA_BULK_ENR_REQUESTS
1202 WHERE BULK_ENR_REQUEST_ID = l_blk_enr_request_id;
1203
1204 COMMIT;
1205
1206 resultout := 'COMPLETE';
1207 ELSE IF (funcmode='CANCEL') THEN
1208 resultout := 'COMPLETE';
1209 END IF;
1210 END IF;
1211 END delete_bulk_enr_request;
1212
1213
1214
1215 PROCEDURE notify_requestor(p_enr_request_id IN NUMBER)
1216 IS
1217 l_proc varchar2(72) := g_package||'create_wf_process';
1218 l_process wf_activities.name%type :='OTA_BLK_ENR_NTF_PRC';
1219 l_item_type wf_items.item_type%type := 'OTWF';
1220 l_item_key wf_items.item_key%type;
1221
1222 l_user_name varchar2(80);
1223 l_person_id per_all_people_f.person_id%type;
1224
1225 l_process_display_name varchar2(240);
1226 l_request_rec csr_get_request_info%ROWTYPE;
1227
1228
1229 Cursor get_display_name is
1230 SELECT wrpv.display_name displayName
1231 FROM wf_runnable_processes_v wrpv
1232 WHERE wrpv.item_type = l_item_type
1233 AND wrpv.process_name = l_process;
1234
1235
1236 CURSOR csr_get_user_name(p_person_id IN VARCHAR2) IS
1237 SELECT user_name
1238 FROM fnd_user
1239 WHERE employee_id=p_person_id;
1240
1241
1242 CURSOR csr_get_person_name(p_person_id IN number) IS
1243 SELECT ppf.full_name
1244 FROM per_all_people_f ppf
1245 WHERE person_id = p_person_id;
1246
1247 CURSOR csr_get_error_learners IS
1248 SELECT COUNT(person_id)
1249 FROM ota_bulk_enr_req_members
1250 WHERE bulk_enr_request_id = p_enr_request_id
1251 and error_message IS NOT NULL;
1252
1253 CURSOR csr_get_selected_learners IS
1254 SELECT COUNT(person_id)
1255 FROM ota_bulk_enr_req_members
1256 WHERE bulk_enr_request_id = p_enr_request_id;
1257
1258 CURSOR csr_get_successful_learners IS
1259 SELECT COUNT(person_id)
1260 FROM ota_bulk_enr_req_members
1261 WHERE bulk_enr_request_id = p_enr_request_id
1262 and enrollment_status IS NOT NULL;
1263
1264 l_object_name VARCHAR2(240);
1265 l_person_full_name per_all_people_f.FULL_NAME%TYPE;
1266 l_error_learners NUMBER := 0;
1267 l_success_learners NUMBER := 0;
1268 l_selected_learners NUMBER := 0;
1269
1270 l_role_name wf_roles.name%type;
1271 l_role_display_name wf_roles.display_name%type;
1272
1273 BEGIN
1274 hr_utility.set_location('Entering:'||l_proc, 5);
1275
1276
1277 OPEN get_display_name;
1278 FETCH get_display_name INTO l_process_display_name;
1279 CLOSE get_display_name;
1280
1281 OPEN csr_get_request_info(p_enr_request_id);
1282 FETCH csr_get_request_info INTO l_request_rec;
1283 CLOSE csr_get_request_info;
1284
1285 l_object_name := OTA_BULK_ENROLL_UTIL.get_object_name(l_request_rec.object_type, l_request_rec.object_id);
1286
1287 -- Get the next item key from the sequence
1288 select hr_workflow_item_key_s.nextval
1289 into l_item_key
1290 from sys.dual;
1291
1292 WF_ENGINE.CREATEPROCESS(l_item_type, l_item_key, l_process);
1293
1294 l_person_id := l_request_rec.requestor_id;
1295
1296 wf_engine.additemattr
1297 (itemtype => l_item_type
1298 ,itemkey => l_item_key
1299 ,aname => 'BLK_ENR_REQUEST_ID');
1300
1301 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'BLK_ENR_REQUEST_ID',p_enr_request_id);
1302 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'CONC_REQUEST_ID',l_request_rec.conc_program_request_id);
1303 --Enh 5606090: Language support for Bulk enrollment.
1304 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'OBJECT_NAME',l_request_rec.object_id);
1305 WF_ENGINE.setitemattrtext(
1306 l_item_type
1307 ,l_item_key
1308 ,'OBJECT_TYPE'
1309 ,l_request_rec.object_type);
1310 OPEN csr_get_error_learners;
1311 FETCH csr_get_error_learners INTO l_error_learners;
1312 CLOSE csr_get_error_learners;
1313
1314 OPEN csr_get_selected_learners;
1315 FETCH csr_get_selected_learners INTO l_selected_learners;
1316 CLOSE csr_get_selected_learners;
1317
1318 OPEN csr_get_successful_learners;
1319 FETCH csr_get_successful_learners INTO l_success_learners;
1320 CLOSE csr_get_successful_learners;
1321
1322 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'TOTAL_NUMBER',l_selected_learners);
1323 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'ERROR_NUMBER',l_error_learners);
1324 WF_ENGINE.setitemattrnumber(l_item_type,l_item_key,'SUCCESS_NUMBER',l_success_learners);
1325
1326 IF l_person_id IS NOT NULL THEN
1327 OPEN csr_get_person_name(l_person_id);
1328 FETCH csr_get_person_name INTO l_person_full_name;
1329 CLOSE csr_get_person_name;
1330
1331 SELECT user_name INTO l_user_name
1332 FROM fnd_user
1333 WHERE employee_id=l_person_id
1334 AND trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')) --Bug 5676892
1335 AND ROWNUM =1 ;
1336 fnd_file.put_line(FND_FILE.LOG,'Requestor Name ' ||l_person_full_name);
1337 IF l_person_full_name IS NOT NULL then
1338 WF_ENGINE.setitemattrtext(l_item_type,l_item_key,'EVENT_OWNER',l_user_name);
1339 END IF;
1340 END IF;
1341
1342 -- Get and set owner role
1343
1344 hr_utility.set_location('Before Getting Owner'||l_proc, 10);
1345
1346 WF_DIRECTORY.GetRoleName(p_orig_system =>'PER',
1347 p_orig_system_id => l_person_id,
1348 p_name =>l_role_name,
1349 p_display_name =>l_role_display_name);
1350
1351
1352 WF_ENGINE.SetItemOwner(itemtype => l_item_type,
1353 itemkey =>l_item_key,
1354 owner =>l_role_name);
1355
1356 hr_utility.set_location('After Setting Owner'||l_proc, 10);
1357
1358
1359 WF_ENGINE.STARTPROCESS(l_item_type,l_item_key);
1360
1361 hr_utility.set_location('leaving:'||l_proc, 20);
1362
1363 EXCEPTION
1364 WHEN OTHERS THEN
1365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1366 END notify_requestor;
1367
1368 PROCEDURE mass_subscribe_to_lp(
1369 p_enr_request_id IN NUMBER
1370 ,p_from_conc_program IN boolean default false)
1371 IS
1372
1373 l_request_rec csr_get_request_info%ROWTYPE;
1374 l_req_member_rec csr_get_request_members%ROWTYPE;
1375 l_error_message ota_bulk_enr_req_members.error_message%TYPE;
1376 l_person_name per_all_people_f.full_name%TYPE;
1377 l_lp_enrollment_id ota_lp_enrollments.lp_enrollment_id%TYPE;
1378 l_path_status_code ota_lp_enrollments.path_status_code%TYPE;
1379 l_return_status varchar2(30);
1380
1381
1382 BEGIN
1383 OPEN csr_get_request_info(p_enr_request_id);
1384 FETCH csr_get_request_info INTO l_request_rec;
1385 IF csr_get_request_info%NOTFOUND THEN
1386 CLOSE csr_get_request_info;
1387 -- Raise error that no request found
1388 ELSE
1389 CLOSE csr_get_request_info;
1390 FOR l_req_member_rec IN csr_get_request_members(p_enr_request_id) LOOP
1391
1392 IF l_req_member_rec.enrollment_status IS NULL THEN
1393 l_lp_enrollment_id := null;
1394 l_path_status_code := null;
1395 l_error_message := null;
1396
1397 begin
1398 OPEN csr_get_person_name(l_req_member_rec.person_id);
1399 FETCH csr_get_person_name INTO l_person_name;
1400 CLOSE csr_get_person_name;
1401 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1402
1403 -- call subscribe for each learning path
1404 ota_lp_enrollment_api.subscribe_to_learning_path(
1405 p_learning_path_id => l_request_rec.object_id
1406 ,p_person_id => l_req_member_rec.person_id
1407 ,p_enrollment_source_code => 'ADMIN'
1408 ,p_business_group_id => l_request_rec.business_group_id
1409 ,p_creator_person_id => l_request_rec.requestor_id
1410 ,p_lp_enrollment_id => l_lp_enrollment_id
1411 ,p_path_status_code => l_path_status_code);
1412 EXCEPTION
1413 when others then
1414
1415 l_error_message := fnd_message.get;
1416 fnd_message.clear;
1417 l_error_message := nvl(l_error_message,'Error When creating Learning Path subscription ');
1418 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
1419 UPDATE ota_bulk_enr_req_members
1420 SET error_message = l_error_message, enrollment_status = NULL
1421 WHERE person_id = l_req_member_rec.person_id
1422 AND bulk_enr_request_id = p_enr_request_id;
1423 END;
1424
1425 IF l_lp_enrollment_id IS NOT NULL THEN
1426 FND_FILE.PUT_LINE(FND_FILE.LOG,'Subscription Status - '
1427 || ota_utility.get_lookup_meaning('OTA_LEARNING_PATH_STATUS',l_path_status_code, 810));
1428 -- update lpe status to bulk_enr_req_members table
1429 UPDATE ota_bulk_enr_req_members
1430 SET enrollment_status = l_path_status_code, error_message = NULL
1431 WHERE person_id = l_req_member_rec.person_id
1432 AND bulk_enr_request_id = p_enr_request_id;
1433 END IF;
1434 END IF;
1435 END LOOP;
1436 IF p_from_conc_program THEN
1437 -- Start workflow and send a notification to the requestor
1438 notify_requestor(p_enr_request_id => p_enr_request_id);
1439 END IF;
1440
1441 END IF;
1442
1443 END mass_subscribe_to_lp;
1444
1445 PROCEDURE mass_subscribe_to_cert(
1446 p_enr_request_id IN NUMBER
1447 ,p_from_conc_program IN boolean default false)
1448 IS
1449
1450 l_request_rec csr_get_request_info%ROWTYPE;
1451 l_req_member_rec csr_get_request_members%ROWTYPE;
1452 l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%TYPE;
1453 l_error_message ota_bulk_enr_req_members.error_message%TYPE;
1454 l_person_name per_all_people_f.full_name%TYPE;
1455 l_certification_status_code ota_cert_enrollments.CERTIFICATION_STATUS_CODE%TYPE;
1456
1457
1458 BEGIN
1459 OPEN csr_get_request_info(p_enr_request_id);
1460 FETCH csr_get_request_info INTO l_request_rec;
1461 IF csr_get_request_info%NOTFOUND THEN
1462 -- Raise error that no request found
1463 CLOSE csr_get_request_info;
1464 ELSE
1465 CLOSE csr_get_request_info;
1466 FOR l_req_member_rec IN csr_get_request_members(p_enr_request_id) LOOP
1467 IF l_req_member_rec.enrollment_status IS NULL THEN
1468 l_cert_enrollment_id := null;
1469 l_certification_status_code := null;
1470 l_error_message := null;
1471
1472 begin
1473 OPEN csr_get_person_name(l_req_member_rec.person_id);
1474 FETCH csr_get_person_name INTO l_person_name;
1475 CLOSE csr_get_person_name;
1476 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1477
1478 -- call subscribe for each learning path
1479 OTA_CERT_ENROLLMENT_API.subscribe_to_certification(
1480 p_certification_id => l_request_rec.object_id
1481 ,p_person_id => l_req_member_rec.person_id
1482 ,p_business_group_id => l_request_rec.business_group_id
1483 ,p_approval_flag => 'N'
1484 ,p_is_history_flag => 'N'
1485 ,p_cert_enrollment_id => l_cert_enrollment_id
1486 ,p_certification_status_code => l_certification_status_code);
1487
1488 EXCEPTION
1489 when others then
1490 l_error_message := fnd_message.get;
1491 fnd_message.clear;
1492 l_error_message := nvl(l_error_message,'Error When creating Certification subscription ');
1493 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
1494 UPDATE ota_bulk_enr_req_members
1495 SET error_message = l_error_message, enrollment_status = NULL
1496 WHERE person_id = l_req_member_rec.person_id
1497 AND bulk_enr_request_id = p_enr_request_id;
1498 END;
1499
1500 IF l_cert_enrollment_id IS NOT NULL THEN
1501 FND_FILE.PUT_LINE(FND_FILE.LOG,'Subscription Status - '
1502 || ota_utility.get_lookup_meaning('OTA_CERT_ENROLL_STATUS',l_certification_status_code, 810));
1503 -- update lpe status to bulk_enr_req_members table
1504 UPDATE ota_bulk_enr_req_members
1505 SET enrollment_status = l_certification_status_code, error_message = NULL
1506 WHERE person_id = l_req_member_rec.person_id
1507 AND bulk_enr_request_id = p_enr_request_id;
1508 END IF;
1509 END IF;
1510 END LOOP;
1511 IF p_from_conc_program THEN
1512 -- Start workflow and send a notification to the requestor
1513 notify_requestor(p_enr_request_id => p_enr_request_id);
1514 END IF;
1515
1516 END IF;
1517
1518 END mass_subscribe_to_cert;
1519
1520 PROCEDURE mass_subscribe_to_class(
1521 p_enr_request_id IN NUMBER
1522 ,p_from_conc_program IN boolean default false)
1523 IS
1524
1525 CURSOR csr_get_booking_status_id(l_booking_id NUMBER) IS
1526 SELECT btt.booking_status_type_id , btt.name booking_status
1527 FROM ota_delegate_bookings tdb, ota_booking_status_types_tl btt
1528 WHERE booking_id = l_booking_id
1529 and tdb.booking_status_type_id = btt.booking_status_type_id
1530 and btt.language = USERENV('LANG');
1531
1532 CURSOR csr_get_assignment_info(l_assignment_id NUMBER) IS
1533 SELECT paf.organization_id
1534 FROM per_all_assignments_f paf
1535 WHERE paf.assignment_id = l_assignment_id;
1536
1537 CURSOR csr_get_cost_center_info(l_assignment_id NUMBER) IS
1538 SELECT pcak.cost_allocation_keyflex_id
1539 FROM per_all_assignments_f assg,
1540 pay_cost_allocations_f pcaf,
1541 pay_cost_allocation_keyflex pcak
1542 WHERE assg.assignment_id = pcaf.assignment_id
1543 AND assg.assignment_id = l_assignment_id
1544 AND assg.Primary_flag = 'Y'
1545 AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
1546 AND pcak.enabled_flag = 'Y'
1547 AND sysdate between nvl(pcaf.effective_start_date,sysdate)
1548 and nvl(pcaf.effective_end_date,sysdate+1)
1549 AND trunc(sysdate) between nvl(assg.effective_start_date,trunc(sysdate))
1550 and nvl(assg.effective_end_date,trunc(sysdate+1));
1551
1552 l_error_message ota_bulk_enr_req_members.error_message%TYPE;
1553 l_booking_status_type_id ota_booking_status_types.booking_status_type_id%TYPE;
1554 l_booking_status ota_booking_status_types_tl.name%TYPE;
1555
1556 l_request_rec csr_get_request_info%ROWTYPE;
1557 l_req_member_rec csr_get_request_members%ROWTYPE;
1558 l_booking_id ota_delegate_bookings.booking_id%TYPE;
1559
1560 l_assignment_info csr_get_assignment_info%ROWTYPE;
1561 l_cost_center_info csr_get_cost_center_info%ROWTYPE;
1562
1563 l_person_name per_all_people_f.full_name%TYPE;
1564
1565 BEGIN
1566 OPEN csr_get_request_info(p_enr_request_id);
1567 FETCH csr_get_request_info INTO l_request_rec;
1568 IF csr_get_request_info%NOTFOUND THEN
1569 -- Raise error that no request found
1570 CLOSE csr_get_request_info;
1571 ELSE
1572 CLOSE csr_get_request_info;
1573 FOR l_req_member_rec IN csr_get_request_members(p_enr_request_id) LOOP
1574 IF l_req_member_rec.enrollment_status IS NULL THEN
1575 l_booking_id := null;
1576 l_error_message := null;
1577
1578 OPEN csr_get_person_name(l_req_member_rec.person_id);
1579 FETCH csr_get_person_name INTO l_person_name;
1580 CLOSE csr_get_person_name;
1581 FND_FILE.PUT_LINE(FND_FILE.LOG,'Learner Name - ' || l_person_name);
1582
1583 OPEN csr_get_assignment_info(l_req_member_rec.assignment_id);
1584 FETCH csr_get_assignment_info INTO l_assignment_info;
1585 CLOSE csr_get_assignment_info;
1586
1587 OPEN csr_get_cost_center_info(l_req_member_rec.assignment_id);
1588 FETCH csr_get_cost_center_info INTO l_cost_center_info;
1589 CLOSE csr_get_cost_center_info;
1590
1591 BEGIN
1592 -- Call Process save enrollment
1593 Create_Enrollment_And_Finance(
1594 p_event_id => l_request_rec.object_id
1595 ,p_cost_centers => l_cost_center_info.cost_allocation_keyflex_id
1596 ,p_assignment_id => l_req_member_rec.assignment_id
1597 ,p_delegate_contact_id => null
1598 ,p_business_group_id_from => l_request_rec.business_group_id
1599 ,p_organization_id => l_assignment_info.organization_id
1600 ,p_person_id => l_req_member_rec.person_id
1601 ,p_booking_id => l_booking_id
1602 ,p_message_name => l_error_message
1603 ,p_override_prerequisites => 'Y');
1604 EXCEPTION
1605 WHEN OTHERS THEN
1606 l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating Enrollment ');
1607 UPDATE ota_bulk_enr_req_members
1608 SET error_message = l_error_message, enrollment_status = NULL
1609 WHERE person_id = l_req_member_rec.person_id
1610 AND bulk_enr_request_id = p_enr_request_id;
1611 END;
1612
1613 IF l_booking_id IS NOT NULL THEN
1614 -- update booking status type id to bulk_enr_req_members table
1615 OPEN csr_get_booking_status_id(l_booking_id);
1616 FETCH csr_get_booking_status_id INTO l_booking_status_type_id, l_booking_status;
1617 CLOSE csr_get_booking_status_id;
1618
1619 FND_FILE.PUT_LINE(FND_FILE.LOG,'Enrollment Status - ' || l_booking_status);
1620
1621 UPDATE ota_bulk_enr_req_members
1622 SET enrollment_status = l_booking_status_type_id, error_message = NULL
1623 WHERE person_id = l_req_member_rec.person_id
1624 AND assignment_id = l_req_member_rec.assignment_id
1625 AND bulk_enr_request_id = p_enr_request_id;
1626 ELSE
1627 l_error_message := nvl(substr(l_error_message,1,2000),'Error When creating Enrollment ');
1628 FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
1629 UPDATE ota_bulk_enr_req_members
1630 SET error_message = l_error_message, enrollment_status = NULL
1631 WHERE person_id = l_req_member_rec.person_id
1632 AND bulk_enr_request_id = p_enr_request_id;
1633 END IF;
1634 END IF;
1635 END LOOP;
1636 IF p_from_conc_program THEN
1637 -- Start workflow and send a notification to the requestor
1638 notify_requestor(p_enr_request_id => p_enr_request_id);
1639 END IF;
1640 END IF;
1641 END mass_subscribe_to_class;
1642
1643 PROCEDURE submit_bulk_enrollments(
1644 p_enr_request_id IN NUMBER
1645 ,p_conc_request_id OUT NOCOPY NUMBER
1646 ,p_object_type OUT NOCOPY VARCHAR2
1647 ,p_object_name OUT NOCOPY VARCHAR2)
1648 IS
1649 l_threshold number;
1650 l_learners_selected NUMBER := 0;
1651 l_request_rec csr_get_request_info%ROWTYPE;
1652
1653 BEGIN
1654 l_threshold := FND_PROFILE.VALUE('OTA_MAX_ENR_PRC_ONLINE');
1655
1656 IF l_threshold IS NULL THEN l_threshold := 0; END IF;
1657 SELECT count(person_id)
1658 INTO l_learners_selected
1659 FROM ota_bulk_enr_req_members
1660 WHERE bulk_enr_request_id = p_enr_request_id;
1661
1662 OPEN csr_get_request_info(p_enr_request_id);
1663 FETCH csr_get_request_info INTO l_request_rec;
1664 CLOSE csr_get_request_info;
1665
1666 IF l_threshold >= l_learners_selected THEN
1667 -- No concurrent processing required
1668 p_conc_request_id := -1;
1669
1670 IF l_request_rec.object_type = 'CL' THEN
1671 mass_subscribe_to_class(
1672 p_enr_request_id => p_enr_request_id
1673 ,p_from_conc_program => false);
1674 ELSIF l_request_rec.object_type = 'LP' THEN
1675 mass_subscribe_to_lp(
1676 p_enr_request_id => p_enr_request_id
1677 ,p_from_conc_program => false);
1678 ELSIF l_request_rec.object_type = 'CRT' THEN
1679 mass_subscribe_to_cert(
1680 p_enr_request_id => p_enr_request_id
1681 ,p_from_conc_program => false);
1682 END IF;
1683
1684 ELSE
1685 -- Concurrent processing needs to be done
1686 p_conc_request_id := FND_REQUEST.SUBMIT_REQUEST(
1687 application => 'OTA'
1688 , program => 'OTBLKENR'
1689 , argument1 => p_enr_request_id);
1690 IF p_conc_request_id = 0 THEN
1691 -- Raise error submitting concurrent program
1692 null;
1693 END IF;
1694 END IF;
1695 UPDATE ota_bulk_enr_requests ber
1696 SET CONC_PROGRAM_REQUEST_ID = p_conc_request_id
1697 WHERE bulk_enr_request_id = p_enr_request_id;
1698
1699 p_object_type := ota_utility.get_lookup_meaning(
1700 'OTA_OBJECT_TYPE'
1701 ,l_request_rec.object_type
1702 ,810);
1703 p_object_name := get_object_name(l_request_rec.object_type, l_request_rec.object_id );
1704 END submit_bulk_enrollments;
1705
1706 Procedure process_bulk_enrollments
1707 (ERRBUF OUT NOCOPY VARCHAR2,
1708 RETCODE OUT NOCOPY VARCHAR2
1709 ,p_enr_request_id IN NUMBER) as
1710
1711 l_completed boolean;
1712 l_enr_request_id number;
1713
1714 failure exception;
1715 l_proc varchar2(72) := g_package||' bulk_enroll';
1716
1717 l_object_name varchar2(80);
1718
1719 l_request_rec csr_get_request_info%ROWTYPE;
1720
1721
1722 BEGIN
1723 OPEN csr_get_request_info(p_enr_request_id);
1724 FETCH csr_get_request_info INTO l_request_rec;
1725 l_object_name := OTA_BULK_ENROLL_UTIL.get_object_name(l_request_rec.object_type, l_request_rec.object_id);
1726
1727 IF csr_get_request_info%NOTFOUND THEN
1728 CLOSE csr_get_request_info;
1729 --fnd_concurrent.set_completion_status('ERROR');
1730 ELSIF l_request_rec.object_type = 'LP' THEN
1731 CLOSE csr_get_request_info;
1732
1733 FND_FILE.PUT_LINE(FND_FILE.LOG,'Executing Bulk Enrollment for the Learning Path - ' || l_object_name);
1734
1735 mass_subscribe_to_lp(
1736 p_enr_request_id => p_enr_request_id
1737 ,p_from_conc_program => true);
1738
1739 ELSIF l_request_rec.object_type = 'CL' THEN
1740 CLOSE csr_get_request_info;
1741
1742 FND_FILE.PUT_LINE(FND_FILE.LOG,'Executing Bulk Enrollment for the Class - ' || l_object_name);
1743 mass_subscribe_to_class(
1744 p_enr_request_id => p_enr_request_id
1745 ,p_from_conc_program => true);
1746
1747 ELSIF l_request_rec.object_type = 'CRT' THEN
1748 CLOSE csr_get_request_info;
1749
1750 FND_FILE.PUT_LINE(FND_FILE.LOG,'Executing Bulk Enrollment for the Certification - ' || l_object_name);
1751 mass_subscribe_to_cert(
1752 p_enr_request_id => p_enr_request_id
1753 ,p_from_conc_program => true);
1754 ELSE
1755 CLOSE csr_get_request_info;
1756 --fnd_concurrent.set_completion_status('ERROR');
1757 -- Raise error for unknown object type
1758 END IF;
1759
1760 commit;
1761
1762 EXCEPTION
1763 when others then
1764 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
1765 ||','||SUBSTR(SQLERRM, 1, 500));
1766
1767 END process_bulk_enrollments;
1768
1769
1770
1771 end ota_bulk_enroll_util;