HR_SRCH_ACC1_VW (SQL View)	Index Back
SES:Direc Reports By Dept Tree Used internally by the HR_SRCH_DR AE for Acc Type 1, this view returns all employees for which the user has access according to the user's RowSecClass, when the user's RowSecClass is joined to this view.

HR_SRCH_ACC1_VW

(SQL View)

SES:Direc Reports By Dept Tree

Used internally by the HR_SRCH_DR AE for Acc Type 1, this view returns all employees for which the user has access according to the user's RowSecClass, when the user's RowSecClass is joined to this view.

SELECT DISTINCT OPR.ROWSECCLASS , JOB.EMPLID , JOB.EMPL_RCD , JOB.EFFDT , JOB.EFFSEQ , JOB.EMPL_STATUS , JOB.HR_STATUS , JOB.FULL_PART_TIME , JOB.SETID_DEPT , JOB.DEPTID , JOB.SETID_LOCATION , JOB.LOCATION , JOB.SETID_JOBCODE , JOB.JOBCODE , JOB.POSITION_NBR , JOB.POSITION_OVERRIDE , JOB.SUPERVISOR_ID , JOB.PER_ORG ,JOB.LASTUPDDTTM FROM PS_JOB JOB ,PS_SJT_CLASS_ALL CLS , PSOPRDEFN OPR WHERE JOB.HR_STATUS = 'A' AND JOB.EFFDT=( SELECT MAX(JOB4.EFFDT) FROM PS_JOB JOB4 WHERE JOB.EMPLID=JOB4.EMPLID AND JOB.EMPL_RCD=JOB4.EMPL_RCD AND JOB.EFFDT<=%CurrentDateIn) AND JOB.EFFSEQ=( SELECT MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3 WHERE JOB.EMPLID=JOB3.EMPLID AND JOB.EMPL_RCD=JOB3.EMPL_RCD AND JOB.EFFDT=JOB3.EFFDT) AND JOB.DEPTID=CLS.SCRTY_KEY2 AND JOB.SETID_DEPT=CLS.SCRTY_KEY1 AND ( CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = '001' AND CLS.CLASSID = OPR.ROWSECCLASS )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security

2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

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

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

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

6 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

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

8 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time

9 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

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

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

13 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID

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

15 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

16 POSITION_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Override Position Data

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

18 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

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

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	ROWSECCLASS	Character(30)	VARCHAR2(30) NOT NULL	The class used to determine row level security
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
4	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
5	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
6	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
7	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
8	FULL_PART_TIME	Character(1)	VARCHAR2(1) NOT NULL	Full/Part Time D=On Demand F=Full-Time P=Part-Time
9	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID
10	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
11	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location Set ID
12	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
13	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code Set ID
14	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
15	POSITION_NBR	Character(8)	VARCHAR2(8) NOT NULL	Position Number
16	POSITION_OVERRIDE	Character(1)	VARCHAR2(1) NOT NULL	Override Position Data
17	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
18	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
19	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.