SSR_ASC_CAF_VL1

(SQL View)
 Index Back

Rel Lang for SSR_ASC_CAF_VW1


SELECT D.CRSE_ID , D.CRSE_OFFER_NBR , D.STRM , D.SESSION_CODE , F.CLASS_SECTION , D.ASSOCIATED_CLASS , D.SCC_CAF_ATTR_SEQ , E.LANGUAGE_CD , E.DESCRFORMAL FROM PS_SSR_FCT_CAF_ATT A , PS_SCC_CAF_RECATTR B , PS_SCC_CAF_ATTRIBS C , PS_SSR_ASSOC_CAF D , PS_SSR_CSCAFLOV_LN E , PS_SSR_CLS_TBL_ES F , PS_TERM_TBL G WHERE A.SCC_CAF_ATTR_SEQ = 1 AND B.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND B.EFF_STATUS = 'A' AND C.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND C.SCC_CAF_ATTR_TYPE = 'LO' AND D.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND E.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND E.SCC_CAF_ATTR_VAL = D.SCC_CAF_ATTR_VAL AND F.CRSE_ID = D.CRSE_ID AND F.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND F.STRM = D.STRM AND F.SESSION_CODE = D.SESSION_CODE AND F.ASSOCIATED_CLASS = D.ASSOCIATED_CLASS AND F.CLASS_SECTION = ( SELECT MIN(F2.CLASS_SECTION) FROM PS_CLASS_TBL F2 WHERE F2.CRSE_ID = F.CRSE_ID AND F2.CRSE_OFFER_NBR = F.CRSE_OFFER_NBR AND F2.STRM = F.STRM AND F2.SESSION_CODE = F.SESSION_CODE AND F2.ASSOCIATED_CLASS = F.ASSOCIATED_CLASS) AND G.INSTITUTION = F.INSTITUTION AND G.ACAD_CAREER = F.ACAD_CAREER AND G.STRM = D.STRM AND A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_SSR_FCT_CAF_ATT A2 WHERE A2.SCC_CAF_ATTR_SEQ = A.SCC_CAF_ATTR_SEQ AND A2.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND A2.EFFDT <= G.TERM_BEGIN_DT) UNION SELECT D.CRSE_ID , D.CRSE_OFFER_NBR , D.STRM , D.SESSION_CODE , F.CLASS_SECTION , D.ASSOCIATED_CLASS , D.SCC_CAF_ATTR_SEQ , A.LANGUAGE_CD , CASE WHEN C.SCC_CAF_ATTR_TYPE = '04' THEN %NumToChar(D.SCC_CAF_ATTR_NVAL) WHEN C.SCC_CAF_ATTR_TYPE = '05' THEN %NumToChar(CAST (%Dateout(D.SCC_CAF_ATTR_DVAL) AS CHAR(10))) WHEN C.SCC_CAF_ATTR_TYPE = '06' THEN %NumToChar(CAST (%Time(D.SCC_CAF_ATTR_TIME) AS TIME)) ELSE D.SCC_CAF_ATTR_VAL END AS SCC_CAF_ATTR_VAL FROM PS_SSR_FCTCAFATTLN A , PS_SCC_CAF_RECATTR B , PS_SCC_CAF_ATTRIBS C , PS_SSR_ASSOC_CAF D , PS_SSR_CLS_TBL_ES F , PS_TERM_TBL G WHERE A.SCC_CAF_ATTR_SEQ = 1 AND B.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND B.EFF_STATUS = 'A' AND C.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND C.SCC_CAF_ATTR_TYPE <> '07' AND C.SCC_CAF_ATTR_TYPE <> 'LO' AND D.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND F.CRSE_ID = D.CRSE_ID AND F.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND F.STRM = D.STRM AND F.SESSION_CODE = D.SESSION_CODE AND F.ASSOCIATED_CLASS = D.ASSOCIATED_CLASS AND F.CLASS_SECTION = ( SELECT MIN(F2.CLASS_SECTION) FROM PS_CLASS_TBL F2 WHERE F2.CRSE_ID = F.CRSE_ID AND F2.CRSE_OFFER_NBR = F.CRSE_OFFER_NBR AND F2.STRM = F.STRM AND F2.SESSION_CODE = F.SESSION_CODE AND F2.ASSOCIATED_CLASS = F.ASSOCIATED_CLASS) AND G.INSTITUTION = F.INSTITUTION AND G.ACAD_CAREER = F.ACAD_CAREER AND G.STRM = D.STRM AND A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_SSR_FCT_CAF_ATT A2 WHERE A2.SCC_CAF_ATTR_SEQ = A.SCC_CAF_ATTR_SEQ AND A2.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND A2.EFFDT <= G.TERM_BEGIN_DT) UNION SELECT D.CRSE_ID , D.CRSE_OFFER_NBR , D.STRM , D.SESSION_CODE , F.CLASS_SECTION , D.ASSOCIATED_CLASS , D.SCC_CAF_ATTR_SEQ , E.LANGUAGE_CD , CASE WHEN A.SSR_DESCR <> ' ' THEN A.SSR_DESCR || ' (' || E.XLATSHORTNAME || ')' ELSE E.XLATSHORTNAME END AS XLATSHORTNAME FROM PS_SSR_FCT_CAF_ATT A , PS_SCC_CAF_RECATTR B , PS_SCC_CAF_ATTRIBS C , PS_SSR_ASSOC_CAF D , PSXLATITEMLANG E , PS_SSR_CLS_TBL_ES F , PS_TERM_TBL G WHERE A.SCC_CAF_ATTR_SEQ = 1 AND B.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND B.EFF_STATUS = 'A' AND C.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND C.SCC_CAF_ATTR_TYPE = '07' AND D.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND E.FIELDNAME = 'SSR_CAF_YNO_XLAT' AND E.FIELDVALUE = D.SCC_CAF_ATTR_YNO AND E.EFFDT = ( SELECT MAX(E2.EFFDT) FROM PSXLATITEMLANG E2 WHERE E2.FIELDNAME = E.FIELDNAME AND E2.FIELDVALUE = E.FIELDVALUE AND E2.LANGUAGE_CD = E.LANGUAGE_CD AND E2.EFFDT <= %CurrentDateIn) AND F.CRSE_ID = D.CRSE_ID AND F.CRSE_OFFER_NBR = D.CRSE_OFFER_NBR AND F.STRM = D.STRM AND F.SESSION_CODE = D.SESSION_CODE AND F.ASSOCIATED_CLASS = D.ASSOCIATED_CLASS AND F.CLASS_SECTION = ( SELECT MIN(F2.CLASS_SECTION) FROM PS_CLASS_TBL F2 WHERE F2.CRSE_ID = F.CRSE_ID AND F2.CRSE_OFFER_NBR = F.CRSE_OFFER_NBR AND F2.STRM = F.STRM AND F2.SESSION_CODE = F.SESSION_CODE AND F2.ASSOCIATED_CLASS = F.ASSOCIATED_CLASS) AND G.INSTITUTION = F.INSTITUTION AND G.ACAD_CAREER = F.ACAD_CAREER AND G.STRM = D.STRM AND A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_SSR_FCT_CAF_ATT A2 WHERE A2.SCC_CAF_ATTR_SEQ = A.SCC_CAF_ATTR_SEQ AND A2.SCC_CAF_ATTRIB_NM = A.SCC_CAF_ATTRIB_NM AND A2.EFFDT <= G.TERM_BEGIN_DT)

  • Related Language Record for SSR_ASC_CAF_VW1

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
    2 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
    3 STRM Character(4) VARCHAR2(4) NOT NULL Term
    4 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
    5 CLASS_SECTION Character(4) VARCHAR2(4) NOT NULL Class Section
    6 ASSOCIATED_CLASS Number(4,0) SMALLINT NOT NULL Associated Class
    7 SCC_CAF_ATTR_SEQ Number(4,0) SMALLINT NOT NULL Attribute Sequence
    8 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    9 SSR_CAF_DESCR1 Character(30) VARCHAR2(30) NOT NULL CAF Option 1