TL_ESS_JOB_VW3(SQL View) |
Index Back |
|---|---|
Select Job Title ViewThis view is used for selecting current T&L jobs. |
| SELECT A.EMPLID ,A.EMPL_RCD ,A.EFFDT ,A.BUSINESS_UNIT ,A.SETID_DEPT ,A.SETID_JOBCODE ,A.DEPTID ,A.JOBCODE ,JC.DESCR ,B.EFFDT ,B.TIME_RPTR_IND ,A.JOB_INDICATOR ,A.COMPANY ,A.PAYGROUP ,A.GP_PAYGROUP ,A.PAY_SYSTEM_FLG FROM PS_JOB A , PS_TL_EMPL_DATA B , PS_JOBCODE_TBL JC ,PS_DEPT_TBL D WHERE B.TIME_RPTG_STATUS = 'A' AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_TL_EMPL_DATA WHERE PS_TL_EMPL_DATA.EMPLID = B.EMPLID AND PS_TL_EMPL_DATA.EMPL_RCD = B.EMPL_RCD AND EFFDT <= %CurrentDateIn) AND A.EFFDT = ( SELECT MAX (EFFDT) FROM PS_JOB WHERE PS_JOB.EMPLID = A.EMPLID AND PS_JOB.EMPL_RCD = A.EMPL_RCD AND EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(PS_JOB.EFFSEQ) FROM PS_JOB WHERE PS_JOB.EMPLID = A.EMPLID AND PS_JOB.EMPL_RCD = A.EMPL_RCD AND EFFDT = A.EFFDT) AND JC.SETID = A.SETID_JOBCODE AND JC.JOBCODE = A.JOBCODE AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL JC1 WHERE JC1.SETID = A.SETID_JOBCODE AND JC1.JOBCODE = A.JOBCODE AND JC1.EFFDT <= %CurrentDateIn) AND D.SETID = A.SETID_DEPT AND D.DEPTID = A.DEPTID AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_DEPT_TBL D1 WHERE D1.SETID = A.SETID_DEPT AND D1.DEPTID = A.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
Default Value: NEW Prompt Table: PERSON |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | ACTION_DT | Date(10) | DATE | Action Date |
| 4 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT Prompt Table: BUS_UNIT_TBL_HR |
| 5 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 6 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code Set ID |
| 7 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPT_TBL |
| 8 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL |
Job Code
Prompt Table: JOBCODE_TBL |
| 9 | JOB_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Description |
| 10 | DATE_WRK | Date(10) | DATE | Date Work Field |
| 11 | 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 |
| 12 | JOB_INDICATOR | Character(1) | VARCHAR2(1) NOT NULL |
This Indicator is used on JOB to specify which employment record is considered to be the primary one for an employee.
In case of Multiple Jobs per Employee, this property is being used to make a conscious decision in related features where only 1 Employment Record per Employee needs to be returned.
N=Not Applicable P=Primary Job S=Secondary Job |
| 13 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 14 | PAYGROUP | Character(3) | VARCHAR2(3) NOT NULL | Pay Group |
| 15 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 16 | PAY_SYSTEM_FLG | Character(2) | VARCHAR2(2) NOT NULL |
Payroll System
GP=Global Payroll NA=Payroll for North America OT=Other PI=Payroll Interface |