View Definition:
SELECT DISTINCT
EMP.EJS_EMP_ID,
EMP.MASTER_EMP_ID,
EMP.LOGIN_ID,
EMP.EMPLOYEE_ID,
EMP.OFFICER_ID,
EMP.ACTIVE_STATUS_CODE,
(SELECT EC.DESCRIPTION
FROM EJS_CODES EC
WHERE EC.CODE_TYPE =
NVL (EMP.ACTIVE_STATUS_CODE_TYPE,
'EMP_ACTIVE_STATUS_CODES')
AND EC.CODE = EMP.ACTIVE_STATUS_CODE)
AS ACTIVE_STATUS_DESC,
EMP.AGENCY_CODE,
AC.AGENCY_DESC,
EMP.BARGAINING_UNIT,
EMP.BLOOD_CODE,
(SELECT BLOOD_DESC
FROM BLOOD_TYPE_CODES BTC
WHERE BTC.CODE = EMP.BLOOD_CODE)
AS BLOOD_DESC,
EMP.DATE_HIRED,
EMP.EMP_TYPE_CODE,
(SELECT EC.DESCRIPTION
FROM EJS_CODES EC
WHERE EC.CODE_TYPE =
NVL (EMP.EMP_TYPE_CODE_TYPE,
'EMPLOYEE_TYPE_CODES')
AND EC.CODE = EMP.EMP_TYPE_CODE)
AS EMP_TYPE_DESC,
EMP.EMPLOYEE_LEVEL_CODE,
(SELECT EC.DESCRIPTION
FROM EJS_CODES EC
WHERE EC.CODE_TYPE =
NVL (EMP.EMPLOYEE_LEVEL_CODE_TYPE,
'EMP_LEVEL_CODES')
AND EC.CODE = EMP.EMPLOYEE_LEVEL_CODE)
AS EMPLOYEE_LEVEL_DESC,
EMP.END_DATE,
EMP.ETHNICITY_CODE,
(SELECT EC.DESCRIPTION
FROM EJS_CODES EC
WHERE EC.CODE_TYPE =
NVL (EMP.ETHNICITY_CODE_TYPE, 'ETHNICITY_CODES')
AND EC.CODE = EMP.ETHNICITY_CODE)
AS ETHNICITY_DESC,
EMP.HAND_DOMINANCE_CODE,
(SELECT EC.DESCRIPTION
FROM EJS_CODES EC
WHERE EC.CODE_TYPE =
NVL (EMP.HAND_DOMINANCE_CODE_TYPE,
'HAND_DOMINANCE_CODES')
AND EC.CODE = EMP.HAND_DOMINANCE_CODE)
AS HAND_DOMINANCE_DESC,
EMP.LONGEVITY_DATE,
EMP.RACE_CODE,
(SELECT EC.DESCRIPTION
FROM EJS_CODES EC
WHERE EC.CODE_TYPE = NVL (EMP.RACE_CODE_TYPE, 'RACE_CODES')
AND EC.CODE = EMP.RACE_CODE)
AS RACE_CODE_DESC,
EMP.SEX_CODE,
(SELECT EC.DESCRIPTION
FROM EJS_CODES EC
WHERE EC.CODE_TYPE = NVL (EMP.SEX_CODE_TYPE, 'SEX_CODES')
AND EC.CODE = EMP.SEX_CODE)
AS SEX_DESC,
EMP.SUFFIX,
EMP.TITLE_CODE,
(SELECT EC.DESCRIPTION
FROM EJS_CODES EC
WHERE EC.CODE_TYPE =
NVL (EMP.TITLE_CODE_TYPE, 'TITLE_CODES')
AND EC.CODE = EMP.TITLE_CODE)
AS TITLE_DESC,
EMP.ENC_LNAME,
EMP.ENC_FNAME,
EMP.ENC_MNAME,
EMP.ENC_MAIDEN_NAME,
EMP.ENC_SSN,
EMP.ENC_DOB,
EMP.ENC_PLACE_OF_BIRTH,
CERT.ID AS CERTIFICATION_ID,
CERT.NAME,
CERT.AGENCY_CODE,
CE.DATE_OF_INFO,
(CASE WHEN CERT.CERT_LENGTH_DAYS > 0
THEN TRUNC(CE.DATE_OF_INFO) + CERT.CERT_LENGTH_DAYS END) AS CERTIFICATION_EXPIRATION_DATE
FROM EMPLOYEES EMP1
INNER JOIN EMPLOYEES EMP ON EMP.EJS_EMP_ID = NVL (EMP1.MASTER_EMP_ID, EMP1.EJS_EMP_ID)
INNER JOIN CERTIFICATION_EMPLOYEE CE ON EMP1.EJS_EMP_ID = CE.EJS_EMP_ID
INNER JOIN CERTIFICATIONS CERT ON CE.CERTIFICATION_ID = CERT.ID
LEFT OUTER JOIN AGENCY_CODES AC
ON AC.AGENCY_CODE = EMP1.AGENCY_CODE
