ALTER TABLE fco_integrada_rt07 DROP CONSTRAINT pk_fco_integrada_rt07 
ALTER TABLE fco_integrada_rt07 DROP COLUMN tipo_concepto 
GO 
ALTER TABLE fco_integrada_rt07 ADD CONSTRAINT pk_fco_integrada_rt07 PRIMARY KEY (compania, anio, mes, sucursal) GO 
ALTER TABLE fco_integrada_rt08 DROP CONSTRAINT pk_fco_integrada_rt08 
ALTER TABLE fco_integrada_rt08 DROP COLUMN tipo_concepto 
GO 
ALTER TABLE fco_integrada_rt08 ADD CONSTRAINT pk_fco_integrada_rt08 PRIMARY KEY (compania, anio, mes, sucursal)
go
DELETE rel_oper_menus WHERE ejecuta_operacion = 'FCO5030';
DELETE rel_oper_modulos WHERE operacion = 'FCO5030';
DELETE operaciones WHERE operacion = 'FCO5030';
DELETE aut_operaciones WHERE operacion = 'FCO5030';

INSERT INTO operaciones (operacion, descripcion_ope, icono, tipo_operacion)  
VALUES ('FCO5030', 'Archivos para la DIAN', 4, 1);

INSERT INTO rel_oper_menus (modulo, menu, secuencia, tipo_llamada, ejecuta_menu, ejecuta_operacion) 
SELECT 'F1', 50, IsNull(max(secuencia),0) + 1, 2, null, 'FCO5030' from rel_oper_menus WHERE modulo='F1' and menu = 50;

INSERT INTO rel_oper_modulos (operacion, modulo)  VALUES ('FCO5030','F1');

INSERT INTO aut_operaciones (usuario, operacion, nivel_seguridad) VALUES ('ADAM','FCO5030',4);
INSERT INTO aut_operaciones (usuario, operacion, nivel_seguridad)
SELECT usuario, 'FCO5030', nivel_seguridad FROM aut_operaciones  WHERE usuario <> 'ADAM' AND operacion = 'MF5040'
go

delete parametros_oper WHERE operacion IN ('MF0001', 'MF5040')
delete aut_operaciones WHERE operacion IN ('MF0001', 'MF5040')
delete rel_oper_menus WHERE ejecuta_operacion IN ('MF0001', 'MF5040')
delete rel_oper_modulos WHERE operacion IN ('MF0001', 'MF5040')
delete operaciones WHERE operacion IN ('MF0001', 'MF5040')
GO

delete fdt_parametros where pais = 'COL' and compania = '0' and parametro = 'otros' and secuencia Between 100 And 105
go
insert into fdt_parametros (pais, compania, parametro, secuencia, descripcion, dato, tipo, campo)
select 'COL', '0', 'otros', 100, 'Archivos DIAN - Tope para información de pagos', 20000000, 4, 0
go
insert into fdt_parametros (pais, compania, parametro, secuencia, descripcion, dato, tipo, campo)
select 'COL', '0', 'otros', 101, 'Archivos DIAN - Base Retención en la Fuente', 6005, 0, 0
go
insert into fdt_parametros (pais, compania, parametro, secuencia, descripcion, dato, tipo, campo)
select 'COL', '0', 'otros', 102, 'Archivos DIAN - Saldo de Pasivos', 'EMP', 2, 0
go
insert into fdt_parametros (pais, compania, parametro, secuencia, descripcion, dato, tipo, campo)
select 'COL', '0', 'otros', 103, 'Archivos DIAN - Tope para Pasivos', 5000000, 4, 0
go
insert into fdt_parametros (pais, compania, parametro, secuencia, descripcion, dato, tipo, campo)
select 'COL', '0', 'otros', 104, 'Archivos DIAN - Agrupación Créditos a tener en cuenta', 'CRE', 5, 0
go
insert into fdt_parametros (pais, compania, parametro, secuencia, descripcion, dato, tipo, campo)
select 'COL', '0', 'otros', 105, 'Archivos DIAN - Tope para Créditos', 5000000, 4, 0
go
IF OBJECT_ID('dbo.fn_fco_acum_ani') IS NOT NULL
    DROP FUNCTION dbo.fn_fco_acum_ani
GO
CREATE FUNCTION dbo.fn_fco_acum_ani
(
    @pvc_parametro varchar(10),
    @pti_secuencia tinyint,
    @pch_compania char(4),
    @psi_anio smallint,
    @pch_trabajador char(10)
)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE 
        @vti_tip_rtf tinyint,
        @vsi_cnc_rtf smallint,
        @vch_agr_rtf char(3),
        @vdc_rtf decimal(19,6)
        
    SELECT @vti_tip_rtf = tipo FROM vw_fdt_parametros
    WHERE compania = @pch_compania AND parametro = @pvc_parametro AND secuencia = @pti_secuencia

    IF @vti_tip_rtf = 0
        SELECT @vsi_cnc_rtf = dato, @vch_agr_rtf = '' FROM vw_fdt_parametros
        WHERE compania = @pch_compania AND parametro = @pvc_parametro AND secuencia = @pti_secuencia
    ELSE
        SELECT @vsi_cnc_rtf = 0, @vch_agr_rtf = dato FROM vw_fdt_parametros
        WHERE compania = @pch_compania AND parametro = @pvc_parametro AND secuencia = @pti_secuencia
    
    SELECT @vdc_rtf =
        acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
        acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
        acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
    FROM acumulados_mensuales
    WHERE compania = @pch_compania
      AND trabajador = @pch_trabajador
      AND agr_conceptos = @vch_agr_rtf
      AND concepto = @vsi_cnc_rtf
      AND anio = @psi_anio

    RETURN IsNull(@vdc_rtf, 0)
END
GO
GRANT EXECUTE ON dbo.fn_fco_acum_ani TO PUBLIC
GO
IF OBJECT_ID('dbo.fn_fco_cir_salarios') IS NOT NULL
    DROP FUNCTION dbo.fn_fco_cir_salarios
