Text Size:   A+ A- A   •   Text Only
Find     
Site Image
OSPA Datamart
What's in the OSPA Datamart?
The OSPA Datamart contains data processed through the Oregon Statewide Payroll Application.  Data is updated monthly following the close of the payroll month, which is around the 14th of the following month.  Check the OSPS Processing Calendar for specific dates.
 
The OSPA Datamart contains individual employee payroll information including:
  • Deductions
  • Job Status
  • Labor Costing
  • Leave Accrual
  • Gross and Net Pay Detail
  • Work Schedules
NOTE:  For greater detail and a comprehensive list, see the Datamart Content Reference Material section below.
 
The OSPA Datamart does not contain:
  • Confidential Information, such as Social Security numbers, wage withholding/garnishment info, bank account information or certain insurance surcharges
  • Data sourced from other applications such as salary range or employee addresses
  • Detailed daily time information if not using Online Daily Time
  • Furlough savings information

 
Why Use the OSPA Datamart?
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 post-tax rather than pre-tax 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.
  • Collective standardization of common requests.  Access the Datamart Reports Repository to take advantage of reports (pdf) already established by users of the OSPA Datamart.

 
How to Access the OSPA Datamart
How do I access the Datamart?
 
You will need:
  • Query software (required).  The state holds an enterprise license for Hyperion System 11 IR Studio (formerly Brio).
  • Security clearance to the OSPA database tables (required).
  • Datamart training (optional).  FBS manages the Datamart and provides training.
  • Datamart news subscription (optional).  FBS manages the Datamart and provides communication on related updates.

 
Datamart Content Reference Material
Datamart Table Reference Lists
These lists detail the fields available in each of the OSPA Datamart's six major tables.  For a Master List of all available fields, see the last portion of this section.
 

 OSPA Datamart Table
 Description and Field Listing
 OSPS_DED  Deductions Table Reference Guide (pdf)
 OSPS_JS_LAB_ADJ  Job Status, Labor and Adjustments Table Reference Guide (pdf)
 OSPS_LABOR_COST  Labor Cost Table Reference Guide (pdf)
 OSPS_LV_ACCRUAL  Leave Accrual Table Reference Guide (pdf)
 OSPS_NET_PAY  Net Pay Table Reference Guide (pdf)
OSPS_WORK_SCHED  Work Schedules Table Reference Guide (pdf)

Table Extensions Reference
Each of the OSPA Datamart tables has historic data available up to three prior biennia.  This information is broken down into four segments for each table.
 
This grid shows the segments for each table extension.  Print a handy reference card (pdf) as well.

Table Extension
Table Extension
Period Covered
 Example
No extension
Current Biennium only
7/1/13 to 6/30/15
 OSPS_DED
_B
Current and One Prior Biennia
7/1/11 to 6/30/15
 OSPS_DED_B
_PB
One Prior Biennium
7/1/11 to 6/30/13
 OSPS_DED_PB
_PP
Two and Three Prior Biennia
7/1/07 to 6/30/11
 OSPS_DED_PP
 

Master Field List
This Master List is also available in PDF format.
 
Guide to Table Abbreviations used in Master List
Abbreviation
Table Identified
Abbreviation
Table Identified
DED
OSPS_DED
LV
OSPS_LV_ACCRUAL
JSL
OSPS_JS_LAB_ADJ
NP
OSPS_NET_PAY
LC
OSPS_LABOR_COST
WS
OSPS_WORK_SCHED
 
If you want this …
Look here
Field Name
Field Definition
Table
ACTIVITY DESC
Comment entered for a gross pay adjustment, if any.
JSL
ADDED FED TAX
The dollar amount an employee decides to withhold in addition to calculated federal income taxes.
NP
ADDED STATE TAX
The dollar amount an employee decides to withhold in addition to calculated state income taxes.
NP
ADJ BASE RATE
Employee’s base salary rate plus any fixed pay differentials assigned by PPDB.
JSL
LV
ADJUST TYPE CD
Adjustment type for gross pay adjustments.  “A” is a one-time entry, blank is ongoing.
JSL
AGENCY NUM
The 5-digit payroll agency number.  This can be different from an employee’s home agency, and is driven by the PPDB “PR AGENCY” field.
All tables
AGY OBJ
The 4-digit SFMA agency object code associated with a pay type.
 
