GPCH_AL_LHIR_VW(SQL View) |
Index Back |
|---|---|
Latest Hire ViewLatest Hire View |
| SELECT J1.EMPLID ,J1.EMPL_RCD ,J1.EFFDT ,J1.ACTION ,J1.COMPANY ,J1.PAY_SYSTEM_FLG FROM PS_JOB J1 WHERE J1.PER_ORG = 'EMP' AND J1.PAY_SYSTEM_FLG = 'GP' AND (( J1.ACTION IN ('HIR','REH','ADL') AND NOT EXISTS ( SELECT 'x' FROM PS_JOB J2 WHERE J1.PER_ORG = J2.PER_ORG AND J1.PAY_SYSTEM_FLG = J2.PAY_SYSTEM_FLG AND J1.EMPLID = J2.EMPLID AND J1.EMPL_RCD <> J2.EMPL_RCD AND J1.COMPANY = J2.COMPANY AND J2.EFFDT = ( SELECT MAX(J3.EFFDT) FROM PS_JOB J3 WHERE J2.EMPLID = J3.EMPLID AND J2.PER_ORG = J3.PER_ORG AND J2.PAY_SYSTEM_FLG = J3.PAY_SYSTEM_FLG AND J2.EMPL_RCD = J3.EMPL_RCD AND J2.COMPANY = J3.COMPANY AND J3.EFFDT < J1.EFFDT ) AND J2.EFFSEQ = ( SELECT MAX(J3_effseq.EFFSEQ) FROM PS_JOB J3_effseq WHERE J2.EMPLID = J3_effseq.EMPLID AND J2.PER_ORG = J3_effseq.PER_ORG AND J2.PAY_SYSTEM_FLG = J3_effseq.PAY_SYSTEM_FLG AND J2.EMPL_RCD = J3_effseq.EMPL_RCD AND J2.COMPANY = J3_effseq.COMPANY AND J3_effseq.EFFDT = J2.EFFDT ) AND J2.ACTION <> 'TER' AND J2.ACTION <> 'TWP') ) OR ( J1.ACTION IN ('TER' ,'TWP') AND NOT EXISTS ( SELECT 'x' FROM PS_JOB J4 WHERE J1.EMPLID = J4.EMPLID AND J1.EMPL_RCD <> J4.EMPL_RCD AND J1.PER_ORG = J4.PER_ORG AND J1.PAY_SYSTEM_FLG = J4.PAY_SYSTEM_FLG AND J1.COMPANY = J4.COMPANY AND J4.EMPL_STATUS = 'A' AND J4.EFFDT = ( SELECT MAX(J5.EFFDT) FROM PS_JOB J5 WHERE J4.EMPLID = J5.EMPLID AND J4.EMPL_RCD = J5.EMPL_RCD AND J4.PER_ORG = J5.PER_ORG AND J4.PAY_SYSTEM_FLG = J5.PAY_SYSTEM_FLG AND J4.COMPANY = J5.COMPANY AND J5.EFFDT <= J1.EFFDT ) AND J4.EFFSEQ = ( SELECT MAX(J5_effseq.EFFSEQ) FROM PS_JOB J5_effseq WHERE J4.EMPLID = J5_effseq.EMPLID AND J4.PER_ORG = J5_effseq.PER_ORG AND J4.PAY_SYSTEM_FLG = J5_effseq.PAY_SYSTEM_FLG AND J4.EMPL_RCD = J5_effseq.EMPL_RCD AND J4.COMPANY = J5_effseq.COMPANY AND J5_effseq.EFFDT = J4.EFFDT ) ) ) ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | ACTION | Character(3) | VARCHAR2(3) NOT NULL |
Action
Prompt Table: ACTION_TBL |
| 5 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 6 | PAY_SYSTEM_FLG | Character(2) | VARCHAR2(2) NOT NULL |
Payroll System
GP=Global Payroll NA=Payroll for North America OT=Other PI=Payroll Interface |