TL_LEAV_COMP_VW(SQL View) |
Index Back |
|---|---|
Leave and Comp Summary ViewLeave and Compensatory Time Summary View |
| 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' ,%DateNull ,%DateNull ,' ' ,' ' ,0 ,0 ,0 ,0 ,0 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.TL_GROUP_ID = F.TL_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(SN.EFFDT) FROM PS_NAMES SN WHERE SN.EMPLID = N.EMPLID AND SN.NAME_TYPE=N.NAME_TYPE AND SN.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 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 | ACCRUAL_PROC_DT | Date(10) | DATE | Accrual Process Date |
| 24 | EXP_DT | Date(10) | DATE | Expected Completion Date |
| 25 | COMP_TIME_PLAN | Character(10) | VARCHAR2(10) NOT NULL | Compensatory Time Off Plan |
| 26 | PLAN_TYPE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Plan Type Description |
| 27 | HRS_CARRYOVER | Signed Number(13,6) | DECIMAL(11,6) NOT NULL |
Hours Carried Over-Prev Year
Default Value: 0 |
| 28 | HRS_TAKEN_YTD | Signed Number(9,3) | DECIMAL(7,3) NOT NULL | Starting Balance |
| 29 | HOURS_EARNED | Signed Number(12,6) | DECIMAL(10,6) NOT NULL |
Hours Earned
Default Value: 0 |
| 30 | HRS_TAKEN | Signed Number(9,3) | DECIMAL(7,3) NOT NULL |
Hours Taken
Default Value: 0 |
| 31 | END_BAL | Signed Number(9,3) | DECIMAL(7,3) NOT NULL |
Ending Balance
Default Value: 0 |