金蝶K3 取消批次管理的SQL

K3取消批次管理的SQL脚本


由于期初启用了批次管理，但后期进行退货再加工流程时发现出现问题，无法流转，所以，必须取消批次。由于已经存在大量业务数据，不可能再重新开始，只能通过底层语句进行批次的取消操作。


处理步骤：
找到4个和批次有关的表
分别是：ICInvInitial初始化数据表 ICBal存货余额表 poInvBal代管余额表（不用理会也可） ICInvBal库房存货余额表 ICInventory即时库存（记录可以清空，通过校对库存，数据自动生成）


处理原则：相同库房里取消批次后，是否有相同记录，有则合并


一些必要的字段：FItemID商品内码 FstockID库房内码


这三个字段是需要清除数据的字段：FBatchNo批次号 FKFPeriod保质期 FKFDate生产/采购日期


因为我们公司的帐套还同时启用了保质期，所以最初合并记录没有到位，后期将保质期FKFPeriod数据清除，进行二次合并，通过。




以下是取消批次的脚本（没有处理保质期），拷贝到查询分析器中执行即可完成。
*/





--1.初始化表
--将数据汇总转入临时表
SELECT FBrNo,FPeriod,FStockID,FItemID,CONVERT(VARCHAR(200),'') AS FBatchNo,SUM(FBegQty) AS FBegQty,SUM(FReceive) AS FReceive,SUM(FSend) AS FSend,
SUM(FYtdReceive) AS FYtdReceive, SUM(FYtdSend) AS FYtdSend,SUM(FEndQty) AS FEndQty, SUM(FBegBal) AS FBegBal,
SUM(FDebit) AS FDebit,SUM(FCredit) AS FCredit,SUM(FYtdDebit) AS FYtdDebit,SUM(FYtdCredit) AS FYtdCredit,SUM(FEndBal) AS FEndBal,
SUM(FBegDiff) AS FBegDiff,SUM(FReceiveDiff) AS FReceiveDiff,SUM(FSendDiff) AS FSendDiff,FBillInterID,FUnitID,
SUM(FAuxBegQty) AS FAuxBegQty,SUM(FYtdBegQty) AS FYtdBegQty,SUM(FYtdAuxBegQty) AS FYtdAuxBegQty,SUM(FYtdAuxReceive) AS FYtdAuxReceive,
SUM(FYtdAuxSend) AS FYtdAuxSend,SUM(FYtdBegBal) AS FYtdBegBal,SUM(FYtdBegDiff) AS FYtdBegDiff, SUM(FYtdReceiveDiff) AS FYtdReceiveDiff,
SUM(FYtdSendDiff) AS FYtdSendDiff,FKFDate,FKFPeriod,FSPID,SUM(FSecBegQty) AS FSecBegQty,SUM(FSecReceive) As FSecReceive,
SUM(FSecSend) AS FSecSend,SUM(FSecYtdReceive) AS FSecYtdReceive,SUM(FSecYtdSend) AS FSecYtdSend,SUM(FSecEndQty) AS FSecEndQty,
FSecUnitID,FStockInDate,FAuxPropID,0 AS FSecCoefficient,SUM(FAuxUnitBegQty) AS FAuxUnitBegQty,SUM(FYtdAuxUnitBegQty) AS FYtdAuxUnitBegQty,
SUM(FYtdAuxUnitSend) AS FYtdAuxUnitSend,SUM(FYtdAuxUnitReceive) AS FYtdAuxUnitReceive,FSNListID
INTO #TempICInvInitial
FROM ICInvInitial GROUP BY FBrNo,FPeriod,FStockID,FItemID,FBillInterID,FUnitID,FKFDate,FKFPeriod,FSPID,FSecUnitID,FStockInDate,FAuxPropID,FSNListID
--清空表
TRUNCATE TABLE ICInvInitial
--转回数据
INSERT INTO ICInvInitial SELECT * FROM #TempICInvInitial
--倒算辅助换算率
UPDATE ICInvInitial SET FSecCoefficient = CASE WHEN FSecBegQty<>0 THEN FBegQty/FSecBegQty ELSE 0 END
--删除临时表
DROP T

