ALTER function [dbo].[ftApprovers] 
(
	@approvalItemId int
)
returns @approvers TABLE 
(
	iPersonId int,
	sPerson nvarchar(1024),
	sPersonEmail nvarchar(1024)
)
as begin
	insert into @approvers
	SELECT        
		p.iPersonId, p.sPerson, p.sPersonEmail
	FROM
	(
		SELECT        
			ai.iHdTicketApprovalItemId, p.liPersonManagerPersonId
		FROM
			dbo.tHdTicketApprovalItem ai
			INNER JOIN dbo.tHdTicketApproval a on a.iHdTicketApprovalId = ai.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket t on t.iHdTicketId = a.liHdTicketApprovalHdTicketId
			INNER JOIN dbo.tApprovalSchemaItem asi ON asi.iApprovalSchemaItemId = ai.liHdTicketApprovalItemApprovalSchemaItemId 
			INNER JOIN dbo.tPerson p ON t.liHdTicketUserPersonId = p.iPersonId
		WHERE        
			asi.bApprovalSchemaItemAuthorityManagerOfUser = 1
		UNION
		SELECT
			ai.iHdTicketApprovalItemId, n.liPersonDelegatePersonId
		FROM
			dbo.tHdTicketApprovalItem ai 
			INNER JOIN dbo.tHdTicketApproval a on a.iHdTicketApprovalId = ai.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket t on t.iHdTicketId = a.liHdTicketApprovalHdTicketId
			INNER JOIN dbo.tApprovalSchemaItem asi ON asi.iApprovalSchemaItemId = ai.liHdTicketApprovalItemApprovalSchemaItemId 
			INNER JOIN dbo.tPerson p ON t.liHdTicketUserPersonId = p.iPersonId
			INNER JOIN dbo.tPerson AS n ON n.iPersonId = p.liPersonManagerPersonId AND n.IsOutOfOffice = 1
		WHERE
			asi.bApprovalSchemaItemAuthorityManagerOfUser = 1 AND n.liPersonDelegatePersonId IS NOT NULL

		UNION

		SELECT
			ai.iHdTicketApprovalItemId, rp.liRolePersonPersonId
		FROM 
			dbo.tHdTicketApprovalItem ai 
			INNER JOIN dbo.tHdTicketApproval a on a.iHdTicketApprovalId = ai.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket t on t.iHdTicketId = a.liHdTicketApprovalHdTicketId
			INNER JOIN dbo.tApprovalSchemaItem asi ON asi.iApprovalSchemaItemId = ai.liHdTicketApprovalItemApprovalSchemaItemId 
			INNER JOIN dbo.tRolePerson rp ON rp.liRolePersonRoleId = asi.liApprovalSchemaItemAuthorityRoleId
		WHERE
			asi.OnlyRequesterOrHisManager = 0
		UNION
		SELECT        
			ai.iHdTicketApprovalItemId, p.liPersonDelegatePersonId
		FROM
			dbo.tHdTicketApprovalItem ai 
			INNER JOIN dbo.tHdTicketApproval a on a.iHdTicketApprovalId = ai.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket t on t.iHdTicketId = a.liHdTicketApprovalHdTicketId
			INNER JOIN dbo.tApprovalSchemaItem asi ON asi.iApprovalSchemaItemId = ai.liHdTicketApprovalItemApprovalSchemaItemId 
			INNER JOIN dbo.tRolePerson rp ON rp.liRolePersonRoleId = asi.liApprovalSchemaItemAuthorityRoleId
			inner join dbo.tPerson p ON p.iPersonId = rp.liRolePersonPersonId AND p.IsOutOfOffice = 1
		WHERE
			p.liPersonDelegatePersonId IS NOT NULL AND asi.OnlyRequesterOrHisManager = 0
						  
		UNION

		SELECT
			AI.iHdTicketApprovalItemId, PM.ManagerPersonId
		FROM			
			dbo.tHdTicketApprovalItem AI 
			INNER JOIN dbo.tHdTicketApproval A on A.iHdTicketApprovalId = AI.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket T on T.iHdTicketId = A.liHdTicketApprovalHdTicketId
			JOIN	   dbo.tApprovalSchemaItem AS ASI ON ASI.iApprovalSchemaItemId = AI.liHdTicketApprovalItemApprovalSchemaItemId
			CROSS APPLY
					(SELECT TOP 1 PM2.ManagerPersonId FROM
						(SELECT * FROM dbo.PersonManager 
						UNION 
						SELECT DISTINCT requester.iPersonId, requester.iPersonId, 0 FROM dbo.tPerson requester
						) AS PM2
					JOIN tRolePerson AS RP ON PM2.ManagerPersonId = RP.liRolePersonPersonId
					WHERE PM2.EmployeePersonId = T.liHdTicketUserPersonId and RP.liRolePersonRoleId = ASI.liApprovalSchemaItemAuthorityRoleId
					ORDER BY Distance ASC) AS PM
		WHERE 
			ASI.OnlyRequesterOrHisManager = 1
		UNION
		SELECT		
			AI.iHdTicketApprovalItemId, TP.liPersonDelegatePersonId
		FROM			
			dbo.tHdTicketApprovalItem AI 
			INNER JOIN dbo.tHdTicketApproval A on A.iHdTicketApprovalId = AI.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket T on T.iHdTicketId = A.liHdTicketApprovalHdTicketId
			JOIN			dbo.tApprovalSchemaItem AS ASI ON ASI.iApprovalSchemaItemId = AI.liHdTicketApprovalItemApprovalSchemaItemId
			CROSS APPLY
						(SELECT TOP 1 PM2.ManagerPersonId FROM
							(SELECT * FROM dbo.PersonManager 
							UNION 
							SELECT DISTINCT requester.iPersonId, requester.iPersonId, 0 FROM dbo.tPerson requester
							) AS PM2
						JOIN dbo.tRolePerson AS RP ON PM2.ManagerPersonId = RP.liRolePersonPersonId
						WHERE PM2.EmployeePersonId = T.liHdTicketUserPersonId and RP.liRolePersonRoleId = ASI.liApprovalSchemaItemAuthorityRoleId
						ORDER BY PM2.Distance ASC) AS PM
			JOIN			dbo.tPerson as TP on TP.iPersonId = PM.ManagerPersonId AND TP.IsOutOfOffice = 1
			WHERE (ASI.OnlyRequesterOrHisManager = 1) AND (TP.liPersonDelegatePersonId IS NOT NULL)

		UNION

		SELECT
			AI.iHdTicketApprovalItemId, RM.iPersonId
		FROM
			dbo.tHdTicketApprovalItem AI 
			INNER JOIN dbo.tHdTicketApproval A on A.iHdTicketApprovalId = AI.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket T on T.iHdTicketId = A.liHdTicketApprovalHdTicketId
			INNER JOIN dbo.tApprovalSchemaItem ASI ON ASI.iApprovalSchemaItemId = AI.liHdTicketApprovalItemApprovalSchemaItemId
			INNER JOIN dbo.tPerson AS U ON T.liHdTicketUserPersonId = U.iPersonId
			INNER JOIN dbo.PersonManager ON dbo.PersonManager.EmployeePersonId = U.iPersonId
			INNER JOIN dbo.tPerson AS RM ON RM.iPersonId = dbo.PersonManager.ManagerPersonId AND RM.liPersonManagerPersonId IS NULL
		WHERE
			ASI.bApprovalSchemaItemAuthorityRootManagerOfUser = 1
		UNION
		SELECT
			AI.iHdTicketApprovalItemId, RM.liPersonDelegatePersonId
		FROM            
			dbo.tHdTicketApprovalItem AI 
			INNER JOIN dbo.tHdTicketApproval A on A.iHdTicketApprovalId = AI.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket T on T.iHdTicketId = A.liHdTicketApprovalHdTicketId
			INNER JOIN dbo.tApprovalSchemaItem ASI ON ASI.iApprovalSchemaItemId = AI.liHdTicketApprovalItemApprovalSchemaItemId
			INNER JOIN dbo.tPerson AS U ON T.liHdTicketUserPersonId = U.iPersonId
			INNER JOIN dbo.PersonManager ON dbo.PersonManager.EmployeePersonId = U.iPersonId
			INNER JOIN dbo.tPerson AS RM ON RM.iPersonId = dbo.PersonManager.ManagerPersonId AND RM.liPersonManagerPersonId IS NULL AND RM.IsOutOfOffice = 1
		WHERE        
			ASI.bApprovalSchemaItemAuthorityRootManagerOfUser = 1 AND RM.liPersonDelegatePersonId IS NOT NULL

		UNION

		SELECT        
			ai.iHdTicketApprovalItemId, ai.liHdTicketApprovalItemPersonId
		FROM            
			dbo.tHdTicketApprovalItem ai 
		WHERE
			ai.liHdTicketApprovalItemPersonId IS NOT NULL
		UNION
		SELECT
			ai.iHdTicketApprovalItemId, p.liPersonDelegatePersonId
		FROM            
			dbo.tHdTicketApprovalItem ai 
			inner join dbo.tPerson p ON p.iPersonId = ai.liHdTicketApprovalItemPersonId AND p.IsOutOfOffice = 1
		WHERE
			p.liPersonDelegatePersonId IS NOT NULL

		UNION
                          
		SELECT
			ai.iHdTicketApprovalItemId, t.liHdTicketUserPersonId
		FROM            
			dbo.tHdTicketApprovalItem ai 
			INNER JOIN dbo.tHdTicketApproval a on a.iHdTicketApprovalId = ai.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket t on t.iHdTicketId = a.liHdTicketApprovalHdTicketId
			INNER JOIN dbo.tApprovalSchemaItem asi ON asi.iApprovalSchemaItemId = ai.liHdTicketApprovalItemApprovalSchemaItemId
		WHERE
			asi.AuthorityRequester = 1

		UNION
						     
		SELECT  
			ai.iHdTicketApprovalItemId, aac.liPersonId
		FROM        
			dbo.tHdTicketApprovalItem ai 
			INNER JOIN dbo.tHdTicketApproval a on a.iHdTicketApprovalId = ai.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket t on t.iHdTicketId = a.liHdTicketApprovalHdTicketId    
			INNER JOIN dbo.vApprovalAuthority_Custom aac on aac.liHdTicketId = t.iHdTicketId
		UNION
		SELECT        
			ai.iHdTicketApprovalItemId, p.liPersonDelegatePersonId
		FROM            
			dbo.tHdTicketApprovalItem ai 
			INNER JOIN dbo.tHdTicketApproval a on a.iHdTicketApprovalId = ai.liHdTicketApprovalItemHdTicketApprovalId
			INNER JOIN dbo.tHdTicket t on t.iHdTicketId = a.liHdTicketApprovalHdTicketId    
			INNER JOIN dbo.vApprovalAuthority_Custom aac on aac.liHdTicketId = t.iHdTicketId
			INNER JOIN dbo.tPerson p ON p.iPersonId = aac.liPersonId
		WHERE        
			p.IsOutOfOffice = 1 AND p.liPersonDelegatePersonId IS NOT NULL
	) AS a
	INNER JOIN tPerson p on p.iPersonId = a.liPersonManagerPersonId
	WHERE
		a.liPersonManagerPersonId IS NOT NULL and a.iHdTicketApprovalItemId = @approvalItemId
		and p.bHidden = 0 and p.dPersonRemoved is null and p.bPersonAccountDisabled = 0

	RETURN
end

GO