SELECT TRAYSET.TRAY_NO, TRAYSET.TS_NO, ASSEMBLY.ASM_TIME AS ACTIVITY_DT, ASSEMBLY.ASM_LOC_NO AS LOCATION_ID, 'Assembly' AS ACTIVITY, 'ASID' AS ID_TYPE, TRAYSET.TS_NO AS ID_NO, ASSEMBLY.USER_ID FROM TRAYSET, ASSEMBLY WHERE TRAYSET.TS_NO = ASSEMBLY.TS_NO AND ASSEMBLY.ACTIVITY = 6 AND ASSEMBLY.ASM_ID = ( SELECT MIN(ASM_ID) FROM ASSEMBLY A2 WHERE A2.TS_NO = TRAYSET.TS_NO AND A2.ACTIVITY = 6 ) UNION SELECT TRAYSET.TRAY_NO, TRAYSET.TS_NO, CASECART_ITEMS.ACT_DATE AS ACTIVITY_DT, CASECART_ITEMS.ACTIVITY_LOC_NO AS LOCATION_ID, 'Picking' AS ACTIVITY, 'Pick List ID' AS ID_TYPE, PICK.PICKLIST_NO AS ID_NO, PICK.USER_ID FROM TRAYSET, CASECART_ITEMS, PICK WHERE TRAYSET.TS_NO = CASECART_ITEMS.TS_NO AND CASECART_ITEMS.CARTSET_ASSM_NO = PICK.CARTSET_ASSM_NO AND CASECART_ITEMS.ACTIVITY_LOC_NO = PICK.PICK_LOC_NO AND CASECART_ITEMS.ACTIVITY = 2 UNION SELECT TRAYSET.TRAY_NO, TRAYSET.TS_NO, LOAD_CONTENT.ADDED_DATE AS ACTIVITY_DT, STERILIZER_EQUIPMENT.LOC_NO AS LOCATION_ID, 'Sterilization' AS ACTIVITY, 'Cycle No.' AS ID_TYPE, LOAD_STER.CYCLE_NO AS ID_NO, LOAD_CONTENT.ADDED_BY AS USER_ID FROM TRAYSET, LOAD_CONTENT, LOAD_STER, STERILIZER_EQUIPMENT WHERE TRAYSET.TS_NO = LOAD_CONTENT.ASID AND LOAD_CONTENT.LOAD_ID = LOAD_STER.LOAD_ID AND LOAD_STER.STERILIZER_EQ_NO = STERILIZER_EQUIPMENT.STERILIZER_EQ_NO UNION SELECT TRAYSET.TRAY_NO, TRAYSET.TS_NO, TRANSFER.END_TIME AS ACTIVITY_DT, TRANSFER.TO_LOC_NO AS LOCATION_ID, 'Transfer' AS ACTIVITY, '--' AS ID_TYPE, '--' AS ID_NO, TRANSFER.USER_ID FROM TRANSFER, TRAYSET WHERE TRANSFER.TS_NO = TRAYSET.TS_NO UNION SELECT TRAYSET.TRAY_NO, TRAYSET.TS_NO, TRANSFER.END_TIME AS ACTIVITY_DT, TRANSFER.TO_LOC_NO AS LOCATION_ID, 'Transfer' AS ACTIVITY, 'Case Cart ID' AS ID_TYPE, CASECART_ASSEMBLY.CASECART_NO AS ID_NO, TRANSFER.USER_ID FROM TRANSFER, CASECART_ITEMS, CASECART_ASSEMBLY, TRAYSET WHERE TRANSFER.CARTSET_ASSM_NO = CASECART_ITEMS.CARTSET_ASSM_NO AND TRANSFER.START_TIME >= CASECART_ITEMS.ACT_DATE AND CASECART_ITEMS.CARTSET_ASSM_NO = CASECART_ASSEMBLY.CARTSET_ASSM_NO AND CASECART_ITEMS.TS_NO = TRAYSET.TS_NO UNION SELECT TRAYSET.TRAY_NO, TRAYSET.TS_NO, ASSEMBLY.ASM_TIME AS ACTIVITY_DT, ASSEMBLY.ASM_LOC_NO AS LOCATION_ID, 'Decontamination' AS ACTIVITY, 'Case Cart ID' AS ID_TYPE, DECONTAM_DETAILS.CASECART_NO AS ID_NO, ASSEMBLY.USER_ID FROM TRAYSET, ASSEMBLY, DECONTAM_DETAILS WHERE TRAYSET.TS_NO = ASSEMBLY.TS_NO AND ASSEMBLY.TS_NO = DECONTAM_DETAILS.ASID AND ASSEMBLY.ASM_TIME = DECONTAM_DETAILS.ACT_DATE AND ASSEMBLY.ACTIVITY = 11