ORACLE -SIGESA: jerarquia de grupos roles y roles consulta recursiva

SELECT
    CONNECT_BY_ROOT GR1.ID_GRUPO_ROL as ID_GRUPO_ROL,
    CONNECT_BY_ROOT GR1.NOMBRE AS GRUPO_RAIZ,
    SUBSTR(SYS_CONNECT_BY_PATH( GR2.ID_GRUPO_ROL || '.' || GR2.NOMBRE, ' > '), INSTR(SYS_CONNECT_BY_PATH(GR2.NOMBRE, ' > '), '> ') + 2) AS JERARQUIA,
    (
        SELECT 
            LISTAGG(ROL.ID_ROL || '.' || ROL.NOMBRE || CHR(13), ',') WITHIN GROUP (ORDER BY ROL.NOMBRE) AS ROLES
            
        FROM SAS.ROL ROL
        INNER JOIN SAS.DETALLE_GRUPO_ROL DGR ON (DGR.ROL = ROL.ID_ROL)
        WHERE DGR.GRUPO_ROL= GR2.ID_GRUPO_ROL
    ) AS ROLES
FROM SAS.DETALLE_GRUPO_ROL DGR
JOIN GRUPO_ROL GR1 ON DGR.GRUPO_ROL = GR1.ID_GRUPO_ROL
JOIN GRUPO_ROL GR2 ON DGR.grupo = GR2.ID_GRUPO_ROL
WHERE GR2.NOMBRE LIKE '%PPI%' AND GR2.NOMBRE NOT LIKE '%ACL%'
START WITH GR1.ID_GRUPO_ROL = 39
CONNECT BY NOCYCLE PRIOR GR2.ID_GRUPO_ROL = DGR.GRUPO_ROL
ORDER BY CONNECT_BY_ROOT GR1.ID_GRUPO_ROL, JERARQUIA;

Consulta final

WITH RESULTADO_ROLES_DIRECTOS AS (

    -- *******************************************************
    -- CONSULTA: ROLES ASIGNADOS DIRECTAMENTE AL USURIO
    -- *******************************************************
    SELECT 
        PER.IDENTIFICACION  AS IDENTIFICACION,
        PER.NOMBRE          AS PERSONA,
        UE.NOMBRE           AS UNIDAD_EJECUTORA,
        NULL                AS GRUPO_ROL,
        NULL                AS JERARQUIA,
        LISTAGG(
        
            '-------------------------------' ||
            CHR(13) ||
            ROL.NOMBRE || 
            CHR(13) ||
            '-------------------------------' ||
            CHR(13) ||
            'Rol:' || 
            (CASE 
                WHEN ROL.ESTADO = 1 THEN 'Activo'
                WHEN ROL.ESTADO = 0 THEN 'Inactivo'
                ELSE 'Desconocido'
            END ) || 
            CHR(13) ||
            'Usuario Rol:' || 
            (CASE 
                WHEN UR.ESTADO = 1 THEN 'Activo'
                WHEN UR.ESTADO = 0 THEN 'Inactivo'
                ELSE 'Desconocido'
            END ) || 
            CHR(13) ||
            'Fecha Desde:'|| 
            TO_CHAR(UR.FECHA_DESDE, 'DD/MM/YYYY') ||
            CHR(13) ||
            'Fecha Hasta:'|| 
            TO_CHAR(UR.FECHA_HASTA, 'DD/MM/YYYY') 
            || CHR(13), '') WITHIN GROUP (ORDER BY ROL.NOMBRE) AS ROLES
            
    FROM SAS.ROL ROL
    INNER JOIN SAS.USUARIO_ROL      UR      ON (UR.ROL = ROL.ID_ROL)
    INNER JOIN SAS.USUARIO          US      ON (UR.USUARIO = US.ID_USUARIO)
    INNER JOIN PGE.PERSONA_USUARIO  PEUS    ON (PEUS.USUARIO = US.ID_USUARIO)
    INNER JOIN PERSONA              PER     ON (PEUS.PERSONA = PER.ID_PERSONA)
    INNER JOIN PERSONA_FISICA       PF      ON (PF.PERSONA = PER.ID_PERSONA)
    INNER JOIN FUNCIONARIO          FUNC    ON (FUNC.PERSONA_FISICA = PF.ID_PERSONA_FISICA)
    INNER JOIN RHU.NOMBRAMIENTO     NOM     ON (NOM.FUNCIONARIO = FUNC.ID_FUNCIONARIO)
    INNER JOIN RHU.PLAZA            PLA     ON (NOM.PLAZA = PLA.ID_PLAZA)
    INNER JOIN EPF.UNIDAD_EJECUTORA UE      ON (PLA.UNIDAD_EJECUTORA = UE.ID_UNIDAD_EJECUTORA)
    WHERE 
        ROL.NOMBRE LIKE '%PPI%' AND ROL.NOMBRE NOT LIKE '%ACL%' 
        AND NOM.ESTADO_NOMBRAMIENTO = 10 
        AND (ue.id_unidad_ejecutora = :unidadEjecutora OR :unidadEjecutora IS NULL OR ue.id_unidad_ejecutora IS NULL)
    GROUP BY PER.IDENTIFICACION, PER.NOMBRE,UE.NOMBRE
    ORDER BY UE.NOMBRE,PER.IDENTIFICACION,PER.NOMBRE 
)

