针对提单中的问题我们给出的答复如下：
（安全警示：为了您的账套数据安全，请在下面操作或数据更新前做好数据的备份）

现象分析：即时库存中出现相同物料有正负数量显示，没有进行合并。分析原因为部分物料库存余额表的保质期数据异常导致。

解决方法如下步骤执行：

–1、修改库存单据异常的保质期
update e set e.FKFDate=NULL from ICStockBillEntry e
inner join t_ICItem c on c.FItemID=e.FItemID
where c.FISKFPeriod=0 and e.FKFDate is not null

–2、修改库存余额异常的保质期（单条记录）
update i set i.FKFDate=” from ICInvBal i
inner join
(select a.FItemID,a.FStockID,a.FStockInDate,a.FYear,a.FPeriod,a.FBatchNo,a.FStockPlaceID,a.FKFPeriod,a.FAuxPropID,a.FBillInterID,a.FMTONo,a.FSupplyID
from ICInvBal a
inner join (select b.FItemID,b.FStockID,b.FStockInDate,b.FYear,b.FPeriod,b.FBatchNo,b.FStockPlaceID,b.FKFPeriod,b.FAuxPropID,b.FBillInterID,b.FMTONo,b.FSupplyID from ICInvBal b
inner join t_ICItem c on c.FItemID=b.FItemID
where c.FISKFPeriod=0 and b.FKFDate<>”
group by b.FItemID,b.FStockID,b.FStockInDate,b.FYear,b.FPeriod,b.FBatchNo,b.FStockPlaceID,b.FKFPeriod,b.FAuxPropID,b.FBillInterID,b.FMTONo,b.FSupplyID) d
on a.FItemID=d.FItemID and a.FStockID=d.FStockID and a.FStockInDate=d.FStockInDate and a.FYear=d.FYear and a.FPeriod=d.FPeriod and a.FBatchNo=d.FBatchNo and a.FStockPlaceID=d.FStockPlaceID and a.FKFPeriod=d.FKFPeriod and a.FAuxPropID=d.FAuxPropID and a.FBillInterID=d.FBillInterID and a.FMTONo=d.FMTONo and a.FSupplyID=d.FSupplyID
group by a.FItemID,a.FStockID,a.FStockInDate,a.FYear,a.FPeriod,a.FBatchNo,a.FStockPlaceID,a.FKFPeriod,a.FAuxPropID,a.FBillInterID,a.FMTONo,a.FSupplyID
having COUNT(*)=1) e
on i.FItemID=e.FItemID and i.FStockID=e.FStockID and i.FStockInDate=e.FStockInDate and i.FYear=e.FYear and i.FPeriod=e.FPeriod and i.FBatchNo=e.FBatchNo and i.FStockPlaceID=e.FStockPlaceID and i.FKFPeriod=e.FKFPeriod and i.FAuxPropID=e.FAuxPropID and i.FBillInterID=e.FBillInterID and i.FMTONo=e.FMTONo and i.FSupplyID=e.FSupplyID

