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.

1 2 3 4 5 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 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;