[Home] [Help]
PACKAGE BODY: APPS.OTA_TRAINING_RECORD
Source
1 PACKAGE BODY ota_training_record AS
2 /* $Header: ottraqry.pkb 120.0.12010000.8 2009/01/29 14:16:07 srgnanas 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 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'), ST.NAME) STATUS
862 ,D.BOOKING_ID
863 ,D.DELEGATE_PERSON_ID
864 ,D.IS_HISTORY_FLAG
865 ,D.DATE_STATUS_CHANGED
866 ,D.SUCCESSFUL_ATTENDANCE_FLAG
867 , nvl(D.IS_MANDATORY_ENROLLMENT,'N') is_mandatory_enrollment
868 ,E.EVENT_ID
869 FROM ota_activity_versions_vl a ,
870 OTA_EVENTS E,
871 OTA_EVENTS_TL ET,
872 OTA_DELEGATE_BOOKINGS D,
873 OTA_BOOKING_STATUS_TYPES S,
874 OTA_BOOKING_STATUS_TYPES_TL ST,
875 OTA_OFFERINGS O,
876 OTA_OFFERINGS_TL OT,
877 OTA_CATEGORY_USAGES C,
878 OTA_CATEGORY_USAGES_TL CT,
879 OTA_ACTIVITY_VERSIONS_TL OAV,
880 OTA_EVALUATIONS EVAL
881 WHERE a.activity_version_id = e.activity_version_id
882 AND OAV.ACTIVITY_VERSION_ID = a.ACTIVITY_VERSION_ID
883 AND E.EVENT_ID=D.EVENT_ID
884 AND S.BOOKING_STATUS_TYPE_ID=D.BOOKING_STATUS_TYPE_ID
885 --AND E.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
886 AND E.PARENT_OFFERING_ID=O.OFFERING_ID
887 AND O.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
888 AND a.ACTIVITY_VERSION_ID = O.ACTIVITY_VERSION_ID
889 AND OAV.LANGUAGE=USERENV('LANG')
890 AND E.EVENT_ID = ET.EVENT_ID
891 AND ET.LANGUAGE=USERENV('LANG')
892 AND S.BOOKING_STATUS_TYPE_ID = ST.BOOKING_STATUS_TYPE_ID
893 AND ST.LANGUAGE=USERENV('LANG')
894 AND O.OFFERING_ID = OT.OFFERING_ID
895 AND OT.LANGUAGE=USERENV('LANG')
896 AND C.CATEGORY_USAGE_ID = CT.CATEGORY_USAGE_ID
897 AND CT.LANGUAGE=USERENV('LANG')
898 AND E.EVENT_TYPE IN ('SCHEDULED','SELFPACED')
899 AND E.BOOK_INDEPENDENT_FLAG = 'N'
900 AND E.EVENT_ID = EVAL.OBJECT_ID(+)
901 AND (EVAL.OBJECT_TYPE is null or EVAL.OBJECT_TYPE = 'E')
902 AND nvl(p_person_id, D.delegate_person_id) = D.delegate_person_id
903 AND D.delegate_person_id BETWEEN nvl(p_start_person_id, D.delegate_person_id)
904 AND nvl(p_end_person_id, D.delegate_person_id)
905 AND (( ( p_view_history = 'N' AND
906 ((D.IS_HISTORY_FLAG IS NULL OR D.IS_HISTORY_FLAG = 'N')
907 AND ( E.COURSE_END_DATE IS NULL
908 OR TO_DATE( TO_CHAR(nvl(E.COURSE_END_DATE,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') ||
909 ' ' || nvl(E.COURSE_END_TIME,'23:59'), 'YYYY/MM/DD HH24:MI')
910 >= OTA_TIMEZONE_UTIL.CONVERT_DATE(TRUNC(SYSDATE), TO_CHAR(SYSDATE, 'HH24:MI'),
911 OTA_TIMEZONE_UTIL.GET_SERVER_TIMEZONE_CODE, E.TIMEZONE)
912 )
913 AND ((C.ONLINE_FLAG = 'Y' AND S.TYPE IN ('A','P','E')) OR (C.ONLINE_FLAG = 'N' AND S.TYPE in( 'P','E'))))))
914 OR
915 (p_view_history = 'Y'
916 AND ((S.TYPE NOT IN ('R','W'))
917 AND ((D.IS_HISTORY_FLAG = 'Y')
918 OR ( E.COURSE_END_DATE IS NOT NULL
919 AND TO_DATE( TO_CHAR(nvl(E.COURSE_END_DATE,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD')
920 || ' ' || nvl(E.COURSE_END_TIME,'23:59'), 'YYYY/MM/DD HH24:MI')
921 < OTA_TIMEZONE_UTIL.CONVERT_DATE(TRUNC(SYSDATE), TO_CHAR(SYSDATE, 'HH24:MI'),
922 OTA_TIMEZONE_UTIL.GET_SERVER_TIMEZONE_CODE, E.TIMEZONE)
923 )
924 OR (C.ONLINE_FLAG = 'N' AND S.TYPE IN ('A','C'))
925 OR (C.ONLINE_FLAG = 'Y' AND S.TYPE IN ('C'))
926 )
927 )));
928
929 l_activity_rec activity_rectype;
930 l_activity_tbl activity_tabletype;
931 l_count NUMBER := 1;
932 l_event_id ota_events.event_id%TYPE;
933 BEGIN
934 l_activity_tbl := activity_tabletype();
935 OPEN get_activity_rec;
936 LOOP
937 FETCH get_activity_rec INTO
938 l_activity_rec.activity_version_id,
939 l_activity_rec.activity_version_name,
940 l_activity_rec.activity_description,
941 l_activity_rec.activity_objectives,
942 l_activity_rec.activity_audience,
943 l_activity_rec.activity_keywords,
944 l_activity_rec.tav_information_category,
945 l_activity_rec.tav_information1,
946 l_activity_rec.tav_information2,
947 l_activity_rec.tav_information3,
948 l_activity_rec.tav_information4,
949 l_activity_rec.tav_information5,
950 l_activity_rec.tav_information6,
951 l_activity_rec.tav_information7,
952 l_activity_rec.tav_information8,
953 l_activity_rec.tav_information9,
954 l_activity_rec.tav_information10,
955 l_activity_rec.tav_information11,
956 l_activity_rec.tav_information12,
957 l_activity_rec.tav_information13,
958 l_activity_rec.tav_information14,
959 l_activity_rec.tav_information15,
960 l_activity_rec.tav_information16,
961 l_activity_rec.tav_information17,
962 l_activity_rec.tav_information18,
963 l_activity_rec.tav_information19,
964 l_activity_rec.tav_information20,
965 l_activity_rec.activity_version_code,
966 l_activity_rec.success_criteria,
967 l_activity_rec.professional_credits,
968 l_activity_rec.professional_credit_meaning,
969 l_activity_rec.controlling_person_id,
970 l_activity_rec.booking.status,
971 l_activity_rec.booking.booking_id,
972 l_activity_rec.booking.delegate_person_id,
973 l_activity_rec.booking.is_history_flag,
974 l_activity_rec.booking.date_status_changed,
975 l_activity_rec.booking.successful_attendance_flag,
976 l_activity_rec.booking.is_mandatory_enrollment,
977 l_event_id;
978 IF get_activity_rec%NOTFOUND AND l_count = 1 THEN
979 RAISE invalid_person_id;
980 ELSIF get_activity_rec%NOTFOUND THEN
981 EXIT;
982 END IF;
983
984 get_events(p_event_id => l_event_id,
985 p_activity_version_id => l_activity_rec.activity_version_id,
986 p_events_tbl => l_activity_rec.events);
987
988 l_activity_tbl.EXTEND(1);
989 l_activity_tbl(l_count) := l_activity_rec;
990 l_count := l_count + 1;
991
992 END LOOP;
993 CLOSE get_activity_rec;
994 p_activity_tbl := l_activity_tbl;
995 END get_training_details_internal;
996
997 PROCEDURE get_training_details_internal(p_booking_id IN OTA_DELEGATE_BOOKINGS.BOOKING_ID%TYPE
998 ,p_activity_tbl OUT NOCOPY activity_tabletype)
999 IS
1000 CURSOR get_activity_rec IS
1001 SELECT distinct
1002 a.activity_version_id Activity_Version_Id,
1003 a.version_name Activity_Version_Name,
1004 a.description Activity_Description,
1005 a.objectives Activity_Objectives,
1006 a.intended_audience Activity_Audience,
1007 a.keywords Activity_Keywords,
1008 a.tav_information_category ,
1009 a.tav_information1,
1010 a.tav_information2,
1011 a.tav_information3,
1012 a.tav_information4,
1013 a.tav_information5,
1014 a.tav_information6,
1015 a.tav_information7,
1016 a.tav_information8,
1017 a.tav_information9,
1018 a.tav_information10,
1019 a.tav_information11,
1020 a.tav_information12,
1021 a.tav_information13,
1022 a.tav_information14,
1023 a.tav_information15,
1024 a.tav_information16,
1025 a.tav_information17,
1026 a.tav_information18,
1027 a.tav_information19,
1028 a.tav_information20,
1029 a.Version_Code Activity_Version_Code,
1030 hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
1031 a.professional_credits,
1032 hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
1033 a.Controlling_Person_Id Controlling_Person_Id,
1034 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'), ST.NAME) STATUS
1035 ,D.BOOKING_ID
1036 ,D.DELEGATE_PERSON_ID
1037 ,D.IS_HISTORY_FLAG
1038 ,D.DATE_STATUS_CHANGED
1039 ,D.SUCCESSFUL_ATTENDANCE_FLAG
1040 , nvl(D.IS_MANDATORY_ENROLLMENT,'N') is_mandatory_enrollment
1041 ,E.EVENT_ID
1042 FROM ota_activity_versions_vl a ,
1043 OTA_EVENTS E,
1044 OTA_EVENTS_TL ET,
1045 OTA_DELEGATE_BOOKINGS D,
1046 OTA_BOOKING_STATUS_TYPES S,
1047 OTA_BOOKING_STATUS_TYPES_TL ST,
1048 OTA_OFFERINGS O,
1049 OTA_OFFERINGS_TL OT,
1050 OTA_CATEGORY_USAGES C,
1051 OTA_CATEGORY_USAGES_TL CT,
1052 OTA_ACTIVITY_VERSIONS_TL OAV,
1053 OTA_EVALUATIONS EVAL
1054 WHERE a.activity_version_id = e.activity_version_id
1055 AND OAV.ACTIVITY_VERSION_ID = a.ACTIVITY_VERSION_ID
1056 AND E.EVENT_ID=D.EVENT_ID
1057 AND S.BOOKING_STATUS_TYPE_ID=D.BOOKING_STATUS_TYPE_ID
1058 --AND E.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
1059 AND E.PARENT_OFFERING_ID=O.OFFERING_ID
1060 AND O.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
1061 AND a.ACTIVITY_VERSION_ID = O.ACTIVITY_VERSION_ID
1062 AND OAV.LANGUAGE=USERENV('LANG')
1063 AND E.EVENT_ID = ET.EVENT_ID
1064 AND ET.LANGUAGE=USERENV('LANG')
1065 AND S.BOOKING_STATUS_TYPE_ID = ST.BOOKING_STATUS_TYPE_ID
1066 AND ST.LANGUAGE=USERENV('LANG')
1067 AND O.OFFERING_ID = OT.OFFERING_ID
1068 AND OT.LANGUAGE=USERENV('LANG')
1069 AND C.CATEGORY_USAGE_ID = CT.CATEGORY_USAGE_ID
1070 AND CT.LANGUAGE=USERENV('LANG')
1071 AND E.EVENT_TYPE IN ('SCHEDULED','SELFPACED')
1072 AND E.BOOK_INDEPENDENT_FLAG = 'N'
1073 AND E.EVENT_ID = EVAL.OBJECT_ID(+)
1074 AND (EVAL.OBJECT_TYPE is null or EVAL.OBJECT_TYPE = 'E')
1075 AND D.BOOKING_ID = p_booking_id;
1076
1077 l_activity_rec activity_rectype;
1078 l_activity_tbl activity_tabletype;
1079 l_event_id ota_events.event_id%TYPE;
1080 BEGIN
1081 l_activity_tbl := activity_tabletype();
1082
1083 OPEN get_activity_rec;
1084 FETCH get_activity_rec INTO
1085 l_activity_rec.activity_version_id,
1086 l_activity_rec.activity_version_name,
1087 l_activity_rec.activity_description,
1088 l_activity_rec.activity_objectives,
1089 l_activity_rec.activity_audience,
1090 l_activity_rec.activity_keywords,
1091 l_activity_rec.tav_information_category,
1092 l_activity_rec.tav_information1,
1093 l_activity_rec.tav_information2,
1094 l_activity_rec.tav_information3,
1095 l_activity_rec.tav_information4,
1096 l_activity_rec.tav_information5,
1097 l_activity_rec.tav_information6,
1098 l_activity_rec.tav_information7,
1099 l_activity_rec.tav_information8,
1100 l_activity_rec.tav_information9,
1101 l_activity_rec.tav_information10,
1102 l_activity_rec.tav_information11,
1103 l_activity_rec.tav_information12,
1104 l_activity_rec.tav_information13,
1105 l_activity_rec.tav_information14,
1106 l_activity_rec.tav_information15,
1107 l_activity_rec.tav_information16,
1108 l_activity_rec.tav_information17,
1109 l_activity_rec.tav_information18,
1110 l_activity_rec.tav_information19,
1111 l_activity_rec.tav_information20,
1112 l_activity_rec.activity_version_code,
1113 l_activity_rec.success_criteria,
1114 l_activity_rec.professional_credits,
1115 l_activity_rec.professional_credit_meaning,
1116 l_activity_rec.controlling_person_id,
1117 l_activity_rec.booking.status,
1118 l_activity_rec.booking.booking_id,
1119 l_activity_rec.booking.delegate_person_id,
1120 l_activity_rec.booking.is_history_flag,
1121 l_activity_rec.booking.date_status_changed,
1122 l_activity_rec.booking.successful_attendance_flag,
1123 l_activity_rec.booking.is_mandatory_enrollment,
1124 l_event_id;
1125 IF get_activity_rec%NOTFOUND THEN
1126 RAISE invalid_delegate_booking_id;
1127 END IF;
1128
1129 get_events(p_event_id => l_event_id,
1130 p_activity_version_id => l_activity_rec.activity_version_id,
1131 p_events_tbl => l_activity_rec.events);
1132
1133 l_activity_tbl.EXTEND(1);
1134 l_activity_tbl(1) := l_activity_rec;
1135 CLOSE get_activity_rec;
1136 p_activity_tbl := l_activity_tbl;
1137 END get_training_details_internal;
1138
1139
1140
1141 PROCEDURE get_training_details_internal(p_course_id IN ota_activity_versions.activity_id%TYPE
1142 ,p_activity_tbl OUT NOCOPY activity_tabletype)
1143 IS
1144 CURSOR get_activity_rec IS
1145 SELECT
1146 a.activity_version_id Activity_Version_Id,
1147 a.version_name Activity_Version_Name,
1148 a.description Activity_Description,
1149 a.objectives Activity_Objectives,
1150 a.intended_audience Activity_Audience,
1151 a.keywords Activity_Keywords,
1152 a.tav_information_category ,
1153 a.tav_information1,
1154 a.tav_information2,
1155 a.tav_information3,
1156 a.tav_information4,
1157 a.tav_information5,
1158 a.tav_information6,
1159 a.tav_information7,
1160 a.tav_information8,
1161 a.tav_information9,
1162 a.tav_information10,
1163 a.tav_information11,
1164 a.tav_information12,
1165 a.tav_information13,
1166 a.tav_information14,
1167 a.tav_information15,
1168 a.tav_information16,
1169 a.tav_information17,
1170 a.tav_information18,
1171 a.tav_information19,
1172 a.tav_information20,
1173 a.Version_Code Activity_Version_Code,
1174 hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
1175 a.professional_credits,
1176 hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
1177 a.Controlling_Person_Id Controlling_Person_Id
1178 FROM ota_activity_versions_vl a
1179 WHERE a.activity_version_id = p_course_id;
1180 l_activity_rec activity_rectype;
1181 l_activity_tbl activity_tabletype;
1182 BEGIN
1183 l_activity_tbl := activity_tabletype();
1184 OPEN get_activity_rec;
1185 FETCH get_activity_rec INTO
1186 l_activity_rec.activity_version_id,
1187 l_activity_rec.activity_version_name,
1188 l_activity_rec.activity_description,
1189 l_activity_rec.activity_objectives,
1190 l_activity_rec.activity_audience,
1191 l_activity_rec.activity_keywords,
1192 l_activity_rec.tav_information_category,
1193 l_activity_rec.tav_information1,
1194 l_activity_rec.tav_information2,
1195 l_activity_rec.tav_information3,
1196 l_activity_rec.tav_information4,
1197 l_activity_rec.tav_information5,
1198 l_activity_rec.tav_information6,
1199 l_activity_rec.tav_information7,
1200 l_activity_rec.tav_information8,
1201 l_activity_rec.tav_information9,
1202 l_activity_rec.tav_information10,
1203 l_activity_rec.tav_information11,
1204 l_activity_rec.tav_information12,
1205 l_activity_rec.tav_information13,
1206 l_activity_rec.tav_information14,
1207 l_activity_rec.tav_information15,
1208 l_activity_rec.tav_information16,
1209 l_activity_rec.tav_information17,
1210 l_activity_rec.tav_information18,
1211 l_activity_rec.tav_information19,
1212 l_activity_rec.tav_information20,
1213 l_activity_rec.activity_version_code,
1214 l_activity_rec.success_criteria,
1215 l_activity_rec.professional_credits,
1216 l_activity_rec.professional_credit_meaning,
1217 l_activity_rec.controlling_person_id;
1218 IF get_activity_rec%NOTFOUND THEN
1219 RAISE invalid_course_id;
1220 END IF;
1221
1222 get_events(p_event_id => null,
1223 p_activity_version_id => l_activity_rec.activity_version_id,
1224 p_events_tbl => l_activity_rec.events);
1225
1226 l_activity_tbl.EXTEND(1);
1227 l_activity_tbl(1) := l_activity_rec;
1228 CLOSE get_activity_rec;
1229 p_activity_tbl := l_activity_tbl;
1230 END get_training_details_internal;
1231
1232 PROCEDURE get_training_details_internal(p_start_person_id IN ota_delegate_bookings.delegate_person_id%TYPE,
1233 p_end_person_id IN ota_delegate_bookings.delegate_person_id%TYPE,
1234 p_activity_tbl OUT NOCOPY activity_tabletype)
1235 IS
1236 CURSOR get_activity_rec IS
1237 SELECT distinct
1238 a.activity_version_id Activity_Version_Id,
1239 a.version_name Activity_Version_Name,
1240 a.description Activity_Description,
1241 a.objectives Activity_Objectives,
1242 a.intended_audience Activity_Audience,
1243 a.keywords Activity_Keywords,
1244 a.tav_information_category ,
1245 a.tav_information1,
1246 a.tav_information2,
1247 a.tav_information3,
1248 a.tav_information4,
1249 a.tav_information5,
1250 a.tav_information6,
1251 a.tav_information7,
1252 a.tav_information8,
1253 a.tav_information9,
1254 a.tav_information10,
1255 a.tav_information11,
1256 a.tav_information12,
1257 a.tav_information13,
1258 a.tav_information14,
1259 a.tav_information15,
1260 a.tav_information16,
1261 a.tav_information17,
1262 a.tav_information18,
1263 a.tav_information19,
1264 a.tav_information20,
1265 a.Version_Code Activity_Version_Code,
1266 hr_general_utilities.get_lookup_meaning('ACTIVITY_SUCCESS_CRITERIA', a.success_criteria) Success_Criteria,
1267 a.professional_credits,
1268 hr_general.decode_lookup('PROFESSIONAL_CREDIT_TYPE', a.professional_credit_type) Professional_Credit_Meaning,
1269 a.Controlling_Person_Id Controlling_Person_Id,
1270 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'), ST.NAME) STATUS
1271 ,D.BOOKING_ID
1272 ,D.DELEGATE_PERSON_ID
1273 ,D.IS_HISTORY_FLAG
1274 ,D.DATE_STATUS_CHANGED
1275 ,D.SUCCESSFUL_ATTENDANCE_FLAG
1276 , nvl(D.IS_MANDATORY_ENROLLMENT,'N') is_mandatory_enrollment
1277 ,E.EVENT_ID
1278 FROM ota_activity_versions_vl a ,
1279 OTA_EVENTS E,
1280 OTA_EVENTS_TL ET,
1281 OTA_DELEGATE_BOOKINGS D,
1282 OTA_BOOKING_STATUS_TYPES S,
1283 OTA_BOOKING_STATUS_TYPES_TL ST,
1284 OTA_OFFERINGS O,
1285 OTA_OFFERINGS_TL OT,
1286 OTA_CATEGORY_USAGES C,
1287 OTA_CATEGORY_USAGES_TL CT,
1288 OTA_ACTIVITY_VERSIONS_TL OAV,
1289 OTA_EVALUATIONS EVAL
1290 WHERE a.activity_version_id = e.activity_version_id
1291 AND OAV.ACTIVITY_VERSION_ID = a.ACTIVITY_VERSION_ID
1292 AND E.EVENT_ID=D.EVENT_ID
1293 AND S.BOOKING_STATUS_TYPE_ID=D.BOOKING_STATUS_TYPE_ID
1294 --AND E.BUSINESS_GROUP_ID = OTA_GENERAL.GET_BUSINESS_GROUP_ID
1295 AND E.PARENT_OFFERING_ID=O.OFFERING_ID
1296 AND O.DELIVERY_MODE_ID = C.CATEGORY_USAGE_ID
1297 AND a.ACTIVITY_VERSION_ID = O.ACTIVITY_VERSION_ID
1298 AND OAV.LANGUAGE=USERENV('LANG')
1299 AND E.EVENT_ID = ET.EVENT_ID
1300 AND ET.LANGUAGE=USERENV('LANG')
1301 AND S.BOOKING_STATUS_TYPE_ID = ST.BOOKING_STATUS_TYPE_ID
1302 AND ST.LANGUAGE=USERENV('LANG')
1303 AND O.OFFERING_ID = OT.OFFERING_ID
1304 AND OT.LANGUAGE=USERENV('LANG')
1305 AND C.CATEGORY_USAGE_ID = CT.CATEGORY_USAGE_ID
1306 AND CT.LANGUAGE=USERENV('LANG')
1307 AND E.EVENT_TYPE IN ('SCHEDULED','SELFPACED')
1308 AND E.BOOK_INDEPENDENT_FLAG = 'N'
1309 AND E.EVENT_ID = EVAL.OBJECT_ID(+)
1310 AND (EVAL.OBJECT_TYPE is null or EVAL.OBJECT_TYPE = 'E')
1311 AND D.SUCCESSFUL_ATTENDANCE_FLAG = 'Y'
1312 AND D.delegate_person_id BETWEEN nvl(p_start_person_id, D.delegate_person_id)
1313 AND nvl(p_end_person_id, D.delegate_person_id);
1314
1315 l_activity_rec activity_rectype;
1316 l_activity_tbl activity_tabletype;
1317 l_count NUMBER := 1;
1318 l_event_id ota_events.event_id%TYPE;
1319 BEGIN
1320 l_activity_tbl := activity_tabletype();
1321
1322 OPEN get_activity_rec;
1323 LOOP
1324 FETCH get_activity_rec INTO
1325 l_activity_rec.activity_version_id,
1326 l_activity_rec.activity_version_name,
1327 l_activity_rec.activity_description,
1328 l_activity_rec.activity_objectives,
1329 l_activity_rec.activity_audience,
1330 l_activity_rec.activity_keywords,
1331 l_activity_rec.tav_information_category,
1332 l_activity_rec.tav_information1,
1333 l_activity_rec.tav_information2,
1334 l_activity_rec.tav_information3,
1335 l_activity_rec.tav_information4,
1336 l_activity_rec.tav_information5,
1337 l_activity_rec.tav_information6,
1338 l_activity_rec.tav_information7,
1339 l_activity_rec.tav_information8,
1340 l_activity_rec.tav_information9,
1341 l_activity_rec.tav_information10,
1342 l_activity_rec.tav_information11,
1343 l_activity_rec.tav_information12,
1344 l_activity_rec.tav_information13,
1345 l_activity_rec.tav_information14,
1346 l_activity_rec.tav_information15,
1347 l_activity_rec.tav_information16,
1348 l_activity_rec.tav_information17,
1349 l_activity_rec.tav_information18,
1350 l_activity_rec.tav_information19,
1351 l_activity_rec.tav_information20,
1352 l_activity_rec.activity_version_code,
1353 l_activity_rec.success_criteria,
1354 l_activity_rec.professional_credits,
1355 l_activity_rec.professional_credit_meaning,
1356 l_activity_rec.controlling_person_id,
1357 l_activity_rec.booking.STATUS,
1358 l_activity_rec.booking.BOOKING_ID,
1359 l_activity_rec.booking.delegate_person_id,
1360 l_activity_rec.booking.IS_HISTORY_FLAG,
1361 l_activity_rec.booking.DATE_STATUS_CHANGED,
1362 l_activity_rec.booking.SUCCESSFUL_ATTENDANCE_FLAG,
1363 l_activity_rec.booking.is_mandatory_enrollment,
1364 l_event_id;
1365 IF get_activity_rec%NOTFOUND THEN
1366 EXIT;
1367 END IF;
1368
1369 get_events(p_event_id => l_event_id,
1370 p_activity_version_id => l_activity_rec.activity_version_id,
1371 p_events_tbl => l_activity_rec.events);
1372
1373 l_activity_tbl.EXTEND(1);
1374 l_activity_tbl(l_count) := l_activity_rec;
1375 l_count := l_count + 1;
1376 END LOOP;
1377 CLOSE get_activity_rec;
1378 p_activity_tbl := l_activity_tbl;
1379 END get_training_details_internal;
1380
1381 PROCEDURE get_booking_status(p_delegate_booking_id IN ota_delegate_bookings.booking_id%TYPE,
1382 p_status_name OUT NOCOPY ota_booking_status_types_VL.name%TYPE,
1383 p_status_type OUT NOCOPY ota_booking_status_types_VL.type%TYPE)
1384 IS
1385 CURSOR get_status IS
1386 SELECT s.name,
1387 s.type
1388 FROM ota_delegate_bookings b,
1389 ota_booking_status_types_VL s
1390 WHERE b.booking_status_type_id = s.booking_status_type_id
1391 AND b.booking_id = p_delegate_booking_id;
1392
1393 l_name ota_booking_status_types_VL.name%TYPE;
1394 l_type ota_booking_status_types_VL.type%TYPE;
1395
1396 BEGIN
1397 OPEN get_status;
1398 FETCH get_status INTO
1399 l_name,
1400 l_type;
1401 CLOSE get_status;
1402 p_status_name := l_name;
1403 p_status_type := l_type;
1404 END get_booking_status;
1405
1406 PROCEDURE get_training_details(p_query_options IN course_query_input_rectype,
1407 p_training OUT NOCOPY activity_tabletype,
1408 p_certifications OUT NOCOPY certification_tabletype,
1409 p_ispartofcertification OUT NOCOPY BOOLEAN)
1410 IS
1411 l_person_id ota_delegate_bookings.delegate_person_id%TYPE := NULL;
1412 l_activity_version_id ota_activity_versions.activity_id%TYPE := NULL;
1413 l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%TYPE;
1414 l_event_id ota_events.event_id%TYPE := NULL;
1415 l_activity_rec activity_rectype;
1416 l_event_rec event_rectype;
1417
1418 l_events_tbl event_tabletype;
1419 l_activity_tbl activity_tabletype;
1420
1421 l_event_action VARCHAR2(50);
1422 l_is_part_of_certification BOOLEAN := FALSE;
1423 l_status_name ota_booking_status_types_VL.name%TYPE;
1424 l_status_type ota_booking_status_types_VL.type%TYPE;
1425
1426 --certification input options
1427 l_cert_input_options cert_query_input_rectype;
1428 l_certifications_tbl certification_tabletype;
1429 l_query_options query_options;
1430 BEGIN
1431
1432 IF p_query_options.person_id IS NOT NULL THEN
1433 IF p_query_options.view_history THEN
1434 get_training_details_internal(p_person_id => p_query_options.person_id
1435 ,p_start_person_id => p_query_options.start_person_id
1436 ,p_end_person_id => p_query_options.end_person_id
1437 ,p_view_history => 'Y'
1438 ,p_activity_tbl => l_activity_tbl);
1439 ELSE
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 => 'N'
1444 ,p_activity_tbl => l_activity_tbl);
1445 END IF;
1446 ELSIF p_query_options.course_id IS NOT NULL THEN
1447 get_training_details_internal(p_course_id => p_query_options.course_id
1448 ,p_activity_tbl => l_activity_tbl);
1449 ELSIF p_query_options.delegate_booking_id IS NOT NULL THEN
1450 get_training_details_internal(p_booking_id => p_query_options.delegate_booking_id
1451 ,p_activity_tbl => l_activity_tbl);
1452 ELSE
1453 get_training_details_internal(p_start_person_id => p_query_options.start_person_id
1454 ,p_end_person_id => p_query_options.end_person_id
1455 ,p_activity_tbl => l_activity_tbl);
1456 END IF;
1457
1458 --get status of booking
1459 get_booking_status(p_delegate_booking_id => p_query_options.delegate_booking_id,
1460 p_status_name => l_status_name,
1461 p_status_type => l_status_type);
1462
1463 --set event action in training record
1464 IF l_status_type='A' AND (upper(l_status_name) ='ATTENDED' OR upper(l_status_name) ='PASSED') THEN
1465 l_event_action := 'TRAINING_COMPLETED';
1466 ELSIF l_status_type='A' AND (upper(l_status_name) ='FAILED') THEN
1467 l_event_action := 'TRAINING_FAILED';
1468 ELSIF l_status_type='R' THEN
1469 l_event_action := 'TRAINING_REQUESTED';
1470 ELSIF l_status_type='P' THEN
1471 l_event_action := 'TRAINING_ENROLLED';
1472 ELSIF l_status_type='W' THEN
1473 l_event_action := 'TRAINING_WAITLISTED';
1474 ELSIF l_status_type='C' THEN
1475 l_event_action := 'TRAINING_CANCELLED';
1476 END IF;
1477
1478 --if part of certification fetch the certification and pass it to the output also
1479 IF p_query_options.delegate_booking_id IS NOT NULL THEN
1480
1481 l_is_part_of_certification := is_part_of_certification(p_query_options.delegate_booking_id,l_cert_enrollment_id);
1482
1483 IF l_is_part_of_certification THEN
1484 l_query_options(1) := 'DETAIL';
1485 l_query_options(2) := 'COMPETENCY';
1486 l_query_options(3) := 'COMPONENT';
1487 l_cert_input_options.options := l_query_options;
1488 l_cert_input_options.cert_enrollment_id := l_cert_enrollment_id;
1489 get_certification_details(p_query_options => l_cert_input_options,
1490 p_certifications => l_certifications_tbl);
1491
1492 -- set output
1493 p_ispartofcertification := l_is_part_of_certification;
1494 p_certifications := l_certifications_tbl;
1495 ELSE
1496 p_certifications := certification_tabletype();
1497 p_ispartofcertification := NULL;
1498 END IF;
1499 ELSE
1500 p_certifications := certification_tabletype();
1501 p_ispartofcertification := NULL;
1502 END IF;
1503
1504 --set the event action if delegate booking is passed
1505 IF p_query_options.delegate_booking_id IS NOT NULL AND l_activity_tbl.COUNT = 1 THEN
1506 l_activity_tbl(1).event_action := l_event_action;
1507 END IF;
1508
1509 p_training := l_activity_tbl;
1510 EXCEPTION
1511 WHEN invalid_delegate_booking_id THEN
1512 p_training := activity_tabletype();
1513 p_certifications := certification_tabletype();
1514 p_ispartofcertification := NULL;
1515 WHEN invalid_course_id THEN
1516 p_training := activity_tabletype();
1517 p_certifications := certification_tabletype();
1518 p_ispartofcertification := NULL;
1519 WHEN invalid_person_id THEN
1520 p_training := activity_tabletype();
1521 p_certifications := certification_tabletype();
1522 p_ispartofcertification := NULL;
1523 END get_training_details;
1524 END ota_training_record;