"One is not allowed to be drunk during intimacy with one's spouse.
See... Issurei Biya 21:12."
The Rambam does not use the word אסור (forbidden) as he does by the case of thinking of another woman, rather he uses לֹא - i.e. "should not", which indicates that we are not dealing with an outright prohibition.
In the continuation of the halakhah there, the Rambam describes the anticipated outcome for the children of such a union:
ואם עשה כן--הבנים אינן הגונים, אלא מהן עזי פנים, ומהן מורדים ופושעים
If he does so, the children will not be of proper character. There
will be those who are brazen and others who are rebellious and sinful.
This parallels the sugya of בני תשע מידות (children born of nine attributes) in Nedarim 20b. This indicates that we are dealing with character development more than outright issur (prohibition) per se. I say this not to undermine the gravity with which the exhortation should be taken, rather to put it in its proper context.
For confirmation of this, the Yam Shel Shelomo (Yebhamoth 22) wrote:
הרמב"ם השמיט בן הנדה מט' מידות שהרי הוא בא מאיסורי כרת ואינו דומה לכל
אינך שאין בהן איסור ממש אלא שפוגמי' הולד
The Rambam omitted the one concieved from a niddah from the nine
traits for it is generated from the prohibition incurring kareth and
is not similar to all the rest which do not involve an actual
prohibition (issur mamash) but rather are the cause of defect to offspring
Given that we are talking about Hazalic prescriptions in aid of the cultivation of ethical perfection, it is proper to explore wherein lies the failing in engaging in relations whilst intoxicated.
The Lebhush OH 240:3 states:
שכרות אין בהם כוונת אהבה
Those that are intoxicated do not have the intent of love.
Which is to say, that while one's mind is in a state of stupefaction it is not a genuine union of partners but rather errs more to the simple satisfaction of base desire.
The Raban ha-Yarhi on Kallah wrote:
בני ישינה אין לה כוונת המצוה והיא כשכרות
The reason against those conceived by slumbered relations is due to
the lack of intent to perform the commandment, just as by
intoxication.
Though he frames it slightly different about what the positive intent should be, it seems clear that it is agreed that a lack of intent is what is problematic about engaging in relations while intoxicated. Leaving just the satisfaction of lust at play rather than anything that transcends it.
R. Teherani, 'Amude Mishpat, vol. 1, no. 8 makes this more explicit and states:
בני שכרות ביאתו לאו ביאה גמורה אלא כביאת זנות היא שאין זה מתכוין ...
לשם מה בועל אותה אלא רק כדי למלאות תאות יצרו והוי כזנות
Relations of intoxication are not complete relations, rather they are
the relations of licentiousness because the person does not have
intent... for what does he engage in relations with her? for none
other than to satisfy his desire, and so it is like licentiousness
As regards tipsy vs outright drunk and this intoxicant vs another intoxicant, I think that there is a great degree of subjectivity in all this and one must take great stock of themselves in order to be sure they are capable and ready to engage in relations with the proper intentions in mind. I don't think a measurement for שכרות in this matter is a quantifiable measurement of liquid volume or pharmaceutical dosage, rather it would be a function of taking personal stock. A person must look within and make the determination about the propriety of engaging in relations at that moment. Under the influence of the intoxicant, is the person just seeking to objectify the other partner in order to satisfy their base desire? Or do they want to join together with their partner with the intent of love, marital duty, commandment? Its obviously not a strict binary and operates along a spectrum, which is why it is notable that the Rambam includes this halakhah in H. De'oth 5:4-5 as regards the proper conduct of a talmid hakham (wise student). Its place there demonstrating that this is an area within which one can work and attain lesser or greater degrees of perfection. Again, I think the key to it is taking a genuine look within before proceeding.