PC_CURJOB_VW

(SQL View)
 Index Back

Current Job view

View of employee's current Job

SELECT J.EMPLID , J.BUSINESS_UNIT , J.LOCATION , J.JOBCODE , J.DEPTID , J.STD_HOURS , J.STD_HRS_FREQUENCY , J.SUPERVISOR_ID FROM %Table(JOB) J WHERE (J.EFFDT IS NULL OR J.EFFDT = ( SELECT MAX(H.EFFDT) FROM %Table(JOB) H WHERE J.EMPLID = H.EMPLID AND J.EMPL_RCD = H.EMPL_RCD AND H.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(I.EFFSEQ) FROM %Table(JOB) I WHERE J.EMPLID = I.EMPLID AND J.EMPL_RCD = I.EMPL_RCD AND J.EFFDT = I.EFFDT))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 BUSINESS_UNIT_HR Character(5) VARCHAR2(5) NOT NULL Business Unit HR

Prompt Table: SP_BUPCHR_NONVW

3 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

5 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
6 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
7 STD_HRS_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Standard Work Period
8 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID