SET QUOTED_IDENTIFIER OFF
IF OBJECT_ID('dbo.sp_fco_crea_riesgos') IS NOT NULL
    DROP PROCEDURE dbo.sp_fco_crea_riesgos
GO
CREATE PROCEDURE dbo.sp_fco_crea_riesgos
(                      
    @pch_compania varchar(4),
    @psi_anio smallint,
    @pti_mes tinyint,
    @pvc_suc 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 Riesgos***/
/*** Autor:       Juan Carlos Leguizamon                            ***/
/*** Fecha MF:    Julio 10 de 2001                                  ***/
/*** Fecha FCO:   Marzo 24 de 2006                                  ***/
/*** Ultima Revisión: JCL. Julio 5/06. Control de fechas de ingreso y baja ***/
/**********************************************************************/
AS
DECLARE
    @vch_acum_imp char(15),
    @vch_acum_tie char(15),
    
    @vch_periodo char(8),
    
    @vch_agr_08 char(5), /* Vacaciones */
    @vch_agr_09 char(5), /* Cotizacion Obligatoria Riesgos Profesionales */
    @vch_agr_10 char(5), /* Licencia de Maternidad */
    @vch_agr_11 char(5), /* Incapacidad Enfermedad General */
    @vch_agr_12 char(5), /* Incapacidad por Accidente de Trabajo */
    @vch_agr_17 char(5), /* IBC Riesgos Profesionales */
    @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_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 */
    SET @vch_periodo = "'" + RTrim(@psi_anio) + Right('00' + RTrim(@pti_mes), 2) + "'"
        
    /*** Recuperar Agrupaciones de Conceptos ***/
    SELECT @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_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) + "'"
    
    /*** Establecer el Nombre de la Tabla ***/
    SET @vsy_tabla = 'FCO_ARP_' + 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_ARP
    
    IF @vbi_existe = 0
    BEGIN
        /*** Crear y dar permisos a la tabla de autoliquidacion ***/
        EXECUTE ('SELECT * INTO dbo.' + @vsy_tabla + ' FROM FCO_ARP')  
        /** 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 ##tmpcuantosARP FROM ' + @vsy_tabla)
    IF (SELECT cuantos FROM ##tmpcuantosARP) = 0 SET @vbi_existe = 0  
    DROP TABLE ##tmpcuantosARP

    IF @pbi_regenerar = 1
    BEGIN
        IF IsNull(LTrim(@pvc_SUC), '') <> ''
            SELECT @vvc_cond_0 = " WHERE COD_SUC = '" + @pvc_SUC + "'", -- DELETE e INSERT en depurada
                   @vvc_cond_1 = " AND SUC.dato = '" + @pvc_SUC + "'", -- Insert General
                   @vvc_cond_2 = " AND COD_SUC = '" + @pvc_SUC + "'", -- Novedades
                   @vvc_cond_3 = " AND SUC = '" + @pvc_SUC + "'" -- 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 modelo (FCO_ARP) */
        SET @vvc_sql =
        " INSERT INTO FCO_ARP" +
        " SELECT tg.compania, tg.trabajador, suc.dato, " +
        " tid.tipo_id As C11A, t.clave_unica As C11B, t.nombre As C12ABC," +
        " ' ' As C13A, ' ' As C13B, ' ' As C13C, ' ' As C13D," +
        " C13E = CASE am.agr_conceptos WHEN " + @vch_agr_20 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13F = CASE am.agr_conceptos WHEN " + @vch_agr_18 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13G = CASE am.agr_conceptos WHEN " + @vch_agr_11 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13H = CASE am.agr_conceptos WHEN " + @vch_agr_10 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " C13I = CASE am.agr_conceptos WHEN " + @vch_agr_08 + " THEN CASE " + @vch_acum_imp + " WHEN 0 THEN ' ' ELSE 'X' END ELSE ' ' END," +
        " ' ' As C13J," +
        " C13K = CASE am.agr_conceptos WHEN " + @vch_agr_12 + " THEN " + @vch_acum_tie + " ELSE 0 END," +
        " C14A = CASE am.agr_conceptos WHEN " + @vch_agr_17 + " 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_17 + " THEN Round((" + @vch_acum_imp + "-1)/1000,0)*1000 ELSE 0 END," +
        " dbo.fn_fco_tarifa_ARP(tg.compania, tg.trabajador) As C28A," +
        " dbo.fn_fco_centro_trabajo(tg.compania, tg.trabajador) As C28B," +
        " C29A = CASE am.agr_conceptos WHEN " + @vch_agr_09 + " THEN Round((" + @vch_acum_imp + "-1)/100,0)*100 ELSE 0 END," +
        " ' ' C30A, ' ' C30B," +
        " C32B = CASE am.agr_conceptos WHEN " + @vch_agr_12 + " THEN " + @vch_acum_imp + " ELSE 0 END" +
        " FROM trabajadores t" +
        " INNER JOIN trabajadores_grales tg ON t.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_08 + "," + @vch_agr_09 + "," + @vch_agr_10 + "," + @vch_agr_11 + "," + 
                                         @vch_agr_12 + "," + @vch_agr_17 + "," + @vch_agr_18 + "," + @vch_agr_20 + ")" + 
        @vvc_cond_1                      

        EXECUTE (@vvc_sql)                      
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la tabla FCO_ARP', 16, 1)
    
        /* Inserta los valores (Depurados) a la tabla */                      
        SET @vvc_sql =
        " INSERT INTO " + @vsy_tabla +
        " SELECT CIA, COD_TRAB, COD_SUC, C11A, C11B, C12ABC," +
        " ' ' C13A, ' ' C13B, ' ' C13C, ' ' C13D, Max(C13E), Max(C13F), Max(C13G)," +
        " Max(C13H), Max(C13I), ' ' C13J, Max(C13K), Max(C14A), Max(C15A), Max(C17A)," +
        " 0 C28A, Max(C28B), Max(C29A), ' ' C30A, ' ' C30B, Max(C32B)" +
        " FROM FCO_ARP" + @vvc_cond_0 +
        " GROUP BY CIA, C12ABC, C11B, COD_TRAB, COD_SUC, C11A"
        EXECUTE (@vvc_sql)                      
        IF @@ERROR <> 0 RAISERROR ('Error al llenar la tabla del período', 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 + ")" +
        /* Anexar que el trabajador tenga valor reportado en ARP - JAS 20040316 */                
        " AND C17A > 0 " + @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)
        
        /* VSP: VARIACION DEL SALARIO */
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C13C = '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 novedad VSP', 16, 1)
    
        /* VCT - CAMBIO DE CENTRO DE TRABAJO - */                     
        /* Registrar la novedad siempre y cuando no sea un ingreso - JAS - 20041014 */      
        SET @vvc_sql = 
        " UPDATE " + @vsy_tabla +
        " SET C13J = 'X'" +
        " WHERE CIA = '" + @pch_compania + "'" +
          " AND C13A <> 'X'" +
          " AND EXISTS (SELECT 1 FROM vw_fco_hist_traslados_ctr" +
                      " 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 llenar la novedad VCT', 16, 1)
    
        /* CODIGO NUEVA ADMINISTRADORA */                      
        /* EPS */                      
        SET @vvc_sql =
        " UPDATE " + @vsy_tabla +
        " SET C30A = a.cod_super" +
        " FROM vw_fco_administradoras a, fco_traslados_eps t" +
        " WHERE a.codigo = t.EPS_nueva" +
          " AND a.tipo = '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 C30B = a.cod_super" +
        " FROM vw_fco_administradoras a, fco_traslados_fpo t" +
        " WHERE a.codigo = t.FPO_nueva" +
          " AND a.tipo = '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)
    
        /* INSERCION DE LAS AUTORIZACIONES POR EP y AT EN LA TABLA DEL PIE DE REPORTE */                      
        SELECT @vvc_nro_aut = Max(numero), @vdc_vlr_aut = Sum(valor_total)
        FROM fco_autorizaciones
        WHERE compania = @pch_compania
          AND sucursal = @pvc_suc
          AND tipo_entidad = 1
          AND periodo = SubString(@vch_periodo,2,6)

        IF IsNull(@vdc_vlr_aut , 0) > 0
            EXECUTE sp_fco_cambio_ala_datos_adi @vsy_tabla, 2, 'ARP', @pvc_suc, 2, @vvc_nro_aut, @vdc_vlr_aut, null, 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," +
            " 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, 0 C13K, dias_arp C14A," +
            " salario C15A, ibc_arp C17A, 0 C28A, 0 C28B, aporte_arp C29A," +
            " '' C30A, '' C30B, 0 C32B" +
            " FROM fco_fantasmas" +
            " WHERE compania ='" + @pch_compania + "'" +
            "   AND IsNull(dias_arp, 0) > 0 AND IsNull(aporte_arp, 0) > 0" +
            "   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                      

  /* Insertar en la tabla MF_ARP_TOTAL, el número de trabajadores - JAS 20040415 */        
