-- -- ec_cash_amount_to_refund/2 -- create or replace function ec_cash_amount_to_refund( numeric, integer ) returns numeric as $$ DECLARE v_amount alias for $1; v_order_id alias for $2; amount_paid numeric; items_amount_paid numeric; order_amount_paid numeric; amount_refunded numeric; curr_gc_amount numeric; max_cash_refundable numeric; cash_to_refund numeric; BEGIN -- the maximum amount of cash refundable is equal to -- the amount paid (in cash + certificates) for shipped items only (since -- money is not paid until an item actually ships) -- minus the amount refunded (in cash + certificates) (only occurs for shipped items) -- minus the current gift certificate amount applied to this order -- or 0 if the result is negative select sum(coalesce(price_charged,0)) + sum(coalesce(shipping_charged,0)) + sum(coalesce(price_tax_charged,0)) + sum(coalesce(shipping_tax_charged,0)) into items_amount_paid from ec_items where order_id=v_order_id and shipment_id is not null and item_state <> 'void'; select coalesce(shipping_charged,0) + coalesce(shipping_tax_charged,0) into order_amount_paid from ec_orders where order_id=v_order_id; amount_paid := items_amount_paid + order_amount_paid; amount_refunded := ec_total_refund(v_order_id); curr_gc_amount := ec_order_gift_cert_amount(v_order_id); max_cash_refundable := amount_paid - amount_refunded - curr_gc_amount; cash_to_refund := least(max_cash_refundable, v_amount); RETURN cash_to_refund; END;$$ language plpgsql;