HR_I9_PER_VW

(SQL Table)
 Index Back

I9 Data

This record stores all data for the employee I-9 forms.

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

3 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

Default Value: 0

4 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
5 SSN Character(9) VARCHAR2(9) NOT NULL Social Security #
6 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
7 BIRTHDATE Date(10) DATE Date of Birth
8 NAME Character(50) VARCHAR2(50) NOT NULL Name
9 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
10 MIDDLE_INITIAL Character(1) VARCHAR2(1) NOT NULL The employee's middle initial
11 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
12 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
13 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

14 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
15 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
16 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
17 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
18 CITY Character(30) VARCHAR2(30) NOT NULL City
19 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
20 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
21 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference
22 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
23 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
24 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
25 COUNTY Character(30) VARCHAR2(30) NOT NULL County
26 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

27 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
28 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
29 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

30 COUNTRY_EE Character(3) VARCHAR2(3) NOT NULL Country
CAN=Canada
MEX=Mexico
USA=United States

Default Value: USA

31 ZIP_CODE_EE Character(10) VARCHAR2(10) NOT NULL Postal Code
32 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code
33 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
34 PHONE_EE Character(12) VARCHAR2(12) NOT NULL Telephone
35 EMAIL_ADDR Character(70) VARCHAR2(70) NOT NULL Email Address
36 MAIDEN_NAME Character(50) VARCHAR2(50) NOT NULL Maiden Name
37 I9_ELIG_TYPE Character(1) VARCHAR2(1) NOT NULL Eligibility Type
A=Alien
C=Citizen
N=Non Citizen of US
R=Permanent Resident
38 I9_ALIEN_NBR Character(12) VARCHAR2(12) NOT NULL Alien Number
39 I9_ALIEN_USCIS Character(1) VARCHAR2(1) NOT NULL Alien/USCIS Number
A=Alien Number
U=USCIS Number
40 I9_ALIEN_AUTH_DT Date(10) DATE Alien Authorization Date
41 I9_ALIEN_NBR2 Character(12) VARCHAR2(12) NOT NULL Alien Number
42 I9_ALIEN_USCIS_2 Character(1) VARCHAR2(1) NOT NULL Alien/USCIS Number
A=Alien Number
U=USCIS Number
43 I9_ADMISSION_NBR Character(12) VARCHAR2(12) NOT NULL Alien Number
44 I9_SRC_ADMISS_NBR Character(1) VARCHAR2(1) NOT NULL Source of Admission Number
C=Customs and Border Protection
U=US Citizenship and Immigration
45 I9_PASSPORT_NBR Character(15) VARCHAR2(15) NOT NULL Foreign Passport Number
46 I9_COUNTRY_PASSPRT Character(3) VARCHAR2(3) NOT NULL Country of Issuance

Prompt Table: COUNTRY_TBL

47 I9_PASSPORT_COUNTR Character(33) VARCHAR2(33) NOT NULL Foreign Passport Number and Co
48 I9_SPCL_PLACEMENT Character(1) VARCHAR2(1) NOT NULL Special Placement

Y/N Table Edit

Default Value: N

49 I9_MINOR Character(1) VARCHAR2(1) NOT NULL Minor

Y/N Table Edit

Default Value: N

50 I9_TRANSLATOR Character(1) VARCHAR2(1) NOT NULL Translator

Y/N Table Edit

Default Value: N

51 I9_EE_SIGN_DT Date(10) DATE Employee Sign Date
52 NAME_PREPARER Character(50) VARCHAR2(50) NOT NULL Administrator Name
53 FIRST_NM_PREPARER Character(30) VARCHAR2(30) NOT NULL First Name
54 LAST_NM_PREPARER Character(30) VARCHAR2(30) NOT NULL Last Name
55 ADDRESS1_PREPARER Character(55) VARCHAR2(55) NOT NULL Address
56 CITY_PREPARER Character(30) VARCHAR2(30) NOT NULL City
57 STATE_PREPARER Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

58 POSTAL_PREPARER Character(10) VARCHAR2(10) NOT NULL Postal Code
59 COUNTRY_PREPARER Character(3) VARCHAR2(3) NOT NULL Country
CAN=Canada
MEX=Mexico
USA=United States

