DECLARE @lnCnt INT,
@inc INT=1,
@rcptcnt INT,
@rcptinc INT=1,
@Lnno VARCHAR(25)='',
@RcptNo VARCHAR(30)='',
@CllnAmt NUMERIC(18, 2)= 0,
@Effdt DATETIME='1900-01-01',
@InstAmt NUMERIC(18, 2),
@balance NUMERIC(18, 2)=0,
@InstlmtDueDt DATETIME,
@process CHAR(1)
SELECT @rcptcnt = Count(LnRcptNo)
FROM #Instcoll
WHERE Isnull(process, '') <> 'Y'
WHILE @rcptinc <= @rcptcnt
BEGIN
SELECT @balance = TranTypeAmt FROM #Instcoll WHERE pk_id = @inc AND process <> 'Y'
IF @balance > 0
SELECT @Effdt = Min(effdt) FROM #Instcoll WHERE Isnull(process, '') <> 'Y'
SELECT @CllnAmt = TranTypeAmt,@RcptNo = LnRcptNo,@lnno = lnno,@balance = TranTypeAmt
FROM #Instcoll WHERE Isnull(process, '') <> 'Y' AND effdt = @Effdt
SELECT @InstlmtDueDt = Min(InstlmtDueDt) FROM #custDue WHERE process <> 'Y'
SELECT @InstAmt = TotInstlmtAmt,@LnNo = LnNo
FROM #custDue WHERE process <> 'Y'
AND InstlmtDueDt = @InstlmtDueDt
UPDATE #Instcoll SET balance = @balance - @instamt, process = 'Y'
FROM #Instcoll WHERE LnRcptNo = @RcptNo AND effdt = @effdt
UPDATE #custDue SET process = 'Y', status = 'U',CollRcpt = @RcptNo,CollDt = @effdt,collamt = @balance
WHERE LnNo = @LnNo AND InstlmtDueDt = @InstlmtDueDt
SET @balance=@balance - @instamt
if @balance=0
break;
select @balance
WHILE @balance > 0
BEGIN
SELECT @balance, @instamt, * FROM #Instcoll
WHERE pk_id = @inc --and process <> 'Y'
SET @balance =@balance - @instamt
SELECT @balance, @effdt, @InstlmtDueDt, @RcptNo
END
SET @rcptinc=@rcptinc + 1
END
Nilesh ShahPosted Jul 27, 2017, 8:35 AM
what do you mean when you say it is not working properly?
doesn't the code inside while loop execute at all?