Lojas KD - SAP B1 Hacked Integration + Multibranch mode

Grab some coffee. This will be long, 2 parts, story.

Part I

When I started at Lojas KD, the e-commerce was running on a server from a local vendor. I'm not even sure if it was a shared server, but for restarting a server, we need to phone the support and give precise instructions to the fellow guys... You got the picture. One of the first moves was to migrate this proprietary e-commerce platform to a cloud-based service (read AWS), so we would get some control over it.

On the other hand, the physical operation had recently implemented SAP Business One, and had one on premise integration solution that read from an integration table on e-commerce database and write to SAP.

We migrated the e-commerce platform to cloud, not without some turbulence, but we made it. And a few weeks on a sunny afternoon... The orders started entering SAP to the wrong customers. No recent deploy. Nothing that could justify that behavior at first...

On the day after, the problem was still there. My manager called the SAP Consulting firm that sold and implemented it. The closest schedule they had was 3 weeks ahead, and we knew we would be broken by that time. In this meantime, we noticed that new user ids were incrementing by some large random amounts. I found the solution that worked passed 9:00 PM. It was a very version specific bug on the autoincrement lock algorithm on MySQL.

A quick parenthesis here. The on premise integration solution that was implemented wasn't a good one since ever. The infrastructure guys had to restart it 5 or 6 times a day. It usually crashed the server, and took 99% processing from one core all the time.

Well, after solving the MySQL autoincrement issue I asked my manager to see the integration process...

- Davis, we do not have the code. It is just an executable file that keeps running.

- OK, can I get a copy of it?

- What are you talking about? Are you thinking on reverse engineer that?

- Yes! I'm willing to disassemble and reverse engineer that.

- But Davis, you are just a PHP programmer.

- Can I get a copy, please?

In a mix of disbelief, perplexity about my unknown skills and despair about the lack of a better solution, I get a copy of the .exe file. One hour later I get some readable code.

Another guy on the team promptly recognized the code as C#... A few minutes later, we found two villains on the code. The integrator was truncating the ID to 6 digits only, so when the autoincrement went over the number 1 million, we start to see the problems. And everything was running inside a while (true) block.

Fix, compile, deploy. Voila! Up and running again. But we knew some day we would need to refactor that integration entirely.

Part II

One of the major business changes we enabled at Lojas KD was the logistic model change. It operated as a cross docking but in a continental country as Brazil, this implied into a lot of inefficiency on products transportation and all related costs.

A product produced near a customer need to travel 3-5 days to our dock. And wait for a few days, until another 3-5 days back to customer house.

A better solution to our case was changing it to drop shipping, ie, the product would leave the factory directly to customer's house.

But this required a lot of changes, starting by enabling the multi branch mode on SAP B1, as well as the integration piece must know to which branch(es) a given order should enter. The other part was to change our product listing to reinforce a location-based purchase, thus reducing the costs - this was made on the e-commerce database as well as on Apache Solr search engine that used to power our pages.

Aftermath

It wasn't a short task, but it was a very grateful experience. I worked together with our DBA, which was also a senior C# developer. We pair programmed. I learned C# and a lot of neat tricks. We built some really nice pieces of software. And this saved basically a year of operation in costs reduction.

Would you like that too? Hire me!