Integration - ADP/EV5 And PeopleSoft Financials

Transcription

Integration - ADP/EV5 and PeopleSoft FinancialsPeriodic Reporting ToolsTABLE OF CONTENTSType of ReconReportYear EndMonth EndAudit ToolsReport/Query NameEmployee PaycheckPayroll YTD SummaryCompensated Absences QueryCompare Job RatesSalary DataRetirement Contributions by ProviderBenefits Summary7/5ths Faculty Reconciliation Jan-JulBuild Retiree Recv. Aging DataRetiree Receivables Aging QueryOpen Coupon QueryPayroll Deduction RegisterPayroll Register by Ded. CodeBenefit Process AuditJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST FinancialsPage #241214151619202122242627281 Page01.11.16

Periodic Reporting ToolsFor Year EndRecon Tool TypePathWhat this reportprovidesAuditor Item #YE Checklist #Data Source(s)ADP Employee Paycheck (BORH003)Year EndBOR Menus - BOR Payroll - BOR Payroll Reports - Employee PaycheckThis report shows employee's detailed paycheck data for a specified timeperiod. Users can run the report for a list of employees or for ALL employeesfor their business unit.93(used for employee compensation testing)81ADP JOBADP PAY CHECKRun PageOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials2 Page01.11.16

Person,TestJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials00904923 Page01.11.16

Recon Tool TypePathWhat this report providesAuditor Item #YE Checklist #Data Source(s)Payroll YTD Summary (BORR133A)Year EndBOR Menus - BOR Payroll - BOR Payroll Reports - Payroll YTD SummaryThis report shows institution's payroll YTD summary data for a specifiedtime period. Confirmed payroll processes within the selected report runparameters will be reflected in this report’s summary totalsThis report allows users to get the same type of payroll summariesinformation the EV5 PAY018 (Payroll Summary) report provided, butinstead of by individual Pay Run ID, users can run this report by a daterange. Therefore, this can be used as a tool for month end reconciliation.N/AN/AADP PAY CALENDRADP PAY CHECKADP PAY DEDUCTNADP PAY ERNINGSADP PAY OTH ERNADP PAY SPC ERNADP PAY TAXRun PageOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials4 Page01.11.16

Job Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials5 Page01.11.16

Job Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials6 Page01.11.16

Job Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials7 Page01.11.16

Job Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials8 Page01.11.16

Job Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials9 Page01.11.16

Job Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials10 P a g e01.11.16

Job Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials11 P a g e01.11.16

Compensated Absences QueryRecon Tool TypePathWhat this reportprovidesYear EndBOR Menus - BOR Payroll - BOR Payroll Reports - Compensated AbsencesQueryCompensated Absences Query (BOR ADP COMPABS) originated from thePeopleSoft HRMS Compensated Absences Report (BORR139) users ran as part ofthe Fiscal Year End Audit process. This query provides information for use by theauditors concerning value of employee vacation balances, and hence shows theinstitution’s liability of employee vacation pay as of the end of a fiscal year.Employee leave data is now stored in the ADP eTime system. The CompensatedAbsences process begins with ADP generating the initial report from eTime. SSCprovides the data/report to ITS to load into PeopleSoft Financials.One potential issue with the data from eTime is the accuracy of the hourly rate asof the fiscal year end. The hourly rate is part of Job data and is stored in a separatesystem, EV5. That piece of data is fed to eTime via an EV5 nightly process (Exportto Enterprise eTIME - TAA028). However, the hourly rate picked up by the processis the hourly rate of the employee as of the date the process is run. Since thisdata is not effective-dated in eTime, when this report is run AFTER the fiscal yearend AND if an employee has a future pay raise job row, the newly increased jobrate will be used instead of the job rate PRIOR to the Fiscal Year End.To correct the job rate issue, the Compensated Absences Query(BOR ADP COMPABS) is joined to EV5 Job Data (ADP JOB), which is pulled intoPS Financials daily, in order to get the employee’s hourly rate as of the fiscal yearend.Auditor Item #YE Checklist #Data Source(s)Currently, ADP generates the Compensated Absences report weekly on Saturdayand SSC provides ITS the report Monday morning to load the PeopleSoftFinancials.33 & 3490BOR COMPABS VWRun ParametersCurrent OutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials12 P a g e01.11.16

