The ubiquitous yelp first graced the screen in the 1951 flick, Distant Drums. The stock sound effect didn't get it's name until what is believed to be the second use in a 1953 movie, The Charge at Feather River. The character Private Wilhelm is shot with an arrow. He releases a sound of pain and surprise. Like you would if you were pierced by an arrow. The sound lingered in the vaults, just barely used. That is, until one Ben Burtt - yes, that one - found the original recording and used it in Star Wars: A New Hope. The reel he found was labeled "Man Being Eaten By Alligator."
The scream has been used in probably over one hundred films, television shows, and video games. You've heard it in every Star Wars and Indiana Jones movie, Lord of the Rings, Toy Story, Aeon Flux, and the list goes on and on. I have noticed that the effect is most used when characters are falling great heights or being thrown. The way the sound tapers at the end seems to suit that. It's also occasionally used for sword or lightsaber slices.
Check out this compilation video to see many of the movies and TV shows you can hear the famous scream in: