Several months ago I was asked by my manager to find a way to monitor incoming and outgoing quantities of CHEP pallets. I was provided with a login to https://my.chep.com/ website where I can download an excel file with all transactions. Then I should compare the quantities on CHEP website with the ones we have on our programs, but I came into several issues.
We have an excel program called Delivery Schedule. When a delivery is due to come in a haulier must book in with us and provide us with the quantity of pallets they are planning to deliver and we enter that amount to Delivery Schedule. However not all suppliers/hauliers do that. Even if they provide us with the quantity, it is not always correct (for example if the supplier didn’t produce enough stock till delivery is due, it will send what it has and will organize the remaining order to be delivered later, but the first booking on our system will unlikely to be changed to represent the change in pallet quantity, so in this case if I just use quantities from this booking system, it will not be correct a lot of the times). So using Delivery Schedule is not a viable option.
Secondly, I thought about using PROTEAN to determine how many pallets were delivered, but again there is no way to determine the accuracy. My initial idea was to extract the data of locations were the pallets have been put away (meaning 1 location is equal to one pallet), but this approach doesn’t work in the case when pallets are put away on the floor in one line. In this case I could divide the total weight in that location by single pallets weight (as most of them are identically packed) and come with the pallet quantity, but in a case where I have two half loaded pallets this approach will provide inaccurate results. What makes this approach even less accurate, is that not all pallets delivered are CHEP pallets, so even if I calculate the amount of pallets by locations, there is no way to determine how many of them are CHEP and how many are not. So PROTEAN is also not a viable option.
What complicates things even more is that the supplier names on PROTEAN and https://my.chep.com/ do not match in a lot of cases! So even if I somehow manage to get the quantity of CHEP pallets delivered from our programs (PROTEAN or Delivery Schedule) in order to compare it, I would need to write a “bridge script”. Which in essence would look like this:
- Pick a supplier name from CHEP portal (excel file).
- Find a match using the “bridge script” that refers to Supplier name on Protean (or Delivery schedule).
- Retrieve pallet quantity from Protean using the supplier name from “bridge script”.
- Compare CHEP and PROTEAN (Delivery Schedule) pallet quantities
- Repeat processes for another supplier until there are none left.
And this bridge script would require to be updated constantly as suppliers come and go.
The best way to do it, would be to establish a procedure where CHEP pallets are calculated once delivery is being unloaded, and just store this data in a database or excel sheet, but this hasn’t been implemented and I don’t think it will as it would add more paperwork to existing procedures.
As my task is to just keep an eye on this and look for MAJOR discrepancies and I can’t find a way to use our programs to provide me with helpful data, I decided to just rely on CHEP Transaction Report excel file to do it. How I’m gonna do it will write in another post 😉