GP_WC_RETRO_VW

(SQL View)
 Index Back

GP WC Retro

GP WC Retro

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.COUNTRY , A.TRGR_EFFDT , A.TRGR_CREATE_TS , A.RTO_TRGR_SRC , A.TRGR_EVENT_ID , E.DESCR , A.TRGR_STATUS , A.RECNAME , A.FIELDNAME , (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 , A.TRGR_DESCR , C.GP_PAYGROUP , C.CAL_RUN_ID ,C.CAL_ID,C.CALC_TYPE,D.OFF_CYCLE ,B.COMPANY,B.DEPTID,B.HR_STATUS,B.EMPL_STATUS , 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_RTO_TRGR A LEFT OUTER JOIN PS_GP_RTO_EVT E ON A.COUNTRY = E.COUNTRY AND A.TRGR_EVENT_ID = E.TRGR_EVENT_ID ) , PS_JOB B , PS_GP_CAL_RUNOF_VW C, PS_GP_CAL_RUN D WHERE A.EMPLID = B.EMPLID AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_JOB B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.EMPL_RCD = B_ES.EMPL_RCD AND B.EFFDT = B_ES.EFFDT) AND C.CAL_RUN_ID = D.CAL_RUN_ID AND B.GP_PAYGROUP = C.GP_PAYGROUP AND A.COUNTRY = C.COUNTRY AND A.COUNTRY = D.COUNTRY AND ((A.TRGR_EFFDT < C.PRD_BGN_DT AND A.TRGR_STATUS ='U') OR ( A.CAL_RUN_ID = C.CAL_RUN_ID AND A.TRGR_STATUS = 'P')) AND EXISTS ( SELECT F.GP_PAYGROUP FROM PS_GP_WC_USR_PYGRP F WHERE F.GP_PAYGROUP = B.GP_PAYGROUP )

  • Related Language Record: GP_WC_RTOLNG_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 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    4 TRGR_EFFDT Date(10) DATE Trigger Effective Date
    5 TRGR_CREATE_TS DateTime(26) TIMESTAMP Trigger Creation Timestamp
    6 RTO_TRGR_SRC Character(1) VARCHAR2(1) NOT NULL Retro Trigger Source
    A=Automatically Generated
    F=Benefits Administration
    M=Manually Generated
    S=Mass Trigger
    U=Utility Generated
    7 TRGR_EVENT_ID Character(10) VARCHAR2(10) NOT NULL Trigger Event ID
    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 GP_WC_TG_STA Character(1) VARCHAR2(1) NOT NULL Trigger status field
    P=Processed
    U=Unprocessed
    10 RECNAME Character(15) VARCHAR2(15) NOT NULL Record (Table) Name (see PSRECDEFN).
    11 FIELDNAME Character(18) VARCHAR2(18) NOT NULL Field Name (see PSDBFIELD).
    12 FIELD_VALUE Character(30) VARCHAR2(30) NOT NULL Field Value
    13 TRGR_DESCR Character(30) VARCHAR2(30) NOT NULL Trigger Tag
    14 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
    15 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
    16 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
    17 GP_CALC_TYPE Character(1) VARCHAR2(1) NOT NULL Calculation type
    A=Absence Calculation
    P=Payroll Calculation
    18 GP_OFF_CYCLE Character(2) VARCHAR2(2) NOT NULL OFF CYCLE Translate values
    N=No
    Y=Yes

    Y/N Table Edit

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