GO
CREATE FUNCTION dbo.fn_fco_cir_salarios
(
    @pch_compania char(4),
    @psi_anio smallint,
    @pch_trabajador char(10)
)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE 
        @vch_agr_sal char(3),
        @vch_agr_che char(3),
        @vdc_sal decimal(19,6),
        @vdc_che decimal(19,6),
        @vsi_dif_dias smallint,
        @vdt_hasta smalldatetime,
        @vdt_desde smalldatetime,
        @vdc_tope_che decimal(19,6),
        @vdc_sal_minimo  decimal(19,6),            
        @vch_var_cheque  varchar(15)
        
    /* PARÁMETRO DEL VALOR DE SALARIOS */
    SELECT @vch_agr_sal = dato FROM vw_fdt_parametros
    WHERE compania = @pch_compania AND parametro = 'retencion' AND secuencia = 1

    /* PARÁMETRO DEL VALOR DE CHEQUES CANASTA */
    SELECT @vch_agr_che = dato FROM vw_fdt_parametros
    WHERE compania = @pch_compania AND parametro = 'retencion' AND secuencia = 21

    /* HALLAR TOPE DE CHEQUES */            
    SELECT @vdc_sal_minimo  = salario_minimo FROM hist_sal_min_zona
    WHERE zona = 'A' AND Year(fecha) = @psi_anio

    SELECT @vch_var_cheque = 'var_nom_' + LTrim(RTrim(IsNull(dato, ''))) FROM vw_fdt_parametros            
    WHERE compania = @pch_compania AND parametro = 'retencion' AND secuencia = 20            
    
    SELECT @vdc_tope_che = real * @vdc_sal_minimo FROM parametros_rh
    WHERE parametro_rh = @vch_var_cheque AND compania = @pch_compania

    /* VALOR DE LOS SALARIOS */
    SELECT @vdc_sal =
      acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
      acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
      acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
    FROM acumulados_mensuales
    WHERE compania = @pch_compania 
      AND trabajador = @pch_trabajador
      AND agr_conceptos = @vch_agr_sal
      AND concepto = 0
      AND anio = @psi_anio

    /* VALOR DE LOS CHEQUES CANASTA */
    SELECT @vdc_che =
      acum_imp_mes_01 + acum_imp_mes_02 + acum_imp_mes_03 + acum_imp_mes_04 +
      acum_imp_mes_05 + acum_imp_mes_06 + acum_imp_mes_07 + acum_imp_mes_08 +
      acum_imp_mes_09 + acum_imp_mes_10 + acum_imp_mes_11 + acum_imp_mes_12
    FROM acumulados_mensuales
    WHERE compania = @pch_compania               
      AND trabajador = @pch_trabajador               
      AND agr_conceptos = @vch_agr_che               
      AND concepto = 0               
      AND anio = @psi_anio

    SELECT 
        @vdt_desde = Case When Year(fecha_ingreso) = @psi_anio Then fecha_ingreso Else Convert(smalldatetime, RTrim(@psi_anio) + '0101') End,
        @vdt_hasta = Case When IsNull(Year(fecha_baja), 0) = @psi_anio Then fecha_baja Else Convert(smalldatetime, RTrim(@psi_anio) + '1231') End
    FROM trabajadores_grales
    WHERE compania = @pch_compania
      AND trabajador = @pch_trabajador

    /* Hallando la diferencia en dias entre desde y hasta */            
    IF IsNull(@vdc_che, 0) > 0            
    BEGIN            
        SET @vsi_dif_dias = 
        ((Year(@vdt_hasta) * 360) + 
         (Month(@vdt_hasta) * 30) +                 
         (Case Day(@vdt_hasta) 
            WHEN 31 THEN 30 
            WHEN 28 THEN Case Month(@vdt_hasta) WHEN 2 THEN 30 ELSE Day(@vdt_hasta) END 
            ELSE Day(@vdt_hasta) 
          END)) -                 
        ((Year(@vdt_desde) * 360) + 
         (Month(@vdt_desde) * 30) + 
         (Case Day(@vdt_desde)                 
            WHEN 31 THEN 30 
            WHEN 28 THEN Case Month(@vdt_desde) WHEN 2 THEN 30 ELSE Day(@vdt_desde) END 
            ELSE Day(@vdt_desde) 
          END)) + 1  
                   
        IF (@vdc_sal / @vsi_dif_dias) < @vdc_tope_che / 30            
            SET @vdc_sal = @vdc_sal - @vdc_che            
    END            

    RETURN IsNull(@vdc_sal, 0)
END
GO
GRANT EXECUTE ON dbo.fn_fco_cir_salarios TO PUBLIC
GO
IF OBJECT_ID('dbo.fn_fco_dian_creditos') IS NOT NULL
    DROP FUNCTION dbo.fn_fco_dian_creditos
