/** THREATS ASSESSMENT EXPORT--SUBNATIONAL

This query returns subnational Threats Assessment data for import into Threats Data Compiled.

REMEMBER TO CHANGE WORKING LIST (or ELEMENT_SUBNATIONAL_IDs) AT END OF QUERY

******/

SELECT 

    sn.scientific_name gname

  , getsciname(es.sname_id) sname

  , es.element_subnational_id   

  , eg.elcode_bcd   

  , dtic.threat_impact_calc_cd overall_impact_calc_cd 

  , dtia.threat_impact_assigned_cd overall_impact_assigned_cd 

  , esr.s_impact_adjustment_reasons  

  , dtc.iucn_threat_category_cd 

  , dtc.iucn_threat_category_desc    

  , dtic2. threat_impact_calc_cd 

  , dtic2. threat_impact_calc_desc 

  , dtsc.display_value scope_cd 

  , dtse.display_value severity_cd 

  , dtt.display_value  timing_cd 

  , ta.s_iucn_threat_comments 

FROM 

    EL_SUBNATL_THREATS_ASSESS ta

  , element_global eg 

  , element_national en

  , element_subnational es

  , scientific_name sn 

  , element_subnatl_rank esr

  , d_iucn_threat_category dtc 

  , d_threat_impact_assigned dtia

  , d_threat_impact_calc dtic

  , d_threat_impact_calc dtic2

  , d_iucn_threat_scope dtsc 

  , d_iucn_threat_severity dtse 

  , d_iucn_threat_timing dtt 

WHERE

  ta.element_subnational_id = es.element_subnational_id

  and eg.element_global_id = en.element_global_id

  and en.element_national_id = es.element_national_id

  and eg.gname_id = sn.scientific_name_id 

  and ta.element_subnational_id = esr.element_subnational_id (+) 

  and ta.d_iucn_threat_category_id = dtc.d_iucn_threat_category_id 

  and esr.d_threat_impact_assigned_id = dtia.d_threat_impact_assigned_id (+)

  and esr.d_threat_impact_calc_id = dtic.d_threat_impact_calc_id (+)

  and ta.d_threat_impact_calc_id = dtic2.d_threat_impact_calc_id (+)

  and ta.d_iucn_threat_scope_id = dtsc.d_iucn_threat_scope_id (+)

  and ta.d_iucn_threat_severity_id = dtse.d_iucn_threat_severity_id (+)

  and ta.d_iucn_threat_timing_id = dtt.d_iucn_threat_timing_id (+)

  and dtc.IUCN_THREAT = 'Y'   /* Limit to IUCN threats */ 

  and eg.inactive_ind = 'N' /* EXCLUDE INACTIVES */

  and es.element_subnational_id in

  /***CHANGE WORKING LIST NAME***/

  (select wled.data_id 

  from working_list_element_data wled, working_list wl

   where wled.working_list_id = wl.working_list_id

   and wl.working_list_name = 'ENTER WORKING LIST NAME HERE') 

/***

TO USE IDs INSTEAD OF WORKING LIST (for advanced users):

Update and uncomment this element_subnational_id list and remove the working list section.***/

/* (19637)  */

ORDER BY

  sn.scientific_name, dtc.display_order