SET QUOTED_IDENTIFIER OFF
GO
IF OBJECT_ID('dtsp_crea_mm_dian_2006') IS NOT NULL
    DROP PROCEDURE dtsp_crea_mm_dian_2006
GO
CREATE PROCEDURE dtsp_crea_mm_dian_2006
(                      
  @pch_compania    CHAR(4)
)               
-- JCL 12-MAY-07 VERSION PARA DERSA Y GRASCO, ACUMULADOS HASTA AGOSTO, APLICA PARA SENA, ICBF Y CAJAS
       
-- JCL 25-ABR-07
-- VERSION ESPECIAL PARA GENERAR HISTORIA DEL AÑO 2006 EN LAS TABLAS DE LA PLANILLA INTEGRADA (SOLO TABLAS DE TOTALES)
-- LA HISTORIA SE GENERARÁ EN EL MES DE ENERO SIN IMPORTAR A CUANTOS MESES EQUIVALGA
AS                              
DECLARE                              
  /* Variables de Ingresos laborales */                      
  @vsm_tipo_ing       SMALLINT,       /* Tipo de consulta */                      
  @vch_agr_ing        CHAR(3),        /* Agrupación */                      
  @vsm_cnc_ing        SMALLINT,       /* Concepto */                      
  @vdc_tope_ing       DECIMAL(19, 6), /* Valor del tope */                      
  @vch_tipo_ing       VARCHAR(4),     /* Tipo */                      
  @vch_codigo_ing     VARCHAR(4),     /* Código */                      
  @vch_subcodigo_ing  VARCHAR(4),     /* Subcódigo */                      
  @vdc_ingresos       DECIMAL(19, 6), /* Valor */                      
  @vdc_ing_menores    DECIMAL(19, 6), /* Valor */                      
                      
  /* Variables de Aportes Parafiscales */                      
  @vsm_tipo_par       SMALLINT,       /* Tipo de consulta */                      
  @vch_agr_par        CHAR(3),        /* Agrupación */                      
  @vsm_cnc_par        SMALLINT,       /* Concepto */                      
  @vdc_tope_par       DECIMAL(19, 6), /* Valor del tope */                      
  @vch_tipo_par       VARCHAR(4),     /* Tipo */                      
  @vch_codigo_par     VARCHAR(4),     /* Código */                      
  @vch_subcodigo_par  VARCHAR(4),     /* Subcódigo */                      
  @vsm_tipo_sena      SMALLINT,       /* Tipo consulta SENA */                      
  @vch_agr_sena       CHAR(3),        /* Agrupación de SENA */                      
  @vsm_cnc_sena       SMALLINT,       /* Concepto de SENA */                      
  @vch_nom_sena       VARCHAR(30),    /* Nombre de SENA */                      
  @vch_nit_sena       VARCHAR(11),    /* NIT de SENA */                      
  @vch_dir_sena       VARCHAR(50),    /* Dirección de SENA */                      
  @vsm_tipo_icbf      SMALLINT,       /* Tipo consulta ICBF */                      
  @vch_agr_icbf       CHAR(3),        /* Agrupación de ICBF */                      
  @vsm_cnc_icbf       SMALLINT,       /* Concepto de ICBF */                      
  @vch_nom_icbf       VARCHAR(30),    /* Nombre de ICBF */                      
  @vch_nit_icbf       VARCHAR(11),    /* NIT de ICBF */                      
  @vch_dir_icbf       VARCHAR(50),    /* Dirección de ICBF */                      
  @vch_agr_cajas      VARCHAR(10),    /* Agrupacion de CAJAS */                      
                      
  @cch_trabajador     CHAR(10),       /* Código del trabajador */                      
  @cch_nombre         VARCHAR(100),   /* Apellidos del trabajador */                      
  @cch_cedula         VARCHAR(25),    /* Cédula del trabajador */                      
  @cch_domicilio      VARCHAR(100),   /* Dirección del trabajador */                      
  @cdt_ingreso        SMALLDATETIME,  /* Fecha de Ingreso del trabajador */                      
  @cdt_baja           SMALLDATETIME,  /* Fecha de Baja del trabajador */                      
                      
  /* Variable para ARP y EPS */                      
  @vsm_tipo_arp       SMALLINT,       /* Tipo de consulta */       
  @vch_agr_arp        CHAR(3),        /* Agrupación */                      
  @vsm_cnc_arp        SMALLINT,  /* Concepto */                      
  @vdc_tope_arp       DECIMAL(19, 6), /* Valor del tope */            
  @vch_tipo_arp       VARCHAR(4),     /* Tipo */                      
  @vch_codigo_arp     VARCHAR(4),     /* Código */                      
  @vch_subcodigo_arp  VARCHAR(4),     /* Subcódigo */                      
  @vch_nom_arp        VARCHAR(30),    /* Nombre de SENA */                      
  @vch_nit_arp        VARCHAR(11),    /* NIT de SENA */                      
  @vch_dir_arp        VARCHAR(50),    /* Dirección de SENA */                      
                      
  /* Variable para FPO */                      
  @vsm_tipo_fpo SMALLINT,       /* Tipo de consulta */                      
  @vch_agr_fpo        CHAR(3),        /* Agrupación */                      
  @vsm_cnc_fpo        SMALLINT,       /* Concepto */                      
  @vdc_tope_fpo       DECIMAL(19, 6), /* Valor del tope */                      
  @vch_tipo_fpo       VARCHAR(4),     /* Tipo */                      
  @vch_codigo_fpo     VARCHAR(4),     /* Código */                      
  @vch_subcodigo_fpo  VARCHAR(4),     /* Subcódigo */                      
  @vch_nom_fpo        VARCHAR(30),    /* Nombre de SENA */                      
  @vch_nit_fpo        VARCHAR(11),    /* NIT de SENA */                      
  @vch_dir_fpo        VARCHAR(50),    /* Dirección de SENA */                      
                                                                  
  @vch_per_hasta      VARCHAR(6),     /* Límite de periodo de consulta */                      
  @vch_per            VARCHAR(6),     /* Periodo de consulta */                      
  @vsm_anio           SMALLINT,       /* Año de consulta */                      
  @vti_mes            TINYINT,        /* Mes de consulta */                      
  @vch_sql            VARCHAR(2000),  /* Consulta SQL */                      
  @vvc_tabla          VARCHAR(200),   /* Tabla para EPS, FPO y ARP */                      
  @vti_existe         TINYINT,        /* Flag de existencia de tabla */                      
                  
  @vch_usuario        VARCHAR(20),    /* Usuario de las tablas */                  
  @vti_estatus        TINYINT,        /* Estado del pago */                  
  @vdc_saldo          DECIMAL(19, 2), /* Saldo del crédito */                  
  @vdt_fecha_venc  SMALLDATETIME,  /* Fecha vencimiento */                
  @vdt_fecha_pago     SMALLDATETIME,  /* Fecha pago */                
  @vdc_amortizacion   DECIMAL(19, 2), /* Amortizacion del crédito */                  
  @vti_arp_iss TINYINT,                  
                
  /* Variables revisión JCL 2006 - JCL - 20060329 */        
  @vvc_nom_1 varchar(20),        
  @vvc_nom_2 varchar(20),        
  @vvc_ape_1 varchar(20),        
  @vvc_ape_2 varchar(20),        
  @vch_mun char(3),        
  @vch_dpto char(2),        
  @vvc_tdoc varchar(10),        
  @vvc_raz varchar(60),        
  @cch_dv char(1)        
        
BEGIN                              
  /* Usuario */                  
  IF IS_MEMBER('db_owner') = 0                            
  SET @vch_usuario = SYSTEM_USER                  
  ELSE                  
    IF IS_SRVROLEMEMBER ('sysadmin') = 0                
      SET @vch_usuario = SYSTEM_USER                  
    ELSE                
      SET @vch_usuario = 'dbo'                  
                  
  /* Borrar datos actuales */                      
  DELETE FROM dttb_mf_medio_dian                      
  DELETE FROM mf_dian_det WHERE compania = @pch_compania AND anio = 2006        
                   
--  SET @vch_per_hasta = CONVERT(VARCHAR(4), 2006) + '12'                      
    SELECT @vch_per_hasta = RTrim(2006) + 
        Case @pch_compania When 'DETE' Then '08'
                           When 'GRAS' Then '08'
                           When 'GRAC' Then '09'
                           When 'JABC' Then '12'
        End
                                  
  /* Traer parámetros de Parafiscales */                      
  SELECT                       
    @vsm_tipo_par = tipo_consulta,                      
    @vdc_tope_par = ISNULL(tope, 0),                      
    @vch_tipo_par = tipo,                      
    @vch_codigo_par = codigo,                      
    @vch_subcodigo_par = subcodigo                      
  FROM                       
    dttb_mf_parametros_dian                       
  WHERE                       
    compania = @pch_compania                       
    AND secuencia = 2                      
                      
  IF @vsm_tipo_par = 0                      
  BEGIN                      
    SET @vch_agr_par = ''                      
    SELECT                       
      @vsm_cnc_par = dato                      
    FROM                      
      dttb_mf_parametros_dian                      
    WHERE                      
      compania = @pch_compania                      
      AND secuencia = 2                      
  END                      
  ELSE                      
    IF @vsm_tipo_par = 1                      
    BEGIN                      
      SET @vsm_cnc_par = 0                      
      SELECT                       
        @vch_agr_par = dato                      
      FROM                      
        dttb_mf_parametros_dian                      
      WHERE                      
      compania = @pch_compania                      
        AND secuencia = 2                      
    END                      
    ELSE                      
    BEGIN                      
      SET @vsm_cnc_par = 0                      
      SET @vch_agr_par = ''                      
    END                      
                      
  SELECT                      
    @vsm_tipo_sena = tipo                      
  FROM                       
    parametros_mf                       
  WHERE                       
    compania = @pch_compania                       
    AND parametro = 'parafiscal'                      
    AND secuencia = 3                      
                      
  IF @vsm_tipo_sena = 0                      
  BEGIN               
    SET @vch_agr_sena = ''                      
    SELECT                      
      @vsm_cnc_sena = dato                      
    FROM                      
      parametros_mf                      
    WHERE                      
      compania = @pch_compania                      
      AND parametro = 'parafiscal'                      
      AND secuencia = 3                      
  END                      
  ELSE                      
  BEGIN                      
    SET @vsm_cnc_sena = 0                      
    SELECT                      
      @vch_agr_sena = dato                      
    FROM                      
      parametros_mf                      
    WHERE                      
      compania = @pch_compania                        
      AND parametro = 'parafiscal'                      
      AND secuencia = 3        
  END                      
                      
  SELECT                      
    @vsm_tipo_icbf = tipo                      
  FROM                       
    parametros_mf                       
  WHERE                       
    compania = @pch_compania                       
    AND parametro = 'parafiscal'                      
    AND secuencia = 4                      
                      
  IF @vsm_tipo_icbf = 0                      
  BEGIN                      
    SET @vch_agr_icbf = ''                      
    SELECT                      
      @vsm_cnc_icbf = dato                      
    FROM                      
      parametros_mf            
    WHERE                      
      compania = @pch_compania                      
      AND parametro = 'parafiscal'                      
      AND secuencia = 4                      
  END                      
  ELSE                      
  BEGIN                      
    SET @vsm_cnc_icbf = 0                      
    SELECT                      
      @vch_agr_icbf = dato                      
    FROM                      
      parametros_mf                      
    WHERE                      
      compania = @pch_compania                      
      AND parametro = 'parafiscal'                      
      AND secuencia = 4                      
  END                      
                      
  SELECT                       
    @vch_nom_sena = nombre_SENA,                      
    @vch_nit_sena = nit_SENA,                      
    @vch_dir_sena = dir_SENA,                      
    @vch_nom_icbf = nombre_ICBF,                      
    @vch_nit_icbf = nit_ICBF,                      
    @vch_dir_icbf = dir_ICBF,                      
    @vch_nom_arp = nombre_ARP,                      
    @vch_nit_arp = nit_ARP,                      
    @vch_dir_arp = dir_ARP                      
  FROM                      
    companias_datos_adi                      
  WHERE                      
    compania = @pch_compania                      
                      
  SELECT                      
    @vch_agr_cajas = dato                      
  FROM                       
    parametros_mf                       
  WHERE                       
    compania = @pch_compania                       
    AND parametro = 'parafiscal'                      
    AND secuencia = 1                      
                      
  /* Traer parámetros de ARP y EPS */                      
  SELECT                       
    @vsm_tipo_arp = tipo_consulta,                      
    @vdc_tope_arp = ISNULL(tope, 0),                      
    @vch_tipo_arp = tipo,                      
    @vch_codigo_arp = codigo,                      
    @vch_subcodigo_arp = subcodigo                      
  FROM                       
   dttb_mf_parametros_dian                       
  WHERE                       
    compania = @pch_compania                       
    AND secuencia = 3                      
                      
  IF @vsm_tipo_arp = 0                      
  BEGIN                      
    SET @vch_agr_arp = ''                      
    SELECT                       
      @vsm_cnc_arp = dato                      
    FROM                      
      dttb_mf_parametros_dian                      
    WHERE                      
      compania = @pch_compania                      
      AND secuencia = 3                      
  END                      
  ELSE                          
    IF @vsm_tipo_arp = 1                      
    BEGIN                      
      SET @vsm_cnc_arp = 0                      
      SELECT                       
        @vch_agr_arp = dato                      
      FROM                      
        dttb_mf_parametros_dian                      
      WHERE                      
        compania = @pch_compania                      
        AND secuencia = 3                      
    END                      
    ELSE                      
    BEGIN                      
      SET @vsm_cnc_arp = 0                      
      SET @vch_agr_arp = 0                      
    END                        
                  
  /* Traer parámetros de FPO */                      
  SELECT                       
    @vsm_tipo_fpo = tipo_consulta,                  
    @vdc_tope_fpo = ISNULL(tope, 0),                      
    @vch_tipo_fpo = tipo,                      
    @vch_codigo_fpo = codigo,                      
    @vch_subcodigo_fpo = subcodigo                      
  FROM                       
    dttb_mf_parametros_dian                       
  WHERE                       
    compania = @pch_compania                       
    AND secuencia = 4                      
                      
  IF @vsm_tipo_fpo = 0                      
  BEGIN                      
    SET @vch_agr_fpo = ''                      
    SELECT                       
