SAA_RPT_PLAN_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.PLAN_SEQUENCE , C.DESCR , B.ACAD_PLAN_TERM , D.DESCR , C.ACAD_PLAN_TYPE , E.XLATLONGNAME , ' ' , F.ITEM_R_STATUS , ' ' FROM PS_SAA_ADB_REPORT A , PS_SAA_ADB_WHATIF B , PS_ACAD_PLAN_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_PLAN_TERM AND C.INSTITUTION = A.INSTITUTION AND C.ACAD_PLAN = B.ACAD_PLAN AND C.EFFDT = ( SELECT MAX (C1.EFFDT) FROM PS_ACAD_PLAN_TBL C1 WHERE C1.INSTITUTION = C.INSTITUTION AND C1.ACAD_PLAN = C.ACAD_PLAN AND C1.EFFDT <= D.TERM_BEGIN_DT AND C1.EFF_STATUS = 'A') AND E.FIELDNAME = 'ACAD_PLAN_TYPE' AND E.FIELDVALUE = C.ACAD_PLAN_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 F.ENTRY_R_TYPE = 'KEYPLN'

# 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 PLAN_SEQUENCE Number(2,0) SMALLINT NOT NULL Plan Sequence
7 SAA_PLAN_DESCR Character(30) VARCHAR2(30) NOT NULL This field is used in the views for reporting to display the plan description in the report.
8 ACAD_PLAN_TERM Character(4) VARCHAR2(4) NOT NULL Academic plan term
9 SAA_TERM_DESCR Character(30) VARCHAR2(30) NOT NULL This field is used in the advising report views to display the short term description.
10 ACAD_PLAN_TYPE Character(3) VARCHAR2(3) NOT NULL Academic Plan Type
CON=Concentration
COS=Course of Study
HON=Honors
MAJ=Major
MIN=Minor
PRP=Preparation
RTC=ROTC
SP=Specialization
11 XLATLONGNAME Character(30) VARCHAR2(30) NOT NULL Translate Long Name
12 SAA_DESCR80 Character(80) VARCHAR2(80) NOT NULL This field is used to display information in the new student view for academic advisement reports.
13 ITEM_R_STATUS Character(4) VARCHAR2(4) NOT NULL Item Status
COMP=Complete
EXMP=Exempted
FAIL=Fail
14 SAA_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL This field is used in advising report views for displaying the description of the status.