/** THREATS ASSESSMENT EXPORT--GLOBAL

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

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

******/

SELECT 

    sn.scientific_name 

  , eg.element_global_id   

  , eg.elcode_bcd   

  , dtic.threat_impact_calc_cd overall_impact_calc_cd 

  , dtia.threat_impact_assigned_cd overall_impact_assigned_cd 

  , egr.g_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.g_iucn_threat_comments 

FROM 

    EL_GLOBAL_THREATS_ASSESS ta

  , element_global eg 

  , scientific_name sn 

  , element_global_rank egr

  , 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_global_id = eg.element_global_id 

  and eg.gname_id = sn.scientific_name_id 

  and ta.element_global_id = egr.element_global_id (+) 

  and ta.d_iucn_threat_category_id = dtc.d_iucn_threat_category_id 

  and egr.d_threat_impact_assigned_id = dtia.d_threat_impact_assigned_id (+)

  and egr.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 eg.element_global_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_global_id list and remove the working list section.***/

/*  (134469,159233,158927,129364,146099)  */

ORDER BY

  sn.scientific_name, dtc.display_order