HR_RWORK_VW2 (SQL View)	Index Back
Remote work Details This view holds the active employees job and remote request details

HR_RWORK_VW2

(SQL View)

Remote work Details

This view holds the active employees job and remote request details

SELECT A.emplid ,A.empl_rcd ,A.EFFDT ,A.EFFSEQ ,R.start_date ,A.PER_ORG ,A.DEPTID ,A.POSITION_NBR ,A.LOCATION ,A.REG_TEMP ,A.FULL_PART_TIME ,A.COMPANY ,A.PAYGROUP ,A.BUSINESS_UNIT ,A.SETID_DEPT ,A.SETID_JOBCODE ,A.SETID_LOCATION ,A.REG_REGION ,A.GP_PAYGROUP ,A.PAY_SYSTEM_FLG ,A.ESTABID ,A.ABSENCE_SYSTEM_CD ,A.SUPERVISOR_ID ,A.REPORTS_TO ,A.ACTION ,A.ACTION_REASON ,A.HR_STATUS ,A.EMPL_STATUS ,A.EMPL_TYPE ,A.FLSA_status ,R.end_date ,R.days ,R.hr_rw_type ,R.hr_rw_reason ,R.hr_rw_loc_type ,CASE WHEN hr_rw_loc_type ='W' THEN( SELECT DESCR FROM PS_LOCATION_TBL L WHERE R.LOCATION=L.LOCATION AND L.SETID=R.SETID_LOCATION AND L.EFFDT=( SELECT MAX(L1.EFFDT) FROM PS_LOCATION_TBL L1 WHERE L.SETID = L1.SETID AND L.LOCATION = L1.LOCATION AND L1.EFFDT <= %CurrentDateIn) ) ELSE 'Home/Others' END ,R.rw_percent , CASE WHEN RW_PERCENT>0 AND RW_PERCENT <=20 THEN '1%-20%' WHEN RW_PERCENT >20 AND RW_PERCENT<=40 THEN '21%-40%' WHEN RW_PERCENT >40 AND RW_PERCENT<=60 THEN '41%-60%' WHEN RW_PERCENT >60 AND RW_PERCENT<=80 THEN '61%-80%' WHEN RW_PERCENT >80 AND RW_PERCENT <=99.99 THEN '81%-99.99%' WHEN R.hr_rw_type ='F' THEN '100%' ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE message_set_nbr='1000' AND message_nbr='3051' ) END , CASE WHEN HR_RW_TYPE='H' THEN ( SELECT XLATLONGNAME FROM PSXLATITEM A WHERE FIELDNAME = 'HR_RW_TYPE' AND FIELDVALUE = 'H' AND EFFDT = ( SELECT MAX(EFFDT) FROM PSXLATITEM AA WHERE A.FIELDNAME = AA.FIELDNAME AND A.FIELDVALUE = AA.FIELDVALUE AND AA.EFFDT <= %CurrentDateIn) ) WHEN HR_RW_TYPE='F' THEN ( SELECT XLATLONGNAME FROM PSXLATITEM A WHERE FIELDNAME = 'HR_RW_TYPE' AND FIELDVALUE = 'F' AND EFFDT = ( SELECT MAX(EFFDT) FROM PSXLATITEM AA WHERE A.FIELDNAME = AA.FIELDNAME AND A.FIELDVALUE = AA.FIELDVALUE AND AA.EFFDT <= %CurrentDateIn) ) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE message_set_nbr='1000' AND message_nbr='3050' ) END ,HR_RW_TRACK_DAYS , R.RW_DAY1 ,R.RW_DAY2 ,R.RW_DAY3 ,R.RW_DAY4 ,R.RW_DAY5 ,R.RW_DAY6 ,R.RW_DAY7 ,WF_STATUS ,RW_SS_STATUS , CASE WHEN R.LAST_UPDATE_DTTM IS NOT NULL AND R.LAST_UPDATE_DTTM >= A.LASTUPDDTTM THEN R.LAST_UPDATE_DTTM WHEN R.LAST_UPDATE_DTTM IS NOT NULL AND A.LASTUPDDTTM >= R.LAST_UPDATE_DTTM THEN A.LASTUPDDTTM WHEN R.LAST_UPDATE_DTTM IS NULL THEN A.LASTUPDDTTM END ,A.JOBCODE FROM ((PS_JOB A LEFT OUTER JOIN PS_HR_RWORK_TBL R ON A.EMPLID=R.EMPLID AND A.EMPL_RCD=R.EMPL_RCD )) WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) AND A.HR_STATUS='A'

Related Language Record: HR_RWORK_LVW

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Default Value: PER_ORG_ASGN.EMPLID
Prompt Table: PERS_SRCH_GBL

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

3 EFFDT Date(10) DATE Effective Date
Default Value: %date

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

5 START_DATE Date(10) DATE NOT NULL Start Date for Gen Standing PO

