Vendor Ref No.

[Former Member]

Dear All,

in the AP Invoice there is a field of Vendor Ref. No. I can understand the use of this but when i put the duplicate reference no. for the same vendor there is no restiction from the system side and that can ultimately lead to the duplicate posting

so can anyone suggest how to go about and how to save the duplicate posting

[Former Member · Accepted solution]

this is still pending can anyone answer plz

[Former Member · Accepted solution]

You can block the duplication with stored procedure transactionotification. If the document is Added (A), then check, if exist same number, if yes, throw error.

For example for vendor deliveries it will be as:

if @object_type = '20' and (@transaction_type= 'A' or @transaction_type= 'U')

begin

declare @venno as varchar(200)

select @venno = numatcard from opdn where docentry =@list_of_cols_val_tab_del

if 1 != (select count(docentry) from opdn with(nolock) where numatcard = @venno)

begin

select @error =1

select @error_message = 'Duplicate vendor no.'

end

end

[former_member583013 · Accepted solution]

Sana,

I would suggest that you first upgrade your 2005A version to the latest patch.

The system has an Auto check (out of the box) and when you enter a duplicate Vendor Invoice No in the Ref No field you will get a message like this

Vendor Ref. No. already exists in a document of this type. Continue?

[Former Member · Accepted solution]

Hi, Suda,

The system message is only a warning type, but it will not block the transaction if user ignore the message. we are also looking for any block setup in the system now; (SAP B1 2007)

Look forward to get good news if anyone know that !!

Best regards,

Michelle

[former_member583013 · Accepted solution]

Michelle,

Petr has already posted the solution in an earlier reply

Please take a look

[Former Member · Accepted solution]

Hi, Petr,

Actually I hope to get the solution from SAP administration.

If the store procedure is the only solution ...

For user side/role, we don't quite know the program (sp) bundled into SAP system. Currently I only write SP for crystal report usage. Don't know if that's easy and convenient for you to detail how to put the store procedure into AP Invoice function?

Very appreciate.

Michelle

[Former Member · Accepted solution]

If you can write SP for crystal report usage, this will be more than enough for you to modify the ready made SP by SAP.

In "add code here" of that SP, put Petr's code there. That is all for you to do.

Thanks,

Gordon

[former_member583013 · Accepted solution]

Check this link which explains SBO_SP_TransactionNotification procedure

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/e991e2b9-0901-0010-0395-ef5...

[Former Member · Accepted solution]

Its easy, open sql managemet studio and connect to sql server. Then on left side expand the db you want to apply the changes, then expand programability and stored procedures. There find sbo_transaction_notification use right click and modify. In section "Here put your code...." insert my code. It will work.

If you are using sql 2000 (previous was for 2005), open enterprise manager and find the stored procedure. Then click on it and select Edit.

[Former Member · Accepted solution]

Hi, Gordon and Peru,

Glad to hear my company can go the solution. But I can't find the "Add Code here"! that's in SAP system or SQL Server management studio? can you elaborate the add on process?

Also, before copy the code of Peru, I think I have to fix the code as below, to make it to be available for 'Vendor Ref No ' entried no matter in PO Receipt or AP Invoice either one will get checked if duplicated, Is there any missing to reach my purpose?

if (@object_type = '20' or @object_type ='18') and (@transaction_type= 'A' or @transaction_type= 'U')

begin

declare @venno as varchar(200)

select @venno = numatcard from opdn where docentry =@list_of_cols_val_tab_del

if 1 != (select count(docentry) from opdn with(nolock) where numatcard = @venno)

begin

select @error =1

select @error_message = 'Duplicate vendor no.'

end

end

Very appreciate for you both help on this.

Michelle

[former_member583013 · Accepted solution]

Michelle

Add Code here is just a place holder, if it is not there don't worry about it..

Your code seems correct..

For NumatCard field you do not really need 200 chars. The field itself is only 100

Suda

[Former Member · Accepted solution]

From SQL Server management studio, find all SP associated with B1 database, you can pick up that SP easily.

Thanks,

Gordon

[Former Member · Accepted solution]

Sorry I missed the last 2 mails before I asked question "can't find the SP" this morning.

I found the SP, and tested it. But any data entried in Vendor Ref No will be blocked now.

I think that's because the select count from OPCH/OPDN condition... I am tuning the code now.

Very Thanks to

Peru, Gordon, and Suda. And the question initiator...

Best regards,

Michelle

[Former Member · Accepted solution]

Hi, guys,

Here's the code I revised and it works. For anyone who needs !

/** block duplicate Vendor Invoice number **/

if (@object_type = '20' or @object_type = '18')

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

begin

declare @venno as varchar(100)

if @object_type = '20'

begin

select @venno = NumAtCard

from opdn

where docentry =@list_of_cols_val_tab_del

if 1 != (select count(docentry) from opdn with(nolock) where NumAtCard = @venno)

begin

select @error = 1

select @error_message = 'Duplicate vendor ref no in PO Receipts. ! '

end

end

else

if @object_type = '18'

begin

select @venno = NumAtCard

from opch

where docentry =@list_of_cols_val_tab_del

if 1 != (select count(docentry) from opch with(nolock) where NumAtCard = @venno)

begin

select @error = 2

select @error_message = 'Duplicate vendor ref no in AP Invoices. ! '

end

end

end

Best regards,

Michelle

[Former Member · Accepted solution]

Hi Guys,

There is a slight problem in the code given above.

In my system i am having the GRPO with vendor ref no. 100. and when i am adding the Grpo with vendor Ref. no. 0100 it is adding with out blocking the documents in this case also it should block. can any one please give me a solution for this senario.

Regards,

Srinivas

[Former Member · Accepted solution]

Hi Srinivas,

Seeing as the NumAtCard field in SAP is NVARCHAR then 0100 is not the same as 100 and so it's correct not to block it. However, you are saying you need 0100 to be blocked - to do this you would need to CAST the value as INT and then compare. But, is the Vendor Ref. No. always going to be in numeric format? If not then you will get errors casting as INT and will need further checks to see if the value is numeric before casting.

Regards,

Andrew.

[Former Member]

U can restrict from Administration --> System initialization --> Documents settings, per document tab , choose document 'AP Invoice' choose block release when 'When duplicated Vendor Reference No. occurs'

Regards,

Dhana.

[Former Member]

Sorry, i do not see such option at there

in per document>AP Invoice >

it is blank and i cannot see any option like that

[Former Member]

Which version of SBO r u working on?

Dhana.

[Former Member]

it is SBO 2005 A PL 10

[Former Member]

Sorry Sana! the option is available in 2005B. Hope anyone can help u!

Dhana.

[Former Member]

This option (to block release of duplicate vendor reference" does not seem to exit in version 2007. Has it been moved or is there another way to accomplish this?

Sal

[Former Member]

?