SSR_BR_EPVW_ATR

(SQL View)
 Index Back

Enrolment Period Attributes

Enrolment Period Attributes View /* 2021-10-06: fix for Bug-ID: 32929597 - RIOv3 */

SELECT DISTINCT ES.INSTITUTION , ES.SCC_BRINCD_NLD , ES.EMPLID , ES.SSR_BR_ENRL_SQ_NLD , AP.SSR_BR_ENR_ST_DT , EPCF.RECNAME , CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN PROGCAF.SCC_CAF_ATTRIB_NM ELSE CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN APTCAF.SCC_CAF_ATTRIB_NM END END , EPCF.SEQNBR , CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN PROGCAF.SCC_CAF_ATTR_VAL ELSE CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN APTCAF.SCC_CAF_ATTR_VAL END END , CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN PROGCAF.SCC_CAF_ATTR_NVAL ELSE CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN APTCAF.SCC_CAF_ATTR_NVAL END END , CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN PROGCAF.SCC_CAF_ATTR_DVAL ELSE CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN APTCAF.SCC_CAF_ATTR_DVAL END END , CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN PROGCAF.SCC_CAF_ATTR_TVAL ELSE CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN APTCAF.SCC_CAF_ATTR_TVAL END END , CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN PROGCAF.SCC_CAF_ATTR_TIME ELSE CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN APTCAF.SCC_CAF_ATTR_TIME END END , CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN PROGCAF.SCC_CAF_ATTR_YNO ELSE CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN APTCAF.SCC_CAF_ATTR_YNO END END , CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_LVAL) ELSE CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_LVAL) END END , CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN CASE WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '01' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_VAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '02' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_TVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '03' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_LVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '04' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_NVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '05' THEN %NumToChar(CAST (%Date(PROGCAF.SCC_CAF_ATTR_DVAL) AS DATE)) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '06' THEN %NumToChar(CAST (%Time(PROGCAF.SCC_CAF_ATTR_TIME) AS TIME)) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '07' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_YNO) ELSE PROGCAF.SCC_CAF_ATTR_VAL END ELSE CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN CASE WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '01' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_VAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '02' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_TVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '03' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_LVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '04' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_NVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '05' THEN %NumToChar(CAST (%Date(APTCAF.SCC_CAF_ATTR_DVAL) AS DATE)) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '06' THEN %NumToChar(CAST (%Time(APTCAF.SCC_CAF_ATTR_TIME) AS TIME)) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '07' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_YNO) ELSE APTCAF.SCC_CAF_ATTR_VAL END END END , EPCF.SSR_BR_XMLTAG_NLD , CASE WHEN XMLV.SSR_BR_XMLVL_NLD IS NULL THEN CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN CASE WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '01' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_VAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '02' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_TVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '03' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_LVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '04' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_NVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '05' THEN TO_CHAR(TO_DATE(PROGCAF.SCC_CAF_ATTR_DVAL) ,'YYYY-MM-DD') WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '06' THEN %NumToChar(CAST (%Time(PROGCAF.SCC_CAF_ATTR_TIME) AS TIME)) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '07' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_YNO) ELSE PROGCAF.SCC_CAF_ATTR_VAL END ELSE CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN CASE WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '01' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_VAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '02' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_TVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '03' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_LVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '04' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_NVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '05' THEN TO_CHAR(TO_DATE(APTCAF.SCC_CAF_ATTR_DVAL) ,'YYYY-MM-DD') WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '06' THEN %NumToChar(CAST (%Time(APTCAF.SCC_CAF_ATTR_TIME) AS TIME)) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '07' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_YNO) ELSE APTCAF.SCC_CAF_ATTR_VAL END END END ELSE %NumToChar(XMLV.SSR_BR_XMLVL_NLD) END , ES.ACAD_CAREER , ES.STDNT_CAR_NBR FROM PS_SSR_BR_ENRVWNLD ES JOIN PS_SSR_BR_EPVW2NLD AP ON ES.INSTITUTION = AP.INSTITUTION AND ES.SCC_BRINCD_NLD = AP.SCC_BRINCD_NLD AND ES.EMPLID = AP.EMPLID AND ES.ACAD_CAREER = AP.ACAD_CAREER AND ES.STDNT_CAR_NBR = AP.STDNT_CAR_NBR , PS_SSR_BR_EPCF_NLD EPCF LEFT OUTER JOIN PS_SSR_BR_XMLV_NLD XMLV ON XMLV.INSTITUTION = EPCF.INSTITUTION AND XMLV.SCC_BRINCD_NLD = EPCF.SCC_BRINCD_NLD AND XMLV.EFFDT = EPCF.EFFDT AND XMLV.RECNAME = EPCF.RECNAME AND XMLV.SCC_CAF_ATTRIB_NM = EPCF.SCC_CAF_ATTRIB_NM , PS_SCC_CAF_ATTRIBS CAFATTR , PS_ACAD_PROG AP2 LEFT OUTER JOIN PS_SSR_PROG_CAF PROGCAF ON PROGCAF.EMPLID = AP2.EMPLID AND PROGCAF.ACAD_CAREER = AP2.ACAD_CAREER AND PROGCAF.STDNT_CAR_NBR = AP2.STDNT_CAR_NBR AND PROGCAF.EFFDT = AP2.EFFDT AND PROGCAF.EFFSEQ = AP2.EFFSEQ ,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 AP2.EMPLID = AP.EMPLID AND AP2.ACAD_CAREER = AP.ACAD_CAREER AND AP2.STDNT_CAR_NBR = AP.STDNT_CAR_NBR AND AP2.EFFDT = AP.SSR_BR_ENR_ST_DT AND AP2.EFFSEQ = AP.EFFSEQ AND APT.INSTITUTION = AP.INSTITUTION AND APT.ACAD_PROG = AP.ACAD_PROG AND %EffdtCheck(ACAD_PROG_TBL APT_ED,APT,AP.SSR_BR_ENR_ST_DT) AND EPCF.INSTITUTION = ES.INSTITUTION AND EPCF.SCC_BRINCD_NLD = ES.SCC_BRINCD_NLD AND %EffdtCheck(SSR_BR_EPCF_NLD EPCF1,EPCF,AP.SSR_BR_ENR_ST_DT) AND EPCF.RECNAME IN ('ACAD_PROG','ACAD_PROG_TBL') AND (EPCF.SCC_CAF_ATTRIB_NM = CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN PROGCAF.SCC_CAF_ATTRIB_NM END OR EPCF.SCC_CAF_ATTRIB_NM = CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN APTCAF.SCC_CAF_ATTRIB_NM END) AND (CAFATTR.SCC_CAF_ATTRIB_NM = CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN PROGCAF.SCC_CAF_ATTRIB_NM END OR CAFATTR.SCC_CAF_ATTRIB_NM = CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN APTCAF.SCC_CAF_ATTRIB_NM END) AND ((XMLV.SSR_BR_FIELDVL_NLD = CASE WHEN EPCF.RECNAME = 'ACAD_PROG' THEN CASE WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '01' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_VAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '02' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_TVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '03' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_LVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '04' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_NVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '05' THEN %NumToChar(CAST (%Date(PROGCAF.SCC_CAF_ATTR_DVAL) AS DATE)) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '06' THEN %NumToChar(CAST (%Time(PROGCAF.SCC_CAF_ATTR_TIME) AS TIME)) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '07' THEN %NumToChar(PROGCAF.SCC_CAF_ATTR_YNO) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = 'LO' THEN PROGCAF.SCC_CAF_ATTR_VAL END END OR XMLV.SSR_BR_FIELDVL_NLD = CASE WHEN EPCF.RECNAME = 'ACAD_PROG_TBL' THEN CASE WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '01' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_VAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '02' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_TVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '03' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_LVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '04' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_NVAL) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '05' THEN %NumToChar(CAST (%Date(APTCAF.SCC_CAF_ATTR_DVAL) AS DATE)) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '06' THEN %NumToChar(CAST (%Time(APTCAF.SCC_CAF_ATTR_TIME) AS TIME)) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = '07' THEN %NumToChar(APTCAF.SCC_CAF_ATTR_YNO) WHEN CAFATTR.SCC_CAF_ATTR_TYPE = 'LO' THEN APTCAF.SCC_CAF_ATTR_VAL END 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 SCC_BRINCD_NLD Character(4) VARCHAR2(4) NOT NULL BRINcode
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 SSR_BR_ENRL_SQ_NLD Character(20) VARCHAR2(20) NOT NULL 2020-04-15 bug 31180485 - enlarged enrollment seq nbr
5 SSR_BR_ENR_ST_DT Date(10) DATE NOT NULL Start Date
6 RECNAME Character(15) VARCHAR2(15) NOT NULL Record (Table) Name (see PSRECDEFN).

Prompt Table: SSR_BR_RECNM_VW

7 SCC_CAF_ATTRIB_NM Character(20) VARCHAR2(20) NOT NULL Common Attribute

Prompt Table: SSR_BR_ATRNM_VW

8 SEQNBR Number(5,0) INTEGER NOT NULL Sequence Number
9 SCC_CAF_ATTR_VAL Character(20) VARCHAR2(20) NOT NULL Attribute Value
10 SCC_CAF_ATTR_NVAL Number(32,11) DECIMAL(31,11) NOT NULL Numeric Value
11 SCC_CAF_ATTR_DVAL Date(10) DATE Date Value
12 SCC_CAF_ATTR_TVAL Character(50) VARCHAR2(50) NOT NULL Text Value
13 SCC_CAF_ATTR_TIME Time(15) TIMESTAMP Time Value
14 SCC_CAF_ATTR_YNO Character(1) VARCHAR2(1) NOT NULL Yes/No Value

Y/N Table Edit

15 SCC_CAF_ATTR_LVAL Long Character CLOB Long Text Value
16 SSR_BR_FIELDVL_NLD Character(254) VARCHAR2(254) NOT NULL /* 2021-10-13: fix for Bug-ID: 32929597 - RIOv3 */
17 SSR_BR_XMLTAG_NLD Character(50) VARCHAR2(50) NOT NULL XML node
18 SSR_BR_XMLVL_NLD Long Character CLOB /* 2021-10-13: fix for Bug-ID: 32929597 - RIOv3 */ Converted to long.
19 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
BAC=Bachelor (NLD)
BBL=Vocational Coaching (NLD)
BOL=Vocational Training (NLD)
BUSN=Graduate Business
CNED=Continuing Education
CRED=Semester Credit
EDU=Education (NLD)
EXED=Extended Education
GRAD=Graduate
LAW=Law
MEDS=Medical School
NONA=Non Award
PGRD=Postgraduate
RSCH=Research
TECH=Technical
UENG=Undergraduate Engineering
UGRD=Undergraduate
VAVO=Advanced General Educ. (NLD)
VETM=Veterinary Medicine
20 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr