dbo.TareasDependientes

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE OR ALTER FUNCTION dbo.TareasDependientes(@informe_padre INT,@idTipoIforme INT,@tarea INT)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        LN_informe_design.idElem,
        informes.estado_informe,
        informes.ready_to_start,
        informes.informe,
        LN_informe_design.dependency_tree,
        (
            SELECT 
                COUNT(*) 
            FROM informes
            WHERE 
                informe_padre = @informe_padre 
                AND estado_informe NOT IN ('K','N') 
                AND idTarea IN (SELECT item FROM dbo.DelimitedSplit8K(LN_informe_design.dependency_tree,','))
        ) AS pend_aprobar
    FROM LN_informe_design 
        INNER JOIN informes ON ln_informe_design.idElem = informes.idTarea
    WHERE 
        idTipoInforme=@idTipoIforme 
        AND LN_informe_design.tipo_elem='T' 
        AND LN_informe_design.activo=1
        AND informes.informe_padre = @informe_padre
        AND idelem IN (
            SELECT DISTINCT 
                LN_informe_design.idElem
            FROM informes
                INNER JOIN informes iPPAL ON informes.informe_padre = iPPAL.informe
                INNER JOIN LN_informe_design ON (
                    LN_informe_design.idTipoInforme=iPPAL.id_tipo_informe 
                    AND LN_informe_design.tipo_elem='T' 
                    AND LN_informe_design.activo=1
                )
                CROSS APPLY dbo.DelimitedSplit8K(LN_informe_design.dependency_tree, ',') LISTOSPARAINICIAR 
            WHERE 
                informes.informe = @tarea 
                AND LISTOSPARAINICIAR.Item = informes.idtarea
        )
);

GO