SI Data Conversion

The SI Data Conversion process can be used by new customers to convert Stores Inventory Data from their legacy system to BusinessPlus. The process works based on a spreadsheet concept with column headings that tell the system what data is being converted. Information for each table is contained in a different file and is processed one at a time.

The process BEGBAL creates a set to be distributed by usual SI processes to establish a "beginning balance" situation to the subsystem, which is different than the usual spreadsheet to table collation.

There is no field for the Fully-Qualified-Account number. To specify this feature:

Enter the FQA in the GL-KEY field
Include GL-OBJ, JL-KEY, and JL-OBJ columns in the spreadsheet but leave the fields blank (if not using the JL ledger, leave these columns off the spreadsheet).

The process deciphers an FQA and uses it to fill in the column information specified in the spreadsheet.

Spreadsheets

Spreadsheets add 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.

Here are some features to the process, on a file-by-file basis:

The columns do not have to be in any specific order. The process handles special features based on the column heading of that field (row 1).
The name of the spreadsheet is the name of the table the information is being inserted into, except for the BEGBAL process, which uses tables SIB-BATCH-MSTR and SIR-BATCH-DTL.
The columns are color-coded as to the importance of the data. All colors other than red are not necessarily needed in the file in order to process it.
1. Red columns must be in the data file whether inserting or updating.
2. Green columns must be there if inserting data.
3. Gold columns are information that is needed/recommended by our consultants.
4. White columns are just other optional information.
Verify each column appears in the upload file only once. If there are duplicate columns, the Insert will not happen, regardless if the tail sheet says it did.
There are two tabs per spreadsheet file. The first tab contains the data. The second tab contains the descriptions of the field columns. The screen and tab to find this data is contained in the field description tab as well as what type of data, length of text fields, how many digits follow the decimal point, any comments, defaults, and recommendations.
The process deciphers the column heading row. It handles dashes as well as lower and upper-case letters to adjust the field name. If a field name specified is not in the table, the process displays the error. It still updates or inserts the data information for all other columns unless that one wrong column is a required one (red).
Dates – Eight different date formats are available for use. The only requirement is that all dates within a file are the same format. Specify the format and the program modifies the date to fit the correct format that the database table accepts.
Numbers / Amounts – The program handles all decimal options. It calculates 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. 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. Note: If a data file has many number fields with commas in the numbers, TAB delimited works better than COMMA.
The table clears the field data if a blank field is found when updating information.
Some data fields are 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. To override this default option, manually insert 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 data is purged unless specified.

BEGBAL (Insert Only)

SIB-BATCH-MSTR is inserted. 1 record per Set ID. All fields in this table are derived from the process.
SIR-BATCH-DTL is inserted. 1 record per input record.
The SIR-BATCH-DTL table pruges all set entries before reinserting them.
Sorted by SIR-BATCH-ID.
Defaults:
- SIR-BATCH-ID = can come from the Common Code
- SIR-GL-GR = User GL Code
- SIR-JL-GR = User JL Code
- SIR-GL-KEY, SIR-GL-OBJ, SIR-JL-KEY, SIR-JL-OBJ = can be derived from and FQA
- SIR-TRNS-FORMAT = "NB"
- SIR-POST-CD = can be derived by the system

Common Codes

Common code SIRL/ROLLOVER runs the process without the questions with additional options. This code is required by the SIUTZZ01 utility that imports data into SI tables from flat files.

How to Run the Process

Before running the process, set up the tail sheet to be archived so that it can be looked at later for statistics, totals and error reports.

There are two options to running SI conversion:
SIUTZZ01 — Generic Common Code Launch — This option uses the common code to set up the process. It asks three basic questions:

Which table information to convert?
What input file to use?
Whether or not to run the conversion in trial mode (debug).

All other information comes from the SIRL/ROLLOVER common code.

Locate the file to use for the conversion. After the file is selected, the question field displays the path to the file and is greyed out.
The remote upload may not be able to handle extremely large input files. Check the tail sheet to verify how many records were read/updated/inserted. If the input file is too large, either split the file up into smaller files or place the file on the App server in the /admin/data directory, which can handle larger file sizes. On the App server, the file name can be no longer than eight characters, all letters in uppercase, and no file extension.
The default location of the input file question field is the /data/admin directory on the App server. If a file name is entered here instead of using the upload/browse feature, the system will look for that file on the App server when the utility is run.
For Trial (Debug) Mode: "YES" will have the process handle all the error checking, but not actually insert or update the table. "NO" inserts/updates the table.
Submit the file first in Trial Mode to evaluate for potential errors needing to be resolved. Inspect the tail sheet for the count of records read and any records that may have failed. If the trial tail sheet looks correct, uncheck the Trial Mode box (change to N) and then submit again. After the job completes, open the targeted mask to view the inserted record(s).

SIUTZZ02 — Custom Conversion — Use this option if the programmer writes and sets a custom program