Shipment Tracking System Outline

The customer in this case is a ZF Lemforder South Africa branch. For more on ZF click here.

The new system is intended to simplify and improve on the existing excel based system currently in place making it quicker and easier to perform the basic functions required. The existing system can be broken down into the following key steps:

1. A list of OPEN PURCHASE ORDERS, in the form of an excel spreadsheet, is extracted from Syspro using an existing visual basics program based on a user entered date range. This list includes local suppliers so these suppliers are manually deleted from the list. This list is then emailed to K & N. This is just for their records.
2. Certain suppliers in the list generated in step 1 need to be sent to LOCTON. Those suppliers that do not need to be sent to LOCTON are deleted from the list and it is then emailed to LOCTON.
3. Open orders generated in step 1 and sent to K & N are taken as being in a status of 0. A second spreadsheet is created containing only the first weeks open orders from the list generated in step 1 and it is called the K & N status report. It is also emailed to K & N. K & N will update this data with details on the status of each purchase order line and send the list back to ZF. Lines in the list are sent back and forth until a purchase order line is complete and it is placed in a status of 99. The line is then removed from the list.
4. A third excel spreadsheet called the container requests list is updated daily with details on all containers that are expected to arrive. The items in these containers are placed IN TRANSIT in Syspro and a GRN is created.
5. When items physically arrive, a warehouse transfer is performed in Syspro and the items are placed in the AC warehouse.

In order to better understand these requirements some points need to be clarified:

• What is an open purchase order? It is a purchase order that still has outstanding items that have not yet been supplied.
• What is Syspro? Syspro is a well know international ERP system. For more info on Syspro please click here.
• What is K&N? KUEHNE+NAGEL is an international logistics company. Please click here for more info.
• What is LOCTON? LOCTON is a packaging company. Certain items have special packaging requirements and need to go via LOCTON.

So basically the existing system consists of a VB6 program that extracts a list of purchase orders from certain Syspro database tables and produces an excel spreadsheet. This excel spreadsheet is manually processed by one or more people and the information contained in it feeds into 2 other spreadsheets. Some information is sent via email to K&N, some to LOCTON and some is returned with updated details back to ZF for further processing. Finally the information is used to manually run one or more standard Syspro function.

Clearly many problems can already be identified and the information in the outline is still only at its most basic level of detail. The main problems are as follows:

1. The steps to perform the required tasks are split between a VB6 program, manipulating a number of excel spreadsheets and manually running some standard Syspro functions from within Syspro. It would be better if all these tasks could be performed by a single system.
2. There are no data validations performed in the excel spreadsheets against the Syspro database.
3. Security is a problem because anyone that has access to the spreadsheet could make changes and many users require access to a single spreadsheet.
4. Finding records in the spreadsheet is difficult when the spreadsheet becomes very large.