New Outputs after Release 2.41, with three additional fieldsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials13 P a g e01.11.16

Compare Job RatesRecon Tool TypePathWhat this reportprovidesYear EndBOR Menus - BOR Payroll - BOR Payroll Reports - Compare Job RatesThis query shows a list of employees who have a different hourly ratebetween the Fiscal Year End Date and the Comp Absence Report Dateentered.User puts in two dates on Prompt: 1) Fiscal Year End Date, 6/30/XX; and 2)Comp Absence Report Date. Query will show the hourly rates of eachemployee as of 6/30/XX AND as of the Comp Absence Report date.For example, if you put in Fiscal Year End Date of 6/30/12 and Comp AbsenceReport Date of 7/5/12, it will show you a list of employees who have theirhourly rate changed between 6/30/12 and 7/5/12. This query is particularlyuseful when user is also running the Compensated Absences Query AFTERfiscal year end date.Auditor Item #YE Checklist #Data Source(s)Note: Although the prompts are labeled as Fiscal Year End Date and CompAbsence Report Date, users can use this query to find out whose hourly ratehas changed over a certain period of time.N/AN/AADP JOBRun ParametersOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials14 P a g e01.11.16

Salary DataRecon Tool TypePathWhat this reportprovidesAuditor Item #YE Checklist #Data Source(s)Year EndBOR Menus - BOR Payroll - BOR Payroll Reports - Salary DataThis query provides user a "snapshot" of their employees (regardless of their status),paygroup, and salary grade, and compensation data as of a certain effective date.9492ADP JOBRun ParametersOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials15 P a g e01.11.16

Retirement Data by ProviderRecon Tool TypePathWhat this reportprovidesYear EndBOR Menus - BOR Payroll - Common Remitter - Retirement - RetirementInquiry - Ret Provider Summary HistThis query shows Retirement Contribution Data by Provider for a specific monthor for the fiscal year for annual reporting data.This query should be run for each month and downloaded or copied into anExcel spreadsheet to compile the entire Fiscal Year data. The data may then befiltered or pivoted for each provider or for all providers.Auditor Item #YE Checklist #Data Source(s)Note: This query is created from the BOR CR WRK HST table and contains onlynon-deleted data rows with a Valid data row status. This report may be used inconjunction with the monthly reconciliation process. Invoices/Adjustmentsissued by a Provider will not be included in this query and will require that theybe added to the appropriate total to reconcile with general ledger.N/AN/ABOR CR WRK HSTBOR CR SUMPV VWRun ParametersOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials16 P a g e01.11.16

Spreadsheet ViewOnce all months for the Fiscal Year have been placed in an Excel spreadsheet the data may be filteredby either the Plan or by the Provider. Excel Spreadsheet data may also used to create a pivot table forall providers/plans for annual reporting.Note: Filtered data data is shown for each month and the associated calendar year within the FiscalYear.Data should be totalled by Provider to obtain the total Employer contribution amount for the FY. Thebelow example is filtered for the Provider FID. Reporting by provider will require that the data befiltered and totalled multiple times.Job Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials17 P a g e01.11.16

Pivot Table ViewInserting a Pivot table will easily return the annual total for each provider and avoid multiple filteringwhen using the spreadsheet alone.Data in the Pivot Table may also be filtered to narrow the results.Job Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials18 P a g e01.11.16

