DIR_REPORT1_VW

(SQL View)
 Index Back

Direct Reports By Dept Tree

View used for direct reports access by security tree

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

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
5 EFFDT Date(10) DATE Effective Date

Default Value: %date

6 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
7 NAME Character(50) VARCHAR2(50) NOT NULL Name
8 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
9 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name
10 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
11 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
12 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