IF OBJECT_ID('dbo.sp_fco_calcula_totales_ala_2006') IS NOT NULL
    DROP PROCEDURE dbo.sp_fco_calcula_totales_ala_2006
GO
CREATE PROCEDURE dbo.sp_fco_calcula_totales_ala_2006(@pch_compania char(4))
/*******************************************************/
/***     FCO Módulo Fiscal Colombiano                ***/
/***        Archivos para la DIAN                    ***/
/*** Este procedimiento recalcula los totales        ***/
/*** que no son calculados en la planilla integrada: ***/
/*** Totales por Pensiones, Cajas, SENA, ICBF        ***/
/*** Autor: Juan Carlos Leguizamón                   ***/
/*** Fecha: 25-ABR-07                                ***/
/*** Versión especial para el año 2006               ***/
/*** Mezcla de autoliquidación vieja con planilla    ***/
/*******************************************************/
AS
BEGIN
    DECLARE @i tinyint
    DECLARE @ini tinyint

    SELECT @ini = 
        Case @pch_compania When 'DETE' Then 9
                           When 'GRAS' Then 9
                           When 'GRAC' Then 10
                           When 'JABC' Then 13
        End

    DELETE fco_integrada_rt03 WHERE compania = @pch_compania AND anio = 2006
    DELETE fco_integrada_rt04 WHERE compania = @pch_compania AND anio = 2006 AND mes = 1
    DELETE fco_integrada_rt05 WHERE compania = @pch_compania AND anio = 2006 AND mes = 1
    DELETE fco_integrada_rt06 WHERE compania = @pch_compania AND anio = 2006
    DELETE fco_integrada_rt07 WHERE compania = @pch_compania AND anio = 2006
    DELETE fco_integrada_rt08 WHERE compania = @pch_compania AND anio = 2006

    SET @i = @ini
    WHILE @i < 13
    BEGIN
        /* PENSIONES */
        SELECT distinct cod_AFP
        INTO #afp
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = 2006
          AND mes = @i

        SELECT cod_AFP, IDENTITY(tinyint, 1, 1) secuencia
        INTO #afp2
        FROM #afp
        INNER JOIN fco_administradoras a ON #afp.cod_AFP = codigo
        WHERE a.tipo = 'AFP' and IsNull(RTrim(a.cod_super), '') <> ''

        DROP TABLE #afp

        INSERT INTO fco_integrada_rt03
        (compania, anio, mes, sucursal, cod_AFP, secuencia, nit, dv, suma_aportes, suma_voluntarios, suma_voluntarios_cia, suma_FSP_sol, suma_FSP_sub, 
         dias_mora, int_mora_aportes, int_mora_FSP_sol, int_mora_FSP_sub, total_afiliados)
        SELECT compania, anio, mes, sucursal, a.cod_AFP, b.secuencia, c.registro_fiscal, c.digito_verificacion, SUM(aporte_AFP), 
        SUM(voluntario_AFP), SUM(voluntario_AFP_cia), SUM(aporte_FSP), SUM(aporte_FSP_subsistencia), 0, 0, 0, 0, COUNT(distinct trabajador)
        FROM fco_integrada_det a
        INNER JOIN #afp2 b ON b.cod_AFP = a.cod_AFP
        INNER JOIN fco_administradoras c ON a.cod_AFP = c.codigo 
        WHERE a.compania = @pch_compania
          AND a.anio = 2006
          AND a.mes = @i
          AND c.tipo = 'AFP'
        GROUP BY compania, anio, mes, sucursal, a.cod_AFP, b.secuencia, c.registro_fiscal, c.digito_verificacion

        DROP TABLE #afp2

        /* CAJAS DE COMPENSACION */
        SELECT distinct cod_CCF
        INTO #CCF
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = 2006
          AND mes = @i

        SELECT cod_CCF, IDENTITY(tinyint, 1, 1) secuencia
        INTO #CCF2
        FROM #CCF
        INNER JOIN fco_administradoras a ON #CCF.cod_CCF = codigo
        WHERE a.tipo = 'CCF' and IsNull(RTrim(a.cod_super), '') <> ''

        DROP TABLE #CCF

        INSERT INTO fco_integrada_rt06
        (compania, anio, mes, sucursal, cod_CCF, secuencia, nit, dv, suma_aportes, dias_mora, int_mora, total_afiliados)
        SELECT compania, anio, mes, sucursal, a.cod_CCF, b.secuencia, c.registro_fiscal, c.digito_verificacion, 
               SUM(aporte_CCF), 0, 0, COUNT(trabajador)
        FROM fco_integrada_det a
        INNER JOIN #CCF2 b ON b.cod_CCF = a.cod_CCF
        INNER JOIN fco_administradoras c ON a.cod_CCF = c.codigo 
        WHERE a.compania = @pch_compania
          AND a.anio = 2006
          AND a.mes = @i
          AND c.tipo = 'CCF'
        GROUP BY compania, anio, mes, sucursal, a.cod_CCF, b.secuencia, c.registro_fiscal, c.digito_verificacion

        DROP TABLE #CCF2

        /* SENA */
        INSERT INTO fco_integrada_rt07
        (compania, anio, mes, sucursal, secuencia, nit, dv, valor, dias_mora, int_mora, nro_personas)
        SELECT compania, anio, mes, sucursal, 1, '', '', SUM(aporte_SENA), 0, 0, COUNT(trabajador)
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = 2006
          AND mes = @i
        GROUP BY compania, anio, mes, sucursal

        /* ICBF */
        INSERT INTO fco_integrada_rt08
        (compania, anio, mes, sucursal, secuencia, nit, dv, valor, dias_mora, int_mora, nro_personas)
        SELECT compania, anio, mes, sucursal, 1, '', '', SUM(aporte_ICBF), 0, 0, COUNT(trabajador)
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = 2006
          AND mes = @i
        GROUP BY compania, anio, mes, sucursal

        SET @i = @i + 1
    END

    -- HALLAR LO QUE FALTA, LO QUE SE HIZO EN AUTOLIQUIDACION VIEJA
    EXEC dtsp_crea_mm_dian_2006 @pch_compania
END
GO
GRANT EXECUTE ON dbo.sp_fco_calcula_totales_ala_2006 TO PUBLIC
GO

