Untitled

with rep_alloc as (
  SELECT 
    rpa.loan_id, 
    rpa.repayment_id, 
    rpa.due_date, 
    rpa.repayment_purpose, 
    rpa.as_of_date, 
    Sum(
      CASE WHEN rpa.balance_type = 'PRINCIPAL' THEN rpa.amount_allocated ELSE 0 END
    ) AS principal_share, 
    Sum(
      CASE WHEN rpa.balance_type = 'INTEREST' THEN rpa.amount_allocated ELSE 0 END
    ) AS interest_share, 
    Sum(
      CASE WHEN rpa.balance_type = 'CHARGES' THEN rpa.amount_allocated ELSE 0 END
    ) AS charges_share 
  FROM 
    "omni-lms".repayment_allocation rpa 
  where 
    funding_allocation = 1 
    and payment_status != 'UNPAID' 
  group by 
    1, 
    2, 
    3, 
    4, 
    5
), 
rep_summary as (
  SELECT 
    loan_id, 
    repayment_id, 
    excess_amount, 
    debit_date 
  FROM 
    "omni-lms".repayment_summary 
  where 
    funding_allocation = 1 
    and latest = true 
    and deleted = 0
), 
disb_data as (
  SELECT 
    loan_id, 
    appform_id 
  from 
    "omni-lms".disbursement 
  where 
    deleted = 0
), 
customer_info AS (
  SELECT 
    lt.appform_id AS appform_id, 
    appl.NAME AS customer_name, 
    ap.loan_id AS loan_id 
  FROM 
    "appform".loan_term lt 
    INNER JOIN "appform".applicant appl ON appl.appform_id = lt.appform_id 
    INNER JOIN "appform".appform ap ON ap.id = appl.appform_id 
  WHERE 
    appl.applicant_type = 'borrower'
) 
SELECT 
  ra.loan_id as loan_id, 
  ci.customer_name as customer_name, 
  ra.due_date as due_date, 
  ra.principal_share + ra.interest_share + ra.charges_share as total_allocated_amount, 
  CASE WHEN ra.repayment_purpose = 'EXCESS_ALLOCATION' THEN 'yes' ELSE 'no' END as excess_amount_allocated, 
  ra.principal_share as principal_share, 
  ra.interest_share as interest_share, 
  ra.charges_share as other_charges, 
  rs.excess_amount as remaining_excess_amount, 
  ra.repayment_id as repayment_id, 
  rs.debit_date as transaction_date, 
  ra.as_of_date as value_date, 
  ra.repayment_purpose as allocation_type, 
  ra.repayment_purpose as allocation_event 
FROM 
  rep_alloc ra 
  inner join disb_data d on ra.loan_id = d.loan_id 
  left join rep_summary rs on ra.loan_id = rs.loan_id 
  and ra.repayment_id = rs.repayment_id 
  left join customer_info ci on ci.appform_id = d.appform_id;