How to get rid of duplicate events from OS_SPMN_COLL_RECV_DETAILS_VIEW?

The problem is - in the previous incarnation of OpenSpecimen i.e. caTissue, the collection and received events were copied from primary specimens to all of its descendants. As a result, our view query, which doesn't have any limiting condition on lineage, ends up having as many collection and received events as the depth of the specimen in the specimen tree.

To solve above problem, soft delete all the collection and received events for non-primary specimens.

Step 1: Find count of collection and received events for non-primary specimens.

select
  count(re.identifier)
from
  catissue_specimen s
  inner join catissue_form_record_entry re on re.object_id = s.identifier
  inner join catissue_form_context fc on fc.identifier = re.form_ctxt_id
  inner join dyextn_containers f on f.identifier = fc.container_id
where
  s.lineage != 'New' and
  re.activity_status = 'ACTIVE' and
  f.name in ('SpecimenCollectionEvent', 'SpecimenReceivedEvent') and
  fc.deleted_on is null

If the query returns anything but non-zero, then you need to execute DML given in step 2.

Step 2: Soft delete collection and received events logged for non-primary specimens.

update
  catissue_form_record_entry
set
  activity_status = 'CLOSED'
where
  object_id in (
    select 
      identifier 
    from 
      catissue_specimen 
    where 
      lineage != 'New' and 
      activity_status != 'Disabled'
  ) and
  form_ctxt_id in (
    select 
      fc.identifier 
    from 
      catissue_form_context fc 
      inner join dyextn_containers f on f.identifier = fc.container_id 
    where 
      f.name in ('SpecimenCollectionEvent', 'SpecimenReceivedEvent') and 
      fc.deleted_on is null
  );

Step 3: Commit the changes

commit;

Rerun the query in step 1 to ensure there are no more collection and received events for non-primary specimens.