GO
CREATE FUNCTION dbo.fn_fco_dian_creditos
(
    @pch_compania char(4),
    @psi_anio smallint,
    @pch_trabajador char(10)
)
RETURNS decimal(19,6)
AS
BEGIN
    DECLARE 
        @vdc_valor decimal(19,6),
        @vch_agr_cre char(3),
        @cch_folio char(10),
        @csi_concepto smallint,
        @csi_pago smallint,
        @vdc_saldo decimal(19,2),
        @vdt_fecha_venc smalldatetime,
        @vdt_fecha_pago smalldatetime,
        @vdc_amortizacion decimal(19,2),
        @vti_estatus tinyint

    SELECT @vch_agr_cre = dato FROM vw_fdt_parametros
    WHERE pais = 'COL' AND compania = @pch_compania AND parametro = 'otros' AND secuencia = 104

    DECLARE cr_creditos CURSOR FOR
    SELECT 
        A.folio, A.concepto, MAX(A.pago)
    FROM
        saldos_creditos A, definicion_credito B, creditos_personal C
    WHERE A.compania = @pch_compania
        AND A.trabajador = @pch_trabajador
        AND B.compania = A.compania
        AND B.concepto = A.concepto 
        AND B.cred_especial = 0
        AND A.compania = C.compania
        AND A.trabajador = C.trabajador         
        AND A.folio = C.folio
        AND A.concepto = C.concepto
        AND ((Convert(char(8), A.fecha_vencimiento, 112) <= RTrim(@psi_anio) + '1231') 
        OR (Convert(char(8), C.fecha_credito, 112) <= RTrim(@psi_anio) + '1231' AND
            Convert(char(8), A.fecha_vencimiento, 112) > RTrim(@psi_anio) + '1231' AND
            A.pago = 1))
        AND Convert(char(8), A.fecha_vencimiento, 112) > RTrim(@psi_anio) + '0101'            
        AND C.concepto IN 
        (SELECT concepto FROM rel_conceptos_agr 
         WHERE compania = @pch_compania AND agr_conceptos = @vch_agr_cre)
    GROUP BY
        A.folio, A.concepto
            
    OPEN cr_creditos
            
    FETCH NEXT FROM cr_creditos INTO @cch_folio, @csi_concepto, @csi_pago
            
    SET @vdc_valor = 0
    WHILE @@fetch_status = 0
    BEGIN
        SELECT 
            @vdc_saldo = saldo_capital,
            @vdc_amortizacion = amortizacion_capital,  
            @vti_estatus = estatus,
            @vdt_fecha_venc = fecha_vencimiento,
            @vdt_fecha_pago = fecha_pago
        FROM
            saldos_creditos
        WHERE             
            compania = @pch_compania 
            AND trabajador = @pch_trabajador
            AND folio = @cch_folio
            AND concepto = @csi_concepto 
            AND pago = @csi_pago  
            
       IF @vti_estatus IN (0, 2)   
            IF @csi_pago = 1   
            BEGIN  
                SET @vdc_valor = ISNULL(@vdc_valor, 0) + @vdc_saldo  

                FETCH NEXT FROM cr_creditos INTO @cch_folio, @csi_concepto, @csi_pago
            END  
            ELSE  
                SET @csi_pago = @csi_pago - 1  
        ELSE  
        BEGIN
            IF @vti_estatus = 1
                IF @vdt_fecha_venc > Convert(varchar, @psi_anio) + '1231'
                    SET @vdc_valor = ISNULL(@vdc_valor, 0) + @vdc_saldo
                ELSE
                    IF @vdt_fecha_pago > Convert(varchar, @psi_anio) + '1231'
                        SET @vdc_valor = ISNULL(@vdc_valor, 0) + @vdc_saldo
                    ELSE
                        SET @vdc_valor = ISNULL(@vdc_valor, 0) + @vdc_saldo - @vdc_amortizacion
        
            FETCH NEXT FROM cr_creditos INTO @cch_folio, @csi_concepto, @csi_pago
        END
            
    END
    
    CLOSE cr_creditos
    DEALLOCATE cr_creditos            

    RETURN IsNull(@vdc_valor, 0)
END
GO
GRANT EXECUTE ON fn_fco_dian_creditos TO PUBLIC
GO
IF OBJECT_ID('dbo.fco_dian') IS NOT NULL
    DROP TABLE dbo.fco_dian
GO
CREATE TABLE dbo.fco_dian
(
    compania char(4) NOT NULL,
    anio smallint NOT NULL,
    formato smallint NOT NULL,
    concepto smallint NOT NULL,
    subconcepto varchar(10) NOT NULL,
    trabajador char(10) NOT NULL,
    entidad char(10) NOT NULL,
    valor decimal(10) NOT NULL,
    base decimal(10) NOT NULL,
    Constraint pk_fco_dian 
    Primary Key (compania, anio, formato, concepto, subconcepto, trabajador, entidad)
)
GO
GRANT ALL ON dbo.fco_dian TO PUBLIC
GO
IF OBJECT_ID('dbo.vw_fco_dir_tra') IS NOT NULL
    DROP VIEW dbo.vw_fco_dir_tra
GO
CREATE VIEW dbo.vw_fco_dir_tra
AS
SELECT
    trabajador,
    dire = Replace(IsNull(RTrim(dato_10), ' ') + ' ' +
           IsNull(LTrim(RTrim(SubString(dato_09, 8, 50))), ' '), ',', ' '),
    mpio = IsNull(Left(dato_09, 3), ''),
    dpto = IsNull(SubString(dato_09, 5, 2), '')
FROM
    inf_soc_trabajador
WHERE
    indice_inf_soc = '2VV_UBICAC'
GO
GRANT SELECT ON dbo.vw_fco_dir_tra TO PUBLIC
GO
IF OBJECT_ID('dbo.vw_fco_dian_x_ced') IS NOT NULL
    DROP VIEW dbo.vw_fco_dian_x_ced
GO
CREATE VIEW dbo.vw_fco_dian_x_ced
AS
SELECT 
    a.compania, anio, formato, concepto, subconcepto, 
    IsNull(t.clave_unica, 'Sin_Cedula') As cedula, 
    IsNull(Max(RTrim(td.dato)), 'CC') As tdoc,
    dbo.fn_ape1(Max(t.nombre)) As ape1,
    dbo.fn_ape2(Max(t.nombre)) As ape2,
    dbo.fn_nom1(Max(t.nombre)) As nom1,
    dbo.fn_nom2(Max(t.nombre)) As nom2,
    IsNull(Max(d.dire), '') As dire,
    IsNull(Max(d.dpto), '') As dpto,
    IsNull(Max(d.mpio), '') As mpio,
    Sum(valor) As valor, 
    Sum(base) As base
FROM fco_dian a
INNER JOIN trabajadores t ON a.trabajador = t.trabajador
LEFT JOIN vw_fco_tdo_trabajador td ON a.compania = td.compania AND a.trabajador = td.trabajador
LEFT JOIN vw_fco_dir_tra d ON d.trabajador = a.trabajador
WHERE a.trabajador <> ''
GROUP BY a.compania, anio, formato, concepto, subconcepto, IsNull(t.clave_unica, 'Sin_Cedula')
GO
GRANT SELECT ON dbo.vw_fco_dian_x_ced TO PUBLIC
GO
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 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)                              
  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)                              
  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)                              
      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                      
    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)                              
    FROM                              
      acumulados_mensuales                              
    WHERE                              
      compania = @pch_compania AND                      
      trabajador = @cch_trabajador AND                      
      anio = 2006 AND                      
      agr_conceptos = @vch_agr_par AND                       
      concepto = @vsm_cnc_par                      
                      
    IF ISNULL(@vdc_ingresos, 0) > @vdc_tope_par                      
    BEGIN                      
