Integration between an ERP and a planning and scheduling application
Integration between an ERP and a planning and scheduling application like frePPLe remains a magic subject surrounded with myths, mystery and smoke.
This is quite unjustified – the integration is far from complicated if you approach it correctly.
Here are our 5 main best practices, learnt the hard way over many integrations we have worked on over the years.
- Technically, it’s straightforward and trivial
- Your ERP data extract needs to be a complete and consistent snapshot
- There is rarely a need to plan more frequently than daily
- Separate execution and planning horizons
- Stick to the planning capabilities of an APS software
1) Technically, it’s straightforward and trivial
It really is. If you are facing technical issues downloading or uploading the data, something is very wrong.
The bulk of the effort should go in correctly understanding the context and meaning of data in both systems and making the correct mapping from a business point of view.
We have created a template for an ERP integration in which we extract the input for each frePPLe object with a query: see the documentation or the source code. The resulting csv-formatted files can be uploaded in the Cloud Edition of frePPLe, or when running frePPLe locally you can choose to directly load the data in the database. It can easily be extended to use different technologies or communication protocols.
2) Your ERP data extract needs to be a complete and consistent snapshot
A planning application needs an accurate and complete vision of the current status.
We recognize 2 kinds of data elements to be interfaced:
- Static information that is not varying too often
Elements in this class are items, customers, resources, suppliers, bills of materials and routings.
Some of these tables are rarely updated (for example the location table) and it might not be worth writing interfaces for them as manually populating them in frePPLe is enough.
- Dynamic information that changes from day to day
Elements in this class are inventories, open sales orders, open purchase orders, open manufacturing orders (work in progress)…
For the static data, a mapping needs to be created between the data model of the ERP and frePPLe.
We typically start from identifying the data that needs to be populated in frePPLe from the frePPLe data requirements and then work out the mapping with the ERP data.
For the dynamic data, the data extract needs to be a complete and consistent snapshot of the status of your supply chain.
Think of it as a picture taken at a particular moment of time.
“Complete” means we need all relevant demand and supply elements.
“Consistent” means that all the data elements have to be in sync, without duplications and black-holes.
The examples below describe some situations of duplications and black holes from real life cases.
A purchase order was placed with your supplier, and you are currently receiving the ordered material in your warehouse. The material may spend some time in quality control process before is booked in inventory.
To obtain a consistent picture in the data extraction we need to see a supply of 100 pieces of item A at every moment we take the picture.
At moment 1 we see an open purchase purchase of 100 pieces of item A.
At moment 3 we see that the inventory of item A has been increased with 100 units.
What do see at moment 2?
If we see both the purchase order and the in-quality-control stock, we are double-counting the supply.
The planning system will incorrectly reduce future purchases for the item as a result.
If we see neither the purchase order or the stock, we have a black hole and miss some supply.
The planning system will incorrectly react to this by generating a (potentially urgent) new purchase order.
A consistent snapshot requires that we see only one of these. A bit of thought and a correct filter for the data extraction is all it takes to do it right.
Imagine we ship material from our warehouse A to our warehouse B. If these warehouses are in different parts of the organisation for accounting reason, it is not uncommon to register a sales order to a dummy internal customer in warehouse A, plus a purchase order from a dummy internal supplier in warehouse B.
The purchase order and sales order may be created at different moments by different people.
They can also be closed or change status at different moments by different people.
And how do we see material that is in transit between both warehouses?
The simplest solution to assure a clear and consistent snapshot of the current status is to consider either one of the transactions as the leading one, and filter out the other.
Refreshing only a part of the dynamic data is never a good idea. All of the data elements are linked with each other, and we need a consistent and correct picture across all data elements.
For instance, refreshing inventory data without refreshing the list of open purchase orders isn’t correct. The closing of a purchase order will change the inventory on hand. If we see the new inventory plus the purchase order that was yesterday still open, we get an incorrect snapshot.
Similarly, refreshing inventory data without the list of open sales orders isn’t correct.
3) There is rarely a need to plan more frequently than daily
It is a common misconception that you need to replan very frequently to adjust for every event that changes the planning input data.
Even for short term planning and scheduling a daily replanning process is sufficient for the majority of businesses. In reality, today’s plan is already communicated to the shop floor and is being executed.
Interfering with actions that are in progress or about to be started is not easy. Continuously revising the plan just leads to unnecessary nervousness and confusion. It is a better practice to hand out the execution plan for the day, and leave the shop floor some room to self-organize the details and self-adjust for small variances and minor unforeseen events. Situations where the shop floor needs to contact the planner to know what to do next will occur, but they should be exceptions rather the rule.
4) Separate execution and planning horizons
A planning tool provides visibility of your capacity and material requirements for a time horizon that can be beyond what is relevant for the ERP.
You need to feed the ERP with the plan output it needs to start executing pretty soon.
In most cases, the remainder of the plan doesn’t need to be sent to the ERP at all. It can stay in the planning tool, and as time goes on it will eventually roll into the horizon that is relevant to the ERP.
After all an ERP system is very transaction-focused and registering all activity going in the companies. The role of a planning system is to provide clear visibility, analysis transparency of the future plans beyond the transactional details.
5) Stick to the planning capabilities of an APS software
A planning tool is not the right place to implement all your reporting, tracking and monitoring needs. One should avoid abusing the APS database as a generic data repository.
It is meaningful to use an APS to display the average purchase orders value per location in the upcoming months but you wouldn’t use an APS to display the average stock on hand in the past months.
Indeed, the former is nothing else than the computed plan visualized in another metric. The later report would require that you stock on the database a huge amount of data to be able to build the desired report, overloading the database with data that should rather be used in a ERP or BI tool and extending the workflow duration.