SET QUOTED_IDENTIFIER OFF
IF OBJECT_ID('dbo.sp_fco_crea_pension') IS NOT NULL
    DROP PROCEDURE sp_fco_crea_pension
GO
CREATE PROCEDURE sp_fco_crea_pension
(        
    @pch_compania char(4),
    @psi_anio smallint,
    @pti_mes tinyint,
    @pvc_SUC varchar(10),
    @pvc_FPO varchar(10),
    @pbi_generar_mm bit,
    @pbi_regenerar bit
)        
/**********************************************************************/
/*** Modulo:      Modulo Fiscal Colombiano Version FCO - ADAM V.3   ***/
/*** Descripcion: Crea tabla de autoliquidacion de aportes a Pensión***/
/*** Autor:       Juan Carlos Leguizamon                            ***/
/*** Fecha MF:    Julio 05 de 2001                                  ***/
/*** Fecha FCO:   Marzo 21 de 2006                                  ***/
/**********************************************************************/
AS        
DECLARE        
    @vch_acum_imp char(15),
    @vch_acum_tie char(15),
    
    @vch_periodo char(8),        
    @vch_per_ant char(8), /* JAS - 20041014 */    

    @vch_agr_02 char(5), /* IBC de Pension */
    @vch_agr_03 char(5), /* Cotizacion Obligatoria de Pension */
    @vch_agr_04 char(5), /* Fondo de Solidaridad Pensional */
    @vch_agr_08 char(5), /* Vacaciones */
    @vch_agr_10 char(5), /* Licencia de Maternidad */
    @vch_agr_11 char(5), /* Incapacidad Enfermedad General */
    @vch_agr_13 char(5), /* Cotizacion Voluntaria al FPO por parte del Empleado */
    @vch_agr_14 char(5), /* Cotizacion Voluntaria al FPO por parte del Patrono */
    @vch_agr_18 char(5), /* Licencia No Remunerada y Sanciones (Suspension temporal del contrato) */
    @vch_agr_19 char(5), /* Valor No Retenido por Aportes Voluntarios al FPO */
    @vch_agr_20 char(5), /* Ingresos o Aporte Sin Sueldo Basico (Variacion Temporal del Salario) */
        
    @vsy_tabla sysname,
    @vbi_existe bit,
    @vvc_sql varchar(3000),
            
    @vvc_cond_0 varchar(255), --WHERE        
    @vvc_cond_1 varchar(255), --AND        
    @vvc_cond_2 varchar(255), --AND        
    @vvc_cond_3 varchar(255), --AND        

    @vch_var_PeT char(2), -- Variable Pensión Trabajador  
    @vch_var_PeE char(2), -- Variable Pensión Empleador  
    @vdc_tar_AFP decimal(6,5) -- Tarifa Pensión  

