|
SELECT A.HRS_PERSON_ID , B.HRS_SUBMITTED_DTTM , A.EMPLID , %subrec(HRS_NAMEGBL_SBR,E) , %subrec(ADDRESS_SBR,F) , A.STATUS_CODE , A.STATUS_REASON , A.STATUS_DT , A.APP_PER_STATUS , B.APPLIC_PURGE_DT , A.SEX , C.MAR_STATUS , C.MAR_STATUS_DT , A.BIRTHDATE , A.BIRTHPLACE , A.BIRTHCOUNTRY , A.BIRTHSTATE , B.HIGHEST_EDUC_LVL , F.ADDRESS1_AC , F.ADDRESS2_AC , F.ADDRESS3_AC , B.LANG_CD , F.CITY_AC , B.YEARS_OF_EXP , B.HRS_REPORT_DEFN_ID , B.HRS_REPORT_TMPL_ID , B.LETTER_PRINTED_DT , B.TRAVELLING , B.RELOCATE , B.UNION_CD , B.DESIRED_HOURS , B.MIN_RATE , B.CURRENCY_CD , B.DESIRED_START_DT , B.DESIRED_FULL_PART , B.DESIRED_REG_TEMP , B.DESIRED_SHIFT , B.MON , B.TUES , B.WED , B.THURS , B.FRI , B.SAT , B.SUN , B.BUSINESS_UNIT , B.HRS_DES_LOC1 , B.HRS_DES_LOC2 , B.PAY_FREQ_ABBRV , B.HRS_TRAVEL_PERCENT , B.GEOG_PREFER , D.OPEN_STS_IND FROM PS_HRS_APPLICANT A ,PS_HRS_APP_PROFILE B ,PS_HRS_APP_EFFDT C ,PS_HRS_STS_APP_I D ,PS_HRS_APP_NAMES E ,PS_HRS_APP_ADDRESS F WHERE B.HRS_PERSON_ID = A.HRS_PERSON_ID AND C.HRS_PERSON_ID = B.HRS_PERSON_ID AND E.HRS_PERSON_ID = C.HRS_PERSON_ID AND F.HRS_PERSON_ID = C.HRS_PERSON_ID AND B.HRS_SUBMITTED_DTTM = ( SELECT MAX(D1.HRS_SUBMITTED_DTTM) FROM PS_HRS_APP_PROFILE D1 WHERE D1.HRS_PERSON_ID = B.HRS_PERSON_ID) AND C.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_HRS_APP_EFFDT E WHERE E.HRS_PERSON_ID = C.HRS_PERSON_ID) AND D.STATUS_CODE = A.STATUS_CODE
|
| # |
PeopleSoft Field Name |
PeopleSoft Field Type |
Database Column Type |
Description |
| 1 |
 HRS_PERSON_ID |
