SELECT TS.STATUS TS_STATUS, L.COMPANY_NO, L.LOCATION_ID, L.LOCATION_CODE, L.NAME LOCATION_NAME, I.ITEM_NO, DECODE(LI.ALT_ITEM_DESCRIPTION, '', I.DESCR, LI.ALT_ITEM_DESCRIPTION) ITEM_DESCRIPTION, IT.INSTRUMENT_ID, IT.STATUS, DECODE(TS.STATUS, 1, '', TS.TS_NO) ASID, DECODE(TS.STATUS, 1, '', TRAY_NO) TRAY_NO, DECODE(TS.STATUS, 1, '', TL.DESCR) DESCR, IT.DT_TIME MOD_DATE, C.DESCRIPTION COMPANY_NAME, (SELECT PATIENT_ID FROM TRANSFER WHERE (TS_NO, END_TIME) = (SELECT TS_NO, MAX(END_TIME) FROM TRANSFER WHERE TS_NO = TS.TS_NO GROUP BY TS_NO) ) PATIENT_ID, (SELECT COMMENTS FROM TRANSFER WHERE (TS_NO, END_TIME) = (SELECT TS_NO, MAX(END_TIME) FROM TRANSFER WHERE TS_NO = TS.TS_NO GROUP BY TS_NO) ) COMMENTS, DECODE(IT.USAGE_COUNT, NULL, 0, IT.USAGE_COUNT) USAGE_COUNT FROM INSTRUMENT_TRACK IT, LOCATION L, LOCATION_ITEM LI, ITEM I, INSTRUMENT_ID_ASSEMBLY_VIEW TITD, TRAYSET TS, TRAY_LIST TL, COMPANY C WHERE L.LOCATION_ID = IT.LOCATION_ID AND L.COMPANY_NO = C.COMPANY_NO AND LI.LOCATION_ITEM_ID = IT.LOCATION_ITEM_ID AND LI.ITEM_ID = I.ITEM_ID AND IT.INSTRUMENT_TRACK_ID = TITD.INSTRUMENT_TRACK_ID(+) AND (TITD.TS_INST_TK_DATA_ID IS NULL OR TITD.TS_INST_TK_DATA_ID = (SELECT MAX(TS_INST_TK_DATA_ID) FROM INSTRUMENT_ID_ASSEMBLY_VIEW WHERE INSTRUMENT_TRACK_ID = TITD.INSTRUMENT_TRACK_ID)) AND TITD.TS_ID = TS.TS_ID(+) AND TS.TL_ID = TL.TL_ID(+) AND IT.STATUS!='D' AND LI.STATUS='A'