{"id":15440,"date":"2023-08-03T15:32:50","date_gmt":"2023-08-03T21:32:50","guid":{"rendered":"https:\/\/sada.services\/?p=15440"},"modified":"2023-08-08T11:33:13","modified_gmt":"2023-08-08T17:33:13","slug":"oracle-sigesa-jerarquia-de-grupos-roles-y-roles-consulta-recursiva","status":"publish","type":"post","link":"https:\/\/sada.services\/?p=15440","title":{"rendered":"ORACLE -SIGESA: jerarquia de grupos roles y roles consulta recursiva"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>SELECT\n    CONNECT_BY_ROOT GR1.ID_GRUPO_ROL as ID_GRUPO_ROL,\n    CONNECT_BY_ROOT GR1.NOMBRE AS GRUPO_RAIZ,\n    SUBSTR(SYS_CONNECT_BY_PATH( GR2.ID_GRUPO_ROL || '.' || GR2.NOMBRE, ' &gt; '), INSTR(SYS_CONNECT_BY_PATH(GR2.NOMBRE, ' &gt; '), '&gt; ') + 2) AS JERARQUIA,\n    (\n        SELECT \n            LISTAGG(ROL.ID_ROL || '.' || ROL.NOMBRE || CHR(13), ',') WITHIN GROUP (ORDER BY ROL.NOMBRE) AS ROLES\n            \n        FROM SAS.ROL ROL\n        INNER JOIN SAS.DETALLE_GRUPO_ROL DGR ON (DGR.ROL = ROL.ID_ROL)\n        WHERE DGR.GRUPO_ROL= GR2.ID_GRUPO_ROL\n    ) AS ROLES\nFROM SAS.DETALLE_GRUPO_ROL DGR\nJOIN GRUPO_ROL GR1 ON DGR.GRUPO_ROL = GR1.ID_GRUPO_ROL\nJOIN GRUPO_ROL GR2 ON DGR.grupo = GR2.ID_GRUPO_ROL\nWHERE GR2.NOMBRE LIKE '%PPI%' AND GR2.NOMBRE NOT LIKE '%ACL%'\nSTART WITH GR1.ID_GRUPO_ROL = 39\nCONNECT BY NOCYCLE PRIOR GR2.ID_GRUPO_ROL = DGR.GRUPO_ROL\nORDER BY CONNECT_BY_ROOT GR1.ID_GRUPO_ROL, JERARQUIA;<\/code><\/pre>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Consulta final<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>WITH RESULTADO_ROLES_DIRECTOS AS (\n\n    -- *******************************************************\n    -- CONSULTA: ROLES ASIGNADOS DIRECTAMENTE AL USURIO\n    -- *******************************************************\n    SELECT \n        PER.IDENTIFICACION  AS IDENTIFICACION,\n        PER.NOMBRE          AS PERSONA,\n        UE.NOMBRE           AS UNIDAD_EJECUTORA,\n        NULL                AS GRUPO_ROL,\n        NULL                AS JERARQUIA,\n        LISTAGG(\n        \n            '-------------------------------' ||\n            CHR(13) ||\n            ROL.NOMBRE || \n            CHR(13) ||\n            '-------------------------------' ||\n            CHR(13) ||\n            'Rol:' || \n            (CASE \n                WHEN ROL.ESTADO = 1 THEN 'Activo'\n                WHEN ROL.ESTADO = 0 THEN 'Inactivo'\n                ELSE 'Desconocido'\n            END ) || \n            CHR(13) ||\n            'Usuario Rol:' || \n            (CASE \n                WHEN UR.ESTADO = 1 THEN 'Activo'\n                WHEN UR.ESTADO = 0 THEN 'Inactivo'\n                ELSE 'Desconocido'\n            END ) || \n            CHR(13) ||\n            'Fecha Desde:'|| \n            TO_CHAR(UR.FECHA_DESDE, 'DD\/MM\/YYYY') ||\n            CHR(13) ||\n            'Fecha Hasta:'|| \n            TO_CHAR(UR.FECHA_HASTA, 'DD\/MM\/YYYY') \n            || CHR(13), '') WITHIN GROUP (ORDER BY ROL.NOMBRE) AS ROLES\n            \n    FROM SAS.ROL ROL\n    INNER JOIN SAS.USUARIO_ROL      UR      ON (UR.ROL = ROL.ID_ROL)\n    INNER JOIN SAS.USUARIO          US      ON (UR.USUARIO = US.ID_USUARIO)\n    INNER JOIN PGE.PERSONA_USUARIO  PEUS    ON (PEUS.USUARIO = US.ID_USUARIO)\n    INNER JOIN PERSONA              PER     ON (PEUS.PERSONA = PER.ID_PERSONA)\n    INNER JOIN PERSONA_FISICA       PF      ON (PF.PERSONA = PER.ID_PERSONA)\n    INNER JOIN FUNCIONARIO          FUNC    ON (FUNC.PERSONA_FISICA = PF.ID_PERSONA_FISICA)\n    INNER JOIN RHU.NOMBRAMIENTO     NOM     ON (NOM.FUNCIONARIO = FUNC.ID_FUNCIONARIO)\n    INNER JOIN RHU.PLAZA            PLA     ON (NOM.PLAZA = PLA.ID_PLAZA)\n    INNER JOIN EPF.UNIDAD_EJECUTORA UE      ON (PLA.UNIDAD_EJECUTORA = UE.ID_UNIDAD_EJECUTORA)\n    WHERE \n        ROL.NOMBRE LIKE '%PPI%' AND ROL.NOMBRE NOT LIKE '%ACL%' \n        AND NOM.ESTADO_NOMBRAMIENTO = 10 \n        AND (ue.id_unidad_ejecutora = :unidadEjecutora OR :unidadEjecutora IS NULL OR ue.id_unidad_ejecutora IS NULL)\n    GROUP BY PER.IDENTIFICACION, PER.NOMBRE,UE.NOMBRE\n    ORDER BY UE.NOMBRE,PER.IDENTIFICACION,PER.NOMBRE \n)\n\n, \n\n-- *******************************************************\n-- CONSULTA: HERENCIA DE GRUPO ROLES\n-- *******************************************************\nRESULTADO_HERENCIA AS (\n\n   \n    SELECT \n        *\n    FROM ( \n        SELECT\n            CONNECT_BY_ROOT GR1.ID_GRUPO_ROL as ID_GRUPO_ROL,\n            CONNECT_BY_ROOT GR1.NOMBRE AS GRUPO_RAIZ,\n            SUBSTR(SYS_CONNECT_BY_PATH( GR2.NOMBRE, ' > '), INSTR(SYS_CONNECT_BY_PATH(GR2.NOMBRE, ' > '), '> ') + 2) AS JERARQUIA,\n            (\n                SELECT \n                    LISTAGG(ROL.NOMBRE || CHR(13), '') WITHIN GROUP (ORDER BY ROL.NOMBRE) AS ROLES\n                            \n                FROM SAS.ROL ROL\n                INNER JOIN SAS.DETALLE_GRUPO_ROL DGR ON (DGR.ROL = ROL.ID_ROL)\n                WHERE DGR.GRUPO_ROL= GR2.ID_GRUPO_ROL \n            ) AS ROLES\n            FROM SAS.DETALLE_GRUPO_ROL DGR\n            JOIN GRUPO_ROL GR1 ON DGR.GRUPO_ROL = GR1.ID_GRUPO_ROL\n            JOIN GRUPO_ROL GR2 ON DGR.grupo = GR2.ID_GRUPO_ROL\n            WHERE GR2.NOMBRE LIKE '%PPI%' AND GR2.NOMBRE NOT LIKE '%ACL%' \n            CONNECT BY NOCYCLE PRIOR GR2.ID_GRUPO_ROL = DGR.GRUPO_ROL\n    ) RESULTADOS_JERARQUIA\n    WHERE ROLES IS NOT NULL\n\n)\nSELECT\n    UGR.IDENTIFICACION,\n    UGR.PERSONA,\n    UGR.UNIDAD_EJECUTORA,\n    UGR.GRUPO_ROL AS GRUPO_ROL,\n    HERENCIA.JERARQUIA AS JERARQUIA,\n    HERENCIA.ROLES\nFROM RESULTADO_HERENCIA HERENCIA\n\n\nJOIN \n\n-- *******************************************************\n-- CONSULTA: GRUPO ROLES\n-- *******************************************************\n(\n    SELECT \n        NOM.FUNCIONARIO,\n        PER.IDENTIFICACION AS IDENTIFICACION,\n        PER.NOMBRE AS PERSONA,\n        UE.NOMBRE AS UNIDAD_EJECUTORA,\n        GR.ID_GRUPO_ROL AS ID_GRUPO_ROL,\n        '--------------------------------------------' ||\n        CHR(13) ||\n        GR.NOMBRE || \n        CHR(13) ||\n        '--------------------------------------------' ||\n        CHR(13) ||\n        'Grupo Rol:' || \n        (CASE \n            WHEN GR.ACTIVO = 1 THEN 'Activo'\n            WHEN GR.ACTIVO = 0 THEN 'Inactivo'\n            ELSE 'Desconocido'\n        END ) || \n        CHR(13) ||\n        'Usuario Grupo Rol:' || \n        (CASE \n            WHEN UGR.ACTIVO = 1 THEN 'Activo'\n            WHEN UGR.ACTIVO = 0 THEN 'Inactivo'\n            ELSE 'Desconocido'\n        END ) || \n        CHR(13) ||\n        'Fecha Desde:'|| \n        TO_CHAR(UGR.FECHA_DESDE, 'DD\/MM\/YYYY') ||\n        CHR(13) ||\n        'Fecha Hasta:'|| \n        TO_CHAR(UGR.FECHA_HASTA, 'DD\/MM\/YYYY') \n        || CHR(13) AS GRUPO_ROL,\n        UGR.GRUPO_ROL AS UGR_GRUPO_ROL\n    FROM SAS.USUARIO_GRUPO_ROL UGR\n    INNER JOIN SAS.GRUPO_ROL GR ON (GR.ID_GRUPO_ROL = UGR.GRUPO_ROL)\n    INNER JOIN SAS.USUARIO US ON (US.ID_USUARIO = UGR.USUARIO)\n    INNER JOIN PGE.PERSONA_USUARIO PEUS ON (PEUS.USUARIO = US.ID_USUARIO)\n    INNER JOIN PERSONA PER ON (PEUS.PERSONA = PER.ID_PERSONA)\n    INNER JOIN PERSONA_FISICA       PF      ON (PF.PERSONA = PER.ID_PERSONA)\n    INNER JOIN FUNCIONARIO          FUNC    ON (FUNC.PERSONA_FISICA = PF.ID_PERSONA_FISICA)\n    INNER JOIN RHU.NOMBRAMIENTO     NOM     ON (NOM.FUNCIONARIO = FUNC.ID_FUNCIONARIO AND NOM.ESTADO_NOMBRAMIENTO = 10)\n    INNER JOIN RHU.PLAZA            PLA     ON NOM.PLAZA = PLA.ID_PLAZA\n    INNER JOIN EPF.UNIDAD_EJECUTORA UE      ON PLA.UNIDAD_EJECUTORA = UE.ID_UNIDAD_EJECUTORA\n    \n    WHERE UGR.GRUPO_ROL IN (\n        SELECT \n            DISTINCT DGR.GRUPO_ROL AS GRUPO_ROL\n        FROM DETALLE_GRUPO_ROL DGR\n        CONNECT BY PRIOR DGR.GRUPO_ROL = DGR.grupo\n        START WITH DGR.ROL IN (\n            SELECT \n                ROL.ID_ROL\n            FROM SAS.ROL ROL \n            WHERE \n                ROL.NOMBRE LIKE '%PPI%' \n                AND ROL.NOMBRE NOT LIKE '%ACL%'\n        )\n    ) \n    AND (ue.id_unidad_ejecutora = :unidadEjecutora OR :unidadEjecutora IS NULL OR ue.id_unidad_ejecutora IS NULL)\n    ORDER BY UE.NOMBRE,PER.IDENTIFICACION,PER.NOMBRE \n) UGR ON HERENCIA.ID_GRUPO_ROL = UGR.UGR_GRUPO_ROL\n\n\nUNION ALL\n\n-- *******************************************************\n-- CONSULTA: ELIMINA REGISTROS CON ROL NULL\n-- *******************************************************\nSELECT \n* \nFROM RESULTADO_ROLES_DIRECTOS\nWHERE ROLES IS NOT NULL\nORDER BY UNIDAD_EJECUTORA,IDENTIFICACION,PERSONA;<\/code><\/pre>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Varias recursivas<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>WITH RESULTADO (GRUPO_ROL, GRUPO, NIVEL) AS (\n\n  -- REGITRO INICIAL\n  SELECT \n    GRUPO_ROL,\n    GRUPO,\n    1 AS NIVEL\n  FROM SAS.DETALLE_GRUPO_ROL\n  WHERE GRUPO_ROL = 38\n\n  UNION ALL\n  \n  -- REGISTRO RECURSIVO\n  SELECT \n    HIJO.GRUPO_ROL, \n    HIJO.GRUPO, \n    PARENT.NIVEL + 1 AS NIVEL\n  FROM RESULTADO PARENT\n  JOIN SAS.DETALLE_GRUPO_ROL HIJO ON PARENT.GRUPO = HIJO.GRUPO_ROL\n  \n)\nSELECT \n    NIVEL,\n    GRUPO AS ID_GRUPO,\n    GR.NOMBRE\n    --ROL\n    --GR.DESCRIPCION,\n    --GR.ACTIVO\n    --DGR.ROL\n    \nFROM RESULTADO\nINNER JOIN SAS.GRUPO_ROL GR ON (GR.ID_GRUPO_ROL = GRUPO)\nORDER BY NIVEL;\n\n\n\n\n\nWITH RESULTADO (GRUPO_ROL, GRUPO, NIVEL) AS (\n  -- REGISTRO INICIAL\n  SELECT \n    GRUPO_ROL,\n    GRUPO,\n    1 AS NIVEL\n  FROM SAS.DETALLE_GRUPO_ROL\n  WHERE GRUPO_ROL = 38\n\n  UNION ALL\n  \n  -- REGISTRO RECURSIVO\n  SELECT \n    HIJO.GRUPO_ROL, \n    HIJO.GRUPO, \n    PARENT.NIVEL + 1 AS NIVEL\n  FROM RESULTADO PARENT\n  JOIN SAS.DETALLE_GRUPO_ROL HIJO ON PARENT.GRUPO = HIJO.GRUPO_ROL\n)\nSELECT \n    *\nFROM RESULTADO\nORDER BY NIVEL;\n\n\n\nWITH RESULTADO (GRUPO_ROL, GRUPO, NIVEL) AS (\n  -- REGISTRO INICIAL\n  SELECT \n    GRUPO_ROL,\n    GRUPO,\n    1 AS NIVEL\n  FROM SAS.DETALLE_GRUPO_ROL\n  WHERE GRUPO_ROL = 38\n\n  UNION ALL\n  \n  -- REGISTRO RECURSIVO\n  SELECT \n    HIJO.GRUPO_ROL, \n    HIJO.GRUPO, \n    PARENT.NIVEL + 1 AS NIVEL\n  FROM RESULTADO PARENT\n  JOIN SAS.DETALLE_GRUPO_ROL HIJO ON PARENT.GRUPO = HIJO.GRUPO_ROL\n)\nSELECT \n    GRUPO_ROL AS ID_GRUPO_PADRE,\n    LISTAGG(GR.NOMBRE, ' > ') WITHIN GROUP (ORDER BY NIVEL) AS JERARQUIA\nFROM RESULTADO\nINNER JOIN SAS.GRUPO_ROL GR ON (GR.ID_GRUPO_ROL = GRUPO)\nGROUP BY GRUPO_ROL\nORDER BY ID_GRUPO_PADRE;\n\n\n\n\n<\/code><\/pre>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Consulta final Varias recursivas<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[519,443],"tags":[548],"class_list":["post-15440","post","type-post","status-publish","format-standard","hentry","category-oracle","category-sigesa","tag-recursiva"],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":6}},"_links":{"self":[{"href":"https:\/\/sada.services\/index.php?rest_route=\/wp\/v2\/posts\/15440","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sada.services\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sada.services\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sada.services\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sada.services\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=15440"}],"version-history":[{"count":3,"href":"https:\/\/sada.services\/index.php?rest_route=\/wp\/v2\/posts\/15440\/revisions"}],"predecessor-version":[{"id":15444,"href":"https:\/\/sada.services\/index.php?rest_route=\/wp\/v2\/posts\/15440\/revisions\/15444"}],"wp:attachment":[{"href":"https:\/\/sada.services\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=15440"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sada.services\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=15440"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sada.services\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=15440"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}