Number(15,0) |
DECIMAL(15) NOT NULL |
Person Id
|
| 2 |
HRS_SUBMITTED_DTTM |
DateTime(26) |
TIMESTAMP |
Submitted Date Time
|
| 3 |
EMPLID |
Character(11) |
VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table:
PERSON
|
| 4 |
COUNTRY_NM_FORMAT |
Character(3) |
VARCHAR2(3) NOT NULL |
Supported Name Format Types
Default Value: 001
Prompt Table:
NAME_FORMAT_TBL
|
| 5 |
NAME |
Character(50) |
VARCHAR2(50) NOT NULL |
Name
|
| 6 |
NAME_INITIALS |
Character(6) |
VARCHAR2(6) NOT NULL |
Name Initials
|
| 7 |
NAME_PREFIX |
Character(4) |
VARCHAR2(4) NOT NULL |
Name Prefix
Prompt Table:
NAME_PREFIX_TBL
|
| 8 |
NAME_SUFFIX |
Character(15) |
VARCHAR2(15) NOT NULL |
Name Suffix
Prompt Table:
NAME_SUFFIX_TBL
|
| 9 |
NAME_ROYAL_PREFIX |
Character(15) |
VARCHAR2(15) NOT NULL |
Name Royal Prefix
Prompt Table:
NM_ROYPREF_GBL
|
| 10 |
NAME_ROYAL_SUFFIX |
Character(15) |
VARCHAR2(15) NOT NULL |
Name - Royal Suffix
Prompt Table:
NM_ROYSUFF_GBL
|
| 11 |
NAME_TITLE |
Character(30) |
VARCHAR2(30) NOT NULL |
Name Title
Prompt Table:
TITLE_TBL
|
| 12 |
MIDDLE_NAME_SRCH |
Character(30) |
VARCHAR2(30) NOT NULL |
Middle Name uppercase search field
|
| 13 |
LAST_NAME_SRCH |
Character(30) |
VARCHAR2(30) NOT NULL |
Last Name
|
| 14 |
FIRST_NAME_SRCH |
Character(30) |
VARCHAR2(30) NOT NULL |
First Name
|
| 15 |
LAST_NAME |
Character(30) |
VARCHAR2(30) NOT NULL |
Last Name
|
| 16 |
FIRST_NAME |
Character(30) |
VARCHAR2(30) NOT NULL |
First Name
|
| 17 |
MIDDLE_NAME |
Character(30) |
VARCHAR2(30) NOT NULL |
MIDDLE_NAME
|
| 18 |
SECOND_LAST_NAME |
Character(30) |
VARCHAR2(30) NOT NULL |
Second Last Name
|
| 19 |
SECOND_LAST_SRCH |
Character(30) |
VARCHAR2(30) NOT NULL |
Second Last Name
|
| 20 |
NAME_AC |
Character(50) |
VARCHAR2(50) NOT NULL |
Alternate Character Name
|
| 21 |
PREF_FIRST_NAME |
Character(30) |
VARCHAR2(30) NOT NULL |
Preferred First Name
|
| 22 |
PREF_LAST_NAME |
Character(30) |
VARCHAR2(30) NOT NULL |
Preferred Last Name
|
| 23 |
PREF_MIDDLE_NAME |
Character(30) |
VARCHAR2(30) NOT NULL |
Preferred Middle Name
|
| 24 |
PREF_SEC_LAST_NAME |
Character(30) |
VARCHAR2(30) NOT NULL |
Preferred Second Last Name
|
| 25 |
PREF_NAME_PREFIX |
Character(4) |
VARCHAR2(4) NOT NULL |
Preferred Name Prefix
Prompt Table:
NAME_PREFIX_TBL
|
| 26 |
PREF_NAME_SUFFIX |
Character(15) |
VARCHAR2(15) NOT NULL |
Preferred Name Suffix
Prompt Table:
NAME_SUFFIX_TBL
|
| 27 |
PREF_NAME_TITLE |
Character(30) |
VARCHAR2(30) NOT NULL |
Preferred Name Title
Prompt Table:
TITLE_TBL
|
| 28 |
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.
|
| 29 |
PARTN_LAST_NM_SRCH |
Character(30) |
VARCHAR2(30) NOT NULL |
Uppercase version of partner last name to improve performance on searches.
|
| 30 |
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
|
| 31 |
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
|
| 32 |
NAME_DISPLAY |
Character(50) |
VARCHAR2(50) NOT NULL |
Display Name - name formatted for Display based on the Country
|
| 33 |
NAME_FORMAL |
Character(60) |
VARCHAR2(60) NOT NULL |
Formal Name - name formatted for Formal Display by COuntry. Normally used in Correspondence.
|
| 34 |
NAME_FORMAL_PSF |
Character(60) |
VARCHAR2(60) NOT NULL |
Preferred Name (PS FORMAT)
|
| 35 |
PREF_FIRST_NM_SRCH |
Character(30) |
VARCHAR2(30) NOT NULL |
Preferred First Name
|
| 36 |
PREF_LAST_NM_SRCH |
Character(30) |
VARCHAR2(30) NOT NULL |
Uppercase version of preferred last name to improve performance on searches.
|
| 37 |
PREF_MIDL_NM_SRCH |
Character(30) |
VARCHAR2(30) NOT NULL |
Uppercase version of preferred middle name to improve performance on searches.
|
| 38 |
PREF_LAST_SEC_SRCH |
Character(30) |
VARCHAR2(30) NOT NULL |
Uppercase version of preferred second last name to improve performance on searches.
|
| 39 |
NAME_FORMAL_SRCH |
Character(60) |
VARCHAR2(60) NOT NULL |
Uppercase version of formal name to improve performance on searches.
|
| 40 |
NAME_DISPLAY_SRCH |
Character(50) |
VARCHAR2(50) NOT NULL |
Uppercase version of Name to improve performance on searches.
|
| 41 |
COUNTRY |
Character(3) |
VARCHAR2(3) NOT NULL |
Country
Prompt Table:
COUNTRY_TBL
|
| 42 |
ADDRESS1 |
Character(55) |
VARCHAR2(55) NOT NULL |
Address 1
|
| 43 |
ADDRESS2 |
Character(55) |
VARCHAR2(55) NOT NULL |
Address 2
|
| 44 |
ADDRESS3 |
Character(55) |
VARCHAR2(55) NOT NULL |
Address 3
|
| 45 |
ADDRESS4 |
Character(55) |
VARCHAR2(55) NOT NULL |
Address 4
|
| 46 |
CITY |
Character(30) |
VARCHAR2(30) NOT NULL |
City
|
| 47 |
NUM1 |
Character(6) |
VARCHAR2(6) NOT NULL |
Number 1
|
| 48 |
NUM2 |
Character(6) |
VARCHAR2(6) NOT NULL |
Number 2
|
| 49 |
HOUSE_TYPE |
Character(2) |
VARCHAR2(2) NOT NULL |
House Type
AB=House Boat Reference
WW=House Trailer Reference
|
| 50 |
ADDR_FIELD1 |
Character(2) |
VARCHAR2(2) NOT NULL |
Address Field 1
|
| 51 |
ADDR_FIELD2 |
Character(4) |
VARCHAR2(4) NOT NULL |
Address Field 2
|
| 52 |
ADDR_FIELD3 |
Character(4) |
VARCHAR2(4) NOT NULL |
Address Field 3
|
| 53 |
COUNTY |
Character(30) |
VARCHAR2(30) NOT NULL |
County
|
| 54 |
STATE |
Character(6) |
VARCHAR2(6) NOT NULL |
State
Prompt Table:
STATE_TBL
|
| 55 |
POSTAL |
Character(12) |
VARCHAR2(12) NOT NULL |
Postal
|
| 56 |
GEO_CODE |
Character(11) |
VARCHAR2(11) NOT NULL |
Geo Code
|
| 57 |
IN_CITY_LIMIT |
Character(1) |
VARCHAR2(1) NOT NULL |
In City Limit
Y/N Table Edit
|
| 58 |
STATUS_CODE |
Character(3) |
VARCHAR2(3) NOT NULL |
Identifies a status.
Prompt Table:
HRS_STS_APP_I
|
| 59 |
STATUS_REASON |
Character(3) |
VARCHAR2(3) NOT NULL |
Identifies a reason associated with a particular status.
Prompt Table:
HRS_RSN_APP_I
|
| 60 |
STATUS_DT |
Date(10) |
DATE |
Status Date
Default Value: %date
|
| 61 |
APP_PER_STATUS |
Character(1) |
VARCHAR2(1) NOT NULL |
Identifies the type of applicant - External Applicant, Employee or Non-Employee
A=External Applicant
E=Employee
N=Non-Employee
Default Value: A
|
| 62 |
APPLIC_PURGE_DT |
Date(10) |
DATE |
Store date by which applicant records are to be deleted as part of the applicant purge process
|
| 63 |
SEX |
Character(1) |
VARCHAR2(1) NOT NULL |
Gender
F=Female
M=Male
U=Unknown
Default Value: U
|
| 64 |
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
|
| 65 |
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. */
|
| 66 |
BIRTHDATE |
Date(10) |
DATE |
Date of Birth
|
| 67 |
BIRTHPLACE |
Character(30) |
VARCHAR2(30) NOT NULL |
Birth Location
|
| 68 |
BIRTHCOUNTRY |
Character(3) |
VARCHAR2(3) NOT NULL |
Birth Country
Prompt Table:
COUNTRY_TBL
|
| 69 |
BIRTHSTATE |
Character(6) |
VARCHAR2(6) NOT NULL |
Birth State
Prompt Table:
BIRTHSTATE_VW
|
| 70 |
HIGHEST_EDUC_LVL |
Character(2) |
VARCHAR2(2) NOT NULL |
Highest Education Level
Default Value: A
|
| 71 |
ADDRESS1_AC |
Character(55) |
VARCHAR2(55) NOT NULL |
Alternate Character Address 1
|
| 72 |
ADDRESS2_AC |
Character(55) |
VARCHAR2(55) NOT NULL |
Alternate Character Address 2
|
| 73 |
ADDRESS3_AC |
Character(55) |
VARCHAR2(55) NOT NULL |
Alternate Character Address 3
|
| 74 |
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
|
| 75 |
CITY_AC |
Character(30) |
VARCHAR2(30) NOT NULL |
Alternate Character City
|
| 76 |
YEARS_OF_EXP |
Number(5,1) |
DECIMAL(4,1) NOT NULL |
Years of Work Experience
Default Value: 0
|
| 77 |
HRS_REPORT_DEFN_ID |
Character(12) |
VARCHAR2(12) NOT NULL |
The Fusion Publisher Report Definition ID used by Recruiting
|
| 78 |
HRS_REPORT_TMPL_ID |
Character(30) |
VARCHAR2(30) NOT NULL |
The Fusion publisher Report template ID used by RS.
|
| 79 |
LETTER_PRINTED_DT |
Date(10) |
DATE |
Date letter was printed
|
| 80 |
TRAVELLING |
Character(1) |
VARCHAR2(1) NOT NULL |
Willing to Travel
N=No
Y=Yes
Y/N Table Edit
Default Value: N
|
| 81 |
RELOCATE |
Character(1) |
VARCHAR2(1) NOT NULL |
Willing to Relocate
N=No
Y=Yes
Y/N Table Edit
Default Value: N
|
| 82 |
UNION_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Union Code
|
| 83 |
DESIRED_HOURS |
Number(4,1) |
DECIMAL(3,1) NOT NULL |
Desired Hours Per Week
|
| 84 |
MIN_RATE |
Number(19,6) |
DECIMAL(18,6) NOT NULL |
Minimum Acceptable Pay Rate
|
| 85 |
CURRENCY_CD |
Character(3) |
VARCHAR2(3) NOT NULL |
Currency Code
|
| 86 |
DESIRED_START_DT |
Date(10) |
DATE |
Desired Start Date
|
| 87 |
DESIRED_FULL_PART |
Character(1) |
VARCHAR2(1) NOT NULL |
Full/Part-Time
E=Either
F=Full-Time
P=Part-Time
|
| 88 |
DESIRED_REG_TEMP |
Character(1) |
VARCHAR2(1) NOT NULL |
Regular/Temporary
E=Either
R=Regular
T=Temporary
|
| 89 |
DESIRED_SHIFT |
Character(1) |
VARCHAR2(1) NOT NULL |
Desired Shift
1=1
2=2
3=3
A=Any
C=Compressed
N=Not Applicable
R=Rotating
|
| 90 |
MON |
Character(1) |
VARCHAR2(1) NOT NULL |
Monday
Default Value: Y
|
| 91 |
TUES |
Character(1) |
VARCHAR2(1) NOT NULL |
Tuesday
Default Value: Y
|
| 92 |
WED |
Character(1) |
VARCHAR2(1) NOT NULL |
Wednesday
Default Value: Y
|
| 93 |
THURS |
Character(1) |
VARCHAR2(1) NOT NULL |
Thursday
Default Value: Y
|
| 94 |
FRI |
Character(1) |
VARCHAR2(1) NOT NULL |
Friday
Default Value: Y
|
| 95 |
SAT |
Character(1) |
VARCHAR2(1) NOT NULL |
Saturday
|
| 96 |
SUN |
Character(1) |
VARCHAR2(1) NOT NULL |
Sunday
|
| 97 |
BUSINESS_UNIT |
Character(5) |
VARCHAR2(5) NOT NULL |
Business Unit
|
| 98 |
HRS_DES_LOC1 |
Number(15,0) |
DECIMAL(15) NOT NULL |
Desired Location 1
Prompt Table:
HRS_LOC_I
|
| 99 |
HRS_DES_LOC2 |
Number(15,0) |
DECIMAL(15) NOT NULL |
Desired Location 2
Prompt Table:
HRS_LOC_I
|
| 100 |
PAY_FREQ_ABBRV |
Character(1) |
VARCHAR2(1) NOT NULL |
Pay Frequency
A=Annual
B=Bi-weeky
H=Hour
M=Month
Y=Year
|
| 101 |
HRS_TRAVEL_PERCENT |
Character(1) |
VARCHAR2(1) NOT NULL |
Travel percent
0=Never or rarely
1=Up to 25% of the time
2=Up to 50% of the time
3=Up to 75% of the time
4=Up to 100% of the time
|
| 102 |
GEOG_PREFER |
Long Character |
CLOB |
Geographic Preference
|
| 103 |
OPEN_STS_IND |
Character(1) |
VARCHAR2(1) NOT NULL |
Indicates an open state.
|