RELATION_VW

(SQL View)
 Index Back

Relationships View

Relationships View contains all fields in the Relationships table. It is used to update Relationships.

SELECT A.EMPLID , A.RELATIONSHIP_NBR , A.EFFDT , A.EFF_STATUS , A.PEOPLE_RELATION , A.GUARDIAN_RELATIONS , A.NAME , A.EMPLID_RELATED , A.RELATION_NBR_RCP , A.HIGHEST_EDUC_LVL , A.GUARDIAN_OCC , A.EXT_ORG_ID , A.EMPLOYER , A.RELATIONS_INCOME , A.MAR_STATUS , A.RESIDENCY_DT , A.STATE_RESIDENCY , A.COUNTRY_RESIDENCY , A.NATIONAL_ID , A.CURRENCY_CD , A.ADDRESS1 , A.ADDRESS2 , A.ADDRESS3 , A.ADDRESS4 , A.CITY , A.IN_CITY_LIMIT , A.COUNTY , A.STATE , A.POSTAL , A.COUNTRY , a.num1 , a.num2 , a.house_type , a.geo_code , A.CHG_ADDR , A.CHG_OTHER , A.NAME_PREFIX , A.NAME_SUFFIX , A.SALUTATION , A.CONTACT_THRU , A.PHONE_DAY , A.PHONE_EVE , A.PHONE_FAX , A.EMAILID , A.URL_ADDRESS , A.SEX , A.CITIZEN_COUNTRY , A.CITIZENSHIP_STATUS , A.ALL_COMMUNICATIONS , A.COMMENTS , A.JOINT_RECORD , A.JOINT_ADDRESS , A.JOINT_ADDRESS_RCP , A.ADDR_TYPE3 , A.ADDRESS_TYPE , A.DISSOLVE_REASON , A.E_ADDR_TYPE , A.E_ADDR_TYP2 , A.BIRTHCOUNTRY FROM PS_RELATIONSHIPS A WHERE 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) AND EFF_STATUS='A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PEOPLE_SRCH

2 RELATIONSHIP_NBR Number(2,0) SMALLINT NOT NULL Relationship Nbr
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

Default Value: A

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

Default Value: NI

6 GUARDIAN_RELATIONS Character(2) VARCHAR2(2) NOT NULL Guardian Relationship
LG=Legal Guardian
NA=Not Applicable
OT=Other
PG=Parent Guardian
SG=Self Guardian
XG=Primary Guardian

Default Value: NA

7 NAME Character(50) VARCHAR2(50) NOT NULL Name
8 EMPLID_RELATED Character(11) VARCHAR2(11) NOT NULL Related People ID

Prompt Table: PEOPLE_SRCH

9 RELATION_NBR_RCP Number(2,0) SMALLINT NOT NULL Reciprocal Relationship Number
10 HIGHEST_EDUC_LVL Character(2) VARCHAR2(2) NOT NULL Highest Education Level
11 GUARDIAN_OCC Character(10) VARCHAR2(10) NOT NULL Guardian Occupation

Prompt Table: US_SOC_TBL

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

Prompt Table: EXT_ORG_TBL

13 EMPLOYER Character(30) VARCHAR2(30) NOT NULL Employer
14 RELATIONS_INCOME Number(9,0) DECIMAL(9) NOT NULL Relations Income
15 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
C=Common-Law
D=Divorced
E=Separated
H=Head of Household
L=DissDeclLost Civil Partner
M=Married
P=Civil Partnership
S=Single
T=Surviving Civil Partner
U=Unknown
V=Dissolved Civil Partnership
W=Widowed
16 RESIDENCY_DT Date(10) DATE Residency Date
17 STATE_RESIDENCY Character(6) VARCHAR2(6) NOT NULL State of Residency

Prompt Table: STATE_RES_VW

18 COUNTRY_RESIDENCY Character(3) VARCHAR2(3) NOT NULL Country of Residency

