Page Comparison

...

Level Token SQL to reset sequence

Specimen

SYS_UID

Code Block

language	sql
title	Oracle

insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
values
  (key_generator_seq.nextval, 'Specimen', 'SYS_UID', <max specimen number like 1000>);

Code Block

language	sql
title	MySQL

insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
values
  ('Specimen', 'SYS_UID', <max specimen number like 1000>);

Note: If the table key_seq_generator already has a row for the combination ('Specimen', 'SYS_UID') then update the row with the new sequence value using below SQL.

Code Block

language	sql

update
  key_seq_generator
set
  key_sequence_id = <max specimen number like 1000>
where
  key_type = 'Specimen' and
  key_value = 'SYS_UID';

Participant

CP_UID(n)

Code Block

language	sql
title	Oracle

insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
values
  (key_generator_seq.nextval, 'PPID', <CP ID>, <max PPID number like 1000>);

Code Block

language	sql
title	MySQL

insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
values
  ('PPID', <CP ID>, <max PPID number like 1000>);

Note: If the table key_seq_generator already contains the row for the combination ('PPID', <CP ID>) then update that row with the new key sequence value using below SQL:

Code Block

language	sql

update
  key_seq_generator
set
  key_sequence_id = <max PPID number like 1000>
where
  key_type = 'PPID' and
  key_value = '<CP_ID>';

Visit

EVENT_UID(n)

Code Block

language	sql
title	Check for presence of any existing sequences

select
  count(*) 
from 
  key_seq_generator 
where 
  key_type = 'EVENT_UID' and 
  key_value in (
    select
      concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id))
    from
      catissue_coll_prot_reg cpr
      inner join catissue_specimen_coll_group visit
        on visit.collection_protocol_reg_id = cpr.identifier
    where
      cpr.collection_protocol_id in (<cp_id_list>)
  );

Code Block

language	sql
title	Oracle: Recreate sequences for all visits

insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
select
  key_generator_seq.nextval, 'EVENT_UID', t.key_value, t.key_sequence_id
from (
  select 
    concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id)) as key_value,
    count(visit.identifier) as key_sequence_id
  from
    catissue_coll_prot_reg cpr
    inner join catissue_specimen_coll_group visit
      on visit.collection_protocol_reg_id = cpr.identifier
  where
    cpr.collection_protocol_id in (<cp_id_list>) and
    visit.collection_protocol_event_id is not null
  group by
    concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id))
) t

Code Block

language	sql
title	MySQL: : Recreate sequences for all visits

insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
select
  'EVENT_UID',
  concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id)),
  count(visit.identifier)
from
  catissue_coll_prot_reg cpr
  inner join catissue_specimen_coll_group visit
    on visit.collection_protocol_reg_id = cpr.identifier
where
  cpr.collection_protocol_id in (<cp_id_list>)
group by
  concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id));

Note: If the table key_seq_generator contains the row for ('EVENT_ID', <PPID>_<EVENT_ID>) combination then update the key sequence value using below SQL

Code Block

language	sql

update
  key_seq_generator
set
  key_sequence_id = <max visit number like 1000>
where
  key_type = 'EVENT_UID' and
  key_value = '<PPID>_<EVENT_ID>';

Code Block

language	sql
title	Delete all pre-existing sequences

delete from 
  key_seq_generator 
where 
  key_type = 'EVENT_UID' and 
  key_value in (
    select
      concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id))
    from
      catissue_coll_prot_reg cpr
	  inner join catissue_specimen_coll_group visit
	    on visit.collection_protocol_reg_id = cpr.identifier
    where
      cpr.collection_protocol_id in (<cp_id_list>)
  );

Visit

VISIT_UID

Code Block

language	sql
title	Check for existing sequence

select
   count(*)
from
   key_seq_generator 
where
   key_type = 'VISIT_UID' 
   and key_value in 
   (
      select
         visit.identifier
      from
         catissue_coll_prot_reg cpr 
         inner join
            catissue_specimen_coll_group visit 
            on visit.collection_protocol_reg_id = cpr.identifier 
         inner join
            catissue_specimen spec 
            on visit.identifier = spec.specimen_collection_group_id 
      where
         cpr.collection_protocol_id in (<cp_id_list>)
   );

Code Block

language	sql
title	Delete the existing sequence

