SCC_PERDATA_VW

(SQL View)
 Index Back

PERSONAL_DATA Query View

Live view of the person data in the same format at the PERSONAL_DATA table. This is not a snapshot view. This is the live data. This view can be used in lieu of the PERSONAL_DATA table.

SELECT P.EMPLID ,%subrec(NAMEGBL_NPC_SBR,NAM) ,%subrec(ADDRESS_SBR,ADR) , PDE.SEX , PDE.MAR_STATUS , PDE.MAR_STATUS_DT , P.BIRTHDATE , P.BIRTHPLACE , P.BIRTHCOUNTRY , P.BIRTHSTATE , P.DT_OF_DEATH , PDE.HIGHEST_EDUC_LVL , PDE.FT_STUDENT , PDE.LANG_CD , PDE.ALTER_EMPLID , ADR.ADDRESS1_AC , ADR.ADDRESS2_AC , ADR.ADDRESS3_AC , ADR.CITY_AC , OTR.COUNTRY_OTHER , OTR.ADDRESS1_OTHER , OTR.ADDRESS2_OTHER , OTR.ADDRESS3_OTHER , OTR.ADDRESS4_OTHER , OTR.CITY_OTHER , OTR.COUNTY_OTHER , OTR.STATE_OTHER , OTR.POSTAL_OTHER , OTR.NUM1_OTHER , OTR.NUM2_OTHER , OTR.HOUSE_TYPE_OTHER , OTR.ADDR_FIELD1_OTHER , OTR.ADDR_FIELD2_OTHER , OTR.ADDR_FIELD3_OTHER , OTR.IN_CITY_LMT_OTHER , OTR.GEO_CODE_OTHER , %subrec(PHONE_SBR,PHN) , PHN.EXTENSION , SA.VA_BENEFIT , SA.CAMPUS_ID , SA.DEATH_CERTIF_NBR , SA.FERPA , SA.PLACE_OF_DEATH , USA.US_WORK_ELIGIBILTY , USA.MILITARY_STATUS , USA.CITIZEN_PROOF1 , USA.CITIZEN_PROOF2 , USA.MEDICARE_ENTLD_DT , JPN.HONSEKI_JPN , ITA.MILITARY_STAT_ITA , ITA.MILITARY_TYPE_ITA , ITA.MILITARY_RANK_ITA , ITA.MILITARY_END_ITA , FRA.ENTRY_DT_FRA , FRA.MILIT_SITUATN_FRA , FRA.CPAMID , CAN.BILINGUALISM_CODE , CAN.HEALTH_CARE_NBR , CAN.HEALTH_CARE_STATE , ESP.MILIT_SITUATN_ESP , ESP.SOC_SEC_AFF_DT , DEU.MILITARY_STAT_GER , DEU.EXPCTD_MILITARY_DT , DEU.HR_RESPONSIBLE_ID , SM.SMOKER , SM.SMOKER_DT , %subrec(PERS_DTFED3_SBR,USF) , P.LAST_CHILD_UPDDTM FROM PS_PERSON P , PS_SCC_PERPDE_VW PDE , PS_SCC_PERNAM_VW NAM , PS_SCC_PERADR_VW ADR , PS_SCC_PEROTH_VW OTR , PS_SCC_PERPHN_VW PHN , PS_SCC_PERSMK_VW SM , PS_SCC_PERSSA_VW SA , PS_SCC_PERUSA_VW USA , PS_SCC_PERJPN_VW JPN , PS_SCC_PERITA_VW ITA , PS_SCC_PERFRA_VW FRA , PS_SCC_PERCAN_VW CAN , PS_SCC_PERESP_VW ESP , PS_SCC_PERDEU_VW DEU , PS_SCC_PERUSF_VW USF WHERE PDE.EMPLID = P.EMPLID AND NAM.EMPLID = P.EMPLID AND ADR.EMPLID = P.EMPLID AND OTR.EMPLID = P.EMPLID AND PHN.EMPLID = P.EMPLID AND SM.EMPLID = P.EMPLID AND SA.EMPLID = P.EMPLID AND USA.EMPLID = P.EMPLID AND JPN.EMPLID = P.EMPLID AND ITA.EMPLID = P.EMPLID AND FRA.EMPLID = P.EMPLID AND CAN.EMPLID = P.EMPLID AND ESP.EMPLID = P.EMPLID AND DEU.EMPLID = P.EMPLID AND USF.EMPLID = P.EMPLID

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

