Ecommerce and ERP integration manage inventory.

S

SBSP

Senior Member
Joined
Sep 7, 2007
Messages
667
Reaction score
16
Hi All!

From time to time I teach my self new things by picking a topic , research it and then try to implement it in a test environment.
I'm using a student version of an ERP (Sage 300 aka ACCPAC) .

I happen to know it 's back-end very well.

This is what I have done.
Created and order form using PHP.
A JSON based end-point that exposes a price list with Item, description, price and stock available form the inventory.

I'm able to add items to the order form which I get from the web service as opposed directly from it's MSSQL database.
I try to use AJAX as much as possible to minimize it from hitting the database too much. I think ive don a good job so far :-)

The said order form currently saves the order in a MYSQL table. This is where I have a problem. The order does not yet exist in the ERP's database. If the customer had to select and item and there is 1 item left and he's taking his time to add other items to the order, some or other time someone else will snatch the item and offcoarse the ERP is not yet aware of this. By the time the order is sent to the ERP someone is not going to be happy. My thought is if the item wasnt available from the start the back order would have been avoided.

I dont know what's acceptable as far as live inventory values go.
So I'm asking the experienced. Should an Ecommerce front end be fully integrated as in when the item is selected and added to an order it should be committed ?

Or should I attempt to expose the actual order in the ERP to the web front end and then edit the order directly?
 
In a retail environment, absolutely, yes.

Add a table that moves the stock item quantity on the SO to a "on hand, but not available" status whilst the user is processing the SO.
 
Last edited:
I think I get what you are saying. I should create a table in the ERP's database that will list the items used on the web front end order along with their QTYs and then remove stock by joining it with the query used to expose the stock availability when they add items to the order ?

That's a good idea. But if the system is used from both sides the native capture screen will not take that into consideration. Unless you setup a dedicated warehouse for online orders which might cause allot of maintenance especially if you want your customers to be able to by any product you have.

Or you have a dedicated dummy SO used to capture and commit stock.
 
SBSP said:
Or you have a dedicated dummy SO used to capture and commit stock.
Click to expand...

The sneaky dodgy way could be to avoid the ERP's API and enter a line in to the Order details table that will cause the ERP's native screens to include it in it's stock availability calculation. Sound like integrity errors. Will give it a try just for fun.
 
Why are you separating what happens on the front-end, entirely with what happens on the ERP? a micro-service that checks stock and allocates it to a pending order straight from the ERP shouldn't slow down the front-end... just wondering why you chose the separation?

Separating it, IMO, just adds another system that could potentially break
 
I figured it would have negative user experience due to poor performance when trying to manage the order directly from the Web UI. I recon most companies would prefer a hosted solution by having their web portal in a data center and the actual ERP package in their server room. Also if their SQL server is poorly managed it would stop the web front end from working aswell..

I also think most companies dont have adequate internet connections.
I chose this based on my own experience at work.



I get your point though.
 
Stock reservation in carts is a difficult one and it depends on your target market.

* "A products" are high revenue, eg 30 products doing 50%.of your sales
* "B products" are 40-45% of sales, eg 5000 products
* "C products" are 5% of sales with many never a sale, eg 5 million products


For daily deals page, or any A products, you need some sort reservation counter that needs to be considered your source of truth. Redis is excellent here.

For your B products you might only want stock reservation once low stock threshold hit, else caching that is updated after each stock adjustment.

For C products no stock reservation as these should all be back order to reduce warehousing costs. Strictly cached with adjustment after stock movement.

Real stock adjustment is only done once payment had cleared. You want to autoexpire stale carts and indicate this with your stock reservation system.

Stock syncs are essential and this is where your business rules need to have downtime in off peak.
 
cbrunsdonza said:
Stock reservation in carts is a difficult one and it depends on your target market.

* "A products" are high revenue, eg 30 products doing 50%.of your sales
* "B products" are 40-45% of sales, eg 5000 products
* "C products" are 5% of sales with many never a sale, eg 5 million products


For daily deals page, or any A products, you need some sort reservation counter that needs to be considered your source of truth. Redis is excellent here.

For your B products you might only want stock reservation once low stock threshold hit, else caching that is updated after each stock adjustment.

For C products no stock reservation as these should all be back order to reduce warehousing costs. Strictly cached with adjustment after stock movement.

Real stock adjustment is only done once payment had cleared. You want to autoexpire stale carts and indicate this with your stock reservation system.

Stock syncs are essential and this is where your business rules need to have downtime in off peak.
Click to expand...

Thanks. My approach is aimed at B2B which complicates it a bit more.

I ran into some obstacles.

Especially with back orders involved. I cant seem to get around how to automatically for-full Back orders efficiently. Once you allow the customer to create back orders they will end up partially shipping the order in small bits which could lead to unnecessary shipping cost.

Allow the customer to commit stock and have back orders , allow back orders only without stock commitments, or just allow whats on hand.

Allowing commitments with back orders will reserve the stock and the customer is guaranteed that they will get it.
But they can also use that to their advantage by blocking the stock until they have money to ship it.

Not allowing the commitments but allow back orders means they have to ship the stock ASAP!
 
SBSP said:
Thanks. My approach is aimed at B2B which complicates it a bit more.

I ran into some obstacles.

Especially with back orders involved. I cant seem to get around how to automatically for-full Back orders efficiently. Once you allow the customer to create back orders they will end up partially shipping the order in small bits which could lead to unnecessary shipping cost.

Allow the customer to commit stock and have back orders , allow back orders only without stock commitments, or just allow whats on hand.

Allowing commitments with back orders will reserve the stock and the customer is guaranteed that they will get it.
But they can also use that to their advantage by blocking the stock until they have money to ship it.

Not allowing the commitments but allow back orders means they have to ship the stock ASAP!
Click to expand...

Actually, you are complicating your business rules.

This is why you have split shipment but delivery needs to be charged on each one. No such thing as a free lunch and why B2B charges delivery on each parcel.

Don't rely on automation, instead switch over to manual splitting as well.

Let the client decide when to split the shipment.

I've sat in countless meetings discussing split shipment along with seeing multiple short-lived attempts get rolled backed. Having millions of customers on your platform can easily poke holes within hours.

You could always ping your client with an update when an item is ready for packing to convert them to split shipment. I've seen this before.

But human intervention is often the secret automation tool. Clearly define your business rules with all the correct controls & checks, implement them and then mark any exception for human intervention.
 
Now to throw the next spanner into your system, you need to design a system for your returns as well to handle your failed deliveries, stolen deliveries, wrong goods shipped, wrong goods ordered and goods returned due to warranty claims.

The first iteration took my previous employer 24hrs to build, that was their deadline. Second iteration took me three years to improve and automate.
 
After a few days messing with this I started over twice :-)

The first attempts was a mix between PHP and MSSQL, Simply because I'm not so much into ASP. But from what I have read PHP and MSSQL drivers don't make the best combination. Then I realized Sage300 comes with a REST WebAPI (My own fault for not checking this first) The API has a few minor limitation but can easily be extended with a custom API. At this moment I'm back at getting the API to work then only can I get back at the business logic.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X