Benefits SummaryRecon Tool TypePathWhat this reportprovidesAuditor Items #YE Checklist #Data Source(s)Known IssueYear EndBOR Menus - BOR Payroll - BOR Payroll Reports - Benefits SummaryThis Benefits summary view shows for a given period of time, for each month,by employee status, by each plan ID, how many employees are in thatparticular plan, and how much the employee and the employer contributionsare respectively.Note: The Employee Count shown on the query is for EACH MONTH, so youmay want to get an average numbers of the Count for the whole 12 monthsinstead.63, 6583BOR ADP CES TBLBOR ADP CESP VWThis query currently picks up all CES transaction types: Normal, Corrections,and Retro charges, therefore, inflating the counts by including the correctionsand retro rows. The transaction codes will allow you to count only the “N”(normal transaction) rows to obtain an accurate enrollment count. However,all rows should be used in determining costs for employee and employerpremium amounts in order to match the monthly benefits impound invoice.Run ParametersOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials19 P a g e01.11.16

Recon Tool TypePathWhat this reportprovidesAuditor Item #YE Checklist #Data Source(s)7/5 ths Reconciliation Jan-JulAuditor ToolBOR Menus - BOR Payroll - Benefit Reconciliation - Benefit Accounting Benefit Inquiry/Reports - 7/5ths Reconciliation Jan-JulThis query shows for a given calendar year, employees in paygroups subjectto the 7/5ths calculation for their payroll deduction and the CES amount foreach deduction code for each month (based on the Journal Date). It is aprogression of CES premium charges vs 7/5 deductions from Jan-May withthe expectations of having 1 month of benefits accrual in the liabilityaccounts at FY End. This will help locate any 7/5 discrepancies for the wholeacademic year.Note: Create Benefit Reporting Table process must be run PRIOR to runningthis query. The Create Benefit Reporting Table process may be run for eachmonth or multiple months at one time.N/AN/ABOR ADP CES RPTRun ParametersOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials20 P a g e01.11.16

Periodic Reporting ToolsFor Month EndBuild Retiree Recv. Aging DataRecon Tool TypePathWhat this reportprovidesMonth EndBOR Menus - BOR Payroll - Benefit Reconciliation - Retiree Benefit Processes- Build Retiree Recv Aging DataThis application engine program populates the reporting table(BOR ADP RET AGE) that is used by the Retiree Receivables Aging Query.This process only picks up transactions with Account beginning with 129XXXfrom the PERS SERV BOR table.Auditor Item #YE Checklist #Data Source(s)Note: This report does not provide data for Leave of Absence participation if theinstitution uses a Banner clearing account instead of the 129220 LOA receivable.Then only retiree and Cobra data will show.N/AN/APERS SERV BORRun PageJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials21 P a g e01.11.16

Retiree Receivables Aging QueryRecon Tool TypePathWhat this reportprovidesMonth EndBOR Menus - BOR Payroll - Benefit Reconciliation - Retiree BenefitInquery/RPTS - Retiree Receivable AgingThis query shows retiree aging data as of a given date, by deduction code andaccount, in five continuous date range buckets.This report, along with the Open Coupon Report and Supplemental PDR (for prepays), can be used to balance back to institution’s GL AR accounts.After running the Retiree Receivable Aging query, download data to Excel. Anadditional column is needed to total all outstanding “buckets” in order to createa pivot table and see the outstanding balance for each participant.The pivot table data should balance to the general ledger/trial balance data forthe retiree receivable account, 129210 and the COBRA receivable account129230. This data is then reconciled to the Open Coupon Query data for thesame reconciling period. (There is a 3-way reconciliation between the retireereceivable aging data, general ledger balances and open coupon query data.)Auditor Item #YE Checklist #Data Source(s)Note: Build Retiree Recv. Aging Data process must be run PRIOR to running thisquery as this query uses the data from the reporting table populated by theBuild Retiree Recv. Aging Data process.N/AN/ABOR ADP RET AGERun ParametersJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials22 P a g e01.11.16

OutputsPivot TableJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials23 P a g e01.11.16

