OTA OLM Certification

Description
Categories: BI Publisher
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, 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)
Parameter Name SQL text Validation
Certification Name
 
LOV Oracle
Subscription Status
 
LOV Oracle
Learner Name
 
From Date
 
Date
To Date
 
Date
Expiry Date From
 
Date
Expiry To Date
 
Date