PEOPLE_TP_SF (SQL View)	Index Back
People Search SF 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.

PEOPLE_TP_SF

(SQL View)

People Search SF 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 A.BUSINESS_UNIT ,A.COMMON_ID ,A.COMMON_ID ,A.CONTRACT_EMPLID ,C.COUNTRY ,C.NATIONAL_ID_TYPE ,D.DESCRSHORT ,C.NATIONAL_ID ,E.BIRTHDATE ,F.SEX ,B.NAME ,E.CAMPUS_ID ,B.LAST_NAME_SRCH ,B.FIRST_NAME_SRCH FROM PS_ITEM_SF A ,PS_SCC_PRI_NAME_VW B ,PS_PERS_NID C ,PS_NID_TYPE_TBL D ,PS_SCC_PERS_SA_VW E ,PS_HCR_PER_PDE_I F WHERE A.CONTRACT_EMPLID = B.EMPLID AND A.CONTRACT_EMPLID = C.EMPLID AND A.CONTRACT_EMPLID = E.EMPLID AND A.CONTRACT_EMPLID = F.EMPLID AND D.COUNTRY = C.COUNTRY AND D.NATIONAL_ID_TYPE = C.NATIONAL_ID_TYPE AND A.SA_ID_TYPE = 'O' AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_HCR_PER_PDE_I F1 WHERE F1.EMPLID = F.EMPLID AND F1.EFFDT <= %CurrentDateIn) GROUP BY A.BUSINESS_UNIT ,A.COMMON_ID ,A.COMMON_ID ,A.CONTRACT_EMPLID ,C.COUNTRY ,C.NATIONAL_ID_TYPE ,D.DESCRSHORT ,C.NATIONAL_ID ,E.BIRTHDATE ,F.SEX ,B.NAME ,E.CAMPUS_ID ,B.LAST_NAME_SRCH ,B.FIRST_NAME_SRCH

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_CS.BUSINESS_UNIT
Prompt Table: SP_BU_SF_NONVW

2 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value

3 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID

4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: PEOPLE_SRCH

5 NID_COUNTRY Character(3) VARCHAR2(3) NOT NULL National ID Country

6 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type
Prompt Table: NID_TYPE_TBL

7 NID_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL NID Short Description

8 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID

9 BIRTHDATE Date(10) DATE Date of Birth

10 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec

11 NAME Character(50) VARCHAR2(50) NOT NULL Name

12 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID

13 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

14 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_CS.BUSINESS_UNIT Prompt Table: SP_BU_SF_NONVW
2	COMMON_ID	Character(11)	VARCHAR2(11) NOT NULL	Common ID to store Personal ID / Ext Org ID value
3	EXT_ORG_ID	Character(11)	VARCHAR2(11) NOT NULL	External Org ID
4	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: PEOPLE_SRCH
5	NID_COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	National ID Country
6	NATIONAL_ID_TYPE	Character(6)	VARCHAR2(6) NOT NULL	National ID Type Prompt Table: NID_TYPE_TBL
7	NID_DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	NID Short Description
8	NATIONAL_ID	Character(20)	VARCHAR2(20) NOT NULL	National ID
9	BIRTHDATE	Date(10)	DATE	Date of Birth
10	SEX	Character(1)	VARCHAR2(1) NOT NULL	Gender F=Female M=Male U=Unknown X=Indeterminate/Intersex/Unspec
11	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
12	CAMPUS_ID	Character(16)	VARCHAR2(16) NOT NULL	Campus ID
13	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
14	FIRST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	First Name