Default Value: INSTALLATION.COUNTRY

Prompt Table: COUNTRY_TBL

19 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
20 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: INSTALLATION.EXCHNG_TO_CURRENCY

Prompt Table: CURRENCY_CD_TBL

21 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
22 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
23 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
24 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
25 CITY Character(30) VARCHAR2(30) NOT NULL City
26 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

Default Value: N

27 COUNTY Character(30) VARCHAR2(30) NOT NULL County
28 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_NAMES_TBL

29 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
30 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Default Value: INSTALLATION.COUNTRY

Prompt Table: COUNTRY_TBL

31 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
32 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
33 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference
34 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
35 CHG_ADDR Character(1) VARCHAR2(1) NOT NULL Address Changed

Y/N Table Edit

Default Value: N

36 CHG_OTHER Character(1) VARCHAR2(1) NOT NULL Other Changed

Y/N Table Edit

Default Value: N

37 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix

Prompt Table: NAME_PREFIX_TBL

38 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix

Prompt Table: NAME_SUFFIX_TBL

39 SALUTATION Character(20) VARCHAR2(20) NOT NULL Salutation

Prompt Table: SALUTATION_TBL

40 CONTACT_THRU Number(2,0) SMALLINT NOT NULL Contact Thru

Prompt Table: REL_CONTACT_VW

41 PHONE_DAY Character(30) VARCHAR2(30) NOT NULL Daytime Telephone
42 PHONE_EVE Character(30) VARCHAR2(30) NOT NULL Evening Telephone
43 PHONE_FAX Character(30) VARCHAR2(30) NOT NULL Fax Nbr
44 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
45 URL_ADDRESS Character(254) VARCHAR2(254) NOT NULL URL Address
46 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
47 CITIZEN_COUNTRY Character(3) VARCHAR2(3) NOT NULL Country of Citizenship

Prompt Table: COUNTRY_TBL

48 CITIZENSHIP_STATUS Character(1) VARCHAR2(1) NOT NULL Citizenship Status

Prompt Table: REL_CITIZEN_VW
Set Control Field: CITIZEN_COUNTRY

49 ALL_COMMUNICATIONS Character(1) VARCHAR2(1) NOT NULL All Communications

Y/N Table Edit

Default Value: N

50 COMMENTS Long Character CLOB Comment
51 JOINT_RECORD Character(1) VARCHAR2(1) NOT NULL Create Joint Records

Y/N Table Edit

Default Value: N

52 JOINT_ADDRESS Character(1) VARCHAR2(1) NOT NULL Joint Address

Y/N Table Edit

Default Value: N

53 JOINT_ADDRESS_RCP Character(1) VARCHAR2(1) NOT NULL Joint Address

Y/N Table Edit

Default Value: N

54 ADDR_TYPE3 Character(4) VARCHAR2(4) NOT NULL Address Type

Prompt Table: RELATED_ADDR_VW

55 ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type

Prompt Table: ADDRESSES_EFFVW

56 DISSOLVE_REASON Character(2) VARCHAR2(2) NOT NULL Dissolution Reason
DI=Divorce
DP=Deceased Partner
LA=Leave of Absence
LD=Long Term Disability
MV=Move
OT=Other
PR=Per Request
SP=Separation
57 E_ADDR_TYPE Character(4) VARCHAR2(4) NOT NULL Email Address Type. Note: Dorm long name has been updated as Dormitory as part of PI26 updates. And, the owner id of the field is updated to CC from HR.
BUSN=Business
CAMP=Campus
DORM=Dormitory
HOME=Home
OTHR=Other
58 E_ADDR_TYP2 Character(4) VARCHAR2(4) NOT NULL Email Address Type
BUSN=Business
CAMP=Campus
DORM=Dormitory
HOME=Home
OTHR=Other
PREF=Preferred
59 BIRTHCOUNTRY Character(3) VARCHAR2(3) NOT NULL Birth Country