I would NEVER have sex in front of an animal. I don't think it's appropriate. Animals thrive on pheromones, they know what you are doing, and even sometimes will want to join in. That's just.... wrong.
We have always subscribed to the Pack Hierarchy Method of dog training (Cesar Milan is a good proponent of this method) and our dogs are crated at night, or when small children, people afraid of dogs or work men are in the house.
Our dogs are not allowed upstairs. That space is for the Alpha Pair and their Young. (That's Us and our kids!)They respect this authority and they actually live happily knowing they are in a Pack that takes care of them. They always know what is expected of them, what the rules are (because the rules are well laid out and consistently adhered to) and they feel safe, loved and at home. They would no more think of going into our bedroom while we were having sex than they would get up at night and fire up the BBQ!
We have never had any of the 5 or 6 dogs we've had over the years whine and/or scratch on doors when people are in those rooms. They learned as pups to not be afraid to be alone when necessary, and they know when a Human closes a door, they just have to wait.
Dogs aren't children, they are dogs. Train them well and with consistent effort when they are puppies and you will have little or no problems with them later. We have Malamutes (a seriously difficult breed to train, and strong as hell) and Labs. Labs usually want to please, Malamutes are usually more independent. We start loving training the day the puppy comes into our home. They always know what to expect and they know their Alphas and the Alpha's Young will always take care of them. Our pups are as sweet as can be, happy in their home and love their crates and the toys they are allowed to have in them. It works well for us.
We have never had cats, due to my allergies. So, I can't comment on them. I would still never allow them in the room during sex. I also think it's best for animals to learn self sufficiency so they feel good about themselves and not have to rely on their humans to be able to sleep. But, I don't know much about cats.