Technology: SQL Server 2005 and VB.Net 2003
I was asked by a client if I could create an inventory system to track avocado harvests, packing into boxes, storage, cold room management, inventory control, import from other countries as well as sales and interface with accounting software. I said yes.
Between the start of the project and my hand-off, I created a system which could do the following:
- Cooler monitoring and control
- Tracks temperatures and humidity and records their values in a database for later reporting or retrieval onscreen.
- Provides for a “cooler” mode and a “gas” mode. Cooler mode enforces a set of two low and high temperature alarm values, while gas mode enforces a set of two higher low and high alarm values. When the system detects that temperatures are outside of their boundary values, an alarm program displays a visual and audio alarm that there might be a cooler malfunction. Also, an email alert is sent to designated users in case the alarm is triggered after hours.
- This sub-system can also track carbon dioxide and ethylene concentrations provided the appropriate detectors are installed to feed digital readings to the monitoring program.
- This system runs as a background process on a local server, or can run on a remote server if desired.
- Grower Packouts
- Collects data from a sizer computer (can be customized for any sizer that outputs data in any format) based on grower tickets, number of bins, commodity, gross weight of the bin, etc).
- Collects the actual sizer run which provides fruit count by size and/or quality class, cup weight as reported by the sizer, and any other information the sizer provides, including drop lane, bin drop, label or no label, etc.
- Allows an operator to reconcile gross minus tare of bulk weight vs. sized weight vs. packed weight.
- Allows a preliminary packout with or without prices to be faxed or emailed to a grower.
- Can batch print packouts that need to be mailed to growers.
- Can indicate whether the grower has been paid or not (depends on interface to accounting system).
- Grower packouts are recorded in the document management system.
- Summary system shows breakdown by grower, commodity, size, grade, etc.
- Bin Inventory System
- This module allows for entry into a special inventory system for plastic or wood bins based on their assigned number.
- Can track when a bin leaves the plant, where it is in the field, and how many are unaccounted for.
- Inventory System
- Inventory is multi-location and multi-commodity.
- Each box can be serialized for traceability (provided there is an adequate mechanism to indicate which growers bins are being processed at the time).
- Each pallet can contain any mix of sizes or configurations (lugs or tray packs or even deli packs) on the same pallet.
- Pallets can be partial pallets.
- Allows incomplete pallets to be posted to inventory but to be filled up the following day. A pallet can also contain any combination of pack dates in case partial pallets are filled as a regular practice. The inventory system will indicate which pallets were filled on different days.
- Domestic Inventory starts during packing when a pallet tag is assigned to a pallet. Operator uses a touch screen terminal to create a pallet tag when the pallet is filled to its designated limit (which is configurable).
- There are aged inventory reports which show which pallets are at what age since being packed.
- There is a bin location system which was never implemented which can use a grid to tell where a pallet can be found. If this is implemented with RFID (radio frequency identification), system can automatically tell you which cold room a pallet is in, even if you don’t need to know the actual coordinates of the pallet.
- Import and purchased fruit are handled in such a way that their inventory numbers show as in-transit and pending arrival. There is a mechanism for tracking when and how long an imported pallet is in inspection by the USDA, and reason codes as to why a pallet was rejected. This is so you can do charge-backs to the exporter if they sent fruit that arrived in unsalable condition.
- Depending on the importer, can track the boat, departure date, estimated arrival date, container number, temperature recorder number, whether the fruit was in a controlled atmosphere container, its hold location, which grower packed it, etc.
- A receiving module which allows warehouse personnel to receive the fruit and check if the actual delivery matches the manifest sent prior.
- A shipping station module which allows warehouse personnel to ship based on pick ticket number. Their screen shows a list of pending pick-ups and allows them to prepare a delivery in case they need time to do that. Can optionally prevent them from shipping the wrong size, variety, commodity, plu vs non-plu fruit, etc. They can be given as much or a little leeway as management dictates.
- Warehouse personnel can also generate pallet tags for purchased fruit which comes from another packer or warehouse. Since this fruit often arrives without any tags or manifest of tags, this allows you to record it into inventory as purchased fruit under a generic category, or under that specific packer’s designation.
- Fruit which is returned to the warehouse for any reason gets a reject or return code and can be tagged as destroyed or returned to stock.
- Allows for batching multiple orders onto one truck, and records the signature of the driver once for all orders.
- When an order is shipped, bill of lading(s) go to driver, passing and summary report go to sales office.
- All shipping documents in the system are recorded in a document management system native to this software. You can retrieve any and all shipping documents from the original pick ticket to the bill of lading, purchase request, passing and invoice (if you use this system to generate invoices).
- There are numerous screens which allow designated personnel to view various levels of detail or summary of all inventory activity. These can also be printed in a report form.
- Import Inventory Module
- With appropriate documentation from an exporter, their electronic manifest (Excel spreadsheet) is entered
- Ins and outs of inventory are similar to domestic inventory
- Loads can be tracked by boat or by a user generated code.
- Accounting is performed which tracks ancillary costs for the boat, along with advances paid and money remaining to be paid.
- Can track and apply pre-season and other advances for a particular boat and for a pre-season non-specific payment advance.
- Can report back to exporter the load’s disposition (how much sold and at what price, etc.). Reports can be emailed or faxed. These reports are also snap-shotted for the document management system.
- Aged inventory can show domestic and import, or just domestic or just import.
- Order Entry
- Allows sales personnel to create an order in three mouse clicks or less. Each customer’s prior orders are summarized, so that their most commonly ordered commodity is listed first, so the sales desk can quickly verify they are ordering something similar to past orders in order to pre-populate the order.
- Allows for a customer to have multiple ship-to locations which can be selected in order entry.
- Can handle actual orders as well as cooler transfers, purchase orders and drop shipments.
- Order can indicate if shipment should originate from a remote cooler.
- Pick ticket or other documents can be faxed to a remote cooler so they know to ship it.
- Once an order is placed, inventory is reserved against the order.
- Once an order is shipped, inventory is released from stock.
- Inter-warehouse transfers show the stock as being in-transfer, while imports are shown as being in-transit.
- Sales History
- Shows breakdown of sales by customer or variety or a combination of these and other variables.
- Can display sales by year and compare multiple years.
- Deployed in a browser such as Internet Explorer or FireFox. Can be run on a local area network or over the Internet for access by field personnel or home based workers.
- Requires Microsoft SQL Server 2008 be installed at the main plant.
- Can be multilingual if required.
I own the source code for this project, and it is copyrighted by me personally. This is a powerful package which has enormous potential to enter a seemingly saturated market with a low cost self-hosted or SAAS alternative for small packing and brokerage houses.
Bring this to market as a module in the Drupal 8.x world, and/or as a plugin for the WordPress world. I am looking for a financial/marketing partner who could sustain dedicated development for at least a year. Ideally, 5-10 pilot sites, each with a different produce commodity, could fund most of the development.
The pilot sites would spend a fraction of what they would have paid for a commercial package, while the partner could recoup costs and enjoy profits based on recurring income from maintenance and/or service fees for hosted environments.