Automatic Generation of BP Code

[Former Member]

Hello,

I have searched the forum and have found a number of queries that may be used as a FMS in order to automatically create a BP Code in the Business Partner Master Data.

My customer has had the following query set up and has been using it as a FMS for a couple of years:

SELECT MAX(T0. CardCode)+1 FROM OCRD T0 WHERE T0.CARDTYPE = $[OCRD.CARDTYPE] FOR BROWSE

He mentioned the last time I was at their facility that the formatted search no longer works. It pulls up 1000 everytime even though there's already a customer code 1000 assigned to a BP. So, he's been manually entering the BP code.

Has anybody had issues with this query failing eventually? Is there anyway to edit this query so it works again for the customer? Perhaps this cannot be answerd on the forum since you don't have thier data to look at, but I'm hoping somebody can at least point me in the correct direction.

I noticed there were a few variations of this query available on the forum. I have tried a number of them without success.

I appreciate anybody's help.

Thank you,

Amanda

[Former Member · Accepted solution]

Hi Amanda,

It is easy to check the query result by bring up BP master screen and then run query directly to see the result.

In this way, you can find the problem quickly.

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,

Do you mean that I should run the FMS on the BP Master screen? I have done that and it brings up 1000.

Or should I be running it some other way?

Thank you,

Amanda

[jitin_chawla · Accepted solution]

Hi Amanda,

This query is from SAP Documented Note : 649675.

Please check the steps mentioned in that and check if the same are also correct or not for working of the query.

I cannot say it will help but you can check all the settings.

Regards,

Jitin

[Former Member · Accepted solution]

No. Just run it under normal query window like other queries. You can see the actual variables picked up.

[Former Member · Accepted solution]

Hi Jitin,

I read the note you referenced and all of the steps were followed. For some reason, however, the query quit working for them. I'm not sure of the details. Perhaps a user entered in their own BP Code which messed up the numbering series. I'm not really sure.

I appreciate the suggestion.

Thank you,

Amanda

[Former Member · Accepted solution]

Hi Gordon,

I ran the query in the query generator and it comes back with 1000 as a result.

Thank you,

Amanda

[Former Member]

Hi,

If they want to give manuallay then this series whatever you write it won't work out. the purpose of creating the Code in automatic is not to skip any number, it may alpha numeric are numeric..

Think that first and reply ...

regards

chidambaram

[Former Member]

Hello,

The customer didn't want to have to manually add the BP code, but unfortunately, because they have been for several months now, I don't think there's a way to go back and have it automatically generate the code anymore.

Thank you to everyone for your ideas.

Amanda

[Former Member]

There might be a Character A - Z or a special character entered in the CardCode when It was manually entered by someone at your client. That Character might be throwing the max(T0.CardCode) function off the intended Max value !

If that is not the case Run queries:

SELECT MAX(T0.CardCode) FROM OCRD T0 WHERE T0.CARDTYPE = 'C' FOR BROWSE

SELECT T0.CardCode FROM OCRD T0 WHERE T0.CARDTYPE = 'C' ORDER BY T0.CardCode FOR BROWSE

and compare results

Edited by: Deepak Aurora on Oct 28, 2008 12:49 AM

[former_member187989]

SAP Note:1058266
Automatic creation of consecutive alphanumeric BP codes


Symptom


How to create consecutive alphanumeric business partner codes automatically.


Other terms


Code, automatic, generate, fill, count, business partner, query, formatted search, populate, suggest, SAP Business


Reason and Prerequisites


Consulting


Solution


In order to create consecutive alphanumeric business partner codes, such as C1000, C1001 etc, please follow the procedure outlined below:


Manually create the first customer, supplier and lead type business partners, e.g. C1000, S5000, L8000.
Go to Reports -> Query Generator -> Click on 'Execute' without making any selection.
In the 'Queries' window click on the pencil icon in the upper left area to activate the query structure area. The area appears grey when blocked, white when ready and yellow when active.
Copy and paste the query from below or the attached text file (BPcode_autocreate.txt) into the window, replacing the 'SELECT *' that is present by default:

select max(cardtype)

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

+ 1 as nvarchar(10)) from ocrd

where cardtype = $[OCRD.CARDTYPE]


Click on the button 'Save',then, in the 'Save Query' window, enter an appropriate name for the query and select the appropriate category. Then click on 'Save'.
Click on 'Cancel' in the window 'Queries' and 'Close' in the window 'Query Generator'.
Go to Business Partners -> Business Partner Master Data, change to 'Add' mode.
Map a formatted search to this field by either of the following means:
Clicking in the field 'Code' to activate it, then select 'Tools' in the strip menu, select 'Search Function', then 'Define'.
Pressing Ctrl+Alt+F2 in the active field.
In the 'Define Formatted Search' window, click on the button for 'Search by Saved Query'
Bring the cursor to the bar that will pop up just below 'Search by Saved Query' until it changes from an arrow to a pointing hand, then double-click.
The 'Query Manager' window will pop up, select the category where the query above was saved under, then click on the query name to select it, click on 'OK'.
Place a tick in the box for 'Auto Refresh When Field Changes' and select 'BP Name' from the drop down menu.
Click on 'Cancel' in the Business Partner Master Data window and open it up again.
Click on 'Refresh Regularly' and click on 'Save'.
Change to 'Add' mode, select the 'Type' (Customer, Supplier, Lead) and enter a name for the new business partner. Press the 'Tab' key and the next available BP code will automatically be inserted into the BP code field.

Jeyakanthan

[Former Member]

Hi Jeyakanthan,

Thank you for the copy of the note. I had read and tried to use the query in the note. However, I run this query it puts an alphanumeric value into the field such as C1000. Our customer would like to try and keep the numbers numeric if possible. Will this be possible considering they have been manually assigning numbers for some time now which has led to a gap in the numbering sequence?

Thank you again for the note and the suggestion.

Amanda

[Former Member]

hi,

Better to create a add-on for the automatic creation based on the following business partner type - vendor/customer/lead.

If Vendor you can start with 'V00001'

If Customer you can start with 'C00001'

If Leas you can start with 'L00001'

Regards

chidambaram

[Former Member]

Thank you Chidambaram for the suggestion.

Amanda

[former_member583013]

Amanda,

What is the format of the existing Customer Codes..

Is it C1000 or only numeric 1000

If it only 1000 are they using more than 4 digits in their numbering..example: 001000

or did it start with 0001, 0002, 0003, ...0998, 0999, 1000

Please use the SQL to check the values in OCRD

SELECT CardCode from OCRD WHERE CardType = 'C'

[Former Member]

Hi Suda,

It looks like the numbering series started at 3 and continued from there. However, there are gaps in the numbering sequence which I'm assuming is causing the problem. I'm not sure what happened or when, but I'm assuming that at some point somebody entered in their own number instead of using the FMS which started to throw things off.

As of right now, the numbering series is at 200000 level. But like I said, there are huge gaps in there. My customer wasn't sure of how long ago the FMS stopped working, so the gaps don't surprise me.

Is there anyway to get them back on track to have a FMS work again? Or will they have to continue manually assigning numbers from now on?

Thank you,

Amanda