Personnel Payroll Query Database (PP QDB)
The data contained in the PP QDB comes from the Employee Database (EDB), including history screens, the Payroll Audit Record (PAR), the Committed Salary Expenditure Report (CSER), the Expense Distribution Report and the Financial System (FS). See the PPS Manual, Section D3.0 for more information on PP QDB.
This article provides information on the method to be used in gaining access to the data, software that may be used to query the database, and the nature and extent of the data available to the user. The PP QDB Clinic in-service training class provides instruction on using the system.
Access Needed to Run PP QDB Queries
To receive access to run PP QDB queries, the departmental user must:
- Obtain authorization from the department head.
- Have an OASIS logon ID and password.
- Have access to PPDDBINQ for a department or an organization.
The Departmental Security Administrator (DSA) must submit a System Access Request (SAR) to Information Technology Services(IT Services) to request access, as well as a QDB logon ID and password. See "Getting an IT Services or QDB Logon ID" in Related Information for more details.
The user will receive access to PP QDB when the OASIS function PPDDBINQ has been granted and the QDB logon ID and password have been created. The user will also have access to the Financial Systems (FS) QDB once the QDB logon ID and password have been created.
Software Required to Run PP QDB Queries
PP QDB queries may be run using a variety of software, including but not limited to BIQuery (formerly known as GQL), Excel, Crystal Reports and Access. The data are stored centrally and may be accessed through the various software products.
Nature and Extent of Data Available
The data are sorted into a series of tables for ease of use. The table titles give a general sense of the nature of the data to be found within the table. In most cases, the PP QDB tables are updated nightly. The EDB data only show what was in the database the day before; the history tables keep a rolling two years of data. Other table update schedules and data amounts are shown below.
|Table||Frequency of Update||Amount of Data Shown|
|PAR tables||Following each monthly and biweekly check write||Rolling thirteen months of data|
Expense Distribution (Current) contains up to a rolling three years of dataExpense Distribution (All) contains data from February 1995 to present
|CSER tables||Monthly||Rolling three months of data|
Sample PP QDB Queries
Learning to run queries from the PP QDB takes time and practice. Up until Fall 2008, Payroll Services offered a PP QDB Clinic through the Staff Development in-service training program. In addition to providing some basic information about the PP QDB, queries were demonstrated that could be helpful to those users who needed beginning level assistance in developing queries.
Samples of some of the queries demonstrated in the clinic can be accessed by selecting PP QDB Sample Queries in Related Information. A list of the queries available is provided below:
- Query 1: Employee and Benefits 1 Tables
- Query 2: Distribution of Payroll Expense
- Query 3: Expense Distribution – Vacation Accrual Assessment
- Query 4: Department List of Current Staff Employees by Appointment Type & REG Description of Service Code
- Query 5: Department List of Current Academic Employees with DOS Codes BRX and BRS for School of Medicine or with DOS Codes REG and RGA for Campus Academic Departments
- Query 6: Campus Addresses of Academic Senate Members Sorted by Series
- Query 7: Campus Addresses of Staff Employees in Non-Exempt Titles
- Query 8: One Employee’s Appointment and Distribution History
- Query 9: Employees Who Received a Merit Increase
- Query 10: Payroll Audit Record - Leave Accrual and Usage
- Query 11: Payroll Audit Record - Hours and Pay by FAU for a Specified Pay Date
- Query 12: CSER and GL Balances Tables - Calculating a Projected Account Balance
- Query 13: Using FS Lookup Tables - Selecting Expense Distribution Activity by Fund Type
- Query 14: Calculate FTE from the Expense Distribution Table
- Query 15: IAP Assessments
- Query 16: IAP Usage
- Query 17: Anticipated Payments per Payday
Some helpful hints when structuring queries are also provided in Related Information.