ECM / Docstar table schema

ECM
1

Our finance users wanted to see the state of invoices going through ECM within Epicor, rather than having to switch systems.

I was able to get the tables layout from Epicor and create a dashboard (external baq) that shows information from the ECM tables and added tabs to display the actual invoice and PO details.

You can see the ECM table layout at https://pedro.docstar.com/edb18.2/

May be helpful for some.

4 Likes
2

That’s excellent - thanks for sharing the link. Could you share your BAQ?

I’ve got a handful of SQL queries to do similar data exploration like this one for document information regarding workflow state or available workflow recordings

SELECT WFDocument.id
		,Document.Id
		,document.DocumentType
		,document.WorkflowDataJSON
		,Workflow.Name as WFName
		,WFStep.Name as WFStep
		,WFAction.Name as WFAction
		,WFDocument.Parameters
		,WFDocument.State --????
		,WFDocument.Started
		,WFDocument.Completed
		,WFDocument.ChatHistory
		,WFDocument.Exception
		,WFDocument.ExceptionDetail
		,WFDocument.AssignedOn
		,WFDocument.ActionStarted
		,WFDocument.Recording
		--,[WorkflowId]
		--,[VersionId]
		--,[PackageId]
		--,[AssigneeId]
		--,[OwnerId]
		--,[CurrentStepId]
		--,[CurrentActionId]

FROM WFDocument
		left join Document on WFDocument.Id=Document.Id
		LEFT JOIN WFStep ON WFStep.Id = WFDocument.CurrentStepId
		LEFT JOIN WFAction ON WFAction.Id = WFDocument.CurrentActionId
		LEFT JOIN Workflow ON Workflow.Id = WFDocument.WorkflowId
		left join ContentType on document.ContentTypeId=ContentType.Id
		-- Add Package, PackageType, PackageSlot and PackageSlotDocument to get Package data

--where Recording is not null -- this will show you those that have WF recordings
where WFStep.Name is not null -- this will show everything that is still IN an active workflow state
3 Likes
3

Thanks for sharing that.