PPS Section D3, Query Data Base (QDB)

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.

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.

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.

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

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.

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.

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 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.

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.

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.

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.

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.

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)

PPS MANUAL

UCLA Payroll Services

PPS MANUAL, SECTION D - REPORTING

D3.0 PERSONNEL/PAYROLL SYSTEM AD-HOC REPORTS