DESCRIPTIONKB 119 instructs Kestrel users to submit an issue to Product Support if they receive an automated email notifying them that one or more observations are linked to deleted Biotics ENT records. The notification email will list the "name" of a deleted record as "UNKNOWN SPECIES: ELEMENT_NATIONAL.Y.XXXXXX", where Y = the element_national_ou_uid and XXXXXX = the element_national_seq_uid. We at NatureServe will need to query the deleted schema of Central Biotics to find out the name of the element and tell the Kestrel user. |
CAUSE
Occasionally elements are deleted in Biotics because a duplicate has been entered, either with exactly the same name or with a slightly different name. There's no taxonomic difference between the two, so one is deleted instead of inactivated. |
RESOLUTION
Example: The deleted species is UNKNOWN SPECIES: ELEMENT_NATIONAL.2.792094
Log into the deleted schema in Toad or TOra. Schema name is hdmsdel, password is same as production Biotics regular schema.
Run the following query:
select entdel.element_national_id, sn.scientific_name, entdel.n_primary_common_name, entdel.element_national_seq_uid, entdel.element_national_ou_uid
from element_national_del entdel, hdms.scientific_name sn
where entdel.nname_id = sn.scientific_name_id
and entdel.element_national_seq_uid = 792094 and entdel.element_national_ou_uid = 2
Example Results:
792094 Alces americanus Moose 792094 2
(usually, but not always, the element_national_id is the same as the element_national_seq_uid)
First, search for a Canadian ENT with exactly the same name in Tracker. If you find one, tell the Kestrel user the original scientific and common names and that there is a replacement record with the same scientific (and common, if applicable) name. He/she needs to edit the Observation record and search for that name (e.g., Alces americanus), select it, and save the Observation.
If you can't find the replacement element with the same name or you aren't sure about it, email a central zoologist or botanist to ask them why the original record was deleted and what took its place. |
For subnational instances of Kestrel, such as Saskatchewan, run the following query on the subnational instance of Biotics (i.e. bioticsSK):
select estdel.element_subnational_id, sn.scientific_name, estdel.s_primary_common_name, estdel.element_subnational_seq_uid, estdel.element_subnational_ou_uid from biotics_del.element_subnational_del estdel, biotics_user.scientific_name sn where estdel.sname_id = sn.scientific_name_id and estdel.element_subnational_seq_uid in (315847,320544,332884,335451,425565,440459,450502,455598,458998,505595,517805,532759,539253,543618,562736,578293,616960,634659,638384,735717) and estdel.element_subnational_ou_uid = 2
If a record cannot be found in the deleted schema, it was undoubtedly reparented and can be found running the following query(s):
SELECT es.element_subnational_id,
est_elcode(es.element_subnational_id) elcode,
getsciname(sname_id) sname,
al.audit_date,
al.table_name,
alc.column_name,
alc.old_value,
alc.comments,
al.action_code,
al.user_name,
al.org_unit_id
FROM
AUDIT_LOG al,
AUDIT_LOG_COLUMN alc,
ELEMENT_SUBNATIONAL es
WHERE
al.audit_log_id = alc.audit_log_id(+)
and al.primary_key_id = es.element_subnational_id(+)
and al.table_name = 'ELEMENT_SUBNATIONAL'
and alc.old_value = '12617'
and column_name='ELEMENT_SUBNATIONAL_SEQ_UID';
That doesn't check what the ou_uid was. To make sure that was updated for the same record, you could then run this, using the EST_ID found in the previous query:
SELECT es.element_subnational_id,
est_elcode(es.element_subnational_id) elcode,
getsciname(sname_id) sname,
al.audit_date,
al.table_name,
alc.column_name,
alc.old_value,
alc.comments,
al.action_code,
al.user_name,
al.org_unit_id
FROM
AUDIT_LOG al,
AUDIT_LOG_COLUMN alc,
ELEMENT_SUBNATIONAL es
WHERE
al.audit_log_id = alc.audit_log_id(+)
and al.primary_key_id = es.element_subnational_id(+)
and al.table_name = 'ELEMENT_SUBNATIONAL'
and column_name='ELEMENT_SUBNATIONAL_OU_UID'
and al.primary_key_id=12617;
