SCC_PERUSA_VW

(SQL View)
 Index Back

Person USA View

Person USA View used by personal data view SCC_PERDATA_VW.

SELECT P.EMPLID , U.US_WORK_ELIGIBILTY , U.MILITARY_STATUS , U.CITIZEN_PROOF1 , U.CITIZEN_PROOF2 , U.MEDICARE_ENTLD_DT FROM PS_PERSON P , PS_PERS_DATA_USA U , PS_PERSON_DT_SETUP S , PS_INSTALLATION I WHERE U.EMPLID = P.EMPLID AND %EffdtCheck(PERS_DATA_USA U2, U, %CURRENTDATEIN) AND S.COUNTRY_FLG = 'Y' AND I.US = 'Y' UNION SELECT PX.EMPLID , 'Y' , '1' , ' ' , ' ' , %DateNull FROM PS_PERSON PX WHERE NOT EXISTS ( SELECT 'X' FROM PS_PERS_DATA_USA UX , PS_PERSON_DT_SETUP SX , PS_INSTALLATION IX WHERE UX.EMPLID = PX.EMPLID AND %EffdtCheck(PERS_DATA_USA UX2, UX, %CURRENTDATEIN) AND SX.COUNTRY_FLG = 'Y' AND IX.US = 'Y' )

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

Default Value: PERSON.EMPLID

Prompt Table: PERSON

2 US_WORK_ELIGIBILTY Character(1) VARCHAR2(1) NOT NULL Eligible to Work in U.S.

Y/N Table Edit

Default Value: Y

3 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
4 CITIZEN_PROOF1 Character(10) VARCHAR2(10) NOT NULL Citizenship (Proof 1)
5 CITIZEN_PROOF2 Character(10) VARCHAR2(10) NOT NULL Citizenship (Proof 2)
6 MEDICARE_ENTLD_DT Date(10) DATE Date Entitled to Medicare