/*
    declare @vdc_nro_trab smallint

   DELETE FROM dbo.MF_ARP_TOTAL WHERE compania = @pch_compania AND anio = @psi_anio and mes = @pti_mes              
              
   SET @vvc_sql = "INSERT INTO dbo.MF_ARP_TOTAL SELECT '" + @pch_compania + "', " + CONVERT(VARCHAR, @psi_anio) + ", " +               
              CONVERT(VARCHAR, @pti_mes) + ", COUNT(DISTINCT C12ABC) FROM " + @vsy_tabla + " GROUP BY C12ABC " +              
              "HAVING NOT (MAX(ISNULL(C13A, ' ')) <> 'X' AND MAX(ISNULL(C13B, ' ')) <> 'X' AND " +              
              "MAX(ISNULL(C13C, ' ')) <> 'X' AND MAX(ISNULL(C13D, ' ')) <> 'X' AND " +              
              "MAX(ISNULL(C13E, ' ')) <> 'X' AND MAX(ISNULL(C13F, ' ')) <> 'X' AND " +              
              "MAX(ISNULL(C13G, ' ')) <> 'X' AND MAX(ISNULL(C13H, ' ')) <> 'X' AND " +              
              "MAX(ISNULL(C13I, ' ')) <> 'X' AND MAX(ISNULL(C13J, ' ')) <> 'X' AND " +              
              "SUM(ISNULL(C14A, 0)) = 0 AND SUM(ISNULL(C17A, 0)) = 0 AND " +              
              "SUM(ISNULL(C29A, 0)) = 0 AND SUM(ISNULL(C13K, 0)) = 0)"              
   EXECUTE (@vvc_sql)              
  SELECT               
    @vdc_nro_trab = SUM(total)              
  FROM              
    dbo.MF_ARP_TOTAL               
  WHERE               
    compania = @pch_compania               
    AND anio = @psi_anio               
    AND mes = @pti_mes              
              
  DELETE FROM               
    dbo.MF_ARP_TOTAL               
  WHERE               
    compania = @pch_compania               
    AND anio = @psi_anio               
    AND mes = @pti_mes              
              
   SET @vvc_sql = "INSERT INTO dbo.MF_ARP_TOTAL VALUES ('" + @pch_compania + "', " + CONVERT(VARCHAR, @psi_anio) + ", " +               
              CONVERT(VARCHAR, @pti_mes) + ", " + CONVERT(VARCHAR, @vdc_nro_trab) + ")"              
   EXECUTE (@vvc_sql)              
*/    
    
    IF @pbi_generar_mm = 1
        EXEC sp_fco_crea_riesgos_mm @pch_compania, @psi_anio, @pti_mes, @pvc_suc                      
END
GO
GRANT EXECUTE ON dbo.sp_fco_crea_riesgos TO PUBLIC
GO
