Description of New Record/Table
New view (CU_PERSONAL_DATA) containing similar data to that provided in PERSONAL_DATA which will not require NPI access and include a few additional details.
DOB in month/day format (no year), last four of SSN (if exists), campus email (if exists). Any sensitive HR/payroll data would not be included.
Fields for record PERSONAL_DATA - PERSONAL_DATA for Rpting: (136)
| Key | Description |
|---|---|
Y | EMPLID - Empl ID |
COUNTRY_NM_FORMAT - Format Using | |
NAME - Name | |
NAME_INITIALS - Name Initials | |
NAME_PREFIX - Name Prefix | |
NAME_SUFFIX - Name Suffix | |
NAME_ROYAL_PREFIX - Name Royal Prefix | |
NAME_ROYAL_SUFFIX - Name Royal Suffix | |
NAME_TITLE - Title | |
LAST_NAME_SRCH - Last Name | |
FIRST_NAME_SRCH - First Name | |
LAST_NAME - Last Name | |
FIRST_NAME - First Name | |
MIDDLE_NAME - Middle Name | |
SECOND_LAST_NAME - Second Last Name | |
SECOND_LAST_SRCH - Second Name | |
NAME_AC - Alternate Character Name | |
PREF_FIRST_NAME - Preferred First Name | |
PARTNER_LAST_NAME - Last Name Partner | |
PARTNER_ROY_PREFIX - Prefix Partner | |
LAST_NAME_PREF_NLD - Last Name Preference | |
NAME_DISPLAY - Display Name | |
NAME_FORMAL - Formal Name | |
COUNTRY - Country | |
ADDRESS1 - Address Line 1 | |
ADDRESS2 - Address Line 2 | |
ADDRESS3 - Address Line 3 | |
ADDRESS4 - Address Line 4 | |
CITY - City | |
NUM1 - Number 1 | |
NUM2 - Number 2 | |
HOUSE_TYPE - House Type | |
ADDR_FIELD1 - Address Field 1 | |
ADDR_FIELD2 - Address Field 2 | |
ADDR_FIELD3 - Address Field 3 | |
COUNTY - County | |
STATE - State | |
POSTAL - Postal Code | |
GEO_CODE - Tax Vendor Geographical Code | |
IN_CITY_LIMIT - In City Limit | |
SEX - Gender | |
MAR_STATUS - Marital Status | |
MAR_STATUS_DT - Marital Status Date | |
BIRTHDATE - Date of Birth **(Month and day only) | |
BIRTHPLACE - Birth Location | |
BIRTHCOUNTRY - Birth Country | |
BIRTHSTATE - Birth State | |
DT_OF_DEATH - Date of Death | |
HIGHEST_EDUC_LVL - Highest Education Level | |
FT_STUDENT - Full-Time Student | |
LANG_CD - Language Code | |
ALTER_EMPLID - Alternate Employee ID | |
ADDRESS1_AC - Alternate Character Address | |
ADDRESS2_AC - Alternate Character Address 2 | |
ADDRESS3_AC - Alternate Character Address 3 | |
CITY_AC - Alternate Character City | |
COUNTRY_OTHER - Country - Other | |
ADDRESS1_OTHER - Address Line 1 | |
ADDRESS2_OTHER - Address Line 2 | |
ADDRESS3_OTHER - Address Line 3 | |
ADDRESS4_OTHER - Address Line 4 | |
CITY_OTHER - City - Other | |
COUNTY_OTHER - County - Other | |
STATE_OTHER - State - Other | |
POSTAL_OTHER - Postal Code - Other | |
NUM1_OTHER - Number 1 Other | |
NUM2_OTHER - Number 2 Other | |
HOUSE_TYPE_OTHER - House Type Other | |
ADDR_FIELD1_OTHER - Address Field 1 | |
ADDR_FIELD2_OTHER - Address Field 2 | |
ADDR_FIELD3_OTHER - Address Field 3 | |
IN_CITY_LMT_OTHER - In City Limit Other | |
GEO_CODE_OTHER - Geo Code Other | |
COUNTRY_CODE - Country Code | |
PHONE - Telephone | |
EXTENSION - Phone Extension | |
VA_BENEFIT - VA Benefit | |
CAMPUS_ID - Campus ID | |
DEATH_CERTIF_NBR - Death Certificate Nbr | |
FERPA - FERPA | |
PLACE_OF_DEATH - Place of Death | |
US_WORK_ELIGIBILTY - Eligible to Work in U.S. | |
MILITARY_STATUS - Military Status | |
CITIZEN_PROOF1 - Citizenship (Proof 1) | |
CITIZEN_PROOF2 - Citizenship (Proof 2) | |
MEDICARE_ENTLD_DT - Date Entitled to Medicare | |
HONSEKI_JPN - Honseki Prefecture | |
MILITARY_STAT_ITA - Military Status Italy | |
MILITARY_TYPE_ITA - Type of Service | |
MILITARY_RANK_ITA - Function or Rank | |
MILITARY_END_ITA - Military End Date | |
ENTRY_DT_FRA - Date of First Entry in France | |
MILIT_SITUATN_FRA - Military Status | |
CPAMID - CPAM ID | |
BILINGUALISM_CODE - Bilingualism Code | |
HEALTH_CARE_NBR - Health Care Number | |
HEALTH_CARE_STATE - Health Care Province | |
MILIT_SITUATN_ESP - Military Status | |
SOC_SEC_AFF_DT - Social Security Affiliation Dt | |
MILITARY_STAT_GER - Military Status | |
EXPCTD_MILITARY_DT - Expected Military Date | |
HR_RESPONSIBLE_ID - HR Responsible ID | |
SMOKER - Smoker | |
SMOKER_DT - Smoker Date | |
GVT_CRED_MIL_SVCE - Creditable Military Service | |
GVT_MILITARY_COMP - Uniformed Service | |
GVT_MIL_GRADE - Military Grade | |
GVT_MIL_RESRVE_CAT - Reserve Category | |
GVT_MIL_SEP_RET - Military Separation Status | |
GVT_MIL_SVCE_END - Military Service End Date | |
GVT_MIL_SVCE_START - Military Service Start Date | |
GVT_MIL_VERIFY - Military Service Verified | |
GVT_PAR_NBR_LAST - Last Personnel Action Reqst # | |
GVT_UNIF_SVC_CTR - Notify Military Pay Center | |
GVT_VET_PREF_APPT - Veterans Preference | |
GVT_VET_PREF_RIF - Veterans Preference RIF | |
GVT_CHANGE_FLAG - Change Flag | |
GVT_DRAFT_STATUS - Draft Status | |
GVT_YR_ATTAINED - Year Attained | |
DISABLED_VET - Disabled Veteran | |
DISABLED - Disabled | |
GVT_DISABILITY_CD - Disability Code | |
GRADE - Salary Grade **(drop) | |
SAL_ADMIN_PLAN - Salary Administration Plan **(drop) | |
GVT_CURR_AGCY_EMPL - Current Agency Employee **(drop) | |
GVT_CURR_FED_EMPL - Current Federal Employee **(drop) | |
GVT_HIGH_PAY_PLAN - Highest Pay Plan **(drop) | |
GVT_HIGH_GRADE - Highest Grade **(drop) | |
GVT_PREV_AGCY_EMPL - Previous Agency Employee **(drop) | |
GVT_PREV_FED_EMPL - Previous Federal Employee **(drop) | |
GVT_SEP_INCENTIVE - Separation Incentive **(drop) | |
GVT_SEP_INCENT_DT - Separation Incentive Date **(drop) | |
GVT_TENURE - Highest Career Tenure **(drop) | |
GVT_PAY_PLAN - Pay Plan **(drop) | |
BARG_UNIT - Bargaining Unit **(drop) | |
LASTUPDDTTM - Last Update Date/Time |
Rational for Request
The PERSONAL_DATA record was built for query reporting but due to the NPI-access issue, few viewers/managers can use it or see queries which use it. Not being able to use this table in queries requires joining multiple disparate records to obtain the same data which resides in PERSONAL_DATA.
Why is this needed? Who does this benefit? How will it be used?
Currently, it order to obtain personal information for a student, several tables must be joined into the query. The name is in one table, the address is in another, campus email is in another (with an outer join, as it may not exist), sex is in another, military status is in another. This makes for complicated queries which stresses the system unnecessarily. Also, it makes the task of writing simple queries more cumbersome, especially for the novice query writer and other non tech query writer as well. This new view would ease the process for obtaining a student's personal data - it would be in one view. In addition, modification of queries in future will be much simpler for query writers as well.
Proposed SQL or other technical documentation
Select PD.EMPLID , PD.COUNTRY_NM_FORMAT , PD.NAME, PD.NAME_INITIALS , PD.NAME_PREFIX , PD.NAME_SUFFIX , PD.NAME_ROYAL_PREFIX , PD.NAME_ROYAL_SUFFIX , PD.NAME_TITLE , PD. LAST_NAME_SRCH , PD.FIRST_NAME_SRCH , PD.LAST_NAME , PD.FIRST_NAME , PD.MIDDLE_NAME , PD.SECOND_LAST_NAME , PD.SECOND_LAST_SRCH , PD.NAME_AC , PD.PREF_FIRST_NAME , PD.PARTNER_LAST_NAME , PD.PARTNER_ROY_PREFIX , PD.LAST_NAME_PREF_NLD , PD.NAME_DISPLAY , PD.NAME_FORMAL , PD.COUNTRY , PD.ADDRESS1 , PD.ADDRESS2 , PD.ADDRESS3 , PD.ADDRESS4 , PD.CITY , PD.NUM1 , PD.NUM2 , PD.HOUSE_TYPE , PD.ADDR_FIELD1 , PD.ADDR_FIELD2 , PD.ADDR_FIELD3 , PD.COUNTY , PD.STATE , PD.POSTAL , PD.GEO_CODE , PD.IN_CITY_LIMIT , PD.SEX , PD.MAR_STATUS , PD.MAR_STATUS_DT , PD.BIRTHDATE **(Month and day only) , PD.BIRTHPLACE , PD.BIRTHCOUNTRY , PD.BIRTHSTATE , PD.DT_OF_DEATH , PD.HIGHEST_EDUC_LVL , PD.FT_STUDENT , PD.LANG_CD , PD.ALTER_EMPLID , PD.ADDRESS1_AC , PD.ADDRESS2_AC , PD.ADDRESS3_AC , PD.CITY_AC , PD.COUNTRY_OTHER , PD.ADDRESS1_OTHER , PD.ADDRESS2_OTHER , PD.ADDRESS3_OTHER , PD.ADDRESS4_OTHER , PD.CITY_OTHER , PD.COUNTY_OTHER , PD.STATE_OTHER , PD.POSTAL_OTHER , PD.NUM1_OTHER , PD.NUM2_OTHER , PD.HOUSE_TYPE_OTHER , PD.ADDR_FIELD1_OTHER , PD.ADDR_FIELD2_OTHER , PD.ADDR_FIELD3_OTHER , PD.IN_CITY_LMT_OTHER , PD.GEO_CODE_OTHER , PD.COUNTRY_CODE , PD.PHONE , PD.EXTENSION , PD.VA_BENEFIT , PD.CAMPUS_ID , PD.DEATH_CERTIF_NBR , PD.FERPA , PD.PLACE_OF_DEATH , PD.US_WORK_ELIGIBILTY , PD.MILITARY_STATUS , PD.CITIZEN_PROOF1 , PD.CITIZEN_PROOF2 , PD.MEDICARE_ENTLD_DT , PD.CPAMID , PD.BILINGUALISM_CODE , PD.HEALTH_CARE_NBR , PD.HEALTH_CARE_STATE , PD.SOC_SEC_AFF_DT , PD.EXPCTD_MILITARY_DT , PD.HR_RESPONSIBLE_ID , PD.SMOKER , PD.SMOKER_DT , PD.GVT_CRED_MIL_SVCE , PD.GVT_MILITARY_COMP , PD.GVT_MIL_GRADE , PD.GVT_MIL_RESRVE_CAT , PD.GVT_MIL_SEP_RET , PD.GVT_MIL_SVCE_END , PD.GVT_MIL_SVCE_START , PD.GVT_MIL_VERIFY , PD.GVT_PAR_NBR_LAST , PD.GVT_UNIF_SVC_CTR , PD.GVT_VET_PREF_APPT , PD.GVT_VET_PREF_RIF , PD.GVT_CHANGE_FLAG , PD.GVT_DRAFT_STATUS , PD.GVT_YR_ATTAINED , PD.DISABLED_VET , PD.DISABLED , PD.GVT_DISABILITY_CD , PD.LASTUPDDTTM , substr (NID.NATIONAL_ID, 6, 4) , EA.EMAIL_ADDR FROM PS_PERSONAL_DATA PD, PS_PERS_NID NID, PS_EMAIL_ADDRESSES EA WHERE PD.EMPLID = NID.EMPLID (+) AND 'USA' = NID.COUNTRY (+) AND 'PR' = NID.NATIONAL_ID_TYPE (+) AND PD.EMPLID = EA.EMPLID (+) AND 'CAMP' = EA.E_ADDR_TYPE (+)