Reporte jerarquico usando Interactive Report

-- Crear la tabla para almacenar datos jerárquicos
CREATE TABLE demo_hierarchical_data (
    id                NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    parent_id         NUMBER,
    nombre            VARCHAR2(100) NOT NULL,
    descripcion       VARCHAR2(4000),
    tipo              VARCHAR2(50),
    fecha_creacion    DATE DEFAULT SYSDATE,
    estado            VARCHAR2(20) DEFAULT 'ACTIVO',
    orden             NUMBER,
    creado_por        VARCHAR2(50) DEFAULT USER,
    
    -- Restricciones
    CONSTRAINT demo_hier_parent_fk FOREIGN KEY (parent_id) 
        REFERENCES demo_hierarchical_data(id) ON DELETE CASCADE,
    CONSTRAINT demo_hier_check_estado 
        CHECK (estado IN ('ACTIVO', 'INACTIVO'))
);

CREATE INDEX demo_hier_parent_idx ON demo_hierarchical_data(parent_id);
CREATE SEQUENCE demo_hier_seq START WITH 100 INCREMENT BY 1;

CREATE OR REPLACE VIEW v_demo_hierarchical_report AS
SELECT 
    id,
    parent_id,
    nombre,
    CASE
        WHEN LEVEL = 1 THEN nombre
        WHEN CONNECT_BY_ISLEAF = 1 THEN 
            LPAD(' ', (LEVEL-1)*3, ' ') || '└─ ' || nombre
        ELSE 
            LPAD(' ', (LEVEL-1)*3, ' ') || '├─ ' || nombre
    END AS nombre_jerarquico,
    
    -- Versión con HTML para mejor visualización
    CASE 
        WHEN CONNECT_BY_ISLEAF = 1 THEN 
            '<div class="hierarchy-item level-' || LEVEL || ' leaf-node">' ||
            '<span class="fa fa-file-o" style="margin-right:5px;"></span>' || nombre || '</div>'
        ELSE 
            '<div class="hierarchy-item level-' || LEVEL || ' parent-node">' ||
            '<span class="fa fa-folder-o" style="margin-right:5px;"></span>' || nombre || '</div>'
    END AS nombre_html,
    
    -- Ruta completa
    SYS_CONNECT_BY_PATH(nombre, ' > ') AS path_completo,
    
    tipo,
    descripcion,
    fecha_creacion,
    estado,
    orden,
    creado_por,
    LEVEL AS nivel_jerarquia,
    CONNECT_BY_ISLEAF AS es_hoja,
    CONNECT_BY_ROOT id AS id_raiz,
    CONNECT_BY_ROOT nombre AS nombre_raiz
FROM 
    demo_hierarchical_data
START WITH 
    parent_id IS NULL
CONNECT BY PRIOR 
    id = parent_id
ORDER SIBLINGS BY 
    orden, nombre;

SELECT 
    id,
    parent_id,
    nombre_html AS nombre,
    descripcion,
    fecha_creacion,
    estado,
    nivel_jerarquia
FROM 
    v_demo_hierarchical_report
ORDER BY 
    path_completo

.hierarchy-item {
  white-space: nowrap;
  display: flex;
  align-items: center;
}

.hierarchy-item.level-1 { margin-left: 0; }
.hierarchy-item.level-2 { margin-left: 20px; }
.hierarchy-item.level-3 { margin-left: 40px; }
.hierarchy-item.level-4 { margin-left: 60px; }
.hierarchy-item.level-5 { margin-left: 80px; }
.hierarchy-item.level-6 { margin-left: 100px; }

.hierarchy-item.parent-node span.fa-folder-o { color: #ffa500; }
.hierarchy-item.leaf-node span.fa-file-o { color: #6f6f6f; }