Line 634: | Line 634: | ||
=====Maintain Branch===== | =====Maintain Branch===== | ||
Command | |||
<pre> | |||
[SQL Payroll bin folder Path]> sqlpay -token "<accesstoken>" -dataprovider -id ""branch"" -out "<json file>" | |||
</pre> | |||
Example | |||
<pre> | |||
C:\estream\SQL Payroll\bin>sqlpay -token "eyJ0eXAiOi..." -dataprovider -id "branch" -out "c:\Users\Star\Desktop\branch.json" | |||
</pre> | |||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> | ||
=====Maintain Department===== | =====Maintain Department===== | ||
<div style="float: right;"> [[#top|[top]]]</div> | <div style="float: right;"> [[#top|[top]]]</div> |
Revision as of 04:15, 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
Maintain HR Group
Maintain Category
Maintain Project
Maintain Job
Maintain Task
Maintain Leave Type
Maintain Leave Group
Leave Application
Maintain Wages
Maintain Allowance
Maintain Overtime
Maintain Commission
Maintain Claim
Maintain Deduction
PaySheet
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