, 

-- *******************************************************
-- CONSULTA: HERENCIA DE GRUPO ROLES
-- *******************************************************
RESULTADO_HERENCIA AS (

   
    SELECT 
        *
    FROM ( 
        SELECT
            CONNECT_BY_ROOT GR1.ID_GRUPO_ROL as ID_GRUPO_ROL,
            CONNECT_BY_ROOT GR1.NOMBRE AS GRUPO_RAIZ,
            SUBSTR(SYS_CONNECT_BY_PATH( GR2.NOMBRE, ' > '), INSTR(SYS_CONNECT_BY_PATH(GR2.NOMBRE, ' > '), '> ') + 2) AS JERARQUIA,
            (
                SELECT 
                    LISTAGG(ROL.NOMBRE || CHR(13), '') WITHIN GROUP (ORDER BY ROL.NOMBRE) AS ROLES
                            
                FROM SAS.ROL ROL
                INNER JOIN SAS.DETALLE_GRUPO_ROL DGR ON (DGR.ROL = ROL.ID_ROL)
                WHERE DGR.GRUPO_ROL= GR2.ID_GRUPO_ROL 
            ) AS ROLES
            FROM SAS.DETALLE_GRUPO_ROL DGR
            JOIN GRUPO_ROL GR1 ON DGR.GRUPO_ROL = GR1.ID_GRUPO_ROL
            JOIN GRUPO_ROL GR2 ON DGR.grupo = GR2.ID_GRUPO_ROL
            WHERE GR2.NOMBRE LIKE '%PPI%' AND GR2.NOMBRE NOT LIKE '%ACL%' 
            CONNECT BY NOCYCLE PRIOR GR2.ID_GRUPO_ROL = DGR.GRUPO_ROL
    ) RESULTADOS_JERARQUIA
    WHERE ROLES IS NOT NULL

)
SELECT
    UGR.IDENTIFICACION,
    UGR.PERSONA,
    UGR.UNIDAD_EJECUTORA,
    UGR.GRUPO_ROL AS GRUPO_ROL,
    HERENCIA.JERARQUIA AS JERARQUIA,
    HERENCIA.ROLES
FROM RESULTADO_HERENCIA HERENCIA


JOIN 

-- *******************************************************
-- CONSULTA: GRUPO ROLES
-- *******************************************************
(
    SELECT 
        NOM.FUNCIONARIO,
        PER.IDENTIFICACION AS IDENTIFICACION,
        PER.NOMBRE AS PERSONA,
        UE.NOMBRE AS UNIDAD_EJECUTORA,
        GR.ID_GRUPO_ROL AS ID_GRUPO_ROL,
        '--------------------------------------------' ||
        CHR(13) ||
        GR.NOMBRE || 
        CHR(13) ||
        '--------------------------------------------' ||
        CHR(13) ||
        'Grupo Rol:' || 
        (CASE 
            WHEN GR.ACTIVO = 1 THEN 'Activo'
            WHEN GR.ACTIVO = 0 THEN 'Inactivo'
            ELSE 'Desconocido'
        END ) || 
        CHR(13) ||
        'Usuario Grupo Rol:' || 
        (CASE 
            WHEN UGR.ACTIVO = 1 THEN 'Activo'
            WHEN UGR.ACTIVO = 0 THEN 'Inactivo'
            ELSE 'Desconocido'
        END ) || 
        CHR(13) ||
        'Fecha Desde:'|| 
        TO_CHAR(UGR.FECHA_DESDE, 'DD/MM/YYYY') ||
        CHR(13) ||
        'Fecha Hasta:'|| 
        TO_CHAR(UGR.FECHA_HASTA, 'DD/MM/YYYY') 
        || CHR(13) AS GRUPO_ROL,
        UGR.GRUPO_ROL AS UGR_GRUPO_ROL
    FROM SAS.USUARIO_GRUPO_ROL UGR
    INNER JOIN SAS.GRUPO_ROL GR ON (GR.ID_GRUPO_ROL = UGR.GRUPO_ROL)
    INNER JOIN SAS.USUARIO US ON (US.ID_USUARIO = UGR.USUARIO)
    INNER JOIN PGE.PERSONA_USUARIO PEUS ON (PEUS.USUARIO = US.ID_USUARIO)
    INNER JOIN PERSONA PER ON (PEUS.PERSONA = PER.ID_PERSONA)
    INNER JOIN PERSONA_FISICA       PF      ON (PF.PERSONA = PER.ID_PERSONA)
    INNER JOIN FUNCIONARIO          FUNC    ON (FUNC.PERSONA_FISICA = PF.ID_PERSONA_FISICA)
    INNER JOIN RHU.NOMBRAMIENTO     NOM     ON (NOM.FUNCIONARIO = FUNC.ID_FUNCIONARIO AND NOM.ESTADO_NOMBRAMIENTO = 10)
    INNER JOIN RHU.PLAZA            PLA     ON NOM.PLAZA = PLA.ID_PLAZA
    INNER JOIN EPF.UNIDAD_EJECUTORA UE      ON PLA.UNIDAD_EJECUTORA = UE.ID_UNIDAD_EJECUTORA
    
    WHERE UGR.GRUPO_ROL IN (
        SELECT 
            DISTINCT DGR.GRUPO_ROL AS GRUPO_ROL
        FROM DETALLE_GRUPO_ROL DGR
        CONNECT BY PRIOR DGR.GRUPO_ROL = DGR.grupo
        START WITH DGR.ROL IN (
            SELECT 
                ROL.ID_ROL
            FROM SAS.ROL ROL 
            WHERE 
                ROL.NOMBRE LIKE '%PPI%' 
                AND ROL.NOMBRE NOT LIKE '%ACL%'
        )
    ) 
    AND (ue.id_unidad_ejecutora = :unidadEjecutora OR :unidadEjecutora IS NULL OR ue.id_unidad_ejecutora IS NULL)
    ORDER BY UE.NOMBRE,PER.IDENTIFICACION,PER.NOMBRE 
) UGR ON HERENCIA.ID_GRUPO_ROL = UGR.UGR_GRUPO_ROL