Default Value: NEW

2 COUNTRY_NM_FORMAT Character(3) VARCHAR2(3) NOT NULL Supported Name Format Types

Prompt Table: NAME_FORMAT_TBL

3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 NAME_INITIALS Character(6) VARCHAR2(6) NOT NULL Name Initials
5 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix

Prompt Table: NAME_PREFIX_TBL

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

Prompt Table: NAME_SUFFIX_TBL

7 NAME_ROYAL_PREFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Prefix

Prompt Table: NM_ROYPREF_GBL

8 NAME_ROYAL_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Royal Suffix

Prompt Table: NM_ROYSUFF_GBL

9 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Title

Prompt Table: TITLE_TBL

10 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
11 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
12 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
13 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
14 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
15 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
16 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Name
17 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
18 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
19 PARTNER_LAST_NAME Character(30) VARCHAR2(30) NOT NULL The Partner Last Name is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name as their prefered last name.
20 PARTNER_ROY_PREFIX Character(15) VARCHAR2(15) NOT NULL The Partner Royal Prefix is being used by the Netherlands for the employee name formatting, where a person can choose to use the partners last name and prefix as their prefered last name.

Prompt Table: NM_ROYPREF_GBL

21 LAST_NAME_PREF_NLD Character(1) VARCHAR2(1) NOT NULL The field Last Name Preference is used in the Netherlands specific for people who are married to indicated their preference regarding the last name, which can be 4 choices based on the own last name and the partners last name.
1=Own Name
2=Name Partner
3=Name Partner + Own Name
4=Own Name + Name Partner

Default Value: 1

22 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
23 NAME_FORMAL Character(60) VARCHAR2(60) NOT NULL Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
24 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: SCC_CNT_ADFMTVW

25 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
26 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
27 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
28 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
29 CITY Character(30) VARCHAR2(30) NOT NULL City
30 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
31 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
32 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference
33 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
34 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
35 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
36 COUNTY Character(30) VARCHAR2(30) NOT NULL County
37 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

38 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
39 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
40 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

41 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
42 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
43 MAR_STATUS_DT Date(10) DATE This is the date that the employee's Marital status is/was effective. /* Marital Status date is stored redundantly on PERS_DATA_EFFDT for two reasons. 1. To allow the date to be entered prior to the first PERS_DATA_EFFDT so that the actual date for the marital status on the hire row can be entered. This is the only time that the field is enterable by the user. 2. To allow for easier reporting on the data via SQR and Crystal. This is denormalizing the PERS_DATA_EFFDT record, but the number of requests for this redundancy coupled with the need for #1, has provided the justification for denormalizing this information and taking on the added maintenance. */
44 BIRTHDATE Date(10) DATE Date of Birth
45 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
46 BIRTHCOUNTRY Character(3) VARCHAR2(3) NOT NULL Birth Country

Prompt Table: COUNTRY_TBL

47 BIRTHSTATE Character(6) VARCHAR2(6) NOT NULL Birth State

Prompt Table: BIRTHSTATE_VW

48 DT_OF_DEATH Date(10) DATE Date of Death
49 HIGHEST_EDUC_LVL Character(2) VARCHAR2(2) NOT NULL Highest Education Level
50 FT_STUDENT Character(1) VARCHAR2(1) NOT NULL Full-Time Student

Y/N Table Edit

