WITH SUPERVISOR AS ( select D.ID_NUMBER as SUPERVISOR_EMPLOYEE_ID,D.GIVEN_NAME +' '+D.FAMILY_NAME as SUPERVISOR_NAME,POSITION.POSITION_CODE,POSITION.JOB_POS_TITLE,D.USER_ID,D.ALIAS_NAME from "finprod"."dbo"."HRHMN_EMPL_REF" D, ( select ID_NUMBER,POSITION_CODE,JOB_POS_TITLE from ( select A.* from HRHMN_EMPL_JOB_POS A, ( select POSITION_CODE,max(VERS) VERS,MAX(EFFECTIVE_DATE) EFD,MAX(cast(cast(cast(LAST_MOD_DATEI as DATE)as varchar) +' '+right(stuff(LAST_MOD_TIMEI,len(LAST_MOD_TIMEI)-3,len(LAST_MOD_TIMEI),''),2) +':'+ right(stuff(LAST_MOD_TIMEI,len(LAST_MOD_TIMEI)-1,len(LAST_MOD_TIMEI),''),2)+':'+ right( LAST_MOD_TIMEI,2) as datetime)) LSMODDATETIME from HRHMN_EMPL_JOB_POS --where POSITION_CODE=100544 group by POSITION_CODE ) B WHERE A.POSITION_CODE=B.POSITION_CODE --and A.VERS=B.VERS --and A.EFFECTIVE_DATE=B.EFD and cast(cast(cast(A.LAST_MOD_DATEI as DATE)as varchar) +' '+right(stuff(A.LAST_MOD_TIMEI,len(A.LAST_MOD_TIMEI)-3,len(A.LAST_MOD_TIMEI),''),2)+':'+ right(stuff(A.LAST_MOD_TIMEI,len(A.LAST_MOD_TIMEI)-1,len(A.LAST_MOD_TIMEI),''),2)+':'+ right( A.LAST_MOD_TIMEI,2) as datetime)=B.LSMODDATETIME ) A --WHERE A.POSITION_CODE=100544 )POSITION where D.ID_NUMBER=POSITION.ID_NUMBER ) --select * from SUPERVISOR SELECT M.samaccountname, ISNULL ((select FDESCR from GLF_SELN_CODE where SELN_TYPE='SEC' and SELN_CODE=M.extensionAttribute2),'') AS extensionAttribute2, ISNULL ((select FDESCR from GLF_SELN_CODE where SELN_TYPE='TEAM' and SELN_CODE=M.extensionAttribute3),'') AS extensionAttribute3, ISNULL ((select FDESCR from GLF_SELN_CODE where SELN_TYPE='SUBT' and SELN_CODE=M.extensionAttribute4),'') AS extensionAttribute4, M.extensionAttribute5, --(select TOP 1 [USER_ID] from SUPERVISOR where POSITION_CODE=M.Supervisor_Position) AS manager_orig, case --when --len(HRHMN_EMPL_REF.FAMILY_NAME)>13 then (select FUP.OVERRIDE_USER_ID from F1_USER_PROFILE FUP where FUP.[USER_ID]=HRHMN_EMPL_REF.USER_ID) when len((select FUP.OVERRIDE_USER_ID from F1_USER_PROFILE FUP where FUP.[USER_ID]=(select TOP 1 [USER_ID] from SUPERVISOR where POSITION_CODE=M.Supervisor_Position)))>0 then (select FUP.OVERRIDE_USER_ID from F1_USER_PROFILE FUP where FUP.[USER_ID]=(select TOP 1 [USER_ID] from SUPERVISOR where POSITION_CODE=M.Supervisor_Position)) else (select TOP 1 [USER_ID] from SUPERVISOR where POSITION_CODE=M.Supervisor_Position) end as manager_samaccountname FROM ( SELECT HRHMN_EMPL_REF.ID_NUMBER AS Employee_id, HRHMN_EMPL_JOB_CTL.EFFECTIVE_DATE AS Effective_Date, case --when --len(HRHMN_EMPL_REF.FAMILY_NAME)>13 then (select FUP.OVERRIDE_USER_ID from F1_USER_PROFILE FUP where FUP.[USER_ID]=HRHMN_EMPL_REF.USER_ID) when len((select FUP.OVERRIDE_USER_ID from F1_USER_PROFILE FUP where FUP.[USER_ID]=HRHMN_EMPL_REF.USER_ID))>0 then (select FUP.OVERRIDE_USER_ID from F1_USER_PROFILE FUP where FUP.[USER_ID]=HRHMN_EMPL_REF.USER_ID) else HRHMN_EMPL_REF.USER_ID end AS samaccountname, JOB_HRSYS_USRSELN_DTL.SELN_CODE1 AS Department,JOB_HRSYS_USRSELN_DTL.SELN_CODE3 AS extensionAttribute5, JOB_HRSYS_USRSELN_DTL.SELN_CODE4 AS extensionAttribute2, JOB_HRSYS_USRSELN_DTL.SELN_CODE5 AS extensionAttribute3, JOB_HRSYS_USRSELN_DTL.SELN_CODE6 AS extensionAttribute4, HRSYS_HIEREFF_MST.POSITION_SUPERVSR AS Supervisor_Position FROM HRHMN_EMPL_JOB_POS INNER JOIN HRHMN_EMPL_JOB_CTL ON HRHMN_EMPL_JOB_CTL.id_number = HRHMN_EMPL_JOB_POS.id_number AND HRHMN_EMPL_JOB_CTL.effective_date = HRHMN_EMPL_JOB_POS.effective_date AND HRHMN_EMPL_JOB_CTL.effective_date = (select max(SUBDATE.effective_date)from hrhmn_empl_job_ctl SUBDATE where SUBDATE.id_number = HRHMN_EMPL_JOB_CTL.id_number and SUBDATE.effective_date <= CONVERT(datetime,getdate(), 20) ) AND ( hrhmn_empl_job_pos.position_type_ind = (SELECT MIN (subtype.position_type_ind) FROM hrhmn_empl_job_pos subtype WHERE subtype.id_number = hrhmn_empl_job_ctl.id_number AND subtype.effective_date = hrhmn_empl_job_ctl.effective_date AND (subtype.end_date >= CONVERT(datetime,getdate(), 20)OR subtype.end_date <= '1-Jan-1900') ) OR (hrhmn_empl_job_pos.position_type_ind = 'S' AND hrhmn_empl_job_pos.id_number = hrhmn_empl_job_ctl.id_number AND hrhmn_empl_job_pos.effective_date = hrhmn_empl_job_ctl.effective_date AND (hrhmn_empl_job_pos.end_date >= CONVERT(datetime,getdate(), 20) OR hrhmn_empl_job_pos.end_date <= '1-Jan-1900') ) ) AND HRHMN_EMPL_JOB_CTL.id_number not in (select HRHMN_EMPLTERM_DTL.id_number from HRHMN_EMPLTERM_DTL where HRHMN_EMPLTERM_DTL.id_number = HRHMN_EMPL_JOB_CTL.id_number and HRHMN_EMPLTERM_DTL.term_date >= HRHMN_EMPL_JOB_CTL.effective_date and HRHMN_EMPLTERM_DTL.term_date < CONVERT(datetime,getdate(), 20)) LEFT OUTER JOIN HRHMN_EMPL_REF ON HRHMN_EMPL_JOB_CTL.ID_NUMBER = HRHMN_EMPL_REF.ID_NUMBER LEFT OUTER JOIN HRSYS_USRSELN_DTL JOB_HRSYS_USRSELN_DTL ON JOB_HRSYS_USRSELN_DTL.ENTITY_TYPE = 'EPS' AND (NOT (JOB_HRSYS_USRSELN_DTL.ENTITY_KEY_2 = 'POSITION')) AND JOB_HRSYS_USRSELN_DTL.ENTITY_KEY_1 = CAST(HRHMN_EMPL_JOB_CTL.ID_NUMBER AS VARCHAR(10)) AND JOB_HRSYS_USRSELN_DTL.ENTITY_KEY_2 = HRHMN_EMPL_JOB_POS.EFFECTIVE_DATE AND JOB_HRSYS_USRSELN_DTL.ENTITY_KEY_3 = HRHMN_EMPL_JOB_POS.POSITION_CODE AND JOB_HRSYS_USRSELN_DTL.UNIQUE_NBR IN (SELECT Job1.UNIQUE_NBR FROM HRSYS_USRSELN_DTL Job1 WHERE Job1.ENTITY_TYPE = 'EPS' AND (NOT (Job1.ENTITY_KEY_2 = 'POSITION')) AND Job1.ENTITY_KEY_1 = CAST(HRHMN_EMPL_JOB_CTL.ID_NUMBER AS VARCHAR(10)) AND Job1.ENTITY_KEY_2 = HRHMN_EMPL_JOB_POS.EFFECTIVE_DATE AND Job1.ENTITY_KEY_3 = HRHMN_EMPL_JOB_POS.POSITION_CODE AND Job1.UNIQUE_NBR = (SELECT MAX(Job2.UNIQUE_NBR) FROM HRSYS_USRSELN_DTL Job2 WHERE Job2.ENTITY_TYPE = 'EPS' AND (NOT (Job2.ENTITY_KEY_2 = 'POSITION')) AND Job2.ENTITY_KEY_1 = CAST(HRHMN_EMPL_JOB_CTL.ID_NUMBER AS VARCHAR(10)) AND Job2.ENTITY_KEY_2 = HRHMN_EMPL_JOB_POS.EFFECTIVE_DATE AND Job2.ENTITY_KEY_3 = HRHMN_EMPL_JOB_POS.POSITION_CODE)) INNER JOIN (HRSYS_HIEREFF_MST INNER JOIN HRSYS_HIERARCH_CTL ON HRSYS_HIERARCH_CTL.COMPANY_CODE = HRSYS_HIEREFF_MST.COMPANY_CODE AND HRSYS_HIERARCH_CTL.HIERARCHY_CODE = HRSYS_HIEREFF_MST.HIERARCHY_CODE AND HRSYS_HIERARCH_CTL.HIERARCHY_TYPE = HRSYS_HIEREFF_MST.HIERARCHY_TYPE) ON HRHMN_EMPL_JOB_CTL.COMPANY_CODE = HRSYS_HIEREFF_MST.COMPANY_CODE AND HRHMN_EMPL_JOB_POS.POSITION_CODE = HRSYS_HIEREFF_MST.HIERARCHY_CODE WHERE HRSYS_HIEREFF_MST.effective_datei = (select max(SUBDATE.effective_datei) from HRSYS_HIEREFF_MST SUBDATE where SUBDATE.company_code = HRSYS_HIEREFF_MST.company_code and SUBDATE.hierarchy_code = HRSYS_HIEREFF_MST.hierarchy_code and SUBDATE.hierarchy_type = 'POSITION' and SUBDATE.effective_datei <= CONVERT(datetime,getdate(), 20) ) AND HRSYS_HIEREFF_MST.HIERARCHY_TYPE='POSITION' )M where len(M.samaccountname)!=0