SMS Data Files (Basic)
This basic approach to data extraction would focus on the creation of three separate files.  This eliminates the need to program and merge data fields into one complex file.  These three files can be supplied in ASCII format, delimited format, Excel format, Access format or other (if you have a special requirement just call).  The strict order of fields is listed below, but the field lengths can be variable and there is no limit to the number of records.  Each must be extracted at the same point in time so that they are synchronized with each other.  Three separate files should be created - not one spreadsheet with three pages.  Each must be in the formats defined below - not just a "dump" in some other format or field order.   Each should be named Detail, History and Orders respectively.  For delimited format use the ~ sign as a delimiter.
 
File One - Item Detail (one record per item per location)

Field Field Name Field Description Field Range Notes
1 Item Item Number, SKU 20 Alpha-Numeric  
2 Location Warehouse, Branch 20 Alpha-Numeric  
3 Description Long Description of Item

30 Alpha-Numeric

 
4 On Hand Available Stock on Shelf 8 Numeric 0 – 99,999,999
5 Back Ordered  “Owed” - unavailable 8 Numeric 0 – 99,999,999
6 Incremental EOQ Pack Size (i.e. pack of 10) 8 Numeric 0 – 99,999,999
7
Part Cost Item Cost 10.2 Numeric 0.01 – 99,999,999.99
8
Part Price Item Price 10.2 Numeric 0.01 – 99,999,999.99
9
Service Level Item "Criticality"  2 Numeric 88 – 99 (Default to 97%)
10 Carrying Cost Storage - % of total cost 2 Numeric 0 - 50
11 Lead Time # of weeks for delivery 2 Numeric 1 – 99 weeks
12 Ordering Cost Admin cost to place order 7.2 Numeric 0.01 – 99,999.00
13 Supplier Usually primary supplier 20 Alpha-Numeric Can be Product Grouping
14 MIN Qty to trigger an order 8 Numeric 0 – 99,999,999
15 MAX Max # of units to keep 8 Numeric 0 - 99,999,999

























File Two - Sales History
(one record per issue/sale - usually multiple issues per item)

Field Field Name Field Description Field Range Notes
1 Item Item Number, SKU 20 Alpha-Numeric  
2 Location Warehouse, Branch 20 Alpha-Numeric  
3 Issue/Sale Qty Transaction quantity 8 Numeric 0 - 99,999,999
4 Issue/Sale Date Transaction date 8 Alpha-Numeric MM/DD/YR or DD/MM/YR











File Three - Incoming Replenishments
(one record per order - multiple orders per item)

Field

Field Name

Field Description

Field Range

Notes

1

Item

Item Number, SKU

20 Alpha-Numeric

 

2

Location

Warehouse, Branch

20 Alpha-Numeric

 

3

Order Amount

Quantity ordered

8 Numeric

0 - 99,999,999

4

Delivery Date

Expected date delivered

8 Alpha-Numeric

MM/DD/YR or DD/MM/YR

5

P.O. Number

Order Number

10 Alpha-Numeric