SSR_IAM_RTVW2LN

(SQL View)
 Index Back

Rel Lang for SSR_IAM_RSLTVW2

Rel Lang for SSR_IAM_RSLTVW2

SELECT DISTINCT A.INSTITUTION , A.EMPLID , A.SSR_ACTIVITY_ID , A.SSR_ACT_ID_SEQ_NBR , A.SSR_RSLT_TYPE , A.SSR_RSLT_SEQ , FL.LANGUAGE_CD , FL.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_CATLG_LNG FL , 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 FL.CRSE_ID = F.CRSE_ID AND FL.EFFDT = F.EFFDT 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 for SSR_IAM_RSLTVW2

    • # 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description