Subtraction with numeric_Std doesn't work

Fair enough. Using package and functions may be good exercise for study but you don't really need two adders and two subtractors for same operations.

Thus, for practical design I will do the following(code not tested):

library ieee;
use ieee.std_logic_1164.all;
use ieee.numeric_std.all;
entity Arith is
    port(
            op_code    : in std_logic_vector(1 downto 0);
            a_in          : in std_logic_vector(7 downto 0);
            b_in          : in std_logic_vector(7 downto 0);
            zero_det    : out std_logic;
            overflow     : out std_logic;
            alu_out      : out std_logic_vector(7 downto 0)                    
        );
end entity Arith;
architecture ALU of Arith is
signal Tmp_a    :       signed(8 downto 0) :=(others=>'0');
signal Tmp_b    :       signed8 downto 0)  :=(others=>'0');
signal Tmp_out    :    signed(8 downto 0) :=(others=>'0');
begin
    Tmp_a <= resize(signed(a_in),9);
    Tmp_b <= resize(signed(b_in),9);
    
    ALU:process(op_code,Tmp_out, Tmp_a, Tmp_b)    
    begin
        
        case op_code is
            when "00" =>     Tmp_out <= Tmp_a and Tmp_b;
            when "01" =>    Tmp_out <=  Tmp_a or Tmp_b;
            when "10" =>    Tmp_out <=  Tmp_a + Tmp_b;
            when "11" =>    Tmp_out <=    Tmp_a - Tmp_b;
            when others =>  null;
        end case;
        
            alu_out <= std_logic_vector(Tmp_out(7 downto 0));
            overflow <= Tmp_out(8) xor Tmp_out(7);
            zero_det <= '0';
            if Tmp_out = "000000000" then
                zero_det <= '1'; 
            end if;
        
    end process;
    
end architecture ALU;

Also notice that your design is all combinatorial. Nothing wrong about that but once you integrate with registers then you may decide to add registers.

Moreover you can reduce adder/subtractor to one adder only by negating the input (negation require invert and add 1, a small adder):

Tmp_out <= Tmp_a + Tmp; --before case

...

when "10" => Tmp <= Tmp_b;

when "11" => Tmp <= -Tmp_b;