Mr. Franzese sent me this, a compilation of movie clips featuring the Wilhelm Scream. This particular sound effect is one that directors apparently use just for the sake of using it. I first heard about in in researching the "Kill Bill" trivia, and while the below video does not feature any "Kill Bill" segments, you'll have to trust me that it's there. According to the Wikipedia entry for it, the Wilhelm Scream was probably recorded by Sheb Wooley, the guy who later sang "Purple People Eater." So, you know, there's that.
The Wikipedia also posted a list of films that use the Wilhelm Scream.