SSR_RI_OPPV_NLD

(SQL View)
 Index Back

Current view (off. Progr per)

Current view (off. Progr. period) /* 2020-07-23: fix for Bug-ID: 31865490 - RIOv2 */ /* 2021-01-07: Fix for Bug 32187682 - ENH: RIO/ BRON issue for delivery of changed educ provider on offered prog/enrol */ /* 2022-10-13: fix for Bug-ID: 34695718 - RIOv4 */ Added SSR_RI_DSC3000_NLD

SELECT A.INSTITUTION , B.SSR_RIO_PRG_UUID , C.EFFDT , D.SSR_DESC200_NLD ,CASE WHEN E.LANGUAGE_CD = 'ENG' THEN E.SSR_DESC200_NLD END , D.SSR_DESC3000_NLD , B.ACAD_PROG FROM PS_SSR_RI_EPS_NLD A , PS_SSR_RIO_PRG_NLD B , PS_ACAD_PROG_TBL C , PS_SSR_PRG_NRM_NLD D LEFT OUTER JOIN PS_SSR_PRG_NRM_LNG E ON D.INSTITUTION = E.INSTITUTION AND D.ACAD_PROG = E.ACAD_PROG AND D.EFFDT = E.EFFDT AND E.LANGUAGE_CD = 'ENG' WHERE A.INSTITUTION = B.INSTITUTION AND B.ACAD_PROG = C.ACAD_PROG AND B.INSTITUTION = C.INSTITUTION AND D.ACAD_PROG = C.ACAD_PROG AND D.INSTITUTION = C.INSTITUTION AND D.EFFDT = C.EFFDT AND %EffdtCheck(SSR_RI_EPS_NLD A_ED, A, %CURRENTDATEIN) AND C.EFFDT >= (%Sql(SSR_RI_OPR_BEGIN_DT_NLD, B.INSTITUTION, B.ACAD_PROG, B.CAMPUS, B.ACAD_LOAD_APPR,B.ACAD_ORG)) AND C.EFFDT <= ( SELECT F.EFFDT FROM PS_SCC_CMPLOAD_NLD F , PS_SCC_HM_CAMP_NLD G WHERE F.INSTITUTION = B.INSTITUTION AND F.ACAD_PROG = B.ACAD_PROG AND F.CAMPUS = B.CAMPUS AND F.ACAD_LOAD_APPR= B.ACAD_LOAD_APPR AND F.INSTITUTION = G.INSTITUTION AND F.ACAD_PROG = G.ACAD_PROG AND F.CAMPUS = G.CAMPUS AND F.EFFDT = G.EFFDT AND G.ACAD_ORG = B.ACAD_ORG AND F.EFFDT = ( SELECT MAX(F2.EFFDT) FROM PS_SCC_CMPLOAD_NLD F2 , PS_SCC_HM_CAMP_NLD G2 WHERE F2.INSTITUTION = B.INSTITUTION AND F2.ACAD_PROG = B.ACAD_PROG AND F2.CAMPUS = B.CAMPUS AND F2.ACAD_LOAD_APPR= B.ACAD_LOAD_APPR AND F2.EFFDT <= %CurrentDateIn AND G2.INSTITUTION = F2.INSTITUTION AND G2.ACAD_PROG = F2.ACAD_PROG AND G2.CAMPUS = F2.CAMPUS AND G2.EFFDT = F2.EFFDT AND G2.ACAD_ORG = B.ACAD_ORG)) AND ( C.EFFDT NOT IN ( SELECT C_IED.EFFDT FROM PS_ACAD_PROG_TBL C_IED WHERE C.INSTITUTION = C_IED.INSTITUTION AND C.ACAD_PROG = C_IED.ACAD_PROG AND C_IED.EFFDT = ( SELECT MAX(C_IED1.EFFDT) FROM PS_ACAD_PROG_TBL C_IED1 WHERE C_IED1.INSTITUTION = C_IED.INSTITUTION AND C_IED1.ACAD_PROG = C_IED.ACAD_PROG AND C_IED1.EFFDT > ( SELECT MAX(C_EDX.EFFDT) FROM PS_ACAD_PROG_TBL C_EDX WHERE C_EDX.INSTITUTION = C.INSTITUTION AND C_EDX.ACAD_PROG = C.ACAD_PROG AND C_EDX.EFF_STATUS = 'A' AND C_EDX.EFFDT <= %CurrentDateIn ) AND C_IED1.EFF_STATUS = 'I' AND C_IED1.EFFDT <= %CurrentDateIn) ) )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
2 SSR_RIO_PRG_UUID Character(50) VARCHAR2(50) NOT NULL Program UUID
3 SSR_RI_OPR_ST_DT Date(10) DATE NOT NULL Begin Date
4 SSR_RI_DSC225_NLD Character(225) VARCHAR2(225) NOT NULL Description
5 SSR_RI_DSC225E_NLD Character(225) VARCHAR2(225) NOT NULL Description Language(ENG)
6 SSR_RI_DSC3000_NLD Long Character(3000) CLOB /* 2022-10-13: fix for Bug-ID: 34695718 - RIOv4 */
7 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program