GP_WC_ACTNPY_VW

(SQL View)
 Index Back

GP WC Active Emp not paid

GP WC Active Emp not paid

SELECT D.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)), D.EMPL_RCD , D.ACTION, ( SELECT ACT.ACTION_DESCR FROM PS_ACTION_TBL ACT WHERE ACT.ACTION = D.ACTION AND ACT.EFF_STATUS = 'A' AND ACT.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_ACTION_TBL B WHERE ACT.ACTION = B.ACTION AND B.EFF_STATUS = 'A' AND B.EFFDT <= %CurrentDateIn)) , D.EFFDT, A.PIN_GROSS_VAL, A.PIN_NET_VAL , A.CAL_RUN_ID ,A.CAL_ID , A.RSLT_SEG_NUM, J.OFF_CYCLE , B.GP_PAYGROUP , D.COMPANY , D.DEPTID , D.EMPL_STATUS ,D.HR_STATUS , CASE WHEN ( SELECT DISTINCT D1.RUN_OPEN_IND FROM PS_GP_CAL_RUN D1 WHERE D1.CAL_RUN_ID = J.CAL_RUN_ID AND RUN_FINALIZED_IND = 'N') = 'Y' THEN 'O' WHEN ( SELECT DISTINCT J.RUN_FINALIZED_IND FROM PS_GP_CAL_RUN D2 WHERE D2.CAL_RUN_ID = J.CAL_RUN_ID AND D2.PMT_SENT_IND = 'N') = 'Y' THEN 'F' WHEN ( SELECT DISTINCT J.RUN_FINALIZED_IND FROM PS_GP_CAL_RUN D3 WHERE D3.CAL_RUN_ID = J.CAL_RUN_ID AND D3.PMT_SENT_IND = 'Y') = 'Y' THEN 'B' END ,(%Sql(GP_WC_FINAL_TS,J.CAL_RUN_ID)), 0 FROM PS_GP_PYE_SEG_STAT A , PS_GP_CAL_RUN J ,PS_GP_PYE_PRC_STAT B, PS_JOB D 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 A.PIN_GROSS_VAL = 0 AND A.RSLT_VER_NUM = 1 AND A.RSLT_REV_NUM = 1 AND A.PYE_CALC_STAT >= '50' AND A.RSLT_VER_NUM = B.RSLT_VER_NUM AND A.RSLT_REV_NUM = B.RSLT_REV_NUM AND B.EMPLID = D.EMPLID AND B.EMPL_RCD = D.EMPL_RCD AND B.GP_PAYGROUP = D.GP_PAYGROUP AND B.SEL_STAT = 'A' 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) 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 J.CAL_RUN_ID = A.CAL_RUN_ID 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)

  • Related Language Record: GP_WC_ANPLNG_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

    Prompt Table: ACTION_TBL

    5 ACTION_DESCR Character(50) VARCHAR2(50) NOT NULL Action Description
    6 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    7 PIN_GROSS_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
    8 PIN_NET_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Net Pay Value
    9 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
    10 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
    11 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number
    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_CAL_STATUS Character(1) VARCHAR2(1) NOT NULL Calculation status
    B=Finalized with Banking
    F=Finalized
    O=Open
    19 GP_WC_FINAL_DAYS Number(4,0) SMALLINT NOT NULL Finalized day count
    20 GP_WC_LIMIT_DAY Number(4,0) SMALLINT NOT NULL Work item day count