Workflow sample

here is a basic sample how to process data and report processing back into the data interface:

-- a variable to store the bulk ID currently being processed by your system 
DECLARE @BulkId INT = -1;

-- a variable to store the EcENTITY.id of the top entity within the current bulk ID 
DECLARE @EntId BIGINT = -1;

-- get the next pending bulk ID (data waiting to be processed by your system):
SET @BulkId = (SELECT TOP 1 bulk_id FROM EcENTITY WHERE comm_dir=0 AND comm_status=0 ORDER BY id)

-- update comm_status – the bulk processing has started
UPDATE EcENTITY SET comm_status = 1, processed1=GETDATE() WHERE bulk_id = @BulkId

-- get the top entity of the bulk structure
SET @EntId = (SELECT TOP 1 id FROM EcENTITY WHERE bulk_id=@BulkId AND EcENTITY.id NOT IN (SELECT
EcASSOC.ent_id_child FROM EcASSOC))

-- get a list of associations for the top entity – first level associations 
SELECT * FROM EcASSOC WHERE ent_id_parent=@EntId;

-- get a list of links for the top entity 
SELECT * FROM EcLINK WHERE ent_id=@EntId;

-- get a list of child entities in the first level
SELECT * FROM EcENTITY WHERE EcENTITY.id IN (SELECT ent_id_child FROM EcASSOC WHERE
ent_id_parent=@EntId);

-- XXXXXXXXXXXXXXXXXXXXXXXXXXX
-- write data into your system
-- XXXXXXXXXXXXXXXXXXXXXXXXXXX

-- report the bulk as successfully processed by your system

UPDATE EcENTITY SET comm_status = 7, processed2=GETDATE() WHERE bulk_id = @BulkId

-- or report an error
INSERT INTO EcLOG (ent_id, level, message) VALUES (@EntId, 1, 'unknown Unit of Measure') 
UPDATE EcENTITY SET comm_status = 9, processed2=GETDATE() WHERE bulk_id = @BulkId