NHP Data Exchange Export Template Version 0.1Export template that B4 NHP/CDC will use to export NHP data files to NatureServe at the start of the data exchange. Limits the EST & EO export so that only records for the source subnation will be sent. User must change the SUBNATION_NAME to match their own subnation in the EST & EO export SQL.NHP_B4_dx_export_0_4.xpttyler_christensenWed Jul 10 13:05:52 EDT 2002cscottWed May 10 13:15:52 EDT 20062005-12-12tyler_christensenWed Jul 10 13:05:23 EDT 2002cscottTue May 09 16:51:39 EDT 2006exists (select 1 from scientific_name sn, d_name_category d where element_global.gname_id = sn.scientific_name_id and sn.d_name_category_id = d.d_name_category_id and d.name_type_cd in ('A','P') )inactive_ind = 'Y' tyler_christensen Wed Jul 10 15:17:45 EDT 2002 tyler_christensen Mon Sep 13 16:31:21 EDT 2004 exists ( select 1 from scientific_name sn, d_name_category d where element_national.nname_id = sn.scientific_name_id and sn.d_name_category_id = d.d_name_category_id and d.name_type_cd in ( 'A','P')) exists (select 1 from element_global egt where element_national.element_global_id = egt.element_global_id and egt.inactive_ind = 'Y') tyler_christensenWed Jul 10 15:41:59 EDT 2002cscottTue May 09 16:25:07 EDT 2006subnation_id = (select s.subnation_id from subnation s where s.nation_id in (225, 38) and s.subnation_code = 'NV' ) and exists( select 1 from scientific_name sn , d_name_category d where element_subnational.sname_id = sn.scientific_name_id and sn.d_name_category_id = d.d_name_category_id and d.name_type_cd in ('A','P') )exists ( select 1 from element_national ent, element_global egt where element_subnational.element_national_id = ent.element_national_id and egt.element_global_id = ent.element_global_id and egt.inactive_ind = 'Y')tyler_christensenWed Jul 10 16:09:16 EDT 2002cscottTue May 09 15:22:16 EDT 2006exists ( select 1 from element_subnational est, scientific_name sn, d_name_category d where eo.element_subnational_id = est.element_subnational_id and est.subnation_id = ( select s.subnation_id from subnation s where s.nation_id in (225, 38) and s.subnation_code = 'NV' ) and est.sname_id = sn.scientific_name_id and sn.d_name_category_id = d.d_name_category_id and d.name_type_cd in ( 'A','P') )exists ( select 1 from element_subnational est, element_national ent, element_global egt where eo.element_subnational_id = est.element_subnational_id and ent.element_national_id = est.element_national_id and egt.element_global_id = ent.element_global_id and egt.inactive_ind = 'Y') tyler_christensen Thu Jul 11 10:46:28 EDT 2002 tyler_christensen Wed Jan 28 11:34:24 EST 2004 CONSERVATION_SITE_ID > 0 tyler_christensen Thu Jul 11 10:55:51 EDT 2002 tyler_christensen Wed Jan 28 11:34:28 EST 2004 MANAGED_AREA_ID > 0 tyler_christensenMon Jul 15 13:25:09 EDT 2002cscottTue May 09 15:36:59 EDT 2006( REFERENCE_ID in (select reference_id from element_global_ref) or REFERENCE_ID in (select reference_id from element_subnatl_ref) or REFERENCE_ID in (select reference_id from eo_reference) or reference_id in (select concept_reference_id from element_global) or reference_id in (select distribution_reference_id from element_subnational) or reference_id in (select reference_id from managed_area_reference) or reference_id in (select reference_id from site_reference) or reference_id in (select reference_id from scientific_name_ref) or reference_id in (select reference_id from el_subnatl_agency_status) ) tyler_christensen Mon Jul 15 13:36:22 EDT 2002 tyler_christensen Fri Mar 18 13:25:21 EST 2005 ( scientific_name_id in (select gname_id from element_global) or scientific_name_id in (select concept_name_id from element_global) or scientific_name_id in (select nname_id from element_national) or scientific_name_id in (select sname_id from element_subnational) or scientific_name_id in (select scientific_name_id from el_subnatl_agency_status) ) d_name_category_id in (select d_name_category_id from d_name_category where name_type_cd = 'C') cscottTue May 09 17:23:04 EDT 2006cscottWed May 10 13:14:35 EDT 2006exists (select 'x' from eo_source_feature esf where source_feature.shape_id = esf.source_feature_shape_id)exists (select 'x' from element_subnational est, element_national ent, element_global egt, scientific_name sn, d_name_category dnc where source_feature.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 est.sname_id = sn.scientific_name_id and sn.d_name_category_id = dnc.d_name_category_id and (egt.inactive_ind = 'Y' OR dnc.name_type_cd = 'C') )