HI SIR,
I Have a doubt in my script iamn having two table 1. My due 2. Coll Month on month my due will generate in due table but coll will come either correctly or two months gap like that.i want to know for this due when I got my coll if due and coll is equal need to update my status and when my coll got that date and amount.
1.coll got adusted in due again some balance is tere balance only taken for next due adjustment how to do this sir.
Iam giving my sample data.
Due table
pk_id LnNo InstlmtDueDt TotInstlmtAmt process Status CollRcpt CollDt CollAmt
1 L1 2016-03-07 2560 N Null Null Null Null
2 L1 2016-04-07 2560 N Null Null Null Null
3. L1 2016-05-07 2560 N Null Null Null Null
4. L1 2016-06-07 2560 N Null Null Null Null
5. L1 2016-07-07 2560 N Null Null Null Null
6. L1 2016-08-07 2560 N Null Null Null Null
Instalment amount will be same.
Coll table
pk_id LnNo LnRcptNo EffDt TranTypeAmt Balance Process collstatus
1 L1 R1 2016-03-07 2560.00 0.00 N NULL
2. L1 R2 2016-05-31 5120.00 0.00 N NULL
3. L1 R3 2016-07-01 2600.00 0.00 N NULL
3. L1 R4 2016-07-30 2520.00 0.00 N NULL
3. L1 R5 2016-08-27 2560.00 0.00 N NULL
Expected o/p In due table
pk_id LnNo InstlmtDueDt TotInstlmtAmt process Status CollRcpt CollDt CollAmt
1 L1 2016-03-07 2560 Y U R1 2016-03-07 2560
2 L1 2016-04-07 2560 Y U R2 2016-05-31 2560
3. L1 2016-05-07 2560 Y U R2 2016-05-31 2560
3. L1 2016-06-07 2560 Y U R3 2016-07-01 2560
4. L1 2016-07-07 40 Y I R3 2016-07-01 40 (Newly Inserted)
5. L1 2016-07-07 2520 Y U R4 2016-07-30 2520
6. L1 2016-08-07 2560 Y U R5 2016-08-27 2560
Colltable process alone Y (if adjusted to due)