IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'dbo.sp_fco_gen_bco_santander') 
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.sp_fco_gen_bco_santander
GO
CREATE PROCEDURE dbo.sp_fco_gen_bco_santander
(
  @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 Santander                       ***/
/*** Autor:	Diana Cecilia Rios Peinado                            ***/
/*** Fecha:	Mayo 21/2005                                          ***/
/********************************************************************/
--OCT-01-05: DCR, Pasado a SQL de Oracle
-- 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


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

  --Ahora el detalle
    INSERT INTO fdt_bancos_mm(id_rep, usuariow, banco, sec_archivo, secuencia, registros)
    SELECT @psi_id_rep, @pvc_usuario, @pti_banco, 1, 2,
		SUBSTRING(LTRIM(t.clave_unica), 1, 15) + --Identificacion
		REPLACE(LTRIM(RTRIM(t.nombre)), '/' , ' ') + SPACE(22 - LEN(REPLACE(LTRIM(RTRIM(t.nombre)), '/' , ' '))) + -- NOMBRE BENEFICIARIO  
		SUBSTRING(ISNULL(tns.cuenta_dep,'0'), 1, 17) + SPACE(17 - LEN(SUBSTRING(ISNULL(tns.cuenta_dep,'0'), 1, 17))) + -- cuenta
		REPLICATE('0', 15 - LEN(LTRIM(RTRIM(CONVERT(CHAR, FLOOR(tns.importe)))))) + LTRIM(RTRIM(CONVERT(CHAR, FLOOR(tns.importe)))) +  --valor
		'PAGO DE NOMINA' + SPACE(80 - LEN('PAGO DE NOMINA')) +
		SUBSTRING(LTRIM(ISNULL(tns.tipo_doc,'01')), 1, 2) + --Codigo de identificacion del destinatario
		SUBSTRING(LTRIM(ISNULL(tns.tipo_cuenta,'1')), 1, 1) + -- Tipo de Cuenta  1. Ahorros, 2 Corriente   
		REPLICATE('0', 4 -LEN(SUBSTRING(LTRIM(tns.ins_dep),1, 4))) + SUBSTRING(LTRIM(tns.ins_dep),1, 4) + --codigo del banco
    '0001' +
    '1'    
    FROM fdt_bancos_tmp tns, trabajadores t
    WHERE t.trabajador = tns.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_santander TO PUBLIC
GO
