SELECT TRAY_LIST.TL_NO, TRAY_LIST.TL_ID AS CURRENT_TL_ID, TRAY_LIST.DESCR, TRAY_LIST.SERV_NO, TRAY_LIST.VERSION, TRAY_LIST.SOURCE_LOCATION_NO, SERVICE.NAME AS SERVICE_NAME, COMPANY.DESCRIPTION AS FACILITY, LOC.NAME AS LOCATION_NAME, NVL(ALL_TRAYS.NUM_TRAYS, 0) AS NUM_TRAYS, (SELECT COUNT(1) FROM TL_GROUP TLG, TL_ITEM TLI, LOCATION_ITEM LI, ITEM I WHERE TLG.TL_GRP_ID=TLI.TL_GRP_ID AND I.ITEM_ID=TLI.ITEM_ID AND LI.ITEM_ID = I.ITEM_ID AND LI.LOCATION_ID=TRAY_LIST.SOURCE_LOCATION_NO AND (I.SERIAL_TRACKED_IND='Y' OR LI.SERIAL_TRACKED_IND='Y') AND TLG.TL_ID=TRAY_LIST.TL_ID) AS ITEM_TRACKED, TRAY_LIST.TOTAL AS NUM_ITEMS, LAST_ASSEMBLED.ASSM_DT AS LAST_USED_DT, FIRST_TRAY_LIST.CREATED AS CREATED_DT, TRAY_LIST.CREATED AS LAST_MODIFIED_DATE FROM SERVICE, LOCATION LOC, COMPANY, TRAY_LIST_CURRENT_VIEW TRAY_LIST, ( SELECT TRAY_LIST_ALL2.TL_NO, MIN(TRAY_LIST_ALL2.CREATED) AS CREATED FROM TRAY_LIST TRAY_LIST_ALL2 GROUP BY TRAY_LIST_ALL2.TL_NO ) FIRST_TRAY_LIST, ( SELECT TRAY.TL_NO, COUNT(*) AS NUM_TRAYS FROM TRAY GROUP BY TRAY.TL_NO) ALL_TRAYS, ( SELECT TRAY_LIST_ALL.TL_NO, MAX(TRAYSET.ASSM_DT) AS ASSM_DT FROM TRAY_LIST TRAY_LIST_ALL, TRAYSET WHERE TRAY_LIST_ALL.TL_ID = TRAYSET.TL_ID GROUP BY TRAY_LIST_ALL.TL_NO ) LAST_ASSEMBLED WHERE TRAY_LIST.TL_NO = FIRST_TRAY_LIST.TL_NO AND TRAY_LIST.SERV_NO = SERVICE.SERV_NO (+) AND TRAY_LIST.SOURCE_LOCATION_NO = LOC.LOCATION_ID AND COMPANY.COMPANY_NO = LOC.COMPANY_NO AND TRAY_LIST.TL_NO = LAST_ASSEMBLED.TL_NO (+) AND TRAY_LIST.TL_NO = ALL_TRAYS.TL_NO (+) AND TRAY_LIST.TL_TYPE <> 3