GP_WC_SEG_VW

(SQL View)
 Index Back

GP WC Retro

GP WC Retro

SELECT DISTINCT A.EMPLID , A.EMPL_RCD ,( 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.COUNTRY , A.TRGR_EFFDT , A.TRGR_CREATE_TS , ( CASE WHEN A.TRGR_EVENT_ID <> ' ' THEN E.SEG_TYPE END),A.SEG_TRGR_SRC , A.TRGR_EVENT_ID, ( CASE WHEN A.TRGR_EVENT_ID <> ' ' THEN E.DESCR END) , A.SEG_TRGR_STATUS ,A.SEG_TRGR_LVL ,A.RECNAME , A.FIELDNAME , ( SELECT PIN_NM FROM PS_GP_PIN_NM_VW WHERE PIN_NUM = A.TRGR_FLD_VAL_PIN), (CASE WHEN A.TRGR_FLD_VAL_DT IS NULL THEN '' ELSE %Cast(A.TRGR_FLD_VAL_DT, Date,Character) END) %Concat (CASE WHEN A.TRGR_FLD_VAL_NUM = 0 THEN '' ELSE %NumToChar(A.TRGR_FLD_VAL_NUM) END) %Concat A.TRGR_FLD_VAL_CHAR ,( SELECT DISTINCT J.GP_PAYGROUP FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD= J.EMPL_RCD AND J1.EFFDT <= A.TRGR_EFFDT ) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD= J.EMPL_RCD AND J2.EFFDT = J.EFFDT) ) , C.CAL_RUN_ID ,C.CAL_ID ,D.OFF_CYCLE ,( SELECT DISTINCT J.COMPANY FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD= J.EMPL_RCD AND J1.EFFDT <= A.TRGR_EFFDT ) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD= J.EMPL_RCD AND J2.EFFDT = J.EFFDT) ), ( SELECT DISTINCT J.DEPTID FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD= J.EMPL_RCD AND J1.EFFDT <= A.TRGR_EFFDT ) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD= J.EMPL_RCD AND J2.EFFDT = J.EFFDT) ) ,( SELECT DISTINCT J.HR_STATUS FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD= J.EMPL_RCD AND J1.EFFDT <= A.TRGR_EFFDT) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD= J.EMPL_RCD AND J2.EFFDT = J.EFFDT) ), ( SELECT DISTINCT J.EMPL_STATUS FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT = ( SELECT MAX(J1.EFFDT) FROM PS_JOB J1 WHERE J1.EMPLID = J.EMPLID AND J1.EMPL_RCD= J.EMPL_RCD AND J1.EFFDT <= A.TRGR_EFFDT ) AND J.EFFSEQ = ( SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD= J.EMPL_RCD AND J2.EFFDT = J.EFFDT) ) , CASE WHEN ( SELECT DISTINCT D1.RUN_OPEN_IND FROM PS_GP_CAL_RUN D1 WHERE D1.CAL_RUN_ID = D.CAL_RUN_ID AND RUN_FINALIZED_IND = 'N') = 'Y' THEN 'O' WHEN ( SELECT DISTINCT D.RUN_FINALIZED_IND FROM PS_GP_CAL_RUN D2 WHERE D2.CAL_RUN_ID = D.CAL_RUN_ID AND D2.PMT_SENT_IND = 'N') = 'Y' THEN 'F' WHEN ( SELECT DISTINCT D.RUN_FINALIZED_IND FROM PS_GP_CAL_RUN D3 WHERE D3.CAL_RUN_ID = C.CAL_RUN_ID AND D3.PMT_SENT_IND = 'Y') = 'Y' THEN 'B' END ,(%Sql(GP_WC_FINAL_TS,C.CAL_RUN_ID)), %DateDiff(A.TRGR_EFFDT , %Datein(%CurrentDateOut)) FROM PS_GP_SEG_TRGR A, PS_GP_PYE_PRC_STAT F , PS_GP_SEG_EVT E , PS_GP_CAL_RUNOF_VW C , PS_GP_CAL_RUN D WHERE A.EMPLID = F.EMPLID AND A.EMPL_RCD = F.EMPL_RCD AND A.TRGR_EFFDT BETWEEN F.PRD_BGN_DT AND F.PRD_END_DT AND F.CALC_TYPE = 'P' AND F.SEL_STAT = 'A' AND A.COUNTRY = F.COUNTRY AND F.RSLT_VER_NUM =1 AND F.RSLT_REV_NUM = 1 AND F.PRC_ORD_TS = ( SELECT MAX(S.PRC_ORD_TS) FROM PS_GP_PYE_PRC_STAT S WHERE S.EMPLID = F.EMPLID AND S.EMPL_RCD = F.EMPL_RCD AND S.CAL_RUN_ID = F.CAL_RUN_ID AND S.GP_PAYGROUP = F.GP_PAYGROUP AND S.CAL_ID = F.CAL_ID) AND ((A.COUNTRY = E.COUNTRY AND A.TRGR_EVENT_ID = E.TRGR_EVENT_ID) OR (A.TRGR_EVENT_ID = ' ' AND A.COUNTRY = F.COUNTRY ) ) AND C.CAL_RUN_ID = D.CAL_RUN_ID AND F.GP_PAYGROUP = C.GP_PAYGROUP AND F.CAL_RUN_ID = C.CAL_RUN_ID AND A.COUNTRY = C.COUNTRY AND A.COUNTRY = D.COUNTRY AND C.CALC_TYPE = 'P' AND (A.TRGR_EFFDT <= C.PRD_END_DT AND A.SEG_TRGR_STATUS ='A') AND A.SEG_TRGR_STATUS = 'A' AND EXISTS ( SELECT G.GP_PAYGROUP FROM PS_GP_WC_USR_PYGRP G WHERE G.GP_PAYGROUP = F.GP_PAYGROUP )

  • Related Language Record: GP_WC_SEGLNG_VW

    • # 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 NAME Character(50) VARCHAR2(50) NOT NULL Name
    4 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    5 TRGR_EFFDT Date(10) DATE Trigger Effective Date
    6 TRGR_CREATE_TS DateTime(26) TIMESTAMP Trigger Creation Timestamp
    7 SEG_TYPE Character(1) VARCHAR2(1) NOT NULL Segmentation Type
    F=Period
    P=Element
    8 SEG_TRGR_SRC Character(1) VARCHAR2(1) NOT NULL Segmentation Trigger Source
    A=Automatically Generated
    M=Manually Generated
    9 TRGR_EVENT_ID Character(10) VARCHAR2(10) NOT NULL Trigger Event ID
    10 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    11 SEG_TRGR_STATUS Character(1) VARCHAR2(1) NOT NULL Segmentation Trigger Status
    A=Active
    C=Cancelled
    12 SEG_TRGR_LVL Character(1) VARCHAR2(1) NOT NULL Segmentation Trigger Level
    E=Payee Level
    J=Job Level
    13 RECNAME Character(15) VARCHAR2(15) NOT NULL Record (Table) Name (see PSRECDEFN).
    14 FIELDNAME Character(18) VARCHAR2(18) NOT NULL Field Name (see PSDBFIELD).
    15 PIN_NM Character(18) VARCHAR2(18) NOT NULL Element Name field
    16 FIELD_VALUE Character(30) VARCHAR2(30) NOT NULL Field Value
    17 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
    18 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
    19 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
    20 GP_OFF_CYCLE Character(2) VARCHAR2(2) NOT NULL OFF CYCLE Translate values
    N=No
    Y=Yes

    Y/N Table Edit

    21 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    22 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    23 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
    A=Active
    I=Inactive
    24 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
    25 GP_CAL_STATUS Character(1) VARCHAR2(1) NOT NULL Calculation status
    B=Finalized with Banking
    F=Finalized
    O=Open
    26 GP_WC_FINAL_DAYS Number(4,0) SMALLINT NOT NULL Finalized day count
    27 GP_WC_LIMIT_DAY Number(4,0) SMALLINT NOT NULL Work item day count