It is a terrible motivator as the impact of a raise is short lived, we very quickly come to believe we are worth the higher pay. See Wall Street and SV salaries. Once you're happy with your pay things like challenging work, recognising success, personal development, good co-workers become much more effective.
It is a powerful de-motivator if you're being paid less than you think you should be, to the point that the weighting between the two effects is almost entirely on the de-motivation side.
However, the lack of money (or other tangible benefits) that people feel owed is a huge motivation for those people to leave.
Thus far the market has always agreed with me.
It's probably true that post-raise motivation boost is short lived, and people's expectations level out, and it might be even true that money could even act as a demotivator. But it doesn't mean people don't want more money and will quit if an opportunity shows up.
But if you're underpaying people or not giving raises, people quickly become unhappy and will either slack off or eventually leave - that's the 'certain point'! You absolutely need to keep giving good raises to keep good people there - but for most people it will just maintain their performance, not to motivate them to be better.