Slowbro is good in RBY because of Amnesia. It's basically built perfectly for it, having a Psychic resistance to set up on stuff like Alakazam, a hefty Defense stat so that you don't need to worry about Amnesia not pumping that up, and a good STAB in Surf that hits a good portion of the tier for neutral damage. Unfortunately, it's really slow (which Thunder Wave mitigates to a certain extent, but still), relies on Rest for recovery, and is done no favors by RBY's setup-unfriendly crit mechanics. Still, it's a good Pokemon that can be scary as hell in a late-game sweeping position.
As for GSC, Slowbro's history there is... interesting. Once upon a time, it was OU, no doubt due in part to the fascination for Growlbro held by Celia/Bob/whatever his name really was. However, nowadays, Slowbro is really just too... slow to be very useful. Thunder Wave support is real nice, but outside of that Slowbro is a poor man's bulky water that is outsped by Marowak and can't counter Tyranitar because of the Crunch weakness. So it's not really a very good bulky water at all. And if you want paralysis support + Growl for Curselax, just use Miltank, at least that gets instant recovery and Heal Bell support. Slowbro does get Reflect, but that just isn't enough to warrant it a teamslot over other stuff. Sorry Bro :(. That being said, this thing is a monster in GSC UU. It can wall pretty much whatever it wants, has enough Special Attack to actually hurt things, and is basically the best at forcing things to take Thunder Waves in a metagame that really hates dealing with paralysis. It's definitely suspect in UU.
As for ADV, as far as I'm aware it is, again, kind of a poor man's bulky water, only this time it gets Calm Mind. Not sure why anybody would use it instead of Suicune unless they wanted to gimmick. Maybe to have STAB Psychic to get past Gyarados and OHKO Gengar? Maybe to get Thunder Wave to hit Zapdos with a CM-boosted Surf before it can be hit in return by a Thunderbolt? I know, I'm reaching, but I seriously don't see why Slowbro would be preferred when it's basically a slower, worse Suicune with a Crunch weakness.