@vsm_cnc_fpo = dato                      
    FROM                      
      dttb_mf_parametros_dian                      
    WHERE                      
      compania = @pch_compania                      
      AND secuencia = 4                      
  END         
  ELSE                      
    IF @vsm_tipo_fpo = 1                      
    BEGIN                      
      SET @vsm_cnc_fpo = 0                      
      SELECT                       
        @vch_agr_fpo = dato                      
      FROM                      
        dttb_mf_parametros_dian                      
      WHERE                      
        compania = @pch_compania                      
        AND secuencia = 4                      
    END                      
    ELSE                      
    BEGIN                      
      SET @vsm_cnc_fpo = 0                      
      SET @vch_agr_fpo = 0                      
    end                 
     
  /* Registrar los aportes al SENA */                      
  SET @vdc_ingresos = 0                      
  SELECT                               
    @vdc_ingresos =                              
    SUM(acum_imp_mes_01) + SUM(acum_imp_mes_02) + SUM(acum_imp_mes_03) + SUM(acum_imp_mes_04) +                              
    SUM(acum_imp_mes_05) + SUM(acum_imp_mes_06) + SUM(acum_imp_mes_07) + SUM(acum_imp_mes_08)
/*    SUM(acum_imp_mes_09) + SUM(acum_imp_mes_10) + SUM(acum_imp_mes_11) + SUM(acum_imp_mes_12) */ -- JCL 12-MAY-07
  FROM                              
    acumulados_mensuales                              
  WHERE                              
   compania = @pch_compania AND                      
    anio = 2006 AND                      
    agr_conceptos = @vch_agr_sena AND                       
    concepto = @vsm_cnc_sena                      
                      
    IF ISNULL(@vdc_ingresos, 0) > @vdc_tope_par                      
    BEGIN             
/*
        INSERT INTO dttb_mf_medio_dian (secuencia, registro)        
        VALUES (5, @vch_tipo_par + @vch_codigo_par + @vch_subcodigo_par + ',NIT,' + ISNULL(Left(@vch_nit_sena,9), ' ') +        
                ',' + ISNULL(Right(@vch_nit_sena,1), ' ') + ',,,,,' + ISNULL(RTRIM(@vch_nom_sena), ' ') + ',' +         
                ISNULL(@vch_dir_sena, ' ') + ',' + '11,001,' + RTrim(ROUND(@vdc_ingresos, 0)))        
*/
        INSERT INTO fco_integrada_rt07 (compania, anio, mes, sucursal, secuencia, nit, dv, valor, dias_mora, int_mora, nro_personas)
        SELECT @pch_compania, 2006, 1, '', 1, '', '', ROUND(@vdc_ingresos, 0), 0, 0, 0        
    END        
                      
  /* Registrar los aportes al ICBF */                      
  SET @vdc_ingresos = 0                      
  SELECT                               
    @vdc_ingresos =                              
    SUM(acum_imp_mes_01) + SUM(acum_imp_mes_02) + SUM(acum_imp_mes_03) + SUM(acum_imp_mes_04) +                              
    SUM(acum_imp_mes_05) + SUM(acum_imp_mes_06) + SUM(acum_imp_mes_07) + SUM(acum_imp_mes_08)
    /*SUM(acum_imp_mes_09) + SUM(acum_imp_mes_10) + SUM(acum_imp_mes_11) + SUM(acum_imp_mes_12) */-- JCL 12-MAY-07
  FROM              
    acumulados_mensuales                              
  WHERE                              
    compania = @pch_compania AND                      
    anio = 2006 AND                    
    agr_conceptos = @vch_agr_icbf AND                       
    concepto = @vsm_cnc_icbf                      
                      
    IF ISNULL(@vdc_ingresos, 0) > @vdc_tope_par                      
    BEGIN                      
