PY_MOD_RSERN_VW

(SQL View)
 Index Back

Payroll Regulqr Earnings

Paycheck Modeling Salaried employees regular earnings vw

SELECT A.PY_MOD_OPRID , A.PY_MOD_EMPLID , A.COMPANY , A.PAYGROUP , A.PAY_END_DT , A.OFF_CYCLE , A.PAGE_NUM , A.LINE_NUM , A.ERNCD_REG_EARNS , A.HOURLY_RT , C.DESCR , C.PAYMENT_TYPE , C.EFF_STATUS , 'RE' , 0 , SUM( A.REG_EARNS) , COUNT(*) , 'I' FROM PS_MODEL_PAY_ERNVW A , PS_MODEL_PAY_LINE B , PS_EARNINGS_TBL C WHERE A.ADDLPAY_REASON = 'N' AND EXISTS ( SELECT 'Y' FROM PS_PY_MOD_TXDST_V2 T WHERE T.PY_MOD_OPRID = A.PY_MOD_OPRID AND T.PY_MOD_EMPLID = A.PY_MOD_EMPLID AND T.COMPANY = A.COMPANY AND T.PAYGROUP = A.PAYGROUP AND T.PAY_END_DT = A.PAY_END_DT AND T.OFF_CYCLE = A.OFF_CYCLE AND T.PAGE_NUM = A.PAGE_NUM AND T.LINE_NUM = A.LINE_NUM AND T.ADDL_NBR = A.ADDL_NBR AND T.ADDLPAY_REASON = A.ADDLPAY_REASON) AND A.PY_MOD_OPRID = B.PY_MOD_OPRID AND A.PY_MOD_EMPLID = B.PY_MOD_EMPLID AND A.COMPANY = B.COMPANY AND A.PAYGROUP = B.PAYGROUP AND A.PAY_END_DT = B.PAY_END_DT AND A.OFF_CYCLE = B.OFF_CYCLE AND A.PAGE_NUM = B.PAGE_NUM AND A.LINE_NUM = B.LINE_NUM AND B.EMPL_TYPE = 'S' AND A.ERNCD_REG_EARNS = C.ERNCD AND C.EFFDT = ( SELECT MAX(C_ED.EFFDT) FROM PS_EARNINGS_TBL C_ED WHERE C.ERNCD = C_ED.ERNCD AND C_ED.EFFDT <= %CurrentDateIn) GROUP BY A.PY_MOD_OPRID , A.PY_MOD_EMPLID , A.COMPANY , A.PAYGROUP , A.PAY_END_DT , A.OFF_CYCLE , A.PAGE_NUM , A.LINE_NUM , A.ERNCD_REG_EARNS , A.HOURLY_RT , C.DESCR , C.PAYMENT_TYPE, C.EFF_STATUS

  • Parent record: PAY_EARNINGS

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 PY_MOD_OPRID Character(30) VARCHAR2(30) NOT NULL PayCheck Modeler User Id
    2 PY_MOD_EMPLID Character(11) VARCHAR2(11) NOT NULL PayCheck Modeler Employee ID
    3 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    4 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
    5 PAY_END_DT Date(10) DATE NOT NULL Pay Period End Date
    6 OFF_CYCLE Character(1) VARCHAR2(1) NOT NULL Off Cycle ?

    Y/N Table Edit

    Default Value: Y

    7 PAGE_NUM Number(5,0) INTEGER NOT NULL Page Nbr
    8 LINE_NUM Number(3,0) SMALLINT NOT NULL Line Nbr
    9 ERNCD Character(3) VARCHAR2(3) NOT NULL Earnings Code
    10 COMPRATE_USED Number(19,6) DECIMAL(18,6) NOT NULL Comp Rate Used
    11 PY_MOD_DESCR_LABEL Character(30) VARCHAR2(30) NOT NULL Earnings Type
    12 PAYMENT_TYPE Character(1) VARCHAR2(1) NOT NULL Payment Type
    A=Amounts Only
    B=Both Hours and Amount OK
    E=Either Hours or Amount OK
    F=Flat Amount
    H=Hours Only
    S=Defined by Salary System
    U=Unit/Override Rate
    13 STATUS_FLAG Character(1) VARCHAR2(1) NOT NULL Status Flag
    14 PY_MOD_EARNSCE_TYP Character(2) VARCHAR2(2) NOT NULL Paycheck Modeling Earning Source Type: RE - Regular OT - Overtime OE - Others
    15 OTH_HRS Signed Number(8,2) DECIMAL(6,2) NOT NULL Other hours.
    16 OTH_EARNS Signed Number(12,2) DECIMAL(10,2) NOT NULL Other Earnings
    17 COUNT_ROWS Number(3,0) SMALLINT NOT NULL Count rows.
    18 CHANGE_CODE Character(1) VARCHAR2(1) NOT NULL Whether this record represents an New, Change, Delete or initial load. Used in discounts and perks eligibility file.
    C=Change
    D=Delete
    I=Initial Load
    N=New