OTA OLM Certification
Description
Categories: BI Publisher
Application: Learning Management
Source: OLM Certification Report
Short Name: OTACERT
DB package: ota_cert_xmlp_pkg
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 |
|
Char | |
From Date |
|
Date | |
To Date |
|
Date | |
Expiry Date From |
|
Date | |
Expiry To Date |
|
Date |