Skip to Content
0
Former Member
Mar 16, 2010 at 11:03 AM

Stored Procedure for checking Invoice Vendor ref No

128 Views

Hi All

I need a sp for checking a vendor ref no on an invoice if it already exist in the system or the user forgot to include one when trying to add the document.

I get this error on the one that I created

16/03/2010 12:21:54: [Microsoft][SQL Native Client][SQL Server]Conversion failed when converting the nvarchar value '24 0' to data type int. (CINF)

Here is my SP :-

DECLARE @Invoice AS VARCHAR(15)

DECLARE @Card AS VarChar (20)

SELECT @invoice = NumatCard, @card = CardCode FROM dbo.OPCH

WHERE DocEntry = @list_of_cols_val_tab_del

if @object_type = '18' and @transaction_type in (N'A', N'U') -- AP Invoice is null

begin

If exists (SELECT T0.cardcode, T0.NumAtCard FROM OPCH T0 where T0.NumatCard is null and T0.cardcode=@list_of_cols_val_tab_del)

begin

SET @error = 10

SET @error_message = N'Supplier invoice number not entered.'

END

END

SELECT @INVOICE = NumatCard FROM dbo.OPCH

WHERE DocEntry = @list_of_cols_val_tab_del

if @object_type = '18' and @transaction_type in (N'A', N'U') -- AP Invoice to check if the invoice no exist

begin

If exists (SELECT T0.cardcode, T0.NumatCard FROM OPCH T0 where T0.NumatCard = @invoice and T0.cardcode=@list_of_cols_val_tab_del)

begin

SET @error = 10

SET @error_message = N'This invoice number already exist for this supplier.'

END

END

Regards

Bongani Dlamini