IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'dbo.sp_fco_gen_bco_ACH_gnbs') 
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.sp_fco_gen_bco_ACH_gnbs
GO
CREATE PROCEDURE sp_fco_gen_bco_ACH_gnbs
(
  @psi_id_rep smallint,
  @pvc_usuario varchar(30),
  @pti_banco tinyint,
  @pdt_fecha smalldatetime = NULL,
  @pvc_responsable varchar(20) = '',
  @pti_filtra_cta tinyint = 0  --0: no filtra, 1: si filtra 
)
AS
/********************************************************************/
/*** Módulo Fiscal de Colombia - Medios Magneticos para Bancos    ***/
/*** Formato: Transmision por ACH del GNBSudameris                ***/
/*** Autor:	Diana Cecilia Rios Peinado                            ***/
/*** Fecha:	Sept 15/2005                                         ***/
/********************************************************************/
-- JCL FEB-12-07 Ajuste para permitir filtro de trabajadores
DECLARE
@vdt_fecha_hoy	smalldatetime,
@vti_sit tinyint,
@vvc_tablaTN varchar(20),
@vvc_sql varchar(2000),
@vnu_concepto1 smallint,
@vnu_concepto2 smallint,
@vnu_concepto3 smallint,
@vnu_concepto4 smallint,
@vnu_concepto5 smallint,
@vch_simb_mon  char(5),
@vti_tipo_cta  tinyint,
@vvc_cuenta_bco  varchar(30),
@vvc_oficina_cta varchar(15),
@vvc_registro_fiscal varchar(20),
@vvc_mensaje varchar(80),
@vdc_total     decimal(19,6),
@vsi_nro_trab  integer,

@vch_agr_id    char(10),
@vch_agr_cta   char(10),

@cch_compania  char(4),
@cch_clase_nom char(2),
@cch_tipo_nom  char(2),
@cnu_anio      smallint,
@cnu_periodo   tinyint,
@cnu_id_cal    smallint
     
BEGIN
--borrando la informacion 
	DELETE fdt_bancos_mm
	WHERE id_rep = @psi_id_rep AND
	usuariow = @pvc_usuario AND
	banco = @pti_banco

  --borramos la tabla temporal que se uso  
  DELETE fdt_bancos_tmp
  WHERE id_rep = @psi_id_rep
  AND usuariow = @pvc_usuario
  
