FT_COMPCHNG_VW

(SQL View)
 Index Back

Employees with company Change

Employees with company change. Used with Source data analyzer reports

SELECT J.EMPLID , J.EFFDT , J_TLR.EFFDT , J.EMPL_RCD , J_TLR.EMPL_RCD , J.EFFSEQ , J_TLR.EFFSEQ , J.COMPANY , J_TLR.COMPANY , J.ACTION , J_TLR.ACTION FROM PS_JOB J , PS_JOB J_TLR WHERE J.EMPLID = J_TLR.EMPLID AND J.EMPL_RCD=J_TLR.EMPL_RCD AND (J_TLR.EFFDT =( SELECT MIN(EFFDT) FROM PS_JOB J_DT WHERE J_DT.EMPLID = J.EMPLID AND J_DT.EMPL_RCD = J.EMPL_RCD AND J_DT.EFFDT > J.EFFDT) AND J.EFFSEQ= ( SELECT MAX(J_SEQ.EFFSEQ) FROM PS_JOB J_SEQ WHERE J_SEQ.EMPLID=J.EMPLID AND J_SEQ.EMPL_RCD=J.EMPL_RCD AND J_SEQ.EFFDT=J.EFFDT) AND J_TLR.EFFSEQ=( SELECT MIN(J_SEQ1.EFFSEQ) FROM PS_JOB J_SEQ1 WHERE J_SEQ1.EMPLID = J.EMPLID AND J_SEQ1.EMPL_RCD = J.EMPL_RCD AND J_SEQ1.EFFDT = J_TLR.EFFDT)) AND J_TLR.COMPANY <> J.COMPANY AND J_TLR.ACTION NOT IN ('REH', 'HIR', 'ADD','RNW','ADL', 'ASG') UNION SELECT J.EMPLID , J.EFFDT , J_TLR.EFFDT , J.EMPL_RCD , J_TLR.EMPL_RCD , J.EFFSEQ , J_TLR.EFFSEQ , J.COMPANY , J_TLR.COMPANY , J.ACTION , J_TLR.ACTION FROM PS_JOB J , PS_JOB J_TLR WHERE J.EMPLID = J_TLR.EMPLID AND J.EMPL_RCD=J_TLR.EMPL_RCD AND J.EFFDT=J_TLR.EFFDT AND J.EFFSEQ = ( SELECT MAX(J_SEQ2.EFFSEQ) FROM PS_JOB J_SEQ2 WHERE J_SEQ2.EMPLID= J.EMPLID AND J_SEQ2.EMPL_RCD =J.EMPL_RCD AND J_SEQ2.EFFDT=J_TLR.EFFDT AND J_SEQ2.EFFSEQ J.COMPANY AND J_TLR.ACTION NOT IN ('REH', 'HIR', 'ADD','RNW','ADL', 'ASG')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EFFDT Date(10) DATE Effective Date

Default Value: %date

3 FT_TLR_EFFDT Date(10) DATE Trailing Effdt
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
5 FT_TLR_EMPL_RCD Number(3,0) SMALLINT NOT NULL Trailing Employee Record Numb
6 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
7 FT_TLR_EFFSEQ Number(3,0) SMALLINT NOT NULL Trailing Effective Sequence
8 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
9 FT_TLR_COMPANY Character(3) VARCHAR2(3) NOT NULL Trailing Company
10 ACTION Character(3) VARCHAR2(3) NOT NULL Action
11 FT_TLR_ACTION Character(3) VARCHAR2(3) NOT NULL Trailing Action