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