Skip to Content
0

prevent duplicate customer reference

Feb 07, 2017 at 09:46 AM

130

avatar image

Hi all

I found this prevent Duplicate Vendor Reference for same Vendor stored proc from Balakumar useful.

Please advise how to modify it to apply to only one specific Customer?

if @object_type = '18'

and (@transaction_type = 'A' or @transaction_type = 'U')

Begin

declare @venno as varchar (100)

declare @vennam as varchar (100)

declare @apdate as datetime

if (@object_type = '18')

Begin

select @venno = NumAtCard, @vennam = CardCode, @apdate = DocDate from OPCH T0 where docentry = @list_of_cols_Val_tab_del

Begin

if (@venno is not null)

Begin

if 1!= (select count (docentry) from OPCH with (nolock)where (NumatCard = @venno) and (@apdate = DocDate) and (cardcode = @vennam))

Begin

select @error = 2

select @error_message = 'Duplicate Vendor Ref Number!'

End

End

End

End

End

Kedalene

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

7 Answers

Nagarajan K Feb 07, 2017 at 02:15 PM
1

Hi,

Use standard system function to block duplicated reference for AP Invoice under Document settings-->Per Document-->AP Invoice.

Regards,

Nagarajan

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Dear Nagarajan,

I went trough the forum, and found out this answer.

I just want to double conform with you.

This setting will apply after user key in all the items and data inside the document, then when user want to click add,

the system will prompt notification right?

0

Yes, its correct.

0
Kedalene Chong Feb 08, 2017 at 06:58 AM
1

Hi Nagarajan, Taseeb

The SAP Document Settings applies to all Vendors. Besides, it prevent posting if the duplicated Vendor Reference is used by different Vendors when it should apply to each Vendor only.

Share
10 |10000 characters needed characters left characters exceeded
Gaurav Bali Feb 07, 2017 at 11:39 AM
0

Hi Kedalene Chong

You can change condition as shown below:

if @object_type = '18'
and (@transaction_type = 'A' or @transaction_type = 'U')
Begin
declare @venno as varchar (100)
declare @vennam as varchar (100)
declare @apdate as datetime
if (@object_type = '18')
Begin
select @venno = NumAtCard, @vennam = CardCode, @apdate = DocDate from OPCH T0 where docentry = @list_of_cols_Val_tab_del
Begin
if (@venno in ('CARDCODE')) --change cardcode to required vender code
Begin
if 1!= (select count (docentry) from OPCH with (nolock)where (NumatCard = @venno) and (@apdate = DocDate) and (cardcode = @vennam))
Begin
select @error = 2
select @error_message = 'Duplicate Vendor Ref Number!'
End
End
End
End
End

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Gaurav

The control doesn't work after changing

if (@venno in ('CARDCODE')) --change cardcode to required vender code

if (@venno in ('V10000')) --change cardcode to required vender code?

0

Hi Kedalene

A small correction in code

if ((@vennam in ('CARDCODE')) and (@venno is not null))

This should work

0
Taseeb Saeed Feb 07, 2017 at 02:34 PM
0

Hello,

Agreed with nagarajan K

Thanks

Engr. Taseeb Saeed

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 08, 2017 at 07:05 AM
0

I don't think all vendors will follow same reference number!

Share
10 |10000 characters needed characters left characters exceeded
Agustin Marcos Cividanes Feb 08, 2017 at 08:20 AM
0

Hi

try this code:

SELECT @Period = year(TaxDate),@codcli = CardCode,@Numref = NumAtCard FROM OPCH with (nolock) WHERE DocEntry = @list_of_cols_Val_tab_del


SELECT @amount = (SELECT isnull(count(*),0) FROM OPCH WHERE NumAtCard = @Numref AND YEAR(TaxDate)= @PeriodoFcontable
AND CardCode = @codcli)

IF @amount > 1
BEGIN
Set @error = 1
Set @message = 'Document number is duplicated for this period'
END

Kind regards

Agustin

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 08, 2017 at 02:17 PM
0

Hi,

Try this,

IF @transaction_type in ('U') AND (@OBJECT_TYPE='18') BEGIN If exists ( SELECT count(*) FROM OPCH T0 WHERE T0.[NumAtCard] IN (select T0.[NumAtCard] from OPCH T0 where T0.Docentry = @list_of_cols_val_tab_del) and T0.cardcode = 'V70000' having count(*) >1 ) Begin SET @error = 10 SET @error_message = 'Duplicated vendor reference' End END

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded