IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'dbo.sp_fco_gen_bco_ACH_occi_ni') 
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.sp_fco_gen_bco_ACH_occi_ni
GO
CREATE PROCEDURE sp_fco_gen_bco_ACH_occi_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 para NI                         ***/
/*** Autor:	Diana Cecilia Rios Peinado                            ***/
/*** Fecha:	Marzo 17/2005                                         ***/
/********************************************************************/
--OCT-01-05: DCR, Pasada a SQL de Oracle
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),

@vvc_comprobante varchar(10),
@vvc_nombre_cia varchar(60)
     
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

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

		--trayendo el nombre del cia:
		SET @vvc_nombre_cia = ''
		
		SELECT @vvc_nombre_cia = nombre_cia
		FROM companias
		WHERE compania = @cch_compania
      
		SET @vvc_comprobante =  '00' + LTRIM(RTRIM(DATEPART(YEAR, @pdt_fecha))) +
													  CASE LEN(LTRIM(RTRIM(DATEPART(MONTH, @pdt_fecha)))) WHEN 1 THEN '0'+LTRIM(RTRIM(DATEPART(MONTH, @pdt_fecha))) 
																																 WHEN 2 THEN LTRIM(RTRIM(DATEPART(MONTH, @pdt_fecha))) END

		--como se maneja un consecutivo, debemos hacer uso de una tabla temporal
		SELECT tns.trabajador, IDENTITY(smallint, 1, 1) as secuencia
		INTO #tmp_sec_bco
		FROM fdt_bancos_tmp tns
    WHERE tns.id_rep = @psi_id_rep
    AND tns.usuariow = @pvc_usuario
    AND tns.tipo = 2      

  --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, Abs(sec.secuencia)+1, 
    '2' + --constante
		REPLICATE('0', 4 - LEN(ABS(sec.secuencia))) + Abs(sec.secuencia) + --consecutivo
		REPLICATE('0', 16 - LEN(ISNULL(@vvc_cuenta_bco,'0'))) + ISNULL(@vvc_cuenta_bco,'0') + --cuenta del banco
		REPLACE(LTRIM(RTRIM(t.nombre)), '/' , ' ') + SPACE(30 - LEN(REPLACE(LTRIM(RTRIM(t.nombre)), '/' , ' '))) + --nombre del trabajador
		REPLICATE('0', 11 - LEN(SUBSTRING(LTRIM(t.clave_unica), 1, 11))) + SUBSTRING(LTRIM(t.clave_unica), 1, 11) + --cedula
		REPLICATE('0', 4 - LEN(RTRIM(tns.ins_dep))) + RTRIM(tns.ins_dep) + --codigo del banco
    CONVERT(CHAR(8), @pdt_fecha, 112) + --fecha digitada en el front
		CASE LTRIM(RTRIM(tns.ins_dep)) WHEN '023' THEN '2' ELSE '3' END + --forma de pago
		REPLICATE('0', 15 - LEN(LTRIM(RTRIM(CONVERT(CHAR, FLOOR(tns.importe)))))) + LTRIM(RTRIM(CONVERT(CHAR, FLOOR(tns.importe)))) +  --Importe, parte entera
		SUBSTRING(ISNULL(tns.cuenta_dep,'0'), 2, 16) + SPACE(16 - LEN(SUBSTRING(ISNULL(tns.cuenta_dep,'0'), 2, 16))) + --cuenta deposito
		LTRIM(RTRIM(@vvc_comprobante)) + REPLICATE('0', 4 - LEN(ABS(sec.secuencia))) + Abs(sec.secuencia) + --comprobante
    SUBSTRING(ISNULL(tns.cuenta_dep,'0'), 1, 1) + --tipo de cuenta
    ('Nomina ' + LTRIM(RTRIM(@vvc_nombre_cia))) + SPACE(80- LEN(('Nomina ' + LTRIM(RTRIM(@vvc_nombre_cia)))))
    FROM trabajadores t , fdt_bancos_tmp tns, #tmp_sec_bco sec
    WHERE t.trabajador = tns.trabajador
		AND tns.trabajador = sec.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)

  --insertamos la cabeza
  INSERT INTO fdt_bancos_mm(id_rep, usuariow, banco, sec_archivo, secuencia, registros)
  SELECT @psi_id_rep, @pvc_usuario, @pti_banco, 1, 1, 
  '10000' + --constante
  CONVERT(CHAR(8), @pdt_fecha, 112) + --fecha digitada en el front
  REPLICATE('0', 4 - LEN(LTRIM(RTRIM(@vsi_nro_trab)))) + LTRIM(RTRIM(@vsi_nro_trab)) + --total registros del detalle
	REPLICATE('0', 18 - LEN(LTRIM(RTRIM(CONVERT(CHAR, FLOOR(@vdc_total)))))) + LTRIM(RTRIM(CONVERT(CHAR, FLOOR(@vdc_total)))) + --valor total del detalle
	REPLICATE('0', 16 - LEN(ISNULL(@vvc_cuenta_bco,'0'))) + ISNULL(@vvc_cuenta_bco,'0') + --cuenta del banco
  REPLICATE('0', 148)
  
  --insertamos el pie
  INSERT INTO fdt_bancos_mm(id_rep, usuariow, banco, sec_archivo, secuencia, registros)
  SELECT @psi_id_rep, @pvc_usuario, @pti_banco, 1, Abs(@vsi_nro_trab)+2, 
  '39999' + --constante
  REPLICATE('0', 4 - LEN(LTRIM(RTRIM(@vsi_nro_trab)))) + LTRIM(RTRIM(@vsi_nro_trab)) + --total registros del detalle
	REPLICATE('0', 18 - LEN(LTRIM(RTRIM(CONVERT(CHAR, FLOOR(@vdc_total)))))) + LTRIM(RTRIM(CONVERT(CHAR, FLOOR(@vdc_total)))) + --valor total del detalle  
  REPLICATE('0', 172)

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

	DROP TABLE #tmp_sec_bco

END
GO
GRANT EXECUTE ON dbo.sp_fco_gen_bco_ACH_occi_ni TO PUBLIC
GO
