Few things....
1-The SE 790a should work fine for Cingular. It has the 850/1900 bands. Don't know how the lack of the 900 band will affect you in the U.S. I think it will only be an issue if you travel abroad. It does have an awesome camera (based on plenty of reviews).
2-There are no modifications needed to make it work on Cingular's network. You may not even need to set it up for the SMS/MMS/Web sessions (not sure here). If you have a recent SIM card (~1-2 years) it should read the settings from the SIM card. Not sure on the SE phones, but that's how it works on my Nokia N73. Even if you do have to put the web and SMS sessions into the phone....it's no harder than typing a text message. Sometimes a bit challenging to find the right menu though...
3-Now....one thing I've heard but not confirmed....is that if you are using a non Cingular branded phone...and sending MMS messages (images/video or multimedia messages)....Cingular automatically resizes images/video sent to/from that unbranded device to be very small. Check the Cingular forums, several threads on this. One way around this is to take the pictures or videos with the phone, load/transfer them up on your desktop, and then use your regular email to send them. This is what I do sending pics of the grandkids to my parents (they don't have data plans on their cell phones).
4-You may also look into the N73 (sorry, a bit of a fanboy, I love this phone). I just got one back in December. The only other phone I was considering was the k790a that you are looking at. Several reviews put the two cameras between the N73 and k790a very close to each other in terms of quality-the only ding on the N73 is the low light capability. I went with the N73 because it runs the Symbian OS, previous positive experiences with Nokia phones, and I like to play with all the extra features/software that goes with that OS. My wife had a SE before I gave her the Moto v635 (my previous phone before the N73). I really never got comfortable with the SE user interface, but many like it.
HTH.