The client receives Sales Order information from a web order entry system. Web order data is currently emailed and manually keyed into Macola as a new OE Sales Order. You would like to eliminate the re-keying process and automate the data entry by directly importing the data into Macola sales orders.
A MS Access 2000 application will be written to import Sales data, create new customers as necessary, validate the items and automatically generate a Macola Sales Order for these items.
The application will present the user with a start-up form.
An Admin command button will expose a maintenance form.
These settings will specify the database logon information, the location from which to import and the location of the Archive folder into which the imported file(s) will be placed after they are processed.
Data will be sent to Burton Golf in the form of a standard 'CSV' (Comma Separated Values) text file. A comma will separate field values and all text values will be enclosed in quotes. Each record will be terminated with a carriage return and line feed.
- Note 1: The creation of this file is not a part of this specification or quote. It is assumed that the file will be developed by those responsible for the Web development.
When the 'Import' command is selected, the application will import the first (and only the first) text file found in the 'Import file path' folder.
The application will import data from the text file formatted as follows:
Order header Record:
Order Line Item Record
The file may contain records for multiple orders.
All orders are imported using the same Macola Customer No ('999999999999'), with different Bill To and/or Ship to addresses as specified in the Import file. The application will create a new Alternate Address code for each incoming order.
The incoming data will be validated and the user will be presented with a 'view' screen.
The user may view the order details, print the order or delete the order. The user cannot make changes to the data, but may delete individual lines that are not valid to facilitate entering the order to Macola.
Changes to the data may be made from within Macola OE after the import has completed.
After deleting lines, the 'Validate' command (main menu) will reevaluate all line items and update the 'Status' of the entire order. Header and all line items must be 'Valid' before the order may be sent to Macola.
Header and line items will be validated as follows:
1. Customer No '999999999999' must exist in the Macola Customer Master file
2. Tax Code must be valid
1. Item must be exist and be Active in the Item Master file
2. Item must be exist and be Active in the Item Location file
Send To Macola:
The 'Send To Macola' command will be enabled when all line items are validated. A confirming message will be displayed.
For each order found in the import file, a new Sales order will be created in Macola as follows:
Order Type Always "O"
Order No From Import File
Order Date From Import File
P/O No From Import File
Ship Date From Import File
Customer No From Import File (Always '999999999999')
Bill To Name From Import File
Bill To Address From Import File
Ship To Code From automatically created Alt Address Code (see above)
Ship To Name From Alt Address
Ship To Address From Alt Address
Ship Via From Customer Master 'Ship Via'
Job No Blank
AR Terms From Customer Master 'Terms'
Location From Customer Master - Default Location
Disc Pct Always 0
Order Taxable From Import File 'Taxable' Flag
Def Form From Customer Master 'Default Invoice Form'
Tax Code From Import file
Salesperson From Customer Master 'Salesperson'
Ship Instruction 1 From Import File
Ship Instruction 2 From Import File
User_Def_Fld_1 Web_Ord_No from the Import File
User_Def_Fld_2 Import Filename from which this order originated
All other fields use Macola default value.
Item_No From Import File
Item Description From Import File
Loc From Customer Master - Default Location
Requested Date From Import File - "Ship Date" (Default is current date)
Promised Date From Import File - "Ship Date" (Default is current date)
Required Ship Date From Import File - "Ship Date" (Default is current date)
Unit Price From Import File
Unit Cost From Inventory Location file
Qty Ordered From Import File - "Qty"
Qty To Ship From Import File - Always equal to Qty Ordered, regardless of availability
All other fields use Macola default values.
In addition to creating the Header and Line item records, the application will allocate inventory (for 'Controlled' items), create an OE Order Inquiry summary record and create OE Header and Line Audit records.
Features and/or functions found on the Macola OE screen (Kit Items, Feature Items, Sales Commissions, Tax Schedules, etc) that are not specifically detailed in this document are not supported in this application.
Upon completion of processing, the user will be notified via a standard message box.
When order processing is complete, the import text file will be moved to an 'Archive' folder (see Admin above for file path). The filename will be appended with a date/time stamp to insure a unique filename in this folder. Archived files may be renamed and moved back into the Import folder for processing again if necessary.
|Languages||Exact Product Line|
|Development Platform||Modules Impacted|