Stored procedure in purchase order for duplicate item for a vendor

[Former Member]

Hi all

I have written a stored procedure for duplicate item for vendor the output i need from this SP is whenever for a vendor we will raise purchase order the system should check that their shouldn't be any po in the system for the same item by the selected vendor. Its working fine when only one item is selected but it is behaving wrongly when more than one item is selected.

If (@object_type='22' and @transaction_type='A')

BEGIN

Declare @Vend as varchar(200)

Declare @ItemCode as varchar(200)

Select @Vend =CardCode From OPOR Where DocEntry = @list_of_cols_val_tab_del

Select @ItemCode=ItemCode From POR1 Where DocEntry=@list_of_cols_val_tab_del

IF 1 !=(Select Count(T0.DocEntry) From OPOR T0 Inner Join POR1 T1 On T0.DocEntry=T1.DocEntry Where T0.CardCode=@Vend and T1.ItemCode=@ItemCode)

Begin

select @error =1

select @error_message = 'Item Code Already Exists For This Vendor'

end

end

Thanks

Rashid.

[Former Member · Accepted solution]

Hi,

You can try this query !

If (@object_type='22' and @transaction_type='A')

BEGIN

Declare @Vend as varchar(200)

Select @Vend =CardCode From OPOR Where DocEntry = @list_of_cols_val_tab_del

If exists ( Select T0.DocEntry ,count(T1.ItemCode)

From OPOR T0 Inner Join POR1 T1 On T0.DocEntry=T1.DocEntry

and T1.ItemCode in (select T4.ItemCode From POR1 T4 Where T4.DocEntry= @list_of_cols_val_tab_del)

Where T0.CardCode= @Vend and t0.DocEntry <> @list_of_cols_val_tab_del

group by T0.DocEntry

having Count(T1.ItemCode) = (select count(T3.ItemCode) From POR1 T3 Where T3.DocEntry= @list_of_cols_val_tab_del' ) )

begin

select '1'

select @error_message = 'Item Code Already Exists For This Vendor'

end

END

Regards,

H2

[kvbalakumar]

Hi Rashid,

Try this,

if @object_type = N'22' and @transaction_type in (N'A', N'U')
begin
 declare @line1 int
 declare @lin1 int
 declare @out1 int
 Set @out1 = 0
 SET @lin1 = 0
Declare @Vend as varchar(200)
Declare @ItemCode as varchar(200)
Select @Vend = CardCode From OPOR Where DocEntry = @list_of_cols_val_tab_del
Select @line1 = Max (LineNum)FROM POR1 WHERE POR1.DocEntry = @list_of_cols_val_tab_del
 While @lin1 < @line1
 Begin
	Select @ItemCode=ItemCode From POR1 Where DocEntry=@list_of_cols_val_tab_del and LineNum = @lin1
	 if (SELECT COUNT(T0.DocEntry) FROM POR1 T0 inner join OPOR T1 on T0.DocEntry = T1.DocEntry
	 WHERE T0.ItemCode = @ItemCode and T1.CardCode = @Vend)> 1
	 Begin
		 Set @Out1 = 1
		 Break;
	 END
		 Else
		 Begin
		 Set @lin1 = @lin1+1
		 Continue
	 END
 END
 Set @lin1 = @lin1 + 1
 if @out1 = 1
 begin
	 Set @error = 1
	 Set @error_message = 'Item Code in line ' + CONVERT(nvarchar(4), @lin1) + N'already Exists For This Vendor!'
 End
END

Regards,

Bala

[Former Member]

Hi Rashid,

check this link,

Sorry but this Sp will block addition of PO with duplicate line items.(Not as required by you)

thanks,

Joseph

Edited by: Joseph Antony on Jan 4, 2011 4:59 PM

[former_member196081]

Hi,

Try Below Code..

.. IF @transaction_type IN ('A','U') AND @object_type = '22' BEGIN IF EXISTS (SELECT T0.CODE FROM [dbo].[por1] T0 WHERE T0.docentry = @list_of_cols_val_tab_del GROUP BY T0.CODE HAVING COUNT(T0.CODE) > 1) BEGIN SELECT @Error = 1, @error_message = 'Duplicate Item in this document' END END

Regards

Deepak Tyagi