TL_ADM_SRCH_VW

(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 OPR1.OPRID , OPR1.ROWSECCLASS , E.EMPLID , E.EMPL_RCD , E.TIME_RPTG_STATUS , E.WORKGROUP , E.TASKGROUP , E.TASK_PROFILE_ID , E.ELP_TR_TMPLT_ID , E.PCH_TR_TMPLT_ID , E.PERIOD_ID , P.NAME , J.SETID_DEPT , J.DEPTID , J.JOBCODE , JC.DESCR , J.EFFDT , J.COMPANY ,J.PAYGROUP ,J.SUPERVISOR_ID ,J.REPORTS_TO ,J.POSITION_NBR ,J.GP_PAYGROUP ,J.LOCATION ,J.BUSINESS_UNIT ,P.NAME_DISPLAY FROM PS_JOBCODE_TBL JC , PS_JOB J , PS_PERSON_NAME P , PS_TL_EMPL_DATA E , PSCLASSDEFN O , PSOPRDEFN OPR1 WHERE OPR1.ROWSECCLASS = O.CLASSID AND ( (EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID) AND E.EMPLID IN ( SELECT F.EMPLID FROM PS_TL_GRP_SECURITY S , PS_TL_GROUP_DTL F WHERE S.TL_GROUP_ID = F.TL_GROUP_ID AND F.EMPLID = E.EMPLID AND F.EMPL_RCD = E.EMPL_RCD AND S.ROWSECCLASS = O.CLASSID )) OR (NOT EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = O.CLASSID ) AND E.EMPLID IN ( SELECT SEC.EMPLID FROM %Sql(SCRTY_PER_FROM2) WHERE %Sql(SCRTY_NO_APPT1) AND %Sql(SCRTY_WHERE2, 'PPLJOB') AND E.EMPLID = SEC.EMPLID AND E.EMPL_RCD = SEC.EMPL_RCD AND OPR.ROWSECCLASS = OPR1.ROWSECCLASS AND OPR.OPRID = OPR1.OPRID) ) ) AND E.EFFDT = ( SELECT MAX(E1.EFFDT) FROM PS_TL_EMPL_DATA E1 WHERE E1.EMPLID = E.EMPLID AND E1.EMPL_RCD = E.EMPL_RCD AND E1.EFFDT <= %CurrentDateIn ) AND E.EMPLID = P.EMPLID AND E.EMPLID = J.EMPLID AND E.EMPL_RCD = J.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 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 <= J.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
5 TIME_RPTG_STATUS Character(1) VARCHAR2(1) NOT NULL Time Reporter Status
A=Active
I=Inactive
6 WORKGROUP Character(10) VARCHAR2(10) NOT NULL Workgroup
7 TASKGROUP Character(10) VARCHAR2(10) NOT NULL Taskgroup
8 TASK_PROFILE_ID Character(10) VARCHAR2(10) NOT NULL Task Profile ID
9 ELP_TR_TMPLT_ID Character(10) VARCHAR2(10) NOT NULL Elapsed Reporting Template
10 PCH_TR_TMPLT_ID Character(10) VARCHAR2(10) NOT NULL Punch Reporting Template
11 PERIOD_ID Character(12) VARCHAR2(12) NOT NULL Time Period ID
12 NAME Character(50) VARCHAR2(50) NOT NULL Name
13 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
15 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
16 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
17 TL_SYSTEM_DT Date(10) DATE TL System Date
18 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
19 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
20 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
21 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number
22 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number
23 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
24 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
25 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
26 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country