SSR_IAM_APT_BND(SQL View) |
Index Back |
|---|---|
IAM APT/EOC Bind TableThis record is the pop select bind record for rhe queries used for the IAM Batch Generator |
| 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 , 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_PERIOD_ID , F.SSR_PRD_START_DT , F.SSR_PRD_END_DT , G.ACAD_ORG , G.SUBJECT , G.CATALOG_NBR , 0 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_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 F.INSTITUTION = A.INSTITUTION AND F.SSR_PERIOD_ID = E.SSR_PERIOD_ID /* AND F.SSR_PRD_START_DT >= %CurrentDateIn */ 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 F.SSR_PRD_START_DT >= %CurrentDateIn */) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | INSTITUTION | Character(5) | VARCHAR2(5) NOT NULL | Academic Institution |
| 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 | SSR_ENRL_CAT | Character(4) | VARCHAR2(4) NOT NULL |
Enrollment Category
Prompt Table: SSR_ENRL_CAT |
| 12 | SSR_ADVR_APPR_STAT | Character(4) | VARCHAR2(4) NOT NULL |
Advisor Approval Status
20=Not Required 40=Pending 60=Approved 80=Denied |
| 13 | 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 |
| 14 | ACAD_YEAR | Character(4) | VARCHAR2(4) NOT NULL | Academic Year |
| 15 | STRM | Character(4) | VARCHAR2(4) NOT NULL | Term |
| 16 | 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 |
| 17 | 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 |
| 18 | SSR_ACTIVITY_ID | Character(11) | VARCHAR2(11) NOT NULL |
Course Root
Prompt Table: SSR_ACT_ROOTEXT |
| 19 | SSR_PERIOD_ID | Character(12) | VARCHAR2(12) NOT NULL |
Academic Period ID
Prompt Table: SSR_PERIOD_TBL |
| 20 | SSR_PRD_START_DT | Date(10) | DATE | Campus Period Start Date |
| 21 | SSR_PRD_END_DT | Date(10) | DATE | Campus Period End Date |
| 22 | ACAD_ORG | Character(10) | VARCHAR2(10) NOT NULL | Academic Organization |
| 23 | SUBJECT | Character(8) | VARCHAR2(8) NOT NULL | Subject Area |
| 24 | CATALOG_NBR | Character(10) | VARCHAR2(10) NOT NULL | Catalog Nbr |
| 25 | CLASS_NBR | Number(5,0) | INTEGER NOT NULL | Class Nbr |