质检校验方案
大约 1 分钟
代码示例
DECLARE @CNT INT;
Declare @DocNum NVARCHAR(50);
DECLARE @ItemCode NVARCHAR(50);
if @object_type = '20' --采购收货单时做数据校验,必须要做送检才可以收货
begin
SELECT @CNT = COUNT(T2."DocEntry"),@ItemCode = T2.ItemCode FROM OPDN T0
JOIN PDN1 T1 ON T1.DocEntry = T0.DocEntry
JOIN POR1 R1 ON R1.DocEntry = T1.BaseEntry AND R1.LineNum = T1.BaseLine AND T1.BaseType = 22
JOIN PDN1 T2 ON R1.DocEntry = T2.BaseEntry AND R1.LineNum = T2.BaseLine AND T2.BaseType = 22
--获取采购订单中被送检且是合格的采购订单行和数量
LEFT JOIN (SELECT S1.U_BaseEntry,S1.U_ItemCode,SUM(S1.U_Qty) QTY
FROM "@QAS1" S1
JOIN "@OQAS" S0 ON S0.DocEntry = S1.DocEntry
Left Join "@OQAD" S2 ON S1.DocEntry = S2.U_BaseEntry And S1.LineId=S2.U_BaseLine
WHERE S1.U_BaseType = 22 And S2.DocEntry is not null And S2.DocEntry not in
(Select DocEntry From "@QAD1" Where U_Status='NG' And isnull(U_IsPass,'N')='N') GROUP BY S1.U_BaseEntry,S1.U_ItemCode )
TT ON TT.U_BaseEntry = T2.BaseEntry AND TT.U_ItemCode = T2.ItemCode
WHERE T0.DocEntry = @list_of_cols_val_tab_del AND T0.CANCELED = 'N' GROUP BY T2.ItemCode,T2.BaseEntry,TT.QTY HAVING SUM(T2.Quantity)-ISNULL(TT.QTY,0) > 0
IF @CNT > 0
BEGIN
select @error =0,@error_message = N'采购收货数量超过送检数量,物料号为'+ @ItemCode;
END
ElSE
BEGIN
Select @DocNum=BaseDocNum From PDN1 Where DocEntry = @list_of_cols_val_tab_del
Update "@OQAD" Set Status='C' Where U_DocNum = ISNULL(@DocNum,'') And U_ItemCode in (Select ItemCode From PDN1 Where DocEntry = @list_of_cols_val_tab_del)
END
end
if @object_type = '59' --生产入库
begin
SELECT @CNT = COUNT(T1."DocEntry"),@ItemCode = T0.ItemCode FROM
OWOR T0 JOIN IGN1 T1 ON T1.BaseEntry = T0.DocEntry And T1.BaseType=202
--获取采购订单中被送检且是合格的生产入库单行和数量
LEFT JOIN
(SELECT S1.U_BaseEntry,SUM(S1.U_Qty) QTY
FROM "@QAS1" S1
JOIN "@OQAS" S0 ON S0.DocEntry = S1.DocEntry
Left Join "@OQAD" S2 ON S1.DocEntry = S2.U_BaseEntry And S1.LineId=S2.U_BaseLine
WHERE S1.U_BaseType = 202 And S2.DocEntry is not null And S2.DocEntry not in
(Select DocEntry From "@QAD1" Where U_Status='NG' And isnull(U_IsPass,'N')='N') GROUP BY S1.U_BaseEntry )
TT ON TT.U_BaseEntry = T0.DocEntry
WHERE T1.DocEntry = @list_of_cols_val_tab_del GROUP BY T0.ItemCode,T1."DocEntry",TT.QTY,T0.PlannedQty
HAVING SUM(T0.CmpltQty)-ISNULL(TT.QTY,0) > 0
IF @CNT > 0
BEGIN
select @error =0,@error_message = N'生产收货数量超过送检数量,物料号为'+ @ItemCode;
END
ElSE
BEGIN
Select @DocNum=BaseDocNum From IGN1 Where DocEntry = @list_of_cols_val_tab_del
Update "@OQAD" Set Status='C' Where U_DocNum = ISNULL(@DocNum,'') And U_ItemCode in (Select ItemCode From IGN1 Where DocEntry = @list_of_cols_val_tab_del)
END
end