--
-- ec_shipment_shipping/1
--
create or replace function ec_shipment_shipping(
  integer
) returns numeric as $$

DECLARE
	v_shipment_id 		alias for $1;
        item_shipping           numeric;
        base_shipping           numeric;
        v_order_id              ec_orders.order_id%TYPE;
        min_shipment_id         ec_shipments.shipment_id%TYPE;
BEGIN
        SELECT into v_order_id order_id 
	    FROM ec_shipments where shipment_id=v_shipment_id;
        SELECT into min_shipment_id min(s.shipment_id) 
	    from ec_shipments s, ec_items i, ec_products p
	    where s.order_id = v_order_id
	    and s.shipment_id = i.shipment_id
	    and i.product_id = p.product_id
	    and p.no_shipping_avail_p = 'f';
        IF v_shipment_id=min_shipment_id THEN
                SELECT into base_shipping 
		    coalesce(shipping_charged,0) - coalesce(shipping_refunded,0)
		    FROM ec_orders where order_id=v_order_id;
        ELSE
                base_shipping := 0;
        END IF;
        SELECT into item_shipping 
	    coalesce(sum(shipping_charged),0) - coalesce(sum(shipping_refunded),0) 
	    FROM ec_items where shipment_id=v_shipment_id 
	    and item_state <> 'void';
        RETURN item_shipping + base_shipping;
END;$$ language plpgsql;