HR_ESS_JOB_VW(SQL View) |
Index Back |
|---|---|
ESS Resignation Muti Job ViewESS Resignation Muti Job View |
| SELECT JB.EMPLID ,JB.EMPL_RCD ,JB.SETID_DEPT ,JB.SETID_JOBCODE ,JB.DEPTID ,JB.business_unit ,JB.EMPL_STATUS ,JB.SETID_LOCATION ,JB.Location ,JB.Position_nbr ,JB.Jobcode ,JB.Supervisor_ID ,JB.Reports_to ,JB.Reg_Region ,ST.WF_STATUS ,CASE WHEN ST.WF_STATUS IS NULL THEN 'Y' WHEN ST.WF_STATUS ='D' THEN 'Y' ELSE 'N' END AS ELIGIBLE FROM PS_JOB JB LEFT JOIN ( SELECT * FROM PS_HR_RESIGN_DAT XT WHERE XT.EFFDT_FROM > %CurrentDateIn AND XT.REQUEST_DT= ( SELECT MAX(XT1.REQUEST_DT) FROM PS_HR_RESIGN_DAT XT1 WHERE XT1.EMPLID=XT.EMPLID AND XT1.EMPL_RCD=XT.EMPL_RCD) AND XT.EFFSEQ =( SELECT MAX(XT2.EFFSEQ) FROM PS_HR_RESIGN_DAT XT2 WHERE XT.EMPLID = XT2.EMPLID AND XT.EMPL_RCD = XT2.EMPL_RCD AND XT.REQUEST_DT = XT2.REQUEST_DT )) ST ON JB.EMPLID=ST.EMPLID AND JB.EMPL_RCD=ST.EMPL_RCD WHERE JB.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JB1 WHERE JB1.EMPLID = JB.EMPLID AND JB1.EMPL_RCD = JB.EMPL_RCD AND JB1.EFFDT <= %CurrentDateIn) AND JB.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = JB.EMPLID AND JB2.EFFDT = JB.EFFDT AND JB2.EMPL_RCD = JB.EMPL_RCD) AND JB.HR_STATUS='A' AND JB.EMPL_STATUS NOT IN ('L','S','P') |
| # | 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 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 4 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code Set ID |
| 5 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 6 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 7 | EMPL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Payroll Status
A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration |
| 8 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location Set ID |
| 9 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 10 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
| 11 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 12 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 13 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |
| 14 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL | Regulatory Region |
| 15 | WF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Workflow Status
A=Approved C=Cancelled D=Denied E=Error. Contact Administrator. F=Awaiting final approval I=In Approval Process M=Administrator is Processing N=Not Available P=Rework S=Submitted V=Data Saved |
| 16 | ELIGIBLE | Character(1) | VARCHAR2(1) NOT NULL | Eligible |