( SELECT "EJS_EMP_ID","EMPLOYEE_NAME","EMPLOYEE_ID","COURSE_NAME","EXPIRE_DAYS","AGENCY_CODE","COURSE_INSTANCE_ID","COMPLETED_DATE","COMPLETED_DATE_STR","COURSE_TEMPLATE_ID","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(CIE.COMPLETED_DATE) ) END AS EXPIRE_DAYS, CRS.AGENCY_CODE AS AGENCY_CODE, CRS.ID AS COURSE_INSTANCE_ID, CIE.COMPLETED_DATE AS COMPLETED_DATE, TO_CHAR(CIE.COMPLETED_DATE,'MM/DD/YYYY') AS COMPLETED_DATE_STR, C.ID AS COURSE_TEMPLATE_ID, ROW_NUMBER() OVER (PARTITION BY C.ID, CASE WHEN EMPS.MASTER_EMP_ID IS NULL THEN EMPS.EJS_EMP_ID ELSE EMPS.MASTER_EMP_ID END ORDER BY CIE.COMPLETED_DATE DESC) AS ROWN FROM COURSE_INSTANCE_EMPLOYEE CIE INNER JOIN COURSE_INSTANCE CRS ON CRS.ID = CIE.COURSE_INSTANCE_ID INNER JOIN COURSE C ON C.ID = CRS.COURSE_ID INNER JOIN EMPLOYEES EMPS ON EMPS.EJS_EMP_ID = CIE.EJS_EMP_ID WHERE CIE.COMPLETED_DATE IS NOT NULL AND CIE.PASS_FLAG = 'Y' )WHERE ROWN = 1 )