SELECT CA.CALENDAR_ASSIGNMENT_ID ,CA.ACTIVE ,CA.CODE ,AC.DESCRIPTION ,CA.TYPE ,(SELECT ACAT.DESCRIPTION FROM EJS_CODES ACAT WHERE ACAT.CODE = AC.ASSIGNMENT_CATEGORY AND ACAT.CODE_TYPE = AC.ASSIGNMENT_CATEGORY_TYPE) AS ASSIGNMENT_CAT_DESC ,CA.START_DATE_TIME ,CA.END_DATE_TIME ,TO_CHAR(CA.START_DATE_TIME,'HH24MI') ||' - '|| TO_CHAR(CA.END_DATE_TIME,'HH24MI') ASSIGN_HOURS ,CA.HOURS ,CA.TITLE ,CA.OVERTIME_FLG ,CA.MINIMUM_STAFFING_FLG ,CA.OFFICER_ID ,CA.EMPLOYEE_ID ,CASE WHEN CA.OFFICER_ID IS NOT NULL THEN O.FNAME||' '||O.LNAME||' ('||O.INTERNAL_ID||')' ELSE EMP.ENC_FNAME||' '||EMP.ENC_LNAME||' ('||EMP.EMPLOYEE_ID||')' END AS ASSOCIATE_NAME ,CASE WHEN CA.OFFICER_ID IS NOT NULL THEN 'O' ELSE 'E' END AS ASSOCIATE_TYPE_CODE ,CASE WHEN CA.OFFICER_ID IS NOT NULL THEN 'OFFICER' ELSE 'EMPLOYEE' END AS ASSOCIATE_TYPE_CODE ,CASE WHEN CA.OFFICER_ID IS NOT NULL THEN INTERNAL_ID ELSE EMP.EMPLOYEE_ID END AS ASSOCIATE_ID ,CA.ASSIGNMENT_SHIFT_ID ,CA.USER_SHIFT_ID ,CASE WHEN CA.ASSIGNMENT_SHIFT_ID = USER_SHIFT_ID THEN 'Permanent Assignment' ELSE 'Temporary Assignment' END ,CS.NAME AS SHIFT_NAME ,CAL.AGENCY_CODE ,(SELECT AGENCY_DESC FROM AGENCY_CODES AC WHERE AC.AGENCY_CODE = CAL.AGENCY_CODE) ,CS.START_TIME ,CS.END_TIME ,CS.MONDAY_MIN_STAFF ,CS.TUESDAY_MIN_STAFF ,CS.WEDNESDAY_MIN_STAFF ,CS.THURSDAY_MIN_STAFF ,CS.FRIDAY_MIN_STAFF ,CS.SATURDAY_MIN_STAFF ,CS.SUNDAY_MIN_STAFF FROM CALENDAR_ASSIGNMENTS CA INNER JOIN CALENDAR_SHIFTS CS ON CA.ASSIGNMENT_SHIFT_ID = CS.CALENDAR_SHIFT_ID INNER JOIN CALENDARS CAL ON CS.CALENDAR_ID = CAL.CALENDAR_ID INNER JOIN ASSIGNMENT_CODES AC ON CA.CODE = AC.ASSIGNMENT_CODE LEFT OUTER JOIN OFFICERS O ON CA.OFFICER_ID = O.OFFICER_ID LEFT OUTER JOIN EMPLOYEES EMP ON CA.EMPLOYEE_ID = EMP.EJS_EMP_ID