HRCD_JBTRSRC_VW(SQL View) |
Index Back |
|---|---|
Co Directory-Tree Src ViewCompany Directory - Tree Source View |
| SELECT T.TREE_NODE , T.EMPLID , T.EMPL_RCD , J.EFFDT , %Substring(N.NAME_DISPLAY,1,30) , 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 PS_HRCD_JOB_TREE T , PS_JOB J , PS_PERSON_NAME N WHERE T.EMPLID = N.EMPLID AND T.EMPLID = J.EMPLID AND T.EMPL_RCD = J.EMPL_RCD 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) UNION SELECT T.TREE_NODE , T.EMPLID , T.EMPL_RCD , J.effdt , M.MESSAGE_TEXT %Concat ' ' %Concat %Substring (T.EMPLID, 4, length( T.EMPLID)) , J.BUSINESS_UNIT , J.COMPANY , ' ' , J.DEPTID , ' ' , J.JOBCODE , ' ' , J.LOCATION , J.POSITION_NBR , ' ' , ' ' , ' ' , J.REPORTS_TO , ' ' FROM PS_HRCD_JOB_TREE T , PS_POSITION_DATA J , PSMSGCATDEFN M WHERE %Substring(T.EMPLID,1,3) = 'EP$' AND T.EMPLID= 'EP$' %Concat J.POSITION_NBR AND J.EFFDT = ( SELECT MAX(D.EFFDT) FROM PS_POSITION_DATA D WHERE D.POSITION_NBR = J.POSITION_NBR AND D.EFFDT = %CurrentDateIn) AND M.MESSAGE_SET_NBR = 1010 AND M.MESSAGE_NBR = 2189 |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  TREE_NODE |
Character(20) | VARCHAR2(20) NOT NULL | Node name |
| 2 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
| 3 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL |
Empl Record
Prompt Table: PER_ORG_ASGN |
| 4 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
| 5 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 6 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 7 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 8 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 9 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPT_TBL |
| 10 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code Set ID |
| 11 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL |
Job Code
Prompt Table: JOBCODE_TBL |
| 12 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location Set ID |
| 13 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 14 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL |
Position Number
Prompt Table: POSITION_DATA |
| 15 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 16 | 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 |
| 17 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 18 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |
| 19 | 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 |