Probably best to decide whether you're interested in Visual FX compositing or Motion Graphics before asking a question like that, but what the hell, I'll answer both.
I have never used Motion, I've simply had a look at it. i haven't opened the new 3d version even though i've had fcs2 for a while now. I do know, however, that whilst After FX hasn't been the leading desktop compositing package for years, it still rules the roost in terms of motion graphics in what concerns logos, TV and advertising.
As for compositing, contrary to popular belief, Shake is still the industry standard for film and advertising (altho a lot of ads are still done on real-time packages with integrated hardware/software solutions akin to Avid suites, so that the client can sit in with the compositor - the most popular ones are Flame/Inferno, but they're dying out as computers become more advanced). Studios invested heavily in shake the moment support was dropped by buying the source code off apple. I have heard varying reports as to what will be the next big thing, and most ppl seem convinced whatever it is will take over before Apple's next compositing package appears sometime next year.
Most compositors hate After Effects for compositing, but it can do everything Shake does, and more besides (admittedly most of that more relates to motion graphics). It is not so conducive to big projects, however. It's workflow is not very helpful with complicated composites, which is why it is not as popular. However, there are many books (e.g. Advanced Studio Techniques written by a supervisor on The Day After Tomorrow) which outline how it can be used as a compositing tool and even companies that prefer to work with it (mainly in America, the only one I know of for sure is Stargate Digital, but I have seen others in job listings forums).
And obviously, yes, After FX is better at integrating with the rest of the Adobe Suite, but its not like Shake/Motion can't read .psd files. Shake, i think, can even separate out the layers of a photoshop file. They just don't have some of the bells and whistles you'd expect of a compositing/motion graphics package from the same suite.