SALPLN_JOBCD_VW

(SQL View)
 Index Back

Salary Plan - Job Code View

Salary Plan - Job Code View This view is used to determine fields from the Job Code Table related to a SALARY_PLAN record.

SELECT SP.EMPLID , SP.EMPL_RCD , SP.EFFDT , SP.EFFSEQ , JC1.JOBCODE , JC1.SAL_RANGE_MID_RATE , JC1.SAL_RANGE_CURRENCY , JC1.SAL_RANGE_FREQ , JC1.STD_HOURS , JC1.STD_HRS_FREQUENCY FROM PS_SALARY_PLAN SP ,PS_JOB J1 ,PS_JOBCODE_TBL JC1 WHERE J1.EMPLID = SP.EMPLID AND J1.EMPL_RCD = SP.EMPL_RCD AND J1.EFFDT = ( SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = SP.EMPLID AND J2.EMPL_RCD = SP.EMPL_RCD AND J2.EFFDT <= SP.EFFDT) AND J1.EFFSEQ = ( SELECT MAX(J3.EFFSEQ) FROM PS_JOB J3 WHERE J3.EMPLID = J1.EMPLID AND J3.EMPL_RCD = J1.EMPL_RCD AND J3.EFFDT = J1.EFFDT) AND JC1.SETID = J1.SETID_JOBCODE AND JC1.JOBCODE = J1.JOBCODE AND JC1.EFF_STATUS = 'A' AND JC1.EFFDT = ( SELECT MAX(JC2.EFFDT) FROM PS_JOBCODE_TBL JC2 WHERE JC2.SETID = JC1.SETID AND JC2.JOBCODE = JC1.JOBCODE AND JC2.EFFDT <= J1.EFFDT)

# 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

Prompt Table: PER_ORG_ASGN_VW

3 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

6 SAL_RANGE_MID_RATE Number(19,6) DECIMAL(18,6) NOT NULL Salary Range Midpoint Rate
7 SAL_RANGE_CURRENCY Character(3) VARCHAR2(3) NOT NULL Salary Range Currency
8 SAL_RANGE_FREQ Character(5) VARCHAR2(5) NOT NULL Salary Range Frequency

Prompt Table: COMP_FREQ_VW

9 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
10 STD_HRS_FREQUENCY Character(5) VARCHAR2(5) NOT NULL Standard Work Period

Prompt Table: COMP_FREQ_VW