Shipment Tracking System Feasibility Document

FILE IMPORT AND VALIDATION

The file currently returned by Kuhane & Nagel is a Microsoft Excel file containing, among others, the following columns:

Supplier name
Supplier code
Purchase order number
Purchase order line number
Ex Works date
Supplier delivery note number
Supplier invoice date
Supplier invoice number
Invoice value in Euros
Part value in Euros
Part number
Order quantity
Container size
Container number
Vessel
Voyage
Estimated ship date
Estimated arrival date in Cape Town
Status

A program will be created to validate and import the data in this file into the system. The user will be required to save the file attachment from the email message onto their computer. The program will then ask the user for the location of the file to import. This will allow the user to overwrite the existing file each time when saving the attachment or save each file received with a new file name in order to keep a history of files received.

In order for the program to import the file the format of the file must be fixed and must be agreed upon by all parties. The following column names are suggested:

SupplierName
SupplierCode
POnumber
POline
ExWorksdate
DeliveryNote
InvoiceDate
InvoiceNumber
InvoiceValue
PartValue
PartNumber
OrderQty
ContainerSize
ContainerNumber
Vessel
Voyage
ETSdate
ETAdate
Status

All date fields must be in the CCYY/MM/DD format for example 2006/08/28. An example of this file in the required format will be made available.

The first validation performed by the program will be to make sure that the format of the file is acceptable in that all required columns exists in the file identified by their column names and that the data contained in each column is in the correct format such as numeric, alphanumeric or date(CCYY/MM/DD) etc. For example a common problem with importing Excel files is that one ore more blank lines exist at the end of the file or one or more blank columns exists. This problem can easily be corrected by simply deleting this blank information and saving the file. If the file is found to be in an incompatible format an error message will be displayed to inform the user.

Once the format of the file is correct the program will display the lines to be imported. The user will then select to import the displayed lines by means of an IMPORT BUTTON.

The program will attempt to validate all imported lines. It will be assumed that the purchase order, line number and container combination will uniquely identify each record. If the imported line does not pass one or more validation criteria a descriptive error message will be displayed for each validation criteria that failed.

The validation criteria are as follows:

If no matching line exists (based on purchase order number, line and container) but there are lines that have the same purchase order number and line number then those lines will be displayed to the user for checking. The user can then decide to accept or reject the new line. This validation criteria applies to the import function only.
If the line does exist but the vessel, voyage or ETS date has changed the new value will be assumed to be correct and the program will automatically overwrite the existing values. This validation criteria applies to the import function only.
If the line does exist but values other than the vessel, voyage or ETS date have changed the new values will be displayed to the user for checking. The user can decide to discard or accept the new values. This validation criteria applies to the import function only.
The suppler code must be a valid Syspro supplier
The Purchase order must exists and be in a valid status
The purchase order line must exists
The part must be a valid Syspro stock code
The part number for the line must be correct
The part value will be validated based on the rand value of the purchase order line in Syspro and the exchange rate for the invoice date. The exchange rate for the invoice date could be maintained in an external table or possibly using Syspro’s contract pricing.
The status will be validated against a status table

The program will display valid and invalid lines differently (possibly using different colors or by means of a check box) and for invalid lines will display one or more error messages linked to the line. A printable exception report will display all invalid lines. Each validation could possibly be given a code so that exceptions displayed or printed can be filtered by type to allow specific users to resolve only errors of interest to them.

The routines that perform the above validations can also be run after corrections have been made by means of a VALIDATE button. The same routines will also be performed as part of the PURCHASE ORDER RECEIPT function.

If any changes made, such as expected due date, require modification of the purchase order details in Syspro the PORTOI business object will be used to make the required changes in Syspro.

Work to be done:

Design of tables and database structure…………………. 6 hours
Initial import of Excel file……………………………….. 6 hours
Validation functions…………………………………….. 20 hours
Exception screen display………………………………... 5 hours
Exception reports………………………………………… 10 hours
Syspro purchase order changes PORTIO………………. 20 hours


QUERY FUNCTIONS

Once the data has been imported into the system the data can be queried using the same program. To make the query more functional datasets will be used to temporarily store batches of data on the client side. Using this approach trips to the database to retrieve data are reduced decreasing network traffic.

Client side filters will then select the data actually displayed in the program to help the user find the desired records. This process should be quite efficient because the batch or records to filter are cached in memory on the client computer. The size of the record batch should be large enough to limit network traffic but small enough to remain efficient. To achieve this, the actual query to the database should include some less specific selection criteria such as an EST date range of two months defaulting to the current month and last month.

The following fields will be used to filter data on the client side:

Supplier name
Supplier code
Purchase order number
Delivery note number
Invoice number
Invoice date
Part number
Container number
Vessel
Voyage
ETA date

Each filter will apply a LIKE selection to the dataset. For example, if the user was looking for a part number that started with 6760330- but the part could be 6760330-01,6760330-02 or 6760330-03 the user would simply type 6760330- into the stock code filter text box and only lines that have part numbers that contain the character string 6760330- would be displayed. The character string can appear anywhere in the part number. If the user typed in 760330- into the stock code filter text box then lines containing the following part numbers would be displayed:

· 1760330-01
· 1760330-02
· 2760330-03
· 2760330-04
· 3760330-05
· 3760330-06
· 4760330-07
· 4760330-08

Combining more than one filter selection at a time will provide the user with a very fast and efficient way to find the desired records.

This program can be setup to run from an icon on the users desktop or an icon in Syspro’s user defined menu.

Work to be done:

Query of record batch and required screen options………. 4 hours
Filter options screen………………………………………. 8 hours


LOGIN AND SECURITY

The program will limit user functionality based on command line arguments passed into the program to allow some users to edit the data but only allow others to query the data. The program will be run from within Syspro, so this will provide some level of security because in order to run the program the user would need to be logged into Syspro.

Using command line arguments such as MODE=EDIT or MODE=QUERY when setting up the user menu icons in Syspro would be the simplest and most cost effective way of providing security and limiting user functionality and with detailed installation manuals for the program it should be quite simple for an administrator to manage user access and security for the system.

The Syspro operator code could be passed into the program, so that when any records in the database are changed the system could record which operator made the last change as well as the data and time the change was made.

Work to be done:

Command line arguments passed into program……………. 3 hours



PURCHASE ORDER RECEIPTS

The receipt of goods into the AC warehouse will be done from within this program by clicking on the CONTAINER RECEIPT button. The user will make use of the container filter to ensure that only records for a single container are displayed. All other filters will automatically be cleared to ensure that all records for the container are included. If records for more than one container are included in the selection this function will display an error message indicating this.

Before continuing this function will run the validation routines detailed above and will only attempt the container receipt if there are no errors found. The function will use Syspro’s purchase order receipt business object (PORTOR) to receive all lines for the container into the AC warehouse.

Once a line has been receipted it will be flagged as such and will no longer be visible in the program to avoid duplicate receipts.

Work to be done:

Function validations…………………………………………. 5 hours
Purchase order receipt PORTOR……………………………. 20 hours


PURGING OF HISTORY DATA

A function will be provided in the program to allow the size of the database to be controlled by purging unwanted records from the database. The user will input the purge date and all records with a supplier invoice date older than the purge date will be permanently deleted.

Work to be done:

Create purge function………………………………………… 5 hours


SYSTEM DESIGN

The system will be written in Microsoft Visual Studio 2005 using C# and will make use of web services over the intranet as the interface between client and server. This will provide greater scalability and should reduce connectivity problems.



TOTAL HOURS…………………………………………………….. 112 hours