摘要由AI智能服务提供本文描述了更正物料计量单位错误的过程，包括查询正确的计量单位并复制主键，通过SQL查询确认并修改物料的基本信息、库存、销售、采购等环节的计量单位，同时修改物料状态以允许修改，并确保单据及库存相关表计量单位的一致性。
本文讲述了因物料计量单位错误而需要更正的过程。首先，查询正确的计量单位并复制主键。接着，通过多步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='物料编码')
--修改库存余额表计量单位
----------------------
赞 73
打赏