A couple days ago, Gina left a comment on my 3 Secrets To An Awesome ERP System! blog post. She was wondering if it was possible on the Payables Transaction Workflow to include Document Type, Document Date, Document Number, Transaction Description, Vendor Name and Amount. The answer is YES, it is possible. The below SQL view only works with the Payables Transaction Workflow but can be modified to work with other transactional workflows. It will not work with batch workflows because batch IDs lack an unique ID to create the relationship against. Here is the modified SQL view to complete the request:
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_WorkFlow_Status]
AS
WITH CTE_FINAL (WorkflowInstanceID, Workflow_Name, Workflow_Step_Name, Approver, Workflow_Action, Completion_Date, Completion_Time, Comments )
AS
(select d.WorkflowInstanceID,
d.Workflow_Name,
d.Workflow_Step_Name,
CASE WHEN a.ADDisplayName is null THEN ''
ELSE a.ADDisplayName
END as [Assigned Approver],
CASE WHEN d.Workflow_Action = 1 THEN 'Submit'
WHEN d.Workflow_Action = 2 THEN 'Resubmit'
WHEN d.Workflow_Action = 3 THEN 'Approve'
WHEN d.Workflow_Action = 4 THEN 'Task Complete'
WHEN d.Workflow_Action = 5 THEN 'Reject'
WHEN d.Workflow_Action = 6 THEN 'Delegate'
WHEN d.Workflow_Action = 7 THEN 'Recall'
WHEN d.Workflow_Action = 8 THEN 'Escalate'
WHEN d.Workflow_Action = 9 THEN 'Edit'
ELSE 'Final Approve'
END as Workflow_Action,
convert(varchar(10),d.Workflow_Completion_Date, 101) as [Completion_Date],
right('0'+LTRIM(right(convert(varchar,d.Workflow_Completion_Time,100),8)),7) as Completion_Time,
d.Workflow_Comments
from dbo.WF30100 d
LEFT JOIN WF40200 a ON d.Workflow_Step_Assign_To = a.UsersListGuid
WHERE d.Workflow_Action = 10), CTE_PM as (SELECT P.VENDORID Vendor_ID,
V.VENDNAME Vendor_Name,
P.VCHRNMBR Voucher,
CASE P.DOCTYPE
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
WHEN 4 THEN 'Return'
WHEN 5 THEN 'Credit Memo'
WHEN 6 THEN 'Payment'
END Document_Type,
P.DOCDATE Document_Date,
P.PSTGDATE GL_Posting_Date,
P.DUEDATE Due_Date,
P.DOCNUMBR Document_Number,
P.DOCAMNT Document_Amount,
P.CURTRXAM Unapplied_Amount,
P.TRXDSCRN [Description],
CASE P.VOIDED
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
END Voided
FROM (SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE,
DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
FROM PM20000
UNION ALL
SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE,
DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
FROM PM30200
UNION ALL
SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE,
DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, 0 as VOIDED
FROM PM10000) P
INNER JOIN
PM00200 V
ON V.VENDORID = P.VENDORID)
Select WF.*
,coalesce(pm.Document_Type,'') as Document_Type
,coalesce(pm.Document_Date,'') as Document_Date
,coalesce(pm.Document_Number, '') as Document_Number
,pm.Voucher
,coalesce(pm.Description, '') as Description
,coalesce(pm.Vendor_Name,'') as Vendor_Name
,coalesce(pm.Document_Amount,0) as Document_Amount
FROM
(SELECT ROW_NUMBER() OVER ( ORDER BY A.DEX_ROW_ID ) RecordSequence ,
ISNULL(C.WfBusObjKey, '') WFBusObjKey ,
LEFT(WfBusObjKey, ISNULL(NULLIF(CHARINDEX('~', WFBusObjKey) - 1, -1), LEN(WfBusObjKey))) as Split,
Workflow_History_User ,
C.Workflow_Type_Name,
A.Workflow_Name ,
A.Workflow_Step_Name ,
ISNULL(B.WorkflowTaskAssignedTo, '') WorkflowTaskAssignedTo ,
CASE C.Workflow_Status
WHEN 1 THEN 'Not Submitted'
WHEN 2 THEN 'Submitted (Deprecated)'
WHEN 3 THEN 'No Action Needed'
WHEN 4 THEN 'Pending User Action'
WHEN 5 THEN 'Recalled'
WHEN 6 THEN 'Completed'
WHEN 7 THEN 'Rejected'
WHEN 8 THEN 'Workflow Ended (Depricated)'
WHEN 9 THEN 'Not Activated'
WHEN 10 THEN 'Deactivated (Depricated)'
ELSE ''
END AS WORKFLOW_STATUS ,
CASE A.Workflow_Action
WHEN 1 THEN 'Submit'
WHEN 2 THEN 'Resubmit'
WHEN 3 THEN 'Approve'
WHEN 4 THEN 'Task Complete'
WHEN 5 THEN 'Reject'
WHEN 6 THEN 'Delegate'
WHEN 7 THEN 'Recall'
WHEN 8 THEN 'Escalate'
WHEN 9 THEN 'Edit'
WHEN 10 THEN 'Final Approve'
ELSE ''
END AS Workflow_Action ,
A.Workflow_Due_Date ,
A.Workflow_Completion_Date ,
A.DEX_ROW_ID ,
Workflow_Comments,
CASE WHEN d.Approver is null THEN ''
ELSE d.Approver
END as Approver,
CASE WHEN d.Completion_Date is null THEN ''
ELSE d.Completion_Date
END as Completion_Date,
CASE WHEN d.Completion_Time is null THEN ''
ELSE d.Completion_Time
END as Completion_Time,
CASE WHEN d.Comments is null THEN ''
ELSE d.Comments
END as Comments
FROM WF30100 AS A
LEFT OUTER JOIN WFI10004 AS B ON A.WorkflowInstanceID = B.WorkflowInstanceID
AND A.WorkflowStepInstanceID = B.WorkflowStepInstanceID
LEFT OUTER JOIN WFI10002 AS C ON C.WorkflowInstanceID = A.WorkflowInstanceID
LEFT OUTER JOIN CTE_FINAL d ON D.WorkflowInstanceID = c.WorkflowInstanceID) WF
LEFT OUTER JOIN CTE_PM pm on pm.Voucher = WF.Split
GO
Until next time. Enjoy the code!