Usuários de escolas

 1 /*
 2 - Usuários de escola (CP, diretor..)
 3 */
 4 
 5 USE PUB_SPO_GestaoPedagogica --ALTERAR BD
 6 GO
 7 
 8 SELECT
 9 	UadSuperior.uad_nome AS DRE
10 	, Esc.esc_nome AS Escola
11 	, Pes.pes_nome as Nome
12 	, Usu.usu_login AS [login]
13 	, Gru.gru_nome AS GrupoLogin
14 	,visao.vis_nome AS Visão
15 FROM RHU_Colaborador Col WITH(NOLOCK)
16 INNER JOIN Synonym_PES_Pessoa Pes WITH(NOLOCK)
17 	ON Pes.pes_id = Col.pes_id
18 INNER JOIN Synonym_SYS_Usuario Usu WITH(NOLOCK)
19 	ON Usu.pes_id = Pes.pes_id
20 	AND Usu.usu_situacao <> 3
21 INNER JOIN Synonym_SYS_UsuarioGrupo UGru WITH(NOLOCK)
22 	ON Ugru.usu_id = Usu.usu_id
23 	AND UGru.usg_situacao <> 3
24 INNER JOIN PUB_SPO_CoreSSO..SYS_UsuarioGrupoUA UGruUA WITH(NOLOCK) --ALTERAR BD
25 	ON UGruUA.gru_id = UGru.gru_id
26 	AND UGruUA.usu_id = Usu.usu_id
27 INNER JOIN Synonym_SYS_Grupo Gru WITH(NOLOCK)
28 	ON Gru.gru_id = UGru.gru_id
29 	AND Gru.gru_situacao <> 3
30 	AND Gru.sis_id = 102 -- SGP
31 
32 INNER JOIN Synonym_SYS_UnidadeAdministrativa Uad WITH(NOLOCK)
33 	ON Uad.ent_id = UGruUA.ent_id
34 	AND Uad.uad_id = UGruUA.uad_id
35 
36 INNER JOIN ESC_Escola Esc WITH(NOLOCK)
37 	ON Esc.ent_id = Uad.ent_id
38 	AND Esc.uad_id = Uad.uad_id
39 	AND Esc.esc_situacao <> 3
40 
41 INNER JOIN Synonym_SYS_UnidadeAdministrativa UadSuperior WITH(NOLOCK)
42 	ON UadSuperior.ent_id = Uad.ent_id
43 	AND UadSuperior.uad_id = ISNULL(esc.uad_idSuperiorGestao, Uad.uad_idSuperior)
44 
45 INNER JOIN PUB_SPO_CoreSSO..SYS_Visao visao WITH(NOLOCK) ON visao.vis_id = Gru.vis_id --ALTERAR BD
46 
47 WHERE
48 	Col.col_situacao <> 3
49 	--AND NOT EXISTS (SELECT 1 FROM ACA_Docente Doc WITH(NOLOCK) WHERE Doc.col_id = Col.col_id AND Doc.doc_situacao <> 3)
50 
51 order by
52 	Uad.uad_nome
53 	, Pes.pes_nome