AV_STF_PRPS_VW

(SQL View)
 Index Back

AV Staff Purpose View

This view contains all current purpose codes for the selected staff member

SELECT B.INSTITUTION , B.STAFF_ID , B.PURPOSE_CD , E.EFFDT , E.EFF_STATUS , E.DESCR , E.DESCRSHORT FROM PS_AV_STAFF A , PS_AV_STF_PRPS_TBL B , PS_AV_PRPS_TBL E WHERE A.INSTITUTION = B.INSTITUTION AND A.STAFF_ID = B.STAFF_ID AND A.STAFF_TYPE = B.STAFF_TYPE AND A.EFFDT = B.EFFDT AND B.STAFF_TYPE = 'STF' AND B.PURPOSE_CD = E.PURPOSE_CD AND E.PURPOSE_TYPE='ASG' AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_AV_STF_PRPS_TBL D WHERE D.EFFDT <= %CurrentDateIn AND B.INSTITUTION = D.INSTITUTION AND B.STAFF_ID = D.STAFF_ID AND B.STAFF_TYPE = D.STAFF_TYPE )

  • Related Language Record: AVSTFPRPSVWLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
    2 STAFF_ID Character(11) VARCHAR2(11) NOT NULL Staff ID
    3 PURPOSE_CD Character(3) VARCHAR2(3) NOT NULL Role
    4 EFFDT Date(10) DATE Effective Date
    5 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description