/*
        INSERT INTO dttb_mf_medio_dian (secuencia, registro)        
        VALUES (8, @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, '', '', '', ROUND(@vdc_ingresos, 0), 0, 0, 0        
*/
        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
IF OBJECT_ID('dbo.sp_fco_calcula_totales_ala_2006') IS NOT NULL
    DROP PROCEDURE dbo.sp_fco_calcula_totales_ala_2006
GO
CREATE PROCEDURE dbo.sp_fco_calcula_totales_ala_2006(@pch_compania char(4))
/*******************************************************/
/***     FCO Módulo Fiscal Colombiano                ***/
/***        Archivos para la DIAN                    ***/
/*** Este procedimiento recalcula los totales        ***/
/*** que no son calculados en la planilla integrada: ***/
/*** Totales por Pensiones, Cajas, SENA, ICBF        ***/
/*** Autor: Juan Carlos Leguizamón                   ***/
/*** Fecha: 25-ABR-07                                ***/
/*** Versión especial para el año 2006               ***/
/*** Mezcla de autoliquidación vieja con planilla    ***/
/*******************************************************/
AS
BEGIN
    DECLARE @i tinyint
    DECLARE @ini tinyint

    SELECT @ini = 
        Case @pch_compania When 'DETE' Then 9
                           When 'GRAS' Then 9
                           When 'GRAC' Then 10
                           When 'JABC' Then 13
        End

    DELETE fco_integrada_rt03 WHERE compania = @pch_compania AND anio = 2006
    DELETE fco_integrada_rt04 WHERE compania = @pch_compania AND anio = 2006 AND mes = 1
    DELETE fco_integrada_rt05 WHERE compania = @pch_compania AND anio = 2006 AND mes = 1
    DELETE fco_integrada_rt06 WHERE compania = @pch_compania AND anio = 2006
    DELETE fco_integrada_rt07 WHERE compania = @pch_compania AND anio = 2006
    DELETE fco_integrada_rt08 WHERE compania = @pch_compania AND anio = 2006

    SET @i = @ini
    WHILE @i < 13
    BEGIN
        /* PENSIONES */
        SELECT distinct cod_AFP
        INTO #afp
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = 2006
          AND mes = @i

        SELECT cod_AFP, IDENTITY(tinyint, 1, 1) secuencia
        INTO #afp2
        FROM #afp
        INNER JOIN fco_administradoras a ON #afp.cod_AFP = codigo
        WHERE a.tipo = 'AFP' and IsNull(RTrim(a.cod_super), '') <> ''

        DROP TABLE #afp

        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 compania, anio, mes, sucursal, a.cod_AFP, b.secuencia, c.registro_fiscal, c.digito_verificacion, SUM(aporte_AFP), 
        SUM(voluntario_AFP), SUM(voluntario_AFP_cia), SUM(aporte_FSP), SUM(aporte_FSP_subsistencia), 0, 0, 0, 0, COUNT(distinct trabajador)
        FROM fco_integrada_det a
        INNER JOIN #afp2 b ON b.cod_AFP = a.cod_AFP
        INNER JOIN fco_administradoras c ON a.cod_AFP = c.codigo 
        WHERE a.compania = @pch_compania
          AND a.anio = 2006
          AND a.mes = @i
          AND c.tipo = 'AFP'
        GROUP BY compania, anio, mes, sucursal, a.cod_AFP, b.secuencia, c.registro_fiscal, c.digito_verificacion

        DROP TABLE #afp2

        /* CAJAS DE COMPENSACION */
        SELECT distinct cod_CCF
        INTO #CCF
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = 2006
          AND mes = @i

        SELECT cod_CCF, IDENTITY(tinyint, 1, 1) secuencia
        INTO #CCF2
        FROM #CCF
        INNER JOIN fco_administradoras a ON #CCF.cod_CCF = codigo
        WHERE a.tipo = 'CCF' and IsNull(RTrim(a.cod_super), '') <> ''

        DROP TABLE #CCF

        INSERT INTO fco_integrada_rt06
        (compania, anio, mes, sucursal, cod_CCF, secuencia, nit, dv, suma_aportes, dias_mora, int_mora, total_afiliados)
        SELECT compania, anio, mes, sucursal, a.cod_CCF, b.secuencia, c.registro_fiscal, c.digito_verificacion, 
               SUM(aporte_CCF), 0, 0, COUNT(trabajador)
        FROM fco_integrada_det a
        INNER JOIN #CCF2 b ON b.cod_CCF = a.cod_CCF
        INNER JOIN fco_administradoras c ON a.cod_CCF = c.codigo 
        WHERE a.compania = @pch_compania
          AND a.anio = 2006
          AND a.mes = @i
          AND c.tipo = 'CCF'
        GROUP BY compania, anio, mes, sucursal, a.cod_CCF, b.secuencia, c.registro_fiscal, c.digito_verificacion

        DROP TABLE #CCF2

        /* SENA */
        INSERT INTO fco_integrada_rt07
        (compania, anio, mes, sucursal, secuencia, nit, dv, valor, dias_mora, int_mora, nro_personas)
        SELECT compania, anio, mes, sucursal, 1, '', '', SUM(aporte_SENA), 0, 0, COUNT(trabajador)
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = 2006
          AND mes = @i
        GROUP BY compania, anio, mes, sucursal

        /* ICBF */
        INSERT INTO fco_integrada_rt08
        (compania, anio, mes, sucursal, secuencia, nit, dv, valor, dias_mora, int_mora, nro_personas)
        SELECT compania, anio, mes, sucursal, 1, '', '', SUM(aporte_ICBF), 0, 0, COUNT(trabajador)
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = 2006
          AND mes = @i
        GROUP BY compania, anio, mes, sucursal

        SET @i = @i + 1
    END

    -- HALLAR LO QUE FALTA, LO QUE SE HIZO EN AUTOLIQUIDACION VIEJA
    EXEC dtsp_crea_mm_dian_2006 @pch_compania
END
GO
GRANT EXECUTE ON dbo.sp_fco_calcula_totales_ala_2006 TO PUBLIC
GO

IF OBJECT_ID('dbo.sp_fco_calcula_totales_ala') IS NOT NULL
    DROP PROCEDURE dbo.sp_fco_calcula_totales_ala
