# Presentation: 006473

## eRA Address and Degree ChangesJuly 2003 Release 

- June 19, 2003

## Address Line 5 Retirement

- Ultimate goal: identify addresses by components
- Why start with line 5?
- Easiest part of the address to extract to structured fields
- Structured fields (city, state, zip) already exist in the database
- Many users are already entering information in the structured fields
- Opportunity to synchronize with the Commons
- Conforms with USPS guidelines

## Address Line 5 Changes

- Conversion of existing data, where possible
- Modification to address screens
- Modification to miscellaneous documents
- Line 5 will remain in the database

## Conversion of Address Line 5 

- Analysis of line 5 data:
- Domestic addresses with city, state, and zip
- Internat’l addresses with city, country, postal code
- The remainder can be anything
- Identify domestic line 5 that can be parsed into city, state, zip (validate against USPS database)
- Where line 5 and structured fields *both* contain data, convert only if data matches
- Data not converted will remain as is

## Modification to Address Screens

- Address line 5 will not be updateable
- Users will be instructed to reenter the information from line 5 in lines 1-4, and  erase the data in line 5
- Before saving *any* address changes:
- Line 5 must be empty
- Domestic addresses must have city, state, zip data
- Non-US and Canadian addresses must not have state code

## Example of Address Screen

## Modification to Other Documents

- Reports, mailers, summary statement, etc. where address is displayed
- Where Line 5 is currently displayed, it will be displayed only when there is data in the field
- Changes made so that city, state, zip is now included where not previously on the document, when Line 5 is null

## IRDB Changes

- Added columns to PV_GRANT_PI_MV
- instn_bus_country_seq_num
- pi_wrk_country_seq_num
- instn_bus_country_name
- pi_wrk_country_name
- Added columns to PV_GRANT_PI PowerView:
- country_seq_num
- country_name

## New Table

- USPS_cities_t captures detail from USPS “City State Product”
- Columns:
- city_name
- state_code
- county_name
- zip_code
- Monthly update from USPS will be applied to database

## Future Changes

- Clear out all data from line 5; drop from database
- Incorporate business rule validation against USPS database
- Migrate to atomic fields for all address lines

## Degree Changes

- Degree codes and categories
- “Earned degree” information
- Duplicate degree records

## Degree Codes and Categories

- 563 distinct degree codes currently in IMPAC II
- Ineffective for management reporting
- Data integrity issues
- New list of degree codes developed—start with degrees that represent 98% of the data
- Additional queries done to find the top five degrees by IC
- Degrees not on list can be entered in “other degree text” field, with corresponding generic degree code

## New List of Degrees

- AB   	Bachelor Of Arts BA   	Bachelor Of Arts BS   	Bachelor Of Science DDS  	Doctor Of Dental Surgery DMD  	Doctor Of Medical Dentistry DPH  	Doctor Of Public Health DSC  	Doctor Of Science DVM  	Doctor Of Veterinary Medicine EDD  	Doctor  Of Education MA   	Master Of Arts MBBS 	Foreign - Bachelor Of Medicine And Surgery MD   	Doctor Of Medicine MPH  	Master Of Public Health MS   	Master Of Science PHD  	Doctor Of Philosophy RN   	Registered Nurse SCD  	Doctor Of Science VMD  	Doctor Of Veterinary Medicine DRPH 	Doctor Of Public Health
- BSN     	Bachelor Of Science In Nursing DC       	Doctor Of Chiropractic DNSC   	Doctor Of Nursing Science DO      	Doctor of Osteopathy FAAN   	Fellow Of The American Academy Of Nursing JD        	Doctor Of Juris Prudence MB      	Foreign - Bachelor of Medicine MBA     	Master Of Business Administration MLS     	Master Of Library Science MPA     	Master Of Public Administration MSN     	Master Of Science In Nursing OD       	Doctor Of Optometry

- OTH  	Other BOTH    	Other Baccalaureate MOTH    	Other Masters DOTH    	Other Doctorate MDOT    	Other Doctor of Medicine VDOT    	Other Doctor of Veterinary Medicine DDOT    	Other Doctor of Medical Dentistry

**Current Commons**
**Degree Codes**

**Additional **
**Degree Codes**

**“****Other” **
**Degree Codes**

## Degree Categories and Subcategories

- Categories used for grouping by highest degree (e.g., summary statement)
- Not stated or cannot be determined
- No degree held
- Baccalaureate held
- Masters held
- Academic doctorate held
- Professional doctorate held

- Subcategories used for reporting granularity
- Other
- Baccalaureate
- Masters
- PhD Equivalent (PhD, SCD, DSC, DPH, DRPH)
- Other Doctorate
- Doctor of Medicine (or equivalent degree)
- Doctor of Veterinary Medicine (or equivalent degree)
- Doctor of Medical Dentistry (or equivalent degree)

- One “other degree”
- code to correspond to
- each subcategory

## Conversion of Person Degree Data

- For person_degs records with degree codes not on the new list of degrees:
- Move the degree code to other_degree_text
- Set the degree code column to the appropriate “other” degree code value
- Example: DE (Doctor of Entomology)
- In person_degs, move ‘DE’ to other_degree_text
- Set deg_code=‘DOTH’ (the “other” degree code for Other Doctorate)

## Changes to Application

- Subcategory displayed to user on entry of degree
- User who enters “other” degree will be required to enter other degree text
- Reports and documents pull from degree field *or* from other degree text field, as appropriate
- Use other degree text if degree is an “other” degree

## New Screen Shot

## Earned Degree Changes

- Problem in the interpretation of degree_status_code
- For trainee data, and for F30 and F31, if the deg_status_code is null then the degree *is not* earned
- For PI and Committee Member data (excluding F30 and F31), if the deg_status_code is null then the degree *is* earned.

## Solution

- Convert existing null data to ‘Y’ or ‘N’, depending on the business context
- Set a default in the application:
- If the calling module is TA, default the degree status code for a new person degree to ‘N’; otherwise, default to ‘Y’

## Duplicate Degree Records

- People with more than one of a specific type of degree store that degree once for each occurrence
- Will collapse all degree records that are obvious duplicates
- Within the application, display a warning message upon entry of a duplicate degree

## New Database Tables

- deg_subcategories_t
- deg_subcategory_code
- descrip
- deg_category_code
- deg_categories_t (replaces current_degs_t)
- deg_category_code
- descrip
- history_degs_t
- A replica of the current degs_t table
- Provides historic degree data to users working with degrees that are no longer stored in degs_t

## Changes to Database Tables

- degs_t
- Added deg_subcategory_code
- Added other_deg_code (indicates if degree is an “other degree” or not)
- Reinstated foreign key constraint from person_degs_t to degs_t
- person_degs_t
- Force entry of other_degree_text to upper-case
- New function to strip out punctuation (excluding blank spaces)
- Dropped initial_set_indicator_code
- Modified deg_status_code to “not null”
- Applied all changes to the IRDB

## Future Changes

- Analyze and drop unneeded degree columns  from the database
- Evaluate further collapses of duplicate degree records for a person, even with additional descriptor data
- Provide additional degree sort flexibility
- Provide validation on degree entry (e.g., give  warning for fellowships if post-doc degree is being entered for pre-doc activity code)
- Correct study field text data
- Add database element to capture honors, fellowships, licenses, awards, or board certification.