How to fix the global search results?

Note: Issue-specific to Oracle database

In version 6.3 and before, there was a bug that caused incorrect results to come up in the global search on searching for an MRN (Medical Record Number). For example, a user wants to look for a participant with MRN' 12345' (imaginary 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 set 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;