SSR_APT_DEST_VW

(SQL View)
 Index Back

SELECT A.EMPLID , A.INSTITUTION , A.SSR_APT_INSTANCE , A.SSR_APT_ITEM_SEQ , A.ATTEMPT_NBR , ' ' AS STRM , ' ' AS SSR_ATT_PRGRS_LVL , ' ' AS CRSE_GRADE_OFF , ' ' AS SSR_ATT_OUTCOME , ' ' AS SSR_INCL_IN_CALC , B.SSR_APT_ITEM_PAR , B.SSR_ITEM_TYPE , B.SSR_ITEM_ID , B.SSR_GRADE_ITEM_FL , B.SSR_ENRL_CAT , A.CRSE_ID , A.CRSE_OFFER_NBR , B.SSR_APT_ITEM_FOR , B.SSR_SUBSTITUTED_FL , G.SSR_APT_TRANS_STAT , G.SSR_APT_TRANS_BY , G.SSR_APT_TRANS_ON , G.SSR_APT_INS_SRC , G.SSR_ITEM_ID_SRC FROM PS_SSR_APT_ATT A LEFT OUTER JOIN PS_SSR_APT_TRN_LOG G ON A.EMPLID = G.EMPLID AND A.INSTITUTION = G.INSTITUTION AND A.SSR_APT_INSTANCE = G.SSR_APT_INSTANCE AND A.SSR_APT_ITEM_SEQ = G.SSR_APT_ITEM_SEQ AND A.ATTEMPT_NBR = G.ATTEMPT_NBR , PS_SSR_APT_ITEM B WHERE A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND A.SSR_APT_INSTANCE = B.SSR_APT_INSTANCE AND A.SSR_APT_ITEM_SEQ = B.SSR_APT_ITEM_SEQ AND A.CRSE_ID = ' ' UNION SELECT A.EMPLID , A.INSTITUTION , A.SSR_APT_INSTANCE , A.SSR_APT_ITEM_SEQ , A.ATTEMPT_NBR , C.STRM , C.SSR_ATT_PRGRS_LVL , D.CRSE_GRADE_OFF , A.SSR_ATT_OUTCOME , A.SSR_INCL_IN_CALC , B.SSR_APT_ITEM_PAR , B.SSR_ITEM_TYPE , B.SSR_ITEM_ID , B.SSR_GRADE_ITEM_FL , B.SSR_ENRL_CAT , A.CRSE_ID , A.CRSE_OFFER_NBR , B.SSR_APT_ITEM_FOR , B.SSR_SUBSTITUTED_FL , G.SSR_APT_TRANS_STAT , G.SSR_APT_TRANS_BY , G.SSR_APT_TRANS_ON , G.SSR_APT_INS_SRC , G.SSR_ITEM_ID_SRC FROM PS_SSR_APT_ATT A LEFT OUTER JOIN PS_SSR_APT_TRN_LOG G ON A.EMPLID = G.EMPLID AND A.INSTITUTION = G.INSTITUTION AND A.SSR_APT_INSTANCE = G.SSR_APT_INSTANCE AND A.SSR_APT_ITEM_SEQ = G.SSR_APT_ITEM_SEQ AND A.ATTEMPT_NBR = G.ATTEMPT_NBR , PS_SSR_APT_ITEM B , PS_SSR_APT_SCHD C , PS_STDNT_ENRL_SSVW D WHERE A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND A.SSR_APT_INSTANCE = B.SSR_APT_INSTANCE AND A.SSR_APT_ITEM_SEQ = B.SSR_APT_ITEM_SEQ AND A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.SSR_APT_INSTANCE = C.SSR_APT_INSTANCE AND A.ATTEMPT_NBR = C.ATTEMPT_NBR AND A.SSR_APT_ITEM_SEQ = C.SSR_APT_ITEM_SEQ AND A.CRSE_ID <> ' ' AND A.EMPLID = D.EMPLID AND A.INSTITUTION = D.INSTITUTION AND C.STRM = D.STRM AND A.CRSE_ID = D.CRSE_ID AND EXISTS ( SELECT 'X' FROM PS_SSR_APT_ENRL E WHERE A.EMPLID = E.EMPLID AND A.INSTITUTION = E.INSTITUTION AND A.SSR_APT_INSTANCE = E.SSR_APT_INSTANCE AND A.SSR_APT_ITEM_SEQ = E.SSR_APT_ITEM_SEQ AND A.ATTEMPT_NBR = E.ATTEMPT_NBR AND C.SSR_SEQ_NBR = E.SSR_SEQ_NBR AND D.STRM = E.SRC_TERM AND D.CLASS_NBR = E.CLASS_NBR ) UNION SELECT A.EMPLID , A.INSTITUTION , A.SSR_APT_INSTANCE , A.SSR_APT_ITEM_SEQ , A.ATTEMPT_NBR , C.STRM , C.SSR_ATT_PRGRS_LVL , ' ' , A.SSR_ATT_OUTCOME , A.SSR_INCL_IN_CALC , B.SSR_APT_ITEM_PAR , B.SSR_ITEM_TYPE , B.SSR_ITEM_ID , B.SSR_GRADE_ITEM_FL , B.SSR_ENRL_CAT , A.CRSE_ID , A.CRSE_OFFER_NBR , B.SSR_APT_ITEM_FOR , B.SSR_SUBSTITUTED_FL , G.SSR_APT_TRANS_STAT , G.SSR_APT_TRANS_BY , G.SSR_APT_TRANS_ON , G.SSR_APT_INS_SRC , G.SSR_ITEM_ID_SRC FROM PS_SSR_APT_ATT A LEFT OUTER JOIN PS_SSR_APT_TRN_LOG G ON A.EMPLID = G.EMPLID AND A.INSTITUTION = G.INSTITUTION AND A.SSR_APT_INSTANCE = G.SSR_APT_INSTANCE AND A.SSR_APT_ITEM_SEQ = G.SSR_APT_ITEM_SEQ AND A.ATTEMPT_NBR = G.ATTEMPT_NBR , PS_SSR_APT_ITEM B , PS_SSR_APT_SCHD C WHERE A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND A.SSR_APT_INSTANCE = B.SSR_APT_INSTANCE AND A.SSR_APT_ITEM_SEQ = B.SSR_APT_ITEM_SEQ AND A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.SSR_APT_INSTANCE = C.SSR_APT_INSTANCE AND A.ATTEMPT_NBR = C.ATTEMPT_NBR AND A.SSR_APT_ITEM_SEQ = C.SSR_APT_ITEM_SEQ AND A.CRSE_ID <> ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_SSR_APT_ENRL D WHERE A.EMPLID = D.EMPLID AND A.INSTITUTION = D.INSTITUTION AND A.SSR_APT_INSTANCE = D.SSR_APT_INSTANCE AND A.SSR_APT_ITEM_SEQ = D.SSR_APT_ITEM_SEQ AND A.ATTEMPT_NBR = D.ATTEMPT_NBR AND C.SSR_SEQ_NBR = D.SSR_SEQ_NBR ) UNION SELECT A.EMPLID , A.INSTITUTION , A.SSR_APT_INSTANCE , A.SSR_APT_ITEM_SEQ , A.ATTEMPT_NBR , ' ' AS STRM , ' ' AS SSR_ATT_PRGRS_LVL , ' ' AS CRSE_GRADE_OFF , ' ' AS SSR_ATT_OUTCOME , ' ' AS SSR_INCL_IN_CALC , B.SSR_APT_ITEM_PAR , B.SSR_ITEM_TYPE , B.SSR_ITEM_ID , B.SSR_GRADE_ITEM_FL , B.SSR_ENRL_CAT , A.CRSE_ID , A.CRSE_OFFER_NBR , B.SSR_APT_ITEM_FOR , B.SSR_SUBSTITUTED_FL , G.SSR_APT_TRANS_STAT , G.SSR_APT_TRANS_BY , G.SSR_APT_TRANS_ON , G.SSR_APT_INS_SRC , G.SSR_ITEM_ID_SRC FROM PS_SSR_APT_ATT A LEFT OUTER JOIN PS_SSR_APT_TRN_LOG G ON A.EMPLID = G.EMPLID AND A.INSTITUTION = G.INSTITUTION AND A.SSR_APT_INSTANCE = G.SSR_APT_INSTANCE AND A.SSR_APT_ITEM_SEQ = G.SSR_APT_ITEM_SEQ AND A.ATTEMPT_NBR = G.ATTEMPT_NBR , PS_SSR_APT_ITEM B , PS_CRSE_OFFER_TCVW F WHERE A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND A.SSR_APT_INSTANCE = B.SSR_APT_INSTANCE AND A.SSR_APT_ITEM_SEQ = B.SSR_APT_ITEM_SEQ AND NOT EXISTS ( SELECT 'X' FROM PS_SSR_APT_SCHD C WHERE A.EMPLID = C.EMPLID AND A.INSTITUTION = C.INSTITUTION AND A.SSR_APT_INSTANCE = C.SSR_APT_INSTANCE AND A.ATTEMPT_NBR = C.ATTEMPT_NBR AND A.SSR_APT_ITEM_SEQ = C.SSR_APT_ITEM_SEQ ) AND A.CRSE_ID <> ' ' UNION SELECT A.EMPLID , A.INSTITUTION , A.SSR_APT_INSTANCE , A.SSR_APT_ITEM_SEQ , 1 AS ATTEMPT_NBR , ' ' AS STRM , ' ' AS SSR_ATT_PRGRS_LVL , ' ' AS CRSE_GRADE_OFF , ' ' AS SSR_ATT_OUTCOME , ' ' AS SSR_INCL_IN_CALC , A.SSR_APT_ITEM_PAR , A.SSR_ITEM_TYPE , A.SSR_ITEM_ID , A.SSR_GRADE_ITEM_FL , A.SSR_ENRL_CAT , ' ' AS CRSE_ID , 0 AS CRSE_OFFER_NBR , A.SSR_APT_ITEM_FOR , A.SSR_SUBSTITUTED_FL , ' ' AS SSR_APT_TRANS_STAT , ' ' AS SSR_APT_TRANS_BY , %DateTimeNull AS SSR_APT_TRANS_ON , 0 AS SSR_APT_INS_SRC , ' ' AS SSR_ITEM_ID_SRC FROM PS_SSR_APT_ITEM A WHERE NOT EXISTS ( SELECT 'X' FROM PS_SSR_APT_ATT B WHERE B.EMPLID = A.EMPLID AND B.INSTITUTION = A.INSTITUTION AND B.SSR_APT_INSTANCE = A.SSR_APT_INSTANCE AND B.SSR_APT_ITEM_SEQ = A.SSR_APT_ITEM_SEQ )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
3 SSR_APT_INSTANCE Number(3,0) SMALLINT NOT NULL Academic Progress Tracker Instance Number
4 SSR_APT_ITEM_SEQ Number(6,0) INTEGER NOT NULL APT Item Seq
5 ATTEMPT_NBR Number(3,0) SMALLINT NOT NULL Attempt Nbr
6 STRM Character(4) VARCHAR2(4) NOT NULL Term
7 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
8 CRSE_GRADE_OFF Character(3) VARCHAR2(3) NOT NULL Official Grade
9 SSR_ATT_OUTCOME Character(4) VARCHAR2(4) NOT NULL Attempt Outcome
20=Not Passed
40=Passed
10 SSR_INCL_IN_CALC Character(1) VARCHAR2(1) NOT NULL Include in Calculation

