Historico_Relatorio de Inconsistencias Cadastrais

SELECT distinct a.situacao
FROM (

         SELECT 'SERVIDOR ATIVO SEM LOGIN' AS SITUACAO,
 'A matricula ' || MATRICULA || ' está ativa mas não tem login configurado no sistema' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 500 PRIORIDADE,
 'Mat. ' || MATRICULA CODIGO
         FROM RH_SERVIDOR
                  INNER JOIN rh_pessoa ON pessoa_fisica_id = rh_pessoa.ID
 WHERE rh_servidor.ativo is true
 AND user_id IS NULL
 AND type_by_possession NOT IN ('EXT', 'EST', 'BFP', 'APS', 'SAP', 'MAP', 'EXT', 'TCR', 'CTR')

         UNION ALL

 select 'SERVIDOR ATIVO SEM PROVIMENTO' AS SITUACAO,
 'A matricula ' || MATRICULA || '(' || nome || ') está ativa mas não tem provimento ativo' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 500 PRIORIDADE,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         from rh_servidor
                  INNER JOIN rh_pessoa ON pessoa_fisica_id = rh_pessoa.ID
 inner join rh_movpessoal on rh_servidor.id = rh_movpessoal.servidor_id
 left join rh_movposse on rh_movpessoal.id = rh_movposse.movimentacaopessoal_ptr_id
 where rh_servidor.ativo is true
 and type_by_possession not in ('EST', 'TCR', 'EXT')
         group by matricula, nome, type_by_possession, rh_servidor.ativo
 having count(rh_movposse.*) < 1

 UNION ALL

 select 'LOGIN SEM VINCULO COM SERVIDOR' AS SITUACAO,
 'O login ' || auth_user.username || ' não tem vinculo com nenhum servidor' DESCRICAO,
 'DTI' RESPONSAVEL,
 500 prioridade,
 'Login ' || auth_user.username CODIGO
         from auth_user
                  left join rh_servidor on auth_user.id = rh_servidor.user_id
 where matricula is null
 and is_active is true

 UNION ALL

 select 'SERVIDOR ATIVO SEM PERMISSÃO PADRÃO' AS SITUACAO,
 'Mat. ' || matricula || ' sem a permissão padrão: mpmt-perfil-vdf-padrao' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 750 prioridade,
 'Mat. ' || matricula || ' Login ' || auth_user.username CODIGO
         from auth_user
                  left join auth_user_groups aug on auth_user.id = aug.user_id
 left join auth_group ag on aug.group_id = ag.id and ag.name = 'mpmt-perfil-vdf-padrao'
 inner join rh_servidor on auth_user.id = rh_servidor.user_id AND rh_servidor.ativo IS TRUE
 where ag.name is null
 and (select USERNAME ~ '[0-9]') is false -- usuarios com login valido;

 union all

 select 'SERVIDOR ATIVO SEM MENU PADRÃO' AS SITUACAO,
 'Mat. ' || matricula || ' sem menu padrão: mpmt-menu-vdf-padrao' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 750 prioridade,
 'Mat. ' || matricula || ' Login ' || auth_user.username CODIGO
         from auth_user
                  inner join rh_servidor rs on auth_user.id = rs.user_id and rs.ativo is true
 where auth_user.id not in (
             select auth_user.id
 from auth_user
                      inner join engine_controllerpermission_users
                                 on auth_user.id = engine_controllerpermission_users.user_id
 inner join engine_controllerpermission
                                 on engine_controllerpermission_users.controllerpermission_id =
                                    engine_controllerpermission.id
 WHERE name like 'mpmt-menu-vdf-padrao%'
 )
           and (select USERNAME ~ '[0-9]') is false

 UNION ALL

 select 'SERVIDOR ATIVO COM CPF INVÁLIDO' AS SITUACAO,
 'Mat. ' || matricula || ' não tem cpf valido cadastrado (CPF: "' || rh_pessoafisica.cpf ||
                '")' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 750 prioridade,
 'Mat. ' || matricula CODIGO
         FROM rh_servidor
                  INNER JOIN rh_pessoa ON pessoa_fisica_id = rh_pessoa.ID
 INNER JOIN RH_PESSOAFISICA ON rh_servidor.pessoa_fisica_id = rh_pessoafisica.pessoa_ptr_id
 where length(cpf) <> 11
 OR (select cpf ~ '[a-zA-Z]') is true

 union all

 SELECT 'DEPENDENTE DE SERVIDOR ATIVO COM CPF INVÁLIDO' AS SITUACAO,
 'Dependente ' || dependente.nome || ' da matricula ' || rh_servidor.matricula ||
                ' não tem cpf válido (CPF: "' || RH_PESSOAFISICA.cpf || '")' DESCRICAO,
 'DTI' RESPONSAVEL,
 500 prioridade,
 'Mat. ' || matricula || ' Dep. ' || dependente.nome CODIGO
         FROM rh_dependente
                  INNER JOIN RH_SERVIDOR ON rh_dependente.servidor_id = rh_servidor.id
 INNER JOIN RH_PESSOAFISICA ON rh_dependente.pessoa_fisica_id = rh_pessoafisica.pessoa_ptr_id
 INNER JOIN RH_PESSOA dependente ON rh_pessoafisica.pessoa_ptr_id = dependente.id
 where rh_servidor.ativo is true and
 length(RH_PESSOAFISICA.cpf) <> 11
 OR (select RH_PESSOAFISICA.cpf ~ '[a-zA-Z]') is true

 union all

 select 'SERVIDORES ATIVOS COM PROVIMENTO ENCERRADOS' AS SITUACAO,
 'Mat. ' || matricula ||
                ' está ativa no sistema mas os provimentos estão encerrados, verificar se falta desligar' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 250 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         from rh_servidor
         where rh_servidor.id not in
 (
                   select distinct rh_servidor.id
 from rh_servidor
                            inner join rh_movpessoal on rh_servidor.id = rh_movpessoal.servidor_id
 inner join rh_movposse on rh_movpessoal.id = rh_movposse.movimentacaopessoal_ptr_id
 where 1 = 1
 and rh_movposse.data_desligamento is null
 and rh_servidor.ativo is true)
           and rh_servidor.ativo is true
 and type_by_possession not in ('EXT', 'EST')

         union all

 select 'SERVIDORES ATIVOS SEM CARGA HORARIA CADASTRADA' AS SITUACAO,
 'Mat. ' || matricula || ' não tem carga horaria ativa cadastrada' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 750 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         from rh_servidor
         where matricula not in (
             select matricula
 from rh_servidor
                      left outer join rh_cargahoraria on rh_servidor.id = rh_cargahoraria.servidor_id
 where data_fim is null
 )
           and rh_servidor.ativo is true

 UNION ALL

 select 'SERVIDOR ATIVO SEM LOTACAO/TITULARIDADE' AS SITUACAO,
 'Mat. ' || matricula || ' não tem lotacao ativa ou titularidade' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 750 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         from rh_servidor
         where matricula not in (
             select rh_servidor.matricula
 from rh_servidor
                      inner join rh_servidorlotacao on rh_servidor.id = rh_servidorlotacao.servidor_id
 where designacao is not true
 and data_vigencia_fim is null
 )
           and rh_servidor.ativo is true
 and rh_servidor.type_by_possession not IN ('MAP', 'BFP', 'MBR', 'SAP', 'EXT', 'TCR')

         UNION ALL

 select 'SERVIDOR ATIVO SEM DESIGNACAO' AS SITUACAO,
 'Mat. ' || matricula || ' não tem designacao ativa' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 750 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         from rh_servidor
         where matricula not in (
             select rh_servidor.matricula
 from rh_servidor
                      inner join rh_servidorlotacao on rh_servidor.id = rh_servidorlotacao.servidor_id
 where designacao is true
 and data_vigencia_fim is null
 )
           and rh_servidor.ativo is true
 and rh_servidor.type_by_possession not IN ('MAP', 'BFP', 'MBR', 'SAP', 'EXT', 'TCR')

         union all

 SELECT 'SERVIDOR ATIVO EM LOTACAO INATIVA' AS SITUACAO,
 'Mat. ' || matricula || ' está lotado/designado em uma lotação inativa ( ' || rh_orgaogeral.nome ||
                ' )' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 750 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         FROM RH_SERVIDOR
                  INNER JOIN RH_SERVIDORLOTACAO ON rh_servidor.id = rh_servidorlotacao.servidor_id
 INNER JOIN RH_LOTACAO ON rh_servidorlotacao.lotacao_id = rh_lotacao.orgaogeral_ptr_id
 INNER JOIN RH_ORGAOGERAL ON rh_lotacao.orgaogeral_ptr_id = rh_orgaogeral.id
 WHERE rh_orgaogeral.ativo IS FALSE
 and rh_servidor.ativo = true

 UNION ALL

 SELECT 'SERVIDOR ATIVO COM SALDO DE FÉRIAS DIFERENTE DE 30' AS SITUACAO,
 'Mat. ' || matricula || ' com saldo de férias diferente de 30 dias. (Periodo Aquisitivo ' ||
                dayoff_groupperiod.year_reference || '/' || dayoff_groupperiod.period || ' - ' ||
                dayoff_acquisitionperiod.days_not_booked_cache || ' dias)' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 750 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         from dayoff_acquisitionperiod
                  inner join dayoff_groupperiod on dayoff_acquisitionperiod.group_period_id = dayoff_groupperiod.id
 inner join dayoff_configuration on dayoff_groupperiod.configuration_id = dayoff_configuration.id
 inner join rh_servidor on dayoff_acquisitionperiod.employee_id = rh_servidor.id
 inner join rh_pessoafisica on rh_servidor.pessoa_fisica_id = rh_pessoafisica.pessoa_ptr_id
 inner join rh_pessoa on rh_pessoafisica.pessoa_ptr_id = rh_pessoa.id
 where dayoff_configuration.sub_type_of_usufruct in (9000, 9001)
           and dayoff_acquisitionperiod.days_not_booked_cache > 0
 and dayoff_acquisitionperiod.days_not_booked_cache not in (30, 60)
           and rh_servidor.ativo is true


 union all


 SELECT 'SERVIDOR ATIVO COM LICENÇA PREMIO DIFERENTE DE 90 DIAS' AS SITUACAO,
 'Mat. ' || matricula ||
                ' tem periodo de licença premio com total de dias diferente de 90. (Periodo Aquisitivo ' ||
                dayoff_groupperiod.year_reference || '/' || dayoff_groupperiod.period || ' - ' ||
                dayoff_acquisitionperiod.days || ' dias)' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END RESPONSAVEL,
 750 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         from dayoff_acquisitionperiod
                  inner join dayoff_groupperiod on dayoff_acquisitionperiod.group_period_id = dayoff_groupperiod.id
 inner join dayoff_configuration on dayoff_groupperiod.configuration_id = dayoff_configuration.id
 inner join rh_servidor on dayoff_acquisitionperiod.employee_id = rh_servidor.id
 inner join rh_pessoafisica on rh_servidor.pessoa_fisica_id = rh_pessoafisica.pessoa_ptr_id
 inner join rh_pessoa on rh_pessoafisica.pessoa_ptr_id = rh_pessoa.id
 where dayoff_configuration.sub_type_of_usufruct in (9009)
           and dayoff_acquisitionperiod.days > 90
 and rh_servidor.ativo is true

 union all

 SELECT 'LOTACAO COM CAMPO SUBSTITUTO PREENCHIDO' AS SITUACAO,
 'Lotação. "' || ro.nome || '" tem o campo subsituto preenchido' DESCRICAO,
 'DTI' RESPONSAVEL,
 750 prioridade,
 'Lotação. ' || ro.nome CODIGO
         from rh_lotacao
                  inner join rh_orgaogeral ro on rh_lotacao.orgaogeral_ptr_id = ro.id
 where rh_lotacao.responsible_substituted_id is not null

 UNION ALL

 SELECT 'SERVIDOR ATIVO SEM EMAIL' AS SITUACAO,
 'Mat. "' || matricula || '" não tem email no sistema Athenas' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END responsavel,
 750 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         FROM rh_servidor
                  INNER JOIN rh_pessoafisica ON rh_servidor.pessoa_fisica_id = rh_pessoafisica.pessoa_ptr_id
 INNER JOIN RH_PESSOA ON rh_pessoafisica.pessoa_ptr_id = rh_pessoa.id
 WHERE ATIVO IS TRUE
 AND (email_pessoal IS NULL OR email_pessoal = '' OR email_pessoal = ' ')
           AND rh_servidor.type_by_possession not IN ('MAP', 'BFP', 'MBR', 'SAP', 'EXT', 'TCR', 'REQ')


         UNION ALL

 SELECT 'MEMBRO COM CATEGORIA FUNCIONAL ERRADA NO ATHENAS' AS SITUACAO,
 'Mat. "' || matricula || '" está com categoria funcional incorreta no Athenas' DESCRICAO,
 'GER.MEMBROS' responsavel,
 750 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         FROM rh_servidor
                  INNER JOIN rh_pessoafisica ON rh_servidor.pessoa_fisica_id = rh_pessoafisica.pessoa_ptr_id
 INNER JOIN RH_PESSOA ON rh_pessoafisica.pessoa_ptr_id = rh_pessoa.id
 WHERE 1 = 1
 AND matricula >= 1000
 and matricula < 2000
 AND (email_pessoal IS NULL OR email_pessoal = '' OR email_pessoal = ' ')
           AND rh_servidor.type_by_possession not IN ('MAP', 'BFP', 'MBR', 'SAP', 'EXT', 'TCR', 'REQ')

         UNION ALL

 select 'SERVIDOR ATIVO COM DATA DE NASCIMENTO ERRADO' AS SITUACAO,
 'Mat. "' || matricula || '" está com data de nascicmento errada. Data menor que 18 anos' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END responsavel,
 750 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO
         from rh_servidor
                  inner join rh_pessoafisica on rh_servidor.pessoa_fisica_id = rh_pessoafisica.pessoa_ptr_id
 inner join rh_pessoa on rh_pessoafisica.pessoa_ptr_id = rh_pessoa.id
 where ativo is true
 and ((data_nascimento >= (CURRENT_DATE - 365 * 18)))
           and type_by_possession not in ('BFP')

         union all

 select 'SERVIDOR ATIVO EM CARGO DE CHEFIA COM DESIGNACAO SEM RESPONSABILIDADE' AS SITUACAO,
 'Mat. "' || matricula || '" está em cargo de chefia mas na designação não é responsável' DESCRICAO,
 CASE
 WHEN type_by_possession IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')
                        THEN 'GER.MEMBROS'
 ELSE 'GER.SERVIDORES'
 END responsavel,
 750 prioridade,
 'Mat. ' || matricula || ' Tipo ' || rh_servidor.type_by_possession CODIGO

         from rh_servidor
                  inner join rh_movpessoal on rh_servidor.id = rh_movpessoal.servidor_id
 inner join rh_movposse on rh_movpessoal.id = rh_movposse.movimentacaopessoal_ptr_id
 inner join rh_servidorlotacao
                             on rh_movposse.movimentacaopessoal_ptr_id = rh_servidorlotacao.movimentacao_posse_id
 inner join rh_quadro on rh_movposse.quadro_id = rh_quadro.id
 inner join rh_cargo on rh_quadro.cargo_id = rh_cargo.id
 where rh_servidorlotacao.designacao is false
 and rh_servidor.ativo is true
 and rh_servidorlotacao.ativo is true
 and rh_movposse.data_desligamento is null
 and rh_cargo.chefia
 and rh_servidor.type_by_possession not IN ('MBR', 'MEL', 'MCM', 'MEC', 'MBR2', 'MEL2', 'MCM2', 'MEC2')

         union all

 -- rh_pessoa com email null
 select 'rh_pessoa com email null' AS SITUACAO,
 'Id. "' || rh_pessoa.id || '" está com campo email null' DESCRICAO,
 'DTI' responsavel,
 750 prioridade,
 'Id. ' || rh_pessoa.id CODIGO
         from rh_pessoa
         where email is null
 ) A
WHERE RESPONSAVEL NOT IN ('DTI');
order by codigo, prioridade;



-- Consultas previstas: * Serão implementadas aos poucos conforme necessidade
-- Servidores sem superior imediato configurado no Athenas
-- Solicitação do portal aguardando aprovador já desligado do MP
-- Solicitação aguardando aprovador afastado sem substituto informado
-- Substituições futuras vinculadas a designações encerradas
-- Servidores e membros com programações conflitantes
-- servidores e membros com substituições em periodo de usufrutos
-- Quantitativo de Servidores com permissão superadmin
-- servidores com 3 férias acumuladas
-- PERIODOS AQUISITIVOS EM ABERTO E COM SALDO VINCULADO A MATRICULAS INATIVAS
-- FILTRO de DEPENDENTES DE SERVIDORES EXONERADOS A PARTIR DE 22/11/2021 mas que precisa enviar para o ESOCIAL
-- lotacao sem lotação superior configurado
-- servidores desligados com verbas extras ativas
-- solicitações de retificação/cancelamento vinculado a programações canceladas