金蝶K3通过SQL语句取消保质期管理实例
--以下是在公司实务中取消金蝶K3物料中误勾选“启用保质期管理”的语句，执行前做好账套数据备份。
--以下语句的条件：fitemid = 3514，可以通过替换内码为3514实现取消该物料保质期管理
UPDATE t_icitem
SET fiskfperiod = 0,
fkfperiod = 0
WHERE fiskfperiod = 1
AND fkfperiod <> 0
AND fitemid = 3514 --是否进行保质期管理，0表示不启用保质期管理
UPDATE t_icitem
SET fiskfperiod = 0,
fkfperiod = 0
WHERE fiskfperiod = 1
AND fkfperiod = 0
AND fitemid = 3514 --是否进行保质期管理，0表示不启用保质期管理
UPDATE icinvinitial
SET fkfdate = '',
fkfperiod = 0
WHERE fitemid = 3514 --初始化数据表,所影响的行数为 2 行）
UPDATE icinvbal
SET fkfdate = '',
fkfperiod = 0
WHERE fitemid = 3514 --库房存货余额表，如果更新前有错误提示则在完成最后校对即时库存后执行，（所影响的行数为 236 行），前执行提示：违反了 PRIMARY KEY 约束 'pk_ICInvBal'。不能在对象 'ICInvBal' 中插入重复键。
UPDATE icinvbackup
SET fkfdate = '',
fkfperiod = 0
WHERE fitemid = 3514 --本语句更新不成功，违反了 PRIMARY KEY 约束 'pk_ICInvBackup'。不能在对象 'ICInvBackup' 中插入重复键。
UPDATE icstockbillentry
SET FKFPeriod = 0,
FKFDate = NULL
WHERE fitemid = 3514 --是否进行保质期管理，0表示不启用保质期管理，所影响的行数为 1835 行）
UPDATE ICSaleEntry
SET FKFPeriod = 0,
FKFDate = NULL
WHERE fitemid = 3514 --是否进行保质期管理，0表示不启用保质期管理（所影响的行数为 1664 行）
SELECT *
INTO #t1
FROM icinvbal
WHERE fitemid IN(SELECT DISTINCT fitemid
FROM icinvbal
WHERE ( fkfdate <> ''
OR fkfperiod <> 0 )
AND fitemid = 3514)
--（所影响的行数为 0 行）
SELECT FBrNo,
FItemID,
FBatchNo,
FStockID,
FStockPlaceID,
fyear,
fperiod,
Sum(FBegQty) FBegQty,
Sum(FReceive) FReceive,
Sum(FSend) FSend,
Sum(FYtdReceive) FYtdReceive,
Sum(FYtdSend) FYtdSend,
Sum(FEndQty) FEndQty,
Sum(FBegBal) FBegBal,
Sum(FDebit) FDebit,
Sum(FCredit) FCredit,
Sum(FYtdDebit) FYtdDebit,
Sum(FYtdCredit) FYtdCredit,
Sum(FEndBal) FEndBal,
Sum(FBegDiff) FBegDiff,
Sum(FReceiveDiff) FReceiveDiff,
Sum(FSendDiff) FSendDiff,
Sum(FEndDiff) FEndDiff,
Sum(FYtdReceiveDiff) FYtdReceiveDiff,
Sum(FYtdSendDiff) FYtdSendDiff
INTO #t2
FROM #t1
WHERE fitemid = 3514
GROUP BY FBrNo,
FItemID,
FBatchNo,
FStockID,
FStockPlaceID,
fyear,
fperiod
HAVING Count(*) > 1
--（所影响的行数为 0 行）
SELECT DISTINCT t1.*
FROM icinvbal t1,
#t2 t2
WHERE t1.fstockid = t2.fstockid
AND t1.fitemid = t2.fitemid
AND t1.fyear = t2.fyear
AND t1.fperiod = t2.fperiod
AND t1.FBatchNo = t2.FBatchNo
AND t1.FStockPlaceID = t2.FStockPlaceID
AND t1.fitemid = 3514
ORDER BY t1.fitemid
--（所影响的行数为 4 行）
DELETE t1
FROM icinvbal t1,
#t2 t2
WHERE t1.fstockid = t2.fstockid
AND t1.fitemid = t2.fitemid
AND t1.fyear = t2.fyear
AND t1.fperiod = t2.fperiod
AND t1.FBatchNo = t2.FBatchNo
AND t1.FStockPlaceID = t2.FStockPlaceID
AND t1.fitemid = 3514
--（所影响的行数为 4 行）
INSERT INTO icinvbal
(FBrNo,
fyear,
FPeriod,
FStockID,
FItemID,
FBatchNo,
FBegQty,
FReceive,
FSend,
FYtdReceive,
FYtdSend,
FEndQty,
FBegBal,
FDebit,
FCredit,
FYtdDebit,
FYtdCredit,
FEndBal,
FBegDiff,
FReceiveDiff,
FSendDiff,
FBillInterID,
FYtdReceiveDiff,
FYtdSendDiff,
fenddiff,
FKFDate,
FKFPeriod,
fstockplaceid)
SELECT 0 FBrNo,
fyear,
FPeriod,
FStockID,
fitemid,
FBatchNo,
FBegQty,
FReceive,
FSend,
FYtdReceive,
FYtdSend,
FEndQty,
FBegBal,
FDebit,
FCredit,
FYtdDebit,
FYtdCredit,
FEndBal,
FBegDiff,
FReceiveDiff,
FSendDiff,
0 fbillinterid,
FYtdReceiveDiff,
FYtdSendDiff,
fenddiff,
'',
0,
fstockplaceid
FROM #t2
--（所影响的行数为 2 行）
DROP TABLE #t1
DROP TABLE #t2
--===以下修改icinvinitial
SELECT *
INTO #t1
FROM icinvinitial
WHERE fitemid IN (SELECT DISTINCT fitemid
FROM icinvinitial
WHERE fitemid = 3514
AND ( fkfdate <> ''
OR fkfperiod <> 0 ))
--（所影响的行数为 0 行）
SELECT FBrNo,
FItemID,
FBatchNo,
FStockID,
FSPID,
fperiod,
Sum(FBegQty) FBegQty,
Sum(FReceive) FReceive,
Sum(FSend) FSend,
Sum(FYtdReceive) FYtdReceive,
Sum(FYtdSend) FYtdSend,
Sum(FEndQty) FEndQty,
Sum(FBegBal) FBegBal,
Sum(FDebit) FDebit,
Sum(FCredit) FCredit,
Sum(FYtdDebit) FYtdDebit,
Sum(FYtdCredit) FYtdCredit,
Sum(FEndBal) FEndBal,
Sum(FBegDiff) FBegDiff,
Sum(FReceiveDiff) FReceiveDiff,
Sum(FSendDiff) FSendDiff,
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) FYtdBegDiff,
Sum(FYtdReceiveDiff) FYtdReceiveDiff,
Sum(FYtdSendDiff) FYtdSendDiff,
Max(FBillInterID) AS FBillInterID,
Max (FUnitID) AS FUnitID
INTO #t2
FROM #t1
WHERE fitemid = 3514
GROUP BY FBrNo,
FItemID,
FBatchNo,
FStockID,
FSPID,
fperiod
HAVING Count(*) > 1
--（所影响的行数为 0 行）
SELECT DISTINCT t1.*
FROM icinvinitial t1,
#t2 t2
WHERE t1.fstockid = t2.fstockid
AND t1.fitemid = t2.fitemid
AND t1.fperiod = t2.fperiod
AND t1.FBatchNo = t2.FBatchNo
AND t1.FSPID = t2.FSPID
AND t1.fitemid = 3514
ORDER BY t1.fitemid
--（所影响的行数为 0 行）
DELETE t1
FROM icinvinitial t1,
#t2 t2
WHERE t1.fstockid = t2.fstockid
AND t1.fitemid = t2.fitemid
AND t1.fperiod = t2.fperiod
AND t1.FBatchNo = t2.FBatchNo
AND t1.FSPID = t2.FSPID
AND t1.fitemid = 3514
--（所影响的行数为 0 行）
INSERT INTO icinvinitial
(FBrNo,
FPeriod,
FStockID,
FItemID,
FBatchNo,
FBegQty,
FReceive,
FSend,
FYtdReceive,
FYtdSend,
FEndQty,
FBegBal,
FDebit,
FCredit,
FYtdDebit,
FYtdCredit,
FEndBal,
FBegDiff,
FReceiveDiff,
FSendDiff,
FBillInterID,
FUnitID,
FAuxBegQty,
FYtdBegQty,
FYtdAuxBegQty,
FYtdAuxReceive,
FYtdAuxSend,
FYtdBegBal,
FYtdBegDiff,
FYtdReceiveDiff,
FYtdSendDiff,
FKFDate,
FKFPeriod,
FSPID)
SELECT FBrNo,
FPeriod,
FStockID,
FItemID,
FBatchNo,
FBegQty,
FReceive,
FSend,
FYtdReceive,
FYtdSend,
FEndQty,
FBegBal,
FDebit,
FCredit,
FYtdDebit,
FYtdCredit,
FEndBal,
FBegDiff,
FReceiveDiff,
FSendDiff,
FBillInterID,
FUnitID,
FAuxBegQty,
FYtdBegQty,
FYtdAuxBegQty,
FYtdAuxReceive,
FYtdAuxSend,
FYtdBegBal,
FYtdBegDiff,
FYtdReceiveDiff,
FYtdSendDiff,
'',
0,
FSPID
FROM #t2
--（所影响的行数为 0 行）
DROP TABLE #t1
DROP TABLE #t2
EXEC Checkinventory
***本文档到此结束***
不知道是不是物料上面少更新了，一开始参照这个https://vip.kingdee.com/article/162186566659057664?productLineId=7&lang=zh-CN，FKFDate = NULL更新成0，不是空值，照着老师的修改一下就好了
金蝶K3通过SQL语句取消保质期管理实例
本文2024-09-16 15:50:46发表“k3wise知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3wise-5436.html