BEGIN        
    /* Campos que interesan de la tabla Acumulados Mensuales (importe y tiempo) */
    SELECT @vch_acum_imp = 'acum_imp_mes_' + Right('00' + RTrim(@pti_mes), 2),
           @vch_acum_tie = 'acum_tie_mes_' + Right('00' + RTrim(@pti_mes), 2)
        
    /* Periodo de Cotizacion */        
    SET @vch_periodo = "'" + RTrim(@psi_anio) + Right('00' + RTrim(@pti_mes), 2) + "'"
    /* Período anterior, facilita busqueda de traslados */
    IF @pti_mes = 1
        SET @vch_per_ant = "'" + RTrim(@psi_anio - 1) + '12' + "'"
    ELSE
        SET @vch_per_ant = "'" + RTrim(@psi_anio) + Right('00' + RTrim(@pti_mes - 1), 2) + "'"
    
    /*** Recuperar Agrupaciones de Conceptos ***/        
    SELECT @vch_agr_02 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 02) + "'",
           @vch_agr_03 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 03) + "'",
           @vch_agr_04 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 04) + "'",
           @vch_agr_08 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 08) + "'",
           @vch_agr_10 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 10) + "'",
           @vch_agr_11 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 11) + "'",
           @vch_agr_13 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 13) + "'",
           @vch_agr_14 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 14) + "'",
           @vch_agr_18 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 18) + "'",
           @vch_agr_19 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 19) + "'",
           @vch_agr_20 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 20) + "'"

    /*** Recuperar otros parámetros ***/
    SELECT @vch_var_PeT = dato FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = @pch_compania AND parametro = 'autoliq' AND secuencia = 27  
    SELECT @vch_var_PeE = dato FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = @pch_compania AND parametro = 'autoliq' AND secuencia = 28  
    SET @vdc_tar_AFP = ISNULL(dbo.fn_fco_var_nom(@pch_compania, @vch_var_PeT, 0) + dbo.fn_fco_var_nom(@pch_compania, @vch_var_PeE, 0), 0.15)  
    
    /*** Establecer el Nombre de la Tabla ***/
    SET @vsy_tabla = 'FCO_FPO_' + RTrim(@pch_compania) + '_' + RTrim(@psi_anio) + '_' + RTrim(@pti_mes)
    
    SET @vbi_existe = 0
    /*** Verificar Existencia de la Tabla ***/
    IF OBJECT_ID(@vsy_tabla) IS NOT NULL 
        SET @vbi_existe = 1 
    ELSE 
        SET @vbi_existe = 0  
        
    /*** Borrar datos de la tabla modelo ***/        
    DELETE FCO_FPO
        
    IF @vbi_existe = 0        
    BEGIN        
        /*** Crear y dar permisos a la tabla de autoliquidacion ***/        
        EXECUTE ('SELECT * INTO dbo.' + @vsy_tabla + ' FROM FCO_FPO')                
        /** La siguiente línea causa error cuando los usuarios no son dbowner **/
        /** sin embargo las tablas de autoliquidacion se crearan desde el cierre */
        /** por lo tanto está linea no debería ejecutarse nunca (solo en salida en vivo) */
        EXECUTE ('GRANT SELECT, UPDATE, DELETE, INSERT ON dbo.' + @vsy_tabla + ' TO PUBLIC')        
    END
        
    EXEC ('SELECT COUNT(*) cuantos INTO ##tmpcuantosFPO FROM ' + @vsy_tabla)
    IF (SELECT cuantos FROM ##tmpcuantosFPO) = 0 SET @vbi_existe = 0    
    DROP TABLE ##tmpcuantosFPO

    IF @pbi_regenerar = 1        
    BEGIN        
        IF IsNull(LTrim(@pvc_SUC), '') <> ''
            SELECT @vvc_cond_0 = " WHERE COD_SUC = '" + @pvc_SUC + "' AND COD_FPO = '" + @pvc_FPO + "'", -- DELETE e INSERT en depurada
                   @vvc_cond_1 = " AND SUC.dato = '" + @pvc_SUC + "' AND FPO.dato = '" + @pvc_FPO + "'", -- Insert General
                   @vvc_cond_2 = " AND COD_SUC = '" + @pvc_SUC + "' AND COD_FPO = '" + @pvc_FPO + "'", -- Novedades
                   @vvc_cond_3 = " AND sucursal = '" + @pvc_SUC + "' AND AFP = '" + @pvc_FPO + "'" -- Fantasmas
        ELSE
            SELECT @vvc_cond_0 = " WHERE COD_FPO = '" + @pvc_FPO + "'", -- DELETE e INSERT en depurada        
                   @vvc_cond_1 = " AND FPO.dato = '" + @pvc_FPO + "'", -- Insert General        
                   @vvc_cond_2 = " AND COD_FPO = '" + @pvc_FPO + "'", -- Novedades        
                   @vvc_cond_3 = " AND AFP = '" + @pvc_FPO + "'" -- Fantasmas        

        EXECUTE ('DELETE FROM ' + @vsy_tabla + @vvc_cond_0)        
    END
    ELSE
        SELECT @vvc_cond_0 = '', @vvc_cond_1 = '', @vvc_cond_2 = '', @vvc_cond_3 = ''
        
    IF @vbi_existe = 0 OR @pbi_regenerar = 1        
    BEGIN                
        /* Insertar los valores No Depurados a la tabla modelo (FCO_FPO) */        
        SET @vvc_sql =
        " INSERT INTO FCO_FPO" +
        " SELECT am.compania, tg.trabajador, fpo.dato, suc.dato," + 
        " tid.tipo_id As C11A, t.clave_unica As C11B, t.nombre As C12ABC," +
        " ' ' As C13A, ' ' As C13B, ' ' As C13C, ' ' As C13D, ' ' As C13E, ' ' As C13F," +
        " C13G = CASE am.agr_conceptos WHEN " + @vch_agr_20 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13H = CASE am.agr_conceptos WHEN " + @vch_agr_18 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13I = CASE am.agr_conceptos WHEN " + @vch_agr_11 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13J = CASE am.agr_conceptos WHEN " + @vch_agr_10 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13K = CASE am.agr_conceptos WHEN " + @vch_agr_08 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " ' ' C13L," +
        " C14A = CASE am.agr_conceptos WHEN " + @vch_agr_02 + " THEN " + @vch_acum_tie + " ELSE 0 END," +
        " C15A = dbo.fn_fco_sueldo(tg.compania, tg.trabajador, " + RTrim(@psi_anio) + "," + RTrim(@pti_mes) + "), " +
        " C17A = CASE am.agr_conceptos WHEN " + @vch_agr_02 + " THEN Round((" + @vch_acum_imp + "-1)/1000,0)*1000 ELSE 0 END," +        
        " C18A = " + RTrim(@vdc_tar_AFP) + "," +
        " C19A = CASE am.agr_conceptos WHEN " + @vch_agr_03 + " THEN Round((" + @vch_acum_imp + "-1)/100,0)*100 ELSE 0 END," +
        " C20A = CASE am.agr_conceptos WHEN " + @vch_agr_13 + " THEN " + @vch_acum_imp + " ELSE 0 END," +
        " C20B = CASE am.agr_conceptos WHEN " + @vch_agr_14 + " THEN " + @vch_acum_imp + " ELSE 0 END," +
        " C22A = CASE am.agr_conceptos WHEN " + @vch_agr_04 + " THEN Round((" + @vch_acum_imp + "-1)/100,0)*100 ELSE 0 END," +
        " C23A = CASE am.agr_conceptos WHEN " + @vch_agr_19 + " THEN " + @vch_acum_imp + " ELSE 0 END" +
        " FROM trabajadores t" +
        " INNER JOIN trabajadores_grales tg ON tg.trabajador = t.trabajador"+
        " INNER JOIN vw_fco_fpo_trabajador fpo ON fpo.compania = tg.compania AND fpo.trabajador = tg.trabajador" +
        " INNER JOIN vw_fco_div_trabajador suc ON suc.compania = tg.compania AND suc.trabajador = tg.trabajador" +
        " INNER JOIN acumulados_mensuales am ON am.compania = tg.compania AND am.trabajador = tg.trabajador" +
        " INNER JOIN vw_fco_tid_trabajador tid ON tg.compania = tid.compania AND tg.trabajador = tid.trabajador" +
        " WHERE tg.compania = '" + @pch_compania + "'" +
          " AND am.anio =" + RTrim(@psi_anio) +
          " AND Convert(char(6), tg.fecha_ingreso, 112) <= " + @vch_periodo +
          " AND (tg.fecha_baja IS NULL OR Convert(char(6), tg.fecha_baja, 112) >= " + @vch_periodo + ")" +
          " AND am.agr_conceptos IN (" + @vch_agr_02 + "," + @vch_agr_03 + "," + @vch_agr_04 + "," + @vch_agr_08 + "," +
                                         @vch_agr_10 + "," + @vch_agr_11 + "," + @vch_agr_13 + "," + @vch_agr_14 + "," + 
                                         @vch_agr_18 + "," + @vch_agr_19 + "," + @vch_agr_20 + ")" + 
        @vvc_cond_1
        
        EXECUTE (@vvc_sql)        
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la tabla MF_FPO', 16, 1)
        
        /* Inserta los valores (Depurados) a la tabla */        
        SET @vvc_sql = "INSERT INTO " + @vsy_tabla +
        " SELECT CIA, COD_TRAB, COD_FPO, COD_SUC, C11A, C11B, C12ABC," +
        " ' ' C13A, ' ' C13B, ' ' C13C, ' ' C13D, ' ' C13E, ' ' C13F, Max(C13G)," +
        " Max(C13H), Max(C13I), Max(C13J), Max(C13K), ' ' C13L, Max(C14A), Max(C15A)," +
        " Max(C17A), Max(C18A), Max(C19A), Max(C20A), Max(C20B), Max(C22A), Max(C23A)" +
        " FROM FCO_FPO" + @vvc_cond_0 +
        " GROUP BY CIA, C12ABC, C11B, COD_TRAB, COD_FPO, COD_SUC, C11A"
        
        EXECUTE (@vvc_sql)
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la tabla del periodo', 16, 1)
        
        /*** NOVEDADES ***/
        
        /* ING: INGRESO A LA COMPANIA */        
        SET @vvc_sql = "UPDATE " + @vsy_tabla +
         " SET C13A = 'X'" +
         " WHERE cia = '" + @pch_compania + "'" +
           " AND EXISTS (SELECT 1 FROM trabajadores_grales" +
                        " WHERE compania   = cia" +
                          " AND trabajador = cod_trab" +
                          " AND Convert(char(6),fecha_ingreso,112) = " + @vch_periodo + ")" + @vvc_cond_2
        EXECUTE (@vvc_sql)
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la insertar la novedad ING', 16, 1)
        
        /* RET: RETIRO DE LA COMPANIA */        
        SET @vvc_sql = "UPDATE " + @vsy_tabla +
        " SET C13B = 'X'" +
        " WHERE cia = '" + @pch_compania + "'" +        
          " AND EXISTS (SELECT 1 FROM trabajadores_grales" +        
                      " WHERE compania = cia" +
                      " AND trabajador = cod_trab" +
                      " AND Convert(char(6),fecha_baja,112) = " + @vch_periodo + ")" + @vvc_cond_2        
        EXECUTE (@vvc_sql)                
        
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la insertar la novedad RET', 16, 1)
        
        /* RET: RETIRO DE LA COMPANIA EN PERIODOS ANTERIORES */
        /* Por si lo dieron de baja fuera de tiempo */
        SET @vvc_sql = "UPDATE " + @vsy_tabla +
        " SET C13B = 'X'" +
        " WHERE cia = '" + @pch_compania + "'" +
          " AND EXISTS (SELECT 1 FROM trabajadores_grales" +
                      " WHERE compania = cia" +
                        " AND trabajador = cod_trab" +
                        " AND Convert(char(6),fecha_baja,112) < " + @vch_periodo + ") "  + 
          " AND C17A > 0 " + @vvc_cond_2
        EXECUTE (@vvc_sql)  
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la insertar la novedad RET', 16, 1)

        /*** RET: RETIROS EN EL MISMO PERIODO. CASO AVIDESA ***/        
        SET @vvc_sql = "UPDATE " + @vsy_tabla +        
             " SET C13B = 'X'" +        
             " WHERE cia ='" + @pch_compania + "' AND "+        
             " EXISTS (SELECT 1 FROM historial_laboral" +        
                     " WHERE compania = cia AND " +        
                     " trabajador = cod_trab AND" +        
                     " codigo_incidencia = '0000000002' AND" +        
                     " Convert(char(6), valor_03, 112) = " + @vch_periodo + ")" + @vvc_cond_2        
        EXECUTE (@vvc_sql)        
        IF @@ERROR <> 0 RAISERROR ('Error al insertar retiros de personas que reingresaron en el periodo', 16, 1)        
        
        /* TDA: INGRESO A ESTE FONDO */        
        /* Colocar la novedad siempre y cuando se haya registrado el cambio en TRASLADOS_FPO */    
        /* en el mes anterior - JAS - 20041014 */    
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +        
        " SET C13C = 'X'" +        
        " WHERE cia = '" + @pch_compania + "'" + 
          " AND C13A <> 'X'" + 
          " AND EXISTS (SELECT 1 FROM vw_fco_hist_traslados_fpo" + 
                      " WHERE compania = cia" +
                        " AND trabajador = cod_trab" +
                        " AND Convert(char(6),fecha_alta,112) = " + @vch_periodo + ")" +
          " AND EXISTS (SELECT 1 FROM fco_traslados_fpo " +
                      " WHERE compania = cia" +
                        " AND trabajador = cod_trab" +
                        " AND Convert(char(6), fecha_cambio, 112) = " + @vch_per_ant + ") " + @vvc_cond_2
        EXECUTE (@vvc_sql)
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la insertar la novedad TDA', 16, 1)
        
        /* TAA: RETIRO DE ESTE FONDO */        
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C13D = 'X'" +
        " WHERE CIA = '" + @pch_compania + "'" +
          " AND EXISTS (SELECT 1 FROM fco_traslados_fpo" +
                      " WHERE compania = cia" +
                        " AND trabajador = cod_trab" +
                        " AND Convert(char(6),fecha_cambio,112) = " + @vch_periodo + ")" + @vvc_cond_2
        EXECUTE (@vvc_sql)
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la insertar la novedad TAA', 16, 1)
        
        /* VSP: VARIACION DEL SALARIO */
        SET @vvc_sql =
        " UPDATE " + @vsy_tabla +
        " SET C13E = 'X'" +
        " WHERE CIA = '" + @pch_compania + "'" +
          " AND C13A <> 'X'" +
          " AND EXISTS (SELECT 1 FROM historico_sueldos" +
                      " WHERE compania = cia" +
                        " AND trabajador = cod_trab" +
                        " AND Convert(char(6), fecha, 112) = " + @vch_periodo + ")" + @vvc_cond_2
        EXECUTE (@vvc_sql)
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la insertar la novedad VSP', 16, 1)
        
        /*** INCORPORACION DE APORTANTES FANTASMAS ***/        
        IF OBJECT_ID('dbo.fco_fantasmas') IS NOT NULL
        BEGIN
            SET @vvc_sql = 
            " INSERT INTO " + @vsy_tabla +
            " SELECT compania, trabajador, afp, sucursal, Left(tipo_id, 1), trabajador," +
            " Replace(IsNull(primer_apellido, '') + '/' + IsNull(segundo_apellido, '') + '/' + IsNull(primer_nombre, '') + ' ' + IsNull(segundo_nombre,  ''), '  ', ' ') C12ABC," +
            " '' C13A, '' C13B, '' C13C, '' C13D, '' C13E, '' C13F," +
            " '' C13G, '' C13H, '' C13I, '' C13J, '' C13K, '' C13L, dias_afp C14A," +
            " salario C15A, ibc_afp C17A, " + RTrim(@vdc_tar_AFP) + " C18A, aporte_afp C19A, 0 C20A, 0 C20B," +
            " aporte_FSP_sol + aporte_FSP_sub C22A, 0 C23A" +
            " FROM fco_fantasmas" +
            " WHERE compania ='" + @pch_compania + "'" +
              " AND IsNull(AFP, '') <> ''" +
              " AND (anio - 2000)*12 + mes >= " + RTrim((@psi_anio - 2000)*12 + @pti_mes) +
            @vvc_cond_3
            EXECUTE (@vvc_sql)        
            IF @@ERROR <> 0 RAISERROR ('Error al incorporar aportantes fantasmas', 16, 1)
        END
        
    END
    
    IF @pbi_generar_mm = 1        
        EXECUTE sp_fco_crea_pension_mm @pch_compania, @psi_anio, @pti_mes, @pvc_SUC, @pvc_FPO    
END        
GO
GRANT EXECUTE ON dbo.sp_fco_crea_pension TO PUBLIC
GO
