Hi All,
I have an installment table and a repayment table, payment is done with no reference to specific installment. I need to mark each installment as Full, Partial, or Not paid by distributing the total repayment amount over each loan installments ordered by installment number (inst_no).
Below is an example:
create table install
(
insta_serial number primary key,
fk_loan_no number,
inst_no number,
inst_amount number,
inst_due date
);
create table repay
(
repay_serial number primary key,
fk_loan_no number,
repay_amount number,
repay_date date
);
insert ALL
into install values (1,10,1,2000,to_date('01/01/2010','DD/MM/YYYY'))
into install values (2,10,2,1000,to_date('01/01/2011','DD/MM/YYYY'))
into install values (3,10,3,2000,to_date('01/01/2012','DD/MM/YYYY'))
into install values (4,10,4,1000,to_date('01/01/2013','DD/MM/YYYY'))
into install values (5,10,5,2000,to_date('01/01/2014','DD/MM/YYYY'))
into install values (6,20,1,5000,to_date('30/05/2010','DD/MM/YYYY'))
into install values (7,20,2,2000,to_date('31/12/2010','DD/MM/YYYY'))
into repay values (1,10,500,to_date('01/01/2010','DD/MM/YYYY'))
into repay values (2,10,1500,to_date('01/01/2010','DD/MM/YYYY'))
into repay values (3,10,2500,to_date('01/02/2010','DD/MM/YYYY'))
into repay values (4,10,500,to_date('01/03/2014','DD/MM/YYYY'))
into repay values (5,10,1000,to_date('02/03/2014','DD/MM/YYYY'))
into repay values (6,20,500,to_date('01/01/2010','DD/MM/YYYY'))
into repay values (7,20,500,to_date('30/05/2010','DD/MM/YYYY'))
into repay values (8,20,500,to_date('01/06/2010','DD/MM/YYYY'))
into repay values (9,20,500,to_date('01/01/2011','DD/MM/YYYY'))
into repay values (10,20,500,to_date('01/01/2011','DD/MM/YYYY'))
into repay values (11,20,500,to_date('01/01/2011','DD/MM/YYYY'))
into repay values (12,20,2500,to_date('01/01/2011','DD/MM/YYYY'))
select * from DUAL;
What I need to get is:
INSTA_SERIAL FK_LOAN_NO INST_NO INST_AMOUNT INST_DUE REPAY_REMAIN FLAG
1 10 1 2000 01-Jan-10 4000 FULL
2 10 2 1000 01-Jan-11 3000 FULL
3 10 3 2000 01-Jan-12 1000 FULL
4 10 4 1000 01-Jan-13 0 FULL
5 10 5 2000 01-Jan-14 -2000 NOT PAID
6 20 1 5000 30-May-10 500 FULL
7 20 2 2000 31-Dec-10 -1500 PARTIAL
Thanks a lot,
Ferro