SAE_LIS_PHN_VW (SQL View)	Index Back
SAIP Person phone view Person Phone details view of SAIP

SAE_LIS_PHN_VW

(SQL View)

SAIP Person phone view

Person Phone details view of SAIP

SELECT PHONE.EMPLID ,PHNMAP.SAE_TARGET_ID , PHNMAP.SAE_LIS_PHN_TYPE , PHONE.PHONE_TYPE , XLAT.XLATLONGNAME SAE_LIS_PHN_LNAME , PHONE.COUNTRY_CODE , PHONE.PHONE , PHONE.EXTENSION , PHONE.PREF_PHONE_FLAG , 'A' SAE_MODE FROM %Table(PERSONAL_PHONE) PHONE , %Table(SAE_LIS_PHNMAP) PHNMAP , PSXLATITEM XLAT WHERE PHNMAP.SAE_CS_PHN_TYPE <> 'PRF' AND PHONE.PHONE_TYPE = PHNMAP.SAE_CS_PHN_TYPE AND XLAT.FIELDNAME = 'SAE_LIS_PHN_TYPE' AND XLAT.FIELDVALUE = PHNMAP.SAE_LIS_PHN_TYPE AND %EffdtCheck(PSXLATITEM XL, XLAT, %CurrentDateIn) AND XLAT.EFF_STATUS = 'A' UNION ALL SELECT PHONE.EMPLID ,PHNMAP.SAE_TARGET_ID , PHNMAP.SAE_LIS_PHN_TYPE , PHONE.PHONE_TYPE , XLAT.XLATLONGNAME SAE_LIS_PHN_LNAME , PHONE.COUNTRY_CODE , PHONE.PHONE , PHONE.EXTENSION , PHONE.PREF_PHONE_FLAG , 'A' SAE_MODE FROM %Table(PERSONAL_PHONE) PHONE , %Table(SAE_LIS_PHNMAP) PHNMAP , PSXLATITEM XLAT WHERE PHNMAP.SAE_CS_PHN_TYPE = 'PRF' AND PHONE.PREF_PHONE_FLAG = 'Y' AND XLAT.FIELDNAME = 'SAE_LIS_PHN_TYPE' AND XLAT.FIELDVALUE = PHNMAP.SAE_LIS_PHN_TYPE AND %EffdtCheck(PSXLATITEM XL, XLAT, %CurrentDateIn) AND XLAT.EFF_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 SAE_TARGET_ID Character(50) VARCHAR2(50) NOT NULL SAIP Target ID

3 SAE_LIS_PHN_TYPE Character(4) VARCHAR2(4) NOT NULL LIS Contact Info Type
FAX=Facsimile
FAXH=FacsimileHome
FAXW=FacsimileWork
MOB=Mobile
MOBH=MobileHome
MOBW=MobileWork
MOHP=MobileHomePrimary
MOHS=MobileHomeSecondary
MOWP=MobileWorkPrimary
MOWS=MobileWorkSecondary
PAG=Pager
SMS=SMS
TELE=Telephone
TELH=TelephoneHome
TELP=TelephonePrimary
TELS=TelephoneSecondary
TELW=TelephoneWork
TLHP=TelephoneHomePrimary
TLHS=TelephoneHomeSecondary
TLWP=TelephoneWorkPrimary
TLWS=TelephoneWorkSecondary

4 PHONE_TYPE Character(4) VARCHAR2(4) NOT NULL Phone Type
BUSN=Business
CAMP=Campus
CELL=Mobile
DORM=Dormitory
FAX=FAX
HOME=Home
MAIN=Main
OTR=Other
PGR1=Pager 1
PGR2=Pager 2
TELX=Telex
WORK=Work

5 SAE_LIS_PHN_LNAME Character(30) VARCHAR2(30) NOT NULL LIS Phone

6 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code

7 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone

8 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone number extension

9 PREF_PHONE_FLAG Character(1) VARCHAR2(1) NOT NULL Preferred

10 SAE_MODE Character(1) VARCHAR2(1) NOT NULL Operation Mode
A=Add
C=Change
D=Delete

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	SAE_TARGET_ID	Character(50)	VARCHAR2(50) NOT NULL	SAIP Target ID
3	SAE_LIS_PHN_TYPE	Character(4)	VARCHAR2(4) NOT NULL	LIS Contact Info Type FAX=Facsimile FAXH=FacsimileHome FAXW=FacsimileWork MOB=Mobile MOBH=MobileHome MOBW=MobileWork MOHP=MobileHomePrimary MOHS=MobileHomeSecondary MOWP=MobileWorkPrimary MOWS=MobileWorkSecondary PAG=Pager SMS=SMS TELE=Telephone TELH=TelephoneHome TELP=TelephonePrimary TELS=TelephoneSecondary TELW=TelephoneWork TLHP=TelephoneHomePrimary TLHS=TelephoneHomeSecondary TLWP=TelephoneWorkPrimary TLWS=TelephoneWorkSecondary
4	PHONE_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Phone Type BUSN=Business CAMP=Campus CELL=Mobile DORM=Dormitory FAX=FAX HOME=Home MAIN=Main OTR=Other PGR1=Pager 1 PGR2=Pager 2 TELX=Telex WORK=Work
5	SAE_LIS_PHN_LNAME	Character(30)	VARCHAR2(30) NOT NULL	LIS Phone
6	COUNTRY_CODE	Character(3)	VARCHAR2(3) NOT NULL	Country Code
7	PHONE	Character(24)	VARCHAR2(24) NOT NULL	Telephone
8	EXTENSION	Character(6)	VARCHAR2(6) NOT NULL	Phone number extension
9	PREF_PHONE_FLAG	Character(1)	VARCHAR2(1) NOT NULL	Preferred
10	SAE_MODE	Character(1)	VARCHAR2(1) NOT NULL	Operation Mode A=Add C=Change D=Delete