...
...
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 |
...
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
Code Block | ||||
---|---|---|---|---|
| ||||
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 - Address Field 1 , PD.ADDR_FIELD2 - Address Field 2 , PD.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 , PD.COUNTY , PD.STATE , PD.POSTAL , PD.GEO_CODE , PD.IN_CITY_LIMIT , PD.SEX , PD.MAR_STATUS , PD.MAR_STATUS_DT , - Marital Status Date BIRTHDATE - Date of Birth **(Month and day PD.BIRTHDATE **(Month and day only) , BIRTHPLACE - Birth Location BIRTHCOUNTRY - Birth Country BIRTHSTATE - Birth State PD.BIRTHPLACE , PD.BIRTHCOUNTRY , PD.BIRTHSTATE , PD.DT_OF_DEATH - Date of Death , PD.HIGHEST_EDUC_LVL - Highest Education Level , PD.FT_STUDENT - Full-Time Student , PD.LANG_CD - Language Code , PD.ALTER_EMPLID - Alternate Employee ID , PD.ADDRESS1_AC - Alternate Character Address , PD.ADDRESS2_AC - Alternate Character Address 2 , PD.ADDRESS3_AC - Alternate Character Address 3 , PD.CITY_AC - Alternate Character City , PD.COUNTRY_OTHER - Country - Other , PD.ADDRESS1_OTHER - Address Line 1 , PD.ADDRESS2_OTHER - Address Line 2 , PD.ADDRESS3_OTHER - Address Line 3 , PD.ADDRESS4_OTHER - Address Line 4 , PD.CITY_OTHER - City - Other , PD.COUNTY_OTHER - County - Other , PD.STATE_OTHER - State - Other , PD.POSTAL_OTHER - Postal Code - Other , PD.NUM1_OTHER - Number 1 Other , PD.NUM2_OTHER - Number 2 Other , PD.HOUSE_TYPE_OTHER - House Type Other , PD.ADDR_FIELD1_OTHER - Address Field 1 , PD.ADDR_FIELD2_OTHER - Address Field 2 , PD.ADDR_FIELD3_OTHER - Address Field 3 , PD.IN_CITY_LMT_OTHER - In City Limit Other , PD.GEO_CODE_OTHER - Geo Code Other , PD.COUNTRY_CODE - Country Code , PHONE - Telephone EXTENSION - Phone Extension VA_BENEFIT - VA Benefit CAMPUS_ID - Campus ID PD.PHONE , PD.EXTENSION , PD.VA_BENEFIT , PD.CAMPUS_ID , PD.DEATH_CERTIF_NBR - Death Certificate Nbr FERPA - , PD.FERPA , PD.PLACE_OF_DEATH - Place of Death , PD.US_WORK_ELIGIBILTY - Eligible to Work in U.S. , PD.MILITARY_STATUS - Military Status , PD.CITIZEN_PROOF1 - Citizenship (Proof 1) , PD.CITIZEN_PROOF2 - Citizenship (Proof 2) , PD.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 , 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 (+) |