JPM_CSC_JOB_VW

(SQL View)
 Index Back

JPM Compare Job Data

Select the latest effective dated data for Active Employees. Calculates Years in current job as: current date - effdt of previous job code. if no previous jobcode exists, then the minumum Effdt is selected.

SELECT A.EMPLID ,A.EMPL_RCD ,A.BUSINESS_UNIT ,A.REG_REGION ,A.SETID_DEPT ,A.DEPTID ,A.SETID_JOBCODE ,A.JOBCODE ,A.SETID_LOCATION ,A.LOCATION ,A.ANNUAL_RT ,A.CURRENCY_CD ,A.SETID_SALARY ,A.SAL_ADMIN_PLAN ,A.GRADE ,A.STEP ,A.COMPANY ,A.REG_TEMP ,A.FULL_PART_TIME ,A.STD_HOURS ,A.HIRE_DT ,%Round((%DateDiff(B.EFFDT,%CurrentDateIn) / 365), 1) FROM PS_JOB A , PS_JOB B WHERE A.EFFDT = ( SELECT MAX (A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND A1.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.EFFDT = A.EFFDT) AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOBCODE = B.JOBCODE AND A.HR_STATUS = 'A' AND B.EFFDT = ( SELECT MIN (B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B.EMPLID AND B1.EMPL_RCD = B.EMPL_RCD AND B1.EFFDT <= %CurrentDateIn AND B1.JOBCODE = B.JOBCODE) AND B.EFFSEQ = ( SELECT MIN(B2.EFFSEQ) FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EMPL_RCD = B.EMPL_RCD AND B2.EFFDT = B.EFFDT AND B2.JOBCODE = B.JOBCODE) AND (A.JOB_INDICATOR = 'P' OR (NOT EXISTS ( SELECT 'X' FROM PS_JOB PI WHERE PI.JOB_INDICATOR = 'P' AND A.EMPLID = PI.EMPLID AND PI.HR_STATUS = 'A' AND PI.EFFDT = ( SELECT MAX (PI1.EFFDT) FROM PS_JOB PI1 WHERE PI1.EMPLID = PI.EMPLID AND PI1.EMPL_RCD = PI.EMPL_RCD AND PI1.EFFDT <= %CurrentDateIn) AND PI.EFFSEQ = ( SELECT MAX(PI2.EFFSEQ) FROM PS_JOB PI2 WHERE PI2.EMPLID = PI.EMPLID AND PI2.EMPL_RCD = PI.EMPL_RCD AND PI2.EFFDT = PI.EFFDT) ) AND A.EMPL_RCD = ( SELECT MIN(EMPL_RCD) FROM PS_JOB ER WHERE A.EMPLID = ER.EMPLID AND ER.HR_STATUS = 'A' AND ER.EFFDT = ( SELECT MAX (ER1.EFFDT) FROM PS_JOB ER1 WHERE ER1.EMPLID = ER.EMPLID AND ER1.EMPL_RCD = ER.EMPL_RCD AND ER1.EFFDT <= %CurrentDateIn) AND ER.EFFSEQ = ( SELECT MAX(ER2.EFFSEQ) FROM PS_JOB ER2 WHERE ER2.EMPLID = ER.EMPLID AND ER2.EMPL_RCD = ER.EMPL_RCD AND ER2.EFFDT = ER.EFFDT) ) ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Default Value: NEW

Prompt Table: PERSON

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

Prompt Table: BUS_UNIT_TBL_HR

4 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region

Prompt Table: REG_REGION_TBL

5 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
6 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

7 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
8 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

9 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
10 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

11 ANNUAL_RT Number(19,3) DECIMAL(18,3) NOT NULL Annual Rate
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
13 SETID_SALARY Character(5) VARCHAR2(5) NOT NULL Salary Set ID
14 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
15 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
16 STEP Number(2,0) SMALLINT NOT NULL Step
17 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

18 REG_TEMP Character(1) VARCHAR2(1) NOT NULL Regular/Temporary
R=Regular
T=Temporary
19 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
20 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
21 HIRE_DT Date(10) DATE First Start Date
22 YEARS_IN_JOB Number(5,1) DECIMAL(4,1) NOT NULL Years in Current Job