( SELECT "EJS_EMP_ID","EMPLOYEE_NAME","EMPLOYEE_ID","COURSE_NAME","EXPIRE_DAYS","AGENCY_CODE","CERTIFICATION_ID","COMPLETED_DATE","COMPLETED_DATE_STR","ROWN" FROM(SELECT CASE WHEN EMPS.MASTER_EMP_ID IS NULL THEN EMPS.EJS_EMP_ID ELSE EMPS.MASTER_EMP_ID END AS EJS_EMP_ID, EJS_ENCRYPTION.DECRYPT_AES256(EMPS.ENC_FNAME) || ' ' || EJS_ENCRYPTION.DECRYPT_AES256(EMPS.ENC_LNAME) AS EMPLOYEE_NAME, CASE WHEN EMPS.MASTER_EMP_ID IS NULL THEN EMPS.EMPLOYEE_ID ELSE (SELECT E1.EMPLOYEE_ID FROM EMPLOYEES E1 WHERE E1.EJS_EMP_ID = EMPS.MASTER_EMP_ID ) END AS EMPLOYEE_ID, CRS.NAME AS COURSE_NAME, CASE WHEN CRS.CERT_LENGTH_DAYS IS NULL OR CRS.CERT_LENGTH_DAYS = 0 THEN NULL ELSE CRS.CERT_LENGTH_DAYS - ( TRUNC(SYSDATE) - TRUNC(CE.DATE_OF_INFO) ) END AS EXPIRE_DAYS, CRS.AGENCY_CODE AS AGENCY_CODE, CRS.ID AS CERTIFICATION_ID, CE.DATE_OF_INFO AS COMPLETED_DATE, TO_CHAR(CE.DATE_OF_INFO,'MM/DD/YYYY') AS COMPLETED_DATE_STR, ROW_NUMBER() OVER (PARTITION BY CRS.ID, CASE WHEN EMPS.MASTER_EMP_ID IS NULL THEN EMPS.EJS_EMP_ID ELSE EMPS.MASTER_EMP_ID END ORDER BY CE.DATE_OF_INFO DESC) AS ROWN FROM CERTIFICATION_EMPLOYEE CE INNER JOIN CERTIFICATIONS CRS ON CRS.ID = CE.CERTIFICATION_ID INNER JOIN EMPLOYEES EMPS ON EMPS.EJS_EMP_ID = CE.EJS_EMP_ID WHERE CE.DATE_OF_INFO IS NOT NULL ) WHERE ROWN = 1 )