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'
), 
transf_data AS (
  SELECT 
    loan_id, 
    product_partnership_id, 
    product_id, 
    repayment_id, 
    STRING_AGG(
      collection_id :: text, 
      ',' 
      ORDER BY 
        transfer_date ASC
    ) AS collection_id 
  FROM 
    "omni-lms".transfer_data 
  WHERE 
    deleted = 0 
  GROUP BY 
    loan_id, 
    product_partnership_id, 
    product_id, 
    repayment_id
), 
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 orig_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.receipt_date AS orig_receipt_date,   
  TO_CHAR(
    TO_TIMESTAMP(cd.created_at / 1000), 
    'YYYY-MM-DD'
  ) AS omni_receipt_date,
  cd.customer_debit_amount AS customer_debit_amount, 
  COALESCE(ltd.collection_id, '') AS otd_id,
  cd.payment_method AS payment_method, 
  cd.repayment_purpose AS repayment_purpose, 
  cd.utr AS utr,   
  (
    cd.customer_debit_amount - cd.amount_paid
  ) 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 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 
  LEFT JOIN colending_bank_data bank 
    ON cd.product_partnership_id = bank.ppid :: varchar 
ORDER BY 
  cd.loan_id;