陆陆续续处理过多个专业版客户在做存货核算的过程中提示“溢出”，一般的处理过程都是移开这个小的提示框去看具体哪个物料结转存在问题，然后去查对应物料的收发明细表。
我处理的客户提示“溢出”的原因主要有两个：
1、单据数据异常，出库类单据数量为0；
2、单据状态异常，单据上的数量不影响即时库存的变化。
第一个情况的处理办法就是找到对应单据以后删除数量为0的分录；第二种情况的处理办法是反审核对应单据删除有问题的分录重新做单据。
附上处理其中一个客户在数据库跟踪出来的语句供大家参考：
SELECT ICStockBill.FInterID ,
ICStockBill.FBillNo ,
ICStockBill.FDate ,
CASE WHEN ICStockBill.FROB = -1 THEN '红字'
ELSE ''
END + ICTransactionType.FName AS FTranName ,
ICStockBill.FTranType ,
ICStockBill.FRefType ,
ICStockBill.FBillTypeID ,
ICStockBill.FROB ,
ISNULL(ICStockBillEntry.FSCStockID, 0) AS FSCStockID ,
ISNULL(ICStockBillEntry.FDCStockID, 0) AS FDCStockID ,
ISNULL(t_Voucher.FVoucherID, 0) AS FVchInterID ,
ISNULL(ICStockBillEntry.FSourceInterId, 0) AS FSourceInterID ,
ICStockBillEntry.FSourceEntryID ,
ICStockBill.FStatus ,
ICStockBillEntry.FEntryID ,
ICStockBillEntry.FItemID ,
ISNULL(ICStockBillEntry.FReProduceType, 0) AS FReproduceType ,
( ISNULL(t_MeasureUnit.FCoefficient, 1) + ISNULL(t_MeasureUnit.FScale,
0) ) AS FAuxData ,
ICStockBillEntry.FPlanAmount ,
ICStockBillEntry.FBatchNo ,
ICStockBillEntry.FQty ,
ICStockBillEntry.FPrice ,
ICStockBillEntry.FAmount ,
ICStockBillEntry.FPriceRef ,
ICStockBillEntry.FAmtRef ,
ISNULL(CASE WHEN ( ICStockBill.FTranType = 21
AND ICStockBillEntry.FSourceTranType = 21
)
OR ( ICStockBill.FTranType = 24
AND ICStockBillEntry.FSourceTranType = 24
)
OR ( ICStockBill.FTranType = 92
AND ICStockBillEntry.FSourceTranType = 92
)
OR ( ICStockBill.FTranType = 43
AND ICStockBillEntry.FSourceTranType = 43
)
OR ( ICStockBill.FTranType = 29
AND ICStockBillEntry.FSourceTranType = 29
)
THEN ( SELECT u1.FPrice
FROM ICStockBillEntry u1
WHERE u1.FEntryID = ICStockBillEntry.FSourceEntryID
AND u1.FInterID = ICStockBillEntry.FSourceInterId
)
ELSE 0
END, 0) AS FPrePrice ,
CASE WHEN ICStockBill.FTranType IN ( 1, 2, 10, 40, 41, 100, 101, 93 )
THEN ISNULL(t_Stock.FGroupID, 0)
ELSE 0
END AS FInStockGroupID ,
CASE WHEN ICStockBill.FTranType IN ( 24, 92, 41 )
THEN ISNULL(t_Stock1.FGroupID, 0)
WHEN ICStockBill.FTranType IN ( 21, 29, 43 )
THEN ISNULL(t_Stock.FGroupID, 0)
ELSE 0
END AS FOutStockGroupID ,
CASE WHEN ICStockBill.FTranType IN ( 41 )
THEN ISNULL(t_Stock.FTypeID, 0)
ELSE 0
END AS FInStockTypeID ,
CASE WHEN ICStockBill.FTranType IN ( 41 )
THEN ISNULL(t_Stock1.FTypeID, 0)
ELSE 0
END AS FOutStockTypeID ,
t_ICItem.FQtyDecimal
FROM ICStockBill
INNER JOIN ICStockBillEntry ON ICStockBill.FInterID = ICStockBillEntry.FInterID
INNER JOIN t_ICItem ON ICStockBillEntry.FItemID = t_ICItem.FItemID
INNER JOIN ICTransactionType ON ICTransactionType.FID = ICStockBill.FTranType
LEFT JOIN t_MeasureUnit ON t_MeasureUnit.FItemID = ICStockBillEntry.FUnitID
LEFT OUTER JOIN t_Stock ON ICStockBillEntry.FDCStockID = t_Stock.FItemID
LEFT OUTER JOIN t_Stock t_Stock1 ON ICStockBillEntry.FSCStockID = t_Stock1.FItemID
LEFT JOIN t_Voucher ON ICStockBill.FVchInterID = t_Voucher.FVoucherID
WHERE ICStockBill.FCancellation = 0
AND ICStockBill.FStatus > 0
AND ( ( ICStockBill.FTranType = 41
AND ( ISNULL(t_Stock.FTypeID, 999) <> 502
OR ISNULL(t_Stock1.FTypeID, 999) <> 502
)
)
OR ( ICStockBill.FTranType <> 41
AND ISNULL(t_Stock.FTypeID, 999) <> 502
AND ISNULL(t_Stock1.FTypeID, 999) <> 502
)
)
AND ICStockBill.FDate >= '2020-12-01'
AND ICStockBill.FDate < '2021-01-01'
AND t_ICItem.FNumber >= '09.018'
AND t_ICItem.FNumber <= '09.018'
AND 1 = 1
AND t_ICItem.FNumber >= '09.018'
AND t_ICItem.FNumber <= '09.018'
ORDER BY ICStockBill.FDate ,
CASE WHEN ICStockBill.FTranType IN ( 1, 2, 10, 40, 93 ) THEN 0
ELSE 1
END ,
ICStockBill.FInterID ,
ICStockBillEntry.FEntryID;
赞 21
打赏推荐阅读