/* Purpose :Get Last Number
Author : PPG Dinesh Asanka
Create Date : 2002-01-23


Version Date Modification
1.01 2002-02-28 Change to Get Last Number from Channel wise table

*/
CREATE PROCEDURE [dbo].[sp_Get_Next_Number]
@TypeID as varchar(5),@TranDate as varchar(20) = "",
                      @secondType as varchar(5) = ""
AS
DECLARE @Prefix VARCHAR(1),
@LastNumber int,
@Length smallint,
@Year bit,
@Month bit,
@LastYear smallint,
@LastMonth smallint,
@DiffrentTable bit,
@TableName varchar(10),
@TrnYear varchar(2),
@TrnMonth varchar(2),
@char_Next_Number as varchar(25),
@SQL as varchar(120),
@charLastNumber as varchar(15)

If @TranDate = ""
Select @TranDate = GetDate()

if exists (Select * From SEQ_NO Where TYPE = @TypeID)
begin
Select @prefix = Prefix,
@LastNumber = LastNumber,
@Length = Length,
@Year = [Year],
@Month = [Month],
@LastYear = [LastYear],
@LastMonth = [LastMonth],
@DiffrentTable = [DiffrentTable],
@TableName = [TableName]
From SEQ_NO Where TYPE = @TypeID

if (@DiffrentTable = 1)
Select @LastNumber = LastNumber From SEQ_CHAN Where CODE = @secondType

Select @LastNumber = @LastNumber + 1
-- To Fill 0 for prefix
Select @charLastNumber = RTRIM(REPLICATE("0",@length - len(@LastNumber))
                         + cast(@LastNumber as varchar(10)))

Select @TrnYear = RIGHT(DATEPART(yyyy, @TranDate) ,2)
Select @TrnMonth = Month( @TranDate)
If Len(@TrnMonth) = 1 Select @TrnMonth = '0' + @TrnMonth

if @Year = 1 AND @Month = 1 -- Next Number is change for Every Month
if @LastMonth = Month(@TranDate) AND @LastYear
              = RIGHT(DATEPART(yy, @TranDate) ,2)
Select @char_Next_Number = @TrnYear + @TrnMonth + @charLastNumber
else
begin
Select @char_Next_Number = @TrnYear + @TrnMonth
                         + REPLICATE ("0" ,@length -1) + '1'
Select @LastNumber = 1
Select @LastMonth = @TrnMonth
Select @LastYear = @TrnYear
end
else
if @Year = 1 -- Next Number is changing for Every year
if @LastYear = RIGHT(DATEPART(yy, @TranDate) ,2)
Select @char_Next_Number = @TrnYear + @charLastNumber
else
begin
Select @LastYear = @LastYear
Select @LastNumber = 1
Select @char_Next_Number = @TrnYear + REPLICATE ("0" ,@length -1) + '1'
end
else
Select @char_Next_Number = @charLastNumber

Select RTRIM(LTRIM(ISNULL(@prefix,"") + @char_Next_Number))

-- Update Tables for next Numbers
if (@DiffrentTable = 1)
begin

Select @sql = "Update " + @Tablename + " Set LastNumber ="
+ Cast(@LastNumber as varchar(10)) +
" Where CODE ='" + @secondType + "'"
execute (@sql)
end
Update SEQ_NO Set LastNumber = @LastNumber,
LastYear = @LastYear,
LastMonth = @LastMonth
Where TYPE = @TypeID
end
else
Select "Invalid Type"

GO