Populate the correct specimen type id in specimen requirement and specimen table.

Reason: When upgrading from v6.1 to v6.3, there was a bug in migration script, which was populating the specimen_type for ‘Not Specified’ dropdown values as ‘Not Specified - <Class>’. The actual dropdown value was ‘<Class> - Not Specified’. Due to this, in specimen requirement and specimen table, NULL specimen_type_id was populated. The issue is fixed in 7.0 builds.

Specimen Requirements

Check NULL specimen_type_id present in the specimen requirement table.

1 2 3 4 5 6 7 select count(*) from catissue_cp_req_specimen where (specimen_class_id is null or specimen_type_id is null) and activity_status != 'Disabled';

Step 1: Take a backup of the existing specimen requirement table.

create table cat_cp_req_specimen_bk as select * from catissue_cp_req_specimen;

Step 2: Note down the dropdown IDs for each specimen class/type.

Query 1: select identifier from catissue_permissible_value where value='<specimen_type>';

Query 2: select identifier from catissue_permissible_value where value='<specimen_class>';

Example:

Specimen Class: Cell and Specimen Type: Cell - Not Specified

1 2 3 4 5 6 7 8 9 10 11 12 13 select identifier from catissue_permissible_value where value='Cell - Not Specified'; select identifier from catissue_permissible_value where value = 'Cell';

Step 3: Update specimen_type_id for each specimen class/type in specimen requirement table.

For Cell:

1 2 3 4 5 6 7 8 9 update catissue_cp_req_specimen set specimen_type_id = <output identifier of query 1> where specimen_type = 'Not Specified - Cell' and activity_status != 'Disabled' and specimen_type_id is null and specimen_class_id = <output identifier of query 2>;

For Molecular:

1 2 3 4 5 6 7 8 9 update catissue_cp_req_specimen set specimen_type_id=<output of query 1> where specimen_type = 'Not Specified - Molecular' and activity_status != 'Disabled' and specimen_type_id is null and specimen_class_id = <output identifier of query 2>;

For Tissue:

1 2 3 4 5 6 7 8 9 update catissue_cp_req_specimen set specimen_type_id = <output identifier of query 1> where specimen_type = 'Not Specified - Tissue' and activity_status != 'Disabled' and specimen_type_id is null and specimen_class_id = <output identifier of query 2>;

For Fluid:

1 2 3 4 5 6 7 8 9 update catissue_cp_req_specimen set specimen_type_id=21 where specimen_type = 'Not Specified - Fluid' and activity_status != 'Disabled' and specimen_type_id is null and specimen_class_id = <output identifier of query 2>;

Run the first query again to check if any records are present with specimen_type_id = NULL.

Specimen

Check NULL specimen_type_id in the specimen table.

1 2 3 4 5 6 7 8 select specimen_type, count(*) from catissue_specimen where specimen_type_id is null group by specimen_type;

Step 1: Backup the existing specimen table.

1 create table catissue_specimen_bk as select * from catissue_specimen;

Step 2: Update the specimen table.

For Cell:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 update catissue_specimen set specimen_type_id = ( select identifier from catissue_permissible_value where public_id = 'specimen_type' and value = 'Cell - Not Specified' ) where specimen_class = 'Cell' and specimen_type = 'Not Specified - Cell';

For Molecular:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 update catissue_specimen set specimen_type_id = ( select identifier from catissue_permissible_value where public_id = 'specimen_type' and value = 'Molecular - Not Specified' ) where specimen_class = 'Molecular' and specimen_type = 'Not Specified - Molecular';

For Tissue:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 update catissue_specimen set specimen_type_id = ( select identifier from catissue_permissible_value where public_id = 'specimen_type' and value = 'Tissue - Not Specified' ) where specimen_class = 'Tissue' and specimen_type = 'Not Specified - Tissue';

For Fluid:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 update catissue_specimen set specimen_type_id = ( select identifier from catissue_permissible_value where public_id = 'specimen_type' and value = 'Fluid - Not Specified' ) where specimen_class = 'Fluid' and specimen_type = 'Not Specified - Fluid';

Run the initial query from the specimen section to make sure there is no specimen_type_id with a NULL value.