----------------------------------采购收货数量不能超出采购订单数量------------------------------------------------------
SELECT TOP 1 @ERROR = 1,
@ERROR_MESSAGE = N‘物料:‘ + t1.ItemCode + N‘ 收货数量已超出订单数量！
订单未清数量为：‘ + CAST (t3.OpenCreQty AS NVARCHAR)
+ N‘, 本次总计收货数量为：‘
+ CAST (t1.Quantity AS NVARCHAR)
FROM
(SELECT ItemCode, BaseEntry, BaseLine, SUM (Quantity) AS Quantity
FROM PDN1 AS t0
WHERE (DocEntry = @list_of_cols_val_tab_del) GROUP BY ItemCode, BaseEntry, BaseLine)
AS t1
LEFT OUTER JOIN
(SELECT t2.LineNum, t2.OpenCreQty, t2.DocEntry
FROM
(SELECT ROW_NUMBER () OVER (PARTITION BY T.DOCENTRY ORDER BY t.LogInstanc DESC) indx,
t.LogInstanc, t.LineNum, t.OpenCreQty,t.DocEntry
FROM ADO1 t WHERE ObjType = 22 ) t2
WHERE t2.indx = 1) t3
ON t1.BaseLine = t3.LineNum and t1.BaseEntry =t3.DocEntry
WHERE t3.OpenCreQty < t1.Quantity
--------------------------------------------------------------------------------------------------------------------------
SELECT TOP 1 @error = 1,
@message = N‘子物料：‘ + CAST (t1.ItemCode AS VARCHAR) + N‘ 未发过货，订单不能关闭！如果确实不需要，请从工单中删除该物料！‘
FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T0.DocEntry = #1 AND T1.IssuedQty = 0 AND T0.RjctQty +T0.CmpltQty >0
---------------------------------------------------------------------------------------------
采购订单数量不能超出采购申请
SELECT TOP 1 @error = 1,
@message = N‘第‘ + CAST (t.VisOrder + 1 AS VARCHAR) + N‘行‘
+ CASE WHEN t.BaseType <> 1470000113 THEN N‘没有基于采购申请单！‘
WHEN t.Quantity < t.PoQty THEN N‘累计下达订单已超出了申请单数量！‘ END
FROM
(SELECT T0.VisOrder + 1 VisOrder, t0.BaseType, T1.Quantity,
SUM (T2.Quantity) OVER (PARTITION BY T2.BASEENTRY, T2.BASELINE) PoQty
FROM POR1 T0
LEFT OUTER JOIN PRQ1 T1 ON T0.BaseType = T1.ObjType AND T0.BaseEntry = T1.DocEntry AND T0.BaseLine = T1.LineNum
LEFT OUTER JOIN (POR1 T2 INNER JOIN OPOR T3 ON T2.DocEntry = T3.DocEntry AND T3.CANCELED = ‘N‘)
ON T2.BaseType = T1.ObjType AND T2.BaseEntry = T1.DocEntry AND T2.BaseLine = T1.LineNum
WHERE T0.DocEntry = #1) T
WHERE (t.BaseType <> 1470000113 OR t.Quantity < t.PoQty)
---------------------------------------------------------------------------------------------
本文出自 “johnny” 博客，请务必保留此出处http://johnny.blog.51cto.com/161381/1949201
原文：http://johnny.blog.51cto.com/161381/1949201