Import Sales Orders

Solution Categories

Automotive | Chemical Manufacturing | Computer & Electronic Equip. Manufacturing | Construction | Education | Finance, Insurance and Real Estate | Food & Beverage Manufacturing | Government | Life Sciences | Machinery Manufacturing | Metal Fabrication | Not for Profit | Other Manufacturing | Other Services | Paper & Printing | Professional, Scientific, and Technical Services | Retail Trade | Telecommunications | Transportation and Warehousing | Utilities | Wholesale Distribution

Import Sales Orders

Overview:

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.

Proposed Solution:

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.

Detail:

The application will present the user with a start-up form.

ADMIN

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.

Import Data

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:

Field Data Type Desc Notes
Record_Type Char (1) 'H' for header
Web_Ord_No Char (8) Order No Order No from Website for tracking
Ord_Dt Int Order date
OE_PO_No Char (25) Customer PO No
Cus_No Char (12) Customer No Always '999999999999'
Bill To Name Char (40) Customer Name
Bill_To_Addr_1 Char (40) 1st address line
Bill_To_Addr_2 Char (40) 2nd address line
Bill_To_Addr_3 Char (40) 3rd address line
Ship_To_Name Char (40) Ship To name
Ship_To_Addr_1 Char (40) 1st Ship To address line
Ship_To_Addr_2 Char (40) 2nd Ship To address line
Ship_To_City Char (26) Ship To City
Ship_To_ St Char(2) Ship To State
Ship_To_Zip Char(10) Ship To Zip Code
Ship_To_Ctry Char(20) Ship To Country
Ship_To_Contact Char(40) Ship To Contact Name
Ship_To_Phone Char(20) Ship To Phone No
Ship_To_Email Char(40) Ship To email address
Ship_To_Terr Char(2) Ship To Territory
Ship_To_UDF1 Char(30) Ship To User Defined Field 1
Ship_To_UDF2 Char(30) Ship To User Defined Field 2
Ship Date Int Requested Ship Date
Ship_Instr_1 Char (40) Shipping Instructions Line 1
Ship_Instr_2 Char (40) Shipping Instructions Line 2
Taxable_Fg Char(1) Taxable status 'Y'es or 'N'o - Applies to this order only
Tax_Cd Char(3) Tax Code Applies to this order only
Ship_Via Char(3) Ship Via Code
Frt_Chg Decimal(9,4) Freight Charge
Misc_Chg Decimal(9,4) Miscellaneous Charge Handling charge

Order Line Item Record

Field Datatype Desc
Record_Type Char (1) 'L' for Line Item
Web_Ord_No Char (8) Order No
Line_Seq_No Int Line Sequence No
Item_No Char (15) Item No
Item_Desc_1 Char (30) Item Description Line 1
Item_Desc_2 Char (30) Item Description Line 2
Qty Ordered Decimal (9,4) Qty Ordered
Unit Price Decimal (9,4) Unit Price If not populated, default 'Price' from IM Location file will be used

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.

  • Alternate address code will be an incrementing number. It will be automatically assigned using the next available number.
  • Fields beginning with 'Ship To' in the detail above will be used to fill the corresponding fields in the Alternate Address record.
  • The newly created Alternate Address code will be used to populate the 'Ship To' code on the Order Header.

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.

Note:

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.

Validate Data:

Header and line items will be validated as follows:

Header:

1. Customer No '999999999999' must exist in the Macola Customer Master file

2. Tax Code must be valid

Line Items:

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:

Header:

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'

Col/Prepaid Blank

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.

Order Lines:

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

  • Note: No Backorders will be created

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.

File Archive:

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.
General Information
  • Live Implementation: 1-5
  • Customer References Available: No
  • Maintenance Plan Available: No
  • Support Plan Available: No
  • Technical Documentation: Yes
  • User Manual Documentation: No
  • Marketing Literature: No
  • Suggested Retail Price: $5,400.00
  • Available for Resale: No
  • Primary Contact Person - Sales: This e-mail address is being protected from spambots. You need JavaScript enabled to view it - Tel: 800-468-0834 Ext.2650
  • Primary Contact Person - Technical: This e-mail address is being protected from spambots. You need JavaScript enabled to view it
  • Web-Site/URL: www.exactamerica.com
  • Information Last Updated: 2006-09-20
Languages Exact Product Line
  • English
  • Exact Macola ES
  • Exact Macola Progression
Development Platform Modules Impacted
  • Other
  • Order Entry
Database Supported Version
  • MS SQL 2000
  • Pervasive PSQL
  • N/A
Industries Horizontal Focus
  • Automotive
  • Chemical Manufacturing
  • Computer and Electronic Equip. Manufacturing
  • Construction
  • Education
  • Finance, Insurance, & Real Estate
  • Food & Beverage Manufacturing
  • Government
  • Life Sciences
  • Metal Fabrication
  • Machinery Manufacturing
  • Not for Profit
  • Other Manufacturing
  • Other Services
  • Paper & Printing
  • Professional, Scientific, and Technical Services
  • Retail Trade
  • Telecommunications
  • Transportation and Warehousing
  • Utilities
  • Wholesale Distribution