plsql - iterate back to adjust the calculated values in for loop

Question

I have writing a PLSQL Program to Calculate the Running Balance for an Installment System.I am facing a issue on the Remaining balance when it is not equal to zero i want to adjust it to previous Installment amounts

Please see the below code as you can test it any Plsql editor

DECLARE
   l_bal_flg              VARCHAR2 (10) := 'Y';
   l_install_amt          NUMBER := 1362;
   l_calcaulatedbalance   NUMBER := 59024;
   l_curr_bal             NUMBER := 0;
   l_residual_amt         NUMBER := 14125;
   l_installment_cnt      NUMBER := 34;
   l_install_seq          NUMBER := 1;
BEGIN
   FOR j IN 1 .. l_installment_cnt LOOP
      IF l_residual_amt <> 0 THEN
         IF l_installment_cnt = l_install_seq THEN
            l_install_amt := l_residual_amt;
         END IF;
      END IF;


      IF l_bal_flg = 'Y' THEN
         l_curr_bal := l_calcaulatedbalance - l_install_amt;
         l_bal_flg := 'N';
      ELSE
         l_curr_bal := l_curr_bal - l_install_amt;


      END IF;

      l_install_seq := l_install_seq + 1;

      DBMS_OUTPUT.PUT_LINE (l_install_seq||' '||l_install_amt || '  ' || l_curr_bal);


   END LOOP;
END;

OUTPUT

1 1362  57662
2 1362  56300
3 1362  54938
4 1362  53576
5 1362  52214
6 1362  50852
7 1362  49490
8 1362  48128
9 1362  46766
10 1362  45404
11 1362  44042
12 1362  42680
13 1362  41318
14 1362  39956
15 1362  38594
16 1362  37232
17 1362  35870
18 1362  34508
19 1362  33146
20 1362  31784
21 1362  30422
22 1362  29060
23 1362  27698
24 1362  26336
25 1362  24974
26 1362  23612
27 1362  22250
28 1362  20888
29 1362  19526
30 1362  18164
31 1362  16802
32 1362  15440
33 1362  14078
34 14125  -47

I want to Adjust back -47 to Row 33 as below

33 1362+(-47)  14078
34 14125       0

if -47 is or some other value greater than 1362 for Example remaining value is 1500 then what will happen

32 1362+(-138)   15440
33 1362+(-1362)  14078
34 14125         0

Answer 1

following code snippet should work as per the logic required by you..

DECLARE
   l_bal_flg              VARCHAR2 (10) := 'Y';
   l_install_amt          NUMBER := 1362;
   l_calcaulatedbalance   NUMBER := 59024;
   l_curr_bal             NUMBER := 0;
   l_tot_inst_amount             NUMBER := 0;
   l_last_bal_amount             NUMBER := 0;
   l_install_amount2             NUMBER := 0;

   l_last_bal_amount2             NUMBER := 0;
   l_residual_amt         NUMBER := 14125;
   l_installment_cnt      NUMBER := 34;
   l_install_seq          NUMBER := 1;
   l_bal_trans            NUMBER :=0;
   l_bal_trans_cnt            NUMBER :=0;
BEGIN
-- amount to be balanced at last before transaction
l_tot_inst_amount := l_calcaulatedbalance-l_residual_amt;
l_last_bal_amount := round(((l_tot_inst_amount/(l_installment_cnt-1)) -  l_install_amt) * (l_installment_cnt-1));

-- to get the transaction from which it has to be balanced
IF l_last_bal_amount <> 0
THEN
   l_last_bal_amount2 :=   ABS(l_last_bal_amount);
   l_install_amount2  :=   l_install_amt;
   l_BAL_TRANS := ceil(l_last_bal_amount2/l_install_amt);
   -- to get the amount to be added on first transaction to be balanced
loop
exit when l_last_bal_amount2 < l_install_amt;
l_last_bal_amount2 := l_last_bal_amount2 - l_install_amt;
end loop;

-- to get the exact transaction count on which first balance to be made
l_bal_trans_cnt :=l_installment_cnt-l_bal_trans;
END IF;

   FOR j IN 1 .. l_installment_cnt LOOP

-- adjustment at the transactions to be balanced
         IF l_bal_trans_cnt = l_install_seq THEN
            l_install_amt := l_install_amount2+(l_last_bal_amount2*(l_last_bal_amount/ABS(l_last_bal_amount)));

        elsif l_install_seq > l_bal_trans_cnt  and l_last_bal_amount >0  then
        l_install_amt := l_install_amount2+l_install_amount2;
         END IF;  



      IF l_residual_amt <> 0 THEN
         IF l_installment_cnt = l_install_seq THEN
            l_install_amt := l_residual_amt;
         END IF;
         -- if balance to be paid is the residual amount then no installment to be paid for the current month 
         IF l_curr_bal = l_residual_amt and l_installment_cnt <> l_install_seq THEN
            l_install_amt := 0;
         END IF;         
      END IF;


      IF l_bal_flg = 'Y' THEN
         l_curr_bal := l_calcaulatedbalance - l_install_amt;
         l_bal_flg := 'N';
      ELSE
         l_curr_bal := l_curr_bal - l_install_amt;
      END IF;

      l_install_seq := l_install_seq + 1;

      DBMS_OUTPUT.PUT_LINE (l_install_seq||' '||l_install_amt || '  ' || l_curr_bal);


   END LOOP;
END;


-- output:
for calculated balance 59024
34 1315  14125
35 14125  0

for calculated balance 63024
31 1362  22164
32 2591  19573
33 2724  16849
34 2724  14125
35 14125  0

for calculated balance 55024
31 1362  14164
32 39  14125
33 0  14125
34 0  14125
35 14125  0