Versions Compared

Old Version 32

changes.mady.by.user Valentyna Plokhykh

Saved on

compared with

New Version Current

changes.mady.by.user Daria Chebotaieva

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
minLevel1
maxLevel2

This article explains how to

...

use the Import Spreadsheet routine as a powerful tool that allows to add various payroll-related information to the batch of employees.

Table of Contents
minLevel1
maxLevel2

1.   Introduction

Import Spreadsheet tool allows

...

uploading various payroll-related information into the system for a batch of persons with the help of a simple xls / xlsx file.

It allows

...

importing:

-  Person's Payroll information (mostly that is added under Payroll → Constants tab and Payment tab).

-  Payroll Entries (in current and future periods).

-  Additional Payment Info for those Entry Codes.

To access the routine, go to Payroll tab > Batch Entry as shown in the below screenshot.

...

Image Added
Info

To access this tool access rights should be set to Y for the routines 766 Spreadsheet in Adonis Control Center (ACC).

...

Image Added

2.   Import setup

Once you open the Import

...

Spreadsheet window you will see the following dialogue that allows you to run the import.

However, before you start you need to

...

set up and configure the import parameters.

Image Modified

The setup can be found under the Tools -> Import -> Spreadsheet -> Properties button.

Here you can

...

set up and configure the import details by following the below recommendations.

...

Image Added
Info

To access the setup part, access rights should be set to Y for the routine 767 Spreadsheet Properties in Adonis Control Center (ACC).

...

Image Added

2.1.   Add transactions

Once you click on Properties, you will see the following setup screen.

...

Image Added

  1. Primary Worksheet Name – is a free input text field where user can define the name of the source tab from excel template (.xls / .xlsx) to be used for import.

...

  1. Image Added

  2. Batch Start / Batch End – numeric fields where identify first & last rows containing import information within the excel file (.xls / .xlsx). Only the records in this range will be processed. The row with headers should not be included.

...

  1. Image Added

  2. Primary Column – here you define the letter of the column from the excel file which will be used as the primary key value. Usually, it is a column with a PIN.
    The field is mandatory for input. If there is no value in this column, the transaction is stopped even if the Batch End is not reached.

...

  1. Image Added

  2. Add / Delete buttons – are used to define the set of transactions that will be performed during import. Usually, only one transaction is defined per each import.

    Image Modified

  3. Transaction nr - Integer value identifying the transaction number. The import utility will import transaction by transaction, the transaction number is used to group all the field assignments for one transaction.

  4. Table Name - predefined list of database tables that can be used for import.

    Image Modified

    In total Import Spreadsheet contains 3 database tables:
    -  Table PW001PAY (Payroll Entries in current and future periods)
    -  Table PW001PAI (Payroll Entry Additional Info on the Entry Code).
    -  Table PW001P0Y (Person's Payroll information - mostly taken from Constant page in Payroll window).

    Info

    1)  PW001PAY and PW001P0Y tables can not be imported at the same time.
    For each of them you need to make a separate setup each time you make the import.
    If you make import only for one of those tables, then there is no need to delete the setup. You can re-use it the next time when you do the import.

    2) To import PW001PAI table you must import PW001PAY table as well. PW001PAI can not be imported by itself.
    In order to add the link between PAI information and PAY entries you have to import PW001PAY.REF field with "Y" value.
    Columns from both tables should be placed on the same worksheet. PAI information should be places in the same line as PAY Entry Code for which you import the Additional Payment Info.

    Image Modified


  5. Update if record exist - enables the input to the column "Key Fields" in the Details screen.
    If this option is set, the system needs to know what are the key fields identifying the transaction that

...

  1. needs to be updated.

2.2. Tables description

Expand
titleTable PW001PAY (Payroll Entries, Current and future periods)

Reference from list of the table PW001PAY

Foreign Key Columns

Child Table

Parent Table

PIN

PW001PAY (Payroll Entries, Current and future periods)

PW001P01 (Person Details)

