ALTER PROCEDURE [dbo].[Redeemvoucherverify]
(
@amount as decimal
,@mobileNO Bigint
,@SecurCode as int
,@BillNO as varchar(10)
,@adminID as int
,@Retvalue as int out
)
AS
BEGIN
Create Table #tbl_RedeemMaster (CORF NVARCHAR(255,CORFID NVARCHAR(255),Role NVARCHAR(255),CreatedBy NVARCHAR(255),CreatedDate DATETIME)
set @Retvalue =0
declare @code int
set @code=0
select @code = Securecode from RedeemMaster Where Securecode =@SecurCode
if ( @code !=0 )
begin
declare @amount1 int
declare @amount2 int
declare @a1 decimal,@a2 decimal,@a3 decimal ,@aa2 decimal,@aa3 decimal
set @mobileno=@mobileNO
set @BillNo=@BillNO
set @amount=@amount
set @SecurCode=@SecurCode
set @a1=@amount
set @aa2 =(select AllocatedAmount from ConsumerMaster where MobileNo =@mobileno)
While @aa2>=@a1@a1 and @a1!=0
BEGIN
set @a2=(select top (1) UpdatedAmount from AllocateMaster Where MobileNo=@mobileno and UpdatedAmount!=0 and ExpireDate >=GETDATE() and Status=1 and AllocateFrom='C' and AllocateFromID=@adminID )
set @a3=(select AllocatedAmount from ConsumerMaster where MobileNo =@mobileno)
if(@a1<@a2)
begin
Update RedeemMaster set Status=1,BillNo=@BillNO Where MobileNo=@mobileno and Securecode=@SecurCode
insert into RedeemMaster (AllocatedID,Amount,MobileNo,BillNo,Securecode,Status) values ((select top (1) AllocateID from AllocateMaster Where MobileNo=@mobileno and UpdatedAmount!=0 and ExpireDate >=GETDATE() and Status=1 and AllocateFrom='C' and AllocateFromID=@adminID),@a1,@mobileno,@BillNo,@SecurCode,1)
Insert Into #tbl_RedeemMaster
(select CORF,CORFID,Role,CreatedBy,CreatedDate from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode)
update RedeemMaster set CORF=tb.CORF,CORFID=,Role=tb.CORFID,CreatedBy=tb.CreatedBy,CreatedDate=tb.CreatedDate
From #tbl_RedeemMaster tb
Where Amount=@a1
Update top (1) AllocateMaster set UpdatedAmount=ISNULL(UpdatedAmount,0)-@a1,UtiltizedAmount=ISNULL(UtiltizedAmount,0)+@a1 Where MobileNo=@mobileno and UpdatedAmount!=0 and ExpireDate >=GETDATE() and Status=1 and AllocateFromID=@adminID and AllocateFrom='C'
Update ConsumerMaster set AllocatedAmount=ISNULL(AllocatedAmount,0)-@a1,RedeemedAmount =ISNULL(RedeemedAmount,0)+@a1 where MobileNo=@mobileno and AllocatedAmount!=0
set @a1=@a1-@a2
end
if(@a1>=@a2)
begin
Update RedeemMaster set Status=1,BillNo=@BillNO Where MobileNo=@mobileno and Securecode=@SecurCode
declare @a4 int
set @a4=(select top (1) UpdatedAmount from AllocateMaster Where MobileNo=@mobileno and UpdatedAmount!=0 and ExpireDate >=GETDATE() and Status=1 and AllocateFrom='C' and AllocateFromID=@adminID)
Update ConsumerMaster set AllocatedAmount=ISNULL(AllocatedAmount,0)-@a4,RedeemedAmount =ISNULL(RedeemedAmount,0)+@a4 where MobileNo=@mobileno and AllocatedAmount!=0
insert into RedeemMaster (AllocatedID,Amount,MobileNo,BillNo,Securecode,Status) values ((select top (1) AllocateID from AllocateMaster Where MobileNo=@mobileno and UpdatedAmount!=0 and ExpireDate >=GETDATE() and Status=1),@a4,@mobileno,@BillNo,@SecurCode,1)
update RedeemMaster set CORF=(select CORF from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CORFID=(select CORFID from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),Role=(select Role from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CreatedBy=(select CreatedBy from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CreatedDate=(select CreatedDate from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode) Where Amount=@a4
Update top (1) AllocateMaster set UpdatedAmount=ISNULL(UpdatedAmount,0)-@a4,UtiltizedAmount=ISNULL(UtiltizedAmount,0)+@a4 Where MobileNo=@mobileno and UpdatedAmount!=0 and ExpireDate >=GETDATE() and Status=1 and AllocateFromID=@adminID and AllocateFrom='C'
set @a1=@a1-@a2
end
IF @a1=0 or @a1<0
BREAK
ELSE
CONTINUE
END
set @RetValue= 1
end
return @Retvalue
end
Look down to the ---Mychanges