--This view was created by NatureServe for the
--Kentucky State Nature Preserves Commission

CREATE OR REPLACE VIEW HANDY_EO_VW
(EO_ID, SHAPE_ID, EOCODE,
SCIENTIFIC_NAME, COMMON_NAME,
GRANK, SRANK,
USESA, SPROT,
EORANK, IDENT,
LASTOBS, LATITUDE,
LONGITUDE, DIRECTIONS,
S_HAB_PLANT, S_HAB_ANIMAL,
G_HAB_PLANT, G_HAB_ANIMAL)
AS(
SELECT
EO.EO_ID,
EO.SHAPE_ID,
EO.EO_NUM_BCD,
SCIENTIFIC_NAME.SCIENTIFIC_NAME,
ELEMENT_SUBNATIONAL.S_PRIMARY_COMMON_NAME,
ELEMENT_GLOBAL.G_RANK,
ELEMENT_SUBNATIONAL.S_RANK,
D_USESA.DISPLAY_VALUE,
ELEMENT_SUBNATIONAL.S_PROTECTION_STATUS,
D_BASIC_EO_RANK.DISPLAY_VALUE,
D_ID_CONFIRMED.DISPLAY_VALUE,
EO.LAST_OBS_DATE,
EO.LATITUDE,
EO.LONGITUDE,
EO.DIRECTIONS,
PLANT_CAS.S_HABITAT_COM,
ANIMAL_CAS.S_HABITAT_COM,
PLANT_CAG.G_HABITAT_COM,
ANIMAL_CAG.G_HABITAT_COM
FROM
EO, D_BASIC_EO_RANK, D_ID_CONFIRMED, ELEMENT_SUBNATIONAL,
ELEMENT_NATIONAL, ELEMENT_GLOBAL, TAXON_GLOBAL, D_USESA,
TAXON_SUBNATIONAL, SCIENTIFIC_NAME, PLANT_CAS, ANIMAL_CAS,
PLANT_CAG, ANIMAL_CAG
WHERE
EO.D_ID_CONFIRMED_ID = D_ID_CONFIRMED.D_ID_CONFIRMED_ID(+) AND
EO.ELEMENT_SUBNATIONAL_ID = ELEMENT_SUBNATIONAL.ELEMENT_SUBNATIONAL_ID(+) AND
EO.D_BASIC_EO_RANK_ID = D_BASIC_EO_RANK.D_BASIC_EO_RANK_ID(+) AND
ELEMENT_SUBNATIONAL.ELEMENT_NATIONAL_ID = ELEMENT_NATIONAL.ELEMENT_NATIONAL_ID AND
ELEMENT_NATIONAL.ELEMENT_GLOBAL_ID = ELEMENT_GLOBAL.ELEMENT_GLOBAL_ID AND
ELEMENT_GLOBAL.ELEMENT_GLOBAL_ID = TAXON_GLOBAL.ELEMENT_GLOBAL_ID(+) AND
TAXON_GLOBAL.D_USESA_ID = D_USESA.D_USESA_ID(+) AND
EO.ELEMENT_SUBNATIONAL_ID = TAXON_SUBNATIONAL.ELEMENT_SUBNATIONAL_ID(+) AND
ELEMENT_SUBNATIONAL.SNAME_ID = SCIENTIFIC_NAME.SCIENTIFIC_NAME_ID(+) AND
EO.ELEMENT_SUBNATIONAL_ID = PLANT_CAS.ELEMENT_SUBNATIONAL_ID(+) AND
EO.ELEMENT_SUBNATIONAL_ID = ANIMAL_CAS.ELEMENT_SUBNATIONAL_ID(+) AND
TAXON_GLOBAL.ELEMENT_GLOBAL_ID = PLANT_CAG.ELEMENT_GLOBAL_ID(+) AND
TAXON_GLOBAL.ELEMENT_GLOBAL_ID = ANIMAL_CAG.ELEMENT_GLOBAL_ID(+)
);