SSR_RI_OPAV_NLD

(SQL View)
 Index Back

Current View (off. Progr attr)

Current View (off. Progr. attributes) /* 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 */ /* 2021-10-06: fix for Bug-ID: 32929597 - RIOv3 */

SELECT A.INSTITUTION , B.SSR_RIO_PRG_UUID , 'eigenAangebodenOpleidingSleutel' AS SSR_RI_AT_NAME_NLD ,' ' ,C.ACAD_PROG || '-' || C.CAMPUS || '-' || C.ACAD_LOAD_APPR || '-' || B.ACAD_ORG AS SSR_RI_AT_TVAL_NLD ,' ' ,0 ,%DateNull , ' ' ,0 ,C.ACAD_PROG || '-' || C.CAMPUS || '-' || C.ACAD_LOAD_APPR || '-' || B.ACAD_ORG AS SSR_BR_FIELDVL_NLD ,C.ACAD_PROG || '-' || C.CAMPUS || '-' || C.ACAD_LOAD_APPR || '-' || B.ACAD_ORG AS SSR_BR_XMLVL_NLD ,'01' FROM PS_SSR_RI_EPS_NLD A , PS_SSR_RIO_PRG_NLD B , PS_SCC_CMPLOAD_NLD C WHERE A.INSTITUTION = B.INSTITUTION AND B.INSTITUTION = C.INSTITUTION AND B.ACAD_PROG = C.ACAD_PROG AND B.CAMPUS = C.CAMPUS AND B.ACAD_LOAD_APPR = C.ACAD_LOAD_APPR AND %EffdtCheck(SSR_RI_EPS_NLD A_ED, A, %CurrentDateIn) AND %EffdtCheck(SCC_CMPLOAD_NLD C_ED, C, %CurrentDateIn) UNION SELECT OPR.INSTITUTION ,OPR.SSR_RIO_PRG_UUID ,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_OPRV_NLD OPR , 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 = OPR.INSTITUTION AND APT.ACAD_PROG = OPR.ACAD_PROG AND %EffdtCheck(ACAD_PROG_TBL APT_ED,APT,%CurrentDateIn) AND OPCF.INSTITUTION = OPR.INSTITUTION AND %EffdtCheck(SSR_RI_OPCF_NLD OPCF1,OPCF,%CurrentDateIn) AND OPCF.RECNAME = 'ACAD_PROG_TBL' AND OPCF.SSR_RI_OPGRP_NLD = 'N' AND OPCF.SCC_CAF_ATTRIB_NM = APTCAF.SCC_CAF_ATTRIB_NM AND CAFATTR.SCC_CAF_ATTRIB_NM = APTCAF.SCC_CAF_ATTRIB_NM 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_AT_NAME_NLD Character(50) VARCHAR2(50) NOT NULL Common Attribute
4 SSR_RI_AT_VAL_NLD Character(20) VARCHAR2(20) NOT NULL Attribute Value
5 SSR_RI_AT_TVAL_NLD Character(50) VARCHAR2(50) NOT NULL Text Value
6 SSR_RI_AT_BVAL_NLD Character(1) VARCHAR2(1) NOT NULL Yes/No Value
7 SSR_RI_AT_NVAL_NLD Number(32,11) DECIMAL(31,11) NOT NULL Numeric Value
8 SSR_RI_AT_DVAL_NLD Date(10) DATE Date Value
9 SSR_RI_AT_LVAL_NLD Long Character CLOB Text Value
10 SEQNBR Number(5,0) INTEGER NOT NULL Sequence Number
11 SSR_BR_FIELDVL_NLD Character(254) VARCHAR2(254) NOT NULL /* 2021-10-13: fix for Bug-ID: 32929597 - RIOv3 */
12 SSR_BR_XMLVL_NLD Long Character CLOB /* 2021-10-13: fix for Bug-ID: 32929597 - RIOv3 */ Converted to long.
13 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