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 L

WHERE 1=1

AND L.LOT_DEL_YN = ' '

AND L.TRX_CODE IN ('START','CREATE')

),

LOT_TEMPB AS

(SELECT

L.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 L

WHERE 1=1

AND L.LOT_DEL_YN = ' '

AND L.TRX_CODE = 'FINISH')

SELECT

A.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_TIME

FROM 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

) A

WHERE 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