/* script to tidy up the mess created by inserting keys for national parks into R6 when they already existed in R2002*/

-- need to move 12107 and 12108 to new keys at the end of the sequence in R2002

INSERT INTO ADMIN_AREA (ADMIN_AREA_KEY, ITEM_NAME, ADMIN_TYPE_KEY, ENTERED_BY, 
ENTRY_DATE, SYSTEM_SUPPLIED_DATA, CUSTODIAN)
VALUES ('NBNSYS0000013004', 'The New Forest','NBNSYS0000000022', 
'NBNSYS0000000005','1/12/2006', -1,'NBNSYS00')

INSERT INTO ADMIN_AREA (ADMIN_AREA_KEY, ITEM_NAME, ADMIN_TYPE_KEY, ENTERED_BY, 
ENTRY_DATE, SYSTEM_SUPPLIED_DATA, CUSTODIAN)
VALUES ('NBNSYS0000013005', 'South Downs','NBNSYS0000000022', 
'NBNSYS0000000005','1/12/2006', -1,'NBNSYS00')

UPDATE LOCATION_ADMIN_AREAS SET ADMIN_AREA_KEY = 'NBNSYS0000013004' WHERE ADMIN_AREA_KEY 
= 'NBNSYS0000012107'

UPDATE LOCATION_ADMIN_AREAS SET ADMIN_AREA_KEY = 'NBNSYS0000013005' WHERE ADMIN_AREA_KEY 
= 'NBNSYS0000012108'

UPDATE BIOTOPE_RELATION_ADMIN_AREA SET ADMIN_AREA_KEY = 'NBNSYS0000013004' WHERE 
ADMIN_AREA_KEY = 'NBNSYS0000012107'

UPDATE BIOTOPE_RELATION_ADMIN_AREA SET ADMIN_AREA_KEY = 'NBNSYS0000013005' WHERE ADMIN_AREA_KEY = 'NBNSYS0000012108'

DELETE FROM ADMIN_AREA WHERE ADMIN_AREA_KEY = 'NBNSYS0000012107';

DELETE FROM ADMIN_AREA WHERE ADMIN_AREA_KEY = 'NBNSYS0000012108';

-- update what was 12106
INSERT INTO ADMIN_AREA (ADMIN_AREA_KEY, ITEM_NAME, ADMIN_TYPE_KEY, ENTERED_BY, 
ENTRY_DATE, SYSTEM_SUPPLIED_DATA, CUSTODIAN)
VALUES ('NBNSYS0000012107', 'Loch Lomond and The Trossachs National 
Park','NBNSYS0000000022', 'NBNSYS0000000005','1/12/2006', -1,'NBNSYS00')

UPDATE LOCATION_ADMIN_AREAS SET ADMIN_AREA_KEY = 'NBNSYS0000012107' WHERE ADMIN_AREA_KEY 
= 'NBNSYS0000012106';

UPDATE BIOTOPE_RELATION_ADMIN_AREA SET ADMIN_AREA_KEY = 'NBNSYS0000012107' WHERE 
ADMIN_AREA_KEY = 'NBNSYS0000012106'

DELETE FROM ADMIN_AREA WHERE ADMIN_AREA_KEY = 'NBNSYS0000012106';

-- update what was 12105
INSERT INTO ADMIN_AREA (ADMIN_AREA_KEY, ITEM_NAME, ADMIN_TYPE_KEY, ENTERED_BY, 
ENTRY_DATE, SYSTEM_SUPPLIED_DATA, CUSTODIAN)
VALUES('NBNSYS0000012106','Cairngorms National Park','NBNSYS0000000022', 
'NBNSYS0000000005','1/12/2006',-1,'NBNSYS00')

UPDATE LOCATION_ADMIN_AREAS SET ADMIN_AREA_KEY = 'NBNSYS0000012106' WHERE ADMIN_AREA_KEY 
= 'NBNSYS0000012105';

UPDATE BIOTOPE_RELATION_ADMIN_AREA SET ADMIN_AREA_KEY = 'NBNSYS0000012106' WHERE 
ADMIN_AREA_KEY = 'NBNSYS0000012105'

DELETE FROM ADMIN_AREA WHERE ADMIN_AREA_KEY = 'NBNSYS0000012105';