DataLoad Programs Version : 1.0
Date: 02/12/03
DataLoad Programs
For the Implementation of
MAPICS SyteLine 7.03
…………………………..
Prepared by:
Reviewed by:
Document Ref:
Issue Date: 03 December 2003
Issue Number: v1.0
Supersedes:
Status: for review
INDEX
INDEX 2
INTRODUCTION 4
ASSUMPTIONS AND CONSTRAINTS 4
How to Use 4
Key points to know 6
EXAMPLE 8
Syteline tables for DataLoad: 9
ARIMPORT Table 11
AR Invoices, Credit & Debit Memos 11
ARPMIMPORT Table 13
AR Payment 13
COIMPORT Table 15
Customer Orders 15
COLINEIMPORT Table 16
Customer Order Lines 16
COLINENOTESIMPORT Table 17
CONOTESIMPORT Table 17
CUSTOMERIMPORT Table 18
Customers 19
CUSTOMERNOTESIMPORT Table 19
ITEMIMPORT Table 20
Items 21
ITEMLOCATIONIMPORT Table 22
Item/Stockroom location 22
ITEMNOTESIMPORT Table 22
Material 23
POIMPORT Table 24
PO (Purchase Order) 24
POLINEIMPORT Table 25
PO Line 25
POLINENOTESIMPORT Table 26
PONOTESIMPORT Table 26
ROUTEIMPORT Table 27
Current Operations 27
VENDORIMPORT Table 28
Vendor 29
VENDORNOTESIMPORT Table 29
Zip file Content: 30
END OF REPORT 31
INTRODUCTION
This Document has been compiled to help in loading data into Syteline 7 for a new implementation of Syteline 7.XX. This tool is not intended for use to upgrade from earlier versions of Syteline.
The DataLoad forms part of the Implementation Preparation phase of the FOCUS Methodology.
ASSUMPTIONS AND CONSTRAINTS
Go Live Date is Set for ………………..
To be able to set up the DataLoad parameters in the correct sequence some Code tables have to set up as well. The completion and checking of the Parameters and Codes is a pre requisite. The DataLoad Programs are used to load data from your old System into Syteline 7. It is your responsibility to extract data from your old system and create the tables used in this DataLoad. Dataload.exe is an executable that will use SQL to populate the Syteline database. The recommended sequence to load the Syteline data base(s) is in a nutshell the “normal” business flow. This will be described in chapter Syteline Tables for dataLoad of this document. Dependent on the use of single or multiple sites this can become a fairly complex matter. Careful planning and sufficient preparation and documentation are the key to success.
How to Use
You have been supplied with SL7Dataload.zip This file contains all the SL7 Dataload programs.
You need to create a folder (EG. SL7-Dataload on the Database Server ………..) and unzip the programs into this folder.
To run, double click on the 'Dataload.exe' which will prompt for :-
Server – Enter the name of the SQL server. Couple of ways to find it: Open Query analyzer and it will have the list of servers for you log into or if it says ‘local’ log into Query analyzer and on title of the window you should see the server name and then the name of the database name. For e.g. catbert\sl702.MI_App.sa is in my title. In this example, catbert\sl702 is my server (or server instance), MI_App is the database name and sa is the user name that I used to log on. If all this is greek to you then contact your customer’s IT person.
Password – Password of the user sa (system administrator account of SQL server).
Database – The name of the SQL database in which you will be loading data into. For e.g., MI_APP, OH_APP, etc.
Table – Choose the table that you want to load the data for. Right now, the following tables are available:
CO (Regular Customer orders),
Coline (Customer order lines),
Customer,
ItemLocation (Item/Stockroom location with or without lots),
Material (Current Materials),
PO (Regular Purchase Orders),
PoLine (Purchase Order Lines),
Route (Current Routings), and
Vendor
Path and File name – Enter the name of the data file with extension including it’s entire path. For e.g., d:\users\vairam\customer.txt.
Delimiter – Delimiter can by any printable character including #, |, $, etc. Basically the character that separates the fields in the database.
Action - You can either Load new records, modify existing records (available only for customer, vendor and item tables) or load notes.
Delete Old Records? – Deletes already loaded records prior to loading new records.
Key points to know
· All the files that come with the tool need go into the same directory as your data file. Obviously, the tool should be able to see these files.
· The tool creates error files for every step (it does multiple steps seamlessly) in the same directory as your data file. If you see at least one file created after starting the tool with a size greater than 0 bytes it means you have error. Open the file to see what the error is – Most errors should be user-friendly.
· The number of fields and their order should match exactly as given in the corresponding .tbl file. These are common mistakes in the data file: The number of columns don’t match –nothing will get loaded, size of the fields is larger than specified – only those rows with that problem will not load, if not passing data instead of just passing the delimiter as the place holder passing space. So, if for example you don’t want to pass customer type then pass ,, (for , delimiter) and not a space like this , ,. Always, make sure that you are passing in the same order and the size of the fields is less than or equal to the size as specified in the .tbl file. Ensure all cost/price fields matches the format specified in the ‘Currency Codes’ form. Ensure all quantity fields matches the format specified in the ‘Inventory Parameters’ form. If they don’t then there will be SQL errors.
· The last but one field is notes. Notes can be loaded when loading the table or later as a separate load. If loading with the table then notes need to be one long string of several characters (most likely no one should hit the limit). If unable to send notes in the same file and/or unable to send it as one long string then use the separate load functionality.
· If not loading notes with the table then the last 2 entries (for each row) in your data file should have ,, (assuming , as the delimiter). If loading notes then the last 2 entries (for each row) in your data file would look like , this is my notes that I am going to load,.
· Some of the data load table have OLD and NEW fields. For Example in ARIMPORT Cust_Num and New_Cust_Num. The DataLoad programs do have the capability to keep or to convert your old numbers into Syteline 7 assigned numbers. See the parameter settings of the Prefixes. When you like to keep your existing numbering schema you feed the old number and store in the NEW field a NULL (No value). When you like to convert the old numbers into Syteline 7 conventions, you then Store the Old value in the first field and store in the second field a ‘TBD’ or ‘%?’ value. So: Old number = Number of your Old System New Number = becomes the Old number automatically or will be generated
· No value for a field does not mean blank. Remember blank is a value!
Any errors in the dataload will be output to an error file in the same folder.
EXAMPLE:
The program responds with the following message:
Rejected records get output to a separate .DAT file (eg. itemimporterr.DAT) Once errors have been fixed (maybe Product Codes or U/M were missing from SL7) this file can be renamed to, say, items2.txt and reloaded (you don't need to re-format the .....err.dat file in any way)
Error Messages are output to a .TXT files (eg. itemimporterr.TXT)
EXAMPLE
An example of data file for Items would be as follows :-
T-PART1|C|1|Alt Item text||A|F|10|Description for T-PART1|0|0|0||M|1|PLN|M|J|FG-100|1|S|EA|10|100|1|2|0|0|25|1|35|0|DRG1234||||
just need a value for each field in the .TBL file, seperated by a delimiter(we use "|" as it is not used in Descriptions etc)
Logicals are 1 for YES and 0 for NO
If a value is not passed (just || in that position) then the default in the .TBL file will be used.
Example of Item Notes (for the Item above) would be :-
T-PART1|My Subject|These are the notes for item T-PART1 |
Page 4
DataLoad Programs Version : 1.0
Date: 02/12/03
Syteline tables for DataLoad:
Syteline tables that can be loaded with the DataLoad tool provided that some pre requisite(s) are available:
The normal business flow should be followed, setting up Syteline 7 starting with the Parameters and codes. Create your company structure and Chart of accounts/financial structure. Followed by Engineering data, Production Control, Logistics, Purchasing/Procurement, Sales etc etc.
Alfabetical sequence
Table name/Form name / Pre Req / Pre Req / Pre Req / Pre Req / Pre Req / Seqarimport.tbl / Parameters and Codes / Customer / Customer Orders / Customer Order Lines / 5
arpmtimport.tbl / Parameters and Codes / Customer / Customer Orders / Customer Order Lines / Accounts Rec. / 6
coimport.tbl / Parameters and Codes / Customer / 2
colineimport.tbl / Parameters and Codes / Customer / Customer Orders / Items / 4
colinenotesimport.tbl / Parameters and Codes / Customer / Customer Orders / Customer Order Lines / 5
conotesimport.tbl / Parameters and Codes / Customer / Customer Orders / 3
customerimport.tbl / Parameters and Codes / 1
customernotesimport.tbl / Parameters and Codes / Customer / 2
itemimport.tbl / Parameters and Codes / 1
itemlocationimport.tbl / Parameters and Codes / Item / 2
itemnotesimport.tbl / Parameters and Codes / Item / 2
materialimport.tbl / Parameters and Codes / Item / Route / 3
poimport.tbl / Parameters and Codes / Vendor / 2
polineimport.tbl / Parameters and Codes / Vendor / Purchase Orders / Items / 4
polinenotesimport.tbl / Parameters and Codes / Vendor / Purchase Orders / Purchase Orde lines / 5
ponotesimport.tbl / Parameters and Codes / Vendor / Purchase Orders / 3
routeimport.tbl / Parameters and Codes / Item / 2
vendorimport.tbl / Parameters and Codes / 1
vendornotesimport.tbl / Parameters and Codes / Vendor / 2
Sequential
Table name/Form name / Pre Req / Pre Req / Pre Req / Pre Req / Pre Req / Seqcustomerimport.tbl / Parameters and Codes / 1
itemimport.tbl / Parameters and Codes / 1
vendorimport.tbl / Parameters and Codes / 1
coimport.tbl / Parameters and Codes / Customer / 2
customernotesimport.tbl / Parameters and Codes / Customer / 2
itemlocationimport.tbl / Parameters and Codes / Item / 2
itemnotesimport.tbl / Parameters and Codes / Item / 2
poimport.tbl / Parameters and Codes / Vendor / 2
routeimport.tbl / Parameters and Codes / Item / 2
vendornotesimport.tbl / Parameters and Codes / Vendor / 2
conotesimport.tbl / Parameters and Codes / Customer / Customer Orders / 3
ponotesimport.tbl / Parameters and Codes / Vendor / Purchase Orders / 3
materialimport.tbl / Parameters and Codes / Item / Route / 3
colineimport.tbl / Parameters and Codes / Customer / Customer Orders / Items / 4
polineimport.tbl / Parameters and Codes / Vendor / Purchase Orders / Items / 4
colinenotesimport.tbl / Parameters and Codes / Customer / Customer Orders / Customer Order Lines / 5
arimport.tbl / Parameters and Codes / Customer / Customer Orders / Customer Order Lines / 5
polinenotesimport.tbl / Parameters and Codes / Vendor / Purchase Orders / Purchase Orde lines / 5
arpmtimport.tbl / Parameters and Codes / Customer / Customer Orders / Customer Order Lines / Accounts Rec. / 6
Return to INDEX
ARIMPORT Table
(cust_num char(7) default null, / Customer Numbernew_cust_num char(7) default null, / Customer Number / NULL TBD or %?
inv_num char(12) default '0', / Invoice Number
new_inv_num char(12) default null, / Invoice Number / NULL TBD or %?
type char(1) default 'I', / Invoice / Invoice, Credit Memo, Debit Memo,
inv_date char(8) default null, / Invoice date / required
due_date char(8) default null, / Due date
amount char(21) default '0', / Invoice amount
disc_amt char(21) default '0', / Discount amount
description char(40) default null, / Description
disc_date char(8) default null, / Discount Date
misc_charges char(21) default '0', / Miscelaneous charges
sales_tax char(21) default '0', / Sales tax / VAT amount
freight char(21) default '0', / Freight Charges
bank_code char(3) default null, / Bank Code / Must match Bank table
tax_code1 char(6) default null, / Tax/VAT code / Must Match table
notes oleobjecttype, / A maximum of 1 note
AR Invoices, Credit & Debit Memos
The following tables should have records prior to loading AR Invoices: Customers
· The tool loads these records as if it is been entered in the A/R Invoices, Credit & Debit Memo form.
· It generates the distributions using default accounts (remember you are expected to delete the journal entries created by the tool). And then does the posting.
· There is a separate option to load payments.
· Customer number has 2 fields one for existing customer number and one for new customer number (if you are renumbering customer number in the new system). If not renumbering provide no value for the new number. It is expected that you have used the tool to load customers.
· Invoice numbers has 2 fields one for old invoice number and one for new invoice number (if you are renumbering invoice number in the new system). If not renumbering provide no value for the new number.
· Invoice numbers when null for type ‘I’ transactions the system will generate the next invoice number, for types ‘C’ & ‘D’ the invoice number will be set to 0 to indicate that these transactions are open.
· Reference and description when no value will get system generated reference and description.
· Type should be one of ‘I’nvoice, ‘C’redit memo or ‘D’ebit memo.
· Invoice date is required.
· If due date is left null then the tool computes due date based on standard logic.
· If sales tax is left with no value then the tool will compute tax. For no tax pass 0 for sales tax.
· Bank code if left blank will get customer bank code from the customer master.