View Source

OpenSpecimen allows to auto-generate labels based on various tokens. For more details, refer to Label configuration.

When a new center starts using OpenSpecimen, the legacy data might be in other sources like Excel, different DB, etc. Once the legacy data is imported, the sequences of new participants and specimens need to start from the last number of legacy data. To achieve this, the administrator has to reset the sequences based on the tokens used. Currently, this is achieved using SQLs. Below are some examples:

Please take a backup of the database before running any SQLs

If there are sequence records for the same key_type and key_value, it needs to be deleted before new entries are created

Level

Token

SQL to reset sequence

Specimen

SYS_UID

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>);

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.

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

Participant (Registration)

SYS_UID

To check if there is a value already present

select * from key_seq_generator
where
  key_type = 'Registration' and
  key_value = 'SYS_UID';

To update the value (if a value is already present):

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

To insert a value (if no value is present) (MySQL):

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

Participant

CP_UID(n)

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>);

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:

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)

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>)
  );

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

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

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

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

PPI_UID

Check for presence of any existing sequences

select
  count(*) 
from 
  key_seq_generator 
where 
  key_type = 'PPI_UID' and 
  key_value in (
    select
      distinct visit.collection_protocol_reg_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 cpr.activity_status = 'Active'
  );

Oracle: Recreate sequences for all visits

insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
select
  key_generator_seq.nextval, 'PPI_UID', t.key_value, t.key_sequence_id
from (
  select 
    visit.COLLECTION_PROTOCOL_REG_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
    cpr.activity_status = 'Active'
  group by
    visit.COLLECTION_PROTOCOL_REG_ID
) t;

MySQL: : Recreate sequences for all visits

insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
select
  'PPI_UID',
  visit.COLLECTION_PROTOCOL_REG_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 cpr.activity_status = 'Active'
group by
  visit.COLLECTION_PROTOCOL_REG_ID;

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

update
  key_seq_generator
set
  key_sequence_id = <max visit number for COLLECTION_PROTOCOL_REG_ID>
where
  key_type = 'PPI_UID' and
  key_value = '<COLLECTION_PROTOCOL_REG_ID>';

Delete all pre-existing sequences

delete from 
  key_seq_generator 
where 
  key_type = 'PPI_UID' and 
  key_value in (
    select
      distinct visit.collection_protocol_reg_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 cpr.activity_status = 'Active'
  );

Visit

PUID

Check for presence of any existing sequences

select
  count(*)
from
  key_seq_generator
where
  key_type = 'VISIT_NAME_PUID' and
  key_value in (
    select
      distinct cpr.participant_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 cpr.activity_status = 'Active'
  );

Oracle: Recreate sequences for all visits

insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
select
  key_generator_seq.nextval, 'VISIT_NAME_PUID', t.key_value, t.key_sequence_id
from (
  select 
    cpr.participant_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 cpr.activity_status = 'Active'
  group by
    cpr.participant_id
) t;

MySQL: : Recreate sequences for all visits

insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
select
  'VISIT_NAME_PUID',
  cpr.participant_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 cpr.activity_status = 'Active'
group by
  cpr.participant_id;

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

update
  key_seq_generator
set
  key_sequence_id = <total number of visits for PARTICIPANT_ID across all CPs>
where
  key_type = 'VISIT_NAME_PUID' and
  key_value = '<PARTICIPANT_ID>';

Delete all pre-existing sequences

delete from 
  key_seq_generator 
where 
  key_type = 'VISIT_NAME_PUID' and 
  key_value in (
    select
      distinct cpr.participant_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 cpr.activity_status = 'Active'
  );

Visit

VISIT_UID

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>)
   );

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>)
         );

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

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>);

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:

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

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
  )

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
  )

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;

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_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;

Specimen

CP_PPI_UID

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

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

Note: If the table key_seq_generator contains the row for ('VISIT_SP_TYPE_UID', <Visit Name> , and <Specimen_Type_ID>) combination then update the key sequence value using the below SQL

MySQL

update 
  key_seq_generator
set (key_value,key_type,key_sequence_id)
  set key_sequence_id = (<max specimen number of specific type under a visit>)
where
  key_value = '<Visit Name>_<Specimen_Type_ID>' and key_type = 'VISIT_SP_TYPE_UID';

Check for presence of any pre-existing sequences

select
  count(*)
from
  key_seq_generator
where
  key_type = 'VISIT_SP_TYPE_UID' and 
  key_value in (
     select
       concat(visit.name,'_',spec.specimen_type_id)
     from
       catissue_specimen_coll_group visit
       inner join catissue_specimen spec
         on visit.identifier=spec.specimen_collection_group_id
     where
       visit.activity_status = 'Active' and
       spec.collection_protocol_id in (<cp_ids>)
     group by
       visit.name, spec.specimen_type_id);

Delete pre-existing sequences

delete from
  key_seq_generator
where
  key_type = 'VISIT_SP_TYPE_UID' and
  key_value in (
     select
       concat(visit.name,'_',spec.specimen_type_id)
     from
       catissue_specimen_coll_group visit
       inner join catissue_specimen spec
         on visit.identifier=spec.specimen_collection_group_id
     where
       visit.activity_status = 'Active' and
       spec.collection_protocol_id in (<cp_ids>)
     group by
       visit.name, spec.specimen_type_id);

Oracle: Recreate sequences for all specimens

insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
select
  key_generator_seq.nextval, 'VISIT_SP_TYPE_UID', t.key_value, t.key_sequence_value
from (
  select 
       concat(visit.name,'_',spec.specimen_type_id) as key_value,
       count(spec.label) as key_sequence_value
from
  catissue_specimen_coll_group visit
  inner join catissue_specimen spec
    on visit.identifier=spec.specimen_collection_group_id
where
  visit.activity_status = 'Active' and
  spec.collection_protocol_id in (<cp_ids>)
group by
  visit.name, spec.specimen_type_id;
) t;

MySQL: Recreate sequences for all specimens

insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
select 'VISIT_SP_TYPE_UID' as key_type,
       concat(visit.name,'_',spec.specimen_type_id) as key_value,
       count(spec.label) as key_sequence_id
from
  catissue_specimen_coll_group visit
  inner join catissue_specimen spec
    on visit.identifier=spec.specimen_collection_group_id
where
  visit.activity_status = 'Active' and
  spec.collection_protocol_id in (<cp_ids>)
group by
  visit.name, spec.specimen_type_id;

Specimen

PPI_SPEC_TYPE_UID

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>) 
   );

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

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));

Resetting existing sequence:

UPDATE key_seq_generator
    SET key_sequence_id = <max number of specimen already created for the PPID + Specimen Type combination>
WHERE key_type = 'PPI_SPEC_TYPE_UID' AND key_value = 'PPID + '_' + specimen_type_id';

note

key value is the combination of PPID and Specimen type ID.
To find the specimen type ID for your specimen type follow the below steps:
1. Navigate to Extras → Dropdown Manager → Specimen Type
2. Go to the specimen type of your choice and check the URL as shown below. Below is the specimen_type_id for 'Whole Blood' is 471

key value is the combination of PPID and Specimen type ID.
To find the specimen type ID for your specimen type follow the below steps:
1. Navigate to Extras → Dropdown Manager → Specimen Type
2. Go to the specimen type of your choice and check the URL as shown below. Below is the specimen_type_id for 'Whole Blood' is 471

Distribution Protocol

DP_UID

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>);

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.

Specimen

PRIMARY_SPMN_SP_TYPE_ABBR_UID

Delete pre-existing sequences

delete from
  key_seq_generator
where
  key_type = 'PRIMARY_SPMN_SP_TYPE_ABBR_UID' and 
  key_value in (
    select
  concat(t.specimen_id, concat('_', t.abbr)) as key_value
from (
  select
    p.identifier as specimen_id,
    pv_props.value as abbr,
    count(*) as specimens
  from
    catissue_specimen p
    inner join catissue_specimen_hierarchy h on h.ancestor_id = p.identifier
    inner join catissue_specimen d on d.identifier = h.descendent_id
    inner join catissue_permissible_value pv on pv.identifier = d.specimen_type_id
    inner join os_pv_props pv_props on pv_props.pv_id = pv.identifier
  where
    p.collection_protocol_id in (<cp_id>) and
    p.lineage = 'New' and
    p.activity_status != 'Disabled' and
    d.activity_status != 'Disabled' and
    pv_props.name = 'abbreviation'
  group by
    p.identifier, pv_props.value
) t
)

MySQL: Recreate sequences

insert into
  key_seq_generator(key_type, key_value, key_sequence_id)  
select
  'PRIMARY_SPMN_SP_TYPE_ABBR_UID' as key_type,
  concat(t.specimen_id, concat('_', t.abbr)) as key_value,
  t.specimens as key_sequence_id
from (
  select
    p.identifier as specimen_id,
    pv_props.value as abbr,
    count(*) as specimens
  from
    catissue_specimen p
    inner join catissue_specimen_hierarchy h on h.ancestor_id = p.identifier
    inner join catissue_specimen d on d.identifier = h.descendent_id
    inner join catissue_permissible_value pv on pv.identifier = d.specimen_type_id
    inner join os_pv_props pv_props on pv_props.pv_id = pv.identifier
  where
    p.collection_protocol_id in (<cp_ids>) and
    p.lineage = 'New' and
    p.activity_status != 'Disabled' and
    d.activity_status != 'Disabled' and
    pv_props.name = 'abbreviation'
  group by
    p.identifier, pv_props.value
) t

Specimen

SPEC_CP_UID

Resetting existing key sequence ID

update
  key_seq_generator
set
  key_sequence_id = <one less than the next expected number>
where
  key_value = '<CP_ID>'
  and key_type = 'SPEC_CP_UID';

Adding the new key sequence ID

insert into 
key_seq_generator (
  key_value, key_sequence_id, key_type
) 
VALUES 
  ('<CP_ID>', <one less than the next expected number>, 'SPEC_CP_UID');