SET QUOTED_IDENTIFIER OFF
IF OBJECT_ID('dbo.sp_fco_crea_iss') IS NOT NULL
    DROP PROCEDURE dbo.sp_fco_crea_iss
GO
CREATE PROCEDURE dbo.sp_fco_crea_iss
(
    @pch_compania char(4),
    @psi_anio smallint,
    @pti_mes tinyint,
    @pvc_sucursal 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 al ISS   ***/
/***              Metodologia SIGA - Formato Integrado              ***/
/*** Autor:       Juan Carlos Leguizamon                            ***/
/*** Fecha MF:    Junio 19 de 2001                                  ***/
/*** Fecha FCO:   Marzo 30 de 2006                                  ***/
/**********************************************************************/
AS
DECLARE    
    @vch_acum_imp char(15),
    @vch_acum_tie char(15),

    @vsy_tabla sysname,
    @vbi_existe bit,
    @vvc_sql varchar(8000),

    @vch_agr_02 char(3), /* IBC de Pension */
    @vch_agr_03 char(3), /* Aporte a Pension */
    @vch_agr_04 char(3), /* Fondo de Solidaridad Pensional */
    @vch_agr_05 char(3), /* Aporte a Salud */
    @vch_agr_08 char(3), /* Vacaciones */
    @vch_agr_09 char(3), /* Aporte a Riesgos Profesional */
    @vch_agr_10 char(3), /* Licencia de Maternidad */
    @vch_agr_11 char(3), /* Incapacidad por Enfermedad General */
    @vch_agr_12 char(3), /* Incapacidad por Accidente de Trabajo */
    @vch_agr_15 char(3), /* UPC (Aporte Unidad de Capitacion) */
    @vch_agr_16 char(3), /* IBC Salud */
    @vch_agr_17 char(3), /* IBC Riesgos Profesionales */
    @vch_agr_18 char(3), /* Licencia No Remunerada y Sanciones */
    @vch_agr_20 char(3), /* Ingresos o Aporte Sin Sueldo Basico */

    @vch_periodo char(6),

    @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

    @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

    @vvc_iss_eps varchar(10), /* Código del ISS como EPS de los trabajadores en ADAM */
    @vvc_iss_afp varchar(10)  /* Código del ISS como AFP de los trabajadores en ADAM */
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)
    
    /*** 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_05 = dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 05),
           @vch_agr_08 = dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 08),
           @vch_agr_09 = dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 09),
           @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_12 = dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 12),
           @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_17 = dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 17),
           @vch_agr_18 = dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 18),
           @vch_agr_20 = dbo.fn_fco_parametro_agr(@pch_compania, 'autoliq', 20)

    /* Recueperar los códigos del ISS como EPS y AFP */
    SELECT @vvc_iss_eps = dato FROM vw_fdt_parametros x
    WHERE x.pais = 'COL' AND x.compania = @pch_compania AND x.parametro = 'otros' AND x.secuencia = 11

    SET @vvc_iss_eps = IsNull(@vvc_iss_eps, 'ISS')
    
    SELECT @vvc_iss_afp = dato FROM vw_fdt_parametros x
    WHERE x.pais = 'COL' AND x.compania = @pch_compania AND x.parametro = 'otros' AND x.secuencia = 10    

    SET @vvc_iss_afp = IsNull(@vvc_iss_afp, 'ISS')

    /*** 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.155)  

    /*** Establecer el Nombre de la Tabla ***/
    SET @vsy_tabla = 'FCO_ISS_' + 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_ISS
    
    IF @vbi_existe = 0
    BEGIN
        /*** Crear y dar permisos a la tabla de autoliquidacion ***/
        EXECUTE ('SELECT * INTO dbo.' + @vsy_tabla + ' FROM FCO_ISS')  
        /** 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 ##tmpcuantosISS FROM ' + @vsy_tabla)
    IF (SELECT cuantos FROM ##tmpcuantosISS) = 0 SET @vbi_existe = 0  
    DROP TABLE ##tmpcuantosISS

    IF @pbi_regenerar = 1
    BEGIN
        IF LTrim(IsNull(@pvc_sucursal, '')) <> ''
            SELECT @vvc_cond_0 = " WHERE COD_SUC = '" + @pvc_sucursal + "'", -- DELETE e INSERT en depurada
                   @vvc_cond_1 = " AND SUC.dato = '" + @pvc_sucursal + "'", -- Insert General
                   @vvc_cond_2 = " AND COD_SUC = '" + @pvc_sucursal + "'", -- Novedades
                   @vvc_cond_3 = " AND sucursal = '" + @pvc_sucursal + "'"     -- Fantasmas
        ELSE
            SELECT @vvc_cond_0 = '', @vvc_cond_1 = '', @vvc_cond_2 = '', @vvc_cond_3 = ''
        
        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 FCO_ISS ***/
        SET @vvc_sql =
            " INSERT INTO FCO_ISS" +
            " SELECT am.compania, tg.trabajador," + 
            " CASE WHEN itr.es_eps = 1 THEN 'ISS' ELSE 'Otro' END, " +
            " CASE WHEN itr.es_afp = 1 THEN 'ISS' ELSE 'Otro' END, " +
            " suc.dato," +
            " tid.tipo_id C03, t.clave_unica C04, t.nombre C05," +
            " ' ' C09, ' ' C10, ' ' C11, ' ' C12, ' ' C13, ' ' C14,"+
            " C15 = CASE am.agr_conceptos WHEN '" + @vch_agr_20 + "' THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
            " C16 = CASE am.agr_conceptos WHEN '" + @vch_agr_18 + "' THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
            " C17 = CASE am.agr_conceptos WHEN '" + @vch_agr_11 + "' THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
            " C18 = CASE am.agr_conceptos WHEN '" + @vch_agr_10 + "' THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
            " C19 = CASE am.agr_conceptos WHEN '" + @vch_agr_08 + "' THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
            " ' ' C20, ' ' C21, 0 C22," +
            " C23 = CASE am.agr_conceptos WHEN '" + @vch_agr_02 + "' THEN " + @vch_acum_tie + " ELSE 0 END," +
            " C24 = Case When itr.es_eps = 1 Then CASE am.agr_conceptos WHEN '" + @vch_agr_16 + "' THEN " + @vch_acum_tie + " ELSE 0 END Else 0 End," +
            " C25 = 0," +
            " C26 = dbo.fn_fco_sueldo(tg.compania, tg.trabajador, " + RTrim(@psi_anio) + "," + RTrim(@pti_mes) + "), " +
            " C27 = 0," +
            " C28 = Case When itr.es_afp = 1 Then CASE am.agr_conceptos WHEN '" + @vch_agr_02 + "' THEN Round((" + @vch_acum_imp + "-1)/1000,0)*1000 ELSE 0 END Else 0 End," +
            " C29 = Case When itr.es_eps = 1 Then CASE am.agr_conceptos WHEN '" + @vch_agr_16 + "' THEN Round((" + @vch_acum_imp + "-1)/1000,0)*1000 ELSE 0 END Else 0 End," +
            " C30 = 0," +
            " C31 = Case When itr.es_afp = 1 Then " + RTrim(@vdc_tar_AFP) + " Else 0 End," +
            " C32 = Case When itr.es_afp = 1 Then CASE am.agr_conceptos WHEN '" + @vch_agr_03 + "' THEN Round((" + @vch_acum_imp + "-1)/100,0)*100 ELSE 0 END Else 0 End," +
            " 0 C33, 0 C34, 0 C35," +
            " C36 = Case When itr.es_afp = 1 Then CASE am.agr_conceptos WHEN '" + @vch_agr_04 + "' THEN Round((" + @vch_acum_imp + "-1)/100,0)*100 ELSE 0 END Else 0 End," +
            " 0 C37," +
            " C38 = Case When itr.es_eps = 1 Then CASE am.agr_conceptos WHEN '" + @vch_agr_05 + "' THEN Round((" + @vch_acum_imp + "-1)/100,0)*100 ELSE 0 END Else 0 End," + 
            " ' ' C39, 0 C40, ' ' C41, 0 C42," +
            " C43 = Case When itr.es_eps = 1 Then CASE am.agr_conceptos WHEN '" + @vch_agr_15 + "' THEN " + @vch_acum_imp + " ELSE 0 END Else 0 End," +
            " 0 C44, 0 C45, 0 C46, ' ' C47, ' ' C48" +
            " FROM trabajadores t" +
            " INNER JOIN trabajadores_grales tg ON tg.trabajador = t.trabajador" +
            " INNER JOIN vw_fco_iss_trabajador itr ON itr.compania = tg.compania AND itr.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 am.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_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_iss', 16, 1)
        
        /* Inserta los valores (Depurados) a la tabla */
        SET @vvc_sql =  
        " INSERT INTO " + @vsy_tabla +
        " SELECT CIA, COD_TRAB, COD_EPS, COD_FPO, COD_SUC, C03, C04, C05," +
        " ' ' C09, ' ' C10, ' ' C11, ' ' C12, ' ' C13, ' ' C14, Max(C15)," +
        " Max(C16), Max(C17), Max(C18), Max(C19), ' ' C20, ' ' C21, 0 C22," +
        " Max(C23), Max(C24), 0 C25, Max(C26), 0 C27, Max(C28), Max(C29)," +
        " 0 C30, Max(C31), Max(C32), 0 C33, 0 C34, 0 C35, Max(C36)," +
        " 0 C37, Max(C38), ' ' C39, 0 C40, ' ' C41, 0 C42, Max(C43)," +
        " 0 C44, 0 C45, 0 C46, ' ' C47, ' ' C48" +
        " FROM FCO_ISS" + @vvc_cond_0 +
        " GROUP BY CIA, C05, C04, COD_TRAB, COD_EPS, COD_FPO, COD_SUC, C03"
        EXECUTE (@vvc_sql)
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la tabla depurada.', 16, 1)
        
        /*** INSERCION DE NOVEDADES ***/
        /* ING: INGRESO A LA COMPANIA */
        SET @vvc_sql =  
        " UPDATE " + @vsy_tabla +
        " SET C09 = '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 insertar Novedad ING', 16, 1)
        
        /* RET: RETIRO DE LA COMPANIA */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C10 = '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 insertar Novedad RET', 16, 1)
        
        /*** RETIROS EN EL MISMO PERIODO. CASO AVIDESA ***/
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C10 = '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 AL ISS EN PENSIONES */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C11 = 'X'" +
        " WHERE CIA = '" + @pch_compania + "'" +
          " AND COD_FPO = 'ISS'" +
          " 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 +  "')" +
        @vvc_cond_2
        EXECUTE (@vvc_sql)
        IF @@ERROR <> 0 RAISERROR ('Error al insertar Novedad TDA (FPO)', 16, 1)
        
        /* TDA: INGRESO AL ISS EN SALUD */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C11 = 'X'" +
        " WHERE CIA = '" + @pch_compania + "'"+
          " AND COD_EPS = 'ISS'" +
          " 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 +  "')" +
                     @vvc_cond_2
        EXECUTE (@vvc_sql)
        IF @@ERROR <> 0 RAISERROR ('Error al insertar Novedad TDA (EPS)', 16, 1)
        
        /* TAA: RETIRO DEL ISS EN PENSIONES */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C12 = 'X'" +
        " WHERE CIA = '" + @pch_compania + "'" +
          " AND COD_FPO = 'ISS'" +
          " 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 insertar Novedad TAA (FPO)', 16, 1)
        
        /* TAA: RETIRO DEL ISS EN SALUD */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C12 = 'X'" +
        " WHERE CIA ='" + @pch_compania + "'" +
          " AND COD_EPS = 'ISS'" +
          " 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 insertar Novedad TAA (EPS)', 16, 1)
        
        /* VSP: CAMBIO DE SUELDO */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C13 = 'X'" +
        " WHERE CIA ='" + @pch_compania + "'" +
          " AND C09 <> '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 insertar Novedad VSP', 16, 1)
        
        /* CODIGO NUEVA ADMINISTRADORA */
        /* EPS */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla + 
        " SET C47 = a.cod_super" +
        " FROM fco_administradoras a, fco_traslados_eps t" +
        " WHERE a.codigo = t.EPS_nueva" +
          " AND a.tipo = 'EPS'" +
          " AND t.EPS_actual = '" + @vvc_iss_eps + "'" +
          " AND CIA = T.compania" +
          " AND COD_TRAB = T.trabajador" +
          " AND CIA = '" + @pch_compania + "'" +
          " AND Convert(char(6), T.fecha_cambio, 112) = '" + @vch_periodo + "'" +
        @vvc_cond_2
        EXECUTE (@vvc_sql)
        IF @@ERROR <> 0 RAISERROR ('Error al insertar codigo nueva EPS', 16, 1)
        
        /* AFP */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla + 
        " SET C48 = A.cod_super" +
        " FROM fco_administradoras a, fco_traslados_fpo t" +
        " WHERE a.codigo = t.FPO_nueva" +
          " AND a.tipo = 'AFP'" +
          " AND t.FPO_actual = '" + @vvc_iss_afp + "'" +
          " AND CIA = t.compania" +
          " AND COD_TRAB = t.trabajador" +
          " AND CIA ='" + @pch_compania + "'" +
          " AND Convert(char(6), t.fecha_cambio, 112) = '" + @vch_periodo + "'" +
        @vvc_cond_2
        EXECUTE (@vvc_sql)
        IF @@ERROR <> 0 RAISERROR ('Error al insertar codigo nueva AFP', 16, 1)
        
        /* AUTORIZACIONES ENFERMEDAD GENERAL (NUMERO Y VALOR POR TRABAJADOR) */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C39 = numero, C40 = 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 */
        SELECT @vvc_nro_aut = Max(IsNull(numero, '')), @vdc_vlr_aut = Sum(IsNull(valor_total,0))
        FROM fco_autorizaciones
        WHERE compania = @pch_compania
          AND sucursal = @pvc_sucursal
          AND cod_entidad = @vvc_iss_eps
          AND periodo = @vch_periodo
          AND tipo_incapacidad = 0
        IF @vdc_vlr_aut > 0 
            EXECUTE sp_fco_cambio_ala_datos_adi @vsy_tabla, 4, @vvc_iss_eps, @pvc_sucursal, 0, @vvc_nro_aut, null, @vdc_vlr_aut, null
        
        /* AUTORIZACIONES LICENCIAS DE MATERNIDAD (NUMERO Y VALOR POR TRABAJADOR) */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C41 = numero, C42 = 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 */
        SELECT @vvc_nro_aut = Max(IsNull(numero,'')), @vdc_vlr_aut = Sum(IsNull(valor_total,0))
        FROM fco_autorizaciones
        WHERE compania = @pch_compania
          AND sucursal = @pvc_sucursal
          AND cod_entidad = @vvc_iss_eps
          AND periodo=@vch_periodo
          AND tipo_incapacidad = 1
        IF @vdc_vlr_aut > 0 
            EXECUTE sp_fco_cambio_ala_datos_adi @vsy_tabla, 4, @vvc_iss_eps, @pvc_sucursal, 1, @vvc_nro_aut, null, @vdc_vlr_aut, null

        /*** 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," +
            " afp COD_FPO, sucursal COD_SUC, Left(tipo_id, 1) C03, trabajador C04," +
            " Replace(IsNull(primer_apellido, '') + '/' + IsNull(segundo_apellido, '') + '/' + IsNull(primer_nombre, '') + ' ' + IsNull(segundo_nombre,  ''), '  ', ' ') C05," +
            " '' C09, '' C10, '' C11, '' C12, '' C13, '' C14," +
            " '' C15, '' C16, '' C17, '' C18, '' C19, '' C20, '' C21, 0 C22," +
            " Case When eps = 'ISS' Then dias_afp Else 0 End C23," + 
            " Case When eps = 'ISS' Then dias_eps Else 0 End C24, 0 C25," + 
            " salario C26, 0 C27," +
            " Case When afp = 'ISS' Then ibc_afp Else 0 End C28," +
            " Case When eps = 'ISS' Then ibc_eps Else 0 End C29," +
            " 0 C30, " + 
            " Case When afp = 'ISS' Then " + RTrim(@vdc_tar_AFP) + " Else 0 End C31," +
            " Case When afp = 'ISS' Then aporte_afp Else 0 End C32," +
            " 0 C33, 0 C34, 0 C35," + 
            " Case When afp = 'ISS' Then aporte_fsp_sol + aporte_fsp_sub Else 0 End C36," + 
            " 0 C37," + 
            " Case When eps = 'ISS' Then aporte_eps Else 0 End C38," +
            " '' C39, 0 C40, '' C41, 0 C42, 0 C43, 0 C44, 0 C45, 0 C46," +
            " '' C47, '' C48" +
            " FROM fco_fantasmas" +
            " WHERE compania ='" + @pch_compania + "'" +
              " AND (eps = '" + @vvc_iss_eps + "' OR afp = '" + @vvc_iss_afp + "')" +
              " AND RTrim(anio) + Right('00' + RTrim(mes), 2) >= " + RTrim(@psi_anio) + Right('00' + RTrim(@pti_mes), 2) +
            @vvc_cond_3
            EXECUTE (@vvc_sql)
            IF @@ERROR <> 0 RAISERROR ('Error al incorporar aportantes fantasmas en Salud y Pension', 16, 1)
        END
    END
    IF @pbi_generar_mm = 1
        EXECUTE sp_fco_crea_ISS_mm @pch_compania, @psi_anio, @pti_mes, @pvc_sucursal

END
GO
GRANT EXECUTE ON dbo.sp_fco_crea_iss TO PUBLIC
GO
