declare
        -- v_order_id              integer;
        n_items                 integer;
        n_shipped_items         integer;
        n_received_back_items   integer;
        n_void_items            integer;
        n_nonvoid_items         integer;

begin
	select count(*) into n_items from ec_items where order_id=NEW.order_id;
        select count(*) into n_shipped_items from ec_items 
	    where order_id=NEW.order_id
	    and item_state='shipped' or item_state='arrived';
        select count(*) into n_received_back_items
	    from ec_items where order_id=NEW.order_id
	    and item_state='received_back';
        select count(*) into n_void_items from ec_items 
	    where order_id=NEW.order_id and item_state='void';

        IF n_items = n_void_items THEN
            update ec_orders set order_state='void', voided_date=now()
		where order_id=NEW.order_id;
        ELSE
            n_nonvoid_items := n_items - n_void_items;
            IF n_nonvoid_items = n_received_back_items THEN
                update ec_orders set order_state='returned' 
		    where order_id=NEW.order_id;
            ELSE 
		IF n_nonvoid_items = n_received_back_items + n_shipped_items THEN
		    update ec_orders set order_state='fulfilled' 
			where order_id=NEW.order_id;
            	ELSE
		    IF n_shipped_items >= 1 or n_received_back_items >=1 THEN
			update ec_orders set order_state='partially_fulfilled'
			    where order_id=NEW.order_id;
            	    END IF;
        	END IF;
	    END IF;
	END IF;
	return new;
end;