GO
CREATE PROCEDURE dbo.sp_fco_calcula_totales_ala
(
    @pch_compania char(4),
    @psi_anio smallint
)
/*******************************************************/
/***     FCO Módulo Fiscal Colombiano                ***/
/***        Archivos para la DIAN                    ***/
/*** Este procedimiento recalcula los totales        ***/
/*** que no son calculados en la planilla integrada: ***/
/*** Totales por Pensiones, Cajas, SENA, ICBF        ***/
/*** Autor: Juan Carlos Leguizamón                   ***/
/*** Fecha: 25-ABR-07                                ***/
/*******************************************************/
AS
BEGIN

    IF @psi_anio = 2006
    /* Llamado especial por el cambio a planilla integrada ocurrido durante ese año */
    BEGIN
        EXEC sp_fco_calcula_totales_ala_2006 @pch_compania
        RETURN
    END

    DECLARE @i tinyint

    DELETE fco_integrada_rt03 WHERE compania = @pch_compania AND anio = @psi_anio
    DELETE fco_integrada_rt06 WHERE compania = @pch_compania AND anio = @psi_anio
    DELETE fco_integrada_rt07 WHERE compania = @pch_compania AND anio = @psi_anio
    DELETE fco_integrada_rt08 WHERE compania = @pch_compania AND anio = @psi_anio

    SET @i = 1
    WHILE @i < 13
    BEGIN
        /* PENSIONES */
        SELECT distinct cod_AFP
        INTO #afp
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = @psi_anio
          AND mes = @i

        SELECT cod_AFP, IDENTITY(tinyint, 1, 1) secuencia
        INTO #afp2
        FROM #afp
        INNER JOIN fco_administradoras a ON #afp.cod_AFP = codigo
        WHERE a.tipo = 'AFP' and IsNull(RTrim(a.cod_super), '') <> ''

        DROP TABLE #afp

        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 compania, anio, mes, sucursal, a.cod_AFP, b.secuencia, c.registro_fiscal, c.digito_verificacion, SUM(aporte_AFP), 
        SUM(voluntario_AFP), SUM(voluntario_AFP_cia), SUM(aporte_FSP), SUM(aporte_FSP_subsistencia), 0, 0, 0, 0, COUNT(distinct trabajador)
        FROM fco_integrada_det a
        INNER JOIN #afp2 b ON b.cod_AFP = a.cod_AFP
        INNER JOIN fco_administradoras c ON a.cod_AFP = c.codigo 
        WHERE a.compania = @pch_compania
          AND a.anio = @psi_anio
          AND a.mes = @i
          AND c.tipo = 'AFP'
        GROUP BY compania, anio, mes, sucursal, a.cod_AFP, b.secuencia, c.registro_fiscal, c.digito_verificacion

        DROP TABLE #afp2

        /* CAJAS DE COMPENSACION */
        SELECT distinct cod_CCF
        INTO #CCF
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = @psi_anio
          AND mes = @i

        SELECT cod_CCF, IDENTITY(tinyint, 1, 1) secuencia
        INTO #CCF2
        FROM #CCF
        INNER JOIN fco_administradoras a ON #CCF.cod_CCF = codigo
        WHERE a.tipo = 'CCF' and IsNull(RTrim(a.cod_super), '') <> ''

        DROP TABLE #CCF

        INSERT INTO fco_integrada_rt06
        (compania, anio, mes, sucursal, cod_CCF, secuencia, nit, dv, suma_aportes, dias_mora, int_mora, total_afiliados)
        SELECT compania, anio, mes, sucursal, a.cod_CCF, b.secuencia, c.registro_fiscal, c.digito_verificacion, 
               SUM(aporte_CCF), 0, 0, COUNT(trabajador)
        FROM fco_integrada_det a
        INNER JOIN #CCF2 b ON b.cod_CCF = a.cod_CCF
        INNER JOIN fco_administradoras c ON a.cod_CCF = c.codigo 
        WHERE a.compania = @pch_compania
          AND a.anio = @psi_anio
          AND a.mes = @i
          AND c.tipo = 'CCF'
        GROUP BY compania, anio, mes, sucursal, a.cod_CCF, b.secuencia, c.registro_fiscal, c.digito_verificacion

        DROP TABLE #CCF2

        /* SENA */
        INSERT INTO fco_integrada_rt07
        (compania, anio, mes, sucursal, secuencia, nit, dv, valor, dias_mora, int_mora, nro_personas)
        SELECT compania, anio, mes, sucursal, 1, '', '', SUM(aporte_SENA), 0, 0, COUNT(trabajador)
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = @psi_anio
          AND mes = @i
        GROUP BY compania, anio, mes, sucursal

        /* ICBF */
        INSERT INTO fco_integrada_rt08
        (compania, anio, mes, sucursal, secuencia, nit, dv, valor, dias_mora, int_mora, nro_personas)
        SELECT compania, anio, mes, sucursal, 1, '', '', SUM(aporte_ICBF), 0, 0, COUNT(trabajador)
        FROM fco_integrada_det
        WHERE compania = @pch_compania
          AND anio = @psi_anio
          AND mes = @i
        GROUP BY compania, anio, mes, sucursal

        SET @i = @i + 1
    END
END
GO
GRANT EXECUTE ON dbo.sp_fco_calcula_totales_ala TO PUBLIC
GO

IF OBJECT_ID('dbo.sp_fco_archivos_dian') IS NOT NULL
    DROP PROCEDURE sp_fco_archivos_dian
