Payroll Personnel Data Warehouse
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_v where nvl(to_char(appt_end_date,'MMDDYY'),'x') > '040197'
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: Yes, access to employee’s records is based upon a number of different variables:
a) the department that funded one or more of their pay distributions, b) their primary home department or
c) their alternate or work department. If the faculty has a pay distribution that is funded from your
department even if their primary home department is different, you can see their records.
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_V.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_v, ctvhme where ctvhme.hme_dept_no = nvl(edbper_v.alt_home_dept,
edbper.home_dept).
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:Complete and submit the PPS DW
and/or DS access application
indicating that you want cancel/terminate this account, ensure the person's
usercode is listed (if applicable). Any questions contact the Help Desk at (530) 754-2500
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_V), 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 into 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, the
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 records just disappear.
When programming with these three tables, it is a good idea to exclude the EDBPER_V, EDBAPP_V and EDBDIS_V 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: March 6, 2009