Export Payroll
The last step in an API-based payroll or accruals integration is to export the payroll or accruals data for consumption by an external system. This involves an asychronous API call that generates an execution key on success. This execution key is used to retrieve the status and response payload of the asynchronous job.
Prerequisites
Before exporting payroll or accruals, you must create a custom table and stage payroll.
You must understand how to work with asynchronous calls. For a brief introduction, refer to the A Guide to Running Payroll overview topic.
Example
In this example, we export payroll asynchronously, retrieve the job status, and, once completed, retrieve the response payload.
Export Payroll Asynchronously
The export request:
- passes the
requestIdgenerated during payroll or accruals staging; refer to the Stage Payroll topic - defines a SQL
querythat selects columns from the payroll or accruals tables defined in the Overview topic - configures export options within
exportConfig, including format, delimiter, and file name - defines
headersandfooters
Example request
Call the Export Payroll Asynchronously (POST /v1/commons/payroll/export/async) operation with the following request payload.
{
"requestId": "10982d9f-ea2e-46ad-8370-1e60d5a665b7",
"query": "select person_num, full_name, amount_type, apply_date, hours_amount, days_amount, money_amount, paycode_name, range_start_date, range_end_date from `$Payroll`",
"exportConfig": {
"format": "csv",
"fileName": "TestPayrollLB.dat",
"delimiter": ","
},
"headers": [
{
"prefix": "Header Date: ",
"queries": [
"SELECT FORMAT_DATETIME('%m/%d-%Y %H:%M:%S', CURRENT_DATETIME())"
]
}
],
"footers": [
{
"prefix": "Total Payroll Records,Total Payroll Seconds "
},
{
"queries": [
"SELECT COUNT(person_num) FROM `$Payroll`",
"SELECT ','",
"SELECT SUM(hours_amount) FROM `$Payroll`"
]
}
]
}
Example response
A success response returns HTTP status code 200 and a response body similar to the following example.
{
"state": "PENDING",
"message": "Export request being processed",
"nextPing": 60,
"expiresAt": "2021-12-11T18:47:05.663",
"executionKey": "73dcbd9f-7119-411f-975a-721d567f95da"
}
Retrieve summary of all jobs
To retrieve a summary of all asynchronous export jobs, call the Retrieve Summary of Asynchronous Payroll Export Jobs (GET /v1/commons/payroll/export/async) operation.
Calling GET /v1/commons/payroll/export/async returns:
{
"records": [
{
"expiresAt": "2021-12-11T18:47:05.663",
"executionKey": "73dcbd9f-7119-411f-975a-721d567f95da",
"state": "SUCCESSFUL"
},
{
"expiresAt": "2021-12-10T19:51:23.726",
"executionKey": "0ff586a5-6a1a-4d89-b9a2-2cf4c98bd498",
"state": "SUCCESSFUL"
},
{
"expiresAt": "2021-12-08T12:36:57.113",
"executionKey": "53c9bb4f-93ab-4d87-88df-5c23cd27b0e4",
"state": "SUCCESSFUL"
},
{
"expiresAt": "2021-12-08T12:31:59.19",
"executionKey": "fe5054c9-a61d-46a6-bfd5-1ebe2a5d66cc",
"state": "SUCCESSFUL"
},
{
"expiresAt": "2021-12-08T11:50:38.2",
"executionKey": "b26caeb1-1815-4055-9567-a857647d921c",
"state": "SUCCESSFUL"
},
{
"expiresAt": "2021-12-08T11:26:20.18",
"executionKey": "a31ad618-0fbb-4b10-9f20-f4b4db74f813",
"state": "SUCCESSFUL"
}
],
"totalElements": 6
}
Retrieve job status
To retrieve the job status for an asynchronous export job, call the Retrieve Payroll Export Asynchronous Request Status by Key (GET /v1/commons/payroll/export/async/{executionKey}/status) operation.
Calling GET /v1/commons/payroll/export/async/73dcbd9f-7119-411f-975a-721d567f95da/status returns:
{
"message": "Export request completed successfully",
"expiresAt": "2021-12-11T18:47:05.663",
"nextPing": 60,
"executionKey": "73dcbd9f-7119-411f-975a-721d567f95da",
"state": "SUCCESSFUL"
}
Retrieve response payload
To retrieve the response payload for an asynchronous export job, call the Retrieve Payroll Export Asynchronous Response Payload by Key (GET /v1/commons/payroll/export/async/{executionKey}/response) operation.
Calling GET /v1/commons/payroll/export/async/73dcbd9f-7119-411f-975a-721d567f95da/response returns:
Header Date: 12/09-2021 19:51:22
person_num,full_name,amount_type,apply_date,hours_amount,days_amount,money_amount,paycode_name,range_start_date,range_end_date
20335,"Adams, Eliza",HOUR,2021-11-08,19560,,,Regular,2021-11-08,2021-11-14
20335,"Adams, Eliza",MONEY,2021-11-09,,,233.5235,Bonus,2021-11-08,2021-11-14
Total Payroll Records,Total Payroll Seconds
2,19560.0
Export Small Batch Payroll Example
In this example, we export payroll for a small group. This operation is intended to be used with groups that result in a total expected execution time of 5 minutes or less. This is not an asynchronous operation.
Example request
Call the Export Small Batch Payroll (POST /v1/commons/payroll/export) operation with the following request payload.
{
"requestId": "f6506c28-27dc-41f8-b913-4b0322ecb2f2",
"query": "select person_num, full_name, amount_type, apply_date, hours_amount, days_amount, money_amount, paycode_name, range_start_date, range_end_date from `$Payroll`",
"exportConfig": {
"format": "csv",
"fileName": "TestPayrollLB.dat",
"delimiter": ","
},
"headers": [
{
"prefix": "Header Date: ",
"queries": [
"SELECT FORMAT_DATETIME('%m/%d-%Y %H:%M:%S', CURRENT_DATETIME())"
]
}
],
"footers": [
{
"prefix": "Total Payroll Records,Total Payroll Seconds "
},
{
"queries": [
"SELECT COUNT(person_num) FROM `$Payroll`",
"SELECT ','",
"SELECT SUM(hours_amount) FROM `$Payroll`"
]
}
]
}
Example response
A success response returns HTTP status code 200 and a CSV response body similar to the following example.
Header Date: 12/09-2021 19:53:24
person_num,full_name,amount_type,apply_date,hours_amount,days_amount,money_amount,paycode_name,range_start_date,range_end_date
20335,"Adams, Eliza",HOUR,2021-11-08,19560,,,Regular,2021-11-08,2021-11-14
20335,"Adams, Eliza",MONEY,2021-11-09,,,233.5235,Bonus,2021-11-08,2021-11-14
Total Payroll Records,Total Payroll Seconds
2,19560.0
Updated over 1 year ago