Generating the Number

One of the most popular issues that I’ve seen in a central create scenario is the question of how to generate the number. If the creation of a new record occurs in MDM, then how does the ECC number get generated? Everyone seems to want MDM to operate the same way that ECC does, which allows you to configure different number ranges based on material types or account groups (depending on what business object you are working with). Unfortunately this is a difficult task to do for several reasons. I’m going to first address why it looks simple to do, but isn’t quite as simple as it looks. Then I’ll talk about some possible technical solutions that I have experimented with. Finally, I’ll address the issue the correct way, which is by designing the correct system architecture to support global numbering.

At first it seems like creating the number is a simple task in MDM. After all, MDM has a field type called Auto ID which generates a new unique number that increments by one each time a new record is established in the repository. Additionally, you can configure a lookup table to store account groups. For those of you that are familiar with MM configuration in R/3, you know that it’s a pretty simple process to configure a number range. You can simply define your account groups in IMG, and then use transaction XKN1 to define the ranges. At first I thought a decent solution would be to use an assignment that would take the Auto ID number and add that to a range.

Unfortunately, there’s one major issue with this approach. There will be gaps in the number range. It doesn’t matter if you create a PAY, CARR, or ICOM record, the Auto ID is going to increment its value regardless. Therefore, your number may be 90000030 even though you’ve only created one PAY record, because there were 29 other records created in another range. This becomes a big issue if you want to use small ranges because you can quickly run out of numbers for a particular account group.

So I decided to try taking it a step further by maintaining a table with the ranges. It seems simple enough, until you try to increment the range value. If you try to use an assignment on the main table then you’ll quickly discover that you can’t modify a record in a lookup table from an assignment on the main table. Furthermore, you can’t use an assignment inside the lookup table because it is unable to read data from the main table so it doesn’t know what the account group is.

So the next thing I tried was using a workflow. I figured I would branch the workflow based on a validation that checked the account group. Once the workflow had branched I would call an assignment in the lookup table that would increment the correct range. So I would have three assignments, each would increment only one range, and only one would get called depending on the path the workflow took from the validation. This seemed like it would work well except that if a workflow is built on the main table it can’t reference assignments in a lookup table. Also, a workflow on the main table can’t launch a workflow from inside a lookup table. This pretty much means I was stuck with no good way to increment the value of a range if and only if it was a particular account group.

So where to go from here? The next best thing would be to use the qualified range feature of the remote systems in the MDM Console. Basically, you can configure a number range for your remote key based on the system that you are syndicating to.

Then, in the syndication map, you map the remote key to the number field. Then, when you syndicate a record out of the repository it will take the appropriate number range for that remote system which meets the criteria of the record and populates the number field in the file generated by the syndicator. This actually works really well; the only downfall is that the number is not generated in the repository, only in the file which is syndicated. This is a problem for most businesses because they want to be able to see the number in the MDM repository so they know what the number is in ECC.

This brings us to my alternative solution, which is to build a custom program in the Java API that generates the number. I hate building custom programs because I try to stay as close to standard functionality as possible, however sometimes if the business requirements are strict you simply can’t avoid it. On one of my projects they absolutely wouldn’t bend this requirement, and by the time they realized how difficult it was to generate the number in MDM, the number ranges were already solidified because other teams had started building their configuration around them. This meant we had to solve the problem in MDM. In order to write a custom program to generate the number, you have to think about how that program is going to get called. Additionally, you need to consider the longevity of the solution. We integrated the custom code into a Portal iView so the user didn’t even know it was getting called in the backend. Besides, the decision had already been made that all end-users were going to use an iView.

First, I built a table in the data model to store the account groups. Next, I built a table called Number Ranges which stores the range, as well as a key which is a unique identifier for each range.

Finally, I built a table called Key Mapping which stored a list of the account groups, and then had a field which was a lookup to the Number Range table in which we could map the key of a range to an account group.

So the Java program would start by first grabbing the account group that was selected for the record, then jumping to the Key Mapping table and finding out what key was associated with that particular account group, and finally it would go into the Number Range table and find out what range was associated with that key. Then it would increment the number and assign it to the number field on the main table. This solution allowed the user to add new account groups, and change the ranges in the future and the Java program would work just the same because the ranges weren’t hard coded into the program.

Now, before I give anyone the wrong idea, I want to mention that in my opinion none of the methods mentioned above are any good. I personally believe that the best way to generate the number is by designing your landscape and integration architecture correctly. The best implementation that I've ever seen used a global number in MDM at the time of creation (new record), and then each corresponding remote system (ECC included) generated it's own number when the record was interfaced in. That number was then sent back to MDM and updated in the key mappings table. The idea is that MDM stores a global key, and then the key mappings table stores reference to all the numbers in your partner systems. I really do think this methodology is using MDM the way it was designed to work.