GP_WC_SEGLNG_VW

(SQL View)
 Index Back

GP WC Retro

GP WC Retro

SELECT DISTINCT A.EMPLID , A.EMPL_RCD ,( SELECT D1.NAME_DISPLAY FROM PS_PERSON_NAME D1 WHERE D1.EMPLID = A.EMPLID) EmpName, A.COUNTRY , A.TRGR_EFFDT , A.TRGR_EVENT_ID, ( CASE WHEN A.TRGR_EVENT_ID <> ' ' THEN E.LANGUAGE_CD END), ( CASE WHEN A.TRGR_EVENT_ID <> ' ' THEN E.DESCR END) ,A.RECNAME , A.FIELDNAME , %Cast(A.TRGR_FLD_VAL_DT, Date,Character) %Concat (CASE WHEN A.TRGR_FLD_VAL_NUM = 0 THEN '' ELSE %NumToChar(A.TRGR_FLD_VAL_NUM) END) %Concat A.TRGR_FLD_VAL_CHAR , C.GP_PAYGROUP , C.CAL_RUN_ID ,C.CAL_ID ,( 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) ) , 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_LNG 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 for GP_WC_SEG_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_EVENT_ID Character(10) VARCHAR2(10) NOT NULL Trigger Event ID
    7 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 RECNAME Character(15) VARCHAR2(15) NOT NULL Record (Table) Name (see PSRECDEFN).
    10 FIELDNAME Character(18) VARCHAR2(18) NOT NULL Field Name (see PSDBFIELD).
    11 FIELD_VALUE Character(30) VARCHAR2(30) NOT NULL Field Value
    12 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
    13 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
    14 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
    15 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    16 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    17 GP_CAL_STATUS Character(1) VARCHAR2(1) NOT NULL Calculation status
    B=Finalized with Banking
    F=Finalized
    O=Open
    18 GP_WC_FINAL_DAYS Number(4,0) SMALLINT NOT NULL Finalized day count
    19 GP_WC_LIMIT_DAY Number(4,0) SMALLINT NOT NULL Work item day count