-- SCRIPT CREATED BY SBW 23/06/2008 to populate the SOURCE field in the
-- TAXON_DESIGNATION table for the JNCC Designation list. This is to ease
-- reporting off it in the report wizard

DELETE FROM REFERENCE_AUTHOR WHERE AUTHOR_KEY = 'JNCCDEV600000001';
DELETE FROM REFERENCE WHERE SOURCE_KEY = 'JNCCDEV600000001';
DELETE FROM SOURCE WHERE SOURCE_KEY = 'JNCCDEV600000001';

INSERT INTO SOURCE (SOURCE_KEY, INTERNAL, CUSTODIAN)
VALUES ('JNCCDEV600000001', '1', 'JNCCDEV6');

INSERT INTO REFERENCE (SOURCE_KEY, YEAR_VAGUE_DATE_START, YEAR_VAGUE_DATE_END, YEAR_VAGUE_DATE_TYPE, FULL_REFERENCE, TITLE, REFERENCE_TYPE, ENTERED_BY, ENTRY_DATE, SYSTEM_SUPPLIED_DATA) VALUES
('JNCCDEV600000001', '39083','39447','Y','JNCC collation of taxon designations','JNCC collation of taxon designations','Other','NBNSYS0000000005', '20080623' ,'1');

INSERT INTO REFERENCE_AUTHOR (AUTHOR_KEY, ITEM_NAME, SOURCE_KEY, SORT_ORDER, ENTERED_BY, ENTRY_DATE, CUSTODIAN, SYSTEM_SUPPLIED_DATA) VALUES
('JNCCDEV600000001', 'Joint Nature Conservation Committee', 'JNCCDEV600000001', '1','NBNSYS0000000005', '20080623', 'JNCCDEV6', '1');

UPDATE TAXON_DESIGNATION SET TAXON_DESIGNATION.SOURCE_KEY = 'JNCCDEV600000001', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '20080623' 
FROM TAXON_DESIGNATION INNER JOIN TAXON_LIST_ITEM
ON TAXON_DESIGNATION.TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY
WHERE TAXON_LIST_ITEM.TAXON_LIST_VERSION_KEY='NHMSYS0020424780';