SELECT
                    eate.auttraesc_cod,
                    ea.aluno_cod,
                    bp_aluno.pes_nome,
                    este.soltpesc_maincod AS num_requisicao,
                    formatar_data(peraut_dt_de) || ' a '|| formatar_data(peraut_dt_ate) AS peraut_cod,
                    formatar_data(eate.auttraesc_dt_revogacao) as data_expiracao,
                    bs_aut.sta_titulo AS status,
                    CASE
                        WHEN eates.auttraescst_status = 1
                            THEN 1
                            ELSE 0
                        END
                    AS autorizada

                FROM edu_sol_tp_escolar este

                INNER JOIN edu_aluno ea
                    ON este.soltpesc_alunocod = ea.aluno_cod

                INNER JOIN bas_pessoa bp_aluno
                    ON ea.aluno_pescod = bp_aluno.pes_cod

                INNER JOIN edu_aut_transp_escolar eate
                    ON este.soltpesc_cod = eate.auttraesc_soltpesccod

                INNER JOIN edu_aut_transp_escolar_status eates
                    ON eate.auttraesc_cod = eates.auttraescst_auttraesccod
                        AND eates.auttraescst_ativo = 1

                INNER JOIN bas_status bs_aut
                    ON eates.auttraescst_status = bs_aut.sta_numero
                        AND bs_aut.sta_tipo = 'STATUS_AUTORIZACAO_TRANSP'

                INNER JOIN edu_periodo_aut epa
                    ON eate.auttraesc_perautcod = epa.peraut_cod
                        AND epa.peraut_cod = 490

                LEFT JOIN edu_aut_transp_escolar eate2
                    ON este.soltpesc_cod = eate2.auttraesc_soltpesccod
                        AND eate2.auttraesc_perautcod = epa.peraut_cod
                            AND eate2.auttraesc_cod <> eate.auttraesc_cod

                LEFT JOIN edu_aut_transp_escolar_status eates2
                    ON eate2.auttraesc_cod = eates2.auttraescst_auttraesccod
                        AND eates2.auttraescst_ativo = 1
                            AND eates2.auttraescst_status = 1

                WHERE 1=1
                    AND este.soltpesc_ano = 2019
                    AND este.soltpesc_escolcod = 19
                    AND eates2.auttraescst_cod IS NULL
               ORDER BY
                    bp_aluno.pes_nome