Default Value: USA

60 I9_PREP_SIGN_DT Date(10) DATE Preparer Sign Date
61 HIRE_DT Date(10) DATE First Start Date
62 REHIRE_DT Date(10) DATE Rehire Date
63 NAME_ADMIN Character(50) VARCHAR2(50) NOT NULL Administrator Name
64 NAME_ADMIN_FIRST Character(30) VARCHAR2(30) NOT NULL Administrator Name
65 NAME_ADMIN_LAST Character(30) VARCHAR2(30) NOT NULL Administrator Name
66 NAME_INITIALS Character(6) VARCHAR2(6) NOT NULL Name Initials
67 TITLE Character(35) VARCHAR2(35) NOT NULL Title
68 I9_ER_SIGN_DT Date(10) DATE Employer Sign Date
69 I9_ER_COMPANY Character(3) VARCHAR2(3) NOT NULL Company
70 ADDRESS1_COMPANY Character(55) VARCHAR2(55) NOT NULL Address
71 ADDRESS2_COMPANY Character(55) VARCHAR2(55) NOT NULL Address
72 CITY_COMPANY Character(30) VARCHAR2(30) NOT NULL City
73 STATE_COMPANY Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

74 POSTAL_COMPANY Character(12) VARCHAR2(12) NOT NULL Postal Code
75 I9_REV_DOC_TITLE Character(2) VARCHAR2(2) NOT NULL Document Title

Prompt Table: I9_IAUTH_REV_VW

76 I9_REV_DOCTITLE Character(50) VARCHAR2(50) NOT NULL Document Title
77 I9_REV_RECEIPT_FLG Character(1) VARCHAR2(1) NOT NULL Receipt

Y/N Table Edit

Default Value: N

78 I9_REV_DOC_NBR Character(30) VARCHAR2(30) NOT NULL Document Number
79 I9_REV_DOC_EXP_DT Date(10) DATE Exp. Date (if any)
80 I9_REV_RECEIPT_NBR Character(30) VARCHAR2(30) NOT NULL Receipt Document Number
81 I9_REV_REC_EXP_DT Date(10) DATE Exp. Date (if any)
82 I9_REVERIFIED_DT Date(10) DATE Reverification Date
83 I9_DOC_TYPE Character(1) VARCHAR2(1) NOT NULL Doc Type
A=List A
B=List B AND List C

Default Value: A

84 I9_DOC_TITLE_A Character(2) VARCHAR2(2) NOT NULL Document Title

Prompt Table: I9_IAUTH_A_VW

85 I9_DOC_TITLE_A2 Character(2) VARCHAR2(2) NOT NULL Document Title

Prompt Table: I9_IAUTH_A2_VW

86 I9_DOC_TITLE_A3 Character(2) VARCHAR2(2) NOT NULL Document Title

Prompt Table: I9_IAUTH_A3_VW

87 I9_DOC_TITLE_B Character(2) VARCHAR2(2) NOT NULL Document Title
1=School Record/Report Card
2=Clinic, Dr. or Hospital Record
3=Day-care/Nursery School Record
K=US Driver's License
L=Government ID Card
M=School ID Card
N=Voter's Registration Card
O=US Military Card
P=Military Dependent's ID Card
Q=US Coast Guard/Mariner Card
R=Native American Tribal Doc
S=Canadian Driver's License
X=Special Placement
Y=Individual under age 18
Z=Special and Minor
88 I9_DOC_TITLE_C Character(2) VARCHAR2(2) NOT NULL Document Title
8=Certification of Birth(FS-545)
9=Certi.Report of Birth(DS-1350)
T=US Social Security Card
T=US Social Security AccNo: Card
U=Certificate of Birth Abroad
V=US Birth Ceritificate
W=Native American Tribal Doc
X=US Citizen ID Card
Y=Resident Citizen ID Card
Z=Employment Authorization Doc
89 I9_DOCTITLE_A Character(50) VARCHAR2(50) NOT NULL Document Title
90 I9_DOCTITLE_A2 Character(50) VARCHAR2(50) NOT NULL Document Title
91 I9_DOCTITLE_A3 Character(50) VARCHAR2(50) NOT NULL Document Title
92 I9_DOCTITLE_B Character(50) VARCHAR2(50) NOT NULL Document Title
93 I9_DOCTITLE_C Character(50) VARCHAR2(50) NOT NULL Document Title
94 I9_ISSUE_AUTH_A Character(50) VARCHAR2(50) NOT NULL Issuing Authority

Prompt Table: I9_IAUTH_A_VW

95 I9_ISSUE_AUTH_A2 Character(50) VARCHAR2(50) NOT NULL Issuing Authority

Prompt Table: I9_IAUTH_A2_VW

96 I9_ISSUE_AUTH_A3 Character(50) VARCHAR2(50) NOT NULL Issuing Authority

Prompt Table: I9_IAUTH_A3_VW

97 I9_ISSUE_AUTH_B Character(50) VARCHAR2(50) NOT NULL Issuing Authority

Prompt Table: I9_IAUTH_B_VW

98 I9_ISSUE_AUTH_C Character(50) VARCHAR2(50) NOT NULL Issuing Authority

Prompt Table: I9_IAUTH_C_VW

99 I9_RECEIPT_FLG_A Character(1) VARCHAR2(1) NOT NULL Receipt

Y/N Table Edit

Default Value: N

100 I9_RECEIPT_FLG_A2 Character(1) VARCHAR2(1) NOT NULL Receipt

Y/N Table Edit

Default Value: N

101 I9_RECEIPT_FLG_A3 Character(1) VARCHAR2(1) NOT NULL Receipt

Y/N Table Edit

Default Value: N

102 I9_RECEIPT_FLG_B Character(1) VARCHAR2(1) NOT NULL Receipt

Y/N Table Edit

Default Value: N

103 I9_RECEIPT_FLG_C Character(1) VARCHAR2(1) NOT NULL Receipt

Y/N Table Edit

Default Value: N