51 LANG_CD Character(3) VARCHAR2(3) NOT NULL Language Code
CFR=Canadian French
DAN=Danish
DUT=Dutch
ENG=English
ESP=Spanish
FRA=French
GER=German
GRK=Greek
INE=International English
ITA=Italian
JPN=Japanese
KOR=Korean
POR=Portuguese
SVE=Swedish
THA=Thai
ZHS=Simplified Chinese
ZHT=Traditional Chinese
52 ALTER_EMPLID Character(11) VARCHAR2(11) NOT NULL Alternate Employee ID
53 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1
54 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2
55 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3
56 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City
57 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT

Prompt Table: COUNTRY_TBL

58 ADDRESS1_OTHER Character(55) VARCHAR2(55) NOT NULL Address 1 Other
59 ADDRESS2_OTHER Character(55) VARCHAR2(55) NOT NULL Address 2 Other
60 ADDRESS3_OTHER Character(55) VARCHAR2(55) NOT NULL Address 3 Other
61 ADDRESS4_OTHER Character(55) VARCHAR2(55) NOT NULL Address 4 Other
62 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other
63 COUNTY_OTHER Character(30) VARCHAR2(30) NOT NULL County Other
64 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other

Prompt Table: STATE_OTH

65 POSTAL_OTHER Character(12) VARCHAR2(12) NOT NULL Postal Other
66 NUM1_OTHER Character(6) VARCHAR2(6) NOT NULL Number 1 Other
67 NUM2_OTHER Character(6) VARCHAR2(6) NOT NULL Number 2 Other
68 HOUSE_TYPE_OTHER Character(2) VARCHAR2(2) NOT NULL House Type Other
AB=House Boat Reference
WW=House Trailer Reference
69 ADDR_FIELD1_OTHER Character(2) VARCHAR2(2) NOT NULL Address Field 1 Other
70 ADDR_FIELD2_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 2 Other
71 ADDR_FIELD3_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 3 Other
72 IN_CITY_LMT_OTHER Character(1) VARCHAR2(1) NOT NULL In City Limit Other
73 GEO_CODE_OTHER Character(11) VARCHAR2(11) NOT NULL Geo Code Other
74 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code
75 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
76 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone number extension
77 VA_BENEFIT Character(1) VARCHAR2(1) NOT NULL VA Benefit

Y/N Table Edit

Default Value: N

78 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
79 DEATH_CERTIF_NBR Character(10) VARCHAR2(10) NOT NULL Death Certificate Nbr
80 FERPA Character(1) VARCHAR2(1) NOT NULL FERPA

Y/N Table Edit

Default Value: N

81 PLACE_OF_DEATH Character(30) VARCHAR2(30) NOT NULL Place of Death
82 US_WORK_ELIGIBILTY Character(1) VARCHAR2(1) NOT NULL Eligible to Work in U.S.

Y/N Table Edit

Default Value: Y

83 MILITARY_STATUS Character(1) VARCHAR2(1) NOT NULL Veteran Status / Military Status
1=Not indicated
2=No Military Service
3=Veteran of the Vietnam Era
4=Active Duty/Campaign Badge Vet
4=Other Protected Veteran
5=Active Reserve
6=Inactive Reserve
7=Retired Military
8=Veteran (VA Ineligible)
9=Vietnam & Active Duty Vet
9=Vietnam & Other Protected Vet
A=Armed Forces Service Medal Vet
B=Pre-Vietnam-Era Veteran
C=Separated/Srvc Medal/Active
C=Separated/Srvc Medal/Other Vet
D=Protected, Not Classified
N=Not a Vietnam-Era Veteran
O=Service Medal & Active Duty
O=Service Medal & Other Vet
P=Post-Vietnam-Era Veteran
R=Recently Separated Veteran
S=Separated & Service Medal Vet
T=Separated & Active Duty Vet
T=Separated & Other Vet
V=Vietnam-Era Veteran
X=Not a Veteran
X=Not a Veteran
Z=Not a Protected Veteran

Default Value: 1

