Line 855: | Line 855: | ||
} | } | ||
</pre> | </pre> | ||
=====Leave Application===== | |||
: Leave application available fields | |||
{| class="wikitable" style="margin: 1em auto 1em auto;" | |||
|- | |||
! Field Name !! Type !! Size !! (M)andatory/(O)ptional !! Description | |||
|- | |||
| code || String || 30 || M || Employee Code | |||
|- | |||
| leavedate || date || || O || | |||
* 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" | |||
|} | |||
<div style="float: right;"> [[#top|[top]]]</div> | |||
==Things To Consider== | ==Things To Consider== |
Revision as of 04:55, 12 September 2020
Introduction
This function is to import Information/Data from other Software Data
Below is the list Data that we can Import from
Special Import
Standard Import
SQL Payroll Program
May get it from here
Modules Require
- For all import except UBS Payroll & AutoCount Payroll
- Data Import
Guideline & Example
- There are 2 type of import
- Single File Import
- Batch File Import (Available in Version 1.2019.167.146 & above)
- Sample Output - 20190529
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
- Column Field name can be free format
- No need to Hash File
Cons
- Need Manually Mapping the Column
- Manually Import individually
- Not secure cause user can change the file before import
Overtime & Wages
|
|
Table Fields Properties | |||
---|---|---|---|
Field Name | Field Type | Field Size | Remarks |
Date | Date | 0 | In dd/mm/yyyy or 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 |
Leave
|
|
Table Fields Properties | |||
---|---|---|---|
Field Name | Field Type | Field Size | Remarks |
Date | Date | 0 | In dd/mm/yyyy or 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 |
|
Employee
Example 1 - Employee |
---|
"Code";"Name";"DOB";"Gender";"Salary";"Resident";"EISTYPE";"EISCATEGORY" |
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 |
Table Fields Properties | |
---|---|
Field Name | Remarks |
Marital Status |
|
Socso Type |
|
EIS Type |
|
EIS Category |
|
Bank Code | Is Bank SWIFT Code
|
Allowance & Deduction
|
|
Table Fields Properties | |||
---|---|---|---|
Field Name | Field Type | Field Size | Remarks |
Date | Date | 0 | In dd/mm/yyyy or dd-mm-yyyy format |
Employee | String | 30 | Employee Code/ID |
Code | String | 20 | Allowance or Deduction Code |
Unit | FMTBcd | 8 | Qty or Unit |
Claim
|
|
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 | 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 |
Batch File Import
- 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 Button to import all the files
- No need to manually mapping the column
- Secure from changes by user cause file is Hash
Cons
- Need follow the column name given
Overtime & Wages
Transaction Type | File Name |
---|---|
Wages |
|
Overtime |
|
|
|
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 |
Leave
File Name
- leave.application.csv
- leave.application.hash
|
|
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 |
|
Allowance & Deduction
Transaction Type | File Name |
---|---|
Allowance |
|
Deduction |
|
|
|
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 | Allowance or Deduction Code |
WorkUnit | FMTBcd | 8 | Qty or Unit |
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
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
[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] |
|
-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 |
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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"
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
- Leave application available fields
Field Name | Type | Size | (M)andatory/(O)ptional | Description |
---|---|---|---|---|
code | String | 30 | M | Employee Code |
leavedate | date | O |
| |
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" |
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)
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- ----------------------------------------------------------------------------------------------------------------------------------------------------
- Need assistance? May email to support@sql.com.my
See also
- Others Customisation