The codes break down payroll expenditures by such categories as regular time, overtime, differential pay, pay for deceased employees, etc.  For Payroll objects, the agency and comptroller objects are the same.
LC
AGY OBJ TITLE
The title for the agency object code used in the “AGY OBJ” field.
LC
AGY TRANS DATE
Effective date for SFMA posting.  Most agencies choose to post to the pay period month, but some choose to post to the check date month.
LC
AMNT FED TAX
Federal tax withheld from employee pay based on earnings and exemptions.
NP
AMNT STAT TAX
State tax withheld from employee pay based on earnings and exemptions.
NP
APPN YEAR
The 2-digit biennium year identifier for budgetary fiscal postings.
LC
APPNT TYPE CODE
The type of appointment - (P)ermanent, (S)easonal, (L)imited duration, (T)emporary, (A)cademic Year.
JSL
LV
APPOINTMNT DATE
The date employee was appointed to this position.
JSL
AUTH NUMBER
(Irrelevant to most queries)
This number identifies the authorization of the position on PICS (Position Inventory Control System).  PPDB field.
JSL
BASE PAY RATE
The unadjusted base salary rate for the employee’s classification as defined by the compensation plan.
JSL
LV
BENEFIT BRD CD
(Irrelevant to most queries)
Code to indicate who receives administrative fees collected as part of a specific deduction premium.  As of 2000, the only codes used are “S” for PEBB, “N” for None, or blank.  Historical possibilities include (S)EBB, (B)UBB, (N)one or blank.
DED
BENEFIT PKG CODE
This is the 2-character code used by OSPA to reference benefit package tables which validate pay types, insurance benefits, and leave availabilities and accruals.
 
See the OSPA Operating Manual, Appendix B, for a complete listing of current benefit package codes.
JSL
LV
NP
BRD SHR PREM
The portion of a deduction that goes to PEBB as a fee for administering the benefit.
DED
CHECK ISSUE DT
Date inscribed on the employee’s pay check.  Normally 1st and 15th of the month, but can differ due to weekends or holidays, or by using the P010 date to change the default payday date.
NP
CITY CODE
(Irrelevant to most queries)
The city code from PPDB which defines the employee's official duty station.
JSL
CLASSIFIED CODE
This code identifies the job class of an employee.
JSL, LV
CO REC REM NO
This code identifies the wage/job class code used by PERS to determine the benefit structure to which a member belongs.
JSL
CO SYS RETIRE 1
CO SYS RETIRE 2
CO SYS RETIRE 3
Corresponds to the “RTMT STAT” field on the P010 screen.  Indicates responsibility for employee contributions to retirement accounts.  Possible codes are:
D – Employer responsible
E – Employee responsible
N – No contribution
P – State match only, no employee contribution
 
NOTE:  Most state employees active in a pension plan are coded with “D”.
NP
CODE PAY TRANS
(Irrelevant to most queries)
This code is the same as the P190 “RUN TYPE” field – (O)riginal, (R)eversal, or (C)orrective.
DED, JSL, NP
CODE RUN PAY
(Irrelevant to most queries)
Payroll run and register process code.  This code is the same as the P190 “TRANS TYPE” field -  (O)riginal or (R)etro process.  Retro is currently unused.
DED, JSL, NP
CODE SRT SEQ
(Irrelevant to most queries)
This code is used to allow records to be sorted into the correct sequence;  1=agency header;  2=year to date correction; and  3=payroll set, set of records for a particular pay check.  1 and 2 are currently not reported.
JSL, NP
COMMENT
Reflects the comment line information entered with the deduction code, if any.
DED
CONCRNT JOB NUM
Number computer-assigned by PPDB to permit separate control of multiple jobs for an employee within an agency.
JSL, LV
COST ORIDE CD
The cost distribution code to which a pay code has been overridden from the PPDB default.
JSL
COUNTY CODE
(Irrelevant to most queries)
The county code from PPDB which defines the employee's official duty station.
JSL
COVERAGE CODE
Indicates type of insurance coverage associated with a deduction plan code.
  1. Employee Only
  2. Employee and Spouse or Domestic Partner (DP)
  3. Employee and Children (no spouse/DP)
  4. Employee and Family (spouse/DP and children)
DED
CREW NUMBER
An optional field for agencies to define the work group to which an employee is assigned.  Corresponds to the “PAY DISTR” field in PPDB.
JSL
DEDN ADJ TYPE
Indicates the type of deduction adjustment.
A- one-time adjustment
F- FMLA-related one-time adjustment
T- Table-driven (PTD2) one-time adjustment
W- CBIW or USERRA-related ongoing adjustment
Blank- ongoing deduction, not an adjustment
DED
DEDN BEN PKG
(Irrelevant to most queries)
This code is the last two digits of a deduction code.  For instance, in this code, DCNN, the “BEN PKG” would be “NN”.  Formerly used to identify OPE classifications and state match for insurance contributions.
DED
DEDN DESC
12-character description of the deduction code.  Prints in the vendor column of an employee’s remittance advice.
DED
DEDN PCT GROSS
Percent of gross used to compute a current deduction amount based on gross pay.
DED
DEDN PCT NET
(Irrelevant to most queries)
Percent of net used to compute a current deduction amount after all other deductions are known.  Not used.
DED
DEDN PKG
First two characters of a deduction code.  Useful when querying for a family of deduction codes.
 
For example, “BK” to capture all food bank deductions.
DED
DEDN PLAN CODE
Plan code associated with a given deduction code, if applicable.  Designates further breakdowns within a deduction code.
 
Example:  For deduction code XDNN, plan code 111 designates first fixed deposit, 120 designates second fixed deposit, 555 designates a net deposit.
DED
DEDN PLAN DESC
12-character description of the deduction code’s plan code, if any.  Prints in the description column of an employee’s remittance advice.
DED
DEDN PLAN DISTR
(Irrelevant to most queries)
Code of various deduction options by distributor code; describing which of several contacts may apply for a given carrier.  Example 1=basic, 2=major medical 3=comprehensive, 4=panel, 5=dental, etc.  As of 1999, this field is no longer used.
DED
DEDN TYPE
Four-character deduction code which corresponds to an employee’s P070 Net Pay Deduction screen.  Also corresponds to an employee’s deductions listed on the right-hand side of the paycheck.
 
Example:  XDNN (direct deposit) or SLNN (basic life)
 
NOTE:  Garnishment codes are not archived and cannot be queried.
DED
DIFF AMNT1
DIFF AMNT2
DIFF AMNT3
The fixed dollar amount of employee’s monthly differential(s) which make up the adjusted base rate.
JSL
DIFF PCNT1
DIFF PCNT2
DIFF PCNT3
The fixed percentage of employee’s monthly differential(s) which make up the adjusted base rate.
JSL
DOUBLE FILL CD
(Irrelevant to most queries)
A suffix to the position number which allows association of more than one employee with one position number due to (A)dministrative, (L)eave Without Pay, or (T)raining purposes.
JSL, LV
EARN INC CR AMT
Amount of tax credited to employee under the Earned Income Tax Credit program.
NP
EARN INC CR CD
Code indicating eligibility for the Earned Income Tax Credit program.  Anything other than (N)ot subject must have a completed IRS Form W-5, updated annually. Valid codes are:   (N)ot subject, (J)oint, or (S)ingle.
NP
EAS ACCT CODE
(Irrelevant to most queries)
Same as “AGY OBJ” field.  Agencies that interface to SFMA on a summary level may see different data here.
LC
EE DEDN AMT
The portion of a deduction paid by the employee. (employee share + employer share = total deduction)
DED
EE FICA TAX
Amount of FICA tax paid by employee.
NP
EE MEDR TAX
Amount of Medicare tax paid by employee.
NP
EE RETIRE NUM1
EE RETIRE NUM2
EE RETIRE NUM3
(Irrelevant to most queries)
Employee’s retirement account number.  No longer necessary or relevant; employee is now tracked by SSN or ID#.
NP
EE SHR DEDNS
Total of employee-paid deductions for the requested timeframe.
 
NOTE:  Direct deposits are deductions and are included in this figure.
NP
EE WORK CMP TAX
Amount of the Workers’ Comp tax paid by employee.
NP
EE YTD DEDN
Tax-year-to-date total that the employee has paid for the corresponding deduction.
DED
EMPLOYEE NAME
Name of employee as entered into PPDB, last name first.
All tables
EMPLOYEE NUM
9-character alphanumeric identifier beginning with “OR”, assigned by PPDB to uniquely identify an employee without using a Social Security Number.
All tables
ER DEDN AMNT
The portion of a deduction paid by the employer.   (employee share + employer share = total deduction)
DED
ER FICA TAX
Amount of FICA tax paid by employer.
NP
ER MEDR TAX
Amount of Medicare tax paid by employer.
NP
ER SHR DEDNS
Total of employer-paid deductions for the requested timeframe.
NP
ER WRK CMP TAX
Amount of the Workers’ Comp tax paid by employer.
NP
ERB ASMNT
Amount of assessment paid to the Employee Relations Board for an employee by the employer.
NP
ERB ASMT FLAG
Indicates whether or not the employees of an agency are subject to Employment Relations Board assessments.
JSL
EXPNS REIMBURS
Amounts associated with designated reimbursement pay codes.  Examples are “ER”, “MSN”, or “TLA”.  Reimbursement pay types have an “R” in the “INC TYP” column on employee benefit code tables (PTB2).
NP
FED TAX EXMPTNS
Employee’s federal income tax exemptions as claimed on IRS Form W-4.  Two-digit number from 00 to 99.  System default if no W-4 submitted is 00.
NP
FED TAX STATUS
Employee’s filing status as reported on IRS Form W-4 for federal tax withholding.  Choices are (S)ingle, (J)oint, or (N)one (for exempt).  System default if no W-4 submitted is (S)ingle.
NP
FED TAX SUB INC
Amount of income subject to federal tax withholding.
NP
FICA SUB FLAG
Indicates whether or not a pay type is subject to FICA tax levy.
JSL
FICA SUB INC
Amount of income subject to FICA withholding.
NP
FICA SUB PAY FL
Indicates whether or not employee is subject to FICA withholding.  Corresponds to the “FICA SUBJ” field on the P010 screen.  Choices are (Y)es or (N)o.
NP
FICA TAX MAX
The maximum amount of FICA tax to be collected for the tax year requested, as determined annually by the Social Security Administration.
NP
FIX DIFF CODE1

