declare
  v_cpf VARCHAR2(11) := '0';
  v_ano_base NUMBER := 2014;
  v_id_ies NUMBER :=587;
  
begin
  dbms_output.put_line('30|' || v_id_ies || '|3');
  
  for r in (
    select '31' as TipoRegistro,
           null as IdDocenteIes,
           translate(p.nm_pess,'âàãáÁÂÀÃéèêÈÉÊìíÌÍòóôõÒÓÔÕùüúÙÜÚÇç','AAAAAAAAEEEEEEIIIIOOOOOOOOUUUUUUCC') as Nome,
           p.nm_cpf_pess as CPF,
           case p.nm_sigl_pais_nac
                when 'BRA' then null
                else p.nu_pass
           end as Passaporte,
           to_char(p.dt_nasc,'DDMMYYYY') as DataNascimento,
           case p.nm_sexo
                when 'M' then '0'
                else '1'
           end as Sexo,
           '0' as Cor,
           translate(p.nm_mae,'âàãáÁÂÀÃéèêÈÉÊìíÌÍòóôõÒÓÔÕùüúÙÜÚÇç','AAAAAAAAEEEEEEIIIIOOOOOOOOUUUUUUCC') as NomeMae,
           case p.nm_sigl_pais_nac
                when 'BRA' then '1'
                else '3'
           end as Nacionalidade,
           p.nm_sigl_pais_nac as PaisOrigem,  
                      case 
               when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat in ('5136','5154','5189','5198','5216','5255','5259','5301','5302','5362','5384','5406','5416','5453','5483') then '26'
               when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='9668' then '35'
               when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat is null then '26'
               when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat not in ('5136','5154','5189','5198','5216','5255','5259','5301','5302','5362','5384','5406','5416','5453','5483','9668') then
                    (select uf.cd_uf
                       from ibge_uf uf,                   
                            ibge_municipio mu,
                            siga_cidade ci
                      where uf.nm_sigla=ci.nm_sigl_uf
                        and mu.cd_uf=uf.cd_uf
                        and mu.nm_munic=ci.nm_cidd
                        and ci.cd_cidd=p.cd_cidd_nat
                     ) 
                else null
           end as UFNascimento,
           case 
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5136' then '2600104'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5154' then '2601201'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5189' then '2602902'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5198' then '2603454'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5216' then '2604106'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5255' then '2605707'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5259' then '2606002'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat in ('5301','5302') then '2607901'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5362' then '2609600'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5384' then '2610707'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5406' then '2611606'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5416' then '2612208'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5453' then '2613909'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='5483' then '2615706'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat='9668' then '3550308'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat is null then '2611606'
                when p.nm_sigl_pais_nac='BRA' and p.cd_cidd_nat not in ('5136','5154','5189','5198','5216','5255','5259','5301','5302','5362','5384','5406','5416','5453','5483','9668') then                    
                     (select mu.cd_munic
                        from ibge_uf uf,                   
                             ibge_municipio mu,
                             siga_cidade ci
                       where uf.nm_sigla=ci.nm_sigl_uf
                         and mu.cd_uf=uf.cd_uf
                         and mu.nm_munic=ci.nm_cidd
                          and ci.cd_cidd=p.cd_cidd_nat
                      )
                else null
           end as MunicipioNascimento,           
           '0' as DocenteDeficiencia,
           null as DeficienciaCegueira,
           null as DeficienciaVisao,
           null as DeficienciaSurdez,
           null as DeficienciaAuditiva,
           null as DeficienciaFisica,
           null as DeficienciaSurdoCegueira,
           null as DeficienciaMultipla,
           null as DeficienciaIntelectual,
           '2' as Escolaridade,
           case  
                when d.cd_esclr in (12,15) then 1
                when d.cd_esclr in (13) then 2
                when d.cd_esclr in (14,19,16,18) then 3
                else 0
           end as PosGraduacao,
           '1' as SituacaoDocente,
           case 
                when d.dt_demis is null then 1
                else 0
           end as Exercicio3112,
           case d.nm_regm_docnt
                when 'DE' then 1
                when '40' then 2
                else 3
           end as RegimeTrabalho,
           case
                when d.nm_sigl_tp_docnt in ('MAS','MAT') then 1
                else 0
           end as DocenteSubstituto,
           0 as DocenteVisitante,
           null as TipoVinculoVisitante,
           0 as AtuacaoSequencial,
           case                
               when (select count(8)
                     from siga_docnt_turma dt,
                          siga_turma t,
                          siga_atividade_academica aa,
                          siga_periodo_letivo pl 
                    where dt.nm_cpf_pess=d.nm_cpf_pess
                      and dt.cd_docnt=d.cd_docnt
                      and dt.cd_turma=t.cd_turma
                      and t.cd_ativ_acad=aa.cd_ativ_acad
                      and aa.cd_perd_letv=pl.cd_perd_letv
                      and pl.nu_ano_perd_letv=v_ano_base 
                      and exists (select 8
                                    from siga_matricula_turma mt,
                                         siga_vinculo v,
                                         siga_programa_formacao pf,
                                         siga_modal_progr_form mpf
                                   where mt.cd_turma=t.cd_turma
                                     and mt.nu_matr_curso=v.nu_matr_curso
                                     and v.cd_progr_form=pf.cd_progr_form
                                     and pf.cd_progr_form = mpf.cd_progr_form
                                     and pf.cd_nivel='1'
                                     and mpf.cd_modal<>'1'
                                     and mt.nu_sit_matr_turma=1
                                     and pf.cd_progr_form not in (14,15)
                                 )                                  
                    ) > 0 then 1
                    else 0
           end as AtuacaoPresencial,
           case                
               when (select count(8)
                     from siga_docnt_turma dt,
                          siga_turma t,
                          siga_atividade_academica aa,
                          siga_periodo_letivo pl 
                    where dt.nm_cpf_pess=d.nm_cpf_pess
                      and dt.cd_docnt=d.cd_docnt
                      and dt.cd_turma=t.cd_turma
                      and t.cd_ativ_acad=aa.cd_ativ_acad
                      and aa.cd_perd_letv=pl.cd_perd_letv
                      and pl.nu_ano_perd_letv=v_ano_base 
                      and exists (select 8
                                    from siga_matricula_turma mt,
                                         siga_vinculo v,
                                         siga_programa_formacao pf,
                                         siga_modal_progr_form mpf
                                   where mt.cd_turma=t.cd_turma
                                     and mt.nu_matr_curso=v.nu_matr_curso
                                     and v.cd_progr_form=pf.cd_progr_form
                                     and pf.cd_progr_form = mpf.cd_progr_form
                                     and pf.cd_nivel='1'
                                     and mpf.cd_modal='1'
                                     and mt.nu_sit_matr_turma=1
                                     and pf.cd_progr_form not in (14,15)
                                 )                                  
                    ) > 0 then 1
                    else 0
           end as AtuacaoEAD,
           case                
               when (select count(8)
                     from siga_docnt_turma dt,
                          siga_turma t,
                          siga_atividade_academica aa,
                          siga_periodo_letivo pl 
                    where dt.nm_cpf_pess=d.nm_cpf_pess
                      and dt.cd_docnt=d.cd_docnt
                      and dt.cd_turma=t.cd_turma
                      and t.cd_ativ_acad=aa.cd_ativ_acad
                      and aa.cd_perd_letv=pl.cd_perd_letv
                      and pl.nu_ano_perd_letv=v_ano_base 
                      and exists (select 8
                                    from siga_matricula_turma mt,
                                         siga_vinculo v,
                                         siga_programa_formacao pf,
                                         siga_modal_progr_form mpf
                                   where mt.cd_turma=t.cd_turma
                                     and mt.nu_matr_curso=v.nu_matr_curso
                                     and v.cd_progr_form=pf.cd_progr_form
                                     and pf.cd_progr_form = mpf.cd_progr_form
                                     and pf.cd_nivel in ('2','5','7')
                                     and mpf.cd_modal<>'1'
                                     and mt.nu_sit_matr_turma=1
                                     and pf.cd_progr_form not in (85)
                                 )                                  
                    ) > 0 then 1
                    else 0
           end as AtuacaoPosGradPresencial,
           case                
               when (select count(8)
                     from siga_docnt_turma dt,
                          siga_turma t,
                          siga_atividade_academica aa,
                          siga_periodo_letivo pl 
                    where dt.nm_cpf_pess=d.nm_cpf_pess
                      and dt.cd_docnt=d.cd_docnt
                      and dt.cd_turma=t.cd_turma
                      and t.cd_ativ_acad=aa.cd_ativ_acad
                      and aa.cd_perd_letv=pl.cd_perd_letv
                      and pl.nu_ano_perd_letv=v_ano_base 
                      and exists (select 8
                                    from siga_matricula_turma mt,
                                         siga_vinculo v,
                                         siga_programa_formacao pf,
                                         siga_modal_progr_form mpf
                                   where mt.cd_turma=t.cd_turma
                                     and mt.nu_matr_curso=v.nu_matr_curso
                                     and v.cd_progr_form=pf.cd_progr_form
                                     and pf.cd_progr_form = mpf.cd_progr_form
                                     and pf.cd_nivel in ('2','5','7')
                                     and mpf.cd_modal='1'
                                     and mt.nu_sit_matr_turma=1
                                     and pf.cd_progr_form not in (85)
                                 )                                  
                    ) > 0 then 1
                    else 0
           end as AtuacaoPosGradEAD,
           0 as AtuacaoPesquisa,
           0 as AtuacaoExtensao,
           0 as AtuacaoGestao,
           null as BolsaPesquisa,
           d.cd_docnt
      from siga_pessoa p,
           siga_docente d
     where p.nm_cpf_pess=d.nm_cpf_pess
       and d.nm_sigl_tp_docnt <> 'PEB'
       and d.cd_docnt=(select di.cd_docnt 
                         from siga_docente di
                        where di.nm_cpf_pess=d.nm_cpf_pess
                          and ((di.dt_admis<to_date('1612' || v_ano_base,'DDMMYYYY') and di.dt_demis is null) or 
                               (to_char(di.dt_demis,'YYYY')=v_ano_base and not exists (select 8
                                                                                   from siga_docente di1
                                                                                  where di1.nm_cpf_pess=di.nm_cpf_pess
                                                                                    and di1.cd_docnt<>di.cd_docnt
                                                                                    and di1.dt_admis<to_date('1612' || v_ano_base,'DDMMYYYY') 
                                                                                    and di1.dt_demis is null
                                                                                )
                               ))
                      )          
       and d.cd_tp_sit_docnt not in (2,10,145,144,150,151,147,152,155,153,154,148,146,149,165)                      
      -- and p.nm_cpf_pess in ('96297379491')    
    order by p.nm_pess,
             p.nm_cpf_pess
  ) loop
    if v_cpf <> r.CPF then
       dbms_output.put_line(r.TipoRegistro || '|' ||
                            r.IdDocenteIes || '|' ||                            
                            r.Nome || '|' ||
                            r.CPF || '|' ||
                            r.Passaporte || '|' ||
                            r.DataNascimento || '|' ||
                            r.Sexo || '|' ||
                            r.Cor || '|' ||
                            r.NomeMae || '|' ||
                            r.Nacionalidade || '|' ||
                            r.PaisOrigem || '|' ||           
                            r.UFNascimento || '|' ||
                            r.MunicipioNascimento || '|' ||           
                            r.DocenteDeficiencia || '|' ||
                            r.DeficienciaCegueira || '|' ||
                            r.DeficienciaVisao || '|' ||
                            r.DeficienciaSurdez || '|' ||
                            r.DeficienciaAuditiva || '|' ||
                            r.DeficienciaFisica || '|' ||
                            r.DeficienciaSurdoCegueira || '|' ||
                            r.DeficienciaMultipla || '|' ||
                            r.DeficienciaIntelectual || '|' ||
                            r.Escolaridade || '|' ||
                            r.PosGraduacao || '|' ||
                            r.SituacaoDocente || '|' ||
                            r.Exercicio3112 || '|' ||
                            r.RegimeTrabalho || '|' ||
                            r.DocenteSubstituto || '|' ||
                            r.DocenteVisitante || '|' ||
                            r.TipoVinculoVisitante || '|' ||
                            r.AtuacaoSequencial || '|' ||
                            r.AtuacaoPresencial || '|' ||
                            r.AtuacaoEAD || '|' ||
                            r.AtuacaoPosGradPresencial || '|' ||
                            r.AtuacaoPosGradEAD || '|' ||
                            r.AtuacaoPesquisa || '|' ||
                            r.AtuacaoExtensao || '|' ||
                            r.AtuacaoGestao || '|' ||
                            r.BolsaPesquisa
                            );                                       
    end if;
    
    for s in (       
       select pf.cd_inep
         from siga_docnt_turma dt,
              siga_turma t,
              siga_atividade_academica aa,
              siga_periodo_letivo pl,
              siga_matricula_turma mt,
              siga_vinculo v,
              siga_programa_formacao pf
        where dt.nm_cpf_pess=r.CPF
          and dt.cd_docnt=r.cd_docnt
          and dt.cd_turma=t.cd_turma
          and t.cd_ativ_acad=aa.cd_ativ_acad
          and aa.cd_perd_letv=pl.cd_perd_letv
          and pl.nu_ano_perd_letv=v_ano_base
          and mt.cd_turma=t.cd_turma
          and mt.nu_matr_curso=v.nu_matr_curso
          and v.cd_progr_form=pf.cd_progr_form          
          and pf.cd_nivel='1'
          and pf.cd_progr_form not in (14,15)
          and mt.nu_sit_matr_turma=1
    group by pf.cd_inep
    order by pf.cd_inep
    ) loop
       dbms_output.put_line('32|' || s.cd_inep);
      end loop;
      
    v_cpf := r.CPF;    
    end loop;
end;