Here are the changes made to the tables in regards to DATA_SENSITIVE fields in the Biotics 5.9.5 Release on March 22, 2017, and additional changes implemented in 5.9.8 and planned for 5.9.9.
EO:
Replace DATA_SENSITIVE_EO_IND with D_DATA_SENSITIVE_ID dropdown
Add D_DATA_SENSITIVE_CATEGORY_ID drop-down
SF:
Add D_DATA_SENSITIVE_ID dropdown
Add D_DATA_SENSITIVE_CATEGORY_ID drop-down
Add DATA_SENSITIVE_REASONS rich text field
EST:
Replace S_DATA_SENSITIVE_IND with D_DATA_SENSITIVE_ID dropdown
Add D_DATA_SENSITIVE_CATEGORY_ID drop-down
Add DATA_SENSITIVE_REASONS rich text field
In order to figure out what tables/views were affected, you can run the following query or refer to this solution. Note: Only ELEMENT_SUBNATIONAL, EO, and SOURCE_FEATURE tables were modified. No changes were made to ELEMENT_NATIONAL or other tables.
SELECT table_name, column_name FROM all_tab_columns where column_name like '%DATA_SENSITIVE%' and column_name like '%IND%' AND table_name not like'%UVW%' and table_name not like '%DVW%' and table_name not like '%HVW%' and table_name not like '%ELEMENT_NATIONAL%' order by table_name asc
* This query excludes the NatureServe views that end in HVW, DVW, and UVW. Those were a legacy of the Biotics 4 Data Exchange days and will eventually go away. You are free to modify them on your own, however, we highly recommend that you recreate these views with a different name going forward. Don’t worry, we don’t plan on removing these anytime soon and we would give everyone PLENTY of notice! However, we are not maintaining these views.
You can also use the query below to find all Invalid Objects, including broken views, in your database:
SELECT owner,object_name,subobject_name,object_type FROM all_objects WHERE status='INVALID' and object_name not like '%UVW' and object_name not like '%DVW' and object_name not like '%HVW'
Note: Remember when modifying your views, you will need to incorporate the new domain tables to pull in the correct display values.
The following are the values in the new domain tables:
D_DATA_SENSITIVE: Programs are not to modify the values in this table
D_DATA_SENSITIVE CATEGORY: Programs will be allowed to add additional values (see sql example below).
If you would like to add additional values to the D_DATA_SENSITIVE_CATEGORY table, you can use the following insert statements as a guide, modifying the text in red to whatever you want your descriptions to be. You can also modify the display order, but my suggestion would be to insert all the values you want in first in numeric display order and then do an update statement (sql below insert statements) once all of your rows were inserted:
SAMPLE SQL to insert additional rows into D_DATA_SENSITIVE_CATEGORY domain table
Insert into D_DATA_SENSITIVE_CATEGORY (D_DATA_SENSITIVE_CATEGORY_ID, DATA_SENSITIVE_CATEGORY_DESC, DISPLAY_ORDER) values (getnextseq('D_DATA_SENSITIVE_CATEGORY'),'This is the next data sensitive category', 5);
Insert into D_DATA_SENSITIVE_CATEGORY (D_DATA_SENSITIVE_CATEGORY_ID, DATA_SENSITIVE_CATEGORY_DESC, DISPLAY_ORDER) values (getnextseq('D_DATA_SENSITIVE_CATEGORY'),'This is the last data sensitive category', 6);
SAMPLE SQL to update display order for D_DATA_SENSITIVE_CATEGORY
update D_DATA_SENSITIVE_CATEGORY SET DISPLAY_ORDER = 1 WHERE D_DATA_SENSITVE_ID = 5;
update D_DATA_SENSITIVE_CATEGORY SET DISPLAY_ORDER = 2 WHERE D_DATA_SENSITVE_ID = 6;
update D_DATA_SENSITIVE_CATEGORY SET DISPLAY_ORDER = 3 WHERE D_DATA_SENSITVE_ID = 1;
etc...
BX-2694