TL_WC_TR_STS_VW(SQL View) |
Index Back |
|---|---|
Time Rpt Sts TL WrkCenterFetches Time Reporter Status for TL Fluid WorkCenter |
| SELECT J.EMPLID , J.EMPL_RCD , N.NAME_DISPLAY , N.LAST_NAME , N.FIRST_NAME , 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 , ' ' , 'N' , TS.TA_STATUS , TS.EARLIEST_CHGDT , TS.PRCS_THRU_DT , TS.LASTUPDDTTM , TS.LAST_PRCS_DTTM , TS.PROCESS_INSTANCE , TS.OPRID , TS.RUN_CNTL_ID FROM PS_TL_TR_STATUS TS , PS_NAMES N , PS_JOBCODE_TBL JC , PS_JOB J , PS_TL_EMPL_DATA ED , PS_DEPT_TBL D WHERE TS.EMPLID = J.EMPLID AND TS.EMPL_RCD=J.EMPL_RCD AND TS.EMPLID = N.EMPLID AND N.NAME_TYPE = 'PRI' AND N.EFFDT = ( SELECT MAX(SN.EFFDT) FROM PS_NAMES SN WHERE SN.EMPLID = N.EMPLID AND SN.NAME_TYPE=N.NAME_TYPE AND SN.EFFDT <= %CurrentDateIn) AND TS.EMPLID = J.EMPLID AND TS.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 <= %CurrentDateIn) AND J.EMPLID = ED.EMPLID AND J.EMPL_RCD = ED.EMPL_RCD 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 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 Record |
| 3 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 4 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 5 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 6 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 7 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 8 | JOB_DESCR | Character(30) | VARCHAR2(30) NOT NULL |
Job Description
Prompt Table: TL_JOB_DESC_VW |
| 9 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPT_TBL |
| 10 | DEPT_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Department Description |
| 11 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL |
Supervisor ID
Prompt Table: TL_SUPRVSR_VW |
| 12 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL |
Reports To Position Number
Prompt Table: TL_RPTS_TO_VW |
| 13 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL |
Location Code
Prompt Table: LOCATION_TBL |
| 14 | LOC_DESCR_SS | Character(30) | VARCHAR2(30) NOT NULL | Location Description |
| 15 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 16 | PAYGROUP | Character(3) | VARCHAR2(3) NOT NULL |
North American Pay Group
Prompt Table: PAYGROUP_TBL |
| 17 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 18 | WORKGROUP | Character(10) | VARCHAR2(10) NOT NULL |
Workgroup
Prompt Table: TL_WRKGRP_TBL |
| 19 | TASKGROUP | Character(10) | VARCHAR2(10) NOT NULL |
Taskgroup
Prompt Table: TL_TASKGRP_TBL |
| 20 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
| 21 | SCHEDULE_GRP | Character(10) | VARCHAR2(10) NOT NULL | Scheduling Organizational Group Code |
| 22 | PROCESSED | Character(1) | VARCHAR2(1) NOT NULL |
Processed?
I=In Process N=Not Processed Y=Processed Y/N Table Edit Default Value: N |
| 23 | TA_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Time Administration Status
N=Not Up For Processing X=In Process Y=Up For Processing |
| 24 | EARLIEST_CHGDT | Date(10) | DATE | Earliest Date of Change |
| 25 | PRCS_THRU_DT | Date(10) | DATE | Process Through Date |
| 26 | LASTUPDDTTM | DateTime(26) | TIMESTAMP | Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts. |
| 27 | LAST_PRCS_DTTM | DateTime(26) | TIMESTAMP | Contains the time an entity was Last Processed. Introduced for tracking TA processing of time reporters. |
| 28 | PROCESS_INSTANCE | Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
| 29 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 30 | RUN_CNTL_ID | Character(30) | VARCHAR2(30) NOT NULL | Run Control ID |