Bring manager and employee who are on the same table

Question

I have a table of EMPLOYEES and another call HIER_SAP. In the HIER_SAP I have two important fields for what I want, that are the fields CODSUP and CODFUNC. For each CODFUNC(official) I have a correspondent CODSUP(manager). Well, what I need is to catch the employee manager in the table EMPLOYEES. So I did this query:

SELECT
         --SEQ_DADOS_CADASTRAIS.NEXTVAL,
         --SYSDATE,
         PAN.ATSF_NOME,
         GE.ATSF_NOME AS GESTOR,
         PAN.PKSF_CHAPA,
         PAN.FKSF_CCUSTO,
         QUA.ATSF_FUN_PORT_EXT AS CARGO,
         COMP.ATSF_CO_PORT_EXT AS UO,
         AREA.ATSF_AREA_PORT_ABR,

         'P',
         '1'
     FROM 
         PANA101V_EMPREGADO PAN 
         LEFT JOIN TAKB100T_EMPREGADO  EMP  ON PAN.PKSF_CHAPA = EMP.PKND_NUMERO_PESSOAL
         LEFT JOIN PANA004T_COMPONENT COMP ON COMP.PKSF_COMP_ORGAN = PAN.FKND_COMP_ORGAN
         LEFT JOIN  PANA001T_QUALIFICA QUA ON QUA.PKSF_FUNCAO_NIVEL = PAN.FKSF_FUNCAO
         LEFT JOIN PANA011T_AREA AREA ON AREA.PKSF_AREA = COMP.FKSF_AREA

         LEFT JOIN(SELECT
         S.CODFUNC,
         S.CODSUP,
         P.ATSF_NOME
         FROM TAKB437T_HIER_SAP S INNER JOIN PANA101V_EMPREGADO P ON S.CODFUNC = P.PKSF_CHAPA
         WHERE CODFUNC IN( SELECT PKND_NUMERO_PESSOAL FROM TAKB100T_EMPREGADO))GE ON PAN.PKSF_CHAPA = GE.CODSUP

         WHERE PKSF_CHAPA = 1705873

The GE table is wrong, because it is not bringing me the correct data. It needs to be adjusted, I know, but I do not know how to do. What do I need to do to resolve this gap? The table that has the employee and manager is this: PANA101V_EMPREGADO. The others are to bring other information. This query will popular a INSERT in another table created.

Answer 1

If I understand correctly it is enough only the EMPLOYEES table participate with two roles: EMPLOYEES and MANAGER:

SELECT
         --SEQ_DADOS_CADASTRAIS.NEXTVAL,
         --SYSDATE,
         PAN.ATSF_NOME,
         GESTOR.ATSF_NOME AS GESTOR,
         PAN.PKSF_CHAPA,
         PAN.FKSF_CCUSTO,
         QUA.ATSF_FUN_PORT_EXT AS CARGO,
         COMP.ATSF_CO_PORT_EXT AS UO,
         AREA.ATSF_AREA_PORT_ABR,
         'P',
         '1'
     FROM 
         PANA101V_EMPREGADO PAN 
         LEFT JOIN TAKB100T_EMPREGADO  EMP  ON PAN.PKSF_CHAPA = EMP.PKND_NUMERO_PESSOAL
         LEFT JOIN PANA004T_COMPONENT COMP ON COMP.PKSF_COMP_ORGAN = PAN.FKND_COMP_ORGAN
         LEFT JOIN PANA001T_QUALIFICA QUA ON QUA.PKSF_FUNCAO_NIVEL = PAN.FKSF_FUNCAO
         LEFT JOIN PANA011T_AREA AREA ON AREA.PKSF_AREA = COMP.FKSF_AREA
            LEFT JOIN TAKB437T_HIER_SAP S ON S.CODFUNC = PAN.CODFUNC
            LEFT JOIN PANA101V_EMPREGADO GESTOR ON GESTOR.CODSUP = PAN.CODSUP;
         WHERE PKSF_CHAPA = 1705873