6 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
Default Value: PER_ORG_ASGN.PER_ORG

7 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: DEPT_TBL

8 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
Prompt Table: POSN_DATA_VW

9 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

10 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
R=Regular
T=Temporary
Default Value: R

11 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
Default Value: F

12 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
Prompt Table: COMPANY_TBL

13 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
Prompt Table: %EDITTABLE5

14 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT
Prompt Table: BUSUNIT_HR_VW

15 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
Default Value: OPR_DEF_TBL_HR.SETID

16 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
Default Value: OPR_DEF_TBL_HR.SETID

17 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID

18 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
Default Value: OPR_DEF_TBL_HR.REG_REGION
Prompt Table: REG_STANDARD_VW

19 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group

20 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
GP=Global Payroll
NA=Payroll for North America
OT=Other
PI=Payroll Interface
Default Value: OPR_DEF_TBL_HR.PAY_SYSTEM_FLG

21 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
Prompt Table: %EDITTABLE3

22 ABSENCE_SYSTEM_CD Character(3) VARCHAR2(3) NOT NULL What Absence System this Assignment is tied to.
AM=Absence Management
OT=Other
Default Value: OT

23 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

24 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number

25 ACTION Character(3) VARCHAR2(3) NOT NULL Action

26 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

27 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive

28 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration

29 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried

30 FLSA_STATUS Character(1) VARCHAR2(1) NOT NULL FLSA Status
A=Administrative
C=Computer Employee
E=Executive
H=Highly Compensated Employees
M=Management
N=Nonexempt
O=Outside Salesperson
P=Professional
V=Nonexempt Alt Overtime
X=No FLSA Required

31 END_DATE Date(10) DATE End Date

32 DAYS Number(3,0) SMALLINT NOT NULL Days

33 HR_RW_TYPE Character(1) VARCHAR2(1) NOT NULL Remote Worker Type(Fully remote/Hybrid)
F=Fully Remote
H=Hybrid

34 HR_RW_REASON Character(60) VARCHAR2(60) NOT NULL Remote Worker Reason

35 HR_RW_LOC_TYPE Character(1) VARCHAR2(1) NOT NULL Remote Worker Location Type
H=Home
O=Other
W=Workplace

36 HR_RW_LOCATION Character(30) VARCHAR2(30) NOT NULL Remote Work Location

37 RW_PERCENT Number(6,2) DECIMAL(5,2) NOT NULL Percentage

38 RW_PERCENT_DESCR Character(30) VARCHAR2(30) NOT NULL Remote Work Percent

39 RW_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Remote Worker Type

40 HR_RW_TRACK_DAYS Character(1) VARCHAR2(1) NOT NULL Remote Worker Track Days
M=Average Days Per Month
S=Specific Days Per Week
W=Average Days Per Week

41 RW_DAY1 Character(1) VARCHAR2(1) NOT NULL New fiedl to display Monday
A=Active
I=Inactive

42 RW_DAY2 Character(1) VARCHAR2(1) NOT NULL New fiedl to display Monday
A=Active
I=Inactive

43 RW_DAY3 Character(1) VARCHAR2(1) NOT NULL New fiedl to display Wednesday
A=Active
I=Inactive

44 RW_DAY4 Character(1) VARCHAR2(1) NOT NULL New fiedl to display Thursday
A=Active
I=Inactive

45 RW_DAY5 Character(1) VARCHAR2(1) NOT NULL New fiedl to display Friday
A=Active
I=Inactive

46 RW_DAY6 Character(1) VARCHAR2(1) NOT NULL New fiedl to display Saturday
A=Active
I=Inactive

47 RW_DAY7 Character(1) VARCHAR2(1) NOT NULL New fiedl to display Sunday
A=Active
I=Inactive

48 WF_STATUS Character(1) VARCHAR2(1) NOT NULL Workflow Status
A=Approved
C=Cancelled
D=Denied
E=Error. Contact Administrator.
F=Awaiting final approval
I=In Approval Process
M=Administrator is Processing
N=Not Available
P=Rework
S=Submitted
V=Data Saved

49 RW_SS_STATUS Character(1) VARCHAR2(1) NOT NULL Health and Safety self-service status
A=Approved
D=Denied
I=Draft
M=Pushed Back
P=Pending Approval
S=Submitted

50 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.

