I disagree with his theory, but the fact of the matter it's a clever one. Those who agree with it can easily pull the 'you're in denial' card on those of us that don't. They could have a point, but I'd rather believe otherwise.
Freud is now well known to have faked almost all of his results. It remains a mystery to me that he is so highly regarded in the public eye, when almost every serious psychologist laughs at his ridiculous ideas.
While the field has moved on a lot since Freud, psychodynamic oriented psychologists still use the "Oedipus complex" in their clinical work. Its not really about literally wanting to sleep with your mother, but relates to patterns related to identification, achievement and posession. I speak more about it here:
At the undergraduate level, you are not going to get the full picture or cover this in any depth, so its probably just useful to think of it as a historical construct. If you want to go on to be a clinical psychologist or become decent at any form of psychotherapy accept you may have to re-evaluate this later.