SQL to LINQ conversion error

No Error Amira.. the count of result is the issue.

I am sending you the SQL Query. pls check it

 

USE [NOV-28-02-2025]
GO
/****** Object:  StoredProcedure [dbo].[RetrieveEmployeeInformationAPI]    Script Date: 04-04-2025 19:54:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[RetrieveEmployeeInformationAPI]
@Mode varchar(50)='',
@EmpID int=0,
@RoleID int=0,
@UserID int=0,
@EmpIDs VARCHAR(max)='0',
@SystemStatus varchar(10)='0',
@FilterType varchar(100)='',
@Status varchar(15)='0',
@DurationFrom DATETIME=null,
@DurationTo DATETIME=null,
@ProbationStatus varchar(10)=''
AS
BEGIN
SET NOCOUNT ON; --Added to prevent extra result sets from interfering with SELECT statements.

 If @Mode='ConfirmedEditEmployeeList'
   BEGIN

   declare @InfoFormat int=0
   select @InfoFormat=[dbo].[GetDefaultCompanyParameter](CONVERT(VARCHAR, @EmpID),'EmpInfoFormat','COM') 

   if (@InfoFormat=1 or @InfoFormat=0)
    BEGIN
        Declare @CurrentStatusDesc varchar(10)='';
        select @CurrentStatusDesc=Status from EmployeeCurrentStatus where Status_Desc='On Notice'
        if exists(SELECT LinkLevel  FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and  s.LinkLevel=15)
         begin
          ;with cteEmployeeDetails as (
          select a.Emp_Id,a.Emp_Code,(case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
          isnull(a.Guardians_Name,'') as Guardians_Name,
          FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate, 
          case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,
          a.SeperationStatus,
          [dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,Is_probation,LastEntity,
          CurrentStatus,emp_status,isnull(EmpFileNumber,'') as EmpFileNumber,isnull(a.IsSave,0) as IsSave  
          from HR_EMP_MASTER a  WITH (NOLOCK)
          left join Resignation t on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
              where
            (
             (a.Join_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=1)
             or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=2)
             or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=3)     or @FilterType is null     or  @FilterType='' or @FilterType=0
            )
            and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
            and (
                a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID not in(select item from dbo.Split(@Status,',')))
                or
                (a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7')) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID in(select item from dbo.Split(@Status,',')))            
                )
            and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
            and isnull(IsDelete,0)=0
            and (a.Emp_Id in(select item from dbo.Split(@EmpIDs,',')) or @EmpIDs='0')
            )
            
    
           select a.Emp_Id,i.image_url,a.Emp_Code,a.Name,Guardians_Name,a.DateOfBirth,
           JoinDate,DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
           isnull(a.Gender,'NA')as Gender,
           FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,
           FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
           b.PersonalEMail,b.Phone,
           case when Marital_Status='S' then 'Single' 
           when Marital_Status='M' then 'Married' 
           when Marital_Status='W' then 'Widowed' 
           when Marital_Status='X' then 'Separated' 
           when Marital_Status='D' then 'Divorcee' 
           ELSE 'NA' end as Marital_Status,Age,a.Probation_Dt,
           f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
           f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
           rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
           case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
           cm.Country_Name as Nationality,a.IsSave,a.EmpFileNumber,a.CurrentStatus,IsSave
           from cteEmployeeDetails a 
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on r.emp_id=a.Emp_Id  
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            left join HR_EMP_IMAGES i  WITH (NOLOCK) on i.emp_id=a.Emp_Id
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            inner join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and  t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK) on cm.Country_ID=c.Nationality
            order by a.Emp_Code asc
          end
         else
          begin
            if (@EmpIDs='0')--1
              begin
              declare @lnklev int=0,@linkselect int=0
              
              IF exists(select 1 from Specialaccessrights where roleid=@RoleID)
              begin
              select @linkselect=LinkLevel from Specialaccessrights  where roleid=@RoleID
              end
              else
              begin
              select top 1 @linkselect=LinkLevel  from EntityAccessRights02  where roleid=@RoleID order by LinkLevel
              end
              ;with ctnew(item,LinkLevel) as(
              select item,ROW_NUMBER() OVER(ORDER BY (SELECT 1))+1 AS LinklevelSelf from  dbo.SplitStrings_XML((select  EmpEntity from HR_EMP_MASTER where Emp_Id=@EmpID),',' ) f)
              
              ,ApplicableFinalnew (item,LinkLevel)
                              AS
                              (
                                  SELECT f.item as item,LinkLevel as new FROM EntityAccessRights02 AS  s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and f.item<>''
                                  union all
                                  select  item,LinkLevel from ctnew  where  @lnklev>0  and ctnew.LinkLevel>=@lnklev and ctnew.item<>'' 
                              )
                          
              select @EmpIDs=COALESCE(@EmpIDs, '') + CONVERT(VARCHAR(MAX),(Emp_Id))+','  from HR_EMP_MASTER d  where 
              exists (SELECT LinkLevel  FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and  s.LinkLevel=15) or 
               Emp_Id in (            
              (select Emp_Id AS Employee from HR_EMP_MASTER d  WITH (NOLOCK)     
               join HighLevelViewTable a  WITH (NOLOCK) on d.LastEntity=a.LastEntityID and isnull(d.IsDelete,0)=0
               inner join  ApplicableFinalnew b  WITH (NOLOCK) on  
              ((a.LevelOneId=b.item and b.linklevel=1)
              or (a.LevelTwoId=b.item and b.linklevel=2)
              or (a.LevelThreeId=b.item and b.linklevel=3)
              or (a.LevelFourId=b.item and b.linklevel=4)
              or (a.LevelFiveId=b.item and b.linklevel=5)
              or (a.LevelSixId=b.item and b.linklevel=6)
              or (a.LevelSevenId=b.item and b.linklevel=7)
              or (a.LevelEightId=b.item and b.linklevel=8)
              or (a.LevelNineId=b.item and b.linklevel=9)
              or (a.LevelTenId=b.item and b.linklevel=10)
              or (a.LevelElevenId=b.item and b.linklevel=11)
              or (a.LevelTwelveId=b.item and b.linklevel=12))))
    
           end
    
           declare @strEmployees xml
           set @strEmployees=( 
           select Item as EmpID from dbo.Split(@EmpIDs,',')
           FOR XML Path('Employee') ,ROOT('EmployeeArray'));
    
      if exists(select 1 from HR_EMP_MASTER where isnull(IsSave,0)=1)  --If Saved Employee Exist
       begin
    
         IF OBJECT_ID('tempdb..#tempConfirmedEmployee') IS NOT NULL drop table #tempConfirmedEmployee
         IF OBJECT_ID('tempdb..#tempSavedEmployee') IS NOT NULL drop table #tempSavedEmployee
    
         select a.Emp_Id,i.image_url,a.Emp_Code,
         (case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
         isnull(a.Guardians_Name,'') as Guardians_Name,
         FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
         case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,
         FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
         b.PersonalEMail,b.Phone,
          case when Marital_Status='S' then 'Single' 
          when Marital_Status='M' then 'Married' 
          when Marital_Status='W' then 'Widowed' 
          when Marital_Status='X' then 'Separated' 
          when Marital_Status='D' then 'Divorcee' 
          ELSE 'NA' end as Marital_Status,[dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,
          f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
          f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
          rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
          case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
          cm.Country_Name as Nationality,isnull(a.IsSave,0) as IsSave,isnull(a.EmpFileNumber,'') as EmpFileNumber
          into #tempConfirmedEmployee from HR_EMP_MASTER a  WITH (NOLOCK)
            join @strEmployees.nodes('EmployeeArray/Employee') as x(v) on a.Emp_Id=x.v.value('EmpID[1]','int')
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on  r.emp_id=a.Emp_Id  
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            left join HR_EMP_IMAGES i  WITH (NOLOCK)  on i.emp_id=a.Emp_Id
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            inner join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK)  on cm.Country_ID=c.Nationality
            --where ((a.emp_status in (@Status) or @Status='') and (a.CurrentStatus in (@SystemStatus) or @SystemStatus='') and s.isResignation<>1) and
            where
            (
             (a.Join_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=1)
             or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=2)
             or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=3)     or @FilterType is null     or  @FilterType='' or @FilterType=0
            )
            and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
            and (
                a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID not in(select item from dbo.Split(@Status,',')))
                or
                (a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7')) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID in(select item from dbo.Split(@Status,',')))            
                )
            and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
            and (isnull(IsDelete,0)=0)OPTION(RECOMPILE)
                    
            select a.Emp_Id,i.image_url,a.Emp_Code,
            (case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
            isnull(a.Guardians_Name,'') as Guardians_Name,
            FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
            case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,
            '' as Resignation_Date,'' as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
            case when Marital_Status='S' then 'Single' 
            when Marital_Status='M' then 'Married' 
            when Marital_Status='W' then 'Widowed' 
            when Marital_Status='X' then 'Separated' 
            when Marital_Status='D' then 'Divorcee' 
            ELSE 'NA' end as Marital_Status,[dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,
            isnull(LevelOneDescription,'NA') as LevelOneDescription,isnull(LevelTwoDescription,'NA') as LevelTwoDescription,isnull(LevelThreeDescription,'NA') as LevelThreeDescription,
            isnull(LevelFourDescription,'NA') as LevelFourDescription,isnull(LevelFiveDescription,'NA') as LevelFiveDescription,isnull(LevelSixDescription,'NA') as LevelSixDescription,
            isnull(LevelSevenDescription,'NA') as LevelSevenDescription ,isnull(LevelEightDescription,'NA') as LevelEightDescription,
            isnull(LevelNineDescription,'NA') as LevelNineDescription ,isnull(LevelTenDescription,'NA') as LevelTenDescription,isnull(LevelElevenDescription,'NA') as LevelElevenDescription,
            rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
            case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
            cm.Country_Name as Nationality,isnull(a.IsSave,0) as IsSave,isnull(a.EmpFileNumber,'') as EmpFileNumber ,a.CurrentStatus
            into #tempSavedEmployee
            from HR_EMP_MASTER a  WITH (NOLOCK)
            left join HR_EMP_IMAGES i  WITH (NOLOCK) on i.emp_id=a.Emp_Id
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on r.emp_id=a.Emp_Id  
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            left join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' --and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK) on cm.Country_ID=c.Nationality
            where (a.CurrentStatus = @SystemStatus or @SystemStatus='0') and a.Entry_By=@UserID
            
            select * from #tempConfirmedEmployee WHERE IsSave=0 
            union
            select * from #tempSavedEmployee WHERE IsSave=1
            order by Emp_Code
    
            IF OBJECT_ID('tempdb..#tempConfirmedEmployee') IS NOT NULL drop table #tempConfirmedEmployee
            IF OBJECT_ID('tempdb..#tempSavedEmployee') IS NOT NULL drop table #tempSavedEmployee
         end
        else --If Saved Employee dont Exist
         BEGIN
          select a.Emp_Id,i.image_url,a.Emp_Code,
          (case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
          isnull(a.Guardians_Name,'') as Guardians_Name,
          FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
           case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,
           FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
           b.PersonalEMail,b.Phone,
           case when Marital_Status='S' then 'Single' 
           when Marital_Status='M' then 'Married' 
           when Marital_Status='W' then 'Widowed' 
           when Marital_Status='X' then 'Separated' 
           when Marital_Status='D' then 'Divorcee' 
           ELSE 'NA' end as Marital_Status,[dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,
           f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
           f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
           rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
           case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
           cm.Country_Name as Nationality,isnull(a.IsSave,0) as IsSave,isnull(a.EmpFileNumber,'') as EmpFileNumber,a.CurrentStatus
           from HR_EMP_MASTER a  WITH (NOLOCK)
            join @strEmployees.nodes('EmployeeArray/Employee') as x(v) on a.Emp_Id=x.v.value('EmpID[1]','int')
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id 
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on r.emp_id=a.Emp_Id  
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            left join HR_EMP_IMAGES i  WITH (NOLOCK) on i.emp_id=a.Emp_Id
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            inner join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK) on cm.Country_ID=c.Nationality
            --where ((a.emp_status in (@Status) or @Status='') and (a.CurrentStatus in (@SystemStatus) or @SystemStatus='') and s.isResignation<>1) and
            where
            (
             (a.Join_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=1)
             or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=2)
             or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=3)     or @FilterType is null     or  @FilterType='' or @FilterType=0
            )
            and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
            and (
                a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID not in(select item from dbo.Split(@Status,',')))
                or
                (a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7')) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID in(select item from dbo.Split(@Status,',')))            
                )
            and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
            and (isnull(IsDelete,0)=0)
            order by a.Emp_Code asc
            OPTION(RECOMPILE)
          END
        END        
      END
    else if (@InfoFormat=2)  --Novelty
     BEGIN
        set @CurrentStatusDesc ='';
        select @CurrentStatusDesc=Status from EmployeeCurrentStatus where Status_Desc='On Notice'
        if exists(SELECT LinkLevel  FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and  s.LinkLevel=15)
         begin
          ;with cteEmployeeDetails as (
          select a.Emp_Id,a.Emp_Code,(case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
          isnull(a.Guardians_Name,'') as Guardians_Name,
          FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,
          case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,
          a.SeperationStatus,
          [dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,Is_probation,LastEntity,CurrentStatus,emp_status,a.IsSave,isnull(EmpFileNumber,'') as EmpFileNumber,
          isnull(DailyRateTypeId,0) as DailyRateTypeId,Isnull(PayrollMode,0) as PayrollMode
          from HR_EMP_MASTER a  WITH (NOLOCK)
          left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
              where
            (
             (a.Join_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=1)
             or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=2)
             or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=3)     or @FilterType is null     or  @FilterType='' or @FilterType=0
            )
            and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
            and (
                a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID not in(select item from dbo.Split(@Status,',')))
                or
                (a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7')) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID in(select item from dbo.Split(@Status,',')))            
                )
            and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
            and isnull(IsDelete,0)=0
            and (a.Emp_Id in(select item from dbo.Split(@EmpIDs,',')) or @EmpIDs='0') 
            )

            ,ctePayscale as 
            (
              select EmployeeId,TotalPay from (
              select EmployeeId,TotalPay,
              RANK() OVER(PARTITION BY EmployeeID  ORDER BY EffectiveFrom desc) as Ranks  from Payscale00
              where EffectiveFrom<=GETUTCDATE())a
              where a.Ranks=1
            )

           select a.Emp_Id,i.image_url,a.Emp_Code,a.Name,a.Guardians_Name,a.DateOfBirth,
           JoinDate,DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
           isnull(a.Gender,'NA')as Gender,FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,
           FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
           b.PersonalEMail,b.Phone,
           case when Marital_Status='S' then 'Single' 
           when Marital_Status='M' then 'Married' 
           when Marital_Status='W' then 'Widowed' 
           when Marital_Status='X' then 'Separated' 
           when Marital_Status='D' then 'Divorcee' 
           ELSE 'NA' end as Marital_Status,Age,a.Probation_Dt,
           f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
           f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
           rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
           case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
           cm.Country_Name as Nationality,isnull(a.IsSave,0) as IsSave,a.EmpFileNumber,isnull(relm.Religion_Name,'') as Religion_Name,
           isnull(c.Blood_Grp,'') as Blood_Grp,rmd.Emp_Code as ReportingEmployeeCode,rmd.Name as ReportingEmployeeName,
           case when a.SeperationStatus=0 then 'Live' when a.SeperationStatus>0 then 'Resigned' end as WorkingStatus,
           case when t.RelievingDate is null then '' else FORMAT(t.RelievingDate,'dd-MM-yyyy') end as RelievingDate,
           --'Monthly' as SalaryType,
           --case when a.DailyRateTypeId=0 then 'Monthly'
           --when a.DailyRateTypeId=1 and (a.PayrollMode=1 or a.PayrollMode=0) then 'Monthly'
           --when a.DailyRateTypeId=1 and (a.PayrollMode=2) then 'Daily' end as SalaryType,
           case when  (a.PayrollMode=1 or a.PayrollMode=0) then 'Monthly Wage'
           when (a.PayrollMode=2) then 'Daily Wage' end as SalaryType,
           isnull(cpay.TotalPay,0) as GrossSalary,a.CurrentStatus
           from cteEmployeeDetails a  WITH (NOLOCK)
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on r.emp_id=a.Emp_Id 
            left join Employeedetails rmd on rmd.Emp_Id=r.Reprot_to_whome
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            left join HR_EMP_IMAGES i  WITH (NOLOCK) on i.emp_id=a.Emp_Id
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            inner join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK) on cm.Country_ID=c.Nationality
            left join ADM_Religion_Master relm  WITH (NOLOCK) on c.Religion=relm.Religion_ID
            left join ctePayscale cpay  WITH (NOLOCK) on cpay.EmployeeId=a.Emp_Id
            order by a.Emp_Code asc
          end
         else
          begin
            if (@EmpIDs='0')--2
              begin
              set @lnklev =0;set @linkselect =0
              
              IF exists(select 1 from Specialaccessrights where roleid=@RoleID)
              begin
              select @linkselect=LinkLevel from Specialaccessrights where roleid=@RoleID
              end
              else
              begin
              select top 1 @linkselect=LinkLevel  from EntityAccessRights02 where roleid=@RoleID order by LinkLevel
              end
              ;with ctnew(item,LinkLevel) as(
              select item,ROW_NUMBER() OVER(ORDER BY (SELECT 1))+1 AS LinklevelSelf from  dbo.SplitStrings_XML((select  EmpEntity from HR_EMP_MASTER where Emp_Id=@EmpID),',' ) f)
              
              ,ApplicableFinalnew (item,LinkLevel)
                              AS
                              (
                                  SELECT f.item as item,LinkLevel as new FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and f.item<>''
                                  union all
                                  select  item,LinkLevel from ctnew  where  @lnklev>0  and ctnew.LinkLevel>=@lnklev and ctnew.item<>'' 
                              )
                          
              select @EmpIDs=COALESCE(@EmpIDs, '') + CONVERT(VARCHAR(MAX),(Emp_Id))+','  from HR_EMP_MASTER d  where 
              exists (SELECT LinkLevel  FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and  s.LinkLevel=15) or 
               Emp_Id in (            
              (select Emp_Id AS Employee from HR_EMP_MASTER d  WITH (NOLOCK)       
               join HighLevelViewTable a  WITH (NOLOCK) on d.LastEntity=a.LastEntityID and isnull(d.IsDelete,0)=0 and isnull(d.IsSave,0)=0
               inner join  ApplicableFinalnew b  WITH (NOLOCK) on  
              ((a.LevelOneId=b.item and b.linklevel=1)
              or (a.LevelTwoId=b.item and b.linklevel=2)
              or (a.LevelThreeId=b.item and b.linklevel=3)
              or (a.LevelFourId=b.item and b.linklevel=4)
              or (a.LevelFiveId=b.item and b.linklevel=5)
              or (a.LevelSixId=b.item and b.linklevel=6)
              or (a.LevelSevenId=b.item and b.linklevel=7)
              or (a.LevelEightId=b.item and b.linklevel=8)
              or (a.LevelNineId=b.item and b.linklevel=9)
              or (a.LevelTenId=b.item and b.linklevel=10)
              or (a.LevelElevenId=b.item and b.linklevel=11)
              or (a.LevelTwelveId=b.item and b.linklevel=12))))
    
           end
    
           declare @strEmployees2 xml
           set @strEmployees2=( 
           select Item as EmpID from dbo.Split(@EmpIDs,',')
           FOR XML Path('Employee') ,ROOT('EmployeeArray'));

           ; with ctePayscale as 
            (
              select EmployeeId,TotalPay from (
              select EmployeeId,TotalPay,
              RANK() OVER(PARTITION BY EmployeeID  ORDER BY EffectiveFrom desc) as Ranks  from Payscale00
              where EffectiveFrom<=GETUTCDATE())a
              where a.Ranks=1
            )

           select a.Emp_Id,i.image_url,a.Emp_Code,
          (case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
           isnull(a.Guardians_Name,'') as Guardians_Name, 
           FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
           case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,       
           FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
           b.PersonalEMail,b.Phone,
           case when Marital_Status='S' then 'Single' 
           when Marital_Status='M' then 'Married' 
           when Marital_Status='W' then 'Widowed' 
           when Marital_Status='X' then 'Separated' 
           when Marital_Status='D' then 'Divorcee' 
           ELSE 'NA' end as Marital_Status,[dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,
           f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
           f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
           rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
           case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
           cm.Country_Name as Nationality,isnull(a.IsSave,0) as IsSave,isnull(a.EmpFileNumber,'') as EmpFileNumber,isnull(relm.Religion_Name,'') as Religion_Name,
           isnull(c.Blood_Grp,'') as Blood_Grp,rmd.Emp_Code as ReportingEmployeeCode,rmd.Name as ReportingEmployeeName,
           case when a.SeperationStatus=0 then 'Live' when a.SeperationStatus>0 then 'Resigned' end as WorkingStatus,
           case when t.RelievingDate is null then '' else FORMAT(t.RelievingDate,'dd-MM-yyyy') end as RelievingDate,
           case when  (isnull(a.PayrollMode,0)=1 or isnull(a.PayrollMode,0)=0) then 'Monthly Wage'
           when (isnull(a.PayrollMode,0)=2) then 'Daily Wage' end as SalaryType,
           isnull(cpay.TotalPay,0) as GrossSalary,a.CurrentStatus
           from HR_EMP_MASTER a  WITH (NOLOCK)
            join @strEmployees2.nodes('EmployeeArray/Employee') as x(v) on a.Emp_Id=x.v.value('EmpID[1]','int')
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on r.emp_id=a.Emp_Id  
            left join Employeedetails rmd  WITH (NOLOCK) on rmd.Emp_Id=r.Reprot_to_whome
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            left join HR_EMP_IMAGES i  WITH (NOLOCK) on i.emp_id=a.Emp_Id
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            inner join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK) on cm.Country_ID=c.Nationality
            left join ADM_Religion_Master relm  WITH (NOLOCK) on c.Religion=relm.Religion_ID
            left join ctePayscale cpay  WITH (NOLOCK) on cpay.EmployeeId=a.Emp_Id
            --where ((a.emp_status in (@Status) or @Status='') and (a.CurrentStatus in (@SystemStatus) or @SystemStatus='') and s.isResignation<>1) and
            where
            (
             (a.Join_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=1)
             or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=2)
             or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=3)     or @FilterType is null     or  @FilterType='' or @FilterType=0
            )
            and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
            and (
                a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID not in(select item from dbo.Split(@Status,',')))
                or
                (a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7' )) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID in(select item from dbo.Split(@Status,',')))            
                )
            and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
            and (isnull(a.IsDelete,0)=0)
            order by a.Emp_Code asc
            OPTION(RECOMPILE)
        END
     END
          else if(@InfoFormat=3)---ABRECO
     BEGIN
            Declare @CurrentStatusDesc1 varchar(10)='';
        select @CurrentStatusDesc1=Status from EmployeeCurrentStatus where Status_Desc='On Notice'
        if exists(SELECT LinkLevel  FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and  s.LinkLevel=15)
         begin
          ;with cteEmployeeDetails as (
          select a.Emp_Id,a.Emp_Code,(case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
          isnull(a.Guardians_Name,'') as Guardians_Name,
          FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,
          FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate, 
          case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,
          a.SeperationStatus,
          [dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,Is_probation,LastEntity,
          CurrentStatus,emp_status,isnull(EmpFileNumber,'') as EmpFileNumber,isnull(a.IsSave,0) as IsSave  
          from HR_EMP_MASTER a  WITH (NOLOCK)
          left join Resignation t on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc1 then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc1 then 'A' else 'A' end)) and t.Rejoin_Status='P'
              where
            (
             (a.Join_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=1)
             or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=2)
             or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=3)     or @FilterType is null     or  @FilterType='' or @FilterType=0
            )
            and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc1 and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
            and (
                a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID not in(select item from dbo.Split(@Status,',')))
                or
                (a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7' or a.emp_status='9' or a.emp_status='10')) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID in(select item from dbo.Split(@Status,',')))            
                )
            and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
            and isnull(IsDelete,0)=0
            and (a.Emp_Id in(select item from dbo.Split(@EmpIDs,',')) or @EmpIDs='0')
            )
            
    
           select a.Emp_Id,i.image_url,a.Emp_Code,a.Name,Guardians_Name,a.DateOfBirth,
           JoinDate,FORMAT( c.Wedding_Date, 'dd/MM/yyyy') as WeddingDate,DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
           isnull(a.Gender,'NA')as Gender,
           FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,
           FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
           b.PersonalEMail,b.Phone,
           case when Marital_Status='S' then 'Single' 
           when Marital_Status='M' then 'Married' 
           when Marital_Status='W' then 'Widowed' 
           when Marital_Status='X' then 'Separated' 
           when Marital_Status='D' then 'Divorcee' 
           ELSE 'NA' end as Marital_Status,Age,a.Probation_Dt,
           f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
           f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
           rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
           case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
           cm.Country_Name as Nationality,a.IsSave,a.EmpFileNumber,a.CurrentStatus,IsSave
           from cteEmployeeDetails a 
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on r.emp_id=a.Emp_Id  
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            left join HR_EMP_IMAGES i  WITH (NOLOCK) on i.emp_id=a.Emp_Id
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            inner join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and  t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc1 then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc1 then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK) on cm.Country_ID=c.Nationality
            order by a.Emp_Code asc
          end

         else
          begin
            if (@EmpIDs='0')--3
              begin
              declare @lnklev1 int=0,@linkselect1 int=0
              
              IF exists(select 1 from Specialaccessrights where roleid=@RoleID)
              begin
              select @linkselect1=LinkLevel from Specialaccessrights  where roleid=@RoleID
              end
              else
              begin
              select top 1 @linkselect1=LinkLevel  from EntityAccessRights02  where roleid=@RoleID order by LinkLevel
              end
              ;with ctnew(item,LinkLevel) as(
              select item,ROW_NUMBER() OVER(ORDER BY (SELECT 1))+1 AS LinklevelSelf from  dbo.SplitStrings_XML((select  EmpEntity from HR_EMP_MASTER where Emp_Id=@EmpID),',' ) f)
              
              ,ApplicableFinalnew (item,LinkLevel)
                              AS
                              (
                                  SELECT f.item as item,LinkLevel as new FROM EntityAccessRights02 AS  s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and f.item<>''
                                  union all
                                  select  item,LinkLevel from ctnew  where  @lnklev1>0  and ctnew.LinkLevel>=@lnklev1 and ctnew.item<>'' 
                              )
                          
              select @EmpIDs=COALESCE(@EmpIDs, '') + CONVERT(VARCHAR(MAX),(Emp_Id))+','  from HR_EMP_MASTER d  where 
              exists (SELECT LinkLevel  FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and  s.LinkLevel=15) or 
               Emp_Id in (            
              (select Emp_Id AS Employee from HR_EMP_MASTER d  WITH (NOLOCK)     
               join HighLevelViewTable a  WITH (NOLOCK) on d.LastEntity=a.LastEntityID and isnull(d.IsDelete,0)=0
               inner join  ApplicableFinalnew b  WITH (NOLOCK) on  
              ((a.LevelOneId=b.item and b.linklevel=1)
              or (a.LevelTwoId=b.item and b.linklevel=2)
              or (a.LevelThreeId=b.item and b.linklevel=3)
              or (a.LevelFourId=b.item and b.linklevel=4)
              or (a.LevelFiveId=b.item and b.linklevel=5)
              or (a.LevelSixId=b.item and b.linklevel=6)
              or (a.LevelSevenId=b.item and b.linklevel=7)
              or (a.LevelEightId=b.item and b.linklevel=8)
              or (a.LevelNineId=b.item and b.linklevel=9)
              or (a.LevelTenId=b.item and b.linklevel=10)
              or (a.LevelElevenId=b.item and b.linklevel=11)
              or (a.LevelTwelveId=b.item and b.linklevel=12))))
    
           end
    
           declare @strEmployees3 xml
           set @strEmployees3=( 
           select Item as EmpID from dbo.Split(@EmpIDs,',')
           FOR XML Path('Employee') ,ROOT('EmployeeArray'));
    
      if exists(select 1 from HR_EMP_MASTER where isnull(IsSave,0)=1)  --If Saved Employee Exist
       begin
    
         IF OBJECT_ID('tempdb..#tempConfirmedEmployee3') IS NOT NULL drop table #tempConfirmedEmployee3
         IF OBJECT_ID('tempdb..#tempSavedEmployee3') IS NOT NULL drop table #tempSavedEmployee3
    
         select a.Emp_Id,i.image_url,a.Emp_Code,
         (case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
         isnull(a.Guardians_Name,'') as Guardians_Name,
         FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,
         FORMAT( c.Wedding_Date, 'dd/MM/yyyy') as WeddingDate,
         FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
         case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,
         FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
         b.PersonalEMail,b.Phone,
          case when Marital_Status='S' then 'Single' 
          when Marital_Status='M' then 'Married' 
          when Marital_Status='W' then 'Widowed' 
          when Marital_Status='X' then 'Separated' 
          when Marital_Status='D' then 'Divorcee' 
          ELSE 'NA' end as Marital_Status,[dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,
          f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
          f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
          rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
          case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
          cm.Country_Name as Nationality,isnull(a.IsSave,0) as IsSave,isnull(a.EmpFileNumber,'') as EmpFileNumber
          into #tempConfirmedEmployee3 from HR_EMP_MASTER a  WITH (NOLOCK)
            join @strEmployees3.nodes('EmployeeArray/Employee') as x(v) on a.Emp_Id=x.v.value('EmpID[1]','int')
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on  r.emp_id=a.Emp_Id  
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            left join HR_EMP_IMAGES i  WITH (NOLOCK)  on i.emp_id=a.Emp_Id
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            inner join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc1 then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc1 then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK)  on cm.Country_ID=c.Nationality
            --where ((a.emp_status in (@Status) or @Status='') and (a.CurrentStatus in (@SystemStatus) or @SystemStatus='') and s.isResignation<>1) and
            where
            (
             (a.Join_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=1)
             or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=2)
             or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=3)     or @FilterType is null     or  @FilterType='' or @FilterType=0
            )
            and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc1 and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
            and (
                a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID not in(select item from dbo.Split(@Status,',')))
                or
                (a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7' or a.emp_status='9' or a.emp_status='10')) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID in(select item from dbo.Split(@Status,',')))            
                )
            and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
            and (isnull(IsDelete,0)=0)OPTION(RECOMPILE)
                    
            select a.Emp_Id,i.image_url,a.Emp_Code,
            (case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
            isnull(a.Guardians_Name,'') as Guardians_Name,
            FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,
            FORMAT( c.Wedding_Date, 'dd/MM/yyyy') as WeddingDate,
            FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
            case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,
            '' as Resignation_Date,'' as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
            case when Marital_Status='S' then 'Single' 
            when Marital_Status='M' then 'Married' 
            when Marital_Status='W' then 'Widowed' 
            when Marital_Status='X' then 'Separated' 
            when Marital_Status='D' then 'Divorcee' 
            ELSE 'NA' end as Marital_Status,[dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,
            isnull(LevelOneDescription,'NA') as LevelOneDescription,isnull(LevelTwoDescription,'NA') as LevelTwoDescription,isnull(LevelThreeDescription,'NA') as LevelThreeDescription,
            isnull(LevelFourDescription,'NA') as LevelFourDescription,isnull(LevelFiveDescription,'NA') as LevelFiveDescription,isnull(LevelSixDescription,'NA') as LevelSixDescription,
            isnull(LevelSevenDescription,'NA') as LevelSevenDescription ,isnull(LevelEightDescription,'NA') as LevelEightDescription,
            isnull(LevelNineDescription,'NA') as LevelNineDescription ,isnull(LevelTenDescription,'NA') as LevelTenDescription,isnull(LevelElevenDescription,'NA') as LevelElevenDescription,
            rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
            case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
            cm.Country_Name as Nationality,isnull(a.IsSave,0) as IsSave,isnull(a.EmpFileNumber,'') as EmpFileNumber ,a.CurrentStatus
            into #tempSavedEmployee3
            from HR_EMP_MASTER a  WITH (NOLOCK)
            left join HR_EMP_IMAGES i  WITH (NOLOCK) on i.emp_id=a.Emp_Id
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on r.emp_id=a.Emp_Id  
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            left join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' --and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc1 then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc1 then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK) on cm.Country_ID=c.Nationality
            where (a.CurrentStatus = @SystemStatus or @SystemStatus='0') and a.Entry_By=@UserID
            
            select * from #tempConfirmedEmployee3 WHERE IsSave=0 
            union
            select * from #tempSavedEmployee3 WHERE IsSave=1
            order by Emp_Code
    
            IF OBJECT_ID('tempdb..#tempConfirmedEmployee3') IS NOT NULL drop table #tempConfirmedEmployee3
            IF OBJECT_ID('tempdb..#tempSavedEmployee3') IS NOT NULL drop table #tempSavedEmployee3
         end
        else --If Saved Employee dont Exist
         BEGIN
          select a.Emp_Id,i.image_url,a.Emp_Code,
          (case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
          isnull(a.Guardians_Name,'') as Guardians_Name,
          FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,
          FORMAT( c.Wedding_Date, 'dd/MM/yyyy') as WeddingDate,
          FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
           case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,
           FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
           b.PersonalEMail,b.Phone,
           case when Marital_Status='S' then 'Single' 
           when Marital_Status='M' then 'Married' 
           when Marital_Status='W' then 'Widowed' 
           when Marital_Status='X' then 'Separated' 
           when Marital_Status='D' then 'Divorcee' 
           ELSE 'NA' end as Marital_Status,[dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,
           f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
           f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
           rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
           case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
           cm.Country_Name as Nationality,isnull(a.IsSave,0) as IsSave,isnull(a.EmpFileNumber,'') as EmpFileNumber,a.CurrentStatus
           from HR_EMP_MASTER a  WITH (NOLOCK)
            join @strEmployees3.nodes('EmployeeArray/Employee') as x(v) on a.Emp_Id=x.v.value('EmpID[1]','int')
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id 
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on r.emp_id=a.Emp_Id  
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            left join HR_EMP_IMAGES i  WITH (NOLOCK) on i.emp_id=a.Emp_Id
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            inner join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc1 then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc1 then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK) on cm.Country_ID=c.Nationality
            --where ((a.emp_status in (@Status) or @Status='') and (a.CurrentStatus in (@SystemStatus) or @SystemStatus='') and s.isResignation<>1) and
            where
            (
             (a.Join_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=1)
             or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=2)
             or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=3)     or @FilterType is null     or  @FilterType='' or @FilterType=0
            )
            and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc1 and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
            and (
                a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID not in(select item from dbo.Split(@Status,',')))
                or
                (a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7'or a.emp_status='9' or a.emp_status='10')) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID in(select item from dbo.Split(@Status,',')))            
                )
            and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
            and (isnull(IsDelete,0)=0)
            order by a.Emp_Code asc
            OPTION(RECOMPILE)
          END
        END        
    end

else if (@InfoFormat=4)  --next
     BEGIN
      declare @cols nvarchar(max)=''
        set @CurrentStatusDesc ='';
        select @CurrentStatusDesc=Status from EmployeeCurrentStatus where Status_Desc='On Notice'
        if exists(SELECT LinkLevel  FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and  s.LinkLevel=15)
         begin

         select @cols = STUFF((SELECT distinct ',' + QUOTENAME(b.DocDescription) from HRMS_Document00 a inner join HRMS_DocumentField00 b on a.DocID=b.DocID inner join HRMS_DocTypeMaster c on c.DocTypeId=a.DocType where a.DocType in(5,3)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

        --select @cols
        ;with [earn] 
AS
(SELECT * from 
            (
       select a.Emp_Id,a.emp_code,a.name as Employee,d.DocDescription,c.DocValues from EmployeeDetails a  join  HRMS_EMPDocumentsApproved00 b on a.Emp_Id=b.EmpID join  HRMS_EMPDocumentsApproved01 c on b.DetailID=c.DetailID  left join HRMS_DocumentField00 d on c.docfields=d.DocFieldID 
inner join HRMS_Document00 f on f.DocID=d.DocID and f.DocType in (5,3)AND B.Status!='D'

      
           )x              
      pivot               
      (                   
      MAX(DocValues)                  
      for DocDescription in ([ACCOUNT NUMBER],[BANK NAME],[IFSC CODE],[IsActive],[NAME],[PAYMENT TYPE],[Adhar Card Number],[PAN Number],[UAN Number],[PF ACCOUNT NUMBER])             
      ) tblEarn 
),
         

           cteEmployeeDetails as (
          select a.Emp_Id,a.Emp_Code,(case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
          isnull(a.Guardians_Name,'') as Guardians_Name,
          FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,
          case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,
          a.SeperationStatus,
          [dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,Is_probation,LastEntity,CurrentStatus,emp_status,a.IsSave,isnull(EmpFileNumber,'') as EmpFileNumber,
          isnull(DailyRateTypeId,0) as DailyRateTypeId,Isnull(PayrollMode,0) as PayrollMode
          from HR_EMP_MASTER a  WITH (NOLOCK)
          left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
              where
            (
             (a.Join_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=1)
             or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=2)
             or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=3)     or @FilterType is null     or  @FilterType='' or @FilterType=0
            )
            and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
            and (
                a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID not in(select item from dbo.Split(@Status,',')))
                or
                (a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7')) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID in(select item from dbo.Split(@Status,',')))            
                )
            and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
            and isnull(IsDelete,0)=0
            and (a.Emp_Id in(select item from dbo.Split(@EmpIDs,',')) or @EmpIDs='0') 
            )

            ,ctePayscale as 
            (
              select EmployeeId,TotalPay from (
              select EmployeeId,TotalPay,
              RANK() OVER(PARTITION BY EmployeeID  ORDER BY EffectiveFrom desc) as Ranks  from Payscale00
              where EffectiveFrom<=GETUTCDATE())a
              where a.Ranks=1
            )

           select a.Emp_Id,i.image_url,a.Emp_Code,a.Name,a.Guardians_Name,a.DateOfBirth,
           JoinDate,DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
           isnull(a.Gender,'NA')as Gender,FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,
           FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
           b.PersonalEMail,b.Phone,
           case when Marital_Status='S' then 'Single' 
           when Marital_Status='M' then 'Married' 
           when Marital_Status='W' then 'Widowed' 
           when Marital_Status='X' then 'Separated' 
           when Marital_Status='D' then 'Divorcee' 
           ELSE 'NA' end as Marital_Status,Age,a.Probation_Dt,
           f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
           f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
           rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
           case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
           cm.Country_Name as Nationality,isnull(a.IsSave,0) as IsSave,a.EmpFileNumber,isnull(relm.Religion_Name,'') as Religion_Name,
           isnull(c.Blood_Grp,'') as Blood_Grp,rmd.Emp_Code as ReportingEmployeeCode,rmd.Name as ReportingEmployeeName,
           case when a.SeperationStatus=0 then 'Live' when a.SeperationStatus>0 then 'Resigned' end as WorkingStatus,
           case when t.RelievingDate is null then '' else FORMAT(t.RelievingDate,'dd-MM-yyyy') end as RelievingDate,
           --'Monthly' as SalaryType,
           --case when a.DailyRateTypeId=0 then 'Monthly'
           --when a.DailyRateTypeId=1 and (a.PayrollMode=1 or a.PayrollMode=0) then 'Monthly'
           --when a.DailyRateTypeId=1 and (a.PayrollMode=2) then 'Daily' end as SalaryType,
           case when  (a.PayrollMode=1 or a.PayrollMode=0) then 'Monthly Wage'
           when (a.PayrollMode=2) then 'Daily Wage' end as SalaryType,
           isnull(cpay.TotalPay,0) as GrossSalary,a.CurrentStatus,
           EA.[IFSC Code] AS IFSC,
            EA.[Bank Name]as Bank,EA.[ACCOUNT NUMBER] AS ACCOUNTNO,HI.Description AS BANKNAME,ms.Notice_period as noticeprriod,b.Add1 as permanantadddress,ad.ContactAddr as presentadress,
            a.Guardians_Name as fathername,b.PBNo as permanantpin,ad.PinNo2 as presentpin,ea.[PAN Number] as pan,ea.[Adhar Card Number] as adhar,ea.[UAN Number] as uannumber,ea.[PF ACCOUNT NUMBER] as pfacccountnumber,''as state,''as city

           from cteEmployeeDetails a  WITH (NOLOCK)
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on r.emp_id=a.Emp_Id 
            left join Employeedetails rmd on rmd.Emp_Id=r.Reprot_to_whome
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            left join HR_EMP_IMAGES i  WITH (NOLOCK) on i.emp_id=a.Emp_Id
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            inner join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status

            left    JOIN earn EA ON EA.Emp_Id=a.Emp_Id
            left join ReasonMaster hI on EA.[BANK NAME]=hI.Reason_Id
            left join HR_EMP_ADDRESS_01 ad on ad.EmpID=a.Emp_Id
            left join HR_EMP_MASTER ms on a.Emp_Id=ms.Emp_Id
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id        
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK) on cm.Country_ID=c.Nationality
            left join ADM_Religion_Master relm  WITH (NOLOCK) on c.Religion=relm.Religion_ID
            left join ctePayscale cpay  WITH (NOLOCK) on cpay.EmployeeId=a.Emp_Id
            order by a.Emp_Code asc
          end
         else
          begin
            if (@EmpIDs='0')--4
              begin
              set @lnklev =0;set @linkselect =0
              
              IF exists(select 1 from Specialaccessrights where roleid=@RoleID)
              begin
              select @linkselect=LinkLevel from Specialaccessrights where roleid=@RoleID
              end
              else
              begin
              select top 1 @linkselect=LinkLevel  from EntityAccessRights02 where roleid=@RoleID order by LinkLevel
              end
              ;with ctnew(item,LinkLevel) as(
              select item,ROW_NUMBER() OVER(ORDER BY (SELECT 1))+1 AS LinklevelSelf from  dbo.SplitStrings_XML((select  EmpEntity from HR_EMP_MASTER where Emp_Id=@EmpID),',' ) f)
              
              ,ApplicableFinalnew (item,LinkLevel)
                              AS
                              (
                                  SELECT f.item as item,LinkLevel as new FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and f.item<>''
                                  union all
                                  select  item,LinkLevel from ctnew  where  @lnklev>0  and ctnew.LinkLevel>=@lnklev and ctnew.item<>'' 
                              )
                          
              select @EmpIDs=COALESCE(@EmpIDs, '') + CONVERT(VARCHAR(MAX),(Emp_Id))+','  from HR_EMP_MASTER d  where 
              exists (SELECT LinkLevel  FROM EntityAccessRights02 AS s CROSS APPLY dbo.SplitStrings_XML(s.LinkId, default) AS f where roleid=@RoleID and  s.LinkLevel=15) or 
               Emp_Id in (            
              (select Emp_Id AS Employee from HR_EMP_MASTER d  WITH (NOLOCK)       
               join HighLevelViewTable a  WITH (NOLOCK) on d.LastEntity=a.LastEntityID and isnull(d.IsDelete,0)=0 and isnull(d.IsSave,0)=0
               inner join  ApplicableFinalnew b  WITH (NOLOCK) on  
              ((a.LevelOneId=b.item and b.linklevel=1)
              or (a.LevelTwoId=b.item and b.linklevel=2)
              or (a.LevelThreeId=b.item and b.linklevel=3)
              or (a.LevelFourId=b.item and b.linklevel=4)
              or (a.LevelFiveId=b.item and b.linklevel=5)
              or (a.LevelSixId=b.item and b.linklevel=6)
              or (a.LevelSevenId=b.item and b.linklevel=7)
              or (a.LevelEightId=b.item and b.linklevel=8)
              or (a.LevelNineId=b.item and b.linklevel=9)
              or (a.LevelTenId=b.item and b.linklevel=10)
              or (a.LevelElevenId=b.item and b.linklevel=11)
              or (a.LevelTwelveId=b.item and b.linklevel=12))))
    
           end
    

           set @strEmployees2=( 
           select Item as EmpID from dbo.Split(@EmpIDs,',')
           FOR XML Path('Employee') ,ROOT('EmployeeArray'));

           ; with ctePayscale as 
            (
              select EmployeeId,TotalPay from (
              select EmployeeId,TotalPay,
              RANK() OVER(PARTITION BY EmployeeID  ORDER BY EffectiveFrom desc) as Ranks  from Payscale00
              where EffectiveFrom<=GETUTCDATE())a
              where a.Ranks=1
            )

            select @cols = STUFF((SELECT distinct ',' + QUOTENAME(b.DocDescription) from HRMS_Document00 a inner join HRMS_DocumentField00 b on a.DocID=b.DocID inner join HRMS_DocTypeMaster c on c.DocTypeId=a.DocType where a.DocType in(5,3)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

        --select @cols
        ;with [earn] 
AS
(SELECT * from 
            (
       select a.Emp_Id,a.emp_code,a.name as Employee,d.DocDescription,c.DocValues from EmployeeDetails a  join  HRMS_EMPDocumentsApproved00 b on a.Emp_Id=b.EmpID join  HRMS_EMPDocumentsApproved01 c on b.DetailID=c.DetailID  left join HRMS_DocumentField00 d on c.docfields=d.DocFieldID 
inner join HRMS_Document00 f on f.DocID=d.DocID and f.DocType in (5,3)

      
           )x              
      pivot               
      (                   
      MAX(DocValues)                  
      for DocDescription in ([ACCOUNT NUMBER],[BANK NAME],[IFSC CODE],[IsActive],[NAME],[PAYMENT TYPE],[Adhar Card Number],[PAN Number],[UAN Number],[PF ACCOUNT NUMBER])             
      ) tblEarn 
)

--select * from earn
           select a.Emp_Id,i.image_url,a.Emp_Code,
          (case when a.Middle_Name is null and a.Last_Name is null then a.First_Name  when a.Middle_Name is null then a.First_Name+' '+a.Last_Name  when a.Last_Name is null then a.First_Name+' '+a.Middle_Name   else a.First_Name+' '+a.Middle_Name+' '+a.Last_Name    end) as Name,
           isnull(a.Guardians_Name,'') as Guardians_Name, 
           FORMAT( a.DateOfBirth, 'dd/MM/yyyy') as DateOfBirth, FORMAT( a.Join_Dt, 'dd/MM/yyyy') as JoinDate,FORMAT( a.Join_Dt, 'yyyyMMdd') as DataDate,j.Status_Desc,s.Status_Desc as EmpStatus,
           case when a.Gender is null then 'NA' when a.Gender='M' then 'Male' when a.Gender='F' then 'Female'  when a.Gender='O' then 'Other' else a.Gender end  as Gender,       
           FORMAT(t.Resignation_Date,'dd/MM/yyyy')as Resignation_Date,FORMAT(t.RelievingDate,'dd/MM/yyyy')as RelievingDate,a.SeperationStatus,isnull(EMail,'NA') as OfficialEmail,
           b.PersonalEMail,b.Phone,
           case when Marital_Status='S' then 'Single' 
           when Marital_Status='M' then 'Married' 
           when Marital_Status='W' then 'Widowed' 
           when Marital_Status='X' then 'Separated' 
           when Marital_Status='D' then 'Divorcee' 
           ELSE 'NA' end as Marital_Status,[dbo].[GetEmployeeAge](a.DateOfBirth) as Age,FORMAT( a.Probation_Dt, 'dd/MM/yyyy') as Probation_Dt,
           f.LevelOneDescription,f.LevelTwoDescription,f.LevelThreeDescription,f.LevelFourDescription,f.LevelFiveDescription,f.LevelSixDescription,
           f.LevelSevenDescription ,f.LevelEightDescription,f.LevelNineDescription ,f.LevelTenDescription,f.LevelElevenDescription,
           rm.Description,(select Status_Desc from HR_EMP_STATUS_SETTINGS where StatusID=t.Relieving_Type) as ResignationType,
           case when a.Is_probation=0 then 'CONFIRMED' when a.Is_probation=1 then 'PROBATION' end as ProbationStatus,
           cm.Country_Name as Nationality,isnull(a.IsSave,0) as IsSave,isnull(a.EmpFileNumber,'') as EmpFileNumber,isnull(relm.Religion_Name,'') as Religion_Name,
           isnull(c.Blood_Grp,'') as Blood_Grp,rmd.Emp_Code as ReportingEmployeeCode,rmd.Name as ReportingEmployeeName,
           case when a.SeperationStatus=0 then 'Live' when a.SeperationStatus>0 then 'Resigned' end as WorkingStatus,
           case when t.RelievingDate is null then '' else FORMAT(t.RelievingDate,'dd-MM-yyyy') end as RelievingDate,
           case when  (isnull(a.PayrollMode,0)=1 or isnull(a.PayrollMode,0)=0) then 'Monthly Wage'
           when (isnull(a.PayrollMode,0)=2) then 'Daily Wage' end as SalaryType,
           EA.[IFSC Code] AS IFSC,
            EA.[Bank Name]as Bank,EA.[ACCOUNT NUMBER] AS ACCOUNTNO,HI.Description AS BANKNAME,a.Notice_period as noticeprriod,b.Add1 as permanantadddress,ad.ContactAddr as presentadress,
            a.Guardians_Name as fathername,b.PBNo as permanantpin,ad.PinNo2 as presentpin,ea.[PAN Number] as pan,ea.[Adhar Card Number] as adhar,ea.[UAN Number] as uannumber,ea.[PF ACCOUNT NUMBER] as pfacccountnumber,''as state,''as city,
           --isnull(cpay.TotalPay,0) as GrossSalary,
           a.CurrentStatus
           from HR_EMP_MASTER a  WITH (NOLOCK)
            join @strEmployees2.nodes('EmployeeArray/Employee') as x(v) on a.Emp_Id=x.v.value('EmpID[1]','int')
            left join HR_EMP_ADDRESS b  WITH (NOLOCK) on a.Emp_Id=b.Emp_Id
            left join  HR_EMP_PERSONAL c  WITH (NOLOCK) on c.Emp_Id=a.Emp_Id 
            left join HR_EMP_REPORTING r  WITH (NOLOCK) on r.emp_id=a.Emp_Id  
            left join Employeedetails rmd  WITH (NOLOCK) on rmd.Emp_Id=r.Reprot_to_whome
            left join HighLevelViewTable f  WITH (NOLOCK) on a.LastEntity=f.LastEntityID
            left join HR_EMP_IMAGES i  WITH (NOLOCK) on i.emp_id=a.Emp_Id
            inner join EmployeeCurrentStatus j  WITH (NOLOCK) on a.CurrentStatus=j.Status
            inner join HR_EMP_STATUS_SETTINGS s  WITH (NOLOCK) on s.StatusID=a.emp_status
            left join Resignation t  WITH (NOLOCK) on t.Emp_Id=a.Emp_Id and t.CurrentRequest=1 and t.ApprovalStatus<>'D' and t.ApprovalStatus<>'R' and (t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'P' else 'A' end) or t.ApprovalStatus=(CASE when @SystemStatus=@CurrentStatusDesc then 'A' else 'A' end)) and t.Rejoin_Status='P'
            --left join HR_EMP_MASTER z on a.Emp_Id=z.Emp_Id
        left JOIN earn EA ON EA.Emp_Id=A.Emp_Id
            left join ReasonMaster hI on EA.[BANK NAME]=hI.Reason_Id
            left join HR_EMP_ADDRESS_01 ad on ad.EmpID=a.Emp_Id
                
            left join ReasonMaster rm  WITH (NOLOCK) on t.Reason=rm.Reason_Id and rm.Type='Reason'
            left join ADM_Country_Master cm  WITH (NOLOCK) on cm.Country_ID=c.Nationality
            left join ADM_Religion_Master relm  WITH (NOLOCK) on c.Religion=relm.Religion_ID
            --left join ctePayscale cpay  WITH (NOLOCK) on cpay.EmployeeId=a.Emp_Id
            --where ((a.emp_status in (@Status) or @Status='') and (a.CurrentStatus in (@SystemStatus) or @SystemStatus='') and s.isResignation<>1) and
            where
            (
             (a.Join_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=1)
             or(a.Probation_Dt between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=2)
             or(a.RelievingDate between @DurationFrom and @DurationTo and (select item from  dbo.Split(@FilterType,','))=3)     or @FilterType is null     or  @FilterType='' or @FilterType=0
            )
            and (a.CurrentStatus = @SystemStatus or @SystemStatus='0' or (@SystemStatus=@CurrentStatusDesc and t.Resignation_Id is not null and t.RelievingDate>=GETUTCDATE()))
            and (
                a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation!=1)) and ISNULL(a.SeperationStatus,0) not in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID not in(select item from dbo.Split(@Status,',')))
                or
                (a.emp_status in(select item from dbo.Split(@Status,',') where Item in(select StatusId from HR_EMP_STATUS_SETTINGS where isResignation!=1)) or (a.emp_status='1' or a.emp_status='2' or a.emp_status='3' or a.emp_status='7' )) and exists(select 1 from dbo.Split(@Status,',') where item in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1)) and ISNULL(a.SeperationStatus,0) in(select StatusID from HR_EMP_STATUS_SETTINGS where isResignation=1 and StatusID in(select item from dbo.Split(@Status,',')))            
                )
            and ((@ProbationStatus=2 and ISNULL(a.Is_Probation,0)=1)or(@ProbationStatus=3 and ISNULL(a.Is_Probation,0)=0) or (@ProbationStatus=1 and (ISNULL(a.Is_Probation,0)=1 or ISNULL(a.Is_Probation,0)=0)))
            and (isnull(a.IsDelete,0)=0)
            order by a.Emp_Code asc
            OPTION(RECOMPILE)
        END
     END

  END
END
 

Can you please share the error you are getting ?

Dear Amira,

Still getting the same result.

Your current LINQ logic in GetFilteredEmployeesAsync is mixing conditions in a way that may not fully replicate the SQL two-branch structure.

public async Task> GetFilteredEmployeesAsync(HashSet statusSet)
{
    var nonResignationStatuses = await GetStatusSetAsync(false); // isResignation = false
    var resignationStatuses = await GetStatusSetAsync(true);     // isResignation = true
    var excludedSeparationStatuses = await GetStatusSetAsync(true, statusSet); // isResignation = true and in statusSet

    var employees = await _context.HrEmpMasters
        .Where(emp =>
            // First condition (non-resignation + not in resignation separation)
            (
                emp.EmpStatus.HasValue &&
                nonResignationStatuses.Contains(emp.EmpStatus.Value) &&
                !excludedSeparationStatuses.Contains(emp.SeperationStatus.GetValueOrDefault())
            )
            ||
            // Second condition (non-resignation OR 1,2,3,7) AND separation in resignation statuses
            (
                emp.EmpStatus.HasValue &&
                (nonResignationStatuses.Contains(emp.EmpStatus.Value) || new[] { 1, 2, 3, 7 }.Contains(emp.EmpStatus.Value)) &&
                resignationStatuses.Intersect(statusSet).Any() &&
                excludedSeparationStatuses.Contains(emp.SeperationStatus.GetValueOrDefault())
            )
        )
        .Select(emp => emp.EmpStatus)
        .Distinct()
        .ToListAsync();

    return employees;
}