GO
CREATE PROCEDURE sp_fco_archivos_dian
(
    @pch_compania char(4),
    @psi_anio smallint,
    @pti_recalcular_ala tinyint = 1
)
/*************************************************/
/*** FCO - Módulo Fiscal Colombiano            ***/
/*** Autor: Juan Carlos Leguizamón             ***/
/*** Fecha: 24-ABR-07                          ***/
/*** Descripcion: Genera archivos para la DIAN ***/
/*************************************************/
AS
BEGIN
    DELETE fco_dian WHERE compania = @pch_compania AND anio = @psi_anio
    
    /* PARTE 1. FORMATO 1001 - INFORMACION DE PAGOS O ABONOS EN CUENTA */
    /* Concepto 5001 - Salarios, prestaciones sociales y demás pagos laborales */
    INSERT INTO fco_dian (compania, anio, formato, concepto, subconcepto, trabajador, entidad, valor, base)
    SELECT @pch_compania, @psi_anio, 1001, 5001, 'Salarios', trabajador, '', dbo.fn_fco_cir_salarios(@pch_compania, @psi_anio, trabajador), 0
    FROM trabajadores_grales
    WHERE compania = @pch_compania
      AND Year(fecha_ingreso) <= @psi_anio
      AND (fecha_baja is null OR Year(fecha_baja) >= @psi_anio)
    
    -- VALORES TOMADOS DE AUTOLIQUIDACIONES DE APORTES
    IF @pti_recalcular_ala = 1
        EXEC sp_fco_calcula_totales_ala @pch_compania, @psi_anio

    /* Concepto 5010 - Pagos parafiscales (SENA, Cajas de Compensación e ICBF) */
    -- Aportes al SENA
    INSERT INTO fco_dian (compania, anio, formato, concepto, subconcepto, trabajador, entidad, valor, base)
    SELECT @pch_compania, @psi_anio, 1001, 5010, 'SENA', '', 'SENA', SUM(total_pagar), 0
    FROM fco_integrada_rt07
    WHERE compania = @pch_compania AND anio = @psi_anio

    -- Aportes a Cajas de Compensación Familiar
    INSERT INTO fco_dian (compania, anio, formato, concepto, subconcepto, trabajador, entidad, valor, base)
    SELECT @pch_compania, @psi_anio, 1001, 5010, 'CCF', '', cod_CCF, SUM(total_pagar), 0
    FROM fco_integrada_rt06 a
    INNER JOIN fco_administradoras b ON a.cod_CCF = b.codigo AND b.tipo = 'CCF' AND IsNull(b.cod_super, '') <> ''
    WHERE compania = @pch_compania AND anio = @psi_anio
    GROUP BY cod_CCF

    -- Aportes al ICBF
    INSERT INTO fco_dian (compania, anio, formato, concepto, subconcepto, trabajador, entidad, valor, base)
    SELECT @pch_compania, @psi_anio, 1001, 5010, 'ICBF', '', 'ICBF', SUM(total_pagar), 0
    FROM fco_integrada_rt08
    WHERE compania = @pch_compania AND anio = @psi_anio

    /* Concepto 5011 - Pagos a EPS y ARP */
    -- Aportes a la EPS
    INSERT INTO fco_dian (compania, anio, formato, concepto, subconcepto, trabajador, entidad, valor, base)
    SELECT @pch_compania, @psi_anio, 1001, 5011, 'EPS', '', cod_EPS, SUM(total_pagar), 0
    FROM fco_integrada_rt04 a
    INNER JOIN fco_administradoras b ON a.cod_EPS = b.codigo AND b.tipo = 'EPS' AND IsNull(b.cod_super, '') <> ''
    WHERE compania = @pch_compania AND anio = @psi_anio
    GROUP BY cod_EPS

    -- Aportes a la ARP
    INSERT INTO fco_dian (compania, anio, formato, concepto, subconcepto, trabajador, entidad, valor, base)
    SELECT @pch_compania, @psi_anio, 1001, 5011, 'ARP', '', 'ARP', SUM(total_pagar), 0
    FROM fco_integrada_rt05
    WHERE compania = @pch_compania AND anio = @psi_anio

    /* Concepto 5012 - Pagos a Pensiones */
    INSERT INTO fco_dian (compania, anio, formato, concepto, subconcepto, trabajador, entidad, valor, base)
    SELECT @pch_compania, @psi_anio, 1001, 5012, 'AFP', '', cod_AFP, SUM(total_pagar), 0
    FROM fco_integrada_rt03 a
    INNER JOIN fco_administradoras b ON a.cod_AFP = b.codigo AND b.tipo = 'AFP' AND IsNull(b.cod_super, '') <> ''
    WHERE compania = @pch_compania AND anio = @psi_anio
    GROUP BY cod_AFP

    /* PARTE 2. FORMATO 1002 - INFORMACION DE RETENCIONES EN LA FUENTE PRACTICADAS */
    /* Concepto 2301 - Retención por salarios y demás pagos laborales */
    INSERT INTO fco_dian (compania, anio, formato, concepto, subconcepto, trabajador, entidad, valor, base)
    SELECT @pch_compania, @psi_anio, 1002, 2301, 'Retencion', trabajador, '', 
        dbo.fn_fco_acum_ani('retencion', 7, @pch_compania, @psi_anio, trabajador), 
        dbo.fn_fco_acum_ani('otros', 101, @pch_compania, @psi_anio, trabajador)
    FROM trabajadores_grales
    WHERE compania = @pch_compania
      AND Year(fecha_ingreso) <= @psi_anio
      AND (fecha_baja is null OR Year(fecha_baja) >= @psi_anio)
      AND IsNull(dbo.fn_fco_acum_ani('retencion', 7, @pch_compania, @psi_anio, trabajador), 0) + 
          IsNull(dbo.fn_fco_acum_ani('otros', 101, @pch_compania, @psi_anio, trabajador), 0) > 0

    /* PARTE 3. FORMATO 1009 - INFORMACION DEL SALDO DE LOS PASIVOS */
    /* Concepto 2205 - Saldo de los pasivos laborales incluido en el cálculo actuarial */
    INSERT INTO fco_dian (compania, anio, formato, concepto, subconcepto, trabajador, entidad, valor, base)
    SELECT @pch_compania, @psi_anio, 1009, 2205, 'Pasivos', trabajador, '', 
    Round(dbo.fn_fco_acum_ani('otros', 102, @pch_compania, @psi_anio, trabajador), 0), 0
    FROM trabajadores_grales
    WHERE compania = @pch_compania
      AND Year(fecha_ingreso) <= @psi_anio
      AND (fecha_baja is null OR Year(fecha_baja) >= @psi_anio)
      AND dbo.fn_fco_acum_ani('otros', 102, @pch_compania, @psi_anio, trabajador) > 0

    /* PARTE 4. FORMATO 1008 - INFORMACION DE LOS DEUDORES DE CREDITOS ACTIVOS A DICIEMBRE 31 DEL AÑO ANTERIOR */
    /* Concepto 1317 - Cuentas por cobrar a empleados */    
    INSERT INTO fco_dian (compania, anio, formato, concepto, subconcepto, trabajador, entidad, valor, base)
    SELECT @pch_compania, @psi_anio, 1008, 1317, 'Créditos', trabajador, '', dbo.fn_fco_dian_creditos(@pch_compania, @psi_anio, trabajador), 0
    FROM trabajadores_grales
    WHERE compania = @pch_compania
      AND Year(fecha_ingreso) <= @psi_anio
      AND (fecha_baja is null OR Year(fecha_baja) >= @psi_anio)    