UNION ALL

-- *******************************************************
-- CONSULTA: ELIMINA REGISTROS CON ROL NULL
-- *******************************************************
SELECT 
* 
FROM RESULTADO_ROLES_DIRECTOS
WHERE ROLES IS NOT NULL
ORDER BY UNIDAD_EJECUTORA,IDENTIFICACION,PERSONA;

Varias recursivas

WITH RESULTADO (GRUPO_ROL, GRUPO, NIVEL) AS (

  -- REGITRO INICIAL
  SELECT 
    GRUPO_ROL,
    GRUPO,
    1 AS NIVEL
  FROM SAS.DETALLE_GRUPO_ROL
  WHERE GRUPO_ROL = 38

  UNION ALL
  
  -- REGISTRO RECURSIVO
  SELECT 
    HIJO.GRUPO_ROL, 
    HIJO.GRUPO, 
    PARENT.NIVEL + 1 AS NIVEL
  FROM RESULTADO PARENT
  JOIN SAS.DETALLE_GRUPO_ROL HIJO ON PARENT.GRUPO = HIJO.GRUPO_ROL
  
)
SELECT 
    NIVEL,
    GRUPO AS ID_GRUPO,
    GR.NOMBRE
    --ROL
    --GR.DESCRIPCION,
    --GR.ACTIVO
    --DGR.ROL
    
FROM RESULTADO
INNER JOIN SAS.GRUPO_ROL GR ON (GR.ID_GRUPO_ROL = GRUPO)
ORDER BY NIVEL;





WITH RESULTADO (GRUPO_ROL, GRUPO, NIVEL) AS (
  -- REGISTRO INICIAL
  SELECT 
    GRUPO_ROL,
    GRUPO,
    1 AS NIVEL
  FROM SAS.DETALLE_GRUPO_ROL
  WHERE GRUPO_ROL = 38

  UNION ALL
  
  -- REGISTRO RECURSIVO
  SELECT 
    HIJO.GRUPO_ROL, 
    HIJO.GRUPO, 
    PARENT.NIVEL + 1 AS NIVEL
  FROM RESULTADO PARENT
  JOIN SAS.DETALLE_GRUPO_ROL HIJO ON PARENT.GRUPO = HIJO.GRUPO_ROL
)
SELECT 
    *
FROM RESULTADO
ORDER BY NIVEL;



WITH RESULTADO (GRUPO_ROL, GRUPO, NIVEL) AS (
  -- REGISTRO INICIAL
  SELECT 
    GRUPO_ROL,
    GRUPO,
    1 AS NIVEL
  FROM SAS.DETALLE_GRUPO_ROL
  WHERE GRUPO_ROL = 38

  UNION ALL
  
  -- REGISTRO RECURSIVO
  SELECT 
    HIJO.GRUPO_ROL, 
    HIJO.GRUPO, 
    PARENT.NIVEL + 1 AS NIVEL
  FROM RESULTADO PARENT
  JOIN SAS.DETALLE_GRUPO_ROL HIJO ON PARENT.GRUPO = HIJO.GRUPO_ROL
)
SELECT 
    GRUPO_ROL AS ID_GRUPO_PADRE,
    LISTAGG(GR.NOMBRE, ' > ') WITHIN GROUP (ORDER BY NIVEL) AS JERARQUIA
FROM RESULTADO
INNER JOIN SAS.GRUPO_ROL GR ON (GR.ID_GRUPO_ROL = GRUPO)
GROUP BY GRUPO_ROL
ORDER BY ID_GRUPO_PADRE;