ENTRYCODE

PW001PAY (Payroll Entries, Current and future periods)

PYETY001 (Entrycode information)

ACTIVITYSEQ

PW001PAY (Payroll Entries, Current and future periods)

PW001P03 (Activity Datagroup)

EXECUTIONID_CALC

PW001PAY (Payroll Entries, Current and future periods)

PWLOG_PAYROLL_EXEC (Logs who and when performs payroll operations)

 Column list of the table PW001PAY

Code

Comment

Data type

Mandatory

Primary

Foreign Key

PIN


integer

X


X

SEQNO


integer

X

X


UPDATEFLAGS


varchar(125)




SOURCE


varchar(1)




FROMVESSEL


integer




CREATEDBY


varchar(6)

X



CREATETIME


date

X



CHANGEDBY


varchar(6)




CHANGETIME


date




CALCULATEDBY


varchar(6)




CALCULATETIME


date




FROMPERIODE


integer




TOPERIODE


integer




FROMDATE


date




TODATE


date




REF


varchar(2)




ENTRYCODE


integer

X


X

ENTRYFIELD1


varchar(20)




ENTRYFIELD2


varchar(20)




ENTRYFIELD3


varchar(20)




ENTRYFIELD4


varchar(20)




ENTRYFIELD5


varchar(20)




ACCOUNTNO


varchar(30)




ACCOUNTDIM2


varchar(10)




ACCOUNTDIM3


varchar(10)




ACCOUNTDIM4


varchar(10)




ACCOUNTDIM5


varchar(10)




ACCOUNTDIM6


varchar(10)




LTOREF


varchar(5)




TEXT


varchar(30)




PERIODEUSED


integer




CREDITENTRY


char(1)




BASIS


varchar(20)




CURRENCYCODECALC


varchar(3)




CALCULATEDAMOUNT1


float




CALCULATEDAMOUNT2


float




CALCULATEDAMOUNT3


float




CALCULATEDAMOUNT4


float




CALCULATEDAMOUNT5


float




CALCULATEDAMOUNT6


float




CALCULATEDAMOUNT7


float




CALCULATEDAMOUNT8


float




CALCULATEDAMOUNT9


float




CALCULATEDAMOUNT10


float




CALCULATEDAMOUNT11


float




CURRENCYCODE


varchar(3)




CURRENCYRATE


float




ACCOUNTNOOFCALCAMOUNT1


varchar(30)




ACCOUNTNOOFCALCAMOUNT2


varchar(30)




ACCOUNTNOOFCALCAMOUNT3


varchar(30)




ACCOUNTNOOFCALCAMOUNT4


varchar(30)




ACCOUNTNOOFCALCAMOUNT5


varchar(30)




ACCOUNTNOOFCALCAMOUNT6


varchar(30)




ACCOUNTNOOFCALCAMOUNT7


varchar(30)




ACCOUNTNOOFCALCAMOUNT8


varchar(30)




ACCOUNTNOOFCALCAMOUNT9


varchar(30)




ACCOUNTNOOFCALCAMOUNT10


varchar(30)




ACCOUNTNOOFCALCAMOUNT11


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT1


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT2


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT3


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT4


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT5


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT6


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT7


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT8


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT9


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT10


varchar(30)




ACCOUNTNOOFCALCAMOUNTCREDIT11


varchar(30)




FLAGOFCALCAMOUNT1


varchar(1)




FLAGOFCALCAMOUNT2


varchar(1)




FLAGOFCALCAMOUNT3


varchar(1)




FLAGOFCALCAMOUNT4


varchar(1)




FLAGOFCALCAMOUNT5


varchar(1)




FLAGOFCALCAMOUNT6


varchar(1)




FLAGOFCALCAMOUNT7


varchar(1)




FLAGOFCALCAMOUNT8


varchar(1)




FLAGOFCALCAMOUNT9


varchar(1)




FLAGOFCALCAMOUNT10


varchar(1)




FLAGOFCALCAMOUNT11


