There are a number of important query changes for the upgrade of Access.SMU to PeopleSoft version 8.9. (SMU will be live on version 8.9 on Monday, June 25) Some of your queries may require editing, and a few you may not be able to find. The most important change is how personal bio/demo data is stored in the system, and two others are table name changes and postal code format.
Personal Bio/Demo Data Changes
PeopleSoft 8.9 has a logical split between Human Resource objects and Higher Education objects. While the data for each resides in the same database, controls have been put into place to separate the two. Personal data must have an ultimate owner and it is now Human Resources. As such, Human Resource row level security has been attached to tables that contain personal data. Human Resource row level security restricts what a user can see based on Department ID. At best, most users can query only people in their own Department, but many do not have any Human Resource row level security at all, and so cannot query any people when personal data is involved.
Any of your queries that pull name, email, phone, and diversity (to name just the big ones) will not pull the data you expect, if it pulls any data at all. These queries will need to be edited, removing the "old" personal data tables and inserting the "new" ones.
To accommodate Higher Education, a number of query views have been delivered. The query views contain the same data as the parent table, except that the Human Resource row level security is now attached. These query views are what you must use if you wish to include personal bio/demo data in your queries. The attached file contains a list of the "old" tables and their corresponding "new" views. Any queries you run, or queries you have created for others to run, may need to be updated if they are to work correctly.
|
Core Person Model Record (the "old" table) |
PS Query View for Campus Solutions (the "new" table) |
| ACCOMPLISHMENTS | SCC_ACCOMP_QVW |
| ACCOM_DIAGNOSIS | SCC_ACCOM_D_QVW |
| ACCOM_JOB_TASK | SCC_ACCOM_T_QVW |
| ACCOM_OPTION | SCC_ACCOM_O_QVW |
| ACCOM_REQUEST | SCC_ACCOM_R_QVW |
| ADDRESSES | ADDRESS_NPC_VW |
| AUDIOMETRIC_TST | SCC_AUDIO_T_QVW |
| CITIZENSHIP | SCC_CITIZEN_QVW |
| CITIZEN_PSSPRT | SCC_CITZN_P_QVW |
| DISABILITY | SCC_DISABLE_QVW |
| DIVERSITY | SCC_DIVERS_QVW |
| DIVERS_ETHNIC | SCC_DIV_ETH_QVW |
| DRIVERS_LC | SCC_DRIVERS_QVW |
| EMAIL_ADDRESSES | SCC_EMAIL_QVW |
| EMERGENCY_CNTCT | SCC_EMERG_C_QVW |
| EMERGENCY_PHONE | SCC_EMERG_P_QVW |
| EYE_EXAM | SCC_EYE_EXA_QVW |
| NAMES | SCC_NAMES_QVW |
| PERSON | PERSON_NPC_VW |
| PERS_DATA_EFFDT | SCC_PER_EFF_QVW |
| PERS_DATA_CAN | SCC_PDE_CAN_QVW |
| PERS_DATA_USA | SCC_PDE_USA_QVW |
| PERS_NID | SCC_PERS_NI_QVW |
| PERSON_PHONE | SCC_PERS_PH_QVW |
| PHYSICAL_EXAM | SCC_PHYS_PH_QVW |
| PUBLICATIONS | SCC_PUBLICA_QVW |
| RESPIRATORY_EXM | SCC_RESP_EX_QVW |
| VISA_PMT_DATA | SCC_VISA_P_QVW |
| VISA_PMT_SUPPRT | SCC_VISA_S_QVW |
Table Name Changes
A few of the table names have changed, some delivered and some custom. We have identified the queries that contain missing tables and the functional areas will work to recreate some of them. If one of your queries includes a table that no longer exists, then you will not be able to find the query in the query search. If you cannot find a query that you know should exist, then email your functional lead, or you can email group DES ROOR, and we can provide you with the query definition (tables, fields, criteria) so that you can recreate your query.
Postal Code Format
Postal Code now includes a dash. Previously, postal code was a string of 9 numbers. Some queries include expressions that manually place a dash in the postal code. These will now appear with two dashes. Query output that includes address will have to be reviewed. This is a quick fix: simply remove the expression and pull the POSTAL field directly.