/*
        INSERT INTO dttb_mf_medio_dian (secuencia, registro)        
        VALUES (6, @vch_tipo_par + @vch_codigo_par + @vch_subcodigo_par + ',NIT,' + ISNULL(Left(@vch_nit_icbf,9), ' ') +        
                ',' + ISNULL(Right(@vch_nit_icbf,1), ' ') + ',,,,,' + ISNULL(RTRIM(@vch_nom_icbf), ' ') + ',' +         
                ISNULL(@vch_dir_icbf, ' ') + ',' + '11,001,' + RTrim(ROUND(@vdc_ingresos, 0)))        
*/
        INSERT INTO fco_integrada_rt08 (compania, anio, mes, sucursal, secuencia, nit, dv, valor, dias_mora, int_mora, nro_personas)
        SELECT @pch_compania, 2006, 1, '', 1, '', '', ROUND(@vdc_ingresos, 0), 0, 0, 0        
    END                      
                      
  /* Registrar los aportes a las cajas */                      
  SET @vdc_ingresos = 0                      
                      
  IF @vsm_tipo_par = 2                      
  BEGIN                      
    /* Cursor de todas las cajas */                      
    DECLARE cr_cajas CURSOR FOR                              
    SELECT                               
      a.codigo, b.descripcion, ISNULL(a.registro_fiscal, ''), ISNULL(a.digito_verificacion, ''), ISNULL(a.direccion, '')                      
    FROM                              
      administradoras a, datos_agr_trab b                      
    WHERE      
      a.codigo = b.dato And                              
      a.tipo = 'PAR' And                              
      b.agrupacion = @vch_agr_cajas                      
    ORDER BY b.descripcion                      
                      
    OPEN cr_cajas                      
                        
    FETCH NEXT FROM cr_cajas                      
    INTO @cch_trabajador, @cch_nombre, @cch_cedula, @cch_dv, @cch_domicilio                      
                      
    WHILE @@fetch_status = 0                              
    BEGIN                              
      SELECT                               
        @vdc_ingresos =                              
        SUM(acum_imp_mes_01) + SUM(acum_imp_mes_02) + SUM(acum_imp_mes_03) + SUM(acum_imp_mes_04) +                              
        SUM(acum_imp_mes_05) + SUM(acum_imp_mes_06) + SUM(acum_imp_mes_07) + SUM(acum_imp_mes_08) 
        /*SUM(acum_imp_mes_09) + SUM(acum_imp_mes_10) + SUM(acum_imp_mes_11) + SUM(acum_imp_mes_12) */ -- JCL 12-MAY-07
      FROM                              
        acumulados_mensuales                              
      WHERE                              
        compania = @pch_compania AND                      
        anio = 2006 AND                    
        concepto IN                       
        (SELECT concepto FROM dttb_mf_rel_adm_conceptos WHERE compania = @pch_compania AND codigo = @cch_trabajador)                      
                      
      IF ISNULL(@vdc_ingresos, 0) > @vdc_tope_par                      
      BEGIN                      
/*
        INSERT INTO dttb_mf_medio_dian (secuencia, registro)        
        VALUES (7, @vch_tipo_par + @vch_codigo_par + @vch_subcodigo_par + ',NIT,' + ISNULL(@cch_cedula, '') +        
                ',' + ISNULL(@cch_dv, '') + ',,,,,' + ISNULL(RTRIM(@cch_nombre), ' ') + ',' +         
                ISNULL(@cch_domicilio, ' ') + ',' + '11,001,' + RTrim(ROUND(@vdc_ingresos, 0)))        
*/
        INSERT INTO fco_integrada_rt06 (compania, anio, mes, sucursal, secuencia, cod_CCF, nit, dv, suma_aportes, dias_mora, int_mora, total_afiliados)
        SELECT @pch_compania, 2006, 1, '', 1, @cch_trabajador, '', '', ROUND(@vdc_ingresos, 0), 0, 0, 0        

      END                      
                      
      FETCH NEXT FROM cr_cajas                      
      INTO @cch_trabajador, @cch_nombre, @cch_cedula, @cch_dv, @cch_domicilio        
    END                      
    CLOSE cr_cajas                      
    DEALLOCATE cr_cajas                      
  END                      
  ELSE                      
  BEGIN                      
        raiserror ('el programa espera que el parametro dian de cajas sea tipo 2', 16, 1)
        return
    END                      
  END                      
                      
  /* Registrar los aportes de ARP */                      
  SET @vdc_ingresos = 0                      
  SET @vsm_anio = 2006                  
  SET @vti_mes = 1                  
  SET @vch_per = CONVERT(VARCHAR(4), 2006) + '01'                      
  CREATE TABLE ##TEMP_INGRESOS (VALOR DECIMAL(19, 6))                      
                      
  WHILE @vch_per <= @vch_per_hasta                      
  BEGIN                      
    SET @vvc_tabla = 'FCO_ARP_' + LTRIM(RTRIM(@pch_compania)) + '_' + CONVERT(VARCHAR, @vsm_anio) + '_' + CONVERT(VARCHAR, @vti_mes)                      
    SET @vti_existe = 0                          
                      
    IF IS_MEMBER('db_owner') = 0                            
    BEGIN                          
      IF EXISTS (SELECT 1 FROM Information_Schema.Tables                          
      WHERE Table_Type = 'BASE TABLE' AND Table_Name = UPPER(@vvc_tabla)                          
      AND Table_Schema = SYSTEM_USER)                          
      BEGIN                      
        SET @vti_existe = 1                          
        SET @vvc_tabla = SYSTEM_USER + '.' + @vvc_tabla                       
      END                      
      ELSE                      
      BEGIN                      
        SET @vti_existe = 1                          
        SET @vvc_tabla = @vvc_tabla                       
      END                    
    END                       
    ELSE                          
      IF EXISTS (SELECT 1 FROM Information_Schema.Tables                          
      WHERE Table_Type = 'BASE TABLE' AND Table_Name = UPPER(@vvc_tabla))                     
        SET @vti_existe = 1                          
                  
    SELECT @vti_arp_iss = arp_iss FROM companias_datos_adi WHERE compania = @pch_compania                   
                      
    IF @vti_existe = 1 AND @vti_arp_iss = 0                  
    BEGIN                      
      SET @vch_sql =                      
      'INSERT INTO ##TEMP_INGRESOS SELECT SUM(ISNULL(C29A, 0)) FROM ' + @vvc_tabla                      
      EXECUTE (@vch_sql)                      
      SET @vch_sql =                      
      "INSERT INTO ##TEMP_INGRESOS SELECT -1 * Sum(ISNULL(A.valor_1,0)) " +                      
      "FROM fco_ala_datos_adi A, fco_rel_sgss_da B " +                      
      "WHERE A.sgss = B.sgss and A.item = B.ala_datos_adi " +                      
      "AND RTrim(A.tabla) = '" + @vvc_tabla + "' " +                      
      "AND A.valor_1 <> 0 " +                      
      "AND B.secuencia = 1 " +                      
      "GROUP BY B.secuencia "                      
      EXECUTE (@vch_sql)                      
    END                      
    ELSE           
    IF @vti_arp_iss = 1 /* SCAV - 20060420  aplica solo si la arp actual es ISS */                  
    BEGIN                      
      SET @vvc_tabla = 'FCO_VW_ISS_' + LTRIM(RTRIM(@pch_compania)) + '_' + CONVERT(VARCHAR, @vsm_anio) + '_' + CONVERT(VARCHAR, @vti_mes)                      
      SET @vch_sql =                      
      "INSERT INTO ##TEMP_INGRESOS SELECT CASE WHEN B.secuencia IN (0, 2, 3) THEN 1 ELSE -1 END * Sum(ISNULL(A.valor_3,0)) " +                      
      "FROM fco_ala_datos_adi A, fco_rel_sgss_da B " +                      
      "WHERE A.sgss = B.sgss and A.item = B.ala_datos_adi " +                      
      "AND RTrim(A.tabla) = '" + @vvc_tabla + "' " +                      
      "AND RTrim(A.cod_adm) = 'ISS' " +                      
      "AND A.valor_3 <> 0 " +                      
      "AND B.secuencia IN (0, 2, 3, 4) " +                      
      "GROUP BY B.secuencia "                      
      EXECUTE (@vch_sql)                      
    END                      
                      
    IF @vti_mes = 12                      
    BEGIN                      
      SET @vti_mes = 1                      
      SET @vsm_anio = @vsm_anio + 1                      
    END                      
    ELSE                      
    BEGIN                      
      SET @vti_mes = @vti_mes + 1                      
    END                      
                      
    IF @vti_mes > 9          
      SET @vch_per = CONVERT(VARCHAR(4), @vsm_anio) + CONVERT(VARCHAR, @vti_mes)                      
    ELSE                      
      SET @vch_per = CONVERT(VARCHAR(4), @vsm_anio) + '0' + CONVERT(VARCHAR, @vti_mes)                      
  END                      
                      
  SELECT @vdc_ingresos = SUM(ISNULL(VALOR, 0)) FROM ##TEMP_INGRESOS                      
  DROP TABLE ##TEMP_INGRESOS                      
                      
  IF ISNULL(@vdc_ingresos, 0) > @vdc_tope_arp                      
  BEGIN                      
