Employee Pay Summary

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 sql transformations docid 5zjgrvbhtywqw8 olioh0 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 sql transformations docid 5zjgrvbhtywqw8 olioh0 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