select p.nm_pess as nome,
       v.nm_cpf_pess as cpf,      
       p.nm_sexo as sexo,
       to_char (p.dt_nasc,'DD/MM/YYYY') as nascimento,
       ci.nm_cidd as cidade_endereco,
       p.nm_bair as bairro,
       ca.nm_campus as campus,
       pf.nm_progr_form as curso,
       v.nu_ano_admis || '.' || v.nu_semtr_admis as ingresso,              
       ti.ds_tp_ingrs as tipo_ingresso,
       pe.cd_perf as perfil,
       t.nm_turno as turno,
       sa.cd_perd_letv as periodo_letivo,
       tsa.ds_tp_sit_acad as situacao_academica,
       case
         when exists (select 8
                        from siga_dado_hist dh1
                       where dh1.nu_matr_curso=v.nu_matr_curso
                         and dh1.cd_sit_hist IN (1,7,8,9)
                     ) then
                        (SELECT trunc(SUM(cc.nu_carga_horr_tot) * 100 /  pe.nu_carga_horr_tot, 2)
                          FROM siga_dado_hist dh, 
                               siga_atividade_academica aa, 
                               siga_componente_curricular cc   
                         WHERE dh.nu_matr_curso = v.nu_matr_curso
                           AND dh.cd_ativ_acad = aa.cd_ativ_acad
                           AND aa.cd_compnt_curr = cc.cd_compnt_curr
                           AND dh.cd_sit_hist IN (1,7,8,9)                         
                           and aa.cd_perd_letv <=sa.cd_perd_letv
                         ) 
                      else trunc(0,2)
       end AS integralidade,              
       case 
          when pf.cd_nivel=1 and 
               exists (select 8
                  from siga_dado_hist dh1
                 where dh1.nu_matr_curso=v.nu_matr_curso
                   and dh1.cd_sit_hist IN (1,3,4,5,7,8,9)
                   and dh1.nu_media_fim > 0
               )  then               
                (SELECT trunc(SUM(dh.nu_media_fim) / count(8), 2)
                   FROM siga_dado_hist dh, 
                        siga_atividade_academica aa, 
                        siga_componente_curricular cc   
                  WHERE dh.nu_matr_curso = v.nu_matr_curso
                    AND dh.cd_ativ_acad = aa.cd_ativ_acad
                    AND aa.cd_compnt_curr = cc.cd_compnt_curr           
                    AND dh.cd_sit_hist IN (1,3,4,5,7,8,9)
                    and aa.cd_perd_letv<=sa.cd_perd_letv
                 )
              else trunc(0,2)
       end AS media,         
       case  
         when p.nm_email_secd is null then 'email_nao_informado' 
         else p.nm_email_secd
       end as email
 from siga_vinculo v,
      siga_pessoa p,
      siga_programa_formacao pf,
      siga_turno_vinculo tv,      
      siga_turno t,
      siga_vinculo_perfil vp,
      siga_campus ca,
      siga_cidade ci,
      siga_perfil pe,
      siga_tipo_ingresso ti,
      siga_situacao_academica sa,
       siga_tipo_situacao_academica tsa 
 where v.nm_cpf_pess=p.nm_cpf_pess
   and v.cd_progr_form=pf.cd_progr_form
   and v.nu_matr_curso=tv.nu_matr_curso
   and v.nu_matr_curso=vp.nu_matr_curso
   and v.cd_tp_ingrs=ti.cd_tp_ingrs
   and vp.cd_perf=pe.cd_perf
   and vp.tp_perf=pe.tp_perf
   and v.nu_matr_curso=sa.nu_matr_curso
   and sa.cd_tp_sit_acad=tsa.cd_tp_sit_acad
   and v.fl_vinc='1'
   and pf.cd_nivel in (1) -- Graduacao
   and tv.cd_turno=t.cd_turno
   and tv.cd_perd_letv=(select max(tv1.cd_perd_letv)
                          from siga_turno_vinculo tv1
                         where tv1.nu_matr_curso=tv.nu_matr_curso
                            and tv1.cd_perd_letv<=sa.cd_perd_letv)
   and vp.cd_perd_letv=(select max(vp1.cd_perd_letv)
                          from siga_vinculo_perfil vp1
                         where vp1.nu_matr_curso=vp.nu_matr_curso
                           and vp1.cd_perd_letv<=sa.cd_perd_letv)     
   and sa.dt_def_sit=(select max(sa2.dt_def_sit)
                        from siga_situacao_academica sa2
                       where sa2.nu_matr_curso=sa.nu_matr_curso
                         and sa2.cd_perd_letv=sa.cd_perd_letv)                           
  and pf.cd_progr_form not in (14,15,85)    
  and pf.cd_campus=ca.cd_campus
  and p.cd_cidd_end=ci.cd_cidd
  and v.nu_ano_admis>2005
  and v.nm_cpf_pess IN ('03732225488','02312302497')
order by ca.nm_campus, 
         pf.nm_progr_form,
         p.nm_pess,
         sa.cd_perd_letv