select tp.idregistro ,tc.nombre as congreso, tcl.nombre_area as tematica,
regexp_replace(
        regexp_replace(
            regexp_replace(tema, '<[^>]*>', '', 'g'),
            '/\\*.*?\\*/', '', 'g'
        ),
        '\s+', ' ', 'g'
    )  as tema,
case when tpa.id_personal_tmp is null then p.correo_personal_institucional
else tpe.correo
end as correoPonente,
case when tpa.id_personal_tmp is null
then concat(p.apellido1,' ',p.apellido2,' ',p.nombres)
else concat(tpe.apellido1,' ',tpe.apellido2,' ',tpe.nombres)
end as autor,
tce.descripcion ,
case when tp.codigo = 'CCIUTM-REG-ADM'
then 'MAGISTRAL'
else ''
end as tipo
from esq_conv_2020.tbl_ponencias tp
join esq_conv_2020.tbl_ponencias_autores tpa on tpa.id_ponencia = tp.idregistro
join esq_conv_2020.tbl_congreso_linea tcl on tp.id_congreso_linea = tcl.idregistro
join esq_conv_2020.tbl_congreso tc on tc.idregistro = tcl.id_congreso
join esq_conv_2020.tbl_convencion_estado tce on tce.idregistro = tp.id_estado
left join esq_datos_personales.personal p on p.idpersonal = tpa.id_personal_sga
left join esq_conv_2020.tbl_personal_ext tpe on tpe.idregistro = tpa.id_personal_tmp
where tce.clave in('AP','PP','AGG','ACMA','ACME')
order by tc.nombre, tcl.nombre_area