SELECT TRANSFER.TRANSFER_ID, TRANSFER.TRANSFER_NO, TRANSFER.USER_ID, TRANSFER.TO_LOC_NO, TRANSFER.END_TIME, LOCATION.LOCATION_CODE AS TRANSFER_LOCATION_CODE, LOCATION.NAME AS TRANSFER_LOCATION_NAME, LOCATION.COMPANY_NO AS TRANSFER_FACILITY_NUMBER, USERS.FIRST_NAME AS TRANSFER_USER_FIRST_NAME, USERS.LAST_NAME AS TRANSFER_USER_LAST_NAME, DECODE(TRANSFER.CARTSET_ASSM_NO, NULL, 0, 1 ) AS NUM_CASECARTS, DECODE(TRANSFER.TS_NO, NULL, ( SELECT COUNT(*) FROM CASECART_ITEMS WHERE CARTSET_ASSM_NO = TRANSFER.CARTSET_ASSM_NO AND ACT_DATE <= TRANSFER.END_TIME ), 1 ) AS NUM_TRAYS FROM TRANSFER, USERS, LOCATION WHERE TRANSFER.USER_ID = USERS.USER_ID AND TRANSFER.TO_LOC_NO = LOCATION.LOCATION_ID AND TRANSFER.BATCH_NO IS NULL