Untitled
unknown
plain_text
24 days ago
5.6 kB
2
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 ), originator_data AS( SELECT rpa.loan_id, rpa.product_partnership_id, rpa.product_id, rpa.repayment_id, SUM(rpa.amount_allocated) AS originator_allocation FROM "omni-lms".repayment_collection rc LEFT OUTER JOIN "omni-lms".repayment_allocation rpa ON rc.repayment_id = rpa.repayment_id AND rc.product_partnership_id = rpa.product_partnership_id JOIN "omni-lms".disbursement dis ON rpa.loan_id = dis.loan_id AND rpa.product_partnership_id = dis.product_partnership_id AND rpa.product_id = dis.product_id WHERE rpa.deleted = 0 AND rc.deleted = 0 and dis.deleted = 0 AND rpa.funding_allocation != 1.0 AND rpa.partner_id = dis.partner_id GROUP BY rpa.loan_id, rpa.product_id, rpa.product_partnership_id, rpa.repayment_id ), lender_data AS( SELECT rpa.loan_id, rpa.product_partnership_id, rpa.repayment_id, SUM(rpa.amount_allocated) AS lender_allocation FROM "omni-lms".repayment_collection rc LEFT OUTER JOIN "omni-lms".repayment_allocation rpa ON rc.repayment_id = rpa.repayment_id AND rc.product_partnership_id = rpa.product_partnership_id JOIN "omni-lms".disbursement dis ON rpa.loan_id = dis.loan_id AND rpa.product_partnership_id = dis.product_partnership_id WHERE rpa.deleted = 0 and rc.deleted = 0 and dis.deleted = 0 AND rpa.funding_allocation != 1.0 AND rpa.partner_id != dis.partner_id GROUP BY rpa.loan_id, rpa.product_partnership_id, rpa.repayment_id ), rep_summ AS ( SELECT * FROM "omni-lms".repayment_summary WHERE deleted = 0 AND funding_allocation = 1 and latest = 'TRUE' ), transf_data AS ( SELECT loan_id, product_partnership_id, product_id, repayment_id, STRING_AGG( transfer_date :: text, ',' ORDER BY transfer_date ASC ) AS transfer_date, STRING_AGG( escrow_transfer_utr :: text, ',' ORDER BY transfer_date ASC ) AS escrow_transfer_utr, STRING_AGG( collection_id :: text, ',' ORDER BY transfer_date ASC ) AS collection_id FROM "omni-lms".transfer_data WHERE deleted = 0 and loan_id = '100255468169' Group BY loan_id, product_partnership_id, product_id, repayment_id ), loan_id_mapping_data AS ( SELECT dis.loan_id as parent_loan_id, dis.product_partnership_id, lim.related_loan_id, dis.partner_id FROM "omni-lms".disbursement dis left join "omni-lms".loan_id_mapping as lim on dis.loan_id = lim.parent_loan_id and dis.product_partnership_id = lim.product_partnership_id where lim.related_loan_id is null or ( lim.mapping_type = 'LENDER' AND lim.deleted = 0 ) ), 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, lim.related_loan_id as lender_loan_id, bank.bank_name as lender_name, cd.repayment_id AS orig_payment_ref_number, cd.debit_date AS customer_debit_date, cd.debit_date AS value_date, cd.receipt_date AS orig_receipt_date, rs.excess_amount AS excess_amount, bank.bank_name AS credit_bank_name, cd.credit_bank_account_number AS credit_bank_account_number, cd.collection_account_type AS credit_account_type, TO_CHAR( TO_TIMESTAMP(cd.created_at / 1000), 'YYYY-MM-DD' ) AS omni_receipt_date, cd.amount_paid AS allocation_to_cx_loan, cd.payment_method AS payment_method, cd.repayment_purpose AS repayment_purpose, cd.utr AS utr, COALESCE(od.originator_allocation, 0) AS originator_share, COALESCE(ld.lender_allocation, 0) AS bank_nbfc_share, COALESCE(ltd.transfer_date, '') AS escrow_transfer_date, COALESCE(ltd.escrow_transfer_utr, '') AS escrow_transfer_utr, COALESCE(ltd.collection_id, '') AS otd_id, cd.customer_debit_amount AS customer_debit_amount, ( cd.customer_debit_amount - cd.amount_paid ) AS parked_towards_charges 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 originator_data od ON cd.loan_id = od.loan_id AND cd.repayment_id = od.repayment_id LEFT JOIN lender_data ld ON cd.loan_id = ld.loan_id AND cd.repayment_id = ld.repayment_id LEFT JOIN transf_data ltd ON cd.loan_id = ltd.loan_id AND cd.product_partnership_id = ltd.product_partnership_id AND cd.repayment_id = ltd.repayment_id AND ( ltd.product_id IS NULL OR od.product_id != ltd.product_id ) LEFT join loan_id_mapping_data lim on lim.parent_loan_id = ld.loan_id and lim.product_partnership_id = ld.product_partnership_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