ST_JOB_DATA_VW

(SQL View)
 Index Back

Primary Job and Employment

This view pulls the Primary Job Data Information and some Employment Information as well. The primary use for this view is for the Personal Profile Page.

SELECT C.EMPLID , C.EFFDT_NOKEY , C.EMPL_RCD , C.EFFSEQ_NOKEY , A.JOB_INDICATOR , A.PER_ORG , A.POI_TYPE , C.DEPTID , C.JOBCODE , C.POSITION_NBR , C.EMPL_STATUS , C.LOCATION , C.BUSINESS_UNIT , C.COMPANY , C.PAYGROUP , A.TAX_LOCATION_CD , C.REG_TEMP , C.FULL_PART_TIME , A.ACTION , A.ACTION_REASON , A.GRADE , A.PAY_SYSTEM_FLG , A.REG_REGION , A.FTE , B.BUSINESS_TITLE , B.CMPNY_SENIORITY_DT , B.HIRE_DT , B.PROBATION_DT , B.LAST_HIRE_DT , A.REPORTS_TO , B.SERVICE_DT , A.SUPERVISOR_ID , B.TERMINATION_DT , D.NAME , E.NATIONAL_ID FROM PS_JOB A , PS_PER_ORG_ASGN_VW B , PS_JOB_ALL_CURR_VW C , PS_PERSON_NAME D , PS_PERS_NID_VW E WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT_NOKEY = A.EFFDT AND C.EFFSEQ_NOKEY = A.EFFSEQ AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND A.JOB_INDICATOR = 'P'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EFFDT Date(10) DATE Effective Date
3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 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
6 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
7 POI_TYPE Character(5) VARCHAR2(5) NOT NULL Defines the Transactional usage of POIs in HRMS.

Prompt Table: POI_TYPE_TBL
Set Control Field: DESCR

8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
9 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
10 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
11 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
12 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
13 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
14 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
15 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
16 TAX_LOCATION_CD Character(10) VARCHAR2(10) NOT NULL Tax Location Code
17 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
R=Regular
T=Temporary
18 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
19 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

20 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
21 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
22 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
GP=Global Payroll
NA=Payroll for North America
OT=Other
PI=Payroll Interface
23 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
24 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
25 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title
26 CMPNY_SENIORITY_DT Date(10) DATE Company Seniority Date
27 HIRE_DT Date(10) DATE First Start Date
28 PROBATION_DT Date(10) DATE Probation Date
29 REHIRE_DT Date(10) DATE Rehire Date
30 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
31 SERVICE_DT Date(10) DATE Service Date
32 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
33 TERMINATION_DT Date(10) DATE Termination Date
34 NAME Character(50) VARCHAR2(50) NOT NULL Name
35 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID