SELECT I.ITEM_ID, I.ITEM_NO, NVL(LOCATION_ITEM.ALT_ITEM_DESCRIPTION, I.DESCR) AS ITEM_DESCRIPTION, I.PROC_ITEM_NO, I.MFR_CAT_NO AS MFR_NO, I.LAST_MODIFIED_DT, NVL(LOCATION_ITEM.ALT_IMGLNK, I.IMGLNK) AS IMAGEPATH, LOCATION_ITEM.LOCATION_ID, LOCATION.LOCATION_CODE, LOCATION.NAME AS LOCATION_NAME, COMPANY.COMPANY_NO, COMPANY.DESCRIPTION AS COMPANY_NAME, M.NAME AS MANUFACTURER_NAME, M.MFG_DIV AS MANUFACTURER_DIV, M.MFG_CODE AS MANUFACTURER_CODE, NVL(IS1.NUMBER_OF_TRAYLISTS, 0) AS NUMBER_OF_TRAYLISTS, NVL(IS2.QUANTITY_IN_SERVICE, 0) AS QUANTITY_IN_SERVICE FROM LOCAL_ITEM_VW I, LOCATION_ITEM, LOCATION, COMPANY, MFGR M, ( SELECT ITEM_ID, SOURCE_LOCATION_NO, COUNT(DISTINCT ITEM_ON_TRAY_LIST_HELP.TL_ID) NUMBER_OF_TRAYLISTS FROM ITEM_ON_TRAY_LIST_HELP GROUP BY ITEM_ID, SOURCE_LOCATION_NO) IS1, ( SELECT ITEM_ID, SOURCE_LOCATION_NO, SUM(ITEM_ON_TRAY_LIST_HELP.QTY) QUANTITY_IN_SERVICE FROM ITEM_ON_TRAY_LIST_HELP, TRAY WHERE ITEM_ON_TRAY_LIST_HELP.TL_NO = TRAY.TL_NO GROUP BY ITEM_ID, SOURCE_LOCATION_NO) IS2 WHERE LOCATION_ITEM.ITEM_ID = I.ITEM_ID AND LOCATION.LOCATION_ID = LOCATION_ITEM.LOCATION_ID AND LOCATION.COMPANY_NO = COMPANY.COMPANY_NO AND LOCATION_ITEM.ITEM_ID = IS1.ITEM_ID (+) AND LOCATION_ITEM.LOCATION_ID = IS1.SOURCE_LOCATION_NO (+) AND LOCATION_ITEM.ITEM_ID = IS2.ITEM_ID (+) AND LOCATION_ITEM.LOCATION_ID = IS2.SOURCE_LOCATION_NO (+) AND I.MFR_ID = M.MFR_ID (+) AND LOCATION_ITEM.STATUS = 'A'