OTA OLM Certification

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, cre.contact_id)) like upper('%'||:P_LEARNER_NAME||'%') or :P_LEARNER_NAME is null)
and ( (:P_EXPIRY_FROM_DATE is null) or (crt.RENEWABLE_FLAG = 'N')
              or
      (trunc(cpe.expiration_date) >= trunc(:P_EXPIRY_FROM_DATE))
     )
and ( (:P_EXPIRY_TO_DATE is null) or (crt.RENEWABLE_FLAG = 'N')
              or
      (trunc(cpe.expiration_date) <= trunc(:P_EXPIRY_TO_DATE))
     )
and ( (:P_SUBSCRIPTION_START_DATE is null)
              or
      (trunc(cpe.cert_period_start_date) >= trunc(:P_SUBSCRIPTION_START_DATE))
     )
and ( (:P_SUBSCRIPTION_END_DATE is null)
              or
      (trunc(cpe.cert_period_start_date) <= trunc(:P_SUBSCRIPTION_END_DATE))
     )
and ((:P_SUBSCRIPTION_STATUS is not null and ota_cpe_util.get_cre_status(cre.cert_enrollment_id, 'c') = :P_SUBSCRIPTION_STATUS) or :P_SUBSCRIPTION_STATUS is null)
and ota_admin_access_util.admin_can_access_object('CER',crt.certification_id)='Y'
and ((crt.RENEWABLE_FLAG = 'N') or (crt.RENEWABLE_FLAG = 'Y' and cpe.expiration_date = (select max(expiration_date) from ota_cert_prd_enrollments where cert_enrollment_id = cre.cert_enrollment_id)))
and (exists (select 1 from per_person_type_usages_f ptu, per_person_types ppt
where ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type in ('EMP','CWK','APL','OTHER')
and sysdate between ptu.effective_start_date and ptu.effective_end_date
and ptu.person_id = cre.person_id) or cre.contact_id is not null)
order by
ctl.name,
ota_cpe_util.get_cre_status(cre.cert_enrollment_id, 'm'),
cpe.cert_period_start_date,
ota_utility.get_learner_name(cre.person_id, null, cre.contact_id)