TL_UNPRCS_TM_VW(SQL View) |
Index Back |
|---|---|
Unprocessed Time SummaryThe view returns the employee names and job titles for each employee and job that the operator has access to. |
| 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 ,WG.INCL_ML_BRK_FLG ,TR.EARLIEST_CHGDT ,TR.TA_STATUS ,WG.TIME_RPTG_TYPE FROM PS_JOBCODE_TBL JC ,PS_JOB J ,PS_TL_EMPL_DATA ED ,PS_TL_WRKGRP_TBL WG ,PS_TL_GROUP_DTL F ,PS_TL_GRP_SECURITY S ,PS_NAMES N ,PS_DEPT_TBL D ,PS_TL_TR_STATUS TR 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 F.EMPLID = TR.EMPLID AND F.EMPL_RCD = TR.EMPL_RCD AND ED.WORKGROUP = WG.WORKGROUP 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 |
| 18 | INCL_ML_BRK_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Include in Totals
1=Exclude Meals and Breaks 2=Include Meals 3=Include Breaks 4=Include Breaks and Meals |
| 19 | EARLIEST_CHGDT | Date(10) | DATE | Earliest Date of Change |
| 20 | TA_STATUS | Character(1) | VARCHAR2(1) NOT NULL | Time Administration Run Needed |
| 21 | TIME_RPTG_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Time Reporting Type
E=Exception Time Reporting P=Positive Time Reporting |