Automatic BP no. creation in SAP Business One

[Former Member]

Hi

Does it possible to get automatic BP no. creation (separate for customer and vender). if yes then please let me know what query i need to fetch.

Any pointer will be appreciated.

Regards

Mohit

[Former Member · Accepted solution]

Hi Mohit,

Hope this will help u out .

select max(cardtype)

+ cast(substring( max(cardcode), 2, len(max(cardcode)))

+ 1 as nvarchar(10)) from ocrd

where cardtype = $[OCRD.CARDTYPE]

In detail:

To generate a business partner code automatically, please perform the following steps:

Please note! The cards' codes must fulfill two conditions:

a. It can only be a numeric code.

b. All cards must have the same number of characters in the code.

1. Createthefollowing query in "Query Interface" under the component "Reporting":

SELECT MAX (CardCode)+1 FROM OCRD TO FOR BROWSE

2. Save the query and give it a relevant name.

3. Now place the cursor into the "Code" field in the "Business Partner Master Data" (BP) window.

4. Select "Formatted Search". From the taskbar select "Tools" and "Search Function" and "Define Formatted Search".

5. Check the radio button for "Search by Saved Query" and double click on the grey field.

6. Choose the relevant query and confirm with "Open", "Update" and "OK".

Rgds,

Premraj

Edited by: PremRaj kaushik on Feb 25, 2009 4:59 AM

[Former Member · Accepted solution]

thanaks Premraj...issue resolved.

Regards

Mohit

[Former Member · Accepted solution]

What if the user is not creating the BP through SAP, but through a b1if scenario?

[Former Member]

Sorry Mohit,

Forgot to mention that!! you can assign a formatted search on the BP code !! Thanks Marini

Best Regards,

Joseph

[Former Member]

Hi Mohit,

use this query;

Declare @A Varchar(10)

Set @A=$http://OCRD.Cardtype

SELECT Top(1)

Case

when @A='s' then 'VE'+ (You can edit this, i.e the way you want the Code to be)

cast((select max(cast(substring(( T0.CardCode ),3,3) as int))+1 from OCRD T0 where cardtype='s')as varchar(10))

when @A='c' or @A='l'

then 'CU' + (You can edit this, i.e the way you want the Code to be)

cast((select max(cast(substring(( T0.CardCode ),3,5) as int))+1 from OCRD T0 where cardtype='c'or cardtype='l')as varchar(10))

Regards,

Joseph

[Former Member]

Hi Mohit,

you can use this query;

Declare @A Varchar(10)

Set @A=$[OCRD.Cardtype]

SELECT Top(1)

Case

when @A='s' then 'VE'+ (You can edit this, i.e the way you want the Code to be)

cast((select max(cast(substring(( T0.CardCode ),3,3) as int))+1 from OCRD T0 where cardtype='s')as varchar(10))

--when @A='c' or @A='l'

--then 'CU' + (You can edit this, i.e the way you want the Code to be)

--cast((select max(cast(substring(( T0.CardCode ),3,5) as int))+1 from OCRD T0 where cardtype='c'or cardtype='l')as varchar(10))

end

from OCRD T0

Best regards,

Joseph Antony

[Former Member]

Hi Mohit,

Then you can set Formatted Search on the BP Code field calling Joseph's query and execute it when you want to create new BP.

Cheers,

Marini