SSR_RI_OPQV_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-10-06: fix for Bug-ID: 32929597 - RIOv3 */

SELECT DISTINCT OPP.INSTITUTION ,OPP.SSR_RIO_PRG_UUID ,OPP.SSR_RI_OPR_ST_DT ,OPCF.SSR_RI_AT_NAME_NLD ,APTCAF.SCC_CAF_ATTR_VAL ,APTCAF.SCC_CAF_ATTR_TVAL ,APTCAF.SCC_CAF_ATTR_YNO ,APTCAF.SCC_CAF_ATTR_NVAL ,APTCAF.SCC_CAF_ATTR_DVAL ,%Cast(%Substring(APTCAF.SCC_CAF_ATTR_LVAL,1,1000),Long,Character) ,OPCF.SEQNBR ,CASE WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '01' THEN APTCAF.SCC_CAF_ATTR_VAL WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '02' THEN APTCAF.SCC_CAF_ATTR_TVAL WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '03' THEN %Cast(%Substring(APTCAF.SCC_CAF_ATTR_LVAL,1,1000),Long,Character) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '04' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_NVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '05' THEN (CAST (%DateOut(APTCAF.SCC_CAF_ATTR_DVAL) AS CHAR(10))) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '07' THEN APTCAF.SCC_CAF_ATTR_YNO ELSE APTCAF.SCC_CAF_ATTR_VAL END AS SSR_BR_FIELDVL_NLD , CASE WHEN XMLV.SSR_BR_XMLVL_NLD IS NULL THEN CASE WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '01' THEN APTCAF.SCC_CAF_ATTR_VAL WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '02' THEN APTCAF.SCC_CAF_ATTR_TVAL WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '03' THEN %Cast(%Substring(APTCAF.SCC_CAF_ATTR_LVAL,1,1000),Long,Character) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '04' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_NVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '05' THEN (CAST (%DateOut(APTCAF.SCC_CAF_ATTR_DVAL) AS CHAR(10))) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '07' THEN APTCAF.SCC_CAF_ATTR_YNO ELSE APTCAF.SCC_CAF_ATTR_VAL END ELSE %Cast(%Substring(XMLV.SSR_BR_XMLVL_NLD,1,1000),Long,Character) END AS SSR_BR_XMLVL_NLD ,OPCF.SSR_RI_ATRTYPE_NLD FROM PS_SSR_RI_OPPV_NLD OPP , PS_SSR_RI_OPCF_NLD OPCF LEFT OUTER JOIN PS_SSR_RI_XMLV_NLD XMLV ON XMLV.INSTITUTION = OPCF.INSTITUTION AND XMLV.EFFDT = OPCF.EFFDT AND XMLV.RECNAME = OPCF.RECNAME AND XMLV.SCC_CAF_ATTRIB_NM = OPCF.SCC_CAF_ATTRIB_NM ,PS_SCC_CAF_ATTRIBS CAFATTR ,PS_ACAD_PROG_TBL APT LEFT OUTER JOIN PS_ACAD_PROG_CAF APTCAF ON APT.INSTITUTION = APTCAF.INSTITUTION AND APT.ACAD_PROG = APTCAF.ACAD_PROG AND APT.EFFDT = APTCAF.EFFDT WHERE APT.INSTITUTION = OPP.INSTITUTION AND APT.ACAD_PROG = OPP.ACAD_PROG AND OPCF.INSTITUTION = OPP.INSTITUTION AND %EffdtCheck(SSR_RI_OPCF_NLD OPCF1,OPCF,%CurrentDateIn) AND OPCF.RECNAME = 'ACAD_PROG_TBL' AND OPCF.SSR_RI_OPGRP_NLD = 'P' AND OPCF.SCC_CAF_ATTRIB_NM = APTCAF.SCC_CAF_ATTRIB_NM AND CAFATTR.SCC_CAF_ATTRIB_NM = APTCAF.SCC_CAF_ATTRIB_NM AND (APTCAF.EFFDT = OPP.SSR_RI_OPR_ST_DT OR APTCAF.EFFDT IS NULL) AND (XMLV.SSR_BR_FIELDVL_NLD = CASE WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '01' THEN APTCAF.SCC_CAF_ATTR_VAL WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '02' THEN APTCAF.SCC_CAF_ATTR_TVAL WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '03' THEN %Cast(%Substring(APTCAF.SCC_CAF_ATTR_LVAL,1,1000),Long,Character) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '04' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_NVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '05' THEN (CAST (%DateOut(APTCAF.SCC_CAF_ATTR_DVAL) AS CHAR(10))) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '07' THEN APTCAF.SCC_CAF_ATTR_YNO WHEN CAFATTR.SCC_CAF_ATTR_TYPE = 'LO' THEN APTCAF.SCC_CAF_ATTR_VAL END OR XMLV.EFFDT IS NULL )

# 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_AT_NAME_NLD Character(50) VARCHAR2(50) NOT NULL Common Attribute
5 SSR_RI_AT_VAL_NLD Character(20) VARCHAR2(20) NOT NULL Attribute Value
6 SSR_RI_AT_TVAL_NLD Character(50) VARCHAR2(50) NOT NULL Text Value
7 SSR_RI_AT_BVAL_NLD Character(1) VARCHAR2(1) NOT NULL Yes/No Value
8 SSR_RI_AT_NVAL_NLD Number(32,11) DECIMAL(31,11) NOT NULL Numeric Value
9 SSR_RI_AT_DVAL_NLD Date(10) DATE Date Value
10 SSR_RI_AT_LVAL_NLD Long Character CLOB Text Value
11 SEQNBR Number(5,0) INTEGER NOT NULL Sequence Number
12 SSR_BR_FIELDVL_NLD Character(254) VARCHAR2(254) NOT NULL /* 2021-10-13: fix for Bug-ID: 32929597 - RIOv3 */
13 SSR_BR_XMLVL_NLD Long Character CLOB /* 2021-10-13: fix for Bug-ID: 32929597 - RIOv3 */ Converted to long.
14 SSR_RI_ATRTYPE_NLD Character(2) VARCHAR2(2) NOT NULL RIO Attribute Type /* 2021-10-13: fix for Bug-ID: 32929597 - RIOv3 */ Additional translate value 06.
01=Text
02=Enumeration
03=Date
04=Number
05=Boolean
06=Long text