–3、合并保质期异常的库存余额信息的金额和数量（多条记录）
update i set i.FBegQty=e.FBegQty,i.FReceive=e.FReceive,i.FSend=e.FSend,i.FYtdReceive=e.FYtdReceive,i.FYtdSend=e.FYtdSend,i.FEndQty=e.FEndQty,i.FBegBal=e.FBegBal,i.FDebit=e.FDebit,i.FCredit=e.FCredit,i.FYtdDebit=e.FYtdDebit,i.FYtdCredit=e.FYtdCredit,i.FEndBal=e.FEndBal,i.FBegDiff=e.FBegDiff,i.FReceiveDiff=e.FReceiveDiff,i.FSendDiff=e.FSendDiff,i.FEndDiff=e.FEndDiff,i.FYtdReceiveDiff=e.FYtdReceiveDiff,i.FYtdSendDiff=e.FYtdSendDiff,i.FSecBegQty=e.FSecBegQty,i.FSecReceive=e.FSecReceive,i.FSecSend=e.FSecSend,i.FSecYtdReceive=e.FSecYtdReceive,i.FSecYtdSend=e.FSecYtdSend,i.FSecEndQty=e.FSecEndQty
from ICInvBal i
inner join
(select a.FItemID,a.FStockID,a.FStockInDate,a.FYear,a.FPeriod,a.FBatchNo,a.FStockPlaceID,a.FKFPeriod,a.FAuxPropID,a.FBillInterID,a.FMTONo,a.FSupplyID,SUM(a.FBegQty) as FBegQty,SUM(a.FReceive) as FReceive,SUM(a.FSend) as FSend,SUM(a.FYtdReceive) as FYtdReceive,SUM(a.FYtdSend) as FYtdSend,SUM(a.FEndQty) as FEndQty,SUM(a.FBegBal) as FBegBal,SUM(a.FDebit) as FDebit,SUM(a.FCredit) as FCredit,SUM(a.FYtdDebit) as FYtdDebit,SUM(a.FYtdCredit) as FYtdCredit,SUM(a.FEndBal) as FEndBal,SUM(a.FBegDiff) as FBegDiff,SUM(a.FReceiveDiff) as FReceiveDiff,SUM(a.FSendDiff) as FSendDiff,SUM(a.FEndDiff) as FEndDiff,SUM(a.FYtdReceiveDiff) as FYtdReceiveDiff,SUM(a.FYtdSendDiff) as FYtdSendDiff,SUM(a.FSecBegQty) as FSecBegQty,SUM(a.FSecReceive) as FSecReceive,SUM(a.FSecSend) as FSecSend,SUM(a.FSecYtdReceive) as FSecYtdReceive,SUM(a.FSecYtdSend) as FSecYtdSend,SUM(a.FSecEndQty) as FSecEndQty
from ICInvBal a
inner join (select b.FItemID,b.FStockID,b.FStockInDate,b.FYear,b.FPeriod,b.FBatchNo,b.FStockPlaceID,b.FKFPeriod,b.FAuxPropID,b.FBillInterID,b.FMTONo,b.FSupplyID from ICInvBal b
inner join t_ICItem c on c.FItemID=b.FItemID
where c.FISKFPeriod=0 and b.FKFDate<>”
group by b.FItemID,b.FStockID,b.FStockInDate,b.FYear,b.FPeriod,b.FBatchNo,b.FStockPlaceID,b.FKFPeriod,b.FAuxPropID,b.FBillInterID,b.FMTONo,b.FSupplyID) d
on a.FItemID=d.FItemID and a.FStockID=d.FStockID and a.FStockInDate=d.FStockInDate and a.FYear=d.FYear and a.FPeriod=d.FPeriod and a.FBatchNo=d.FBatchNo and a.FStockPlaceID=d.FStockPlaceID and a.FKFPeriod=d.FKFPeriod and a.FAuxPropID=d.FAuxPropID and a.FBillInterID=d.FBillInterID and a.FMTONo=d.FMTONo and a.FSupplyID=d.FSupplyID
group by a.FItemID,a.FStockID,a.FStockInDate,a.FYear,a.FPeriod,a.FBatchNo,a.FStockPlaceID,a.FKFPeriod,a.FAuxPropID,a.FBillInterID,a.FMTONo,a.FSupplyID
having COUNT(*)>1) e
on i.FItemID=e.FItemID and i.FStockID=e.FStockID and i.FStockInDate=e.FStockInDate and i.FYear=e.FYear and i.FPeriod=e.FPeriod and i.FBatchNo=e.FBatchNo and i.FStockPlaceID=e.FStockPlaceID and i.FKFPeriod=e.FKFPeriod and i.FAuxPropID=e.FAuxPropID and i.FBillInterID=e.FBillInterID and i.FMTONo=e.FMTONo and i.FSupplyID=e.FSupplyID
where i.FKFDate=”

–4、删除保质期异常的库存余额数据（多条记录）
delete i from ICInvBal i
inner join
(select a.FItemID,a.FStockID,a.FStockInDate,a.FYear,a.FPeriod,a.FBatchNo,a.FStockPlaceID,a.FKFPeriod,a.FAuxPropID,a.FBillInterID,a.FMTONo,a.FSupplyID
from ICInvBal a
inner join (select b.FItemID,b.FStockID,b.FStockInDate,b.FYear,b.FPeriod,b.FBatchNo,b.FStockPlaceID,b.FKFPeriod,b.FAuxPropID,b.FBillInterID,b.FMTONo,b.FSupplyID from ICInvBal b
inner join t_ICItem c on c.FItemID=b.FItemID
where c.FISKFPeriod=0 and b.FKFDate<>”
group by b.FItemID,b.FStockID,b.FStockInDate,b.FYear,b.FPeriod,b.FBatchNo,b.FStockPlaceID,b.FKFPeriod,b.FAuxPropID,b.FBillInterID,b.FMTONo,b.FSupplyID) d
on a.FItemID=d.FItemID and a.FStockID=d.FStockID and a.FStockInDate=d.FStockInDate and a.FYear=d.FYear and a.FPeriod=d.FPeriod and a.FBatchNo=d.FBatchNo and a.FStockPlaceID=d.FStockPlaceID and a.FKFPeriod=d.FKFPeriod and a.FAuxPropID=d.FAuxPropID and a.FBillInterID=d.FBillInterID and a.FMTONo=d.FMTONo and a.FSupplyID=d.FSupplyID
group by a.FItemID,a.FStockID,a.FStockInDate,a.FYear,a.FPeriod,a.FBatchNo,a.FStockPlaceID,a.FKFPeriod,a.FAuxPropID,a.FBillInterID,a.FMTONo,a.FSupplyID
having COUNT(*)>1) e
on i.FItemID=e.FItemID and i.FStockID=e.FStockID and i.FStockInDate=e.FStockInDate and i.FYear=e.FYear and i.FPeriod=e.FPeriod and i.FBatchNo=e.FBatchNo and i.FStockPlaceID=e.FStockPlaceID and i.FKFPeriod=e.FKFPeriod and i.FAuxPropID=e.FAuxPropID and i.FBillInterID=e.FBillInterID and i.FMTONo=e.FMTONo and i.FSupplyID=e.FSupplyID
where i.FKFDate<>”

–5、校对即时库存
exec CheckInventory