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.

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;

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

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:

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:

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:

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:

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.

select
  specimen_type, count(*)
from
  catissue_specimen
where
  specimen_type_id is null
group by
  specimen_type;

Step 1: Backup the existing specimen table.

create table catissue_specimen_bk as select * from catissue_specimen;

Step 2: Update the specimen table.