Sound effects for commands completing in inactive tabs/windows
It's certainly achievable if you're using scripts, I often use

tput bel


at the point where work is complete. This will not only beep the terminal but put a badge on the dock icon as well.

In terms of handling exit codes then you can string a couple together for a different sound:

if command_or_exit_status
then
# exit OK
        tput bel
else
# not ok
        tput bel; tput bel;
fi