END
GO
GRANT EXECUTE ON sp_fco_archivos_dian TO PUBLIC
GO

IF OBJECT_ID('dbo.fntb_fco_dian_1001') IS NOT NULL
    DROP FUNCTION dbo.fntb_fco_dian_1001
GO
CREATE FUNCTION dbo.fntb_fco_dian_1001
(
    @pch_compania char(4),
    @psi_anio smallint
)
-- JCL FCO 25-ABR-07
-- FORMATO 1001 PARA LA DIAN
RETURNS TABLE
AS
RETURN
(
/* PARTE 1. SALARIOS */
/* Cuantías Mayores */
SELECT 
    0 As secuencia, 
    registros =  
    RTrim(concepto) + ',' + tdoc + ',' + cedula + ',,' + -- DV
    ape1 + ',' + ape2 + ',' + nom1 + ',' + nom2 + ',,' + -- Razón Social
    dire + ',"' + dpto + '","' + mpio + '",' + RTrim(Floor(valor))
FROM vw_fco_dian_x_ced a
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5001
  AND a.valor >=
  (Select Convert(decimal(19,6), x.dato) From vw_fdt_parametros x
   Where x.pais = 'COL' And x.compania = a.compania And x.parametro = 'otros' And x.secuencia = 100)

UNION

/* Cuantías Menores */
SELECT 
    1 As secuencia, 
    registros =  
    RTrim(a.concepto) + ',,222222222,,,,,,CUANTÍAS MENORES,,,,' + RTrim(Sum(a.valor))
FROM vw_fco_dian_x_ced a
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5001
  AND a.valor <
  (Select Convert(decimal(19,6), x.dato) From vw_fdt_parametros x
   Where x.pais = 'COL' And x.compania = a.compania And x.parametro = 'otros' And x.secuencia = 100)
GROUP BY a.concepto

UNION

/* PARTE 2. CAJAS DE COMPENSACIÓN */
SELECT
    2 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(b.registro_fiscal, '') + ',' +
    IsNull(b.digito_verificacion, '') + ',' +
    ',,,,' +
    IsNull(b.descripcion, '') + ',' +
    IsNull(b.direccion, '') + ',' + 
    '"' + IsNull(b.departamento, '') + '",' + 
    '"' + IsNull(b.municipio, '') + '",' + 
    RTrim(Round(Sum(a.valor), 0))
FROM fco_dian a
INNER JOIN vw_fco_administradoras b ON a.compania = b.compania AND a.entidad = b.codigo
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5010
  AND a.subconcepto = 'CCF'
  AND b.tipo = 'CCF'
GROUP BY a.concepto, b.registro_fiscal, b.digito_verificacion, b.descripcion, b.direccion, b.departamento, b.municipio

UNION

/* PARTE 3. SENA */
SELECT
    3 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(Left(b.nit_SENA, 9), '899999034') + ',' +
    IsNull(Right(b.nit_SENA, 1), '1') + ',' +
    ',,,,' +
    IsNull(b.nombre_SENA, 'SENA') + ',' +
    IsNull(b.dir_SENA, 'Calle 57 No 8-69') + ',' + 
    '"11","001",' +
    RTrim(Round(a.valor, 0))
FROM fco_dian a
INNER JOIN vw_fco_cias_datos_adi b ON a.compania = b.compania
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5010
  AND a.subconcepto = 'SENA'

UNION

/* PARTE 4. ICBF */
SELECT
    4 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(Left(b.nit_ICBF, 9), '899999239') + ',' +
    IsNull(Right(b.nit_ICBF, 1), '2') + ',' +
    ',,,,' +
    IsNull(b.nombre_ICBF, 'INSTITUTO COLOMBIANO DE BIENESTAR FAMILIAR') + ',' +
    IsNull(b.dir_ICBF, 'Av 68 64-01 piso 3') + ',' + 
    '"11","001",' +
    RTrim(Round(a.valor, 0))
FROM fco_dian a
INNER JOIN vw_fco_cias_datos_adi b ON a.compania = b.compania
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5010
  AND a.subconcepto = 'ICBF'

UNION

/* PARTE 5. ENTIDADES PROMOTORAS DE SALUD */
SELECT
    5 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(b.registro_fiscal, '') + ',' +
    IsNull(b.digito_verificacion, '') + ',' +
    ',,,,' +
    IsNull(b.descripcion, '') + ',' +
    IsNull(b.direccion, '') + ',' + 
    '"' + IsNull(b.departamento, '') + '",' + 
    '"' + IsNull(b.municipio, '') + '",' + 
    RTrim(Round(Sum(a.valor), 0))
FROM fco_dian a
INNER JOIN vw_fco_administradoras b ON a.compania = b.compania AND a.entidad = b.codigo
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5011
  AND a.subconcepto = 'EPS'
  AND b.tipo = 'EPS'
GROUP BY a.concepto, b.registro_fiscal, b.digito_verificacion, b.descripcion, b.direccion, b.departamento, b.municipio

UNION

/* PARTE 6. ADMINISTRADORA DE RIESGOS PROFESIONALES */
SELECT
    6 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(Left(b.nit_ARP, 9), '') + ',' +
    IsNull(Right(b.nit_ARP, 1), '') + ',' +
    ',,,,' +
    IsNull(b.nombre_ARP, '') + ',' +
    IsNull(b.dir_ARP, '') + ',' + 
    '"' + IsNull(b.cod_dpto, '') + '",' + 
    '"' + IsNull(b.cod_ciudad, '') + '",' +
    RTrim(Round(a.valor, 0))
FROM fco_dian a
INNER JOIN vw_fco_cias_datos_adi b ON a.compania = b.compania
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5011
  AND a.subconcepto = 'ARP'

UNION

/* PARTE 7. ADMINISTRADORAS DE FONDOS DE PENSIONES */
SELECT
    7 As secuencia,
    registros =
    RTrim(a.concepto) + ',NIT,' +
    IsNull(b.registro_fiscal, '') + ',' +
    IsNull(b.digito_verificacion, '') + ',' +
    ',,,,' +
    IsNull(b.descripcion, '') + ',' +
    IsNull(b.direccion, '') + ',' + 
    '"' + IsNull(b.departamento, '') + '",' + 
    '"' + IsNull(b.municipio, '') + '",' + 
    RTrim(Round(Sum(a.valor), 0))
FROM fco_dian a
INNER JOIN vw_fco_administradoras b ON a.compania = b.compania AND a.entidad = b.codigo
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1001
  AND a.concepto = 5012
  AND a.subconcepto = 'AFP'
  AND b.tipo = 'AFP'
GROUP BY a.concepto, b.registro_fiscal, b.digito_verificacion, b.descripcion, b.direccion, b.departamento, b.municipio

)
GO
GRANT SELECT ON dbo.fntb_fco_dian_1001 TO PUBLIC
GO
IF OBJECT_ID('dbo.fntb_fco_dian_1002') IS NOT NULL
    DROP FUNCTION dbo.fntb_fco_dian_1002
GO
CREATE FUNCTION dbo.fntb_fco_dian_1002
(
    @pch_compania char(4),
    @psi_anio smallint
)
-- JCL FCO 25-ABR-07
-- FORMATO 1002 PARA LA DIAN
RETURNS TABLE
AS
RETURN
(
/* Cuantías Mayores */
SELECT
    1 As secuencia, 
    registros =  
    RTrim(concepto) + ',' + tdoc + ',' + cedula + ',,' + -- DV
    ape1 + ',' + ape2 + ',' + nom1 + ',' + nom2 + ',,' + -- Razón Social
    dire + ',' + dpto + ',' + mpio + ',' + RTrim(Floor(base)) + ',' + RTrim(Floor(valor))
FROM vw_fco_dian_x_ced a
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1002
  AND a.concepto = 2301
)
GO
GRANT SELECT ON dbo.fntb_fco_dian_1002 TO PUBLIC
GO
IF OBJECT_ID('dbo.fntb_fco_dian_1008') IS NOT NULL
    DROP FUNCTION dbo.fntb_fco_dian_1008
GO
CREATE FUNCTION dbo.fntb_fco_dian_1008
(
    @pch_compania char(4),
    @psi_anio smallint
)
-- JCL FCO 25-ABR-07
-- FORMATO 1008 PARA LA DIAN
RETURNS TABLE
AS
RETURN
(
/* Cuantías Mayores */
SELECT
    0 As secuencia, 
    registros =  
    RTrim(concepto) + ',' + tdoc + ',' + cedula + ',,' + -- DV
    ape1 + ',' + ape2 + ',' + nom1 + ',' + nom2 + ',,' + -- Razón Social
    dire + ',' + dpto + ',' + mpio + ',' + RTrim(Floor(valor))
FROM vw_fco_dian_x_ced a
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1008
  AND a.concepto = 1317
  AND a.valor >=
  (Select Convert(decimal(19,6), x.dato) From vw_fdt_parametros x
   Where x.pais = 'COL' And x.compania = a.compania And x.parametro = 'otros' And x.secuencia = 105)

UNION
/* Cuantías Menores */
SELECT 
    1 As secuencia, 
    registros =  
    RTrim(a.concepto) + ',,222222222,,,,,,CUANTÍAS MENORES,,,,' + RTrim(Sum(a.valor))
FROM vw_fco_dian_x_ced a
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1008
  AND a.concepto = 1317
  AND a.valor <
  (Select Convert(decimal(19,6), x.dato) From vw_fdt_parametros x
   Where x.pais = 'COL' And x.compania = a.compania And x.parametro = 'otros' And x.secuencia = 105)
GROUP BY a.concepto
)
GO
GRANT SELECT ON dbo.fntb_fco_dian_1008 TO PUBLIC
GO
IF OBJECT_ID('dbo.fntb_fco_dian_1009') IS NOT NULL
    DROP FUNCTION dbo.fntb_fco_dian_1009
GO
CREATE FUNCTION dbo.fntb_fco_dian_1009
(
    @pch_compania char(4),
    @psi_anio smallint
)
-- JCL FCO 25-ABR-07
-- FORMATO 1009 PARA LA DIAN
RETURNS TABLE
AS
RETURN
(
/* Cuantías Mayores */
SELECT
    0 As secuencia, 
    registros =  
    RTrim(concepto) + ',' + tdoc + ',' + cedula + ',,' + -- DV
    ape1 + ',' + ape2 + ',' + nom1 + ',' + nom2 + ',,' + -- Razón Social
    dire + ',' + dpto + ',' + mpio + ',' + RTrim(Floor(valor))
FROM vw_fco_dian_x_ced a
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1009
  AND a.concepto = 2205
  AND a.valor >=
  (Select Convert(decimal(19,6), x.dato) From vw_fdt_parametros x
   Where x.pais = 'COL' And x.compania = a.compania And x.parametro = 'otros' And x.secuencia = 103)

UNION
/* Cuantías Menores */
SELECT 
    1 As secuencia, 
    registros =  
    RTrim(a.concepto) + ',,222222222,,,,,,CUANTÍAS MENORES,,,,' + RTrim(Sum(a.valor))
FROM vw_fco_dian_x_ced a
WHERE a.compania = @pch_compania
  AND a.anio = @psi_anio
  AND a.formato = 1009
  AND a.concepto = 2205
  AND a.valor <
  (Select Convert(decimal(19,6), x.dato) From vw_fdt_parametros x
   Where x.pais = 'COL' And x.compania = a.compania And x.parametro = 'otros' And x.secuencia = 103)
GROUP BY a.concepto
)
GO
GRANT SELECT ON dbo.fntb_fco_dian_1009 TO PUBLIC
GO