51 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Default Value: PER_ORG_ASGN.EMPLID Prompt Table: PERS_SRCH_GBL
2	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
3	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
5	START_DATE	Date(10)	DATE NOT NULL	Start Date for Gen Standing PO
6	PER_ORG	Character(3)	VARCHAR2(3) NOT NULL	Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest. CWR=Contingent Worker EMP=Employee POI=Person of Interest Default Value: PER_ORG_ASGN.PER_ORG
7	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: DEPT_TBL
8	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number Prompt Table: POSN_DATA_VW
9	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
10	REG_TEMP	Character(1)	VARCHAR2(1) NOT NULL	Regular/Temporary R=Regular T=Temporary Default Value: R
11	FULL_PART_TIME	Character(1)	VARCHAR2(1) NOT NULL	Full/Part Time D=On Demand F=Full-Time P=Part-Time Default Value: F
12	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company Prompt Table: COMPANY_TBL
13	PAYGROUP	Character(3)	VARCHAR2(3) NOT NULL	Pay Group Prompt Table: %EDITTABLE5
14	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT Prompt Table: BUSUNIT_HR_VW
15	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID Default Value: OPR_DEF_TBL_HR.SETID
16	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code Set ID Default Value: OPR_DEF_TBL_HR.SETID
17	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location Set ID
18	REG_REGION	Character(5)	VARCHAR2(5) NOT NULL	Regulatory Region Default Value: OPR_DEF_TBL_HR.REG_REGION Prompt Table: REG_STANDARD_VW
19	GP_PAYGROUP	Character(10)	VARCHAR2(10) NOT NULL	Global Payroll pay group
20	PAY_SYSTEM_FLG	Character(2)	VARCHAR2(2) NOT NULL	Payroll System GP=Global Payroll NA=Payroll for North America OT=Other PI=Payroll Interface Default Value: OPR_DEF_TBL_HR.PAY_SYSTEM_FLG
21	ESTABID	Character(12)	VARCHAR2(12) NOT NULL	Establishment ID Prompt Table: %EDITTABLE3
22	ABSENCE_SYSTEM_CD	Character(3)	VARCHAR2(3) NOT NULL	What Absence System this Assignment is tied to. AM=Absence Management OT=Other Default Value: OT
23	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
24	REPORTS_TO	Character(8)	VARCHAR2(8) NOT NULL	Reports To Position Number
25	ACTION	Character(3)	VARCHAR2(3) NOT NULL	Action
26	ACTION_REASON	Character(3)	VARCHAR2(3) NOT NULL	Reason Code
27	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
28	EMPL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payroll Status A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration
29	EMPL_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Employee Type E=Exception Hourly H=Hourly N=Not Applicable S=Salaried
30	FLSA_STATUS	Character(1)	VARCHAR2(1) NOT NULL	FLSA Status A=Administrative C=Computer Employee E=Executive H=Highly Compensated Employees M=Management N=Nonexempt O=Outside Salesperson P=Professional V=Nonexempt Alt Overtime X=No FLSA Required
31	END_DATE	Date(10)	DATE	End Date
32	DAYS	Number(3,0)	SMALLINT NOT NULL	Days
33	HR_RW_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Remote Worker Type(Fully remote/Hybrid) F=Fully Remote H=Hybrid
34	HR_RW_REASON	Character(60)	VARCHAR2(60) NOT NULL	Remote Worker Reason
35	HR_RW_LOC_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Remote Worker Location Type H=Home O=Other W=Workplace
36	HR_RW_LOCATION	Character(30)	VARCHAR2(30) NOT NULL	Remote Work Location
37	RW_PERCENT	Number(6,2)	DECIMAL(5,2) NOT NULL	Percentage
38	RW_PERCENT_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Remote Work Percent
39	RW_TYPE_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Remote Worker Type
40	HR_RW_TRACK_DAYS	Character(1)	VARCHAR2(1) NOT NULL	Remote Worker Track Days M=Average Days Per Month S=Specific Days Per Week W=Average Days Per Week
41	RW_DAY1	Character(1)	VARCHAR2(1) NOT NULL	New fiedl to display Monday A=Active I=Inactive
42	RW_DAY2	Character(1)	VARCHAR2(1) NOT NULL	New fiedl to display Monday A=Active I=Inactive
43	RW_DAY3	Character(1)	VARCHAR2(1) NOT NULL	New fiedl to display Wednesday A=Active I=Inactive
44	RW_DAY4	Character(1)	VARCHAR2(1) NOT NULL	New fiedl to display Thursday A=Active I=Inactive
45	RW_DAY5	Character(1)	VARCHAR2(1) NOT NULL	New fiedl to display Friday A=Active I=Inactive
46	RW_DAY6	Character(1)	VARCHAR2(1) NOT NULL	New fiedl to display Saturday A=Active I=Inactive
47	RW_DAY7	Character(1)	VARCHAR2(1) NOT NULL	New fiedl to display Sunday A=Active I=Inactive
48	WF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Workflow Status A=Approved C=Cancelled D=Denied E=Error. Contact Administrator. F=Awaiting final approval I=In Approval Process M=Administrator is Processing N=Not Available P=Rework S=Submitted V=Data Saved
49	RW_SS_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Health and Safety self-service status A=Approved D=Denied I=Draft M=Pushed Back P=Pending Approval S=Submitted
50	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.
51	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code