PPS MANUAL

UCLA Payroll Services

A guide on the various databases, tables, reports and security features within the Personnel Payroll System (PPS).
PPS MANUAL, SECTION D - REPORTING

D3.0 PERSONNEL/PAYROLL SYSTEM AD-HOC REPORTS

 

D3.1 Overview

UCLA utilizes two ad-hoc reporting programs. The chart below list the similarities and differences between the two systems. Payroll Services offers training for the PP CDW. The class for PP QBD is no longer offered, however PP QBD is still used within the campus.

Comparison Between the Two Campus Approved Ad-Hoc Programs

bullet

PERSONNEL/PAYROLL QUERY DATA BASE (PP QDB) - D3.2

bullet

PERSONNEL/PAYROLL COMPUTER DATA WAREHOUSE (PP CDW) - D3.5

  • Computer software-based (BiQuery, etc.) front end
  • Cost of software and maintenance
  • EDB data are overwritten nightly (separate history tables)
  • Hourly as monthly estimate is only prebuilt logic
  • Data are not integrated
  • No snapshots
  • No employee balances stored
  • Fields names are frequently abbreviated
  • Data are grouped by table only
  • Web-based (Cognos) front end
  • Free to users
  • Data are organized by date (no separate history tables)
  • New data with prebuilt logic (i.e. hourly as monthly estimate, plus FTE data)
  • Integrated data
  • Employee Snapshots
  • Employee balances are stored
  • Logically named fields
  • Logically grouped data
 

D3.2 PERSONNEL/PAYROLL QUERY DATA BASE (PP QDB)

The Query Data Base (QDB) is a "data warehouse" for administrative data at UCLA. The Personnel/Payroll data in the QDB comes from the Employee Data Base (EDB), the History Data Base (HDB), the Expense Distribution Workfile (EDW), the Payroll Audit Record (PAR) and the Committed Salary Expenditure Reports (CSER).

QDB Data from the EDB and HDB is updated nightly; the data from the EDW is updated once a month; the data from the PAR is updated after each pay compute; and the CSER data is updated monthly.

The QDB provides departmental users with access to data so they can run their own queries for ad-hoc reporting. To receive access to run PP QDB queries, the user's department head must authorize the Department Security Administrator (DSA) to:

The user will automatically receive access to FS QDB when PP QDB is granted by AIS.

In order to retrieve the data from the QDB, the user must have a front-end PC or Macintosh tool, such as Excel, MS Access, BIQuery Hummingbird or Crystal Reports. Once the data has been retrieved, these same tools can be used to create customized reports. More information about the QDB, including the data dictionary, is available on the AIS/QDB web site.   The PP QDB Data Model is displayed below.


Personnel/Payroll Query Data Base


The data model (shown above) is a graphical representation of data tables that are linked together in order to facilitate the report writing process. Each box in the data model represents a QDB table. The blue boxes represent tables containing only current data; the yellow, red, green, and lavender boxes represent tables containing current and historical data; and the purple boxes represent reference data. 

D3.3 PPQDB Tables and Data Fields

The PP QDB tables are defined in the chart below which lists the PP QDB table, a brief description of the PP QDB table and the data fields contained in each PP QDB table.

PP QDB Table Description Fields
Employee The employee table provides current information related to the employee as a person. The fields in this table are applicable to all employees, regardless of personnel program or pay status. For example, all employee records are expected to contain a home address, ethnic identity and citizenship codes and social security numbers.  Address-Campus- Room
Address-Campus-Building
Address-Permanent- City
Address-Permanent- Line 1
Address-Permanent- Line 2
Address-Permanent- Zip
Address-Permanent-Foreign Country Code
Address-Permanent-Foreign Postal Code
Address-Permanent-Foreign Province
Address-Permanent-State
Campus Mailcode
Campus Phone-1
Citizenship Status Code-EDB
Country of Residency
Date of Birth
Education Level Code
Education Level Year
Email Address
Employee Identification Number
Employee First Name
Employee Full Name
Employee Last Name
Employee Middle Name
Employee Suffix Name
Employment Service Credit Months
Employment Service Credit Start Date
Employment Status
Ethnic Origin Code-Payroll
Federal Tax Marital Status
Federal Tax Withholding Allowance
Foreign Address Code
Home Address Release Flag
Home Phone Release Flag
Home Department Code-EDB
Home Phone Number
Most Recent Hire Date-EDB
Name-Spouse
Next Salary Review Date
Next Salary Review Type Code
Nonresident Alien Federal Tax Amount
Number of Registered UC Student Units
Organization Address Release Flag
Organization Phone Release Flag
Original Hire Date-EDB
Probationary Period End Date
Sex Code
Social Security Number
Spouse Release Flag
US Date of Entry
Veteran Disability Status Code
Veteran Status Code
Visa Type Code-EDB
Visa/Work Permit End Date
Employee Academic The employee academic table provides information on academic employees, including educational background, academic discipline, and the educational institution they go to after separation from UCLA.  Academic Programmatic Unit Code-EDB
Education Field Code-Highest Degree-EDB
Employee Full Name
Employee Identification Number
Employment Status
Institution Code-Future University Service-EDB
Institution Code-Highest Degree-EDB
Institution Name-Highest Degree
Institution Name-Future University Service
Quarter/Semesters/Mnths Extension to 8-Year Rule Time
Quarters/Semesters as a Teaching Assistant
Quarters/Semesters as Teaching Assistant Time Type
Quarters/Semesters As Teaching Assistant-As of Term
Quarters/Semesters in Unit 18
Quarters/Semesters in Unit 18 Time Type
Quarters/Semesters/Months on 19900 at 50%+ Time
Quarters/Semesters/Mnths on 19900 at 50%+ Time Type
Quarters/Semesters/Months Extension to 8-Year Rule
Quarters/Semesters/Months in the 8-Year Rule
Quarters/Semesters/Months in 8-Year Rule Time Type
Specialty Code-Current-1-EDB
Specialty Code-Current-2
Specialty Code-Current-3
Employee Actions The employee actions table provides current information related to action codes which describe the reasons for entries made to the employee records. For example, actions due to new hire, rehire, promotion, reclassification, transfer, fund change, and separation are recorded. Note that actions affecting the employee, such as hires, leaves of absence and separations, are associated with effective dates; actions affecting appointments and distributions, such as promotions and merit increases, are associated with their related appointment and distribution numbers as well as with effective dates. Action Name
Appointment Number
Distribution Number
Effective Date-ACT
Employee Full Name
Employee Identification Number
Employment Status
Personnel Action Code-Employee-1-ACT
Employee Actions History The employee actions history table provides historical information related to action codes which describe the reasons for entries made to the employee records. For example, actions due to new hire, rehire, promotion, reclassification, transfer, fund change, and separation are recorded. Note that actions affecting the employee, such as hires, leaves of absence and separations, are associated with effective dates; actions affecting appointments and distributions, such as promotions and merit increases, are associated with their related appointment and distribution numbers as well as with effective dates. Action Name
Appointment Number
Current Data Flag
Delete Flag
Distribution Number
Effective Date-ACT
Employee Full Name
Employee Identification Number
Employment Status
Entry Expiration Date
Incorrect Record Indicator
Personnel Action Code-Employee-1-ACT
System Entry Date
System Entry Time
Employee Addition The employee add table provides miscellaneous current data on employees which are unrelated to individual appointments. For example, an employee's leave of absence is contained in this table, as is employee-level collective bargaining unit data, student status code, and separation information. Destination Code - Separated Employees
Employee Full Name
Employee Identification Number
Employment Status
Employee Relations Code
Employee Representation Code
Employee Unit Code
Leave of Absence Begin Date
Leave of Absence Code-Current
Leave of Absence Return Date
Leave of Absence Type Code
Separation Date
Separation Reason Code
UC Student Status Code-EDB
Employee Addition History The employee add table provides miscellaneous historical data on employees which are unrelated to individual appointments. For example, an employee's leave of absence history is contained in this table, as is employee-level collective bargaining unit data, student status code history, and separation information. Current Data Flag
Destination Code - Separated Employees
Employee Full Name
Employee Identification Number
Employment Status
Employee Relations Code
Employee Relations Delete Flag
Employee Relations Incorrect Record Incorrect Indicator
Employee Representation Code
Employee Unit Code
Entry Expiration Date
Leave of Absence Begin Date
Leave of Absence Delete Flag
Leave of Absence Incorrect Record Indicator
Leave of Absence Return Date
Leave of Absence Code-Current
Leave of Absence Status Indicator
Separation Date
Separation Delete Flag
Separation Incorrect Record Indicator
Separation Reason Code
System Entry Date
System Entry Time
UC Student Status Code-EDB
Employee Alien The employee alien table provides current information related to an employee's tax treaties. Employee Full Name
Employee Identification Number
Employment Status
Tax Treaty Article Number
Tax Treaty Dollar Limit
Tax Treaty End Date
Tax Treaty Income Code
Tax Treaty Year-to-date Total Gross
Employee Appointments The employee appointment table provides current information related to an employee's appointments with campus departments. Each appointment contains data related to the terms and conditions of employment, such as effective dates, title code, personnel program, department code and anticipated percent of time. Academic Rank Date
Appointment Basis Code
Appointment Begin Date
Appointment Department Code
Appointment End Date
Appointment Number
Appointment Pay Rate-Annual
Appointment Pay Schedule Code
Appointment Representation Code
Appointment Time (Anticipated)
Appointment Type Code
Duration of Appointment Code
Employee Full Name
Employee Identification Number
Employment Status
Fixed/Variable Code
Leave Accrual Code
Paid Over Code
Personnel Program Code
Rate Code
Salary Grade
Sub-location Code
Time Reporting Code
Title Code
Title Name - Abbreviated
Title Unit Code
Employee Appointments History The employee appointment table provides historical information related to an employee's appointments with campus departments. Each appointment contains data related to the terms and conditions of employment, such as effective dates, title code, personnel program, department code and anticipated percent of time.  Academic Rank Date
Appointment Basis Code
Appointment Begin Date
Appointment Department Code
Appointment End Date
Appointment Number
Appointment Pay Rate-Annual
Appointment Pay Schedule Code
Appointment Representation Code
Appointment Time (Anticipated)
Appointment Type Code
Current Data Flag
Delete Flag
Duration of Appointment Code
Employee Full Name
Employee Identification Number
Employment Status
Entry Expiration Date
Fixed/Variable Code
Incorrect Record Indicator
Iteration Number
Leave Accrual Code
Paid Over Code
Personnel Program Code
Rate Code
Salary Grade
Sub-location Code
System Entry Date
System Entry Time
Time Reporting Code
Title Code
Title Name - Abbreviated
Title Unit Code
Employee Awards The employee awards table provides historical information on performance and incentive awards received by non-academic employees.  Award Type Code-EDB
Award ADC Code
Award Amount-EDB
Award Date-EDB
Award Number
Award Source Code-EDB
Employee Full Name
Employee Identification Number
Employment Status
Employee Benefits 1 The employee benefits 1 table provides current benefits eligibility information that pertains to all employees. For example, all employee records are expected to contain retirement eligibility codes, FICA codes and benefits eligibility level indicator (BELI) codes. BELI Effective Date
Benefits Eligibility Level Indicator-Assigned
Benefits Eligibility Level Indicator-Derived-EDB
Employee Full Name
Employee Identification Number
Employment Status
FICA Eligibility Code-EDB
Period of Initial Eligibility End Date
Primary BELI Status Qualification Code
Primary BELI Status Qualification Eff Date
Retirement/FICA Derive Code
Retirement System Code-EDB
Secondary BELI Status Qualification Code
Secondary BELI Status Qualification Eff Date
Employee Benefits 2 The employee benefits 2 table provides current information on benefits that have been chosen by eligible employees. For example, this table contains data on the dental, health and vision plans in which an employee has enrolled. Dental Plan Code-EDB
Dental Plan Coverage Code-EDB
Dental Plan Coverage Effective Date-EDB
Employee Full Name
Employee Identification Number
Employment Status
Future Dental Plan
Future Dental Plan Coverage Effective Date-EDB
Future Dental Plan Enrollment Reason Code
Future Health Plan
Future Health Plan Coverage Effective Date-EDB
Future Health Plan Enrollment Reason Code
Future Vision Plan
Future Vision Plan Coverage Effective Date-EDB
Future Vision Plan Enrollment Reason Code
Health Plan Code
Health Plan Coverage Code
Health Plan Coverage Effective Date-EDB
Vision Plan Code
Vision Plan Coverage Code-EDB
Vision Plan Coverage Effective Date-EDB
Employee Benefits Dependents The employee benefits dependents table provides current information on dependents that are covered by an eligible employee's dental, health and/or vision plans in which an employee has enrolled. Dependent Birth Date
Dependent Disabled Code
Dependent Health Coverage Effective Date
Dependent Name
Dependent Number
Dependent Relationship to Employee
Employee Full Name
Employee Identification Number
Employment Status
Employee Benefits Eligibility Hours The employee benefits eligibility hours table provides current information on monthly and total hours toward eligibility for full benefits and career status for employees in limited appointments. Employee Full Name
Employee Identification Number
Employment Status
Hours Toward Benefits Eligibility - 01
Hours Toward Benefits Eligibility - 02
Hours Toward Benefits Eligibility - 03
Hours Toward Benefits Eligibility - 04
Hours Toward Benefits Eligibility - 05
Hours Toward Benefits Eligibility - 06
Hours Toward Benefits Eligibility - 07
Hours Toward Benefits Eligibility - 08
Hours Toward Benefits Eligibility - 09
Hours Toward Benefits Eligibility - 10
Hours Toward Benefits Eligibility - 11
Hours Toward Benefits Eligibility - 12
Hours Toward Benefits Eligibility - Current
Hours Toward Benefits Eligibility - Total
Hours Toward Career Status Eligibility - 01
Hours Toward Career Status Eligibility - 02
Hours Toward Career Status Eligibility - 03
Hours Toward Career Status Eligibility - 04
Hours Toward Career Status Eligibility - 05
Hours Toward Career Status Eligibility - 06
Hours Toward Career Status Eligibility - 07
Hours Toward Career Status Eligibility - 08
Hours Toward Career Status Eligibility - 09
Hours Toward Career Status Eligibility - 10
Hours Toward Career Status Eligibility - 11
Hours Toward Career Status Eligibility - 12
Hours Toward Career Status Eligibility - Current
Hours Toward Career Status Eligibility - Total
Employee CSER Detail The employee Committed Salary Expenditure Reporting (CSER) table contains data that can be used to produce data simliar to the employee detail on the printed CSER reports that departments receive on a monthly basis. Account Number
Commitment Begin Date
Commitment End Date
Committed Benefit Amount
Committed Salary Amount
Cost Center Code
Cummulative Prior Benefit Amount
Cummulative Prior Salary Amount
Current Benefit Amount
Current Salary Amount
Department Code Account
Description of Service Earnings Type Code
Employee Full Name
Employee Identification Number
Employment Status
Employee Name
Fund Number
Ledger Year Month
Location Code
Organization Code
Project Code
Sub Account Code
Title Code
Total Benefit Amount
Total Salary Amount
Employee Deductions Audit The employee deductions audit table contains data on deductions, reductions and contributions applied to an employee's pay check.    Deductions which are considered personal and confidential that will be aggregated to a "composite" GTN number. Deduction Source Code
Employee Full Name
Employee Identification Number
Employment Status
Entry Sequence Number
Gross Override Indicator
Gross-To-Net (GTN) Number
GTN Amount
GTN Data Accs Indicator
GTN Description
GTN Group
GTN Type
PAR Control Number
Pay Cycle Code
Pay Transaction Date
Record Type
Employee Description of Service The employee description of service table provides information on various attributes of description of service (DOS) codes. For example, both short and long DOS names are provided in addition to codes which categorize the type of hours paid as regular, overtime or non-work hours. The fields on this table are a subset of the fields that are contained on the Personnel/Payroll System's DOS Code Table. Abbreviated DOS
Description of Service Code
DOS Payment Category
DOS Type of Hours Code
Full DOS Description
Employee Distribution The employee distribution table provides current information about distribution of an employee's pay. Each distribution is related to a corresponding appointment and contains data such as effective dates, the full accounting unit to which pay is charged, and percent of time. Note that the data in this table contains information related to how pay is intended to be distributed. If data related to actual pay expense is required, the Employee Expense Distribution table should be used. Account Number
Appointment Begin Date
Appointment Number
Cost Center Code
Description of Service Code
Distribution Charge Start Date
Distribution Charge Stop Date
Distribution Department Code
Distribution Number
Distribution Pay Rate-Hourly
Distribution Pay Rate-Monthly
Distribution Time (Anticipated)
Employee Full Name
Employee Identification Number
Employment Status
Expenditure Account Location Code
Fund Number
Hourly Rate Expressed as Monthly Estimate
Incorrect Record Indicator
Off/Above Scale Indicator
Perquisite Type Code
Project Code
Salary Step
Sub Account Code
Sub-location Code
Work Study Program Code
Employee Distribution History The employee distribution table provides historical information about distribution of an employee's pay. Each distribution is related to a corresponding appointment and contains data such as effective dates, the full accounting unit to which pay is charged, and percent of time. Note that the data in this table contains information related to how pay is intended to be distributed. If data related to actual pay expense is required, the Employee Expense Distribution table should be used. Account Number
Appointment Begin Date
Appointment Entry Date
Appointment Entry Time
Appointment Number
Cost Center Code
Current Data Flag
Delete Flag
Description of Service Code
Distribution Charge Start Date
Distribution Charge Stop Date
Distribution Department Code
Distribution Entry Date
Distribution Entry Time
Distribution Number
Distribution Pay Rate-Hourly
Distribution Pay Rate-Monthly
Distribution Time (Anticipated)
Employee Full Name
Employee Identification Number
Employment Status
Entry Expiration Date
Expenditure Account Location Code
Fund Number
Hourly Rate Expressed as Monthly Estimate
Incorrect Record Indicator
Iteration Number
Off/Above Scale Indicator
Perquisite Type Code
Project Code
Salary Step
Sub Account Code
Sub-location Code
Title Code
Work Study Program Code
Employee Earnings Audit The Employee Earnings Audit table contains data related to each individual transaction for an employee for the pay date.  It is similar to the data that is displayed on the IERN screens in OASIS.  Account Number
Appointment Representation Code
Appointment Type
Class Title Outline
Cost Center Code
Description of Service Code (DOS Earnings Type)
Distribution Hourly Pay Rate
Distribution Number
Distribution Monthly Pay Rate
Employee Full Name
Employee Identification Number
Employment Status
Employee Relations Code
Entry Sequence Number
Expense Transfer Reason Code
Fund Number
Leave Accrual Code
Location Code
Paid Amount
Paid Hours
Paid Percent
PAR Control Number
Pay Cycle Code
Pay Period End Date
Pay Schedule Code
Pay Transaction Date
Project Code
Rate Adjustment Indicator
Record Type
Sub Account Code
Sub-location Code
Title Code
Title Unit Code
Transaction Code (for example, LX, RX, FT)
Work Study Program Code
Employee Expense Distribution The employee expense distribution table contains information on all payroll transactions and corresponding benefit charges processed on employees for two fiscal years plus the fiscal year-to-date (for example, July 1999 to February 2002). Most of the data in this table is displayed on the PPP5302 Distribution of Payroll Expense reports that departments receive each month.  Account Number-PAR
Annuitant Health
Benefit type Code
Core Life Insurance
Core Medical Insurance
Cost Center Code-PAR
Dental Insurance
Department Code
Description of Service Code-PAR
Distribution Pay Rate-monthly
Distribution Payrate-hourly
Earnings Distribution Amount
Earnings Distribution Pay period end date
Earnings Distribution Percent
Earnings Distribution Hours
Employee Expense Distribution Year/Month
Employee Support Program
Expenditure Account Location code-PAR
Expense Distribution Sequence Number
Expense Transfer Indicator
Expense Transfer line number
Expense Transfer page number
Expense Transfer Year/Month
Fund Number-PAR
Graduate Student Health
Health Insurance
Hours/% time indicator
IAP Assessment Amount
IAP Benefit Offset Amount
Identification Number-Employee
Medicare
Name-employee-PAR
Object Code
Old Age Survivors Disability Insurance
OP Fee Remission
PAR Control Number
PAR Distribution Number
Pay adjustment indicator
Pay Transaction Date
Project code-PAR
Record generation pay cycle code
Retirement Matching
Retirement plan code
Retroactive pay indicator
Special benefits contribution code-PAR
Sub Account Code-PAR
Sub-location Code
Title Code-PAR
Title unit code (TUC)-PAR
Total benefit amount
UC Non-industrial Disability Insurance
UCLA Graduate Student Fee Remission
Unemployment Insurance
Vacation Accrual Amount
Vision Insurance
Workers' compensation
Employee Expense Distribution All The employee expense distribution table - all contains information on all payroll transactions and corresponding benefit charges processed on employees. Most of the data in this table is displayed on the PPP5302 Distribution of Payroll Expense reports that departments receive each month.  Account Number-PAR
Annuitant Health
Benefit type Code
Core Life Insurance
Core Medical Insurance
Cost Center Code-PAR
Dental Insurance
Department Code
Description of Service Code-PAR
Distribution Pay Rate-monthly
Distribution Payrate-hourly
Earnings Distribution Amount
Earnings Distribution Pay period end date
Earnings Distribution Percent
Earnings Distribution Hours
Employee Expense Distribution Year/Month
Employee Support Program
Expenditure Account Location code-PAR
Expense Distribution Sequence Number
Expense Transfer Indicator
Expense Transfer line number
Expense Transfer page number
Expense Transfer Year/Month
Fund Number-PAR
Graduate Student Health
Health Insurance
Hours/% time indicator
IAP Assessment Amount
IAP Benefit Offset Amount
Identification Number-Employee
Medicare
Name-employee-PAR
Object Code
Old Age Survivors Disability Insurance
OP Fee Remission
PAR Control Number
PAR Distribution Number
Pay adjustment indicator
Pay Transaction Date
Project code-PAR
Record generation pay cycle code
Retirement Matching
Retirement plan code
Retroactive pay indicator
Special benefits contribution code-PAR
Sub Account Code-PAR
Sub-location Code
Title Code-PAR
Title unit code (TUC)-PAR
Total benefit amount
UC Non-industrial Disability Insurance
UCLA Graduate Student Fee Remission
Unemployment Insurance
Vacation Accrual Amount
Vision Insurance
Workers' compensation
Employee Expense Distribution History The historical employee expense distribution table contains information on all payroll transactions and corresponding benefit charges processed on employees for older data back to February 1995. Most of the data in this table is displayed on the PPP5302 Distribution of Payroll Expense reports that departments receive each month.  Account Number-PAR
Annuitant Health
Benefit type Code
Core Life Insurance
Core Medical Insurance
Cost Center Code-PAR
Dental Insurance
Department Code
Description of Service Code-PAR
Distribution Pay Rate-monthly
Distribution Payrate-hourly
Earnings Distribution Amount
Earnings Distribution Pay period end date
Earnings Distribution Percent
Earnings Distribution Hours
Employee Expense Distribution Year/Month
Employee Support Program
Expenditure Account Location code-PAR
Expense Distribution Sequence Number
Expense Transfer Indicator
Expense Transfer line number
Expense Transfer page number
Expense Transfer Year/Month
Fund Number-PAR
Graduate Student Health
Health Insurance
Hours/% time indicator
IAP Assessment Amount
IAP Benefit Offset Amount
Identification Number-Employee
Medicare
Name-employee-PAR
Object Code
Old Age Survivors Disability Insurance
OP Fee Remission
PAR Control Number
PAR Distribution Number
Pay adjustment indicator
Pay Transaction Date
Project code-PAR
Record generation pay cycle code
Retirement Matching
Retirement plan code
Retroactive pay indicator
Special benefits contribution code-PAR
Sub Account Code-PAR
Sub-location Code
Title Code-PAR
Title unit code (TUC)-PAR
Total benefit amount
UC Non-industrial Disability Insurance
UCLA Graduate Student Fee Remission
Unemployment Insurance
Vacation Accrual Amount
Vision Insurance
Workers' compensation
Employee Future Committed Salary The employee future committed salary table provides information on future committed salary liens that departments entered into CSER. Account Number
Cost Center Code
Expenditure Account Location Code
Fund Number
Future Control Sequence Number
Future Description
Future Encumbrance Amount
Future Entry Date
Future Reference
Future Reversal Date
Sub Account Code
Employee Honors The employee honors table provides historical information on honors received by academic employees. Employee Full Name
Employee Identification Number
Employment Status
Honor Add/Delete/Change Code
Honor Date
Honor Type Code
Employee Layoffs The Employee Layoff table does not contain usable data at this time. Employee Full Name
Employee Identification Number
Employment Status
Layoff Title Code
Layoff Department Code
Layoff Unit Code
Layoff Add/Change/Delete Code
Layoff Salary
Layoff Salary Indicator
Layoff Percent Time
Rehire Begin Date
Rehire Expiration Date
Rehire Suspension Begin Term
Rehire Suspension End
Rehire Suspension Term Reason
Recall Begin Date
Recall Expiration Date
Recall Suspension Begin Term
Recall Suspension End
Recall Suspension Term Reason
Employee Leave Accrual The employee leave accrual table provides current leave balances for employees, including vacation and sick leave, compensatory time and paid time off balances. Compensatory Time Hours Balance-Current
Employee Full Name
Employee Identification Number
Employment Status
Paid Time Off Hours Balance-Current
Sick Leave Hours Balance-Current
Vacation Leave Hours Balance-Current
Vacation Leave Maximum Hours-Normal
Employee License Certificate The employee licenses and certificates table provides historical information on licenses and certificates held by employees and their renewal dates. Employee Full Name
Employee Identification Number
Employment Status
License/Certificate Code
License/Certificate Add/Delete/Change Code
License/Certificate Renewal Date
License Number
Employee Pay Audit The Employee Pay Audit table contains summary data of all the payroll transactions for an employee for each pay date.   Accrual Indicator
Accrued Sick Leave Hours
Accrued Vacation Hours Lost
Accrued Vacation Leave Hours
Check Date
Check Disposition Code
Check Number
Citizenship Code
DCP Plan Code
Employee Collective Bargaining Unit Code (CBUC)
Employee Full Name
Employee Identification Number
Employment Status
Employee Name
Employee Relations Code
Employee Relations Unit
Employment Representation Code
Employment Status
Federal Tax Exemptions
Federal Tax Marital Status
Federal Withholding Gross
FICA At Max Indicator
FICA Eligibility Code
FICA Eligibility Gross
Home Department Code
Medicare Gross
Name Suffix
Net Pay Amount
OASDI Gross
PAR Control Number
Pay Cycle Code
Pay Transaction Date
Primary Pay Schedule
Prior Employee ID
Record Type
Retirement Gross
Retirement Plan Code
Social Security Number
State Tax Itemized Deductions
State Tax Marital Status
State Tax Personal Deductions
State Withholding Gross
Stop/Unclaimed Check Indicator
Student Status
Total Gross
Total Nonwork Hours
Total Overtime Hours
Total Regular Hours
Unclaimed Source ID
Employee Sabbatical The employee sabbatical table provides information related to academic employee sabbatical credits. Employee Full Name
Employee Identification Number
Employment Status
Last Sabbatical Credit Balance
Sabbatical Credit Accrued
Sabbatical Credit Accrued Through Date
Sabbatical Credit Balance Total
Sabbatical Credit Date
Sabbatical Credit Used
Employee Service The employee service table provides information on where employees were employed prior to coming to UCLA and the number of months of UC service an employee had prior to being hired at UCLA. Employee Full Name
Employee Identification Number
Employment Status
Institution Code-Prior University Service-EDB
Prior Service Code
Prior Service Months
Employee Title Code The employee title table provides information on various attributes of title codes. For example, both short and long title names are provided in addition to the personnel program associated with the title and its overtime exemption status.  In addition, academic titles are described as to rank and series and pay basis. Abbreviated Title Name
Academic Senate Title Indicator
Academic Series Code
Active Title Code Flag
Federal Occupational Code
Federal Occupational Subgroup Code
Full Title Name
Ladder Title Indicator
Pay Basis Code
Tenure Title Indicator
Title Academic Rank Code
Title Class Title Outline Code
Title Code
Title FLSA Status
Title Health Flag
Title Overtime Exemption Code
Title Personnel Program
Title Professional & Support Salary Grade
Title Sabbatical Eligibility Code
Title Unit Code
Employee Title Academic Rate The employee title academic rate table provides information related to the academic salary scales. Annual Rate
Hourly Rate
Monthly Rate
Pay Representation Code
Rate Effective Date
Rate Sequence Number
Title Code
Employee Title Non-Academic Range The employee title non-academic range table provides information related to the non-academic salary ranges. Annual Range Maximum
Annual Range Midpoint
Annual Range Minimum
Hourly Range Maximum
Hourly Range Midpoint
Hourly Range Minimum
Monthly Range Maximum
Monthly Range Midpoint
Monthly Range Minimum
Pay Representation Code
Range Effective Date
Salary Grade
Salary Grade Type
Sub Location Code
Title Code
Employee Title Non-Academic Rate The employee title non-academic rate table provides information related to the non-academic salary scales. Annual Rate
Hourly Rate
Monthly Rate
Pay Representation Code
Rate Effective Date
Rate Sequence Number
Sub Location Code
Title Code
Employee Vacation Lost The employee vacation lost table provides a 12 month history of the number of vacation hours lost by an employee if vacation accrual for that month would have resulted in a balance greater than the maximum accrual allowable. Employee Full Name
Employee Identification Number
Employment Status
Leave Accrual Period-End Date
Leave Accrual Period-Vacation Hours Lost
Employee Work Study The work-study table provides information related to the work-study limit and the earnings that are subject to the work-study split for an employee per work-study fiscal year. Employee Full Name
Employee Identification Number
Employment Status
Fiscal Year
Fiscal Year to Date Work Study Gross
Prior Work Study Limit
Prior Work Study Limit As Of Date
Work Study Add/Change/Delete Code
Work Study Limit
Work Study Limit As Of Date
Work Study Program Code
Work Study Sequence Number


D3.4 PP QDB Reports on the UCLA On-line Financial System Reports (OFSR)

The UCLA On-line Financial System Reports gives the users access to several "canned" reports that can be run from PPQDB data on the web.  Payroll Reports available on the OFSR include the following:

The first two reports on the above list are enhancements to standard reports available on Document Direct-RDS.  The Separated Employees Report and the Online Department Contact List are available only from OFSR.  The first two reports are available to anyone with PPQDB access.  Access to the Separated Employees report is granted separately.  The Online Department Contact List can be accessed by any user who has access to EDB Entry/Update.  The OFSR reports have selection and sorting options and can also be downloaded into excel for further customization. 

Separated Employees Report

The Separated Employee Report provides a list of employees who are currently separated (i.e. employment status on the EDB on the date the report is run is "S")  from UCLA, UCOP or UC Merced and have a separation date that is within a range of dates specified by the user. The user may choose to display the separated employees of an entire location, such as all of UCLA, or for any organization, division, sub division or department within the location. The report displays employee name, home department number and name, date of separation, date the separation was entered into the EDB, campus work phone number and address.

The report is intended for use by administrative offices of organizations which need lists of separated employees in order to ensure the return of University equipment or to remove separated employees’ access to systems or property. It may also assist large departments in fulfilling their responsibilities to provide terminal pay to separating employees.

