|
SELECT A.EMPLID , A. EMPL_RCD , A.DEPTID ,A.JOBCODE ,A.POSITION_NBR ,A.REG_TEMP ,A.FULL_PART_TIME ,A.BUSINESS_UNIT ,A.PER_ORG ,A.LOCATION , ' ' , COALESCE(C.DESCR ,' ' ) , A.REG_REGION , COALESCE(E.DESCR ,' ') , COALESCE(F.DESCR ,' ') , N.NAME , N.LAST_NAME , N.FIRST_NAME , N.MIDDLE_NAME , N.SECOND_LAST_NAME , N.NAME_AC , N.NAME_DISPLAY ,CASE WHEN COALESCE(A.LASTUPDDTTM ,%DateTimeIn('1900-01-01-00.00.00.000000')) > COALESCE(RW.LASTUPDDTTM ,%DateTimeIn('1900-01-01-00.00.00.000000')) THEN COALESCE(A.LASTUPDDTTM ,%DateTimeIn('1900-01-01-00.00.00.000000')) ELSE COALESCE(RW.LASTUPDDTTM ,%DateTimeIn('1900-01-01-00.00.00.000000')) END ,D.DESCR50 ,A.PAYGROUP ,A.GP_PAYGROUP ,A.PAY_SYSTEM_FLG , COALESCE(I.DESCR ,' ' ) ,COALESCE(M.DESCR ,' ' ) ,A. ABSENCE_SYSTEM_CD ,A.COMPANY ,COALESCE(J.XLATLONGNAME ,' ' ) ,A.ESTABID ,K.HR_SRCH_ABS_STM_CD ,COALESCE(J.XLATLONGNAME ,' ' ) ,COALESCE(K.XLATLONGNAME ,' ' ) ,N.LAST_NAME_SRCH ,N.SECOND_LAST_SRCH ,N.NAME_DISPLAY ,A.SUPERVISOR_ID ,A.REPORTS_TO ,A.ACTION ,A.ACTION_REASON ,A.HR_STATUS ,A.EMPL_STATUS ,A.EMPL_TYPE ,N.PREF_FIRST_NAME ,%Coalesce(( SELECT N.NAME_DISPLAY FROM PS_NAMES N WHERE N.EMPLID = A.SUPERVISOR_ID AND N.NAME_TYPE = 'PRI' AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N1.EMPLID = N.EMPLID AND N1.NAME_TYPE = 'PRI' AND N1.EFFDT <= %CurrentDateIn )),' ') ,%Coalesce (( SELECT P.DESCR FROM PS_POSITION_DATA P WHERE P.POSITION_NBR=A.REPORTS_TO AND P.EFFDT=( SELECT MAX(EFFDT) FROM PS_POSITION_DATA P2 WHERE P.POSITION_NBR=P2.POSITION_NBR AND P2.EFFDT<=%CurrentDateIn AND P2.EFF_STATUS = 'A')),' ') ,PORG.BUSINESS_TITLE ,%Coalesce(M3.MILITARY_SERVICE,' ') ,%Coalesce(M1.MIL_RANK,' ') ,%Coalesce(M1.MIL_WORN_RANK,' ') ,%Coalesce(( SELECT A2.DESCR FROM PS_BUS_UNIT_TBL_HR A2 WHERE A.BUSINESS_UNIT = A2.BUSINESS_UNIT),' ') ,A.FLSA_STATUS ,RW.START_DATE ,%Coalesce(RW.HR_RW_TYPE , 'X') ,'reason' ,%Coalesce(RW.HR_RW_LOC_TYPE ,' ') ,%Coalesce(RW.HR_RW_LOCATION ,' ') ,%Coalesce(RW.RW_PERCENT ,0) ,%Coalesce(RW.RW_PERCENT_DESCR ,' ') ,%Coalesce(RW.RW_TYPE_DESCR ,'Not Remote') ,%Coalesce(RW.RW_DAY1,' ') ,%Coalesce(RW.RW_DAY2,' ') ,%Coalesce(RW.RW_DAY3,' ') ,%Coalesce(RW.RW_DAY4 ,' ') ,%Coalesce(RW.RW_DAY5 ,' ') ,%Coalesce(RW.RW_DAY6 ,' ') ,%Coalesce(RW.RW_DAY7 ,' ') ,'A' FROM (((((((((PS_JOB A LEFT OUTER JOIN PS_PAYGROUP_TBL I ON I.COMPANY = A.COMPANY AND I.PAYGROUP = A.PAYGROUP ) LEFT OUTER JOIN PS_GP_SRCH_PAYSTM J ON J.PAY_SYSTEM_FLG = A.PAY_SYSTEM_FLG ) LEFT OUTER JOIN PS_HR_SRCH_ABS_STM K ON K.HR_SRCH_ABS_STM_CD = A.ABSENCE_SYSTEM_CD) LEFT OUTER JOIN PS_GP_PYGRP M ON M.GP_PAYGROUP = A.GP_PAYGROUP) LEFT OUTER JOIN PS_JOB_MIL M1 ON (A.EMPLID=M1.EMPLID AND A.EMPL_RCD=M1.EMPL_RCD AND A.EFFDT=M1.EFFDT AND A.EFFSEQ=M1.EFFSEQ)) LEFT OUTER JOIN PS_JOB_MIL_ASGN_VW M2 ON (A.EMPLID=M2.EMPLID AND A.EMPL_RCD=M2.EMPL_RCD)) LEFT OUTER JOIN PS_MIL_RANK_TBL M3 ON (M3.MILITARY_SERVICE=M2.MILITARY_SERVICE AND M3.MIL_RANK=M1.MIL_RANK))LEFT OUTER JOIN PS_HR_RMWRK_DAT RW ON RW.EMPLID=A.EMPLID AND A.EMPL_RCD=RW.EMPL_RCD)LEFT OUTER JOIN ( SELECT * FROM PS_LOCATION_TBL C WHERE C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_LOCATION_TBL C1 WHERE C1.SETID = C.SETID AND C1.LOCATION = C.LOCATION AND C1.EFFDT <= %CurrentDateIn))C ON C.SETID = A.SETID_LOCATION AND C.LOCATION = A.LOCATION ) , PS_DEPT_TBL E , PS_JOBCODE_TBL F,PS_REG_REGION_TBL D , PS_COMPANY_TBL L , PS_NAMES N ,PS_PER_ORG_ASGN PORG WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <=%CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) AND A.EMPL_RCD = ( SELECT MAX(A_ED.EMPL_RCD) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A.EFFDT = A_ED.EFFDT) AND N.EMPLID = A.EMPLID AND N.NAME_TYPE = 'PRI' AND N.EFFDT = ( SELECT MAX(N1.EFFDT) FROM PS_NAMES N1 WHERE N1.EMPLID = N.EMPLID AND N1.NAME_TYPE = 'PRI' AND N1.EFFDT <= %CurrentDateIn ) AND L.COMPANY = A.COMPANY AND D.REG_REGION = A.REG_REGION AND A.EMPLID=PORG.EMPLID AND A.EMPLID=N.EMPLID AND A.EMPL_RCD=PORG.EMPL_RCD AND E.SETID = A.SETID_DEPT AND E.DEPTID = A.DEPTID AND E.EFFDT = ( SELECT MAX(E_ED.EFFDT) FROM PS_DEPT_TBL E_ED WHERE E.SETID = E_ED.SETID AND E.DEPTID = E_ED.DEPTID AND E_ED.EFFDT <= %CurrentDateIn ) AND F.SETID = A.SETID_JOBCODE AND F.JOBCODE = A.JOBCODE AND F.EFFDT = ( SELECT MAX(F_ED.EFFDT) FROM PS_JOBCODE_TBL F_ED WHERE F.SETID = F_ED.SETID AND F.JOBCODE = F_ED.JOBCODE AND F_ED.EFFDT <= %CurrentDateIn ) AND (I.EFFDT = ( SELECT MAX(I_ED.EFFDT) FROM PS_PAYGROUP_TBL I_ED WHERE I.COMPANY = I_ED.COMPANY AND I.PAYGROUP = I_ED.PAYGROUP AND I_ED.EFFDT <= %CurrentDateIn ) OR I.EFFDT IS NULL) AND L.EFFDT = ( SELECT MAX(L_ED.EFFDT) FROM PS_COMPANY_TBL L_ED WHERE L.COMPANY = L_ED.COMPANY AND L_ED.EFFDT <= A.EFFDT)
|
EMPLID