/****** Object:  UserDefinedFunction [dbo].[acOpCR]    Script Date: 06/13/2014 10:30:09 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO


create FUNCTION [dbo].[acOpCR] (@acmastid varchar(40),@enddt datetime,@yearstart datetime,@locid varchar(40),@secid varchar(40))
RETURNS numeric(15,2) AS  
BEGIN 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

declare @retbal numeric(15,2) 
if @locid = '#ALL'
set @locid=null
if @secid = '#ALL'
set @secid=null
if @locid is not null
Begin	
    if @secid is not null
    Begin
	   select @retbal =  sum(Cr) from AcSum (nolock) where t = 0  and acmastid = @acmastid  and Dt>= @yearstart and dt < @enddt and locid=@locid and secid=@secid
	end   
	else
	begin
	   select @retbal =  sum(Cr) from AcSum (nolock) where t = 0  and acmastid = @acmastid  and Dt>= @yearstart and dt < @enddt and locid=@locid 
	end   
end
	 
 else
 begin
    select @retbal =  sum(Cr) from AcSum (nolock) where t = 0  and acmastid = @acmastid  and Dt>= @yearstart and dt < @enddt  
  end
return @retbal

ENd
GO


****** Object:  UserDefinedFunction [dbo].[acOpDR]    Script Date: 06/13/2014 10:31:14 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE FUNCTION [dbo].[acOpDR] (@acmastid varchar(40),@enddt datetime,@yearstart datetime,@locid varchar(40),@secid varchar(40))
RETURNS numeric(15,2) AS  
BEGIN 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

declare @retbal numeric(15,2) 
if @locid = '#ALL'
set @locid=null
if @secid = '#ALL'
set @secid=null
if @locid is not null
Begin	
    if @secid is not null
    Begin
	   select @retbal =  sum(Dr) from AcSum (nolock) where t = 0  and acmastid = @acmastid  and Dt>= @yearstart and dt < @enddt and locid=@locid and secid=@secid
	end   
	else
	begin
	   select @retbal =  sum(Dr) from AcSum (nolock) where t = 0  and acmastid = @acmastid  and Dt>= @yearstart and dt < @enddt and locid=@locid 
	end   
end
	 
 else
 begin
    select @retbal =  sum(Dr) from AcSum (nolock) where t = 0  and acmastid = @acmastid  and Dt>= @yearstart and dt < @enddt  
  end
return @retbal

ENd
GO