SAA_ACD_PRG_VW(SQL View) |
Index Back |
|---|---|
Academic Progress |
| SELECT A.EMPLID , A.ANALYSIS_DB_SEQ , A.SAA_CAREER_RPT , A.TSCRPT_TYPE , 'KEYRQG' , A.RQRMNT_GROUP , A.REQUIREMENT , A.RQ_LINE_NBR , A.RQRMNT_GROUP , B.SAA_DESCR80 , A.SAA_ENTRY_SEQ , A.ITEM_R_STATUS , A.REQ_LINE_TYPE , A.SAA_DISPLAY_UNITS , A.UNITS_REQUIRED , A.SAA_UNITS_USED , A.UNITS_NEEDED , A.SAA_DISPLAY_CRSCNT , A.CRSES_REQUIRED , A.SAA_CRSES_USED , A.CRSES_NEEDED , A.SAA_DISPLAY_GPA , A.GPA_REQUIRED , A.GPA_ACTUAL , 1 , CASE WHEN A.ITEM_R_STATUS <> 'COMP' AND A.UNITS_REQUIRED > 0 THEN 'Y' WHEN A.ITEM_R_STATUS = 'COMP' AND A.SAA_DISPLAY_UNITS = 'Y' THEN 'Y' ELSE 'N' END , CASE WHEN A.ITEM_R_STATUS <> 'COMP' AND A.CRSES_REQUIRED > 0 THEN 'Y' WHEN A.ITEM_R_STATUS = 'COMP' AND A.SAA_DISPLAY_CRSCNT = 'Y' THEN 'Y' ELSE 'N' END , CASE WHEN A.ITEM_R_STATUS <> 'COMP' AND A.GPA_REQUIRED > 0 THEN 'Y' WHEN A.ITEM_R_STATUS = 'COMP' AND A.SAA_DISPLAY_GPA = 'Y' THEN 'Y' ELSE 'N' END , A.SAA_HIDE_STATUS FROM PS_SAA_ARSLT_RGVW A , PS_RQ_GRP_TBL B WHERE A.RQRMNT_GROUP = B.RQRMNT_GROUP AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_RQ_GRP_TBL B1 WHERE B1.RQRMNT_GROUP = B.RQRMNT_GROUP AND B1.EFFDT <= %CurrentDateIn) AND B.EFF_STATUS = 'A' UNION ALL SELECT A.EMPLID , A.ANALYSIS_DB_SEQ , A.SAA_CAREER_RPT , A.TSCRPT_TYPE , 'KEYREQ' , A.RQRMNT_GROUP , A.REQUIREMENT , A.RQ_LINE_NBR , A.REQUIREMENT , B.SAA_DESCR80 , A.SAA_ENTRY_SEQ , C.ITEM_R_STATUS , C.REQ_LINE_TYPE , C.SAA_DISPLAY_UNITS , C.UNITS_REQUIRED , C.SAA_UNITS_USED , C.UNITS_NEEDED , C.SAA_DISPLAY_CRSCNT , C.CRSES_REQUIRED , C.SAA_CRSES_USED , C.CRSES_NEEDED , C.SAA_DISPLAY_GPA , C.GPA_REQUIRED , C.GPA_ACTUAL , 2 , CASE WHEN C.ITEM_R_STATUS <> 'COMP' AND C.UNITS_REQUIRED > 0 THEN 'Y' WHEN C.ITEM_R_STATUS = 'COMP' AND C.SAA_DISPLAY_UNITS = 'Y' THEN 'Y' ELSE 'N' END , CASE WHEN C.ITEM_R_STATUS <> 'COMP' AND C.CRSES_REQUIRED > 0 THEN 'Y' WHEN C.ITEM_R_STATUS = 'COMP' AND C.SAA_DISPLAY_CRSCNT = 'Y' THEN 'Y' ELSE 'N' END , CASE WHEN C.ITEM_R_STATUS <> 'COMP' AND C.GPA_REQUIRED > 0 THEN 'Y' WHEN C.ITEM_R_STATUS = 'COMP' AND C.SAA_DISPLAY_GPA = 'Y' THEN 'Y' ELSE 'N' END , B.SAA_HIDE_STATUS FROM PS_SAA_ARSLT_RLVW A , PS_RQ_MAIN_TBL B , PS_SAA_ARSLT_RQVW C WHERE A.SAA_ENTRY_SEQ = ( SELECT MIN(A1.SAA_ENTRY_SEQ) FROM PS_SAA_ARSLT_RLVW A1 WHERE A1.EMPLID = A.EMPLID AND A1.ANALYSIS_DB_SEQ = A.ANALYSIS_DB_SEQ AND A1.SAA_CAREER_RPT = A.SAA_CAREER_RPT AND A1.RQRMNT_GROUP = A.RQRMNT_GROUP AND A1.REQUIREMENT = A.REQUIREMENT) AND A.REQUIREMENT = B.REQUIREMENT AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_RQ_MAIN_TBL B1 WHERE B1.REQUIREMENT = B.REQUIREMENT AND B1.EFFDT <= %CurrentDateIn) AND B.EFF_STATUS = 'A' AND B.RQ_PRINT_CNTL NOT IN ('NVR','SKP') AND C.EMPLID = A.EMPLID AND C.ANALYSIS_DB_SEQ = A.ANALYSIS_DB_SEQ AND C.SAA_CAREER_RPT = A.SAA_CAREER_RPT AND C.RQRMNT_GROUP = A.RQRMNT_GROUP AND C.REQUIREMENT = A.REQUIREMENT UNION ALL SELECT A.EMPLID , A.ANALYSIS_DB_SEQ , A.SAA_CAREER_RPT , A.TSCRPT_TYPE , 'KEYRQL' , A.RQRMNT_GROUP , A.REQUIREMENT , A.RQ_LINE_NBR , A.RQ_LINE_NBR , C.SAA_DESCR80 , A.SAA_ENTRY_SEQ , A.ITEM_R_STATUS , A.REQ_LINE_TYPE , A.SAA_DISPLAY_UNITS , A.UNITS_REQUIRED , A.SAA_UNITS_USED , A.UNITS_NEEDED , A.SAA_DISPLAY_CRSCNT , A.CRSES_REQUIRED , A.SAA_CRSES_USED , A.CRSES_NEEDED , A.SAA_DISPLAY_GPA , A.GPA_REQUIRED , A.GPA_ACTUAL , 3 , CASE WHEN A.ITEM_R_STATUS <> 'COMP' AND A.UNITS_REQUIRED > 0 THEN 'Y' WHEN A.ITEM_R_STATUS = 'COMP' AND A.SAA_DISPLAY_UNITS = 'Y' THEN 'Y' ELSE 'N' END , CASE WHEN A.ITEM_R_STATUS <> 'COMP' AND A.CRSES_REQUIRED > 0 THEN 'Y' WHEN A.ITEM_R_STATUS = 'COMP' AND A.SAA_DISPLAY_CRSCNT = 'Y' THEN 'Y' ELSE 'N' END , CASE WHEN A.ITEM_R_STATUS <> 'COMP' AND A.GPA_REQUIRED > 0 THEN 'Y' WHEN A.ITEM_R_STATUS = 'COMP' AND A.SAA_DISPLAY_GPA = 'Y' THEN 'Y' ELSE 'N' END , A.SAA_HIDE_STATUS FROM PS_SAA_ARSLT_RLVW A , PS_RQ_MAIN_TBL B , PS_RQ_LINE_TBL C WHERE A.REQUIREMENT = B.REQUIREMENT AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_RQ_MAIN_TBL B1 WHERE B1.REQUIREMENT = B.REQUIREMENT AND B1.EFFDT <= %CurrentDateIn) AND B.EFF_STATUS = 'A' AND C.REQUIREMENT = A.REQUIREMENT AND C.RQ_LINE_NBR = A.RQ_LINE_NBR AND B.EFFDT = C.EFFDT |
| # | 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 | TSCRPT_TYPE |
Character(5) | VARCHAR2(5) NOT NULL | Transcript Type |
| 5 | ENTRY_R_TYPE |
Character(6) | VARCHAR2(6) NOT NULL | Entry Type |
| 6 | RQRMNT_GROUP |
Character(6) | VARCHAR2(6) NOT NULL | Requirement Group |
| 7 | REQUIREMENT |
Character(9) | VARCHAR2(9) NOT NULL | Academic Requirement |
| 8 | RQ_LINE_NBR |
Character(4) | VARCHAR2(4) NOT NULL | Line Nbr |
| 9 | SAA_RQG_REQ_RQL |
Character(9) | VARCHAR2(9) NOT NULL | Academic Requirements |
| 10 | SAA_DESCR80 | Character(80) | VARCHAR2(80) NOT NULL | This field is used to display information in the new student view for academic advisement reports. |
| 11 | SAA_ENTRY_SEQ |
Number(4,0) | SMALLINT NOT NULL | This field is used in Academic Advisement to track the position of the output in the report. |
| 12 | ITEM_R_STATUS |
Character(4) | VARCHAR2(4) NOT NULL |
Item Status
COMP=Complete EXMP=Exempted FAIL=Fail |
| 13 | REQ_LINE_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Line Type
CND=Condition Requirement GLM=Global Limit GSQ=Global Sequential Restriction LIM=Unit, Course or GPA Limit MT=Course Requirement SC=Specified Courses SEQ=Sequential Restriction |
| 14 | SAA_DISPLAY_UNITS | Character(1) | VARCHAR2(1) NOT NULL | This field is used to track if the calculated units are to be displayed on an Academic Advisement report if the processing level is satisfied. |
| 15 | UNITS_REQUIRED | Number(7,2) | DECIMAL(6,2) NOT NULL | Units required |
| 16 | SAA_UNITS_USED | Number(7,2) | DECIMAL(6,2) NOT NULL | This field is used in Academic Advisement to report the number of units used by a requirement. |
| 17 | UNITS_NEEDED | Number(7,2) | DECIMAL(6,2) NOT NULL | Units needed |
| 18 | SAA_DISPLAY_CRSCNT | Character(1) | VARCHAR2(1) NOT NULL | This field is used to track if the calculated course counts are to be displayed on an Academic Advisement report if the processing level is satisfied. |
| 19 | CRSES_REQUIRED | Number(7,2) | DECIMAL(6,2) NOT NULL | Courses required |
| 20 | SAA_CRSES_USED | Number(7,2) | DECIMAL(6,2) NOT NULL | This field is used in Academic Advisement to report the number of courses used by a particular requirement. |
| 21 | CRSES_NEEDED | Number(7,2) | DECIMAL(6,2) NOT NULL | Courses needed |
| 22 | SAA_DISPLAY_GPA | Character(1) | VARCHAR2(1) NOT NULL | This field is used to track if the GPA is to be displayed on an Academic Advisement report if the processing level is satisfied. |
| 23 | GPA_REQUIRED | Number(7,3) | DECIMAL(6,3) NOT NULL | Minimum GPA |
| 24 | GPA_ACTUAL | Number(7,3) | DECIMAL(6,3) NOT NULL | GPA actual |
| 25 | ENTRY_SEQ | Number(3,0) | SMALLINT NOT NULL | Entry Sequence |
| 26 | UNIT_FLAG | Character(1) | VARCHAR2(1) NOT NULL | Unit |
| 27 | CRSE_ATTR_FLAG | Character(1) | VARCHAR2(1) NOT NULL | Course Attribute Flag |
| 28 | GPAR_CU_FLG | Character(1) | VARCHAR2(1) NOT NULL | Accomplished Flag |
| 29 | SAA_HIDE_STATUS | Character(1) | VARCHAR2(1) NOT NULL | This field is used to determine if the status description is to print on AA reports for the processing level. |