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
), 
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;