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