[Home] [Help]
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
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)
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;
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
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
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
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,
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
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