Employee Pay Summary

6min
For the Employee Pay Summary entity the EMPLOYEE_PAY_SUMMARY table is created in your Target connection. Depending on your business processes in how time entries are managed in Ceridian, you may see a trickle down effect in Target table. Below are steps to handle several situations that may be impacted by your Ceridian Dayforce processes.
Manually Updated Timesheets in Ceridian
There are situations where a person has issues punching a clock and the time recorded in Ceridian needs to be manually adjusted. It's possible these adjustments are made by a manager in Ceridian several days later in order to have accruate information prior to a payroll run.
By default, a Sync Bridge replicates the last 30 days worth of Employee Pay Summary records.
When this situation occurs, Ceridian does not provide a field in their API to indicate that a punch has changed and provides the updated record as a new record in the API. To account for this situation a Transformation﻿ needs to be created in DataLakeHouse.io in order to mark the __DLH_IS_DELETED flag to TRUE. 
Create a Transformation with Pre-SQL Update
Login to the Target destination and create a copy or clone of the EMPLOYEE_PAY_SUMMARY table. The example below is to create a clone of the table in Snowflake:
CREATE TABLE EMPLOYEE_PAY_SUMMARY_BKUP CLONE EMPLOYEE_PAY_SUMMARY;
Login into DataLakeHouse.io and click on Transformations﻿
Select the option for PRE-SQL
Enter a PRE-SQL update statement such as this one below and then save the transformation 
UPDATE DATALAKEHOUSE_DB.PRODDB_CERIDIAN_DAYFORCE.EMPLOYEE_PAY_SUMMARY 
     SET __dlh_is_active = false, __dlh_is_deleted = true
 WHERE EMPLOYEE_XREF IN (
     SELECT DISTINCT(EMPLOYEE_XREF) FROM 
         DATALAKEHOUSE_DB._TMP_PRODDB_CERIDIAN_DAYFORCE.EMPLOYEE_PAY_SUMMARY 
 )
 ;
After you've entered the Pre-SQL logic:
Complete the remainder of the required fields
Save the transformation form
Next time the bridge runs and completes a load for the EMPLOYEE_PAY_SUMMARY table your transformation and the update statement will run prior to loading the data into your target schema.
Pay Summary Amounts Not Matching Ceridian Dayforce
When writing queries against the EMPLOYEE_PAY_SUMMARY table you may find that the total amounts are not matching the pay stub in Ceridian Dayforce. This is due to they fact that the Ceridian API is not providing a way to uniquely identify the records in this entity which causes the perception that there are duplicate rows. Since these are not duplicates the following pre-SQL Transformation﻿ needs to be created using the SQL below. 
Create a Transformation with Pre-SQL Update
Login to the Target destination and create a copy or clone of the EMPLOYEE_PAY_SUMMARY table. The example below is to create a clone of the table in Snowflake:
CREATE TABLE EMPLOYEE_PAY_SUMMARY_BKUP CLONE EMPLOYEE_PAY_SUMMARY;
Login into DataLakeHouse.io and click on Transformations﻿
Select the option for PRE-SQL
Enter a PRE-SQL update statement such as this one below and then save the transformation. Replace the bolded text in the script below with your details.
-- Remove the old records from the last run so that the number of records in the table do not grow out of control.

DELETE FROM database.schema_CERIDIAN_DAYFORCE.EMPLOYEE_PAY_SUMMARY
WHERE __DLH_IS_DELETED = true AND TIME_END IS NULL AND PUNCH_SEGMENT_START IS NULL;

-- Update the records in the table from the last run so that this bridge sync loads the current records.

UPDATE database.schema_CERIDIAN_DAYFORCE.EMPLOYEE_PAY_SUMMARY EPS SET EPS.__DLH_IS_DELETED = true, EPS.__DLH_IS_ACTIVE = false
FROM (SELECT EMPLOYEE_XREF, PAY_DATE, POSITION_XREF, RATE, PAY_CODE_XREF_CODE, PAY_CATEGORY_XREF_CODE FROM database._TMP_schema_CERIDIAN_DAYFORCE.EMPLOYEE_PAY_SUMMARY) SEP
WHERE EPS.EMPLOYEE_XREF = SEP.EMPLOYEE_XREF
AND EPS.PAY_DATE = SEP.PAY_DATE
AND EPS.POSITION_XREF = SEP.POSITION_XREF
AND EPS.RATE = SEP.RATE
AND EPS.PAY_CODE_XREF_CODE = SEP.PAY_CODE_XREF_CODE
AND ((EPS.PAY_CATEGORY_XREF_CODE = SEP.PAY_CATEGORY_XREF_CODE) OR (EPS.PAY_CATEGORY_XREF_CODE IS NULL AND SEP.PAY_CATEGORY_XREF_CODE IS NULL))
AND EPS.TIME_END IS NULL AND EPS.PUNCH_SEGMENT_START IS NULL;
After you've entered the Pre-SQL logic:
Complete the remainder of the required fields
Save the transformation form
Next time the bridge runs and completes a load for the EMPLOYEE_PAY_SUMMARY table your transformation and the update statement will run prior to loading the data into your target schema.
Updated 14 Mar 2024
Did this page help you?
Dayforce Reports to Data Warehouse Target
Employee Work Assignment