WF_JOB_SUMM_VW

(SQL View)
 Index Back

Workforce Job Summary

View to show the total workforce data for Employees, Contingent Workers, and Persons of Interest.

SELECT A.EMPLID , J.EFFDT , A.EMPL_RCD , J.EFFSEQ , A.PER_ORG , A.ORG_INSTANCE_ERN , A.POI_TYPE , J.HR_STATUS , J.EMPL_STATUS , J.ACTION , J.ACTION_REASON , J.JOBCODE , J.EMPL_TYPE , J.FULL_PART_TIME , J.REG_TEMP , J.STD_HOURS , J.STD_HRS_FREQUENCY , J.POSITION_NBR , J.POSITION_ENTRY_DT , J.COMPANY , J.LOCATION , J.DEPTID , J.REPORTS_TO , J.SAL_ADMIN_PLAN , J.GRADE , J.STEP , J.PAYGROUP , J.COMPRATE , J.COMP_FREQUENCY , J.ANNUAL_RT , J.MONTHLY_RT , J.DAILY_RT , J.HOURLY_RT , J.CURRENCY_CD , J.CHANGE_AMT , J.CHANGE_PCT , J.BUSINESS_UNIT FROM PS_PER_ORG_ASGN A , PS_JOB J WHERE A.EMPLID = J.EMPLID AND A.EMPL_RCD = J.EMPL_RCD

# 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

Default Value: %date

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 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
6 ORG_INSTANCE_ERN Number(3,0) SMALLINT NOT NULL Indicates which EMPL_RCD (ERN) is considered the Organizational Instance for this particular ERN. An Organizational Instance defines an Instance of Employment or an Instance of Contingent Workforce. Hire, Termination, and Service dates are captured at the Organiational Instance level.
7 POI_TYPE Character(5) VARCHAR2(5) NOT NULL Defines the Transactional usage of POIs in HRMS.
8 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
9 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
10 ACTION Character(3) VARCHAR2(3) NOT NULL Action
11 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code
12 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
13 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried
14 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
15 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
R=Regular
T=Temporary
16 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
17 STD_HRS_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Standard Work Period
18 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
19 POSITION_ENTRY_DT Date(10) DATE Position Entry Date
20 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
21 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
22 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
23 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
24 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
25 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
26 STEP Number(2,0) SMALLINT NOT NULL Step
27 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
28 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
29 COMP_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Compensation Frequency
A=Annual
B=Biweekly
C=Contract
D=Daily
H=Hourly
M=Monthly
S=Semimonthly
W=Weekly
30 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
31 MONTHLY_RT Number(19,3) DECIMAL(18,3) NOT NULL Monthly Rate
32 DAILY_RT Number(19,3) DECIMAL(18,3) NOT NULL Daily Rate
33 HOURLY_RT Number(19,6) DECIMAL(18,6) NOT NULL Hourly Rate
34 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
35 CHANGE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Change Amount
36 CHANGE_PCT Signed Number(8,3) DECIMAL(6,3) NOT NULL Change Percent
37 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit