DIR_REPORTS_VW1

(SQL View)
 Index Back

Self Service by Dept Tree

DIR_REPORTS_VW4 is used in Self Service to determine the reporting relationship based on the standard department tree.

SELECT JOB.EMPLID , JOB.EMPL_RCD , OPR.OPRID , OPR.ROWSECCLASS , A.NAME , A.LAST_NAME_SRCH , A.NAME_AC , A.LAST_NAME , A.FIRST_NAME , JOB.PER_ORG , JOB.EFFDT , JOB.EFFSEQ FROM PS_PERSON_NAME A ,PS_JOB JOB , PS_SJT_CLASS_ALL CLS , PSOPRDEFN OPR WHERE A.EMPLID = JOB.EMPLID AND A.EMPLID = JOB.EMPLID AND JOB.HR_STATUS = 'A' AND (CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = '001' AND CLS.CLASSID = OPR.ROWSECCLASS ) AND JOB.DEPTID=CLS.SCRTY_KEY2 AND JOB.SETID_DEPT=CLS.SCRTY_KEY1

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
3 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
4 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security
5 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
7 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
8 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
9 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
10 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
11 EFFDT Date(10) DATE Effective Date

Default Value: %date

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