|
|
|
 |
| Introduction |
|
The purpose of this manual is to provide additional tools and support to state payroll technicians and managers for processing and managing payroll at their agencies. As a supplement to each agency’s own policies, procedures, or internal controls, it includes technical information on how to process transactions in OSPA, system reports and outputs, and recommendations about basic payroll procedures and controls.
All files below are pdf unless marked otherwise.
|
|
|
| Codes |
|
All files below are pdf unless marked otherwise
| Title |
Effective Date |
| Benefit Package Codes |
June 29, 2011 |
| Deduction Codes |
November 18, 2011 |
| Garnishment Codes |
January 19, 2012 |
| Pay and Leave Codes |
January 13, 2012 |
| Screen Message Codes |
April 15, 2011 |
| Work Schedule Codes, 7-days (One Week) |
January 19, 2012 |
| Work Schedule Codes, 14-days (Two Weeks) |
January 19, 2012 |
Work Schedule Codes, Database (All Options) (mdb 3.85 MB)
-- PLEASE NOTE: You must download this file to your computer before you can use it. Right-click the link, then choose "save" when prompted to do so.
|
December 30, 2011 |
|
|
|
| Datamart |
|
What is the OSPA Datamart?
The OSPA Datamart is a repository for information processed through the Oregon Statewide Payroll Application (OSPA). Currently, information is available from 7/1/2001 to present.
The OSPA Datamart is updated monthly following final payroll cutoff of run 2. Specific dates are listed on the OSPS Processing Calendar.
Information is stored in various tables, which can be queried using any software that is ODBC-compliant. Hyperion Intelligence Explorer is the software supported by the Statewide Accounting and Reporting Services section of the State Controller's Division.
How do I access the OSPA Datamart?
You will need security clearance to the database tables, and ODBC-compliant querying software.
To request security clearance, agency security officers should contact systems security by e-mail or fax (503) 378-8940. For each request, include:
- Name: (Same as in PPDB – Use PBER to verify)
- User ID
- User Telephone #
- Agency Name/Mnemonic (initials) i.e. DAS-OBA, DAS-BAM, CBS, OYA
- Agency Number
- Internet E-Mail Address:
There are many ODBC-compliant software packages. A select few are Hyperion (formerly BrioQuery), Crystal Reports, and Microsoft Access.
Training for SFMA and OSPA Datamart usage as well as the Brio/Hyperion application are available through the Statewide Accounting and Reporting (SARS) section of the State Controller's Division.
Why would I use the OSPA Datmart?
There are many, many uses for the OSPA Datamart. Here are just a few:
-
Custom Reports. Just about anything you can think of can be queried from the OSPA Datamart and customized to your specific needs. Do you need to complete a PERS salary breakdown? How about finding out how often someone uses sick leave? Curious about usage statistics for parking? All of these and much more can be queried from the OSPA Datamart.
-
Identify Errors. Use the OSPA Datamart to query for specific pay or deduction codes that give you troubles. For example, use the OSPA Datamart to ensure that each SEIU union deduction code has an accompanying issues code. Or, query to ensure that insurances are being forced using a subsidy code and not just plan codes.
-
Monitor for Potential Abuse. The OSPA Datamart is ideal for doing spot queries to ensure that usage of pay codes fall within normal tolerances. For instance, you can run a query on a specific class to enquire about comp time accrual, then measure that against an expected average. Or, query specific employee groups for abnormal overtime usage.
Datmart Table Information
Use the Master Reference Guide for all OSPA Datamart Table Fields (pdf) if you are not sure which table to use to build your query. Or if you want to know "Can I query for this?"
Extensions Reference Card (pdf). Use this to understand the _B, _PB, _PP, and blank extensions to the OSPA Datamart Tables.
Further Information
Further information including details about the OSPA Datamart tables and establishing a connection for Hyperion can be found on the SARS Datamart page.
|
|
|
| Entry Guides |
|
All files below are pdf unless marked otherwise
| Entry Guide |
Effective Date |
| Direct Deposit |
May 13, 2011 |
| ODT, Approving Time |
April 15, 2011 |
| ODT, Entering Time |
April 15, 2011 |
| ODT, PIN |
September 12, 2008 |
| Time Capture, Entering Time |
April 22, 2011 |
| Time Capture, Timesheets |
January 29, 2010 |
| Additional Entry Guides |
Still to Come |
|
|
|
| Handbooks |
|
All files below are pdf unless marked otherwise
|
|
|
| Interfaces |
|
All files below are pdf unless marked otherwise
|
|
|
| Internal Controls |
|
Internal Control Guidebook and Suggested Plan
The Statewide Financial Services (SFS) Internal Control Guidebook (doc) provides an overview of the control process, including why we have internal controls and a suggested plan for evaluating and strengthening controls.
Appendix D (doc) of the Internal Control Guidebook, Page 32, provides specific guidelines for the payroll cycle.
Internal Control and Risk Management Tools
The State Controller's Division, Statewide Financial Services section provides information and several related resources for internal control and risk management tools.
This resource includes internal control newsletters and training opportunities.
Self-Assessment Tool for Payroll Internal Controls
SFS provides self-assessment tools for several financial responsibilities. The SFS Payroll Self-Assessment Tool (doc) helps you evaluate existing internal controls and identify strengths and weaknesses.
Policies and Procedures
The Oregon Accounting Manual (OAM) contains policies and procedures to guide you in your internal control practices.
Relevant policies and procedures include:
|
|
|
| Recommended Practices |
|
All files below are pdf unless marked otherwise
| Recommended Practice |
Effective Date |
| Checks, Manual |
May 13, 2011 |
| Checks, Stale Dated |
August 4, 2011 |
| Collective Bargaining Agreements |
May 27, 2011 |
| Deceased Employees |
January 12, 2012 |
| Leave, Injured Worker |
January 30, 2009 |
| New Employees |
October 28, 2011 |
| Separations |
November 18, 2011 |
| Taxes, Corrected W-2 - See Handbooks |
March 4, 2011 |
| Taxes, Foreign Nationals - See Handbooks |
March 4, 2011 |
| Taxes, Reimbursements and Fringe Benefits - See Handbooks |
March 4, 2011 |
| Taxes, Withholding - See Handbooks |
March 4, 2011 |
| Time and Attendance |
April 15, 2011 |
| Transfers |
August 4, 2011 |
| Volunteers, Americorp |
May 11, 2007 |
| Volunteers, Boards and Commissions |
July 20, 2007 |
| Volunteers, Unpaid |
July 23, 2009 |
| Year End |
October 28, 2011 |
| Additional Recommended Practices |
Still to Come |
|
|
|
| Report Descriptions |
|
Conventions Used in the Reports Section
Some reports have OSPS as part of the report number or the report title. For the report descriptions, we have omitted that designation. The reports are in alphabetical order by title, omitting the OSPS reference.
All files below are pdf unless marked otherwise
Report Number
|
Report Name
|
*DM? |
Effective Date
|
XREFRA
|
ACH Direct Deposit Register
|
No |
February 23, 2007
|
J463-30-1
|
ACH Returns
|
No |
May 8, 2009
|
E404-09-1
|
Agency Employees with Leave Type CT |
Yes |
April 22, 2011
|
E510A
E510C
|
Agency Overtime Report |
Yes |
November 9, 2006
|
XREFRL
|
Alpha Listing of Employee Checks |
No |
November 9, 2006
|
B010
|
Batch Time Capture |
No |
November 9, 2006
|
| ECFD Series |
CFD Donations |
|
December 16, 2011 |
E469-05-1
|
Check Reconciliation File-in Agy/Mcr Sequence |
No |
May 19, 2011
|
E311-018A
|
Child Support Amounts by Agency |
No |
May 14, 2010
|
ANNLDEC
|
Deceased Employees – Agency Summary |
Yes |
May 14, 2010
|
E322-01
|
Deductions Past Job End Date |
No |
September 11, 2009
|
XREF01
|
Deduction Reconciliation |
No |
August 14, 2009
|
XREF76
XREF76C
XREF76DC
XREF15
XREF15C
|
Deduction Register Detail & Summary |
Yes |
December 2, 2010
|
| E421-260-1 |
EID Security Table Entries by RDC/Supervisor |
No |
August 14, 2009 |
E122-077-01
E122-077-02
|
Employee Furlough Leave Used |
Yes
|
January 15, 2010 |
ETV7
ETVALP
ETV7RDC
|
Employee Monthly Timesheet |
Yes |
April 22, 2011 |
B055CC
B055RG
|
Employee Time Exception |
No |
September 11, 2009
|
| B360 |
Employee Update Screen Activity Report |
No |
May 14, 2010 |
| E122-080-01 |
Employee Voluntary Furlough Leave Used |
Yes |
August 6, 2010 |
E357-020
|
Employees Eligible for Participation in Retirement |
No |
February 5, 2010
|
E315-009A
|
Employees Unqualified for State Pay Benefits |
No |
March 21, 2011
|
XREF68N
|
Employees with Negative Amount(s) Listed |
No |
February 4, 2011
|
E497
|
Employees with Non-blank Waivers |
Yes |
September 18, 2009
|
B500A
B500C
|
EXAS OSPS Detail Labor Distribution and Prorate |
No |
September 4, 2009 |
XREF10-A
|
FICA Mag-File Detail Note of Irregularities |
No |
February 18, 2011
|
XREF11
|
FICA Mag-File Summary for Tax Year #### |
No |
February 25, 2011
|
E512A
E512C
|
FMLA/OFLA Leave Hours
|
Yes |
January 29, 2010
|
B075A
B075B
|
Gross Pay Adjustments |
No |
March 26, 2010
|
E820-1
E820-2
E820-3
|
Gross Pay Adjustments Outdated |
No |
October 31, 2008
|
E260-100
|
Gross Pay Over 2.0 x Ajd Base Exception Report |
No |
March 26, 2010
|
E370-06-01
|
Income Tax Exemptions |
Yes |
September 18, 2009
|
E460
|
Leave Accrual -- Adjustments and Transfers |
Yes |
April 17, 2009
|
E505-152
|
Leave Accrual – Employee’s Leave Balance and Usage |
Yes |
April 29, 2011
|
| E455 |
Leave Accrual - Exception Report |
No
|
March 19, 2009 |
B470C
B470CM
B470D
B470S
|
Leave Accrual Detail Transaction Register |
Yes |
April 29, 2011
|
E461-01
|
Leave Adjustments Made to Closed Periods |
No |
August 4, 2011
|
ETV8L
|
List of Daily Timesheets with a Supervisor Lock |
No |
November 9, 2006
|
ETV8N
|
List of Daily Timesheets without a Supervisor Lock |
No |
November 9, 2006
|
E560-04-01
E560-04-02
|
List of In/Outdated Deductions |
No |
November 18, 2011
|
E404-25-1
|
Lost Leave Accrual CT Balance |
Yes |
April 22, 2011
|
| E370-09-1 |
Mandatory Withholdings Determination |
No |
September 18, 2009 |
E311-018B
|
Negative Child Support Amounts by Agency |
No |
May 14, 2010
|
| E311-38 |
Negative Vendor ACH Generated This Run; To Be Used Next Payroll Run |
No |
April 9, 2009 |
| E502-02 |
Negative Vendor ACH Payments - Please Bill |
No |
March 5, 2010 |
E260-200
|
Net Pay Negative Report |
No |
March 21, 2011
|
| E397-120-01 |
Non-leave Time Recorded on Holiday
|
Yes |
May 6, 2011
|
| B850-01 |
Nov or Dec Pay Reported in Two Tax Years |
Yes |
November 15, 2008 |
E475-B
|
OPEU FLSA Exempt Employees with CT/ST Balances |
Yes |
April 15, 2011
|
| E701-30-A |
Outstanding Checks to be Purged from Check Recon |
No
|
May 19, 2011 |
XREF24
XREF18
|
P&F Extra Retirement Units |
Yes |
February 5, 2010
|
XREF87
|
Payroll Check Reversals |
No |
November 9, 2006
|
E315-009B
E315-009C
E315-009D
|
Payroll Deduction Adjustment |
No |
March 21, 2011
|
E120-042A
E120-052A
E122-042A
E122-046A
E122-056A
|
Payroll Exception |
No |
December 2, 2010
|
XREF43
XREF44
XREF45
XREF46
XREF46D
|
Payroll Register |
Yes |
March 4, 2011
|
E120-052B
E122-042B
E122-042C
|
PEBB Domestic Partner |
Yes |
August 14, 2009
|
| XREF51 |
PEBB Insurance Exceptions |
No |
February 25, 2011 |
XREF16
XREF17
|
Regular Contributions |
No |
February 5, 2010
|
XREF32
|
Retirement Remittance Advice |
No |
November 9, 2006
|
E480A
|
Seasonal, Temporary, and Permanent Part-time Hours Worked |
Yes |
November 9, 2006
|
E357-030
|
Separated Employees with Net Pay Positive Adj |
No |
February 25, 2011
|
XREF90
XREF90C
XREF91
XREF91C
XREF92
XREF92C
XREF93
XREF93C
XREF94
XREF95
XREF96
XREF96C
XREF99
|
SFMS Cost Distribution |
Yes |
September 4, 2009 |
| JR07-1 |
Terminal List - By Agency |
No |
August 28, 2009 |
E492
|
Terminated Employees Having Leave Balances |
Yes |
August 4, 2011
|
B065A
B065C
|
Timesheet Audit and Control |
No |
November 6, 2006
|
B030-020
|
Timesheet Forecast Control |
No |
November 9, 2006
|
E513A
E513C
|
Training Hours Taken/Given |
Yes |
November 9, 2006
|
E311-01
|
Vendor ACH Deductions |
No |
November 9, 2006
|
XREF23
|
Vendor Check Summary |
No |
November 9, 2006
|
XREF21
|
WBF Assessment & SAIF Premium Summary |
No |
November 9, 2006
|
| E381-30-2 |
W-2 Distribution List |
No |
May 13, 2011 |
| E489-1 |
Year End Report: Total Regular Hours Worked |
Yes |
November 9, 2006 |
XREF68-C
XREF68-P
|
Year to Date |
Yes |
February 25, 2011
|
XREF85
|
Year to Date and Current Correction |
No |
November 9, 2006
|
* DM = Report can be reproduced or partially reproduced through use of the OSPA Datamart. Details are contained within the report description document.
|
|
|
| Screen Descriptions |
|
December 16, 2011
All files below are pdf unless marked otherwise
Screen
Number
|
Screen Title |
Effective Date |
| ADB1 |
Payroll Benefit Package Table |
December 2, 2011 |
| ADB2 |
Payroll Benefit Package Table |
November 12, 2010 |
| ADD1 |
Payroll Deduction Table |
December 16, 2011 |
| ADD2 |
Payroll Deduction Table |
December 16, 2011 |
| ADD3 |
Payroll Deduction Table |
December 16, 2011 |
| ADW1 |
Payroll Work Schedule Table |
August 31, 2006 |
| ADW2 |
Payroll Work Schedule Table |
August 31, 2006 |
| P001 |
Session Default Values |
March 26, 2010 |
| P002 |
Time Capture Screen |
March 26, 2010 |
| P003 |
Time Capture Screen |
April 15, 2011 |
| P004 |
Time Capture Screen |
February 12, 2010 |
| P005 |
Payroll Time Capture - Employee Entry Menu |
April 15, 2011 |
| P006 |
Payroll Time Capture - Supervisor Entry Menu |
July 9, 2009 |
| P007 |
Payroll Time Capture - Entered Pay Types by Day - Summary |
July 25, 2008 |
| P009 |
PIN Scrub Utility |
August 31, 2006 |
| P010 |
Withhholding Data / Locator Data |
November 18, 2011
|
| P020 |
Work Schedule Data |
August 31, 2006 |
| P030 |
Job Status Data |
March 21, 2011
|
| P031 |
Job Status Update |
July 2, 2009 |
| P032 |
Job Status Display |
September 4, 2009 |
| P050 |
Gross Pay Adjustments on File |
March 26, 2010 |
| P060 |
Tax/OPE Adjustments on File |
November 18, 2011
|
| P070 |
Deductions and Deduction Adjustments |
December 16, 2011 |
| P071 |
Cloning Utility |
April 20, 2007 |
| P090 |
YTD Wages, Taxes, and Retirement |
November 18, 2011
|
| P091 |
YTD Wages, Taxes, and Retirement - Calculated from P370 |
November 18, 2011
|
| P130 |
Check Reversal/Stop Payment |
May 13, 2011 |
| P140 |
Manual Checks |
May 13, 2011 |
| P160 |
Name Locator |
August 31, 2006 |
| P190 |
Payroll Register Data |
March 30, 2009 |
| P191 |
Register Summary |
March 21, 2011 |
| P192 |
Register Listing |
March 21, 2011 |
| P300 |
Vendor Checks |
May 13, 2011 |
| P310 |
Vendor Check Reversal/Stop Payment |
March 28.2008 |
| P320 |
Check Print Request |
May 13, 2011 |
| P370 |
Calculation of Current Payoff |
April 22, 2011 |
| P420 |
Leave Clearing Account Balances |
March 21, 2011 |
| P430 |
Employee Leave Data |
March 26, 2010 |
| P435 |
Employee Leave Data |
November 18, 2011 |
| PACH |
ACH Stops |
April 2, 2009 |
| PBAP |
PPDB Agency Table - Payroll |
November 12, 2010 |
| PCHG |
OSPS - Work Charge Entry Table |
August 31, 2006 |
| PMSG |
OSPS Newsletter |
August 31, 2006 |
| PPRM |
Variable Update Screen |
October 30, 2009 |
| PRPT |
OSPS - Agency Report Selection |
March 4, 2011 |
| PSEC |
OSPS - Time Entry Security Table |
September 18, 2009 |
| PTB1 |
Payroll Benefit Package Table |
December 2, 2011 |
| PTB2 |
Payroll Benefit Package Table |
November 12, 2010 |
| PTD1 |
Payroll Deduction Table |
December 16, 2011 |
| PTD2 |
Payroll Deduction Table |
December 16, 2011 |
| PTD3 |
Payroll Deduction Table |
December 16, 2011 |
| PTW1 |
Payroll Work Schedule Table |
August 31, 2006 |
| PTW2 |
Payroll Work Schedule Table |
August 31, 2006 |
|
|
| |
|
|
