ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F2633869E053B46B63588994 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) Y OTA OTACERT AR OTA تقرير الشهادة في OLM Application: إدارة التعليم Source: تقرير الشهادة في OLM Short Name: OTACERT DB package: ota_cert_xmlp_pkg D OTA OLM-Zertifizierung Application: Learning Management Source: OLM-Zertifizierung Short Name: OTACERT DB package: ota_cert_xmlp_pkg F OTA OLM - Etat de certification Application: Learning Management Source: OLM - Etat de certification Short Name: OTACERT DB package: ota_cert_xmlp_pkg US OTA OLM Certification Application: Learning Management Source: OLM Certification Report Short Name: OTACERT DB package: ota_cert_xmlp_pkg ZHS OTA OLM 证明报表 Application: Oracle Learning Management Source: OLM 证明报表 Short Name: OTACERT DB package: ota_cert_xmlp_pkg BI Publisher :c_certification_name :c_expiry_from_date :c_expiry_to_date :c_subscription_end_date :c_subscription_start_date :c_subscription_status :p_certification_id :p_expiry_from_date :p_expiry_to_date :p_learner_name :p_subscription_end_date :p_subscription_start_date :p_subscription_status 1 10 :p_certification_id LOV Oracle OTA_CERTIFICATIONS Y select certification_id id, name value, null description from ota_certifications_vl where business_group_id=ota_general.get_business_group_id and ota_admin_access_util.admin_can_access_object('CER',certification_id)='Y' order by value,description AR اسم الشهادة D Zertifizierungsbezeichnung F Nom de la certification US Certification Name ZHS 证明名称 2 20 :p_subscription_status LOV Oracle OTA_CERT_REPORT_STATUS_LIST Y select es.lookup_code id, es.meaning value, null description from hr_lookups es where es.lookup_type='OTA_CERT_ENROLL_STATUS' and sysdate between nvl(es.start_date_active,sysdate) and nvl (es.end_date_active,sysdate) and es.enabled_flag='Y' and (lookup_code!='CONCLUDED' and lookup_code!='REJECTED') order by value,description AR موقف الاشتراك D Abonnementstatus F Statut d'inscription (certifications) US Subscription Status ZHS 登记状态 3 30 :p_learner_name Char AR اسم الدارس D Teilnehmername F Nom du stagiaire US Learner Name ZHS 学员姓名 4 40 :p_subscription_start_date Date AR من تاريخ D Von Datum F Date - Du US From Date ZHS 自日期 5 50 :p_subscription_end_date Date AR إلى تاريخ D Bis Datum F Date - Au US To Date ZHS 至日期 6 60 :p_expiry_from_date Date AR تاريخ انتهاء الصلاحية من D Gültig von F Date d'expiration - Du US Expiry Date From ZHS 到期日自 7 70 :p_expiry_to_date Date AR انتهاء الصلاحية إلى تاريخ D Gültig bis F Date d'expiration - Au US Expiry To Date ZHS 到期终止日期