'Cups', in the plural. has meant 'drunken revelry' since the start of the 15th century, presumably - as xyz says - because that's what people drank from in those days. The very first use of the phrase using 'in' appeared in a 1611 version of the Bible, in the apocryphal book of Esdras Chapter 3 verse 22, which reads: "And when they are in their cups, they forget their love both to friends and brethren, and a little after draw out swords."
I'd always assumed this meant you had passed out drunk, so your head was lying on the table amongst the empty cups or glasses - though this may be completely wrong!