My Work Items

Discussions related to Crystal Reports development.

Moderator: Lisa Ennis

Post Reply
cgriffin
Posts: 22
Joined: Mon Nov 17, 2014 6:14 pm

My Work Items

Post by cgriffin »

How do I retrieve from the database - the view of My Work Items? Specifically the information in the tabs under my work items. For the queues, and then My Orders/Tasks.

Thank you,
Colin.
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: My Work Items

Post by Lisa Ennis »

The stored procedures listed below are used for the reports related to order tasks and workflow. These may be a good starting point for you to write your own queries. Please let me know if you have additional questions.

rptdoc.OpenOrderTasksWithDateParameters
rptdoc.OpenOrderTasksByEscrowOfficerCloser
rptdoc.OpenOrderTasksByTitleOfficerExaminer
rptdoc.WorkflowProductivityWithDateParameters
rptdoc.WorkflowStepsInProgress
Lisa Ennis
Senior Report Developer
SoftPro
cgriffin
Posts: 22
Joined: Mon Nov 17, 2014 6:14 pm

Re: My Work Items

Post by cgriffin »

Thank you for this information. Unfortunately, it only provides what is assigned. I really need to find orders that are in queues, and have yet to be assigned to any particular user. How do I go about retrieving that information?
Lisa Ennis
Posts: 84
Joined: Thu Sep 11, 2008 1:57 pm

Re: My Work Items

Post by Lisa Ennis »

I started with the WorkflowStepsInProgress stored procedure and modified it to look for AssignedTrusteeID IS NULL. This is where I would start. It would be difficult for me to be more specific as you would need to test accordingly as you are writing your queries to determine you are getting the data that you want.

Code: Select all

DECLARE @CLIENTTIMEZONE VARCHAR(50)
SET @CLIENTTIMEZONE = 'Eastern Standard Time'

 SELECT
	O.RootId#, 
	O.Number AS OrderNumber, 
	O.ReceivedDate AS OrderReceivedDateUTC,
	dbo.ConvertTimeFromUtc(O.ReceivedDate, @CLIENTTIMEZONE) AS OrderReceivedDate,
	O.SettlementDate AS OrderSettlementDateUTC,
	dbo.ConvertTimeFromUtc(O.SettlementDate, @CLIENTTIMEZONE) AS OrderSettlementDate,
	E.Guid# AS OrderGuid,
	O.IsRush, 
	O.IsTemplate,
	WP.Name AS WorkflowProcess, 
	WS.Name AS WorkflowStep, 
	dbo.ConvertTimeFromUtc(WH.StepAssignDateTime, @CLIENTTIMEZONE) AS StepAssignDateTime, 
	WH.StepAssignDateTime AS StepAssignDateTimeUTC, 
	dbo.ConvertTimeFromUtc(WH.UserAssignDateTime, @CLIENTTIMEZONE) AS UserAssignDateTime, 
	WH.UserAssignDateTime AS UserAssignDateTimeUTC, 
	dbo.ConvertTimeFromUtc(WH.WorkflowStatusDateTime, @CLIENTTIMEZONE) AS WorkflowStatusDateTime,
	WH.WorkflowStatusDateTime AS WorkflowStatusDateTimeUTC,
	SI.Name AS UserName,
	SI.FullName,
	SU.Title,
	CASE WHEN SI.FullName IS NULL OR SI.FullName='' THEN SI.Name ELSE SI.FullName END AS FullOrUserName,
	BSS.Name AS BuyerShortName,
	OS.LookupCode AS OfficeLookupCode
FROM   
	pfm.[Order] AS O WITH (NOLOCK)
	INNER JOIN pfm.Entity AS E WITH (NOLOCK) ON O.RootId#=E.RootId# AND O.Id#=E.Id#
	INNER JOIN pfm.Contact AS BSS WITH (NOLOCK) ON O.RootId#=BSS.RootId# AND (BSS.Id# BETWEEN O.Id# AND O.LastId#) AND BSS.IsGroupContact=1 AND BSS.Code='BA'  --All Buyers 
	INNER JOIN pf.WorkflowHistory AS WH WITH (NOLOCK) ON E.Guid#=WH.OrderID
	INNER JOIN pf.WorkflowStep AS WS WITH (NOLOCK) ON WH.WorkflowStepID = WS.ID
	INNER JOIN pf.WorkflowProcessVersion AS WPV WITH (NOLOCK) ON WH.WorkflowProcessID = WPV.VersionID
	INNER JOIN pf.WorkflowProcess AS WP WITH (NOLOCK) ON WPV.ProcessID = WP.ID
	INNER JOIN zref.WorkflowStatus AS zWS WITH (NOLOCK) ON WH.WorkflowStatusID = zWS.ID
	LEFT JOIN zref.WorkflowReason AS zWR WITH (NOLOCK) on WH.WorkflowReasonID = zWR.ID
	LEFT OUTER JOIN core.SecurityIdentity AS SI WITH (NOLOCK) ON WH.AssignedTrusteeID = SI.ID
	LEFT OUTER JOIN core.SecurityUser AS SU WITH (NOLOCK) ON WH.AssignedTrusteeID=SU.ID   
	LEFT OUTER JOIN rptdoc.OfficeShort AS OS WITH (NOLOCK) ON O.Rootid#=OS.RootID#
WHERE
	O.IsTemplate=0
	AND WH.AssignedTrusteeID IS NULL
GO
Lisa Ennis
Senior Report Developer
SoftPro
Post Reply