Import Data

Introduction

This function is to import Information/Data from other Software Data
Below is the list Data that we can Import from

Special Import

• UBS Payroll
• AutoCount Payroll

Standard Import

System	Contact Info
ELID	Elid Sdn Bhd
SynGlobal Time Attendance	SynGlobal Technologies Sdn Bhd
FingerTec	TimeTec Computing Sdn Bhd
ANVIZ	Hi-Profile Achievement (M) Sdn Bhd
Click Time Attendance	EGroup Solution Sdn Bhd
Nexpro TimeTracX	Nexus Professional Sdn Bhd
Check Time	MVIT Solution
ECOTimePrint	Eco Solution Technology
Invotime	Invocore Sdn Bhd

SQL Payroll Program

May get it from here

Modules Require

For all import except UBS Payroll & AutoCount Payroll

• Data Import

Guideline & Example

There are 3 type of import

• Single File Import
• Batch File Import (Available in Version 1.2019.167.146 & above)
• Command Line Sync (Available in Version 1.2020.186.160 & above)

Single File Import

This guide is all import except UBS Payroll & AutoCount Payroll

Just copy below example & paste to notepad & save it as CSV

Pros

1. Column Field name can be free format
2. No need to Hash File

Cons

1. Need Manually Mapping the Column
2. Manually Import individually
3. Not secure cause user can change the file before import

Overtime & Wages

Example 1 - OT or Wages (not applicable for version 192 & above) Date,Employee,Code,UnitAsTime 26/6/2015,A001,DR01,04:00 28/6/2015,A001,DR02,09:38 26/6/2015,A002,DR02,06:18 29/6/2015,A002,DR03,03:22 26/6/2015,A003,DR03,04:55

Example 2 - OT or Wages "Date","Employee","Code","Unit" 26/6/2015,A001,DR01,4 28/6/2015,A001,DR02,9.63 26/6/2015,A002,DR02,6.30 29/6/2015,A002,DR03,3.37 26/6/2015,A003,DR03,4.92

Table Fields Properties
Field Name	Field Type	Field Size	Remarks
Date	Date	0	In dd/mm/yyyy format
Employee	String	30	Employee Code/ID
Code	String	20	Overtime or Wages Code
Unit	FMTBcd	8	Time in rate eg 3 hr 15 min = 3.25 (3 + (15/60))
UnitAsTime	Time	0	In HH:MM format not applicable for version 192 & above

[top]

Leave

Example 1 - Leave TransDate,Employee,LeaveCode,UnitAsTime 08/11/2015,001,UL,08:00 19/11/2015,003,UL,09:20 15/11/2015,005,UL,08:00 01/11/2015,001,AL,08:00 03/11/2015,003,AL,08:00 08/11/2015,005,MC,08:00

Example 2 - Leave TransDate,Employee,LeaveCode,Unit 08/11/2015,001,UL,1 19/11/2015,003,UL,1.17 15/11/2015,005,UL,1 01/11/2015,001,AL,1 03/11/2015,003,AL,1 08/11/2015,005,MC,1

Table Fields Properties
Field Name	Field Type	Field Size	Remarks
Date	Date	0	In dd/mm/yyyy format
Employee	String	30	Employee Code/ID
LeaveCode	String	20	Leave Code (Eg AL, MC, etc)
Unit	FMTBcd	8	Day in rate eg 9 hr 20 min for 8 hr per day = 1.17 (((9+(20/60))/24)*(24/8))
UnitAsTime	Time	0	In HH:MM format

It adviceable to use Unit instead of UnitAsTime as UnitAsTime max value can enter is 23:59 only if you Export as Grand Total

[top]

Employee

Example 1 - Employee
"Code";"Name";"DOB";"Gender";"Salary";"Resident";"EISTYPE";"EISCATEGORY" "0018";"Shiro Emiya";"14/06/1980";"M";"1200";"F";"1";"0" "0019";"Nanoha Takamachi";"01/10/1995";"F";"2000";"F";"0";"1" "0020";"Belldandy";"01/09/1977";"F";"2500";"T";"1";"0"

Example 2 - Employee