104 I9_RECEIPT_NBR_A1 Character(30) VARCHAR2(30) NOT NULL Receipt Document Number
105 I9_RECEIPT_NBR_A2 Character(30) VARCHAR2(30) NOT NULL Receipt Document Number
106 I9_RECEIPT_NBR_A3 Character(30) VARCHAR2(30) NOT NULL Receipt Document Number
107 I9_RECEIPT_NBR_B Character(30) VARCHAR2(30) NOT NULL Receipt Document Number
108 I9_RECEIPT_NBR_C Character(30) VARCHAR2(30) NOT NULL Receipt Document Number
109 I9_REC_EXP_DT_A1 Date(10) DATE Exp. Date (if any)
110 I9_REC_EXP_DT_A2 Date(10) DATE Exp. Date (if any)
111 I9_REC_EXP_DT_A3 Date(10) DATE Exp. Date (if any)
112 I9_REC_EXP_DT_B Date(10) DATE Exp. Date (if any)
113 I9_REC_EXP_DT_C Date(10) DATE Exp. Date (if any)
114 I9_DOC_NBR_A1 Character(39) VARCHAR2(39) NOT NULL Document Number
115 I9_DOC_NBR_A2 Character(39) VARCHAR2(39) NOT NULL Document Number
116 I9_DOC_NBR_A3 Character(39) VARCHAR2(39) NOT NULL Document Number
117 I9_DOC_NBR_B Character(45) VARCHAR2(45) NOT NULL Document Number
118 I9_DOC_NBR_C Character(39) VARCHAR2(39) NOT NULL Document Number
119 I9_DOC_EXP_DT_A1 Date(10) DATE Exp. Date (if any)
120 I9_DOC_EXP_DT_A2 Date(10) DATE Exp. Date (if any)
121 I9_DOC_EXP_DT_A3 Date(10) DATE Exp. Date (if any)
122 I9_DOC_EXP_DT_B Date(10) DATE Exp. Date (if any)
123 I9_DOC_EXP_DT_C Date(10) DATE Exp. Date (if any)
124 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
125 NAME_AC_FIRST Character(30) VARCHAR2(30) NOT NULL First Name
126 NAME_AC_LAST Character(30) VARCHAR2(30) NOT NULL Last Name
127 NAME_AC_MIDDLE Character(1) VARCHAR2(1) NOT NULL The employee's middle initial
128 NAME_FIELD Character(50) VARCHAR2(50) NOT NULL Employer Representative
129 I9_MSG_TXT Long Character CLOB Message
130 I9_NAME_PREPARER_2 Character(50) VARCHAR2(50) NOT NULL Administrator Name
131 I9_NAME_PREPARER_3 Character(50) VARCHAR2(50) NOT NULL Administrator Name
132 I9_NAME_PREPARER_4 Character(50) VARCHAR2(50) NOT NULL Administrator Name
133 I9_NAME_PREPARER_5 Character(50) VARCHAR2(50) NOT NULL Administrator Name
134 I9_PREP_SIGN_DT_2 Date(10) DATE Preparer Sign Date
135 I9_PREP_SIGN_DT_3 Date(10) DATE Preparer Sign Date
136 I9_PREP_SIGN_DT_4 Date(10) DATE Preparer Sign Date
137 I9_PREP_SIGN_DT_5 Date(10) DATE Preparer Sign Date
138 I9_LAST_NM_PREP_2 Character(30) VARCHAR2(30) NOT NULL Last Name
139 I9_LAST_NM_PREP_3 Character(30) VARCHAR2(30) NOT NULL Last Name
140 I9_LAST_NM_PREP_4 Character(30) VARCHAR2(30) NOT NULL Last Name
141 I9_LAST_NM_PREP_5 Character(30) VARCHAR2(30) NOT NULL Last Name
142 I9_FIRST_NM_PREP_2 Character(30) VARCHAR2(30) NOT NULL First Name
143 I9_FIRST_NM_PREP_3 Character(30) VARCHAR2(30) NOT NULL First Name
144 I9_FIRST_NM_PREP_4 Character(30) VARCHAR2(30) NOT NULL First Name
145 I9_FIRST_NM_PREP_5 Character(30) VARCHAR2(30) NOT NULL First Name
146 I9_MID_NM_PREP_2 Character(1) VARCHAR2(1) NOT NULL The employee's middle initial
147 I9_MID_NM_PREP_3 Character(1) VARCHAR2(1) NOT NULL The employee's middle initial
148 I9_MID_NM_PREP_4 Character(1) VARCHAR2(1) NOT NULL The employee's middle initial
149 I9_MID_NM_PREP_5 Character(1) VARCHAR2(1) NOT NULL The employee's middle initial
150 I9_ADDRESS1_PREP_2 Character(55) VARCHAR2(55) NOT NULL Address
151 I9_ADDRESS1_PREP_3 Character(55) VARCHAR2(55) NOT NULL Address
152 I9_ADDRESS1_PREP_4 Character(55) VARCHAR2(55) NOT NULL Address
153 I9_ADDRESS1_PREP_5 Character(55) VARCHAR2(55) NOT NULL Address
154 I9_CITY_PREP_2 Character(30) VARCHAR2(30) NOT NULL City
155 I9_CITY_PREP_3 Character(30) VARCHAR2(30) NOT NULL City
156 I9_CITY_PREP_4 Character(30) VARCHAR2(30) NOT NULL City
157 I9_CITY_PREP_5 Character(30) VARCHAR2(30) NOT NULL City
158 I9_STATE_PREP_2 Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

159 I9_STATE_PREP_3 Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

160 I9_STATE_PREP_4 Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

161 I9_STATE_PREP_5 Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

