IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'dbo.sp_fco_gen_bco_ACH_gnbs_ni') 
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.sp_fco_gen_bco_ACH_gnbs_ni
GO
CREATE PROCEDURE sp_fco_gen_bco_ACH_gnbs_ni
(
  @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 16/2005                                         ***/
/********************************************************************/
DECLARE
@cch_compania  char(4),

@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)
     
BEGIN
--borrando la informacion 
	DELETE fdt_bancos_mm
	WHERE id_rep = @psi_id_rep AND
	usuariow = @pvc_usuario AND
	banco = @pti_banco

  DELETE fdt_bancos_tmp
  WHERE id_rep = @psi_id_rep
  AND usuariow = @pvc_usuario

  --cambiar de acuerdo a la agrupacion que maneja cada cliente
  SET @vch_agr_id = 'TIPO_DOC'
  SET @vch_agr_cta = 'TIPO_CTA'
    
-- Recorriendo el cursor de los registros a procesar.
	DECLARE c_reg_procesa CURSOR FOR
	SELECT DISTINCT compania
	FROM ri_liq_procesar
	WHERE id_rep = @psi_id_rep
	AND usuariow = @pvc_usuario
	
	OPEN c_reg_procesa
	FETCH c_reg_procesa INTO @cch_compania
	
	WHILE (@@FETCH_STATUS = 0)  
	BEGIN--B1

      -- 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, '')

      INSERT INTO fdt_bancos_tmp (id_rep, usuariow, compania, numero_liquidacion, trabajador, importe,cuenta_dep, 
      simbolo_mon, tipo, tipo_cuenta, tipo_doc)      
      SELECT @psi_id_rep, @pvc_usuario, ld.compania, ld.numero_liquidacion,
      ld.trabajador, ld.importe, '0', @vch_simb_mon, 1, '0', '0'
      FROM liquidaciones_det ld, ri_liq_procesar lp
      WHERE ld.compania = lp.compania
      AND ld.tipo_nomina = lp.tipo_nomina
      AND ld.numero_liquidacion = lp.numero_liquidacion
      AND ld.trabajador = lp.trabajador
      AND ld.compania = @cch_compania
      AND ld.concepto IN (@vnu_concepto1, @vnu_concepto2, @vnu_concepto3, @vnu_concepto4, @vnu_concepto5)
      AND ld.importe > 0
      AND ld.sit_transaccion = 2
      AND lp.id_rep = @psi_id_rep
      AND lp.usuariow = @pvc_usuario

      --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)
				FROM fdt_bancos_tmp c
				WHERE compania = @cch_compania          
				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)
				FROM fdt_bancos_tmp c
				WHERE compania = @cch_compania          
				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)
				FROM fdt_bancos_tmp c
				WHERE compania = @cch_compania
				AND id_rep = @psi_id_rep
				AND usuariow = @pvc_usuario
				AND 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 fdt_bancos_tmp
            WHERE compania = @cch_compania
            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)
        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.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.id_rep = @psi_id_rep
				AND c.usuariow = @pvc_usuario

   FETCH c_reg_procesa INTO @cch_compania
            
  END--B1  

  CLOSE c_reg_procesa
	DEALLOCATE c_reg_procesa

  SET @vvc_mensaje = 'PAGO NOMINA INTERACTIVA'

  --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)
  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
  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

  --hallando la maxima compañia que esta en ri_liq_procesar
  SELECT @cch_compania = MAX(compania)
  FROM ri_liq_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(@vvc_mensaje)), 1, 80) + SPACE(80 - LEN(SUBSTRING(LTRIM(RTRIM(@vvc_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_ni TO PUBLIC
GO
