HRS_APP_CURR_VW

(SQL View)
 Index Back

Search Vw-Applicant/EE

The APPLICANT_DATA view is a search record used in Recruitment to access applicant records (employees and outside applicants).

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 Title

Prompt Table: TITLE_TBL

12 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
13 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
14 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
15 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
16 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME
17 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
18 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Name
19 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
20 PREF_FIRST_NAME Character(30) VARCHAR2(30) NOT NULL Preferred First Name
21 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.
22 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

23 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

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

Prompt Table: SCC_CNT_ADFMTVW

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

Prompt Table: STATE_TBL

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

Y/N Table Edit

43 STATUS_CODE Character(3) VARCHAR2(3) NOT NULL Identifies a status.

Prompt Table: HRS_STS_APP_I

44 STATUS_REASON Character(3) VARCHAR2(3) NOT NULL Identifies a reason associated with a particular status.

Prompt Table: HRS_RSN_APP_I

45 STATUS_DT Date(10) DATE Status Date

Default Value: %date

46 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

47 APPLIC_PURGE_DT Date(10) DATE Store date by which applicant records are to be deleted as part of the applicant purge process
48 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec

Default Value: U

49 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
50 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. */
51 BIRTHDATE Date(10) DATE Date of Birth
52 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
53 BIRTHCOUNTRY Character(3) VARCHAR2(3) NOT NULL Birth Country

Prompt Table: COUNTRY_TBL

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

Prompt Table: BIRTHSTATE_VW

55 HIGHEST_EDUC_LVL Character(2) VARCHAR2(2) NOT NULL Highest Education Level

Default Value: A

56 ADDRESS1_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 1
57 ADDRESS2_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 2
58 ADDRESS3_AC Character(55) VARCHAR2(55) NOT NULL Alternate Character Address 3
59 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
60 CITY_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character City
61 YEARS_OF_EXP Number(5,1) DECIMAL(4,1) NOT NULL Years of Work Experience

Default Value: 0

62 HRS_REPORT_DEFN_ID Character(12) VARCHAR2(12) NOT NULL The Fusion Publisher Report Definition ID used by Recruiting
63 HRS_REPORT_TMPL_ID Character(30) VARCHAR2(30) NOT NULL The Fusion publisher Report template ID used by RS.
64 LETTER_PRINTED_DT Date(10) DATE Date letter was printed
65 TRAVELLING Character(1) VARCHAR2(1) NOT NULL Willing to Travel

Y/N Table Edit

Default Value: N

66 RELOCATE Character(1) VARCHAR2(1) NOT NULL Willing to Relocate

Y/N Table Edit

Default Value: N

67 UNION_CD Character(3) VARCHAR2(3) NOT NULL Union Code
68 DESIRED_HOURS Number(4,1) DECIMAL(3,1) NOT NULL Desired Hours Per Week
69 MIN_RATE Number(19,6) DECIMAL(18,6) NOT NULL Minimum Acceptable Pay Rate
70 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
71 DESIRED_START_DT Date(10) DATE Desired Start Date
72 DESIRED_FULL_PART Character(1) VARCHAR2(1) NOT NULL Full/Part-Time
E=Either
F=Full-Time
P=Part-Time
73 DESIRED_REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
E=Either
R=Regular
T=Temporary
74 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
75 MON Character(1) VARCHAR2(1) NOT NULL Monday

Default Value: Y

76 TUES Character(1) VARCHAR2(1) NOT NULL Tuesday

Default Value: Y

77 WED Character(1) VARCHAR2(1) NOT NULL Wednesday

Default Value: Y

78 THURS Character(1) VARCHAR2(1) NOT NULL Thursday

Default Value: Y

79 FRI Character(1) VARCHAR2(1) NOT NULL Friday

Default Value: Y

80 SAT Character(1) VARCHAR2(1) NOT NULL Saturday
81 SUN Character(1) VARCHAR2(1) NOT NULL Sunday
82 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
83 HRS_DES_LOC1 Number(15,0) DECIMAL(15) NOT NULL Desired Location 1

Prompt Table: HRS_LOC_I

84 HRS_DES_LOC2 Number(15,0) DECIMAL(15) NOT NULL Desired Location 2

Prompt Table: HRS_LOC_I

85 PAY_FREQ_ABBRV Character(1) VARCHAR2(1) NOT NULL Pay Frequency
A=Annual
B=Bi-weeky
H=Hour
M=Month
Y=Year
86 HRS_TRAVEL_PERCENT Character(1) VARCHAR2(1) NOT NULL Travel percent
0=None
1=<= 25%
2=26 - 50 %
3=51 - 75 %
4=76 - 100 %
87 GEOG_PREFER Long Character CLOB Geographic Preference
88 OPEN_STS_IND Character(1) VARCHAR2(1) NOT NULL Indicates an open state.