Relevant Tables:
Table Field Name Field Descr
ACAD_DEGR_PLAN ACAD_PLAN Code for Academic Plan, or Major/Minor
ACAD_DEGR_SPLN ACAD_PLAN Code for Academic Plan, or Major/Minor
ACAD_PLAN ACAD_PLAN Code for Academic Plan, or Major/Minor
ACAD_PLAN_OWNER ACAD_PLAN Code for Academic Plan, or Major/Minor
ACAD_PLAN_TBL ACAD_PLAN Code for Academic Plan, or Major/Minor
ACAD_SUBPLAN ACAD_PLAN Code for Academic Plan, or Major/Minor
ACAD_SUBPLN_TBL ACAD_PLAN Code for Academic Plan, or Major/Minor
I'm not sure about your data but this may do it. But now that i've thought about it double majors or not declaring one may be a problem... but its an idea.
FROM (PS_STDNT_CAR_TERM A LEFT OUTER JOIN PS_SCC_EMAIL_QVW B ON A.EMPLID = B.EMPLID AND B.E_ADDR_TYPE = 'CAMP' ), PS_SCC_PERDATA_QVW C, PS_TERM_TBL E, (PS_ACAD_PROG F LEFT OUTER JOIN PS_SCC_VISA_P_QVW G ON F.EMPLID = G.EMPLID AND G.EFFDT >= F.EFFDT ), ((PS_ACAD_PLAN H LEFT OUTER JOIN PS_ACAD_SUBPLAN J ON H.EMPLID = J.EMPLID AND H.ACAD_CAREER = J.ACAD_CAREER AND H.STDNT_CAR_NBR = J.STDNT_CAR_NBR AND H.EFFSEQ = J.EFFSEQ AND H.ACAD_PLAN = J.ACAD_PLAN AND J.EFFDT <= SYSDATE ) LEFT OUTER JOIN PS_ACAD_SUBPLN_TBL D ON J.ACAD_PLAN = D.ACAD_PLAN AND J.ACAD_SUB_PLAN = D.ACAD_SUB_PLAN AND D.EFFDT <= SYSDATE ), (PS_ACAD_PROG K LEFT OUTER JOIN PS_ACAD_PROG L ON K.EMPLID = L.EMPLID AND K.ACAD_CAREER = L.ACAD_CAREER AND K.STDNT_CAR_NBR = L.STDNT_CAR_NBR AND K.EFFSEQ = L.EFFSEQ AND L.EFFDT <= K.EFFDT AND L.PROG_ACTION = 'MATR' AND L.ADMIT_TERM = K.ADMIT_TERM ), PS_ADDRESS_NPC_VW M, PS_ACAD_PLAN_OWNER N, PS_TU_ACAD_ORG_GRP O, PS_ACAD_PLAN_TBL P
WHERE P.ACAD_PLAN_TYPE ='whatever value you use for major' AND
( A.STRM = :1
AND A.UNT_TAKEN_PRGRSS+ A.UNT_AUDIT > 0