DIFF CODE2

FIX DIFF CODE3
Code associated with fixed monthly differential(s), assigned in PPDB, which make up the employee’s adjusted base rate.
JSL
FLSA SUBJ FLAG
Indicates whether the employee is (E)xempt or (N)on-exempt from the Fair Labor Standards Act.
JSL, LV
GRANT NO
A 6-digit number used to track payroll costs charged to various grants.  Grant numbers and phases are captured on individual employee time and gross pay adjustment screens preceded by “GNT”.  This field populates only if specifically overridden on an employee’s record.
LC
GRANT PHASE
A 2-digit number to further track payroll costs charged to various phases of a grant. Grant numbers and phases are captured on individual employee time and gross pay adjustment screens preceded by “GNT”.  This field populates only if specifically overridden on an employee’s record.
LC
GROSS PAY AMNT
Total monies paid to an employee before deductions.
NP
HOLIDAY CODE
(Irrelevant to most queries)
A code used by OSPA to forecast holidays for various employee groups.
JSL
HOURLY PAY RATE
Sometimes referred to as the equivalent hourly rate.  The adjusted base divided by the annualized number of hours per month as indicated by the “STANDARD HRS PER DAY” field.
JSL, LV
INCOME AMNT
The gross dollar amount associated with a pay or reimbursement code.
JSL
INCOME TYPE
INCOME TYPE CD
This code indicates how the income will be reported on the employee’s W-2.
(T)axable income
(R)eimbursement of an expense
(O)ther taxable income
(N)ot an income such as assumed wages of volunteer workers, housing subject to FICA but not federal/state income tax, and memo-only pay types such as leave without pay.
 
NOTE:  The field without “CD” at the end means the pay code was used on the gross pay adjustment screen rather than a time entry screen.
JSL
INDEX
A 5-digit SFMA code to classify accounting transactions at the lowest level of an agency’s organization structure. If an agency doesn’t designate to this degree, the index code defaults to 00000.
LC
INS WAIV 1

WAIV 2

INS WAIV 3

WAIV 4

INS WAIV 5

WAIV 6

INS WAIV 7
Indicates which insurance coverage benefit, if any, is being waived (declined).  Choices are:
D – Dental insurance
F – Flexible spending accounts
H – Health insurance
L – Life insurance
S – State Police insurance
T – Long term disability
 
NOTE:  “C” (for composite – health and dental) was used with BUBB health plans but is no longer valid as of 1999.
NP
JOB END DATE
The ending date of this assignment.  All 9s in the stop date means no end date has been established; the job is ongoing.
JSL, LV
JOB SHR PCNT
(Irrelevant to most queries)
As of 1999, this feature is no longer used.  Percent by which a job share employee shares insurance benefits, etc. with his/her job share partner.
DED
JOB START DATE
The beginning date of this job assignment.
JSL, LV
LABOR COST PCT1
LABOR COST PCT2
LABOR COST PCT3
LABOR COST PCT4
The percentage of this employee's costs to be assigned to the related cost center.
JSL
LABOR COST1
LABOR COST2
LABOR COST3
LABOR COST4
The Biennium-PCA-Index number used to define where the employee's payroll costs are to be charged.  Maximum of four can be assigned on a personnel action.
JSL
LEAVE ABS END
The actual date of return to pay status after an official leave without pay absence.
JSL
LEAVE ABS START
The date on which the employee began official leave without pay status.
JSL
LEAVE ACCRL CDE
A code used to indicate the employee’s rate of vacation accrual, controlled by benefit package.
JSL, LV
LOAD DATE
The date that this information was loaded to the warehouse.  This field has no relation to the Pay Period Ending date.
All tables
LV ABS READ CD
(Irrelevant to most queries)
The PPDB code defining the LWOP type.
JSL
LV ACTIVITY DATE
The pay period ending date for the last time the associated leave code had activity.  Activity includes usage, accrual, or when the leave code is reset to zero.
LV
LV ADJ COMMENTS​ Comment field from the P435 Employee Leave Data Screen.  Populated beginning May 2012.​ LV​
LV BALANCE DATE
Defines the “Beginning Balance” date for the pay period requested.  Usually is one month prior to current date.
LV
LV CONTINUOUS DATE
Pay period ending date for the first time the leave was used after being reset to zero.
LV
LV COST AMT
Dollar value of leave taken within the requested pay period. 
LV
LV HOURS
Number of hours associated with the transaction code requested.  Use in conjunction with the “TRANSACTION CODE” field.
LV
LV HOURS ADVANCED
Number of leave hours given to an employee in advance of regular accrual.  This typically happens when an employee leaves state service in the middle of a month.  Also can be as a result of special employment negotiations which allow immediate use of leave rather than the normal waiting period.
LV
LV HOURS LOST
Number of leave hours which an employee has lost due to accrual or usage limits.
LV
LV LIABILITY AMT
Dollar value of the positive end balance of leave hours.
LV
LV TYPE
The two-character leave code used to report summary leave data.  For example, three-character leave codes (i.e. VA2, VAF, VA4) roll up into one two-character leave type (i.e. VA).
LV
MASS TRANS DIST
(Irrelevant to most queries)
Identifies the transit district location established for the position as determined by PPDB.
JSL, LC, NP
MASS TRANS FLG
(Irrelevant to most queries)
Indicates whether or not a pay type is subject to mass transit tax levy.
JSL, LC
MEDR SUB INC
Amount of income subject to Medicare tax.
NP
MEDR TAX MAX
The maximum amount of Medicare tax to be collected for the tax year requested, as determined annually by the Social Security Administration.
 
NOTE:  Historically there hasn’t been a maximum issued for Medicare tax.  This is a placeholder against future use. Current default value is $19,999.99.
NP
NET PAY AMNT
Employee’s gross wages minus all deductions.
 
NOTE:  Direct deposits are deductions and are included in the “minus all deductions” total.  This means that employees with net pay deposits will show a zero in this field.
NP
NMBR ISS
This number reflects how many times the record has been calculated.  Corresponds to the “REG SET” number on P190.  This is normally 1 for Run 1, 2 for Run 2, etc., but does vary depending on paperwork timing.
DED, JSL, NP
NMBR SEQ
(Irrelevant to most queries)
Makes segment sequence-key a unique number.  This system-generated number is used to form a unique sequence-key.
JSL
NON TAXBL CODE
Flag in the deduction table indicating whether the deduction is to be taken before or after payroll taxes are calculated.
N – non-taxable, reduces taxable income
T – taxable, will not reduce taxable income
X – reduces taxable income and FICA-subject salary
F – flexible taxable income - if the amount is positive, functions the same as “N”;  if the amount is negative, functions the same as “T”.  NOTE:  This code is not currently used.
DED
NON TAXBL INC
Income not subject to state or federal income taxes.
NP
NUMBER OF HOURS
The number of hours associated with each agency object code.  Some codes such as employee expenses (health premiums, taxes, etc.) won’t have hours identified.
LC
NUMBER OF UNITS
The number of units (hour, piece, meeting, week, day, etc.) for which the associated pay code is calculated.
JSL
OPE CLASS CODE
(Irrelevant to most queries)
Code used to group similar types of deductions for financial reporting purposes. Voluntary payroll deductions may be from any of several general OPE (Other Payroll Expense) classes. This field is used to further identify the particular deduction type.  Classifications are:
C – composite benefit
D – dental insurance
F – flexible benefits
H – health insurance
L – life insurance
N – not an OPE benefit
S – state police insurance
T – long-term disability insurance 
DED
​OT PAY IND ​Used to calculate the FLSA regular rate of pay for the calculation of overtime.
Possible values include:
B - FLSA subject dollars and hours
D - FLSA subject dollars
N - neither dollars nor hours are FLSA subject
O - FLSA subject hours, paid at recalculated overtime rate
P - Paid according to the O/T MLT or the RATE UNIT rate on the PTB2 screen
​JSL
OTHR TAXBL INC
The taxable value of non-cash fringe benefits received by an employee.
NP
OVRTIME ELIGBL
Indicates eligibility for overtime pay with (Y)es or (N)o.
JSL, LV
PAY CODE
The way in which an employee's earnings are to be calculated - (S)alaried, (P)artial salary, (H)ourly, (U)ndefined.
JSL, LV
PAY DIST CODE
Also called the “check distribution code”.  An optional field for agencies to define the work group to which an employee is assigned.  Corresponds to the “PAY DISTR” field in PPDB.
DED, JSL, LC, LV, NP
PAY LEAVE IND
Indicates if a pay code is also a leave code. 
A - paid accruable leave
N - not a leave type
P - paid but not accruable
U - unpaid; does not count as time worked
W - unpaid; does count as time worked
JSL
PAY OPTION CODE
(Irrelevant to most queries)
Indicates the type of pay option on the compensation plan.  “A” is the normal range; all other codes are restricted to specific classifications as set by PPDB.
JSL, LV
PAY PERIOD END
 
The pay period ending date is the last calendar day of any given month.  Corresponds to the P190 “PAY PER END DATE” field.
All tables
PAY RATE
The pay rate entered through PPDB, which may be higher or lower than the base rate.  Differences here are usually due to other-than-100% FTE percentages paid on a partial salary basis.  This pay rate is dependent on the pay basis code.
JSL, LV
PAY RATE UNIT
Default pay rate assigned to the requested pay code.  Can be overridden using the gross pay adjustment screen.
JSL
PAY STEP
The employee's pay range step.
JSL
PAY TYPE CODE
The pay code used for employee payments.  Examples are  RG, VA, CTA, etc.
JSL, LV
PAY TYPE DESCR
Description associated with a pay type code.  Examples are regular, vacation, comp tm accr, etc.
JSL
PERS CLASS Code
The PERS job class code.  This is what PPDB has on the P030 screen. 
LC, NP
PCA
The 5-digit SFMA Program Cost Account code identifies detailed program structure designated by the agency and is used to track revenue, expenditures, and transfers against the agency’s Legislatively approved budget.
LC
PCNT AMNT CODE1
PCNT AMNT CODE2
PCNT AMNT CODE3
Indicates whether the fixed monthly differential being received is a (P)ercentage or (A)ctual amount.
JSL
PERCNT POSITION
The FTE percentage for this employee.  Refers to the percent of a position’s regularly scheduled work hours that will be worked by a particular employee.
JSL, LV
PERS CLASS CODE
The PERS job class code as assigned by the PPDB.  This code designates the benefit structure to which an employee belongs.
JSL, NP
POSNO
(Irrelevant to most queries)
Postion Number.  The identifying number assigned to the position by the agency.  Duplicates possible across agencies.
JSL, LC, LV
PRM WG NIC SAIF
Wages listed under the “SAIF SUB INC” field which are not subject to SAIF calculation.  Prior to PPE 8/31/04, this field did not get reported to SAIF.  All SAIF wages were calculated outside of benefit table direction.
NP
PROJECT NO
A 6-digit number used to track payroll costs charged to various projects.  Project numbers and phases are captured on individual employee time and gross pay adjustment screens preceded by “PJT”.  This field populates only if specifically overridden on an employee’s record.
LC
PROJECT PHASE
A 2-digit number to further track payroll costs charged to various phases of a project. Project numbers and phases are captured on individual employee time and gross pay adjustment screens preceded by “PJT”.  This field populates only if specifically overridden on an employee’s record.
LC
PT FT CODE
Employment status code - (F)ull time, (P)art time, (J)obshare, (T)emporary.
JSL
RANGE OPTN CODE
(Irrelevant to most queries)
Indicates the type of salary range on the compensation plan. “A” is the normal range; all other codes are restricted to specific classifications as set by PPDB.
JSL, LV
REC TYPE
Indicates whether the pay type was reported on the (G)ross pay adjustment or (T)imesheet screens.
JSL
RECOG SVC DATE
The date the employee started state service unbroken by chargeable leave without pay.
JSL, LV
REG WG NIC SAIF
Amount of regular wages which are not included in SAIF premiums (such as vacation leave, sick leave, etc.).
NP
REGULAR PAY IND
Indicates whether or not a pay type is to be included in computing the regular pay (RG, VA, SL, etc.). Used in labor pricing and leave accrual calculation.
JSL
REPRESENT CODE
Code used to define an employees representation group as assigned by PPDB.
JSL, LV
REPT DIST CODE
A code established by the agency for the identification of divisions/units for the purpose of personnel report distribution and subtotals.  Corresponds to the “RDC” field in PPDB.
DED, JSL, LC, LV, NP
RETIRE EE SHR1
RETIRE EE SHR2
RETIRE EE SHR3
(Irrelevant to most queries)
Amount of funds paid by the employee into the employee’s retirement system.  This is typically 6% of the “RETIRE SUB INC” field.
 
