SELECT a.row_id,
(SELECT (b.inv_net_tot_cus + b.inv_tax_tot_cus + b.crn_net_tot_cus + b.crn_tax_tot_cus
    + b.jnl_tot_cus + b.rec_net_tot_cus + b.rec_dsc_tot_cus + b.rec_dtx_tot_cus)
    FROM ccc.ar_cust_totals b
    WHERE b.cust_row_id = a.row_id AND b.tran_date <= '2015-09-30'
    ORDER BY b.tran_date DESC LIMIT 1)
    as "balance_cust AS [DECTEXT]",

COALESCE(SUM(due_curr.amount_cust), 0) +
    COALESCE(SUM(alloc_curr.alloc_cust + alloc_curr.disc_cust), 0)
    AS "bal_cust_curr AS [DECTEXT]",
COALESCE(SUM(due_30.amount_cust), 0) +
    COALESCE(SUM(alloc_30.alloc_cust + alloc_30.disc_cust), 0)
    AS "bal_cust_30 AS [DECTEXT]",
COALESCE(SUM(due_60.amount_cust), 0) +
    COALESCE(SUM(alloc_60.alloc_cust + alloc_60.disc_cust), 0)
    AS "bal_cust_60 AS [DECTEXT]",
COALESCE(SUM(due_90.amount_cust), 0) +
    COALESCE(SUM(alloc_90.alloc_cust + alloc_90.disc_cust), 0)
    AS "bal_cust_90 AS [DECTEXT]",
COALESCE(SUM(due_120.amount_cust), 0) +
    COALESCE(SUM(alloc_120.alloc_cust + alloc_120.disc_cust), 0)
    AS "bal_cust_120 AS [DECTEXT]"

FROM ccc.ar_customers a
LEFT JOIN ccc.ar_trans trans ON trans.cust_row_id = a.row_id

LEFT JOIN ccc.ar_trans_due due_curr ON
    due_curr.tran_type = trans.tran_type AND due_curr.tran_row_id = trans.tran_row_id
    AND trans.tran_date > '2015-08-31'
LEFT JOIN ccc.ar_trans_alloc alloc_curr ON
    alloc_curr.due_row_id = due_curr.row_id
LEFT JOIN ccc.ar_trans trans_alloc_curr ON
    trans_alloc_curr.tran_type = alloc_curr.tran_type AND
    trans_alloc_curr.tran_row_id = alloc_curr.tran_row_id AND
    trans_alloc_curr.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_30 ON
    due_30.tran_type = trans.tran_type AND due_30.tran_row_id = trans.tran_row_id
    AND trans.tran_date > '2015-07-31' AND trans.tran_date <= '2015-08-31'
LEFT JOIN ccc.ar_trans_alloc alloc_30 ON
    alloc_30.due_row_id = due_30.row_id
LEFT JOIN ccc.ar_trans trans_alloc_30 ON
    trans_alloc_30.tran_type = alloc_30.tran_type AND
    trans_alloc_30.tran_row_id = alloc_30.tran_row_id AND
    trans_alloc_30.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_60 ON
    due_60.tran_type = trans.tran_type AND due_60.tran_row_id = trans.tran_row_id
    AND trans.tran_date > '2015-06-30' AND trans.tran_date <= '2015-07-31'
LEFT JOIN ccc.ar_trans_alloc alloc_60 ON
    alloc_60.due_row_id = due_60.row_id
LEFT JOIN ccc.ar_trans trans_alloc_60 ON
    trans_alloc_60.tran_type = alloc_60.tran_type AND
    trans_alloc_60.tran_row_id = alloc_60.tran_row_id AND
    trans_alloc_60.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_90 ON
    due_90.tran_type = trans.tran_type AND due_90.tran_row_id = trans.tran_row_id
    AND trans.tran_date > '2015-05-31' AND trans.tran_date <= '2015-06-30'
LEFT JOIN ccc.ar_trans_alloc alloc_90 ON
    alloc_90.due_row_id = due_90.row_id
LEFT JOIN ccc.ar_trans trans_alloc_90 ON
    trans_alloc_90.tran_type = alloc_90.tran_type AND
    trans_alloc_90.tran_row_id = alloc_90.tran_row_id AND
    trans_alloc_90.tran_date <= '2015-09-30'

LEFT JOIN ccc.ar_trans_due due_120 ON
    due_120.tran_type = trans.tran_type AND due_120.tran_row_id = trans.tran_row_id
    AND trans.tran_date <= '2015-05-31'
LEFT JOIN ccc.ar_trans_alloc alloc_120 ON
    alloc_120.due_row_id = due_120.row_id
LEFT JOIN ccc.ar_trans trans_alloc_120 ON
    trans_alloc_120.tran_type = alloc_120.tran_type AND
    trans_alloc_120.tran_row_id = alloc_120.tran_row_id AND
    trans_alloc_120.tran_date <= '2015-09-30'

WHERE a.ledger_row_id = ? AND a.party_row_id = ? AND a.deleted_id = ?
GROUP BY a.row_id