Usuários de DRE

 1 /*
 2 - Usuários de DRE
 3 */
 4 
 5 USE SGP_GestaoPedagogica
 6 GO
 7 
 8 
 9 
10 SELECT
11 	Uad.uad_nome AS DRE
12 	, Pes.pes_nome as Nome
13 	, Usu.usu_login AS [login]
14 	, Gru.gru_nome AS GrupoLogin
15 FROM RHU_Colaborador Col WITH(NOLOCK)
16 
17 INNER JOIN Synonym_PES_Pessoa Pes WITH(NOLOCK)
18 	ON Pes.pes_id = Col.pes_id
19 INNER JOIN Synonym_SYS_Usuario Usu WITH(NOLOCK)
20 	ON Usu.pes_id = Pes.pes_id
21 	AND Usu.usu_situacao <> 3
22 INNER JOIN Synonym_SYS_UsuarioGrupo UGru WITH(NOLOCK)
23 	ON Ugru.usu_id = Usu.usu_id
24 	AND UGru.usg_situacao <> 3
25 INNER JOIN SGP_CoreSSO..SYS_UsuarioGrupoUA UGruUA WITH(NOLOCK)
26 	ON UGruUA.gru_id = UGru.gru_id
27 	AND UGruUA.usu_id = Usu.usu_id
28 INNER JOIN Synonym_SYS_Grupo Gru WITH(NOLOCK)
29 	ON Gru.gru_id = UGru.gru_id
30 	AND Gru.gru_situacao <> 3
31 	AND Gru.sis_id = 102 -- SGP
32 
33 INNER JOIN Synonym_SYS_UnidadeAdministrativa Uad WITH(NOLOCK)
34 	ON Uad.ent_id = UGruUA.ent_id
35 	AND Uad.uad_id = UGruUA.uad_id
36 	AND Uad.tua_id = '52022558-8C00-4539-99FB-B647BC994D5E'
37 WHERE
38 	Col.col_situacao <> 3
39 	AND NOT EXISTS (SELECT 1 FROM ACA_Docente Doc WITH(NOLOCK) WHERE Doc.col_id = Col.col_id AND Doc.doc_situacao <> 3)
40 
41 order by
42 	Uad.uad_nome
43 	, Pes.pes_nome