AV_ACTN_VOL_VW

(SQL View)
 Index Back

Action volunteer view

selects all volunteers that have been assigned to an action as either a solicitor or the responsible person.

SELECT A.INSTITUTION , B.EMPLID , B.NAME FROM PS_INSTITUTION_TBL A , PS_HCR_PERSON_NM_I B , PS_AV_STAFF C WHERE A.INSTITUTION = C.INSTITUTION AND B.EMPLID = C.STAFF_ID AND C.STAFF_TYPE = 'VOL' AND A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(INS.EFFDT) FROM PS_INSTITUTION_TBL INS WHERE INS.INSTITUTION = A.INSTITUTION AND INS.EFFDT <= %CurrentDateIn) AND C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(AV.EFFDT) FROM PS_AV_STAFF AV WHERE AV.INSTITUTION = C.INSTITUTION AND AV.STAFF_ID = C.STAFF_ID AND AV.STAFF_TYPE = C.STAFF_TYPE AND AV.EFFDT <= %CurrentDateIn) AND (B.EMPLID IN ( SELECT D.RSPL_ID FROM PS_AV_CNST_ACTN D WHERE D.INSTITUTION = A.INSTITUTION) OR B.EMPLID IN ( SELECT E.SLCTR_ID FROM PS_AV_CNST_ACTN E WHERE E.INSTITUTION = A.INSTITUTION) OR B.EMPLID IN ( SELECT F.STAFF_ID FROM PS_AV_CNST_AC_ASGN F WHERE F.INSTITUTION = A.INSTITUTION) OR B.EMPLID IN ( SELECT G.RSPL_ID FROM PS_AV_INTV_ACTN G WHERE G.INSTITUTION = A.INSTITUTION) OR B.EMPLID IN ( SELECT I.STAFF_ID FROM PS_AV_INTV_AC_ASGN I WHERE I.INSTITUTION = A.INSTITUTION))

# 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 NAME Character(50) VARCHAR2(50) NOT NULL Name