Como retornar a hierarquia de funcionários do HR via SQL

Depois de um longo período sem escrever no blog, estou retornando com uma dica para quem precisa montar uma query que retorne a hierarquia de funcionários do HR (ou qualquer outra com hierarquia).

Eu geralmente utilizo a base dessa query quando estou montando um grupo dinâmico de aprovadores no AME (Módulo de aprovações do EBS). Nesse caso, a query retorna o supervisor de cada funcionário a partir de um funcionário informado.

Para realizar esse procedimento de busca hierárquica precisamos utilizar um auto-relacionamento entre as tabelas, ou seja, recursividade:

No Oracle existe um recurso que torna isso muito fácil, caso contrário teríamos que utilizar várias sub-queries. Para construir essa query vamos utilizar o CONNECT BY PRIOR e START WITH.

No cadastro de funcionários do HR, cada funcionário pode ter um supervisor associado. E como esse supervisor também é um funcionário, ele também poderá ter um supervisor e assim sucessivamente.

Como exemplo, criei três funcionários no EBS:

Gerente Um
Diretor
Presidente

A associação foi feita conforme abaixo (Diretor é supervisor do Gerente Um):

O mesmo foi feito para Diretor x Presidente.

Se pesquisarmos na tabela onde é feito o relacionamento entre funcionário x supervisor (PER_ALL_ASSIGNMENTS_F), teremos:

Onde:

Linha 1

ASSIGNMENT_ID 56271: ID do relacionamento
PERSON_ID 56271: É o ID do funcionário “Gerente UM”
SUPERVISOR_ID 56272: É o ID do funcionário “Diretor”

Linha 2

ASSIGNMENT_ID 56272: ID do relacionamento
PERSON_ID 56272: É o ID do funcionário “Diretor”
SUPERVISOR_ID 56273: É o ID do funcionário “Presidente”

Bom, agora que a hierarquia está explicada, vamos ver como ficaria a query hierárquica:

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT LEVEL
      ,PAP.first_name      first_name
      ,PAP.last_name       last_name
FROM   per_all_assignments_f PAA
      ,per_all_people_f      PAP
WHERE  TRUNC(SYSDATE) BETWEEN TRUNC(PAA.effective_start_date) 
                          AND TRUNC(PAA.effective_end_date)
AND    PAA.person_id = PAP.person_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(PAP.effective_start_date) 
                          AND TRUNC(PAP.effective_end_date)
CONNECT BY PRIOR PAA.supervisor_id = PAA.person_id
START WITH PAA.person_id = 56271 -- ID do funcionário Gerente Um
ORDER BY LEVEL

A cláusula CONNECT BY PRIOR faz o auto-relacionamento e a cláusula START WITH informa ao comando SQL por onde começar a busca hierárquica. Como informamos o ID do “Gerente Um”, ele vai iniciar nele e vai subindo conforme cada supervisor na hierarquia, veja o resultado:

A coluna “Level” é virtual e criada automaticamente, ela identifica o nível da hierarquia.

Mais detalhes sobre esses comandos podem ser vistos aqui.

Já deu uma olhada no mini-curso grátis de SQL aqui no blog? Fica a dica!

E qualquer dúvida, não deixe de perguntar nos comentários.

Até a próxima.