IF OBJECT_ID('dbo.fn_fco_dian_creditos') IS NOT NULL
    DROP FUNCTION dbo.fn_fco_dian_creditos
GO
CREATE FUNCTION dbo.fn_fco_dian_creditos
(
    @pch_compania char(4),
    @psi_anio smallint,
    @pch_trabajador char(10)
)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE 
        @vdc_valor decimal(19,6),
        @vch_agr_cre char(3),
        @cch_folio char(10),
        @csi_concepto smallint,
        @csi_pago smallint,
        @vdc_saldo decimal(19,2),
        @vdt_fecha_venc smalldatetime,
        @vdt_fecha_pago smalldatetime,
        @vdc_amortizacion decimal(19,2),
        @vti_estatus tinyint

    SELECT @vch_agr_cre = dato FROM vw_fdt_parametros
    WHERE pais = 'COL' AND compania = @pch_compania AND parametro = 'otros' AND secuencia = 104

    DECLARE cr_creditos CURSOR FOR
    SELECT 
        A.folio, A.concepto, MAX(A.pago)
    FROM
        saldos_creditos A, definicion_credito B, creditos_personal C
    WHERE A.compania = @pch_compania
        AND A.trabajador = @pch_trabajador
        AND B.compania = A.compania
        AND B.concepto = A.concepto 
        AND B.cred_especial = 0
        AND A.compania = C.compania
        AND A.trabajador = C.trabajador         
        AND A.folio = C.folio
        AND A.concepto = C.concepto
        AND ((Convert(char(8), A.fecha_vencimiento, 112) <= RTrim(@psi_anio) + '1231') 
        OR (Convert(char(8), C.fecha_credito, 112) <= RTrim(@psi_anio) + '1231' AND
            Convert(char(8), A.fecha_vencimiento, 112) > RTrim(@psi_anio) + '1231' AND
            A.pago = 1))
        AND Convert(char(8), A.fecha_vencimiento, 112) > RTrim(@psi_anio) + '0101'            
        AND C.concepto IN 
        (SELECT concepto FROM rel_conceptos_agr 
         WHERE compania = @pch_compania AND agr_conceptos = @vch_agr_cre)
    GROUP BY
        A.folio, A.concepto
            
    OPEN cr_creditos
            
    FETCH NEXT FROM cr_creditos INTO @cch_folio, @csi_concepto, @csi_pago
            
    SET @vdc_valor = 0
    WHILE @@fetch_status = 0
    BEGIN
        SELECT 
            @vdc_saldo = saldo_capital,
            @vdc_amortizacion = amortizacion_capital,  
            @vti_estatus = estatus,
            @vdt_fecha_venc = fecha_vencimiento,
            @vdt_fecha_pago = fecha_pago
        FROM
            saldos_creditos
        WHERE             
            compania = @pch_compania 
            AND trabajador = @pch_trabajador
            AND folio = @cch_folio
            AND concepto = @csi_concepto 
            AND pago = @csi_pago  
            
       IF @vti_estatus IN (0, 2)   
            IF @csi_pago = 1   
            BEGIN  
                SET @vdc_valor = ISNULL(@vdc_valor, 0) + @vdc_saldo  

                FETCH NEXT FROM cr_creditos INTO @cch_folio, @csi_concepto, @csi_pago
            END  
            ELSE  
                SET @csi_pago = @csi_pago - 1  
        ELSE  
        BEGIN
            IF @vti_estatus = 1
                IF @vdt_fecha_venc > Convert(varchar, @psi_anio) + '1231'
                    SET @vdc_valor = ISNULL(@vdc_valor, 0) + @vdc_saldo
                ELSE
                    IF @vdt_fecha_pago > Convert(varchar, @psi_anio) + '1231'
                        SET @vdc_valor = ISNULL(@vdc_valor, 0) + @vdc_saldo
                    ELSE
                        SET @vdc_valor = ISNULL(@vdc_valor, 0) + @vdc_saldo - @vdc_amortizacion
        
            FETCH NEXT FROM cr_creditos INTO @cch_folio, @csi_concepto, @csi_pago
        END
            
    END
    
    CLOSE cr_creditos
    DEALLOCATE cr_creditos            

    RETURN IsNull(@vdc_valor, 0)
END
GO
GRANT EXECUTE ON fn_fco_dian_creditos TO PUBLIC
GO
