AV_PL_SEC_SRCH (SQL View)	Index Back
AV People Pldg Srch Secured 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. Derived from AV_PLDG_SRCH, this view incorporates a join to the BU Security table for Business Unit Security. It is assigned to the component AV_PLDG_SCHD_ADJ by the Security App Engine Process. 10/24/2003 8.9 Person Model Before: SELECT C.OPRID , C.BUSINESS_UNIT , A.EMPLID , A.NAME , D.SESSION_NO , D.GIFT_NO , D.INSTITUTION , 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_AV_SCRTY_BU_TBL 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 D.BUSINESS_UNIT = C.BUSINESS_UNIT 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'

AV_PL_SEC_SRCH

(SQL View)

AV People Pldg Srch Secured

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. Derived from AV_PLDG_SRCH, this view incorporates a join to the BU Security table for Business Unit Security. It is assigned to the component AV_PLDG_SCHD_ADJ by the Security App Engine Process. 10/24/2003 8.9 Person Model Before: SELECT C.OPRID , C.BUSINESS_UNIT , A.EMPLID , A.NAME , D.SESSION_NO , D.GIFT_NO , D.INSTITUTION , 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_AV_SCRTY_BU_TBL 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 D.BUSINESS_UNIT = C.BUSINESS_UNIT 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.BUSINESS_UNIT , A.EMPLID , B.NAME , D.SESSION_NO , D.GIFT_NO , D.INSTITUTION , D.SEQ_NO , 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_AV_SCRTY_BU_TBL C , PS_AV_PLEDGE_DTL D , PS_AV_SESSION_TBL G WHERE A.EMPLID = B.EMPLID AND A.EMPLID = D.EMPLID AND D.BUSINESS_UNIT = C.BUSINESS_UNIT 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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: AV_OPR_DEF_TBL.BUSINESS_UNIT
Prompt Table: AV_BU_SCRTY_VW

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 Gift Nbr
Prompt Table: AV_PLDG2_SRCH

7 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
Default Value: AV_OPR_DEF_TBL.INSTITUTION

8 SEQ_NO Number(2,0) SMALLINT NOT NULL Sequence Number

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

#	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	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: AV_OPR_DEF_TBL.BUSINESS_UNIT Prompt Table: AV_BU_SCRTY_VW
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	Gift Nbr Prompt Table: AV_PLDG2_SRCH
7	INSTITUTION	Character(5)	VARCHAR2(5) NOT NULL	Academic Institution Default Value: AV_OPR_DEF_TBL.INSTITUTION
8	SEQ_NO	Number(2,0)	SMALLINT NOT NULL	Sequence Number
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