GP_WC_TERMINA_Q(SQL View) |
Index Back |
|---|---|
GP WC TerminateGP WC Terminate |
| SELECT A.EMPLID , A.CAL_RUN_ID , A.CAL_ID , A.RUN_TYPE , B.FIRST_NAME , B.LAST_NAME , A.EMPL_RCD , D.NATIONAL_ID , A.ACTION , A.LAST_HIRE_DT , A.TERMINATION_DT , A.COMPANY , A.BUSINESS_UNIT , A.DEPTID , A.ESTABID , C.LOCATION_DESCR , A.JOBCODE , C.JOBCODE_DESCR , A.HR_STATUS , A.EMPL_STATUS , A.GP_PAYGROUP , A.EMPL_CLASS , C.CONTRACT_TYPE , A.MONTHLY_RT , A.FTE , F.PIN_GROSS_VAL , F.PIN_NET_VAL , A.GP_WORKITEM_TYPE , A.LOCATION , A.PER_ORG , A.COMPRATE , C.CONTRACT_NUM FROM PS_GP_WC_TERMINATE A , PS_GP_PYE_SEG_STAT F , PS_PERS_NID D , PS_PERSON_NAME B , PS_GP_JB_DESCR_VW C WHERE ( ( A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_GP_WC_TERMINATE A_ED WHERE A.CAL_RUN_ID = A_ED.CAL_RUN_ID AND A.CAL_ID = A_ED.CAL_ID AND A.RUN_TYPE = A_ED.RUN_TYPE AND A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= F.SEG_END_DT + 1) AND A.CAL_RUN_ID = F.CAL_RUN_ID AND A.CAL_ID = F.CAL_ID AND A.EMPLID = F.EMPLID AND A.EMPL_RCD = F.EMPL_RCD AND F.GP_PAYGROUP = A.GP_PAYGROUP AND A.EFFDT = F.SEG_END_DT+1 AND EXISTS ( SELECT E.GP_PAYGROUP FROM PS_GP_WC_USR_PYGRP E WHERE E.GP_PAYGROUP = A.GP_PAYGROUP ) AND A.EMPLID = C.EMPLID AND A.EMPL_RCD = C.EMPL_RCD AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_GP_JB_DESCR_VW C_ED WHERE C.EMPLID = C_ED.EMPLID AND C.EMPL_RCD = C_ED.EMPL_RCD AND C_ED.EFFDT <= F.SEG_END_DT) AND C.EFFSEQ = ( SELECT MAX(C_ES.EFFSEQ) FROM PS_GP_JB_DESCR_VW C_ES WHERE C.EMPLID = C_ES.EMPLID AND C.EMPL_RCD = C_ES.EMPL_RCD AND C.EFFDT = C_ES.EFFDT) AND A.EFFSEQ = C.EFFSEQ AND A.EMPLID = D.EMPLID AND D.PRIMARY_NID = 'Y' AND A.EMPLID = B.EMPLID )) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | CAL_RUN_ID |
Character(18) | VARCHAR2(18) NOT NULL | Calendar Run Id |
| 3 | CAL_ID |
Character(18) | VARCHAR2(18) NOT NULL | calendar id |
| 4 | RUN_TYPE |
Character(10) | VARCHAR2(10) NOT NULL | Run Type Name |
| 5 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 6 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 7 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 8 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 9 | ACTION |
Character(3) | VARCHAR2(3) NOT NULL | Action |
| 10 | LAST_HIRE_DT |
Date(10) | DATE | Latest Start Date - The latest time that an Employee or a Contingent Worker has started - based on EMPLID/EMPL_RCD. |
| 11 | TERMINATION_DT |
Date(10) | DATE | Termination Date |
| 12 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 13 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 14 | DEPTID |
Character(10) | VARCHAR2(10) NOT NULL | Department |
| 15 | ESTABID | Character(12) | VARCHAR2(12) NOT NULL | Establishment ID |
| 16 | LOCATION_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Location Description |
| 17 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 18 | JOBCODE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Code Description |
| 19 | HR_STATUS |
Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 20 | 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 |
| 21 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 22 | EMPL_CLASS | Character(3) | VARCHAR2(3) NOT NULL | Employee Classification |
| 23 | CONTRACT_TYPE | Character(3) | VARCHAR2(3) NOT NULL | Contract Type |
| 24 | MONTHLY_RT | Number(19,3) | DECIMAL(18,3) NOT NULL | Monthly Rate |
| 25 | FTE | Number(8,6) | DECIMAL(7,6) NOT NULL | This field represent Full Time Equivalence |
| 26 | PIN_GROSS_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Gross Pay Value |
| 27 | PIN_NET_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Net Pay Value |
| 28 | GP_WORKITEM_TYPE |
Character(254) | VARCHAR2(254) NOT NULL | GP WC Type of Employee |
| 29 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 30 | PER_ORG | Character(3) | VARCHAR2(3) NOT NULL |
Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker EMP=Employee POI=Person of Interest |
| 31 | COMPRATE | Number(19,6) | DECIMAL(18,6) NOT NULL | Compensation Rate |
| 32 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract Number |