NOTE 1:  Fields 2 and 3 will be populated only if more than one retirement system has had activity for this employee.  (See the “CO SYS RETIRE” 2 and 3 fields for retirement system possibilities.)
 
NOTE 2: Only one agency (62800) uses this method.  All other employees use the “RETIRE ST PD” option.
NP
RETIRE ER SHR1
RETIRE ER SHR2
RETIRE ER SHR3
Amount of funds “matched” by the state and paid into the employee’s retirement system.  This amount varies based on the employee’s retirement system, and is calculated off of the “RETIRE SUB INC” field.
 
NOTE:  Fields 2 and 3 will be populated only if more than one retirement system has had activity for this employee.  (See the “CO SYS RETIRE” 2 and 3 fields for retirement system possibilities.)
NP
RETIRE ST PD1
RETIRE ST PD2
RETIRE ST PD3
Amount of funds “picked up” by the state and paid into the employee’s retirement system.  This is typically 6% of the “RETIRE SUB INC” field.
 
NOTE:  Fields 2 and 3 will be populated only if more than one retirement system has had activity for this employee.  (See the “CO SYS RETIRE” 2 and 3 fields for retirement system possibilities.)
NP
RETIRE SUB INC1
RETIRE SUB INC2
RETIRE SUB INC3
Amount of income subject to retirement.
 
NOTE:  Fields 2 and 3 will be populated only if more than one retirement system has had activity for this employee.  (See the “CO SYS RETIRE” 2 and 3 fields for retirement system possibilities.)
NP
RETIRE SUBJ FLG
Indicates whether or not a pay type is included in the calculation for wages subject to retirement.
JSL
RETIRE SYS CD1
RETIRE SYS CD2
RETIRE SYS CD3
Corresponds to the “RTMT SYST” field on the P010 screen.  Indicates to which retirement system employee belongs.  Possible codes are:
F – OPSRP plan, police & fire member
G – OPSRP plan, general member
L – Legislature member
N – Not a member
P – Judges plan member
S – PERS plan, general member
T – PERS plan, police & fire member
NP
SAIF EXEMP WAGE
Wages show in this field if an employee’s P010 screen is coded as not subject to SAIF.
NP
SAIF PCNT
Multiplier for SAIF-subject salary to determine applicable reportable income.
JSL
SAIF SUB INC
Amount of income subject to SAIF premiums.
NP
SAIF TAX EXMPT
This field is no longer used.
NP
SEP REASON CODE
The PPDB reason code for the employee’s separation.
JSL
SEPARATION CODE
This code indicates the number of times an employee has separated from this position.
JSL, LV
SEPARATION DATE
Date the employee separated from a particular concurrent job.
JSL
SEQ KEY
(Irrelevant to most queries)
Currently unused.  OSPS register variable sequence field two contains variable information that is used for sequence and control of the register records.
JSL
SFMS AGENCY
The 3-digit agency code being charged for the associated payroll fiscal transactions.
LC
SFMS APPN YEAR
The 4-digit biennium year associated with a transaction.
LC
STAT TAX EXEMPT
Employee’s state income tax exemptions as claimed on IRS Form W-4.  Two-digit number from 00 to 99.  System default if no W-4 submitted is 00.
NP
STATE TX STATUS
Employee’s filing status as reported on IRS Form W-4 for state tax withholding.  Choices are (S)ingle, (J)oint, or (N)one (for exempt).  System default if no W-4 submitted is (S)ingle.
NP
STD HRS PER DAY
(Irrelevant to most queries)
Hours per day which define the employee work week.  This code tells OSPA how many hours to use for the “HOURLY PAY RATE” field.
JSL
SUM TOT INC
This amount is the total of the “GROSS PAY AMNT” field, plus all non-cash taxable fringe benefits (DPT, MS, etc.)
NP
TAX YEAR
The calendar year for which taxes were paid.
DED, JSL, NP
TAXABLE INC FLG
Indicates whether or not a pay type is subject to federal and state income taxes.  Some hard-coding may exist to exempt particular pay codes from one or the other entities; not indicated in any field.
JSL
TEXT REG SEQ
Defines where in the employee record the pay type resides.
 
For pay codes recorded on a time screen, this code contains the date, page number, and line item.  Example: 20XX030201001 is March 2, 20XX, first page, first line item.  NOTE:  For employees not on Online Daily Time, the date will always be the last day of the month or job segment, whichever comes first.
 
For pay codes recorded on the gross pay adjustment screen, this field contains a four-digit tracking number followed by the associated income type code (refer to “INCOME TYPE” above for a list of income type codes).
JSL
TIME PER END DT
 
Time period ending date for the associated pay code.  This can be either the daily record or the monthly record date (may contain a mid-month date if the record is split).  Gross pay adjustments always have the end of the month in this field.
JSL
TRANS AMT
The dollars being charged to each associated agency cost structure.
LC
TRANS SUB INC
Amount of income subject to mass transit.
NP
TRANSACTION CODE
(CRUCIAL to most queries)
Code to designate what type of transaction is being reported for the leave detail requested.
          10 – Beginning Balance
          20 – Adjustment (manual or OSPA           adjustments)
          30 – Transfer in from Clearing Account
          32 – No longer used
          35 – Transfer in from one Concurrent Job to           Another
          40 – Entered Accrual
          50 – Leave Taken
          55 – Leave Paid
          60 – Automatic Accrual
          70 – Transfer out to Clearing Account
          72 – No longer used
          75 – Transfer out from one Concurrent Job to           Another
          80 – Lost Leave
          85 – Dropped Leave
          90 – Ending Balance
LV
UNEMP SUB CD
Corresponds to the “UNEM SUBJ” field on the P010 screen, and indicates whether or not employee is subject to unemployment tax.  Choices are (Y)es or (N)o.
 
NOTE:  The value “S” was used when records were first populated, but is no longer valid.  Existing records with no change since initial population will still display an “S”.
NP
UNEMP SUB FLG
Indicates whether or not a pay type is subject to unemployment insurance.
JSL
VENDOR AMNT
The portion of a deduction that goes to the vendor.
DED
VOL ASSUM WAGE
This field is used by agencies to record assumed wages worked by volunteers, captured by agencies on the P050 screen using code “AW”.
 
This field is also used by Legislature to capture the 6% Legislative PERS option.
NP
WC TAX SUB FLG
Corresponds to the “SAIF SUBJ” field on the P010 screen, and indicates whether or not employee is subject to Workers’ Compensation tax.  Choices are (Y)es or (N)o.
NP
WEEKS WORKED
(Irrelevant to most queries)
Number of weeks worked this pay period.
NP
WG FOR PRM B
For percent-based deductions, this field identifies the wage used in the calculation.
DED
WORK CHARGE CD
If there is an associated PCHG entry, or the field isn’t used, this field will be blank.
 
If there is any data in this field, it is informational only.  The labor costing was not allocated anywhere other than defaults (unless also allocated using the “COST ORIDE CD” field).  It is likely a PCHG entry needs to be generated to set up proper SFMA coding for overrides.
JSL
WORK FREQUENCY
Employee work frequency code.
  F  Full Time
  P  Part Time
  I  Intermittent
  J  Joint
LV
WORK SCHED CODE
This is the 3-character code used by OSPA to reference work schedule tables which specify hours projected to work each day.
 
See the Work Schedule Code Database or code lists for a complete listing of current work schedule codes.
WS
WORK SCHED END DATE
The date this work schedule ended.  See the dedicated work schedule table document for an explanation how to query for specific dates.
 
NOTE:  To query for codes without an end date, limit your query to 12/31/9999.
WS
WORK SCHED START DATE
The date this work schedule began.  See the dedicated work schedule table document for an explanation how to query for specific dates.
WS
YEAR PCA INDEX
This number is a composite of the “APPN YEAR”, “PCA CODE” and “INDEX CODE” fields.  Contains all the same numbers, just in one field instead of three.
 
This number and its components can be overridden on employee time and gross pay adjustment screens.
LC

 
Troubleshooting and User Maintenance
Change your password or kill a query using the user maintenance site.  NOTE: you will get a security challenge - please select the option to proceed. 
 
Common things that can make your query return unpredictable results include:

Problem Possible Solution
Query returns no results *Check filters for proper restrictions
*Check request is in proper format (20120229 or 2/29/12)
Leave data contains too many numbers Restrict your results by using the proper transaction code.

 
Contact Info
The Financial Business Services section manages the Datamart.  Their web page includes greater detail about the Datamart and the other info available. 
 
FBS Contact Info:
Aaron Wallace, Senior Datamart Business Analyst
155 Cottage Street NE, U50
Salem, OR 97301-3969
Phone: 503-373-0269
E-mail:  Datamart Support