质检校验方案

代码示例

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