SELECT NVL(SERVICE.NAME, 'No Service Defined') AS SERVICE_NAME, TL_CURRENT.TL_NO, TL_CURRENT.DESCR AS TL_DESCR, NVL(EXCEPTIONS.EXCEPTION_IND, 0) AS EXCEPTION_IND, STER_LOAD.STER_DT, TS.ASSM_LOC_NO AS LOCATION_ID, TL_CURRENT.STORAGE_BIN AS SUBLOCATION_CODE FROM TRAYSET_CURRENT_VW TS, TRAY_LIST TL, TRAY_LIST_CURRENT_VIEW TL_CURRENT, SERVICE, ( SELECT TRAYSET.TS_ID, MAX(LOAD_CONTENT.ADDED_DATE) AS STER_DT FROM LOAD_CONTENT, TRAYSET WHERE LOAD_CONTENT.ASID = TRAYSET.TS_NO AND TRAYSET.STATUS <> 11 GROUP BY TRAYSET.TS_ID ) STER_LOAD, ( SELECT DISTINCT TS_ID, 1 AS EXCEPTION_IND FROM TS_ITEM_EXCEPTION ) EXCEPTIONS WHERE TS.TL_ID = TL.TL_ID AND TL.TL_NO = TL_CURRENT.TL_NO AND TL_CURRENT.SERV_NO = SERVICE.SERV_NO (+) AND TS.TS_ID = STER_LOAD.TS_ID (+) AND TS.TS_ID = EXCEPTIONS.TS_ID (+) AND EXISTS(SELECT 1 FROM TRAY WHERE TRAY.TL_NO=TL_CURRENT.TL_NO)