PC_EMPL_SRCH_VW

(SQL View)
 Index Back

SQL View

FROM PS_JOB JOB , PS_PERSONAL_DATA PD , PS_PC_RATE_EMPL EMPL

SELECT JOB.EMPLID , PD.NAME , JOB.DEPTID , JOB.JOBCODE , EMPL.COST_RATE , EMPL.BILL_RATE FROM PS_JOB JOB , PS_PERSONAL_DATA PD , PS_PC_RATE_EMPL EMPL WHERE JOB.EMPLID = PD.EMPLID AND JOB.EMPL_RCD = ( SELECT MIN(J2.EMPL_RCD) FROM PS_JOB J2 WHERE JOB.EMPLID = J2.EMPLID AND J2.EFFDT = ( SELECT MAX(J3.EFFDT) FROM PS_JOB J3 WHERE J3.EMPLID = J2.EMPLID AND J3.EMPL_RCD = J2.EMPL_RCD AND J3.EFFDT <= %CurrentDateIn) AND J2.EFFSEQ = ( SELECT MAX(J4.EFFSEQ) FROM PS_JOB J4 WHERE J4.EMPLID = J2.EMPLID AND J4.EMPL_RCD = J2.EMPL_RCD AND J4.EFFDT = J2.EFFDT) AND J2.EMPL_STATUS = 'A') AND JOB.EFFDT=( SELECT MAX(J5.EFFDT) FROM PS_JOB J5 WHERE J5.EMPLID=JOB.EMPLID AND J5.EMPL_RCD=JOB.EMPL_RCD AND J5.EFFDT<= %CurrentDateIn) AND JOB.EFFSEQ = ( SELECT MAX(J6.EFFSEQ) FROM PS_JOB J6 WHERE JOB.EMPLID = J6.EMPLID AND JOB.EMPL_RCD = J6.EMPL_RCD AND JOB.EFFDT = J6.EFFDT) AND JOB.EMPLID = EMPL.EMPLID AND JOB.EMPL_STATUS = 'A'

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

Prompt Table: PC_RATE_EMPL

2 NAME Character(50) VARCHAR2(50) NOT NULL Name

Prompt Table: PC_EMPL_SRCH_VW

3 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
4 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
5 COST_RATE Number(27,3) DECIMAL(26,3) NOT NULL Cost Rate
6 BILL_RATE Number(27,3) DECIMAL(26,3) NOT NULL Billing Rate