SCH_MGR_DAILY(SQL View) |
Index Back |
|---|---|
Admin/Manager Search VwSearch view intended for use by administrative operators such as data entry clerks or with Manager events. The view returns the employee names and job titles for each employee and job that the operator has access to. Access is determined by the TL Group Security defined for the Operator's Row Security Class. When the Operator's Row Security Class has no specific Group Security, HR Department Security is used to determine access. |
| SELECT J.EMPLID ,J.EMPL_RCD ,N.NAME_DISPLAY ,J.BUSINESS_UNIT ,J.JOBCODE ,JC.DESCR ,J.DEPTID ,D.DESCR ,J.SUPERVISOR_ID ,J.REPORTS_TO ,J.LOCATION ,J.COMPANY ,J.PAYGROUP ,J.GP_PAYGROUP ,ED.WORKGROUP ,ED.TASKGROUP ,J.POSITION_NBR FROM PS_JOBCODE_TBL JC , PS_JOB J , PS_TL_EMPL_DATA ED , PS_TL_GROUP_DTL F ,PS_TL_GRP_SECURITY S ,PS_NAMES N , PS_DEPT_TBL D WHERE S.GROUP_ID = F.GROUP_ID AND F.EMPLID = J.EMPLID AND F.EMPL_RCD = J.EMPL_RCD AND F.EMPLID = N.EMPLID AND N.NAME_TYPE = 'PRI' AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N1.EMPLID = N.EMPLID AND N1.NAME_TYPE= N.NAME_TYPE AND N1.EFFDT <= %CurrentDateIn) AND F.EMPLID = ED.EMPLID AND F.EMPL_RCD = ED.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD AND J2.EFFDT = J.EFFDT ) AND ED.EFFDT = ( SELECT MAX(ED1.EFFDT) FROM PS_TL_EMPL_DATA ED1 WHERE ED1.EMPLID = ED.EMPLID AND ED1.EMPL_RCD = ED.EMPL_RCD AND ED1.EFFDT <= %CurrentDateIn) AND JC.SETID = J.SETID_JOBCODE AND JC.JOBCODE = J.JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = J.SETID_JOBCODE AND JC1.JOBCODE = J.JOBCODE AND JC1.EFFDT <= %CurrentDateIn) AND D.SETID = J.SETID_DEPT AND D.DEPTID = J.DEPTID AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID = J.SETID_DEPT AND D1.DEPTID = J.DEPTID AND D1.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: TL_EMPL_NAME_VW |
| 2 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Rcd Nbr |
| 3 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 4 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 5 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 6 | JOB_DESCR | Character(30) | VARCHAR2(30) NOT NULL |
Job Description
Prompt Table: TL_JOB_DESC_VW |
| 7 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPT_TBL |
| 8 | DEPT_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Department Description |
| 9 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL |
Supervisor ID
Prompt Table: TL_SUPRVSR_VW |
| 10 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL |
Reports To Position Number
Prompt Table: TL_RPTS_TO_VW |
| 11 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL |
Location Code
Prompt Table: LOCATION_TBL |
| 12 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 13 | PAYGROUP | Character(3) | VARCHAR2(3) NOT NULL |
Pay Group
Prompt Table: PAYGROUP_TBL |
| 14 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 15 | WORKGROUP | Character(10) | VARCHAR2(10) NOT NULL |
Workgroup
Prompt Table: TL_WRKGRP_TBL |
| 16 | TASKGROUP | Character(10) | VARCHAR2(10) NOT NULL |
Taskgroup
Prompt Table: TL_TASKGRP_TBL |
| 17 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |