GPAU_STP_LA_VW

(SQL View)
 Index Back

Lump A Indicator

This view is to populate the LUMP A code in PAYEVNTEE Xml

SELECT DISTINCT STP.PROCESS_INSTANCE , STP.EMPLID_BGRP , STP.CAL_RUN_ID , CASE WHEN STP.GPAU_LUMPA_IND <> ' ' THEN STP.GPAU_LUMPA_IND WHEN (JOB.ACTION = 'TER' AND (JOB.ACTION_REASON = 'PTD' OR JOB.ACTION_REASON = 'ELI' OR JOB.ACTION_REASON = 'ERT')) OR STP.GPAU_STP_LUMPD <> 0 THEN 'R' ELSE 'T' END FROM PS_GPAU_STP_TMPSTG STP , PS_JOB JOB , PS_JOB_JR JR WHERE JOB.EMPLID = STP.EMPLID AND JOB.GP_PAYGROUP = STP.GP_PAYGROUP AND STP.GPAU_STP_LUMPA <> 0 AND JOB.EFFDT = ( SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE JOB2.EMPLID = JOB.EMPLID AND JOB2.EMPL_RCD = JOB.EMPL_RCD AND JOB2.HR_STATUS = 'I' AND JOB2.EFFDT <= %DateAdd(STP.PRD_END_DT, 1) AND JOB2.GP_PAYGROUP IN ( SELECT PYG.GP_PAYGROUP FROM PS_GP_PYGRP PYG WHERE PYG.PAY_ENTITY =STP.PAY_ENTITY )) AND JOB.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = JOB.EMPLID AND JOB3.EMPL_RCD = JOB.EMPL_RCD AND JOB3.HR_STATUS = 'I' AND JOB3.EFFDT = JOB.EFFDT) AND JR.EMPLID = JOB.EMPLID AND JR.EMPL_RCD = JOB.EMPL_RCD AND JR.EFFDT = JOB.EFFDT AND JR.EFFSEQ = JOB.EFFSEQ AND JR.BALANCE_GRP_NUM = STP.USER_KEY2

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
2 EMPLID_BGRP Character(14) VARCHAR2(14) NOT NULL Emplid
3 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
4 GPAU_LUMPA_IND Character(1) VARCHAR2(1) NOT NULL Lump sum A payment indicator