SSR_IAMAPTEOCVW

(SQL View)
 Index Back

Secured IAM APT/EOC View

IAM View with Acad Org Security

SELECT DISTINCT A.INSTITUTION , A.ACAD_CAREER , A.EMPLID , A.SSR_APT_INSTANCE , B.SSR_APT_ITEM_SEQ , C.ATTEMPT_NBR , D.SSR_SEQ_NBR , C.CRSE_ID , C.CRSE_OFFER_NBR , C.CRS_TOPIC_ID , S.OPRID , A.SSR_ITEM_ID , B.SSR_ENRL_CAT , C.SSR_ADVR_APPR_STAT , D.SSR_YR_OF_PROG , D.ACAD_YEAR , D.STRM , D.SESSION_CODE , D.SSR_ATT_PRGRS_LVL , E.SSR_ACTIVITY_ID , E.SSR_ACTIVITY_ID , E.SSR_PERIOD_ID , F.SSR_PRD_START_DT , F.SSR_PRD_END_DT , G.ACAD_ORG , G.SUBJECT , G.CATALOG_NBR , 0 , B.SSR_ITEM_ID FROM PS_SSR_APT_HDR A , PS_SSR_APT_ITEM B , PS_SSR_APT_ATT C , PS_SSR_APT_SCHD D , PS_SSR_ACT_XREF E , PS_SSR_ACT_ROOTEXT EE , PS_SSR_PERIOD_TBL F , PS_CRSE_OFFER G ,PS_SCRTY_TBL_ACAD S WHERE B.EMPLID = A.EMPLID AND B.INSTITUTION = A.INSTITUTION AND B.SSR_APT_INSTANCE = A.SSR_APT_INSTANCE AND C.EMPLID = A.EMPLID AND C.INSTITUTION = A.INSTITUTION AND C.SSR_APT_INSTANCE = A.SSR_APT_INSTANCE AND C.SSR_APT_ITEM_SEQ = B.SSR_APT_ITEM_SEQ AND D.EMPLID = A.EMPLID AND D.INSTITUTION = A.INSTITUTION AND D.SSR_APT_INSTANCE = A.SSR_APT_INSTANCE AND D.SSR_APT_ITEM_SEQ = B.SSR_APT_ITEM_SEQ AND D.ATTEMPT_NBR = C.ATTEMPT_NBR AND D.SSR_ATT_PRGRS_LVL <> '40' AND D.SSR_ATT_PRGRS_LVL <> '60' AND E.SSR_AC_EXAM_ONLY = 'Y' AND E.SSR_AC_CONTENT_TP = '1000' AND E.CRSE_ID = C.CRSE_ID AND E.CRS_TOPIC_ID = C.CRS_TOPIC_ID AND ((C.CRSE_OFFER_NBR > 0 AND E.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR) OR (C.CRSE_OFFER_NBR = 0 AND E.CRSE_OFFER_NBR = ( SELECT MIN(E2.CRSE_OFFER_NBR) FROM PS_SSR_ACT_XREF E2 WHERE E2.CRSE_ID = E.CRSE_ID AND E2.SSR_AC_CONTENT_TP = '1000' AND E2.SSR_ACTIVITY_ID = E.SSR_ACTIVITY_ID))) AND E.SSR_ACTIVITY_ID NOT IN ( SELECT X.SSR_ACTIVITY_ID FROM PS_SSR_IAM_ROOTEXT X WHERE X.EMPLID = A.EMPLID AND X.INSTITUTION = A.INSTITUTION) AND EE.SSR_ACTIVITY_ID = E.SSR_ACTIVITY_ID AND EE.INSTITUTION = A.INSTITUTION AND EE.SSR_ACT_STATUS = 'C' AND F.INSTITUTION = A.INSTITUTION AND F.SSR_PERIOD_ID = E.SSR_PERIOD_ID AND G.CRSE_ID = C.CRSE_ID AND ((C.CRSE_OFFER_NBR > 0 AND G.CRSE_OFFER_NBR = C.CRSE_OFFER_NBR) OR (C.CRSE_OFFER_NBR = 0 AND G.CRSE_OFFER_NBR = ( SELECT MIN(G2.CRSE_OFFER_NBR) FROM PS_CRSE_OFFER G2 WHERE G2.CRSE_ID = G.CRSE_ID))) AND G.EFFDT = ( SELECT MAX(G3.EFFDT) FROM PS_CRSE_OFFER G3 WHERE G3.CRSE_ID = G.CRSE_ID AND G3.CRSE_OFFER_NBR = G.CRSE_OFFER_NBR AND G3.EFFDT <= F.SSR_PRD_START_DT) AND S.ACCESS_CD='Y' AND EXISTS ( SELECT 'X' FROM PSTREENODE T WHERE T.SETID=' ' AND T.TREE_NAME='ACAD_ORGANIZATION' AND T.EFFDT=S.TREE_EFFDT AND T.TREE_NODE=G.ACAD_ORG AND T.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END AND NOT EXISTS ( SELECT 'X' FROM PS_SCRTY_TBL_ACAD S2 WHERE S.OPRID=S2.OPRID AND S.TREE_NODE_NUM<>S2.TREE_NODE_NUM AND T.TREE_NODE_NUM BETWEEN S2.TREE_NODE_NUM AND S2.TREE_NODE_NUM_END AND S2.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Prompt Table: INSTITUTION_TBL

2 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
BAC=Bachelor (NLD)
BBL=Vocational Coaching (NLD)
BOL=Vocational Training (NLD)
BUSN=Graduate Business
CNED=Continuing Education
CRED=Semester Credit
EDU=Education (NLD)
EXED=Extended Education
GRAD=Graduate
LAW=Law
MEDS=Medical School
NONA=Non Award
PGRD=Postgraduate
RSCH=Research
TECH=Technical
UENG=Undergraduate Engineering
UGRD=Undergraduate
VAVO=Advanced General Educ. (NLD)
VETM=Veterinary Medicine
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 SSR_APT_INSTANCE Number(3,0) SMALLINT NOT NULL Academic Progress Tracker Instance Number
5 SSR_APT_ITEM_SEQ Number(6,0) INTEGER NOT NULL APT Item Seq
6 ATTEMPT_NBR Number(3,0) SMALLINT NOT NULL Attempt Nbr
7 SSR_SEQ_NBR Number(4,0) SMALLINT NOT NULL Sequence Number
8 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
9 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
10 CRS_TOPIC_ID Number(3,0) SMALLINT NOT NULL Course Topic ID
11 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
12 SSR_ITEM_ID Character(11) VARCHAR2(11) NOT NULL Academic Item ID
13 SSR_ENRL_CAT Character(4) VARCHAR2(4) NOT NULL Enrollment Category

Prompt Table: SSR_ENRL_CAT

14 SSR_YR_OF_PROG Character(4) VARCHAR2(4) NOT NULL Year of Program
00=Not Set
01=01 - First Year
02=02 - Second Year
03=03 - Third Year
04=04 - Fourth Year
05=05 - Fifth Year
06=06 - Sixth Year
15 SSR_ADVR_APPR_STAT Character(4) VARCHAR2(4) NOT NULL Advisor Approval Status
20=Not Required
40=Pending
60=Approved
80=Denied
16 ACAD_YEAR Character(4) VARCHAR2(4) NOT NULL Academic Year
17 STRM Character(4) VARCHAR2(4) NOT NULL Term
18 SESSION_CODE Character(3) VARCHAR2(3) NOT NULL Session
1=Regular Academic Session
10W=10 Week
12W=Twelve Week
4W1=Four Week - First
4W2=Four Week - Second
4W3=Four Week - Third
4W4=Four Week - Fourth
6W1=Six Week - First
6W2=Six Week - Second
8W1=Eight Week - First
8W2=Eight Week - Second
A11=Medical Period 1, 4 Weeks
A12=Medical Period 1, 8 Weeks
A13=Medical Pd 1, 12 Weeks
A21=Medical Period 2, 4 Weeks
A22=Medical Period 2, 8 Weeks
A23=Medical Period 2, 12 Week
FYR=Full Year
MIN=Mini Session
OEE=Open Entry/Open Exit
SM1=Semester 1
SM2=Semester 2
19 SSR_ATT_PRGRS_LVL Character(2) VARCHAR2(2) NOT NULL Progress Level
00=00-Newly Added
20=20-Planned
40=40-In Progress
60=60-Completed
80=80-Dropped
20 SSR_ACTIVITY_ID Character(11) VARCHAR2(11) NOT NULL Class Activity ID

Prompt Table: SSR_ACT_ROOTEXT

21 SSR_ACT_ID_ROOT Character(11) VARCHAR2(11) NOT NULL Course Root Activity ID
22 SSR_PERIOD_ID Character(12) VARCHAR2(12) NOT NULL Academic Period ID

Prompt Table: SSR_PERIOD_TBL

23 SSR_PRD_START_DT Date(10) DATE Campus Period Start Date
24 SSR_PRD_END_DT Date(10) DATE Campus Period End Date
25 ACAD_ORG Character(10) VARCHAR2(10) NOT NULL Academic Organization
26 SUBJECT Character(8) VARCHAR2(8) NOT NULL Subject Area
27 CATALOG_NBR Character(10) VARCHAR2(10) NOT NULL Catalog Nbr
28 CLASS_NBR Number(5,0) INTEGER NOT NULL Class Nbr
29 SSR_ITEM_ID_2 Character(11) VARCHAR2(11) NOT NULL Academic Item ID