UPDATE c_bpartner bp SET updatedby='0', updated=now(), so_creditused = (
  SELECT SUM(t.amount) as customercredit
  FROM (
    SELECT COALESCE(SUM(c_currency_convert(ps.outstandingamt * (CASE WHEN inv.issotrx = 'Y' THEN 1 ELSE -1 END), inv.c_currency_id, bp.bp_currency_id, inv.created, null, inv.ad_client_id, inv.ad_org_id)), 0) as amount
    FROM c_invoice inv
    JOIN fin_payment_schedule ps
    ON ps.c_invoice_id = inv.c_invoice_id
    WHERE inv.c_bpartner_id = bp.c_bpartner_id
    AND ps.outstandingamt <> 0
    UNION ALL
    SELECT COALESCE(SUM(c_currency_convert((p.generated_credit - p.used_credit) * (CASE WHEN p.isreceipt = 'Y' THEN -1 ELSE 1 END), p.c_currency_id, bp.bp_currency_id, p.created, null, p.ad_client_id, p.ad_org_id)), 0) as amount
    FROM FIN_PAYMENT p    
    WHERE p.c_bpartner_id = bp.c_bpartner_id
    AND (p.generated_credit - p.used_credit) <> 0
    AND p.generated_credit <> 0
    AND p.processed = 'Y'
  ) t
)