/*
        INSERT INTO dttb_mf_medio_dian (secuencia, registro)        
        VALUES (9, @vch_tipo_arp + @vch_codigo_arp + @vch_subcodigo_arp + ',NIT,' + ISNULL(Left(@vch_nit_arp, 9), '') +        
                ',' + ISNULL(Right(@vch_nit_arp, 1), '') + ',,,,,' + ISNULL(RTRIM(@vch_nom_arp), ' ') + ',' +         
                ISNULL(@vch_dir_arp, ' ') + ',' + '11,001,' + RTrim(ROUND(@vdc_ingresos, 0)))        
*/
        INSERT INTO fco_integrada_rt05
        (compania, anio, mes, sucursal, cod_ARP, secuencia, nit, dv, suma_aportes, nro_aut, incapacidades, otros_riesgos, dias_mora, int_mora, nro_radicacion, saldo_favor, FSRP, total_afiliados, origen)
        SELECT @pch_compania, 2006, 1, '', 'ARP', 1, '', '', ROUND(@vdc_ingresos, 0), '', 0, 0, 0, 0, '', 0, 0, 0, 0
  END                      
                      
  /* Registrar los aportes de EPS */                      
  SET @vdc_ingresos = 0                      
  SET @vsm_anio = 2006                   
  SET @vti_mes = 1                  
  SET @vch_per = '200601'                      
  CREATE TABLE TEMP_EPS (COD_EPS VARCHAR(10), VALOR DECIMAL(19, 6))                    
                    
  WHILE @vch_per <= @vch_per_hasta                      
  BEGIN                      
    SET @vvc_tabla = 'FCO_EPS_' + LTRIM(RTRIM(@pch_compania)) + '_' + CONVERT(VARCHAR, @vsm_anio) + '_' + CONVERT(VARCHAR, @vti_mes)                      
    SET @vti_existe = 0                          
    IF IS_MEMBER('db_owner') = 0                            
    BEGIN                          
      IF EXISTS (SELECT 1 FROM Information_Schema.Tables                          
      WHERE Table_Type = 'BASE TABLE' AND Table_Name = UPPER(@vvc_tabla)                          
      AND Table_Schema = SYSTEM_USER)                          
      BEGIN                      
        SET @vti_existe = 1                          
        SET @vvc_tabla = SYSTEM_USER + '.' + @vvc_tabla                       
      END                    
      ELSE                      
      BEGIN                      
        SET @vti_existe = 1                          
        SET @vvc_tabla = @vvc_tabla                       
      END                    
    END                       
    ELSE                          
      IF EXISTS (SELECT 1 FROM Information_Schema.Tables                          
      WHERE Table_Type = 'BASE TABLE' AND Table_Name = UPPER(@vvc_tabla))                          
      BEGIN                    
        SET @vti_existe = 1                          
      END                    
    IF @vti_existe = 1                       
    BEGIN                      
      /* ACUMULADO APORTES EPS */        
      SET @vch_sql =                      
      'INSERT INTO ' + @vch_usuario + '.TEMP_EPS SELECT COD_EPS, /*(8.000/12.000) *  */ SUM(ISNULL(C24A, 0)) FROM ' + @vvc_tabla + ' GROUP BY COD_EPS'                      
      EXECUTE (@vch_sql)                      
                  
      /* ACUMULADO INCAPACIDADES */        
      SET @vch_sql =                      
      "INSERT INTO " + @vch_usuario + ".TEMP_EPS SELECT A.cod_adm, (-1) * Sum(ISNULL(A.valor_1,0)) " +                      
      "FROM fco_ala_datos_adi A, fco_rel_sgss_da B " +                      
      "WHERE A.sgss = B.sgss and A.item = B.ala_datos_adi " +                      
      "AND RTrim(A.tabla) = '" + @vvc_tabla + "' " +                      
      "AND A.valor_1 <> 0 " +                      
      "AND B.secuencia IN (1, 2) " +                      
      "GROUP BY B.secuencia, A.cod_adm "                      
      EXECUTE (@vch_sql)                   
        
      /* ACUMULADO UPC - SCA - 20060322 */        
      SET @vch_sql =                      
      'INSERT INTO ' + @vch_usuario + '.TEMP_EPS SELECT COD_EPS, SUM(ISNULL(C27A, 0)) FROM ' + @vvc_tabla + ' GROUP BY COD_EPS'                      
      EXECUTE (@vch_sql)                      
           
    END                      
                    
    SET @vvc_tabla = 'FCO_VW_ISS_' + LTRIM(RTRIM(@pch_compania)) + '_' + CONVERT(VARCHAR, @vsm_anio) + '_' + CONVERT(VARCHAR, @vti_mes)                      
    SET @vti_existe = 0                          
             
    IF IS_MEMBER('db_owner') = 0                            
    BEGIN                          
      IF EXISTS (SELECT 1 FROM Information_Schema.Tables                          
      WHERE Table_Type = 'BASE TABLE' AND Table_Name = UPPER(@vvc_tabla)                          
      AND Table_Schema = SYSTEM_USER)                          
      BEGIN                      
        SET @vti_existe = 1                          
        SET @vvc_tabla = SYSTEM_USER + '.' + @vvc_tabla                       
      END                      
      ELSE                      
      BEGIN                      
        SET @vti_existe = 1                          
        SET @vvc_tabla = @vvc_tabla                       
      END                    
    END                       
    ELSE                          
      IF EXISTS (SELECT 1 FROM Information_Schema.Tables                          
      WHERE Table_Type = 'BASE TABLE' AND Table_Name = UPPER(@vvc_tabla))                          
        SET @vti_existe = 1                          
                      
    IF @vti_existe = 1                       
    BEGIN                      
      /* ACUMULADO APORTES EPS */        
      SET @vch_sql =                      
      "INSERT INTO " + @vch_usuario + ".TEMP_EPS SELECT COD_EPS, /*(8.000/12.000) * */ SUM(ISNULL(C14C, 0)) FROM " + @vvc_tabla + " WHERE COD_EPS = 'ISS' GROUP BY COD_EPS"                       
      EXECUTE (@vch_sql)                      
        
      /* ACUMULADO INCAPACIDADES */        
      SET @vch_sql =                      
      "INSERT INTO " + @vch_usuario + ".TEMP_EPS SELECT 'ISS', CASE WHEN B.secuencia IN (3, 4, 5) THEN -1 ELSE 1 END * Sum(ISNULL(A.valor_2, 0)) " +                      
      "FROM fco_ala_datos_adi A, fco_rel_sgss_da B " +                      
      "WHERE A.sgss = B.sgss and A.item = B.ala_datos_adi " +                      
      "AND RTrim(A.tabla) = '" + @vvc_tabla + "' " +                      
      "AND RTrim(A.cod_adm) = 'ISS' " +                      
      "AND A.valor_2 <> 0 " +                      
      "AND B.secuencia IN (2, 3, 4, 5) " +                      
      "GROUP BY B.secuencia "                      
      EXECUTE (@vch_sql)                      
    END                      
      
      /* ACUMULADO ARP -ISS -SCA 20060420 APLICA CUANDO HA HABIDO CAMBIO DE ARP EN EL TRANSCURSO DEL AÑO DEL ISS A OTRA */        
      IF @vti_arp_iss = 0      
      BEGIN      
        SET @vch_sql =                      
        "INSERT INTO " + @vch_usuario + ".TEMP_EPS SELECT 'ISS', CASE WHEN B.secuencia IN (0, 2, 3) THEN 1 ELSE -1 END * Sum(ISNULL(A.valor_3,0)) " +                       
        "FROM fco_ala_datos_adi A, fco_rel_sgss_da B " +                      
        "WHERE A.sgss = B.sgss and A.item = B.ala_datos_adi " +                      
        "AND RTrim(A.tabla) = '" + @vvc_tabla + "' " +                      
        "AND RTrim(A.cod_adm) = 'ISS' " +                      
        "AND A.valor_3 <> 0 " +                      
        "AND B.secuencia IN (0, 2, 3, 4) " +                      
        "GROUP BY B.secuencia "                      
        EXECUTE (@vch_sql)                   
      END      
                      
    IF @vti_mes = 12                      
    BEGIN                      
      SET @vti_mes = 1                      
      SET @vsm_anio = @vsm_anio + 1                      
    END                      
    ELSE                      
    BEGIN                      
      SET @vti_mes = @vti_mes + 1                      
    END                      
                      
    IF @vti_mes > 9                       
      SET @vch_per = CONVERT(VARCHAR(4), @vsm_anio) + CONVERT(VARCHAR, @vti_mes)                      
    ELSE                      
      SET @vch_per = CONVERT(VARCHAR(4), @vsm_anio) + '0' + CONVERT(VARCHAR, @vti_mes)                      
  END                      
                      
  /* Cursor de todas las eps */                      
  SET @vch_sql =                       
  "SELECT " +                    
  " a.codigo, b.descripcion, ISNULL(a.registro_fiscal, ''), ISNULL(a.digito_verificacion, ''), ISNULL(a.direccion, ''), SUM(ISNULL(c.valor, 0)) " +                    
  " FROM " +         
  " administradoras a, datos_agr_trab b, " + @vch_usuario + ".TEMP_EPS c " +                    
  " WHERE " +                    
  " a.codigo = b.dato And " +                    
  " a.tipo = 'EPS' And " +                             
  " b.agrupacion = 'EPS' And " +                    
  " c.cod_eps = a.codigo " +                    
  " GROUP BY " +                    
  " a.codigo, b.descripcion, ISNULL(a.registro_fiscal, ''), ISNULL(a.digito_verificacion, ''), ISNULL(a.direccion, '') " +                     
  " ORDER BY " +                    
  " b.descripcion "                    
                    
  EXECUTE('DECLARE cr_eps CURSOR FOR ' + @vch_sql)                    
  OPEN cr_eps                      
                        
  FETCH NEXT FROM cr_eps          
  INTO @cch_trabajador, @cch_nombre, @cch_cedula, @cch_dv, @cch_domicilio, @vdc_ingresos                      
                      
  WHILE @@fetch_status = 0                              
  BEGIN                              
    IF ISNULL(@vdc_ingresos, 0) > @vdc_tope_arp                      
    BEGIN                   
