[Home] [Help]
PACKAGE BODY: APPS.OTA_TRAINING_RECORD
Source
1 PACKAGE BODY ota_training_record AS
2 /* $Header: ottraqry.pkb 120.0.12010000.10 2009/05/05 07:50:41 dparthas noship $ */
3 FUNCTION is_required(p_options query_options,
4 p_option_value VARCHAR2) RETURN BOOLEAN
5 IS
6 BEGIN
7 IF p_options.COUNT > 0 THEN
8 FOR i IN p_options.FIRST..p_options.LAST LOOP
9 IF p_options(i) = p_option_value THEN
10 RETURN TRUE;
11 END IF;
12 END LOOP;
13 END IF;
14 RETURN FALSE;
15 END is_required;
16
17 PROCEDURE get_events(p_event_id IN ota_events_v.event_id%TYPE,
18 p_activity_version_id IN ota_activity_versions_vl.activity_version_id%TYPE,
19 p_events_tbl OUT NOCOPY event_tabletype)
20 IS
21 CURSOR cur_event IS
22 SELECT event_id,
23 object_version_number,
24 business_group_id,
25 title,
26 course_start_date,
27 course_start_time,
28 course_end_date,
29 course_end_time,
30 duration,
31 duration_units,
32 enrolment_start_date,
33 enrolment_end_date,
34 resource_booking_flag,
35 public_event_flag,
36 minimum_attendees,
37 maximum_attendees,
38 maximum_internal_attendees,
39 standard_price,
40 parent_event_id,
41 book_independent_flag,
42 actual_cost,
43 budget_cost,
44 budget_currency_code,
45 created_by,
46 creation_date,
47 last_updated_by,
48 last_update_login,
49 last_update_date,
50 comments,
51 evt_information_category,
52 evt_information1,
53 evt_information2,
54 evt_information3,
55 evt_information4,
56 evt_information5,
57 evt_information6,
58 evt_information7,
59 evt_information8,
60 evt_information9,
61 evt_information10,
62 evt_information11,
63 evt_information12,
64 evt_information13,
65 evt_information14,
66 evt_information15,
67 evt_information16,
68 evt_information17,
69 evt_information18,
70 evt_information19,
71 evt_information20,
72 secure_event_flag,
73 organization_id,
74 organization_name,
75 centre,
76 centre_meaning,
77 currency_code,
78 development_event_type,
79 development_event_type_meaning,
80 language_code,
81 language_description,
82 price_basis,
83 programme_code,
84 programme_code_meaning,
85 event_status,
86 event_status_meaning,
87 activity_name,
88 activity_version_id,
89 activity_version_name,
90 event_type,
91 event_type_meaning,
92 invoiced_amount,
93 user_status,
94 user_status_meaning,
95 vendor_id,
96 vendor_name,
97 project_id,
98 project_name,
99 project_number,
100 line_id,
101 org_id,
102 owner_id,
103 training_center_id,
104 location_id,
105 offering_id,
106 timezone,
107 inventory_item_id,
108 parent_offering_id,
109 data_source
110 FROM OTA_EVENTS_V
111 WHERE ((p_event_id IS NULL) OR (p_event_id IS NOT NULL AND event_id = p_event_id))
112 AND ((p_activity_version_id IS NULL) OR (p_activity_version_id IS NOT NULL AND activity_version_id = p_activity_version_id));
113
114 l_event_rec event_rectype;
115 l_events_tbl event_tabletype;
116 l_count NUMBER := 1;
117 BEGIN
118 l_events_tbl := event_tabletype();
119 OPEN cur_event;
120 LOOP
121 FETCH cur_event INTO l_event_rec;
122 IF cur_event%NOTFOUND THEN
123 EXIT;
124 END IF;
125
126 l_events_tbl.EXTEND(1);
127 l_events_tbl(l_count) := l_event_rec;
128 l_count := l_count + 1;
129 END LOOP;
130 CLOSE cur_event;
131 p_events_tbl := l_events_tbl;
132 END get_events;
133
134 PROCEDURE get_completed_certifications(p_start_person_id IN ota_cert_enrollments.person_id%TYPE
135 ,p_end_person_id IN ota_cert_enrollments.person_id%TYPE
136 ,p_certification_tbl OUT NOCOPY certification_tabletype)
137 IS
138 CURSOR get_certification IS
139 SELECT ctl.name cert_name,
140 cre.certification_id certification_id,
141 cre.certification_status_code certification_status_code,
142 ota_cpe_util.get_cre_status(cre.cert_enrollment_id) cert_status_meaning,
143 cpe.period_status_code period_status_code,
144 cpe_lkp.meaning period_status_meaning,
145 cpe.cert_period_start_date cert_period_start_date,
146 decode(cre.certification_status_code,'CERTIFIED', decode(crt.renewable_flag,'Y',cre.expiration_date,null), cpe.cert_period_end_date) cert_period_end_date,
147 cpe.completion_date cre_completion_date,
148 cre.person_id person_id,
149 cre.contact_id contact_id,
150 cre.cert_enrollment_id,
151 cpe.cert_prd_enrollment_id,
152 cre.is_history_flag,
153 crt.renewable_flag,
154 ota_cpe_util.is_period_renewable(cre.cert_enrollment_id) Is_Period_Renewable,
155 cre.earliest_enroll_date,
156 cpe.expiration_date,
157 crt.start_date_active,
158 crt.end_date_active
159 FROM ota_certifications_b crt
160 ,ota_certifications_tl ctl
161 ,ota_cert_enrollments cre
162 ,ota_cert_prd_enrollments cpe
163 ,hr_lookups cpe_lkp
164 WHERE cre.person_id BETWEEN nvl(p_start_person_id, cre.person_id)
165 AND nvl(p_end_person_id, cre.person_id)
166 AND crt.certification_id = cre.certification_id
167 AND cre.cert_enrollment_id = cpe.cert_enrollment_id(+)
168 AND crt.certification_id = ctl.certification_id
169 AND ctl.language = USERENV('LANG')
170 AND cpe_lkp.lookup_code(+) = cpe.period_status_code
171 AND cpe_lkp.lookup_type(+) = 'OTA_CERT_PRD_ENROLL_STATUS'
172 AND cre.certification_status_code = 'CERTIFIED';
173
174 l_certification_tbl certification_tabletype;
175 l_certification_rec certification_rectype;
176 l_count NUMBER := 1;
177
178 BEGIN
179 l_certification_tbl := certification_tabletype();
180
181 OPEN get_certification;
182 LOOP
183 FETCH get_certification INTO
184 l_certification_rec.cert_name,
185 l_certification_rec.certification_id,
186 l_certification_rec.certification_status_code,
187 l_certification_rec.cert_status_meaning,
188 l_certification_rec.period_status_code,
189 l_certification_rec.period_status_meaning,
190 l_certification_rec.cert_period_start_date,
191 l_certification_rec.cert_period_end_date,
192 l_certification_rec.cre_completion_date,
193 l_certification_rec.person_id,
194 l_certification_rec.contact_id,
195 l_certification_rec.cert_enrollment_id,
196 l_certification_rec.cert_prd_enrollment_id,
197 l_certification_rec.is_history_flag,
198 l_certification_rec.renewable_flag,
199 l_certification_rec.is_period_renewable,
200 l_certification_rec.earliest_enroll_date,
201 l_certification_rec.expiration_date,
202 l_certification_rec.start_date_active,
203 l_certification_rec.end_date_active;
204 IF get_certification%NOTFOUND THEN
205 EXIT;
206 END IF;
207
208 l_certification_tbl.EXTEND(1);
209 l_certification_tbl(l_count) := l_certification_rec;
210 l_count := l_count + 1;
211 END LOOP;
212 CLOSE get_certification;
213 p_certification_tbl := l_certification_tbl;
214 END get_completed_certifications;
215
216 PROCEDURE get_certifications(p_person_id IN ota_cert_enrollments.person_id%TYPE
217 ,p_start_person_id IN ota_cert_enrollments.person_id%TYPE
218 ,p_end_person_id IN ota_cert_enrollments.person_id%TYPE
219 ,p_is_history_flag IN varchar2
220 ,p_certification_tbl OUT NOCOPY certification_tabletype)
221 IS
222 CURSOR get_certification IS
223 select ctl.name cert_name,
224 cre.certification_id certification_id,
225 cre.certification_status_code certification_status_code,
226 ota_cpe_util.get_cre_status(cre.cert_enrollment_id) cert_status_meaning,
227 cpe.period_status_code period_status_code,
228 cpe_lkp.meaning period_status_meaning,
229 cpe.cert_period_start_date cert_period_start_date,
230 decode(cre.certification_status_code,'CERTIFIED', decode(crt.renewable_flag,'Y',cre.expiration_date,null), cpe.cert_period_end_date) cert_period_end_date,
231 cpe.completion_date cre_completion_date,
232 cre.person_id person_id,
233 cre.contact_id contact_id,
234 cre.cert_enrollment_id,
235 cpe.cert_prd_enrollment_id,
236 cre.is_history_flag,
237 crt.renewable_flag,
238 ota_cpe_util.is_period_renewable(cre.cert_enrollment_id) Is_Period_Renewable,
239 cre.earliest_enroll_date,
240 cpe.expiration_date,
241 crt.start_date_active,
242 crt.end_date_active
243 FROM ota_certifications_b crt
244 ,ota_certifications_tl ctl
245 ,ota_cert_enrollments cre
246 ,ota_cert_prd_enrollments cpe
247 ,hr_lookups cpe_lkp
248 WHERE
249 nvl(p_person_id, cre.person_id) = cre.person_id
250 AND cre.person_id BETWEEN nvl(p_start_person_id, cre.person_id)
251 AND nvl(p_end_person_id, cre.person_id)
252 AND crt.certification_id = cre.certification_id
253 AND cre.cert_enrollment_id = cpe.cert_enrollment_id(+)
254 AND crt.certification_id = ctl.certification_id
255 AND ctl.language = USERENV('LANG')
256 AND cpe_lkp.lookup_code(+) = cpe.period_status_code
257 AND cpe_lkp.lookup_type(+) = 'OTA_CERT_PRD_ENROLL_STATUS'
258 AND ((p_is_history_flag = 'Y' and
259 ((cre.is_history_flag ='Y'
260 OR (CERTIFICATION_STATUS_CODE IN ('CANCELLED','EXPIRED'))
261 OR (crt.renewable_flag ='Y' AND PERIOD_STATUS_CODE NOT IN ('ACTIVE','ENROLLED'))
262 OR (NVL(TRUNC(crt.end_date_active), TRUNC(SYSDATE)) < TRUNC(SYSDATE))) OR (NVL(TRUNC(cpe.cert_period_end_date), TRUNC(SYSDATE)) < TRUNC(SYSDATE))))
263 OR (p_is_history_flag = 'N' AND ((cre.is_history_flag IS NULL OR cre.is_history_flag = 'N')
264 AND (NVL(TRUNC(crt.end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE))
265 AND CERTIFICATION_STATUS_CODE NOT IN ('CANCELLED','REJECTED','AWAITING_APPROVAL')
266 AND ((cpe.cert_prd_enrollment_id IS NULL)
267 OR (cpe.cert_prd_enrollment_id IS NOT NULL
268 AND cpe.cert_prd_enrollment_id =
269 (SELECT MAX(cpe2.cert_prd_enrollment_id)
270 FROM ota_cert_prd_enrollments cpe2
271 WHERE cpe2.cert_enrollment_id(+) = cre.cert_enrollment_id))))));
272
273 l_certification_tbl certification_tabletype;
274 l_certification_rec certification_rectype;
275 l_count NUMBER := 1;
276
277 BEGIN
278 l_certification_tbl := certification_tabletype();
279
280 OPEN get_certification;
281 LOOP
282 FETCH get_certification INTO
283 l_certification_rec.cert_name,
284 l_certification_rec.certification_id,
285 l_certification_rec.certification_status_code,
286 l_certification_rec.cert_status_meaning,
287 l_certification_rec.period_status_code,
288 l_certification_rec.period_status_meaning,
289 l_certification_rec.cert_period_start_date,
290 l_certification_rec.cert_period_end_date,
291 l_certification_rec.cre_completion_date,
292 l_certification_rec.person_id,
293 l_certification_rec.contact_id,
294 l_certification_rec.cert_enrollment_id,
295 l_certification_rec.cert_prd_enrollment_id,
296 l_certification_rec.is_history_flag,
297 l_certification_rec.renewable_flag,
298 l_certification_rec.is_period_renewable,
299 l_certification_rec.earliest_enroll_date,
300 l_certification_rec.expiration_date,
301 l_certification_rec.start_date_active,
302 l_certification_rec.end_date_active;
303 IF get_certification%NOTFOUND AND l_count = 1 THEN
304 RAISE invalid_person_id;
305 ELSIF get_certification%NOTFOUND THEN
306 EXIT;
307 END IF;
308
309 l_certification_tbl.EXTEND(1);
310 l_certification_tbl(l_count) := l_certification_rec;
311 l_count := l_count + 1;
312 END LOOP;
313 CLOSE get_certification;
314 p_certification_tbl := l_certification_tbl;
315 END get_certifications;
316
317 PROCEDURE get_certifications(p_person_id IN ota_cert_enrollments.person_id%TYPE
318 ,p_certification_id IN ota_cert_enrollments.certification_id%TYPE
319 ,p_certification_tbl OUT NOCOPY certification_tabletype)
320 IS
321 CURSOR get_certification IS
322 select ctl.name cert_name,
323 cre.certification_id certification_id,
324 cre.certification_status_code certification_status_code,
325 ota_cpe_util.get_cre_status(cre.cert_enrollment_id) cert_status_meaning,
326 cpe.period_status_code period_status_code,
327 cpe_lkp.meaning period_status_meaning,
328 cpe.cert_period_start_date cert_period_start_date,
329 decode(cre.certification_status_code,'CERTIFIED', decode(crt.renewable_flag,'Y',cre.expiration_date,null), cpe.cert_period_end_date) cert_period_end_date,
330 cpe.completion_date cre_completion_date,
331 cre.person_id person_id,
332 cre.contact_id contact_id,
333 cre.cert_enrollment_id,
334 cpe.cert_prd_enrollment_id,
335 cre.is_history_flag,
336 crt.renewable_flag,
337 ota_cpe_util.is_period_renewable(cre.cert_enrollment_id) Is_Period_Renewable,
338 cre.earliest_enroll_date,
339 cpe.expiration_date,
340 crt.start_date_active,
341 crt.end_date_active
342 FROM ota_certifications_b crt
343 ,ota_certifications_tl ctl
344 ,ota_cert_enrollments cre
345 ,ota_cert_prd_enrollments cpe
346 ,hr_lookups cpe_lkp
347 WHERE ((p_person_id IS NULL) OR (p_person_id IS NOT NULL AND cre.person_id = p_person_id))
348 AND ((p_certification_id IS NULL) OR (cre.certification_id = p_certification_id))
349 AND crt.certification_id = cre.certification_id
350 AND cre.cert_enrollment_id = cpe.cert_enrollment_id(+)
351 AND crt.certification_id = ctl.certification_id
352 AND ctl.language = USERENV('LANG')
353 AND cpe_lkp.lookup_code(+) = cpe.period_status_code
354 AND cpe_lkp.lookup_type(+) = 'OTA_CERT_PRD_ENROLL_STATUS';
355
356 l_certification_tbl certification_tabletype;
357 l_certification_rec certification_rectype;
358 l_count NUMBER := 1;
359
360 BEGIN
361 l_certification_tbl := certification_tabletype();
362
363 OPEN get_certification;
364 LOOP
365 FETCH get_certification INTO
366 l_certification_rec.cert_name,
367 l_certification_rec.certification_id,
368 l_certification_rec.certification_status_code,
369 l_certification_rec.cert_status_meaning,
370 l_certification_rec.period_status_code,
371 l_certification_rec.period_status_meaning,
372 l_certification_rec.cert_period_start_date,
373 l_certification_rec.cert_period_end_date,
374 l_certification_rec.cre_completion_date,
375 l_certification_rec.person_id,
376 l_certification_rec.contact_id,
377 l_certification_rec.cert_enrollment_id,
378 l_certification_rec.cert_prd_enrollment_id,
379 l_certification_rec.is_history_flag,
380 l_certification_rec.renewable_flag,
381 l_certification_rec.is_period_renewable,
382 l_certification_rec.earliest_enroll_date,
383 l_certification_rec.expiration_date,
384 l_certification_rec.start_date_active,
385 l_certification_rec.end_date_active;
386 IF get_certification%NOTFOUND AND l_count = 1 THEN
387 RAISE invalid_certification_id;
388 ELSIF get_certification%NOTFOUND THEN
389 EXIT;
390 END IF;
391
392 l_certification_tbl.EXTEND(1);
393 l_certification_tbl(l_count) := l_certification_rec;
394 l_count := l_count + 1;
395 END LOOP;
396 CLOSE get_certification;
397 p_certification_tbl := l_certification_tbl;
398 END get_certifications;
399
400 PROCEDURE get_certification_description(p_certification_id IN ota_certifications_b.certification_id%TYPE,
401 p_cert_enrollment_id IN ota_cert_enrollments.cert_enrollment_id%TYPE,
402 p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
403 p_certification_desc_rec OUT NOCOPY cert_description_rectype)
404 IS
405 CURSOR get_cert_description IS
406 select
407 b.certification_id certification_id
408 , b.INITIAL_COMPLETION_DATE
409 , b.INITIAL_COMPLETION_DURATION
410 , b.INITIAL_COMPL_DURATION_UNITS
411 , b.RENEWAL_DURATION
412 , b.RENEWAL_DURATION_UNITS
413 , b.NOTIFY_DAYS_BEFORE_EXPIRE
414 , b.VALIDITY_DURATION
415 , b.VALIDITY_DURATION_UNITS
416 , b.RENEWABLE_FLAG RENEWABLE_FLAG_CODE
417 , ota_utility.get_lookup_meaning('YES_NO',b.renewable_flag, '810') renewable_flag_meaning
418 , b.start_date_active
419 , B.END_DATE_ACTIVE
420 , tl.name Name
421 , tl.description Description
422 , tl.objectives Objectives
423 , tl.purpose Purpose
424 , tl.keywords Keywords
425 , INITIAL_PERIOD_COMMENTS
426 , tl.RENEWAL_PERIOD_COMMENTS
427 from ota_certifications_b b,
428 ota_certifications_tl tl
429 where
430 b.certification_id = tl.certification_id
431 --and b.business_group_id = ota_general.get_business_group_id
432 and tl.language = USERENV ('LANG')
433 and b.certification_id = p_certification_id;
434
435 CURSOR get_certification_details IS
436 SELECT cre.certification_status_code certification_status_code
437 , crt_lkp.meaning cert_status_meaning
438 , cpe.period_status_code period_status_code
439 , cpe_lkp.meaning period_status_meaning
440 , cre.expiration_date
441 , cre.earliest_enroll_date
442 , cpe.cert_period_start_date cert_period_start_date
443 , cpe.cert_period_end_date cert_period_end_date
444 , cre.cert_enrollment_id cert_enrollment_id
445 , cpe.cert_prd_enrollment_id cert_prd_enrollment_id
446 , cre.completion_date cre_completion_date
447 FROM ota_cert_enrollments cre
448 , ota_cert_prd_enrollments cpe
449 , hr_lookups crt_lkp
450 , hr_lookups cpe_lkp
451 WHERE cre.cert_enrollment_id = cpe.cert_enrollment_id (+)
452 AND crt_lkp.lookup_code = cre.certification_status_code
453 AND crt_lkp.lookup_type = 'OTA_CERT_ENROLL_STATUS'
454 AND cpe_lkp.lookup_code (+) = cpe.period_status_code
455 AND cpe_lkp.lookup_type (+) = 'OTA_CERT_PRD_ENROLL_STATUS'
456 AND cre.cert_enrollment_id = p_cert_enrollment_id
457 AND cpe.cert_prd_enrollment_id (+) = p_cert_prd_enrollment_id;
458
459 l_certification_desc_rec cert_description_rectype := NULL;
460
461 BEGIN
462 OPEN get_cert_description;
463 FETCH get_cert_description INTO
464 l_certification_desc_rec.certification_id,
465 l_certification_desc_rec.initial_completion_date,
466 l_certification_desc_rec.initial_completion_duration,
467 l_certification_desc_rec.initial_compl_duration_units,
468 l_certification_desc_rec.renewal_duration,
469 l_certification_desc_rec.renewal_duration_units,
470 l_certification_desc_rec.notify_days_before_expire,
471 l_certification_desc_rec.validity_duration,
472 l_certification_desc_rec.validity_duration_units,
473 l_certification_desc_rec.renewable_flag_code,
474 l_certification_desc_rec.renewable_flag_meaning,
475 l_certification_desc_rec.start_date_active,
476 l_certification_desc_rec.end_date_active,
477 l_certification_desc_rec.name,
478 l_certification_desc_rec.description,
479 l_certification_desc_rec.objectives,
480 l_certification_desc_rec.purpose,
481 l_certification_desc_rec.keywords,
482 l_certification_desc_rec.initial_period_comments,
483 l_certification_desc_rec.renewal_period_comments;
484 CLOSE get_cert_description;
485
486 --get certification details
487 OPEN get_certification_details;
488 FETCH get_certification_details INTO
489 l_certification_desc_rec.certification_status_code,
490 l_certification_desc_rec.cert_status_meaning,
491 l_certification_desc_rec.period_status_code,
492 l_certification_desc_rec.period_status_meaning,
493 l_certification_desc_rec.expiration_date,
494 l_certification_desc_rec.earliest_enroll_date,
495 l_certification_desc_rec.cert_period_start_date,
496 l_certification_desc_rec.cert_period_end_date,
497 l_certification_desc_rec.cert_enrollment_id,
498 l_certification_desc_rec.cert_prd_enrollment_id,
499 l_certification_desc_rec.cre_completion_date;
500 CLOSE get_certification_details;
501 p_certification_desc_rec := l_certification_desc_rec;
502
503 END get_certification_description;
504
505 PROCEDURE get_cert_competencies(p_certification_id IN ota_certifications_b.certification_id%TYPE,
506 p_competencies_tbl OUT NOCOPY cert_competencies_tabletype)
507 IS
508 CURSOR get_competencies IS
509 SELECT comp.competence_id Competence_Id,
510 cpn.name Competence_Name,
511 comp.proficiency_level_id Proficiency_Level_Id,
512 ratl1.step_value || DECODE(ratl1.name,'','', ' - ' || ratl1.name) Proficiency_Level_Name,
513 comp.effective_date_from Effective_Date_From,
514 comp.effective_date_to Effective_Date_To,
515 comp.object_id object_id,
516 comp.business_group_id Business_Group_Id
517 FROM per_competence_elements comp,
518 per_competences_tl cpn, per_rating_levels ratl1
519 WHERE comp.object_id = p_certification_id
520 AND comp.type = 'OTA_CERTIFICATION'
521 AND cpn.competence_id = comp.competence_id
522 AND comp.proficiency_level_id = ratl1.rating_level_id(+)
523 AND cpn.language = USERENV('LANG')
524 ORDER BY COMPETENCE_NAME;
525
526 l_competencies_rec cert_comp_rectype := NULL;
527 l_competencies_tbl cert_competencies_tabletype;
528 l_count NUMBER := 1;
529 BEGIN
530 l_competencies_tbl := cert_competencies_tabletype();
531 OPEN get_competencies;
532 LOOP
533 FETCH get_competencies INTO l_competencies_rec;
534 IF get_competencies%NOTFOUND THEN
535 EXIT;
536 END IF;
537 l_competencies_tbl.EXTEND(1);
538 l_competencies_tbl(l_count) := l_competencies_rec;
539 l_count := l_count + 1;
540
541 END LOOP;
542 CLOSE get_competencies;
543 p_competencies_tbl := l_competencies_tbl;
544 END get_cert_competencies;
545
546 PROCEDURE get_cert_components(p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
547 p_components_tbl OUT NOCOPY components_tabletype)
548 IS
549 CURSOR get_components IS
550 select cme.cert_mbr_enrollment_id cert_mbr_enrollment_id,
551 tav.activity_version_id Activity_version_Id,
552 cme.member_status_code member_status_code,
553 tav.version_name Course_Name,
554 cme.completion_date completion_date,
555 lkp.meaning Member_Status_Meaning,
556 decode( ota_cme_util.chk_active_cme_enrl(cme.cert_mbr_enrollment_id),
557 'F', 'DetailsIconDisabled',
558 decode( cme.member_status_code,
559 'ACTIVE','DetailsIconEnabled',
560 'CANCELLED','DetailsIconDisabled',
561 'PLANNED','DetailsIconDisabled',
562 'AWAITING_APPROVAL','DetailsIconDisabled',
563 'COMPLETED','DetailsIconEnabled')
564 ) Enrollment_Details_Icon,
565 tav.Version_Code Version_Code,
566 tav.Version_Name Activity_Version_Name,
567 tav.Start_Date Start_Date,
568 tav.End_Date End_Date,
569 cmb.certification_member_id certification_member_id,
570 cmb.MEMBER_SEQUENCE MEMBER_SEQUENCE,
571 OTA_LO_UTILITY.get_cme_online_event_id(NVL(cre.person_id, cre.contact_id),
572 DECODE(cre.person_id, NULL, 'C', 'E'),
573 cme.cert_mbr_enrollment_id) as Event_Id,
574 OTA_LO_UTILITY.get_cert_lo_status(NVL(cre.person_id, cre.contact_id),
575 DECODE(cre.person_id, NULL, 'C', 'E'),
576 cme.cert_mbr_enrollment_id) as Perf_Status, cme.Cert_Prd_Enrollment_Id,
577 cre.Cert_Enrollment_Id,
578 cre.Certification_Id,
579 fnd_profile.value('OTA_ILEARNING_SITE_ADDRESS') AS SITE_ADDRESS ,
580 fnd_profile.value('OTA_ILEARNING_SITE_ID') AS SITE_SHORT_NAME ,
581 fnd_profile.value('USERNAME') AS FND_USER_NAME ,
582 WFA_HTML.CONV_SPECIAL_URL_CHARS(fnd_profile.value('OTA_ILEARNING_SITE_ADDRESS')) Encoded_Site_Address ,
583 (select e.offering_id
584 from ota_events e
585 where e.event_id = OTA_LO_UTILITY.get_cme_online_event_id(NVL(cre.person_id, cre.contact_id),
586 DECODE(cre.person_id, NULL, 'C', 'E'), cme.cert_mbr_enrollment_id))AS CLASSROOM_ID
587 from ota_cert_enrollments cre,
588 ota_cert_prd_enrollments cpe,
589 ota_cert_mbr_enrollments cme,
590 ota_certification_members cmb,
591 ota_activity_versions_vl tav,
592 hr_lookups lkp
593 where tav.activity_version_id = cmb.object_id
594 and cmb.object_type = 'H'
595 and cmb.certification_member_id = cme.cert_member_id
596 and lkp.lookup_code = cme.member_status_code
597 and lkp.lookup_type = 'OTA_CERT_MBR_ENROLL_STATUS'
598 AND trunc(sysdate) BETWEEN NVL(lkp.start_date_active,trunc(sysdate))
599 AND NVL (lkp.end_date_active, trunc(sysdate))
600 AND lkp.enabled_flag ='Y'
601 and cpe.cert_enrollment_id = cre.cert_enrollment_id
602 and cme.cert_prd_enrollment_id = cpe.cert_prd_enrollment_id
603 and cme.cert_prd_enrollment_id = p_cert_prd_enrollment_id
604 order by MEMBER_SEQUENCE asc;
605
606 l_components_rec cert_component_rectype := NULL;
607 l_components_tbl components_tabletype;
608 l_count NUMBER := 1;
609 BEGIN
610 l_components_tbl := components_tabletype();
611 OPEN get_components;
612 LOOP
613 FETCH get_components INTO l_components_rec;
614 IF get_components%NOTFOUND THEN
615 EXIT;
616 END IF;
617 l_components_tbl.EXTEND(1);
618 l_components_tbl(l_count) := l_components_rec;
619 l_count := l_count+1;
620 END LOOP;
621 CLOSE get_components;
622 p_components_tbl := l_components_tbl;
623 END get_cert_components;
624
625 PROCEDURE get_learner(p_cert_enrollment_id IN ota_cert_enrollments.cert_enrollment_id%TYPE,
626 p_person_id OUT NOCOPY ota_cert_enrollments.person_id%TYPE,
627 p_certification_id OUT NOCOPY ota_cert_enrollments.certification_id%TYPE)
628 IS
629 CURSOR get_info IS
630 SELECT oce.person_id,
631 oce.certification_id
632 FROM ota_cert_enrollments oce
633 WHERE oce.cert_enrollment_id = p_cert_enrollment_id;
634
635 l_person_id ota_cert_enrollments.person_id%TYPE;
636 l_certification_id ota_cert_enrollments.certification_id%TYPE;
637
638 BEGIN
639 OPEN get_info;
640 FETCH get_info into l_person_id, l_certification_id;
641 IF get_info%NOTFOUND THEN
642 RAISE invalid_cert_enrollment_id;
643 END IF;
644 CLOSE get_info;
645
646 p_person_id := l_person_id;
647 p_certification_id := l_certification_id;
648 END get_learner;
649
650 PROCEDURE get_certification_details( p_query_options IN cert_query_input_rectype,
651 p_certifications OUT NOCOPY certification_tabletype)
652 IS
653 l_certification_rec certification_rectype;
654 l_certification_desc_rec cert_description_rectype := NULL;
655 l_competencies_rec cert_comp_rectype := NULL;
656 l_components_rec cert_component_rectype := NULL;
657
658 l_certification_tbl certification_tabletype;
659 l_competencies_tbl cert_competencies_tabletype;
660 l_components_tbl components_tabletype;
661
662 l_person_id ota_cert_enrollments.person_id%TYPE := NULL;
663 l_certification_id ota_cert_enrollments.certification_id%TYPE := NULL;
664
665 l_details_required BOOLEAN := FALSE;
666 l_competencies_required BOOLEAN := FALSE;
667 l_components_required BOOLEAN := FALSE;
668
669 BEGIN
670 --l_details_required := is_required(p_query_options.options, 'DETAIL');
671 l_details_required := TRUE;
672 l_competencies_required := is_required(p_query_options.options, 'COMPETENCY');
673 l_components_required := is_required(p_query_options.options, 'COMPONENT');
674
675 IF p_query_options.person_id IS NOT NULL THEN
676 IF p_query_options.view_history THEN
677 get_certifications( p_person_id => p_query_options.person_id
678 ,p_start_person_id => p_query_options.start_person_id
679 ,p_end_person_id => p_query_options.end_person_id
680 ,p_is_history_flag => 'Y'
681 ,p_certification_tbl => l_certification_tbl);
682 ELSE
683 get_certifications( p_person_id => p_query_options.person_id
684 ,p_start_person_id => p_query_options.start_person_id
685 ,p_end_person_id => p_query_options.end_person_id
686 ,p_is_history_flag => 'N'
687 ,p_certification_tbl => l_certification_tbl);
688 END IF;
689 ELSIF p_query_options.certification_id IS NOT NULL THEN
690 get_certifications( p_person_id => null
691 ,p_certification_id => p_query_options.certification_id
692 ,p_certification_tbl => l_certification_tbl);
693 ELSIF p_query_options.cert_enrollment_id IS NOT NULL THEN
694 get_learner( p_query_options.cert_enrollment_id,
695 l_person_id,
696 l_certification_id);
697 get_certifications( p_person_id => l_person_id
698 ,p_certification_id => l_certification_id
699 ,p_certification_tbl => l_certification_tbl);
700 ELSE
701 get_completed_certifications(p_start_person_id => p_query_options.start_person_id
702 ,p_end_person_id => p_query_options.end_person_id
703 ,p_certification_tbl => l_certification_tbl);
704 END IF;
705
706 IF l_certification_tbl.COUNT > 0 THEN
707 FOR i in l_certification_tbl.FIRST..l_certification_tbl.LAST LOOP
708 l_certification_rec := l_certification_tbl(i);
709
710 IF l_details_required THEN
711 -- get certification descriptions
712 get_certification_description(l_certification_rec.certification_id,
713 l_certification_rec.cert_enrollment_id,
714 l_certification_rec.cert_prd_enrollment_id,
715 l_certification_desc_rec);
716
717 l_certification_rec.cert_description := l_certification_desc_rec;
718 END IF;
719
720 IF l_competencies_required THEN
721 -- get competencies
722 get_cert_competencies(l_certification_rec.certification_id,
723 l_competencies_tbl);
724 l_certification_rec.cert_competencies := l_competencies_tbl;
725 END IF;
726
727 IF l_components_required THEN
728 -- get components
729 get_cert_components(l_certification_rec.cert_prd_enrollment_id,
730 l_components_tbl);
731 l_certification_rec.cert_components := l_components_tbl;
732 END IF;
733
734 -- set certification status code
735 l_certification_rec.event_action := 'CERTIFICATION_' || l_certification_rec.certification_status_code;
736
737 l_certification_tbl(i) := l_certification_rec;
738 END LOOP;
739 END IF;
740 p_certifications := l_certification_tbl;
741 EXCEPTION
742 WHEN invalid_cert_enrollment_id THEN
743 p_certifications := certification_tabletype();
744 WHEN invalid_person_id THEN
745 p_certifications := certification_tabletype();
746 WHEN invalid_certification_id THEN
747 p_certifications := certification_tabletype();
748 END get_certification_details;
749
750 FUNCTION is_part_of_certification(p_booking_id IN ota_delegate_bookings.booking_id%TYPE,
751 p_cert_enrollment_id OUT NOCOPY ota_cert_enrollments.cert_enrollment_id%TYPE) RETURN BOOLEAN
752 IS
753
754 CURSOR get_enrollments IS
755 SELECT *
756 FROM (
757 SELECT cpe.cert_prd_enrollment_id,
758 cre.cert_enrollment_id,
759 cre.certification_id,
760 to_char(b.booking_id) Enrollment_Number,
761 cme.cert_mbr_enrollment_id,
762 cre.person_id,
763 cpe.cert_period_start_date,
764 cpe.cert_period_end_date,
765 e.course_end_date Course_End,
766 e.course_start_date event_start_date,
767 e.event_type event_type
768 FROM ota_events e,
769 ota_events_tl et,
770 hr_all_organization_units o,
771 hr_all_organization_units_tl haotl,
772 ota_activity_versions a,
773 ota_delegate_bookings b,
774 ota_booking_status_types_VL s,
775 ota_cert_enrollments cre,
776 ota_cert_prd_enrollments cpe,
777 ota_cert_mbr_enrollments cme,
778 ota_certification_members cmb,
779 ota_offerings ofr,
780 ota_category_usages c
781 WHERE e.event_id = b.event_id
782 AND cre.cert_enrollment_id = cpe.cert_enrollment_id
783 AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id
784 AND e.event_id= et.event_id
785 AND s.type <> 'C'
786 AND et.language = USERENV('LANG')
787 AND e.training_center_id = o.organization_id(+)
788 And haotl.organization_id(+) = o.organization_id
789 AND cme.cert_member_id = cmb.certification_member_id
790 AND cmb.object_id = a.activity_version_id
791 AND cmb.object_type = 'H'
792 AND e.parent_offering_id = ofr.offering_id
793 And haotl.language(+) = USERENV ('LANG')
794 AND e.activity_version_id = a.activity_version_id
795 AND b.booking_status_type_id = s.booking_status_type_id
796 AND ((cre.person_id IS NOT NULL AND b.delegate_person_id = cre.person_id)
797 OR
798 (cre.CONTACT_ID IS NOT NULL AND b.delegate_contact_id = cre.contact_id))
799 AND E.PARENT_OFFERING_ID=OFR.OFFERING_ID
800 AND OFR.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
801 ) QRSLT
802 WHERE (enrollment_number = p_booking_id
803 AND (
804 ( event_start_date >= cert_period_start_date
805 and nvl(course_end,to_date('4712/12/31', 'YYYY/MM/DD')) <= cert_period_end_date )
806 or ( event_type ='SELFPACED'
807 and ((cert_period_end_date >= event_start_date) AND ((course_end is null) or (course_end IS NOT NULL AND course_end >= cert_period_start_date)) ))));
808
809 l_cursor_rec get_enrollments%ROWTYPE;
810 BEGIN
811 OPEN get_enrollments;
812 FETCH get_enrollments INTO l_cursor_rec;
813 IF get_enrollments%FOUND THEN
814 p_cert_enrollment_id := l_cursor_rec.cert_enrollment_id;
815 RETURN TRUE;
816 END IF;
817 RETURN FALSE;
818 CLOSE get_enrollments;
819 END is_part_of_certification;
820
821 PROCEDURE get_training_details_internal(p_person_id IN ota_delegate_bookings.delegate_person_id%TYPE,
822 p_start_person_id IN ota_delegate_bookings.delegate_person_id%TYPE,
823 p_end_person_id IN ota_delegate_bookings.delegate_person_id%TYPE,
824 p_view_history IN VARCHAR,
825 p_activity_tbl OUT NOCOPY activity_tabletype)
826 IS
827 CURSOR get_activity_rec IS
828 SELECT distinct
829 a.activity_version_id Activity_Version_Id,
830 a.version_name Activity_Version_Name,
831 a.description Activity_Description,
832 a.objectives Activity_Objectives,
833 a.intended_audience Activity_Audience,
834 a.keywords Activity_Keywords,
835 a.tav_information_category ,
836 a.tav_information1,
837 a.tav_information2,
838 a.tav_information3,
839 a.tav_information4,
840 a.tav_information5,
841 a.tav_information6,
842 a.tav_information7,
843 a.tav_information8,
844 a.tav_information9,
845 a.tav_information10,
846 a.tav_information11,
847 a.tav_information12,
848 a.tav_information13,
849 a.tav_information14,
850 a.tav_information15,
851 a.tav_information16,
852 a.tav_information17,
853 a.tav_information18,
854 a.tav_information19,
855 a.tav_information20,
856 a.Version_Code Activity_Version_Code,
857 hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
858 a.professional_credits,
859 hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
860 a.Controlling_Person_Id Controlling_Person_Id,
861 ST.NAME status,
862 DECODE(C.ONLINE_FLAG ,'Y',OTA_LO_UTILITY.get_enroll_lo_status(NVL(D.delegate_person_id, D.contact_id), DECODE(D.delegate_person_id, NULL, 'C', 'E') , E.EVENT_ID,D.BOOKING_STATUS_TYPE_ID,D.BOOKING_ID,null,'N'), null) player_status
863 ,D.BOOKING_ID
864 ,D.DELEGATE_PERSON_ID
865 ,D.IS_HISTORY_FLAG
866 ,D.DATE_STATUS_CHANGED
867 ,D.SUCCESSFUL_ATTENDANCE_FLAG
868 , nvl(D.IS_MANDATORY_ENROLLMENT,'N') is_mandatory_enrollment
869 ,E.EVENT_ID
870 FROM ota_activity_versions_vl a ,
871 OTA_EVENTS E,
872 OTA_EVENTS_TL ET,
873 OTA_DELEGATE_BOOKINGS D,
874 OTA_BOOKING_STATUS_TYPES S,
875 OTA_BOOKING_STATUS_TYPES_TL ST,
876 OTA_OFFERINGS O,
877 OTA_OFFERINGS_TL OT,
878 OTA_CATEGORY_USAGES C,
879 OTA_CATEGORY_USAGES_TL CT,
880 OTA_ACTIVITY_VERSIONS_TL OAV,
881 OTA_EVALUATIONS EVAL
882 WHERE a.activity_version_id = e.activity_version_id
883 AND OAV.ACTIVITY_VERSION_ID = a.ACTIVITY_VERSION_ID
884 AND E.EVENT_ID=D.EVENT_ID
885 AND S.BOOKING_STATUS_TYPE_ID=D.BOOKING_STATUS_TYPE_ID
886 --AND E.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
887 AND E.PARENT_OFFERING_ID=O.OFFERING_ID
888 AND O.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
889 AND a.ACTIVITY_VERSION_ID = O.ACTIVITY_VERSION_ID
890 AND OAV.LANGUAGE=USERENV('LANG')
891 AND E.EVENT_ID = ET.EVENT_ID
892 AND ET.LANGUAGE=USERENV('LANG')
893 AND S.BOOKING_STATUS_TYPE_ID = ST.BOOKING_STATUS_TYPE_ID
894 AND ST.LANGUAGE=USERENV('LANG')
895 AND O.OFFERING_ID = OT.OFFERING_ID
896 AND OT.LANGUAGE=USERENV('LANG')
897 AND C.CATEGORY_USAGE_ID = CT.CATEGORY_USAGE_ID
898 AND CT.LANGUAGE=USERENV('LANG')
899 AND E.EVENT_TYPE IN ('SCHEDULED','SELFPACED')
900 AND E.BOOK_INDEPENDENT_FLAG = 'N'
901 AND E.EVENT_ID = EVAL.OBJECT_ID(+)
902 AND (EVAL.OBJECT_TYPE is null or EVAL.OBJECT_TYPE = 'E')
903 AND nvl(p_person_id, D.delegate_person_id) = D.delegate_person_id
904 AND D.delegate_person_id BETWEEN nvl(p_start_person_id, D.delegate_person_id)
905 AND nvl(p_end_person_id, D.delegate_person_id)
906 AND (( ( p_view_history = 'N' AND
907 ((D.IS_HISTORY_FLAG IS NULL OR D.IS_HISTORY_FLAG = 'N')
908 AND ( E.COURSE_END_DATE IS NULL
909 OR TO_DATE( TO_CHAR(nvl(E.COURSE_END_DATE,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') ||
910 ' ' || nvl(E.COURSE_END_TIME,'23:59'), 'YYYY/MM/DD HH24:MI')
911 >= OTA_TIMEZONE_UTIL.CONVERT_DATE(TRUNC(SYSDATE), TO_CHAR(SYSDATE, 'HH24:MI'),
912 OTA_TIMEZONE_UTIL.GET_SERVER_TIMEZONE_CODE, E.TIMEZONE)
913 )
914 AND ((C.ONLINE_FLAG = 'Y' AND S.TYPE IN ('A','P','E')) OR (C.ONLINE_FLAG = 'N' AND S.TYPE in( 'P','E'))))))
915 OR
916 (p_view_history = 'Y'
917 AND ((S.TYPE NOT IN ('R','W'))
918 AND ((D.IS_HISTORY_FLAG = 'Y')
919 OR ( E.COURSE_END_DATE IS NOT NULL
920 AND TO_DATE( TO_CHAR(nvl(E.COURSE_END_DATE,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD')
921 || ' ' || nvl(E.COURSE_END_TIME,'23:59'), 'YYYY/MM/DD HH24:MI')
922 < OTA_TIMEZONE_UTIL.CONVERT_DATE(TRUNC(SYSDATE), TO_CHAR(SYSDATE, 'HH24:MI'),
923 OTA_TIMEZONE_UTIL.GET_SERVER_TIMEZONE_CODE, E.TIMEZONE)
924 )
925 OR (C.ONLINE_FLAG = 'N' AND S.TYPE IN ('A','C'))
926 OR (C.ONLINE_FLAG = 'Y' AND S.TYPE IN ('C'))
927 )
928 )));
929
930 l_activity_rec activity_rectype;
931 l_activity_tbl activity_tabletype;
932 l_count NUMBER := 1;
933 l_event_id ota_events.event_id%TYPE;
934 BEGIN
935 l_activity_tbl := activity_tabletype();
936 OPEN get_activity_rec;
937 LOOP
938 FETCH get_activity_rec INTO
939 l_activity_rec.activity_version_id,
940 l_activity_rec.activity_version_name,
941 l_activity_rec.activity_description,
942 l_activity_rec.activity_objectives,
943 l_activity_rec.activity_audience,
944 l_activity_rec.activity_keywords,
945 l_activity_rec.tav_information_category,
946 l_activity_rec.tav_information1,
947 l_activity_rec.tav_information2,
948 l_activity_rec.tav_information3,
949 l_activity_rec.tav_information4,
950 l_activity_rec.tav_information5,
951 l_activity_rec.tav_information6,
952 l_activity_rec.tav_information7,
953 l_activity_rec.tav_information8,
954 l_activity_rec.tav_information9,
955 l_activity_rec.tav_information10,
956 l_activity_rec.tav_information11,
957 l_activity_rec.tav_information12,
958 l_activity_rec.tav_information13,
959 l_activity_rec.tav_information14,
960 l_activity_rec.tav_information15,
961 l_activity_rec.tav_information16,
962 l_activity_rec.tav_information17,
963 l_activity_rec.tav_information18,
964 l_activity_rec.tav_information19,
965 l_activity_rec.tav_information20,
966 l_activity_rec.activity_version_code,
967 l_activity_rec.success_criteria,
968 l_activity_rec.professional_credits,
969 l_activity_rec.professional_credit_meaning,
970 l_activity_rec.controlling_person_id,
971 l_activity_rec.booking.status,
972 l_activity_rec.booking.player_status,
973 l_activity_rec.booking.booking_id,
974 l_activity_rec.booking.delegate_person_id,
975 l_activity_rec.booking.is_history_flag,
976 l_activity_rec.booking.date_status_changed,
977 l_activity_rec.booking.successful_attendance_flag,
978 l_activity_rec.booking.is_mandatory_enrollment,
979 l_event_id;
980 IF get_activity_rec%NOTFOUND AND l_count = 1 THEN
981 RAISE invalid_person_id;
982 ELSIF get_activity_rec%NOTFOUND THEN
983 EXIT;
984 END IF;
985
986 get_events(p_event_id => l_event_id,
987 p_activity_version_id => l_activity_rec.activity_version_id,
988 p_events_tbl => l_activity_rec.events);
989
990 l_activity_tbl.EXTEND(1);
991 l_activity_tbl(l_count) := l_activity_rec;
992 l_count := l_count + 1;
993
994 END LOOP;
995 CLOSE get_activity_rec;
996 p_activity_tbl := l_activity_tbl;
997 END get_training_details_internal;
998
999 PROCEDURE get_training_details_internal(p_booking_id IN OTA_DELEGATE_BOOKINGS.BOOKING_ID%TYPE
1000 ,p_activity_tbl OUT NOCOPY activity_tabletype)
1001 IS
1002 CURSOR get_activity_rec IS
1003 SELECT distinct
1004 a.activity_version_id Activity_Version_Id,
1005 a.version_name Activity_Version_Name,
1006 a.description Activity_Description,
1007 a.objectives Activity_Objectives,
1008 a.intended_audience Activity_Audience,
1009 a.keywords Activity_Keywords,
1010 a.tav_information_category ,
1011 a.tav_information1,
1012 a.tav_information2,
1013 a.tav_information3,
1014 a.tav_information4,
1015 a.tav_information5,
1016 a.tav_information6,
1017 a.tav_information7,
1018 a.tav_information8,
1019 a.tav_information9,
1020 a.tav_information10,
1021 a.tav_information11,
1022 a.tav_information12,
1023 a.tav_information13,
1024 a.tav_information14,
1025 a.tav_information15,
1026 a.tav_information16,
1027 a.tav_information17,
1028 a.tav_information18,
1029 a.tav_information19,
1030 a.tav_information20,
1031 a.Version_Code Activity_Version_Code,
1032 hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
1033 a.professional_credits,
1034 hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
1035 a.Controlling_Person_Id Controlling_Person_Id,
1036 ST.NAME status,
1037 DECODE(C.ONLINE_FLAG ,'Y',OTA_LO_UTILITY.get_enroll_lo_status(NVL(D.delegate_person_id, D.contact_id), DECODE(D.delegate_person_id, NULL, 'C', 'E') , E.EVENT_ID,D.BOOKING_STATUS_TYPE_ID,D.BOOKING_ID,null,'N'), null) player_status
1038 ,D.BOOKING_ID
1039 ,D.DELEGATE_PERSON_ID
1040 ,D.IS_HISTORY_FLAG
1041 ,D.DATE_STATUS_CHANGED
1042 ,D.SUCCESSFUL_ATTENDANCE_FLAG
1043 , nvl(D.IS_MANDATORY_ENROLLMENT,'N') is_mandatory_enrollment
1044 ,E.EVENT_ID
1045 FROM ota_activity_versions_vl a ,
1046 OTA_EVENTS E,
1047 OTA_EVENTS_TL ET,
1048 OTA_DELEGATE_BOOKINGS D,
1049 OTA_BOOKING_STATUS_TYPES S,
1050 OTA_BOOKING_STATUS_TYPES_TL ST,
1051 OTA_OFFERINGS O,
1052 OTA_OFFERINGS_TL OT,
1053 OTA_CATEGORY_USAGES C,
1054 OTA_CATEGORY_USAGES_TL CT,
1055 OTA_ACTIVITY_VERSIONS_TL OAV,
1056 OTA_EVALUATIONS EVAL
1057 WHERE a.activity_version_id = e.activity_version_id
1058 AND OAV.ACTIVITY_VERSION_ID = a.ACTIVITY_VERSION_ID
1059 AND E.EVENT_ID=D.EVENT_ID
1060 AND S.BOOKING_STATUS_TYPE_ID=D.BOOKING_STATUS_TYPE_ID
1061 --AND E.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
1062 AND E.PARENT_OFFERING_ID=O.OFFERING_ID
1063 AND O.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
1064 AND a.ACTIVITY_VERSION_ID = O.ACTIVITY_VERSION_ID
1065 AND OAV.LANGUAGE=USERENV('LANG')
1066 AND E.EVENT_ID = ET.EVENT_ID
1067 AND ET.LANGUAGE=USERENV('LANG')
1068 AND S.BOOKING_STATUS_TYPE_ID = ST.BOOKING_STATUS_TYPE_ID
1069 AND ST.LANGUAGE=USERENV('LANG')
1070 AND O.OFFERING_ID = OT.OFFERING_ID
1071 AND OT.LANGUAGE=USERENV('LANG')
1072 AND C.CATEGORY_USAGE_ID = CT.CATEGORY_USAGE_ID
1073 AND CT.LANGUAGE=USERENV('LANG')
1074 AND E.EVENT_TYPE IN ('SCHEDULED','SELFPACED')
1075 AND E.BOOK_INDEPENDENT_FLAG = 'N'
1076 AND E.EVENT_ID = EVAL.OBJECT_ID(+)
1077 AND (EVAL.OBJECT_TYPE is null or EVAL.OBJECT_TYPE = 'E')
1078 AND D.BOOKING_ID = p_booking_id;
1079
1080 l_activity_rec activity_rectype;
1081 l_activity_tbl activity_tabletype;
1082 l_event_id ota_events.event_id%TYPE;
1083 BEGIN
1084 l_activity_tbl := activity_tabletype();
1085
1086 OPEN get_activity_rec;
1087 FETCH get_activity_rec INTO
1088 l_activity_rec.activity_version_id,
1089 l_activity_rec.activity_version_name,
1090 l_activity_rec.activity_description,
1091 l_activity_rec.activity_objectives,
1092 l_activity_rec.activity_audience,
1093 l_activity_rec.activity_keywords,
1094 l_activity_rec.tav_information_category,
1095 l_activity_rec.tav_information1,
1096 l_activity_rec.tav_information2,
1097 l_activity_rec.tav_information3,
1098 l_activity_rec.tav_information4,
1099 l_activity_rec.tav_information5,
1100 l_activity_rec.tav_information6,
1101 l_activity_rec.tav_information7,
1102 l_activity_rec.tav_information8,
1103 l_activity_rec.tav_information9,
1104 l_activity_rec.tav_information10,
1105 l_activity_rec.tav_information11,
1106 l_activity_rec.tav_information12,
1107 l_activity_rec.tav_information13,
1108 l_activity_rec.tav_information14,
1109 l_activity_rec.tav_information15,
1110 l_activity_rec.tav_information16,
1111 l_activity_rec.tav_information17,
1112 l_activity_rec.tav_information18,
1113 l_activity_rec.tav_information19,
1114 l_activity_rec.tav_information20,
1115 l_activity_rec.activity_version_code,
1116 l_activity_rec.success_criteria,
1117 l_activity_rec.professional_credits,
1118 l_activity_rec.professional_credit_meaning,
1119 l_activity_rec.controlling_person_id,
1120 l_activity_rec.booking.status,
1121 l_activity_rec.booking.player_status,
1122 l_activity_rec.booking.booking_id,
1123 l_activity_rec.booking.delegate_person_id,
1124 l_activity_rec.booking.is_history_flag,
1125 l_activity_rec.booking.date_status_changed,
1126 l_activity_rec.booking.successful_attendance_flag,
1127 l_activity_rec.booking.is_mandatory_enrollment,
1128 l_event_id;
1129 IF get_activity_rec%NOTFOUND THEN
1130 RAISE invalid_delegate_booking_id;
1131 END IF;
1132
1133 get_events(p_event_id => l_event_id,
1134 p_activity_version_id => l_activity_rec.activity_version_id,
1135 p_events_tbl => l_activity_rec.events);
1136
1137 l_activity_tbl.EXTEND(1);
1138 l_activity_tbl(1) := l_activity_rec;
1139 CLOSE get_activity_rec;
1140 p_activity_tbl := l_activity_tbl;
1141 END get_training_details_internal;
1142
1143
1144
1145 PROCEDURE get_training_details_internal(p_course_id IN ota_activity_versions.activity_id%TYPE
1146 ,p_activity_tbl OUT NOCOPY activity_tabletype)
1147 IS
1148 CURSOR get_activity_rec IS
1149 SELECT
1150 a.activity_version_id Activity_Version_Id,
1151 a.version_name Activity_Version_Name,
1152 a.description Activity_Description,
1153 a.objectives Activity_Objectives,
1154 a.intended_audience Activity_Audience,
1155 a.keywords Activity_Keywords,
1156 a.tav_information_category ,
1157 a.tav_information1,
1158 a.tav_information2,
1159 a.tav_information3,
1160 a.tav_information4,
1161 a.tav_information5,
1162 a.tav_information6,
1163 a.tav_information7,
1164 a.tav_information8,
1165 a.tav_information9,
1166 a.tav_information10,
1167 a.tav_information11,
1168 a.tav_information12,
1169 a.tav_information13,
1170 a.tav_information14,
1171 a.tav_information15,
1172 a.tav_information16,
1173 a.tav_information17,
1174 a.tav_information18,
1175 a.tav_information19,
1176 a.tav_information20,
1177 a.Version_Code Activity_Version_Code,
1178 hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
1179 a.professional_credits,
1180 hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
1181 a.Controlling_Person_Id Controlling_Person_Id
1182 FROM ota_activity_versions_vl a
1183 WHERE a.activity_version_id = p_course_id;
1184 l_activity_rec activity_rectype;
1185 l_activity_tbl activity_tabletype;
1186 BEGIN
1187 l_activity_tbl := activity_tabletype();
1188 OPEN get_activity_rec;
1189 FETCH get_activity_rec INTO
1190 l_activity_rec.activity_version_id,
1191 l_activity_rec.activity_version_name,
1192 l_activity_rec.activity_description,
1193 l_activity_rec.activity_objectives,
1194 l_activity_rec.activity_audience,
1195 l_activity_rec.activity_keywords,
1196 l_activity_rec.tav_information_category,
1197 l_activity_rec.tav_information1,
1198 l_activity_rec.tav_information2,
1199 l_activity_rec.tav_information3,
1200 l_activity_rec.tav_information4,
1201 l_activity_rec.tav_information5,
1202 l_activity_rec.tav_information6,
1203 l_activity_rec.tav_information7,
1204 l_activity_rec.tav_information8,
1205 l_activity_rec.tav_information9,
1206 l_activity_rec.tav_information10,
1207 l_activity_rec.tav_information11,
1208 l_activity_rec.tav_information12,
1209 l_activity_rec.tav_information13,
1210 l_activity_rec.tav_information14,
1211 l_activity_rec.tav_information15,
1212 l_activity_rec.tav_information16,
1213 l_activity_rec.tav_information17,
1214 l_activity_rec.tav_information18,
1215 l_activity_rec.tav_information19,
1216 l_activity_rec.tav_information20,
1217 l_activity_rec.activity_version_code,
1218 l_activity_rec.success_criteria,
1219 l_activity_rec.professional_credits,
1220 l_activity_rec.professional_credit_meaning,
1221 l_activity_rec.controlling_person_id;
1222 IF get_activity_rec%NOTFOUND THEN
1223 RAISE invalid_course_id;
1224 END IF;
1225
1226 get_events(p_event_id => null,
1227 p_activity_version_id => l_activity_rec.activity_version_id,
1228 p_events_tbl => l_activity_rec.events);
1229
1230 l_activity_tbl.EXTEND(1);
1231 l_activity_tbl(1) := l_activity_rec;
1232 CLOSE get_activity_rec;
1233 p_activity_tbl := l_activity_tbl;
1234 END get_training_details_internal;
1235
1236 PROCEDURE get_training_details_internal(p_start_person_id IN ota_delegate_bookings.delegate_person_id%TYPE,
1237 p_end_person_id IN ota_delegate_bookings.delegate_person_id%TYPE,
1238 p_activity_tbl OUT NOCOPY activity_tabletype)
1239 IS
1240 CURSOR get_activity_rec IS
1241 SELECT distinct
1242 a.activity_version_id Activity_Version_Id,
1243 a.version_name Activity_Version_Name,
1244 a.description Activity_Description,
1245 a.objectives Activity_Objectives,
1246 a.intended_audience Activity_Audience,
1247 a.keywords Activity_Keywords,
1248 a.tav_information_category ,
1249 a.tav_information1,
1250 a.tav_information2,
1251 a.tav_information3,
1252 a.tav_information4,
1253 a.tav_information5,
1254 a.tav_information6,
1255 a.tav_information7,
1256 a.tav_information8,
1257 a.tav_information9,
1258 a.tav_information10,
1259 a.tav_information11,
1260 a.tav_information12,
1261 a.tav_information13,
1262 a.tav_information14,
1263 a.tav_information15,
1264 a.tav_information16,
1265 a.tav_information17,
1266 a.tav_information18,
1267 a.tav_information19,
1268 a.tav_information20,
1269 a.Version_Code Activity_Version_Code,
1270 hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
1271 a.professional_credits,
1272 hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
1273 a.Controlling_Person_Id Controlling_Person_Id,
1274 ST.NAME status,
1275 DECODE(C.ONLINE_FLAG ,'Y',OTA_LO_UTILITY.get_enroll_lo_status(NVL(D.delegate_person_id, D.contact_id), DECODE(D.delegate_person_id, NULL, 'C', 'E') , E.EVENT_ID,D.BOOKING_STATUS_TYPE_ID,D.BOOKING_ID,null,'N'), null) player_status
1276 ,D.BOOKING_ID
1277 ,D.DELEGATE_PERSON_ID
1278 ,D.IS_HISTORY_FLAG
1279 ,D.DATE_STATUS_CHANGED
1280 ,D.SUCCESSFUL_ATTENDANCE_FLAG
1281 , nvl(D.IS_MANDATORY_ENROLLMENT,'N') is_mandatory_enrollment
1282 ,E.EVENT_ID
1283 FROM ota_activity_versions_vl a ,
1284 OTA_EVENTS E,
1285 OTA_EVENTS_TL ET,
1286 OTA_DELEGATE_BOOKINGS D,
1287 OTA_BOOKING_STATUS_TYPES S,
1288 OTA_BOOKING_STATUS_TYPES_TL ST,
1289 OTA_OFFERINGS O,
1290 OTA_OFFERINGS_TL OT,
1291 OTA_CATEGORY_USAGES C,
1292 OTA_CATEGORY_USAGES_TL CT,
1293 OTA_ACTIVITY_VERSIONS_TL OAV,
1294 OTA_EVALUATIONS EVAL
1295 WHERE a.activity_version_id = e.activity_version_id
1296 AND OAV.ACTIVITY_VERSION_ID = a.ACTIVITY_VERSION_ID
1297 AND E.EVENT_ID=D.EVENT_ID
1298 AND S.BOOKING_STATUS_TYPE_ID=D.BOOKING_STATUS_TYPE_ID
1299 --AND E.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
1300 AND E.PARENT_OFFERING_ID=O.OFFERING_ID
1301 AND O.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
1302 AND a.ACTIVITY_VERSION_ID = O.ACTIVITY_VERSION_ID
1303 AND OAV.LANGUAGE=USERENV('LANG')
1304 AND E.EVENT_ID = ET.EVENT_ID
1305 AND ET.LANGUAGE=USERENV('LANG')
1306 AND S.BOOKING_STATUS_TYPE_ID = ST.BOOKING_STATUS_TYPE_ID
1307 AND ST.LANGUAGE=USERENV('LANG')
1308 AND O.OFFERING_ID = OT.OFFERING_ID
1309 AND OT.LANGUAGE=USERENV('LANG')
1310 AND C.CATEGORY_USAGE_ID = CT.CATEGORY_USAGE_ID
1311 AND CT.LANGUAGE=USERENV('LANG')
1312 AND E.EVENT_TYPE IN ('SCHEDULED','SELFPACED')
1313 AND E.BOOK_INDEPENDENT_FLAG = 'N'
1314 AND E.EVENT_ID = EVAL.OBJECT_ID(+)
1315 AND (EVAL.OBJECT_TYPE is null or EVAL.OBJECT_TYPE = 'E')
1316 AND D.SUCCESSFUL_ATTENDANCE_FLAG = 'Y'
1317 AND D.delegate_person_id BETWEEN nvl(p_start_person_id, D.delegate_person_id)
1318 AND nvl(p_end_person_id, D.delegate_person_id);
1319
1320 l_activity_rec activity_rectype;
1321 l_activity_tbl activity_tabletype;
1322 l_count NUMBER := 1;
1323 l_event_id ota_events.event_id%TYPE;
1324 BEGIN
1325 l_activity_tbl := activity_tabletype();
1326
1327 OPEN get_activity_rec;
1328 LOOP
1329 FETCH get_activity_rec INTO
1330 l_activity_rec.activity_version_id,
1331 l_activity_rec.activity_version_name,
1332 l_activity_rec.activity_description,
1333 l_activity_rec.activity_objectives,
1334 l_activity_rec.activity_audience,
1335 l_activity_rec.activity_keywords,
1336 l_activity_rec.tav_information_category,
1337 l_activity_rec.tav_information1,
1338 l_activity_rec.tav_information2,
1339 l_activity_rec.tav_information3,
1340 l_activity_rec.tav_information4,
1341 l_activity_rec.tav_information5,
1342 l_activity_rec.tav_information6,
1343 l_activity_rec.tav_information7,
1344 l_activity_rec.tav_information8,
1345 l_activity_rec.tav_information9,
1346 l_activity_rec.tav_information10,
1347 l_activity_rec.tav_information11,
1348 l_activity_rec.tav_information12,
1349 l_activity_rec.tav_information13,
1350 l_activity_rec.tav_information14,
1351 l_activity_rec.tav_information15,
1352 l_activity_rec.tav_information16,
1353 l_activity_rec.tav_information17,
1354 l_activity_rec.tav_information18,
1355 l_activity_rec.tav_information19,
1356 l_activity_rec.tav_information20,
1357 l_activity_rec.activity_version_code,
1358 l_activity_rec.success_criteria,
1359 l_activity_rec.professional_credits,
1360 l_activity_rec.professional_credit_meaning,
1361 l_activity_rec.controlling_person_id,
1362 l_activity_rec.booking.status,
1363 l_activity_rec.booking.player_status,
1364 l_activity_rec.booking.BOOKING_ID,
1365 l_activity_rec.booking.delegate_person_id,
1366 l_activity_rec.booking.IS_HISTORY_FLAG,
1367 l_activity_rec.booking.DATE_STATUS_CHANGED,
1368 l_activity_rec.booking.SUCCESSFUL_ATTENDANCE_FLAG,
1369 l_activity_rec.booking.is_mandatory_enrollment,
1370 l_event_id;
1371 IF get_activity_rec%NOTFOUND THEN
1372 EXIT;
1373 END IF;
1374
1375 get_events(p_event_id => l_event_id,
1376 p_activity_version_id => l_activity_rec.activity_version_id,
1377 p_events_tbl => l_activity_rec.events);
1378
1379 l_activity_tbl.EXTEND(1);
1380 l_activity_tbl(l_count) := l_activity_rec;
1381 l_count := l_count + 1;
1382 END LOOP;
1383 CLOSE get_activity_rec;
1384 p_activity_tbl := l_activity_tbl;
1385 END get_training_details_internal;
1386
1387 PROCEDURE get_booking_status(p_delegate_booking_id IN ota_delegate_bookings.booking_id%TYPE,
1388 p_status_name OUT NOCOPY ota_booking_status_types_VL.name%TYPE,
1389 p_status_type OUT NOCOPY ota_booking_status_types_VL.type%TYPE)
1390 IS
1391 CURSOR get_status IS
1392 SELECT s.name,
1393 s.type
1394 FROM ota_delegate_bookings b,
1395 ota_booking_status_types_VL s
1396 WHERE b.booking_status_type_id = s.booking_status_type_id
1397 AND b.booking_id = p_delegate_booking_id;
1398
1399 l_name ota_booking_status_types_VL.name%TYPE;
1400 l_type ota_booking_status_types_VL.type%TYPE;
1401
1402 BEGIN
1403 OPEN get_status;
1404 FETCH get_status INTO
1405 l_name,
1406 l_type;
1407 CLOSE get_status;
1408 p_status_name := l_name;
1409 p_status_type := l_type;
1410 END get_booking_status;
1411
1412 PROCEDURE get_training_details(p_query_options IN course_query_input_rectype,
1413 p_training OUT NOCOPY activity_tabletype,
1414 p_certifications OUT NOCOPY certification_tabletype,
1415 p_ispartofcertification OUT NOCOPY BOOLEAN)
1416 IS
1417 l_person_id ota_delegate_bookings.delegate_person_id%TYPE := NULL;
1418 l_activity_version_id ota_activity_versions.activity_id%TYPE := NULL;
1419 l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%TYPE;
1420 l_event_id ota_events.event_id%TYPE := NULL;
1421 l_activity_rec activity_rectype;
1422 l_event_rec event_rectype;
1423
1424 l_events_tbl event_tabletype;
1425 l_activity_tbl activity_tabletype;
1426
1427 l_event_action VARCHAR2(50);
1428 l_is_part_of_certification BOOLEAN := FALSE;
1429 l_status_name ota_booking_status_types_VL.name%TYPE;
1430 l_status_type ota_booking_status_types_VL.type%TYPE;
1431
1432 --certification input options
1433 l_cert_input_options cert_query_input_rectype;
1434 l_certifications_tbl certification_tabletype;
1435 l_query_options query_options;
1436 BEGIN
1437
1438 IF p_query_options.person_id IS NOT NULL THEN
1439 IF p_query_options.view_history THEN
1440 get_training_details_internal(p_person_id => p_query_options.person_id
1441 ,p_start_person_id => p_query_options.start_person_id
1442 ,p_end_person_id => p_query_options.end_person_id
1443 ,p_view_history => 'Y'
1444 ,p_activity_tbl => l_activity_tbl);
1445 ELSE
1446 get_training_details_internal(p_person_id => p_query_options.person_id
1447 ,p_start_person_id => p_query_options.start_person_id
1448 ,p_end_person_id => p_query_options.end_person_id
1449 ,p_view_history => 'N'
1450 ,p_activity_tbl => l_activity_tbl);
1451 END IF;
1452 ELSIF p_query_options.course_id IS NOT NULL THEN
1453 get_training_details_internal(p_course_id => p_query_options.course_id
1454 ,p_activity_tbl => l_activity_tbl);
1455 ELSIF p_query_options.delegate_booking_id IS NOT NULL THEN
1456 get_training_details_internal(p_booking_id => p_query_options.delegate_booking_id
1457 ,p_activity_tbl => l_activity_tbl);
1458 ELSE
1459 get_training_details_internal(p_start_person_id => p_query_options.start_person_id
1460 ,p_end_person_id => p_query_options.end_person_id
1461 ,p_activity_tbl => l_activity_tbl);
1462 END IF;
1463
1464 --get status of booking
1465 get_booking_status(p_delegate_booking_id => p_query_options.delegate_booking_id,
1466 p_status_name => l_status_name,
1467 p_status_type => l_status_type);
1468
1469 --set event action in training record
1470 IF l_status_type='A' AND (upper(l_status_name) ='ATTENDED' OR upper(l_status_name) ='PASSED') THEN
1471 l_event_action := 'TRAINING_COMPLETED';
1472 ELSIF l_status_type='A' AND (upper(l_status_name) ='FAILED') THEN
1473 l_event_action := 'TRAINING_FAILED';
1474 ELSIF l_status_type='R' THEN
1475 l_event_action := 'TRAINING_REQUESTED';
1476 ELSIF l_status_type='P' THEN
1477 l_event_action := 'TRAINING_ENROLLED';
1478 ELSIF l_status_type='W' THEN
1479 l_event_action := 'TRAINING_WAITLISTED';
1480 ELSIF l_status_type='C' THEN
1481 l_event_action := 'TRAINING_CANCELLED';
1482 END IF;
1483
1484 --if part of certification fetch the certification and pass it to the output also
1485 IF p_query_options.delegate_booking_id IS NOT NULL THEN
1486
1487 l_is_part_of_certification := is_part_of_certification(p_query_options.delegate_booking_id,l_cert_enrollment_id);
1488
1489 IF l_is_part_of_certification THEN
1490 l_query_options(1) := 'DETAIL';
1491 l_query_options(2) := 'COMPETENCY';
1492 l_query_options(3) := 'COMPONENT';
1493 l_cert_input_options.options := l_query_options;
1494 l_cert_input_options.cert_enrollment_id := l_cert_enrollment_id;
1495 get_certification_details(p_query_options => l_cert_input_options,
1496 p_certifications => l_certifications_tbl);
1497
1498 -- set output
1499 p_ispartofcertification := l_is_part_of_certification;
1500 p_certifications := l_certifications_tbl;
1501 ELSE
1502 p_certifications := certification_tabletype();
1503 p_ispartofcertification := NULL;
1504 END IF;
1505 ELSE
1506 p_certifications := certification_tabletype();
1507 p_ispartofcertification := NULL;
1508 END IF;
1509
1510 --set the event action if delegate booking is passed
1511 IF p_query_options.delegate_booking_id IS NOT NULL AND l_activity_tbl.COUNT = 1 THEN
1512 l_activity_tbl(1).event_action := l_event_action;
1513 END IF;
1514
1515 p_training := l_activity_tbl;
1516 EXCEPTION
1517 WHEN invalid_delegate_booking_id THEN
1518 p_training := activity_tabletype();
1519 p_certifications := certification_tabletype();
1520 p_ispartofcertification := NULL;
1521 WHEN invalid_course_id THEN
1522 p_training := activity_tabletype();
1523 p_certifications := certification_tabletype();
1524 p_ispartofcertification := NULL;
1525 WHEN invalid_person_id THEN
1526 p_training := activity_tabletype();
1527 p_certifications := certification_tabletype();
1528 p_ispartofcertification := NULL;
1529 END get_training_details;
1530 END ota_training_record;