TL_ESSD_SRCH_VW (SQL View)	Index Back
Self Service Search Vw Search view intended for use with Self Service events that are not date driven. For the Operator who is logged in, the view returns a row for each current job associated to the Operator's Employee ID.

TL_ESSD_SRCH_VW

(SQL View)

Self Service Search Vw

Search view intended for use with Self Service events that are not date driven. For the Operator who is logged in, the view returns a row for each current job associated to the Operator's Employee ID.

SELECT O.OPRID , E.EMPLID , E.EMPL_RCD , %CurrentDateIn , P.NAME , J.DEPTID , J.JOBCODE , JC.DESCR FROM PS_TL_EMPL_DATA E , PS_PERSON_NAME P , PS_JOB J , PS_JOBCODE_TBL JC , PS_TL_OPR_CLASS_VW O WHERE E.EMPLID = O.EMPLID AND E.EMPLID = P.EMPLID AND E.EMPLID = J.EMPLID AND E.EMPL_RCD = J.EMPL_RCD 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 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
Prompt Table: TL_ESS_SRCH_VW

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
Prompt Table: TL_ESS_EERCD_VW

4 DUR Date(10) DATE Date Under Report associates time being reported, scheduled or paid, to a specific date.
Default Value: %DATE

5 NAME Character(50) VARCHAR2(50) NOT NULL Name

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

7 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

8 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description

#	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	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID Prompt Table: TL_ESS_SRCH_VW
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record Prompt Table: TL_ESS_EERCD_VW
4	DUR	Date(10)	DATE	Date Under Report associates time being reported, scheduled or paid, to a specific date. Default Value: %DATE
5	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
6	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
7	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
8	JOBCODE_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Job Code Description