Untitled
unknown
plain_text
22 days ago
2.0 kB
3
Indexable
WITH customer_data AS( SELECT rc.loan_id, rc.product_partnership_id, rc.debit_date, rc.repayment_id, rc.payment_method, rc.repayment_purpose, rc.receipt_date, rc.amount_paid, rc.as_of_date, rc.transfer_date, rc.created_at, rc.id, rc.utr, rc.credit_bank_account_number, rc.collection_account_type, rc.amount_received as customer_debit_amount FROM omni_lms_repayment_collection rc WHERE rc.deleted = 0 ), rep_summ AS ( SELECT loan_id, repayment_id, excess_amount FROM omni_lms_repayment_summary WHERE deleted = 0 AND funding_allocation = 1 and latest = 'TRUE' ), colending_bank_data as ( SELECT p3.partnership_product_id as ppid, opm.partner_name AS bank_name FROM partner_service_partner_product_partnership p3 JOIN partner_service_omni_partner_master opm ON opm.partner_id = p3.lender_id ) SELECT cd.loan_id AS loan_id, cd.repayment_id AS unique_payment_ref_number, cd.debit_date AS customer_debit_date, rs.excess_amount AS excess_amount, cd.debit_date AS value_date, bank.bank_name AS credit_bank_name, cd.credit_bank_account_number AS credit_bank_account_number, cd.debit_date AS transaction_date, TO_CHAR( TO_TIMESTAMP(cd.created_at / 1000), 'YYYY-MM-DD' ) AS payment_receipt_date, cd.customer_debit_amount AS customer_debit_amount, cd.id as unique_system_generated_receipt_num, cd.payment_method AS payment_method, cd.repayment_purpose AS repayment_purpose, cd.utr AS utr, COALESCE(cd.customer_debit_amount, 0) - COALESCE(cd.amount_paid, 0) AS parked_towards_charges, cd.amount_paid AS allocation_to_cx_loan FROM customer_data cd LEFT JOIN rep_summ rs ON cd.loan_id = rs.loan_id AND cd.repayment_id = rs.repayment_id LEFT JOIN colending_bank_data bank ON cd.product_partnership_id = bank.ppid :: varchar ORDER BY cd.loan_id;
Editor is loading...
Leave a Comment