CODE,NAME,NAME2,GENDER,DOB,ADDRESS1,ADDRESS2,ADDRESS3,ADDRESS4,PHONE1,PHONE2,MOBILE,EMAIL,NEWIC,OLDIC,PASSPORT,IMMIGRATIONNO,BRANCH,HRGROUP,DEPARTMENT,CATEGORY,PROJECT,JOB,TASK,COUNTRY,RACE,CALENDAR,JOBTITLE,JOINDATE,CONFIRMDATE,RESIGNDATE,MARITALSTATUS,ISACTIVE,WAGESTYPE,WAGES,CONTRIB,FREQUENCY,PAYMENTMETHOD,BANK,BANKACCNO,EPFNO,NK,INITIAL,SOCSONO,SOCSOTYPE,TAXBRANCH,TAXNO,DISABLED,RESIDENT,EASERIALNO,SPOUSENAME,SPOUSEADDRESS1,SPOUSEADDRESS2,SPOUSEADDRESS3,SPOUSEADDRESS4,SPOUSEPHONE1,SPOUSEPHONE2,SPOUSEMOBILE,SPOUSETAXBRANCH,SPOUSETAXNO,SPOUSEICNO,SPOUSEWORKING,SPOUSEDISABLED,LEAVEGROUP 001,LEE CHONG WAI,,M,12/12/1990,,,,,,,,lcw@gmail.com,901212014321,,,,----,----,----,----,----,----,----,MY,----,----,,02/01/2012,01/04/2012,,M,1,----,3000,----,----,MBB,CIBBMY,111265660,,,,,1,,,0,1,,,,,,,,,,,,,0,0,---- 002,KOO KIAN KEAT,,M,01/01/1992,,,,,,,,,920101101001,,,,----,----,----,----,----,----,----,MY,----,----,,01/08/2012,01/11/2012,,S,1,----,2000,----,HALF,MBB,RHBBMY,56706890,,,,,1,,,0,1,,,,,,,,,,,,,0,0,---- 003,NICOLE,,F,31/08/1994,,,,,,,,,940831105000,,,,----,----,----,----,----,----,----,MY,----,----,,02/01/2012,01/04/2012,,S,1,----,2800,----,----,MBB,MBBEMY,9048540654,,,,,1,,,0,1,,,,,,,,,,,,,0,0,---- 004,WONG CHUN HAN,,M,21/06/1984,,,,,,,,,840621083003,,,,----,----,----,----,----,----,----,MY,----,----,,01/02/2012,02/05/2012,,M,1,----,3200,----,----,MBB,HLBBMY,5646506160,,,,,1,,,0,1,,,,,,,,,,,,,0,0,---- 005,YAP KIM HOCK,,M,28/06/1980,,,,,,,,,800628063333,,,,----,----,----,----,----,----,----,MY,----,----,,02/01/2012,01/04/2012,,M,1,----,5000,----,----,MBB,OCBCMY,58946514315,,,,,1,,,0,1,,,,,,,,,,,,,0,0,----

Table Fields Properties
Field Name	Remarks
Marital Status	S - Single M - Married D - Divorced W - Widow
Socso Type	1 - First Category 2 - Second Category 0 - No Socso
EIS Type	0 - No EIS 1 - Has EIS
EIS Category	1 - Foreign workers 2 - Public sector 3 - Domestic servant 4 - Age 57 to 59 (never contribute EIS before) 5 - Employer with no employee 6 - Self employed/sole proprietors 7 - Spouse of employer
Bank Code	Is Bank SWIFT Code PHBMMY - Affin Bank Berhad RJHIMY - Al Rajhi Banking & Investment Corporation (Malaysia) Berhad MFBBMY - Alliance Bank Malaysia Berhad ARBKMY - AmBank (M) Berhad BIMBMY - Bank Islam Malaysia Berhad BKRMMY - Bank Kerjasama Rakyat Malaysia Berhad BMMBMY - Bank Muamalat Malaysia Berhad BSNAMY - Bank Simpanan Nasional Berhad CIBBMY - CIMB Bank Berhad CITIMY - Citibank Berhad DEUTMY - Deutsche Bank (Malaysia) Berhad HLBBMY - Hong Leong Bank Berhad HBMBMY - HSBC Bank Malaysia Berhad ICBKMY - Industrial and Commercial Bank of China (Malaysia) Berhad MBBEMY - Malayan Banking Berhad OCBCMY - OCBC Bank (Malaysia) Berhad PBBEMY - Public Bank Berhad RHBBMY - RHB Bank Berhad SCBLMY - Standard Chartered Bank Malaysia Berhad SMBCMY - Sumitomo Mitsui Banking Corporation Malaysia Berhad UOVBMY - United Overseas Bank (Malaysia) Berhad

