Country and State Code Changes Required for Grants.gov S2S Formset F

Overview

With the new forthcoming updated Federal Grants.gov forms (forms F) there are specific changes to the official country and state list options in drop downs throughout forms.  The new standards is explained in the following government announcement: https://www.fgdc.gov/standards/news/GENC.

Since existing Federal forms (i.e. SF-424 Form Family) are not yet updating the country code options we have to accommodate both lists in Kuali Research.  In order to accomplish this we will be adding new columns to the database tables for Country and State to track.  When using country lookups, the system will always use the new column entries first and fall back to the original column if it is blank.  So in the dropdown options all new values, all updated/renamed values, and all countries no longer used will be available.  If by chance an outdated country is selected and you attempt to print/validate a form using that value it will throw a validation error saying it's invalid and a new selection must be made.  By default we will not be inactivating any options in the Country table since they could technically be used in older forms; however, you can chose to inactivate options locally via the maintenance table.

Database Changes

To accommodate these needed updates we did make adjustments to the database table and data within the tables..  For table KRLC_CNTRY_T we added a new column of POSTAL_CNTRY_NM_V3.  We then copied all of the old values from POSTAL_CNTRY_NM into the new column if the country still exists in v3 and updated anything that changed with V3 to the new column. Where a country no longer exists, we explicitly cleared the v3 column for that entry. When using country lookups, we always use the v3 column first and fall back to the original column if it is blank.  The same logic was done to the state table KRLC_ST_T by adding POSTAL_STATE_NM_V3.

NOTE:  Since Country and State values link to S2S form mapping logic is it recommended to not alter the out of the box values; unless you chose to inactive values as appropriate for out dated countries.