varchar(1)




QUANTITY1


float




QUANTITY2


float




CURRENTCURRENCY


char(1)




CALCPRIORITY


integer




DEPARTMENT


integer




COUNTYNO


integer




PAYSCALETABLE


varchar(10)




PAYSCALECODE


varchar(10)




PAYRISETABLE


varchar(10)




PAYRISECODE


varchar(10)




CALCSIGN


varchar(1)




ADDITIONALINFO


varchar(30)




NEVERTAX


varchar(1)




RANK


varchar(10)




ENTRYFIELD6


varchar(20)




CURRENCYRATEFORCALC


float




AUTOENTRY


char(1)




ISBACKPAYENTRY


varchar(1)




SD


varchar(1)




ACTIVITYSEQ


integer



X

ACCOUNTDIM7


varchar(30)




ACCOUNTDIM8


varchar(30)




ACCOUNTDIM9


varchar(30)




PAYNAME2


varchar(35)




CURRENCYFORPAYMENT


char(3)




CURRENCYRATEFORPAYMENT


float




PAID_WEEK


integer




EXECUTIONID_CALC

Indicates when a record was last changed during payroll calculation procedure. Refers to PWLOG_PAYROLL_EXEC.EXECUTIONID.

int



X

EXECUTIONID_CLOSE_M

Indicates when a record was last changed during closing period procedure. Refers to PWLOG_PAYROLL_EXEC.EXECUTIONID.

int




EXECUTIONID_CLOSE_Y

Indicates when a record was last changed during closing year procedure. Refers to PWLOG_PAYROLL_EXEC.EXECUTIONID.

int




SESSIONID

A current session ID. Refers to PWLOGINSESSION.SESSIONID.

int

X



DBACTION

Indicates a type of changes done.

varchar(10)

X



AUDIT_LINENR


int

X



repl_ModifiedBySite

Site that modified the record

integer




repl_ModifiedDate

Date of modification

datetime




repl_GMTModifiedDate

GMT date of modification

datetime





Expand
titleTable PW001PAI (Payroll Entry Aditional Info on the Entry Code)

Reference from list of the table PW001PAI

Foreign Key Columns

Child Table

Parent Table

PIN

PW001PAI (Payroll Entry Additional Info)

PW001P01 (Person Details)

EXECUTIONID_CLOSE_M

PW001PAI (Payroll Entry Additional Info)

PWLOG_PAYROLL_EXEC (Logs who and when performs payroll operations)

Column list of the table PW001PAI

Code

Comment

Data type

Mandatory

Primary

Foreign Key

PIN

Person's pin.

integer

X


X

SEQNO

A value coincides with PW001PAY.SEQNO for current payroll records or PW001PYH.SEQNO for historical records.

integer

X

X


TEXT

Additional text.

varchar(100)




ACCOUNTNO

Account No.

varchar(35)




SWIFT

Swift code.

varchar(11)




PAYNAME

Receiver name.

varchar(35)




PAYCOUNTRY


varchar(2)




PAYPOSTCODE


varchar(102)




ADDRESS1


varchar(40)




ADDRESS2


varchar(40)




ADDRESS3


varchar(40)




PAYBANKCODE


varchar(40)




INTERNATIONALBANK

Link to PW001C26

varchar(10)




INTERMEDIATEBANK

Link to PW001C26

varchar(10)




RECEIVERBANK

Link to PW001C26

varchar(10)




RTYPEINTERNATIONAL


varchar(11)




RTYPEINTERMEDIATE


varchar(11)




RTYPERECEIVER


varchar(11)




RCODEINTERNATIONAL


varchar(10)




RCODEINTERMEDIATE


varchar(10)




RCODERECEIVER


varchar(10)




BALANCEACTION

List of Values:

0=Carry Forward To Next Month

1=Transfer To Bank Acct.

2=Interpay

3=Alpha Credit Bank

4=Cash

5=Banque Worms

6=Citibank

7=Chase Manhattan

