SET QUOTED_IDENTIFIER OFF
IF OBJECT_ID('dbo.sp_fco_crea_salud') IS NOT NULL
    DROP PROCEDURE dbo.sp_fco_crea_salud
GO
CREATE PROCEDURE dbo.sp_fco_crea_salud
(
    @pch_compania char(4),
    @psi_anio smallint,
    @pti_mes tinyint,
    @pvc_suc varchar(10),
    @pvc_EPS 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 Salud  ***/
/*** Autor:       Juan Carlos Leguizamon                            ***/
/*** Fecha MF:    Junio 29 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),

    @vch_agr_05 char(5), /* Cotización Obligatoria de Salud */
    @vch_agr_08 char(5), /* Vacaciones */
    @vch_agr_10 char(5), /* Licencia de Maternidad */
    @vch_agr_11 char(5), /* Incapacidad por Enfermedad General */
    @vch_agr_15 char(5), /* UPC Adicional (Aporte Unidad de Capitacion) */
    @vch_agr_16 char(5), /* IBC Salud */
    @vch_agr_18 char(5), /* Licencia No Remunerada y Sanciones (Suspension temporal del contrato) */
    @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_cod_eps varchar(10),
    @vvc_cod_suc varchar(10),
    @vvc_nro_aut varchar(10),
    @vdc_vlr_aut decimal(9,0),
    
    @vvc_cond_0 varchar(255), --WHERE
    @vvc_cond_1 varchar(255), --AND
    @vvc_cond_2 varchar(255), --AND
    @vvc_cond_3 varchar(255)  --AND    
    
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)

    /* Período de acumulados (anterior al 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' + "'" -- JCL FEB-05-07 FALTABA LA COMILLA
    ELSE
        SET @vch_per_ant = "'" + RTrim(@psi_anio) + Right('00' + RTrim(@pti_mes - 1), 2) + "'"
        
    /*** Recuperar Agrupaciones de Conceptos ***/
    SELECT @vch_agr_05 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 05) + "'",
           @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_15 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 15) + "'",
           @vch_agr_16 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 16) + "'",
           @vch_agr_18 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 18) + "'",
           @vch_agr_20 = "'" + dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 20) + "'"
    
    /*** Establecer el Nombre de la Tabla ***/
    SET @vsy_tabla = 'FCO_EPS_' + 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_EPS
    
    IF @vbi_existe = 0
    BEGIN
        /*** Crear y dar permisos a la tabla de autoliquidacion ***/
        EXECUTE ('SELECT * INTO dbo.' + @vsy_tabla + ' FROM FCO_EPS')  
        /** 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 ##tmpcuantosEPS FROM ' + @vsy_tabla)
    IF (SELECT cuantos FROM ##tmpcuantosEPS) = 0 SET @vbi_existe = 0  
    DROP TABLE ##tmpcuantosEPS

    IF @pbi_regenerar = 1
    BEGIN
        IF IsNull(LTrim(@pvc_suc), '') <> ''
            SELECT @vvc_cond_0 = " WHERE COD_SUC = '" + @pvc_suc + "' AND COD_EPS = '" + @pvc_EPS + "'", -- DELETE e INSERT en depurada
                   @vvc_cond_1 = " AND SUC.dato = '" + @pvc_suc + "' AND EPS.dato = '" + @pvc_EPS + "'", -- Insert General
                   @vvc_cond_2 = " AND COD_SUC = '" + @pvc_suc + "' AND COD_EPS = '" + @pvc_EPS + "'", -- Novedades
                   @vvc_cond_3 = " AND sucursal = '" + @pvc_suc + "' AND EPS = '" + @pvc_EPS + "'" -- Fantasmas
        ELSE
            SELECT @vvc_cond_0 = " WHERE COD_EPS = '" + @pvc_EPS + "'", -- DELETE e INSERT en depurada
                   @vvc_cond_1 = " AND EPS.dato = '" + @pvc_EPS + "'", -- Insert General
                   @vvc_cond_2 = " AND COD_EPS = '" + @pvc_EPS + "'", -- Novedades
                   @vvc_cond_3 = " AND EPS = '" + @pvc_EPS + "'" -- 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_EPS) */
        SET @vvc_sql = 
        " INSERT INTO FCO_EPS" +
        " SELECT tg.compania, tg.trabajador, eps.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," +
        " C13F = CASE am.agr_conceptos WHEN " + @vch_agr_20 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13G = CASE am.agr_conceptos WHEN " + @vch_agr_18 + " THEN CASE " + @vch_acum_tie + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13H = CASE am.agr_conceptos WHEN " + @vch_agr_11 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13I = CASE am.agr_conceptos WHEN " + @vch_agr_10 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13J = CASE am.agr_conceptos WHEN " + @vch_agr_08 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C14A = CASE am.agr_conceptos WHEN " + @vch_agr_16 + " THEN " + @vch_acum_tie + " ELSE 0 END," +
        " C15A = dbo.fn_fco_sueldo(tg.compania, tg.trabajador, " + RTrim(@psi_anio) + "," + RTrim(@pti_mes) + "), " +
        " 0 As C16A," +
        " C17A = CASE am.agr_conceptos WHEN " + @vch_agr_16 + " THEN Round((" + @vch_acum_imp + "-1)/1000,0)*1000 ELSE 0 END," +
        " C24A = CASE am.agr_conceptos WHEN " + @vch_agr_05 + " THEN Round((" + @vch_acum_imp + "-1)/100,0)*100 ELSE 0 END," +
        " ' ' As C25A, 0 As C25B, ' ' As C26A, 0 As C26B," +
        " C27A = CASE am.agr_conceptos WHEN " + @vch_agr_15 + " THEN " + @vch_acum_imp + " ELSE 0 END" +
        " FROM trabajadores t" +
        " INNER JOIN trabajadores_grales tg ON t.trabajador = tg.trabajador" +
        " INNER JOIN vw_fco_eps_trabajador eps ON tg.compania = eps.compania AND tg.trabajador = eps.trabajador" +
        " INNER JOIN vw_fco_div_trabajador suc ON tg.compania = suc.compania AND tg.trabajador = suc.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_05 + "," + @vch_agr_08 + "," + @vch_agr_10 + "," + @vch_agr_11 + "," + 
                                         @vch_agr_15 + "," + @vch_agr_16 + "," + @vch_agr_18 + "," + @vch_agr_20 + ")" + 
        @vvc_cond_1
    
        EXECUTE (@vvc_sql)      
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la tabla FCO_EPS', 16, 1)
        
        /* Insertar los valores (Depurados) a la tabla */
        SET @vvc_sql = 
        " INSERT INTO " + @vsy_tabla +
        " SELECT CIA, COD_TRAB, COD_EPS, COD_SUC, C11A, C11B, C12ABC," +
        " ' ' C13A, ' ' C13B, ' ' C13C, ' ' C13D, ' ' C13E, Max(C13F), Max(C13G)," +
        " Max(C13H), Max(C13I), Max(C13J), Max(C14A), Max(C15A), 0 C16A, Max(C17A)," +
        " Max(C24A), ' ' C25A, 0 C25B, ' ' C26A, 0 C26B, Max(C27A)" +
        " FROM FCO_EPS" + @vvc_cond_0 +
        " GROUP BY CIA, C12ABC, C11B, COD_TRAB, COD_EPS, 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 ESTA EPS */
        /* Colocar la novedad siempre y cuando se haya registrado el cambio en TRASLADOS_EPS */    
        /* 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_eps" +
                      " WHERE compania   = cia" +
                        " AND trabajador = cod_trab" +
                        " AND Convert(char(6), fecha_alta, 112) = " + @vch_periodo + ")" +
         " AND EXISTS (SELECT 1 FROM fco_traslados_eps " +
                     " 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 ESTA EPS */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C13D = 'X'" +
        " WHERE CIA = '" + @pch_compania + "'" +
          " AND EXISTS (SELECT 1 FROM fco_traslados_eps" +
                      " 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)
    
        /* AUTORIZACIONES ENFERMEDAD GENERAL (NUMERO Y VALOR POR TRABAJADOR) */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C25A = numero, C25B = valor_total" +
        " FROM FCO_autorizaciones C, FCO_autorizaciones_det D" +
        " WHERE C.compania = D.compania And C.autorizacion = D.autorizacion" +
          " AND cia = C.compania" +
          " AND cod_trab = D.trabajador" +
          " AND tipo_incapacidad = 0" +
          " AND cia = '" + @pch_compania + "'" +
          " AND periodo = " + @vch_periodo + @vvc_cond_2
        EXECUTE (@vvc_sql)  
        IF @@ERROR <> 0 RAISERROR ('Error al llenar las autorizaciones de Incapacidades por EG', 16, 1)
    
        /* INSERCION DE LAS AUTORIZACIONES POR EG EN LA TABLA DEL PIE DE REPORTE */
        DECLARE cr_autorizaciones CURSOR FOR
        SELECT cod_entidad, sucursal, Max(numero) NRO, Sum(valor_total) VLR
        FROM fco_autorizaciones
        WHERE compania = @pch_compania 
          AND periodo = SubString(@vch_periodo,2,6) 
          AND tipo_incapacidad = 0
        GROUP BY cod_entidad, sucursal
        HAVING Sum(valor_total) > 0
        
        OPEN cr_autorizaciones
        FETCH NEXT FROM cr_autorizaciones INTO @vvc_cod_eps, @vvc_cod_suc, @vvc_nro_aut, @vdc_vlr_aut      
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC sp_fco_cambio_ala_datos_adi @vsy_tabla, 3, @vvc_cod_eps, @vvc_cod_suc, 0, @vvc_nro_aut, @vdc_vlr_aut, null, null
            FETCH NEXT FROM cr_autorizaciones INTO @vvc_cod_eps, @vvc_cod_suc, @vvc_nro_aut, @vdc_vlr_aut
        END
        CLOSE cr_autorizaciones
        DEALLOCATE cr_autorizaciones
    
        /* AUTORIZACIONES POR LICENCIA DE MATERNIDAD (NUMERO Y VALOR POR TRABAJADOR) */
        SET @vvc_sql = " UPDATE " + @vsy_tabla +
        " SET C26A = numero, C26B = valor_total" +
        " FROM FCO_autorizaciones C, FCO_autorizaciones_det D" +
        " WHERE C.compania = D.compania And C.autorizacion = D.autorizacion" +
        " AND cia = C.compania" +
        " AND cod_trab = D.trabajador" +
        " AND tipo_incapacidad = 1" +
        " AND cia = '" + @pch_compania + "'" +
        " AND periodo=" + @vch_periodo + @vvc_cond_2
        EXECUTE (@vvc_sql)  
        IF @@ERROR <> 0 RAISERROR ('Error al llenar las autorizaciones de licencias de maternidad', 16, 1)
    
        /* INSERCION DE LAS AUTORIZACIONES POR LM EN LA TABLA DEL PIE DE REPORTE */
        DECLARE cr_autorizaciones CURSOR FOR
        SELECT cod_entidad, sucursal, Max(numero) NRO, Sum(valor_total) VLR
        FROM fco_autorizaciones
        WHERE compania = @pch_compania 
          AND periodo = SubString(@vch_periodo,2,6) 
          AND tipo_incapacidad = 1
        GROUP BY cod_entidad, sucursal
        HAVING Sum(valor_total) > 0
        
        OPEN cr_autorizaciones
        FETCH NEXT FROM cr_autorizaciones INTO @vvc_cod_eps, @vvc_cod_suc, @vvc_nro_aut, @vdc_vlr_aut      
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXECUTE sp_fco_cambio_ala_datos_adi @vsy_tabla, 3, @vvc_cod_eps, @vvc_cod_suc, 1, @vvc_nro_aut, @vdc_vlr_aut, null, null
            FETCH NEXT FROM cr_autorizaciones INTO @vvc_cod_eps, @vvc_cod_suc, @vvc_nro_aut, @vdc_vlr_aut
        END
        CLOSE cr_autorizaciones
        DEALLOCATE cr_autorizaciones
        
        /*** INCORPORACION DE APORTANTES FANTASMAS ***/
        IF OBJECT_ID('dbo.fco_fantasmas') IS NOT NULL
        BEGIN
            SET @vvc_sql = "INSERT INTO " + @vsy_tabla +
            " SELECT compania CIA, trabajador COD_TRAB," +
            " eps COD_EPS, sucursal COD_SUC, Left(tipo_id, 1) C11A, trabajador C11B," +
            " Replace(IsNull(primer_apellido, '') + '/' + IsNull(segundo_apellido, '') + '/' + IsNull(primer_nombre, '') + ' ' + IsNull(segundo_nombre,  ''), '  ', ' ') C12ABC, " +
            " '' C13A, '' C13B, '' C13C, '' C13D, '' C13E, '' C13F," +
            " '' C13G, '' C13H, '' C13I, '' C13J, dias_eps C14A, salario C15A," +
            " 0 C16A, ibc_eps C17A, aporte_eps C24A, '' C25A, 0 C25B, '' C26A, 0 C26B, 0 C27A" +
            " FROM fco_fantasmas" +
            " WHERE compania ='" + @pch_compania + "'" +
              " AND IsNull(eps, '') <> ''" + 
              " 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_salud_mm @pch_compania, @psi_anio, @pti_mes, @pvc_suc, @pvc_EPS
    
END
GO
GRANT EXECUTE ON dbo.sp_fco_crea_salud TO PUBLIC
GO