[top]

Allowance & Deduction

Example - Allowance "Date","Employee","Code","Unit" 26/6/2017,A0001,HP,4 28/6/2017,A0001,FUEL,9.63 26/6/2017,A0002,HP,6.30 26/6/2017,A0003,FUEL,4.92 26/6/2017,A0004,HP,1

Example - Deduction "Date","Employee","Code","Unit" 26/6/2017,A0001,LATE,4 28/6/2017,A0001,LATE,9.63 26/6/2017,A0002,LATE,6.30 29/6/2017,A0002,LATE,3.37 26/6/2017,A0003,LATE,4.92

Table Fields Properties
Field Name	Field Type	Field Size	Remarks
Date	Date	0	In dd/mm/yyyy format
Employee	String	30	Employee Code/ID
Code	String	20	Allowance or Deduction Code
Unit	FMTBcd	8	Qty or Unit

[top]

Claim

Example 1 - Claim "TransDate","PostDate","Employee","Code","Amount" 26/6/2017,26/6/2017,A0001,TOLL,4 28/6/2017,28/6/2017,A0001,PARKING,9.63 26/6/2017,26/6/2017,A0002,TOLL,6.30 26/6/2017,26/6/2017,A0003,PARKING,4.92 26/6/2017,26/6/2017,A0004,TOLL,1

Example 2 - Claim "TransDate","PostDate","Employee","Code","Description","Ref1","REF2","Amount" 26/6/2017,26/6/2017,A0001,PARKING,"MID VALLEY - CUSTOMER AB","IV-01243","1235481377828",4 28/6/2017,28/6/2017,A0001,PARKING,"MID VALLEY - CUSTOMER AB","IV-01243","2584986928248",9.65 26/6/2017,26/6/2017,A0002,TOLL,"TOLL CLAIM","","",6.30

Table Fields Properties
Field Name	Field Type	Field Size	Remarks
TransDate	Date	0	In dd/mm/yyyy format Date of transaction Default TransDate is same as PostDate
PostDate	Date	0	In dd/mm/yyyy format Date of Process Date Default PostDate is same as TransDate
Employee	String	30	Employee Code/ID
Code	String	20	Claim Code
Description	String	100	Claim Description (Optional)
Ref1	String	20	Claim Reference 1 (Optional)
Ref2	String	20	Claim Reference 2 (Optional)
Amount	FMTBcd	8	Claim Amount

[top]

Batch File Import(Deprecated)

This function only Available in Version 1.2019.167.146 & above.

All file should be in CSV also but need to be in 1 zip file.

In Zip should had At least 2 file

• Transaction File (eg pending.overtime.csv)
• Hash File (eg pending.overtime.hash)

Zip File Name can be any name you like

Pros

1. 1 Button to import all the files
2. No need to manually mapping the column
3. Secure from changes by user cause file is Hash

Cons

1. Need follow the column name given

Overtime & Wages

Transaction Type	File Name
Wages	pending.wages.csv pending.wages.hash
Overtime	pending.overtime.csv pending.overtime.hash

Example 1 - OT or Wages TransDate,PostDate,Employee,Code,WorkUnitAsTime 26/6/2015,26/6/2015,A001,DR01,04:00 28/6/2015,28/6/2015,A001,DR02,09:38 26/6/2015,26/6/2015,A002,DR02,06:18 29/6/2015,29/6/2015,A002,DR03,03:22 26/6/2015,26/6/2015,A003,DR03,04:55

Example 2 - OT or Wages "TransDate","PostDate","Employee","Code","WorkUnit" 26/6/2015,26/6/2015,A001,DR01,4 28/6/2015,28/6/2015,A001,DR02,9.63 26/6/2015,26/6/2015,A002,DR02,6.30 29/6/2015,29/6/2015,A002,DR03,3.37 26/6/2015,26/6/2015,A003,DR03,4.92

