--
-- ec_earlier_certs_on_one_order/2
--
create or replace function ec_earlier_certs_on_one_order(
  integer,
  integer
) returns numeric as $$

DECLARE
	v_gift_certificate_id	alias for $1;
	v_order_id		alias for $2;
        bal_amount_used         numeric;
BEGIN
        select coalesce(sum(u.amount_used),0)-coalesce(sum(u.amount_reinstated),0) into bal_amount_used
        from ec_gift_certificate_usage u, ec_gift_certificates g, ec_gift_certificates g2
        where u.gift_certificate_id=g.gift_certificate_id
        and g2.gift_certificate_id=v_gift_certificate_id
        and u.order_id=v_order_id
        and (g.expires < g2.expires or (g.expires = g2.expires and g.gift_certificate_id < g2.gift_certificate_id));

        return bal_amount_used;
END;$$ language plpgsql;