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

DECLARE
	v_shipment_id 		alias for $1;
        item_price_tax          numeric;
        item_shipping_tax       numeric;
        base_shipping_tax       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_tax 
		coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0) 
		FROM ec_orders where order_id=v_order_id;
        ELSE
                base_shipping_tax := 0;
        END IF;
        SELECT into item_price_tax 
	coalesce(sum(price_tax_charged),0) - coalesce(sum(price_tax_refunded),0) 
	FROM ec_items where shipment_id=v_shipment_id and item_state <> 'void';
        SELECT into item_shipping_tax 
	coalesce(sum(shipping_tax_charged),0) - coalesce(sum(shipping_tax_refunded),0) 
	FROM ec_items where shipment_id=v_shipment_id and item_state <> 'void';
        RETURN item_price_tax + item_shipping_tax + base_shipping_tax;
END;$$ language plpgsql;