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