CREATE OR REPLACE VIEW public.location_view AS SELECT r.id as regionid, r.regionname, d.id as districtid, d.districtname, d.hckey, ct.id as countyid, ct.countyname, sb.id as subcountyid, sb.subcountyname, ph.id as parishid, ph.parishname, v.id, v.villagename FROM region r JOIN district d ON (r.id=d.region_id) JOIN county ct ON (d.id=ct.district_id) JOIN subcounty sb ON (ct.id=sb.county_id) JOIN parish ph ON (sb.id=ph.subcounty_id) JOIN village v ON (ph.id=v.parish_id); CREATE OR REPLACE VIEW public.organisation_feature_view AS SELECT comp.id, sc.id AS component_id, sc.component, sc.component_desc AS "desc", sc.type, sc.key, sc.parent_component AS parent, sc.is_active AS is_feature_active, comp.date_added, comp.is_active, org.id AS org_id, org.name AS org_name, org.short_name AS org_short_name FROM system_component sc JOIN org_component comp ON comp.system_component_id = sc.id JOIN organisation org ON org.id = comp.component_org_id; CREATE OR REPLACE VIEW public.user_permissions_view AS SELECT uar.id, ur.role_name, ur.role_desc, ur.is_active AS is_role_active, rc.is_active AS is_role_component_active, uar.date_added, uar.user_id, uar.assigned_role_added_by_id, uar.is_active AS is_user_role_active, comp.component_org_id AS org_id, comp.is_active AS is_org_comp_active, sc.id AS permission_id, sc.component AS permission, sc.component_desc AS "desc", sc.type, sc.key, sc.parent_component AS parent, sc.is_active AS is_feature_active FROM user_role ur JOIN role_component rc ON rc.user_role_id = ur.id JOIN user_assigned_role uar ON uar.assigned_role_id = ur.id JOIN org_component comp ON comp.id = rc.org_component_id JOIN system_component sc ON comp.system_component_id = sc.id; DROP VIEW public.loanapplicationsview; CREATE OR REPLACE VIEW public.loanapplicationsview AS SELECT lapp.id, lapp.loan_amount, lapp.date_added, lapp.reason, lapp.app_grace_period, lapp.loan_date, lapp.status, lapp.int_rate, lapp.int_method, cust.id AS customer_id, cust.name, cust.member_number, cust.old_member_number, cust.status AS customer_status, lp.id AS loan_product_id, lp.product_name, cust.customer_branch_id, cust_org_b.name AS customer_branch_name, loan_org_b.name AS loan_branch_name, lapp.organisation_branch_id AS loan_branch_id, lapp.is_deleted, org.id as organisation_id FROM loan_applications lapp JOIN customer cust ON lapp.customer_id = cust.id JOIN organisation_branch cust_org_b ON cust_org_b.id = cust.customer_branch_id JOIN loan_products lp ON lapp.loan_application_product_id = lp.id JOIN organisation_branch loan_org_b ON loan_org_b.id = lapp.organisation_branch_id JOIN organisation org ON org.id=loan_org_b.branch_organisation_id; DROP VIEW public.loanview; CREATE OR REPLACE VIEW public.loanview 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, lps.id AS loan_product_id, lps.product_name, lps.int_rate AS product_int_rate, lps.int_method AS product_int_method, sf.id AS loan_officer_id, sf.name AS loan_officer_full_name, cr.gender, cr.telephone 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 JOIN loan_products lps ON lps.id = lap.loan_application_product_id JOIN staff sf ON sf.id = lap.loan_officer_id; ALTER TABLE user_session_management DROP CONSTRAINT user_session_management_user_id_key; DROP VIEW public.loan_installment_repayment_view; CREATE OR REPLACE VIEW public.loan_installment_repayment_view 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, ob.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_amount, loan_disbursed.loan_start_date, loan_disbursed.loan_disbursement_date, 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, schedule.id AS schedule_id, COALESCE(schedule.principal_expected, 0::double precision) AS schedule_principal_expected, COALESCE(schedule.interest_expected, 0::double precision) AS schedule_interest_expected, schedule.expected_date AS schedule_expected_date, schedule.status AS schedule_status, COALESCE(int_waivered.amount, 0::double precision) AS int_waivered_amount, int_waivered.comment AS int_waivered_comment, pen.id AS penalty_id, COALESCE(pen.amount, 0::double precision) AS penalty_amount, pen.comment AS penalty_comment, pen_waivered.id AS penalty_waivered_id, COALESCE(pen_waivered.amount, 0::double precision) AS penalty_waivered_amount, pen_waivered.comment AS pen_waivered_comment, COALESCE(trans.amount, 0::double precision) AS paid_amount, trans.voucher_no, trans.payment_method, lmt.transaction_type, loan_approval.period_type, loan_approval.loan_period, 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, CASE WHEN loan_approval.period_type::text = 'w'::text THEN loan_disbursed.loan_start_date + make_interval(weeks => COALESCE(loan_approval.loan_period, 0)) WHEN loan_approval.period_type::text = 'm'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period, 0)) WHEN loan_approval.period_type::text = 'q'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period * 3, 0)) WHEN loan_approval.period_type::text = 'y'::text THEN loan_disbursed.loan_start_date + make_interval(years => COALESCE(loan_approval.loan_period, 0)) ELSE loan_disbursed.loan_start_date + make_interval(days => COALESCE(loan_approval.loan_period, 0)) END AS loan_end_date 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_repayments_schedule schedule ON schedule.loan_application_id = lap.id LEFT JOIN loan_penalty pen ON pen.loan_repayment_schedule_id = schedule.id LEFT JOIN loan_penalty_waivered pen_waivered ON pen_waivered.loan_repayment_schedule_id = schedule.id LEFT JOIN loan_interest_waivered int_waivered ON int_waivered.loan_repayment_schedule_id = schedule.id LEFT JOIN loan_payments payments ON payments.loan_repayment_schedule_id = schedule.id LEFT JOIN loan_main_transactions lmt ON lmt.id = payments.loan_main_transaction_id LEFT JOIN system_transactions trans ON trans.id = lmt.system_transaction_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; DROP VIEW public.loan_repayment_view; CREATE OR REPLACE VIEW public.loan_repayment_view 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, ob.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_amount, loan_disbursed.loan_start_date, loan_disbursed.loan_disbursement_date, 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, schedule.id AS schedule_id, COALESCE(schedule.principal_expected, 0::double precision) AS schedule_principal_expected, COALESCE(schedule.interest_expected, 0::double precision) AS schedule_interest_expected, schedule.expected_date AS schedule_expected_date, schedule.status AS schedule_status, COALESCE(( SELECT sum(loan_penalty.amount) AS sum FROM loan_penalty WHERE loan_penalty.loan_repayment_schedule_id = schedule.id), 0::double precision) AS penalty_amount, COALESCE(( SELECT sum(loan_penalty_waivered.amount) AS sum FROM loan_penalty_waivered WHERE loan_penalty_waivered.loan_repayment_schedule_id = schedule.id), 0::double precision) AS penalty_waivered_amount, COALESCE(( SELECT sum(loan_interest_waivered.amount) AS sum FROM loan_interest_waivered WHERE loan_interest_waivered.loan_repayment_schedule_id = schedule.id), 0::double precision) AS interest_waivered_amount, COALESCE(( SELECT sum(loan_payments.princ_paid) AS sum FROM loan_payments WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) AS princ_paid, COALESCE(( SELECT sum(loan_payments.int_paid) AS sum FROM loan_payments WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) AS int_paid, COALESCE(( SELECT sum(loan_payments.penalty_paid) AS sum FROM loan_payments WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) AS penalty_paid, COALESCE(schedule.principal_expected, 0::double precision) - COALESCE(( SELECT sum(loan_payments.princ_paid) AS sum FROM loan_payments WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) AS principal_balance, COALESCE(schedule.interest_expected, 0::double precision) - (COALESCE(( SELECT sum(loan_payments.int_paid) AS sum FROM loan_payments WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) + COALESCE(( SELECT sum(loan_interest_waivered.amount) AS sum FROM loan_interest_waivered WHERE loan_interest_waivered.loan_repayment_schedule_id = schedule.id), 0::double precision)) AS interest_balance, COALESCE(( SELECT sum(loan_penalty.amount) AS sum FROM loan_penalty WHERE loan_penalty.loan_repayment_schedule_id = schedule.id), 0::double precision) - (COALESCE(( SELECT sum(loan_payments.penalty_paid) AS sum FROM loan_payments WHERE loan_payments.loan_repayment_schedule_id = schedule.id), 0::double precision) + COALESCE(( SELECT sum(loan_penalty_waivered.amount) AS sum FROM loan_penalty_waivered WHERE loan_penalty_waivered.loan_repayment_schedule_id = schedule.id), 0::double precision)) AS penalty_balance, loan_approval.period_type, loan_approval.loan_period, 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, CASE WHEN loan_approval.period_type::text = 'w'::text THEN loan_disbursed.loan_start_date + make_interval(weeks => COALESCE(loan_approval.loan_period, 0)) WHEN loan_approval.period_type::text = 'm'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period, 0)) WHEN loan_approval.period_type::text = 'q'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period * 3, 0)) WHEN loan_approval.period_type::text = 'y'::text THEN loan_disbursed.loan_start_date + make_interval(years => COALESCE(loan_approval.loan_period, 0)) ELSE loan_disbursed.loan_start_date + make_interval(days => COALESCE(loan_approval.loan_period, 0)) END AS loan_end_date, CASE WHEN loan_approval.period_type::text = 'w'::text THEN loan_disbursed.loan_start_date + make_interval(weeks => COALESCE(loan_approval.loan_period, 0)) WHEN loan_approval.period_type::text = 'm'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period, 0)) WHEN loan_approval.period_type::text = 'q'::text THEN loan_disbursed.loan_start_date + make_interval(months => COALESCE(loan_approval.loan_period * 3, 0)) WHEN loan_approval.period_type::text = 'y'::text THEN loan_disbursed.loan_start_date + make_interval(years => COALESCE(loan_approval.loan_period, 0)) ELSE loan_disbursed.loan_start_date + make_interval(days => COALESCE(loan_approval.loan_period, 0)) END + CASE WHEN loan_disbursed.write_off_period_type::text = 'w'::text THEN make_interval(weeks => COALESCE(loan_disbursed.write_off_grace_period, 0)) WHEN loan_disbursed.write_off_period_type::text = 'bw'::text THEN make_interval(weeks => COALESCE(loan_disbursed.write_off_grace_period * 2, 0)) WHEN loan_disbursed.write_off_period_type::text = 'm'::text THEN make_interval(months => COALESCE(loan_disbursed.write_off_grace_period, 0)) WHEN loan_disbursed.write_off_period_type::text = 'q'::text THEN make_interval(months => COALESCE(loan_disbursed.write_off_grace_period * 3, 0)) WHEN loan_disbursed.write_off_period_type::text = 'y'::text THEN make_interval(years => COALESCE(loan_disbursed.write_off_grace_period, 0)) ELSE make_interval(days => COALESCE(loan_disbursed.write_off_grace_period, 0)) END AS loan_write_off_date, CASE WHEN loan_disbursed.arrears_period_type::text = 'w'::text THEN schedule.expected_date + make_interval(weeks => COALESCE(loan_disbursed.arrear_grace_period, 0)) WHEN loan_disbursed.arrears_period_type::text = 'bw'::text THEN schedule.expected_date + make_interval(weeks => COALESCE(loan_disbursed.arrear_grace_period * 2, 0)) WHEN loan_disbursed.arrears_period_type::text = 'm'::text THEN schedule.expected_date + make_interval(months => COALESCE(loan_disbursed.arrear_grace_period, 0)) WHEN loan_disbursed.arrears_period_type::text = 'q'::text THEN schedule.expected_date + make_interval(months => COALESCE(loan_disbursed.arrear_grace_period * 3, 0)) WHEN loan_disbursed.arrears_period_type::text = 'y'::text THEN schedule.expected_date + make_interval(years => COALESCE(loan_disbursed.arrear_grace_period, 0)) ELSE schedule.expected_date + make_interval(days => COALESCE(loan_disbursed.arrear_grace_period, 0)) END AS loan_arrear_date 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_repayments_schedule schedule ON schedule.loan_application_id = lap.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; ALTER TABLE public.loan_installment_repayment_view OWNER TO postgres; SELECT setval('system_component_id_seq', COALESCE((SELECT MAX(id)+1 FROM system_component), 1), false); SELECT setval('village_id_seq', COALESCE((SELECT MAX(id)+1 FROM village), 1), false); SELECT setval('parish_id_seq', COALESCE((SELECT MAX(id)+1 FROM parish), 1), false); SELECT setval('subcounty_id_seq', COALESCE((SELECT MAX(id)+1 FROM subcounty), 1), false); SELECT setval('county_id_seq', COALESCE((SELECT MAX(id)+1 FROM county), 1), false); SELECT setval('district_id_seq', COALESCE((SELECT MAX(id)+1 FROM district), 1), false); #back us -- FUNCTION: public.loan_repayment_func(timestamp without time zone, timestamp without time zone) -- DROP FUNCTION IF EXISTS public.loan_repayment_func(timestamp without time zone, timestamp without time zone); CREATE OR REPLACE FUNCTION public.loan_repayment_func( as_at timestamp without time zone, start_date timestamp without time zone DEFAULT NULL::timestamp without 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, lps.arrears_period, 0) AS arrear_grace_period, COALESCE(loan_disbursed.write_off_grace_period, lps.write_off_period, 0) AS write_off_grace_period, COALESCE(loan_disbursed.arrears_period_type, lps.arrears_period_type,''d''::character varying) AS arrears_period_type, COALESCE(loan_disbursed.write_off_period_type, lps.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 date(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 date(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 date(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 date(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 date(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 date(loan_interest_waivered.date_added) <= %L ), 0::double precision) AS interest_waivered, arrear_days(lap.id, COALESCE(loan_disbursed.arrear_grace_period, lps.arrears_period, 0), COALESCE(loan_disbursed.arrears_period_type, lps.arrears_period_type,''d''::character varying), %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, lps.arrears_period, 0) AS arrear_grace_period, COALESCE(loan_disbursed.write_off_grace_period,lps.write_off_period, 0) AS write_off_grace_period, COALESCE(loan_disbursed.arrears_period_type,lps.arrears_period_type, ''d''::character varying) AS arrears_period_type, COALESCE(loan_disbursed.write_off_period_type,lps.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 date(loan_payments.payment_date) <= %L AND date(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 date(loan_payments.payment_date) <= %L AND date(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 date(loan_payments.payment_date) <= %L AND date(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 date(loan_penalty.date_added) <= %L AND date(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 date(loan_penalty_waivered.date_added) <= %L AND date(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 date(loan_interest_waivered.date_added) <= %L AND date(loan_interest_waivered.date_added) >= %L ), 0::double precision) AS interest_waivered, arrear_days(lap.id, COALESCE(loan_disbursed.arrear_grace_period, lps.arrears_period, 0), COALESCE(loan_disbursed.arrears_period_type, lps.arrears_period_type,''d''::character varying), %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$; ALTER FUNCTION public.loan_repayment_func(timestamp without time zone, timestamp without time zone) OWNER TO postgres;