SSR_IAM_RSLTVW2

(SQL View)
 Index Back

IAM Result View 2

IAM Result View 2

SELECT DISTINCT A.INSTITUTION , A.EMPLID , A.SSR_ACTIVITY_ID , A.SSR_ACT_ID_SEQ_NBR , A.SSR_RSLT_TYPE , A.SSR_RSLT_SEQ , A.SSR_IAM_RSLT_STAT , B.SSR_ACT_ID_ROOT , C.SSR_AC_CONTENT_TP , ' ' , D.SSR_PERIOD_ID , D.SSR_AC_EXAM_ONLY , G.ACAD_CAREER , ' ' , E.SSR_PRD_START_DT , G.ACAD_ORG , ' ' , G.CRSE_ID , G.CRSE_OFFER_NBR , G.SUBJECT , G.CATALOG_NBR , 0 , I.SSR_APT_INSTANCE , 0 , F.DESCR FROM PS_SSR_IAM_RESULT A , PS_SSR_IAM B , PS_SSR_IAM_EXMONLY BX , PS_SSR_ACTIVITY C , PS_SSR_ACT_XREF D , PS_SSR_PERIOD_TBL E , PS_CRSE_CATALOG F , PS_CRSE_OFFER G , PS_SSR_PERIOD_TBL H , PS_ACAD_PROG I WHERE A.SSR_INCL_IN_CALC = 'Y' AND A.SSR_RSLT_SEQ = ( SELECT MAX(A2.SSR_RSLT_SEQ) FROM PS_SSR_IAM_RESULT A2 WHERE A2.EMPLID = A.EMPLID AND A2.INSTITUTION = A.INSTITUTION AND A2.SSR_ACTIVITY_ID = A.SSR_ACTIVITY_ID AND A2.SSR_RSLT_TYPE = A.SSR_RSLT_TYPE) AND A.SSR_RSLT_SEQ NOT IN ( SELECT P.SSR_RSLT_SEQ FROM PS_SSR_IAM_RSLTPST P WHERE P.EMPLID = A.EMPLID AND P.INSTITUTION = A.INSTITUTION AND P.SSR_ACTIVITY_ID = A.SSR_ACTIVITY_ID AND P.SSR_ACT_ID_SEQ_NBR = A.SSR_ACT_ID_SEQ_NBR AND P.SSR_RSLT_SEQ = A.SSR_RSLT_SEQ AND P.SSR_IAM_RSLTPST_ST = 'P') AND B.EMPLID = A.EMPLID AND B.INSTITUTION = A.INSTITUTION AND B.SSR_ACTIVITY_ID = A.SSR_ACTIVITY_ID AND B.SSR_ACT_ID_SEQ_NBR = A.SSR_ACT_ID_SEQ_NBR AND BX.EMPLID = B.EMPLID AND BX.INSTITUTION = B.INSTITUTION AND BX.SSR_ACTIVITY_ID = B.SSR_ACT_ID_ROOT AND C.INSTITUTION = A.INSTITUTION AND C.SSR_ACTIVITY_ID = A.SSR_ACTIVITY_ID AND D.SSR_ACTIVITY_ID = B.SSR_ACT_ID_ROOT AND D.SSR_AC_CONTENT_TP = '1000' AND D.SSR_AC_EXAM_ONLY = 'Y' AND E.INSTITUTION = A.INSTITUTION AND E.SSR_PERIOD_ID = D.SSR_PERIOD_ID AND F.CRSE_ID = D.CRSE_ID AND F.EFFDT = ( SELECT MAX(F2.EFFDT) FROM PS_CRSE_CATALOG F2 WHERE F2.CRSE_ID = F.CRSE_ID AND F2.EFFDT <= E.SSR_PRD_START_DT) AND F.EFF_STATUS = 'A' AND G.CRSE_ID = D.CRSE_ID AND G.EFFDT = F.EFFDT AND G.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND H.INSTITUTION = A.INSTITUTION AND H.SSR_PERIOD_ID = D.SSR_PERIOD_ID AND I.EMPLID = A.EMPLID AND I.ACAD_CAREER = G.ACAD_CAREER AND I.ACAD_PROG = BX.ACAD_PROG AND I.EFFDT = ( SELECT MAX(I2.EFFDT) FROM PS_ACAD_PROG I2 WHERE I2.EMPLID = I.EMPLID AND I2.ACAD_CAREER = I.ACAD_CAREER AND I2.STDNT_CAR_NBR = I.STDNT_CAR_NBR AND I2.EFFDT <= H.SSR_PRD_START_DT) AND I.EFFSEQ = ( SELECT MAX(I3.EFFSEQ) FROM PS_ACAD_PROG I3 WHERE I3.EMPLID = I.EMPLID AND I3.ACAD_CAREER = I.ACAD_CAREER AND I3.STDNT_CAR_NBR = I.STDNT_CAR_NBR AND I3.EFFDT = I.EFFDT) AND (I.SSR_APT_INSTANCE <> 0) AND (C.SSR_AC_CONTENT_TP IN ( SELECT M.SSR_AC_CONTENT_TP FROM PS_SSR_AC_RSLTMAP M , PS_SSR_RSLT_ITMTYP N WHERE M.SSR_RSLT_TYPE = A.SSR_RSLT_TYPE AND M.SSR_AC_CONTX_TYPE = C.SSR_AC_CONTX_TYPE AND M.SSR_AC_CONTENT_TP = C.SSR_AC_CONTENT_TP AND M.EFFDT = ( SELECT MAX(M2.EFFDT) FROM PS_SSR_AC_RSLTMAP M2 WHERE M2.SSR_RSLT_TYPE = M.SSR_RSLT_TYPE AND M2.SSR_AC_CONTX_TYPE = M.SSR_AC_CONTX_TYPE AND M2.SSR_AC_CONTENT_TP = M.SSR_AC_CONTENT_TP AND M2.EFFDT <= H.SSR_PRD_START_DT) AND N.SSR_RSLT_TYPE = A.SSR_RSLT_TYPE AND N.SSR_ITEM_TYPE = M.SSR_ITEM_TYPE AND N.SSR_RSLT_PEAM = 'Y' AND N.EFFDT = ( SELECT MAX(N2.EFFDT) FROM PS_SSR_RSLT_ITMTYP N2 WHERE N2.SSR_RSLT_TYPE = N.SSR_RSLT_TYPE AND N2.SSR_ITEM_TYPE = N.SSR_ITEM_TYPE AND N2.EFFDT <= H.SSR_PRD_START_DT)))

  • Related Language Record: SSR_IAM_RTVW2LN

    • # 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 SSR_ACTIVITY_ID Character(11) VARCHAR2(11) NOT NULL Academic Activity ID
    4 SSR_ACT_ID_SEQ_NBR Number(4,0) SMALLINT NOT NULL Activity ID Sequence Number
    5 SSR_RSLT_TYPE Character(20) VARCHAR2(20) NOT NULL Result Type
    6 SSR_RSLT_SEQ Number(3,0) SMALLINT NOT NULL Result Sequence Nbr
    7 SSR_IAM_RSLT_STAT Character(2) VARCHAR2(2) NOT NULL Result Status
    00=00-Undetermined
    05=05-Manual
    07=07-Imported
    10=10-Calculated
    20=20-Awaiting Approval
    30=30-Approved
    40=40-Overridden
    50=50-Final
    8 SSR_ACT_ID_ROOT Character(11) VARCHAR2(11) NOT NULL Course Root Activity ID
    9 SSR_AC_CONTENT_TP Character(20) VARCHAR2(20) NOT NULL Academic Content Type
    10 SSR_COMPONENT Character(3) VARCHAR2(3) NOT NULL Course Component.
    CLN=Clinical
    CON=Continuance
    DIS=Discussion
    FLD=Field Studies
    IND=Independent Study
    LAB=Laboratory
    LEC=Lecture
    PRA=Practicum
    RSC=Research
    SEM=Seminar
    SUP=Supervision
    THE=Thesis Research
    TUT=Tutorial
    11 SSR_PERIOD_ID Character(12) VARCHAR2(12) NOT NULL Academic Period ID
    12 SSR_AC_EXAM_ONLY Character(1) VARCHAR2(1) NOT NULL Exam Only Course
    13 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
    14 STRM Character(4) VARCHAR2(4) NOT NULL Term
    15 SSR_EFFDT Date(10) DATE Effective Date
    16 ACAD_ORG Character(10) VARCHAR2(10) NOT NULL Academic Organization
    17 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
    18 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
    19 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
    20 SUBJECT Character(8) VARCHAR2(8) NOT NULL Subject Area
    21 CATALOG_NBR Character(10) VARCHAR2(10) NOT NULL Catalog Nbr
    22 CLASS_NBR Number(5,0) INTEGER NOT NULL Class Nbr
    23 SSR_APT_INSTANCE Number(3,0) SMALLINT NOT NULL Academic Progress Tracker Instance Number
    24 SSR_APT_ITEM_SEQ Number(6,0) INTEGER NOT NULL APT Item Seq
    25 DESCR Character(30) VARCHAR2(30) NOT NULL Description