CREATE OR REPLACE VIEW biotics_user.core_eo_fields
(
    eo_id
  , higher_class
  , eocode
  , sname
  , scomname
  , g_rank
  , s_rank
  , usesa
  , s_prot
  , ident
  , last_obs_date
  , prec
  , eo_rank
  , county
  , quad
  , latitude
  , longitude
  , epa_waterbody
  , directions
  , animal_hab
  , plant_hab
  , datasens
)
AS
select	eo.eo_id,
	substr(eo.eo_num_bcd,1,4) Higher_class,
	eo.eo_num_bcd EOCODE,
	scientific_name.scientific_name SNAME,
	est.s_primary_common_name SCOMNAME,
	egt.G_RANK,
	est.S_RANK,
	d_usesa.USESA_CD USESA,
	est.S_PROTECTION_STATUS S_Prot,
	d_id_confirmed.id_confirmed_cd Ident,
	eo.LAST_OBS_DATE,
	eo.PRECISION_BCD Prec,
	d_basic_eo_rank.BASIC_EO_RANK_CD EO_Rank,
	delimlist('SELECT distinct(d_county.county_name) FROM d_county,eo_county,eo WHERE d_county.D_COUNTY_ID = eo_county.D_COUNTY_ID and eo_county.eo_id ='||eo.eo_id,', ') AS County,
	delimlist('SELECT distinct(d_mapsheet.mapsheet_name) FROM d_mapsheet,eo_mapsheet,eo WHERE d_mapsheet.D_MAPSHEET_ID = eo_MAPSHEET.D_MAPSHEET_ID and eo_mapsheet.eo_id ='||eo.eo_id,', ') AS Quad,
   	eo.LATITUDE,
	eo.LONGITUDE,
	delimlist('SELECT distinct(d_watershed.display_value) FROM d_watershed,eo_watershed,eo WHERE d_watershed.D_watershed_ID = eo_watershed.D_watershed_ID and eo_watershed.eo_id ='||eo.eo_id,', ') AS EPA_Waterbody,
	eo.DIRECTIONS,
	acas.s_habitat_com Animal_Hab,
	pcas.s_habitat_com Plant_Hab,
	eo.data_sensitive_eo_ind Datasens
FROM 	eo,
	scientific_name,
	d_basic_eo_rank,
	d_id_confirmed,
	element_subnational est,
	element_national ent,
	element_global egt,
	taxon_global,
	d_usesa,
	animal_cas acas,
	plant_cas pcas
WHERE	eo.element_subnational_id = est.element_subnational_id AND
	est.element_national_id = ent.element_national_id AND
	ent.element_global_id = egt.element_global_id AND
	ent.element_global_id = taxon_global.element_global_id (+) AND
	taxon_global.D_USESA_ID = d_usesa.D_USESA_ID (+) AND
	EST.SNAME_ID = scientific_name.SCIENTIFIC_NAME_ID AND
	eo.D_BASIC_EO_RANK_ID = d_basic_eo_rank.D_BASIC_EO_RANK_ID (+) AND
	eo.d_id_confirmed_id = d_id_confirmed.d_id_confirmed_id (+) AND
	est.element_subnational_id = acas.element_subnational_id (+) AND
	est.element_subnational_id = pcas.element_subnational_id (+)
;
grant select on core_eo_fields to biotics_report;