delete
      from
         key_seq_generator 
      where
         key_type = 'VISIT_UID' 
         and key_value in 
         (
            select
               visit.identifier
            from
               catissue_coll_prot_reg cpr 
               inner join
                  catissue_specimen_coll_group visit 
                  on visit.collection_protocol_reg_id = cpr.identifier 
               inner join
                  catissue_specimen spec 
                  on visit.identifier = spec.specimen_collection_group_id 
            where
               cpr.collection_protocol_id in (<cp_id_list>)
         );

Code Block

language	sql
title	Insert new sequence

insert into
   key_seq_generator (key_type, key_value, key_sequence_id) 
   select
      'VISIT_UID',
      visit.identifier,
      count(visit.identifier) 
   from
      catissue_coll_prot_reg cpr 
      inner join
         catissue_specimen_coll_group visit 
         on visit.collection_protocol_reg_id = cpr.identifier 
      inner join
         catissue_specimen spec 
         on visit.identifier = spec.specimen_collection_group_id 
   where
      cpr.collection_protocol_id in (<cp_id_list>)
      and spec.lineage = 'New' 
   group by
      visit.identifier;

Visit

SYS_UID

Code Block

language	sql
title	Oracle

insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
values
  (key_generator_seq.nextval, 'Visit', 'SYS_UID', <max visit number like 1000>);

Code Block

language	sql
title	MySQL

insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
values
  ('Visit', 'SYS_UID', <max visit number like 1000>);

Note: If the table key_seq_generator contains a row for the combination ('Visit', 'SYS_UID') then update the key sequence value using below SQL:

Code Block

language	sql

update
  key_seq_generator
set
  key_sequence_id = <max visit number like 1000>
where
  key_type = 'Visit' and
  key_value = 'SYS_UID';

Specimen

PSPEC_UID

Code Block

language	sql
title	Check for presence of any pre-existing sequences

select
  count(*)
from
  key_seq_generator
where
  key_type = 'PSPEC_UID' and 
  key_value in (
    select
      parent.identifier
    from
      catissue_specimen parent
    where
      parent.collection_protocol_id in (<cp_id_list>) and
      parent.collection_status='Collected' and
      parent.activity_status='Active' and
      parent.label is not null
  )

Code Block

language	sql
title	Delete pre-existing sequences

delete from
  key_seq_generator
where
  key_type = 'PSPEC_UID' and 
  key_value in (
    select
      parent.identifier
    from
      catissue_specimen parent
    where
      parent.collection_protocol_id in (<cp_id_list>) and
      parent.collection_status='Collected' and
      parent.activity_status='Active' and
      parent.label is not null
  )

Code Block

language	sql
title	Oracle: Recreate sequences for all parent specimens

insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
select
  key_generator_seq.nextval, 'PSPEC_UID', t.key_value, t.key_sequence_value
from (
  select
    parent.identifier as key_value, count(child.identifier) as key_sequence_value 
  from 
    catissue_specimen parent 
    inner join catissue_specimen child 
      on parent.identifier=child.parent_specimen_id
  where
    parent.collection_protocol_id in (<cp_id_list>) and
    parent.collection_status='Collected' and
    parent.activity_status='Active' and
    child.activity_status='Active' and 
    parent.label is not null 
  group by 
    parent.identifier
) t;

Code Block

language	sql
title	MySQL: Recreate sequences for all parent specimens

insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
select
  'PSPEC_UID', t.key_value, t.key_sequence_value
from (
  select
    parent.identifier as key_value, count(child.identifier) as key_sequence_value 
  from 
    catissue_specimen parent 
    inner join catissue_specimen child 
      on parent.identifier=child.parent_specimen_id
  where
    parent.collection_status='Collected' and
    parent.activity_status='Active' and
    child.activity_status='Active' and 
    parent.label is not null 
  group by 
    parent.identifier
) t;

Specimen

CP_PPI_UID

Code Block

language	sql
title	Oracle

insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
select
  key_generator_seq.nextval, 'CP_PPI_UID', t.key_value, t.key_sequence_value
from (
  select
    concat(cpr.collection_protocol_id, concat('_', cpr.protocol_participant_id)) as key_value,
    count(specimen.identifier) as key_sequence_value
  from
    catissue_coll_prot_reg cpr
    inner join catissue_specimen_coll_group visit
      on visit.collection_protocol_reg_id = cpr.identifier 
    inner join catissue_specimen specimen 
      on specimen.specimen_collection_group_id = visit.identifier
  where
    cpr.collection_protocol_id in (<CP IDs>) and
    specimen.lineage = 'New'
  group by 
    concat(cpr.collection_protocol_id, concat('_', cpr.protocol_participant_id))
) t

