鲁莽style(提问者) 回复 CD20160914SELECT
CASE WHEN T.PROCESSOPERATIONNAME =\'M701\' THEN \'待RW IN\'
WHEN T.PROCESSOPERATIONNAME =\'MR101\' THEN \'待拆解\'
WHEN T.PROCESSOPERATIONNAME =\'MR112\' THEN \'待贴膜\'
WHEN T.PROCESSOPERATIONNAME =\'M710\' THEN \'待 OC_FA\'
WHEN T.PROCESSOPERATIONNAME =\'M711\' THEN \'待整合判级\'
WHEN T.PROCESSOPERATIONNAME =\'MR200\' THEN \'待F级 IN\'
WHEN T.PROCESSOPERATIONNAME =\'MR102\' THEN \'待POL\'
WHEN T.PROCESSOPERATIONNAME =\'MR103\' THEN \'待切POL\'
WHEN T.PROCESSOPERATIONNAME =\'MR104\' THEN \'待B/D\'
WHEN T.PROCESSOPERATIONNAME =\'MR105\' THEN \'试修\'
WHEN T.PROCESSOPERATIONNAME =\'MR106\' THEN \'待LASER\'
WHEN T.PROCESSOPERATIONNAME =\'MR107\' THEN \'待solder\'
WHEN T.PROCESSOPERATIONNAME =\'MR108\' THEN \'待CF Film\'
WHEN T.PROCESSOPERATIONNAME =\'MR120\' THEN \'待点灯\'
WHEN T.PROCESSOPERATIONNAME =\'MR121\' THEN \'待外观\'
WHEN T.PROCESSOPERATIONNAME =\'MR152\' THEN \'待投主线\'
WHEN T.PROCESSOPERATIONNAME =\'MR153\' THEN \'待投主线\'
WHEN T.PROCESSOPERATIONNAME =\'M720\' THEN \'待NDF\'
WHEN T.PROCESSOPERATIONNAME =\'MR150\' THEN \'待投主线\'
WHEN T.PROCESSOPERATIONNAME =\'MR1010\' THEN \'待祛除\'
WHEN T.PROCESSOPERATIONNAME =\'M702\' THEN \'待维修NG FA IN\'
WHEN T.PROCESSOPERATIONNAME =\'M7010\' THEN \'待 MDL_FA\'
WHEN T.PROCESSOPERATIONNAME =\'MR1040\' THEN \'UV 涂覆\'
WHEN T.PROCESSOPERATIONNAME =\'MR1020\' THEN \'POL 撕除\'
WHEN T.PROCESSOPERATIONNAME =\'MR100\' THEN \'rework待接收\'
END AS 站点名,T.*,
RH.DESCRIPTION AS RDESCRIPTION,
RH.REASONCODETYPE,
(sysdate-T.LASTEVENTTIME) *24 AS 呆滞小时,
CASE WHEN (sysdate-T.LASTEVENTTIME) *24 = 4 AND (sysdate-T.LASTEVENTTIME) *24 = 12 AND (sysdate-T.LASTEVENTTIME) *24 = 24 AND (sysdate-T.LASTEVENTTIME) *24 = 48 AND (sysdate-T.LASTEVENTTIME) *24 = 72 AND (sysdate-T.LASTEVENTTIME) *24 = 120 AND (sysdate-T.LASTEVENTTIME) *24 = 168 AND (sysdate-T.LASTEVENTTIME) *24 = 240 AND (sysdate-T.LASTEVENTTIME) *24 = 720 AND (sysdate-T.LASTEVENTTIME) *24 = 1440 AND (sysdate-T.LASTEVENTTIME) *24 = 2160 THEN \'90天以上\'
END AS \"滞留时长\"
FROM
BIOTADM_MNT_LOT T
LEFT OUTER JOIN (SELECT * FROM BIOTADM_MES_REASONCODE WHERE SYSTEMNAME =\'MNT\') RH ON(T.REASONCODE=RH.REASONCODE)
WHERE 1=1
AND (RH.REASONCODETYPE = \'Defect Code FI\' OR T.REASONCODETYPE IS NULL)
AND T.PROCESSOPERATIONNAME=\'MR102\'
AND (T.PROCESSOPERATIONNAME LIKE \'MR%\' OR T.PROCESSOPERATIONNAME IN (\'M701\',\'M710\',\'M711\',\'M7010\',\'M720\',\'M702\'))
AND length(PRODUCTREQUESTNAME)=9
AND T.LOTSTATE = \'Released\'
AND T.LASTEVENTNAME !=\'Scrap\'
AND T.LASTEVENTNAME NOT IN (\'ShipToFGMS\')
AND SUBSTR(PRODUCTREQUESTNAME,1,1) IN(\'1\',\'2\',\'8\')
--${IF(LEN(WORKORDER)=0,\"\",\"AND substr(T.PRODUCTREQUESTNAME,1,1) IN (\'\"+WORKORDER+\"\')\")}
ORDER BY DECODE(T.PROCESSOPERATIONNAME,\'M701\',1,\'M711\',2,\'MR200\',3,\'M710\',4,\'M7010\',5,\'M720\',6,\'MR106\',7,\'MR101\',8,\'MR108\',9,\'MR112\',10,\'MR1020\',11,\'MR102\',12,\'MR103\',13,\'MR1010\',14,\'MR107\',15,\'MR104\',16,\'MR105\',17,\'MR1040\',18,\'MR120\',19,\'M702\',20,\'MR121\',21,\'MR150\',22,\'MR152\',23,\'MR153\',24),PROCESSOPERATIONNAME,
CASE WHEN (sysdate-T.LASTEVENTTIME) *24 = 4 AND (sysdate-T.LASTEVENTTIME) *24 = 12 AND (sysdate-T.LASTEVENTTIME) *24 = 24 AND (sysdate-T.LASTEVENTTIME) *24 = 48 AND (sysdate-T.LASTEVENTTIME) *24 = 72 AND (sysdate-T.LASTEVENTTIME) *24 = 120 AND (sysdate-T.LASTEVENTTIME) *24 = 168 AND (sysdate-T.LASTEVENTTIME) *24 = 240 AND (sysdate-T.LASTEVENTTIME) *24 = 720 AND (sysdate-T.LASTEVENTTIME) *24 = 1440 AND (sysdate-T.LASTEVENTTIME) *24 = 2160 THEN \'90天以上\'
END