AV_CTYP_MIN_VW

(SQL View)
 Index Back

Person Constituent Type

Person Constituent Type This view will retrieve records that have and have not been past dated. The non-past dated rows will be retrieved first. If there are only past dated rows, then they will be displayed. Past dated rows will have the text 'Past' prepended to their descriptions.

SELECT A.INSTITUTION , A.EMPLID , A.CNST_TYPE_PAST_FLG , A.CNST_TYPE , B.DESCR FROM PS_AV_CNST_TYP_TBL A , PS_AV_CTYP_CD_TBL B WHERE A.CNST_TYPE = B.CNST_TYPE AND A.EMPLID <> ' ' AND A.CNST_TYPE IN ( SELECT MIN(C.CNST_TYPE) FROM PS_AV_CNST_TYP_TBL C WHERE A.INSTITUTION = C.INSTITUTION AND A.EMPLID = C.EMPLID AND C.CNST_TYPE_PAST_FLG <> 'Y') UNION ALL SELECT A.INSTITUTION , A.EMPLID , A.CNST_TYPE_PAST_FLG , A.CNST_TYPE , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR =14390 AND MESSAGE_NBR = 498) || ' ' || B.DESCR FROM PS_AV_CNST_TYP_TBL A , PS_AV_CTYP_CD_TBL B WHERE A.CNST_TYPE = B.CNST_TYPE AND A.EMPLID <> ' ' AND A.CNST_TYPE IN ( SELECT MIN(C.CNST_TYPE) FROM PS_AV_CNST_TYP_TBL C WHERE A.INSTITUTION = C.INSTITUTION AND A.EMPLID = C.EMPLID AND C.CNST_TYPE_PAST_FLG = 'Y')

  • Related Language Record: AVCTYPMINVWLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
    2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 CNST_TYPE_PAST_FLG Character(1) VARCHAR2(1) NOT NULL Past
    N=No
    Y=Yes
    4 CNST_TYPE Number(3,0) SMALLINT NOT NULL Constituent Type
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description