JOB_OTH_VW2(SQL View) |
Index Back |
|---|---|
Other Job RecordsJOB_OTH_VW2 is an addition to JOB_OTH_VW and is used within Job Data to screen on Multiple Jobs. It selects all 'other' Concurrent Job records in Add-mode for the 'active' Employment record. Only the highest Sequence Numbers by Effective Date are considered. This extra view was required, because in Add-mode the new Employment record does not exist in the database yet and therefor the join in JOB_OTH_VW was not returning any row. |
| SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.EMPL_STATUS , A.JOB_INDICATOR , A.STD_HOURS , A.STD_HRS_FREQUENCY , A.FTE , A.ACTION , A.APPT_TYPE , A.COMPANY , A.BUSINESS_UNIT , A.SAL_ADMIN_PLAN , A.ACTION_REASON FROM PS_JOB A WHERE A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT = A.EFFDT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD_OTH |
Number(3,0) | SMALLINT NOT NULL | Other Employment Record |
| 3 | EFFDT_OTH |
Date(10) | DATE | Other Effective Date |
| 4 | EMPL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Payroll Status
A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration |
| 5 | 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 |
| 6 | STD_HOURS | Number(7,2) | DECIMAL(6,2) NOT NULL | Standard Hours |
| 7 | STD_HRS_FREQUENCY | Character(5) | VARCHAR2(5) NOT NULL | Standard Work Period |
| 8 | FTE | Number(8,6) | DECIMAL(7,6) NOT NULL | This field represent Full Time Equivalence |
| 9 | ACTION | Character(3) | VARCHAR2(3) NOT NULL |
Action
Prompt Table: ACTION_TBL |
| 10 | APPT_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
This field tracks whether a row is a Main Appointment (which all rows outside Japan are), or an Additional Appointment, which is a feature only used by Japanese customers.
0=Core Appointment 1=Japan Kenmu Appointment 2=French Public Sector Appt 3=Staffing |
| 11 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 12 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 13 | SAL_ADMIN_PLAN | Character(4) | VARCHAR2(4) NOT NULL | Salary Administration Plan |
| 14 | ACTION_REASON | Character(3) | VARCHAR2(3) NOT NULL | Reason Code |