HRCD_JBTRPRT_VW(SQL View) |
Index Back |
|---|---|
Co Directory-Tree Prompt ViewCompany Directory - Prompt view for Start Node on run control page |
| SELECT TD.SETID , TD.SETCNTRLVALUE , TD.TREE_NAME , TD.EFFDT , T.TREE_NODE , T.EMPLID , T.EMPL_RCD , J.EFFDT , %Substring(N.NAME_DISPLAY,1,30) , J.JOB_INDICATOR , J.BUSINESS_UNIT , J.COMPANY , J.SETID_DEPT , J.DEPTID , J.SETID_JOBCODE , J.JOBCODE , J.POSITION_NBR , J.HR_STATUS , J.PER_ORG , J.SUPERVISOR_ID , J.REPORTS_TO 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 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 |  SETID_TREE |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | SETCNTRLVALUE |
Character(20) | VARCHAR2(20) NOT NULL | Tree Definition User Key Value |
| 3 | TREE_NAME |
Character(18) | VARCHAR2(18) NOT NULL | Tree Name |
| 4 | TREE_EFFDT |
Date(10) | DATE | Effective date of the Tree |
| 5 | TREE_NODE |
Character(20) | VARCHAR2(20) NOT NULL | Node name |
| 6 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
| 7 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL |
Empl Record
Prompt Table: PER_ORG_ASGN |
| 8 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
| 9 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 10 | 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 |
| 11 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 12 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 13 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL |
Department Set ID
Prompt Table: SET_DEPT_VLD_VW |
| 14 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: SET_DEPT_VW |
| 15 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL |
Job Code Set ID
Prompt Table: SET_JOBC_VLD_VW |
| 16 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL |
Job Code
Prompt Table: SET_JOBCD_VW |
| 17 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL |
Position Number
Prompt Table: POSITION_DATA |
| 18 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 19 | 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 |
| 20 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 21 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |