select v.nm_cpf_pess as cpf,
       p.nm_pess as nome,            
       p.nm_sexo as sexo,
       (case
         when p.nm_est_civil='1' then 'SOLTEIRO(A)'
         when p.nm_est_civil='2' then	'CASADO(A)'       
         when p.nm_est_civil='3' then	'SEPARADO JUDICIALMENTE'
         when p.nm_est_civil='4' then	'DIVORCIADO'
         when p.nm_est_civil='5' then 'VIUVO(A)'
         when p.nm_est_civil='6' then 'NAO INFORMADO'
         else 'NAO INFORMADO'
       end) as estado_civil,
       to_char (p.dt_nasc,'DD/MM/YYYY') as nascimento,
       ci.nm_cidd as cidade_endereco,
       case  
         when p.nm_email_secd is null then 'email_nao_informado' 
         else p.nm_email_secd
       end as email,
       (select '(' || c1.cd_ddd || ')' || t.nm_telf
          from siga_telefone_pessoa tp,
               siga_telefone t,
               siga_cidade c1
         where tp.nm_cpf_pess=v.nm_cpf_pess
           and tp.cd_telf=t.cd_telf
           and t.cd_cidd=c1.cd_cidd
           and tp.tp_telf='CELULAR'
           and tp.ts_telf_pess=(select max(tp1.ts_telf_pess)
                                  from siga_telefone_pessoa tp1
                                 where tp1.nm_cpf_pess=tp.nm_cpf_pess
                                   and tp1.tp_telf=tp.tp_telf
                                   and rownum=1
                               )
           and rownum=1                               
       ) as fone_celular,
       (select '(' || c1.cd_ddd || ')' || t.nm_telf
          from siga_telefone_pessoa tp,
               siga_telefone t,
               siga_cidade c1
         where tp.nm_cpf_pess=v.nm_cpf_pess
           and tp.cd_telf=t.cd_telf
           and t.cd_cidd=c1.cd_cidd
           and tp.tp_telf='RESIDENCIAL'
           and tp.ts_telf_pess=(select max(tp1.ts_telf_pess)
                                  from siga_telefone_pessoa tp1
                                 where tp1.nm_cpf_pess=tp.nm_cpf_pess
                                   and tp1.tp_telf=tp.tp_telf
                                   and rownum=1
                               )
           and rownum=1                               
       ) as fone_residencial,       
     --p.nm_bair as bairro,
       pf.nm_progr_form as curso,    
       ca.nm_campus as campus,
       t.nm_turno as turno,
       v.nu_ano_admis || '.' || v.nu_semtr_admis as ingresso,              
       ti.ds_tp_ingrs as tipo_ingresso,
       pe.cd_perf as perfil,       
       sa.cd_perd_letv as periodo_letivo,
       tsa.ds_tp_sit_acad as situacao_academica,
       (select pf1.nm_progr_form || ' - ' || v1.nu_ano_admis || '.' || v1.nu_semtr_admis
          from siga_vinculo v1,
               siga_programa_formacao pf1
         where v1.cd_progr_form=pf1.cd_progr_form
           and v1.nm_cpf_pess=v.nm_cpf_pess
           and v1.nu_matr_curso<>v.nu_matr_curso
           and pf1.cd_nivel=1
           and v1.nu_ano_admis>=v.nu_ano_admis
           and v1.fl_vinc=1
           and rownum=1
       ) as outro_curso_ingresso
 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' -- Ativo/Inativo
   and pf.cd_nivel in (1) -- Graduacao
   and tv.cd_turno=t.cd_turno
   and sa.cd_tp_sit_acad in (6,7,8,9,17)
   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 sa.cd_perd_letv>='2010.1'
  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