Y/N Table Edit

11 SSR_APT_ITEM_PAR Number(6,0) INTEGER NOT NULL Parent Item Seq Nbr
12 SSR_ITEM_TYPE Character(20) VARCHAR2(20) NOT NULL Academic Item Type
13 SSR_ITEM_ID Character(11) VARCHAR2(11) NOT NULL Academic Item ID
14 SSR_GRADE_ITEM_FL Character(1) VARCHAR2(1) NOT NULL Grading Item Type

Y/N Table Edit

15 SSR_ENRL_CAT Character(4) VARCHAR2(4) NOT NULL Enrollment Category
16 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
17 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
18 SSR_APT_ITEM_FOR Number(6,0) INTEGER NOT NULL Substitute for
19 SSR_SUBSTITUTED_FL Character(1) VARCHAR2(1) NOT NULL Substituted
20 SSR_APT_TRANS_STAT Character(30) VARCHAR2(30) NOT NULL Transfer Status
21 SSR_APT_TRANS_BY Character(30) VARCHAR2(30) NOT NULL Transferred By
22 SSR_APT_TRANS_ON DateTime(26) TIMESTAMP Transferred On
23 SSR_APT_INS_SRC Number(3,0) SMALLINT NOT NULL APT Instance Source
24 SSR_ITEM_ID_SRC Character(11) VARCHAR2(11) NOT NULL Academic Item ID