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 , 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 (+)