Code Block

language	sql
title	MySQL

insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
select
  'CP_PPI_UID', t.key_value, t.key_sequence_value
from (
  select
    concat(cpr.collection_protocol_id, concat('_', cpr.protocol_participant_id)) as key_value,
    count(specimen.identifier) as key_sequence_value
  from
    catissue_coll_prot_reg cpr
    inner join catissue_specimen_coll_group visit
      on visit.collection_protocol_reg_id = cpr.identifier 
    inner join catissue_specimen specimen 
      on specimen.specimen_collection_group_id = visit.identifier
  where
    cpr.collection_protocol_id in (<CP IDs>) and
    specimen.lineage = 'New'
  group by 
    concat(cpr.collection_protocol_id, concat('_', cpr.protocol_participant_id))
) t

Specimen

VISIT_SP_TYPE_UID

Code Block

language	sql
title	MySQL

Insert into key_seq_generator
(key_value,key_type,
key_sequence_id) values ('<Visit Name>_<Specimen_type>','VISIT_SP_TYPE_UID', <max specimen number like 1000>);

Code Block

language	sql
title	Oracle

Insert into key_seq_generator
(identifier,key_value,key_type,
key_sequence_id) values (key_generator_seq.nextval,'<Visit Name>_<Specimen_type>','VISIT_SP_TYPE_UID', <max specimen number like 1000>);

Specimen

PPI_SPEC_TYPE_UID

Code Block

language	sql
title	Check for presence of any pre-existing sequences

select
   count(*)
from
   key_seq_generator 
where
   key_type = 'PPI_SPEC_TYPE_UID' 
   and key_value in 
   (
      select
         concat(cpr.protocol_participant_id, concat('_', spec.specimen_type_id)) 
      from
         catissue_coll_prot_reg cpr 
         inner join
            catissue_specimen_coll_group visit 
            on visit.collection_protocol_reg_id = cpr.identifier 
         inner join
            catissue_specimen spec 
            on visit.identifier = spec.specimen_collection_group_id 
      where
         cpr.collection_protocol_id in (<cp_id_list>) 
   );

Code Block

language	sql
title	Delete pre-existing sequences

delete
from
   key_seq_generator 
where
   key_type = 'PPI_SPEC_TYPE_UID' 
   and key_value in 
   (
      select
         concat(cpr.protocol_participant_id, concat('_', spec.specimen_type_id)) 
      from
         catissue_coll_prot_reg cpr 
         inner join
            catissue_specimen_coll_group visit 
            on visit.collection_protocol_reg_id = cpr.identifier 
         inner join
            catissue_specimen spec 
            on visit.identifier = spec.specimen_collection_group_id 
      where
         cpr.collection_protocol_id in (<cp_id_list>) 
   );

Note: Below SQLs are being executed for the label format set at the primary specimen level. If the token is used at derived or aliquot level change the spec.lineage accordingly

Code Block

language	sql
title	MySQL: : Recreate sequences for all primary specimens

insert into
   key_seq_generator (key_type, key_value, key_sequence_id) 
   select
      'PPI_SPEC_TYPE_UID',
      concat(cpr.protocol_participant_id, concat('_', spec.specimen_type_id)),
      count(cpr.protocol_participant_id) 
   from
      catissue_coll_prot_reg cpr 
      inner join
         catissue_specimen_coll_group visit 
         on visit.collection_protocol_reg_id = cpr.identifier 
      inner join
         catissue_specimen spec 
         on visit.identifier = spec.specimen_collection_group_id 
   where
      cpr.collection_protocol_id in (<cp_id_list>)
      and spec.lineage = 'New' 
   group by
      concat(cpr.protocol_participant_id, concat('_', spec.specimen_type_id));

Distribution Protocol

DP_UID

Code Block

language	sql
title	Oracle

insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
values
  (key_generator_seq.nextval, 'DP_UID', <DP ID>, <max distributed specimen count>);

Code Block

language	sql
title	MySQL

insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
values
  ('DP_UID', <DP ID>, <max distributed specimen count>);

Note : <DP ID> value will be system identifier of that distribution protocol which can be found in the URL after clicking on DP.

...

Versions Compared

Old Version 23

New Version 24

Key