ALTER VIEW [dbo].[vApprovalAuthority]
AS
SELECT DISTINCT iHdTicketId AS liApprovalAuthorityHdTicketId, liPersonManagerPersonId AS liApprovalAuthorityPersonId
FROM            (SELECT        dbo.tHdTicket.iHdTicketId, dbo.tPerson.liPersonManagerPersonId
                          FROM            dbo.tHdTicket INNER JOIN
                                                    dbo.tHdTicketApprovalItem ON dbo.tHdTicketApprovalItem.iHdTicketApprovalItemId = dbo.tHdTicket.liHdTicketHdTicketApprovalItemId INNER JOIN
                                                    dbo.tApprovalSchemaItem ON dbo.tApprovalSchemaItem.iApprovalSchemaItemId = dbo.tHdTicketApprovalItem.liHdTicketApprovalItemApprovalSchemaItemId INNER JOIN
                                                    dbo.tPerson ON dbo.tHdTicket.liHdTicketUserPersonId = dbo.tPerson.iPersonId
                          WHERE        (dbo.tApprovalSchemaItem.bApprovalSchemaItemAuthorityManagerOfUser = 1)
                          UNION
                          SELECT        dbo.tHdTicket.iHdTicketId, N.liPersonDelegatePersonId
                          FROM            dbo.tHdTicket INNER JOIN
                                                   dbo.tHdTicketApprovalItem ON dbo.tHdTicketApprovalItem.iHdTicketApprovalItemId = dbo.tHdTicket.liHdTicketHdTicketApprovalItemId INNER JOIN
                                                   dbo.tApprovalSchemaItem ON dbo.tApprovalSchemaItem.iApprovalSchemaItemId = dbo.tHdTicketApprovalItem.liHdTicketApprovalItemApprovalSchemaItemId INNER JOIN
                                                   dbo.tPerson AS U ON U.iPersonId = dbo.tHdTicket.liHdTicketUserPersonId INNER JOIN
                                                   dbo.tPerson AS N ON N.iPersonId = U.liPersonManagerPersonId AND N.IsOutOfOffice = 1
                          WHERE        (dbo.tApprovalSchemaItem.bApprovalSchemaItemAuthorityManagerOfUser = 1) AND (N.liPersonDelegatePersonId IS NOT NULL)
						  UNION
                          SELECT        dbo.tHdTicket.iHdTicketId, dbo.tRolePerson.liRolePersonPersonId
                          FROM            dbo.tHdTicket INNER JOIN
                                                   dbo.tHdTicketApprovalItem ON dbo.tHdTicketApprovalItem.iHdTicketApprovalItemId = dbo.tHdTicket.liHdTicketHdTicketApprovalItemId INNER JOIN
                                                   dbo.tApprovalSchemaItem ON dbo.tApprovalSchemaItem.iApprovalSchemaItemId = dbo.tHdTicketApprovalItem.liHdTicketApprovalItemApprovalSchemaItemId INNER JOIN
                                                   dbo.tRolePerson ON dbo.tRolePerson.liRolePersonRoleId = dbo.tApprovalSchemaItem.liApprovalSchemaItemAuthorityRoleId
						  WHERE			dbo.tApprovalSchemaItem.OnlyRequesterOrHisManager = 0
                          UNION
                          SELECT        dbo.tHdTicket.iHdTicketId, dbo.tPerson.liPersonDelegatePersonId
                          FROM            dbo.tHdTicket INNER JOIN
                                                   dbo.tHdTicketApprovalItem ON dbo.tHdTicketApprovalItem.iHdTicketApprovalItemId = dbo.tHdTicket.liHdTicketHdTicketApprovalItemId INNER JOIN
                                                   dbo.tApprovalSchemaItem ON dbo.tApprovalSchemaItem.iApprovalSchemaItemId = dbo.tHdTicketApprovalItem.liHdTicketApprovalItemApprovalSchemaItemId INNER JOIN
                                                   dbo.tRolePerson ON dbo.tRolePerson.liRolePersonRoleId = dbo.tApprovalSchemaItem.liApprovalSchemaItemAuthorityRoleId INNER JOIN
                                                   dbo.tPerson ON dbo.tPerson.iPersonId = dbo.tRolePerson.liRolePersonPersonId AND dbo.tPerson.IsOutOfOffice = 1
                          WHERE        (dbo.tPerson.liPersonDelegatePersonId IS NOT NULL) AND (dbo.tApprovalSchemaItem.OnlyRequesterOrHisManager = 0)
						  UNION
                          SELECT		T.iHdTicketId, PM.ManagerPersonId
						  FROM			dbo.tHdTicket AS T
						  JOIN			dbo.tHdTicketApprovalItem AS AI ON AI.iHdTicketApprovalItemId = T.liHdTicketHdTicketApprovalItemId
						  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		T.iHdTicketId, TP.liPersonDelegatePersonId
						  FROM			dbo.tHdTicket AS T
						  JOIN			dbo.tHdTicketApprovalItem AS AI ON AI.iHdTicketApprovalItemId = T.liHdTicketHdTicketApprovalItemId
						  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        dbo.tHdTicket.iHdTicketId, RM.iPersonId
                          FROM            dbo.tHdTicket INNER JOIN
                                                   dbo.tHdTicketApprovalItem ON dbo.tHdTicketApprovalItem.iHdTicketApprovalItemId = dbo.tHdTicket.liHdTicketHdTicketApprovalItemId INNER JOIN
                                                   dbo.tApprovalSchemaItem ON dbo.tApprovalSchemaItem.iApprovalSchemaItemId = dbo.tHdTicketApprovalItem.liHdTicketApprovalItemApprovalSchemaItemId INNER JOIN
                                                   dbo.tPerson AS U ON dbo.tHdTicket.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        (dbo.tApprovalSchemaItem.bApprovalSchemaItemAuthorityRootManagerOfUser = 1)
                          UNION
                          SELECT        dbo.tHdTicket.iHdTicketId, RM.liPersonDelegatePersonId
                          FROM            dbo.tHdTicket INNER JOIN
                                                   dbo.tHdTicketApprovalItem ON dbo.tHdTicketApprovalItem.iHdTicketApprovalItemId = dbo.tHdTicket.liHdTicketHdTicketApprovalItemId INNER JOIN
                                                   dbo.tApprovalSchemaItem ON dbo.tApprovalSchemaItem.iApprovalSchemaItemId = dbo.tHdTicketApprovalItem.liHdTicketApprovalItemApprovalSchemaItemId INNER JOIN
                                                   dbo.tPerson AS U ON dbo.tHdTicket.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        (dbo.tApprovalSchemaItem.bApprovalSchemaItemAuthorityRootManagerOfUser = 1) AND (RM.liPersonDelegatePersonId IS NOT NULL)
                          UNION
                          SELECT        dbo.tHdTicket.iHdTicketId, dbo.tHdTicketApprovalItem.liHdTicketApprovalItemPersonId
                          FROM            dbo.tHdTicket INNER JOIN
                                                   dbo.tHdTicketApprovalItem ON dbo.tHdTicketApprovalItem.iHdTicketApprovalItemId = dbo.tHdTicket.liHdTicketHdTicketApprovalItemId
                          WHERE        (dbo.tHdTicketApprovalItem.liHdTicketApprovalItemPersonId IS NOT NULL)
                          UNION
                          SELECT        dbo.tHdTicket.iHdTicketId, dbo.tPerson.liPersonDelegatePersonId
                          FROM            dbo.tHdTicket INNER JOIN
                                                   dbo.tHdTicketApprovalItem ON dbo.tHdTicketApprovalItem.iHdTicketApprovalItemId = dbo.tHdTicket.liHdTicketHdTicketApprovalItemId INNER JOIN
                                                   dbo.tPerson ON dbo.tPerson.iPersonId = dbo.tHdTicketApprovalItem.liHdTicketApprovalItemPersonId AND dbo.tPerson.IsOutOfOffice = 1
                          WHERE        (dbo.tPerson.liPersonDelegatePersonId IS NOT NULL)
                          UNION
                          SELECT        dbo.tHdTicket.iHdTicketId, dbo.tHdTicket.liHdTicketUserPersonId
                          FROM            dbo.tHdTicket INNER JOIN
                                                   dbo.tHdTicketApprovalItem ON dbo.tHdTicketApprovalItem.iHdTicketApprovalItemId = dbo.tHdTicket.liHdTicketHdTicketApprovalItemId INNER JOIN
                                                   dbo.tApprovalSchemaItem ON dbo.tApprovalSchemaItem.iApprovalSchemaItemId = dbo.tHdTicketApprovalItem.liHdTicketApprovalItemApprovalSchemaItemId
                          WHERE        (dbo.tApprovalSchemaItem.AuthorityRequester = 1)
                          UNION
						     SELECT        liHdTicketId, liPersonId
                          FROM            dbo.vApprovalAuthority_Custom
                          UNION
                          SELECT        dbo.vApprovalAuthority_Custom.liHdTicketId, dbo.tPerson.liPersonDelegatePersonId
                          FROM            dbo.vApprovalAuthority_Custom INNER JOIN
                                                   dbo.tPerson ON dbo.tPerson.iPersonId = dbo.vApprovalAuthority_Custom.liPersonId
                          WHERE        (dbo.tPerson.IsOutOfOffice = 1) AND (dbo.tPerson.liPersonDelegatePersonId IS NOT NULL)) AS A
WHERE        (liPersonManagerPersonId IS NOT NULL)
GO