AV_PLDG_SRCH

(SQL View)
 Index Back

Advancement People/Pledge Srch

Common view used when searching for/selecting a person that has active pledges for display/update. This view goes against the names table as well as the personal data table and the pledge details. The PeopleCode contained on this view is based upon the peoplecode in PEOPLE_SRCH. ----------------------------------------------------------------------------------------------------------------- Changed the search field order to be consistent with other search records - 22 Aug 2000. SELECT A.EMPLID, A.NAME, D.SESSION_NO, D.GIFT_NO, D.SEQ_NO, E.COUNTRY, F.NATIONAL_ID_TYPE, F.DESCRSHORT, E.NATIONAL_ID, C.INSTITUTION, C.OPRID, D.PLEDGE_DT, A.BIRTHDATE, A.SEX, A.CAMPUS_ID, A.LAST_NAME_SRCH, A.FIRST_NAME_SRCH FROM PS_PERSONAL_DATA A, PS_SCRTY_TBL_INST C, PS_AV_PLEDGE_DTL D, PS_PERS_NID E, PS_NID_TYPE_TBL F WHERE A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.ADJUSTMENT_FLG='N' AND E.COUNTRY = F.COUNTRY AND E.NATIONAL_ID_TYPE = F.NATIONAL_ID_TYPE ----------------------------------------------------------------------------------------------------------------- 06/23/99 Jitendra Gupta - Removed the Names table and the Distinct criteria from the view. SELECT A.EMPLID, A.NAME, D.GIFT_NO, E.COUNTRY, F.NATIONAL_ID_TYPE, F.DESCRSHORT, E.NATIONAL_ID, C.INSTITUTION, C.OPRID, D.PLEDGE_DT, A.BIRTHDATE, A.SEX, A.CAMPUS_ID, B.LAST_NAME_SRCH, B.FIRST_NAME_SRCH, B.NAME_TYPE FROM PS_PERSONAL_DATA A, PS_NAMES B, PS_SCRTY_TBL_INST C, PS_AV_PLEDGE_DTL D, PS_PERS_NID E, PS_NID_TYPE_TBL F WHERE A.EMPLID = B.EMPLID AND B.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND B.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.ADJUSTMENT_FLG='N' AND E.COUNTRY = F.COUNTRY AND E.NATIONAL_ID_TYPE = F.NATIONAL_ID_TYPE DD 2/4/00 - Adding session number as a key & adding sequence number for APRD: R-ACLARK-W26U6. OLD: SELECT A.EMPLID, A.NAME, D.GIFT_NO, E.COUNTRY, F.NATIONAL_ID_TYPE, F.DESCRSHORT, E.NATIONAL_ID, C.INSTITUTION, C.OPRID, D.PLEDGE_DT, A.BIRTHDATE, A.SEX, A.CAMPUS_ID, A.LAST_NAME_SRCH, A.FIRST_NAME_SRCH FROM PS_PERSONAL_DATA A, PS_SCRTY_TBL_INST C, PS_AV_PLEDGE_DTL D, PS_PERS_NID E, PS_NID_TYPE_TBL F WHERE A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.ADJUSTMENT_FLG='N' AND E.COUNTRY = F.COUNTRY AND E.NATIONAL_ID_TYPE = F.NATIONAL_ID_TYPE 10/24/2003 8.9 Person Model Before: SELECT C.OPRID , C.INSTITUTION , A.EMPLID , A.NAME , D.SESSION_NO , D.GIFT_NO , D.SEQ_NO , E.COUNTRY , F.NATIONAL_ID_TYPE , F.DESCRSHORT , E.NATIONAL_ID , D.PLEDGE_DT , A.BIRTHDATE , A.SEX , A.CAMPUS_ID , A.LAST_NAME_SRCH , A.FIRST_NAME_SRCH FROM PS_PERSONAL_DATA A , PS_SCRTY_TBL_INST C , PS_AV_PLEDGE_DTL D , PS_PERS_NID E , PS_NID_TYPE_TBL F , PS_AV_SESSION_TBL G WHERE A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND A.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.ADJUSTMENT_FLG='N' AND E.COUNTRY = F.COUNTRY AND E.NATIONAL_ID_TYPE = F.NATIONAL_ID_TYPE AND D.BUSINESS_UNIT = G.BUSINESS_UNIT AND D.SESSION_NO = G.SESSION_NO AND G.SESS_STATUS = 'P'

SELECT C.OPRID , C.INSTITUTION , A.EMPLID , B.NAME , D.SESSION_NO , D.GIFT_NO , D.SEQ_NO , D.GIFT_AMT , D.PLEDGE_DT , A.CAMPUS_ID , B.LAST_NAME_SRCH , B.FIRST_NAME_SRCH FROM PS_SCC_PERS_SA_VW A , PS_HCR_PERSON_NM_I B , PS_SCRTY_TBL_INST C , PS_AV_PLEDGE_DTL D , PS_AV_SESSION_TBL G WHERE A.EMPLID = B.EMPLID AND A.EMPLID = D.EMPLID AND B.LAST_NAME_SRCH <> ' ' AND C.ACCESS_CD = 'Y' AND D.ADJUSTMENT_FLG='N' AND D.BUSINESS_UNIT = G.BUSINESS_UNIT AND D.SESSION_NO = G.SESSION_NO AND G.SESS_STATUS = 'P'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Institution

Default Value: AV_OPR_DEF_TBL.INSTITUTION

Prompt Table: INSTITUTN_SCRTY

3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: AV_PEOPLE_SRCH

4 NAME Character(50) VARCHAR2(50) NOT NULL Name
5 SESSION_NO Character(11) VARCHAR2(11) NOT NULL Session Nbr
6 GIFT_NO Character(11) VARCHAR2(11) NOT NULL Pledge Number
7 SEQ_NO Number(2,0) SMALLINT NOT NULL Sequence Number
8 GIFT_AMT Signed Number(17,2) DECIMAL(15,2) NOT NULL Pledge Amount
9 PLEDGE_DT Date(10) DATE First Payment Date
10 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
11 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
12 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name