SAA_RPT_SPLN_VW

(SQL View)
 Index Back

Advising Report Career View

This view is used to retrieve and hold data to be used in creating a hard copy report in academic advising.

SELECT A.EMPLID , A.ANALYSIS_DB_SEQ , A.SAA_CAREER_RPT , B.ACAD_PROG , B.ACAD_PLAN , B.ACAD_SUB_PLAN , B.SUB_PLAN_SEQUENCE , C.DESCR , B.ACAD_SUB_PLAN_TERM , D.DESCR , C.ACAD_SUBPLAN_TYPE , E.XLATLONGNAME , ' ' , F.ITEM_R_STATUS , ' ' FROM PS_SAA_ADB_REPORT A , PS_SAA_ADB_WHATIF B , PS_ACAD_SUBPLN_TBL C , PS_TERM_TBL D , PSXLATITEM E , PS_SAA_ADB_RESULTS F WHERE A.EMPLID = B.EMPLID AND A.ANALYSIS_DB_SEQ = B.ANALYSIS_DB_SEQ AND A.SAA_CAREER_RPT = B.ACAD_CAREER AND B.ACAD_PROG <> ' ' AND B.ACAD_PLAN <> ' ' AND B.ACAD_SUB_PLAN <> ' ' AND D.INSTITUTION = A.INSTITUTION AND D.ACAD_CAREER = A.SAA_CAREER_RPT AND D.STRM = B.ACAD_SUB_PLAN_TERM AND C.INSTITUTION = A.INSTITUTION AND C.ACAD_PLAN = B.ACAD_PLAN AND C.ACAD_SUB_PLAN = B.ACAD_SUB_PLAN AND C.EFFDT = ( SELECT MAX (C1.EFFDT) FROM PS_ACAD_SUBPLN_TBL C1 WHERE C1.INSTITUTION = C.INSTITUTION AND C1.ACAD_PLAN = C.ACAD_PLAN AND C1.ACAD_SUB_PLAN = C.ACAD_SUB_PLAN AND C1.EFFDT <= D.TERM_BEGIN_DT AND C1.EFF_STATUS = 'A') AND E.FIELDNAME = 'ACAD_SUBPLAN_TYPE' AND E.FIELDVALUE = C.ACAD_SUBPLAN_TYPE AND E.EFFDT = ( SELECT MAX (E1.EFFDT) FROM PSXLATITEM E1 WHERE E1.FIELDNAME = E.FIELDNAME AND E1.FIELDVALUE = E.FIELDVALUE AND E1.EFFDT <= D.TERM_BEGIN_DT AND E1.EFF_STATUS = 'A') AND B.EMPLID = F.EMPLID AND B.ANALYSIS_DB_SEQ = F.ANALYSIS_DB_SEQ AND B.SAA_CAREER_RPT = F.SAA_CAREER_RPT AND B.ACAD_PROG = F.ACAD_PROG AND B.ACAD_PLAN = F.ACAD_PLAN AND B.ACAD_SUB_PLAN = F.ACAD_SUB_PLAN AND F.ENTRY_R_TYPE = 'KEYSUB'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 ANALYSIS_DB_SEQ Number(4,0) SMALLINT NOT NULL Analysis DB Sequence
3 SAA_CAREER_RPT Character(4) VARCHAR2(4) NOT NULL This field is used in academic advisement as a key to the results tables.
4 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program
5 ACAD_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Plan
6 ACAD_SUB_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Sub-Plan
7 SUB_PLAN_SEQUENCE Number(2,0) SMALLINT NOT NULL Sub-Plan Seq
8 SAA_SUBPLAN_DESCR Character(30) VARCHAR2(30) NOT NULL This field is used in the views for reporting to display the subplan description in the report.
9 ACAD_SUB_PLAN_TERM Character(4) VARCHAR2(4) NOT NULL Academic subplan term
10 SAA_TERM_DESCR Character(30) VARCHAR2(30) NOT NULL This field is used in the advising report views to display the short term description.
11 ACAD_SUBPLAN_TYPE Character(3) VARCHAR2(3) NOT NULL Academic Sub-Plan Type
CON=Concentration
EMP=Emphasis
MIN=Minor
OPT=Option
SPC=Specialization
TRK=Track
12 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
13 SAA_DESCR80 Character(80) VARCHAR2(80) NOT NULL This field is used to display information in the new student view for academic advisement reports.
14 ITEM_R_STATUS Character(4) VARCHAR2(4) NOT NULL Item Status
COMP=Complete
EXMP=Exempted
FAIL=Fail
15 SAA_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL This field is used in advising report views for displaying the description of the status.