update new_lms_installmentextension nli set is_marginalized=it.is_marginalized, partial_marginalization_ledger_amount_id =it.partial_marginalization_ledger_amount_id, marginalization_ledger_amount_id =it.marginalization_ledger_amount_id, is_partially_marginalized=it.is_partially_marginalized, partial_marginalization_date=it.partial_marginalization_date, marginalization_date =it.marginalization_date where
from int_marg_90_days it where it.id=nli.installment_ptr_id; 
 
 
drop materialized view if exists int_marg_90_days2;
create materialized view int_marg_90_days2 as
select * from (
select lai.id ,is_marginalized, partial_marginalization_ledger_amount_id, marginalization_ledger_amount_id, is_partially_marginalized, partial_marginalization_date, marginalization_date, (select status_id from loan_app_loanstatushistroy where loan_id=lal.id and status_type=0 and day <='2025-09-30' order by id desc limit 1) as d
from new_lms_installmentextension nli 
inner join loan_app_installment lai on lai.id=nli.installment_ptr_id
inner join loan_app_loan lal on lal.id=lai.loan_id where (is_interest_paid =false or interest_paid_at::date > '2025-09-30')
) as h where d in (1,2,3,4);
 
update new_lms_installmentextension set is_marginalized=false, partial_marginalization_ledger_amount_id =null, marginalization_ledger_amount_id =null, is_partially_marginalized=false, partial_marginalization_date=null, marginalization_date =null where
installment_ptr_id in (
select id from (
select lai.id, 
(select status_id from loan_app_loanstatushistroy where loan_id=lal.id and status_type=0 and day <='2025-09-30' order by id desc limit 1) as d
from loan_app_installment lai
inner join loan_app_loan lal on lal.id=lai.loan_id
) as h where d in (1,2,3,4) 
)
--and marginalization_ledger_amount_id is not null
and (partial_marginalization_date is not null or marginalization_date is not null) and (is_interest_paid =false or interest_paid_at::date > '2025-09-30');

