GP_WC_INACTIVE

(SQL View)
 Index Back

GP WC Inactive Employees paid

GP WC Inactive Employees paid

SELECT D.EMPLID , A.CAL_ID , E.FIRST_NAME , E.LAST_NAME , D.EMPL_RCD , D.LAST_HIRE_DT , D.TERMINATION_DT , D.COMPANY , D.BUSINESS_UNIT , D.DEPTID , D.ESTABID , D.LOCATION , D13X.XLATLONGNAME , D14X.XLATLONGNAME , D.GP_PAYGROUP , D.EMPL_CLASS , D.FTE , A.PIN_GROSS_VAL , A.PIN_NET_VAL , B.CAL_RUN_ID FROM PS_GP_PYE_SEG_STAT A , (((PS_GP_PYE_PRC_STAT B ) LEFT OUTER JOIN (PS_JOB D ) ON B.EMPLID = D.EMPLID AND B.EMPL_RCD = D.EMPL_RCD AND B.GP_PAYGROUP = D.GP_PAYGROUP ) LEFT OUTER JOIN ( SELECT * FROM PSXLATITEM TA WHERE TA.FIELDNAME='HR_STATUS' AND TA.EFF_STATUS = 'A' AND TA.EFFDT = ( SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME='HR_STATUS' AND TA.FIELDVALUE=TB.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <= %CurrentDateIn )) D13X ON D13X.FIELDVALUE = D.HR_STATUS AND D13X.FIELDNAME='HR_STATUS' LEFT OUTER JOIN ( SELECT * FROM PSXLATITEM TA WHERE TA.FIELDNAME='EMPL_STATUS' AND TA.EFF_STATUS = 'A' AND TA.EFFDT = ( SELECT MAX(EFFDT) FROM PSXLATITEM TB WHERE TB.FIELDNAME='EMPL_STATUS' AND TA.FIELDVALUE=TB.FIELDVALUE AND TB.EFF_STATUS = 'A' AND TB.EFFDT <= %CurrentDateIn )) D14X ON D14X.FIELDVALUE = D.EMPL_STATUS AND D14X.FIELDNAME='EMPL_STATUS' LEFT OUTER JOIN PS_PERSON_NAME E ON B.EMPLID = E.EMPLID ) WHERE ( ( A.EMPLID = B.EMPLID AND A.CAL_RUN_ID = B.CAL_RUN_ID AND A.EMPL_RCD = B.EMPL_RCD AND A.GP_PAYGROUP = B.GP_PAYGROUP AND A.CAL_ID = B.CAL_ID AND A.ORIG_CAL_RUN_ID = B.ORIG_CAL_RUN_ID AND B.RUN_TYPE = ( SELECT C.RUN_TYPE FROM PS_GP_RUN_TYPE C WHERE ( C.CALC_TYPE = 'P' AND B.RUN_TYPE = C.RUN_TYPE )) AND A.PIN_GROSS_VAL <> 0 AND B.SEL_STAT = 'I' AND D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_JOB D_ED WHERE D.EMPLID = D_ED.EMPLID AND D.EMPL_RCD = D_ED.EMPL_RCD AND D_ED.EFFDT <= A.SEG_END_DT+1) AND D.EFFSEQ = ( SELECT MAX(D_ES.EFFSEQ) FROM PS_JOB D_ES WHERE D.EMPLID = D_ES.EMPLID AND D.EMPL_RCD = D_ES.EMPL_RCD AND D.EFFDT = D_ES.EFFDT) AND B.CALC_TYPE = 'P' AND EXISTS ( SELECT F.GP_PAYGROUP FROM PS_GP_WC_USR_PYGRP F WHERE ( F.GP_PAYGROUP = B.GP_PAYGROUP )) ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
3 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
4 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
6 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.
7 TERMINATION_DT Date(10) DATE Termination Date
8 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
11 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
12 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
13 HR_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL HR Status
14 EMPL_STATUS_DESCR Character(30) VARCHAR2(30) NOT NULL Employee Status
15 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
16 EMPL_CLASS Character(3) VARCHAR2(3) NOT NULL Employee Classification
17 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
18 PIN_GROSS_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
19 PIN_NET_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Net Pay Value
20 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id