Payroll Personnel Data Warehouse

Frequently Asked Questions

This is a collection of questions frequently asked regarding the PPS Data Warehouse. Please send comments to PPS Data Warehouse Support.

QUESTION: When I am selecting current or future appointments, the numbers returned are less than I expected?
ANSWER: The EDB Entry/Update and EDB Inquiry screens of the UCOP PPS system show indefinite appointments and distributions as having ending dates of 99/99/99. So, specifying an ending date selection criteria of greater than the current date was sufficient to select all current and future appointments and distributions. The PPS data warehouse is different, indefinite ending dates are stored as null values (an Oracle data base will not allow a date that it considers to be invalid). The SQL to select current and future appointments would correctly read:
select * from edbapp where nvl(to_char(appt_end_date,'MMDDYY'),'x') > '040197'

QUESTION: I am accessing the data warehouse thru Microsoft Excel and Microsoft Query. My query was supposed to return all the appointments for our college - about 20,000 entries and it didn't. What happened? ANSWER: Microsoft Excel has a maximum number of lines that it will hold regardless of how many data base entries are returned. That maximum number is 16,384.

QUESTION: Why does CTLTCI table have multiple title codes descriptions for the same title code (e.g. TCI_TITLE_CODE = 0753)? This is creating a problem for us and we are having to parse out the duplicates.
ANSWER:The UCOP update cycle creates a new record in the CTLTCI table each time a title code is updated, causing the multiple title codes descriptions. The original copy of the table with the duplicates has now been renamed to CTLTCI_UCOP. The current version of the CTLTCI table has been processed to include only the most current entry per title code.

QUESTION: Does the data warehouse have the capability to be accessed via modem? If so, how would we do it?
ANSWER: I do not believe it can currently be accessed via modem as data warehouse users are not logging onto the Antares system; rather the client (your PC) is accessing the data base on the server (Antares), but not actually dialing in or logging on.

QUESTION: We have faculty that belong to a different college but do work for us, can we see their records? What about some staff that do volunteer work within our college?
ANSWER: Access to the employee’s records is based upon the home department that funded their distributions or their primary home department. If the faculty has a distribution that is funded from your department even if their primary home department is different, you can see their records. For staff that do volunteer work, you can set up a without salary distribution from the department for the staff.

QUESTION: How do I identify what department that an employee actually works at or receives mail at?
ANSWER: The employee’s administrative home department is EDBPER.HOME_DEPT and an optional work or alternate home department is EDBPER.ALT_HOME_DEPT. An employee may or may not have this additional department number, so the correct SQL condition statement for first checking the alternate address then the primary is:
select ... from edbper, ctvhme where ctvhme.hme_dept_no = nvl(edbper.alt_home_dept, edbper.home_dept) Note, the CTVHME table is the view of the home department translation table (CTLHME) with the name and address fields reformatted.

QUESTION: I did not see any reference to the AFPACC table. Prior to the DaFIS conversion, I would locate AFPACC records by linking DIS.ACCOUNT_NUM to AFPACC.AFP_ACCOUNT_NUM. Is there still a FAU_ACCT lookup to give me the account title description?
ANSWER: The AFPACC table has been obsoleted in favor of the DaFIS Chart of Accounts Tables, effective after the July 4, 1997 DaFIS conversion. Each component of the Full Accounting Unit (FAU) has a translation table. Please see the Data Dictionary for more information on these tables:
COAACR - DAFIS Chart of Accounts Account Table
COACRT - DAFIS Chart of Accounts Chart Table
COAOBJ - DAFIS Chart of Accounts Object Table
COAPRJ - DAFIS Chart of Accounts Project Table
COASAC - DAFIS Chart of Accounts Subaccount Table
COASOB - DAFIS Chart of Accounts Subobject Table

QUESTION:A person in our department who has access to the PPS Data Warehouse and PPS Decision Support has left the University. What is the procedure for terminating their access?
ANSWER:You can either contact the Help Desk at (530) 757-3299 with the person's usercode or complete the PPS DW/DS access application indicating that you want cancel this account. If the usercode begins with "DV" this indicates that they also have access to the production payroll screens (PPS) at Office of the President. Send an Email to PPS Administrative Assistant to have that access also cancelled. She can be contacted by phone at (530) 752-1758.

QUESTION:I am seeing pay distributions in the PPS Data Warehouse that aren't on the PPS Inquiry screen access to Office of the President. Why is that happening?
ANSWER:Records that have been deleted at Office of the President are flagged for deletion on the PPS Data Warehouse and then actually deleted during the update a week later. Three of the PPS Data Warehouse data tables; the person-related data (EDBPER), the appointment data (EDBAPP) and the payroll distribution data (EDBDIS); have two data fields that track the update information as data comes from Office of the President to the PPS Data Warehouse. The UCD_ADC_CODE and UCD_ADC_DATE indicate whether the local record has been added, deleted or changed and the date. If this is a new record at Office of the President that isn't on the PPS Data Warehouse, it is added with a UCD_ADC_CODE of 'A' and UCD_ADC_DATE = today's date. Unless that record is changed, the date and code will stay on the record for one week. If there was a change to the record at Office of the President, our PPS Data Warehouse record is updated and the UCD_ADC_CODE is set to 'C' and UCD_ADC_DATE is set to the update date - today. Again, these codes stay on the PPS Data Warehouse for one week. During the update one week later, these codes and dates are blanked out so that departments only picking up the 'changed' or 'added' records on a weekly basis don't pick them up a second time. The deleted records are handled much the same way. If a record (say a recently-ended pay distribution) is deleted at Office of the President, we set the UCD_ADC_CODE to 'D' and set the UCD_ADC_DATE to the current date on the matching record in our PPS Data Warehouse. One week later, we delete the record. This week delay allows department's update programs to identify records that are about to be deleted rather than having the record just disappear.

When programming with these three tables, it is a good idea to exclude the EDBPER, EDBAPP and EDBDIS records where the UCD_ADC_CODE is equal to 'D' since these records are about to be deleted. This exclusion has to be done with a null-value statement since you can't test a null data field for a value: NVL(UCD_ADC_CODE,'X') <> 'D'. (This command temporarily sets the UCD_ADC_CODE equal to 'X' if the field is null, so it would pass the test of not being equal to or flagged as a 'deleted' record.

Last Updated: February 15, 2006