ABLE #TempICInvInitial


--2.余额表
--将数据汇总转入临时表
SELECT FBrNo,FYear,FPeriod,FItemID,CONVERT(VARCHAR(200),'') AS FBatchNo,SUM(FBegQty) AS FBegQty,SUM(FReceive) AS FReceive,SUM(FSend) AS FSend,
SUM(FYtdReceive) AS FYtdReceive, SUM(FYtdSend) AS FYtdSend,SUM(FEndQty) AS FEndQty, SUM(FBegBal) AS FBegBal,
SUM(FDebit) AS FDebit,SUM(FCredit) AS FCredit,SUM(FYtdDebit) AS FYtdDebit,SUM(FYtdCredit) AS FYtdCredit,SUM(FEndBal) AS FEndBal,
SUM(FBegDiff) AS FBegDiff,SUM(FReceiveDiff) AS FReceiveDiff,SUM(FSendDiff) AS FSendDiff,SUM(FEndDiff) AS FEndDiff,
FBillInterID,FEntryID,FStockGroupID,SUM(FYtdReceiveDiff) AS FYtdReceiveDiff,SUM(FYtdSendDiff) AS FYtdSendDiff,
SUM(FSecBegQty) AS FSecBegQty,SUM(FSecReceive) As FSecReceive,SUM(FSecSend) AS FSecSend,SUM(FSecYtdReceive) AS FSecYtdReceive,
SUM(FSecYtdSend) AS FSecYtdSend,SUM(FSecEndQty) AS FSecEndQty,FStockInDate,FAuxPropID
INTO #TempICBal
FROM ICBal GROUP BY FBrNo,FYear,FPeriod,FItemID,FBillInterID,FEntryID,FStockGroupID,FStockInDate,FAuxPropID
--清空表
TRUNCATE TABLE ICBal
--转回数据
INSERT INTO ICBal SELECT * FROM #TempICBal
--删除临时表
DROP TABLE #TempICBal


--3.库存余额表
--将数据汇总转入临时表
SELECT FBrNo,FYear,FPeriod,FStockID,FItemID,CONVERT(VARCHAR(200),'') AS FBatchNo,SUM(FBegQty) AS FBegQty,SUM(FReceive) AS FReceive,SUM(FSend) AS FSend,
SUM(FYtdReceive) AS FYtdReceive, SUM(FYtdSend) AS FYtdSend,SUM(FEndQty) AS FEndQty, SUM(FBegBal) AS FBegBal,
SUM(FDebit) AS FDebit,SUM(FCredit) AS FCredit,SUM(FYtdDebit) AS FYtdDebit,SUM(FYtdCredit) AS FYtdCredit,SUM(FEndBal) AS FEndBal,
SUM(FBegDiff) AS FBegDiff,SUM(FReceiveDiff) AS FReceiveDiff,SUM(FSendDiff) AS FSendDiff,SUM(FEndDiff) AS FEndDiff,FBillInterID,FStockPlaceID,
FKFPeriod,FKFDate,SUM(FYtdReceiveDiff) AS FYtdReceiveDiff,SUM(FYtdSendDiff) AS FYtdSendDiff,SUM(FSecBegQty) AS FSecBegQty,SUM(FSecReceive) As FSecReceive,
SUM(FSecSend) AS FSecSend,SUM(FSecYtdReceive) AS FSecYtdReceive,SUM(FSecYtdSend) AS FSecYtdSend,SUM(FSecEndQty) AS FSecEndQty,
FAuxPropID,FStockInDate
INTO #TempICInvBal
FROM ICInvBal GROUP BY FBrNo,FYear,FPeriod,FStockID,FItemID,FBillInterID,FStockPlaceID,FKFPeriod,FKFDate,FAuxPropID,FStockInDate
--清空表
TRUNCATE TABLE ICInvBal
--转回数据
INSERT INTO ICInvBal SELECT * FROM #TempICInvBal
--删除临时表
DROP TABLE #TempICInvBal


