GPFR_CITS_VW

(SQL View)
 Index Back

CITS VIEW

To store TAX SALAIRE1 deduction amount

SELECT X.EMPLID ,MIN(X.EMPL_RCD) ,SUM(X.BASE_RSLT_VAL) FROM PS_GP_RSLT_ERN_DED X , PS_GP_PYE_SEG_STAT SEG WHERE X.PIN_NUM = ( SELECT Y.PIN_NUM FROM PS_GP_PIN Y WHERE Y.PIN_NM = 'TAX SALAIRE1' AND Y.PIN_TYPE = 'DD') AND X.SLICE_BGN_DT BETWEEN %DateIn ('2017-01-01') AND %DateIn('2017-12-31') AND X.EMPLID= SEG.EMPLID AND X.EMPL_RCD=SEG.EMPL_RCD AND X.GP_PAYGROUP =SEG.GP_PAYGROUP AND X.CAL_ID=SEG.CAL_ID AND X.CAL_RUN_ID=SEG.CAL_RUN_ID AND SEG.RSLT_VER_NUM = ( SELECT MAX(SEG1.RSLT_VER_NUM) FROM PS_GP_PYE_SEG_STAT SEG1 WHERE SEG.EMPLID= SEG1.EMPLID AND SEG.EMPL_RCD=SEG1.EMPL_RCD AND SEG.GP_PAYGROUP =SEG1.GP_PAYGROUP AND SEG.CAL_ID=SEG1.CAL_ID AND SEG.ORIG_CAL_RUN_ID=SEG1.ORIG_CAL_RUN_ID AND SEG.RSLT_SEG_NUM =SEG1.RSLT_SEG_NUM) GROUP BY X.EMPLID

# 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 BASE_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculated Base Value