|
SELECT DISTINCT A.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)) ,A.MESSAGE_SET_NBR ,A.MESSAGE_NBR, C.LANGUAGE_CD,REPLACE( REPLACE(REPLACE(REPLACE(REPLACE( C.MESSAGE_TEXT ,'%1' , CASE WHEN A.PIN_IND1= 'Y' THEN ( SELECT PIN_NM FROM PS_GP_PIN WHERE PIN_NUM =A.MSGDATA1 ) ELSE A.MSGDATA1 END),'%2' , CASE WHEN PIN_IND2= 'Y' THEN ( SELECT PIN_NM FROM PS_GP_PIN WHERE PIN_NUM =A.MSGDATA2 ) ELSE A.MSGDATA2 END) ,'%3' , CASE WHEN A.PIN_IND3= 'Y' THEN ( SELECT PIN_NM FROM PS_GP_PIN WHERE PIN_NUM =A.MSGDATA3 ) ELSE A.MSGDATA3 END) ,'%4' , CASE WHEN A.PIN_IND4= 'Y' THEN ( SELECT PIN_NM FROM PS_GP_PIN WHERE PIN_NUM =A.MSGDATA4 ) ELSE A.MSGDATA4 END) ,'%5' , CASE WHEN A.PIN_IND5= 'Y' THEN ( SELECT PIN_NM FROM PS_GP_PIN WHERE PIN_NUM =A.MSGDATA5 ) ELSE A.MSGDATA5 END) , A.CAL_RUN_ID ,A.CAL_ID , A.GP_PAYGROUP , ( SELECT 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 < %CurrentDateIn) 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 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 < %CurrentDateIn) 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) ) FROM PS_GP_PYE_MSG_VW A ,PSMSGCATLANG C WHERE C.MESSAGE_SET_NBR=A.MESSAGE_SET_NBR AND C.MESSAGE_NBR = A.MESSAGE_NBR AND A.GP_MSG_SEVERITY IN ( 'E' ,'W')
|
EMPLID