Because this report is for users who may not need access to other employee EDB information, a separate DACSS function was established which limits a user’s access to this one report.  A QDB user who is given access to this DACSS function will be able to retrieve separation dates and work addresses of any separated employee in any organization, but s/he will not be able to retrieve other data on separated employees nor any data on current employees. For example, a user who only has access to QDB and PPQDBSEP will not be able to retrieve data from the Payroll Expense Distribution nor the Committed Salary Expenditure reports.

Online Department Contact List

The Online Department Contact List provides users the ability to find the names, email address and phone number of the EDB preparers who have access to a given department.  The report was created to assist EDB preparers in finding the preparers in another department so that they can request alternate department access for their own department.   

Once the report has been accessed from the Payroll page of the OFSR, the user would select a location code from the drop-down menu and then click on the drop-down menu for the Department.  A drop-down list of departments in alphabetical order will be available.  Select the department you require and click on “Get Report.”

The report can be accessed by any user who has access to EDB Entry/Update.

 

D3.5 PERSONNEL/PAYROLL COMPUTER DATA WAREHOUSE (PP CDW)

The Personnel Payroll Computer Data Warehouse (PP CDW) is an ad-hoc reporting system containing reference tables for administrative data at UCLA. The Personnel/Payroll data in the PP CDW comes from the Employee Data Base (EDB), the Expense Distribution Workfile (EDW), the Payroll Audit Record (PAR) and the Committed Salary Expenditure Reports (CSER). The primary difference with PP CDW and PP QDB is that PP CDW uses a web base system (Cognos) and there is no need for a front-end PC or Macintosh tool, such as Excel, MS Access, BIQuery Hummingbird or Crystal Reports used by PP QDB.

Users are required to have a PP QDB Logon ID and BOL Logon ID and password. The Department Security Administrator (DSA) must authorize user access in DACSS to PPDDBINQ - Departmental EDB Inquiry; this access will define which employee records the user will be able to query.

Added Value from CDW

COGNOS Features

COGNOS Terminology

Personnel Payroll Computer Data Warehouse Landing Page

 

The PP CDW - Personnel & Payroll Reporting Landing Page is Split into Three Sections

 

A. Welcome

 

B. Getting Started

 

C. Need Help

The Welcome section contains stored reports that can be run according to set search criteria. If you have a specific need and would like to create your own reports, click on Query Studio to access the report builder tool.

 

This section contains Tools and Tables referencing the data storage system for PP CDW.
Below this chart is a sample of the PP CDW landing page. Please note the links on the image below are not functioning, this is for demonstration purposes.

 

 

 

D3.5.A Welcome Section

There are four sections under the Welcome Section. Each section contains reports that have been created for immediate use. For Example, under Pay Audit Reports, you will find Pay by Pay Transaction Date, which will allow you to view all pay transactions for a selected period for an individual or a department.

 

D3.5.B  Getting Started - Query Studio

This section allows users to create custom reports by drag-and-drop of data elements into the workspace. The toolbar provides the ability to manipulate the data such as filter, or sort. Once your report is complete you can store the report for future use or download the data to excel.

