Untitled
unknown
plain_text
17 days ago
2.3 kB
3
Indexable
with rep_alloc as ( SELECT rpa.loan_id, rpa.repayment_id, rpa.due_date, rpa.repayment_purpose, rpa.as_of_date, Sum( CASE WHEN rpa.balance_type = 'PRINCIPAL' THEN rpa.amount_allocated ELSE 0 END ) AS principal_share, Sum( CASE WHEN rpa.balance_type = 'INTEREST' THEN rpa.amount_allocated ELSE 0 END ) AS interest_share, Sum( CASE WHEN rpa.balance_type = 'CHARGES' THEN rpa.amount_allocated ELSE 0 END ) AS charges_share FROM "omni-lms".repayment_allocation rpa where funding_allocation = 1 and payment_status != 'UNPAID' group by 1, 2, 3, 4, 5 ), rep_summary as ( SELECT loan_id, repayment_id, excess_amount, debit_date FROM "omni-lms".repayment_summary where funding_allocation = 1 and latest = true and deleted = 0 ), disb_data as ( SELECT loan_id, appform_id from "omni-lms".disbursement where deleted = 0 ), customer_info AS ( SELECT lt.appform_id AS appform_id, appl.NAME AS customer_name, ap.loan_id AS loan_id FROM "appform".loan_term lt INNER JOIN "appform".applicant appl ON appl.appform_id = lt.appform_id INNER JOIN "appform".appform ap ON ap.id = appl.appform_id WHERE appl.applicant_type = 'borrower' ) SELECT ra.loan_id as loan_id, ci.customer_name as customer_name, ra.due_date as due_date, ra.principal_share + ra.interest_share + ra.charges_share as total_allocated_amount, CASE WHEN ra.repayment_purpose = 'EXCESS_ALLOCATION' THEN 'yes' ELSE 'no' END as excess_amount_allocated, ra.principal_share as principal_share, ra.interest_share as interest_share, ra.charges_share as other_charges, rs.excess_amount as remaining_excess_amount, ra.repayment_id as repayment_id, rs.debit_date as transaction_date, ra.as_of_date as value_date, ra.repayment_purpose as allocation_type, ra.repayment_purpose as allocation_event FROM rep_alloc ra inner join disb_data d on ra.loan_id = d.loan_id left join rep_summary rs on ra.loan_id = rs.loan_id and ra.repayment_id = rs.repayment_id left join customer_info ci on ci.appform_id = d.appform_id;
Editor is loading...
Leave a Comment