Metro - Inventory Design and Strategy

Inventory Design - Metro Garage Door Co.

There are 3 inventory tables (files):
  PRODUCT - has onhand balance total for all locations
  PRODLOC - has onhand balance by location (truck or whse)
  PRODTRX - has transactions by location (truck or whse)

Each mobile device will have PRODLOC and PRODTRX for only its
location. They will have partial data, and the balances and
transactions will not match the server tables. Only the host DB
has accurate inventory data. That is where all reporting is
done. Handhelds are data collection only.

The MySQL DB on the server will have PRODLOC/PRODTRX for all
locations.

When inventory receipts, returns and adjustments are entered on
truck and warehouse devices, the transactions will be stored in
the device DB table PRODTRX and uploaded to the server database
on the next sync upload, and will add records to the server
table PRODTRX, and will update balances in the server tables
PRODUCT and PRODLOC (creating records as needed).

All inventory reporting and maintaining of inventory balances
and transactions will be done from the server DB.

To create starting onhand balances for each product/location:

(end user steps in BROWN below)

1. in the server DB:
   - set QTYONHAND, QTYONORDER, and QTYALLOC to zero in all
     PRODUCT records
   - delete all PRODLOC and PRODTRX records (sync will create
     PRODLOC records as needed)

2. do a physical inventory of the whse and of each truck

3. Warehouse user (rep 99) using mobile device of PDA Emulator
   - enter adjustments for all products in the whse
   - do a host sync to upload data (it does inventory trxs first)

4. Truck user using mobile device in manager mode (or PDA Emulator set to truck user)
   - enter adjustments for all products in the truck
   - do a host sync to upload data (it does inventory trxs first)

5. repeat step 4 for all other trucks

NOTE: Step #4 not manditory, but truck inventories will not be reflected accurately if this step is not done.