if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FuncMayAgencias]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FuncMayAgencias]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FuncMayBonifTarif]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FuncMayBonifTarif]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FuncMayNomAgencia]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FuncMayNomAgencia]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION FuncMayAgencias  (@pmNContrato INT,@pmIdCia CHAR(2))
RETURNS INT
 AS  
BEGIN 
 DECLARE @CantAgenc  INT
 SET  @CantAgenc=(SELECT COUNT(*) FROM Trn_MayAgencias WHERE NContrato=@pmNContrato AND IdCia=@pmIdCia)
 RETURN @CantAgenc
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION FuncMayBonifTarif  (@pmNContrato INT,@pmIdCia CHAR(2))
RETURNS DECIMAL(16,6)
 AS  
BEGIN 
 DECLARE @MaxTarif   DECIMAL(16,6)
 SET  @MaxTarif=(SELECT  MAX(Tarifa) FROM Trn_MayBonif
 WHERE  NContrato=@pmNContrato AND IdCia=@pmIdCia)
 RETURN @MaxTarif
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE FUNCTION FuncMayNomAgencia  (@pmNContrato INT,@pmIdCia CHAR(2))
RETURNS VARCHAR(250)
 AS  
BEGIN 
 DECLARE @NomAgencia  VARCHAR(250)
 SET  @NomAgencia=(SELECT TOP 1 Agencia 
 FROM Trn_MayAgencias AS MA INNER JOIN Agencias AS A ON MA.IdAgencia=A.IdAgencia
 WHERE MA.NContrato=@pmNContrato AND MA.IdCia=@pmIdCia)
 RETURN @NomAgencia
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO