WITH LOT_TEMPA AS(SELECT L.HIST_SEQ,L.TRX_TIME,L.FACTORY ,L.ORDER_ID ,L.START_EQUIP_ID EQUIP_ID,L.FINISH_GOOD_ID DEVICE,L.PRODUCT_ID PROCESS,L.PRODUCT_VER PROCESS_VER,L.ROUTE,L.ROUTE_VER,L.OPER,L.LOT_ID,L.QTY_1 PanelNum,L.CREATE_TIME ,L.START_TIME ,L.OPER_IN_TIME FROM WIPMLOTTHIST@MESDB LWHERE 1=1 AND L.LOT_DEL_YN = ' 'AND L.TRX_CODE IN ('START','CREATE')),LOT_TEMPB AS(SELECTL.HIST_SEQ,L.FACTORY ,L.ORDER_ID ,L.FINISH_EQUIP_ID EQUIP_ID,L.FINISH_GOOD_ID DEVICE,L.PRODUCT_ID PROCESS,L.PRODUCT_VER PROCESS_VER,L.ROUTE,L.ROUTE_VER,L.OPER,L.LOT_ID,L.QTY_1 PanelNum,L.CREATE_TIME ,L.START_TIME ,L.OPER_IN_TIME ,L.FINISH_TIME ,L.OLD_OPER FROM WIPMLOTTHIST@MESDB LWHERE 1=1 AND L.LOT_DEL_YN = ' 'AND L.TRX_CODE = 'FINISH')SELECTA.FACTORY,A.ORDER_ID,A.EQUIP_ID,A.DEVICE,F.FINISH_GOOD_GRP_4 PLANTFORM,A.PROCESS,A.PROCESS_VER,A.ROUTE,A.ROUTE_VER,A.OPER,A.LOT_ID,A.PanelNum,CASE WHEN A.CREATE_TIME=' ' THEN ' ' ELSE TO_CHAR(TO_DATE(A.CREATE_TIME,'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') END CREATE_TIME,CASE WHEN A.START_TIME=' ' THEN ' ' ELSE TO_CHAR(TO_DATE(A.START_TIME,'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') END START_TIME,CASE WHEN A.OPER_IN_TIME=' ' THEN ' ' ELSE TO_CHAR(TO_DATE(A.OPER_IN_TIME,'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') END OPER_IN_TIME,CASE WHEN B.FINISH_TIME=' ' THEN ' ' ELSE TO_CHAR(TO_DATE(B.FINISH_TIME,'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') END FINISH_TIME,CASE WHEN B.FINISH_TIME=' ' THEN 0 ELSE ROUND((TO_DATE(B.FINISH_TIME,'yyyymmddhh24miss')-TO_DATE(A.START_TIME,'yyyymmddhh24miss'))*24,2) END CYCLE_TIME,CASE WHEN A.START_TIME=' ' THEN ROUND((SYSDATE-TO_DATE(NVL(TRIM(A.LAST_FINISH_TIME),A.CREATE_TIME),'YYYYMMDDHH24MISS'))*24,2) ELSE ROUND((TO_DATE(A.START_TIME,'YYYYMMDDHH24MISS')-TO_DATE(NVL(TRIM(A.LAST_FINISH_TIME),A.CREATE_TIME),'YYYYMMDDHH24MISS'))*24,2) END WAIT_TIME,CASE WHEN B.FINISH_TIME=' ' THEN ROUND((SYSDATE-TO_DATE(NVL(TRIM(A.LAST_FINISH_TIME),A.CREATE_TIME),'YYYYMMDDHH24MISS'))*24,2) ELSE ROUND((TO_DATE(B.FINISH_TIME,'YYYYMMDDHH24MISS')-TO_DATE(NVL(TRIM(A.LAST_FINISH_TIME),A.CREATE_TIME),'YYYYMMDDHH24MISS'))*24,2) END WIP_TIMEFROM LOT_TEMPB B,WIPMFHGDDFNT@MESDB F,(SELECT (SELECT MAX(FINISH_TIME) FROM WIPMLOTTHIST@MESDB B WHERE B.LOT_ID=A.LOT_ID AND B.HIST_SEQ < A.HIST_SEQ AND B.ROUTE = A.ROUTE) LAST_FINISH_TIME,A.* FROM LOT_TEMPA A ) AWHERE 1=1 AND A.FACTORY = B.FACTORY(+)AND A.LOT_ID = B.LOT_ID(+)AND A.ORDER_ID = B.ORDER_ID(+)AND A.DEVICE = B.DEVICE(+)AND A.PROCESS = B.PROCESS(+)AND A.ROUTE = B.ROUTE(+)AND A.OPER = B.OLD_OPER(+)AND A.FACTORY = F.FACTORY (+)AND A.DEVICE = F.FINISH_GOOD_ID (+)AND A.PROCESS = F.PRODUCT_ID (+)AND A.FACTORY = NVL(TRIM('${FACTORY}'),A.FACTORY)AND A.LOT_ID = NVL(TRIM('${LOT_ID}'),A.LOT_ID)AND A.OPER = NVL(TRIM('${OPER}'),A.OPER)AND A.TRX_TIME >= TO_CHAR(TO_DATE(TRIM('${fromTime}'),'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24miss')AND A.TRX_TIME <= TO_CHAR(TO_DATE(TRIM('${toTime}'),'yyyy-mm-dd hh24:mi:ss'),'yyyymmddhh24miss')ORDER BY A.FACTORY,A.ORDER_ID,A.ROUTE,A.OPER,A.LOT_ID
