GP_WC_NEWHIR_VW

(SQL View)
 Index Back

GP WC Newhire

GP WC Newhire

SELECT DISTINCT A.EMPLID , ( SELECT B.NAME FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_NAMES B1 WHERE B1.EMPLID = B.EMPLID AND B1.NAME_TYPE = B.NAME_TYPE AND B1.EFFDT <= %CurrentDateIn)) NAME , A.EMPL_RCD , A.ACTION, ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(T.EFFDT) FROM PS_ACTION_TBL T WHERE ACT.ACTION = T.ACTION AND T.EFFDT <= %CurrentDateIn)) ,A.EFFDT , A.LAST_HIRE_DT ,( SELECT DISTINCT G.PIN_GROSS_VAL FROM PS_GP_PYE_SEG_STAT G WHERE G.EMPLID = P.EMPLID AND G.EMPL_RCD = P.EMPL_RCD AND G.CAL_RUN_ID = P.CAL_RUN_ID AND G.RSLT_VER_NUM = P.RSLT_VER_NUM AND G.RSLT_REV_NUM = P.RSLT_REV_NUM AND G.GP_PAYGROUP = P.GP_PAYGROUP AND G.ORIG_CAL_RUN_ID = P.ORIG_CAL_RUN_ID AND G.CAL_ID = P.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP )) , ( SELECT DISTINCT G.PIN_NET_VAL FROM PS_GP_PYE_SEG_STAT G WHERE G.EMPLID = P.EMPLID AND G.EMPL_RCD = P.EMPL_RCD AND G.CAL_RUN_ID = P.CAL_RUN_ID AND G.RSLT_VER_NUM = P.RSLT_VER_NUM AND G.RSLT_REV_NUM = P.RSLT_REV_NUM AND G.GP_PAYGROUP = P.GP_PAYGROUP AND G.ORIG_CAL_RUN_ID = P.ORIG_CAL_RUN_ID AND G.CAL_ID = P.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP )) ,P.CAL_RUN_ID , P.CAL_ID ,J.OFF_CYCLE , A.GP_PAYGROUP ,A.COMPANY , A.DEPTID , A.EMPL_STATUS , A.HR_STATUS , (CASE WHEN A.EFFDT = B.PRD_BGN_DT THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5094) WHEN A.EFFDT <> B.PRD_BGN_DT THEN ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5087) END ) ,CASE WHEN J.RUN_OPEN_IND = 'Y' THEN 'O' WHEN J.RUN_FINALIZED_IND = 'Y' AND J.PMT_SENT_IND <> 'Y' THEN 'F' WHEN J.PMT_SENT_IND = 'Y' THEN 'B' END CALSTATUS ,(%Sql(GP_WC_FINAL_TS,J.CAL_RUN_ID)), %DateDiff(A.EFFDT , %Datein(%CurrentDateOut)) FROM PS_JOB A ,PS_GP_PYE_PRC_STAT P , PS_GP_CAL_RUNOF_VW B, PS_GP_CAL_RUN J WHERE J.CAL_RUN_ID = B.CAL_RUN_ID AND B.CALC_TYPE = 'P' AND B.GP_PAYGROUP = A.GP_PAYGROUP AND A.EFFDT BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT AND A.ACTION IN( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = B.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = B.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'H' ) AND P.EMPLID = A.EMPLID AND P.EMPL_RCD = A.EMPL_RCD AND P.CALC_TYPE = 'P' AND P.GP_PAYGROUP = A.GP_PAYGROUP AND P.GP_PAYGROUP = B.GP_PAYGROUP AND P.CAL_RUN_ID = B.CAL_RUN_ID AND ((P.CAL_ID = B.CAL_ID) OR (B.CAL_ID = ' ' AND J.OFF_CYCLE = 'Y')) AND NOT EXISTS( SELECT 1 FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND %DateAdd(%DateIn(J.EFFDT),-1) BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT AND %DateAdd(%DateIn(J.EFFDT),-1) >= A.EFFDT AND J.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT = J.EFFDT) AND J.ACTION IN ( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = B.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = B.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'T' ) ) UNION SELECT DISTINCT A.EMPLID , ( SELECT B.NAME FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_NAMES B1 WHERE B1.EMPLID = B.EMPLID AND B1.NAME_TYPE = B.NAME_TYPE AND B1.EFFDT <= %CurrentDateIn)) , A.EMPL_RCD , A.ACTION , ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(T.EFFDT) FROM PS_ACTION_TBL T WHERE ACT.ACTION = T.ACTION AND T.EFFDT <= %CurrentDateIn)),A.EFFDT , A.LAST_HIRE_DT ,( SELECT DISTINCT G.PIN_GROSS_VAL FROM PS_GP_PYE_SEG_STAT G WHERE G.EMPLID = P.EMPLID AND G.EMPL_RCD = P.EMPL_RCD AND G.CAL_RUN_ID = P.CAL_RUN_ID AND G.RSLT_VER_NUM = P.RSLT_VER_NUM AND G.RSLT_REV_NUM = P.RSLT_REV_NUM AND G.GP_PAYGROUP = P.GP_PAYGROUP AND G.ORIG_CAL_RUN_ID = P.ORIG_CAL_RUN_ID AND G.CAL_ID = P.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP )), ( SELECT DISTINCT G.PIN_NET_VAL FROM PS_GP_PYE_SEG_STAT G WHERE G.EMPLID = P.EMPLID AND G.EMPL_RCD = P.EMPL_RCD AND G.CAL_RUN_ID = P.CAL_RUN_ID AND G.RSLT_VER_NUM = P.RSLT_VER_NUM AND G.RSLT_REV_NUM = P.RSLT_REV_NUM AND G.GP_PAYGROUP = P.GP_PAYGROUP AND G.ORIG_CAL_RUN_ID = P.ORIG_CAL_RUN_ID AND G.CAL_ID = P.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP )) ,P.CAL_RUN_ID , P.CAL_ID ,J.OFF_CYCLE , A.GP_PAYGROUP ,A.COMPANY , A.DEPTID , A.EMPL_STATUS , A.HR_STATUS , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5088) ,CASE WHEN J.RUN_OPEN_IND = 'Y' THEN 'O' WHEN J.RUN_FINALIZED_IND = 'Y' AND J.PMT_SENT_IND <> 'Y' THEN 'F' WHEN J.PMT_SENT_IND = 'Y' THEN 'B' END ,(%Sql(GP_WC_FINAL_TS,J.CAL_RUN_ID)), %DateDiff(A.EFFDT , %Datein(%CurrentDateOut)) FROM PS_JOB A ,PS_GP_PYE_PRC_STAT P , PS_GP_CAL_RUNOF_VW B, PS_GP_CAL_RUN J WHERE J.CAL_RUN_ID = P.CAL_RUN_ID AND B.CALC_TYPE = 'P' AND A.ACTION IN( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = B.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = B.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'H' ) AND A.ACTION_DT >= ( SELECT MAX(%DatePart(B1.CAL_IDNT_TS )) FROM PS_GP_CAL_RUN_DTL B1 WHERE B1.GP_PAYGROUP = B.GP_PAYGROUP AND B1.CAL_FINAL_TS IS NOT NULL ) AND P.EMPLID = A.EMPLID AND P.EMPL_RCD = A.EMPL_RCD AND P.GP_PAYGROUP = A.GP_PAYGROUP AND P.PRD_TYPE = 'R' AND P.CALC_TYPE = 'P' AND B.GP_PAYGROUP = P.GP_PAYGROUP AND ((P.CAL_ID = B.CAL_ID) OR (B.CAL_ID = ' ' AND J.OFF_CYCLE = 'Y')) AND A.EFFDT BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT UNION SELECT DISTINCT A.EMPLID , ( SELECT B.NAME FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = 'PRI' AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_NAMES B1 WHERE B1.EMPLID = B.EMPLID AND B1.NAME_TYPE = B.NAME_TYPE AND B1.EFFDT <= %CurrentDateIn)) , A.EMPL_RCD , A.ACTION, ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = A.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(T.EFFDT) FROM PS_ACTION_TBL T WHERE ACT.ACTION = T.ACTION AND T.EFFDT <= %CurrentDateIn)) ,A.EFFDT , A.LAST_HIRE_DT ,( SELECT DISTINCT G.PIN_GROSS_VAL FROM PS_GP_PYE_SEG_STAT G WHERE G.EMPLID = P.EMPLID AND G.EMPL_RCD = P.EMPL_RCD AND G.CAL_RUN_ID = P.CAL_RUN_ID AND G.RSLT_VER_NUM = P.RSLT_VER_NUM AND G.RSLT_REV_NUM = P.RSLT_REV_NUM AND G.GP_PAYGROUP = P.GP_PAYGROUP AND G.ORIG_CAL_RUN_ID = P.ORIG_CAL_RUN_ID AND G.CAL_ID = P.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP )), ( SELECT DISTINCT G.PIN_NET_VAL FROM PS_GP_PYE_SEG_STAT G WHERE G.EMPLID = P.EMPLID AND G.EMPL_RCD = P.EMPL_RCD AND G.CAL_RUN_ID = P.CAL_RUN_ID AND G.RSLT_VER_NUM = P.RSLT_VER_NUM AND G.RSLT_REV_NUM = P.RSLT_REV_NUM AND G.GP_PAYGROUP = P.GP_PAYGROUP AND G.ORIG_CAL_RUN_ID = P.ORIG_CAL_RUN_ID AND G.CAL_ID = P.CAL_ID AND G.RSLT_SEG_NUM = ( SELECT MAX(G1.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT G1 WHERE G1.EMPLID = G.EMPLID AND G.EMPL_RCD= G1.EMPL_RCD AND G1.CAL_RUN_ID = G.CAL_RUN_ID AND G1.GP_PAYGROUP = G.GP_PAYGROUP )) ,P.CAL_RUN_ID , P.CAL_ID ,C.OFF_CYCLE , A.GP_PAYGROUP ,A.COMPANY , A.DEPTID , A.EMPL_STATUS , A.HR_STATUS , ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = 17000 AND MESSAGE_NBR = 5089) ,CASE WHEN C.RUN_OPEN_IND = 'Y' THEN 'O' WHEN C.RUN_FINALIZED_IND = 'Y' AND C.PMT_SENT_IND <> 'Y' THEN 'F' WHEN C.PMT_SENT_IND = 'Y' THEN 'B' END ,(%Sql(GP_WC_FINAL_TS,C.CAL_RUN_ID)), %DateDiff(A.EFFDT , %Datein(%CurrentDateOut)) FROM PS_JOB A ,PS_GP_PYE_PRC_STAT P , PS_GP_CAL_RUNOF_VW B, PS_JOB J,PS_GP_CAL_RUN C WHERE C.CAL_RUN_ID = B.CAL_RUN_ID AND B.CALC_TYPE = 'P' AND A.EFFDT BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT AND A.ACTION IN( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = B.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = B.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'H' ) AND P.EMPLID = A.EMPLID AND P.EMPL_RCD = A.EMPL_RCD AND P.CALC_TYPE = 'P' AND P.GP_PAYGROUP = A.GP_PAYGROUP AND P.GP_PAYGROUP = B.GP_PAYGROUP AND P.CAL_RUN_ID = B.CAL_RUN_ID AND P.CAL_ID = B.CAL_ID AND ((P.CAL_ID = B.CAL_ID) OR (B.CAL_ID = ' ' AND C.OFF_CYCLE = 'Y')) AND J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND %DateAdd(%DateIn(J.EFFDT),-1) BETWEEN B.PRD_BGN_DT AND B.PRD_END_DT AND %DateAdd(%DateIn(J.EFFDT),-1) >= A.EFFDT AND J.EFFSEQ = ( SELECT MAX(J1.EFFSEQ) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.EFFDT = J.EFFDT) AND J.ACTION IN ( SELECT T.ACTION FROM PS_GP_WC_ACT_DTL T WHERE ( T.COUNTRY = B.COUNTRY OR ( T.COUNTRY = 'ALL' AND (NOT EXISTS( SELECT S.COUNTRY FROM PS_GP_WC_ACT_DTL S WHERE S.COUNTRY = B.COUNTRY )) ) ) AND T.GP_WC_ACTION_ID = 'T' ) AND J.EFFDT = ( SELECT MIN(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD = J.EMPL_RCD AND J1.ACTION = J.ACTION AND J1.LAST_HIRE_DT= A.EFFDT)

  • Related Language Record: GP_WC_NHRLNG_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 NAME Character(50) VARCHAR2(50) NOT NULL Name
    3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    4 ACTION Character(3) VARCHAR2(3) NOT NULL HR Action
    5 ACTION_DESCR Character(50) VARCHAR2(50) NOT NULL Action Description
    6 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    7 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.
    8 PIN_GROSS_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
    9 PIN_NET_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Net Pay Value
    10 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
    11 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
    12 GP_OFF_CYCLE Character(2) VARCHAR2(2) NOT NULL OFF CYCLE Translate values
    N=No
    Y=Yes
    13 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
    14 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    15 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    16 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
    17 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    18 GP_WC_WORKITM_TYPE Character(254) VARCHAR2(254) NOT NULL GP WorkCenter WorkItem Type
    19 GP_CAL_STATUS Character(1) VARCHAR2(1) NOT NULL Calculation status
    B=Finalized with Banking
    F=Finalized
    O=Open
    20 GP_WC_FINAL_DAYS Number(4,0) SMALLINT NOT NULL Finalized day count
    21 GP_WC_LIMIT_DAY Number(4,0) SMALLINT NOT NULL Work item day count