|
|
|
 |
| 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 |
December 31, 2009 |
| Deduction Codes |
January 29, 2010 |
| Garnishment Codes |
March 5, 2010 |
| Pay and Leave Codes |
February 12, 2010 |
| Screen Message Codes |
March 5, 2010 |
| Work Schedule Codes, 7-days (One Week) |
March 5, 2010 |
| Work Schedule Codes, 14-days (Two Weeks) |
March 5, 2010 |
Work Schedule Codes, Database (All Options) (mdb 4.03 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.
|
March 5, 2010 |
|
|
|
| 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 (pdf).
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 are available through the Statewide Accounting and Reporting (SARS) section of the State Controller's Division. Additionally, DAS Statewide Training and Development Services conducts classes for Hyperion, Crystal Reports, and Microsoft Access software.
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 |
August 6, 2009 |
| Leave, Adjust Balances - See Handbooks |
December 31, 2009 |
| Leave, Advance - See Handbooks |
December 31, 2009 |
| Leave, Pay Off - See Handbooks |
December 31, 2009 |
| Leave, Restore Lost - See Handbooks |
December 31, 2009 |
| Leave, Transfer - See Handbooks |
December 31, 2009 |
| ODT, Approving Time |
July 9 2009 |
| ODT, Entering Time |
January 29, 2010 |
| ODT, PIN |
September 12, 2008 |
| PEBB, Domestic Partners - See Handbooks |
January 7, 2010 |
| PEBB, Opt Out - See Handbooks |
January 7, 2010 |
| PEBB, Part-time and Job Share Employees - See Handbooks |
January 7, 2010 |
| PEBB, Protected Leave, Full-time Employees - See Handbooks |
January 7, 2010 |
| PEBB, Protected Leave, Part-time and Job Share - See Handbooks |
January 7, 2010 |
| PEBB, Waive Benefits - See Handbooks |
January 7, 2010 |
| PERS, Start Contributions - See Handbooks |
January 29, 2010 |
| PERS, Wage and Contribution Adjustments - See Handbooks |
January 29, 2010 |
| Time Capture, Entering Time |
January 29, 2010 |
| 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 |
January 22, 2010 |
| Checks, Stale Dated |
March 19, 2009 |
| Collective Bargaining Agreements |
May 20, 2009 |
| Deceased Employees |
June 6, 2008 |
| Leave, Injured Worker |
January 30, 2009 |
| New Employees |
January 29, 2010 |
| Separations |
September 24, 2009 |
| Taxes, Corrected W-2 |
February 12, 2010 |
| Taxes, Foreign Nationals |
December 24, 2009 |
| Taxes, Reimbursements and Fringe Benefits |
August 14, 2009 |
| Taxes, Withholding |
September 14, 2009 |
| Time and Attendance |
July 9, 2009 |
| Transfers |
May 8, 2009 |
| Volunteers, Americorp |
May 11, 2007 |
| Volunteers, Boards and Commissions |
July 20, 2007 |
| Volunteers, Unpaid |
July 23, 2009 |
| Year End |
December 3, 2009 |
| 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 |
May 1, 2009
|
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
|
E469-05-1
|
Check Reconciliation File-in Agy/Mcr Sequence |
No |
May 15, 2009
|
E311-018A
|
Child Support Amounts by Agency |
No |
November 9, 2006
|
BONDE010
BONDE010-2
|
Contribution Register |
No |
August 14, 2009
|
ANNLDEC
|
Deceased Employees – Agency Summary |
Yes |
November 9, 2006
|
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 |
January 15, 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 |
B055CC
B055RG
|
Employee Time Exception |
No |
September 11, 2009
|
| B360 |
Employee Update Screen Activity Report |
No |
December 7, 2007 |
E357-020
|
Employees Eligible for Participation in Retirement |
No |
February 5, 2010
|
E315-009A
|
Employees Unqualified for State Pay Benefits |
No |
November 9, 2006
|
XREF68N
|
Employees with Negative Amount(s) Listed |
No |
February 5, 2010
|
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 |
November 6, 2009
|
XREF11
|
FICA Mag-File Summary for Tax Year #### |
No |
April 11, 2008
|
E512A
E512C
|
FMLA/OFLA Leave Hours
|
Yes |
January 29, 2010
|
B075A
B075B
|
Gross Pay Adjustments |
No |
November 9, 2006
|
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 |
November 9, 2006
|
E370-06-01
|
Income Tax Exemptions |
Yes |
September 18, 2009
|
XREF51
|
Insurance Analysis Exceptions |
No |
January 22, 2010
|
E460
|
Leave Accrual -- Adjustments and Transfers |
Yes |
April 17, 2009
|
E505-152
|
Leave Accrual – Employee’s Leave Balance and Usage |
Yes |
January 22, 2010
|
| E455 |
Leave Accrual - Exception Report |
No
|
March 19, 2009 |
B470C
B470CM
B470D
B470S
|
Leave Accrual Register |
Yes |
January 22, 2010
|
E461-01
|
Leave Adjustments Made to Closed Periods |
No |
April 17, 2009
|
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
|
List of In/Outdated Deductions |
No |
November 15, 2008
|
E404-25-1
|
Lost Leave Accrual CT Balance |
Yes |
May 30, 2008
|
| E370-09-1 |
Mandatory Withholdings Determination |
No |
September 18, 2009 |
E311-018B
|
Negative Child Support Amounts by Agency |
No |
November 6, 2006
|
| 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 |
|
Net Pay Negative Report
|
No |
November 9, 2006
|
| E397-120-01 |
Non-leave Time Recorded on Holiday
|
Yes |
November 9, 2006
|
| 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 17, 2009
|
| E701-30-A |
Outstanding Checks to be Purged from Check Recon |
No
|
May 15, 2009 |
XREF24
XREF18
|
P&F Extra Retirement Units |
Yes |
February 5, 2010
|
XREF87
|
Payroll Check Reversals |
No |
November 9, 2006
|
E315-009B
E315-009C
|
Payroll Deduction Adjustment F & W |
No |
March 6, 2009
|
E120-042A
E120-052A
E122-042A
E122-046A
E122-056A
|
Payroll Exception |
No |
January 30, 2009
|
XREF43
XREF44
XREF45
XREF46
XREF46D
|
Payroll Register |
Yes |
September 4, 2009
|
E120-052B
E122-042B
E122-042C
|
PEBB Domestic Partner |
Yes |
August 14, 2009
|
XREF16
XREF17
|
Regular Contributions |
No |
February 5, 2010
|
XREF32
|
Retirement Remittance Advice |
No |
November 9, 2006
|
BD115
BD115A
BD115B
BD115C
BD115D
|
Savings Bonds Listing |
No |
September 4, 2009 |
E480A
|
Seasonal, Temporary, and Permanent Part-time Hours Worked |
Yes |
November 9, 2006
|
E357-030
|
Separated Employees with Net Pay Positive Adj |
No |
November 15, 2008
|
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 |
September 24, 2009
|
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 |
August 22, 2008 |
| E489-1 |
Year End Report: Total Regular Hours Worked |
Yes |
November 9, 2006 |
XREF68
|
Year to Date |
Yes |
April 11, 2008
|
XREF85
|
Year to Date and Current Correction |
No |
November 9, 2006
|
| EICRPT |
Yearly Earned Income Credit for Tax Year YYYY |
Yes |
August 14, 2009 |
* DM = Report can be reproduced or partially reproduced through use of the OSPA Datamart. Details are contained within the report description document.
|
|
|
| Screen Descriptions |
|
All files below are pdf unless marked otherwise
Screen
Number
|
Screen Title |
Effective Date |
| ADB1 |
Payroll Benefit Package Table |
February 12, 2010 |
| ADB2 |
Payroll Benefit Package Table |
February 13, 2007 |
| ADD1 |
Payroll Deduction Table |
November 19, 2009 |
| ADD2 |
Payroll Deduction Table |
November 19, 2009 |
| ADW1 |
Payroll Work Schedule Table |
August 31, 2006 |
| ADW2 |
Payroll Work Schedule Table |
August 31, 2006 |
| P001 |
Session Default Values |
August 31, 2006 |
| P002 |
Time Capture Screen |
July 2, 2009 |
| P003 |
Time Capture Screen |
February 12, 2010 |
| P004 |
Time Capture Screen |
February 12, 2010 |
| P005 |
Payroll Time Capture - Employee Entry Menu |
January 18, 2008 |
| 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 |
February 5, 2010
|
| P020 |
Work Schedule Data |
August 31, 2006 |
| P030 |
Job Status Data |
February 5, 2010
|
| P031 |
Job Status Update |
July 2, 2009 |
| P032 |
Job Status Display |
September 4, 2009 |
| P050 |
Gross Pay Adjustments on File |
October 3, 2008 |
| P060 |
Tax/OPE Adjustments on File |
February 5, 2010
|
| P070 |
Deductions and Deduction Adjustments |
January 29, 2010 |
| P071 |
Cloning Utility |
April 20, 2007 |
| P080 |
Bond Data Entry |
February 12, 2010 |
| P081 |
OSPS Bond System - Person Locator |
January 4, 2007 |
| P090 |
YTD Wages, Taxes, and Retirement |
February 5, 2010
|
| P091 |
YTD Wages, Taxes, and Retirement - Calculated from P370 |
February 5, 2010
|
| P130 |
Check Reversal/Stop Payment |
August 31, 2006 |
| P140 |
Manual Checks |
March 5, 2010 |
| P160 |
Name Locator |
August 31, 2006 |
| P190 |
Payroll Register Data |
March 30, 2009 |
| P191 |
Register Summary |
May 28, 2009 |
| P192 |
Register Listing |
March 30, 2009 |
| P300 |
Vendor Checks |
March 5, 2010 |
| P310 |
Vendor Check Reversal/Stop Payment |
March 28.2008 |
| P320 |
Check Print Request |
March 28, 2008 |
| P370 |
Calculation of Current Payoff |
March 5, 2010 |
| P420 |
Leave Clearing Account Balances |
November 13, 2009 |
| P430 |
Employee Leave Data |
November 13, 2009 |
| P435 |
Employee Leave Data |
June 4, 2009 |
| PACH |
ACH Stops |
April 2, 2009 |
| PBAP |
PPDB Agency Table - Payroll |
March 5, 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 |
September 8, 2009 |
| PSEC |
OSPS - Time Entry Security Table |
September 18, 2009 |
| PTB1 |
Payroll Benefit Package Table |
February 12, 2010 |
| PTB2 |
Payroll Benefit Package Table |
February 13, 2007 |
| PTD1 |
Payroll Deduction Table |
December 3, 2009 |
| PTD2 |
Payroll Deduction Table |
November 19, 2009 |
| PTW1 |
Payroll Work Schedule Table |
August 31, 2006 |
| PTW2 |
Payroll Work Schedule Table |
August 31, 2006 |
|
|
| |
|
|