dXBkYXRlIG5ld19sbXNfaW5zdGFsbG1lbnRleHRlbnNpb24gbmxpIHNldCBpc19tYXJnaW5hbGl6ZWQ9aXQuaXNfbWFyZ2luYWxpemVkLCBwYXJ0aWFsX21hcmdpbmFsaXphdGlvbl9sZWRnZXJfYW1vdW50X2lkID1pdC5wYXJ0aWFsX21hcmdpbmFsaXphdGlvbl9sZWRnZXJfYW1vdW50X2lkLCBtYXJnaW5hbGl6YXRpb25fbGVkZ2VyX2Ftb3VudF9pZCA9aXQubWFyZ2luYWxpemF0aW9uX2xlZGdlcl9hbW91bnRfaWQsIGlzX3BhcnRpYWxseV9tYXJnaW5hbGl6ZWQ9aXQuaXNfcGFydGlhbGx5X21hcmdpbmFsaXplZCwgcGFydGlhbF9tYXJnaW5hbGl6YXRpb25fZGF0ZT1pdC5wYXJ0aWFsX21hcmdpbmFsaXphdGlvbl9kYXRlLCBtYXJnaW5hbGl6YXRpb25fZGF0ZSA9aXQubWFyZ2luYWxpemF0aW9uX2RhdGUgd2hlcmUKZnJvbSBpbnRfbWFyZ185MF9kYXlzIGl0IHdoZXJlIGl0LmlkPW5saS5pbnN0YWxsbWVudF9wdHJfaWQ7IAoKCmRyb3AgbWF0ZXJpYWxpemVkIHZpZXcgaWYgZXhpc3RzIGludF9tYXJnXzkwX2RheXMyOwpjcmVhdGUgbWF0ZXJpYWxpemVkIHZpZXcgaW50X21hcmdfOTBfZGF5czIgYXMKc2VsZWN0ICogZnJvbSAoCnNlbGVjdCBsYWkuaWQgLGlzX21hcmdpbmFsaXplZCwgcGFydGlhbF9tYXJnaW5hbGl6YXRpb25fbGVkZ2VyX2Ftb3VudF9pZCwgbWFyZ2luYWxpemF0aW9uX2xlZGdlcl9hbW91bnRfaWQsIGlzX3BhcnRpYWxseV9tYXJnaW5hbGl6ZWQsIHBhcnRpYWxfbWFyZ2luYWxpemF0aW9uX2RhdGUsIG1hcmdpbmFsaXphdGlvbl9kYXRlLCAoc2VsZWN0IHN0YXR1c19pZCBmcm9tIGxvYW5fYXBwX2xvYW5zdGF0dXNoaXN0cm95IHdoZXJlIGxvYW5faWQ9bGFsLmlkIGFuZCBzdGF0dXNfdHlwZT0wIGFuZCBkYXkgPD0nMjAyNS0wOS0zMCcgb3JkZXIgYnkgaWQgZGVzYyBsaW1pdCAxKSBhcyBkCmZyb20gbmV3X2xtc19pbnN0YWxsbWVudGV4dGVuc2lvbiBubGkgCmlubmVyIGpvaW4gbG9hbl9hcHBfaW5zdGFsbG1lbnQgbGFpIG9uIGxhaS5pZD1ubGkuaW5zdGFsbG1lbnRfcHRyX2lkCmlubmVyIGpvaW4gbG9hbl9hcHBfbG9hbiBsYWwgb24gbGFsLmlkPWxhaS5sb2FuX2lkIHdoZXJlIChpc19pbnRlcmVzdF9wYWlkID1mYWxzZSBvciBpbnRlcmVzdF9wYWlkX2F0OjpkYXRlID4gJzIwMjUtMDktMzAnKQopIGFzIGggd2hlcmUgZCBpbiAoMSwyLDMsNCk7Cgp1cGRhdGUgbmV3X2xtc19pbnN0YWxsbWVudGV4dGVuc2lvbiBzZXQgaXNfbWFyZ2luYWxpemVkPWZhbHNlLCBwYXJ0aWFsX21hcmdpbmFsaXphdGlvbl9sZWRnZXJfYW1vdW50X2lkID1udWxsLCBtYXJnaW5hbGl6YXRpb25fbGVkZ2VyX2Ftb3VudF9pZCA9bnVsbCwgaXNfcGFydGlhbGx5X21hcmdpbmFsaXplZD1mYWxzZSwgcGFydGlhbF9tYXJnaW5hbGl6YXRpb25fZGF0ZT1udWxsLCBtYXJnaW5hbGl6YXRpb25fZGF0ZSA9bnVsbCB3aGVyZQppbnN0YWxsbWVudF9wdHJfaWQgaW4gKApzZWxlY3QgaWQgZnJvbSAoCnNlbGVjdCBsYWkuaWQsIAooc2VsZWN0IHN0YXR1c19pZCBmcm9tIGxvYW5fYXBwX2xvYW5zdGF0dXNoaXN0cm95IHdoZXJlIGxvYW5faWQ9bGFsLmlkIGFuZCBzdGF0dXNfdHlwZT0wIGFuZCBkYXkgPD0nMjAyNS0wOS0zMCcgb3JkZXIgYnkgaWQgZGVzYyBsaW1pdCAxKSBhcyBkCmZyb20gbG9hbl9hcHBfaW5zdGFsbG1lbnQgbGFpCmlubmVyIGpvaW4gbG9hbl9hcHBfbG9hbiBsYWwgb24gbGFsLmlkPWxhaS5sb2FuX2lkCikgYXMgaCB3aGVyZSBkIGluICgxLDIsMyw0KSAKKQotLWFuZCBtYXJnaW5hbGl6YXRpb25fbGVkZ2VyX2Ftb3VudF9pZCBpcyBub3QgbnVsbAphbmQgKHBhcnRpYWxfbWFyZ2luYWxpemF0aW9uX2RhdGUgaXMgbm90IG51bGwgb3IgbWFyZ2luYWxpemF0aW9uX2RhdGUgaXMgbm90IG51bGwpIGFuZCAoaXNfaW50ZXJlc3RfcGFpZCA9ZmFsc2Ugb3IgaW50ZXJlc3RfcGFpZF9hdDo6ZGF0ZSA+ICcyMDI1LTA5LTMwJyk7