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;