Searching for Records to Use in Query
One of the more confusing aspects of working with Query (or any reporting tool) in a very complex database structure, such as PeopleSoft, is knowing what tables and fields you need in order to report correctly.
Selecting a View or Record
- You will use certain tables over and over again and you will know the table names without needing to do research.
- There will be times when you are familiar with the data from using PeopleSoft Pages, but you may not know what records the data exist in behind those pages. Most of the records in the database have an abbreviated name, which will give you some clues as to their contents.
- There will be existing queries used by others in your office that you can use as a starting point.
Terminology
- RECORD (TABLE): As used in Query, a Record is a collection of fields or columns that have something in common (i.e. the Addresses table contains fields for EmplID, Address 1, Address 2, City, State, etc.). (Such a collection is called a Table in customary database parlance.)
- VIEW: A view is a logical representation of data and may consist of data from multiple tables depending on how the record was defined. Many views are delivered with PeopleSoft and others are created by ITS Staff.
- A view makes it easier for the user to utilize the data without having to join the tables.
- Additionally, views may already have criteria associated with them.
- If an appropriate view of the required tables is not provided and the user requires data from multiple tables, the application user must know in which tables the data are stored, and how to join those tables.
- Views generally end with the letters VW. An exception is the WES_STUDENT view.
Some Commonly Used Wesleyan Records (Tables) and Views
The following list contains a few of the basic Record names, directing you to some of the most commonly used areas.
Note that users have varying degrees of access to tables within PeopleSoft which determines which records and queries they can access.
| Records / Views (Tables) | Description | More Information | Subject Area |
|---|---|---|---|
| ACAD_PLAN | Student Academic Plan Table | Contains the academic plans (or majors) of a student. Multiple rows for effective dates, and within effective dates for multiple plans. Also includes completion term, degree checkout status, and advisement status. | Academics |
| ACAD_PLAN_TBL | Academic Plan Table | A listing of Academic Plans and descriptions. | Academics |
| ACAD_PROG | Student Academic Program | Contains the academic program of a student. | Academics |
| ACAD_PROG_TBL | Academic Program Table | A listing of Academic Programs and descriptions. | Academics |
| ADDRESSES | Address Type | A table that contains multiple rows for address types and, within address type, multiple effective dated rows for addresses. Includes the country code (3 characters) of the address. | Address |
| CLASS_TBL | Class Table | The core table for data associated with classes, incuding the Course ID, session, class status, enrollment capacity, enrollment total, etc. | Course / Class |
| COUNTRY_TBL | Countries | Contains the long description for country codes. | Countries |
| CRSE_CATALOG | Course Catalog Data | Contains the codes for courses in the course catalog, including Course ID, description, grading basis, credits (units), component, etc. | Course / Class |
| DIVERS_ETHNIC | Ethnic Diversity | Includes EMPLID, ETHNIC_GRP_CD and PRIMARY_INDICATOR | Ethnicity |
| EMAIL_ADDRESSES | Email Addresses | Contains four fields: EMPLID; E_ADDR_TYPE; EMAIL_ADDR; PREF_EMAIL_FLAG | Emails |
| ETHNIC_GRP_TBL | Ethnic Group Table | Contains ETHNIC_GRP_CD (A, A1, A2…) with Descriptions and ETHNIC_GROUP (translate table 1, 2, 3…) | Ethnicity |
| ETHNICITY_DTL | Ethnicity Detail | Fields on Ethnicity page (ETHNICITY_DETAIL) – Campus Community > Personal Information > Biographical > Personal Attributes > Ethnicity | Ethnicity |
| EXT_DEGREE | External Degrees | External Degree related data by student. | External Organizations |
| EXT_ORG_PRI_VW | External Org Primary View | A view that gathers together information from various separate External Organization tables, including Location information, EXT_ORG_TYPE and Proprietorship. | External Organizations |
| EXT_ORG_TBL | External Organization Table | The base table for External Organizations. With the introduciton of PS Ver. 9.0, some fields were moved to other tables and views. | External Organizations |
| EXT_ORG_TBL_ADM | External Org Admissions | Contains External Organization data related to schools/ universities and various codes. | External Organizations |
| EXTR_ACTVTY_TBL | Extracurricular Activity Table | A listing of Extracurricular Activities and descriptions. | Activities |
| INSTR_ADVISOR | Instructor/Advisor table | Instructor/Advisor information with Academic Org and Instructor Type. | Instructors or Advisors |
| NAMES | Person Names | Shows detail on name, including first, middle and last for various name types. | Persons |
| ORG_INST_TBL | Org – Institution Table | External Organization grading scheme, etc. | External Organizations |
| ORG_LOCATION | Organization Location Table | External Organization location information. | External Organizations |
| ORG_LOCATION_VW | Organization Location View | External Organization location information. | External Organizations |
| PERS_DATA_SA_VW | Common Person Information | The Personal_Data record is one of the core records in the database. Pers_Data_SA_VW is a view of this record to be used for queries and contains personal and demographic information about people including name, home address, birthdate, ethnic group and sex. | Persons |
| PERSONAL_DATA | PERSONAL_DATA for Rpting | Persons | |
| RELATION_VW | Relationships view | Persons | |
| RELATIONSHIPS | Relationships | Persons | |
| SCC_ACCOMP_QVW | Accomplishments Query View | Activities | |
| SCC_CITIZEN_QVW (includes Country) | Citizenship Query View [includes Country] | Includes Fields: EMPLID, DEPENDENT_ID, COUNTRY, CITIZENSHIP_STATUS | Citizenship |
| SCC_DIV_ETH_QVW | Ethnic Diversity Query View | From DIVERS_ETHNIC. Includes EMPLID, ETHNIC_GRP_CD and PRIMARY_INDICATOR | Ethnicity |
| SCC_EMAIL_QVW | Email Addresses Query View | Contains four fields: EMPLID; E_ADDR_TYPE; EMAIL_ADDR; PREF_EMAIL_FLAG | Emails |
| SCC_ETH_PRI_VW | Ethnic Group Primary View | Includes EMPLID, PRIMARY_INDICATOR with ETHNIC_GRP_CD and ETHNIC_GROUP | Ethnicity |
| SCC_NAMES_QVW | Person Names Query View | Persons | |
| SCC_PER_EFF_QVW | Eff Dated Person Data Query Vw | Persons | |
| SCC_PRI_NAME_VW | Current Primary Name View | Persons | |
| STDNT_CAR_TERM | Student Career Term Table | Contains career related fields, including the form of study, grade points, unit counts (i.e. total audit, total in progress, total cumulative) and other valuable statistics. | Student Career |
| STDNT_ENRL | Student Enrollment Table | Contains the key enrollment status data for a student. Fields include course career, student enrollment status, last enrollment action, units earned, etc. | Student Enrollment |
| WES_ACAD_HIST | Wes and Trans Credits | This view was designed to collect all of a student’s credits in one place. There are 4 types of credits indicated in the field WES_CREDIT_TYPE. They are WES, NRS, TRAN, and TEST. The WES credits contain all Wesleyan Courses including sponsored NRS courses. The NRS credits include all credits from NRS Approved Programs. The WES credits come from the STDNT_ENRL and CLASS_TBL tables. The TRANS credits from the TRNS_CRSE_SCH, TRNS_CRSE_DET tables. The NRS approved credits come from the EXT_COURSE and EXT_ORG_TBL tables as well as the TRNS_CRSE_SCH, TRNS_CRSE_DET tables. Test credits come from the TRNS_TEST_DTL, and CRSE_OFFER tables. | Student |
| WES_CITIZENSHIP | No description [does not include Country. To display Country, use the function wes_get_citizenship] | Fields: EMPLID, CITIZENSHIP_STATUS, DESCR, DESCRSHORT | Citizenship |
| WES_INSTR_CLASS | Wes Instructor Classes | This view presents the classes from the view point of a given instructor. There is one record for each class for each instructor. In the PeopleSoft data structure, the Class_Instr table is a child to the Class_Mtg_Pat. So in a simple joined view there would be two records for a class with one instructor if it met at two different patterned times. This view removes that redundancy. It also includes the Wes_Enrl_Tot, which sums the enrollment across the offerings and a single field for the combined meeting days and times and locations. | Course / Class |
| WES_SCHEDULE | Wesleyan Schedule File | This view contains one record for each student for each class meeting time for any given semester. It is created using data from the STDNT_ENRL table and the WES_MTG_PATTERN view. | Course / Class |
| WES_STUDENT | Wesleyan Student Status | This view is designed so that we can see all pertinent information about a current student’s status by term. It contains information from the ACAD_PROG, ACAD_PLAN, STDNT_CAR_TERM, PS_ADDRESSES, PERSONAL_DATA, NAMES, EMAIL_ADDRESSES, and PERS_NID records. | Student |