Weird I was just writing about this exact idea over at Reddit.com.
Anyways I'm waiting for a final test, but my guess is that Yes it will replace my need for a Point & Shoot.
I recently had been considering selling my Digital Rebel for one of those new tiny Micro 4/3's cameras with replaceable lenses. I simply got sick of having to decide on days where nothing special was supposed to happen whether I was going to lug my DSLR around or have to rely on a crappy 3g picture.
However after reading articles like arstechnica.com/apple/news/2010/06/sizing-up-the-iphone-4-for-shutterbugs.ars over at Ars Technica, I have a decent amount of realistic hope that the iPhone 4's camera will compe pretty close to suiting my every day needs.
Basically, Apple did exactly what I've been wishing for, they ignored what everyone else was doing as far as quantitative specs go-and just did whatever it took to have a good image in the end. I don't care if an EVO owner can claim to have 8mp, or if the Sony Ericsson phone has optical zoom. First and foremost I want the image that I'm taking to be nice, clear, and devoid of as much noise as possible. Then you can work on any other features you want.