ST Data Conversion
Overview
The SubTracker Conversion (STUTZZ) process was created so that clients can convert their data to BusinessPlus SubTracker database tables. The process works based on a spreadsheet concept with column headings that tell the system what data is being converted. Data for one or more tables is contained in a file and is processed one at a time for maximum control. Since SubTracker is part of the Human Resource Subsystem, employee data can be set aside for testing purposes and not updated by the conversion process. To prevent updates, enter TEST in the Selection Code 2 field of the Employee Master Record. To make this employee updateable, delete TEST out of that field.
Spreadsheets
The conversion spreadsheet gives maximum flexibility to the generic process. The key to the spreadsheet is row 1 in which the name of the information contained in that field column exists. These names match the name of the column in the database table as well as the online help field descriptions on each page. Some features to the process:
The columns do not have to be in any specific order. The process will handle special features based on the column heading of that field which is why it is so important (row 1).
The name of the spreadsheet is the name of the table the information is being inserted into.
The columns are color coded as to the importance of the data. Red columns must be in the data file whether inserting or updating. Green columns must be there if inserting data. Gold columns are information needed / recommended by our consultants. White columns are optional. All colors other than red are not necessarily needed in the file in order to process it.
There are two tabs per spreadsheet file: the first tab is for the data, the second tab has the descriptions of the field columns. The page and tab to find this data is also contained in the field description tab as well as what type of data, length of text fields, how many digits follow the decimal point and any comments, defaults and recommendations.
The process will decipher the column heading row. It will handle dashes as well as lower- and upper-case letters to adjust the field name to what it needs to be. If a field name specified is not one within the table, the process will display the error, but will still update/insert the data information for all other columns unless that one wrong column is a required one (red).
Dates – Eight different date formats are available. The only requirement is that all dates within a file are the same format. The user will specify that format and the program will modify the date to fit the correct format that the database table will accept.
Numbers and Amounts – The program handles all decimal options. It will calculate what type of number the data is, validate that it is a number, then format it to the correct form to be accepted by the database table. This is on a field-by-field basis. The only exception is that no dollar signs ($) are permitted.
The data file can be delimited three different ways: TAB, COMMA and PIPE. This is on a file by file basis. If a data file has numerous number fields with commas in the numbers, TAB-delimited works better than COMMA.
When updating information, if a blank data field is found, the table will clear that field data as well.
Some data fields have been kept off of the spreadsheet columns to minimize data clutter. These fields are usually standard data that is the same for all records. Based on this, if the process does not find that column field name, it will take the preset default. These fields are defined in the spreadsheet field description tab and are dependent on the database table. This default option can be overridden by manually inserting the column into the spreadsheet.
Sorting of a spreadsheet is usually based on the red columns and is specified on the field description tab. This is essential at times to find duplication of data or to be sure data is grouped properly for processing.
Tables
Tables are in update and insertion mode only. No purging of data will be done unless specified.
01 - STIMPORT
1 record per entity-id, job-id, absence-dt and filename.
There must be either one of these groups defined: SUB fields or ABS fields, but usually not both.
The sub-id or abs-id field value will be modified to fit specifications defined in the common code. It will then be processed through the sub-idtran table to find the BusinessPlus equivalent employee id. If not found, then the field value will be placed as the employee id and will be validated in the HREMEN (hr-empmstr) page.
Defaults:
ENTITY_ID = ROOT
ABSENCE-DT = current date
FILENAME = input file name
POSITION = X
Common Code Options
The Common Code to run the process without the questions and with more options is STRL/ROLLOVER.
How to Run the Process
Before running the process, the tail sheet will need to be set up to be archived so that it can be looked at later for statistics, totals and error reports.
There are two options to running ST Conversion under STUTZZ:
01 - Generic – Question Launch – This option uses questions to run the process. It has fewer features than the Common Code Launch. The questions are for the table name, name of the data file, length of the Employee ID, delimiter type, date format type and debug on or off.
02 - Generic – Common Code Launch - This option uses common code STRL/ROLLOVER to set up the process.