金蝶云星空已被使用的物料修改计量单位

有一些客户因为特殊原因导致物料已经被使用以后才发现物料计量单位错了，也有的物料确实是录错了使用了一段时间才发现，这种情况一般都需要废掉之前的物料新增个正确的，但是许多客户都不想这么干，所以研究了一下。

1、先查询一下正确计量单位，把计量单位主键复制出来

select * from T_BD_UNIT a join T_BD_UNIT_L al on a.FUNITID=al.FUNITID and al.FLOCALEID='2052' where a.FNUMBER like '%gen%'  --查询计量单位

2、修改物料计量单位，可以先查一下原来的计量单位，然后在update。

--查询物料所有单位

select FBASEUNITID,FWEIGHTUNITID,* from t_BD_MaterialBase where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--查询物料基本信息单位

select FSTOREUNITID,* from t_BD_MaterialStock where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--查询物料库存单位

select FSALEUNITID,FSALEPRICEUNITID,* from t_BD_MaterialSale where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--查询物料销售单位

select FPURCHASEUNITID,FPURCHASEPRICEUNITID,* from t_bd_MaterialPurchase where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--查询物料采购单位

select FSUBCONUNITID,FSUBCONPRICEUNITID,* from t_bd_MaterialSubcon where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--查询物料委外单位

select FPRODUCEUNITID,FMINISSUEUNITID,* from t_BD_MaterialProduce where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码') 查询物料生产单位

--修改物料计量单位

update T_BD_MATERIAL set FDOCUMENTSTATUS='D' where fnumber='物料编码'

--修改物料状态为【重新审核】  不修改物料状态系统不允许改计量单位  下边语句中109318是正确计量单位主键

update t_BD_MaterialBase set FBASEUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--更新物料基本信息单位

update t_BD_MaterialStock set FSTOREUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--更新物料库存单位

update t_BD_MaterialSale set FSALEUNITID='109318',FSALEPRICEUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--更新物料销售单位

update t_bd_MaterialPurchase set FPURCHASEUNITID='109318',FPURCHASEPRICEUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--更新物料采购单位

update t_bd_MaterialSubcon set FSUBCONUNITID='109318',FSUBCONPRICEUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--更新物料委外单位

update t_BD_MaterialProduce set FPRODUCEUNITID='109318',FBOMUNITID='109318',FMINISSUEUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--更新物料生产单位

update T_BD_MATERIAL set FDOCUMENTSTATUS='C' where fnumber='物料编码'

--修改物料状态为【已审核】

3、修改单据及库存相关表计量单位，如果某单据没有业务发生可以不修改

update T_SP_INSTOCKENTRY set FBASEUNITID='109318',FSTOCKUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--修改简单生产入库计量单位

update T_SP_PICKMTRLDATA set FUNITID='109318',FBASEUNITID='109318',FSTOCKUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--修改简单生产领料计量单位

update T_STK_INVINITDETAIL set FBASEUNITID='109318',FUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--修改初始库存计量单位

update T_PUR_ReqEntry  set FBASEUNITID='109318',FUNITID='109318'   where   FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')-- 修改采购申请单计量单位

update t_PUR_POOrderEntry  set FBASEUNITID='109318',FUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--修改采购订单计量单位

update  T_PUR_POORDERENTRY_F set FPRICEUNITID ='109318' where  FENTRYID in(select  FENTRYID   from  T_PUR_POORDERENTRY where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码'))  --修改采购订单计价单位

update T_PUR_ReceiveEntry set FBASEUNITID='109318',FUNITID='109318' , FSTOCKUNITID ='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码') --收料通知单

 update T_PUR_RECEIVEENTRY_F  set FPRICEUNITID = '109318'  where  FENTRYID in (select  FENTRYID   from  T_PUR_ReceiveEntry where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')) --收料通知单计价单位

update T_STK_INSTOCKENTRY  set FBASEUNITID='109318',FUNITID='109318' where   FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')-- 修改采购入库单计量单位

update T_STK_INSTOCKENTRY_F set FPRICEUNITID='109318',FREMAININSTOCKUNITID='109318'  where FENTRYID in(select  FENTRYID   from  T_STK_INSTOCKENTRY where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBE