Wisconsin cheese Group - WCG/EVD Design Specs

Design Specs - ForceField application for WCG/EVD

04/24/2013 - original version of specs document

TABLE OF CONTENTS:

A. Rules for exporting Canopy data to handhelds
B. Text files created for Canopy processing
C. Emails
D. Web Browser Reports
E. Order upload rules for handhelds


DETAIL CONTENTS:

A. Rules for exporting Canopy data to handhelds

   1. Shipvia codes
      - export only "Our Truck" (no Canopy table used)
   
   2. Terms codes
      - export all records in Canopy table "AR_CodeTerms"
   
   3. Customers
      - select records from Canopy table "AR_Customers" where:
        - Name is not blank
        - Name does not begin with an asterisk ("*")
        - SelectionCode #9 (DSD customer) is "D" (WCG only)
        - RouteCode is equal to handheld route# (non-warehouse users only)
      - for each customer selected, find all its shipto records from Canopy table "OE_CustomerShiptos"
      - export data from each shipto (plus customer data) to handheld customer table
        - credit data is from Canopy table "AR_CustomerAmounts"
        - customer comments/notes are from Canopy table "AR_CustomerNotes"
          - if size is 490 characters or less, they are put in handheld customer comments field.
          - otherwise the first 450 chars are put there, and full text is put in customer Notes table.

   4. Customer Order Guide (standard orders)
      - export all records in Canopy table "OE_StandardOrders" for customers exported in #3 above.
      
   5. Items
      - select records from Canopy table "IC_Products" where:
        - Discontinued = 0
        - ProductCode between '00000' AND '99999'
        - Description1 does not begin with '*'
        - WarehouseCode = 81 (WCG) or 32 (EVD)
      - item category is from Canopy table "IC_CodeProcesses" record for item's "ProcessCode"
      - item subcategory is from Canopy table "IC_CodeProductCategories" record for item's "ProductCategoryCode"
      - item brand is from Canopy table "IC_CodeBrands" record for item "BrandCode"
      - alternate UOM conversion factor is from Canopy table "IC_ProductUnitsOfMeasure" for item's "UnitOfMeasure_Alt"
      - case UPC code is from Canopy table "IC_ProductUnitsOfMeasure" for item's "UnitOfMeasure_Alt"
      - item qty onhand, on order and available are from Canopy table "IC_ProductQuantities"
    
   6. Barcodes
      - select all barcodes from Canopy table "IC_ProductUnitsOfMeasure" for items selected in #5 above.
      
   7. Prices
      - select all prices from Canopy table "OE_PrecalculatedPrices" for customers and items selected above.


B. Text files created for Canopy processing

   When new or delivered orders are uploaded from handhelds to the server, the ForceField sync script creates textfiles for processing by Canopy.
   
   For uploaded orders, two types of textfiles are created:
   
   1. An "order textfile" is for presales orders (new orders taken in the field and not yet delivered). These have 4-digit handheld order numbers (like "1001").

   2. A "lot detail textfile" is for delivered orders (no matter how they were originated). Their "order numbers" are either Canopy invoice numbers (for presales orders being delivered), or handheld order numbers (for peddle route orders that are created and delivered in one step).
   
   These textfiles are created into two different folders for WCG, and ditto for EVD (four folders in total).
   
   Then the Canopy program:
   1. Reads each new textfile in each folder
   2. Uses the file contents to update the Canopy database
   3. Renames the textfile (as described below)
   
   TEXTFILE NAMES AND CONTENTS:
   
   1. Order textfiles.

   1.1 Order textfiles are created in these folders for WCG and EVD:
       C:\inetpub\wwwroot\Jargon_WCG\upload\Orders  (new WCG orders)
       C:\inetpub\wwwroot\Jargon_EVD\upload\Orders  (new EVD orders)
   
   1.2 Order textfile names:
       When created by the ForceField host script, these files are named as 'Ord-RR-NNNN.txt', where:
         RR   = route# (handheld user ID)
         NNNN = handheld order#
       Example: "Ord-5-1092.txt"

       After Canopy has processed a file, it renames it by prefixing the date/time processed to the name as 'YYYYMMDDHHMMSS-Ord-RR-NNNN.txt' where:
         YYYYMMDD = date processed (year/month/day)
         HHMMSS   = time processed (hour/minute/second, using 24-hour military time)
         RR   = rep#
         NNNN = handheld order#
       Example: "20130422151501-Ord-5-1092.txt"

   1.3 Order textfile contents:
       There is no "order header" line. Each line in the textfile is for an order "line item".
       Note that Canopy gets the order number from the textfile name, not from the contents.

       Fields on each line (separated by tabs) are:
         - Customer Number
         - Customer PO# (normally RR-NNNN where RR=rep NNNN=handheld order#)
         - Item Number
         - UPC Number (optional)
         - Quantity (no minus signs)
         - Alternate UOM (optional)
         - Override price (optional)
         - Line item discount description
         - Line item discount percentage
         - Order type (1=charge 2=credit)
         - Order date (MM/DD/YYYY)
         - Requested delivery date (MM/DD/YYYY)
         - Line item special instructions

   2. Lot Detail textfiles.

   2.1 Lot Detail textfiles are created in these folders for WCG and EVD:
       C:\inetpub\wwwroot\Jargon_WCG\upload\Lots    (delivered WCG orders)
       C:\inetpub\wwwroot\Jargon_EVD\upload\Lots    (delivered EVD orders)

   2.2 Lot Detail textfile names:
       When created by the ForceField host script, these files are named as 'Lot-RR-NNNN.txt', where:
         RR   = route# (handheld user ID)
         NNNN = handheld order#
       Example: "Lot-5-1092.txt"

       After Canopy has processed a file, it renames it by prefixing the date/time processed to the name as 'YYYYMMDDHHMMSS-Ord-RR-NNNN.txt' where:
         YYYYMMDD = date processed (year/month/day)
         HHMMSS   = time processed (hour/minute/second, using 24-hour military time)
         RR   = rep#
         NNNN = handheld order#
       Example: "20130422151501-Lot-5-1092.txt"

   2.3 Lot Detail textfile contents:
       Note that Canopy gets the order number from the textfile name, not from the contents.
       The first line is an order header line. Fields are separated by commas.
       The contents are different for presales orders vs peddle orders:
         - Presales orders: #NNNNNN, CCCCCC, PO
         - Peddle Orders:   #, CCCCCC, PO
       where:
         NNNNNN = Canopy invoice number (not present on peddle orders)
         CCCCCC = customer number
         PO     = PO# (normally RR-NNNN where RR=rep NNNN=handheld order#)

       The second and following lines in the textfile contain order line item data.
       The information is in fixed width columns with no delimiters as 'IIIIIILLLLLLLLWWWWWW', where:
         IIIIIII  = Item number (6 char)
         LLLLLLLL = Lot number  (8 char)
         WWWWWW   = Weight      (6 char assuming 2 decimals)


C. Emails

   Send emails to following addresses if an error occurs during uploads:
   - EVD: evdhandhelds@wisconsincheesegroup.com
   - WCG: wcghandhelds@wisconsincheesegroup.com
   
   Emails are delivered from:
   - User: wcg@jargonsoft.com
   - Host: mail.jargonsoft.com
   - Userid and Password: secured (see values in the jsi_config.php file for that division)


D. Web Browser Reports

   Reports from the ForceField repository database are run from and displayed in a web browser.
   Users can print reports from the web browser if desired.

   1. If the parameter "REP_ENFORCE_LOGIN" is set to "true" in jsi_config.php, the userid and password values are enforced in order to access the reports menu (validated against USERS.USERID and USERS.USERPW in ForceField repository database).
   
   2. Administrator user status is set by value of USERS.IS_ADMIN field ("Y" or "N").
   
   3. Reports menu:
      - Order History Report
      - Order/Invoice Print
      - Lot Detail Report
      
   4. Order History Report

      Display summary or detail report of all orders matching selections in web browser.

      Report selections are:
      - From/Thru Sales Rep (Admin mode only)
      - From/Thru Customer No.
      - From/Thru Order Date
      
       Contents include:
         - Sales rep
         - Customer number
         - Customer Name
         - Order date
         - Order number
         - Item number
         - Quantity sold
         - UOM
         - Order amount
         - Normal amount
         - Override percentage
         - Payment amount
         - Payment type
         - Payment reference

   5. Order/Invoice Print
     
      Creates a report of uploaded order/invoice documents (with signatures) that users can generate and then print via web browser.
      
      The report includes the uploaded HTML order/invoice print documents and associated signature files for all orders in the ForceField respository database that match the report selections in the web browser.

      Report selections are:
      - Order Status
      - From/Thru Sales Rep (Admin mode only)
      - From/Thru Customer No.
      - From/Thru Order Date
      
      Order Status selection:
         Select all records in FORCEFIELD_ORDHEAD where:
         (a) 'New Orders':       FORCEFIELD_ORDHEAD.HOST_STATUS != 'Presale'
             These are new orders taken in field. (Not presales orders downloaded from host).
             SALESREP will be the field sales rep.
         
         (b) 'Filled Orders':    FORCEFIELD_ORDHEAD.UDF1 = 'N'
             These are orders filled by the warehouse, not signed.
             SALESREP will be the warehouse user.
      
         (c) 'Delivered Orders': FORCEFIELD_ORDHEAD.UDF1 = 'Y'
             These are orders delivered to customer and signed by customer.
             SALESREP will be the delivery user, or sometimes the warehouse user.
      
      Apply from/thru for User ID, Order Date, and Customer No. as usual.
         - skip if both are blank
         - if "thru" is blank, set "thru" = "from", so that both have values
         - select range of values if both are non-blank
         
      For each order found by this select, get SALESREP & ORDNUM values.
      
      Search the two folders specified in jsi_config.php for order upload files.
         Search in the following folders for a file named 'OA_[SALESREP]_[ORDNUM].htm'
         EVD:
           C:/inetpub/wwwroot/Jargon_EVD/upload/Sigs/
           C:/inetpub/wwwroot/Jargon_EVD2/upload/Sigs/
         WCG:
           C:/inetpub/wwwroot/Jargon_WCG/upload/Sigs/
           C:/inetpub/wwwroot/Jargon_WCG2/upload/Sigs/

      If above file is found in one of the folders, read it and include the  contents on the report, and if it is "Delivered", also include links to any signature files for this order that are found in the same folder.
         Delivery sig: 'Sig-[SALESREP]_[ORDNUM].bmp'
         Payment sig:  'Sig-[SALESREP]_[ORDNUM]-Cust.bmp'
         
         Otherwise, add an error message to the report that the report file is missing.
         This means that there was a file upload error for this order, or else that somebody removed the file after it was uploaded.
      
      Note:
      The reason the report is designed to print from uploaded files (instead of from the contents of the FORCEFIELD database tables) is due to the requirement to print signatures (if they exist), for "proof of delivery" to the customer.
      To print sig files, they must be on the server. If they were uploaded, then the corresponding order/invoice textfile should also be on the server.
      
   
   6. Lot Detail Report
   
      Creates an HTML based Lot Detail report that users can generate and then print via web browser.

      Report selections are:
      - Rep (Route) number
      - Item number
      - Lot number
      - Customer number
      - Invoice date
      - Order number
      
      Contents include:
      - Rep ID
      - Customer Number
      - Customer Shipto Location
      - Order date
      - Order time
      - Order number
      - Item number
      - Lot number
      - Weight
      - Quantity

E. Order upload rules for handhelds
   
   See the attached PDF file "OrderProcessingRules_rev1.pdf" for the rules that control when new and delivered orders can be uploaded from a handheld to the server.
 

 

Article Details

Article ID:
185
Category:
Date added:
2013-04-24 11:34:13
Views:
7,478
Rating (Votes):
(512)