TL_EMPLM_SCH_FL

(SQL View)
 Index Back

TL Empl Data Search

Time Reporter Search View for Maintain Time Reporter Data and create time reporter data

( SELECT O.OPRID , CL.CLASSID , E.EMPLID , E.EMPL_RCD , P.NAME , P.FIRST_NAME_SRCH , P.LAST_NAME_SRCH , J.BUSINESS_UNIT , J.SETID_DEPT , J.DEPTID , E.WORKGROUP , W.DESCR , J.PER_ORG , E.TASKGROUP , T.DESCR , E.TIME_RPTR_IND , 'Yes' FROM PS_PERSON_NAME P , PS_JOB J , PSCLASSDEFN CL , PSOPRDEFN O , PS_TL_EMPL_DATA E , PS_TL_WRKGRP_TBL W , PS_TL_TASKGRP_TBL T WHERE P.EMPLID = J.EMPLID AND ((EXISTS ( SELECT 'X' FROM PS_TL_GRP_SECURITY S WHERE S.ROWSECCLASS = CL.CLASSID) AND J.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 = J.EMPLID AND F.EMPL_RCD = J.EMPL_RCD AND S.ROWSECCLASS = CL.CLASSID)) OR (J.EMPLID IN ( SELECT SEC.EMPLID FROM %Sql(SCRTY_PER_FROM) WHERE %Sql(SCRTY_NO_APPT1) AND %Sql(SCRTY_WHERE, 'PPLJOB') AND J.EMPLID = SEC.EMPLID AND J.EMPL_RCD = SEC.EMPL_RCD AND OPR.ROWSECCLASS = O.ROWSECCLASS AND OPR.OPRID = O.OPRID))) AND E.WORKGROUP = W.WORKGROUP AND E.TASKGROUP = T.TASKGROUP AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD) 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 J.EMPLID = E.EMPLID AND J.EMPL_RCD = E.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 W.EFFDT = ( SELECT MAX(W1.EFFDT) FROM PS_TL_WRKGRP_TBL W1 WHERE W1.WORKGROUP = W.WORKGROUP AND W1.EFFDT <= E.EFFDT) AND T.EFFDT = ( SELECT MAX(T1.EFFDT) FROM PS_TL_TASKGRP_TBL T1 WHERE T1.TASKGROUP = T.TASKGROUP AND T1.EFFDT <= E.EFFDT)) UNION ( SELECT O.OPRID , CL.CLASSID , J.EMPLID , J.EMPL_RCD , P.NAME , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , 'No' FROM PS_PERSON_NAME P , PS_JOB J , PSCLASSDEFN CL , PSOPRDEFN O WHERE J.EMPLID NOT IN ( SELECT DISTINCT E.EMPLID FROM PS_TL_EMPL_DATA E WHERE E.EMPLID = J.EMPLID AND E.EMPL_RCD = J.EMPL_RCD) AND P.EMPLID = J.EMPLID AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD) 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))

# 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 NAME Character(50) VARCHAR2(50) NOT NULL Name
6 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
7 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
9 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
11 WORKGROUP Character(10) VARCHAR2(10) NOT NULL Workgroup
12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
13 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest
14 TASKGROUP Character(10) VARCHAR2(10) NOT NULL Taskgroup
15 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
16 TIME_RPTR_IND Character(1) VARCHAR2(1) NOT NULL Indicates whether a Time Reporter is primarily reports Elapsed Time or Punch Time.
E=Elapsed Time Reporter
P=Punch Time Reporter
17 ENROLLED_FLAG Character(1) VARCHAR2(1) NOT NULL Enrolled Flag

Y/N Table Edit