162 I9_COUNTRY_PREP_2 Character(3) VARCHAR2(3) NOT NULL Country
CAN=Canada
MEX=Mexico
USA=USA
163 I9_COUNTRY_PREP_3 Character(3) VARCHAR2(3) NOT NULL Country
CAN=Canada
MEX=Mexico
USA=USA
164 I9_COUNTRY_PREP_4 Character(3) VARCHAR2(3) NOT NULL Country
CAN=Canada
MEX=Mexico
USA=USA
165 I9_COUNTRY_PREP_5 Character(3) VARCHAR2(3) NOT NULL Country
CAN=Canada
MEX=Mexico
USA=USA
166 I9_POSTAL_PREP_2 Character(10) VARCHAR2(10) NOT NULL Postal Code
167 I9_POSTAL_PREP_3 Character(10) VARCHAR2(10) NOT NULL Postal Code
168 I9_POSTAL_PREP_4 Character(10) VARCHAR2(10) NOT NULL Postal Code
169 I9_POSTAL_PREP_5 Character(10) VARCHAR2(10) NOT NULL Postal Code
170 I9_PREP_COUNT Character(1) VARCHAR2(1) NOT NULL How Many
1=1
2=2
3=3
4=4
5=5
171 I9_PREP_CHK Character(1) VARCHAR2(1) NOT NULL Preparer CheckBox
172 I9_LIST_C7_TITLE Character(30) VARCHAR2(30) NOT NULL List C#7 Title
173 I9_EXP_DS Character(3) VARCHAR2(3) NOT NULL Expiration Date D/S
174 I9_VERIFY_DOC Character(1) VARCHAR2(1) NOT NULL E Verify Documents
N=No
Y=Yes
175 I9_REVERIFY_DOC Character(1) VARCHAR2(1) NOT NULL E Verify Documents
N=No
Y=Yes
176 I9_ADMIN_NAME_TITL Character(50) VARCHAR2(50) NOT NULL Administrator Name
177 MIDDLE_NM_PREPARER Character(1) VARCHAR2(1) NOT NULL The employee's middle initial
178 I9_REV_MSG_TXT Long Character CLOB Message
179 I9_NAME_AC_LAST_2 Character(30) VARCHAR2(30) NOT NULL Last Name
180 I9_NAME_AC_LAST_3 Character(30) VARCHAR2(30) NOT NULL Last Name
181 I9_NAME_AC_FIRST_2 Character(30) VARCHAR2(30) NOT NULL First Name
182 I9_NAME_AC_FIRST_3 Character(30) VARCHAR2(30) NOT NULL First Name
183 I9_NAME_AC_MIDDLE2 Character(1) VARCHAR2(1) NOT NULL The employee's middle initial
184 I9_NAME_AC_MIDDLE3 Character(1) VARCHAR2(1) NOT NULL The employee's middle initial
185 I9_REV_DOCTITLE2 Character(50) VARCHAR2(50) NOT NULL Document Title

Prompt Table: I9_IAUTH_REV_VW

186 I9_REV_DOCTITLE3 Character(50) VARCHAR2(50) NOT NULL Document Title

Prompt Table: I9_IAUTH_REV_VW

187 I9_REV_DOC_NBR2 Character(30) VARCHAR2(30) NOT NULL Document Number
188 I9_REV_DOC_NBR3 Character(30) VARCHAR2(30) NOT NULL Document Number
189 I9_REV_DOC_EXP_DT2 Date(10) DATE Exp. Date (if any)
190 I9_REV_DOC_EXP_DT3 Date(10) DATE Exp. Date (if any)
191 I9_REV_MSG_TXT2 Long Character CLOB Message
192 I9_REV_MSG_TXT3 Long Character CLOB Message
193 I9_REVERIFY_DOC2 Character(1) VARCHAR2(1) NOT NULL E Verify Documents
N=No
Y=Yes
194 I9_REVERIFY_DOC3 Character(1) VARCHAR2(1) NOT NULL E Verify Documents
N=No
Y=Yes
195 I9_REVERIFIED_DT_2 Date(10) DATE Reverification Date
196 I9_REVERIFIED_DT_3 Date(10) DATE Reverification Date
197 I9_REV_SIGNATURE2 Character(1) VARCHAR2(1) NOT NULL Employer Signature
S=Signature
198 I9_REV_SIGNATURE3 Character(1) VARCHAR2(1) NOT NULL Employer Signature
S=Signature
199 I9_NAME_FIELD2 Character(50) VARCHAR2(50) NOT NULL Name Field
200 I9_NAME_FIELD3 Character(50) VARCHAR2(50) NOT NULL Name Field
201 I9_REHIRE_DT_2 Date(10) DATE Rehire Date
202 I9_REHIRE_DT_3 Date(10) DATE Rehire Date
203 I9_STATUS Character(1) VARCHAR2(1) NOT NULL I9 status
A=Approved
D=Denied
I=Draft
M=Pushed Back
P=Pending Approval
Q=I9 Verified- Not Approved
R=I9 Reverified
S=Submitted
T=New Document - Pending Approva
U=New Document Submitted
V=Completed
X=Not Started