SELECT S.NAME AS SERVICE_NAME, TL.SERV_NO AS "SERVICE_NO", TL.TL_NO AS "TRAY_LIST_NO", TL.DESCR AS "TRAY_LIST_NAME", DECODE(TS.TS_TYPE, 2, 'Peel pack', 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", TS.ASSM_LOC_NO AS "TRAYSET_LOCATION", TS.TRAYSET_BIN AS "TRAYSET_SUBLOCATION", NVL(( SELECT COUNT(TSET_EX.TS_ID) FROM TS_ITEM_EXCEPTION TSET_EX WHERE TSET_EX.TS_ID = TS.TS_ID ), 0) + NVL(( SELECT COUNT(TS_INST_TK_EXCEPTION.TS_INST_TK_DATA_ID) FROM TS_INST_TK_EXCEPTION, TS_INST_TK_DATA WHERE TS_INST_TK_EXCEPTION.TS_INST_TK_DATA_ID=TS_INST_TK_DATA.TS_INST_TK_DATA_ID AND TS_INST_TK_DATA.TS_ID = TS.TS_ID ), 0) AS "TRAYSET_EXCEPTION_COUNT", TL.SOURCE_LOCATION_NO AS "INSTR_SRC_LOCATION", L.COMPANY_NO AS "COMPANY_NO", TS.ASSM_DT AS "FIRST_ASSM_TIME", DECODE(TS.TS_TYPE, 1, ( SELECT MIN(TS2.ASSM_DT) AS NEXT_ASSM_TIME FROM TRAYSET TS2, TRAY_LIST TL2 WHERE TS2.TRAY_NO = TS.TRAY_NO AND TS2.TL_ID = TL2.TL_ID AND TL2.TL_NO = TL.TL_NO AND TS2.ASSM_DT IS NOT NULL AND TS2.TS_NO > TS.TS_NO ), NULL ) AS "NEXT_ASSM_TIME" FROM TRAY_LIST TL, TRAYSET TS, LOCATION L, SERVICE S WHERE TL.TL_ID = TS.TL_ID AND TL.SERV_NO = S.SERV_NO AND TL.SOURCE_LOCATION_NO = L.LOCATION_ID AND TS.TS_TYPE IN (1,2)