UPDATE dbo.POR_LOT SET POR_LOT.SHIPPED_FLAG =B.SHIPPED_FLAG

FROM POR_LOT A INNER JOIN ( SELECT T5.LOT_NUMBER,(CASE WHEN t3.TYPE ='生产出库' THEN 0 WHEN T3.TYPE='销售出库' THEN 1 end) SHIPPED_FLAG

FROM WMS_STORAGE_OUT_DETAIL T2

INNER JOIN WMS_STORAGE_OUT T3 ON T2.ORDER_ID = T3.ORDER_ID

INNER JOIN dbo.WIP_CONSIGNMENT T4 ON T2.PALLET_NO=T4.PALLET_NO

INNER JOIN dbo.WIP_CONSIGNMENT_DETAIL T5 ON T4.PALLET_NO=T5.PALLET_NO

WHERE T3.ORDER_NUMBER = 'O002'

) B ON B.LOT_NUMBER=A.LOT_NUMBER

 

 

 

UPDATE t3

SET DataFlag = -1

FROM ( SELECT PRODUCT_ID ,

MAX(CHANGE_DATE) AS CHANGE_DATE

FROM POR_MATERIAL_JK

WHERE DataFlag = 1

GROUP BY PRODUCT_ID

) t1 ,

dbo.POR_MATERIAL_JK t3 ,

@userTable t2

WHERE t2.PRODUCT_ID = t1.PRODUCT_ID

AND t2.PRODUCT_ID = t3.PRODUCT_ID

AND t2.CHANGE_DATE > t1.CHANGE_DATE;