OTA OLM Certification

Description
Categories: BI Publisher, Human Resources
Columns: Cert Name, Cert Status Code, Cert Status Meaning, Cert Period Start Date, Cert Period End Date, Cre Completion Date, Cert Expiry Date, Person Id, Contact Id, Cert Enrollment Id ...
Application: Learning Management
Source: OLM Certification Report
Short Name: OTACERT
DB package: ota_cert_xmlp_pkg
select
ctl.name CERT_NAME,
--cre.certification_status_code certification_status_code,
ota_cpe_util.get_cre_status(cre.cert_enrollment_id, 'c') CERT_STATUS_CODE,
--crt_lkp.meaning cert_status_meaning,
ota_cpe_util.get_cre_status(cre.cert_enrollment_id, 'm') CERT_STATUS_MEANING,
--cpe.period_status_code period_status_code,
--cpe_lkp.meaning period_status_meaning,
fnd_date.date_to_displaydate(cpe.cert_period_start_date,2) CERT_PERIOD_START_DATE,
fnd_date.date_to_displaydate(cpe.cert_period_end_date,2) CERT_PERIOD_END_DATE,
fnd_date.date_to_displaydate(cre.completion_date,2) cre_completion_date,
fnd_date.date_to_displaydate(cpe.expiration_date,2) CERT_EXPIRY_DATE,
cre.person_id person_id,
cre.contact_id contact_id,
cre.cert_enrollment_id,
cpe.cert_prd_enrollment_id,
crt.certification_id,
crt.start_date_active,
crt.end_date_active,
ota_utility.get_learner_name(cre.person_id, null, cre.contact_id) CERT_LEARNER_NAME
from
ota_certifications_b crt,
ota_certifications_tl ctl,
ota_cert_enrollments cre,
ota_cert_prd_enrollments cpe
where crt.certification_id = cre.certification_id
and cre.cert_enrollment_id = cpe.cert_enrollment_id(+)
and crt.certification_id = ctl.certification_id
and ctl.language = USERENV('LANG')
and cre.business_group_id = ota_general.get_business_group_id
and ((:P_CERTIFICATION_ID is not null and crt.certification_id = :P_CERTIFICATION_ID) or :P_CERTIFICATION_ID is null)
and (upper(ota_utility.get_learner_name(cre.person_id, null