IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'[dbo].[CF_PagoFacil_CB_FactElec_TX]') 
                   and [type] = 'FN')
BEGIN
    DROP FUNCTION dbo.CF_PagoFacil_CB_FactElec_TX
END
GO

CREATE FUNCTION [dbo].[CF_PagoFacil_CB_FactElec_TX](@Trans INT) 
RETURNS CHAR(42)
AS
BEGIN


declare @trans_char char(8)

declare @id_pagoPyme char(4) --es fija 1434
DECLARE @Importe1 CHAR(8)
DECLARE @Fecha1 CHAR(5)
declare @cliente CHAR(5)
DECLARE @Retorno CHAR(42)
DECLARE @Mult CHAR(50)
DECLARE @Importe2 CHAR(6)
DECLARE @Fecha2 CHAR(5)
declare @tipoGeneracion char(1) --es fija 9
declare @nroOperacion char(6)-- es el id_trans
declare @moneda char(1) --es fija 0 (pesos)
DECLARE @pos smallint
DECLARE @Suma DECIMAL(12,2)
DECLARE @dv  CHAR(1)
declare @subtotal int


set @id_pagoPyme = '1434'
set @moneda = '0'
SET @Importe1  = (SELECT RIGHT ( '000000' + CAST(cast((ISNULL(Imp_Total,0) + ISNULL(Saldo_Anterior,0)) * Cotizacion * 100 AS int) AS VARCHAR), 8) 
                 FROM Cbtes WHERE Id_Trans = @trans)
SET @Fecha1    = (select substring(cast(DATEPART(YY,Fecha_Venc) as CHAR(4)),3,2) + 
                 SUBSTRING(CAST(DATEPART(dy,Fecha_Venc) + 1000 AS CHAR(4)),2,3)
                 FROM Cbtes WHERE Id_Trans = @trans)
--print @Fecha1
SET @Importe2  = '000000'
                 
SET @Fecha2    = '00'
                 
SET @cliente = (select RIGHT( '00000' + cast (substring(i.Id_Cliente_Externo,1,7)as varchar),5)
                 FROM Cbtes C , Clientes i WHERE c.id_Cliente = i.id_Cliente and Id_Trans = @trans)
                 
set @tipoGeneracion = '9'
--print @trans
set @trans_char = right( '00000000' + cast(@trans as varchar),8)
                 
set @retorno = '000000000000000000000000000000000000000000'                 
                 
--IF substring(@cliente,1,1) <> 'E'
--BEGIN
   set @retorno = @id_pagoPyme + @Importe1 + @Fecha1 + '00273' + @tipoGeneracion + @trans_char +
                  @moneda + @Importe2 + @Fecha2
  -- print @retorno
  -- print '123456789012345678901234567890123456789012345678901234567890'
   SET @Mult = '13579357935793579357935793579357935793579357935793579357935793579'
   SET @pos = 1
   SET @Suma = 0
   WHILE @pos <= 40
   BEGIN
        set @subtotal = CAST(SUBSTRING(@Retorno,@pos,1) AS INT) * CAST(SUBSTRING(@Mult,@pos,1) AS INT)
--       print cast (@pos as char(2)) +'   ' + cast (@subtotal as char(3))
		SET @Suma = @Suma + @subtotal
        SET @pos = @pos + 1
   END
  -- print @Suma
   SET @Suma = @Suma / 2
   SET @dv = CAST(CAST(@Suma AS INT) % 10 AS CHAR(1))
   SET @retorno = SUBSTRING(@retorno,1,40) + @dv

   SET @pos = 1
   SET @Suma = 0
   WHILE @pos <= 41
   BEGIN
		SET @Suma = @Suma + CAST(SUBSTRING(@Retorno,@pos,1) AS INT) * CAST(SUBSTRING(@Mult,@pos,1) AS INT)
        SET @pos = @pos + 1
   END
   SET @Suma = @Suma / 2
 --  print @Suma
   SET @dv = CAST(CAST(@Suma AS INT) % 10 AS CHAR(1))
   SET @retorno = SUBSTRING(@retorno,1,41) + @dv


return @retorno
END		