84 CITIZEN_PROOF1 Character(10) VARCHAR2(10) NOT NULL Citizenship (Proof 1)
85 CITIZEN_PROOF2 Character(10) VARCHAR2(10) NOT NULL Citizenship (Proof 2)
86 MEDICARE_ENTLD_DT Date(10) DATE Date Entitled to Medicare
87 HONSEKI_JPN Character(2) VARCHAR2(2) NOT NULL Honseki Prefecture

Prompt Table: STATE_MC_JPN_VW

88 MILITARY_STAT_ITA Character(1) VARCHAR2(1) NOT NULL Military Status Italy
C=Completed
E=Exempt
M=Mandatory
R=Revisited
89 MILITARY_TYPE_ITA Character(2) VARCHAR2(2) NOT NULL Type of Service
AE=Airforce
CA=Carabinieri
CI=Civil Service
M=Military
MA=Navy
90 MILITARY_RANK_ITA Character(50) VARCHAR2(50) NOT NULL Function or Rank
91 MILITARY_END_ITA Date(10) DATE Military End Date
92 ENTRY_DT_FRA Date(10) DATE Date of First Entry in France
93 MILIT_SITUATN_FRA Character(3) VARCHAR2(3) NOT NULL Military Status
APA=No Country Person
BEN=Asile Right
DEG=Military Service Done
ETR=Alien
REF=Reformed (don't do milit serv)
SER=Military Service to Come
SUR=Military Service Delayed
94 CPAMID Character(6) VARCHAR2(6) NOT NULL CPAM ID

Prompt Table: CPAM_TBL

95 BILINGUALISM_CODE Character(1) VARCHAR2(1) NOT NULL Bilingualism Code
1=Not Bilingual
2=Bilingual
3=Receptive Skills Only
9=Unknown
96 HEALTH_CARE_NBR Character(12) VARCHAR2(12) NOT NULL Health Care Number
97 HEALTH_CARE_STATE Character(6) VARCHAR2(6) NOT NULL Health Care Province

Prompt Table: PROVINCE_NM_VW

98 MILIT_SITUATN_ESP Character(3) VARCHAR2(3) NOT NULL Military Status
0=Not Applicable
1=Service Done
2=Exempt from Service
3=Service
4=Conscientious Objector
9=No Information
99 SOC_SEC_AFF_DT Date(10) DATE Social Security Affiliation Dt
100 MILITARY_STAT_GER Character(1) VARCHAR2(1) NOT NULL Military Status
1=Service Completed
2=Exempt from Service
3=Not Applicable
4=Mandatory
5=No Information
101 EXPCTD_MILITARY_DT Date(10) DATE Expected Military Date
102 HR_RESPONSIBLE_ID Character(11) VARCHAR2(11) NOT NULL HR Responsible ID

Prompt Table: PERSON_NAME

103 SMOKER Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) an employee or dependent is a smoker.
N=Non Smoker
Y=Smoker

Y/N Table Edit

