TL_MGR_APR_VW

(SQL View)
 Index Back

T&L Exception

This table stores one approval row for each Employee/Empl_Rcd/TRC Type where the status = 'NA' and the rows match the date search criteria.

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