SCM 提示物流 货品单位库存所属货主ID出错

SCM 提示物流 货品单位库存所属货主ID出错

背景简介

由于不知名原因,下发的单位,货品,所属货主出错,导致收货的库存也会出错.从正确的货主,改变为另一个不相关的.需要修改货主ID.

涉及系统

SCM    英克WMS（2.0）

需要处理以下数据

SELECT goodsownercode FROM tpl_goodsowner group by goodsownercode having count(1)>1

SELECT a.goodsownercode, A.*,A.rowid FROM tpl_goodsowner A WHERE goodsownercode is null

--货品
UPDATE TPL_GOODS SET  GOODSOWNERID = 922 WHERE WAREGOODSID IN (
SELECT A.WAREGOODSID FROM TPL_GOODS A,TPL_PUB_GOODS B  WHERE A.WAREGOODSID= B.GOODSID AND A.GOODSOWNID = B.MDMGOODSCODE 
AND GOODSOWNERID = 942
AND SUBSTR(GOODSOWNID,0,4) = 'SJ02'
)


SELECT  A.*,A.rowid FROM TPL_GOODS A,TPL_PUB_GOODS B  WHERE A.WAREGOODSID= B.GOODSID AND A.GOODSOWNID = B.MDMGOODSCODE 
AND GOODSOWNERID = 942
AND SUBSTR(GOODSOWNID,0,4) = 'SJ02'


--单位
UPDATE Tpl_go_company SET  GOODSOWNERID = 922 WHERE companyid IN (
SELECT companyid FROM Tpl_go_company A WHERE  SUBSTR(mdcode,0,4) = 'SJ02'

)

--库存
SELECT A.*,A.rowid FROM WMS_ST_QTY_LST A,TPL_PUB_GOODS B,TPL_GOODS C  WHERE A.GOODSID= B.GOODSID 
AND C.WAREGOODSID= B.GOODSID 
AND C.GOODSOWNID = B.MDMGOODSCODE 
AND SUBSTR(B.MDMGOODSCODE,0,4) = 'SJ02'
AND A.GOODSOWNERID = 942





--入库单
UPDATE wms_in_order SET  GOODSOWNERID = 922 WHERE INID IN (
  SELECT INID FROM wms_in_order A   WHERE 1=1
  AND SUBSTR(A.srcno,0,4) = 'SJ02'
  AND A.goodsownerid = 942
);

UPDATE tpl_in_order SET  GOODSOWNERID = 922 WHERE INID IN (
  SELECT INID FROM tpl_in_order A   WHERE 1=1
  AND SUBSTR(A.srcno,0,4) = 'SJ02'
  AND A.goodsownerid = 942
)

--收货单
UPDATE wms_in_order_dtl SET  GOODSOWNERID = 922 WHERE INDTLID IN (
SELECT A.INDTLID  FROM wms_in_order_dtl A ,wms_in_order B  WHERE 1=1
  AND a.inid = b.inid
  AND SUBSTR(B.srcno,0,4) = 'SJ02'
  AND A.goodsownerid = 942
);

--出库单
UPDATE wms_out_order SET  GOODSOWNERID = 922 WHERE OUTID IN (
  SELECT OUTID FROM wms_out_order A   WHERE 1=1
  AND SUBSTR(A.srcexpno,0,4) = 'SJ02'
  AND A.goodsownerid = 942
);

UPDATE tpl_out_order SET  GOODSOWNERID = 922 WHERE OUTID IN (
  SELECT OUTID FROM tpl_out_order A   WHERE 1=1
  AND SUBSTR(A.srcexpno,0,4) = 'SJ02'
  AND A.goodsownerid = 942
)