Table Fields Properties
Field Name	Field Type	Field Size	Remarks
TransDate	Date	0	In dd/mm/yyyy or dd-mm-yyyy format Date of transaction
PostDate	Date	0	In dd/mm/yyyy or dd-mm-yyyy format Date of Process Date
Employee	String	30	Employee Code/ID
Code	String	20	Overtime or Wages Code
WorkUnit	FMTBcd	8	Time in rate eg 3 hr 15 min = 3.25 (3 + (15/60))
WorkUnitAsTime	Time	0	In HH:MM format

[top]

Leave

File Name

• leave.application.csv
• leave.application.hash

Example 1 - Leave LeaveDate,Code,LeaveType,Description,LeaveDayAsTime 08/11/2015,001,UL,Sick,08:00 19/11/2015,003,UL,Fever,09:20 15/11/2015,005,UL,Sick,08:00 01/11/2015,001,AL,Holiday To Melaka,08:00 03/11/2015,003,AL,Holiday To Penang,08:00 08/11/2015,005,MC,High Fever,08:00

Example 2 - Leave LeaveDate,Code,LeaveType,Description,LeaveDay 08/11/2015,001,UL,Sick,1 19/11/2015,003,UL,Fever,1.17 15/11/2015,005,UL,Sick,1 01/11/2015,001,AL,Holiday To Melaka,1 03/11/2015,003,AL,Holiday to Penang,1 08/11/2015,005,MC,High Fever,1

Table Fields Properties
Field Name	Field Type	Field Size	Remarks
Date	Date	0	In dd/mm/yyyy or dd-mm-yyyy format
Code	String	30	Employee Code/ID
LeaveType	String	20	Leave Code (Eg AL, MC, etc)
Description	String	80	Leave Description
LeaveDay	FMTBcd	8	Day in rate eg 9 hr 20 min for 8 hr per day = 1.17 (((9+(20/60))/24)*(24/8))
LeaveDayAsTime	Time	0	In HH:MM format

It adviceable to use LeaveDay instead of LeaveDayAsTime as LeaveDayAsTime max value can enter is 23:59 only if you Export as Grand Total

[top]

Allowance & Deduction

Transaction Type	File Name
Allowance	pending.allowance.csv pending.allowance.hash
Deduction	pending.deduction.csv pending.deduction.hash

Example - Allowance "TransDate","PostDate","Employee","Code","WorkUnit" 26/6/2017,26/6/2017,A0001,HP,4 28/6/2017,28/6/2017,A0001,FUEL,9.63 26/6/2017,26/6/2017,A0002,HP,6.30 26/6/2017,26/6/2017,A0003,FUEL,4.92 26/6/2017,26/6/2017,A0004,HP,1

Example - Deduction "TransDate","PostDate","Employee","Code","WorkUnit" 26/6/2017,26/6/2017,A0001,LATE,4 28/6/2017,28/6/2017,A0001,LATE,9.63 26/6/2017,26/6/2017,A0002,LATE,6.30 29/6/2017,29/6/2017,A0002,LATE,3.37 26/6/2017,26/6/2017,A0003,LATE,4.92

Table Fields Properties
Field Name	Field Type	Field Size	Remarks
TransDate	Date	0	In dd/mm/yyyy or dd-mm-yyyy format Date of transaction Default TransDate is same as PostDate
PostDate	Date	0	In dd/mm/yyyy or dd-mm-yyyy format Date of Process Date Default PostDate is same as TransDate
Employee	String	30	Employee Code/ID
Code	String	20	Allowance or Deduction Code
WorkUnit	FMTBcd	8	Qty or Unit

[top]

File Hash

The Hash is using MD5

Steps

01. File Hash : Hash MD5 the whole file

02. File String Hash : Hash MD5 again using the result shown in MD5 in Step 1.

03. Save the result in Step 2 in .hash file

Example Using the Allowance Information as above

01. Result from File Hash : e83045758c13cb7c17ba8f2150919f36

