HS_CURRENT_JOB

(SQL View)
 Index Back

Current Job from Job Table

This is a clone of the CURRENT_JOB_ALL table but with different key structure.

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ , A.DEPTID , A.JOBCODE , A.POSITION_NBR , A.POSITION_OVERRIDE , A.HR_STATUS , A.EMPL_STATUS , A.ACTION , A.ACTION_DT , A.ACTION_REASON , A.LOCATION , A.JOB_ENTRY_DT , A.DEPT_ENTRY_DT , A.POSITION_ENTRY_DT , A.SHIFT , A.REG_TEMP , A.FULL_PART_TIME , A.FLSA_STATUS , A.OFFICER_CD , A.COMPANY , A.PAYGROUP , A.EMPL_TYPE , A.HOLIDAY_SCHEDULE , A.STD_HOURS , A.EEO_CLASS , A.EMPL_CLASS , A.SAL_ADMIN_PLAN , A.GRADE , A.GRADE_ENTRY_DT , A.STEP , A.STEP_ENTRY_DT , A.GL_PAY_TYPE , A.ACCT_CD , A.EARNS_DIST_TYPE , A.COMP_FREQUENCY , A.COMPRATE , A.CHANGE_AMT , A.CHANGE_PCT , A.ANNUAL_RT , A.MONTHLY_RT , A.HOURLY_RT , A.ANNL_BENEF_BASE_RT , A.SHIFT_RT , A.SHIFT_FACTOR , A.CURRENCY_CD , A.BUSINESS_UNIT , %CurrentDateIn , A.ADDS_TO_FTE_ACTUAL , A.CLASS_INDC , A.FICA_STATUS_EE , A.FTE , A.PRORATE_CNT_AMT , A.ENCUMB_OVERRIDE , A.PAY_SYSTEM_FLG , A.SETID_DEPT , A.SETID_JOBCODE , A.SETID_LOCATION , A.SETID_SALARY , A.REG_REGION , RG.COUNTRY , A.PER_ORG , A.JOB_INDICATOR , A.CONTRACT_NUM , A.REPORTS_TO , A.SUPERVISOR_ID , %subrec(JOB_DATES_SBR, A) FROM PS_JOB A , PS_REG_REGION_TBL RG WHERE RG.REG_REGION = A.REG_REGION AND A.EFFDT = ( SELECT MAX (C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = A.EMPLID AND D.EMPL_RCD = A.EMPL_RCD AND D.EFFDT = A.EFFDT) AND A.HR_STATUS = 'A' 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

Prompt Table: PERSON

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 EFFDT Date(10) DATE NOT NULL Effective Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

6 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

7 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
8 POSITION_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Override Position Data

Y/N Table Edit

9 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
10 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
11 ACTION Character(3) VARCHAR2(3) NOT NULL Action

Prompt Table: ACTION_TBL

12 ACTION_DT Date(10) DATE Action Date
13 ACTION_REASON Character(3) VARCHAR2(3) NOT NULL Reason Code

Prompt Table: ACTN_REASON_TBL

14 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

15 JOB_ENTRY_DT Date(10) DATE Job Entry Date
16 DEPT_ENTRY_DT Date(10) DATE Department Entry Date
17 POSITION_ENTRY_DT Date(10) DATE Position Entry Date
18 SHIFT Character(1) VARCHAR2(1) NOT NULL Regular Shift
1=Day
2=Evening
3=Night
A=Any
C=Compressed
N=Not Applicable
R=Rotating
19 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
R=Regular
T=Temporary
20 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
21 FLSA_STATUS Character(1) VARCHAR2(1) NOT NULL FLSA Status
A=Administrative
C=Computer Employee
E=Executive
H=Highly Compensated Employees
M=Management
N=Nonexempt
O=Outside Salesperson
P=Professional
V=Nonexempt Alt Overtime
X=No FLSA Required
22 OFFICER_CD Character(1) VARCHAR2(1) NOT NULL Officer Code
C=Chairman
D=Director
N=None
O=Officer
P=President
S=Secretary
T=Treasurer
V=Vice President
23 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

24 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

Prompt Table: PAYGROUP_TBL

25 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried

Prompt Table: PAYGRP_EMPLTYPE

26 HOLIDAY_SCHEDULE Character(6) VARCHAR2(6) NOT NULL Holiday Schedule

Prompt Table: HOLIDAY_TBL

27 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
28 EEO_CLASS Character(1) VARCHAR2(1) NOT NULL EEO Classification
E=Exclude from Count
N=None of the Above
P=Production Trainee
W=White-Collar Trainee
29 EMPL_CLASS Character(3) VARCHAR2(3) NOT NULL Employee Classification

Prompt Table: EMPL_CLASS_TBL
Set Control Field: REG_REGION

30 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
31 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade

Prompt Table: SAL_GRADE_TBL

32 GRADE_ENTRY_DT Date(10) DATE Grade Entry Date
33 STEP Number(2,0) SMALLINT NOT NULL Step

Prompt Table: SAL_STEP_TBL

34 STEP_ENTRY_DT Date(10) DATE Step Entry Date
35 GL_PAY_TYPE Character(6) VARCHAR2(6) NOT NULL General Ledger Pay Type
36 ACCT_CD Character(25) VARCHAR2(25) NOT NULL Combination Code

Prompt Table: ACCT_CD_TBL

37 EARNS_DIST_TYPE Character(1) VARCHAR2(1) NOT NULL Earnings Distribution Type
A=By Amount
H=By Hours
N=None
P=By Percent
38 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

Prompt Table: COMP_FREQ_VW

39 COMPRATE Number(19,6) DECIMAL(18,6) NOT NULL Compensation Rate
40 CHANGE_AMT Signed Number(20,6) DECIMAL(18,6) NOT NULL Change Amount
41 CHANGE_PCT Signed Number(8,3) DECIMAL(6,3) NOT NULL Change Percent
42 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
43 MONTHLY_RT Number(19,3) DECIMAL(18,3) NOT NULL Monthly Rate
44 HOURLY_RT Number(19,6) DECIMAL(18,6) NOT NULL Hourly Rate
45 ANNL_BENEF_BASE_RT Number(19,3) DECIMAL(18,3) NOT NULL Specifies a compensation amount used to calculate benefits and benefit deductions for an employee. The base compensation amount may include commissions and bonuses in addition to regular compensation. If no Annual Benefits Base Rate is entered, then benefits calculations are based on an employee's regular compensation.
46 SHIFT_RT Number(19,6) DECIMAL(18,6) NOT NULL Shift Differential Rate
47 SHIFT_FACTOR Number(5,3) DECIMAL(4,3) NOT NULL Shift Differential Factor
48 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Prompt Table: CURRENCY_CD_TBL

49 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
50 ASOFDATE Date(10) DATE As of Date
51 ADDS_TO_FTE_ACTUAL Character(1) VARCHAR2(1) NOT NULL Adds to FTE Actual Count
52 CLASS_INDC Character(1) VARCHAR2(1) NOT NULL Classified/Unclassified Ind
B=All
C=Classified
T=Temporary
U=Unclassified
53 FICA_STATUS_EE Character(1) VARCHAR2(1) NOT NULL FICA Status-Employee
E=Exempt
H=ER FICA Exmpt
J=EE FICA Exmpt
K=EE/ER FICA Exmpt
M=Medicare only
N=Subject
54 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
55 PRORATE_CNT_AMT Character(1) VARCHAR2(1) NOT NULL Prorate Contract Change Amount
C=Prorate Over Contract Period
E=Prorate Using Effective Date
N=No Proration of Change Amt.
P=Prorate Over Payment Period
56 ENCUMB_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Encumbrance Override
57 PAY_SYSTEM_FLG Character(2) VARCHAR2(2) NOT NULL Payroll System
GP=Global Payroll
NA=Payroll for North America
OT=Other
PI=Payroll Interface
58 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
59 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
60 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
61 SETID_SALARY Character(5) VARCHAR2(5) NOT NULL Salary Set ID
62 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
63 REG_REGION_COUNTRY Character(3) VARCHAR2(3) NOT NULL Reg Region Country
64 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
65 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
66 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
67 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
68 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
69 HIRE_DT Date(10) DATE First Start Date
70 LAST_HIRE_DT Date(10) DATE Latest Start Date - The latest time that an Employee or a Contingent Worker has started - based on EMPLID/EMPL_RCD.
71 TERMINATION_DT Date(10) DATE Termination Date
72 ASGN_START_DT Date(10) DATE Start date of the specific EMPL_RCD assignment. This is different from the Person's Hire date.
73 LST_ASGN_START_DT Date(10) DATE Lastest start date of the specific EMPL_RCD assignment. This is different from the Person's organizational instance Last Hire Date.
74 ASGN_END_DT Date(10) DATE End date of the specific EMPL_RCD assignment. This is different from the Person's Termination date.
75 LDW_OVR Character(1) VARCHAR2(1) NOT NULL Used to indicate whether the user has overridden the Last Date Worked - ON THIS JOB_DATES row. It will only be set to 'Y' on the row where the date is entered, it will be 'N' on the later rows. This allows us to identify which JOB_DATES row is the row where the Date was either set or updated without having to look at the previous row. If the LDW = ( EFFDT - 1 ) or the LDW_OVR is 'Y', then this is the row where the LDW was set - and therefore can be corrected.

Y/N Table Edit

Default Value: N

76 LAST_DATE_WORKED Date(10) DATE Last Date Worked
77 EXPECTED_RETURN_DT Date(10) DATE Expected Return Date
78 EXPECTED_END_DATE Date(10) DATE Expected End Date of a job.
79 AUTO_END_FLG Character(1) VARCHAR2(1) NOT NULL This field is on the Job page and it allows the users to indicate whether they want the system to automatically end this job on the Expected End Date of the job. This field is dependent on the Expected End Date having a value.

Y/N Table Edit

Default Value: N