Untitled

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;