02. Result from String Hash from Step 1 : 1b324dad30a84f322c23a1cdec481cc1

03. Save the Result in Step 2 in pending.allowance.hash file

[top]

Command Line Sync

This function only Available in Version 1.2020.186.160 & above.

This function can import and export file from/to SQL Payroll

Setting In SQL Payroll

An access token is required when the third party application calls data provider and data import commands.

01. Click Tools | Maintain User

02. Double Click ADMIN user

03. Click More | Generate Access Token

04. Enter the ADMIN password

05. Save the Token File

Data Provider (Export)

This command is to export data from SQL Payroll to Json file

Sample Output - 20200914

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "<id>" [-<param name> "<param value>"] -out "<json file>"

Command	Description
[SQL Payroll bin folder Path]	Folder path where SQL Payroll Install Eg C:\estream\SQL Payroll\bin
-token	A Code where generate
-dataprovider	Indicator To export Data from SQL Payroll
-id	Indicator type of Data to export
[-<param name> "<param value>"]	Additional parameter for some id type
-out	Json file folder & name to save to

[top]

Maintain Employee

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "employee" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "employee" -out "c:\Users\Star\Desktop\employee.json" 

[top]

Maintain Branch

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "branch" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "branch" -out "c:\Users\Star\Desktop\branch.json" 

[top]

Maintain Department

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "department" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "department" -out "c:\Users\Star\Desktop\department.json" 

[top]

Maintain HR Group

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "hrgroup" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "hrgroup" -out "c:\Users\Star\Desktop\hrgroup.json" 

[top]

Maintain Category

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "category" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "category" -out "c:\Users\Star\Desktop\category.json" 

[top]

Maintain Project

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "project" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "project" -out "c:\Users\Star\Desktop\project.json" 

[top]

Maintain Job

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "job" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "job" -out "c:\Users\Star\Desktop\job.json" 

[top]

Maintain Task

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "task" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "task" -out "c:\Users\Star\Desktop\task.json" 

[top]

Maintain Leave Type

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "leavetype" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "leavetype" -out "c:\Users\Star\Desktop\leavetype.json" 

[top]

Maintain Leave Group

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "leavegroup" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "leavegroup" -out "c:\Users\Star\Desktop\leavegroup.json" 

[top]

Leave Application

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "leave" -datefrom "<yyyy-mm-dd>" -dateto "<yyyy-mm-dd>" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "leave" -datefrom "2020-01-01" -dateto "2020-12-31" -out "c:\Users\Star\Desktop\leave.json" 

[top]

Maintain Wages

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "wages" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "wages" -out "c:\Users\Star\Desktop\wages.json" 

[top]

Maintain Allowance

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "allowance" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "allowance" -out "c:\Users\Star\Desktop\allowance.json" 

[top]

Maintain Overtime

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "overtime" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "overtime" -out "c:\Users\Star\Desktop\overtime.json" 

[top]

Maintain Commission

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "commission" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "commission" -out "c:\Users\Star\Desktop\commission.json" 

[top]

Maintain Claim

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "claim" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "claim" -out "c:\Users\Star\Desktop\claim.json" 

[top]

Maintain Deduction

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "deduction" -out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "deduction" -out "c:\Users\Star\Desktop\deduction.json" 

[top]

PaySheet

Command

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id "paysheet" -year "<ProcessYear>" -month "<ProcessMonth>"-out "<json file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "paysheet" -year "2019" -month "1" -out "c:\Users\Star\Desktop\paysheet.json" 

[top]

Data Import

This command is to import data to SQL Payroll

