GRPO Duplicate Serial Number Should Not allow for same items or items in STOCK, etc?

164 Views

Dear All, I have this SP for not allowing duplicate serial number to be created as follows FOR GRPO -------validation for duplication of serial number------

IF @transaction_type IN ('A','U') AND (@object_type = '20' ) -- For GRPO BEGIN declare @seril as varchar(36) set @seril=(SELECT top 1 T2.IntrSerial From OPDN T0 JOIN PDN1 T1 ON T1.DocEntry=T0.DocENtry JOIN OSRI T2 ON T2.BaseLinNum =T1.LineNum AND T2.BaseEntry=T0.docEntry AND T2.IntrSerial IN (SELECT IntrSerial FROM OSRI WHERE BaseType = '20' AND [Status]=0 AND BaseEntry != T0.DocEntry) WHERE T0.DocEntry=@list_of_cols_val_tab_del) set @ItemCode1=(SELECT top 1 T1.ItemCode From OPDN T0 JOIN PDN1 T1 ON T1.DocEntry=T0.DocENtry JOIN OSRI T2 ON T2.BaseLinNum =T1.LineNum AND T2.BaseEntry=T0.docEntry AND T2.IntrSerial IN (SELECT IntrSerial FROM OSRI WHERE BaseType = '20' AND [Status]=0 AND BaseEntry != T0.DocEntry) WHERE T0.DocEntry=@list_of_cols_val_tab_del) IF Exists (SELECT T0.DocEntry From OPDN T0 JOIN PDN1 T1 ON T1.DocEntry=T0.DocENtry JOIN OSRI T2 ON T2.BaseLinNum =T1.LineNum AND T2.BaseEntry=T0.docEntry AND T2.IntrSerial IN (SELECT IntrSerial FROM OSRI WHERE BaseType = '20' AND [Status]=0 AND BaseEntry != T0.DocEntry) WHERE T0.DocEntry=@list_of_cols_val_tab_del) BEGIN set @error =122 set @error_message = N'Serial Number is already exists in ItemCode : '+@ItemCode1 +'Serial : '+@seril END END However when we do a Goods Receipt PO , the duplicate serial number is taken and the error doesn't show even if there is a duplicate serial number added. What changes needs to done for the SP, so that during the stage of GRPO only system should disallows taking the duplicate serial number. Thanksℜgards Smiley