GP_PSLP_PRI_VW2(SQL View) |
Index Back |
|---|---|
CLONE of Primary Job View - HRCLONED FROM PRIMARY_JOB_VW is used to return the most current Primary Job information at a certain Effective Date for an Employee. (1) This view returns the 'active' job with the lowest employment record number where the Job_Indicator is equal to "P"; (2) If no row found under (1), it will return the active job with the lowest empl_rcd#; (3) If no row found under (2), it will return the lowest empl_rcd# where the Job_Indicator is equal to "P"; (4) If no row found under (3), it will return the lowest empl_rcd#. Note: This view will always return 1 row per employee as there should be only one Primary Job. |
| SELECT JOB.EMPLID ,JOB.EMPL_RCD ,JOB.EFFDT ,JOB.EFFSEQ ,JOB.GP_PAYGROUP ,JOB.DEPTID , JOB.EMPL_TYPE , JOB.SETID_JOBCODE , JOB.SUPERVISOR_ID , JOB.COMPRATE , JOB.COMP_FREQUENCY ,JOB.JOBCODE ,JOB.POSITION_NBR ,JOB.EMPL_STATUS ,JOB.LOCATION ,JOB.BUSINESS_UNIT ,JOB.JOB_INDICATOR ,JOB.COMPANY ,JOB.TAX_LOCATION_CD ,REG_TEMP ,FULL_PART_TIME FROM PS_JOB JOB WHERE JOB.JOB_INDICATOR = 'P' AND JOB.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE EMPLID = JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND EFFDT <= %CurrentDateIn) AND JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = JOB.EMPLID AND EMPL_RCD = JOB.EMPL_RCD AND EFFDT = JOB.EFFDT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | EFFDT |
Date(10) | DATE | Effective Date |
| 4 | EFFSEQ | Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 5 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 6 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 7 | EMPL_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Employee Type
E=Exception Hourly H=Hourly N=Not Applicable S=Salaried |
| 8 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code Set ID |
| 9 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 10 | COMPRATE | Number(19,6) | DECIMAL(18,6) NOT NULL | Compensation Rate |
| 11 | COMP_FREQUENCY | Character(5) | VARCHAR2(5) NOT NULL |
Compensation Frequency
A=Annual B=Biweekly C=Contract D=Daily H=Hourly M=Monthly S=Semimonthly W=Weekly |
| 12 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 13 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
| 14 | 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 |
| 15 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 16 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 17 | 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 |
| 18 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 19 | TAX_LOCATION_CD | Character(10) | VARCHAR2(10) NOT NULL | Tax Location Code |
| 20 | REG_TEMP | Character(1) | VARCHAR2(1) NOT NULL |
Regular/Temporary
R=Regular T=Temporary |
| 21 | FULL_PART_TIME | Character(1) | VARCHAR2(1) NOT NULL |
Full/Part Time
D=On Demand F=Full-Time P=Part-Time |