Is academic success in an undergraduate degree predictive of career success in the field of psychology?

Not necessarily. However, you will need to do well enough academically in graduate school to become a counselor, social worker, or psychologist. It is becoming more important for people in these fields to have some knowledge of biology since we now know that it plays an important role in mental health. Getting a Master's in counseling or social work will require less science and statistics than getting a Master's degree in psychology.