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

CREATE FUNCTION [dbo].[CF_Descrip_Cbte](@pId_Trans INT)
RETURNS VARCHAR(100)	
AS

BEGIN

DECLARE @Descripcion			VARCHAR(100)
DECLARE @Id_Tipo_Cbte			CHAR(4)
DECLARE @Id_Sucursal			SMALLINT
DECLARE @Id_Sucursal_Compra 	SMALLINT
DECLARE @Compromete_Compras		SMALLINT
DECLARE @Compromete_Remitos		SMALLINT
DECLARE @Letra					VARCHAR(3)
DECLARE @Descrip_Corta			CHAR(4)
DECLARE @Cant_Dig_Cbtes			INT
DECLARE @Sucursal_Char			VARCHAR(5)
DECLARE @Comprobante_Nro		INT
DECLARE @Comprobante_Nro_Char	VARCHAR(9)



SELECT @Id_Tipo_Cbte		= ISNULL(CB.Id_Tipo_Cbte, '')
     , @Descrip_Corta		= ISNULL(RTRIM(LTRIM(CT.Descrip_Corta)), '')
	 , @Letra				= ISNULL(RTRIM(LTRIM(CT.Letra)), '')
	 , @Id_Sucursal			= ISNULL(CT.Id_Sucursal, 0)
	 , @Id_Sucursal_Compra	= ISNULL(CB.Id_Sucursal, 0)
	 , @Compromete_Compras	= ISNULL(CT.Compromete_Compras, 0)
	 , @Compromete_Remitos	= ISNULL(CT.Compromete_Remitos, 0)
FROM Cbtes CB, Cbtes_Tipos CT
WHERE 
CB.Id_Tipo_Cbte = CT.Id_Tipo_Cbte
AND CB.Id_Trans = @pId_Trans

/*============================================================================*/

IF(@Compromete_Remitos IN (1, -1))
BEGIN
	SET @Cant_Dig_Cbtes = (dbo.CF_xParam('Cant_Dig_Rem'))
END
ELSE
BEGIN
	SET @Cant_Dig_Cbtes = (dbo.CF_xParam('Cant_Dig_Cbtes'))
END

IF(@Cant_Dig_Cbtes = 0)
BEGIN
	SET @Cant_Dig_Cbtes = 8
END

/*============================================================================*/

IF (@Compromete_Compras IN (1, -1))
BEGIN
   SET @Id_Sucursal	= @Id_Sucursal_Compra
END
ELSE
BEGIN
   SET @Id_Sucursal	= ISNULL((SELECT ISNULL(Sucursal_Nro, 0) FROM Empresas_Suc WHERE Id_Sucursal = @Id_Sucursal),0)
END

/*============================================================================*/
   
SET @Sucursal_Char			= (SELECT RIGHT('00000' + CAST(@Id_Sucursal AS VARCHAR(5)), 5))
SET @Comprobante_Nro		= ISNULL((SELECT ISNULL(Comprobante_Nro, 0) FROM Cbtes WHERE Id_Trans = @pId_Trans),0)

/*=====================================================================================================*/

IF(@Cant_Dig_Cbtes <= 4)
BEGIN
	SET @Comprobante_Nro_Char	= (SELECT RIGHT('00000'+CAST(@Comprobante_Nro AS VARCHAR(4)), 4))
END

IF(@Cant_Dig_Cbtes = 5)
BEGIN
	SET @Comprobante_Nro_Char	= (SELECT RIGHT('00000'+CAST(@Comprobante_Nro AS VARCHAR(5)), 5))
END

IF(@Cant_Dig_Cbtes = 6)
BEGIN
	SET @Comprobante_Nro_Char	= (SELECT RIGHT('000000'+CAST(@Comprobante_Nro AS VARCHAR(6)), 6))
END

IF(@Cant_Dig_Cbtes = 7)
BEGIN
	SET @Comprobante_Nro_Char	= (SELECT RIGHT('0000000'+CAST(@Comprobante_Nro AS VARCHAR(7)), 7))
END

IF(@Cant_Dig_Cbtes = 8)
BEGIN
	SET @Comprobante_Nro_Char	= (SELECT RIGHT('00000000'+CAST(@Comprobante_Nro AS VARCHAR(8)), 8))
END

IF(@Cant_Dig_Cbtes >= 9)
BEGIN
	SET @Comprobante_Nro_Char	= (SELECT RIGHT('000000000'+CAST(@Comprobante_Nro AS VARCHAR(9)), 9))
END

/*=====================================================================================================*/

SET @Descripcion = (@Descrip_Corta + ' ' + @Letra + ' ' + @Sucursal_Char + '-' + @Comprobante_Nro_Char);

RETURN ISNULL(@Descripcion, '')

END  