Skip to Content

prevent duplicate customer reference

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Feb 07, 2017 at 02:15 PM

    Hi,

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

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 06:58 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 07, 2017 at 11:39 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 07, 2017 at 02:34 PM

    Hello,

    Agreed with nagarajan K

    Thanks

    Engr. Taseeb Saeed

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 07:05 AM

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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 08:20 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 02:17 PM

    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

    Add comment
    10|10000 characters needed characters exceeded