CREATE OR REPLACE FUNCTION loan_repayment_func( as_at timestamp with time zone) RETURNS TABLE(customer_type_id bigint, customer_type character varying(150), organisation_id bigint, has_members boolean, customer_type_date_added timestamp with time zone, customer_id bigint, name character varying(150) , member_number character varying(150), old_member_number character varying(150), customer_status character varying(150), branch_id bigint, branch_name character varying(150), branch_short_name character varying(150), id bigint, loan_amount double precision, loan_date_added timestamp with time zone, reason character varying(1556), status character varying(150), int_rate double precision, loan_date timestamp with time zone, app_grace_period int, int_method character varying(150), is_deleted boolean, loan_sector_id bigint, loan_product_id bigint, product_name character varying(150), product_int_rate double precision, product_int_method character varying(150), approval_amount double precision, approval_date timestamp with time zone, disbursed_amount double precision, loan_start_date timestamp with time zone, loan_disbursement_date timestamp with time zone, total_interest_expected double precision, total_principal_expected double precision, total_expected double precision, arrear_grace_period int, write_off_grace_period int, arrears_period_type character varying(150), write_off_period_type character varying(150), loan_officer_id bigint, loan_officer_full_name character varying(150), gender character varying(150), telephone character varying(150), princ_paid double precision, int_paid double precision, penalty_paid double precision, total_penalty double precision, penalty_waivered double precision, interest_waivered double precision ) AS $func$ BEGIN EXECUTE format('CREATE OR REPLACE TEMP VIEW tmp AS SELECT ct.id AS customer_type_id, ct.customer_type, ct.organisation_id, ct.has_members, ct.date_added AS customer_type_date_added, cr.id AS customer_id, cr.name, cr.member_number, cr.old_member_number, cr.status AS customer_status, lap.organisation_branch_id AS branch_id, ob.name AS branch_name, ob.short_name AS branch_short_name, lap.id, lap.loan_amount, lap.date_added AS loan_date_added, lap.reason, lap.status, lap.int_rate, lap.loan_date, lap.app_grace_period, lap.int_method, lap.is_deleted, lap.loan_sector_id, lps.id AS loan_product_id, lps.product_name, lps.int_rate AS product_int_rate, lps.int_method AS product_int_method, loan_approval.loan_amount AS approval_ammount, loan_approval.approval_date, loan_disbursed.loan_amount AS disbursed_ammount, loan_disbursed.loan_start_date, loan_disbursed.loan_disbursement_date, loan_disbursed.total_interest_expected, loan_disbursed.total_principal_expected, loan_disbursed.total_expected, COALESCE(loan_disbursed.arrear_grace_period, 0) AS arrear_grace_period, COALESCE(loan_disbursed.write_off_grace_period, 0) AS write_off_grace_period, COALESCE(loan_disbursed.arrears_period_type, ''d''::character varying) AS arrears_period_type, COALESCE(loan_disbursed.write_off_period_type, ''d''::character varying) AS write_off_period_type, sf.id AS loan_officer_id, sf.name AS loan_officer_full_name, cr.gender, cr.telephone, COALESCE(( SELECT sum(loan_payments.princ_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L ), 0::double precision) AS princ_paid, COALESCE(( SELECT sum(loan_payments.int_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L ), 0::double precision) AS int_paid, COALESCE(( SELECT sum(loan_payments.penalty_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L ), 0::double precision) AS penalty_paid, COALESCE(( SELECT sum(loan_penalty.amount) AS sum FROM loan_penalty WHERE loan_penalty.loan_application_id = lap.id AND loan_penalty.date_added <= %L ), 0::double precision) AS total_penalty, COALESCE(( SELECT sum(loan_penalty_waivered.amount) AS sum FROM loan_penalty_waivered WHERE loan_penalty_waivered.loan_application_id = lap.id AND loan_penalty_waivered.date_added <= %L ), 0::double precision) AS penalty_waivered, COALESCE(( SELECT sum(loan_interest_waivered.amount) AS sum FROM loan_interest_waivered WHERE loan_interest_waivered.loan_application_id = lap.id AND loan_interest_waivered.date_added <= %L ), 0::double precision) AS interest_waivered FROM customer_type ct JOIN customer cr ON cr.branch_customer_type_id = ct.id JOIN organisation_branch ob ON ob.id = cr.customer_branch_id JOIN loan_applications lap ON lap.customer_id = cr.id LEFT JOIN loan_application_approval loan_approval ON loan_approval.loan_application_id = lap.id LEFT JOIN loan_application_disbursement loan_disbursed ON loan_disbursed.loan_application_id = lap.id JOIN loan_products lps ON lps.id = lap.loan_application_product_id JOIN staff sf ON sf.id = lap.loan_officer_id', as_at, as_at, as_at, as_at, as_at, as_at); -- return the rows of view tmp RETURN QUERY SELECT * FROM tmp; END $func$ LANGUAGE plpgsql; SELECT * FROM loan_repayment_func('2023-02-15 12:00'); CREATE OR REPLACE FUNCTION public.arrear_days( loan_id bigint, arrear_grace_period integer, arrear_grace_period_type character varying, as_at timestamp with time zone) RETURNS bigint LANGUAGE 'plpgsql' AS $BODY$ DECLARE temprow RECORD; arrear_days bigint :=0; total_princ_payments double precision :=0; arrear_date timestamp with time zone :=now(); arrear_days_count bigint :=0; BEGIN FOR temprow IN SELECT * FROM loan_repayments_schedule WHERE loan_application_id = loan_id AND status = 'active' ORDER BY payment_number ASC LOOP SELECT sum(princ_paid) INTO total_princ_payments FROM loan_payments WHERE loan_repayment_schedule_id = temprow.id AND payment_date <= as_at; if temprow.principal_expected > total_princ_payments then if arrear_grace_period_type = 'd' then arrear_date = temprow.expected_date + make_interval(days => COALESCE(arrear_grace_period, 0)); elsif arrear_grace_period_type = 'w' then arrear_date = temprow.expected_date + make_interval(weeks => COALESCE(arrear_grace_period, 0)); elsif arrear_grace_period_type = 'bw' then arrear_date = temprow.expected_date + make_interval(weeks => COALESCE(arrear_grace_period*2, 0)); elsif arrear_grace_period_type = 'm' then arrear_date = temprow.expected_date + make_interval(months => COALESCE(arrear_grace_period, 0)); elsif arrear_grace_period_type = 'q' then arrear_date = temprow.expected_date + make_interval(months => COALESCE(arrear_grace_period*3, 0)); elsif arrear_grace_period_type = 'y' then arrear_date = temprow.expected_date + make_interval(years => COALESCE(arrear_grace_period, 0)); END if; arrear_days_count:= as_at::DATE - arrear_date::DATE; if arrear_days_count > 0 then RETURN arrear_days_count; else RETURN 0; END if; END if; END LOOP; RETURN arrear_days; END; $BODY$; CREATE OR REPLACE FUNCTION loan_repayment_func( as_at timestamp with time zone) RETURNS TABLE(customer_type_id bigint, customer_type character varying(150), organisation_id bigint, has_members boolean, customer_type_date_added timestamp with time zone, customer_id bigint, name character varying(150) , member_number character varying(150), old_member_number character varying(150), customer_status character varying(150), branch_id bigint, branch_name character varying(150), branch_short_name character varying(150), id bigint, loan_amount double precision, loan_date_added timestamp with time zone, reason character varying(1556), status character varying(150), int_rate double precision, loan_date timestamp with time zone, app_grace_period int, int_method character varying(150), is_deleted boolean, loan_sector_id bigint, loan_product_id bigint, product_name character varying(150), product_int_rate double precision, product_int_method character varying(150), approval_amount double precision, approval_date timestamp with time zone, disbursed_amount double precision, loan_start_date timestamp with time zone, loan_disbursement_date timestamp with time zone, total_interest_expected double precision, total_principal_expected double precision, total_expected double precision, arrear_grace_period int, write_off_grace_period int, arrears_period_type character varying(150), write_off_period_type character varying(150), loan_officer_id bigint, loan_officer_full_name character varying(150), gender character varying(150), telephone character varying(150), princ_paid double precision, int_paid double precision, penalty_paid double precision, total_penalty double precision, penalty_waivered double precision, interest_waivered double precision, arrear_days bigint ) AS $func$ BEGIN EXECUTE format('CREATE OR REPLACE TEMP VIEW tmp AS SELECT ct.id AS customer_type_id, ct.customer_type, ct.organisation_id, ct.has_members, ct.date_added AS customer_type_date_added, cr.id AS customer_id, cr.name, cr.member_number, cr.old_member_number, cr.status AS customer_status, lap.organisation_branch_id AS branch_id, ob.name AS branch_name, ob.short_name AS branch_short_name, lap.id, lap.loan_amount, lap.date_added AS loan_date_added, lap.reason, lap.status, lap.int_rate, lap.loan_date, lap.app_grace_period, lap.int_method, lap.is_deleted, lap.loan_sector_id, lps.id AS loan_product_id, lps.product_name, lps.int_rate AS product_int_rate, lps.int_method AS product_int_method, loan_approval.loan_amount AS approval_ammount, loan_approval.approval_date, loan_disbursed.loan_amount AS disbursed_ammount, loan_disbursed.loan_start_date, loan_disbursed.loan_disbursement_date, loan_disbursed.total_interest_expected, loan_disbursed.total_principal_expected, loan_disbursed.total_expected, COALESCE(loan_disbursed.arrear_grace_period, 0) AS arrear_grace_period, COALESCE(loan_disbursed.write_off_grace_period, 0) AS write_off_grace_period, COALESCE(loan_disbursed.arrears_period_type, ''d''::character varying) AS arrears_period_type, COALESCE(loan_disbursed.write_off_period_type, ''d''::character varying) AS write_off_period_type, sf.id AS loan_officer_id, sf.name AS loan_officer_full_name, cr.gender, cr.telephone, COALESCE(( SELECT sum(loan_payments.princ_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L ), 0::double precision) AS princ_paid, COALESCE(( SELECT sum(loan_payments.int_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L ), 0::double precision) AS int_paid, COALESCE(( SELECT sum(loan_payments.penalty_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L ), 0::double precision) AS penalty_paid, COALESCE(( SELECT sum(loan_penalty.amount) AS sum FROM loan_penalty WHERE loan_penalty.loan_application_id = lap.id AND loan_penalty.date_added <= %L ), 0::double precision) AS total_penalty, COALESCE(( SELECT sum(loan_penalty_waivered.amount) AS sum FROM loan_penalty_waivered WHERE loan_penalty_waivered.loan_application_id = lap.id AND loan_penalty_waivered.date_added <= %L ), 0::double precision) AS penalty_waivered, COALESCE(( SELECT sum(loan_interest_waivered.amount) AS sum FROM loan_interest_waivered WHERE loan_interest_waivered.loan_application_id = lap.id AND loan_interest_waivered.date_added <= %L ), 0::double precision) AS interest_waivered, arrear_days(lap.id, loan_disbursed.arrear_grace_period, loan_disbursed.arrears_period_type, %L) AS arrear_days FROM customer_type ct JOIN customer cr ON cr.branch_customer_type_id = ct.id JOIN organisation_branch ob ON ob.id = cr.customer_branch_id JOIN loan_applications lap ON lap.customer_id = cr.id LEFT JOIN loan_application_approval loan_approval ON loan_approval.loan_application_id = lap.id LEFT JOIN loan_application_disbursement loan_disbursed ON loan_disbursed.loan_application_id = lap.id JOIN loan_products lps ON lps.id = lap.loan_application_product_id JOIN staff sf ON sf.id = lap.loan_officer_id', as_at, as_at, as_at, as_at, as_at, as_at, as_at); -- return the rows of view tmp RETURN QUERY SELECT * FROM tmp; END $func$ LANGUAGE plpgsql; SELECT * FROM loan_repayment_func('2023-02-15'); CREATE OR REPLACE VIEW public.loans_repayment_view AS SELECT * FROM loan_repayment_func(CAST(now() as timestamp without time zone)); CREATE OR REPLACE FUNCTION public.loan_repayment_func( as_at timestamp with time zone, start_date timestamp with time zone DEFAULT NULL::timestamp with time zone) RETURNS TABLE(customer_type_id bigint, customer_type character varying, organisation_id bigint, has_members boolean, customer_type_date_added timestamp with time zone, customer_id bigint, name character varying, member_number character varying, old_member_number character varying, customer_status character varying, branch_id bigint, branch_name character varying, branch_short_name character varying, id bigint, loan_amount double precision, loan_date_added timestamp with time zone, reason character varying, status character varying, int_rate double precision, loan_date timestamp with time zone, app_grace_period integer, int_method character varying, is_deleted boolean, loan_sector_id bigint, loan_product_id bigint, product_name character varying, product_int_rate double precision, product_int_method character varying, approval_amount double precision, approval_date timestamp with time zone, disbursed_amount double precision, loan_start_date timestamp with time zone, loan_disbursement_date timestamp with time zone, total_interest_expected double precision, total_principal_expected double precision, total_expected double precision, arrear_grace_period integer, write_off_grace_period integer, arrears_period_type character varying, write_off_period_type character varying, loan_officer_id bigint, loan_officer_full_name character varying, gender character varying, telephone character varying, princ_paid double precision, int_paid double precision, penalty_paid double precision, total_penalty double precision, penalty_waivered double precision, interest_waivered double precision, arrear_days bigint, write_off_days bigint, written_off_date timestamp with time zone, write_off_comment character varying, loan_writen_off_ammount double precision) LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE ROWS 1000 AS $BODY$ BEGIN if start_date is null then EXECUTE format('CREATE OR REPLACE TEMP VIEW tmp AS SELECT ct.id AS customer_type_id, ct.customer_type, ct.organisation_id, ct.has_members, ct.date_added AS customer_type_date_added, cr.id AS customer_id, cr.name, cr.member_number, cr.old_member_number, cr.status AS customer_status, lap.organisation_branch_id AS branch_id, ob.name AS branch_name, ob.short_name AS branch_short_name, lap.id, lap.loan_amount, lap.date_added AS loan_date_added, lap.reason, lap.status, lap.int_rate, lap.loan_date, lap.app_grace_period, lap.int_method, lap.is_deleted, lap.loan_sector_id, lps.id AS loan_product_id, lps.product_name, lps.int_rate AS product_int_rate, lps.int_method AS product_int_method, loan_approval.loan_amount AS approval_ammount, loan_approval.approval_date, loan_disbursed.loan_amount AS disbursed_ammount, loan_disbursed.loan_start_date, loan_disbursed.loan_disbursement_date, loan_disbursed.total_interest_expected, loan_disbursed.total_principal_expected, loan_disbursed.total_expected, COALESCE(loan_disbursed.arrear_grace_period, 0) AS arrear_grace_period, COALESCE(loan_disbursed.write_off_grace_period, 0) AS write_off_grace_period, COALESCE(loan_disbursed.arrears_period_type, ''d''::character varying) AS arrears_period_type, COALESCE(loan_disbursed.write_off_period_type, ''d''::character varying) AS write_off_period_type, sf.id AS loan_officer_id, sf.name AS loan_officer_full_name, cr.gender, cr.telephone, COALESCE(( SELECT sum(loan_payments.princ_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L ), 0::double precision) AS princ_paid, COALESCE(( SELECT sum(loan_payments.int_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L ), 0::double precision) AS int_paid, COALESCE(( SELECT sum(loan_payments.penalty_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L ), 0::double precision) AS penalty_paid, COALESCE(( SELECT sum(loan_penalty.amount) AS sum FROM loan_penalty WHERE loan_penalty.loan_application_id = lap.id AND loan_penalty.date_added <= %L ), 0::double precision) AS total_penalty, COALESCE(( SELECT sum(loan_penalty_waivered.amount) AS sum FROM loan_penalty_waivered WHERE loan_penalty_waivered.loan_application_id = lap.id AND loan_penalty_waivered.date_added <= %L ), 0::double precision) AS penalty_waivered, COALESCE(( SELECT sum(loan_interest_waivered.amount) AS sum FROM loan_interest_waivered WHERE loan_interest_waivered.loan_application_id = lap.id AND loan_interest_waivered.date_added <= %L ), 0::double precision) AS interest_waivered, arrear_days(lap.id, loan_disbursed.arrear_grace_period, loan_disbursed.arrears_period_type, %L) AS arrear_days, write_off_days(loan_disbursed.loan_start_date,loan_approval.loan_period,loan_approval.period_type, loan_disbursed.write_off_grace_period,loan_disbursed.write_off_period_type,%L) AS write_off_days, written_off.write_off_date AS written_off_date, written_off.description AS write_off_comment, written_off.loan_writeoff_ammount AS loan_writen_off_ammount FROM customer_type ct JOIN customer cr ON cr.branch_customer_type_id = ct.id JOIN organisation_branch ob ON ob.id = cr.customer_branch_id JOIN loan_applications lap ON lap.customer_id = cr.id LEFT JOIN loan_application_approval loan_approval ON loan_approval.loan_application_id = lap.id LEFT JOIN loan_application_disbursement loan_disbursed ON loan_disbursed.loan_application_id = lap.id LEFT JOIN loan_written_off written_off ON written_off.loan_application_id = lap.id JOIN loan_products lps ON lps.id = lap.loan_application_product_id JOIN staff sf ON sf.id = lap.loan_officer_id', as_at, as_at, as_at, as_at, as_at, as_at, as_at, as_at); else EXECUTE format('CREATE OR REPLACE TEMP VIEW tmp AS SELECT ct.id AS customer_type_id, ct.customer_type, ct.organisation_id, ct.has_members, ct.date_added AS customer_type_date_added, cr.id AS customer_id, cr.name, cr.member_number, cr.old_member_number, cr.status AS customer_status, lap.organisation_branch_id AS branch_id, ob.name AS branch_name, ob.short_name AS branch_short_name, lap.id, lap.loan_amount, lap.date_added AS loan_date_added, lap.reason, lap.status, lap.int_rate, lap.loan_date, lap.app_grace_period, lap.int_method, lap.is_deleted, lap.loan_sector_id, lps.id AS loan_product_id, lps.product_name, lps.int_rate AS product_int_rate, lps.int_method AS product_int_method, loan_approval.loan_amount AS approval_ammount, loan_approval.approval_date, loan_disbursed.loan_amount AS disbursed_ammount, loan_disbursed.loan_start_date, loan_disbursed.loan_disbursement_date, loan_disbursed.total_interest_expected, loan_disbursed.total_principal_expected, loan_disbursed.total_expected, COALESCE(loan_disbursed.arrear_grace_period, 0) AS arrear_grace_period, COALESCE(loan_disbursed.write_off_grace_period, 0) AS write_off_grace_period, COALESCE(loan_disbursed.arrears_period_type, ''d''::character varying) AS arrears_period_type, COALESCE(loan_disbursed.write_off_period_type, ''d''::character varying) AS write_off_period_type, sf.id AS loan_officer_id, sf.name AS loan_officer_full_name, cr.gender, cr.telephone, COALESCE(( SELECT sum(loan_payments.princ_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L AND loan_payments.payment_date >= %L ), 0::double precision) AS princ_paid, COALESCE(( SELECT sum(loan_payments.int_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L AND loan_payments.payment_date >= %L ), 0::double precision) AS int_paid, COALESCE(( SELECT sum(loan_payments.penalty_paid) AS sum FROM loan_payments WHERE loan_payments.loan_application_id = lap.id AND loan_payments.payment_date <= %L AND loan_payments.payment_date >= %L ), 0::double precision) AS penalty_paid, COALESCE(( SELECT sum(loan_penalty.amount) AS sum FROM loan_penalty WHERE loan_penalty.loan_application_id = lap.id AND loan_penalty.date_added <= %L AND loan_penalty.date_added >= %L ), 0::double precision) AS total_penalty, COALESCE(( SELECT sum(loan_penalty_waivered.amount) AS sum FROM loan_penalty_waivered WHERE loan_penalty_waivered.loan_application_id = lap.id AND loan_penalty_waivered.date_added <= %L AND loan_penalty_waivered.date_added >= %L ), 0::double precision) AS penalty_waivered, COALESCE(( SELECT sum(loan_interest_waivered.amount) AS sum FROM loan_interest_waivered WHERE loan_interest_waivered.loan_application_id = lap.id AND loan_interest_waivered.date_added <= %L AND loan_interest_waivered.date_added >= %L ), 0::double precision) AS interest_waivered, arrear_days(lap.id, loan_disbursed.arrear_grace_period, loan_disbursed.arrears_period_type, %L, %L) AS arrear_days, write_off_days(loan_disbursed.loan_start_date,loan_approval.loan_period,loan_approval.period_type, loan_disbursed.write_off_grace_period,loan_disbursed.write_off_period_type,%L) AS write_off_days, written_off.write_off_date AS written_off_date, written_off.description AS write_off_comment, written_off.loan_writeoff_ammount AS loan_writen_off_ammount FROM customer_type ct JOIN customer cr ON cr.branch_customer_type_id = ct.id JOIN organisation_branch ob ON ob.id = cr.customer_branch_id JOIN loan_applications lap ON lap.customer_id = cr.id LEFT JOIN loan_application_approval loan_approval ON loan_approval.loan_application_id = lap.id LEFT JOIN loan_application_disbursement loan_disbursed ON loan_disbursed.loan_application_id = lap.id LEFT JOIN loan_written_off written_off ON written_off.loan_application_id = lap.id JOIN loan_products lps ON lps.id = lap.loan_application_product_id JOIN staff sf ON sf.id = lap.loan_officer_id', as_at, start_date, as_at, start_date,as_at,start_date,as_at,start_date,as_at,start_date,as_at,start_date,as_at,start_date,as_at); END if; -- return the rows of view tmp RETURN QUERY SELECT * FROM tmp; END $BODY$; $func$ LANGUAGE plpgsql; SELECT * FROM loan_repayment_func('2023-02-15 12:00'); CREATE OR REPLACE FUNCTION public.arrear_days( loan_id bigint, arrear_grace_period integer, arrear_grace_period_type character varying, as_at timestamp with time zone, start_date timestamp with time zone default null) RETURNS bigint LANGUAGE 'plpgsql' AS $BODY$ DECLARE temprow RECORD; arrear_days bigint :=0; total_princ_payments double precision :=0; arrear_date timestamp with time zone :=now(); arrear_days_count bigint :=0; BEGIN FOR temprow IN SELECT * FROM loan_repayments_schedule WHERE loan_application_id = loan_id AND status = 'active' ORDER BY payment_number ASC LOOP if start_date is null then SELECT sum(princ_paid) INTO total_princ_payments FROM loan_payments WHERE loan_repayment_schedule_id = temprow.id AND payment_date <= as_at; else SELECT sum(princ_paid) INTO total_princ_payments FROM loan_payments WHERE loan_repayment_schedule_id = temprow.id AND payment_date <= as_at AND payment_date >= start_date; END if; if temprow.principal_expected > total_princ_payments then if arrear_grace_period_type = 'd' then arrear_date = temprow.expected_date + make_interval(days => COALESCE(arrear_grace_period, 0)); elsif arrear_grace_period_type = 'w' then arrear_date = temprow.expected_date + make_interval(weeks => COALESCE(arrear_grace_period, 0)); elsif arrear_grace_period_type = 'bw' then arrear_date = temprow.expected_date + make_interval(weeks => COALESCE(arrear_grace_period*2, 0)); elsif arrear_grace_period_type = 'm' then arrear_date = temprow.expected_date + make_interval(months => COALESCE(arrear_grace_period, 0)); elsif arrear_grace_period_type = 'q' then arrear_date = temprow.expected_date + make_interval(months => COALESCE(arrear_grace_period*3, 0)); elsif arrear_grace_period_type = 'y' then arrear_date = temprow.expected_date + make_interval(years => COALESCE(arrear_grace_period, 0)); END if; arrear_days_count:= as_at::DATE - arrear_date::DATE; if arrear_days_count > 0 then RETURN arrear_days_count; else RETURN 0; END if; END if; END LOOP; RETURN arrear_days; END; $BODY$; CREATE OR REPLACE FUNCTION public.write_off_days( loan_start_date timestamp with time zone, arrear_grace_period integer, write_off_period_type character varying) RETURNS bigint LANGUAGE 'plpgsql' AS $BODY$ DECLARE write_off_date timestamp with time zone :=now(); write_off_days_count bigint :=0; loan_approval.loan_period BEGIN if write_off_period_type = 'd' then write_off_date = loan + make_interval(days => COALESCE(write_off_grace_period, 0)); elsif write_off_period_type = 'w' then write_off_date = loan_start_date + make_interval(weeks => COALESCE(write_off_grace_period, 0)); elsif write_off_period_type = 'bw' then write_off_date = loan_start_date + make_interval(weeks => COALESCE(write_off_grace_period*2, 0)); elsif write_off_period_type = 'm' then write_off_date = loan_start_date + make_interval(months => COALESCE(write_off_grace_period, 0)); elsif write_off_period_type = 'q' then write_off_date = loan_start_date + make_interval(months => COALESCE(write_off_grace_period*3, 0)); elsif write_off_period_type = 'y' then write_off_date = loan_start_date + make_interval(years => COALESCE(write_off_grace_period, 0)); END if; write_off_days_count:= loan_start_date::DATE - write_off_date::DATE; RETURN write_off_days_count; END; $BODY$;