View slot9.WORLD.TSTA.PERSONNEL_COURSE_SEARCH_VIEW
Legend:
Primary key columns
Columns with indexes
Implied relationships
Excluded column relationships
< n > number of related tables
 
Column Type Size Nulls Auto Default Children Parents Comments
EJS_EMP_ID number 30
EMPLOYEES.EJS_EMP_ID Implied Constraint R
MASTER_EMP_ID number 30  √  null
LOGIN_ID varchar2 100  √  null
IJIS_USER_PROFILES.LOGIN_ID Implied Constraint R
EMPLOYEE_ID varchar2 20  √  null
OFFICER_ID number 38  √  null
ACTIVE_STATUS_CODE varchar2 30  √  null
ACTIVE_STATUS_DESC varchar2 4000  √  null
AGENCY_CODE varchar2 30
AGENCY_CODES.AGENCY_CODE Implied Constraint R
AGENCY_DESC varchar2 40  √  null
BARGAINING_UNIT varchar2 30  √  null
BLOOD_CODE varchar2 30  √  null
BLOOD_DESC varchar2 40  √  null
HIRE_DATE date 7  √  null
EMP_TYPE_CODE varchar2 30  √  null
EMP_TYPE_DESC varchar2 4000  √  null
EMPLOYEE_LEVEL_CODE varchar2 30  √  null
EMPLOYEE_LEVEL_DESC varchar2 4000  √  null
END_DATE date 7  √  null
ETHNICITY_CODE varchar2 30  √  null
ETHNICITY_DESC varchar2 4000  √  null
HAND_DOMINANCE_CODE varchar2 30  √  null
HAND_DOMINANCE_DESC varchar2 4000  √  null
LONGEVITY_DATE date 7  √  null
RACE_CODE varchar2 30  √  null
RACE_DESC varchar2 4000  √  null
SEX_CODE varchar2 30  √  null
SEX_DESC varchar2 4000  √  null
SUFFIX varchar2 30  √  null
TITLE_CODE varchar2 30  √  null
TITLE_DESC varchar2 4000  √  null
ENC_LNAME raw 64  √  null
ENC_FNAME raw 64  √  null
ENC_MNAME raw 64  √  null
ENC_MAIDEN_NAME raw 64  √  null
ENC_SSN raw 32  √  null
ENC_DOB raw 32  √  null
ENC_PLACE_OF_BIRTH raw 512  √  null
COURSE_INSTANCE_ID number 0
COURSE_TEMPLATE_ID number 0
COURSE_NAME varchar2 100
COURSE_AGENCY_CODE varchar2 30  √  null
COURSE_TYPE_CODE varchar2 30  √  null
COURSE_TYPE_DESC varchar2 4000  √  null
COURSE_CLASSIFICATION_CODE varchar2 30  √  null
COURSE_CLASSIFICATION_DESC varchar2 4000  √  null
COURSE_LOCATION varchar2 100  √  null
COURSE_INSTRUCTOR varchar2 100  √  null
COURSE_START_DATE date 7  √  null
COURSE_END_DATE date 7  √  null
COURSE_COMPLETION_DATE date 7  √  null
COURSE_PASS_FLAG varchar2 1  √  null
EXPIRATION_DATE date 7  √  null

Analyzed at Mon Sep 20 21:05 MDT 2021

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, CIE.COURSE_INSTANCE_ID, CI.COURSE_ID, CI.NAME, CI.AGENCY_CODE, CI.TYPE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = NVL (CI.TYPE_CODE_TYPE,'COURSE_TYPE_CODES') AND EC.CODE = CI.TYPE_CODE) AS COURSE_TYPE_DESC, CI.CLASSIFICATION_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = NVL (CI.CLASSIFICATION_CODE_TYPE,'COURSE_CLASS_CODES') AND EC.CODE = CI.CLASSIFICATION_CODE) AS COURSE_CLASSIFICATION_DESC, CI.LOCATION, CI.INSTRUCTORS, CI.START_DATE_TIME, CI.END_DATE_TIME, CIE.COMPLETED_DATE, CIE.PASS_FLAG, (CASE WHEN CIE.COMPLETED_DATE IS NOT NULL AND CIE.PASS_FLAG = 'Y' AND CI.CERT_LENGTH_DAYS > 0 THEN TRUNC(CIE.COMPLETED_DATE) + CI.CERT_LENGTH_DAYS END) AS 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 COURSE_INSTANCE_EMPLOYEE CIE ON EMP1.EJS_EMP_ID = CIE.EJS_EMP_ID INNER JOIN COURSE_INSTANCE CI ON CIE.COURSE_INSTANCE_ID = CI.ID LEFT OUTER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = EMP1.AGENCY_CODE
 
Possibly Referenced Tables/Views:
AGENCY_CODES BLOOD_TYPE_CODES COURSE_INSTANCE COURSE_INSTANCE_EMPLOYEE EJS_CODES EMPLOYEES 


Close relationships: