SELECT
hcu_o.higher_class_unit_name as Tax_Order
, hcu_f.higher_class_unit_name AS Family
, sn.scientific_name AS Taxa
, sn.author_name AS Author
, eg.rounded_g_rank AS G_Rank
, d_usesa.usesa_cd AS Listed
, eg.element_global_id
FROM
element_global eg
, scientific_name sn
, taxon_global tg
, higher_class_unit hcu
, higher_class_unit hcu_f
, higher_class_unit hcu_o
, higher_taxon ht
, higher_taxon ht_f
, higher_taxon ht_o
, d_usesa
, working_list_element_data wled
WHERE
wled.working_list_id = 102530 /* MODIFY WORKING_LIST_ID HERE */
and wled.data_id = eg.element_global_id
and sn.scientific_name_id = eg.gname_id
and eg.element_global_id = tg.element_global_id
and tg.d_usesa_id = d_usesa.d_usesa_id (+)
/* LINKS HIGHER_CLASS_UNIT RECORDS THROUGH THE ORDER LEVEL. */
and eg.higher_class_unit_id = hcu.higher_class_unit_id
and hcu.parent_unit_id = hcu_f.higher_class_unit_id
and hcu_f.parent_unit_id = hcu_o.higher_class_unit_id
/* LINKS THE HIGHER_TAXON RECORDS TO THE HIGHER_CLASS_UNIT RECORDS
SO YOU CAN SORT THE RECORDS TAXONOMICALLY. */
and hcu.higher_class_unit_id = ht.higher_class_unit_id
and hcu.parent_unit_id = ht_f.higher_class_unit_id
and hcu_f.parent_unit_id = ht_o.higher_class_unit_id
ORDER BY
ht_o.higher_taxon_sequence_num
, ht_f.higher_taxon_sequence_num
, sn.scientific_name