-- Cursos Presenciais

begin
for r in (
select v.nm_cpf_pess,
       v.nu_matr_curso,
       pf.nm_progr_form,
       saMin.Cd_Perd_Letv as perMin,
       saMax.Cd_Perd_Letv as perMax   
  from siga_vinculo v,
       siga_situacao_academica saMin,
       siga_situacao_academica saMax,
       siga_situacao_academica sa,
       siga_programa_formacao pf
 where v.cd_progr_form not in (14,15)
   and v.nu_matr_curso=saMin.nu_matr_curso
   and v.nu_matr_curso=saMax.nu_matr_curso
   and v.cd_progr_form=pf.cd_progr_form
--   and v.fl_vinc='1'
   and pf.cd_nivel='1'
   and saMin.cd_perd_letv=(select min(sa1.cd_perd_letv)
                        from siga_situacao_academica sa1
                       where sa1.nu_matr_curso=saMin.nu_matr_curso)
   and saMax.cd_perd_letv=(select max(sa2.cd_perd_letv)
                        from siga_situacao_academica sa2
                       where sa2.nu_matr_curso=saMax.nu_matr_curso)  
   and sa.nu_matr_curso=v.nu_matr_curso
   and sa.cd_perd_letv in (2014 || '.1', 2014 || '.2')  -- Alterar Ano Base
   and sa.cd_tp_sit_acad not in (18)
   and sa.dt_def_sit=(select max(sa2.dt_def_sit)
                        from siga_situacao_academica sa2
                       where sa.nu_matr_curso=sa2.nu_matr_curso
                         and sa.cd_perd_letv=sa2.cd_perd_letv
                         )                                                   
                       
order by pf.nm_progr_form,
         v.nm_cpf_pess                                
) loop
  for s in (
  select r.nm_progr_form as prog,
         r.nm_cpf_pess as cpf,
         r.nu_matr_curso as mat, 
         pl.cd_perd_letv
    from siga_periodo_letivo pl
   where pl.cd_perd_letv>r.permin
     and pl.cd_perd_letv<r.permax
     and pl.cd_tp_perd_letv=1
     and not exists (select *
                       from siga_situacao_academica sa3
                      where sa3.nu_matr_curso=r.nu_matr_curso
                        and sa3.cd_perd_letv=pl.cd_perd_letv)
     order by pl.cd_perd_letv                        
   ) loop
     dbms_output.put_line(s.prog || ';' || s.cpf || ';' || s.cd_perd_letv);
   end loop;                                                
end loop;
end;        

--Cursos de EAD

begin
for r in (
select v.nm_cpf_pess,
       v.nu_matr_curso,
       pf.nm_progr_form,
       saMin.Cd_Perd_Letv as perMin,
       saMax.Cd_Perd_Letv as perMax   
  from siga_vinculo v,
       siga_situacao_academica saMin,
       siga_situacao_academica saMax,
       siga_situacao_academica sa,
       siga_programa_formacao pf,
       siga_modal_progr_form mpf
 where v.cd_progr_form not in (14,15)
   and v.nu_matr_curso=saMin.nu_matr_curso
   and v.nu_matr_curso=saMax.nu_matr_curso
   and v.cd_progr_form=pf.cd_progr_form
   and pf.cd_progr_form = mpf.cd_progr_form
   and mpf.cd_modal = 1
   and pf.cd_nivel='1'
   and saMin.cd_perd_letv=(select min(sa1.cd_perd_letv)
                        from siga_situacao_academica sa1
                       where sa1.nu_matr_curso=saMin.nu_matr_curso)
   and saMax.cd_perd_letv=(select max(sa2.cd_perd_letv)
                        from siga_situacao_academica sa2
                       where sa2.nu_matr_curso=saMax.nu_matr_curso)  
   and sa.nu_matr_curso=v.nu_matr_curso                    
   and sa.cd_perd_letv in (2014 || '.1', 2014 || '.2')  -- Alterar Ano Base
   and sa.cd_tp_sit_acad not in (18)
   and sa.dt_def_sit=(select max(sa2.dt_def_sit)
                        from siga_situacao_academica sa2
                       where sa.nu_matr_curso=sa2.nu_matr_curso
                         and sa.cd_perd_letv=sa2.cd_perd_letv
                         )                                                                         
order by pf.nm_progr_form,
         v.nm_cpf_pess                                
) loop
  for s in (
  select r.nm_progr_form as prog,
         r.nm_cpf_pess as cpf,
         r.nu_matr_curso as mat, 
         pl.cd_perd_letv
    from siga_periodo_letivo pl
   where pl.cd_perd_letv>r.permin
     and pl.cd_perd_letv<r.permax
     and pl.cd_tp_perd_letv='1'
     and not exists (select *
                       from siga_situacao_academica sa3
                      where sa3.nu_matr_curso=r.nu_matr_curso
                        and sa3.cd_perd_letv=pl.cd_perd_letv)
     order by pl.cd_perd_letv                        
   ) loop
     dbms_output.put_line(s.prog || ';' || s.cpf || ';' || s.cd_perd_letv);
   end loop;                                                
end loop;
end;       

-- Cursos de Pós-Graduação

begin
for r in (
select v.nm_cpf_pess,
       v.nu_matr_curso,
       pf.nm_progr_form,
       saMin.Cd_Perd_Letv as perMin,
       saMax.Cd_Perd_Letv as perMax   
  from siga_vinculo v,
       siga_situacao_academica sa,
       siga_situacao_academica saMin,
       siga_situacao_academica saMax,
       siga_programa_formacao pf
 where v.cd_progr_form not in (14,15)
   and v.nu_matr_curso=saMin.nu_matr_curso
   and v.nu_matr_curso=saMax.nu_matr_curso
   and v.cd_progr_form=pf.cd_progr_form
--   and v.fl_vinc='1'
   and pf.cd_nivel in ('2','5','6','7','8')
   and saMin.cd_perd_letv=(select min(sa1.cd_perd_letv)
                        from siga_situacao_academica sa1
                       where sa1.nu_matr_curso=saMin.nu_matr_curso)
   and saMax.cd_perd_letv=(select max(sa2.cd_perd_letv)
                        from siga_situacao_academica sa2
                       where sa2.nu_matr_curso=saMax.nu_matr_curso)  
   and sa.nu_matr_curso=v.nu_matr_curso
   and sa.cd_perd_letv in (2014 || '.1', 2014 || '.2')  -- Alterar Ano Base
   and sa.cd_tp_sit_acad not in (18)
   and sa.dt_def_sit=(select max(sa2.dt_def_sit)
                        from siga_situacao_academica sa2
                       where sa.nu_matr_curso=sa2.nu_matr_curso
                         and sa.cd_perd_letv=sa2.cd_perd_letv
                         )                                                   
order by pf.nm_progr_form,
         v.nm_cpf_pess                                
) loop
  for s in (
  select r.nm_progr_form as prog,
         r.nm_cpf_pess as cpf,
         r.nu_matr_curso as mat, 
         pl.cd_perd_letv
    from siga_periodo_letivo pl
   where pl.cd_perd_letv>r.permin
     and pl.cd_perd_letv<r.permax
     and pl.cd_tp_perd_letv=1
     and not exists (select *
                       from siga_situacao_academica sa3
                      where sa3.nu_matr_curso=r.nu_matr_curso
                        and sa3.cd_perd_letv=pl.cd_perd_letv)
     order by pl.cd_perd_letv                        
   ) loop
     dbms_output.put_line(s.prog || ';' || s.cpf || ';' || s.cd_perd_letv);
   end loop;                                                
end loop;
end;