Namespace for Authorisations

[David Abbishaw]

Namespace for Authorisations

Hi,

Is there a namespace that holds who (preferably the email address) approved the requisition?

Im hoping that we can do something like - #Service.Requisition.Approver.Email#

thanks

David

[Emir E]

if you send the email before the next tasks starts, I believe you can use Performer.*

[ChrisMMorris]

Correct, it's only available before the next task runs.  After the following task is started you can't access the previous performers.

We had to use a complex SQL query to get that information after all tasks have run.  This was done in a task that used the SQL Adapater and placed the information that was found back in the request so it could be emailed in the following task.

[alangonzalez]

Any chance you can share that complex SQL query?

[mike_jones]

I've used the following to pull in contact information for approvals / tasks after the fact. You need to replace [RequisitionID] and [TaskName] with the appropriate values for your request. If you only want e-mail address, you can pull out the DirPerson references and the join.

SELECT DirPerson.FirstName, DirPerson.LastName, DirContactInfo.Value AS Email FROM DirContactInfo

JOIN DirPerson ON DirPerson.PersonID = DirContactInfo.PersonID

WHERE DirContactInfo.PersonID =

(

SELECT PerformerID FROM RequestCenter.dbo.TxActivity WHERE TicketID =

(

SELECT RequisitionEntryID FROM RequestCenter.dbo.TxRequisitionEntry

WHERE RequisitionID = [RequisitionID]

)

AND TitleExpression = [TaskName]

)

AND ContactTypeID = 1

[ChrisMMorris]

This is the query that we used:

select distinct a.RequisitionID as reqId, c.Subject as TaskName,c.CompletedOn,b.RequisitionEntryID as arrEntryId, d.Name as ServiceName, u.Loginname as 'ApproverId', t.FirstName + ' ' + t.LastName as 'ApproverName', r.Description as 'StateDescription', c.ActivityID, f.ChannelID from [RequestCenterDev2].[dbo].[TxRequisition] a join [RequestCenterDev2].[dbo].[TxRequisitionEntry] b on a.RequisitionID=b.RequisitionID join [RequestCenterDev2].[dbo].[TxActivity] c on c.ticketID= b.RequisitionEntryID join [RequestCenterDev2].[dbo].[DefService] d on d.ServiceId=b.ServiceId join [RequestCenterDev2].[dbo].[DirPersonExt] e on a.OwnerID=e.personID right join [RequestCenterDev2].[dbo].[XtrChannelInfo] f on a.RequisitionID = f.RequisitionID join [RequestCenterDev2].[dbo].[XtrChannelInfo] g on f.ChannelID = g.ChannelID join [RequestCenterDev2].[dbo].[TxActivity] h on g.ActivityID = h.ActivityID join [RequestCenterDev2].[dbo].[DefFSMState] r on c.StateID = r.StateID join [RequestCenterDev2].[dbo].[TxActivityAssignment] s on s.ActivityID = c.ActivityID join [RequestCenterDev2].[dbo].[DirPerson] t on t.PersonID = s.PerformerID join [RequestCenterDev2].[dbo].[DirNetworkInfo] u on u.PersonID = t.PersonID WHERE c.Subject IN ('Approver 1', 'Approver 2', 'Get Approvers') AND f.AgentID = 7 AND h.StateID != 3 ORDER BY reqId, ActivityID