[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataimport -in "<zip file>"

Example

C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataimport -in "c:\Users\Star\Desktop\output.zip" 

Each ZIP file can contains one or more data file, each data file is a JSON object that has id and data fields.

{
  "id":"",
  "data":[
  ]
}

Leave Application

Fields Properties
Field Name	Type	Size	(M)andatory/(O)ptional	Description
code	String	30	M	Employee Code
leavedate	date		M	ISO 8601 Date Format (yyyy-mm-dd). Example: "2020-09-08"
description	String	160	O	Leave Description
leavetype	String	20	M	Leave Type Code
leaveday	decimal	18,8	O	Decimal in string. Example: "0.16666667"
leavedayastime	time		O	Time Format (hh:nn). Example: "01:20"

Example
{ "id":"leave.application", "data":[ { "code":"001", "leavedate":"2020-09-09", "description":"Attent Shopping event", "leavetype":"AL", "leaveday":"1" }, { "code":"002", "leavedate":"2020-09-08", "leavetype":"AL", "leavedayastime":"01:20" } ] }

[top]

Pending Wages, Overtime, Allowance & Deduction

Fields Properties
Field Name	Type	Size	(M)andatory/(O)ptional	Description
postdate	date		M	ISO 8601 Date Format (yyyy-mm-dd). Example: "2020-09-08"
employee	String	30	M	Employee Code
project	String	20	O	Project Code
job	String	20	O	Job Code
task	String	20	O	Task Code
code	String	20	M	pending.wages => Wages Type Code pending.overtime => Overtime Type Code pending.allowance => Allowance Type Code pending.deduction => Deduction Type Code
description	String	160	O	Transaction Description
workunit	decimal	18,8	O	Decimal in string. Example: "0.16666667"
workunitastime	time		O	Time Format (hh:nn). Example: "01:20"

Example Pending Wages { "id":"pending.wages", "data":[ { "postdate":"2020-09-08", "employee":"001", "code":"DR01", "description":"aaa", "workunit":"1" }, { "postdate":"2020-09-08", "employee":"002", "code":"DR01", "workunitastime":"02:10" } ] }

Example Pending Overtime { "id":"pending.overtime", "data":[ { "postdate":"2020-09-08", "employee":"001", "code":"DR01", "description":"aaa", "workunit":"1" }, { "postdate":"2020-09-08", "employee":"002", "code":"DR01", "workunitastime":"02:10" } ] }

[top]

Pending Claim

Fields Properties
Field Name	Type	Size	(M)andatory/(O)ptional	Description
postdate	date		M	ISO 8601 Date Format (yyyy-mm-dd). Example: "2020-09-08"
employee	String	30	M	Employee Code
project	String	20	O	Project Code
job	String	20	O	Job Code
task	String	20	O	Task Code
code	String	20	M	Claim Type Code
description	String	160	O	Transaction Description
ref1	string	20	O	Claim Reference 1
ref2	string	20	O	Claim Reference 2
amount	18,2		M	Claim Amount

Example
{ "id":"pending.claim", "data":[ { "postdate":"2020-09-08", "employee":"001", "description":"aaa", "amount":"1.12" }, { "postdate":"2020-09-08", "employee":"001", "amount":"1.12" } ] }

[top]

Appendix

Below are constants used in SQL Payroll

Gender

Value	Description
M	Male
F	Female

Married Status

Value	Description
S	Single
M	Married
D	Divorced
W	Widow

SOCSO Type

Value	Description
0	No SOCSO
1	First Category
2	Second Category

EIS Type

Value	Description
0	No EIS
1	With EIS

Leave Application Status

Value	Description
0	Pending
1	Approved
2	Rejected
3	Cancelled
4	Commiteed

[top]

Things To Consider

01. The target database must had Valid

- Employee Code

- OT Code

- Leave Code

- Wages Code

02. File extention must be in csv (e.g. OT.csv)

03. The data in "Net Figure" (eg. OT WorkUnitAsTime := Logout Time - Login Time)

04. There is no display format for Amount/Qty (eg 1600.12 not 1,600.12)

05. There is no extra spacing at the Begin & End of data (eg Tes1,Test2 not Tes1 , Tes2 ,)

06. The data type which is Boolean (0 = False & 1 = True)

07. Must 1 Transaction type 1 file (i.e. Overtime 1 file & Leave 1 file)

08. The output can be export out in Day or Time but not both in same time

09. Make sure only export out relevant data (eg if OT = 0 or No leave taken don't export out)

----------------------------------------------------------------------------------------------------------------------------------------------------

Notes 1. Output Can be in Tab, Semicolon or Comma. 2. Field can be added by just look in the field chooser in Pending Payroll.

----------------------------------------------------------------------------------------------------------------------------------------------------

• Need assistance? May email to support@sql.com.my

See also

• Others Customisation