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

本文讲述了因物料计量单位错误而需要更正的过程。首先，查询正确的计量单位并复制主键。接着，通过多步SQL查询确认并修改物料的基本信息、库存、销售、采购等各个环节的计量单位。为执行这些修改，需先修改物料状态为“重新审核”，更新各相关表项的计量单位后，再将物料状态改回“已审核”。此外，还涉及修改单据及库存相关表的计量单位，确保所有相关记录的一致性

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 '%Pcs%'  --查询计量单位

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 FNUMBER='物料编码')) --修改采购入库单计价单位和采购单位

update T_SAL_ORDERENTRY set FBASEUNITID='109318',FUNITID='109318',FSTOCKUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')

--修改销售订单明细计量单位

update  T_SAL_ORDERENTRY_F set FPRICEUNITID ='109318',FSETPRICEUNITID ='109318' where  FENTRYID in(select  FENTRYID   from  T_SAL_ORDERENTRY where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码'))

--修改销售订单计价单位和定价单位

update T_SAL_DELIVERYNOTICEENTRY set FBASEUNITID='109318',FUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')

--修改发货通知单计量单位

update  T_SAL_DELIVERYNOTICEENTRY_F set FPRICEUNITID ='109318'  where  FENTRYID in(select  FENTRYID   from  T_SAL_DELIVERYNOTICEENTRY where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')) --修改发货通知单计价单位

update T_SAL_OUTSTOCKENTRY set FBASEUNITID='109318',FUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--修改销售出库单计量单位

update  T_SAL_OUTSTOCKENTRY_F set FPRICEUNITID ='109318',FSALUNITID ='109318'  where  FENTRYID in(select  FENTRYID   from  T_SAL_OUTSTOCKENTRY where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码'))--修改销售出库单计价单位和销售单位

update t_AR_receivableEntry set  FBASICUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')

--修改应收单计量单位

update T_STK_StockConvertEntry  set FBASEUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--修改库存状态转换单单位

update T_STK_ASSEMBLYSUBITEM  set FBASEUNITID='109318',FUNITID='109318' where   FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')

--修改组装拆卸单子件表计量单位

update T_STK_ASSEMBLYPRODUCT  set FBASEUNITID='109318',FUNITID='109318' where   FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')

--修改组装拆卸单成品表计量单位

update T_BD_LOTMASTERBILLTRACE set FBASEUNITID='109318',FUNITID='109318'  where flotid in (select  flotid from T_BD_LOTMASTER where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码'))--修改批号跟踪表

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

update T_STK_MISDELIVERYENTRY set FBASEUNITID='109318',FUNITID='109318'   where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')"--修改其他出库单

update T_STK_INVENTORY set FBASEUNITID='109318',FSTOCKUNITID='109318' where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')--修改即时库存单计量单位

update T_STK_INVBAL set  FBASEUNITID='109318'  where FMATERIALID in(select FMATERIALID from T_BD_MATERIAL where FNUMBER='物料编码')

--修改库存余额表计量单位

----------------------