SELECT
  sales.date,
  sales.contract_number,
  clients.last_name,
  clients.first_name,
  sales.interest,
  sales.term,
  sales.payment_amount,
  sales.total_price,
  sales.financed_amount,
(
  sales.financed_amount-
  (
  SELECT
	coalesce(sum(pxi.amount), 0)
  FROM
	payments_x_invoices as pxi
  JOIN	invoices as i
	ON pxi.invoice_id = i.id
  JOIN	sales as s
	ON i.sale_id = s.id
  WHERE
	s.id = sales.id AND
	pxi.type = 'principal'
  )
) as principal
FROM
  public.sales,
  public.tours,
  public.clients
WHERE
  sales.tour_id = tours.id AND
  tours.client_id = clients.id
  AND
  sales.is_active AND
  lower(sales.contract_number) LIKE '%rci%'
  AND	NOT(sales.is_paid)
  AND	NOT(sales.is_paid_early)
  AND	NOT(sales.is_cashout)
  AND 	NOT(sales.is_pending_cancel)
  AND	NOT(sales.is_cancel)
  AND	NOT(sales.is_default);