EMPLIDINSTITUTIONPrompt Table: INSTITUTION_TBL
SSR_ACT_ID_ROOTLANGUAGE_CDSSR_IAM_ACRTVLN(SQL View) |
Index Back |
|---|---|
Rel Lang for SSR_IAM_ACROTVWRel Lang for SSR_IAM_ACROTVW |
| SELECT DISTINCT A.EMPLID , E.INSTITUTION , E.SSR_ACTIVITY_ID , G.LANGUAGE_CD , G.DESCR FROM PS_STDNT_ENRL A , PS_CLASS_TBL B , PS_SSR_ACT_XREF C , PS_SSR_ACTIVITY D , PS_SSR_ACT_ROOTEXT E , PS_SSR_AC_REG_HDR F , PS_CLASS_TBL_LANG L ,PS_CRSE_CATLG_LNG G , PS_CRSE_OFFER H , PS_TERM_TBL I , PS_CRSE_CATALOG J WHERE A.GRADING_BASIS_ENRL <> 'NON' AND B.STRM = A.STRM AND B.CLASS_NBR = A.CLASS_NBR AND C.CRSE_ID = B.CRSE_ID AND C.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR AND C.STRM = B.STRM AND C.SESSION_CODE = B.SESSION_CODE AND C.ASSOCIATED_CLASS = B.ASSOCIATED_CLASS AND C.CLASS_SECTION = B.CLASS_SECTION AND ((D.SSR_ACTIVTY_ID_LNK = C.SSR_ACTIVITY_ID AND E.SSR_ACTIVITY_ID = D.SSR_ACTIVTY_ID_PAR) OR (D.SSR_ACTIVITY_ID = C.SSR_ACTIVITY_ID AND E.SSR_ACTIVITY_ID = D.SSR_ACTIVITY_ID)) AND F.SSR_AC_CONTENT_ID = D.SSR_AC_CONTENT_ID AND F.INSTITUTION = D.INSTITUTION AND F.EFFDT = E.SSR_AC_EFFDT AND L.CRSE_ID = B.CRSE_ID AND L.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR AND L.STRM = B.STRM AND L.SESSION_CODE = B.SESSION_CODE AND L.CLASS_SECTION = B.CLASS_SECTION 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 G.CRSE_ID = B.CRSE_ID AND B.CRSE_ID = H.CRSE_ID AND H.CRSE_OFFER_NBR = B.CRSE_OFFER_NBR AND G.EFFDT = H.EFFDT AND G.CRSE_ID = J.CRSE_ID AND G.EFFDT = J.EFFDT AND A.INSTITUTION = I.INSTITUTION AND A.ACAD_CAREER = I.ACAD_CAREER AND A.STRM = I.STRM AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_CRSE_CATALOG J1 WHERE J.CRSE_ID = J1.CRSE_ID AND J1.EFFDT <= I.TERM_BEGIN_DT) UNION SELECT DISTINCT A.EMPLID , D.INSTITUTION , E.SSR_ACTIVITY_ID , L.LANGUAGE_CD , B.DESCR FROM PS_STDNT_CAREER A , PS_CRSE_CATALOG B , PS_CRSE_OFFER BO , PS_SSR_ACT_XREF C , PS_SSR_ACTIVITY D , PS_SSR_ACT_ROOTEXT E , PS_SSR_AC_REG_HDR F , PS_CRSE_CATLG_LNG L , PS_SSR_PERIOD_TBL G WHERE BO.CRSE_ID = B.CRSE_ID AND C.CRSE_ID = BO.CRSE_ID AND C.CRSE_OFFER_NBR = BO.CRSE_OFFER_NBR AND C.SSR_AC_EXAM_ONLY = 'Y' AND ((D.SSR_ACTIVTY_ID_LNK = C.SSR_ACTIVITY_ID AND E.SSR_ACTIVITY_ID = D.SSR_ACTIVTY_ID_PAR) OR (D.SSR_ACTIVITY_ID = C.SSR_ACTIVITY_ID AND E.SSR_ACTIVITY_ID = D.SSR_ACTIVITY_ID)) AND F.SSR_AC_CONTENT_ID = D.SSR_AC_CONTENT_ID AND F.INSTITUTION = D.INSTITUTION AND F.EFFDT = E.SSR_AC_EFFDT AND L.CRSE_ID = B.CRSE_ID AND L.EFFDT = B.EFFDT 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 = D.INSTITUTION) AND D.INSTITUTION = G.INSTITUTION AND G.SSR_PERIOD_ID = C.SSR_PERIOD_ID AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_CRSE_CATALOG B1 WHERE B.CRSE_ID = B1.CRSE_ID AND B1.EFFDT <= G.SSR_PRD_START_DT) AND B.EFFDT = BO.EFFDT |
| # | 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
Prompt Table: INSTITUTION_TBL |
| 3 | SSR_ACT_ID_ROOT |
Character(11) | VARCHAR2(11) NOT NULL | Course Root Activity ID |
| 4 | LANGUAGE_CD |
Character(3) | VARCHAR2(3) NOT NULL | Language Code |
| 5 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |