SELECT PICK_HEADER.PICKLIST_NO, PICK_HEADER.USER_ID, PICK_HEADER.PICK_LOC_NO, PICK_START.START_TIME, PICK_END.END_TIME, PICK_END.NUMBER_OF_PICKED_TRAYS, PICK_END.NUMBER_OF_CASECARTS, LOCATION.LOCATION_CODE AS PICK_LOCATION_CODE, LOCATION.LOCATION_ID AS PICK_LOCATION_ID, LOCATION.NAME AS PICK_LOCATION, COMPANY.COMPANY_NO AS FACILITY_ID, COMPANY.DESCRIPTION AS FACILITY_NAME FROM ( SELECT DISTINCT PICKLIST_NO, USER_ID, PICK_LOC_NO FROM PICK WHERE CARTSET_ASSM_NO <> '---' ) PICK_HEADER, ( SELECT MAP.PICKLIST_NO, LOG.USER_ID, LOG.LOC_NO AS PICK_LOC_NO, MIN(ASSM_DATE) AS START_TIME FROM PICKLIST_CARTSET_MAP_VW MAP, CASECART_ASSEMBLY_LOG LOG WHERE MAP.CARTSET_ASSM_NO = LOG.CARTSET_ASSM_NO AND LOG.ACTIVITY = 6 GROUP BY MAP.PICKLIST_NO, LOG.USER_ID, LOG.LOC_NO ) PICK_START, ( SELECT MAP.PICKLIST_NO, LOG.USER_ID, LOG.LOC_NO AS PICK_LOC_NO, SUM(LOG.TOTAL) AS NUMBER_OF_PICKED_TRAYS, COUNT(DISTINCT LOG.CARTSET_ASSM_NO) AS NUMBER_OF_CASECARTS, MAX(ASSM_DATE) AS END_TIME FROM PICKLIST_CARTSET_MAP_VW MAP, CASECART_ASSEMBLY_LOG LOG WHERE MAP.CARTSET_ASSM_NO = LOG.CARTSET_ASSM_NO AND LOG.ACTIVITY = 7 GROUP BY MAP.PICKLIST_NO, LOG.USER_ID, LOG.LOC_NO ) PICK_END, LOCATION, COMPANY WHERE PICK_HEADER.PICKLIST_NO = PICK_START.PICKLIST_NO AND PICK_HEADER.USER_ID = PICK_START.USER_ID AND PICK_HEADER.PICK_LOC_NO = PICK_START.PICK_LOC_NO AND PICK_HEADER.PICKLIST_NO = PICK_END.PICKLIST_NO AND PICK_HEADER.USER_ID = PICK_END.USER_ID AND PICK_HEADER.PICK_LOC_NO = PICK_END.PICK_LOC_NO AND PICK_HEADER.PICK_LOC_NO = LOCATION.LOCATION_ID AND LOCATION.COMPANY_NO = COMPANY.COMPANY_NO