SCH_MGR_DAILY

(SQL View)
 Index Back

Admin/Manager Search Vw

Search 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 , N.LAST_NAME , N.FIRST_NAME , 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 , ' ' , 'N' 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.TL_GROUP_ID = F.TL_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 Record
3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
5 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_HR

7 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
8 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description

Prompt Table: TL_JOB_DESC_VW

9 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

10 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
11 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

Prompt Table: TL_SUPRVSR_VW

12 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number

Prompt Table: TL_RPTS_TO_VW

13 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

14 LOC_DESCR_SS Character(30) VARCHAR2(30) NOT NULL Location Description
15 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

16 PAYGROUP Character(3) VARCHAR2(3) NOT NULL North American Pay Group

Prompt Table: PAYGROUP_TBL

17 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
18 WORKGROUP Character(10) VARCHAR2(10) NOT NULL Workgroup

Prompt Table: TL_WRKGRP_TBL

19 TASKGROUP Character(10) VARCHAR2(10) NOT NULL Taskgroup

Prompt Table: TL_TASKGRP_TBL

20 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
21 SCHEDULE_GRP Character(10) VARCHAR2(10) NOT NULL Scheduling Organizational Group Code
22 PROCESSED Character(1) VARCHAR2(1) NOT NULL Processed?
I=In Process
N=Not Processed
Y=Processed

Y/N Table Edit

Default Value: N