Published on : July 29, 2020
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
         start transaction;
         DROP temporary TABLE IF EXISTS t_data;
          CREATE temporary TABLE t_data (
              object_id varchar(100) NOT NULL,
              id bigint NOT NULL,
              status tinyint default 0,
              PRIMARY KEY object_id (object_id),
              KEY id (id)
          ) ENGINE=InnoDB;


        #select @total:=count(1) from t_data;
        SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
        CREATE temporary TABLE t_loan_monthly_obs like data_store.loan_monthly_obs;
        CREATE temporary TABLE entries like loantap_in.loan_entries;

        /*Create skeleton for daily obs*/
        CREATE TEMPORARY TABLE t_loan_daily_obs (
            row_num bigint(20) NOT NULL,
            ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            obs_date date NOT NULL,
            opening_primary_dues decimal(11,0)  DEFAULT 0,
            opening_instalment_interest decimal(11,0)  DEFAULT 0,
            opening_instalment_principal decimal(11,0)  DEFAULT 0,
            opening_instalment decimal(11,0) GENERATED ALWAYS AS (opening_instalment_interest + opening_instalment_principal) STORED,
            opening_penalty_dues decimal(11,0) DEFAULT 0,
            opening_all_dues decimal(11,0) GENERATED ALWAYS AS (opening_primary_dues + opening_instalment + opening_penalty_dues) STORED,
            added_primary_dues decimal(11,0)  DEFAULT 0,
            added_instalment_interest decimal(11,0)  DEFAULT 0,
            added_instalment_principal decimal(11,0)  DEFAULT 0,
            added_instalment decimal(11,0) GENERATED ALWAYS AS (added_instalment_interest + added_instalment_principal) STORED,
            added_penalty_dues decimal(11,0) DEFAULT 0,
            added_all_dues decimal(11,0) GENERATED ALWAYS AS (added_primary_dues + added_instalment + added_penalty_dues) STORED,
            closing_dues_account decimal(11,0)  DEFAULT 0,
            knocked_off	decimal(11,0) GENERATED ALWAYS AS (opening_all_dues + added_all_dues - closing_dues_account) STORED,
            closing_penalty_dues decimal(11,0) DEFAULT 0,
            closing_instalment_principal decimal(11,0)  DEFAULT 0,
            closing_instalment_interest decimal(11,0)  DEFAULT 0,
            closing_primary_dues decimal(11,0)  DEFAULT 0,
            closing_instalment decimal(11,0) GENERATED ALWAYS AS (closing_instalment_interest + closing_instalment_principal) STORED,
            closing_all_dues decimal(11,0) GENERATED ALWAYS AS (closing_primary_dues + closing_instalment + closing_penalty_dues) STORED,
            dpd_days int(5) Default 0,
            PRIMARY KEY id (id),
            KEY row_num (row_num),
            KEY obs_date (obs_date)
        ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


        select @object_id:='SUB16432021248430581510577';


        insert into entries select * from loantap_in.loan_entries where sublan_id=@object_id;

		select @max_date:=ifnull(max(entry_date),LAST_DAY(curdate())) from entries where account='closed';

		insert into t_loan_monthly_obs(sublan_id,lan_id,obs_start,obs_end,obs_month)	
		with 
		q0 as (
		select lan_id,sublan_id,max(entry_date) as max_date,min(entry_date) as min_date from entries
		),
		q1 as (
		select q0.*,obs_dates.* from loantap_in.obs_dates join q0 
		on obs_month >=EXTRACT(YEAR_MONTH FROM min_date) and  obs_month <=EXTRACT(YEAR_MONTH FROM @max_date)
		)
		select sublan_id,lan_id,obs_start,obs_end,obs_month from q1; 

		#update sublan id
		#UPDATE t_loan_monthly_obs as update_table
		#SET update_table.sublan_id=(select sublan_id from entries limit 1);


		################new and old section#######################################
		# Setup sublan
		CREATE temporary TABLE t_sublan_entries like loantap_in.sublan;

		#lapp_meta 
		CREATE temporary TABLE t_lapp_meta like loantap_in.lapp_meta;

		insert into t_sublan_entries 
		select * from loantap_in.sublan where object_id=@object_id;    


		#product_or_scheme
		select @product_or_scheme:=IF(count(1)>0, 'product', 'scheme') from t_sublan_entries where coll_id='sublan' and coll_type='core' and meta_key='product_id';

		UPDATE t_loan_monthly_obs as update_table
		SET update_table.product_or_scheme=@product_or_scheme;
        
        #current meta
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		with q1 as (
		SELECT 
		obs_month,max(entries.ID) as ID 
		FROM 
		t_loan_monthly_obs 
		JOIN 
		entries 
		ON 
		entry_date<=obs_end 
		AND account='Current Meta' 
		GROUP BY 
		obs_end
		)
		select obs_month,current_meta from entries  join q1  on entries.ID=q1.ID 
		) as data
		on update_table.obs_month=data.obs_month
		set update_table.current_meta=data.current_meta;

        delimiter //
		IF @product_or_scheme='product' 
		THEN
          #product JSON (@product_json)
          select @product_json:=meta_value from sublan where object_id=@object_id and coll_id='sublan' and coll_type='loan_details' and meta_key='product_json';
          select json_value(@product_json,'$.base.end_use');

          UPDATE t_loan_monthly_obs as update_table
          SET update_table.loan_id=sublan_id;

          #product_id -> loan_product
          UPDATE t_loan_monthly_obs as update_table
          join
          (
          select meta_value as loan_product from t_sublan_entries where coll_id='sublan' and coll_type='core' and meta_key='product_id'
          ) as data
          SET update_table.loan_product=data.loan_product;

          #bureau_account_type
          UPDATE t_loan_monthly_obs as update_table
          SET update_table.bureau_account_type=json_value(@product_json, '$.bureau.account_type');

          #sublan_instalment_method  from product
          UPDATE t_loan_monthly_obs as update_table
          SET update_table.sublan_instalment_method=json_value(@product_json, '$.instalment.instalment_method');

          #disbursal_beneficiary
          UPDATE t_loan_monthly_obs as update_table
          SET update_table.disbursal_beneficiary=json_value(@product_json, '$.disbursal.beneficiary');


          #lapp_id from sublan
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as lapp_id from t_sublan_entries where coll_id='sublan' and coll_type='core' and meta_key='lapp_id'
          ) as data
          SET update_table.lapp_id=data.lapp_id;

          #insert data into t_lapp_meta
          insert into t_lapp_meta (updated_by,object_id,coll_id,coll_type,meta_key,meta_value)
          with 
          q0 as (select lapp_id  from t_loan_monthly_obs group by lapp_id),
          q1 as (select q0.*,updated_by,object_id,coll_id,coll_type,meta_key,meta_value from q0 join lapp_meta on lapp_meta.object_id=q0.lapp_id )
          select updated_by,object_id,coll_id,coll_type,meta_key,meta_value from q1;

          #customer_id from sublan
          #get the parent lan from sublan table and take customer id
          UPDATE t_loan_monthly_obs as update_table
          join
          (
           with 
          q0 as (select meta_value as lan_id from t_sublan_entries where coll_id='sublan' and meta_key="lan_id"),
          q1 as (select q0.*,meta_value as customer_id from q0 join sublan on sublan.object_id=q0.lan_id and  coll_id='lan' and meta_key='customer_id')        
          select * from q1
          ) as data
          SET update_table.customer_id=data.customer_id;

          #nbfc
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as nbfc from t_sublan_entries where coll_id='sublan' and coll_type='core' and meta_key='nbfc'
          ) as data
          SET update_table.nbfc=data.nbfc;

          #sublan_loan_tenure
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sublan_loan_tenure from t_sublan_entries where coll_id='sublan' and coll_type='loan_details' and meta_key='loan_tenure'
          ) as data
          SET update_table.sublan_loan_tenure=data.sublan_loan_tenure;

          #sublan_loan_interest_rate
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sublan_loan_interest_rate from t_sublan_entries where coll_id='sublan' and coll_type='sublan' and meta_key='interest_rate'
          ) as data
          SET update_table.sublan_loan_interest_rate=data.sublan_loan_interest_rate;

          #sublan_loan_amount
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sanction_amount from t_sublan_entries where coll_id='sublan' and coll_type='loan_details' and meta_key='sanction_amount'
          ) as data
          SET update_table.sublan_loan_amount=data.sanction_amount;

          #sublan_setup_date
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sublan_setup_date from t_sublan_entries where coll_id='sublan' and coll_type='loan_details' and meta_key='setup_date'
          ) as data
          SET update_table.sublan_setup_date=data.sublan_setup_date;

          #sublan_advance_instalments
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select  if(meta_value IS NULL or meta_value = '', 0, meta_value) as sublan_advance_instalments from t_sublan_entries where coll_id='sublan' and coll_type='loan_details' and meta_key='advance_instalments'
          ) as data
          SET update_table.sublan_advance_instalments=data.sublan_advance_instalments;

          #sublan_virtual_account
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sublan_virtual_account from t_sublan_entries where coll_id='sublan' and coll_type='core' and meta_key='virtual_account'
          ) as data
          SET update_table.sublan_virtual_account=data.sublan_virtual_account;
		 
          #sublan_loan_end_date
		  UPDATE t_loan_monthly_obs as update_table
		  join 
		  (
		  select meta_value as sublan_loan_end_date from t_sublan_entries where coll_id='sublan' and coll_type='loan_details' and meta_key='loan_end_date'
		  ) as data
		  SET update_table.sublan_loan_end_date=data.sublan_loan_end_date;

          #sublan_end_date

          #loan_end_use
          UPDATE t_loan_monthly_obs as update_table
          SET update_table.loan_end_use=json_value(@product_json, '$.base.end_use');

          #sublan_dealer_code
          UPDATE t_loan_monthly_obs as update_table
          SET update_table.sublan_dealer_code=json_value(@product_json, '$.dealer.type');
          
          #loan_end_date
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          with q1 as (
          SELECT max(ID) as id,due_date FROM t_loan_monthly_obs JOIN entries on entry_date < obs_end where account ='Loan End Date'  group by obs_end
          )
          select due_date as loan_end_date from q1 join entries on  entries.ID=q1.id
          )as data
          SET update_table.loan_end_date=data.loan_end_date;

		ELSE
          UPDATE t_loan_monthly_obs as update_table
          SET update_table.loan_id=lan_id;

          #scheme id -> loan_product
          #bureau_account_type from scheme
          #sublan_instalment_method from scheme (doubt)
          UPDATE t_loan_monthly_obs as update_table
          join
          (
          with 
          q0 as (select meta_value as loan_product from t_sublan_entries where coll_id='sublan' and coll_type='sublan' and meta_key='scheme_id'),
          q1 as (select q0.*,meta_value as bureau_account_type from q0 left join loan_scheme on q0.loan_product=loan_scheme.object_id and coll_id='scheme' and meta_key='bureau_account_type'),		
          q2 as (select q1.*,meta_value as sublan_instalment_method from q1 left join loan_scheme on q1.loan_product=loan_scheme.object_id  and coll_id='instalment_method' and coll_type='scheme' and meta_key='default'),
          q3 as (select q2.*,meta_value as disbursal_beneficiary from q2 left join loan_scheme on q2.loan_product=loan_scheme.object_id  and coll_id='disbursal_beneficiary' and coll_type='scheme' and meta_key='default'),	
          q4 as (select q3.*,meta_value as product_category from q3 left join loan_scheme on q3.loan_product=loan_scheme.object_id and coll_id='scheme' and meta_key='product_category')	
  select * from q4
          ) as data
          SET update_table.loan_product=data.loan_product,
          update_table.bureau_account_type=data.bureau_account_type,
          update_table.sublan_instalment_method=data.sublan_instalment_method,
          update_table.product_category =data.product_category,
          update_table.disbursal_beneficiary =data.disbursal_beneficiary;



          #lapp_id from sublan
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as lapp_id from t_sublan_entries where coll_id='sublan' and coll_type='sublan' and meta_key='lapp_id'
          ) as data
          SET update_table.lapp_id=data.lapp_id;

          insert into t_lapp_meta (updated_by,object_id,coll_id,coll_type,meta_key,meta_value)
          with 
          q0 as (select lapp_id  from t_loan_monthly_obs group by lapp_id),
          q1 as (select q0.*,updated_by,object_id,coll_id,coll_type,meta_key,meta_value from q0 join lapp_meta on lapp_meta.object_id=q0.lapp_id )
          select updated_by,object_id,coll_id,coll_type,meta_key,meta_value from q1;

          #customer_id from lapp
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select lapp_id,meta_value as customer_id from t_loan_monthly_obs join t_lapp_meta on t_loan_monthly_obs.lapp_id=t_lapp_meta.object_id and coll_id='lapp' and meta_key='customer_id'
          ) as data
          SET update_table.customer_id=data.customer_id;

          #nbfc
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as nbfc from t_sublan_entries where coll_id='sublan' and coll_type='sublan' and meta_key='nbfc'
          ) as data
          SET update_table.nbfc=data.nbfc;

          #sublan_loan_tenure
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sublan_loan_tenure from t_sublan_entries where coll_id='sublan' and coll_type='sublan' and meta_key='tenure'
          ) as data
          SET update_table.sublan_loan_tenure=data.sublan_loan_tenure;

          #sublan_loan_interest_rate
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sublan_loan_interest_rate from t_sublan_entries where coll_id='sublan' and coll_type='sublan' and meta_key='interest_rate'
          ) as data
          SET update_table.sublan_loan_interest_rate=data.sublan_loan_interest_rate;


          #sublan_loan_amount from sublan
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sanction_amount from t_sublan_entries where coll_id='sublan' and coll_type='sublan' and meta_key='sanction_amount'
          ) as data
          SET update_table.sublan_loan_amount=data.sanction_amount;


          #sublan_setup_date
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sublan_setup_date from t_sublan_entries where coll_id='sublan' and coll_type='sublan' and meta_key='setup_date'
          ) as data
          SET update_table.sublan_setup_date=data.sublan_setup_date;

          #sublan_advance_instalments
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select  if(meta_value IS NULL or meta_value = '', 0, meta_value) as sublan_advance_instalments from t_sublan_entries where coll_id='sublan' and coll_type='sublan' and meta_key='advance_instalments'
          ) as data
          SET update_table.sublan_advance_instalments=data.sublan_advance_instalments;

          #sublan_virtual_account
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sublan_virtual_account from t_sublan_entries where coll_id='sublan' and coll_type='sublan' and meta_key='virtual_account'
          ) as data
          SET update_table.sublan_virtual_account=data.sublan_virtual_account;

          #sublan_end_date (DOUBT)
          
          #sublan_loan_end_date 
		  UPDATE t_loan_monthly_obs as update_table
          join
          (
			select object_id as sublan_id,DATE_FORMAT(STR_TO_DATE(meta_value, '%d %M,%Y'), '%Y%m%d') as sublan_loan_end_date  from t_sublan_entries where coll_id='txn' and coll_type='txn' and meta_key='emi_end_date'
          ) as data
          SET update_table.sublan_loan_end_date= DATE_FORMAT(IF(data.sublan_loan_end_date,data.sublan_loan_end_date,DATE_ADD(update_table.sublan_setup_date, INTERVAL update_table.sublan_loan_tenure MONTH)),'%Y%m%d');
	
          #sublan_loan_end_date_label
          UPDATE t_loan_monthly_obs as update_table
          SET update_table.sublan_loan_end_date_label=DATE_FORMAT(update_table.sublan_loan_end_date,'%d %M, %Y');
          
          

          #loan_end_use  lapp.decision_end_use
          UPDATE t_loan_monthly_obs as update_table
          join
          (
          select lapp_id,meta_value as decision_end_use from t_loan_monthly_obs join t_lapp_meta on t_loan_monthly_obs.lapp_id=t_lapp_meta.object_id and coll_id='lapp' and meta_key='decision_end_use'
          ) as data
          SET update_table.loan_end_use=data.decision_end_use;


          #sublan_dealer_code not in new???????
          UPDATE t_loan_monthly_obs as update_table
          join 
          (
          select meta_value as sublan_dealer_code from t_sublan_entries where coll_id='sublan' and coll_type='dealer' and meta_key='dealer_code'
          ) as data
          SET update_table.sublan_dealer_code=data.sublan_dealer_code;
          
          #loan_end_date
          UPDATE t_loan_monthly_obs as update_table
		SET 
		update_table.loan_end_date=IF(json_value(current_meta, '$.end_date'),json_value(current_meta, '$.end_date'),DATE_ADD(update_table.sublan_setup_date, INTERVAL update_table.sublan_loan_tenure MONTH));


		END IF;//
        delimiter ;

		##########################################################################

		#tenure
		UPDATE t_loan_monthly_obs as update_table
		SET 
		update_table.loan_tenure=json_value(current_meta, '$.tenure'),
		update_table.interest_rate=json_value(current_meta, '$.interest_rate');

		#loan_status
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		with q1 as (
		SELECT max(ID) as id,obs_month FROM t_loan_monthly_obs JOIN entries ON entry_date<=obs_end 
		where head ='Loan Status' group by obs_end
		)
		select obs_month ,account as loan_status from entries join q1 on entries.id=q1.ID 
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.loan_status=data.loan_status;

		#final_loan_status
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		with q1 as (
		SELECT max(ID) as id,obs_month FROM t_loan_monthly_obs JOIN entries where head ='Loan Status'  group by obs_end
		)
		select account as loan_status,obs_month from q1  join entries on  entries.ID=q1.id
		)as data
		SET update_table.final_loan_status=data.loan_status;

        #loan_city_label 
        UPDATE t_loan_monthly_obs as update_table
        join
        (
        select t_lapp_meta.meta_value as loan_city_label from t_loan_monthly_obs join t_lapp_meta on t_lapp_meta.object_id=t_loan_monthly_obs.lapp_id and coll_id='lapp' and meta_key='loan_city_label'
        ) as data
        SET update_table.loan_city_label=data.loan_city_label;

		#loan_closed_date
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,entry_date as loan_closed_date from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account ='closed' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.loan_closed_date=data.loan_closed_date;




		#loan_amount
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(debit-credit),0) as loan_amount from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account ='Loan Sanction' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.loan_amount=data.loan_amount;

		#sanction_date
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,DATE_FORMAT(min(entry_date), '%Y%m%d') as sanction_date from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account ='Loan Sanction' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.sanction_date=data.sanction_date;


		#loan_advance_instalments
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select  if(meta_value IS NULL or meta_value = '', 0, meta_value) as loan_advance_instalments from t_sublan_entries where coll_id='sublan' and  meta_key='advance_instalments'
		) as data
		SET update_table.loan_advance_instalments=data.loan_advance_instalments;

		#instalment details
		#next_instalment_date,next_instalment_due_date,next_instalment_amount
		UPDATE t_loan_monthly_obs as update_table
		SET
		update_table.next_instalment_date=json_value(current_meta, '$.next_instalment_date'),
		update_table.next_instalment_due_date=json_value(current_meta, '$.next_instalment_due_date'),
		update_table.next_instalment_amount=json_value(current_meta, '$.next_instalment_amount'),
		update_table.instalments_left=json_value(current_meta, '$.instalments_left'),
		update_table.instalments_total=json_value(current_meta, '$.instalments_total'),
		update_table.instalment_end_date=json_value(current_meta, '$.end_date');



		#loan_line_utilized -> disbursal_amount
		UPDATE t_loan_monthly_obs as update_table
		join
		(
		select obs_month,ifnull(sum(credit - debit),0) as loan_line_utilized from t_loan_monthly_obs join entries 
		on entry_date>=obs_start and entry_date<=obs_end and account ='Loan Disbursed' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.loan_line_utilized=data.loan_line_utilized;
        
        #pending_disbursal
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month, ifnull(sum(credit-debit),0) as pending_disbursal from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account ='Loan Account Pending Disbursement' and head='Loan Account' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.pending_disbursal=data.pending_disbursal;

		#first_loan_line_utilization_date -> disbursal_date
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,DATE_FORMAT(min(entry_date), '%Y%m%d') as first_loan_line_utilization_date from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account ='Loan Disbursed' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.first_loan_line_utilization_date=data.first_loan_line_utilization_date;

		#computed - first_loan_line_utilization_month
		#cum_loan_line_utilized
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(credit - debit),0) as cum_loan_line_utilized from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account ='Loan Disbursed' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_loan_line_utilized=data.cum_loan_line_utilized;



		# Closing Principal
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(debit-credit),0) as closing_principal from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account ='Loan Account Principal' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.closing_principal=data.closing_principal;

		#cum_bank_receipts
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(debit),0) as cum_bank_receipts from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and head='Bank' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_bank_receipts=data.cum_bank_receipts;

		#cum_excess_amount
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month, sum(credit-debit) as cum_excess_amount from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account ='Loan Account Excess' and head='Loan Account' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_excess_amount=data.cum_excess_amount;

		#bank_receipts
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(debit),0) as bank_receipts from t_loan_monthly_obs join entries 
		on entry_date>=obs_start and entry_date<=obs_end and head='Bank' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.bank_receipts=data.bank_receipts;

		#last_bank_receipt_date
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,max(entry_date) as last_bank_receipt_date from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and head='Bank' and debit>0 group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.last_bank_receipt_date=data.last_bank_receipt_date;


		#Cumulative Processing Fees
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(debit-credit),0) as cum_processing_fees from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account = 'Loan Account Processing Fees' and entry_set in ('GST Processing Fees','Processing Fees') group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_processing_fees=data.cum_processing_fees;

		# Cumulative Broken Period Interest
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(credit-debit),0) as cum_bpi from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account = 'Broken Period Interest' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_bpi=data.cum_bpi;

		# Cumulative Insurance Fees
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(credit-debit),0) as cum_insurance_fees from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and head ='Insurance Dealer' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_insurance_fees=data.cum_insurance_fees;


		# Cumulative Foreclosure Fees
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(debit-credit),0) as cum_foreclosure_fees from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account = 'Loan Account Foreclosure Fees' and entry_set in ('Foreclosure Fees','GST Foreclosure Fees') group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_foreclosure_fees=data.cum_foreclosure_fees;


		# Cumulative Other Interest
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(credit-debit),0) as cum_other_interest from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account = 'Days Interest' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_other_interest=data.cum_other_interest;


		# Cumulative Primary Dues - Calculated


		# Cumulative Instalment
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(debit-credit),0) as cum_instalment from t_loan_monthly_obs  join entries 
		on entry_date<=obs_end and entry_set in ('Monthly Instalment','Instalment','Advance EMI','Advance Instalments','Advance Credit') and account ='Loan Account Monthly Instalment' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_instalment=data.cum_instalment;

		# Cumulative Instalment Interest
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(credit-debit),0) as cum_instalment_interest from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and entry_set in ('Monthly Instalment','Instalment','Advance EMI','Advance Instalments','Advance Credit') and head='Interest Income' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_instalment_interest=data.cum_instalment_interest;

		# Cumulative Instalment Principal
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(credit-debit),0) as cum_instalment_principal from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and entry_set in ('Monthly Instalment','Instalment','Advance EMI','Advance Instalments','Advance Credit') and account ='Loan Account Principal' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_instalment_principal=data.cum_instalment_principal;


		# Cumulative Late Payment Fees
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(debit-credit),0) as cum_late_payment_fees from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account = 'Loan Account Late Payment Fees' and entry_set in ('GST Late Payment Fees','Late Payment Fees') group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_late_payment_fees=data.cum_late_payment_fees;

		# Cumulative Penalty Interest
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(credit-debit),0) as cum_penalty_interest from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and account = 'Penalty Interest' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_penalty_interest=data.cum_penalty_interest;

		# Cumulative Penalty Dues
		UPDATE t_loan_monthly_obs as update_table
		SET update_table.cum_penalty_dues=cum_penalty_interest + cum_late_payment_fees;

		#Penalty Dues
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month, ifnull(cum_penalty_dues - LAG(cum_penalty_dues) OVER (ORDER BY obs_month),0) AS penalty_dues  from t_loan_monthly_obs
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.penalty_dues=data.penalty_dues;

		# Instalment
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,ifnull(sum(debit-credit),0) as instalment from t_loan_monthly_obs join entries 
		on entry_date>=obs_start and entry_date<=obs_end and entry_set in ('Monthly Instalment','Instalment','Advance EMI','Advance Instalments','Advance Credit') and account ='Loan Account Monthly Instalment' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.instalment=data.instalment;

		#Instalment Interest
		UPDATE t_loan_monthly_obs as update_table
		join
		(
		select obs_month,ifnull(sum(credit-debit),0) as instalment_interest from t_loan_monthly_obs join entries
		on entry_date>=obs_start and entry_date<=obs_end and entry_set in ('Monthly Instalment','Instalment','Advance EMI','Advance Instalments','Advance Credit') and head='Interest Income' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.instalment_interest=data.instalment_interest;

		#Instalment Principal
		UPDATE t_loan_monthly_obs as update_table
		join
		(
		select obs_month,ifnull(sum(credit-debit),0) as instalment_principal from t_loan_monthly_obs join entries
		on entry_date>=obs_start and entry_date<=obs_end and entry_set in ('Monthly Instalment','Instalment','Advance EMI','Advance Instalments','Advance Credit') and account ='Loan Account Principal' group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.instalment_principal=data.instalment_principal;


		#first_instalment_month
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,EXTRACT(YEAR_MONTH FROM entries.entry_date) as first_instalment_month  from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and txn_set='Monthly Instalment' group by obs_end  limit 1
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.first_instalment_month=data.first_instalment_month;

		#first_bounce_month
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_month,EXTRACT(YEAR_MONTH FROM entries.entry_date) as first_bounce_month  from t_loan_monthly_obs join entries 
		on entry_date<=obs_end and  account = 'Loan Account Returns' group by obs_end limit 1 
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.first_bounce_month=data.first_bounce_month;





		select @max_date:=ifnull(max(entry_date),LAST_DAY(curdate())) from entries where account='closed';

		insert into t_loan_daily_obs(obs_date,row_num)	
		with 
		q0 as (
		select min(entry_date) as min_date from entries
		),
		q1 as (
		select obs_daily.obs_date from loantap_in.obs_daily join q0 
		on obs_daily.obs_date >=min_date and  obs_daily.obs_date <=@max_date
		),
		q2 as (
		select obs_date,ROW_NUMBER() OVER (ORDER BY obs_date ASC) AS row_num from q1
		)
		select obs_date,row_num from q2;


		UPDATE t_loan_daily_obs as update_table
		join 
		(
		with q0 as (
		select obs_date,ifnull(sum(debit-credit),0) as primary_dues from t_loan_daily_obs join entries 
		on entry_date=t_loan_daily_obs.obs_date and account = 'Loan Account Processing Fees' and entry_set in ('GST Processing Fees','Processing Fees') group by obs_date
		UNION All
		select obs_date,ifnull(sum(credit-debit),0) as primary_dues from t_loan_daily_obs join entries 
		on entry_date=t_loan_daily_obs.obs_date and account = 'Broken Period Interest' group by obs_date
		UNION All
		select obs_date,ifnull(sum(credit-debit),0) as primary_dues from t_loan_daily_obs join entries 
		on entry_date=t_loan_daily_obs.obs_date and head ='Insurance Dealer' group by obs_date
		UNION All
		select obs_date,ifnull(sum(credit-debit),0) as primary_dues from t_loan_daily_obs join entries 
		on entry_date=t_loan_daily_obs.obs_date and head ='Insurance Dealer' group by obs_date
		UNION All
		select obs_date,ifnull(sum(debit-credit),0) as primary_dues from t_loan_daily_obs join entries 
		on entry_date=t_loan_daily_obs.obs_date and account = 'Loan Account Foreclosure Fees' and entry_set in ('Foreclosure Fees','GST Foreclosure Fees') group by obs_date
		UNION All
		select obs_date,ifnull(sum(credit-debit),0) as primary_dues from t_loan_daily_obs join entries 
		on entry_date=t_loan_daily_obs.obs_date and account = 'Days Interest' group by obs_date
		),
		q1 as (
		select obs_date,sum(primary_dues) as added_primary_dues from q0 group by obs_date
		)
		select * from q1
		) as data
		on update_table.obs_date=data.obs_date
		SET update_table.added_primary_dues=data.added_primary_dues;




		#Instalment Interest
		UPDATE t_loan_daily_obs as update_table
		join 
		(
		select obs_date,ifnull(sum(credit-debit),0) as added_instalment_interest from t_loan_daily_obs join entries 
		on entry_date=obs_date and entry_set in ('Monthly Instalment','Instalment','Advance EMI','Advance Instalments','Advance Credit') and head='Interest Income' group by obs_date
		) as data
		on update_table.obs_date=data.obs_date
		SET update_table.added_instalment_interest=data.added_instalment_interest;

		#Instalment Principal
		UPDATE t_loan_daily_obs as update_table
		join 
		(
		select obs_date,ifnull(sum(credit-debit),0) as added_instalment_principal from t_loan_daily_obs join entries 
		on entry_date=obs_date and entry_set in ('Monthly Instalment','Instalment','Advance EMI','Advance Instalments','Advance Credit') and account ='Loan Account Principal' group by obs_date
		) as data
		on update_table.obs_date=data.obs_date
		SET update_table.added_instalment_principal=data.added_instalment_principal;


		# Penalty Dues
		UPDATE t_loan_daily_obs as update_table
		join 
		(
		with q0 as (
		select obs_date,ifnull(sum(debit-credit),0) as penalty_dues from t_loan_daily_obs join entries 
		on entry_date=t_loan_daily_obs.obs_date and account = 'Loan Account Late Payment Fees' and entry_set in ('GST Late Payment Fees','Late Payment Fees') group by obs_date
		UNION All
		select obs_date,ifnull(sum(credit-debit),0) as penalty_dues from t_loan_daily_obs join entries 
		on entry_date=t_loan_daily_obs.obs_date and account = 'Penalty Interest' group by obs_date
		),
		q1 as (
		select obs_date,sum(penalty_dues) as added_penalty_dues from q0 group by obs_date
		)
		select * from q1
		) as data
		on update_table.obs_date=data.obs_date
		SET update_table.added_penalty_dues=data.added_penalty_dues;


		# Closing Dues Account
		UPDATE t_loan_daily_obs as update_table
		join 
		(
		select obs_date,ifnull(sum(debit-credit),0) as closing_dues_account from t_loan_daily_obs join entries 
		on entry_date<=obs_date and account in ('Loan Account Dues','Loan Account Instalment Dues','Loan Account Future Dues') group by obs_date
		) as data
		on update_table.obs_date=data.obs_date
		SET update_table.closing_dues_account=data.closing_dues_account;


		UPDATE t_loan_daily_obs as update_table
		join 
		(
		select obs_date,ifnull(LAG(closing_primary_dues) OVER (ORDER BY obs_date),0) as opening_primary_dues from t_loan_daily_obs where row_num>1
		) as data
		on update_table.obs_date=data.obs_date
		SET update_table.opening_primary_dues=data.opening_primary_dues
		where row_num>1;

		UPDATE t_loan_daily_obs as update_table
		join 
		(
		select obs_date,ifnull(LAG(closing_penalty_dues) OVER (ORDER BY obs_date),0) as opening_penalty_dues from t_loan_daily_obs where row_num>1
		) as data
		on update_table.obs_date=data.obs_date
		SET update_table.opening_penalty_dues=data.opening_penalty_dues
		where row_num>1;


		select @daily_total:=count(1) from t_loan_daily_obs;

		delimiter //
		FOR i IN 1..@daily_total
		DO


		UPDATE t_loan_daily_obs as update_table
		join
		(
          select 
          closing_primary_dues as opening_primary_dues,
          closing_instalment_interest as opening_instalment_interest,
          closing_instalment_principal as opening_instalment_principal,
          closing_penalty_dues as opening_penalty_dues
          from t_loan_daily_obs where row_num=i-1
		) as data
		SET 
		update_table.opening_primary_dues=data.opening_primary_dues,
		update_table.opening_instalment_interest=data.opening_instalment_interest,
		update_table.opening_instalment_principal=data.opening_instalment_principal,
		update_table.opening_penalty_dues=data.opening_penalty_dues	
		where row_num=i;


		update t_loan_daily_obs
		set closing_penalty_dues=least(closing_dues_account,opening_penalty_dues + added_penalty_dues),
		closing_instalment_principal=least(closing_dues_account - closing_penalty_dues,opening_instalment_principal + added_instalment_principal),
		closing_instalment_interest=least(closing_dues_account - closing_penalty_dues - closing_instalment_principal,opening_instalment_interest + added_instalment_interest),
		closing_primary_dues = closing_dues_account - closing_penalty_dues - closing_instalment_principal - closing_instalment_interest
		where row_num=i;

		select @opening:=opening_instalment,@obs_date:=obs_date from t_loan_daily_obs where row_num=i;
		UPDATE t_loan_daily_obs as update_table
		join
		(
		with q0 as (
		SELECT obs_date,added_instalment,SUM(added_instalment) OVER (ORDER BY row_num DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum_before
		FROM t_loan_daily_obs where row_num0 ORDER BY row_num ASC
		)
		select ifnull(datediff(@obs_date,max(obs_date)),0) as dpd_days from q0 where sum_before>=@opening
		) as data
		SET 
		update_table.dpd_days=data.dpd_days
		where row_num=i;
		END FOR; //
        delimiter ;

		/*

		# Closing All Dues *** Change to closing_dues_account
		# take from Days 
		/*
		opening_primary_dues
		opening_instalment_interest
		opening_instalment_principal
		opening_instalment
		opening_penalty_dues
		opening_all_dues
		knocked_off

		closing_penalty_dues decimal(11,0) from days,
		closing_instalment_principal decimal(11,0) from days,
		closing_instalment_interest decimal(11,0) from days,
		closing_primary_dues from days
		*/


		/*
		loan_quality

		-----------------
		Standard			dpd_days<=30
		SMA1					dpd_days>=31 and dpd_days<=60
		SMA2					dpd_days>=61 and dpd_days<=90
		SubStandard		dpd_days>=91 and dpd_days<=365
		Doubtful			dpd_days>=366

		loan_quality_days
		---------------------
		000						dpd_days<=30
		030						dpd_days>=31 and dpd_days<=60
		060						dpd_days>=61 and dpd_days<=90
		090						dpd_days>=91 and dpd_days<=365
		365+					dpd_days>=366

		dpd
		-------------------
		Regular				dpd_days=0
		1-30
		31-60
		61-90
		91-120
		121-150
		151-180
		181-270
		271-365
		365-450
		451-540
		541+


		*/
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_date as obs_start,
		t_loan_daily_obs.opening_primary_dues,
		t_loan_daily_obs.opening_instalment_interest,
		t_loan_daily_obs.opening_instalment_principal,
		t_loan_daily_obs.opening_instalment,
		t_loan_daily_obs.opening_penalty_dues,
		t_loan_daily_obs.opening_all_dues
		from t_loan_daily_obs join t_loan_monthly_obs
		on t_loan_daily_obs.obs_date=t_loan_monthly_obs.obs_start
		) as data
		on update_table.obs_start=data.obs_start
		SET update_table.opening_primary_dues=data.opening_primary_dues,
		update_table.opening_instalment_interest=data.opening_instalment_interest,
		update_table.opening_instalment_principal=data.opening_instalment_principal,
		update_table.opening_instalment=data.opening_instalment,
		update_table.opening_penalty_dues=data.opening_penalty_dues,
		update_table.opening_all_dues=data.opening_all_dues;

		/*knocked off should be sum accross the month */
		UPDATE t_loan_monthly_obs as update_table
		join (
		select sum(t_loan_daily_obs.knocked_off) as cum_knocked_off, obs_date 
		from t_loan_daily_obs join t_loan_monthly_obs
		on EXTRACT(YEAR_MONTH from t_loan_daily_obs.obs_date) = t_loan_monthly_obs.obs_month
		) as data
		on update_table.obs_month=EXTRACT(YEAR_MONTH FROM data.obs_date ) 
		set update_table.knocked_off=ifnull(data.cum_knocked_off,0);

		/*Closing calculated bases on end date of each month*/  
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_date as obs_end,
		t_loan_daily_obs.closing_dues_account,
		t_loan_daily_obs.closing_penalty_dues,
		t_loan_daily_obs.closing_instalment_principal,
		t_loan_daily_obs.closing_instalment_interest,
		t_loan_daily_obs.closing_primary_dues,
		t_loan_daily_obs.dpd_days
		from t_loan_daily_obs join t_loan_monthly_obs
		on t_loan_daily_obs.obs_date=t_loan_monthly_obs.obs_end
		) as data
		on update_table.obs_end=data.obs_end
		SET update_table.closing_dues_account=data.closing_dues_account,
		update_table.closing_penalty_dues=data.closing_penalty_dues,
		update_table.closing_instalment_principal=data.closing_instalment_principal,
		update_table.closing_instalment_interest=data.closing_instalment_interest,
		update_table.closing_primary_dues=data.closing_primary_dues,
		update_table.dpd_days=ifnull(data.dpd_days,0);



		/*dpd movements */
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select obs_end,
		case 
		when LAG(bucket_movement_index) OVER (ORDER BY obs_date) is null then 'stable' 
		when bucket_movement_index=LAG(bucket_movement_index) OVER (ORDER BY obs_date) then 'stable'
		when bucket_movement_index>LAG(bucket_movement_index) OVER (ORDER BY obs_date) then 'flow' 
		when ((bucket_movement_index=obs_start and entry_date<=obs_end  and entry_set='Bounce'  and 
		account='Loan Account Returns'  group by entry_set_id,obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.no_of_bounces=data.no_of_bounces;

		#cum_no_of_bounces
		UPDATE t_loan_monthly_obs as update_table
		join
		(
		select obs_month,count(entry_set_id) as cum_no_of_bounces from t_loan_monthly_obs join entries
		on  entry_date<=obs_end  and entry_set='Bounce' and account='Loan Account Returns'  
		group by entry_set_id,obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.cum_no_of_bounces=data.cum_no_of_bounces;




		#loan_line_available
		UPDATE t_loan_monthly_obs as update_table
		join
		(
		select obs_month,ifnull(sum(credit - debit),0) as loan_line_available from t_loan_monthly_obs join entries
		on  entry_date<=obs_end  and account='Loan Line'  
		group by obs_end
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.loan_line_available=data.loan_line_available;



		#hypothecation lapp.debt_hypothecation
		UPDATE t_loan_monthly_obs as update_table
		join
		(
		select t_lapp_meta.meta_value as debt_hypothecation from t_loan_monthly_obs join t_lapp_meta on t_lapp_meta.object_id=t_loan_monthly_obs.lapp_id and coll_id='lapp' and meta_key='debt_hypothecation'
		) as data
		SET update_table.hypothecation=data.debt_hypothecation;

		#dealer_code
		UPDATE t_loan_monthly_obs as update_table
		join 
		(
		select meta_value as sublan_dealer_code from t_sublan_entries where coll_id='sublan' and coll_type='dealer' and meta_key='dealer_code'
		) as data
		SET update_table.dealer_code=data.sublan_dealer_code;


		#nach_status
		UPDATE t_loan_monthly_obs as update_table
		join
		(
		select obs_month,meta_value as nach_status from t_loan_monthly_obs join t_sublan_entries as sublan
		on t_loan_monthly_obs.sublan_id = sublan.object_id and coll_id='sublan' and meta_key='nach_status'
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.nach_status=data.nach_status;

		#nach_umrn
		UPDATE t_loan_monthly_obs as update_table
		join
		(
		select obs_month,meta_value as nach_umrn from t_loan_monthly_obs join t_sublan_entries as sublan
		on t_loan_monthly_obs.sublan_id = sublan.object_id and coll_id='sublan' and meta_key='nach_umrn'
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.nach_umrn=data.nach_umrn;

		#nach_mandate_id
		UPDATE t_loan_monthly_obs as update_table
		join
		(
		select obs_month,meta_value as nach_mandate_id from t_loan_monthly_obs join t_sublan_entries
		on t_loan_monthly_obs.sublan_id = t_sublan_entries.object_id and coll_id='sublan' and meta_key='nach_mandate_id'
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.nach_mandate_id=data.nach_mandate_id;




		#######################################################

		/*
		closing_all_dues decimal(11,0) GENERATED ALWAYS AS (closing_primary_dues + closing_instalment + closing_penalty_dues) STORED,
		closing_instalment decimal(11,0) GENERATED ALWAYS AS (closing_instalment_principal + closing_instalment_interest) STORED,
		*/	

		#instalment_consumed  formula

		# Instalment Consumed - formula 	

		#Relevant Principal
		UPDATE t_loan_monthly_obs as update_table
		join 
		(

		with q0 as (
		select obs_month,instalment_consumed from t_loan_monthly_obs
		),
		q1 as (
		select  q0.obs_month,t_loan_monthly_obs.obs_month as relevant_month from q0 join t_loan_monthly_obs on
		t_loan_monthly_obs.cum_instalment<=q0.instalment_consumed
		),
		q2 as (
		select obs_month,max(relevant_month) as max_month from q1 group by obs_month
		)
		select q2.obs_month,t_loan_monthly_obs.closing_principal as relevant_principal from q2 join t_loan_monthly_obs 
		on q2.max_month=t_loan_monthly_obs.obs_month
		) as data
		on update_table.obs_month=data.obs_month
		SET update_table.relevant_principal=data.relevant_principal;	



		delete from data_store.loan_monthly_obs where sublan_id=@object_id;	


		insert into data_store.loan_monthly_obs( lapp_id,
      loan_id,
      lan_id,
      customer_id,
      sublan_id,
      nbfc,
      product_or_scheme,
      product_category,
      obs_start,
      obs_end,
      obs_month,
      sublan_loan_tenure,
      sublan_loan_interest_rate,
      sublan_loan_amount,
      sublan_setup_date,
      sublan_advance_instalments,
      sublan_dealer_code,
      sublan_instalment_method,
      sublan_virtual_account,
      sublan_loan_end_date,
sublan_loan_end_date_label,
      loan_amount,
      loan_tenure,
      interest_rate,
      loan_status,
      final_loan_status,
      loan_city_label,
      loan_product,
      bureau_account_type,
      closing_principal,
      cum_bank_receipts,
      bank_receipts,
      last_bank_receipt_date,
      cum_excess_amount,
      cum_processing_fees,
      cum_bpi,
      cum_insurance_fees,
      cum_foreclosure_fees,
      cum_other_interest,
      cum_instalment,
      cum_instalment_interest,
      cum_instalment_principal,
      cum_late_payment_fees,
      cum_penalty_interest,
      cum_penalty_dues,
      penalty_dues,
      instalment,
      instalment_interest,
      instalment_principal,
      closing_dues_account,
      opening_primary_dues,
      opening_instalment_interest,
      opening_instalment_principal,
      opening_instalment,
      opening_penalty_dues,
      opening_all_dues,
      knocked_off,
      closing_all_dues,
      closing_penalty_dues,
      closing_instalment_principal,
      closing_instalment_interest,
      closing_primary_dues,
      relevant_principal,
      dpd_days,
      dpd_movement,
      loan_line_utilized,
      pending_disbursal,
      loan_line_available,
      first_loan_line_utilization_date,
      cum_loan_line_utilized,
      cum_principal_receipt_count,
      sanction_date,
      first_instalment_month,
      first_bounce_month,
      loan_closed_date,
      no_of_bounces,
      cum_no_of_bounces,
      loan_end_date,
      loan_end_date_label,
      loan_advance_instalments,
      loan_end_use,
      hypothecation,
      dealer_code,
      nach_status,
      nach_umrn,
      nach_mandate_id,
      disbursal_beneficiary,
      instalments_left,
      instalments_total,
      next_instalment_date,
      next_instalment_due_date,
      next_instalment_amount,
      instalment_end_date)
		select  lapp_id,
      loan_id,
      lan_id,
      customer_id,
      sublan_id,
      nbfc,
      product_or_scheme,
      product_category,
      obs_start,
      obs_end,
      obs_month,
      sublan_loan_tenure,
      sublan_loan_interest_rate,
      sublan_loan_amount,
      sublan_setup_date,
      sublan_advance_instalments,
      sublan_dealer_code,
      sublan_instalment_method,
      sublan_virtual_account,
      sublan_loan_end_date,
       sublan_loan_end_date_label,
      loan_amount,
      loan_tenure,
      interest_rate,
      loan_status,
      final_loan_status,
      loan_city_label,
      loan_product,
      bureau_account_type,
      closing_principal,
      cum_bank_receipts,
      bank_receipts,
      last_bank_receipt_date,
      cum_excess_amount,
      cum_processing_fees,
      cum_bpi,
      cum_insurance_fees,
      cum_foreclosure_fees,
      cum_other_interest,
      cum_instalment,
      cum_instalment_interest,
      cum_instalment_principal,
      cum_late_payment_fees,
      cum_penalty_interest,
      cum_penalty_dues,
      penalty_dues,
      instalment,
      instalment_interest,
      instalment_principal,
      closing_dues_account,
      opening_primary_dues,
      opening_instalment_interest,
      opening_instalment_principal,
      opening_instalment,
      opening_penalty_dues,
      opening_all_dues,
      knocked_off,
      closing_all_dues,
      closing_penalty_dues,
      closing_instalment_principal,
      closing_instalment_interest,
      closing_primary_dues,
      relevant_principal,
      dpd_days,
      dpd_movement,
      loan_line_utilized,
      pending_disbursal,
      loan_line_available,
      first_loan_line_utilization_date,
      cum_loan_line_utilized,
      cum_principal_receipt_count,
      sanction_date,
      first_instalment_month,
      first_bounce_month,
      loan_closed_date,
      no_of_bounces,
      cum_no_of_bounces,
      loan_end_date,
      loan_end_date_label,
      loan_advance_instalments,
      loan_end_use,
      hypothecation,
      dealer_code,
      nach_status,
      nach_umrn,
      nach_mandate_id,
      disbursal_beneficiary,
      instalments_left,
      instalments_total,
      next_instalment_date,
      next_instalment_due_date,
      next_instalment_amount,
      instalment_end_date from t_loan_monthly_obs;	

delete from data_store.loan_dataset
		where sublan_id=@object_id;
insert into data_store.loan_dataset(lapp_id,
      loan_id,
      lan_id,
      customer_id,
      sublan_id,
      nbfc,
      product_or_scheme,
      product_category,
      obs_start,
      obs_end,
      obs_month,
      sublan_loan_tenure,
      sublan_loan_interest_rate,
      sublan_loan_amount,
      sublan_setup_date,
      sublan_advance_instalments,
      sublan_dealer_code,
      sublan_instalment_method,
      sublan_virtual_account,
      sublan_loan_end_date,
       sublan_loan_end_date_label,
      loan_amount,
      loan_tenure,
      interest_rate,
      loan_status,
      final_loan_status,
      loan_city_label,
      loan_product,
      bureau_account_type,
      closing_principal,
      cum_bank_receipts,
      bank_receipts,
      last_bank_receipt_date,
      cum_excess_amount,
      cum_processing_fees,
      cum_bpi,
      cum_insurance_fees,
      cum_foreclosure_fees,
      cum_other_interest,
      cum_instalment,
      cum_instalment_interest,
      cum_instalment_principal,
      cum_late_payment_fees,
      cum_penalty_interest,
      cum_penalty_dues,
      penalty_dues,
      instalment,
      instalment_interest,
      instalment_principal,
      closing_dues_account,
      opening_primary_dues,
      opening_instalment_interest,
      opening_instalment_principal,
      opening_instalment,
      opening_penalty_dues,
      opening_all_dues,
      knocked_off,
      closing_all_dues,
      closing_penalty_dues,
      closing_instalment_principal,
      closing_instalment_interest,
      closing_primary_dues,
      relevant_principal,
      dpd_days,
      dpd_movement,
      loan_line_utilized,
      pending_disbursal,
      loan_line_available,
      first_loan_line_utilization_date,
      cum_loan_line_utilized,
      cum_principal_receipt_count,
      sanction_date,
      first_instalment_month,
      first_bounce_month,
      loan_closed_date,
      no_of_bounces,
      cum_no_of_bounces,
      loan_end_date,
      loan_end_date_label,
      loan_advance_instalments,
      loan_end_use,
      hypothecation,
      dealer_code,
      nach_status,
      nach_umrn,
      nach_mandate_id,
      disbursal_beneficiary,
      instalments_left,
      instalments_total,
      next_instalment_date,
      next_instalment_due_date,
      next_instalment_amount,
      instalment_end_date )
		select lapp_id,
      loan_id,
      lan_id,
      customer_id,
      sublan_id,
      nbfc,
      product_or_scheme,
      product_category,
      obs_start,
      obs_end,
      obs_month,
      sublan_loan_tenure,
      sublan_loan_interest_rate,
      sublan_loan_amount,
      sublan_setup_date,
      sublan_advance_instalments,
      sublan_dealer_code,
      sublan_instalment_method,
      sublan_virtual_account,
      sublan_loan_end_date,
       sublan_loan_end_date_label,
      loan_amount,
      loan_tenure,
      interest_rate,
      loan_status,
      final_loan_status,
      loan_city_label,
      loan_product,
      bureau_account_type,
      closing_principal,
      cum_bank_receipts,
      bank_receipts,
      last_bank_receipt_date,
      cum_excess_amount,
      cum_processing_fees,
      cum_bpi,
      cum_insurance_fees,
      cum_foreclosure_fees,
      cum_other_interest,
      cum_instalment,
      cum_instalment_interest,
      cum_instalment_principal,
      cum_late_payment_fees,
      cum_penalty_interest,
      cum_penalty_dues,
      penalty_dues,
      instalment,
      instalment_interest,
      instalment_principal,
      closing_dues_account,
      opening_primary_dues,
      opening_instalment_interest,
      opening_instalment_principal,
      opening_instalment,
      opening_penalty_dues,
      opening_all_dues,
      knocked_off,
      closing_all_dues,
      closing_penalty_dues,
      closing_instalment_principal,
      closing_instalment_interest,
      closing_primary_dues,
      relevant_principal,
      dpd_days,
      dpd_movement,
      loan_line_utilized,
      pending_disbursal,
      loan_line_available,
      first_loan_line_utilization_date,
      cum_loan_line_utilized,
      cum_principal_receipt_count,
      sanction_date,
      first_instalment_month,
      first_bounce_month,
      loan_closed_date,
      no_of_bounces,
      cum_no_of_bounces,
      loan_end_date,
      loan_end_date_label,
      loan_advance_instalments,
      loan_end_use,
      hypothecation,
      dealer_code,
      nach_status,
      nach_umrn,
      nach_mandate_id,
      disbursal_beneficiary,
      instalments_left,
      instalments_total,
      next_instalment_date,
      next_instalment_due_date,
      next_instalment_amount,
      instalment_end_date  from t_loan_monthly_obs order by obs_month desc limit 1;	
commit;
select * from data_store.loan_dataset where sublan_id=@object_id;