There are two methods to Launch Query Studio:

 

Below is the opening page for Query Studio. Under Personnel and Payroll, you will find twelve data 'trees' containing data elements that can be selected and inserted into the workspace to create an ad-hoc report. Once the data elements are brought over to the workspace on the right side of the screen, parameters can be set using the tool bar and the data is retrieved by clicking, Run once with all data, located at the lower right-hand corner.

Query Studio Workspace:

 

D3.5.C  Need Help

When you first visit PP CDW, locate the PP Resources: Helpful Hints on Using Cognos and PP Data. The Personnel and Payroll model in Cognos is comprised of twelve ‘trees’ of data that are organized by their content. Each tree contains all of the data elements that can be linked within a single query using that folder.

The nature of this data source is such that no more than data tree may be used when running a query in Query Studio.

Personnel and Payroll

The main container that houses all the data trees for Personnel and Payroll data

Expense Distribution

All the elements that create an Expense Distribution record. Data available beginning February 2006.

Appointment

All the elements that create an Appointment record. Data available beginning June 2006.

Distribution

All the elements that create a Distribution record. Data available beginning June 2006.

Appointment & Distribution Snapshots

New in CDW: All the Appointment & Distribution elements frozen at the end of each month to make a snapshot. Data available beginning June 2006.

Actions

All the elements that create an Actions record. Data available beginning September 2008.

Pay Audit

All the elements that create a Pay Audit record. Data available beginning with Pay Transaction Date May 1, 2005.

Pay Audit & Earn Audit

All the elements that create an Earn Audit record, including the corresponding Pay Audit record. Data available beginning with Pay Transaction Date May 1, 2005.

Pay Audit & Deduction Audit

All the elements that create a Deduction Audit record, including the corresponding Pay Audit record. Data available beginning with Pay Transaction Date May 1, 2005.

Employee Balances

New in CDW: Shows all the events affecting an employee on a monthly basis, including actions, vacation/sick leave, benefits/career eligibilities and hours lost. Data available beginning February 2006.

Committed Salary

All the elements that create a Committed Salary record. Data available beginning March 2008.

Title Ranges & Rates

Reference Tree: A listing of title codes with academic and non-academic rate and range information.

Employee Reference

Reference Tree: Information for a specific employee (honors, awards, work study, layoffs, background check and license/certificate)

 

 

to top


Previous Next