摘要由AI智能服务提供本文讲述了因物料计量单位错误而需要更正的过程。首先，查询正确的计量单位并复制主键。接着，通过多步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 '%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 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='物料编码')
--修改库存余额表计量单位
赞 42
打赏