RELATION_SRCH

(SQL View)
 Index Back

People Search View

Common view used when searching for/selecting a person for display/update. This view should be used as the basis for all other views which go against people. The PeopleCode contained on this view is important for any other view which is based on this one - the searching/formatting for names and setting the carry id is done with this code.

SELECT B.OPRCLASS ,A.EMPLID ,A.RELATIONSHIP_NBR ,B.NAME ,B.NATIONAL_ID ,B.NATIONAL_ID_MSK ,B.NATIONAL_ID_TYPE ,B.NID_DESCRSHORT ,B.LAST_NAME_SRCH ,B.FIRST_NAME_SRCH ,A.EMPLID_RELATED ,A.PEOPLE_RELATION ,A.NAME ,A.JOINT_RECORD FROM PS_RELATIONSHIPS A , PS_PEOPLE_SRCH B WHERE A.EMPLID = B.EMPLID AND A.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_RELATIONSHIPS X WHERE X.EMPLID = A.EMPLID AND X.RELATIONSHIP_NBR = A.RELATIONSHIP_NBR AND X.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

3 RELATIONSHIP_NBR Number(2,0) SMALLINT NOT NULL Relationship Nbr
4 AV_NAME2 Character(50) VARCHAR2(50) NOT NULL Name
5 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
6 NATIONAL_ID_MSK Character(20) VARCHAR2(20) NOT NULL National ID
7 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type

Prompt Table: NID_TYPE1_VW

8 NID_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL NID Short Description
9 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
10 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
11 EMPLID_RELATED Character(11) VARCHAR2(11) NOT NULL Related People ID

Prompt Table: PEOPLE_SRCH

12 PEOPLE_RELATION Character(2) VARCHAR2(2) NOT NULL Relationship

Default Value: NI

13 NAME Character(50) VARCHAR2(50) NOT NULL Name
14 JOINT_RECORD Character(1) VARCHAR2(1) NOT NULL Create Joint Records

Y/N Table Edit

Default Value: N