SUPERVISOR_IDSUPERVIS_EMPL_RCDEMPLIDEMPL_RCDEFFDTDefault Value: %date
LANGUAGE_CDHR_TM_COMP_LVW(SQL View) |
Index Back |
|---|---|
Scatter Plot Pivot Lang ViewScatter Plot Pivot Base Lang View |
| SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , B.EFFDT , DEPTLNG.LANGUAGE_CD , DEPTLNG.DESCR , LOCLNG.DESCR , JTBLLNG.DESCR FROM PS_HR_TM_PG_DIR_VW A , PS_JOB B , PS_DEPT_TBL_LANG DEPTLNG , PS_LOCATION_LANG LOCLNG , PS_JOBCODE_LANG JTBLLNG WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOB_EFFDT = B.EFFDT AND B.SETID_DEPT = DEPTLNG.SETID AND B.SETID_LOCATION = LOCLNG.SETID AND B.SETID_JOBCODE = JTBLLNG.SETID AND B.DEPTID = DEPTLNG.DEPTID AND B.LOCATION = LOCLNG.LOCATION AND B.JOBCODE = JTBLLNG.JOBCODE AND LOCLNG.LANGUAGE_CD = DEPTLNG.LANGUAGE_CD AND JTBLLNG.LANGUAGE_CD = DEPTLNG.LANGUAGE_CD AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND DEPTLNG.EFFDT = ( SELECT MAX(DEPT_ED.EFFDT) FROM PS_DEPT_TBL_LANG DEPT_ED WHERE DEPT_ED.SETID = DEPTLNG.SETID AND DEPT_ED.DEPTID = DEPTLNG.DEPTID AND DEPT_ED.LANGUAGE_CD = DEPTLNG.LANGUAGE_CD AND DEPT_ED.EFFDT <= %CurrentDateIn) AND LOCLNG.EFFDT = ( SELECT MAX(LOC_ED.EFFDT) FROM PS_LOCATION_LANG LOC_ED WHERE LOC_ED.SETID = LOCLNG.SETID AND LOC_ED.LOCATION = LOCLNG.LOCATION AND LOC_ED.LANGUAGE_CD = LOCLNG.LANGUAGE_CD AND LOC_ED.EFFDT <= %CurrentDateIn) AND JTBLLNG.EFFDT = ( SELECT MAX(JC_ED.EFFDT) FROM PS_JOBCODE_LANG JC_ED WHERE JC_ED.SETID = JTBLLNG.SETID AND JC_ED.JOBCODE = JTBLLNG.JOBCODE AND JC_ED.LANGUAGE_CD = JTBLLNG.LANGUAGE_CD AND JC_ED.EFFDT <= %CurrentDateIn) AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') UNION SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , B.EFFDT , LOCLNG.LANGUAGE_CD , DEPT.DESCR , LOCLNG.DESCR , JTBLLNG.DESCR FROM PS_HR_TM_PG_DIR_VW A , PS_JOB B , PS_DEPT_TBL DEPT , PS_LOCATION_LANG LOCLNG , PS_JOBCODE_LANG JTBLLNG WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOB_EFFDT = B.EFFDT AND B.SETID_DEPT = DEPT.SETID AND B.SETID_LOCATION = LOCLNG.SETID AND B.SETID_JOBCODE = JTBLLNG.SETID AND B.DEPTID = DEPT.DEPTID AND B.LOCATION = LOCLNG.LOCATION AND B.JOBCODE = JTBLLNG.JOBCODE AND JTBLLNG.LANGUAGE_CD = LOCLNG.LANGUAGE_CD AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND DEPT.EFFDT = ( SELECT MAX(DEPT_ED.EFFDT) FROM PS_DEPT_TBL DEPT_ED WHERE DEPT_ED.SETID = DEPT.SETID AND DEPT_ED.DEPTID = DEPT.DEPTID AND DEPT_ED.EFFDT <= %CurrentDateIn) AND LOCLNG.EFFDT = ( SELECT MAX(LOC_ED.EFFDT) FROM PS_LOCATION_LANG LOC_ED WHERE LOC_ED.SETID = LOCLNG.SETID AND LOC_ED.LOCATION = LOCLNG.LOCATION AND LOC_ED.LANGUAGE_CD = LOCLNG.LANGUAGE_CD AND LOC_ED.EFFDT <= %CurrentDateIn) AND JTBLLNG.EFFDT = ( SELECT MAX(JC_ED.EFFDT) FROM PS_JOBCODE_LANG JC_ED WHERE JC_ED.SETID = JTBLLNG.SETID AND JC_ED.JOBCODE = JTBLLNG.JOBCODE AND JC_ED.LANGUAGE_CD = JTBLLNG.LANGUAGE_CD AND JC_ED.EFFDT <= %CurrentDateIn) AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') AND NOT EXISTS ( SELECT 'X' FROM PS_DEPT_TBL_LANG DEPTLNG WHERE DEPTLNG.SETID = DEPT.SETID AND DEPTLNG.DEPTID = DEPT.DEPTID AND DEPTLNG.LANGUAGE_CD = LOCLNG.LANGUAGE_CD AND DEPTLNG.EFFDT <= %CurrentDateIn) UNION SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , B.EFFDT , JTBLLNG.LANGUAGE_CD , DEPT.DESCR , LOC.DESCR , JTBLLNG.DESCR FROM PS_HR_TM_PG_DIR_VW A , PS_JOB B , PS_DEPT_TBL DEPT , PS_LOCATION_TBL LOC , PS_JOBCODE_LANG JTBLLNG WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOB_EFFDT = B.EFFDT AND B.SETID_DEPT = DEPT.SETID AND B.SETID_LOCATION = LOC.SETID AND B.SETID_JOBCODE = JTBLLNG.SETID AND B.DEPTID = DEPT.DEPTID AND B.LOCATION = LOC.LOCATION AND B.JOBCODE = JTBLLNG.JOBCODE AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND DEPT.EFFDT = ( SELECT MAX(DEPT_ED.EFFDT) FROM PS_DEPT_TBL DEPT_ED WHERE DEPT_ED.SETID = DEPT.SETID AND DEPT_ED.DEPTID = DEPT.DEPTID AND DEPT_ED.EFFDT <= %CurrentDateIn) AND LOC.EFFDT = ( SELECT MAX(LOC_ED.EFFDT) FROM PS_LOCATION_TBL LOC_ED WHERE LOC_ED.SETID = LOC.SETID AND LOC_ED.LOCATION = LOC.LOCATION AND LOC_ED.EFFDT <= %CurrentDateIn) AND JTBLLNG.EFFDT = ( SELECT MAX(JC_ED.EFFDT) FROM PS_JOBCODE_LANG JC_ED WHERE JC_ED.SETID = JTBLLNG.SETID AND JC_ED.JOBCODE = JTBLLNG.JOBCODE AND JC_ED.LANGUAGE_CD = JTBLLNG.LANGUAGE_CD AND JC_ED.EFFDT <= %CurrentDateIn) AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') AND NOT EXISTS ( SELECT 'X' FROM PS_DEPT_TBL_LANG DEPTLNG WHERE DEPTLNG.SETID = DEPT.SETID AND DEPTLNG.DEPTID = DEPT.DEPTID AND DEPTLNG.LANGUAGE_CD = JTBLLNG.LANGUAGE_CD AND DEPTLNG.EFFDT <= %CurrentDateIn) AND NOT EXISTS ( SELECT 'X' FROM PS_LOCATION_LANG LOCLNG WHERE LOCLNG.SETID = LOC.SETID AND LOCLNG.LOCATION = LOC.LOCATION AND LOCLNG.LANGUAGE_CD = JTBLLNG.LANGUAGE_CD AND LOCLNG.EFFDT <= %CurrentDateIn) UNION SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , B.EFFDT , DEPTLNG.LANGUAGE_CD , DEPTLNG.DESCR , LOC.DESCR , JTBLLNG.DESCR FROM PS_HR_TM_PG_DIR_VW A , PS_JOB B , PS_DEPT_TBL_LANG DEPTLNG , PS_LOCATION_TBL LOC , PS_JOBCODE_LANG JTBLLNG WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOB_EFFDT = B.EFFDT AND B.SETID_DEPT = DEPTLNG.SETID AND B.SETID_LOCATION = LOC.SETID AND B.SETID_JOBCODE = JTBLLNG.SETID AND B.DEPTID = DEPTLNG.DEPTID AND B.LOCATION = LOC.LOCATION AND B.JOBCODE = JTBLLNG.JOBCODE AND DEPTLNG.LANGUAGE_CD = JTBLLNG.LANGUAGE_CD AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND DEPTLNG.EFFDT = ( SELECT MAX(DEPT_ED.EFFDT) FROM PS_DEPT_TBL_LANG DEPT_ED WHERE DEPT_ED.SETID = DEPTLNG.SETID AND DEPT_ED.DEPTID = DEPTLNG.DEPTID AND DEPT_ED.LANGUAGE_CD = DEPTLNG.LANGUAGE_CD AND DEPT_ED.EFFDT <= %CurrentDateIn) AND LOC.EFFDT = ( SELECT MAX(LOC_ED.EFFDT) FROM PS_LOCATION_TBL LOC_ED WHERE LOC_ED.SETID = LOC.SETID AND LOC_ED.LOCATION = LOC.LOCATION AND LOC_ED.EFFDT <= %CurrentDateIn) AND JTBLLNG.EFFDT = ( SELECT MAX(JC_ED.EFFDT) FROM PS_JOBCODE_LANG JC_ED WHERE JC_ED.SETID = JTBLLNG.SETID AND JC_ED.JOBCODE = JTBLLNG.JOBCODE AND JC_ED.LANGUAGE_CD = JTBLLNG.LANGUAGE_CD AND JC_ED.EFFDT <= %CurrentDateIn) AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') AND NOT EXISTS ( SELECT 'X' FROM PS_LOCATION_LANG LOCLNG WHERE LOCLNG.SETID = LOC.SETID AND LOCLNG.LOCATION = LOC.LOCATION AND LOCLNG.LANGUAGE_CD = DEPTLNG.LANGUAGE_CD AND LOCLNG.EFFDT <= %CurrentDateIn) UNION SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , B.EFFDT , DEPTLNG.LANGUAGE_CD , DEPTLNG.DESCR , LOCLNG.DESCR , JOBCD.DESCR FROM PS_HR_TM_PG_DIR_VW A , PS_JOB B , PS_DEPT_TBL_LANG DEPTLNG , PS_LOCATION_LANG LOCLNG , PS_JOBCODE_TBL JOBCD WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOB_EFFDT = B.EFFDT AND B.SETID_DEPT = DEPTLNG.SETID AND B.SETID_LOCATION = LOCLNG.SETID AND B.SETID_JOBCODE = JOBCD.SETID AND B.DEPTID = DEPTLNG.DEPTID AND B.LOCATION = LOCLNG.LOCATION AND B.JOBCODE = JOBCD.JOBCODE AND DEPTLNG.LANGUAGE_CD = LOCLNG.LANGUAGE_CD AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND DEPTLNG.EFFDT = ( SELECT MAX(DEPT_ED.EFFDT) FROM PS_DEPT_TBL_LANG DEPT_ED WHERE DEPT_ED.SETID = DEPTLNG.SETID AND DEPT_ED.DEPTID = DEPTLNG.DEPTID AND DEPT_ED.LANGUAGE_CD = DEPTLNG.LANGUAGE_CD AND DEPT_ED.EFFDT <= %CurrentDateIn) AND LOCLNG.EFFDT = ( SELECT MAX(LOC_ED.EFFDT) FROM PS_LOCATION_LANG LOC_ED WHERE LOC_ED.SETID = LOCLNG.SETID AND LOC_ED.LOCATION = LOCLNG.LOCATION AND LOC_ED.LANGUAGE_CD = LOCLNG.LANGUAGE_CD AND LOC_ED.EFFDT <= %CurrentDateIn) AND JOBCD.EFFDT = ( SELECT MAX(JC_ED.EFFDT) FROM PS_JOBCODE_TBL JC_ED WHERE JC_ED.SETID = JOBCD.SETID AND JC_ED.JOBCODE = JOBCD.JOBCODE AND JC_ED.EFFDT <= %CurrentDateIn) AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') AND NOT EXISTS ( SELECT 'X' FROM PS_JOBCODE_LANG JCLNG WHERE JCLNG.SETID = JOBCD.SETID AND JCLNG.JOBCODE = JOBCD.JOBCODE AND JCLNG.LANGUAGE_CD = DEPTLNG.LANGUAGE_CD AND JCLNG.EFFDT <= %CurrentDateIn) UNION SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , B.EFFDT , LOCLNG.LANGUAGE_CD , DEPT.DESCR , LOCLNG.DESCR , JOBCD.DESCR FROM PS_HR_TM_PG_DIR_VW A , PS_JOB B , PS_DEPT_TBL DEPT , PS_LOCATION_LANG LOCLNG , PS_JOBCODE_TBL JOBCD WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOB_EFFDT = B.EFFDT AND B.SETID_DEPT = DEPT.SETID AND B.SETID_LOCATION = LOCLNG.SETID AND B.SETID_JOBCODE = JOBCD.SETID AND B.DEPTID = DEPT.DEPTID AND B.LOCATION = LOCLNG.LOCATION AND B.JOBCODE = JOBCD.JOBCODE AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND DEPT.EFFDT = ( SELECT MAX(DEPT_ED.EFFDT) FROM PS_DEPT_TBL DEPT_ED WHERE DEPT_ED.SETID = DEPT.SETID AND DEPT_ED.DEPTID = DEPT.DEPTID AND DEPT_ED.EFFDT <= %CurrentDateIn) AND LOCLNG.EFFDT = ( SELECT MAX(LOC_ED.EFFDT) FROM PS_LOCATION_LANG LOC_ED WHERE LOC_ED.SETID = LOCLNG.SETID AND LOC_ED.LOCATION = LOCLNG.LOCATION AND LOC_ED.LANGUAGE_CD = LOCLNG.LANGUAGE_CD AND LOC_ED.EFFDT <= %CurrentDateIn) AND JOBCD.EFFDT = ( SELECT MAX(JC_ED.EFFDT) FROM PS_JOBCODE_TBL JC_ED WHERE JC_ED.SETID = JOBCD.SETID AND JC_ED.JOBCODE = JOBCD.JOBCODE AND JC_ED.EFFDT <= %CurrentDateIn) AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') AND NOT EXISTS ( SELECT 'X' FROM PS_DEPT_TBL_LANG DEPTLNG WHERE DEPTLNG.SETID = DEPT.SETID AND DEPTLNG.DEPTID = DEPT.DEPTID AND DEPTLNG.LANGUAGE_CD = LOCLNG.LANGUAGE_CD AND DEPTLNG.EFFDT <= %CurrentDateIn) AND NOT EXISTS ( SELECT 'X' FROM PS_JOBCODE_LANG JCLNG WHERE JCLNG.SETID = JOBCD.SETID AND JCLNG.JOBCODE = JOBCD.JOBCODE AND JCLNG.LANGUAGE_CD = LOCLNG.LANGUAGE_CD AND JCLNG.EFFDT <= %CurrentDateIn) UNION SELECT A.SUPERVISOR_ID , A.SUPERVIS_EMPL_RCD , A.EMPLID , A.EMPL_RCD , B.EFFDT , DEPTLNG.LANGUAGE_CD , DEPTLNG.DESCR , LOC.DESCR , JOBCD.DESCR FROM PS_HR_TM_PG_DIR_VW A , PS_JOB B , PS_DEPT_TBL_LANG DEPTLNG , PS_LOCATION_TBL LOC , PS_JOBCODE_TBL JOBCD WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.JOB_EFFDT = B.EFFDT AND B.SETID_DEPT = DEPTLNG.SETID AND B.SETID_LOCATION = LOC.SETID AND B.SETID_JOBCODE = JOBCD.SETID AND B.DEPTID = DEPTLNG.DEPTID AND B.LOCATION = LOC.LOCATION AND B.JOBCODE = JOBCD.JOBCODE AND B.EFFSEQ = ( SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2 WHERE JB2.EMPLID = B.EMPLID AND JB2.EFFDT = B.EFFDT AND JB2.EMPL_RCD = B.EMPL_RCD) AND DEPTLNG.EFFDT = ( SELECT MAX(DEPT_ED.EFFDT) FROM PS_DEPT_TBL_LANG DEPT_ED WHERE DEPT_ED.SETID = DEPTLNG.SETID AND DEPT_ED.DEPTID = DEPTLNG.DEPTID AND DEPT_ED.LANGUAGE_CD = DEPTLNG.LANGUAGE_CD AND DEPT_ED.EFFDT <= %CurrentDateIn) AND LOC.EFFDT = ( SELECT MAX(LOC_ED.EFFDT) FROM PS_LOCATION_TBL LOC_ED WHERE LOC_ED.SETID = LOC.SETID AND LOC_ED.LOCATION = LOC.LOCATION AND LOC_ED.EFFDT <= %CurrentDateIn) AND JOBCD.EFFDT = ( SELECT MAX(JC_ED.EFFDT) FROM PS_JOBCODE_TBL JC_ED WHERE JC_ED.SETID = JOBCD.SETID AND JC_ED.JOBCODE = JOBCD.JOBCODE AND JC_ED.EFFDT <= %CurrentDateIn) AND (B.PER_ORG = 'EMP' OR B.PER_ORG = 'CWR') AND NOT EXISTS ( SELECT 'X' FROM PS_LOCATION_LANG LOCLNG WHERE LOCLNG.SETID = LOC.SETID AND LOCLNG.LOCATION = LOC.LOCATION AND LOCLNG.LANGUAGE_CD = DEPTLNG.LANGUAGE_CD AND LOCLNG.EFFDT <= %CurrentDateIn) AND NOT EXISTS ( SELECT 'X' FROM PS_JOBCODE_LANG JCLNG WHERE JCLNG.SETID = JOBCD.SETID AND JCLNG.JOBCODE = JOBCD.JOBCODE AND JCLNG.LANGUAGE_CD = DEPTLNG.LANGUAGE_CD AND JCLNG.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SUPERVISOR_ID |
Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 2 | SUPERVIS_EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Supervisor employee record. |
| 3 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 4 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 5 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 6 | LANGUAGE_CD |
Character(3) | VARCHAR2(3) NOT NULL | Language Code |
| 7 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 8 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |
| 9 | DESCR3 | Character(30) | VARCHAR2(30) NOT NULL | Descr 3 |