Custom Tables
Before staging payroll or accruals for export, you must create a custom table (with or without a schema). Each custom table acts as a lookup table, for example, you might create an Effective Wage Lookup Table. You must create and maintain custom tables to generate variance reports.
Prerequisites
There are no prerequisites for creating custom tables.
Example
In this example, we create, verify, update, and delete a custom table.
Create a custom table
The create request:
- contains form data with a set of files (paths) to upload to the system and accepts the table definition as a CSV file
- local files are supported
- the form-data entry key is
file
Example request
Call the Create Payroll Table (POST /v1/commons/payroll/tables/apply_create) operation with the following request payload.
- header
Content-Type: multipart/form-data
file=@"/Users/Payroll/CustomTableWageRate.csv
The form-data encoding requires the key passed as file and the value as the desired CSV file. Local files are supported.
Format the CSV based on the following basic example:
primary_job,base_wage_rate
Organization/United States/Metropolitan Plant/Machine Shop/Apprentice Welder,60
Organization/United States/Secondary Plant Location/Machinist,30
Example response
A success response returns HTTP status code 200 and a response body similar to the following example.
{
"inputType": "text/csv",
"inputName": "CustomTableWageRate.csv",
"inputSize": 175,
"outputType": "table",
"outputName": "hcm_max_prgtw7b_nonprd_01.CustomTableWageRate"
}
Retrieve all tables
To retrieve all payroll and accrual tables in the system, call the Retrieve All Payroll Tables (GET /v1/commons/payroll/tables) operation.
Calling GET /v1/commons/payroll/tables returns a response similar to the following example:
{
"records": [
{
"name": "API",
"type": "CUSTOM"
},
{
"name": "Accrual_20211113093439_55d5132b-f2b8-4824-bf29-afd0220e334e",
"type": "STAGE"
},
{
"name": "Accrual_20211116051451_a72f4c2a-83bc-46b0-8724-6c90b8695b9d",
"type": "STAGE"
},
{
"name": "Accrual_20211117083633_83ae263d-66a0-47e2-9c9a-f3f04053f03a",
"type": "STAGE"
},
{
"name": "Accrual_20211117090014_5431ab1a-c82b-4349-943d-14fdc8fd73f6",
"type": "STAGE"
},
{
"name": "Accrual_20211117101821_74991eb8-fdd7-41f2-a862-c85a8b2dd54e",
"type": "STAGE"
},
{
"name": "Accrual_20211118061118_21b86d7c-5abf-49b5-8e78-31c6561b22bd",
"type": "STAGE"
},
{
"name": "Accrual_20211122081130_0b1825b1-3767-4dec-bb01-3821aebcc34e",
"type": "STAGE"
},
{
"name": "Accrual_20211122081634_6e89831d-ccd6-4a4a-8d43-c546e1cc9295",
"type": "STAGE"
},
{
"name": "Accrual_20211122084316_a791a2a8-1f19-4512-a75f-e1f6f3f1135a",
"type": "STAGE"
},
{
"name": "Accrual_20211122084735_c8643e04-55a7-4390-b4ee-9b5103e11b8d",
"type": "STAGE"
},
{
"name": "Accrual_20211122090316_0647be82-8c22-4682-ade7-033f4a60be5e",
"type": "STAGE"
},
{
"name": "Accrual_20211122090639_2c3f357d-fecb-4748-aec1-0673401b17c9",
"type": "STAGE"
},
{
"name": "Accrual_20211122091448_57d6ef43-6236-4f38-8745-3ea938a82214",
"type": "STAGE"
},
{
"name": "Accrual_20211122111150_7e6629d0-17b2-424d-b83d-eb8dee45a02f",
"type": "STAGE"
},
{
"name": "Accrual_20211122111949_9bca138b-3e1a-4125-b748-18eeda7619e8",
"type": "STAGE"
},
{
"name": "Accrual_20211122112508_f7d8eb06-bfc3-4f9a-bb26-abafa197865f",
"type": "STAGE"
},
{
"name": "Accrual_20211122113225_8a9a4e3e-c39c-48ab-ae9b-f111a5c177bf",
"type": "STAGE"
},
{
"name": "Accrual_20211122113513_e60a7c4e-626a-49c5-9c5c-665172283c37",
"type": "STAGE"
},
{
"name": "Accrual_20211122114534_121d38dc-a094-4e2d-9aab-746487826c32",
"type": "STAGE"
},
{
"name": "Accrual_20211122114949_0d6a56b6-44e6-4f6a-b04e-da3bce16dc2d",
"type": "STAGE"
},
{
"name": "Accrual_20211123012840_1c5c7c87-4a66-4b5e-bee7-4c5849a4a19b",
"type": "STAGE"
},
{
"name": "Accrual_20211123014135_ebf96439-d6b4-4aa5-ac5e-6c0fe1b91861",
"type": "STAGE"
},
{
"name": "Accrual_20211123020450_eb87a9a0-7780-4767-8c8e-59c496700720",
"type": "STAGE"
},
{
"name": "Accrual_20211123021903_fd7c1405-fe18-46a8-9b0f-e88b94cb598a",
"type": "STAGE"
},
{
"name": "Accrual_20211123022454_911d57c9-d7f9-42ba-b26d-6e553e2879fc",
"type": "STAGE"
},
{
"name": "Accrual_20211123024050_7d6bcb06-aeff-45bc-9c22-d15a11ad21e4",
"type": "STAGE"
},
{
"name": "Accrual_20211123110001_5694fb60-e375-4666-a638-e2f263d2ae53",
"type": "STAGE"
},
{
"name": "Accrual_20211123112745_46f61cfa-1280-4dd3-a5f2-4e736089df06",
"type": "STAGE"
},
{
"name": "Accrual_20211124070456_9126c51d-d2ad-43e2-b9b5-bbc153ca78de",
"type": "STAGE"
},
{
"name": "Accrual_20211124122043_ed5f3ba2-956a-422f-9944-7e759d097224",
"type": "STAGE"
},
{
"name": "Accrual_20211130112947_768f048f-95f0-4869-912e-9d24617ef545",
"type": "STAGE"
},
{
"name": "Accrual_20211206012019_63798866-5a5a-463d-bb40-5e8dd6eff551",
"type": "STAGE"
},
{
"name": "Accrual_20211206012157_8b584359-80ae-41fc-a3bf-1fc1f98c6703",
"type": "STAGE"
},
{
"name": "Accrual_20211206012448_908a2332-b352-4597-bde6-2f79f1e9c477",
"type": "STAGE"
},
{
"name": "Accrual_20211206012749_683328b4-1d8f-410d-ad56-636fc792dc65",
"type": "STAGE"
},
{
"name": "Accrual_20211206113400_2c3a772d-5c07-4414-a739-007693104164",
"type": "STAGE"
},
{
"name": "Accrual_20211206123508_7beb6f8b-a8b4-4efa-bb74-0bc92dbf139f",
"type": "STAGE"
},
{
"name": "Accrual_20211206123552_3db462ca-a19d-45c1-837c-1eb67b1dafd0",
"type": "STAGE"
},
{
"name": "Accrual_20211207022741_92314631-ae01-49ec-b512-08120a7dd9a6",
"type": "STAGE"
},
{
"name": "Accrual_20211207094503_7ab11c2a-98b7-430f-b277-07494729594c",
"type": "STAGE"
},
{
"name": "CustomTableWageRate",
"type": "CUSTOM"
},
{
"name": "Payroll_20211115015815_351e4c30-8973-4ce8-a81b-fdcfb5d5c29c",
"type": "STAGE"
},
{
"name": "Payroll_20211115113446_f6506c28-27dc-41f8-b913-4b0322ecb2f2",
"type": "STAGE"
},
{
"name": "Payroll_20211116044136_2f69fb82-dd50-464e-8ee1-58b0abb4d9c6",
"type": "STAGE"
},
{
"name": "Payroll_20211116051451_a72f4c2a-83bc-46b0-8724-6c90b8695b9d",
"type": "STAGE"
},
{
"name": "Payroll_20211116075507_d7c33473-2283-4034-b7ac-7aa3cb20e268",
"type": "STAGE"
},
{
"name": "Payroll_20211116084550_8e5b3ccb-0c97-4a1c-b77f-d44fe9c580ba",
"type": "STAGE"
},
{
"name": "Payroll_20211116085214_34ec50bf-f379-49fb-8ec4-3cea7d9745f7",
"type": "STAGE"
},
{
"name": "Payroll_20211116085930_fcd371eb-240b-4969-a63c-f0300c0aa1e6",
"type": "STAGE"
}
],
"totalElements": 50
}
Note: STAGE table names use the following name format: type_dateStamp_requestId
Verify a single table
To verify the contents of a single table, call the Retrieve Payroll Table by Name (GET /v1/commons/payroll/tables/{name}) operation.
Calling GET /v1/commons/payroll/tables/CustomTableWageRate returns:
{
"name": "CustomTableWageRate",
"type": "CUSTOM",
"rows": 2,
"creationTime": "2021-12-10T17:35:28",
"schema": [
{
"mode": "NULLABLE",
"name": "primary_job",
"type": "STRING"
},
{
"mode": "NULLABLE",
"name": "base_wage_rate",
"type": "INTEGER"
}
]
}
The partitionConfig object can be returned in the response when the table type is CUSTOM. Calling GET /v1/commons/payroll/tables/{name} for such a custom table returns a response similar to the following:
{
"name": "payroll_dates",
"type": "CUSTOM",
"rows": 34,
"creationTime": "2021-12-20T08:35:01",
"partitionConfig": {
"column": "Time",
"granularity": "DAY",
"expirationHours": 4
},
"schema": [
{
"mode": "NULLABLE",
"name": "range_start_date",
"type": "DATE"
},
{
"mode": "NULLABLE",
"name": "Time",
"type": "TIMESTAMP"
},
{
"mode": "NULLABLE",
"name": "person_num",
"type": "INTEGER"
},
{
"mode": "NULLABLE",
"name": "hours_amount",
"type": "FLOAT"
}
]
}
Update an existing custom table
To update an existing custom table, call the Create or Update Payroll Table (POST /v1/commons/payroll/tables/apply_upsert) operation with the following request payload.
Example request
{
"query": "select range_start_date, CURRENT_TIMESTAMP() as Time, COUNT(person_num) as person_num,SUM(hours_amount) as hours_amount from `Payroll_20211122084735_c8643e04-55a7-4390-b4ee-9b5103e11b8d` GROUP BY range_start_date",
"tableName": "payroll_dates",
"partitionConfig": {
"column": "Time",
"granularity": "DAY",
"expirationHours": 4
}
}
Example response
A success response returns HTTP status code 200 and a response body similar to the following example.
{
"status": "APPENDED",
"rows": 1
}
Note: If the operation creates a new table, the status returned is CREATED. If the operation modifies an existing table, the status returned is APPENDED.
Delete the custom table
To delete this custom table, call the Delete Payroll Table by Name (DELETE /v1/commons/payroll/tables/CustomTableWageRate) operation.
The system returns HTTP status 204 with an empty response body.
Updated 11 months ago