SPI Vancouver Workday Interface Requirement to ADP Paywork - EZL 3-20-14 ver 19_signed
SPI – Vancouver / ADP Payroll (Imageworks)
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
SPI – Vancouver / ADP Payroll (Imageworks)
Workday to ADP Pay@Work and EZLabor Interface
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
Revision History
| Date | Version | Description | Author |
|---|---|---|---|
| 4/16/2013 | 1.0 | Initial Draft | Kim Arnold |
| 2/11/2014 | 1.1 | Updated Error Handling and Field requirements | Kim Arnold |
| 2/18/2014 | 1.2 | Updated Error Handling | Kim Arnold |
| 2/19/2014 | 1.3 | Updated after receiving input from ADP | Kim Arnold |
| 2/26/2014 | 1.4 | Update and review of Error Handling | Kim Arnold |
| 2/27/14 | 1.5 | Update Control File Requirements and Error Handling | Karen Dedo |
| 3/3/14 | 1.6 | Updated Error Handling with ADP Email address | Kim Arnold |
| 3/11/14 | 1.7 | Updated based on meeting with ADP and Workday Team on 3/7/14 | Kim Arnold |
| 3/12/14 | 1.8 | Updated Section 4 Error Handling based on meeting with ADP and Workday team on 3/12/14 | Kim Arnold |
| 3/22/14 | 1.9 | Added trailer record layouts, updated file name conventions and Sec 3.2 ADP Process, based on review with ADP and Workday team on 3/20/14. | Karen Dedo |
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
Table of Contents
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
Introduction
Sony Pictures Imageworks (SPI) uses Workday to maintain and manage all employee HR related information. In the U.S., Workday provides the Sony payroll systems with new hire information, rates changes, terminations and other payroll related data. SPI Canada uses ADP Pay@Work and ADP's EZLabor system for payroll processing and time and labor management. Currently, any new hire or changes to employee information is entered into Workday. The SPI Canada Payroll Administrator uses a report from Workday and manually updates the report with the ADP required employee information. That spreadsheet is then sent to ADP and manually entered it into their systems
This document includes the requirements for automating the Workday to ADP Pay@work and EZLabor interfaces.
1. Workday Interface to ADP Pay@Work
SPI Vancouver's payroll week ends Saturday's at 12:00 midnight PT. This means that in order for an employee to enter their timecard in EZLabor and have hours included in the next payroll cycle, all new hires must be loaded into the ADP systems before 9:00am PT Fridays. This Interface should run every week on Friday AM just after midnight (12:05:00am)
The interface from Workday that will load data into Pay@Work is actually two files, one with New Hires information and the other with Employee Updates/Changes and Terminations. The requirements for both are nearly identical and the requirements in this section apply to both unless specifically noted otherwise.
The interfaces to Pay@Work MUST be run BEFORE the interface to EZLabor. Once updated, Pay@work will populate EZLabor with the New or Updated information about the employee. The interface from Workday to EZLabor will contain information about the employee that is unique to and required by EZLabor.
1.1 ADP Pay@work Import File Requirements
|
The following characters will not be accepted in the import:
|
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
1.2 Data File Requirements
1.2.1 File Name Conventions
- ADP company code: R0FU (zero not the letter O)
- Description of File: New Hires or Emp Updates
- Payroll week-ending date (MM/DD/YYYY).
- Time Stamp
EXAMPLE: R0FU_SPI_New_Hires_06_16_2014_12:05:00.csv
1.2.2 Data Population
- SAP Company Code: 5283
- Current Week New Hire File: New and Re-Hires during the Mon-Thurs of the week the interface is run AND any New/Hires from the Friday prior.
- Current Week Change File: Employees with changes to Pay@Work data and Status Changes (Term/Inactive) during the Mon-Thurs of the week the interface is run AND any from the Friday prior.
Interface files cannot include FUTURE New Hires/rehires, or changes. - A complete record for each is required
- If there are no records for the week, generate a blank file(s) with a control total of 0
1.2.3 Data Record Layout
- If data exceeds the length of the ADP field it will need to be truncated during the generation of the interface file.
- All fields must be populated with data unless specifically noted below.
- The New Hire file will contain only the Y fields listed in Green below
- The Change Termination file will contain only the Y fields listed in Blue below
| ADP Header | ADP Format | Sample | New Hire File | CHG File | Notes: |
|---|---|---|---|---|---|
| ADP Company Code | A(4) | R0FU | Y | Y | Zero not O |
| Last Name | A(20) | Smith | Y | Y | |
| First Name | A(15) | Joe | Y | Y | |
| Initial | A(2) | B | Y | Y | Can be blank |
| Soc. Ins. No. | A(9) | 555222777 | Y | Y | no spaces or dashes. Ok to be Blank if emp does not have |
| Birth Date | MM/DD/YYYY | 4/15/1968 | Y | Y | |
| Employee ID | A(9) | 00605123 | Y | Y | REQUIRED – Qualifier Field Must have leading Zero's (zero fill to 8) |
| EZLabor Employee ID | A(9) | 00605123 | Y | Y | Same value as Employee ID |
| Add Employee to EZL | A(1) | Y | Y | Y | Default to Y |
| Emp Exists in EZL | A(1) | Y | Y | Y | Default to Y |
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
| ADP Header | ADP Format | Sample | New Hire File | CHG File | Notes: |
|---|---|---|---|---|---|
| Branch # | A(1) | blank | Y | Y | Required ADP Field. Not currently used by Sony, so this will be blank or Null |
| Department # | A(6) | blank | Y | Y | Required ADP Field - Not currently used. This may be used when the GL Interface is implemented Note: Once updated, all Payroll reports will be sorted by this field |
| Street | A(24) | Y | Y | ||
| City | A(24) | Y | Y | ||
| Province | A(2) | BC | Y | Y | |
| Postal / Zip Code | LNLNLN | Y | Y | ||
| Hire Date | MM/DD/YYYY | Y | Y | Hire date or if a US employee, the date the start in Vancouver | |
| EI Occupation | A(16) | Y | Y | Job Title | |
| Language | A(1) | E | Y | Y | E = English (Default) F = French |
| Employment Province | N(2) | 03 | Y | Y | 03 Default (See Section 5.1 for complete list) |
| One Tax Form Flag | Yes or No | YES | N | Y | Only needed if the Employment Province is changing. Can default to YES |
| Residence Province | N(2) | Y | Y | See Section 5.1 for complete list | |
| Payroll Status | A(1) | A | Y | Y | A=Active I=Inactive, T=Term |
| Termination Date | MM/DD/YYYY | N | Y | Mandatory if Status is T or I Workday Date = Term Date | |
| Pay this Pay Period | Yes or No | N | Y | Mandatory if Status is T or I. Default to NO Blank if Status is A or unchanged | |
| Payroll Type | A(1) | Y | Y | S = Salary H = Hourly E = Exception Hourly See Section 0 for Business Logic |
|
| Rate | N(9,4) | Y | Y | Hourly Rate | |
| Standard Hours | N(9,2) | 40 | Y | Y | Default to 40 |
| Salary per pay | N(9,2) | If emp is hourly ok to be blank | Y | Y | Weekly Base wage If employee is Hourly= Blank |
| Payment Type | A(1) | C | Y | N | C = Check Default |
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
1.2.4 Trailer Record Layout
| Field Name | Format | Sample | Notes: |
|---|---|---|---|
| ADP Company Code | A(5) | ##### | Value will always be ##### which are invalid characters so the import process will not load to ADP. |
| Control Total | 9(10) | 106 | Record Count for Pay@work New Hires or Emp Update file. |
2. Workday Interface to ADP EZLabor
2.1 ADP EZLabor Import File Requirements
- Required format: Comma Separated Value (.CSV or .TXT)
- Max File size:
- Max # of rows per file:
- Employee ID Must be the first column
- Text Fields cannot contain commas
- Invalid Character for Non ID Fields
(\) – Back Slash
(<) – Less Than
(>) -- Greater Than
2.2 Data Requirements
2.2.1 File Name Conventions
- ADP company code ( R0FU),
- Description of File: EZLabor
- Payroll week-ending date (MM/DD/YYYY).
- Time Stamp
EXAMPLE: R0FU_SPI_EZLabor_06_16_2014_12:06:00.csv
2.2.2 Data Population
- SAP Company Code: 5283
- Current Week: New/Re-Hires, Employees with changes to EZLabor data and Status Changes (Term/Inactive) during the Mon-Thurs of the week the interface is run AND any from the Friday prior.
Interface files cannot include FUTURE New Hires/rehires, or changes. - A complete record for each is required
- If there are no records for the week, generate a blank file with a control total of 0
2.2.3 Header & Data Record Layout
- A header record is required for the EZLabor file. All names in the header record must be in all CAPITAL S as shown in the format below.
- For the data record, if data exceeds the length of the ADP field it will need to be truncated
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
during the generation of the interface file.
- All fields must be populated with data unless specifically noted below
| ADP Field Name | ADP Format | Sample | Notes |
|---|---|---|---|
| EMPLOYEEID | 25 | 00605123 | Required, qualifier field |
| USERID | 25 | SPI User ID | |
| COMPANYCODE | 4 | R0FU | |
| LASTNAME | 25 | ||
| FIRSTNAME | 25 | ||
| PAYROLLID | 25 | 00605123 | Same as Employee ID |
| BADGE | 15 | 00605123 | Same as Employee ID |
| PAYGROUPID | 25 | See Section 0 Paygroup must be Active in EZLabor |
|
| ADMINFLAG | 1 | T or F | Calc Field. F unless employee is on Supervisor List as Admin |
| SUPERVISORID | 25 | '00603654 | This is the ID of the Timecard Approver. This is based on the Security group the emp is assigned to. |
| SUPERVISORFLAG | 1 | T or F | Calc Field. F unless employee is on the Supervisor list |
| EMPLOYEESECURITYGROUPID | 25 | Digital Billable | See Section 0 |
| EMPLOYEESECURITYGROUPSTARTDATE | MM/DD/YYYY | Date employee starts in this security group. Probably hire date – can be an effective date | |
| EMPLOYEESECURITYGROUPENDDATE | MM/DD/YYYY | Default to 12312099 unless the Emp is changing Security Groups. | |
| USERSECURITYGROUPID | 25 | Will be blank for most employees | Will pull from Supervisor list See Section 5.4 |
| USERSECURITYGROUPSTARTDATE | Date employee starts as approver for this security group. Probably hire date – can be an effective date | ||
| USERSECURITYGROUPENDDATE | Default to 12312099 unless the Emp is changing Security Groups. | ||
| DATEID1 | HIRE | Default the work HIRE |
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
| ADP Field Name | ADP Format | Sample | Notes |
|---|---|---|---|
| EMPLOYEEDATE1 | MM/DD/YYYY | Same date used in Pay@work. Workday date = Hire Date or if US emp, the date they started in Vancouver | |
| DATEID2 | REHIRE | Default the word REHIRE | |
| EMPLOYEEID2 | MM/DD/YYYY | Workday date = Most Recent Hire Date | |
| DATEID3 | ACCRU | Default the word ACCRU | |
| EMPLOYEEID3 | MM/DD/YYYY | Workday date = Continuous Service Date Year Only – default month and day to 1/1 (Jan 1st) | |
| COCODE | 5283 | SAP Company Code | |
| STATUS | A = Active, I=Inactive, T=terminated | ||
| TERMINATIONCODEID | Term | Blank if Status is A or I, "TERM" if Status is T | |
| TERMINATIONDATE | MM/DD/YYYY | ||
| USERROLEID | 25 | Calc Field based on Supervisor List EMPLOYEE SUPERVISOR CLIENTADMIN | |
| USERGROUPID | Calc Field based on Supervisor List EMPL SUPVR ADMIN | ||
| USERACCESSFLAG | 1 | T or F | If status is A = T (Access Granted) If status is I or T = F (Access Denied) |
| CULTURE | En-CA | Default to En-CA | |
| TRANSFERTOPAYROLL | 1 | T | Default to T |
| TIMEZONE | PST | Default to PST |
2.2.4 Trailer Record Layout
| Field Name | Format | Sample | Notes: |
|---|---|---|---|
| ADP Company Code | A(5) | ##### | Value will always be ##### which are invalid characters so the import process will not load to |
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
| ADP. | |||
| Control Total | 9(10) | 106 | Record Count for EZ Labor New/Re-Hires and Employees with changes to EZLabor data. |
2.2.5 File Sort Order
The EZLabor interface file should be sorted in the following order:
- Employee Changes listed first
- New Hires
- Terminations listed last
2.2.6 Security Group Changes
If an employee changes security group, two records are required. The first, would include the current Security Group ID and with an END Date. The second would include the New Security Group ID and with a New Start date
Record 2: Creative Billable, Start Date: 2/15/2015, End Date: 1/1/2099
This also applies to the USER Security field.
3. Interface & Data Transport Process
3.1 SPI Process
- Weekly (FRIDAY morning at 12:05am) generate the following data and control files:
- Workday to Pay@Work New Hires for the week
- Workday to Pay@Work Employee Changes/Terminations
- Workday to EZLabor
- Deliver weekly file to ADP's sFTP site.
- Send confirmation email of file generation to: YVR_payroll@Imageworks.com and HR_Tech@spe.sony.com
3.2 ADP Process
- Retrieve Weekly files from sFTP site
- Import Workday to Pay@Work New Hires file
- Verify that control total in trailer record matches Pay@work New Hires records received
- Import Workday to Pay@Work Employee Changes/Terminations file
- Verify that control total in trailer record matches Pay@work Changes records received
- "Sync" Pay@Work to EZLabor
- Verify that control total in trailer record matches EZ Labor records received
- Import Workday to EZLabor file
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
4. Error Handling
4.1 Email Contacts
SONY Workday Support Team (WST): HR_Tech@spe.sony.com
ADP EZLabor Interface Issues: Canada_Halifax_COS_Nationals@adp.com AND Canada.ManagedTLM@adp.com
ADP Pay@Work Interface Issues: Canada_Halifax_COS_Nationals@adp.com
4.2 Error Handling Table
| # | ERROR or ISSUE | Comments/Possible Handling |
|---|---|---|
| 1 | Errors as result of Import load validations. Examples include but are not limited to:
|
If Pay@work interface
|
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
| # | ERROR or ISSUE | Comments/Possible Handling |
|---|---|---|
| 2 | Termination ID is Term, but there is no value for Term Date. Or Term Date has a value, but status is Active | If Pay@work interface
|
| 3 | Control record count included in the file name does not match the number of records received. | If Pay@work interface
|
| 4 | sFTP or other failure results in ADP not receiving a file from SPI. Note: There should be 3 Sony files each week even if there are no changes in which case blank files are sent. |
If Pay@work interface
|
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
| # | ERROR or ISSUE | Comments/Possible Handling |
|---|---|---|
|
||
| 5 | Employee comes through the interface as a Termination, but the ADP COS team has NOT received the appropriate manual paperwork. Reverse as well: ADP COS has the appropriate manual Termination paperwork, but the employee does not come through as a term in the interface files |
ADP Payroll Support Team (Jennifer Collins) will need to monitor this and refer back to SPI Vancouver Payroll if there are issues |
| 6 | Non-Required fields are empty, however they are fields that are needed – for example a Social or Job Description | ADP Payroll Support Team (Jennifer Collins) will need to monitor this and refer back to the Workday Support Team and/or Vancouver payroll depending on the issue |
5. Tables or Valid Field Values
These Tables are valid as of the Go-Live date. Some tables, such as Paygroup, Security Group and Supervisor list will change over time and will require ongoing maintenance.
5.1 Pay@Work Employment Province or Residence Province (ADP Mandated)
| Valid Code | Province | |
|---|---|---|
| 04 | = | Alberta |
| 03 | = | British Columbia |
| 06 | = | Manitoba |
| 08 | = | New Brunswick |
| 09 | = | Newfoundland/Labrador |
| 00 | = | Non Resident |
| 07 | = | Nova Scotia |
| 11 | = | Northwest Territory |
| 13 | = | Nunavut |
| 01 | = | Ontario |
| 10 | = | Prince Edward Island |
| 02 | = | Quebec |
| 05 | = | Saskatchewen |
| 12 | = | Yukon Territory |
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
5.2 EZLabor Paygroup (Sony Specific)
| Paygroup Name | Payroll Type | |
|---|---|---|
| SALARY EZL | = | S |
| SALARY QC EZL | = | S |
| SALARY PREM EZL | = | E |
| SALARY PREM TK | = | E |
| SALARY NON PREM EZL | = | S |
| SALARY NON PREM TK | = | S |
| SALARY QC NON PREM TK | = | S |
| PST SHIFT | = | E |
| HOURLY FT EZL | = | H |
| HOURLY FT TK | = | H |
| HOURLY 4HR EZL | = | H |
| HOURLY 4HR TK | = | H |
| HOURLY 6HR EZL | = | H |
| HOURLY 6HR TK | = | H |
5.3 EZLabor Security Groups and User Groups (Sony Specific)
| Security Group |
|---|
| Artist Management |
| Creative Billable |
| Dev Billable Back |
| Dev Billable Front |
| Dev Billable Mid |
| Digital Billable |
| Facilities |
| Featr Prod Billable |
| Operations |
| Operations Sr |
| PO |
| PO Sr |
| PST Billable |
| Payroll |
| Production Accounting |
| Sys Billable |
| Sys Billable SR |
| Training |
Proprietary and Confidential
| SPI - Vancouver (Imageworks) | Version: 1.9 |
| Workday to ADP Pay@Work and EZLabor Interface | Date: 3/22/14 |
5.4 Supervisor List
The Supervisor list will need to contain the following information:
- Employee Name
- Employee ID
- Security Group: (this is the security group the supervisor is allowed to approve timecards for. In EZLabor it's called the USER Security Group
- User Role: Admin or Supervisor
6. Approval
I approve the SPI Workday to ADP Pay@Work/EZLabor Interface Requirements Document. This document will be used to implement the Workday to ADP Pay@Work/EZLabor Interfaces.
Director, Production Finance, Imageworks
Proprietary and Confidential