How to fix the global search results?

Note: Issue specific to Oracle database

In version 6.3 and before, there was a bug which caused incorrect results to come up in the global search on searching for an MRN (Medical Record Number). For example, user wants to look for a participant with MRN ‘12345’ (hypothetical numbers), the global search would show a participant with PPID '10102' which does not have the MRN. This issue is fixed in v7.0, hence for versions prior to that this issue needs to be fixed manually from the database.

The table OS_SEARCH_ENTITY_KEYWORDS is used to show the results for the items searched in the global search.

How to fix the search issue?

Please execute all the below SQLs on a test environment and then on the production server.

Step 1: Delete the incorrect MRN search keywords

delete from
  os_search_entity_keywords
where
  entity = 'collection_protocol_registration' and 
  name = 'medicalRecordNumber'

Step 2: Feed the correct MRN search keywords pointing to the correct registration records

insert into
  os_search_entity_keywords(identifier, entity, entity_id, name, value, status)
select
  os_search_entity_keywords_seq.nextval, 
  'collection_protocol_registration', r.identifier, 
  'medicalRecordNumber', lower(pmi.medical_record_number), 1
from
  catissue_part_medical_id pmi
  inner join catissue_participant p on p.identifier = pmi.participant_id
  inner join catissue_coll_prot_reg r on r.participant_id = p.identifier
where
  p.activity_status != 'Disabled' and
  r.activity_status != 'Disabled' and
  pmi.medical_record_number is not null;