HRCD_EMP_VW(SQL View) |
Index Back |
|---|---|
Co Directory-Tree Src ViewCompany Directory - Tree Source View |
| SELECT DISTINCT T.EMPLID , T.EMPL_RCD , N.NAME_DISPLAY , J.BUSINESS_UNIT , J.COMPANY , J.SETID_DEPT , J.DEPTID , J.SETID_JOBCODE , J.JOBCODE ,J.SETID_LOCATION ,J.LOCATION , J.POSITION_NBR , J.HR_STATUS , J.PER_ORG , J.SUPERVISOR_ID , J.REPORTS_TO , J.JOB_INDICATOR FROM PSTREEDEFN TD , PSTREENODE TN , PS_HRCD_JOB_TREE T , PS_JOB J , PS_PERSON_NAME N WHERE TN.TREE_NODE = T.TREE_NODE AND TN.SETID = TD.SETID AND TN.SETCNTRLVALUE = TD.SETCNTRLVALUE AND TN.TREE_NAME = TD.TREE_NAME AND TN.EFFDT = TD.EFFDT AND T.EMPLID = N.EMPLID AND T.EMPLID = J.EMPLID AND T.EMPL_RCD = J.EMPL_RCD AND TD.EFFDT = ( SELECT MAX(TD1.EFFDT) FROM PSTREEDEFN TD1 WHERE TD.SETCNTRLVALUE = TD1.SETCNTRLVALUE AND TD.TREE_NAME = TD1.TREE_NAME) AND J.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_JOB D WHERE D.EMPLID = J.EMPLID AND D.EMPL_RCD = J.EMPL_RCD AND D.EFFDT <= TD.EFFDT) AND J.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = J.EMPLID AND D.EMPL_RCD = J.EMPL_RCD AND D.EFFDT = J.EFFDT) |
| # | 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 Record |
| 3 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 4 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 5 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 6 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL |
Department Set ID
Prompt Table: SETID_TBL |
| 7 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table:
DEPT_TBL
|
| 8 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL |
Job Code Set ID
Prompt Table: SETID_TBL |
| 9 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL |
Job Code
Prompt Table:
JOBCODE_TBL
|
| 10 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL |
Location Set ID
Prompt Table: SETID_TBL |
| 11 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL |
Location Code
Prompt Table:
LOCATION_TBL
|
| 12 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL |
Position Number
Prompt Table: POSITION_DATA |
| 13 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 14 | 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 |
| 15 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 16 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |
| 17 | JOB_INDICATOR | Character(1) | VARCHAR2(1) NOT NULL |
This Indicator is used on JOB to specify which employment record is considered to be the primary one for an employee.
In case of Multiple Jobs per Employee, this property is being used to make a conscious decision in related features where only 1 Employment Record per Employee needs to be returned.
N=Not Applicable P=Primary Job S=Secondary Job |