As you say, in Stargate Atlantis Season 1, Episode 1 ( Rising ) the city-ship of Atlantis is submerged underwater, and is protected by a Shield which holds back the water.
However, in in Stargate Atlantis Season 2 , Episode 1 ( The Seige, Part III ) the shield is modified by Rodney McKay (and Radek Zalenka) so that it can also be used as a cloak, like a Puddle Jumper (however, Atlantis is unable to have both shield and cloak active simultaneously) SHEPPARD: We disappear.
CALDWELL:
Destroy the city, you mean? SHEPPARD:
No, we just make it look like we did.
WEIR:
How?
(Rodney clicks his fingers.)
McKAY
: We cloak it.
ZELENKA:
Yes.
Yes!
The Puddle Jumpers are equipped with cloaking technology which renders them invisible to the naked eye, and to Wraith scans.
If we just simply remove one of the cloak generators from the Puddle Jumper ... McKAY: I wouldn't say simply, but yes -- by interfacing a Jumper's stealth cloak generator with the city's shield, we should be able to render the city invisible.
( Transcript on Gateworld ) (Emphasis mine)
This is part of a plan to use a nuclear warhead to trick the Wraith into thinking that Atlantis has self-destructed It is also in this same episode that the Daedalus delivers the ZPM to Atlantis, meaning that it occurs before Stargate SG-1 Season 9 Episode 13 ( Ripple Effect ) (Kudos to Lightness Races in Orbit for tracking down the quote)
scifi.stackexchange.com/users/98382/Chronocidal
The team never had to "look for it" or "find it", underwater or otherwise.
They "found" it because they had its gate address and they dialed it from Earth's gate in Antartica.
They didn't know it was underwater until they got there and tried to look out of a window.
The gate system knew how to connect the wormhole to Atlantis's gate, even though the city was underwater at the time.
scifi.stackexchange.com/users/31445/Jamie Hanrahan
The ZPM is being used to keep the city underwater, which is why the Wraith cannot detect it.
That is the nature of the "cloak".
When the humans arrive, the city powers up, further depleting the ZPM, so it cannot maintain the power-hungry shield holding back the water.
It has no choice but to surface, which leaves it vulnerable to the Wraith.
scifi.stackexchange.com/users/72071/nebogipfel
Here's the conversation you're referring to: BLACK BDU MITCHELL: All that ZPM is doing right now is allowing one-way travel from the Pegasus Galaxy to Earth.
The billions of the lives that we save is worth more the inconvenience of schlepping back and forth on the Daedalus.
MITCHELL: We need that ZPM to cloak Atlantis.
If the Wraith come back and discover the city wasn't destroyed, they'll launch another attack.
If Atlantis falls, this galaxy is the next target.
( Transcript on Gateworld )
If this SG-1 conversation had taken place before Atlantis
the show had begun, when the city was on the ocean floor, Cam wouldn't know that the city existed let alone that it was being cloaked by a ZPM (which, as you point out, it wasn't).
In fact, this conversation takes place at least a year after the Atlantis expedition had already arrived (by Gate, which works regardless), during Season 2 of Atlantis , by which point the expedition was using the Moebius ZPM to periodically cloak the city using a ZPM following the events of "The Siege" : SHEPPARD:
Well, what if they thought we were gone?
TEYLA: Then there'd be no reason for them to stay.
SHEPPARD:
Exactly!
McKAY:
Exactly what?
SHEPPARD: We disappear.
CALDWELL:
Destroy the city, you mean? SHEPPARD:
No, we just make it look like we did.
WEIR:
How?
(Rodney clicks his fingers.)
McKAY
: We cloak it.
ZELENKA:
Yes.
Yes!
The Puddle Jumpers are equipped with cloaking technology which renders them invisible to the naked eye, and to Wraith scans.
If we just simply remove one of the cloak generators from the Puddle Jumper ... McKAY: I wouldn't say simply, but yes -- by interfacing a Jumper's stealth cloak generator with the city's shield, we should be able to render the city invisible.
CALDWELL:
That's all well and good, but I think if the city were to suddenly disappear, they might be able to put one and one together.
McKAY:
Which is why we fake a self-destruct.
Have the Daedalus beam a nuke right above the city's shield, and then detonate it.
While their sensors are blinded, we cloak the city; when the smoke clears ... WEIR: ... nothing but ocean.
[..] McKAY: I can confirm that.
They're headed for hyperspace.
Look, I'm gonna keep the cloak up for a while just to be sure, but, uh, I think they bought it.
( Transcript on Gateworld )
These events are chronicled by the first seasons of the show Atlantis .
The Stargate wikia has a chronology of how the episodes intertwine between the two shows (which mostly followed air order; remember, SG1 and SGA aired concurrently for a number of years).
In fact, in the episode of Atlantis ( "Critical Mass" ) that aired directly after "Ripple Effect" : WEIR: How long before they get here?
McKAY:
A day, maybe a day and a half, depending on if they need to make a hyperspace pause along the way.
We'll need to cloak the city again.
( Transcript on Gateworld )
A very timely reminder of what Mitchell was referring to.
If you really want a plot hole from that episode, try this !
scifi.stackexchange.com/users/1167/Lightness Races in Orbit
If I'm understanding the confusion right, you missed that Stargate SG-1 and Stargate: Atlantis overlap chronologically.
Atlantis is discovered somewhere around the end of Season 7 of SG-1 and they ran in parallel for a couple years after that.
In short, the Season 9 episode of SG-1
you're commenting on takes place quite a long time after the Atlantis expedition left.
scifi.stackexchange.com/users/94653/Ruadhan2300
In  Stargate SG-1  Season 9 Episode 13,  "Ripple Effect" :  There is a conversation about Atlantis. Cam says the ZPM is being used to cloak the city from the Wraith but when Stargate Atlantis starts, the expedition team finds Atlantis submerged in the ocean... I thought the Ancients hid the city under water.   Can someone explain this? Did I miss something or is this a plot hole?
How did Atlantis come to be cloaked when it was originally submerged when the team arrived in the city?
scifi.stackexchange.com
<stargate><stargate-sg1>
scifi.stackexchange.com/questions/203372
scifi.stackexchange.com/users/110435/Kenny
Atlantis came to be cloaked because in Season 1, Episode 1, Atlantis is submerged underwater and protected by a shield to hold back the water. Then in Season 2, Episode 1, this shield is modified by Rodney McKay so that it can be used as a cloak. The shield can also be a cloak but not both at the same time and when the humans come they have the gate address and can find Atlantis that way.
Atlantis is submerged under water and protected by a shield but in Season 2, Episode 1 the shield is modified so it can be a cloak - but it cannot be both shield and cloak simultaneously. The team never had to look for Atlantis because the had its gate address and the gate system was able to connect the wormhole to Atlantis' gate. The humans have to surface when they no longer have the power to keep the shield holding back the water and after that it is vulnerable to the Wraith.