/*
        INSERT INTO dttb_mf_medio_dian (secuencia, registro)        
        VALUES (10, @vch_tipo_arp + @vch_codigo_arp + @vch_subcodigo_arp + ',NIT,' + ISNULL(@cch_cedula, '') +        
                ',' + ISNULL(@cch_dv, '') + ',,,,,' + ISNULL(RTRIM(@cch_nombre), ' ') + ',' +         
                ISNULL(@cch_domicilio, ' ') + ',' + '11,001,' + RTrim(ROUND(@vdc_ingresos, 0)))        
*/
        INSERT INTO fco_integrada_rt04 
        (compania, anio, mes, sucursal, cod_EPS, secuencia, nit, dv, suma_aportes, suma_UPC, nro_aut_IGE, suma_aut_IGE, 
         nro_aut_LMA, suma_aut_LMA, dias_mora, int_mora_aportes, int_mora_UPC, nro_radicacion, saldo_favor_aportes, saldo_favor_UPC, FOSYGA, total_afiliados, origen)
        SELECT @pch_compania, 2006, 1, '', @cch_trabajador, 1, '', '', ROUND(@vdc_ingresos, 0), 0, '', 0, '', 0, 0, 0, 0, '', 0, 0, 0, 0, 0
    END                      
                      
    FETCH NEXT FROM cr_eps                      
    INTO @cch_trabajador, @cch_nombre, @cch_cedula, @cch_dv, @cch_domicilio, @vdc_ingresos                      
  END                    
  CLOSE cr_eps                      
  DEALLOCATE cr_eps                      
                      
  DROP TABLE TEMP_EPS                      
                      
  /* Registrar los aportes de FPO */                      
  SET @vdc_ingresos = 0                      
  SET @vsm_anio = 2006                  
  SET @vti_mes = 1                  
  SET @vch_per = CONVERT(VARCHAR(4), 2006) + '01'                      
  CREATE TABLE TEMP_FPO (COD_EPS VARCHAR(10), VALOR DECIMAL(19, 6))                      
                    
  WHILE @vch_per <= @vch_per_hasta                      
  BEGIN                      
    SET @vvc_tabla = 'FCO_FPO_' + LTRIM(RTRIM(@pch_compania)) + '_' + CONVERT(VARCHAR, @vsm_anio) + '_' + CONVERT(VARCHAR, @vti_mes)                      
    SET @vti_existe = 0                          
                      
    IF IS_MEMBER('db_owner') = 0                            
    BEGIN                          
      IF EXISTS (SELECT 1 FROM Information_Schema.Tables                          
      WHERE Table_Type = 'BASE TABLE' AND Table_Name = UPPER(@vvc_tabla)                          
      AND Table_Schema = SYSTEM_USER)                          
      BEGIN                      
        SET @vti_existe = 1                          
        SET @vvc_tabla = SYSTEM_USER + '.' + @vvc_tabla                       
      END                      
      ELSE                      
      BEGIN                      
        SET @vti_existe = 1                          
        SET @vvc_tabla = @vvc_tabla                       
      END                    
    END                       
    ELSE                          
      IF EXISTS (SELECT 1 FROM Information_Schema.Tables                          
      WHERE Table_Type = 'BASE TABLE' AND Table_Name = UPPER(@vvc_tabla))                          
        SET @vti_existe = 1                          
                      
    IF @vti_existe = 1                       
    BEGIN                      
     /* ACUMULADO APORTE FONDO DE PENSION */        
      SET @vch_sql =                      
      'INSERT INTO ' + @vch_usuario + '.TEMP_FPO SELECT COD_FPO, /*(10.125/13.5) * */ SUM(ISNULL(C19A, 0)) FROM ' + @vvc_tabla + ' GROUP BY COD_FPO'                      
      EXECUTE (@vch_sql)                  
        
     /* ACUMULADO APORTE FONDO DE SOLIDARIDAD */        
      SET @vch_sql =                      
      'INSERT INTO ' + @vch_usuario + '.TEMP_FPO SELECT COD_FPO, SUM(ISNULL(C22A, 0)) FROM ' + @vvc_tabla + ' GROUP BY COD_FPO'                      
      EXECUTE (@vch_sql)                  
    END                      
                      
    SET @vvc_tabla = 'FCO_VW_ISS_' + LTRIM(RTRIM(@pch_compania)) + '_' + CONVERT(VARCHAR, @vsm_anio) + '_' + CONVERT(VARCHAR, @vti_mes)                      
    SET @vti_existe = 0                          
                      
    IF IS_MEMBER('db_owner') = 0                            
    BEGIN                          
      IF EXISTS (SELECT 1 FROM Information_Schema.Tables                          
      WHERE Table_Type = 'BASE TABLE' AND Table_Name = UPPER(@vvc_tabla)                          
      AND Table_Schema = SYSTEM_USER)                          
      BEGIN                      
        SET @vti_existe = 1                          
        SET @vvc_tabla = SYSTEM_USER + '.' + @vvc_tabla                       
      END                      
      ELSE                      
      BEGIN                      
        SET @vti_existe = 1                          
        SET @vvc_tabla = @vvc_tabla                       
      END                    
    END                       
    ELSE                          
      IF EXISTS (SELECT 1 FROM Information_Schema.Tables                          
      WHERE Table_Type = 'BASE TABLE' AND Table_Name = UPPER(@vvc_tabla))                          
        SET @vti_existe = 1                          
                      
    IF @vti_existe = 1                       
    BEGIN                      
     /* ACUMULADO APORTE FONDO DE PENSION ISS*/        
      SET @vch_sql =                      
      "INSERT INTO " + @vch_usuario + ".TEMP_FPO SELECT COD_FPO, /*(10.125/13.5) * */ SUM(ISNULL(C14A, 0)) FROM " + @vvc_tabla + " WHERE COD_FPO = 'ISS' GROUP BY COD_FPO"                       
      EXECUTE (@vch_sql)        
     /* ACUMULADO APORTE FONDO DE SOLIDARIDAD ISS -SCA 20060420 */        
      SET @vch_sql =                      
      "INSERT INTO " + @vch_usuario + ".TEMP_FPO SELECT COD_FPO, /*(10.125/13.5) * */ SUM(ISNULL(C14B, 0)) FROM " + @vvc_tabla + " WHERE COD_FPO = 'ISS' GROUP BY COD_FPO"                       
      EXECUTE (@vch_sql)        
                    
      SET @vch_sql =                      
      "INSERT INTO " + @vch_usuario + ".TEMP_FPO SELECT 'ISS', CASE WHEN B.secuencia IN (3) THEN -1 ELSE 1 END * Sum(ISNULL(A.valor_2, 0)) " +                      
      "FROM fco_ala_datos_adi A, fco_rel_sgss_da B " +                      
      "WHERE A.sgss = B.sgss and A.item = B.ala_datos_adi " +                      
      "AND RTrim(A.tabla) = '" + @vvc_tabla + "' " +                      
      "AND RTrim(A.cod_adm) = 'ISS' " +                      
      "AND A.valor_1 <> 0 " +                      
      "AND B.secuencia IN (2, 3) " +                      
      "GROUP BY B.secuencia "                      
      EXECUTE (@vch_sql)                      
    END                      
                      
    IF @vti_mes = 12                      
    BEGIN                      
      SET @vti_mes = 1                      
      SET @vsm_anio = @vsm_anio + 1                      
    END                      
    ELSE                      
    BEGIN                      
      SET @vti_mes = @vti_mes + 1                      
    END               
                      
    IF @vti_mes > 9                       
      SET @vch_per = CONVERT(VARCHAR(4), @vsm_anio) + CONVERT(VARCHAR, @vti_mes)                      
    ELSE                      
      SET @vch_per = CONVERT(VARCHAR(4), @vsm_anio) + '0' + CONVERT(VARCHAR, @vti_mes)                      
  END                      
                      
  /* Cursor de todas las fpo */                      
  SET @vch_sql =                     
  "SELECT " +                    
  " a.codigo, b.descripcion, ISNULL(a.registro_fiscal, ''), ISNULL(a.digito_verificacion, ''), ISNULL(a.direccion, ''), SUM(ISNULL(c.valor, 0)) " +                    
  " FROM " +                    
  " administradoras a, datos_agr_trab b, " + @vch_usuario + ".TEMP_FPO c " +                    
  " WHERE " +                    
  " a.codigo = b.dato And " +                    
  " a.tipo = 'AFP' And " +                             
  " b.agrupacion = 'FPO' And " +                    
  " c.cod_eps = a.codigo " +                    
  " GROUP BY " +                    
  " a.codigo, b.descripcion, ISNULL(a.registro_fiscal, ''), ISNULL(a.digito_verificacion, ''), ISNULL(a.direccion, '') " +                    
  " ORDER BY " +                    
  " b.descripcion "                    
                      
  EXECUTE('DECLARE cr_fpo CURSOR FOR ' + @vch_sql)                    
                    
  OPEN cr_fpo                      
                        
  FETCH NEXT FROM cr_fpo                     
  INTO @cch_trabajador, @cch_nombre, @cch_cedula, @cch_dv, @cch_domicilio, @vdc_ingresos                      
                      
  WHILE @@fetch_status = 0                              
  BEGIN                              
    IF ISNULL(@vdc_ingresos, 0) > @vdc_tope_fpo                      
    BEGIN                      
/*
        INSERT INTO dttb_mf_medio_dian (secuencia, registro)        
        VALUES (11, @vch_tipo_fpo + @vch_codigo_fpo + @vch_subcodigo_fpo + ',NIT,' + ISNULL(@cch_cedula, '') +        
                ',' + ISNULL(@cch_dv, '') + ',,,,,' + ISNULL(RTRIM(@cch_nombre), ' ') + ',' +         
                ISNULL(@cch_domicilio, ' ') + ',' + '11,001,' + RTrim(ROUND(@vdc_ingresos, 0)))        
*/
        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 @pch_compania, 2006, 1, '', @cch_trabajador, 1, '', '', ROUND(@vdc_ingresos, 0), 0, 0, 0, 0, 0, 0, 0, 0, 0
    END                      
               
    FETCH NEXT FROM cr_fpo                      
    INTO @cch_trabajador, @cch_nombre, @cch_cedula, @cch_dv, @cch_domicilio, @vdc_ingresos                      
  END                      
  CLOSE cr_fpo                      
  DEALLOCATE cr_fpo                      
                      
  DROP TABLE TEMP_FPO                      
                      
END                      
GO
GRANT EXECUTE ON dtsp_crea_mm_dian_2006 TO PUBLIC
GO