8=Hellenic Bank

9=Isabel Domestic

A=Isabel International

B=Bank Transfer

B4=ISO20022/Swift

B7=Check Payment

B8=Check Onboard

C=Swedish Domestic

D=Swedish International

E=Philippine Banking

F=Manual Bank

G=Zagrebancka

H=ING Bank

I=Direct Deposit E-Money

J=Deutsche Bank

K=BACS

L=Hellenic Cyprus

M=MT100

varchar(1)




EXECUTIONID_CLOSE_M

Indicates when a record was last changed during closing period procedure. Refers to PWLOG_PAYROLL_EXEC.EXECUTIONID.

int



X

repl_ModifiedBySite

Site that modified the record

integer




repl_ModifiedDate

Date of modification

datetime




repl_GMTModifiedDate

GMT date of modification

datetime





Expand
titleTable PW001P0Y (Person's Payroll information (Mostly Constant page in Payroll window))

Reference from list of the table PW001P0Y

Foreign Key Columns

Child Table

Parent Table

PIN

PW001P0Y (Person's Payroll information (Mostly Constant and Payment page in Payroll window))

PW001P01 (Person Details)

EXECUTIONID_LOCK

PW001P0Y (Person's Payroll information (Mostly Constant and Payment page in Payroll window))

PWLOG_PAYROLL_EXEC (Logs who and when performs payroll operations)

Column list of the table PW001P0Y

Code

Comment

Data type

Mandatory

Primary

Foreign Key

PIN


integer

X

X

X

PAYSCALECODE


varchar(14)




LEAVEPAY

Payroll > page Constant:

integer




PAYROLLPERIODLOCK


varchar(1)




LAYOUTSLIP


integer




REGIONTAXADVANCE


integer




TAXTABLE

Payroll > Constant page: Table No

integer




TAXPERCENTAGE

Payroll > page Constant: Percentage Tax

integer




TAXDEDUCTIONCODE


varchar(1)




TAXFREELIMIT

Payroll > page Constant: Exception Limit

integer




SEAMANSALLOWANCE


varchar(1)




WAGESOPTIONS


varchar(12)




DAYSEMPLOYED


integer




LEAVEPAYSTARTBALANCE


integer




PAYCOUNTRY


varchar(3)




PAYSWIFTCODE


varchar(20)




PAYNAME

Payroll > Payment page > Alternative Receiver page: Name

varchar(30)




PAYNAME2

Payroll > Payment page > Alternative Receiver page: Name

varchar(35)




PAYADDRESS1

Payroll > Payment page > Alternative Receiver page: Address 1

varchar(40)




PAYADDRESS2

Payroll > Payment page > Alternative Receiver page: Address 2

varchar(40)




PAYADDRESS3

Payroll > Payment page > Alternative Receiver page: Address 3

varchar(40)




PAYADDRESSCOUNTRY

Payroll > Payment page > Alternative Receiver page: Country

varchar(2)




PAYPOSTCODE

Payroll > Payment page > Alternative Receiver page: Post Code

varchar(102)




REGIONCODE

Payroll > page Constant: Region Code

varchar(10)




NOEMPLOYERFEE


char(1)




PAYBANKCODE


varchar(40)




AUTOTAX

Payroll > page Constant: Automatic Tax


integer




TAXEXEMPTION


integer




TAXCOMMENT


varchar(100)




ALTERNATIVEASCODE


integer




INTERNATIONALBANK


varchar(10)




INTERMEDIATEBANK


varchar(10)




RECEIVERBANK


varchar(10)




RTYPEINTERNATIONAL


varchar(11)




RTYPEINTERMEDIATE


varchar(11)




RTYPERECEIVER


varchar(11)




RCODEINTERNATIONAL


varchar(10)




RCODEINTERMEDIATE


varchar(10)




RCODERECEIVER


varchar(10)




PAYBANKNO


integer




PAYBANK_CODE


varchar(10)




TROSSAMFUND


varchar(1)




LEAVEDAYSDEDUCTEDFACTOR


varchar(20)




ALLOWANCECLAIMED


integer




TAXINFO

Payroll > page Constant: Tax Info Number

varchar(15)




MBANKNAME


varchar(40)




MBANKROUTINGTYPE


varchar(40)




MBANKROUTINGCODE


varchar(40)




MBANKADDRESS1


varchar(40)




MBANKADDRESS2


varchar(40)




MBANKADDRESS3


varchar(40)




MBANKCOUNTRY


varchar(3)




MBANKPOSTCODE


varchar(102)




TAXCODE


varchar(10)




MONYHWEEKBASIS


varchar(1)




NI_NUMBER


varchar(9)




UKPAYRATE


varchar(4)




UKPAYCATEGORY


varchar(1)




EXECUTIONID_LOCK

Indicates when a record was last locked. Refers to PWLOG_PAYROLL_EXEC.EXECUTIONID.

int



X

SESSIONID

A current session ID. Refers to PWLOGINSESSION.SESSIONID.

int

X



DBACTION

Indicates a type of changes done.

varchar(10)

X



AUDIT_LINENR


int

X



repl_ModifiedBySite

Site that modified the record

integer




repl_ModifiedDate

Date of modification

datetime




repl_GMTModifiedDate

GMT date of modification

datetime





2.3. Add transaction details

A list of fields to be imported & the rules of import should be defined for each existing transaction.

In order to do this select the corresponding Transaction & click Details.

...

Image Added

  1. Field - offers you a list of fields from the selected database table that you can use in the import
    e.g. PIN, ENTRYCODE, ENTRYFIELD1 (also called L21 or Amount), ENTRYFIELD2 (also called L22 or Quantity/Days) etc.
    If you don't know which payroll fields correspond to which database tables, contact Adonis support for assistance.

  2. XLS Reference - defines the column (or cell) that contains data to be imported into selected field (e.g. A1, A2, B1, B7 etc.)
    It is possible to set not only the cell but also the sheet that it belongs to (e.g. SHEET1!A1, SHEET2!A1 etc.) Reference should be the specific column from the Excel file which corresponds the PIN field on the payroll table.

...

  1. Image Added

  1. Batch - when XLS reference is set to a cell value (meaning that it is the same for the entire batch), the Batch option should be set off (disabled).

  2. Mandatory - defines if the value in this field is mandatory or not. When set and the value is missing - the corresponding transaction line is skipped.
    Mandatory should be checked accordingly as importing will not be possible without knowing which person it should be imported to.

  3. Key Field - is taken to count only if the Update record if exist is set Onfor the Transaction on Properties level. Otherwise, it is treated the same as Mandatory.
    If both options are set - the transaction line checks if the table already has the record with Key Field values. If so - such record is updated. If not - the record is created.
    If multiple records meet the set of Key Fields - the system displays warning & update is not performed. This is to avoid updating multiple records by mistake.

3.   Import procedure

  1. In the initial dialogue window, you should browse the root folder for imported files and click Prepare.

...


  1. Image Added



    The system will create folders Receive / Rejected / Archive within the folder you

...

  1. selected.
     

    Image Modified

  2. Place your import file(s) into the Receive folder. The file should be of xls / xlsx format.
    Then click Prepare button again.
    The system will generate a list of all the xls / xlsx files existing in Receive folder.

...

  1. Image Added

  2. If the list is ok, click Start Import. It will upload all files from the list according to defined transactions.
    The file should be closed during the import.

     a) If import is successful, the status will be changed to Imported.

...

  1. Image Added


    The successfully imported files will be moved to the Archive folder, the date and time of import will be added to the name of the file.

    Image Modified


    b) If an error occurred during the import the status will be set to Rejected with an explanation line.

...

  1. Image Added

...


  1. Image Added



    The rejected file will be moved to the Rejected folder.

    Image Modified

  2. You can use Save Summary to save the log file with import results to your hard drive.

    Image Modified