Articles

Importing Salary Schedule Records

This FYI describes the requirements and use of a Salary Schedule Import program for Escape Personnel/Payroll. The program is run from a DOS prompt, and creates salary schedule records based on information in the import file.

Salary Import Program

Below are the steps for importing salary records. They are described in detail in the following pages.

  1. Create the import file.
  2. Backup your files.
  3. Set clavm0.
  4. Run the program.

NOTE: All users must be out of the PAY and PSL modules before you begin importing salary schedules. If there are users in the system, a red box reporting insufficient space will display, and the program will abort in the middle of the process.

Step 1 - Create Import File

The first step is to create the import file. The file is comma delimited. It must have a CSV extension and reside in the \ESCAPE\IMPORT directory. It should contain the following fields:

Field Length Description/Notes
Org Id 3 Must match database
Sal Sched code 8 For example, A1, TT, CL
Description 40
Begin date 10 mm/dd/yyyy
Time Unit 1 M(onthly), A(nnually), D(aily), H(ourly)
Days per Year 6 999.999 (decimal is implied, only use if time unit is A or M)
Salary Movement 1 H(orizontal) or V(ertical)
Row 3 For example, 001, CL1, 020
Amount 999999.99 35 occurrences

Import Functionality

The import file can contain multiple org id values. The import will only process records where the org id matches the database's org id. The import will create a new salary schedule record when the salary schedule code value in the import file changes.

Field values placed in Salary Schedule Record

  • Salary Schedule Code
  • Description
  • Begin Date
  • Time Unit
  • Days/Year - use this field only if the time unit is A or M

Fields set according to the following logic

  • Row Alpha - always set to Y.
  • Pay Prd % - set to Y if the time unit is M. Otherwise, leave blank.
  • How Many Row - set to 1 if the movement field value is V.
  • How Many Column - set to 1 if the movement field value is H.

Fields left blank

  • End Date
  • Override Date
  • Col Alpha
  • Max Value Row
  • Max Value Column

Row/Column/Salary Rate/Description Area

The row value is set from the row value field. All columns for an existing row will be replaced with this new data. Escape numbers the columns according to the order they are found in the record. The first amount field will be column 1, and the amount will be placed in the salary rate field of the record. The second amount field will be placed in column 2, and the amount placed in the salary rate field. If the field value is 0, the column/rate will not be set, but the column number will increment. For example, if the first 15 amounts are zero or blank, the table will begin with column 16.

To help you get a visual of how this process takes place, we have created a sample import file, translated into a spreadsheet format, and finally what that file would look like in Escape.

The following is a sample from an import file. Column 1 is bold to help you see how the columns are defined.

200,10,Cert Management,07/01/2000,A,184000,H,001,0.00,49410.00,52502.00,55569.00,58568.00

200,10,Cert Management,07/01/2000,A,220000,H,002,96106.00,100015.00,103923.00,107835.00,111746.00

200,10,Cert Management,07/01/2000,A,220000,H,003,148459.00,0.00,0.00,0.00,0.00

Now we see what that same information would look like in a spreadsheet format. Notice the blank cells. Any time an import file has a blank or 0.00, the cell will be left blank.

  Col 1 Col 2 Col 3 Col 4 Col 5
Row 1   49410.00 52502.00 55569.00 58568.00
Row 2 96106.00 100015.00 103923.00 107835.00 111746.00
Row 3 148459.00        

Finally, this is what that same information would look like in Escape. Notice how Row 1 starts at Column 2, and how Row 3 only has information in Column 1.

001 2 $49,410.00
001 3 $52,502.00
001 4 $55,569.00
001 5 $58,568.00
002 1 $96,106.00
002 2 $100,015.00
002 3 $103,923.00
002 4 $107,835.00
002 5 $111,746.00
003 1 $148,459.00

Here is a sample file for you to review.

Step 2 - Backup Your Files

You should backup the following files before importing your data. This gives you the ability to restore the files back to their original form in case there are any problems with the import file or process.

  • PAYSALS.DFF
  • SSFINFO.DBF
  • SALSCH.*

Step 3 - Set clavm0

From the DOS command prompt, type "set clavm0=c:\escape\virtual, 1024" (those are zeroes not letter o). This sets the Clarion virtual memory. If this step is not done, the import will halt after processing hundreds of records, and display a red box with error code 8, Insufficient Memory.

Step 4 - Run SSIMPORT.EXE

Run SSIMPORT.EXE from the COMMAND prompt with the import file name (with no file extension) as a parameter. The file extension has been hard coded to be ".CSV". 

SSIMPORT  Salaries

where salaries is the name of the file with an extension of ".CSV" (i.e., "Salaries.CSV") in the \ESCAPE\IMPORT directory.

The first three lines display immediately and the "Processed:" shows a running status. The last two lines are summary statistics, and they will appear when the program completes.

    Locating salary records for this organization (618 )
    Located 769 salary records. We will now begin to import the data
    Processed: 23 11-D 008
    Finished updating PaySals File.
    SSIMPORT SUMMARY: Read = 769 Updated = 769 Errors = 0

Your original import file will be renamed to have an extension of ".CMP".