RS_EMPL_VRFY_VW

(SQL View)
 Index Back

EmplRecord Verification View

Returns the correct empl record for a given Employee. For workers the record RS_WRKR_EFFDT determines the empl record value. For non-workers, the empl record is retrieved from the JOB table. For non-workers the minimum JOB.EMPL_RCD is returned. SUJ--Modified SQL to determine the empl record value from record RS_WRKR_EFFDT only for Resource Mgmt workers

SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EFFSEQ FROM %Table(JOB) A WHERE A.EFFDT = ( SELECT MAX(B.EFFDT) FROM %Table(JOB) B WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(BA.EFFSEQ) FROM %Table(JOB) BA WHERE BA.EMPLID = A.EMPLID AND BA.EMPL_RCD = A.EMPL_RCD AND BA.EFFDT = A.EFFDT) AND (EXISTS ( SELECT 'X' FROM %Table(RS_WRKR_EFFDT) CA WHERE CA.EMPLID = A.EMPLID AND CA.SYSTEM_SOURCE = 'RS' AND %EffdtCheck(RS_WRKR_EFFDT CAA, CA, %CurrentDateIn) AND CA.EMPL_RCD = A.EMPL_RCD) OR (NOT EXISTS ( SELECT 'X' FROM %Table(RS_WRKR_EFFDT) DA WHERE DA.EMPLID = A.EMPLID AND DA.SYSTEM_SOURCE = 'RS' AND %EffdtCheck(RS_WRKR_EFFDT DAA, DA, %CurrentDateIn)) AND A.EMPL_RCD = ( SELECT MIN(EA.EMPL_RCD) FROM %Table(JOB) EA WHERE EA.EMPLID = A.EMPLID AND EA.EFFDT = ( SELECT MAX(EAA.EFFDT) FROM %Table(JOB) EAA WHERE EAA.EMPLID = EA.EMPLID AND EAA.EMPL_RCD = EA.EMPL_RCD AND EAA.EFFDT <= %CurrentDateIn) AND EA.EFFSEQ = ( SELECT MAX(EBA.EFFSEQ) FROM %Table(JOB) EBA WHERE EBA.EMPLID = EA.EMPLID AND EBA.EMPL_RCD = EA.EMPL_RCD AND EBA.EFFDT = EA.EFFDT) AND EMPL_STATUS = 'A')))

# 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: PERSONAL_DATA

2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence