Description of New Record/Table
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 **(Blank out, or drop) | |
SAL_ADMIN_PLAN - Salary Administration Plan **(blank out or 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. This new view would ease the process for obtaining a student's personal data - it would be in one view.
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 GEO_CODE IN_CITY_LIMIT SEX MAR_STATUS MAR_STATUS_DT BIRTHDATE **(Month and day only) BIRTHPLACE BIRTHCOUNTRY BIRTHSTATE DT_OF_DEATH HIGHEST_EDUC_LVL FT_STUDENT LANG_CD ALTER_EMPLID ADDRESS1_AC ADDRESS2_AC ADDRESS3_AC CITY_AC COUNTRY_OTHER ADDRESS1_OTHER ADDRESS2_OTHER ADDRESS3_OTHER ADDRESS4_OTHER CITY_OTHER COUNTY_OTHER STATE_OTHER POSTAL_OTHER NUM1_OTHER NUM2_OTHER HOUSE_TYPE_OTHER ADDR_FIELD1_OTHER ADDR_FIELD2_OTHER ADDR_FIELD3_OTHER IN_CITY_LMT_OTHER GEO_CODE_OTHER COUNTRY_CODE PHONE EXTENSION VA_BENEFIT CAMPUS_ID DEATH_CERTIF_NBR 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 , 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 FROM PS_PERSONAL_DATA PD