--  Fecha Actual
	SET @vdt_fecha_hoy = getdate()

  --cambiar de acuerdo a la agrupacion que maneja cada cliente
  SET @vch_agr_id = 'TIPO_DOC'
  SET @vch_agr_cta = 'TIPO_CTA'

	DECLARE c_reg_procesa CURSOR FOR
	SELECT DISTINCT c.compania, c.clase_nomina, c.tipo_nomina, c.anio, c.periodo, a.id_calendario
	FROM calendario_procesos a, cal_proc_cias b, ri_procesar c
	WHERE a.id_calendario = b.id_calendario
	AND a.tipo_nomina = c.tipo_nomina
	AND a.anio = c.anio
	AND a.periodo = c.periodo
	AND b.compania = c.compania
	AND c.id_rep = @psi_id_rep
	AND c.usuariow = @pvc_usuario

	OPEN c_reg_procesa  
  FETCH c_reg_procesa INTO @cch_compania, @cch_clase_nom, @cch_tipo_nom, @cnu_anio, @cnu_periodo, @cnu_id_cal

	WHILE (@@FETCH_STATUS = 0)  
	BEGIN--B1
			SET @cnu_id_cal = ISNULL(@cnu_id_cal,0)
	
	    IF IsNull(@cnu_id_cal,0) = 0
	    BEGIN
	        SET @vvc_sql = @cch_tipo_nom + ' ' + RTRIM(@cnu_anio) + ' ' + RTRIM(@cnu_periodo)
	        RAISERROR ('Error de parametrizacion: No existe el periodo específicado (%s)', 16, 1, @vvc_sql)
	        GOTO SgteRegistro
	    END

      --  Verificar el estado del periodo
		  SELECT @vti_sit = sit_periodo
		  FROM cal_proc_cias
		  WHERE compania = @cch_compania And
		  id_calendario = @cnu_id_cal

			SET @vti_sit = ISNULL(@vti_sit, 1)

      --  Asignar la tabla de acuerdo al estado del periodo
	    IF @vti_sit = 2
	    BEGIN
	        SET @vvc_tablaTN = 'trans_' + RTRIM(@cch_compania) + '_' + RTRIM(@cnu_id_cal)
	        IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = Upper(@vvc_tablaTN))  
	        BEGIN  
	            RAISERROR ('Error de datos: No existe la tabla de respaldo [%s]', 16, 1, @vvc_tablaTN)
	            RETURN
	        END
	    END
	    ELSE
	        SET @vvc_tablaTN = 'transacciones_ns'

      -- Trayendo al informacion del banco      
			 SELECT @vnu_concepto1 = b.concepto1, 
			 @vnu_concepto2 = b.concepto2, 
			 @vnu_concepto3 = b.concepto3, 
			 @vnu_concepto4 = b.concepto4, 
			 @vnu_concepto5 = b.concepto5,
			 @vch_simb_mon = b.simbolo_moneda, 
			 @vti_tipo_cta = b.tipo_cta, 
			 @vvc_cuenta_bco = b.cuenta, 
			 @vvc_oficina_cta = b.oficina_cta, 
			 @vvc_registro_fiscal = b.registro_fiscal
			 FROM fdt_bancos a, fdt_bancos_cias_datos b
			 WHERE a.banco = b.banco AND
			 b.compania = @cch_compania AND
			 a.banco = @pti_banco

      --Insertando en la tabla temporal los trabajadores
      SET @vnu_concepto1 = ISNULL(@vnu_concepto1, 0)
      SET @vnu_concepto2 = ISNULL(@vnu_concepto2, 0)
      SET @vnu_concepto3 = ISNULL(@vnu_concepto3, 0)
      SET @vnu_concepto4 = ISNULL(@vnu_concepto4, 0)
      SET @vnu_concepto5 = ISNULL(@vnu_concepto5, 0)
			SET @vch_simb_mon = ISNULL(@vch_simb_mon,'')
			SET @vti_tipo_cta = ISNULL(@vti_tipo_cta,1)
			SET @vvc_cuenta_bco = ISNULL(@vvc_cuenta_bco,'') 
			SET @vvc_oficina_cta = ISNULL(@vvc_oficina_cta,'') 
			SET @vvc_registro_fiscal = ISNULL(@vvc_registro_fiscal, '')
   
      SET @vvc_sql = 'INSERT INTO fdt_bancos_tmp' +
                 ' SELECT ' +  RTRIM(@psi_id_rep) + ', ' +
                  '''' + @pvc_usuario + ''', ' +
                  'tns.compania, tns.clase_nomina, tns.id_calendario, ' +
                  'tns.trabajador, SUM(tns.importe), 0, 0, ' +
                  '''' + @vch_simb_mon + ''', 1, 0, 0, 0, NULL' +
                  ' FROM ' + @vvc_tablaTN + ' tns, trabajadores_grales tg' +
                  ' WHERE tns.compania = tg.compania' +
                  ' AND tns.trabajador = tg.trabajador' +
                  ' AND tg.forma_pago = 3' +
                  ' AND tns.importe > 0' +
                  ' AND tns.sit_transaccion = 2' +
                  ' AND tns.compania = ''' + @cch_compania + '''' +
                  ' AND tns.clase_nomina = ''' + @cch_clase_nom + '''' +
                  ' AND tns.id_calendario = ' + RTRIM(@cnu_id_cal) +
                  ' AND tns.concepto IN ( ' + RTRIM(@vnu_concepto1) + ',' + RTRIM(@vnu_concepto2) + ',' + RTRIM(@vnu_concepto3) + ',' +
                  RTRIM(@vnu_concepto4) + ',' + RTRIM(@vnu_concepto5) + ')' +
                  ' GROUP BY tns.trabajador, tns.compania, tns.clase_nomina, tns.id_calendario'

        EXECUTE (@vvc_sql)

-- JCL FEB-12-07
    IF OBJECT_ID('dbo.fdt_bancos_trab') IS NOT NULL
    BEGIN
        if exists 
        (select 1 from fdt_bancos_trab 
         where usuariow = system_user
           and banco = @pti_banco
           and procesar = 1)

            DELETE fdt_bancos_tmp
            WHERE compania = @cch_compania 
              AND clase_nomina = @cch_clase_nom
              AND id_calendario = @cnu_id_cal
              AND id_rep = @psi_id_rep
              AND usuariow = @pvc_usuario
              AND NOT EXISTS
                (SELECT 1 FROM fdt_bancos_trab x
                 WHERE x.usuariow = system_user
                   and x.banco = @pti_banco
                   and fdt_bancos_tmp.compania = x.compania
                   and fdt_bancos_tmp.trabajador = x.trabajador
                   and x.procesar = 1)
    END
-- END JCL
      --actualizamos los que tienen cuenta de deposito
			--sin importar de que banco sea, se debe traer la cuenta del trabajador
        UPDATE fdt_bancos_tmp
        SET cuenta_dep =
             (SELECT MAX(a.cuenta_deposito)
              FROM rel_trab_ins_dep a
              WHERE a.compania = c.compania
              AND a.trabajador = c.trabajador
              AND a.ins_dep_principal = 1 
              AND c.compania = @cch_compania
              AND c.clase_nomina = @cch_clase_nom
              AND c.id_calendario = @cnu_id_cal)
				 FROM fdt_bancos_tmp c	
         WHERE compania = @cch_compania 
         AND clase_nomina = @cch_clase_nom
         AND id_calendario = @cnu_id_cal
         AND id_rep = @psi_id_rep
         AND usuariow = @pvc_usuario

        UPDATE fdt_bancos_tmp
        SET ins_dep = 
             (SELECT MAX(b.cod_banco)
              FROM rel_trab_ins_dep a, fdt_bancos_ins_dep b 
              WHERE a.compania = c.compania
              AND a.trabajador = c.trabajador
              AND a.ins_dep_principal = 1
              AND a.institucion_deposito = b.ins_dep
              AND b.banco = @pti_banco
              AND c.compania = @cch_compania 
              AND c.clase_nomina = @cch_clase_nom
              AND c.id_calendario = @cnu_id_cal)
				 FROM fdt_bancos_tmp c
         WHERE compania = @cch_compania 
         AND clase_nomina = @cch_clase_nom
         AND id_calendario = @cnu_id_cal
         AND id_rep = @psi_id_rep
         AND usuariow = @pvc_usuario

--si por casualidad ins_dep queda en cero, buscar la info de rel_trab_ins_dep
        UPDATE fdt_bancos_tmp
        SET ins_dep = 
             (SELECT MAX(a.institucion_deposito)
              FROM rel_trab_ins_dep a
              WHERE a.compania = c.compania
              AND a.trabajador = c.trabajador
              AND a.ins_dep_principal = 1 
              AND c.compania = @cch_compania
              AND c.clase_nomina = @cch_clase_nom
              AND c.id_calendario = @cnu_id_cal)
				 FROM fdt_bancos_tmp c
         WHERE compania = @cch_compania 
         AND clase_nomina = @cch_clase_nom
         AND id_calendario = @cnu_id_cal
         AND id_rep = @psi_id_rep
         AND usuariow = @pvc_usuario
         AND LTRIM(RTRIM(c.ins_dep)) = '0'
      
      --si viene la opcion de filtrar unicamente los trabajadores que tengan cuenta en las ins dep parametrizadas
      IF @pti_filtra_cta = 1
      BEGIN
          DELETE FROM fdt_bancos_tmp
          WHERE compania = @cch_compania
          AND clase_nomina = @cch_clase_nom
          AND id_calendario = @cnu_id_cal
          AND id_rep = @psi_id_rep
          AND usuariow = @pvc_usuario
          AND NOT EXISTS (SELECT 1 FROM rel_trab_ins_dep a, fdt_bancos_ins_dep b
                          WHERE a.institucion_deposito = b.ins_dep
                           AND a.compania = fdt_bancos_tmp.compania
                           AND a.trabajador = fdt_bancos_tmp.trabajador
                           AND b.banco = @pti_banco
                           AND fdt_bancos_tmp.compania = @cch_compania                                
                           AND fdt_bancos_tmp.clase_nomina = @cch_clase_nom
                           AND fdt_bancos_tmp.id_calendario = @cnu_id_cal)
      END

      --Actualizar el tipo de documento *** cambiar de acuerdo al cliente ***
			UPDATE fdt_bancos_tmp
			SET tipo_doc = CASE LTRIM(RTRIM(a.dato)) WHEN '01' THEN '02' WHEN '02' THEN '04' WHEN '04' THEN '01' WHEN '05' THEN '03' END
			FROM fdt_bancos_tmp c, rel_trab_agr a
			WHERE a.compania = c.compania
      AND a.trabajador = c.trabajador
      AND a.agrupacion = @vch_agr_id
			AND c.compania = @cch_compania 
			AND c.clase_nomina = @cch_clase_nom
			AND c.id_calendario = @cnu_id_cal
			AND c.id_rep = @psi_id_rep
			AND c.usuariow = @pvc_usuario

      UPDATE fdt_bancos_tmp
      SET tipo_cuenta = LTRIM(RTRIM(a.dato))
			FROM fdt_bancos_tmp c, rel_trab_agr a
			WHERE a.compania = c.compania
      AND a.trabajador = c.trabajador
      AND a.agrupacion = @vch_agr_cta
			AND c.compania = @cch_compania 
			AND c.clase_nomina = @cch_clase_nom
			AND c.id_calendario = @cnu_id_cal
			AND c.id_rep = @psi_id_rep
			AND c.usuariow = @pvc_usuario

			--Armando el mensaje
			IF @cch_tipo_nom = 'QN'
			BEGIN--QN
			  SELECT   
			    @vvc_mensaje =  
			  CASE @cnu_periodo  
			    WHEN 1 THEN '1A QUINC. ENE'   
			    WHEN 2 THEN '2A QUINC. ENE'  
			    WHEN 3 THEN '1A QUINC. FEB'  
			    WHEN 4 THEN '2A QUINC. FEB'  
			    WHEN 5 THEN '1A QUINC. MAR'  
			    WHEN 6 THEN '2A QUINC. MAR'  
			    WHEN 7 THEN '1A QUINC. ABR'  
			    WHEN 8 THEN '2A QUINC. ABR'  
			    WHEN 9 THEN '1A QUINC. MAY'  
			    WHEN 10 THEN '2A QUINC. MAY'  
			    WHEN 11 THEN '1A QUINC. JUN'  
			    WHEN 12 THEN '2A QUINC. JUN'  
			    WHEN 13 THEN '1A QUINC. JUL'  
			    WHEN 14 THEN '2A QUINC. JUL'  
			    WHEN 15 THEN '1A QUINC. AGO'  
			    WHEN 16 THEN '2A QUINC. AGO'  
			    WHEN 17 THEN '1A QUINC. SEP'  
			    WHEN 18 THEN '2A QUINC. SEP'  
			    WHEN 19 THEN '1A QUINC. OCT'  
			    WHEN 20 THEN '2A QUINC. OCT'  
			    WHEN 21 THEN '1A QUINC. NOV'  
			    WHEN 22 THEN '2A QUINC. NOV'  
			    WHEN 23 THEN '1A QUINC. DIC'  
			    WHEN 24 THEN '2A QUINC. DIC'  
			  END  
			  
			  SET @vvc_mensaje = 'NOMINA ' + LTRIM(RTRIM(@vvc_mensaje)) + '/' + LTRIM(RTRIM(@cnu_anio))
			END--QN
			ELSE
			  SET @vvc_mensaje = 'NOMINA ' + LTRIM(RTRIM(@cch_tipo_nom)) + ' Periodo ' + LTRIM(RTRIM(@cnu_periodo)) + '/' + LTRIM(RTRIM(@cnu_anio))

			UPDATE fdt_bancos_tmp
			SET mensaje = @vvc_mensaje
			FROM fdt_bancos_tmp c
			WHERE c.compania = @cch_compania 
			AND c.clase_nomina = @cch_clase_nom
			AND c.id_calendario = @cnu_id_cal
			AND c.id_rep = @psi_id_rep
			AND c.usuariow = @pvc_usuario

SgteRegistro:
  	FETCH c_reg_procesa INTO @cch_compania, @cch_clase_nom, @cch_tipo_nom, @cnu_anio, @cnu_periodo, @cnu_id_cal                    
  END--B1  

  CLOSE c_reg_procesa
	DEALLOCATE c_reg_procesa

  --Finalizado el cursor, se debe agrupar por una sola cuenta por trabajador, los registros de los varios
  --tipos de nomina seleccionados
    INSERT INTO fdt_bancos_tmp(id_rep, usuariow, trabajador, importe, ins_dep, cuenta_dep, simbolo_mon, tipo,
    tipo_cuenta, tipo_doc, mensaje)
    SELECT tn.id_rep, tn.usuariow, tn.trabajador, SUM(tn.importe), tn.ins_dep, tn.cuenta_dep, tn.simbolo_mon, 2,
    tn.tipo_cuenta, tn.tipo_doc, tn.mensaje
    FROM fdt_bancos_tmp tn
    WHERE tn.id_rep = @psi_id_rep
    AND tn.usuariow = @pvc_usuario
    AND tn.tipo = 1
    GROUP BY tn.id_rep, tn.usuariow, tn.trabajador, tn.ins_dep, tn.cuenta_dep, tn.simbolo_mon, tn.tipo_cuenta, tn.tipo_doc, tn.mensaje

  --hallando la maxima compañia que esta en ri_procesar
    SELECT @cch_compania  = MAX(compania)
    FROM ri_procesar
    WHERE id_rep = @psi_id_rep
    AND usuariow = @pvc_usuario

	  -- Trayendo al informacion del banco      
		SELECT 
		@vch_simb_mon = b.simbolo_moneda, 
		@vti_tipo_cta = b.tipo_cta, 
		@vvc_cuenta_bco = b.cuenta, 
		@vvc_oficina_cta = b.oficina_cta, 
		@vvc_registro_fiscal = b.registro_fiscal
		FROM fdt_bancos a, fdt_bancos_cias_datos b
		WHERE a.banco = b.banco AND
		b.compania = @cch_compania AND
		a.banco = @pti_banco

		SET @vti_tipo_cta = ISNULL(@vti_tipo_cta, 1)
		SET @vvc_cuenta_bco = ISNULL(@vvc_cuenta_bco, 0)

		--Armamos la estructura que necesita el banco.
		--Esta estructura la personalizo asi el banco, puede que otro banco la personalice diferente
		--El orden de los campos es el mismo, solo cambia el tamaño.

		INSERT INTO fdt_bancos_mm(id_rep, usuariow, banco, sec_archivo, secuencia, registros)
		SELECT @psi_id_rep, @pvc_usuario, @pti_banco, 1, 1,
		REPLICATE(' ', 16 - LEN (LTRIM(RTRIM(@vvc_cuenta_bco)))) + LTRIM(RTRIM(@vvc_cuenta_bco)) + --nro de cuenta a debitar
		REPLICATE(' ', 2 - LEN (LTRIM(RTRIM(@vti_tipo_cta)))) + LTRIM(RTRIM(@vti_tipo_cta)) + --tipo de cuenta
		SUBSTRING(LTRIM(RTRIM(tns.ins_dep)),1, 6) + SPACE(6 - LEN(LTRIM(RTRIM(tns.ins_dep)))) + --codigo del banco a acreditar
--		SUBSTRING(LTRIM(RTRIM(tns.cuenta_dep)), 1, LEN(LTRIM(RTRIM(tns.cuenta_dep))) - 1) + SPACE(16 - LEN(SUBSTRING(LTRIM(RTRIM(tns.cuenta_dep)), 1, LEN(LTRIM(RTRIM(tns.cuenta_dep))) - 1))) + --Nro de cuenta a acreditar
--		CASE RIGHT(LTRIM(RTRIM(tns.cuenta_dep)),1) WHEN 'D' THEN ' 1' WHEN 'S' THEN ' 2' ELSE ' 2' END + --tipo de cuenta a acreditar
		LTRIM(RTRIM(tns.cuenta_dep)) + SPACE (16 - LEN(LTRIM(RTRIM(tns.cuenta_dep)))) + --Nro de cuenta a acreditar
		REPLICATE(' ', 2 - LEN(LTRIM(RTRIM(tns.tipo_cuenta)))) + LTRIM(RTRIM(tns.tipo_cuenta)) + --tipo de cuenta a acreditar
		REPLICATE(' ', 12 - LEN (SUBSTRING(LTRIM(RTRIM(ISNULL(t.clave_unica,''))), 1, 12))) + SUBSTRING(LTRIM(RTRIM(ISNULL(t.clave_unica,''))), 1, 12) + --nro de identificacion del destinatario
		SUBSTRING(LTRIM(RTRIM(REPLACE(t.nombre, '/', ' '))),1, 34) + SPACE(35 - LEN(SUBSTRING(LTRIM(RTRIM(REPLACE(t.nombre, '/', ' '))),1, 34))) + --nombre del destinatario de pago
		REPLICATE(' ', 15 - LEN(LTRIM(RTRIM(CONVERT(CHAR, FLOOR(tns.importe)))))) + LTRIM(RTRIM(CONVERT(CHAR, FLOOR(tns.importe)))) +  --Importe, parte entera
		SUBSTRING(LTRIM(RTRIM(tns.mensaje)), 1, 80) + SPACE(80 - LEN(SUBSTRING(LTRIM(RTRIM(tns.mensaje)), 1, 80))) + --descripcion del pago
		SUBSTRING(LTRIM(RTRIM(ISNULL(t.e_mail,''))), 1, 80) + SPACE(80 - LEN(SUBSTRING(LTRIM(RTRIM(ISNULL(t.e_mail,''))), 1, 80))) --direccion de correo valida
		FROM fdt_bancos_tmp tns, trabajadores t
		WHERE tns.trabajador = t.trabajador 
		AND tns.id_rep = @psi_id_rep
		AND tns.usuariow = @pvc_usuario
		AND tns.tipo = 2
        
------------------------------------
--  Generar Estadisticas del Proceso
------------------------------------
    SELECT @vdc_total = ISNULL(SUM(importe), 0), 
		@vsi_nro_trab = COUNT(trabajador)
    FROM fdt_bancos_tmp tns 
    WHERE tns.id_rep = @psi_id_rep
    AND tns.usuariow = @pvc_usuario
    AND tipo = 2

  DELETE
      fdt_bancos_resultados
  WHERE
      id_rep = @psi_id_rep AND
      usuariow = @pvc_usuario AND
      banco = @pti_banco

  INSERT INTO fdt_bancos_resultados
    (id_rep, usuariow, banco, trabajadores, valor_total)
  VALUES (@psi_id_rep, @pvc_usuario, @pti_banco, @vsi_nro_trab, @vdc_total)
  
	--si se debe generar reporte 
	IF EXISTS(SELECT 1 vnu_reporte
				    FROM fdt_rutas
				    WHERE operacion = 'FCO1070'
				    AND codigo = @pti_banco
				    AND tipo = 1)
	BEGIN --rep
		DELETE fdt_bancos_rep
		WHERE id_rep = @psi_id_rep AND
		usuariow = @pvc_usuario AND
		banco = @pti_banco

		INSERT INTO fdt_bancos_rep
		SELECT tns.id_rep, tns.usuariow,
		@pti_banco, @pdt_fecha, @pvc_responsable, 
		tns.trabajador, tns.importe, tns.cuenta_dep, tns.simbolo_mon
		FROM fdt_bancos_tmp tns
		WHERE tns.id_rep = @psi_id_rep
		AND tns.usuariow = @pvc_usuario
		AND tipo = 2
 	END--rep
    
END
GO
GRANT EXECUTE ON dbo.sp_fco_gen_bco_ACH_gnbs TO PUBLIC
GO
