转移WMS货主数据

WMS2.0 获取库存

背景简介

由于SCM 重复下载 货主数据,可能导致相同货主编码 的数据 ，导致SCM传输订单失败

涉及系统

SCM    英克WMS（2.0）

所有需要清理的数据

--货品
UPDATE TPL_GOODS SET  GOODSOWNERID = 924 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 = 943
AND SUBSTR(GOODSOWNID,0,4) = 'SJ04'
);



--单位
UPDATE Tpl_go_company SET  GOODSOWNERID = 924 WHERE companyid IN (
SELECT companyid FROM Tpl_go_company A WHERE  SUBSTR(mdcode,0,4) = 'SJ04' AND GOODSOWNERID=943

);


--库存
SELECT A.*,A.rowid FROM TPL_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) = 'SJ04'
AND A.GOODSOWNERID = 943;


SELECT A.*,A.rowid FROM TPL_ST_QTY_LST A WHERE A.goodsownerid = 943;


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

UPDATE tpl_in_order SET  GOODSOWNERID = 924 WHERE INID IN (
  SELECT INID FROM tpl_in_order A   WHERE 1=1
  AND SUBSTR(A.srcno,0,4) = 'SJ04'
  AND A.goodsownerid = 943
);

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

UPDATE tpl_out_order SET  GOODSOWNERID = 924 WHERE OUTID IN (
  SELECT OUTID FROM tpl_out_order A   WHERE 1=1
  AND SUBSTR(A.srcexpno,0,4) = 'SJ04'
  AND A.goodsownerid = 943
);


UPDATE TPL_GO_COMPANY SET  GOODSOWNERID = 924 WHERE companyid IN (
  SELECT companyid  FROM TPL_GO_COMPANY A  WHERE 1=1
  AND SUBSTR(A.Mdcode,0,4) = 'SJ04'
  AND A.goodsownerid = 943
);


UPDATE WMS_TRANSPORT_ADDRESS SET  GOODSOWNERID = 924 WHERE TRANSID IN (
  SELECT A.TRANSID FROM WMS_TRANSPORT_ADDRESS A , TPL_GO_COMPANY_V B  WHERE 1=1
  AND A.GOCOMPANYID=B.COMPANYID 
  AND B.GCOMPANYNO = 'SJ04'
  AND A.GOODSOWNERID = 943
);



UPDATE GSP_ST_IO_RECORD SET  GOODSOWNERID = 924 WHERE inoutid IN (
  SELECT inoutid FROM GSP_ST_IO_RECORD A WHERE A.goodsownerid = 943
);

UPDATE TPL_OUT_ORDER_DTL SET  GOODSOWNERID = 924 WHERE outid IN (
  SELECT outid FROM TPL_OUT_ORDER_DTL A WHERE A.goodsownerid = 943
);


UPDATE WMS_IN_ORDER_DTL SET  GOODSOWNERID = 924 WHERE inid IN (
  SELECT inid FROM WMS_IN_ORDER_DTL A WHERE A.goodsownerid = 943
);


UPDATE WMS_OUT_ORDER_DTL SET  GOODSOWNERID = 924 WHERE outid IN (
  SELECT outid FROM WMS_OUT_ORDER_DTL A WHERE A.goodsownerid = 943
);

UPDATE WMS_QTY_CUT SET  GOODSOWNERID = 924 WHERE cutid IN (
  SELECT cutid FROM WMS_QTY_CUT A WHERE A.goodsownerid = 943
);


UPDATE WMS_QTY_CUT_DTL SET  GOODSOWNERID = 924 WHERE cutdtlid IN (
  SELECT cutdtlid FROM WMS_QTY_CUT_DTL A WHERE A.goodsownerid = 943
);


UPDATE WMS_ST_QTY_EVERYDAY SET  GOODSOWNERID = 924 WHERE goodsownerid = 943;


UPDATE wms_trade_order SET  GOODSOWNERID = 924 WHERE goodsownerid = 943;