Open Coupon QueryRecon Tool TypePathWhat this reportprovidesMonth EndBOR Menus - BOR Payroll - Benefit Reconciliation - Benefit AccountingAudit Tools - Open Coupon QueryThis query shows the monthly Open Coupon report loaded to the BORReconciliation Portal.BeneDirect runs this the first of the month for the previous month. It containsa listing of Retirees/Cobras that still have ‘Open Coupons’ (remember thatADP doesn’t have accounts receivable - ADP refers to them as‘coupons’). These are people that BeneDirect hasn’t received payments fromfor the Coupons they have sent. The people/amounts listed should balanceback to institution’s AR Aging Report with the exceptions of errors (caused byBenefitsBilling/BeneDirect prorating) or pre-payments (can be identified onthe Supplemental PDR report).Auditor Item #YE Checklist #Data Source(s)Download the Open Coupon query data to Excel and delete the SSN columnsas they are not needed for reconcilement or reporting. Create a pivot tableusing the EMPLID as the row field. Column fields are not needed. The value isthe monthly premium (change value from count to sum). Filter on BenefitStatus (Cobra or Direct Billing/Retiree). This should provide Open Couponbalances by Emplid and status to reconcile back to the GL receivable accountsand Retiree/Cobra Aging query data. Any variances between RetireeAging/Open Coupon and GL/Trial Balance amounts should be researched todetermine where the issue originated.N/AN/ABOR ADP COBRECVRun ParametersOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials24 P a g e01.11.16

Pivot Table of Open Coupon query dataJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials25 P a g e01.11.16

Payroll Deduction RegisterRecon Tool TypePathWhat this reportprovidesAuditor Item #YE Checklist #Data Source(s)Month EndBOR Menus - BOR Payroll - BOR Payroll Reports - Payroll DeductionRegisterThis query is a replica of PS HRMS Payroll Deduction Register reportBORR022. This query will return results for a given period of time, allemployees, all deduction codes, the deduction amount, MTD, QTD, and YTDamounts. The results will be sorted by Deduction Code, Deduction Class,Employee Name (alphabetical order), and Pay End Date.Note: MTD, QTD and YTD totals are “running” totals and will be the sameamounts for each row in a monthly or quarterly time period.N/AN/AADP DEDUCT BALADP PAY CALENDRADP PAY CHECKADP PAY DEDUCTNRun ParametersOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials26 P a g e01.11.16

Recon Tool TypePathWhat this reportprovidesAuditor Item #YE Checklist #Data Source(s)Payroll Register by Ded. CodeMonth EndBOR Menus - BOR Payroll - BOR Payroll Reports - Payroll Register by Ded.CodeThis query is a replica of PS HRMS Payroll Deduction Register reportBORR022. Just like the Payroll Deduction Register query, this query willreturn results for a given period of time, all employees, all deduction codes,the deduction amount, MTD, QTD, and YTD amounts. The results will besorted by Deduction Code, Deduction Class, Employee Name (alphabeticalorder), and Pay End Date. However, this query allows user to query the databy specific Deduction Code.N/AN/AADP DEDUCT BALADP PAY CALENDRADP PAY CHECKADP PAY DEDUCTNRun ParametersOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials27 P a g e01.11.16

Periodic Reporting ToolsAudit ToolsBenefit Process AuditRecon Tool TypePathWhat this reportprovidesAuditor Item #YE Checklist #Data Source(s)Audit ToolBOR Menus - BOR Payroll - Benefit Reconciliation - Benefit Accounting AuditTools - Benefit Process AuditThis shows a list of Benefit Reconciliation processes run by journal linedescription/process, operator ID and dates.This query is more of a reference or auditor's tool. Institutions may use this todetermine what benefit reconciliation processes have been run by date andoperator.N/AN/ABOR ADP CES AUDOutputsJob Aid: Integration-ADP/EV5 and PeopleSoft Financials Periodic Reporting ToolsGeorgiaFIRST Financials28 P a g e01.11.16

Absences process begins with ADP generating the initial report from eTime. SSC provides the data/report to ITS to load into PeopleSoft Financials. One potential issue with the data from eTime is the accuracy of the hourly rate as of the fiscal year end. The hourly rate is