SELECT ( SELECT S.NAME FROM SERVICE S WHERE S.SERV_NO = TL.SERV_NO) AS "SERVICE_NAME", TL.SERV_NO AS "SERVICE_NO", TL.TL_NO AS "TRAY_LIST_NO", TL.DESCR AS "TRAY_LIST_NAME", TS.TRAY_NO AS "TRAY_NO", TS.TS_NO AS "TRAY_ASID", TS.CREAT_DT AS "TRAYSET_ASSEMBLY_START_DT", TS.ASSM_DT AS "TRAYSET_ASSEMBLY_END_DT", TS.TOTAL AS "TOTAL_ITEMS", ROUND(((ASSM_DT - CREAT_DT) * (3600 * 24)) / 60) AS "ASSEMBLY_TIME_MINUTE", ((((ASSM_DT - CREAT_DT) * (3600 * 24)) / 60) - ROUND(((ASSM_DT - CREAT_DT) * (3600 * 24)) / 60) ) * 60 AS "ASSEMBLY_TIME_SEC", TS.ASSM_LOC_NO AS "TRAYSET_LOCATION", TS.TRAYSET_BIN AS "TRAYSET_SUBLOCATION", DECODE(TS.STATUS, 3, 'SCANNED', 4, 'INPROCESS', 5, 'ONHOLD', 6, 'COMPLETE_ASSEMBLED', 7, 'INCOMPLETE', 8, 'STERILIZED', 9, 'READY', 10, 'OPROOM', 11, 'DECONTAM', 12, 'PICKED', 13, 'BATCH', NULL ) AS ASSEMBLED_TRAY_STATUS, NVL((SELECT COUNT(TSET_EX.TS_ID) FROM TS_ITEM_EXCEPTION TSET_EX WHERE TSET_EX.TS_ID = TS.TS_ID ), 0) AS "TRAYSET_EXCEPTION_COUNT", TL.SOURCE_LOCATION_NO AS "INSTR_SRC_LOCATION", C.COMPANY_NO AS "COMPANY_NO", C.DESCRIPTION AS "COMPANY_NAME" FROM TRAY_LIST TL, TRAYSET TS, COMPANY C, LOCATION L WHERE TL.TL_ID = TS.TL_ID AND TS.STATUS IN ('3','4','5','6','7','8','9','11','12','13') AND TL.SOURCE_LOCATION_NO = L.LOCATION_ID AND L.COMPANY_NO = C.COMPANY_NO