/* script to create an addition list type for defunct checklist type and then update
the lists that fall into this catageory (namely:Freshwater checklist, NBN Gateway Vascular Plant Dataset, Passmore-Edwards Museum number). The reason for these changes is to avoid these lists being used for data entry.

The script also updates some other status lists as such again to avoid them being used for data entry.*/

INSERT INTO taxon_list_type (TAXON_LIST_TYPE_KEY, SHORT_NAME, LONG_NAME, DESCRIPTION, SCHEDULE, ENTERED_BY, ENTRY_DATE, SYSTEM_SUPPLIED_DATA, CUSTODIAN, Allow_Data_Entry)
VALUES ('NBNSYS0000000006', 'Defunct checklist', 'Defunct checklist', 'A checklist that exists in the dictionary historically but has no current genuine use', 0, 'NBNSYS0000000005', convert(datetime,'29/11/2006',103), 1, 'NBNSYS00', 0);

-- passmore edwards
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000006', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006' 
WHERE TAXON_LIST_KEY = 'NBNSYS0000000033';

-- guidelines for SSSI selection
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000003', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006'  WHERE TAXON_LIST_KEY = 'NBNSYS0000000090';

-- pink list
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000003', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006'  WHERE TAXON_LIST_KEY = 'NBNSYS0000000091';

-- JNCC interprtation of higher level designations
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000003', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006'  WHERE TAXON_LIST_KEY = 'NBNSYS0000000125';

-- species of conservation concern
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000003', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006'  WHERE TAXON_LIST_KEY = 'NBNSYS0200000001';

-- long list of globally threatened species
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000002', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006'  WHERE TAXON_LIST_KEY = 'NBNSYS0000000104';

-- freshwater checklist
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000006', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006'  WHERE TAXON_LIST_KEY = 'NBNSYS0000000034';

-- gateway vascular plant list
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000006', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006'  WHERE TAXON_LIST_KEY = 'NHMSYS0100000001';

-- added 4 December 2006

--Red Data Book of European Bryophytes
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000002', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006'  WHERE TAXON_LIST_KEY = 'NBNSYS0000000098';

-- Red Data Book Vascular Plants: Ireland
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000002', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006'  WHERE TAXON_LIST_KEY = 'EHSSYS0000000002';

--Red Data Book Vertebrates: Ireland
UPDATE TAXON_LIST SET TAXON_LIST_TYPE_KEY = 'NBNSYS0000000002', CHANGED_BY = 'NBNSYS0000000005', CHANGED_DATE = '1/12/2006'  WHERE TAXON_LIST_KEY = 'EHSSYS0000000001';