【问题描述】

商贸版业务结账提示：结账过程中发生错误，请重新登录再试。违反了 PRIMARY KEY 约束 'PK_t_CC_stock_BatchNo'。不能在对象 't_CC_Stock_BatchNo' 中插入重复键。

【原因分析】

原因：

1.启用了保质期管理的商品，同一批号同一商品，其保质期、有效期、生产日期数据不一致，导致业务结账失败；

2.未启用保质期管理的商品，同一批号同一商品，部分保质期、有效期、生产日期存在数据，导致业务结账失败；

【解决方案】

该种场景：1.启用了保质期管理的商品，同一批号同一商品，其保质期、有效期、生产日期数据不一致，导致业务结账失败；

可参考以下脚本执行处理：
declare crkdjpc cursor for     
select a.FKFDate,a.FPeriodDate,a.FKFPeriod,a.FBatchNo,a.FItemID from
T_CC_StockBillEntry a inner join T_CC_StockBillEntry b on a.FItemID=b.FItemID  
where a.FItemID =b.FItemID  and a.FBatchNo=b.FBatchNo and
(a.FKFDate<>b.FKFDate or a.FPeriodDate<>b.FPeriodDate or a.FKFPeriod<>b.FKFPeriod) and a.FBatchNo<>''
group by a.FItemID,a.FBatchNo,a.FKFDate,a.FPeriodDate,a.FKFPeriod     

open crkdjpc                  

declare   @FKFDate datetime              
declare   @FPeriodDate datetime
declare   @FKFPeriod int
declare   @FBatchNo varchar(255)
declare   @FItemID int

fetch next from crkdjpc into @FKFDate,@FPeriodDate,@FKFPeriod,@FBatchNo,@FItemID
while @@FETCH_STATUS=0
begin

update a set a.FKFDate=@FKFDate,a.FPeriodDate=@FPeriodDate,a.FKFPeriod=@FKFPeriod from T_CC_StockBillEntry a inner join t_ICItem b on a.FItemID=b.FItemID
where a.FItemID =@FItemID and a.FBatchNo=@FBatchNo

update t_CC_Stock_BatchNo set FKFDate=@FKFDate,FPeriodDate=@FPeriodDate,FKFPeriod=@FKFPeriod
where FItemID =@FItemID and FBatchNo=@FBatchNo

fetch next from crkdjpc into @FKFDate,@FPeriodDate,@FKFPeriod,@FBatchNo,@FItemID
end
close crkdjpc
deallocate crkdjpc

go

该种场景：2.未启用保质期管理的商品，同一批号同一商品，部分保质期、有效期、生产日期存在数据，导致业务结账失败；

可参考以下脚本执行处理：

update b set FKFDate=NULL,FPeriodDate=NULL,FKFPeriod=0 from T_CC_StockBill a join T_CC_StockBillEntry b on a.FID=b.FID where FItemID in (
select FItemID from t_ICItem where FISKFPeriod=0) and (FKFDate is not null or FPeriodDate is not null or FKFPeriod<>0)
go
update t_CC_Stock_BatchNo set FKFDate='1900-01-01 00:00:00.000',FPeriodDate='1900-01-01 00:00:00.000',FKFPeriod=0
where FItemID in (
select FItemID from t_ICItem where FISKFPeriod=0) and (FKFDate<>'1900-01-01 00:00:00.000'
or FPeriodDate<>'1900-01-01 00:00:00.000' or FKFPeriod<>0)
go

【注意事项】

正式账套执行脚本前请先做好备份，建议待在测试账套中核实无误后再在正式账套中执行。