-----------===================
--代管余额表 可以不用处理
--将数据汇总转入临时表
SELECT FBrNo,FYear,FPeriod,FStockID,FItemID,CONVERT(VARCHAR(200),'') AS FBatchNo,SUM(FBegQty) AS FBegQty,
SUM(FReceive) AS FReceive,SUM(FSend) AS FSend,
SUM(FYtdReceive) AS FYtdReceive, SUM(FYtdSend) AS FYtdSend,SUM(FEndQty) AS FEndQty, SUM(FBegBal) AS FBegBal,
SUM(FDebit) AS FDebit,SUM(FCredit) AS FCredit,SUM(FYtdDebit) AS FYtdDebit,SUM(FYtdCredit) AS FYtdCredit,
SUM(FEndBal) AS FEndBal,
SUM(FBegDi

ff) AS FBegDiff,SUM(FReceiveDiff) AS FReceiveDiff,SUM(FSendDiff) AS FSendDiff,SUM(FEndDiff) AS FEndDiff,FBillInterID,
FKFPeriod,FKFDate,sum(FSPID) FSPID,sum(FDCSPID) FDCSPID,SUM(FSecBegQty) AS FSecBegQty,SUM(FSecReceive) As FSecReceive,
SUM(FSecSend) AS FSecSend,SUM(FSecYtdReceive) AS FSecYtdReceive,SUM(FSecYtdSend) AS FSecYtdSend,
SUM(FSecEndQty) AS FSecEndQty,
FAuxPropID
INTO #TemppoInvBal
FROM poInvBal GROUP BY FBrNo,FYear,FPeriod,FStockID,FItemID,FBillInterID,FKFPeriod,FKFDate,FAuxPropID
--清空表
TRUNCATE TABLE poInvBal
--转回数据
INSERT INTO poInvBal SELECT * FROM #TemppoInvBal
--删除临时表
DROP TABLE #TemppoInvBal
--insert into poInvBal select * from poInvBalback
---


--4. *******************实仓即时库存表
--将数据汇总转入临时表
SELECT FBrNo,FItemID,CONVERT(VARCHAR(200),'') AS FBatchNo,FStockID,SUM(FQty) AS FQty,SUM(FBal) AS FBal,
FStockPlaceID,FKFPeriod,FKFDate,SUM(FQtyLock) AS FQtyLock,FAuxPropID,SUM(FSecQty) AS FSecQty
INTO #TempIcInventory
FROM ICInventory GROUP BY FBrNo,FItemID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FAuxPropID
--清空表
TRUNCATE TABLE ICInventory
--转回数据
INSERT INTO ICInventory SELECT * FROM #TempIcInventory
--删除临时表
DROP TABLE #TempIcInventory


--代管库存表 虚仓即时库存表 可以不用处理
--将数据汇总转入临时表
SELECT FBrNo,FItemID,CONVERT(VARCHAR(200),'') AS FBatchNo,FStockID,SUM(FQty) AS FQty,SUM(FBal) AS FBal,
FStockPlaceID,FKFPeriod,FKFDate,FStockTypeID,FAuxPropID,SUM(FSecQty) AS FSecQty
INTO #TempPOInventory
FROM POInventory GROUP BY FBrNo,FItemID,FStockID,FStockPlaceID,FKFPeriod,FKFDate,FStockTypeID,FAuxPropID
--清空表
TRUNCATE TABLE POInventory
--转回数据
INSERT INTO POInventory SELECT * FROM #TempPOInventory
--删除临时表
DROP TABLE #TempPOInventory





--清除物料批次属性 商品表里
UPDATE t_ICItem SET FBatchManager = 0 WHERE FBatchManager = 1