104 SMOKER_DT Date(10) DATE Smoker Date
105 GVT_CRED_MIL_SVCE Character(4) VARCHAR2(4) NOT NULL Creditable Military Service
106 GVT_MILITARY_COMP Character(1) VARCHAR2(1) NOT NULL Uniformed Service
A=Air Force
C=Coast Guard
M=Marines
N=Navy
R=Army
107 GVT_MIL_GRADE Character(3) VARCHAR2(3) NOT NULL Military Grade
E1=E1
E2=E2
E3=E3
E4=E4
E5=E5
E6=E6
E7=E7
E8=E8
E9=E9
O1=O1
O10=O10
O2=O2
O3=O3
O4=O4
O5=O5
O6=O6
O7=O7
O8=O8
O9=O9
W1=W1
W2=W2
W3=W3
W4=W4
108 GVT_MIL_RESRVE_CAT Character(1) VARCHAR2(1) NOT NULL Reserve Category
A=IMA - Air Force
B=IMA - Army
C=IMA - Coast Guard
D=IMA - Marines
E=IMA - Navy
F=Selected Reserve - Air Force
G=Selected Reserve - Army
H=Selected Reserve - Coast Guard
I=Selected Reserve - Marine Corp
J=Selected Reserves - Navy
K=Air National Guard
L=Army National Guard
M=IRR - Air Force
N=IRR - Army
O=IRR - Coast Guard
P=IRR - Marine Corps
Q=IRR - Navy
S=Standby Reserve - Air Force
T=Standby Reserve - Army
U=Standby Reserve - Coast Guard
V=Standby Reserve - Marine Corps
W=Standby Reserve - Navy
X=Navy Reserve - Merchant Marine
Y=Draft Eligible
Z=Not Applicable
109 GVT_MIL_SEP_RET Character(1) VARCHAR2(1) NOT NULL Military Separation Status
R=Retired
S=Separated
Z=Not Applicable
110 GVT_MIL_SVCE_END Date(10) DATE Military Service End Date
111 GVT_MIL_SVCE_START Date(10) DATE Military Service Start Date
112 GVT_MIL_VERIFY Character(1) VARCHAR2(1) NOT NULL Military Service Verified
113 GVT_PAR_NBR_LAST Number(6,0) INTEGER NOT NULL Last Personnel Action Reqst #
114 GVT_UNIF_SVC_CTR Character(1) VARCHAR2(1) NOT NULL Notify Military Pay Center
115 GVT_VET_PREF_APPT Character(1) VARCHAR2(1) NOT NULL Veterans Preference
116 GVT_VET_PREF_RIF Character(1) VARCHAR2(1) NOT NULL Veterans Preference RIF

Default Value: N

117 GVT_CHANGE_FLAG Character(1) VARCHAR2(1) NOT NULL Change Flag
118 GVT_DRAFT_STATUS Character(1) VARCHAR2(1) NOT NULL Draft Status
N=Not Yet Registered
R=Registered
X=Not Applicable
119 GVT_YR_ATTAINED Date(10) DATE Year Attained
120 DISABLED_VET Character(1) VARCHAR2(1) NOT NULL Disabled Veteran

Y/N Table Edit

121 DISABLED Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not (Y or N) a dependent is a disabled.

Default Value: N

122 GVT_DISABILITY_CD Character(2) VARCHAR2(2) NOT NULL Disability Code

Default Value: 05

123 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
124 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan

Default Value: 0000

125 GVT_CURR_AGCY_EMPL Character(1) VARCHAR2(1) NOT NULL Current Agency Employee

Y/N Table Edit

Default Value: N

126 GVT_CURR_FED_EMPL Character(1) VARCHAR2(1) NOT NULL Current Federal Employee

Y/N Table Edit

Default Value: N

127 GVT_HIGH_PAY_PLAN Character(2) VARCHAR2(2) NOT NULL Highest Pay Plan
128 GVT_HIGH_GRADE Character(3) VARCHAR2(3) NOT NULL Highest Grade
129 GVT_PREV_AGCY_EMPL Character(1) VARCHAR2(1) NOT NULL Previous Agency Employee

Y/N Table Edit

Default Value: N

130 GVT_PREV_FED_EMPL Character(1) VARCHAR2(1) NOT NULL Previous Federal Employee

Y/N Table Edit

Default Value: N

131 GVT_SEP_INCENTIVE Character(1) VARCHAR2(1) NOT NULL Separation Incentive
B=Buyout/VSIP
E=Early Retirement
O=Other Incentive
132 GVT_SEP_INCENT_DT Date(10) DATE Separation Incentive Date
133 GVT_TENURE Character(1) VARCHAR2(1) NOT NULL Highest Career Tenure
0=None
1=Permanent
2=Conditional
3=Indefinite
134 GVT_PAY_PLAN Character(2) VARCHAR2(2) NOT NULL Pay Plan
135 BARG_UNIT Character(4) VARCHAR2(4) NOT NULL Bargaining Unit
1=Bargaining Unit 1
2=Bargaining Unit 2
136 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.