Fixing duplicate PPIDs

Error: Migration failed for change set db/3.4/schema.xml::Unique PPID per collection protocol::vlonushte: Reason: liquibase.exception.DatabaseException: java.sql.SQLIntegrityConstraintViolationException: ORA-02299: cannot validate (OPENSPECIMEN.CAT_CPR_CP_ID_PPID_UQ) - duplicate keys found

Reason: caTissue DB allowed duplicate PPIDs within a CP whereas OS DB has a constraint to enforce unique PPID per CP.

Solution: Update the duplicate PPIDs by suffixing participant IDs to make them unique

Query to check presence of duplicate PPIDs:

select
  cp.short_title, cpr.protocol_participant_id, count(cpr.protocol_participant_id)
from 
 catissue_coll_prot_reg cpr 
 inner join catissue_collection_protocol cp on cp.identifier = cpr.collection_protocol_id
group by
 cp.short_title, cpr.protocol_participant_id
having 
  count(cpr.protocol_participant_id) > 1;

Query to update duplicate PPIDs to <PPID>_<PARTICIPANT_ID>

update 
  catissue_coll_prot_reg 
set 
  protocol_participant_id = concat(concat(protocol_participant_id, '_'), identifier)
where 
  protocol_participant_id in ( 
    select 
	  t.protocol_participant_id 
	from ( 
	  select 
	    protocol_participant_id 
	  from
        catissue_coll_prot_reg reg 
	  group by 
	    reg.collection_protocol_id, reg.protocol_participant_id 
	  having
	    count(reg.protocol_participant_id) > 1  
  );

Query to update null PPIDs to <CP_ID>_<PARTICIPANT_ID>

update
  catissue_coll_prot_reg
set
  protocol_participant_id = concat(concat(collection_protocol_id, '_'), identifier)
where
  protocol_participant_id is null;