Lesson 29 – Interfaces

| Monday 10, 2015

Interfaces

The interfaces module enables importing ASCII format data to Harmony tables, or exporting data from Harmony to TXT format files that can be viewed via Excel, email, FTP. The exportation option can be divided into two parts: the standard Output interface and the Payroll file interface.

The interfaces will be marked as follows:

Input interface (import)

Output interface (export)

 

The technology enables exportation in DTS (Data Transformation Services) technology provided by the SQL server. Using this technology it is possible to create a package that can perform activities on data files.

 

To open the Interfaces module open the System Def. menu and click the Interfaces option.

 

The Interfaces screen opens a screen will appear consisting of all of the interfaces in the system. During setup the icon appears.

Double-clicking the icon opens the following menu, described below.

image

New - Enables creating a new input/output/payroll/transaction interface or generating a project summary as follow:

  • Input Interface - imports ASCII files.
  • Output interface - exports to ASCII files.
  • Payroll file – generating file for payroll.
  • Transactions interface - imports Attendance and Access control transactions.
  • Project summary.

Update - Enables changing one of the A/M interface types.

Run - Runs the selected interface.

Save as - You can save the current interface including its parameters under a different name.

Delete - Enables deleting an interface.

Import external DTS - Imports external interfaces (not Harmony generated) to be activated within the Harmony system.

Backup interfaces - It is possible to update all interfaces. The software builds files and uploads a mail server to enable sending those files (a temporary generation of files to be copied, to be moved to FTP, mail, saved to file.

Harmony Operation and Installation ManualInterfaces

Generate SQL Script - For export purposes, generates an SQL script file from an existing interface.

Restore SQL Script - For import purposes, it is possible to restore an SQL script file.

 

Input interface

 1

General Tab

Interface Name - The name under which the interface file will be saved.

Separator - A sign separating between the different fields.

ASCII File Name - Use the Open screen to select the physical file from which data will be imported. It is also possible to manually fill-in the file path or write the path of another server.

Destination Table Name - The table to which data will be imported.

Override Destination Table Data - Determine whether existing data will be deleted.

Delete original ASCII File - Whether after the activity was performed the original ASCII file should be deleted. If you choose to delete it, the data file will be backed-up to a back-up folder as per your choice. It will be saved under its name + date and time of deletion.

Header - If the imported file has a header line, mark this box.

Interfaces Harmony Operation and Installation Manual

190
Properties Tab

 2

Fields List (B)

All the fields that comprise the specific file.
Selected Fields (C)

Key fields will be displayed here automatically. These fields must include a value and they are the link between various table types. It is possible to move additional fields to this section from the Fields List section.

Properties (D)

All details concerning each selected field is outlined here. You must mark the required field in the list, then the field name will appear under the right-hand screen under Properties |Name. Some of the data will be updated automatically: Name, column Number., Field Size

Alignment- Will the data in the field be aligned to the right or to the left.

Format- Designated only for fields containing date or time.

Merge Fields- Enables importing several fields from the source file to one field in the destination file. i.e.: In the employee file there is a “Name” field. In this field the First name and Family name are merged. You can drag from the column section to this field or write the column number manually, then add a “,” separator.

Fixed- Enables defining a fixed value for a field on the data fields which is not in the input file. i.e.: the Valid Date field for the employee file password.

 

Step 1. Using the mouse drag the relevant fields from the Fields List (A) to the Selected Fields (B).

Step 2.Each field in the Selected Fields section must have an equivalent field (to be dragged from the fields ruler). Then you must go to section D and define the relevant parameters.

Step 3.Click Save and exit.

Step 4.Right-click or double click the transaction to run and transmit the required file.armony Operation and Installation ManualInterfaces

Step 5.Go to the relevant data storage location (i.e.: Tables | s…)

 

Importing definitions for a Security Group assigned to an Employee

The following section describes the way in which to import definitions for security groups from a csv file defining the security groups and matching the definitions to Harmony definitions.

Creating the Security Group CSV file with Security group information:
3

To import the information to Harmony create a CSV file with the relevant information in Excel.

Step 1. Create an Excel file to import to Harmony containing all of the information defining the security groups to set in Harmony. The following information needs to be set in the Excel file: employee number, security group, from date and to date.

 

The Excel file should appear as follows:

Note:You can only import data describing the relation between the employee and the security group.

Step 2.When you have the information you need in the file, save the file as a CSV file in Excel, as displayed below.

4

Importing the Security Group CSV to Harmony:

Step 1. In the Interfaces screen, right click the screen area and open the Input interface screen.

Step 2.Fill in the information as follows:

5

Interface name - Enter a name.

ASCII File name - Browse to the CSV file.

Destination file name - Scroll and select the TLASG2ORG table.

Step 3.In the Properties tab, assign the information from the CSV file to the fields in the Harmony security group table are as follows:
Importing Definitions for Balance Tracking Totals

The following section describes the way in which to import definitions for Balance Tracking Totals from a csv file defining the Balance Tracking Totals and matching the definitions to Harmony definitions.
Creating the Balance Tracking Total CSV file with Balance Tracking Total information:

To import the information to Harmony create a CSV file with the information, in Excel.

Step 1. Create an Excel file to import to Harmony containing all of the information defining the Balance Tracking Total to set in Harmony.

The following information needs to be set in the Excel file:

Employee number - can include up to 9 digits

Date - contains the date first date of the period for updating the balance. The

recommended date format is DD/MM.YYYY.

Symbol - this field contains the Quota code assigned to the employee in

Harmony.

Balance total by date - the monthly balance total in days.

Balance total by time - the monthly balance total in hours.

Step 2.The Excel file should appear as follows:

Step 3.When you have the information you need in the file, save the file as a CSV file in Excel.
Importing the Balance Tracking Total CSV to Harmony:

Step 1. In the Interfaces screen, right click the screen area and open the Input interface screen.

Field name

Description

SGroup | Security Group

Import the security group assinged to the employee.

GroupType | Type

Not in Use

LevelOrg | Level

Always enter 6 in this field.

CodeOrg | Organization

Import the Employee number

DateFrom | From Date

Import the From date

DateTo | To Date

Import the To date

Harmony Operation and Installation ManualInterfaces

Step 2.Fill in the information as follows:

Interface name - Enter a name.

ASCII File name - Browse to the CSV file.

Destination file name - Scroll and select the TL_PeriodPayTotal table.

Step 3.In the Properties tab, assign the information from the CSV file to the fields in the Harmony table are as follows

Note: The fields in the Selected fields column are mandatory fields defined with constant values or imported data.

Field name Description

CodeNo | No. This field contains the Quota code assigned to the employee

in Harmony.

CodeType | Code This field contains the constant value y (in lowercase).

Emp_no | Employee No. This field contains the Employee number.

FromDate | Period From

Date

This field contains the first date of the period for updating the Balance.

ToDate | Period To Date This field contains the same data as in the Period From Date (the first date of the period for updating the balance).

SecondCode | Secondary Field

This field contains the constant value -1.

TotalPerCode | Hours This field contains the monthly balance in hours.

Sum_d | Qnty in Days This field contains the monthly balance in days.

CountPerCode | Quantity Not in use in this interface.

Hand | Manual Mandatory field. This field contains the constant value 1.

Note: This field contains the data for importing calculated data from